Create Fast Formulas for Maternity Absences

You can write your own custom logic in the fast formula for absence payments.

To create the formulas, here's what to do:

Navigation: My Client Groups > Payroll > Fast Formulas

Create an Oracle payment formula based on a documented sample formula for payment:

/********************************************************************************
* $Header:                                                                     *
 * Formula Name : ORA_HRX_IE_MATERNITY_PAYMENT                                   *
 * Description  : This is seeded formula for calculating the absence payment.   *
                  This will be invoked from the Entitlement Result formula.     *
 * Detail       :                                                               *
 *                                                                              *
 *                                                                              *
*********************************************************************************/

DEFAULT FOR PAY_DIR_COMP_MIN_START_DATE IS '0001/01/01 00:00:00' (date)
DEFAULT FOR PAY_DIR_COMP_MAX_END_DATE IS '0001/01/01 00:00:00' (date)
DEFAULT for PAY_EARN_PERIOD_END is '4712/12/31 00:00:00' (DATE)
DEFAULT for PAY_EARN_PERIOD_START is '0001/01/01 00:00:00' (date)
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER1 is 0
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 is 0
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_DATE1 is '0001/01/01 00:00:00' (date)
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_DATE2 is '4712/12/31 00:00:00' (date)
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE1 is 'X'
DEFAULT for ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE2 is 'X'


INPUTS ARE i_dir_card_id (NUMBER)
           ,i_dir_comp_id (NUMBER)
           ,i_factor (NUMBER)
		   ,i_pay_value (NUMBER)
	       ,i_deduction (NUMBER)
           ,i_unit (NUMBER)

maternity_benefit_payment = 0
factor = i_factor
pay_value = 0
deduction = i_deduction
unit = i_unit
L_PROCESS_DATE =  '0001/01/01 00:00:00' (date)


l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_01 - Inside ORA_HRX_IE_MATERNITY_PAYMENT' )

dir_card_id = i_dir_card_id
dir_comp_id = i_dir_comp_id

l_comp_id = DEDUCTION_COMPONENT_BY_EE
l_ded_card_id  = DEDUCTION_CARD_BY_EE

l_cb_id = GET_CONTEXT(CALC_BREAKDOWN_ID,1)
l_person_id = GET_CONTEXT(PERSON_ID,1)
l_ORA_HRX_IE_MAT_STD_WKLY_RATE=0

l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_02 - l_cb_id '|| to_char(l_cb_id))
l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_03 - l_person_id '|| to_char(l_person_id))
l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_04 - dir_card_id '||to_char(dir_card_id))
l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_05 - dir_comp_id '||to_char(dir_comp_id))
l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_06 - l_comp_id '||to_char(l_comp_id))
l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_07 - l_ded_card_id '||to_char(l_ded_card_id))



l_comp_id = DEDUCTION_COMPONENT_BY_EE
CHANGE_CONTEXTS(DIR_CARD_COMP_ID = l_comp_id)
(
  l_abs_min_date = PAY_DIR_COMP_MIN_START_DATE
  l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_08 l_abs_start_date : ' + to_char(l_abs_min_date) ) 
  
)    


CHANGE_CONTEXTS(DEDUCTION_CARD_ID = dir_card_id , DIR_CARD_COMP_ID = dir_comp_id)
(
  l_abs_comp_date = PAY_DIR_COMP_MIN_START_DATE
  L_PROCESS_DATE = l_abs_comp_date
  l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_09 l_abs_comp_date      : ' +to_char(l_abs_comp_date) )      
)

CHANGE_CONTEXTS(DEDUCTION_CARD_ID = dir_card_id , DIR_CARD_COMP_ID = l_comp_id,EFFECTIVE_DATE = l_abs_comp_date) 
  (
   l_ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 = ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 
    
    temp = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_09 l_ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 : ' +to_char(l_ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 )) 
  )

