abirama62

query_full_fix_final

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