VIEW: APPS.HXC_RESOURCE_RULES_PERSON_V

Source

View Text - Preformatted

SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, ppx.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, to_char(null), hrr.pref_hierarchy_id, hph.name, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, ppx.effective_start_date, ppx.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hxc_pref_hierarchies hph, hr_standard_lookups hrl, per_people_f ppx, hxc_resource_rules hrr WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'ALL_PEOPLE' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id IS NULL AND ppx.business_group_id = Nvl(hph.business_group_id, ppx.business_group_id) AND ppx.business_group_id = Nvl(hrr.business_group_id, ppx.business_group_id) UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, ppx.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, ppx.full_name, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, ppx.effective_start_date, ppx.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'PERSON' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(ppx.person_id) UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, hru.NAME, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hr_organization_units hru, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_SESSIONS sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'ORGANIZATION' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(hru.organization_id) AND hru.organization_id = paf.organization_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, pos.NAME || ' - ' || hou.NAME, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hr_organization_units hou, per_organization_structures pos, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'ROLLUP' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND Substr(hrr.eligibility_criteria_id, Instr(hrr.eligibility_criteria_id, '-') + 1) = To_char(hou.organization_id) AND Substr(hrr.eligibility_criteria_id, 1, Instr(hrr.eligibility_criteria_id, '-') - 1) = To_char(pos.organization_structure_id) AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date AND hou.business_group_id = pos.business_group_id AND pos.business_group_id = paf.business_group_id AND Substr(hrr.eligibility_criteria_id, Instr(hrr.eligibility_criteria_id, '-') + 1) IN (SELECT pose.organization_id_parent FROM per_org_structure_elements pose START WITH organization_id_child = paf.organization_id AND pose.org_structure_version_id = hxc_preference_evaluation.Return_version_id( hxc_preference_evaluation.Check_number (Substr(hrr.eligibility_criteria_id, 1, Instr(hrr.eligibility_criteria_id, '-', 1, 1) - 1)), hrr.eligibility_criteria_type) CONNECT BY PRIOR organization_id_parent = organization_id_child AND pose.org_structure_version_id = hxc_preference_evaluation.Return_version_id( hxc_preference_evaluation.Check_number (Substr(hrr.eligibility_criteria_id, 1, Instr(hrr.eligibility_criteria_id, '-', 1, 1) - 1)), hrr.eligibility_criteria_type) UNION SELECT organization_id FROM hr_all_organization_units WHERE organization_id = paf.organization_id) AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, hl.location_code, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hr_locations hl, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'LOCATION' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(hl.location_id) AND hl.location_id = paf.location_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, ppg.group_name, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM pay_people_groups ppg, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'PEOPLE_GROUP' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(ppg.people_group_id) AND ppg.people_group_id = paf.people_group_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, ppx.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, ppl.user_person_type, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, ptu.effective_start_date, ptu.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM per_people_f ppx, per_person_types ppt, per_person_types_tl ppl, per_person_type_usages_f ptu, hxc_pref_hierarchies hph, hr_standard_lookups hrl, per_assignments_f paf, hxc_resource_rules hrr WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'PERSON_TYPE' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(ppt.person_type_id) AND ppt.person_type_id = ppl.person_type_id AND ppl.language = Userenv('LANG') AND ppt.person_type_id = ptu.person_type_id AND ptu.person_id = ppx.person_id AND ppt.business_group_id = hrr.business_group_id AND paf.person_id = ppx.person_id AND paf.effective_start_date <= ptu.effective_end_date AND paf.effective_end_date >= ptu.effective_start_date AND paf.effective_start_date <= ppx.effective_end_date AND paf.effective_end_date >= ppx.effective_start_date AND ppt.system_person_type IN ( 'EMP', 'EX_EMP', 'EMP_APL', 'EX_EMP_APL', 'CWK', 'EX_CWK' ) AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, pj.NAME, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM per_jobs pj, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'JOB' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(pj.job_id) AND pj.job_id = paf.job_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, pp.NAME, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM per_positions pp, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, fnd_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'POSITION' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(pp.position_id) AND pp.position_id = paf.position_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, ppf.payroll_name, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM pay_payrolls_f ppf, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'PAYROLL' AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = To_char(ppf.payroll_id) AND ppf.payroll_id = paf.payroll_id AND paf.person_id = ppx.person_id AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, Decode (hrr.eligibility_criteria_type, 'ASSIGNMENT', hrl.meaning, hxc_resource_rules_utils.Get_criteria_meaning ( hrr.eligibility_criteria_type, hrr.business_group_id)) meaning, hrr.eligibility_criteria_id, Decode (hrr.eligibility_criteria_type, 'ASSIGNMENT', paf.assignment_number, hxc_resource_rules_utils.Get_meaning (hrr.eligibility_criteria_type, hrr.eligibility_criteria_id, hrr.business_group_id, bg.legislation_code)), hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM per_business_groups bg, per_assignments_f paf, per_people_f ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND paf.assignment_type IN ( 'E', 'C' ) AND paf.primary_flag = 'Y' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND paf.person_id = ppx.person_id AND ( ( hrr.eligibility_criteria_id = To_char(paf.assignment_id) AND hrr.eligibility_criteria_type = 'ASSIGNMENT' ) OR ( Decode (Substr(hrr.eligibility_criteria_type, 1, 3), 'SCL', Decode (paf.soft_coding_keyflex_id, NULL, -1, hxc_resource_rules_utils.Chk_flex_valid ( 'SCL', paf.soft_coding_keyflex_id, Substr (hrr.eligibility_criteria_type, 5), hrr.eligibility_criteria_id)), -1) = 1 ) OR ( Decode (Substr(hrr.eligibility_criteria_type, 1, 6), 'PEOPLE' , Decode (paf.people_group_id, NULL, -1, hxc_resource_rules_utils.Chk_flex_valid ( 'PEOPLE' , paf.people_group_id, Substr( hrr.eligibility_criteria_type, 8), hrr.eligibility_criteria_id)), -1) = 1 ) OR ( Decode (Substr(hrr.eligibility_criteria_type, 1, 5), 'GRADE', Decode (paf.grade_id, NULL, -1, hxc_resource_rules_utils.Chk_flex_valid ( 'GRADE', paf.grade_id, Substr ( hrr.eligibility_criteria_type, 7), hrr.eligibility_criteria_id)), -1) = 1 ) ) AND hrr.business_group_id = bg.business_group_id AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, ppx.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, frv.responsibility_name, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, ppx.effective_start_date, ppx.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hxc_pref_hierarchies hph, hr_standard_lookups hrl, per_people_f ppx, hxc_resource_rules hrr, fnd_responsibility_vl frv, fnd_user_resp_groups fur, fnd_user fu WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.eligibility_criteria_type = 'RESPONSIBILITY' AND hrr.eligibility_criteria_id = To_char(frv.responsibility_id) AND frv.responsibility_id = fur.responsibility_id AND fur.user_id = fu.user_id AND fu.employee_id = ppx.person_id AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND ppx.business_group_id = Nvl(hph.business_group_id, ppx.business_group_id) AND ppx.business_group_id = Nvl(hrr.business_group_id, ppx.business_group_id) UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, paf.person_id, ppx.full_name, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, hll.lookup_code, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, hrr.end_date, paf.effective_start_date, paf.effective_end_date, hrr.object_version_number, hrr.created_by, hrr.creation_date, hrr.last_updated_by, hrr.last_update_date, hrr.last_update_login FROM hr_lookups hll, per_assignments_f paf, per_people_x ppx, hxc_pref_hierarchies hph, hr_standard_lookups hrl, hxc_resource_rules hrr, FND_sessions sess WHERE hrl.lookup_type = 'HXC_ELIG_CRITERIA_TYPE' AND hrl.lookup_code = hrr.eligibility_criteria_type AND hrl.application_id = 809 AND hrr.pref_hierarchy_id = hph.pref_hierarchy_id AND hrr.eligibility_criteria_id = hll.lookup_code AND paf.person_id = ppx.person_id AND paf.primary_flag = 'Y' AND sess.session_id = USERENV('SESSIONID') AND sess.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND sess.effective_date BETWEEN ppx.effective_start_date AND ppx.effective_end_date AND ( ( hrr.eligibility_criteria_type = 'EMP_CATEGORY' AND hll.lookup_type = 'EMPLOYEE_CATG' AND hll.lookup_code = paf.employee_category ) OR ( hrr.eligibility_criteria_type = 'ASGN_CATEGORY' AND hll.lookup_type = 'EMP_CAT' AND paf.assignment_type = 'E' AND hll.lookup_code = paf.employment_category ) OR ( hrr.eligibility_criteria_type = 'ASGN_CATEGORY' AND hll.lookup_type = 'CWK_ASG_CATEGORY' AND paf.assignment_type = 'C' AND hll.lookup_code = paf.employment_category ) )