l_eff_date = l_abs_comp_date
l_day_of_week = GET_DATE_DAY_OF_WEEK(l_eff_date)


	if factor > 0 then
	(
	l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_10 l_abs_comp_date      : ' +to_char(l_abs_comp_date) )      

	IF (l_day_of_week = 'SUN') then 
	(
	maternity_benefit_payment = 0
    l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_11 l_day_of_week      : ' +l_day_of_week )      
	)
	else
	(
	
	if l_ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2 > 0 then
			(
			l_ORA_HRX_IE_MAT_STD_WKLY_RATE = l_ORA_HRX_IE_ABS_REF_ABS_RECORD_ATTRIBUTE_NUMBER2
			l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_12 l_ORA_HRX_IE_MAT_STD_WKLY_RATE OVERRIDE : ' +to_char(l_ORA_HRX_IE_MAT_STD_WKLY_RATE) )      
			)
			else
			(
				
			CHANGE_CONTEXTS(PART_NAME = 'ORA_HRX_IE_MAT_STD_WKLY_RATE', EFFECTIVE_DATE = l_abs_comp_date)
				(
				SET_INPUT('BASE', 1)
				EXECUTE('CALL_CALC_VALUE')
				l_ORA_HRX_IE_MAT_STD_WKLY_RATE = GET_OUTPUT('DED_AMOUNT',0)
				l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_13 l_ORA_HRX_IE_MAT_STD_WKLY_RATE : ' +to_char(l_ORA_HRX_IE_MAT_STD_WKLY_RATE) )      			
				)
			)
		
			maternity_benefit_payment = (l_ORA_HRX_IE_MAT_STD_WKLY_RATE/6)
			maternity_benefit_payment = trunc(maternity_benefit_payment + 0.005, 2)
			l_wsa_code = 'WSA:'||'ORA_HRX_IE_MAT_STD_DAILY_RATE'||'_'||to_char(l_person_id)||'_'|| to_char(l_cb_id) ||'_'|| to_char(l_eff_date)
			l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_14 l_wsa_code : ' + l_wsa_code )      			
				
		IF WSA_EXISTS(l_wsa_code,'NUMBER')  THEN
		(  
				maternity_benefit_payment = 0
		)
		else
		( 
				WSA_SET('WSA:'||'ORA_HRX_IE_MAT_STD_DAILY_RATE'||'_'||to_char(l_person_id)||'_'||to_char(l_cb_id)||'_'||to_char(l_eff_date),maternity_benefit_payment)
				l_log = PAY_INTERNAL_LOG_WRITE('ORA_HRX_IE_MATERNITY_PAYMENT_15 Set maternity_benefit_payment OUTPUT : ' + to_char(maternity_benefit_payment) )      				
		)
			
	)		
	pay_value = maternity_benefit_payment
	)
	else
	(
	pay_value = 0
	)
    RETURN pay_value,L_PROCESS_DATE

Note: Depending on whether ATTRIBUTE_NUMBER1 or ATTRIBUTE_NUMBER2 is used for configuring Weekly Rate Override, modify the logic in the formula accordingly.

Create an entitlement formula based on a documented sample formula for entitlement. The purpose of the formula is to:

Set the entitlement limits

Generate a certificate whenever the required Certificate is marked as Not Received.

