End-to-end configuration implementation task for Oracle Fusion cloud Absence Management.

Below are the details of the end-to-end configuration implementation task for Oracle Fusion cloud Absence Management.

 

Sno	Task Name	Functional/Technical
1	Absence Management Implementation Overview	Functional
2	Responsive User Experience Setup	Functional
3	Absence Lookups, Value Sets, and Flexfields	Functional
4	Formula Creation and Error Handling for Absence Management	Functional
5	Eligibility Profiles for Absence Plans	Functional
6	Rate Definitions for Absence Payments	Functional
7	Elements for Absence Management	Functional
8	Effective Dates in Absence Management	Functional
9	Accrual Absence Plans	Functional
10	Qualification Absence Plans	Functional
11	Compensatory Time	Functional
12	Donation	Functional
13	Absence Types, Reasons, and Categories	Functional
14	Absence Certifications	Functional
15	Absence Processes	Functional
16	Integrated Workbooks for Loading(HDL/HSDL) Absence Data	Technical
17	HCM BI Report for Absence Data	Technical
18	HCM OTBI for Absence Data	Technical
19	HCM Extracts for Absence Data	Technical

 

Task Expired for Checklist(Journeys) using SQL Query in Oracle Cloud HCM

The following SQL query will help with the task 'Expired for Checklist(Journeys)'  with Checklist completed for employees in Oracle Cloud HCM.

 SELECT DISTINCT
    PAPF.person_number,
    ACT.checklist_name,
    ATT.task_name,
    CASE
    WHEN AC.checklist_status = 'COM' THEN 'Completed'
    WHEN AC.checklist_status = 'INI' THEN 'Pending'
    ELSE ' '
    END AS CHECKLIST_STATUS,
    To_char(AC.allocation_date, 'mm/dd/yyyy') as "ALLOCATION_DATE_",
    To_char(AC.completion_date, 'mm/dd/yyyy') as "COMPLETION_DATE",
    AC.checklist_category,
    AC.checklist_id,
    AC.allocated_checklist_id,
    AC.initiator_person_id,
    AT.STATUS,
    AV1.DISPLAY_NAME,
    PE.EMAIL_ADDRESS
FROM
    per_allocated_checklists AC,
    per_allocated_checklists_tl ACT,
    per_allocated_tasks AT,
    per_allocated_tasks_tl ATT,
    per_assignment_secured_list_v AV,
    PER_PERSON_NAMES_F AV1,
    per_all_people_f PAPF,
    PER_EMAIL_ADDRESSES PE,
    PER_ALL_ASSIGNMENTS_M PAAM
WHERE 1 = 1
    AND AT.STATUS <> 'COM'
    AND AT.STATUS <> 'DEP'
    AND AT.STATUS <> 'INI'
    AND AT.STATUS <> 'INP'
    AND PAAM.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
    AND AC.allocated_checklist_id = ACT.allocated_checklist_id
    AND AT.allocated_checklist_id = AC.allocated_checklist_id
    AND ATT.allocated_task_id = AT.allocated_task_id
    AND AC.person_id = AV.person_id
    AND AV.person_id = AV1.person_id
    AND Trunc(sysdate) BETWEEN AV.effective_start_date AND AV.effective_end_date
    AND Trunc(sysdate) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
    AND AV.assignment_status_type IN ( 'ACTIVE', 'SUSPENDED' )
    AND AV.effective_latest_change = 'Y'
    AND ACT.checklist_name = 'ChecklistName'
    AND PAPF.person_id = AV.person_id
    AND PAPF.person_id = PE.person_id
    AND PE.EMAIL_TYPE = 'W1'
ORDER BY PAPF.person_number

Organization(Department) Tree hierarchy SQL Query in Oracle Cloud HCM

