REM HEADER
REM $Header: atg_fnd_usr_resp.sql v4.0 AXGONZAL $
REM
REM MODIFICATION LOG:
REM
REM
REM
REM 03-NOV-2008 GGGRANT Added Active Responsibilities for the user and queries
REM of all events on WF_DEFERRED and WF_JAVA_DEFERRED to
REM evaluate performance.
REM
REM 03-NOV-2008 GGGRANT Added who data and orig_system_id for the user.
REM 09-MAR-2009 GGGRANT Added Sefl Referencing Role
REM
REM
REM atg_supp_fnd_usr_resp.sql
REM
REM This script was created to collect the required information
REM to determine the cause of missing responsibilities associated
REM to one specific user.
REM
REM How to run it?
REM
REM sqlplus apps/<password>
REM
REM @atg_supp_fnd_usr_resp.sql
REM
REM Parameter:
REM
REM User_Name in UPPERCASE
REM
REM Output file
REM
REM wf_<UserName>_wfds.html
REM
REM
REM Created: Oct 2nd, 2008
REM Last Updated: Mar 9th, 2009
REM
REM
set arraysize 1
set heading off
set feedback off
set verify off
SET CONCAT ON
SET CONCAT .
set lines 120
set pages 9999
def username = "&&username"
def outputfile = "wf_&&username._wfds.html"
spool &&outputfile
prompt <FONT SIZE=3 FACE=ARIAL>
prompt User_name : &&username
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
prompt </FONT>
prompt <HTML>
prompt <HEAD>
prompt <TITLE>User - Responsibility Information</TITLE>
prompt <STYLE TYPE="text/css">
prompt <!-- TD {font-size: 8pt; font-family: arial; font-style: normal} -->
prompt </STYLE>
prompt </HEAD>
prompt <BODY>
prompt <P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=5 BGCOLOR==BLUE><FONT COLOR=WHITE FACE=ARIAL>
prompt <B>User/Responsibility Information
prompt <BR>Quick Links to Tables</B></TD></TR>
prompt <TR>
prompt <TD><A HREF="#wflr">WF_LOCAL_ROLES</A></TD>
prompt <TD><A HREF="#wflur">WF_LOCAL_USER_ROLES</A></TD>
prompt <TD><A HREF="#wflura">WF_USER_ROLE_ASSIGNMENTS</A></TD>
prompt <TD><A HREF="#fnd_user_resp">ACTIVE RESPONSIBILITIES</A></TD>
prompt <TD><A HREF="#wfd">WF_DEFERRED</A></TD>
prompt </TR>
prompt <TR>
prompt <TD><A HREF="#wfjd">WF_JAVA_DEFERRED</A></TD>
prompt <TD><A HREF="#fndc">FND_SVC_COMPONENTS</A></TD>
prompt <TD><A HREF="#pkg">PACKAGE_VERSIONS</A></TD>
prompt </TR>
prompt </TABLE><P><P>
prompt </FONT>
REM
REM ******* WF_LOCAL_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=9 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflr">WF_LOCAL_ROLES - User Information</A></B></TD></TR>
prompt <TR>
prompt <TD>NAME</TD>
prompt <TD>DISPLAY_NAME</TD>
prompt <TD>ORIG_SYSTEM</TD>
prompt <TD>ORIG_SYSTEM_ID</TD>
prompt <TD>STATUS</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION<BR>DATE</TD>
prompt <TD>USER_FLAG</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>PARENT_ORIG_SYSTEM</TD>
select
'<TR><TD>'||NAME||'</TD>'||chr(10)||
'<TD>'||DISPLAY_NAME||'</TD>'||chr(10)||
'<TD>'||ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||STATUS||'</TD>'||chr(10)||
'<TD>'||START_DATE||'</TD>'||chr(10)||
'<TD>'||EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||USER_FLAG||'</TD>'||chr(10)||
'<TD>'||LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||PARENT_ORIG_SYSTEM||'</TD></TR>'
from wf_local_roles
where name = upper('&&username');
prompt </TABLE><P><P>
REM
REM ******* WF_LOCAL_USER_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflur">WF_LOCAL_USER_ROLES - &&username User Self Referencing Role Information</A></B></TD></TR>
prompt <TR>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_<BR>ORIG_SYSTEM</TD>
prompt <TD>ROLE_<BR>ORIG_SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD><FONT FACE="Arial" SIZE=1>'||wfur.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_local_user_roles wfur
where user_name = upper('&&username')
and role_name = upper('&&username');
prompt </TABLE><P><P>
REM
REM ******* WF_LOCAL_USER_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflur">WF_LOCAL_USER_ROLES - &&username User and Roles Information</A></B></TD></TR>
prompt <TR>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_<BR>ORIG_SYSTEM</TD>
prompt <TD>ROLE_<BR>ORIG_SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD><FONT FACE="Arial" SIZE=1>'||wfur.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_local_user_roles wfur, fnd_responsibility_vl r
where user_name = upper('&&username')
and r.responsibility_id = wfur.role_orig_system_id
and wfur.role_name like '%|%'
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* WF_USER_ROLE_ASSIGNMENTS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflura">WF_USER_ROLE_ASSIGNMENTS - &&username User and Role Information</A></B></TD></TR>
prompt <TR>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>END_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD><FONT FACE="Arial" SIZE=1>'||wfura.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfura.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfura.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfura.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfura.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfura.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_user_role_assignments wfura, fnd_responsibility_vl r
where user_name = upper('&&username')
and r.responsibility_id = wfura.role_orig_system_id
and wfura.role_name like '%|%'
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* ACTIVE RESPONSIBILITIES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="fnd_user_resp">ACTIVE RESPONSIBILITIES - Active Responsibilites for &&username</A></B></TD></TR>
prompt <TR>
prompt <TD>USER_ID</TD>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>RESPONSIBILITY_ID</TD>
prompt <TD>RESPONSIBILITY_<BR>APPLICATION_ID</TD>
prompt <TD>SECURITY_<BR>GROUP_ID</TD>
prompt</TR>
select
'<TR><TD>'||fu.USER_ID||'</TD>'||chr(10)||
'<TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD>'||ur.RESPONSIBILITY_ID||'</TD>'||chr(10)||
'<TD>'||ur.RESPONSIBILITY_APPLICATION_ID||'</TD>'||chr(10)||
'<TD>'||ur.SECURITY_GROUP_ID||'</TD>'||chr(10)||
'</TR>'
from fnd_user_resp_groups ur, fnd_responsibility_vl r, fnd_user fu
where fu.user_id=ur.user_id
and fu.user_name= upper('&&username')
and ur.RESPONSIBILITY_ID=r.RESPONSIBILITY_ID
and ur.RESPONSIBILITY_APPLICATION_ID=r.APPLICATION_ID
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* WF_DEFERRED *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfd">WF_DEFERRED - &&username WFDS Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_deferred wfd
where wfd.user_data.event_key= upper('&&username')
group by wfd.corr_id, wfd.msg_state;
prompt </TABLE><P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfd">WF_DEFERRED - All Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_deferred wfd
group by wfd.corr_id, wfd.msg_state;
prompt </TABLE><P><P>
REM
REM ******* WF_JAVA_DEFERRED *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfjd">WF_JAVA_DEFERRED - &&username WFDS Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfjd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfjd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_java_deferred wfjd
where wfjd.user_data.GET_STRING_PROPERTY('USER_NAME') = upper('&&username')
group by corr_id, wfjd.msg_state;
prompt </TABLE><P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfjd">WF_JAVA_DEFERRED - All Events Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfjd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfjd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_java_deferred wfjd
group by corr_id, wfjd.msg_state;
prompt </TABLE><P><P>
REM
REM ******* WF_ACTIVE_SUBSCRIPTIONS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfas">WF_ACTIVE_SUBSCRIPTIONS - Event subscriptions</A></B></TD></TR>
prompt <TR>
prompt <TD>EVENT_NAME</TD>
prompt <TD>SUBSCRIPTION_RULE_FUNCTION</TD>
select
'<TR><TD>'||EVENT_NAME||'</TD>'||chr(10)||
'<TD>'||SUBSCRIPTION_RULE_FUNCTION||'</TD></TR>'
from wf_active_subscriptions_v
where event_name in ( 'oracle.apps.fnd.wf.ds.userRole.updated',
'oracle.apps.fnd.wf.ds.userRole.created',
'oracle.apps.fnd.wf.ds.role.created',
'oracle.apps.fnd.wf.ds.role.updated',
'oracle.apps.fnd.wf.ds.user.updated',
'oracle.apps.fnd.wf.ds.user.nameChanged' )
order by event_name;
prompt </TABLE><P><P>
REM
REM ******* FND_SVC_COMPONENTS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="fndc"> FND_SVC_COMPONENTS - Components Information</A></B></TD></TR>
prompt <TR>
prompt <TD>COMPONENT_ID</TD>
prompt <TD>COMPONENT_NAME</TD>
prompt <TD>COMPONENT_STATUS</TD>
select
'<TR><TD>'||COMPONENT_ID||'</TD>'||chr(10)||
'<TD>'||COMPONENT_NAME||'</TD>'||chr(10)||
'<TD>'||COMPONENT_STATUS||'</TD></TR>'
from fnd_svc_components
where component_type in ('WF_AGENT_LISTENER','WF_JAVA_AGENT_LISTENER')
order by COMPONENT_NAME;
prompt </TABLE><P><P>
REM
REM ******* PACKAGE_VERSIONS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="pkg"> Package Versions </A></B></TD></TR>
prompt <TD><B>PACKAGE_NAME</B></TD>
prompt <TD><B>TEXT</B></TD></TR>
select
'<TR><TD>'||NAME||'</TD>'||chr(10)||
'<TD>'||TEXT||'</TD>'||chr(10)||'</TD></TR>'
from dba_source
where name in ('WF_LOCAL_SYNCH','WF_ROLE_HIERARCHY')
and line = 2;
prompt </TABLE><P><P>
undef username
spool off
set heading on
set feedback on
set verify on
exit
;
REM $Header: atg_fnd_usr_resp.sql v4.0 AXGONZAL $
REM
REM MODIFICATION LOG:
REM
REM
REM
REM 03-NOV-2008 GGGRANT Added Active Responsibilities for the user and queries
REM of all events on WF_DEFERRED and WF_JAVA_DEFERRED to
REM evaluate performance.
REM
REM 03-NOV-2008 GGGRANT Added who data and orig_system_id for the user.
REM 09-MAR-2009 GGGRANT Added Sefl Referencing Role
REM
REM
REM atg_supp_fnd_usr_resp.sql
REM
REM This script was created to collect the required information
REM to determine the cause of missing responsibilities associated
REM to one specific user.
REM
REM How to run it?
REM
REM sqlplus apps/<password>
REM
REM @atg_supp_fnd_usr_resp.sql
REM
REM Parameter:
REM
REM User_Name in UPPERCASE
REM
REM Output file
REM
REM wf_<UserName>_wfds.html
REM
REM
REM Created: Oct 2nd, 2008
REM Last Updated: Mar 9th, 2009
REM
REM
set arraysize 1
set heading off
set feedback off
set verify off
SET CONCAT ON
SET CONCAT .
set lines 120
set pages 9999
def username = "&&username"
def outputfile = "wf_&&username._wfds.html"
spool &&outputfile
prompt <FONT SIZE=3 FACE=ARIAL>
prompt User_name : &&username
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
prompt </FONT>
prompt <HTML>
prompt <HEAD>
prompt <TITLE>User - Responsibility Information</TITLE>
prompt <STYLE TYPE="text/css">
prompt <!-- TD {font-size: 8pt; font-family: arial; font-style: normal} -->
prompt </STYLE>
prompt </HEAD>
prompt <BODY>
prompt <P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=5 BGCOLOR==BLUE><FONT COLOR=WHITE FACE=ARIAL>
prompt <B>User/Responsibility Information
prompt <BR>Quick Links to Tables</B></TD></TR>
prompt <TR>
prompt <TD><A HREF="#wflr">WF_LOCAL_ROLES</A></TD>
prompt <TD><A HREF="#wflur">WF_LOCAL_USER_ROLES</A></TD>
prompt <TD><A HREF="#wflura">WF_USER_ROLE_ASSIGNMENTS</A></TD>
prompt <TD><A HREF="#fnd_user_resp">ACTIVE RESPONSIBILITIES</A></TD>
prompt <TD><A HREF="#wfd">WF_DEFERRED</A></TD>
prompt </TR>
prompt <TR>
prompt <TD><A HREF="#wfjd">WF_JAVA_DEFERRED</A></TD>
prompt <TD><A HREF="#fndc">FND_SVC_COMPONENTS</A></TD>
prompt <TD><A HREF="#pkg">PACKAGE_VERSIONS</A></TD>
prompt </TR>
prompt </TABLE><P><P>
prompt </FONT>
REM
REM ******* WF_LOCAL_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=9 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflr">WF_LOCAL_ROLES - User Information</A></B></TD></TR>
prompt <TR>
prompt <TD>NAME</TD>
prompt <TD>DISPLAY_NAME</TD>
prompt <TD>ORIG_SYSTEM</TD>
prompt <TD>ORIG_SYSTEM_ID</TD>
prompt <TD>STATUS</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION<BR>DATE</TD>
prompt <TD>USER_FLAG</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>PARENT_ORIG_SYSTEM</TD>
select
'<TR><TD>'||NAME||'</TD>'||chr(10)||
'<TD>'||DISPLAY_NAME||'</TD>'||chr(10)||
'<TD>'||ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||STATUS||'</TD>'||chr(10)||
'<TD>'||START_DATE||'</TD>'||chr(10)||
'<TD>'||EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||USER_FLAG||'</TD>'||chr(10)||
'<TD>'||LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||PARENT_ORIG_SYSTEM||'</TD></TR>'
from wf_local_roles
where name = upper('&&username');
prompt </TABLE><P><P>
REM
REM ******* WF_LOCAL_USER_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflur">WF_LOCAL_USER_ROLES - &&username User Self Referencing Role Information</A></B></TD></TR>
prompt <TR>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_<BR>ORIG_SYSTEM</TD>
prompt <TD>ROLE_<BR>ORIG_SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD><FONT FACE="Arial" SIZE=1>'||wfur.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_local_user_roles wfur
where user_name = upper('&&username')
and role_name = upper('&&username');
prompt </TABLE><P><P>
REM
REM ******* WF_LOCAL_USER_ROLES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflur">WF_LOCAL_USER_ROLES - &&username User and Roles Information</A></B></TD></TR>
prompt <TR>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_<BR>ORIG_SYSTEM</TD>
prompt <TD>ROLE_<BR>ORIG_SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>EXPIRATION_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD><FONT FACE="Arial" SIZE=1>'||wfur.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfur.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfur.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EXPIRATION_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfur.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfur.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_local_user_roles wfur, fnd_responsibility_vl r
where user_name = upper('&&username')
and r.responsibility_id = wfur.role_orig_system_id
and wfur.role_name like '%|%'
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* WF_USER_ROLE_ASSIGNMENTS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wflura">WF_USER_ROLE_ASSIGNMENTS - &&username User and Role Information</A></B></TD></TR>
prompt <TR>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>ROLE_NAME</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM_ID</TD>
prompt <TD>USER_ORIG<BR>SYSTEM</TD>
prompt <TD>ROLE_ORIG<BR>SYSTEM</TD>
prompt <TD>START_DATE</TD>
prompt <TD>END_DATE</TD>
prompt <TD>USER<BR>START_DATE</TD>
prompt <TD>USER<BR>END_DATE</TD>
prompt <TD>ROLE<BR>START_DATE</TD>
prompt <TD>ROLE<BR>END_DATE</TD>
prompt <TD>LAST_UPDATED_BY</TD>
prompt <TD>LAST_UPDATE_DATE</TD>
prompt <TD>EFFECTIVE<BR>START_DATE</TD>
prompt <TD>EFFECTIVE<BR>END_DATE</TD></TR>
select
'<TR><TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD><FONT FACE="Arial" SIZE=1>'||wfura.ROLE_NAME||'</FONT></TD>'||chr(10)||
'<TD>'||wfura.ROLE_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfura.USER_ORIG_SYSTEM_ID||'</TD>'||chr(10)||
'<TD>'||wfura.USER_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_ORIG_SYSTEM||'</TD>'||chr(10)||
'<TD>'||wfura.START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.USER_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.USER_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.ROLE_END_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.LAST_UPDATED_BY||'</TD>'||chr(10)||
'<TD>'||wfura.LAST_UPDATE_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.EFFECTIVE_START_DATE||'</TD>'||chr(10)||
'<TD>'||wfura.EFFECTIVE_END_DATE||'</TD></TR>'
from wf_user_role_assignments wfura, fnd_responsibility_vl r
where user_name = upper('&&username')
and r.responsibility_id = wfura.role_orig_system_id
and wfura.role_name like '%|%'
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* ACTIVE RESPONSIBILITIES *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=13 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="fnd_user_resp">ACTIVE RESPONSIBILITIES - Active Responsibilites for &&username</A></B></TD></TR>
prompt <TR>
prompt <TD>USER_ID</TD>
prompt <TD>RESPONSIBILITY_NAME</TD>
prompt <TD>RESPONSIBILITY_ID</TD>
prompt <TD>RESPONSIBILITY_<BR>APPLICATION_ID</TD>
prompt <TD>SECURITY_<BR>GROUP_ID</TD>
prompt</TR>
select
'<TR><TD>'||fu.USER_ID||'</TD>'||chr(10)||
'<TD>'||r.RESPONSIBILITY_NAME||'</TD>'||chr(10)||
'<TD>'||ur.RESPONSIBILITY_ID||'</TD>'||chr(10)||
'<TD>'||ur.RESPONSIBILITY_APPLICATION_ID||'</TD>'||chr(10)||
'<TD>'||ur.SECURITY_GROUP_ID||'</TD>'||chr(10)||
'</TR>'
from fnd_user_resp_groups ur, fnd_responsibility_vl r, fnd_user fu
where fu.user_id=ur.user_id
and fu.user_name= upper('&&username')
and ur.RESPONSIBILITY_ID=r.RESPONSIBILITY_ID
and ur.RESPONSIBILITY_APPLICATION_ID=r.APPLICATION_ID
order by r.RESPONSIBILITY_NAME;
prompt </TABLE><P><P>
REM
REM ******* WF_DEFERRED *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfd">WF_DEFERRED - &&username WFDS Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_deferred wfd
where wfd.user_data.event_key= upper('&&username')
group by wfd.corr_id, wfd.msg_state;
prompt </TABLE><P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfd">WF_DEFERRED - All Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_deferred wfd
group by wfd.corr_id, wfd.msg_state;
prompt </TABLE><P><P>
REM
REM ******* WF_JAVA_DEFERRED *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfjd">WF_JAVA_DEFERRED - &&username WFDS Event Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfjd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfjd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_java_deferred wfjd
where wfjd.user_data.GET_STRING_PROPERTY('USER_NAME') = upper('&&username')
group by corr_id, wfjd.msg_state;
prompt </TABLE><P><P>
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfjd">WF_JAVA_DEFERRED - All Events Information</A></B></TD></TR>
prompt <TR>
prompt <TD>CORRID</TD>
prompt <TD>STATE</TD>
prompt <TD>COUNT(*)</TD>
select
'<TR><TD>'||wfjd.corr_id||'</TD>'||chr(10)||
'<TD>'||wfjd.msg_state||'</TD>'||chr(10)||
'<TD>'||count(*)||'</TD></TR>'
from applsys.aq$wf_java_deferred wfjd
group by corr_id, wfjd.msg_state;
prompt </TABLE><P><P>
REM
REM ******* WF_ACTIVE_SUBSCRIPTIONS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="wfas">WF_ACTIVE_SUBSCRIPTIONS - Event subscriptions</A></B></TD></TR>
prompt <TR>
prompt <TD>EVENT_NAME</TD>
prompt <TD>SUBSCRIPTION_RULE_FUNCTION</TD>
select
'<TR><TD>'||EVENT_NAME||'</TD>'||chr(10)||
'<TD>'||SUBSCRIPTION_RULE_FUNCTION||'</TD></TR>'
from wf_active_subscriptions_v
where event_name in ( 'oracle.apps.fnd.wf.ds.userRole.updated',
'oracle.apps.fnd.wf.ds.userRole.created',
'oracle.apps.fnd.wf.ds.role.created',
'oracle.apps.fnd.wf.ds.role.updated',
'oracle.apps.fnd.wf.ds.user.updated',
'oracle.apps.fnd.wf.ds.user.nameChanged' )
order by event_name;
prompt </TABLE><P><P>
REM
REM ******* FND_SVC_COMPONENTS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="fndc"> FND_SVC_COMPONENTS - Components Information</A></B></TD></TR>
prompt <TR>
prompt <TD>COMPONENT_ID</TD>
prompt <TD>COMPONENT_NAME</TD>
prompt <TD>COMPONENT_STATUS</TD>
select
'<TR><TD>'||COMPONENT_ID||'</TD>'||chr(10)||
'<TD>'||COMPONENT_NAME||'</TD>'||chr(10)||
'<TD>'||COMPONENT_STATUS||'</TD></TR>'
from fnd_svc_components
where component_type in ('WF_AGENT_LISTENER','WF_JAVA_AGENT_LISTENER')
order by COMPONENT_NAME;
prompt </TABLE><P><P>
REM
REM ******* PACKAGE_VERSIONS *******
REM
prompt <TABLE BORDER=1>
prompt <TR><TD COLSPAN=12 BGCOLOR=BLUE><font color=white face=arial>
prompt <B><A NAME="pkg"> Package Versions </A></B></TD></TR>
prompt <TD><B>PACKAGE_NAME</B></TD>
prompt <TD><B>TEXT</B></TD></TR>
select
'<TR><TD>'||NAME||'</TD>'||chr(10)||
'<TD>'||TEXT||'</TD>'||chr(10)||'</TD></TR>'
from dba_source
where name in ('WF_LOCAL_SYNCH','WF_ROLE_HIERARCHY')
and line = 2;
prompt </TABLE><P><P>
undef username
spool off
set heading on
set feedback on
set verify on
exit
;