View: ICX_PO_SUPPLIER_CATALOGS_V

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

View Text

SELECT PH.PO_HEADER_ID
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME)
, PV.PARENT_VENDOR_ID
, PV.HOLD_FLAG
, PH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PLC.DISPLAYED_FIELD
, PH.SEGMENT1
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.TERMS_ID
, AT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.RATE_TYPE
, PH.RATE_DATE
, PH.RATE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID
, PH.START_DATE
, PH.END_DATE
, PH.BLANKET_TOTAL_AMOUNT
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, PL.LINE_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, TO_CHAR(PL.LINE_NUM)
, PL.ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID
, PL.ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PL.VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED
, PL.MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY
, GSB.CURRENCY_CODE
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5)
, TO_NUMBER(NULL)
, DCT.USER_CONVERSION_TYPE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD
, PV.ATTRIBUTE14
, PL.ATTRIBUTE14
, MSI.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, MSI.ORGANIZATION_ID
FROM PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PER_PEOPLE_F PPF
, GL_SETS_OF_BOOKS GSB
, GL_DAILY_CONVERSION_TYPES DCT
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, PO_LINE_TYPES PLT
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND ((PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
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 PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.TERMS_ID = AT.TERM_ID(+)
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'QUOTATION'
, 'BLANKET'
, 'PA'
, 'PLANNED'
, 'PO') = PDT.DOCUMENT_TYPE_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.QUOTE_TYPE_LOOKUP_CODE
, 'BLANKET'
, 'BLANKET'
, 'PLANNED'
, 'PLANNED') = PDT.DOCUMENT_SUBTYPE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS) = PLC.LOOKUP_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'RFQ/QUOTE STATUS'
, 'BLANKET'
, 'AUTHORIZATION STATUS'
, 'PLANNED'
, 'AUTHORIZATION STATUS') = PLC.LOOKUP_TYPE
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC2.LOOKUP_CODE
AND PLC2.LOOKUP_TYPE = 'ORDER TYPE'
AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
AND PL.ITEM_ID IS NOT NULL UNION SELECT PH.PO_HEADER_ID
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME)
, PV.PARENT_VENDOR_ID
, PV.HOLD_FLAG
, PH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PLC.DISPLAYED_FIELD
, PH.SEGMENT1
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.TERMS_ID
, AT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.RATE_TYPE
, PH.RATE_DATE
, PH.RATE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID
, PH.START_DATE
, PH.END_DATE
, PH.BLANKET_TOTAL_AMOUNT
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, PL.LINE_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, TO_CHAR(PL.LINE_NUM)
, PL.ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID
, PL.ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PL.VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED
, PL.MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY
, GSB.CURRENCY_CODE
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5)
, TO_NUMBER(NULL)
, DCT.USER_CONVERSION_TYPE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD
, PV.ATTRIBUTE14
, PL.ATTRIBUTE14
, NULL
, NULL
, TO_NUMBER(NULL)
FROM PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PER_PEOPLE_F PPF
, GL_SETS_OF_BOOKS GSB
, GL_DAILY_CONVERSION_TYPES DCT
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, PO_LINE_TYPES PLT
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND ((PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
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 PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.TERMS_ID = AT.TERM_ID(+)
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'QUOTATION'
, 'BLANKET'
, 'PA'
, 'PLANNED'
, 'PO') = PDT.DOCUMENT_TYPE_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.QUOTE_TYPE_LOOKUP_CODE
, 'BLANKET'
, 'BLANKET'
, 'PLANNED'
, 'PLANNED') = PDT.DOCUMENT_SUBTYPE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS) = PLC.LOOKUP_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'RFQ/QUOTE STATUS'
, 'BLANKET'
, 'AUTHORIZATION STATUS'
, 'PLANNED'
, 'AUTHORIZATION STATUS') = PLC.LOOKUP_TYPE
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC2.LOOKUP_CODE
AND PLC2.LOOKUP_TYPE = 'ORDER TYPE'
AND PL.ITEM_ID IS NULL

Columns