Below is the query for the Organization(Department) Tree hierarchy in Oracle Cloud HCM.

 ----------------

 WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
                   ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
                AND potnv.tree_code = 'Bristlecone Department Tree'
                AND potnv.tree_version_id = ftv.tree_version_id
                AND ftv.tree_code = potnv.tree_code
                AND ftv.status = 'ACTIVE'
                AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
               START WITH potnv.parent_organization_id IS NULL
             CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name l1
             ,level2.child_org_name l2
             ,level3.child_org_name l3
             ,level4.child_org_name l4
         FROM org_tree level1
             ,org_tree level2
             ,org_tree level3
             ,org_tree level4
             ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
         AND level2.child_org_id = level3.parent_org_id
         AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
         AND haouf.organization_id = level4.child_org_id
         AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
        
 select distinct
 (
    SELECT MEANING
    FROM HR_LOOKUPS
    WHERE LOOKUP_TYPE = 'SYSTEM_PERSON_TYPE'
    AND LOOKUP_CODE = PAAM.SYSTEM_PERSON_TYPE) as "Worker_Type"
  ,papf.PERSON_NUMBER "Employee ID"
,paam.Assignment_Number as "AssignmentNumber"
,ppnf.FULL_NAME as "Full_Name"
,TO_CHAR(ppos.DATE_START, 'YYYY-Mon-DD','NLS_DATE_LANGUAGE=AMERICAN') as "DateOfJoining"
,dt.l2 as "ParentDepartment"
,dt.l3 as "SubDepartment"
,HOU.NAME as "Department"
 from  PER_ALL_PEOPLE_F papf
 ,per_all_assignments_f paam
 ,PER_PERIODS_OF_SERVICE ppos
 ,HR_ALL_ORGANIZATION_UNITS HOU
 ,dept_tree dt
 ,PER_PERSON_NAMES_F ppnf
 where 1=1
 and ppos.person_id=papf.person_id
 AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
 and papf.person_id=paam.person_id
 and paam.PRIMARY_FLAG ='Y'
 and paam.ASSIGNMENT_TYPE in ('E','C')
 AND PAAM.ASSIGNMENT_STATUS_TYPE='ACTIVE'
 and paam.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
 and papf.person_id=ppnf.person_id
 and ppnf.name_type='GLOBAL'
 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
 and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
 and TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
 and dt.l4(+)=HOU.NAME
 ORDER BY papf.person_number

How many types of Absence Plans in Oracle Fusion Absence Module?

 

Types of Absence Plans

Create the following types of plans using the Absence Plans task in the Absences work area:

• Accrual

• Qualification

• No Entitlement

• Agreement

• Compensatory

• Donation

Accrual

Use this type to create absence plans that enable workers to accrue time for taking paid leave, such as a vacation plan. Configure rules that determine various aspects of leave time, such as:

• Length and type of the accrual term in which workers accrue time

• Maximum time that workers can accrue in a term

• Maximum time that workers can carry forward to the next term

Qualification

Use this type to create an absence plan where workers qualify for the plan and receive payments during the absence period. For example, use qualification plans for defining absence rules related to events, such as long term illness or maternity. Configure rules to determine the payment percentages that apply for specific periods during the absence, for specific workers.

No Entitlement

Create absence plans of this type to track unpaid absences without maintaining an accrual balance or providing leave entitlements, such as periodic accruals. Similar to an accrual plan, you can define the length and type of the plan term and determine when eligible workers can enroll in the plan. You can also use plans of this type in combination with a qualification plan. For example, use a no-entitlement plan if a worker isn't eligible for a standard maternity absence qualification plan.

Agreement

Create absence plans of this type in accordance with statutory rules for use under an agreement. For example, create a shared parental leave agreement for a worker and qualified parental partner to allocate paid or unpaid time off among parenting partners.

Compensatory

Create absence plans of this type to track earned compensatory time that can be used for time off. Compensatory time is offered as compensation for working outside of regular work schedules. Instead of paying overtime, an employer can create a policy to give paid time off.

Donation

Create absence plans of this type to track donated time that a worker receives. Workers can donate their accrual plan balance to a coworker who needs additional time off and is eligible to receive the donated time.