eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MSC_SDA_UTILS

Source


1 package body MSC_SDA_UTILS as
2 /*  $Header: MSCSDAUB.pls 120.39.12010000.3 2008/08/26 19:40:31 pabram ship $ */
3 
4   procedure println(p_msg varchar2) is
5   begin
6     if ( not(g_log_flag) ) then
7       return;
8     end if;
9 
10     --dbms_output.put_line(p_msg);
11     g_log_row := g_log_row + 1;
12 
13    if (g_log_file_name is null) then
14      select ltrim(rtrim(value))
15      into g_log_file_dir
16      from (select value from v$parameter2 where name='utl_file_dir'
17 	     order by rownum desc)
18      where rownum <2;
19      g_log_file_name := 'mscsda-1.txt';
20      g_log_file_handle := utl_file.fopen(g_log_file_dir, g_log_file_name, 'w');
21    end if;
22 
23    if (utl_file.is_open(g_log_file_handle)) then
24      utl_file.put_line(g_log_file_handle, p_msg);
25      utl_file.fflush(g_log_file_handle);
26      utl_file.fclose(g_log_file_handle);
27    else
28      g_log_file_handle := utl_file.fopen(g_log_file_dir, g_log_file_name, 'a');
29      utl_file.put_line(g_log_file_handle, p_msg);
30      utl_file.fflush(g_log_file_handle);
31      utl_file.fclose(g_log_file_handle);
32    end if;
33 
34   exception
35    when others then
36       return;
37   end println;
38 
39   function escapeSplChars(p_value varchar2) return varchar2 is
40     l_value varchar2(1000);
41   begin
42     l_value := p_value;
43     l_value := replace(l_value, c_field_seperator, c_field_seperator_esc);
44     l_value := replace(l_value, c_record_seperator, c_record_seperator_esc);
45     if (l_value is null) then
46       l_value := c_null_space;
47     end if;
48     return l_value;
49   end escapeSplChars;
50 
51   procedure addRecordToOutStream(p_one_record varchar2,
52     p_out_data_index in out nocopy number,
53     p_out_data in out nocopy msc_sda_utils.maxchartbl) is
54   begin
55     if (nvl(length(p_out_data(1)),0) = 1) then -- {
56       p_out_data(1) := p_one_record;
57     elsif ( nvl(length(p_out_data(p_out_data_index)),0) + length(p_one_record) < 32000 ) then
58       p_out_data(p_out_data_index) := p_out_data(p_out_data_index) || c_record_seperator || p_one_record;
59     else
60       p_out_data_index := p_out_data_index + 1;
61       p_out_data.extend;
62       p_out_data(p_out_data_index) := c_record_seperator || p_one_record;
63     end if; -- }
64   end addRecordToOutStream;
65 
66   procedure addToOutStream(p_one_record varchar2,
67     p_out_data_index in out nocopy number,
68     p_out_data in out nocopy msc_sda_utils.maxchartbl,
69     p_debug_flag number default null) is
70     l_one_record varchar2(32000);
71   begin
72     --println(' addToOutStream in/out');
73     if (nvl(length(p_out_data(1)),0) = 1) then -- {
74       p_out_data(1) := p_one_record;
75     elsif ( nvl(length(p_out_data(p_out_data_index)),0) + length(p_one_record) < 32000 ) then
76       p_out_data(p_out_data_index) := p_out_data(p_out_data_index) || p_one_record;
77     else
78       p_out_data_index := p_out_data_index + 1;
79       p_out_data.extend;
80       p_out_data(p_out_data_index) := p_one_record;
81     end if; -- }
82   end addToOutStream;
83 
84   function getNewFormQueryId return number is
85      cursor c_query is
86      select msc_form_query_s.nextval
87      from dual;
88      l_query_id number;
89   begin
90     open c_query;
91     fetch c_query into l_query_id;
92     close c_query;
93     return l_query_id;
94   end getNewFormQueryId;
95 
96   function getNewAnalysisQueryId return number is
97      cursor c_query is
98      select msc_analysis_query_s.nextval
99      from dual;
100      l_query_id number;
101   begin
102     open c_query;
103     fetch c_query into l_query_id;
104     close c_query;
105     return l_query_id;
106   end getNewAnalysisQueryId;
107 
108   function getRepairItem(p_plan_id number, p_lower_item_id number, p_highest_item_id number) return number is
109 
110     cursor c_repair_item_cur is
111     select higher_item_id
112     from msc_item_substitutes
113     where plan_id = p_plan_id
114       and relationship_type = c_mis_repair_to_type
115       and lower_item_id = p_lower_item_id;
116       --and highest_item_id = p_highest_item_id;
117 
118       l_repair_item_id number;
119   begin
120     open c_repair_item_cur;
121     fetch c_repair_item_cur into l_repair_item_id;
122     close c_repair_item_cur;
123     return l_repair_item_id;
124   end getRepairItem;
125 
126   function flushSupersessionChain(p_plan number, p_item number) return number is
127     l_query_id number;
128   begin
129     l_query_id := getNewFormQueryId;
130 
131     insert into msc_form_query(query_id,
132     creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
133     number5, number6, number7, date1, date2)
134     select  l_query_id,sysdate, -1, -1, sysdate,
135       lower_item_id, higher_item_id, highest_item_id, level,
136       reciprocal_flag,
137       msc_sda_utils.getRepairItem(plan_id, lower_item_id, highest_item_id) repair_item_id,
138       prime_item_id,
139       effective_date, disable_date
140     from msc_item_substitutes
141       where plan_id = p_plan
142         and relationship_type = c_mis_supersession_type
143         and highest_item_id = p_item
144         and inferred_flag = 2
145         and forward_rule = 1
146       start with highest_item_id = p_item
147         and inferred_flag = 2
148         and highest_item_id = higher_item_id
149       connect by nocycle higher_item_id = prior lower_item_id
150         and plan_id = prior plan_id
151         and relationship_type = prior relationship_type
152 	and inferred_flag = prior inferred_flag
153         and forward_rule = prior forward_rule
154         --  and effective_date = prior effective_date
155       order by level desc;
156 
157     return l_query_id;
158   end flushSupersessionChain;
159 
160   function getOrgList(p_query_id number) return varchar2 is
161     cursor c_name is
162     select query_name
163     from msc_personal_queries
164     where query_id = p_query_id;
165     l_name varchar2(250);
166   begin
167     open c_name;
168     fetch c_name into l_name;
169     close c_name;
170     return l_name;
171   end getOrgList;
172 
173   function getRegionList(p_query_id number) return varchar2 is
174     cursor c_name is
175     select query_name
176     from msc_personal_queries
177     where query_id = p_query_id;
178     l_name varchar2(250);
179   begin
180     open c_name;
181     fetch c_name into l_name;
182     close c_name;
183     return l_name;
184   end getRegionList;
185 
186   function getRegionName(p_region_id number) return varchar2 is
187     cursor c_name is
188     select decode(region_type,
189       0,country,
190       1,country||'-'||state,
191       2,country||'-'||state||'-'||city,
192       3,country||'-'||state||'-'||city||'-'||postal_code_from||'-'||postal_code_to,
193       10, mr.zone) reg_list_name
194     from msc_regions mr
195     where mr.region_id = p_region_id;
196     l_name varchar2(250);
197   begin
198     open c_name;
199     fetch c_name into l_name;
200     close c_name;
201     return l_name;
202   end getRegionName;
203 
204   procedure  getRegListValues(p_region_list varchar2, p_region_type number,
205     p_reg_list_id out nocopy number, p_reg_list out nocopy varchar2,
206     p_region_id out nocopy number, p_region_code out nocopy varchar2) is
207   begin
208         if (p_region_type = c_reg_list_view) then
209 	  p_reg_list_id := p_region_list;
210 	  p_reg_list := getRegionList(p_region_list);
211 	  p_region_id := null;
212 	  p_region_code := null;
213 	elsif (p_region_type = c_reg_view) then
214 	  p_reg_list_id := null;
215 	  p_reg_list := null;
216 	  p_region_id := p_region_list;
217 	  p_region_code := getRegionName(p_region_list);
218         end if;
219   end getRegListValues;
220 
221   procedure  getOrgListValues(p_orglist varchar2, p_org_type number,
222     p_org_list_id out nocopy number, p_org_list out nocopy varchar2,
223     p_inst_id out nocopy number, p_org_id out nocopy number,
224     p_org_code out nocopy varchar2) is
225 
226     l_open_pos number;
227     l_close_pos number;
228     l_comma_pos number;
229   begin
230 	if (p_org_type = c_org_list_view) then
231           p_org_list_id := p_orglist;
232           p_org_list := getOrgList(p_orglist);
233           p_inst_id := null;
234           p_org_id := null;
235           p_org_code := null;
236 	elsif (p_org_type = c_org_view) then
237           l_open_pos := instr(p_orglist,'(');
238           l_comma_pos := instr(p_orglist,'-');
239           if (l_comma_pos = 0) then
240             l_comma_pos := instr(p_orglist,',');
241           end if;
242           l_close_pos := instr(p_orglist,')');
243           p_org_list_id := null;
244           p_org_list := null;
245           p_inst_id := substr(p_orglist,l_open_pos+1, l_comma_pos-l_open_pos-1);
246           p_org_id := substr(p_orglist,l_comma_pos+1, l_close_pos-l_comma_pos-1);
247           p_org_code := msc_get_name.org_code(p_org_id, p_inst_id);
248 	end if;
249      println('getOrgListValues out');
250   end getOrgListValues;
251 
252   procedure  getItemListValues(p_cur_item_id number, p_item_view_type number,
253     p_top_item_id out nocopy number, p_top_item_name out nocopy varchar2,
254     p_item_id out nocopy number, p_item_name out nocopy varchar2)  is
255   begin
256         if (p_item_view_type = c_item_view) then
257 	  p_top_item_id := null;
258 	  p_top_item_name := null;
259 	  p_item_id := p_cur_item_id;
260 	  p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
261 	else
262 	  p_top_item_id := p_cur_item_id;
263 	  p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
264 	  p_item_id := null;
265 	  p_item_name := null;
266         end if;
267    end getItemListValues;
268 
269   procedure  getItemPrimeSS(p_plan_id number, p_item_id number,
270     p_prime_item_id out nocopy number, p_ss_item_id out nocopy number) is
271 
272     cursor c_prime_ss_cur is
273     select decode(p_item_id, lower_item_id, prime_item_id, higher_item_id),
274       highest_item_id
275     from msc_item_substitutes
276     where plan_id = p_plan_id
277       and relationship_type = c_mis_supersession_type
278       and inferred_flag = 2
279       and forward_rule = 1
280       and (lower_item_id = p_item_id
281 	   or (higher_item_id = highest_item_id and higher_item_id = p_item_id) );
282     --pabram..need to change.. add effective date logic here to pick the right one
283   begin
284     open c_prime_ss_cur;
285     fetch c_prime_ss_cur into p_prime_item_id, p_ss_item_id;
286     close c_prime_ss_cur;
287   end getItemPrimeSS;
288 
289   function check_row_exists(p_query_id number, p_row_index number,
290     p_org_list_id number, p_inst_id number, p_org_id number,
291     p_top_item_id number, p_item_id number, p_orglist_action number, p_itemlist_action number) return number is
292 
293     cursor c_maq_cur is
294     select count(*)
295     from msc_analysis_query
296     where query_id = p_query_id
297       and parent_row_index = p_row_index
298       and nvl(org_list_id, -1) = nvl(p_org_list_id, -1)
299       and nvl(inst_id, -1) = nvl(p_inst_id, -1)
300       and nvl(org_id, -1) = nvl(p_org_id, -1)
301       and nvl(top_item_id, -1) = nvl(p_top_item_id, -1)
302       and nvl(item_id, -1) = nvl(p_item_id, -1)
303       and nvl(org_list_state, -1) = nvl(p_orglist_action, -1)
304       and nvl(top_item_name_state, -1) = nvl(p_itemlist_action, -1) ;
305     l_count number;
306   begin
307     open c_maq_cur;
308     fetch c_maq_cur into l_count;
309     close c_maq_cur;
310 
311     if l_count = 0 then
312       return c_sys_no;
313     end if;
314     return c_sys_yes;
315   end check_row_exists;
316 
317   procedure flushRegsOrgsIntoMfq(p_plan_id number, p_region_type number, p_region_list number,
318     p_org_type number, p_org_list varchar2,
319     p_region_query_id out nocopy number, p_org_query_id out nocopy number) is
320 
321     cursor c_regions_cur(p_view_type number) is
322     select distinct
323       to_number(null) region_list_id,
324       to_char(null) region_list,
325       p_region_list region_id,
326       msc_sda_utils.getRegionName(p_region_list) region_code,
327       p_region_list sort_column
328     from dual
329     where p_region_type = c_reg_view
330       and p_view_type = 1
331     union all
332     select distinct
333       mpq.query_id region_list_id,
334       mpq.query_name region_list,
335       mpt.object_type region_id,
336       msc_sda_utils.getRegionName(mpt.object_type) region_code,
337       mpt.sequence_id sort_column
338     from msc_pq_types mpt,
339       msc_personal_queries mpq
340     where  mpq.query_id = p_region_list
341       and mpq.query_id  = mpt.query_id
342       and p_region_type = c_reg_list_view
343       and p_view_type = 1
344     order by 5;
345 
346 /*
347     --pabram..need to use supersession chain also to reduce inserting too many rows
348     cursor c_orgs_cur is
349     select md.zone_id region_id,
350       md.sr_instance_id inst_id,
351       md.organization_id org_id,
352       msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
353       md.inventory_item_id
354     from msc_demands md,
355       msc_form_query mfq
356     where mfq.query_id = p_region_query_id
357       and md.plan_id = p_plan_id
358       and md.zone_id = mfq.number2;
359 */
360     cursor c_orgs_cur (p_view_type number, p_inst_id number, p_org_id number) is
361     select distinct mfq.number2 region_id,
362       mtp.sr_instance_id inst_id,
363       mtp.sr_tp_id org_id,
364       msc_get_name.org_code(mpo.organization_id, mpo.sr_instance_id) org_code,
365       to_number(null) inventory_item_id
366     from
367       --msc_region_locations mrl,
368       --msc_location_associations mla,
369       msc_trading_partners mtp,
370       msc_plan_organizations mpo,
371       msc_form_query mfq
372       --,msc_zone_regions mzr
373     where mfq.query_id = p_region_query_id
374       and nvl(mfq.number2,-1) > 0
375       --and mzr.parent_region_id = mfq.number2
376       --and mrl.region_id = mzr.region_id
377       --and mrl.location_id = mla.location_id
378       --and mla.partner_id = mtp.partner_id
379       and mtp.partner_type = 3
380       and mpo.plan_id = p_plan_id
381       and mpo.sr_instance_id = mtp.sr_instance_id
382       and mpo.organization_id = mtp.sr_tp_id
383       and p_view_type = 1 --region list selected by user
384       --pabram..commented out --msc_location_associations, msc_trading_partners for testing,
385       --we need to enable this when these tables are flushed correctly
386       --6736491, need to add mrl back
387     union all
388     select distinct
389       c_mbp_not_null_value region_id,
390       mtp.sr_instance_id inst_id,
391       mtp.sr_tp_id org_id,
392       msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
393       to_number(null) inventory_item_id
394     from msc_trading_partners mtp
395     where mtp.sr_tp_id = p_org_id
396       and mtp.sr_instance_id = p_inst_id
397       and mtp.partner_type = 3
398       and p_org_type = c_org_view
399       and p_view_type = 2
400     union all
401     select distinct
402       c_mbp_not_null_value region_id,
403       mpt.source_type inst_id,
404       mpt.object_type org_id,
405       msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
406       to_number(null) inventory_item_id
407     from msc_pq_types mpt,
408       msc_personal_queries mpq
409     where mpq.query_id = p_org_list
410       and mpq.query_id  = mpt.query_id
411       and p_org_type = c_org_list_view
412       and p_view_type = 2
413     order by 4;
414 
415     ll_org_list_id number;
416     ll_org_list varchar2(250);
417     ll_inst_id number;
418     ll_org_id number;
419     ll_org_code varchar2(10);
420 
421     ll_view_type number;
422   begin
423     println('flushRegsOrgsIntoMfq in');
424     p_region_query_id := getNewFormQueryId;
425     p_org_query_id := getNewFormQueryId;
426 
427     if ( p_region_list is not null) then
428       ll_view_type := 1;
429     elsif (p_org_list is not null) then
430       msc_sda_utils.getOrgListValues(p_org_list, p_org_type, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code);
431       ll_view_type := 2;
432     end if;
433 
434     for c_regions in c_regions_cur(ll_view_type)
435     loop
436       println('regions loop in');
437       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
438         number1, char1, number2, char2, number3)
439       values (p_region_query_id, sysdate, -1, -1, sysdate,
440         c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
441 
442       --insert a global org for each region
443       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
444         number1, number2, number3, char1, number4)
445       values (p_org_query_id, sysdate, -1, -1, sysdate,
446         c_regions.region_id, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
447 
448       println('regions loop out');
449     end loop;
450 
451       --insert one global orgs for global without region_id
452       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
453         number1, number2, number3, char1, number4)
454       values (p_org_query_id, sysdate, -1, -1, sysdate,
455         c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
456 
457     --add global region
458       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
459         number1, char1, number2, char2, number3)
460       values (p_region_query_id, sysdate, -1, -1, sysdate,
461         to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
462 
463     --add local region
464       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
465         number1, char1, number2, char2, number3)
466       values (p_region_query_id, sysdate, -1, -1, sysdate,
467         to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
468 
469       --insert one global orgs for local with region_id for usage based forecast local
470       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
471         number1, number2, number3, char1, number4)
472       values (p_org_query_id, sysdate, -1, -1, sysdate,
473         c_local_reg_type, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
474 
475     for c_orgs in c_orgs_cur(ll_view_type, ll_inst_id, ll_org_id)
476     loop
477       println('region-orgs loop in');
478       --insert orgs for regions
479       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
480         number1, number2, number3, char1, number4)
481       values (p_org_query_id, sysdate, -1, -1, sysdate,
482         c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
483 
484       --insert orgs for global
485       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
486         number1, number2, number3, char1, number4)
487       values (p_org_query_id, sysdate, -1, -1, sysdate,
488         c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
489 
490       --insert orgs for local
491       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
492         number1, number2, number3, char1, number4)
493       values (p_org_query_id, sysdate, -1, -1, sysdate,
494         c_local_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
495     end loop;
496 
497     println(' p_org_query_id p_region_query_id '||p_org_query_id||' - '||p_region_query_id);
498     println('flushRegsOrgsIntoMfq out');
499   end flushRegsOrgsIntoMfq;
500 
501   function flushOrgsIntoMfq(p_query_id number, p_row_index number, p_org_type number) return number is
502     l_query_id number;
503 
504     cursor c_orgs_cur is
505     select distinct
506       to_number(null) org_list_id,
507       to_char(null) org_list,
508       mtp.sr_instance_id inst_id,
509       mtp.sr_tp_id org_id,
510       msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
511       mtp.sr_tp_id sort_column
512     from msc_trading_partners mtp,
513       msc_analysis_query maq
514     where maq.query_id = p_query_id
515       and maq.row_index = p_row_index
516       and mtp.sr_instance_id = maq.inst_id
517       and mtp.sr_tp_id = maq.org_id
518       and mtp.partner_type = 3
519       and p_org_type = c_org_view
520     union all
521     select distinct
522       mpq.query_id org_list_id,
523       mpq.query_name org_list,
524       mpt.source_type inst_id,
525       mpt.object_type org_id,
526       msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
527       mpt.sequence_id sort_column
528     from msc_pq_types mpt,
529       msc_personal_queries mpq,
530       msc_analysis_query maq
531     where maq.query_id = p_query_id
532       and maq.row_index = p_row_index
533       and mpq.query_id = maq.org_list_id
534       and mpq.query_id  = mpt.query_id
535       and p_org_type = c_org_list_view
536     order by 6;
537 
538   begin
539     l_query_id := getNewFormQueryId;
540     for c_orgs in c_orgs_cur
541     loop
542       println('inserting +');
543       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
544         number1, char1, number2, number3, char4, number4)
545       values (l_query_id, sysdate, -1, -1, sysdate,
546         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);
547     end loop;
548     return l_query_id;
549   end flushOrgsIntoMfq;
550 
551   function flushChainIntoMfq(p_query_id number, p_plan_id number,
552     p_item_view_type number, p_item_id number) return number is
553 
554    cursor c_sschain_cur is
555    select to_number(null) top_item_id,
556      to_char(null) top_item_name,
557      inventory_item_id item_id,
558      item_name,
559      1 sort_column
560    from msc_items
561    where inventory_item_id = p_item_id
562      and p_item_view_type = c_item_view
563    union all
564    select distinct
565      p_item_id top_item_id,
566      msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
567      decode(p_item_id, prime_item_id, lower_item_id, higher_item_id) item_id,
568      msc_get_name.item_name(decode(p_item_id, prime_item_id, lower_item_id, higher_item_id),null, null, null) item_name,
569      1 sort_column
570    from msc_item_substitutes
571    where plan_id = p_plan_id
572      and (prime_item_id = p_item_id or (higher_item_id = highest_item_id and higher_item_id = p_item_id))
573      and relationship_type = c_mis_supersession_type
574      and p_item_view_type = c_prime_view
575      and inferred_flag = 2
576      and forward_rule = 1
577 /*
578    start with prime_item_id = p_item_id
579         --and highest_item_id = higher_item_id
580       connect by nocycle higher_item_id = prior lower_item_id
581         and plan_id = prior plan_id
582         and relationship_type = prior relationship_type
583 	and prime_item_id = prior prime_item_id
584 	and inferred_flag = prior inferred_flag
585         and forward_rule = prior forward_rule
586 */ --commented since where cl is enough to fetch this info
587    union all
588    select distinct
589      highest_item_id top_item_id,
590      msc_get_name.item_name(highest_item_id,null, null, null) top_item_name,
591      lower_item_id item_id,
592      msc_get_name.item_name(lower_item_id,null, null, null) item_name,
593      1 sort_column
594    from msc_item_substitutes
595    where plan_id = p_plan_id
596      and relationship_type = c_mis_supersession_type
597      and p_item_view_type = c_supersession_view
598      and highest_item_id = p_item_id
599      and inferred_flag = 2
600      and forward_rule = 1
601 /*
602    start with highest_item_id = p_item_id
603      and inferred_flag = 2
604      and highest_item_id = higher_item_id
605      --and highest_item_id = higher_item_id
606       connect by nocycle higher_item_id = prior lower_item_id
607         and plan_id = prior plan_id
608         and relationship_type = prior relationship_type
609 	and inferred_flag = prior inferred_flag
610         and forward_rule = prior forward_rule
611 */ --commented since where cl is enough to fetch this info
612       order by sort_column desc;
613       --pabram..need to change.. need to add effective_date logic also
614 
615      l_query_id number;
616      l_found boolean := false;
617      l_item_name varchar2(300);
618   begin
619     l_query_id := getNewFormQueryId;
620     for c_sschain in c_sschain_cur
621     loop
622       println(' populating into chain '||c_sschain.item_name);
623       insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
624       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,
625       c_sschain.sort_column);
626       if (c_sschain.item_id = p_item_id) then
627         l_found := true;
628       end if;
629     end loop;
630 
631     if (p_item_view_type in (c_prime_view, c_supersession_view) ) then
632       if  (l_found = false) then
633         l_item_name := msc_get_name.item_name(p_item_id, null, null, null);
634         insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
635         values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
636       end if;
637     end if;
638     return l_query_id;
639   end flushChainIntoMfq;
640 
641 function createHistCalInMfq(p_start_date date, p_end_date date) return number is
642   l_query_id number;
643 
644   l_first_day date;
645   l_last_day date;
646 
647   l_month number;
648   l_year number;
649   l_date_index number := 1;
650 
651   l_start_date date;
652   l_end_date date;
653 begin
654   l_query_id := getNewFormQueryId;
655   l_start_date := trunc(p_start_date, 'MM');
656   l_end_date :=  trunc(p_end_date, 'MM');
657 
658   l_month := to_char(l_start_date, 'MM');
659   l_year := to_char(l_start_date, 'YYYY');
660   loop
661     l_first_day := to_date( '01/' || l_month || '/' ||l_year, 'DD/MM/YYYY');
662     l_last_day := last_day(l_first_day);
663 
664     insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
665       date1, date2, number1)
666     values (l_query_id, sysdate, -1, -1, sysdate, l_first_day, l_last_day, l_date_index);
667 
668     if (l_last_day > l_end_date) then
669       exit;
670     end if;
671     if (l_month <12) then
672       l_month := l_month + 1;
673     else
674       l_month := 1;
675       l_year := l_year + 1;
676     end if;
677     l_date_index := l_date_index + 1;
678   end loop;
679   return l_query_id;
680 end createHistCalInMfq;
681 
682   procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
683     cursor c_item_prompts_cur (p_folder_object varchar2) is
684     select field_type,
685       field_name,
686       field_prompt,
687       decode(folder_object,
688 	c_item_folder, nvl(group_by,2),
689 	1) default_flag
690     from msc_criteria
691     where folder_object = p_folder_object
692        and field_name <> 'PRE_POSITION_INVENTORY'
693        order by to_number(field_type);
694 
695     l_one_record varchar2(500);
696     l_row_count number := 0;
697     l_out_data_index number := 1;
698   begin
699     -- items column prompts
700     for c_item_prompts in c_item_prompts_cur(c_item_folder) loop
701       l_one_record := c_item_prompts.field_type
702         || c_field_seperator || c_item_prompts.field_name
703         || c_field_seperator || c_item_prompts.field_prompt
704         || c_field_seperator || c_item_prompts.default_flag;
705 
706       l_row_count := l_row_count + 1;
707       if (l_row_count = 1) then
708         l_one_record := c_sdview_items_messages || c_bang_separator || c_record_seperator || l_one_record;
709       end if;
710       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
711     end loop;
712 
713    l_row_count := 0;
714 
715     -- comments column prompts
716     for c_item_prompts in c_item_prompts_cur(c_comments_folder) loop
717       l_one_record := c_item_prompts.field_type
718         || c_field_seperator || c_item_prompts.field_name
719         || c_field_seperator || c_item_prompts.field_prompt
720         || c_field_seperator || c_item_prompts.default_flag;
721 
722       l_row_count := l_row_count + 1;
723       if (l_row_count = 1) then
724         l_one_record := c_sdview_comments_messages || c_bang_separator || c_record_seperator || l_one_record;
725       end if;
726       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
727     end loop;
728 
729    l_row_count := 0;
730 
731     -- excp_summary column prompts
732     for c_item_prompts in c_item_prompts_cur(c_excp_folder) loop
733       l_one_record := c_item_prompts.field_type
734         || c_field_seperator || c_item_prompts.field_name
735         || c_field_seperator || c_item_prompts.field_prompt
736         || c_field_seperator || c_item_prompts.default_flag;
737 
738       l_row_count := l_row_count + 1;
739       if (l_row_count = 1) then
740         l_one_record := c_sdview_excp_messages || c_bang_separator || c_record_seperator || l_one_record;
741       end if;
742       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
743     end loop;
744   end spreadTableMessages;
745 
746   procedure getCommentsData(p_plan_id number, p_chain_query_id number,
747     p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_stream_label varchar2) is
748 
749     cursor c_comments_cur is
750     select distinct mun.note_id,
751       nvl(mun.last_update_date, mun.creation_date) comment_date,
752       msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
753       substr(mun.note_text1,1,80) comment_text
754     from msc_user_notes mun,
755       msc_form_query mfq
756     where
757       mun.entity_type = c_comment_entity_type
758       and mun.inventory_item_id  in (mfq.number1, number2)
759       and mfq.query_id = p_chain_query_id
760       order by 2 desc;
761     --where mun.plan_id = p_plan_id
762 
763     l_one_record varchar2(32000);
764     l_out_data_index number := 1;
765     l_row_count number := 0;
766   begin
767     println('getCommentsData in');
768       for c_comments in c_comments_cur
769       loop
770          l_row_count := l_row_count + 1;
771          l_one_record := to_char(c_comments.note_id)
772 	    || c_field_seperator || to_char(c_comments.comment_date, c_date_format)
773 	    || c_field_seperator || msc_sda_utils.escapeSplChars(c_comments.comment_text)
774 	    || c_field_seperator || msc_sda_utils.escapeSplChars(c_comments.item_name);
775         if (l_row_count = 1) then
776           l_one_record := p_stream_label || c_bang_separator || c_record_seperator || l_one_record;
777 	end if;
778         msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
779       end loop;
780     println('getCommentsData out');
781   end getCommentsData;
782 
783 function getPreference(p_plan_id number, p_plan_type number, p_preference in varchar2) return varchar2 is
784   l_pref_value number;
785   l_def_pref_id number;
786 
787   cursor c_pref_id is
788   select preference_id
789   from msc_user_preferences
790   where default_flag =1
791   and user_id = fnd_global.user_id
792   and nvl(plan_type,-1) = p_plan_type;
793 
794  begin
795     open c_pref_id;
796     fetch c_pref_id into l_def_pref_id;
797     close c_pref_id;
798     if (l_def_pref_id is null) then
799       l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
800     end if;
801 
802     l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
803     return l_pref_value;
804   end getPreference;
805 
806 
807   procedure getItemsData(p_plan_id number, p_org_query_id number, p_chain_query_id number, p_out_data in out nocopy maxCharTbl) is
808     l_category_set_id number;
809 
810   cursor c_item_cur is
811   select distinct to_char(sr_instance_id)||'-'||to_char(organization_id)||'-'||to_char(inventory_item_id) node_id,
812     item_segments, organization_code, description, planner_code,
813     nettable_inventory_quantity, nonnettable_inventory_quantity, buyer_name,
814     mrp_planning_code_text, critical_component_flag critical_component_flag,
815     wip_supply_type_text,
816     bom_item_type_text, end_assembly_pegging_text, base_model,
817     category, category_desc, product_family_item, product_family_item_desc,
818     planning_exception_set, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(repetitive_type,2)) repetitive_type,
819     standard_cost, carrying_cost,
820     uom_code, planning_time_fence_date, planning_time_fence_days,
821     inventory_use_up_date, planning_make_buy_code_text,
822     ato_forecast_control_text, shrinkage_rate, preprocessing_lead_time,
823     full_lead_time, postprocessing_lead_time, leadtime_variability,
824     fixed_lead_time, variable_lead_time, fixed_order_quantity,
825     fixed_lot_multiplier, minimum_order_quantity, maximum_order_quantity,
826     safety_stock_days, safety_stock_percent, fixed_days_supply,
827     msc_get_name.lookup_meaning('SYS_YES_NO', rounding_control_type) rounding_control_type,
828     effectivity_control_type, abc_class_name, selling_price,
829     margin, average_discount, net_selling_price, service_level,
830     demand_time_fence_days, demand_time_fence_date, safety_stock_code,
831     atp_flag, atp_components_flag, drp_planned, weight_uom,
832     unit_weight, volume_uom, pip_flag, msc_get_name.lookup_meaning('SYS_YES_NO', create_supply_flag) create_supply_flag,
833     substitution_window,
834     convergence_text, divergence_text, continous_transfer_text, exclude_from_budget,
835     days_tgt_inv_window, days_max_inv_window, days_tgt_inv_supply,
836     days_max_inv_supply, shelf_life_days, release_time_fence_days,
837     min_shelf_life_days, unit_volume, to_number(null) max_early_days,
838    demand_fulfillment_lt, end_of_life_date, fcst_rule_for_demands_text,
839    fcst_rule_for_returns_text, interarrival_time, life_time_buy_date,
840    msc_get_name.lookup_meaning('SYS_YES_NO', decode(preposition_point,'Y','1','2')) preposition_point,
841    repair_cost,
842    repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
843    repetitive_planning_flag_text,
844     mfq.number3
845     from msc_system_items_sc_v msiv,
846       msc_form_query mfq,  --items
847       msc_form_query mfq1 --orgs
848     where plan_id = p_plan_id
849       and category_set_id = l_category_set_id
850       and inventory_item_id  in (mfq.number2) --, number1)
851       and mfq.query_id = p_chain_query_id
852       and mfq1.query_id = p_org_query_id
853       --and nvl(mfq1.number1,1) >0
854       and mfq1.number2 = msiv.sr_instance_id
855       and mfq1.number3 = msiv.organization_id
856     order by msiv.organization_code, mfq.number3;
857 
858 
859     l_one_record varchar2(32000);
860     l_out_data_index number := 1;
861     l_row_count number := 0;
862 
863   begin
864     println('getItemsData in');
865 
866     --6726798 bugfix
867     l_category_set_id := getPreference(p_plan_id, 8, 'CATEGORY_SET_ID');
868 
869     for c_item in c_item_cur loop
870 
871      l_one_record := c_item.node_id || c_field_seperator ||
872        msc_sda_utils.escapeSplChars(c_item.item_segments) || c_field_seperator ||
873        msc_sda_utils.escapeSplChars(c_item.organization_code) || c_field_seperator ||
874        msc_sda_utils.escapeSplChars(c_item.description) || c_field_seperator ||
875        msc_sda_utils.escapeSplChars(c_item.planner_code) || c_field_seperator ||
876        msc_sda_utils.escapeSplChars(c_item.nettable_inventory_quantity) || c_field_seperator ||
877        msc_sda_utils.escapeSplChars(c_item.nonnettable_inventory_quantity) || c_field_seperator ||
878        msc_sda_utils.escapeSplChars(c_item.buyer_name) || c_field_seperator ||
879        msc_sda_utils.escapeSplChars(c_item.mrp_planning_code_text) || c_field_seperator ||
880        msc_sda_utils.escapeSplChars(c_item.critical_component_flag) || c_field_seperator ||
881        msc_sda_utils.escapeSplChars(c_item.wip_supply_type_text) || c_field_seperator ||
882        msc_sda_utils.escapeSplChars(c_item.bom_item_type_text) || c_field_seperator ||
883        msc_sda_utils.escapeSplChars(c_item.end_assembly_pegging_text) || c_field_seperator ||
884        msc_sda_utils.escapeSplChars(c_item.base_model) || c_field_seperator ||
885        msc_sda_utils.escapeSplChars(c_item.category) || c_field_seperator ||
886        msc_sda_utils.escapeSplChars(c_item.category_desc) || c_field_seperator ||
887        msc_sda_utils.escapeSplChars(c_item.product_family_item) || c_field_seperator ||
888        msc_sda_utils.escapeSplChars(c_item.product_family_item_desc) || c_field_seperator ||
889        msc_sda_utils.escapeSplChars(c_item.planning_exception_set) || c_field_seperator ||
890        msc_sda_utils.escapeSplChars(c_item.repetitive_type) || c_field_seperator ||
891        msc_sda_utils.escapeSplChars(c_item.standard_cost) || c_field_seperator ||
892        msc_sda_utils.escapeSplChars(c_item.carrying_cost) || c_field_seperator ||
893        msc_sda_utils.escapeSplChars(c_item.uom_code) || c_field_seperator ||
894        msc_sda_utils.escapeSplChars(c_item.planning_time_fence_date) || c_field_seperator ||
895        msc_sda_utils.escapeSplChars(c_item.planning_time_fence_days) || c_field_seperator ||
896        msc_sda_utils.escapeSplChars(c_item.inventory_use_up_date) || c_field_seperator ||
897        msc_sda_utils.escapeSplChars(c_item.planning_make_buy_code_text) || c_field_seperator ||
898        msc_sda_utils.escapeSplChars(c_item.ato_forecast_control_text) || c_field_seperator ||
899        msc_sda_utils.escapeSplChars(c_item.shrinkage_rate) || c_field_seperator ||
900        msc_sda_utils.escapeSplChars(c_item.preprocessing_lead_time) || c_field_seperator ||
901        msc_sda_utils.escapeSplChars(c_item.full_lead_time) || c_field_seperator ||
902        msc_sda_utils.escapeSplChars(c_item.postprocessing_lead_time) || c_field_seperator ||
903        msc_sda_utils.escapeSplChars(c_item.leadtime_variability) || c_field_seperator ||
904        msc_sda_utils.escapeSplChars(c_item.fixed_lead_time) || c_field_seperator ||
905        msc_sda_utils.escapeSplChars(c_item.variable_lead_time) || c_field_seperator ||
906        msc_sda_utils.escapeSplChars(c_item.fixed_order_quantity) || c_field_seperator ||
907        msc_sda_utils.escapeSplChars(c_item.fixed_lot_multiplier) || c_field_seperator ||
908        msc_sda_utils.escapeSplChars(c_item.minimum_order_quantity) || c_field_seperator ||
909        msc_sda_utils.escapeSplChars(c_item.maximum_order_quantity) || c_field_seperator ||
910        msc_sda_utils.escapeSplChars(c_item.safety_stock_days) || c_field_seperator ||
911        msc_sda_utils.escapeSplChars(c_item.safety_stock_percent) || c_field_seperator ||
912        msc_sda_utils.escapeSplChars(c_item.fixed_days_supply) || c_field_seperator ||
913        msc_sda_utils.escapeSplChars(c_item.rounding_control_type) || c_field_seperator ||
914        msc_sda_utils.escapeSplChars(c_item.effectivity_control_type) || c_field_seperator ||
915        msc_sda_utils.escapeSplChars(c_item.abc_class_name) || c_field_seperator ||
916        msc_sda_utils.escapeSplChars(c_item.selling_price) || c_field_seperator ||
917        msc_sda_utils.escapeSplChars(c_item.margin) || c_field_seperator ||
918        msc_sda_utils.escapeSplChars(c_item.average_discount) || c_field_seperator ||
919        msc_sda_utils.escapeSplChars(c_item.net_selling_price) || c_field_seperator ||
920        msc_sda_utils.escapeSplChars(c_item.service_level) || c_field_seperator ||
921        msc_sda_utils.escapeSplChars(c_item.demand_time_fence_days) || c_field_seperator ||
922        msc_sda_utils.escapeSplChars(c_item.demand_time_fence_date) || c_field_seperator ||
923        msc_sda_utils.escapeSplChars(c_item.safety_stock_code) || c_field_seperator ||
924        msc_sda_utils.escapeSplChars(c_item.atp_flag) || c_field_seperator ||
925        msc_sda_utils.escapeSplChars(c_item.atp_components_flag) || c_field_seperator ||
926        msc_sda_utils.escapeSplChars(c_item.drp_planned) || c_field_seperator ||
927        msc_sda_utils.escapeSplChars(c_item.weight_uom) || c_field_seperator ||
928        msc_sda_utils.escapeSplChars(c_item.unit_weight) || c_field_seperator ||
929        msc_sda_utils.escapeSplChars(c_item.volume_uom) || c_field_seperator ||
930        msc_sda_utils.escapeSplChars(c_item.pip_flag) || c_field_seperator ||
931        msc_sda_utils.escapeSplChars(c_item.create_supply_flag) || c_field_seperator ||
932        msc_sda_utils.escapeSplChars(c_item.substitution_window) || c_field_seperator ||
933        msc_sda_utils.escapeSplChars(c_item.convergence_text) || c_field_seperator ||
934        msc_sda_utils.escapeSplChars(c_item.divergence_text) || c_field_seperator ||
935        msc_sda_utils.escapeSplChars(c_item.continous_transfer_text) || c_field_seperator ||
936        msc_sda_utils.escapeSplChars(c_item.exclude_from_budget) || c_field_seperator ||
937        msc_sda_utils.escapeSplChars(c_item.days_tgt_inv_window) || c_field_seperator ||
938        msc_sda_utils.escapeSplChars(c_item.days_max_inv_window) || c_field_seperator ||
939        msc_sda_utils.escapeSplChars(c_item.days_tgt_inv_supply) || c_field_seperator ||
940        msc_sda_utils.escapeSplChars(c_item.days_max_inv_supply) || c_field_seperator ||
941        msc_sda_utils.escapeSplChars(c_item.shelf_life_days) || c_field_seperator ||
942        msc_sda_utils.escapeSplChars(c_item.release_time_fence_days) || c_field_seperator ||
943        msc_sda_utils.escapeSplChars(c_item.min_shelf_life_days) || c_field_seperator ||
944        msc_sda_utils.escapeSplChars(c_item.unit_volume) || c_field_seperator ||
945        msc_sda_utils.escapeSplChars(c_item.max_early_days) || c_field_seperator ||
946        msc_sda_utils.escapeSplChars(c_item.demand_fulfillment_lt) || c_field_seperator ||
947        msc_sda_utils.escapeSplChars(to_char(c_item.end_of_life_date, c_datetime_format)) || c_field_seperator ||
948        msc_sda_utils.escapeSplChars(c_item.fcst_rule_for_demands_text) || c_field_seperator ||
949        msc_sda_utils.escapeSplChars(c_item.fcst_rule_for_returns_text) || c_field_seperator ||
950        msc_sda_utils.escapeSplChars(c_item.interarrival_time) || c_field_seperator ||
951        msc_sda_utils.escapeSplChars(to_char(c_item.life_time_buy_date, c_datetime_format)) || c_field_seperator ||
952        msc_sda_utils.escapeSplChars(c_item.preposition_point) || c_field_seperator ||
953        msc_sda_utils.escapeSplChars(c_item.repair_cost) || c_field_seperator ||
954        msc_sda_utils.escapeSplChars(c_item.repair_lead_time) || c_field_seperator ||
955        msc_sda_utils.escapeSplChars(c_item.repair_program_text) || c_field_seperator ||
956        msc_sda_utils.escapeSplChars(c_item.repair_yield) || c_field_seperator ||
957        msc_sda_utils.escapeSplChars(c_item.std_dmd_over_horizon) || c_field_seperator ||
958        msc_sda_utils.escapeSplChars(c_item.repetitive_planning_flag_text);
959 
960        l_row_count := l_row_count + 1;
961 
962        if (l_row_count = 1) then
963          l_one_record := c_sdview_items_data || c_bang_separator || c_record_seperator || l_one_record;
964        end if;
965        msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
966     end loop;
967     println('getItemsData out');
968   end getItemsData;
969 
970   procedure getExceptionsData(p_plan_id number, p_chain_query_id number, p_org_query_id number,
971     p_out_data in out nocopy maxCharTbl) is
972 
973   cursor c_excp_summary_cur is
974   select med.exception_type,
975     msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
976     count(*) excp_all_count,
977     sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
978   from msc_exception_details med,
979     msc_form_query mfq, --items
980     msc_form_query mfq1 --orgs
981   where med.plan_id = p_plan_id
982     and med.inventory_item_id = mfq.number2
983     and mfq.query_id = p_chain_query_id
984     and mfq1.query_id = p_org_query_id
985     and nvl(mfq1.number1,1) >0
986     and mfq1.number2 = med.sr_instance_id
987     and mfq1.number3 = med.organization_id
988   group by med.exception_type,
989     msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
990    union all
991    select -99 exception_type,
992     msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
993     count(*) excp_all_count,
994     0 excp_no_count
995   from msc_supplies ms,
996     msc_system_items msi,
997     msc_form_query mfq, --items
998     msc_form_query mfq1 --orgs
999   where ms.plan_id = p_plan_id
1000     and ms.inventory_item_id = mfq.number2
1001     and mfq.query_id = p_chain_query_id
1002     and mfq1.query_id = p_org_query_id
1003     and nvl(mfq1.number1,1) >0
1004     and mfq1.number2 = ms.sr_instance_id
1005     and mfq1.number3 = ms.organization_id
1006    and ms.plan_id = msi.plan_id
1007    and ms.sr_instance_id = msi.sr_instance_id
1008    and ms.organization_id = msi.organization_id
1009    and ms.inventory_item_id = msi.inventory_item_id
1010    and ( (ms.order_type = 13)
1011            or (ms.order_type = 5
1012 	           and nvl(ms.implemented_quantity,0)+nvl(ms.quantity_in_process,0) < nvl(ms.firm_quantity,ms.new_order_quantity)
1013 	     and (nvl(msi.lots_exist,0) <> 2 or ms.new_order_quantity =0)
1014 	     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)
1015 			   and msi.purchasing_enabled_flag = 1)
1016 	    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)
1017 	    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)
1018 	    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))
1019 	    )
1020 	  )
1021   group by -99,
1022     msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10);
1023   --pabram.need to add recommendations
1024 
1025     l_one_record varchar2(32000);
1026     l_out_data_index number := 1;
1027     l_row_count number := 0;
1028   begin
1029     println('getExceptionsData in');
1030     for c_excp_summary in c_excp_summary_cur loop
1031       l_one_record :=
1032         msc_sda_utils.escapeSplChars(p_chain_query_id||'-'||c_excp_summary.exception_type) || c_field_seperator ||
1033        msc_sda_utils.escapeSplChars(c_excp_summary.exception_type_text) || c_field_seperator ||
1034        msc_sda_utils.escapeSplChars(c_excp_summary.excp_all_count) || c_field_seperator ||
1035        msc_sda_utils.escapeSplChars(c_excp_summary.excp_no_count);
1036 
1037        l_row_count := l_row_count + 1;
1038        if (l_row_count = 1) then
1039          l_one_record := c_sdview_excp_data || c_bang_separator || c_record_seperator || l_one_record;
1040        end if;
1041        msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1042     end loop;
1043     println('getExceptionsData out');
1044   end getExceptionsData;
1045 
1046   procedure getWorkSheetPrefData(p_out_data in out nocopy maxCharTbl, p_refresh_flag number) is
1047   cursor c_userpref_cur is
1048   select name, key, nvl(value, c_null_space) value
1049   from msc_analyze_preference
1050   where module = c_sda_pref_set
1051     and userid= fnd_global.user_id
1052   order by name, key;
1053 
1054     l_one_record varchar2(32000);
1055     l_out_data_index number := 1;
1056     l_row_count number := 0;
1057     l_stream_label varchar2(200);
1058   begin
1059     println('getWorkSheetPrefData in');
1060 
1061     if (p_refresh_flag = 1) then
1062       l_stream_label := c_sdview_prefset_data_ref;
1063     else
1064       l_stream_label := c_sdview_prefset_data;
1065     end if;
1066     for c_userpref in c_userpref_cur
1067     loop
1068       l_one_record :=
1069         msc_sda_utils.escapeSplChars(c_userpref.name) || c_field_seperator ||
1070        msc_sda_utils.escapeSplChars(c_userpref.key) || c_field_seperator ||
1071        msc_sda_utils.escapeSplChars(c_userpref.value);
1072 
1073        l_row_count := l_row_count + 1;
1074        if (l_row_count = 1) then
1075          l_one_record := l_stream_label || c_bang_separator || c_record_seperator || l_one_record;
1076        end if;
1077        msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1078     end loop;
1079     println('getWorkSheetPrefData out');
1080   end getWorkSheetPrefData;
1081 
1082   procedure sendSDRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1083     cursor c_sd_rowtypes_cur is
1084     select lookup_code, meaning
1085     from mfg_lookups
1086     where lookup_type = c_sdview_rowtype_lookup
1087     order by 1;
1088     l_one_record varchar2(32000) := null;
1089     l_token varchar2(1000);
1090     l_out_data_index number := 1;
1091     l_flag number; -- 0 or null - sum, 1 - avg, 2- use-last-record-in-bucket, 3 show min
1092     l_visible_flag number; -- 0 hide, 1 show
1093     l_total_row_flag number; -- 1 yes, 2 no
1094   begin
1095       for c_sd_rowtypes in c_sd_rowtypes_cur
1096       loop
1097          l_flag := 0;
1098 	 l_visible_flag := 1;
1099 	 l_total_row_flag := 2;
1100 
1101 	 if (c_sd_rowtypes.lookup_code in (45) ) then
1102 	   --safety stock qty
1103            l_flag := 1;
1104          end if;
1105 	 if (c_sd_rowtypes.lookup_code in (31,32,44) ) then
1106            --31 Projected Available Balance
1107            --32 Projected On-hand
1108            --44 Projected Available Balance (Defective)
1109            l_flag := 2;
1110          end if;
1111 	 if (c_sd_rowtypes.lookup_code in (26,28,29) ) then
1112 	   --26 Safety Stock (Days of Supply)
1113 	   --28 Projected Service Level
1114 	   --29 Target Service Level
1115            l_flag := 3;
1116          end if;
1117 	 if (c_sd_rowtypes.lookup_code in (24,26,27,28,29,30) ) then
1118            --24 Planned Warranty Orders
1119            --26 Safety Stock (Days of Supply)
1120            --27 Total Unconstrained Demand
1121            --28 Projected Service Level (%)
1122            --29 Target Service Level (%)
1123            --30 Maximum Level
1124 	   l_visible_flag := 0;
1125 	 end if;
1126 
1127 	 if (c_sd_rowtypes.lookup_code in (4,7,8,9,25,27,36,43) ) then
1128            --4 Independent Demand
1129            --7 Dependent Demand
1130            --8 Other Demand
1131            --9 Total Demand
1132            --25 Total Supply
1133            --27 Total Unconstrained Demand
1134            --36 Total Defective Part Demand
1135            --43 Total Defective Supply
1136 	   l_total_row_flag := 1;
1137 	 end if;
1138 
1139          l_token := c_sd_rowtypes.lookup_code
1140 	    || c_field_seperator || msc_sda_utils.escapeSplChars(c_sd_rowtypes.meaning)
1141 	    || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1142         if (l_one_record is null) then
1143           l_one_record := c_sdview_rowtypes || c_bang_separator || c_sd_total_row_types || c_record_seperator || l_token;
1144 	else
1145           l_one_record := l_one_record || c_record_seperator || l_token;
1146 	end if;
1147       end loop;
1148       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1149   end sendSDRowTypes;
1150 
1151   procedure sendFcstRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1152     cursor c_fcst_rowtypes_cur is
1153     select lookup_code, meaning
1154     from mfg_lookups
1155     where lookup_type = c_fcstview_rowtype_lookup
1156     order by 1;
1157     l_one_record varchar2(32000) := null;
1158     l_token varchar2(1000);
1159     l_out_data_index number := 1;
1160     l_flag number; -- 0 - sum, 1 - avg, 2- use-last-record-in-bucket
1161     l_visible_flag number; -- 0 hide, 1 show
1162     l_total_row_flag number; -- 1 yes, 2 no
1163   begin
1164       for c_fcst_rowtypes in c_fcst_rowtypes_cur
1165       loop
1166          l_flag := 0;
1167          l_visible_flag := 1;
1168 	 l_total_row_flag := 2;
1169 
1170 	 if (c_fcst_rowtypes.lookup_code in (8,13,16)) then
1171            --8 Best Fit forecast
1172 	   --13 Returns Best Fit forecast
1173            l_visible_flag := 0;
1174 	 end if;
1175 	 --6657610 bugfix,
1176 
1177 	 if (c_fcst_rowtypes.lookup_code in (1,9)) then
1178            --1 Total Forecast
1179            --9 Total Returns Forecast
1180            l_total_row_flag := 1;
1181 	 end if;
1182 
1183          l_token := c_fcst_rowtypes.lookup_code
1184 	    || c_field_seperator || msc_sda_utils.escapeSplChars(c_fcst_rowtypes.meaning)
1185 	    || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1186         if (l_one_record is null) then
1187           l_one_record := c_fcstview_rowtypes || c_bang_separator || c_fcst_total_row_types || c_record_seperator || l_token;
1188 	else
1189           l_one_record := l_one_record || c_record_seperator || l_token;
1190 	end if;
1191       end loop;
1192       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1193   end sendFcstRowTypes;
1194 
1195   procedure sendHistRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1196     cursor c_hist_rowtypes_cur is
1197     select lookup_code, meaning
1198     from mfg_lookups
1199     where lookup_type = c_histview_rowtype_lookup
1200     order by 1;
1201     l_one_record varchar2(32000) := null;
1202     l_token varchar2(1000);
1203     l_out_data_index number := 1;
1204     l_flag number; -- 0 - sum, 1 - avg, 2- use-last-record-in-bucket
1205     l_visible_flag number; -- 0 hide, 1 show
1206     l_total_row_flag number; -- 1 yes, 2 no
1207   begin
1208       for c_hist_rowtypes in c_hist_rowtypes_cur
1209       loop
1210          l_flag := 0;
1211          l_visible_flag := 1;
1212 	 l_total_row_flag := 2;
1213          l_token := c_hist_rowtypes.lookup_code
1214 	    || c_field_seperator || msc_sda_utils.escapeSplChars(c_hist_rowtypes.meaning)
1215 	    || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1216         if (l_one_record is null) then
1217           l_one_record := c_histview_rowtypes || c_bang_separator || c_hist_total_row_types || c_record_seperator || l_token;
1218 	else
1219           l_one_record := l_one_record || c_record_seperator || l_token;
1220 	end if;
1221       end loop;
1222       msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1223   end sendHistRowTypes;
1224 
1225   function getTokenizedMsg(p_msg varchar2) return varchar2 is
1226     l_msg_text varchar2(300);
1227   begin
1228     FND_MESSAGE.set_name('MSC', p_msg);
1229     l_msg_text:= FND_MESSAGE.get;
1230     if (l_msg_text is null) then
1231       l_msg_text:= p_msg|| c_field_seperator || p_msg;
1232     else
1233       l_msg_text:= p_msg|| c_field_seperator || msc_sda_utils.escapeSplChars(l_msg_text);
1234     end if;
1235     return l_msg_text;
1236   end getTokenizedMsg;
1237 
1238  procedure addMessages(p_msg varchar2, p_out_data_index in out nocopy number,
1239    p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1240     l_one_record varchar2(1200) := null;
1241     l_token varchar2(1000);
1242  begin
1243    l_one_record := getTokenizedMsg(p_msg);
1244    msc_sda_utils.addRecordToOutStream(l_one_record, p_out_data_index, p_out_data);
1245  end addMessages;
1246 
1247   procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1248     l_one_record varchar2(32000) := null;
1249     l_token varchar2(1000);
1250     l_out_data_index number := 1;
1251   begin
1252 	 l_token := getTokenizedMsg('MENU_ORDER_DETAILS');
1253          l_one_record := c_sdview_nls_messages || c_bang_separator || c_record_seperator || l_token;
1254          msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1255 
1256 	 addMessages('MENU_FORECAST_RULE', l_out_data_index, p_out_data);
1257 	 addMessages('MENU_CALENDAR', l_out_data_index, p_out_data);
1258  	 addMessages('MENU_SOURCES', l_out_data_index, p_out_data);
1259  	 addMessages('MENU_SUPPLY_CHAIN_BILL', l_out_data_index, p_out_data);
1260  	 addMessages('MENU_DESTINATION', l_out_data_index, p_out_data);
1261  	 addMessages('MENU_SUPPLY_CHAIN', l_out_data_index, p_out_data);
1262 	 addMessages('PROMPT_ADD_NEW_COMMENT', l_out_data_index, p_out_data);
1263 	 addMessages('MENU_EXCP_ALL', l_out_data_index, p_out_data);
1264 	 addMessages('MENU_EXCP_ACTION_TAKEN', l_out_data_index, p_out_data);
1265 	 addMessages('MENU_EXCP_NO_ACTION', l_out_data_index, p_out_data);
1266 	 addMessages('LABEL_WORKSHEET_PREF', l_out_data_index, p_out_data);
1267 	 addMessages('DLG_TITLE_GRAPH', l_out_data_index, p_out_data);
1268 	 addMessages('DLG_MSG_BAD_GRID_SEL', l_out_data_index, p_out_data);
1269 	 addMessages('MENU_HIDE_COLUMN', l_out_data_index, p_out_data);
1270 	 addMessages('MENU_CHOOSE_COLUMNS', l_out_data_index, p_out_data);
1271 	 addMessages('DLG_TITLE_CHOOSE_COLUMNS', l_out_data_index, p_out_data);
1272 	 addMessages('BTN_LABEL_OK', l_out_data_index, p_out_data);
1273 	 addMessages('BTN_LABEL_CANCEL', l_out_data_index, p_out_data);
1274 	 addMessages('LBL_NOT_ENOUGH_DATA', l_out_data_index, p_out_data);
1275 	 addMessages('GRAPH_ROW_TYPE_PREF', l_out_data_index, p_out_data);
1276 	 addMessages('GRAPH_SEL_ROWTYPES', l_out_data_index, p_out_data);
1277 	 addMessages('GRAPH_AVAIL_ROWTYPES', l_out_data_index, p_out_data);
1278 	 addMessages('GRAPH_BTN_LABEL_REFRESH', l_out_data_index, p_out_data);
1279 	 addMessages('GRAPH_TIME_INTERVAL', l_out_data_index, p_out_data);
1280 	 addMessages('GRAPH_ITEMS', l_out_data_index, p_out_data);
1281 	 addMessages('GRAPH_CHART_TYPE', l_out_data_index, p_out_data);
1282 	 addMessages('GRAPH_BAR', l_out_data_index, p_out_data);
1283 	 addMessages('GRAPH_LINE', l_out_data_index, p_out_data);
1284 	 addMessages('GRAPH_COMBO', l_out_data_index, p_out_data);
1285 	 addMessages('GRAPH_LEGEND', l_out_data_index, p_out_data);
1286 	 addMessages('GRAPH_LABEL_SHOW', l_out_data_index, p_out_data);
1287 	 addMessages('GRAPH_LABEL_HIDE', l_out_data_index, p_out_data);
1288 	 addMessages('GRAPH_PREF', l_out_data_index, p_out_data);
1289 	 addMessages('GRID_LABEL_PAST', l_out_data_index, p_out_data);
1290 	 addMessages('MSC_FORECAST_RULE_RETURNS', l_out_data_index, p_out_data);
1291 	 addMessages('MSC_ITEM_FAILURE_RATES', l_out_data_index, p_out_data);
1292 	 addMessages('PROMPT_NO_TABLEDATA_ROWS', l_out_data_index, p_out_data);
1293 	 addMessages('GRAPH_BTN_LABEL_CLOSE', l_out_data_index, p_out_data);
1294 	 addMessages('SDA_SAVE_FOLDER', l_out_data_index, p_out_data);
1295 	 addMessages('MSC_EC_SAVE_SETTINGS', l_out_data_index, p_out_data);
1296   end sendNlsMessages;
1297 
1298   procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
1299     p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
1300 
1301     TYPE lCurTyp IS REF CURSOR;
1302     theCursor lCurTyp;
1303 
1304     l_token varchar2(500);
1305     l_one_record varchar2(32000);
1306 
1307     l_sql_stmt varchar2(500);
1308     l_sql_stmt1 varchar2(200);
1309     l_sql_stmt2 varchar2(200);
1310     l_sql_stmt3 varchar2(200);
1311     l_sql_stmt4 varchar2(200);
1312     l_lookup_code number;
1313     l_meaning varchar2(250);
1314   begin
1315     l_sql_stmt1 := 'select lookup_code, meaning from mfg_lookups where lookup_type = :1 ';
1316     l_sql_stmt4 := ' and lookup_code not in (8,13,16) ';
1317     l_sql_stmt2 := 'and lookup_code not in ('|| p_lookup_code_list ||') order by 1';
1318     l_sql_stmt3 := 'and lookup_code in ('|| p_lookup_code_list ||') order by 1';
1319 
1320     if (p_lookup_type = c_fcstview_rowtype_lookup) then
1321       l_sql_stmt := l_sql_stmt1||l_sql_stmt4||l_sql_stmt2;
1322     else
1323       l_sql_stmt := l_sql_stmt1||l_sql_stmt2;
1324     end if;
1325     open theCursor for l_sql_stmt using p_lookup_type;
1326     loop
1327       fetch theCursor into l_lookup_code, l_meaning;
1328       exit when theCursor%notfound;
1329       l_token := l_lookup_code || c_field_seperator || msc_sda_utils.escapeSplChars(l_meaning);
1330         if (l_one_record is null) then
1331           l_one_record := SET_FROM_LIST || c_bang_separator || l_token;
1332 	else
1333           l_one_record := l_one_record || c_record_seperator || l_token;
1334 	end if;
1335     end loop;
1336     close theCursor;
1337     p_from_list := l_one_record;
1338 
1339     l_one_record := null;
1340     if (p_lookup_type = c_fcstview_rowtype_lookup) then
1341       l_sql_stmt := l_sql_stmt1||l_sql_stmt4||l_sql_stmt3;
1342     else
1343       l_sql_stmt := l_sql_stmt1||l_sql_stmt3;
1344     end if;
1345     open theCursor for l_sql_stmt using p_lookup_type;
1346     loop
1347       fetch theCursor into l_lookup_code, l_meaning;
1348       exit when theCursor%notfound;
1349       l_token := l_lookup_code || c_field_seperator || msc_sda_utils.escapeSplChars(l_meaning);
1350         if (l_one_record is null) then
1351           l_one_record := SET_TO_LIST || c_bang_separator || l_token;
1352 	else
1353           l_one_record := l_one_record || c_record_seperator || l_token;
1354 	end if;
1355     end loop;
1356     close theCursor;
1357     p_to_list := l_one_record;
1358   end set_shuttle_from_to;
1359 
1360   procedure save_item_folder(p_folder_name varchar, p_folder_value varchar, p_default_flag number, p_public_flag number) is
1361     pragma autonomous_transaction;
1362     cursor c_count (p_module_name varchar2, p_name varchar) is
1363     select count(*)
1364     from msc_analyze_preference
1365     where module = p_module_name
1366       and name = p_name;
1367 
1368     l_temp number;
1369     l_default_flag number := nvl(p_default_flag,2);
1370     l_public_flag number := nvl(p_public_flag,2);
1371   begin
1372       commit;
1373 
1374       open c_count(c_sda_save_item_folder, p_folder_name);
1375       fetch c_count into l_temp;
1376       close c_count;
1377 
1378       if (l_temp <> 0) then
1379         update msc_analyze_preference
1380 	set defaultset = to_char(l_default_flag),
1381 	  public_flag = l_public_flag,
1382 	  value = p_folder_value
1383 	where name = p_folder_name
1384 	  and module = c_sda_save_item_folder;
1385       else
1386         insert into msc_analyze_preference
1387         (userid, name, module, key, value,  defaultset, public_flag,
1388           last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1389         values (fnd_global.user_id, p_folder_name, c_sda_save_item_folder, c_sda_save_item_folder, p_folder_value,
1390 	  to_char(l_default_flag), l_public_flag,
1391           sysdate, -1, sysdate, -1, -1);
1392       end if;
1393       commit;
1394   end save_item_folder;
1395 
1396   procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
1397     pragma autonomous_transaction;
1398 
1399     cursor c_count (p_module_name varchar2) is
1400     select count(*)
1401     from msc_analyze_preference
1402     where module = p_module_name
1403       and userid = fnd_global.user_id;
1404     l_temp number;
1405 
1406   begin
1407     commit;
1408 /*
1409     if (p_event = c_sda_save_item_folder) then
1410       open c_count(c_sda_save_item_folder);
1411       fetch c_count into l_temp;
1412       close c_count;
1413 
1414       if (l_temp <> 0) then
1415         delete from msc_analyze_preference
1416         where module = c_sda_save_item_folder
1417           and userid = fnd_global.user_id;
1418       end if;
1419 
1420       insert into msc_analyze_preference
1421         (userid, name, module, key, value,  defaultset,
1422         last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1423       values (fnd_global.user_id, c_sda_save_item_folder, c_sda_save_item_folder, c_sda_save_item_folder, p_event_list, 'N',
1424         sysdate, -1, sysdate, -1, -1);
1425     end if;
1426 */
1427 
1428     if (p_event = c_sda_save_settings) then
1429       open c_count(c_sda_save_settings);
1430       fetch c_count into l_temp;
1431       close c_count;
1432 
1433       if (l_temp <> 0) then
1434         delete from msc_analyze_preference
1435         where module = c_sda_save_settings
1436           and userid = fnd_global.user_id;
1437       end if;
1438 
1439       insert into msc_analyze_preference
1440         (userid, name, module, key, value,  defaultset,
1441         last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1442       values (fnd_global.user_id, c_sda_save_settings, c_sda_save_settings, c_sda_save_settings, p_event_list, 'N',
1443         sysdate, -1, sysdate, -1, -1);
1444     end if;
1445     commit;
1446   end update_close_settings;
1447 
1448   procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
1449     p_save_settings_list out nocopy varchar2) is
1450 
1451     cursor c_pref (p_module_name varchar2) is
1452     select value
1453     from msc_analyze_preference
1454     where module = p_module_name
1455       and userid = fnd_global.user_id;
1456   begin
1457     open c_pref(c_sda_save_item_folder);
1458     fetch c_pref into p_item_folder_save_list;
1459     close c_pref;
1460     if (p_item_folder_save_list is not null) then
1461       p_item_folder_save_list := c_sda_save_item_folder  || c_bang_separator || c_record_seperator || p_item_folder_save_list;
1462     end if;
1463 
1464     open c_pref(c_sda_save_settings);
1465     fetch c_pref into p_save_settings_list;
1466     close c_pref;
1467     if (p_save_settings_list is not null) then
1468       p_save_settings_list := c_sda_save_settings  || c_bang_separator || p_save_settings_list;
1469     end if;
1470   end send_close_settings;
1471 
1472   procedure update_pref_set (p_name varchar2, p_desc varchar2,
1473     p_days number, p_weeks number, p_periods number,
1474     p_factor number, p_decimal_places number,
1475     p_sd_row_list varchar2, p_fcst_row_list varchar2) is
1476     pragma autonomous_transaction;
1477 
1478     cursor c_count is
1479     select count(*)
1480     from msc_analyze_preference
1481     where module = c_sda_pref_set
1482       and name = p_name;
1483     l_temp number;
1484   begin
1485       commit;
1486       open c_count;
1487       fetch c_count into l_temp;
1488       close c_count;
1489 
1490       if (l_temp = 0) then
1491 	insert into msc_analyze_preference
1492 	(userid, name, module, key, value,  defaultset,
1493 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1494 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_days, p_days, 'N',
1495 	  sysdate, -1, sysdate, -1, -1);
1496 
1497 	insert into msc_analyze_preference
1498 	(userid, name, module, key, value,  defaultset,
1499 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1500 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_weeks, p_weeks, 'N',
1501 	  sysdate, -1, sysdate, -1, -1);
1502 
1503 	insert into msc_analyze_preference
1504 	(userid, name, module, key, value,  defaultset,
1505 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1506 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_periods, p_periods, 'N',
1507 	  sysdate, -1, sysdate, -1, -1);
1508 
1509 	insert into msc_analyze_preference
1510 	(userid, name, module, key, value,  defaultset,
1511 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1512 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_factor, p_factor, 'N',
1513 	  sysdate, -1, sysdate, -1, -1);
1514 
1515 	insert into msc_analyze_preference
1516 	(userid, name, module, key, value,  defaultset,
1517 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1518 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_decimals, p_decimal_places, 'N',
1519 	  sysdate, -1, sysdate, -1, -1);
1520 
1521 	insert into msc_analyze_preference
1522 	(userid, name, module, key, value,  defaultset,
1523 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1524 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_sd, p_sd_row_list, 'N',
1525 	  sysdate, -1, sysdate, -1, -1);
1526 
1527 	insert into msc_analyze_preference
1528 	(userid, name, module, key, value,  defaultset,
1529 	  last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1530 	values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_fcst, p_fcst_row_list, 'N',
1531 	  sysdate, -1, sysdate, -1, -1);
1532       else
1533         update msc_analyze_preference
1534           set value = p_days
1535         where module = c_sda_pref_set and name = p_name and key = c_keys_days;
1536 
1537         update msc_analyze_preference
1538           set value = p_weeks
1539         where module = c_sda_pref_set and name = p_name and key = c_keys_weeks;
1540 
1541         update msc_analyze_preference
1542           set value = p_periods
1543         where module = c_sda_pref_set and name = p_name and key = c_keys_periods;
1544 
1545         update msc_analyze_preference
1546           set value = p_factor
1547         where module = c_sda_pref_set and name = p_name and key = c_keys_factor;
1548 
1549         update msc_analyze_preference
1550           set value = p_decimal_places
1551         where module = c_sda_pref_set and name = p_name and key = c_keys_decimals;
1552 
1553         update msc_analyze_preference
1554           set value = p_sd_row_list
1555         where module = c_sda_pref_set and name = p_name and key = c_keys_sd;
1556 
1557         update msc_analyze_preference
1558           set value = p_fcst_row_list
1559         where module = c_sda_pref_set and name = p_name and key = c_keys_fcst;
1560       end if;
1561       commit;
1562   end update_pref_set;
1563 
1564 end MSC_SDA_UTILS;