[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select plan_type
from msc_plans
where plan_id = p_plan_id;
SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
mpsd.period_start_date
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 = 2
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;
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_analysis_aggregate
(
plan_id,
record_type,
safety_stock_qty,
safety_stock_dollars,
safety_stock_dos,
target_safety_stock_qty,
target_safety_stock_dollars,
target_safety_stock_dos,
userdef_safety_stock_qty,
userdef_safety_stock_dollars,
userdef_safety_stock_dos,
total_unpooled_safety_stock,
demand_var_ss_percent,
mfg_ltvar_ss_percent,
transit_ltvar_ss_percent,
sup_ltvar_ss_percent,
achieved_service_level,
target_service_level,
inventory_value_dollars,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(
aRecord.plan_id,
aRecord.record_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.total_unpooled_safety_stock,
aRecord.demand_var_ss_percent,
aRecord.mfg_ltvar_ss_percent,
aRecord.transit_ltvar_ss_percent,
aRecord.sup_ltvar_ss_percent,
l_achieved_service_level,
l_target_service_level,
NULL,
CALENDAR_TYPE_MFG,
l_week_start_date,
aRecord.period_start_date,
aRecord.instance_id,
aRecord.org_id,
aRecord.sr_category_inst_id,
aRecord.sr_category_id,
aRecord.category_name,
aRecord.item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
);
INSERT INTO msc_form_query
(query_id, last_update_date, last_updated_by,creation_date, created_by,
NUMBER1,NUMBER2,NUMBER3,
NUMBER4,NUMBER5,CHAR1,
DATE1,DATE2,DATE3,
NUMBER9, NUMBER10, NUMBER11,
NUMBER12,NUMBER13,NUMBER14,
NUMBER15,NUMBER16,PROGRAM_ID,
PROGRAM_APPLICATION_ID, REQUEST_ID,
LAST_UPDATE_LOGIN, NUMBER7,
CHAR7, CHAR6, char11, char12, char13, char14, char15)
VALUES
(query_id, SYSDATE, -1, SYSDATE, aRecord.record_type,
aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
aRecord.item_id, aRecord.sr_category_id, aRecord.category_name,
l_bkt_start_date,l_bkt_end_date, aRecord.period_start_date,
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,
l_achieved_service_level, aRecord.sr_category_inst_id,
aRecord.target_service_level, aRecord.period_type,
aRecord.partner_id, aRecord.customer_class_code,
aRecord.total_unpooled_safety_stock, aRecord.demand_var_ss_percent,
aRecord.mfg_ltvar_ss_percent, aRecord.transit_ltvar_ss_percent, aRecord.sup_ltvar_ss_percent);
SELECT msd.plan_id AS plan_id,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
msd.inventory_item_id AS item_id,
mic.sr_instance_id AS sr_category_inst_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
trunc(msd.using_assembly_demand_date) AS week_start_date,
NULL AS week_next_date,
NULL AS period_start_date,
'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 nr_ss_records,
NULL AS inv_value_dollars,
CALENDAR_TYPE_MFG AS period_type,
NULL AS total_unpooled_safety_stock,
NULL AS demand_var_ss_percent,
NULL AS mfg_ltvar_ss_percent,
NULL AS transit_ltvar_ss_percent,
NULL AS sup_ltvar_ss_percent,
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 nr_sl_records,
decode(GROUPING(msd.customer_id), 1, NULL, nvl(msd.customer_id, UNDEFINED_CUSTOMER_ID)) AS partner_id,
decode(GROUPING(cust.customer_class_code), 1, NULL, NVL(cust.customer_class_code, UNDEFINED_CUSTOMER_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 trunc(msd.using_assembly_demand_date) between l_start_date AND l_end_date
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.inventory_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 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 mic.category_set_id = l_default_category_set_id
AND msi.inventory_item_id = mic.inventory_item_id
AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
AND mp.plan_id = mpo.plan_id
AND msd.organization_id = mpo.organization_id
AND msd.sr_instance_id = mpo.sr_instance_id
AND msd.customer_id = cust.partner_id (+)
GROUP BY msd.plan_id, mp.compile_designator, trunc(msd.using_assembly_demand_date) ,
CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name,
msd.customer_id, cust.customer_class_code)
HAVING (
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0
)
ORDER BY 1,2,3,4, 5, 6, 28, 29, 8;
SELECT curr_start_date, curr_cutoff_date
FROM msc_plans
WHERE plan_id = p_plan_id;
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL AS record_type,
sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_MFG AS period_type,
mpb.bkt_start_date AS week_start_date,
mpsd.period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_plan_buckets mpb,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners owning,
msc_period_start_dates mpsd
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.inventory_item_id = msi.inventory_item_id
AND msd.plan_id = mp.plan_id
AND mp.plan_id = mpb.plan_id
AND mpb.bucket_type IN (2,3)
AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
AND mp.sr_instance_id = owning.sr_instance_id
AND mp.organization_id = owning.sr_tp_id
AND owning.partner_type = 3
AND owning.calendar_code = mpsd.calendar_code
AND exception_set_id = mpsd.exception_set_id
AND mpb.sr_instance_id = mpsd.sr_instance_id
AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
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
GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type,
CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
(
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0
)
AND (mpb.bucket_type <> 3 OR GROUPING(mpb.bkt_start_date) <> 0)
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL AS record_type,
sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
msbp.START_DATE AS period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_system_items msi,
msc_item_categories mic,
msc_bis_periods msbp
WHERE mp.plan_id = p_plan_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 msd.plan_id = mpo.plan_id
AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
AND msd.plan_id = msi.plan_id
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.inventory_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 mp.sr_instance_id = msbp.sr_instance_id
AND mp.organization_id = msbp.organization_id
AND msbp.period_set_name = g_period_setname
AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
GROUP BY msd.plan_id, msbp.start_date,
CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL AS record_type,
sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
sum(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_MFG AS period_type,
mpb.bkt_start_date AS week_start_date,
mpsd.period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_plan_buckets mpb,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners owning,
msc_period_start_dates mpsd
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.inventory_item_id = msi.inventory_item_id
AND msd.plan_id = mp.plan_id
AND mp.plan_id = mpb.plan_id
AND mpb.bucket_type IN (2,3)
AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
AND mp.sr_instance_id = owning.sr_instance_id
AND mp.organization_id = owning.sr_tp_id
AND owning.partner_type = 3
AND owning.calendar_code = mpsd.calendar_code
AND exception_set_id = mpsd.exception_set_id
AND mpb.sr_instance_id = mpsd.sr_instance_id
AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
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
GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type,
CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
(
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1
AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0
)
AND (mpb.bucket_type <> 3 OR GROUPING(mpb.bkt_start_date) <> 0)
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL AS record_type,
sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
sum(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
msbp.START_DATE AS period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_system_items msi,
msc_item_categories mic,
msc_bis_periods msbp
WHERE mp.plan_id = p_plan_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 msd.plan_id = mpo.plan_id
AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
AND msd.plan_id = msi.plan_id
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.inventory_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 mp.sr_instance_id = msbp.sr_instance_id
AND mp.organization_id = msbp.organization_id
AND msbp.period_set_name = g_period_setname
AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
GROUP BY msd.plan_id, msbp.start_date,
CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
demand_class,
category_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_MFG AS period_type,
mpb.bkt_start_date AS week_start_date,
mpsd.period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
msd.demand_class as demand_class,
mic.category_set_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_plan_buckets mpb,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners owning,
msc_period_start_dates mpsd
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.inventory_item_id = msi.inventory_item_id
AND msd.plan_id = mp.plan_id
AND mp.plan_id = mpb.plan_id
AND mpb.bucket_type IN (2,3)
AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
AND mp.sr_instance_id = owning.sr_instance_id
AND mp.organization_id = owning.sr_tp_id
AND owning.partner_type = 3
AND owning.calendar_code = mpsd.calendar_code
AND exception_set_id = mpsd.exception_set_id
AND mpb.sr_instance_id = mpsd.sr_instance_id
AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
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
GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type, mic.category_set_id,
CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name,
msd.demand_class)
HAVING (
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) =0
AND ( GROUPING(msd.demand_class) = 0)
-- AND ( GROUPING(mpb.bkt_start_date) = 0)
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
demand_class,
category_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_MFG AS period_type,
mpb.bkt_start_date AS week_start_date,
null as period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
msd.demand_class as demand_class,
mic.category_set_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_plan_buckets mpb,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners owning
--msc_period_start_dates mpsd
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.inventory_item_id = msi.inventory_item_id
AND msd.plan_id = mp.plan_id
AND mp.plan_id = mpb.plan_id
AND mpb.bucket_type IN (2)
AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
AND mp.sr_instance_id = owning.sr_instance_id
AND mp.organization_id = owning.sr_tp_id
AND owning.partner_type = 3
-- AND owning.calendar_code = mpsd.calendar_code
--AND exception_set_id = mpsd.exception_set_id
--AND mpb.sr_instance_id = mpsd.sr_instance_id
--AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
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 mpb.bkt_start_date between mp.curr_start_date and mp.curr_cutoff_date
GROUP BY msd.plan_id, mpb.bkt_start_date, mpb.bucket_type, mic.category_set_id,
msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class;
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
demand_class,
category_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
SUM(NVL(msd.old_demand_quantity,0) * NVL(msd.probability,1)) * 100 AS achieved_service_level_qty1,
SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * NVL(msd.probability,1)) AS achieved_service_level_qty2,
DECODE(COUNT(*), 0, 50, SUM(NVL(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_MFG AS period_type,
NULL AS week_start_date,
mpsd.period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
msd.demand_class AS demand_class,
mic.category_set_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_trading_partners owning,
msc_period_start_dates mpsd,
msc_demands msd,
msc_system_items msi,
msc_item_categories mic
WHERE mp.plan_id = p_plan_id
AND mp.sr_instance_id = owning.sr_instance_id
AND mp.organization_id = owning.sr_tp_id
AND owning.partner_type = 3
AND owning.calendar_code = mpsd.calendar_code
-- AND exception_set_id = exception_set_id
AND owning.sr_instance_id = mpsd.sr_instance_id
AND mpsd.next_date > mp.curr_start_date
AND mpsd.period_start_date <= mp.curr_cutoff_date
AND msi.plan_id = mp.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 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.inventory_item_id = msi.inventory_item_id
AND TRUNC(msd.using_assembly_demand_date) BETWEEN mpsd.period_start_date AND mpsd.next_date
GROUP BY msd.plan_id,mpsd.period_start_date, mic.category_set_id,
msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class;
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
achieved_service_level_qty1,
achieved_service_level_qty2,
target_service_level,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
demand_class,
category_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
msd.plan_id,
RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
msbp.START_DATE AS period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
msd.inventory_item_id AS item_id,
msd.demand_class as demand_class,
mic.category_set_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_demands msd,
msc_system_items msi,
msc_item_categories mic,
msc_bis_periods msbp
WHERE mp.plan_id = p_plan_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 msd.plan_id = mpo.plan_id
AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
AND msd.plan_id = msi.plan_id
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.inventory_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 mp.sr_instance_id = msbp.sr_instance_id
AND mp.organization_id = msbp.organization_id
AND msbp.period_set_name = g_period_setname
AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
GROUP BY msd.plan_id, msbp.start_date,mic.category_set_id,
CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class)
HAVING (
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
AND GROUPING(msd.inventory_item_id) =0
AND GROUPING(msd.demand_class) = 0
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
plan_name,
record_type,
planned_production_cost,
planned_carrying_cost,
planned_purchasing_cost,
planned_tp_cost,
planned_total_cost,
planned_revenue,
period_type,
detail_level,
category_set_id,
category_id,
category_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
plan_id,
plan_name,
RECORD_COST_BRKDOWN AS record_type,
sum(planned_production_cost),
sum(planned_carrying_cost),
sum(planned_purchasing_cost),
0,
sum(planned_total_cost),
sum(planned_revenue),
CALENDAR_TYPE_MFG AS period_type,
detail_level,
category_set_id,
category_id,
category_name,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_cost_breakdown_notpcost_v
WHERE plan_id = p_plan_id
and nvl(detail_level,0) = 0
and nvl(period_type,0) = 1
GROUP BY plan_id, plan_name, period_type,period_type, detail_level,category_set_id, category_id,category_name;
INSERT INTO msc_analysis_aggregate
(
plan_id,
plan_name,
record_type,
planned_production_cost,
planned_carrying_cost,
planned_purchasing_cost,
planned_tp_cost,
planned_total_cost,
planned_revenue,
period_type,
detail_level,
category_set_id,
category_id,
category_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
plan_id,
plan_name,
RECORD_COST_BRKDOWN AS record_type,
sum(planned_production_cost),
sum(planned_carrying_cost),
sum(planned_purchasing_cost),
0,
sum(planned_total_cost),
sum(planned_revenue),
CALENDAR_TYPE_BIS AS period_type,
detail_level,
category_set_id,
category_id,
category_name,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_cost_breakdown_notpcost_v
WHERE plan_id = p_plan_id
and nvl(detail_level,0) = 0
and nvl(period_type,0) = 0
GROUP BY plan_id, plan_name, period_type,period_type, detail_level,category_set_id,category_id,category_name;
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
inventory_value_dollars,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
mp.plan_id,
RECORD_INVENTORY_VALUE AS record_type,
SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
CALENDAR_TYPE_MFG AS period_type,
decode(NVL(mbid.detail_level, 0), 0, NULL, mbid.detail_date) AS week_start_date,
mpsd.period_start_date,
mpo.sr_instance_id AS instance_id,
mpo.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
NULL AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_bis_inv_detail mbid,
msc_system_items mis,
msc_item_categories mic,
msc_trading_partners mtp,
msc_period_start_dates mpsd,
msc_plan_buckets mpb
WHERE mp.plan_id = p_plan_id
AND mp.plan_id = mpo.plan_id
AND mpo.plan_id = mbid.plan_id
AND mpo.organization_id = mbid.organization_id
AND mpo.sr_instance_id = mbid.sr_instance_id
AND mbid.period_type = CALENDAR_TYPE_MFG
-- BEGIN FUNNY SECTION
-- For some strange reason MSC_BIS_INV_DETAIL has week records for period buckets
-- So we need to filter thsese out before we aggregate
AND mpb.plan_id = mbid.plan_id
AND (mpb.bucket_type = 2 OR nvl(detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD)
AND mbid.detail_date BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
-- END FUNNY SECTION
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 mis.budget_constrained = BUDGET_CONSTRAINED_ON
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 mtp.sr_tp_id = mp.organization_id
AND mtp.sr_instance_id = mp.sr_instance_id
AND mtp.partner_type = 3
AND mtp.sr_Instance_id = mpsd.sr_instance_id
AND mtp.calendar_code = mpsd.calendar_code
AND mtp.calendar_exception_set_id = mpsd.exception_set_id
AND ((nvl(mbid.detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD AND mbid.detail_date = mpsd.period_start_date) OR
(mbid.detail_level = DETAIL_LEVEL_WEEK AND mbid.detail_date >= mpsd.period_start_date AND mbid.detail_date < mpsd.next_date))
GROUP BY mp.plan_id, mbid.period_type, mpsd.period_start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
);
INSERT INTO msc_analysis_aggregate
(
plan_id,
record_type,
inventory_value_dollars,
period_type,
week_start_date,
period_start_date,
sr_instance_id,
organization_id,
sr_cat_instance_id,
sr_category_id,
category_name,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
SELECT
mp.plan_id,
RECORD_INVENTORY_VALUE AS record_type,
SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
mpsd.start_date,
mpo.sr_instance_id AS instance_id,
mpo.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
NULL AS item_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
to_number(null)
FROM msc_plans mp,
msc_plan_organizations mpo,
msc_bis_inv_detail mbid,
msc_system_items mis,
msc_item_categories mic,
msc_bis_periods mpsd
WHERE mp.plan_id = p_plan_id
AND mp.plan_id = mpo.plan_id
AND mpo.plan_id = mbid.plan_id
AND mpo.organization_id = mbid.organization_id
AND mpo.sr_instance_id = mbid.sr_instance_id
AND nvl(mbid.period_type, CALENDAR_TYPE_BIS) = CALENDAR_TYPE_BIS
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 mis.budget_constrained = BUDGET_CONSTRAINED_ON
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 mp.sr_instance_id = mpsd.sr_instance_id
AND mp.organization_id = mpsd.organization_id
AND mpsd.period_set_name = g_period_setname
AND mbid.detail_date = mpsd.START_DATE
GROUP BY mp.plan_id, mbid.period_type, mpsd.start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
);
SELECT mss.plan_id AS plan_id,
mss.sr_instance_id AS instance_id,
mss.organization_id AS org_id,
msi.inventory_item_id AS item_id,
mic.sr_instance_id AS sr_category_inst_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
trunc(mss.period_start_date) AS week_start_date,
NULL AS week_next_date,
NULL AS period_start_date,
'N' AS last_week_of_period,
sum(nvl(mss.safety_stock_quantity, 0)) AS achieved_ss_qty,
sum(nvl(mss.safety_stock_quantity*msi.standard_cost, 0)) AS achieved_ss_dollars,
sum(nvl(mss.achieved_days_of_supply, 0)) AS achieved_ss_days,
sum(nvl(mss.target_safety_stock, 0)) AS target_ss_qty,
sum(nvl(mss.target_safety_stock * msi.standard_cost, 0)) AS target_ss_dollars,
sum(nvl(mss.target_days_of_supply, 0)) AS target_ss_days,
sum(nvl(mss.user_defined_safety_stocks, 0)) AS userdef_ss_qty,
sum(nvl(mss.user_defined_safety_stocks * msi.standard_cost, 0)) AS userdef_ss_dollars,
sum(nvl(mss.user_defined_dos, 0)) AS userdef_ss_days,
0 AS nr_ss_records,
NULL AS inv_value_dollars,
NULL AS period_type,
sum(nvl(mss.total_unpooled_safety_stock, 0)) AS total_unpooled_safety_stock,
decode( sum(mss.total_unpooled_safety_stock), 0, 0,
sum((mss.demand_var_ss_percent *mss.total_unpooled_safety_stock)/100)
/ sum(mss.total_unpooled_safety_stock))*100 AS demand_var_ss_percent,
decode( sum(mss.total_unpooled_safety_stock), 0, 0,
sum((mss.mfg_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
/ sum(mss.total_unpooled_safety_stock))*100 AS mfg_ltvar_ss_percent,
decode( sum(mss.total_unpooled_safety_stock), 0, 0,
sum((mss.transit_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
/ sum(mss.total_unpooled_safety_stock))*100 AS transit_ltvar_ss_percent,
decode( sum(mss.total_unpooled_safety_stock), 0, 0,
sum((mss.sup_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
/ sum(mss.total_unpooled_safety_stock))*100 AS sup_ltvar_ss_percent,
NULL AS delivered_quantity,
NULL AS required_quantity,
NULL AS target_service_level,
0 AS nr_sl_records,
NULL AS partner_id,
NULL AS customer_class_code,
RECORD_SAFETY_STOCK AS record_type
FROM msc_plan_organizations mpo, msc_safety_stocks mss,
msc_system_items msi, msc_item_categories mic
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.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 = mpo.plan_id
AND mss.organization_id = mpo.organization_id
AND mss.sr_instance_id = mpo.sr_instance_id
GROUP BY mss.plan_id, TRUNC(mss.period_start_date),
CUBE (msi.inventory_item_id, mss.sr_instance_id, mss.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING
GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 0 OR
GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 0 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 0
ORDER BY 1,2,3,4, 5, 6, 11;
SELECT mss.plan_id AS plan_id,
mss.sr_instance_id AS instance_id,
mss.organization_id AS org_id,
msi.inventory_item_id AS item_id,
mic.sr_instance_id AS sr_category_inst_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
--trunc(mss.period_start_date) AS week_start_date,
trunc(mpb.bkt_start_date) AS week_start_date,
NULL AS week_next_date,
NULL AS period_start_date,
'N' AS last_week_of_period,
sum(nvl(mss.safety_stock_quantity, 0)) AS achieved_ss_qty,
sum(nvl(mss.safety_stock_quantity*msi.standard_cost, 0)) AS achieved_ss_dollars,
sum(nvl(mss.achieved_days_of_supply, 0)) AS achieved_ss_days,
sum(nvl(mss.target_safety_stock, 0)) AS target_ss_qty,
sum(nvl(mss.target_safety_stock * msi.standard_cost, 0)) AS target_ss_dollars,
sum(nvl(mss.target_days_of_supply, 0)) AS target_ss_days,
sum(nvl(mss.user_defined_safety_stocks, 0)) AS userdef_ss_qty,
sum(nvl(mss.user_defined_safety_stocks * msi.standard_cost, 0)) AS userdef_ss_dollars,
sum(nvl(mss.user_defined_dos, 0)) AS userdef_ss_days,
0 AS total_unpooled_safety_stock,
0 AS demand_var_ss_percent,
0 AS mfg_ltvar_ss_percent,
0 AS transit_ltvar_ss_percent,
0 AS sup_ltvar_ss_percent,
0 AS nr_ss_records,
NULL AS inv_value_dollars,
NULL AS period_type,
NULL AS delivered_quantity,
NULL AS required_quantity,
NULL AS target_service_level,
0 AS nr_sl_records,
NULL AS partner_id,
NULL AS customer_class_code,
RECORD_SAFETY_STOCK AS record_type
FROM msc_plan_organizations mpo, msc_safety_stocks mss,
msc_system_items msi, msc_item_categories mic,
msc_plan_buckets mpb
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.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 = mpo.plan_id
AND mss.plan_id = mpb.plan_id
AND mss.organization_id = mpo.organization_id
AND mss.sr_instance_id = mpo.sr_instance_id
AND mss.period_start_date between mpb.bkt_start_date and mpb.bkt_end_date
GROUP BY mss.plan_id, TRUNC(mpb.bkt_start_date),
CUBE (msi.inventory_item_id, mss.sr_instance_id, mss.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING
GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 0
ORDER BY 1,2,3,4, 5, 6, 11;
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_instance_id AS sr_category_inst_id,
mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
mss.period_start_date AS week_start_date,
NULL AS week_next_date,
NULL AS period_start_date,
'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 nr_ss_records,
NULL AS inv_value_dollars,
CALENDAR_TYPE_MFG AS period_type,
nvl(mss.total_unpooled_safety_stock, 0) AS total_unpooled_safety_stock,
nvl(mss.demand_var_ss_percent, 0) AS demand_var_ss_percent,
nvl(mss.mfg_ltvar_ss_percent, 0) AS mfg_ltvar_ss_percent,
nvl(mss.transit_ltvar_ss_percent, 0) AS transit_ltvar_ss_percent,
nvl(mss.sup_ltvar_ss_percent, 0) AS sup_ltvar_ss_percent,
NULL AS delivered_quantity,
NULL AS required_quantity,
NULL AS target_service_level,
0 AS nr_sl_records,
NULL AS partner_id,
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 mss.period_start_date BETWEEN l_start_date AND l_end_date
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 2,3,4,5,6;
SELECT curr_start_date, curr_cutoff_date
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT 1
FROM msc_plans mp
WHERE mp.curr_plan_type = 4 OR mp.curr_plan_type = 9
AND mp.curr_start_date IS NOT NULL
AND mp.plan_id = p_plan_id;
SELECT msc_form_query_s.nextval
INTO p_query_id
FROM dual;
INSERT INTO msc_form_query
(
query_id,
NUMBER1, -- PLAN_ID
CREATED_BY, -- RECORD_TYPE
NUMBER9, -- achieved_ss_qty
NUMBER10,
NUMBER11,
NUMBER12,
NUMBER13,
NUMBER14,
NUMBER15,
NUMBER16,
PROGRAM_ID,
PROGRAM_APPLICATION_ID, -- achieved_sl
LAST_UPDATE_LOGIN, -- target_sl
NUMBER6, -- inventory_value_dollars
NUMBER7, -- period_type
DATE1, -- week_start_date
DATE3, -- period_start_date
NUMBER2, -- sr_instance_id
NUMBER3, -- org_id
REQUEST_ID, -- sr_cat_instance_id
NUMBER5, -- sr_category_id
CHAR1, -- category_name
NUMBER4, -- inventory_item_id
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE
)
SELECT
p_query_id,
mp.plan_id,
RECORD_INVENTORY_VALUE AS record_type,
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,
NULL AS achieved_sl,
NULL AS target_service_level,
SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
CALENDAR_TYPE_MFG AS period_type,
decode(NVL(mbid.detail_level, 0), 0, NULL, mbid.detail_date) AS week_start_date,
mpsd.period_start_date,
mpo.sr_instance_id AS instance_id,
mpo.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
NULL AS item_id,
SYSDATE AS last_update_date,
-1 AS last_updated_by,
SYSDATE AS CREATION_DATE
FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
msc_trading_partners mtp, msc_period_start_dates mpsd, msc_plan_buckets mpb
WHERE mp.plan_id = l_planlist(l_index)
AND mp.plan_id = mpo.plan_id
AND mpo.plan_id = mbid.plan_id
AND mpo.organization_id = mbid.organization_id
AND mpo.sr_instance_id = mbid.sr_instance_id
AND mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
AND mbid.period_type = CALENDAR_TYPE_MFG
-- BEGIN FUNNY SECTION
-- For some strange reason MSC_BIS_INV_DETAIL has week records for period buckets
-- So we need to filter these out before we aggregate
AND mpb.plan_id = mbid.plan_id
AND (mpb.bucket_type = 2 OR nvl(detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD)
AND mbid.detail_date BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
-- END FUNNY SECTION
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 nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
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.sr_instance_id = nvl(l_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
AND mic.category_set_id = l_default_category_set_id
AND mtp.sr_tp_id = mp.organization_id
AND mtp.sr_instance_id = mp.sr_instance_id
AND mtp.partner_type = 3
AND mtp.sr_Instance_id = mpsd.sr_instance_id
AND mtp.calendar_code = mpsd.calendar_code
AND mtp.calendar_exception_set_id = mpsd.exception_set_id
AND ((nvl(mbid.detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD AND mbid.detail_date = mpsd.period_start_date) OR
(mbid.detail_level = DETAIL_LEVEL_WEEK AND mbid.detail_date >= mpsd.period_start_date AND mbid.detail_date < mpsd.next_date))
GROUP BY mp.plan_id, mbid.period_type, mpsd.period_start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
);
INSERT INTO msc_form_query
(
query_id,
NUMBER1, -- PLAN_ID
CREATED_BY, -- RECORD_TYPE
NUMBER9, -- achieved_ss_qty
NUMBER10,
NUMBER11,
NUMBER12,
NUMBER13,
NUMBER14,
NUMBER15,
NUMBER16,
PROGRAM_ID,
PROGRAM_APPLICATION_ID, -- achieved_sl
LAST_UPDATE_LOGIN, -- target_sl
NUMBER6, -- inventory_value_dollars
NUMBER7, -- period_type
DATE1, -- week_start_date
DATE3, -- period_start_date
NUMBER2, -- sr_instance_id
NUMBER3, -- org_id
REQUEST_ID, -- sr_cat_instance_id
NUMBER5, -- sr_category_id
CHAR1, -- category_name
NUMBER4, -- inventory_item_id
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE
)
SELECT
p_query_id,
mp.plan_id,
RECORD_INVENTORY_VALUE AS record_type,
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,
NULL AS achieved_sl,
NULL AS target_service_level,
SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
mpsd.start_date,
mpo.sr_instance_id AS instance_id,
mpo.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
NULL AS item_id,
SYSDATE AS last_update_date,
-1 AS last_updated_by,
SYSDATE AS CREATION_DATE
FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
msc_bis_periods mpsd
WHERE mp.plan_id = l_planlist(l_index)
AND mp.plan_id = mpo.plan_id
AND mpo.plan_id = mbid.plan_id
AND mpo.organization_id = mbid.organization_id
AND mpo.sr_instance_id = mbid.sr_instance_id
AND mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
AND nvl(mbid.period_type, CALENDAR_TYPE_BIS) = CALENDAR_TYPE_BIS
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 nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
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(l_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
AND mp.sr_instance_id = mpsd.sr_instance_id
AND mp.organization_id = mpsd.organization_id
AND mpsd.period_set_name = g_period_setname
AND mbid.detail_date = mpsd.START_DATE
GROUP BY mp.plan_id, mbid.period_type, mpsd.start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
);
SELECT msc_form_query_s.nextval
INTO query_id
FROM dual;
SELECT msc_form_query_s.nextval
INTO p_query_id
FROM dual;
INSERT INTO msc_form_query
(
query_id,
NUMBER1, -- PLAN_ID
CREATED_BY, -- RECORD_TYPE
NUMBER9, -- achieved_ss_qty
NUMBER10,
NUMBER11,
NUMBER12,
NUMBER13,
NUMBER14,
NUMBER15,
NUMBER16,
PROGRAM_ID,
PROGRAM_APPLICATION_ID, -- achieved_sl --_qty2
LAST_UPDATE_LOGIN, -- target_sl
NUMBER6, -- inventory_value_dollars
NUMBER7, -- period_type
DATE1, -- week_start_date
DATE3, -- period_start_date
NUMBER2, -- sr_instance_id
NUMBER3, -- org_id
REQUEST_ID, -- sr_cat_instance_id
NUMBER5, -- sr_category_id
CHAR1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE
)
SELECT
p_query_id,
msd.plan_id,
RECORD_SERVICE_LEVEL AS record_type,
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,
sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 /
sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_sl,
decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
NULL AS inventory_value_dollars,
CALENDAR_TYPE_BIS AS period_type,
NULL AS week_start_date,
msbp.START_DATE AS period_start_date,
msd.sr_instance_id AS instance_id,
msd.organization_id AS org_id,
mic.sr_instance_id AS sr_cat_instance_id,
mic.sr_category_id AS sr_category_id,
mic.category_name AS category_name,
SYSDATE,
-1,
SYSDATE
FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
msc_item_categories mic, msc_bis_periods msbp
WHERE mp.plan_id = l_planlist(l_index)
AND mp.plan_id = mpo.plan_id
AND msd.organization_id = mpo.organization_id
AND msd.sr_instance_id = mpo.sr_instance_id
AND msd.plan_id = mpo.plan_id
AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
AND msd.plan_id = msi.plan_id
AND msd.sr_instance_id = msi.sr_instance_id
AND msd.organization_id = msi.organization_id
AND msd.inventory_item_id = msi.inventory_item_id
AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
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 mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
AND mic.sr_instance_id = nvl(l_sr_cat_instance_id, mic.sr_instance_id)
AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
AND mp.sr_instance_id = msbp.sr_instance_id
AND mp.organization_id = msbp.organization_id
AND msbp.period_set_name = g_period_setname
AND TRUNC(msd.using_assembly_demand_date) BETWEEN msbp.START_DATE AND msbp.end_date
GROUP BY msd.plan_id, msbp.start_date,
CUBE (msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
HAVING (
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
);
select count(*)
from all_tab_partitions
where TABLE_NAME = 'MSC_ANALYSIS_AGGREGATE'
and table_owner = lv_msc_schema
and partition_name = l_partition_name;
select count(*) into l_temp from sys.all_tab_partitions where table_name = 'MSC_ANALYSIS_AGGREGATE'
AND PARTITION_NAME = 'ANALYSIS_AGGREGATE_'||P_PLAN_ID;
DELETE FROM msc_analysis_aggregate WHERE plan_id = p_plan_id;