Oracle Workflow Run Backend in PL/SQL

 --PLSQL Code to Run Workflow Backend
--Create sequence XXJG_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 :='XXJGWF'; --Item Internal Name -- It can be 8 Charater
P_X_PROC_INT_NAME := 'XXJG_FIRST_PRC'; --Process Name 
P_USER_NAME :='OPERATIONS' ; 
SELECT XXJG_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 => 'XXPONUM' ,avalue => 'PO-ABC-199');
  
  -- 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;

Oracle R12 Workflow Notification Event Messages Tracking Script

--- Query Notification mail status is MAIL or INVALID

 SELECT status, mail_status

        FROM wf_notifications

        WHERE notification_id = :P_notif_ID;

--- Recipient Role is a valid e-mail address and notification preference

        SELECT name, email_address, 

        nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference) preference

        FROM wf_roles

       WHERE upper(name) = upper(:p_recipient_role);

--- Ensure that this index is in place:

        select index_name,column_name,column_position

        from dba_ind_columns

        where table_name='WF_NOTIFICATION_OUT' and

        table_owner='APPLSYS';

Oracle R12 Find the Workflow Events

 --Oracle Workflow Events Query

  SELECT a.*,

         a.msg_state,

         a.user_data.event_name,

         TRUNC (a.user_data.send_date)

    FROM applsys.aq$wf_deferred a

   WHERE     a.user_data.send_date > SYSDATE - 1 / 24

         AND a.user_data.event_name IN

                ('oracle.apps.gmi.inv.po.receipt',

                 'oracle.apps.gml.po.receipt.created',

                 'oracle.apps.gmi.inventory.created')

ORDER BY 1, 2

SELECT w_even.name,

       w_even.status event_status,

       w_e_subs.status subscription_status,

       NVL (w_e_subs.phase, 0) subscription_phase,

       w_e_subs.rule_function

  FROM wf_events w_even, wf_event_subscriptions w_e_subs

 WHERE     w_even.name IN

              ('oracle.apps.gmi.inv.po.receipt',

               'oracle.apps.gml.po.receipt.created',

               'oracle.apps.gmi.inventory.created')

       AND w_e_subs.event_filter_guid = w_even.guid;

Oracle Work Flow Related tables

–Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXTEST';
SELECT * FROM WF_ROLES where name = 'XXTEST';
SELECT * FROM WF_USER_ROLES where user_name = 'XXTEST';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXTEST';

–Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; –PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV);
–OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%PO%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP’;
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

–Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = ‘OKCAUKAP’ and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = ‘OKCAUKAP’;
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = ‘CREATE_SOA’;
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

–Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = ‘OKC_DB_RETRY’;

–Workflow Messages
SELECT * FROM WF_MESSAGES where type = ‘OKCAUKAP’ and name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGES_TL where type = ‘OKCAUKAP’ and name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = ‘OKCAUKAP’ and message_name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = ‘OKCAUKAP’ and message_name = ‘APPROVE_CONTRACT’;

–Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = ‘OKCAUKAP’ order by begin_date desc;
–USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = ‘20683312’;
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = ‘OKCAUKAP’ and item_key=’20683312′;
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = ‘OKCAUKAP’ and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = ‘OKCAUKAP’ AND MESSAGE_NAME = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;

—
SELECT * FROM wf_user_role_assignments
SELECT * FROM wf_user_roles
SELECT * FROM wf_roles
SELECT * FROM wf_items
SELECT * FROM wf_item_attributes
SELECT * FROM wf_item_attribute_values
SELECT * FROM wf_item_attributes_tl
SELECT * FROM wf_activities
SELECT * FROM wf_activities_tl
SELECT * FROM wf_activity_attributes
SELECT * FROM wf_activity_attributes_tl
SELECT * FROM wf_activity_transitions
SELECT * FROM wf_deferred–wf_control

SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE '%JG%’
AND PROCESS_ACTIVITY_ID
IN(
SELECT *– PROCESS_ACTIVITY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = ‘OEOH’
AND ITEM_KEY =’62348′)

SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL

SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE ='TEST'
ORDER BY BEGIN_DATE DESC

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_COMMENTS

SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES

Run the Workflow backend or PL/SQL Code

The below script to run workflow from PL/SQL code-

DECLARE
   v_itemtype   VARCHAR2 (50);
   v_itemkey    VARCHAR2 (50);
   v_process    VARCHAR2 (50);
   v_userkey    VARCHAR2 (50);
BEGIN
   v_itemtype := 'TEXPO_W1';
   v_itemkey := '123';
   v_userkey := '123';
   v_process := 'TEX_PO_P';
   wf_engine.threshold := -1;
   wf_engine.createprocess (v_itemtype, v_itemkey, v_process);
   wf_engine.setitemuserkey (v_itemtype, v_itemkey, v_userkey);
   wf_engine.setitemowner (v_itemtype, v_itemkey, 'SYSADMIN');
   wf_engine.startprocess (v_itemtype, v_itemkey);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Workflow :- Hiding Workflow Buttons

1. Identify the .wft file that contains the definition of the process for which the email notification needs to be changed.

2. Connect to database using the Workflow Builder.

3. Open the .wft file in Oracle Workflow Builder.

4. Go to (M) Help > About Oracle Workflow Builder

5. Change Access level to 0 and check 'Allow modifications to customization'.

6. Expand the node of the item that needs to be changed, then under it, expand the  Messages node.

7. Right-click on the message that is currently sent to the users, and needs to be changed.

8. Define the attribute #WFM_OPEN_MAIL (Template for email with response).

9. Set its Default value to WFMAIL:OPEN_MAIL_OUTLOOK.

10. Click on Apply and save the change, and retest with a new WF process.

How to find Workflow User, Item Name, Function Message and Lookups

--Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';

--Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV); 
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

--Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

--Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';

--Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';


--Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '3308531';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;