VIEW: APPS.POR_LINES_ALL_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)