VIEW: APPS.ICX_POR_REQ_TEMPLATES

Source

View Text - Preformatted

SELECT TO_CHAR(NULL) CONTRACT_NUM, TO_NUMBER(NULL) CONTRACT_LINE, PRL.EXPRESS_NAME TEMPLATE_ID, PRL.SEQUENCE_NUM TEMPLATE_LINE_ID, PRL.PO_HEADER_ID CONTRACT_ID, PRL.PO_LINE_ID CONTRACT_LINE_ID, PRL.ITEM_ID ITEM_ID, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION, PRL.LINE_TYPE_ID LINE_TYPE_ID, PRL.CATEGORY_ID CATEGORY_ID, UOMTL.UOM_CODE UNIT_OF_MEASURE, PRL.UNIT_PRICE FUNCTIONAL_PRICE, GSB.CURRENCY_CODE CURRENCY, PRL.UNIT_PRICE CONTRACT_PRICE, TO_CHAR(NULL) CONTRACT_CURRENCY, TO_CHAR(NULL) CONTRACT_RATE_TYPE, TO_DATE(NULL) CONTRACT_RATE_DATE, TO_NUMBER(NULL) CONTRACT_RATE, TO_CHAR(NULL) PICTURE_URL, PV.VENDOR_NAME SUPPLIER, PRL.SUGGESTED_VENDOR_ID SUPPLIER_ID, PRL.SUGGESTED_VENDOR_SITE_ID SUPPLIER_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE, PRL.SUGGESTED_VENDOR_PRODUCT_CODE SUPPLIER_PART_NUMBER, PRL.ORG_ID OPERATING_UNIT_ID, PRL.CREATION_DATE CREATION_DATE, GREATEST( PRL.LAST_UPDATE_DATE, PRH.LAST_UPDATE_DATE) LAST_UPDATE_DATE, PRH.INACTIVE_DATE INACTIVE_DATE, PRH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE, PRL.SOURCE_TYPE_CODE SOURCE_TYPE_CODE FROM PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, PO_REQEXPRESS_HEADERS_ALL PRH, PO_REQEXPRESS_LINES_ALL PRL, MTL_UNITS_OF_MEASURE_TL UOMTL, GL_SETS_OF_BOOKS GSB, FINANCIALS_SYSTEM_PARAMS_ALL FSPA WHERE PRL.EXPRESS_NAME = PRH.EXPRESS_NAME AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE AND UOMTL.LANGUAGE IN (SELECT LANGUAGE_CODE FROM FND_LANGUAGES WHERE INSTALLED_FLAG = 'B') AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE AND UOMTL.SOURCE_LANG = UOMTL.LANGUAGE AND (PRL.ORG_ID IS NULL AND PRH.ORG_ID IS NULL OR PRL.ORG_ID = PRH.ORG_ID) AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID (+) AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND (PRH.ORG_ID IS NULL AND FSPA.ORG_ID IS NULL OR PRH.ORG_ID = FSPA.ORG_ID) AND GSB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID AND PRL.PO_HEADER_ID IS NULL UNION SELECT PH.SEGMENT1 CONTRACT_NUM, PL.LINE_NUM CONTRACT_LINE, PRL.EXPRESS_NAME TEMPLATE_ID, PRL.SEQUENCE_NUM TEMPLATE_LINE_ID, PRL.PO_HEADER_ID CONTRACT_ID, PRL.PO_LINE_ID CONTRACT_LINE_ID, PRL.ITEM_ID ITEM_ID, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION, PRL.LINE_TYPE_ID LINE_TYPE_ID, PL.CATEGORY_ID CATEGORY_ID, UOMTL.UOM_CODE UNIT_OF_MEASURE, DECODE(GC.MINIMUM_ACCOUNTABLE_UNIT,NULL, ROUND(PL.UNIT_PRICE*NVL(PH.RATE,1),GC.EXTENDED_PRECISION), ROUND(PL.UNIT_PRICE*NVL(PH.RATE,1)/GC.MINIMUM_ACCOUNTABLE_UNIT)* GC.MINIMUM_ACCOUNTABLE_UNIT) FUNCTIONAL_PRICE, PH.CURRENCY_CODE CURRENCY_CODE, PL.UNIT_PRICE CONTRACT_PRICE, PH.CURRENCY_CODE CONTRACT_CURRENCY, PH.RATE_TYPE CONTRACT_RATE_TYPE, PH.RATE_DATE CONTRACT_RATE_DATE, PH.RATE CONTRACT_RATE, PL.ATTRIBUTE14 PICTURE_URL, PV.VENDOR_NAME SUPPLIER, PH.VENDOR_ID SUPPLIER_ID, PH.VENDOR_SITE_ID SUPPLIER_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE, PL.VENDOR_PRODUCT_NUM SUPPLIER_PART_NUMBER, PRL.ORG_ID OPERATING_UNIT_ID, PRL.CREATION_DATE CREATION_DATE, GREATEST( PRL.LAST_UPDATE_DATE, PRH.LAST_UPDATE_DATE) LAST_UPDATE_DATE, PRH.INACTIVE_DATE INACTIVE_DATE, PRH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE, PRL.SOURCE_TYPE_CODE SOURCE_TYPE_CODE FROM PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, PO_REQEXPRESS_HEADERS_ALL PRH, PO_REQEXPRESS_LINES_ALL PRL, MTL_UNITS_OF_MEASURE_TL UOMTL, FINANCIALS_SYSTEM_PARAMS_ALL FSPA, PO_HEADERS_ALL PH, PO_LINES_ALL PL, GL_CURRENCIES GC WHERE PRL.EXPRESS_NAME = PRH.EXPRESS_NAME AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE AND UOMTL.LANGUAGE IN (SELECT LANGUAGE_CODE FROM FND_LANGUAGES WHERE INSTALLED_FLAG = 'B') AND (PRL.ORG_ID IS NULL AND PRH.ORG_ID IS NULL OR PRL.ORG_ID = PRH.ORG_ID) AND PH.VENDOR_ID = PV.VENDOR_ID (+) AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND (PRH.ORG_ID IS NULL AND FSPA.ORG_ID IS NULL OR PRH.ORG_ID = FSPA.ORG_ID) AND PRL.PO_HEADER_ID IS NOT NULL AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID AND PRL.PO_LINE_ID = PL.PO_LINE_ID AND PH.CURRENCY_CODE = GC.CURRENCY_CODE

