VIEW: APPS.HR_ASSIGNMENT_SET_AMENDMENTS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(ASG PP) INDEX(ASG PER_ASSIGNMENTS_F_PK) INDEX(PP PER_PEOPLE_F_PK) */ AA.ROWID ROW_ID , AA.ASSIGNMENT_ID , AA.ASSIGNMENT_SET_ID , UPPER(HR1.MEANING) INCLUDE_OR_EXCLUDE , AA.INCLUDE_OR_EXCLUDE INC_EXC_CODE , AA.LAST_UPDATE_DATE , AA.LAST_UPDATED_BY , AA.LAST_UPDATE_LOGIN , AA.CREATED_BY , AA.CREATION_DATE , PP.FULL_NAME , ASG.ASSIGNMENT_NUMBER , NVL(PP.CURRENT_EMPLOYEE_FLAG,'N') CURRENT_EMPLOYEE_FLAG , HR.MEANING CURRENT_EMP_SWITCH FROM HR_LOOKUPS HR1, HR_ASSIGNMENT_SET_AMENDMENTS AA, PER_ALL_ASSIGNMENTS_F ASG, FND_SESSIONS F, PER_ALL_PEOPLE_F PP, HR_LOOKUPS HR WHERE F.SESSION_ID = USERENV('SESSIONID') AND F.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND AA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PP.PERSON_ID AND PP.CURRENT_EMPLOYEE_FLAG = HR.LOOKUP_CODE AND PP.CURRENT_EMPLOYEE_FLAG = 'Y' AND HR.LOOKUP_TYPE = 'YES_NO_ALL' AND HR.ENABLED_FLAG = 'Y' AND HR1.LOOKUP_TYPE = 'INCLUDE_EXCLUDE' AND HR1.LOOKUP_CODE = AA.INCLUDE_OR_EXCLUDE AND HR1.ENABLED_FLAG = 'Y' union all SELECT /*+ ORDERED USE_NL(ASG PP) INDEX(ASG PER_ASSIGNMENTS_F_PK) INDEX(PP PER_PEOPLE_F_PK) */ AA.ROWID ROW_ID , AA.ASSIGNMENT_ID , AA.ASSIGNMENT_SET_ID , UPPER(HR1.MEANING) INCLUDE_OR_EXCLUDE , AA.INCLUDE_OR_EXCLUDE INC_EXC_CODE , AA.LAST_UPDATE_DATE , AA.LAST_UPDATED_BY , AA.LAST_UPDATE_LOGIN , AA.CREATED_BY , AA.CREATION_DATE , PP.FULL_NAME , ASG.ASSIGNMENT_NUMBER , NVL(PP.CURRENT_EMPLOYEE_FLAG,'N') CURRENT_EMPLOYEE_FLAG , HR.MEANING CURRENT_EMP_SWITCH FROM HR_LOOKUPS HR1, HR_ASSIGNMENT_SET_AMENDMENTS AA, PER_ALL_ASSIGNMENTS_F ASG, FND_SESSIONS F, PER_ALL_PEOPLE_F PP, HR_LOOKUPS HR WHERE F.SESSION_ID = USERENV('SESSIONID') AND AA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PP.PERSON_ID AND HR.LOOKUP_CODE = 'N' AND PP.CURRENT_EMPLOYEE_FLAG IS NULL AND HR.LOOKUP_TYPE = 'YES_NO_ALL' AND HR.ENABLED_FLAG = 'Y' AND HR1.LOOKUP_TYPE = 'INCLUDE_EXCLUDE' AND HR1.LOOKUP_CODE = AA.INCLUDE_OR_EXCLUDE AND HR1.ENABLED_FLAG = 'Y' AND ASG.EFFECTIVE_END_DATE = (SELECT MAX(AA2.EFFECTIVE_END_DATE) FROM PER_ALL_ASSIGNMENTS_F AA2 WHERE ASG.ASSIGNMENT_ID = AA2.ASSIGNMENT_ID AND ASG.PERSON_ID = PP.PERSON_ID) AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(ASG PP) INDEX(ASG PER_ASSIGNMENTS_F_PK) INDEX(PP PER_PEOPLE_F_PK) */ AA.ROWID ROW_ID
, AA.ASSIGNMENT_ID
, AA.ASSIGNMENT_SET_ID
, UPPER(HR1.MEANING) INCLUDE_OR_EXCLUDE
, AA.INCLUDE_OR_EXCLUDE INC_EXC_CODE
, AA.LAST_UPDATE_DATE
, AA.LAST_UPDATED_BY
, AA.LAST_UPDATE_LOGIN
, AA.CREATED_BY
, AA.CREATION_DATE
, PP.FULL_NAME
, ASG.ASSIGNMENT_NUMBER
, NVL(PP.CURRENT_EMPLOYEE_FLAG
, 'N') CURRENT_EMPLOYEE_FLAG
, HR.MEANING CURRENT_EMP_SWITCH
FROM HR_LOOKUPS HR1
, HR_ASSIGNMENT_SET_AMENDMENTS AA
, PER_ALL_ASSIGNMENTS_F ASG
, FND_SESSIONS F
, PER_ALL_PEOPLE_F PP
, HR_LOOKUPS HR
WHERE F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND AA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PP.PERSON_ID
AND PP.CURRENT_EMPLOYEE_FLAG = HR.LOOKUP_CODE
AND PP.CURRENT_EMPLOYEE_FLAG = 'Y'
AND HR.LOOKUP_TYPE = 'YES_NO_ALL'
AND HR.ENABLED_FLAG = 'Y'
AND HR1.LOOKUP_TYPE = 'INCLUDE_EXCLUDE'
AND HR1.LOOKUP_CODE = AA.INCLUDE_OR_EXCLUDE
AND HR1.ENABLED_FLAG = 'Y' UNION ALL SELECT /*+ ORDERED USE_NL(ASG PP) INDEX(ASG PER_ASSIGNMENTS_F_PK) INDEX(PP PER_PEOPLE_F_PK) */ AA.ROWID ROW_ID
, AA.ASSIGNMENT_ID
, AA.ASSIGNMENT_SET_ID
, UPPER(HR1.MEANING) INCLUDE_OR_EXCLUDE
, AA.INCLUDE_OR_EXCLUDE INC_EXC_CODE
, AA.LAST_UPDATE_DATE
, AA.LAST_UPDATED_BY
, AA.LAST_UPDATE_LOGIN
, AA.CREATED_BY
, AA.CREATION_DATE
, PP.FULL_NAME
, ASG.ASSIGNMENT_NUMBER
, NVL(PP.CURRENT_EMPLOYEE_FLAG
, 'N') CURRENT_EMPLOYEE_FLAG
, HR.MEANING CURRENT_EMP_SWITCH
FROM HR_LOOKUPS HR1
, HR_ASSIGNMENT_SET_AMENDMENTS AA
, PER_ALL_ASSIGNMENTS_F ASG
, FND_SESSIONS F
, PER_ALL_PEOPLE_F PP
, HR_LOOKUPS HR
WHERE F.SESSION_ID = USERENV('SESSIONID')
AND AA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PP.PERSON_ID
AND HR.LOOKUP_CODE = 'N'
AND PP.CURRENT_EMPLOYEE_FLAG IS NULL
AND HR.LOOKUP_TYPE = 'YES_NO_ALL'
AND HR.ENABLED_FLAG = 'Y'
AND HR1.LOOKUP_TYPE = 'INCLUDE_EXCLUDE'
AND HR1.LOOKUP_CODE = AA.INCLUDE_OR_EXCLUDE
AND HR1.ENABLED_FLAG = 'Y'
AND ASG.EFFECTIVE_END_DATE = (SELECT MAX(AA2.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F AA2
WHERE ASG.ASSIGNMENT_ID = AA2.ASSIGNMENT_ID
AND ASG.PERSON_ID = PP.PERSON_ID)
AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE