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)
)