Transfer Order(Oracle Fusion)

 --Oracle Fusion Transfer Order

SELECT 

         iop.organization_code

,ir.subinventory_code subinv

        ,esib.item_number

,msit.description item_description

         ,itoh.header_number  Transfer_orders

,nvl(ir.primary_reservation_quantity,0) reserved_qty

,itol.requested_qty demand_qty

,(nvl(itol.requested_qty,0) -  nvl(ir.primary_reservation_quantity,0)) remaining_qty

,esib.primary_uom_code uom

,(SELECT meaning

FROM fnd_lookup_values_vl flvv

WHERE  flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES'

AND flvv.lookup_code = ir.demand_source_type_id

AND flvv.view_application_id = 0) demand_source_type_name  

       ,to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') required_date

   ,to_char(itoh.ordered_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') ordered_date

   ,to_char(itol.SCHEDULED_SHIP_DATE, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') requested_date  

   ,(NVL(sum(ioqd.primary_transaction_quantity),0)) onhand_qty

   , itol.DESTINATION_ORGANIZATION_ID

   ,hl.location_name Destination_location

   -- ,ioqd.primary_transaction_quantity onhand_qty

    /*   ,ir.revision rev

   -- ,ir.lot_number lot_number

   ,ir.locator_id loc_id

   ,ir.demand_source_type_id type_id

   -- ,ir.demand_source_name source  

       -- ,iop.organization_code    

   ,itst.transaction_source_type_name  source_type

   ,iil.locator_name

   ,itst.transaction_source_type_id

   ,ecs.category_set_name

   ,ec.category_code

   ,ec.category_name */

FROM 

inv_transfer_order_headers itoh

    ,inv_transfer_order_lines itol

    ,inv_sales_orders iso

     ,inv_reservations ir

,egp_system_items_b esib

  ,EGP_SYSTEM_ITEMS_TL msit

,inv_org_parameters iop

,inv_txn_source_types_tl itst

,egp_categories_vl ec 

,egp_item_categories  eic

,egp_category_sets_tl  ecs

,inv_generic_dispositions igd

,gl_code_combinations gl

,inv_item_locations iil

,inv_onhand_quantities_detail ioqd

,hr_locations hl

WHERE 1=1

AND esib.INVENTORY_ITEM_ID=msit.INVENTORY_ITEM_ID

AND itoh.header_id     = itol.header_id

AND itol.source_organization_id = ir.organization_id

AND itol.inventory_item_id    = ir.inventory_item_id

AND iso.sales_order_id        = ir.demAND_source_header_id

AND iso.source_order_number  = itoh.header_number

AND ir.organization_id        = esib.organization_id

AND ir.inventory_item_id      = esib.inventory_item_id

AND iop.organization_id      = esib.organization_id

AND ir.organization_id        = msit.organization_id

AND ir.inventory_item_id      = msit.inventory_item_id

AND iop.organization_id      = msit.organization_id

AND ir.demand_source_type_id = itst.transaction_source_type_id

AND itst.language            = 'US'

AND eic.inventory_item_id    = esib.inventory_item_id

AND eic.organization_id      = esib.organization_id

AND ecs.category_set_id      = eic.category_set_id

AND ec.category_id            = eic.category_id 

AND ecs.language              = 'US'

AND ir.demAND_source_header_id  = gl.code_combination_id(+)

AND ir.demAND_source_header_id  = igd.disposition_id(+)

AND  ir.locator_id              = iil.inventory_location_id(+)

AND ioqd.organization_id = esib.organization_id(+)

  AND ioqd.inventory_item_id = esib.inventory_item_id (+)

AND itol.destination_location_id = hl.location_id(+)

-- AND esib.item_number = 'ABC123'

--AND itoh.header_number='1234'

 

Group by 

          iop.organization_code

,ir.subinventory_code 

        ,esib.item_number

,msit.description

         ,itoh.header_number

,nvl(ir.primary_reservation_quantity,0)

,itol.requested_qty 

,(nvl(itol.requested_qty,0) -  nvl(ir.primary_reservation_quantity,0)) 

,esib.primary_uom_code

,ir.demand_source_type_id

,to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

        ,to_char(itoh.ordered_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

,to_char(itol.SCHEDULED_SHIP_DATE, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

,itol.DESTINATION_ORGANIZATION_ID

,hl.location_name

        order by 

to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') desc,

esib.item_number