VIEW: APPS.PER_APP_ASGS_V

Source

View Text - Preformatted

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'))

View Text - HTML Formatted

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'))