VIEW: APPS.ISC_MSC_PURCHASING_BASE_V

Source

View Text - Preformatted

SELECT nvl(supp.new_order_quantity,0)*nvl(its.item_price,NVL(item.standard_cost,0)) purchasing_cost, supp.plan_id, supp.sr_instance_id, supp.organization_id, supp.inventory_item_id, NVL(to_char(supp.source_supplier_id), 'NA') SUPPLIER_ID, supp.new_schedule_date FROM msc_supplies supp, msc_system_items item, msc_item_suppliers its WHERE supp.plan_id = its.plan_id(+) AND supp.sr_instance_id = its.sr_instance_id(+) AND supp.organization_id = its.organization_id(+) AND supp.inventory_item_id = its.inventory_item_id(+) AND supp.order_type = 5 AND supp.source_supplier_id = its.supplier_id(+) AND supp.source_supplier_site_id = its.supplier_site_id(+) AND supp.plan_id = item.plan_id AND supp.sr_instance_id = item.sr_instance_id AND supp.organization_id = item.organization_id AND supp.inventory_item_id = item.inventory_item_id AND supp.source_organization_id IS NULL UNION ALL SELECT nvl(supp.new_order_quantity,0)*nvl(its.item_price,NVL(item.standard_cost,0)) purchasing_cost, supp.plan_id, supp.sr_instance_id, supp.organization_id, supp.inventory_item_id, NVL(to_char(supp.supplier_id), 'NA') SUPPLIER_ID, supp.new_schedule_date FROM msc_supplies supp, msc_system_items item, msc_item_suppliers its WHERE supp.plan_id = its.plan_id(+) AND supp.sr_instance_id = its.sr_instance_id(+) AND supp.organization_id = its.organization_id(+) AND supp.inventory_item_id = its.inventory_item_id(+) AND supp.order_type in (1,2,8) AND supp.supplier_id = its.supplier_id(+) AND supp.supplier_site_id = its.supplier_site_id(+) AND supp.plan_id = item.plan_id AND supp.sr_instance_id = item.sr_instance_id AND supp.organization_id = item.organization_id AND supp.inventory_item_id = item.inventory_item_id

View Text - HTML Formatted

SELECT NVL(SUPP.NEW_ORDER_QUANTITY
, 0)*NVL(ITS.ITEM_PRICE
, NVL(ITEM.STANDARD_COST
, 0)) PURCHASING_COST
, SUPP.PLAN_ID
, SUPP.SR_INSTANCE_ID
, SUPP.ORGANIZATION_ID
, SUPP.INVENTORY_ITEM_ID
, NVL(TO_CHAR(SUPP.SOURCE_SUPPLIER_ID)
, 'NA') SUPPLIER_ID
, SUPP.NEW_SCHEDULE_DATE
FROM MSC_SUPPLIES SUPP
, MSC_SYSTEM_ITEMS ITEM
, MSC_ITEM_SUPPLIERS ITS
WHERE SUPP.PLAN_ID = ITS.PLAN_ID(+)
AND SUPP.SR_INSTANCE_ID = ITS.SR_INSTANCE_ID(+)
AND SUPP.ORGANIZATION_ID = ITS.ORGANIZATION_ID(+)
AND SUPP.INVENTORY_ITEM_ID = ITS.INVENTORY_ITEM_ID(+)
AND SUPP.ORDER_TYPE = 5
AND SUPP.SOURCE_SUPPLIER_ID = ITS.SUPPLIER_ID(+)
AND SUPP.SOURCE_SUPPLIER_SITE_ID = ITS.SUPPLIER_SITE_ID(+)
AND SUPP.PLAN_ID = ITEM.PLAN_ID
AND SUPP.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND SUPP.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SUPP.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND SUPP.SOURCE_ORGANIZATION_ID IS NULL UNION ALL SELECT NVL(SUPP.NEW_ORDER_QUANTITY
, 0)*NVL(ITS.ITEM_PRICE
, NVL(ITEM.STANDARD_COST
, 0)) PURCHASING_COST
, SUPP.PLAN_ID
, SUPP.SR_INSTANCE_ID
, SUPP.ORGANIZATION_ID
, SUPP.INVENTORY_ITEM_ID
, NVL(TO_CHAR(SUPP.SUPPLIER_ID)
, 'NA') SUPPLIER_ID
, SUPP.NEW_SCHEDULE_DATE
FROM MSC_SUPPLIES SUPP
, MSC_SYSTEM_ITEMS ITEM
, MSC_ITEM_SUPPLIERS ITS
WHERE SUPP.PLAN_ID = ITS.PLAN_ID(+)
AND SUPP.SR_INSTANCE_ID = ITS.SR_INSTANCE_ID(+)
AND SUPP.ORGANIZATION_ID = ITS.ORGANIZATION_ID(+)
AND SUPP.INVENTORY_ITEM_ID = ITS.INVENTORY_ITEM_ID(+)
AND SUPP.ORDER_TYPE IN (1
, 2
, 8)
AND SUPP.SUPPLIER_ID = ITS.SUPPLIER_ID(+)
AND SUPP.SUPPLIER_SITE_ID = ITS.SUPPLIER_SITE_ID(+)
AND SUPP.PLAN_ID = ITEM.PLAN_ID
AND SUPP.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND SUPP.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SUPP.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID