cb_cancel_submit_cb_in_ar_PERBAIKAN_20180604

/**
 * @author fredi, 19 March 2015
 * For cancel submit/approve cash bank in ar
 *
 * custom by fitra 2018-04-27
 * perbaikan validasi kecukupan balance bank untuk bank overdraft lihat limit nya
 */
/**
 * Modified by Adrian, Jun 4, 2018
 * Memperbaiki query hapus gl_journal_trx
 */

CREATE OR REPLACE FUNCTION cb_cancel_submit_cb_in_ar(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; -- for update user id in transaction
    pDatetime               ALIAS FOR $4;
    pCbInArId               ALIAS FOR $5; -- In out cb id
    pRemarkApproval         ALIAS FOR $6; -- Remark for appear in approval reset history
    pPrevProcessNo          ALIAS FOR $7; -- Substitute for renaming prevous process no

    vCashbankLedgerCode     character varying := 'CASH.BANK';
    vStatusLedgerNotDone    character varying := '0';
    vInProgress             character varying := 'I';
    vYes                    character varying := 'Y';
    vFunctionSubmit         character varying := 'cb_submit_cb_in_ar';
    vStatusDocRelease       character varying := 'R';
    vStatusDocDraft         character varying := 'D';
    vWorkflowStatusDraft    character varying := 'DRAFT';
    vDocTypeCbInAr  bigint := 621;
    vSignDebit              character varying := 'D';

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

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

    vCashBankId     bigint;
    vJournalTrxId   bigint;
    vCashBankAmount numeric;
    vCurrCode       character varying;
    vCostAmount     numeric;
    vCurrentCashBankBalance     numeric;

    vBankType               character varying;
    vBankTypeOverDraft      character varying:='O';
    vCreditLimitOverDraft   numeric;

    vEmptyId                bigint := -99;

BEGIN
    /**
     * Cancel submit in ar
     * 1. Find document cb_in_out_cashbank, must release and match doc type
     * 2. Cek status ledger not yet closed
     * 3. Cek fi_receipt_ar_balance not yet used on allocation cashbank ar
     * 4. Cek saldo current cashbank f_get_cashbank_balance - sum(cb in ar) must >= 0
     * 5. DELETE fi_receipt_ar_balance
     * 6. UPDATE cb_cashbank_balance, SET amount = amount - sum(cb in ar)
     * 7. Update cb_in_out_cashbank, set doc_status to D
     * 8. PERFORM cancel gl_cancel_admin_journal_trx
     * 9. DELETE gl_journal_trx
     * 10. DELETE gl_journal_trx_item
     * 11. DELETE gl_journal_trx_mapping
     * 12. PERFORM f_reset_approval_to_draft
     * 13. UPDATE t_process_message
     */

    -- * 1. Find document cb_in_out_cashbank, must release and match doc type
    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_in_out_cashbank A
    INNER JOIN m_document B ON A.doc_type_id = B.doc_type_id
    WHERE A.in_out_cashbank_id = pCbInArId
        AND A.doc_type_id = vDocTypeCbInAr
        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 fi_receipt_ar_balance not yet used on allocation cashbank ar
            IF NOT EXISTS (
                SELECT 1
                FROM fi_receipt_ar_balance A
                WHERE A.receipt_ar_balance_id = pCbInArId
                    AND flg_alloc IN (vInProgress, vYes)
            ) THEN
                --* 4. Cek saldo current cashbank f_get_cashbank_balance - sum(cb in ar) must >= 0
                SELECT A.doc_date AS doc_date, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc,
                    B.cashbank_id AS cashbank_id, B.cashbank_amount AS cashbank_amount, B.curr_code AS curr_code
                FROM cb_in_out_cashbank A,cb_in_out_cashbank_payment B
                WHERE A.in_out_cashbank_id = pCbInArId AND
                    A.in_out_cashbank_id = B.in_out_cashbank_id LIMIT 1 INTO result;

                vDocDate := result.doc_date;
                vCashBankId := result.cashbank_id;
                vCashBankAmount := result.cashbank_amount;
                vCurrCode := result.curr_code;
                vOuStructure := result.ou;
                vDocJournal := result.doc;

                IF EXISTS(SELECT 1 FROM cb_in_out_cashbank_cost A WHERE A.in_out_cashbank_id = pCbInArId AND A.curr_code = vCurrCode) THEN
                    SELECT SUM(A.cost_amount) INTO vCostAmount
                    FROM cb_in_out_cashbank_cost A
                    WHERE A.in_out_cashbank_id = pCbInArId AND
                        A.curr_code = vCurrCode
                    GROUP BY A.in_out_cashbank_id;
                ELSE
                    vCostAmount := 0;
                END IF;

                vCurrentCashBankBalance := f_get_cashbank_balance(vCashBankId, vDocDate);

                SELECT flg_cash_bank, credit_limit INTO vBankType, vCreditLimitOverDraft
                FROM m_cashbank WHERE cashbank_id =vCashBankId;

                IF (vCurrentCashBankBalance - (vCashBankAmount - vCostAmount) >= 0 OR
                   (vBankType = vBankTypeOverDraft AND (vCurrentCashBankBalance - (vCashBankAmount - vCostAmount) + vCreditLimitOverDraft >= 0 ))) THEN

                     -- * 5. DELETE fi_receipt_ar_balance
                     DELETE FROM fi_receipt_ar_balance A
                     WHERE A.receipt_ar_balance_id = pCbInArId;

                     --* 6. UPDATE cb_cashbank_balance, SET amount = amount - sum(cb in ar)
                     UPDATE cb_cashbank_balance A
                     SET amount = A.amount - (vCashBankAmount - vCostAmount),
                        version = A.version + 1,
                        update_datetime = pDatetime,
                        update_user_id = pUserId
                     FROM m_cashbank_ou D
                     WHERE A.cash_bank_date = vDocDate
                          AND D.cashbank_id = A.cashbank_id
                          AND A.tenant_id = pTenantId
                          AND A.cashbank_id = vCashBankId
                          AND A.ou_id = D.ou_id
                          AND A.rec_type = vSignDebit;

                    -- * 7. Update cb_in_out_cashbank, set doc_status to DRAFT
                    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 = pCbInArId;

                    --* 8. 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);


                    -- pre 9 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 = vDocTypeId
                        AND doc_id = pCbInArId
                        AND doc_no = vDocNo
                        AND doc_date = vDocDate
                        AND ou_bu_id = (vOuStructure).ou_bu_id
                        AND ou_branch_id = vEmptyId
                        AND ou_sub_bu_id = vEmptyId;

                    -- * 9. DELETE gl_journal_trx
                    DELETE FROM gl_journal_trx
                    WHERE journal_trx_id = vJournalTrxId;

                    -- * 10. DELETE gl_journal_trx_item
                    DELETE FROM gl_journal_trx_item
                    WHERE journal_trx_id = vJournalTrxId;

                    -- * 11. DELETE gl_journal_trx_mapping
                    DELETE FROM gl_journal_trx_mapping
                    WHERE journal_trx_id = vJournalTrxId;

                    -- * 12. PERFORM f_reset_approval_to_draft
                    PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pCbInArId, vDocNo, pDatetime, pRemarkApproval);

                    -- * 13. 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 = pCbInArId || '_' || vDocNo;

                ELSE
                    IF (vBankType = vBankTypeOverDraft) THEN
                        RAISE EXCEPTION 'Cancellation will cause balance over credit limit. Current balance: %, CB In AR Amount: %, Credit Limit: %',
                                                                                TRIM(to_char(vCurrentCashBankBalance,'999,999,999,999.99')),
                                                                                TRIM(to_char(vCashBankAmount,'999,999,999,999.99')),
                                                                                TRIM(to_char(vCreditLimitOverDraft,'999,999,999,999.99'));
                    ELSE
                        RAISE EXCEPTION 'Not enough cash/bank balance. Current cash/bank balance: %, CB In AR: %' ,TRIM(to_char(vCurrentCashBankBalance,'999,999,999,999.99')), TRIM(to_char(vCashBankAmount,'999,999,999,999.99'));
                    END IF;
                END IF;
            ELSE
                RAISE EXCEPTION 'Cashbank In AR document already been used by Allocation Cashbank AR';
            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', pCbInArId;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/