sl_cancel_submit_sales_invoice

CREATE OR REPLACE FUNCTION sl_cancel_submit_sales_invoice(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pDatetime           ALIAS FOR $4;
    pSalesInvoiceId     ALIAS FOR $5;
    pRemarkApproval     ALIAS FOR $6;
    pPrevProcessNo      ALIAS FOR $7;

    vStatusDoc          character varying;
    vWorkflowStatus     character varying;
    vDocNo              character varying;
    vDocDate            character varying;
    vSalesLedgerCode    character varying := 'SALES';
    vStatusLedgerNotDone    character varying := '0';
    vOuId               bigint;
    vJournalTrxId       bigint;

    vDocJournal         DOC_JOURNAL%ROWTYPE;
    vOuStructure        OU_BU_STRUCTURE%ROWTYPE;
    result              RECORD;

    vInvoiceArBalanceId     bigint;
    vInvoiceTaxArBalanceId  bigint;
    vSalesInvoiceTempId     bigint;
    vInvoiceArBalanceIdForTemp  bigint;
    vRegularDiscount        bigint;
    vPromoDiscount          bigint;
    vFakturPajakKeluaran    bigint;
    vDocTypeId              bigint;
    vPaymentAmount          numeric;
    vPaymentAmountTax       numeric;
    vDiffAdjRegularDiscAmount       numeric;
    vDiffAdjPromoDiscAmount         numeric;
    vMaxItemAmount          numeric;
    vRoundingModeNonTax     character varying(5);
    vSalesInvDate           character varying;
    vFlgPayment             character varying;
    vFlgPaymentTax          character varying;
    vScheme                 character varying;
    vDoDocTypeId            bigint;
    vFlagPayment    character varying(1);
    vYes                    character varying(1);
    vInProgress             character varying(1);
BEGIN

    vFakturPajakKeluaran := 281;
    vDoDocTypeId := 311;
    vFlagPayment := 'N';
    vYes := 'Y';
    vInProgress := 'I';
    /*
     * 1. cek data admin process ledger untuk ledger SALES di tahun bulan dan ou sesuai dokumen Sales Invoice terkait.
     *      Apabila tidak ada atau sudah tutup bulan, maka muncul exception dan berhenti prosesnya.
     * 2. cek saldo dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_ar_balance berdasarkan dokumen Sales Invoice terkait.
     *      Apabila tidak ada, maka muncul exception dan berhenti prosesnya.
     * 3. cek saldo tax dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_tax_ar_balance berdasarkan dokumen Sales Invoice terkait.
     *      Apabila tidak ada, maka muncul exception dan berhenti prosesnya.
     * 4. menjalankan function gl_cancel_admin_journal_trx
     * 5. Menghapus data gl_journal_trx_mapping, gl_journal_trx_item berdasarkan gl_journal_trx-nya
     * 6. menghapus gl_journal_trx berdasarkan journal_type, doc_type_id, doc_id, doc_no, doc_date, dan ou_id-nya.
     * 7. menjalankan function f_reset_approval_to_draft
     * 8. update t_process_message berdasarkan process_name, invoice_id, dan doc_no-nya
     * 9. update flg_invoice, invoice_id, update_datetime, update_user_id, version di sl_so_balance_advance_invoice berdasarkan invoice_id dan ref_idnya
     * 10. update flg_payment di fi_invoice_ar_balance berdasarkan invoice_ar_balance_id-nya
     * 11. update sales_invoice_id, sales_invoice_date, update_datetime, update_user_id, version di fi_invoice_tax_advance_ar_balance berdasarkan salesInvoiceId
     * 12. update sales_invoice_id, sales_invoice_date, update_datetime, update_user_id, version di fi_invoice_advance_ar_balance berdasarkan salesInvoiceId
     * 13. Menghapus data fi_vat_out_for_efaktur berdasarkan invoice_ar_balance_id-nya
     * 14. Menghapus fi_vat_out_for_reporting berdasarkan invoice_tax_ar_balance_id-nya
     * 15. Cek apakah temp sales invoicenya ada atau tidak.
     *      Apabila ada, insert ulang fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
     *      dengan query di function insert temp sales invoice-nya dan update flg_invoice,
     *      update_datetime, update_user_id, dan version di sl_do_inv berdasarkan do_id dan invoice_id-nya
     * 16. Menghapus fi_invoice_ar_balance_due_date berdasarkan invoice_ar_balance_id-nya
     * 17. Menghapus fi_invoice_tax_ar_balance_due_date berdasarkan invoice_ar_balance_id-nya
     * 18. Menghapus data fi_invoice_tax_ar_balance berdasarkan invoice_id-nya
     * 19. Menghapus data fi_invoice_ar_balance berdasarkan invoice_id-nya
     * 20. Update flg_invoice, update_datetime, dan update_user_id di sl_so_balance_invoice_tax
     * 21. Update adj_regular_disc_amount, adj_promo_disc_amount, update_datetime, update_user_id, dan version di sl_so_balance_invoice
     * 22. update adj_regular_disc_amount, update_datetime, update_user_id dan version di sl_so_balance_invoice;
     *      update adj_promo_disc_amount, update_datetime, update_user_id dan version di sl_so_balance_invoice
     *      berdasarkan rounding mode non tax-nya
     * 23. Update flg_invoice, update_datetime dan update_user_id sl_so_balance_invoice berdasarkan invoice_id-nya
     * 24. Update status_doc, update_datetime, update_user_id di sl_invoice berdasarkan sales invoice id-nya
     *
     */

    WITH activity_gl_regular_disc AS (
        SELECT A.activity_gl_code
        FROM m_activity_gl A
        WHERE A.activity_gl_code = 'REGULAR_DISC'
    )
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vRegularDiscount
    FROM activity_gl_regular_disc A;

    WITH activity_gl_promo_disc AS (
        SELECT A.activity_gl_code
        FROM m_activity_gl A
        WHERE A.activity_gl_code = 'PROMO_DISC'
    )
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vPromoDiscount
    FROM activity_gl_promo_disc A;

    SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, ref_inv_temp_id as salesInvTempId, A.doc_date
    FROM sl_invoice A
    WHERE A.invoice_id = pSalesInvoiceId INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vSalesInvoiceTempId := result.salesInvTempId;
    vSalesInvDate := result.doc_date;

    -- 1. cek data admin process ledger untuk ledger SALES di tahun bulan dan ou sesuai dokumen Sales Invoice terkait.
    --      Apabila tidak ada atau sudah tutup bulan, maka muncul exception dan berhenti prosesnya.
    SELECT A.ou_id, A.doc_type_id, A.doc_no, A.doc_date, A.status_doc, A.workflow_status, B.scheme
    INTO vOuId, vDocTypeId, vDocNo, vDocDate, vStatusDoc, vWorkflowStatus, vScheme
    FROM sl_invoice A, m_document B
    WHERE A.invoice_id = pSalesInvoiceId
    AND A.doc_type_id = B.doc_type_id;

    IF EXISTS (SELECT 1 FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
        WHERE e.tenant_id = pTenantId AND e.ou_id = f.ou_bu_id AND f.ou_id = vOuId AND
            e.date_year_month = SUBSTR(vDocDate, 1, 6) AND e.ledger_code = vSalesLedgerCode AND
            f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

        IF vStatusDoc = 'R' AND vWorkflowStatus = 'APPROVED' THEN

            -- 2. Sales Invoice yang hendak dibatalkan harus belum pernah dialokasi sama sekali
            SELECT A.invoice_ar_balance_id, A.payment_amount, A.flg_payment
            INTO vInvoiceArBalanceId, vPaymentAmount, vFlgPayment
            FROM fi_invoice_ar_balance A
            WHERE A.tenant_id = pTenantId
                AND A.doc_no = vDocNo
                AND A.doc_date = vDocDate
                AND A.ou_id = vOuId
                AND A.doc_type_id = vDocTypeId
                AND A.invoice_ar_id = pSalesInvoiceId;

            SELECT A.invoice_tax_ar_balance_id, A.payment_amount, A.flg_payment INTO vInvoiceTaxArBalanceId, vPaymentAmountTax, vFlgPaymentTax
            FROM fi_invoice_tax_ar_balance A
            WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId;


--          2. cek saldo dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_ar_balance berdasarkan dokumen Sales Invoice terkait.
--              Apabila tidak sesuai dengan kondisi tersebut, maka muncul exception dan berhenti prosesnya.
--          3. cek saldo tax dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_tax_ar_balance berdasarkan dokumen Sales Invoice terkait.
--              Apabila tidak sesuai dengan kondisi tersebut, maka muncul exception dan berhenti prosesnya.
            IF EXISTS (
                    SELECT 1
                    FROM fi_invoice_ar_balance A
                    WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId
                        AND (A.flg_payment IN ( vYes, vInProgress ) OR A.payment_amount <> 0) )
                OR

                EXISTS (
                    SELECT 1
                    FROM fi_invoice_tax_ar_balance A
                    INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
                    WHERE B.invoice_ar_balance_id = vInvoiceArBalanceId
                        AND (A.flg_payment IN ( vYes, vInProgress ) OR A.payment_amount <> 0) )
            THEN
                RAISE EXCEPTION 'Document is already used in allocation process, process aborted';
            ELSE

--              4. menjalankan function gl_cancel_admin_journal_trx
                PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'DAILY', pDatetime, pUserId);

--              5. Menghapus data gl_journal_trx_mapping, gl_journal_trx_item berdasarkan gl_journal_trx-nya

                SELECT journal_trx_id INTO vJournalTrxId
                FROM gl_journal_trx
                WHERE tenant_id = pTenantId
                    AND journal_type = (vDocJournal).journal_type
                    AND doc_type_id = vDocTypeId
                    AND doc_id = pSalesInvoiceId
                    AND doc_no = vDocNo
                    AND doc_date = vDocDate
                    AND ou_bu_id = (vOuStructure).ou_bu_id
                    AND ou_branch_id = (vOuStructure).ou_branch_id
                    AND ou_sub_bu_id = (vOuStructure).ou_sub_bu_id;

                DELETE FROM gl_journal_trx_item
                WHERE journal_trx_id = vJournalTrxId;

                DELETE FROM gl_journal_trx_mapping
                WHERE journal_trx_id = vJournalTrxId;

--              6. menghapus gl_journal_trx berdasarkan journal_type, doc_type_id, doc_id, doc_no, doc_date, dan ou_id-nya.

                DELETE FROM gl_journal_trx
                WHERE journal_trx_id = vJournalTrxId;

--              7. menjalankan function f_reset_approval_to_draft

                PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pSalesInvoiceId, vDocNo, pDatetime, pRemarkApproval);

