Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT PPMS_TRX_REF_MIN - 1 + LEVEL
- FROM (SELECT
- MIN(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF_MIN,
- MAX(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF_MAX
- FROM
- mtl_material_transactions mmt
- WHERE 1=1
- AND TRUNC(transaction_date) = TRUNC(TO_DATE('2018-08-08','YYYY-MM-DD'))
- --and transaction_Reference like '%FIM%233684'
- AND mmt.transaction_source_type_id = 120
- --and transaction_id = 227030600
- AND mmt.organization_id = 82)
- CONNECT BY LEVEL <= PPMS_TRX_REF_MAX - PPMS_TRX_REF_MIN + 1
- MINUS
- (SELECT DISTINCT
- TO_NUMBER(SUBSTR(transaction_reference,LENGTH(transaction_reference)-5,9999)) PPMS_TRX_REF
- FROM
- mtl_material_transactions mmt
- WHERE 1=1
- AND TRUNC(transaction_date) = TRUNC(TO_DATE('2018-08-08','YYYY-MM-DD'))
- --and transaction_Reference like '%FIM%233684'
- AND mmt.transaction_source_type_id = 120
- --and transaction_id = 227030600
- AND mmt.organization_id = 82
- )
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement