abirama62

query3

Oct 24th, 2020
701
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Conversion CB In Other to Partner (nilai dokumen yang konversi)
  2.     EXECUTE '
  3.         INSERT INTO tt_detail_mutation_cashbank(
  4.             session_id, tenant_id, order_type_data,
  5.             doc_type_id, doc_type_desc,
  6.             doc_no, doc_date, ou_id, ou_code,
  7.             ou_rc_id, ou_rc_code, amount,
  8.             mode_payment, bank_payment, curr_code, remark,
  9.             create_user_id, create_user_name, create_datetime,
  10.             update_user_id, update_user_name, update_datetime,
  11.             remark_header, transaction_type)
  12.         -- nilai yang dikonversi
  13.         SELECT $1, $2, 2,
  14.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  15.             A.doc_no, A.doc_date, X.ou_id, f_get_ou_code(X.ou_id),
  16.             X.ou_id, f_get_ou_code(X.ou_id), -1 * B.cashbank_amount,
  17.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  18.             A.create_user_id, E.username, A.create_datetime,
  19.             A.update_user_id, F.username, A.update_datetime,
  20.             A.remark, ''
  21.         FROM cb_in_out_cashbank A
  22.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  23.         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
  24.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  25.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  26.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  27.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  28.         WHERE A.tenant_id = $2
  29.         AND Z.ou_id = $8 '||
  30.         vFilterCashbankV2||'
  31.         AND A.doc_date BETWEEN $4 AND $5
  32.         AND A.status_doc = $3
  33.         AND A.doc_type_id = $7
  34.  
  35.         UNION ALL
  36.         --nilai yang terkonversi
  37.         SELECT $1, $2, 2,
  38.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  39.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  40.             X.ou_id, f_get_ou_code(X.ou_id), B.cashbank_amount,
  41.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  42.             A.create_user_id, E.username, A.create_datetime,
  43.             A.update_user_id, F.username, A.update_datetime,
  44.             A.remark, $6
  45.         FROM cb_in_out_cashbank A
  46.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  47.         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
  48.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  49.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  50.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  51.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  52.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  53.         WHERE A.tenant_id = $2
  54.         AND Z.ou_id = $8 '||
  55.         vFilterCashbankV2||'
  56.         AND A.doc_date BETWEEN $4 AND $5
  57.         AND A.status_doc = $3
  58.         AND A.doc_type_id = $7
  59.  
  60.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
  61.  
  62. -- Conversion CB In Partner to Other (nilai dokumen yang konversi)
  63.     EXECUTE '
  64.         INSERT INTO tt_detail_mutation_cashbank(
  65.             session_id, tenant_id, order_type_data,
  66.             doc_type_id, doc_type_desc,
  67.             doc_no, doc_date, ou_id, ou_code,
  68.             ou_rc_id, ou_rc_code, amount,
  69.             mode_payment, bank_payment, curr_code, remark,
  70.             create_user_id, create_user_name, create_datetime,
  71.             update_user_id, update_user_name, update_datetime,
  72.             remark_header, transaction_type)
  73.         -- nilai yang dikonversi
  74.         SELECT $1, $2, 2,
  75.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  76.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  77.             X.ou_id, f_get_ou_code(X.ou_id), -1 * B.cashbank_amount,
  78.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  79.             A.create_user_id, E.username, A.create_datetime,
  80.             A.update_user_id, F.username, A.update_datetime,
  81.             A.remark, $6
  82.         FROM cb_in_out_cashbank A
  83.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  84.         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
  85.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  86.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  87.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  88.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  89.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  90.         WHERE A.tenant_id = $2
  91.         AND Z.ou_id = $8 '||
  92.         vFilterCashbankV2||'
  93.         AND A.doc_date BETWEEN $4 AND $5
  94.         AND A.status_doc = $3
  95.         AND A.doc_type_id = $7
  96.  
  97.         UNION ALL
  98.         --nilai yang terkonversi
  99.         SELECT $1, $2, 2,
  100.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  101.             A.doc_no, A.doc_date, X.ou_id, f_get_ou_code(X.ou_id),
  102.             C.ou_id, f_get_ou_code(C.ou_id), B.cashbank_amount,
  103.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  104.             A.create_user_id, E.username, A.create_datetime,
  105.             A.update_user_id, F.username, A.update_datetime,
  106.             A.remark, $6
  107.         FROM cb_in_out_cashbank A
  108.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  109.         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
  110.         INNER JOIN m_partner_ou C ON A.partner_id = C.partner_id
  111.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  112.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  113.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  114.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  115.         WHERE A.tenant_id = $2
  116.         AND Z.ou_id = $8 '||
  117.         vFilterCashbankV2||'
  118.         AND A.doc_date BETWEEN $4 AND $5
  119.         AND A.status_doc = $3
  120.         AND A.doc_type_id = $7
  121.  
  122.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInPO, pOuId;
RAW Paste Data