--              8. update t_process_message berdasarkan process_name, invoice_id, dan doc_no-nya

                UPDATE t_process_message
                SET process_no = pPrevProcessNo,
                    update_datetime = pDatetime,
                    update_user_id = pUserId,
                    version = version - 1
                WHERE tenant_id = pTenantId
                    AND process_name = 'sl_submit_sales_invoice'
                    AND process_no = pSalesInvoiceId || '_' || vDocNo;

--              9. update flg_invoice, invoice_id, update_datetime, update_user_id, version di sl_so_balance_advance_invoice berdasarkan invoice_id dan ref_idnya

                UPDATE sl_so_balance_advance_invoice SET flg_invoice = 'I', invoice_id = -99,
                    update_datetime = pDatetime, update_user_id = pUserId,
                    version = sl_so_balance_advance_invoice.version - 1
                FROM sl_invoice_advance A
                WHERE A.invoice_id = pSalesInvoiceId AND
                    A.invoice_id = sl_so_balance_advance_invoice.invoice_id AND
                    A.ref_id = sl_so_balance_advance_invoice.ref_id;

--              10. update flg_payment di fi_invoice_ar_balance berdasarkan invoice_ar_balance_id-nya

                UPDATE fi_invoice_ar_balance A
                SET flg_payment = 'N'
                WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId;