View Text - HTML Formatted

SELECT TO_CHAR(NULL) CONTRACT_NUM
, TO_NUMBER(NULL) CONTRACT_LINE
, PRL.EXPRESS_NAME TEMPLATE_ID
, PRL.SEQUENCE_NUM TEMPLATE_LINE_ID
, PRL.PO_HEADER_ID CONTRACT_ID
, PRL.PO_LINE_ID CONTRACT_LINE_ID
, PRL.ITEM_ID ITEM_ID
, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PRL.LINE_TYPE_ID LINE_TYPE_ID
, PRL.CATEGORY_ID CATEGORY_ID
, UOMTL.UOM_CODE UNIT_OF_MEASURE
, PRL.UNIT_PRICE FUNCTIONAL_PRICE
, GSB.CURRENCY_CODE CURRENCY
, PRL.UNIT_PRICE CONTRACT_PRICE
, TO_CHAR(NULL) CONTRACT_CURRENCY
, TO_CHAR(NULL) CONTRACT_RATE_TYPE
, TO_DATE(NULL) CONTRACT_RATE_DATE
, TO_NUMBER(NULL) CONTRACT_RATE
, TO_CHAR(NULL) PICTURE_URL
, PV.VENDOR_NAME SUPPLIER
, PRL.SUGGESTED_VENDOR_ID SUPPLIER_ID
, PRL.SUGGESTED_VENDOR_SITE_ID SUPPLIER_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE SUPPLIER_PART_NUMBER
, PRL.ORG_ID OPERATING_UNIT_ID
, PRL.CREATION_DATE CREATION_DATE
, GREATEST( PRL.LAST_UPDATE_DATE
, PRH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, PRH.INACTIVE_DATE INACTIVE_DATE
, PRH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, PRL.SOURCE_TYPE_CODE SOURCE_TYPE_CODE
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_REQEXPRESS_HEADERS_ALL PRH
, PO_REQEXPRESS_LINES_ALL PRL
, MTL_UNITS_OF_MEASURE_TL UOMTL
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
WHERE PRL.EXPRESS_NAME = PRH.EXPRESS_NAME
AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE
AND UOMTL.LANGUAGE IN (SELECT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B')
AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE
AND UOMTL.SOURCE_LANG = UOMTL.LANGUAGE
AND (PRL.ORG_ID IS NULL
AND PRH.ORG_ID IS NULL OR PRL.ORG_ID = PRH.ORG_ID)
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID (+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND (PRH.ORG_ID IS NULL
AND FSPA.ORG_ID IS NULL OR PRH.ORG_ID = FSPA.ORG_ID)
AND GSB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID
AND PRL.PO_HEADER_ID IS NULL UNION SELECT PH.SEGMENT1 CONTRACT_NUM
, PL.LINE_NUM CONTRACT_LINE
, PRL.EXPRESS_NAME TEMPLATE_ID
, PRL.SEQUENCE_NUM TEMPLATE_LINE_ID
, PRL.PO_HEADER_ID CONTRACT_ID
, PRL.PO_LINE_ID CONTRACT_LINE_ID
, PRL.ITEM_ID ITEM_ID
, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PRL.LINE_TYPE_ID LINE_TYPE_ID
, PL.CATEGORY_ID CATEGORY_ID
, UOMTL.UOM_CODE UNIT_OF_MEASURE
, DECODE(GC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PL.UNIT_PRICE*NVL(PH.RATE
, 1)
, GC.EXTENDED_PRECISION)
, ROUND(PL.UNIT_PRICE*NVL(PH.RATE
, 1)/GC.MINIMUM_ACCOUNTABLE_UNIT)* GC.MINIMUM_ACCOUNTABLE_UNIT) FUNCTIONAL_PRICE
, PH.CURRENCY_CODE CURRENCY_CODE
, PL.UNIT_PRICE CONTRACT_PRICE
, PH.CURRENCY_CODE CONTRACT_CURRENCY
, PH.RATE_TYPE CONTRACT_RATE_TYPE
, PH.RATE_DATE CONTRACT_RATE_DATE
, PH.RATE CONTRACT_RATE
, PL.ATTRIBUTE14 PICTURE_URL
, PV.VENDOR_NAME SUPPLIER
, PH.VENDOR_ID SUPPLIER_ID
, PH.VENDOR_SITE_ID SUPPLIER_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
, PL.VENDOR_PRODUCT_NUM SUPPLIER_PART_NUMBER
, PRL.ORG_ID OPERATING_UNIT_ID
, PRL.CREATION_DATE CREATION_DATE
, GREATEST( PRL.LAST_UPDATE_DATE
, PRH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, PRH.INACTIVE_DATE INACTIVE_DATE
, PRH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, PRL.SOURCE_TYPE_CODE SOURCE_TYPE_CODE
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_REQEXPRESS_HEADERS_ALL PRH
, PO_REQEXPRESS_LINES_ALL PRL
, MTL_UNITS_OF_MEASURE_TL UOMTL
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, GL_CURRENCIES GC
WHERE PRL.EXPRESS_NAME = PRH.EXPRESS_NAME
AND PRL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE
AND UOMTL.LANGUAGE IN (SELECT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B')
AND (PRL.ORG_ID IS NULL
AND PRH.ORG_ID IS NULL OR PRL.ORG_ID = PRH.ORG_ID)
AND PH.VENDOR_ID = PV.VENDOR_ID (+)
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND (PRH.ORG_ID IS NULL
AND FSPA.ORG_ID IS NULL OR PRH.ORG_ID = FSPA.ORG_ID)
AND PRL.PO_HEADER_ID IS NOT NULL
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PRL.PO_LINE_ID = PL.PO_LINE_ID
AND PH.CURRENCY_CODE = GC.CURRENCY_CODE