Skip to main content

Oracle Cloud HCM - Sample Absence Validation Fast Formula

Today we are going to see Sample Absence Validation Fast Formula

/* FORMULA TYPE: Global Absence Entry Validation */

DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_TOTALDURATION IS 0
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '

INPUTS ARE IV_END_DATE (date), IV_START_DATE (date), IV_TOTALDURATION

l_eff_dt = get_context(EFFECTIVE_DATE,'0001/01/01 00:00:00'(date))
l_system_year = to_number(to_char(l_eff_dt,'yyyy'))
l_absence_start_year = to_number(to_char(IV_START_DATE,'yyyy'))
l_absence_end_year = to_number(to_char(IV_END_DATE,'yyyy'))

/* START - Next Calendar Year Leaves needs to be applied separately */
if (ANC_ABS_TYP_NAME = 'Casual Leave') then
(
if (l_absence_start_year = l_system_year and l_absence_end_year = l_system_year)
then (valid = 'Y')
else
(
valid = 'N'
error_message = 'Next Calendar Year Leaves needs to be applied separately'
return valid,error_message
)
)
/* END - Next Calendar Year Leaves needs to be applied separately */

/* START - Leave Duration can be either Half Day or multiples of full days */

if (ANC_ABS_TYP_NAME = 'Casual Leave') then
(
 if (MOD(IV_TOTALDURATION,0.5) <> 0) then
  (
   valid = 'N'
   error_message = 'Leave Duration can be either Half Day or multiples of full days'
   return valid,error_message
  )
)
/* END - Leave Duration can be either Half Day or multiples of full days */

/* More than 1 day of Casual Leave cannot be applied on Continuous Days */

 if (ANC_ABS_TYP_NAME = 'Casual Leave') then
 (
  if (IV_TOTALDURATION > 1) then
  (
     valid = 'N'
     error_message = 'XX_ERROR_MSG'
     return valid,error_message 
   )
  if (IV_TOTALDURATION <= 1) then
  (
/* Employee applying for 1 day leave, check whether employee applied Casual Leave for previous and post 1 day */
   l_work_days = 0
   l_holiday_cnt = 0
   i = -1
/* Loop until previous 1 Work Day is found. Saturday, Sunday and Holidays are not counted as Work Day */
   while (l_work_days < 1)
   loop
   (
   l_prev_st_dt = to_date(to_char(add_days(IV_START_DATE,i),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_prev_ed_dt = to_date(to_char(IV_START_DATE,'yyyy-mm-dd'),'yyyy-mm-dd')

/* Fetch Holiday details from UDT table. */
   l_holiday_cnt = instr(GET_TABLE_VALUE('XX_HOLIDAY_CALENDAR_UDT','HolidayName',to_char(l_post_ed_dt1),'NONE'),'PUB',1,1)

/* if you use calendar events, then use below function to fetch holidays. Below function will return 0 for Weekends and Holidays. 1 for Work days */
/*l_holiday_cnt = GET_PAY_AVAILABILITY('ASSIGN',l_st_dt,l_ed_dt,'Y','Y','Y','Y','D')*/   
 
   if ((l_holiday_cnt = 1) or (to_number(to_char(l_prev_st_dt,'Day')) = 6) or (to_number(to_char(l_prev_st_dt,'Day')) = 7)) then
   (
   l_work_days = l_work_days
   )
   else
   (
   l_work_days = l_work_days + 1
   )

   i = i - 1  
   )   
    
   l_work_days = 0
   l_holiday_cnt = 0
   i = 1
/* Loop until post 1 Work Day is found. Saturday, Sunday and Holidays are not counted as Work Day */
   while (l_work_days < 1)
   loop
   (
   l_post_st_dt = to_date(to_char(add_days(IV_END_DATE,1),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_post_ed_dt1 = to_date(to_char(add_days(IV_END_DATE,i),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_post_ed_dt = to_date(to_char(add_days(IV_END_DATE,i + 1),'yyyy-mm-dd'),'yyyy-mm-dd')
  
   l_holiday_cnt = instr(GET_TABLE_VALUE('XX_HOLIDAY_CALENDAR_UDT','HolidayName',to_char(l_post_ed_dt1),'NONE'),'PUB',1,1)
  
   if ((l_holiday_cnt = 1) or (to_number(to_char(l_post_ed_dt1,'Day')) = 6) or (to_number(to_char(l_post_ed_dt1,'Day')) = 7)) then
   (
   l_work_days = l_work_days
   )
   else
   (
   l_work_days = l_work_days + 1
   )

   i = i + 1  
   )   

   l_person_id = GET_CONTEXT(PERSON_ID,0)
   CHANGE_CONTEXTS(PERSON_ID = l_person_id)
   (
/* Below function returns the number of Casual leaves between l_prev_st_dt and l_prev_ed_dt)  */
     l_prev_casual_leave = GET_ABSENCE_DAYS_PER_TYPE('Casual Leave',l_prev_st_dt,l_prev_ed_dt)
/* Below function returns the number of Casual leaves between l_post_st_dt and l_post_ed_dt)  */
     l_post_casual_leave = GET_ABSENCE_DAYS_PER_TYPE('Casual Leave',l_post_st_dt,l_post_ed_dt)
   )
   if (l_prev_casual_leave >= 1 or l_post_casual_leave >= 1) then
   (
     valid = 'N'
     error_message = 'XX_ERROR_MSG'
     return valid,error_message
   )
  )
 )
return valid
Labels:

Comments

Post a Comment

Popular posts from this blog

Do you know how to generate output using eText Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’ ...
20 comments
Read more

Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id   -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSy...
3 comments
Read more

Do you know how to generate output using Excel Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using Excel Templates in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Excel file and save it as type "Excel 97-2003 Workbook" (.xls) I have created 3 tabs "Summary", "Detail" and "XDO_METADATA" XDO_METADATA tab is mandatory. Place the below text in XDO_METADATA tab Version ARU-dbdrv Extractor Version Template Code Template Type TYPE_EXCEL_TEMPLATE Preprocess XSLT File Last Modified Date Last Modified By Data Constraints: In the "Detail" tab create Header row as shown below Now add the XDO tags Select the blank ...
2 comments
Read more