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;

Demantra- Query to find level information

 Query to find level information

DECLARE

l_sql VARCHAR2(1000);

l_num NUMBER;

l_dbschema VARCHAR2(240) := 'DM12';

l_query VARCHAR2(2000);

l_gtable VARCHAR2(50);

l_status VARCHAR2(50);

l_table_label VARCHAR2(100);

TYPE rcur IS REF CURSOR;

rec rcur;

BEGIN

l_query := 'SELECT gtable,table_label,status FROM '||l_dbschema||'.group_tables

          WHERE 1 = 1

          AND UPPER(search_table) IN (''ITEMS'',''LOCATION'')';

OPEN rec FOR l_query;

LOOP

 FETCH rec INTO l_gtable,l_table_label,l_status;

 EXIT WHEN rec%NOTFOUND;

 l_sql := null;

 l_num := 0;

 l_sql := 'SELECT count(*) FROM '||l_dbschema||'.'||l_gtable;

 EXECUTE IMMEDIATE l_sql INTO l_num;

 IF l_num > 2 THEN

    dbms_output.put_line('Level Name :'||l_table_label||'  Table : '||l_gtable||' Status: '||l_status);

 END IF;

END LOOP;

CLOSE rec;

END;

Demantra- Key Tables

DEMANTRA: Key Tables

 Base Tables : 

S.No	Table Name	Important Columns	Description
1	SALES_DATA	ITEM_ID, LOCATION_ID, SALES_DATE	This is the main table which contains all the sales data history and forecast. It also contains any new series you created
2	MDP_MATRIX	ITEM_ID, LOCATION_ID, SALES_DATE, T_EP_ITEM_EP_ID, T_EP_SITE_EP_ID	This is table contains the combinations of item and locations
3	T_EP_ITEMS	T_EP_ITEM_EP_ID, ITEM, DM_ITEM_DESC	Stores the item information
4	T_EP_SITES	T_EP_SITE_EP_ID, SITE, DM_SITE_DESC	States the location/sites information
5	INPUTS	DATET	Contains the time buckets
6	COMPUTED_FIELDS	COMPUTED_NAME, COMPUTED_TITLE,DBNAME	Series definition and its name
7	WF_PROCESS_LOG	SCHEMA_ID, PROCESS_ID	Workflow instances or processes
8	TRANSFER_QUERY	VIEW_NAME, TABLE_NAME, QUERY_NAME, FROM_DATE, UNTIL_DATE	integration interface definitions
9	VERSION_DETAILS	VERSION	Demantra application version details
10	DB_EXCEPTION_LOG	ERR_DATE, PROC_NAME, ERR_MSG	Stores erros if there are any errors in the system
11	NOTES	NOTE_DESC, USER_ID	Notes information
12	WF_SCHEMAS	SCHEMA_NAME	Workflow definition
13	T_EP_EBS_CUST_CLASS	T_EP_CUST_CLASS_EP_ID, EBS_CUST_CLASS, EBS_CUST_CLASS_DESC	Customer Class
14	T_EP_EBS_CUSTOMER	T_EP_EBS_CUSTOMER_EP_ID,EBS_CUSTOMER, EBS_CUSTOMER_DESC	Customer information
15	T_EP_EBS_ACCOUNT	T_EP_EBS_ACCOUNT_EP_ID, EBS_ACCOUNT, EBS_ACCOUNT_DESC	Customer Account information
16	T_EP_ORGANIZATION	T_EP_ORGANIZATION-EP_ID, ORGANIZATION, DM_ORG_DESC	Inventory Organizations
17	T_EP_I_ATT_1	I_ATT_1	Level/Attribute information
18	T_EP_I_ATT_2	I_ATT_2	Level/Attribute information
19	T_EP_I_ATT_3	I_ATT_3	Level/Attribute information
20	T_EP_I_ATT_4	I_ATT_4	Level/Attribute information
21	T_EP_I_ATT_5	I_ATT_5	Level/Attribute information
22	SERIES_GROUPS	GROUP_NAME	Series GroupHeader
23	SERIES_GROUPS_M	SERIES_ID, GROUP_ID	Series group and series

 Interface Tables : 

S.No	Table Name	Important Columns	Description
1	T_SRC_ITEM_TMPL	DM_ITEM_CODE, DM_ITEM_DESC	Interface Table for Items
2	T_SRC_LOC_TMPL	DM_SITE_CODE, DM_SITE_DESC,DM_ORG_CODE, DM_ORG_DESC	Interface Table for Locations
3	T_SRC_SALES_TMPL	DM_ITEM_CODE,DM_SITE_CODE, SALES_DATE,  DM_ORG_CODE,,ACTUAL_QTY, ITEM_PRICE	Interface Tables for sales history data

 Important Procedures :

S.No	Procedure Name	Description
1	EP_LOAD_MAIN	Main procedure which loads all item , locations and sales date into Demantra
2	EP_LOAD_ITEMS	Loads only items
3	EP_LOAD_LOCATIONS	Loads only locations
4	EP_LOAD_SALES	loads only sales data
5	PRE_LOGON	Alter the session to set the language, date format etc