View: POR_RCV_REQ_LOV_V

Product:	ICX - Oracle iProcurement
Description:	The List of Value of Requester in Receive Expanded Search
Implementation/DBA Data:	APPS.POR_RCV_REQ_LOV_V

View Text

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

Columns