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 ))