, , , , ,

Query to get Request Group Details Responsibility wise

SELECT   frg.request_group_name, fat1.application_name, frg.description,
         DECODE (frgu.request_unit_type,
                 ‘P’, ‘Program’,
                 ‘S’, ‘Set’,
                 ‘A’, ‘Application’,
                 frgu.request_unit_type
                ) TYPE,
         DECODE (frgu.request_unit_type,
                 ‘P’, fcpt.user_concurrent_program_name,
                 ‘S’, frst.user_request_set_name,
                 ‘A’, fat3.application_name,
                 frgu.request_unit_type
                ) NAME,
         fat2.application_name
    FROM fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_tl fcpt,
         fnd_application_tl fat1,
         fnd_application_tl fat2,
         fnd_application_tl fat3,
         fnd_request_sets_tl frst
   WHERE frg.request_group_id = frgu.request_group_id
     AND frgu.request_unit_id = fcpt.concurrent_program_id(+)
     AND fcpt.LANGUAGE(+) = USERENV (‘LANG’)
     AND frg.application_id = fat1.application_id
     AND fat1.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = fat2.application_id
     AND fat2.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = fcpt.application_id(+)
     AND frgu.request_unit_id = frst.request_set_id(+)
     AND frst.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.request_unit_id = fat3.application_id(+)
     AND fat3.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = frst.application_id(+)
     AND upper(fat1.application_name) = upper(:application_name)
ORDER BY request_group_name, frgu.request_unit_type, frgu.request_unit_id;

/by
, , , , ,

Reset Oracle Application Password

This is an anonymous PL/SQL block to reset your application password through back end.

This Code is tested in R12.1.3 Instance.

DECLARE
v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘ORACLEERP’,’learn123#’);
COMMIT;
END;

/by
, , , , ,

Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id “Concurrent Process ID”,
       pid “System Process ID”, os_process_id “Oracle Process ID”,
       q.concurrent_queue_name “Concurrent Manager Name”,
       p.process_status_code “Status of Concurrent Manager”,
       TO_CHAR(p.process_start_date,’MM-DD-YYYY HH:MI:SSAM’) “Concurrent Manager Started at”
  FROM fnd_concurrent_processes p,
       fnd_concurrent_queues q,
       fnd_v$process
 WHERE q.application_id = queue_application_id
   AND q.concurrent_queue_id = p.concurrent_queue_id
   AND spid = os_process_id
   AND process_status_code NOT IN (‘K’, ‘S’)
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name
/by
, , , , ,

Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’)

/by
, , , , , , ,

Set Profile Option Value using PL/SQL

On request here is how to set the profile option value using PL/SQL

Function FND_PROFILE.SAVE can be used to set the value of any profile option at any level i.e. Site, Application, Responsibility, User etc.

Below is a sample code of how to use this function

DECLARE
   a   BOOLEAN;
BEGIN
   a := fnd_profile.SAVE (‘CONC_REPORT_ACCESS_LEVEL’
                        , ‘R’
                        , ‘USER’
                        , ‘22746’
                        , NULL
                        , NULL
                         );
   IF a
   THEN
      DBMS_OUTPUT.put_line (‘Success’);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (‘Error’);
   END IF;
END;

/by