SELECT (SELECT DISTINCT person_number
FROM per_all_people_f per
WHERE per.person_id = aor.person_id
) person_number
,(SELECT DISTINCT full_name
FROM per_person_names_f per
WHERE per.person_id = aor.person_id
AND name_type = 'GLOBAL'
) person_name
,aor.responsibility_name
,TO_CHAR(aor.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') start_date
,TO_CHAR(aor.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') end_date
,aor.responsibility_type
,aor.status
,(SELECT hou.name
FROM hr_all_organization_units hou
WHERE hou.organization_id = aor.business_unit_id
) business_unit_name
,(SELECT hou.name
FROM hr_all_organization_units hou
WHERE hou.organization_id = aor.legal_entity_id
) legal_entity
,(SELECT hou.name
FROM hr_all_organization_units hou
WHERE hou.organization_id = aor.organization_id
) department_name
,(SELECT location_name
FROM hr_locations hl
WHERE hl.location_id = aor.location_id
) location_name
,(SELECT DISTINCT name
FROM hr_all_positions_f_vl pp
WHERE pp.position_id = aor.position_id
) position_name
,(SELECT DISTINCT name
FROM per_jobs_f_tl pj
WHERE pj.job_id = aor.job_id
) job_name
,(SELECT DISTINCT name
FROM per_grades_f_tl pg
WHERE pg.grade_id = aor.grade_id
) grade_name
,aor.assignment_category
,(SELECT pay.payroll_name
FROM pay_all_payrolls_f pay
WHERE pay.payroll_id = aor.payroll_id
AND TRUNC(SYSDATE) BETWEEN pay.effective_start_date AND pay.effective_end_date
) payroll_name
,(SELECT name
FROM per_legislative_data_groups_vl pld
WHERE pld.legislative_data_group_id = aor.legislative_data_group_id
) legislative_data_group
FROM per_asg_responsibilities aor
No comments:
Post a Comment