View: ICX_PO_REQUISITION_HEADERS_V

Product:	ICX - Oracle iProcurement
Description:	Requisition Headers View
Implementation/DBA Data:	APPS.ICX_PO_REQUISITION_HEADERS_V

View Text

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')

Columns