VIEW: APPS.ICX_PO_OPEN_REQS_BY_USERS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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