[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail mbis
WHERE mbis.organization_id = l_org_id
AND mbis.sr_instance_id = l_instance_id
AND mbis.plan_id = l_plan_id
AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
and exists ( select 1
from msc_bom_components mbc
where mbc.organization_id = mbis.organization_id
AND mbc.sr_instance_id = mbis.sr_instance_id
AND mbc.plan_id = mbis.plan_id
and mbc.inventory_item_id = mbis.inventory_item_id
and mbc.using_assembly_id = l_product_family_id);
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail mbis
where mbis.plan_id = l_plan_id
AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
and exists ( select 1
from msc_bom_components mbc
where mbc.organization_id = mbis.organization_id
AND mbc.sr_instance_id = mbis.sr_instance_id
AND mbc.plan_id = mbis.plan_id
and mbc.inventory_item_id = mbis.inventory_item_id
and mbc.using_assembly_id = l_product_family_id);
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail mbis
WHERE mbis.organization_id = l_org_id
AND mbis.sr_instance_id = l_instance_id
AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
and mbis.detail_date between v_start_date and v_end_date
AND mbis.plan_id = l_plan_id
and exists ( select 1
from msc_bom_components mbc
where mbc.organization_id = mbis.organization_id
AND mbc.sr_instance_id = mbis.sr_instance_id
AND mbc.plan_id = mbis.plan_id
and mbc.inventory_item_id = mbis.inventory_item_id
and mbc.using_assembly_id = l_product_family_id);
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail mbis
where mbis.detail_date between v_start_date and v_end_date
AND mbis.plan_id = l_plan_id
AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
and exists ( select 1
from msc_bom_components mbc
where mbc.organization_id = mbis.organization_id
AND mbc.sr_instance_id = mbis.sr_instance_id
AND mbc.plan_id = mbis.plan_id
and mbc.inventory_item_id = mbis.inventory_item_id
and mbc.using_assembly_id = l_product_family_id);
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_date_mv_tab
WHERE organization_id = l_org_id
AND sr_instance_id = l_instance_id
AND plan_id = l_plan_id;
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_date_mv_tab
WHERE plan_id = l_plan_id;
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_date_mv_tab
WHERE organization_id = v_org_id
AND sr_instance_id = v_instance_id
and detail_date between v_start_date and v_end_date
AND plan_id = v_plan_id;
SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_date_mv_tab
WHERE detail_date between v_start_date and v_end_date
AND plan_id = v_plan_id;
SELECT SUM(nvl(mbi.inventory_value,0)),
SUM(NVL(mbi.mds_price,0)), SUM(NVL(mbi.mds_cost,0))
FROM msc_bis_inv_detail mbi
WHERE mbi.plan_id = v_plan_id
AND nvl(mbi.period_type,0) = 0 --bis.mfg period changes
and mbi.inventory_item_id = nvl(v_item_id,mbi.inventory_item_id)
and mbi.detail_date between nvl(v_start_date, mbi.detail_date-1) and
nvl(v_end_date, mbi.detail_date+1)
;
SELECT SUM(nvl(mbi.inventory_value,0)),
SUM(NVL(mbi.mds_price,0)), SUM(NVL(mbi.mds_cost,0))
FROM msc_bis_inv_detail mbi
WHERE mbi.plan_id = v_plan_id
AND mbi.organization_id = v_org_id
AND mbi.sr_instance_id = v_instance_id
AND nvl(mbi.period_type,0) = 0 --bis.mfg period changes
and mbi.inventory_item_id = nvl(v_item_id, mbi.inventory_item_id)
and mbi.detail_date between nvl(v_start_date, mbi.detail_date-1) and
nvl(v_end_date, mbi.detail_date+1)
;
select sr_instance_id, organization_id
from msc_plan_organizations
where plan_id = l_plan_id;
SELECT mbp.period_name, mbp.start_date, mbp.end_date
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(overutilization_cost,0))
FROM msc_bis_res_summary
WHERE plan_id = p_plan_id
AND nvl(period_type,0) = 0
AND organization_id = p_organization_id;
SELECT sum(nvl(overutilization_cost,0))
FROM msc_bis_res_summary
WHERE plan_id = p_plan_id
AND nvl(period_type,0) = 0;
SELECT sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail
WHERE organization_id = l_org_id
AND sr_instance_id = l_instance_id
AND plan_id = l_plan_id
AND nvl(period_type,0) = 0 --bis.mfg period changes
and inventory_item_id = l_item_id;
SELECT sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail
where plan_id = l_plan_id
AND nvl(period_type,0) = 0 --bis.mfg period changes
and inventory_item_id = l_item_id;
SELECT sum(nvl(overutilization_cost,0))
FROM msc_bis_res_summary
WHERE plan_id = p_plan_id
AND organization_id = p_organization_id
AND nvl(period_type,0) = 0
AND resource_date between l_start_date and l_end_Date;
SELECT sum(nvl(overutilization_cost,0))
FROM msc_bis_res_summary
WHERE plan_id = p_plan_id
AND nvl(period_type,0) = 0
AND resource_date between l_start_date and l_end_Date;
SELECT sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail
WHERE organization_id = l_org_id
AND sr_instance_id = l_instance_id
AND plan_id = l_plan_id
AND nvl(period_type,0) = 0 --bis.mfg period changes
and inventory_item_id = l_item_id
and detail_date between l_start_date and l_end_date;
SELECT sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(carrying_cost,0))
FROM msc_bis_inv_detail
where plan_id = l_plan_id
and inventory_item_id = l_item_id
AND nvl(period_type,0) = 0 --bis.mfg period changes
and detail_date between l_start_date and l_end_date;
SELECT msc_get_name.org_code(profit.organization_id,
profit.sr_instance_id),
SUM(NVL(profit.mds_price,0)),
SUM(NVL(profit.mds_cost,0))
FROM msc_bis_inv_detail profit
WHERE profit.plan_id = p_plan_id
AND nvl(profit.period_type,0) = 0 --bis.mfg period changes
GROUP BY 1;
sql_statement := ' SELECT sum(mbis.late_order_count) ' ||
' FROM msc_late_order_mv_tab mbis' ||
' WHERE mbis.plan_id = :1 ';
sql_statement := ' SELECT count(distinct mbis.number1) ' ||
' FROM msc_exception_details mbis' ||
' WHERE mbis.plan_id = :1 '||
' AND mbis.exception_type in (13,14,24,26) '||
' AND mbis.number1 is not null ';
SELECT mbp.period_name, mbp.start_date, mbp.end_date
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 mbp.start_date
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 < g_plan_start_date
and mp.plan_id = p_plan_id
and mbp.adjustment_period_flag ='N'
order by mbp.start_date desc;
select nvl(mp.data_start_date,sysdate), mp.cutoff_date
into g_plan_start_date, g_plan_end_date
from msc_plans mp
where plan_id =-1;
sql_statement := ' SELECT '||
' SUM(nvl(mbis.mds_cost,0)) '||
' FROM msc_bis_inv_date_mv_tab mbis' ||
' WHERE mbis.plan_id = :1 ';
sql_statement := ' SELECT '||
' SUM(nvl(mbis.mds_cost,0)) '||
' FROM msc_bis_inv_cat_mv_tab mbis' ||
' WHERE mbis.plan_id = :1 ';
sql_statement := ' SELECT '||
' SUM(nvl(mbis.mds_cost,0)) '||
' FROM msc_bis_inv_detail mbis' ||
' WHERE mbis.plan_id = :1 and nvl(mbis.period_type,0) = 0';
sql_statement := ' SELECT '||
' SUM(nvl(mbis.inventory_cost,0)) '||
' FROM msc_bis_inv_date_mv_tab mbis ' ||
' WHERE mbis.plan_id = :1 '||
' AND mbis.detail_date = :7 ';
sql_statement := ' SELECT '||
' SUM(nvl(mbis.inventory_cost,0)) '||
' FROM msc_bis_inv_cat_mv_tab mbis ' ||
' WHERE mbis.plan_id = :1 '||
' AND mbis.detail_date = :7 ';
sql_statement := ' SELECT '||
' SUM(nvl(mbis.inventory_cost,0)) '||
' FROM msc_bis_inv_detail mbis ' ||
' WHERE mbis.plan_id = :1 and nvl(mbis.period_type,0) = 0 '||
' AND mbis.detail_date = :7 ';
sql_statement := ' SELECT sum(demand_count) '||
' FROM msc_demand_mv_tab mbis'||
' WHERE mbis.plan_id = :1 ';
sql_statement := ' SELECT count(*) '||
' FROM msc_demands_mv_v mbis'||
' WHERE mbis.plan_id = :1 ';
sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
' FROM msc_bis_supplier_summary sup ' ||
' WHERE sup.plan_id = :1 ';
sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
-- sql_statement := ' SELECT avg(nvl(res.UTIL_BY_WT_VOL,0)) ';
sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
' FROM msc_bis_supplier_summary sup ' ||
' WHERE sup.plan_id = :1 ';
sql_statement := ' SELECT '||
' SUM(nvl(mds_cost,0)) '||
' FROM msc_bis_inv_detail mbis' ||
' WHERE mbis.plan_id = :1 '||
' AND mbis.detail_date between :7 AND :8 and nvl(mbis.period_type,0) = 0 ';
sql_statement := ' SELECT count(*) ' ||
' FROM msc_demands_mv_v mbis' ||
' WHERE mbis.plan_id = :1 ' ||
' AND mbis.using_assembly_demand_date '||
' BETWEEN :7 AND :8 ';
sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
' FROM msc_bis_supplier_summary sup ' ||
' WHERE sup.plan_id = :1 ' ||
' AND sup.detail_date ' ||
' between :8 and :9 ' ;
sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
' FROM msc_bis_supplier_summary sup ' ||
' WHERE sup.plan_id = :1 ' ||
' AND sup.detail_date ' ||
' between :8 and :9 ' ;
inv_statement := ' SELECT '||
' SUM(nvl(mbis.inventory_cost,0)) '||
' FROM msc_bis_inv_detail mbis' ||
' WHERE mbis.plan_id = :1 '||
' AND mbis.detail_date =:7 and nvl(mbis.period_type,0) = 0 ';
SELECT t.target
FROM msc_bis_targets t,
msc_bis_target_levels tl,
msc_bis_performance_measures m,
msc_bis_business_plans mbp
WHERE t.target_level_id = tl.target_level_id
and t.sr_instance_id = p_instance_id
and tl.sr_instance_id = p_instance_id
and m.sr_instance_id = p_instance_id
and mbp.sr_instance_id = p_instance_id
AND m.measure_id = tl.measure_id
AND m.measure_short_name = v_measure
and mbp.short_name = 'STANDARD'
AND t.business_plan_id = mbp.business_plan_id
and tl.target_level_short_name = v_target_level
AND t.org_level_value_id = decode(t.org_level_value_id,-1,-1,p_org_id)
AND t.time_level_value_id = nvl(p_time_level, t.time_level_value_id);
' AND exists (select 1 '||
' from msc_demands md '||
' where md.plan_id = mbis.plan_id '||
' and md.demand_id = mbis.number1 '||
' and md.project_id = :5)';
' AND exists (select 1 '||
' from msc_demands md '||
' where md.plan_id = mbis.plan_id '||
' and md.demand_id = mbis.number1 '||
' and md.project_id = :5 '||
' and md.task_id = :6)';
' (select 1 '||
' from msc_item_categories mit ' ||
' where mit.organization_id = mbis.organization_id '||
' and mit.sr_instance_id = mbis.sr_instance_id '||
' and mit.inventory_item_id = mbis.inventory_item_id '||
' and -1 = :9 '||
' and mit.category_set_id = :10 '||
' and mit.category_name = :11 )';
' (select 1 '||
' from msc_item_categories mit ' ||
' where mit.organization_id = mbis.organization_id '||
' and mit.sr_instance_id = mbis.sr_instance_id '||
' and mit.inventory_item_id = mbis.inventory_item_id '||
' and mit.sr_category_id = :9 '||
' and mit.category_set_id = :10 ' ||
' and ''-1'' = :11 )';
' (select 1 '||
' from msc_bom_components mbc ' ||
' where mbc.organization_id = mbis.organization_id '||
' and mbc.sr_instance_id = mbis.sr_instance_id '||
' and mbc.plan_id = mbis.plan_id ' ||
' and mbc.inventory_item_id = mbis.inventory_item_id '||
' and mbc.using_assembly_id = :11 )';
' (select 1 '||
' from msc_demands md '||
' where md.plan_id = mbis.plan_id '||
' and md.demand_id = mbis.number1 '||
' and trunc(nvl(md.assembly_demand_comp_date,md.using_assembly_demand_date)) between :7 and :8)';
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.quantity_by_due_date,0)*nvl(md.probability,1)), '||
' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
' FROM msc_demands md, ' ||
' msc_item_categories mic ' ||
where_stat;
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, ' ||
' msc_item_categories mic ' ||
where_stat;
sql_stat := ' 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_item_categories mic, '||
' msc_interorg_ship_methods mism '|| where_stat;
sql_stat := 'SELECT avg(md.service_level), count(*) '||
' FROM msc_demands md, ' ||
' msc_item_categories mic ' ||
where_stat;
sql_stat := ' SELECT 1 ' ||
' FROM msc_demands ';
SELECT service_level
FROM msc_plans
WHERE plan_id = p_plan;
SELECT service_level
FROM msc_trading_partners
WHERE sr_instance_id = p_instance_id
AND sr_tp_id=p_org_id;
SELECT service_level
FROM msc_system_items
WHERE plan_id = p_plan
AND sr_instance_id = p_instance_id
AND organization_id= p_org_id
AND inventory_item_id = p_item_id;
select display_kpi, curr_plan_type
from msc_plans
where plan_id = p_plan_id;
select plan_type from msc_plans where plan_id =p_plan_id;
select kpi_refresh
from msc_plans
where plan_id = p_plan_id;
select kpi_refresh
into v_kpi_refresh
from msc_plans
where plan_id = p_plan_id;
update msc_plans
set kpi_refresh = decode(v_status,0,0,-1,-1,kpi_refresh+1)
where plan_id = p_plan_id;
delete from msc_bis_inv_date_mv_tab
where plan_id = p_plan_id;
insert into msc_bis_inv_date_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
mds_price,
mds_cost,
inventory_cost,
production_cost,
purchasing_cost,
demand_penalty_cost,
carrying_cost,
plan_id,
organization_id,
sr_instance_id,
detail_date,
inventory_value,
planner_code)
select
sysdate,
-1,
sysdate,
-1,
-1,
sum(nvl(mbid.mds_price,0)),
sum(nvl(mbid.mds_cost,0)),
sum(nvl(mbid.inventory_cost,0)),
sum(nvl(mbid.production_cost,0)),
sum(nvl(mbid.purchasing_cost,0)),
sum(nvl(mbid.demand_penalty_cost,0)+
nvl(mbid.supplier_overcap_cost,0)),
sum(nvl(mbid.carrying_cost,0)),
mbid.plan_id,
mbid.organization_id,
mbid.sr_instance_id,
mbid.detail_date,
sum(nvl(mbid.inventory_value,0)),
msi.planner_code
from msc_bis_inv_detail mbid,
msc_system_items msi
where mbid.plan_id = p_plan_id
and nvl(mbid.period_type,0) = 0
and mbid.organization_id = msi.organization_id
and mbid.sr_instance_id = msi.sr_instance_id
and mbid.plan_id = msi.plan_id
and mbid.inventory_item_id = msi.inventory_item_id
group by mbid.plan_id,
mbid.organization_id,
mbid.sr_instance_id,
mbid.detail_date,
msi.planner_code;
insert into msc_bis_inv_date_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
mds_price,
mds_cost,
inventory_cost,
production_cost,
purchasing_cost,
demand_penalty_cost,
carrying_cost,
plan_id,
organization_id,
sr_instance_id,
detail_date)
select
sysdate,
-1,
sysdate,
-1,
-1,
sum(nvl(mds_price,0)),
sum(nvl(mds_cost,0)),
sum(nvl(inventory_cost,0)),
sum(nvl(production_cost,0)),
sum(nvl(purchasing_cost,0)),
sum(nvl(demand_penalty_cost,0)+
nvl(supplier_overcap_cost,0)),
sum(nvl(carrying_cost,0)),
plan_id,
organization_id,
sr_instance_id,
detail_date
from msc_bis_inv_detail
where plan_id = p_plan_id
and nvl(period_type,0) = 0
group by plan_id,
organization_id,
sr_instance_id,
detail_date;
delete from msc_bis_inv_cat_mv_tab
where plan_id = p_plan_id;
insert into msc_bis_inv_cat_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
mds_price,
mds_cost,
inventory_cost,
production_cost,
purchasing_cost,
demand_penalty_cost,
carrying_cost,
plan_id,
organization_id,
sr_instance_id,
sr_category_id,
category_name,
category_set_id,
detail_date)
select
sysdate,
-1,
sysdate,
-1,
-1,
sum(nvl(mbis.mds_price,0)),
sum(nvl(mbis.mds_cost,0)),
sum(nvl(mbis.inventory_cost,0)),
sum(nvl(mbis.production_cost,0)),
sum(nvl(mbis.purchasing_cost,0)),
sum(nvl(mbis.demand_penalty_cost,0)+
nvl(mbis.supplier_overcap_cost,0)),
sum(nvl(mbis.carrying_cost,0)),
mbis.plan_id,
mbis.organization_id,
mbis.sr_instance_id,
mit.sr_category_id,
mit.category_name,
mit.category_set_id,
mbis.detail_date
from msc_bis_inv_detail mbis,
msc_item_categories mit
where mbis.plan_id = p_plan_id
and mit.organization_id = mbis.organization_id
and mit.sr_instance_id = mbis.sr_instance_id
and mit.inventory_item_id = mbis.inventory_item_id
and nvl(mbis.period_type,0) = 0
group by mbis.plan_id,
mbis.organization_id,
mbis.sr_instance_id,
mit.sr_category_id,
mit.category_name,
mit.category_set_id,
mbis.detail_date;
delete from msc_demand_mv_tab
where plan_id = p_plan_id;
insert into msc_demand_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
plan_id,
organization_id,
sr_instance_id,
demand_count)
select
sysdate,
-1,
sysdate,
-1,
-1,
plan_id,
organization_id,
sr_instance_id,
count(*)
from msc_demands
where origination_type in (6,7,8,9,10,11,12,15,22,24,27,29,30)
and plan_id = p_plan_id
group by plan_id,
organization_id,
sr_instance_id;
delete from msc_late_order_mv_tab
where plan_id = p_plan_id;
insert into msc_late_order_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
plan_id,
organization_id,
sr_instance_id,
late_order_count)
select
sysdate,
-1,
sysdate,
-1,
-1,
plan_id,
organization_id,
sr_instance_id,
count(distinct number1)
from msc_exception_details
where exception_type in (13,14,24,26)
and plan_id = p_plan_id
group by plan_id,
organization_id,
sr_instance_id;
delete from msc_bis_res_date_mv_tab
where plan_id = p_plan_id;
insert into msc_bis_res_date_mv_tab(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
plan_id,
organization_id,
sr_instance_id,
resource_date,
utilization,
util_count,
util_sum)
select
sysdate,
-1,
sysdate,
-1,
-1,
res.plan_id,
res.organization_id,
res.sr_instance_id,
res.resource_date,
avg(nvl(res.utilization,0)),
count(nvl(res.utilization,0)),
sum(nvl(res.utilization,0))
from msc_department_resources mdr,
msc_bis_res_summary res
where mdr.department_id = res.department_id
AND mdr.resource_id = res.resource_id
AND mdr.plan_id = res.plan_id
AND mdr.sr_instance_id = res.sr_instance_id
AND mdr.organization_id = res.organization_id
and mdr.plan_id = p_plan_id
AND nvl(res.period_type,0) = 0
group by res.plan_id,
res.organization_id,
res.sr_instance_id,
res.resource_date;
SELECT a.oracle_username
FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id= 724;
SELECT 1
FROM all_objects
WHERE object_name = 'MSC_SUPPLIER_TREE_MV'
AND owner = lv_msc_schema;
select plan_type
from msc_plans
where plan_id = p_plan_id;
select nvl(archive_flag,2)
from msc_plans
where plan_id = p_plan_id;