CANCEL APPROVE MODIFY HEADER SO

CREATE OR REPLACE FUNCTION sl_cancel_submit_modify_header_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;
    vManageSoHeaderDocTypeId            bigint;
    vFlagNo                             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;
    vWorkflowStatusDraft                character varying;
    vSoId                               bigint;
    vOldSoValue                         json;
    vZero                               bigint;
    vTypeDataHeader                     character varying;
    vTypeDataFinance                    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;

BEGIN

    vEmptyId := -99;
    vStatusRelease := 'R';
    vFlagNo := 'N';
    vManageSoHeaderDocTypeId := 303;

    vFunctionSubmit := 'sl_submit_modify_header_so';
    vStatusDraft := 'D';
    vStatusInProgress = 'I';
    vWorkflowStatusDraft := 'DRAFT';
    vZero := 0;
    vTypeDataHeader := 'header';
    vTypeDataFinance := 'finance';

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

    vEmpty := '';

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

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

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou, B.old_data_value,
        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
    INNER JOIN sl_manage_so_changes B ON B.manage_so_id = A.manage_so_id
    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 = vManageSoHeaderDocTypeId AND
        B.type_data = vTypeDataHeader AND
        A.status_doc = vStatusRelease INTO RESULT;

    IF FOUND THEN
        vOldSoValue := RESULT.old_data_value::json;
        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;

        -- 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_header B, sl_manage_so C
                    WHERE B.manage_so_id = pManageSoId
                        AND B.manage_so_id = C.manage_so_id
                        AND A.so_id = C.so_id
                        AND A.status_doc <> vStatusRelease
                    ) 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
                        partner_id = CAST(vOldSoValue->>'partnerId' AS bigint),
                        partner_cp_id = CAST(vOldSoValue->>'partnerCpId' AS bigint),
                        partner_bill_to_id = CAST(vOldSoValue->>'partnerBillToId' AS bigint),
                        partner_ship_to_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        partner_ship_address_id = CAST(vOldSoValue->>'partnerShipAddressId' AS bigint),
                        partner_ship_cp_id = CAST(vOldSoValue->>'partnerShipCpId' AS bigint),
                        partner_ship_cp_name = vOldSoValue->>'partnerShipCpName',
                        partner_ship_cp_phone1 = vOldSoValue->>'partnerShipCpPhone1',
                        partner_ship_cp_phone2 = vOldSoValue->>'partnerShipCpPhone2',
                        doc_date = vOldSoValue->>'docDate',
                        ext_doc_no = vOldSoValue->>'extDocNo',
                        ext_doc_date = vOldSoValue->>'extDocDate',
                        curr_code = vOldSoValue->>'currCode',
                        flg_top_type = vOldSoValue->>'flgTopType',
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = sl_so.version + 1,
                        status_doc = vStatusInProgress
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND sl_so.so_id = B.so_id
                        AND sl_so.status_doc = vStatusRelease;

                    UPDATE sl_so_item SET
                        partner_ship_to_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        partner_ship_address_id = CAST(vOldSoValue->>'partnerShipAddressId' AS bigint),
                        curr_code = vOldSoValue->>'currCode',
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = sl_so_item.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND sl_so_item.so_id = B.so_id;

                    UPDATE sl_do SET
                        ext_doc_no = vOldSoValue->>'extDocNo',
                        ext_doc_date = vOldSoValue->>'extDocDate',
                        partner_ship_to_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        partner_ship_address_id = CAST(vOldSoValue->>'partnerShipAddressId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = sl_do.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND sl_do.ref_id = B.so_id;

                    UPDATE sl_so_balance_invoice SET
                        curr_code = vOldSoValue->>'currCode',
                        partner_id = CAST(vOldSoValue->>'partnerBillToId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        flg_invoice = vFlagNo,
                        version = sl_so_balance_invoice.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND sl_so_balance_invoice.so_id = B.so_id;

                    UPDATE sl_so_balance_invoice_tax SET
                        curr_code = vOldSoValue->>'currCode',
                        partner_id = CAST(vOldSoValue->>'partnerBillToId' AS bigint),
                        flg_invoice = vFlagNo,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = sl_so_balance_invoice_tax.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND sl_so_balance_invoice_tax.so_id = B.so_id;

                    UPDATE in_balance_do_item SET
                        so_date = vOldSoValue->>'docDate',
                        partner_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = in_balance_do_item.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B
                    WHERE A.manage_so_id = pManageSoId
                    AND A.manage_so_id = B.manage_so_id
                    AND in_balance_do_item.so_id = B.so_id;

                    /** DELIVERY ORDER **/

                    UPDATE gl_journal_trx
                    SET curr_code = vOldSoValue->>'currCode',
                        ext_doc_no = vOldSoValue->>'extDocNo',
                        ext_doc_date = vOldSoValue->>'extDocDate',
                        partner_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = gl_journal_trx.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, sl_do C
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND gl_journal_trx.doc_type_id = C.doc_type_id
                        AND gl_journal_trx.doc_id = C.do_id
                        AND gl_journal_trx.doc_no = C.doc_no
                        AND gl_journal_trx.doc_date = C.doc_date
                        AND gl_journal_trx.tenant_id = C.tenant_id
                        AND gl_journal_trx.ou_bu_id = (vOuStructure).ou_bu_id
                        AND gl_journal_trx.ou_branch_id = (vOuStructure).ou_branch_id
                        AND gl_journal_trx.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                        AND C.ref_id = B.so_id;

                    UPDATE gl_journal_trx_item
                    SET curr_code = vOldSoValue->>'currCode',
                        partner_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = gl_journal_trx_item.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, sl_do C, gl_journal_trx D
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND gl_journal_trx_item.journal_trx_id = D.journal_trx_id
                        AND D.doc_type_id = C.doc_type_id
                        AND D.doc_id = C.do_id
                        AND D.doc_no = C.doc_no
                        AND D.doc_date = C.doc_date
                        AND D.tenant_id = C.tenant_id
                        AND D.ou_bu_id = (vOuStructure).ou_bu_id
                        AND D.ou_branch_id = (vOuStructure).ou_branch_id
                        AND D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                        AND C.ref_id = B.so_id;

                    UPDATE gl_journal_trx_mapping
                    SET curr_code = vOldSoValue->>'currCode',
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = gl_journal_trx_mapping.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, sl_do C, gl_journal_trx D
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND gl_journal_trx_mapping.journal_trx_id = D.journal_trx_id
                        AND D.doc_type_id = C.doc_type_id
                        AND D.doc_id = C.do_id
                        AND D.doc_no = C.doc_no
                        AND D.doc_date = C.doc_date
                        AND D.tenant_id = C.tenant_id
                        AND D.ou_bu_id = (vOuStructure).ou_bu_id
                        AND D.ou_branch_id = (vOuStructure).ou_branch_id
                        AND D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                        AND C.ref_id = B.so_id;

                    /** DELIVERY ORDER **/

                    /** DO RECEIPT **/

                    UPDATE gl_journal_trx
                    SET curr_code = vOldSoValue->>'currCode',
                        update_datetime = pDatetime,
                        partner_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        update_user_id = pUserId,
                        version = gl_journal_trx.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, in_do_receipt C, sl_do D
                    WHERE A.manage_so_id = pManageSoId
                    AND A.manage_so_id = B.manage_so_id
                    AND gl_journal_trx.doc_type_id = C.doc_type_id
                    AND gl_journal_trx.doc_id = C.do_receipt_id
                    AND gl_journal_trx.doc_no = C.doc_no
                    AND gl_journal_trx.doc_date = C.doc_date
                    AND gl_journal_trx.tenant_id = C.tenant_id
                    AND gl_journal_trx.ou_bu_id = (vOuStructure).ou_bu_id
                    AND gl_journal_trx.ou_branch_id = (vOuStructure).ou_branch_id
                    AND gl_journal_trx.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                    AND C.ref_doc_type_id = D.doc_type_id
                    AND C.ref_id = D.do_id
                    AND D.ref_id = B.so_id;

                    UPDATE gl_journal_trx_item
                    SET curr_code = vOldSoValue->>'currCode',
                        partner_id = CAST(vOldSoValue->>'partnerShipToId' AS bigint),
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = gl_journal_trx_item.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, in_do_receipt C, gl_journal_trx D, sl_do E
                    WHERE A.manage_so_id = pManageSoId
                    AND A.manage_so_id = B.manage_so_id
                    AND gl_journal_trx_item.journal_trx_id = D.journal_trx_id
                    AND D.doc_type_id = C.doc_type_id
                    AND D.doc_id = C.do_receipt_id
                    AND D.doc_no = C.doc_no
                    AND D.doc_date = C.doc_date
                    AND D.tenant_id = C.tenant_id
                    AND D.ou_bu_id = (vOuStructure).ou_bu_id
                    AND D.ou_branch_id = (vOuStructure).ou_branch_id
                    AND D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                    AND C.ref_doc_type_id = E.doc_type_id
                    AND C.ref_id = E.do_id
                    AND E.ref_id = B.so_id;

                    UPDATE gl_journal_trx_mapping
                    SET curr_code = vOldSoValue->>'currCode',
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        version = gl_journal_trx_mapping.version + 1
                    FROM sl_manage_so_header A, sl_manage_so B, sl_do C, gl_journal_trx D, sl_do E
                    WHERE A.manage_so_id = pManageSoId
                    AND A.manage_so_id = B.manage_so_id
                    AND gl_journal_trx_mapping.journal_trx_id = D.journal_trx_id
                    AND D.doc_type_id = C.doc_type_id
                    AND D.doc_id = C.do_id
                    AND D.doc_no = C.doc_no
                    AND D.doc_date = C.doc_date
                    AND D.tenant_id = C.tenant_id
                    AND D.ou_bu_id = (vOuStructure).ou_bu_id
                    AND D.ou_branch_id = (vOuStructure).ou_branch_id
                    AND D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
                    AND C.ref_doc_type_id = E.doc_type_id
                    AND C.ref_id = E.do_id
                    AND E.ref_id = B.so_id;

                    /** DO RECEIPT **/

                    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(vManageSoHeaderDocTypeId, 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(vManageSoHeaderDocTypeId, 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;

                    DELETE FROM sl_log_so_balance_item Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM sl_so_item A
                        WHERE A.so_id = vSoId
                            AND Z.tenant_id = A.tenant_id
                            AND Z.so_id = A.so_id
                            AND Z.so_item_id = A.so_item_id
                            AND Z.ref_doc_type_id = vManageSoHeaderDocTypeId
                            AND Z.ref_id = pManageSoId
                            AND Z.ref_item_id = vEmptyId
                            AND Z.qty_trx = vZero
                            AND Z.trx_uom_id = A.so_uom_id
                            AND Z.qty_int = vZero
                            AND Z.base_uom_id = A.base_uom_id
                            AND Z.remark = A.remark
                    );

                    UPDATE sl_so_balance_item SET status_item = vStatusInProgress
                    FROM sl_manage_so_header A, sl_manage_so B, sl_so_item D
                    WHERE A.manage_so_id = pManageSoId
                        AND A.manage_so_id = B.manage_so_id
                        AND B.so_id = D.so_id
                        AND D.so_item_id = sl_so_balance_item.so_item_id;

                    -- delete semua data so finance untuk so id tersebut (karena akan diinsert lagi menggunakan data so finance yang lama)
                    DELETE FROM sl_so_finance WHERE so_id = vSoId;

                    INSERT INTO sl_so_finance
                    (so_id, line_no, tenant_id,
                     flg_collection, due_date,
                     curr_code, amount, trx_curr_code, trx_amount,
                     remark, version,
                     create_datetime, create_user_id, update_datetime, update_user_id)
                    SELECT vSoId, CAST(old_data_value::json->>'lineNo' AS bigint), CAST(old_data_value::json->>'tenantId' AS bigint),
                           old_data_value::json->>'flgCollection', old_data_value::json->>'dueDate',
                           old_data_value::json->>'currCode', CAST(old_data_value::json->>'amount' AS numeric), old_data_value::json->>'trxCurrCode', CAST(old_data_value::json->>'trxAmount' AS numeric),
                           old_data_value::json->>'remark', vZero,
                           pDatetime, pUserId, pDatetime, pUserId
                    FROM sl_manage_so_changes A
                    WHERE A.manage_so_id = pManageSoId
                        AND A.type_data = vTypeDataFinance;

                    -- delete data so ext commission dari manage so
                    DELETE FROM sl_so_external_commission WHERE so_id = vSoId;

                    -- insert data so ext commission dari log
                    INSERT INTO sl_so_external_commission(
                        so_id, line_no, tenant_id, partner_name,
                        commission_curr_code, commission_amount, phone_1, phone_2, bank_code,
                        account_no, bank_curr_code, version, create_datetime, create_user_id,
                        update_datetime, update_user_id)
                    SELECT so_id, line_no, tenant_id, partner_name,
                        commission_curr_code, commission_amount, phone_1, phone_2, bank_code,
                        account_no, bank_curr_code, version, create_datetime, create_user_id,
                        update_datetime, update_user_id
                    FROM sl_log_so_external_commission
                    WHERE so_id = vSoId
                        AND update_datetime = (
                            SELECT MAX(update_datetime)
                            FROM sl_log_so_external_commission
                            WHERE so_id = vSoId
                            GROUP BY so_id, line_no
                        );

                    -- delete data so ext commission yang telah di-insert dari log
                    DELETE FROM sl_log_so_external_commission
                    WHERE so_id = vSoId
                        AND update_datetime = (
                            SELECT MAX(update_datetime)
                            FROM sl_log_so_external_commission
                            WHERE so_id = vSoId
                            GROUP BY so_id, line_no
                        );

                    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;

                    -- 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 % is in use by other documnet', 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;
  /