Basic SQLs – Currently Open EBS Forms

 Business Need:

Find who is logged into EBS Forms and have open (non-navigator) forms. Must be run and inform users before bouncing middle tier. Or else they will be kicked out with unsaved data lost.

 

Query:

SELECT USR.USER_NAME, S.SID, S.SERIAL# "SER#", L.PROCESS_SPID "OS PID", S.PROCESS,
   S.OSUSER, RSP.RESPONSIBILITY_NAME RESP_NAME, FRM.USER_FORM_NAME, TO_CHAR(NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)), 'MonDD hh24:mi') AS START_TIME,
   VA.NAME "COMMAND", S.STATUS, W.EVENT "WAITING FOR", S.MACHINE
FROM APPS.FND_RESPONSIBILITY_VL RSP, APPS.FND_FORM_VL FRM, APPS.FND_USER USR, APPS.FND_LOGINS L,
   APPS.FND_LOGIN_RESPONSIBILITIES R, APPS.FND_LOGIN_RESP_FORMS F, SYS.V_$SESSION S , SYS.V_$SESSION_WAIT W, SYS.AUDIT_ACTIONS VA
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
-- AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND F.AUDSID = S.AUDSID
AND S.SID = W.SID
AND VA.ACTION = S.COMMAND
--and S.SID = '1234'
ORDER BY USR.USER_NAME, START_TIME

 
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Forms, Oracle Applications, Query, SQL, SQLPLUS, Logged Users