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 the TO_CHAR function. It joins PAY_ELEMENT_ENTRIES_F with PAY_ELEMENT_TYPES_F to identify the corresponding payroll element and joins PAY_INPUT_VALUES_F to link the related input values for the element entry. The query also joins PAY_ENTRY_USAGES and PAY_REL_GROUPS_DN to associate the element entry with the payroll relationship group. The result set 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 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
Create a Position in Oracle Fusion
Posted on
