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:
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,
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,
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,
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:
649: p_stream_tbl(i).l_base_stream_id := p_stream_tbl(j).p_stream_id;
650: p_stream_tbl(j).l_distinct_distributions := p_stream_tbl(j).l_distinct_distributions + p_stream_tbl(i).l_stream_count - l_overlap;
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) '
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:
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 '
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) '
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) '
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:
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;
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,
878:
879: IF c%ROWCOUNT > 0 THEN
880:
881: FORALL j IN lb_stream_id.FIRST..lb_stream_id.LAST
882: INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
883: ( STREAM_ID,
884: GEO_DIM,
885: ORG_DIM,
886: PRD_DIM,