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