Pick Slip Report(Oracle Fusion)

--Pick Slip Report ---> 

SELECT

  ipsn.pick_slip_number,

  ipol.source_order,

  ipol.pick_slip_line,

(

    case when ipsn.pick_slip_number > 0 then 'OPEN' else 'CONFIRMED' end

  ) Pick_status,

  ipol.item,

  esiv.DESCRIPTION item_description,

  ipol.uom,

  ipol.requested_quantity,

  ipol.MAXIMUM_PICKED_QUANTITY Pikced_quantity,

  ipsl.subinventory_code,

  iodv.ORGANIZATION_NAME Ship_from_Organization,

  esiv.inventory_item_id,

-- (  SELECT HR.LOCATION_NAME 

            -- FROM  HR_LOCATIONS HR,

      -- WSH_DELIVERY_DETAILS WDD

           -- WHERE HR.LOCATION_ID = WDD.SHIP_TO_LOCATION_ID

   -- AND WDD.INVENTORY_ITEM_ID=ESIV.INVENTORY_ITEM_ID

   -- AND WDD.organization_id=ESIV.organization_id

   -- AND ipol.source_order = WDD.sales_order_number)  as  "Ship to Location"  

Ship_To.LOCATION_NAME  as  "Ship to Location"      

  /*

  ipsn.pick_slip_number,

  ipsn.pick_slip_count,

  ipsn.pick_slip_due_date,

  ipol.movement_request,

  ipol.source_order,

  ipol.shipping_priority,

  ipol.source_subinventory,

  ipol.source_locator,

  its.transaction_source_type_name,

  ipol.customer,

  -- carrier_name||’-‘||mt.meaning||’-‘||sl.meaning shipping_method,

  ipsl.transaction_status*/

 

FROM

  inv_pick_slip_numbers ipsn,

  inv_pick_open_lines_v ipol,

  inv_pick_slip_lines_v ipsl,

  inv_txn_source_types_tl its,

  inv_transaction_types_vl itt,

  wsh_org_carrier_services wocs,

  fnd_lookup_values_vl sl,

  fnd_lookup_values_vl mt,

  wsh_carriers_v wcv,

  inv_org_parameters orgs,

  egp_system_items_vl esiv,

  INV_ORGANIZATION_DEFINITIONS_V iodv,

(  SELECT HR.LOCATION_NAME ,WDD.INVENTORY_ITEM_ID,WDD.organization_id,WDD.sales_order_number

            FROM  HR_LOCATIONS HR,

      WSH_DELIVERY_DETAILS WDD

           WHERE HR.LOCATION_ID = WDD.SHIP_TO_LOCATION_ID

   -- AND WDD.INVENTORY_ITEM_ID=ESIV.INVENTORY_ITEM_ID

   -- AND WDD.organization_id=ESIV.organization_id

   -- AND ipol.source_order = WDD.sales_order_number

   )  Ship_To

  -- WSH_DELIVERY_DETAILS WDD

WHERE

  ipol.organization_id = orgs.organization_id

  AND ipol.organization_id = wocs.organization_id (+)

  AND sl.lookup_type (+) = 'WSH_SERVICE_LEVELS'

  AND sl.lookup_code (+) = service_level

  AND mt.lookup_type (+) = 'WSH_MODE_OF_TRANSPORT'

  AND mt.lookup_code (+) = mode_of_transport

  AND wocs.carrier_id = wcv.carrier_id (+)

  AND ipsl.ship_method_code = wocs.ship_method_code (+)

  AND ipol.pick_slip = ipsn.pick_slip_number

  AND ipol.pick_slip = ipsl.pick_slip_number

  AND ipol.transaction_type = itt.transaction_type_name

  AND itt.transaction_source_type_id = its.transaction_source_type_id

  AND its.language = 'US'

  and ipol.item = esiv.item_number

  and ipol.organization_id = esiv.organization_id

  AND ipsl.organization_id = iodv.organization_id

  -- AND Ship_To.INVENTORY_ITEM_ID= ESIV.INVENTORY_ITEM_ID

-- AND Ship_To.organization_id= ESIV.organization_id

-- AND ipol.source_order = Ship_To.sales_order_number

-- AND ipsn.pick_slip_number='1006'

and (ipsn.pick_slip_number IN (:P_Pickslip_Number) OR 'All' IN (:P_Pickslip_Number || 'All'))                                             

  and (ipol.source_order IN (:P_Sales_order) OR 'All' IN (:P_Sales_order || 'All'))

   and (IODV.ORGANIZATION_id IN (:P_ORG) OR 'All' IN (:P_ORG || 'All')) 

   and (Ship_To.LOCATION_NAME IN (:P_Ship_to_loc) OR 'All' IN (:P_Ship_to_loc|| 'All')) 

  -- and ipsn.pick_slip_number='1006'

  GROUP BY

  ipol.pick_slip_line,

  ipsn.pick_slip_number,

  ipol.item,

  esiv.DESCRIPTION,

  ipol.uom,

  ipol.requested_quantity,

  ipol.source_order,

  -- ipsn.ship_to_location_id,

  ipsl.subinventory_code,

  iodv.ORGANIZATION_NAME, -- WDD.SHIP_TO_LOCATION_ID

  esiv.inventory_item_id,

  ESIV.organization_id,

  ipsl.pick_slip_number,

-- ipsl.PICKED_QUANTITY,

  ipol.MAXIMUM_PICKED_QUANTITY,

  Ship_To.LOCATION_NAME

 

 

-- order by

  -- ipol.pick_slip_line