2 Replies Latest reply on Jul 15, 2016 6:53 AM by 2984326

    Profile option name not able to call by sql* plus report

    2984326

      Dear Support,

       

      If possible, could you please help us on the below issue which we are getting

       

      We had written a query to see the profile option "Utilities:Diagnostics" to see the users who are having access at all the level,

      We created a concurrent program and we are executing it as sql* plus report.

       

      Query used:

       

      SELECT distinct SUBSTR (pro1.user_profile_option_name, 1, 35) USER_PROFILE_OPTION_NAME,

               DECODE (pov.level_id,

                       10001, 'Site',

                       10002, 'Application',

                       10003, 'Resp',

                       10004, 'User')

                   as "PROFILE_LEVEL",

               DECODE (pov.level_id,

                       10001, 'Site',

                       10002, appl.application_short_name,

                       10003, resp.responsibility_name,

                       10004, u.user_name)

                  LEVEL_VALUE,

               NVL (pov.profile_option_value, 'Is Null') PROFILE_OPTION_VALUE,

               --pro.created_by CREATED_BY,

               (select b.user_name ||'-'||b.description from apps.fnd_user b where b.user_id=pov.created_by ) CREATED_BY,

               to_char(pro.creation_date,'DD-MON-YYYY') CREATION_DATE,

               (select b.user_name ||'-'||b.description from apps.fnd_user b where b.user_id=pov.last_updated_by ) LAST_UPDATED_BY ,

               to_char(pro1.last_update_date,'DD-MON-YYYY') LAST_UPDATE_DATE,

                (select Name||'-'||to_char(Current_Timestamp ,'dd-MON-yyyy HH24:MI:SS') From apps.Fnd_Apps_System) AS "INSTANCE"

               --into &USER_PROFILE_OPTION_NAME,  &PROFILE_LEVEL,  &LEVEL_VALUE,  &PROFILE_OPTION_VALUE,  &CREATED_BY,  &CREATION_DATE,  &LAST_UPDATED_BY,  ----&LAST_UPDATE_DATE,  &INSTANCE

          FROM apps.fnd_profile_option_values pov,

               apps.fnd_responsibility_tl resp,

               apps.fnd_application appl,

               apps.fnd_user u,

               apps.fnd_profile_options pro,

               apps.fnd_profile_options_vl pro1

         WHERE     pro1.user_profile_option_name LIKE ('Utilities:Diagnostics')

               AND pro.profile_option_name = pro1.profile_option_name

               AND pro.profile_option_id = pov.profile_option_id

              -- AND resp.responsibility_name LIKE '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */

               AND pov.level_value = resp.responsibility_id(+)

               AND pov.level_value = appl.application_id(+)

               AND pov.level_value = u.user_id(+)

        AND pov.profile_option_value='Y'

               --and u.user_id=pro.created_by

      ORDER BY 1, 2

       

      When running it through database retrieving records when running through EBS as an registered report concurrent program, the program completed normal on clicking view output the error printing is

       

      Enter value for user_profile_option_name: EXEC FND_CONC_STAT.COLLECT;

      Enter value for profile_level:

      Enter value for level_value: EXIT

      Enter value for profile_option_value:

      User requested Interrupt or EOF detected.

       

      Could you please advise how to create a text output

       

      Thanks,

      Sathish