This query selects the EFFECTIVE_START_DATE from PAY_ELEMENT_ENTRY_VALUES_F and calculates a new date by adding 12 months using the ADD_MONTHS function. The result is formatted as YYYY-MM-DD using TO_CHAR. It joins PAY_ELEMENT_ENTRIES_F with PAY_ELEMENT_TYPES_F to identify the related payroll element and further joins PAY_INPUT_VALUES_F to filter input values with base names ‘Amount’ or ‘Total_Amount’. Additionally, the query joins PAY_ENTRY_USAGES and PAY_REL_GROUPS_DN to associate the element entry with the payroll relationship group. The output is limited to one record using ROWNUM = 1.
SQL Query Section
SELECT
TO_CHAR(ADD_MONTHS(PEEVFF4.EFFECTIVE_START_DATE, 12),'YYYY-MM-DD')
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 peuu6 ,
pay_rel_groups_dn pasgg5
WHERE
1=1
AND PEEFF1.ELEMENT_TYPE_ID = PETFF2.ELEMENT_TYPE_ID
--AND UPPER(PETFF2.BASE_ELEMENT_NAME) LIKE UPPER('%Retention%Bonus%')
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 pasgg5.ASSIGNMENT_ID = :{PARAMETER.ASG_ID}
and PEEFF1.ELEMENT_ENTRY_ID = peuu6.ELEMENT_ENTRY_ID
and peuu6.payroll_assignment_id = pasgg5.relationship_group_id
AND ROWNUM = 1 Output Results
Related Posts
Update Salary of an Employee
Posted on