abirama62

query4

Oct 26th, 2020 (edited)
922
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- FUCAS -> nilai dokumen masuk (ref amount cb_in_out_cashbank < 0)
  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.         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.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  16.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  17.             A.create_user_id, E.username, A.create_datetime,
  18.             A.update_user_id, F.username, A.update_datetime,
  19.             A.remark, $6
  20.         FROM cb_in_out_cashbank A
  21.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  22.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  23.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  24.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  25.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  26.         WHERE A.tenant_id = $2
  27.         AND Z.ou_id = $9 '||
  28.         vFilterCashbankV2||'
  29.         AND A.doc_date BETWEEN $4 AND $5
  30.         AND A.status_doc = $3
  31.         AND A.doc_type_id = $8
  32.         AND A.ref_amount < 0;
  33.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;
  34.    
  35. -- FUCAS -> nilai dokumen keluar (ref amount cb_in_out_cashbank > 0)
  36.         EXECUTE '
  37.         INSERT INTO tt_detail_mutation_cashbank(
  38.             session_id, tenant_id, order_type_data,
  39.             doc_type_id, doc_type_desc,
  40.             doc_no, doc_date, ou_id, ou_code,
  41.             ou_rc_id, ou_rc_code, amount,
  42.             mode_payment, bank_payment, curr_code, remark,
  43.             create_user_id, create_user_name, create_datetime,
  44.             update_user_id, update_user_name, update_datetime,
  45.             remark_header, transaction_type)
  46.         SELECT $1, $2, 2,  
  47.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  48.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  49.             Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
  50.             G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  51.             A.create_user_id, E.username, A.create_datetime,
  52.             A.update_user_id, F.username, A.update_datetime,
  53.             A.remark, $6
  54.         FROM cb_in_out_cashbank A
  55.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  56.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  57.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  58.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  59.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  60.         WHERE A.tenant_id = $2
  61.         AND Z.ou_id = $9 '||
  62.         vFilterCashbankV2||'
  63.         AND A.doc_date BETWEEN $4 AND $5
  64.         AND A.status_doc = $3
  65.         AND A.doc_type_id = $8
  66.         AND A.ref_amount > 0;
  67.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;
  68.  
  69.  
  70. -- POS
  71.     EXECUTE '
  72.     INSERT INTO tt_detail_mutation_cashbank(
  73.         session_id, tenant_id, order_type_data,
  74.         doc_type_id, doc_type_desc,
  75.         doc_no, doc_date, ou_id, ou_code,
  76.         ou_rc_id, ou_rc_code,
  77.         amount,
  78.         mode_payment, bank_payment, curr_code, remark,
  79.         create_user_id, create_user_name, create_datetime,
  80.         update_user_id, update_user_name, update_datetime,
  81.         remark_header, transaction_type)
  82.     SELECT $1, $2, 2,
  83.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  84.         A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  85.         A.ou_id, f_get_ou_code(A.ou_id),
  86.         CASE WHEN E.flg_termin = $9 THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END AS amount,
  87.         D.flg_cash_bank, $6, A.curr_code, A.remark,
  88.         A.create_user_id, F.username, A.create_datetime,
  89.         A.update_user_id, G.username, A.update_datetime,
  90.         A.remark, $6
  91.     FROM i_trx_pos A
  92.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  93.         AND A.process_no = B.process_no
  94.         AND A.trx_pos_id = B.trx_pos_id
  95.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  96.         AND A.process_no = E.process_no
  97.         AND A.trx_pos_id = E.trx_pos_id
  98.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  99.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  100.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  101.     INNER JOIN t_user F ON A.create_user_id = F.user_id
  102.     INNER JOIN t_user G ON A.update_user_id = G.user_id
  103.     WHERE A.tenant_id = $2
  104.     AND Z.ou_id = $7
  105.     AND A.status IN ($3, $10)
  106.     AND A.doc_date BETWEEN $4 AND $5;
  107.     'USING pSessionId, pTenantId, vStatusSubmit, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes, vStatusVoid;
  108.  
  109. --POS Void
  110.     EXECUTE '
  111.     INSERT INTO tt_detail_mutation_cashbank(
  112.         session_id, tenant_id, order_type_data,
  113.         doc_type_id, doc_type_desc,
  114.         doc_no, doc_date, ou_id, ou_code,
  115.         ou_rc_id, ou_rc_code,
  116.         amount,
  117.         mode_payment, bank_payment, curr_code, remark,
  118.         create_user_id, create_user_name, create_datetime,
  119.         update_user_id, update_user_name, update_datetime,
  120.         remark_header, transaction_type)
  121.     SELECT $1, $2, 2,
  122.         A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  123.         A.doc_no||''_VOID'', F.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  124.         A.ou_id, f_get_ou_code(A.ou_id),
  125.         CASE WHEN E.flg_termin = $9 THEN -1 * B.payment_amount ELSE -1 * (B.payment_amount- A.total_refund) END AS amount,
  126.         D.flg_cash_bank, $6, A.curr_code, F.remark,
  127.         F.create_user_id, G.username, F.create_datetime,
  128.         F.update_user_id, H.username, F.update_datetime,
  129.         A.remark, $6
  130.     FROM i_trx_pos A
  131.     INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
  132.         AND A.process_no = B.process_no
  133.         AND A.trx_pos_id = B.trx_pos_id
  134.     INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
  135.         AND A.process_no = E.process_no
  136.         AND A.trx_pos_id = E.trx_pos_id
  137.     INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
  138.         AND A.process_no = F.process_no
  139.     INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
  140.     INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
  141.     INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
  142.     INNER JOIN t_user G ON F.create_user_id = G.user_id
  143.     INNER JOIN t_user H ON F.update_user_id = H.user_id
  144.     WHERE A.tenant_id = $2
  145.     AND Z.ou_id = $7
  146.     AND A.status = $3
  147.     AND A.doc_date BETWEEN $4 AND $5;
  148.     'USING pSessionId, pTenantId, vStatusVoid, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes;
RAW Paste Data