Find Workflow Notification Mailer

SQL> SELECT email_address,name, nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference)FROM wf_rolesWHERE name like upper('%xxx%');

SQL> select NOTIFICATION_ID,STATUS,MAIL_STATUS from WF_NOTIFICATIONS where TO_USER='UserName';

SQL> SELECT status,mail_status,MESSAGE_TYPE,MESSAGE_NAME,ITEM_KEY from WF_NOTIFICATIONS WHERE notification_id='2732013';

SQL> select mail_status,count(*) from WF_NOTIFICATIONS group by mail_status;


COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 = EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_DEFERRED
GROUP BY CORRID, STATE;


SET LINES 150
COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 =
EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_NOTIFICATION_OUT
GROUP BY CORRID, STATE;


COL COMPONENT_NAME FORMAT A60
COL COMPONENT_STATUS FORMAT A20
COL STARTUP_MODE FORMAT A20
SET LINES 120
SET PAGES 1000
SELECT COMPONENT_NAME, COMPONENT_STATUS, STARTUP_MODE FROM FND_SVC_COMPONENTS;


SET LINES 160
COL COMPONENT_NAME FORMAT A30
COL PARAMETER_NAME FORMAT A40
COL PARAMETER_VALUE FORMAT A40
SELECT A.COMPONENT_ID, A.COMPONENT_NAME, B.PARAMETER_ID, C.PARAMETER_NAME, B.PARAMETER_VALUE
FROM FND_SVC_COMPONENTS A, FND_SVC_COMP_PARAM_VALS B, FND_SVC_COMP_PARAMS_B C
WHERE A.COMPONENT_ID = B.COMPONENT_ID AND B.PARAMETER_ID = C.PARAMETER_ID
AND UPPER(A.COMPONENT_NAME) LIKE '%MAILER%'
ORDER BY A.COMPONENT_ID, A.COMPONENT_NAME, C.PARAMETER_NAME;

1 comment:

Unknown said...

Dear Ramesh,

good information sharing.
thanks for that.
I have one question.
we upgraded 10.2.0.3 to 10.2.0.4 (RAC). But in OEM showing version 10.2.0.3

Regards
Bhaskara