Currently Running SQL and SQL plan for a Concurrent Program

Many a times you keep looking for SQL ID and SQL plan for a concurrent Program which is stuck and taking long long time to complete. Here are the queries to debug those scenarios and get the exact SQL which is taking time to complete.

1.  Use below SQL to get SQLID Info :-

SELECT fcr.request_id "Req ID",
         parent_request_id "Parent",
         DECODE (fcpt.user_concurrent_program_name,
                 'Check Periodic Alert', 'Alert: ' || fcr.description,
                 fcpt.user_concurrent_program_name)
            "Name",
         r.responsibility_key "Responsibility",
         gvs.module "Module",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         fcr.priority "Priority",
         fcr.requested_start_date "Requested Start",
         fcr.actual_start_date "Actual Start",
         fcr.actual_completion_date "Actual End",
         DECODE (fcr.status_code,
                 'C', 'Complete',
                 'E', 'Error',
                 'R', 'Running',
                 'I', 'Pending',
                 'G', 'Warning',
                 'Q', 'Scheduled',
                 'D', 'Cancelled',
                 'W', 'Waiting',
                 'X', 'Terminated',
                 status_code)
            "Status",
         fcr.phase_code,
         fcr.status_code,
         DECODE (fcr.hold_flag, 'Y', 'Hold', 'Normal') "Hold?",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60,
            2)
            "Time(m)",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60
            * 60,
            2)
            "Time(s)",
         argument_text "Arguments",
         ROUND (
              (  DECODE (fcr.actual_start_date,
                         NULL, SYSDATE,
                         fcr.actual_start_date)
               - requested_start_date)
            * 24
            * 60
            * 60,
            1)
            "Start Delay (s)",
         r.responsibility_key "Responsibility",
         fu.user_name "User",
         gvs.inst_id "Inst",
         gvs.sid "SID",
         gvs.serial# "Serial#",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         gvs.status "S.Status",
         gvs.service_name "Service",
         gvs.sql_child_number "SQL Child",
         gvs.last_call_et "Call time",
         connstr1 "Conn Str",
         gvs.module "Module",
         argument_text "Arguments",
         fcr.priority_request_id "Priority Req ID",
         fcr.logfile_node_name,
         fcr.oracle_session_id,
         fcr.oracle_process_id spid,
         fcr.concurrent_program_id,
         fcr.status_code,
         fcp.os_process_id applmgr_pid,
         fu.user_id,
         fcr.concurrent_program_id "Prog ID",
         queue_id,
         fcqt.user_concurrent_queue_name queue_name,
         fcr.logfile_name,
         fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_user fu,
         apps.fnd_responsibility r,
         gv$session gvs,
         apps.fnd_concurrent_queues fcq,
         apps.fnd_concurrent_queues_tl fcqt,
         apps.fnd_concurrent_processes fcp
   WHERE     fcpt.application_id = fcr.program_application_id
         AND fcpt.concurrent_program_id = fcr.concurrent_program_id
         AND fcr.oracle_session_id = gvs.audsid(+)
         AND r.responsibility_id = fcr.responsibility_id
         AND fcr.requested_by = fu.user_id
         AND fcr.controlling_manager = fcp.concurrent_process_id(+)
         AND fcp.concurrent_queue_id = fcq.concurrent_queue_id(+)
         AND fcq.concurrent_queue_id = fcqt.concurrent_queue_id(+)
         AND fcr.request_id = :p_request_id
ORDER BY 10 DESC NULLS FIRST, 5 DESC;



2. Get the sql details from the above query for the running concurrent program and check the sql and its plan using the below SQL.

SELECT * FROM gv$sql where sql_id = :p_sql_id;

For SQL monitor report use the below query :-

SELECT DBMS_SQLTUNE.report_sql_monitor (sql_id         => 'dsf4z2dc63cfp',
                                        TYPE           => 'TEXT',
                                        report_level   => 'ALL')
          "SQL Monitor Report - Text"
  FROM DUAL;

get the sql details and you will see the sql which is causing the performance issue and analyse the same.

AUTHID CURRENT_USER and AUTHID DEFINER

Oracle Apps developers get confused with these 2 terms when to be used AUTHID CURRENT_USER and when to be used AUTHID DEFINER.

Ill  brief you about these 2 in the below paragraph.

AUTHID CURRENT_USER

This clause is used when you want to execute a piece of code with execute privilege of current user but not with the privilege of the user who defined the pl/sql code.

This is termed as “invoker rights”, the opposite of “definer rights”.

AUTHID DEFINER

AUTHID DEFINER is exactly opposite to AUTHID CURRENT_USER where a user can execute the pl/sql code with definer privileges. Using this clause is as same as granting public access to the pl/sql 

Oracle Apps AR Transaction is not Generated After Autoinvoice Completes

There are many validations Auto-invoice does to create the transaction for the orders in ra_interface_lines_All. Below are the steps to check the validations which are failed if the transaction is not generated after you have run Auto-invoice and its completed.

select * from ra_interface_lines_all
where interface_line_attribute1 = :p_order_number.

get the interface_line_id from the above query and pass it to the below query.

select * from ra_interface_errors_all
where interface_line_id =  :p_interface_line_id

The above query will list down the errors where the transaction validations were failed.

Check the data and verify the setups listed in the error.

Autoinvoice Mater Program Does not spawn Import Program

There could be various reasons why Autoinvoice Master Program does not spawn Autoinvoice Import program, I have listed down all of them below.

1. Order number doesnt exist in ra_interface_lines_all.

2. Batch source name passed in the Autoinvoice Master Program Doesnt belong to the order you are expecting to be invoiced.

3. If the above points are ok then Check the request_id column in ra_interface_lines_All and if its not NULL then query the request_id in fnd_concurrent_Requests.

Query :-

Select * from fnd_concurrent_requests
where request_id = :p_request_id;

Check the stats of concurrent request. If its running then ok if its cancelled or terminated then you need to Nullify the request_id from ra_interface_lines_all and run the Autoinvoice master Program again.

For more about concurrent request status check this link:-
https://oracleapplicationstechfunc.blogspot.in/2017/03/status-code-and-phase-code-in.html


4. Check the parameters passed are correct in Autoinvoice Master Program.

Concurrent Program Parameters and Values sets attached to it

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"
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
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        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    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')
        ORDER BY fdfcuv.column_seq_num;

Check Default mail id setup for Oracle Workflows

Query to Check Default mail id setup for Workflows:-

select p.parameter_id,
p.parameter_name,
v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by p.parameter_name;

Change Apps User Password from Backend

DECLARE
   v_user_name      VARCHAR2 (30) := 'USER_NAME';
   v_new_password   VARCHAR2 (30) := 'oracle123';
   v_status         BOOLEAN;
BEGIN
   v_status :=
      fnd_user_pkg.ChangePassword (username      => v_user_name,
                                   newpassword   => v_new_password);

   IF v_status = TRUE
   THEN
      DBMS_OUTPUT.put_line (
         'The password reset successfully for the User:' || v_user_name);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (
            'Unable to reset password due to'
         || SQLCODE
         || ' '
         || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
   END IF;
END;

Query to find all the responsibilities Assigned to a user in Oracle Apps

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');