Delivery Note(Oracle Fusion)

 SELECT

   esi.item_number||' '||esit.description item_desc,

   hz.party_name customer_name,

  fl.source_order_number order_number,

  dd.REQUESTED_QUANTITY,

  TO_CHAR(nd.CONFIRM_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') as CONFIRM_DATE, 

  dha.header_id,

  dla.DISPLAY_LINE_NUMBER,

  fl.fulfill_line_id,

  dd.SPLIT_FROM_DELIVERY_DETAIL_ID,

  fl.status_code as status,

  fl.ordered_qty as quantity,

  dla.ordered_qty,

  dd.delivery_detail_id,

  nd.delivery_Name,

  fl.fulfillment_split_ref_id,

  fl.shipped_qty,                          

  dd.lot_number,

  DECODE (

    dd.Released_status,

    'B',

    'Backordered',

    'C',

    'Shipped',

    'D',

    'Cancelled',

    'N',

    'Not Ready for Release',

    'R',

    'Ready to Release',

    'S',

    'Released to Warehouse',

    'X',

    'Not Applicable',

    'Y',

    'Staged'

  ) "line status",   

  dd.INV_INTERFACED_FLAG,

  dd.WMS_INTERFACED_FLAG,

  dd.SHIPMENT_ADVICE_STATUS,

  nd.INTERFACE_BATCH_ID,

  nd.STATUS_CODE,

  nd.ASN_STATUS_CODE,

  dd.shipped_quantity,

  fld.TASK_TYPE,

  fld.bill_of_lading_number,

  (SELECT Distinct mumt.Unit_of_Measure

from

inv_material_txns MMT,

egp_system_items_b MSIB1,

inv_units_of_measure_tl mumt,

inv_units_of_measure_b mumb

WHERE 1=1

AND esi.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID

AND  esi.ORGANIZATION_ID = MMT.ORGANIZATION_ID

AND mumt.UNIT_OF_MEASURE_ID=mumb.UNIT_OF_MEASURE_ID

AND upper(mumb.uom_code)= upper(MMT.TRANSACTION_UOM)

and mumt.language= 'US'

and nvl(mumb.disable_date, (sysdate + 1)) > sysdate

AND ROWNUM=1) QUANTITY_UOM,

dheb.attribute_char1 flight_number,

dheb.attribute_char2 Aircraft_type,

dheb.ATTRIBUTE_TIMESTAMP1 dd1,

TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY') Flight_Departure_Time,

TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP2,'DD-MM-YYYY') Delivery_time,

to_char(dha.ORDERED_DATE ,'dd-MM-yyyy') ordered_date,

iodv.ORGANIZATION_NAME Ship_from_Organization

-- TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MON-YYYY''NLS_DATE_LANGUAGE=AMERICAN') Flight_Departure_Time,

-- TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP2,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Delivery_time

 

 

FROM

  doo_headers_all dha,

  doo_headers_eff_b dheb,

  doo_fulfill_lines_all fl,

  doo_fulfill_line_details fld,

  doo_lines_all dla,

  wsh_delivery_details dd,

  wsh_delivery_assignments da,

  wsh_new_deliveries nd,

  egp_system_items_b esi,

  EGP_SYSTEM_ITEMS_TL esit,

  HZ_PARTIES HZ,

  INV_ORGANIZATION_DEFINITIONS_V iodv

 

WHERE

  dha.header_id = fl.header_id

   AND dd.SHIP_TO_PARTY_ID = HZ.PARTY_ID

  AND dha.header_id=dheb.header_id

  AND fl.line_id = dla.line_id

  AND fld.task_type (+) = 'Shipment'

  AND dha.source_order_number = dd.SALES_ORDER_NUMBER

  AND dd.delivery_detail_id = da.delivery_detail_id (+)

  AND nd.delivery_id (+) = da.delivery_id

  AND fl.fulfill_line_id = dd.source_shipment_id

  AND SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL

  AND fld.delivery_name = nd.delivery_name (+)

  AND fl.fulfill_line_id = fld.fulfill_line_id (+)

  AND esi.inventory_item_id = dd.inventory_item_id

  AND esi.organization_id = dd.organization_id

  AND esi.INVENTORY_ITEM_ID=esit.INVENTORY_ITEM_ID

   AND esi.organization_id=esit.organization_id

   AND esi.organization_id=iodv.organization_id

    AND fl.source_order_number=NVL(:p_order_number,fl.source_order_number)

AND (fl.source_order_number IN (:p_order_number) OR 'All' IN (:p_order_number ||'All'))

AND hz.party_name=NVL(:p_custmer_name,hz.party_name)

AND TRUNC(TO_DATE(TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY'),'DD-MM-YYYY'))=NVL(:P_Flight_departure_Dt,TRUNC(TO_DATE(TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY'),'DD-MM-YYYY')))

and dheb.attribute_char1=NVL(:p_flight_number,dheb.attribute_char1

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

 

Item availability Report

 --Item availability Report --> 

Select  

       a.organization_code Organization_name,

   a.secondary_inventory_name  Subinventory,

   a.item_number,

   a.onhand_qty,

   a.Primary_Reserv_qty,

   a.onhand_qty - a.Primary_Reserv_qty  Available_to_Reserve,

       a.onhand_qty-( a.onhand_qty - a.Primary_Reserv_qty) Reserve_Quantity    

from 

(

SELECT

  iop.organization_code ,

    isi.secondary_inventory_name ,

    esib.item_number  ,

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

(select  (nvl(sum(ir.PRIMARY_RESERVATION_QUANTITY),0)) 

from inv_reservations ir  

where 1=1

AND esib.inventory_item_id = ir.inventory_item_id 

AND esib.organization_id = ir.organization_id 

and ioqd.subinventory_code = ir.subinventory_code  )

Primary_Reserv_qty,

(Select sum(TRANSACTION_QUANTITY) 

from inv_material_txns imt

where  esib.inventory_item_id = imt.inventory_item_id 

AND esib.organization_id = imt.organization_id 

and ioqd.subinventory_code = imt.subinventory_code ) mtr_qty

 

 

FROM

  egp_system_items_v esib,

  inv_org_parameters iop,

  inv_secondary_inventories isi,

  inv_item_locations iil,

  inv_onhand_quantities_detail ioqd

  -- inv_reservations ir

WHERE

  iop.organization_id = esib.organization_id

  AND iop.organization_id = isi.organization_id

  AND isi.secondary_inventory_name = ioqd.subinventory_code(+)

  AND isi.organization_id = ioqd.organization_id (+)

  AND ioqd.organization_id = esib.organization_id(+)

  AND ioqd.inventory_item_id = esib.inventory_item_id (+)

  AND ioqd.locator_id = iil.inventory_location_id(+)

  AND ioqd.organization_id = iil.organization_id(+)

  -- AND esib.inventory_item_id = ir.inventory_item_id 

-- AND iop.organization_id = ir.organization_id 

-- AND isi.subinventory_id = ir.subinventory_id 

  -- and esib.ITEM_NUMBvER in ('ABC123','ABC234','ABC456')

GROUP BY

  iop.organization_code,

  esib.item_number,

  esib.primary_uom_code,

  esib.description,

  isi.secondary_inventory_name,

  isi.description,

  iil.locator_name,

  ioqd.revision,

  ioqd.locator_id,

  esib.inventory_item_id,

  isi.subinventory_id,

  esib.organization_id,

  ioqd.subinventory_code

ORDER BY

  isi.secondary_inventory_name,

  esib.item_number

  ) A

  order by  a.item_number,a.secondary_inventory_name