API To Upload Value Set Values

DECLARE

   v_enabled_flag        VARCHAR2 (2)                  := 'Y';

   v_summary_flag        VARCHAR2 (2)                  := 'Y';

   v_start_date_active   DATE                          := SYSDATE;

   v_error_msg           VARCHAR2 (1000)               := NULL;

   v_who_type            fnd_flex_loader_apis.who_type;

How to Register / Delete procedure in USER Hook

How TO REGISTER/DELETE PROCEDURE IN USER Hook

1)     Create API Hook Call (Register Procedure in User Hook)

PACKAGE Name: hr_api_hook_call_api.create_api_hook_call

DECLARE

   l_api_hook_call_id        NUMBER;

   l_object_version_number   NUMBER;

Concurrent Managers that are currently running requests

SELECT Concurrent_Queue_Name Manager,

       Request_Id Request,

       User_name,

       Concurrent_Program_Name Program,

       Run_Alone_Flag,

       TO_CHAR (Actual_Start_Date, 'DD-MON-YY HH24:MI') Started

  FROM Fnd_Concurrent_Queues Fcq,

       Fnd_Concurrent_Requests Fcr,

       Fnd_Concurrent_Programs Fcp,

       Fnd_User Fu,

       Fnd_Concurrent_Processes Fpro

 WHERE Phase_Code = 'R' AND Fcr.Controlling_Manager = Concurrent_Process_Id

       AND (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id

            AND Fcq.Application_Id = Fpro.Queue_Application_Id)

       AND (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id

            AND Fcr.Program_Application_Id = Fcp.Application_Id)

       AND Fcr.Requested_By = User_Id

Print the Log file names of the managers that can run a given request

SELECT    'The '

       || Concurrent_Queue_Name

       || ' concurrent manager ran your request from',

          TO_CHAR (Actual_Start_date, '     MON-DD-YY HH:MI:SS AM')

       || ' - to - '

       || TO_CHAR (Actual_COMPLETION_date, 'MON-DD-YY HH:MI:SS AM'),

          'The '

       || Concurrent_Queue_Name

       || ' concurrent manager log file is '

       || P.Logfile_Name,

       'Request log file is ' || R.Logfile_Name

  FROM Fnd_Concurrent_Queues Q,

       Fnd_Concurrent_requests R,

       Fnd_Concurrent_Processes P

 WHERE (P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID

        AND Queue_Application_ID = Q.Application_ID)

       AND R.Controlling_Manager = P.Concurrent_Process_ID

       AND R.Phase_Code = 'C'

       AND Request_ID = &Request_ID

Show Database Sessions related to an Application Module

SELECT s.audsid AUDSID,

       i.instance_name "Instance",

       s.machine "Machine",

       s.program "Program",

       s.module "Module",

       s.username "Username",

       s.osuser "User",

       s.process "Process",

       s.status "Status",

       s.logon_time "Logon Time"

  FROM gv$session s, gv$instance i

 WHERE     s.audsid > 0

       AND s.inst_id = i.inst_id

       AND s.module LIKE '%' || &1 || '%'

       AND s.module NOT LIKE '%:R'

       AND s.process LIKE '%' || &2 || '%';

Important queries on snapshots

-- Show all the Available Snapshots

  SELECT s.snap_id "Snap ID",

         di.instance_name "Instance Name",

         di.host_name "Host Name",

         di.parallel "RAC Instance",

         TO_CHAR (s.startup_time, ' dd Mon "at" HH24

Difference between values recorded in two oracle snapshots

REM

REM    NAME

REM      afamsprep.sql

REM

REM    DESCRIPTION

REM      SQL*Plus command file to report on differences between

REM      values recorded in two snapshots.

REM

Useful database session tracking scripts for DBA

-- Show Database Sessions for a certain node

SELECT s.audsid AUDSID,

       i.instance_name "Instance",

Get the Active Database Sessions

SELECT s.audsid AUDSID,

       i.instance_name "Instance",

       s.machine "Machine",

       s.program "Program",

       s.module "Module",

       s.osuser "User",

       s.process "Process",

       s.status "Status",

       s.logon_time "Logon Time"

  FROM gv$session s, gv$instance i

 WHERE s.audsid > 0 AND s.inst_id = i.inst_id;

Get the DB Lock Information

SELECT s.audsid AUDSID,

       l.os_user_name "OS Username",

       l.oracle_username "Username",

       l.process "Process",

       p.spid "SPID",

       o.owner "Owner",

       o.object_name "Object",

       DECODE (l.locked_mode,

               1, 'No Lock',

               2, 'Row Share',

               3, 'Row Exclusive',

               4, 'Share',

               5, 'Share Row Excl',

               6, 'Exclusive',

               NULL)

          "Lock Mode",

       SUBSTR (s.status, 1, 8) "Status"

  FROM v$locked_object l,

       all_objects o,

       v$session s,

       v$process p

 WHERE     l.object_id = o.object_id

       AND l.session_id = s.sid

       AND s.paddr = p.addr

       AND s.status <> 'KILLED';

Average Runtime of Concurrent Programs

  SELECT fcp.description "Description",

         concurrent_program_name "Program",

         TRUNC (

            AVG (

               (TO_NUMBER (TO_CHAR (actual_completion_date, 'SSSSS'))

                - TO_NUMBER (TO_CHAR (actual_start_date, 'SSSSS')))

               / 60),

            2)

            "Time in Min."

    FROM fnd_concurrent_programs_vl fcp, fnd_concurrent_requests fcr

   WHERE fcp.application_id = fcr.program_application_id

         AND fcp.concurrent_program_id = fcr.concurrent_program_id

         AND TO_CHAR (actual_completion_date, 'DD-MON-YY') =

                TO_CHAR (actual_start_date, 'DD-MON-YY')

GROUP BY concurrent_program_name, fcp.description

ORDER BY fcp.description;