CANCEL SUBMIT MODIFY ITEM SO

-- Adrian, Mar 2, 2017

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

    vEmptyId                            bigint;
    vManageSoItemDocTypeId              bigint;
    vFlagNo                             character varying(1);
    vFlagYes                            character varying(1);
    vRoundingModeNonTax                 character varying(5);
    vStatusRelease                      character varying(1);
    vOuStructure                        OU_BU_STRUCTURE%ROWTYPE;
    RESULT                              RECORD;

    vFunctionSubmit                     character varying;
    vStatusDraft                        character varying;
    vStatusInProgress                   character varying;
    vStatusFinal                        character varying;
    vStatusVoid                     character varying;
    vWorkflowStatusDraft                character varying;
    vSoId                               bigint;
    vOldSoValue                         json;
    vZero                               bigint;
    vTypeDataItem                       character varying;

    vDoDocDate                          character varying;
    vDoReceiptDocDate                   character varying;
    vOuId                               bigint;
    vLedgerCode                         character varying;
    vStatusLedgerNotDone                character varying;
    vEmpty                              character varying;
    vScheme                             character varying;
    vDocNo                              character varying;

    vDeliveryOrderDocTypeId             bigint;
    vDoReceiptDocTypeId                 bigint;

BEGIN

    vEmptyId := -99;
    vStatusRelease := 'R';
    vFlagNo := 'N';
    vFlagYes := 'Y';
    vManageSoItemDocTypeId := 305;

    vFunctionSubmit := 'sl_submit_modify_item_so';
    vStatusDraft := 'D';
    vStatusInProgress = 'I';
    vStatusFinal := 'F';
    vStatusVoid = 'V';
    vWorkflowStatusDraft := 'DRAFT';
    vZero := 0;
    vTypeDataItem := 'item';

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

    vEmpty := '';
    vDeliveryOrderDocTypeId := 311;
    vDoReceiptDocTypeId := 525;

    --RAISE EXCEPTION 'Function Submit For Document Modify Item SO';

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou,
        A.ou_id, A.doc_date,
        COALESCE(C.doc_date, '') AS do_doc_date, COALESCE(D.doc_date, '') AS do_receipt_doc_date,
        A.doc_no, E.scheme
    FROM sl_manage_so A
    LEFT JOIN sl_do C ON C.ref_id = A.so_id
    LEFT JOIN in_do_receipt D ON D.ref_id = C.do_id
    INNER JOIN m_document E ON E.doc_type_id = A.doc_type_id
    WHERE A.manage_so_id = pManageSoId AND
        A.doc_type_id = vManageSoItemDocTypeId AND
        A.status_doc = vStatusRelease INTO RESULT;

    IF FOUND THEN
        vOuStructure := RESULT.ou;
        vOuId := RESULT.ou_id;
        vDoDocDate := RESULT.do_doc_date;
        vDoReceiptDocDate := RESULT.do_receipt_doc_date;
        vScheme := RESULT.scheme;
        vDocNo := RESULT.doc_no;

        SELECT A.so_id INTO vSoId FROM sl_manage_so A WHERE A.manage_so_id = pManageSoId;

        -- Cek status ledger not yet closed for DO
        IF ((vDoDocDate = vEmpty) OR 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(vDoDocDate, 1, 6)
                AND e.ledger_code = vLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone
            )) THEN

            -- Cek status ledger not yet closed for DO Receipt
            IF ((vDoReceiptDocDate = vEmpty) OR 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(vDoReceiptDocDate, 1, 6)
                    AND e.ledger_code = vLedgerCode AND
                    f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone
                )) THEN

                /*
                 * validasi so tidak sedang digunakan
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM sl_so A, sl_manage_so C
                    WHERE C.manage_so_id = pManageSoId
                        AND A.so_id = C.so_id
                        AND (
                            (A.status_doc <> vStatusRelease)
                            AND (A.status_doc <> vStatusFinal)
                        )
                    ) THEN

                    /**
                     * validasi tidak sedang digunakan oleh PO
                     */
                    IF NOT EXISTS (
                        SELECT (1)
                        FROM sl_so_po_balance_item A, sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
                        WHERE A.so_item_id = B.so_item_id
                            AND B.manage_so_id = C.manage_so_id
                            AND A.so_item_id = D.so_item_id
                            AND B.manage_so_id = pManageSoId
                            AND (
                                (A.status_item <> vStatusRelease)
                                AND (A.status_item <> vStatusFinal)
                            )
                    ) THEN

                        /**
                         * validasi tidak sedang digunakan oleh dokumen lain
                         */
                        IF NOT EXISTS (
                            SELECT (1)
                            FROM sl_so_balance_item A, sl_manage_so_item B, sl_manage_so C
                            WHERE A.so_item_id = B.so_item_id
                                AND B.manage_so_id = C.manage_so_id
                                AND B.manage_so_id = pManageSoId
                                AND (
                                    (A.status_item <> vStatusRelease)
                                    AND (A.status_item <> vStatusFinal)
                                )
                        ) THEN

                            /**
                             * validasi tidak sedang digunakan oleh invoice
                             */
                            IF NOT EXISTS (
                                SELECT (1)
                                FROM sl_so_balance_invoice A, sl_manage_so B
                                WHERE A.so_id = B.so_id
                                    AND B.manage_so_id = pManageSoId
                                    AND (
                                        (A.flg_invoice <> vFlagNo)
                                        AND (A.flg_invoice <> vStatusVoid)
                                    )
                            ) THEN

                                DELETE FROM tt_sl_so_tax_for_modify_so WHERE session_id = pSessionId;

                                --SELECT A.so_id INTO vSoId FROM sl_manage_so A WHERE A.manage_so_id = pManageSoId;

                                UPDATE sl_so SET update_datetime = pDatetime, update_user_id = pUserId, version = sl_so.version + 1, status_doc = vStatusInProgress
                                WHERE sl_so.so_id = vSoId
                                    AND (
                                            (sl_so.status_doc = vStatusRelease)
                                            OR (sl_so.status_doc = vStatusFinal)
                                        );

                                -- update so item untuk item lama
                                UPDATE sl_so_item
                                SET update_datetime = pDatetime,
                                    update_user_id = pUserId,
                                    version = sl_so_item.version + 1,
                                    gross_sell_price = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'grossSellPrice' AS numeric),
                                    flg_tax_amount = (A.old_data_value::json->'salesOrderItemDto')::json->>'flgTaxAmount',
                                    tax_id = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'taxId' AS bigint),
                                    tax_percentage = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'taxPercentage' AS numeric(5,2)),
                                    tax_price = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'taxPrice' AS numeric),
                                    nett_sell_price = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'nettSellPrice' AS numeric),
                                    qty_so = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'qtySo' AS numeric),
                                    qty_int = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'qtyInt' AS numeric),
                                    nett_item_amount = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'nettItemAmount' AS numeric),
                                    tax_amount = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'taxAmount' AS numeric),
                                    flg_disc = (A.old_data_value::json->'salesOrderItemDto')::json->>'flgDisc',
                                    discount_amount = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'discountAmount' AS numeric),
                                    discount_percentage = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'discountPercentage' AS numeric)
                                FROM sl_manage_so_changes A, sl_manage_so B
                                WHERE A.manage_so_id = pManageSoId
                                    AND A.manage_so_id = B.manage_so_id
                                    AND A.type_data = vTypeDataItem
                                    AND sl_so_item.so_item_id = CAST((A.old_data_value::json->'salesOrderItemDto')::json->>'id' AS bigint)
                                    AND sl_so_item.so_id = vSoId;

                                -- delete so item untuk item baru
                                DELETE FROM sl_so_item Z
                                WHERE EXISTS (
                                        SELECT (1)
                                        FROM sl_manage_so_item A
                                        INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
                                        WHERE A.manage_so_id = pManageSoId
                                            AND Z.so_item_id = A.so_item_id
                                            AND A.flg_new_item = vFlagYes
                                            AND Z.so_id = vSoId
                                    );

                                -- delete so item purchasing untuk item baru
                                DELETE FROM sl_so_item_purchasing Z
                                WHERE EXISTS (
                                        SELECT (1)
                                        FROM sl_manage_so_item A
                                        INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
                                        WHERE A.manage_so_id = pManageSoId
                                            AND A.flg_new_item = vFlagYes
                                            AND B.so_id = vSoId
                                            AND Z.so_item_id = A.so_item_id
                                    );

                                -- delete so activity wo untuk item baru
                                DELETE FROM sl_so_activity_wo Z
                                WHERE EXISTS (
                                        SELECT (1)
                                        FROM sl_manage_so_item A
                                        INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
                                        WHERE A.manage_so_id = pManageSoId
                                            AND A.flg_new_item = vFlagYes
                                            AND B.so_id = vSoId
                                            AND Z.so_item_id = A.so_item_id
                                    );

                                -- update so balance item untuk item lama
                                UPDATE sl_so_balance_item
                                SET update_datetime = pDatetime,
                                    update_user_id = pUserId,
                                    version = sl_so_balance_item.version + 1,
                                    status_item = vStatusInProgress,
                                    qty_add = CAST((A.old_data_value::json->'soBalanceItemDto')::json->>'qtyAdd' AS numeric),
                                    qty_add_int = CAST((A.old_data_value::json->'soBalanceItemDto')::json->>'qtyAddInt' AS numeric)
                                FROM sl_manage_so_changes A, sl_manage_so B, sl_manage_so_item C
                                WHERE A.manage_so_id = pManageSoId
                                    AND A.manage_so_id = B.manage_so_id
                                    AND CAST((A.old_data_value::json->'soBalanceItemDto')::json->>'soItemId' AS bigint) = C.so_item_id
                                    AND sl_so_balance_item.so_item_id = CAST((A.old_data_value::json->'soBalanceItemDto')::json->>'soItemId' AS bigint)
                                    AND C.flg_new_item = vFlagNo
                                    AND A.type_data = vTypeDataItem
                                    AND B.so_id = vSoId;

                                -- delete so balance item untuk item baru
                                DELETE FROM sl_so_balance_item Z
                                WHERE EXISTS (
                                        SELECT (1)
                                        FROM sl_manage_so_item A
                                        INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
                                        WHERE A.manage_so_id = pManageSoId
                                            AND A.flg_new_item = vFlagYes
                                            AND B.so_id = vSoId
                                            AND Z.so_item_id = A.so_item_id
                                    );

                                -- delete dan update dari table saldo qty SO terhadap PO
                                -- Update status menjadi sama dengan so balance item
                                UPDATE sl_so_po_balance_item A
                                SET update_datetime = pDatetime, update_user_id = pUserId, version = A.version + 1, status_item = D.status_item
                                FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
                                WHERE A.so_item_id = B.so_item_id
                                    AND B.manage_so_id = C.manage_so_id
                                    AND A.so_item_id = D.so_item_id
                                    AND B.manage_so_id = pManageSoId
                                    AND B.flg_new_item = vFlagNo
                                    AND (
                                            (A.status_item = vStatusRelease)
                                            OR (A.status_item = vStatusFinal)
                                        );

                                -- Update status menjadi 'I' jika terdapat PO yang menggunakan dan masih draft
                                UPDATE sl_so_po_balance_item A
                                SET update_datetime = pDatetime, update_user_id = pUserId, version = A.version + 1, status_item = vStatusInProgress
                                FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D, pu_po_item E, pu_po F
                                WHERE A.so_item_id = B.so_item_id
                                    AND B.manage_so_id = C.manage_so_id
                                    AND A.so_item_id = D.so_item_id
                                    AND B.manage_so_id = pManageSoId
                                    AND B.flg_new_item = vFlagNo
                                    AND (
                                            (A.status_item = vStatusRelease)
                                            OR (A.status_item = vStatusFinal)
                                        )
                                    AND E.ref_id = D.so_item_id
                                    AND F.po_id = E.po_id
                                    AND F.status_doc = vStatusDraft;

                                -- delete so sl_so_po_balance_item untuk item baru
                                DELETE FROM sl_so_po_balance_item Z
                                WHERE EXISTS(
                                    SELECT (1)
                                    FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
                                    WHERE Z.so_item_id = B.so_item_id
                                        AND B.manage_so_id = C.manage_so_id
                                        AND Z.so_item_id = D.so_item_id
                                        AND B.manage_so_id = pManageSoId
                                        AND B.flg_new_item = vFlagYes
                                    )
                                AND (
                                    (Z.status_item = vStatusRelease)
                                    OR (Z.status_item = vStatusFinal)
                                );

                                --delete sl_log_so_balnce_item
                                DELETE FROM sl_log_so_balance_item Z
                                WHERE EXISTS (
                                    SELECT (1)
                                    FROM sl_manage_so A, sl_manage_so_item B
                                    WHERE Z.tenant_id = A.tenant_id
                                        AND Z.so_id = A.so_id
                                        AND Z.so_item_id = B.so_item_id
                                        AND Z.ref_doc_type_id = A.doc_type_id
                                        AND Z.ref_id = A.manage_so_id
                                        AND Z.ref_item_id = B.manage_so_item_id
                                        AND Z.qty_trx = B.qty_so
                                        AND Z.trx_uom_id = B.so_uom_id
                                        AND Z.qty_int = B.qty_int
                                        AND Z.base_uom_id = B.base_uom_id
                                        AND Z.remark = A.remark
                                        AND A.manage_so_id = pManageSoId
                                        AND A.manage_so_id = B.manage_so_id
                                );

                                -- update tax di so_item
                                INSERT INTO tt_sl_so_tax_for_modify_so
                                (session_id, tenant_id, so_id, tax_id, flg_amount,
                                tax_percentage, base_amount, tax_amount, remark)
                                SELECT pSessionId, A.tenant_id, A.so_id, A.tax_id, B.flg_amount,
                                    A.tax_percentage, SUM(f_get_amount_before_tax((A.gross_sell_price - A.discount_amount) * A.qty_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManageSoItemDocTypeId, A.curr_code), vRoundingModeNonTax)),
                                    f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.gross_sell_price - A.discount_amount) * A.qty_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManageSoItemDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage), B.tax_name
                                FROM sl_so_item A, m_tax B
                                WHERE A.tax_id = B.tax_id AND
                                      A.so_id = vSoId AND
                                      A.tax_id <> vEmptyId
                                GROUP BY A.tenant_id, A.so_id, A.tax_id, B.flg_amount,
                                        A.tax_percentage, B.tax_name;

                                UPDATE sl_so_tax SET base_amount = A.base_amount, tax_amount = A.tax_amount, update_datetime = pDatetime, update_user_id = pUserId, version = sl_so_tax.version + 1
                                FROM tt_sl_so_tax_for_modify_so A
                                WHERE sl_so_tax.tenant_id = A.tenant_id
                                AND A.session_id = pSessionId
                                AND sl_so_tax.so_id = A.so_id
                                AND sl_so_tax.tax_id = A.tax_id;

                                /*
                                 * jika flg os change do=y, maka update:
                                 * 1. sl_so_balance_invoice
                                 * 2. sl_so_balance_invoice_tax (bisa insert / update)
                                 */

                                /** UPDATE BALANCE DELIVERY ORDER **/

                                INSERT INTO tt_os_delivery_order_item_manage_item_so(
                                        session_id, tenant_id,
                                        ou_id, delivery_order_doc_type_id, delivery_order_item_id, delivery_order_id,
                                        so_id, so_item_id, delivery_order_no, delivery_order_date, curr_code,
                                        partner_id, gross_sell_price, discount_amount, nett_sell_price, flg_tax_amount,
                                        tax_id, tax_percentage, qty_dlv_so, so_balance_invoice_id)
                                SELECT pSessionId, pTenantId,
                                       F.ou_id, C.doc_type_id, D.do_item_id, C.do_id,
                                       G.so_id, G.so_item_id, C.doc_no, C.doc_date, G.curr_code,
                                       F.partner_bill_to_id, G.gross_sell_price, G.discount_amount, G.nett_sell_price, G.flg_tax_amount,
                                       G.tax_id, G.tax_percentage, D.qty_dlv_so, E.so_balance_invoice_id
                                FROM sl_manage_so_item A
                                INNER JOIN sl_manage_so B ON A.manage_so_id = B.manage_so_id
                                INNER JOIN sl_do C ON C.ref_id = B.so_id
                                INNER JOIN sl_do_item D ON C.do_id = D.do_id AND A.so_item_id = D.ref_id
                                INNER JOIN sl_so F ON F.so_id = B.so_id
                                INNER JOIN sl_so_balance_invoice E
                                    ON E.ref_doc_type_id = C.doc_type_id
                                    AND E.ref_id = C.do_id
                                    AND E.ref_item_id = D.do_item_id
                                    AND E.so_id = B.so_id
                                    AND E.tenant_id = pTenantId
                                    AND E.ou_id = B.ou_id
                                    AND E.partner_id = F.partner_bill_to_id
                                    AND E.do_receipt_item_id = vEmptyId
                                INNER JOIN sl_so_item G ON G.so_item_id = A.so_item_id
                                WHERE E.flg_invoice = vFlagNo
                                    AND E.invoice_id = vEmptyId
                                    AND A.manage_so_id = pManageSoId
                                    AND A.flg_change_do = vFlagYes;

                                UPDATE sl_so_balance_invoice A
                                SET price_so = B.nett_sell_price,
                                    item_amount = f_get_amount_before_tax_and_disc(B.qty_dlv_so * B.gross_sell_price, B.qty_dlv_so * B.discount_amount, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, B.curr_code), vRoundingModeNonTax),
                                    version = A.version + 1,
                                    update_datetime = pDatetime,
                                    update_user_id = pUserId
                                FROM tt_os_delivery_order_item_manage_item_so B
                                WHERE B.session_id = pSessionId
                                AND B.so_balance_invoice_id = A.so_balance_invoice_id;

                                DELETE FROM sl_so_balance_invoice_tax A
                                WHERE A.tenant_id = pTenantId
                                AND EXISTS (
                                    SELECT 1 FROM tt_os_delivery_order_item_manage_item_so B
                                    WHERE A.tenant_id = B.tenant_id
                                    AND A.ou_id = B.ou_id
                                    AND A.partner_id = B.partner_id
                                    AND A.so_id = B.so_id
                                    AND A.ref_doc_type_id = B.delivery_order_doc_type_id
                                    AND A.ref_id = B.delivery_order_id
                                    AND A.ref_item_id = B.delivery_order_item_id
                                    AND A.do_receipt_item_id = vEmptyId
                                    AND B.session_id = pSessionId
                                );

                                INSERT INTO sl_so_balance_invoice_tax
                                    (tenant_id, ou_id, partner_id, so_id,
                                    ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
                                    tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
                                    "version", create_datetime, create_user_id, update_datetime, update_user_id)
                                SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
                                    A.delivery_order_doc_type_id, A.delivery_order_id, A.delivery_order_item_id, A.tax_id, B.flg_amount,
                                    A.tax_percentage, A.curr_code,
                                    f_get_amount_before_tax_and_disc(A.qty_dlv_so * A.gross_sell_price, A.qty_dlv_so * A.discount_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code), vRoundingModeNonTax),
                                    f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.qty_dlv_so * (A.gross_sell_price - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code), vRoundingModeNonTax), A.tax_percentage), vFlagNo, vEmptyId,
                                    0, pDatetime, pUserId, pDatetime, pUserId
                                FROM tt_os_delivery_order_item_manage_item_so A
                                INNER JOIN m_tax B ON A.tax_id = B.tax_id
                                WHERE A.session_id = pSessionId;

                                /** UPDATE BALANCE DO RECEIPT **/

                                INSERT INTO tt_os_delivery_order_receipt_item_manage_item_so(
                                        session_id, tenant_id,
                                        ou_id, delivery_order_doc_type_id, delivery_order_item_id, delivery_order_id,
                                        so_id, so_item_id, delivery_order_receipt_no, delivery_order_receipt_date, curr_code,
                                        partner_id, gross_sell_price, discount_amount, nett_sell_price,
                                        regular_disc_amount, promo_disc_amount, flg_tax_amount,
                                        tax_id, tax_percentage, qty_return, qty_so, qty_int, qty_dlv_so,
                                        so_balance_invoice_id, do_receipt_item_id)
                                SELECT pSessionId, pTenantId,
                                       F.ou_id, C.doc_type_id, E.ref_id, C.do_id,
                                       F.so_id, G.so_item_id, D.doc_no, D.doc_date, G.curr_code,
                                       F.partner_bill_to_id, G.gross_sell_price, G.discount_amount, G.nett_sell_price,
                                       I.regular_disc_amount, I.promo_disc_amount, G.flg_tax_amount,
                                       G.tax_id, G.tax_percentage, E.qty_return, G.qty_so, G.qty_int, I.qty_dlv_so,
                                       H.so_balance_invoice_id, E.do_receipt_item_id
                                FROM sl_manage_so_item A
                                INNER JOIN sl_manage_so B ON A.manage_so_id = B.manage_so_id
                                INNER JOIN sl_do C ON C.ref_id = B.so_id
                                INNER JOIN sl_do_item J ON C.do_id = J.do_id AND A.so_item_id = J.ref_id
                                INNER JOIN in_do_receipt D ON D.ref_id = C.do_id
                                INNER JOIN in_do_receipt_item E ON E.do_receipt_id = D.do_receipt_id AND E.ref_id = J.do_item_id
                                INNER JOIN sl_so F ON B.so_id = F.so_id
                                INNER JOIN sl_so_item G ON F.so_id = G.so_id AND G.so_item_id = J.ref_id
                                INNER JOIN sl_so_balance_invoice H
                                    ON H.ref_doc_type_id = C.doc_type_id
                                    AND H.ref_id = C.do_id
                                    AND H.ref_item_id = E.ref_id
                                    AND H.tenant_id = pTenantId
                                    AND H.ou_id = D.ou_id
                                    AND H.partner_id = F.partner_bill_to_id
                                    AND H.do_receipt_item_id = E.do_receipt_item_id
                                INNER JOIN sl_so_balance_invoice I
                                    ON I.ref_doc_type_id = C.doc_type_id
                                    AND I.ref_id = C.do_id
                                    AND I.ref_item_id = E.ref_id
                                    AND I.tenant_id = pTenantId
                                    AND I.ou_id = D.ou_id
                                    AND I.partner_id = F.partner_bill_to_id
                                    AND I.do_receipt_item_id = vEmptyId
                                WHERE H.flg_invoice = vFlagNo
                                    AND H.invoice_id = vEmptyId
                                    AND A.manage_so_id = pManageSoId
                                    AND A.flg_change_do = vFlagYes;

                                UPDATE sl_so_balance_invoice A
                                SET price_so = f_get_price_before_tax_and_disc(B.gross_sell_price, ((B.regular_disc_amount / B.qty_dlv_so) + (B.promo_disc_amount / B.qty_dlv_so)), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, B.curr_code), vRoundingModeNonTax),
                                    item_amount = -1 * f_get_amount_before_tax_and_disc((B.qty_return * B.qty_so / B.qty_int) * B.gross_sell_price, (B.regular_disc_amount + B.promo_disc_amount) * (B.qty_return * B.qty_so / B.qty_int) / B.qty_dlv_so, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, B.curr_code), vRoundingModeNonTax),
                                    version = A.version + 1,
                                    update_datetime = pDatetime,
                                    update_user_id = pUserId
                                FROM tt_os_delivery_order_receipt_item_manage_item_so B
                                WHERE B.session_id = pSessionId
                                AND B.so_balance_invoice_id = A.so_balance_invoice_id;

                                DELETE FROM sl_so_balance_invoice_tax A
                                WHERE A.tenant_id = pTenantId
                                AND EXISTS (
                                    SELECT 1 FROM tt_os_delivery_order_receipt_item_manage_item_so B
                                    WHERE A.tenant_id = B.tenant_id
                                    AND A.ou_id = B.ou_id
                                    AND A.partner_id = B.partner_id
                                    AND A.so_id = B.so_id
                                    AND A.ref_doc_type_id = B.delivery_order_doc_type_id
                                    AND A.ref_id = B.delivery_order_id
                                    AND A.ref_item_id = B.delivery_order_item_id
                                    AND A.do_receipt_item_id = B.do_receipt_item_id
                                    AND B.session_id = pSessionId
                                );

                                INSERT INTO sl_so_balance_invoice_tax
                                (tenant_id, ou_id, partner_id, so_id,
                                ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
                                tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
                                "version", create_datetime, create_user_id, update_datetime, update_user_id,
                                do_receipt_item_id)
                                SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
                                    A.delivery_order_doc_type_id, A.delivery_order_id, A.delivery_order_item_id, A.tax_id, B.flg_amount,
                                    A.tax_percentage, A.curr_code,
                                    -1 * f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so / A.qty_int) * A.gross_sell_price, (A.qty_return * A.qty_so / A.qty_int) * A.discount_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, A.curr_code), vRoundingModeNonTax),
                                    -1 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax((A.qty_return * A.qty_so / A.qty_int) * (A.gross_sell_price - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, A.curr_code), vRoundingModeNonTax), A.tax_percentage), vFlagNo, vEmptyId,
                                    0, pDatetime, pUserId, pDatetime, pUserId,
                                    A.do_receipt_item_id
                                FROM tt_os_delivery_order_receipt_item_manage_item_so A
                                INNER JOIN m_tax B ON A.tax_id = B.tax_id
                                WHERE A.session_id = pSessionId;

                                -- update nilai so_item_id pada sl_manage_so_item menjadi -99
                                UPDATE sl_manage_so_item
                                SET so_item_id = vEmptyId
                                WHERE flg_new_item = vFlagYes
                                    AND manage_so_id = pManageSoId;

                                -- update status doc manage so
                                UPDATE sl_manage_so SET status_doc = vStatusDraft, workflow_status = vWorkflowStatusDraft, update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
                                WHERE manage_so_id = pManageSoId
                                    AND status_doc = vStatusRelease;

                                DELETE FROM tt_sl_so_tax_for_modify_so WHERE session_id = pSessionId;
                                DELETE FROM tt_os_delivery_order_item_manage_item_so WHERE session_id = pSessionId;
                                DELETE FROM tt_os_delivery_order_receipt_item_manage_item_so WHERE session_id = pSessionId;

                                -- PERFORM f_reset_approval_to_draft
                                PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pManageSoId, 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 = pManageSoId || '_' || vDocNo;


                            ELSE
                                RAISE EXCEPTION 'Sales Order with id % already used for invoice', vSoId;
                            END IF;

                        ELSE
                            RAISE EXCEPTION 'Sales Order with id % already used for other document', vSoId;
                        END IF;

                    ELSE
                        RAISE EXCEPTION 'Sales Order with id % is void, or is canceled', vSoId;
                    END IF;

                ELSE
                    RAISE EXCEPTION 'Sales Order with id % is in use by other document, is void, or is canceled', vSoId;
                END IF;

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

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

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

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