Active Responsibility List with Active User Count

If you need to create an ad-hoc report showing all the currently active Oracle responsibilities with all active users that are using them, below is a simple query that can help you.

SELECT

       fat.application_name         "Application Name",

       frv.responsibility_name      "Active Responsibility Name",

       COUNT(fu.user_name)          "Active User Count"

  FROM

       fnd_user                     fu,

       fnd_user_resp_groups_direct  furgd,

       fnd_responsibility_vl        frv,

       fnd_application_tl           fat

 WHERE

       1=1

   --

   AND furgd.end_date IS NULL

   --

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))

   --

   AND fat.application_id       =  frv.application_id

   --

   AND furgd.responsibility_id  =  frv.responsibility_id

   AND furgd.user_id            =  fu.user_id

   --

 GROUP BY fat.application_name, frv.responsibility_name

 ORDER BY fat.application_name, frv.responsibility_name;

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

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

-- delete concurrent program definition and executable from back-end

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

-- syntax:

--     delete_program    (program_short_name, application_short_name)

--     delete_executable (program_short_name, application_short_name)

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

DECLARE

  lv_prog_short_name    VARCHAR2(240);

  lv_appl_short_name    VARCHAR2(240);

BEGIN

   -- set the variables first

   lv_prog_short_name := 'XX_TEST';     -- concurrent program short name

   lv_appl_short_name := 'XX';          -- application short name

  

   -- see if the program exists. if found, delete the program

   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND

      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)    

   THEN

     

      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);

      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);

     

      COMMIT;

  

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');

  

   -- if the program does not exist in the system

   ELSE

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');

   END IF;

  

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

  

END;

Query to find the first word from a string

Recently I had a challenge to extract the first word from a string field. For example, I had to extract "Toshiba" from the string field, "Toshiba America Medical Systems" or "California" from the string "California", and so on.

Although, I initially created a CASE-WHEN statement for my query, using SUBSTR and INSTR functionality - which in fact worked just fine, but I kept thinking there must be a better way to handle this.

Sure enough, with a little bit of Google search, I stumbled upon the following link, where the username "jeneesh" showed an example using REGEXP_REPLACE expression to retrieve the same information -- which seemed better one line solution in my opinion:

https://forums.oracle.com/thread/2543764?start=0&tstart=0

So, I just wanted to share the query in case it helps in future.

WITH qry

AS

(

   SELECT NULL field_name FROM dual

      UNION ALL

   SELECT 'Aopu' FROM dual

      UNION ALL

   SELECT 'Abul Mohsin' FROM dual

      UNION ALL

   SELECT 'Abul M Mohsin' FROM dual

      UNION ALL

   SELECT 'Abul M. Mohsin' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines, Ltd' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines, Ltd.' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines Ltd' FROM dual

)

SELECT field_name,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\1')  exp1,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\2')  exp2,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\3')  exp3

  FROM qry;