cb_submit_cg_realization_20170925

-- Function: cb_submit_cg_realization(bigint, character varying, character varying)
/**
 * Modified by Adrian, Sep 25, 2017
 * Menambahkan perhitungan cost amount pada saat update cb_cashbank_balance
 */

DROP FUNCTION cb_submit_cg_realization(bigint, character varying, character varying);

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

    vProcessId              bigint;
    vDocId                  bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vCashBankCOA            character varying(10);
    vJournalTrxId           bigint;
    vCashBankId             bigint;
    vDocDate                character varying(8);
    vCashBankAmount         numeric;
    vStatusAccept           character varying(10);
    vStatusReject           character varying(10);
    vGroupCoaPiutangCekGiro character varying(20);

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;
    vCostAmount             numeric;
    vCBAmount               numeric;
    vSystemCOA              character varying(10);

    vRecTypeKredit          character varying(1);

    BEGIN
    vEmptyId    := -99;
    vStatusRelease := 'R';
    vStatusDraft    := 'D';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vCashBankCOA := 'CASHBANK';
    vStatusAccept := 'ACCEPT';
    vStatusReject := 'REJECT';
    vGroupCoaPiutangCekGiro := 'AyatSilangCekGiro';
    vSystemCOA := 'SYSTEM';
    vRecTypeKredit := 'K';

    --emptying temp table
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    --get variable
    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'cb_submit_cg_realization' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vDocId
    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';

    --get detail document
    SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vDocId
    LIMIT 1 INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;

    --get cashbankId
    SELECT cashbank_id INTO vCashBankId
    FROM cb_cheque_giro_realization
    WHERE in_out_cashbank_id = vDocId
    LIMIT 1;

    --insert and then update data cashbank balance
    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, C.ou_id, B.cashbank_id, A.doc_date,
        vSignDebit, B.curr_code, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank A
        INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN m_cashbank_ou C ON B.cashbank_id = C.cashbank_id
    WHERE A.in_out_cashbank_id = vDocId AND
        B.realization_status = vStatusAccept AND
        NOT EXISTS (SELECT 1 FROM cb_cashbank_balance D
                    WHERE A.tenant_id = D.tenant_id AND
                            C.ou_id = D.ou_id AND
                            B.cashbank_id = D.cashbank_id AND
                            A.doc_date = D.cash_bank_date AND
                            D.rec_type = vSignDebit)
    GROUP BY A.tenant_id, C.ou_id, B.cashbank_id, A.doc_date, B.curr_code;

    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
    )
    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, C.ou_id, B.cashbank_id, A.doc_date,
        vRecTypeKredit, D.curr_code, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    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 m_cashbank_ou C ON B.cashbank_id = C.cashbank_id
        INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
    WHERE A.in_out_cashbank_id = vDocId AND
        --B.realization_status = vStatusAccept AND
        NOT EXISTS (SELECT 1 FROM cb_cashbank_balance D
                    WHERE A.tenant_id = D.tenant_id AND
                            C.ou_id = D.ou_id AND
                            B.cashbank_id = D.cashbank_id AND
                            A.doc_date = D.cash_bank_date AND
                            D.rec_type = vRecTypeKredit)
    GROUP BY A.tenant_id, C.ou_id, B.cashbank_id, A.doc_date, D.curr_code;

    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 = vDocId 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 = vDatetime,
        update_user_id = vUserId
    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 = vDocId
        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 = vDatetime,
        update_user_id = vUserId
    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 : 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 = vDocId;

    --update data cheque/giro balance yg statusnya ACCEPT
    UPDATE cb_cheque_giro_balance A
    SET flg_realization = 'Y',
        realization_doc_type_id = B.doc_type_id,
        realization_doc_no = B.doc_no,
        realization_doc_date = B.doc_date,
        version = A.version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        remark = C.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
    WHERE B.in_out_cashbank_id = vDocId AND
        A.cheque_giro_balance_id = C.ref_balance_id AND
        C.realization_status = vStatusAccept;

    --update data cheque/giro balance yg statusnya REJECT
    UPDATE cb_cheque_giro_balance A
    SET flg_realization = 'N',
        flg_deposit = 'N',
        version = A.version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        remark = C.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
    WHERE B.in_out_cashbank_id = vDocId AND
        A.cheque_giro_balance_id = C.ref_balance_id AND
        C.realization_status = vStatusReject;

    /*
     * jurnal :
     *      Credit  CHEQUE_GIRO
     * Debit        CHEQUE_GIRO_COST
     * Debit        CASH_BANK
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_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 = vDocId;

    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, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, 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 = vDocId;

    INSERT INTO tt_journal_trx_item
    (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)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        B.partner_id, vEmptyId, B.cashbank_id, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id , vGroupCoaPiutangCekGiro), B.curr_code, 0, vEmptyId,
        B.cheque_giro_amount, A.doc_date, vTypeRate,
        1, 1, 'CHEQUE_GIRO', A.remark
    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 = vDocId AND
        B.realization_status = vStatusAccept;

    INSERT INTO tt_journal_trx_item
    (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)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.in_out_cashbank_cost_id,
        vEmptyId, 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, 'CHEQUE_GIRO_COST', B.remark
    FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_cost B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN m_activity_gl E ON B.activity_gl_id = E.activity_gl_id
    WHERE A.in_out_cashbank_id = vDocId;

    -- SUM All Cost
    IF EXISTS(SELECT 1 FROM cb_in_out_cashbank_cost A WHERE A.in_out_cashbank_id = vDocId) THEN
        SELECT SUM(A.cost_amount) INTO vCostAmount
        FROM cb_in_out_cashbank_cost A
        WHERE A.in_out_cashbank_id = vDocId;
    ELSE
        vCostAmount := 0;
    END IF;

    -- SUM All CashBank for Accept Cheque/Giro
    IF EXISTS(SELECT 1 FROM cb_cheque_giro_realization A WHERE A.in_out_cashbank_id = vDocId AND A.realization_status = vStatusAccept) THEN
        SELECT SUM(A.cheque_giro_amount) INTO vCBAmount
        FROM cb_cheque_giro_realization A
        WHERE A.in_out_cashbank_id = vDocId AND
            A.realization_status = vStatusAccept;
    ELSE
        vCBAmount := 0;
    END IF;

    INSERT INTO tt_journal_trx_item
    (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)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        vEmptyId, vEmptyId, B.cashbank_id, vEmptyId,
        vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
        B.coa_id, B.curr_code, 0, vEmptyId,
        (vCBAmount - vCostAmount) AS amount, A.doc_date, vTypeRate,
        1, 1, 'CASH_BANK', A.remark
    FROM cb_in_out_cashbank A, m_cashbank B
    WHERE A.in_out_cashbank_id = vDocId AND
        B.cashbank_id = vCashBankId;

    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)
    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
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc IN ('CHEQUE_GIRO_COST', 'CASH_BANK');

    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)
    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
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc = 'CHEQUE_GIRO';

    --emptying temp table
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

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