Get active session info in EBS database

When an EBS job or session runs too long, everyone wants to know what it is doing in the database (11g & 12c) and how far away from its finish. Depending on the session stage, if the job is actively processing sql statements, two scripts here will provide similar Output:
... ... ...
Progress in DB (SID: 3258)
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:00:54
Elapsed in min .... : 0:11
Remaining ......... : 0:3
Complete_pct .... : 79.59
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_PAYMENT_SCHEDULES_ALL
Message ......... : Index Fast Full Scan:  AR.AR_PAYMENT_SCHEDULES_ALL: 58776 out of 73853 Blocks done
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:01:08
Elapsed in min .... : 69:35
Remaining ......... : 39:24
Complete_pct .... : 63.51
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_CASH_RECEIPTS_ALL
Message ......... : Table Scan:  AR.AR_CASH_RECEIPTS_ALL: 528906 out of 832791 Blocks done

• Script finds database session info by entering a concurrent Request ID:

When the concurrent Request is still in Running status and if column ORACLE_SESSION_ID in table fnd_concurrent_requests is populated, below query shall return accurate information. Seem to me when the concurrent Request is near to finish, sometimes that column could get erased to NULL.
X~~~~~~~~~~~~~~~~~~~~~ enter EBS Request ID ~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000

DECLARE
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w, fnd_concurrent_requests f
where
           s.paddr = p.addr
and     s.sid = w.sid
and     f.ORACLE_SESSION_ID = s.AUDSID
and     f.request_id='&Request_id';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' CM Request: database session details associated with Request id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;

  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/

• Script finds database session info by entering OS process ID on Apps node 

X~~~~~~~~~~~~~~~~~~~~~ enter OS process ID ~~~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000
DECLARE
v_sql_id  v$sql.sql_id%type;
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w
where
           s.paddr = p.addr
and     s.sid = w.sid
and     s.process = '&EBS_OS_pid';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' Database session details associated with Process id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        -- select distinct sql_id into v_sql_id from v$sqltext v
        --  where v.hash_value = x.sql_hash_value
        --   and v.address = x.sql_address;
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;
  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/