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.

APPS.MSD_COMPOSITE_GROUPING SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 73


 select dimension_code
 into lv_dimension_code
 from msd_levels
 where level_id=p_level_id
 and plan_type is NULL;           -- Bug# 4928951

Line: 94


 select count(*) into lv_desig_count
 from (select distinct cs_name from msd_cs_data where cs_definition_id=p_cs_definition_id);

Line: 108


 select count(*)
 into lv_count
 from MSD_DISTINCT_DIM_VAL_TEMP
 where stream_id=p_cs_definition_id;

Line: 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))
 from msd_cs_data
 where cs_definition_id= p_cs_definition_id
 and cs_data_id = (select min(cs_data_id) from msd_cs_data where cs_definition_id=p_cs_definition_id);

Line: 139


    UPDATE MSD_CS_DTLS_TEMP
    SET COLLECT_LEVEL_ID  = lv_collect_level_id
    WHERE DIMENSION_CODE  = p_dimension_code
    AND  CS_DEFINITION_ID = p_cs_definition_id;

Line: 207


  SELECT  DISTINCT x.ship_to_loc,
                   x.inv_org,
                   x.item,
                   x.sales_rep,
                   x.sales_channel,
                   x.user_defined1,
                   x.user_defined2,
                   null    -- dcs level value
  FROM msd_shipment_data_v x;

Line: 223


 select cs_definition_id into lv_stream_id
 from msd_cs_definitions
 where name='MSD_SHIPMENT_HISTORY';

Line: 242


        INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
                   ( STREAM_ID,
                     GEO_DIM,
                     ORG_DIM,
                     PRD_DIM,
                     REP_DIM,
                     CHN_DIM,
                     UD1_DIM,
                     UD2_DIM,
                     DCS_DIM,
                     BASE_STREAM_ID )
              VALUES
                  ( lv_stream_id,
                    lb_geo_dim(j),
                    lb_org_dim(j),
                    lb_prd_dim(j),
                    lb_rep_dim(j),
                    lb_chn_dim(j),
                    lb_ud1_dim(j),
                    lb_ud2_dim(j),
                    lb_dcs_dim(j),
                    lv_stream_id);

Line: 268


   INSERT INTO MSD_CS_DTLS_TEMP
                   ( CS_DEFINITION_ID,
                     DIMENSION_CODE,
                     COLLECT_LEVEL_ID )
   SELECT a.cs_definition_id,
          a.dimension_code,
          a.collect_level_id
   FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
   WHERE a.cs_definition_id = b.cs_definition_id
   AND b.cs_definition_id = lv_stream_id
   AND a.dimension_code <> 'TIM'
   AND a.collect_flag='Y'
   AND b.strict_flag='Y'
   AND b.valid_flag='Y'
   AND b.enable_flag='Y'
   AND EXISTS (select 1 from msd_shipment_data where rownum=1);

Line: 298


  SELECT  DISTINCT x.ship_to_loc,
                   x.inv_org,
                   x.item,
                   x.sales_rep,
                   x.sales_channel,
                   x.user_defined1,
                   x.user_defined2,
                   null    -- dcs level value
  FROM msd_booking_data_v x;

Line: 314


 select cs_definition_id into lv_stream_id
 from msd_cs_definitions
 where name='MSD_BOOKING_HISTORY';

Line: 333


        INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
                   ( STREAM_ID,
                     GEO_DIM,
                     ORG_DIM,
                     PRD_DIM,
                     REP_DIM,
                     CHN_DIM,
                     UD1_DIM,
                     UD2_DIM,
                     DCS_DIM,
                     BASE_STREAM_ID )
              VALUES
                  ( lv_stream_id,
                    lb_geo_dim(j),
                    lb_org_dim(j),
                    lb_prd_dim(j),
                    lb_rep_dim(j),
                    lb_chn_dim(j),
                    lb_ud1_dim(j),
                    lb_ud2_dim(j),
                    lb_dcs_dim(j),
                    lv_stream_id);

