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 ;

Demantra- Custom Procedure to clean log tables in Demantra

Custom Procedure to clean log tables in Demantra

PROCEDURE  clean_log_tab

/*

      ------------------------------------------------------------------

      Procedure to clean log tables(audit_trail,DB_EXCEPTION_LOG)

      ------------------------------------------------------------------

      */

IS

   vs_sql                     VARCHAR2(32000);

   vs_proc                    VARCHAR2(30) := 'clean_log_tab';

   vs_msg                     VARCHAR2(4000);

   vi_months_to_keep          NUMBER(5);

   vd_start_date              DATE;

   vd_end_date                DATE;

   vi_delete_count_audit      INTEGER := 0;

   vi_delete_count_c_log      INTEGER := 0;

   vi_delete_count_ds_log     INTEGER := 0;

   vi_delete_count_e_log      INTEGER := 0;

   vi_delete_count_hd_log     INTEGER := 0;

   vi_delete_count_h_log      INTEGER := 0;

   vi_delete_count_i_log      INTEGER := 0;

   vi_delete_count_p_log      INTEGER := 0;

   vi_delete_count_s_log      INTEGER := 0;

   vi_delete_count_sql_log    INTEGER := 0;

   vi_delete_count_t_log      INTEGER := 0;

   vi_delete_count_usql_log   INTEGER := 0;

   vi_delete_count_w_log      INTEGER := 0;

   vi_loop_count              INTEGER := 0;

   vt_procedure               NUMBER  := 0;

   vd_start_time              NUMBER;

   vd_end_time                NUMBER;

   v_lc_dem_schema            VARCHAR2(4000);



BEGIN



   pre_logon;



   vd_start_time := DBMS_UTILITY.get_time;

/*

      ------------------------------------------------------------------

      check the demantra schema global variable

      ------------------------------------------------------------------

*/

BEGIN

  SELECT parameter_value

  INTO v_lc_dem_schema

  FROM apps.msd_dem_setup_parameters

  WHERE parameter_name = 'MSD_DEM_SCHEMA';

EXCEPTION

WHEN NO_DATA_FOUND THEN

  vs_msg := 'Demantra Clean log tables  Process status:- '||SQLERRM;



WHEN OTHERS THEN

  vs_msg := 'Demantra Clean log tables  Process status:- '||SQLERRM;



END;

   

IF v_lc_dem_schema IS NOT NULL THEN

/*

      ------------------------------------------------------------------

      Get the audit_history_length in months from sys_params

      ------------------------------------------------------------------

*/



   get_param('sys_params','audit_history_length',vi_months_to_keep);



   vs_msg := 'audit_history_length => '|| NVL(TO_CHAR( vi_months_to_keep ),'NULL');

   DBEX(vs_msg, vs_proc, 'C');





  IF vi_months_to_keep > 0 THEN



      vi_months_to_keep := -1 * TO_NUMBER (TRIM( vi_months_to_keep ));

      vd_end_date       := add_months (CAST(SYSTIMESTAMP AS DATE), vi_months_to_keep);

