VIEW: APPS.PA_AGR_ADM_DET_V

Source

View Text - Preformatted

SELECT distinct p.full_name full_name,p.person_id person_id,p.employee_number employee_number FROM per_people_f p, per_all_assignments_f a, per_periods_of_service b, pa_lookups pl WHERE P.CURRENT_EMPLOYEE_FLAG = 'Y' AND P.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) AND P.EMPLOYEE_NUMBER IS NOT NULL AND P.PERSON_ID = A.PERSON_ID AND A.PRIMARY_FLAG = 'Y' AND A.ASSIGNMENT_TYPE ='E' AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID AND A.PERSON_ID = B.PERSON_ID AND A.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) AND (B.ACTUAL_TERMINATION_DATE >= trunc(sysdate) OR B.ACTUAL_TERMINATION_DATE is null) AND pl.lookup_type='PA_PERSON_TYPE' AND pl.lookup_code='EMP' UNION ALL SELECT distinct p.full_name full_name,p.person_id person_id,p.employee_number employee_number FROM per_people_f p , per_all_assignments_f a, Per_Periods_Of_Placement b, pa_lookups pl WHERE P.CURRENT_NPW_FLAG='Y' AND P.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) AND P.NPW_NUMBER IS NOT NULL AND P.PERSON_ID = A.PERSON_ID AND A.PRIMARY_FLAG = 'Y' AND A.ASSIGNMENT_TYPE = 'C' AND b.date_start = a.period_of_placement_date_start AND a.person_id = b.person_id AND A.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) AND (B.ACTUAL_TERMINATION_DATE >= trunc(sysdate) or B.ACTUAL_TERMINATION_DATE is null) and pl.lookup_type='PA_PERSON_TYPE'

View Text - HTML Formatted

SELECT DISTINCT P.FULL_NAME FULL_NAME
, P.PERSON_ID PERSON_ID
, P.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
FROM PER_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE B
, PA_LOOKUPS PL
WHERE P.CURRENT_EMPLOYEE_FLAG = 'Y'
AND P.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND P.EMPLOYEE_NUMBER IS NOT NULL
AND P.PERSON_ID = A.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE ='E'
AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
AND A.PERSON_ID = B.PERSON_ID
AND A.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND (B.ACTUAL_TERMINATION_DATE >= TRUNC(SYSDATE) OR B.ACTUAL_TERMINATION_DATE IS NULL)
AND PL.LOOKUP_TYPE='PA_PERSON_TYPE'
AND PL.LOOKUP_CODE='EMP' UNION ALL SELECT DISTINCT P.FULL_NAME FULL_NAME
, P.PERSON_ID PERSON_ID
, P.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
FROM PER_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_PLACEMENT B
, PA_LOOKUPS PL
WHERE P.CURRENT_NPW_FLAG='Y'
AND P.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND P.NPW_NUMBER IS NOT NULL
AND P.PERSON_ID = A.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'C'
AND B.DATE_START = A.PERIOD_OF_PLACEMENT_DATE_START
AND A.PERSON_ID = B.PERSON_ID
AND A.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND (B.ACTUAL_TERMINATION_DATE >= TRUNC(SYSDATE) OR B.ACTUAL_TERMINATION_DATE IS NULL)
AND PL.LOOKUP_TYPE='PA_PERSON_TYPE'