• 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

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.