Functon Submit POAP

CREATE OR REPLACE FUNCTION fi_submit_payment_order_ap(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId          bigint;
    vPaymentOrderApId   bigint;
    vUserId             bigint;
    vDatetime           character varying(14);
    vStatusRelease      character varying(1);
    vEmptyId            bigint;
    vEmptyValue         character varying(1);

    vCursorDebtAp       fi_payment_order_invoice%ROWTYPE;
    vDebtAmount         numeric;
    vCreditAmount       numeric;
    vCountDebtAp        integer;
    vCountCreditAp      integer;
    vStartCount         integer;
    vAllocAmount        numeric;
    vOuId               bigint;
    vDocDebtDate        character varying(8);
    vParentOuId         bigint;
    vJournalType        character varying(20);
    vJournalTrxId       bigint;
    vDocJournal         DOC_JOURNAL%ROWTYPE;
    vOuStructure        OU_BU_STRUCTURE%ROWTYPE;
    result              RECORD;

    vSldPrepaymentDoc   bigint;

    vStatusDraft        character varying(1);
    vSignDebit          character varying(1);
    vSignCredit         character varying(1);
    vTypeRate           character varying(3);
    vSystemCOA          character varying(10);
    vCountAllocAP       integer;
    vValutaBuku         character varying(5);
    vCountDocDNAP       bigint:=0;

BEGIN
    vCountAllocAP := 0;
    vSldPrepaymentDoc := 212;

    vStatusRelease := 'R';
    vEmptyId := -99;
    vEmptyValue := ' ';
    vDebtAmount := 0;
    vCreditAmount := 0;
    vCountDebtAp := 0;
    vCountCreditAp := 0;
    vAllocAmount := 0;
    vStatusDraft := 'D';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vSystemCOA := 'SYSTEM';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'fi_submit_payment_order_ap' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vPaymentOrderApId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'paymentOrderApId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    DELETE FROM tt_fi_payment_order_alloc_balance WHERE session_id = pSessionId;

    DELETE FROM tt_fi_payment_alloc_credit WHERE session_id = pSessionId;

/*
 * 1.insert cb_trx_cashbank_balance, jika payment order AP amount > 0
 * 2.update status fi_invoice_ap_balance, fi_invoice_tax_ap_balance
 * 3.insert data fi_payment_order_alloc_balance
 * 4.update workflow status payment order AP
 */
    SELECT A.ou_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM fi_payment_order A
    WHERE A.payment_order_id = vPaymentOrderApId INTO result;

    vOuId := result.ou_id;
    vOuStructure := result.ou;
    vDocJournal := result.doc;

    INSERT INTO cb_trx_cashbank_balance
    (tenant_id, ou_id, doc_type_id, payment_id,
      payment_doc_no, payment_doc_date, payment_remark,
      partner_id, partner_bank_id, curr_code, amount,
      due_date, flg_payment, ref_doc_type_id, ref_id,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id,
        A.doc_no, A.doc_date, A.remark,
        A.partner_id, A.partner_bank_id, A.curr_code, A.payment_amount,
        A.due_date, 'N', vEmptyId, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM fi_payment_order A
    WHERE A.payment_order_id = vPaymentOrderApId AND
        A.payment_amount > 0;

    UPDATE fi_invoice_ap_balance SET payment_amount = fi_invoice_ap_balance.payment_amount + A.credit_amount - A.debit_amount,
                                flg_payment = CASE WHEN (fi_invoice_ap_balance.amount - (fi_invoice_ap_balance.payment_amount + A.credit_amount - A.debit_amount)) <> 0 THEN 'N' ELSE 'Y' END,
                                update_datetime = vDatetime, update_user_id = vUserId
    FROM fi_payment_order_invoice A
    WHERE A.payment_order_id = vPaymentOrderApId AND
        fi_invoice_ap_balance.doc_type_id = A.ref_doc_type_id AND
        invoice_ap_balance_id = A.ref_id;

    UPDATE fi_invoice_tax_ap_balance SET payment_amount = fi_invoice_tax_ap_balance.payment_amount + A.credit_amount - A.debit_amount,
                                flg_payment = CASE WHEN (fi_invoice_tax_ap_balance.gov_tax_amount - (fi_invoice_tax_ap_balance.payment_amount + A.credit_amount - A.debit_amount)) <> 0 THEN 'N' ELSE 'Y' END,
                                update_datetime = vDatetime, update_user_id = vUserId
    FROM fi_payment_order_invoice A
    WHERE A.payment_order_id = vPaymentOrderApId AND
        fi_invoice_tax_ap_balance.doc_type_id = A.ref_doc_type_id AND
        invoice_tax_ap_balance_id = A.ref_id;

    SELECT COUNT(*) INTO vCountDebtAp
    FROM fi_payment_order_invoice A
    WHERE A.payment_order_id = vPaymentOrderApId AND
            A.debit_amount > 0 AND
            A.credit_amount = 0;

    SELECT COUNT(*) INTO vCountCreditAp
    FROM fi_payment_order_invoice A
    WHERE A.payment_order_id = vPaymentOrderApId AND
            A.debit_amount = 0 AND
            A.credit_amount > 0;

    IF vCountDebtAp > 0 THEN

        INSERT INTO tt_fi_payment_alloc_credit
        (session_id, ranking,
        ref_doc_type_id, ref_id, ref_doc_date, curr_code,
        credit_amount, alloc_amount)
        SELECT pSessionId, RANK() OVER (PARTITION BY A.payment_order_id ORDER BY credit_amount DESC, ref_id),
            A.ref_doc_type_id, A.ref_id,
            fi_get_rate_date_invoice_ap(A.ref_doc_type_id, A.ref_id), A.curr_code,
            A.credit_amount, 0
        FROM fi_payment_order_invoice A
        WHERE A.payment_order_id = vPaymentOrderApId AND
            A.credit_amount > 0 AND
            A.debit_amount = 0;

        vStartCount := 1;

        FOR vCursorDebtAp IN SELECT * FROM fi_payment_order_invoice
                            WHERE payment_order_id = vPaymentOrderApId AND
                                    debit_amount > 0 AND credit_amount = 0
                                    ORDER BY debit_amount DESC
        LOOP
            vDebtAmount := vCursorDebtAp.debit_amount;
            vDocDebtDate := fi_get_rate_date_invoice_ap(vCursorDebtAp.ref_doc_type_id, vCursorDebtAp.ref_id);

            FOR i IN vStartCount..vCountCreditAp LOOP

                SELECT credit_amount - alloc_amount INTO vAllocAmount
                FROM tt_fi_payment_alloc_credit A
                WHERE A.session_id = pSessionId AND
                    A.ranking = i;

                IF vAllocAmount > vDebtAmount THEN

                    INSERT INTO tt_fi_payment_order_alloc_balance
                    (session_id, payment_order_id, tenant_id, ou_id, flg_alloc,
                    credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
                    debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount)
                    SELECT pSessionId, vPaymentOrderApId, pTenantId, vOuId, 'A',
                        A.ref_doc_type_id, A.ref_doc_date, A.ref_id, A.curr_code, vDebtAmount,
                        vCursorDebtAp.ref_doc_type_id, vDocDebtDate, vCursorDebtAp.ref_id, vCursorDebtAp.curr_code, vDebtAmount
                    FROM tt_fi_payment_alloc_credit A
                    WHERE A.session_id = pSessionId AND
                        A.ranking = i;

                    UPDATE tt_fi_payment_alloc_credit SET alloc_amount = alloc_amount + vDebtAmount
                    WHERE session_id = pSessionId AND
                        ranking = i;

                    EXIT;
                ELSE
                    INSERT INTO tt_fi_payment_order_alloc_balance
                    (session_id, payment_order_id, tenant_id, ou_id, flg_alloc,
                    credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
                    debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount)
                    SELECT pSessionId, vPaymentOrderApId, pTenantId, vOuId, 'A',
                        A.ref_doc_type_id, A.ref_doc_date, A.ref_id, A.curr_code, vAllocAmount,
                        vCursorDebtAp.ref_doc_type_id, vDocDebtDate, vCursorDebtAp.ref_id, vCursorDebtAp.curr_code, vAllocAmount
                    FROM tt_fi_payment_alloc_credit A
                    WHERE A.session_id = pSessionId AND
                        A.ranking = i;

                    UPDATE tt_fi_payment_alloc_credit SET alloc_amount = alloc_amount + vAllocAmount
                    WHERE session_id = pSessionId AND
                        ranking = i;

                    vDebtAmount := vDebtAmount - vAllocAmount;

                    vStartCount := i + 1;
                END IF;

            END LOOP;

            --RETURN NEXT vCursorDebtAp; -- return current row of SELECT
        END LOOP;

        INSERT INTO tt_fi_payment_order_alloc_balance
        (session_id, payment_order_id, tenant_id, ou_id, flg_alloc,
        credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
        debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount)
        SELECT pSessionId, vPaymentOrderApId, pTenantId, vOuId, 'C',
            A.ref_doc_type_id, A.ref_doc_date, A.ref_id, A.curr_code, A.credit_amount - A.alloc_amount,
            vEmptyId, vEmptyValue, vEmptyId, vEmptyValue, 0
        FROM tt_fi_payment_alloc_credit A
        WHERE A.session_id = pSessionId AND
            A.credit_amount - A.alloc_amount > 0;

    ELSE
        INSERT INTO tt_fi_payment_order_alloc_balance
        (session_id, payment_order_id, tenant_id, ou_id, flg_alloc,
        credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
        debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount)
        SELECT pSessionId, vPaymentOrderApId, pTenantId, vOuId, 'C',
            A.ref_doc_type_id, fi_get_rate_date_invoice_ap(A.ref_doc_type_id, A.ref_id), A.ref_id, A.curr_code, A.credit_amount,
            vEmptyId, vEmptyValue, vEmptyId, vEmptyValue, 0
        FROM fi_payment_order_invoice A
        WHERE A.payment_order_id = vPaymentOrderApId;

    END IF;

    INSERT INTO fi_payment_order_alloc_balance
    (payment_order_id, tenant_id, ou_id,
    credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
    debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount,
    flg_alloc, ref_alloc_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT payment_order_id, tenant_id, ou_id,
        credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
        debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount,
        flg_alloc, vEmptyId, vEmptyId, vEmptyValue, vEmptyValue,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_fi_payment_order_alloc_balance
    WHERE session_id = pSessionId;

    UPDATE fi_payment_order SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE payment_order_id = vPaymentOrderApId;

    /**
     * Erl 28 oktober 2021,
     * Tidak perlu buat journal dengan syarat
     * 1. Ada debt invoice (Return Purchase Invoice, DN AP, Prepayment Balance) yang digunakan
     * 2. Semua invoice yang digunakan, baik credit invoice (Purchase Invoice, CN AP) maupun debt invoice (Return Purchase Invoice, DN AP, Prepayment Balance), dalam valuta sesuai valuta buku.
     *
     * Note: Prepayment tidak disebutkan, karena di POAP dokumen Prepayment tidak boleh digabung dengan debt invoice, berarti secara tidak langsung tidak termasuk dalam kondisi 1 dan 2.
     */

    /*
     * hanya jika PB AP ada lakukan alokasi Credit AP dan Debit AP
     * membuat data transaksi jurnal :
     * 1. buat admin
     * 2. buat temlate jurnal
     */
    SELECT COUNT(*) INTO vCountAllocAP
    FROM tt_fi_payment_order_alloc_balance
    WHERE session_id = pSessionId AND
        flg_alloc = 'A';

    RAISE NOTICE 'vCountAllocAP = %',vCountAllocAP;

    IF vCountAllocAP > 0 THEN -- ini sudah termasuk syarat 1, kar ada dokumenena bila ada alokasi maka berarti ada dokumen Debt invoice dan credit invoice
        IF EXISTS (
            SELECT 1
            FROM fi_payment_order A
            JOIN fi_payment_order_invoice B ON A.payment_order_id = B.payment_order_id
            WHERE A.payment_order_id = vPaymentOrderApId
            AND A.curr_code <> vValutaBuku -- syarat 2
        ) OR EXISTS (
                SELECT 1
                FROM fi_invoice_ap_balance A
                INNER JOIN fi_payment_order_invoice B ON A.invoice_ap_balance_id = B.ref_id AND B.ref_doc_type_id = vSldPrepaymentDoc
                WHERE B.payment_order_id  = vPaymentOrderApId
        )
        THEN

            PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
            FROM fi_payment_order A
            WHERE A.payment_order_id = vPaymentOrderApId;

            SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

            INSERT INTO gl_journal_trx
            (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
            ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
            ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.payment_order_id, A.doc_no, A.doc_date,
                (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
                A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
                0, vDatetime, vUserId, vDatetime, vUserId
            FROM fi_payment_order A, fi_payment_order_alloc_balance B
            WHERE A.payment_order_id = vPaymentOrderApId AND
                A.payment_order_id = B.payment_order_id AND
                B.flg_alloc = 'A'
            GROUP BY A.tenant_id, A.doc_type_id, A.payment_order_id, A.doc_no, A.doc_date, A.partner_id, A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark;

            /*
             * journal detail debit invoice yang melunasi credit invoice
             * tanggal rate menggunakan tanggal Debt Ap Dokumen
             *
             * jika debit doc type id <> saldo prepayment, maka Credit AP
             * jika debit doc type id = saldo prepayment, maka Credit AP_ADVANCE
             */
            INSERT INTO tt_journal_trx_item
            (session_id, tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark)
            SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
                B.debit_doc_type_id, B.debit_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
                f_get_ap_coa_partner(A.tenant_id, A.partner_id), B.debit_curr_code, 0, vEmptyId,
                SUM(B.debit_amount), B.debit_doc_date, vTypeRate,
                1, 1, 'AP_DEBIT_INVOICE', A.remark
            FROM fi_payment_order A, fi_payment_order_alloc_balance B
            WHERE A.payment_order_id = vPaymentOrderApId AND
                A.payment_order_id = B.payment_order_id AND
                B.flg_alloc = 'A' AND
                B.debit_doc_type_id <> vSldPrepaymentDoc
            GROUP BY A.tenant_id, B.debit_doc_type_id, B.debit_id, A.partner_id, B.debit_curr_code, B.debit_doc_date, A.remark;

            INSERT INTO tt_journal_trx_item
            (session_id, tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark)
            SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
                B.debit_doc_type_id, B.debit_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
                f_get_coa_id_for_prepayment(A.tenant_id), B.debit_curr_code, 0, vEmptyId,
                SUM(B.debit_amount), B.debit_doc_date, vTypeRate,
                1, 1, 'AP_ADVANCE', A.remark
            FROM fi_payment_order A, fi_payment_order_alloc_balance B
            WHERE A.payment_order_id = vPaymentOrderApId AND
                A.payment_order_id = B.payment_order_id AND
                B.flg_alloc = 'A' AND
                B.debit_doc_type_id = vSldPrepaymentDoc
            GROUP BY A.tenant_id, B.debit_doc_type_id, B.debit_id, A.partner_id, B.debit_curr_code, B.debit_doc_date, A.remark;

            /*
             * journal detail credit invoice yang dilunasi oleh debit invoice
             * tanggal rate menggunakan tanggal invoice
             */
            INSERT INTO tt_journal_trx_item
            (session_id, tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark)
            SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
                B.credit_doc_type_id, B.credit_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
                f_get_ap_coa_partner(A.tenant_id, A.partner_id), B.credit_curr_code, 0, vEmptyId,
                SUM(B.credit_amount), B.credit_doc_date, vTypeRate,
                1, 1, 'AP_CREDIT_INVOICE', A.remark
            FROM fi_payment_order A, fi_payment_order_alloc_balance B
            WHERE A.payment_order_id = vPaymentOrderApId AND
                A.payment_order_id = B.payment_order_id AND
                B.flg_alloc = 'A'
            GROUP BY A.tenant_id, B.credit_doc_type_id, B.credit_id, A.partner_id, B.credit_curr_code, B.credit_doc_date, A.remark;

            INSERT INTO gl_journal_trx_item
            (tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
                A.ref_doc_type_id, A.ref_id,
                A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
                A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
                A.coa_id, A.curr_code, A.qty, A.uom_id,
                A.amount, A.journal_date, A.type_rate,
                A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
                0, vDatetime, vUserId, vDatetime, vUserId
            FROM tt_journal_trx_item A
            WHERE A.session_id = pSessionId AND
                journal_desc IN ('AP_CREDIT_INVOICE');

            INSERT INTO gl_journal_trx_mapping
            (tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
                A.ref_doc_type_id, A.ref_id,
                A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
                A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
                A.coa_id, A.curr_code, A.qty, A.uom_id,
                A.amount, A.journal_date, A.type_rate,
                A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
                0, vDatetime, vUserId, vDatetime, vUserId
            FROM tt_journal_trx_item A
            WHERE A.session_id = pSessionId AND
                journal_desc IN ('AP_DEBIT_INVOICE', 'AP_ADVANCE');

            DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
        END IF;
    END IF;

    DELETE FROM tt_fi_payment_order_alloc_balance WHERE session_id = pSessionId;

    DELETE FROM tt_fi_payment_alloc_credit WHERE session_id = pSessionId;

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