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