Monday, May 02, 2011

Queries : Find Workflow Notification Mailer

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

SELECT NOTIFICATION_ID,STATUS,MAIL_STATUS from WF_NOTIFICATIONS where TO_USER='UserName';

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

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;


Regards,
Sukhwinder Singh

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

There was an error in this gadget