User Preferences in Oracle E-Business Suite

How to get the values from user the Preferences

Current Session Language

SELECT FND_PROFILE.VALUE_SPECIFIC (NAME => 'ICX_LANGUAGE', USER_ID=>9999)    FROM DUAL;

Output :  AMERICAN

Territory

 SELECT FND_PROFILE.VALUE_SPECIFIC (NAME =>'ICX_TERRITORY', USER_ID=>9999)

 FROM DUAL;

Output : AMERICA

Date Format

SELECT FND_PROFILE.VALUE_SPECIFIC (NAME =>'ICX_DATE_FORMAT_MASK', USER_ID=>9999)

  FROM DUAL;

Output : DD-MON-RRRR

Number Format

SELECT FND_PROFILE.VALUE_SPECIFIC ('ICX_NUMERIC_CHARACTERS', 9999)

  FROM DUAL;

Output : .,

Validations On Concurrent Program Parameters

Validating the Parameters in Concurrent Programs can be approached by using the Special type LOV.

Scenario:

There are two parameters From Invoice Date and To Invoice Date. The To Invoice Date should not be greater than the 90 days from the From Invoice Date.

Solution:

1. Take the From Invoice Date Parameter with FND_STANDARD_DATE Value Set.
2. Create a Special Type Value Set "XX_DATE_SPECIAL" with Action Validate.

3. Add the Below code to the code-section

FND PLSQL "DECLARE
l_frm_dte DATE:=to_date(':$FLEX$.P_FROM_INV_DATE','YYYY/MM/DD HH24:MI:SS');
l_to_dte DATE:= :!VALUE;
BEGIN
IF l_to_dte > l_frm_dte + 90 THEN
FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN('MESSAGE','Please enter date range less than 90 days.');
FND_MESSAGE.RAISE_ERROR;
END IF;
END;"

4. Create a Parameter To Invoice Date and attach the Special Value set "XX_VALIDATE_DATE90"

AOL - Concurrent Program/User/Responsibilities/Form/Function Queries

Concurrent Programs

1. Query to list out 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_name = :user_name

       AND furg.user_id = fu.user_id

       AND furg.responsibility_id = fr.responsibility_id

       AND fr.language(+) = USERENV ('lang');

2. List of responsibilities to which the concurrent program is assigned

SELECT fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcp.concurrent_program_id,

       frt.responsibility_name,

       REQUEST_GROUP_NAME

  FROM fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_request_groups frg,

       fnd_request_group_units frgu,

       fnd_responsibility fr,

       fnd_responsibility_tl frt

 WHERE     CONCURRENT_PROGRAM_NAME LIKE :p_conc_program_name

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fr.responsibility_id = frt.responsibility_id

       AND frgu.request_group_id = frg.request_group_id

       AND frgu.request_group_id = fr.request_group_id

       AND fcpt.concurrent_program_id = frgu.request_unit_id

       AND fcpt.language(+) = USERENV ('lang')

       AND frt.language(+) = USERENV ('lang');

3. To find out concurrent program details including parameters, their valuesets, default values etc.

SELECT fcpl.user_concurrent_program_name,

       fcp.concurrent_program_name,

       fav.application_short_name,

       fav.application_name,

       fav.application_id,

       fdfcuv.end_user_column_name,

       fdfcuv.form_left_prompt prompt,

       fdfcuv.enabled_flag,

       fdfcuv.required_flag,

       fdfcuv.display_flag,

       fdfcuv.flex_value_set_id,

       ffvs.flex_value_set_name,

       flv.meaning default_type,

       fdfcuv.DEFAULT_VALUE,descriptive_flexfield_name

  FROM fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpl,

       fnd_descr_flex_col_usage_vl fdfcuv,

       fnd_flex_value_sets ffvs,

       fnd_lookup_values flv,

       fnd_application_vl fav

 WHERE     fcp.concurrent_program_id = fcpl.concurrent_program_id

       AND fcpl.user_concurrent_program_name = :conc_prg_name

       AND fcpl.language = USERENV ('lang')

       AND fav.application_id = fcp.application_id

       AND fdfcuv.descriptive_flexfield_name =

              '$SRS$.' || fcp.concurrent_program_name

       AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

       AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

       AND flv.lookup_code(+) = fdfcuv.default_type

       AND flv.language(+) = USERENV ('lang');

3A. How to Delete Errored Concurrent Requests of an User

DELETE FROM fnd_concurrent_requests

      WHERE     status_code = 'E'

            AND request_date LIKE SYSDATE

            AND requested_by = (SELECT user_id

                                  FROM fnd_user

                                 WHERE user_name = 'USERNAME')

Request Sets

4. To find out the request sets to which a concurrent program is assigned

