EBS Workflow Mailer not sending mails mail_status failed solution

 

When a notification fails to be rendered or delivered from the Java tier (in this case due to an email server outage) then the notification will be placed into a FAILED state. The notifications that fails to be delivered to a user then has their notification preference set to DISABLED and any subsequent notifications will be automatically be set to FAILED either at the PL/SQL level or the java level depending on where the request for a notification is received. 

Run the following select statement to identify those users that are DISABLED:

select user_name from fnd_user_preferences where PREFERENCE_VALUE = 'DISABLED';

Below block update the user preferences

DECLARE

 l_disabled_count NUMBER;

 l_updated_count  NUMBER;

BEGIN

 DBMS_OUTPUT.PUT_LINE('Program to display updated row count');

 DBMS_OUTPUT.PUT_LINE('**************************************');

    SELECT count(*)

    INTO   l_disabled_count

    FROM fnd_user_preferences

    WHERE PREFERENCE_VALUE = 'DISABLED';

    dbms_output.put_line('Disabled Count :'||l_disabled_count);

     BEGIN

     update fnd_user_preferences

     set    preference_value = 'MAILHTML'

     where PREFERENCE_VALUE = 'DISABLED';

       

     dbms_output.put_line('Updated Row count: '||SQL%ROWCOUNT);

       COMMIT;

       

     EXCEPTION WHEN OTHERS THEN

     dbms_output.put_line('Error in Update - '||SUBSTR(SQLERRM,1,100));

     END;

       

 DBMS_OUTPUT.PUT_LINE('**************************************');

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error in Main - '||SUBSTR(SQLERRM,1,100));

END;