CANCEL SUBMIT CG RECEIPT

-- Adrian, Mar 21, 2017

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

    vEmptyId                            bigint;
    vCGReceiptDocTypeId                 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';
    vCGReceiptDocTypeId := 624;

    vFunctionSubmit := 'cb_submit_cg_receipt';
    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 Receipt 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 = pCGReceiptId AND
        A.doc_type_id = C.doc_type_id AND
        A.doc_type_id = vCGReceiptDocTypeId 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 dan flg_realization harus N
         */
        IF NOT EXISTS (
            SELECT (1)
            FROM cb_cheque_giro_balance A, cb_in_out_cashbank B, cb_in_out_cheque_giro_payment C
            WHERE B.in_out_cashbank_id = pCGReceiptId
                AND B.in_out_cashbank_id = C.in_out_cashbank_id
                AND A.tenant_id = B.tenant_id
                AND A.in_out_cashbank_id = B.in_out_cashbank_id
                AND A.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id
                AND (
                    A.flg_deposit <> vNo
                    OR A.flg_realization <> vNo
                )
        ) THEN

            /*
             * validasi flg_alloc harus N
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM fi_receipt_ar_balance A, cb_in_out_cheque_giro_payment B
                WHERE B.in_out_cashbank_id = pCGReceiptId
                    AND A.receipt_ar_balance_id = B.in_out_cheque_giro_payment_id
                    AND A.flg_alloc <> vNo
            ) THEN

                -- delete data cheque/giro balance
                DELETE
                FROM cb_cheque_giro_balance Z
                WHERE EXISTS (
                    SELECT (1)
                    FROM cb_in_out_cashbank A
                    INNER JOIN cb_in_out_cheque_giro_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
                    WHERE A.in_out_cashbank_id = pCGReceiptId
                        AND Z.tenant_id = A.tenant_id
                        AND Z.in_out_cashbank_id = A.in_out_cashbank_id
                        AND Z.in_out_cheque_giro_payment_id = B.in_out_cheque_giro_payment_id
                );

                -- 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 = pCGReceiptId;

                -- delete data saldo for allocation
                DELETE
                FROM fi_receipt_ar_balance Z
                WHERE EXISTS (
                    SELECT (1)
                    FROM cb_in_out_cheque_giro_payment A
                    WHERE A.in_out_cashbank_id = pCGReceiptId
                        AND Z.receipt_ar_balance_id = A.in_out_cheque_giro_payment_id
                );

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

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

        ELSE
            RAISE EXCEPTION 'Document with id % is already used for realization or is void', pCGReceiptId;
        END IF;

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

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