View: PSP_ENC_PAYROLL_ASSIGNMENT_V

Product:	PSP - Labor Distribution
Description:	View for setting up payroll assignments for labor encumbrances
Implementation/DBA Data:	APPS.PSP_ENC_PAYROLL_ASSIGNMENT_V

View Text

SELECT A.ASSIGNMENT_ID
, C.ASSIGNMENT_NUMBER
, A.ENC_PAYROLL_ID
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
, A.ENC_PAYROLL_ASSIGNMENT_ID
, DECODE (E.INC_EXC_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y') INCLUDE
, DECODE (E.INC_EXC_FLAG
, 'Y'
, 'Y'
, 'N'
, 'N') EXCLUDE
, B.FULL_NAME
, E.PAYROLL_ID
FROM PSP_ENC_PAYROLL_ASSIGNMENTS A
, PER_PEOPLE_F B
, PER_ASSIGNMENTS_F C
, PSP_ENC_PAYROLLS E
, PER_ASSIGNMENT_STATUS_TYPES T
WHERE A.ASSIGNMENT_ID = C.ASSIGNMENT_ID
AND E.ENC_PAYROLL_ID = A.ENC_PAYROLL_ID
AND C.PERSON_ID = B.PERSON_ID
AND (SYSDATE BETWEEN B.EFFECTIVE_START_DATE
AND B.EFFECTIVE_END_DATE OR (SYSDATE < (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_PEOPLE_F PPF2
WHERE PPF2.PERSON_ID=B.PERSON_ID)))
AND (SYSDATE BETWEEN C.EFFECTIVE_START_DATE
AND C.EFFECTIVE_END_DATE OR (SYSDATE < (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F PAF2
WHERE PAF2.ASSIGNMENT_ID =C.ASSIGNMENT_ID)))
AND C.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
AND C.PERIOD_OF_SERVICE_ID IS NOT NULL
AND T.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN' UNION SELECT C.ASSIGNMENT_ID
, C.ASSIGNMENT_NUMBER
, E.ENC_PAYROLL_ID
, E.BUSINESS_GROUP_ID
, E.SET_OF_BOOKS_ID
, 0
, DECODE (E.INC_EXC_FLAG
, 'Y'
, 'Y'
, 'N'
, 'N'
, NULL
, 'Y') INCLUDE
, DECODE (E.INC_EXC_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'
, NULL
, 'N') EXCLUDE
, B.FULL_NAME
, C.PAYROLL_ID
FROM PER_ASSIGNMENTS_F C
, PER_PEOPLE_F B
, PAY_PAYROLLS_F D
, PSP_ENC_PAYROLLS E
, PER_ASSIGNMENT_STATUS_TYPES T
WHERE C.PAYROLL_ID = E.PAYROLL_ID (+)
AND C.ASSIGNMENT_ID NOT IN (SELECT PEPA.ASSIGNMENT_ID
FROM PSP_ENC_PAYROLL_ASSIGNMENTS PEPA
WHERE PEPA.ENC_PAYROLL_ID = E.ENC_PAYROLL_ID)
AND C.PERSON_ID = B.PERSON_ID
AND (SYSDATE BETWEEN B.EFFECTIVE_START_DATE
AND B.EFFECTIVE_END_DATE OR (SYSDATE < (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_PEOPLE_F PPF2
WHERE PPF2.PERSON_ID=B.PERSON_ID)))
AND (SYSDATE BETWEEN C.EFFECTIVE_START_DATE
AND C.EFFECTIVE_END_DATE OR (SYSDATE < (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F PAF2
WHERE PAF2.ASSIGNMENT_ID =C.ASSIGNMENT_ID)))
AND C.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
AND T.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND C.PERIOD_OF_SERVICE_ID IS NOT NULL
AND C.PAYROLL_ID=D.PAYROLL_ID
AND (( C.EFFECTIVE_START_DATE BETWEEN D.EFFECTIVE_START_DATE
AND D.EFFECTIVE_END_DATE) OR (C.EFFECTIVE_END_DATE BETWEEN D.EFFECTIVE_START_DATE
AND D.EFFECTIVE_END_DATE) OR (D.EFFECTIVE_START_DATE <= C.EFFECTIVE_START_DATE
AND D.EFFECTIVE_END_DATE >=C.EFFECTIVE_END_DATE ))

Columns