WMS Scheduled Shipment Report (Without Barcodes)

Description
Categories: BI Publisher
Application: Warehouse Management
Source: Scheduled Shipment Report (Without Barcodes) (XML)
Short Name: WMSSCHS1_XML
DB package: WMS_WMSSCHS1_XMLP_PKG
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')
Parameter NameSQL textValidation
Organization
 
LOV Oracle
Customer
 
LOV Oracle
Sales Order Type
 
LOV Oracle
Sales Order Number
 
LOV Oracle
Sales Order Line Number
 
LOV Oracle
Schedule Date From
 
Date
Schedule Date To
 
Date
Request Date From
 
Date
Request Date To
 
Date
Delivery
 
LOV Oracle
Trip
 
LOV Oracle
Project
 
LOV Oracle
Task
 
LOV Oracle
Print Prior Reservations Only
 
LOV Oracle
Print Customer Production Details
 
LOV Oracle