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