The following lines contain the word 'select', 'insert', 'update' or 'delete':
* p_preserve_num_days no of days bfr which the recs must be deleted
* OUT:
* Errbuf Error buffer
* Retcode Return code
*
*/
PROCEDURE cleanse_main (
Errbuf OUT NOCOPY VARCHAR2,
Retcode OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := 'bot_cleanse';
Cleanse concurrent program identifies and deletes the records that
were processed before the cutoff date.
How to calculate the cutoff date
Cutoff date is calculated based on number of days data that user wants
to preserve.
Logic
. identify the cutoff date
. delete records prior that were processed prior to cutoff date.
Known Caveat
There is a possibility that the cleanse concurrent program deletes recs
before they are actually consumed by any subscription.
Consider the following case:
. On a customer instance, customer has Rule Based Subscriptions
for Person Update Event.
. Raise Concurrent Program Raised PersonBO.update Event On Sep 12th 2005.
. WF Listeners were not scheduled, hence the event was deferred.
. Cleanse Concurrent Program was scheduled to run at the end of each day
with following parameter value:
p_preserve_num_days = 0
This means do not preserve any rows. Cocnurrent program will delete rows
for whcih event is populated and date is < SYSDATE.
. On Sept., 13th 2005, Clenase concurrent program would have deleted all
records in BOT.
. On Sept 30th 2005 WF Listeners were scheduled and all the information
in BOT is lost. So, all the user subscriptions will fail as there is
no data to select in BOT.
This known issue was communicated to PM and as there is no other option,
PM agreed to handle this through user documentation.
*/
l_preserve_num_days := TO_NUMBER(FND_PROFILE.VALUE('HZ_BO_EVENTS_PRESERVE_DAYS'));