SysAdmin :SQL Query to display Current Statement of Active Session/Program

     SELECT s.sid,
       s.client_info,
       s.machine,
       s.program,
       s.type,
       s.logon_time,
       s.osuser,
       sq.sorts,
       sq.disk_reads,
       sq.buffer_gets,
       sq.rows_processed,
       sq.sqltype,
       sq.sql_text
  FROM gv$session s,
               gv$sql sq
 WHERE s.sql_hash_value = sq.hash_value
   AND s.inst_id = :p_inst_id           -- replace with ID from above
   AND s.sid = :p_sid                   -- replace with instID from above
   AND sq.inst_id = s.inst_id;

Read More

FND: Query to select user and responsibility details associated to an Application(FND) user

  SELECT u.user_name,

              u.start_date user_start_date,

              u.end_date user_end_date,

              u.email_address user_email_address,

              r.responsibility_name,

              g.start_date resp_assign_start_date,

              g.end_date resp_assign_end_date,

              g.user_id,

              r.responsibility_id

    FROM apps.fnd_user u,

             apps.fnd_user_resp_groups g,

             apps.fnd_responsibility_tl r

   WHERE     1 = 1

        AND r.responsibility_id = g.responsibility_id

        AND g.user_id = u.user_id

        AND u.user_name = NVL(:p_user_name, u.user_name)

        AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)

ORDER BY u.user_name, r.responsibility_name DESC;

Read More

Replicate Seed Data

The purpose of Replicate Seed Data Concurrent Program is to populate the Installed modules seeded values, so that new organizations can use the seeded data.

Whenever new Organizations are created (under the Multi-org Setup), running Replicate Seed data is a pre-requisite step before you can start working in the newly created organization.

The Replicate Seed Data Concurrent Program can be run for a particular Operating Unit or for all Operating Units (as it is not mandatory parameter) but you cannot run the program for a particular module.

The concept of the program is that whenever new organizations are created they should be should have seeded data from all relevant modules for normal integration among the modules. The seeded data is basic data needed to perform basic steps when setting up a new organization.

Running this program will not damage the system because it is providing a new organization with the basic information required to set up the organization.

This progromme we need to run in System Administrator responsibility.

Seed data replicaiton program works in two modes

• Single organization - Pariticular Operating Unit.
• All Organizations - This mode applies when the seed data replication program is executed without an operating unit.

Make sure no user is logged in while running this program.

Read More