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:

1. 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):

1. If you are Building Finance reports use : ApplicationDB_FSCM

2. If you are Building HCM reports use : ApplicationDB_HCM

3. 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:

Data Model is created. Now we need to check the data retrieved.

. Create List of Values for Parameters.

List OF VALUES

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.

Ashish Harbhajanka

Add comment