[Home] [Help]
PACKAGE BODY: APPS.MSC_SDA_UTILS
Source
1 package body MSC_SDA_UTILS as
2 /* $Header: MSCSDAUB.pls 120.55 2010/12/17 20:36:08 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 flushSupersessionChainNew(p_plan number, p_item number) return number is
127 l_query_id number;
128 l_level number := 1;
129
130 cursor c_curr_level_cur (ll_query_id number, ll_level number) is
131 select
132 number1,
133 number2,
134 date1,
135 date2
136 from msc_form_query
137 where query_id = ll_query_id
138 and number4 = ll_level;
139
140 cursor c_chain_cur(ll_query_id number) is
141 select
142 number1,
143 number2,
144 date1,
145 date2
146 from msc_form_query
147 where query_id = ll_query_id;
148
149 cursor c_ods_data_cur(l_number1 number, l_number2 number) is
150 select b.effective_date,
151 b.disable_date
152 from msc_item_substitutes b, msc_plans mp
153 where b.plan_id = -1
154 and b.relationship_type = 8
155 and b.lower_item_id = l_number1
156 and b.higher_item_id = l_number2
157 and mp.plan_id = p_plan
158 and b.sr_instance_id = mp.sr_instance_id;
159 --and trunc(effective_date) >= trunc(sysdate);
160
161 l_st_date date;
162 l_end_date date;
163
164 l_found boolean := false;
165 begin
166 l_query_id := getNewFormQueryId;
167
168 insert into msc_form_query(query_id,
169 creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
170 number5, number6, number7, date1, date2)
171 select l_query_id,sysdate, -1, -1, sysdate,
172 b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,
173 msc_sda_utils.getRepairItem(b.plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
174 b.prime_item_id,
175 b.effective_date,
176 b.disable_date
177 from msc_item_substitutes b, msc_plans mp
178 where b.plan_id = p_plan
179 and b.relationship_type = c_mis_supersession_type
180 and b.highest_item_id = p_item
181 and b.inferred_flag = 2
182 and b.forward_rule = 1
183 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date, sysdate))
184 or trunc(b.effective_date) >= trunc(sysdate) )
185 and b.plan_id = mp.plan_id
186 and b.sr_instance_id = mp.sr_instance_id
187 and b.higher_item_id = p_item;
188 loop
189 l_found := false;
190 l_level := l_level + 1;
191 for c_curr_row in c_curr_level_cur(l_query_id, l_level-1)
192 loop
193 insert into msc_form_query(query_id,
194 creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
195 number5, number6, number7, date1, date2)
196 select l_query_id,sysdate, -1, -1, sysdate,
197 b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,
198 msc_sda_utils.getRepairItem(b.plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
199 b.prime_item_id,
200 b.effective_date,
201 b.disable_date
202 from msc_item_substitutes b, msc_plans mp
203 where b.plan_id = p_plan
204 and b.relationship_type = c_mis_supersession_type
205 and b.highest_item_id = p_item
206 and b.inferred_flag = 2
207 and b.forward_rule = 1
208 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date, sysdate))
209 or trunc(b.effective_date) >= trunc(sysdate) )
210 and b.plan_id = mp.plan_id
211 and b.sr_instance_id = mp.sr_instance_id
212 and b.higher_item_id = c_curr_row.number1;
213
214 if (sql%rowcount <> 0) then
215 l_found := true;
216 end if;
217 end loop;
218
219 if (not l_found) then
220 exit;
221 end if;
222 end loop;
223
224 --update effective dates from ods data
225 for c_chain_row in c_chain_cur(l_query_id)
226 loop
227 open c_ods_data_cur(c_chain_row.number1, c_chain_row.number2);
228 fetch c_ods_data_cur into l_st_date, l_end_date;
229 close c_ods_data_cur;
230
231 update msc_form_query
232 set date1=l_st_date, date2=l_end_date
233 where query_id = l_query_id
234 and number1 = c_chain_row.number1
235 and number2 = c_chain_row.number2;
236
237 end loop;
238
239 return l_query_id;
240 end flushSupersessionChainNew;
241
242 function is_item_in_chain(p_plan number, p_item_id number) return number is
243 cursor c_check_cur is
244 select count(*)
245 from msc_item_substitutes mis
246 where plan_id = p_plan
247 and mis.relationship_type = c_mis_supersession_type
248 and mis.inferred_flag = 2
249 and mis.forward_rule = 1
250 and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
251 or trunc(mis.effective_date) >= trunc(sysdate) )
252 and (lower_item_id = p_item_id or higher_item_id = p_item_id or highest_item_id = p_item_id);
253 l_count number;
254 begin
255 open c_check_cur;
256 fetch c_check_cur into l_count;
257 close c_check_cur;
258 if (l_count = 0) then
259 return 2;
260 end if;
261 return 1;
262 end is_item_in_chain;
263
264 function is_chain_duplicate(ll_qid number, l_highest_item_id number, ll_chain_id number) return number is
265 cursor c_check_cur is
266 select count(*)
267 from msc_form_query mfq
268 where mfq.query_id = ll_qid
269 and mfq.number8 = 100
270 and mfq.number9 <> ll_chain_id
271 and l_highest_item_id in (mfq.number1, mfq.number2, mfq.number3);
272 l_count number;
273 begin
274 open c_check_cur;
275 fetch c_check_cur into l_count;
276 close c_check_cur;
277 if (l_count = 0) then
278 return 2;
279 end if;
280 return 1;
281 end is_chain_duplicate;
282
283 function flushSupersessionChain(p_plan number, p_item number, p_related_flag number default null) return number is
284 l_query_id number;
285 l_qid1 number;
286
287 cursor c_highest_cur is
288 select distinct mis.highest_item_id
289 from msc_system_items msi,
290 msc_plans mp,
291 msc_item_substitutes mis,
292 msc_system_items msi2
293 where mp.plan_id = p_plan
294 and msi.plan_id = mp.plan_id
295 and msi.sr_instance_id = mp.sr_instance_id
296 and msi.inventory_item_id = p_item
297 and msi.group_id is not null
298 and mis.plan_id = p_plan
299 and mis.relationship_type = c_mis_supersession_type
300 and mis.inferred_flag = 2
301 and mis.forward_rule = 1
302 and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
303 or trunc(mis.effective_date) >= trunc(sysdate) )
304 and mis.plan_id = msi2.plan_id
305 and mis.sr_instance_id = msi2.sr_instance_id
306 and mis.highest_item_id = msi2.inventory_item_id
307 and msi2.group_id = msi.group_id
308 order by 1;
309 l_related_query_id number;
310 l_level number :=0;
311
312 cursor c_grouped_items_cur is
313 select distinct msi2.inventory_item_id
314 from msc_system_items msi,
315 msc_system_items msi2
316 where msi.plan_id = p_plan
317 and msi.inventory_item_id = p_item
318 and msi.group_id is not null
319 and msi2.plan_id = msi.plan_id
320 and msi2.group_id = msi.group_id
321 order by 1;
322
323 cursor c_highest_item_cur (ll_query_id number) is
324 select distinct mis.highest_item_id
325 from msc_item_substitutes mis,
326 msc_form_query mfq
327 where mis.plan_id = p_plan
328 and mis.relationship_type = c_mis_supersession_type
329 and mis.inferred_flag = 2
330 and mis.forward_rule = 1
331 and (trunc(sysdate) between trunc(mis.effective_date) and trunc(nvl(mis.disable_date, sysdate))
332 or trunc(mis.effective_date) >= trunc(sysdate) )
333 and mfq.query_id = ll_query_id
334 and mfq.number8 = 1
335 and mfq.number1 in (mis.lower_item_id, mis.higher_item_id, mis.highest_item_id)
336 and mis.higher_item_id = mis.highest_item_id;
337 --order by mis.effective_date desc;
338
339 cursor c_chain_cur (ll_query_id number) is
340 select distinct mfq.number3, mfq.number9
341 from msc_form_query mfq
342 where mfq.query_id = ll_query_id
343 and mfq.number8 = 100
344 and mfq.number2 = mfq.number3
345 order by 2,1;
346
347 l_chain_id number := 0;
348 l_flag number;
349 begin
350 if nvl(p_related_flag,2) =1 then
351
352 l_qid1 := getNewFormQueryId;
353 l_related_query_id := getNewFormQueryId;
354
355 --get all items with same group id and insert into l_qid1 and number8 with values 1,2
356 for c_row in c_grouped_items_cur
357 loop
358 l_flag := is_item_in_chain(p_plan, c_row.inventory_item_id);
359 insert into msc_form_query(query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number8)
360 values (l_qid1, sysdate, -1, -1, sysdate, c_row.inventory_item_id, l_flag);
361 println('c_grouped_items_cur item_id name '||c_row.inventory_item_id||msc_get_name.item_name(c_row.inventory_item_id, null, null, null));
362 end loop;
363
364 --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
365 for c_row in c_highest_item_cur(l_qid1)
366 loop
367 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));
368 l_level := l_level + 1000;
369 l_chain_id := l_chain_id +1;
370 l_query_id := flushSupersessionChainNew(p_plan, c_row.highest_item_id);
371 insert into msc_form_query(query_id,
372 creation_date, created_by, last_updated_by, last_update_date,
373 number1, number2, number3, number4,
374 number5, number6, number7, date1, date2, number8, number9,number10)
375 select l_qid1, sysdate, -1, -1, sysdate,
376 number1, number2, number3, number4+l_level,
377 number5, number6, number7, date1, date2, 100, l_chain_id,2
378 from msc_form_query
379 where query_id = l_query_id
380 order by number3, number4 desc;
381 end loop;
382
383 --find and mark duplicates with number10=1
384 for c_row in c_chain_cur(l_qid1)
385 loop
386 l_flag := is_chain_duplicate(l_qid1, c_row.number3, c_row.number9);
387 println('c_chain_cur qid number3, name number9 l_flag '||l_qid1||' - '||c_row.number3||' - '||msc_get_name.item_name(c_row.number3, null, null, null)||' - '||c_row.number9||' - '||l_flag);
388 if l_flag = 1 then
389 update msc_form_query
390 set number10 = 1
391 where query_id = l_qid1
392 and number9 = c_row.number9;
393 end if;
394 end loop;
395
396 --insert unique chains
397 insert into msc_form_query(query_id,
398 creation_date, created_by, last_updated_by, last_update_date,
399 number1, number2, number3, number4,
400 number5, number6, number7, date1, date2, number8, number9,number10)
401 select l_related_query_id, sysdate, -1, -1, sysdate,
402 number1, number2, number3, number4,
403 number5, number6, number7, date1, date2, number8, number9,number10
404 from msc_form_query
405 where query_id = l_qid1
406 and number8 = 100
407 and number10 = 2
408 order by number3, number4 desc;
409 /*
410 --insert dangling items [not part of any chain]
411 insert into msc_form_query(query_id,
412 creation_date, created_by, last_updated_by, last_update_date,
413 number1, number2, number3, number4,
414 number5, number6, number7, date1, date2, number8, number9,number10)
415 select l_related_query_id, sysdate, -1, -1, sysdate,
416 number1, number2, number1 number3, -1,
417 number5, number6, number7, date1, date2, number8, number9,number10
418 from msc_form_query
419 where query_id = l_qid1
420 and number8 = 2
421 order by number1 desc;
422 */
423
424
425 return l_related_query_id;
426
427
428 /*
429 l_related_query_id := getNewFormQueryId;
430 for c_row in c_highest_cur
431 loop
432 l_level := l_level + 1000;
433 l_query_id := flushSupersessionChainNew(p_plan, c_row.highest_item_id);
434 insert into msc_form_query(query_id,
435 creation_date, created_by, last_updated_by, last_update_date,
436 number1, number2, number3, number4,
437 number5, number6, number7, date1, date2)
438 select l_related_query_id, sysdate, -1, -1, sysdate,
439 number1, number2, number3, number4+l_level,
440 number5, number6, number7, date1, date2
441 from msc_form_query
442 where query_id = l_query_id
443 order by number3, number4 desc;
444 end loop;
445 return l_related_query_id;
446 */
447 else
448 l_query_id := flushSupersessionChainNew(p_plan, p_item);
449 return l_query_id;
450 end if;
451
452 --not used code below
453 l_query_id := getNewFormQueryId;
454
455 insert into msc_form_query(query_id,
456 creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
457 number5, number6, number7, date1, date2)
458 select l_query_id,sysdate, -1, -1, sysdate,
459 b.lower_item_id, b.higher_item_id, b.highest_item_id, level,
460 b.reciprocal_flag,
461 msc_sda_utils.getRepairItem(plan_id, b.lower_item_id, b.highest_item_id) repair_item_id,
462 b.prime_item_id,
463 (select min(a.effective_date) from msc_item_substitutes a
464 where a.plan_id=p_plan
465 and a.lower_item_id = b.lower_item_id
466 and a.HIGHER_ITEM_ID=b.higher_item_id
467 and a.relationship_type = c_mis_supersession_type
468 and a.inferred_flag = 2
469 and a.forward_rule = 1) as effective_date,
470 disable_date
471 from msc_item_substitutes b
472 where b.plan_id = p_plan
473 and b.relationship_type = c_mis_supersession_type
474 and b.highest_item_id = p_item
475 and b.inferred_flag = 2
476 and b.forward_rule = 1
477 start with b.highest_item_id = p_item
478 and b.inferred_flag = 2
479 and b.highest_item_id = b.higher_item_id
480 connect by nocycle b.higher_item_id = prior b.lower_item_id
481 and b.plan_id = prior b.plan_id
482 and b.relationship_type = prior b.relationship_type
483 and b.inferred_flag = prior b.inferred_flag
484 and b.forward_rule = prior b.forward_rule
485 -- and effective_date = prior effective_date
486 order by level desc;
487
488 return l_query_id;
489 end flushSupersessionChain;
490
491 function getOrgList(p_query_id number) return varchar2 is
492 cursor c_name is
493 select query_name
494 from msc_personal_queries
495 where query_id = p_query_id;
496 l_name varchar2(250);
497 begin
498 open c_name;
499 fetch c_name into l_name;
500 close c_name;
501 return l_name;
502 end getOrgList;
503
504 function getRegionList(p_query_id number) return varchar2 is
505 cursor c_name is
506 select query_name
507 from msc_personal_queries
508 where query_id = p_query_id;
509 l_name varchar2(250);
510 begin
511 open c_name;
512 fetch c_name into l_name;
513 close c_name;
514 return l_name;
515 end getRegionList;
516
517 function getRegionName(p_region_id number) return varchar2 is
518 cursor c_name is
519 select decode(region_type,
520 0,country,
521 1,country||'-'||state,
522 2,country||'-'||state||'-'||city,
523 3,country||'-'||state||'-'||city||'-'||postal_code_from||'-'||postal_code_to,
524 10, mr.zone) reg_list_name
525 from msc_regions mr
526 where mr.region_id = p_region_id;
527 l_name varchar2(250);
528 begin
529 open c_name;
530 fetch c_name into l_name;
531 close c_name;
532 return l_name;
533 end getRegionName;
534
535 procedure getRegListValues(p_region_list varchar2, p_region_type number,
536 p_reg_list_id out nocopy number, p_reg_list out nocopy varchar2,
537 p_region_id out nocopy number, p_region_code out nocopy varchar2) is
538 begin
539 if (p_region_type = c_reg_list_view) then
540 p_reg_list_id := p_region_list;
541 p_reg_list := getRegionList(p_region_list);
542 p_region_id := null;
543 p_region_code := null;
544 elsif (p_region_type = c_reg_view) then
545 p_reg_list_id := null;
546 p_reg_list := null;
547 p_region_id := p_region_list;
548 p_region_code := getRegionName(p_region_list);
549 end if;
550 end getRegListValues;
551
552 procedure getOrgListValues(p_orglist varchar2, p_org_type number,
553 p_org_list_id out nocopy number, p_org_list out nocopy varchar2,
554 p_inst_id out nocopy number, p_org_id out nocopy number,
555 p_org_code out nocopy varchar2) is
556
557 l_open_pos number;
558 l_close_pos number;
559 l_comma_pos number;
560 begin
561 if (p_org_type = c_org_list_view) then
562 p_org_list_id := p_orglist;
563 p_org_list := getOrgList(p_orglist);
564 p_inst_id := null;
565 p_org_id := null;
566 p_org_code := null;
567 elsif (p_org_type = c_org_view) then
568 l_open_pos := instr(p_orglist,'(');
569 l_comma_pos := instr(p_orglist,'-');
570 if (l_comma_pos = 0) then
571 l_comma_pos := instr(p_orglist,',');
572 end if;
573 l_close_pos := instr(p_orglist,')');
574 p_org_list_id := null;
575 p_org_list := null;
576 p_inst_id := substr(p_orglist,l_open_pos+1, l_comma_pos-l_open_pos-1);
577 p_org_id := substr(p_orglist,l_comma_pos+1, l_close_pos-l_comma_pos-1);
578 p_org_code := msc_get_name.org_code(p_org_id, p_inst_id);
579 end if;
580 println('getOrgListValues out');
581 end getOrgListValues;
582
583 procedure getItemListValues(p_cur_item_id number, p_item_view_type number,
584 p_top_item_id out nocopy number, p_top_item_name out nocopy varchar2,
585 p_item_id out nocopy number, p_item_name out nocopy varchar2) is
586 begin
587 if (p_item_view_type = c_item_view) then
588 p_top_item_id := null;
589 p_top_item_name := null;
590 p_item_id := p_cur_item_id;
591 p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
592 else
593 p_top_item_id := p_cur_item_id;
594 p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
595 p_item_id := null;
596 p_item_name := null;
597 end if;
598 end getItemListValues;
599
600 procedure getItemPrimeSS(p_plan_id number, p_item_id number,
601 p_prime_item_id out nocopy number, p_ss_item_id out nocopy number) is
602
603 l_effective_date date;
604
605 --- change the cursor to sort by effective_date
606 --- this is required since we only pick the first row in the cursor
607 cursor c_prime_ss_cur is
608 select b.prime_item_id,
609 b.highest_item_id,
610 b.effective_date
611 from msc_item_substitutes b, msc_plans mp
612 where b.plan_id = p_plan_id
613 and b.relationship_type = c_mis_supersession_type
614 and b.inferred_flag = 2
615 and b.forward_rule = 1
616 and b.lower_item_id = p_item_id
617 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
618 or trunc(b.effective_date) >= trunc(sysdate))
619 and b.plan_id = mp.plan_id
620 and b.sr_instance_id = mp.sr_instance_id
621 order by b.effective_date DESC;
622 --pabram..need to change.. add effective date logic here to pick the right one
623
624 -- for last item in chain, same item itself is prime, and also highest item
625 cursor c_prime_ss_cur1 is
626 select b.highest_item_id prime_item_id,
627 b.highest_item_id,
628 b.effective_date
629 from msc_item_substitutes b, msc_plans mp
630 where b.plan_id = p_plan_id
631 and b.relationship_type = c_mis_supersession_type
632 and b.inferred_flag = 2
633 and b.forward_rule = 1
634 and b.higher_item_id = p_item_id
635 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
636 or trunc(b.effective_date) >= trunc(sysdate))
637 and b.plan_id = mp.plan_id
638 and b.sr_instance_id = mp.sr_instance_id
639 order by effective_date DESC;
640
641 begin
642 open c_prime_ss_cur;
643 fetch c_prime_ss_cur into p_prime_item_id, p_ss_item_id,l_effective_date;
644 close c_prime_ss_cur;
645
646 if p_ss_item_id is null then
647 open c_prime_ss_cur1;
648 fetch c_prime_ss_cur1 into p_prime_item_id, p_ss_item_id,l_effective_date;
649 close c_prime_ss_cur1;
650 end if;
651
652 end getItemPrimeSS;
653
654 function check_row_exists(p_query_id number, p_row_index number,
655 p_org_list_id number, p_inst_id number, p_org_id number,
656 p_top_item_id number, p_item_id number, p_orglist_action number, p_itemlist_action number) return number is
657
658 cursor c_maq_cur is
659 select count(*)
660 from msc_analysis_query
661 where query_id = p_query_id
662 and parent_row_index = p_row_index
663 and nvl(org_list_id, -1) = nvl(p_org_list_id, -1)
664 and nvl(inst_id, -1) = nvl(p_inst_id, -1)
665 and nvl(org_id, -1) = nvl(p_org_id, -1)
666 and nvl(top_item_id, -1) = nvl(p_top_item_id, -1)
667 and nvl(item_id, -1) = nvl(p_item_id, -1)
668 and nvl(org_list_state, -1) = nvl(p_orglist_action, -1)
669 and nvl(top_item_name_state, -1) = nvl(p_itemlist_action, -1) ;
670 l_count number;
671 begin
672 open c_maq_cur;
673 fetch c_maq_cur into l_count;
674 close c_maq_cur;
675
676 if l_count = 0 then
677 return c_sys_no;
678 end if;
679 return c_sys_yes;
680 end check_row_exists;
681
682 procedure flushRegsOrgsIntoMfq(p_plan_id number, p_region_type number, p_region_list number,
683 p_org_type number, p_org_list varchar2,
684 p_region_query_id out nocopy number, p_org_query_id out nocopy number) is
685
686 cursor c_regions_cur(p_view_type number) is
687 select distinct
688 to_number(null) region_list_id,
689 to_char(null) region_list,
690 p_region_list region_id,
691 msc_sda_utils.getRegionName(p_region_list) region_code,
692 p_region_list sort_column
693 from dual
694 where p_region_type = c_reg_view
695 and p_view_type = 1
696 union all
697 select distinct
698 mpq.query_id region_list_id,
699 mpq.query_name region_list,
700 mpt.object_type region_id,
701 msc_sda_utils.getRegionName(mpt.object_type) region_code,
702 mpt.sequence_id sort_column
703 from msc_pq_types mpt,
704 msc_personal_queries mpq
705 where mpq.query_id = p_region_list
706 and mpq.query_id = mpt.query_id
707 and p_region_type = c_reg_list_view
708 and p_view_type = 1
709 order by 5;
710
711 /*
712 --pabram..need to use supersession chain also to reduce inserting too many rows
713 cursor c_orgs_cur is
714 select md.zone_id region_id,
715 md.sr_instance_id inst_id,
716 md.organization_id org_id,
717 msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
718 md.inventory_item_id
719 from msc_demands md,
720 msc_form_query mfq
721 where mfq.query_id = p_region_query_id
722 and md.plan_id = p_plan_id
723 and md.zone_id = mfq.number2;
724 */
725 cursor c_orgs_cur (p_view_type number, p_inst_id number, p_org_id number) is
726 select distinct mfq.number2 region_id,
727 mtp.sr_instance_id inst_id,
728 mtp.sr_tp_id org_id,
729 msc_get_name.org_code(mpo.organization_id, mpo.sr_instance_id) org_code,
730 to_number(null) inventory_item_id
731 from
732 --msc_region_locations mrl,
733 --msc_location_associations mla,
734 msc_trading_partners mtp,
735 msc_plan_organizations mpo,
736 msc_form_query mfq
737 --,msc_zone_regions mzr
738 where mfq.query_id = p_region_query_id
739 and nvl(mfq.number2,-1) > 0
740 --and mzr.parent_region_id = mfq.number2
741 --and mrl.region_id = mzr.region_id
742 --and mrl.location_id = mla.location_id
743 --and mla.partner_id = mtp.partner_id
744 and mtp.partner_type = 3
745 and mpo.plan_id = p_plan_id
746 and mpo.sr_instance_id = mtp.sr_instance_id
747 and mpo.organization_id = mtp.sr_tp_id
748 and p_view_type = 1 --region list selected by user
749 --pabram..commented out --msc_location_associations, msc_trading_partners for testing,
750 --we need to enable this when these tables are flushed correctly
751 --6736491, need to add mrl back
752 union all
753 select distinct
754 c_mbp_not_null_value region_id,
755 mtp.sr_instance_id inst_id,
756 mtp.sr_tp_id org_id,
757 msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
758 to_number(null) inventory_item_id
759 from msc_trading_partners mtp
760 where mtp.sr_tp_id = p_org_id
761 and mtp.sr_instance_id = p_inst_id
762 and mtp.partner_type = 3
763 and p_org_type = c_org_view
764 and p_view_type = 2
765 union all
766 select distinct
767 c_mbp_not_null_value region_id,
768 mpt.source_type inst_id,
769 mpt.object_type org_id,
770 msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
771 to_number(null) inventory_item_id
772 from msc_pq_types mpt,
773 msc_personal_queries mpq
774 where mpq.query_id = p_org_list
775 and mpq.query_id = mpt.query_id
776 and p_org_type = c_org_list_view
777 and p_view_type = 2
778 order by 4;
779
780 ll_org_list_id number;
781 ll_org_list varchar2(250);
782 ll_inst_id number;
783 ll_org_id number;
784 ll_org_code varchar2(10);
785
786 ll_view_type number;
787 begin
788 println('flushRegsOrgsIntoMfq in');
789 p_region_query_id := getNewFormQueryId;
790 p_org_query_id := getNewFormQueryId;
791
792 if ( p_region_list is not null) then
793 ll_view_type := 1;
794 elsif (p_org_list is not null) then
795 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);
796 ll_view_type := 2;
797 end if;
798
799 for c_regions in c_regions_cur(ll_view_type)
800 loop
801 println('regions loop in');
802 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
803 number1, char1, number2, char2, number3)
804 values (p_region_query_id, sysdate, -1, -1, sysdate,
805 c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
806
807 --insert a global org for each region
808 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
809 number1, number2, number3, char1, number4)
810 values (p_org_query_id, sysdate, -1, -1, sysdate,
811 c_regions.region_id, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
812
813 println('regions loop out');
814 end loop;
815
816 --insert one global orgs for global without region_id
817 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
818 number1, number2, number3, char1, number4)
819 values (p_org_query_id, sysdate, -1, -1, sysdate,
820 c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
821
822 --add global region
823 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
824 number1, char1, number2, char2, number3)
825 values (p_region_query_id, sysdate, -1, -1, sysdate,
826 to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
827
828 --add local region
829 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
830 number1, char1, number2, char2, number3)
831 values (p_region_query_id, sysdate, -1, -1, sysdate,
832 to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
833
834 --insert one global orgs for local with region_id for usage based forecast local
835 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
836 number1, number2, number3, char1, number4)
837 values (p_org_query_id, sysdate, -1, -1, sysdate,
838 c_local_reg_type, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
839
840 for c_orgs in c_orgs_cur(ll_view_type, ll_inst_id, ll_org_id)
841 loop
842 println('region-orgs loop in');
843 --insert orgs for regions
844 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
845 number1, number2, number3, char1, number4)
846 values (p_org_query_id, sysdate, -1, -1, sysdate,
847 c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
848
849 --insert orgs for global
850 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
851 number1, number2, number3, char1, number4)
852 values (p_org_query_id, sysdate, -1, -1, sysdate,
853 c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
854
855 --insert orgs for local
856 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
857 number1, number2, number3, char1, number4)
858 values (p_org_query_id, sysdate, -1, -1, sysdate,
859 c_local_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
860 end loop;
861
862 println(' p_org_query_id p_region_query_id '||p_org_query_id||' - '||p_region_query_id);
863 println('flushRegsOrgsIntoMfq out');
864 end flushRegsOrgsIntoMfq;
865
866 function flushOrgsIntoMfq(p_query_id number, p_row_index number, p_org_type number) return number is
867 l_query_id number;
868
869 cursor c_orgs_cur is
870 select distinct
871 to_number(null) org_list_id,
872 to_char(null) org_list,
873 mtp.sr_instance_id inst_id,
874 mtp.sr_tp_id org_id,
875 msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
876 mtp.sr_tp_id sort_column
877 from msc_trading_partners mtp,
878 msc_analysis_query maq
879 where maq.query_id = p_query_id
880 and maq.row_index = p_row_index
881 and mtp.sr_instance_id = maq.inst_id
882 and mtp.sr_tp_id = maq.org_id
883 and mtp.partner_type = 3
884 and p_org_type = c_org_view
885 union all
886 select distinct
887 mpq.query_id org_list_id,
888 mpq.query_name org_list,
889 mpt.source_type inst_id,
890 mpt.object_type org_id,
891 msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
892 mpt.sequence_id sort_column
893 from msc_pq_types mpt,
894 msc_personal_queries mpq,
895 msc_analysis_query maq
896 where maq.query_id = p_query_id
897 and maq.row_index = p_row_index
898 and mpq.query_id = maq.org_list_id
899 and mpq.query_id = mpt.query_id
900 and p_org_type = c_org_list_view
901 order by 6;
902
903 begin
904 l_query_id := getNewFormQueryId;
905 for c_orgs in c_orgs_cur
906 loop
907 println('inserting +');
908 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
909 number1, char1, number2, number3, char4, number4)
910 values (l_query_id, sysdate, -1, -1, sysdate,
911 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);
912 end loop;
913 return l_query_id;
914 end flushOrgsIntoMfq;
915
916 function flushChainIntoMfq(p_query_id number, p_plan_id number,
917 p_item_view_type number, p_item_id number) return number is
918
919 l_rqid number;
920 l_rqid_sorted number;
921
922 cursor c_sschain_cur is
923 select to_number(null) top_item_id,
924 to_char(null) top_item_name,
925 inventory_item_id item_id,
926 item_name,
927 1 sort_column
928 from msc_items
929 where inventory_item_id = p_item_id
930 and p_item_view_type = c_item_view
931 union all
932 select distinct
933 p_item_id top_item_id,
934 msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
935 decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id) item_id,
936 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,
937 1 sort_column
938 from msc_item_substitutes b, msc_plans mp
939 where b.plan_id = p_plan_id
940 and (b.prime_item_id = p_item_id
941 or (higher_item_id = highest_item_id and higher_item_id = p_item_id)
942 )
943 and b.relationship_type = c_mis_supersession_type
944 and p_item_view_type = c_prime_view
945 and b.inferred_flag = 2
946 and b.forward_rule = 1
947 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
948 or trunc(b.effective_date) >= trunc(sysdate))
949 and b.plan_id = mp.plan_id
950 and b.sr_instance_id = mp.sr_instance_id
951 /*
952 start with prime_item_id = p_item_id
953 --and highest_item_id = higher_item_id
954 connect by nocycle higher_item_id = prior lower_item_id
955 and plan_id = prior plan_id
956 and relationship_type = prior relationship_type
957 and prime_item_id = prior prime_item_id
958 and inferred_flag = prior inferred_flag
959 and forward_rule = prior forward_rule
960 */ --commented since where cl is enough to fetch this info
961 union all
962 select distinct
963 b.highest_item_id top_item_id,
964 msc_get_name.item_name(b.highest_item_id,null, null, null) top_item_name,
965 b.lower_item_id item_id,
966 msc_get_name.item_name(b.lower_item_id,null, null, null) item_name,
967 1 sort_column
968 from msc_item_substitutes b, msc_plans mp
969 where b.plan_id = p_plan_id
970 and b.relationship_type = c_mis_supersession_type
971 and p_item_view_type = c_supersession_view
972 and b.highest_item_id = p_item_id
973 and b.inferred_flag = 2
974 and b.forward_rule = 1
975 and (trunc(sysdate) between trunc(b.effective_date) and trunc(nvl(b.disable_date,sysdate))
976 or trunc(b.effective_date) >= trunc(sysdate))
977 and b.plan_id = mp.plan_id
978 and b.sr_instance_id = mp.sr_instance_id
979 /*
980 start with highest_item_id = p_item_id
981 and inferred_flag = 2
982 and highest_item_id = higher_item_id
983 --and highest_item_id = higher_item_id
984 connect by nocycle higher_item_id = prior lower_item_id
985 and plan_id = prior plan_id
986 and relationship_type = prior relationship_type
987 and inferred_flag = prior inferred_flag
988 and forward_rule = prior forward_rule
989 */ --commented since where cl is enough to fetch this info
990 union all
991 select distinct
992 p_item_id top_item_id,
993 msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
994 mfq.number2 item_id,
995 msc_get_name.item_name(mfq.number2,null, null, null) item_name,
996 mfq.number3 sort_column
997 from msc_form_query mfq
998 where mfq.query_id = l_rqid_sorted
999 order by sort_column desc;
1000
1001 l_query_id number;
1002 l_found boolean := false;
1003 l_item_name varchar2(300);
1004 l_sysdate date := trunc(sysdate);
1005
1006 cursor c_related_cur is
1007 select number1, number2, number3
1008 from msc_form_query
1009 where query_id = l_rqid
1010 order by number1, number4 desc;
1011
1012 cursor c_related_check_cur(ll_query_id number, ll_item_id number) is
1013 select count(*)
1014 from msc_form_query
1015 where query_id = ll_query_id
1016 and number2 = ll_item_id;
1017
1018 l_temp number;
1019 l_order number := 0;
1020 begin
1021 if p_item_view_type = c_ritems_view then
1022 l_rqid := msc_sda_utils.flushSupersessionChain(p_plan_id, p_item_id,1);
1023
1024 l_rqid_sorted := getNewFormQueryId;
1025 for c_rchain in c_related_cur
1026 loop
1027 open c_related_check_cur(l_rqid_sorted, c_rchain.number1);
1028 fetch c_related_check_cur into l_temp;
1029 close c_related_check_cur;
1030 if (l_temp = 0) then
1031 l_order := l_order + 1;
1032 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1033 values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number1, l_order);
1034 end if;
1035
1036 open c_related_check_cur(l_rqid_sorted, c_rchain.number2);
1037 fetch c_related_check_cur into l_temp;
1038 close c_related_check_cur;
1039 if (l_temp = 0) then
1040 l_order := l_order + 1;
1041 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1042 values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number2, l_order);
1043 end if;
1044
1045 if (c_rchain.number2 = c_rchain.number3) then
1046 open c_related_check_cur(l_rqid_sorted, c_rchain.number3);
1047 fetch c_related_check_cur into l_temp;
1048 close c_related_check_cur;
1049 if (l_temp = 0) then
1050 l_order := l_order + 1;
1051 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1052 values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number3, l_order);
1053 end if;
1054 end if;
1055
1056 end loop;
1057 end if;
1058
1059 l_query_id := getNewFormQueryId;
1060 for c_sschain in c_sschain_cur
1061 loop
1062 println(' populating into chain '||c_sschain.item_name);
1063 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
1064 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,
1065 c_sschain.sort_column);
1066 if (c_sschain.item_id = p_item_id) then
1067 l_found := true;
1068 end if;
1069 end loop;
1070
1071 if (p_item_view_type in (c_prime_view, c_supersession_view, c_ritems_view) ) then
1072 if (l_found = false) then
1073 l_item_name := msc_get_name.item_name(p_item_id, null, null, null);
1074 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
1075 values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
1076 end if;
1077 end if;
1078 return l_query_id;
1079 end flushChainIntoMfq;
1080
1081 function createHistCalInMfq(p_start_date date, p_end_date date) return number is
1082 l_query_id number;
1083
1084 l_first_day date;
1085 l_last_day date;
1086
1087 l_month number;
1088 l_year number;
1089 l_date_index number := 1;
1090
1091 l_start_date date;
1092 l_end_date date;
1093 begin
1094 l_query_id := getNewFormQueryId;
1095 l_start_date := trunc(p_start_date, 'MM');
1096 l_end_date := trunc(p_end_date, 'MM');
1097
1098 l_month := to_char(l_start_date, 'MM');
1099 l_year := to_char(l_start_date, 'YYYY');
1100 loop
1101 l_first_day := to_date( '01/' || l_month || '/' ||l_year, 'DD/MM/YYYY');
1102 l_last_day := last_day(l_first_day);
1103
1104 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
1105 date1, date2, number1)
1106 values (l_query_id, sysdate, -1, -1, sysdate, l_first_day, l_last_day, l_date_index);
1107
1108 if (l_last_day > l_end_date) then
1109 exit;
1110 end if;
1111 if (l_month <12) then
1112 l_month := l_month + 1;
1113 else
1114 l_month := 1;
1115 l_year := l_year + 1;
1116 end if;
1117 l_date_index := l_date_index + 1;
1118 end loop;
1119 return l_query_id;
1120 end createHistCalInMfq;
1121
1122 procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1123 cursor c_item_prompts_cur (p_folder_object varchar2) is
1124 select field_type,
1125 field_name,
1126 field_prompt,
1127 decode(folder_object,
1128 c_item_folder, nvl(group_by,2),
1129 1) default_flag
1130 from msc_criteria
1131 where folder_object = p_folder_object
1132 and field_name <> 'PRE_POSITION_INVENTORY'
1133 order by to_number(field_type);
1134
1135 l_one_record varchar2(500);
1136 l_row_count number := 0;
1137 l_out_data_index number := 1;
1138 begin
1139 -- items column prompts
1140 for c_item_prompts in c_item_prompts_cur(c_item_folder) loop
1141 l_one_record := c_item_prompts.field_type
1142 || c_field_seperator || c_item_prompts.field_name
1143 || c_field_seperator || c_item_prompts.field_prompt
1144 || c_field_seperator || c_item_prompts.default_flag;
1145
1146 l_row_count := l_row_count + 1;
1147 if (l_row_count = 1) then
1148 l_one_record := c_sdview_items_messages || c_bang_separator || c_record_seperator || l_one_record;
1149 end if;
1150 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1151 end loop;
1152
1153 l_row_count := 0;
1154
1155 -- comments column prompts
1156 for c_item_prompts in c_item_prompts_cur(c_comments_folder) loop
1157 l_one_record := c_item_prompts.field_type
1158 || c_field_seperator || c_item_prompts.field_name
1159 || c_field_seperator || c_item_prompts.field_prompt
1160 || c_field_seperator || c_item_prompts.default_flag;
1161
1162 l_row_count := l_row_count + 1;
1163 if (l_row_count = 1) then
1164 l_one_record := c_sdview_comments_messages || c_bang_separator || c_record_seperator || l_one_record;
1165 end if;
1166 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1167 end loop;
1168
1169 l_row_count := 0;
1170
1171 -- excp_summary column prompts
1172 for c_item_prompts in c_item_prompts_cur(c_excp_folder) loop
1173 l_one_record := c_item_prompts.field_type
1174 || c_field_seperator || c_item_prompts.field_name
1175 || c_field_seperator || c_item_prompts.field_prompt
1176 || c_field_seperator || c_item_prompts.default_flag;
1177
1178 l_row_count := l_row_count + 1;
1179 if (l_row_count = 1) then
1180 l_one_record := c_sdview_excp_messages || c_bang_separator || c_record_seperator || l_one_record;
1181 end if;
1182 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1183 end loop;
1184 end spreadTableMessages;
1185
1186 procedure getCommentsData(p_plan_id number, p_chain_query_id number,
1187 p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_stream_label varchar2) is
1188
1189 cursor c_comments_cur is
1190 select distinct mun.note_id,
1191 nvl(mun.last_update_date, mun.creation_date) comment_date,
1192 msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
1193 substr(mun.note_text1,1,80) comment_text
1194 from msc_user_notes mun,
1195 msc_form_query mfq
1196 where
1197 mun.entity_type = c_comment_entity_type
1198 and mun.inventory_item_id in (mfq.number1, number2)
1199 and mfq.query_id = p_chain_query_id
1200 order by 2 desc;
1201 --where mun.plan_id = p_plan_id
1202
1203 l_one_record varchar2(32000);
1204 l_out_data_index number := 1;
1205 l_row_count number := 0;
1206 begin
1207 println('getCommentsData in');
1208 for c_comments in c_comments_cur
1209 loop
1210 l_row_count := l_row_count + 1;
1211 l_one_record := to_char(c_comments.note_id)
1212 || c_field_seperator || to_char(c_comments.comment_date, c_date_format)
1213 || c_field_seperator || msc_sda_utils.escapeSplChars(c_comments.comment_text)
1214 || c_field_seperator || msc_sda_utils.escapeSplChars(c_comments.item_name);
1215 if (l_row_count = 1) then
1216 l_one_record := p_stream_label || c_bang_separator || c_record_seperator || l_one_record;
1217 end if;
1218 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1219 end loop;
1220 println('getCommentsData out');
1221 end getCommentsData;
1222
1223 function getPreference(p_plan_id number, p_plan_type number, p_preference in varchar2) return varchar2 is
1224 l_pref_value number;
1225 l_def_pref_id number;
1226
1227 cursor c_pref_id is
1228 select preference_id
1229 from msc_user_preferences
1230 where default_flag =1
1231 and user_id = fnd_global.user_id
1232 and nvl(plan_type,-1) = p_plan_type;
1233
1234 begin
1235 open c_pref_id;
1236 fetch c_pref_id into l_def_pref_id;
1237 close c_pref_id;
1238 if (l_def_pref_id is null) then
1239 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1240 end if;
1241
1242 l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
1243 return l_pref_value;
1244 end getPreference;
1245
1246
1247 procedure getItemsData(p_plan_id number, p_org_query_id number, p_chain_query_id number, p_out_data in out nocopy maxCharTbl) is
1248 l_category_set_id number;
1249
1250 cursor c_item_cur is
1251 select distinct to_char(sr_instance_id)||'-'||to_char(organization_id)||'-'||to_char(inventory_item_id) node_id,
1252 item_segments, organization_code, description, planner_code,
1253 nettable_inventory_quantity, nonnettable_inventory_quantity, buyer_name,
1254 mrp_planning_code_text, critical_component_flag critical_component_flag,
1255 wip_supply_type_text,
1256 bom_item_type_text, end_assembly_pegging_text, base_model,
1257 category, category_desc, product_family_item, product_family_item_desc,
1258 planning_exception_set, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(repetitive_type,2)) repetitive_type,
1259 standard_cost, carrying_cost,
1260 uom_code, planning_time_fence_date, planning_time_fence_days,
1261 inventory_use_up_date, planning_make_buy_code_text,
1262 ato_forecast_control_text, shrinkage_rate, preprocessing_lead_time,
1263 full_lead_time, postprocessing_lead_time, leadtime_variability,
1264 fixed_lead_time, variable_lead_time, fixed_order_quantity,
1265 fixed_lot_multiplier, minimum_order_quantity, maximum_order_quantity,
1266 safety_stock_days, safety_stock_percent, fixed_days_supply,
1267 msc_get_name.lookup_meaning('SYS_YES_NO', rounding_control_type) rounding_control_type,
1268 effectivity_control_type, abc_class_name, selling_price,
1269 margin, average_discount, net_selling_price, service_level,
1270 demand_time_fence_days, demand_time_fence_date, safety_stock_code,
1271 atp_flag, atp_components_flag, drp_planned, weight_uom,
1272 unit_weight, volume_uom, pip_flag, msc_get_name.lookup_meaning('SYS_YES_NO', create_supply_flag) create_supply_flag,
1273 substitution_window,
1274 convergence_text, divergence_text, continous_transfer_text, exclude_from_budget,
1275 days_tgt_inv_window, days_max_inv_window, days_tgt_inv_supply,
1276 days_max_inv_supply, shelf_life_days, release_time_fence_days,
1277 min_shelf_life_days, unit_volume, to_number(null) max_early_days,
1278 demand_fulfillment_lt, end_of_life_date, fcst_rule_for_demands_text,
1279 fcst_rule_for_returns_text, interarrival_time, life_time_buy_date,
1280 msc_get_name.lookup_meaning('SYS_YES_NO', decode(preposition_point,'Y','1','2')) preposition_point,
1281 repair_cost,
1282 repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
1283 repetitive_planning_flag_text,
1284 mfq.number3,
1285 msiv.ROP_SAFETY_STOCK,
1286 msiv.COMPUTE_SS,
1287 msiv.COMPUTE_EOQ,
1288 msiv.ORDER_COST,
1289 msiv.MAX_USAGE_FACTOR,
1290 msc_get_name.lookup_meaning('SYS_YES_NO', nvl(msiv.INTERMITTENT_DEMAND,2)) INTERMITTENT_DEMAND,
1291 msiv.sr_instance_id,
1292 msiv.inventory_item_id
1293 from msc_system_items_sc_v msiv,
1294 msc_form_query mfq, --items
1295 msc_form_query mfq1 --orgs
1296 where plan_id = p_plan_id
1297 and category_set_id = l_category_set_id
1298 and inventory_item_id in (mfq.number2) --, number1)
1299 and mfq.query_id = p_chain_query_id
1300 and mfq1.query_id = p_org_query_id
1301 --and nvl(mfq1.number1,1) >0
1302 and mfq1.number2 = msiv.sr_instance_id
1303 and mfq1.number3 = msiv.organization_id
1304 order by msiv.organization_code, mfq.number3 desc;
1305
1306
1307 l_one_record varchar2(32000);
1308 l_out_data_index number := 1;
1309 l_row_count number := 0;
1310
1311 l_note_flag number;
1312 begin
1313 println('getItemsData in');
1314
1315 --6726798 bugfix
1316 l_category_set_id := getPreference(p_plan_id, 8, 'CATEGORY_SET_ID');
1317
1318 for c_item in c_item_cur loop
1319
1320 msc_sda_utils.attachment_flag(l_note_flag, c_item.sr_instance_id, null, c_item.inventory_item_id, null, p_plan_id);
1321
1322 l_one_record := c_item.node_id || c_field_seperator ||
1323 msc_sda_utils.escapeSplChars(l_note_flag) || c_field_seperator ||
1324 msc_sda_utils.escapeSplChars(c_item.item_segments) || c_field_seperator ||
1325 msc_sda_utils.escapeSplChars(c_item.organization_code) || c_field_seperator ||
1326 msc_sda_utils.escapeSplChars(c_item.description) || c_field_seperator ||
1327 msc_sda_utils.escapeSplChars(c_item.planner_code) || c_field_seperator ||
1328 msc_sda_utils.escapeSplChars(c_item.nettable_inventory_quantity) || c_field_seperator ||
1329 msc_sda_utils.escapeSplChars(c_item.nonnettable_inventory_quantity) || c_field_seperator ||
1330 msc_sda_utils.escapeSplChars(c_item.buyer_name) || c_field_seperator ||
1331 msc_sda_utils.escapeSplChars(c_item.mrp_planning_code_text) || c_field_seperator ||
1332 msc_sda_utils.escapeSplChars(c_item.critical_component_flag) || c_field_seperator ||
1333 msc_sda_utils.escapeSplChars(c_item.wip_supply_type_text) || c_field_seperator ||
1334 msc_sda_utils.escapeSplChars(c_item.bom_item_type_text) || c_field_seperator ||
1335 msc_sda_utils.escapeSplChars(c_item.end_assembly_pegging_text) || c_field_seperator ||
1336 msc_sda_utils.escapeSplChars(c_item.base_model) || c_field_seperator ||
1337 msc_sda_utils.escapeSplChars(c_item.category) || c_field_seperator ||
1338 msc_sda_utils.escapeSplChars(c_item.category_desc) || c_field_seperator ||
1339 msc_sda_utils.escapeSplChars(c_item.product_family_item) || c_field_seperator ||
1340 msc_sda_utils.escapeSplChars(c_item.product_family_item_desc) || c_field_seperator ||
1341 msc_sda_utils.escapeSplChars(c_item.planning_exception_set) || c_field_seperator ||
1342 msc_sda_utils.escapeSplChars(c_item.repetitive_type) || c_field_seperator ||
1343 msc_sda_utils.escapeSplChars(c_item.standard_cost) || c_field_seperator ||
1344 msc_sda_utils.escapeSplChars(c_item.carrying_cost) || c_field_seperator ||
1345 msc_sda_utils.escapeSplChars(c_item.uom_code) || c_field_seperator ||
1346 msc_sda_utils.escapeSplChars(c_item.planning_time_fence_date) || c_field_seperator ||
1347 msc_sda_utils.escapeSplChars(c_item.planning_time_fence_days) || c_field_seperator ||
1348 msc_sda_utils.escapeSplChars(c_item.inventory_use_up_date) || c_field_seperator ||
1349 msc_sda_utils.escapeSplChars(c_item.planning_make_buy_code_text) || c_field_seperator ||
1350 msc_sda_utils.escapeSplChars(c_item.ato_forecast_control_text) || c_field_seperator ||
1351 msc_sda_utils.escapeSplChars(c_item.shrinkage_rate) || c_field_seperator ||
1352 msc_sda_utils.escapeSplChars(c_item.preprocessing_lead_time) || c_field_seperator ||
1353 msc_sda_utils.escapeSplChars(c_item.full_lead_time) || c_field_seperator ||
1354 msc_sda_utils.escapeSplChars(c_item.postprocessing_lead_time) || c_field_seperator ||
1355 msc_sda_utils.escapeSplChars(c_item.leadtime_variability) || c_field_seperator ||
1356 msc_sda_utils.escapeSplChars(c_item.fixed_lead_time) || c_field_seperator ||
1357 msc_sda_utils.escapeSplChars(c_item.variable_lead_time) || c_field_seperator ||
1358 msc_sda_utils.escapeSplChars(c_item.fixed_order_quantity) || c_field_seperator ||
1359 msc_sda_utils.escapeSplChars(c_item.fixed_lot_multiplier) || c_field_seperator ||
1360 msc_sda_utils.escapeSplChars(c_item.minimum_order_quantity) || c_field_seperator ||
1361 msc_sda_utils.escapeSplChars(c_item.maximum_order_quantity) || c_field_seperator ||
1362 msc_sda_utils.escapeSplChars(c_item.safety_stock_days) || c_field_seperator ||
1363 msc_sda_utils.escapeSplChars(c_item.safety_stock_percent) || c_field_seperator ||
1364 msc_sda_utils.escapeSplChars(c_item.fixed_days_supply) || c_field_seperator ||
1365 msc_sda_utils.escapeSplChars(c_item.rounding_control_type) || c_field_seperator ||
1366 msc_sda_utils.escapeSplChars(c_item.effectivity_control_type) || c_field_seperator ||
1367 msc_sda_utils.escapeSplChars(c_item.abc_class_name) || c_field_seperator ||
1368 msc_sda_utils.escapeSplChars(c_item.selling_price) || c_field_seperator ||
1369 msc_sda_utils.escapeSplChars(c_item.margin) || c_field_seperator ||
1370 msc_sda_utils.escapeSplChars(c_item.average_discount) || c_field_seperator ||
1371 msc_sda_utils.escapeSplChars(c_item.net_selling_price) || c_field_seperator ||
1372 msc_sda_utils.escapeSplChars(c_item.service_level) || c_field_seperator ||
1373 msc_sda_utils.escapeSplChars(c_item.demand_time_fence_days) || c_field_seperator ||
1374 msc_sda_utils.escapeSplChars(c_item.demand_time_fence_date) || c_field_seperator ||
1375 msc_sda_utils.escapeSplChars(c_item.safety_stock_code) || c_field_seperator ||
1376 msc_sda_utils.escapeSplChars(c_item.atp_flag) || c_field_seperator ||
1377 msc_sda_utils.escapeSplChars(c_item.atp_components_flag) || c_field_seperator ||
1378 msc_sda_utils.escapeSplChars(c_item.drp_planned) || c_field_seperator ||
1379 msc_sda_utils.escapeSplChars(c_item.weight_uom) || c_field_seperator ||
1380 msc_sda_utils.escapeSplChars(c_item.unit_weight) || c_field_seperator ||
1381 msc_sda_utils.escapeSplChars(c_item.volume_uom) || c_field_seperator ||
1382 msc_sda_utils.escapeSplChars(c_item.pip_flag) || c_field_seperator ||
1383 msc_sda_utils.escapeSplChars(c_item.create_supply_flag) || c_field_seperator ||
1384 msc_sda_utils.escapeSplChars(c_item.substitution_window) || c_field_seperator ||
1385 msc_sda_utils.escapeSplChars(c_item.convergence_text) || c_field_seperator ||
1386 msc_sda_utils.escapeSplChars(c_item.divergence_text) || c_field_seperator ||
1387 msc_sda_utils.escapeSplChars(c_item.continous_transfer_text) || c_field_seperator ||
1388 msc_sda_utils.escapeSplChars(c_item.exclude_from_budget) || c_field_seperator ||
1389 msc_sda_utils.escapeSplChars(c_item.days_tgt_inv_window) || c_field_seperator ||
1390 msc_sda_utils.escapeSplChars(c_item.days_max_inv_window) || c_field_seperator ||
1391 msc_sda_utils.escapeSplChars(c_item.days_tgt_inv_supply) || c_field_seperator ||
1392 msc_sda_utils.escapeSplChars(c_item.days_max_inv_supply) || c_field_seperator ||
1393 msc_sda_utils.escapeSplChars(c_item.shelf_life_days) || c_field_seperator ||
1394 msc_sda_utils.escapeSplChars(c_item.release_time_fence_days) || c_field_seperator ||
1395 msc_sda_utils.escapeSplChars(c_item.min_shelf_life_days) || c_field_seperator ||
1396 msc_sda_utils.escapeSplChars(c_item.unit_volume) || c_field_seperator ||
1397 msc_sda_utils.escapeSplChars(c_item.max_early_days) || c_field_seperator ||
1398 msc_sda_utils.escapeSplChars(c_item.demand_fulfillment_lt) || c_field_seperator ||
1399 msc_sda_utils.escapeSplChars(to_char(c_item.end_of_life_date, c_datetime_format)) || c_field_seperator ||
1400 msc_sda_utils.escapeSplChars(c_item.fcst_rule_for_demands_text) || c_field_seperator ||
1401 msc_sda_utils.escapeSplChars(c_item.fcst_rule_for_returns_text) || c_field_seperator ||
1402 msc_sda_utils.escapeSplChars(c_item.interarrival_time) || c_field_seperator ||
1403 msc_sda_utils.escapeSplChars(to_char(c_item.life_time_buy_date, c_datetime_format)) || c_field_seperator ||
1404 msc_sda_utils.escapeSplChars(c_item.preposition_point) || c_field_seperator ||
1405 msc_sda_utils.escapeSplChars(c_item.repair_cost) || c_field_seperator ||
1406 msc_sda_utils.escapeSplChars(c_item.repair_lead_time) || c_field_seperator ||
1407 msc_sda_utils.escapeSplChars(c_item.repair_program_text) || c_field_seperator ||
1408 msc_sda_utils.escapeSplChars(c_item.repair_yield) || c_field_seperator ||
1409 msc_sda_utils.escapeSplChars(c_item.std_dmd_over_horizon) || c_field_seperator ||
1410 msc_sda_utils.escapeSplChars(c_item.repetitive_planning_flag_text) || c_field_seperator ||
1411 msc_sda_utils.escapeSplChars(c_item.MAX_USAGE_FACTOR) || c_field_seperator ||
1412 msc_sda_utils.escapeSplChars(c_item.COMPUTE_SS) || c_field_seperator ||
1413 msc_sda_utils.escapeSplChars(c_item.COMPUTE_EOQ) || c_field_seperator ||
1414 msc_sda_utils.escapeSplChars(c_item.ORDER_COST) || c_field_seperator ||
1415 msc_sda_utils.escapeSplChars(c_item.ROP_SAFETY_STOCK)
1416 || c_field_seperator || msc_sda_utils.escapeSplChars(c_item.INTERMITTENT_DEMAND);
1417
1418 l_row_count := l_row_count + 1;
1419
1420 if (l_row_count = 1) then
1421 l_one_record := c_sdview_items_data || c_bang_separator || c_record_seperator || l_one_record;
1422 end if;
1423 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1424 end loop;
1425 println('getItemsData out');
1426 end getItemsData;
1427
1428 procedure getExceptionsData(p_plan_id number, p_chain_query_id number, p_org_query_id number,
1429 p_out_data in out nocopy maxCharTbl) is
1430
1431 cursor c_excp_summary_cur is
1432 select med.exception_type,
1433 msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
1434 count(*) excp_all_count,
1435 sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
1436 from msc_exception_details med,
1437 msc_form_query mfq, --items
1438 msc_form_query mfq1 --orgs
1439 where med.plan_id = p_plan_id
1440 and med.inventory_item_id = mfq.number2
1441 and mfq.query_id = p_chain_query_id
1442 and mfq1.query_id = p_org_query_id
1443 and nvl(mfq1.number1,1) >0
1444 and mfq1.number2 = med.sr_instance_id
1445 and mfq1.number3 = med.organization_id
1446 group by med.exception_type,
1447 msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
1448 union all
1449 select -99 exception_type,
1450 msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
1451 count(*) excp_all_count,
1452 0 excp_no_count
1453 from msc_supplies ms,
1454 msc_system_items msi,
1455 msc_form_query mfq, --items
1456 msc_form_query mfq1 --orgs
1457 where ms.plan_id = p_plan_id
1458 and ms.inventory_item_id = mfq.number2
1459 and mfq.query_id = p_chain_query_id
1460 and mfq1.query_id = p_org_query_id
1461 and nvl(mfq1.number1,1) >0
1462 and mfq1.number2 = ms.sr_instance_id
1463 and mfq1.number3 = ms.organization_id
1464 and ms.plan_id = msi.plan_id
1465 and ms.sr_instance_id = msi.sr_instance_id
1466 and ms.organization_id = msi.organization_id
1467 and ms.inventory_item_id = msi.inventory_item_id
1468 and ( (ms.order_type = 13)
1469 or (ms.order_type = 5
1470 and nvl(ms.implemented_quantity,0)+nvl(ms.quantity_in_process,0) < nvl(ms.firm_quantity,ms.new_order_quantity)
1471 and (nvl(msi.lots_exist,0) <> 2 or ms.new_order_quantity =0)
1472 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)
1473 and msi.purchasing_enabled_flag = 1)
1474 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)
1475 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)
1476 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))
1477 )
1478 )
1479 group by -99,
1480 msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10);
1481 --pabram.need to add recommendations
1482
1483 l_one_record varchar2(32000);
1484 l_out_data_index number := 1;
1485 l_row_count number := 0;
1486 begin
1487 println('getExceptionsData in');
1488 for c_excp_summary in c_excp_summary_cur loop
1489 l_one_record :=
1490 msc_sda_utils.escapeSplChars(p_chain_query_id||'-'||c_excp_summary.exception_type) || c_field_seperator ||
1491 msc_sda_utils.escapeSplChars(c_excp_summary.exception_type_text) || c_field_seperator ||
1492 msc_sda_utils.escapeSplChars(c_excp_summary.excp_all_count) || c_field_seperator ||
1493 msc_sda_utils.escapeSplChars(c_excp_summary.excp_no_count);
1494
1495 l_row_count := l_row_count + 1;
1496 if (l_row_count = 1) then
1497 l_one_record := c_sdview_excp_data || c_bang_separator || c_record_seperator || l_one_record;
1498 end if;
1499 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1500 end loop;
1501 println('getExceptionsData out');
1502 end getExceptionsData;
1503
1504 procedure getWorkSheetPrefData(p_out_data in out nocopy maxCharTbl, p_refresh_flag number) is
1505 cursor c_userpref_cur is
1506 select name, key, nvl(value, c_null_space) value
1507 from msc_analyze_preference
1508 where module = c_sda_pref_set
1509 and userid= fnd_global.user_id
1510 order by name, key;
1511
1512 l_one_record varchar2(32000);
1513 l_out_data_index number := 1;
1514 l_row_count number := 0;
1515 l_stream_label varchar2(200);
1516 begin
1517 println('getWorkSheetPrefData in');
1518
1519 if (p_refresh_flag = 1) then
1520 l_stream_label := c_sdview_prefset_data_ref;
1521 else
1522 l_stream_label := c_sdview_prefset_data;
1523 end if;
1524 for c_userpref in c_userpref_cur
1525 loop
1526 l_one_record :=
1527 msc_sda_utils.escapeSplChars(c_userpref.name) || c_field_seperator ||
1528 msc_sda_utils.escapeSplChars(c_userpref.key) || c_field_seperator ||
1529 msc_sda_utils.escapeSplChars(c_userpref.value);
1530
1531 l_row_count := l_row_count + 1;
1532 if (l_row_count = 1) then
1533 l_one_record := l_stream_label || c_bang_separator || c_record_seperator || l_one_record;
1534 end if;
1535 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1536 end loop;
1537 println('getWorkSheetPrefData out');
1538 end getWorkSheetPrefData;
1539
1540 procedure sendSDRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1541 cursor c_sd_rowtypes_cur is
1542 select lookup_code, meaning
1543 from mfg_lookups
1544 where lookup_type = c_sdview_rowtype_lookup
1545 order by 1;
1546 l_one_record varchar2(32000) := null;
1547 l_token varchar2(1000);
1548 l_out_data_index number := 1;
1549 l_flag number; -- 0 or null - sum, 1 - avg, 2- use-last-record-in-bucket, 3 show min
1550 l_visible_flag number; -- 0 hide, 1 show
1551 l_total_row_flag number; -- 1 yes, 2 no
1552 begin
1553 for c_sd_rowtypes in c_sd_rowtypes_cur
1554 loop
1555 l_flag := 0;
1556 l_visible_flag := 1;
1557 l_total_row_flag := 2;
1558
1559 if (c_sd_rowtypes.lookup_code in (45) ) then
1560 --safety stock qty
1561 l_flag := 2;
1562 end if;
1563 if (c_sd_rowtypes.lookup_code in (31,32,44) ) then
1564 --31 Projected Available Balance
1565 --32 Projected On-hand
1566 --44 Projected Available Balance (Defective)
1567 l_flag := 2;
1568 end if;
1569 if (c_sd_rowtypes.lookup_code in (26,28,29) ) then
1570 --26 Safety Stock (Days of Supply)
1571 --28 Projected Service Level
1572 --29 Target Service Level
1573 l_flag := 3;
1574 end if;
1575 if (c_sd_rowtypes.lookup_code in (24,26,27,28,29,30) ) then
1576 --24 Planned Warranty Orders
1577 --26 Safety Stock (Days of Supply)
1578 --27 Total Unconstrained Demand
1579 --28 Projected Service Level (%)
1580 --29 Target Service Level (%)
1581 --30 Maximum Level
1582 l_visible_flag := 0;
1583 end if;
1584
1585 if (c_sd_rowtypes.lookup_code in (4,7,8,9,25,27,36,43) ) then
1586 --4 Independent Demand
1587 --7 Dependent Demand
1588 --8 Other Demand
1589 --9 Total Demand
1590 --25 Total Supply
1591 --27 Total Unconstrained Demand
1592 --36 Total Defective Part Demand
1593 --43 Total Defective Supply
1594 l_total_row_flag := 1;
1595 end if;
1596
1597 l_token := c_sd_rowtypes.lookup_code
1598 || c_field_seperator || msc_sda_utils.escapeSplChars(c_sd_rowtypes.meaning)
1599 || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1600 if (l_one_record is null) then
1601 l_one_record := c_sdview_rowtypes || c_bang_separator || c_sd_total_row_types || c_record_seperator || l_token;
1602 else
1603 l_one_record := l_one_record || c_record_seperator || l_token;
1604 end if;
1605 end loop;
1606 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1607 end sendSDRowTypes;
1608
1609 procedure sendFcstRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1610 cursor c_fcst_rowtypes_cur is
1611 select lookup_code, meaning
1612 from mfg_lookups
1613 where lookup_type = c_fcstview_rowtype_lookup
1614 order by 1;
1615 l_one_record varchar2(32000) := null;
1616 l_token varchar2(1000);
1617 l_out_data_index number := 1;
1618 l_flag number; -- 0 - sum, 1 - avg, 2- use-last-record-in-bucket
1619 l_visible_flag number; -- 0 hide, 1 show
1620 l_total_row_flag number; -- 1 yes, 2 no
1621 begin
1622 for c_fcst_rowtypes in c_fcst_rowtypes_cur
1623 loop
1624 l_flag := 0;
1625 l_visible_flag := 1;
1626 l_total_row_flag := 2;
1627
1628 if (c_fcst_rowtypes.lookup_code in (8,13,16)) then
1629 --8 Best Fit forecast
1630 --13 Returns Best Fit forecast
1631 l_visible_flag := 0;
1632 end if;
1633 --6657610 bugfix,
1634
1635 if (c_fcst_rowtypes.lookup_code in (1,9)) then
1636 --1 Total Forecast
1637 --9 Total Returns Forecast
1638 l_total_row_flag := 1;
1639 end if;
1640
1641 l_token := c_fcst_rowtypes.lookup_code
1642 || c_field_seperator || msc_sda_utils.escapeSplChars(c_fcst_rowtypes.meaning)
1643 || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1644 if (l_one_record is null) then
1645 l_one_record := c_fcstview_rowtypes || c_bang_separator || c_fcst_total_row_types || c_record_seperator || l_token;
1646 else
1647 l_one_record := l_one_record || c_record_seperator || l_token;
1648 end if;
1649 end loop;
1650 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1651 end sendFcstRowTypes;
1652
1653 procedure sendHistRowTypes(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1654 cursor c_hist_rowtypes_cur is
1655 select lookup_code, meaning
1656 from mfg_lookups
1657 where lookup_type = c_histview_rowtype_lookup
1658 order by 1;
1659 l_one_record varchar2(32000) := null;
1660 l_token varchar2(1000);
1661 l_out_data_index number := 1;
1662 l_flag number; -- 0 - sum, 1 - avg, 2- use-last-record-in-bucket
1663 l_visible_flag number; -- 0 hide, 1 show
1664 l_total_row_flag number; -- 1 yes, 2 no
1665 begin
1666 for c_hist_rowtypes in c_hist_rowtypes_cur
1667 loop
1668 l_flag := 0;
1669 l_visible_flag := 1;
1670 l_total_row_flag := 2;
1671 l_token := c_hist_rowtypes.lookup_code
1672 || c_field_seperator || msc_sda_utils.escapeSplChars(c_hist_rowtypes.meaning)
1673 || c_field_seperator || l_flag || c_field_seperator || l_visible_flag || c_field_seperator || l_total_row_flag;
1674 if (l_one_record is null) then
1675 l_one_record := c_histview_rowtypes || c_bang_separator || c_hist_total_row_types || c_record_seperator || l_token;
1676 else
1677 l_one_record := l_one_record || c_record_seperator || l_token;
1678 end if;
1679 end loop;
1680 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1681 end sendHistRowTypes;
1682
1683 function getTokenizedMsg(p_msg varchar2) return varchar2 is
1684 l_msg_text varchar2(300);
1685 begin
1686 FND_MESSAGE.set_name('MSC', p_msg);
1687 l_msg_text:= FND_MESSAGE.get;
1688 if (l_msg_text is null) then
1689 l_msg_text:= p_msg|| c_field_seperator || p_msg;
1690 else
1691 l_msg_text:= p_msg|| c_field_seperator || msc_sda_utils.escapeSplChars(l_msg_text);
1692 end if;
1693 return l_msg_text;
1694 end getTokenizedMsg;
1695
1696 procedure addMessages(p_msg varchar2, p_out_data_index in out nocopy number,
1697 p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1698 l_one_record varchar2(1200) := null;
1699 l_token varchar2(1000);
1700 begin
1701 l_one_record := getTokenizedMsg(p_msg);
1702 msc_sda_utils.addRecordToOutStream(l_one_record, p_out_data_index, p_out_data);
1703 end addMessages;
1704
1705 procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
1706 l_one_record varchar2(32000) := null;
1707 l_token varchar2(1000);
1708 l_out_data_index number := 1;
1709 begin
1710 l_token := getTokenizedMsg('MENU_ORDER_DETAILS');
1711 l_one_record := c_sdview_nls_messages || c_bang_separator || c_record_seperator || l_token;
1712 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1713
1714 addMessages('MENU_FORECAST_RULE', l_out_data_index, p_out_data);
1715 addMessages('MENU_CALENDAR', l_out_data_index, p_out_data);
1716 addMessages('MENU_SOURCES', l_out_data_index, p_out_data);
1717 addMessages('MENU_SUPPLY_CHAIN_BILL', l_out_data_index, p_out_data);
1718 addMessages('MENU_DESTINATION', l_out_data_index, p_out_data);
1719 addMessages('MENU_SUPPLY_CHAIN', l_out_data_index, p_out_data);
1720 addMessages('PROMPT_ADD_NEW_COMMENT', l_out_data_index, p_out_data);
1721 addMessages('MENU_EXCP_ALL', l_out_data_index, p_out_data);
1722 addMessages('MENU_EXCP_ACTION_TAKEN', l_out_data_index, p_out_data);
1723 addMessages('MENU_EXCP_NO_ACTION', l_out_data_index, p_out_data);
1724 addMessages('LABEL_WORKSHEET_PREF', l_out_data_index, p_out_data);
1725 addMessages('DLG_TITLE_GRAPH', l_out_data_index, p_out_data);
1726 addMessages('DLG_MSG_BAD_GRID_SEL', l_out_data_index, p_out_data);
1727 addMessages('MENU_HIDE_COLUMN', l_out_data_index, p_out_data);
1728 addMessages('MENU_CHOOSE_COLUMNS', l_out_data_index, p_out_data);
1729 addMessages('DLG_TITLE_CHOOSE_COLUMNS', l_out_data_index, p_out_data);
1730 addMessages('BTN_LABEL_OK', l_out_data_index, p_out_data);
1731 addMessages('BTN_LABEL_CANCEL', l_out_data_index, p_out_data);
1732 addMessages('LBL_NOT_ENOUGH_DATA', l_out_data_index, p_out_data);
1733 addMessages('GRAPH_ROW_TYPE_PREF', l_out_data_index, p_out_data);
1734 addMessages('GRAPH_SEL_ROWTYPES', l_out_data_index, p_out_data);
1735 addMessages('GRAPH_AVAIL_ROWTYPES', l_out_data_index, p_out_data);
1736 addMessages('GRAPH_BTN_LABEL_REFRESH', l_out_data_index, p_out_data);
1737 addMessages('GRAPH_TIME_INTERVAL', l_out_data_index, p_out_data);
1738 addMessages('GRAPH_ITEMS', l_out_data_index, p_out_data);
1739 addMessages('GRAPH_CHART_TYPE', l_out_data_index, p_out_data);
1740 addMessages('GRAPH_BAR', l_out_data_index, p_out_data);
1741 addMessages('GRAPH_LINE', l_out_data_index, p_out_data);
1742 addMessages('GRAPH_COMBO', l_out_data_index, p_out_data);
1743 addMessages('GRAPH_LEGEND', l_out_data_index, p_out_data);
1744 addMessages('GRAPH_LABEL_SHOW', l_out_data_index, p_out_data);
1745 addMessages('GRAPH_LABEL_HIDE', l_out_data_index, p_out_data);
1746 addMessages('GRAPH_PREF', l_out_data_index, p_out_data);
1747 addMessages('GRID_LABEL_PAST', l_out_data_index, p_out_data);
1748 addMessages('MSC_FORECAST_RULE_RETURNS', l_out_data_index, p_out_data);
1749 addMessages('MSC_ITEM_FAILURE_RATES', l_out_data_index, p_out_data);
1750 addMessages('PROMPT_NO_TABLEDATA_ROWS', l_out_data_index, p_out_data);
1751 addMessages('GRAPH_BTN_LABEL_CLOSE', l_out_data_index, p_out_data);
1752 addMessages('SDA_SAVE_FOLDER', l_out_data_index, p_out_data);
1753 addMessages('MSC_EC_SAVE_SETTINGS', l_out_data_index, p_out_data);
1754
1755 l_one_record := 'ICX_DATE_FORMAT_MASK'|| c_field_seperator
1756 || msc_sda_utils.escapeSplChars(nvl(fnd_profile.value('ICX_DATE_FORMAT_MASK'), 'MM/DD/YYYY'));
1757 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1758 addMessages('MSC_NOTE_SUMMARY', l_out_data_index, p_out_data);
1759 end sendNlsMessages;
1760
1761 procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
1762 p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
1763
1764 TYPE lCurTyp IS REF CURSOR;
1765 theCursor lCurTyp;
1766
1767 l_token varchar2(500);
1768 l_one_record varchar2(32000);
1769
1770 l_sql_stmt varchar2(500);
1771 l_sql_stmt1 varchar2(200);
1772 l_sql_stmt2 varchar2(200);
1773 l_sql_stmt3 varchar2(200);
1774 l_sql_stmt4 varchar2(200);
1775 l_lookup_code number;
1776 l_meaning varchar2(250);
1777 begin
1778 l_sql_stmt1 := 'select lookup_code, meaning from mfg_lookups where lookup_type = :1 ';
1779 l_sql_stmt4 := ' and lookup_code not in (8,13,16) ';
1780 l_sql_stmt2 := 'and lookup_code not in ('|| p_lookup_code_list ||') order by 1';
1781 l_sql_stmt3 := 'and lookup_code in ('|| p_lookup_code_list ||') order by 1';
1782
1783 if (p_lookup_type = c_fcstview_rowtype_lookup) then
1784 l_sql_stmt := l_sql_stmt1||l_sql_stmt4||l_sql_stmt2;
1785 else
1786 l_sql_stmt := l_sql_stmt1||l_sql_stmt2;
1787 end if;
1788 open theCursor for l_sql_stmt using p_lookup_type;
1789 loop
1790 fetch theCursor into l_lookup_code, l_meaning;
1791 exit when theCursor%notfound;
1792 l_token := l_lookup_code || c_field_seperator || msc_sda_utils.escapeSplChars(l_meaning);
1793 if (l_one_record is null) then
1794 l_one_record := SET_FROM_LIST || c_bang_separator || l_token;
1795 else
1796 l_one_record := l_one_record || c_record_seperator || l_token;
1797 end if;
1798 end loop;
1799 close theCursor;
1800 p_from_list := l_one_record;
1801
1802 l_one_record := null;
1803 if (p_lookup_type = c_fcstview_rowtype_lookup) then
1804 l_sql_stmt := l_sql_stmt1||l_sql_stmt4||l_sql_stmt3;
1805 else
1806 l_sql_stmt := l_sql_stmt1||l_sql_stmt3;
1807 end if;
1808 open theCursor for l_sql_stmt using p_lookup_type;
1809 loop
1810 fetch theCursor into l_lookup_code, l_meaning;
1811 exit when theCursor%notfound;
1812 l_token := l_lookup_code || c_field_seperator || msc_sda_utils.escapeSplChars(l_meaning);
1813 if (l_one_record is null) then
1814 l_one_record := SET_TO_LIST || c_bang_separator || l_token;
1815 else
1816 l_one_record := l_one_record || c_record_seperator || l_token;
1817 end if;
1818 end loop;
1819 close theCursor;
1820 p_to_list := l_one_record;
1821 end set_shuttle_from_to;
1822
1823 procedure save_item_folder(p_folder_name varchar, p_folder_value varchar, p_default_flag number, p_public_flag number) is
1824 pragma autonomous_transaction;
1825 cursor c_count (p_module_name varchar2, p_name varchar) is
1826 select count(*)
1827 from msc_analyze_preference
1828 where module = p_module_name
1829 and name = p_name;
1830
1831 l_temp number;
1832 l_default_flag number := nvl(p_default_flag,2);
1833 l_public_flag number := nvl(p_public_flag,2);
1834 begin
1835 commit;
1836
1837 open c_count(c_sda_save_item_folder, p_folder_name);
1838 fetch c_count into l_temp;
1839 close c_count;
1840
1841 if (l_temp <> 0) then
1842 update msc_analyze_preference
1843 set defaultset = to_char(l_default_flag),
1844 public_flag = l_public_flag,
1845 value = p_folder_value
1846 where name = p_folder_name
1847 and module = c_sda_save_item_folder;
1848 else
1849 insert into msc_analyze_preference
1850 (userid, name, module, key, value, defaultset, public_flag,
1851 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1852 values (fnd_global.user_id, p_folder_name, c_sda_save_item_folder, c_sda_save_item_folder, p_folder_value,
1853 to_char(l_default_flag), l_public_flag,
1854 sysdate, -1, sysdate, -1, -1);
1855 end if;
1856 commit;
1857 end save_item_folder;
1858
1859 procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
1860 pragma autonomous_transaction;
1861
1862 cursor c_count (p_module_name varchar2) is
1863 select count(*)
1864 from msc_analyze_preference
1865 where module = p_module_name
1866 and userid = fnd_global.user_id;
1867 l_temp number;
1868
1869 begin
1870 commit;
1871 /*
1872 if (p_event = c_sda_save_item_folder) then
1873 open c_count(c_sda_save_item_folder);
1874 fetch c_count into l_temp;
1875 close c_count;
1876
1877 if (l_temp <> 0) then
1878 delete from msc_analyze_preference
1879 where module = c_sda_save_item_folder
1880 and userid = fnd_global.user_id;
1881 end if;
1882
1883 insert into msc_analyze_preference
1884 (userid, name, module, key, value, defaultset,
1885 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1886 values (fnd_global.user_id, c_sda_save_item_folder, c_sda_save_item_folder, c_sda_save_item_folder, p_event_list, 'N',
1887 sysdate, -1, sysdate, -1, -1);
1888 end if;
1889 */
1890
1891 if (p_event = c_sda_save_settings) then
1892 open c_count(c_sda_save_settings);
1893 fetch c_count into l_temp;
1894 close c_count;
1895
1896 if (l_temp <> 0) then
1897 delete from msc_analyze_preference
1898 where module = c_sda_save_settings
1899 and userid = fnd_global.user_id;
1900 end if;
1901
1902 insert into msc_analyze_preference
1903 (userid, name, module, key, value, defaultset,
1904 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1905 values (fnd_global.user_id, c_sda_save_settings, c_sda_save_settings, c_sda_save_settings, p_event_list, 'N',
1906 sysdate, -1, sysdate, -1, -1);
1907 end if;
1908 commit;
1909 end update_close_settings;
1910
1911 procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
1912 p_save_settings_list out nocopy varchar2) is
1913
1914 cursor c_pref (p_module_name varchar2) is
1915 select value
1916 from msc_analyze_preference
1917 where module = p_module_name
1918 and userid = fnd_global.user_id;
1919 begin
1920 open c_pref(c_sda_save_item_folder);
1921 fetch c_pref into p_item_folder_save_list;
1922 close c_pref;
1923 if (p_item_folder_save_list is not null) then
1924 p_item_folder_save_list := c_sda_save_item_folder || c_bang_separator || c_record_seperator || p_item_folder_save_list;
1925 end if;
1926
1927 open c_pref(c_sda_save_settings);
1928 fetch c_pref into p_save_settings_list;
1929 close c_pref;
1930 if (p_save_settings_list is not null) then
1931 p_save_settings_list := c_sda_save_settings || c_bang_separator || p_save_settings_list;
1932 end if;
1933 end send_close_settings;
1934
1935 procedure update_pref_set (p_name varchar2, p_desc varchar2,
1936 p_days number, p_weeks number, p_periods number,
1937 p_factor number, p_decimal_places number,
1938 p_sd_row_list varchar2, p_fcst_row_list varchar2) is
1939 pragma autonomous_transaction;
1940
1941 cursor c_count is
1942 select count(*)
1943 from msc_analyze_preference
1944 where module = c_sda_pref_set
1945 and name = p_name;
1946 l_temp number;
1947 begin
1948 commit;
1949 open c_count;
1950 fetch c_count into l_temp;
1951 close c_count;
1952
1953 if (l_temp = 0) then
1954 insert into msc_analyze_preference
1955 (userid, name, module, key, value, defaultset,
1956 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1957 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_days, p_days, 'N',
1958 sysdate, -1, sysdate, -1, -1);
1959
1960 insert into msc_analyze_preference
1961 (userid, name, module, key, value, defaultset,
1962 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1963 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_weeks, p_weeks, 'N',
1964 sysdate, -1, sysdate, -1, -1);
1965
1966 insert into msc_analyze_preference
1967 (userid, name, module, key, value, defaultset,
1968 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1969 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_periods, p_periods, 'N',
1970 sysdate, -1, sysdate, -1, -1);
1971
1972 insert into msc_analyze_preference
1973 (userid, name, module, key, value, defaultset,
1974 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1975 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_factor, p_factor, 'N',
1976 sysdate, -1, sysdate, -1, -1);
1977
1978 insert into msc_analyze_preference
1979 (userid, name, module, key, value, defaultset,
1980 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1981 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_decimals, p_decimal_places, 'N',
1982 sysdate, -1, sysdate, -1, -1);
1983
1984 insert into msc_analyze_preference
1985 (userid, name, module, key, value, defaultset,
1986 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1987 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_sd, p_sd_row_list, 'N',
1988 sysdate, -1, sysdate, -1, -1);
1989
1990 insert into msc_analyze_preference
1991 (userid, name, module, key, value, defaultset,
1992 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
1993 values (fnd_global.user_id, p_name, c_sda_pref_set, c_keys_fcst, p_fcst_row_list, 'N',
1994 sysdate, -1, sysdate, -1, -1);
1995 else
1996 update msc_analyze_preference
1997 set value = p_days
1998 where module = c_sda_pref_set and name = p_name and key = c_keys_days;
1999
2000 update msc_analyze_preference
2001 set value = p_weeks
2002 where module = c_sda_pref_set and name = p_name and key = c_keys_weeks;
2003
2004 update msc_analyze_preference
2005 set value = p_periods
2006 where module = c_sda_pref_set and name = p_name and key = c_keys_periods;
2007
2008 update msc_analyze_preference
2009 set value = p_factor
2010 where module = c_sda_pref_set and name = p_name and key = c_keys_factor;
2011
2012 update msc_analyze_preference
2013 set value = p_decimal_places
2014 where module = c_sda_pref_set and name = p_name and key = c_keys_decimals;
2015
2016 update msc_analyze_preference
2017 set value = p_sd_row_list
2018 where module = c_sda_pref_set and name = p_name and key = c_keys_sd;
2019
2020 update msc_analyze_preference
2021 set value = p_fcst_row_list
2022 where module = c_sda_pref_set and name = p_name and key = c_keys_fcst;
2023 end if;
2024 commit;
2025 end update_pref_set;
2026
2027 procedure attachment_flag(p_flag out nocopy number,
2028 p_inst_id number, p_entity_name number,
2029 p_pk_value1 number,
2030 p_pk_value2 number default null,
2031 p_pk_value3 number default null,
2032 p_pk_value4 number default null,
2033 p_pk_value5 number default null) is
2034 l_entity_name varchar2(40) := 'MSC_USER_NOTES';
2035
2036 cursor c_note_flag is
2037 select count(*)
2038 from msc_doc_attachments
2039 where sr_instance_id = nvl(p_inst_id, sr_instance_id)
2040 and entity_name = l_entity_name
2041 and (
2042 --for items
2043 (p_pk_value1 in (pk_value1, nvl(pk_value2,-1))
2044 and nvl(p_pk_value2,-1) = -1)
2045 or
2046 --for superssion
2047 (pk_value1 = p_pk_value1
2048 and pk_value2 = p_pk_value2)
2049 );
2050
2051 cursor c_chain_cur (l_plan_id number) is
2052 select count(*)
2053 from msc_item_substitutes b
2054 where b.plan_id = l_plan_id
2055 and b.inferred_flag=2
2056 and b.forward_rule=1
2057 and ( ( p_pk_value1 in (b.lower_item_id, b.higher_item_id)
2058 and nvl(p_pk_value2,-1) = -1 )
2059 or (b.lower_item_id = p_pk_value1 and b.higher_item_id = p_pk_value2)
2060 );
2061 l_temp number;
2062
2063 begin
2064 if p_entity_name is not null then
2065 l_entity_name := p_entity_name;
2066 end if;
2067
2068 if (p_pk_value3 is not null) then
2069 open c_chain_cur(p_pk_value3);
2070 fetch c_chain_cur into l_temp;
2071 close c_chain_cur;
2072 if (l_temp = 0) then
2073 p_flag := 2;
2074 return;
2075 end if;
2076 end if;
2077
2078 open c_note_flag;
2079 fetch c_note_flag into p_flag;
2080 close c_note_flag;
2081 if p_flag = 0 then
2082 p_flag := 2;
2083 else
2084 p_flag := 1;
2085 end if;
2086 end attachment_flag;
2087
2088 end MSC_SDA_UTILS;