VIEW: APPS.CLN_XML_CONTAINERS_RN_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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