abirama62

query

Oct 23rd, 2020 (edited)
989
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- CB TRANSFER YG CB OU dari assign ou sesuai filter (Untuk OU yang Transfer)
  2.     EXECUTE '
  3.         -- CB Transfer -> nilai dokumen keluar
  4.         INSERT INTO tt_detail_mutation_cashbank(
  5.             session_id, tenant_id, order_type_data,
  6.             doc_type_id, doc_type_desc,
  7.             doc_no, doc_date, ou_id, ou_code,
  8.             ou_rc_id, ou_rc_code, amount,
  9.             mode_payment, bank_payment, curr_code, remark,
  10.             create_user_id, create_user_name, create_datetime,
  11.             update_user_id, update_user_name, update_datetime)
  12.         SELECT  $1, $2, 2,
  13.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  14.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  15.             B.ou_to_id, f_get_ou_code(B.ou_to_id), -1 * (A.transfer_amount),
  16.             G.flg_cash_bank, A.bank_payment, A.curr_code, A.remark,
  17.             A.create_user_id, D.username, A.create_datetime,
  18.             A.update_user_id, E.username, A.update_datetime
  19.         FROM cb_transfer_cashbank A
  20.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  21.         INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
  22.         INNER JOIN t_user D ON A.create_user_id = D.user_id
  23.         INNER JOIN t_user E ON A.update_user_id = E.user_id
  24.         INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  25.         WHERE A.tenant_id = $2
  26.         AND C.ou_id = $6 '||
  27.         vFilterCashbankV1   ||'
  28.         AND A.status_doc = $3
  29.         AND A.doc_date BETWEEN $4 AND $5
  30.  
  31.         UNION ALL
  32.  
  33.         -- CB Transfer -> nilai cost
  34.         SELECT $1, $2, 2,
  35.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  36.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  37.             B.ou_to_id, f_get_ou_code(B.ou_to_id), -1 * (COALESCE(C.payment_amount, 0)),
  38.             G.flg_cash_bank, A.bank_payment, A.curr_code, A.remark,
  39.             A.create_user_id, E.username, A.create_datetime,
  40.             A.update_user_id, F.username, A.update_datetime
  41.         FROM cb_transfer_cashbank A
  42.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  43.         INNER JOIN cb_transfer_cashbank_cost C ON A.transfer_cashbank_id = C.transfer_cashbank_id
  44.         INNER JOIN m_cashbank_assignment_ou D ON A.cashbank_id = D.cashbank_id
  45.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  46.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  47.         INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
  48.         WHERE A.tenant_id = $2
  49.         AND D.ou_id = $6 '||
  50.         vFilterCashbankV1   ||'
  51.         AND A.status_doc = $3
  52.         AND A.doc_date BETWEEN $4 AND $5
  53.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, pOuId, vEmpty;
  54.  
  55. -- CB TRANSFER YG CB OU dari assign ou sesuai filter (Untuk OU yang Penerima)
  56.     EXECUTE '
  57.         INSERT INTO tt_detail_mutation_cashbank(
  58.             session_id, tenant_id, order_type_data,
  59.             doc_type_id, doc_type_desc,
  60.             doc_no, doc_date, ou_id, ou_code,
  61.             ou_rc_id, ou_rc_code, amount,
  62.             mode_payment, bank_payment, curr_code, remark,
  63.             create_user_id, create_user_name, create_datetime,
  64.             update_user_id, update_user_name, update_datetime)
  65.         SELECT $1, $2, 2,
  66.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  67.             A.doc_no, A.doc_date, A.ou_id AS ou_id_from, f_get_ou_code(A.ou_id) AS ou_from,
  68.             B.ou_to_id AS ou_id_to, f_get_ou_code(B.ou_to_id) AS ou_to, B.receive_amount,
  69.             G.flg_cash_bank, B.bank_payment, A.curr_code, B.remark,
  70.             A.create_user_id, E.username, A.create_datetime,
  71.             A.update_user_id, F.username, A.update_datetime
  72.         FROM cb_transfer_cashbank A
  73.         INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
  74.         INNER JOIN m_cashbank_assignment_ou E ON B.cashbank_to_id = E.cashbank_id
  75.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  76.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  77.         INNER JOIN m_cashbank G ON B.cashbank_to_id = G.cashbank_id
  78.         WHERE A.tenant_id = $2
  79.         AND E.ou_id = $7 '||
  80.         vFilterCashbankV1   ||'
  81.         AND A.status_doc = $3
  82.         AND A.doc_date BETWEEN $4 AND $5
  83.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, pOuId, vEmpty;
  84.  
  85. -- CB Out dari Payment Order (Non AP)
  86.     EXECUTE '
  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.         SELECT $1, $2, 2,
  96.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  97.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  98.             A.ou_id, f_get_ou_code(A.ou_id), -1 * B.cashbank_amount,
  99.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  100.             A.create_user_id, E.username, A.create_datetime,
  101.             A.update_user_id, F.username, A.update_datetime
  102.         FROM cb_in_out_cashbank A
  103.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  104.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  105.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  106.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  107.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  108.         WHERE A.tenant_id = $2
  109.         AND Z.ou_id = $7 ' ||
  110.         vFilterCashbankV2||'
  111.         AND A.doc_date BETWEEN $4 AND $5
  112.         AND A.status_doc = $3
  113.         AND A.doc_type_id = $6
  114.         AND A.ref_doc_type_id = $8; '
  115.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
  116.     vDocTypePaymentOrderNonAP, vEmpty;
  117.  
  118. -- CB Out dari Req Cash Advance
  119.     EXECUTE '
  120.         INSERT INTO tt_detail_mutation_cashbank(
  121.             session_id, tenant_id, order_type_data,
  122.             doc_type_id, doc_type_desc,
  123.             doc_no, doc_date, ou_id, ou_code,
  124.             ou_rc_id, ou_rc_code, amount,
  125.             mode_payment, bank_payment, curr_code, remark,
  126.             create_user_id, create_user_name, create_datetime,
  127.             update_user_id, update_user_name, update_datetime)
  128.         SELECT $1, $2, 2,
  129.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  130.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  131.             A.ou_id, f_get_ou_code(A.ou_id), -1 * B.cashbank_amount,
  132.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  133.             A.create_user_id, E.username, A.create_datetime,
  134.             A.update_user_id, F.username, A.update_datetime
  135.         FROM cb_in_out_cashbank A
  136.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  137.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  138.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  139.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  140.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  141.         WHERE A.tenant_id = $2
  142.         AND Z.ou_id = $7 ' ||
  143.         vFilterCashbankV2||'
  144.         AND A.doc_date BETWEEN $4 AND $5
  145.         AND A.status_doc = $3
  146.         AND A.doc_type_id = $6
  147.         AND A.ref_doc_type_id = $8; '
  148.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
  149.     vDocTypeRequestCashAdvance, vEmpty;
  150.  
  151. -- CB IN Partner Receive -> nilai dokumen masuk
  152.     EXECUTE '
  153.         INSERT INTO tt_detail_mutation_cashbank(
  154.             session_id, tenant_id, order_type_data,
  155.             doc_type_id, doc_type_desc,
  156.             doc_no, doc_date, ou_id, ou_code,
  157.             ou_rc_id, ou_rc_code, amount,
  158.             mode_payment, bank_payment, curr_code, remark,
  159.             create_user_id, create_user_name, create_datetime,
  160.             update_user_id, update_user_name, update_datetime)
  161.         SELECT $1, $2, 2,  
  162.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  163.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  164.             A.ou_id, f_get_ou_code(A.ou_id), B.cashbank_amount,
  165.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  166.             A.create_user_id, E.username, A.create_datetime,
  167.             A.update_user_id, F.username, A.update_datetime
  168.         FROM cb_in_out_cashbank A
  169.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  170.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  171.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  172.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  173.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  174.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  175.         WHERE A.tenant_id = $2
  176.         AND Z.ou_id = $9 '||
  177.         vFilterCashbankV2||'
  178.         AND A.doc_date BETWEEN $4 AND $5
  179.         AND A.status_doc = $3
  180.         AND A.doc_type_id = $8;
  181.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCashBankInAr, pOuId;
  182.    
  183. -- CB IN Partner Receive -> jika ada nilai cost
  184.     EXECUTE '
  185.         INSERT INTO tt_detail_mutation_cashbank(
  186.             session_id, tenant_id, order_type_data,
  187.             doc_type_id, doc_type_desc,
  188.             doc_no, doc_date, ou_id, ou_code,
  189.             ou_rc_id, ou_rc_code, amount,
  190.             mode_payment, bank_payment, curr_code, remark,
  191.             create_user_id, create_user_name, create_datetime,
  192.             update_user_id, update_user_name, update_datetime)
  193.         SELECT $1, $2, 2,  
  194.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  195.             A.doc_no, A.doc_date, D.ou_id, f_get_ou_code(D.ou_id),
  196.             A.ou_id, f_get_ou_code(A.ou_id), -1 * C.cost_amount,
  197.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  198.             A.create_user_id, E.username, A.create_datetime,
  199.             A.update_user_id, F.username, A.update_datetime
  200.         FROM cb_in_out_cashbank A
  201.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  202.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  203.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  204.         INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
  205.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  206.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  207.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  208.         WHERE A.tenant_id = $2
  209.         AND Z.ou_id = $8 '||
  210.         vFilterCashbankV2||'
  211.         AND A.doc_date BETWEEN $4 AND $5
  212.         AND A.status_doc = $3
  213.         AND A.doc_type_id = $7
  214.         AND C.cost_amount <> 0;
  215.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInAr, pOuId;
  216.    
  217. -- CB IN Other -> nilai dokumen (per activity gl terima)
  218.     EXECUTE '
  219.         INSERT INTO tt_detail_mutation_cashbank(
  220.             session_id, tenant_id, order_type_data,
  221.             doc_type_id, doc_type_desc,
  222.             doc_no, doc_date, ou_id, ou_code,
  223.             ou_rc_id, ou_rc_code, amount,
  224.             mode_payment, bank_payment, curr_code, remark,
  225.             create_user_id, create_user_name, create_datetime,
  226.             update_user_id, update_user_name, update_datetime)
  227.         SELECT $1, $2, 2,  
  228.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  229.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  230.             A.ou_id, f_get_ou_code(A.ou_id), C.cost_amount,
  231.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  232.             A.create_user_id, E.username, A.create_datetime,
  233.             A.update_user_id, F.username, A.update_datetime
  234.         FROM cb_in_out_cashbank A
  235.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  236.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  237.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  238.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  239.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  240.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  241.         WHERE A.tenant_id = $2
  242.         AND Z.ou_id = $8 '||
  243.         vFilterCashbankV2||'
  244.         AND A.doc_date BETWEEN $4 AND $5
  245.         AND A.status_doc = $3
  246.         AND A.doc_type_id = $7;
  247.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInOther, pOuId;
RAW Paste Data