[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*select CONV_RATE
into l_currency_rate
from MSC_CURRENCY_CONVERSIONS
where FROM_CURRENCY = p_func_currency
and TO_CURRENCY = l_reporting_currency
and SR_INSTANCE_ID = p_sr_instance_id
and CONV_DATE = p_date;*/
/*select currency_code
into l_func_currency
from msc_trading_partners
where sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and partner_type = 3;*/
select meaning
from mfg_lookups
where lookup_type = 'MSC_EXCEPTION_GROUP'
and lookup_code = p_exception_group_id;
select nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100))
into l_list_price
from msc_system_items msi
where
msi.plan_id =p_plan_id
and msi.sr_instance_id = p_inst_id
and msi.organization_id = p_org_id
and msi.inventory_item_id = p_item_id;
select count(1) into l_plan_constrained
from
msc_plans mp
where
mp.plan_id = p_plan_id
and( nvl(mp.daily_resource_constraints,0 ) = 1
or nvl(mp.weekly_resource_constraints,0) = 1
or nvl(mp.period_resource_constraints,0) = 1
or nvl(mp.daily_material_constraints,0 ) = 1
or nvl(mp.weekly_material_constraints,0) = 1
or nvl(mp.period_material_constraints,0) = 1);
select CURR_PLAN_TYPE
into l_plan_type
from
msc_plans
where
plan_id = p_plan_id;
select distinct p.plan_id
from msc_scenario_plans sp, msc_scenarios s,
(select distinct plan_id, plan_type from msc_plans
union all
select distinct scenario_id, to_number(10)
from msd_dp_ascp_scenarios_v
where demand_plan_id=5555555) p
where sp.scenario_id=s.scenario_id
and sp.plan_id=p.plan_id
and p.plan_type=decode(sign(nvl(p_plan_type, -1)), 1, p_plan_type, p.plan_type)
and s.scenario_id=p_scenario_id;
select plan_id
into l_plan_id
from msc_plan_runs
where plan_run_name=p_plan_run_name;
select distinct u.user_name
into l_user_name
from fnd_user u, fnd_user_resp_groups g
where u.user_id=g.user_id
and g.responsibility_application_id=724
and sysdate between u.start_date and nvl(u.end_date, sysdate)
and u.user_id=p_user_id;
insert into msc_hub_query(
query_id,
number1, -- plan_id
number2, -- plan_run_id
number3, -- owning_inst_id
number4, -- owning_org_id
number5, -- inventory_item_id
number6, -- aggr_type
number7, -- category_set_id
number8, -- sr_category_id
last_update_date, last_updated_by, creation_date, created_by, last_update_login
)
select distinct p_query_id, p_plan_id, p_plan_run_id,
f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
p_aggr_type,
p_category_set_id,
nvl(ic.sr_category_id, -23453),
sysdate, 1, sysdate, 1, 1
from msc_demantra_f f, msc_phub_item_categories_mv ic
where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
and f.owning_inst_id = ic.sr_instance_id(+)
and f.owning_org_id = ic.organization_id(+)
and f.inventory_item_id = ic.inventory_item_id(+)
and ic.category_set_id(+) = p_category_set_id;
insert into msc_hub_query(
query_id,
number1, -- plan_id
number2, -- plan_run_id
number3, -- sr_instance_id
number4, -- organization_id
number5, -- inventory_item_id
number6, -- aggr_type
number7, -- category_set_id
number8, -- sr_category_id
last_update_date, last_updated_by, creation_date, created_by, last_update_login
)
select distinct p_query_id, p_plan_id, p_plan_run_id,
i.sr_instance_id, i.organization_id, i.inventory_item_id,
p_aggr_type,
p_category_set_id,
nvl(ic.sr_category_id, -23453),
sysdate, 1, sysdate, 1, 1
from msc_system_items i, msc_phub_item_categories_mv ic
where i.plan_id=p_plan_id
and i.sr_instance_id = ic.sr_instance_id(+)
and i.organization_id = ic.organization_id(+)
and i.inventory_item_id = ic.inventory_item_id(+)
and ic.category_set_id(+) = p_category_set_id
union all
select distinct p_query_id, p_plan_id, p_plan_run_id,
i.sr_instance_id, i.organization_id, to_number(-23453),
p_aggr_type,
p_category_set_id,
to_number(-23453),
sysdate, 1, sysdate, 1, 1
from msc_system_items i
where i.plan_id=p_plan_id;
select furg.user_id, furg.responsibility_id, responsibility_application_id
from fnd_user_resp_groups furg,
fnd_user fu,
fnd_responsibility fr
where furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fu.user_name = 'APCC_ADMIN'
and fr.responsibility_key = 'APS_SCN_PLN';