eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HXC_RESOURCE_RULES_V

Source

View Text - Preformatted


SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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_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.eligibility_criteria_type = 'PERSON' and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = ppx.person_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = hru.organization_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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)+1) =to_char( hou.organization_id) and substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1)-1)= to_char(pos.organization_structure_id) UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = hl.location_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = ppg.people_group_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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_tl pj, 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 pj.language= USERENV('LANG') and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = pj.job_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = pp.position_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = ppf.payroll_id UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, psf.assignment_number, hrr.pref_hierarchy_id, hph.name, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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_assignments_f psf, 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 = 'ASSIGNMENT' and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = psf.assignment_id and psf.assignment_type in ('E','C') UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, frt.responsibility_name, hrr.pref_hierarchy_id, hph.name, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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 fnd_responsibility_tl frt, 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 IN ('RESPONSIBILITY','PERST_RESPONSIBILITY') and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = frt.responsibility_id and frt.language= USERENV('LANG') UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, 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, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 = 'LOGIN' and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = to_char(fnd_global.login_id) UNION SELECT hrr.rowid row_id, hrr.resource_rule_id, hrr.name, hrr.business_group_id, hrr.legislation_code, hrr.eligibility_criteria_type, hxc_resource_rules_utils.get_criteria_meaning ( hrr.eligibility_criteria_type , hrr.business_group_id ) eligibility_criteria, hrr.eligibility_criteria_id, hxc_resource_rules_utils.get_meaning ( hrr.eligibility_criteria_type , hrr.eligibility_criteria_id , hrr.business_group_id ,bg.legislation_code ) meaning, hrr.pref_hierarchy_id, hph.name pref_hierarchy_name, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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, 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 ((SUBSTR(hrr.eligibility_criteria_type,1,3) = 'SCL') OR (SUBSTR(hrr.eligibility_criteria_type,1,6) = 'PEOPLE') OR (SUBSTR(hrr.eligibility_criteria_type,1,5) = 'GRADE')) 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, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, ppl.user_person_type, hrr.pref_hierarchy_id, hph.name pref_hierarchy_name, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, decode(hrr.end_date,hr_general.end_of_time,to_date(null),hrr.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_person_types ppt, per_person_types_tl ppl, 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_TYPE' and hrr.pref_hierarchy_id = hph.pref_hierarchy_id and hrr.eligibility_criteria_id = ppt.person_type_id and ppt.person_type_id=ppl.person_type_id and ppl.language = USERENV('LANG') and ppt.business_group_id = hrr.business_group_id UNION SELECT hrr.ROWID row_id, hrr.resource_rule_id, hrr.NAME, hrr.business_group_id, hrr.legislation_code, hrr.eligibility_criteria_type, hrl.meaning, hrr.eligibility_criteria_id, hll.meaning, hrr.pref_hierarchy_id, hph.NAME, hrr.rule_evaluation_order, hrr.resource_type, hrr.start_date, DECODE ( hrr.end_date, hr_general.end_of_time, TO_DATE (NULL), hrr.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, 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 ( ( hrr.eligibility_criteria_type = 'EMP_CATEGORY' AND hll.lookup_type = 'EMPLOYEE_CATG' ) OR ( hrr.eligibility_criteria_type = 'ASGN_CATEGORY' AND ( hll.lookup_type = 'EMP_CAT' OR hll.lookup_type = 'CWK_ASG_CATEGORY' ) ) )

View Text - HTML Formatted

SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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_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.ELIGIBILITY_CRITERIA_TYPE = 'PERSON'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = PPX.PERSON_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = HRU.ORGANIZATION_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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)+1) =TO_CHAR( HOU.ORGANIZATION_ID)
AND SUBSTR(HRR.ELIGIBILITY_CRITERIA_ID
, 1
, INSTR(HRR.ELIGIBILITY_CRITERIA_ID
, '-'
, 1)-1)= TO_CHAR(POS.ORGANIZATION_STRUCTURE_ID) UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = HL.LOCATION_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = PPG.PEOPLE_GROUP_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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_TL PJ
, 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 PJ.LANGUAGE= USERENV('LANG')
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = PJ.JOB_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = PP.POSITION_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = PPF.PAYROLL_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PSF.ASSIGNMENT_NUMBER
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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_ASSIGNMENTS_F PSF
, 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 = 'ASSIGNMENT'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = PSF.ASSIGNMENT_ID
AND PSF.ASSIGNMENT_TYPE IN ('E'
, 'C') UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, FRT.RESPONSIBILITY_NAME
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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 FND_RESPONSIBILITY_TL FRT
, 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 IN ('RESPONSIBILITY'
, 'PERST_RESPONSIBILITY')
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = FRT.RESPONSIBILITY_ID
AND FRT.LANGUAGE= USERENV('LANG') UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, 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
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 = 'LOGIN'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = TO_CHAR(FND_GLOBAL.LOGIN_ID) UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HXC_RESOURCE_RULES_UTILS.GET_CRITERIA_MEANING ( HRR.ELIGIBILITY_CRITERIA_TYPE
, HRR.BUSINESS_GROUP_ID ) ELIGIBILITY_CRITERIA
, HRR.ELIGIBILITY_CRITERIA_ID
, HXC_RESOURCE_RULES_UTILS.GET_MEANING ( HRR.ELIGIBILITY_CRITERIA_TYPE
, HRR.ELIGIBILITY_CRITERIA_ID
, HRR.BUSINESS_GROUP_ID
, BG.LEGISLATION_CODE ) MEANING
, HRR.PREF_HIERARCHY_ID
, HPH.NAME PREF_HIERARCHY_NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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
, 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 ((SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 3) = 'SCL') OR (SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 6) = 'PEOPLE') OR (SUBSTR(HRR.ELIGIBILITY_CRITERIA_TYPE
, 1
, 5) = 'GRADE'))
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
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, PPL.USER_PERSON_TYPE
, HRR.PREF_HIERARCHY_ID
, HPH.NAME PREF_HIERARCHY_NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, DECODE(HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE(NULL)
, HRR.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_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPL
, 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_TYPE'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID=PPL.PERSON_TYPE_ID
AND PPL.LANGUAGE = USERENV('LANG')
AND PPT.BUSINESS_GROUP_ID = HRR.BUSINESS_GROUP_ID UNION SELECT HRR.ROWID ROW_ID
, HRR.RESOURCE_RULE_ID
, HRR.NAME
, HRR.BUSINESS_GROUP_ID
, HRR.LEGISLATION_CODE
, HRR.ELIGIBILITY_CRITERIA_TYPE
, HRL.MEANING
, HRR.ELIGIBILITY_CRITERIA_ID
, HLL.MEANING
, HRR.PREF_HIERARCHY_ID
, HPH.NAME
, HRR.RULE_EVALUATION_ORDER
, HRR.RESOURCE_TYPE
, HRR.START_DATE
, DECODE ( HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, HRR.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
, 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 ( ( HRR.ELIGIBILITY_CRITERIA_TYPE = 'EMP_CATEGORY'
AND HLL.LOOKUP_TYPE = 'EMPLOYEE_CATG' ) OR ( HRR.ELIGIBILITY_CRITERIA_TYPE = 'ASGN_CATEGORY'
AND ( HLL.LOOKUP_TYPE = 'EMP_CAT' OR HLL.LOOKUP_TYPE = 'CWK_ASG_CATEGORY' ) ) )