How to check send_email_flag for customer contact?

SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID,ROLE_ACCT.cust_account_id,party.party_type,party.party_name Contact_Name,
rel.relationship_code,rel.relationship_id, ACCT_ROLE.attribute10 send_email_flag,
party.party_id,rel_party.party_id,rel.subject_id,rel.object_id, rel.party_id,
SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME ,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME ,
PARTY.CUSTOMER_KEY CONTACT_KEY ,
REL_PARTY.EMAIL_ADDRESS ,
PARTY.PARTY_ID ,
ORG_CONT.ORG_CONTACT_ID ,
CONT_POINT.CONTACT_POINT_ID ,
ORG_CONT.CONTACT_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_PARTIES REL_PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT ,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and ROLE_ACCT.cust_account_id = &Enter_Account_id
and rel.object_id = &<PARTY_ID>
AND rel.subject_table_name ='HZ_PARTIES'
AND rel.object_table_name ='HZ_PARTIES'
and party.status ='A'
and rel_party.status ='A'
and rel.status ='A'
and party.party_name = '<CONTACT_NAME>'
and CONT_POINT.CREATED_BY_MODULE = 'USER REGISTRATION';

Delete pull sequence and Kanban cards --- usefull scripts

--
PROCEDURE Delete_Pull_Sequence
(x_return_status  Out NOCOPY Varchar2,
 p_kanban_plan_id     Number,
 pull_sequence_id   NUMBER)

IS

Cursor Get_Pull_Sequences IS
Select pull_sequence_id
From mtl_kanban_pull_sequences
Where kanban_plan_id = p_kanban_plan_id;

l_return_status      Varchar2(1) := FND_API.G_RET_STS_SUCCESS;

    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Begin
      If p_kanban_plan_id = INV_kanban_PVT.G_Current_Plan
        then
                For pull_sequences in get_pull_sequences
                loop
                        If Ok_To_Delete_Pull_Sequence(pull_sequences.pull_sequence_id)
                        then
                                INV_PullSequence_Pkg.delete_Row(l_return_status,pull_sequences.pull_sequence_id);
                        Else
                                Raise FND_API.G_EXC_ERROR;
                        end if;
                end loop;
        Else
                Delete from Mtl_kanban_pull_sequences
                Where kanban_plan_id = p_kanban_plan_id;
        end if;
        x_return_status := l_return_status;

Exception

    WHEN FND_API.G_EXC_ERROR THEN

       x_return_status := FND_API.G_RET_STS_ERROR;

    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN

       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

    WHEN OTHERS THEN

        IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (   G_PKG_NAME
            ,   'Validate_Pull_Sequence'
            );
        END IF;
       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

End Delete_Pull_Sequence;

Calling Procedure:

INV_PullSequence_PKG.Delete_Row(x_return_status,uas_upd_rec.pull_sequence_id);

WF_MAILER not sending emails to users - Troubleshooting

Check user preference Email Notification style. 

SELECT EMAIL_ADDRESS, NVL(apps.WF_PREF.GET_PREF(NAME, 'MAILTYPE'),NOTIFICATION_PREFERENCE)
FROM apps.WF_ROLES
WHERE name = upper('<ROLE>');

Notification not sent waiting to be mailed ?

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS

where status = ‘OPEN’ and mail_status = ‘MAIL’;

To debug the notification id ?

$FND_TOP/sql

run wfmlrdbg.sql

If Workflow mailer is down?

SELECT COMPONENT_NAME, COMPONENT_STATUS
FROM apps.FND_SVC_COMPONENTS
WHERE component_type = 'WF_MAILER';

Notification deffered?

SELECT * FROM APPS.WF_NOTIFICATIONS WHERE SUBJECT LIKE '%Parts%Online%Order%Entered%'
AND  MAIL_STATUS = 'SENT'
--AND TO_USER = 'ISTORE_DAN'
and recipient_role = 'ISTORE_DAN'
order by BEGIN_DATE desc;

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<notif id>;

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
WF.USER_DATA.ERROR_STACK ERROR_STACK, WF.USER_DATA.ERROR_MESSAGE ERROR_MSG
from apps.wf_error wf where wf.user_data.event_key = '<notif id>';

