Saturday, January 29, 2022

Query to find all elements attached to employee in oracle apps



SELECT pbg.NAME business_group_name ,
       papf.employee_number ,
       papf.full_name ,
       petf.element_name ,
       peef.effective_start_date
FROM   per_all_people_f papf ,
       per_all_assignments_f paaf ,
       pay_element_entries_f peef ,
       pay_element_types_f petf ,
       per_business_groups pbg
WHERE  papf.person_id = paaf.person_id
AND    papf.business_group_id = pbg.business_group_id
AND    pbg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND    papf.current_employee_flag = 'Y'
AND    paaf.assignment_id = peef.assignment_id
AND    peef.element_type_id = petf.element_type_id
AND    Trunc (sysdate) BETWEEN papf.effective_start_date AND    papf.effective_end_date
AND    Trunc (sysdate) BETWEEN paaf.effective_start_date AND    paaf.effective_end_date
AND    Trunc (sysdate) BETWEEN peef.effective_start_date AND    peef.effective_end_date
AND    Trunc (sysdate) BETWEEN petf.effective_start_date AND    petf.effective_end_date
AND    petf.element_name LIKE 'PF%'

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...