VIEW: APPS.IC_PURG_VW1

Source

View Text - Preformatted

SELECT 'PROD' , PLANT_CODE , BATCH_NO , BATCH_ID , last_update_date FROM PM_BTCH_HDR WHERE BATCH_STATUS = 4 UNION SELECT 'PORD', ORGN_CODE, PO_NO, PO_ID, LAST_UPDATE_DATE FROM PO_ORDR_HDR WHERE PO_STATUS = 20 UNION SELECT 'RECV', ORGN_CODE, RECV_NO, RECV_ID, RECV_DATE FROM PO_RECV_HDR WHERE DELETE_MARK = 0 UNION SELECT DOC_TYPE, ORGN_CODE, '', DOC_ID, TRANS_DATE FROM IC_TRAN_CMP WHERE DOC_TYPE IN ('ADJI','ADJR','GRDI', 'GRDR','STSI','STSR', 'TRNI','TRNR') UNION SELECT 'OPSO', ORGN_CODE, ORDER_NO, ORDER_ID, LAST_UPDATE_DATE FROM OP_ORDR_HDR WHERE /* ORDER_TYPE = 1 AND */ /* REMOVED LIMITATION ON ORDER TYPE */ DELETE_MARK = 0 AND COMPLETED_IND >= 0 AND ORDER_ID NOT IN (SELECT DISTINCT ORDER_ID FROM OP_ORDR_DTL WHERE SHIP_STATUS < 20 AND COMPLETED_IND = 0) UNION SELECT 'OPSO', ORGN_CODE, BOL_NO, BOL_ID, LAST_UPDATE_DATE FROM OP_BILL_LAD WHERE DELETE_MARK = 0 AND BOL_ID NOT IN (SELECT DISTINCT BOL_ID FROM OP_ORDR_DTL WHERE SHIP_STATUS < 20 AND COMPLETED_IND = 0)

View Text - HTML Formatted

SELECT 'PROD'
, PLANT_CODE
, BATCH_NO
, BATCH_ID
, LAST_UPDATE_DATE
FROM PM_BTCH_HDR
WHERE BATCH_STATUS = 4 UNION SELECT 'PORD'
, ORGN_CODE
, PO_NO
, PO_ID
, LAST_UPDATE_DATE
FROM PO_ORDR_HDR
WHERE PO_STATUS = 20 UNION SELECT 'RECV'
, ORGN_CODE
, RECV_NO
, RECV_ID
, RECV_DATE
FROM PO_RECV_HDR
WHERE DELETE_MARK = 0 UNION SELECT DOC_TYPE
, ORGN_CODE
, ''
, DOC_ID
, TRANS_DATE
FROM IC_TRAN_CMP
WHERE DOC_TYPE IN ('ADJI'
, 'ADJR'
, 'GRDI'
, 'GRDR'
, 'STSI'
, 'STSR'
, 'TRNI'
, 'TRNR') UNION SELECT 'OPSO'
, ORGN_CODE
, ORDER_NO
, ORDER_ID
, LAST_UPDATE_DATE
FROM OP_ORDR_HDR
WHERE /* ORDER_TYPE = 1
AND */ /* REMOVED LIMITATION ON ORDER TYPE */ DELETE_MARK = 0
AND COMPLETED_IND >= 0
AND ORDER_ID NOT IN (SELECT DISTINCT ORDER_ID
FROM OP_ORDR_DTL
WHERE SHIP_STATUS < 20
AND COMPLETED_IND = 0) UNION SELECT 'OPSO'
, ORGN_CODE
, BOL_NO
, BOL_ID
, LAST_UPDATE_DATE
FROM OP_BILL_LAD
WHERE DELETE_MARK = 0
AND BOL_ID NOT IN (SELECT DISTINCT BOL_ID
FROM OP_ORDR_DTL
WHERE SHIP_STATUS < 20
AND COMPLETED_IND = 0)