VIEW: APPS.PAY_JP_SOE_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT /* This view returns same records if session_date is in the same year. */ v.assignment_id, v.business_group_id, v.effective_date, pa3.assignment_type, pa3.assignment_number, pa3.payroll_id, pa3.person_id, ppt.system_person_type, pp.current_employee_flag, pp.current_applicant_flag, pp.current_emp_or_apl_flag, pp.employee_number, pp.applicant_number, pp.date_of_birth, pp.full_name, pp.global_name, pp.local_name, pa3.job_id, pjt.name from per_person_types ppt, per_all_people_f pp, per_jobs_tl pjt, per_all_assignments_f pa3, ( select pa.assignment_id, pa.business_group_id, /* When a assignment exists in the year but does not exist on session_date, nearest date ESD or EED is applied to effective_date. */ pay_jp_soe_pkg.get_effective_date(fnd.effective_date, pa.assignment_id) EFFECTIVE_DATE from per_assignments_f2 pa, fnd_sessions fnd where fnd.session_id = userenv('sessionid') /* Record must exist with payroll_id is not NULL in the same year. The following "exists" statement is called once for each assignment. */ and exists( select NULL from per_all_assignments_f pa2 where pa2.assignment_id = pa.assignment_id and to_number(to_char(fnd.effective_date,'YYYY')) between to_number(to_char(pa2.effective_start_date,'YYYY')) and to_number(to_char(pa2.effective_end_date,'YYYY')) and pa2.payroll_id is not NULL) group by pa.assignment_id, pa.business_group_id, fnd.effective_date ) v WHERE pa3.assignment_id = v.assignment_id and v.effective_date between pa3.effective_start_date and pa3.effective_end_date and pjt.job_id(+) = pa3.job_id and pjt.language (+) = userenv('LANG') and pp.person_id = pa3.person_id and v.effective_date between pp.effective_start_date and pp.effective_end_date and ppt.person_type_id = pp.person_type_id

View Text - HTML Formatted

SELECT /* THIS VIEW RETURNS SAME RECORDS IF SESSION_DATE IS IN THE SAME YEAR. */ V.ASSIGNMENT_ID
, V.BUSINESS_GROUP_ID
, V.EFFECTIVE_DATE
, PA3.ASSIGNMENT_TYPE
, PA3.ASSIGNMENT_NUMBER
, PA3.PAYROLL_ID
, PA3.PERSON_ID
, PPT.SYSTEM_PERSON_TYPE
, PP.CURRENT_EMPLOYEE_FLAG
, PP.CURRENT_APPLICANT_FLAG
, PP.CURRENT_EMP_OR_APL_FLAG
, PP.EMPLOYEE_NUMBER
, PP.APPLICANT_NUMBER
, PP.DATE_OF_BIRTH
, PP.FULL_NAME
, PP.GLOBAL_NAME
, PP.LOCAL_NAME
, PA3.JOB_ID
, PJT.NAME
FROM PER_PERSON_TYPES PPT
, PER_ALL_PEOPLE_F PP
, PER_JOBS_TL PJT
, PER_ALL_ASSIGNMENTS_F PA3
, ( SELECT PA.ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID
, /* WHEN A ASSIGNMENT EXISTS IN THE YEAR BUT DOES NOT EXIST ON SESSION_DATE
, NEAREST DATE ESD OR EED IS APPLIED TO EFFECTIVE_DATE. */ PAY_JP_SOE_PKG.GET_EFFECTIVE_DATE(FND.EFFECTIVE_DATE
, PA.ASSIGNMENT_ID) EFFECTIVE_DATE
FROM PER_ASSIGNMENTS_F2 PA
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV('SESSIONID') /* RECORD MUST EXIST WITH PAYROLL_ID IS NOT NULL IN THE SAME YEAR. THE FOLLOWING "EXISTS" STATEMENT IS CALLED ONCE FOR EACH ASSIGNMENT. */
AND EXISTS( SELECT NULL
FROM PER_ALL_ASSIGNMENTS_F PA2
WHERE PA2.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND TO_NUMBER(TO_CHAR(FND.EFFECTIVE_DATE
, 'YYYY')) BETWEEN TO_NUMBER(TO_CHAR(PA2.EFFECTIVE_START_DATE
, 'YYYY'))
AND TO_NUMBER(TO_CHAR(PA2.EFFECTIVE_END_DATE
, 'YYYY'))
AND PA2.PAYROLL_ID IS NOT NULL) GROUP BY PA.ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID
, FND.EFFECTIVE_DATE ) V
WHERE PA3.ASSIGNMENT_ID = V.ASSIGNMENT_ID
AND V.EFFECTIVE_DATE BETWEEN PA3.EFFECTIVE_START_DATE
AND PA3.EFFECTIVE_END_DATE
AND PJT.JOB_ID(+) = PA3.JOB_ID
AND PJT.LANGUAGE (+) = USERENV('LANG')
AND PP.PERSON_ID = PA3.PERSON_ID
AND V.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PPT.PERSON_TYPE_ID = PP.PERSON_TYPE_ID