func_rekap_bank

CREATE OR REPLACE FUNCTION public.r_report_rekap_mutation_cashbank(
    character varying,
    bigint,
    character varying,
    bigint,
    bigint,
    character varying,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetailSaldoAwal         REFCURSOR := 'refDetailSaldoAwal';
    pRefDetailOU            REFCURSOR := 'refDetailOU';
  pRefDetailPartner         REFCURSOR := 'refDetailPartner';
  pRefDetailActivity            REFCURSOR := 'refDetailActivity';
  pRefDetailCoa         REFCURSOR := 'refDetailCoa';
  pSessionId            ALIAS FOR $1;
  pTenantId           ALIAS FOR $2;
  pDatetime           ALIAS FOR $3;
    pOuId                   ALIAS FOR $4;
    pCashbankId         ALIAS FOR $5;
    pDateStart          ALIAS FOR $6;
    pDateEnd              ALIAS FOR $7;

  vRecordTypeSaldoAwal character varying := 'A';
  vSignCredit     character varying := 'C';
  vSignDebit      character varying := 'D';
  vStatusSubmit                 character varying :='S';
  vStatusVoid               character varying :='V';
  vYes            character varying := 'Y';
  vFlagCash       character varying := 'C';
  vTrxTypeBeginningBalance character varying := 'Beginning Balance';
  vStatusApproved           character varying := 'APPROVED';
  vStatusAccept                 character varying := 'ACCEPT';

  vEmptyId              bigint := -99;
    vEmpty                  character varying(1) := ' ';
  vEmptyValue                   character varying := '';

  vDocTypeCashBankOut               bigint := 611;
  vDocTypePaymentOrderAP            bigint := 231;
  vDocTypePaymentOrderNonAP         bigint := 601;
  vDocTypeRequestCashAdvance            bigint := 602;
  vDocTypeCashAdvanceSettlement         bigint := 603;
  vDocTypeCashBankInAr      bigint := 621;
  vDocTypeEdcSettlement     bigint := 622;
  vDocTypeCashBankInOther       bigint := 623;
  vDocTypeCgRealization     bigint := 625;
  vDocTypeConversionCBInOP          bigint := 626;
  vDocTypeConversionCBInPO          bigint := 627;
  vDocTypeVoidConversionCBIn            bigint := 630;

  vDateMinusOneDay character varying;

BEGIN
  DELETE FROM tt_rekap_transaksi_cashbank WHERE session_id = pSessionId;

  --  1. Get data saldo awal cash/bank
  --  2. Get data trx debit - kredit per OU
  --  3. Get data trx debit - kredit Hutang Dagang per Partner
  --  4. Get data trx debit - kredit per Activity Gl
  --  5. Get data total Bon Sementara (Cash Advance)
  --  6. Get data total Piutang Karyawan(?)

  SELECT TO_CHAR(pDateStart::date - INTERVAL '1 day', 'YYYYMMDD') into vDateMinusOneDay;

  /* 1. Get data saldo awal cash/bank */
  WITH calc_beggining_balance AS (
    WITH beggining_balance AS (
      -- get saldo awal terkakhir (rec_type = 'A')
      SELECT A.tenant_id, A.cashbank_id, C.flg_cash_bank, MAX(A.cash_bank_date) AS last_date, SUM(A.amount) AS bg_amount
      FROM cb_cashbank_balance A
      INNER JOIN m_cashbank_assignment_ou B ON A.cashbank_id = B.cashbank_id
      INNER JOIN m_cashbank C ON A.cashbank_id = C.cashbank_id
      WHERE A.tenant_id = pTenantId
      AND B.ou_id = pOuId
      AND A.rec_type = vRecordTypeSaldoAwal
      AND A.cash_bank_date <= pDateStart
      GROUP BY A.tenant_id, A.cashbank_id, C.flg_cash_bank
      ORDER BY A.cashbank_id
    ), cb_beggining AS (
      -- get total saldo dari tanggal terakhir saldo awal sampai H-1 tanggal filter
      SELECT A.cashbank_id, COALESCE(SUM(CASE WHEN A.rec_type = 'K' THEN A.amount * -1 ELSE A.amount END), 0) AS amount
      FROM cb_cashbank_balance A
      INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
      INNER JOIN beggining_balance B ON A.cashbank_id = B.cashbank_id AND A.tenant_id = B.tenant_id
          WHERE A.tenant_id = B.tenant_id
          AND C.ou_id = pOuId
          AND A.rec_type <> vRecordTypeSaldoAwal
          AND A.cash_bank_date >= B.last_date
          AND A.cash_bank_date <= vDateMinusOneDay
          GROUP BY A.cashbank_id
    )
    SELECT A.cashbank_id, A.flg_cash_bank, A.bg_amount + COALESCE(B.amount, 0) AS saldo_amount
      FROM beggining_balance A
      LEFT OUTER JOIN cb_beggining B ON A.cashbank_id = B.cashbank_id
    UNION ALL
    --jika tidak ada tanggal saldo awal
    SELECT A.cashbank_id, B.flg_cash_bank, COALESCE(SUM(CASE WHEN A.rec_type = 'K' THEN A.amount * -1 ELSE A.amount END), 0) AS saldo_amount
        FROM cb_cashbank_balance A
        INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
        INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND C.ou_id = pOuId
        AND A.cash_bank_date <= vDateMinusOneDay
        AND NOT EXISTS (
            SELECT 1
            FROM beggining_balance X WHERE A.cashbank_id = X.cashbank_id
        )
        GROUP BY A.cashbank_id, B.flg_cash_bank
  ) INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type,
    cash_bank_type, debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, vTrxTypeBeginningBalance,
  CASE WHEN A.flg_cash_bank = 'C' THEN 'KAS' ELSE 'BANK' END AS jenis_bank, 0, 0, SUM(A.saldo_amount)
  FROM calc_beggining_balance A
  GROUP BY jenis_bank;

  /* 2. Get data trx debit - kredit Transfer */
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    B.receive_amount AS debt_amount, 0 AS credit_amount, 0
  FROM cb_transfer_cashbank A
  INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_to_id = Z.cashbank_id
  INNER JOIN m_cashbank G ON B.cashbank_to_id = G.cashbank_id
  INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
        AND A.doc_type_id = O.doc_type_id
        AND A.tenant_id = O.tenant_id
  INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        AND A.cashbank_id = P.cashbank_id
  INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  WHERE A.tenant_id = pTenantId
  AND Z.ou_id = pOuId
  AND A.workflow_status = vStatusApproved
  AND P.sign_journal = vSignCredit
  AND A.doc_date BETWEEN pDateStart AND pDateEnd

  UNION ALL

  SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(X.ou_id), f_get_ou_name(X.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    0, B.transfer_amount, 0
  FROM cb_transfer_cashbank A
  INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  INNER JOIN m_cashbank_assignment_ou X ON B.cashbank_to_id = X.cashbank_id
  INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
        AND A.doc_type_id = O.doc_type_id
        AND A.tenant_id = O.tenant_id
  INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        AND X.cashbank_id = P.cashbank_id
        --AND O.doc_type_id = P.ref_doc_type_id
  INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  WHERE A.tenant_id = pTenantId
  AND C.ou_id = pOuId
  AND A.workflow_status = vStatusApproved
  AND P.sign_journal = vSignDebit
  AND A.doc_date BETWEEN pDateStart AND pDateEnd

  UNION ALL

  SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(B.ou_to_id), f_get_ou_name(B.ou_to_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, C.payment_amount, 0
  FROM cb_transfer_cashbank A
  INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  INNER JOIN cb_transfer_cashbank_cost C ON A.transfer_cashbank_id = C.transfer_cashbank_id
  INNER JOIN m_cashbank_assignment_ou D ON A.cashbank_id = D.cashbank_id
  INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  INNER JOIN m_coa L ON K.coa_id = L.coa_id
  INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  WHERE A.tenant_id = pTenantId
  AND D.ou_id = pOuId
  AND A.workflow_status = vStatusApproved
  AND A.doc_date BETWEEN pDateStart AND pDateEnd;

  /* 3. Get data trx debit - kredit CB Out */
  -- CB Out dari Payment Order (AP)
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    partner_code, partner_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    E.partner_code, E.partner_name,
    0, B.cashbank_amount, 0
  FROM cb_in_out_cashbank A
  INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  INNER JOIN m_partner E ON A.partner_id = E.partner_id
  INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
        AND A.doc_type_id = O.doc_type_id
        AND A.tenant_id = O.tenant_id
  INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  WHERE A.tenant_id = pTenantId
  AND Z.ou_id = pOuId
  AND A.doc_date BETWEEN pDateStart AND pDateEnd
  AND A.workflow_status = vStatusApproved
  AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  AND P.line_no = 1
  AND A.doc_type_id = vDocTypeCashBankOut
  AND A.ref_doc_type_id = vDocTypePaymentOrderAP;

  -- CB Out dari Payment Order (Non AP)
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, H.add_amount, 0
  FROM cb_in_out_cashbank A
  INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.trx_cashbank_balance_id
  INNER JOIN cb_payment_order D ON C.payment_id = D.payment_order_id AND C.doc_type_id = D.doc_type_id
  INNER JOIN cb_payment_order_cost H ON D.payment_order_id = H.payment_order_id
  INNER JOIN m_activity_gl K ON H.activity_gl_id = K.activity_gl_id
  INNER JOIN m_coa L ON L.coa_id = K.coa_id
  INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  WHERE A.tenant_id = pTenantId
  AND Z.ou_id = pOuId
  AND A.doc_date BETWEEN pDateStart AND pDateEnd
  AND A.workflow_status = vStatusApproved
  AND A.doc_type_id = vDocTypeCashBankOut
  AND A.ref_doc_type_id = vDocTypePaymentOrderNonAP;

  -- CB Out dari Req Cash Advance
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(D.ou_id), f_get_ou_name(D.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    0, B.cashbank_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
            AND A.doc_type_id = O.doc_type_id
            AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeCashBankOut
        AND A.ref_doc_type_id = vDocTypeRequestCashAdvance;

  /* 4. Get data trx debit - kredit CB In */
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  --CB In Partner Receive
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    B.cashbank_amount, 0, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
            AND A.doc_type_id = O.doc_type_id
            AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
            AND O.doc_id = P.ref_id
            AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeCashBankInAr

  UNION ALL
  --CB In Partner Receive (cost)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, C.cost_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment 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
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeCashBankInAr
        AND C.cost_amount <> 0;

  --CB In Other
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    C.cost_amount, 0, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment 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
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeCashBankInOther;

  /* 5. Get data trx debit - kredit CAS */
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  -- Cash Advance Settlement (pengembalian BS)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    E.cashbank_amount, 0, 0
  FROM cb_advance_settle A
        INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
        INNER JOIN cb_in_out_cashbank D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.trx_cashbank_balance_id
        INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON D.in_out_cashbank_id = O.doc_id
                    AND D.doc_type_id = O.doc_type_id
                    AND D.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeCashAdvanceSettlement

  UNION ALL
  -- Cash Advance Settlement (cost activity gl)
  SELECT pSessionId, pTenantId, 'Mutation OU',
    CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
                CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
                    f_get_ou_code(A.ou_id)
                ELSE f_get_ou_code(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
    ELSE f_get_ou_code(COALESCE(F.ou_branch_id, A.ou_id)) END,
    CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
                CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
                    f_get_ou_name(A.ou_id)
                ELSE f_get_ou_name(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
    ELSE f_get_ou_name(COALESCE(F.ou_branch_id, A.ou_id)) END,
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, B.cost_amount, 0
  FROM cb_advance_settle A
        INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
        INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
        INNER JOIN cb_in_out_cashbank D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.trx_cashbank_balance_id
        INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
        LEFT OUTER JOIN cb_advance_settle_cost_custom_for_sasa F ON B.advance_settle_cost_id = F.advance_settle_cost_id
        INNER JOIN m_activity_gl K ON B.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeCashAdvanceSettlement;

  /* 6. Get data trx debit - kredit EDC Settlement */
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(Y.ou_id), f_get_ou_name(Y.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    B.cashbank_amount, 0, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_edc E ON A.in_out_cashbank_id = E.in_out_cashbank_id AND A.tenant_id = E.tenant_id
        INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
        INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
        INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeEdcSettlement

  UNION ALL
    -- EDC  Settlement -> nilai cost
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(Y.ou_id), f_get_ou_name(Y.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, C.cost_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment 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
        INNER JOIN cb_in_out_cashbank_edc E ON A.in_out_cashbank_id = E.in_out_cashbank_id AND A.tenant_id = E.tenant_id
        INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
        INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeEdcSettlement
        AND C.cost_amount <> 0;

  /* 7. Get data trx debit - kredit Conversion CB In */
  -- Conversion Other to Partner
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, B.cashbank_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON X.in_out_cashbank_id = O.doc_id
            AND X.doc_type_id = O.doc_type_id
            AND X.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        INNER JOIN m_activity_gl K ON P.activity_gl_id = K.activity_gl_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('ACTIVITY')
        AND A.doc_type_id = vDocTypeConversionCBInOP

  UNION ALL

  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(C.ou_id_cb_in_partner), f_get_ou_name(C.ou_id_cb_in_partner),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    B.cashbank_amount, 0, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_ext C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
            AND A.doc_type_id = O.doc_type_id
            AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
            AND O.doc_id = P.ref_id
            AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM')
        AND A.doc_type_id = vDocTypeConversionCBInOP;

  -- Conversion Other to Partner (cost)
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, C.cost_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment 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
        INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeConversionCBInOP;

  -- Conversion CB In Partner to Other
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    0, B.cashbank_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON X.in_out_cashbank_id = O.doc_id
                    AND X.doc_type_id = O.doc_type_id
                    AND X.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeConversionCBInPO

  UNION ALL

  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    D.cost_amount, 0, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
        INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeConversionCBInPO;

  --Void Conversion CB In
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(D.ou_id_cb_in_partner), f_get_ou_name(D.ou_id_cb_in_partner),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    0, C.cashbank_amount, 0
  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank B ON A.ref_id = B.in_out_cashbank_id AND A.ref_doc_type_id = B.doc_type_id AND A.tenant_id = B.tenant_id
        INNER JOIN cb_in_out_cashbank_payment C ON B.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_ext D ON B.in_out_cashbank_id = D.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON C.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON C.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM')
        AND A.doc_type_id = vDocTypeVoidConversionCBIn

  UNION ALL

  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, K.activity_gl_code, K.activity_gl_name,
    C.cashbank_amount, 0, 0

  FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank B ON A.ref_id = B.in_out_cashbank_id AND A.ref_doc_type_id = B.doc_type_id AND A.tenant_id = B.tenant_id
        INNER JOIN cb_in_out_cashbank_payment C ON B.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON C.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON C.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        INNER JOIN m_activity_gl K ON P.activity_gl_id = K.activity_gl_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('ACTIVITY')
        AND A.doc_type_id = vDocTypeVoidConversionCBIn;

  /* 8. Get data trx debit - kredit Cheque/Giro Realization */
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
    B.cheque_giro_amount, 0, 0
  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 cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
                    AND O.doc_id = P.ref_id
                    AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
        AND A.doc_type_id = vDocTypeCgRealization
        AND B.realization_status = vStatusAccept;

  -- Cheque Giro Realization -> nilai cost
  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
            WHERE B.realization_status = vStatusAccept
            GROUP BY A.in_out_cashbank_id, B.cashbank_id)
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(COALESCE(C.ou_rc_id, A.ou_id)), f_get_ou_name(COALESCE(C.ou_rc_id, A.ou_id)),
    CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
    0, C.cost_amount, 0
  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
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
        WHERE A.tenant_id = pTenantId
        AND Z.ou_id = pOuId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.workflow_status = vStatusApproved
        AND A.doc_type_id = vDocTypeCgRealization
        AND C.cost_amount <> 0;

  /* 9. Get data trx debit - kredit POS */
  -- POS (Cash)
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    vEmpty, vEmpty, vEmpty, vEmpty,
    CASE WHEN E.flg_termin = vYes THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END, 0, 0
  FROM i_trx_pos A
    INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
        AND A.process_no = B.process_no
        AND A.trx_pos_id = B.trx_pos_id
    INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
        AND A.process_no = E.process_no
        AND A.trx_pos_id = E.trx_pos_id
    INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
    INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = vFlagCash
    INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
    INNER JOIN t_user F ON A.create_user_id = F.user_id
    INNER JOIN t_user G ON A.update_user_id = G.user_id
    WHERE A.tenant_id = pTenantId
    AND Z.ou_id = pOuId
    AND A.status IN (vStatusSubmit, vStatusVoid)
    AND A.doc_date BETWEEN pDateStart AND pDateEnd;

  -- POS Void
  INSERT INTO tt_rekap_transaksi_cashbank(
    session_id, tenant_id, transaction_type, ou_code, ou_name,
    coa_code, coa_name, activity_gl_code, activity_gl_name,
    debt_amount, credit_amount, total_amount)
  SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
    vEmpty, vEmpty, vEmpty, vEmpty,
    CASE WHEN E.flg_termin = vYes THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END, 0, 0
  FROM i_trx_pos A
    INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
        AND A.process_no = B.process_no
        AND A.trx_pos_id = B.trx_pos_id
    INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
        AND A.process_no = E.process_no
        AND A.trx_pos_id = E.trx_pos_id
    INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
        AND A.process_no = F.process_no
    INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
    INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = vFlagCash
    INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
    INNER JOIN t_user G ON F.create_user_id = G.user_id
    INNER JOIN t_user H ON F.update_user_id = H.user_id
    WHERE A.tenant_id = pTenantId
    AND Z.ou_id = pOuId
    AND A.status = vStatusVoid
    AND F.doc_date BETWEEN pDateStart AND pDateEnd;

  -- set data to RefCursor
    Open pRefHeader FOR
    SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name,
      pDateStart AS date_start, pDateEnd AS date_end, pDatetime AS datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetailSaldoAwal FOR
    SELECT A.cash_bank_type, (A.debt_amount) AS debt_amount, (A.credit_amount) AS credit_amount,
      total_amount AS total_saldo_amount
    FROM tt_rekap_transaksi_cashbank A
    WHERE A.session_id = pSessionId
    AND transaction_type = vTrxTypeBeginningBalance;
    RETURN NEXT pRefDetailSaldoAwal;

  Open pRefDetailOU FOR
    SELECT A.ou_code, A.ou_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
      SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
    FROM tt_rekap_transaksi_cashbank A
    WHERE A.session_id = pSessionId
    AND transaction_type <> vTrxTypeBeginningBalance
    GROUP BY A.ou_code, A.ou_name;
  RETURN NEXT pRefDetailOU;

  Open pRefDetailPartner FOR
    SELECT A.partner_code, A.partner_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
      SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
    FROM tt_rekap_transaksi_cashbank A
    WHERE A.session_id = pSessionId
    AND A.partner_code NOT IN (vEmpty, vEmptyValue)
    AND A.partner_name NOT IN (vEmpty, vEmptyValue)
    GROUP BY A.partner_code, A.partner_name;
  RETURN NEXT pRefDetailPartner;

  Open pRefDetailActivity FOR
    SELECT A.activity_gl_code, A.activity_gl_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
      SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
    FROM tt_rekap_transaksi_cashbank A
    WHERE A.session_id = pSessionId
    AND A.activity_gl_code NOT IN (vEmpty, vEmptyValue)
    AND A.activity_gl_name NOT IN (vEmpty, vEmptyValue)
    GROUP BY A.activity_gl_code, A.activity_gl_name;
  RETURN NEXT pRefDetailActivity;

  Open pRefDetailCoa FOR
    SELECT A.coa_code, A.coa_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
      SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
    FROM tt_rekap_transaksi_cashbank A
    INNER JOIN m_coa B ON A.coa_code = CONCAT(B.main_acc, ' - ', B.sub_acc)
    INNER JOIN m_group_coa C ON B.group_coa_id = C.group_coa_id
    WHERE A.session_id = pSessionId
    AND A.coa_code NOT IN (vEmpty, vEmptyValue)
    AND A.coa_name NOT IN (vEmpty, vEmptyValue)
    AND C.type_coa IN ('C', 'R')
    GROUP BY A.coa_code, A.coa_name;
  RETURN NEXT pRefDetailCoa;

  DELETE FROM tt_rekap_transaksi_cashbank WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /