View: HXC_RESOURCE_RULES_PERSON_V

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

View Text

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 = 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 = 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 = 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 = 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
, DECODE (HRR.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, 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 = 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 = 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 = 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 = 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 = 'RESPONSIBILITY'
AND HRR.ELIGIBILITY_CRITERIA_ID = 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 ) )

Columns