View: PJM_PROJECT_REQ_V

Product:	PJM - Project Manufacturing
Description:	Project related purchase requisition information for the Web Inquiry.
Implementation/DBA Data:	APPS.PJM_PROJECT_REQ_V

View Text

SELECT DISTINCT DIST.PROJECT_ID
, DIST.TASK_ID
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.CREATION_DATE
, PRH.DESCRIPTION
, PRH.TYPE_LOOKUP_CODE
, PLC_STA.DISPLAYED_FIELD
, PAH.EMPLOYEE_ID
, HR2.FULL_NAME
, PJM_INQUIRY.YES_NO( NVL( PRH.CLOSED_CODE
, 'N' ) )
, PJM_INQUIRY.YES_NO( NVL(PRH.CANCEL_FLAG
, 'N') )
, PRH.PREPARER_ID
, HRV.FULL_NAME
, SOB.CURRENCY_CODE
, TO_CHAR( PO_REQUISITION_HEADERS_PKG.GET_REQ_TOTAL ( PRH.REQUISITION_HEADER_ID )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( SOB.CURRENCY_CODE
, 40 ) )
, MSI.CONCATENATED_SEGMENTS
, LINE.ITEM_DESCRIPTION
, DIST.END_ITEM_UNIT_NUMBER
FROM GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LOOKUP_CODES PLC_STA
, PO_LOOKUP_CODES PLC2
, PO_DOCUMENT_TYPES PODT
, PO_ACTION_HISTORY PAH
, PER_ALL_PEOPLE_F HR2
, PER_ALL_PEOPLE_F HRV
, PO_REQUISITION_HEADERS PRH
, PO_REQ_DISTRIBUTIONS DIST
, PO_REQUISITION_LINES LINE
, MTL_SYSTEM_ITEMS_KFV MSI
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.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN NVL( HRV.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL( HRV.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND FSP.SET_OF_BOOKS_ID = DIST.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.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN NVL( HR2.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL( HR2.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PLC2.LOOKUP_TYPE = 'DOCUMENT STATE'
AND PLC2.LOOKUP_CODE (+) = NVL(PRH.CLOSED_CODE
, 'OPEN')
AND MSI.ORGANIZATION_ID (+) = LINE.DESTINATION_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = LINE.ITEM_ID

Columns