wda.parent_delivery_detail_id container_instance_id,
wdd.inventory_item_id item_id, msi.description item_description,
wdd.source_header_id order_header_id,
wdd.source_line_id order_line_id,
wdd.delivery_detail_id delivery_detail_id,
wdd.customer_item_id customer_item_id, wdd.ato_line_id ato_line_id,
mci.customer_item_number customer_part_number,
DECODE
(wdd.client_id,
NULL, mif.item_number,
wms_deploy.get_client_item (mif.organization_id,
mif.inventory_item_id
)
) supplier_part_number,
wdd.date_requested requested_date,
wdd.shipped_quantity shipped_quantity,
wdd.requested_quantity_uom item_uom_code_int,
mcc.commodity_code commodity_code_int,
msi.container_item_flag container_item_flag,
msi.container_type_code container_type_code_int,
msi.customer_order_enabled_flag customer_order_flag,
DECODE (wdd.reference_line_quantity_uom,
NULL, wdd.src_requested_quantity,
wdd.src_requested_quantity_uom, wdd.reference_line_quantity,
NULL
) ordered_quantity,
NVL (wdd.cancelled_quantity, 0) cancelled_quantity,
DECODE
(wda.parent_delivery_detail_id,
NULL, 0,
(SELECT edpq.packed_quantity
FROM wsh_dsno_packed_quantity_v edpq
WHERE wda.delivery_id = edpq.delivery_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)
) packed_quantity,
wdd.unit_price unit_list_price,
wdd.ship_model_complete_flag ship_model_complete_flag,
wdd.customer_dock_code customer_dock_code,
wdd.shipment_priority_code shipment_priority_code_int,
wnd.confirm_date shipment_confirmed_date,
mci.customer_item_desc customer_item_description,
'UN' hazardous_material_code_int, phc.hazard_class hazard_class_int,
phc.description hazard_class_description,
wdd.attribute_category del_detail_attrib_category,
wdd.attribute1 delivery_detail_attribute1,
wdd.attribute2 delivery_detail_attribute2,
wdd.attribute3 delivery_detail_attribute3,
wdd.attribute4 delivery_detail_attribute4,
wdd.attribute5 delivery_detail_attribute5,
wdd.attribute6 delivery_detail_attribute6,
wdd.attribute7 delivery_detail_attribute7,
wdd.attribute8 delivery_detail_attribute8,
wdd.attribute9 delivery_detail_attribute9,
wdd.attribute10 delivery_detail_attribute10,
wdd.attribute11 delivery_detail_attribute11,
wdd.attribute12 delivery_detail_attribute12,
wdd.attribute13 delivery_detail_attribute13,
wdd.attribute14 delivery_detail_attribute14,
wdd.attribute15 delivery_detail_attribute15,
mif.attribute_category line_part_category,
mif.attribute1 line_part_attribute1,
mif.attribute2 line_part_attribute2,
mif.attribute3 line_part_attribute3,
mif.attribute4 line_part_attribute4,
mif.attribute5 line_part_attribute5,
mif.attribute6 line_part_attribute6,
mif.attribute7 line_part_attribute7,
mif.attribute8 line_part_attribute8,
mif.attribute9 line_part_attribute9,
mif.attribute10 line_part_attribute10,
mif.attribute11 line_part_attribute11,
mif.attribute12 line_part_attribute12,
mif.attribute13 line_part_attribute13,
mif.attribute14 line_part_attribute14,
mif.attribute15 line_part_attribute15,
DECODE (wdd.reference_line_quantity_uom,
NULL, wdd.src_requested_quantity_uom,
wdd.src_requested_quantity_uom, wdd.reference_line_quantity_uom,
NULL
) order_quantity_uom_int,
wdd.customer_prod_seq cust_production_seq_num,
wdd.cust_po_number cust_po_number,
wdd.freight_terms_code freight_terms_code_int,
wdd.fob_code fob_point_code_int,
wdd.organization_id organization_id, wdd.customer_job customer_job,
wdd.customer_production_line customer_production_line,
wdd.cust_model_serial_number customer_model_serial_number,
wdd.reference_line_number reference_line_number
FROM po_hazard_classes phc,
mtl_system_items msi,
mtl_customer_items mci,
mtl_commodity_codes mcc,
mtl_item_flexfields mif,
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE NVL (wdd.container_flag, 'N') = 'N'
AND msi.hazard_class_id = phc.hazard_class_id(+)
AND wdd.customer_item_id = mci.customer_item_id(+)
AND mci.commodity_code_id = mcc.commodity_code_id(+)
AND wdd.organization_id = msi.organization_id
AND wdd.inventory_item_id = msi.inventory_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_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND wnd.delivery_type = 'STANDARD';
