[Home] [Help]
PACKAGE BODY: APPS.MSD_STRIPE_DEMAND_PLAN
Source
1 PACKAGE BODY msd_stripe_demand_plan AS
2 /* $Header: msdstrpb.pls 120.11.12010000.2 2008/09/10 13:08:31 nallkuma ship $ */
3
4 --
5 -- Private procedures
6 --
7
8 /********************************************************************
9 * Build the level values stripe in a temporary table.
10 */
11
12 procedure create_level_val_stripe (errbuf out nocopy varchar2,
13 retcode out nocopy varchar2,
14 p_demand_plan_id in number,
15 p_stripe_instance in varchar2,
16 p_stripe_level_id in number,
17 p_stripe_sr_level_pk in varchar2);
18
19 procedure create_level_val_stripe_stream (errbuf out nocopy varchar2,
20 retcode out nocopy varchar2,
21 p_demand_plan_id in number,
22 p_stripe_stream_name in varchar2,
23 p_stripe_stream_desig in varchar2);
24
25 /********************************************************************
26 * Helper for create_level_val_stripe.
27 * Walk up a hierarchy and insert parents into temporary table.
28 */
29 procedure walk_up_hierarchy (errbuf out nocopy varchar2,
30 retcode out nocopy varchar2,
31 p_demand_plan_id in number,
32 p_level_id in number);
33
34 /********************************************************************
35 * Helper for create_level_val_stripe.
36 * Walk down a hierarchy and insert children into temporary table.
37 */
38
39 procedure walk_down_hierarchy (errbuf out nocopy varchar2,
40 retcode out nocopy varchar2,
41 p_demand_plan_id in number,
42 p_level_id in number);
43
44 /********************************************************************
45 * Helper for create_level_val_stripe.
46 * Insert orgs into stripe which are related to level values in
47 * stripe.
48 */
49
50 procedure insert_related_orgs (errbuf out nocopy varchar2,
51 retcode out nocopy varchar2,
52 p_demand_plan_id in number,
53 p_level_id in number);
54
55 /********************************************************************
56 * Helper for create_level_val_stripe.
57 * Insert level values into stripe which are related to orgs in
58 * stripe.
59 */
60
61 procedure insert_related_level_values (errbuf out nocopy varchar2,
62 retcode out nocopy varchar2,
63 p_demand_plan_id in number,
64 p_level_id in number);
65
66
67 /********************************************************************
68 * Helper for create_level_val_stripe.
69 * Insert level values for non-striped dimensions.
70 */
71
72 procedure insert_non_stripe_level_values (errbuf out nocopy varchar2,
73 retcode out nocopy varchar2,
74 p_demand_plan_id in number,
75 p_insert_rep in varchar2,
76 p_insert_geo in varchar2);
77
78 /********************************************************************
79 * Helper for create_level_val_stripe.
80 * Determine which level values should be striped.
81 */
82
83 procedure handle_remaining_level_values (errbuf out nocopy varchar2,
84 retcode out nocopy varchar2,
85 p_demand_plan_id in number);
86
87
88 /********************************************************************
89 * Copies the level values stripe from temporary table to stripe table.
90 */
91
92 procedure copy_level_val_stripe (errbuf out nocopy varchar2,
93 retcode out nocopy varchar2,
94 p_demand_plan_id in number);
95
96 /********************************************************************
97 * Insert Fact Data into Stripe.
98 */
99
100 procedure insert_fact_data (errbuf out nocopy varchar2,
101 retcode out nocopy varchar2,
102 p_demand_plan_id in number,
103 p_fast_refresh in varchar2);
104
105 /********************************************************************
106 * Delete Fact Data from Stripe.
107 */
108
109 procedure delete_fact_data (errbuf out nocopy varchar2,
110 retcode out nocopy varchar2,
111 p_demand_plan_id in number);
112
113 /********************************************************************
114 * Update Parameters Stripe Information.
115 */
116
117 procedure update_dp_parameters_ds (errbuf out nocopy varchar2,
118 retcode out nocopy varchar2,
119 p_demand_plan_id in number);
120 --
121 -- Private functions
122 --
123
124 /*********************************************************************
125 * Returns True or False if stripe in temp table equals previous stripe
126 */
127
128 function is_level_val_stripe_equal (errbuf out nocopy varchar2,
129 retcode out nocopy varchar2,
130 p_demand_plan_id in number) return varchar2;
131
132 /***********************************************************************
133 * Returns True or False if level values collected is newer than stripe.
134 */
135
136 function is_level_val_collected (errbuf out nocopy varchar2,
137 retcode out nocopy varchar2,
138 p_demand_plan_id in number) return varchar2;
139
140 function is_dimension_changed (errbuf out nocopy varchar2,
141 retcode out nocopy varchar2,
142 p_demand_plan_id in number) return varchar2;
143
144
145 function is_event_collected (errbuf out nocopy varchar2,
146 retcode out nocopy varchar2,
147 p_demand_plan_id in number) return varchar2;
148
149 /***********************************************************************
150 * Returns True or False if user changed stripe level value from
151 * that previously used.
152 */
153
154 function is_new_stripe (errbuf out nocopy varchar2,
155 retcode out nocopy varchar2,
156 p_demand_plan_id in number,
157 p_stripe_instance in varchar2,
158 p_stripe_level_id in number,
159 p_stripe_sr_level_pk in varchar2,
160 p_build_stripe_level_pk in number,
161 p_build_stripe_stream_name in varchar2) return varchar2;
162
163 /***********************************************************************
164 * Returns True or False if user changed stripe stream value from
165 * that previously used.
166 */
167
168 function is_new_stream_stripe (errbuf out nocopy varchar2,
169 retcode out nocopy varchar2,
170 p_demand_plan_id in number,
171 p_build_stripe_level_pk in number,
172 p_stripe_stream_name in varchar2,
173 p_stripe_stream_desig in varchar2,
174 p_build_stripe_stream_name in varchar2,
175 p_build_stripe_stream_desig in varchar2,
176 p_build_stripe_stream_ref_num in number) return varchar2;
177
178 /***********************************************************************
179 * Returns True or False if user changed stripe stream value from
180 * that previously used.
181 */
182
183 function is_new_lob_stream_stripe (errbuf out nocopy varchar2,
184 retcode out nocopy varchar2,
185 p_demand_plan_id in number,
186 p_stripe_instance in varchar2,
187 p_stripe_level_id in number,
188 p_stripe_sr_level_pk in varchar2,
189 p_build_stripe_level_pk in number,
190 p_stripe_stream_name in varchar2,
191 p_stripe_stream_desig in varchar2,
192 p_build_stripe_stream_name in varchar2,
193 p_build_stripe_stream_desig in varchar2,
194 p_build_stripe_stream_ref_num in number) return varchar2;
195
196 /************************************************************************
197 * Stripe Demand Plan w/LOB defined only.
198 */
199 procedure stripe_demand_plan_lob (errbuf out nocopy varchar2,
200 retcode out nocopy varchar2,
201 p_demand_plan_id in number,
202 p_stripe_instance in varchar2,
203 p_stripe_level_id in number,
204 p_stripe_sr_level_pk in varchar2,
205 p_build_stripe_level_pk in varchar2,
206 p_build_stripe_stream_name in varchar2);
207
208 /************ TO BE CODED ************************/
209 /************************************************************************
210 * Stripe Demand Plan w/Stream defined only.
211 */
212 procedure stripe_demand_plan_stream (errbuf out nocopy varchar2,
213 retcode out nocopy varchar2,
214 p_demand_plan_id in number,
215 p_build_stripe_level_pk in number,
216 p_stripe_stream_name in varchar2,
217 p_stripe_stream_desig in varchar2,
218 p_build_stripe_stream_name in varchar2,
219 p_build_stripe_stream_desig in varchar2,
220 p_build_stripe_stream_ref_num in number);
221
222 /************************************************************************
223 * Stripe Demand Plan w/LOB and Stream defined.
224 */
225 procedure stripe_demand_plan_lob_stream (errbuf out nocopy varchar2,
226 retcode out nocopy varchar2,
227 p_demand_plan_id in number,
228 p_stripe_instance in varchar2,
229 p_stripe_level_id in number,
230 p_stripe_sr_level_pk in varchar2,
231 p_build_stripe_level_pk in number,
232 p_stripe_stream_name in varchar2,
233 p_stripe_stream_desig in varchar2,
234 p_build_stripe_stream_name in varchar2,
235 p_build_stripe_stream_desig in varchar2,
236 p_build_stripe_stream_ref_num in number);
237
238 /***************************************************************************
239 * Check to see if values are populated for a level from associations.
240 * If not then dump all level values for that level.
241 */
242 function chk_insert_no_associations (errbuf out nocopy varchar2,
243 retcode out nocopy varchar2,
244 p_demand_plan_id in number,
245 p_level_id in number) return varchar2;
246
247 procedure chk_insert_org_no_associations (errbuf out nocopy varchar2,
248 retcode out nocopy varchar2,
249 p_demand_plan_id in number,
250 p_level_id in number);
251
252 procedure create_lvl_val_stripe_strm_lob (errbuf out nocopy varchar2,
253 retcode out nocopy varchar2,
254 p_demand_plan_id in number,
255 p_stripe_instance in varchar2,
256 p_stripe_level_id in number,
257 p_stripe_sr_level_pk in varchar2 ,
258 p_stripe_stream_name in varchar2,
259 p_stripe_stream_desig in varchar2);
260
261 procedure insert_stream_items(errbuf out nocopy varchar2,
262 retcode out nocopy varchar2,
263 p_demand_plan_id in number,
264 p_stripe_stream_name in varchar2,
265 p_stripe_stream_desig in varchar2,
266 p_dim_code in varchar2);
267
268 procedure filter_stream_items(errbuf out nocopy varchar2,
269 retcode out nocopy varchar2,
270 p_demand_plan_id in number,
271 p_stripe_stream_name in varchar2,
272 p_stripe_stream_desig in varchar2,
273 p_dim_code in varchar2);
274 procedure insert_supercession_items (errbuf out nocopy varchar2,
275 retcode out nocopy varchar2,
276 p_demand_plan_id in number);
277
278 procedure handle_fact_data (errbuf out nocopy varchar2,
279 retcode out nocopy varchar2,
280 p_demand_plan_id in number);
281
282 procedure ins_pseudo_level_pk (errbuf out nocopy varchar2,
283 retcode out nocopy varchar2,
284 p_demand_plan_id in number);
285
286 procedure ins_other_level_val (errbuf out nocopy varchar2,
287 retcode out nocopy varchar2,
288 p_demand_plan_id in number);
289 procedure ins_all_level_val (errbuf out nocopy varchar2,
290 retcode out nocopy varchar2,
291 p_demand_plan_id in number);
292
293 function get_desig_clmn_name (p_cs_id in number) return VARCHAR2;
294
295 function is_dim_in_plan (p_demand_plan_id in number, p_dim_code in varchar2) return varchar2;
296
297 /********************************************************************
298 * ISO Code Change
299 * This function checks whether there is any change in the ISO orgs
300 * attached to the demand plan
301 */
302 FUNCTION is_iso_orgs_changed (errbuf OUT NOCOPY VARCHAR2,
303 retcode OUT NOCOPY VARCHAR2,
304 p_demand_plan_id IN NUMBER)
305 RETURN VARCHAR2;
306
307 --
308 -- Constants
309 --
310
311 C_TRUE Constant varchar2(30):='TRUE';
312 C_FALSE Constant varchar2(30):='FALSE';
313 C_FAST_REF Constant varchar2(30):='FAST';
314 C_FULL_REF Constant varchar2(30):='FULL';
315 C_FACT Constant varchar2(30):='FACT';
316 C_LVL_VAL Constant varchar2(30):='LEVEL_VALUES';
317 C_DIM Constant varchar2(30):='DIMENSION';
318 C_EVENT Constant varchar2(30):='EVENT';
319 C_NPI_BASE_PRODUCT Constant varchar2(1):='1';
320 C_YES_FLAG Constant varchar2(30):='Y';
321 C_PRE_PROC Constant varchar2(30) := 'PRE-PROCESS';
322 C_POS_PROC Constant varchar2(30) := 'POST-PROCESS';
323 C_PROCESS Constant varchar2(30) := 'PROCESSED';
324 C_PSEUDO_PK Constant number := 0;
325
326 C_LEVEL_PLAN Constant number := -1;
327
328 C_ITEM_LEVEL_ID Constant number := 1;
329 C_ORGS_LEVEL_ID Constant number := 7;
330 C_SHIP_LEVEL_ID Constant number := 11;
331 C_REPS_LEVEL_ID Constant number := 18;
332
333 C_PRD_DIM_CODE Constant varchar2(30) := 'PRD';
334 C_ORG_DIM_CODE Constant varchar2(30) := 'ORG';
335 C_GEO_DIM_CODE Constant varchar2(30) := 'GEO';
336 C_REP_DIM_CODE Constant varchar2(30) := 'REP';
337 C_TIM_DIM_CODE Constant varchar2(30) := 'TIM';
338
339 FATAL_ERROR Constant varchar2(30):='FATAL_ERROR';
340 ERROR Constant varchar2(30):='ERROR';
341 WARNING Constant varchar2(30):='WARNING';
342 INFORMATION Constant varchar2(30):='INFORMATION';
343 HEADING Constant varchar2(30):='HEADING';
344 SECTION Constant varchar2(30):='SECTION';
345 SUCCESS Constant varchar2(30):='SUCCESS';
346
347 l_system_attribute1 VARCHAR2(240) := MSD_COMMON_UTILITIES.GET_SYSTEM_ATTRIBUTE1_DESC('I'); --Bug#4249928
348
349 l_debug VARCHAR2(240) := NVL(fnd_profile.value('MRP_DEBUG'), 'N');
350 l_result varchar2(300);
351
352 g_ret_code number;
353 --
354 -- USED BY DISPLAY_MESSAGE
355 --
356 l_last_msg_type varchar2(30);
357 --
358 -- Define Exception
359 --
360 EX_FATAL_ERROR Exception;
361 --
362 -- Fast Refresh or Full refresh for Fact Data.
363 --
364 l_fast_refresh_fact varchar2(100);
365 --
366 -- Status for Customization
367 --
368 l_status varchar2(100);
369
370 --
371 -- get demand plan record
372 --
373
374 CURSOR get_dp (p_demand_plan_id NUMBER) IS
375 SELECT demand_plan_id,
376 demand_plan_name,
377 stripe_instance,
378 stripe_level_id,
379 stripe_sr_level_pk,
380 build_stripe_level_pk,
381 stripe_stream_name,
382 stripe_stream_desig,
383 build_stripe_stream_name,
384 build_stripe_stream_desig,
385 build_stripe_stream_ref_num
386 FROM msd_demand_plans
387 WHERE demand_plan_id = p_demand_plan_id;
388
389 --
390 -- get last refresh num
391 --
392
393 CURSOR get_refresh_num(p_name in varchar2, p_desig in varchar2) IS
394 select last_refresh_num
395 from msd_cs_data_headers
396 where cs_definition_id = (select cs_definition_id
397 from msd_cs_definitions
398 where name = p_name)
399 and cs_name = nvl(p_desig, cs_name)
400 order by last_refresh_num desc;
401
402 --
403 -- Private functions/proceudres
404 --
405 --
406 -- Store result
407 --
408 Procedure calc_result ( p_msg_type in varchar2) is
409 Begin
410 if p_msg_type = FATAL_ERROR then
411 g_ret_code := 4;
412 l_result := FATAL_ERROR;
413 elsif p_msg_type = ERROR then
414 g_ret_code := 2;
415 l_result := p_msg_type;
416 elsif p_msg_type = WARNING then
417 if g_ret_code <> 2 then
418 g_ret_code := 1;
419 l_result := p_msg_type;
420 end if;
421 end if;
422 End;
423 --
424 Procedure show_message(p_text in varchar2) is
425 Begin
426 fnd_file.put_line(fnd_file.log, p_text);
427 -- dbms_output.put_line(p_text);
428 end;
429
430 Procedure debug_out(p_text in varchar2) is
431 i number := 1;
432 Begin
433 while i<= length(p_text) loop
434 fnd_file.put_line(fnd_file.output, substr(p_text, i, 90));
435 -- dbms_output.put_line(substr(p_text, i, 90));
436 i := i+90;
437 end loop;
438 end;
439 --
440
441 Procedure display_message(p_text varchar2, msg_type varchar2 default null) is
442 l_tab varchar2(4):=' ';
443 L_MAX_LENGTH number:=90;
444 Begin
445 if msg_type = SECTION then
446 if nvl(l_last_msg_type, 'xx') <> SECTION then
447 show_message('');
448 end if;
449 --
450 show_message( substr(p_text, 1, L_MAX_LENGTH) );
451 --
452 elsif msg_type in (INFORMATION, HEADING) then
453 show_message( l_tab || substr(p_text, 1, L_MAX_LENGTH));
454 else
455 show_message( l_tab || rpad(p_text, L_MAX_LENGTH) || ' ' || msg_type );
456 end if;
457 --
458 if msg_type in (ERROR, WARNING, FATAL_ERROR) then
459 calc_result (msg_type);
460 end if;
461 --
462 if msg_type = FATAL_ERROR then
463 show_message(' ');
464 show_message( l_tab || 'Exiting Demand Plan validation process with FATAL ERROR');
465 raise EX_FATAL_ERROR;
466 end if;
467 --
468 l_last_msg_type := msg_type;
469 End;
470 --
471 Procedure Blank_Line is
472 Begin
473 fnd_file.put_line(fnd_file.log, '');
474 -- dbms_output.put_line('');
475 End;
476 --
477
478 Procedure set_demand_plan(
479 p_demand_plan_id in number) is
480
481 begin
482
483 if (p_demand_plan_id is not null) then
484
485 delete from msd_dp_session;
486
487 insert into msd_dp_session
488 ( demand_plan_id )
489 values
490 ( p_demand_plan_id );
491
492 -- Incorporate later into msd_analyze
493 --fnd_stats.gather_table_stats('MSD', 'MSD_DP_SESSION', 10, 4);
494
495 end if;
496
497 end;
498
499
500 Procedure stripe_demand_plan(
501 errbuf out nocopy varchar2,
502 retcode out nocopy varchar2,
503 p_demand_plan_id in number) is
504
505 l_dp_rec get_dp%rowtype;
506
507 Begin
508
509 if l_debug = C_YES_FLAG then
510 debug_out( 'Entering stripe_demand_plan ' || to_char(sysdate, 'hh24:mi:ss'));
511 end if;
512 --
513 -- initialize
514 --
515 l_result := SUCCESS;
516 g_ret_code := 0;
517 --
518 -- find demand plan
519 --
520
521 --
522 -- Retrieve Demand Plan information.
523 --
524 open get_dp(p_demand_plan_id);
525 fetch get_dp into l_dp_rec;
526 close get_dp;
527
528
529 --
530 -- If both LOB and Stream is specified.
531 --
532 if ((l_dp_rec.stripe_sr_level_pk is not null) and (l_dp_rec.stripe_stream_name is not null)) then
533
534 msd_stripe_custom.custom_populate (errbuf,
535 retcode,
536 l_dp_rec.demand_plan_id,
537 C_PRE_PROC,
538 l_status,
539 null,null,null,null,null);
540
541 if (l_status is null or l_status <> C_PROCESS) then
542 stripe_demand_plan_lob_stream (errbuf,
543 retcode,
544 l_dp_rec.demand_plan_id,
545 l_dp_rec.stripe_instance,
546 l_dp_rec.stripe_level_id,
547 l_dp_rec.stripe_sr_level_pk,
548 l_dp_rec.build_stripe_level_pk,
549 l_dp_rec.stripe_stream_name,
550 l_dp_rec.stripe_stream_desig,
551 l_dp_rec.build_stripe_stream_name,
552 l_dp_rec.build_stripe_stream_desig,
553 l_dp_rec.build_stripe_stream_ref_num);
554 end if;
555
556 msd_stripe_custom.custom_populate (errbuf,
557 retcode,
558 l_dp_rec.demand_plan_id,
559 C_POS_PROC,
560 l_status,
561 null,null,null,null,null);
562
563
564 handle_fact_data (errbuf, retcode, p_demand_plan_id);
565
566 --
567 -- If LOB, but not Stream is specified.
568 --
569 elsif ((l_dp_rec.stripe_sr_level_pk is not null) and (l_dp_rec.stripe_stream_name is null)) then
570
571 msd_stripe_custom.custom_populate (errbuf,
572 retcode,
573 l_dp_rec.demand_plan_id,
574 C_PRE_PROC,
575 l_status,
576 null,null,null,null,null);
577
578 if (l_status is null or l_status <> C_PROCESS) then
579 stripe_demand_plan_lob (errbuf,
580 retcode,
581 l_dp_rec.demand_plan_id,
582 l_dp_rec.stripe_instance,
583 l_dp_rec.stripe_level_id,
584 l_dp_rec.stripe_sr_level_pk,
585 l_dp_rec.build_stripe_level_pk,
586 l_dp_rec.build_stripe_stream_name);
587 end if;
588
589 msd_stripe_custom.custom_populate (errbuf,
590 retcode,
591 l_dp_rec.demand_plan_id,
592 C_POS_PROC,
593 l_status,
594 null,null,null,null,null);
595
596
597 handle_fact_data (errbuf, retcode, p_demand_plan_id);
598
599 --
600 -- If LOB is not specified, but Stream is specified.
601 --
602 elsif ((l_dp_rec.stripe_sr_level_pk is null) and (l_dp_rec.stripe_stream_name is not null)) then
603
604 msd_stripe_custom.custom_populate (errbuf,
605 retcode,
606 l_dp_rec.demand_plan_id,
607 C_PRE_PROC,
608 l_status,
609 null,null,null,null,null);
610
611 if (l_status is null or l_status <> C_PROCESS) then
612 stripe_demand_plan_stream (errbuf,
613 retcode,
614 l_dp_rec.demand_plan_id,
615 l_dp_rec.build_stripe_level_pk,
616 l_dp_rec.stripe_stream_name,
617 l_dp_rec.stripe_stream_desig,
618 l_dp_rec.build_stripe_stream_name,
619 l_dp_rec.build_stripe_stream_desig,
620 l_dp_rec.build_stripe_stream_ref_num);
621 end if;
622
623 msd_stripe_custom.custom_populate (errbuf,
624 retcode,
625 l_dp_rec.demand_plan_id,
626 C_POS_PROC,
627 l_status,
628 null,null,null,null,null);
629
630
631 handle_fact_data (errbuf, retcode, p_demand_plan_id);
632 --
633 -- If LOB is not specified and Stream is not specified, but existing partition exists.
634 --
635 elsif ((l_dp_rec.build_stripe_level_pk is not null) or (l_dp_rec.build_stripe_stream_name is not null)) then
636
637 delete from msd_level_values_ds
638 where demand_plan_id = p_demand_plan_id;
639
640 delete from msd_cs_Data_ds
641 where demand_plan_id = p_demand_plan_id;
642
643 delete from msd_dp_parameters_ds
644 where demand_plan_id = p_demand_plan_id;
645
646 update msd_demand_plans
647 set build_stripe_level_pk = null,
648 build_stripe_stream_name = null,
649 build_stripe_stream_desig = null,
650 build_stripe_stream_ref_num = null
651 where demand_plan_id = p_demand_plan_id;
652 --
653 -- If LOB is not specified and Stream is not specified, and no existing partition exists.
654 --
655 else
656 null;
657 end if;
658
659 if l_debug = C_YES_FLAG then
660 debug_out( 'Exiting stripe_demand_plan ' || to_char(sysdate, 'hh24:mi:ss'));
661 end if;
662
663 Exception when others then
664 retcode := 2;
665 errbuf := substr( sqlerrm, 1, 80);
666 rollback;
667 End;
668
669
670 procedure walk_down_hierarchy (errbuf out nocopy varchar2,
671 retcode out nocopy varchar2,
672 p_demand_plan_id in number,
673 p_level_id in number) is
674
675 cursor c0 is
676 select dimension_code
677 from msd_levels ml
678 where level_id = p_level_id;
679
680 cursor c1(p_hierarchy_id in number,
681 p_parent_level_id in number) is
682 select mhl.level_id
683 from msd_hierarchy_levels mhl
684 where mhl.hierarchy_id = p_hierarchy_id
685 and mhl.parent_level_id = p_parent_level_id;
686
687 cursor c2 (p_dimension_code in varchar2) is
688 select hierarchy_id
689 from msd_hierarchies
690 where dimension_code = p_dimension_code;
691
692 x_current_parent_level_id number;
693 x_child_level number;
694 l_rec_0 c0%rowtype;
695
696 begin
697
698 if l_debug = C_YES_FLAG then
699 debug_out( 'Entering walk_down_hierarchy ' || to_char(sysdate, 'hh24:mi:ss'));
700 end if;
701
702
703 open c0;
704 fetch c0 into l_rec_0;
705 close c0;
706
707 for c2_rec in c2 (l_rec_0.dimension_code) loop
708
709 x_current_parent_level_id := p_level_id;
710
711 loop
712
713 open c1(c2_rec.hierarchy_id, x_current_parent_level_id);
714 fetch c1 into x_child_level;
715
716 if (c1%NOTFOUND) then
717 close c1;
718 exit;
719 end if;
720
721 close c1;
722
723 insert into msd_level_values_ds_temp
724 (
725 DEMAND_PLAN_ID,
726 INSTANCE,
727 LEVEL_ID,
728 SR_LEVEL_PK,
729 LEVEL_PK,
730 LAST_UPDATE_DATE,
731 LAST_UPDATED_BY,
732 CREATION_DATE,
733 CREATED_BY,
734 SYSTEM_ATTRIBUTE1,
735 SYSTEM_ATTRIBUTE2,
736 DP_ENABLED_FLAG
737 )
738 select distinct
739 p_demand_plan_id,
740 mlv.instance,
741 mlv.level_id,
742 mlv.sr_level_pk,
743 mlv.level_pk,
744 sysdate,
745 fnd_global.user_id,
746 sysdate,
747 fnd_global.user_id,
748 mlv.system_attribute1,
749 mlv.system_attribute2,
750 mlv.dp_enabled_flag
751 from msd_level_values mlv,
752 msd_level_values_ds_temp mld,
753 msd_level_associations mla
754 where mla.level_id = x_child_level
755 and mla.parent_level_id = x_current_parent_level_id
756 and mla.parent_level_id = mld.level_id
757 and mla.sr_parent_level_pk = mld.sr_level_pk
758 and mlv.instance = mla.instance
759 and mla.instance = mld.instance
760 and mlv.level_id = mla.level_id
761 and mlv.sr_level_pk = mla.sr_level_pk
762 and mld.demand_plan_id = p_demand_plan_id
763 minus
764 select p_demand_plan_id,
765 instance,
766 level_id,
767 sr_level_pk,
768 level_pk,
769 sysdate,
770 fnd_global.user_id,
771 sysdate,
772 fnd_global.user_id,
773 system_attribute1,
774 system_attribute2,
775 dp_enabled_flag
776 from msd_level_values_ds_temp
777 where demand_plan_id = p_demand_plan_id;
778
779
780 x_current_parent_level_id := x_child_level;
781
782 end loop;
783 end loop;
784
785 if l_debug = C_YES_FLAG then
786 debug_out( 'Exiting walk_down_hierarchy ' || to_char(sysdate, 'hh24:mi:ss'));
787 end if;
788
789 end;
790
791 procedure walk_up_hierarchy (errbuf out nocopy varchar2,
792 retcode out nocopy varchar2,
793 p_demand_plan_id in number,
794 p_level_id in number) is
795
796 cursor c0 is
797 select dimension_code
798 from msd_levels ml
799 where level_id = p_level_id;
800
801 cursor c1(p_hierarchy_id in number,
802 p_child_level_id in number) is
803 select mhl.parent_level_id
804 from msd_hierarchy_levels mhl
805 where mhl.hierarchy_id = p_hierarchy_id
806 and mhl.level_id = p_child_level_id;
807
808 cursor c2 (p_dimension_code in varchar2) is
809 select hierarchy_id
810 from msd_hierarchies
811 where dimension_code = p_dimension_code;
812
813 x_current_child_level_id number;
814 x_parent_level number;
815 l_rec_0 c0%rowtype;
816
817 begin
818
819 if l_debug = C_YES_FLAG then
820 debug_out( 'Entering walk_up_hierarchy ' || to_char(sysdate, 'hh24:mi:ss'));
821 end if;
822
823 open c0;
824 fetch c0 into l_rec_0;
825 close c0;
826
827 for c2_rec in c2 (l_rec_0.dimension_code) loop
828
829 x_current_child_level_id := p_level_id;
830
831 loop
832
833 open c1(c2_rec.hierarchy_id, x_current_child_level_id);
834 fetch c1 into x_parent_level;
835
836 if (c1%NOTFOUND) then
837 close c1;
838 exit;
839 end if;
840
841 close c1;
842
843 insert into msd_level_values_ds_temp
844 (
845 DEMAND_PLAN_ID,
846 INSTANCE,
847 LEVEL_ID,
848 SR_LEVEL_PK,
849 LEVEL_PK,
850 LAST_UPDATE_DATE,
851 LAST_UPDATED_BY,
852 CREATION_DATE,
853 CREATED_BY,
854 SYSTEM_ATTRIBUTE1,
855 SYSTEM_ATTRIBUTE2,
856 DP_ENABLED_FLAG
857 )
858 select distinct
859 p_demand_plan_id,
860 mlv.instance,
861 mlv.level_id,
862 mlv.sr_level_pk,
863 mlv.level_pk,
864 sysdate,
865 fnd_global.user_id,
866 sysdate,
867 fnd_global.user_id,
868 mlv.system_attribute1,
869 mlv.system_attribute2,
870 mlv.dp_enabled_flag
871 from msd_level_values mlv,
872 msd_level_values_ds_temp mld,
873 msd_level_associations mla
874 where mla.parent_level_id = x_parent_level
875 and mla.level_id = x_current_child_level_id
876 and mla.level_id = mld.level_id
877 and mla.sr_level_pk = mld.sr_level_pk
878 and mlv.instance = mla.instance
879 and mla.instance = mld.instance
880 and mlv.level_id = mla.parent_level_id
881 and mlv.sr_level_pk = mla.sr_parent_level_pk
882 and mld.demand_plan_id = p_demand_plan_id
883 minus
884 select p_demand_plan_id,
885 instance,
886 level_id,
887 sr_level_pk,
888 level_pk,
889 sysdate,
890 fnd_global.user_id,
891 sysdate,
892 fnd_global.user_id,
893 system_attribute1,
894 system_attribute2,
895 dp_enabled_flag
896 from msd_level_values_ds_temp
897 where demand_plan_id = p_demand_plan_id;
898
899 x_current_child_level_id := x_parent_level;
900
901 end loop;
902 end loop;
903
904 if l_debug = C_YES_FLAG then
905 debug_out( 'Exiting walk_up_hierarchy ' || to_char(sysdate, 'hh24:mi:ss'));
906 end if;
907
908 end;
909
910 procedure insert_related_orgs (errbuf out nocopy varchar2,
911 retcode out nocopy varchar2,
912 p_demand_plan_id in number,
913 p_level_id in number) is
914
915 begin
916
917 if l_debug = C_YES_FLAG then
918 debug_out( 'Entering insert_related_orgs ' || to_char(sysdate, 'hh24:mi:ss'));
919 end if;
920
921
922 insert into msd_level_values_ds_temp
923 (
924 DEMAND_PLAN_ID,
925 INSTANCE,
926 LEVEL_ID,
927 SR_LEVEL_PK,
928 LEVEL_PK,
929 LAST_UPDATE_DATE,
930 LAST_UPDATED_BY,
931 CREATION_DATE,
932 CREATED_BY,
933 SYSTEM_ATTRIBUTE1,
934 SYSTEM_ATTRIBUTE2,
935 DP_ENABLED_FLAG
936 )
937 select p_demand_plan_id,
938 mlv.instance,
939 mlv.level_id,
940 mlv.sr_level_pk,
941 mlv.level_pk,
942 sysdate,
943 fnd_global.user_id,
944 sysdate,
945 fnd_global.user_id,
946 mlv.system_attribute1,
947 mlv.system_attribute2,
948 mlv.dp_enabled_flag
949 from msd_level_org_asscns mlo,
950 msd_level_values mlv,
951 msd_level_values_ds_temp mld
952 where mld.demand_plan_id = p_demand_plan_id
953 and mlo.instance = mld.instance
954 and mlo.org_level_id = p_level_id
955 and mlo.org_sr_level_pk = mlv.sr_level_pk
956 and mlo.instance = mlv.instance
957 and mlo.org_level_id = mlv.level_id
958 and mlo.level_id = mld.level_id
959 and mlo.sr_level_pk = mld.sr_level_pk
960 minus
961 select p_demand_plan_id,
962 instance,
963 level_id,
964 sr_level_pk,
965 level_pk,
966 sysdate,
967 fnd_global.user_id,
968 sysdate,
969 fnd_global.user_id,
970 system_attribute1,
971 system_attribute2,
972 dp_enabled_flag
973 from msd_level_values_ds_temp
974 where demand_plan_id = p_demand_plan_id;
975
976 chk_insert_org_no_associations (errbuf,
977 retcode,
978 p_demand_plan_id,
979 C_ORGS_LEVEL_ID);
980
981 if l_debug = C_YES_FLAG then
982 debug_out( 'Exiting insert_related_orgs ' || to_char(sysdate, 'hh24:mi:ss'));
983 end if;
984
985 end;
986
987 procedure insert_related_level_values (errbuf out nocopy varchar2,
988 retcode out nocopy varchar2,
989 p_demand_plan_id in number,
990 p_level_id in number) is
991
992 cursor chk_dim (p_dim_code in varchar2) is
993 select C_TRUE
994 from msd_dp_dimensions
995 where demand_plan_id = p_demand_plan_id
996 and dimension_code = p_dim_code;
997
998 /* ISO Code Change */
999 CURSOR c_get_internal_desc
1000 IS
1001 SELECT meaning
1002 FROM fnd_lookup_values_vl
1003 WHERE
1004 lookup_type = 'MSD_LEVEL_VALUE_DESC'
1005 AND lookup_code = 'I';
1006
1007 v_is_dp_dim varchar2(30) := C_FALSE;
1008
1009 /* ISO Code Change */
1010 v_internal_desc VARCHAR2(100);
1011
1012 begin
1013
1014 if l_debug = C_YES_FLAG then
1015 debug_out( 'Entering insert_related_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1016 end if;
1017
1018 /* ISO Code Change */
1019 OPEN c_get_internal_desc;
1020 FETCH c_get_internal_desc INTO v_internal_desc;
1021 CLOSE c_get_internal_desc;
1022
1023 -- For Sales Rep and Ship to Location, only insert
1024 -- related level values if attached to Demand Plan.
1025 -- Otherwise, insert all level values.
1026
1027 if (p_level_id in (C_REPS_LEVEL_ID)) then
1028 open chk_dim(C_REP_DIM_CODE);
1029 fetch chk_dim into v_is_dp_dim;
1030 close chk_dim;
1031 elsif (p_level_id in (C_SHIP_LEVEL_ID)) then
1032 open chk_dim(C_GEO_DIM_CODE);
1033 fetch chk_dim into v_is_dp_dim;
1034 close chk_dim;
1035 else
1036 v_is_dp_dim := C_TRUE;
1037 end if;
1038
1039 if (v_is_dp_dim = C_TRUE) then
1040
1041 insert into msd_level_values_ds_temp
1042 (
1043 DEMAND_PLAN_ID,
1044 INSTANCE,
1045 LEVEL_ID,
1046 SR_LEVEL_PK,
1047 LEVEL_PK,
1048 LAST_UPDATE_DATE,
1049 LAST_UPDATED_BY,
1050 CREATION_DATE,
1051 CREATED_BY,
1052 SYSTEM_ATTRIBUTE1,
1053 SYSTEM_ATTRIBUTE2,
1054 DP_ENABLED_FLAG
1055 )
1056 select p_demand_plan_id,
1057 mlv.instance,
1058 mlv.level_id,
1059 mlv.sr_level_pk,
1060 mlv.level_pk,
1061 sysdate,
1062 fnd_global.user_id,
1063 sysdate,
1064 fnd_global.user_id,
1065 mlv.system_attribute1,
1066 mlv.system_attribute2,
1067 mlv.dp_enabled_flag
1068 from msd_level_org_asscns mlo,
1069 msd_level_values mlv,
1070 msd_level_values_ds_temp mld
1071 where mld.demand_plan_id = p_demand_plan_id
1072 and mlo.instance = mld.instance
1073 -- and mlo.org_level_id = mld.level_id Bug# 4929528
1074 and mlo.org_level_id = decode(p_level_id,C_ITEM_LEVEL_ID, 7, 8) -- Bug# 4929528
1075 and mld.level_id = decode(p_level_id,C_ITEM_LEVEL_ID, 7, 8) -- Bug# 4929528
1076 and mlo.org_sr_level_pk = mld.sr_level_pk
1077 and mlo.instance = mlv.instance
1078 and mlo.level_id = mlv.level_id
1079 and mlo.level_id = p_level_id
1080 and mlo.sr_level_pk = mlv.sr_level_pk
1081 and nvl(mlv.system_attribute1, '123') <> v_internal_desc -- ISO Code Change
1082 minus
1083 select p_demand_plan_id,
1084 instance,
1085 level_id,
1086 sr_level_pk,
1087 level_pk,
1088 sysdate,
1089 fnd_global.user_id,
1090 sysdate,
1091 fnd_global.user_id,
1092 system_attribute1,
1093 system_attribute2,
1094 dp_enabled_flag
1095 from msd_level_values_ds_temp
1096 where demand_plan_id = p_demand_plan_id;
1097
1098 /* ISO Code Change - Only for GEO dimension - Insert the internal sites for the
1099 * ISO orgs attached to the plan
1100 */
1101 IF (p_level_id in (C_SHIP_LEVEL_ID)) THEN
1102
1103 INSERT INTO msd_level_values_ds_temp
1104 ( DEMAND_PLAN_ID,
1105 INSTANCE,
1106 LEVEL_ID,
1107 SR_LEVEL_PK,
1108 LEVEL_PK,
1109 LAST_UPDATE_DATE,
1110 LAST_UPDATED_BY,
1111 CREATION_DATE,
1112 CREATED_BY,
1113 SYSTEM_ATTRIBUTE1,
1114 SYSTEM_ATTRIBUTE2,
1115 DP_ENABLED_FLAG )
1116 SELECT p_demand_plan_id,
1117 mlv.instance,
1118 mlv.level_id,
1119 mlv.sr_level_pk,
1120 mlv.level_pk,
1121 sysdate,
1122 fnd_global.user_id,
1123 sysdate,
1124 fnd_global.user_id,
1125 mlv.system_attribute1,
1126 mlv.system_attribute2,
1127 mlv.dp_enabled_flag
1128 FROM msd_dp_iso_organizations mdio,
1129 msd_level_org_asscns mloa,
1130 msd_level_values mlv
1131 WHERE
1132 mdio.demand_plan_id = p_demand_plan_id
1133 AND mloa.instance = mdio.sr_instance_id
1134 AND mloa.level_id = 11
1135 AND mloa.org_level_id = 7
1136 AND mloa.org_sr_level_pk = mdio.sr_organization_id
1137 AND mlv.instance = mloa.instance
1138 AND mlv.level_id = 11
1139 AND mlv.sr_level_pk = mloa.sr_level_pk
1140 AND mlv.system_attribute1 = v_internal_desc
1141 MINUS
1142 SELECT p_demand_plan_id,
1143 instance,
1144 level_id,
1145 sr_level_pk,
1146 level_pk,
1147 sysdate,
1148 fnd_global.user_id,
1149 sysdate,
1150 fnd_global.user_id,
1151 system_attribute1,
1152 system_attribute2,
1153 dp_enabled_flag
1154 FROM msd_level_values_ds_temp
1155 WHERE demand_plan_id = p_demand_plan_id;
1156
1157 END IF;
1158 end if;
1159
1160
1161 if l_debug = C_YES_FLAG then
1162 debug_out( 'Exiting insert_related_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1163 end if;
1164
1165 end;
1166
1167
1168 procedure insert_non_stripe_level_values (errbuf out nocopy varchar2,
1169 retcode out nocopy varchar2,
1170 p_demand_plan_id in number,
1171 p_insert_rep in varchar2,
1172 p_insert_geo in varchar2) is
1173
1174 begin
1175
1176 if l_debug = C_YES_FLAG then
1177 debug_out( 'Entering insert_non_stripe_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1178 end if;
1179
1180 if ((p_insert_rep is null) and (p_insert_geo is null)) then
1181
1182 insert into msd_level_values_ds_temp
1183 (
1184 DEMAND_PLAN_ID,
1185 INSTANCE,
1186 LEVEL_ID,
1187 SR_LEVEL_PK,
1188 LEVEL_PK,
1189 LAST_UPDATE_DATE,
1190 LAST_UPDATED_BY,
1191 CREATION_DATE,
1192 CREATED_BY,
1193 SYSTEM_ATTRIBUTE1,
1194 SYSTEM_ATTRIBUTE2,
1195 DP_ENABLED_FLAG
1196 )
1197 select p_demand_plan_id,
1198 mlv.instance,
1199 mlv.level_id,
1200 mlv.sr_level_pk,
1201 mlv.level_pk,
1202 sysdate,
1203 fnd_global.user_id,
1204 sysdate,
1205 fnd_global.user_id,
1206 mlv.system_attribute1,
1207 mlv.system_attribute2,
1208 mlv.dp_enabled_flag
1209 from msd_level_values mlv,
1210 msd_levels ml
1211 where ml.level_id = mlv.level_id
1212 and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE,C_REP_DIM_CODE,C_GEO_DIM_CODE)
1213 minus
1214 select p_demand_plan_id,
1215 instance,
1216 level_id,
1217 sr_level_pk,
1218 level_pk,
1219 sysdate,
1220 fnd_global.user_id,
1221 sysdate,
1222 fnd_global.user_id,
1223 system_attribute1,
1224 system_attribute2,
1225 dp_enabled_flag
1226 from msd_level_values_ds_temp
1227 where demand_plan_id = p_demand_plan_id;
1228
1229 elsif ((p_insert_rep is not null) and (p_insert_geo is null)) then
1230
1231 insert into msd_level_values_ds_temp
1232 (
1233 DEMAND_PLAN_ID,
1234 INSTANCE,
1235 LEVEL_ID,
1236 SR_LEVEL_PK,
1237 LEVEL_PK,
1238 LAST_UPDATE_DATE,
1239 LAST_UPDATED_BY,
1240 CREATION_DATE,
1241 CREATED_BY,
1242 SYSTEM_ATTRIBUTE1,
1243 SYSTEM_ATTRIBUTE2,
1244 DP_ENABLED_FLAG
1245 )
1246 select p_demand_plan_id,
1247 mlv.instance,
1248 mlv.level_id,
1249 mlv.sr_level_pk,
1250 mlv.level_pk,
1251 sysdate,
1252 fnd_global.user_id,
1253 sysdate,
1254 fnd_global.user_id,
1255 mlv.system_attribute1,
1256 mlv.system_attribute2,
1257 mlv.dp_enabled_flag
1258 from msd_level_values mlv,
1259 msd_levels ml
1260 where ml.level_id = mlv.level_id
1261 and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE,C_GEO_DIM_CODE)
1262 minus
1263 select p_demand_plan_id,
1264 instance,
1265 level_id,
1266 sr_level_pk,
1267 level_pk,
1268 sysdate,
1269 fnd_global.user_id,
1270 sysdate,
1271 fnd_global.user_id,
1272 system_attribute1,
1273 system_attribute2,
1274 dp_enabled_flag
1275 from msd_level_values_ds_temp
1276 where demand_plan_id = p_demand_plan_id;
1277
1278 elsif ((p_insert_rep is null) and (p_insert_geo is not null)) then
1279
1280 insert into msd_level_values_ds_temp
1281 (
1282 DEMAND_PLAN_ID,
1283 INSTANCE,
1284 LEVEL_ID,
1285 SR_LEVEL_PK,
1286 LEVEL_PK,
1287 LAST_UPDATE_DATE,
1288 LAST_UPDATED_BY,
1289 CREATION_DATE,
1290 CREATED_BY,
1291 SYSTEM_ATTRIBUTE1,
1292 SYSTEM_ATTRIBUTE2,
1293 DP_ENABLED_FLAG
1294 )
1295 select p_demand_plan_id,
1296 mlv.instance,
1297 mlv.level_id,
1298 mlv.sr_level_pk,
1299 mlv.level_pk,
1300 sysdate,
1301 fnd_global.user_id,
1302 sysdate,
1303 fnd_global.user_id,
1304 mlv.system_attribute1,
1305 mlv.system_attribute2,
1306 mlv.dp_enabled_flag
1307 from msd_level_values mlv,
1308 msd_levels ml
1309 where ml.level_id = mlv.level_id
1310 and ml.dimension_code not in (C_PRD_DIM_CODE,C_GEO_DIM_CODE,C_REP_DIM_CODE)
1311 minus
1312 select p_demand_plan_id,
1313 instance,
1314 level_id,
1315 sr_level_pk,
1316 level_pk,
1317 sysdate,
1318 fnd_global.user_id,
1319 sysdate,
1320 fnd_global.user_id,
1321 system_attribute1,
1322 system_attribute2,
1323 dp_enabled_flag
1324 from msd_level_values_ds_temp
1325 where demand_plan_id = p_demand_plan_id;
1326
1327 elsif ((p_insert_rep is not null) and (p_insert_geo is not null)) then
1328
1329 insert into msd_level_values_ds_temp
1330 (
1331 DEMAND_PLAN_ID,
1332 INSTANCE,
1333 LEVEL_ID,
1334 SR_LEVEL_PK,
1335 LEVEL_PK,
1336 LAST_UPDATE_DATE,
1337 LAST_UPDATED_BY,
1338 CREATION_DATE,
1339 CREATED_BY,
1340 SYSTEM_ATTRIBUTE1,
1341 SYSTEM_ATTRIBUTE2,
1342 DP_ENABLED_FLAG
1343 )
1344 select p_demand_plan_id,
1345 mlv.instance,
1346 mlv.level_id,
1347 mlv.sr_level_pk,
1348 mlv.level_pk,
1349 sysdate,
1350 fnd_global.user_id,
1351 sysdate,
1352 fnd_global.user_id,
1353 mlv.system_attribute1,
1354 mlv.system_attribute2,
1355 mlv.dp_enabled_flag
1356 from msd_level_values mlv,
1357 msd_levels ml
1358 where ml.level_id = mlv.level_id
1359 and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE)
1360 minus
1361 select p_demand_plan_id,
1362 instance,
1363 level_id,
1364 sr_level_pk,
1365 level_pk,
1366 sysdate,
1367 fnd_global.user_id,
1368 sysdate,
1369 fnd_global.user_id,
1370 system_attribute1,
1371 system_attribute2,
1372 dp_enabled_flag
1373 from msd_level_values_ds_temp
1374 where demand_plan_id = p_demand_plan_id;
1375 end if;
1376
1377 -- Bug 3239820. Cannot Download Input Scenario if LOB used
1378 -- Performance enhancement to insert level_pk 0.
1379
1380 ins_pseudo_level_pk (errbuf,
1381 retcode,
1382 p_demand_plan_id);
1383
1384 ins_other_level_val (errbuf,
1385 retcode,
1386 p_demand_plan_id);
1387 ins_all_level_val (errbuf,
1388 retcode,
1389 p_demand_plan_id);
1390
1391 if l_debug = C_YES_FLAG then
1392 debug_out( 'Exiting insert_non_stripe_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1393 end if;
1394
1395 end;
1396
1397 procedure handle_remaining_level_values (errbuf out nocopy varchar2,
1398 retcode out nocopy varchar2,
1399 p_demand_plan_id in number) is
1400
1401 x_rep_dim_code varchar2(30);
1402 x_geo_dim_code varchar2(30);
1403
1404 begin
1405
1406 if l_debug = C_YES_FLAG then
1407 debug_out( 'Entering handle_remaining_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1408 end if;
1409
1410 if (is_dim_in_plan(p_demand_plan_id, C_REP_DIM_CODE) = C_TRUE) then
1411
1412 insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_REPS_LEVEL_ID);
1413
1414 if (chk_insert_no_associations (errbuf,
1415 retcode,
1416 p_demand_plan_id,
1417 C_REPS_LEVEL_ID) = C_TRUE) then
1418
1419 x_rep_dim_code := C_REP_DIM_CODE;
1420
1421 else
1422
1423 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_REPS_LEVEL_ID);
1424
1425 end if;
1426 else
1427 x_rep_dim_code := C_REP_DIM_CODE;
1428 end if;
1429
1430 if (is_dim_in_plan(p_demand_plan_id, C_GEO_DIM_CODE) = C_TRUE) then
1431
1432 insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_SHIP_LEVEL_ID);
1433
1434 if (chk_insert_no_associations (errbuf,
1435 retcode,
1436 p_demand_plan_id,
1437 C_SHIP_LEVEL_ID) = C_TRUE) then
1438
1439 x_geo_dim_code := C_GEO_DIM_CODE;
1440
1441 else
1442
1443 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_SHIP_LEVEL_ID);
1444
1445 end if;
1446
1447 else
1448 x_geo_dim_code := C_GEO_DIM_CODE;
1449 end if;
1450
1451 insert_non_stripe_level_values( errbuf, retcode, p_demand_plan_id, x_rep_dim_code, x_geo_dim_code);
1452
1453 if l_debug = C_YES_FLAG then
1454 debug_out( 'Exiting handle_remaining_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
1455 end if;
1456
1457 end;
1458
1459
1460 -- This Procedure Produces and executes a dynamic sql statement
1461 -- to insert data into the fact stripe table. Here's an example
1462 -- of what the sql looks like :
1463 -- insert into msd_cs_data_ds
1464 -- (
1465 -- demand_plan_id,
1466 -- cs_data_id,
1467 -- cs_definition_id,
1468 -- cs_name,
1469 -- last_update_date,
1470 -- last_updated_by,
1471 -- creation_date,
1472 -- created_by
1473 -- )
1474 -- select mds.demand_plan_id,
1475 -- csd.cs_data_id,
1476 -- csd.cs_definition_id,
1477 -- csd.cs_name,
1478 -- sysdate,
1479 -- fnd_global.user_id,
1480 -- sysdate,
1481 -- fnd_global.user_id
1482 -- from (select demand_plan_id from msd_dp_session) mds,
1483 -- msd_cs_data_v1 csd,
1484 -- msd_level_values_ds mld
1485 -- where csd.cs_definition_id = 21
1486 -- and mld.level_pk = csd.product_lvl_pk
1487
1488 procedure insert_fact_data (errbuf out nocopy varchar2,
1489 retcode out nocopy varchar2,
1490 p_demand_plan_id in number,
1491 p_fast_refresh in varchar2) is
1492 cursor c0 is
1493 select distinct
1494 parameter_type,
1495 parameter_name
1496 from msd_dp_parameters
1497 where demand_plan_id = p_demand_plan_id;
1498
1499 cursor c1(p_cs_defn_name in varchar2) is
1500 select cs_definition_id,
1501 multiple_stream_flag,
1502 stripe_flag
1503 from msd_cs_definitions
1504 where name = p_cs_defn_name;
1505
1506 cursor c2(p_cs_id in number) is
1507 select dimension_code
1508 from msd_cs_defn_dim_dtls
1509 where cs_definition_id = p_cs_id
1510 and collect_flag = C_YES_FLAG
1511 and dimension_code <> 'TIM';
1512
1513 cursor c4 (p_cs_id in number, p_cs_name in varchar2) is
1514 select last_refresh_num
1515 from msd_cs_data_headers
1516 where cs_definition_id = p_cs_id
1517 and cs_name = nvl(p_cs_name, cs_name)
1518 order by last_refresh_num desc;
1519
1520 cursor c5 (p_parameter_type in varchar2, p_parameter_name in varchar2) is
1521 select refresh_num
1522 from msd_dp_parameters_ds
1523 where demand_plan_id = p_demand_plan_id
1524 and parameter_type = p_parameter_type
1525 and nvl(parameter_name, '&*') = nvl(p_parameter_name, nvl(parameter_name, '&*'))
1526 and data_type = C_FACT;
1527
1528 fact_refresh number;
1529 stripe_refresh number;
1530
1531 i number:= 0;
1532 x_dim_level_id_clmn varchar2(100);
1533 x_dim_level_pk_clmn varchar2(100);
1534 x_str_name varchar2(1000);
1535
1536 v_sql_stmt varchar2(5000);
1537 v_sql_where_stmt varchar2(5000);
1538 v_sql_refresh_stmt varchar2(500);
1539
1540 l_c1_rec c1%rowtype;
1541
1542 begin
1543
1544 if l_debug = C_YES_FLAG then
1545 debug_out( 'Entering insert_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
1546 end if;
1547
1548
1549 for c0_rec in c0 loop
1550
1551 v_sql_stmt := '';
1552 v_sql_where_stmt := '';
1553 i := 0;
1554
1555 for c1_rec in c1(c0_rec.parameter_type) loop
1556
1557 if (c1_rec.stripe_flag = 'N') then
1558 exit;
1559 end if;
1560
1561 if (p_fast_refresh = C_FAST_REF) then
1562
1563 open c4(c1_rec.cs_definition_id, c0_rec.parameter_name);
1564 fetch c4 into fact_refresh;
1565 close c4;
1566
1567 open c5(c0_rec.parameter_type, c0_rec.parameter_name);
1568 fetch c5 into stripe_refresh;
1569 close c5;
1570
1571 if (stripe_refresh >= fact_refresh) then
1572 exit;
1573 end if;
1574
1575 /* append the following sql to the insert statement */
1576 /* This will only insert records which have last_refresh num > the current one */
1577 if (stripe_refresh < fact_refresh) then
1578 v_sql_refresh_stmt := ' and created_by_refresh_num > ' || stripe_refresh;
1579 end if;
1580
1581 else
1582
1583 open c5(c0_rec.parameter_type, c0_rec.parameter_name);
1584 fetch c5 into stripe_refresh;
1585 close c5;
1586
1587 /* Check to see if this stripe has been built already. */
1588
1589 if (stripe_refresh is not null) then
1590
1591 /* check if delete by parameter_name is necesary */
1592 if (c1_rec.multiple_stream_flag = C_YES_FLAG) then
1593 delete from msd_cs_data_ds
1594 where demand_plan_id = p_demand_plan_id
1595 and cs_definition_id = c1_rec.cs_definition_id
1596 and cs_name = c0_rec.parameter_name;
1597 else
1598 delete from msd_cs_data_ds
1599 where demand_plan_id = p_demand_plan_id
1600 and cs_definition_id = c1_rec.cs_definition_id;
1601 end if;
1602 end if;
1603 end if;
1604
1605
1606
1607
1608 v_sql_stmt := v_sql_stmt || ' insert into msd_cs_data_ds ';
1609 v_sql_stmt := v_sql_stmt || ' (demand_plan_id, cs_data_id, cs_definition_id, cs_name, ';
1610 v_sql_stmt := v_sql_stmt || ' last_update_date, last_updated_by, creation_date, created_by) ';
1611 v_sql_stmt := v_sql_stmt || ' select /*+ ORDERED */ mds.demand_plan_id, cdv.cs_data_id, cdv.cs_definition_id, cdv.cs_name, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id ';
1612 v_sql_stmt := v_sql_stmt || ' from (select demand_plan_id from msd_dp_session) mds, ';
1613 v_sql_stmt := v_sql_stmt || ' msd_cs_data_v1 cdv ';
1614 v_sql_where_stmt := v_sql_where_stmt || ' where cdv.cs_definition_id = ' || c1_rec.cs_definition_id || ' and mds.demand_plan_id = ' || p_demand_plan_id;
1615
1616 for c2_rec in c2(c1_rec.cs_definition_id) loop
1617
1618 i := i + 1;
1619 v_sql_stmt := v_sql_stmt || ' , ' || ' msd_level_values_ds mld' || i ;
1620
1621 v_sql_where_stmt := v_sql_where_stmt || ' and mld' || i || '.level_pk = cdv.' || c2_rec.dimension_code || '_LEVEL_VALUE_PK ';
1622 v_sql_where_stmt := v_sql_where_stmt || ' and mld' || i || '.demand_plan_id = mds.demand_plan_id ';
1623 end loop;
1624
1625 if (c1_rec.multiple_stream_flag = C_YES_FLAG) then
1626
1627 x_str_name := get_desig_clmn_name(c1_rec.cs_definition_id);
1628
1629 v_sql_where_stmt := v_sql_where_stmt || ' and cdv.' || x_str_name || ' = :parameter_name ' ;
1630
1631 end if;
1632
1633 v_sql_stmt := v_sql_stmt || v_sql_where_stmt;
1634
1635 -- Fast Refresh
1636 if ( p_fast_refresh = C_FAST_REF ) then
1637 v_sql_stmt := v_sql_stmt || v_sql_refresh_stmt;
1638 end if;
1639
1640
1641 if l_debug = C_YES_FLAG then
1642 i := 1;
1643 while i<= length(v_sql_stmt) loop
1644 debug_out( substr(v_sql_stmt, i, 90));
1645 i := i+90;
1646 end loop;
1647 end if;
1648
1649 /* Set demand plan for session */
1650 msd_stripe_demand_plan.set_demand_plan(p_demand_plan_id);
1651
1652 -- Execute Statement
1653 if (c1_rec.multiple_stream_flag = C_YES_FLAG) then
1654 execute immediate v_sql_stmt using c0_rec.parameter_name;
1655 else
1656 execute immediate v_sql_stmt;
1657 end if;
1658
1659
1660 fact_refresh := null;
1661 stripe_refresh := null;
1662 end loop;
1663 end loop;
1664
1665
1666 if l_debug = C_YES_FLAG then
1667 debug_out( 'Exiting insert_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
1668 end if;
1669
1670
1671 end;
1672
1673 procedure create_level_val_stripe (errbuf out nocopy varchar2,
1674 retcode out nocopy varchar2,
1675 p_demand_plan_id in number,
1676 p_stripe_instance in varchar2,
1677 p_stripe_level_id in number,
1678 p_stripe_sr_level_pk in varchar2
1679
1680 ) is
1681
1682 x_dimension_code varchar2(300);
1683
1684 x_lv_last_refresh_num number;
1685 x_dp_lv_last_refresh_num number;
1686 l_refresh_level_values varchar2(100) := C_FALSE;
1687
1688 cursor c0(p_level_id in number) is
1689 select dimension_code
1690 from msd_levels ml
1691 where level_id = p_level_id;
1692
1693 /* Bug# 4937978
1694 * The columns system_attribute1, system_attribute2 and dp_enabled_flag
1695 * should also be inserted.
1696 */
1697 cursor c1(p_instance in varchar2,
1698 p_level_id in number,
1699 p_sr_level_pk in varchar2) is
1700 select level_pk, level_value, system_attribute1, system_attribute2, dp_enabled_flag
1701 from msd_level_values
1702 where instance = p_instance
1703 and level_id = p_level_id
1704 and sr_level_pk = p_sr_level_pk;
1705
1706 l_c1_rec c1%rowtype;
1707
1708 begin
1709
1710 if l_debug = C_YES_FLAG then
1711 debug_out( 'Entering create_level_val_stripe ' || to_char(sysdate, 'hh24:mi:ss'));
1712 end if;
1713
1714
1715 delete from msd_level_values_ds_temp
1716 where demand_plan_id = p_demand_plan_id;
1717
1718
1719 open c1(p_stripe_instance, p_stripe_level_id, p_stripe_sr_level_pk);
1720 fetch c1 into l_c1_rec;
1721 close c1;
1722
1723 insert into msd_level_values_ds_temp
1724 (
1725 DEMAND_PLAN_ID,
1726 INSTANCE,
1727 LEVEL_ID,
1728 SR_LEVEL_PK,
1729 LEVEL_PK,
1730 LAST_UPDATE_DATE,
1731 LAST_UPDATED_BY,
1732 CREATION_DATE,
1733 CREATED_BY,
1734 SYSTEM_ATTRIBUTE1, -- Bug# 4937978
1735 SYSTEM_ATTRIBUTE2,
1736 DP_ENABLED_FLAG
1737 ) VALUES
1738 (
1739 p_demand_plan_id,
1740 p_stripe_instance,
1741 p_stripe_level_id,
1742 p_stripe_sr_level_pk,
1743 l_c1_rec.level_pk,
1744 sysdate,
1745 fnd_global.user_id,
1746 sysdate,
1747 fnd_global.user_id,
1748 l_c1_rec.system_attribute1, -- Bug# 4937978
1749 l_c1_rec.system_attribute2,
1750 l_c1_rec.dp_enabled_flag
1751 );
1752
1753 update msd_demand_plans
1754 set build_stripe_level_pk = l_c1_rec.level_pk,
1755 build_stripe_stream_name = null,
1756 build_stripe_stream_desig = null,
1757 build_stripe_stream_ref_num = null
1758 where demand_plan_id = p_demand_plan_id;
1759
1760 walk_down_hierarchy(errbuf, retcode, p_demand_plan_id, p_stripe_level_id);
1761
1762 open c0(p_stripe_level_id);
1763 fetch c0 into x_dimension_code;
1764 close c0;
1765
1766 if (x_dimension_code = C_PRD_DIM_CODE) then
1767 insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
1768 end if;
1769
1770 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
1771
1772 -- Checking for Org Dimension
1773 if (x_dimension_code <> C_PRD_DIM_CODE) then
1774 insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
1775 end if;
1776
1777 /* insert supercession items */
1778 insert_supercession_items(errbuf, retcode, p_demand_plan_id);
1779
1780 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
1781 handle_remaining_level_values( errbuf, retcode, p_demand_plan_id);
1782
1783 if l_debug = C_YES_FLAG then
1784 debug_out( 'Exiting create_level_val_stripe ' || to_char(sysdate, 'hh24:mi:ss'));
1785 end if;
1786
1787 Exception
1788 When EX_FATAL_ERROR then
1789 retcode := 2;
1790 errbuf := substr( sqlerrm, 1, 80);
1791 when others then
1792 retcode := 2;
1793 errbuf := substr( sqlerrm, 1, 80);
1794 End;
1795
1796 function is_level_val_stripe_equal (errbuf out nocopy varchar2,
1797 retcode out nocopy varchar2,
1798 p_demand_plan_id in number) return varchar2 is
1799 cursor c1 is
1800 select demand_plan_id, instance, level_id, sr_level_pk
1801 from msd_level_values_ds
1802 where demand_plan_id = p_demand_plan_id
1803 minus
1804 select demand_plan_id, instance, level_id, sr_level_pk
1805 from msd_level_values_ds_temp
1806 where demand_plan_id = p_demand_plan_id;
1807
1808 cursor c2 is
1809 select demand_plan_id, instance, level_id, sr_level_pk
1810 from msd_level_values_ds_temp
1811 where demand_plan_id = p_demand_plan_id
1812 minus
1813 select demand_plan_id, instance, level_id, sr_level_pk
1814 from msd_level_values_ds
1815 where demand_plan_id = p_demand_plan_id;
1816
1817 l_rec_1 c1%rowtype;
1818 l_rec_2 c2%rowtype;
1819 x_equal varchar2(30) := C_FALSE;
1820
1821 begin
1822
1823 open c1;
1824 fetch c1 into l_rec_1;
1825 if (c1%NOTFOUND) then
1826 open c2;
1827 fetch c2 into l_rec_2;
1828 if (c2%NOTFOUND) then
1829 x_equal := C_TRUE;
1830 end if;
1831 end if;
1832
1833 return x_equal;
1834 end;
1835
1836
1837 procedure copy_level_val_stripe (errbuf out nocopy varchar2,
1838 retcode out nocopy varchar2,
1839 p_demand_plan_id in number) is
1840
1841 begin
1842
1843 if l_debug = C_YES_FLAG then
1844 debug_out( 'Entering copy_level_val_stripe ' || to_char(sysdate, 'hh24:mi:ss'));
1845 end if;
1846
1847
1848 delete from msd_level_values_ds
1849 where demand_plan_id = p_demand_plan_id;
1850
1851 insert into msd_level_values_ds
1852 (
1853 DEMAND_PLAN_ID,
1854 INSTANCE,
1855 LEVEL_ID,
1856 SR_LEVEL_PK,
1857 LEVEL_PK,
1858 LAST_UPDATE_DATE,
1859 LAST_UPDATED_BY,
1860 CREATION_DATE,
1861 CREATED_BY,
1862 SYSTEM_ATTRIBUTE1,
1863 SYSTEM_ATTRIBUTE2,
1864 DP_ENABLED_FLAG
1865 )
1866 select DEMAND_PLAN_ID,
1867 INSTANCE,
1868 LEVEL_ID,
1869 SR_LEVEL_PK,
1870 LEVEL_PK,
1871 LAST_UPDATE_DATE,
1872 LAST_UPDATED_BY,
1873 CREATION_DATE,
1874 CREATED_BY,
1875 SYSTEM_ATTRIBUTE1,
1876 SYSTEM_ATTRIBUTE2,
1877 DP_ENABLED_FLAG
1878 from msd_level_values_ds_temp
1879 where demand_plan_id = p_demand_plan_id;
1880
1881 delete from msd_level_values_ds_temp
1882 where demand_plan_id = p_demand_plan_id;
1883
1884 /* Bug# 5078878
1885 Calling analyze table MSD_LEVEL_VALUES_DS so that the statistics
1886 are upto date when insert into msd_cs_data_ds is done.
1887 */
1888 commit;
1889 MSD_ANALYZE_TABLES.analyze_table('MSD_LEVEL_VALUES_DS',null);
1890
1891 if l_debug = C_YES_FLAG then
1892 debug_out( 'Exiting copy_level_val_stripe ' || to_char(sysdate, 'hh24:mi:ss'));
1893 end if;
1894
1895 end;
1896
1897 function is_level_val_collected (errbuf out nocopy varchar2,
1898 retcode out nocopy varchar2,
1899 p_demand_plan_id in number) return varchar2 is
1900
1901
1902 x_lv_last_refresh_num number;
1903 x_dp_lv_last_refresh_num number;
1904 l_refresh_level_values varchar2(100) := C_TRUE;
1905
1906 cursor c2 is
1907 select refresh_num
1908 from msd_dp_parameters_ds
1909 where demand_plan_id = C_LEVEL_PLAN
1910 and data_type = C_LVL_VAL;
1911
1912 cursor c3 is
1913 select refresh_num
1914 from msd_dp_parameters_ds
1915 where demand_plan_id = p_demand_plan_id
1916 and data_type = C_LVL_VAL;
1917
1918 begin
1919
1920 open c2;
1921 fetch c2 into x_lv_last_refresh_num;
1922 close c2;
1923
1924 open c3;
1925 fetch c3 into x_dp_lv_last_refresh_num;
1926 close c3;
1927
1928 if (x_dp_lv_last_refresh_num >= x_lv_last_refresh_num) then
1929 l_refresh_level_values := C_FALSE;
1930 end if;
1931
1932 return l_refresh_level_values;
1933
1934 end;
1935
1936 -- This function checks to see whether the GEO or REP dimensions
1937 -- were previously attached and not attached now, or previously
1938 -- attached and now not attached to plan. This is needed because
1939 -- if these plans are not attached to plan then they should not
1940 -- be striped.
1941
1942 function is_dimension_changed (errbuf out nocopy varchar2,
1943 retcode out nocopy varchar2,
1944 p_demand_plan_id in number) return varchar2 is
1945
1946 cursor chk_change_dim1 is
1947 select dimension_code
1948 from msd_dp_dimensions
1949 where demand_plan_id = p_demand_plan_id
1950 and dimension_code in (C_REP_DIM_CODE,C_GEO_DIM_CODE)
1951 minus
1952 select parameter_type
1953 from msd_dp_parameters_ds
1954 where demand_plan_id = p_demand_plan_id
1955 and data_type = C_DIM
1956 and parameter_type in (C_REP_DIM_CODE,C_GEO_DIM_CODE);
1957
1958 cursor chk_change_dim2 is
1959 select parameter_type
1960 from msd_dp_parameters_ds
1961 where demand_plan_id = p_demand_plan_id
1962 and data_type = C_DIM
1963 and parameter_type in (C_REP_DIM_CODE,C_GEO_DIM_CODE)
1964 minus
1965 select dimension_code
1966 from msd_dp_dimensions
1967 where demand_plan_id = p_demand_plan_id
1968 and dimension_code in (C_REP_DIM_CODE,C_GEO_DIM_CODE);
1969
1970 x_dim_code varchar2(30);
1971
1972 begin
1973
1974 open chk_change_dim1;
1975 fetch chk_change_dim1 into x_dim_code;
1976 close chk_change_dim1;
1977
1978 if (x_dim_code is not null) then
1979 return C_TRUE;
1980 end if;
1981
1982 open chk_change_dim2;
1983 fetch chk_change_dim2 into x_dim_code;
1984 close chk_change_dim2;
1985
1986 if (x_dim_code is not null) then
1987 return C_TRUE;
1988 else
1989 return C_FALSE;
1990 end if;
1991
1992 end is_dimension_changed;
1993
1994 function is_event_collected (errbuf out nocopy varchar2,
1995 retcode out nocopy varchar2,
1996 p_demand_plan_id in number) return varchar2 is
1997
1998 l_refresh_events varchar2(30) := C_FALSE;
1999 x_instance varchar2(100);
2000 x_level_id number;
2001 x_sr_level_pk varchar2(240);
2002
2003 cursor c1 is
2004 select mlv.instance,
2005 mlv.level_id,
2006 mlv.sr_level_pk
2007 from msd_events me,
2008 msd_dp_events mde,
2009 msd_event_products mep,
2010 msd_evt_prod_relationships mepr,
2011 msd_level_values_ds mlvd,
2012 msd_level_values mlv
2013 where mepr.instance = mlvd.instance
2014 and mepr.product_lvl_id = mlvd.level_id
2015 and mlvd.level_id = 1
2016 and mepr.sr_product_lvl_pk = mlvd.sr_level_pk
2017 and mep.event_id = mepr.event_id
2018 and mep.seq_id = mepr.seq_id
2019 and mepr.npi_prod_relationship = C_NPI_BASE_PRODUCT
2020 and me.event_id = mep.event_id
2021 and me.event_type = 3
2022 and me.event_id = mde.event_id
2023 and mde.demand_plan_id = p_demand_plan_id
2024 and mlv.instance = mep.instance
2025 and mlv.sr_level_pk = mep.sr_product_lvl_pk
2026 and mlv.level_id = mep.product_lvl_id
2027 and mlvd.demand_plan_id = p_demand_plan_id
2028 minus
2029 select instance,
2030 level_id,
2031 sr_level_pk
2032 from msd_level_values_ds
2033 where demand_plan_id = p_demand_plan_id;
2034
2035 begin
2036
2037 if l_debug = C_YES_FLAG then
2038 debug_out( 'Entering is_event_collected ' || to_char(sysdate, 'hh24:mi:ss'));
2039 end if;
2040
2041 open c1;
2042 fetch c1 into x_instance, x_level_id, x_sr_level_pk;
2043 close c1;
2044
2045 if (x_sr_level_pk is not null) then
2046 l_refresh_events := C_TRUE;
2047 end if;
2048
2049 if l_debug = C_YES_FLAG then
2050 debug_out( 'Exiting is_event_collected ' || to_char(sysdate, 'hh24:mi:ss'));
2051 end if;
2052
2053 return l_refresh_events;
2054
2055 end is_event_collected;
2056
2057 function is_new_stripe (errbuf out nocopy varchar2,
2058 retcode out nocopy varchar2,
2059 p_demand_plan_id in number,
2060 p_stripe_instance in varchar2,
2061 p_stripe_level_id in number,
2062 p_stripe_sr_level_pk in varchar2,
2063 p_build_stripe_level_pk in number,
2064 p_build_stripe_stream_name in varchar2) return varchar2 is
2065
2066 cursor c1(p_instance in varchar2,
2067 p_level_id in number,
2068 p_sr_level_pk in varchar2) is
2069 select level_pk, level_value
2070 from msd_level_values
2071 where instance = p_instance
2072 and level_id = p_level_id
2073 and sr_level_pk = p_sr_level_pk;
2074
2075 l_new_stripe varchar2(100) := C_TRUE;
2076 l_c1_rec c1%rowtype;
2077
2078 begin
2079
2080 --
2081 -- check to see if previous stripe existed with stripe stream
2082 -- if so, then this time stripe could be new.
2083 --
2084 if (p_build_stripe_stream_name is not null ) then
2085 return l_new_stripe;
2086 end if;
2087
2088 open c1 (p_stripe_instance, p_stripe_level_id, p_stripe_sr_level_pk);
2089 fetch c1 into l_c1_rec;
2090 close c1;
2091
2092 if (l_c1_rec.level_pk = p_build_stripe_level_pk) then
2093 l_new_stripe := C_FALSE;
2094 end if;
2095
2096 return l_new_stripe;
2097
2098 end;
2099
2100
2101 procedure delete_fact_data (errbuf out nocopy varchar2,
2102 retcode out nocopy varchar2,
2103 p_demand_plan_id in number) is
2104
2105 cursor c1 is
2106 select parameter_type, parameter_name
2107 from msd_dp_parameters_ds
2108 where demand_plan_id = p_demand_plan_id
2109 and data_type = C_FACT
2110 minus
2111 select parameter_type, parameter_name
2112 from msd_dp_parameters
2113 where demand_plan_id = p_demand_plan_id;
2114
2115 begin
2116
2117 if l_debug = C_YES_FLAG then
2118 debug_out( 'Entering delete_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
2119 end if;
2120
2121
2122 for c1_rec in c1 loop
2123
2124 if (c1_rec.parameter_name is not null) then
2125
2126 delete from msd_cs_data_ds
2127 where cs_definition_id = (select cs_definition_id
2128 from msd_cs_definitions
2129 where name = c1_rec.parameter_type)
2130 and cs_name = c1_rec.parameter_name
2131 and demand_plan_id = p_demand_plan_id;
2132
2133 else
2134
2135 delete from msd_cs_data_ds
2136 where cs_definition_id = (select cs_definition_id
2137 from msd_cs_definitions
2138 where name = c1_rec.parameter_type)
2139 and demand_plan_id = p_demand_plan_id;
2140
2141 end if;
2142
2143 end loop;
2144
2145 if l_debug = C_YES_FLAG then
2146 debug_out( 'Exiting delete_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
2147 end if;
2148
2149 end;
2150
2151 procedure update_dp_parameters_ds (errbuf out nocopy varchar2,
2152 retcode out nocopy varchar2,
2153 p_demand_plan_id in number) is
2154
2155
2156
2157 begin
2158
2159 if l_debug = C_YES_FLAG then
2160 debug_out( 'Entering update_dp_parameters_ds ' || to_char(sysdate, 'hh24:mi:ss'));
2161 end if;
2162
2163
2164
2165 delete from msd_dp_parameters_ds
2166 where demand_plan_id = p_demand_plan_id;
2167
2168 insert into msd_dp_parameters_ds
2169 ( DEMAND_PLAN_ID,
2170 DATA_TYPE,
2171 PARAMETER_TYPE,
2172 PARAMETER_NAME,
2173 REFRESH_NUM,
2174 LAST_UPDATE_DATE,
2175 LAST_UPDATED_BY,
2176 CREATION_DATE,
2177 CREATED_BY
2178 )
2179 select p_demand_plan_id,
2180 C_LVL_VAL,
2181 null,
2182 null,
2183 refresh_num,
2184 sysdate,
2185 fnd_global.user_id,
2186 sysdate,
2187 fnd_global.user_id
2188 from msd_dp_parameters_ds
2189 where demand_plan_id = C_LEVEL_PLAN
2190 and data_type = C_LVL_VAL
2191 union all
2192 select p_demand_plan_id,
2193 C_FACT,
2194 mdp.parameter_type,
2195 mdp.parameter_name,
2196 max(csh.last_refresh_num),
2197 sysdate,
2198 fnd_global.user_id,
2199 sysdate,
2200 fnd_global.user_id
2201 from msd_dp_parameters mdp,
2202 msd_cs_data_headers csh,
2203 msd_cs_definitions csd
2204 where mdp.parameter_type = csd.name
2205 and csd.cs_definition_id = csh.cs_definition_id
2206 and csh.cs_name = nvl(mdp.parameter_name, csh.cs_name)
2207 and mdp.demand_plan_id = p_demand_plan_id
2208 group by mdp.parameter_type, mdp.parameter_name
2209 union all
2210 select p_demand_plan_id,
2211 C_DIM,
2212 mdd.dimension_code,
2213 null,
2214 null,
2215 sysdate,
2216 fnd_global.user_id,
2217 sysdate,
2218 fnd_global.user_id
2219 from msd_dp_dimensions mdd
2220 where mdd.demand_plan_id = p_demand_plan_id;
2221
2222 if l_debug = C_YES_FLAG then
2223 debug_out( 'Exiting update_dp_parameters_ds ' || to_char(sysdate, 'hh24:mi:ss'));
2224 end if;
2225
2226 end;
2227
2228
2229
2230 Procedure stripe_demand_plan_lob (errbuf out nocopy varchar2,
2231 retcode out nocopy varchar2,
2232 p_demand_plan_id in number,
2233 p_stripe_instance in varchar2,
2234 p_stripe_level_id in number,
2235 p_stripe_sr_level_pk in varchar2,
2236 p_build_stripe_level_pk in varchar2,
2237 p_build_stripe_stream_name in varchar2) IS
2238
2239 Begin
2240 if l_debug = C_YES_FLAG then
2241 debug_out( 'Entering stripe_demand_plan_lob ' || to_char(sysdate, 'hh24:mi:ss'));
2242 end if;
2243 --
2244 -- Set the demand plan Id in the temp table.
2245 --
2246 set_demand_plan(p_demand_plan_id);
2247
2248 --
2249 -- Check if level values has been re-collected or a different LOB
2250 -- has been selected.
2251 --
2252 if ((is_level_val_collected (errbuf, retcode, p_demand_plan_id) = C_TRUE) or
2253 (is_event_collected(errbuf,retcode,p_demand_plan_id) = C_TRUE) or
2254 (is_new_stripe (errbuf,retcode,p_demand_plan_id,
2255 p_stripe_instance,
2256 p_stripe_level_id,
2257 p_stripe_sr_level_pk,
2258 p_build_stripe_level_pk,
2259 p_build_stripe_stream_name) = C_TRUE) or
2260 (is_dimension_changed(errbuf, retcode, p_demand_plan_id) = C_TRUE) or
2261 (is_iso_orgs_changed (errbuf, retcode, p_demand_plan_id) = C_TRUE)) then /* ISO Code Change */
2262
2263 --
2264 -- Create the Stripe in the Temp Stripe Table.
2265 --
2266 create_level_val_stripe (errbuf,
2267 retcode,
2268 p_demand_plan_id,
2269 p_stripe_instance,
2270 p_stripe_level_id,
2271 p_stripe_sr_level_pk);
2272
2273
2274 --
2275 -- If the stripe is different then copy it into the Stripe Table.
2276 --
2277 if (is_level_val_stripe_equal(errbuf, retcode, p_demand_plan_id) = C_FALSE) then
2278 copy_level_val_stripe (errbuf, retcode, p_demand_plan_id);
2279 l_fast_refresh_fact := C_FULL_REF;
2280 else
2281 l_fast_refresh_fact := C_FAST_REF;
2282 end if;
2283 else
2284 l_fast_refresh_fact := C_FAST_REF;
2285 end if;
2286
2287 if l_debug = C_YES_FLAG then
2288 debug_out( 'Exiting stripe_demand_plan_lob ' || to_char(sysdate, 'hh24:mi:ss'));
2289 end if;
2290 end;
2291
2292 procedure stripe_demand_plan_stream (errbuf out nocopy varchar2,
2293 retcode out nocopy varchar2,
2294 p_demand_plan_id in number,
2295 p_build_stripe_level_pk in number,
2296 p_stripe_stream_name in varchar2,
2297 p_stripe_stream_desig in varchar2,
2298 p_build_stripe_stream_name in varchar2,
2299 p_build_stripe_stream_desig in varchar2,
2300 p_build_stripe_stream_ref_num in number) IS
2301
2302 Begin
2303 if l_debug = C_YES_FLAG then
2304 debug_out( 'Entering stripe_demand_plan_stream ' || to_char(sysdate, 'hh24:mi:ss'));
2305 end if;
2306 --
2307 -- Set the demand plan Id in the temp table.
2308 --
2309 set_demand_plan(p_demand_plan_id);
2310
2311 --
2312 -- Check if level values has been re-collected or a different LOB
2313 -- has been selected.
2314 --
2315 if ((is_level_val_collected (errbuf, retcode, p_demand_plan_id) = C_TRUE) OR
2316 (is_event_collected(errbuf,retcode,p_demand_plan_id) = C_TRUE) or
2317 (is_new_stream_stripe(errbuf, retcode, p_demand_plan_id,
2318 p_build_stripe_level_pk,
2319 p_stripe_stream_name,
2320 p_stripe_stream_desig ,
2321 p_build_stripe_stream_name,
2322 p_build_stripe_stream_desig,
2323 p_build_stripe_stream_ref_num) = C_TRUE) or
2324 (is_dimension_changed(errbuf, retcode, p_demand_plan_id) = C_TRUE) or
2325 (is_iso_orgs_changed (errbuf, retcode, p_demand_plan_id) = C_TRUE)) then /* ISO Code Change */
2326
2327 --
2328 -- Create the Stripe in the Temp Stripe Table.
2329 --
2330 create_level_val_stripe_stream (errbuf,
2331 retcode,
2332 p_demand_plan_id,
2333 p_stripe_stream_name,
2334 p_stripe_stream_desig);
2335
2336
2337 --
2338 -- If the stripe is different then copy it into the Stripe Table.
2339 --
2340 if (is_level_val_stripe_equal(errbuf, retcode, p_demand_plan_id) = C_FALSE) then
2341 copy_level_val_stripe (errbuf, retcode, p_demand_plan_id);
2342 l_fast_refresh_fact := C_FULL_REF;
2343 else
2344 l_fast_refresh_fact := C_FAST_REF;
2345 end if;
2346 else
2347 l_fast_refresh_fact := C_FAST_REF;
2348 end if;
2349
2350
2351 --
2352 -- For each input parameter insert data into stripe fact table.
2353 --
2354 insert_fact_data (errbuf, retcode, p_demand_plan_id, l_fast_refresh_fact);
2355
2356 --
2357 -- For deleted input parameter remove data from stripe fact table.
2358 --
2359 delete_fact_data (errbuf, retcode, p_demand_plan_id);
2360
2361 --
2362 -- Record what information was used to build the stripe.
2363 --
2364 update_dp_parameters_ds (errbuf, retcode, p_demand_plan_id);
2365
2366 commit;
2367 MSD_ANALYZE_TABLES.analyze_table(null,7);
2368
2369 if l_debug = C_YES_FLAG then
2370 debug_out( 'Exiting stripe_demand_plan_stream ' || to_char(sysdate, 'hh24:mi:ss'));
2371 end if;
2372 end;
2373
2374
2375 function chk_insert_no_associations (errbuf out nocopy varchar2,
2376 retcode out nocopy varchar2,
2377 p_demand_plan_id in number,
2378 p_level_id in number) return varchar2 IS
2379
2380 cursor chk_row_count is
2381 select 1
2382 from msd_level_values_ds_temp
2383 where demand_plan_id = p_demand_plan_id
2384 and level_id = p_level_id
2385 and rownum = 1;
2386
2387 x_cnt number;
2388
2389 begin
2390
2391 open chk_row_count;
2392 fetch chk_row_count into x_cnt;
2393
2394 if (chk_row_count%NOTFOUND) then
2395 close chk_row_count;
2396 return C_TRUE;
2397 else
2398 close chk_row_count;
2399 return C_FALSE;
2400 end if;
2401
2402 end chk_insert_no_associations;
2403
2404 procedure create_level_val_stripe_stream (errbuf out nocopy varchar2,
2405 retcode out nocopy varchar2,
2406 p_demand_plan_id in number,
2407 p_stripe_stream_name in varchar2,
2408 p_stripe_stream_desig in varchar2) IS
2409
2410 x_dimension_code varchar2(300);
2411
2412 x_lv_last_refresh_num number;
2413 x_dp_lv_last_refresh_num number;
2414 l_refresh_level_values varchar2(100) := C_FALSE;
2415 x_refresh_num number;
2416
2417 cursor c0(p_level_id in number) is
2418 select dimension_code
2419 from msd_levels ml
2420 where level_id = p_level_id;
2421
2422 begin
2423
2424 if l_debug = C_YES_FLAG then
2425 debug_out( 'Entering create_level_val_stripe_stream ' || to_char(sysdate, 'hh24:mi:ss'));
2426 end if;
2427
2428 delete from msd_level_values_ds_temp
2429 where demand_plan_id = p_demand_plan_id;
2430
2431 insert_stream_items(errbuf,
2432 retcode,
2433 p_demand_plan_id,
2434 p_stripe_stream_name,
2435 p_stripe_stream_desig,
2436 C_PRD_DIM_CODE);
2437
2438 /* insert supercession items */
2439 insert_supercession_items(errbuf, retcode, p_demand_plan_id);
2440
2441 open get_refresh_num(p_stripe_stream_name, p_stripe_stream_desig);
2442 fetch get_refresh_num into x_refresh_num;
2443 close get_refresh_num;
2444
2445 update msd_demand_plans
2446 set build_stripe_stream_name = p_stripe_stream_name,
2447 build_stripe_stream_desig = p_stripe_stream_desig,
2448 build_stripe_stream_ref_num = x_refresh_num,
2449 build_stripe_level_pk = null
2450 where demand_plan_id = p_demand_plan_id;
2451
2452 insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
2453
2454 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
2455 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
2456
2457 handle_remaining_level_values( errbuf, retcode, p_demand_plan_id);
2458
2459 if l_debug = C_YES_FLAG then
2460 debug_out( 'Exiting create_level_val_stripe_stream ' || to_char(sysdate, 'hh24:mi:ss'));
2461 end if;
2462 END create_level_val_stripe_stream;
2463
2464 procedure create_lvl_val_stripe_strm_lob (errbuf out nocopy varchar2,
2465 retcode out nocopy varchar2,
2466 p_demand_plan_id in number,
2467 p_stripe_instance in varchar2,
2468 p_stripe_level_id in number,
2469 p_stripe_sr_level_pk in varchar2 ,
2470 p_stripe_stream_name in varchar2,
2471 p_stripe_stream_desig in varchar2) IS
2472 x_dimension_code varchar2(300);
2473
2474 x_lv_last_refresh_num number;
2475 x_dp_lv_last_refresh_num number;
2476 l_refresh_level_values varchar2(100) := C_FALSE;
2477
2478 cursor c0(p_level_id in number) is
2479 select dimension_code
2480 from msd_levels ml
2481 where level_id = p_level_id;
2482
2483 /* Bug# 4937978
2484 * The columns system_attribute1, system_attribute2 and dp_enabled_flag
2485 * should also be inserted.
2486 */
2487 cursor c1(p_instance in varchar2,
2488 p_level_id in number,
2489 p_sr_level_pk in varchar2) is
2490 select level_pk, level_value, system_attribute1, system_attribute2, dp_enabled_flag
2491 from msd_level_values
2492 where instance = p_instance
2493 and level_id = p_level_id
2494 and sr_level_pk = p_sr_level_pk;
2495
2496 l_c1_rec c1%rowtype;
2497 x_refresh_num number;
2498
2499 BEGIN
2500
2501 delete from msd_level_values_ds_temp
2502 where demand_plan_id = p_demand_plan_id;
2503
2504 open c1(p_stripe_instance, p_stripe_level_id, p_stripe_sr_level_pk);
2505 fetch c1 into l_c1_rec;
2506 close c1;
2507
2508 insert into msd_level_values_ds_temp
2509 (
2510 DEMAND_PLAN_ID,
2511 INSTANCE,
2512 LEVEL_ID,
2513 SR_LEVEL_PK,
2514 LEVEL_PK,
2515 LAST_UPDATE_DATE,
2516 LAST_UPDATED_BY,
2517 CREATION_DATE,
2518 CREATED_BY,
2519 SYSTEM_ATTRIBUTE1, -- Bug# 4937978
2520 SYSTEM_ATTRIBUTE2,
2521 DP_ENABLED_FLAG
2522 ) VALUES
2523 (
2524 p_demand_plan_id,
2525 p_stripe_instance,
2526 p_stripe_level_id,
2527 p_stripe_sr_level_pk,
2528 l_c1_rec.level_pk,
2529 sysdate,
2530 fnd_global.user_id,
2531 sysdate,
2532 fnd_global.user_id,
2533 l_c1_rec.system_attribute1, -- Bug# 4937978
2534 l_c1_rec.system_attribute2,
2535 l_c1_rec.dp_enabled_flag
2536 );
2537
2538 open get_refresh_num(p_stripe_stream_name, p_stripe_stream_desig);
2539 fetch get_refresh_num into x_refresh_num;
2540 close get_refresh_num;
2541
2542 update msd_demand_plans
2543 set build_stripe_level_pk = l_c1_rec.level_pk,
2544 build_stripe_stream_name = p_stripe_stream_name,
2545 build_stripe_stream_desig = p_stripe_stream_desig,
2546 build_stripe_stream_ref_num = x_refresh_num
2547 where demand_plan_id = p_demand_plan_id;
2548
2549 walk_down_hierarchy(errbuf, retcode, p_demand_plan_id, p_stripe_level_id);
2550
2551 open c0(p_stripe_level_id);
2552 fetch c0 into x_dimension_code;
2553 close c0;
2554
2555 if (x_dimension_code = C_ORG_DIM_CODE) then
2556 insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
2557 end if;
2558
2559 filter_stream_items(errbuf,
2560 retcode,
2561 p_demand_plan_id,
2562 p_stripe_stream_name,
2563 p_stripe_stream_desig,
2564 C_PRD_DIM_CODE);
2565
2566 /* insert supercession items */
2567 insert_supercession_items(errbuf, retcode, p_demand_plan_id);
2568
2569 -- If Product Stripe then insert orgs
2570 if (x_dimension_code = C_PRD_DIM_CODE) then
2571 insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
2572 end if;
2573
2574 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
2575 walk_up_hierarchy(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
2576
2577 handle_remaining_level_values( errbuf, retcode, p_demand_plan_id);
2578
2579 END create_lvl_val_stripe_strm_lob;
2580
2581 procedure stripe_demand_plan_lob_stream (errbuf out nocopy varchar2,
2582 retcode out nocopy varchar2,
2583 p_demand_plan_id in number,
2584 p_stripe_instance in varchar2,
2585 p_stripe_level_id in number,
2586 p_stripe_sr_level_pk in varchar2,
2587 p_build_stripe_level_pk in number,
2588 p_stripe_stream_name in varchar2,
2589 p_stripe_stream_desig in varchar2,
2590 p_build_stripe_stream_name in varchar2,
2591 p_build_stripe_stream_desig in varchar2,
2592 p_build_stripe_stream_ref_num in number) is
2593
2594 Begin
2595 if l_debug = C_YES_FLAG then
2596 debug_out( 'Entering stripe_demand_plan_lob_stream ' || to_char(sysdate, 'hh24:mi:ss'));
2597 end if;
2598 --
2599 -- Set the demand plan Id in the temp table.
2600 --
2601 set_demand_plan(p_demand_plan_id);
2602
2603
2604 --
2605 -- Check if level values has been re-collected or a different LOB
2606 -- has been selected.
2607 --
2608 if ((is_level_val_collected (errbuf, retcode, p_demand_plan_id) = C_TRUE) or
2609 (is_event_collected(errbuf,retcode,p_demand_plan_id) = C_TRUE) or
2610 (is_new_lob_stream_stripe (errbuf,
2611 retcode,
2612 p_demand_plan_id,
2613 p_stripe_instance,
2614 p_stripe_level_id,
2615 p_stripe_sr_level_pk,
2616 p_build_stripe_level_pk,
2617 p_stripe_stream_name,
2618 p_stripe_stream_desig,
2619 p_build_stripe_stream_name,
2620 p_build_stripe_stream_desig,
2621 p_build_stripe_stream_ref_num) = C_TRUE) or
2622 (is_dimension_changed(errbuf, retcode, p_demand_plan_id) = C_TRUE) or
2623 (is_iso_orgs_changed (errbuf, retcode, p_demand_plan_id) = C_TRUE)) then /* ISO Code Change */
2624
2625 --
2626 -- Create the Stripe in the Temp Stripe Table.
2627 --
2628 create_lvl_val_stripe_strm_lob (errbuf,
2629 retcode ,
2630 p_demand_plan_id ,
2631 p_stripe_instance ,
2632 p_stripe_level_id ,
2633 p_stripe_sr_level_pk ,
2634 p_stripe_stream_name ,
2635 p_stripe_stream_desig );
2636
2637
2638 --
2639 -- If the stripe is different then copy it into the Stripe Table.
2640 --
2641 if (is_level_val_stripe_equal(errbuf, retcode, p_demand_plan_id) = C_FALSE) then
2642 copy_level_val_stripe (errbuf, retcode, p_demand_plan_id);
2643 l_fast_refresh_fact := C_FULL_REF;
2644 else
2645 l_fast_refresh_fact := C_FAST_REF;
2646 end if;
2647 else
2648 l_fast_refresh_fact := C_FAST_REF;
2649 end if;
2650
2651 if l_debug = C_YES_FLAG then
2652 debug_out( 'Exiting stripe_demand_plan_stream_lob ' || to_char(sysdate, 'hh24:mi:ss'));
2653 end if;
2654 end;
2655
2656 procedure insert_stream_items(errbuf out nocopy varchar2,
2657 retcode out nocopy varchar2,
2658 p_demand_plan_id in number,
2659 p_stripe_stream_name in varchar2,
2660 p_stripe_stream_desig in varchar2,
2661 p_dim_code in varchar2) IS
2662
2663 cursor get_stream_defn(p_name in varchar2) is
2664 select mcd.cs_definition_id,
2665 mcd.name,
2666 mcd.multiple_stream_flag,
2667 mcd.stripe_flag,
2668 nvl(mcd.planning_server_view_name, 'MSD_CS_DATA_V')
2669 from msd_cs_definitions mcd
2670 where name = p_name;
2671
2672 cursor get_dim_col_id (p_cs_id in number) is
2673 SELECT collect_level_id
2674 FROM msd_cs_defn_dim_dtls
2675 where dimension_code = p_dim_code
2676 and collect_flag = 'Y'
2677 and cs_definition_id = p_cs_id;
2678
2679 cursor get_dim_lvl_clmn_name(p_cs_id in number) IS
2680 SELECT planning_view_column_name
2681 FROM msd_cs_defn_column_dtls_v
2682 WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
2683 AND identifier_type = 'DIMENSION_ID'
2684 AND cs_definition_id = p_cs_id;
2685
2686 l_sql_stmt varchar2(2000);
2687 x_cs_definition_id number;
2688 x_name varchar2(30);
2689 x_multiple_stream_flag varchar2(30);
2690 x_stripe_flag varchar2(30);
2691 x_planning_server_view_name varchar2(30);
2692 x_dim_col_id number;
2693 x_dim_lvl_clmn_name varchar2(30);
2694 x_desig_clmn_name varchar2(30);
2695
2696 begin
2697
2698 if l_debug = C_YES_FLAG then
2699 debug_out( 'Entering insert_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
2700 end if;
2701
2702 open get_stream_defn(p_stripe_stream_name);
2703 fetch get_stream_defn into x_cs_definition_id,
2704 x_name,
2705 x_multiple_stream_flag,
2706 x_stripe_flag,
2707 x_planning_server_view_name;
2708 close get_stream_defn;
2709
2710 open get_dim_lvl_clmn_name(x_cs_definition_id);
2711 fetch get_dim_lvl_clmn_name into x_dim_lvl_clmn_name;
2712 close get_dim_lvl_clmn_name;
2713
2714 open get_dim_col_id (x_cs_definition_id);
2715 fetch get_dim_col_id into x_dim_col_id;
2716 close get_dim_col_id;
2717
2718 x_desig_clmn_name := get_desig_clmn_name (x_cs_definition_id);
2719
2720 l_sql_stmt := ' insert into msd_level_values_ds_temp ' ||
2721 ' ( ' ||
2722 ' DEMAND_PLAN_ID, ' ||
2723 ' INSTANCE, ' ||
2724 ' LEVEL_ID, ' ||
2725 ' SR_LEVEL_PK, ' ||
2726 ' LEVEL_PK, ' ||
2727 ' LAST_UPDATE_DATE, ' ||
2728 ' LAST_UPDATED_BY, ' ||
2729 ' CREATION_DATE, ' ||
2730 ' CREATED_BY, ' ||
2731 ' SYSTEM_ATTRIBUTE1, ' ||
2732 ' SYSTEM_ATTRIBUTE2, ' ||
2733 ' DP_ENABLED_FLAG ' ||
2734 ' ) ' ||
2735 ' select distinct ' || p_demand_plan_id || ' , ' ||
2736 ' mlv.instance, ' ||
2737 ' mlv.level_id, ' ||
2738 ' mlv.sr_level_pk, ' ||
2739 ' mlv.level_pk, ' ||
2740 ' sysdate, ' ||
2741 ' fnd_global.user_id, ' ||
2742 ' sysdate, ' ||
2743 ' fnd_global.user_id, ' ||
2744 ' mlv.system_attribute1, ' ||
2745 ' mlv.system_attribute2, ' ||
2746 ' mlv.dp_enabled_flag ' ||
2747 ' from msd_level_values mlv, ' ||
2748 x_planning_server_view_name || ' fact ' ||
2749 ' where mlv.level_pk = fact.' || p_dim_code || '_LEVEL_VALUE_PK ' ||
2750 ' and mlv.level_id = fact.' || x_dim_lvl_clmn_name ||
2751 ' and fact.action_code = ''I''';
2752
2753 if (x_stripe_flag = 'Y') then
2754 l_sql_stmt := l_sql_stmt || ' and fact.cs_definition_id = ' || x_cs_definition_id;
2755 end if;
2756
2757 if ((p_stripe_stream_desig is not null) and (x_multiple_stream_flag = 'Y')) then
2758 l_sql_stmt := l_sql_stmt || ' and fact.' || x_desig_clmn_name || ' = ''' || replace(p_stripe_stream_desig, '''', '''''') || '''';
2759 end if;
2760 debug_out(l_sql_stmt);
2761 execute immediate l_sql_stmt;
2762
2763
2764
2765 if l_debug = C_YES_FLAG then
2766 debug_out( 'Exiting insert_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
2767 end if;
2768
2769 end;
2770
2771 /***********************************************************************
2772 * Returns True or False if user changed stripe stream value from
2773 * that previously used.
2774 */
2775
2776 function is_new_stream_stripe (errbuf out nocopy varchar2,
2777 retcode out nocopy varchar2,
2778 p_demand_plan_id in number,
2779 p_build_stripe_level_pk in number,
2780 p_stripe_stream_name in varchar2,
2781 p_stripe_stream_desig in varchar2,
2782 p_build_stripe_stream_name in varchar2,
2783 p_build_stripe_stream_desig in varchar2,
2784 p_build_stripe_stream_ref_num in number) return varchar2 is
2785
2786 x_curr_ref_num number;
2787
2788 begin
2789
2790 --
2791 -- check to see if previous stripe existed with stripe lob
2792 -- if so, then this time stripe could be new.
2793 --
2794 if (p_build_stripe_level_pk is not null ) then
2795 return C_TRUE;
2796 end if;
2797
2798 -- Is this the same selection
2799
2800 if (((p_build_stripe_stream_name = p_stripe_stream_name)
2801 and (((p_build_stripe_stream_desig is null) and (p_stripe_stream_desig is null))
2802 or (p_build_stripe_stream_desig = p_stripe_stream_desig)))
2803 and (p_build_stripe_level_pk is null)) then
2804
2805 -- Check to see if the refresh num has increased
2806 open get_refresh_num(p_stripe_stream_name, p_stripe_stream_desig);
2807 fetch get_refresh_num into x_curr_ref_num;
2808 close get_refresh_num;
2809
2810 if (x_curr_ref_num > p_build_stripe_stream_ref_num) then
2811 return C_TRUE;
2812 else
2813 return C_FALSE;
2814 end if;
2815 else
2816 return C_TRUE;
2817 end if;
2818 end is_new_stream_stripe;
2819
2820
2821 /***********************************************************************
2822 * Returns True or False if user changed stripe stream value from
2823 * that previously used.
2824 */
2825
2826 function is_new_lob_stream_stripe (errbuf out nocopy varchar2,
2827 retcode out nocopy varchar2,
2828 p_demand_plan_id in number,
2829 p_stripe_instance in varchar2,
2830 p_stripe_level_id in number,
2831 p_stripe_sr_level_pk in varchar2,
2832 p_build_stripe_level_pk in number,
2833 p_stripe_stream_name in varchar2,
2834 p_stripe_stream_desig in varchar2,
2835 p_build_stripe_stream_name in varchar2,
2836 p_build_stripe_stream_desig in varchar2,
2837 p_build_stripe_stream_ref_num in number) return varchar2 is
2838 cursor c1(p_instance in varchar2,
2839 p_level_id in number,
2840 p_sr_level_pk in varchar2) is
2841 select level_pk, level_value
2842 from msd_level_values
2843 where instance = p_instance
2844 and level_id = p_level_id
2845 and sr_level_pk = p_sr_level_pk;
2846
2847 l_new_stripe varchar2(100) := C_TRUE;
2848 l_c1_rec c1%rowtype;
2849 x_curr_ref_num number;
2850
2851 begin
2852
2853 open c1 (p_stripe_instance, p_stripe_level_id, p_stripe_sr_level_pk);
2854 fetch c1 into l_c1_rec;
2855 close c1;
2856
2857 if (l_c1_rec.level_pk = p_build_stripe_level_pk)
2858 and
2859 ((p_build_stripe_stream_name = p_stripe_stream_name)
2860 and (((p_build_stripe_stream_desig is null) and (p_stripe_stream_desig is null))
2861 or (p_build_stripe_stream_desig = p_stripe_stream_desig))) then
2862
2863 -- Check to see if the refresh num has increased
2864 open get_refresh_num(p_stripe_stream_name, p_stripe_stream_desig);
2865 fetch get_refresh_num into x_curr_ref_num;
2866 close get_refresh_num;
2867
2868 if (x_curr_ref_num > p_build_stripe_stream_ref_num) then
2869 return C_TRUE;
2870 else
2871 return C_FALSE;
2872 end if;
2873 else
2874 return C_TRUE;
2875 end if;
2876
2877 end is_new_lob_stream_stripe;
2878
2879
2880 procedure filter_stream_items(errbuf out nocopy varchar2,
2881 retcode out nocopy varchar2,
2882 p_demand_plan_id in number,
2883 p_stripe_stream_name in varchar2,
2884 p_stripe_stream_desig in varchar2,
2885 p_dim_code in varchar2) IS
2886
2887 cursor get_stream_defn(p_name in varchar2) is
2888 select mcd.cs_definition_id,
2889 mcd.name,
2890 mcd.multiple_stream_flag,
2891 mcd.stripe_flag,
2892 nvl(mcd.planning_server_view_name,'MSD_CS_DATA_V')
2893 from msd_cs_definitions mcd
2894 where name = p_name;
2895
2896 cursor get_dim_col_id (p_cs_id in number) is
2897 SELECT collect_level_id
2898 FROM msd_cs_defn_dim_dtls
2899 where dimension_code = p_dim_code
2900 and collect_flag = 'Y'
2901 and cs_definition_id = p_cs_id;
2902
2903 cursor get_dim_lvl_clmn_name(p_cs_id in number) IS
2904 SELECT planning_view_column_name
2905 FROM msd_cs_defn_column_dtls_v
2906 WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
2907 AND identifier_type = 'DIMENSION_ID'
2908 AND cs_definition_id = p_cs_id;
2909
2910 l_sql_stmt varchar2(2000);
2911 x_cs_definition_id number;
2912 x_name varchar2(30);
2913 x_multiple_stream_flag varchar2(30);
2914 x_stripe_flag varchar2(30);
2915 x_planning_server_view_name varchar2(30);
2916 x_dim_col_id number;
2917 x_dim_lvl_clmn_name varchar2(30);
2918 x_dim_val_clmn_name varchar2(30);
2919 x_desig_clmn_name varchar2(30);
2920
2921 begin
2922
2923 if l_debug = C_YES_FLAG then
2924 debug_out( 'Entering filter_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
2925 end if;
2926
2927 open get_stream_defn(p_stripe_stream_name);
2928 fetch get_stream_defn into x_cs_definition_id,
2929 x_name,
2930 x_multiple_stream_flag,
2931 x_stripe_flag,
2932 x_planning_server_view_name;
2933 close get_stream_defn;
2934
2935 open get_dim_col_id (x_cs_definition_id);
2936 fetch get_dim_col_id into x_dim_col_id;
2937 close get_dim_col_id;
2938
2939 x_desig_clmn_name := get_desig_clmn_name(x_cs_definition_id);
2940
2941 l_sql_stmt := ' delete from msd_level_values_ds_temp ' ||
2942 ' where level_id = ' || C_ITEM_LEVEL_ID ||
2943 ' and demand_plan_id = ' || p_demand_plan_id ||
2944 ' and level_pk in ( ' ||
2945 ' select to_char(level_pk) from msd_level_values_ds_temp ' ||
2946 ' where demand_plan_id = ' || p_demand_plan_id ||
2947 ' minus select to_char(' ||
2948 ' fact.' || p_dim_code || '_LEVEL_VALUE_PK) ' ||
2949 ' from ' || x_planning_server_view_name || ' fact ' ||
2950 ' where 1 = 1 ';
2951
2952 if (x_stripe_flag = 'Y') then
2953 l_sql_stmt := l_sql_stmt || ' and fact.cs_definition_id = ' || x_cs_definition_id;
2954 end if;
2955
2956 if ((p_stripe_stream_desig is not null) and (x_multiple_stream_flag = 'Y')) then
2957 l_sql_stmt := l_sql_stmt || ' and fact.' || x_desig_clmn_name || ' = ''' || p_stripe_stream_desig || '''';
2958 end if;
2959
2960 l_sql_stmt := l_sql_stmt || ')';
2961
2962
2963 execute immediate l_sql_stmt;
2964
2965 if l_debug = C_YES_FLAG then
2966 debug_out( 'Exiting filter_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
2967 end if;
2968
2969 end filter_stream_items;
2970
2971 procedure insert_supercession_items (errbuf out nocopy varchar2,
2972 retcode out nocopy varchar2,
2973 p_demand_plan_id in number) IS
2974
2975 begin
2976
2977 if l_debug = C_YES_FLAG then
2978 debug_out( 'Entering insert_supercession_items ' || to_char(sysdate, 'hh24:mi:ss'));
2979 end if;
2980
2981 insert into msd_level_values_ds_temp
2982 (
2983 DEMAND_PLAN_ID,
2984 INSTANCE,
2985 LEVEL_ID,
2986 SR_LEVEL_PK,
2987 LEVEL_PK,
2988 LAST_UPDATE_DATE,
2989 LAST_UPDATED_BY,
2990 CREATION_DATE,
2991 CREATED_BY,
2992 SYSTEM_ATTRIBUTE1,
2993 SYSTEM_ATTRIBUTE2,
2994 DP_ENABLED_FLAG
2995 )
2996 select p_demand_plan_id,
2997 mlv.instance,
2998 mlv.level_id,
2999 mlv.sr_level_pk,
3000 mlv.level_pk,
3001 sysdate,
3002 fnd_global.user_id,
3003 sysdate,
3004 fnd_global.user_id,
3005 mlv.system_attribute1,
3006 mlv.system_attribute2,
3007 mlv.dp_enabled_flag
3008 from msd_dp_events mde,
3009 msd_events me,
3010 msd_event_products mep,
3011 msd_evt_prod_relationships mepr,
3012 msd_level_values_ds_temp mlvd,
3013 msd_level_values mlv
3014 where mepr.instance = mlvd.instance
3015 and mepr.product_lvl_id = mlvd.level_id
3016 and mlvd.level_id = 1
3017 and mepr.sr_product_lvl_pk = mlvd.sr_level_pk
3018 and mep.event_id = mepr.event_id
3019 and mep.seq_id = mepr.seq_id
3020 and mepr.npi_prod_relationship = C_NPI_BASE_PRODUCT
3021 and me.event_id = mep.event_id
3022 and me.event_type = 3
3023 and mde.event_id = me.event_id
3024 and mde.demand_plan_id = p_demand_plan_id
3025 and mlv.instance = mep.instance
3026 and mlv.sr_level_pk = mep.sr_product_lvl_pk
3027 and mlv.level_id = mep.product_lvl_id
3028 and mlvd.demand_plan_id = p_demand_plan_id
3029 minus
3030 select p_demand_plan_id,
3031 instance,
3032 level_id,
3033 sr_level_pk,
3034 level_pk,
3035 sysdate,
3036 fnd_global.user_id,
3037 sysdate,
3038 fnd_global.user_id,
3039 system_attribute1,
3040 system_attribute2,
3041 dp_enabled_flag
3042 from msd_level_values_ds_temp
3043 where demand_plan_id = p_demand_plan_id;
3044
3045 if l_debug = C_YES_FLAG then
3046 debug_out( 'Exiting insert_supercession_items ' || to_char(sysdate, 'hh24:mi:ss'));
3047 end if;
3048
3049 end;
3050
3051 function get_desig_clmn_name (p_cs_id in number) return VARCHAR2 IS
3052
3053 CURSOR get_str_name (p_id NUMBER) IS
3054 SELECT planning_view_column_name
3055 FROM msd_cs_defn_column_dtls_v
3056 WHERE cs_definition_id = p_id
3057 AND identifier_type = 'CSIDEN';
3058
3059 x_str_name varchar2(30);
3060
3061 BEGIN
3062
3063 open get_str_name (p_cs_id);
3064 fetch get_str_name into x_str_name;
3065 close get_str_name;
3066
3067 if (x_str_name is null) then
3068 x_str_name := 'CS_NAME';
3069 end if;
3070
3071 return x_str_name;
3072 end;
3073
3074 procedure handle_fact_data (errbuf out nocopy varchar2,
3075 retcode out nocopy varchar2,
3076 p_demand_plan_id in number) IS
3077
3078 BEGIN
3079
3080 --
3081 -- For each input parameter insert data into stripe fact table.
3082 --
3083 insert_fact_data (errbuf, retcode, p_demand_plan_id, l_fast_refresh_fact);
3084
3085 --
3086 -- For deleted input parameter remove data from stripe fact table.
3087 --
3088 delete_fact_data (errbuf, retcode, p_demand_plan_id);
3089
3090 --
3091 -- Record what information was used to build the stripe.
3092 --
3093 update_dp_parameters_ds (errbuf, retcode, p_demand_plan_id);
3094
3095 commit;
3096 MSD_ANALYZE_TABLES.analyze_table(null,7);
3097
3098 END Handle_Fact_data;
3099
3100 -- Bug 3239820. Insert Level Value Pk of 0 to optimize
3101 -- striped view for input scenario.
3102 -- Inserts a row of level_pk 0 into the plan stripe.
3103 -- MSD_DP_SCENARIO_ENTRIES contains 0 in level_pk
3104 -- columns if that particular dimension is not used.
3105
3106 procedure ins_pseudo_level_pk (errbuf out nocopy varchar2,
3107 retcode out nocopy varchar2,
3108 p_demand_plan_id in number) is
3109
3110 cursor chk_pseudo is
3111 select 1
3112 from msd_level_values_ds_temp
3113 where demand_plan_id = p_demand_plan_id
3114 and level_pk = C_PSEUDO_PK;
3115
3116 x_cnt number;
3117
3118 begin
3119
3120 if l_debug = C_YES_FLAG then
3121 debug_out( 'Entering ins_pseudo_level_pk ' || to_char(sysdate, 'hh24:mi:ss'));
3122 end if;
3123
3124 open chk_pseudo;
3125 fetch chk_pseudo into x_cnt;
3126 if (chk_pseudo%NOTFOUND) then
3127
3128 insert into msd_level_values_ds_temp
3129 (
3130 DEMAND_PLAN_ID,
3131 INSTANCE,
3132 LEVEL_ID,
3133 SR_LEVEL_PK,
3134 LEVEL_PK,
3135 LAST_UPDATE_DATE,
3136 LAST_UPDATED_BY,
3137 CREATION_DATE,
3138 CREATED_BY
3139 ) VALUES
3140 (
3141 p_demand_plan_id,
3142 C_PSEUDO_PK,
3143 C_PSEUDO_PK,
3144 C_PSEUDO_PK,
3145 C_PSEUDO_PK,
3146 sysdate,
3147 fnd_global.user_id,
3148 sysdate,
3149 fnd_global.user_id
3150 );
3151
3152 end if;
3153
3154 close chk_pseudo;
3155
3156 open chk_pseudo;
3157 fetch chk_pseudo into x_cnt;
3158 if (chk_pseudo%NOTFOUND) then
3159 if l_debug = C_YES_FLAG then
3160 debug_out( 'Error Insert Pseudo Level Value into Stripe.');
3161 retcode := '2';
3162 end if;
3163 end if;
3164 close chk_pseudo;
3165
3166 if l_debug = C_YES_FLAG then
3167 debug_out( 'Exiting ins_pseudo_level_pk ' || to_char(sysdate, 'hh24:mi:ss'));
3168 end if;
3169
3170 end ins_pseudo_level_pk;
3171
3172 procedure ins_other_level_val (errbuf out nocopy varchar2,
3173 retcode out nocopy varchar2,
3174 p_demand_plan_id in number) is
3175
3176 x_other_sr_level_pk varchar2(30) := '-777';
3177
3178 begin
3179
3180 if l_debug = C_YES_FLAG then
3181 debug_out( 'Entering ins_other_level_val ' || to_char(sysdate, 'hh24:mi:ss'));
3182 end if;
3183
3184 insert into msd_level_values_ds_temp
3185 (
3186 DEMAND_PLAN_ID,
3187 INSTANCE,
3188 LEVEL_ID,
3189 SR_LEVEL_PK,
3190 LEVEL_PK,
3191 LAST_UPDATE_DATE,
3192 LAST_UPDATED_BY,
3193 CREATION_DATE,
3194 CREATED_BY,
3195 SYSTEM_ATTRIBUTE1,
3196 SYSTEM_ATTRIBUTE2,
3197 DP_ENABLED_FLAG
3198 )
3199 select p_demand_plan_id,
3200 mlv.instance,
3201 mlv.level_id,
3202 mlv.sr_level_pk,
3203 mlv.level_pk,
3204 sysdate,
3205 fnd_global.user_id,
3206 sysdate,
3207 fnd_global.user_id,
3208 mlv.system_attribute1,
3209 mlv.system_attribute2,
3210 mlv.dp_enabled_flag
3211 from msd_level_values mlv
3212 where mlv.sr_level_pk = x_other_sr_level_pk
3213 minus
3214 select p_demand_plan_id,
3215 instance,
3216 level_id,
3217 sr_level_pk,
3218 level_pk,
3219 sysdate,
3220 fnd_global.user_id,
3221 sysdate,
3222 fnd_global.user_id,
3223 system_attribute1,
3224 system_attribute2,
3225 dp_enabled_flag
3226 from msd_level_values_ds_temp
3227 where demand_plan_id = p_demand_plan_id;
3228
3229 if l_debug = C_YES_FLAG then
3230 debug_out( 'Exiting ins_other_level_val ' || to_char(sysdate, 'hh24:mi:ss'));
3231 end if;
3232
3233 end ins_other_level_val;
3234
3235 /*Bug 6672593*/
3236 procedure ins_all_level_val (errbuf out nocopy varchar2,
3237 retcode out nocopy varchar2,
3238 p_demand_plan_id in number) is
3239
3240 begin
3241
3242 if l_debug = C_YES_FLAG then
3243 debug_out( 'Entering ins_all_level_val ' || to_char(sysdate, 'hh24:mi:ss'));
3244 end if;
3245
3246 insert into msd_level_values_ds_temp
3247 (
3248 DEMAND_PLAN_ID,
3249 INSTANCE,
3250 LEVEL_ID,
3251 SR_LEVEL_PK,
3252 LEVEL_PK,
3253 LAST_UPDATE_DATE,
3254 LAST_UPDATED_BY,
3255 CREATION_DATE,
3256 CREATED_BY,
3257 SYSTEM_ATTRIBUTE1,
3258 SYSTEM_ATTRIBUTE2,
3259 DP_ENABLED_FLAG
3260 )
3261 select p_demand_plan_id,
3262 mlv.instance,
3263 mlv.level_id,
3264 mlv.sr_level_pk,
3265 mlv.level_pk,
3266 sysdate,
3267 fnd_global.user_id,
3268 sysdate,
3269 fnd_global.user_id,
3270 mlv.system_attribute1,
3271 mlv.system_attribute2,
3272 mlv.dp_enabled_flag
3273 from msd_level_values mlv
3274 where (mlv.level_id, mlv.sr_level_pk) IN (select level_id, sr_level_pk from msd_level_values where level_id in
3275 (select level_id from msd_levels where level_type_code = 1))
3276 minus
3277 select p_demand_plan_id,
3278 instance,
3279 level_id,
3280 sr_level_pk,
3281 level_pk,
3282 sysdate,
3283 fnd_global.user_id,
3284 sysdate,
3285 fnd_global.user_id,
3286 system_attribute1,
3287 system_attribute2,
3288 dp_enabled_flag
3289 from msd_level_values_ds_temp
3290 where demand_plan_id = p_demand_plan_id;
3291
3292 if l_debug = C_YES_FLAG then
3293 debug_out( 'Exiting ins_all_level_val ' || to_char(sysdate, 'hh24:mi:ss'));
3294 end if;
3295
3296 end ins_all_level_val;
3297
3298
3299 function is_dim_in_plan (p_demand_plan_id in number, p_dim_code in varchar2) return varchar2 is
3300
3301 cursor chk_dim is
3302 select C_TRUE
3303 from msd_dp_dimensions
3304 where dimension_code = p_dim_code
3305 and demand_plan_id = p_demand_plan_id;
3306
3307 x_is_dim_in_plan varchar2(30) := C_FALSE;
3308
3309 begin
3310
3311 open chk_dim;
3312 fetch chk_dim into x_is_dim_in_plan;
3313 close chk_dim;
3314
3315 return x_is_dim_in_plan;
3316
3317 end is_dim_in_plan;
3318
3319 procedure chk_insert_org_no_associations (errbuf out nocopy varchar2,
3320 retcode out nocopy varchar2,
3321 p_demand_plan_id in number,
3322 p_level_id in number) IS
3323
3324 cursor chk_row_count is
3325 select 1
3326 from msd_level_values_ds_temp
3327 where demand_plan_id = p_demand_plan_id
3328 and level_id = p_level_id
3329 and rownum = 1;
3330
3331 x_cnt number;
3332
3333 begin
3334
3335 open chk_row_count;
3336 fetch chk_row_count into x_cnt;
3337
3338 if (chk_row_count%NOTFOUND) then
3339 insert into msd_level_values_ds_temp
3340 (
3341 DEMAND_PLAN_ID,
3342 INSTANCE,
3343 LEVEL_ID,
3344 SR_LEVEL_PK,
3345 LEVEL_PK,
3346 LAST_UPDATE_DATE,
3347 LAST_UPDATED_BY,
3348 CREATION_DATE,
3349 CREATED_BY,
3350 SYSTEM_ATTRIBUTE1,
3351 SYSTEM_ATTRIBUTE2,
3352 DP_ENABLED_FLAG
3353 )
3354 select p_demand_plan_id,
3355 mlv.instance,
3356 mlv.level_id,
3357 mlv.sr_level_pk,
3358 mlv.level_pk,
3359 sysdate,
3360 fnd_global.user_id,
3361 sysdate,
3362 fnd_global.user_id,
3363 mlv.system_attribute1,
3364 mlv.system_attribute2,
3365 mlv.dp_enabled_flag
3366 from msd_level_values mlv
3367 where mlv.level_id = p_level_id
3368 minus
3369 select p_demand_plan_id,
3370 instance,
3371 level_id,
3372 sr_level_pk,
3373 level_pk,
3374 sysdate,
3375 fnd_global.user_id,
3376 sysdate,
3377 fnd_global.user_id,
3378 system_attribute1,
3379 system_attribute2,
3380 dp_enabled_flag
3381 from msd_level_values_ds_temp
3382 where demand_plan_id = p_demand_plan_id;
3383 end if;
3384 close chk_row_count;
3385 end chk_insert_org_no_associations;
3386
3387
3388 /********************************************************************
3389 * ISO Code Change
3390 * This function checks whether there is any change in the ISO orgs
3391 * attached to the demand plan
3392 */
3393 FUNCTION is_iso_orgs_changed (errbuf OUT NOCOPY VARCHAR2,
3394 retcode OUT NOCOPY VARCHAR2,
3395 p_demand_plan_id IN NUMBER)
3396 RETURN VARCHAR2
3397 IS
3398
3399 /*
3400 * This cursor checks whether any new internal orgs/sites have been added
3401 */
3402 CURSOR c_is_added_iso_orgs
3403 IS
3404 SELECT 1
3405 FROM dual
3406 WHERE EXISTS (SELECT mloa.sr_level_pk
3407 FROM msd_dp_iso_organizations mdio,
3408 msd_level_org_asscns mloa
3409 WHERE
3410 mdio.demand_plan_id = p_demand_plan_id
3411 AND mloa.instance = mdio.sr_instance_id
3412 AND mloa.level_id = 11
3413 AND mloa.org_level_id = 7
3414 AND mloa.org_sr_level_pk = mdio.sr_organization_id
3415 MINUS
3416 SELECT sr_level_pk
3417 FROM msd_level_values_ds
3418 WHERE
3419 demand_plan_id = p_demand_plan_id
3420 AND level_id = 11);
3421
3422 /*
3423 * This cursor checks whether any existing internal orgs/sites have been deleted
3424 */
3425 CURSOR c_is_deleted_iso_orgs
3426 IS
3427 SELECT 1
3428 FROM dual
3429 WHERE EXISTS (SELECT sr_level_pk
3430 FROM msd_level_values_ds
3431 WHERE
3432 demand_plan_id = p_demand_plan_id
3433 AND level_id = 11
3434 MINUS
3435 SELECT mloa.sr_level_pk
3436 FROM msd_dp_iso_organizations mdio,
3437 msd_level_org_asscns mloa
3438 WHERE
3439 mdio.demand_plan_id = p_demand_plan_id
3440 AND mloa.instance = mdio.sr_instance_id
3441 AND mloa.level_id = 11
3442 AND mloa.org_level_id = 7
3443 AND mloa.org_sr_level_pk = mdio.sr_organization_id);
3444
3445 x_is_present NUMBER := -1;
3446
3447 BEGIN
3448
3449 /* Check if any new internal orgs/sites have been added */
3450 OPEN c_is_added_iso_orgs;
3451 FETCH c_is_added_iso_orgs INTO x_is_present;
3452 CLOSE c_is_added_iso_orgs;
3453
3454 IF x_is_present = 1 THEN
3455 RETURN C_TRUE;
3456 END IF;
3457
3458 x_is_present := -1;
3459 /* Check if any existing internal orgs/sites have been deleted */
3460 OPEN c_is_deleted_iso_orgs;
3461 FETCH c_is_deleted_iso_orgs INTO x_is_present;
3462 CLOSE c_is_deleted_iso_orgs;
3463
3464 IF x_is_present = 1 THEN
3465 RETURN C_TRUE;
3466 END IF;
3467
3468 EXCEPTION
3469 WHEN OTHERS THEN
3470 retcode := 2;
3471 errbuf := substr( sqlerrm, 1, 80);
3472 RETURN C_FALSE;
3473
3474 END is_iso_orgs_changed;
3475
3476 End;