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

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

Query

SQL_Statement
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, PAA.uom_code PURCHASING_UNIT_OF_MEASURE, sup.partner_name, supsite.vendor_site_code party_site_code, (SELECT NVL(MAX(NVL(pll.unit_price,0)),0) FROM po_lines_all pll, po_asl_documents pad WHERE pll.po_line_id = pad.DOCUMENT_LINE_ID AND pll.po_header_id = pad.DOCUMENT_HEADER_ID AND pll.item_id = PASL.ITEM_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.asl_id = PASL.ASL_ID AND pad.using_organization_id = -1 AND pad.DOCUMENT_TYPE_CODE = 'BLANKET' ) ITEM_PRICE, PASL.PRIMARY_VENDOR_ITEM, greatest(pasl.last_update_date,paa.last_update_date) last_update_date FROM PO_APPROVED_SUPPLIER_LIST PASL, 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 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

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,

PAA.uom_code PURCHASING_UNIT_OF_MEASURE,

sup.partner_name,

supsite.vendor_site_code party_site_code,

(SELECT NVL(MAX(NVL(pll.unit_price,0)),0)

FROM po_lines_all pll,

po_asl_documents pad

WHERE pll.po_line_id = pad.DOCUMENT_LINE_ID

AND pll.po_header_id = pad.DOCUMENT_HEADER_ID

AND pll.item_id = PASL.ITEM_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.asl_id = PASL.ASL_ID

AND pad.using_organization_id = -1

AND pad.DOCUMENT_TYPE_CODE = 'BLANKET'

) ITEM_PRICE,

PASL.PRIMARY_VENDOR_ITEM,

greatest(pasl.last_update_date,paa.last_update_date) last_update_date

FROM PO_APPROVED_SUPPLIER_LIST PASL,

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 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