View: HXC_RESOURCE_RULES_V

Product:	HXC - Time and Labor Engine
Description:
Implementation/DBA Data:	APPS.HXC_RESOURCE_RULES_V

View Text

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 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 = 'RESPONSIBILITY'
AND HRR.PREF_HIERARCHY_ID = HPH.PREF_HIERARCHY_ID
AND HRR.ELIGIBILITY_CRITERIA_ID = FRT.RESPONSIBILITY_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
, 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' ) ) )

Columns