View Text - HTML Formatted

SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PPX.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, TO_CHAR(NULL)
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PPX.EFFECTIVE_START_DATE
, PPX.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, PER_PEOPLE_F PPX
, HXC_RESOURCE_RULES HRR
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'ALL_PEOPLE'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID IS NULL
AND PPX.BUSINESS_GROUP_ID = NVL(HPH.BUSINESS_GROUP_ID
, PPX.BUSINESS_GROUP_ID)
AND PPX.BUSINESS_GROUP_ID = NVL(HRR.BUSINESS_GROUP_ID
, PPX.BUSINESS_GROUP_ID) UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PPX.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PPX.FULL_NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PPX.EFFECTIVE_START_DATE
, PPX.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'PERSON'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PPX.PERSON_ID) UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, HRU.NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HR_ORGANIZATION_UNITS HRU
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'ORGANIZATION'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(HRU.ORGANIZATION_ID)
AND HRU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, POS.NAME || ' - ' || HOU.NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HR_ORGANIZATION_UNITS HOU
, PER_ORGANIZATION_STRUCTURES POS
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'ROLLUP'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-') + 1) = TO_CHAR(HOU.ORGANIZATION_ID)
AND SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, 1
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-') - 1) = TO_CHAR(POS.ORGANIZATION_STRUCTURE_ID)
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE
AND HOU.BUSINESS_GROUP_ID = POS.BUSINESS_GROUP_ID
AND POS.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
AND SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-') + 1) IN (SELECT POSE.ORGANIZATION_ID_PARENT
FROM PER_ORG_STRUCTURE_ELEMENTS POSE START WITH ORGANIZATION_ID_CHILD = PAF.ORGANIZATION_ID
AND POSE.ORG_STRUCTURE_VERSION_ID = HXC_PREFERENCE_EVALUATION.RETURN_VERSION_ID( HXC_PREFERENCE_EVALUATION.CHECK_NUMBER (SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, 1
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-'
, 1
, 1) - 1))
, HRR.ELIGIBILITY_CRITERIA_TYPE) CONNECT BY PRIOR ORGANIZATION_ID_PARENT = ORGANIZATION_ID_CHILD
AND POSE.ORG_STRUCTURE_VERSION_ID = HXC_PREFERENCE_EVALUATION.RETURN_VERSION_ID( HXC_PREFERENCE_EVALUATION.CHECK_NUMBER (SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, 1
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-'
, 1
, 1) - 1))
, HRR.ELIGIBILITY_CRITERIA_TYPE) UNION SELECT ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = PAF.ORGANIZATION_ID)
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y' UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, HL.LOCATION_CODE
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HR_LOCATIONS HL
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'LOCATION'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(HL.LOCATION_ID)
AND HL.LOCATION_ID = PAF.LOCATION_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PPG.GROUP_NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PAY_PEOPLE_GROUPS PPG
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'PEOPLE_GROUP'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PPG.PEOPLE_GROUP_ID)
AND PPG.PEOPLE_GROUP_ID = PAF.PEOPLE_GROUP_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PPX.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PPL.USER_PERSON_TYPE
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PTU.EFFECTIVE_START_DATE
, PTU.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PER_PEOPLE_F PPX
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPL
, PER_PERSON_TYPE_USAGES_F PTU
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, PER_ASSIGNMENTS_F PAF
, HXC_RESOURCE_RULES HRR
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'PERSON_TYPE'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PPT.PERSON_TYPE_ID)
AND PPT.PERSON_TYPE_ID = PPL.PERSON_TYPE_ID
AND PPL.LANGUAGE = USERENV('LANG')
AND PPT.PERSON_TYPE_ID = PTU.PERSON_TYPE_ID
AND PTU.PERSON_ID = PPX.PERSON_ID
AND PPT.BUSINESS_GROUP_ID = HRR.BUSINESS_GROUP_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.EFFECTIVE_START_DATE <= PTU.EFFECTIVE_END_DATE
AND PAF.EFFECTIVE_END_DATE >= PTU.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_START_DATE <= PPX.EFFECTIVE_END_DATE
AND PAF.EFFECTIVE_END_DATE >= PPX.EFFECTIVE_START_DATE
AND PPT.SYSTEM_PERSON_TYPE IN ( 'EMP'
, 'EX_EMP'
, 'EMP_APL'
, 'EX_EMP_APL'
, 'CWK'
, 'EX_CWK' )
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y' UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PJ.NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PER_JOBS PJ
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'JOB'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PJ.JOB_ID)
AND PJ.JOB_ID = PAF.JOB_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PP.NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PER_POSITIONS PP
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'POSITION'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PP.POSITION_ID)
AND PP.POSITION_ID = PAF.POSITION_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PPF.PAYROLL_NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PAY_PAYROLLS_F PPF
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'PAYROLL'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PPF.PAYROLL_ID)
AND PPF.PAYROLL_ID = PAF.PAYROLL_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, DECODE (HRR.ELIGIBILITY_CRITERIA_TYPE
, 'ASSIGNMENT'
, HRL.MEANING
, HXC_RESOURCE_RULES_UTILS.GET_CRITERIA_MEANING ( HRR.ELIGIBILITY_CRITERIA_TYPE
, HRR.BUSINESS_GROUP_ID)) MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, DECODE (HRR.ELIGIBILITY_CRITERIA_TYPE
, 'ASSIGNMENT'
, PAF.ASSIGNMENT_NUMBER
, HXC_RESOURCE_RULES_UTILS.GET_MEANING (HRR.ELIGIBILITY_CRITERIA_TYPE
, HRR.ELIGIBILITY_CRITERIA_ID
, HRR.BUSINESS_GROUP_ID
, BG.LEGISLATION_CODE))
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM PER_BUSINESS_GROUPS BG
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND PAF.ASSIGNMENT_TYPE IN ( 'E'
, 'C' )
AND PAF.PRIMARY_FLAG = 'Y'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND PAF.PERSON_ID = PPX.PERSON_ID
AND ( ( HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(PAF.ASSIGNMENT_ID)
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'ASSIGNMENT' ) OR ( DECODE (SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 3)
, 'SCL'
, DECODE (PAF.SOFT_CODING_KEYFLEX_ID
, NULL
, -1
, HXC_RESOURCE_RULES_UTILS.CHK_FLEX_VALID ( 'SCL'
, PAF.SOFT_CODING_KEYFLEX_ID
, SUBSTR (HRR.ELIGIBILITY_CRITERIA_TYPE
, 5)
, HRR.ELIGIBILITY_CRITERIA_ID))
, -1) = 1 ) OR ( DECODE (SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 6)
, 'PEOPLE'
, DECODE (PAF.PEOPLE_GROUP_ID
, NULL
, -1
, HXC_RESOURCE_RULES_UTILS.CHK_FLEX_VALID ( 'PEOPLE'
, PAF.PEOPLE_GROUP_ID
, SUBSTR( HRR.ELIGIBILITY_CRITERIA_TYPE
, 8)
, HRR.ELIGIBILITY_CRITERIA_ID))
, -1) = 1 ) OR ( DECODE (SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 5)
, 'GRADE'
, DECODE (PAF.GRADE_ID
, NULL
, -1
, HXC_RESOURCE_RULES_UTILS.CHK_FLEX_VALID ( 'GRADE'
, PAF.GRADE_ID
, SUBSTR ( HRR.ELIGIBILITY_CRITERIA_TYPE
, 7)
, HRR.ELIGIBILITY_CRITERIA_ID))
, -1) = 1 ) )
AND HRR.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PPX.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, FRV.RESPONSIBILITY_NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PPX.EFFECTIVE_START_DATE
, PPX.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, PER_PEOPLE_F PPX
, HXC_RESOURCE_RULES HRR
, FND_RESPONSIBILITY_VL FRV
, FND_USER_RESP_GROUPS FUR
, FND_USER FU
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.ELIGIBILITY_CRITERIA_TYPE = 'RESPONSIBILITY'
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(FRV.RESPONSIBILITY_ID)
AND FRV.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
AND FUR.USER_ID = FU.USER_ID
AND FU.EMPLOYEE_ID = PPX.PERSON_ID
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND PPX.BUSINESS_GROUP_ID = NVL(HPH.BUSINESS_GROUP_ID
, PPX.BUSINESS_GROUP_ID)
AND PPX.BUSINESS_GROUP_ID = NVL(HRR.BUSINESS_GROUP_ID
, PPX.BUSINESS_GROUP_ID) UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, PAF.PERSON_ID
, PPX.FULL_NAME
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, HLL.LOOKUP_CODE
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, HRR.END_DATE
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, HRR.OBJECT_VERSION_NUMBER
, HRR.CREATED_BY
, HRR.CREATION_DATE
, HRR.LAST_UPDATED_BY
, HRR.LAST_UPDATE_DATE
, HRR.LAST_UPDATE_LOGIN
FROM HR_LOOKUPS HLL
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_X PPX
, HXC_PREF_HIERARCHIES HPH
, HR_STANDARD_LOOKUPS HRL
, HXC_RESOURCE_RULES HRR
, FND_SESSIONS SESS
WHERE HRL.LOOKUP_TYPE = 'HXC_ELIG_CRITERIA_TYPE'
AND HRL.LOOKUP_CODE = HRR.ELIGIBILITY_CRITERIA_TYPE
AND HRL.APPLICATION_ID = 809
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = HLL.LOOKUP_CODE
AND PAF.PERSON_ID = PPX.PERSON_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SESS.EFFECTIVE_DATE BETWEEN PPX.EFFECTIVE_START_DATE
AND PPX.EFFECTIVE_END_DATE
AND ( ( HRR.ELIGIBILITY_CRITERIA_TYPE = 'EMP_CATEGORY'
AND HLL.LOOKUP_TYPE = 'EMPLOYEE_CATG'
AND HLL.LOOKUP_CODE = PAF.EMPLOYEE_CATEGORY ) OR ( HRR.ELIGIBILITY_CRITERIA_TYPE = 'ASGN_CATEGORY'
AND HLL.LOOKUP_TYPE = 'EMP_CAT'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND HLL.LOOKUP_CODE = PAF.EMPLOYMENT_CATEGORY ) OR ( HRR.ELIGIBILITY_CRITERIA_TYPE = 'ASGN_CATEGORY'
AND HLL.LOOKUP_TYPE = 'CWK_ASG_CATEGORY'
AND PAF.ASSIGNMENT_TYPE = 'C'
AND HLL.LOOKUP_CODE = PAF.EMPLOYMENT_CATEGORY ) )