PER ADA

Description
Categories: BI Publisher
Application: Human Resources
Source: ADA Report (XML)
Short Name: PERRPADA_XML
DB package: PER_PERUSADA_XMLP_PKG
SELECT  
        peo.person_id,
        initcap(peo.full_name) 		d_full_name,
        peo.employee_number             	d_employee_number,
        peo.applicant_number              	d_applicant_number,
        decode(peo.registered_disabled_flag, 
	   'Y', 'Yes','P','Yes','F','Yes','No')                              d_disabled,
        job.name                                    	d_job_name,
        gdt.name                               	d_grade_name,
        hr_general.decode_position_latest_name(ass.position_id)     d_position_name,
        loc.location_code                     	d_location,
         ass.job_id,
        ass.position_id
FROM    
        hr_locations                              loc,
        per_grades                               grd,
        per_grades_tl                           gdt,
        per_person_types                    type,
        per_all_people_f	       peo,
        hr_organization_information      hoi1,
        hr_organization_information      hoi2,
        hr_soft_coding_keyflex            scf,
        per_jobs_vl    job,
       per_all_assignments_f               ass
WHERE   
             peo.person_id                  	= ass.person_id
AND     job.job_information_category   	= 'US'
AND     ass.job_id                     	                  = job.job_id
AND     ass.location_id                                = loc.location_id
AND     ass.grade_id                      = grd.grade_id (+)
AND     ass.grade_id 		= gdt.grade_id (+)
AND      gdt.language(+)                 = userenv('LANG')
AND      ass.business_group_id	= grd.business_group_id (+)
AND     peo.effective_start_date 	<= :P_SESSION_DATE
AND     peo.effective_end_date   	>= :P_SESSION_DATE
AND     ass.primary_flag		= 'Y'
AND     ass.assignment_type 		= 'E'
AND     peo.person_type_id		= type.person_type_id
AND     peo.business_group_id	= type.business_group_id
AND     ass.assignment_status_type_id    = hoi1.org_information1
AND     hoi1.org_information_context        = 'Reporting Statuses'
AND     hoi1.organization_id                        = :P_BUSINESS_GROUP_ID
AND     ass.employment_category             = hoi2.org_information1
AND     hoi2.organization_id                       = :P_BUSINESS_GROUP_ID
AND     hoi2.org_information_context        = 'Reporting Categories'
AND    ass.effective_start_date                   <= :P_SESSION_DATE
AND    ass.effective_end_date		>= :P_SESSION_DATE
AND     
                ass.soft_coding_keyflex_id 	= scf.soft_coding_keyflex_id 
AND       scf.segment1 = to_char(:tax_unit_id)
AND       (                scf.segment9       = :establishment_id
                 AND       scf.segment9 is not null 
                  OR        
	      scf.segment9 is null      
	AND     :P_ORG_STRUCTURE_VERSION_ID_1 != -1
                  AND     ass.organization_id in 
                      (         
                       SELECT ose.organization_id_child
                       FROM   per_org_structure_elements ose
                       WHERE  ose.org_structure_version_id = :P_ORG_STRUCTURE_VERSION_ID_1
                        CONNECT BY 
	            PRIOR ose.organization_id_child         =  ose.organization_id_parent
                                  AND  ose.org_structure_version_id +0 = :P_ORG_STRUCTURE_VERSION_ID_1
                       START WITH ose.organization_id_child = :establishment_id
                       UNION
--select :establishment_id
select to_number(:establishment_id1)
from dual
                       )
               )
AND (peo.person_id in (select a.person_id 
	   from    per_person_analyses  a
	   where a.person_id = peo.person_id
	   and     a.id_flex_num in ( :c_disability_id_flex_num, :c_disability_acc_id_flex_num)
                     )
         or peo.registered_disabled_flag in ('Y','P','F')) 
&c_lex_assign_where
ORDER BY 
&c_lex_assign_order
Parameter Name SQL text Validation
PER_DATES_STANDARD
 
Date
GRE
 
LOV Oracle
Organization Hierarchy
 
LOV Oracle
Employee
 
LOV Oracle
Employee Number
 
LOV Oracle
Job
 
LOV Oracle
Location
 
LOV Oracle
View Job Requirements
 
LOV Oracle
View Position Requirements
 
LOV Oracle
View Reg. Disabled Only
 
LOV Oracle
Sort By
 
LOV Oracle
BUSINESS_GROUP_ID