View: PER_APP_ASGS_V

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_APP_ASGS_V

View Text

SELECT A.ROWID
, P.PERSON_ID
, A.APPLICATION_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.APPLICANT_NUMBER
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID)
, LOCTL.LOCATION_CODE
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS)
, P2.FULL_NAME
, AP.DATE_RECEIVED
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.VACANCY_ID
, A.RECRUITMENT_ACTIVITY_ID
, PG.GROUP_NAME
, A.PEOPLE_GROUP_ID
, A.ORGANIZATION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, A.POSITION_ID
, HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID)
, GRT.NAME
, G.GRADE_ID
, G.GRADE_DEFINITION_ID
, JBT.NAME
, J.JOB_ID
, J.JOB_DEFINITION_ID
, A.LOCATION_ID
, A.ASSIGNMENT_STATUS_TYPE_ID
, AST.PER_SYSTEM_STATUS
, A.RECRUITER_ID
, PR.NAME
, PV.NAME
FROM PER_GRADES_TL GRT
, PER_GRADES G
, PER_JOBS_TL JBT
, PER_JOBS J
, PAY_PEOPLE_GROUPS PG
, PER_ALL_PEOPLE_F P2
, HR_LOCATIONS_NO_JOIN LOC
, HR_LOCATIONS_ALL_TL LOCTL
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, FND_SESSIONS S
, PER_APPLICATIONS AP
, PER_ALL_ASSIGNMENTS_F A
, PER_PEOPLE_F P
, PER_REQUISITIONS PR
, PER_VACANCIES PV
WHERE P.PERSON_ID = A.PERSON_ID
AND A.ASSIGNMENT_TYPE = 'A'
AND A.APPLICATION_ID = AP.APPLICATION_ID
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+)
AND A.JOB_ID = J.JOB_ID (+)
AND JBT.JOB_ID (+) = J.JOB_ID
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND A.GRADE_ID = G.GRADE_ID (+)
AND GRT.GRADE_ID (+) = G.GRADE_ID
AND GRT.LANGUAGE (+) = USERENV('LANG')
AND A.LOCATION_ID = LOC.LOCATION_ID (+)
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND A.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND A.BUSINESS_GROUP_ID + 0 = ASTA.BUSINESS_GROUP_ID(+) + 0
AND A.RECRUITER_ID = P2.PERSON_ID (+)
AND A.VACANCY_ID = PV.VACANCY_ID (+)
AND PV.REQUISITION_ID = PR.REQUISITION_ID (+)
AND S.SESSION_ID = USERENV('SESSIONID')
AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND ( P2.PERSON_ID IS NULL OR S.EFFECTIVE_DATE BETWEEN P2.EFFECTIVE_START_DATE
AND P2.EFFECTIVE_END_DATE )
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV('LANG')
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, ASTATL.LANGUAGE) = DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, USERENV('LANG'))
AND LOC.LOCATION_ID = LOCTL.LOCATION_ID (+)
AND DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, LOCTL.LANGUAGE) = DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))

Columns