cb_cancel_submit_cg_realization_20170925

-- Adrian, Mar 22, 2017
/**
 * Modified by Adrian, Sep 25, 2017
 * Menambahkan perhitungan cost amount pada saat update cb_cashbank_balance
 */

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

    vDocType                            bigint;
    vEmptyId                            bigint;
    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying;
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vStatusDocVoid                      character varying;
    vWorkflowStatusDraft                character varying;
    vYes                                character varying;
    vNo                                 character varying;
    vZero                               bigint;

    vOuId                               bigint;
    vJournalTrxId                       bigint;
    vDocNo                              character varying;
    vDocDate                            character varying;
    vScheme                             character varying;

    vDocJournal                         DOC_JOURNAL%ROWTYPE;
    vOuStructure                        OU_BU_STRUCTURE%ROWTYPE;
    result                              RECORD;

    vLedgerCode                         character varying;
    vStatusLedgerNotDone                character varying;

    vOuStructureJournalItem             OU_BU_STRUCTURE%ROWTYPE;

    vRefAmount                          numeric;
    vSignDebit                          character varying(1);
    vSignCredit                         character varying(1);
    vTypeRate                           character varying(3);
    vActivityCOA                        character varying(10);
    vRefId                              bigint;
    vRefDocTypeId                       bigint;
    vCbInOtherId                        bigint;
    vPaymentAmountConversion            numeric;

    vStatusAccept                       character varying(10);
    vStatusReject                       character varying(10);
    vEmpty                              character varying;

    vRecTypeKredit                      character varying(1);

