Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Introduction

While I hope that most of us by now are already aware of how to create a simple BI Publisher Report from Data Model using SQL query, but in case you are not aware you may feel free to refer this article here and it would get you started.

If one has some basic idea of SQL then its fine but if you are a newbie or an individual working primarily with OTBI reports (also referred to as a drag and drop reports) one may face challenges. In such scenarios one may make use of  “Query Builder” feature. This feature allows one to simply pick choose fields from the list of available fields and prepares a basic SQL query out of it. One may then simply add conditional clauses and the complete query is ready.

We will illustrate the same in the worked example here.

 

Worked Out Example

In this example we will try to get few details of person’s employee and assignment tables. At this point we know that we will be using “Standard SQL” and Data Source is “ApplicationDB_HCM”.

 

Once we click on the Query Builder a new page will open where we have to choose the Schema Name (“FUSION” for this example) and also the table names from which we want to fetch the column details.

 

Once we select the tables we would need to select the columns ( using checkbox) Remember one may even use the “Check All” option (but this will only work if the total number of columns in less than 60). The tables we are using here have more than 60 columns and hence we will have to manually choose. The application screen (post selection) would appear as below:

 

And the moment we do this a basic SQL will be ready which can be seen in the “SQL” tab

 

The SQL query which gets created can be fetched from the table below:

SQL Query Created from Query Builder

select "PER_ALL_PEOPLE_F"."PERSON_ID" as "PERSON_ID",

"PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",

"PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE",

"PER_ALL_PEOPLE_F"."BUSINESS_GROUP_ID" as "BUSINESS_GROUP_ID",

"PER_ALL_PEOPLE_F"."START_DATE" as "START_DATE",

"PER_ALL_PEOPLE_F"."APPLICANT_NUMBER" as "APPLICANT_NUMBER",

"PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER",

"PER_ALL_PEOPLE_F"."WAIVE_DATA_PROTECT" as "WAIVE_DATA_PROTECT",

"PER_ALL_ASSIGNMENTS_F"."ACTION_CODE" as "ACTION_CODE",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_ID" as "ASSIGNMENT_ID",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_NAME" as "ASSIGNMENT_NAME",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_NUMBER" as "ASSIGNMENT_NUMBER",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_SEQUENCE" as "ASSIGNMENT_SEQUENCE",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_STATUS_TYPE" as "ASSIGNMENT_STATUS_TYPE",

"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_TYPE" as "ASSIGNMENT_TYPE",

"PER_ALL_ASSIGNMENTS_F"."BARGAINING_UNIT_CODE" as "BARGAINING_UNIT_CODE",

"PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE_1",

"PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE_1",

"PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_SEQUENCE" as "EFFECTIVE_SEQUENCE",

"PER_ALL_ASSIGNMENTS_F"."EMPLOYEE_CATEGORY" as "EMPLOYEE_CATEGORY",

"PER_ALL_ASSIGNMENTS_F"."EMPLOYMENT_CATEGORY" as "EMPLOYMENT_CATEGORY",

"PER_ALL_ASSIGNMENTS_F"."NORMAL_HOURS" as "NORMAL_HOURS",

"PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD" as "NOTICE_PERIOD",

"PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD_UOM" as "NOTICE_PERIOD_UOM",

"PER_ALL_ASSIGNMENTS_F"."PERIOD_OF_SERVICE_ID" as "PERIOD_OF_SERVICE_ID",

"PER_ALL_ASSIGNMENTS_F"."PERSON_ID" as "PERSON_ID_1"

from"FUSION"."PER_ALL_ASSIGNMENTS_F" "PER_ALL_ASSIGNMENTS_F",

"FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F"

 

And one may add additional where clause as required. For this example we will use the following where clause

Where Clause

WHERE  "PER_ALL_PEOPLE_F"."PERSON_ID" = "PER_ALL_ASSIGNMENTS_F"."PERSON_ID"

AND TRUNC(SYSDATE) BETWEEN  "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" AND "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE"

AND TRUNC(SYSDATE) BETWEEN  "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_START_DATE" AND "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_END_DATE"

AND "PER_ALL_PEOPLE_F"."PERSON_NUMBER" = '481'

 

And once done we can see that the Data Model is created

 

Finally, if we click on the “Data” tab we will get the results.

 

 

Summary

So this is how one can make use of query builder feature and prepare simple SQL query based data model in Oracle ERP Cloud Application. This might serve as a good starting point for all non-techie guys to build a very basic BI Report.

Hope this is useful and you guys will be able to utilize this for self-learning or for your project deliverables.

That’s all from my side. Thanks for your time and have a nice day!


Ashish Harbhajanka

Comments   

0 #1 Glamour Models 2021-07-21 03:04
Aw, this was a very nice post. Spending some time and actual effort to produce a top notch article… but
what can I say… I hesitate a lot and don't manage to get anything done.



my page ... Glamour Models: https://blinkmodelmanagement.com/atmosphere-models-services-las-vegas/private-lingeries/
Quote

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner