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;