--              11. update sales_invoice_id, sales_invoice_date, update_datetime, update_user_id, version di fi_invoice_tax_advance_ar_balance berdasarkan salesInvoiceId
--              12. update sales_invoice_id, sales_invoice_date, update_datetime, update_user_id, version di fi_invoice_advance_ar_balance berdasarkan salesInvoiceId
--              Nomor 11 dan 12 diabaikan dulu karena berhubungan dengan down payment

--              13. Menghapus data fi_vat_out_for_efaktur berdasarkan invoice_ar_balance_id-nya

                DELETE FROM fi_vat_out_for_efaktur
                WHERE doc_id = pSalesInvoiceId
                AND doc_type_id = vDocTypeId
                AND ou_id = vOuId
                AND tenant_id = pTenantId;

--              14. Menghapus fi_vat_out_for_reporting berdasarkan invoice_tax_ar_balance_id-nya

                DELETE FROM fi_vat_out_for_reporting
                WHERE ref_id = vInvoiceTaxArBalanceId;

--              15. Cek apakah temp sales invoicenya ada atau tidak.
--                  Apabila ada, insert ulang fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
--                  dengan query di function insert temp sales invoice-nya dan update flg_invoice,
--                  update_datetime, update_user_id, dan version di sl_do_inv berdasarkan do_id dan invoice_id-nya

                IF vSalesInvoiceTempId <> -99 THEN

                    SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceIdForTemp;

                    INSERT INTO fi_invoice_ar_balance
                    ( invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
                    doc_no, doc_date, ext_doc_no, ext_doc_date,
                    ref_doc_type_id, ref_id,  partner_id, due_date,
                    curr_code, amount, remark, payment_amount, flg_payment,
                    "version", create_datetime, create_user_id, update_datetime, update_user_id)
                    SELECT vInvoiceArBalanceIdForTemp, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_temp_id,
                            A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                            A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
                            A.curr_code, A.total_amount, A.remark, 0, vFlagPayment,
                            0, pDatetime, pUserId, pDatetime, pUserId
                    FROM sl_invoice_temp A
                    WHERE A.invoice_temp_id = vSalesInvoiceTempId;

                    INSERT INTO fi_invoice_tax_ar_balance
                    (tenant_id, ou_id, doc_type_id, invoice_ar_balance_id,
                    partner_id, tax_id, tax_no, tax_date,
                    curr_code, tax_amount, tax_curr_code, gov_tax_amount,
                    due_date, remark, payment_amount, flg_payment,
                    "version", create_datetime, create_user_id, update_datetime, update_user_id)
                    SELECT A.tenant_id, A.ou_id, vFakturPajakKeluaran, C.invoice_ar_balance_id,
                        A.partner_id, B.tax_id, B.tax_no, B.tax_date,
                        A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
                        A.due_date, B.remark, 0, vFlagPayment,
                        0, pDatetime, pUserId, pDatetime, pUserId
                    FROM sl_invoice_temp A, sl_invoice_temp_tax B, fi_invoice_ar_balance C
                    WHERE A.invoice_temp_id = vSalesInvoiceTempId AND
                        A.invoice_temp_id = B.invoice_temp_id AND
                        A.tenant_id = C.tenant_id AND
                        A.doc_type_id = C.doc_type_id AND
                        A.invoice_temp_id = C.invoice_ar_id;

                    PERFORM fi_insert_invoice_ar_balance_due_date(pTenantId, pSessionId, vInvoiceArBalanceIdForTemp, pUserId, pDatetime);

                    UPDATE sl_do_inv SET flg_invoice = 'N',
                        update_datetime = pDatetime, update_user_id = pUserId, version = sl_do_inv.version - 1
                    FROM sl_invoice_item A
                    WHERE A.invoice_id = pSalesInvoiceId AND
                        A.ref_id = sl_do_inv.do_id;

                END IF;

