Advertisement
Quantos

ppms missing

Aug 13th, 2018
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 0.97 KB | None | 0 0
  1. SELECT PPMS_TRX_REF_MIN - 1 + LEVEL
  2. FROM (SELECT
  3.         MIN(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF_MIN,
  4.         MAX(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF_MAX
  5. FROM
  6. mtl_material_transactions mmt
  7. WHERE 1=1
  8. AND TRUNC(transaction_date) = TRUNC(TO_DATE('2018-08-08','YYYY-MM-DD'))
  9. --and transaction_Reference like '%FIM%233684'
  10. AND mmt.transaction_source_type_id = 120
  11. --and transaction_id = 227030600
  12. AND mmt.organization_id = 82)
  13. CONNECT BY LEVEL <= PPMS_TRX_REF_MAX - PPMS_TRX_REF_MIN + 1
  14. MINUS
  15. (SELECT DISTINCT
  16.         TO_NUMBER(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF
  17. FROM
  18. mtl_material_transactions mmt
  19. WHERE 1=1
  20. AND TRUNC(transaction_date) = TRUNC(TO_DATE('2018-08-08','YYYY-MM-DD'))
  21. --and transaction_Reference like '%FIM%233684'
  22. AND mmt.transaction_source_type_id = 120
  23. --and transaction_id = 227030600
  24. AND mmt.organization_id = 82
  25. )
  26. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement