View: HRFV_JOB_APPLICATIONS

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_JOB_APPLICATIONS

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, VAC.NAME VACANCY_NAME
, APP.DATE_RECEIVED START_DATE
, APP.DATE_END END_DATE
, APP.CURRENT_EMPLOYER CURRENT_EMPLOYER
, APP.PROJECTED_HIRE_DATE PROJECTED_HIRE_DATE
, DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, DECODE(LAT.ASSIGNMENT_TYPE
, 'A'
, DECODE(Y1.PER_SYSTEM_STATUS
, 'TERM_APL'
, Y1T.USER_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, XT.USER_STATUS
, X1T.USER_STATUS))
, 'E'
, DECODE(Y2.PER_SYSTEM_STATUS
, 'TERM_ASSIGN'
, Y2T.USER_STATUS
, X2T.USER_STATUS))
, NVL(YT.USER_STATUS
, XT.USER_STATUS)) APPLICATION_STATUS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'ACTIVE_APL'
, 1
, 0) NEW_APPLICANTS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'INTERVIEW1'
, 1
, 0) INTERVIEW1
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'INTERVIEW2'
, 1
, 0) INTERVIEW2
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, DECODE(LAT.ASSIGNMENT_TYPE
, 'A'
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'TERM_APL'
, 1
, 0) TERMINATIONS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'OFFER'
, 1
, 0) OFFERS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'ACCEPTED'
, 1
, 0) ACCEPTS
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, DECODE(GREATEST(NVL(PES.ACTUAL_TERMINATION_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 0
, 1)
, 0) STILL_CURRENT_EMPLOYEE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, 'Y'
, 'N')) HIRED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(GREATEST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'N'
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, 'N'
, 'Y'))) APPLICATION_TERMINATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, RAC.NAME RECRUITMENT_ACTIVITY_NAME
, REF.FULL_NAME REFERRING_EMPLOYEE_NAME
, REF.EMPLOYEE_NUMBER REFERRING_EMPLOYEE_NUMBER
, SOUT.NAME SOURCE_ORGANIZATION_NAME
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, DECODE(APP.TERMINATION_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TERM_APL_REASON'
, APP.TERMINATION_REASON)) TERMINATION_REASON
, REC.FULL_NAME RECRUITER_NAME
, ORGT.NAME ORGANIZATION_NAME
, POS.NAME POSITION_NAME
, JOB.NAME JOB_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GRD.NAME GRADE_NAME
, PGR.GROUP_NAME PEOPLE_GROUP
, '_DF:PER:PER_ASSIGNMENTS:HIR'
, '_DF:PER:PER_APPLICATIONS:APP'
, LAT.APPLICATION_ID APPLICATION_ID
, LAT.ASSIGNMENT_ID ASSIGNMENT_ID
, LAT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRD.GRADE_ID GRADE_ID
, JOB.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, HIR.ORGANIZATION_ID ORGANIZATION_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, LAT.PERSON_ID PERSON_ID
, POS.POSITION_ID POSITION_ID
, REC.PERSON_ID RECRUITER_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, REF.PERSON_ID REFERRING_EMPLOYEE_ID
, SUP.PERSON_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
FROM PER_RECRUITMENT_ACTIVITIES RAC
, PER_ASS_STATUS_TYPE_AMENDS_TL YT
, (SELECT *
FROM PER_ASS_STATUS_TYPE_AMENDS
WHERE BUSINESS_GROUP_ID = HR_BIS.GET_SEC_PROFILE_BG_ID) Y
, PER_ASS_STATUS_TYPE_AMENDS_TL Y1T
, (SELECT *
FROM PER_ASS_STATUS_TYPE_AMENDS
WHERE BUSINESS_GROUP_ID = HR_BIS.GET_SEC_PROFILE_BG_ID) Y1
, PER_ASS_STATUS_TYPE_AMENDS_TL Y2T
, (SELECT *
FROM PER_ASS_STATUS_TYPE_AMENDS
WHERE BUSINESS_GROUP_ID = HR_BIS.GET_SEC_PROFILE_BG_ID) Y2
, PER_ASSIGNMENT_STATUS_TYPES_TL XT
, PER_ASSIGNMENT_STATUS_TYPES X
, PER_ASSIGNMENT_STATUS_TYPES_TL X1T
, PER_ASSIGNMENT_STATUS_TYPES X1
, PER_ASSIGNMENT_STATUS_TYPES_TL X2T
, PER_ASSIGNMENT_STATUS_TYPES X2
, PER_PERIODS_OF_SERVICE PES
, HR_ALL_ORGANIZATION_UNITS_TL SOUT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ALL_POSITIONS POS
, PER_JOBS JOB
, HR_LOCATIONS_ALL_TL LOCT
, PER_GRADES GRD
, PER_ALL_VACANCIES VAC
, PAY_PEOPLE_GROUPS PGR
, PER_APPLICATIONS APP
, PER_PEOPLE_X REC
, PER_PEOPLE_X SUP
, PER_PEOPLE_X REF
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F LAT
, PER_ASSIGNMENTS_F HIR
WHERE HIR.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND HIR.POSITION_ID = POS.POSITION_ID(+)
AND HIR.JOB_ID = JOB.JOB_ID(+)
AND HIR.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND HIR.GRADE_ID = GRD.GRADE_ID(+)
AND HIR.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND HIR.VACANCY_ID = VAC.VACANCY_ID(+)
AND HIR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HIR.APPLICATION_ID = APP.APPLICATION_ID
AND HIR.PERSON_ID = PEO.PERSON_ID
AND PEO.APPLICANT_NUMBER IS NOT NULL
AND HIR.SOURCE_ORGANIZATION_ID = SOUT.ORGANIZATION_ID (+)
AND SOUT.LANGUAGE (+) = USERENV('LANG')
AND HIR.ASSIGNMENT_ID = LAT.ASSIGNMENT_ID
AND LAT.PERIOD_OF_SERVICE_ID = PES.PERIOD_OF_SERVICE_ID(+)
AND LAT.ASSIGNMENT_STATUS_TYPE_ID = X.ASSIGNMENT_STATUS_TYPE_ID
AND X.ASSIGNMENT_STATUS_TYPE_ID = XT.ASSIGNMENT_STATUS_TYPE_ID
AND XT.LANGUAGE = USERENV('LANG')
AND LAT.ASSIGNMENT_STATUS_TYPE_ID = Y.ASSIGNMENT_STATUS_TYPE_ID(+)
AND Y.ASS_STATUS_TYPE_AMEND_ID = YT.ASS_STATUS_TYPE_AMEND_ID(+)
AND YT.LANGUAGE (+) = USERENV('LANG')
AND X1.PER_SYSTEM_STATUS = 'TERM_APL'
AND X1.BUSINESS_GROUP_ID IS NULL
AND X1.ASSIGNMENT_STATUS_TYPE_ID = X1T.ASSIGNMENT_STATUS_TYPE_ID
AND X1T.LANGUAGE = USERENV('LANG')
AND X2.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
AND X2.BUSINESS_GROUP_ID IS NULL
AND X2.DEFAULT_FLAG = 'Y'
AND X2.ASSIGNMENT_STATUS_TYPE_ID = X2T.ASSIGNMENT_STATUS_TYPE_ID
AND X2T.LANGUAGE = USERENV('LANG')
AND X1.ASSIGNMENT_STATUS_TYPE_ID = Y1.ASSIGNMENT_STATUS_TYPE_ID(+)
AND Y1.ASS_STATUS_TYPE_AMEND_ID = Y1T.ASS_STATUS_TYPE_AMEND_ID(+)
AND Y1T.LANGUAGE (+) = USERENV('LANG')
AND X2.ASSIGNMENT_STATUS_TYPE_ID = Y2.ASSIGNMENT_STATUS_TYPE_ID(+)
AND Y2.ASS_STATUS_TYPE_AMEND_ID = Y2T.ASS_STATUS_TYPE_AMEND_ID(+)
AND Y2T.LANGUAGE (+) = USERENV('LANG')
AND HIR.PERSON_REFERRED_BY_ID = REF.PERSON_ID (+)
AND HIR.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND HIR.RECRUITER_ID = REC.PERSON_ID (+)
AND HIR.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID(+)
AND HIR.EFFECTIVE_START_DATE = ( SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_ID = HIR.ASSIGNMENT_ID
AND A2.ASSIGNMENT_TYPE = 'A') )
AND LAT.EFFECTIVE_START_DATE = ( SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_ID = LAT.ASSIGNMENT_ID) )
AND APP.DATE_RECEIVED <= TRUNC(SYSDATE)
AND NVL(X.BUSINESS_GROUP_ID
, PEO.BUSINESS_GROUP_ID) = PEO.BUSINESS_GROUP_ID
AND HIR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, HIR.BUSINESS_GROUP_ID) WITH READ ONLY

Columns