CANCEL SUBMIT DO RECEIPT

CREATE OR REPLACE FUNCTION in_cancel_submit_do_receipt(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;
    pDoReceiptId                        ALIAS FOR $5;
    pRemarkApproval                     ALIAS FOR $6;
    pPrevProcessNo                      ALIAS FOR $7;

    vDoReceiptDocTypeId                 bigint;
    vEmptyId                            bigint;
    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying;
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vStatusDocFinal                     character varying;
    vStatusDocVoid                      character varying;
    vWorkflowStatusDraft                character varying;
    vWorkflowStatusApproved             character varying;
    vYes                                character varying;
    vNo                                 character varying;
    vZero                               bigint;
    vEmptyValue                         character varying(1);

    vOuId                               bigint;
    vJournalTrxId                       bigint;
    vDocNo                              character varying;
    vDocDate                            character varying;
    vScheme                             character varying;
    vSalesOrderId                       bigint;
    vDeliveryOrderId                    bigint;
    vSalesInvoiceTempId                 bigint;
    VWarehouseOuId                      bigint;

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

    vLedgerCode                         character varying;
    vStatusLedgerNotDone                character varying;

    vOuStructureJournalItem             OU_BU_STRUCTURE%ROWTYPE;

    vDeliveryOrderDocTypeId             bigint;
    vSalesOrderDocTypeId                bigint;
    vSalesInvoiceTempDocTypeId          bigint;

    vInvoiceArBalanceId                 bigint;
    vJointDppPpn                        character varying(1);
    vFakturPajakKeluaran                bigint;

BEGIN

    vDoReceiptDocTypeId := 526;
    vEmptyId := -99;
    vFunctionSubmit := 'in_submit_do_receipt';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress := 'I';
    vStatusDocFinal := 'F';
    vStatusDocVoid := 'V';
    vWorkflowStatusDraft := 'DRAFT';
    vWorkflowStatusApproved := 'APPROVED';
    vYes := 'Y';
    vNo := 'N';
    vZero := 0;

    vLedgerCode := 'INV';
    vStatusLedgerNotDone := '0';

    vDeliveryOrderDocTypeId := 311;
    vSalesOrderDocTypeId := 301;
    vSalesInvoiceTempDocTypeId := 361;

    vEmptyValue := ' ';
    vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
    vFakturPajakKeluaran := 281;

    --RAISE EXCEPTION 'Function Submit For Document DO Receipt';

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou, A.ou_id, A.doc_no, A.doc_date,
            f_get_document_journal(A.doc_type_id) as doc, C.scheme,
            A.ref_id AS do_id,
            D.ou_id AS warehouse_ou_id
    FROM in_do_receipt A, in_do_receipt_item B, m_document C, m_warehouse_ou D
    WHERE A.do_receipt_id = B.do_receipt_id AND
        A.doc_type_id = C.doc_type_id AND
        A.warehouse_id = D.warehouse_id AND
        A.do_receipt_id = pDoReceiptId AND
        A.doc_type_id = vDoReceiptDocTypeId
        AND A.status_doc = vStatusDocReleased INTO result;

    IF FOUND THEN
        vOuStructure := result.ou;
        vOuId := result.ou_id;
        vDocNo := result.doc_no;
        vDocDate := result.doc_date;
        vDocJournal := result.doc;
        vScheme := result.scheme;
        vDeliveryOrderId := result.do_id;
        VWarehouseOuId := result.warehouse_ou_id;

        -- Cek status ledger not yet closed
        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 = vLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

            /*
             * validasi belum digunakan untuk invoice
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM sl_so_balance_invoice A, in_do_receipt_item B, in_do_receipt C, in_balance_do_item D, sl_so_item E, sl_so F
                WHERE C.do_receipt_id = pDoReceiptId
                    AND B.do_receipt_id = C.do_receipt_id
                    AND B.ref_id = D.do_item_id
                    AND D.so_item_id = E.so_item_id
                    AND E.so_id = F.so_id
                    AND B.qty_return > 0
                    AND A.tenant_id = C.tenant_id
                    AND A.ou_id = C.ou_id
                    AND A.partner_id = F.partner_bill_to_id
                    AND A.ref_doc_type_id = C.ref_doc_type_id
                    AND A.ref_id = C.ref_id
                    AND A.ref_item_id = B.ref_id
                    AND A.flg_invoice <> vNo
                    AND A.invoice_id <> vEmptyId
                ) THEN

                /*
                 * validasi tax belum digunakan untuk invoice
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM sl_so_balance_invoice_tax A, in_do_receipt_item B, in_do_receipt C, in_balance_do_item D, sl_so_item E, sl_so F
                    WHERE C.do_receipt_id = pDoReceiptId
                        AND B.do_receipt_id = C.do_receipt_id
                        AND B.ref_id = D.do_item_id
                        AND D.so_item_id = E.so_item_id
                        AND E.so_id = F.so_id
                        AND B.qty_return > 0
                        AND A.tenant_id = C.tenant_id
                        AND A.ou_id = C.ou_id
                        AND A.partner_id = F.partner_bill_to_id
                        AND A.ref_doc_type_id = C.ref_doc_type_id
                        AND A.ref_id = C.ref_id
                        AND A.ref_item_id = B.ref_id
                        AND A.tax_id = E.tax_id
                        AND A.flg_invoice <> vNo
                        AND A.invoice_id <> vEmptyId
                    ) THEN

                    /*
                     * 1. update status doc in_do_receipt
                     * 2. add sl_log_so_balance_item
                     * 3. add sl_so_balance_invoice
                     * 4. add sl_so_balance_invoice_tax
                     * 5. update sl_so_balance_invoice untuk Do Id
                     * 6. update sl_so_balance_invoice_tax untuk Do Id
                     * 7. void sales invoice temporer untuk So Id
                     * 8. delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
                     * 9. add in_log_product_balance_stock
                     * 10. update in_product_balance_stock
                     * 11. update in_balance_do_item
                     * 12. update sl_so_balance_item - status dan qty
                     * 13. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
                     * 14. add gl_journal_trx
                     * 15. add gl_journal_trx_item
                     * 16. add gl_journal_trx_mapping
                     *
                     */

                    SELECT ref_id INTO vSalesOrderId
                    FROM sl_do
                    WHERE do_id = vDeliveryOrderId;

                    /*
                     * update status doc in_do_receipt
                     */
                    UPDATE in_do_receipt SET status_doc = vStatusDocDraft, workflow_status = vWorkflowStatusDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
                    WHERE do_receipt_id = pDoReceiptId
                        AND status_doc = vStatusDocReleased;

                    /*
                     * hapus data log sl_log_so_balance_item
                     */
                    DELETE
                    FROM sl_log_so_balance_item Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM in_do_receipt_item A
                        INNER JOIN in_do_receipt B ON B.do_receipt_id = A.do_receipt_id
                        WHERE A.do_receipt_id = pDoReceiptId
                            AND Z.ref_id = A.do_receipt_id
                            AND Z.ref_doc_type_id = B.doc_type_id
                            AND Z.ref_item_id = A.do_receipt_item_id
                            AND A.qty_return > 0
                    );

                    /*
                     * hapus data sl_so_balance_Invoice
                     */
                    DELETE
                    FROM sl_so_balance_invoice Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM in_do_receipt A
                        INNER JOIN in_do_receipt_item B ON B.do_receipt_id = A.do_receipt_id
                        INNER JOIN in_balance_do_item C ON C.do_item_id = B.ref_id
                        INNER JOIN sl_so_item D ON D.so_item_id = C.so_item_id
                        INNER JOIN sl_so E ON E.so_id = D.so_id
                        WHERE A.do_receipt_id = pDoReceiptId
                            AND Z.tenant_id = A.tenant_id
                            AND Z.ou_id = A.ou_id
                            AND Z.partner_id = E.partner_bill_to_id
                            AND Z.ref_doc_type_id = A.ref_doc_type_id
                            AND Z.ref_id = A.ref_id
                            AND Z.ref_item_id = B.ref_id
                            AND Z.flg_invoice = vNo
                            AND Z.invoice_id = vEmptyId
                    );

                    /*
                     * hapus data sl_so_balance_Invoice_tax
                     */
                    DELETE
                    FROM sl_so_balance_invoice_tax Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM in_do_receipt A
                        INNER JOIN in_do_receipt_item B ON B.do_receipt_id = A.do_receipt_id
                        INNER JOIN in_balance_do_item C ON C.do_item_id = B.ref_id
                        INNER JOIN sl_so_item D ON D.so_item_id = C.so_item_id
                        INNER JOIN sl_so E ON E.so_id = D.so_id
                        WHERE A.do_receipt_id = pDoReceiptId
                            AND Z.tenant_id = A.tenant_id
                            AND Z.ou_id = A.ou_id
                            AND Z.partner_id = E.partner_bill_to_id
                            AND Z.ref_doc_type_id = A.ref_doc_type_id
                            AND Z.ref_id = A.ref_id
                            AND Z.ref_item_id = B.ref_id
                            AND Z.tax_id = D.tax_id
                            AND Z.flg_invoice = vNo
                            AND Z.invoice_id = vEmptyId
                    );


                    /*************** INV TEMP ***************/

                    /*
                     * update sl_so_balance_invoice, dan sl_so_balance_invoice_tax
                     */
                    UPDATE sl_so_balance_invoice SET flg_invoice = vStatusDocInProgress, flg_invoice_temp = vNo, invoice_id = vEmptyId
                    WHERE ref_id = vDeliveryOrderId AND
                        ref_doc_type_id = vDoReceiptDocTypeId;

                    UPDATE sl_so_balance_invoice_tax SET flg_invoice = vNo, invoice_id = vEmptyId
                    WHERE ref_id = vDeliveryOrderId AND
                        ref_doc_type_id = vDoReceiptDocTypeId;

                    /*
                     * update status data sales invoice temporer yang menggunakan DO Item id yang sama dng Do Receipt :
                     * 1. cari invoice temp id yang memiliki data DO Item yang sama dengan DO Receipt
                     * 2. update status doc invoice temp sesuai id yang diperoleh di langkah ke 1
                     */
                    SELECT A.invoice_temp_id INTO vSalesInvoiceTempId
                    FROM sl_invoice_temp_item A, in_do_receipt_item B, in_do_receipt C, sl_invoice_temp D
                    WHERE A.ref_doc_type_id = B.ref_doc_type_id AND
                        A.ref_id = vDeliveryOrderId AND
                        A.ref_item_id = B.ref_id AND
                        B.do_receipt_id = C.do_receipt_id AND
                        C.ref_id = A.ref_id AND
                        B.do_receipt_id = pDoReceiptId AND
                        A.invoice_temp_id = D.invoice_temp_id AND
                        D.status_doc = vStatusDocVoid;

                    IF FOUND THEN

                        UPDATE sl_invoice_temp SET status_doc = vStatusDocReleased, update_datetime = pDatetime, update_user_id = pUserId
                        WHERE invoice_temp_id = vSalesInvoiceTempId
                            AND status_doc = vWorkflowStatusApproved;

                        /*
                         * update sl_so_balance_invoice
                         */
                        UPDATE sl_so_balance_invoice SET flg_invoice_temp = vYes
                        WHERE ref_id = vDeliveryOrderId AND
                            ref_doc_type_id = vDoReceiptDocTypeId;

                        /*
                         * update advance invoice
                         */
                        UPDATE sl_so_balance_advance_invoice B SET flg_invoice = vNo, flg_invoice_temp = vYes, invoice_id = vEmptyId
                        WHERE B.so_id = vSalesOrderId
                        AND EXISTS (
                            SELECT 1 FROM sl_invoice_temp_advance A
                            WHERE A.ref_id = B.ref_id
                            AND A.ref_doc_type_id = B.ref_doc_type_id
                            AND A.invoice_temp_id = vSalesInvoiceTempId
                        );

                        /*
                         * update nomor faktur pajak
                         */
                        UPDATE m_gen_tax_number A
                        SET
                        invoice_id = vSalesInvoiceTempId, invoice_doc_type_id = B.doc_type_id, invoice_doc_no = B.inv_doc_no, invoice_doc_date = B.inv_doc_date
                        FROM sl_invoice_temp B
                        WHERE A.invoice_doc_no = vEmptyValue
                            AND A.invoice_doc_date = vEmptyValue
                            AND A.invoice_doc_type_id = vEmptyId
                            AND A.invoice_id = vEmptyId
                            AND B.invoice_temp_id = vSalesInvoiceTempId;

                        SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;

                        IF vJointDppPpn = 'N' THEN
                            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 vInvoiceArBalanceId, 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, vDatetime, vUserId, vDatetime, vUserId
                            FROM sl_invoice_temp A
                            WHERE A.invoice_temp_id = vSalesInvoiceTempId;

                            UPDATE fi_invoice_ar_balance A
                            SET flg_payment = vYes
                            WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId
                                AND A.amount = 0;

                            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, vNo,
                                0, vDatetime, vUserId, vDatetime, vUserId
                            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;
                        ELSE
                            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 vInvoiceArBalanceId, 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 + COALESCE(B.gov_tax_amount, 0), A.remark, 0, vNo,
                                    0, vDatetime, vUserId, vDatetime, vUserId
                            FROM sl_invoice_temp A
                            LEFT OUTER JOIN sl_invoice_temp_tax B ON A.invoice_temp_id = B.invoice_temp_id
                            WHERE A.invoice_temp_id = vSalesInvoiceTempId;

                            UPDATE fi_invoice_ar_balance A
                            SET flg_payment = vYes
                            WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId
                                AND A.amount = 0;
                        END IF;

                        PERFORM fi_insert_invoice_ar_balance_due_date(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);

                    END IF;

                    /************** INV TEMP ***********************/

                    /*
                     * hapus data log product balance stock
                     * ref item id = do_receipt_id
                     */
                    DELETE
                    FROM in_log_product_balance_stock Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM in_do_receipt A
                        INNER JOIN in_do_receipt_item B ON B.do_receipt_id = A.do_receipt_id
                        INNER JOIN in_do_receipt_product C ON C.do_receipt_item_id = B.do_receipt_item_id
                        WHERE A.do_receipt_id = pDoReceiptId
                            AND Z.tenant_id = A.tenant_id
                            AND Z.ou_id = A.ou_id
                            AND Z.doc_type_id = A.doc_type_id
                            AND Z.ref_id = A.do_receipt_id
                            AND Z.doc_date = A.doc_date
                            AND Z.warehouse_id = A.warehouse_id
                            AND Z.product_id = C.product_id
                            AND Z.product_balance_id = C.product_balance_id
                            AND Z.product_status = C.product_status
                    );

                    /*
                     * update in_product_balance_stock
                     */
                    WITH tt_in_product_balance_summary AS (
                        SELECT  C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status, SUM(C.qty_return) AS qty_return
                        FROM    in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C
                        WHERE   A.do_receipt_id = pDoReceiptId
                                AND A.do_receipt_id = B.do_receipt_id
                                AND B.do_receipt_item_id = C.do_receipt_item_id
                                AND C.qty_return > 0
                        GROUP BY C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status
                    )
                    UPDATE in_product_balance_stock SET qty = qty - A.qty_return, update_datetime = pDatetime, update_user_id = pUserId
                    FROM tt_in_product_balance_summary A
                    WHERE in_product_balance_stock.product_id = A.product_id AND
                        in_product_balance_stock.tenant_id = A.tenant_id AND
                        in_product_balance_stock.warehouse_id = A.warehouse_id AND
                        in_product_balance_stock.product_balance_id = A.product_balance_id AND
                        in_product_balance_stock.product_status = A.product_status;

                    /*
                     * update data balance do item
                     */
                    UPDATE in_balance_do_item SET qty_dlv_int = qty_dlv_int + A.qty_return,
                                                qty_dlv = in_balance_do_item.qty_dlv + (A.qty_return * in_balance_do_item.qty_dlv / (qty_dlv_int + A.qty_return)),
                                                status_item = vStatusDocInProgress, update_datetime = pDatetime, update_user_id = pUserId
                    FROM in_do_receipt_item A
                    WHERE A.do_receipt_id = pDoReceiptId AND
                        in_balance_do_item.do_item_id = A.ref_id AND
                        A.qty_return > 0;

                    UPDATE in_balance_do_item
                    SET status_item = vStatusDocInProgress,
                        update_datetime = pDatetime,
                        update_user_id = pUserId
                    FROM in_do_receipt A
                    WHERE A.do_receipt_id = pDoReceiptId AND
                        in_balance_do_item.do_id = A.ref_id AND
                        A.ref_doc_type_id = vDeliveryOrderDocTypeId;

                    /*
                     * update status SO
                     */
                    UPDATE sl_so
                    SET status_doc = vStatusDocInProgress,
                        flg_old_status = status_doc,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = version + 1
                    WHERE so_id = vSalesOrderId;

                    /*
                     * update flg invoice so balance inv
                     */
                    UPDATE sl_so_balance_invoice
                    SET flg_invoice = vStatusDocInProgress,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = version + 1
                    WHERE so_id = vSalesOrderId
                        AND ref_id = vDeliveryOrderId;

                    /*
                     * update status so balance item
                     */
                    UPDATE sl_so_balance_item B
                    SET status_item = vStatusDocInProgress,
                        flg_old_status = status_doc,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = B.version + 1
                    FROM sl_so A
                    INNER JOIN sl_so_item C ON A.so_id = C.so_id
                    INNER JOIN sl_do_item D ON D.ref_id = C.so_item_id
                    WHERE B.so_item_id = C.so_item_id
                        AND A.so_id = vSalesOrderId
                        AND D.do_id = vDeliveryOrderId;

                    UPDATE sl_so_balance_item SET qty_dlv = sl_so_balance_item.qty_dlv + (A.qty_return * sl_so_balance_item.qty_so / sl_so_balance_item.qty_so_int),
                                                qty_dlv_int = sl_so_balance_item.qty_dlv_int + A.qty_return,
                                                status_item = vStatusDocInProgress
                    FROM in_do_receipt_item A, in_balance_do_item B
                    WHERE A.do_receipt_id = pDoReceiptId AND
                        A.ref_id = B.do_item_id AND
                        sl_so_balance_item.so_item_id = B.so_item_id AND
                        A.qty_return > 0;

                    UPDATE sl_so SET status_doc = vStatusDocInProgress
                    WHERE so_id = vSalesOrderId;

                    IF (vOuId <> vWarehouseOuId) THEN
                        SELECT f_get_ou_bu_structure(vWarehouseOuId) as ou_structure INTO result;
                        vOuStructureJournalItem := result.ou_structure;
                    ELSE
                        vOuStructureJournalItem := ROW(-99, -99, -99);
                    END IF;

                    -- PERFORM cancel 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), 'MONTHLY', pDatetime, pUserId);

                    -- Find journal trx id
                    SELECT journal_trx_id INTO vJournalTrxId
                    FROM gl_journal_trx
                    WHERE tenant_id = pTenantId
                        AND journal_type = (vDocJournal).journal_type
                        AND doc_type_id = vDoReceiptDocTypeId
                        AND doc_id = pDoReceiptId
                        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 gl_journal_trx
                    DELETE FROM gl_journal_trx
                    WHERE journal_trx_id = vJournalTrxId;

                    -- DELETE gl_journal_trx_item
                    DELETE FROM gl_journal_trx_item
                    WHERE journal_trx_id = vJournalTrxId;

                    -- DELETE gl_journal_trx_mapping
                    DELETE FROM gl_journal_trx_mapping
                    WHERE journal_trx_id = vJournalTrxId;

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

                    -- UPDATE t_process_message
                    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 = vFunctionSubmit
                        AND process_no = pDoReceiptId || '_' || vDocNo;

                ELSE
                    RAISE EXCEPTION 'Document with id % is aready used for invoice', pDoReceiptId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Document with id % is aready used for invoice', pDoReceiptId;
            END IF;

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

    ELSE
        RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pDoReceiptId;
    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;