This query selects the SCREEN_ENTRY_VALUE from PAY_ELEMENT_ENTRY_VALUES_F for element entries that contain input values with the base names ‘Amount’ or ‘Total_Amount’. It joins PAY_ELEMENT_ENTRIES_F with PAY_ELEMENT_TYPES_F to identify the related element type and further joins PAY_INPUT_VALUES_F to filter specific input values. The query also joins PAY_ENTRY_USAGES and PAY_REL_GROUPS_DN to associate the element entry with the payroll relationship group. The result is restricted to a single record using ROWNUM = 1.
SQL Query
SELECT
PEEVFF4.SCREEN_ENTRY_VALUE
FROM
PAY_ELEMENT_ENTRIES_F PEEFF1,
PAY_ELEMENT_TYPES_F PETFF2 ,
PAY_INPUT_VALUES_F PIVFF3 ,
PAY_ELEMENT_ENTRY_VALUES_F PEEVFF4 ,
pay_entry_usages peuu5 ,
pay_rel_groups_dn pasgg6
WHERE
1=1
AND PEEFF1.ELEMENT_TYPE_ID = PETFF2.ELEMENT_TYPE_ID
--AND PETFF2.BASE_ELEMENT_NAME like ('%Relocation%')
AND PETFF2.ELEMENT_TYPE_ID = PIVFF3.ELEMENT_TYPE_ID
AND PIVFF3.BASE_NAME IN ('Amount','Total_Amount')
AND PEEFF1.ELEMENT_ENTRY_ID = PEEVFF4.ELEMENT_ENTRY_ID
AND PIVFF3.INPUT_VALUE_ID = PEEVFF4.INPUT_VALUE_ID
--AND pasgg6.ASSIGNMENT_ID = :{PARAMETER.ASG_ID}
and PEEFF1.ELEMENT_ENTRY_ID = peuu5.ELEMENT_ENTRY_ID
and peuu5.payroll_assignment_id = pasgg6.relationship_group_id
AND ROWNUM = 1
Output Results
Related Posts
How to Create Eligibility Profiles
Posted on