BEGIN

    vDocType := 625;
    vEmptyId := -99;
    vFunctionSubmit := 'cb_submit_cg_realization';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress := 'I';
    vStatusDocVoid := 'V';
    vWorkflowStatusDraft := 'DRAFT';
    vYes := 'Y';
    vNo := 'N';
    vZero := 0;

    vLedgerCode := 'CASH.BANK';
    vStatusLedgerNotDone := '0';

    vSignDebit := 'D';

    vStatusAccept := 'ACCEPT';
    vStatusReject := 'REJECT';
    vEmpty := '';

    vRecTypeKredit := 'K';

    --RAISE EXCEPTION 'Function Submit For Document Cheque Giro Realization 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 = pDocId AND
        A.doc_type_id = C.doc_type_id AND
        A.doc_type_id = vDocType AND
        A.status_doc = vStatusDocReleased
    INTO result;

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

        -- 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 = vLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

            /*
             * validasi flg_realization for accepted cg
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM cb_cheque_giro_balance A, cb_in_out_cashbank B, cb_cheque_giro_realization C
                WHERE B.in_out_cashbank_id = pDocId
                    AND B.in_out_cashbank_id = C.in_out_cashbank_id
                    AND A.cheque_giro_balance_id = C.ref_balance_id
                    AND C.realization_status = vStatusAccept
                    AND flg_realization <> vYes
            ) THEN

                /*
                 * validasi flg_realization dan flg_deposit for rejected cg
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM cb_cheque_giro_balance A, cb_in_out_cashbank B, cb_cheque_giro_realization C
                    WHERE B.in_out_cashbank_id = pDocId
                        AND B.in_out_cashbank_id = C.in_out_cashbank_id
                        AND A.cheque_giro_balance_id = C.ref_balance_id
                        AND C.realization_status = vStatusReject
                        AND (
                            flg_realization <> vNo
                            OR flg_deposit <> vNo
                        )
                ) THEN

                    --update data cashbank balance
                    WITH amount AS (
                        SELECT A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id, SUM(B.cheque_giro_amount) AS cheque_giro_amount
                        FROM cb_in_out_cashbank A
                            INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
                        WHERE A.in_out_cashbank_id = pDocId AND
                            B.realization_status = vStatusAccept
                        GROUP BY A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id
                    )
                    UPDATE cb_cashbank_balance D
                    SET amount = D.amount - A.cheque_giro_amount,
                        version = D.version + 1,
                        update_datetime = pDatetime,
                        update_user_id = pUserId
                    FROM amount A
                    WHERE D.tenant_id = A.tenant_id AND
                          D.ou_id = A.ou_id AND
                          D.cashbank_id = A.cashbank_id AND
                          D.cash_bank_date = A.doc_date AND
                          D.rec_type = vSignDebit;

                    WITH cost_amount AS (
                        WITH tt_in_out_cashbank AS(
                            SELECT A.in_out_cashbank_id, B.cashbank_id
                            FROM cb_in_out_cashbank A
                            INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
                            GROUP BY A.in_out_cashbank_id, B.cashbank_id
                        )
                        SELECT A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id, SUM(C.cost_amount) AS cost_amount
                        FROM cb_in_out_cashbank A
                        INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
                        INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
                        WHERE A.in_out_cashbank_id = pDocId
                        GROUP BY A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id
                    )
                    UPDATE cb_cashbank_balance D
                    SET amount = D.amount - A.cost_amount,
                        version = D.version + 1,
                        update_datetime = pDatetime,
                        update_user_id = pUserId
                    FROM cost_amount A
                    WHERE D.tenant_id = A.tenant_id AND
                          D.ou_id = A.ou_id AND
                          D.cashbank_id = A.cashbank_id AND
                          D.cash_bank_date = A.doc_date AND
                          D.rec_type = vRecTypeKredit;

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

                    --update data cheque/giro balance yg statusnya ACCEPT
                    WITH old_remark AS (
                        SELECT log_deposit_administation_cg_id, remark, cheque_giro_balance_id,
                            ROW_NUMBER() OVER(
                                PARTITION BY cheque_giro_balance_id
                                ORDER BY log_deposit_administation_cg_id DESC
                            ) AS row_number
                        FROM cb_log_deposit_administration_cheque_giro
                    )
                    UPDATE cb_cheque_giro_balance A
                    SET flg_realization = vNo,
                        realization_doc_type_id = vEmptyId,
                        realization_doc_no = vEmpty,
                        realization_doc_date = vEmpty,
                        version = A.version + 1,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        remark = D.remark
                    FROM cb_in_out_cashbank B
                        INNER JOIN cb_cheque_giro_realization C ON B.in_out_cashbank_id = C.in_out_cashbank_id
                        INNER JOIN old_remark D ON D.cheque_giro_balance_id = C.ref_balance_id
                    WHERE B.in_out_cashbank_id = pDocId AND
                        A.cheque_giro_balance_id = C.ref_balance_id AND
                        D.row_number = 1 AND
                        C.realization_status = vStatusAccept;

                    --update data cheque/giro balance yg statusnya REJECT
                    WITH old_remark AS (
                        SELECT log_deposit_administation_cg_id, remark, cheque_giro_balance_id,
                            ROW_NUMBER() OVER(
                                PARTITION BY cheque_giro_balance_id
                                ORDER BY log_deposit_administation_cg_id DESC
                            ) AS row_number
                        FROM cb_log_deposit_administration_cheque_giro
                    )
                    UPDATE cb_cheque_giro_balance A
                    SET flg_realization = vNo,
                        flg_deposit = vYes,
                        version = A.version + 1,
                        update_datetime = pDatetime,
                        update_user_id = pUserId,
                        remark = D.remark
                    FROM cb_in_out_cashbank B
                        INNER JOIN cb_cheque_giro_realization C ON B.in_out_cashbank_id = C.in_out_cashbank_id
                        INNER JOIN old_remark D ON D.cheque_giro_balance_id = C.ref_balance_id
                    WHERE B.in_out_cashbank_id = pDocId AND
                        A.cheque_giro_balance_id = C.ref_balance_id AND
                        D.row_number = 1 AND
                        C.realization_status = vStatusReject;

                    -- PERFORM cancel gl_cancel_admin_journal_trx
                    PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'DAILY', pDatetime, pUserId);

                    -- Find journal trx id
                    SELECT journal_trx_id INTO vJournalTrxId
                    FROM gl_journal_trx
                    WHERE tenant_id = pTenantId
                        AND journal_type = (vDocJournal).journal_type
                        AND doc_type_id = vDocType
                        AND doc_id = pDocId
                        AND doc_no = vDocNo
                        AND doc_date = vDocDate
                        AND ou_bu_id = (vOuStructure).ou_bu_id
                        AND ou_branch_id = (vOuStructure).ou_branch_id
                        AND ou_sub_bu_id = (vOuStructure).ou_sub_bu_id;

                    -- DELETE gl_journal_trx
                    DELETE FROM gl_journal_trx
                    WHERE journal_trx_id = vJournalTrxId;

                    -- DELETE gl_journal_trx_item
                    DELETE FROM gl_journal_trx_item
                    WHERE journal_trx_id = vJournalTrxId;

                    -- DELETE gl_journal_trx_mapping
                    DELETE FROM gl_journal_trx_mapping
                    WHERE journal_trx_id = vJournalTrxId;

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

                ELSE
                    RAISE EXCEPTION 'Item in document with id % is already used for other documnet', pDocId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Item in document with id % is already used for other documnet', pDocId;
            END IF;

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

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

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