The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
msi.plan_id
, msi.inventory_item_id
, msi.organization_id
, msi.sr_instance_id
, mtp.modeled_customer_id
, mtp.modeled_customer_site_id
, NVL(msi.forecast_horizon, 0) forecast_horizon
, msi.vmi_forecast_type
, msi.uom_code
, mvt.average_daily_demand
FROM msc_system_items msi
, msc_trading_partners mtp
, msc_vmi_temp mvt
WHERE msi.inventory_planning_code = 7
AND msi.organization_id = mtp.sr_tp_id
AND msi.sr_instance_id = mtp.sr_instance_id
AND mtp.partner_type = 3 -- org
AND mtp.modeled_customer_id IS NOT NULL
AND mtp.modeled_customer_site_id IS NOT NULL
AND msi.plan_id = -1
and mvt.plan_id (+) = msi.plan_id
and mvt.inventory_item_id (+) = msi.inventory_item_id
and mvt.organization_id (+) = msi.organization_id
and mvt.sr_instance_id (+) = msi.sr_instance_id
and mvt.vmi_type (+) = 2 -- customer facing vmi
;
SELECT distinct SUM(sd.primary_quantity) total_demand,
sd.primary_uom
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = p_plan_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.customer_id = p_customer_id
AND sd.customer_site_id = p_customer_site_id
AND TRUNC(key_date) BETWEEN TRUNC(p_horizon_start_date)
AND TRUNC(p_horizon_end_date)
AND publisher_order_type = l_forecast_type
AND sd.supplier_id = 1
AND p_forecast_horizon > 0
GROUP BY
sd.primary_uom
;
UPDATE msc_system_items
SET -- average_daily_demand = l_average_daily_demand
vmi_refresh_flag = NVL(l_vmi_refresh_flag, vmi_refresh_flag)
WHERE plan_id = p_plan_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND sr_instance_id = p_sr_instance_id
;
print_debug_info( ' vmi refresh flag updated, number of rows updated = '
|| SQL%ROWCOUNT
);
UPDATE msc_vmi_temp
SET average_daily_demand = l_average_daily_demand
WHERE plan_id = p_plan_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND sr_instance_id = p_sr_instance_id
AND vmi_type = 2 -- customer facing vmi
;
print_debug_info( ' average daily demand updated, number of rows updated = '
|| SQL%ROWCOUNT
);
INSERT INTO msc_vmi_temp
( PLAN_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
SUPPLIER_ID ,
SUPPLIER_SITE_ID ,
USING_ORGANIZATION_ID ,
VMI_TYPE ,
AVERAGE_DAILY_DEMAND
) VALUES
( p_PLAN_ID,
p_INVENTORY_ITEM_ID,
p_ORGANIZATION_ID ,
p_SR_INSTANCE_ID ,
NULL, -- p_SUPPLIER_ID ,
NULL, -- p_SUPPLIER_SITE_ID ,
NULL, -- p_USING_ORGANIZATION_ID ,
2 ,
l_AVERAGE_DAILY_DEMAND
);
print_debug_info( ' average daily demand inserted, number of rows inserted = '
|| SQL%ROWCOUNT
);
SELECT cr.object_id
FROM msc_trading_partner_maps map
, msc_company_relationships cr
WHERE map.map_type = p_map_type
AND map.tp_key = p_tp_key
AND map.company_key = cr.relationship_id
AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
;
SELECT map.company_key
FROM msc_trading_partner_maps map
, msc_trading_partners tp
WHERE map.map_type = p_map_type
AND tp.partner_id = map.tp_key
AND tp.sr_tp_id = p_tp_key
AND tp.sr_instance_id = p_sr_instance_id
;
SELECT map.company_key
FROM msc_trading_partner_maps map
WHERE map.map_type = p_map_type
AND map.tp_key = p_tp_key
;
SELECT map.tp_key
FROM msc_trading_partner_maps map
, msc_company_relationships cr
WHERE map.map_type = p_map_type
AND cr.object_id = p_company_key
AND map.company_key = cr.relationship_id
AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
AND cr.subject_id = OEM_COMPANY_ID
;
SELECT tp.sr_tp_id
FROM msc_trading_partner_maps map
, msc_trading_partners tp
WHERE map.map_type = p_map_type
AND tp.partner_id = map.tp_key
AND map.company_key= p_company_key
;
SELECT map.tp_key
FROM msc_trading_partner_maps map
WHERE map.map_type = p_map_type
AND map.company_key = p_company_key
;