Line: 359


   INSERT INTO MSD_CS_DTLS_TEMP
                   ( CS_DEFINITION_ID,
                     DIMENSION_CODE,
                     COLLECT_LEVEL_ID )
   SELECT a.cs_definition_id,
          a.dimension_code,
          a.collect_level_id
   FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
   WHERE a.cs_definition_id = b.cs_definition_id
   AND b.cs_definition_id = lv_stream_id
   AND a.dimension_code <> 'TIM'
   AND a.collect_flag='Y'
   AND b.strict_flag='Y'
   AND b.valid_flag='Y'
   AND b.enable_flag='Y'
   AND EXISTS (select 1 from msd_booking_data where rownum=1);

Line: 388


select distinct ship_to_loc_pk.level_value,
                org_pk.level_value,
                item_pk.level_value,
                null,
                sales_channel_pk.level_value,
                null,
                null,
                dcs_pk.level_value
from
    msd_mfg_forecast mbd,
    msd_level_values org_pk,
    msd_level_values dcs_pk,
    msd_level_values item_pk,
    msd_level_values sales_channel_pk,
    msd_level_values ship_to_loc_pk
WHERE (org_pk.instance = mbd.instance and org_pk.sr_level_pk = mbd.sr_inv_org_pk and org_pk.level_id = 7)
AND (dcs_pk.instance = mbd.instance and dcs_pk.sr_level_pk = mbd.sr_demand_class_pk and dcs_pk.level_id = 34)
AND (item_pk.instance = mbd.instance and item_pk.sr_level_pk = mbd.sr_item_pk and item_pk.level_id = 1)
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)
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);

Line: 414


 select cs_definition_id into lv_stream_id
 from msd_cs_definitions
 where name='MSD_MANUFACTURING_FORECAST';

Line: 418


 select count(*) into lv_forecast_designator
 from ( select distinct forecast_designator from msd_mfg_forecast);

Line: 438


        INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
                   ( STREAM_ID,
                     GEO_DIM,
                     ORG_DIM,
                     PRD_DIM,
                     REP_DIM,
                     CHN_DIM,
                     UD1_DIM,
                     UD2_DIM,
                     DCS_DIM,
                     BASE_STREAM_ID )
              VALUES
                  ( lv_stream_id,
                    lb_geo_dim(j),
                    lb_org_dim(j),
                    lb_prd_dim(j),
                    lb_rep_dim(j),
                    lb_chn_dim(j),
                    lb_ud1_dim(j),
                    lb_ud2_dim(j),
                    lb_dcs_dim(j),
                    lv_stream_id);

Line: 464


   INSERT INTO MSD_CS_DTLS_TEMP
                   ( CS_DEFINITION_ID,
                     DIMENSION_CODE,
                     COLLECT_LEVEL_ID )
   SELECT a.cs_definition_id,
          a.dimension_code,
          a.collect_level_id
   FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
   WHERE a.cs_definition_id = b.cs_definition_id
   AND b.cs_definition_id = lv_stream_id
   AND a.dimension_code <> 'TIM'
   AND a.collect_flag='Y'
   AND b.strict_flag='Y'
   AND b.valid_flag='Y'
   AND b.enable_flag='Y'
   AND EXISTS (select 1 from MSD_MFG_FCST_CS_V where rownum=1);

Line: 625


                lv_sql_stmt := '   select sum(count(*))/2  '
                             ||' from MSD_DISTINCT_DIM_VAL_TEMP  '
                             ||' where base_stream_id in ( :l_id1, :l_id2 )  '
                             ||' group by  '||p_stream_tbl(i).l_dimension_clause
                             ||' having count(*) > 1 ';

Line: 653


                       lv_sql_stmt :=  '  UPDATE MSD_DISTINCT_DIM_VAL_TEMP   '
                                     ||'  SET base_stream_id = 0             '
                                     || ' WHERE rowid in ( select min(rowid) '
                                                          ||' from MSD_DISTINCT_DIM_VAL_TEMP '
                                                          ||' where base_stream_id in (:l_id1, :l_id2) '
                                                          ||' group by '||p_stream_tbl(i).l_dimension_clause
                                                          ||' having count(*) > 1)';  */

Line: 661


                        lv_sql_stmt :=   '  UPDATE MSD_DISTINCT_DIM_VAL_TEMP '
                                       ||'  SET BASE_STREAM_ID = 0 '
                                       ||'  WHERE ROWID IN ( select x.rowid from '
                                       ||'                     (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, '
                                       ||'                     (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  '
                                       ||'                   where nvl(x.geo_dim,-1) = nvl(y.geo_dim,-1) '
                                       ||'                   and   nvl(x.org_dim,-1) = nvl(y.org_dim,-1) '
                                       ||'                   and   nvl(x.prd_dim,-1) = nvl(y.prd_dim,-1) '
                                       ||'                   and   nvl(x.rep_dim,-1) = nvl(y.rep_dim,-1) '
                                       ||'                   and   nvl(x.chn_dim,-1) = nvl(y.chn_dim,-1) '
                                       ||'                   and   nvl(x.dcs_dim,-1) = nvl(y.dcs_dim,-1) '
                                       ||'                   and   nvl(x.ud1_dim,-1) = nvl(y.ud1_dim,-1) '
				       ||'                   and   nvl(x.ud2_dim,-1) = nvl(y.ud2_dim,-1)  )';

Line: 685


                        UPDATE MSD_DISTINCT_DIM_VAL_TEMP
                        SET    base_stream_id  = p_stream_tbl(j).p_stream_id
                        WHERE  stream_id       = p_stream_tbl(i).p_stream_id
                        AND    base_stream_id  = p_stream_tbl(i).p_stream_id;

Line: 797


  SELECT  DISTINCT x.cs_definition_id,
      x.attribute_8,
      x.attribute_12,
      x.attribute_4,
      x.attribute_20,
      x.attribute_24,
      x.attribute_28,
      x.attribute_32,
      x.attribute_47,
      x.cs_definition_id
  FROM msd_cs_data x;

Line: 810


  SELECT a.cs_definition_id,a.dimension_code,a.collect_level_id
  FROM msd_cs_defn_dim_dtls a, msd_cs_definitions b
  WHERE a.cs_definition_id = b.cs_definition_id
  AND a.dimension_code <> 'TIM'
  AND a.collect_flag='Y'
  AND b.strict_flag='Y'
  AND b.valid_flag='Y'
  AND b.enable_flag='Y'
  AND ( ( b.multiple_stream_flag='N') OR ( b.multiple_stream_flag='Y' and MSD_COMPOSITE_GROUPING.number_of_designators(b.cs_definition_id)=1 ))
  AND EXISTS (select 1 from msd_cs_data where cs_definition_id=a.cs_definition_id) ;

Line: 823


 SELECT cs_definition_id,dimension_code
 FROM   MSD_CS_DTLS_TEMP
 WHERE COLLECT_LEVEL_ID IS NULL;

Line: 833


SELECT cs_definition_id stream_id,
MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) stream_count,
MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) distinct_stream_count,
cs_definition_id base_stream_id,
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)
reading_level_string,
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))||
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))||
decode(dim8,NULL,'',','||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim8)) dimension_clause
FROM
(select cs_definition_id,collect_level_id as dim1,
LEAD(collect_level_id,1) over (partition by cs_definition_id order by collect_level_id) as dim2,
LEAD(collect_level_id,2) over (partition by cs_definition_id order by collect_level_id) as dim3,
LEAD(collect_level_id,3) over (partition by cs_definition_id order by collect_level_id) as dim4,
LEAD(collect_level_id,4) over (partition by cs_definition_id order by collect_level_id) as dim5,
LEAD(collect_level_id,5) over (partition by cs_definition_id order by collect_level_id) as dim6,
LEAD(collect_level_id,6) over (partition by cs_definition_id order by collect_level_id) as dim7,
LEAD(collect_level_id,7) over (partition by cs_definition_id order by collect_level_id) as dim8,
row_number() over (partition by cs_definition_id order by collect_level_id) as rno
from MSD_CS_DTLS_TEMP )
WHERE rno=1
ORDER BY dim1,dim2,dim3,dim4,dim5,dim6,dim7,dim8;

