VIEW: APPS.PSP_ENC_PAYROLL_ASSIGNMENT_V

Source

View Text - Preformatted

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, d.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 D.PAYROLL_ID=E.PAYROLL_ID AND /*redundant Clause for preparing parallel execution path*/ D.PAYROLL_ID = C.PAYROLL_ID AND E.PAYROLL_ID=C.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 B.EFFECTIVE_END_DATE >= SYSDATE AND /* Duplicate Clause for preparing parallel execution path*/ B.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE) FROM PER_all_PEOPLE_F PPF2 WHERE PPF2.PERSON_ID=B.PERSON_ID and EFFECTIVE_END_DATE>=sysdate) AND C.EFFECTIVE_END_DATE >= SYSDATE AND /* Duplicate Clause for preparing parallel execution path*/ C.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE) FROM PER_all_ASSIGNMENTS_F PAF2 WHERE PAF2.ASSIGNMENT_ID =C.ASSIGNMENT_ID and EFFECTIVE_END_DATE>=sysdate) AND T.ASSIGNMENT_STATUS_TYPE_ID = C.ASSIGNMENT_STATUS_TYPE_ID AND T.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN' AND C.PERIOD_OF_SERVICE_ID IS NOT NULL AND C.EFFECTIVE_START_DATE <=D.EFFECTIVE_END_DATE AND C.EFFECTIVE_END_DATE >= D.EFFECTIVE_START_DATE

View Text - HTML Formatted

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
, D.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 D.PAYROLL_ID=E.PAYROLL_ID
AND /*REDUNDANT CLAUSE FOR PREPARING PARALLEL EXECUTION PATH*/ D.PAYROLL_ID = C.PAYROLL_ID
AND E.PAYROLL_ID=C.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 B.EFFECTIVE_END_DATE >= SYSDATE
AND /* DUPLICATE CLAUSE FOR PREPARING PARALLEL EXECUTION PATH*/ B.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PPF2
WHERE PPF2.PERSON_ID=B.PERSON_ID
AND EFFECTIVE_END_DATE>=SYSDATE)
AND C.EFFECTIVE_END_DATE >= SYSDATE
AND /* DUPLICATE CLAUSE FOR PREPARING PARALLEL EXECUTION PATH*/ C.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PAF2
WHERE PAF2.ASSIGNMENT_ID =C.ASSIGNMENT_ID
AND EFFECTIVE_END_DATE>=SYSDATE)
AND T.ASSIGNMENT_STATUS_TYPE_ID = C.ASSIGNMENT_STATUS_TYPE_ID
AND T.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND C.PERIOD_OF_SERVICE_ID IS NOT NULL
AND C.EFFECTIVE_START_DATE <=D.EFFECTIVE_END_DATE
AND C.EFFECTIVE_END_DATE >= D.EFFECTIVE_START_DATE