PER ADA

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