VIEW: APPS.ICX_POR_REQ_INACTIVE_CONTRACTS

Source

View Text - Preformatted

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, PL.UNIT_MEAS_LOOKUP_CODE UNIT_OF_MEASURE, ROUND(PL.UNIT_PRICE*NVL(PH.RATE,1),5) UNIT_PRICE, GSB.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, 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 FROM PO_VENDORS PV, PER_PEOPLE_F PPF, PO_LINE_TYPES PLT, PO_HEADERS_ALL PH, PO_LINES_ALL PL, GL_SETS_OF_BOOKS GSB, FINANCIALS_SYSTEM_PARAMS_ALL FSPA, GL_DAILY_CONVERSION_TYPES GDCT WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID AND PH.TYPE_LOOKUP_CODE IN ('BLANKET','QUOTATION') AND PH.AGENT_ID = PPF.PERSON_ID AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PH.VENDOR_ID = PV.VENDOR_ID AND NOT (((PH.APPROVED_DATE IS NOT NULL AND NVL(PH.APPROVED_FLAG,'N') = 'Y' AND NVL(PH.CANCEL_FLAG, 'N') != 'Y' AND NVL(PH.FROZEN_FLAG, 'N') != 'Y' AND NVL(PH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND NVL(PL.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND NVL(PL.CANCEL_FLAG, 'N') != 'Y') OR (NVL(PH.STATUS_LOOKUP_CODE,'FOO') = 'A' AND NVL(PH.QUOTATION_CLASS_CODE,'FOO') = 'CATALOG')) AND SYSDATE < NVL(PL.EXPIRATION_DATE, SYSDATE+1) AND NVL(PLT.OUTSIDE_OPERATION_FLAG, 'N') = 'N' AND (SYSDATE BETWEEN NVL(PH.START_DATE, SYSDATE-1) AND NVL(PH.END_DATE, SYSDATE+1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE), TRUNC(SYSDATE))) AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE-1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE+1)) AND PH.RATE_TYPE = GDCT.CONVERSION_TYPE (+) AND (PH.ORG_ID IS NULL AND FSPA.ORG_ID IS NULL OR PH.ORG_ID = FSPA.ORG_ID) AND GSB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID

View Text - HTML Formatted

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
, PL.UNIT_MEAS_LOOKUP_CODE UNIT_OF_MEASURE
, ROUND(PL.UNIT_PRICE*NVL(PH.RATE
, 1)
, 5) UNIT_PRICE
, GSB.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
, 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
FROM PO_VENDORS PV
, PER_PEOPLE_F PPF
, PO_LINE_TYPES PLT
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, GL_DAILY_CONVERSION_TYPES GDCT
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND PH.AGENT_ID = PPF.PERSON_ID
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND NOT (((PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND NVL(PL.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (NVL(PH.STATUS_LOOKUP_CODE
, 'FOO') = 'A'
AND NVL(PH.QUOTATION_CLASS_CODE
, 'FOO') = 'CATALOG'))
AND SYSDATE < NVL(PL.EXPIRATION_DATE
, SYSDATE+1)
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND (SYSDATE BETWEEN NVL(PH.START_DATE
, SYSDATE-1)
AND NVL(PH.END_DATE
, SYSDATE+1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE)
, TRUNC(SYSDATE)))
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE-1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE+1))
AND PH.RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND (PH.ORG_ID IS NULL
AND FSPA.ORG_ID IS NULL OR PH.ORG_ID = FSPA.ORG_ID)
AND GSB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID