VIEW: APPS.PSP_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT paf.person_id, paf.assignment_id, paf.business_group_id, paf.gl_set_of_books_id, paf.begin_date, paf.end_date, paf.effective_date, DECODE(SIGN(TRUNC(SYSDATE) - paf.effective_date), -1, past.user_status, (SELECT user_status FROM per_assignment_status_types WHERE assignment_status_type_id = 8)) ASSIGNMENT_STATUS FROM per_assignment_status_types past, (SELECT A.PERSON_ID, A.ASSIGNMENT_ID, A.BUSINESS_GROUP_ID, B.GL_SET_OF_BOOKS_ID , MIN(A.EFFECTIVE_START_DATE) BEGIN_DATE, DECODE(MAX(A.EFFECTIVE_END_DATE ),TO_DATE('31/12/4712','DD/MM/RRRR'),TO_DATE(NULL), MAX(A.EFFECTIVE_END_DATE )) END_DATE, GREATEST(MIN(A.effective_start_date), GREATEST(trunc(SYSDATE), MAX(a.effective_end_date))) effective_date FROM PER_ASSIGNMENTS_F2 A, PER_PEOPLE_F PER, PAY_PAYROLLS_F B WHERE PER.PERSON_ID = A.PERSON_ID AND A.PAYROLL_ID=B.PAYROLL_ID AND A.ASSIGNMENT_TYPE ='E' AND B.EFFECTIVE_START_DATE <= A.EFFECTIVE_END_DATE AND B.EFFECTIVE_END_DATE >= A.EFFECTIVE_START_DATE GROUP BY A.BUSINESS_GROUP_ID,B.GL_SET_OF_BOOKS_ID, A.PERSON_ID, A.ASSIGNMENT_ID) paf, per_all_assignments_f paaf WHERE paaf.assignment_id = paf.assignment_id AND past.ASSIGNMENT_STATUS_TYPE_ID = paaf.ASSIGNMENT_STATUS_TYPE_ID AND nvl(paf.end_date,TO_DATE('31/12/4712','DD/MM/RRRR')) between paaf.effective_start_date and paaf.effective_end_date

View Text - HTML Formatted

SELECT PAF.PERSON_ID
, PAF.ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID
, PAF.GL_SET_OF_BOOKS_ID
, PAF.BEGIN_DATE
, PAF.END_DATE
, PAF.EFFECTIVE_DATE
, DECODE(SIGN(TRUNC(SYSDATE) - PAF.EFFECTIVE_DATE)
, -1
, PAST.USER_STATUS
, (SELECT USER_STATUS
FROM PER_ASSIGNMENT_STATUS_TYPES
WHERE ASSIGNMENT_STATUS_TYPE_ID = 8)) ASSIGNMENT_STATUS
FROM PER_ASSIGNMENT_STATUS_TYPES PAST
, (SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.BUSINESS_GROUP_ID
, B.GL_SET_OF_BOOKS_ID
, MIN(A.EFFECTIVE_START_DATE) BEGIN_DATE
, DECODE(MAX(A.EFFECTIVE_END_DATE )
, TO_DATE('31/12/4712'
, 'DD/MM/RRRR')
, TO_DATE(NULL)
, MAX(A.EFFECTIVE_END_DATE )) END_DATE
, GREATEST(MIN(A.EFFECTIVE_START_DATE)
, GREATEST(TRUNC(SYSDATE)
, MAX(A.EFFECTIVE_END_DATE))) EFFECTIVE_DATE
FROM PER_ASSIGNMENTS_F2 A
, PER_PEOPLE_F PER
, PAY_PAYROLLS_F B
WHERE PER.PERSON_ID = A.PERSON_ID
AND A.PAYROLL_ID=B.PAYROLL_ID
AND A.ASSIGNMENT_TYPE ='E'
AND B.EFFECTIVE_START_DATE <= A.EFFECTIVE_END_DATE
AND B.EFFECTIVE_END_DATE >= A.EFFECTIVE_START_DATE GROUP BY A.BUSINESS_GROUP_ID
, B.GL_SET_OF_BOOKS_ID
, A.PERSON_ID
, A.ASSIGNMENT_ID) PAF
, PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAST.ASSIGNMENT_STATUS_TYPE_ID = PAAF.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(PAF.END_DATE
, TO_DATE('31/12/4712'
, 'DD/MM/RRRR')) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE