VIEW: APPS.POR_RCV_REQ_LOV_V

Source

View Text - Preformatted

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 b.assignment_type = 'E') AND asgn.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID AND past.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'TERM_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 d.assignment_type = 'E') AND asgn.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID AND past.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'TERM_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

View Text - HTML Formatted

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 B.ASSIGNMENT_TYPE = 'E')
AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'
, 'TERM_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 D.ASSIGNMENT_TYPE = 'E')
AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'
, 'TERM_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