PER EEO Establishment Employment Listing

Description
Categories: BI Publisher
Columns: Est Node Id, Parent Node Id, Est Rep Name, Headquarters, Estab Address, C Tot Act, C Report Emps, C Main Activity, C Tot Emps ...
Application: Human Resources
Source: EEO Establishment Employment Listing (XML)
Short Name: PERRPE1L_XML
DB package: PER_PERUSE1L_XMLP_PKG
Select
   pghn.hierarchy_node_id         "Est_Node_Id"
  ,pghn.parent_hierarchy_node_id  "Parent Node Id" 
  ,upper(hlei1.lei_information1)  "Est_Rep_Name"
  ,NVL(hlei2.lei_information10,'N')        "Headquarters"
  ,upper(ltrim(rtrim(eloc.address_line_1))||' '|| 
         ltrim(rtrim(eloc.address_line_2))||' '||
         ltrim(rtrim(eloc.address_line_3))||' '||  
         ltrim(rtrim(eloc.town_or_city))||', '||      
         ltrim(rtrim(eloc.region_2))||' '||           
         ltrim(rtrim(eloc.postal_code))) "Estab Address", 
	PER_PERUSE1L_XMLP_PKG.c_tot_actformula(pghn.hierarchy_node_id, NVL ( hlei2.lei_information10 , 'N' )) C_tot_act,
	PER_PERUSE1L_XMLP_PKG.c_report_emps_p c_report_emps,
	PER_PERUSE1L_XMLP_PKG.C_main_activity_p C_main_activity,
	PER_PERUSE1L_XMLP_PKG.C_tot_emps_p C_tot_emps
from
   hr_location_extra_info                hlei1
  ,hr_location_extra_info                hlei2
  ,per_gen_hierarchy_nodes         pghn
  ,hr_locations_all                           eloc
where
    (hlei1.information_type = 'EEO-1 Specific Information'
and hlei1.lei_information_category= 'EEO-1 Specific Information') 
and  (hlei2.information_type = 'Establishment Information'
and hlei2.lei_information_category= 'Establishment Information')
and hlei1.location_id = hlei2.location_id
and hlei1.location_id = pghn.entity_id
and pghn.parent_hierarchy_node_id = :c_parent_node_id
and pghn.node_type = 'EST'
and eloc.location_id = pghn.entity_id 
 and pghn.parent_hierarchy_node_id=:Parent_Node_Id
Parameter Name SQL text Validation
Payroll Period Start Date
 
Date
Payroll Period End Date
 
Date
Hierarchy_Name
 
LOV Oracle
Hierarchy Version
 
LOV Oracle
Create Audit Report
 
LOV Oracle
Business Group Name
 
LOV Oracle
Maximum No Of Employees
 
Number