abirama62

query2

Oct 23rd, 2020 (edited)
858
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Cash Advance Settlement (pengembalian BS)
  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.         SELECT $1, $2, 2,
  12.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  13.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  14.             D.ou_id, f_get_ou_code(D.ou_id), E.cashbank_amount,
  15.             G.flg_cash_bank, E.bank_payment, E.curr_code, A.remark,
  16.             A.create_user_id, H.username, A.create_datetime,
  17.             A.update_user_id, I.username, A.update_datetime
  18.         FROM cb_advance_settle A
  19.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  20.         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
  21.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  22.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  23.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  24.         INNER JOIN t_user H ON A.create_user_id = H.user_id
  25.         INNER JOIN t_user I ON A.update_user_id = I.user_id
  26.         WHERE A.tenant_id = $2
  27.         AND Z.ou_id = $8 '||
  28.         vFilterCashbankV2||'
  29.         AND A.doc_date BETWEEN $4 AND $5
  30.         AND A.status_doc = $3
  31.         AND A.doc_type_id = $7; '
  32.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;
  33.  
  34. -- Cash Advance Settlement (cost activity gl)
  35.     EXECUTE '
  36.         INSERT INTO tt_detail_mutation_cashbank(
  37.             session_id, tenant_id, order_type_data,
  38.             doc_type_id, doc_type_desc,
  39.             doc_no, doc_date, ou_id, ou_code,
  40.             ou_rc_id, ou_rc_code, amount,
  41.             mode_payment, bank_payment, curr_code, remark,
  42.             create_user_id, create_user_name, create_datetime,
  43.             update_user_id, update_user_name, update_datetime)
  44.         SELECT $1, $2, 2,
  45.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  46.             A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
  47.             COALESCE(B.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(B.ou_rc_id, A.ou_id)), -1 * B.cost_amount,
  48.             G.flg_cash_bank, E.bank_payment, E.curr_code, B.remark,
  49.             A.create_user_id, H.username, A.create_datetime,
  50.             A.update_user_id, I.username, A.update_datetime
  51.         FROM cb_advance_settle A
  52.         INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  53.         INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
  54.         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
  55.         INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
  56.         INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
  57.         INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
  58.         INNER JOIN t_user H ON A.create_user_id = H.user_id
  59.         INNER JOIN t_user I ON A.update_user_id = I.user_id
  60.         WHERE A.tenant_id = $2
  61.         AND Z.ou_id = $8 '||
  62.         vFilterCashbankV2||'
  63.         AND A.doc_date BETWEEN $4 AND $5
  64.         AND A.status_doc = $3
  65.         AND A.doc_type_id = $7; '
  66.     USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;
  67.  
  68. -- Cheque Giro Realization -> nilai dokumen masuk
  69.     EXECUTE '
  70.         INSERT INTO tt_detail_mutation_cashbank(
  71.             session_id, tenant_id, order_type_data,
  72.             doc_type_id, doc_type_desc,
  73.             doc_no, doc_date, ou_id, ou_code,
  74.             ou_rc_id, ou_rc_code, amount,
  75.             mode_payment, bank_payment, curr_code, remark,
  76.             create_user_id, create_user_name, create_datetime,
  77.             update_user_id, update_user_name, update_datetime)
  78.         SELECT $1, $2, 2,      
  79.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  80.             A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
  81.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
  82.             G.flg_cash_bank, B.bank_payment, B.curr_code, B.remark,
  83.             A.create_user_id, E.username, A.create_datetime,
  84.             A.update_user_id, F.username, A.update_datetime
  85.         FROM cb_in_out_cashbank A
  86.         INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  87.         INNER JOIN m_partner_ou C ON B.partner_id = C.partner_id
  88.         INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
  89.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  90.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  91.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  92.         INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
  93.         WHERE A.tenant_id = $2
  94.         AND Z.ou_id = $8 '||
  95.         vFilterCashbankV2||'
  96.         AND A.doc_date BETWEEN $4 AND $5
  97.         AND A.status_doc = $3
  98.         AND A.doc_type_id = $7
  99.         AND B.realization_status = $9;
  100.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;
  101.  
  102. -- Cheque Giro Realization -> nilai cost
  103.     EXECUTE '
  104.         WITH tt_in_out_cashbank AS(
  105.             SELECT A.in_out_cashbank_id, B.cashbank_id
  106.             FROM cb_in_out_cashbank A
  107.             INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  108.             WHERE B.realization_status = $10 '||
  109.             vFilterCashbankV2||'
  110.             GROUP BY A.in_out_cashbank_id, B.cashbank_id
  111.         )
  112.         INSERT INTO tt_detail_mutation_cashbank(
  113.             session_id, tenant_id, order_type_data,
  114.             doc_type_id, doc_type_desc,
  115.             doc_no, doc_date, ou_id, ou_code,
  116.             ou_rc_id, ou_rc_code, 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.         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.             COALESCE(C.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(C.ou_rc_id, A.ou_id)), -1 * C.cost_amount,
  124.             G.flg_cash_bank, $7, C.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.         FROM cb_in_out_cashbank A
  128.         INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  129.         INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  130.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  131.         INNER JOIN t_user E ON A.create_user_id = E.user_id
  132.         INNER JOIN t_user F ON A.update_user_id = F.user_id
  133.         INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
  134.         WHERE A.tenant_id = $2
  135.         AND Z.ou_id = $9
  136.         AND A.doc_date BETWEEN $4 AND $5
  137.         AND A.status_doc = $3
  138.         AND A.doc_type_id = $8
  139.         AND C.cost_amount <> 0;
  140.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCGRealization, pOuId, vStatusAccept;
  141.  
  142. -- EDC  Settlement -> nilai dokumen masuk
  143.     EXECUTE '
  144.         INSERT INTO tt_detail_mutation_cashbank(
  145.             session_id, tenant_id, order_type_data,
  146.             doc_type_id, doc_type_desc,
  147.             doc_no, doc_date, ou_id, ou_code,
  148.             ou_rc_id, ou_rc_code, amount,
  149.             mode_payment, bank_payment, curr_code, remark,
  150.             create_user_id, create_user_name, create_datetime,
  151.             update_user_id, update_user_name, update_datetime)
  152.         SELECT $1, $2, 2,  
  153.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  154.             A.doc_no, A.doc_date, Y.ou_id, f_get_ou_code(Y.ou_id),
  155.             Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
  156.             H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  157.             A.create_user_id, I.username, A.create_datetime,
  158.             A.update_user_id, J.username, A.update_datetime
  159.         FROM cb_in_out_cashbank A
  160.         INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  161.         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
  162.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  163.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  164.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  165.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  166.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  167.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  168.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  169.         WHERE A.tenant_id = $2
  170.         AND Z.ou_id = $9 '||
  171.         vFilterCashbankV2||'
  172.         AND A.doc_date BETWEEN $4 AND $5
  173.         AND A.status_doc = $3
  174.         AND A.doc_type_id = $8;
  175.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
  176.  
  177. -- EDC  Settlement -> nilai cost
  178.     EXECUTE '
  179.         INSERT INTO tt_detail_mutation_cashbank(
  180.             session_id, tenant_id, order_type_data,
  181.             doc_type_id, doc_type_desc,
  182.             doc_no, doc_date, ou_id, ou_code,
  183.             ou_rc_id, ou_rc_code, amount,
  184.             mode_payment, bank_payment, curr_code, remark,
  185.             create_user_id, create_user_name, create_datetime,
  186.             update_user_id, update_user_name, update_datetime)
  187.         SELECT $1, $2, 2,  
  188.             A.doc_type_id, f_get_doc_desc(A.doc_type_id),
  189.             A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
  190.             Y.ou_id, f_get_ou_code(Y.ou_id), -1 * C.cost_amount,
  191.             H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
  192.             A.create_user_id, I.username, A.create_datetime,
  193.             A.update_user_id, J.username, A.update_datetime
  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 cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  197.         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
  198.         INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
  199.         INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
  200.         INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
  201.         INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
  202.         INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
  203.         INNER JOIN t_user I ON A.create_user_id = I.user_id
  204.         INNER JOIN t_user J ON A.update_user_id = J.user_id
  205.         WHERE A.tenant_id = $2
  206.         AND Z.ou_id = $9 '||
  207.         vFilterCashbankV2||'
  208.         AND A.doc_date BETWEEN $4 AND $5
  209.         AND A.status_doc = $3
  210.         AND A.doc_type_id = $8
  211.         AND C.cost_amount <> 0;
  212.     'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;
RAW Paste Data