DROP PROCEDURE IF EXISTS sp_purchaseReturnVoucherPosting;

CREATE PROCEDURE sp_purchaseReturnVoucherPosting()
BEGIN

/* Create temporary table to fetch all purchases that are required for voucher entry */
    DROP TEMPORARY TABLE IF EXISTS TMP_TBL_PURCHASE_RTRN_VOUCHER_STAGING;

    CREATE TEMPORARY TABLE TMP_TBL_PURCHASE_RTRN_VOUCHER_STAGING(
        REF_ID INT NOT NULL,
        NT_AMT DECIMAL(15,2) NOT NULL,
        CGST DECIMAL(15,2) NOT NULL,
        SGST DECIMAL(15,2) NOT NULL,
        DISC_AMT DECIMAL(15,2) NOT NULL,
        ITEM_ID INT NOT NULL,
        LUV_ID INT NOT NULL,
        MOD_ID INT NOT NULL
    );

    INSERT INTO TMP_TBL_PURCHASE_RTRN_VOUCHER_STAGING(
        REF_ID,
        NT_AMT,
        CGST,
        SGST,
        DISC_AMT,
        ITEM_ID,
        LUV_ID,
        MOD_ID
    )
    SELECT
        PORI.id,
        PORI.net_price,
        PORI.cgst,
        PORI.sgst,
        PORI.discount_amount,
        I.id I_ID,
        LUV.id LUV_ID,
        IGWM.module_id M_ID 
    FROM
        purchase_order_return_items PORI
        JOIN purchase_order_returns POR ON PORI.purchase_order_return_id = POR.id
        JOIN items I ON I.id = PORI.item_id
        JOIN lookup_values LUV ON I.item_group = LUV.short_code
        JOIN lookups LU ON LUV.lookup_id = LU.id 
        AND LU.lookup_code = 'ITEM_GROUP'
        JOIN item_groupwise_module IGWM ON LUV.id = IGWM.lookup_value_id
        WHERE
        PORI.status = 'Y'
        AND POR.status = 'A'
        AND POR.is_adjust = 'Y'
        AND PORI.is_voucher_posted = 'N';

    SET @COUNTER = 1; /* LOOP COUNTER */
    SELECT COUNT(1) INTO @MAX_COUNT FROM TMP_TBL_PURCHASE_RTRN_VOUCHER_STAGING; /* LOOP'S MAX ITERATION COUNTER */


    /* GETTING DEFAULT PAYABLE CODE ID */
    SELECT default_account_code INTO @HOSP_PYBL_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'HOSPITAL_PAYABLES';

    /* GETTING DEFAULT SGST-OUT CODE ID */
    SELECT default_account_code INTO @SGST_OUT_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'SGST(OUT)';

    /* GETTING DEFAULT CGST-OUT CODE ID */
    SELECT default_account_code INTO @CGST_OUT_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'CGST(OUT)';

    /* GETTING DEFAULT DISCOUNT_TAKEN CODE ID */
    SELECT default_account_code INTO @DISC_TAKEN_CD FROM account_service_item_map WHERE account_service_map_code = 'DISCOUNT_TAKEN';

    ACC_PRCH_RTRN_ITRTR:LOOP
        IF @COUNTER > @MAX_COUNT THEN /* ITERATION END CHECKER */
            LEAVE ACC_PRCH_RTRN_ITRTR;
        END IF;

        PREPARE stmt FROM "SELECT
                            NT_AMT,
                            REF_ID,
                            CGST,
                            SGST,
                            DISC_AMT,
                            ITEM_ID,
                            LUV_ID,
                            MOD_ID
                        INTO 
                            @NT_AMT,
                            @REF_ID,
                            @CGST,
                            @SGST,
                            @DISC_AMT,
                            @ITEM_ID,
                            @LUV_ID,
                            @MOD_ID
                        FROM TMP_TBL_PURCHASE_RTRN_VOUCHER_STAGING LIMIT ?, 1;";
        EXECUTE stmt USING @COUNTER;
        DEALLOCATE PREPARE stmt;

        /* GETTING ACC CODE */
        SELECT
            ASIMD.account_map_code_id  
        INTO 
            @ACC_CD
        FROM
            account_service_item_map_details ASIMD
            JOIN account_service_item_map ASIM ON ASIMD.account_service_item_map_id = ASIM.id 
            AND ASIM.account_map_type = 'M' 
            AND ASIM.account_map_module_id = 10
        WHERE
            ASIM.is_active = 'Y' 
            AND ASIMD.is_active = 'Y' 
            AND (
                (
                    ASIM.account_map_lavel = 1 
                    AND ASIMD.module_wise_category_id = @LUV_ID
                )
                OR (
                    ASIM.account_map_lavel = 2
                    AND ASIMD.module_wise_category_id = @MOD_ID
                )
                OR (
                    ASIM.account_map_lavel = 3
                    AND ASIMD.module_wise_category_id = @ITEM_ID
                )
            );

        IF @ACC_CD IS NULL THEN 
            INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('ERROR',"I'M NOT MAPPED");
        ELSE 

            SET @PRODUCT_COST_WO_GST = (@NT_AMT + @DISC_AMT) - (@CGST + @SGST);
            SET @PAID_AMT = @NT_AMT - (@CGST + @SGST);

            /* INSERTING INTO VOUCHER NARRATION */
            INSERT INTO voucher_narration(
                voucher_date,
                voucher_number,
                narration,
                transaction_type,
                voucher_status,
                reference_id,
                reference_table,
                created_at,
                created_by
            ) VALUES(
                CURDATE(), 
                fn_get_curdate_vid(CURDATE()), 
                fn_get_v_narration(10,'PUR',@REF_ID), 
                'Tr', 
                'A', 
                @REF_ID, 
                'purchase_order_return_items', 
                CURRENT_TIMESTAMP,
                0
            );

            SELECT LAST_INSERT_ID() INTO @LAST_INSERT_ID; /* FETCHING LAST INSERTED ID */

            

            /* INSERTING INTO VOUCHER DETAILS FOR INCOME CREDIT AND RECIEVABLE PROVISION DEBIT */
            INSERT INTO voucher_details(
                voucher_narration_id,
                account_head_id,
                voucher_amount,
                debit_credit_flag,
                created_at,
                created_by
            )
            VALUES
            (@LAST_INSERT_ID, @ACC_CD, @PRODUCT_COST_WO_GST, 'C', CURRENT_TIMESTAMP, 0),
            (@LAST_INSERT_ID, @HOSP_PYBL_ACC_CD, @PAID_AMT, 'D', CURRENT_TIMESTAMP, 0),
            (@LAST_INSERT_ID, @SGST_OUT_ACC_CD, @SGST, 'C', CURRENT_TIMESTAMP, 0),
            (@LAST_INSERT_ID, @CGST_OUT_ACC_CD, @CGST, 'C', CURRENT_TIMESTAMP, 0);

            IF @DISC_AMT > 0 THEN 
                /* CREDITING INCOME FOR DISCOUNT RECIEVED */
                INSERT INTO voucher_details(
                    voucher_narration_id,
                    account_head_id,
                    voucher_amount,
                    debit_credit_flag,
                    created_at,
                    created_by
                )
                VALUES
                (@LAST_INSERT_ID, @DISC_TAKEN_CD, @DISC_AMT, 'D', CURRENT_TIMESTAMP, 0);
            END IF;

            /* UPDATING TRANSACTION TABLE ON VOUCHER POSTING AS VOUCHER POSTED */
            UPDATE purchase_order_return_items SET is_voucher_posted = 'Y' WHERE id = @REF_ID;

            INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('SUCCESS',@LAST_INSERT_ID);
        END IF;

        SET @COUNTER = @COUNTER + 1;
    END LOOP ACC_PRCH_RTRN_ITRTR;

END;