Thursday, 31 July 2025

Query to fetch incremental data/delta data/ESS job last run date

 Query to fetch incremental data/delta data/ESS job last run date

WHERE last_update_date > (


select  

  max(R.COMPLETEDTIME)

from 

  (

    select 

      v1.requestid, 

      v1.value report_path 

    from 

      fusion.ess_request_property v1 

    where 

      1 = 1 

      AND v1.name LIKE 'report_url' 

      AND v1.value = '/Custom/Pavani/EFACost_Report.xdo' — replace report nam

  ) F1, 

  (

    select 

      v2.requestid, 

      v2.value job_name 

    from 

      fusion.ess_request_property v2 

    where 

      name = 'ujobname' 

      and value = 'FA' — replace your job name 

  ) F2 ,

  ess_request_history r 

where 

  F1.requestid = F2.requestid 

  AND F1.requestid = r.requestid

  AND EXECUTABLE_STATUS='SUCCEEDED'

  

  )

Differences between OTBI and BIP reports

 Main Differences between OTBI and BIP reports


1.BIP Reports is less user friendly as we need some technical expertise to develop the BIP reports but OTBI reports are more user friendly , we don't need technical expertise. We can develop the OTBI report with just drag and drop option.


2.BIP report is the combination of two Objects (Data Model & Report) but OTBI report has only one object Analysis.


3.For BIP reports , we need to write the SQL Query to extract the Data from Oracle fusion but in OTBI reports , we don't need to write the SQL query , except we just select the column which we want to show in the report using drag and drop and create the OTBI reports.


4. BIP reports mostly suitable for complex reports where we have to extract the data from multiple tables but  OTBI report only suitable for simple data scenarios where we have two extract data from 1 or 2 tables.


5. In BIP reports , we have the more flexibility in terms of data extraction as we can write the sql query as per our requirement but in OTBI reports , we have no flexibility ,  we can only extract those data which is available in OTBI reports , we cannot add or do changes in the Data Extraction.


6. In OTBI reports , system write the sql queries automatically based on our column selection for Data extraction but in the BIP report , we have to write the sql's statements as per our requirment.



7. OTBI reports more about simple data extraction tool but BIP is more robust tool as we can extract the design the reports layout as per our requirements.


8. In OTBI reports , we cannot design the Complex Reports Layouts for Data extraction , we can only design table based data extraction layouts but in BIP reports we can design any report layout as per our requirement.


9. We cannot design the statuary reports in OTBI reports. We have to use the BIP reports tool for statuary reports.


10.From user prospective , BIP reports GUI is much better as compared to OTBI reports.


11. OTBI reports can design very quickly but BIP reports development can take some time.


12.In BIP reports we can use the feature of report bursting but in OTBI reports we cannot use Report bursting.


13. In BIP reports, we can design multiple Templates but in the OTBI reports we can have only single template layout.


14. OTBI Reports access related to Data roles assign to our user account , based on that we can access the data in the Oracle Fusion application but in BIP reports , there is no relation of Data roles assigned to user , so user can access data irrespective of Data roles or access.


15. In terms of security , OTBI reports are more secure as compared to BIP Reports.

Friday, 25 July 2025

Using Sandbox Personalization default alias field in supplier page by concatenating Supplier name and supplier number.

 REQUIREMENT:

Need to generate the value for alias column in supplier page for (supplier name and supplier number) using concatenate.

To avoid the duplicates for supplier in EBS

Example: Supplier Name: Bala Krishna

                 Supplier Number:10090034

They need to default the alias like (Bala Kirshna_10090034)

Note: (“_”) Underscore is used to concatenate the supplier name and supplier number.

 

To Populate the Alias Value Using Sandbox Personalization:

Step 1: Create the Sandbox and select page composer from tools and enter supplier page with edit mode.

Step2: Click on Edit Pages

 


 

Step 3: Select Structure at top of supplier page.

Step 4: Right click on Alias fields as highlighted. 



Step 5: Right click on Value select Expression Builder as highlighted.



A screenshot of a computer

Description automatically generated

Step 6: Write the Expression to concatenate the Supplier Name and Supplier Number.

Step 7: Expression for Alias #{bindings.SupplierName.inputValue}_#{bindings.Segment1.inputValue}

 Click on Test and Ok

 




Step 8: After that Apply it and click on Ok.

Step 9: Save changes and submit it.


Step 10: Publish the Sandbox once the Modification is done.


 

Step 11: The alias value got defaulted in supplier page.


Thursday, 10 July 2025

Schedule OIC integration Using ESS Job/BIP Report

 Is it possible tSchedule OIC integration Using ESS Job/BIP Report ?  Yes