1 PACKAGE BODY inv_txn_purge_main AS
2 /* $Header: INVTPGMB.pls 120.0.12000000.2 2007/02/21 23:06:18 yssingh ship $ */
3 PROCEDURE txn_purge_main(x_errbuf OUT NOCOPY VARCHAR2, x_retcode OUT NOCOPY NUMBER, p_orgid IN NUMBER, p_purge_date IN DATE) IS
4 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5 l_max_workers NUMBER := NVL(fnd_profile.VALUE('INV_MAX_TXN_PG_PROCESSES'), 20);
6 l_num_of_periods NUMBER;
7 l_min_mmt_date DATE;
8 l_min_period_id NUMBER;
9 l_min_date DATE;
10 l_max_date DATE;
11 l_min_log_date DATE;
12 l_period_per_worker NUMBER;
13 l_num_of_workers NUMBER;
14 l_num_of_periods_fetched NUMBER;
15 l_num_of_workers_launched NUMBER;
16 l_purge_req_id NUMBER;
17 l_req_id NUMBER;
18 l_result BOOLEAN;
19 l_count NUMBER;
20 l_sleep_time NUMBER := 5;
21
22 v_min_dt VARCHAR2(20); -- For bug 4055865.
23 v_max_dt VARCHAR2(20); -- For bug 4055865.
24
25
26 TYPE l_reqstatus_table IS TABLE OF NUMBER
27 INDEX BY BINARY_INTEGER;
28
29 l_reqstatus_tbl_type l_reqstatus_table;
30
31 TYPE l_status_code_table IS TABLE OF VARCHAR2(10)
32 INDEX BY BINARY_INTEGER;
33
34 l_status_code_tbl_type l_status_code_table;
35 l_completion_status VARCHAR2(1);
36
37 success BOOLEAN;
38 submission_error_except EXCEPTION;
39
40 TYPE min_max_dates IS RECORD(
41 min_date DATE
42 , max_date DATE
43 , period_id NUMBER
44 );
45
46 CURSOR c_oap IS
47 SELECT period_start_date
48 , schedule_close_date
49 , acct_period_id
50 FROM org_acct_periods
51 WHERE period_start_date <= p_purge_date
52 AND open_flag = 'N'
53 AND organization_id = p_orgid
54 AND acct_period_id >= l_min_period_id
55 ORDER BY acct_period_id;
56
57 period_rec_type min_max_dates;
58 BEGIN
59 IF (l_debug = 1) THEN
60 inv_log_util.TRACE('OrgId = ' || p_orgid || ', Purge Date= ' || p_purge_date, 'PURGE_MAIN', 9);
61 END IF;
62
63 IF l_max_workers > 20 THEN
64 l_max_workers := 20;
65 END IF;
66
67 IF (l_debug = 1) THEN
68 inv_log_util.TRACE('Max # of txn purge processes = ' || l_max_workers, 'PURGE_MAIN', 9);
69 END IF;
70
71 SELECT MIN(transaction_date)
72 INTO l_min_mmt_date
73 FROM mtl_material_transactions
74 WHERE organization_id = p_orgid
75 AND transaction_date <= p_purge_date;
76
77 IF (l_debug = 1) THEN
78 inv_log_util.TRACE('MMT Min Date = ' || l_min_mmt_date, 'PURGE_MAIN', 9);
79 END IF;
80
81 IF l_min_mmt_date IS NOT NULL THEN
82 SELECT acct_period_id
83 INTO l_min_period_id
84 FROM org_acct_periods
85 WHERE organization_id = p_orgid
86 AND TRUNC(period_start_date) <= TRUNC(l_min_mmt_date)
87 AND TRUNC(schedule_close_date) >= TRUNC(l_min_mmt_date)
88 AND open_flag = 'N';
89
90 SELECT COUNT(acct_period_id)
91 INTO l_num_of_periods
92 FROM org_acct_periods
93 WHERE organization_id = p_orgid
94 AND acct_period_id >= l_min_period_id
95 AND TRUNC(period_start_date) <= TRUNC(p_purge_date)
96 AND open_flag = 'N';
97 ELSE
98 l_num_of_periods := 0;
99 IF (l_debug = 1) THEN
100 inv_log_util.TRACE('No Records in MMT to purge', 'PURGE_MAIN', 9);
101 END IF;
102 x_retcode := 0;
103 fnd_message.set_name('INV', 'INV_PURGE_TXN_ERR');
104 x_errbuf := fnd_message.get;
105 RETURN;
106 END IF;
107
108 IF (l_debug = 1) THEN
109 inv_log_util.TRACE('No of Periods = ' || l_num_of_periods, 'PURGE_MAIN', 9);
110 inv_log_util.TRACE('Min period id = ' || l_min_period_id, 'PURGE_MAIN', 9);
111 END IF;
112
113 IF l_num_of_periods > 0 THEN
114 l_period_per_worker := CEIL(l_num_of_periods / l_max_workers);
115 l_num_of_workers := CEIL(l_num_of_periods / l_period_per_worker);
116 END IF;
117
118 IF (l_debug = 1) THEN
119 inv_log_util.TRACE('Period(s) per worker = ' || l_period_per_worker, 'PURGE_MAIN', 9);
120 inv_log_util.TRACE('No. of workers = ' || l_num_of_workers, 'PURGE_MAIN', 9);
121 END IF;
122
123 l_num_of_workers_launched := 0;
124 l_num_of_periods_fetched := 0;
125
126 IF l_period_per_worker > 0 THEN
127 OPEN c_oap;
128
129 LOOP
130 FETCH c_oap INTO period_rec_type;
131 EXIT WHEN c_oap%NOTFOUND;
132
133 -- l_min_date should only be set when l_num_of_periods_fetched = 0
134 IF l_num_of_periods_fetched = 0 THEN
135 --Bug 5894075, Adding trunc
136 l_min_date := TRUNC(period_rec_type.min_date);
137 END IF;
138 --Bug 5894075, Adding trunc
139 l_min_log_date := TRUNC(period_rec_type.min_date);
140
141 --Bug 5894075, removing + 1
142 l_max_date := TRUNC(period_rec_type.max_date);
143
144 IF (l_debug = 1) THEN
145 inv_log_util.TRACE('Min period Date= ' || l_min_log_date || ' Max period Date= ' || (l_max_date), 'PURGE_MAIN', 9);
146 END IF;
147
148 l_num_of_periods_fetched := l_num_of_periods_fetched + 1;
149
150 -- For the last worker we purge by p_purge_date
151 IF (l_num_of_workers_launched = l_num_of_workers - 1) THEN
152 IF (l_debug = 1) THEN
153 inv_log_util.TRACE('Launching last Purge worker with Min period Date= ' || l_min_date || ' and Max period Date= ' || p_purge_date, 'PURGE_MAIN', 9);
154 END IF;
155
156
157 -- For bug 4055865. Converting the date into Varchar based on the date format on user preference tab.
158 v_min_dt := fnd_date.date_to_displaydate(l_min_date);
159 v_max_dt := fnd_date.date_to_displaydate(p_purge_date);
160
161
162 l_purge_req_id :=
163 fnd_request.submit_request(application => 'INV'
164 , program => 'INVTPGWB'
165 , argument1 => p_orgid
166 , argument2 => v_min_dt -- For bug 4055865.
167 , argument3 => v_max_dt); -- For bug 4055865.
168
169 IF (l_purge_req_id = 0) THEN
170 -- Handle submission error --
171 IF (l_debug = 1) THEN
172 inv_log_util.TRACE('Error launching last Purge Worker', 'PURGE_MAIN', 9);
173 END IF;
174
175 RAISE submission_error_except;
176 ELSE
177 COMMIT;
178 END IF;
179
180 IF (l_debug = 1) THEN
181 inv_log_util.TRACE('Concurrent Request_id is ' || l_purge_req_id, 'PURGE_MAIN', 9);
182 END IF;
183
184 l_num_of_workers_launched := l_num_of_workers_launched + 1;
185 l_reqstatus_tbl_type(l_num_of_workers_launched) := l_purge_req_id;
186 EXIT; --Exit the loop, since all workers have been launched
187 END IF;
188
189 IF l_num_of_periods_fetched = l_period_per_worker THEN
190 IF (l_debug = 1) THEN
191 inv_log_util.TRACE('Launching Purge worker with Min period Date= ' || l_min_date || ' and Max period Date= ' || (l_max_date-1), 'PURGE_MAIN', 9);
192 END IF;
193
194
195 -- For bug 4055865. Converting the date into Varchar based on the date format on user preference tab.
196 v_min_dt := fnd_date.date_to_displaydate(l_min_date);
197 v_max_dt := fnd_date.date_to_displaydate(l_max_date);
198
199
200 l_purge_req_id :=
201 fnd_request.submit_request(application => 'INV'
202 , program => 'INVTPGWB'
203 , argument1 => p_orgid
204 , argument2 => v_min_dt -- For bug 4055865.
205 , argument3 => v_max_dt); -- For bug 4055865.
206
207 IF (l_purge_req_id = 0) THEN
208 -- Handle submission error --
209 IF (l_debug = 1) THEN
210 inv_log_util.TRACE('Error launching Purge Worker', 'PURGE_MAIN', 9);
211 END IF;
212
213 RAISE submission_error_except;
214 ELSE
215 COMMIT;
216 END IF;
217
218 IF (l_debug = 1) THEN
219 inv_log_util.TRACE('Concurrent Request_id is ' || l_purge_req_id, 'PURGE_MAIN', 9);
220 END IF;
221
222 l_num_of_workers_launched := l_num_of_workers_launched + 1;
223 l_num_of_periods_fetched := 0; --resetting l_num_of_periods_fetched
224 l_reqstatus_tbl_type(l_num_of_workers_launched) := l_purge_req_id;
225 END IF;
226 END LOOP;
227
228 CLOSE c_oap;
229 /**** Set the request to Pause status if all the Purge workers have not finished ****/
230 l_result := FALSE;
231
232 WHILE(NOT l_result) LOOP
233 FOR idx IN 1 .. l_reqstatus_tbl_type.COUNT LOOP
234 l_req_id := l_reqstatus_tbl_type(idx);
235
236 SELECT COUNT(*)
237 INTO l_count
238 FROM fnd_concurrent_requests
239 WHERE request_id = l_req_id
240 AND phase_code = 'C';
241
242 IF l_count = 1 THEN
243 l_result := TRUE;
244 ELSE
245 l_result := FALSE;
246 END IF;
247
248 IF (NOT l_result) THEN
249 EXIT;
250 END IF;
251 END LOOP; -- end for loop
252
253 DBMS_LOCK.sleep(l_sleep_time);
254 END LOOP; -- end while loop
255
256 --Print the completed status for the requests in the log
257 --Bug 3687369, need to print the errored status of the child requests in the log.
258
259 FOR idx IN 1 .. l_reqstatus_tbl_type.COUNT LOOP
260 SELECT STATUS_CODE INTO l_status_code_tbl_type(idx)
261 FROM fnd_concurrent_requests
262 WHERE request_id = l_reqstatus_tbl_type(idx) AND phase_code = 'C';
263
264 IF (l_status_code_tbl_type(idx) = 'E') then
265 l_status_code_tbl_type(idx) := 'error';
266 l_completion_status := 'E';
267 ELSIF (l_status_code_tbl_type(idx) = 'C') then
268 l_status_code_tbl_type(idx) := 'success';
269 ELSIF (l_status_code_tbl_type(idx) = 'G') then
270 l_status_code_tbl_type(idx) := 'warning';
271 END IF;
272
273 IF (l_debug = 1) THEN
274 inv_log_util.TRACE('Concurrent Request_id ' || l_reqstatus_tbl_type(idx) || ' has completed with ' ||l_status_code_tbl_type(idx), 'PURGE_MAIN', 9);
275 END IF;
276 END LOOP;
277
278 --Bug 3687369, if any of the child requests errors out, then main program should complete with a warning.
279 if (l_completion_status = 'E') then
280 x_retcode := 1;
281 fnd_message.set_name('INV', 'INV_PURGE_TXN_ERR');
282 x_errbuf := fnd_message.get;
283 return;
284 end if;
285
286 x_retcode := 0;
287 x_errbuf := 'Success';
288 ELSE --l_period_per_worker = 0
289 IF (l_debug = 1) THEN
290 inv_log_util.TRACE('No periods to purge','PURGE_MAIN', 1);
291 END IF;
292 x_retcode := 0;
293 fnd_message.set_name('INV', 'INV_PURGE_TXN_ERR');
294 x_errbuf := fnd_message.get;
295 END IF;
296 EXCEPTION
297 WHEN submission_error_except THEN
298 IF (l_debug = 1) THEN
299 inv_log_util.TRACE('submission_error_except :' || SQLCODE, 'PURGE_MAIN', 1);
300 inv_log_util.TRACE('submission_error_except :' || SUBSTR(SQLERRM, 1, 100), 'PURGE_MAIN', 1);
301 END IF;
302
303 IF c_oap%ISOPEN THEN
304 CLOSE c_oap;
305 END IF;
306
307 x_retcode := 2;
308 fnd_message.set_name('INV', 'INV_PURGE_TXN_ERR');
309 x_errbuf := fnd_message.get;
310 WHEN OTHERS THEN
311 IF (l_debug = 1) THEN
312 inv_log_util.TRACE('Error :' || SUBSTR(SQLERRM, 1, 100), 'PURGE_MAIN', 1);
313 END IF;
314
315 IF c_oap%ISOPEN THEN
316 CLOSE c_oap;
317 END IF;
318
319 x_retcode := 2;
320 fnd_message.set_name('INV', 'INV_PURGE_TXN_ERR');
321 x_errbuf := fnd_message.get;
322 END txn_purge_main;
323 END inv_txn_purge_main;
[Home] [Help]