VIEW: APPS.ICX_EDM_PURCHASE_REQ_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)