Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion Blog
  • 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

There might have been situations in the course of an HCM Cloud implementation project which has an outbound interface (generally referred to as HCM Extract) where one or more of the DBI used in the HCM Extracts might not return expected values.

There are many ways to troubleshoot this issue. Some of the common ways being:

  1. Using OTBI Subject Area
  2. Using BI SQL Data Model
  3. Displaying DBI value on UI (using Fast Formula)

In this example we will demonstrate the same. 

For this example, we would try to find the value of  “PER_ASG_MARITAL_STATUS”. Assuming we were using “PER_EXT_PAY_EMPLOYEES_V2_UE” we can get an idea of which specific database field holds the value.

One can get to know the query underlying a UE from Data Exchange -> User Entity Details ->Query

PER_EXT_PAY_EMPLOYEES_V2_UE (Query)

SELECT  paaf.REASON_CODE,ppa.end_date ,ppa.start_date ,papd.end_date ,papd.start_date ,NVL(pay_report_utils.get_parameter_value_date('DATE_EARNED'),pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')) ,papf.business_group_id ,paaf.action_code ,paaf.ASS_ATTRIBUTE1 ,  paaf.ASS_ATTRIBUTE10 ,paaf.ASS_ATTRIBUTE11 ,paaf.ASS_ATTRIBUTE12 ,paaf.ASS_ATTRIBUTE13 ,paaf.ASS_ATTRIBUTE14 ,paaf.ASS_ATTRIBUTE15 , paaf.ASS_ATTRIBUTE16 ,paaf.ASS_ATTRIBUTE17 ,paaf.ASS_ATTRIBUTE18 ,paaf.ASS_ATTRIBUTE19 ,paaf.ASS_ATTRIBUTE2 ,paaf.ASS_ATTRIBUTE20 , paaf.ASS_ATTRIBUTE21 ,paaf.ASS_ATTRIBUTE22 ,paaf.ASS_ATTRIBUTE23 ,paaf.ASS_ATTRIBUTE24 ,paaf.ASS_ATTRIBUTE25 ,paaf.ASS_ATTRIBUTE26 , paaf.ASS_ATTRIBUTE27 ,paaf.ASS_ATTRIBUTE28 ,paaf.ASS_ATTRIBUTE29 ,paaf.ASS_ATTRIBUTE3 ,paaf.ASS_ATTRIBUTE30 ,paaf.ASS_ATTRIBUTE4 , paaf.ASS_ATTRIBUTE5 ,paaf.ASS_ATTRIBUTE6 ,paaf.ASS_ATTRIBUTE7 ,paaf.ASS_ATTRIBUTE8 ,paaf.ASS_ATTRIBUTE9 ,paaf.created_by , paaf.creation_date ,paaf.effective_end_date ,paaf.effective_start_date ,paaf.employee_category ,paaf.employment_category ,  paaf.frequency ,paaf.grade_id ,paaf.job_id ,paaf.last_updated_by ,paaf.last_update_date ,paaf.last_update_login , paaf.legal_entity_id ,paaf.location_id ,paaf.assignment_name ,paaf.assignment_number ,paaf.object_version_number , paaf.organization_id ,paaf.position_id ,paaf.primary_assignment_flag ,paaf.primary_flag ,paaf.primary_work_relation_flag , paaf.primary_work_terms_flag ,paaf.assignment_status_type ,paaf.system_person_type ,paaf.assignment_type ,ppa.hr_assignment_id , ppos.actual_termination_date ,ppos.adjusted_svc_date ,ppos.date_start ,ppos.period_of_service_id ,ppos.legislation_code ,  ppos.original_date_of_hire ,ppos.primary_flag ,ppos.worker_number ,ppa.hr_term_id , pprd.legislative_data_group_id , ppa.legal_employer_id , papf.object_version_number , NVL2(paaf.organization_id,'DEPARTMENT','') , papd.payroll_id , ppa.payroll_assignment_id , ppa.payroll_relationship_id , pprd.payroll_relationship_number , ppa.payroll_term_id , papf.effective_end_date , papf.effective_start_date , papf.person_id , papf.person_number , pprd.payroll_stat_unit_id  

FROM  

       PER_ALL_PEOPLE_F         papf, 

      per_periods_of_service   ppos, 

       per_all_assignments_f    paaf, 

       pay_payroll_assignments  ppa, 

       pay_pay_relationships_dn pprd, 

        (select  

       nvl((select min(pdf.date_value) from pay_dates pdf, pay_time_definitions ptdf where papd.assigned_payroll_id = pdf.source_id and ptdf.short_name = 'FSED' and pdf.source_type ='AP' and ptdf.time_definition_id =pdf.time_definition_id), papd.start_date) as start_date, nvl((select  max(pdf.date_value) from pay_dates pdf, pay_time_definitions ptdf where papd.assigned_payroll_id = pdf.source_id and ptdf.short_name = 'LSED' and pdf.source_type ='AP' and ptdf.time_definition_id =pdf.time_definition_id),papd.end_date) as end_date,papd.payroll_term_id, papd.payroll_id 

from pay_assigned_payrolls_dn papd) papd 

   where ppos.person_id              = papf.person_id

     and paaf.person_id              = ppos.person_id 

     and paaf.period_of_service_id   = ppos.period_of_service_id 

     and paaf.assignment_id          = ppa.hr_assignment_id 

     and papd.payroll_term_id(+)     = ppa.payroll_term_id 

     and ppa.payroll_relationship_id = pprd.payroll_relationship_id 

     and paaf.work_terms_assignment_id = ppa.hr_term_id 

     and trunc(&B1) between papf.effective_start_date  and papf.effective_end_date 

     and trunc(&B1) between paaf.effective_start_date  and paaf.effective_end_date 

     and trunc(&B1) between pprd.start_date and pprd.end_date 

     and trunc(&B1) between ppa.start_date  and ppa.end_date 

     and trunc(&B1) between papd.start_date(+) and papd.end_date(+)

 

Also from OTBI Database Lineage Mapping sheet we can get details of the Database column which holds details of “Marital Status” field.

This clearly shows that the database field which holds the data is PER_PEOPLE_LEGISLATIVE_F.MARITAL_STATUS

Also we will create a custom Fast Formula say TEST_DBI_VALUE of Global Absence Entry Validation Fast Formula which would display the DBI Value.

TEST_DBI_VALUE (Formula Text)

DEFAULT FOR PER_ASG_PERSON_NUMBER is '0'

DEFAULT FOR PER_ASG_MARITAL_STATUS is 'X'



lc_marital_status = PER_ASG_MARITAL_STATUS

lc_person_number = PER_ASG_PERSON_NUMBER

VALID='N'

ERROR_MESSAGE='Marital Status of Person Number is ' ||lc_person_number

              || ' is: ' ||lc_marital_status

RETURN VALID,ERROR_MESSAGE

 

Now we will proceed with verifying the Marital Status value of a colleague.

Marital Status Value as seen from UI (Manage Person)

We will choose John Roberts (Person Number 120) for this demonstration.

We can see from above screenshot that Marital Status of John Roberts is “Single”

Verification Using OTBI Subject Area

We would use “Workforce Management – Person Real Time” subject area and try to display person number and marital status field.

 

Verification Using BI SQL

We can also validate the value using a SQL.

SQL

SELECT papf.person_number,

               pplf.marital_status

from per_all_people_f papf,

        per_people_legislative_f pplf

where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and     trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and pplf.person_id = papf.person_id

and papf.person_number = '120'

 

 

Displaying DBI Value from UI (using Fast Formula)

For this we would need to associate the custom “Global Absence Entry Validation” Fast Formula “TEST_DBI_VALUE” created above earlier to any existing absence type (we choose “Unpaid Absence AU” for this example).

Now when we try to book an absence of “Unpaid Absence AU” for John Roberts we will get the value of DBI Item (PER_ASG_MARITAL_STATUS)

Summary

So this is how we can check the value of a DBI Item in various different ways.

That’s all from my side, hope you guys had a good time reading this article.

Thank you all for your precious time and with this I end my last article for year 2019.

I wish all of you a very HAPPY, PROSPEROUS & EVENTFUL NEW YEAR.


Ashish Harbhajanka

Comments   

0 #1 See Details 2022-05-21 07:14
I know this web page offers quality depending articles or reviews and additional material, is there any other web site
which provides these things in quality?
Quote
0 #2 slot 633 2022-05-25 03:33
Hello there, just became alert to your blog
through Google, and found that it's truly informative.
I am going to watch out for brussels. I'll appreciate if you continue this in future.
Lots of people will be benefited from your
writing. Cheers!
Quote
0 #3 vivo slot 2022-07-17 08:03
Link exchange is nothing else however it is just placing the other person's website link on your page at proper place
and other person will also do similar in favor of you.
Quote
0 #4 vivoslot 2022-07-23 11:00
Heya i am for the first time here. I found this board and I find
It really useful & it helped me out much. I hope to give something back and aid others
like you helped me.
Quote
0 #5 vivoslot 2022-07-23 12:10
It's nearly impossible to find knowledgeable people in this particular topic, however,
you sound like you know what you're talking
about! Thanks
Quote
0 #6 vivoslot 2022-07-27 19:33
I blog often and I really appreciate your information. Your article
has truly peaked my interest. I will book mark your site and keep checking
for new details about once per week. I opted in for your Feed too.
Quote
0 #7 vivoslot 2022-07-28 07:08
You can definitely see your enthusiasm in the article you write.
The arena hopes for even more passionate writers like you
who are not afraid to mention how they believe. At all times go after your heart.
Quote
0 #8 vivoslot 2022-07-28 13:00
Hey! I just wanted to ask if you ever have any issues with hackers?
My last blog (wordpress) was hacked and I ended up losing a few months of hard work due to no back up.
Do you have any methods to protect against hackers?
Quote
0 #9 vivoslot 2022-07-28 18:42
It's actually a great and useful piece of info. I'm satisfied
that you just shared this useful info with us. Please keep us up to date like this.
Thanks for sharing.
Quote
0 #10 vivoslot 2022-07-29 14:03
I was extremely pleased to uncover this website. I need to to thank you for your time for
this particularly fantastic read!! I definitely
appreciated every bit of it and i also have you book marked to look
at new information on your site.
Quote
0 #11 Candelaria 2022-08-07 23:56
I constantly emailed this webpage post page to all my associates, for the reason that if like to read it after that my contacts will too.
Quote
0 #12 Darrel 2022-08-09 18:02
Greate article. Keep writing such kind of information on your blog.
Im really impressed by it.
Hey there, You have done a fantastic job.

I'll certainly digg it and in my opinion suggest to my friends.
I am confident they will be benefited from
this site.
Quote
0 #13 vivoslot 2022-08-16 01:50
Hey there! I'm at work browsing your blog from my new apple iphone!

Just wanted to say I love reading your blog and look forward to all your posts!

Carry on the outstanding work!
Quote
0 #14 vivoslot 2022-08-19 20:06
My developer is trying to persuade me to move to .net from PHP.
I have always disliked the idea because of the expenses.

But he's tryiong none the less. I've been using WordPress on a variety of websites for about
a year and am nervous about switching to another platform.
I have heard great things about blogengine.net.
Is there a way I can transfer all my wordpress content
into it? Any help would be greatly appreciated!
Quote
0 #15 vivoslot 2022-08-25 00:40
Thanks very nice blog!
Quote
0 #16 vivoslot 2022-08-25 06:16
Your method of telling the whole thing in this paragraph is actually
nice, all can without difficulty be aware of it, Thanks a lot.
Quote
0 #17 AlvenBiz 2022-09-03 05:46
Kudos! A good amount of advice!
custom writings how to teach essay writing
Quote
0 #18 college essay 2022-09-08 22:29
Hello! I know this is somewhat off topic but I was wondering which blog
platform are you using for this website? I'm getting tired of Wordpress because I've had issues with hackers and I'm looking at options for another platform.
I would be great if you could point me in the direction of a good platform.
Quote
0 #19 vivoslot 2022-09-13 22:53
Thanks for your marvelous posting! I quite enjoyed reading it, you are a great author.I will ensure that I bookmark your blog and may come back in the future.
I want to encourage one to continue your great writing, have a nice evening!
Quote
0 #20 liontoto 2022-09-30 01:07
If some one wishes to be updated with newest technologies then he must be visit this website and be up to date daily.
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