Line: 882


        INSERT INTO MSD_DISTINCT_DIM_VAL_TEMP
                   ( STREAM_ID,
                     GEO_DIM,
                     ORG_DIM,
                     PRD_DIM,
                     REP_DIM,
                     CHN_DIM,
                     UD1_DIM,
                     UD2_DIM,
                     DCS_DIM,
                     BASE_STREAM_ID )
              VALUES
                  ( lb_stream_id(j),
                    lb_geo_dim(j),
                    lb_org_dim(j),
                    lb_prd_dim(j),
                    lb_rep_dim(j),
                    lb_chn_dim(j),
                    lb_ud1_dim(j),
                    lb_ud2_dim(j),
                    lb_dcs_dim(j),
                    lb_base_stream_id(j));

Line: 919


        INSERT INTO MSD_CS_DTLS_TEMP
                   ( CS_DEFINITION_ID,
                     DIMENSION_CODE,
                     COLLECT_LEVEL_ID )
              VALUES
                  ( lb_cs_definition_id(j),
                    lb_dimension_code(j),
                    lb_level_id(j)          );

Line: 950


 lv_cur_stmt :=    ' SELECT cs_definition_id stream_id, '
 ||' MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) stream_count, '
 ||' MSD_COMPOSITE_GROUPING.GET_STREAM_COUNT(cs_definition_id) distinct_stream_count, '
 ||' cs_definition_id base_stream_id, '
 ||' 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) reading_level_string, '
 ||' 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))|| '
 ||' 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))|| '
 ||' decode(dim8,NULL,'''','',''||MSD_COMPOSITE_GROUPING.GET_DIM_CODE(dim8)) dimension_clause '
 ||' FROM '
 ||' (select cs_definition_id,collect_level_id as dim1, '
 ||' LEAD(collect_level_id,1) over (partition by cs_definition_id order by collect_level_id) as dim2, '
 ||' LEAD(collect_level_id,2) over (partition by cs_definition_id order by collect_level_id) as dim3, '
 ||' LEAD(collect_level_id,3) over (partition by cs_definition_id order by collect_level_id) as dim4, '
 ||' LEAD(collect_level_id,4) over (partition by cs_definition_id order by collect_level_id) as dim5, '
 ||' LEAD(collect_level_id,5) over (partition by cs_definition_id order by collect_level_id) as dim6, '
 ||' LEAD(collect_level_id,6) over (partition by cs_definition_id order by collect_level_id) as dim7, '
 ||' LEAD(collect_level_id,7) over (partition by cs_definition_id order by collect_level_id) as dim8, '
 ||' row_number() over (partition by cs_definition_id order by collect_level_id) as rno '
 ||' from MSD_CS_DTLS_TEMP ) '
 ||' WHERE rno=1 '
 ||' ORDER BY dim1,dim2,dim3,dim4,dim5,dim6,dim7,dim8 ';

Line: 1065


   UPDATE MSD_CS_DEFINITIONS
   SET COMPOSITE_GROUP_CODE = to_number(NULL);

Line: 1073


   UPDATE MSD_CS_DEFINITIONS
   SET COMPOSITE_GROUP_CODE =  v_final_tbl(j).l_group
   WHERE CS_DEFINITION_ID   =  v_final_tbl(j).p_stream_id;