WSH Packing Slip
Description
- Categories
- BI Publisher
- Application
- Shipping Execution
- Source
- Packing Slip Report (XML)
- Short Name
- WSHRDPAK_XML
- DB package
- WSH_WSHRDPAK_XMLP_PKG
Imported from BI Publisher
Packing Slip Report
Packing Slip Report
SQL 185 lines
SELECT det.inventory_item_id c_inv_item_id , &p_customer_item_number c_customer_item_number , fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_flex', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') c_item_flex , msi.description c_item_description , det.customer_item_id c_customer_item_id , decode(:P_STANDALONE ,'N', det.source_header_number,nvl(det.reference_number,det.source_header_number)) c_so_number , decode(:P_STANDALONE,'N',det.source_line_number , nvl(det.reference_line_number,det.source_line_number)) c_so_line_number , det.cust_po_number c_po_number , sum ( round(nvl(det.requested_quantity,0),:p_quantity_precision)) c_req_qty , sum (round(nvl(det.requested_quantity2,0), :p_quantity_precision)) c_req_qty2 , det.requested_quantity_uom c_req_qty_uom , det.requested_quantity_uom2 c_req_qty_uom2 , det.src_requested_quantity_uom c_src_req_uom , det.src_requested_quantity_uom2 c_src_req_uom2 , det.requested_quantity_uom2 c_secondary_qty_shipped_uom , det.requested_quantity_uom2 c_secondary_qty_unshipped_uom ,avg( round(nvl(det.src_requested_quantity,0),:p_quantity_precision) )c_src_req_qty ,avg( round(nvl(det.src_requested_quantity2,0),:p_quantity_precision) ) c_src_req_qty2 , sum(round(nvl(det.shipped_quantity,0),:p_quantity_precision) ) c_ship_qty ,sum( round(nvl(det.shipped_quantity2,0),:p_quantity_precision)) c_ship_qty2 ,sum( round(nvl(det.delivered_quantity,0),:p_quantity_precision) ) c_del_qty ,sum( round(nvl(det.src_requested_quantity,0)-nvl(det.shipped_quantity,0)-nvl(det.requested_quantity,0),:p_quantity_precision) )c_back_qty , sum(round(nvl(det.src_requested_quantity2,0)-nvl(det.shipped_quantity2,0)-nvl(det.requested_quantity2,0),:p_quantity_precision) )c_back_qty2 , det.customer_id c_customer_id , det.source_code c_src_code , det.source_header_id c_src_hdr_id , to_char(det.source_header_id) c_src_hdr_chr , det.source_line_id c_src_line_id , det.lot_number c_lot_number , det.preferred_grade c_preferred_grade , det.shipping_instructions c_ship_instructions , det.packing_instructions c_pack_instructions ,det.organization_id c_organization_id ,det.source_header_id attach_order_id ,det.mcc_code c_mcc_code, WSH_WSHRDPAK_XMLP_PKG.cf_item_descriptionformula(msi.description, det.inventory_item_id, det.organization_id) CF_Item_description, WSH_WSHRDPAK_XMLP_PKG.cf_item_numformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_flex', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), det.inventory_item_id, :C_DEL_ORG_ID) CF_ITEM_NUM, WSH_WSHRDPAK_XMLP_PKG.cf_cust_item_numformula(det.customer_item_id) CF_CUST_ITEM_NUM, WSH_WSHRDPAK_XMLP_PKG.cf_requestor_nameformula(det.source_line_id) CF_requestor_name, WSH_WSHRDPAK_XMLP_PKG.cf_unshipped_qtyformula(det.source_line_id) CF_UNSHIPPED_QTY, WSH_WSHRDPAK_XMLP_PKG.cf_cum_qtyformula(:C_DEL_CUSTOMER_ID, det.customer_id, det.source_line_id) CF_CUM_QTY, WSH_WSHRDPAK_XMLP_PKG.CP_internal_sales_order_p CP_internal_sales_order FROM wsh_delivery_details det , wsh_delivery_assignments_v das , mtl_system_items_vl msi ,mtl_customer_items mci WHERE det.delivery_detail_id=das.delivery_detail_id AND det.container_flag='N' AND das.delivery_id is not null AND das.delivery_id=:c_q2_delivery_id AND det.inventory_item_id is not null &p_organizationid_1 AND msi.organization_id=det.organization_id AND msi.inventory_item_id (+) =det.inventory_item_id AND mci.customer_item_id (+)= det.customer_item_id AND EXISTS (select 'EXISTS' from bom_inventory_components bic where bic.component_item_id = det.inventory_item_id and bic.include_on_ship_docs = 1 and bic.component_sequence_id = ( select component_sequence_id from oe_order_lines_all where line_id = det.source_line_id) and det.top_model_line_id is not null UNION select 'EXISTS' from dual where det.top_model_line_id is null UNION select 'EXISTS' from dual where msi.auto_created_config_flag = 'Y' and det.top_model_line_id is not null UNION select 'EXISTS' from dual where det.top_model_line_id = det.source_line_id UNION select 'EXISTS' from dual where det.top_model_line_id is not null and det.ato_line_id is not null) group by fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_flex', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ,det.inventory_item_id , msi.description ,&p_customer_item_number , det.customer_item_id , decode(:P_STANDALONE ,'N', det.source_header_number,nvl(det.reference_number,det.source_header_number)) , decode(:P_STANDALONE,'N',det.source_line_number , nvl(det.reference_line_number,det.source_line_number)) , det.cust_po_number , det.requested_quantity_uom , det.requested_quantity_uom2 , det.src_requested_quantity_uom , det.src_requested_quantity_uom2 , det.customer_id , det.source_code , det.source_header_id , det.source_line_id , det.lot_number , det.preferred_grade , det.shipping_instructions , det.packing_instructions , det.organization_id , det.mcc_code UNION SELECT det.inventory_item_id c_inv_item_id , NULL c_customer_item_number , NULL c_item_flex , det.item_description c_item_description , det.customer_item_id c_customer_item_id --standalone changes , decode(:P_STANDALONE ,'N', det.source_header_number,nvl(det.reference_number,det.source_header_number)) c_so_number , decode(:P_STANDALONE,'N',det.source_line_number , nvl(det.reference_line_number,det.source_line_number)) c_so_line_number , det.cust_po_number c_po_number , sum ( round(nvl(det.requested_quantity,0),:p_quantity_precision)) c_req_qty , sum (round(nvl(det.requested_quantity2,0), :p_quantity_precision)) c_req_qty2 , det.requested_quantity_uom c_req_qty_uom , det.requested_quantity_uom2 c_req_qty_uom2 , det.src_requested_quantity_uom c_src_req_uom , det.src_requested_quantity_uom2 c_src_req_uom2 , det.requested_quantity_uom2 c_secondary_qty_shipped_uom , det.requested_quantity_uom2 c_secondary_qty_unshipped_uom ,avg( round(nvl(det.src_requested_quantity,0),:p_quantity_precision) )c_src_req_qty ,avg( round(nvl(det.src_requested_quantity2,0),:p_quantity_precision) ) c_src_req_qty2 , sum(round(nvl(det.shipped_quantity,0),:p_quantity_precision) ) c_ship_qty ,sum( round(nvl(det.shipped_quantity2,0),:p_quantity_precision)) c_ship_qty2 ,sum( round(nvl(det.delivered_quantity,0),:p_quantity_precision) ) c_del_qty ,sum( round(nvl(det.src_requested_quantity,0)-nvl(det.shipped_quantity,0)-nvl(det.requested_quantity,0),:p_quantity_precision) )c_back_qty , sum(round(nvl(det.src_requested_quantity2,0)-nvl(det.shipped_quantity2,0)-nvl(det.requested_quantity2,0),:p_quantity_precision) )c_back_qty2 , det.customer_id c_customer_id , det.source_code c_src_code , det.source_header_id c_src_hdr_id , to_char(det.source_header_id) c_src_hdr_chr , det.source_line_id c_src_line_id , det.lot_number c_lot_number , det.preferred_grade c_preferred_grade , det.shipping_instructions c_ship_instructions , det.packing_instructions c_pack_instructions ,det.organization_id c_organization_id ,det.source_header_id attach_order_id ,det.mcc_code c_mcc_code, WSH_WSHRDPAK_XMLP_PKG.cf_item_descriptionformula(NULL, det.inventory_item_id, det.organization_id) CF_Item_description, WSH_WSHRDPAK_XMLP_PKG.cf_item_numformula(NULL, det.inventory_item_id, :C_DEL_ORG_ID) CF_ITEM_NUM, WSH_WSHRDPAK_XMLP_PKG.cf_cust_item_numformula(det.customer_item_id) CF_CUST_ITEM_NUM, WSH_WSHRDPAK_XMLP_PKG.cf_requestor_nameformula(det.source_line_id) CF_requestor_name, WSH_WSHRDPAK_XMLP_PKG.cf_unshipped_qtyformula(det.source_line_id) CF_UNSHIPPED_QTY, WSH_WSHRDPAK_XMLP_PKG.cf_cum_qtyformula(:C_DEL_CUSTOMER_ID, det.customer_id, det.source_line_id) CF_CUM_QTY, WSH_WSHRDPAK_XMLP_PKG.CP_internal_sales_order_p CP_internal_sales_order FROM wsh_delivery_details det , wsh_delivery_assignments_v das WHERE det.delivery_detail_id=das.delivery_detail_id AND det.container_flag='N' AND das.delivery_id is not null AND das.delivery_id=:c_q2_delivery_id &p_organizationid_1 AND det.inventory_item_id is null group by NULL ,det.inventory_item_id , det.item_description , det.customer_item_id --standalone , decode(:P_STANDALONE ,'N', det.source_header_number,nvl(det.reference_number,det.source_header_number)) , decode(:P_STANDALONE,'N',det.source_line_number , nvl(det.reference_line_number,det.source_line_number)) , det.cust_po_number , det.requested_quantity_uom , det.requested_quantity_uom2 , det.src_requested_quantity_uom , det.src_requested_quantity_uom2 , det.customer_id , det.source_code , det.source_header_id , det.source_line_id , det.lot_number , det.preferred_grade , det.shipping_instructions , det.packing_instructions , det.organization_id ,det.mcc_code ORDER BY 27 ASC,26 ASC,2 ASC,3 ASC,4 ASC |
Parameters 9
| Parameter | Type | List of values SQL |
|---|---|---|
| Warehouse | LOV Oracle | |
| Delivery Name | LOV Oracle | |
| Print Customer Item | LOV Oracle | |
| Item Display | LOV Oracle | |
| Print Mode | LOV Oracle | |
| Sort by | LOV Oracle | |
| Delivery Date (Low) | Date | |
| Delivery Date (High) | Date | |
| Display Unshipped Items | LOV Oracle |