validate

-- FUNCTION: public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying)

-- DROP FUNCTION public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION public.cb_cancel_submit_payment_order_cb(
    bigint,
    character varying,
    bigint,
    character varying,
    bigint,
    character varying,
    character varying)
RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
    pTenantId               ALIAS FOR $1;
    pSessionId              ALIAS FOR $2;
    pUserId                 ALIAS FOR $3; -- for update user id in transaction
    pDatetime               ALIAS FOR $4;
    pPaymentOrderId         ALIAS FOR $5; -- invoice id
    pRemarkApproval         ALIAS FOR $6; -- Remark for appear in approval reset history
    pPrevProcessNo          ALIAS FOR $7; -- Substitute for renaming prevous process no

    vDocTypePaymentOrderCb      bigint := 601;
    vCashbankLedgerCode     character varying := 'CASH.BANK';
    vStatusLedgerNotDone    character varying := '0';
    vInProgress     character varying := 'I';
    vYes            character varying := 'Y';
    vFunctionSubmit character varying := 'cb_submit_payment_order_cb';
    vStatusDocRelease       character varying := 'R';
    vStatusDocDraft     character varying := 'D';
    vWorkflowStatusDraft    character varying := 'DRAFT';

    vDocTypeId      bigint;
    vOuId           bigint;
    vDocNo          character varying;
    vDocDate        character varying;
    vScheme         character varying;
    vStatusDoc      character varying;

BEGIN
    /** Cancel payment order CB
     * 1. Find document cb_payment_order, must release and match doc type
     * 2. Cek status ledger not yet closed
     * 3. Cek balance cash bank (cb_trx_cashbank_balance) not yet used on BKK (Cash Bank Out)
     * 4. Delete cb_trx_cashbank_balance
     * 5. Update cb_payment_order, set doc_status to In Progress
     * 6. Reset approval
     */

    SELECT A.ou_id, A.doc_date, A.doc_type_id, A.doc_no, A.status_doc, B.scheme
    INTO vOuId, vDocDate, vDocTypeId, vDocNo, vStatusDoc, vScheme
    FROM cb_payment_order A
    INNER JOIN m_document B ON A.doc_type_id = B.doc_type_id
    WHERE A.payment_order_id = pPaymentOrderId
        AND A.doc_type_id = vDocTypePaymentOrderCb
        AND A.status_doc = vStatusDocRelease;

    IF FOUND THEN
        -- * 2. Cek status ledger not yet closed
        IF EXISTS (
            SELECT 1
            FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
            WHERE e.tenant_id = pTenantId
                AND e.ou_id = f.ou_bu_id
                AND f.ou_id = vOuId
                AND e.date_year_month = SUBSTR(vDocDate, 1, 6)
                AND e.ledger_code = vCashbankLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

            -- * 3. Cek balance cash bank (cb_trx_cashbank_balance) not yet used on BKK (Cash Bank Out)
            IF EXISTS(
                    SELECT 1
                    FROM cb_trx_cashbank_balance A
                    WHERE A.payment_id = pPaymentOrderId
                        AND A.doc_type_id = vDocTypeId
                        AND A.flg_payment IN ( vYes, vInProgress )) THEN

                RAISE EXCEPTION 'Payment Order Cash Bank already been used in Cash Bank Out transaction';
            ELSE
                -- remove cb_trx_cashbank_balance
                DELETE FROM cb_trx_cashbank_balance
                WHERE payment_id = pPaymentOrderId
                        AND doc_type_id = vDocTypeId;

                -- update doc status do draft
                UPDATE cb_payment_order
                SET status_doc = vStatusDocDraft,
                    workflow_status = vWorkflowStatusDraft,
                    version = version + 1,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                WHERE payment_order_id = pPaymentOrderId;

                -- reset approval
                                -- do reset approval to draft
                PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pPaymentOrderId, vDocNo, pDatetime, pRemarkApproval);

                -- update previous process no
                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 = pPaymentOrderId || '_' || vDocNo;

                PERFORM f_process_other_ap_balance_for_cancel_submit_document(pTenantId, vDocTypeId, pPaymentOrderId, pDatetime, pUserId);
            END IF;
        ELSE
            RAISE EXCEPTION 'Admin Process Ledger for Cash Bank in year month % is already closed', SUBSTR(vDocDate, 1, 6);
        END IF;
    ELSE
        RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pPaymentOrderId;
    END IF;

END;
$BODY$;

ALTER FUNCTION public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
    OWNER TO sts;

GRANT EXECUTE ON FUNCTION public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying) TO sts;

GRANT EXECUTE ON FUNCTION public.cb_cancel_submit_payment_order_cb(bigint, character varying, bigint, character varying, bigint, character varying, character varying) TO stsdev;