ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F447B2AF1A992795E05362FB0905CA30Y 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 PER PERRPADA_XML US PER ADA (1) Imported from BI Publisher Description: Americans with Disabilities Act Report Application: Human Resources Source: ADA Report (XML) Short Name: PERRPADA_XML DB package: PER_PERUSADA_XMLP_PKG BI Publisher 1=1 :c_business_group_name :c_disability_acc_id_flex_num :c_disability_id_flex_num :c_employee_number :c_full_name :c_job_name :c_lex_assign_order :c_lex_assign_where :c_location_code :c_organization :c_organization_hierarchy :c_report_type :p_business_group_id :p_conc_request_id :p_employee_number :p_job_id :p_location :p_org_structure_version_id :p_org_structure_version_id_1 :p_organization_id :p_person :p_position_name :p_registered_disabled :p_session_date :p_session_date1 :p_sort :p_view_job_reqs :p_view_position_reqs 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US BUSINESS_GROUP_ID 2 -10 :debugflag Char N US DebugFlag Debug Flag 3 10 :p_session_date Date select fnd_date.date_to_displaydate(effective_date) from fnd_sessions where session_id = fnd_profile.value('DB_SESSION_ID') Y US PER_DATES_STANDARD 4 20 :p_organization_id LOV Oracle HR_US_ADA_GRE Y select t.tax_unit_id id, t.name value, null description from hr_tax_units_v t where to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') between t.date_from and nvl(t.date_to,to_date('4712/12/31','YYYY/MM/DD')) and t.business_group_id=fnd_global.per_business_group_id order by t.name Y US GRE 5 30 :p_org_structure_version_id LOV Oracle HR_US_ADA_HIERARCHY Y select v.org_structure_version_id id, s.name value, null description from per_organization_structures s,per_org_structure_versions v where s.business_group_id= fnd_global.per_business_group_id and s.organization_structure_id=v.organization_structure_id and to_date(:$flex$.per_dates,'YYYY/MM/DD HH24:MI:SS') between v.date_from and nvl(v.date_to, to_date('4712/12/31','YYYY/MM/DD')) and exists (select 'x' from per_org_structure_elements e where e.organization_id_parent=:$flex$.gre) order by s.name Y US Organization Hierarchy 6 40 :p_person LOV Oracle HR_US_PERSON Y Y select ppf.person_id id, ppf.full_name||' (Person ID='||ppf.person_id||')' value, null description from per_people_f ppf,per_person_types ppt where ppf.business_group_id+0= fnd_global.per_business_group_id and ppt.person_type_id=ppf.person_type_id+0 and ppt.system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') and ppf.effective_start_date= (select max(effective_start_date) from per_people_f ppf1 where ppf1.person_id=ppf.person_id) order by nvl(ppf.order_name,ppf.full_name) US Employee 7 50 :p_employee_number LOV Oracle HR_US_PERSON_NUMBER Y select ppf.person_id id, ppf.employee_number value, ppf.full_name description from per_people ppf,per_person_types ppt where ppf.business_group_id+0= fnd_global.per_business_group_id and ppt.person_type_id=ppf.person_type_id and ppt.system_person_type in ('EMP','EMP_APL', 'EX_EMP','EX_EMP_APL') order by value,description US Employee Number 8 60 :p_job_id LOV Oracle PER_JOBS_RESTRICTED_BY_DATE_PARAM Y select j.job_id id, jt.name value, null description from per_jobs j,per_jobs_tl jt where j.business_group_id=fnd_global.per_business_group_id and j.date_from<=to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') and (j.date_to is null or (j.date_to is not null and j.date_to>=to_date(:$flex$.per_dates_standard, 'YYYY/MM/DD HH24:MI:SS') )) and jt.job_id=j.job_id and jt.language=userenv('LANG') order by value,description US Job 9 70 :p_location LOV Oracle HR_US_LOCATIONS Y select hl.location_id id, hl.location_code value, null description from hr_locations hl where location_use='HR' order by location_code US Location 10 80 :p_view_job_reqs LOV Oracle HR_US_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='YES_NO' order by value,description select meaning from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' US View Job Requirements 11 90 :p_view_position_reqs LOV Oracle HR_US_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='YES_NO' order by value,description select meaning from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' US View Position Requirements 12 100 :p_registered_disabled LOV Oracle HR_US_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='YES_NO' order by value,description select meaning from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' US View Reg. Disabled Only 13 110 :p_sort LOV Oracle HR_PERRPADA_SORT_BY Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=102857 order by ffvv.flex_value US Sort By