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 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' 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 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 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 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' 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 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' 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 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' 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 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' 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 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' 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 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 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 IN ('RESPONSIBILITY','PERST_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 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 ( ( 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
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' 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
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 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
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' 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
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' 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
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' 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
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' 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
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' 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
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 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 IN ('RESPONSIBILITY'
, 'PERST_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
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 ( ( 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 ) )