SELECT wt.name Trip_Number,
wnd.name Delivery_Number,
oeh.order_number,
PARTY.party_name customer_name,
PARTY.party_number customer_number,
lpn1.Lpn_id,
lpn1.license_plate_number,
lpn2.License_plate_number outermost_lpn,
lpn1.Subinventory_code,
lpn1.Locator_id,
null c_loc_flexdata,
lpn1.Outermost_lpn_id,
wd2.inventory_item_id,
null c_item_flexdata,
wd2.requested_quantity_uom uom,
wd2.requested_quantity Qty,
wd2.revision,
wd2.lot_number,
wd2.serial_number,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item_flex_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_Item_Flex_Field,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_loc_flex_field', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') CF_Loc_Flex_Field,
WMS_WMSSTCON_XMLP_PKG.inmultiplesoformula(lpn1.Lpn_id) InMultipleSO,
WMS_WMSSTCON_XMLP_PKG.inmultipledeliveryformula(lpn1.Lpn_id) InMultipleDelivery,
WMS_WMSSTCON_XMLP_PKG.inmultipletripformula(lpn1.Lpn_id) InMultipleTrip
FROM WMS_License_Plate_Numbers LPN1,
WSH_DELIVERY_DETAILS WD1,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_TRIP_STOPS WTS,
WSH_TRIPS WT,
WMS_License_Plate_Numbers LPN2,
WSH_DELIVERY_DETAILS WD2,
OE_ORDER_HEADERS_ALL OEH,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
MTL_SYSTEM_ITEMS MSI,
MTL_ITEM_LOCATIONS MIL
WHERE &p_where_clause
and WND.Organization_id = :p_org_id
and WND.delivery_id = WDA.delivery_id
and WDA.parent_delivery_detail_id = WD1.delivery_detail_id (+)
and WD1.lpn_id = LPN1.lpn_id (+)
and LPN1.outermost_lpn_id = LPN2.lpn_id (+)
and WD2.delivery_detail_id = WDA.delivery_detail_id
and WD2.released_status = 'Y'
and WND.delivery_id = WDL.delivery_id (+)
and WDL.pick_up_stop_id = WTS.stop_id (+)
and WTS.trip_id = WT.trip_id (+)
and WD2.source_code = 'OE'
and WD2.source_header_id = OEH.header_id
and WD2.inventory_item_id = MSI.inventory_item_id
and WD2.organization_id = MSI.organization_id
and LPN1.locator_id = MIL.inventory_location_id
and LPN1.organization_id = MIL.organization_id
and PARTY.party_id = oeh.sold_to_org_id
and CUST_ACCT.party_id = PARTY.party_id
ORDER BY
Trip_Number, Delivery_Number, Order_Number, lpn1.License_plate_number, lpn1.subinventory_code, MIL.SEGMENT1 || ',' || MIL.SEGMENT2 || ',' || MIL.SEGMENT3 || ',' || MIL.SEGMENT19 || ',' || MIL.SEGMENT20, MSI.SEGMENT1, wd2.lot_number, wd2.serial_number |