View: ICX_EDM_PURCHASE_REQ_V

Product:	ICX - Oracle iProcurement
Description:	Project Related Purchase Requisitions View
Implementation/DBA Data:	APPS.ICX_EDM_PURCHASE_REQ_V

View Text

SELECT DISTINCT DIST.PROJECT_ID
, DIST.TASK_ID
, MRP_GET_PROJECT.PROJECT(DIST.PROJECT_ID)
, MRP_GET_PROJECT.TASK(DIST.TASK_ID)
, PRH.SEGMENT1 REQUISITION_NUM
, PRH.TYPE_LOOKUP_CODE
, PRH.DESCRIPTION DESCRIPTION
, PRH.CREATION_DATE
, HRV.FULL_NAME PREPARER_NAME
, TO_CHAR(PO_REQUISITION_HEADERS_PKG.GET_REQ_TOTAL( PRH.REQUISITION_HEADER_ID)
, FND_CURRENCY.GET_FORMAT_MASK( SOB.CURRENCY_CODE
, 30 )) REQ_HEADER_AMOUNT
, SOB.CURRENCY_CODE CURRENCY_CODE
, HR2.FULL_NAME APPROVER_NAME
, PLC_STA.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP
, FLK.MEANING CANCEL_DSP
, DECODE(PRH.CLOSED_CODE
, NULL
, 'NO'
, 'YES') CLOSED_CODE_DSP
, PRH.REQUISITION_HEADER_ID
, PRH.PREPARER_ID PREPARER_ID
, PAH.EMPLOYEE_ID APPROVER_ID
FROM GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LOOKUP_CODES PLC_STA
, PO_LOOKUP_CODES PLC2
, FND_LOOKUPS FLK
, PO_DOCUMENT_TYPES_ALL PODT
, PO_ACTION_HISTORY PAH
, HR_EMPLOYEES HR2
, HR_EMPLOYEES HRV
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQ_DISTRIBUTIONS_ALL DIST
, PO_REQUISITION_LINES_ALL LINE
WHERE PLC_STA.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC_STA.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PODT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PODT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PRH.PREPARER_ID = HRV.EMPLOYEE_ID
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND PAH.OBJECT_TYPE_CODE = 'REQUISITION'
AND PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID
AND LINE.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND DIST.REQUISITION_LINE_ID = LINE.REQUISITION_LINE_ID
AND PAH.SEQUENCE_NUM = (SELECT MAX(PAH2.SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH2
WHERE PAH2.OBJECT_TYPE_CODE = 'REQUISITION'
AND PAH2.OBJECT_ID = PRH.REQUISITION_HEADER_ID)
AND PAH.EMPLOYEE_ID = HR2.EMPLOYEE_ID
AND FLK.LOOKUP_TYPE = 'YES_NO'
AND FLK.LOOKUP_CODE = NVL(PRH.CANCEL_FLAG
, 'N')
AND PLC2.LOOKUP_TYPE = 'DOCUMENT STATE'
AND PLC2.LOOKUP_CODE = NVL(PRH.CLOSED_CODE
, 'OPEN')
AND NVL(PODT.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(PRH.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(DIST.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(LINE.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)

Columns