Demantra - Worksheet assigned Series

Worksheet assigned Series

SELECT DISTINCT q.query_name "Worksheet Name"
  --,gt.TABLE_LABEL "Level Name"
  ,
  cf.computed_title "Series Name"
FROM active_series a ,
  computed_fields cf ,
  queries q ,
  groups g
  --,group_tables gt
  --,queries_for_users qfu
  --,user_id ui
WHERE 1                =1
AND a.query_id         = q.query_id
AND a.forecast_type_id = cf.forecast_type_id
AND g.query_id         =q.query_id
  -- and g.group_table_id = gt.group_table_id
  --and q.query_id = qfu.query_id
  -- and qfu.user_id = ui.user_id
AND q.query_name = 'Worksheet Name' ;

Demantra - Query to get user assigned series and worksheets

Query to get user assigned series and worksheets

SELECT ui.user_name ,
  q.query_name "Worksheet Name" ,
  gt.TABLE_LABEL "Level Name" ,
  cf.computed_title "Series Name"
FROM active_series a ,
  computed_fields cf ,
  queries q ,
  groups g ,
  group_tables gt ,
  queries_for_users qfu ,
  user_id ui
WHERE 1                =1
AND a.query_id         = q.query_id
AND a.forecast_type_id = cf.forecast_type_id
AND g.query_id         =q.query_id
AND g.group_table_id   = gt.group_table_id
AND q.query_id         = qfu.query_id
AND qfu.user_id        = ui.user_id
AND ui.user_name       =<USERNAME>;

Demantra - Query to get user assigned series

Query to get user assigned series

select uss.user_id
      ,ui.user_name
      ,cf.COMPUTED_TITLE
      ,cf.computed_name
      ,cf.dbname
from computed_fields cf
    ,user_security_series uss
    ,user_id ui
where 1=1
and cf.forecast_type_id =uss.series_id
and uss.user_id=ui.user_id
and ui.user_name =<USERNAME>;

APPS - dbms_stats.gather_schema_stats

dbms_stats.gather_schema_stats


PROCEDURE  schema_stats

IS
/*
      ------------------------------------------------------------------
      Procedure to run gather_schema_stats
      ------------------------------------------------------------------
*/
   v_sql                     VARCHAR2(32000);
   v_proc                    VARCHAR2(30) := 'schema_stats';
   v_msg                     VARCHAR2(4000);
   v_procedure               NUMBER  := 0;
   v_start_time              NUMBER;
   v_end_time                NUMBER;
   schema_name   VARCHAR2(30) := '<Schema_Name>';


BEGIN
/* This script deriving set default session parameters  */
   pre_logon;

   v_start_time := DBMS_UTILITY.get_time;
 
/*
      ------------------------------------------------------------------
      check the demantra schema global variable
      ------------------------------------------------------------------
*/

IF v_lc_dem_schema IS NOT NULL THEN

 BEGIN
   sys.dbms_stats.gather_schema_stats(ownname => schema_name, estimate_percent => 80,degree => 16);
   EXCEPTION
   WHEN OTHERS THEN
   v_msg :=(' - Error while gathering stats on '||v_lc_dem_schema||' '||SQLERRM);
   DBEX( v_msg, v_proc, 'E' );
  END;
/*
      ------------------------------------------------------------------
      Record timings
      ------------------------------------------------------------------
*/

   v_end_time  := DBMS_UTILITY.get_time;
   v_procedure := (( v_end_time - v_start_time ) / 100 );

   v_msg       :='Procedure Completed in '|| LPAD(TO_CHAR(v_procedure),8) ||' seconds';
   DBEX( v_msg, v_proc, 'T' );
COMMIT;
ELSE
v_msg := '- Procedure completed with Error';
  DBEX( v_msg, v_proc,'E');
END IF;
EXCEPTION
WHEN OTHERS THEN
  v_msg := 'Demantra Maintenance Process - SCHEMA_STATS status:- '||SQLERRM;
  DBEX( v_msg, v_proc, 'E' );
END schema_stats;



Parameter
ownname
 
Schema to analyze (NULL means current schema).

estimate_percent
 
Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100). Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.

block_sample
 
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt
 
Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_
clause] [,column|attribute [size_clause]...],
where size_clause is defined as:

size_clause := SIZE {integer | REPEAT | AUTO |
SKEWONLY},
where integer is in the range [1,254]. This value is passed to all of the individual tables.

