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.