Reset Application Password from Database

declare
    v boolean;
begin
    v := fnd_user_pkg.changepassword('CHI59342','welcome12345');
    commit;
end;

OU-SOB-INVORG Details

  SELECT hou.name operating_unit_name,
         hou.organization_id operating_unit_id,
         hou.set_of_books_id sob_id,
         gsob.name sob_name,
         ood.organization_name inv_org_name,
         ood.organization_code inv_org_code,
         ood.organization_id inv_org_id,
         ood.chart_of_accounts_id
    FROM hr_operating_units hou,
         apps.org_organization_definitions ood,
         apps.gl_sets_of_books gsob
   WHERE     1 = 1
         AND hou.organization_id = ood.operating_unit
         AND hou.set_of_books_id = gsob.set_of_books_id
ORDER BY hou.organization_id ASC

Operating Units and Inventory Orgs

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
     
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Concurrent Program/Set attached to Request Groups

 SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  apps.fnd_request_groups rg,
  apps.fnd_request_group_units rgu,
  apps.fnd_concurrent_programs cp,
  apps.fnd_concurrent_programs_tl cpt
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name = 'XX CM Pack Slip - US'

Menu attached to Responsibilities

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = ‘20538’
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

Form Function attached to Responsibilities

List of Responsibilities associated with Form Functions

SELECT DISTINCT responsibility_id, responsibility_name
    FROM apps.fnd_responsibility_vl a
   WHERE     a.end_date IS NULL
         AND a.menu_id IN
                (    SELECT menu_id
                       FROM apps.fnd_menu_entries_vl
                 START WITH menu_id IN
                               (SELECT menu_id
                                  FROM apps.fnd_menu_entries_vl
                                 WHERE function_id IN
                                          (SELECT function_id
                                             FROM applsys.fnd_form_functions a
                                            WHERE function_name =
                                                     :pc_function_name))
                 CONNECT BY PRIOR menu_id = sub_menu_id)
         AND a.responsibility_id NOT IN
                (SELECT responsibility_id
                   FROM apps.fnd_responsibility_vl
                  WHERE responsibility_id IN
                           (SELECT responsibility_id
                              FROM applsys.fnd_resp_functions resp
                             WHERE action_id IN
                                      (SELECT function_id
                                         FROM applsys.fnd_form_functions a
                                        WHERE function_name = :pc_function_name)))
         AND a.responsibility_id NOT IN
                (SELECT responsibility_id
                   FROM apps.fnd_responsibility_vl
                  WHERE responsibility_id IN
                           (SELECT responsibility_id
                              FROM applsys.fnd_resp_functions resp
                             WHERE action_id IN
                                      (    SELECT menu_id
                                             FROM apps.fnd_menu_entries_vl
                                       START WITH menu_id IN
                                                     (SELECT menu_id
                                                        FROM apps.fnd_menu_entries_vl
                                                       WHERE function_id IN
                                                                (SELECT function_id
                                                                   FROM applsys.fnd_form_functions a
                                                                  WHERE function_name =
                                                                           :pc_function_name))
                                       CONNECT BY PRIOR menu_id = sub_menu_id)))

ORDER BY responsibility_id

List of DFF

 

SELECT fa.application_short_name "Application" , fdft.title "Flex Field Title" , fdf.descriptive_flexfield_name "Flex Field Name" , fdf.application_table_name "Table" , fdf.freeze_flex_definition_flag "Freeze" , fdf.protected_flag "Protected" , fdf.context_column_name "Context Column" , fdft.form_context_prompt "Form Context Prompt" , fdf.default_context_field_name "Default Context Field" , fdf.context_required_flag "Required" , fdf.context_user_override_flag "Display Flag" , ffvs.flex_value_set_name "Conext Value Set" FROM applsys.fnd_descriptive_flexs fdf , applsys.fnd_descriptive_flexs_tl fdft , applsys.fnd_application fa , applsys.fnd_flex_value_sets ffvs WHERE fdf.application_id = fdft.application_id AND fdf.descriptive_flexfield_name = fdft.descriptive_flexfield_name AND fa.application_id = fdf.application_id AND fa.application_short_name in ('AR', 'SQLAP', 'PO', 'SQLGL', 'PA') AND fdf.context_override_value_set_id = ffvs.flex_value_set_id (+) AND fdft.title not like '$SRS$%' ORDER BY 1,2

