DROP PROCEDURE IF EXISTS sp_purchasePaymentVoucherPosting;

CREATE PROCEDURE sp_purchasePaymentVoucherPosting()
BEGIN

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

    CREATE TEMPORARY TABLE TMP_VNDR_PAYMNTS(
        REF_ID INT NOT NULL,
        PY_TYP CHAR(2) NOT NULL,
        AMT DECIMAL(15,2) NOT NULL,
        TRN_ID INT DEFAULT NULL
    );

    INSERT  INTO TMP_VNDR_PAYMNTS(
        REF_ID,
        PY_TYP,
        AMT,
        TRN_ID
    )
    SELECT
        VP.id,
        VP.payment_mode_id,
        VP.paid_amount,
        VP.transaction_bank
    FROM
        vendor_payments VP	
    WHERE
        VP.data_source = 'Purchase' 
        AND VP.payment_type = 'P'
        AND VP.voucher_posting_status = 'N';

    /* 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';

    /* DEFAULT CASH CODE FOR HOSPITAL */
    SELECT default_account_code INTO @HOSP_CASH_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'CASH';

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

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

        PREPARE stmt FROM "SELECT 
                            REF_ID,
                            PY_TYP,
                            AMT,
                            TRN_ID
                        INTO 
                            @REF_ID,
                            @PY_TYP,
                            @AMT,
                            @TRN_ID
                        FROM TMP_VNDR_PAYMNTS LIMIT ?, 1;";
        EXECUTE stmt USING @COUNTER;
        DEALLOCATE PREPARE stmt;

        IF @PY_TYP = 'C' THEN
            SET @ACC_CD = @HOSP_CASH_ACC_CD;
        ELSEIF (@PY_TYP = 'Q' OR @PY_TYP = 'T') AND @TRN_ID IS NOT NULL THEN
            SELECT
                ASIMD.account_map_code_id INTO @ACC_CD 
            FROM
                account_service_item_map_details ASIMD
                JOIN account_service_item_map ASIM ON ASIM.id = ASIMD.account_service_item_map_id 
                AND ASIM.account_map_type = 'M' 
                AND ASIM.account_map_module_id = 24 
            WHERE
                ASIMD.module_wise_category_id = @TRN_ID;
        END IF;

        IF @ACC_CD IS NOT NULL THEN

            /* 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,'PA',@REF_ID), 
                'Tr', 
                'A', 
                @REF_ID, 
                'vendor_payments', 
                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, @AMT, 'C', CURRENT_TIMESTAMP, 0),
            (@LAST_INSERT_ID, @HOSP_PYBL_ACC_CD, @AMT, 'D', CURRENT_TIMESTAMP, 0);

            UPDATE vendor_payments SET voucher_posting_status = 'Y' WHERE id = @LAST_INSERT_ID;

        END IF;
    
        SET @COUNTER = @COUNTER + 1;
    END LOOP ACC_PRCHS_PAY_ITRTR;

END;