Product: | CLN - Supply Chain Trading Connector for RosettaNet |
---|---|
Description: | |
Implementation/DBA Data: | APPS.CLN_XML_CONTAINERS_RN_V |
SELECT DISTINCT WCI.DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WCI.SERIAL_NUMBER CONTAINER_SERIAL_NUMBER
, WCI.CONTAINER_TYPE_CODE CONTAINER_TYPE_INT
, WCI.VOLUME CONTAINER_VOLUME
, WCI.VOLUME_UOM_CODE CONTAINER_VOLUME_UOM_CODE_INT
, WCI.NET_WEIGHT CONTAINER_NET_WEIGHT
, WCI.SEAL_CODE CONTAINER_SEAL_CODE
, WCI.TRACKING_NUMBER CONTAINER_TRACKING_NUMBER
, WDA.DELIVERY_ID DELIVERY_ID
, MTI.UNIT_HEIGHT UNIT_HEIGHT
, MTI.UNIT_LENGTH UNIT_LENGTH
, MTI.UNIT_WIDTH UNIT_WIDTH
, WDA.PARENT_DELIVERY_DETAIL_ID PARENT_DELIVERY_DETAIL_ID
, WCI.LPN_ID LPN_ID
, 'N' RN_DUMMY_CONTAINER
FROM WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WCI
, MTL_SYSTEM_ITEMS MTI
WHERE WDA.DELIVERY_DETAIL_ID = WCI.DELIVERY_DETAIL_ID
AND WCI.CONTAINER_FLAG = 'Y'
AND WCI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID(+)
AND WCI.ORGANIZATION_ID = MTI.ORGANIZATION_ID(+) UNION SELECT DISTINCT TO_NUMBER(NULL) CONTAINER_INSTANCE_ID
, NULL CONTAINER_SERIAL_NUMBER
, NULL CONTAINER_TYPE_INT
, TO_NUMBER(NULL) CONTAINER_VOLUME
, NULL CONTAINER_VOLUME_UOM_CODE_INT
, TO_NUMBER(NULL) CONTAINER_NET_WEIGHT
, NULL CONTAINER_SEAL_CODE
, NULL CONTAINER_TRACKING_NUMBER
, WDA.DELIVERY_ID DELIVERY_ID
, TO_NUMBER(NULL) UNIT_HEIGHT
, TO_NUMBER(NULL) UNIT_LENGTH
, TO_NUMBER(NULL) UNIT_WIDTH
, WDA.PARENT_DELIVERY_DETAIL_ID
, TO_NUMBER(NULL) LPN_ID
, 'Y' RN_DUMMY_CONTAINER
FROM WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WDD
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND NVL(WDD.CONTAINER_FLAG
, 'N')='N'
AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL