VIEW: APPS.ICX_PO_SUPPL_CATALOG_ITEMS_V

Source

View Text - Preformatted

SELECT TO_CHAR(PH.PO_HEADER_ID) PO_HEADER_ID , PH.VENDOR_ID , NVL(PV.VENDOR_NAME, '-') VENDOR_NAME , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '|| PVC.FIRST_NAME) VENDOR_CONTACT_NAME , PV.PARENT_VENDOR_ID , PV.HOLD_FLAG , PH.VENDOR_SITE_ID , NVL(PVS.VENDOR_SITE_CODE, '-') VENDOR_SITE_CODE , PH.TYPE_LOOKUP_CODE , PDT.TYPE_NAME , DECODE(PH.TYPE_LOOKUP_CODE, 'QUOTATION',PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) DOCUMENT_STATUS_CODE , PLC.DISPLAYED_FIELD DOCUMENT_STATUS_DSP , PH.SEGMENT1 DOCUMENT_NUM , PH.AGENT_ID , PPF.FULL_NAME AGENT_NAME , PH.TERMS_ID HEADER_PAYMENT_TERMS_ID , AT.NAME HEADER_PAYMENT_TERMS_NAME , PH.SHIP_VIA_LOOKUP_CODE HEADER_SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE HEADER_FREIGHT_TERMS_CODE , PH.FOB_LOOKUP_CODE HEADER_FOB_LOOKUP_CODE , PH.CURRENCY_CODE , PH.RATE_TYPE , PH.RATE_DATE , PH.RATE , PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUM , PH.APPROVAL_REQUIRED_FLAG QUOTE_APPROVAL_REQUIRED_FLAG , PH.FROM_HEADER_ID ASSOCIATED_RFQ_ID , PH.START_DATE EFFECTIVE_FROM , PH.END_DATE EFFECTIVE_TO , PH.BLANKET_TOTAL_AMOUNT AMOUNT_AGREED , PH.AMOUNT_LIMIT , PH.MIN_RELEASE_AMOUNT HEADER_MIN_RELEASE_AMOUNT , PL.PO_LINE_ID , TO_CHAR(PL.LINE_NUM) LINE_NUM , TO_CHAR(PL.LINE_TYPE_ID) LINE_TYPE_ID , PLT.LINE_TYPE , TO_CHAR(PL.LINE_NUM) LINE_SHIP_CONCAT_NUM , TO_CHAR(PL.ITEM_ID) ITEM_ID , PL.ITEM_REVISION ,PL.CATEGORY_ID CATEGORY_ID , MCK.CONCATENATED_SEGMENTS CATEGORY_NAME , PL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PL.UNIT_MEAS_LOOKUP_CODE LINE_UOM , PL.QUANTITY LINE_QUANTITY , PL.UNIT_PRICE CURRENCY_LINE_PRICE , NVL(PL.VENDOR_PRODUCT_NUM, '-') VENDOR_PRODUCT_NUM , PL.QUANTITY_COMMITTED QUANTITY_AGREED , PL.MIN_ORDER_QUANTITY QUOTE_MIN_ORDER_QUANTITY , PL.MAX_ORDER_QUANTITY QUOTE_MAX_ORDER_QUANTITY , GSB.CURRENCY_CODE FUNCTIONAL_CURRENCY , ROUND(PL.UNIT_PRICE * NVL(PH.RATE, 1), 5) FUNCTIONAL_LINE_PRICE , TO_NUMBER(NULL) FUNCTIONAL_SHIPMENT_PRICE , NVL(TO_NUMBER(NULL),ROUND(PL.UNIT_PRICE * NVL(PH.RATE, 1), 5)) PRICE , DCT.USER_CONVERSION_TYPE RATE_TYPE_NAME , PLT.OUTSIDE_OPERATION_FLAG , PLT.ORDER_TYPE_LOOKUP_CODE , PLC2.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP , PV.ATTRIBUTE14 SUPPLIER_URL , NVL(PL.ATTRIBUTE14, MSI.ATTRIBUTE14) SUPPLIER_ITEM_URL , MSI.ATTRIBUTE14 ITEM_URL , MSI.CONCATENATED_SEGMENTS ITEM_NUMBER , MSI.ORGANIZATION_ID FROM PO_DOCUMENT_TYPES PDT , MTL_CATEGORIES_KFV MCK, PO_LOOKUP_CODES PLC , PO_LOOKUP_CODES PLC2 , PER_PEOPLE_F PPF , GL_DAILY_CONVERSION_TYPES DCT , PO_VENDORS PV , PO_VENDOR_SITES PVS , PO_VENDOR_CONTACTS PVC , AP_TERMS AT , PO_LINE_TYPES PLT, PO_HEADERS PH , PO_LINES PL , MTL_SYSTEM_ITEMS_KFV MSI , GL_SETS_OF_BOOKS GSB , FINANCIALS_SYSTEM_PARAMETERS FSP 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 PH.APPROVED_FLAG = 'Y' 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 MSI.ORGANIZATION_ID = DECODE(PL.ITEM_ID, NULL, NULL, FSP.INVENTORY_ORGANIZATION_ID) AND MCK.CATEGORY_ID = PL.CATEGORY_ID AND PL.ITEM_ID IS NOT NULL AND MSI.PURCHASING_ENABLED_FLAG = 'Y' UNION SELECT TO_CHAR(PH.PO_HEADER_ID) PO_HEADER_ID , PH.VENDOR_ID , NVL(PV.VENDOR_NAME, '-') VENDOR_NAME, PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID,NULL, NULL, PVC.LAST_NAME||', '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME, PV.PARENT_VENDOR_ID , PV.HOLD_FLAG , PH.VENDOR_SITE_ID , NVL(PVS.VENDOR_SITE_CODE, '-') VENDOR_SITE_CODE , PH.TYPE_LOOKUP_CODE , PDT.TYPE_NAME , DECODE(PH.TYPE_LOOKUP_CODE, 'QUOTATION',PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) DOCUMENT_STATUS_CODE, PLC.DISPLAYED_FIELD DOCUMENT_STATUS_DSP, PH.SEGMENT1 DOCUMENT_NUM, PH.AGENT_ID , PPF.FULL_NAME AGENT_NAME, PH.TERMS_ID HEADER_PAYMENT_TERMS_ID, AT.NAME HEADER_PAYMENT_TERMS_NAME, PH.SHIP_VIA_LOOKUP_CODE HEADER_SHIP_VIA_LOOKUP_CODE, PH.FREIGHT_TERMS_LOOKUP_CODE HEADER_FREIGHT_TERMS_CODE, PH.FOB_LOOKUP_CODE HEADER_FOB_LOOKUP_CODE, PH.CURRENCY_CODE , PH.RATE_TYPE , PH.RATE_DATE , PH.RATE , PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUM, PH.APPROVAL_REQUIRED_FLAG QUOTE_APPROVAL_REQUIRED_FLAG, PH.FROM_HEADER_ID ASSOCIATED_RFQ_ID, PH.START_DATE EFFECTIVE_FROM, PH.END_DATE EFFECTIVE_TO, PH.BLANKET_TOTAL_AMOUNT AMOUNT_AGREED, PH.AMOUNT_LIMIT , PH.MIN_RELEASE_AMOUNT HEADER_MIN_RELEASE_AMOUNT, PL.PO_LINE_ID , TO_CHAR(PL.LINE_NUM) LINE_NUM , TO_CHAR(PL.LINE_TYPE_ID) LINE_TYPE_ID , PLT.LINE_TYPE , TO_CHAR(PL.LINE_NUM) LINE_SHIP_CONCAT_NUM, TO_CHAR(PL.ITEM_ID) ITEM_ID, PL.ITEM_REVISION , PL.CATEGORY_ID CATEGORY_ID , MCK.CONCATENATED_SEGMENTS CATEGORY_NAME, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION, PL.UNIT_MEAS_LOOKUP_CODE LINE_UOM, PL.QUANTITY LINE_QUANTITY, PL.UNIT_PRICE CURRENCY_LINE_PRICE, NVL(PL.VENDOR_PRODUCT_NUM, '-') VENDOR_PRODUCT_NUM , PL.QUANTITY_COMMITTED QUANTITY_AGREED, PL.MIN_ORDER_QUANTITY QUOTE_MIN_ORDER_QUANTITY, PL.MAX_ORDER_QUANTITY QUOTE_MAX_ORDER_QUANTITY, GSB.CURRENCY_CODE FUNCTIONAL_CURRENCY, ROUND(PL.UNIT_PRICE * NVL(PH.RATE, 1), 5) FUNCTIONAL_LINE_PRICE, TO_NUMBER(NULL) FUNCTIONAL_SHIPMENT_PRICE, NVL(TO_NUMBER(NULL),ROUND(PL.UNIT_PRICE * NVL(PH.RATE, 1), 5)) PRICE, DCT.USER_CONVERSION_TYPE RATE_TYPE_NAME, PLT.OUTSIDE_OPERATION_FLAG , PLT.ORDER_TYPE_LOOKUP_CODE , PLC2.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP, PV.ATTRIBUTE14 SUPPLIER_URL, PL.ATTRIBUTE14 SUPPLIER_ITEM_URL, NULL ITEM_URL, NULL ITEM_NUMBER, FSP.INVENTORY_ORGANIZATION_ID FROM PO_DOCUMENT_TYPES PDT , MTL_CATEGORIES_KFV MCK, PO_LOOKUP_CODES PLC , PO_LOOKUP_CODES PLC2 , PER_PEOPLE_F PPF , GL_DAILY_CONVERSION_TYPES DCT , PO_VENDORS PV , PO_VENDOR_SITES PVS , PO_VENDOR_CONTACTS PVC , AP_TERMS AT , PO_LINE_TYPES PLT, PO_HEADERS PH , PO_LINES PL , GL_SETS_OF_BOOKS GSB , FINANCIALS_SYSTEM_PARAMETERS FSP 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 PH.APPROVED_FLAG = 'Y' 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 MCK.CATEGORY_ID = PL.CATEGORY_ID AND PL.ITEM_ID IS NULL

