View: POR_LINES_ALL_V

Product:	ICX - Oracle iProcurement
Description:	Requisition Line View
Implementation/DBA Data:	APPS.POR_LINES_ALL_V

View Text

SELECT PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PRL.LINE_TYPE_ID
, POL.LINE_TYPE
, POL.ORDER_TYPE_LOOKUP_CODE
, PRL.ITEM_DESCRIPTION
, PRL.ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, MC.CONCATENATED_SEGMENTS CATEGORY
, PRL.CATALOG_TYPE
, PRL.CATALOG_SOURCE
, PRL.CURRENCY_CODE
, PRL.CURRENCY_UNIT_PRICE
, PRL.AUCTION_DISPLAY_NUMBER || DECODE(PRL.AUCTION_LINE_NUMBER
, NULL
, ''
, ' (' || TO_CHAR(PRL.AUCTION_LINE_NUMBER) || ')') NEGOTIATION_NUMBER
, OOH.ORDERED_DATE SO_CREATION_DATE
, POR_VIEW_REQS_PKG.GET_SHIPMENT_NUMBER(PRL.REQUISITION_LINE_ID) SHIPMENT_NUMBER
, PRL.MANUFACTURER_NAME
, PRL.MANUFACTURER_PART_NUMBER
, PRL.MUST_USE_SUGG_VENDOR_FLAG
, PRL.DELIVER_TO_LOCATION_ID
, HRL.LOCATION_CODE DELIVER_TO_LOCATION
, PRL.DESTINATION_ORGANIZATION_ID DELIVER_TO_ORG_ID
, OOD1.ORGANIZATION_CODE DELIVER_TO_ORG_CODE
, PRL.DESTINATION_SUBINVENTORY
, PRL.DESTINATION_TYPE_CODE
, PLC1.DISPLAYED_FIELD DESTINATION_TYPE
, PRL.REQUESTER_EMAIL
, PRL.REQUESTER_FAX
, PRL.TO_PERSON_ID REQUESTER_ID
, HRE1.FULL_NAME DELIVER_TO_REQUESTER
, PRL.REQUESTER_PHONE
, PRL.ENCUMBERED_FLAG
, PRL.HAZARD_CLASS_ID
, PHC.HAZARD_CLASS
, PRL.JUSTIFICATION
, PRL.MODIFIED_BY_AGENT_FLAG
, PRL.NEED_BY_DATE
, PRL.NOTE_TO_AGENT
, PRL.NOTE_TO_RECEIVER
, PRL.NOTE_TO_VENDOR
, PRL.ON_RFQ_FLAG
, PRL.ORG_ID
, OOD2.ORGANIZATION_CODE ORG_CODE
, PRL.PARENT_REQ_LINE_ID
, PRL.LINE_LOCATION_ID
, PRL.QUANTITY_CANCELLED
, PRL.QUANTITY_DELIVERED
, PRL.QUANTITY
, PRL.QUANTITY_RECEIVED
, PRL.RATE
, PRL.RATE_DATE
, PRL.RATE_TYPE
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_ORGANIZATION_ID
, OOD3.ORGANIZATION_CODE
, PRL.SOURCE_REQ_LINE_ID
, PRL.SOURCE_SUBINVENTORY
, PRL.SOURCE_TYPE_CODE
, PLC2.DISPLAYED_FIELD
, PRL.UNSPSC_CODE
, PRL.OTHER_CATEGORY_CODE
, PRL.SUGGESTED_BUYER_ID
, HRE2.FULL_NAME SUGGESTED_BUYER
, PRL.BLANKET_PO_HEADER_ID
, PH.SEGMENT1 DOCUMENT_NUM
, PRL.BLANKET_PO_LINE_NUM
, PRL.DOCUMENT_TYPE_CODE
, PLC3.LOOKUP_TYPE DOCUMENT_TYPE
, PRL.SUPPLIER_DUNS
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE SUPPLIER_ITEM_NUMBER
, PRL.TAX_STATUS_INDICATOR
, PRL.USSGL_TRANSACTION_CODE
, PRL.PCARD_FLAG
, PRL.TRANSACTION_REASON_CODE
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, PRL.URGENT_FLAG
, PRL.VENDOR_CONTACT_ID
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.VENDOR_ID
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.VENDOR_SITE_ID
, NVL( PVS.VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_LOCATION) VENDOR_SITE_CODE
, PRL.NEW_SUPPLIER_FLAG
, PRL.AUTO_RECEIVE_FLAG
, PRL.AGENT_RETURN_NOTE
, PRL.CANCEL_DATE
, PRL.CANCEL_FLAG
, FLP.MEANING CANCEL_FLAG_DISPLAY
, PRL.CANCEL_REASON
, PRL.CLOSED_CODE
, PLC4.DISPLAYED_FIELD CLOSED_CODE_DISPLAY
, PRL.CLOSED_DATE
, PRL.CLOSED_REASON
, PRL.DESTINATION_CONTEXT
, PRL.UN_NUMBER_ID
, 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.ITEM_SOURCE_ID
, PRL.SUPPLIER_REF_NUMBER
, PRL.WIP_ENTITY_ID
, PRL.WIP_OPERATION_SEQ_NUM
FROM PO_LINE_TYPES POL
, PO_REQUISITION_LINES_ALL PRL
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_CATEGORIES_KFV MC
, HR_LOCATIONS HRL
, ORG_ORGANIZATION_DEFINITIONS OOD1
, PO_LOOKUP_CODES PLC1
, PER_PEOPLE_F HRE1
, PO_HAZARD_CLASSES PHC
, ORG_ORGANIZATION_DEFINITIONS OOD2
, ORG_ORGANIZATION_DEFINITIONS OOD3
, PO_LOOKUP_CODES PLC2
, PER_PEOPLE_F HRE2
, PO_HEADERS_ALL PH
, PO_LOOKUP_CODES PLC3
, PO_VENDOR_SITES_ALL PVS
, FND_LOOKUPS FLP
, PO_LOOKUP_CODES PLC4
, PO_REQUISITION_HEADERS PRH
, OE_ORDER_HEADERS OOH
, PO_SYSTEM_PARAMETERS PSP
WHERE PRL.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND PRL.CATEGORY_ID = MC.CATEGORY_ID
AND PRL.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID
AND PRL.DESTINATION_ORGANIZATION_ID = OOD1.ORGANIZATION_ID (+)
AND PLC1.LOOKUP_TYPE (+) = 'DESTINATION TYPE'
AND PRL.DESTINATION_TYPE_CODE = PLC1.LOOKUP_CODE (+)
AND PRL.TO_PERSON_ID = HRE1.PERSON_ID
AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PRL.ORG_ID = OOD2.ORGANIZATION_ID (+)
AND PRL.SOURCE_ORGANIZATION_ID = OOD3.ORGANIZATION_ID (+)
AND PLC2.LOOKUP_TYPE(+) = 'REQUISITION TYPE'
AND PLC2.LOOKUP_CODE(+) = DECODE(PRL.SOURCE_TYPE_CODE
, 'VENDOR'
, 'PURCHASE'
, 'INTERNAL')
AND PRL.SUGGESTED_BUYER_ID = HRE2.PERSON_ID (+)
AND PRL.BLANKET_PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND PLC3.LOOKUP_TYPE(+) = 'SOURCE DOCUMENT TYPE'
AND PRL.DOCUMENT_TYPE_CODE = PLC3.LOOKUP_CODE (+)
AND PRL.VENDOR_ID = PVS.VENDOR_ID (+)
AND PRL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PRL.CANCEL_FLAG = FLP.LOOKUP_CODE(+)
AND FLP.LOOKUP_TYPE (+) = 'YES_NO'
AND PRL.CLOSED_CODE = PLC4.LOOKUP_CODE (+)
AND PLC4.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND HRE1.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE
AND HRE2.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND HRE2.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF(+)
AND NVL( PSP.ORDER_SOURCE_ID
, 1) = NVL(NVL(OOH.ORDER_SOURCE_ID
, PSP.ORDER_SOURCE_ID)
, 1)

Columns