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.

Welcome Onboard SQL Query with Brusting in Oracle Cloud HCM

 Below is the SQL query with Brusting  for the Welcome Onboard to Company in Oracle Cloud HCM.

Step1-->SQL Query

SELECT
PERSON_NUMBER KEY,
PAPF.PERSON_NUMBER,
PPNF.DISPLAY_NAME,
PEA.EMAIL_ADDRESS ,
TO_CHAR(PAAM.PROJECTED_START_DATE,'DD-MM-YYYY') DOJ
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = ('P')
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PEA.PERSON_ID = PAPF.PERSON_ID
AND EMAIL_TYPE ='H1'
AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE)

Step2--> Brusting Query.

SELECT
KEY KEY,
'Welcome'  TEMPLATE,
'en-us' LOCALE,
'HTML' OUTPUT_FORMAT,   
'Notification' OUTPUT_NAME,          
'EMAIL' DEL_CHANNEL,
EMAIL_ADDRESS PARAMETER1,
'sathwikit@gmail.com' PARAMETER2,
'no-reply@oracle.com' PARAMETER3,
'Welcome to SIT Solutions !' PARAMETER4,
'' PARAMETER5,  
'TRUE' PARAMETER6

FROM(
SELECT
PERSON_NUMBER KEY,
PAPF.PERSON_NUMBER,
PPNF.DISPLAY_NAME,
PEA.EMAIL_ADDRESS ,
TO_CHAR(PAAM.PROJECTED_START_DATE,'DD-MM-YYYY') DOJ
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = ('P')
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PEA.PERSON_ID = PAPF.PERSON_ID
AND EMAIL_TYPE ='H1'
AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE)
)

Difference between UPDATE and CORRECT modes in Oracle Fusion Cloud

Difference between 'UPDATE’ and 'CORRECT' modes in the Oracle Fusion Application.

Hi Folks,

We are having two types of modes like Correct and Update modes in Oracle Cloud Fusion Application. If you are new to Oracle Cloud Fusion Application Always chose 'Update' while edit the existing record in Fusion cloud screens and might be confused with 'UPDATE' and 'CORRECT' mode Actions. Let's see this with simple example in one Business Object.

UPDATE Mode is for updating the existing record. We will go for 'UPDATE' Mode when the particular record will have update with effective dates change day, Prior to that, that particular record holds the old value in back end tables as per Business Object.

For this Example, we will take 'Job’ object. Create new Job called 'Consultant ' with effective start date as “01/01/1951”

Let's assume, Job Function data is changed blank to 'Administrative' effective 01/01/2023. We need to update the same data in the application. In this case, We will go for 'Update' mode option effective date from 01/01/2023 and click on the submit button.

After the change, The Job Function changed to the 'Administrative' will showing blank on or after effective 01/01/1951

If you change the effective Start date before 01/01/2023, It will show old values

Let's look at the Table, there will be two version of records for the same Job Object with effective start Date and end date.

FYI: SQL Queries to get the table values as per below

SELECT JOB_ID,

to_char(EFFECTIVE_START_DATE,'YYYY/MM/DD') EFFECTIVE_START_DATE,

to_char(EFFECTIVE_END_DATE,'YYYY/MM/DD') EFFECTIVE_END_DATE,

JOB_FUNCTION_CODE

FROM PER_JOBS_F WHERE JOB_CODE='SN_CON'

CORRECT mode is something it was wrong data have entered the data in the application and need to be corrected in the system. Let's say, Job name 'Senior Payroll Consultant', it was wrong mentioned 'Senior Consultant' in the system effective 01/01/1951. Now you need to correct that to 'Senior Payroll Consultant'.

Note : Correct mode option Will not allow you to Correct the record effective after the creation date. For example, with above record created on 01/01/1951, you must chose effective change date as same or before the 01/01/1951

Incorrect Location 'Senior Consultant' created effective 01/01/1951

click Edit button-> Correct mode-> Effective ‘01/01/1951' System must throw the warning.

Change the effective date to 01/01/1951 with Job name as 'Senior Payroll Consultant' and click on the Save button.

Also, Let's look at the data using SQL table, there will no date effective row will be created for the 'Correct' mode.

Note : Object version column tells that this 'Location' record was modified from the first version

Hope this article will clarify the difference between 'UPDATE' and 'CORRECT' modes options in the Oracle Applications..

Goal and Goal Plan Details SQL query in Oracle Fusion Cloud HCM

Select 

distinct hg.GOAL_NAME

,hg.DESCRIPTION as Goal_DESCRIPTION

,hg.PUBLISH_DATE

,hg.GOAL_TYPE_CODE

,hgpl.GOAL_PLAN_NAME 

,hgpl.DESCRIPTION as GoalPlan_DESCRIPTION

,to_char(hg.START_DATE,'yyyy/mm/dd') as Goal_START_DATE

,to_char(hg.TARGET_COMPLETION_DATE,'yyyy/mm/dd') TARGET_COMPLETION_DATE

,hg.SUCCESS_CRITERIA

,hg.PERCENT_COMPLETE_CODE

,hg.STATUS_CODE

,hg.APPROVAL_STATUS_CODE

,to_char(hg.ACTUAL_COMPLETION_DATE,'yyyy/mm/dd') ACTUAL_COMPLETION_DATE

,hg.GOAL_VERSION_TYPE_CODE --,to_char(hg.VERSION_DATE,'yyyy/mm/dd') VERSION_DATE

,hg.PRIORITY_CODE GOALPRIORITY_CODE

,hg.COMMENTS

,hg.CREATED_BY GOAL_CREATED_BY

,hg.GOAL_URL

,hg.LEVEL_CODE

,hg.LEVEL_MEANING

,hg.CATEGORY_CODE

,hg.MEASURE_TYPE_CODE

,hg.MEASURE_NAME

,hg.TARGET_VALUE

,hg.TARGET_TYPE

,hg.UOM_CODE

,hg.ACTUAL_VALUE

,hg.GOAL_ACCESS_LEVEL_CODE

,hgpg.WEIGHTING

,hgpg.PRIORITY_CODE PLANPRIORITY_CODE

 from

 HRG_GOALS hg,

 HRG_GOAL_PLAN_GOALS hgpg,

 HRG_GOAL_PLANS_TL hgpl

 where hgpg.GOAL_ID=HG.GOAL_ID

 and hg.GOAL_VERSION_TYPE_CODE='ACTIVE'

 and hgpg.GOAL_PLAN_ID=hgpl.GOAL_PLAN_ID

 order by hg.GOAL_NAME