WSH Packing Slip

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Packing Slip Report
Application: Shipping Execution
Source: Packing Slip Report (XML)
Short Name: WSHRDPAK_XML
DB package: WSH_WSHRDPAK_XMLP_PKG
Run WSH Packing Slip and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
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
Download
Related Blitz Reports
WSH Packing Slip Report PDF Output WSH Bill of Lading PPF_WP3_OM_DETAILS