How to complete Host(Unix Shell Script) Concurrent Program with Warning

If we have a concurrent program as of type host(Unix Shell Script), then the program by default either completes Normal or in Error.
If we return from shell script using text exit 0, program completes in normal and if we use any value other value e.g. exit 1, exit 2, exit 10 etc then the program completes in error.
Below is the code that can be used if requirement is to complete it in warning.

sqlplus -s $p_usr_pwd <<-EOF
  set feedback off
  declare
    l_stat Boolean;
    l_session_id Number;
  begin
    fnd_global.initialize(session_id => l_session_id
                        , user_id => fnd_global.user_id
                        , resp_id => fnd_global.resp_id
                        , resp_appl_id => fnd_global.resp_appl_id
                        , security_group_id => null
                        , site_id => null
                        , login_id => null
                        , conc_login_id => null
                        , prog_appl_id => null
                        , conc_program_id => null
                        , conc_request_id => $p_req_id
                        , conc_priority_request => null);
    l_stat := fnd_concurrent.set_completion_status('WARNING','Completed in Warning. Review log for details.');
    commit;
  end;
/
  exit;
EOF

In the example above $p_usr_pwd stores database username/password, $p_req_id stores the request ID for concurrent request that needs to complete in warning.

Related Post:
Concurrent program parameter in Unix Shell Script
Calling SQLPLUS from unix shell script

Query to find locked objects in Oracle

Sometimes program takes a little longer time then expected, one of the reason to this could be that the table/object you are trying to manipulate is locked by other program and hence it is waiting for the resource to be released.
Below query can be handy to find the objects that are locked

SELECT   c.owner
       , c.object_name
       , c.object_type
       , b.SID
       , b.serial#
       , b.status
       , b.osuser
       , b.machine
       , b.program
       , b.module
       , b.action
FROM     v$locked_object a
       , v$session b
       , dba_objects c
WHERE    b.SID = a.session_id 
AND a.object_id = c.object_id
ORDER BY module

If you want to forcefully kill any session then it can be done using

alter system kill session 'sid,serial#'

e.g. 
altery system kill session '123,5325'

Restart or Bounce Apache Server

Following command can be used to bounce apache. An apache bounce is needed to reflect any changes in the self service pages.

To Stop

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop

To Start

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Profile Option to View Output of Request submitted by other User

Set the value of profile Concurrent:Report Access Level to Responsibility.
The output submitted by other users can now be viewed from the responsibility through which the request was submitted.
The other option available is User,which restricts the output only to the user who submitted the request.

Concurrent Program Name with Parameter, Value set

On request here is the query to list concurrent program name with its parameter, values set and default value/type

  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , par.column_seq_num      
      , par.end_user_column_name
      , par.form_left_prompt prompt
      , par.enabled_flag
      , par.required_flag
      , par.display_flag
      , par.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , par.DEFAULT_VALUE
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl par
      , 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    fcpl.LANGUAGE = 'US'
 AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = par.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = par.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG')
 ORDER BY par.column_seq_num