WSH Packing Slip Report PDF Output

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
            , det.source_header_number c_so_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,
                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,
                                bom_bill_of_materials           bil
                      where bic.component_item_id = det.inventory_item_id
                          and bic.include_on_ship_docs = 1
                          and bil.common_bill_sequence_id = bic.bill_sequence_id
                          and ( bil.organization_id = det.organization_id or bil.organization_id = det.org_id)
                          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
            , det.source_header_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 
			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
            , det.source_header_number c_so_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,
                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
            , det.source_header_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 ORDER BY 27 ASC,26 ASC,2 ASC,3 ASC,4 ASC