degree
 
Degree of parallelism. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.

granularity
 
Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.

SUBPARTITION: Gather subpartition-level statistics.

PARTITION: Gather partition-level statistics.

GLOBAL: Gather global statistics.

ALL: Gather all (subpartition, partition, and global) statistics. 

cascade
 
Gather statistics on the indexes as well.

Index statistics gathering is not parallelized. Using this option is equivalent to running the gather_index_stats procedure on each of the indexes in the schema in addition to gathering table and column statistics.

stattab
 
User stat table identifier describing where to save the current statistics.

statid
 
Identifier (optional) to associate with these statistics within stattab.

options
 
Further specification of which objects to gather statistics for:

GATHER: Gathers statistics on all objects in the schema.

GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.

LIST EMPTY: Returns list of objects which currently have no statistics.

objlist
 
List of objects found to be stale or empty.

statown
 
Schema containing stattab (if different than ownname).

Demantra - Script to validate Object Status

Script to validate Object Status

SELECT
OWNER
,OBJECT_NAME
,OBJECT_TYPE
,case STATUS
  when 'INVALID' then '<html><font size="4" color="red">'||STATUS
  when 'VALID' then '<html><font size="4" color="green">'||STATUS
  end
  STATUS
,CREATED
,LAST_DDL_TIME
,TIMESTAMP
FROM SYS.DBA_OBJECTS
WHERE OBJECT_NAME = <Object Name>

Demantra - Script to validate Series Information

Script to validate Series Info

SELECT
FORECAST_TYPE_ID
,COMPUTED_TITLE
,COMPUTED_NAME
,DECODE(IS_DEFAULT,1,'Check',0,'Uncheck',IS_DEFAULT) IS_DEFAULT
,DECODE(HIST_PRED_TYPE,2,'Forecast',1,'History','3','History ' || chr(38) ||' Forecast') HIST_PRED_TYPE
,DECODE (EDITABLE,3,'Yes',0,'No',EDITABLE) EDITABLE
,HINT_MESSAGE
,AGGR_BY
,DECODE (INFO_TYPE,1,'Table and Graph',2,'Table Only',3,'Graph Only') DISPLAY_TYPE
,DISP_COLOR
,PRINT_COLOR
,DECODE(DISP_LSTYLE,1,'Line',2,'Dash',3,'Dash Dot',4,'Dash Dot Dot',5,'Dot',6,'Transparent') DISPLAY_STYLE
,DECODE(PRINT_LSTYLE,1,'Line',2,'Dash',3,'Dash Dot',4,'Dash Dot Dot',5,'Dot',6,'Transparent') LINE_STYLE
,DECODE(DISP_LSYMBOL,1,'No Symbol',3,'X',12,'Plus',DISP_LSYMBOL) SYmbol_Display
,DECODE(PRINT_LSYMBOL,1,'No Symbol',3,'X',PRINT_LSYMBOL) SYmbol_Print
,TABLE_FORMAT Display_Format
,SUM_FUNC
,CLIENT_EXP_DISP
,SERIES_WIDTH
,COL_SERIES_WIDTH
,DROPDOWN_TABLE_NAME
,LOOKUP_DISPLAY_FIELD
,LOOKUP_DATA_FIELD
,LOOKUP_SECURITY_TYPE_ID
,LOOKUP_EXTRA_FROM
,LOOKUP_EXTRA_WHERE
,DATA_TABLE_NAME
,DBNAME
,DECODE(Data_Type,1,'Numeric',2,'Date',3,'String') DATA_TYPE
,UPDATE_BY_SERIES_ID
,SYNCRO_FIELD
,DECODE(IS_CHECK,1,'Check',0,'Uncheck') IS_CHECK
,DECODE(SCALEBLE,1,'Check',0,'Uncheck') SCALEBLE
,DECODE(SAME_VAL_UPDATE,1,'Check',0,'Uncheck') SAME_VAL_UPDATE
,DECODE(IS_PROPORTION,1,'Check',0,'Uncheck') IS_PROPORTION
,WAVG_BY_SERIES
,AGGR_BY
,INT_AGGR_FUNC
,WAVG_BY_SERIES
,DECODE(PRESERVATION_TYPE,1,'Volume Preservation',2,'Percentage Preservation',3,'Most Common',PRESERVATION_TYPE) PRESERVATION_TYPE
,DECODE(NULL_AS_ZERO,1,'Check',0,'Uncheck') NULL_AS_ZERO
,DECODE(MOVE_PRESERVATION_TYPE,1,'Volume Preservation',2,'Percentage Preservation',3,'Most Common',MOVE_PRESERVATION_TYPE) MOVE_PRESERVATION_TYPE
,EXP_TEMPLATE
,EXTRA_FROM
,EXTRA_WHERE
,CLIENT_EXP_DISP
,LOCK_EXP_DISP
,BACKGROUND_EXP_DISP
,DECODE(MODULE_TYPE,0,'Oracle Demantra Demand Management',MODULE_TYPE) MODULE_TYPE
,MODIFIED_BY
FROM
XXDEM.COMPUTED_FIELDS WHERE 1=1
AND COMPUTED_TITLE like ('<Series Name>')