Note- The flex fields that starts with "$SRS$" are used by concurrent programs

Scheduled Concurrent Programs

SELECT r.request_id,

            p.user_concurrent_program_name

         || CASE

               WHEN p.user_concurrent_program_name = 'Report Set'

               THEN

                  (SELECT ' - ' || s.user_request_set_name

                     FROM apps.fnd_request_sets_tl s

                    WHERE     s.application_id = r.argument1

                          AND s.request_set_id = r.argument2

                          AND language = 'US')

               WHEN p.user_concurrent_program_name = 'Check Periodic Alert'

               THEN

                  (SELECT ' - ' || a.alert_name

                     FROM apps.alr_alerts a

                    WHERE     a.application_id = r.argument1

                          AND a.alert_id = r.argument2

                          AND language = 'US')

            END

            concurrent_program_name,

         DECODE (c.class_type,

                 'P', 'Periodic',

                 'S', 'On Specific Days',

                 'X', 'Advanced',

                 c.class_type)

            schedule_type,

         CASE

            WHEN c.class_type = 'P'

            THEN

                  'Repeat every '

               || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)

               || DECODE (SUBSTR (c.class_info,

                                    INSTR (c.class_info,

                                           ':',

                                           1,

                                           1)

                                  + 1,

                                  1),

                          'N', ' minutes',

                          'M', ' months',

                          'H', ' hours',

                          'D', ' days')

               || DECODE (SUBSTR (c.class_info,

                                    INSTR (c.class_info,

                                           ':',

                                           1,

                                           2)

                                  + 1,

                                  1),

                          'S', ' from the start of the prior run',

                          'C', ' from the completion of the prior run')

            WHEN c.class_type = 'S'

            THEN

                  NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL)

               || DECODE (SUBSTR (c.class_info, 32, 1),

                          '1', 'Last day of month ')

               || DECODE (

                     SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),

                     '1',    'Days of week: '

                          || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')

                          || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')

                          || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')

                          || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')

                          || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')

                          || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')

                          || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa '))

         END

            schedule,

         r.requested_start_date next_run,

         CASE

            WHEN     p.user_concurrent_program_name != 'Report Set'

                 AND p.user_concurrent_program_name != 'Check Periodic Alert'

            THEN

               r.argument_text

         END

            argument_text,

         r.hold_flag on_hold,

         c.date1 start_date,

         c.date2 end_date,

         c.class_info,

         user_name

    FROM apps.fnd_concurrent_requests r,

         applsys.fnd_conc_release_classes c,

         apps.fnd_concurrent_programs_tl p,

         apps.fnd_user usr,

         (    SELECT release_class_id,

                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates,

                     a

                FROM (SELECT release_class_id,

                             RANK ()

                                OVER (PARTITION BY release_class_id ORDER BY s)

                                a,

                             s

                        FROM (SELECT c.class_info,

                                     l,

                                     c.release_class_id,

                                     DECODE (SUBSTR (c.class_info, l, 1),

                                             '1', TO_CHAR (l))

                                        s

                                FROM (    SELECT LEVEL l

                                            FROM DUAL

                                      CONNECT BY LEVEL <= 31),

                                     apps.fnd_conc_release_classes c

                               WHERE c.class_type = 'S')

                       WHERE s IS NOT NULL)

          CONNECT BY PRIOR (a || release_class_id) =

                        (a - 1) || release_class_id

            GROUP BY release_class_id, a) dates

   WHERE     r.phase_code = 'P'

         AND c.application_id = r.release_class_app_id

         AND c.release_class_id = r.release_class_id

         AND NVL (c.date2, SYSDATE + 1) > SYSDATE

         AND c.class_type IS NOT NULL

         AND p.concurrent_program_id = r.concurrent_program_id

         AND p.application_id = r.program_application_id

         AND p.language = 'US'

         AND dates.release_class_id(+) = r.release_class_id

         AND usr.user_id = requested_by

        -- AND user_name = 'XXSDM'

         AND P.user_concurrent_program_name = 'Workflow Background Process'