--              16. Menghapus fi_invoice_ar_balance_due_date berdasarkan invoice_ar_balance_id-nya

                DELETE FROM fi_invoice_ar_balance_due_date WHERE invoice_ar_balance_id = vInvoiceArBalanceId;

--              17. Menghapus fi_invoice_tax_ar_balance_due_date berdasarkan invoice_ar_balance_id-nya

                DELETE FROM fi_invoice_tax_ar_balance_due_date WHERE invoice_tax_ar_balance_id = vInvoiceTaxArBalanceId;

--              18. Menghapus data fi_invoice_tax_ar_balance berdasarkan invoice_id-nya
                DELETE FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvoiceArBalanceId;

--              19. Menghapus data fi_invoice_ar_balance berdasarkan invoice_id-nya
                DELETE FROM fi_invoice_tax_ar_balance WHERE invoice_ar_balance_id = vInvoiceArBalanceId;

--              20. Update flg_invoice, update_datetime, dan update_user_id di sl_so_balance_invoice_tax
                UPDATE sl_so_balance_invoice_tax A SET flg_invoice = 'I', update_datetime = pDatetime, update_user_id = pUserId
                FROM sl_invoice B, sl_invoice_item C
                WHERE B.invoice_id = pSalesInvoiceId AND
                    B.invoice_id = C.invoice_id AND
                    A.ou_id = B.ou_id AND
                    A.so_id = B.ref_id AND
                    A.ref_doc_type_id = vDoDocTypeId AND
                    A.ref_id = C.ref_id AND
                    A.ref_item_id = C.ref_item_id AND
                    A.do_receipt_item_id = C.do_receipt_item_id;

