View: ICX_PO_REQUISITION_OPEN_V

Product:	ICX - Oracle iProcurement
Description:	Open Requisition Lines View
Implementation/DBA Data:	APPS.ICX_PO_REQUISITION_OPEN_V

View Text

SELECT PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PLT.LINE_TYPE_ID
, PRL.ITEM_ID
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.ITEM_DESCRIPTION
, PRL.QUANTITY
, NVL(PRL.QUANTITY_RECEIVED
, 0)
, NVL(PRL.QUANTITY_DELIVERED
, 0)
, NVL(PRL.QUANTITY_CANCELLED
, 0)
, PRL.UNIT_MEAS_LOOKUP_CODE
, TO_CHAR ( PRL.UNIT_PRICE
, FND_CURRENCY.GET_FORMAT_MASK( GSOB.CURRENCY_CODE
, 30 ) ) DISPLAY_UNIT_PRICE
, PRL.UNIT_PRICE
, PRL.NEED_BY_DATE
, PRL.NOTE_TO_AGENT
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.CANCEL_FLAG
, PRL.MODIFIED_BY_AGENT_FLAG
, PRL.REFERENCE_NUM
, PRL.JUSTIFICATION
, PRL.NOTE_TO_RECEIVER
, OOD1.ORGANIZATION_NAME
, HRL1.LOCATION_CODE
, PRL.DESTINATION_SUBINVENTORY
, PRL.SOURCE_SUBINVENTORY
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
, PRL.ATTRIBUTE_CATEGORY
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')
, PRL.DELIVER_TO_LOCATION_ID
, PRL.TO_PERSON_ID
, PO_INQ_SV.GET_PERSON_NAME(TO_PERSON_ID)
, PRL.LAST_UPDATE_DATE
, PRL.LAST_UPDATED_BY
, PRL.LAST_UPDATE_LOGIN
, PRL.CREATION_DATE
, PRL.CREATED_BY
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.CURRENCY_CODE
, PRL.RATE
, PRL.RATE_DATE
, PRL.CURRENCY_UNIT_PRICE
, PRL.SUGGESTED_BUYER_ID
, PO_INQ_SV.GET_PERSON_NAME(PRL.SUGGESTED_BUYER_ID)
, PRL.CLOSED_CODE
, PRL.LINE_LOCATION_ID
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID)
, MCA.CONCATENATED_SEGMENTS
, PRL.CANCEL_REASON
, PRL.CANCEL_DATE
, PRL.VENDOR_ID
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID)
, GDT.USER_CONVERSION_TYPE
, PLC_DEST.DISPLAYED_FIELD
, PLC_STYPE.DISPLAYED_FIELD
, PRH.SEGMENT1
, PRH.PREPARER_ID
, PO_INQ_SV.GET_PERSON_NAME(PRH.PREPARER_ID)
, PRH.AUTHORIZATION_STATUS
, PLC_STTS.DISPLAYED_FIELD
, PRH.TYPE_LOOKUP_CODE
, PODT.TYPE_NAME
, PRH.INTERFACE_SOURCE_CODE
, DECODE(PRL.SOURCE_TYPE_CODE
, 'VENDOR'
, PO_INQ_SV.GET_PO_NUMBER(PRL.LINE_LOCATION_ID)
, PO_INQ_SV.GET_SO_NUMBER(PRH.SEGMENT1
, PRL.LINE_NUM))
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, ICX_GET.GET_ACTION_HISTORY_DATE(PRL.REQUISITION_HEADER_ID
, 'REQUISITION'
, TYPE_LOOKUP_CODE
, 'APPROVE')
FROM GL_DAILY_CONVERSION_TYPES GDT
, PO_LINE_TYPES PLT
, PO_DOCUMENT_TYPES PODT
, PO_LOOKUP_CODES PLC_STTS
, PO_LOOKUP_CODES PLC_STYPE
, PO_LOOKUP_CODES PLC_DEST
, HR_LOCATIONS HRL1
, ORG_ORGANIZATION_DEFINITIONS OOD1
, MTL_SYSTEM_ITEMS MSI
, MTL_CATEGORIES_KFV MCA
, PO_VENDORS POV
, PO_LINES POL
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, GL_SETS_OF_BOOKS GSOB
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND NVL(PRH.CLOSED_CODE
, 'OPEN') = 'OPEN'
AND NVL(PRH.CANCEL_FLAG
, 'N') = 'N'
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, PRL.DESTINATION_ORGANIZATION_ID)
AND MCA.CATEGORY_ID = PRL.CATEGORY_ID
AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE
AND PODT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PODT.DOCUMENT_TYPE_CODE = DECODE(PRH.TYPE_LOOKUP_CODE
, NULL
, 'REQUISITION'
, 'REQUISITION' )
AND PLC_STTS.LOOKUP_TYPE = DECODE(PRH.AUTHORIZATION_STATUS
, NULL
, 'AUTHORIZATION STATUS'
, 'AUTHORIZATION STATUS')
AND PLC_STTS.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC_STYPE.LOOKUP_TYPE = DECODE(PRL.SOURCE_TYPE_CODE
, NULL
, 'REQUISITION SOURCE TYPE'
, 'REQUISITION SOURCE TYPE')
AND PLC_STYPE.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE
AND PLC_DEST.LOOKUP_TYPE = DECODE(PRL.DESTINATION_TYPE_CODE
, NULL
, 'DESTINATION TYPE'
, 'DESTINATION TYPE')
AND PLC_DEST.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND NVL(PRL.CLOSED_CODE
, 'OPEN') = 'OPEN'
AND NVL(PRL.CANCEL_FLAG
, 'N') = 'N'
AND PRL.QUANTITY > PRL.QUANTITY_DELIVERED
AND POL.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID
AND POL.LINE_NUM (+) = PRL.BLANKET_PO_LINE_NUM
AND POV.VENDOR_ID (+) = PRL.VENDOR_ID
AND GSOB.SET_OF_BOOKS_ID = OOD1.SET_OF_BOOKS_ID

Columns