wf queries

select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
Where Status In ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
and begin_date < sysdate-30 -- List only emails older than 30 days ago
order by notification_id;


select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;

PARAMETER_ID PARAMETER_NAME VALUE

10018 ACCOUNT aminali.shamsuddin@toshiba-tjps.in
10026 DISCARD DISCARD
10029 FROM Workflow Mailer - TJTG Production
10033 INBOUND_SERVER 133.199.206.60
10034 INBOX INBOX
10037 NODENAME WFMAIL
10043 OUTBOUND_SERVER 133.199.206.60
10044 PROCESS PROCESS
10053 REPLYTO ebsalert@toshiba-tjps.in

select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

select component_status from apps.fnd_svc_components
where component_id =
(select component_id
from apps.fnd_svc_components
where component_name = 'Workflow Notification Mailer');



select * from WF_NOTIFICATION_IN;
select count(*) from WF_NOTIFICATION_OUT;
select * from WF_ERROR;
select * from WF_DEFERRED;
select * from WF_NOTIFICATIONS;

create table WF_NOTIFICATION_OUT_20jun as select * from WF_NOTIFICATION_OUT;

select count(*) from WF_NOTIFICATION_OUT_20jun;

select * from wf_resources where name='WF_ADMIN_ROLE';

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active'

/applcsf/TJTG/log/FNDCPGSC597257.txt
/applcsf/TJTG/log/FNDCPGSC597264.txt


select notification_id, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
where status in ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
order by notification_id;


select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile, nvl(v.PROFILE_OPTION_VALUE,'Replace with non-load balanced URL') Value, decode(v.level_id, 10001,'Site',
10002,'Application',
10003,'Responsibility',
10004,'User',
10005,'Server',
10006,'Organization') "LEVEL"
from fnd_profile_options t, fnd_profile_option_values v, fnd_profile_options_tl z
where (v.PROFILE_OPTION_ID (+) = t.PROFILE_OPTION_ID)
and (z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME)
and (t.PROFILE_OPTION_NAME in
('APPS_FRAMEWORK_AGENT','WF_MAIL_WEB_AGENT'));

/* Formatted on 2018/06/20 12:08 (Formatter Plus v4.8.8) */

SELECT SUBSTR (node_name, 1, 20) node_name, server_address,
SUBSTR (HOST, 1, 15) HOST, SUBSTR (domain, 1, 20) domain,
SUBSTR (support_cp, 1, 3) cp, SUBSTR (support_web, 1, 3) web,
SUBSTR (support_db, 1, 3) db, SUBSTR (virtual_ip, 1, 30) virtual_ip
FROM fnd_nodes;



select SC.COMPONENT_NAME, v.PARAMETER_DISPLAY_NAME, v.parameter_name, v.PARAMETER_VALUE, fnd.USER_NAME, fnd.DESCRIPTION
from FND_SVC_COMP_PARAM_VALS_V v, FND_SVC_COMPONENTS SC, fnd_user fnd
where v.COMPONENT_ID=sc.COMPONENT_ID
and v.PARAMETER_VALUE=fnd.USER_ID
and v.parameter_name = 'FRAMEWORK_USER'
order by sc.COMPONENT_ID, v.parameter_name;


select p.profile_option_name,v.profile_option_value,
decode(v.level_id,
10001,'SITE',
10002, (select 'App:'||a.application_short_name from fnd_application a
where a.application_id = v.level_value),
10003, (select 'Resp:'||f.RESPONSIBILITY_KEY from fnd_responsibility f
where f.responsibility_id = v.level_value),
10004, (select 'User:'||u.user_name from fnd_user u
where u.user_id = v.level_value),
10005, (select 'Server:'||n.node_name from fnd_nodes n
where n.node_id = v.level_value),
10006, (select 'Org:'||org.name from hr_operating_units org
where org.name = v.level_value),
'NOT SET') PROF_LEVEL
from
fnd_profile_options p,
fnd_profile_option_values v
where
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name in ('WF_MAIL_WEB_AGENT','APPS_FRAMEWORK_AGENT','ICX_FORMS_LAUNCHER');

select substr(node_name, 1, 20) node_name, server_address, substr(host, 1, 15) host,
substr(domain, 1, 20) domain, substr(support_cp, 1, 3) cp, substr(support_web, 1, 3) web,
substr(SUPPORT_DB, 1, 3) db, substr(VIRTUAL_IP, 1, 30) virtual_ip from fnd_nodes;