VIEW: APPS.ICX_CHV_ITEM_ORDERS_V

Source

View Text - Preformatted

SELECT CIO.SCHEDULE_ID , CIO.SCHEDULE_ITEM_ID , CIO.SCHEDULE_ORDER_ID , CIO.SUPPLY_DOCUMENT_TYPE , PLC2.DISPLAYED_FIELD , CIO.ORDER_QUANTITY , CIO.PURCHASING_UNIT_OF_MEASURE , CIO.PRIMARY_UNIT_OF_MEASURE , CIO.DUE_DATE , POR.PO_HEADER_ID, POR.PO_RELEASE_ID , NULL, POH.SEGMENT1||'-'||POR.RELEASE_NUM , PLC1.DISPLAYED_FIELD , POR.REVISION_NUM , CIO.DOCUMENT_LINE_ID , POL.LINE_NUM , CIO.DOCUMENT_SHIPMENT_ID , PLL.SHIPMENT_NUM , CIO.ATTRIBUTE_CATEGORY , CIO.ATTRIBUTE1 , CIO.ATTRIBUTE2 , CIO.ATTRIBUTE3 , CIO.ATTRIBUTE4 , CIO.ATTRIBUTE5 , CIO.ATTRIBUTE6 , CIO.ATTRIBUTE7 , CIO.ATTRIBUTE8 , CIO.ATTRIBUTE9 , CIO.ATTRIBUTE10 , CIO.ATTRIBUTE11 , CIO.ATTRIBUTE12 , CIO.ATTRIBUTE13 , CIO.ATTRIBUTE14 , CIO.ATTRIBUTE15 , CIO.LAST_UPDATE_DATE , CIO.LAST_UPDATED_BY , CIO.CREATION_DATE , CIO.CREATED_BY , CIO.LAST_UPDATE_LOGIN , CIO.REQUEST_ID , CIO.PROGRAM_APPLICATION_ID , CIO.PROGRAM_UPDATE_DATE FROM CHV_ITEM_ORDERS CIO, CHV_SCHEDULE_ITEMS CSI, PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, PO_RELEASES POR, PO_LINE_LOCATIONS PLL, PO_HEADERS POH, PO_LINES POL, PO_DOCUMENT_TYPES PDT WHERE NVL(POR.AUTHORIZATION_STATUS,'INCOMPLETE') = PLC1.LOOKUP_CODE AND PLC1.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND NVL(PDT.DOCUMENT_TYPE_CODE,'RELEASE') = 'RELEASE' AND PDT.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE AND PLC2.LOOKUP_TYPE = 'SCHEDULE_DOCUMENT_TYPE' AND PLC2.LOOKUP_CODE = 'RELEASE' AND CIO.SUPPLY_DOCUMENT_TYPE = 'RELEASE' AND CIO.SCHEDULE_ID = CSI.SCHEDULE_ID AND CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID AND CIO.DOCUMENT_HEADER_ID = PLL.PO_HEADER_ID AND CIO.DOCUMENT_LINE_ID = POL.PO_LINE_ID AND CIO.DOCUMENT_SHIPMENT_ID = PLL.LINE_LOCATION_ID AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID AND POR.PO_HEADER_ID = POH.PO_HEADER_ID AND CIO.SUPPLY_DOCUMENT_TYPE = 'RELEASE' UNION SELECT CIO.SCHEDULE_ID , CIO.SCHEDULE_ITEM_ID , CIO.SCHEDULE_ORDER_ID , CIO.SUPPLY_DOCUMENT_TYPE , PLC2.DISPLAYED_FIELD , CIO.ORDER_QUANTITY , CIO.PURCHASING_UNIT_OF_MEASURE , CIO.PRIMARY_UNIT_OF_MEASURE , CIO.DUE_DATE , TO_NUMBER('') , CIO.DOCUMENT_HEADER_ID , PRH.SEGMENT1 , NULL, PLC1.DISPLAYED_FIELD , TO_NUMBER('') , CIO.DOCUMENT_LINE_ID , PRL.LINE_NUM , CIO.DOCUMENT_SHIPMENT_ID , TO_NUMBER('') , CIO.ATTRIBUTE_CATEGORY , CIO.ATTRIBUTE1 , CIO.ATTRIBUTE2 , CIO.ATTRIBUTE3 , CIO.ATTRIBUTE4 , CIO.ATTRIBUTE5 , CIO.ATTRIBUTE6 , CIO.ATTRIBUTE7 , CIO.ATTRIBUTE8 , CIO.ATTRIBUTE9 , CIO.ATTRIBUTE10 , CIO.ATTRIBUTE11 , CIO.ATTRIBUTE12 , CIO.ATTRIBUTE13 , CIO.ATTRIBUTE14 , CIO.ATTRIBUTE15 , CIO.LAST_UPDATE_DATE , CIO.LAST_UPDATED_BY , CIO.CREATION_DATE , CIO.CREATED_BY , CIO.LAST_UPDATE_LOGIN , CIO.REQUEST_ID , CIO.PROGRAM_APPLICATION_ID , CIO.PROGRAM_UPDATE_DATE FROM CHV_ITEM_ORDERS CIO, CHV_SCHEDULE_ITEMS CSI, PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, PO_DOCUMENT_TYPES PDT WHERE NVL(PRH.AUTHORIZATION_STATUS, 'INCOMPLETE') = PLC1.LOOKUP_CODE AND PLC1.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND NVL(PDT.DOCUMENT_TYPE_CODE, 'REQUISITION') = 'REQUISITION' AND PLC2.LOOKUP_TYPE = 'SCHEDULE_DOCUMENT_TYPE' AND PLC2.LOOKUP_CODE = 'REQUISITION' AND CIO.SUPPLY_DOCUMENT_TYPE = 'REQUISITION' AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE AND CIO.SCHEDULE_ID = CSI.SCHEDULE_ID AND CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID AND CIO.DOCUMENT_HEADER_ID = PRH.REQUISITION_HEADER_ID AND CIO.DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID UNION SELECT CIO.SCHEDULE_ID , CIO.SCHEDULE_ITEM_ID , CIO.SCHEDULE_ORDER_ID , CIO.SUPPLY_DOCUMENT_TYPE , PLC.DISPLAYED_FIELD , CIO.ORDER_QUANTITY , CIO.PURCHASING_UNIT_OF_MEASURE , CIO.PRIMARY_UNIT_OF_MEASURE , CIO.DUE_DATE , TO_NUMBER('') , CIO.DOCUMENT_HEADER_ID , NULL, NULL , NULL , TO_NUMBER('') , CIO.DOCUMENT_LINE_ID , TO_NUMBER('') , CIO.DOCUMENT_SHIPMENT_ID , TO_NUMBER('') , CIO.ATTRIBUTE_CATEGORY , CIO.ATTRIBUTE1 , CIO.ATTRIBUTE2 , CIO.ATTRIBUTE3 , CIO.ATTRIBUTE4 , CIO.ATTRIBUTE5 , CIO.ATTRIBUTE6 , CIO.ATTRIBUTE7 , CIO.ATTRIBUTE8 , CIO.ATTRIBUTE9 , CIO.ATTRIBUTE10 , CIO.ATTRIBUTE11 , CIO.ATTRIBUTE12 , CIO.ATTRIBUTE13 , CIO.ATTRIBUTE14 , CIO.ATTRIBUTE15 , CIO.LAST_UPDATE_DATE , CIO.LAST_UPDATED_BY , CIO.CREATION_DATE , CIO.CREATED_BY , CIO.LAST_UPDATE_LOGIN , CIO.REQUEST_ID , CIO.PROGRAM_APPLICATION_ID , CIO.PROGRAM_UPDATE_DATE FROM CHV_ITEM_ORDERS CIO, PO_LOOKUP_CODES PLC WHERE CIO.SUPPLY_DOCUMENT_TYPE = 'PLANNED_ORDER' AND CIO.SUPPLY_DOCUMENT_TYPE = PLC.LOOKUP_CODE AND 'SCHEDULE_DOCUMENT_TYPE' = PLC.LOOKUP_TYPE

