eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ICX_PO_REQUISITION_HEADERS_V

Source

View Text - Preformatted


SELECT PRH.ROWID ROW_ID , PRH.AGENT_RETURN_NOTE AGENT_RETURN_NOTE , PRH.AUTHORIZATION_STATUS AUTHORIZATION_STATUS , PRH.DESCRIPTION DESCRIPTION , PRH.END_DATE_ACTIVE END_DATE_ACTIVE , PRH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT , PRH.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER , PRH.PREPARER_FINISHED_DATE PREPARER_FINISHED_DATE , PRH.START_DATE_ACTIVE START_DATE_ACTIVE , PRH.INTERFACE_SOURCE_LINE_ID INTERFACE_SOURCE_LINE_ID , PRH.PREPARER_ID PREPARER_ID , PRH.REQUISITION_HEADER_ID , NVL(PRH.CLOSED_CODE,'OPEN') CLOSED_CODE , PRH.INTERFACE_SOURCE_CODE , PRH.TYPE_LOOKUP_CODE , PRH.USSGL_TRANSACTION_CODE , PRH.AGENT_RETURN_FLAG , DECODE(PRH.AUTHORIZATION_STATUS,'CANCELLED','Y',NULL) CANCEL_FLAG , PRH.ENABLED_FLAG , PRH.ON_LINE_FLAG , PRH.PRELIMINARY_RESEARCH_FLAG , PRH.PREPARER_FINISHED_FLAG , PRH.RESEARCH_COMPLETE_FLAG , PRH.SUMMARY_FLAG , PRH.TRANSFERRED_TO_OE_FLAG , PRH.CREATED_BY , PRH.CREATION_DATE , PRH.LAST_UPDATED_BY , PRH.LAST_UPDATE_DATE , PRH.LAST_UPDATE_LOGIN , PRH.PROGRAM_APPLICATION_ID , PRH.PROGRAM_ID , PRH.PROGRAM_UPDATE_DATE , PRH.REQUEST_ID , PRH.SEGMENT1 REQUISITION_NUM , PRH.SEGMENT2 , PRH.SEGMENT3 , PRH.SEGMENT4 , PRH.SEGMENT5 , PRH.ATTRIBUTE1 , PRH.ATTRIBUTE2 , PRH.ATTRIBUTE3 , PRH.ATTRIBUTE4 , PRH.ATTRIBUTE5 , PRH.ATTRIBUTE6 , PRH.ATTRIBUTE7 , PRH.ATTRIBUTE8 , PRH.ATTRIBUTE9 , PRH.ATTRIBUTE10 , PRH.ATTRIBUTE11 , PRH.ATTRIBUTE12 , PRH.ATTRIBUTE13 , PRH.ATTRIBUTE14 , PRH.ATTRIBUTE15 , PRH.ATTRIBUTE_CATEGORY , HRV.FULL_NAME PREPARER_NAME , PLC_STA.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP , PO_REQS_INQ_SV.GET_RESERVED_FLAG (REQUISITION_HEADER_ID) RESERVED_FLAG , PODT.TYPE_NAME DOCUMENT_TYPE_DSP , to_char( PO_REQUISITION_HEADERS_PKG.GET_REQ_TOTAL(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 , PAH.EMPLOYEE_ID APPROVER_ID , DECODE(PRH.CLOSED_CODE,NULL,'No','Yes') CLOSED_CODE_DSP , PLC3.DISPLAYED_FIELD ACTION_CODE_DISPLAY , FLK.MEANING CANCEL_DSP FROM GL_SETS_OF_BOOKS SOB , FINANCIALS_SYSTEM_PARAMETERS FSP , PO_LOOKUP_CODES PLC_STA , PO_LOOKUP_CODES PLC2 , PO_LOOKUP_CODES PLC3 , FND_LOOKUPS FLK , PO_DOCUMENT_TYPES PODT , PO_ACTION_HISTORY PAH , HR_EMPLOYEES HR2 , HR_EMPLOYEES HRV , PO_REQUISITION_HEADERS PRH WHERE PLC_STA.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS,'INCOMPLETE') AND PLC_STA.LOOKUP_TYPE = decode( PRH.AUTHORIZATION_STATUS, null, null, 'AUTHORIZATION STATUS' ) AND PODT.DOCUMENT_TYPE_CODE =decode( PRH.TYPE_LOOKUP_CODE, null, null, '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 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 NVL(PLC3.LOOKUP_TYPE, '1') IN ('APPROVER ACTIONS', 'CONTROL ACTIONS', '1') AND PLC3.LOOKUP_CODE(+) = PAH.ACTION_CODE AND FLK.LOOKUP_TYPE = decode( PRH.AUTHORIZATION_STATUS, null, null, 'YES_NO' ) AND FLK.LOOKUP_CODE = NVL(PRH.CANCEL_FLAG,'N') AND PLC2.LOOKUP_TYPE = decode( PRH.AUTHORIZATION_STATUS, null, null, 'DOCUMENT STATE' ) AND PLC2.LOOKUP_CODE = NVL(PRH.CLOSED_CODE, 'OPEN')

View Text - HTML Formatted

SELECT PRH.ROWID ROW_ID
, PRH.AGENT_RETURN_NOTE AGENT_RETURN_NOTE
, PRH.AUTHORIZATION_STATUS AUTHORIZATION_STATUS
, PRH.DESCRIPTION DESCRIPTION
, PRH.END_DATE_ACTIVE END_DATE_ACTIVE
, PRH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, PRH.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER
, PRH.PREPARER_FINISHED_DATE PREPARER_FINISHED_DATE
, PRH.START_DATE_ACTIVE START_DATE_ACTIVE
, PRH.INTERFACE_SOURCE_LINE_ID INTERFACE_SOURCE_LINE_ID
, PRH.PREPARER_ID PREPARER_ID
, PRH.REQUISITION_HEADER_ID
, NVL(PRH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, PRH.INTERFACE_SOURCE_CODE
, PRH.TYPE_LOOKUP_CODE
, PRH.USSGL_TRANSACTION_CODE
, PRH.AGENT_RETURN_FLAG
, DECODE(PRH.AUTHORIZATION_STATUS
, 'CANCELLED'
, 'Y'
, NULL) CANCEL_FLAG
, PRH.ENABLED_FLAG
, PRH.ON_LINE_FLAG
, PRH.PRELIMINARY_RESEARCH_FLAG
, PRH.PREPARER_FINISHED_FLAG
, PRH.RESEARCH_COMPLETE_FLAG
, PRH.SUMMARY_FLAG
, PRH.TRANSFERRED_TO_OE_FLAG
, PRH.CREATED_BY
, PRH.CREATION_DATE
, PRH.LAST_UPDATED_BY
, PRH.LAST_UPDATE_DATE
, PRH.LAST_UPDATE_LOGIN
, PRH.PROGRAM_APPLICATION_ID
, PRH.PROGRAM_ID
, PRH.PROGRAM_UPDATE_DATE
, PRH.REQUEST_ID
, PRH.SEGMENT1 REQUISITION_NUM
, PRH.SEGMENT2
, PRH.SEGMENT3
, PRH.SEGMENT4
, PRH.SEGMENT5
, PRH.ATTRIBUTE1
, PRH.ATTRIBUTE2
, PRH.ATTRIBUTE3
, PRH.ATTRIBUTE4
, PRH.ATTRIBUTE5
, PRH.ATTRIBUTE6
, PRH.ATTRIBUTE7
, PRH.ATTRIBUTE8
, PRH.ATTRIBUTE9
, PRH.ATTRIBUTE10
, PRH.ATTRIBUTE11
, PRH.ATTRIBUTE12
, PRH.ATTRIBUTE13
, PRH.ATTRIBUTE14
, PRH.ATTRIBUTE15
, PRH.ATTRIBUTE_CATEGORY
, HRV.FULL_NAME PREPARER_NAME
, PLC_STA.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP
, PO_REQS_INQ_SV.GET_RESERVED_FLAG (REQUISITION_HEADER_ID) RESERVED_FLAG
, PODT.TYPE_NAME DOCUMENT_TYPE_DSP
, TO_CHAR( PO_REQUISITION_HEADERS_PKG.GET_REQ_TOTAL(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
, PAH.EMPLOYEE_ID APPROVER_ID
, DECODE(PRH.CLOSED_CODE
, NULL
, 'NO'
, 'YES') CLOSED_CODE_DSP
, PLC3.DISPLAYED_FIELD ACTION_CODE_DISPLAY
, FLK.MEANING CANCEL_DSP
FROM GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LOOKUP_CODES PLC_STA
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, FND_LOOKUPS FLK
, PO_DOCUMENT_TYPES PODT
, PO_ACTION_HISTORY PAH
, HR_EMPLOYEES HR2
, HR_EMPLOYEES HRV
, PO_REQUISITION_HEADERS PRH
WHERE PLC_STA.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC_STA.LOOKUP_TYPE = DECODE( PRH.AUTHORIZATION_STATUS
, NULL
, NULL
, 'AUTHORIZATION STATUS' )
AND PODT.DOCUMENT_TYPE_CODE =DECODE( PRH.TYPE_LOOKUP_CODE
, NULL
, NULL
, '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 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 NVL(PLC3.LOOKUP_TYPE
, '1') IN ('APPROVER ACTIONS'
, 'CONTROL ACTIONS'
, '1')
AND PLC3.LOOKUP_CODE(+) = PAH.ACTION_CODE
AND FLK.LOOKUP_TYPE = DECODE( PRH.AUTHORIZATION_STATUS
, NULL
, NULL
, 'YES_NO' )
AND FLK.LOOKUP_CODE = NVL(PRH.CANCEL_FLAG
, 'N')
AND PLC2.LOOKUP_TYPE = DECODE( PRH.AUTHORIZATION_STATUS
, NULL
, NULL
, 'DOCUMENT STATE' )
AND PLC2.LOOKUP_CODE = NVL(PRH.CLOSED_CODE
, 'OPEN')