VIEW: APPS.POS_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_RELEASE_ID, 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_ALL POR, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_DOCUMENT_TYPES_ALL_TL 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.language = userenv('LANG') 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, CIO.DOCUMENT_HEADER_ID, PRH.SEGMENT1, 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, CIO.DOCUMENT_HEADER_ID, 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 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_RELEASE_ID
, 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_ALL POR
, PO_LINE_LOCATIONS_ALL PLL
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_DOCUMENT_TYPES_ALL_TL 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.LANGUAGE = USERENV('LANG')
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
, CIO.DOCUMENT_HEADER_ID
, PRH.SEGMENT1
, 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
, CIO.DOCUMENT_HEADER_ID
, 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 SUPPLY_DOCUMENT_TYPE = 'PLANNED_ORDER'
AND CIO.SUPPLY_DOCUMENT_TYPE = PLC.LOOKUP_CODE
AND 'SCHEDULE_DOCUMENT_TYPE' = PLC.LOOKUP_TYPE