MSC_AP_PO_SUPPLIERS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ITEM_NAME MINIMUM_ORDER_QUANTITY DISABLE_FLAG FIXED_LOT_MULTIPLIER PURCHASING_UNIT_OF_MEASURE PARTNER_NAME PARTY_SITE_CODE ITEM_PRICE PRIMARY_VENDOR_ITEM LAST_UPDATE_DATE CURRENCY_CODE

Name

ITEM_NAME

MINIMUM_ORDER_QUANTITY

DISABLE_FLAG

FIXED_LOT_MULTIPLIER

PURCHASING_UNIT_OF_MEASURE

PARTNER_NAME

PARTY_SITE_CODE

ITEM_PRICE

PRIMARY_VENDOR_ITEM

LAST_UPDATE_DATE

CURRENCY_CODE

Query

SQL_Statement
WITH bpa as ( SELECT ROW_NUMBER() OVER(PARTITION BY pad.asl_id ORDER BY pll.item_revision DESC NULLS FIRST, NVL(pll.unit_price, 0) DESC ) rownmbr, pll.item_id, pad.asl_id, pll.uom_code, pll.unit_price, pha.currency_code FROM po_lines_all pll, po_asl_documents pad, po_headers_all pha WHERE pll.po_line_id = pad.document_line_id AND pll.po_header_id = pad.document_header_id AND pll.po_header_id = pha.po_header_id AND ( pll.cancel_date IS NULL OR pll.cancel_date >= sysdate ) AND ( pll.closed_date IS NULL OR pll.closed_date >= sysdate ) AND ( pll.expiration_date IS NULL OR pll.expiration_date >= sysdate ) AND pad.using_organization_id = - 1 AND pad.document_type_code = 'BLANKET' ) SELECT (SELECT itmv.ITEM_NUMBER FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V itmv WHERE itmv.INVENTORY_ITEM_ID = PASL.ITEM_ID AND nvl(itmv.OUTSIDE_PROCESS_SERVICE_FLAG,2) <> 1 AND rownum < 2 ) ITEM_NAME, PAA.MIN_ORDER_QTY MINIMUM_ORDER_QUANTITY, DECODE(PASL.DISABLE_FLAG,'Y',1,2) DISABLE_FLAG, PAA.FIXED_LOT_MULTIPLE FIXED_LOT_MULTIPLIER, NVL(bpa.uom_code, paa.uom_code) PURCHASING_UNIT_OF_MEASURE, sup.partner_name, supsite.vendor_site_code party_site_code, nvl(bpa.unit_price,0) ITEM_PRICE, PASL.PRIMARY_VENDOR_ITEM, greatest(pasl.last_update_date,paa.last_update_date) last_update_date, bpa.currency_code FROM PO_APPROVED_SUPPLIER_LIST PASL, bpa, PO_ASL_ATTRIBUTES PAA, MSC_AP_SUPPLIERS_V sup, MSC_AP_SUPPLIER_SITES_V supsite WHERE PASL.USING_ORGANIZATION_ID = -1 AND PAA.using_organization_id = PASL.USING_ORGANIZATION_ID AND PAA.ASL_ID = PASL.ASL_ID AND pasl.item_id = bpa.item_id(+) AND pasl.asl_id = bpa.asl_id(+) AND bpa.rownmbr(+) = 1 AND NOT EXISTS ( SELECT 1 FROM PO_APPROVED_SUPPLIER_LIST ASL, PO_ASL_STATUSES ASR, PO_LOOKUP_CODES PLC WHERE ASL.USING_ORGANIZATION_ID = -1 AND ASL.VENDOR_ID = PASL.VENDOR_ID AND NVL(ASL.VENDOR_SITE_ID, NVL(PASL.VENDOR_SITE_ID, -1)) = NVL(PASL.VENDOR_SITE_ID, -1) AND ASL.ITEM_ID = PASL.ITEM_ID AND ASL.ASL_STATUS_ID = ASR.STATUS_ID AND PLC.LOOKUP_TYPE = 'ASL_STATUS_BUSINESS_RULES' AND ASR.ALLOW_SOURCING_FLAG = 'N' ) AND sup.sr_tp_id(+) = PAA.VENDOR_ID AND supsite.sr_tp_id(+) = PAA.VENDOR_ID AND supsite.sr_tp_site_id(+) = PAA.VENDOR_SITE_ID