/************************************************************************
FORMULA NAME: GIKUMARI_IE_MAT_ENTITLEMENT
FORMULA TYPE: Global Absence Plan Entitlement
DESCRIPTION : IE Maternity Absence Entitlement Formula ************************************************************************/
DEFAULT FOR IV_START_DATE IS '0001/01/01 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 IV_START_DURATION is 0
DEFAULT for IV_END_DURATION is 0
DEFAULT for IV_ACTUALCHILDBIRTHDATE is '4712/12/31 00:00:00' (date)
DEFAULT for IV_ACTUALSTARTDATE is '4712/12/31 00:00:00' (date)
DEFAULT for IV_ACTUALENDDATE is '4712/12/31 00:00:00' (date)
DEFAULT for IV_EXPECTEDCHILDBIRTHDATE is '4712/12/31 00:00:00' (date)
DEFAULT for IV_PLANNEDSTARTDATE is '4712/12/31 00:00:00' (date)
DEFAULT for IV_PLANNEDENDDATE is '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_MATCHING_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR IV_NOTIFICATION_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR IV_UI_PER_CERT_ID is ' '
DEFAULT FOR IV_UI_ABS_CERT_ID is ' '
DEFAULT FOR IV_UI_CERT_TYPE is ' '
DEFAULT FOR IV_UI_CERT_REVPAYSTART_DATE  is '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_UI_CERT_REVPAYEND_DATE  is '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_UI_CERT_REVPAY_FACTOR is 0
DEFAULT FOR IV_UI_CERT_CREATION_TYPE is ' '
DEFAULT FOR IV_UI_CERT_STATUS is ' '
DEFAULT FOR ANC_ABS_ENTR_CERTS_STATUS_ARR is EMPTY_TEXT_NUMBER
DEFAULT_DATA_VALUE for ANC_ABS_ENTR_CERTS_STATUS_ARR is ' '
DEFAULT FOR ANC_ABS_CERTS_INFORMATION1_ARR is EMPTY_TEXT_NUMBER
DEFAULT_DATA_VALUE for ANC_ABS_CERTS_INFORMATION1_ARR is ' '
DEFAULT FOR ANC_ABS_CERTS_NAME_ARR is EMPTY_TEXT_NUMBER
DEFAULT_DATA_VALUE for ANC_ABS_CERTS_NAME_ARR is ' '
DEFAULT FOR ANC_ABS_ENTR_CERTS_REV_PAY_START_DT_ARR is EMPTY_DATE_NUMBER
DEFAULT_DATA_VALUE for ANC_ABS_ENTR_CERTS_REV_PAY_START_DT_ARR is '4712/12/31 00:00:00' (DATE)
DEFAULT FOR ANC_ABS_ENTR_CERTS_REV_PAY_END_DT_ARR is EMPTY_DATE_NUMBER
DEFAULT_DATA_VALUE for ANC_ABS_ENTR_CERTS_REV_PAY_END_DT_ARR is '4712/12/31 00:00:00' (DATE)
DEFAULT FOR LOC_NUMBER_ARRAY IS EMPTY_NUMBER_TEXT
DEFAULT FOR LOC_STRING_ARRAY IS EMPTY_TEXT_TEXT
DEFAULT FOR LOC_DATE_ARRAY IS EMPTY_DATE_TEXT
DEFAULT FOR LOC_API_SKIP is 'N'
DEFAULT FOR IV_ATTRIBUTE_1 is 'X' 
DEFAULT FOR IV_ATTRIBUTE_2 is 'X'
DEFAULT FOR IV_ATTRIBUTE_DATE1 is '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ATTRIBUTE_DATE2 is '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ATTRIBUTE_NUMBER1 is 0 
DEFAULT FOR IV_ATTRIBUTE_NUMBER2 is 0 


INPUTS ARE IV_START_DATE,IV_END_DATE ,IV_TOTALDURATION,IV_START_DURATION,IV_END_DURATION, IV_ACTUALCHILDBIRTHDATE,IV_ACTUALSTARTDATE,IV_ACTUALENDDATE,IV_EXPECTEDCHILDBIRTHDATE, IV_PLANNEDSTARTDATE,IV_PLANNEDENDDATE,IV_INFORMATION_DATE1,IV_INFORMATION_DATE2,IV_INFORMATION_DATE3,IV_INFORMATION_DATE4,IV_MATCHING_DATE, IV_NOTIFICATION_DATE,IV_UI_PER_CERT_ID,IV_UI_ABS_CERT_ID,IV_UI_CERT_TYPE,IV_UI_CERT_REVPAYSTART_DATE,IV_UI_CERT_REVPAYEND_DATE,IV_UI_CERT_REVPAY_FACTOR,IV_UI_CERT_CREATION_TYPE,IV_UI_CERT_STATUS,IV_ATTRIBUTE_1,IV_ATTRIBUTE_2,IV_ATTRIBUTE_DATE1,IV_ATTRIBUTE_DATE2,IV_ATTRIBUTE_NUMBER1,IV_ATTRIBUTE_NUMBER2

l_dummy = ess_log_write( 'MAT_Entitlement  >>  Start of Formula')
l_trunc_eff_date = TRUNC(GET_CONTEXT(EFFECTIVE_DATE,'0001/01/01' (date)))


IF IV_EXPECTEDCHILDBIRTHDATE WAS NOT DEFAULTED THEN
(
   l_placement_date = IV_EXPECTEDCHILDBIRTHDATE
)
IF IV_START_DATE WAS NOT DEFAULTED THEN
(
   l_start_date = IV_START_DATE
)
IF IV_END_DATE WAS NOT DEFAULTED THEN
(
   l_end_date = IV_END_DATE
)
IF IV_ACTUALCHILDBIRTHDATE WAS NOT DEFAULTED THEN
(
   l_placement_date = IV_ACTUALCHILDBIRTHDATE
)
IF IV_ACTUALSTARTDATE WAS NOT DEFAULTED THEN
(
   l_start_date = IV_ACTUALSTARTDATE
)
IF IV_ACTUALENDDATE WAS NOT DEFAULTED THEN
(
   l_end_date = IV_ACTUALENDDATE
)


LOC_API_SKIP = 'N'

l_cert_index = 1
band1name = 'Band1Pay'
band1payfactor = 100
band1entitlement = 182