--              21. Update adj_regular_disc_amount, adj_promo_disc_amount, update_datetime, update_user_id, dan version di sl_so_balance_invoice
--              22. update adj_regular_disc_amount, update_datetime, update_user_id dan version di sl_so_balance_invoice;
--                      update adj_promo_disc_amount, update_datetime, update_user_id dan version di sl_so_balance_invoice
--                      berdasarkan rounding mode non tax-nya
                SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

                IF vRoundingModeNonTax = 'RD' THEN

                    -- Update adj_regular_disc_amount
                    WITH total_regular_disc_amount AS (
                        SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
                        FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                        WHERE B.invoice_id = pSalesInvoiceId AND
                            B.invoice_id = C.invoice_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id AND
                            D.invoice_id = B.invoice_id AND
                            D.activity_gl_id = vRegularDiscount
                        GROUP BY D.add_amount
                    )
                    UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = 0,
                        update_datetime = pDatetime, update_user_id = pUserId, version = A.version - 1
                    FROM total_regular_disc_amount B
                    WHERE A.invoice_id = pSalesInvoiceId AND B.sum_regular_disc_amount > 0;

                    -- Update adj_promo_disc_amount
                    WITH total_promo_disc_amount AS (
                        SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
                        FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                        WHERE B.invoice_id = pSalesInvoiceId AND
                            B.invoice_id = C.invoice_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id AND
                            D.invoice_id = B.invoice_id AND
                            D.activity_gl_id = vPromoDiscount
                        GROUP BY D.add_amount
                    )
                    UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = 0,
                        update_datetime = pDatetime, update_user_id = pUserId, version = A.version - 1
                    FROM total_promo_disc_amount B
                    WHERE A.invoice_id = pSalesInvoiceId AND B.sum_promo_disc_amount > 0;

                ELSE

                    -- Update adj_regular_disc_amount
                    WITH total_regular_disc_amount AS (
                        SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
                        FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                        WHERE B.invoice_id = pSalesInvoiceId AND
                            B.invoice_id = C.invoice_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id AND
                            D.invoice_id = B.invoice_id AND
                            D.activity_gl_id = vRegularDiscount
                        GROUP BY D.add_amount
                    )
                    UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = 0,
                        update_datetime = pDatetime, update_user_id = pUserId, version = A.version - 1
                    FROM total_regular_disc_amount B
                    WHERE A.invoice_id = pSalesInvoiceId AND B.sum_regular_disc_amount > 0;

                    -- Update adj_promo_disc_amount
                    WITH total_promo_disc_amount AS (
                        SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
                        FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                        WHERE B.invoice_id = pSalesInvoiceId AND
                            B.invoice_id = C.invoice_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id AND
                            D.invoice_id = B.invoice_id AND
                            D.activity_gl_id = vPromoDiscount
                        GROUP BY D.add_amount
                    )
                    UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = 0,
                        update_datetime = pDatetime, update_user_id = pUserId, version = A.version - 1
                    FROM total_promo_disc_amount B
                    WHERE A.invoice_id = pSalesInvoiceId AND B.sum_promo_disc_amount > 0;

                END IF;

                -- cari sisa dari hasil pembulatan adj_regular_disc_amount
                SELECT (D.add_amount * -1) - SUM(A.regular_disc_amount) - SUM(A.adj_regular_disc_amount) AS diff_adj_regular_disc_amount,
                        MAX(A.item_amount) AS max_item_amount INTO vDiffAdjRegularDiscAmount, vMaxItemAmount
                FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                WHERE B.invoice_id = pSalesInvoiceId AND
                    B.invoice_id = C.invoice_id AND
                    A.ou_id = B.ou_id AND
                    A.so_id = B.ref_id AND
                    A.ref_doc_type_id = vDoDocTypeId AND
                    A.ref_id = C.ref_id AND
                    A.ref_item_id = C.ref_item_id AND
                    A.do_receipt_item_id = C.do_receipt_item_id AND
                    D.invoice_id = B.invoice_id AND
                    D.activity_gl_id = vRegularDiscount
                GROUP BY D.add_amount;

                -- cari sisa dari hasil pembulatan adj_promo_disc_amount
                SELECT (D.add_amount * -1) - SUM(A.promo_disc_amount) - SUM(A.adj_promo_disc_amount) AS diff_adj_promo_disc_amount INTO vDiffAdjPromoDiscAmount
                FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
                WHERE B.invoice_id = pSalesInvoiceId AND
                    B.invoice_id = C.invoice_id AND
                    A.ou_id = B.ou_id AND
                    A.so_id = B.ref_id AND
                    A.ref_doc_type_id = vDoDocTypeId AND
                    A.ref_id = C.ref_id AND
                    A.ref_item_id = C.ref_item_id AND
                    A.do_receipt_item_id = C.do_receipt_item_id AND
                    D.invoice_id = B.invoice_id AND
                    D.activity_gl_id = vPromoDiscount
                GROUP BY D.add_amount;

                UPDATE sl_so_balance_invoice A SET update_datetime = pDatetime, update_user_id = pUserId, version = A.version - 1
                FROM (SELECT so_balance_invoice_id FROM sl_so_balance_invoice WHERE invoice_id = pSalesInvoiceId AND item_amount = vMaxItemAmount LIMIT 1) B
                WHERE A.so_balance_invoice_id = B.so_balance_invoice_id;

                UPDATE sl_so_balance_invoice_tax A SET flg_invoice = 'I', update_datetime = pDatetime, update_user_id = pUserId
                FROM sl_invoice B, sl_invoice_item C
                WHERE B.invoice_id = pSalesInvoiceId AND
                    B.invoice_id = C.invoice_id AND
                    A.ou_id = B.ou_id AND
                    A.so_id = B.ref_id AND
                    A.ref_doc_type_id = vDoDocTypeId AND
                    A.ref_id = C.ref_id AND
                    A.ref_item_id = C.ref_item_id AND
                    A.do_receipt_item_id = C.do_receipt_item_id;

