235: PER_ALL_ASSIGNMENTS_F PAF,
236: HR_SOFT_CODING_KEYFLEX HSCF,
237: HR_LOCATIONS_ALL HL,
238: PER_JOBS JOB,
239: PER_PERIODS_OF_SERVICE PPS
240: WHERE PPS.PERSON_ID = PPF.PERSON_ID
241: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
242: AND NVL(PPS.ACTUAL_TERMINATION_DATE
243: ,C_END_OF_TIME)
273: PER_ALL_ASSIGNMENTS_F PAF,
274: HR_SOFT_CODING_KEYFLEX HSCF,
275: HR_LOCATIONS_ALL HL,
276: PER_JOBS JOB,
277: PER_PERIODS_OF_SERVICE PPS
278: WHERE PPS.PERSON_ID = PPF.PERSON_ID
279: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
280: AND PPF.EFFECTIVE_END_DATE
281: AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
283: AND not exists (
284: SELECT
285: 1
286: FROM
287: PER_PERIODS_OF_SERVICE PPS2
288: WHERE PPF.PERSON_ID = PPS2.PERSON_ID
289: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
290: AND NVL(PPS2.ACTUAL_TERMINATION_DATE
291: ,C_END_OF_TIME) )
440: PER_ALL_ASSIGNMENTS_F PAF,
441: HR_SOFT_CODING_KEYFLEX HSCF,
442: HR_LOCATIONS_ALL HL,
443: PER_JOBS JOB,
444: PER_PERIODS_OF_SERVICE PPS
445: WHERE PPS.PERSON_ID = PPF.PERSON_ID
446: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
447: AND NVL(PPS.ACTUAL_TERMINATION_DATE
448: ,C_END_OF_TIME)
483: PER_ALL_ASSIGNMENTS_F PAF,
484: HR_SOFT_CODING_KEYFLEX HSCF,
485: HR_LOCATIONS_ALL HL,
486: PER_JOBS JOB,
487: PER_PERIODS_OF_SERVICE PPS
488: WHERE PPS.PERSON_ID = PPF.PERSON_ID
489: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
490: AND PPF.EFFECTIVE_END_DATE
491: AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
493: AND not exists (
494: SELECT
495: 1
496: FROM
497: PER_PERIODS_OF_SERVICE PPS2
498: WHERE PPF.PERSON_ID = PPS2.PERSON_ID
499: AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
500: AND NVL(PPS2.ACTUAL_TERMINATION_DATE
501: ,C_END_OF_TIME) )
524: V_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%TYPE;
525: V_MIDDLE_NAME PER_ALL_PEOPLE_F.MIDDLE_NAMES%TYPE;
526: V_SSN PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%TYPE;
527: V_DOB PER_ALL_PEOPLE_F.DATE_OF_BIRTH%TYPE;
528: V_DATE_START PER_PERIODS_OF_SERVICE.DATE_START%TYPE;
529: V_HEADER NUMBER;
530: V_BUFFER VARCHAR2(120);
531: V_BOOLEAN BOOLEAN;
532: BEGIN