View: ICX_POR_EXT_CONTRACTS

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

View Text

SELECT PH.SEGMENT1 CONTRACT_NUM
, PL.LINE_NUM CONTRACT_LINE
, PL.PO_HEADER_ID CONTRACT_ID
, PL.PO_LINE_ID CONTRACT_LINE_ID
, PL.ITEM_ID ITEM_ID
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.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
, 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
, PL.ORG_ID OPERATING_UNIT_ID
, PL.CREATION_DATE CREATION_DATE
, GREATEST(PL.LAST_UPDATE_DATE
, PH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, PH.APPROVED_DATE CONTRACT_APPROVED_DATE
, PH.APPROVED_FLAG CONTRACT_APPROVED_FLAG
, PH.CANCEL_FLAG CONTRACT_CANCEL_FLAG
, PH.FROZEN_FLAG CONTRACT_FROZEN_FLAG
, PH.CLOSED_CODE CONTRACT_CLOSED_CODE
, PL.CLOSED_CODE CONTRACT_LINE_CLOSED_CODE
, PL.CANCEL_FLAG CONTRACT_LINE_CANCEL_FLAG
, PH.STATUS_LOOKUP_CODE CONTRACT_STATUS_LOOKUP_CODE
, PH.QUOTATION_CLASS_CODE CONTRACT_QUOTATION_CLASS_CODE
, PH.START_DATE CONTRACT_START_DATE
, PH.END_DATE CONTRACT_END_DATE
, PH.AGENT_ID AGENT_ID
, PL.EXPIRATION_DATE EXPIRATION_DATE
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, MTL_UNITS_OF_MEASURE_TL UOMTL
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, GL_CURRENCIES GC
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PL.UNIT_MEAS_LOOKUP_CODE = UOMTL.UNIT_OF_MEASURE
AND UOMTL.LANGUAGE IN (SELECT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B')
AND PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND (PH.ORG_ID IS NULL
AND FSPA.ORG_ID IS NULL OR PH.ORG_ID = FSPA.ORG_ID)
AND PH.CURRENCY_CODE = GC.CURRENCY_CODE

Columns