[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ltrim(rtrim(value))
into g_log_file_dir
from (select value from v$parameter2 where name='utl_file_dir'
order by rownum desc)
where rownum <2;
select msc_form_query_s.nextval
from dual;
select msc_analysis_query_s.nextval
from dual;
select higher_item_id
from msc_item_substitutes
where plan_id = p_plan_id
and relationship_type = c_mis_repair_to_type
and lower_item_id = p_lower_item_id;
select
number1,
number2,
date1,
date2
from msc_form_query
where query_id = ll_query_id
and number4 = ll_level;
select
number1,
number2,
date1,
date2
from msc_form_query
where query_id = ll_query_id;
select b.effective_date,
b.disable_date
from msc_item_substitutes b, msc_plans mp
where b.plan_id = -1
and b.relationship_type = 8
and b.lower_item_id = l_number1
and b.higher_item_id = l_number2
and mp.plan_id = p_plan
and b.sr_instance_id = mp.sr_instance_id;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
number5, number6, number7, date1, date2)
select l_query_id,sysdate, -1, -1, sysdate,
b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,
msc_sda_utils.getRepairItem(b.plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
b.prime_item_id,
b.effective_date,
b.disable_date
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan
and b.relationship_type = c_mis_supersession_type
and b.highest_item_id = p_item
and b.inferred_flag = 2
and b.forward_rule = 1
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date, sysdate))
or trunc(b.effective_date) >= trunc(sysdate) )
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
and b.higher_item_id = p_item;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
number5, number6, number7, date1, date2)
select l_query_id,sysdate, -1, -1, sysdate,
b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,
msc_sda_utils.getRepairItem(b.plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
b.prime_item_id,
b.effective_date,
b.disable_date
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan
and b.relationship_type = c_mis_supersession_type
and b.highest_item_id = p_item
and b.inferred_flag = 2
and b.forward_rule = 1
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date, sysdate))
or trunc(b.effective_date) >= trunc(sysdate) )
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
and b.higher_item_id = c_curr_row.number1;
update msc_form_query
set date1=l_st_date, date2=l_end_date
where query_id = l_query_id
and number1 = c_chain_row.number1
and number2 = c_chain_row.number2;
select count(*)
from msc_item_substitutes mis
where plan_id = p_plan
and mis.relationship_type = c_mis_supersession_type
and mis.inferred_flag = 2
and mis.forward_rule = 1
and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
or trunc(mis.effective_date) >= trunc(sysdate) )
and (lower_item_id = p_item_id or higher_item_id = p_item_id or highest_item_id = p_item_id);
select count(*)
from msc_form_query mfq
where mfq.query_id = ll_qid
and mfq.number8 = 100
and mfq.number9 <> ll_chain_id
and l_highest_item_id in (mfq.number1, mfq.number2, mfq.number3);
select distinct mis.highest_item_id
from msc_system_items msi,
msc_plans mp,
msc_item_substitutes mis,
msc_system_items msi2
where mp.plan_id = p_plan
and msi.plan_id = mp.plan_id
and msi.sr_instance_id = mp.sr_instance_id
and msi.inventory_item_id = p_item
and msi.group_id is not null
and mis.plan_id = p_plan
and mis.relationship_type = c_mis_supersession_type
and mis.inferred_flag = 2
and mis.forward_rule = 1
and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
or trunc(mis.effective_date) >= trunc(sysdate) )
and mis.plan_id = msi2.plan_id
and mis.sr_instance_id = msi2.sr_instance_id
and mis.highest_item_id = msi2.inventory_item_id
and msi2.group_id = msi.group_id
order by 1;
select distinct msi2.inventory_item_id
from msc_system_items msi,
msc_system_items msi2
where msi.plan_id = p_plan
and msi.inventory_item_id = p_item
and msi.group_id is not null
and msi2.plan_id = msi.plan_id
and msi2.group_id = msi.group_id
order by 1;
select distinct mis.highest_item_id
from msc_item_substitutes mis,
msc_form_query mfq
where mis.plan_id = p_plan
and mis.relationship_type = c_mis_supersession_type
and mis.inferred_flag = 2
and mis.forward_rule = 1
and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
or trunc(mis.effective_date) >= trunc(sysdate) )
and mfq.query_id = ll_query_id
and mfq.number8 = 1
and mfq.number1 in (mis.lower_item_id, mis.higher_item_id, mis.highest_item_id)
and mis.higher_item_id = mis.highest_item_id;
select distinct mfq.number3, mfq.number9
from msc_form_query mfq
where mfq.query_id = ll_query_id
and mfq.number8 = 100
and mfq.number2 = mfq.number3
order by 2,1;
--get all items with same group id and insert into l_qid1 and number8 with values 1,2
for c_row in c_grouped_items_cur
loop
l_flag := is_item_in_chain(p_plan, c_row.inventory_item_id);
insert into msc_form_query(query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number8)
values (l_qid1, sysdate, -1, -1, sysdate, c_row.inventory_item_id, l_flag);
--get all items with same group id and insert into l_qid1 and number8 with 100, number9 chain_id, number10 1--duplicate chain, 2-not-dup
for c_row in c_highest_item_cur(l_qid1)
loop
println('c_highest_item_cur item_id name '||c_row.highest_item_id||msc_get_name.item_name(c_row.highest_item_id, null, null, null));
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, number4,
number5, number6, number7, date1, date2, number8, number9,number10)
select l_qid1, sysdate, -1, -1, sysdate,
number1, number2, number3, number4+l_level,
number5, number6, number7, date1, date2, 100, l_chain_id,2
from msc_form_query
where query_id = l_query_id
order by number3, number4 desc;
update msc_form_query
set number10 = 1
where query_id = l_qid1
and number9 = c_row.number9;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, number4,
number5, number6, number7, date1, date2, number8, number9,number10)
select l_related_query_id, sysdate, -1, -1, sysdate,
number1, number2, number3, number4,
number5, number6, number7, date1, date2, number8, number9,number10
from msc_form_query
where query_id = l_qid1
and number8 = 100
and number10 = 2
order by number3, number4 desc;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, number4,
number5, number6, number7, date1, date2, number8, number9,number10)
select l_related_query_id, sysdate, -1, -1, sysdate,
number1, number2, number1 number3, -1,
number5, number6, number7, date1, date2, number8, number9,number10
from msc_form_query
where query_id = l_qid1
and number8 = 2
order by number1 desc;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, number4,
number5, number6, number7, date1, date2)
select l_related_query_id, sysdate, -1, -1, sysdate,
number1, number2, number3, number4+l_level,
number5, number6, number7, date1, date2
from msc_form_query
where query_id = l_query_id
order by number3, number4 desc;
insert into msc_form_query(query_id,
creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
number5, number6, number7, date1, date2)
select l_query_id,sysdate, -1, -1, sysdate,
b.lower_item_id, b.higher_item_id, b.highest_item_id, level,
b.reciprocal_flag,
msc_sda_utils.getRepairItem(plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
b.prime_item_id,
(select min(a.effective_date) from msc_item_substitutes a
where a.plan_id=p_plan
and a.lower_item_id = b.lower_item_id
and a.HIGHER_ITEM_ID=b.higher_item_id
and a.relationship_type = c_mis_supersession_type
and a.inferred_flag = 2
and a.forward_rule = 1) as effective_date,
disable_date
from msc_item_substitutes b
where b.plan_id = p_plan
and b.relationship_type = c_mis_supersession_type
and b.highest_item_id = p_item
and b.inferred_flag = 2
and b.forward_rule = 1
start with b.highest_item_id = p_item
and b.inferred_flag = 2
and b.highest_item_id = b.higher_item_id
connect by nocycle b.higher_item_id = prior b.lower_item_id
and b.plan_id = prior b.plan_id
and b.relationship_type = prior b.relationship_type
and b.inferred_flag = prior b.inferred_flag
and b.forward_rule = prior b.forward_rule
-- and effective_date = prior effective_date
order by level desc;
select query_name
from msc_personal_queries
where query_id = p_query_id;
select query_name
from msc_personal_queries
where query_id = p_query_id;
select decode(region_type,
0,country,
1,country||'-'||state,
2,country||'-'||state||'-'||city,
3,country||'-'||state||'-'||city||'-'||postal_code_from||'-'||postal_code_to,
10, mr.zone) reg_list_name
from msc_regions mr
where mr.region_id = p_region_id;
select b.prime_item_id,
b.highest_item_id,
b.effective_date
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan_id
and b.relationship_type = c_mis_supersession_type
and b.inferred_flag = 2
and b.forward_rule = 1
and b.lower_item_id = p_item_id
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
or trunc(b.effective_date) >= trunc(sysdate))
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
order by b.effective_date DESC;
select b.highest_item_id prime_item_id,
b.highest_item_id,
b.effective_date
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan_id
and b.relationship_type = c_mis_supersession_type
and b.inferred_flag = 2
and b.forward_rule = 1
and b.higher_item_id = p_item_id
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
or trunc(b.effective_date) >= trunc(sysdate))
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
order by effective_date DESC;
select count(*)
from msc_analysis_query
where query_id = p_query_id
and parent_row_index = p_row_index
and nvl(org_list_id, -1) = nvl(p_org_list_id, -1)
and nvl(inst_id, -1) = nvl(p_inst_id, -1)
and nvl(org_id, -1) = nvl(p_org_id, -1)
and nvl(top_item_id, -1) = nvl(p_top_item_id, -1)
and nvl(item_id, -1) = nvl(p_item_id, -1)
and nvl(org_list_state, -1) = nvl(p_orglist_action, -1)
and nvl(top_item_name_state, -1) = nvl(p_itemlist_action, -1) ;
select distinct
to_number(null) region_list_id,
to_char(null) region_list,
p_region_list region_id,
msc_sda_utils.getRegionName(p_region_list) region_code,
p_region_list sort_column
from dual
where p_region_type = c_reg_view
and p_view_type = 1
union all
select distinct
mpq.query_id region_list_id,
mpq.query_name region_list,
mpt.object_type region_id,
msc_sda_utils.getRegionName(mpt.object_type) region_code,
mpt.sequence_id sort_column
from msc_pq_types mpt,
msc_personal_queries mpq
where mpq.query_id = p_region_list
and mpq.query_id = mpt.query_id
and p_region_type = c_reg_list_view
and p_view_type = 1
order by 5;
select md.zone_id region_id,
md.sr_instance_id inst_id,
md.organization_id org_id,
msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
md.inventory_item_id
from msc_demands md,
msc_form_query mfq
where mfq.query_id = p_region_query_id
and md.plan_id = p_plan_id
and md.zone_id = mfq.number2;
select distinct mfq.number2 region_id,
mtp.sr_instance_id inst_id,
mtp.sr_tp_id org_id,
msc_get_name.org_code(mpo.organization_id, mpo.sr_instance_id) org_code,
to_number(null) inventory_item_id
from
--msc_region_locations mrl,
--msc_location_associations mla,
msc_trading_partners mtp,
msc_plan_organizations mpo,
msc_form_query mfq
--,msc_zone_regions mzr
where mfq.query_id = p_region_query_id
and nvl(mfq.number2,-1) > 0
--and mzr.parent_region_id = mfq.number2
--and mrl.region_id = mzr.region_id
--and mrl.location_id = mla.location_id
--and mla.partner_id = mtp.partner_id
and mtp.partner_type = 3
and mpo.plan_id = p_plan_id
and mpo.sr_instance_id = mtp.sr_instance_id
and mpo.organization_id = mtp.sr_tp_id
and p_view_type = 1 --region list selected by user
--pabram..commented out --msc_location_associations, msc_trading_partners for testing,
--we need to enable this when these tables are flushed correctly
--6736491, need to add mrl back
union all
select distinct
c_mbp_not_null_value region_id,
mtp.sr_instance_id inst_id,
mtp.sr_tp_id org_id,
msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
to_number(null) inventory_item_id
from msc_trading_partners mtp
where mtp.sr_tp_id = p_org_id
and mtp.sr_instance_id = p_inst_id
and mtp.partner_type = 3
and p_org_type = c_org_view
and p_view_type = 2
union all
select distinct
c_mbp_not_null_value region_id,
mpt.source_type inst_id,
mpt.object_type org_id,
msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
to_number(null) inventory_item_id
from msc_pq_types mpt,
msc_personal_queries mpq
where mpq.query_id = p_org_list
and mpq.query_id = mpt.query_id
and p_org_type = c_org_list_view
and p_view_type = 2
order by 4;
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, char1, number2, char2, number3)
values (p_region_query_id, sysdate, -1, -1, sysdate,
c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_regions.region_id, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, char1, number2, char2, number3)
values (p_region_query_id, sysdate, -1, -1, sysdate,
to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, char1, number2, char2, number3)
values (p_region_query_id, sysdate, -1, -1, sysdate,
to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_local_reg_type, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, number2, number3, char1, number4)
values (p_org_query_id, sysdate, -1, -1, sysdate,
c_local_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
select distinct
to_number(null) org_list_id,
to_char(null) org_list,
mtp.sr_instance_id inst_id,
mtp.sr_tp_id org_id,
msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
mtp.sr_tp_id sort_column
from msc_trading_partners mtp,
msc_analysis_query maq
where maq.query_id = p_query_id
and maq.row_index = p_row_index
and mtp.sr_instance_id = maq.inst_id
and mtp.sr_tp_id = maq.org_id
and mtp.partner_type = 3
and p_org_type = c_org_view
union all
select distinct
mpq.query_id org_list_id,
mpq.query_name org_list,
mpt.source_type inst_id,
mpt.object_type org_id,
msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
mpt.sequence_id sort_column
from msc_pq_types mpt,
msc_personal_queries mpq,
msc_analysis_query maq
where maq.query_id = p_query_id
and maq.row_index = p_row_index
and mpq.query_id = maq.org_list_id
and mpq.query_id = mpt.query_id
and p_org_type = c_org_list_view
order by 6;
println('inserting +');
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
number1, char1, number2, number3, char4, number4)
values (l_query_id, sysdate, -1, -1, sysdate,
c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.sort_column);
select to_number(null) top_item_id,
to_char(null) top_item_name,
inventory_item_id item_id,
item_name,
1 sort_column
from msc_items
where inventory_item_id = p_item_id
and p_item_view_type = c_item_view
union all
select distinct
p_item_id top_item_id,
msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id) item_id,
msc_get_name.item_name(decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id),null, null, null) item_name,
1 sort_column
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan_id
and (b.prime_item_id = p_item_id
or (higher_item_id = highest_item_id and higher_item_id = p_item_id)
)
and b.relationship_type = c_mis_supersession_type
and p_item_view_type = c_prime_view
and b.inferred_flag = 2
and b.forward_rule = 1
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
or trunc(b.effective_date) >= trunc(sysdate))
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
/*
start with prime_item_id = p_item_id
--and highest_item_id = higher_item_id
connect by nocycle higher_item_id = prior lower_item_id
and plan_id = prior plan_id
and relationship_type = prior relationship_type
and prime_item_id = prior prime_item_id
and inferred_flag = prior inferred_flag
and forward_rule = prior forward_rule
*/ --commented since where cl is enough to fetch this info
union all
select distinct
b.highest_item_id top_item_id,
msc_get_name.item_name(b.highest_item_id,null, null, null) top_item_name,
b.lower_item_id item_id,
msc_get_name.item_name(b.lower_item_id,null, null, null) item_name,
1 sort_column
from msc_item_substitutes b, msc_plans mp
where b.plan_id = p_plan_id
and b.relationship_type = c_mis_supersession_type
and p_item_view_type = c_supersession_view
and b.highest_item_id = p_item_id
and b.inferred_flag = 2
and b.forward_rule = 1
and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
or trunc(b.effective_date) >= trunc(sysdate))
and b.plan_id = mp.plan_id
and b.sr_instance_id = mp.sr_instance_id
/*
start with highest_item_id = p_item_id
and inferred_flag = 2
and highest_item_id = higher_item_id
--and highest_item_id = higher_item_id
connect by nocycle higher_item_id = prior lower_item_id
and plan_id = prior plan_id
and relationship_type = prior relationship_type
and inferred_flag = prior inferred_flag
and forward_rule = prior forward_rule
*/ --commented since where cl is enough to fetch this info
union all
select distinct
p_item_id top_item_id,
msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
mfq.number2 item_id,
msc_get_name.item_name(mfq.number2,null, null, null) item_name,
mfq.number3 sort_column
from msc_form_query mfq
where mfq.query_id = l_rqid_sorted
order by sort_column desc;
select number1, number2, number3
from msc_form_query
where query_id = l_rqid
order by number1, number4 desc;
select count(*)
from msc_form_query
where query_id = ll_query_id
and number2 = ll_item_id;
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number1, l_order);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number2, l_order);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number3, l_order);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
values (l_query_id, sysdate, -1, -1, sysdate, c_sschain.top_item_id, c_sschain.top_item_name, c_sschain.item_id, c_sschain.item_name,
c_sschain.sort_column);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
date1, date2, number1)
values (l_query_id, sysdate, -1, -1, sysdate, l_first_day, l_last_day, l_date_index);
select field_type,
field_name,
field_prompt,
decode(folder_object,
c_item_folder, nvl(group_by,2),
1) default_flag
from msc_criteria
where folder_object = p_folder_object
and field_name <> 'PRE_POSITION_INVENTORY'
order by to_number(field_type);
select distinct mun.note_id,
nvl(mun.last_update_date, mun.creation_date) comment_date,
msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
substr(mun.note_text1,1,80) comment_text
from msc_user_notes mun,
msc_form_query mfq
where
mun.entity_type = c_comment_entity_type
and mun.inventory_item_id in (mfq.number1, number2)
and mfq.query_id = p_chain_query_id
order by 2 desc;
select preference_id
from msc_user_preferences
where default_flag =1
and user_id = fnd_global.user_id
and nvl(plan_type,-1) = p_plan_type;
select distinct to_char(sr_instance_id)||'-'||to_char(organization_id)||'-'||to_char(inventory_item_id) node_id,
item_segments, organization_code, description, planner_code,
nettable_inventory_quantity, nonnettable_inventory_quantity, buyer_name,
mrp_planning_code_text, critical_component_flag critical_component_flag,
wip_supply_type_text,
bom_item_type_text, end_assembly_pegging_text, base_model,
category, category_desc, product_family_item, product_family_item_desc,
planning_exception_set, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(repetitive_type,2)) repetitive_type,
standard_cost, carrying_cost,
uom_code, planning_time_fence_date, planning_time_fence_days,
inventory_use_up_date, planning_make_buy_code_text,
ato_forecast_control_text, shrinkage_rate, preprocessing_lead_time,
full_lead_time, postprocessing_lead_time, leadtime_variability,
fixed_lead_time, variable_lead_time, fixed_order_quantity,
fixed_lot_multiplier, minimum_order_quantity, maximum_order_quantity,
safety_stock_days, safety_stock_percent, fixed_days_supply,
msc_get_name.lookup_meaning('SYS_YES_NO', rounding_control_type) rounding_control_type,
effectivity_control_type, abc_class_name, selling_price,
margin, average_discount, net_selling_price, service_level,
demand_time_fence_days, demand_time_fence_date, safety_stock_code,
atp_flag, atp_components_flag, drp_planned, weight_uom,
unit_weight, volume_uom, pip_flag, msc_get_name.lookup_meaning('SYS_YES_NO', create_supply_flag) create_supply_flag,
substitution_window,
convergence_text, divergence_text, continous_transfer_text, exclude_from_budget,
days_tgt_inv_window, days_max_inv_window, days_tgt_inv_supply,
days_max_inv_supply, shelf_life_days, release_time_fence_days,
min_shelf_life_days, unit_volume, to_number(null) max_early_days,
demand_fulfillment_lt, end_of_life_date, fcst_rule_for_demands_text,
fcst_rule_for_returns_text, interarrival_time, life_time_buy_date,
msc_get_name.lookup_meaning('SYS_YES_NO', decode(preposition_point,'Y','1','2')) preposition_point,
repair_cost,
repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
repetitive_planning_flag_text,
mfq.number3,
msiv.ROP_SAFETY_STOCK,
msiv.COMPUTE_SS,
msiv.COMPUTE_EOQ,
msiv.ORDER_COST,
msiv.MAX_USAGE_FACTOR,
msc_get_name.lookup_meaning('SYS_YES_NO', nvl(msiv.INTERMITTENT_DEMAND,2)) INTERMITTENT_DEMAND,
msiv.sr_instance_id,
msiv.inventory_item_id
from msc_system_items_sc_v msiv,
msc_form_query mfq, --items
msc_form_query mfq1 --orgs
where plan_id = p_plan_id
and category_set_id = l_category_set_id
and inventory_item_id in (mfq.number2) --, number1)
and mfq.query_id = p_chain_query_id
and mfq1.query_id = p_org_query_id
--and nvl(mfq1.number1,1) >0
and mfq1.number2 = msiv.sr_instance_id
and mfq1.number3 = msiv.organization_id
order by msiv.organization_code, mfq.number3 desc;
select med.exception_type,
msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
count(*) excp_all_count,
sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
from msc_exception_details med,
msc_form_query mfq, --items
msc_form_query mfq1 --orgs
where med.plan_id = p_plan_id
and med.inventory_item_id = mfq.number2
and mfq.query_id = p_chain_query_id
and mfq1.query_id = p_org_query_id
and nvl(mfq1.number1,1) >0
and mfq1.number2 = med.sr_instance_id
and mfq1.number3 = med.organization_id
group by med.exception_type,
msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
union all
select -99 exception_type,
msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
count(*) excp_all_count,
0 excp_no_count
from msc_supplies ms,
msc_system_items msi,
msc_form_query mfq, --items
msc_form_query mfq1 --orgs
where ms.plan_id = p_plan_id
and ms.inventory_item_id = mfq.number2
and mfq.query_id = p_chain_query_id
and mfq1.query_id = p_org_query_id
and nvl(mfq1.number1,1) >0
and mfq1.number2 = ms.sr_instance_id
and mfq1.number3 = ms.organization_id
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.organization_id = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and ( (ms.order_type = 13)
or (ms.order_type = 5
and nvl(ms.implemented_quantity,0)+nvl(ms.quantity_in_process,0) < nvl(ms.firm_quantity,ms.new_order_quantity)
and (nvl(msi.lots_exist,0) <> 2 or ms.new_order_quantity =0)
and (((ms.source_organization_id <> ms.organization_id or ms.source_sr_instance_id <> ms.sr_instance_id or ms.source_supplier_id is not null)
and msi.purchasing_enabled_flag = 1)
or (ms.source_organization_id is null and ms.source_supplier_id is null and msi.planning_make_buy_code = 2 and msi.purchasing_enabled_flag = 1)
or (ms.source_organization_id = ms.organization_id and ms.source_sr_instance_id = ms.sr_instance_id and msi.build_in_wip_flag = 1)
or (ms.source_organization_id is null and ms.source_supplier_id is null and msi.planning_make_buy_code = 1 and msi.build_in_wip_flag = 1))
)
)
group by -99,
msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10);
select name, key, nvl(value, c_null_space) value
from msc_analyze_preference
where module = c_sda_pref_set
and userid= fnd_global.user_id
order by name, key;
select lookup_code, meaning
from mfg_lookups
where lookup_type = c_sdview_rowtype_lookup
order by 1;
select lookup_code, meaning
from mfg_lookups
where lookup_type = c_fcstview_rowtype_lookup
order by 1;
select lookup_code, meaning
from mfg_lookups
where lookup_type = c_histview_rowtype_lookup
order by 1;
l_sql_stmt1 := 'select lookup_code, meaning from mfg_lookups where lookup_type = :1 ';
select count(*)
from msc_analyze_preference
where module = p_module_name
and name = p_name;
update msc_analyze_preference
set defaultset = to_char(l_default_flag),
public_flag = l_public_flag,
value = p_folder_value
where name = p_folder_name
and module = c_sda_save_item_folder;
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset, public_flag,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_folder_name, c_sda_save_item_folder, c_sda_save_item_folder, p_folder_value,
to_char(l_default_flag), l_public_flag,
sysdate, -1, sysdate, -1, -1);
procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
pragma autonomous_transaction;
select count(*)
from msc_analyze_preference
where module = p_module_name
and userid = fnd_global.user_id;
delete from msc_analyze_preference
where module = c_sda_save_item_folder
and userid = fnd_global.user_id;
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, c_sda_save_item_folder, c_sda_save_item_folder, c_sda_save_item_folder, p_event_list, 'N',
sysdate, -1, sysdate, -1, -1);
delete from msc_analyze_preference
where module = c_sda_save_settings
and userid = fnd_global.user_id;
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, c_sda_save_settings, c_sda_save_settings, c_sda_save_settings, p_event_list, 'N',
sysdate, -1, sysdate, -1, -1);
end update_close_settings;
select value
from msc_analyze_preference
where module = p_module_name
and userid = fnd_global.user_id;
procedure update_pref_set (p_name varchar2, p_desc varchar2,
p_days number, p_weeks number, p_periods number,
p_factor number, p_decimal_places number,
p_sd_row_list varchar2, p_fcst_row_list varchar2) is
pragma autonomous_transaction;
select count(*)
from msc_analyze_preference
where module = c_sda_pref_set
and name = p_name;
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_days, p_days, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_weeks, p_weeks, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_periods, p_periods, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_factor, p_factor, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_decimals, p_decimal_places, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_sd, p_sd_row_list, 'N',
sysdate, -1, sysdate, -1, -1);
insert into msc_analyze_preference
(userid, name, module, key, value, defaultset,
last_update_date, last_updated_by, creation_date, created_by, last_update_login)
values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_fcst, p_fcst_row_list, 'N',
sysdate, -1, sysdate, -1, -1);
update msc_analyze_preference
set value = p_days
where module = c_sda_pref_set and name = p_name and key = c_keys_days;
update msc_analyze_preference
set value = p_weeks
where module = c_sda_pref_set and name = p_name and key = c_keys_weeks;
update msc_analyze_preference
set value = p_periods
where module = c_sda_pref_set and name = p_name and key = c_keys_periods;
update msc_analyze_preference
set value = p_factor
where module = c_sda_pref_set and name = p_name and key = c_keys_factor;
update msc_analyze_preference
set value = p_decimal_places
where module = c_sda_pref_set and name = p_name and key = c_keys_decimals;
update msc_analyze_preference
set value = p_sd_row_list
where module = c_sda_pref_set and name = p_name and key = c_keys_sd;
update msc_analyze_preference
set value = p_fcst_row_list
where module = c_sda_pref_set and name = p_name and key = c_keys_fcst;
end update_pref_set;
select count(*)
from msc_doc_attachments
where sr_instance_id = nvl(p_inst_id, sr_instance_id)
and entity_name = l_entity_name
and (
--for items
(p_pk_value1 in (pk_value1, nvl(pk_value2,-1))
and nvl(p_pk_value2,-1) = -1)
or
--for superssion
(pk_value1 = p_pk_value1
and pk_value2 = p_pk_value2)
);
select count(*)
from msc_item_substitutes b
where b.plan_id = l_plan_id
and b.inferred_flag=2
and b.forward_rule=1
and ( ( p_pk_value1 in (b.lower_item_id, b.higher_item_id)
and nvl(p_pk_value2,-1) = -1 )
or (b.lower_item_id = p_pk_value1 and b.higher_item_id = p_pk_value2)
);