CANCEL SUBMIT MODIFY ITEM PO

-- Adrian, Mar 8, 2017

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

    vEmptyId                            bigint;
    vManagePoItemDocTypeId              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;
    vStatusCancel                       character varying;
    vStatusVoid                         character varying;
    vWorkflowStatusDraft                character varying;
    vPoId                               bigint;
    vPoStatusDoc                        character varying(5);
    vZero                               bigint;
    vTypeDataItem                       character varying;

    vReceiveGoodsDocDate                character varying;
    vOuId                               bigint;
    vLedgerCode                         character varying;
    vStatusLedgerNotDone                character varying;
    vEmpty                              character varying;
    vScheme                             character varying;
    vDocNo                              character varying;

    vReceiveGoodsDocTypeId              bigint;

BEGIN

    vEmptyId := -99;
    vStatusRelease := 'R';
    vFlagNo := 'N';
    vFlagYes := 'Y';
    vManagePoItemDocTypeId := 105;

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

    vLedgerCode := 'PURCH';
    vStatusLedgerNotDone := '0';

    vEmpty := '';
    vReceiveGoodsDocTypeId := 111;

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

    --RAISE EXCEPTION 'Function Cancel submit modify item for PO is not yet Created';

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou,
        A.ou_id, A.doc_date,
        COALESCE(B.doc_date, '') AS rg_doc_date,
        A.doc_no, C.scheme,
        A.po_id, A.po_status_doc
    FROM pu_manage_po A
    LEFT JOIN pu_receive_goods B ON B.ref_id = A.po_id
    INNER JOIN m_document C ON C.doc_type_id = A.doc_type_id
    WHERE A.manage_po_id = pManagePoId AND
        A.doc_type_id = vManagePoItemDocTypeId AND
        A.status_doc = vStatusRelease INTO RESULT;

    IF FOUND THEN
        vOuStructure := RESULT.ou;
        vOuId := RESULT.ou_id;
        vReceiveGoodsDocDate := RESULT.rg_doc_date;
        vScheme := RESULT.scheme;
        vDocNo := RESULT.doc_no;
        vPoId := RESULT.po_id;
        vPoStatusDoc := RESULT.po_status_doc;

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

            /*
             * validasi po tidak sedang digunakan
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM pu_po A, pu_manage_po B
                WHERE B.manage_po_id = pManagePoId
                    AND A.po_id = B.po_id
                    AND (
                        (A.status_doc <> vStatusRelease)
                        AND (A.status_doc <> vStatusFinal)
                    )
                ) THEN

                /**
                 * validasi tidak sedang digunakan oleh dokumen lain
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM pu_po_balance_item A, pu_manage_po_item B, pu_manage_po C
                    WHERE A.po_item_id = B.po_item_id
                        AND B.manage_po_id = C.manage_po_id
                        AND B.manage_po_id = pManagePoId
                        AND (
                            (A.status_item <> vStatusRelease)
                            AND (A.status_item <> vStatusFinal)
                        )
                    ) THEN

                    /**
                     * validasi tidak sedang digunakan oleh invoice
                     */
                    IF NOT EXISTS (
                        SELECT (1)
                        FROM pu_po_balance_invoice A, pu_manage_po B
                        WHERE A.po_id = B.po_id
                            AND B.manage_po_id = pManagePoId
                            AND (
                                (A.flg_invoice <> vFlagNo)
                                AND (A.flg_invoice <> vStatusVoid)
                            )
                    ) THEN

                        /**
                         * validasi tidak sedang digunakan
                         */
                        IF NOT EXISTS (
                            SELECT (1)
                            FROM sl_so_po_balance_item A, pu_manage_po_item B
                            WHERE A.so_item_id = B.ref_item_id
                                AND B.manage_po_id = pManagePoId
                                AND A.status_item = vStatusInProgress
                        ) THEN

                            DELETE FROM tt_pu_po_tax_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_os_receive_goods_item_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_product_price_balance_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_pu_monthly_price_product_manage_item_po WHERE session_id = pSessionId;

                            UPDATE pu_po
                            SET remark = A.remark,
                                update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = pu_po.version + 1,
                                status_doc = vPoStatusDoc
                            FROM pu_manage_po A
                            WHERE A.manage_po_id = pManagePoId
                                AND pu_po.po_id = A.po_id;

                            -- update po item untuk item lama
                            UPDATE pu_po_item
                            SET update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = pu_po_item.version + 1,
                                gross_price_po = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'grossPricePo' AS numeric),
                                flg_tax_amount = (A.old_data_value::json->'purchaseOrderItemDto')::json->>'flgTaxAmount',
                                tax_id = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'taxId' AS bigint),
                                tax_percentage = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'taxPercentage' AS numeric(5,2)),
                                tax_price = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'taxPrice' AS numeric),
                                nett_price_po = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'nettPricePo' AS numeric),
                                qty_po = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'qtyPo' AS numeric),
                                qty_int = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'qtyInt' AS numeric),
                                gross_item_amount = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'grossItemAmount' AS numeric),
                                nett_item_amount = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'nettItemAmount' AS numeric),
                                tax_amount = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'taxAmount' AS numeric)
                            FROM pu_manage_po_changes A, pu_manage_po B
                            WHERE A.manage_po_id = pManagePoId
                                AND A.manage_po_id = B.manage_po_id
                                AND A.type_data = vTypeDataItem
                                AND pu_po_item.po_item_id = CAST((A.old_data_value::json->'purchaseOrderItemDto')::json->>'id' AS bigint)
                                AND pu_po_item.po_id = vPoId;

                            -- delete po item untuk item baru
                            DELETE FROM pu_po_item Z
                            WHERE EXISTS (
                                    SELECT (1)
                                    FROM pu_manage_po_item A
                                    INNER JOIN pu_manage_po B ON B.manage_po_id = A.manage_po_id
                                    WHERE A.manage_po_id = pManagePoId
                                        AND Z.po_item_id = A.po_item_id
                                        AND A.flg_new_item = vFlagYes
                                        AND Z.po_id = vPoId
                                );

                            -- update po balance item untuk item lama
                            UPDATE pu_po_balance_item
                            SET update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = pu_po_balance_item.version + 1,
                                status_item = vStatusInProgress,
                                qty_add = CAST((A.old_data_value::json->'poBalanceItemDto')::json->>'qtyAdd' AS numeric),
                                qty_int_add = CAST((A.old_data_value::json->'poBalanceItemDto')::json->>'qtyIntAdd' AS numeric)
                            FROM pu_manage_po_changes A, pu_manage_po B, pu_manage_po_item C
                            WHERE A.manage_po_id = pManagePoId
                                AND A.manage_po_id = B.manage_po_id
                                AND CAST((A.old_data_value::json->'poBalanceItemDto')::json->>'poItemId' AS bigint) = C.po_item_id
                                AND pu_po_balance_item.po_item_id = CAST((A.old_data_value::json->'poBalanceItemDto')::json->>'poItemId' AS bigint)
                                AND C.flg_new_item = vFlagNo
                                AND A.type_data = vTypeDataItem
                                AND B.po_id = vPoId;

                            -- delete po balance item untuk item baru
                            DELETE FROM pu_po_balance_item Z
                            WHERE EXISTS (
                                    SELECT (1)
                                    FROM pu_manage_po_item A
                                    INNER JOIN pu_manage_po B ON B.manage_po_id = A.manage_po_id
                                    WHERE A.manage_po_id = pManagePoId
                                        AND A.flg_new_item = vFlagYes
                                        AND B.po_id = vPoId
                                        AND Z.po_item_id = A.po_item_id
                                );

                            --delete pu_log_po_balance_item
                            DELETE FROM pu_log_po_balance_item Z
                            WHERE EXISTS (
                                SELECT (1)
                                FROM pu_manage_po A, pu_manage_po_item B
                                WHERE Z.tenant_id = A.tenant_id
                                    AND Z.po_id = A.po_id
                                    AND Z.po_item_id = B.po_item_id
                                    AND Z.ref_doc_type_id = vManagePoItemDocTypeId
                                    AND Z.ref_id = pManagePoId
                                    AND Z.ref_item_id = B.manage_po_item_id
                                    AND Z.qty_trx = B.qty_po
                                    AND Z.trx_uom_id = B.po_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_po_id = pManagePoId
                                    AND A.manage_po_id = B.manage_po_id
                            );

                            -- update tax di po_item
                            INSERT INTO tt_pu_po_tax_manage_item_po
                            (session_id, tenant_id, po_id, tax_id, flg_amount,
                            tax_percentage, base_amount, tax_amount, remark)
                            SELECT pSessionId, A.tenant_id, A.po_id, A.tax_id, B.flg_amount,
                                A.tax_percentage, SUM(f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManagePoItemDocTypeId, A.curr_code), vRoundingModeNonTax)),
                                f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManagePoItemDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage), B.tax_name
                            FROM pu_po_item A, m_tax B
                            WHERE A.tax_id = B.tax_id AND
                                  A.po_id = vPoId AND
                                  A.tax_id <> vEmptyId
                            GROUP BY A.tenant_id, A.po_id, A.tax_id, B.flg_amount,
                                    A.tax_percentage, B.tax_name;

                            UPDATE pu_po_tax
                            SET base_amount = A.base_amount,
                                tax_amount = A.tax_amount,
                                update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = pu_po_tax.version + 1
                            FROM tt_pu_po_tax_manage_item_po A
                            WHERE pu_po_tax.tenant_id = A.tenant_id
                            AND A.session_id = pSessionId
                            AND pu_po_tax.po_id = A.po_id
                            AND pu_po_tax.tax_id = A.tax_id;

                            /*
                             * untuk item lama dan untuk item itu memiliki receive goods yang belum diinvoice, maka update:
                             * 1. pu_po_balance_invoice
                             * 2. pu_po_balance_invoice_tax (bisa insert / update)
                             * 3. in_product_price_balance (update)
                             * 4. pu_monthly_price_product (update)
                             * 6. gl_journal_trx_item (update)
                             * 7. gl_journal_trx_mapping (update)
                             */

                            -- ambil dulu receive goods item yang belum dibuat invoice untuk PO yg diubah
                            INSERT INTO tt_os_receive_goods_item_manage_item_po
                            (session_id, tenant_id, ou_id, receive_goods_doc_type_id, receive_goods_item_id, receive_goods_id, po_id, po_item_id,
                             receive_goods_no, receive_goods_date, curr_code, partner_id,
                             gross_price_po, discount_amount, nett_price_po, flg_tax_amount, tax_id, tax_percentage, qty_rcv_po, po_balance_invoice_id)
                            SELECT pSessionId, pTenantId, F.ou_id, C.doc_type_id, D.receive_goods_item_id, C.receive_goods_id, B.po_id, G.po_item_id,
                                   C.doc_no, C.doc_date, G.curr_code, F.partner_id,
                                   G.gross_price_po, G.discount_amount, G.nett_price_po, G.flg_tax_amount, G.tax_id, G.tax_percentage, D.qty_rcv_po, E.po_balance_invoice_id
                            FROM pu_manage_po_item A
                            INNER JOIN pu_manage_po B ON A.manage_po_id = B.manage_po_id
                            INNER JOIN pu_receive_goods C ON C.ref_id = B.po_id
                            INNER JOIN pu_receive_goods_item D ON C.receive_goods_id = D.receive_goods_id AND A.po_item_id = D.ref_id
                            INNER JOIN pu_po_balance_invoice E
                                ON E.ref_doc_type_id = C.doc_type_id
                                AND E.ref_id = C.receive_goods_id
                                AND E.ref_item_id = D.receive_goods_item_id
                                AND E.po_id = B.po_id
                                AND E.tenant_id = pTenantId
                                AND E.ou_id = B.ou_id
                            INNER JOIN pu_po F ON F.po_id = B.po_id
                            INNER JOIN pu_po_item G ON G.po_item_id = A.po_item_id
                            WHERE E.flg_invoice = vFlagNo
                                AND E.invoice_id = vEmptyId
                                AND A.manage_po_id = pManagePoId;

                            UPDATE pu_po_balance_invoice A
                            SET price_po = B.nett_price_po,
                                item_amount = f_get_amount_before_tax(B.qty_rcv_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
                                version = A.version + 1,
                                update_datetime = pDatetime,
                                update_user_id = pUserId
                            FROM tt_os_receive_goods_item_manage_item_po B
                            WHERE B.session_id = pSessionId
                            AND B.po_balance_invoice_id = A.po_balance_invoice_id;

                            DELETE FROM pu_po_balance_invoice_tax A
                            WHERE A.tenant_id = pTenantId
                            AND EXISTS (
                                SELECT 1 FROM tt_os_receive_goods_item_manage_item_po B
                                WHERE A.tenant_id = B.tenant_id
                                AND A.ou_id = B.ou_id
                                AND A.partner_id = B.partner_id
                                AND A.po_id = B.po_id
                                AND A.ref_doc_type_id = B.receive_goods_doc_type_id
                                AND A.ref_id = B.receive_goods_id
                                AND A.ref_item_id = B.receive_goods_item_id
                                AND B.session_id = pSessionId
                            );

                            INSERT INTO pu_po_balance_invoice_tax
                            (tenant_id, ou_id, partner_id, po_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.po_id,
                                A.receive_goods_doc_type_id, A.receive_goods_id, A.receive_goods_item_id, A.tax_id, B.flg_amount,
                                A.tax_percentage, A.curr_code,
                                f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax),
                                f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax), A.tax_percentage),
                                vFlagNo, vEmptyId,
                                0, pDatetime, pUserId, pDatetime, pUserId
                            FROM tt_os_receive_goods_item_manage_item_po A
                            INNER JOIN m_tax B ON A.tax_id = B.tax_id
                            WHERE A.session_id = pSessionId AND A.tenant_id = pTenantId;

                            INSERT INTO tt_product_price_balance_manage_item_po
                                (session_id, tenant_id, ou_id, doc_type_id, ref_id, amount)
                            SELECT pSessionId, pTenantId, A.ou_id, A.receive_goods_doc_type_id, A.receive_goods_item_id, SUM(f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax))
                            FROM tt_os_receive_goods_item_manage_item_po A
                            WHERE A.session_id = pSessionId
                            GROUP BY A.ou_id, A.receive_goods_doc_type_id, A.receive_goods_item_id;

                            UPDATE in_product_price_balance B
                            SET amount = A.amount, version = B.version + 1, update_datetime = pDatetime, update_user_id = pUserId
                            FROM tt_product_price_balance_manage_item_po A
                            WHERE A.session_id = pSessionId
                                AND A.tenant_id = B.tenant_id
                                AND A.ou_id = B.ou_id
                                AND A.doc_type_id = B.doc_type_id
                                AND A.ref_id = B.ref_id;

                            INSERT INTO tt_pu_monthly_price_product_manage_item_po
                            (session_id, tenant_id, ou_id, year_month_date,
                             product_id, curr_code, amount,
                             flg_tax_amount, tax_id, tax_percentage)
                            SELECT pSessionId, pTenantId, A.ou_id, B.year_month_date,
                                   C.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
                                   A.flg_tax_amount, A.tax_id, A.tax_percentage
                            FROM tt_os_receive_goods_item_manage_item_po A
                            INNER JOIN dt_date B ON A.receive_goods_date = B.string_date
                            INNER JOIN pu_receive_goods_item C ON A.receive_goods_item_id = C.receive_goods_item_id
                            WHERE A.session_id = pSessionId
                            GROUP BY A.ou_id, B.year_month_date, C.product_id, A.curr_code, A.flg_tax_amount, A.tax_id, A.tax_percentage;

                            -- update pu_monthly_price_product
                            UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
                                                                flg_tax_amount = A.flg_tax_amount,
                                                                tax_id = A.tax_id, tax_percentage = A.tax_percentage,
                                                                version = pu_monthly_price_product.version + 1,
                                                                update_datetime = pDatetime,
                                                                update_user_id = pUserId
                            FROM tt_pu_monthly_price_product_manage_item_po A
                            WHERE A.session_id = pSessionId AND
                                pu_monthly_price_product.tenant_id = A.tenant_id AND
                                pu_monthly_price_product.ou_id = A.ou_id AND
                                pu_monthly_price_product.year_month_date = A.year_month_date AND
                                pu_monthly_price_product.product_id = A.product_id;

                            -- update gl_journal_trx_item
                            UPDATE gl_journal_trx_item A
                            SET amount = f_get_amount_before_tax(B.qty_rcv_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage,f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)
                            FROM tt_os_receive_goods_item_manage_item_po B
                            INNER JOIN gl_journal_trx C ON C.ou_bu_id = B.ou_id AND C.doc_type_id = B.receive_goods_doc_type_id AND C.doc_id = B.receive_goods_id AND C.doc_no = B.receive_goods_no AND C.doc_date = B.receive_goods_date
                            WHERE A.ref_doc_type_id = B.receive_goods_doc_type_id
                                AND A.ref_id = B.receive_goods_item_id
                                AND A.journal_trx_id = C.journal_trx_id;

                            -- update gl_journal_trx_mapping
                            WITH summary_for_update_gl_journal_trx_mapping AS (
                                SELECT C.journal_trx_id, SUM(f_get_amount_before_tax(B.qty_rcv_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage,f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)) AS amount
                                FROM tt_os_receive_goods_item_manage_item_po B
                                INNER JOIN gl_journal_trx C ON C.doc_type_id = B.receive_goods_doc_type_id AND C.doc_id = B.receive_goods_id AND C.doc_no = B.receive_goods_no AND C.doc_date = B.receive_goods_date
                                GROUP BY C.journal_trx_id
                            )
                            UPDATE gl_journal_trx_mapping A
                            SET amount = B.amount
                            FROM summary_for_update_gl_journal_trx_mapping B
                            WHERE A.journal_trx_id = B.journal_trx_id;

                            UPDATE sl_so_po_balance_item A
                            SET status_item = (CASE WHEN A.status_item IN (vStatusCancel, vStatusVoid) THEN A.status_item ELSE vStatusRelease END),
                                update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = A.version + 1,
                                qty_po_int_add = A.qty_po_int_add - B.qty_int
                            FROM pu_manage_po_item B
                            WHERE A.so_item_id = B.ref_item_id
                                AND B.manage_po_id = pManagePoId
                                AND B.flg_new_item = vFlagNo;

                            UPDATE sl_so_po_balance_item A
                            SET status_item = (CASE WHEN A.status_item IN (vStatusCancel, vStatusVoid) THEN A.status_item ELSE vStatusRelease END),
                                update_datetime = pDatetime,
                                update_user_id = pUserId,
                                version = A.version + 1,
                                qty_po_int = A.qty_po_int + B.qty_int
                            FROM pu_manage_po_item B
                            WHERE A.so_item_id = B.ref_item_id
                                AND B.manage_po_id = pManagePoId
                                AND B.flg_new_item = vFlagYes;

                             -- Update status menjadi F, untuk item-item lama dan baru yang tidak memiliki o/s qty PO terhadap SO
                            UPDATE sl_so_po_balance_item A
                            SET status_item = vStatusFinal
                            FROM pu_manage_po_item B, sl_so_balance_item C
                            WHERE A.so_item_id = B.ref_item_id
                                AND A.so_item_id = C.so_item_id
                                AND B.manage_po_id = pManagePoId
                                -- AND B.flg_new_item = vFlagNo
                                AND (C.qty_so_int - C.qty_cancel_int + C.qty_add_int) <= (A.qty_po_int - A.qty_po_int_cancel + A.qty_po_int_add)
                                AND A.status_item = vStatusRelease;

                            -- update nilai po_item_id pada pu_manage_po_item menjadi -99
                            UPDATE pu_manage_po_item
                            SET po_item_id = vEmptyId
                            WHERE flg_new_item = vFlagYes
                                AND manage_po_id = pManagePoId;

                            UPDATE pu_manage_po
                            SET status_doc = vStatusDraft,
                                workflow_status = vWorkflowStatusDraft,
                                update_datetime = pDatetime,
                                update_user_id = pUserId
                            WHERE manage_po_id = pManagePoId;

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

                            DELETE FROM tt_pu_po_tax_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_os_receive_goods_item_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_product_price_balance_manage_item_po WHERE session_id = pSessionId;
                            DELETE FROM tt_pu_monthly_price_product_manage_item_po WHERE session_id = pSessionId;

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

                    ELSE
                        RAISE EXCEPTION 'Purchase Order with id % already used in invoice', vPoId;
                    END IF;

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

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

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

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

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