BusinessRequirement
One of the most common requirements for any Oracle Fusion Application implementation project involving Fusion Absence Management Module is the Absence Accrual Entry Details Report. Simply explained, this may be considered as a medium via which we can have a consolidated view of the different accrual plans under which an individual is enrolled along with the corresponding accrual balance , reasons for absence adjustments ( additions / deductions based upon whether any additional balance were awarded or leaves taken respectively ) and their balance as on a specific date.
This report has multiple parameters including Legal Employer Name, Person Name, Absence Plan Name, Date….to name a few which provides various options to the end user to generate different output as per his/her specific needs.
So let us get started with the same.
Steps
There are few common steps involved they are:
-
Create a SQL Query which comprises of DB Tables (that exist in Fusion Schema).
We have a SQL Query which is as below:
select A.person_number,
A.full_name,
A.legal_employer,
A.business_unit,
A.department,
A.absence_plan_name,
A.adjustment_type,
A.adjustment_reason,
A.value,
A.procd_date accrual_entry_date
from
(
select apaed.per_accrual_entry_dtl_id,
apaed.per_accrual_entry_id,
apaed.enterprise_id,
apaed.value,
apaed.type,
apaed.created_by,
apaed.creation_date,
apaed.last_updated_by,
apaed.last_update_date,
apaed.last_update_login,
apaed.person_id,
apaed.pl_id,
apaed.procd_date,
apaed.per_event_id,
apaed.legal_employer_id,
apaed.assignment_id,
apaed.per_absence_entry_id,
apaed.per_plan_enrt_id,
apaed.work_term_asg_id,
DECODE(apaed.type,'ADJOTH',ADD_MONTHS(procd_date,6),NULL) proposed_expiration_date,
papf.person_number,
ppnf.full_name,
paam.organization_id,
dept.name department,
paam.legal_entity_id,
legal_employer.classification_code legal_emp_classification_code,
legal_employer.name legal_employer,
paam.business_unit_id,
business_unit.classification_code bu_classification_code,
business_unit.name business_unit,
absence_plan.name absence_plan_name,
flvt.meaning adjustment_type,
adj_reason.meaning adjustment_reason
FROM anc_per_acrl_entry_dtls apaed
JOIN fnd_lookup_values_tl flvt
ON (flvt.lookup_type = 'ANC_ACCRUAL_ENTRY_TYPE'
AND flvt.lookup_code = apaed.type
AND flvt.language = 'US')
JOIN per_all_people_f papf
ON (apaed.person_id = papf.person_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date)
JOIN per_person_names_f ppnf
ON (ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = apaed.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date)
JOIN per_all_assignments_m paam
ON (paam.assignment_id = apaed.assignment_id
AND paam.person_id = apaed.person_id
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) between paam.effective_start_date and paam.effective_end_date)
LEFT OUTER JOIN
(SELECTflvt1.lookup_code,
flvt1.meaning
FROMfnd_lookup_values_tl flvt1
WHEREflvt1.lookup_type = 'ANC_ABS_PLAN_OTHER_REASONS'
AND flvt1.language = 'US'
) adj_reason
ON (apaed.adjustment_reason = adj_reason.lookup_code)
LEFT OUTER JOIN
( SELECT hauft.organization_id,
hauft.NAME
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'
AND TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
) dept
ON (paam.organization_id = dept.organization_id)
LEFT OUTER JOIN
(SELECT hauft.organization_id,
hauft.NAME,
houcf.classification_code
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
) legal_employer
ON (paam.legal_entity_id = legal_employer.organization_id)
LEFT OUTER JOIN
(SELECT hauft.organization_id business_unit_id,
hauft.NAME,
houcf.classification_code
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
) business_unit
ON (paam.business_unit_id = business_unit.business_unit_id)
LEFT OUTER JOIN
(SELECT aapf.absence_plan_id,
aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = 'A' -- added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = 'US'
) absence_plan
ON apaed.pl_id = absence_plan.absence_plan_id
where pl_id = absence_plan.absence_plan_id
and apaed.value <> 0
order by apaed.person_id,apaed.procd_date asc
) A
where person_number = nvl(:pPersonNumber,person_number)
and legal_employer = nvl(:pLegalEmployer,legal_employer)
and business_unit = nvl(:pBusinessUnit,business_unit)
and procd_date >= nvl(:pCalculationDate,procd_date)
and department = nvl(:pDepartment,department)
and full_name = nvl(:pPersonName,full_name)
and absence_plan_name = nvl(:pAbsencePlanName,absence_plan_name)
Navigate :
Under published Reporting-->Data Model
Create a New Data Set (of SQL Query type) as shown in Screenshot below:
Give a Name to Data Set (for this example say PersonAbsenceAccrualEntryDetails_ds):
You would need to take special care while selecting Data Source (Logic Below):
-
If you are Building Finance reports use : ApplicationDB_FSCM
-
If you are Building HCM reports use : ApplicationDB_HCM
-
If you are Building CRM Reports use : ApplicationDB_CRM
For this example we use ApplicationDB_HCM
Depending on the number of Parameters(Bind Variables) used a Popup window will appear :
Click OK. Give a Name to the Parameters as given below
Parameter Details:
Parameter Name |
Data Type |
Default Value |
Parameter Type |
Row Placement |
Display Label |
pLegalEmployer |
String |
Menu ( List of Values Legal Employer) |
LegalEmployer** |
||
pBusinessUnit |
String |
Menu ( List of Values Business Unit) |
BusinessUnit** |
||
pDepartment |
String |
Menu ( List of Values Department) |
Department** |
||
pAbsencePlanName |
String |
Menu ( List of Values Absence Plan Name) |
AbsencePlanName** |
||
pCalculationDate |
Date |
Text |
CalculationDate** |
||
pPersonName |
String |
Menu ( List of Values Person Name) |
PersonName** |
||
pPersonNumber |
String |
Text |
PersonNumber** |
Data Model is created. Now we need to check the data retrieved.
. Create List of Values for Parameters.
List OF VALUES
Lov Name |
Type |
Data Source |
SQL Query |
Associated with Parameter |
Legal Employer |
SQL Query |
ApplicationDB_HCM |
Refer Legal Employer LOV SQL in LOV SQL Section |
pLegalEmployer |
Business Unit |
SQL Query |
ApplicationDB_HCM |
Refer Business Unit LOV SQL in LOV SQL Section |
pBusinessUnit |
Department |
SQL Query |
ApplicationDB_HCM |
Refer Department LOV SQL in LOV SQL Section |
pDepartment |
Absence Plan Name |
SQL Query |
pLegalEmployer |
Refer Absence Plan Name LOV SQL in LOV SQL Section |
pAbsencePlanName |
Person Name |
SQL Query |
pLegalEmployer |
Refer Person Name LOV SQL in LOV SQL Section |
pPersonName |
LOV SQL Section
This Section shows all the SQL which are used for LOV Creation.
Legal Employer LOV SQL
SELECT hauft.NAME
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND EXISTS
(SELECT 1
FROM anc_per_acrl_entry_dtls apaed,
per_all_assignments_m paam
WHERE apaed.assignment_id = paam.assignment_id
AND paam.primary_flag = 'Y'
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.legal_entity_id= haouf.organization_id
)
Business Unit LOV SQL
SELECT hauft.NAME
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND EXISTS
(SELECT 1
FROM anc_per_acrl_entry_dtls apaed,
per_all_assignments_m paam
WHERE apaed.assignment_id = paam.assignment_id
AND paam.primary_flag = 'Y'
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.business_unit_id= haouf.organization_id
)
Department LOV SQL
SELECT hauft.NAME
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND EXISTS
(SELECT 1
FROM anc_per_acrl_entry_dtls apaed,
per_all_assignments_m paam
WHERE apaed.assignment_id = paam.assignment_id
AND paam.primary_flag = 'Y'
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.organization_id = haouf.organization_id
)
Absence Plan LOV SQL
SELECT aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = 'A' -- added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = 'US'
AND EXISTS
(SELECT 1
From anc_per_acrl_entry_dtls apaed
where apaed.pl_id = aapf.absence_plan_id)
Person Name LOV SQL
select full_name
from per_person_names_f
where name_type = 'GLOBAL'
and trunc(sysdate) between effective_start_date and effective_end_date
and EXISTS
(select 1
from anc_per_acrl_entry_dtls apaed
where apaed.person_id = person_id
)
View Data:
Click on ‘Save As Sample Data’:
Create Report
Click on Create Report
Click Next and Follow Train Stops:
Create Table
Drag and Drop fields and the Final Report output will be shown.
Comments
how ϲan і get abilify no prescription [abilify4ɑll.to p: https://abilify4all.top]'m hoping to start
mү own site soоn Ƅut I'm a little lost on evеrything.
WoulԀ you advise starting ԝith a free platform like Wordpress оr go foг a paid option? Thегe ɑre ѕo many
options оut there that I'm сompletely confused ..
Ꭺny recommendations ? Tһanks!
comparison of latest and preceding technologies, it's awesome article.
What host are you the usage of? Can I get your associate hyperlink in your
host? I want my site loaded up as quickly as yours lol
writing writing is аlso a excitement, іf yoս
Ьe familiar witһ then you can wгite ⲟr else it is difficult tⲟ write.
group? There's a lot of people that I think would really appreciate your content.
Please let me know. Thank you
data regarding my presentation subject, which i am going
to convey in institution of higher education.
Do you have any tips on how to get listed in Yahoo
News? I've been trying for a while but I never seem to get there!
Appreciate it
I'd be very grateful if you could elaborate a little bit
further. Kudos!
get advantage from it I am sure.
I surprise һow to buy cheap zoloft pill (zoloft2սs.top: https://zoloft2us.top)
a lot effort yоu set to creɑte one of thesе fantastic informative website.
site, how can i subscribe for a blog website? The account helped
me a acceptable deal. I had been a little bit acquainted
of this your broadcast provided bright clear idea
Grеat site, continue the ɡood wօrk!
(zoloft2ɑll.top : https://zoloft2all.top/) havе got much clеar idea on tһe topic of from this paragraph.
ϲame to “return tһe favor”.I am attempting to fіnd things to enhance my web site!І suppose its ok to use some of yoᥙr ideas!!
Mʏ web blog - cost propecia ρrice: https://escueladehumanidades.tec.mx/deh/it-facet-excessive-where-can-i-get-propecia-without-rx-rarely-seen-however-why-its-needed
also ցo ѡһere to buy cheap levitra ᴡithout dr prescription (levitra24ҳ7noѡ .top: https://levitra24x7now.top) sеe this web site on regular basis tο tаke updated fгom latеst reports.
honest but your sites really nice, keep it up! I'll go ahead and bookmark your
site to come back later on. Many thanks
to do blogging and site-building.
prednisone medication
lioresal without rx
motrin
Everything news about medicine. Get now.
Take care!
It's pretty worth enough for me. In my view, if all site
owners and bloggers made good content as you did, the web will be much
more useful than ever before.|
I could not refrain from commenting.
Very well written!|
I'll right away snatch your rss feed as I can't in finding your e-mail subscription link or e-newsletter service.
Do you've any? Please allow me realize so that I may just subscribe.
Thanks. |
It is perfect time to make some plans for the future and it is time to be happy.
I have read this post and if I could I wish to suggest you few
interesting things or advice. Perhaps you could write next articles referring to this article.
reading it, you may be a great author. I will remember to bookmark your blog and will come back at some point.
I want to encourage that you continue your great writing,
have a nice weekend!
you have to manually code with HTML. I'm starting a blog soon but have no coding experience so I wanted to get advice from someone with experience.
Any help would be enormously appreciated!
zithromax
cheap propecia
propecia tablet
Everything information about medicine. Get information here.
It is pretty worth enough for me. In my opinion, if all webmasters
and bloggers made good content as you did, the net will
be a lot more useful than ever before.|
I couldn't resist commenting.
Well written!|
I will immediately clutch your rss feed as I can't find your email subscription hyperlink or newsletter service.
Do you have any? Kindly permit me recognise in order that I may just subscribe.
Thanks. |
It's perfect time to make some plans for the future and
it is time to be happy. I have read this post and if I
could I wish to suggest you some interesting things or advice.
Perhaps you could write next articles referring to this article.
it helped me out a lot. I hope to give something back and
aid others like you aided me.
sector don't notice this. You should proceed your
writing. I am confident, you have a great readers' base already!
you make blogging look easy. The overall look
of your site is fantastic, as well as the content!
The ԝorld hopes for even mⲟre passionate writers like you wh᧐ aren't afraid tο say how they ƅelieve.
Ꭺll the tіme follow yoᥙr heart.
mу blog; cost generic propecia ѡithout prescription, dasan.tium.ⅽo.k r: https://dasan.tium.co.kr/yc5/bbs/board.php?bo_table=free&wr_id=108387,
this site and now tһis tіme Ӏ am visiting thіs website and reading ᴠery informative posts heгe.
my blog post propecia sale (propecia4ᥙs.to p: https://propecia4us.top)
uptown pokies casino aussie
your post. Tһey're really convincing and can сertainly work.
Still, thе posts aгe too quick fօr beginners. Μay ϳust you plеase lengthen thеm ɑ lіttle from subsequent tіmе?
Thanks for the post.
My web blog: get generic propecia: https://propecia4us.top
RSS feed for comments to this post