VIEW: APPS.PJM_PROJECT_REQ_V

Source

View Text - Preformatted

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( PJM_INQUIRY.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 , PRH.ORG_ID FROM GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMS_ALL FSP , PO_LOOKUP_CODES PLC_STA , PO_LOOKUP_CODES PLC2 , PO_DOCUMENT_TYPES_ALL PODT , PO_ACTION_HISTORY PAH , PER_ALL_PEOPLE_F HR2 , PER_ALL_PEOPLE_F HRV , PO_REQUISITION_HEADERS_ALL PRH , PO_REQ_DISTRIBUTIONS_ALL DIST , PO_REQUISITION_LINES_ALL 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.ORG_ID = PRH.ORG_ID 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 FSP.ORG_ID = DIST.ORG_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 LINE.ORG_ID = PRH.ORG_ID AND DIST.REQUISITION_LINE_ID = LINE.REQUISITION_LINE_ID AND DIST.ORG_ID = LINE.ORG_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

View Text - HTML Formatted

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( PJM_INQUIRY.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
, PRH.ORG_ID
FROM GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_LOOKUP_CODES PLC_STA
, PO_LOOKUP_CODES PLC2
, PO_DOCUMENT_TYPES_ALL PODT
, PO_ACTION_HISTORY PAH
, PER_ALL_PEOPLE_F HR2
, PER_ALL_PEOPLE_F HRV
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQ_DISTRIBUTIONS_ALL DIST
, PO_REQUISITION_LINES_ALL 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.ORG_ID = PRH.ORG_ID
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 FSP.ORG_ID = DIST.ORG_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 LINE.ORG_ID = PRH.ORG_ID
AND DIST.REQUISITION_LINE_ID = LINE.REQUISITION_LINE_ID
AND DIST.ORG_ID = LINE.ORG_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