Enable Trace for Concurrent Programs in R12

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.

Goal :
To enable the Trace for Concurrent Programs
To Debug the issues in Concurrent Programs or its dependent objects.

Solution :
1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL
Select oracle_process_id from fnd_concurrent_requests where request_id= &Request_id;

8.And the Trace file path can be derived using the below SQL
Select * from v$parameter where name=’user_dump_dest’;

9. Get the trace file to your local machine.Understanding the raw trace is very complex so use TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.Analyse the Output file to know the answers for your problem.

Sql queries to find the log files by Request id

select req.oracle_process_id             tracename,
       req.request_id,
       req.requested_by,
       usr.user_name,
       prog.user_concurrent_program_name,
       req.request_date,
       req.phase_code,
       req.status_code,
       req.logfile_name,
       req.outfile_name,
       dest.value                        as user_dump_dest
  from apps.                           fnd_concurrent_requests req,
       gv$parameter                    dest,
       apps.fnd_concurrent_programs_vl prog,
       apps.fnd_user                   usr
 where req. concurrent_program_id = prog.concurrent_program_id
   and req.requested_by = usr.user_id –and
 request_id like '6013239'
   and dest.name = 'user_dump_dest'
   and request_id like &Request_id;
   

   

Sql query find the log files by username

  select req.oracle_process_id             tracename,
         req.request_id,
         req.requested_by,
         usr.user_name,
         prog.user_concurrent_program_name,
         req.request_date,
         req.phase_code,
         req.status_code,
         req.logfile_name,
         req.outfile_name,
         dest.value                        as user_dump_dest
    from apps.                           fnd_concurrent_requests req,
         gv$parameter                    dest,
         apps.fnd_concurrent_programs_vl prog,
         apps.fnd_user                   usr
   where req. concurrent_program_id = prog.concurrent_program_id
     and req.requested_by = usr.user_id –and
   request_id like '6013239'
     and dest.name = ‘user_dump_dest’
     and usr.user_name like ‘Ramesh%G%’
   order by request_date desc
             Select oracle_process_id
               from fnd_concurrent_requests
              where request_id = &Request_id;

select *
  from apps.        fnd_concurrent_requests req,
       gv$parameter dest,
       apps        .fnd_concurrent_programs_vl prog
 where req. concurrent_program_id = prog.concurrent_program_id
   and request_id like '601300%'
   and dest.name = 'user_dump_dest'

To Find Profile option values - R12 Query

SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

Delete lookup values - API

DECLARE

  CURSOR c1 IS

    SELECT lookup_type, lookup_code, security_group_id, view_application_id

      FROM fnd_lookup_values_vl

     WHERE lookup_type = 'mylookupname'

       AND lookup_code LIKE 'mylookupcode';

BEGIN

  FOR i IN c1

  LOOP

    BEGIN

      fnd_lookup_values_pkg.delete_row(x_lookup_type => i.lookup_type,

                        x_security_group_id   => i.security_group_id,

                        x_view_application_id => i.view_application_id,

                        x_lookup_code         => i.lookup_code);

      COMMIT;

  

      DBMS_OUTPUT.put_line(i.lookup_code || ' has been Purged !!!');

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.put_line('Inner Exception: ' || SQLERRM);

    END;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line('Main Exception ' || SQLERRM);

END;

Delete ValueSet Values - API

DECLARE

  l_err_msg VARCHAR2(500) := NULL;

  CURSOR c1 IS

    SELECT ffv.flex_value_id, ffv.flex_value

      FROM fnd_flex_value_sets ffvs,

           fnd_flex_values     ffv,

           fnd_flex_values_tl  ffvt

     WHERE flex_value_set_name = ':myvalueset' -- Value Set Name

       AND ffv.flex_value=:flex_val

       AND ffv.flex_value_set_id = ffvs.flex_value_set_id

       AND ffvt.flex_value_id = ffv.flex_value_id

       AND ffvs.flex_value_set_id = ffv.flex_value_set_id

       AND ffvt.language = 'US'

       AND ffv.enabled_flag = 'Y'

       AND ffv.summary_flag = 'N';

BEGIN

  FOR i IN c1

  LOOP

    fnd_flex_values_pkg.delete_row(i.flex_value_id);

    COMMIT;

    DBMS_OUTPUT.put_line(i.flex_value_id || ' Deleted  Successfully !!!!');

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    l_err_msg := SQLERRM;

    DBMS_OUTPUT.put_line('Exception: ' || l_err_msg);

 

END;

Below statement used to deleted DFFS

DECLARE

  l_application_id               NUMBER := 0;

  l_descriptive_flexfield_name   VARCHAR2(100) := 'FND_FLEX_VALUES';

  l_descriptive_flex_context_cod VARCHAR2(100) := 'context code';

BEGIN

  --FND_DESCRIPTIVE_FLEXS_PKG –this package is for DFF

  --FND_DESCR_FLEX_CONTEXTS_PKG –this package is for DFF Context

  --FND_DESCR_FLEX_COL_USAGE_PKG –this package is for DFF Column useage

  --When creating a new DFF Context, it will check the DFF Column usage if the context is already used.

  --so when deleting a DFF Context, both the context and column usage should be deleted.

  FOR c IN (SELECT application_column_name

              FROM fnd_descr_flex_column_usages

             WHERE application_id = l_application_id

               AND descriptive_flexfield_name = l_descriptive_flexfield_name

               AND descriptive_flex_context_code =

                   l_descriptive_flex_context_cod) 

  LOOP

fnd_descr_flex_col_usage_pkg.delete_row(x_application_id => l_application_id,

             x_descriptive_flexfield_name => l_descriptive_flexfield_name,

             x_descriptive_flex_context_cod=> l_descriptive_flex_context_cod,

             x_application_column_name      => c.application_column_name);

  END LOOP;

  begin

    fnd_descr_flex_contexts_pkg.delete_row(0,

                                           l_descriptive_flexfield_name,

                                           l_descriptive_flex_context_cod);

  end;

  commit;

end;