abirama62

func_rekap_bank

Nov 9th, 2020 (edited)
1,054
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION public.r_report_rekap_mutation_cashbank(
  2.     character varying,
  3.     bigint,
  4.     character varying,
  5.     bigint,
  6.     bigint,
  7.     character varying,
  8.     character varying)
  9.   RETURNS SETOF refcursor AS
  10. $BODY$
  11. DECLARE
  12.     pRefHeader          REFCURSOR := 'refHeader';
  13.     pRefDetailSaldoAwal         REFCURSOR := 'refDetailSaldoAwal';
  14.     pRefDetailOU            REFCURSOR := 'refDetailOU';
  15.   pRefDetailPartner         REFCURSOR := 'refDetailPartner';
  16.   pRefDetailActivity            REFCURSOR := 'refDetailActivity';
  17.   pRefDetailCoa         REFCURSOR := 'refDetailCoa';
  18.   pSessionId            ALIAS FOR $1;
  19.   pTenantId           ALIAS FOR $2;
  20.   pDatetime           ALIAS FOR $3;
  21.     pOuId                   ALIAS FOR $4;
  22.     pCashbankId         ALIAS FOR $5;
  23.     pDateStart          ALIAS FOR $6;
  24.     pDateEnd              ALIAS FOR $7;
  25.  
  26.   vRecordTypeSaldoAwal character varying := 'A';
  27.   vSignCredit     character varying := 'C';
  28.   vSignDebit      character varying := 'D';
  29.   vStatusSubmit                 character varying :='S';
  30.   vStatusVoid               character varying :='V';
  31.   vYes            character varying := 'Y';
  32.   vFlagCash       character varying := 'C';
  33.   vTrxTypeBeginningBalance character varying := 'Beginning Balance';
  34.   vStatusApproved           character varying := 'APPROVED';
  35.   vStatusAccept                 character varying := 'ACCEPT';
  36.  
  37.   vEmptyId              bigint := -99;
  38.     vEmpty                  character varying(1) := ' ';
  39.   vEmptyValue                   character varying := '';
  40.  
  41.   vDocTypeCashBankOut               bigint := 611;
  42.   vDocTypePaymentOrderAP            bigint := 231;
  43.   vDocTypePaymentOrderNonAP         bigint := 601;
  44.   vDocTypeRequestCashAdvance            bigint := 602;
  45.   vDocTypeCashAdvanceSettlement         bigint := 603;
  46.   vDocTypeCashBankInAr      bigint := 621;
  47.   vDocTypeEdcSettlement     bigint := 622;
  48.   vDocTypeCashBankInOther       bigint := 623;
  49.   vDocTypeCgRealization     bigint := 625;
  50.   vDocTypeConversionCBInOP          bigint := 626;
  51.   vDocTypeConversionCBInPO          bigint := 627;
  52.   vDocTypeVoidConversionCBIn            bigint := 630;
  53.  
  54.   vDateMinusOneDay character varying;
  55.  
  56. BEGIN
  57.   DELETE FROM tt_rekap_transaksi_cashbank WHERE session_id = pSessionId;
  58.  
  59.   --  1. Get data saldo awal cash/bank
  60.   --  2. Get data trx debit - kredit per OU
  61.   --  3. Get data trx debit - kredit Hutang Dagang per Partner
  62.   --  4. Get data trx debit - kredit per Activity Gl
  63.   --  5. Get data total Bon Sementara (Cash Advance)
  64.   --  6. Get data total Piutang Karyawan(?)
  65.  
  66.   SELECT TO_CHAR(pDateStart::date - INTERVAL '1 day', 'YYYYMMDD') into vDateMinusOneDay;
  67.  
  68.   /* 1. Get data saldo awal cash/bank */
  69.   WITH calc_beggining_balance AS (
  70.     WITH beggining_balance AS (
  71.       -- get saldo awal terkakhir (rec_type = 'A')
  72.       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
  73.       FROM cb_cashbank_balance A
  74.       INNER JOIN m_cashbank_assignment_ou B ON A.cashbank_id = B.cashbank_id
  75.       INNER JOIN m_cashbank C ON A.cashbank_id = C.cashbank_id
  76.       WHERE A.tenant_id = pTenantId
  77.       AND B.ou_id = pOuId
  78.       AND A.rec_type = vRecordTypeSaldoAwal
  79.       AND A.cash_bank_date <= pDateStart
  80.       GROUP BY A.tenant_id, A.cashbank_id, C.flg_cash_bank
  81.       ORDER BY A.cashbank_id
  82.     ), cb_beggining AS (
  83.       -- get total saldo dari tanggal terakhir saldo awal sampai H-1 tanggal filter
  84.       SELECT A.cashbank_id, COALESCE(SUM(CASE WHEN A.rec_type = 'K' THEN A.amount * -1 ELSE A.amount END), 0) AS amount
  85.       FROM cb_cashbank_balance A
  86.       INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  87.       INNER JOIN beggining_balance B ON A.cashbank_id = B.cashbank_id AND A.tenant_id = B.tenant_id
  88.           WHERE A.tenant_id = B.tenant_id
  89.           AND C.ou_id = pOuId
  90.           AND A.rec_type <> vRecordTypeSaldoAwal
  91.           AND A.cash_bank_date >= B.last_date
  92.           AND A.cash_bank_date <= vDateMinusOneDay
  93.           GROUP BY A.cashbank_id
  94.     )
  95.     SELECT A.cashbank_id, A.flg_cash_bank, A.bg_amount + COALESCE(B.amount, 0) AS saldo_amount
  96.       FROM beggining_balance A
  97.       LEFT OUTER JOIN cb_beggining B ON A.cashbank_id = B.cashbank_id
  98.     UNION ALL
  99.     --jika tidak ada tanggal saldo awal
  100.     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
  101.         FROM cb_cashbank_balance A
  102.         INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  103.         INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
  104.         WHERE A.tenant_id = pTenantId
  105.         AND C.ou_id = pOuId
  106.         AND A.cash_bank_date <= vDateMinusOneDay
  107.         AND NOT EXISTS (
  108.             SELECT 1
  109.             FROM beggining_balance X WHERE A.cashbank_id = X.cashbank_id
  110.         )
  111.         GROUP BY A.cashbank_id, B.flg_cash_bank
  112.   ) INSERT INTO tt_rekap_transaksi_cashbank(
  113.     session_id, tenant_id, transaction_type,
  114.     cash_bank_type, debt_amount, credit_amount, total_amount)
  115.   SELECT pSessionId, pTenantId, vTrxTypeBeginningBalance,
  116.   CASE WHEN A.flg_cash_bank = 'C' THEN 'KAS' ELSE 'BANK' END AS jenis_bank, 0, 0, SUM(A.saldo_amount)
  117.   FROM calc_beggining_balance A
  118.   GROUP BY jenis_bank;
  119.  
  120.   /* 2. Get data trx debit - kredit Transfer */
  121.   INSERT INTO tt_rekap_transaksi_cashbank(
  122.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  123.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  124.     debt_amount, credit_amount, total_amount)
  125.   SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  126.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  127.     B.receive_amount AS debt_amount, 0 AS credit_amount, 0
  128.   FROM cb_transfer_cashbank A
  129.   INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  130.   INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_to_id = Z.cashbank_id
  131.   INNER JOIN m_cashbank G ON B.cashbank_to_id = G.cashbank_id
  132.   INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
  133.         AND A.doc_type_id = O.doc_type_id
  134.         AND A.tenant_id = O.tenant_id
  135.   INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  136.         AND A.cashbank_id = P.cashbank_id
  137.   INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  138.   WHERE A.tenant_id = pTenantId
  139.   AND Z.ou_id = pOuId
  140.   AND A.workflow_status = vStatusApproved
  141.   AND P.sign_journal = vSignCredit
  142.   AND A.doc_date BETWEEN pDateStart AND pDateEnd
  143.  
  144.   UNION ALL
  145.  
  146.   SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(X.ou_id), f_get_ou_name(X.ou_id),
  147.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  148.     0, B.transfer_amount, 0
  149.   FROM cb_transfer_cashbank A
  150.   INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  151.   INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  152.   INNER JOIN m_cashbank_assignment_ou X ON B.cashbank_to_id = X.cashbank_id
  153.   INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
  154.         AND A.doc_type_id = O.doc_type_id
  155.         AND A.tenant_id = O.tenant_id
  156.   INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  157.         AND X.cashbank_id = P.cashbank_id
  158.         --AND O.doc_type_id = P.ref_doc_type_id
  159.   INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  160.   INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  161.   WHERE A.tenant_id = pTenantId
  162.   AND C.ou_id = pOuId
  163.   AND A.workflow_status = vStatusApproved
  164.   AND P.sign_journal = vSignDebit
  165.   AND A.doc_date BETWEEN pDateStart AND pDateEnd
  166.  
  167.   UNION ALL
  168.  
  169.   SELECT pSessionId, pTenantId, 'Mutasi OU', f_get_ou_code(B.ou_to_id), f_get_ou_name(B.ou_to_id),
  170.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  171.     0, C.payment_amount, 0
  172.   FROM cb_transfer_cashbank A
  173.   INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  174.   INNER JOIN cb_transfer_cashbank_cost C ON A.transfer_cashbank_id = C.transfer_cashbank_id
  175.   INNER JOIN m_cashbank_assignment_ou D ON A.cashbank_id = D.cashbank_id
  176.   INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  177.   INNER JOIN m_coa L ON K.coa_id = L.coa_id
  178.   INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  179.   WHERE A.tenant_id = pTenantId
  180.   AND D.ou_id = pOuId
  181.   AND A.workflow_status = vStatusApproved
  182.   AND A.doc_date BETWEEN pDateStart AND pDateEnd;
  183.  
  184.   /* 3. Get data trx debit - kredit CB Out */
  185.   -- CB Out dari Payment Order (AP)
  186.   INSERT INTO tt_rekap_transaksi_cashbank(
  187.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  188.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  189.     partner_code, partner_name,
  190.     debt_amount, credit_amount, total_amount)
  191.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  192.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  193.     E.partner_code, E.partner_name,
  194.     0, B.cashbank_amount, 0
  195.   FROM cb_in_out_cashbank A
  196.   INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  197.   INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  198.   INNER JOIN m_partner E ON A.partner_id = E.partner_id
  199.   INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  200.   INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  201.         AND A.doc_type_id = O.doc_type_id
  202.         AND A.tenant_id = O.tenant_id
  203.   INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  204.   INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  205.   WHERE A.tenant_id = pTenantId
  206.   AND Z.ou_id = pOuId
  207.   AND A.doc_date BETWEEN pDateStart AND pDateEnd
  208.   AND A.workflow_status = vStatusApproved
  209.   AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  210.   AND P.line_no = 1
  211.   AND A.doc_type_id = vDocTypeCashBankOut
  212.   AND A.ref_doc_type_id = vDocTypePaymentOrderAP;
  213.  
  214.   -- CB Out dari Payment Order (Non AP)
  215.   INSERT INTO tt_rekap_transaksi_cashbank(
  216.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  217.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  218.     debt_amount, credit_amount, total_amount)
  219.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  220.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  221.     0, H.add_amount, 0
  222.   FROM cb_in_out_cashbank A
  223.   INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  224.   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
  225.   INNER JOIN cb_payment_order D ON C.payment_id = D.payment_order_id AND C.doc_type_id = D.doc_type_id
  226.   INNER JOIN cb_payment_order_cost H ON D.payment_order_id = H.payment_order_id
  227.   INNER JOIN m_activity_gl K ON H.activity_gl_id = K.activity_gl_id
  228.   INNER JOIN m_coa L ON L.coa_id = K.coa_id
  229.   INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  230.   INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  231.   WHERE A.tenant_id = pTenantId
  232.   AND Z.ou_id = pOuId
  233.   AND A.doc_date BETWEEN pDateStart AND pDateEnd
  234.   AND A.workflow_status = vStatusApproved
  235.   AND A.doc_type_id = vDocTypeCashBankOut
  236.   AND A.ref_doc_type_id = vDocTypePaymentOrderNonAP;
  237.  
  238.   -- CB Out dari Req Cash Advance
  239.   INSERT INTO tt_rekap_transaksi_cashbank(
  240.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  241.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  242.     debt_amount, credit_amount, total_amount)
  243.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(D.ou_id), f_get_ou_name(D.ou_id),
  244.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  245.     0, B.cashbank_amount, 0
  246.   FROM cb_in_out_cashbank A
  247.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  248.         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
  249.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  250.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  251.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  252.             AND A.doc_type_id = O.doc_type_id
  253.             AND A.tenant_id = O.tenant_id
  254.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  255.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  256.         WHERE A.tenant_id = pTenantId
  257.         AND Z.ou_id = pOuId
  258.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  259.         AND A.workflow_status = vStatusApproved
  260.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  261.         AND A.doc_type_id = vDocTypeCashBankOut
  262.         AND A.ref_doc_type_id = vDocTypeRequestCashAdvance;
  263.  
  264.   /* 4. Get data trx debit - kredit CB In */
  265.   INSERT INTO tt_rekap_transaksi_cashbank(
  266.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  267.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  268.     debt_amount, credit_amount, total_amount)
  269.   --CB In Partner Receive
  270.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  271.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  272.     B.cashbank_amount, 0, 0
  273.   FROM cb_in_out_cashbank A
  274.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  275.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  276.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  277.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  278.             AND A.doc_type_id = O.doc_type_id
  279.             AND A.tenant_id = O.tenant_id
  280.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  281.             AND O.doc_id = P.ref_id
  282.             AND O.doc_type_id = P.ref_doc_type_id
  283.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  284.         WHERE A.tenant_id = pTenantId
  285.         AND Z.ou_id = pOuId
  286.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  287.         AND A.workflow_status = vStatusApproved
  288.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  289.         AND A.doc_type_id = vDocTypeCashBankInAr
  290.  
  291.   UNION ALL
  292.   --CB In Partner Receive (cost)
  293.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  294.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  295.     0, C.cost_amount, 0
  296.   FROM cb_in_out_cashbank A
  297.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  298.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  299.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  300.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  301.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  302.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  303.         WHERE A.tenant_id = pTenantId
  304.         AND Z.ou_id = pOuId
  305.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  306.         AND A.workflow_status = vStatusApproved
  307.         AND A.doc_type_id = vDocTypeCashBankInAr
  308.         AND C.cost_amount <> 0;
  309.  
  310.   --CB In Other
  311.   INSERT INTO tt_rekap_transaksi_cashbank(
  312.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  313.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  314.     debt_amount, credit_amount, total_amount)
  315.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  316.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  317.     C.cost_amount, 0, 0
  318.   FROM cb_in_out_cashbank A
  319.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  320.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  321.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  322.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  323.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  324.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  325.         WHERE A.tenant_id = pTenantId
  326.         AND Z.ou_id = pOuId
  327.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  328.         AND A.workflow_status = vStatusApproved
  329.         AND A.doc_type_id = vDocTypeCashBankInOther;
  330.  
  331.   /* 5. Get data trx debit - kredit CAS */
  332.   INSERT INTO tt_rekap_transaksi_cashbank(
  333.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  334.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  335.     debt_amount, credit_amount, total_amount)
  336.   -- Cash Advance Settlement (pengembalian BS)
  337.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  338.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  339.     E.cashbank_amount, 0, 0
  340.   FROM cb_advance_settle A
  341.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  342.         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
  343.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  344.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  345.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  346.         INNER JOIN gl_journal_trx O ON D.in_out_cashbank_id = O.doc_id
  347.                     AND D.doc_type_id = O.doc_type_id
  348.                     AND D.tenant_id = O.tenant_id
  349.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  350.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  351.         WHERE A.tenant_id = pTenantId
  352.         AND Z.ou_id = pOuId
  353.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  354.         AND A.workflow_status = vStatusApproved
  355.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  356.         AND A.doc_type_id = vDocTypeCashAdvanceSettlement
  357.  
  358.   UNION ALL
  359.   -- Cash Advance Settlement (cost activity gl)
  360.   SELECT pSessionId, pTenantId, 'Mutation OU',
  361.     CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
  362.                 CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
  363.                     f_get_ou_code(A.ou_id)
  364.                 ELSE f_get_ou_code(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
  365.     ELSE f_get_ou_code(COALESCE(F.ou_branch_id, A.ou_id)) END,
  366.     CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
  367.                 CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
  368.                     f_get_ou_name(A.ou_id)
  369.                 ELSE f_get_ou_name(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
  370.     ELSE f_get_ou_name(COALESCE(F.ou_branch_id, A.ou_id)) END,
  371.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  372.     0, B.cost_amount, 0
  373.   FROM cb_advance_settle A
  374.         INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  375.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  376.         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
  377.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  378.         LEFT OUTER JOIN cb_advance_settle_cost_custom_for_sasa F ON B.advance_settle_cost_id = F.advance_settle_cost_id
  379.         INNER JOIN m_activity_gl K ON B.activity_gl_id = K.activity_gl_id
  380.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  381.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  382.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  383.         WHERE A.tenant_id = pTenantId
  384.         AND Z.ou_id = pOuId
  385.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  386.         AND A.workflow_status = vStatusApproved
  387.         AND A.doc_type_id = vDocTypeCashAdvanceSettlement;
  388.  
  389.   /* 6. Get data trx debit - kredit EDC Settlement */
  390.   INSERT INTO tt_rekap_transaksi_cashbank(
  391.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  392.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  393.     debt_amount, credit_amount, total_amount)
  394.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(Y.ou_id), f_get_ou_name(Y.ou_id),
  395.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  396.     B.cashbank_amount, 0, 0
  397.   FROM cb_in_out_cashbank A
  398.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  399.         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
  400.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  401.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  402.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  403.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  404.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  405.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  406.                     AND A.doc_type_id = O.doc_type_id
  407.                     AND A.tenant_id = O.tenant_id
  408.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  409.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  410.         WHERE A.tenant_id = pTenantId
  411.         AND Z.ou_id = pOuId
  412.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  413.         AND A.workflow_status = vStatusApproved
  414.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  415.         AND A.doc_type_id = vDocTypeEdcSettlement
  416.  
  417.   UNION ALL
  418.     -- EDC  Settlement -> nilai cost
  419.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(Y.ou_id), f_get_ou_name(Y.ou_id),
  420.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  421.     0, C.cost_amount, 0
  422.   FROM cb_in_out_cashbank A
  423.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  424.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  425.         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
  426.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  427.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  428.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  429.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  430.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  431.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  432.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  433.         WHERE A.tenant_id = pTenantId
  434.         AND Z.ou_id = pOuId
  435.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  436.         AND A.workflow_status = vStatusApproved
  437.         AND A.doc_type_id = vDocTypeEdcSettlement
  438.         AND C.cost_amount <> 0;
  439.  
  440.   /* 7. Get data trx debit - kredit Conversion CB In */
  441.   -- Conversion Other to Partner
  442.   INSERT INTO tt_rekap_transaksi_cashbank(
  443.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  444.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  445.     debt_amount, credit_amount, total_amount)
  446.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  447.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, K.activity_gl_code, K.activity_gl_name,
  448.     0, B.cashbank_amount, 0
  449.   FROM cb_in_out_cashbank A
  450.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  451.         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
  452.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  453.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  454.         INNER JOIN gl_journal_trx O ON X.in_out_cashbank_id = O.doc_id
  455.             AND X.doc_type_id = O.doc_type_id
  456.             AND X.tenant_id = O.tenant_id
  457.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  458.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  459.         INNER JOIN m_activity_gl K ON P.activity_gl_id = K.activity_gl_id
  460.         WHERE A.tenant_id = pTenantId
  461.         AND Z.ou_id = pOuId
  462.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  463.         AND A.workflow_status = vStatusApproved
  464.         AND P.flg_source_coa IN ('ACTIVITY')
  465.         AND A.doc_type_id = vDocTypeConversionCBInOP
  466.  
  467.   UNION ALL
  468.  
  469.   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),
  470.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  471.     B.cashbank_amount, 0, 0
  472.   FROM cb_in_out_cashbank A
  473.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  474.         INNER JOIN cb_in_out_cashbank_ext C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  475.         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
  476.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  477.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  478.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  479.             AND A.doc_type_id = O.doc_type_id
  480.             AND A.tenant_id = O.tenant_id
  481.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  482.             AND O.doc_id = P.ref_id
  483.             AND O.doc_type_id = P.ref_doc_type_id
  484.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  485.         WHERE A.tenant_id = pTenantId
  486.         AND Z.ou_id = pOuId
  487.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  488.         AND A.workflow_status = vStatusApproved
  489.         AND P.flg_source_coa IN ('SYSTEM')
  490.         AND A.doc_type_id = vDocTypeConversionCBInOP;
  491.  
  492.   -- Conversion Other to Partner (cost)
  493.   INSERT INTO tt_rekap_transaksi_cashbank(
  494.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  495.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  496.     debt_amount, credit_amount, total_amount)
  497.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  498.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  499.     0, C.cost_amount, 0
  500.   FROM cb_in_out_cashbank A
  501.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  502.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  503.         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
  504.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  505.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  506.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  507.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  508.         WHERE A.tenant_id = pTenantId
  509.         AND Z.ou_id = pOuId
  510.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  511.         AND A.workflow_status = vStatusApproved
  512.         AND A.doc_type_id = vDocTypeConversionCBInOP;
  513.  
  514.   -- Conversion CB In Partner to Other
  515.   INSERT INTO tt_rekap_transaksi_cashbank(
  516.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  517.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  518.     debt_amount, credit_amount, total_amount)
  519.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  520.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  521.     0, B.cashbank_amount, 0
  522.   FROM cb_in_out_cashbank A
  523.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  524.         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
  525.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  526.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  527.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  528.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  529.         INNER JOIN gl_journal_trx O ON X.in_out_cashbank_id = O.doc_id
  530.                     AND X.doc_type_id = O.doc_type_id
  531.                     AND X.tenant_id = O.tenant_id
  532.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  533.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  534.         WHERE A.tenant_id = pTenantId
  535.         AND Z.ou_id = pOuId
  536.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  537.         AND A.workflow_status = vStatusApproved
  538.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  539.         AND A.doc_type_id = vDocTypeConversionCBInPO
  540.  
  541.   UNION ALL
  542.  
  543.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  544.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  545.     D.cost_amount, 0, 0
  546.   FROM cb_in_out_cashbank A
  547.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  548.         INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
  549.         INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
  550.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  551.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  552.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  553.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  554.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  555.         WHERE A.tenant_id = pTenantId
  556.         AND Z.ou_id = pOuId
  557.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  558.         AND A.workflow_status = vStatusApproved
  559.         AND A.doc_type_id = vDocTypeConversionCBInPO;
  560.  
  561.   --Void Conversion CB In
  562.   INSERT INTO tt_rekap_transaksi_cashbank(
  563.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  564.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  565.     debt_amount, credit_amount, total_amount)
  566.   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),
  567.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  568.     0, C.cashbank_amount, 0
  569.   FROM cb_in_out_cashbank A
  570.         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
  571.         INNER JOIN cb_in_out_cashbank_payment C ON B.in_out_cashbank_id = C.in_out_cashbank_id
  572.         INNER JOIN cb_in_out_cashbank_ext D ON B.in_out_cashbank_id = D.in_out_cashbank_id
  573.         INNER JOIN m_cashbank_assignment_ou Z ON C.cashbank_id = Z.cashbank_id
  574.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  575.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  576.         INNER JOIN m_cashbank G ON C.cashbank_id = G.cashbank_id
  577.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  578.                     AND A.doc_type_id = O.doc_type_id
  579.                     AND A.tenant_id = O.tenant_id
  580.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  581.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  582.         WHERE A.tenant_id = pTenantId
  583.         AND Z.ou_id = pOuId
  584.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  585.         AND A.workflow_status = vStatusApproved
  586.         AND P.flg_source_coa IN ('SYSTEM')
  587.         AND A.doc_type_id = vDocTypeVoidConversionCBIn
  588.  
  589.   UNION ALL
  590.  
  591.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  592.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, K.activity_gl_code, K.activity_gl_name,
  593.     C.cashbank_amount, 0, 0
  594.  
  595.   FROM cb_in_out_cashbank A
  596.         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
  597.         INNER JOIN cb_in_out_cashbank_payment C ON B.in_out_cashbank_id = C.in_out_cashbank_id
  598.         INNER JOIN m_cashbank_assignment_ou Z ON C.cashbank_id = Z.cashbank_id
  599.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  600.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  601.         INNER JOIN m_cashbank G ON C.cashbank_id = G.cashbank_id
  602.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  603.                     AND A.doc_type_id = O.doc_type_id
  604.                     AND A.tenant_id = O.tenant_id
  605.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  606.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  607.         INNER JOIN m_activity_gl K ON P.activity_gl_id = K.activity_gl_id
  608.         WHERE A.tenant_id = pTenantId
  609.         AND Z.ou_id = pOuId
  610.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  611.         AND A.workflow_status = vStatusApproved
  612.         AND P.flg_source_coa IN ('ACTIVITY')
  613.         AND A.doc_type_id = vDocTypeVoidConversionCBIn;
  614.  
  615.   /* 8. Get data trx debit - kredit Cheque/Giro Realization */
  616.   INSERT INTO tt_rekap_transaksi_cashbank(
  617.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  618.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  619.     debt_amount, credit_amount, total_amount)
  620.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  621.     CONCAT(Q.main_acc, ' - ', Q.sub_acc), Q.coa_desc, vEmpty, vEmpty,
  622.     B.cheque_giro_amount, 0, 0
  623.   FROM cb_in_out_cashbank A
  624.         INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  625.         INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
  626.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  627.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  628.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  629.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  630.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  631.                     AND A.doc_type_id = O.doc_type_id
  632.                     AND A.tenant_id = O.tenant_id
  633.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  634.                     AND O.doc_id = P.ref_id
  635.                     AND O.doc_type_id = P.ref_doc_type_id
  636.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  637.         WHERE A.tenant_id = pTenantId
  638.         AND Z.ou_id = pOuId
  639.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  640.         AND A.workflow_status = vStatusApproved
  641.         AND P.flg_source_coa IN ('SYSTEM', 'ACTIVITY')
  642.         AND A.doc_type_id = vDocTypeCgRealization
  643.         AND B.realization_status = vStatusAccept;
  644.  
  645.   -- Cheque Giro Realization -> nilai cost
  646.   WITH tt_in_out_cashbank AS(
  647.             SELECT A.in_out_cashbank_id, B.cashbank_id
  648.             FROM cb_in_out_cashbank A
  649.             INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  650.             WHERE B.realization_status = vStatusAccept
  651.             GROUP BY A.in_out_cashbank_id, B.cashbank_id)
  652.   INSERT INTO tt_rekap_transaksi_cashbank(
  653.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  654.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  655.     debt_amount, credit_amount, total_amount)
  656.   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)),
  657.     CONCAT(L.main_acc, ' - ', L.sub_acc), L.coa_desc, K.activity_gl_code, K.activity_gl_name,
  658.     0, C.cost_amount, 0
  659.   FROM cb_in_out_cashbank A
  660.         INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  661.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  662.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  663.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  664.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  665.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  666.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  667.         INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
  668.         WHERE A.tenant_id = pTenantId
  669.         AND Z.ou_id = pOuId
  670.         AND A.doc_date BETWEEN pDateStart AND pDateEnd
  671.         AND A.workflow_status = vStatusApproved
  672.         AND A.doc_type_id = vDocTypeCgRealization
  673.         AND C.cost_amount <> 0;
  674.  
  675.   /* 9. Get data trx debit - kredit POS */
  676.   -- POS (Cash)
  677.   INSERT INTO tt_rekap_transaksi_cashbank(
  678.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  679.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  680.     debt_amount, credit_amount, total_amount)
  681.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  682.     vEmpty, vEmpty, vEmpty, vEmpty,
  683.     CASE WHEN E.flg_termin = vYes THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END, 0, 0
  684.   FROM i_trx_pos A
  685.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  686.         AND A.process_no = B.process_no
  687.         AND A.trx_pos_id = B.trx_pos_id
  688.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  689.         AND A.process_no = E.process_no
  690.         AND A.trx_pos_id = E.trx_pos_id
  691.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  692.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = vFlagCash
  693.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  694.     INNER JOIN t_user F ON A.create_user_id = F.user_id
  695.     INNER JOIN t_user G ON A.update_user_id = G.user_id
  696.     WHERE A.tenant_id = pTenantId
  697.     AND Z.ou_id = pOuId
  698.     AND A.status IN (vStatusSubmit, vStatusVoid)
  699.     AND A.doc_date BETWEEN pDateStart AND pDateEnd;
  700.  
  701.   -- POS Void
  702.   INSERT INTO tt_rekap_transaksi_cashbank(
  703.     session_id, tenant_id, transaction_type, ou_code, ou_name,
  704.     coa_code, coa_name, activity_gl_code, activity_gl_name,
  705.     debt_amount, credit_amount, total_amount)
  706.   SELECT pSessionId, pTenantId, 'Mutation OU', f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
  707.     vEmpty, vEmpty, vEmpty, vEmpty,
  708.     CASE WHEN E.flg_termin = vYes THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END, 0, 0
  709.   FROM i_trx_pos A
  710.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  711.         AND A.process_no = B.process_no
  712.         AND A.trx_pos_id = B.trx_pos_id
  713.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  714.         AND A.process_no = E.process_no
  715.         AND A.trx_pos_id = E.trx_pos_id
  716.     INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
  717.         AND A.process_no = F.process_no
  718.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  719.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = vFlagCash
  720.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  721.     INNER JOIN t_user G ON F.create_user_id = G.user_id
  722.     INNER JOIN t_user H ON F.update_user_id = H.user_id
  723.     WHERE A.tenant_id = pTenantId
  724.     AND Z.ou_id = pOuId
  725.     AND A.status = vStatusVoid
  726.     AND F.doc_date BETWEEN pDateStart AND pDateEnd;
  727.  
  728.   -- set data to RefCursor
  729.     Open pRefHeader FOR
  730.     SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name,
  731.       pDateStart AS date_start, pDateEnd AS date_end, pDatetime AS datetime;
  732.     RETURN NEXT pRefHeader;
  733.  
  734.     Open pRefDetailSaldoAwal FOR
  735.     SELECT A.cash_bank_type, (A.debt_amount) AS debt_amount, (A.credit_amount) AS credit_amount,
  736.       total_amount AS total_saldo_amount
  737.     FROM tt_rekap_transaksi_cashbank A
  738.     WHERE A.session_id = pSessionId
  739.     AND transaction_type = vTrxTypeBeginningBalance;
  740.     RETURN NEXT pRefDetailSaldoAwal;
  741.  
  742.   Open pRefDetailOU FOR
  743.     SELECT A.ou_code, A.ou_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
  744.       SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
  745.     FROM tt_rekap_transaksi_cashbank A
  746.     WHERE A.session_id = pSessionId
  747.     AND transaction_type <> vTrxTypeBeginningBalance
  748.     GROUP BY A.ou_code, A.ou_name;
  749.   RETURN NEXT pRefDetailOU;
  750.  
  751.   Open pRefDetailPartner FOR
  752.     SELECT A.partner_code, A.partner_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
  753.       SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
  754.     FROM tt_rekap_transaksi_cashbank A
  755.     WHERE A.session_id = pSessionId
  756.     AND A.partner_code NOT IN (vEmpty, vEmptyValue)
  757.     AND A.partner_name NOT IN (vEmpty, vEmptyValue)
  758.     GROUP BY A.partner_code, A.partner_name;
  759.   RETURN NEXT pRefDetailPartner;
  760.  
  761.   Open pRefDetailActivity FOR
  762.     SELECT A.activity_gl_code, A.activity_gl_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
  763.       SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
  764.     FROM tt_rekap_transaksi_cashbank A
  765.     WHERE A.session_id = pSessionId
  766.     AND A.activity_gl_code NOT IN (vEmpty, vEmptyValue)
  767.     AND A.activity_gl_name NOT IN (vEmpty, vEmptyValue)
  768.     GROUP BY A.activity_gl_code, A.activity_gl_name;
  769.   RETURN NEXT pRefDetailActivity;
  770.  
  771.   Open pRefDetailCoa FOR
  772.     SELECT A.coa_code, A.coa_name, SUM(A.debt_amount) AS debt_amount, SUM(A.credit_amount) AS credit_amount,
  773.       SUM(A.debt_amount) - SUM(A.credit_amount) AS total_saldo_amount
  774.     FROM tt_rekap_transaksi_cashbank A
  775.     INNER JOIN m_coa B ON A.coa_code = CONCAT(B.main_acc, ' - ', B.sub_acc)
  776.     INNER JOIN m_group_coa C ON B.group_coa_id = C.group_coa_id
  777.     WHERE A.session_id = pSessionId
  778.     AND A.coa_code NOT IN (vEmpty, vEmptyValue)
  779.     AND A.coa_name NOT IN (vEmpty, vEmptyValue)
  780.     AND C.type_coa IN ('C', 'R')
  781.     GROUP BY A.coa_code, A.coa_name;
  782.   RETURN NEXT pRefDetailCoa;
  783.  
  784.   DELETE FROM tt_rekap_transaksi_cashbank WHERE session_id = pSessionId;
  785.  
  786. END;
  787. $BODY$
  788.   LANGUAGE plpgsql VOLATILE
  789.   COST 100
  790.   /
RAW Paste Data