View Text - HTML Formatted

SELECT CIO.SCHEDULE_ID
, CIO.SCHEDULE_ITEM_ID
, CIO.SCHEDULE_ORDER_ID
, CIO.SUPPLY_DOCUMENT_TYPE
, PLC2.DISPLAYED_FIELD
, CIO.ORDER_QUANTITY
, CIO.PURCHASING_UNIT_OF_MEASURE
, CIO.PRIMARY_UNIT_OF_MEASURE
, CIO.DUE_DATE
, POR.PO_HEADER_ID
, POR.PO_RELEASE_ID
, NULL
, POH.SEGMENT1||'-'||POR.RELEASE_NUM
, PLC1.DISPLAYED_FIELD
, POR.REVISION_NUM
, CIO.DOCUMENT_LINE_ID
, POL.LINE_NUM
, CIO.DOCUMENT_SHIPMENT_ID
, PLL.SHIPMENT_NUM
, CIO.ATTRIBUTE_CATEGORY
, CIO.ATTRIBUTE1
, CIO.ATTRIBUTE2
, CIO.ATTRIBUTE3
, CIO.ATTRIBUTE4
, CIO.ATTRIBUTE5
, CIO.ATTRIBUTE6
, CIO.ATTRIBUTE7
, CIO.ATTRIBUTE8
, CIO.ATTRIBUTE9
, CIO.ATTRIBUTE10
, CIO.ATTRIBUTE11
, CIO.ATTRIBUTE12
, CIO.ATTRIBUTE13
, CIO.ATTRIBUTE14
, CIO.ATTRIBUTE15
, CIO.LAST_UPDATE_DATE
, CIO.LAST_UPDATED_BY
, CIO.CREATION_DATE
, CIO.CREATED_BY
, CIO.LAST_UPDATE_LOGIN
, CIO.REQUEST_ID
, CIO.PROGRAM_APPLICATION_ID
, CIO.PROGRAM_UPDATE_DATE
FROM CHV_ITEM_ORDERS CIO
, CHV_SCHEDULE_ITEMS CSI
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_RELEASES POR
, PO_LINE_LOCATIONS PLL
, PO_HEADERS POH
, PO_LINES POL
, PO_DOCUMENT_TYPES PDT
WHERE NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE') = PLC1.LOOKUP_CODE
AND PLC1.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND NVL(PDT.DOCUMENT_TYPE_CODE
, 'RELEASE') = 'RELEASE'
AND PDT.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE
AND PLC2.LOOKUP_TYPE = 'SCHEDULE_DOCUMENT_TYPE'
AND PLC2.LOOKUP_CODE = 'RELEASE'
AND CIO.SUPPLY_DOCUMENT_TYPE = 'RELEASE'
AND CIO.SCHEDULE_ID = CSI.SCHEDULE_ID
AND CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID
AND CIO.DOCUMENT_HEADER_ID = PLL.PO_HEADER_ID
AND CIO.DOCUMENT_LINE_ID = POL.PO_LINE_ID
AND CIO.DOCUMENT_SHIPMENT_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID
AND POR.PO_HEADER_ID = POH.PO_HEADER_ID
AND CIO.SUPPLY_DOCUMENT_TYPE = 'RELEASE' UNION SELECT CIO.SCHEDULE_ID
, CIO.SCHEDULE_ITEM_ID
, CIO.SCHEDULE_ORDER_ID
, CIO.SUPPLY_DOCUMENT_TYPE
, PLC2.DISPLAYED_FIELD
, CIO.ORDER_QUANTITY
, CIO.PURCHASING_UNIT_OF_MEASURE
, CIO.PRIMARY_UNIT_OF_MEASURE
, CIO.DUE_DATE
, TO_NUMBER('')
, CIO.DOCUMENT_HEADER_ID
, PRH.SEGMENT1
, NULL
, PLC1.DISPLAYED_FIELD
, TO_NUMBER('')
, CIO.DOCUMENT_LINE_ID
, PRL.LINE_NUM
, CIO.DOCUMENT_SHIPMENT_ID
, TO_NUMBER('')
, CIO.ATTRIBUTE_CATEGORY
, CIO.ATTRIBUTE1
, CIO.ATTRIBUTE2
, CIO.ATTRIBUTE3
, CIO.ATTRIBUTE4
, CIO.ATTRIBUTE5
, CIO.ATTRIBUTE6
, CIO.ATTRIBUTE7
, CIO.ATTRIBUTE8
, CIO.ATTRIBUTE9
, CIO.ATTRIBUTE10
, CIO.ATTRIBUTE11
, CIO.ATTRIBUTE12
, CIO.ATTRIBUTE13
, CIO.ATTRIBUTE14
, CIO.ATTRIBUTE15
, CIO.LAST_UPDATE_DATE
, CIO.LAST_UPDATED_BY
, CIO.CREATION_DATE
, CIO.CREATED_BY
, CIO.LAST_UPDATE_LOGIN
, CIO.REQUEST_ID
, CIO.PROGRAM_APPLICATION_ID
, CIO.PROGRAM_UPDATE_DATE
FROM CHV_ITEM_ORDERS CIO
, CHV_SCHEDULE_ITEMS CSI
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_DOCUMENT_TYPES PDT
WHERE NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE') = PLC1.LOOKUP_CODE
AND PLC1.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND NVL(PDT.DOCUMENT_TYPE_CODE
, 'REQUISITION') = 'REQUISITION'
AND PLC2.LOOKUP_TYPE = 'SCHEDULE_DOCUMENT_TYPE'
AND PLC2.LOOKUP_CODE = 'REQUISITION'
AND CIO.SUPPLY_DOCUMENT_TYPE = 'REQUISITION'
AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND CIO.SCHEDULE_ID = CSI.SCHEDULE_ID
AND CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID
AND CIO.DOCUMENT_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND CIO.DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID UNION SELECT CIO.SCHEDULE_ID
, CIO.SCHEDULE_ITEM_ID
, CIO.SCHEDULE_ORDER_ID
, CIO.SUPPLY_DOCUMENT_TYPE
, PLC.DISPLAYED_FIELD
, CIO.ORDER_QUANTITY
, CIO.PURCHASING_UNIT_OF_MEASURE
, CIO.PRIMARY_UNIT_OF_MEASURE
, CIO.DUE_DATE
, TO_NUMBER('')
, CIO.DOCUMENT_HEADER_ID
, NULL
, NULL
, NULL
, TO_NUMBER('')
, CIO.DOCUMENT_LINE_ID
, TO_NUMBER('')
, CIO.DOCUMENT_SHIPMENT_ID
, TO_NUMBER('')
, CIO.ATTRIBUTE_CATEGORY
, CIO.ATTRIBUTE1
, CIO.ATTRIBUTE2
, CIO.ATTRIBUTE3
, CIO.ATTRIBUTE4
, CIO.ATTRIBUTE5
, CIO.ATTRIBUTE6
, CIO.ATTRIBUTE7
, CIO.ATTRIBUTE8
, CIO.ATTRIBUTE9
, CIO.ATTRIBUTE10
, CIO.ATTRIBUTE11
, CIO.ATTRIBUTE12
, CIO.ATTRIBUTE13
, CIO.ATTRIBUTE14
, CIO.ATTRIBUTE15
, CIO.LAST_UPDATE_DATE
, CIO.LAST_UPDATED_BY
, CIO.CREATION_DATE
, CIO.CREATED_BY
, CIO.LAST_UPDATE_LOGIN
, CIO.REQUEST_ID
, CIO.PROGRAM_APPLICATION_ID
, CIO.PROGRAM_UPDATE_DATE
FROM CHV_ITEM_ORDERS CIO
, PO_LOOKUP_CODES PLC
WHERE CIO.SUPPLY_DOCUMENT_TYPE = 'PLANNED_ORDER'
AND CIO.SUPPLY_DOCUMENT_TYPE = PLC.LOOKUP_CODE
AND 'SCHEDULE_DOCUMENT_TYPE' = PLC.LOOKUP_TYPE