[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT service_level
INTO l_target_service_level
FROM msc_trading_partners
WHERE partner_type = 2
AND partner_id = customer_id;
select curr_plan_type
from msc_plans
where plan_id = v_plan_id;
SELECT pd.detail_date, mbp.START_DATE, mbp.end_date
FROM
(SELECT DISTINCT mbid.detail_date
FROM msc_bis_inv_detail mbid
WHERE mbid.plan_id = p_Plan_id
AND (mbid.period_type IS NULL OR mbid.period_type = 0)) pd,
msc_plans mp, msc_bis_periods mbp
WHERE mp.plan_id = p_plan_id
AND mp.sr_instance_id = mbp.sr_instance_id
AND mp.organization_id = mbp.organization_id
AND mbp.period_set_name = 'Accounting'
AND pd.detail_date = mbp.START_DATE
ORDER BY pd.detail_date;
SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
mpsd.period_start_date, mpb.bucket_type
FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
WHERE mp.plan_id = p_plan_id
AND mtp.sr_tp_id = mp.organization_id
AND mtp.sr_instance_id = mp.sr_instance_id
AND mtp.partner_type = 3
AND mp.plan_id = mpb.plan_id
AND mp.sr_instance_id = mpb.sr_instance_id
AND mp.organization_id = mpb.organization_id
AND mtp.sr_Instance_id = mpsd.sr_instance_id
AND mtp.calendar_code = mpsd.calendar_code
AND mpb.bucket_type in (2,3)
AND mpb.sr_instance_id = mpsd.sr_instance_id
AND mpsd.exception_set_id = mtp.calendar_exception_set_id
AND mpsd.calendar_code = mtp.calendar_code
AND (mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date)
ORDER BY mpb.bkt_start_date;
INSERT INTO msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
NUMBER1, NUMBER2, NUMBER3,
CHAR1, NUMBER4, CHAR2,
CHAR3, DATE1, DATE2,
NUMBER5, NUMBER6, NUMBER7,
NUMBER8, NUMBER9, NUMBER10,
NUMBER11, NUMBER12, NUMBER13,
NUMBER14,DATE3)
VALUES
(query_id, SYSDATE, -1, SYSDATE, -1,
aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
aRecord.org_name, aRecord.sr_category_id, aRecord.category_name,
aRecord.plan_name, aRecord.bkt_start_date, aRecord.bkt_end_date,
aRecord.period_nr, aRecord.achieved_budget_usd, aRecord.delivered_quantity,
aRecord.required_quantity, aRecord.target_sl, aRecord.num_target_sl,
aRecord.record_type, aRecord.week_nr, aRecord.bucket_type,
aRecord.detail_level,aRecord.period_start_date);
SELECT mbid.plan_id, mbid.sr_instance_id AS instance_id, mbid.organization_id as org_id, mtp.organization_code AS org_name,
mic.sr_category_id, mic.category_name, mp.compile_designator AS plan_name, mbid.detail_date as bkt_start_date,
NULL AS bkt_end_date, NULL AS period_nr,
NULL AS week_nr, NULL AS period_start_date, NULL AS bucket_type, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD) AS detail_level,
SUM(nvl(mbid.inventory_value, 0)) AS achieved_budget_usd, NULL as delivered_quantity, 0 as required_quantity,
NULL AS target_sl, NULL AS num_target_sl, RECORD_BUDGET AS record_type
FROM msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
msc_trading_partners mtp, msc_plans mp
WHERE mbid.plan_id = p_plan_id
AND mbid.plan_id = mp.plan_id
AND mbid.plan_id = mis.plan_id
AND mbid.organization_id = mis.organization_id
AND mbid.sr_instance_id = mis.sr_instance_id
AND mbid.inventory_item_id = mis.inventory_item_id
AND mbid.sr_instance_id = nvl(p_sr_instance_id, mbid.sr_instance_id)
AND mbid.organization_id = nvl(p_sr_tp_id, mbid.organization_id)
AND NVL(mbid.period_type, CALENDAR_TYPE_BIS) = p_calendar_type
AND mis.organization_id = mic.organization_id
AND mis.sr_instance_id = mic.sr_instance_id
AND mis.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = l_default_category_set_id
AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
AND mis.organization_id = mtp.sr_tp_id
AND mis.sr_instance_id = mtp.sr_instance_id
AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
AND mtp.partner_type = 3
AND nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
GROUP BY mbid.plan_id, mbid.sr_instance_id, mbid.organization_id, mtp.organization_code,
mic.sr_category_id, mic.category_name, mp.compile_designator, mbid.detail_date, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD)
ORDER BY mbid.plan_id, mbid.organization_id, mbid.sr_instance_id, mtp.organization_code,
mic.sr_category_id, mic.category_name, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD), mbid.detail_date;
SELECT msd.plan_id AS plan_id,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mtp.organization_code AS org_name,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
mp.compile_designator AS plan_name,
trunc(msd.using_assembly_demand_date) AS bkt_start_date,
NULL AS bkt_end_date,
NULL AS period_nr,
NULL AS week_nr, NULL AS period_start_date, NULL AS bucket_type, NULL AS detail_level,
0 AS achieved_budget_usd,
sum(nvl(msd.old_demand_quantity,0)*
nvl(msd.probability,1)) AS delivered_quantity,
sum(nvl(msd.using_requirement_quantity, 0)*
nvl(msd.probability,1)) AS required_quantity,
sum(nvl(msd.service_level, 50)) AS target_service_level,
count(*) AS num_target_service_level,
RECORD_SERVICE_LEVEL AS record_type
FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
msc_item_categories mic, msc_trading_partners mtp
WHERE mp.plan_id = p_plan_id
AND mp.plan_id = mpo.plan_id
AND mpo.plan_id = msd.plan_id
AND mpo.sr_instance_id = msd.sr_instance_id
AND mpo.organization_id = msd.organization_id
AND msd.plan_id = msi.plan_id
AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.using_assembly_item_id = msi.inventory_item_id
AND msd.plan_id = mp.plan_id
AND msi.organization_id = mic.organization_id
AND msi.sr_instance_id = mic.sr_instance_id
AND msi.budget_constrained = BUDGET_CONSTRAINED_ON
AND mic.category_set_id = l_default_category_set_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msd.organization_id = mtp.sr_tp_id
AND msd.sr_instance_id = mtp.sr_instance_id
AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
GROUP BY msd.plan_id, msd.sr_instance_id, msd.organization_id, mtp.organization_code,
mic.sr_category_id, mic.category_name, mp.compile_designator, trunc(msd.using_assembly_demand_date)
ORDER BY msd.plan_id, msd.organization_id, msd.sr_instance_id, mtp.organization_code,
mic.sr_category_id, mic.category_name, trunc(msd.using_assembly_demand_date);
SELECT plan_id, instance_id, org_id, sr_category_id, category_name, plan_name, org_name, SUM(target_sl) AS target_sl, COUNT(*) AS num_target_sl
FROM
(
SELECT plan_id, instance_id, org_id, sr_category_id, category_name, plan_name, org_name, item_id, item_name, AVG(target_sl) AS target_sl
FROM
(
SELECT plan_id, instance_id, org_id,
sr_category_id, category_name,
plan_name,
org_name, item_id, item_name,
nvl(get_customer_target_sl(partner_id) ,
msc_get_bis_values.service_target(plan_id,instance_id,org_id,item_id)) AS target_sl
FROM
(
SELECT DISTINCT msi.plan_id AS plan_id, msi.sr_instance_id AS instance_id, msi.organization_id AS org_id,
msi.inventory_item_id AS item_id,
mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
mp.compile_designator AS plan_name,
mtp.organization_code AS org_name,
msi.item_name AS item_name, msd.customer_id AS partner_id
FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp,
(SELECT demands.plan_id, demands.sr_instance_id, demands.organization_id, demands.using_assembly_item_id,
demands.customer_id, demands.demand_id
FROM msc_demands demands
WHERE demands.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)) msd,
(SELECT kpi.inventory_item_id, kpi.sr_instance_id, kpi.organization_id, kpi.plan_id
FROM msc_bis_inv_detail kpi
WHERE NVL(kpi.period_type, CALENDAR_TYPE_BIS) = p_calendar_type
AND kpi.plan_id = p_plan_id) mbid
WHERE mp.plan_id = p_plan_id
AND mp.plan_id = msi.plan_id
AND msi.organization_id = mic.organization_id
AND msi.sr_instance_id = mic.sr_instance_id
AND msi.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = l_default_category_set_id
AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
AND msi.organization_id = mtp.sr_tp_id
AND msi.sr_instance_id = mtp.sr_instance_id
AND mtp.partner_type = 3
AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
AND msi.inventory_item_id = mbid.inventory_item_id (+)
AND msi.organization_id = mbid.organization_id (+)
AND msi.sr_instance_id = mbid.sr_instance_id (+)
AND msi.inventory_item_id = mbid.inventory_item_id (+)
AND msi.plan_id = mbid.plan_id (+)
AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
AND msi.organization_id = nvl(p_sr_tp_id, msi.organization_id)
AND msi.plan_id = msd.plan_id (+)
AND msi.sr_instance_id = msd.sr_instance_id (+)
AND msi.organization_id = msd.organization_id (+)
AND msi.inventory_item_id = msd.using_assembly_item_id (+)
AND msi.budget_constrained = BUDGET_CONSTRAINED_ON
AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
AND (mbid.plan_id IS NOT NULL OR msd.demand_id IS NOT NULL)
))
GROUP BY plan_id, instance_id, org_id,
sr_category_id, category_name,
plan_name,
org_name, item_id, item_name)
GROUP BY plan_id, instance_id, org_id,
sr_category_id, category_name,
plan_name,
org_name;
SELECT 1
FROM msc_plans_tree_v mpt, msc_plans mp
WHERE mpt.curr_plan_type = 4
AND mpt.plan_id = mp.plan_id
AND mp.plan_start_date IS NOT NULL
AND mp.plan_id = p_plan_id;
SELECT msc_form_query_s.nextval
INTO query_id
FROM dual;