abirama62

vaidasi_migrate_kas_bon

Oct 14th, 2020 (edited)
912
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH duplicate_data AS (
  2.         SELECT A.ou_code, A.doc_no, A.doc_date
  3.         FROM temp_migarate_kas_bon_karyawan A
  4.         GROUP BY A.ou_code, A.doc_no, A.doc_date
  5.         HAVING COUNT(1) > 1
  6.     )
  7. SELECT pSessionId, A.ou_code, A.partner_code, A.doc_no, A.doc_date, A.remark, A.amount
  8.     FROM temp_migarate_kas_bon_karyawan A
  9.     WHERE doc_no <> '' AND doc_date <> '' AND partner_code <> ''
  10.     AND A.amount > 0
  11.     AND NOT EXISTS (
  12.       SELECT 1 FROM cb_payment_order B WHERE A.doc_no = B.doc_no AND A.doc_date = B.doc_date
  13.     ) AND EXISTS (
  14.       SELECT 1 FROM m_partner C WHERE A.partner_code = C.partner_code AND C.tenant_id = pTenantId
  15.     ) AND EXISTS (
  16.       SELECT 1 FROM t_ou D WHERE A.ou_code = D.ou_code AND D.tenant_id = pTenantId
  17.     ) AND NOT EXISTS (
  18.     SELECT 1 FROM duplicate_data E
  19.     WHERE A.ou_code = E.ou_code AND A.doc_no = E.ou_code AND LEFT(A.doc_date, 6) = LEFT(E.doc_date, 6)
  20.     );
RAW Paste Data