SQL query will help the decide which employees to hire tomorrow in Oracle Fusion Cloud HCM

 The following SQL query will help the organization decide which employees to hire tomorrow. It will also prepare a report and scheduled as per businesses requirement in Oracle Fusion Cloud HCM

 

SELECT DISTINCT
        PAPF.PERSON_NUMBER as "EmployeeNumber",
         TO_CHAR (PAAM.PROJECTED_START_DATE,'DD-Mon-YYYY', 'nls_date_language=American') as "HireDate",
        PAAM.PROJECTED_START_DATE,
        PPNF.FIRST_NAME as "FirstName",
        PPNF.LAST_NAME as "LastName",
        PPNF.DISPLAY_NAME,
        PPNF.FIRST_NAME || (CASE WHEN PPNF.MIDDLE_NAMES IS NOT NULL THEN ' '||PPNF.MIDDLE_NAMES END) || ' '||PPNF.LAST_NAME as "FullName",
        PLDFT.LOCATION_NAME as "LocationName",
        PAPF_MGR.PERSON_NUMBER as "ManagerEmpNo",
        PPNF_MGR.DISPLAY_NAME as "ManagerName",
        HAPFT_MGR.NAME as "ManagerPostion",
        PJFT_MGR.NAME as "ManagerJob",
        PEA_MGR_W.EMAIL_ADDRESS "ManagerEmail",
        HL_WORK_CAT.MEANING as "WorkerCategory",
        HL_EMP_CAT.MEANING as "EmployeeCategory",
        (SELECT PEA.EMAIL_ADDRESS,
                   FROM PER_EMAIL_ADDRESSES PEA
                   WHERE EMAIL_TYPE ='W1'
                   AND PEA.PERSON_ID =PAPF.PERSON_ID AND Rownum=1 ) as "Work_Email"
FROM
        PER_ALL_ASSIGNMENTS_M PAAM,
        PER_PERIODS_OF_SERVICE PPOS,
        PER_ALL_PEOPLE_F PAPF,
        PER_PERSON_NAMES_F PPNF,
        --------------Location starts------------------
        PER_LOCATION_DETAILS_F PLDF,
        PER_LOCATION_DETAILS_F_TL PLDFT,
        -------------Manager starts--------------------
        PER_ASSIGNMENT_SUPERVISORS_F PASF,
        PER_ALL_PEOPLE_F PAPF_MGR,
        PER_PERSON_NAMES_F PPNF_MGR,
        PER_ALL_ASSIGNMENTS_M PAAM_MGR,
        HR_ALL_POSITIONS_F_TL HAPFT_MGR,
        PER_JOBS_F_TL PJFT_MGR,
        -----------------Manager emails--------
        PER_EMAIL_ADDRESSES PEA_MGR_W,
        ------------work category lookup--------
        HCM_LOOKUPS HL_WORK_CAT,
        ------------Employee category lookup--------
        HCM_LOOKUPS HL_EMP_CAT,
        ------------Phone category lookup--------
        PER_PHONES PP
WHERE    1=1
        AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
        AND PAAM.ASSIGNMENT_TYPE IN ('P')
        AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PAPF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PPNF.PERSON_ID
        AND PPNF.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
        -------------location starts-------------------
        AND PAAM.LOCATION_ID = PLDF.LOCATION_ID(+)
        AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
        AND PLDFT.LANGUAGE(+) = USERENV('LANG')
        AND TRUNC(SYSDATE) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)
        ----------------Manager starts-----------------
        AND PAAM.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID(+)
        AND PASF.MANAGER_TYPE(+) = 'LINE_MANAGER'
        AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)
        AND PASF.MANAGER_ID = PAPF_MGR.PERSON_ID(+)
        AND PASF.MANAGER_ASSIGNMENT_ID = PAAM_MGR.ASSIGNMENT_ID(+)
        AND PAAM_MGR.ASSIGNMENT_TYPE(+) = 'E'
        AND PAAM_MGR.EFFECTIVE_LATEST_CHANGE(+) = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM_MGR.EFFECTIVE_START_DATE(+) AND PAAM_MGR.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PAPF_MGR.EFFECTIVE_START_DATE(+) AND PAPF_MGR.EFFECTIVE_END_DATE(+)
        AND PAPF_MGR.PERSON_ID = PPNF_MGR.PERSON_ID(+)
        AND PPNF_MGR.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF_MGR.EFFECTIVE_START_DATE(+) AND PPNF_MGR.EFFECTIVE_END_DATE(+)
        AND PAAM_MGR.POSITION_ID = HAPFT_MGR.POSITION_ID(+)
        AND TRUNC(SYSDATE) BETWEEN HAPFT_MGR.EFFECTIVE_START_DATE(+) AND HAPFT_MGR.EFFECTIVE_END_DATE(+)
        AND HAPFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND PAAM_MGR.JOB_ID = PJFT_MGR.JOB_ID(+)
        AND PJFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND TRUNC(SYSDATE) BETWEEN PJFT_MGR.EFFECTIVE_START_DATE(+) AND PJFT_MGR.EFFECTIVE_END_DATE(+)
        -----------------Manager emails --------
        AND PAAM_MGR.PERSON_ID = PEA_MGR_W.PERSON_ID(+)
        AND PEA_MGR_W.EMAIL_TYPE(+) = 'W1'
        ------------work category lookup starts--------
        AND HL_WORK_CAT.LOOKUP_TYPE(+) = 'EMPLOYEE_CATG'
        AND HL_WORK_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYEE_CATEGORY
        ------------work category lookup ends----------
        AND HL_EMP_CAT.LOOKUP_TYPE(+) = 'EMP_CAT'
        AND HL_EMP_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYMENT_CATEGORY
        AND PP.PERSON_ID(+) = PAAM.PERSON_ID
        AND PP.PHONE_TYPE(+) = 'HM'
        AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE+1)
        ORDER BY PAAM.PROJECTED_START_DATE

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

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,
'sathwikhcm@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)
)