WSH Pick Slip

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Pick Slip Report (XML)
Short Name: WSHRDPIK_XML
DB package: WSH_WSHRDPIK_XMLP_PKG
SELECT DISTINCT wpsv.pick_slip_number,
decode(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
decode(msik.reservable_type,2,wdd.locator_id,wpsv.locator_id) from_locator_id,
wpsv.transfer_subinventory to_subinventory,
wpsv.transfer_to_location to_locator_id,
nvl(wpsv.transaction_temp_id ,-99) transaction_id,
nvl(wpsv.transaction_temp_id,-99) rev_txn_id,
wpsv.move_order_line_id,
wpsv.creation_date detailing_date,
'UNPICKED' line_status,
ABS(wpsv.primary_quantity) primary_qty,
mtrh.request_number mo_number,
mtrl.line_id mo_line_id,
mtrl.line_number mo_line_number,
-99 delivery_detail_id1,
-99 ser_dd_id,
wdd.source_header_number,
to_char(wdd.source_header_id) order_n_header_char,
to_char(wdd.source_line_id) order_n_line_char1,
wdd.source_header_id,
wdd.source_line_id,
to_char(wdd.source_header_id) source_header_chr,
to_char(wdd.source_line_id) source_line_chr,
wdd.shipping_instructions,
wdd.ship_tolerance_above,
wdd.ship_tolerance_below,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wnd.delivery_id,
to_char(wnd.delivery_id) delivery_chr,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
oola.line_number sales_line_number,
&LP_ITEM_DISPLAY_VALUE item_info,
msitl.description item_description,
msik.revision_qty_control_code ,
os.set_name ,
wpgr.customer_flag CUSTOMER_FLAG1,
wpgr.order_number_flag,
wpgr.subinventory_flag,
wpgr.customer_flag,
wpgr.ship_to_flag,
wpgr.carrier_flag,
wpgr.shipment_priority_flag,
wpgr.trip_stop_flag,
wpgr.delivery_flag,
wpgr.name,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.requested_quantity2 secondary_qty_requested,
wdd.requested_quantity_uom2 secondary_qty_requested_uom,
wdd.shipped_quantity2 secondary_qty_shipped,
wdd.requested_quantity_uom2 secondary_qty_shipped_uom,
wdd.preferred_grade grade,
wdd.freight_terms_code freight_terms,
wdd.date_requested requested_ship_date,
wdd.attribute1 wdd_attribute1,
wdd.attribute2 wdd_attribute2,
wdd.attribute3 wdd_attribute3,
wdd.attribute4 wdd_attribute4,
wdd.attribute5 wdd_attribute5,
wdd.attribute6 wdd_attribute6,
wdd.attribute7 wdd_attribute7,
wdd.attribute8 wdd_attribute8,
wdd.attribute9 wdd_attribute9,
wdd.attribute10 wdd_attribute10,
wdd.attribute11 wdd_attribute11,
wdd.attribute12 wdd_attribute12,
wdd.attribute13 wdd_attribute13,
wdd.attribute14 wdd_attribute14,
wdd.attribute15 wdd_attribute15,
wnd.attribute1 wnd_attribute1,
wnd.attribute2 wnd_attribute2,
wnd.attribute3 wnd_attribute3,
wnd.attribute4 wnd_attribute4,
wnd.attribute5 wnd_attribute5,
wnd.attribute6 wnd_attribute6,
wnd.attribute7 wnd_attribute7,
wnd.attribute8 wnd_attribute8,
wnd.attribute9 wnd_attribute9,
wnd.attribute10 wnd_attribute10,
wnd.attribute11 wnd_attribute11,
wnd.attribute12 wnd_attribute12,
wnd.attribute13 wnd_attribute13,
wnd.attribute14 wnd_attribute14,
wnd.attribute15 wnd_attribute15,
msik.concatenated_segments item_name,
wdd.source_code source_code,
WSH_WSHRDPIK_XMLP_PKG.cf_warehouseformula(wdd.organization_id) CF_warehouse,
WSH_WSHRDPIK_XMLP_PKG.cf_tempformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_temp,
WSH_WSHRDPIK_XMLP_PKG.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number,
WSH_WSHRDPIK_XMLP_PKG.cf_ship_to_addressformula(wpgr.ship_to_flag,wpsv.pick_slip_number) CF_ship_to_address,
WSH_WSHRDPIK_XMLP_PKG.cf_carrierformula(wpgr.carrier_flag,wdd.ship_method_code) CF_carrier,
WSH_WSHRDPIK_XMLP_PKG.cf_shipment_priorityformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_shipment_priority,
WSH_WSHRDPIK_XMLP_PKG.cf_subinventoryformula(wpgr.subinventory_flag,decode (msik.reservable_type ,2 ,wdd.subinventory ,wpsv.subinventory_code )) CF_subinventory,
WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id) CF_delivery_id,
WSH_WSHRDPIK_XMLP_PKG.cf_deliveryformula(wpgr.delivery_flag,wnd.name) CF_delivery,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_stop_address1formula(wpgr.trip_stop_flag,wpgr.delivery_flag,WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id),wpsv.pick_slip_number) CF_trip_stop_address,
WSH_WSHRDPIK_XMLP_PKG.cf_order_numberformula(wpgr.order_number_flag,wdd.source_header_number) CF_order_number,
WSH_WSHRDPIK_XMLP_PKG.cf_customer_nameformula(wpgr.customer_flag,wdd.source_header_id) CF_customer_name,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_code_p CP_warehouse_code,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_name_p CP_warehouse_name,
WSH_WSHRDPIK_XMLP_PKG.st_addr1_p st_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr3_p ts_addr3,
WSH_WSHRDPIK_XMLP_PKG.ts_addr4_p ts_addr4,
WSH_WSHRDPIK_XMLP_PKG.ts_addr5_p ts_addr5,
WSH_WSHRDPIK_XMLP_PKG.ts_addr1_p ts_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr2_p ts_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr4_p st_addr4,
WSH_WSHRDPIK_XMLP_PKG.st_addr5_p st_addr5,
WSH_WSHRDPIK_XMLP_PKG.st_addr2_p st_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr3_p st_addr3,
WSH_WSHRDPIK_XMLP_PKG.f_to_locationformula(wpsv.transfer_to_location,wdd.organization_id) F_TO_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_customerformula(wpgr.customer_flag,wdd.source_header_id) CF_customer,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id) CF_trip_id,
WSH_WSHRDPIK_XMLP_PKG.f_item_descriptionformula(msitl.description,wdd.inventory_item_id,wdd.organization_id,msitl.description) F_ITEM_DESCRIPTION,
WSH_WSHRDPIK_XMLP_PKG.f_requested_quantityformula(wdd.source_header_id,wdd.source_line_id,wpsv.move_order_line_id) F_REQUESTED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.f_shipped_quantityformula() F_SHIPPED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_chrformula(WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id)) CF_TRIP_CHR,
WSH_WSHRDPIK_XMLP_PKG.CP_trip_name_p CP_trip_name,
WSH_WSHRDPIK_XMLP_PKG.f_from_locationformula(decode (msik.reservable_type ,2 ,wdd.locator_id ,wpsv.locator_id ),wdd.organization_id) F_FROM_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_freight_terms_nameformula(wdd.freight_terms_code,wdd.source_code) CF_FREIGHT_TERMS_NAME,
WSH_WSHRDPIK_XMLP_PKG.cf_revisionformula(nvl (wpsv.transaction_temp_id ,- 99 )) CF_REVISION,
WSH_WSHRDPIK_XMLP_PKG.B_task_idFt(wdd.organization_id) B_task_idFt
FROM MTL_MATERIAL_TRANSACTIONS_TEMP wpsv,
wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oola,
oe_sets os,
wsh_pick_grouping_rules wpgr,
mtl_system_items_b_kfv msik,
mtl_system_items_tl msitl
WHERE msik.inventory_item_id=msitl.inventory_item_id
AND msik.organization_id=msitl.organization_id
AND msitl.LANGUAGE=userenv('LANG')
AND NVL(wpsv.parent_line_id,0)=0
AND wpsv.pick_slip_number IS NOT NULL
AND ABS(NVL(wpsv.transaction_quantity,0)) > 0
AND wpsv.move_order_line_id=mtrl.line_id
AND mtrl.header_id=mtrh.header_id
AND mtrl.line_id=wdd.move_order_line_id
AND wdd.inventory_item_id=msik.inventory_item_id(+)
AND wdd.organization_id=msik.organization_id(+)
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id(+)
AND (wnd.delivery_type is null or wnd.delivery_type='STANDARD') 
AND wdd.source_line_id=oola.line_id
AND wdd.source_header_id=oola.header_id
AND wdd.released_status='S'
AND wdd.source_code='OE'
AND wdd.container_flag IN ('N','Y') 
AND wdd.ship_set_id=os.set_id(+)
AND mtrh.grouping_rule_id=wpgr.pick_grouping_rule_id(+)
&LP_PICK_STATUS_UNPICK 
&LP_WAREHOUSE_CLAUSE
&LP_MO_CLAUSE
&lp_pick_slip_num
&LP_ORDER_NUM
&LP_ORDER_TYPE
&LP_CUSTOMER_ID
&lp_ship_method_code
&LP_DETAIL_DATE_UNPICK 
&LP_PRINTER_NAME
UNION ALL
SELECT DISTINCT parent_mmtt.pick_slip_number,
decode(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
decode(msik.reservable_type,2,wdd.locator_id,wpsv.locator_id) from_locator_id,
wpsv.transfer_subinventory to_subinventory,
wpsv.transfer_to_location to_locator_id,
nvl(wpsv.transaction_temp_id ,-99) transaction_id,
nvl(wpsv.transaction_temp_id,-99) rev_txn_id,
wpsv.move_order_line_id,
wpsv.creation_date detailing_date,
'UNPICKED' line_status,
ABS(wpsv.primary_quantity) primary_qty,
mtrh.request_number mo_number,
mtrl.line_id mo_line_id,
mtrl.line_number mo_line_number,
-99 delivery_detail_id1,
-99 ser_dd_id,
wdd.source_header_number,
to_char(wdd.source_header_id) order_n_header_char,
to_char(wdd.source_line_id) order_n_line_char1,
wdd.source_header_id,
wdd.source_line_id,
to_char(wdd.source_header_id) source_header_chr,
to_char(wdd.source_line_id) source_line_chr,
wdd.shipping_instructions,
wdd.ship_tolerance_above,
wdd.ship_tolerance_below,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wnd.delivery_id,
to_char(wnd.delivery_id) delivery_chr,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
oola.line_number sales_line_number,
&LP_ITEM_DISPLAY_VALUE item_info,
msitl.description item_description,
msik.revision_qty_control_code ,
os.set_name ,
wpgr.customer_flag CUSTOMER_FLAG1,
wpgr.order_number_flag,
wpgr.subinventory_flag,
wpgr.customer_flag,
wpgr.ship_to_flag,
wpgr.carrier_flag,
wpgr.shipment_priority_flag,
wpgr.trip_stop_flag,
wpgr.delivery_flag,
wpgr.name,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.requested_quantity2 secondary_qty_requested,
wdd.requested_quantity_uom2 secondary_qty_requested_uom,
wdd.shipped_quantity2 secondary_qty_shipped,
wdd.requested_quantity_uom2 secondary_qty_shipped_uom,
wdd.preferred_grade grade,
wdd.freight_terms_code freight_terms,
wdd.date_requested requested_ship_date,
wdd.attribute1 wdd_attribute1,
wdd.attribute2 wdd_attribute2,
wdd.attribute3 wdd_attribute3,
wdd.attribute4 wdd_attribute4,
wdd.attribute5 wdd_attribute5,
wdd.attribute6 wdd_attribute6,
wdd.attribute7 wdd_attribute7,
wdd.attribute8 wdd_attribute8,
wdd.attribute9 wdd_attribute9,
wdd.attribute10 wdd_attribute10,
wdd.attribute11 wdd_attribute11,
wdd.attribute12 wdd_attribute12,
wdd.attribute13 wdd_attribute13,
wdd.attribute14 wdd_attribute14,
wdd.attribute15 wdd_attribute15,
wnd.attribute1 wnd_attribute1,
wnd.attribute2 wnd_attribute2,
wnd.attribute3 wnd_attribute3,
wnd.attribute4 wnd_attribute4,
wnd.attribute5 wnd_attribute5,
wnd.attribute6 wnd_attribute6,
wnd.attribute7 wnd_attribute7,
wnd.attribute8 wnd_attribute8,
wnd.attribute9 wnd_attribute9,
wnd.attribute10 wnd_attribute10,
wnd.attribute11 wnd_attribute11,
wnd.attribute12 wnd_attribute12,
wnd.attribute13 wnd_attribute13,
wnd.attribute14 wnd_attribute14,
wnd.attribute15 wnd_attribute15,
msik.concatenated_segments item_name,
wdd.source_code source_code,
WSH_WSHRDPIK_XMLP_PKG.cf_warehouseformula(wdd.organization_id) CF_warehouse,
WSH_WSHRDPIK_XMLP_PKG.cf_tempformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_temp,
WSH_WSHRDPIK_XMLP_PKG.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number,
WSH_WSHRDPIK_XMLP_PKG.cf_ship_to_addressformula(wpgr.ship_to_flag,parent_mmtt.pick_slip_number) CF_ship_to_address,
WSH_WSHRDPIK_XMLP_PKG.cf_carrierformula(wpgr.carrier_flag,wdd.ship_method_code) CF_carrier,
WSH_WSHRDPIK_XMLP_PKG.cf_shipment_priorityformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_shipment_priority,
WSH_WSHRDPIK_XMLP_PKG.cf_subinventoryformula(wpgr.subinventory_flag,decode (msik.reservable_type ,2 ,wdd.subinventory ,wpsv.subinventory_code )) CF_subinventory,
WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id) CF_delivery_id,
WSH_WSHRDPIK_XMLP_PKG.cf_deliveryformula(wpgr.delivery_flag,wnd.name) CF_delivery,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_stop_address1formula(wpgr.trip_stop_flag,wpgr.delivery_flag,WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id),parent_mmtt.pick_slip_number) CF_trip_stop_address,
WSH_WSHRDPIK_XMLP_PKG.cf_order_numberformula(wpgr.order_number_flag,wdd.source_header_number) CF_order_number,
WSH_WSHRDPIK_XMLP_PKG.cf_customer_nameformula(wpgr.customer_flag,wdd.source_header_id) CF_customer_name,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_code_p CP_warehouse_code,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_name_p CP_warehouse_name,
WSH_WSHRDPIK_XMLP_PKG.st_addr1_p st_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr3_p ts_addr3,
WSH_WSHRDPIK_XMLP_PKG.ts_addr4_p ts_addr4,
WSH_WSHRDPIK_XMLP_PKG.ts_addr5_p ts_addr5,
WSH_WSHRDPIK_XMLP_PKG.ts_addr1_p ts_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr2_p ts_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr4_p st_addr4,
WSH_WSHRDPIK_XMLP_PKG.st_addr5_p st_addr5,
WSH_WSHRDPIK_XMLP_PKG.st_addr2_p st_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr3_p st_addr3,
WSH_WSHRDPIK_XMLP_PKG.f_to_locationformula(wpsv.transfer_to_location,wdd.organization_id) F_TO_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_customerformula(wpgr.customer_flag,wdd.source_header_id) CF_customer,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id) CF_trip_id,
WSH_WSHRDPIK_XMLP_PKG.f_item_descriptionformula(msitl.description,wdd.inventory_item_id,wdd.organization_id,msitl.description) F_ITEM_DESCRIPTION,
WSH_WSHRDPIK_XMLP_PKG.f_requested_quantityformula(wdd.source_header_id,wdd.source_line_id,wpsv.move_order_line_id) F_REQUESTED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.f_shipped_quantityformula() F_SHIPPED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_chrformula(WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id)) CF_TRIP_CHR,
WSH_WSHRDPIK_XMLP_PKG.CP_trip_name_p CP_trip_name,
WSH_WSHRDPIK_XMLP_PKG.f_from_locationformula(decode (msik.reservable_type ,2 ,wdd.locator_id ,wpsv.locator_id ),wdd.organization_id) F_FROM_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_freight_terms_nameformula(wdd.freight_terms_code,wdd.source_code) CF_FREIGHT_TERMS_NAME,
WSH_WSHRDPIK_XMLP_PKG.cf_revisionformula(nvl (wpsv.transaction_temp_id ,- 99 )) CF_REVISION,
WSH_WSHRDPIK_XMLP_PKG.B_task_idFt(wdd.organization_id) B_task_idFt
FROM MTL_MATERIAL_TRANSACTIONS_TEMP wpsv,
MTL_MATERIAL_TRANSACTIONS_TEMP parent_mmtt,
wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oola,
oe_sets os,
wsh_pick_grouping_rules wpgr,
mtl_system_items_b_kfv msik,
mtl_system_items_tl msitl
WHERE wpsv.parent_line_id=parent_mmtt.transaction_temp_id
AND wpsv.parent_line_id<>wpsv.transaction_temp_id
AND msik.inventory_item_id=msitl.inventory_item_id
AND msik.organization_id=msitl.organization_id
AND msitl.LANGUAGE=userenv('LANG')
AND parent_mmtt.pick_slip_number IS NOT NULL
AND ABS(NVL(wpsv.transaction_quantity,0)) > 0
AND wpsv.move_order_line_id=mtrl.line_id
AND mtrl.header_id=mtrh.header_id
AND mtrl.line_id=wdd.move_order_line_id
AND wdd.inventory_item_id=msik.inventory_item_id(+)
AND wdd.organization_id=msik.organization_id(+)
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id(+)
AND (wnd.delivery_type is null or wnd.delivery_type='STANDARD') 
AND wdd.source_line_id=oola.line_id
AND wdd.source_header_id=oola.header_id
AND wdd.released_status='S'
AND wdd.source_code='OE'
AND wdd.container_flag IN ('N','Y') 
AND wdd.ship_set_id=os.set_id(+)
AND mtrh.grouping_rule_id=wpgr.pick_grouping_rule_id(+)
&LP_PICK_STATUS_UNPICK 
&LP_WAREHOUSE_CLAUSE
&LP_MO_CLAUSE
&lp_pick_slip_num
&LP_ORDER_NUM
&LP_ORDER_TYPE
&LP_CUSTOMER_ID
&lp_ship_method_code
&LP_DETAIL_DATE_UNPICK 
&LP_PRINTER_NAME
UNION ALL
SELECT DISTINCT wpsv.pick_slip_number,
decode(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
decode(msik.reservable_type,2,wdd.locator_id,wpsv.locator_id) from_locator_id,
wpsv.transfer_subinventory to_subinventory,
wpsv.transfer_to_location to_locator_id,
nvl(wpsv.transaction_temp_id ,-99) transaction_id,
nvl(wpsv.transaction_temp_id,-99) rev_txn_id,
wpsv.move_order_line_id,
wpsv.creation_date detailing_date,
'UNPICKED' line_status,
ABS(wpsv.primary_quantity) primary_qty,
mtrh.request_number mo_number,
mtrl.line_id mo_line_id,
mtrl.line_number mo_line_number,
-99 delivery_detail_id1,
-99 ser_dd_id,
wdd.source_header_number,
to_char(wdd.source_header_id) order_n_header_char,
to_char(wdd.source_line_id) order_n_line_char1,
wdd.source_header_id,
wdd.source_line_id,
to_char(wdd.source_header_id) source_header_chr,
to_char(wdd.source_line_id) source_line_chr,
wdd.shipping_instructions,
wdd.ship_tolerance_above,
wdd.ship_tolerance_below,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wnd.delivery_id,
to_char(wnd.delivery_id) delivery_chr,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
oola.line_number sales_line_number,
&LP_ITEM_DISPLAY_VALUE item_info,
msitl.description item_description,
msik.revision_qty_control_code ,
os.set_name ,
wpgr.customer_flag CUSTOMER_FLAG1,
wpgr.order_number_flag,
wpgr.subinventory_flag,
wpgr.customer_flag,
wpgr.ship_to_flag,
wpgr.carrier_flag,
wpgr.shipment_priority_flag,
wpgr.trip_stop_flag,
wpgr.delivery_flag,
wpgr.name,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.requested_quantity2 secondary_qty_requested,
wdd.requested_quantity_uom2 secondary_qty_requested_uom,
wdd.shipped_quantity2 secondary_qty_shipped,
wdd.requested_quantity_uom2 secondary_qty_shipped_uom,
wdd.preferred_grade grade,
wdd.freight_terms_code freight_terms,
wdd.date_requested requested_ship_date,
wdd.attribute1 wdd_attribute1,
wdd.attribute2 wdd_attribute2,
wdd.attribute3 wdd_attribute3,
wdd.attribute4 wdd_attribute4,
wdd.attribute5 wdd_attribute5,
wdd.attribute6 wdd_attribute6,
wdd.attribute7 wdd_attribute7,
wdd.attribute8 wdd_attribute8,
wdd.attribute9 wdd_attribute9,
wdd.attribute10 wdd_attribute10,
wdd.attribute11 wdd_attribute11,
wdd.attribute12 wdd_attribute12,
wdd.attribute13 wdd_attribute13,
wdd.attribute14 wdd_attribute14,
wdd.attribute15 wdd_attribute15,
wnd.attribute1 wnd_attribute1,
wnd.attribute2 wnd_attribute2,
wnd.attribute3 wnd_attribute3,
wnd.attribute4 wnd_attribute4,
wnd.attribute5 wnd_attribute5,
wnd.attribute6 wnd_attribute6,
wnd.attribute7 wnd_attribute7,
wnd.attribute8 wnd_attribute8,
wnd.attribute9 wnd_attribute9,
wnd.attribute10 wnd_attribute10,
wnd.attribute11 wnd_attribute11,
wnd.attribute12 wnd_attribute12,
wnd.attribute13 wnd_attribute13,
wnd.attribute14 wnd_attribute14,
wnd.attribute15 wnd_attribute15,
msik.concatenated_segments item_name,
wdd.source_code source_code,
WSH_WSHRDPIK_XMLP_PKG.cf_warehouseformula(wdd.organization_id) CF_warehouse,
WSH_WSHRDPIK_XMLP_PKG.cf_tempformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_temp,
WSH_WSHRDPIK_XMLP_PKG.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number,
WSH_WSHRDPIK_XMLP_PKG.cf_ship_to_addressformula(wpgr.ship_to_flag,wpsv.pick_slip_number) CF_ship_to_address,
WSH_WSHRDPIK_XMLP_PKG.cf_carrierformula(wpgr.carrier_flag,wdd.ship_method_code) CF_carrier,
WSH_WSHRDPIK_XMLP_PKG.cf_shipment_priorityformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_shipment_priority,
WSH_WSHRDPIK_XMLP_PKG.cf_subinventoryformula(wpgr.subinventory_flag,decode (msik.reservable_type ,2 ,wdd.subinventory ,wpsv.subinventory_code )) CF_subinventory,
WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id) CF_delivery_id,
WSH_WSHRDPIK_XMLP_PKG.cf_deliveryformula(wpgr.delivery_flag,wnd.name) CF_delivery,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_stop_address1formula(wpgr.trip_stop_flag,wpgr.delivery_flag,WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id),wpsv.pick_slip_number) CF_trip_stop_address,
WSH_WSHRDPIK_XMLP_PKG.cf_order_numberformula(wpgr.order_number_flag,wdd.source_header_number) CF_order_number,
WSH_WSHRDPIK_XMLP_PKG.cf_customer_nameformula(wpgr.customer_flag,wdd.source_header_id) CF_customer_name,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_code_p CP_warehouse_code,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_name_p CP_warehouse_name,
WSH_WSHRDPIK_XMLP_PKG.st_addr1_p st_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr3_p ts_addr3,
WSH_WSHRDPIK_XMLP_PKG.ts_addr4_p ts_addr4,
WSH_WSHRDPIK_XMLP_PKG.ts_addr5_p ts_addr5,
WSH_WSHRDPIK_XMLP_PKG.ts_addr1_p ts_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr2_p ts_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr4_p st_addr4,
WSH_WSHRDPIK_XMLP_PKG.st_addr5_p st_addr5,
WSH_WSHRDPIK_XMLP_PKG.st_addr2_p st_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr3_p st_addr3,
WSH_WSHRDPIK_XMLP_PKG.f_to_locationformula(wpsv.transfer_to_location,wdd.organization_id) F_TO_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_customerformula(wpgr.customer_flag,wdd.source_header_id) CF_customer,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id) CF_trip_id,
WSH_WSHRDPIK_XMLP_PKG.f_item_descriptionformula(msitl.description,wdd.inventory_item_id,wdd.organization_id,msitl.description) F_ITEM_DESCRIPTION,
WSH_WSHRDPIK_XMLP_PKG.f_requested_quantityformula(wdd.source_header_id,wdd.source_line_id,wpsv.move_order_line_id) F_REQUESTED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.f_shipped_quantityformula() F_SHIPPED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_chrformula(WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id)) CF_TRIP_CHR,
WSH_WSHRDPIK_XMLP_PKG.CP_trip_name_p CP_trip_name,
WSH_WSHRDPIK_XMLP_PKG.f_from_locationformula(decode (msik.reservable_type ,2 ,wdd.locator_id ,wpsv.locator_id ),wdd.organization_id) F_FROM_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_freight_terms_nameformula(wdd.freight_terms_code,wdd.source_code) CF_FREIGHT_TERMS_NAME,
WSH_WSHRDPIK_XMLP_PKG.cf_revisionformula(nvl (wpsv.transaction_temp_id ,- 99 )) CF_REVISION,
WSH_WSHRDPIK_XMLP_PKG.B_task_idFt(wdd.organization_id) B_task_idFt
FROM (SELECT parent_mmtt.subinventory_code from_subinventory ,
parent_mmtt.locator_id from_locator_id ,
parent_mmtt.transfer_subinventory to_subinventory,
parent_mmtt.transfer_to_location to_locator_id ,
0 primary_quantity ,
parent_mmtt.pick_slip_number ,
parent_mmtt.creation_date detailing_date ,
(SELECT child_mmtt.move_order_line_id
FROM mtl_material_transactions_temp child_mmtt
WHERE child_mmtt.parent_line_id=parent_mmtt.transaction_temp_id
AND child_mmtt.pick_slip_number IS NULL
AND ROWNUM=1
) move_order_line_id ,
'UNPICKED' line_status ,
parent_mmtt.transaction_temp_id transaction_id,
creation_date,
transaction_temp_id,
transfer_to_location,
transfer_subinventory,
locator_id,
subinventory_code
FROM mtl_material_transactions_temp parent_mmtt
WHERE parent_mmtt.pick_slip_number IS NOT NULL
AND parent_mmtt.transaction_temp_id=parent_mmtt.parent_line_id) wpsv,
wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oola,
oe_sets os,
wsh_pick_grouping_rules wpgr,
mtl_system_items_b_kfv msik,
mtl_system_items_tl msitl
WHERE msik.inventory_item_id=msitl.inventory_item_id
AND msik.organization_id=msitl.organization_id
AND msitl.LANGUAGE=userenv('LANG')
AND wpsv.pick_slip_number IS NOT NULL
--AND ABS(NVL(wpsv.transaction_quantity,0)) > 0
AND wpsv.move_order_line_id=mtrl.line_id
AND mtrl.header_id=mtrh.header_id
AND mtrl.line_id=wdd.move_order_line_id
AND wdd.inventory_item_id=msik.inventory_item_id(+)
AND wdd.organization_id=msik.organization_id(+)
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id(+)
AND (wnd.delivery_type is null or wnd.delivery_type='STANDARD') 
AND wdd.source_line_id=oola.line_id
AND wdd.source_header_id=oola.header_id
AND wdd.released_status='S'
AND wdd.source_code='OE'
AND wdd.container_flag IN ('N','Y') 
AND wdd.ship_set_id=os.set_id(+)
AND mtrh.grouping_rule_id=wpgr.pick_grouping_rule_id(+)
&LP_PICK_STATUS_UNPICK 
&LP_WAREHOUSE_CLAUSE
&LP_MO_CLAUSE
&lp_pick_slip_num
&LP_ORDER_NUM
&LP_ORDER_TYPE
&LP_CUSTOMER_ID
&lp_ship_method_code
&LP_DETAIL_DATE_UNPICK 
&LP_PRINTER_NAME
UNION ALL
SELECT DISTINCT wpsv.pick_slip_number,
decode(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
decode(msik.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id,
wpsv.to_subinventory,
wpsv.to_locator_id,
nvl(wpsv.transaction_id ,-99) transaction_id,
nvl(wdd.transaction_id,-99) rev_txn_id,
wpsv.move_order_line_id,
wpsv.detailing_date,
'PICKED' line_status,
nvl(wdd.picked_quantity,wdd.requested_quantity) primary_qty,
mtrh.request_number mo_number,
wpsv.line_id mo_line_id,
wpsv.line_number mo_line_number,
wdd.delivery_detail_id delivery_detail_id1,
wdd.delivery_detail_id ser_dd_id,
wdd.source_header_number,
to_char(wdd.source_header_id) order_n_header_char,
to_char(wdd.source_line_id) order_n_line_char1,
wdd.source_header_id,
wdd.source_line_id,
to_char(wdd.source_header_id) source_header_chr,
to_char(wdd.source_line_id) source_line_chr,
wdd.shipping_instructions,
wdd.ship_tolerance_above,
wdd.ship_tolerance_below,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wnd.delivery_id,
to_char(wnd.delivery_id) delivery_chr,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
oola.line_number sales_line_number,
&LP_ITEM_DISPLAY_VALUE item_info,
msitl.description item_description,
msik.revision_qty_control_code,
os.set_name ,
wpgr.customer_flag CUSTOMER_FLAG1,
wpgr.order_number_flag,
wpgr.subinventory_flag,
wpgr.customer_flag,
wpgr.ship_to_flag,
wpgr.carrier_flag,
wpgr.shipment_priority_flag,
wpgr.trip_stop_flag,
wpgr.delivery_flag,
wpgr.name,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.requested_quantity2 secondary_qty_requested,
wdd.requested_quantity_uom2 secondary_qty_requested_uom,
wdd.shipped_quantity2 secondary_qty_shipped,
wdd.requested_quantity_uom2 secondary_qty_shipped_uom,
wdd.preferred_grade grade,
wdd.freight_terms_code freight_terms,
wdd.date_requested requested_ship_date,
wdd.attribute1 wdd_attribute1,
wdd.attribute2 wdd_attribute2,
wdd.attribute3 wdd_attribute3,
wdd.attribute4 wdd_attribute4,
wdd.attribute5 wdd_attribute5,
wdd.attribute6 wdd_attribute6,
wdd.attribute7 wdd_attribute7,
wdd.attribute8 wdd_attribute8,
wdd.attribute9 wdd_attribute9,
wdd.attribute10 wdd_attribute10,
wdd.attribute11 wdd_attribute11,
wdd.attribute12 wdd_attribute12,
wdd.attribute13 wdd_attribute13,
wdd.attribute14 wdd_attribute14,
wdd.attribute15 wdd_attribute15,
wnd.attribute1 wnd_attribute1,
wnd.attribute2 wnd_attribute2,
wnd.attribute3 wnd_attribute3,
wnd.attribute4 wnd_attribute4,
wnd.attribute5 wnd_attribute5,
wnd.attribute6 wnd_attribute6,
wnd.attribute7 wnd_attribute7,
wnd.attribute8 wnd_attribute8,
wnd.attribute9 wnd_attribute9,
wnd.attribute10 wnd_attribute10,
wnd.attribute11 wnd_attribute11,
wnd.attribute12 wnd_attribute12,
wnd.attribute13 wnd_attribute13,
wnd.attribute14 wnd_attribute14,
wnd.attribute15 wnd_attribute15,
msik.concatenated_segments item_name ,
wdd.source_code source_code,
WSH_WSHRDPIK_XMLP_PKG.cf_warehouseformula(wdd.organization_id) CF_warehouse,
WSH_WSHRDPIK_XMLP_PKG.cf_tempformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_temp,
WSH_WSHRDPIK_XMLP_PKG.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number,
WSH_WSHRDPIK_XMLP_PKG.cf_ship_to_addressformula(wpgr.ship_to_flag,wpsv.pick_slip_number) CF_ship_to_address,
WSH_WSHRDPIK_XMLP_PKG.cf_carrierformula(wpgr.carrier_flag,wdd.ship_method_code) CF_carrier,
WSH_WSHRDPIK_XMLP_PKG.cf_shipment_priorityformula(wpgr.shipment_priority_flag,wdd.shipment_priority_code) CF_shipment_priority,
WSH_WSHRDPIK_XMLP_PKG.cf_subinventoryformula(wpgr.subinventory_flag,decode (msik.reservable_type ,2 ,wdd.subinventory ,wpsv.subinventory_code )) CF_subinventory,
WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id) CF_delivery_id,
WSH_WSHRDPIK_XMLP_PKG.cf_deliveryformula(wpgr.delivery_flag,wnd.name) CF_delivery,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_stop_address1formula(wpgr.trip_stop_flag,wpgr.delivery_flag,WSH_WSHRDPIK_XMLP_PKG.cf_delivery_idformula(wpgr.delivery_flag,wnd.delivery_id),wpsv.pick_slip_number) CF_trip_stop_address,
WSH_WSHRDPIK_XMLP_PKG.cf_order_numberformula(wpgr.order_number_flag,wdd.source_header_number) CF_order_number,
WSH_WSHRDPIK_XMLP_PKG.cf_customer_nameformula(wpgr.customer_flag,wdd.source_header_id) CF_customer_name,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_code_p CP_warehouse_code,
WSH_WSHRDPIK_XMLP_PKG.CP_warehouse_name_p CP_warehouse_name,
WSH_WSHRDPIK_XMLP_PKG.st_addr1_p st_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr3_p ts_addr3,
WSH_WSHRDPIK_XMLP_PKG.ts_addr4_p ts_addr4,
WSH_WSHRDPIK_XMLP_PKG.ts_addr5_p ts_addr5,
WSH_WSHRDPIK_XMLP_PKG.ts_addr1_p ts_addr1,
WSH_WSHRDPIK_XMLP_PKG.ts_addr2_p ts_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr4_p st_addr4,
WSH_WSHRDPIK_XMLP_PKG.st_addr5_p st_addr5,
WSH_WSHRDPIK_XMLP_PKG.st_addr2_p st_addr2,
WSH_WSHRDPIK_XMLP_PKG.st_addr3_p st_addr3,
WSH_WSHRDPIK_XMLP_PKG.f_to_locationformula(wpsv.to_locator_id,wdd.organization_id) F_TO_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_customerformula(wpgr.customer_flag,wdd.source_header_id) CF_customer,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id) CF_trip_id,
WSH_WSHRDPIK_XMLP_PKG.f_item_descriptionformula(msitl.description,wdd.inventory_item_id,wdd.organization_id,msitl.description) F_ITEM_DESCRIPTION,
WSH_WSHRDPIK_XMLP_PKG.f_requested_quantityformula(wdd.source_header_id,wdd.source_line_id,wpsv.move_order_line_id) F_REQUESTED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.f_shipped_quantityformula() F_SHIPPED_QUANTITY,
WSH_WSHRDPIK_XMLP_PKG.cf_trip_chrformula(WSH_WSHRDPIK_XMLP_PKG.cf_trip_idformula(wnd.delivery_id,wnd.initial_pickup_location_id)) CF_TRIP_CHR,
WSH_WSHRDPIK_XMLP_PKG.CP_trip_name_p CP_trip_name,
WSH_WSHRDPIK_XMLP_PKG.f_from_locationformula(decode (msik.reservable_type ,2 ,wdd.locator_id ,wpsv.from_locator_id ),wdd.organization_id) F_FROM_LOCATION,
WSH_WSHRDPIK_XMLP_PKG.cf_freight_terms_nameformula(wdd.freight_terms_code,wdd.source_code) CF_FREIGHT_TERMS_NAME,
WSH_WSHRDPIK_XMLP_PKG.cf_revisionformula(nvl (wpsv.transaction_id ,- 99 )) CF_REVISION,
WSH_WSHRDPIK_XMLP_PKG.B_task_idFt(wdd.organization_id) B_task_idFt
FROM 
(
SELECT mmt.subinventory_code subinventory_code,
mmt.locator_id from_locator_id,
mmt.transfer_subinventory to_subinventory,
mmt.transfer_locator_id to_locator_id,
abs(mmt.primary_quantity) primary_qty,
mmt.pick_slip_number,
mmt.transaction_date detailing_date,
mmt.move_order_line_id,
'PICKED' line_status,
mmt.transaction_id,
mmt.transaction_uom,
mmt.secondary_transaction_quantity,
mmt.secondary_uom_code ,
mtrl.header_id,
mtrl.line_id,
mtrl.line_number
FROM mtl_material_transactions mmt,
mtl_txn_request_lines mtrl
WHERE mmt.pick_slip_number IS NOT NULL 
AND nvl(mmt.transaction_quantity,0) < 0 
AND mmt.move_order_line_id=mtrl.line_id
&LP_PICK_STATUS 
&LP_WAREHOUSE_CLAUSE
&LP_DETAIL_DATE_MMT 
&LP_PICK_SLIP_NUM_MMT 
) wpsv,
wsh_delivery_details wdd,
mtl_txn_request_headers mtrh,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oola,
oe_sets os,
wsh_pick_grouping_rules wpgr,
mtl_system_items_b_kfv msik,
mtl_system_items_tl msitl
WHERE msik.inventory_item_id=msitl.inventory_item_id
AND msik.organization_id=msitl.organization_id
AND msitl.LANGUAGE=userenv('LANG')
AND wpsv.header_id=mtrh.header_id
AND wpsv.move_order_line_id=wdd.move_order_line_id
AND nvl(wpsv.transaction_id,-99)=decode(nvl(wdd.transaction_id ,-99),-99,nvl(wpsv.transaction_id,-99),wdd.transaction_id)
AND wdd.inventory_item_id=msik.inventory_item_id(+)
AND wdd.organization_id=msik.organization_id(+)
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id(+)
AND (wnd.delivery_type is null or wnd.delivery_type='STANDARD') 
AND wdd.source_line_id=oola.line_id
AND wdd.source_header_id=oola.header_id
AND wdd.source_code='OE'
AND wdd.released_status != 'S'
AND wdd.container_flag IN ('N','Y') 
AND wdd.ship_set_id=os.set_id(+)
AND mtrh.grouping_rule_id=wpgr.pick_grouping_rule_id(+)
&LP_MO_CLAUSE
&LP_ORDER_NUM
&LP_ORDER_TYPE
&LP_CUSTOMER_ID
&lp_ship_method_code
&LP_PRINTER_NAME
UNION ALL
SELECT DISTINCT wpsv.pick_slip_number,
decode(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
decode(msik.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id,
wpsv.to_subinventory,
wpsv.to_locator_id,
nvl(wpsv.transaction_id ,-99) transaction_id,
nvl(wdd.transaction_id,-99) rev_txn_id,
wpsv.move_order_line_id,
wpsv.detailing_date,
'PICKED' line_status,
nvl(wdd.picked_quantity,wdd.requested_quantity) primary_qty,
mtrh.request_number mo_number,
wpsv.line_id mo_line_id,
wpsv.line_number mo_line_number,
wdd.delivery_detail_id delivery_detail_id1,
wdd.delivery_detail_id ser_dd_id,
wdd.source_header_number,
to_char(wdd.source_header_id) order_n_header_char,
to_char(wdd.source_line_id) order_n_line_char1,
wdd.source_header_id,
wdd.source_line_id,
to_char(wdd.source_header_id) source_header_chr,
to_char(wdd.source_line_id) source_line_chr,
wdd.shipping_instructions,
wdd.ship_tolerance_above,
wdd.ship_tolerance_below,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wnd.delivery_id,
to_char(wnd.delivery_id) delivery_chr,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
oola.line_number sales_line_number,
&LP_ITEM_DISPLAY_VALUE item_info,
msit
Parameter Name SQL text Validation
Locator Flex Code
 
Item Flex Code
 
Printer Name
 
LOV Oracle
Item Display
 
LOV Oracle
Detailing Date (High)
 
Date
Detailing Date (Low)
 
Date
Line Status
 
LOV Oracle
Customer
 
LOV Oracle
Warehouse
 
LOV Oracle
Freight Carrier
 
LOV Oracle
Move Order Number (High)
 
LOV Oracle
Move Order Number (Low)
 
LOV Oracle
Sales Order Number (High)
 
LOV Oracle
Sales Order Number (Low)
 
LOV Oracle
Sales Order Type
 
LOV Oracle
Pick Slip Number (High)
 
LOV Oracle
Pick Slip Number (Low)
 
LOV Oracle