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

    vProcessId          bigint;
    vInOutCbId          bigint;
    vUserId             bigint;
    vDatetime           character varying(14);
   vFlagNo              character varying(1);
   vEmptyValue          character varying(1);
   vStatusDraft         character varying(1);
   vEmptyId             bigint;
   vStatusRelease       character varying(1);
   vTypeRateCom         character varying(50);
   vJournalDescDCP      character varying(50);
   vJournalDescCB      character varying(50);
   result               RECORD;
   vJournalTrxId        bigint;
--   vJournalTypeCB       character varying(50);
--   vOuBuId              bigint;
--   vOuBranchId          bigint;
--   vOuSubBuId           bigint;

   vDirectCPDocTypeId       bigint;
   vDocTypeCashBankOut      bigint;
   vRefDocTypeId            bigint;
   vRefAmount               numeric;
   vPaymentId               bigint;
   vRefId                   bigint;
   vOuStructure             OU_BU_STRUCTURE%ROWTYPE;
   vDocJournal              DOC_JOURNAL%ROWTYPE;
   vCbOutNo                 character varying(30);
   vCbOutDate               character varying(14);

   vSignDebit               character varying(1);
   vSignCredit              character varying(1);
   vTypeRate                character varying(3);
   vActivityCOA             character varying(10);
   vCashBankCOA             character varying(10);


BEGIN
    vFlagNo := 'N';
   vEmptyValue  := '';
   vEmptyId := -99;
   vStatusRelease := 'R';
   vJournalDescDCP := 'DIRECT_COST_PAYMENT';
   vJournalDescCB := 'CASH_BANK';
   vSignDebit := 'D';
   vSignCredit := 'C';
   vTypeRate := 'COM';
   vActivityCOA := 'ACTIVITY';
   vCashBankCOA := 'CASHBANK';
   vStatusDraft := 'D';
   vDocTypeCashBankOut := 611;

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

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

    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';

   INSERT INTO cb_trx_cashbank_balance
   (tenant_id, ou_id, doc_type_id, payment_id, payment_doc_no,
  payment_doc_date, payment_remark, partner_id, partner_bank_id, curr_code,
  amount, due_date, flg_payment, ref_doc_type_id, ref_id,
  version, create_datetime, create_user_id, update_datetime, update_user_id)
  SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no,
      A.doc_date, A.remark, A.partner_id, vEmptyId, A.ref_curr_code,
      SUM(B.cost_amount), A.due_date, vFlagNo, vEmptyId, vEmptyId,
      0, vDatetime, vUserId, vDatetime, vUserId
  FROM cb_in_out_cashbank A, cb_in_out_cashbank_cost B
  WHERE A.in_out_cashbank_id = vInOutCbId
    AND A.in_out_cashbank_id = b.in_out_cashbank_id
  GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.remark, A.partner_id, A.ref_curr_code, A.due_date;

  UPDATE cb_in_out_cashbank SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  WHERE in_out_cashbank_id = vInOutCbId;

  SELECT A.doc_type_id, A.ref_doc_type_id, A.ref_id, A.ref_amount, B.payment_id AS payment_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(611) as doc,
                A.doc_no, A.doc_date
    FROM cb_in_out_cashbank A, cb_trx_cashbank_balance B
    WHERE A.in_out_cashbank_id = vInOutCbId
        AND A.in_out_cashbank_id = B.payment_id INTO result;

    vDirectCPDocTypeId := result.doc_type_id;
    vRefDocTypeId := result.ref_doc_type_id;
    vRefAmount := result.ref_amount;
    vPaymentId := result.payment_id;
    vRefId := result.ref_id;
    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vCbOutNo := result.doc_no;
    vCbOutDate := result.doc_date;

    -- update flag cb_trx_cashbank_balance
    UPDATE cb_trx_cashbank_balance SET flg_payment = 'Y', ref_doc_type_id = vDirectCPDocTypeId, ref_id = vInOutCbId,
                                        version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE payment_id = vInOutCbId;

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

    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, ref_doc_no, ref_doc_date, 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, vDocTypeCashBankOut, 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.doc_type_id, A.in_out_cashbank_id, vEmptyValue, vEmptyValue, 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 = vInOutCbId;

    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 pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        vEmptyId, vEmptyId,
        B.partner_id, vEmptyId, vEmptyId, C.ou_rc_id,
        C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id,
        D.coa_id, C.curr_code, 0, vEmptyId,
        C.payment_amount, B.doc_date, vTypeRate,
        1, 1, vJournalDescDCP, C.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM gl_journal_trx A
    INNER JOIN cb_in_out_cashbank B ON A.doc_id = B.in_out_cashbank_id AND A.ref_doc_type_id = B.doc_type_id
    INNER JOIN cb_in_out_cashbank_cost C ON B.in_out_cashbank_id = C.in_out_cashbank_id
    INNER JOIN m_activity_gl D ON C.activity_gl_id = D.activity_gl_id
    WHERE A.journal_trx_id = vJournalTrxId
    AND B.in_out_cashbank_id = vInOutCbId;

    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 pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        B.ref_doc_type_id, B.in_out_cashbank_id,
        B.partner_id, vEmptyId, D.cashbank_id, vEmptyId,
        vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
        E.coa_id, D.curr_code, 0, vEmptyId,
        D.payment_amount, B.doc_date, vTypeRate,
        1, 1, vJournalDescCB, B.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM gl_journal_trx A
    INNER JOIN cb_in_out_cashbank B ON A.doc_id = B.in_out_cashbank_id AND A.ref_doc_type_id = B.doc_type_id
    INNER JOIN cb_in_out_cashbank_payment D ON B.in_out_cashbank_id = D.in_out_cashbank_id
    INNER JOIN m_cashbank E ON D.cashbank_id = E.cashbank_id
    WHERE A.journal_trx_id = vJournalTrxId
    AND B.in_out_cashbank_id = vInOutCbId;


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