Scripts to Update the Values for Requisition Approval Time-out Reminder Notifications for both New Requisitions and Requisition Change Orders

 Most of the times we receive requests from business to set the value for Requisition Approval Time-out Reminder Notifications for both New Requisitions and Requisition Change Orders. 

In all these case, we have to download the corresponding workflow and update the Attribute Value from the corresponding workflow process. Once we made the changes, we have to migrate/save the workflow to the database.

Also, in most of the cases it is not possible to Test the Time-out Notifications with Real Value (Eg:- 15 Days, we have to wait for 15 days to receive the Time-out Notification even in Test Instance). To avoid the work related to download and uploading of workflow, the below scripts are very much helpful. We can update to a least value(Eg:- 15 mins for Each Reminder to complete the testing in Test Instances before migrating to PROD).

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.

1) Scripts for Purchase Requisition

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

SELECT 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 = 'REQAPPRV') ;



UPDATE apps.wf_activity_attr_values aav
SET    aav.number_value = 10080 -- In Minutes, for 7 days it is 7*24*60 = 10800
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 = 'REQAPPRV') ;
commit;


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

SELECT 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_REMINDER1'
           AND pa.process_item_type = 'REQAPPRV') ;


UPDATE apps.wf_activity_attr_values aav
SET    aav.number_value = 7200-- In Minutes, for 5 days it is 5*24*60 = 7200
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_REMINDER1'
           AND pa.process_item_type = 'REQAPPRV') ;
commit;


Queries to Verify and Update Existing Time-out Value in Minutes for the 3rd Reminder Notification
-------------------------------------------------------------------------------------------------------------
SELECT 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_REMINDER2'
           AND pa.process_item_type = 'REQAPPRV') ;



UPDATE apps.wf_activity_attr_values aav
SET    aav.number_value = 4320 -- In Minutes, for 3 days it is 3*24*60 = 4320
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_REMINDER2'
           AND pa.process_item_type = 'REQAPPRV') ;
commit;


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' ;


2) 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.