--              23. Update flg_invoice, update_datetime dan update_user_id sl_so_balance_invoice berdasarkan invoice_id-nya
                UPDATE sl_so_balance_invoice A SET flg_invoice = 'I', update_datetime = pDatetime, update_user_id = pUserId
                FROM sl_invoice B, sl_invoice_item C
                WHERE B.invoice_id = pSalesInvoiceId AND
                    B.invoice_id = C.invoice_id AND
                    A.ou_id = B.ou_id AND
                    A.so_id = B.ref_id AND
                    A.ref_doc_type_id = vDoDocTypeId AND
                    A.ref_id = C.ref_id AND
                    A.ref_item_id = C.ref_item_id AND
                    A.do_receipt_item_id = C.do_receipt_item_id;

--              24. Update status_doc, update_datetime, update_user_id di sl_invoice berdasarkan sales invoice id-nya
                UPDATE sl_invoice SET workflow_status = 'DRAFT', status_doc = 'D', update_datetime = pDatetime, update_user_id = pUserId
                WHERE invoice_id = pSalesInvoiceId;
            END IF;
        ELSE
            RAISE EXCEPTION 'Document is not approved yet, process aborted';
        END IF;

    ELSE
            RAISE EXCEPTION 'Admin Process Ledger for Sales in year month % is already closed', SUBSTR(vDocDate, 1, 6);
    END IF;



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