Workflow Notification Mailer Trouble Shooting in Oracle APPS

Workflow Notification Mailer Trouble Shooting in Oracle APPS


Hi Friends, in this post we will discuss about Workflow Notification mailer debugging steps and trouble shooting steps in Oracle Apps.

Step1:- We can see all workflow notification in This below Tables.

Select * From Wf_Notifications

If you want to see the open notifications which are pending for user action then you can use below query.


Select * From Wf_Notifications
WHERE STATUS=’OPEN’


If you want to see the open notifications which are pending for user action and those Email notification still pending to deliver then you can use this below query

Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’

if you want to see the open notifications which are pending for user action and those Email notification are delivered then you can use this below query


if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.


Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’

if you want to see the open notifications whose Email notifications Failed to get delivered then you can use below query.

Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘FAILED’


If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”

Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= :NOTIFICATION_ID

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

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 wf_error wf where wf.user_data.event_key =

If user is not receiving the emails from system then check the preferences in this below tables for the user_name

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’TEST_USER’ ;

Status – Active
Notification_preference-> Should be Email
Email Address should not be null


To see the workflow log you can use this below query.

Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory


 If you want to see Other OAF Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2013/03/oaf_10.html

If you want to see  Oracle Fusion Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html
If you want to Learn Oracle Workflow Builder, Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html

 

Workflow Customization : How to restrict Workflow Approval Up to Three Supervisor Hierarchy

Workflow Customization : How to restrict Workflow Approval Up to Three Supervisor Hierarchy



Step 1:- First Register PLSQL package which will use in Workflow Builder as below.

CREATE OR REPLACE PACKAGE XX_FUSION_REPORT_TRIGGER
IS
P_VENDOR_NAME VARCHAR2 (100);
P_WHERE_CLAUSE VARCHAR2 (1000);
FUNCTION before_data
RETURN BOOLEAN;
END XX_FUSION_REPORT_TRIGGER;

CREATE OR REPLACE PACKAGE BODY XX_FUSION_REPORT_TRIGGER
IS
FUNCTION before_data
RETURN BOOLEAN
AS
BEGIN
IF (P_VENDOR_NAME IS NULL)
THEN
p_where_clause := ' AND 1=1';
ELSIF P_VENDOR_NAME IS NOT NULL
THEN
p_where_clause := ' AND upper(a2.vendor_name) like ''%'||UPPER(:P_VENDOR_NAME)||'%'' ';
ELSE
p_where_clause := NULL;
END IF;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
HEN
RETURN FALSE;
END;
END XX_FUSION_REPORT_TRIGGER;
PROCEDURE THREE_SUPERV_RESTRICT(p_item_type    IN VARCHAR2,
                     p_item_key    IN VARCHAR2,
                     p_actid        IN NUMBER,
                     p_funmode        IN VARCHAR2,
                     p_result     OUT NOCOPY VARCHAR2 )
                     IS
cursor c1(l_rpt_approver  NUMBER) is
SELECT DISTINCT ppf.person_id manager_id,ppf.full_name,
    (SELECT name FROM per_positions WHERE position_id = paf.position_id) mgr_position,LEVEL sup_level
                FROM per_all_people_f ppf, per_all_assignments_f PAF
                WHERE 1 = 1 AND ppf.person_id = PAF.PERSON_ID
                AND SYSDATE BETWEEN NVL(paf.effective_Start_Date, SYSDATE) AND NVL(paf.effective_end_Date,SYSDATE)
                AND SYSDATE BETWEEN NVL(ppf.effective_Start_Date, SYSDATE) AND NVL(ppf.effective_end_Date,SYSDATE)          
                AND LEVEL <=3
                CONNECT BY NOCYCLE PRIOR paf.supervisor_id = ppf.person_id
                START WITH paf.person_id = TO_NUMBER(l_rpt_approver) ORDER BY LEVEL;
               
v_report_header_id number;
v_person_id  number;
 v_report_amt  number;
 v_report_cc  number;
 v_report_approver number;
 v_approval_limit  number;
               
     begin
    
 SELECT aeh.report_header_id,aeh.employee_id,SUM(aeh.total) report_amount,flex_concatenated, override_approver_id   
  INTO v_report_header_id ,v_person_id,v_report_amt,v_report_cc, v_report_approver
 FROM wf_items  wfi, ap_expense_report_headers_all aeh
WHERE wfi.item_type='APEXP' and wfi.item_key=p_item_key
AND wfi.user_key = aeh.invoice_Num
GROUP BY aeh.report_header_id,aeh.employee_id,flex_concatenated, override_approver_id;
    
     for i in c(v_report_approver) loop
    
    
     SELECT signing_limit
          INTO v_approval_limit
          FROM AP_WEB_SIGNING_LIMITS_ALL WHERE document_type='APEXP'
          AND employee_id = tmp.mgr_id AND cost_center = v_report_cc;
         
     if  i.sup_level<=3 and v_report_amt<= v_approval_limit then
      p_result := 'COMPLETE:Y';
      ELSE
       p_result := 'COMPLETE:N';
      END IF;
    
     IF p_result = 'COMPLETE:N' THEN
    UPDATE ap_expense_report_headers
    SET  last_update_date = sysdate, 
           expense_last_status_date = sysdate,
           expense_status_code = AP_WEB_OA_ACTIVE_PKG.C_REJECTED
    WHERE  report_header_id = V_report_header_id;
 END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;



Step2: Now we will create Workflow Function in I-Expense approval workflow to execute this above procedure so that our Three Level supervisor Hierarchy will work.

Step3:- Open the Expense Workflow(APEXP) Workflow in workflow Builder.


Step4: - Open 'Manager (Spending) Approval Process' process in Workflow Builder.

Step5:-  This is the standard Approval Process. We will remove this below direction and will create function between that.

Step6:-  Create Function in this process by clicking right click

 

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

Step7:- Create New Function as a Notification as below: Click New Notification.

Step8:-

 

 

 

Step9:- This is the Final Process flow After Changes.

If you want to see Other OAF Related Posts , Please visit Below URL.https://rpforacle.blogspot.in/2013/03/oaf_10.html



If you want to see  Oracle Fusion Related Posts , Please visit Below URL.

https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html

If you want to Learn Oracle Workflow Builder, Please visit Below URL.

https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html