PER Saudi Disabled Employee

Description
Categories: BI Publisher
Columns: Name, Employee Number, Full Name, Disability Status, Job Name, Position Name, Employment Office, Disability Type, Reason, Pay Rate ...
Application: Human Resources
Source: Saudi Disabled Employee Report (XML)
Short Name: PERSADIS_XML
DB package: PER_PERSADIS_XMLP_PKG
SELECT org.name
              ,people.employee_number
              ,people.full_name
              ,hr3.meaning disability_status
              ,job.name job_name
              ,pos.name position_name
              ,emp.name employment_office
              ,hr1.meaning disability_type
              ,hr2.meaning reason
              ,null pay_rate,
	PER_PERSADIS_XMLP_PKG.cf_1formula() CF_1
FROM  per_all_assignments_f assg
             ,per_all_people_f          people
             ,per_disabilities_f        disb
             ,per_jobs                       job
             ,per_all_positions        pos
             ,hr_lookups                   hr1
             ,hr_lookups                   hr2
             ,hr_lookups                   hr3
             ,hr_all_organization_units   org
             ,hr_all_organization_units   emp
WHERE assg.person_id = people.person_id
AND      assg.assignment_type = 'E'
--AND     NVL(:p_date,sysdate) BETWEEN assg.effective_start_date
  AND     NVL(:p_date_t,sysdate) BETWEEN assg.effective_start_date
                  AND   assg.effective_end_date
--AND     NVL(:p_date,sysdate) between people.effective_start_date
  AND     NVL(:p_date_t,sysdate) between people.effective_start_date
                  AND   people.effective_end_date
--AND     NVL(:p_date,sysdate) between disb.effective_start_date
  AND     NVL(:p_date_t,sysdate) between disb.effective_start_date
                  AND   disb.effective_end_date
AND    assg.job_id = job.job_id(+)
AND    assg.position_id = pos.position_id(+)
AND    hr1.lookup_type = 'DISABILITY_CATEGORY'
AND    hr1.lookup_code = disb.category
AND    assg.organization_id = org.organization_id
AND    disb.person_id = people.person_id
AND    disb.dis_information_category = 'SA'
AND    disb.reason = hr2.lookup_code(+)
AND    hr2.lookup_type(+) = 'DISABILITY_REASON'
AND    disb.status = hr3.lookup_code
AND    hr3.lookup_type = 'DISABILITY_STATUS'
&l_org_condition
&l_type_condition
&l_emp_office_condition
&l_status_condition
AND org.business_group_id = :p_business_group_id
AND emp.organization_id = disb.dis_information1
ORDER BY org.name, disability_type, full_name
Parameter Name SQL text Validation
Organization Hierarchy
 
LOV Oracle
Version
 
LOV Oracle
Organization
 
LOV Oracle
Disability Type
 
LOV Oracle
Employment Office
 
LOV Oracle
Disability Status
 
LOV Oracle
Date
 
Date
Business Group Id
 
Number