eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ICX_POR_SEARCH_CONTRACTS_V

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'