/*

      ------------------------------------------------------------------

      Deleting data from Log Tables based on audit_history_length

      ------------------------------------------------------------------

*/



      IF get_is_table_exists( 'DB_CALL_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_call_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_c_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_DETAILED_SECTION_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_detailed_section_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_ds_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_EXCEPTION_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_exception_log WHERE  err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_e_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_HEALTH_DETAILED_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_health_detailed_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_hd_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_HEALTH_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_health_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_h_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_INFO_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_info_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_i_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_PATCH_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_patch_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_p_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_SECTION_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_section_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_s_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_SQL_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_sql_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_sql_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_TIMING_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_timing_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_t_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_UPGRADE_SQL_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_upgrade_sql_log WHERE  msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_sql_log := SQL%ROWCOUNT;

      END IF;



      IF get_is_table_exists( 'DB_WARNING_LOG' ) = 1 THEN

         vs_sql := 'DELETE '

                       ||v_lc_dem_schema

                       ||'.db_warning_log WHERE  err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';

         dynamic_ddl( vs_sql );

         vi_delete_count_w_log := SQL%ROWCOUNT;

      END IF;

      COMMIT;

   END IF;



   /***************************************************/

   /* Record timings

   /***************************************************/

   vd_end_time  := DBMS_UTILITY.get_time;

   vt_procedure := (( vd_end_time - vd_start_time ) / 100 );



   vs_msg       := 'Procedure Completed in '|| LPAD(TO_CHAR(vt_procedure),8) ||' seconds';

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := vs_msg ||' DELETE From '

                       ||v_lc_dem_schema

                       ||'.AUDIT_TRAIL ['|| TO_CHAR(NVL(vi_delete_count_audit,0))   ||']';       

   DBEX( vs_msg, vs_proc, 'T' );



   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_CALL_LOG             ['|| TO_CHAR(NVL(vi_delete_count_c_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_DETAILED_SECTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_ds_log,0))   ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_EXCEPTION_LOG        ['|| TO_CHAR(NVL(vi_delete_count_e_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_HEALTH_DETAILED_LOG  ['|| TO_CHAR(NVL(vi_delete_count_hd_log,0))   ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_HEALTH_LOG           ['|| TO_CHAR(NVL(vi_delete_count_h_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_INFO_LOG             ['|| TO_CHAR(NVL(vi_delete_count_i_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_PATCH_LOG            ['|| TO_CHAR(NVL(vi_delete_count_p_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_SECTION_LOG          ['|| TO_CHAR(NVL(vi_delete_count_s_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_SQL_LOG              ['|| TO_CHAR(NVL(vi_delete_count_sql_log,0))  ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_TIMING_LOG           ['|| TO_CHAR(NVL(vi_delete_count_t_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_UPGRADE_SQL_LOG      ['|| TO_CHAR(NVL(vi_delete_count_usql_log,0)) ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

   vs_msg       := 'DELETE From '

                       ||v_lc_dem_schema

                       ||'.DB_WARNING_LOG          ['|| TO_CHAR(NVL(vi_delete_count_w_log,0))    ||']';       

   DBEX( vs_msg, vs_proc, 'T' );

ELSE

vs_msg := vs_msg||'- Procedure completed with Error';

  DBEX( vs_msg, vs_proc,'E');

END IF;

EXCEPTION

WHEN OTHERS THEN

  vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;

  DBEX( vs_msg, vs_proc, 'E' );

END clean_log_tab;

Demantra- Procedure to launch Demantra Workflow from DB

 Demantra Workflow from DB

DECLARE
    /*
    ------------------------------------------------
    local Variable Declarations
    ------------------------------------------------
    */

    v_lc_api_name            VARCHAR2(30) := 'CALL_DEM_WORKFLOW_DB';
p_in_chr_wf_name VARCHAR2(30) :=<Demantra Workflow name>
    v_lc_schema_name         VARCHAR2(500);
    v_ln_start_time          NUMBER;
    v_ln_end_time            NUMBER;
    v_ln_code_tracker        NUMBER;
    v_ln_schema_id           NUMBER;
    v_ln_owner_id            NUMBER;
    v_ret_process_id         VARCHAR2(2000);
    v_exc_error EXCEPTION;
    v_lc_app_srvr_url        VARCHAR2(500);
    v_dm_username            VARCHAR2(30) := 'dm';
    v_sop_username           VARCHAR2(30) := 'sop';
    v_ln_user_name           VARCHAR2(30);
    v_lc_dm_pass             VARCHAR2(80);
    v_lc_sop_pass            VARCHAR2(80);
    v_fnl_username           VARCHAR2(30);
    v_fnl_password           VARCHAR2(80);
    v_wf_srvr_url            VARCHAR2(100) := '/WorkflowServer?action=run_proc';
    v_wf_user_url            VARCHAR2(100);
    v_wf_pass_url            VARCHAR2(100);
    v_wf_schem_url           VARCHAR2(100);
    v_fnl_wf_url             VARCHAR2(2000);
    v_ld_date                DATE;
    v_ln_cnt                 NUMBER;
    v_ln_cnt1                NUMBER;
    v_ln_cnt_fail            NUMBER;
    v_bool_fail              BOOLEAN;
    v_ln_rec_cre_dt          DATE := SYSDATE;
    v_lc_dem_schema          msd_dem_setup_parameters.parameter_value%TYPE;
    v_chr_apps_ser_url_prm   VARCHAR2(50) := 'AppServerURL';
    --v_ln_cnt1 number;
    v_process_id number(10);
BEGIN
    fnd_file.put_line(fnd_file.log,'Start of API : '
                                     || '.'
                                     || v_lc_api_name);

    v_ln_start_time := dbms_utility.get_time;
    p_out_err_messg := '';
    p_out_ret_code := 0;
    v_ln_code_tracker := 10;
BEGIN
    SELECT
        parameter_value
    INTO v_lc_dem_schema
    FROM
        apps.msd_dem_setup_parameters
    WHERE
        parameter_name = 'MSD_DEM_SCHEMA';
EXCEPTION WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
                                         || '.'
                                         || v_lc_api_name);
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
                                         || '.'
                                         || v_lc_api_name);
END;
      /*
      ------------------------------------------------------------------
      check the demantra schema global variable
      ------------------------------------------------------------------
      */

    IF v_lc_dem_schema IS NULL THEN
        fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
                                         || '.'
                                         || v_lc_api_name);

        RAISE v_exc_error;
    END IF;


      /*
      ----------------------------------------------------------------------------------
      ValiDATE schema name. check IF the workflow is present in wf_schemas
      ----------------------------------------------------------------------------------
      */

    BEGIN
        EXECUTE IMMEDIATE ' SELECT schema_id ,owner_id'
                          || chr(10)
                          || ' FROM '
                          || v_lc_dem_schema
                          || '.wf_schemas '
                          || chr(10)
                          || ' WHERE schema_name = :1 '
        INTO
            v_ln_schema_id,
            v_ln_owner_id
            USING p_in_chr_wf_name;
    EXCEPTION
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.log,'Unable to determine workflow : '
                                             || p_in_chr_wf_name
                                             || ' Please check workflow definition ');
            fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
            RAISE;
    END;


    fnd_file.put_line(fnd_file.log,'Schema ID : '
                                     || v_ln_schema_id
                                     || ' , '
                                     || 'Owner Id : '
                                     || v_ln_owner_id
                                     || ' - '
                                     || 'for workflow : '
                                     || p_in_chr_wf_name);

    fnd_file.put_line(fnd_file.output,'Schema ID : '
                                        || v_ln_schema_id
                                        || ' , '
                                        || 'Owner Id : '
                                        || v_ln_owner_id
                                        || ' - '
                                        || 'for workflow : '
                                        || p_in_chr_wf_name);

    IF v_ln_owner_id IS NOT NULL THEN
        BEGIN
            EXECUTE IMMEDIATE 'SELECT user_name'
                              || chr(10)
                              || 'FROM '
                              || v_lc_dem_schema
                              || '.user_id '
                              || chr(10)
                              || 'WHERE user_id = :1'
            INTO v_ln_user_name
                USING v_ln_owner_id;

        EXCEPTION
            WHEN OTHERS THEN
                fnd_file.put_line(fnd_file.log,'Unable to determine user_id : '
                                                 || v_ln_owner_id
                                                 || ' Please check workflow definition ');
                fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
                RAISE;
        END;
    ELSE
        v_ln_user_name := v_dm_username;
    END IF;

      /*
      ------------------------------------------------------------------
      Get the Apps Server URL for demantra FROM sys_params
      ------------------------------------------------------------------
      */

    BEGIN
        EXECUTE IMMEDIATE ' SELECT pval '
                          || chr(10)
                          || ' FROM '
                          || v_lc_dem_schema
                          || '.sys_params '
                          || chr(10)
                          || ' WHERE pname = :1 '
        INTO v_lc_app_srvr_url
            USING v_chr_apps_ser_url_prm;

        IF v_lc_app_srvr_url IS NULL THEN
            fnd_file.put_line(fnd_file.log,'Application server URL is blank or not set. cannot run workflow!!!!');
            RAISE v_exc_error;
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.log,'Unable to determine Application server URL : '
                                             || v_lc_app_srvr_url
                                             || ' Please check System parameters!!!');
            fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
            RAISE;
    END;

      /*
      ------------------------------------------------------------------
      Determine the user and password for submitting Workflow.
      we will mostly submit via DM or SOP. First preference is DM user
      ELSE SOP user.
      ------------------------------------------------------------------
      */

    BEGIN
        EXECUTE IMMEDIATE ' SELECT password '
                          || chr(10)
                          || ' FROM '
                          || v_lc_dem_schema
                          || '.user_id '
                          || chr(10)
                          || ' WHERE user_name = :1'
        INTO v_lc_dm_pass
            USING v_ln_user_name;
    EXCEPTION
        WHEN OTHERS THEN
            v_lc_dm_pass := NULL;
    END;

    IF v_lc_dm_pass IS NULL THEN
        BEGIN
            EXECUTE IMMEDIATE ' SELECT password '
                              || chr(10)
                              || ' FROM '
                              || v_lc_dem_schema
                              || '.user_id '
                              || chr(10)
                              || ' WHERE user_name = :1'
            INTO v_lc_sop_pass
                USING v_sop_username;
        EXCEPTION
            WHEN OTHERS THEN
                v_lc_sop_pass := NULL;
        END;

        v_fnl_username := v_sop_username;
        v_fnl_password := v_lc_sop_pass;
    ELSE
        v_fnl_username := v_ln_user_name;
        v_fnl_password := v_lc_dm_pass;
    END IF;

    IF v_fnl_password IS NULL THEN
        fnd_file.put_line(fnd_file.log,'Error determing dm/sop username, password. please check user ID table!!!!');
        RAISE v_exc_error;
    ELSE
        fnd_file.put_line(fnd_file.log,'user name : '
                                         || v_fnl_username
                                         || ' is SELECTed!!!! ');
        fnd_file.put_line(fnd_file.output,'user name : '
                                            || v_fnl_username
                                            || ' is SELECTed!!!! ');

        v_lc_schema_name := replace(p_in_chr_wf_name,' ','%20');
        v_wf_user_url := 'user=' || v_fnl_username;
        v_wf_pass_url := 'password=' || v_fnl_password;
        v_wf_schem_url := 'schema=' || v_lc_schema_name;
        v_fnl_wf_url := v_lc_app_srvr_url
                        || v_wf_srvr_url
                        || '&'
                        || v_wf_user_url
                        || '&'
                        || v_wf_pass_url
                        || '&'
                        || v_wf_schem_url;

        fnd_file.put_line(fnd_file.log,'Final URL : ' || v_fnl_wf_url);
        fnd_file.put_line(fnd_file.log,'Submitting workflow......');
        BEGIN
            SELECT
                utl_http.request(v_fnl_wf_url)
            INTO v_ret_process_id
            FROM
                dual;
        EXCEPTION
            WHEN OTHERS THEN
                fnd_file.put_line(fnd_file.log,'Error in submitting workflow : ' || sqlerrm);
                RAISE;
        END;

      /*
      ----------------------------------------------------
      check the status of the workflow FROM
      wf_process_log
      ----------------------------------------------------
      */

        v_ld_date := SYSDATE;
        LOOP
            dbms_lock.sleep(60); -- wait for a minute

        /****** check for program running ******/
            EXECUTE IMMEDIATE 'SELECT count(1) FROM  '
                              || v_lc_dem_schema
                              || '.wf_process_log WHERE schema_id = :1 and status = 1 and record_created >= :2'
            INTO v_ln_cnt
                USING v_ln_schema_id,v_ln_rec_cre_dt;

        /******** check for program failure ******/
            EXECUTE IMMEDIATE 'SELECT count(1) FROM  '
                              || v_lc_dem_schema
                              || '.wf_process_log WHERE schema_id = :1 and status = -1 and record_created >= :2'
            INTO v_ln_cnt_fail
                USING v_ln_schema_id,v_ln_rec_cre_dt;

       if v_ret_process_id IS NOT NULL THEN

          SELECT  count(1) INTO v_ln_cnt1
  FROM portal_task WHERE description LIKE '%'||v_ret_process_id||'%';

        IF v_ln_cnt1 > 0 THEN
              fnd_file.put_line(fnd_file.log,' Exiting program. Demantra workflow errored out. Please check related workflow logs'
              );
              RAISE v_exc_error;
       END IF;
 END IF;



            IF v_ln_cnt_fail > 0 THEN
                fnd_file.put_line(fnd_file.log,' Exiting program. Demantra workflow errored out. Please check related workflow logs'
                );
                RAISE v_exc_error;
            END IF;

            EXIT WHEN v_ln_cnt = 0;
        END LOOP;

        fnd_file.put_line(fnd_file.log,' Demantra workflow completed successfully.....');
    END IF;


      /*
      -----------------------------------------------------------------------------------
      Procedure level COMMIT
      -----------------------------------------------------------------------------------
      */

    v_ln_code_tracker := 70;
    COMMIT;
    v_ln_code_tracker := 80;
    v_ln_end_time := dbms_utility.get_time;
    fnd_file.put_line(fnd_file.log,'END of API : '
                                     || '.'
                                     || v_lc_api_name);

    fnd_file.put_line(fnd_file.log,'Total time taken for the API : '
                                     || round( (v_ln_end_time - v_ln_start_time) / 100,2) );

EXCEPTION
    WHEN OTHERS THEN
        p_out_err_messg := 'Error in API : '
                           || '.'
                           || v_lc_api_name
                           || ' - '
                           || sqlerrm
                           || ' at location : '
                           || v_ln_code_tracker;
fnd_file.put_line(fnd_file.log,'Error in API : '
                           || '.'
                           || v_lc_api_name
                           || ' - '
                           || sqlerrm
                           || ' at location : '
                           || v_ln_code_tracker);
        p_out_ret_code := 2;
END;

Demantra- Query to find Integration Interface

Query to find Integration Interface


SELECT ID,
  TRANSFER_ID,
  VIEW_NAME,
  TABLE_NAME,
  VIEW_TYPE,
  INSERTNEWCOMBINATIONS,
  QUERY_NAME,
  DESCRIPTION,
  FROM_DATE,
  UNTIL_DATE,
  RELATIVE_UNTIL_DATE,
  INTEGRATION_TYPE
FROM transfer_query
WHERE QUERY_NAME = <INTERFACE_NAME>

Demantra- Query to find series details

Query to find series details

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 =<Series Name>

Apps- Find Concurrent program and Request set Status

Find Concurrent program and Request set Status


SELECT   REQUEST_ID,
         DECODE(PHASE_CODE,'C','Completed'
                          ,'I','Inactive'
                          ,'P','Pending'
                          ,'R','Running'
                          ,PHASE_CODE) PHASE_CODE,
        DECODE(STATUS_CODE,'D','Cancelled'
                          ,'U','Disabled'
                          ,'E','Error'
                          ,'M','No Manager'
                          ,'R','Normal'
                          ,'I','Normal'
                          ,'C','Normal'
                          ,'H','On Hold'
                          ,'W','Paused'
                          ,'B','Resuming'
                          ,'P','Scheduled'
                          ,'Q','Standby'
                          ,'S','Suspended'
                          ,'X','Terminated'
                          ,'T','Terminating'
                          ,'A','Waiting'
                          ,'Z','Waiting'
                          ,'G','Warning'
                          ,STATUS_CODE) STATUS_CODE,
               RESPONSIBILITY_ID,
               ACTUAL_START_DATE,
              ACTUAL_COMPLETION_DATE,
              COMPLETION_TEXT,
              ARGUMENT_TEXT,
              PROGRAM_SHORT_NAME,
            REQUESTOR,
            PARENT_REQUEST_ID,
            RESPONSIBILITY_ID,
            COMPLETION_TEXT,
            PROGRAM,
            DESCRIPTION,
            USER_CONCURRENT_PROGRAM_NAME
  FROM APPS.FND_CONC_REQ_SUMMARY_V
 WHERE 1=1
--AND PROGRAM_SHORT_NAME = <PROGRAM SHORT NAME>
--AND REQUEST_ID=<REQUEST ID>
-- AND PARENT_REQUEST_ID= <REQUESTSET ID>
 ORDER BY REQUEST_ID DESC;