abirama62

func_final_detail_mutation_cashbank

Oct 30th, 2020
1,156
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION public.r_report_detail_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.     pRefDetail          REFCURSOR := 'refDetail';
  14.     pSessionId          ALIAS FOR $1;
  15.     pTenantId           ALIAS FOR $2;
  16.     pDatetime           ALIAS FOR $3;
  17.     pOuId               ALIAS FOR $4;
  18.     pCashbankId         ALIAS FOR $5;
  19.     pDateStart          ALIAS FOR $6;
  20.     pDateEnd            ALIAS FOR $7;
  21.    
  22.     vEmptyId                bigint;
  23.     vEmpty                  character varying(1);
  24.     vCashBankInType         character varying(1);
  25.     vCashBankOutType        character varying(1);
  26.     vStatusReleased         character varying;
  27.     vStatusVoid             character varying(1);
  28.     vRecordTypeSaldoAwal    character varying(1);
  29.     vRecordTypeKredit       character varying(1);
  30.     vRecordTypeDebet        character varying(1);
  31.     vTglAwalCashbankBalance character varying(8);
  32.     vDocTypePosShop             bigint := 401;
  33.     vDocTypeCashBankOut         bigint := 611;
  34.     vDocTypeCashBankInOther     bigint := 623;
  35.     vDocTypeCashBankInAr        bigint := 621;
  36.     vDocTypeEdcSettlement       bigint := 622;
  37.     vDocTypeFollowUpCashAdvanceSettlement       bigint := 641;
  38.     vDocTypeCbInDt              bigint := 699;
  39.     vDocTypeCgRealization       bigint := 625;
  40.     vDocTypeCbTransfer          bigint := 631;
  41.     vDocTypePaymentOrderNonAP           bigint := 601;
  42.     vDocTypePaymentOrderAP          bigint := 231;
  43.     vDocTypeRequestCashAdvance          bigint := 602;
  44.     vDocTypeCashAdvanceSettlement           bigint := 603;
  45.     vDocTypeConversionCBInOP            bigint := 626;
  46.     vDocTypeConversionCBInPO            bigint := 627;
  47.     vCount                      bigint;
  48.     vYes                    character varying(1);
  49.     vTotalCgAmount                  numeric;
  50.     vTotalCgCost                    numeric;
  51.     vModePaymentOther               character varying := 'OTHERS';
  52.    
  53.     vAnd                            character varying := 'AND ';
  54.     vFilterCashbankV1               character varying := '';
  55.     vFilterCashbankV2               character varying := '';
  56.     vFilterCashbankV3               character varying := '';
  57.     vStatusAccept                   character varying;
  58.     vStatusSubmit                   character varying:='S';
  59.     vFlagCash                   character varying:='C';
  60.    
  61. BEGIN
  62.    
  63.     vEmptyId := -99;
  64.     vEmpty   := ' ';
  65.     vCashBankInType := 'I';
  66.     vCashBankOutType := 'O';
  67.     vStatusReleased := 'APPROVED';
  68.     vStatusVoid := 'V';
  69.     vRecordTypeSaldoAwal := 'A';
  70.     vRecordTypeKredit := 'K';
  71.     vRecordTypeDebet := 'D';
  72.     vTglAwalCashbankBalance := ' ';
  73.     vYes := 'Y';
  74.     vStatusAccept := 'ACCEPT';
  75.    
  76.     DELETE FROM tt_detail_mutation_cashbank WHERE session_id = pSessionId;
  77.  
  78.     IF pCashbankId <> vEmptyId THEN
  79.         vFilterCashbankV1 := 'AND ( A.cashbank_id = '|| pCashbankId ||' OR B.cashbank_to_id = '||pCashbankId||' )';
  80.         vFilterCashbankV2 := 'AND B.cashbank_id = '|| pCashbankId;
  81.         vFilterCashbankV3 := 'AND E.cashbank_id = '|| pCashbankId;
  82.     END IF;
  83.    
  84.     -- CB TRANSFER YG CB OU dari berasal dari assign ou sesuai filter (Untuk OU yang Transfer)
  85.     EXECUTE '
  86.         -- CB Transfer -> nilai dokumen keluar
  87.         INSERT INTO tt_detail_mutation_cashbank(
  88.             session_id, tenant_id, order_type_data,
  89.             doc_type_id, doc_type_desc,
  90.             doc_no, doc_date, ou_id, ou_code,
  91.             ou_rc_id, ou_rc_code, amount,
  92.             cashbank_code, cashbank_name, account_no,
  93.             mode_payment, bank_payment, curr_code, remark,
  94.             create_user_id, create_user_name, create_datetime,
  95.             update_user_id, update_user_name, update_datetime,
  96.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  97.             coa_acc, coa_desc)
  98.         SELECT  $1, $2, 2,
  99.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  100.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  101.             X.ou_id, f_get_ou_code(X.ou_id), -1 * (B.transfer_amount),
  102.             G.cashbank_code, G.cashbank_name, G.account_no,
  103.             G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
  104.             A.create_user_id, D.username, A.create_datetime,
  105.             A.update_user_id, E.username, A.update_datetime,
  106.             A.remark, $7, $7, $7,
  107.             CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  108.         FROM cb_transfer_cashbank A
  109.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  110.         INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  111.         INNER JOIN m_cashbank_assignment_ou X ON B.cashbank_to_id = X.cashbank_id
  112.         INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
  113.                     AND A.doc_type_id = O.doc_type_id
  114.                     AND A.tenant_id = O.tenant_id
  115.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  116.                     AND X.cashbank_id = P.cashbank_id
  117.                     --AND O.doc_type_id = P.ref_doc_type_id
  118.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  119.         INNER JOIN t_user D ON A.create_user_id = D.user_id
  120.         INNER JOIN t_user E ON A.update_user_id = E.user_id
  121.         INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  122.         WHERE A.tenant_id = $2
  123.         AND C.ou_id = $6 '||
  124.         vFilterCashbankV1   ||'
  125.         AND A.workflow_status = $3
  126.         AND P.sign_journal = ''D''
  127.         AND A.doc_date BETWEEN $4 AND $5
  128.  
  129.         UNION ALL
  130.  
  131.         -- CB Transfer -> nilai cost
  132.         SELECT $1, $2, 2,
  133.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  134.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  135.             B.ou_to_id, f_get_ou_code(B.ou_to_id), -1 * (COALESCE(C.payment_amount, 0)),
  136.             G.cashbank_code, G.cashbank_name, G.account_no,
  137.             G.flg_cash_bank, G.bank_code, C.curr_code, C.remark,
  138.             A.create_user_id, E.username, A.create_datetime,
  139.             A.update_user_id, F.username, A.update_datetime,
  140.             A.remark, $7, K.activity_gl_code, K.activity_gl_name,
  141.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  142.         FROM cb_transfer_cashbank A
  143.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  144.         INNER JOIN cb_transfer_cashbank_cost C ON A.transfer_cashbank_id = C.transfer_cashbank_id
  145.         INNER JOIN m_cashbank_assignment_ou D ON A.cashbank_id = D.cashbank_id
  146.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  147.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  148.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  149.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  150.         INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  151.         WHERE A.tenant_id = $2
  152.         AND D.ou_id = $6 '||
  153.         vFilterCashbankV1   ||'
  154.         AND A.workflow_status = $3
  155.         AND A.doc_date BETWEEN $4 AND $5
  156.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, pOuId, vEmpty;
  157.  
  158.     -- CB TRANSFER YG CB OU dari berasal dari assign ou sesuai filter (Untuk OU yang Penerima)
  159.     EXECUTE '
  160.         INSERT INTO tt_detail_mutation_cashbank(
  161.             session_id, tenant_id, order_type_data,
  162.             doc_type_id, doc_type_desc,
  163.             doc_no, doc_date, ou_id, ou_code,
  164.             ou_rc_id, ou_rc_code, amount,
  165.             cashbank_code, cashbank_name, account_no,
  166.             mode_payment, bank_payment, curr_code, remark,
  167.             create_user_id, create_user_name, create_datetime,
  168.             update_user_id, update_user_name, update_datetime,
  169.             remark_header, transaction_type, coa_acc, coa_desc)
  170.         SELECT $1, $2, 2,
  171.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  172.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  173.             Z.ou_id, f_get_ou_code(Z.ou_id), B.receive_amount,
  174.             G.cashbank_code, G.cashbank_name, G.account_no,
  175.             G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
  176.             A.create_user_id, E.username, A.create_datetime,
  177.             A.update_user_id, F.username, A.update_datetime,
  178.             A.remark, $8, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  179.         FROM cb_transfer_cashbank A
  180.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  181.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_to_id = Z.cashbank_id
  182.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  183.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  184.         INNER JOIN m_cashbank G ON B.cashbank_to_id = G.cashbank_id
  185.         INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
  186.                     AND A.doc_type_id = O.doc_type_id
  187.                     AND A.tenant_id = O.tenant_id
  188.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  189.                     AND A.cashbank_id = P.cashbank_id
  190.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  191.         WHERE A.tenant_id = $2
  192.         AND Z.ou_id = $7 '||
  193.         vFilterCashbankV1   ||'
  194.         AND A.workflow_status = $3
  195.         AND P.sign_journal = ''C''
  196.         AND A.doc_date BETWEEN $4 AND $5
  197.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, pOuId, vEmpty;
  198.  
  199.     -- CB Out dari Payment Order (AP)
  200.     EXECUTE '
  201.         INSERT INTO tt_detail_mutation_cashbank(
  202.             session_id, tenant_id, order_type_data,
  203.             doc_type_id, doc_type_desc,
  204.             doc_no, doc_date, ou_id, ou_code,
  205.             ou_rc_id, ou_rc_code, amount,
  206.             cashbank_code, cashbank_name, account_no,
  207.             mode_payment, bank_payment, curr_code, remark,
  208.             create_user_id, create_user_name, create_datetime,
  209.             update_user_id, update_user_name, update_datetime,
  210.             remark_header, transaction_type, coa_acc, coa_desc)
  211.         SELECT $1, $2, 2,
  212.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  213.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  214.             A.ou_id, f_get_ou_code(A.ou_id), -1 * B.cashbank_amount,
  215.             G.cashbank_code, G.cashbank_name, G.account_no,
  216.             G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
  217.             A.create_user_id, E.username, A.create_datetime,
  218.             A.update_user_id, F.username, A.update_datetime,
  219.             A.remark, ''CBO - Payment Order (AP)'', CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  220.         FROM cb_in_out_cashbank A
  221.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  222.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  223.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  224.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  225.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  226.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  227.                     AND A.doc_type_id = O.doc_type_id
  228.                     AND A.tenant_id = O.tenant_id
  229.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  230.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  231.         WHERE A.tenant_id = $2
  232.         AND Z.ou_id = $7 ' ||
  233.         vFilterCashbankV2||'
  234.         AND A.doc_date BETWEEN $4 AND $5
  235.         AND A.workflow_status = $3
  236.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  237.         AND P.line_no = 1
  238.         AND A.doc_type_id = $6
  239.         AND A.ref_doc_type_id = $8; '
  240.         USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
  241.         vDocTypePaymentOrderAP, vEmpty;
  242.  
  243.     -- CB Out dari Payment Order (Non AP)
  244.     EXECUTE '
  245.         INSERT INTO tt_detail_mutation_cashbank(
  246.             session_id, tenant_id, order_type_data,
  247.             doc_type_id, doc_type_desc,
  248.             doc_no, doc_date, ou_id, ou_code,
  249.             ou_rc_id, ou_rc_code, amount,
  250.             cashbank_code, cashbank_name, account_no,
  251.             mode_payment, bank_payment, curr_code, remark,
  252.             create_user_id, create_user_name, create_datetime,
  253.             update_user_id, update_user_name, update_datetime,
  254.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  255.             coa_acc, coa_desc)
  256.         SELECT $1, $2, 2,
  257.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  258.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  259.             A.ou_id, f_get_ou_code(A.ou_id), -1 * H.add_amount,
  260.             G.cashbank_code, G.cashbank_name, G.account_no,
  261.             G.flg_cash_bank, G.bank_code, H.curr_code, H.remark,
  262.             A.create_user_id, E.username, A.create_datetime,
  263.             A.update_user_id, F.username, A.update_datetime,
  264.             A.remark, ''CBO - Payment Order CB'', K.activity_gl_code, K.activity_gl_name,
  265.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  266.         FROM cb_in_out_cashbank A
  267.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  268.         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
  269.         INNER JOIN cb_payment_order D ON C.payment_id = D.payment_order_id AND C.doc_type_id = D.doc_type_id
  270.         INNER JOIN cb_payment_order_cost H ON D.payment_order_id = H.payment_order_id
  271.         INNER JOIN m_activity_gl K ON H.activity_gl_id = K.activity_gl_id
  272.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  273.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  274.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  275.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  276.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  277.         WHERE A.tenant_id = $2
  278.         AND Z.ou_id = $7 ' ||
  279.         vFilterCashbankV2||'
  280.         AND A.doc_date BETWEEN $4 AND $5
  281.         AND A.workflow_status = $3
  282.         AND A.doc_type_id = $6
  283.         AND A.ref_doc_type_id = $8; '
  284.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
  285.     vDocTypePaymentOrderNonAP, vEmpty;
  286.  
  287.     -- CB Out dari Req Cash Advance
  288.     EXECUTE '
  289.         INSERT INTO tt_detail_mutation_cashbank(
  290.             session_id, tenant_id, order_type_data,
  291.             doc_type_id, doc_type_desc,
  292.             doc_no, doc_date, ou_id, ou_code,
  293.             ou_rc_id, ou_rc_code, amount,
  294.             cashbank_code, cashbank_name, account_no,
  295.             mode_payment, bank_payment, curr_code, remark,
  296.             create_user_id, create_user_name, create_datetime,
  297.             update_user_id, update_user_name, update_datetime,
  298.             remark_header, transaction_type, coa_acc, coa_desc)
  299.         SELECT $1, $2, 2,
  300.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  301.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  302.             D.ou_id, f_get_ou_code(D.ou_id), -1 * B.cashbank_amount,
  303.             G.cashbank_code, G.cashbank_name, G.account_no,
  304.             G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
  305.             A.create_user_id, E.username, A.create_datetime,
  306.             A.update_user_id, F.username, A.update_datetime,
  307.             A.remark, ''CBO - Cash Advance'', CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  308.         FROM cb_in_out_cashbank A
  309.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  310.         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
  311.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  312.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  313.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  314.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  315.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  316.             AND A.doc_type_id = O.doc_type_id
  317.             AND A.tenant_id = O.tenant_id
  318.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  319.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  320.         WHERE A.tenant_id = $2
  321.         AND Z.ou_id = $7 ' ||
  322.         vFilterCashbankV2||'
  323.         AND A.doc_date BETWEEN $4 AND $5
  324.         AND A.workflow_status = $3
  325.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  326.         AND A.doc_type_id = $6
  327.         AND A.ref_doc_type_id = $8; '
  328.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
  329.     vDocTypeRequestCashAdvance, vEmpty;
  330.  
  331.     -- CB IN Partner Receive -> nilai dokumen masuk
  332.     EXECUTE '
  333.         INSERT INTO tt_detail_mutation_cashbank(
  334.             session_id, tenant_id, order_type_data,
  335.             doc_type_id, doc_type_desc,
  336.             doc_no, doc_date, ou_id, ou_code,
  337.             ou_rc_id, ou_rc_code, amount,
  338.             cashbank_code, cashbank_name, account_no,
  339.             mode_payment, bank_payment, curr_code, remark,
  340.             create_user_id, create_user_name, create_datetime,
  341.             update_user_id, update_user_name, update_datetime,
  342.             remark_header, transaction_type, coa_acc, coa_desc)
  343.         SELECT $1, $2, 2,  
  344.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  345.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  346.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  347.             G.cashbank_code, G.cashbank_name, G.account_no,
  348.             G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
  349.             A.create_user_id, E.username, A.create_datetime,
  350.             A.update_user_id, F.username, A.update_datetime,
  351.             A.remark, $7, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  352.         FROM cb_in_out_cashbank A
  353.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  354.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  355.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  356.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  357.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  358.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  359.             AND A.doc_type_id = O.doc_type_id
  360.             AND A.tenant_id = O.tenant_id
  361.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  362.             AND O.doc_id = P.ref_id
  363.             AND O.doc_type_id = P.ref_doc_type_id
  364.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  365.         WHERE A.tenant_id = $2
  366.         AND Z.ou_id = $9 '||
  367.         vFilterCashbankV2||'
  368.         AND A.doc_date BETWEEN $4 AND $5
  369.         AND A.workflow_status = $3
  370.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  371.         AND A.doc_type_id = $8;
  372.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCashBankInAr, pOuId;
  373.    
  374.     -- CB IN Partner Receive -> jika ada nilai cost
  375.     EXECUTE '
  376.         INSERT INTO tt_detail_mutation_cashbank(
  377.             session_id, tenant_id, order_type_data,
  378.             doc_type_id, doc_type_desc,
  379.             doc_no, doc_date, ou_id, ou_code,
  380.             ou_rc_id, ou_rc_code, amount,
  381.             cashbank_code, cashbank_name, account_no,
  382.             mode_payment, bank_payment, curr_code, remark,
  383.             create_user_id, create_user_name, create_datetime,
  384.             update_user_id, update_user_name, update_datetime,
  385.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  386.             coa_acc, coa_desc)
  387.         SELECT $1, $2, 2,  
  388.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  389.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  390.             A.ou_id, f_get_ou_code(A.ou_id), -1 * C.cost_amount,
  391.             G.cashbank_code, G.cashbank_name, G.account_no,
  392.             G.flg_cash_bank, G.bank_code, B.curr_code, C.remark,
  393.             A.create_user_id, E.username, A.create_datetime,
  394.             A.update_user_id, F.username, A.update_datetime,
  395.             A.remark, $6, K.activity_gl_code, K.activity_gl_name,
  396.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  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_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  400.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  401.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  402.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  403.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  404.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  405.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  406.         WHERE A.tenant_id = $2
  407.         AND Z.ou_id = $8 '||
  408.         vFilterCashbankV2||'
  409.         AND A.doc_date BETWEEN $4 AND $5
  410.         AND A.workflow_status = $3
  411.         AND A.doc_type_id = $7
  412.         AND C.cost_amount <> 0;
  413.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInAr, pOuId;
  414.    
  415.     -- CB IN Other -> nilai dokumen (per activity gl terima)
  416.     EXECUTE '
  417.         INSERT INTO tt_detail_mutation_cashbank(
  418.             session_id, tenant_id, order_type_data,
  419.             doc_type_id, doc_type_desc,
  420.             doc_no, doc_date, ou_id, ou_code,
  421.             ou_rc_id, ou_rc_code, amount,
  422.             cashbank_code, cashbank_name, account_no,
  423.             mode_payment, bank_payment, curr_code, remark,
  424.             create_user_id, create_user_name, create_datetime,
  425.             update_user_id, update_user_name, update_datetime,
  426.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  427.             coa_acc, coa_desc)
  428.         SELECT $1, $2, 2,  
  429.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  430.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  431.             Z.ou_id, f_get_ou_code(Z.ou_id), C.cost_amount,
  432.             G.cashbank_code, G.cashbank_name, G.account_no,
  433.             G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
  434.             A.create_user_id, E.username, A.create_datetime,
  435.             A.update_user_id, F.username, A.update_datetime,
  436.             A.remark, $6, K.activity_gl_code, K.activity_gl_name,
  437.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  438.         FROM cb_in_out_cashbank A
  439.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  440.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  441.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  442.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  443.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  444.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  445.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  446.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  447.         WHERE A.tenant_id = $2
  448.         AND Z.ou_id = $8 '||
  449.         vFilterCashbankV2||'
  450.         AND A.doc_date BETWEEN $4 AND $5
  451.         AND A.workflow_status = $3
  452.         AND A.doc_type_id = $7;
  453.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInOther, pOuId;
  454.  
  455.     -- Cash Advance Settlement (pengembalian BS)
  456.     EXECUTE '
  457.         INSERT INTO tt_detail_mutation_cashbank(
  458.             session_id, tenant_id, order_type_data,
  459.             doc_type_id, doc_type_desc,
  460.             doc_no, doc_date, ou_id, ou_code,
  461.             ou_rc_id, ou_rc_code, amount,
  462.             cashbank_code, cashbank_name, account_no,
  463.             mode_payment, bank_payment, curr_code, remark,
  464.             create_user_id, create_user_name, create_datetime,
  465.             update_user_id, update_user_name, update_datetime,
  466.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  467.             coa_acc, coa_desc)
  468.         SELECT $1, $2, 2,
  469.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  470.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  471.             Z.ou_id, f_get_ou_code(Z.ou_id), E.cashbank_amount,
  472.             G.cashbank_code, G.cashbank_name, G.account_no,
  473.             G.flg_cash_bank, G.bank_code, E.curr_code, A.remark,
  474.             A.create_user_id, H.username, A.create_datetime,
  475.             A.update_user_id, I.username, A.update_datetime,
  476.             A.remark, $6, $6, $6,
  477.             CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  478.         FROM cb_advance_settle A
  479.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  480.         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
  481.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  482.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  483.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  484.         INNER JOIN gl_journal_trx O ON D.in_out_cashbank_id = O.doc_id
  485.                     AND D.doc_type_id = O.doc_type_id
  486.                     AND D.tenant_id = O.tenant_id
  487.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  488.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  489.         INNER JOIN t_user H ON A.create_user_id = H.user_id
  490.         INNER JOIN t_user I ON A.update_user_id = I.user_id
  491.         WHERE A.tenant_id = $2
  492.         AND Z.ou_id = $8 '||
  493.         vFilterCashbankV3||'
  494.         AND A.doc_date BETWEEN $4 AND $5
  495.         AND A.workflow_status = $3
  496.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  497.         AND A.doc_type_id = $7; '
  498.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;
  499.  
  500.     -- Cash Advance Settlement (cost activity gl)
  501.     EXECUTE '
  502.         INSERT INTO tt_detail_mutation_cashbank(
  503.             session_id, tenant_id, order_type_data,
  504.             doc_type_id, doc_type_desc,
  505.             doc_no, doc_date, ou_id, ou_code,
  506.             ou_rc_id,
  507.             ou_rc_code,
  508.             amount,
  509.             cashbank_code, cashbank_name, account_no,
  510.             mode_payment, bank_payment, curr_code, remark,
  511.             create_user_id, create_user_name, create_datetime,
  512.             update_user_id, update_user_name, update_datetime,
  513.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  514.             coa_acc, coa_desc)
  515.         SELECT $1, $2, 2,
  516.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  517.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  518.             CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
  519.                 CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
  520.                     (A.ou_id)
  521.                 ELSE (COALESCE(F.ou_sub_bu_id, A.ou_id)) END
  522.             ELSE (COALESCE(F.ou_branch_id, A.ou_id)) END AS ou_rc_id,
  523.             CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
  524.                 CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
  525.                     f_get_ou_code(A.ou_id)
  526.                 ELSE f_get_ou_code(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
  527.             ELSE f_get_ou_code(COALESCE(F.ou_branch_id, A.ou_id)) END AS ou_rc_code,
  528.             -1 * B.cost_amount,
  529.             G.cashbank_code, G.cashbank_name, G.account_no,
  530.             G.flg_cash_bank, G.bank_code, E.curr_code, B.remark,
  531.             A.create_user_id, H.username, A.create_datetime,
  532.             A.update_user_id, I.username, A.update_datetime,
  533.             A.remark, $6, K.activity_gl_code, K.activity_gl_name,
  534.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  535.         FROM cb_advance_settle A
  536.         INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  537.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  538.         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
  539.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  540.         LEFT OUTER JOIN cb_advance_settle_cost_custom_for_sasa F ON B.advance_settle_cost_id = F.advance_settle_cost_id
  541.         INNER JOIN m_activity_gl K ON B.activity_gl_id = K.activity_gl_id
  542.         INNER JOIN m_coa L ON L.coa_id = K.coa_id
  543.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  544.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  545.         INNER JOIN t_user H ON A.create_user_id = H.user_id
  546.         INNER JOIN t_user I ON A.update_user_id = I.user_id
  547.         WHERE A.tenant_id = $2
  548.         AND Z.ou_id = $8 '||
  549.         vFilterCashbankV3||'
  550.         AND A.doc_date BETWEEN $4 AND $5
  551.         AND A.workflow_status = $3
  552.         AND A.doc_type_id = $7; '
  553.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;
  554.  
  555.     -- EDC  Settlement -> nilai dokumen masuk
  556.     EXECUTE '
  557.         INSERT INTO tt_detail_mutation_cashbank(
  558.             session_id, tenant_id, order_type_data,
  559.             doc_type_id, doc_type_desc,
  560.             doc_no, doc_date, ou_id, ou_code,
  561.             ou_rc_id, ou_rc_code, amount,
  562.             cashbank_code, cashbank_name, account_no,
  563.             mode_payment, bank_payment, curr_code, remark,
  564.             create_user_id, create_user_name, create_datetime,
  565.             update_user_id, update_user_name, update_datetime,
  566.             remark_header, transaction_type, coa_acc, coa_desc)
  567.         SELECT $1, $2, 2,  
  568.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  569.             A.doc_no, A.doc_date, Y.ou_id, f_get_ou_code(Y.ou_id),
  570.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  571.             H.cashbank_code, H.cashbank_name, H.account_no,
  572.             H.flg_cash_bank, H.bank_code, B.curr_code, A.remark,
  573.             A.create_user_id, I.username, A.create_datetime,
  574.             A.update_user_id, J.username, A.update_datetime,
  575.             A.remark, $7, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  576.         FROM cb_in_out_cashbank A
  577.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  578.         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
  579.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  580.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  581.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  582.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  583.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  584.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  585.                     AND A.doc_type_id = O.doc_type_id
  586.                     AND A.tenant_id = O.tenant_id
  587.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  588.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  589.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  590.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  591.         WHERE A.tenant_id = $2
  592.         AND Z.ou_id = $9 '||
  593.         vFilterCashbankV2||'
  594.         AND A.doc_date BETWEEN $4 AND $5
  595.         AND A.workflow_status = $3
  596.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  597.         AND A.doc_type_id = $8;
  598.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
  599.  
  600.     -- EDC  Settlement -> nilai cost
  601.     EXECUTE '
  602.         INSERT INTO tt_detail_mutation_cashbank(
  603.             session_id, tenant_id, order_type_data,
  604.             doc_type_id, doc_type_desc,
  605.             doc_no, doc_date, ou_id, ou_code,
  606.             ou_rc_id, ou_rc_code, amount,
  607.             cashbank_code, cashbank_name, account_no,
  608.             mode_payment, bank_payment, curr_code, remark,
  609.             create_user_id, create_user_name, create_datetime,
  610.             update_user_id, update_user_name, update_datetime,
  611.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  612.             coa_acc, coa_desc)
  613.         SELECT $1, $2, 2,  
  614.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  615.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  616.             Y.ou_id, f_get_ou_code(Y.ou_id), -1 * C.cost_amount,
  617.             H.cashbank_code, H.cashbank_name, H.account_no,
  618.             H.flg_cash_bank, H.bank_code, B.curr_code, A.remark,
  619.             A.create_user_id, I.username, A.create_datetime,
  620.             A.update_user_id, J.username, A.update_datetime,
  621.             A.remark, $7, K.activity_gl_code, K.activity_gl_name,
  622.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  623.         FROM cb_in_out_cashbank A
  624.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  625.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  626.         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
  627.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  628.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  629.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  630.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  631.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  632.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  633.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  634.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  635.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  636.         WHERE A.tenant_id = $2
  637.         AND Z.ou_id = $9 '||
  638.         vFilterCashbankV2||'
  639.         AND A.doc_date BETWEEN $4 AND $5
  640.         AND A.workflow_status = $3
  641.         AND A.doc_type_id = $8
  642.         AND C.cost_amount <> 0;
  643.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
  644.  
  645.         -- **WARNING: Jika comment dilepas, perlu penyesuaian data2 yang di insert ke temp table**
  646.  
  647. --  -- Conversion CB In Other to Partner (nilai dokumen yang konversi)
  648. --  EXECUTE '
  649. --      INSERT INTO tt_detail_mutation_cashbank(
  650. --          session_id, tenant_id, order_type_data,
  651. --          doc_type_id, doc_type_desc,
  652. --          doc_no, doc_date, ou_id, ou_code,
  653. --          ou_rc_id, ou_rc_code, amount,
  654. --          mode_payment, bank_payment, curr_code, remark,
  655. --          create_user_id, create_user_name, create_datetime,
  656. --          update_user_id, update_user_name, update_datetime,
  657. --          remark_header, transaction_type)
  658. --      -- nilai yang dikonversi
  659. --      SELECT $1, $2, 2,
  660. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  661. --          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  662. --          Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  663. --          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  664. --          A.create_user_id, E.username, A.create_datetime,
  665. --          A.update_user_id, F.username, A.update_datetime,
  666. --          A.remark, $6
  667. --      FROM cb_in_out_cashbank A
  668. --      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  669. --      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
  670. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  671. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  672. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  673. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  674. --      WHERE A.tenant_id = $2
  675. --      AND Z.ou_id = $8 '||
  676. --      vFilterCashbankV2||'
  677. --      AND A.doc_date BETWEEN $4 AND $5
  678. --      AND A.workflow_status = $3
  679. --      AND A.doc_type_id = $7
  680. --
  681. --      UNION ALL
  682. --      --nilai yang terkonversi
  683. --      SELECT $1, $2, 2,
  684. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  685. --          A.doc_no, A.doc_date, C.ou_id_cb_in_partner, f_get_ou_code(C.ou_id_cb_in_partner),
  686. --          Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  687. --          G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
  688. --          A.create_user_id, E.username, A.create_datetime,
  689. --          A.update_user_id, F.username, A.update_datetime,
  690. --          A.remark, $6
  691. --      FROM cb_in_out_cashbank A
  692. --      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  693. --      INNER JOIN cb_in_out_cashbank_ext C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  694. --      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
  695. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  696. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  697. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  698. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  699. --      WHERE A.tenant_id = $2
  700. --      AND Z.ou_id = $8 '||
  701. --      vFilterCashbankV2||'
  702. --      AND A.doc_date BETWEEN $4 AND $5
  703. --      AND A.workflow_status = $3
  704. --      AND A.doc_type_id = $7
  705. --  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
  706. --
  707. --  -- Conversion CB In Other to Partner (jika ada cost)
  708. --  EXECUTE '
  709. --  INSERT INTO tt_detail_mutation_cashbank(
  710. --          session_id, tenant_id, order_type_data,
  711. --          doc_type_id, doc_type_desc,
  712. --          doc_no, doc_date, ou_id, ou_code,
  713. --          ou_rc_id, ou_rc_code, amount,
  714. --          mode_payment, bank_payment, curr_code, remark,
  715. --          create_user_id, create_user_name, create_datetime,
  716. --          update_user_id, update_user_name, update_datetime,
  717. --          remark_header, transaction_type, activity_gl_code, activity_gl_name)
  718. --      -- nilai yang dikonversi
  719. --      SELECT $1, $2, 2,
  720. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  721. --          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  722. --          Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  723. --          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  724. --          A.create_user_id, E.username, A.create_datetime,
  725. --          A.update_user_id, F.username, A.update_datetime,
  726. --          A.remark, $6, K.activity_gl_code, K.activity_gl_name
  727. --      FROM cb_in_out_cashbank A
  728. --      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  729. --      INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  730. --      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
  731. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  732. --      INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  733. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  734. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  735. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  736. --      WHERE A.tenant_id = $2
  737. --      AND Z.ou_id = $8 '||
  738. --      vFilterCashbankV2||'
  739. --      AND A.doc_date BETWEEN $4 AND $5
  740. --      AND A.workflow_status = $3
  741. --      AND A.doc_type_id = $7
  742. --  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
  743. --
  744. --
  745. --  -- Conversion CB In Partner to Other (nilai dokumen yang konversi)
  746. --  EXECUTE '
  747. --      INSERT INTO tt_detail_mutation_cashbank(
  748. --          session_id, tenant_id, order_type_data,
  749. --          doc_type_id, doc_type_desc,
  750. --          doc_no, doc_date, ou_id, ou_code,
  751. --          ou_rc_id, ou_rc_code, amount,
  752. --          mode_payment, bank_payment, curr_code, remark,
  753. --          create_user_id, create_user_name, create_datetime,
  754. --          update_user_id, update_user_name, update_datetime,
  755. --          remark_header, transaction_type, activity_gl_code, activity_gl_name)
  756. --      -- nilai yang dikonversi
  757. --      SELECT $1, $2, 2,
  758. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  759. --          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  760. --          X.ou_id, f_get_ou_code(X.ou_id), -1 * B.cashbank_amount,
  761. --          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  762. --          A.create_user_id, E.username, A.create_datetime,
  763. --          A.update_user_id, F.username, A.update_datetime,
  764. --          A.remark, $6, K.activity_gl_code, K.activity_gl_name
  765. --      FROM cb_in_out_cashbank A
  766. --      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  767. --      INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
  768. --      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
  769. --      INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
  770. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  771. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  772. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  773. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  774. --      WHERE A.tenant_id = $2
  775. --      AND Z.ou_id = $8 '||
  776. --      vFilterCashbankV2||'
  777. --      AND A.doc_date BETWEEN $4 AND $5
  778. --      AND A.workflow_status = $3
  779. --      AND A.doc_type_id = $7
  780. --
  781. --      UNION ALL
  782. --      --nilai yang terkonversi
  783. --      SELECT $1, $2, 2,
  784. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  785. --          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  786. --          Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  787. --          G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
  788. --          A.create_user_id, E.username, A.create_datetime,
  789. --          A.update_user_id, F.username, A.update_datetime,
  790. --          A.remark, $6, K.activity_gl_code, K.activity_gl_name
  791. --      FROM cb_in_out_cashbank A
  792. --      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  793. --      INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
  794. --      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
  795. --      INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
  796. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  797. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  798. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  799. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  800. --      WHERE A.tenant_id = $2
  801. --      AND Z.ou_id = $8 '||
  802. --      vFilterCashbankV2||'
  803. --      AND A.doc_date BETWEEN $4 AND $5
  804. --      AND A.workflow_status = $3
  805. --      AND A.doc_type_id = $7
  806. --  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInPO, pOuId;
  807.  
  808.     -- Cheque Giro Realization -> nilai dokumen masuk (OU From ambil dari OU Partner)
  809. --  EXECUTE '
  810. --      INSERT INTO tt_detail_mutation_cashbank(
  811. --          session_id, tenant_id, order_type_data,
  812. --          doc_type_id, doc_type_desc,
  813. --          doc_no, doc_date, ou_id, ou_code,
  814. --          ou_rc_id, ou_rc_code, amount,
  815. --          mode_payment, bank_payment, curr_code, remark,
  816. --          create_user_id, create_user_name, create_datetime,
  817. --          update_user_id, update_user_name, update_datetime,
  818. --          remark_header, transaction_type)
  819. --      SELECT $1, $2, 2,
  820. --          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  821. --          A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  822. --          Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
  823. --          G.flg_cash_bank, B.bank_payment, B.curr_code, B.remark,
  824. --          A.create_user_id, E.username, A.create_datetime,
  825. --          A.update_user_id, F.username, A.update_datetime,
  826. --          A.remark, $6
  827. --      FROM cb_in_out_cashbank A
  828. --      INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  829. --      INNER JOIN m_partner_ou C ON B.partner_id = C.partner_id
  830. --      INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
  831. --      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  832. --      INNER JOIN t_user E ON A.create_user_id = E.user_id
  833. --      INNER JOIN t_user F ON A.update_user_id = F.user_id
  834. --      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  835. --      WHERE A.tenant_id = $2
  836. --      AND Z.ou_id = $8 '||
  837. --      vFilterCashbankV2||'
  838. --      AND A.doc_date BETWEEN $4 AND $5
  839. --      AND A.workflow_status = $3
  840. --      AND A.doc_type_id = $7
  841. --      AND B.realization_status = $9;
  842. --  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;
  843.  
  844.     -- Cheque Giro Realization -> nilai dokumen masuk (OU From ambil dari OU Header Document)
  845.     EXECUTE '
  846.         INSERT INTO tt_detail_mutation_cashbank(
  847.             session_id, tenant_id, order_type_data,
  848.             doc_type_id, doc_type_desc,
  849.             doc_no, doc_date, ou_id, ou_code,
  850.             ou_rc_id, ou_rc_code, amount,
  851.             cashbank_code, cashbank_name, account_no,
  852.             mode_payment, bank_payment, curr_code, remark,
  853.             create_user_id, create_user_name, create_datetime,
  854.             update_user_id, update_user_name, update_datetime,
  855.             remark_header, transaction_type, coa_acc, coa_desc)
  856.         SELECT $1, $2, 2,      
  857.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  858.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  859.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
  860.             G.cashbank_code, G.cashbank_name, G.account_no,
  861.             G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
  862.             A.create_user_id, E.username, A.create_datetime,
  863.             A.update_user_id, F.username, A.update_datetime,
  864.             A.remark, $6, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
  865.         FROM cb_in_out_cashbank A
  866.         INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  867.         INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
  868.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  869.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  870.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  871.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  872.         INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
  873.                     AND A.doc_type_id = O.doc_type_id
  874.                     AND A.tenant_id = O.tenant_id
  875.         INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
  876.                     AND O.doc_id = P.ref_id
  877.                     AND O.doc_type_id = P.ref_doc_type_id
  878.         INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
  879.         WHERE A.tenant_id = $2
  880.         AND Z.ou_id = $8 '||
  881.         vFilterCashbankV2||'
  882.         AND A.doc_date BETWEEN $4 AND $5
  883.         AND A.workflow_status = $3
  884.         AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
  885.         AND A.doc_type_id = $7
  886.         AND B.realization_status = $9;
  887.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;
  888.  
  889.     -- Cheque Giro Realization -> nilai cost
  890.     EXECUTE '
  891.         WITH tt_in_out_cashbank AS(
  892.             SELECT A.in_out_cashbank_id, B.cashbank_id
  893.             FROM cb_in_out_cashbank A
  894.             INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  895.             WHERE B.realization_status = $10 '||
  896.             vFilterCashbankV2||'
  897.             GROUP BY A.in_out_cashbank_id, B.cashbank_id
  898.         )
  899.         INSERT INTO tt_detail_mutation_cashbank(
  900.             session_id, tenant_id, order_type_data,
  901.             doc_type_id, doc_type_desc,
  902.             doc_no, doc_date, ou_id, ou_code,
  903.             ou_rc_id, ou_rc_code, amount,
  904.             cashbank_code, cashbank_name, account_no,
  905.             mode_payment, bank_payment, curr_code, remark,
  906.             create_user_id, create_user_name, create_datetime,
  907.             update_user_id, update_user_name, update_datetime,
  908.             remark_header, transaction_type, activity_gl_code, activity_gl_name,
  909.             coa_acc, coa_desc)
  910.         SELECT $1, $2, 2,  
  911.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  912.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  913.             COALESCE(C.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(C.ou_rc_id, A.ou_id)), -1 * C.cost_amount,
  914.             G.cashbank_code, G.cashbank_name, G.account_no,
  915.             G.flg_cash_bank, G.bank_code, C.curr_code, C.remark,
  916.             A.create_user_id, E.username, A.create_datetime,
  917.             A.update_user_id, F.username, A.update_datetime,
  918.             A.remark, $7, K.activity_gl_code, K.activity_gl_name,
  919.             CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
  920.         FROM cb_in_out_cashbank A
  921.         INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  922.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  923.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  924.         INNER JOIN m_coa L ON K.coa_id = L.coa_id
  925.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  926.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  927.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  928.         INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
  929.         WHERE A.tenant_id = $2
  930.         AND Z.ou_id = $9
  931.         AND A.doc_date BETWEEN $4 AND $5
  932.         AND A.workflow_status = $3
  933.         AND A.doc_type_id = $8
  934.         AND C.cost_amount <> 0;
  935.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCGRealization, pOuId, vStatusAccept;
  936.    
  937.     -- POS
  938.     EXECUTE '
  939.     INSERT INTO tt_detail_mutation_cashbank(
  940.         session_id, tenant_id, order_type_data,
  941.         doc_type_id, doc_type_desc,
  942.         doc_no, doc_date, ou_id, ou_code,
  943.         ou_rc_id, ou_rc_code,
  944.         amount,
  945.         cashbank_code, cashbank_name, account_no,
  946.         mode_payment, bank_payment, curr_code, remark,
  947.         create_user_id, create_user_name, create_datetime,
  948.         update_user_id, update_user_name, update_datetime,
  949.         remark_header, transaction_type, coa_acc, coa_desc)
  950.     SELECT $1, $2, 2,
  951.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  952.         A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  953.         A.ou_id, f_get_ou_code(A.ou_id),
  954.         CASE WHEN E.flg_termin = $9 THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END AS amount,
  955.         D.cashbank_code, D.cashbank_name, D.account_no,
  956.         D.flg_cash_bank, D.bank_code, A.curr_code, A.remark,
  957.         A.create_user_id, F.username, A.create_datetime,
  958.         A.update_user_id, G.username, A.update_datetime,
  959.         A.remark, $6, $6, $6
  960.     FROM i_trx_pos A
  961.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  962.         AND A.process_no = B.process_no
  963.         AND A.trx_pos_id = B.trx_pos_id
  964.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  965.         AND A.process_no = E.process_no
  966.         AND A.trx_pos_id = E.trx_pos_id
  967.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  968.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  969.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  970.     INNER JOIN t_user F ON A.create_user_id = F.user_id
  971.     INNER JOIN t_user G ON A.update_user_id = G.user_id
  972.     WHERE A.tenant_id = $2
  973.     AND Z.ou_id = $7
  974.     AND A.status IN ($3, $10)
  975.     AND A.doc_date BETWEEN $4 AND $5;
  976.     'USING pSessionId, pTenantId, vStatusSubmit, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes, vStatusVoid;
  977.  
  978.     --POS Void
  979.     EXECUTE '
  980.     INSERT INTO tt_detail_mutation_cashbank(
  981.         session_id, tenant_id, order_type_data,
  982.         doc_type_id, doc_type_desc,
  983.         doc_no, doc_date, ou_id, ou_code,
  984.         ou_rc_id, ou_rc_code,
  985.         amount,
  986.         cashbank_code, cashbank_name, account_no,
  987.         mode_payment, bank_payment, curr_code, remark,
  988.         create_user_id, create_user_name, create_datetime,
  989.         update_user_id, update_user_name, update_datetime,
  990.         remark_header, transaction_type, coa_acc, coa_desc)
  991.     SELECT $1, $2, 2,
  992.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  993.         A.doc_no||''_VOID'', F.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  994.         A.ou_id, f_get_ou_code(A.ou_id),
  995.         CASE WHEN E.flg_termin = $9 THEN -1 * B.payment_amount ELSE -1 * (B.payment_amount- A.total_refund) END AS amount,
  996.         D.cashbank_code, D.cashbank_name, D.account_no,
  997.         D.flg_cash_bank, D.bank_code, A.curr_code, F.remark,
  998.         F.create_user_id, G.username, F.create_datetime,
  999.         F.update_user_id, H.username, F.update_datetime,
  1000.         A.remark, $6, $6, $6
  1001.     FROM i_trx_pos A
  1002.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  1003.         AND A.process_no = B.process_no
  1004.         AND A.trx_pos_id = B.trx_pos_id
  1005.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  1006.         AND A.process_no = E.process_no
  1007.         AND A.trx_pos_id = E.trx_pos_id
  1008.     INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
  1009.         AND A.process_no = F.process_no
  1010.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  1011.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  1012.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  1013.     INNER JOIN t_user G ON F.create_user_id = G.user_id
  1014.     INNER JOIN t_user H ON F.update_user_id = H.user_id
  1015.     WHERE A.tenant_id = $2
  1016.     AND Z.ou_id = $7
  1017.     AND A.status = $3
  1018.     AND A.doc_date BETWEEN $4 AND $5;
  1019.     'USING pSessionId, pTenantId, vStatusVoid, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes;
  1020.                    
  1021.     Open pRefHeader FOR
  1022.     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,
  1023.            pDateStart AS date_trx, pDatetime AS datetime,
  1024.            pCashbankId AS cashbank_id,
  1025.            CASE WHEN pCashbankId = -99 THEN 'ALL' ELSE f_get_cashbank_code(pCashbankId)||' - '||f_get_cashbank_name(pCashbankId)END AS cashbank_code_name;
  1026.     RETURN NEXT pRefHeader;
  1027.  
  1028.     Open pRefDetail FOR
  1029.         SELECT TO_CHAR(TO_TIMESTAMP(A.doc_date,'YYYYMMDD'),'DD-MM-YYYY') AS doc_date,
  1030.                 A.doc_no,
  1031.                 A.ou_code AS ou_from_code,
  1032.                 A.ou_rc_code AS ou_to_code,
  1033.                 A.remark,
  1034.                 A.coa_acc, A.coa_desc,
  1035.                 A.activity_gl_code, A.activity_gl_name,
  1036.                 --A.remark_header,
  1037.                 CASE WHEN A.mode_payment = 'C' THEN 'Kas' ELSE 'Bank' END AS cashbank_type,
  1038.                 A.cashbank_code, A.cashbank_name, A.account_no,
  1039.                 A.bank_payment,
  1040.                 A.curr_code,
  1041.                 CASE WHEN (SUM(A.amount) > 0 ) THEN SUM(A.amount) ELSE 0 END AS debit,
  1042.                 CASE WHEN (SUM(A.amount) < 0 ) THEN -1 * SUM(A.amount) ELSE 0 END AS credit,
  1043.                 --CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
  1044.                 --CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
  1045.                 CASE WHEN A.transaction_type = vEmpty THEN A.doc_type_desc ELSE A.transaction_type END AS transaction_type,
  1046.                 A.create_user_name AS created_by,
  1047.                 to_char(to_timestamp(A.create_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS create_datetime,
  1048.                 A.update_user_name AS last_update_by,
  1049.                 to_char(to_timestamp(A.update_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS last_update_datetime
  1050.         FROM tt_detail_mutation_cashbank A
  1051.         WHERE A.session_id = pSessionId
  1052.         GROUP BY A.doc_date, A.doc_no, A.ou_code, A.ou_rc_code, A.remark, A.mode_payment, A.bank_payment,
  1053.         A.cashbank_code, A.cashbank_name, A.account_no, A.doc_type_desc, A.transaction_type,
  1054.         A.coa_acc, A.coa_desc, A.activity_gl_code, A.activity_gl_name,
  1055.         A.curr_code, A.create_user_name, A.create_datetime, A.update_user_name, A.update_datetime
  1056.         ORDER BY A.update_datetime ASC, A.doc_date, A.doc_no, A.activity_gl_code, A.ou_code, A.ou_rc_code;
  1057.     RETURN NEXT pRefDetail;
  1058.    
  1059.     DELETE FROM tt_detail_mutation_cashbank WHERE session_id = pSessionId;
  1060.    
  1061. END;
  1062. $BODY$
  1063.   LANGUAGE plpgsql VOLATILE
  1064.   COST 100
  1065.   /
RAW Paste Data