VIEW: APPS.CLN_XML_ITEMS_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')