Tech Trainings

Oracle EBS – Workflow – Approval Sample – For Your Action Notification- FYA

October 14, 2020 by admin

--Package Spec : 
--create table xxltech_wf_log(sno number, msg varchar2(150),cdate date);
--
--create sequence xxltech_wf_log_seq ; 

 Create or replace package xxltech_WF_UTILS  IS 
 
 PROCEDURE APPROVED_WF_STATUS (
       p_item_type           IN     VARCHAR2,
       p_item_key            IN     VARCHAR2,
       p_actid               IN     NUMBER  ,
       p_funcmode            IN     VARCHAR2,
        RESULT     OUT NOCOPY      VARCHAR2) ;
        
PROCEDURE REJECT_WF_STATUS (
       p_item_type           IN     VARCHAR2,
       p_item_key            IN     VARCHAR2,
       p_actid               IN     NUMBER  ,
       p_funcmode            IN     VARCHAR2,
        RESULT     OUT NOCOPY      VARCHAR2);        
 
 End xxltech_WF_UTILS; 

--Package Body 
 Create or replace package BODY xxltech_WF_UTILS  IS 
 
 
PROCEDURE APPROVED_WF_STATUS (
       p_item_type           IN     VARCHAR2,
       p_item_key            IN     VARCHAR2,
       p_actid               IN     NUMBER  ,
       p_funcmode            IN     VARCHAR2,
        RESULT     OUT NOCOPY      VARCHAR2)
   IS
 
       x_wf_result            VARCHAR2(100);
       x_new_status           VARCHAR2(50);
       x_project_id           NUMBER(15);
       x_notif_op varchar2(50) ; 
 
BEGIN
IF (p_funcmode <>  'RUN') then return; end if;


  WF_ENGINE.SETITEMATTRnumber
    ( ITEMTYPE => p_item_type ,itemkey => p_item_key,
aname => 'XXAPPROVAL_AMOUNT' ,avalue => 2500);

  --   x_wf_result  := wf_engine.getitemattrtext   (p_item_type, p_item_key, 'WF_STATUS');
   RESULT := 'COMPLETE';
   
 INSERT INTO   
       xxltech_wf_log values 
       (xxltech_wf_log_seq.nextval, ' Inside : APPROVED_WF_STATUS : APPROVED_WF_STATUS--->'|| RESULT,sysdate)          
       ;

EXCEPTION WHEN OTHERS THEN
 
x_wf_result :=x_wf_result||'Error Code-->'||SQLERRM;
 
 
END  APPROVED_WF_STATUS;



PROCEDURE REJECT_WF_STATUS (
       p_item_type           IN     VARCHAR2,
       p_item_key            IN     VARCHAR2,
       p_actid               IN     NUMBER  ,
       p_funcmode            IN     VARCHAR2,
        RESULT     OUT NOCOPY      VARCHAR2)
   IS
 
       x_wf_result            VARCHAR2(100);
       x_new_status           VARCHAR2(50);
       x_project_id           NUMBER(15);
       x_notif_op varchar2(50) ; 
 
BEGIN
IF (p_funcmode <>  'RUN') then return; end if;

  --   x_wf_result  := wf_engine.getitemattrtext   (p_item_type, p_item_key, 'WF_STATUS');
   RESULT := 'COMPLETE';
   
INSERT INTO   
       xxltech_wf_log values 
       (xxltech_wf_log_seq.nextval, 'Inside : REJECT_WF_STATUS: REJECT--->'|| RESULT,sysdate)          
       ;
               
EXCEPTION WHEN OTHERS THEN
 
x_wf_result :=x_wf_result||'Error Code-->'||SQLERRM;
 
 
END  REJECT_WF_STATUS;
 
 
 
 END xxltech_WF_UTILS; 

--PLSQL Anonymous block to invoke the wf
--Create sequence XXLT_WF_S ; 

DECLARE
  p_x_item_type VARCHAR2(100);
  P_X_PROC_INT_NAME VARCHAR2(100);
  P_X_ATTRIBUTE1 VARCHAR2(100) ;
  x_itemkey number; 
  P_USER_NAME VARCHAR2(100) ; 

BEGIN
P_x_item_type :='XXSNWF'; --Item Internal Name 
P_X_PROC_INT_NAME := 'XXBROADCAST_PROC'; --Process Name 
P_USER_NAME :='OPERATIONS' ; 

SELECT XXLT_WF_S.NEXTVAL INTO x_itemkey FROM DUAL;

  -- API used to create a Process.
  wf_engine.createprocess ( itemtype => p_x_item_type ,itemkey => x_itemkey ,
     process => p_x_proc_int_name);
  -- API used to set the userkey for the Workflow.
  wf_engine.setitemuserkey ( itemtype => p_x_item_type ,itemkey => x_itemkey ,
    userkey => x_itemkey);
  --  -- API used to set the Attribute value in a Workflow.
  WF_ENGINE.SETITEMATTRTEXT ( ITEMTYPE => P_X_ITEM_TYPE ,itemkey => x_itemkey,
      aname => 'XXROLENAME' ,avalue => 'XXLTROLE');
  -- API used to set the Item Owner for the Workflow.
  wf_engine.setitemowner ( itemtype => p_x_item_type ,ITEMKEY => X_ITEMKEY ,owner => P_USER_NAME);
  -- API used to Start Process, here Main Process.
  wf_engine.startprocess (p_x_item_type, x_itemkey);
  
  dbms_output.put_line( 'workflow completed successfully');
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line( 'Exception Raised while Calling the Workflow... ' || SQLCODE || ' - ' || SQLERRM);
END;

--Workflow Code wft file : https://drive.google.com/file/d/1sW9cmBJhELiccWonuqXMPyEbMmg2m652/view?usp=sharing

Comments |0|