View: CLN_XML_ITEMS_V

Product:	CLN - Supply Chain Trading Connector for RosettaNet
Description:	View for SHIPITEM items section in OAG SHOW_SHIPMENT_005 XML payload
Implementation/DBA Data:	APPS.CLN_XML_ITEMS_V

View Text

SELECT DISTINCT WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WND.DELIVERY_ID DELIVERY_ID
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_PART_NUMBER
, MIF.ITEM_NUMBER SUPPLIER_PART_NUMBER
, WDD.SHIPPED_QUANTITY SHIPPED_QUANTITY
, WDD.REQUESTED_QUANTITY_UOM ITEM_UOM_CODE_INT
, WDD.SRC_REQUESTED_QUANTITY ORDERED_QUANTITY
, EDPQ.PACKED_QUANTITY PACKED_QUANTITY
, 'UN' HAZARDOUS_MATERIAL_CODE_INT
, WDD.SRC_REQUESTED_QUANTITY_UOM ORDER_QUANTITY_UOM_INT
, WDD.CUST_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER
, WDD.SHIPPING_INSTRUCTIONS SHIPPING_INSTRUCTIONS
, WDD.REQUESTED_QUANTITY REQUESTED_QUANTITY
, WDD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, WDD.ATTRIBUTE1 ATTRIBUTE1
, WDD.ATTRIBUTE2 ATTRIBUTE2
, WDD.ATTRIBUTE3 ATTRIBUTE3
, WDD.ATTRIBUTE4 ATTRIBUTE4
, WDD.ATTRIBUTE5 ATTRIBUTE5
, WDD.ATTRIBUTE6 ATTRIBUTE6
, WDD.ATTRIBUTE7 ATTRIBUTE7
, WDD.ATTRIBUTE8 ATTRIBUTE8
, WDD.ATTRIBUTE9 ATTRIBUTE9
, WDD.ATTRIBUTE10 ATTRIBUTE10
, WDD.ATTRIBUTE11 ATTRIBUTE11
, WDD.ATTRIBUTE12 ATTRIBUTE12
, WDD.ATTRIBUTE13 ATTRIBUTE13
, WDD.ATTRIBUTE14 ATTRIBUTE14
, WDD.ATTRIBUTE15 ATTRIBUTE15
, WDD.LOT_NUMBER LOT_NUMBER
, OEL.CUSTOMER_LINE_NUMBER
, TO_CHAR (OEL.LINE_NUMBER) ORDER_LINE_NUMBER
, RSL.COUNTRY_OF_ORIGIN_EXT COUNTRY_OF_ORIGIN_EXT
, RSL.CUSTOMER_ITEM_REVISION CUSTOMER_ITEM_REVISION
, RSL.PACKAGING_CODE_EXT PACKAGING_CODE_EXT
, OEL.ORIG_SYS_DOCUMENT_REF
, OEL.CUSTOMER_SHIPMENT_NUMBER
, OEL.HEADER_ID SALES_ORDER_HEADER_ID
, OEL.LINE_ID SALES_ORDER_LINE_ID
, OEH.ORDER_NUMBER SALES_ORDER_NUM
, OEL.LINE_NUMBER SALES_ORDER_LINE_NUM
, 'N' MINOR_LINE_INDICATOR
, OEL.SORT_ORDER SORT_ORDER
FROM MTL_CUSTOMER_ITEMS MCI
, MTL_ITEM_FLEXFIELDS MIF
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_DSNO_PACKED_QUANTITY_V EDPQ
, RLM_SCHEDULE_LINES_ALL RSL
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
WHERE OEH.HEADER_ID = OEL.HEADER_ID
AND WDD.SOURCE_CODE = 'OE'
AND WDD.SOURCE_HEADER_ID = OEL.HEADER_ID
AND WDD.SOURCE_LINE_ID = OEL.LINE_ID
AND DECODE (OEL.SOURCE_DOCUMENT_TYPE_ID
, 5
, OEL.SOURCE_DOCUMENT_LINE_ID
, -99999 ) = RSL.LINE_ID(+)
AND NVL (WDD.CONTAINER_FLAG
, 'N') = 'N'
AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND WDD.ORGANIZATION_ID = MIF.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND NVL (WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_ID = EDPQ.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND NVL (WDA.PARENT_DELIVERY_DETAIL_ID
, 0) = NVL (EDPQ.CONTAINER_ID
, 0)
AND WDD.INVENTORY_ITEM_ID = EDPQ.INVENTORY_ITEM_ID
AND WDD.SOURCE_LINE_ID = EDPQ.SOURCE_LINE_ID
AND WDA.DELIVERY_ID IS NOT NULL
AND (WND.SHIPMENT_DIRECTION IS NULL OR WND.SHIPMENT_DIRECTION = 'O' )
AND (WDD.LINE_DIRECTION IS NULL OR WDD.LINE_DIRECTION = 'O') UNION SELECT WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WND.DELIVERY_ID DELIVERY_ID
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_PART_NUMBER
, MIF.ITEM_NUMBER SUPPLIER_PART_NUMBER
, (OEL2.ORDERED_QUANTITY * WDD.SHIPPED_QUANTITY / OEL.ORDERED_QUANTITY ) SHIPPED_QUANTITY
, NULL ITEM_UOM_CODE_INT
, OEL2.ORDERED_QUANTITY ORDERED_QUANTITY
, (OEL2.ORDERED_QUANTITY * EDPQ.PACKED_QUANTITY / OEL.ORDERED_QUANTITY ) PACKED_QUANTITY
, 'UN' HAZARDOUS_MATERIAL_CODE_INT
, OEL2.ORDER_QUANTITY_UOM ORDER_QUANTITY_UOM_INT
, WDD.CUST_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER
, WDD.SHIPPING_INSTRUCTIONS SHIPPING_INSTRUCTIONS
, (OEL2.ORDERED_QUANTITY * WDD.REQUESTED_QUANTITY / OEL.ORDERED_QUANTITY ) REQUESTED_QUANTITY
, NULL ATTRIBUTE_CATEGORY
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
, NULL LOT_NUMBER
, OEL2.CUSTOMER_LINE_NUMBER
, TO_CHAR (OEL2.LINE_NUMBER) ORDER_LINE_NUMBER
, NULL COUNTRY_OF_ORIGIN_EXT
, NULL CUSTOMER_ITEM_REVISION
, NULL PACKAGING_CODE_EXT
, OEL2.ORIG_SYS_DOCUMENT_REF
, OEL2.CUSTOMER_SHIPMENT_NUMBER
, OEL2.HEADER_ID SALES_ORDER_HEADER_ID
, OEL2.LINE_ID SALES_ORDER_LINE_ID
, OEH.ORDER_NUMBER SALES_ORDER_NUM
, OEL2.LINE_NUMBER SALES_ORDER_LINE_NUM
, 'Y' MINOR_LINE_INDICATOR
, OEL2.SORT_ORDER SORT_ORDER
FROM MTL_CUSTOMER_ITEMS MCI
, MTL_ITEM_FLEXFIELDS MIF
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_DSNO_PACKED_QUANTITY_V EDPQ
, RLM_SCHEDULE_LINES_ALL RSL
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL2
WHERE OEH.HEADER_ID = OEL.HEADER_ID
AND WDD.SOURCE_CODE = 'OE'
AND WDD.SOURCE_HEADER_ID = OEL.HEADER_ID
AND WDD.SOURCE_LINE_ID = OEL.LINE_ID
AND DECODE (OEL.SOURCE_DOCUMENT_TYPE_ID
, 5
, OEL.SOURCE_DOCUMENT_LINE_ID
, -99999 ) = RSL.LINE_ID(+)
AND NVL (WDD.CONTAINER_FLAG
, 'N') = 'N'
AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND OEL2.SHIP_FROM_ORG_ID = MIF.ORGANIZATION_ID
AND OEL2.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND NVL (WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_ID = EDPQ.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND NVL (WDA.PARENT_DELIVERY_DETAIL_ID
, 0) = NVL (EDPQ.CONTAINER_ID
, 0)
AND WDD.INVENTORY_ITEM_ID = EDPQ.INVENTORY_ITEM_ID
AND WDD.SOURCE_LINE_ID = EDPQ.SOURCE_LINE_ID
AND WDA.DELIVERY_ID IS NOT NULL
AND OEL.TOP_MODEL_LINE_ID = OEL2.TOP_MODEL_LINE_ID
AND OEL.HEADER_ID = OEL2.HEADER_ID
AND OEL.ITEM_TYPE_CODE = 'CONFIG'
AND OEL2.ITEM_TYPE_CODE <> 'CONFIG'
AND OEL2.LINE_ID <> OEL2.TOP_MODEL_LINE_ID
AND (WND.SHIPMENT_DIRECTION IS NULL OR WND.SHIPMENT_DIRECTION = 'O')
AND (WDD.LINE_DIRECTION IS NULL OR WDD.LINE_DIRECTION = 'O')

Columns