Script to change Workflow Notification Timeout from backend for easy testing in Oracle Apps

To set the value for Requisition Approval Time-out Reminder Notifications for both New Requisitions and Requisition Change Orders; one has to download the workflow and do the respective changes in Attribute and again upload the workflow. If one wants to do testing for approval process it's quite long process to do the changes and again revert back. 
Below queries will help to update the workflow notification values from back-end (all values are in min) for various reminder notifications. 

We need to run different scripts for Purchase Requisition and Requisition Change Orders.


Note: In all queries, The Time-out value will be shown in Minutes.

Scripts for Purchase Requisition

Queries to Verify and Update Existing Time-out Value in Minutes for the 1st Reminder Notification
------------------------------------------------------------------------------------------------

SELECT aav.PROCESS_ACTIVITY_ID, 
       pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
  FROM wf_process_activities pa, wf_activity_attr_values aav
 WHERE    1=1 
       AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
       AND aav.process_activity_id = pa.instance_id
       AND activity_name = 'PO_REQ_APPROVE'
       AND aav.name = '#TIMEOUT'


UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2      --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_APPROVE'
                  AND aav.name = '#TIMEOUT')

Queries to Verify and Update Existing Time-out Value in Minutes for the 2nd Reminder Notification
----------------------------------------------------------------------------------------------------------
UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2          --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER1'
                  AND aav.name = '#TIMEOUT')

Queries to Verify and Update Existing Time-out Value in Minutes for the 3rd Reminder Notification
---------------------------------------------------------------------------------------------------------
UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2         --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER2'
                  AND aav.name = '#TIMEOUT')
   
Query to verify the values for all 3 Reminders Time-out values using a single query 
---------------------------------------------------------------------------------------------------------

SELECT pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
FROM   apps.wf_process_activities pa,
       apps.wf_activity_attr_values aav
WHERE  pa.process_item_type = 'REQAPPRV'
       AND aav.process_activity_id = pa.instance_id
       AND aav.NAME = '#TIMEOUT' ;

Scripts for Requisition Change Order

Query to Verify Existing Time-out Value in Minutes for the Requisition Change Order Time-out  Notification  
-------------------------------------------------------------------------------------------------------------

SELECT aav.*, aav.number_value
FROM   apps.wf_activity_attr_values aav
WHERE  aav.NAME = '#TIMEOUT'
       AND aav.process_activity_id IN (SELECT pa.instance_id
                                       FROM   apps.wf_process_activities pa
                                       WHERE
           pa.activity_name = 'PO_REQ_APPROVE' AND
            pa.process_item_type = 'POREQCHA') ;

            
Query to update the Existing Time-out Value in Minutes for the Requisition Change Order Time-out  Notification
-----------------------------------------------------------------------------------------------------------
UPDATE apps.wf_activity_attr_values aav
SET    aav.number_value = 10080 -- In Minutes, for 7 days it is 7*24*60 = 10080
WHERE  aav.NAME = '#TIMEOUT'
       AND aav.process_activity_id IN (SELECT pa.instance_id
                                      FROM   apps.wf_process_activities pa
                                      WHERE
           pa.activity_name = 'PO_REQ_APPROVE' AND
            pa.process_item_type = 'POREQCHA') ;
commit;

Note: This will update 2 rows as it has 2 workflow processes linked to it in Requisition Change Order (POREQCHA) workflow.