Oracle Cloud HCM Work Schedules - Query

 

Oracle Cloud HCM Work Schedules - Query

 

Availability details of Workers for a period are defined using Work Schedules.

Oracle Fusion Cloud HCM applications support only time and elapsed work schedules.

Time

A time work schedule has a fixed workday pattern and anyone assigned the schedule is available for specific hours each day. For example, you want an 8-hour schedule for 5 days of the week. You create a time work schedule that starts at 8:00a and ends at 5:00p, Monday through Friday.

Elapsed

An elapsed work schedule doesn't have fixed start or end times for anyone assigned the schedule. Instead, they're available for a specific number of hours each day--a specific duration. For example, everyone works 8 hours a day, Monday through Friday. Some people might start work at 9:00a and others at 11:00a. Some people might work in 2-hour bands with an hour or more of nonwork time between. And some people might work during the night. It doesn't matter as long as they work their assigned duration.

Exception Options in Work Schedules

When you create a work schedule, you can include exceptions, such as public holidays or training sessions. You then use these exceptions to identify people's availability to work.

Calendar Event

A calendar event is an exception for a single event on 1 or more consecutive days, such as a public holiday or training event.

Calendar Event Category

A calendar event category is an exception for all calendar events that make up the event category. For example, the events New Year's Day, Good Friday, and Easter Monday make up the category UK Public Holidays.

Resource Exception

A resource exception is an exception for everyone assigned the work schedule. For example, everyone assigned the Night Shift schedule get scheduled to attend a training event, so they aren't available to do their regular work.

People can see the Work period exceptions for themselves and their team on the Time and Absences work area calendar. They need to select either or both of the Employment schedule and My schedule display options. They can't see any off period, or nonwork, exceptions.

Primary Work Schedule

 The schedule used to identify an individual's work availability is called Primary Work Schedule.

We manage work schedules using the Work Schedule Assignment task in the Person Management work area.

The primary work schedule links to one of these levels. Schedules that the process builds from the work schedule also show assigned calendar events and resource exceptions, and applicable absences.

  1. Primary assignment of the person
  2. Position
  3. Job
  4. Department
  5. Location
  6. Legal Employer
  7. Enterprise

 Query

Here is the Query listing the Work schedule of a Person whichever level it is marked against.

       SELECT DISTINCT
 pam.assignment_number,
case when rex.schedule_name IS NOT NULL then rex.schedule_name
 else
   case when asg.schedule_name IS NOT NULL then asg.schedule_name
    else 
 case when dep.schedule_name IS NOT NULL then dep.schedule_name
   else 
    case when le.schedule_name IS NOT NULL then le.schedule_name 
    else 'NA'
   end
 end 
   end  
 end as schedule 
 FROM 
   per_all_people_f ppf,
   per_periods_of_service ppp,
   per_all_assignments_m pam,  
   (SELECT paam.assignment_id, papf.person_id,
 sst.schedule_name
 FROM 
 PER_ALL_PEOPLE_F papf,
 PER_PERIODS_OF_SERVICE ppp,
 PER_ALL_ASSIGNMENTS_M paam,
 PER_SCHEDULE_ASSIGNMENTS psa,
 PER_SCHEDULE_EXCEPTIONS pse,
 PER_RESOURCE_EXCEPTIONS pre,
 ZMM_SR_SCHEDULE_PATTERNS ssp,
 ZMM_SR_SCHEDULES_B ssb,
 ZMM_SR_SCHEDULES_TL sst
 WHERE  
 pre.exception_id(+) = pse.exception_id
 --and ssp.pattern_id = spd.pattern_id(+)
 and psa.schedule_id  = ssp.schedule_id(+)
 and ssb.schedule_id = sst.schedule_id(+)
 and ssb.schedule_id (+) = psa.schedule_id 
 and psa.resource_id(+) = paam.assignment_id 
 and pse.SCHEDULE_ASSIGNMENT_ID(+) = psa.schedule_assignment_id 
 AND paam.assignment_type IN('E','C')
 --and paam.assignment_status_type = 'ACTIVE'
 AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
 AND PPP.PERIOD_TYPE IN ('E','C')
 AND ppp.person_id = papf.person_id
 --AND NVL(psa.primary_flag,'Y') = 'Y'
 AND ppp.LEGAL_ENTITY_ID = paam.LEGAL_ENTITY_ID
 AND ppp.pERIOD_OF_SERVICE_ID = paam.pERIOD_OF_SERVICE_ID
 AND SYSDATE between paam.effective_start_date and paam.effective_end_date
 AND SYSDATE BETWEEN papf.effective_start_date and papf.effective_end_date
 and paam.person_id = papf.person_id
 AND (
 (
 paam.ASSIGNMENT_STATUS_TYPE = 'INACTIVE'
 AND NOT EXISTS (SELECT 'X'
 FROM PER_ALL_ASSIGNMENTS_M A1
 WHERE A1.EFFECTIVE_LATEST_CHANGE = 'Y'
 AND A1.PERSON_ID = paam.PERSON_ID
 AND SYSDATE BETWEEN A1.EFFECTIVE_START_DATE AND A1.EFFECTIVE_END_DATE
 AND A1.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
 )
 AND ppp.ACTUAL_TERMINATION_DATE = 
                                                (     SELECT MAX(S1.ACTUAL_TERMINATION_DATE)
 FROM PER_PERIODS_OF_SERVICE S1
 WHERE S1.PERSON_ID = paam.PERSON_ID
 )
 )
 OR paam.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
 )
 AND NVL(psa.start_date,SYSDATE) = NVL((select max(start_date) from  PER_SCHEDULE_ASSIGNMENTS a where a.resource_id = paam.assignment_id ),SYSDATE)
 ) rex,
