Sample Table Valuesets for ready reference

Using Table Valueset when DBIs are not available

Using Aggregate functions in table valueset

Bypass FROM clause limit in table valueset

If we are unable to achieve the requirement using the Database Items (DBIs) then we have to use the Table Based Value Set and fetch the required values using the SQL Query and then use the Extract Rule Fast Formula to get the value on the HCM Extract.

Table based Value Sets accepts bind variables and these can be passed from Extract Rule Fast Formula.

Sample 1: Get Phone Number based on Phone Type and Date

Logic to get Phone Number of the bind Person, Phone Type and Date.

select
Phone_number
from 
Per_phones
where
person_id =TO_NUMBER( :{PARAMETER.P_PERSON_ID})
and Phone_type = :{PARAMETER.P_TYPE}
and to_date( :{PARAMETER.P_DATE}, 'MM/DD/YYYY') between NVL( date_from,to_date( :{PARAMETER.P_DATE}, 'MM/DD/YYYY')) and NVL(date_to,
to_date( :{PARAMETER.P_DATE}, 'MM/DD/YYYY'))

Sample 2: Decode Assignment Status Code based on Assignment Id and Date

Logic to get the Assignment Status Code in decoded form based on input assignment id and input date

select
DECODE(past.assignment_status_code,'EXPAT_LT','N','EXPAT_ST','N','Y')
from
per_all_assignments_m paam,per_assignment_status_types_vl past
where
paam.assignment_status_type_id = past.assignment_status_type_id
AND paam.assignment_id=(:{PARAMETER.L_ASG_ID})
AND paam.legislation_code<>'US'
AND past.assignment_status_code NOT IN ('EXPAT_LT','EXPAT_ST')
AND TO_DATE (:{PARAMETER.L_EFF_DATE}, 'YYYYMMDD') BETWEEN paam.effective_start_date AND paam.effective_end_date
AND EXISTS (SELECT 1 FROM per_all_assignments_m paam1,per_assignment_status_types_vl past1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_status_type_id = past1.assignment_status_type_id
AND past1.assignment_status_code IN ('EXPAT_LT','EXPAT_ST')
AND paam1.assignment_type='E'
AND paam1.primary_assignment_flag='Y'
AND paam1.legislation_code='US'
AND paam1.effective_start_date <=paam.effective_start_date)

Sample 3: Get Absence count between date range

Get the count of Absences between the input date range. Normally we cannot use the aggregate functions in table value set however this is a beautiful work around to achieve it.

select
n.counter
from
(SELECT TO_NUMBER( LEVEL) - 1 counter  FROM DUAL  CONNECT BY LEVEL <= 1000000 ) n
where
n.counter = (SELECT  count(DISTINCT sup.PERSON_ID)
	FROM ANC_PER_ABS_ENTRIES anc	,
	PER_ASSIGNMENT_SUPERVISORS_F sup
	WHERE anc.PERSON_ID =sup.PERSON_ID
	AND sup.PERSON_ID                      !=  (:{PARAMETER.P_PERSON_ID})  
	AND sup.MANAGER_ID      = (:{PARAMETER.P_MANAGER_ID})  
           AND ANC.ABSENCE_STATUS_CD = 'SUBMITTED'
           AND ANC.END_DATETIME   >= (:{PARAMETER.P_ABSENCE_START})             
	AND ANC.START_DATETIME   <= (:{PARAMETER.P_ABSENCE_END}))

Sample 4: Check if the input person is an expat or not.

Logic to check if the input person is an expat or not. Return Y for expats.

select
'Y'
from
DUAL
where
EXISTS(SELECT 1
FROM per_all_assignments_m paam
    ,per_assignment_status_types_vl past
 WHERE paam.assignment_status_type_id = past.assignment_status_type_id
   AND paam.person_id=(:{PARAMETER.L_PERSON_ID})
   AND paam.primary_flag='N'
   AND paam.assignment_type ='E'
   AND paam.assignment_status_type ='ACTIVE'
   AND past.assignment_status_code IN ('EXPAT_LT','EXPAT_ST')
   AND paam.legislation_code<>'US'
   AND TO_DATE (:{PARAMETER.L_EFF_DATE}, 'YYYYMMDD') BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TO_DATE (:{PARAMETER.L_EFF_DATE}, 'YYYYMMDD') BETWEEN past.start_date AND NVL(past.end_date,TO_DATE (:{PARAMETER.L_EFF_DATE}, 'YYYYMMDD')))

Sample 5: Get Costing Segments for an organization

Logic to get all costing segments for an organization. We need to concatenate all fields in the Value Column.

select
pcaa.segment1||'-'||pcaa.segment2||'-'||pcaa.segment3||'-'||pcaa.segment4||'-'||pcaa.segment5||'-'||pcaa.segment6||'-'||pcaa.segment7
from
pay_cost_alloc_accounts pcaa ,pay_cost_allocations_f  pcaf
where
pcaa.cost_allocation_record_id = pcaf.cost_allocation_record_id
AND pcaf.source_type = 'ORG'
AND pcaa.source_sub_type = 'COST'
AND pcaf.source_id = :{PARAMETER.P_ORGANIZATION_ID}
AND TO_DATE(:{PARAMETER.P_EFFECTIVE_DATE},'MM/DD/YYYY') BETWEEN pcaf.effective_start_date AND pcaf.effective_end_date

Sample 6: Get data from User Defined Table (UDT)

Logic to get the value from User Defined Table for the input mapping field.

select
INSF.VALUE
from
FF_USER_COLUMN_INSTANCES_F INSF,FF_USER_COLUMNS UCOLS,FF_USER_ROWS_F UROWS,FF_USER_TABLES UTABLES
Where INSF.USER_ROW_ID = UROWS.USER_ROW_ID
AND INSF.USER_COLUMN_ID = UCOLS.USER_COLUMN_ID
AND UROWS.USER_TABLE_ID = UCOLS.USER_TABLE_ID
AND UTABLES.USER_TABLE_ID = UROWS.USER_TABLE_ID
AND UTABLES.USER_TABLE_ID = UCOLS.USER_TABLE_ID
AND UTABLES.BASE_USER_TABLE_NAME = 'BEN_ELEMENT_UDT'
AND UCOLS.BASE_USER_COLUMN_NAME = 'HCM_ELEMENT_NAME'
AND UROWS.ROW_LOW_RANGE_OR_NAME = :{PARAMETER.L_PLAN_NAME}

The above queries provide you with an overview of the use cases for table valuesets in HCM Extracts.

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