APPS.ICX_POR_SEARCH_CONTRACTS_V
[Home] [Help]
| Product: | ICX - Oracle iProcurement |
|---|---|
| Description: | |
| Implementation/DBA Data: |
APPS.ICX_POR_SEARCH_CONTRACTS_V
|
SELECT TO_NUMBER(NULL) POR_ASL_ID
, TO_CHAR(NULL) POR_TEMPLATE_NAME
, TO_NUMBER(NULL) POR_TEMPLATE_LINE_NUM
, TO_CHAR(NULL) POR_RESERVE_PO_NUMBER
, TO_DATE(NULL) POR_TEMPLATE_INACTIVE_DATE
, TO_CHAR(NULL) POR_RFQ_REQUIRED_FLAG
, PL.LINE_TYPE_ID POR_LINE_TYPE_ID
, PLT.LINE_TYPE POR_LINE_TYPE
, PLT.ORDER_TYPE_LOOKUP_CODE POR_ORDER_TYPE_CODE
, PLCL.DISPLAYED_FIELD POR_ORDER_TYPE
, PL.ITEM_ID POR_ITEM_ID
, PL.ITEM_DESCRIPTION POR_ITEM_DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS POR_ITEM_NUM
, PL.ITEM_REVISION POR_ITEM_REVISION
, MSIK.ATTRIBUTE14 POR_ITEM_URL
, MSIK.FULL_LEAD_TIME POR_ITEM_LT
, MSIK.PURCHASING_ENABLED_FLAG POR_ITEM_PURCHASABLE_FLAG
, MSIK.INVENTORY_ITEM_FLAG POR_ITEM_INVENTORY_FLAG
, MSIK.MUST_USE_APPROVED_VENDOR_FLAG POR_USE_APPROVED_SUPPLIER
, PL.CATEGORY_ID POR_CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS POR_CATEGORY
, MCK.DESCRIPTION POR_CATEGORY_DESC
, PH.VENDOR_ID POR_SUPPLIER_ID
, PV.VENDOR_NAME POR_SUPPL_NAME
, PH.VENDOR_SITE_ID POR_SUPPLIER_SITE_ID
, PVS.VENDOR_SITE_CODE POR_SUPPL_SITE
, PH.VENDOR_CONTACT_ID POR_SUPPLIER_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME) POR_SUPPL_CONTACT
, PVC.PHONE POR_SUPPL_CONTACT_PHONE
, PV.ATTRIBUTE14 POR_SUPPL_URL
, PL.VENDOR_PRODUCT_NUM POR_SUPPL_ITEM_NUM
, PL.ATTRIBUTE14 POR_SUPPL_ITEM_URL
, PV.HOLD_FLAG POR_SUPPL_HOLD_FLAG
, TO_CHAR(NULL) POR_MANUFACTURER_NAME
, TO_CHAR(NULL) POR_MANUFACTURER_PART_NUM
, PL.UNIT_MEAS_LOOKUP_CODE POR_UNIT_OF_MEASURE
, GSB.CURRENCY_CODE POR_CURRENCY_CODE
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5) POR_CURRENCY_LINE_PRICE
, PH.CURRENCY_CODE POR_FOREIGN_CURRENCY_CODE
, PH.RATE_TYPE POR_RATE_TYPE
, GDCT.USER_CONVERSION_TYPE POR_FOREIGN_CURRENCY_RATE_TYPE
, PH.RATE_DATE POR_FOREIGN_CURRENCY_RATE_DATE
, PH.RATE POR_FOREIGN_CURRENCY_RATE
, PL.UNIT_PRICE POR_UNIT_PRICE
, PH.TYPE_LOOKUP_CODE POR_TYPE_LOOKUP_CODE
, PDT.TYPE_NAME POR_SOURCE_DOC_TYPE_CODE
, PL.PO_HEADER_ID POR_PO_HEADER_ID
, PH.SEGMENT1 POR_SOURCE_DOC_NUM
, PL.PO_LINE_ID POR_PO_LINE_ID
, PL.LINE_NUM POR_SOURCE_DOC_LINE_NUM
, PH.START_DATE POR_SOURCE_DOC_EFFECTIVE_FROM
, PH.END_DATE POR_SOURCE_DOC_EFFECTIVE_TO
, PL.COMMITTED_AMOUNT POR_SOURCE_DOC_AMOUNT_AGREED
, PH.AMOUNT_LIMIT POR_SOURCE_DOC_AMOUNT_LIMIT
, PL.MIN_RELEASE_AMOUNT POR_SOURCE_DOC_MIN_RELEASE_AMT
, PL.QUANTITY_COMMITTED POR_SOURCE_DOC_QUANTITY_AGREED
, PL.MIN_ORDER_QUANTITY POR_SOURCE_DOC_MIN_ORDER_QTY
, PL.MAX_ORDER_QUANTITY POR_SOURCE_DOC_MAX_ORDER_QTY
, PH.TERMS_ID POR_TERMS_ID
, AT.NAME POR_PAYMENT_TERMS
, PH.SHIP_VIA_LOOKUP_CODE POR_SHIP_VIA_LOOKUP_CODE
, OFR.DESCRIPTION POR_SHIP_VIA
, PH.FREIGHT_TERMS_LOOKUP_CODE POR_FREIGHT_TERMS_LOOKUP_CODE
, PLCFR.DISPLAYED_FIELD POR_FREIGHT_TERMS
, PH.FOB_LOOKUP_CODE POR_FOB_LOOKUP_CODE
, PLCFOB.DISPLAYED_FIELD POR_FOB
, PH.AGENT_ID POR_AGENT_ID
, PPF.FULL_NAME POR_BUYER_NAME
, PL.ATTRIBUTE1 POR_CATTRIBUTE1
, PL.ATTRIBUTE2 POR_CATTRIBUTE2
, PL.ATTRIBUTE3 POR_CATTRIBUTE3
, PL.ATTRIBUTE4 POR_CATTRIBUTE4
, PL.ATTRIBUTE5 POR_CATTRIBUTE5
, PL.ATTRIBUTE6 POR_CATTRIBUTE6
, PL.ATTRIBUTE7 POR_CATTRIBUTE7
, PL.ATTRIBUTE8 POR_CATTRIBUTE8
, PL.ATTRIBUTE9 POR_CATTRIBUTE9
, PL.ATTRIBUTE10 POR_CATTRIBUTE10
, PL.ATTRIBUTE11 POR_CATTRIBUTE11
, PL.ATTRIBUTE12 POR_CATTRIBUTE12
, PL.ATTRIBUTE13 POR_CATTRIBUTE13
, PL.ATTRIBUTE14 POR_CATTRIBUTE14
, PL.ATTRIBUTE15 POR_CATTRIBUTE15
, TO_CHAR(NULL) POR_TATTRIBUTE1
, TO_CHAR(NULL) POR_TATTRIBUTE2
, TO_CHAR(NULL) POR_TATTRIBUTE3
, TO_CHAR(NULL) POR_TATTRIBUTE4
, TO_CHAR(NULL) POR_TATTRIBUTE5
, TO_CHAR(NULL) POR_TATTRIBUTE6
, TO_CHAR(NULL) POR_TATTRIBUTE7
, TO_CHAR(NULL) POR_TATTRIBUTE8
, TO_CHAR(NULL) POR_TATTRIBUTE9
, TO_CHAR(NULL) POR_TATTRIBUTE10
, TO_CHAR(NULL) POR_TATTRIBUTE11
, TO_CHAR(NULL) POR_TATTRIBUTE12
, TO_CHAR(NULL) POR_TATTRIBUTE13
, TO_CHAR(NULL) POR_TATTRIBUTE14
, TO_CHAR(NULL) POR_TATTRIBUTE15
, TO_CHAR(NULL) POR_AATTRIBUTE1
, TO_CHAR(NULL) POR_AATTRIBUTE2
, TO_CHAR(NULL) POR_AATTRIBUTE3
, TO_CHAR(NULL) POR_AATTRIBUTE4
, TO_CHAR(NULL) POR_AATTRIBUTE5
, TO_CHAR(NULL) POR_AATTRIBUTE6
, TO_CHAR(NULL) POR_AATTRIBUTE7
, TO_CHAR(NULL) POR_AATTRIBUTE8
, TO_CHAR(NULL) POR_AATTRIBUTE9
, TO_CHAR(NULL) POR_AATTRIBUTE10
, TO_CHAR(NULL) POR_AATTRIBUTE11
, TO_CHAR(NULL) POR_AATTRIBUTE12
, TO_CHAR(NULL) POR_AATTRIBUTE13
, TO_CHAR(NULL) POR_AATTRIBUTE14
, TO_CHAR(NULL) POR_AATTRIBUTE15
, TO_CHAR(NULL) POR_IATTRIBUTE1
, TO_CHAR(NULL) POR_IATTRIBUTE2
, TO_CHAR(NULL) POR_IATTRIBUTE3
, TO_CHAR(NULL) POR_IATTRIBUTE4
, TO_CHAR(NULL) POR_IATTRIBUTE5
, TO_CHAR(NULL) POR_IATTRIBUTE6
, TO_CHAR(NULL) POR_IATTRIBUTE7
, TO_CHAR(NULL) POR_IATTRIBUTE8
, TO_CHAR(NULL) POR_IATTRIBUTE9
, TO_CHAR(NULL) POR_IATTRIBUTE10
, TO_CHAR(NULL) POR_IATTRIBUTE11
, TO_CHAR(NULL) POR_IATTRIBUTE12
, TO_CHAR(NULL) POR_IATTRIBUTE13
, TO_CHAR(NULL) POR_IATTRIBUTE14
, TO_CHAR(NULL) POR_IATTRIBUTE15
FROM MTL_SYSTEM_ITEMS_KFV MSIK
, PO_LOOKUP_CODES PLCFR
, PO_LOOKUP_CODES PLCFOB
, ORG_FREIGHT OFR
, AP_TERMS AT
, GL_DAILY_CONVERSION_TYPES GDCT
, PO_VENDOR_CONTACTS PVC
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, MTL_CATEGORIES_KFV MCK
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLCL
, PO_LINE_TYPES PLT
, PER_PEOPLE_F PPF
, PO_HEADERS PH
, PO_LINES PL
WHERE PL.ITEM_ID IS NOT NULL
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND ((PH.APPROVED_DATE IS NOT NULL
AND PH.APPROVED_FLAG = '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 (PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
AND (TRUNC(SYSDATE) BETWEEN NVL(TRUNC(PH.START_DATE)
, TRUNC( SYSDATE-1))
AND NVL(TRUNC(PH.END_DATE)
, TRUNC(SYSDATE+1)))
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 PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLCL.LOOKUP_CODE
AND PLCL.LOOKUP_TYPE = 'ORDER TYPE'
AND MCK.CATEGORY_ID = PL.CATEGORY_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 GSB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
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.RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND PH.TERMS_ID = AT.TERM_ID (+)
AND PH.SHIP_VIA_LOOKUP_CODE = OFR.FREIGHT_CODE (+)
AND (OFR.ORGANIZATION_ID IS NULL OR (OFR.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID))
AND NVL(PH.FREIGHT_TERMS_LOOKUP_CODE
, TO_CHAR(PH.PO_HEADER_ID)) = PLCFR.LOOKUP_CODE (+)
AND PLCFR.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND NVL(PH.FOB_LOOKUP_CODE
, TO_CHAR(PH.PO_HEADER_ID)) = PLCFOB.LOOKUP_CODE (+)
AND PLCFOB.LOOKUP_TYPE(+) = 'FOB'
AND MSIK.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = PL.ITEM_ID UNION SELECT TO_NUMBER(NULL) POR_ASL_ID
, TO_CHAR(NULL) POR_TEMPLATE_NAME
, TO_NUMBER(NULL) POR_TEMPLATE_LINE_NUM
, TO_CHAR(NULL) POR_RESERVE_PO_NUMBER
, TO_DATE(NULL) POR_TEMPLATE_INACTIVE_DATE
, TO_CHAR(NULL) POR_RFQ_REQUIRED_FLAG
, PL.LINE_TYPE_ID POR_LINE_TYPE_ID
, PLT.LINE_TYPE POR_LINE_TYPE
, PLT.ORDER_TYPE_LOOKUP_CODE POR_ORDER_TYPE_CODE
, PLCL.DISPLAYED_FIELD POR_ORDER_TYPE
, PL.ITEM_ID POR_ITEM_ID
, PL.ITEM_DESCRIPTION POR_ITEM_DESCRIPTION
, TO_CHAR(NULL) POR_ITEM_NUM
, PL.ITEM_REVISION POR_ITEM_REVISION
, TO_CHAR(NULL) POR_ITEM_URL
, TO_NUMBER(NULL) POR_ITEM_LT
, TO_CHAR(NULL) POR_ITEM_PURCHASABLE_FLAG
, TO_CHAR(NULL) POR_ITEM_INVENTORY_FLAG
, TO_CHAR(NULL) POR_USE_APPROVED_SUPPLIER
, PL.CATEGORY_ID POR_CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS POR_CATEGORY
, MCK.DESCRIPTION POR_CATEGORY_DESC
, PH.VENDOR_ID POR_SUPPLIER_ID
, PV.VENDOR_NAME POR_SUPPL_NAME
, PH.VENDOR_SITE_ID POR_SUPPLIER_SITE_ID
, PVS.VENDOR_SITE_CODE POR_SUPPL_SITE
, PH.VENDOR_CONTACT_ID POR_SUPPLIER_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME) POR_SUPPL_CONTACT
, PVC.PHONE POR_SUPPL_CONTACT_PHONE
, PV.ATTRIBUTE14 POR_SUPPL_URL
, PL.VENDOR_PRODUCT_NUM POR_SUPPL_ITEM_NUM
, PL.ATTRIBUTE14 POR_SUPPL_ITEM_URL
, PV.HOLD_FLAG POR_SUPPL_HOLD_FLAG
, TO_CHAR(NULL) POR_MANUFACTURER_NAME
, TO_CHAR(NULL) POR_MANUFACTURER_PART_NUM
, PL.UNIT_MEAS_LOOKUP_CODE POR_UNIT_OF_MEASURE
, GSB.CURRENCY_CODE POR_CURRENCY_CODE
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5) POR_CURRENCY_LINE_PRICE
, PH.CURRENCY_CODE POR_FOREIGN_CURRENCY_CODE
, PH.RATE_TYPE POR_RATE_TYPE
, GDCT.USER_CONVERSION_TYPE POR_FOREIGN_CURRENCY_RATE_TYPE
, PH.RATE_DATE POR_FOREIGN_CURRENCY_RATE_DATE
, PH.RATE POR_FOREIGN_CURRENCY_RATE
, PL.UNIT_PRICE POR_UNIT_PRICE
, PH.TYPE_LOOKUP_CODE POR_TYPE_LOOKUP_CODE
, PDT.TYPE_NAME POR_SOURCE_DOC_TYPE_CODE
, PL.PO_HEADER_ID POR_PO_HEADER_ID
, PH.SEGMENT1 POR_SOURCE_DOC_NUM
, PL.PO_LINE_ID POR_PO_LINE_ID
, PL.LINE_NUM POR_SOURCE_DOC_LINE_NUM
, PH.START_DATE POR_SOURCE_DOC_EFFECTIVE_FROM
, PH.END_DATE POR_SOURCE_DOC_EFFECTIVE_TO
, PL.COMMITTED_AMOUNT POR_SOURCE_DOC_AMOUNT_AGREED
, PH.AMOUNT_LIMIT POR_SOURCE_DOC_AMOUNT_LIMIT
, PL.MIN_RELEASE_AMOUNT POR_SOURCE_DOC_MIN_RELEASE_AMT
, PL.QUANTITY_COMMITTED POR_SOURCE_DOC_QUANTITY_AGREED
, PL.MIN_ORDER_QUANTITY POR_SOURCE_DOC_MIN_ORDER_QTY
, PL.MAX_ORDER_QUANTITY POR_SOURCE_DOC_MAX_ORDER_QTY
, PH.TERMS_ID POR_TERMS_ID
, AT.NAME POR_PAYMENT_TERMS
, PH.SHIP_VIA_LOOKUP_CODE POR_SHIP_VIA_LOOKUP_CODE
, OFR.DESCRIPTION POR_SHIP_VIA
, PH.FREIGHT_TERMS_LOOKUP_CODE POR_FREIGHT_TERMS_LOOKUP_CODE
, PLCFR.DISPLAYED_FIELD POR_FREIGHT_TERMS
, PH.FOB_LOOKUP_CODE POR_FOB_LOOKUP_CODE
, PLCFOB.DISPLAYED_FIELD POR_FOB
, PH.AGENT_ID POR_AGENT_ID
, PPF.FULL_NAME POR_BUYER_NAME
, PL.ATTRIBUTE1 POR_CATTRIBUTE1
, PL.ATTRIBUTE2 POR_CATTRIBUTE2
, PL.ATTRIBUTE3 POR_CATTRIBUTE3
, PL.ATTRIBUTE4 POR_CATTRIBUTE4
, PL.ATTRIBUTE5 POR_CATTRIBUTE5
, PL.ATTRIBUTE6 POR_CATTRIBUTE6
, PL.ATTRIBUTE7 POR_CATTRIBUTE7
, PL.ATTRIBUTE8 POR_CATTRIBUTE8
, PL.ATTRIBUTE9 POR_CATTRIBUTE9
, PL.ATTRIBUTE10 POR_CATTRIBUTE10
, PL.ATTRIBUTE11 POR_CATTRIBUTE11
, PL.ATTRIBUTE12 POR_CATTRIBUTE12
, PL.ATTRIBUTE13 POR_CATTRIBUTE13
, PL.ATTRIBUTE14 POR_CATTRIBUTE14
, PL.ATTRIBUTE15 POR_CATTRIBUTE15
, TO_CHAR(NULL) POR_TATTRIBUTE1
, TO_CHAR(NULL) POR_TATTRIBUTE2
, TO_CHAR(NULL) POR_TATTRIBUTE3
, TO_CHAR(NULL) POR_TATTRIBUTE4
, TO_CHAR(NULL) POR_TATTRIBUTE5
, TO_CHAR(NULL) POR_TATTRIBUTE6
, TO_CHAR(NULL) POR_TATTRIBUTE7
, TO_CHAR(NULL) POR_TATTRIBUTE8
, TO_CHAR(NULL) POR_TATTRIBUTE9
, TO_CHAR(NULL) POR_TATTRIBUTE10
, TO_CHAR(NULL) POR_TATTRIBUTE11
, TO_CHAR(NULL) POR_TATTRIBUTE12
, TO_CHAR(NULL) POR_TATTRIBUTE13
, TO_CHAR(NULL) POR_TATTRIBUTE14
, TO_CHAR(NULL) POR_TATTRIBUTE15
, TO_CHAR(NULL) POR_AATTRIBUTE1
, TO_CHAR(NULL) POR_AATTRIBUTE2
, TO_CHAR(NULL) POR_AATTRIBUTE3
, TO_CHAR(NULL) POR_AATTRIBUTE4
, TO_CHAR(NULL) POR_AATTRIBUTE5
, TO_CHAR(NULL) POR_AATTRIBUTE6
, TO_CHAR(NULL) POR_AATTRIBUTE7
, TO_CHAR(NULL) POR_AATTRIBUTE8
, TO_CHAR(NULL) POR_AATTRIBUTE9
, TO_CHAR(NULL) POR_AATTRIBUTE10
, TO_CHAR(NULL) POR_AATTRIBUTE11
, TO_CHAR(NULL) POR_AATTRIBUTE12
, TO_CHAR(NULL) POR_AATTRIBUTE13
, TO_CHAR(NULL) POR_AATTRIBUTE14
, TO_CHAR(NULL) POR_AATTRIBUTE15
, TO_CHAR(NULL) POR_IATTRIBUTE1
, TO_CHAR(NULL) POR_IATTRIBUTE2
, TO_CHAR(NULL) POR_IATTRIBUTE3
, TO_CHAR(NULL) POR_IATTRIBUTE4
, TO_CHAR(NULL) POR_IATTRIBUTE5
, TO_CHAR(NULL) POR_IATTRIBUTE6
, TO_CHAR(NULL) POR_IATTRIBUTE7
, TO_CHAR(NULL) POR_IATTRIBUTE8
, TO_CHAR(NULL) POR_IATTRIBUTE9
, TO_CHAR(NULL) POR_IATTRIBUTE10
, TO_CHAR(NULL) POR_IATTRIBUTE11
, TO_CHAR(NULL) POR_IATTRIBUTE12
, TO_CHAR(NULL) POR_IATTRIBUTE13
, TO_CHAR(NULL) POR_IATTRIBUTE14
, TO_CHAR(NULL) POR_IATTRIBUTE15
FROM PO_LOOKUP_CODES PLCFR
, PO_LOOKUP_CODES PLCFOB
, ORG_FREIGHT OFR
, AP_TERMS AT
, GL_DAILY_CONVERSION_TYPES GDCT
, PO_VENDOR_CONTACTS PVC
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, MTL_CATEGORIES_KFV MCK
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLCL
, PO_LINE_TYPES PLT
, PER_PEOPLE_F PPF
, PO_HEADERS PH
, PO_LINES PL
WHERE PL.ITEM_ID IS NULL
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION')
AND ((PH.APPROVED_DATE IS NOT NULL
AND PH.APPROVED_FLAG = '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 (PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
AND (TRUNC(SYSDATE) BETWEEN NVL(TRUNC(PH.START_DATE)
, TRUNC( SYSDATE-1))
AND NVL(TRUNC(PH.END_DATE)
, TRUNC(SYSDATE+1)))
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 PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLCL.LOOKUP_CODE
AND PLCL.LOOKUP_TYPE = 'ORDER TYPE'
AND MCK.CATEGORY_ID = PL.CATEGORY_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 GSB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
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.RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND PH.TERMS_ID = AT.TERM_ID (+)
AND PH.SHIP_VIA_LOOKUP_CODE = OFR.FREIGHT_CODE (+)
AND (OFR.ORGANIZATION_ID IS NULL OR (OFR.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID))
AND NVL(PH.FREIGHT_TERMS_LOOKUP_CODE
, TO_CHAR(PH.PO_HEADER_ID)) = PLCFR.LOOKUP_CODE (+)
AND PLCFR.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND NVL(PH.FOB_LOOKUP_CODE
, TO_CHAR(PH.PO_HEADER_ID)) = PLCFOB.LOOKUP_CODE (+)
AND PLCFOB.LOOKUP_TYPE(+) = 'FOB'