band2name = 'Band2Pay'
band2payfactor = 0
band2entitlement = 9999

l_cert_id_being_changed = IV_UI_ABS_CERT_ID
l_cert_status = IV_UI_CERT_STATUS
l_altcd_of_changed_cert = 'XYZ'

CERT_NAMES=EMPTY_TEXT_NUMBER
CERT_START_DATES=EMPTY_DATE_NUMBER
CERT_END_DATES=EMPTY_DATE_NUMBER
CERT_COMMENTS=EMPTY_TEXT_NUMBER

/* In case certified is being touched then get certificate details */
IF l_cert_id_being_changed != ' '  THEN
(
	l_altcd_of_changed_cert = GET_VALUE_SET('ORA_HRX_IE_CERT_ALTCD' ,'|=CURRENT_CERT_ID='||l_cert_id_being_changed )
)


/*** Start of section for maternity certificate   ***/
l_maternity_certificate_received = 'NO'
l_maternity_certificate_received = IV_ATTRIBUTE_1


/* start if clause maternity certificate  */
IF l_maternity_certificate_received = 'YES' THEN
(
l_dummy = ess_log_write( 'l_maternity_certificate_received '||l_maternity_certificate_received)
)
else
(
	l_create = 'N'
	l_cert_start_date = l_start_date
	l_cert_end_date = l_end_date

	IF ( l_altcd_of_changed_cert = 'XYZ' ) THEN
	l_create = 'Y'
	ELSE
	(
	IF ( l_altcd_of_changed_cert = 'CERT300100674838598' ) THEN
	  l_create = 'N'
	ELSE
	  IF ( l_altcd_of_changed_cert != 'XYZ' )  THEN
	  (
	l_create = 'Y'
	l_array_index=ANC_ABS_CERTS_NAME_ARR.FIRST(-1)
	while (ANC_ABS_CERTS_NAME_ARR.exists(l_array_index)) loop
	(
	  if(TRUNC(ANC_ABS_ENTR_CERTS_REV_PAY_START_DT_ARR[l_array_index])= TRUNC(l_cert_start_date)
		and (TRUNC(ANC_ABS_ENTR_CERTS_REV_PAY_END_DT_ARR[l_array_index]) = TRUNC(l_cert_end_date))
		and (ANC_ABS_ENTR_CERTS_STATUS_ARR[l_array_index] = 'V')
		and (ANC_ABS_CERTS_NAME_ARR[l_array_index]= 'GIKUMARI_IE_NO_MAT_CERT_IE')) THEN
	  (
		l_create = 'N'  /* we have found an existing voided cert so don't create */
	  )
	  l_array_index=ANC_ABS_CERTS_NAME_ARR.NEXT(l_array_index,-1)
	) /** end while **/
	  )
	)
	IF ( l_create = 'Y'     ) THEN
	(
	  CERT_NAMES[l_cert_index] = 'GIKUMARI_IE_NO_MAT_CERT'
	  CERT_COMMENTS[l_cert_index] = 'Non receipt of maternity certificate leads to noneligibility for occupational absence payment.'
	  CERT_START_DATES[l_cert_index] = l_cert_start_date
	  CERT_END_DATES[l_cert_index] = l_cert_end_date
	  l_cert_index = l_cert_index + 1
	  RETURN band1name, band1payfactor, band1entitlement,band2name, band2payfactor, band2entitlement,CERT_NAMES,CERT_START_DATES,CERT_END_DATES,CERT_COMMENTS
	)

) /** end of if clause maternity certificate **/



RETURN band1name, band1payfactor, band1entitlement,band2name, band2payfactor, band2entitlement,CERT_NAMES,CERT_START_DATES,CERT_END_DATES,CERT_COMMENTS

Note: Please replace the variable name in the code above with the actual value in the column of the table ANC_ABS_CERTIFICATIONS_f mentioned below for the certificate created.

Variable Name	Column Name
CERT_NAMES	NAME
ANC_ABS_CERTS_NAME_ARR	BASE_NAME
l_altcd_of_changed_cert	ANC_ABS_CERTIFICATION_F_ALTCD

Note: The absence certificate being referenced to in the entitlement formula should have the same effective start date as the entitlement formula. Ensure that the values of the segments related to Certificate Received and Weekly Rate Override reference in the above formula are modified in the formula as per the actual values stored.

Create a validation formula based on a delivered seeded formula IE Adoption Absence Validation Included
- Warning for Minimum Duration
- Warning for Maximum Duration