SELECT DISTINCT user_request_set_name

  FROM fnd_request_sets_tl frst

 WHERE frst.request_set_id IN (SELECT request_set_id

                                 FROM fnd_request_set_programs frsp

                                WHERE frsp.concurrent_program_id =

                                         (SELECT concurrent_program_id

                                            FROM fnd_concurrent_programs_tl fcpt

                                           WHERE UPPER (fcpt.user_concurrent_program_name) = UPPER ('&conc_prog_name')));

5. To list out the concurrent programs assigned to a request set

SELECT user_concurrent_program_name

  FROM fnd_concurrent_programs_tl

 WHERE concurrent_program_id IN (SELECT concurrent_program_id

                                   FROM fnd_request_set_programs

                                  WHERE request_set_id =

                                           (SELECT request_set_id

                                              FROM fnd_request_sets_tl

                                             WHERE UPPER (user_request_set_name) = UPPER ('&request_set_name')));

6. To find locks in a particular instance

   

SELECT DECODE (request, 0, 'holder: ', 'waiter: ') || sid session,

                  inst_id,

                  id1,

                  id2,

                  lmode,

                  request,

                  TYPE

    FROM gv$lock

   WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE

                                                        FROM gv$lock

                                                      WHERE request > 0)

   ORDER BY id1, request;

7. To find the programs referring a certain package

SELECT fcr.request_id,

       fcpv.user_concurrent_program_name,

       fcpv.concurrent_program_name,

       fcpv.concurrent_program_id,

       fcr.status_code,

       fcr.phase_code

  FROM fnd_concurrent_programs_vl fcpv,

       fnd_executables fe,

       sys.dba_dependencies dd,

       fnd_concurrent_requests fcr

 WHERE     fcpv.executable_id = fe.executable_id

       AND fe.execution_method_code = 'I'

       AND SUBSTR (fe.execution_file_name,

                   1,

                     INSTR (fe.execution_file_name,

                            '.',

                            1,

                            1)

                   - 1) = UPPER (dd.name)

       AND dd.referenced_type IN ('VIEW',

                                  'TABLE',

                                  'TRIGGER',

                                  'PACKAGE')

       AND dd.referenced_name = UPPER ('&package_name')

       AND fcr.concurrent_program_id = fcpv.concurrent_program_id

       AND fcr.phase_code NOT IN ('C', 'P');

8. To find out SID, Serial# of a concurrent request

SELECT a.request_id,

       d.sid,

       d.serial#,

       c.SPID

  FROM apps.fnd_concurrent_requests a,

       apps.fnd_concurrent_processes b,

       v$process c,

       v$session d

 WHERE     a.controlling_manager = b.concurrent_process_id

       AND c.pid = b.oracle_process_id

       AND b.session_id = d.audsid

       AND a.request_id = &conc_req_id

       AND a.phase_code = 'R';

9. To find out trace file location of concurrent request

SELECT request_id,

       oracle_process_id traceid,

       req.enable_trace,

          dest.VALUE

       || '/'

       || LOWER (dbnm.VALUE)

       || '_ora_'

       || oracle_process_id

       || '.trc'

          trace_file,

       prog.user_concurrent_program_name,

          DECODE (phase_code, 'R', 'Running')

       || '-'

       || DECODE (status_code, 'R', 'Normal')

          Status,

       ses.sid,

       ses.serial#,

       ses.module

  FROM fnd_concurrent_requests req,

       v$session ses,

       v$process proc,

       v$parameter dest,

       v$parameter dbnm,

       fnd_concurrent_programs_vl prog,

       fnd_executables execname

 WHERE     req.request_id = &conc_req_id

       AND req.oracle_process_id = proc.spid(+)

       AND proc.addr = ses.paddr(+)

       AND dest.name = 'user_dump_dest'

       AND dbnm.name = 'db_name'

       AND req.concurrent_program_id = prog.concurrent_program_id

       AND req.program_application_id = prog.application_id

       AND prog.executable_application_id = execname.application_id

          AND prog.executable_id = execname.executable_id;

10. How to find assigned user for a given responsibility

  SELECT DISTINCT fu.user_name,

                fu.email_address,

                papf.full_name,

                papf.employee_number,

                fr.responsibility_key

  FROM fnd_logins fl,

       fnd_login_responsibilities flr,

       fnd_responsibility fr,

       fnd_responsibility_tl frt,

       fnd_user fu,

       per_all_people_f papf

 WHERE     flr.responsibility_id = fr.responsibility_id

       AND flr.login_id = fl.login_id

       AND fu.user_id = fl.user_id

       AND fr.responsibility_id = frt.responsibility_id

       AND papf.person_id = fu.employee_id

       AND frt.responsibility_name LIKE 'XX%AP%SUPER%' 

11. How to find the responsibilities for a given request set

