Sample Extract Criteria Fast Formulas for ready reference

Only Include employees from US legislation and only the Person Type of Employee. Rest all legislation employees should be excluded and Pending Worker or Contingent Worker should also be excluded.

DEFAULT FOR PER_ASG_LEGISLATION_CODE IS ' '
DEFAULT FOR PER_ASG_SYSTEM_PERSON_TYPE IS 'X'
DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT

IF PER_ASG_LEGISLATION_CODE = 'US' THEN 
(INCLUDE = 'TRUE')
ELSE (INCLUDE = 'FALSE'
      RETURN INCLUDE)
	  
IF PER_ASG_SYSTEM_PERSON_TYPE='EMP' THEN 
(INCLUDE = 'TRUE')
ELSE (INCLUDE = 'FALSE'
      RETURN INCLUDE)
RETURN INCLUDE

Sample 2: Excluded EEs termed before 7 days

Logic to exclude terminated employees before last 7 days.

DEFAULT FOR PER_PER_TERMINATION_LAST_UPDATE_DATE_TIME IS '0001/01/01 00:00:00' (date)
DEFAULT for PER_ASG_STATUS_USER_STATUS is 'XX'
DEFAULT FOR PER_ASG_EFFECTIVE_START_DATE IS '0001/01/01 00:00:00' (date)
DEFAULT FOR PER_ASG_EFFECTIVE_END_DATE IS '0001/01/01 00:00:00' (date)

INCLUDE = 'TRUE'

l_default_date = TO_DATE('1900/01/01', 'YYYY/MM/DD')
l_log_data = ESS_LOG_WRITE ('l_default_date is '||to_char(l_default_date,'MM/DD/YYYY'))
l_effective_date = TO_CHAR(GET_CONTEXT(EFFECTIVE_DATE, l_default_date), 'YYYY/MM/DD')
l_eff_date_to_use = to_date(l_effective_date, 'YYYY/MM/DD')
l_log_data = ESS_LOG_WRITE ('l_effective_date is '||l_effective_date)
ld_effective_date = ADD_DAYS(to_date(l_effective_date, 'YYYY/MM/DD'), - 7)

l_log_data = ESS_LOG_WRITE ('ld_effective_date is '||to_char(ld_effective_date,'MM/DD/YYYY'))
l_log_data = ESS_LOG_WRITE ('PER_PER_TERMINATION_LAST_UPDATE_DATE_TIME is '||to_char(PER_PER_TERMINATION_LAST_UPDATE_DATE_TIME,'MM/DD/YYYY'))
l_log_data = ESS_LOG_WRITE ('PER_ASG_STATUS_USER_STATUS is '||PER_ASG_STATUS_USER_STATUS)
l_log_data = ESS_LOG_WRITE ('PER_ASG_EFFECTIVE_START_DATE is '||to_char(PER_ASG_EFFECTIVE_START_DATE,'MM/DD/YYYY'))
l_log_data = ESS_LOG_WRITE ('PER_ASG_EFFECTIVE_START_DATE is '||to_char(PER_ASG_EFFECTIVE_START_DATE,'MM/DD/YYYY'))

IF (((PER_PER_TERMINATION_LAST_UPDATE_DATE_TIME <= ld_effective_date ) and (PER_ASG_STATUS_USER_STATUS = 'Inactive - Payroll Eligible'))
   OR ((PER_ASG_STATUS_USER_STATUS = 'Active - Payroll Eligible' OR PER_ASG_STATUS_USER_STATUS = 'LOA - Not Payroll Eligible'
         OR PER_ASG_STATUS_USER_STATUS = 'LOA - Payroll Eligible') and (l_eff_date_to_use <= PER_ASG_EFFECTIVE_START_DATE) and 
   (l_eff_date_to_use >= PER_ASG_EFFECTIVE_END_DATE)))
THEN 
  (
     INCLUDE = 'FALSE'
  )  
  l_log_data = ESS_LOG_WRITE ('INCLUDE is '||INCLUDE)
RETURN INCLUDE

Sample 3: Exclude all employees other than US BU

