cb_submit_cb_in_ar

-- Function: cb_submit_cb_in_ar(bigint, character varying, character varying)

-- DROP FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vCBInId                 bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vParentOuId             bigint;
    vJournalType            character varying(20);
    vStatusDraft            character varying(1);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vCashBankCOA            character varying(10);
    vSystemCOA              character varying(10);
    vJournalTrxId           bigint;
    vCashBankId             bigint;
    vDocDate                character varying(8);
    vCashBankAmount         numeric;
    vCurrCode               character varying(5);
    vCostAmount             numeric;

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

    BEGIN
    vEmptyId    := -99;
    vStatusRelease := 'R';
    vParentOuId     := -99;
    vStatusDraft    := 'D';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vCashBankCOA := 'CASHBANK';
    vSystemCOA := 'SYSTEM';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'cb_submit_cb_in_ar' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vCBInId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'cashbankInId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    DELETE FROM tt_journal_trx_item_for_dlg WHERE session_id = pSessionId;

    /*
     * 1.insert/update cash/bank balance
     * 2.insert saldo cashbank in ar ke module finance
     * 3.update status doc cb_in_out_cashbank
     * 4.create jurnal
     */

-- get variable pendukung
/* NK, 27 Jan 2014 diganti dengan cara pakai user defined type untuk data ou dan data doc journal
    SELECT A.doc_date, f_get_parent_ou_bu(A.tenant_id, A.ou_id), f_get_journal_type(A.doc_type_id), B.cashbank_id, B.cashbank_amount, B.curr_code INTO
                vDocDate, vParentOuId, vJournalType, vCashBankId, vCashBankAmount, vCurrCode
    FROM cb_in_out_cashbank A, cb_in_out_cashbank_payment B
    WHERE A.in_out_cashbank_id = vCBInId AND
        A.in_out_cashbank_id = B.in_out_cashbank_id
    LIMIT 1;
*/
    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 = vCBInId 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 = vCBInId 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 = vCBInId AND
            A.curr_code = vCurrCode
        GROUP BY A.in_out_cashbank_id;
    ELSE
        vCostAmount := 0;
    END IF;

    INSERT INTO cb_cashbank_balance
    (tenant_id, ou_id, cashbank_id, cash_bank_date,
    rec_type, curr_code, amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, D.ou_id, vCashBankId, A.doc_date,
        vSignDebit, vCurrCode, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank A
    INNER JOIN m_cashbank_ou D ON D.cashbank_id = vCashBankId
    WHERE A.in_out_cashbank_id = vCBInId AND
        NOT EXISTS (SELECT 1 FROM cb_cashbank_balance C
                    WHERE A.tenant_id = C.tenant_id AND
                            D.ou_id = C.ou_id AND
                            C.cashbank_id = vCashBankId AND
                            A.doc_date = C.cash_bank_date AND
                            C.rec_type = vSignDebit);

    UPDATE cb_cashbank_balance D SET amount = D.amount + (vCashBankAmount - vCostAmount),
                     version = D.version + 1, update_datetime = vDatetime, update_user_id = vUserId
    FROM cb_in_out_cashbank A
    INNER JOIN m_cashbank_ou C ON C.cashbank_id = vCashBankId
    WHERE A.in_out_cashbank_id = vCBInId AND
          D.cash_bank_date = vDocDate AND
          D.tenant_id = pTenantId AND
          D.cashbank_id = vCashBankId AND
          D.ou_id = C.ou_id AND
          D.rec_type = vSignDebit;

/*
 * untuk membuat saldo cash bank in ar, yang nanti akan digunakan untuk alokasi cash bank in AR
 */
    INSERT INTO fi_receipt_ar_balance
    (receipt_ar_balance_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date,
    cashbank_id, partner_id, curr_code, amount, remark,
    flg_alloc, ref_alloc_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.in_out_cashbank_id, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        vCashBankId, A.partner_id, vCurrCode, vCashBankAmount, A.remark,
        'N', vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vCBInId;

-- update cb_in_out_cashbank : status doc to R
    UPDATE cb_in_out_cashbank SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE in_out_cashbank_id = vCBInId;

    /*
     * jurnal :
     * Credit activity gl
     * Credit AR
     * Debit cash/bank
     *
     * Mod by WTC, 20171127, dikarenakan OU jurnal header adalah OU BU, maka sub_ou_id yg dikirimkan ke admin journal juga ou BU
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, (vOuStructure).ou_bu_id, (vDocJournal).journal_type,
        (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vCBInId;

    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

    INSERT INTO gl_journal_trx
    (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, vEmptyId, vEmptyId, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, A.ref_curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vCBInId;

    INSERT INTO tt_journal_trx_item_for_dlg
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark, ou_branch_id, ou_sub_bu_id)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.in_out_cashbank_cost_id,
        A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
        B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
        E.coa_id, B.curr_code, 0, vEmptyId,
        B.cost_amount, A.doc_date, vTypeRate,
        1, 1, 'AR_COST_RECEIPT', B.remark, F.ou_branch_id, F.ou_sub_bu_id
    FROM cb_in_out_cashbank A, cb_in_out_cashbank_cost B, m_activity_gl E, m_ou_structure F
    WHERE  A.in_out_cashbank_id = vCBInId AND
        A.in_out_cashbank_id = B.in_out_cashbank_id AND
        B.activity_gl_id = E.activity_gl_id AND
        A.ou_id = F.ou_id;

    INSERT INTO tt_journal_trx_item_for_dlg
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark, ou_branch_id, ou_sub_bu_id)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_ar_coa_partner(A.tenant_id, A.partner_id), vCurrCode, 0, vEmptyId,
        vCashBankAmount, A.doc_date, vTypeRate,
        1, 1, 'AR_CASHBANK_IN', A.remark, B.ou_branch_id, B.ou_sub_bu_id
    FROM cb_in_out_cashbank A, m_ou_structure B
    WHERE  A.in_out_cashbank_id = vCBInId AND
        A.ou_id = B.ou_id ;

    INSERT INTO tt_journal_trx_item_for_dlg
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark, ou_branch_id, ou_sub_bu_id)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        A.partner_id, vEmptyId, C.cashbank_id, vEmptyId,
        vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
        C.coa_id, vCurrCode, 0, vEmptyId,
        (vCashBankAmount - vCostAmount), A.doc_date, vTypeRate,
        1, 1, 'CASH_BANK', A.remark, E.ou_branch_id, E.ou_sub_bu_id
    FROM cb_in_out_cashbank A, m_cashbank C, m_cashbank_ou D, m_ou_structure E
    WHERE A.in_out_cashbank_id = vCBInId AND
        C.cashbank_id = vCashBankId AND
        C.cashbank_id = D.cashbank_id AND
        D.ou_id = E.ou_id ;

    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, ou_branch_id, ou_sub_bu_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId, A.ou_branch_id, A.ou_sub_bu_id
    FROM tt_journal_trx_item_for_dlg A
    WHERE A.session_id = pSessionId
    AND A.journal_desc IN ('AR_COST_RECEIPT', 'AR_CASHBANK_IN');

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, ou_branch_id, ou_sub_bu_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId, A.ou_branch_id, A.ou_sub_bu_id
    FROM tt_journal_trx_item_for_dlg A
    WHERE A.session_id = pSessionId
    AND A.journal_desc = 'CASH_BANK';

    DELETE FROM tt_journal_trx_item_for_dlg WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying) TO sts;
GRANT EXECUTE ON FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION cb_submit_cb_in_ar(bigint, character varying, character varying) TO stsdev;