abirama62

query_full

Oct 26th, 2020 (edited)
655
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(1);
  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 := 'R';
  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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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.status_doc = $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, amount,
  434.             mode_payment, bank_payment, curr_code, remark,
  435.             create_user_id, create_user_name, create_datetime,
  436.             update_user_id, update_user_name, update_datetime,
  437.             remark_header, transaction_type, activity_gl_code, activity_gl_name)
  438.         SELECT $1, $2, 2,
  439.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  440.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  441.             COALESCE(F.ou_branch_id, F.ou_sub_bu_id, A.ou_id),
  442.             f_get_ou_code(COALESCE(F.ou_branch_id, F.ou_sub_bu_id, A.ou_id)), -1 * B.cost_amount,
  443.             G.flg_cash_bank, E.bank_payment, E.curr_code, B.remark,
  444.             A.create_user_id, H.username, A.create_datetime,
  445.             A.update_user_id, I.username, A.update_datetime,
  446.             A.remark, $6, K.activity_gl_code, K.activity_gl_name
  447.         FROM cb_advance_settle A
  448.         INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  449.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  450.         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
  451.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  452.         LEFT OUTER JOIN cb_advance_settle_cost_custom_for_sasa F ON B.advance_settle_cost_id = F.advance_settle_cost_id
  453.         INNER JOIN m_activity_gl K ON B.activity_gl_id = K.activity_gl_id
  454.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  455.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  456.         INNER JOIN t_user H ON A.create_user_id = H.user_id
  457.         INNER JOIN t_user I ON A.update_user_id = I.user_id
  458.         WHERE A.tenant_id = $2
  459.         AND Z.ou_id = $8 '||
  460.         vFilterCashbankV3||'
  461.         AND A.doc_date BETWEEN $4 AND $5
  462.         AND A.status_doc = $3
  463.         AND A.doc_type_id = $7; '
  464.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;
  465.  
  466.     -- EDC  Settlement -> nilai dokumen masuk
  467.     EXECUTE '
  468.         INSERT INTO tt_detail_mutation_cashbank(
  469.             session_id, tenant_id, order_type_data,
  470.             doc_type_id, doc_type_desc,
  471.             doc_no, doc_date, ou_id, ou_code,
  472.             ou_rc_id, ou_rc_code, amount,
  473.             mode_payment, bank_payment, curr_code, remark,
  474.             create_user_id, create_user_name, create_datetime,
  475.             update_user_id, update_user_name, update_datetime,
  476.             remark_header, transaction_type)
  477.         SELECT $1, $2, 2,  
  478.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  479.             A.doc_no, A.doc_date, Y.ou_id, f_get_ou_code(Y.ou_id),
  480.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  481.             H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  482.             A.create_user_id, I.username, A.create_datetime,
  483.             A.update_user_id, J.username, A.update_datetime,
  484.             A.remark, $7
  485.         FROM cb_in_out_cashbank A
  486.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  487.         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
  488.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  489.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  490.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  491.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  492.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  493.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  494.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  495.         WHERE A.tenant_id = $2
  496.         AND Z.ou_id = $9 '||
  497.         vFilterCashbankV2||'
  498.         AND A.doc_date BETWEEN $4 AND $5
  499.         AND A.status_doc = $3
  500.         AND A.doc_type_id = $8;
  501.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
  502.  
  503.     -- EDC  Settlement -> nilai cost
  504.     EXECUTE '
  505.         INSERT INTO tt_detail_mutation_cashbank(
  506.             session_id, tenant_id, order_type_data,
  507.             doc_type_id, doc_type_desc,
  508.             doc_no, doc_date, ou_id, ou_code,
  509.             ou_rc_id, ou_rc_code, amount,
  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.         SELECT $1, $2, 2,  
  515.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  516.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  517.             Y.ou_id, f_get_ou_code(Y.ou_id), -1 * C.cost_amount,
  518.             H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  519.             A.create_user_id, I.username, A.create_datetime,
  520.             A.update_user_id, J.username, A.update_datetime,
  521.             A.remark, $7, K.activity_gl_code, K.activity_gl_name
  522.         FROM cb_in_out_cashbank A
  523.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  524.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  525.         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
  526.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  527.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  528.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  529.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  530.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  531.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  532.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  533.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  534.         WHERE A.tenant_id = $2
  535.         AND Z.ou_id = $9 '||
  536.         vFilterCashbankV2||'
  537.         AND A.doc_date BETWEEN $4 AND $5
  538.         AND A.status_doc = $3
  539.         AND A.doc_type_id = $8
  540.         AND C.cost_amount <> 0;
  541.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
  542.  
  543.     -- Conversion CB In Other to Partner (nilai dokumen yang konversi)
  544.     EXECUTE '
  545.         INSERT INTO tt_detail_mutation_cashbank(
  546.             session_id, tenant_id, order_type_data,
  547.             doc_type_id, doc_type_desc,
  548.             doc_no, doc_date, ou_id, ou_code,
  549.             ou_rc_id, ou_rc_code, amount,
  550.             mode_payment, bank_payment, curr_code, remark,
  551.             create_user_id, create_user_name, create_datetime,
  552.             update_user_id, update_user_name, update_datetime,
  553.             remark_header, transaction_type)
  554.         -- nilai yang dikonversi
  555.         SELECT $1, $2, 2,
  556.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  557.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  558.             Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  559.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  560.             A.create_user_id, E.username, A.create_datetime,
  561.             A.update_user_id, F.username, A.update_datetime,
  562.             A.remark, $6
  563.         FROM cb_in_out_cashbank A
  564.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  565.         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
  566.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  567.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  568.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  569.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  570.         WHERE A.tenant_id = $2
  571.         AND Z.ou_id = $8 '||
  572.         vFilterCashbankV2||'
  573.         AND A.doc_date BETWEEN $4 AND $5
  574.         AND A.status_doc = $3
  575.         AND A.doc_type_id = $7
  576.  
  577.         UNION ALL
  578.         --nilai yang terkonversi
  579.         SELECT $1, $2, 2,
  580.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  581.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  582.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  583.             G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
  584.             A.create_user_id, E.username, A.create_datetime,
  585.             A.update_user_id, F.username, A.update_datetime,
  586.             A.remark, $6
  587.         FROM cb_in_out_cashbank A
  588.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  589.         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
  590.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  591.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  592.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  593.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  594.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  595.         WHERE A.tenant_id = $2
  596.         AND Z.ou_id = $8 '||
  597.         vFilterCashbankV2||'
  598.         AND A.doc_date BETWEEN $4 AND $5
  599.         AND A.status_doc = $3
  600.         AND A.doc_type_id = $7
  601.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
  602.  
  603.     -- Conversion CB In Other to Partner (jika ada cost)
  604.     EXECUTE '
  605.     INSERT INTO tt_detail_mutation_cashbank(
  606.             session_id, tenant_id, order_type_data,
  607.             doc_type_id, doc_type_desc,
  608.             doc_no, doc_date, ou_id, ou_code,
  609.             ou_rc_id, ou_rc_code, amount,
  610.             mode_payment, bank_payment, curr_code, remark,
  611.             create_user_id, create_user_name, create_datetime,
  612.             update_user_id, update_user_name, update_datetime,
  613.             remark_header, transaction_type, activity_gl_code, activity_gl_name)
  614.         -- nilai yang dikonversi
  615.         SELECT $1, $2, 2,
  616.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  617.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  618.             Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  619.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  620.             A.create_user_id, E.username, A.create_datetime,
  621.             A.update_user_id, F.username, A.update_datetime,
  622.             A.remark, $6, K.activity_gl_code, K.activity_gl_name
  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 X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
  627.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  628.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  629.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  630.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  631.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  632.         WHERE A.tenant_id = $2
  633.         AND Z.ou_id = $8 '||
  634.         vFilterCashbankV2||'
  635.         AND A.doc_date BETWEEN $4 AND $5
  636.         AND A.status_doc = $3
  637.         AND A.doc_type_id = $7
  638.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
  639.  
  640.  
  641.     -- Conversion CB In Partner to Other (nilai dokumen yang konversi)
  642.     EXECUTE '
  643.         INSERT INTO tt_detail_mutation_cashbank(
  644.             session_id, tenant_id, order_type_data,
  645.             doc_type_id, doc_type_desc,
  646.             doc_no, doc_date, ou_id, ou_code,
  647.             ou_rc_id, ou_rc_code, amount,
  648.             mode_payment, bank_payment, curr_code, remark,
  649.             create_user_id, create_user_name, create_datetime,
  650.             update_user_id, update_user_name, update_datetime,
  651.             remark_header, transaction_type, activity_gl_code, activity_gl_name)
  652.         -- nilai yang dikonversi
  653.         SELECT $1, $2, 2,
  654.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  655.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  656.             Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  657.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  658.             A.create_user_id, E.username, A.create_datetime,
  659.             A.update_user_id, F.username, A.update_datetime,
  660.             A.remark, $6, K.activity_gl_code, K.activity_gl_name
  661.         FROM cb_in_out_cashbank A
  662.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  663.         INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
  664.         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
  665.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  666.         INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
  667.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  668.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  669.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  670.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  671.         WHERE A.tenant_id = $2
  672.         AND Z.ou_id = $8 '||
  673.         vFilterCashbankV2||'
  674.         AND A.doc_date BETWEEN $4 AND $5
  675.         AND A.status_doc = $3
  676.         AND A.doc_type_id = $7
  677.  
  678.         UNION ALL
  679.         --nilai yang terkonversi
  680.         SELECT $1, $2, 2,
  681.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  682.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  683.             C.ou_id, f_get_ou_code(C.ou_id), B.cashbank_amount,
  684.             G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
  685.             A.create_user_id, E.username, A.create_datetime,
  686.             A.update_user_id, F.username, A.update_datetime,
  687.             A.remark, $6, K.activity_gl_code, K.activity_gl_name
  688.         FROM cb_in_out_cashbank A
  689.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  690.         INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
  691.         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
  692.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  693.         INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
  694.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  695.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  696.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  697.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  698.         WHERE A.tenant_id = $2
  699.         AND Z.ou_id = $8 '||
  700.         vFilterCashbankV2||'
  701.         AND A.doc_date BETWEEN $4 AND $5
  702.         AND A.status_doc = $3
  703.         AND A.doc_type_id = $7
  704.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInPO, pOuId;
  705.  
  706.     -- FUCAS -> nilai dokumen masuk (ref amount cb_in_out_cashbank < 0)
  707.     EXECUTE '
  708.         INSERT INTO tt_detail_mutation_cashbank(
  709.             session_id, tenant_id, order_type_data,
  710.             doc_type_id, doc_type_desc,
  711.             doc_no, doc_date, ou_id, ou_code,
  712.             ou_rc_id, ou_rc_code, amount,
  713.             mode_payment, bank_payment, curr_code, remark,
  714.             create_user_id, create_user_name, create_datetime,
  715.             update_user_id, update_user_name, update_datetime,
  716.             remark_header, transaction_type)
  717.         SELECT $1, $2, 2,  
  718.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  719.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  720.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  721.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  722.             A.create_user_id, E.username, A.create_datetime,
  723.             A.update_user_id, F.username, A.update_datetime,
  724.             A.remark, $6
  725.         FROM cb_in_out_cashbank A
  726.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  727.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  728.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  729.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  730.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  731.         WHERE A.tenant_id = $2
  732.         AND Z.ou_id = $9 '||
  733.         vFilterCashbankV2||'
  734.         AND A.doc_date BETWEEN $4 AND $5
  735.         AND A.status_doc = $3
  736.         AND A.doc_type_id = $8
  737.         AND A.ref_amount < 0;
  738.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;
  739.    
  740.     -- FUCAS -> nilai dokumen keluar (ref amount cb_in_out_cashbank > 0)
  741.         EXECUTE '
  742.         INSERT INTO tt_detail_mutation_cashbank(
  743.             session_id, tenant_id, order_type_data,
  744.             doc_type_id, doc_type_desc,
  745.             doc_no, doc_date, ou_id, ou_code,
  746.             ou_rc_id, ou_rc_code, amount,
  747.             mode_payment, bank_payment, curr_code, remark,
  748.             create_user_id, create_user_name, create_datetime,
  749.             update_user_id, update_user_name, update_datetime,
  750.             remark_header, transaction_type)
  751.         SELECT $1, $2, 2,  
  752.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  753.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  754.             A.ou_id, f_get_ou_code(A.ou_id), -1 * B.cashbank_amount,
  755.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  756.             A.create_user_id, E.username, A.create_datetime,
  757.             A.update_user_id, F.username, A.update_datetime,
  758.             A.remark, $6
  759.         FROM cb_in_out_cashbank A
  760.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  761.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  762.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  763.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  764.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  765.         WHERE A.tenant_id = $2
  766.         AND Z.ou_id = $9 '||
  767.         vFilterCashbankV2||'
  768.         AND A.doc_date BETWEEN $4 AND $5
  769.         AND A.status_doc = $3
  770.         AND A.doc_type_id = $8
  771.         AND A.ref_amount > 0;
  772.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;
  773.    
  774.     -- Cheque Giro Realization -> nilai dokumen masuk
  775.     EXECUTE '
  776.         INSERT INTO tt_detail_mutation_cashbank(
  777.             session_id, tenant_id, order_type_data,
  778.             doc_type_id, doc_type_desc,
  779.             doc_no, doc_date, ou_id, ou_code,
  780.             ou_rc_id, ou_rc_code, amount,
  781.             mode_payment, bank_payment, curr_code, remark,
  782.             create_user_id, create_user_name, create_datetime,
  783.             update_user_id, update_user_name, update_datetime,
  784.             remark_header, transaction_type)
  785.         SELECT $1, $2, 2,      
  786.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  787.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  788.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
  789.             G.flg_cash_bank, B.bank_payment, B.curr_code, B.remark,
  790.             A.create_user_id, E.username, A.create_datetime,
  791.             A.update_user_id, F.username, A.update_datetime,
  792.             A.remark, $6
  793.         FROM cb_in_out_cashbank A
  794.         INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  795.         INNER JOIN m_partner_ou C ON B.partner_id = C.partner_id
  796.         INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
  797.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  798.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  799.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  800.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  801.         WHERE A.tenant_id = $2
  802.         AND Z.ou_id = $8 '||
  803.         vFilterCashbankV2||'
  804.         AND A.doc_date BETWEEN $4 AND $5
  805.         AND A.status_doc = $3
  806.         AND A.doc_type_id = $7
  807.         AND B.realization_status = $9;
  808.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;
  809.  
  810.     -- Cheque Giro Realization -> nilai cost
  811.     EXECUTE '
  812.         WITH tt_in_out_cashbank AS(
  813.             SELECT A.in_out_cashbank_id, B.cashbank_id
  814.             FROM cb_in_out_cashbank A
  815.             INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  816.             WHERE B.realization_status = $10 '||
  817.             vFilterCashbankV2||'
  818.             GROUP BY A.in_out_cashbank_id, B.cashbank_id
  819.         )
  820.         INSERT INTO tt_detail_mutation_cashbank(
  821.             session_id, tenant_id, order_type_data,
  822.             doc_type_id, doc_type_desc,
  823.             doc_no, doc_date, ou_id, ou_code,
  824.             ou_rc_id, ou_rc_code, amount,
  825.             mode_payment, bank_payment, curr_code, remark,
  826.             create_user_id, create_user_name, create_datetime,
  827.             update_user_id, update_user_name, update_datetime,
  828.             remark_header, transaction_type, activity_gl_code, activity_gl_name)
  829.         SELECT $1, $2, 2,  
  830.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  831.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  832.             COALESCE(C.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(C.ou_rc_id, A.ou_id)), -1 * C.cost_amount,
  833.             G.flg_cash_bank, $7, C.curr_code, C.remark,
  834.             A.create_user_id, E.username, A.create_datetime,
  835.             A.update_user_id, F.username, A.update_datetime,
  836.             A.remark, $7, K.activity_gl_code, K.activity_gl_name
  837.         FROM cb_in_out_cashbank A
  838.         INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  839.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  840.         INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
  841.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  842.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  843.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  844.         INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
  845.         WHERE A.tenant_id = $2
  846.         AND Z.ou_id = $9
  847.         AND A.doc_date BETWEEN $4 AND $5
  848.         AND A.status_doc = $3
  849.         AND A.doc_type_id = $8
  850.         AND C.cost_amount <> 0;
  851.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCGRealization, pOuId, vStatusAccept;
  852.    
  853.     -- POS
  854.     EXECUTE '
  855.     INSERT INTO tt_detail_mutation_cashbank(
  856.         session_id, tenant_id, order_type_data,
  857.         doc_type_id, doc_type_desc,
  858.         doc_no, doc_date, ou_id, ou_code,
  859.         ou_rc_id, ou_rc_code,
  860.         amount,
  861.         mode_payment, bank_payment, curr_code, remark,
  862.         create_user_id, create_user_name, create_datetime,
  863.         update_user_id, update_user_name, update_datetime,
  864.         remark_header, transaction_type)
  865.     SELECT $1, $2, 2,
  866.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  867.         A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  868.         A.ou_id, f_get_ou_code(A.ou_id),
  869.         CASE WHEN E.flg_termin = $9 THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END AS amount,
  870.         D.flg_cash_bank, $6, A.curr_code, A.remark,
  871.         A.create_user_id, F.username, A.create_datetime,
  872.         A.update_user_id, G.username, A.update_datetime,
  873.         A.remark, $6
  874.     FROM i_trx_pos A
  875.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  876.         AND A.process_no = B.process_no
  877.         AND A.trx_pos_id = B.trx_pos_id
  878.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  879.         AND A.process_no = E.process_no
  880.         AND A.trx_pos_id = E.trx_pos_id
  881.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  882.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  883.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  884.     INNER JOIN t_user F ON A.create_user_id = F.user_id
  885.     INNER JOIN t_user G ON A.update_user_id = G.user_id
  886.     WHERE A.tenant_id = $2
  887.     AND Z.ou_id = $7
  888.     AND A.status IN ($3, $10)
  889.     AND A.doc_date BETWEEN $4 AND $5;
  890.     'USING pSessionId, pTenantId, vStatusSubmit, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes, vStatusVoid;
  891.  
  892.     --POS Void
  893.     EXECUTE '
  894.     INSERT INTO tt_detail_mutation_cashbank(
  895.         session_id, tenant_id, order_type_data,
  896.         doc_type_id, doc_type_desc,
  897.         doc_no, doc_date, ou_id, ou_code,
  898.         ou_rc_id, ou_rc_code,
  899.         amount,
  900.         mode_payment, bank_payment, curr_code, remark,
  901.         create_user_id, create_user_name, create_datetime,
  902.         update_user_id, update_user_name, update_datetime,
  903.         remark_header, transaction_type)
  904.     SELECT $1, $2, 2,
  905.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  906.         A.doc_no||''_VOID'', F.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  907.         A.ou_id, f_get_ou_code(A.ou_id),
  908.         CASE WHEN E.flg_termin = $9 THEN -1 * B.payment_amount ELSE -1 * (B.payment_amount- A.total_refund) END AS amount,
  909.         D.flg_cash_bank, $6, A.curr_code, F.remark,
  910.         F.create_user_id, G.username, F.create_datetime,
  911.         F.update_user_id, H.username, F.update_datetime,
  912.         A.remark, $6
  913.     FROM i_trx_pos A
  914.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  915.         AND A.process_no = B.process_no
  916.         AND A.trx_pos_id = B.trx_pos_id
  917.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  918.         AND A.process_no = E.process_no
  919.         AND A.trx_pos_id = E.trx_pos_id
  920.     INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
  921.         AND A.process_no = F.process_no
  922.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  923.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  924.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  925.     INNER JOIN t_user G ON F.create_user_id = G.user_id
  926.     INNER JOIN t_user H ON F.update_user_id = H.user_id
  927.     WHERE A.tenant_id = $2
  928.     AND Z.ou_id = $7
  929.     AND A.status = $3
  930.     AND A.doc_date BETWEEN $4 AND $5;
  931.     'USING pSessionId, pTenantId, vStatusVoid, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes;
  932.                    
  933.     Open pRefHeader FOR
  934.     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,
  935.            pDateStart AS date_trx, pDatetime AS datetime,
  936.            pCashbankId AS cashbank_id,
  937.            CASE WHEN pCashbankId = -99 THEN 'ALL' ELSE f_get_cashbank_code(pCashbankId)||' - '||f_get_cashbank_name(pCashbankId)END AS cashbank_code_name;
  938.     RETURN NEXT pRefHeader;
  939.  
  940.     Open pRefDetail FOR
  941.         SELECT TO_CHAR(TO_TIMESTAMP(A.doc_date,'YYYYMMDD'),'DD-MM-YYYY') AS doc_date,
  942.                 A.doc_no,
  943.                 A.ou_code AS ou_from_code,
  944.                 A.ou_rc_code AS ou_to_code,
  945.                 A.remark,
  946.                 A.activity_gl_code, A.activity_gl_name,
  947.                 --A.remark_header,
  948.                 CASE WHEN A.mode_payment = 'C' THEN 'Kas' ELSE 'Bank' END AS cashbank_type,
  949.                 A.bank_payment,
  950.                 A.curr_code,
  951.                 CASE WHEN (SUM(A.amount) > 0 ) THEN SUM(A.amount) ELSE 0 END AS debit,
  952.                 CASE WHEN (SUM(A.amount) < 0 ) THEN -1 * SUM(A.amount) ELSE 0 END AS credit,
  953.                 --CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
  954.                 --CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
  955.                 CASE WHEN A.transaction_type = vEmpty THEN A.doc_type_desc ELSE A.transaction_type END AS transaction_type,
  956.                 A.create_user_name AS created_by,
  957.                 to_char(to_timestamp(A.create_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS create_datetime,
  958.                 A.update_user_name AS last_update_by,
  959.                 to_char(to_timestamp(A.update_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS last_update_datetime
  960.         FROM tt_detail_mutation_cashbank A
  961.         WHERE A.session_id = pSessionId
  962.         GROUP BY A.doc_date, A.doc_no, A.ou_code, A.ou_rc_code, A.remark, A.mode_payment, A.bank_payment,
  963.         A.activity_gl_code, A.activity_gl_name, A.doc_type_desc, A.transaction_type,
  964.         A.curr_code, A.create_user_name, A.create_datetime, A.update_user_name, A.update_datetime
  965.         ORDER BY A.update_datetime, A.doc_date, A.doc_no, A.ou_code, A.activity_gl_code ASC;
  966.     RETURN NEXT pRefDetail;
  967.    
  968.     DELETE FROM tt_detail_mutation_cashbank WHERE session_id = pSessionId;
  969.    
  970. END;
  971. $BODY$
  972.   LANGUAGE plpgsql VOLATILE
  973.   COST 100
  974.   /
RAW Paste Data