Demantra- Query to get Worksheet info

Query to get Worksheet info


SELECT *
FROM
  (SELECT DISTINCT q.query_name Worksheet_Name ,
    gt.table_label "Aggrigation Selected levels" ,
    g.GORDER Aggregation_Position ,
    DECODE(q.tf_type_id,1,'Specify as Fixed Dates' ,2,'Spicify Relative to Today' ,3,'Specify Relative to Last Sales Date') "Time Window" ,
    q.Time_Filter_Before "Time Periods Before" ,
    q.time_filter_after "Time Periods After" ,
    (SELECT table_label
    FROM group_tables
    WHERE group_table_id = f.group_table_id
    ) "Selected filter levels" ,
    DECODE(
    (SELECT table_label FROM group_tables WHERE group_table_id = f.group_table_id
    ),'Demand Class',
    (SELECT ebs_demand_class
    FROM t_ep_ebs_demand_class
    WHERE t_ep_ebs_demand_class_ep_id=fv.filter_value
    ) ,'Organization',
    (SELECT organization
    FROM t_ep_organization
    WHERE t_ep_organization_ep_id=fv.filter_value
    ) ,'Customer',
    (SELECT ebs_customer
    FROM t_ep_ebs_customer
    WHERE t_ep_ebs_customer_ep_id=fv.filter_value
    ) ,'Forecaster Name',
    (SELECT i_att_4 FROM t_ep_i_att_4 WHERE t_ep_i_att_4_ep_id=fv.filter_value
    ) ,'Item Type',
    (SELECT i_att_5 FROM t_ep_i_att_5 WHERE t_ep_i_att_5_ep_id=fv.filter_value
    ) ,'Site',
    (SELECT site FROM t_ep_site WHERE t_ep_site_ep_id=fv.filter_value
    ) ,'Customer',
    (SELECT ebs_customer
    FROM t_ep_ebs_customer
    WHERE T_EP_EBS_CUSTOMER_EP_ID=fv.filter_value
    ) ,'Item Description',
    (SELECT I_ATT_1 FROM t_ep_I_ATT_1 WHERE T_EP_I_ATT_1_EP_ID=fv.filter_value
    ) ,'Engine Model',
    (SELECT ENGINE_MODEL
    FROM t_ep_engine_model
    WHERE T_EP_ENGINE_MODEL_EP_ID=fv.filter_value
    ) ,'Item',
    (SELECT ITEM FROM t_ep_item WHERE T_EP_ITEM_EP_ID=fv.filter_value
    ) ,'Lead Part',
    (SELECT LEAD_PART
    FROM t_ep_lead_part
    WHERE T_EP_LEAD_PART_EP_ID=fv.filter_value
    ) ,'Engine Family',
    (SELECT ENGINE_FAMILY
    FROM t_ep_engine_family
    WHERE T_EP_ENGINE_FAMILY_EP_ID=fv.filter_value
    ) ) "Selected Members"-- Add Specific levels with filter value
    ,
    cf.computed_title "Series Name"
  FROM active_series a ,
    queries q ,
    groups g ,
    group_tables gt ,
    filters f ,
    filter_values fv ,
    computed_fields cf
  WHERE 1               =1
  AND a.query_id        = q.query_id
  AND g.query_id        =q.query_id
  AND g.group_table_id  = gt.group_table_id
  AND f.query_id        = q.query_id
  AND f.filter_id       =fv.filter_id
  AND a.forecast_type_id=cf.forecast_type_id
  AND q.query_name      = (<Worksheet Name>)
  )
ORDER BY Worksheet_Name,
  Aggregation_Position ;