CANCEL SUBMIT CG VOID

-- Adrian, Mar 21, 2017

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

    vEmptyId                            bigint;
    vCGVoidDocTypeId                    bigint;
    vFlagNo                             character varying(1);
    vRoundingModeNonTax                 character varying(5);
    vOuStructure                        OU_BU_STRUCTURE%ROWTYPE;
    result                              RECORD;

    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying(1);
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vStatusDocVoid                      character varying(1);
    vWorkflowStatusDraft                character varying;
    vPoId                               bigint;
    vZero                               bigint;

    vOuId                               bigint;
    vScheme                             character varying;
    vDocNo                              character varying;

    vEmpty                              character varying;
    vYes                                character varying;
    vNo                                 character varying;

BEGIN

    vEmptyId := -99;
    vFlagNo := 'N';
    vCGVoidDocTypeId := 629;

    vFunctionSubmit := 'cb_submit_cg_void';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress = 'I';
    vStatusDocVoid := 'V';
    vWorkflowStatusDraft := 'DRAFT';
    vZero := 0;

    vEmpty := '';
    vYes := 'Y';
    vNo := 'N';

    --RAISE EXCEPTION 'Function Submit For Document Cheque Giro Void is not yet Created';

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou, A.ou_id, A.doc_no, A.doc_date,
        f_get_document_journal(A.doc_type_id) as doc, C.scheme
    FROM cb_in_out_cashbank A, m_document C
    WHERE A.in_out_cashbank_id = pCGVoidId AND
        A.doc_type_id = C.doc_type_id AND
        A.doc_type_id = vCGVoidDocTypeId AND
        A.status_doc = vStatusDocReleased
    INTO result;

    IF FOUND THEN
        vOuStructure := result.ou;
        vOuId := result.ou_id;
        vDocNo := result.doc_no;
        vScheme := result.scheme;

        /*
         * validasi flg_deposit harus N dan flg_realization harus Y
         */
        IF NOT EXISTS (
            SELECT (1)
            FROM cb_cheque_giro_balance A, cb_cheque_giro_void B, cb_in_out_cheque_giro_payment C
            WHERE B.in_out_cashbank_id = pCGVoidId
                AND B.ref_id = C.in_out_cheque_giro_payment_id
                AND A.tenant_id = pTenantId
                AND A.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id
                AND (
                    A.flg_deposit <> vNo
                    OR A.flg_realization <> vYes
                )
        ) THEN

            /*
             * validasi flg_alloc harus V
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM fi_receipt_ar_balance A, cb_cheque_giro_void B, cb_in_out_cheque_giro_payment C
                WHERE B.in_out_cashbank_id = pCGVoidId
                    AND B.ref_id = C.in_out_cheque_giro_payment_id
                    AND A.receipt_ar_balance_id = C.in_out_cheque_giro_payment_id
                    AND A.flg_alloc <> vStatusDocVoid
            ) THEN

                --update data cheque/giro balance
                UPDATE cb_cheque_giro_balance A
                SET flg_realization = vNo,
                    version = A.version + 1,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                FROM cb_cheque_giro_void B
                    INNER JOIN cb_in_out_cheque_giro_payment C ON B.ref_id = C.in_out_cheque_giro_payment_id
                WHERE A.tenant_id = pTenantId AND
                    A.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id AND
                    B.in_out_cashbank_id = pCGVoidId;

                -- update cb_in_out_cashbank
                UPDATE cb_in_out_cashbank
                SET status_doc = vStatusDocDraft,
                    workflow_status = vWorkflowStatusDraft,
                    version = version + 1,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                WHERE in_out_cashbank_id = pCGVoidId;

                -- update data AR Balance
                UPDATE fi_receipt_ar_balance A
                SET flg_alloc = vNo,
                    remark = D.remark,
                    version = A.version + 1,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                FROM cb_cheque_giro_void B
                    INNER JOIN cb_in_out_cheque_giro_payment C ON B.ref_id = C.in_out_cheque_giro_payment_id
                    INNER JOIN cb_in_out_cashbank D ON D.in_out_cashbank_id = C.in_out_cashbank_id
                WHERE B.in_out_cashbank_id = pCGVoidId AND
                    A.receipt_ar_balance_id = C.in_out_cheque_giro_payment_id AND
                    A.flg_alloc = vStatusDocVoid;

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

            ELSE
                RAISE EXCEPTION 'Document with id % is already allocated', pCGVoidId;
            END IF;

        ELSE
            RAISE EXCEPTION 'Document with id % is already used in other document', pCGVoidId;
        END IF;

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

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