Logic to exclude all employees other than US Business Unit

DEFAULT FOR PER_ASG_BUSINESS_UNIT_NAME IS 'NA'

log = ess_log_write('BU Formula started')

l_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
l_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)

l_log = ess_log_write('Start Processing for Assignment ID : ' || TO_CHAR(l_assignment_id))
INCLUDE='FALSE'
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = l_assignment_id ,EFFECTIVE_DATE = l_effective_date)
(
    if PER_ASG_BUSINESS_UNIT_NAME = 'US'
    then INCLUDE = 'TRUE'
    else INCLUDE = 'FALSE'
)
return INCLUDE

Sample 4: Include only Top of stack assignment

Logic to include only the latest assignment and exclude any other assignments

/* Default Values */
DEFAULT FOR PER_ASG_REL_DATE_START IS '0001/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_WORK_REL_PRIMARY_FLAG IS 'X' 
DEFAULT_DATA_VALUE FOR PER_EXT_WORK_REL_DATE_START IS '0001/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_WORK_REL_PERIOD_OF_SERVICE_ID IS 0
/*DEFAULT_DATA_VALUE FOR PER_EXT_WORK_REL_ACTUAL_TERMINATION_DATE IS '0001/01/01 00:00:00' (date)*/
DEFAULT_DATA_VALUE FOR PER_EXT_WORK_REL_PERSON_ID IS 0

/*================ FORMULA SECTION BEGIN =======================*/
INCLUDE = 'FALSE'

l_effective_date = GET_CONTEXT(EFFECTIVE_DATE, '0001/01/01 00:00:00'(date))
l_work_relation_dt = '1951/01/01 00:00:00' (date)
l_person_id = 0
I = 1
l_debug_flag = 'N'

WHILE PER_EXT_WORK_REL_PERIOD_OF_SERVICE_ID.EXISTS(I) LOOP  
( 
	IF PER_EXT_WORK_REL_DATE_START[I] <=l_effective_date 
	   AND PER_EXT_WORK_REL_PRIMARY_FLAG[I] = 'Y' 
	   AND l_work_relation_dt <= PER_EXT_WORK_REL_DATE_START[I] 
	   /*AND PER_EXT_WORK_REL_ACTUAL_TERMINATION_DATE[I]<=l_effective_date*/  THEN  
	( 
		l_work_relation_dt = PER_EXT_WORK_REL_DATE_START[I] 
		l_person_id = PER_EXT_WORK_REL_PERSON_ID[I]
	) 
	I = I + 1 
) 

IF (PER_ASG_REL_DATE_START = l_work_relation_dt) THEN
(
 If l_debug_flag = 'Y' THEN
 (
   l_log_accrual = ess_log_write('Person_id: '      ||TO_CHAR(l_person_id))
   l_log_accrual = ess_log_write('l_work_relation_dt: '      ||TO_CHAR(l_work_relation_dt))
 )   
 INCLUDE = 'TRUE'
)
RETURN INCLUDE

Sample 5: Logic to exclude based on Job DFF

Logic to validate and get jobs that have value in PER_JOBS_F.ATTRIBUTE3

DEFAULT FOR PER_JOB_ATTRIBUTE3 IS 'XXX'
INCLUDE = 'FALSE'
IF (PER_JOB_ATTRIBUTE3 <> 'XXX') THEN
(
 INCLUDE = 'TRUE'
)
RETURN INCLUDE

Sample 6: Logic to pick only active employees

DEFAULT FOR PER_ASG_STATUS_USER_STATUS IS 'XXX'
INCLUDE = 'FALSE'
IF (PER_ASG_STATUS_USER_STATUS = 'Active') or (PER_ASG_STATUS_USER_STATUS = 'Leave of Absence') or (PER_ASG_STATUS_USER_STATUS = 'Leave With Pay') or 
(PER_ASG_STATUS_USER_STATUS ='Suspended - No Payroll') THEN
( INCLUDE = 'TRUE')
RETURN INCLUDE

The above samples can be used as a starting point when you have a requirement to include/exclude employees in HCM Extract.

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.