[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 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, creation_date, created_by,
NUMBER1,NUMBER2,NUMBER3,
NUMBER4,NUMBER5,CHAR1,
CHAR2, CHAR3, CHAR4,
DATE1,DATE2,NUMBER6,
DATE3,NUMBER7,NUMBER8,
NUMBER9, NUMBER10, NUMBER11,
NUMBER12,NUMBER13,NUMBER14,
NUMBER15,NUMBER16,PROGRAM_ID,
PROGRAM_APPLICATION_ID, REQUEST_ID,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY,
CHAR7, CHAR5,
CHAR6, CHAR8,
CHAR9)
VALUES
(query_id, SYSDATE, SYSDATE, aRecord.record_type,
aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
aRecord.item_id, aRecord.sr_category_id, aRecord.category_name,
aRecord.plan_name, aRecord.org_name, aRecord.item_name,
aRecord.bkt_start_date,aRecord.bkt_end_date,aRecord.week_nr,
aRecord.period_start_date,aRecord.period_nr,aRecord.bucket_type,
aRecord.achieved_ss_qty,aRecord.achieved_ss_dollars,aRecord.achieved_ss_days,
aRecord.target_ss_qty,aRecord.target_ss_dollars,aRecord.target_ss_days,
aRecord.userdef_ss_qty,aRecord.userdef_ss_dollars,aRecord.userdef_ss_days,
aRecord.delivered_quantity,aRecord.required_quantity,
aRecord.target_service_level,aRecord.num_target_service_level,
aRecord.partner_id, aRecord.partner_name,
aRecord.customer_class_code, aRecord.num_safety_stock,
aRecord.last_week_of_period);
SELECT plan_id, instance_id, org_id,
item_id,
sr_category_id, category_name,
plan_name,
org_name,
item_name,
avg(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, cust.partner_id
FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp, msc_trading_partners cust,
msc_safety_stocks mss,
(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
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.inventory_item_id = NVL(p_item_id, msi.inventory_item_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.sr_instance_id = mss.sr_instance_id (+)
AND msi.plan_id = mss.plan_id (+)
AND msi.organization_id = mss.organization_id (+)
AND msi.inventory_item_id = mss.inventory_item_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 nvl(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
AND msd.customer_id = cust.partner_id (+)
AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX'))
AND (mss.safety_stock_quantity IS NOT NULL OR msd.demand_id IS NOT NULL))
GROUP BY plan_id, instance_id, org_id,
item_id,
sr_category_id, category_name,
plan_name,
org_name,
item_name;
SELECT plan_id, nvl(partner_id, UNDEFINED_CUSTOMER_ID), partner_name, nvl(customer_class_code, UNDEFINED_CUSTOMER_CODE),
avg(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,
cust.partner_id, cust.partner_name, cust.customer_class_code, msi.item_name AS item_name
FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp, msc_trading_partners cust,
(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
WHERE mp.plan_id = p_plan_id
AND mp.plan_id = msi.plan_id
AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_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.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 msd.customer_id = cust.partner_id (+)
AND nvl(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX')))
GROUP BY plan_id, partner_id, partner_name, customer_class_code;
SELECT msd.plan_id AS plan_id,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
msd.using_assembly_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,
mpo.organization_code AS org_name,
msi.item_name AS item_name,
trunc(msd.using_assembly_demand_date) AS week_start_date,
NULL AS week_next_date,
NULL AS week_nr,
NULL AS period_start_date,
NULL AS period_nr,
NULL AS bucket_type,
'N' AS last_week_of_period,
NULL AS achieved_ss_qty,
NULL AS achieved_ss_dollars,
NULL AS achieved_ss_days,
NULL AS target_ss_qty,
NULL AS target_ss_dollars,
NULL AS target_ss_days,
NULL AS userdef_ss_qty,
NULL AS userdef_ss_dollars,
NULL AS userdef_ss_days,
0 AS num_safety_stock,
nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1) AS delivered_quantity,
msd.using_requirement_quantity * nvl(msd.probability,1) AS required_quantity,
NULL AS target_service_level,
0 AS num_target_service_level,
msd.customer_id AS partner_id,
cust.partner_name AS partner_name,
cust.customer_class_code AS customer_class_code,
RECORD_SERVICE_LEVEL AS record_type
FROM msc_demands msd, msc_system_items msi,
msc_item_categories mic, msc_plans mp, msc_plan_organizations mpo,
msc_trading_partners cust
WHERE mp.plan_id = p_plan_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.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
AND msi.organization_id = mic.organization_id
AND msi.sr_instance_id = mic.sr_instance_id
AND mic.category_set_id = l_default_category_set_id
AND msi.inventory_item_id = mic.inventory_item_id
AND mp.plan_id = mpo.plan_id
AND msd.organization_id = mpo.organization_id
AND msd.sr_instance_id = mpo.sr_instance_id
AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_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(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX'))
AND msd.customer_id = cust.partner_id (+)
ORDER BY msd.plan_id,msd.sr_instance_id,msd.organization_id,msd.using_assembly_item_id,msd.customer_id;
SELECT mss.plan_id AS plan_id, mss.sr_instance_id AS instance_id, mss.organization_id AS org_id,
mss.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,
mpo.organization_code AS org_name,
msi.item_name,
mss.period_start_date AS week_start_date,
NULL AS week_next_date,
NULL AS week_nr, NULL AS period_start_date, NULL AS period_nr,
NULL AS bucket_type,
'N' AS last_week_of_period,
mss.safety_stock_quantity AS achieved_ss_qty,
mss.safety_stock_quantity*msi.standard_cost
AS achieved_ss_dollars,
mss.achieved_days_of_supply AS achieved_ss_days,
mss.target_safety_stock AS target_ss_qty,
mss.target_safety_stock * msi.standard_cost
AS target_ss_dollars,
mss.target_days_of_supply AS target_ss_days,
mss.user_defined_safety_stocks AS userdef_ss_qty,
mss.user_defined_safety_stocks * msi.standard_cost
AS userdef_ss_dollars,
mss.user_defined_dos AS userdef_ss_days,
0 AS num_safety_stock,
NULL AS delivered_quantity,
NULL AS required_quantity,
NULL AS target_service_level,
0 AS num_target_service_level,
NULL AS partner_id,
NULL AS partner_name,
NULL AS customer_class_code,
RECORD_SAFETY_STOCK AS record_type
FROM msc_safety_stocks mss, msc_system_items msi, msc_item_categories mic,
msc_plans mp, msc_plan_organizations mpo
WHERE mss.sr_instance_id = msi.sr_instance_id
AND mss.plan_id = msi.plan_id
AND mss.organization_id = msi.organization_id
AND mss.inventory_item_id = msi.inventory_item_id
AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
AND msi.sr_instance_id = mic.sr_instance_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND mic.category_set_id = l_default_category_set_id
AND mss.plan_id = p_plan_id
AND mss.plan_id = mp.plan_id
AND mp.plan_id = mpo.plan_id
AND mss.organization_id = mpo.organization_id
AND mss.sr_instance_id = mpo.sr_instance_id
AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_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)
ORDER BY 1,2,3,4;
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;