select wdd.delivery_detail_id
,wdd.customer_id
,wdd.ship_to_location_id ship_to_location_id
,wnd.name
,to_char(oe1.request_date,'DD-MON-YYYY HH24:MI') request_date
,to_char(oe1.schedule_ship_date,'DD-MON-YYYY HH24:MI') schedule_ship_date
,mr.lpn_id
,wt.name trip_name
,wdd.source_header_number Barcode
,wdd.source_header_number
,to_char(oe1.line_number)||'.'||to_char(oe1.shipment_number)||decode(oe1.option_number,NULL,NULL,'.'||to_char(oe1.option_number))||decode(oe1.component_number, null, null,decode(oe1.option_number, null, '.',null)||'.'||to_char(oe1.component_number)) line_number
,wdd.inventory_item_id
,&p_item_flex_all c_item_flex_all
,wdd.item_description
,mr.revision
,wdd.requested_quantity_uom
,SUM(wdd.requested_quantity) sum_requested_qty
,wdd.project_id
,wdd.task_id
, oes.set_name
,oe1.ship_tolerance_above
,oe1.ship_tolerance_below
,mr.primary_reservation_quantity
, HZL.LOCATION,
WMS_WMSSCHS1_XMLP_PKG.cf_barcodeformula() CF_BARCODE,
WMS_WMSSCHS1_XMLP_PKG.cf_loc_flex_fieldformula() CF_LOC_FLEX_FIELD,
WMS_WMSSCHS1_XMLP_PKG.CF_SHIP_TO_LOCATIONFORMULA(wdd.ship_to_location_id) CF_SHIP_TO_LOCATION,
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,
WMS_WMSSCHS1_XMLP_PKG.CF_LPN_NUMBERFORMULA(mr.lpn_id) CF_LPN_NUMBER,
WMS_WMSSCHS1_XMLP_PKG.CF_OUTERMOST_LPNFORMULA(mr.lpn_id) CF_OUTERMOST_LPN,
WMS_WMSSCHS1_XMLP_PKG.CF_PROJECTFORMULA(wdd.project_id) CF_PROJECT,
WMS_WMSSCHS1_XMLP_PKG.CF_TASKFORMULA(wdd.task_id,wdd.project_id) CF_TASK
FROM
WSH_DELIVERY_DETAILS WDD
,WSH_NEW_DELIVERIES WND
,WSH_DELIVERY_ASSIGNMENTS WDA
,WSH_DELIVERY_LEGS WDL
,OE_ORDER_LINES_ALL oe1
,OE_SETS oes
,MTL_RESERVATIONS mr
,WSH_TRIPS WT
,WSH_TRIP_STOPS WTS
,MTL_SYSTEM_ITEMS MSI
,HZ_CUST_SITE_USES_ALL HZL
WHERE
WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID(+)
AND WDD.ORGANIZATION_ID = :P_ORG_ID
AND WDD.SOURCE_LINE_ID = OE1.LINE_ID
AND WDD.RELEASED_STATUS IN ('B','R')
AND WDD.SOURCE_CODE = 'OE'
AND WND.DELIVERY_ID = WDL.DELIVERY_ID(+)
AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID(+)
AND WTS.TRIP_ID = WT.TRIP_ID(+)
AND WDD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WDD.CONTAINER_FLAG = 'N'
AND MSI.ORGANIZATION_ID = :P_ORG_ID
AND inv_salesorder.get_salesorder_for_oeheader(OE1.HEADER_ID) = MR.demand_source_header_id (+)
AND WDD.CUSTOMER_ID = :P_CUSTOMER_ID
AND oe1.INTMED_SHIP_TO_org_ID= hzl.site_use_id(+)
AND oe1.SHIP_SET_ID= OES.SET_ID(+)
&LP_PRIOR_RES_FLAG
&LP_SHIPMENT_SCHEDULE_DATE
&LP_REQUEST_DATE
&LP_DELIVERY_ID
&LP_TRIP_STOP
&LP_PROJECT
&LP_TASK
&LP_SO_HEADER
&LP_SO_LINE
GROUP BY
wdd.customer_id
,wdd.ship_to_location_id
,wnd.name
,oe1.request_date
,oe1.schedule_ship_date
, HZL.location
,mr.lpn_id
,wt.name
,wdd.source_header_number
, to_char(oe1.line_number)||'.'||to_char(oe1.shipment_number)||decode(oe1.option_number,NULL,NULL,'.'||to_char(oe1.option_number))||decode(oe1.component_number, null, null,decode(oe1.option_number, null, '.',null)||'.'||to_char(oe1.component_number))
,wdd.inventory_item_id
,&p_item_flex_all
,wdd.item_description
,mr.revision
,wdd.requested_quantity_uom
,wdd.project_id
,wdd.task_id
, oes.set_name
,oe1.ship_tolerance_above
,oe1.ship_tolerance_below
,wdd.delivery_detail_id
,mr.primary_reservation_quantity,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') |