SELECT DISTINCT frg.request_group_id,

                frg.request_group_name,

                frgu.REQUEST_UNIT_ID,

                fcp.REQUEST_SET_NAME,

                fcp.REQUEST_SET_ID,

                frvl.RESPONSIBILITY_NAME

  FROM FND_REQUEST_GROUPS frg,

       FND_REQUEST_GROUP_UNITS frgu,

       FND_REQUEST_SETS fcp,

       FND_RESPONSIBILITY_VL frvl

 WHERE     frg.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID

       AND frg.REQUEST_GROUP_ID = frvl.REQUEST_GROUP_ID

       AND frgu.request_unit_type = 'S'             --P for Program, S for Set

       AND fcp.REQUEST_SET_ID = frgu.REQUEST_unit_ID

       AND fcp.REQUEST_SET_NAME LIKE 'XX%'

12. Query to fetch the forms and functions along with the prompt name

SELECT DISTINCT Prompt,

                function_name,

                user_function_name,

                form_name,

                user_form_name

  FROM FND_FORM_FUNCTIONS_VL ffv, fnd_form_vl fv, fnd_menu_entries_vl fm

 WHERE     fv.form_id = ffv.form_id

       AND fm.function_id = ffv.function_id

       AND prompt IS NOT NULL

       AND ffv.application_id = 20003

13. Query to Search the form Names and Menu Names for a Particular Form/Function/Menu

SELECT DISTINCT Prompt,

                Menu_name,

                function_name,

                user_function_name,

                form_name,

                user_form_name

  FROM FND_FORM_FUNCTIONS_VL ffv,

       fnd_form_vl fv,

       fnd_menu_entries_vl fm,

       fnd_menus fmn,

       fnd_application fa

 WHERE     fv.form_id = ffv.form_id

       AND fm.function_id = ffv.function_id

       AND fmn.menu_id = fm.menu_id

       AND prompt IS NOT NULL

       AND form_name = NVL ( :form_name, form_name)

       AND function_name = NVL ( :function_name, form_name)

       AND user_form_name = NVL ( :user_form_name, user_form_name)

       AND user_function_name = NVL ( :user_function_name, user_form_name)

       AND prompt = NVL ( :prompt, prompt)

       AND menu_name = NVL ( :menu_name, menu_name)

       AND ffv.application_id = fa.application_id                      --20003

       AND fa.application_short_name = 'XXARS' 

14. How to find the Responsibility and user to which function is assigned

  SELECT DISTINCT u.user_name,

                  rtl.responsibility_name,

                  ff.function_name,

                  ffl.user_function_name

    FROM fnd_compiled_menu_functions cmf,

         fnd_form_functions ff,

         fnd_form_functions_tl ffl,

         fnd_responsibility r,

         fnd_responsibility_tl rtl,

         fnd_user_resp_groups urg,

         fnd_user u

   WHERE     cmf.function_id = ff.function_id

         AND r.menu_id = cmf.menu_id

         AND urg.responsibility_id = r.responsibility_id

         AND rtl.responsibility_id = r.responsibility_id

         AND cmf.grant_flag = 'Y'

         AND r.application_id = urg.responsibility_application_id

         AND u.user_id = urg.user_id

         AND UPPER (ffl.user_function_name) =

                UPPER ('Batch Progression Sales Order')   --XXARS_BATCH_PRO_SO

         AND ff.function_id = ffl.function_id

ORDER BY u.user_name

15. How to find the Responsibilities for given function name       

SELECT d.responsibility_name

  FROM fnd_menu_entries a,

       fnd_menu_entries_vl b,

       fnd_form_functions c,

       fnd_responsibility_vl d

 WHERE     a.menu_id = b.menu_id

       AND a.function_id = b.function_id

       AND b.function_id = c.function_id

       AND a.menu_id = d.menu_id

       AND c.function_name = :function_name


16. To Get the Performance Timings of Concurrent Programs   

   SELECT fu.user_name,

         fcp.user_concurrent_program_name,

         req.request_id,

         req.request_date,

         req.phase_code,

         req.status_code,

         req.argument_text,

         DECODE (req.phase_code,

                 'R', ROUND ( (SYSDATE - req.actual_start_date) * 1440, 2))

            current_time,

         ROUND (

              (  NVL (req.actual_completion_date, req.LAST_UPDATE_DATE)

               - req.actual_start_date)

            * 1440,

            2)

            total_duration_min

    FROM apps.fnd_concurrent_requests req,

         apps.fnd_concurrent_programs_vl fcp,

         apps.fnd_user fu

   WHERE     fcp.concurrent_program_Id = req.concurrent_program_id

         AND req.request_date > SYSDATE - 365

         AND fu.user_id = req.requested_by

         AND req.concurrent_program_id = 91341

ORDER BY req.request_date DESC