• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:March 15, 2021
  • Reading time:2 mins read
You are currently viewing SQL Query to get the Object Group Employees
SQL Query to get the Object Group Employees
- SQL Query to get the Object Group Employees
Total Visits: 6

In this article we will look into the query to get the employees attached to an Object group.

select 
	pobt.object_group_type_id,
	pobt.name_code,
	pog.base_object_group_name object_group_name,
	pogl.description,
	pobg.object_id payroll_relationship_id,
	decode(pobg.include_or_exclude,'I','Include','E','Exclude',pobg.include_or_exclude) include_or_exclude,
	ppr.payroll_relationship_number,
	papf.person_number,
	ppn.display_name person_name,
	row_number() over (order by papf.person_number) serial_number
from
	pay_object_group_types pobt,
	pay_object_groups pog,
	pay_object_groups_tl pogl,
	pay_object_group_levels pobl,
	pay_object_group_amends pobg,
	pay_pay_relationships_dn ppr,
	per_all_people_f papf,
	per_person_names_f ppn
where
	pobt.name_code = 'PAYEMP'
and pog.object_group_type_id = pog.object_group_type_id
and trunc(sysdate) between pog.start_date and pog.end_date
--and pog.base_object_group_name = 'JAD_Internal_Employees'
and pogl.object_group_id = pog.object_group_id
and pogl.language = 'US'
and pobl.object_group_type_id = pobt.object_group_type_id
and pobl.name_code = 'PAY_REL'
and pobg.object_group_level_id = pobl.object_group_level_id
and pobg.object_group_id = pog.object_group_id
and ppr.payroll_relationship_id = pobg.object_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.person_id = ppr.person_id(+)
and upper(ppn.name_type) = 'GLOBAL'
and trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and ppn.person_id(+) = papf.person_id
--and pog.base_object_group_name = :p_object_name
order by person_number

Feel free to modify it as per your requirement.