SELECT FWU.USER_ID , PAPF.FULL_NAME , PAPF.EMAIL_ADDRESS , PAPF.EMPLOYEE_NUMBER , ASGN.ASSIGNMENT_ID , ASGN.DEFAULT_CODE_COMB_ID , ASGN.LOCATION_ID , NULL , ASGN.ORGANIZATION_ID , PAPF.BUSINESS_GROUP_ID , PAPF.EXPENSE_CHECK_SEND_TO_ADDRESS , FSP.INVENTORY_ORGANIZATION_ID "DELIVER_TO_ORG_ID" FROM PER_ALL_PEOPLE_F PAPF , FND_USER FWU , HR_EMPLOYEES_CURRENT_V HRE , FINANCIALS_SYSTEM_PARAMETERS FSP , PER_ASSIGNMENTS_F ASGN , PER_ASSIGNMENT_STATUS_TYPES PAST WHERE HRE.EMPLOYEE_ID = FWU.EMPLOYEE_ID AND FWU.USER_ID= FND_GLOBAL.USER_ID AND PAPF.BUSINESS_GROUP_ID + 0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID) , 0) FROM FINANCIALS_SYSTEM_PARAMETERS FSP) AND PAPF.EMPLOYEE_NUMBER IS NOT NULL AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(A.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F A WHERE PAPF.PERSON_ID=A.PERSON_ID) AND ASGN.PERSON_ID = PAPF.PERSON_ID AND ASGN.PRIMARY_FLAG = 'Y' AND ASGN.ASSIGNMENT_TYPE = 'E' AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(B.EFFECTIVE_END_DATE) FROM PER_ASSIGNMENTS_F B WHERE ASGN.PERSON_ID=B.PERSON_ID) AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID AND PAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN' , 'SUSP_ASSIGN') AND ASGN.LOCATION_ID IS NULL UNION ALL SELECT FWU.USER_ID , PAPF.FULL_NAME , PAPF.EMAIL_ADDRESS , PAPF.EMPLOYEE_NUMBER , ASGN.ASSIGNMENT_ID , ASGN.DEFAULT_CODE_COMB_ID , ASGN.LOCATION_ID , HRLT.LOCATION_CODE , ASGN.ORGANIZATION_ID , PAPF.BUSINESS_GROUP_ID , PAPF.EXPENSE_CHECK_SEND_TO_ADDRESS , NVL(HRL.INVENTORY_ORGANIZATION_ID , FSP.INVENTORY_ORGANIZATION_ID) "DELIVER_TO_ORG_ID" FROM PER_ALL_PEOPLE_F PAPF , HR_LOCATIONS_ALL HRL , HR_LOCATIONS_ALL_TL HRLT , FND_USER FWU , HR_EMPLOYEES_CURRENT_V HRE , FINANCIALS_SYSTEM_PARAMETERS FSP , PER_ASSIGNMENTS_F ASGN , PER_ASSIGNMENT_STATUS_TYPES PAST WHERE HRE.EMPLOYEE_ID = FWU.EMPLOYEE_ID AND FWU.USER_ID= FND_GLOBAL.USER_ID AND PAPF.BUSINESS_GROUP_ID + 0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID) , 0) FROM FINANCIALS_SYSTEM_PARAMETERS FSP) AND PAPF.EMPLOYEE_NUMBER IS NOT NULL AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(C.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F C WHERE PAPF.PERSON_ID=C.PERSON_ID) AND ASGN.PERSON_ID = PAPF.PERSON_ID AND ASGN.PRIMARY_FLAG = 'Y' AND ASGN.ASSIGNMENT_TYPE = 'E' AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(D.EFFECTIVE_END_DATE) FROM PER_ASSIGNMENTS_F D WHERE ASGN.PERSON_ID=D.PERSON_ID) AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID AND PAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN' , 'SUSP_ASSIGN') AND ASGN.LOCATION_ID IS NOT NULL AND ASGN.LOCATION_ID = HRL.LOCATION_ID AND HRL.LOCATION_ID = HRLT.LOCATION_ID AND HRLT.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE