The following lines contain the word 'select', 'insert', 'update' or 'delete':
select min(detail_date)-1
from msc_bis_inv_detail
where plan_id = p_plan
and nvl(period_type,0) = p_period_type
and nvl(detail_level,0) = p_detail_level
and detail_date > p_curr_pr_date;
select max(detail_date)+1
from msc_bis_inv_detail
where plan_id = p_plan
and sr_instance_id = p_instance
and organization_id = p_org
and inventory_item_id = p_item
and nvl(period_type,0) = p_period_type
and nvl(detail_level,0) = p_detail_level
and detail_date < p_curr_pr_date;
select plan_start_date, curr_cutoff_date
from msc_plans
where plan_id = p_plan;
select sr_instance_id, organization_id
from msc_plan_organizations
where plan_id = l_plan_id;
update msc_form_query
set number1 = l_cat_set_id,
number2 = p_plan_type
where query_id = l_mfq_query_id;
INSERT INTO msc_form_query (QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, NUMBER1, NUMBER2) values
(l_mfq_query_id, sysdate, -1, sysdate, -1, l_cat_set_id, p_plan_type);
select plan_type
from msc_plans
where plan_id = p_plan_id;
SELECT distinct sr_category_id
FROM msc_item_categories
WHERE category_set_id = l_cat_set
and category_name in (select category_name
from msc_item_categories
where category_set_id = l_cat_set
and sr_category_id = l_cat_id
and rownum = 1);
select msc_form_query_s.nextval
from dual;
SELECT sr_instance_id, organization_id
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT count(distinct mpsd.period_start_date)
FROM msc_trading_partners tp,
msc_period_start_dates mpsd,
msc_plans mp
WHERE mpsd.calendar_code = tp.calendar_code
and mpsd.sr_instance_id = tp.sr_instance_id
and mpsd.exception_set_id = tp.calendar_exception_set_id
and tp.sr_instance_id = p_sr_instance_id
and tp.sr_tp_id = p_org_id
and tp.partner_type =3
and mp.plan_id = p_plan_id
and mpsd.period_start_date between mp.data_start_date and mp.cutoff_date;
SELECT count(distinct mbp.period_name)
FROM msc_bis_periods mbp,
msc_plans mp
WHERE mbp.organization_id = mp.organization_id
and mbp.sr_instance_id = mp.sr_instance_id
and ((mbp.start_date between nvl(mp.curr_start_date, sysdate)
and mp.cutoff_date
or mbp.end_date between nvl(mp.curr_start_date,sysdate)
and mp.cutoff_date) or
(mp.curr_start_date between mbp.start_date and mbp.end_date))
and mp.plan_id = p_plan_id;
l_select varchar2(300);
l_insert varchar2(300);
l_mfq_select varchar2(300);
l_mfq_insert varchar2(600);
l_plan_insert varchar2(300);
l_org_insert varchar2(300);
l_cate_insert varchar2(300);
l_pr_insert varchar2(300);
l_plan_org_cate_insert varchar2(300);
l_mfq_plan_insert varchar2(300);
l_mfq_org_insert varchar2(300);
l_mfq_cate_insert varchar2(300);
l_mfq_plan_org_cate_insert varchar2(300);
l_select := ' INSERT INTO msc_form_query ( '||
' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
' NUMBER11, NUMBER12, CHAR5) ';
l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
' round(sum(ss_cost_no_post), '|| l_round || ' ), '||
' round(sum(ss_cost_post), '|| l_round ||'), '||
' round(sum(ss_cost_savings), '|| l_round ||' ), '||
' round(sum(ss_value_no_post), '|| l_round ||' ), '||
' round(sum(ss_value_post), '|| l_round ||' ), '||
' round(sum(ss_value_savings), '|| l_round ||'), ';
l_plan_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), detail_date, plan_name, '||
' to_number(null), to_number(null), to_char(null)';
l_org_insert := ' plan_id, plan_name, '||
' sr_instance_id, organization_id, org_code, '||
' to_number(null), to_char(null), detail_date, plan_name||'' - ''||org_code ,'||
' to_number(null), to_number(null), to_char(null)';
l_cate_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' category_id, category_name, detail_date, plan_name||'' - ''||category_name ,'||
' to_number(null), to_number(null), to_char(null)';
l_pr_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), detail_date, plan_name||'' - ''||detail_date ,'||
' detail_level, period_type, period_type_url ';
l_plan_org_cate_insert := ' plan_id, plan_name, '||
' sr_instance_id, organization_id, org_code, '||
' category_id, category_name, detail_date, plan_name||''-''||org_code||''-''||category_name ,'||
' to_number(null), to_number(null), to_char(null)';
l_mfq_select := ' INSERT INTO msc_form_query ( '||
' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
' NUMBER11, NUMBER12, CHAR5 ) ';
l_mfq_insert := ' SELECT '||l_seq_id2 ||' , sysdate, -1, sysdate, -1, '||
' round(sum(mfq1.NUMBER5), '|| l_round || ' ), '||
' round(sum(mfq1.NUMBER6), '|| l_round ||'), '||
' round(sum(mfq1.NUMBER7), '|| l_round ||' ), '||
' round(sum(mfq1.NUMBER8)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
' round(sum(mfq1.NUMBER9)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
' round(sum(mfq1.NUMBER10)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||'), ';
l_mfq_plan_insert := ' mfq1.number1, mfq1.char1, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), to_date(null), mfq1.char1, '||
' to_number(null), to_number(null), to_char(null) ';
l_mfq_org_insert := ' mfq1.number1, mfq1.char1, '||
' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
' to_number(null), to_char(null), to_date(null), mfq1.char1||'' - ''||mfq1.char2 ,'||
' to_number(null), to_number(null), to_char(null)';
l_mfq_cate_insert := ' mfq1.number1, mfq1.char1, '||
' to_number(null), to_number(null), to_char(null), '||
' mfq1.NUMBER4, mfq1.CHAR3, to_date(null), mfq1.char1||'' - ''||mfq1.CHAR3 ,'||
' to_number(null), to_number(null), to_char(null)';
l_mfq_plan_org_cate_insert := ' mfq1.number1, mfq1.char1, '||
' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
' mfq1.NUMBER4, mfq1.CHAR3, to_date(null), mfq1.char1||''-''||mfq1.char2||''-''||mfq1.CHAR3 ,'||
' to_number(null), to_number(null), to_char(null)';
l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_insert||l_mfq_from||l_mfq_where||l_mfq_plan_groupby;
l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;
l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_org_insert||l_mfq_from||l_mfq_where||l_mfq_org_groupby;
l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_cate_insert||l_mfq_from||
l_mfq_where||l_mfq_cate_groupby;
l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
l_sql_stmt := l_select||l_insert||l_plan_org_cate_insert||l_from||l_where||l_plan_org_cate_groupby;
l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_org_cate_insert||
l_mfq_from||l_mfq_where||l_mfq_plan_org_cate_groupby;
l_sql_stmt := ' SELECT distinct plan_id, period_type, detail_level, detail_date '||
' from msc_bis_inv_detail '||
' where plan_id in ('|| arg_plan_list ||')';
INSERT INTO msc_form_query (
QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
NUMBER1, number2, number3, date1, date2 )
values
( l_mfq_query_id , sysdate, -1, sysdate, -1,
l_cur_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_temp_date,
plan_end_date);
INSERT INTO msc_form_query (
QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
NUMBER1, number2, number3, date1, date2 )
values
( l_mfq_query_id , sysdate, -1, sysdate, -1,
l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_start_date, l_end_date);
INSERT INTO msc_form_query (
QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
NUMBER1, number2, number3, date1, date2 )
values
( l_mfq_query_id , sysdate, -1, sysdate, -1,
l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_end_date, plan_end_date);
l_select varchar2(300);
l_insert varchar2(500);
l_plan_insert varchar2(300);
l_plan_dflt_insert varchar2(200);
l_org_insert varchar2(300);
l_org_dflt_insert varchar2(200);
l_cate_insert varchar2(300);
l_cate_dflt_insert varchar2(200);
l_item_insert varchar2(300);
l_item_dflt_insert varchar2(200);
l_demand_class_insert varchar2(300);
l_demand_class_dflt_insert varchar2(200);
l_pr_insert varchar2(300);
l_pr_dflt_insert varchar2(200);
l_select := ' INSERT INTO msc_form_query ( '||
' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
' NUMBER10, NUMBER11, NUMBER12,'||
' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, '||
' NUMBER4, CHAR3, '||
' NUMBER5, CHAR4, '||
' CHAR5, DATE1, CHAR6, '||
' number14, number15, char7 ) ';
l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
' round(
decode(sum(achieved_service_level_qty2), 0, 0,
sum(achieved_service_level_qty1)/sum(achieved_service_level_qty2))
,'||l_round ||'),'||
' round(avg(target_service_level),'||l_round ||'),';
l_plan_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_char(null), to_date(null) , plan_name, '||
' to_number(null), to_number(null), to_char(null) ';
l_org_insert := ' plan_id, plan_name, '||
' sr_instance_id, organization_id, org_code, '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_char(null), to_date(null) , plan_name||'' - ''||org_code, ' ||
' to_number(null), to_number(null), to_char(null) ';
l_cate_insert := ' plan_id, plan_name,'||
' to_number(null), to_number(null), to_char(null), '||
' category_id, category_name, '||
' to_number(null), to_char(null), '||
' to_char(null), to_date(null) , plan_name||'' - ''||category_name, ' ||
' to_number(null), to_number(null), to_char(null) ';
l_item_insert := ' plan_id, plan_name,'||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' inventory_item_id, item_name, '||
' to_char(null), to_date(null) , plan_name||'' - ''||item_name, ' ||
' to_number(null), to_number(null), to_char(null) ';
l_demand_class_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' demand_class, to_date(null) , '||
' plan_name||'' - ''||demand_class, ' ||
' to_number(null), to_number(null), to_char(null) ';
l_pr_insert := ' plan_id, plan_name,'||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_char(null), detail_date , plan_name||'' - ''||detail_date, ' ||
' period_type, detail_level, period_type_url ';
l_plan_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 0), ';
l_org_dflt_insert := ' to_number(null), ';
l_cate_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 6, null, '||
' null, null, null, null, null, category_id), ';
l_item_dflt_insert := ' to_number(null), ';
l_demand_class_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 4, null, null, '||
' null, demand_class, null, null, null), ';
l_pr_dflt_insert := ' to_number(null), ';
l_sql_stmt := l_select||l_insert||l_plan_dflt_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
l_sql_stmt := l_select||l_insert||l_org_dflt_insert||l_org_insert||l_from||l_where||l_org_groupby;
l_sql_stmt := l_select||l_insert||l_cate_dflt_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
l_sql_stmt := l_select||l_insert||l_item_dflt_insert||l_item_insert||l_from||l_where||l_item_groupby;
l_sql_stmt := l_select||l_insert||l_demand_class_dflt_insert
||l_demand_class_insert||l_from||l_where||l_demand_class_groupby;
l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
select min(detail_date)-1
from msc_bis_inv_detail
where plan_id = p_plan
and sr_instance_id = arg_instance
and organization_id = arg_org
and nvl(period_type,0) = p_period_type
and nvl(detail_level,0) = p_detail_level
and detail_date > p_curr_pr_date;
l_select varchar2(500);
l_insert varchar2(1500);
l_plan_insert varchar2(300);
l_org_insert varchar2(300);
l_cate_insert varchar2(300);
l_item_insert varchar2(300);
l_pr_insert varchar2(300);
l_select := ' insert into msc_form_query ( '||
' query_id, last_update_date, last_updated_by, creation_date, created_by, '||
' number6, number7, number8, number9, number10, number11, number12, number13, number16,'||
' number17, number18, number19, number20,'||
' number1, char1, number2, number3, char2, '||
' number4, char3, '||
' number5, char4, '||
' date1, char5, number14, number15, char6 ) ';
l_insert := ' select '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
' round(sum(nvl(planned_production_cost,0)), '|| l_round ||' ), '||
' round(sum(nvl(planned_carrying_cost,0)), '|| l_round ||' ), '||
' round(sum(nvl(planned_purchasing_cost,0)), '||l_round ||' ), '||
' round(sum(nvl(planned_tp_cost,0)), '|| l_round ||' ), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(planned_production_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(planned_carrying_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(planned_purchasing_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(planned_tp_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
' round(sum(nvl(planned_revenue,0)), '|| l_round ||' ), '||
' round(sum(nvl(int_repair_cost,0)), '|| l_round ||' ), '||
' round(sum(nvl(ext_repair_cost,0)), '|| l_round ||' ), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(int_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
' decode(sum(planned_total_cost),0,0, '||
' round(nvl(sum(ext_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), ';
l_plan_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_date(null) , plan_name, '||
' to_number(null), to_number(null), to_char(null) ';
l_org_insert := ' plan_id, plan_name, '||
' sr_instance_id, organization_id, org_code, '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_date(null) , plan_name||'' - ''||org_code, '||
' to_number(null), to_number(null), to_char(null) ';
l_cate_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' category_id, category_name, '||
' to_number(null), to_char(null), '||
' to_date(null) , plan_name||'' - ''||category_name, '||
' to_number(null), to_number(null), to_char(null) ';
l_item_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' inventory_item_id, item_name, '||
' to_date(null) , plan_name||'' - ''||item_name ,'||
' to_number(null), to_number(null), to_char(null) ';
l_pr_insert := ' plan_id, plan_name, '||
' to_number(null), to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' to_number(null), to_char(null), '||
' detail_date , plan_name||'' - ''||detail_date ,'||
' detail_level, period_type, period_type_url ';
l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;
l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
l_sql_stmt := l_select||l_insert||l_item_insert||l_from||l_where||l_item_groupby;
l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
SELECT count(*)
FROM msc_bis_periods mbp,
msc_plans mp
WHERE mbp.organization_id = mp.organization_id
and mbp.sr_instance_id = mp.sr_instance_id
and ((mbp.start_date between nvl(mp.data_start_date, sysdate)
and mp.cutoff_date
or mbp.end_date between nvl(mp.data_start_date,sysdate)
and mp.cutoff_date) or
(mp.data_start_date between mbp.start_date and mbp.end_date))
and mp.plan_id = p_plan_id
and mbp.adjustment_period_flag ='N'
order by mbp.start_date;
select sum(nvl(MSC_ANALYSIS_PKG.get_tp_cost(mbid.period_type,
mbid.detail_level, mbid.plan_id, mbid.sr_instance_id,
mbid.organization_id, mbid.inventory_item_id, mbid.detail_date),0))
FROM msc_bis_inv_detail mbid
WHERE mbid.plan_id = p_plan_id
and mbid.sr_instance_id = p_inst_id
and mbid.organization_id = p_org_id
and nvl(mbid.detail_level, 0) = 0
and nvl(mbid.period_type, 0) = 0;
select round(sum(nvl(((ms.new_order_quantity * msi.unit_weight) * mism.cost_per_weight_unit),0)),6)
from msc_supplies ms,
msc_system_items msi,
msc_interorg_ship_methods mism,
msc_plans mp
WHERE ms.plan_id = p_plan_id
and ms.organization_id <> ms.source_organization_id
and ms.order_type in (l_otype1, l_otype2, l_otype3)
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.plan_id = mism.plan_id
and ms.organization_id = mism.to_organization_id
and ms.sr_instance_id = mism.sr_instance_id
and ms.source_organization_id = mism.from_organization_id
and ms.source_sr_instance_id = mism.sr_instance_id2
and ms.ship_method = mism.ship_method
and ms.plan_id = mp.plan_id
and trunc(ms.new_dock_date) between mp.curr_start_date and mp.curr_cutoff_date ;
l_cursor := ' select planned_production_cost '||
' ,planned_carrying_cost, planned_purchasing_cost '||
' ,planned_total_cost, planned_revenue '||
' ,planned_gross_profit, planned_gross_profit_pct '||
' ,inventory_value, plan_id '||
' ,ext_repair_cost, int_repair_cost '||
' from msc_srvlvl_profit_v '||
' where plan_id = :1 ';
l_cursor2 := ' select plan_id from msc_plans where plan_id in (' ||arg_plan_list ||') ';
INSERT INTO MSC_FORM_QUERY
(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
char1,
number2,
number3,
number4,
number5,
number6,
number7,
number8,
number9,
number10,
number11,
number12,
number13,
number14
)
VALUES
(
l_seq_id,
sysdate,
-1,
sysdate,
-1,
l_plan_id,
l_plan_name,
round(nvl(l_attained,0), l_round),
round(nvl(l_target,0), l_round),
round(nvl(l_plnd_prod_cost,0), l_round),
round(nvl(l_plnd_carr_cost,0), l_round),
round(nvl(l_plnd_purc_cost,0), l_round),
round(nvl(l_tp_cost,0), l_round),
round(nvl(l_plnd_tot_cost + l_tp_cost,0), l_round),
round(nvl(l_plnd_rev,0), l_round),
round(nvl(l_plnd_gross_profit,0), l_round),
round(nvl(l_calc_gross_profit_pct,0), l_round),
round(nvl(l_inv_value,0), l_round),
round(nvl(l_ext_repair_cost,0), l_round),
round(nvl(l_int_repair_cost,0), l_round)
);
select sr_instance_id, organization_id
from msc_plan_organizations
where plan_id = l_plan_id;
select nvl(DAILY_RESOURCE_CONSTRAINTS,0)+
nvl(WEEKLY_RESOURCE_CONSTRAINTS,0)+
nvl(PERIOD_RESOURCE_CONSTRAINTS,0),
plan_type
into v_constraint, v_plan_type
from msc_plans
where plan_id = p_plan_id;
sql_stat := 'SELECT sum(nvl(md.old_demand_quantity,0)*nvl(md.probability,1)), '||
' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
' FROM msc_demands md ' ||
' WHERE md.plan_id = :1 ' ||
' AND sr_instance_id = :2 ' ||
' AND organization_id = :3 ' ||
' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
sql_stat := 'SELECT avg(md.service_level), count(*) '||
' FROM msc_demands md ' ||
' WHERE md.plan_id = :1 ' ||
' AND md.sr_instance_id = :2 ' ||
' AND md.organization_id = :3 ' ||
' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
sql_stat := ' select round(sum(nvl(((ms.new_order_quantity * '||
' msi.unit_weight) * mism.cost_per_weight_unit),0)),6), 0'||
' from msc_supplies ms, '||
' msc_system_items msi, '||
' msc_interorg_ship_methods mism '||
' WHERE ms.plan_id = :1 ' ||
' and ms.organization_id != ms.source_organization_id '||
' and ms.order_type in (' || l_order_type || ') '||
--' and ms.order_type in (5,11) '||
' and ms.plan_id = msi.plan_id '||
' and ms.organization_id = msi.organization_id '||
' and ms.sr_instance_id = msi.sr_instance_id '||
' and ms.inventory_item_id = msi.inventory_item_id '||
' and ms.plan_id = mism.plan_id '||
' and ms.organization_id = mism.to_organization_id '||
' and ms.sr_instance_id = mism.sr_instance_id '||
' and ms.source_organization_id = mism.from_organization_id '||
' and ms.source_sr_instance_id = mism.sr_instance_id2'||
' and ms.ship_method = mism.ship_method ' ||
' AND ms.organization_id = :2 '||
' AND ms.sr_instance_id = :3 ' ||
' AND ms.inventory_item_id = :4 ' ||
' AND trunc(ms.new_dock_date) BETWEEN :5 AND :6 ';
select nvl(demand_fulfillment_lt,0)
from msc_plans
where plan_id = p_plan_id;
select nvl(demand_fulfillment_lead_time,0)
from msc_service_levels
where plan_id = p_plan_id
--and category_set_id = l_cate_set_id
and definition_level = l_dflt_level
and ( ( sr_instance_id is null and organization_id is null
and p_inst_id is null and p_org_id is null)
or (sr_instance_id = p_inst_id and organization_id = p_org_id))
and ( ( inventory_item_id is null and p_item_id is null)
or (inventory_item_id = p_item_id) )
and ( ( demand_class is null and p_demand_class is null)
or (demand_class = p_demand_class) )
and ( ( customer_id is null and p_customer_id is null)
or (customer_id = p_customer_id) )
and ( ( customer_site_id is null and p_customer_site_id is null)
or (customer_site_id = p_customer_site_id) )
and ( ( sr_category_id is null and p_cate_id is null)
or (sr_category_id = p_cate_id) );
select nvl(demand_fulfillment_lead_time,0)
from msc_service_levels
where plan_id = p_plan_id
--and category_set_id = l_cate_set_id
and definition_level = nvl(p_definition_level, definition_level)
--and nvl(sr_instance_id, -1) = nvl(p_inst_id, -1) --not req as they populate for every row
and nvl(organization_id,-1) = nvl(p_org_id,-1)
and nvl(inventory_item_id,-1) = nvl(p_item_id,-1)
and nvl(demand_class,'-1') = nvl(p_demand_class,'-1')
and nvl(customer_id,-1) = nvl(p_customer_id,-1)
and nvl(customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(sr_category_id,-1) = nvl(p_cate_id,-1)
order by definition_level desc;