How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent

1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table. Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

This should show which notifications are waiting to be e-mailed.

One can use the BEGIN_DATE column to help narrow down the ones not to send if one only wants to stop the e-mails from a specific date range.

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The
mailer will think the e-mail has already been sent and it will not send it again.. (Users can
still reply to the notification from the worklist page in the applications).
Example:

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

OR

update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and MAIL_STATUS = 'MAIL';

This will update all notifications waiting to be sent by the mailer.

2. Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent. It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys

3. Now start the mailer.


Ref Metalink Note - Doc ID 372933.1

How to Rollback SSHR Transaction in Oracle Self Service

How to Rollback SSHR Transaction in oracle apps.begin
hr_transaction_api.rollback_transaction(p_transaction_id);
end;

commit;

Note:

p_transaction_id is the transaction_id that you will find in hr_api_transaction table.

After executing the above statement you will not find any record in hr_api_transaction

OTL API to delete whole time card for the week

DECLARE
   -- Constant declarations
   l_otl_appl_id CONSTANT NUMBER (3) := 809; --OTL application id
   l_resp_appl_id NUMBER (10) := 51102; --OTL Responsibility id
l_user_id VARCHAR2 (20) := 120345; -- id from fnd_user table
BEGIN
-- First initialize your session, this needs to be done for internal reasons so
-- the TimeStore knows who is trying to deposit the information. When you log
-- into SS, the same is done for you by the framework, here however we have to do
-- it manually.
FND_GLOBAL.
APPS_INITIALIZE (user_id => l_user_id,
resp_id => l_resp_appl_id,
resp_appl_id => l_otl_appl_id); -- This is the appl_id for OTL, do not change

--
--Delte time card API calling
--
hxc_timestore_deposit.DELETE_TIMECARD (p_building_block_id => 3423423); --p_building_block_id is the timecard id with scope 'Timecard' and endate should be ''12/31/4712

COMMIT;
END;