WSH Pick Slip Report PDF Output
Description
- Categories
- BI Publisher
- Application
- Shipping Execution
- Source
- Pick Slip Report PDF Output (XML)
- Short Name
- WSHRDPIKX_XML
- DB package
- WSH_WSHRDPIK_XMLP_PKG
Imported from BI Publisher
Pick Slip Report PDF Output
Pick Slip Report PDF Output
SQL 344 lines
SELECT DISTINCT wpsv.pick_slip_number pick_slip_number, decode(msik.reservable_type,2,wdd.subinventory,wpsv.from_subinventory) from_subinventory, decode(msik.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id, wpsv.to_subinventory to_subinventory, wpsv.to_locator_id to_locator_id, nvl(wpsv.transaction_id ,-99) transaction_id, nvl(wpsv.transaction_id, -99) rev_txn_id, wpsv.move_order_line_id move_order_line_id10, wpsv.detailing_date detailing_date, wpsv.line_status line_status, wpsv.primary_qty, mtrh.request_number mo_number, mtrl.line_id mo_line_id, mtrl.line_number mo_line_number, -99 delivery_detail_id, -99 ser_dd_id, wdd.source_header_number source_header_number, to_char(wdd.source_header_id) order_n_header_char, to_char(wdd.source_line_id) order_n_line_char, wdd.source_header_id source_header_id10, wdd.source_line_id source_line_id, to_char(wdd.source_header_id) source_header_chr, to_char(wdd.source_line_id) source_line_chr, wdd.shipping_instructions shipping_instructions, wdd.ship_tolerance_above ship_tolerance_above, wdd.ship_tolerance_below ship_tolerance_below, wdd.inventory_item_id inventory_item_id, wdd.requested_quantity_uom requested_quantity_uom, wnd.delivery_id delivery_id, to_char(wnd.delivery_id) delivery_chr, wnd.name delivery_name, wnd.initial_pickup_location_id 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 set_name , wpgr.customer_flag customer_flag, wpgr.order_number_flag order_number_flag, wpgr.subinventory_flag subinventory_flag, wpgr.customer_flag customer_flag1, wpgr.ship_to_flag ship_to_flag, wpgr.carrier_flag carrier_flag, wpgr.shipment_priority_flag shipment_priority_flag, wpgr.trip_stop_flag trip_stop_flag, wpgr.delivery_flag delivery_flag, wpgr.name name, wdd.ship_method_code carrier, wdd.shipment_priority_code priority, wdd.organization_id 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.from_subinventory )) 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.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.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, 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.CP_trip_name_p CP_trip_name FROM wsh_pick_slip_v 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.line_status = 'UNPICKED' 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_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 &LP_PICK_STATUS &LP_PRINTER_NAME UNION ALL SELECT DISTINCT wpsv.pick_slip_number pick_slip_number, decode(msik.reservable_type,2,wdd.subinventory,wpsv.from_subinventory) from_subinventory, decode(msik.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id, wpsv.to_subinventory to_subinventory, wpsv.to_locator_id to_locator_id, nvl(wpsv.transaction_id ,-99) transaction_id, nvl(wdd.transaction_id, -99) rev_txn_id, wpsv.move_order_line_id move_order_line_id10, wpsv.detailing_date detailing_date, wpsv.line_status line_status, wdd.requested_quantity primary_qty, mtrh.request_number mo_number, mtrl.line_id mo_line_id, mtrl.line_number mo_line_number, wdd.delivery_detail_id, wdd.delivery_detail_id ser_dd_id, wdd.source_header_number source_header_number, to_char(wdd.source_header_id) order_n_header_char, to_char(wdd.source_line_id) order_n_line_char, wdd.source_header_id source_header_id, wdd.source_line_id source_line_id, to_char(wdd.source_header_id) source_header_chr, to_char(wdd.source_line_id) source_line_chr, wdd.shipping_instructions shipping_instructions, wdd.ship_tolerance_above ship_tolerance_above, wdd.ship_tolerance_below ship_tolerance_below, wdd.inventory_item_id inventory_item_id, wdd.requested_quantity_uom requested_quantity_uom, wnd.delivery_id delivery_id, to_char(wnd.delivery_id) delivery_chr, wnd.name delivery_name, wnd.initial_pickup_location_id 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 set_name, wpgr.customer_flag customer_flag, wpgr.order_number_flag order_number_flag, wpgr.subinventory_flag subinventory_flag, wpgr.customer_flag customer_flag1, wpgr.ship_to_flag ship_to_flag, wpgr.carrier_flag, wpgr.shipment_priority_flag shipment_priority_flag, wpgr.trip_stop_flag trip_stop_flag, wpgr.delivery_flag delivery_flag, wpgr.name name, wdd.ship_method_code carrier, wdd.shipment_priority_code priority, wdd.organization_id 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.from_subinventory )) 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.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.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, 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.CP_trip_name_p CP_trip_name FROM wsh_pick_slip_v 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.line_status = 'PICKED' 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 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_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 &LP_PICK_STATUS &LP_PRINTER_NAME order by pick_slip_number, order_n_header_char, organization_id, name, MO_NUMBER, ship_to_flag, customer_flag, order_number_flag, subinventory_flag, customer_flag1, trip_stop_flag, delivery_flag, source_header_id10, to_subinventory, from_subinventory, to_locator_id, move_order_line_id10, DETAILING_DATE, mo_line_id, line_status, mo_line_number, source_header_number, shipping_instructions, ship_tolerance_above, ship_tolerance_below, inventory_item_id, requested_quantity_uom, delivery_id, DELIVERY_NAME, SALES_LINE_NUMBER, order_n_line_char, order_n_header_char, initial_pickup_location_id, delivery_chr, item_description, set_name, carrier, carrier_flag, order_n_line_char, shipment_priority_flag, PRIORITY, source_line_id, from_locator_id, item_info, source_code |
Parameters 15
| Parameter | Type | List of values SQL |
|---|---|---|
| Pick Slip Number (Low) | LOV Oracle | |
| Pick Slip Number (High) | LOV Oracle | |
| Sales Order Type | LOV Oracle | |
| Sales Order Number (Low) | LOV Oracle | |
| Sales Order Number (High) | LOV Oracle | |
| Move Order Number (Low) | LOV Oracle | |
| Move Order Number (High) | LOV Oracle | |
| Freight Carrier | LOV Oracle | |
| Warehouse | LOV Oracle | |
| Customer | LOV Oracle | |
| Line Status | LOV Oracle | |
| Detailing Date (Low) | Date | |
| Detailing Date (High) | Date | |
| Item Display | LOV Oracle | |
| Printer Name | LOV Oracle |