ORDER BY user_concurrent_program_name --requested_by, on_hold, next_run;

User Creation Thru API (FND_USER_PKG)

Below is the code to create multiple users and adding responsibilities to them through API
Other way around, you can load users in table and fetch cursor table data in Loop to create users.

declare
v_user_name := 'euser' ;
v_session_id := sid;
/* Pass here sid from this query- SELECT username, sid, serial#, status FROM v$session WHERE username = 'APPS'; */
BEGIN
for i in 1..1000 loop
fnd_user_pkg.createuser
(x_user_name => v_user_name||i
,x_owner => ''
,x_unencrypted_password => 'welcome1'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'EBS user creation'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_email_address => 'user@myemail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');

fnd_user_pkg.addresp
(username => v_user_name||i
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
end loop;
END;
/

Profile Option Values

Profile Option Details

SELECT
po.profile_option_name as name
,po.user_profile_option_name
,decode(to_char(pov.level_id),'10001','SITE','10002','APP','10003','RESP','10005','SERVER','10006','ORG','10004','USER', '???') as "LEVEL"
, decode(to_char(pov.level_id),'10001','','10002', app.application_short_name,'10003', rsp.responsibility_key,'10005', svr.node_name,'10006', org.name,'10004', usr.user_name,'???') as context
, pov.profile_option_value as value
FROM
fnd_profile_options_vl po
, fnd_profile_option_values pov
, fnd_user usr
, fnd_application app
, fnd_responsibility rsp
, fnd_nodes svr
, hr_operating_units org
WHERE 1=1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id = pov.level_value
AND rsp.application_id = pov.level_value_application_id
AND rsp.responsibility_id = pov.level_value
AND app.application_id = pov.level_value
AND svr.node_id = pov.level_value
AND org.organization_id = pov.level_value
ORDER BY 1, pov.level_id, 5

Note- Initialize your SQL session before running above query


Profile option with Modification Date and User

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

Concurrent Request Details

1) FND_CONCURRENT_REQUESTS stores information about individual concurrent requests.


2) ARGUMENT1 through ARGUMENT25 contain any arguments the application passes to the concurrent program. If the concurrent program needs more than 25 arguments to run, the first 25 arguments are stored in this table, ARGUMENT26 throughARGUMENT100 are stored in table FND_CONC_REQUEST_ARGUMENTS.


3) REQ_INFORMATION is used with report sets to remember the status of the request between runs.


4) When the request is set to use automatic resubmission, RESUBMITTED is a flag to indicate whether the request has been resubmitted or not.


5) IS_SUB_REQUEST is a flag that identifies a child request and HAS_SUB_REQUEST is a flag that identifies a parent request.


STATUS_CODE Column Values:
A- Waiting
B- Resuming
C- Normal
D- Cancelled
E- Error
F- Scheduled
G- Warning
H- On Hold
I- Normal
M- No Manager
Q- Standby
R- Normal
S- Suspended
T- Terminating
U- Disabled
W- Paused
X- Terminated
Z- Waiting


PHASE_CODE Column Values:
C- Completed
I- Inactive
P- Pending
R- Running

Detailed Query for Input Parameter Request ID

SELECT DISTINCT fcr.request_id, fcr.actual_start_date,
fcr.actual_completion_date,
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600) HOURS,
floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60) MINUTES,
round((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) SECS,
DECODE (fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) phase_code,
DECODE (fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
) status_code,
fcr.outfile_name, fcr.number_of_arguments, fcr.argument_text,
frt.responsibility_name, fav.application_name, fav.application_short_name appl_short_name, fu.user_name,
fu.description user_description, fu.start_date user_start_date,
fcp.user_concurrent_program_name,
fcp.concurrent_program_name short_name, fe.executable_name,
DECODE (fe.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
) execution_method,
fe.execution_file_name
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_application_vl fav,
fnd_responsibility_tl frt,
fnd_concurrent_programs_vl fcp,
fnd_executables fe
WHERE fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.responsibility_application_id = fav.application_id
AND fcp.executable_id = fe.executable_id
AND fcr.request_id = :request_id
-- AND fu.user_name= ''