View: ICX_PO_REQUISITION_TEMPLATES_V

Product:	ICX - Oracle iProcurement
Description:	Obsolete
Implementation/DBA Data:	APPS.ICX_PO_REQUISITION_TEMPLATES_V

View Text

SELECT /*+ ORDERED INDEX(PRH PO_REQEXPRESS_HEADERS_U1) INDEX(HRE PER_PEOPLE_F_PK) */ PRH.EXPRESS_NAME
, PRH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, PRH.DESCRIPTION
, PRL.SEQUENCE_NUM
, PRL.PO_HEADER_ID
, PH.SEGMENT1
, PH.TYPE_LOOKUP_CODE
, PDT1.TYPE_NAME
, PH.CURRENCY_CODE
, PH.RATE
, PH.RATE_TYPE
, DCT.USER_CONVERSION_TYPE
, PH.RATE_DATE
, GSB.CURRENCY_CODE
, PRL.PO_LINE_ID
, PL.LINE_NUM
, PL.UNIT_PRICE
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)
, PRL.ITEM_ID
, PRL.ITEM_DESCRIPTION
, PRL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.UNIT_MEAS_LOOKUP_CODE
, NVL(PRL.UNIT_PRICE
, 0)
, PRL.SUGGESTED_VENDOR_ID
, PV.VENDOR_NAME
, PRL.SUGGESTED_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT_ID
, DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME|| '
, '||PVC.FIRST_NAME)
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_BUYER_ID
, HRE.FULL_NAME
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_TYPE_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, MSI.CONCATENATED_SEGMENTS
, MSI.ORGANIZATION_ID
, PV.ATTRIBUTE14
, PL.ATTRIBUTE14
, MSI.ATTRIBUTE14
FROM PO_REQEXPRESS_HEADERS PRH
, PO_REQEXPRESS_LINES PRL
, PER_PEOPLE_F HRE
, PO_DOCUMENT_TYPES PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PO_LOOKUP_CODES PLC1
, PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT1
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(HRE.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND SYSDATE < NVL(PRH.INACTIVE_DATE
, SYSDATE + 1)
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND NVL(PH.PO_HEADER_ID
, -1) = NVL(PL.PO_HEADER_ID
, -1)
AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+)
AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA'
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND PLC1.LOOKUP_TYPE = 'ORDER TYPE'
AND PRL.ITEM_ID IS NOT NULL
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID UNION SELECT /*+ ORDERED INDEX(PRH PO_REQEXPRESS_HEADERS_U1) INDEX(HRE PER_PEOPLE_F_PK) */ PRH.EXPRESS_NAME
, PRH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, PRH.DESCRIPTION
, PRL.SEQUENCE_NUM
, PRL.PO_HEADER_ID
, PH.SEGMENT1
, PH.TYPE_LOOKUP_CODE
, PDT1.TYPE_NAME
, PH.CURRENCY_CODE
, PH.RATE
, PH.RATE_TYPE
, DCT.USER_CONVERSION_TYPE
, PH.RATE_DATE
, GSB.CURRENCY_CODE
, PRL.PO_LINE_ID
, PL.LINE_NUM
, PL.UNIT_PRICE
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)
, PRL.ITEM_ID
, PRL.ITEM_DESCRIPTION
, PRL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.UNIT_MEAS_LOOKUP_CODE
, NVL(PRL.UNIT_PRICE
, 0)
, PRL.SUGGESTED_VENDOR_ID
, PV.VENDOR_NAME
, PRL.SUGGESTED_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT_ID
, DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME)
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_BUYER_ID
, HRE.FULL_NAME
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_TYPE_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, NULL
, TO_NUMBER(NULL)
, PV.ATTRIBUTE14
, PL.ATTRIBUTE14
, NULL
FROM PO_REQEXPRESS_HEADERS PRH
, PO_REQEXPRESS_LINES PRL
, PER_PEOPLE_F HRE
, PO_DOCUMENT_TYPES PDT
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PO_LOOKUP_CODES PLC1
, PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT1
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(HRE.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND SYSDATE < NVL(PRH.INACTIVE_DATE
, SYSDATE + 1)
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND NVL(PH.PO_HEADER_ID
, -1) = NVL(PL.PO_HEADER_ID
, -1)
AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+)
AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA'
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND PLC1.LOOKUP_TYPE = 'ORDER TYPE'
AND PRL.ITEM_ID IS NULL

Columns