SQL_Statement

WITH bpa as (

SELECT

ROW_NUMBER() OVER(PARTITION BY pad.asl_id

ORDER BY pll.item_revision DESC NULLS FIRST, NVL(pll.unit_price, 0) DESC

) rownmbr,

pll.item_id,

pad.asl_id,

pll.uom_code,

pll.unit_price,

pha.currency_code

FROM

po_lines_all pll,

po_asl_documents pad,

po_headers_all pha

WHERE pll.po_line_id = pad.document_line_id

AND pll.po_header_id = pad.document_header_id

AND pll.po_header_id = pha.po_header_id

AND ( pll.cancel_date IS NULL OR pll.cancel_date >= sysdate )

AND ( pll.closed_date IS NULL OR pll.closed_date >= sysdate )

AND ( pll.expiration_date IS NULL OR pll.expiration_date >= sysdate )

AND pad.using_organization_id = - 1

AND pad.document_type_code = 'BLANKET'

)

SELECT

(SELECT itmv.ITEM_NUMBER

FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V itmv

WHERE itmv.INVENTORY_ITEM_ID = PASL.ITEM_ID

AND nvl(itmv.OUTSIDE_PROCESS_SERVICE_FLAG,2) <> 1

AND rownum < 2

) ITEM_NAME,

PAA.MIN_ORDER_QTY MINIMUM_ORDER_QUANTITY,

DECODE(PASL.DISABLE_FLAG,'Y',1,2) DISABLE_FLAG,

PAA.FIXED_LOT_MULTIPLE FIXED_LOT_MULTIPLIER,

NVL(bpa.uom_code, paa.uom_code) PURCHASING_UNIT_OF_MEASURE,

sup.partner_name,

supsite.vendor_site_code party_site_code,

nvl(bpa.unit_price,0) ITEM_PRICE,

PASL.PRIMARY_VENDOR_ITEM,

greatest(pasl.last_update_date,paa.last_update_date) last_update_date,

bpa.currency_code

FROM PO_APPROVED_SUPPLIER_LIST PASL,

bpa,

PO_ASL_ATTRIBUTES PAA,

MSC_AP_SUPPLIERS_V sup,

MSC_AP_SUPPLIER_SITES_V supsite

WHERE PASL.USING_ORGANIZATION_ID = -1

AND PAA.using_organization_id = PASL.USING_ORGANIZATION_ID

AND PAA.ASL_ID = PASL.ASL_ID

AND pasl.item_id = bpa.item_id(+)

AND pasl.asl_id = bpa.asl_id(+)

AND bpa.rownmbr(+) = 1

AND NOT EXISTS (

SELECT 1

FROM PO_APPROVED_SUPPLIER_LIST ASL,

PO_ASL_STATUSES ASR,

PO_LOOKUP_CODES PLC

WHERE ASL.USING_ORGANIZATION_ID = -1

AND ASL.VENDOR_ID = PASL.VENDOR_ID

AND NVL(ASL.VENDOR_SITE_ID, NVL(PASL.VENDOR_SITE_ID, -1)) = NVL(PASL.VENDOR_SITE_ID, -1)

AND ASL.ITEM_ID = PASL.ITEM_ID

AND ASL.ASL_STATUS_ID = ASR.STATUS_ID

AND PLC.LOOKUP_TYPE = 'ASL_STATUS_BUSINESS_RULES'

AND ASR.ALLOW_SOURCING_FLAG = 'N'

)

AND sup.sr_tp_id(+) = PAA.VENDOR_ID

AND supsite.sr_tp_id(+) = PAA.VENDOR_ID

AND supsite.sr_tp_site_id(+) = PAA.VENDOR_SITE_ID