=================

1. Workflow: version

=================

$FND_TOP/sql/wfver.sql

----------------------------------------------------

2. check workflow status.

----------------------------------------------------

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date

from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc

where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

----------------------------------------------------

3. check if workflow is used by only one instance

----------------------------------------------------

select p.parameter_id,

p.parameter_name,

v.parameter_value value

from apps.fnd_svc_comp_param_vals_v v,

apps.fnd_svc_comp_params_b p,

apps.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 ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')

order by p.parameter_name;

----------------------------------------------------

4. check if processor_read_timeout_close is set to 'Y'

----------------------------------------------------

set pagesize 100

set linesize 132

set feedback off

set verify off

col value format a35

col component_name format a30 

select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value

from apps.fnd_svc_comp_param_vals_v v, apps.fnd_svc_comp_params_b p, apps.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 = 'PROCESSOR_READ_TIMEOUT_CLOSE' 

order by c.component_name,p.parameter_name; 

----------------------------------------------------

5. check for bad e-mail address

----------------------------------------------------

If below SQL statement is returning rows you need to correct the email addresses for associated users:

set linesize 170

col name format a40 

col email_address format a80 

select name, email_address from apps.wf_local_roles where email_address like '% %';

select name, email_address from apps.wf_local_roles where email_address like '%%';

============================================

6. How to know mail sent to a user with details:

============================================

select name, display_name, notification_preference, email_address from wf_local_roles where name = '';

====================================================================

7. How to know whether it is set to correct url from porfile options:

====================================================================

set linesize 155;

set pagesize 200;

set verify off;

col Profile format a50;

col Value format a50;

select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile,

nvl(v.PROFILE_OPTION_VALUE,'Replace with non-virtual URL') Value

from apps.fnd_profile_options t, apps.fnd_profile_option_values v, apps.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 ('WF_MAIL_WEB_AGENT'));

====================================

8. How to know reqid, process id, sid..

=====================================

select request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where REQUEST_ID=20422815;

select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=20422815;

select a.session_id,

b.owner,

b.object_type,

b.object_name,

a.oracle_username,

a.os_user_name,

a.process,

a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id

and a.session_id='3383';

select name, display_name, notification_preference, email_address from wf_local_roles where name = 'SYSADMIN';

===============================================

9. Workflow: To see failed, open notifications

===============================================

SELECT message_type, COUNT(1)

FROM apps.wf_notifications

WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'

GROUP BY message_type;

====================================================================

10. To check if email address, notification preference, display_name

====================================================================

select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.com' and STATUS = 'ACTIVE' and DISPLAY_NAME = 'xxxxx';

select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE';

select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE' and EMAIL_ADDRESS = 'mmmmm@yahoo.com';

====================================================

11. How to know workflow responsibility from backend:

====================================================

select wes.status, wes.phase, wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions wes 

where we.name='oracle.apps.fnd.wf.ds.userRole.updated' and we.guid=wes.event_filter_guid;

============================================

12. Steps to drop and recreate WF_CONTROL queue:

============================================

a. Shut down the concurrent managers.

b. Connect to sqlplus session as APPS user:

Execute: (For Workflow Embedded within Apps)

SQL>exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force =>TRUE);

SQL> commit;

Execute: (For Standalone Workflow)

sqlplus / @wfctlqec.sql 

c. Execute wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.

Usage:

sqlplus / @wfjmsqc2.sql 

Both of the script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.

d.Run afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables

Usage:

sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS APPLSYS 

Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.

Workflow Notifications Status 'FAILED' checking status

1. Check workflow mailer service current status

  sqlplus apps/<apps password>
  select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

 
  sqlplus apps/<apps password>
  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');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM
 
  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a 
count of data is at best only a snapshot of a particular moment in time.

1. Here is a more robust script for monitoring the wf_notifications table:
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS
messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'

2. Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.

select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;
Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql