WSH Pick Slip

Description
Categories: BI Publisher
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