View: PSP_ASSIGNMENTS_V

Product:	PSP - Labor Distribution
Description:	Shows assignments for an employee
Implementation/DBA Data:	APPS.PSP_ASSIGNMENTS_V

View Text

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

Columns