(select paam_a.assignment_id,zsct.schedule_name,psa.resource_type
 from per_schedule_assignments psa
 ,per_all_assignments_m paam_a
 ,zmm_sr_schedules_tl zsct
 where psa.resource_type = 'ASSIGN'
 and sysdate between paam_a.effective_start_date and paam_a.effective_end_date
 and psa.resource_id = paam_a.assignment_id
 and zsct.schedule_id = psa.schedule_id
 AND (TO_CHAR(TRUNC(psa.end_date),'YYYY/MM/DD') <> '4712/12/31' OR TRUNC(psa.end_date) > SYSDATE)
AND NVL(psa.start_date,SYSDATE) = 
  NVL((select max(start_date) from  PER_SCHEDULE_ASSIGNMENTS a where a.resource_id = paam_a.assignment_id ),SYSDATE)
 ) asg,
 (select haou.organization_id,zsct.schedule_name,psa.resource_type
 from per_schedule_assignments psa
 ,hr_all_organization_units haou
 ,zmm_sr_schedules_tl zsct
 where psa.resource_type = 'DEP'
 and psa.resource_id = haou.organization_id
 and zsct.schedule_id = psa.schedule_id
 ) dep,
(select haou.organization_id,zsct.schedule_name,psa.resource_type
 from per_schedule_assignments psa
 ,hr_all_organization_units haou
 ,zmm_sr_schedules_tl zsct
 where psa.resource_type = 'LEGALEMP'
 and psa.resource_id = haou.organization_id
 and zsct.schedule_id = psa.schedule_id
 ) le
 where 1=1
 and pam.assignment_status_type = 'ACTIVE'
 and pam.effective_latest_change = 'Y'       
 and pam.assignment_type = 'E'
 and sysdate between pam.effective_start_date and pam.effective_end_date
 and sysdate between ppf.effective_start_date and ppf.effective_end_date
 and ppp.period_type = 'E'
 and ppp.period_of_service_id = pam.period_of_service_id 
 and ppp.legal_entity_id = pam.legal_entity_id
 and ppp.person_id = ppf.person_id
 and rex.person_id(+) = ppf.person_id
 and asg.assignment_id(+) = pam.assignment_id
 and le.organization_id(+) = pam.legal_entity_id
 and dep.organization_id(+) = pam.organization_id
 and pam.person_id = ppf.person_id  
 and ppf.person_number IN ('12345')


Comments

Post a Comment

Popular posts from this blog

Oracle Cloud HCM Open Ended Absences Update

  Oracle Cloud HCM Open Ended Absences       In Oracle Cloud HCM, when there exist open ended absences like Maternity and Sickness and Employee is terminated, it is seen that there is no end date updated to these open absences as of Termination Date. This leads to ambiguity in reporting and payroll To address this issue, worked on an automation process which will pick such records and put an end date same as the termination date.   Design Steps   1.       Build a BIP report to list the Open ended absences for Terminated employees 2.      Write the Bursting definition to feed the BIP report output to Extract 3.      Desing the ETF template to build the HDL Template for End dating Open Absence 4.      Design a HCM Extract that will take the output fed from BIP Report and Auto Loads as   HDL process 5.   ...
Read more

Oracle HCM Absence Orphan Records

Image
Sometimes We come across a less known Error while submitting an Absence in Oracle Cloud HCM with an Error Code ANC-3405194 Likely caused when  Update of Accrual plan is done after an accrual process has already run till a date in future. OR  Entering accrual via HDL in a previous plan period and missing to run calculate accrual. This is Resolved when we Delete the Orphaned records found by Below Query Query to identify the Orphan Absence Records in the System SELECT  per_accrual_entry_dtl_id,         ppl.person_number       ,         ass.assignment_number   ,         plans_tl.NAME           ,         TYPE                    ,         procd_date FROM    fusion.anc_per_acrl_entry_dtls acrl_dtls ,         fusion.per_all_people_f ppl  ...
Read more
Image
           Deploying an AI agent on Redwood Page                                    1.     N avigate to the AI Agent Studio from the Tools Menu             2.    Select any available delivered AI agent and Copy it to be used by    your business.                         3.     Run it to see all the Profiles and Config are good.         4.     Publish the AI Agent              5.       Navigate to My Client Groups -> Guided Journeys      6.       Create New Journey      7.       Create Draf...
Read more