1 PACKAGE BODY HZ_BES_BO_CLEANSE_PKG AS
2 /* $Header: ARHBESCB.pls 120.2 2005/09/21 17:38:05 smattegu noship $ */
3
4 --forward declaration of private procedure and functions
5
6 PROCEDURE log(
7 message IN VARCHAR2,
8 newline IN BOOLEAN DEFAULT TRUE);
9
10 FUNCTION logerror(
11 SQLERRM VARCHAR2 DEFAULT NULL)
12 RETURN VARCHAR2;
13
14 /**
15 * PROCEDURE cleanse_main
16 *
17 * DESCRIPTION
18 * Cleanse Infrastructure Program
19 *
20 * Scope - Public
21 *
22 * ARGUMENTS
23 * IN:
24 * p_preserve_num_days no of days bfr which the recs must be deleted
25 * OUT:
26 * Errbuf Error buffer
27 * Retcode Return code
28 *
29 */
30
31 PROCEDURE cleanse_main (
32 Errbuf OUT NOCOPY VARCHAR2,
33 Retcode OUT NOCOPY VARCHAR2
34 ) IS
35 l_debug_prefix VARCHAR2(30) := 'bot_cleanse';
36 l_cutoff_dt DATE := SYSDATE;
37 l_preserve_num_days NUMBER;
38 BEGIN
39 --Standard start of API savepoint
40 SAVEPOINT cleanse_main_pkg;
41
42 FND_MSG_PUB.initialize;
43 Retcode := 0; -- setting the return code to success
44
45 -- Debug info.
46 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
47 hz_utility_v2pub.debug(p_message=>'cleanse_main (+)',
48 p_prefix=>l_debug_prefix,
49 p_msg_level=>fnd_log.level_procedure);
50 END IF;
51
52 log('Start Cleansing for BES Concurrent Program');
53 /*
54 Goal
55 Cleanse concurrent program identifies and deletes the records that
56 were processed before the cutoff date.
57 How to calculate the cutoff date
58 Cutoff date is calculated based on number of days data that user wants
59 to preserve.
60 Logic
61 . identify the cutoff date
62 . delete records prior that were processed prior to cutoff date.
63 Known Caveat
64 There is a possibility that the cleanse concurrent program deletes recs
65 before they are actually consumed by any subscription.
66 Consider the following case:
67 . On a customer instance, customer has Rule Based Subscriptions
68 for Person Update Event.
69 . Raise Concurrent Program Raised PersonBO.update Event On Sep 12th 2005.
70 . WF Listeners were not scheduled, hence the event was deferred.
71 . Cleanse Concurrent Program was scheduled to run at the end of each day
72 with following parameter value:
73 p_preserve_num_days = 0
74 This means do not preserve any rows. Cocnurrent program will delete rows
75 for whcih event is populated and date is < SYSDATE.
76 . On Sept., 13th 2005, Clenase concurrent program would have deleted all
77 records in BOT.
78 . On Sept 30th 2005 WF Listeners were scheduled and all the information
79 in BOT is lost. So, all the user subscriptions will fail as there is
80 no data to select in BOT.
81 This known issue was communicated to PM and as there is no other option,
82 PM agreed to handle this through user documentation.
83
84
85 */
86
87
88 l_preserve_num_days := TO_NUMBER(FND_PROFILE.VALUE('HZ_BO_EVENTS_PRESERVE_DAYS'));
89
90 l_cutoff_dt := l_cutoff_dt - NVL(l_preserve_num_days,0);
91
92 HZ_BES_BO_UTIL_PKG.del_bot(l_cutoff_dt);
93 log('Finish Cleansing for BES Concurrent Program');
94
95 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
96 hz_utility_v2pub.debug(p_message=>'cleanse_main (-)',
97 p_prefix=>l_debug_prefix,
98 p_msg_level=>fnd_log.level_procedure);
99 END IF;
100
101 EXCEPTION
102 WHEN FND_API.G_EXC_ERROR THEN
103 log('Expected error when cleansing');
104 ROLLBACK TO cleanse_main_pkg;
105 Retcode := 2;
106 Errbuf := logerror(SQLERRM);
107 FND_FILE.close;
108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
109 log('Unexpected error when cleansing');
110 ROLLBACK TO cleanse_main_pkg;
111 Retcode := 2;
112 Errbuf := logerror(SQLERRM);
113 FND_FILE.close;
114 WHEN OTHERS THEN
115 log('Other unexpected error when cleansing');
116 ROLLBACK TO cleanse_main_pkg;
117 Retcode := 2;
118 Errbuf := logerror(SQLERRM);
119 FND_FILE.close;
120 END cleanse_main;
121
122 /**
123 * Procedure to write a message to the log file
124 **/
125 PROCEDURE log(
126 message IN VARCHAR2,
127 newline IN BOOLEAN DEFAULT TRUE) IS
128 BEGIN
129 IF message = 'NEWLINE' THEN
130 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
131 ELSIF (newline) THEN
132 FND_FILE.put_line(fnd_file.log,message);
133 ELSE
134 FND_FILE.put_line(fnd_file.log,message);
135 END IF;
136 END log;
137
138 /*-----------------------------------------------------------------------
139 | Function to fetch messages of the stack and log the error
140 | Also returns the error
141 |-----------------------------------------------------------------------*/
142 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
143 RETURN VARCHAR2 IS
144 l_msg_data VARCHAR2(2000);
145 BEGIN
146 FND_MSG_PUB.Reset;
147
148 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
149 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
150 END LOOP;
151 IF (SQLERRM IS NOT NULL) THEN
152 l_msg_data := l_msg_data || SQLERRM;
153 END IF;
154 log(l_msg_data);
155 RETURN l_msg_data;
156 END logerror;
157
158 END HZ_BES_BO_CLEANSE_PKG;
[Home] [Help]