Query to get ESS Request details(Oracle Fusion)

 SELECT UNIQUE rh1.requestid Child_requestid,rp1.value job_id --,rh.PARENTREQUESTID request_id

  FROM fusion.ess_request_history rh1, fusion.ess_request_property rp1

WHERE rh1.requestid = rp1.requestid

and rp1.name ='outputOverrideParameter'

--and rh.PARENTREQUESTID in (

AND EXISTS (

SELECT UNIQUE rh.requestid Child_requestid --,rh.PARENTREQUESTID request_id

  FROM fusion.ess_request_history rh, fusion.ess_request_property rp

WHERE rh.requestid = rp.requestid

--and rh.requestid = :P_REQ_ID

AND (rh.requestid  = rh1.PARENTREQUESTID OR rh.requestid  = rh1.requestid)

and rh.PARENTREQUESTID = :P_REQ_ID

   )

and rownum = 1

To Display the Run time and instance Details(Oracle Fusion)

 SELECT 

TO_CHAR (:ACCOUNTING_P_FROM_DATE, 'DD-MON-RRRR') From_GL_Date,

TO_CHAR (:ACCOUNTING_P_TO_DATE, 'DD-MON-RRRR') To_GL_Date,

NVL(:P_BU_NAME,'ALL') BU_NAME,

NVL(:P_SOURCE,'ALL') SOURCE,

TO_CHAR (:INV_P_FROM_DATE, 'DD-MON-RRRR') From_Invoice_Date,

TO_CHAR (:INV_P_TO_DATE, 'DD-MON-RRRR') To_Invoice_Date,

TO_CHAR (SYSDATE + 3/24, 'DD-MM-YYYY HH12:MI:SS AM') PRINT_DATE,

to_char(sysdate,'DD-MON-RRRR HH24:MI:SS') as RUN_DATE,

((select SUBSTR(external_virtual_host,INSTR( external_virtual_host, '-', 1,2 )+1,9) from ask_deployed_domains WHERE

deployed_domain_name='FADomain')) instance,

FND_GLOBAL.USER_NAME as RUN_BY

FROM DUAL

Query to extract the user details(Oracle Fusion)

 SELECT 

 pur.username USER

,papf.person_number EMP_NUM

,ppnf.first_name EMP_FIRST_NAME

,ppnf.last_name EMP_LAST_NAME 

,DECODE(SUSPENDED,'Y', 'Inactive User','N', 'Active User') USER_STATUS

,ASSIGNMENT_STATUS_TYPE EMP_STATUS

FROM 

 per_users pur 

,per_person_names_f ppnf

,per_all_people_f papf 

,per_all_assignments_f paaf

WHERE pur.person_id = ppnf.person_id 

AND pur.person_id = papf.person_id

AND paaf.person_id = papf.person_id 

AND name_type = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_Date AND ppnf.effective_end_Date

AND TRUNC(SYSDATE) BETWEEN papf.effective_start_Date AND papf.effective_end_Date

AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_Date AND paaf.effective_end_Date

More Columns in RTF(Oracle Fusion)

 Suppose we are developing the RTF report with more than 30 columns then we don't have sufficient place in the RTF even if we used the landscape. 

  In case we use the regular excel(.xls)output format in the report that doesn't provide the report output in readable format. We need to expand all columns. To avoid that we can use the 'mhtml' format. This format provides the feel good report output with proper width.

Date and Time parameter for Oracle Report(Oracle Fusion)

 Hi Guys,

Usually we are using the format as 'MM-dd-yyyy' for Date parameter in the Data model to display for Date calendar.

In case we want to submit with time stamp, we have to use below format in the Parameters 'Date Format Sting' MM-dd-yyyy HH:mm:ss

To remove the space lines in the notepad++

 Suppose we have the extra space lines between the lines like below then go to 

Edit ----> Line Operations ------> Remove empty lines

Before removing empty lines:

-----------------------------

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

-----After removing the empty lines

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

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(Oracle Fusion)

  --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

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