View: ICX_PO_REQS_BY_USERS_V

Product:	ICX - Oracle iProcurement
Description:	Requisitions by Users View
Implementation/DBA Data:	APPS.ICX_PO_REQS_BY_USERS_V

View Text

SELECT PPF.PERSON_ID
, PPF.FULL_NAME
, HR_GENERAL.GET_WORK_PHONE(PPF.PERSON_ID) "WORK_TELEPHONE"
, POU.NAME "ORGANIZATION_NAME"
, FNU.USER_NAME
, PPF.MAILSTOP
, PPF.ATTRIBUTE1
, PPF.ATTRIBUTE2
, PPF.ATTRIBUTE3
, PPF.ATTRIBUTE4
, PPF.ATTRIBUTE5
, PPF.ATTRIBUTE6
, PPF.ATTRIBUTE7
, PPF.ATTRIBUTE8
, PPF.ATTRIBUTE9
, PPF.ATTRIBUTE10
, PPF.ATTRIBUTE11
, PPF.ATTRIBUTE12
, PPF.ATTRIBUTE13
, PPF.ATTRIBUTE14
, PPF.ATTRIBUTE15
, PPF.ATTRIBUTE16
, PPF.ATTRIBUTE17
, PPF.ATTRIBUTE18
, PPF.ATTRIBUTE19
, PPF.ATTRIBUTE20
, COUNT( PRH.PREPARER_ID ) "REQ_COUNT"
, MIN( PRH.CREATION_DATE ) "FIRST_REQ_DATE"
, MAX( PRH.CREATION_DATE ) "LAST_REQ_DATE"
, TO_CHAR( SUM( PRL.UNIT_PRICE*PRL.QUANTITY )
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE
, 30)) "TOT_REQ_AMOUNT"
, PAF.DEFAULT_CODE_COMB_ID
, ICX_GET.GET_GL_VALUE(101
, 'GL#'
, 101
, PAF.DEFAULT_CODE_COMB_ID
, 'COST CENTER') COST_CENTER
FROM PO_REQUISITION_HEADERS PRH
, PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, PER_ORGANIZATION_UNITS POU
, PO_REQUISITION_LINES PRL
, FND_USER FNU
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PRH.PREPARER_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND POU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND FNU.USER_ID > 0
AND PPF.PERSON_ID (+) = FNU.EMPLOYEE_ID
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID GROUP BY PPF.PERSON_ID
, PPF.FULL_NAME
, POU.NAME
, PPF.WORK_TELEPHONE
, FNU.USER_NAME
, PPF.MAILSTOP
, PPF.ATTRIBUTE1
, PPF.ATTRIBUTE2
, PPF.ATTRIBUTE3
, PPF.ATTRIBUTE4
, PPF.ATTRIBUTE5
, PPF.ATTRIBUTE6
, PPF.ATTRIBUTE7
, PPF.ATTRIBUTE8
, PPF.ATTRIBUTE9
, PPF.ATTRIBUTE10
, PPF.ATTRIBUTE11
, PPF.ATTRIBUTE12
, PPF.ATTRIBUTE13
, PPF.ATTRIBUTE14
, PPF.ATTRIBUTE15
, PPF.ATTRIBUTE16
, PPF.ATTRIBUTE17
, PPF.ATTRIBUTE18
, PPF.ATTRIBUTE19
, PPF.ATTRIBUTE20
, PAF.DEFAULT_CODE_COMB_ID
, SOB.CURRENCY_CODE

Columns