View Text - HTML Formatted

SELECT TO_CHAR(PH.PO_HEADER_ID) PO_HEADER_ID
, PH.VENDOR_ID
, NVL(PV.VENDOR_NAME
, '-') VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME) VENDOR_CONTACT_NAME
, PV.PARENT_VENDOR_ID
, PV.HOLD_FLAG
, PH.VENDOR_SITE_ID
, NVL(PVS.VENDOR_SITE_CODE
, '-') VENDOR_SITE_CODE
, PH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS) DOCUMENT_STATUS_CODE
, PLC.DISPLAYED_FIELD DOCUMENT_STATUS_DSP
, PH.SEGMENT1 DOCUMENT_NUM
, PH.AGENT_ID
, PPF.FULL_NAME AGENT_NAME
, PH.TERMS_ID HEADER_PAYMENT_TERMS_ID
, AT.NAME HEADER_PAYMENT_TERMS_NAME
, PH.SHIP_VIA_LOOKUP_CODE HEADER_SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE HEADER_FREIGHT_TERMS_CODE
, PH.FOB_LOOKUP_CODE HEADER_FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.RATE_TYPE
, PH.RATE_DATE
, PH.RATE
, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUM
, PH.APPROVAL_REQUIRED_FLAG QUOTE_APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID ASSOCIATED_RFQ_ID
, PH.START_DATE EFFECTIVE_FROM
, PH.END_DATE EFFECTIVE_TO
, PH.BLANKET_TOTAL_AMOUNT AMOUNT_AGREED
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT HEADER_MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, TO_CHAR(PL.LINE_NUM) LINE_NUM
, TO_CHAR(PL.LINE_TYPE_ID) LINE_TYPE_ID
, PLT.LINE_TYPE
, TO_CHAR(PL.LINE_NUM) LINE_SHIP_CONCAT_NUM
, TO_CHAR(PL.ITEM_ID) ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE LINE_UOM
, PL.QUANTITY LINE_QUANTITY
, PL.UNIT_PRICE CURRENCY_LINE_PRICE
, NVL(PL.VENDOR_PRODUCT_NUM
, '-') VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED QUANTITY_AGREED
, PL.MIN_ORDER_QUANTITY QUOTE_MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY QUOTE_MAX_ORDER_QUANTITY
, GSB.CURRENCY_CODE FUNCTIONAL_CURRENCY
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5) FUNCTIONAL_LINE_PRICE
, TO_NUMBER(NULL) FUNCTIONAL_SHIPMENT_PRICE
, NVL(TO_NUMBER(NULL)
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5)) PRICE
, DCT.USER_CONVERSION_TYPE RATE_TYPE_NAME
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP
, PV.ATTRIBUTE14 SUPPLIER_URL
, NVL(PL.ATTRIBUTE14
, MSI.ATTRIBUTE14) SUPPLIER_ITEM_URL
, MSI.ATTRIBUTE14 ITEM_URL
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.ORGANIZATION_ID
FROM PO_DOCUMENT_TYPES PDT
, MTL_CATEGORIES_KFV MCK
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PER_PEOPLE_F PPF
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, PO_LINE_TYPES PLT
, PO_HEADERS PH
, PO_LINES PL
, MTL_SYSTEM_ITEMS_KFV MSI
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
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 PH.APPROVED_FLAG = 'Y'
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 MSI.ORGANIZATION_ID = DECODE(PL.ITEM_ID
, NULL
, NULL
, FSP.INVENTORY_ORGANIZATION_ID)
AND MCK.CATEGORY_ID = PL.CATEGORY_ID
AND PL.ITEM_ID IS NOT NULL
AND MSI.PURCHASING_ENABLED_FLAG = 'Y' UNION SELECT TO_CHAR(PH.PO_HEADER_ID) PO_HEADER_ID
, PH.VENDOR_ID
, NVL(PV.VENDOR_NAME
, '-') VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME
, PV.PARENT_VENDOR_ID
, PV.HOLD_FLAG
, PH.VENDOR_SITE_ID
, NVL(PVS.VENDOR_SITE_CODE
, '-') VENDOR_SITE_CODE
, PH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS) DOCUMENT_STATUS_CODE
, PLC.DISPLAYED_FIELD DOCUMENT_STATUS_DSP
, PH.SEGMENT1 DOCUMENT_NUM
, PH.AGENT_ID
, PPF.FULL_NAME AGENT_NAME
, PH.TERMS_ID HEADER_PAYMENT_TERMS_ID
, AT.NAME HEADER_PAYMENT_TERMS_NAME
, PH.SHIP_VIA_LOOKUP_CODE HEADER_SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE HEADER_FREIGHT_TERMS_CODE
, PH.FOB_LOOKUP_CODE HEADER_FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.RATE_TYPE
, PH.RATE_DATE
, PH.RATE
, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUM
, PH.APPROVAL_REQUIRED_FLAG QUOTE_APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID ASSOCIATED_RFQ_ID
, PH.START_DATE EFFECTIVE_FROM
, PH.END_DATE EFFECTIVE_TO
, PH.BLANKET_TOTAL_AMOUNT AMOUNT_AGREED
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT HEADER_MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, TO_CHAR(PL.LINE_NUM) LINE_NUM
, TO_CHAR(PL.LINE_TYPE_ID) LINE_TYPE_ID
, PLT.LINE_TYPE
, TO_CHAR(PL.LINE_NUM) LINE_SHIP_CONCAT_NUM
, TO_CHAR(PL.ITEM_ID) ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE LINE_UOM
, PL.QUANTITY LINE_QUANTITY
, PL.UNIT_PRICE CURRENCY_LINE_PRICE
, NVL(PL.VENDOR_PRODUCT_NUM
, '-') VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED QUANTITY_AGREED
, PL.MIN_ORDER_QUANTITY QUOTE_MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY QUOTE_MAX_ORDER_QUANTITY
, GSB.CURRENCY_CODE FUNCTIONAL_CURRENCY
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5) FUNCTIONAL_LINE_PRICE
, TO_NUMBER(NULL) FUNCTIONAL_SHIPMENT_PRICE
, NVL(TO_NUMBER(NULL)
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5)) PRICE
, DCT.USER_CONVERSION_TYPE RATE_TYPE_NAME
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP
, PV.ATTRIBUTE14 SUPPLIER_URL
, PL.ATTRIBUTE14 SUPPLIER_ITEM_URL
, NULL ITEM_URL
, NULL ITEM_NUMBER
, FSP.INVENTORY_ORGANIZATION_ID
FROM PO_DOCUMENT_TYPES PDT
, MTL_CATEGORIES_KFV MCK
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PER_PEOPLE_F PPF
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, PO_LINE_TYPES PLT
, PO_HEADERS PH
, PO_LINES PL
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
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 PH.APPROVED_FLAG = 'Y'
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 MCK.CATEGORY_ID = PL.CATEGORY_ID
AND PL.ITEM_ID IS NULL