Function Validasi Alokasi FIn AR

CREATE OR REPLACE FUNCTION fi_val_cross_month_alloc_trx_for_closing_ar(bigint, character varying, bigint, character varying, bigint, character varying, bigint)
  RETURNS integer AS
$BODY$
DECLARE

    pTenantId               ALIAS FOR $1;
    pSessionId              ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pYearMonth              ALIAS FOR $4;
    pAdminProcessLedgerId   ALIAS FOR $5;
    pDatetime               ALIAS FOR $6;
    pUserId                 ALIAS FOR $7;

    vFlagAlloc              character varying(1);
    vResult                 integer;
    vDocTypeIdCGReceipt     bigint := 624;
BEGIN

    vFlagAlloc := 'Y';
    vResult := 0;

    DELETE FROM fi_cross_month_allocation_cb_in_ar WHERE admin_process_ledger_id = pAdminProcessLedgerId;

    -- ambil data receipt ar balance sudah dialokasi
    -- HS, 20201111 : exclude CG Receipt, karna tidak masalah dialokasi lintas bulan
    -- karna saat buat CG Receipt tidak ada buat jurnal AR
    INSERT INTO fi_cross_month_allocation_cb_in_ar(
            tenant_id, admin_process_ledger_id,
            ou_id, doc_type_id, doc_no, doc_date, cashbank_id, partner_id,
            curr_code, amount, remark, alloc_id, alloc_doc_no, alloc_doc_date,
            alloc_amount, process_datetime, process_user_id)
    SELECT A.tenant_id, pAdminProcessLedgerId, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.cashbank_id, A.partner_id, A.curr_code, A.amount, A.remark,
        D.allocation_ar_id, D.doc_no AS alloc_doc_no, D.doc_date AS alloc_doc_date,
        SUM(E.credit_amount) AS alloc_amount, pDatetime, pUserId
    FROM fi_receipt_ar_balance A
    INNER JOIN dt_date B ON A.doc_date = B.string_date
    INNER JOIN m_ou_structure C ON A.ou_id = C.ou_id
    INNER JOIN fi_allocation_ar D ON A.ref_alloc_id = D.allocation_ar_id
    INNER JOIn fi_allocation_ar_balance E ON D.allocation_ar_id = E.allocation_ar_id
    WHERE A.tenant_id = pTenantId AND
        A.doc_type_id <> vDocTypeIdCGReceipt AND
        A.flg_alloc = vFlagAlloc AND
        A.doc_date = B.string_date AND
        B.year_month_date = pYearMonth AND
        B.year_month_date <> SUBSTR(D.doc_date,1,6) AND
        A.ou_id = pOuId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.cashbank_id, A.partner_id, A.curr_code, A.amount, A.remark,
        D.allocation_ar_id, D.doc_no, D.doc_date;

    IF (SELECT COUNT(*) FROM fi_cross_month_allocation_cb_in_ar WHERE admin_process_ledger_id = pAdminProcessLedgerId) > 0 THEN
        vResult := 1;
    END IF;

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /