Product: | ICX - Oracle iProcurement |
---|---|
Description: | Open Requisitions by Users view |
Implementation/DBA Data: | APPS.ICX_PO_OPEN_REQS_BY_USERS_V |
SELECT DISTINCT PPF.PERSON_ID
, PPF.FULL_NAME
, FNU.USER_NAME
, PPF.WORK_TELEPHONE
, POU.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 )
, MIN( PRH.CREATION_DATE )
, MAX( PRH.CREATION_DATE )
, TO_CHAR( SUM( PRL.UNIT_PRICE*PRL.QUANTITY )
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE
, 30))
, 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
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, FND_USER FNU
WHERE PRH.PREPARER_ID > 0
AND PRH.PREPARER_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND FNU.EMPLOYEE_ID (+) = PPF.PERSON_ID
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 NVL( PRH.CLOSED_CODE
, 'OPEN' ) = 'OPEN'
AND NVL( PRH.CANCEL_FLAG
, 'N' ) = 'N'
AND NVL( PRL.CLOSED_CODE
, 'OPEN' ) = 'OPEN'
AND NVL( PRL.CANCEL_FLAG
, 'N' ) = 'N'
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID GROUP BY PPF.PERSON_ID
, PPF.FULL_NAME
, POU.NAME
, PPF.WORK_TELEPHONE
, 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
, FNU.USER_NAME