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

Source


1 PACKAGE BODY MSD_COMPOSITE_GROUPING AS -- body
2 /* $Header: MSDCMGRB.pls 120.1 2006/01/19 23:12:42 sjagathe noship $ */
3 
4 TYPE stream_rec_type IS RECORD
5  ( p_stream_id                   NUMBER,
6    l_stream_count                NUMBER,
7    l_distinct_distributions      NUMBER,
8    l_base_stream_id              NUMBER,
9    l_group                       NUMBER,
10    l_reading_level_string        VARCHAR2(300),
11    l_dimension_clause            VARCHAR2(300),
12    l_groupable                   NUMBER );
13 
14 TYPE stream_tbl_type IS TABLE OF stream_rec_type
15                            INDEX BY BINARY_INTEGER;
16 
17 TYPE CharTblTyp IS TABLE OF VARCHAR2(255);
18 TYPE NumTblTyp  IS TABLE OF NUMBER;
19 
20   lb_stream_id          NumTblTyp;
21   lb_geo_dim            CharTblTyp;
22   lb_org_dim            CharTblTyp;
23   lb_prd_dim            CharTblTyp;
24   lb_rep_dim            CharTblTyp;
25   lb_chn_dim            CharTblTyp;
26   lb_dcs_dim            CharTblTyp;
27   lb_ud1_dim            CharTblTyp;
28   lb_ud2_dim            CharTblTyp;
29   lb_base_stream_id     NumTblTyp;
30 
31   lb_cs_definition_id   NumTblTyp;
32   lb_dimension_code     CharTblTyp;
33   lb_level_id           NumTblTyp;
34 
35 v_group_number   PLS_INTEGER;
36 v_counter        PLS_INTEGER;
37 v_final_tbl      stream_tbl_type;
38 v_index          PLS_INTEGER;
39 
40 v_threshold_overlap   NUMBER;
41 
42 
43 PROCEDURE LOG_DEBUG( pBUFF           IN  VARCHAR2)
44 IS
45  BEGIN
46 
47          IF G_MSC_DEBUG = 'Y' THEN
48 
49             FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
50 
51          ELSE
52             NULL;
53             --DBMS_OUTPUT.PUT_LINE( pBUFF);
54 
55          END IF;
56 
57  END LOG_DEBUG;
58 
59  PROCEDURE LOG_MESSAGE( pBUFF           IN  VARCHAR2)
60  IS
61  BEGIN
62 
63             FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
64 
65  END LOG_MESSAGE;
66 
67 FUNCTION GET_DIM_CODE (p_level_id IN NUMBER)
68 RETURN VARCHAR2 AS
69 
70 lv_dimension_code VARCHAR2(10);
71 BEGIN
72 
73  select dimension_code
74  into lv_dimension_code
75  from msd_levels
76  where level_id=p_level_id
77  and plan_type is NULL;           -- Bug# 4928951
78 
79 /* The above fix requires plan_type column (with value as NULL) to be
80    present in msd_levels even if liability code is removed. If the
81    column is removed, the condition on plan_type must be removed */
82 
83  return lv_dimension_code||'_DIM';
84 
85 END GET_DIM_CODE;
86 
87 FUNCTION number_of_designators (p_cs_definition_id IN NUMBER)
88 RETURN NUMBER AS
89 
90 lv_desig_count NUMBER;
91 
92 BEGIN
93 
94  select count(*) into lv_desig_count
95  from (select distinct cs_name from msd_cs_data where cs_definition_id=p_cs_definition_id);
96 
97 
98  return lv_desig_count;
99 
100 END number_of_designators;
101 
102 FUNCTION GET_STREAM_COUNT (p_cs_definition_id IN NUMBER)
103 RETURN NUMBER AS
104 
105 lv_count NUMBER;
106 BEGIN
107 
108  select count(*)
109  into lv_count
110  from MSD_DISTINCT_DIM_VAL_TEMP
111  where stream_id=p_cs_definition_id;
112 
113  return lv_count;
114 
115 END GET_STREAM_COUNT;
116 
117 
118 PROCEDURE GET_LEVEL_ID (  p_cs_definition_id IN NUMBER,
119                           p_dimension_code   IN VARCHAR2)
120 IS
121 
122  CURSOR a(p_cs_definition_id NUMBER,p_dimension_code VARCHAR2) IS
123  select to_number(decode(p_dimension_code,'PRD',attribute_2,'GEO',attribute_6,'ORG',attribute_10,'REP',attribute_18,'CHN',attribute_22,'UD1',attribute_26,'UD2',attribute_30,'DCS',attribute_45))
124  from msd_cs_data
125  where cs_definition_id= p_cs_definition_id
126  and cs_data_id = (select min(cs_data_id) from msd_cs_data where cs_definition_id=p_cs_definition_id);
127 
128  lv_collect_level_id NUMBER;
129  lv_error_text VARCHAR2(2000);
130 
131  BEGIN
132 
133  LOG_DEBUG('Entered in GET_LEVEL_ID with p_cs_definition_id='||p_cs_definition_id||' and p_dimension_code ='||p_dimension_code);
134 
135  OPEN a(p_cs_definition_id,p_dimension_code);
136 
137     FETCH a INTO lv_collect_level_id;
138       LOG_DEBUG('The value of lv_collect_level_id is '||lv_collect_level_id);
139     UPDATE MSD_CS_DTLS_TEMP
140     SET COLLECT_LEVEL_ID  = lv_collect_level_id
141     WHERE DIMENSION_CODE  = p_dimension_code
142     AND  CS_DEFINITION_ID = p_cs_definition_id;
143 
144  CLOSE a;
145 
146 
147  EXCEPTION
148  WHEN OTHERS THEN
149 
150    lv_error_text :=SQLERRM;
151    LOG_DEBUG(lv_error_text);
152 
153 END GET_LEVEL_ID;
154 
155 PROCEDURE get_boundaries(p_index IN NUMBER,
156                          p_lower OUT NOCOPY NUMBER,
157                          p_upper OUT NOCOPY NUMBER,
158                          p_stream_tbl IN stream_tbl_type)
159 IS
160 
161 l_counter  PLS_INTEGER;
162 
163 
164 BEGIN
165 
166 LOG_DEBUG('started get_boundaries');
167 l_counter :=1;
168 
169 FOR i IN 1..p_stream_tbl.COUNT LOOP
170 
171   IF p_stream_tbl(i).l_groupable = p_index THEN
172     p_lower := l_counter;
173     EXIT;
174   ELSE
175     l_counter := l_counter+1;
176   END IF;
177 
178 END LOOP;
179 
180 
181 FOR i IN l_counter..p_stream_tbl.COUNT LOOP
182 
183   IF p_stream_tbl(i).l_groupable = p_index+1  THEN
184     p_upper := l_counter-1;
185     EXIT;
186   ELSE
187    IF i <>  p_stream_tbl.COUNT THEN
188       l_counter := l_counter +1;
189    ELSE
190       p_upper := i;
191    END IF;
192   END IF;
193 
194 END LOOP;
195 
196 EXCEPTION
197  WHEN OTHERS THEN
198    LOG_DEBUG('inside when others error-  get_boundaries');
199    LOG_DEBUG(SQLERRM);
200 
201 END get_boundaries;
202 
203 PROCEDURE LOAD_SHIPMENT_STREAM
204 IS
205 
206 CURSOR shipment IS
207   SELECT  DISTINCT x.ship_to_loc,
208                    x.inv_org,
209                    x.item,
210                    x.sales_rep,
211                    x.sales_channel,
212                    x.user_defined1,
213                    x.user_defined2,
214                    null    -- dcs level value
215   FROM msd_shipment_data_v x;
216 
217   lv_stream_id NUMBER;
218 
219 BEGIN
220 
221  -- MSD_SHIPMENT_HISTORY
222 
223  select cs_definition_id into lv_stream_id
224  from msd_cs_definitions
225  where name='MSD_SHIPMENT_HISTORY';
226 
227 OPEN shipment;
228    FETCH shipment BULK COLLECT INTO
229               lb_geo_dim,
230               lb_org_dim,
231               lb_prd_dim,
232               lb_rep_dim,
233               lb_chn_dim,
234               lb_ud1_dim,
235               lb_ud2_dim,
236               lb_dcs_dim;
237 
238 
239    IF shipment%ROWCOUNT > 0 THEN
240 
241       FORALL j IN lb_geo_dim.FIRST..lb_geo_dim.LAST
242         INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
243                    ( STREAM_ID,
244                      GEO_DIM,
245                      ORG_DIM,
246                      PRD_DIM,
247                      REP_DIM,
248                      CHN_DIM,
249                      UD1_DIM,
250                      UD2_DIM,
251                      DCS_DIM,
252                      BASE_STREAM_ID )
253               VALUES
254                   ( lv_stream_id,
255                     lb_geo_dim(j),
256                     lb_org_dim(j),
257                     lb_prd_dim(j),
258                     lb_rep_dim(j),
259                     lb_chn_dim(j),
260                     lb_ud1_dim(j),
261                     lb_ud2_dim(j),
262                     lb_dcs_dim(j),
263                     lv_stream_id);
264 
265      END IF;
266  CLOSE shipment;
267 
268    INSERT INTO MSD_CS_DTLS_TEMP
269                    ( CS_DEFINITION_ID,
270                      DIMENSION_CODE,
271                      COLLECT_LEVEL_ID )
272    SELECT a.cs_definition_id,
273           a.dimension_code,
274           a.collect_level_id
275    FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
276    WHERE a.cs_definition_id = b.cs_definition_id
277    AND b.cs_definition_id = lv_stream_id
278    AND a.dimension_code <> 'TIM'
279    AND a.collect_flag='Y'
280    AND b.strict_flag='Y'
281    AND b.valid_flag='Y'
282    AND b.enable_flag='Y'
283    AND EXISTS (select 1 from msd_shipment_data where rownum=1);
284 
285   --MSD_SHIPMENT_ORIG_HISTORY ??
286 
287 
288 EXCEPTION
289  WHEN OTHERS THEN
290     LOG_DEBUG ('when others error -LOAD_SHIPMENT_STREAM');
291 
292 END LOAD_SHIPMENT_STREAM;
293 
294 PROCEDURE LOAD_BOOKING_STREAM
295 IS
296 
297 CURSOR booking IS
298   SELECT  DISTINCT x.ship_to_loc,
299                    x.inv_org,
300                    x.item,
301                    x.sales_rep,
302                    x.sales_channel,
303                    x.user_defined1,
304                    x.user_defined2,
305                    null    -- dcs level value
306   FROM msd_booking_data_v x;
307 
308   lv_stream_id NUMBER;
309 
310 BEGIN
311 
312   -- MSD_BOOKING_HISTORY
313 
314  select cs_definition_id into lv_stream_id
315  from msd_cs_definitions
316  where name='MSD_BOOKING_HISTORY';
317 
318 OPEN booking;
319    FETCH booking BULK COLLECT INTO
320               lb_geo_dim,
321               lb_org_dim,
322               lb_prd_dim,
323               lb_rep_dim,
324               lb_chn_dim,
325               lb_ud1_dim,
326               lb_ud2_dim,
327               lb_dcs_dim;
328 
329 
330    IF booking%ROWCOUNT > 0 THEN
331 
332       FORALL j IN lb_geo_dim.FIRST..lb_geo_dim.LAST
333         INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
334                    ( STREAM_ID,
335                      GEO_DIM,
336                      ORG_DIM,
337                      PRD_DIM,
338                      REP_DIM,
339                      CHN_DIM,
340                      UD1_DIM,
341                      UD2_DIM,
342                      DCS_DIM,
343                      BASE_STREAM_ID )
344               VALUES
345                   ( lv_stream_id,
346                     lb_geo_dim(j),
347                     lb_org_dim(j),
348                     lb_prd_dim(j),
349                     lb_rep_dim(j),
350                     lb_chn_dim(j),
351                     lb_ud1_dim(j),
352                     lb_ud2_dim(j),
353                     lb_dcs_dim(j),
354                     lv_stream_id);
355 
356      END IF;
357  CLOSE booking;
358 
359    INSERT INTO MSD_CS_DTLS_TEMP
360                    ( CS_DEFINITION_ID,
361                      DIMENSION_CODE,
362                      COLLECT_LEVEL_ID )
363    SELECT a.cs_definition_id,
364           a.dimension_code,
365           a.collect_level_id
366    FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
367    WHERE a.cs_definition_id = b.cs_definition_id
368    AND b.cs_definition_id = lv_stream_id
369    AND a.dimension_code <> 'TIM'
370    AND a.collect_flag='Y'
371    AND b.strict_flag='Y'
372    AND b.valid_flag='Y'
373    AND b.enable_flag='Y'
374    AND EXISTS (select 1 from msd_booking_data where rownum=1);
375 
376   --MSD_BOOKING_ORIG_HISTORY ??
377 
378 EXCEPTION
379  WHEN OTHERS THEN
380     LOG_DEBUG ('when others error -LOAD_BOOKING_STREAM');
381 
382 END LOAD_BOOKING_STREAM;
383 
384 PROCEDURE LOAD_MFG_FORECAST
385 IS
386 
387 CURSOR mfg_forecast IS
388 select distinct ship_to_loc_pk.level_value,
389                 org_pk.level_value,
390                 item_pk.level_value,
391                 null,
392                 sales_channel_pk.level_value,
393                 null,
394                 null,
395                 dcs_pk.level_value
396 from
397     msd_mfg_forecast mbd,
398     msd_level_values org_pk,
399     msd_level_values dcs_pk,
400     msd_level_values item_pk,
401     msd_level_values sales_channel_pk,
402     msd_level_values ship_to_loc_pk
403 WHERE (org_pk.instance = mbd.instance and org_pk.sr_level_pk = mbd.sr_inv_org_pk and org_pk.level_id = 7)
404 AND (dcs_pk.instance = mbd.instance and dcs_pk.sr_level_pk = mbd.sr_demand_class_pk and dcs_pk.level_id = 34)
405 AND (item_pk.instance = mbd.instance and item_pk.sr_level_pk = mbd.sr_item_pk and item_pk.level_id = 1)
406 AND (sales_channel_pk.instance(+) = mbd.instance and sales_channel_pk.sr_level_pk(+) = mbd.sr_sales_channel_pk and sales_channel_pk.level_id(+) = 27)
407 AND (ship_to_loc_pk.instance(+) = mbd.instance and ship_to_loc_pk.sr_level_pk(+) = mbd.sr_ship_to_loc_pk and ship_to_loc_pk.level_id(+) = 11);
408 
409 lv_stream_id NUMBER;
410 lv_forecast_designator NUMBER;
411 
412 BEGIN
413 
414  select cs_definition_id into lv_stream_id
415  from msd_cs_definitions
416  where name='MSD_MANUFACTURING_FORECAST';
417 
418  select count(*) into lv_forecast_designator
419  from ( select distinct forecast_designator from msd_mfg_forecast);
420 
421  IF ( lv_forecast_designator = 1 ) THEN
422 
423    OPEN mfg_forecast;
424    FETCH mfg_forecast BULK COLLECT INTO
425               lb_geo_dim,
426               lb_org_dim,
427               lb_prd_dim,
428               lb_rep_dim,
429               lb_chn_dim,
430               lb_ud1_dim,
431               lb_ud2_dim,
432               lb_dcs_dim;
433 
434 
435    IF mfg_forecast%ROWCOUNT > 0 THEN
436 
437       FORALL j IN lb_geo_dim.FIRST..lb_geo_dim.LAST
438         INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
439                    ( STREAM_ID,
440                      GEO_DIM,
441                      ORG_DIM,
442                      PRD_DIM,
443                      REP_DIM,
444                      CHN_DIM,
445                      UD1_DIM,
446                      UD2_DIM,
447                      DCS_DIM,
448                      BASE_STREAM_ID )
449               VALUES
450                   ( lv_stream_id,
451                     lb_geo_dim(j),
452                     lb_org_dim(j),
453                     lb_prd_dim(j),
454                     lb_rep_dim(j),
455                     lb_chn_dim(j),
456                     lb_ud1_dim(j),
457                     lb_ud2_dim(j),
458                     lb_dcs_dim(j),
459                     lv_stream_id);
460 
461      END IF;
462  CLOSE mfg_forecast;
463 
464    INSERT INTO MSD_CS_DTLS_TEMP
465                    ( CS_DEFINITION_ID,
466                      DIMENSION_CODE,
467                      COLLECT_LEVEL_ID )
468    SELECT a.cs_definition_id,
469           a.dimension_code,
470           a.collect_level_id
471    FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
472    WHERE a.cs_definition_id = b.cs_definition_id
473    AND b.cs_definition_id = lv_stream_id
474    AND a.dimension_code <> 'TIM'
475    AND a.collect_flag='Y'
476    AND b.strict_flag='Y'
477    AND b.valid_flag='Y'
478    AND b.enable_flag='Y'
479    AND EXISTS (select 1 from MSD_MFG_FCST_CS_V where rownum=1);
480 
481  ELSE
482    NULL;
483  END IF;
484 
485 EXCEPTION
486  WHEN OTHERS THEN
487     LOG_DEBUG ('when others error -LOAD_MFG_FORECAST');
488 
489 END LOAD_MFG_FORECAST;
493 IS
490 
491 
492 PROCEDURE get_in_order ( p_stream_tbl IN OUT NOCOPY stream_tbl_type )
494 
495 temp_rec            stream_rec_type;
496 l_max_index         NUMBER;
497 
498 BEGIN
499 
500   FOR i in 1..p_stream_tbl.COUNT
501    LOOP
502 
503      l_max_index := i ;
504 
505       FOR j in i+1..p_stream_tbl.COUNT
506         LOOP
507 
508          IF p_stream_tbl(j).l_distinct_distributions > p_stream_tbl(l_max_index).l_distinct_distributions
509            THEN
510 
511             l_max_index := j;
512 
513          END IF;
514 
515       END LOOP;
516 
517 
518       temp_rec                  := p_stream_tbl(l_max_index);
519       p_stream_tbl(l_max_index) := p_stream_tbl(i);
520       p_stream_tbl(i)           := temp_rec ;
521 
522   END LOOP;
523 
524 EXCEPTION
525  WHEN OTHERS THEN
526     LOG_DEBUG ('when others error -get_in_order');
527 
528 END get_in_order;
529 
530 
531 
532 
533 PROCEDURE MSD_ASSIGN_GROUPS (p_stream_tbl IN OUT NOCOPY stream_tbl_type)
534 
535 
536 IS
537 
538 l_grouped            BOOLEAN;
539 l_base_stream_index  NUMBER;
540 lv_sql_stmt          VARCHAR2(2000);
541 l_temp_rec           stream_rec_type;
542 
543 l_overlap            NUMBER;
544 l_current_group      NUMBER;
545 
546 
547 BEGIN
548 
549   LOG_MESSAGE('Entered in MSD_ASSIGN_GROUPS');
550 
551   LOG_MESSAGE ('Number of streams in this particular stream set :'||p_stream_tbl.COUNT);
552 
553 /*  For Logging purpose  */
554    FOR j in 1..p_stream_tbl.COUNT
555    LOOP
556     LOG_MESSAGE ( 'Element '||j||': '||p_stream_tbl(j).p_stream_id||', '||p_stream_tbl(j).l_reading_level_string||', '||p_stream_tbl(j).l_groupable );
557    END LOOP;
558 
559 
560 
561 
562    IF p_stream_tbl.COUNT = 1 THEN
563 
564      v_group_number := v_group_number + 1;
565      p_stream_tbl(1).l_group := v_group_number;
566 
567      LOG_DEBUG('Value of v_group_number '||v_group_number);
568 
569      v_counter := v_counter + 1;
570      v_final_tbl(v_counter) := p_stream_tbl(1);
571 
572      LOG_DEBUG('Value of v_counter '||v_counter);
573 
574  /*    LOG_DEBUG('*********SHOW v_final_tbl when count is one-starts********');
575 
576 
577        For Logging purpose
578       FOR j in 1..v_final_tbl.COUNT
579         LOOP
580            LOG_DEBUG ( 'Element '||j||': '||v_final_tbl(j).p_stream_id||', '||v_final_tbl(j).l_reading_level_string||', '||v_final_tbl(j).l_groupable||', '||v_final_tbl(j).l_group );
581       END LOOP;
582 
583 
584 
585      LOG_DEBUG('*********SHOW v_final_tbl when count is one-ends********'); */
586 
587    ELSE
588 
589     LOG_DEBUG ('***********SORTING STARTS***************** ');
590 
591        get_in_order(p_stream_tbl);
592 
593 
594        FOR j in 1..p_stream_tbl.COUNT
595          LOOP
596            LOG_DEBUG ( 'Element '||j||': '||p_stream_tbl(j).p_stream_id||', '||p_stream_tbl(j).l_distinct_distributions||', '||p_stream_tbl(j).l_dimension_clause );
597        END LOOP;
598 
599     LOG_DEBUG ('***********SORTING ENDS***************** ');
600 
601 
602      v_group_number := v_group_number + 1;
603      p_stream_tbl(1).l_group := v_group_number;
604 
605      LOG_DEBUG('Value of v_group_number '||v_group_number);
606 
607      l_current_group :=v_group_number;
608 
609 
610      FOR i IN 2..p_stream_tbl.COUNT
611        LOOP
612 
613        l_grouped             := FALSE;
614        l_base_stream_index   := 0;
615 
616        LOG_DEBUG('-------Value of i is:'||i||'--------');
617 
618          FOR j IN REVERSE 1..(i-1)
619            LOOP
620 
621            LOG_DEBUG('-------Value of j is:'||j||'--------');
622 
623            IF ( (not l_grouped)  AND (p_stream_tbl(j).l_base_stream_id = p_stream_tbl(j).p_stream_id) ) THEN
624 
625                 lv_sql_stmt := '   select sum(count(*))/2  '
626                              ||' from MSD_DISTINCT_DIM_VAL_TEMP  '
627                              ||' where base_stream_id in ( :l_id1, :l_id2 )  '
628                              ||' group by  '||p_stream_tbl(i).l_dimension_clause
629                              ||' having count(*) > 1 ';
630 
631                      LOG_DEBUG('------SQL statement1 to be executed is : '||lv_sql_stmt);
632                      LOG_DEBUG('------Parameters are l_id1'||p_stream_tbl(i).l_base_stream_id);
633                      LOG_DEBUG('------Parameters are l_id2'||p_stream_tbl(j).l_base_stream_id);
634 
635 
636                      EXECUTE IMMEDIATE lv_sql_stmt INTO l_overlap USING p_stream_tbl(i).l_base_stream_id,
637                                                                         p_stream_tbl(j).l_base_stream_id;
638 
639                       LOG_DEBUG('---- Value of l_overlap is : '||l_overlap||'--------');
640 
641                    IF (( l_overlap/(p_stream_tbl(i).l_stream_count + p_stream_tbl(j).l_distinct_distributions - l_overlap)) > v_threshold_overlap )
642                     THEN
643 
644                         LOG_DEBUG('----Passes the threshold value----------');
645 
646                          l_grouped                                  := TRUE;
650                          p_stream_tbl(j).l_distinct_distributions   := p_stream_tbl(j).l_distinct_distributions + p_stream_tbl(i).l_stream_count - l_overlap;
647                          p_stream_tbl(i).l_group                    := p_stream_tbl(j).l_group;
648                          l_base_stream_index                        := j;
649                          p_stream_tbl(i).l_base_stream_id           := p_stream_tbl(j).p_stream_id;
651 
652                       /*
653                        lv_sql_stmt :=  '  UPDATE MSD_DISTINCT_DIM_VAL_TEMP   '
654                                      ||'  SET base_stream_id = 0             '
655                                      || ' WHERE rowid in ( select min(rowid) '
656                                                           ||' from MSD_DISTINCT_DIM_VAL_TEMP '
657                                                           ||' where base_stream_id in (:l_id1, :l_id2) '
658                                                           ||' group by '||p_stream_tbl(i).l_dimension_clause
659                                                           ||' having count(*) > 1)';  */
660 
661                         lv_sql_stmt :=   '  UPDATE MSD_DISTINCT_DIM_VAL_TEMP '
662                                        ||'  SET BASE_STREAM_ID = 0 '
663                                        ||'  WHERE ROWID IN ( select x.rowid from '
664                                        ||'                     (select geo_dim,org_dim,prd_dim,rep_dim,chn_dim,dcs_dim,ud1_dim,ud2_dim from MSD_DISTINCT_DIM_VAL_TEMP where base_stream_id=:l_id1) x, '
665                                        ||'                     (select geo_dim,org_dim,prd_dim,rep_dim,chn_dim,dcs_dim,ud1_dim,ud2_dim from MSD_DISTINCT_DIM_VAL_TEMP where base_stream_id=:l_id2) y  '
666                                        ||'                   where nvl(x.geo_dim,-1) = nvl(y.geo_dim,-1) '
667                                        ||'                   and   nvl(x.org_dim,-1) = nvl(y.org_dim,-1) '
668                                        ||'                   and   nvl(x.prd_dim,-1) = nvl(y.prd_dim,-1) '
669                                        ||'                   and   nvl(x.rep_dim,-1) = nvl(y.rep_dim,-1) '
670                                        ||'                   and   nvl(x.chn_dim,-1) = nvl(y.chn_dim,-1) '
671                                        ||'                   and   nvl(x.dcs_dim,-1) = nvl(y.dcs_dim,-1) '
672                                        ||'                   and   nvl(x.ud1_dim,-1) = nvl(y.ud1_dim,-1) '
673 				       ||'                   and   nvl(x.ud2_dim,-1) = nvl(y.ud2_dim,-1)  )';
674 
675 
676                         LOG_DEBUG('------SQL statement2 to be executed is : '||lv_sql_stmt);
677                         LOG_DEBUG('------Parameters are l_id1'||p_stream_tbl(i).p_stream_id);
678                         LOG_DEBUG('------Parameters are l_id2'||p_stream_tbl(j).p_stream_id);
679 
680                                EXECUTE IMMEDIATE lv_sql_stmt  USING  p_stream_tbl(i).p_stream_id,
681                                                                      p_stream_tbl(j).p_stream_id;
682 
683 
684 
685                         UPDATE MSD_DISTINCT_DIM_VAL_TEMP
686                         SET    base_stream_id  = p_stream_tbl(j).p_stream_id
687                         WHERE  stream_id       = p_stream_tbl(i).p_stream_id
688                         AND    base_stream_id  = p_stream_tbl(i).p_stream_id;
689 
690                 END IF; /* check for p_threshold */
691 
692 
693            ELSIF l_grouped AND ( p_stream_tbl(j).l_base_stream_id =  p_stream_tbl(j).p_stream_id ) THEN
694 
695              LOG_DEBUG('-------Entered in the conditioned where l_grouped is TRUE-----');
696 
697                IF p_stream_tbl(j).l_distinct_distributions < p_stream_tbl(l_base_stream_index).l_distinct_distributions
698                  THEN
699 
700                  LOG_DEBUG('---------Entered in the swapping-----');
701 
702                    l_temp_rec                           := p_stream_tbl(j);
703                    p_stream_tbl(j)                      := p_stream_tbl(l_base_stream_index);
704                    p_stream_tbl(l_base_stream_index)    := l_temp_rec;
705 
706                    l_base_stream_index := j;
707 
708                END IF;
709 
710            END IF;
711 
712 
713 
714           END LOOP; /* for j */
715 
716 
717        IF p_stream_tbl(i).l_group IS NULL THEN
718 
719            LOG_DEBUG('------Entered in the condition -> where l_group is still NULL--------');
720 
721            l_current_group :=l_current_group + 1;
722            v_group_number := v_group_number + 1;
723            p_stream_tbl(i).l_group := l_current_group;
724 
725            LOG_DEBUG('Value of v_group_number '||v_group_number);
726 
727        END IF;
728 
729      END LOOP; /* for i */
730 
731 
732         LOG_DEBUG('----- OUT of i LOOP , incrementing the value of v_counter-------');
733 
734         FOR i IN 1..p_stream_tbl.COUNT LOOP
735 
736            v_counter := v_counter + 1;
737            v_final_tbl(v_counter) := p_stream_tbl(i);
738 
739            LOG_DEBUG('Value of v_counter '||v_counter);
740 
741         END LOOP;
742 
743    END IF;
744 
745 
746    IF v_counter = v_index - 1  THEN
747 
748      LOG_MESSAGE ('Procedure MSD_ASSIGN_GROUPS is sucessfully completed for all the groupable set of streams');
749 
750      --LOG_DEBUG ('value of v_final_tbl.COUNT    '||v_final_tbl.COUNT);
751 
752    /*  -- For Logging purpose
753       FOR j in 1..v_final_tbl.COUNT
754         LOOP
758 
755            LOG_DEBUG ( 'Element '||j||': '||v_final_tbl(j).p_stream_id||', '||v_final_tbl(j).l_reading_level_string||', '||v_final_tbl(j).l_groupable||', '||v_final_tbl(j).l_group );
756       END LOOP;
757     */
759    END IF;
760 
761 
762 EXCEPTION
763  WHEN OTHERS THEN
764     LOG_DEBUG ('when others error -MSD_ASSIGN_GROUPS');
765     ROLLBACK;
766     LOG_DEBUG(SQLERRM);
767 
768 END MSD_ASSIGN_GROUPS;
769 
770 
771 PROCEDURE MSD_GROUP_STREAMS (  ERRBUF   OUT NOCOPY VARCHAR2,
772 			       RETCODE  OUT NOCOPY NUMBER,
773 			       p_mode IN NUMBER DEFAULT SYS_NO,
774 			       p_threshold_overlap IN NUMBER DEFAULT NULL)
775 IS
776 
777  stream_tbl1     stream_tbl_type;
778  stream_tbl2     stream_tbl_type;
779  stream_tbl3     stream_tbl_type;
780 
781 lv_cs_definition_id  NUMBER;
782 lv_dimension_code    VARCHAR2(10);
783 lv_cur_stmt          VARCHAR2(2000);
784 
785 lv_reading_level_string  VARCHAR2(255);
786 lv_dimension_clause      VARCHAR2(255);
787 lv_stream_count          NUMBER;
788 
789 
790 lv_groupable   PLS_INTEGER;
791 x_var         PLS_INTEGER;
792 l_lower       PLS_INTEGER;
793 l_upper       PLS_INTEGER;
794 
795 
796 CURSOR c IS
797   SELECT  DISTINCT x.cs_definition_id,
798       x.attribute_8,
799       x.attribute_12,
800       x.attribute_4,
801       x.attribute_20,
802       x.attribute_24,
803       x.attribute_28,
804       x.attribute_32,
805       x.attribute_47,
806       x.cs_definition_id
807   FROM msd_cs_data x;
808 
809 CURSOR c1 IS
810   SELECT a.cs_definition_id,a.dimension_code,a.collect_level_id
811   FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
812   WHERE a.cs_definition_id = b.cs_definition_id
813   AND a.dimension_code <> 'TIM'
814   AND a.collect_flag='Y'
815   AND b.strict_flag='Y'
816   AND b.valid_flag='Y'
817   AND b.enable_flag='Y'
818   AND ( ( b.multiple_stream_flag='N') OR ( b.multiple_stream_flag='Y' and MSD_COMPOSITE_GROUPING.number_of_designators(b.cs_definition_id)=1 ))
819   AND EXISTS (select 1 from msd_cs_data where cs_definition_id=a.cs_definition_id) ;
820     --need to elimintate custom streams having multiple streams with more than one designator.
821 
822 CURSOR c2 IS
823  SELECT cs_definition_id,dimension_code
824  FROM   MSD_CS_DTLS_TEMP
825  WHERE COLLECT_LEVEL_ID IS NULL;
826 
827  TYPE mastcurtyp IS REF CURSOR;
828  c3 mastcurtyp;
829 
830  --Made c3 Cursor dynamic for Bug 3476620.
831 /*
832 CURSOR c3 IS
833 SELECT cs_definition_id stream_id,
834 MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) stream_count,
835 MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) distinct_stream_count,
836 cs_definition_id base_stream_id,
837 dim1||decode(dim2,NULL,'',','||dim2)||decode(dim3,NULL,'',','||dim3)||decode(dim4,NULL,'',','||dim4)||decode(dim5,NULL,'',','||dim5)||decode(dim6,NULL,'',','||dim6)||decode(dim7,NULL,'',','||dim7)||decode(dim8,NULL,'',','||dim8)
838 reading_level_string,
839 MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim1)||decode(dim2,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim2))||decode(dim3,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim3))||decode(dim4,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim4))||
840 decode(dim5,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim5))||decode(dim6,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim6))||decode(dim7,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim7))||
841 decode(dim8,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim8)) dimension_clause
842 FROM
843 (select cs_definition_id,collect_level_id as dim1,
844 LEAD(collect_level_id,1) over (partition by cs_definition_id order by collect_level_id) as dim2,
845 LEAD(collect_level_id,2) over (partition by cs_definition_id order by collect_level_id) as dim3,
846 LEAD(collect_level_id,3) over (partition by cs_definition_id order by collect_level_id) as dim4,
847 LEAD(collect_level_id,4) over (partition by cs_definition_id order by collect_level_id) as dim5,
848 LEAD(collect_level_id,5) over (partition by cs_definition_id order by collect_level_id) as dim6,
849 LEAD(collect_level_id,6) over (partition by cs_definition_id order by collect_level_id) as dim7,
850 LEAD(collect_level_id,7) over (partition by cs_definition_id order by collect_level_id) as dim8,
851 row_number() over (partition by cs_definition_id order by collect_level_id) as rno
852 from MSD_CS_DTLS_TEMP )
853 WHERE rno=1
854 ORDER BY dim1,dim2,dim3,dim4,dim5,dim6,dim7,dim8;
855 */
856 
857  BEGIN
858 
859    v_threshold_overlap  := p_threshold_overlap;
860    v_threshold_overlap  :=0.85;                  -- Hardcoded Threshold percentage
861 
862   ----- populating the temporary tables MSD_DISTINCT_DIM_VAL_TEMP and MSD_CS_DTLS_TEMP -------
863 
864   --TRUNCATE TABLE MSD_DISTINCT_DIM_VAL_TEMP;
865 
866  OPEN c;
867    FETCH c BULK COLLECT INTO
868               lb_stream_id,
869               lb_geo_dim,
870               lb_org_dim,
871               lb_prd_dim,
872               lb_rep_dim,
873               lb_chn_dim,
874               lb_ud1_dim,
875               lb_ud2_dim,
876               lb_dcs_dim,
877               lb_base_stream_id;
878 
879    IF c%ROWCOUNT > 0 THEN
880 
884                      GEO_DIM,
881       FORALL j IN lb_stream_id.FIRST..lb_stream_id.LAST
882         INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
883                    ( STREAM_ID,
885                      ORG_DIM,
886                      PRD_DIM,
887                      REP_DIM,
888                      CHN_DIM,
889                      UD1_DIM,
890                      UD2_DIM,
891                      DCS_DIM,
892                      BASE_STREAM_ID )
893               VALUES
894                   ( lb_stream_id(j),
895                     lb_geo_dim(j),
896                     lb_org_dim(j),
897                     lb_prd_dim(j),
898                     lb_rep_dim(j),
899                     lb_chn_dim(j),
900                     lb_ud1_dim(j),
901                     lb_ud2_dim(j),
902                     lb_dcs_dim(j),
903                     lb_base_stream_id(j));
904 
905      END IF;
906  CLOSE c;
907 
908   --TRUNCATE TABLE MSD_CS_DTLS_TEMP;
909 
910  OPEN c1;
911    FETCH c1 BULK COLLECT INTO
912               lb_cs_definition_id,
913               lb_dimension_code,
914               lb_level_id;
915 
916    IF c1%ROWCOUNT > 0 THEN
917 
918       FORALL j IN lb_cs_definition_id.FIRST..lb_cs_definition_id.LAST
919         INSERT INTO MSD_CS_DTLS_TEMP
920                    ( CS_DEFINITION_ID,
921                      DIMENSION_CODE,
922                      COLLECT_LEVEL_ID )
923               VALUES
924                   ( lb_cs_definition_id(j),
925                     lb_dimension_code(j),
926                     lb_level_id(j)          );
927 
928      END IF;
929  CLOSE c1;
930 
931   LOAD_SHIPMENT_STREAM;
932 
933   LOAD_BOOKING_STREAM;
934 
935   LOAD_MFG_FORECAST;
936 
937  OPEN c2;
938    LOOP
939     FETCH c2 INTO lv_cs_definition_id,lv_dimension_code;
940       EXIT WHEN c2%NOTFOUND;
941       GET_LEVEL_ID (lv_cs_definition_id,lv_dimension_code);
942    END LOOP;
943  CLOSE c2;
944 
945  COMMIT;
946 
947  ---- Both Temporary tables are populated by here -------------
948 
949  --Made c3 Cursor dynamic for Bug 3476620.
950  lv_cur_stmt :=    ' SELECT cs_definition_id stream_id, '
951  ||' MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) stream_count, '
952  ||' MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) distinct_stream_count, '
953  ||' cs_definition_id base_stream_id, '
954  ||' dim1||decode(dim2,NULL,'''','',''||dim2)||decode(dim3,NULL,'''','',''||dim3)||decode(dim4,NULL,'''','',''||dim4)||decode(dim5,NULL,'''','',''||dim5)||decode(dim6,NULL,'''','',''||dim6)|| '
955  ||' decode(dim7,NULL,'''','',''||dim7)||decode(dim8,NULL,'''','',''||dim8) reading_level_string, '
956  ||' MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim1)||decode(dim2,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim2))||decode(dim3,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim3))|| '
957  ||' decode(dim4,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim4))|| '
958  ||' decode(dim5,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim5))||decode(dim6,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim6))||decode(dim7,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim7))|| '
959  ||' decode(dim8,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim8)) dimension_clause '
960  ||' FROM '
961  ||' (select cs_definition_id,collect_level_id as dim1, '
962  ||' LEAD(collect_level_id,1) over (partition by cs_definition_id order by collect_level_id) as dim2, '
963  ||' LEAD(collect_level_id,2) over (partition by cs_definition_id order by collect_level_id) as dim3, '
964  ||' LEAD(collect_level_id,3) over (partition by cs_definition_id order by collect_level_id) as dim4, '
965  ||' LEAD(collect_level_id,4) over (partition by cs_definition_id order by collect_level_id) as dim5, '
966  ||' LEAD(collect_level_id,5) over (partition by cs_definition_id order by collect_level_id) as dim6, '
967  ||' LEAD(collect_level_id,6) over (partition by cs_definition_id order by collect_level_id) as dim7, '
968  ||' LEAD(collect_level_id,7) over (partition by cs_definition_id order by collect_level_id) as dim8, '
969  ||' row_number() over (partition by cs_definition_id order by collect_level_id) as rno '
970  ||' from MSD_CS_DTLS_TEMP ) '
971  ||' WHERE rno=1 '
972  ||' ORDER BY dim1,dim2,dim3,dim4,dim5,dim6,dim7,dim8 ';
973 
974  /* Populating the pl/sql table for all the possible streams to be grouped */
975 
976  OPEN c3 FOR lv_cur_stmt;  --Made c3 Cursor dynamic for Bug 3476620.
977    v_index :=1;
978 
979    LOOP
980 
981      LOG_DEBUG (' Value of v_index '||v_index);
982      FETCH c3 INTO stream_tbl1(v_index).p_stream_id,
983                    stream_tbl1(v_index).l_stream_count,
984                    stream_tbl1(v_index).l_distinct_distributions,
985                    stream_tbl1(v_index).l_base_stream_id,
986                    stream_tbl1(v_index).l_reading_level_string,
987                    stream_tbl1(v_index).l_dimension_clause;
988 
989        EXIT WHEN c3%NOTFOUND;
990        v_index := v_index + 1;
991 
992    END LOOP;
993 
994  CLOSE c3;
995 
996 
997 
998 
999  /*  Forming the set of streams that can be grouped */
1000   lv_groupable :=1;
1001   stream_tbl1(1).l_groupable := lv_groupable;
1002 
1003   FOR i IN 2..stream_tbl1.COUNT
1004    LOOP
1005       LOG_DEBUG (' Value of lv_groupable '||lv_groupable);
1006       IF stream_tbl1(i).l_reading_level_string = stream_tbl1(i-1).l_reading_level_string THEN
1010          stream_tbl1(i).l_groupable := lv_groupable;
1007          stream_tbl1(i).l_groupable := lv_groupable;
1008       ELSE
1009          lv_groupable := lv_groupable + 1;
1011       END IF;
1012 
1013     END LOOP;
1014 
1015    /*  For Logging purpose  */
1016    FOR j in 1..stream_tbl1.COUNT
1017    LOOP
1018     LOG_MESSAGE ( 'Element '||j||': '||stream_tbl1(j).p_stream_id||', '||stream_tbl1(j).l_reading_level_string||', '||stream_tbl1(j).l_groupable );
1019    END LOOP;
1020 
1021 
1022 
1023 
1024  ----  Calling function  MSD_ASSIGN_GROUPS for each set of groupable streams----------
1025 
1026    v_group_number := 0;
1027    v_counter      := 0;
1028 
1029    FOR i IN 1..lv_groupable
1030    LOOP
1031 
1032      get_boundaries(i,l_lower,l_upper,stream_tbl1);
1033 
1034      LOG_DEBUG (' i ='||i);
1035      LOG_DEBUG('l_lower '||l_lower);
1036      LOG_DEBUG('l_upper '||l_upper);
1037 
1038 
1039      x_var := 1;
1040      FOR j IN l_lower..l_upper
1041      LOOP
1042        LOG_DEBUG ('x_var is'||x_var);
1043        stream_tbl2(x_var) :=stream_tbl1(j);
1044        x_var := x_var + 1;
1045      END LOOP;
1046 
1047      MSD_ASSIGN_GROUPS ( stream_tbl2 );
1048 
1049      stream_tbl2 := stream_tbl3;
1050 
1051    END LOOP;
1052 
1053 
1054 
1055 
1056 
1057  ----  Based on the application mode updating/displaying or displaying the Composite grouping
1058 
1059  IF p_mode = SYS_YES THEN
1060 
1061   LOG_MESSAGE('-------APPLICATION MODE is YES---------');
1062 
1063    LOG_MESSAGE('*************FINAL OUTPUT - STARTS**************');
1064 
1065    UPDATE MSD_CS_DEFINITIONS
1066    SET COMPOSITE_GROUP_CODE = to_number(NULL);
1067 
1068 
1069    FOR j in 1..v_final_tbl.COUNT
1070    LOOP
1071     LOG_MESSAGE ( 'Element '||j||': '||v_final_tbl(j).p_stream_id||', '||v_final_tbl(j).l_dimension_clause||', '||v_final_tbl(j).l_group);
1072 
1073    UPDATE MSD_CS_DEFINITIONS
1074    SET COMPOSITE_GROUP_CODE =  v_final_tbl(j).l_group
1075    WHERE CS_DEFINITION_ID   =  v_final_tbl(j).p_stream_id;
1076 
1077    END LOOP;
1078 
1079    LOG_MESSAGE('*************FINAL OUTPUT - ENDS**************');
1080 
1081    COMMIT;
1082 
1083  ELSE
1084 
1085    LOG_MESSAGE('-----APPLICATION MODE is NO------');
1086 
1087    LOG_MESSAGE('*************FINAL OUTPUT - STARTS**************');
1088 
1089    /*  For Logging purpose  */
1090    FOR j in 1..v_final_tbl.COUNT
1091    LOOP
1092     LOG_MESSAGE ( 'Element '||j||': '||v_final_tbl(j).p_stream_id||', '||v_final_tbl(j).l_dimension_clause||', '||v_final_tbl(j).l_group);
1093    END LOOP;
1094 
1095    LOG_MESSAGE('*************FINAL OUTPUT - ENDS**************');
1096 
1097  END IF;
1098 
1099    RETCODE := G_SUCCESS;
1100 
1101 EXCEPTION
1102   WHEN OTHERS THEN
1103     LOG_DEBUG ('when others error -msd_group_streams');
1104     ROLLBACK;
1105     RETCODE := G_ERROR;
1106     LOG_DEBUG(SQLERRM);
1107 
1108 
1109 END MSD_GROUP_STREAMS;
1110 
1111 END MSD_COMPOSITE_GROUPING;