[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_PUB
Source
1 PACKAGE BODY PA_PURGE_PUB AS
2 -- $Header: PAXPURGB.pls 120.7 2010/10/31 20:09:52 nisinha ship $
3 Invalid_Arg_Exc_Pjr EXCEPTION;
4
5 --
6 -- PROCEDURE
7 -- START_PROCESS
8 -- PURPOSE
9 -- This API is called from the executable of the concurrent program :
10 -- ADM: Purge Obsolete Projects Data
11 -- Based on the Purge Type Value selected in the Concurrent Program ,
12 -- It calls respective APIs to do the purging.
13 --
14 -- Parameter Name In/Out Data Type Null? Default Value Description
15 -- ------------- ------ ---------- ------ ------------- ---------------------------------
16 -- P_PURGE_TYPE IN VARCHAR2 NOT NULL Indicates the purge option.
17 -- Valid values are: ALL DAILY_FCST_INFO PROJECTS_WORKFLOWS REPORTING_EXCEPTIONS
18 -- PURGE_ORG_AUTHORITY PURGE_PJI_DEBUG
19 --
20 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
21 -- Valid values are: 'Y' 'N'
22 --
23 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
24 -- ERRBUF OUT VARCHAR2 N/A N/A Indicates the error buffer to the concurrent program.
25 -- RETCODE OUT VARCHAR2 N/A N/A Indicates the return code to the concurrent program.
26
27 -- HISTORY
28 -- avaithia 01-March-2006 Created
29 --
30
31 PROCEDURE START_PROCESS
32 (
33 errbuf OUT NOCOPY VARCHAR2 ,
34 retcode OUT NOCOPY VARCHAR2 ,
35 p_purge_type IN VARCHAR2 ,
36 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
37 p_commit_size IN NUMBER DEFAULT 10000
38 )
39 IS
40 l_request_id NUMBER;
41 l_debug_level3 NUMBER := 3;
42 l_msg_count NUMBER := 0;
43 l_msg_data VARCHAR2(2000);
44 l_data VARCHAR2(2000);
45 l_msg_index_out NUMBER;
46 l_return_status VARCHAR2(2000);
47 l_local_error_flag VARCHAR2(1):='N'; -- 5201806
48 BEGIN
49 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
50
51 -- Save Point doesnt make sense here
52 -- As the APIs which this API calls ,have intermediate commits
53 -- in them . So,this save point would be lost anyway.
54
55 errbuf := NULL ;
56 retcode := 0;
57 l_return_status := FND_API.G_RET_STS_SUCCESS;
58 pa_debug.set_err_stack('PA_PURGE_PUB.START_PROCESS');
59 FND_MSG_PUB.initialize;
60
61 pa_debug.set_process('PLSQL','LOG',p_debug_mode);
62
63 IF p_debug_mode = 'Y' THEN
64 pa_debug.set_curr_function( p_function => 'START_PROCESS', p_debug_mode => p_debug_mode );
65 pa_debug.g_err_stage:= 'Entering START_PROCESS - Request Id : ' || l_request_id;
66 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
67 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
68 pa_debug.g_err_stage:= 'Purge Type is ' || p_purge_type ;
69 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
70 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
71 pa_debug.g_err_stage:= 'Commit Size is ' || p_commit_size ;
72 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
73 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
74 END IF;
75
76 IF p_purge_type IS NULL THEN
77 IF p_debug_mode = 'Y' THEN
78 pa_debug.g_err_stage:= 'Mandatory parameter to this API : Purge Type is NULL';
79 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
80 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
81 END IF;
82
83 l_return_status := FND_API.G_RET_STS_ERROR;
84 PA_UTILS.ADD_MESSAGE
85 (p_app_short_name => 'PA',
86 p_msg_name => 'PA_INV_PARAM_PASSED');
87 RAISE Invalid_Arg_Exc_Pjr;
88 END IF;
89
90
91 IF p_purge_type in ('ALL', 'DAILY_FCST_INFO') THEN
92 IF p_debug_mode = 'Y' THEN
93 pa_debug.g_err_stage:= 'Before Calling PA_PURGE_PUB.PURGE_FORECAST_ITEMS';
94 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
95 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
96 END IF;
97
98 PA_PURGE_PUB.PURGE_FORECAST_ITEMS
99 (
100 p_debug_mode => p_debug_mode ,
101 p_commit_size => p_commit_size ,
102 p_request_id => l_request_id ,
103 x_return_status => l_return_status,
104 x_msg_count => l_msg_count,
105 x_msg_data => l_msg_data
106 );
107
108 IF p_debug_mode = 'Y' THEN
109 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_FORECAST_ITEMS returned status ' || l_return_status;
110 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
111 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
112 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_FORECAST_ITEMS returned l_msg_count as '||l_msg_count;
113 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
114 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
115 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_FORECAST_ITEMS returned l_msg_data as ' || substrb(l_msg_data,1,240);
116 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
117 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
118 END IF;
119
120 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
121 l_local_error_flag := 'Y'; -- 5201806
122 END IF ;
123 END IF; -- 5171235
124
125 IF p_purge_type in ('ALL','PROJECTS_WORKFLOWS') THEN -- 5171235
126 IF p_debug_mode = 'Y' THEN
127 pa_debug.g_err_stage:= 'Before Calling PA_PURGE_PUB.PURGE_PROJ_WORKFLOW';
128 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
129 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
130 END IF;
131
132 PA_PURGE_PUB.PURGE_PROJ_WORKFLOW
133 (
134 p_debug_mode => p_debug_mode ,
135 p_commit_size => p_commit_size ,
136 p_request_id => l_request_id ,
137 x_return_status => l_return_status,
138 x_msg_count => l_msg_count,
139 x_msg_data => l_msg_data
140 );
141
142 IF p_debug_mode = 'Y' THEN
143 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PROJ_WORKFLOW returned status ' || l_return_status;
144 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
145 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
146 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PROJ_WORKFLOW returned l_msg_count as '||l_msg_count;
147 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
148 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
149 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PROJ_WORKFLOW returned l_msg_data as ' || substrb(l_msg_data,1,240);
150 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
151 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
152 END IF;
153
154 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
155 l_local_error_flag := 'Y'; -- 5201806
156 END IF ;
157 END IF; -- 5171235
158
159 IF p_purge_type in ('ALL', 'REPORTING_EXCEPTIONS') THEN -- 5171235
160
161 IF p_debug_mode = 'Y' THEN
162 pa_debug.g_err_stage:= 'Before Calling PA_PURGE_PUB.PURGE_REPORTING_EXCEPTIONS';
163 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
164 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
165 END IF;
166
167
168 PA_PURGE_PUB.PURGE_REPORTING_EXCEPTIONS
169 (
170 p_debug_mode => p_debug_mode ,
171 p_commit_size => p_commit_size ,
172 p_request_id => l_request_id ,
173 x_return_status => l_return_status,
174 x_msg_count => l_msg_count,
175 x_msg_data => l_msg_data
176 );
177
178 IF p_debug_mode = 'Y' THEN
179 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_REPORTING_EXCEPTIONS returned status ' || l_return_status;
180 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
181 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
182 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_REPORTING_EXCEPTIONS returned l_msg_count as '||l_msg_count;
183 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
184 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
185 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_REPORTING_EXCEPTIONS returned l_msg_data as ' || substrb(l_msg_data,1,240);
186 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
187 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
188 END IF;
189
190 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
191 l_local_error_flag := 'Y'; -- 5201806
192 END IF ;
193
194 END IF;
195
196 IF p_purge_type in ('ALL', 'ORG_AUTH') THEN
197
198 IF p_debug_mode = 'Y' THEN
199 pa_debug.g_err_stage:= 'Before Calling PA_PURGE_PUB.PURGE_ORG_AUTHORITY';
200 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
201 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
202 END IF;
203
204
205 PA_PURGE_PUB.PURGE_ORG_AUTHORITY
206 (
207 p_debug_mode => p_debug_mode ,
208 p_commit_size => p_commit_size ,
209 p_request_id => l_request_id ,
210 x_return_status => l_return_status,
211 x_msg_count => l_msg_count,
212 x_msg_data => l_msg_data
213 );
214
215 IF p_debug_mode = 'Y' THEN
216 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_ORG_AUTHORITY returned status ' || l_return_status;
217 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
218 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
219 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_ORG_AUTHORITY returned l_msg_count as '||l_msg_count;
220 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
221 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
222 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_ORG_AUTHORITY returned l_msg_data as ' || substrb(l_msg_data,1,240);
223 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
224 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
225 END IF;
226
227 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
228 l_local_error_flag := 'Y';
229 END IF ;
230 END IF;
231
232 IF p_purge_type in ('ALL', 'PJI_DEBUG') THEN
233
234 IF p_debug_mode = 'Y' THEN
235 pa_debug.g_err_stage:= 'Before Calling PA_PURGE_PUB.PURGE_PJI_DEBUG';
236 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
237 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
238 END IF;
239
240
241 PA_PURGE_PUB.PURGE_PJI_DEBUG
242 (
243 p_debug_mode => p_debug_mode ,
244 p_commit_size => p_commit_size ,
245 p_request_id => l_request_id ,
246 x_return_status => l_return_status,
247 x_msg_count => l_msg_count,
248 x_msg_data => l_msg_data
249 );
250
251 IF p_debug_mode = 'Y' THEN
252 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PJI_DEBUG returned status ' || l_return_status;
253 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
254 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
255 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PJI_DEBUG returned l_msg_count as '||l_msg_count;
256 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
257 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
258 pa_debug.g_err_stage:= 'PA_PURGE_PUB.PURGE_PJI_DEBUG returned l_msg_data as ' || substrb(l_msg_data,1,240);
259 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
260 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
261 END IF;
262
263 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
264 l_local_error_flag := 'Y';
265 END IF ;
266 END IF;
267
268 PA_PURGE_PUB.PRINT_OUTPUT_REPORT
269 (
270 p_request_id => l_request_id
271 ,x_return_status => l_return_status
272 ,x_msg_count => l_msg_count
273 ,x_msg_data => l_msg_data
274 );
275
276 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
277 --RAISE FND_API.G_EXC_ERROR; -- 5201806
278 l_local_error_flag := 'Y'; -- 5201806
279 END IF;
280
281 COMMIT;
282
283 IF l_local_error_flag = 'Y' THEN -- 5201806
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286
287 IF p_debug_mode = 'Y' THEN
288 pa_debug.g_err_stage:= 'Successfully Exiting START_PROCESS ';
289 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
290 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
291
292 pa_debug.reset_err_stack;
293 pa_debug.reset_curr_function;
294 END IF;
295
296 EXCEPTION
297 WHEN Invalid_Arg_Exc_Pjr THEN
298 l_return_status := FND_API.G_RET_STS_ERROR;
299 l_msg_count := FND_MSG_PUB.count_msg;
300 retcode := '-1';
301
302 IF p_debug_mode = 'Y' THEN
303 pa_debug.g_err_stage := 'Inside Invalid Argument exception of START_PROCESS API';
304 pa_debug.write_file('START_PROCESS : '|| pa_debug.g_err_stage);
305 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
306 END IF;
307
308 -- 5201806 : Commented not needed
309 --IF l_msg_count >= 1 THEN
310 -- PA_INTERFACE_UTILS_PUB.get_messages
311 -- (p_encoded => FND_API.G_TRUE, -- 5201806 : It should have been FALSE
312 -- p_msg_index => 1,
313 -- p_msg_count => l_msg_count,
314 -- p_msg_data => l_msg_data,
315 -- p_data => l_data,
316 -- p_msg_index_out => l_msg_index_out);
317 --
318 -- errbuf := l_data;
319 -- END IF;
320
321 FOR i in 1..FND_MSG_PUB.count_msg LOOP -- 5201806
322 PA_INTERFACE_UTILS_PUB.get_messages
323 (p_encoded => FND_API.G_FALSE,
324 p_msg_index => 1,
325 p_msg_count => l_msg_count,
326 p_msg_data => l_msg_data,
327 p_data => l_data,
328 p_msg_index_out => l_msg_index_out);
329
330 pa_debug.write_file('Error : ' ||i||': '|| l_data);
331 IF i = FND_MSG_PUB.count_msg THEN
332 errbuf := l_data;
333 END IF;
334 END LOOP;
335
336 IF p_debug_mode = 'Y' THEN
337 pa_debug.write_file('START_PROCESS :' || l_data);
338 pa_debug.write('PA_PURGE_PUB.START_PROCESS',l_data,l_debug_level3);
339 pa_debug.reset_err_stack;
340 pa_debug.reset_curr_function;
341 END IF ;
342 -- No raise as per FD
343
344 WHEN FND_API.G_EXC_ERROR THEN
345 l_return_status := FND_API.G_RET_STS_ERROR;
346 l_msg_count := FND_MSG_PUB.count_msg;
347 retcode := '-1';
348
349 IF p_debug_mode = 'Y' THEN
350 pa_debug.g_err_stage := 'Inside Expected Error block of START_PROCESS API';
351 pa_debug.write_file('START_PROCESS : '|| pa_debug.g_err_stage);
352 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
353 END IF;
354
355 -- 5201806 : Commented not needed
356 --IF l_msg_count >= 1 THEN
357 -- PA_INTERFACE_UTILS_PUB.get_messages
358 -- (p_encoded => FND_API.G_TRUE, -- 5201806 : It should have been FALSE
359 -- p_msg_index => 1,
360 -- p_msg_count => l_msg_count,
361 -- p_msg_data => l_msg_data,
362 -- p_data => l_data,
363 -- p_msg_index_out => l_msg_index_out);
364 --
365 -- errbuf := l_data;
366 -- END IF;
367
368 FOR i in 1..FND_MSG_PUB.count_msg LOOP -- 5201806
369 PA_INTERFACE_UTILS_PUB.get_messages
370 (p_encoded => FND_API.G_FALSE,
371 p_msg_index => 1,
372 p_msg_count => l_msg_count,
373 p_msg_data => l_msg_data,
374 p_data => l_data,
375 p_msg_index_out => l_msg_index_out);
376
377 pa_debug.write_file('Error : ' ||i||': '|| l_data);
378 IF i = FND_MSG_PUB.count_msg THEN
379 errbuf := l_data;
380 END IF;
381 END LOOP;
382
383
384 IF p_debug_mode = 'Y' THEN
385 pa_debug.write_file('START_PROCESS :' || l_data);
386 pa_debug.write('PA_PURGE_PUB.START_PROCESS',l_data,l_debug_level3);
387 pa_debug.reset_err_stack;
388 pa_debug.reset_curr_function;
389 END IF ;
390 -- No raise as per FD
391
392 WHEN OTHERS THEN
393 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 retcode := '-1';
395 errbuf := SUBSTRB(SQLERRM,1,240);
396
397 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_PURGE_PUB'
398 ,p_procedure_name => 'START_PROCESS'
399 ,p_error_text => errbuf);
400
401 FOR i in 1..FND_MSG_PUB.count_msg LOOP -- 5201806
402 PA_INTERFACE_UTILS_PUB.get_messages
403 (p_encoded => FND_API.G_FALSE,
404 p_msg_index => 1,
405 p_msg_count => l_msg_count,
406 p_msg_data => l_msg_data,
407 p_data => l_data,
408 p_msg_index_out => l_msg_index_out);
409
410 pa_debug.write_file('Error : ' ||i||': '|| l_data);
411 IF i = FND_MSG_PUB.count_msg THEN
412 errbuf := l_data;
413 END IF;
414 END LOOP;
415
416
417 IF p_debug_mode = 'Y' THEN
418 pa_debug.g_err_stage:='Unexpected Error'||errbuf;
419 pa_debug.write_file('START_PROCESS :' || pa_debug.g_err_stage);
420 pa_debug.write('PA_PURGE_PUB.START_PROCESS',pa_debug.g_err_stage,l_debug_level3);
421 pa_debug.reset_err_stack;
422 pa_debug.reset_curr_function;
423 END IF;
424
425 RAISE;
426
427 END START_PROCESS;
428
429 --
430 -- PROCEDURE
431 -- PURGE_FORECAST_ITEMS
432 -- PURPOSE
433 -- This API purges unused forecast item data from the 3 tables pa_forecast_items ,pa_forecast_item_details
434 -- and pa_fi_amount_details
435 --
436 -- Parameter Name In/Out Data Type Null? Default Value Description
437 -- ------------- ------ ---------- ------ ------------- ---------------------------------
438 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
439 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
440 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
441 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
442 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
443 --
444 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
445 -- in the message stack.
446 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
447 -- if only one error exists.
448
449 -- HISTORY
450 -- avaithia 01-March-2006 Created
451 --
452
453 PROCEDURE PURGE_FORECAST_ITEMS
454 (
455 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
456 p_commit_size IN NUMBER DEFAULT 10000 ,
457 p_request_id IN NUMBER ,
458 x_return_status OUT NOCOPY VARCHAR2 ,
459 x_msg_count OUT NOCOPY NUMBER ,
460 x_msg_data OUT NOCOPY VARCHAR2
461 )
462 IS
463
464 l_fi_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
465 l_fi_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
466 l_rows1 NUMBER :=0;
467 l_rows2 NUMBER :=0;
468 l_rows3 NUMBER :=0;
469 l_debug_level3 NUMBER := 3;
470 l_msg_data VARCHAR2(2000);
471 l_data VARCHAR2(2000);
472 l_msg_count NUMBER;
473 l_msg_index_out NUMBER;
474 i NUMBER;
475 rowexists INTEGER;
476 sql_command VARCHAR2(4000);
477 source_cursor INTEGER;
478 l_rows_returned NUMBER;
479 l_check_pji_summarized_flag VARCHAR2(1) := 'N';
480 l_util_summarized_Code_flag VARCHAR2(1) := 'N';
481 l_local_error_flag VARCHAR2(1) :='N'; -- 5201806
482
483
484 CURSOR c_get_forecast_item_ids IS
485 SELECT forecast_item_id, forecast_item_type
486 FROM pa_forecast_items
487 WHERE delete_flag='Y';
488
489 -- Commented because of GSCC Error File.Sql.47
490 -- CURSOR c_table_exists(l_table_name IN VARCHAR2) IS
491 -- SELECT 'Y'
492 -- FROM dba_objects
493 -- WHERE object_name= l_table_name AND OBJECT_TYPE = 'TABLE';
494 BEGIN
495
496 x_return_status := FND_API.G_RET_STS_SUCCESS ;
497 x_msg_count := 0;
498 x_msg_data := NULL;
499
500 IF p_debug_mode = 'Y' THEN
501 PA_DEBUG.set_curr_function( p_function => 'PURGE_FORECAST_ITEMS',
502 p_debug_mode => p_debug_mode);
503 pa_debug.g_err_stage:= 'Inside PURGE_FORECAST_ITEMS API' ;
504 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
505 END IF;
506
507 sql_command := 'SELECT 1 from dual where exists(SELECT NAME FROM PJI_SYSTEM_PARAMETERS)' ;
508
509 BEGIN
510
511 source_cursor := dbms_sql.open_cursor;
512 dbms_sql.parse(source_cursor,sql_command,dbms_sql.native);
513 rowexists := dbms_sql.execute(source_cursor);
514
515 -- Bug 5201806 : Start
516 l_rows_returned := DBMS_SQL.FETCH_ROWS(source_cursor);
517 IF nvl(l_rows_returned,0) > 0 THEN
518 rowexists := 1 ; -- Assigning 1 to indicate that rowexists
519 ELSE
520 rowexists := 0 ; -- No rows returned by cursor
521 END IF;
522 -- Bug 5201806 : End
523
524 dbms_sql.close_cursor(source_cursor);
525 EXCEPTION
526
527 WHEN OTHERS THEN
528 IF dbms_sql.is_open(source_cursor) THEN
529 dbms_sql.close_cursor(source_cursor);
530 END IF;
531 rowexists := 0;
532 END;
533
534 -- Commented below line for Bug 5175803
535 -- IF l_exists = 'Y' AND rowexists > 0 THEN
536
537 IF rowexists > 0 THEN -- Added for Bug 5175803
538 l_check_pji_summarized_flag := 'Y' ;
539 ELSE -- IF table doesnt exist or no data in this table ,then check only this flag
540 l_util_summarized_Code_flag := 'Y';
541 END IF;
542
543 OPEN c_get_forecast_item_ids;
544 LOOP
545 l_fi_tbl.delete; -- 5201806
546 l_fi_type_tbl.delete; -- 5201806
547 FETCH c_get_forecast_item_ids BULK COLLECT INTO l_fi_tbl,l_fi_type_tbl LIMIT p_commit_size ;
548 EXIT WHEN l_fi_tbl.COUNT <= 0 ;
549
550 -- Forecast Item Details Delete
551 BEGIN
552 -- If forecast_item_type is 'R' , no need to check for any other flag.
553 -- We can delete all the children and parent records.
554 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
555 DELETE FROM pa_forecast_item_details
556 WHERE forecast_item_id = l_fi_tbl(i)
557 AND l_fi_type_tbl(i)='R';
558
559 l_rows2 := l_rows2 + nvl(sql%rowcount,0); -- 5201806 : Using nvl in %rowcount
560 COMMIT;
561
562
563 -- IF forecast_item_type is not 'R' and pji_summarized_flag is checked.
564 IF l_check_pji_summarized_flag = 'Y' THEN
565 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
566 DELETE FROM pa_forecast_item_details
567 WHERE forecast_item_id = l_fi_tbl(i)
568 AND l_fi_type_tbl(i) <> 'R'
569 AND PJI_SUMMARIZED_FLAG in ('X','E');
570
571 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
572 COMMIT;
573
574 -- Delete all child records with ALL (NULL or Y) .Otherwise, ALL N values
575 -- Performance fix 5201806
576 -- Included a direct join between the inner and outer queries
577
578 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
579 DELETE FROM pa_forecast_item_details a
580 WHERE a.forecast_item_id = l_fi_tbl(i)
581 AND l_fi_type_tbl(i) <> 'R'
582 AND ( 'Y' = ALL(SELECT nvl(b.PJI_SUMMARIZED_FLAG,'Y') -- All records are NULL or Y
583 FROM pa_forecast_item_details b
584 WHERE b.forecast_item_id = a.forecast_item_id)
585 OR 'N' = ALL (SELECT nvl(c.PJI_SUMMARIZED_FLAG,'XYZ') -- Otherwise,All records should be N
586 FROM pa_forecast_item_details c
587 WHERE c.forecast_item_id = a.forecast_item_id)
588 ) ;
589
590 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
591 COMMIT;
592 END IF; -- End if pji_summarized_flag is being checked.
593
594
595 -- IF forecast_item_type is not 'R' and util_summarized_code is checked.
596 IF l_util_summarized_Code_flag = 'Y' THEN
597
598 -- delete all child records with UTIL_SUMMARIZED_CODE as 'X' and 'E'
599 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
600 DELETE FROM pa_forecast_item_details
601 WHERE forecast_item_id = l_fi_tbl(i)
602 AND l_fi_type_tbl(i) <> 'R'
603 AND UTIL_SUMMARIZED_CODE in ('X','E');
604
605 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
606 COMMIT;
607
608 -- Delete all child records with ALL (NULL or Y) .Otherwise, ALL N values
609 -- Performance fix 5201806
610 -- Included a direct join between the inner and outer queries
611
612 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
613 DELETE FROM pa_forecast_item_details a
614 where a.forecast_item_id = l_fi_tbl(i)
615 and l_fi_type_tbl(i) <> 'R'
616 and ( 'Y' = ALL(SELECT nvl(b.UTIL_SUMMARIZED_CODE,'Y')
617 FROM pa_forecast_item_details b
618 WHERE b.forecast_item_id = a.forecast_item_id)
619 OR 'N' = ALL (SELECT nvl(c.UTIL_SUMMARIZED_CODE,'XYZ')
620 FROM pa_forecast_item_details c
621 WHERE c.forecast_item_id = a.forecast_item_id)
622 ) ;
623
624 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
625 COMMIT;
626 END IF; -- End if util_summarized_code is being checked.
627
628 -- commit; -- 5201806 : Now commit is happening after each delete
629
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632 NULL ; -- Do nothing
633 WHEN OTHERS THEN
634 -- RAISE;
635 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
636 -- successfully deleted so far.
637 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
638 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
639 Fnd_Msg_Pub.add_exc_msg
640 ( p_pkg_name => 'PA_PURGE_PUB'
641 , p_procedure_name => 'PURGE_FORECAST_ITEMS'
642 , p_error_text => SUBSTRB(SQLERRM,1,240));
643 EXIT; -- 5201806 : exit the loop after discussion with Anders.
644 END;
645
646 -- FI Amount Details Delete
647 BEGIN
648 -- 5175803 Performance fix: Included a direct join between the inner and outer queries
649 -- 5201806 : Added l_fi_type_tbl join too. R type records are deleted from pa_forecast_item_details
650 -- without any conditions. So no need to check for their exostence.
651 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
652 DELETE FROM pa_fi_amount_details fi
653 WHERE fi.forecast_item_id = l_fi_tbl(i)
654 AND( (l_fi_type_tbl(i) = 'R')
655 OR (l_fi_type_tbl(i) <> 'R'
656 AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
657 WHERE dtl.forecast_item_id = fi.forecast_item_id)))
658 ;
659
660 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
661 COMMIT;
662
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 NULL ; -- Do nothing
666 WHEN OTHERS THEN
667 -- RAISE;
668 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
669 -- successfully deleted so far.
670 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
671 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
672 Fnd_Msg_Pub.add_exc_msg
673 ( p_pkg_name => 'PA_PURGE_PUB'
674 , p_procedure_name => 'PURGE_FORECAST_ITEMS'
675 , p_error_text => SUBSTRB(SQLERRM,1,240));
676 EXIT; -- 5201806 : exit the loop after discussion with Anders.
677 END;
678
679 -- Forecast Items Delete
680 BEGIN
681
682 -- 5175803 Performance fix
683 -- Included a direct join between the inner and outer queries
684 -- 5201806 : Added l_fi_type_tbl join too. R type records are deleted from pa_forecast_item_details
685 -- without any conditions. So no need to check for their exostence.
686 FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
687 DELETE FROM pa_forecast_items fi
688 WHERE fi.forecast_item_id = l_fi_tbl(i)
689 AND( (forecast_item_type = 'R')
690 OR (forecast_item_type <> 'R'
691 AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
692 WHERE dtl.forecast_item_id = fi.forecast_item_id)))
693 ;
694
695 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
696 COMMIT;
697
698 EXCEPTION
699 WHEN NO_DATA_FOUND THEN
700 NULL ; -- Do nothing
701 WHEN OTHERS THEN
702 -- RAISE;
703 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
704 -- successfully deleted so far.
705 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
706 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
707 Fnd_Msg_Pub.add_exc_msg
708 ( p_pkg_name => 'PA_PURGE_PUB'
709 , p_procedure_name => 'PURGE_FORECAST_ITEMS'
710 , p_error_text => SUBSTRB(SQLERRM,1,240));
711 EXIT; -- 5201806 : exit the loop after discussion with Anders.
712 END;
713 END LOOP;
714 CLOSE c_get_forecast_item_ids;
715
716 IF p_debug_mode = 'Y' THEN
717 pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_items - ' || l_rows1 ;
718 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
719 pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_item_details - ' || l_rows2;
720 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
721 pa_debug.g_err_stage:= 'No. of rows deleted from pa_fi_amount_details - ' ||l_rows3;
722 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
723 END IF;
724
725 -- Write this info into log table
726 PA_PURGE_PUB.INSERT_PURGE_LOG
727 (
728 p_request_id => p_request_id ,
729 p_table_name => 'PA_FORECAST_ITEMS' ,
730 p_rows_deleted => l_rows1 ,
731 x_return_status => x_return_status ,
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data
734 );
735
736 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737 --RAISE FND_API.G_EXC_ERROR;
738 l_local_error_flag := 'Y'; -- 5201806
739 END IF;
740
741
742 PA_PURGE_PUB.INSERT_PURGE_LOG
743 (
744 p_request_id => p_request_id ,
745 p_table_name => 'PA_FORECAST_ITEM_DETAILS',
746 p_rows_deleted => l_rows2,
747 x_return_status => x_return_status ,
748 x_msg_count => x_msg_count,
749 x_msg_data => x_msg_data
750 );
751
752 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
753 --RAISE FND_API.G_EXC_ERROR;
754 l_local_error_flag := 'Y'; -- 5201806
755 END IF;
756
757 PA_PURGE_PUB.INSERT_PURGE_LOG
758 (
759 p_request_id => p_request_id ,
760 p_table_name => 'PA_FI_AMOUNT_DETAILS',
761 p_rows_deleted => l_rows3,
762 x_return_status => x_return_status ,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data
765 );
766
767 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
768 --RAISE FND_API.G_EXC_ERROR;
769 l_local_error_flag := 'Y'; -- 5201806
770 END IF;
771
772 IF l_local_error_flag = 'Y' THEN -- 5201806
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775
776 IF p_debug_mode = 'Y' THEN
777 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_FI_AMOUNT_DETAILS';
778 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
779 pa_debug.g_err_stage:= 'Exiting PURGE_FORECAST_ITEMS';
780 pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_FORECAST_ITEMS :' || pa_debug.g_err_stage,l_debug_level3);
781 pa_debug.reset_curr_function;
782 END IF;
783
784 EXCEPTION
785 WHEN FND_API.G_EXC_ERROR THEN
786 x_return_status := Fnd_Api.G_RET_STS_ERROR;
787 x_msg_count := Fnd_Msg_Pub.count_msg; -- 5201806 Changed to x_msg_count
788
789 IF c_get_forecast_item_ids%ISOPEN THEN
790 CLOSE c_get_forecast_item_ids;
791 END IF;
792
793 -- 5201806 : Commented not needed...
794 -- IF l_msg_count >= 1 AND x_msg_data IS NULL
795 -- THEN
796 -- Pa_Interface_Utils_Pub.get_messages
797 -- ( p_encoded => Fnd_Api.G_TRUE
798 -- , p_msg_index => 1
799 -- , p_msg_count => l_msg_count
800 -- , p_msg_data => l_msg_data
801 -- , p_data => l_data
802 -- , p_msg_index_out => l_msg_index_out);
803 -- x_msg_data := l_data;
804 -- x_msg_count := l_msg_count;
805 -- ELSE
806 -- x_msg_count := l_msg_count;
807 -- END IF;
808
809 IF p_debug_mode = 'Y' THEN
810 Pa_Debug.reset_curr_function;
811 END IF;
812 WHEN OTHERS THEN
813 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
814 x_msg_count := 1;
815 x_msg_data := SUBSTRB(SQLERRM,1,240);
816
817 IF c_get_forecast_item_ids%ISOPEN THEN
818 CLOSE c_get_forecast_item_ids;
819 END IF;
820
821 Fnd_Msg_Pub.add_exc_msg
822 ( p_pkg_name => 'PA_PURGE_PUB'
823 , p_procedure_name => 'PURGE_FORECAST_ITEMS'
824 , p_error_text => x_msg_data);
825
826 x_msg_count := FND_MSG_PUB.count_msg; --5201806
827
828 IF p_debug_mode = 'Y' THEN
829 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
830 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
831 Pa_Debug.reset_curr_function;
832 END IF;
833 -- RAISE; 5201806 Do not raise in internal APIs
834 END PURGE_FORECAST_ITEMS ;
835
836 --
837 -- PROCEDURE
838 -- PURGE_PROJ_WORKFLOW
839 -- PURPOSE
840 -- This API purges unused denormalized workflow data from 3 tables pa_wf_processes , pa_wf_process_details
841 -- and pa_wf_ntf_performers
842 --
843 --
844 -- Parameter Name In/Out Data Type Null? Default Value Description
845 -- ------------- ------ ---------- ------ ------------- ---------------------------------
846 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
847 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
848 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
849 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
850 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
851 --
852 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
853 -- in the message stack.
854 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
855 -- if only one error exists.
856
857 -- HISTORY
858 -- avaithia 01-March-2006 Created
859 --
860
861 PROCEDURE PURGE_PROJ_WORKFLOW
862 (
863 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
864 p_commit_size IN NUMBER DEFAULT 10000 ,
865 p_request_id IN NUMBER ,
866 x_return_status OUT NOCOPY VARCHAR2 ,
867 x_msg_count OUT NOCOPY NUMBER ,
868 x_msg_data OUT NOCOPY VARCHAR2
869 )
870 IS
871 l_wf_item_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE ();
872 l_wf_item_key_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE ();
873 l_wf_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE ();
874 l_debug_level3 NUMBER := 3;
875 i NUMBER;
876 l_rows1 NUMBER := 0;
877 l_rows2 NUMBER := 0;
878 l_rows3 NUMBER := 0;
879 l_msg_data VARCHAR2(2000);
880 l_data VARCHAR2(2000);
881 l_msg_count NUMBER;
882 l_msg_index_out NUMBER;
883 l_local_error_flag VARCHAR2(1) :='N'; -- 5201806
884
885 CURSOR c_purge_wf_details IS
886 SELECT a.item_type, a.item_key, a.wf_type_code
887 FROM pa_wf_processes a
888 WHERE
889 a.item_type in ('PACANDID','PACOPR','PARADVWF','PARAPTEM','PARFIGEN','PAROVCNT','PAWFGPF','PAYPRJNT','PARMATRX','PAXWFHRU')
890 AND a.item_key NOT IN
891 (SELECT wi.item_key
892 FROM wf_items wi
893 WHERE wi.item_type IN ('PACANDID','PACOPR','PARADVWF','PARAPTEM','PARFIGEN','PAROVCNT','PAWFGPF','PAYPRJNT','PARMATRX','PAXWFHRU')
894 )
895 --bug#10238573 start
896 UNION
897 SELECT item_type, item_key, wf_type_code
898 FROM pa_wf_processes pwp
899 WHERE pwp.wf_type_code = 'ASSIGNMENT_APPROVAL'
900 and pwp.item_type = 'PAWFAAP'
901 and not exists
902 (select 'Y'
903 from pa_wf_ntf_performers ntf
904 where ntf.wf_type_code = 'ASSIGNMENT_APPROVAL'
905 and ntf.item_type = pwp.item_type
906 and ntf.item_key = pwp.item_key)
907 AND pwp.item_key NOT IN
908 (SELECT wi.item_key
909 FROM wf_items wi
910 WHERE wi.item_type = 'PAWFAAP')
911 --bug#10238573 end
912 ;
913
914 BEGIN
915 x_return_status := FND_API.G_RET_STS_SUCCESS ;
916 x_msg_count := 0;
917 x_msg_data := NULL;
918
919 IF p_debug_mode = 'Y' THEN
920 PA_DEBUG.set_curr_function( p_function => 'PURGE_PROJ_WORKFLOW', p_debug_mode => p_debug_mode);
921 pa_debug.g_err_stage:= 'Inside PURGE_PROJ_WORKFLOW API' ;
922 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
923 END IF;
924
925 OPEN c_purge_wf_details ;
926 LOOP
927 l_wf_item_type_tbl.delete; -- 5201806
928 l_wf_item_key_tbl.delete; -- 5201806
929 l_wf_type_code_tbl.delete; -- 5201806
930
931 FETCH c_purge_wf_details BULK COLLECT INTO l_wf_item_type_tbl,l_wf_item_key_tbl,l_wf_type_code_tbl LIMIT p_commit_size;
932 EXIT WHEN l_wf_item_type_tbl.COUNT <= 0;
933
934 -- Delete pa_wf_ntf_performers
935 BEGIN
936
937 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
938 DELETE FROM PA_WF_NTF_PERFORMERS
939 WHERE item_key = l_wf_item_key_tbl(i)
940 AND item_type = l_wf_item_type_tbl(i) ;
941 --bug#10238573
942 -- AND wf_type_code = l_wf_type_code_tbl(i);
943
944 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
945 COMMIT;
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 NULL ; -- Do nothing
949 WHEN OTHERS THEN
950 -- RAISE;
951 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
952 -- successfully deleted so far.
953 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
954 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
955 Fnd_Msg_Pub.add_exc_msg
956 ( p_pkg_name => 'PA_PURGE_PUB'
957 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
958 , p_error_text => SUBSTRB(SQLERRM,1,240));
959 EXIT; -- 5201806 : exit the loop after discussion with Anders.
960 END;
961
962 -- Delete pa_wf_process_details
963 BEGIN
964
965 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
966 DELETE FROM pa_wf_process_details
967 WHERE item_key=l_wf_item_key_tbl(i)
968 AND item_type = l_wf_item_type_tbl(i)
969 AND wf_type_code = l_wf_type_code_tbl(i) ;
970
971 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
972 COMMIT;
973 EXCEPTION
974 WHEN NO_DATA_FOUND THEN
975 NULL ; -- Do nothing
976 WHEN OTHERS THEN
977 -- RAISE;
978 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
979 -- successfully deleted so far.
980 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
981 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
982 Fnd_Msg_Pub.add_exc_msg
983 ( p_pkg_name => 'PA_PURGE_PUB'
984 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
985 , p_error_text => SUBSTRB(SQLERRM,1,240));
986 EXIT; -- 5201806 : exit the loop after discussion with Anders.
987 END;
988
989 -- Delete pa_wf_processes
990 BEGIN
991 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
992 DELETE FROM pa_wf_processes
993 WHERE item_key=l_wf_item_key_tbl(i)
994 AND item_type = l_wf_item_type_tbl(i)
995 AND wf_type_code = l_wf_type_code_tbl(i);
996
997 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
998 COMMIT;
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 NULL ; -- Do nothing
1002 WHEN OTHERS THEN
1003 -- RAISE;
1004 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
1005 -- successfully deleted so far.
1006 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1007 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
1008 Fnd_Msg_Pub.add_exc_msg
1009 ( p_pkg_name => 'PA_PURGE_PUB'
1010 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
1011 , p_error_text => SUBSTRB(SQLERRM,1,240));
1012 EXIT; -- 5201806 : exit the loop after discussion with Anders.
1013 END;
1014 END LOOP;
1015 CLOSE c_purge_wf_details;
1016
1017 IF p_debug_mode = 'Y' THEN
1018 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_processes ' || l_rows1 ;
1019 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1020 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_process_details ' || l_rows2 ;
1021 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1022 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_ntf_performers ' || l_rows3 ;
1023 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1024 END IF;
1025
1026
1027 PA_PURGE_PUB.INSERT_PURGE_LOG
1028 (
1029 p_request_id => p_request_id ,
1030 p_table_name => 'PA_WF_PROCESSES' ,
1031 p_rows_deleted => l_rows1 ,
1032 x_return_status => x_return_status ,
1033 x_msg_count => x_msg_count,
1034 x_msg_data => x_msg_data
1035 );
1036
1037 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1038 --RAISE FND_API.G_EXC_ERROR;
1039 l_local_error_flag := 'Y'; -- 5201806
1040 END IF;
1041
1042
1043 PA_PURGE_PUB.INSERT_PURGE_LOG
1044 (
1045 p_request_id => p_request_id ,
1046 p_table_name => 'PA_WF_PROCESS_DETAILS',
1047 p_rows_deleted => l_rows2,
1048 x_return_status => x_return_status ,
1049 x_msg_count => x_msg_count,
1050 x_msg_data => x_msg_data
1051 );
1052
1053 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 --RAISE FND_API.G_EXC_ERROR;
1055 l_local_error_flag := 'Y'; -- 5201806
1056 END IF;
1057
1058 PA_PURGE_PUB.INSERT_PURGE_LOG
1059 (
1060 p_request_id => p_request_id ,
1061 p_table_name => 'PA_WF_NTF_PERFORMERS',
1062 p_rows_deleted => l_rows3,
1063 x_return_status => x_return_status ,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data
1066 );
1067
1068 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1069 --RAISE FND_API.G_EXC_ERROR;
1070 l_local_error_flag := 'Y'; -- 5201806
1071 END IF;
1072
1073 IF l_local_error_flag = 'Y' THEN -- 5201806
1074 RAISE FND_API.G_EXC_ERROR;
1075 END IF;
1076
1077
1078 IF p_debug_mode = 'Y' THEN
1079 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_WF_NTF_PERFORMERS';
1080 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1081 pa_debug.g_err_stage:= 'Exiting PURGE_PROJ_WORKFLOW';
1082 pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_PROJ_WORKFLOW :' || pa_debug.g_err_stage,l_debug_level3);
1083 pa_debug.reset_curr_function;
1084 END IF;
1085
1086 EXCEPTION
1087 WHEN FND_API.G_EXC_ERROR THEN
1088 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1089 x_msg_count := Fnd_Msg_Pub.count_msg; -- 5201806 Changed to x_msg_count
1090
1091 IF c_purge_wf_details%ISOPEN THEN
1092 Close c_purge_wf_details;
1093 END IF;
1094
1095 -- 5201806 : Commented not needed...
1096 -- IF l_msg_count >= 1 AND x_msg_data IS NULL
1097 -- THEN
1098 -- Pa_Interface_Utils_Pub.get_messages
1099 -- ( p_encoded => Fnd_Api.G_TRUE
1100 -- , p_msg_index => 1
1101 -- , p_msg_count => l_msg_count
1102 -- , p_msg_data => l_msg_data
1103 -- , p_data => l_data
1104 -- , p_msg_index_out => l_msg_index_out);
1105 -- x_msg_data := l_data;
1106 -- x_msg_count := l_msg_count;
1107 -- ELSE
1108 -- x_msg_count := l_msg_count;
1109 -- END IF;
1110
1111 IF p_debug_mode = 'Y' THEN
1112 Pa_Debug.reset_curr_function;
1113 END IF;
1114
1115 WHEN OTHERS THEN
1116 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1117 x_msg_count := 1;
1118 x_msg_data := SUBSTRB(SQLERRM,1,240);
1119
1120 IF c_purge_wf_details%ISOPEN THEN
1121 Close c_purge_wf_details;
1122 END IF;
1123
1124 Fnd_Msg_Pub.add_exc_msg
1125 ( p_pkg_name => 'PA_PURGE_PUB'
1126 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
1127 , p_error_text => x_msg_data);
1128
1129 x_msg_count := FND_MSG_PUB.count_msg;--5201806
1130
1131 IF p_debug_mode = 'Y' THEN
1132 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1133 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1134 Pa_Debug.reset_curr_function;
1135 END IF;
1136 -- RAISE; 5201806 Do not raise in internal APIs
1137 END PURGE_PROJ_WORKFLOW;
1138
1139 --
1140 -- PROCEDURE
1141 -- PURGE_REPORTING_EXCEPTIONS
1142 -- PURPOSE
1143 -- This API will purge unused reporting exception data from pa_reporting_exceptions
1144 --
1145 -- Parameter Name In/Out Data Type Null? Default Value Description
1146 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1147 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1148 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1149 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1150 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1151 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1152 --
1153 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1154 -- in the message stack.
1155 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1156 -- if only one error exists.
1157
1158 -- HISTORY
1159 -- avaithia 01-March-2006 Created
1160 --
1161
1162 PROCEDURE PURGE_REPORTING_EXCEPTIONS
1163 (
1164 p_debug_mode IN VARCHAR2 default 'N' ,
1165 p_commit_size IN NUMBER default 10000 ,
1166 p_request_id IN NUMBER ,
1167 x_return_status OUT NOCOPY VARCHAR2 ,
1168 x_msg_count OUT NOCOPY NUMBER ,
1169 x_msg_data OUT NOCOPY VARCHAR2
1170 )
1171 IS
1172 l_request_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE ();
1173 l_debug_level3 NUMBER :=3;
1174 l_rows1 NUMBER :=0;
1175 l_msg_data VARCHAR2(2000);
1176 l_data VARCHAR2(2000);
1177 l_msg_count NUMBER;
1178 l_msg_index_out NUMBER;
1179 i NUMBER;
1180 l_local_error_flag VARCHAR2(1) :='N'; -- 5201806
1181
1182 CURSOR c_get_request_id IS
1183 SELECT request_id
1184 FROM pa_reporting_exceptions
1185 WHERE request_id not in
1186 (SELECT request_id
1187 FROM fnd_concurrent_requests)
1188 AND nvl(request_id,0) > 0;
1189
1190 --Technically if the pa_reporting_exceptions.request_id does not exist in fnd_concurrent_requests.request_id,
1191 --then the record is eligible to be purged. Note - the code must exclude request_id < 0 as the
1192 --mass assignment flows populate this table using request_id = -1.
1193
1194 BEGIN
1195
1196 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1197 x_msg_count := 0;
1198 x_msg_data := NULL;
1199
1200 IF p_debug_mode = 'Y' THEN
1201 PA_DEBUG.set_curr_function( p_function => 'PURGE_REPORTING_EXCEPTIONS', p_debug_mode => p_debug_mode);
1202 pa_debug.g_err_stage:= 'Entering PURGE_REPORTING_EXCEPTIONS API';
1203 Pa_Debug.WRITE('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1204 END IF;
1205
1206 OPEN c_get_request_id;
1207 LOOP
1208 l_request_id_tbl.delete; -- 5201806
1209 FETCH c_get_request_id BULK COLLECT INTO l_request_id_tbl LIMIT p_commit_size ;
1210 EXIT WHEN l_request_id_tbl.COUNT <= 0 ;
1211
1212 BEGIN
1213 FORALL i IN l_request_id_tbl.FIRST..l_request_id_tbl.LAST
1214 DELETE FROM pa_reporting_exceptions
1215 WHERE request_id = l_request_id_tbl (i);
1216
1217 l_rows1 :=l_rows1+nvl(sql%rowcount,0);
1218 COMMIT;
1219 EXCEPTION
1220 WHEN NO_DATA_FOUND THEN
1221 NULL ; -- Do nothing
1222 WHEN OTHERS THEN
1223 -- RAISE;
1224 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
1225 -- successfully deleted so far.
1226 l_rows1 :=l_rows1+nvl(sql%rowcount,0);
1227 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
1228 Fnd_Msg_Pub.add_exc_msg
1229 ( p_pkg_name => 'PA_PURGE_PUB'
1230 , p_procedure_name => 'PURGE_REPORTING_EXCEPTIONS'
1231 , p_error_text => SUBSTRB(SQLERRM,1,240));
1232 EXIT; -- 5201806 : exit the loop after discussion with Anders.
1233 END;
1234 END LOOP;
1235 CLOSE c_get_request_id;
1236
1237 IF p_debug_mode = 'Y' THEN
1238 pa_debug.g_err_stage:= 'No. of rows deleted from pa_reporting_exceptions ' || l_rows1 ;
1239 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1240 END IF;
1241
1242
1243 PA_PURGE_PUB.INSERT_PURGE_LOG
1244 (
1245 p_request_id => p_request_id ,
1246 p_table_name => 'PA_REPORTING_EXCEPTIONS' ,
1247 p_rows_deleted => l_rows1 ,
1248 x_return_status => x_return_status ,
1249 x_msg_count => x_msg_count,
1250 x_msg_data => x_msg_data
1251 );
1252
1253 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1254 --RAISE FND_API.G_EXC_ERROR;
1255 l_local_error_flag := 'Y'; -- 5201806
1256 END IF;
1257
1258 IF l_local_error_flag = 'Y' THEN -- 5201806
1259 RAISE FND_API.G_EXC_ERROR;
1260 END IF;
1261
1262 IF p_debug_mode = 'Y' THEN
1263 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to pa_reporting_exceptions';
1264 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1265 pa_debug.g_err_stage:= 'Exiting PURGE_REPORTING_EXCEPTIONS';
1266 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1267 Pa_Debug.reset_curr_function;
1268 END IF;
1269 EXCEPTION
1270 WHEN FND_API.G_EXC_ERROR THEN
1271 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1272 x_msg_count := Fnd_Msg_Pub.count_msg; -- 5201806 Changed to x_msg_count
1273
1274 IF c_get_request_id%ISOPEN THEN
1275 CLOSE c_get_request_id;
1276 END IF;
1277
1278 -- 5201806 : Commented not needed...
1279 -- IF l_msg_count >= 1 AND x_msg_data IS NULL
1280 -- THEN
1281 -- Pa_Interface_Utils_Pub.get_messages
1282 -- ( p_encoded => Fnd_Api.G_TRUE
1283 -- , p_msg_index => 1
1284 -- , p_msg_count => l_msg_count
1285 -- , p_msg_data => l_msg_data
1286 -- , p_data => l_data
1287 -- , p_msg_index_out => l_msg_index_out);
1288 --
1289 -- x_msg_data := l_data;
1290 -- x_msg_count := l_msg_count;
1291 -- ELSE
1292 -- x_msg_count := l_msg_count;
1293 -- END IF;
1294
1295 IF p_debug_mode = 'Y' THEN
1296 Pa_Debug.reset_curr_function;
1297 END IF;
1298 WHEN OTHERS THEN
1299 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1300 x_msg_count := 1;
1301 x_msg_data := SUBSTRB(SQLERRM,1,240);
1302
1303 IF c_get_request_id%ISOPEN THEN
1304 CLOSE c_get_request_id;
1305 END IF;
1306
1307 Fnd_Msg_Pub.add_exc_msg
1308 ( p_pkg_name => 'PA_PURGE_PUB'
1309 , p_procedure_name => 'PURGE_REPORTING_EXCEPTIONS'
1310 , p_error_text => x_msg_data);
1311
1312 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1313
1314 IF p_debug_mode = 'Y' THEN
1315 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1316 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1317 Pa_Debug.reset_curr_function;
1318 END IF;
1319 -- RAISE; 5201806 Do not raise in internal APIs
1320 END PURGE_REPORTING_EXCEPTIONS;
1321
1322 --
1323 -- PROCEDURE
1324 -- PURGE_ORG_AUTHORITY
1325 -- PURPOSE
1326 -- This API purges organization authority records of all terminated employees or contingent
1327 -- workers whose termination date is earlier than system date
1328 --
1329 --
1330 -- Parameter Name In/Out Data Type Null? Default Value Description
1331 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1332 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1333 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1334 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1335 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1336 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1337 --
1338 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1339 -- in the message stack.
1340 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1341 -- if only one error exists.
1342
1343 -- HISTORY
1344 -- bifernan 16-October-2007 Created
1345 -- bifernan 05-February-2007 Bug 6749278: Performance changes.
1346 -- Added code to log number of grants deleted.
1347 --
1348
1349 PROCEDURE PURGE_ORG_AUTHORITY
1350 (
1351 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
1352 p_commit_size IN NUMBER DEFAULT 10000 ,
1353 p_request_id IN NUMBER ,
1354 x_return_status OUT NOCOPY VARCHAR2 ,
1355 x_msg_count OUT NOCOPY NUMBER ,
1356 x_msg_data OUT NOCOPY VARCHAR2
1357 )
1358 IS
1359
1360 l_person_id per_all_people_f.person_id%TYPE;
1361 l_orginzation_id NUMBER;
1362 l_menu_name fnd_menus.menu_name%TYPE;
1363 l_debug_level3 NUMBER := 3;
1364 i NUMBER;
1365 l_local_error_flag VARCHAR2(1) :='N';
1366 l_grants_deleted NUMBER := 0;
1367 l_return_status VARCHAR2(30);
1368
1369 CURSOR c_purge_org_authority IS
1370 SELECT DISTINCT per.person_id,
1371 (to_number(fg.instance_pk1_value)) organization_id,
1372 fm.menu_name
1373 FROM fnd_grants fg,
1374 fnd_objects fo,
1375 fnd_menus fm,
1376 per_all_people_f per,
1377 wf_roles wfr
1378 WHERE per.person_id IN ( SELECT person_id
1379 FROM per_periods_of_service ppos
1380 WHERE ppos.person_id = per.person_id
1381 AND ppos.actual_termination_date is not null
1382 AND NOT EXISTS (SELECT 1
1383 FROM per_periods_of_service
1384 WHERE person_id = ppos.person_id
1385 AND actual_termination_date IS NULL )
1386 GROUP BY person_id
1387 HAVING MAX(actual_termination_date) < SYSDATE
1388 UNION
1389 SELECT person_id
1390 FROM per_periods_of_placement ppop
1391 WHERE ppop.person_id = per.person_id
1392 AND ppop.actual_termination_date is not null
1393 AND NOT EXISTS (SELECT 1
1394 FROM per_periods_of_placement
1395 WHERE person_id = ppop.person_id
1396 AND actual_termination_date IS NULL )
1397 GROUP BY person_id
1398 HAVING MAX(actual_termination_date) < SYSDATE )
1399 AND fg.object_id = fo.object_id
1400 AND fo.obj_name = 'ORGANIZATION'
1401 AND fg.instance_type = 'INSTANCE'
1402 AND fg.instance_pk1_value is not null
1403 AND fg.grantee_key = wfr.NAME
1404 AND fg.grantee_type = 'USER'
1405 AND fg.instance_set_id is null
1406 AND wfr.orig_system = 'HZ_PARTY'
1407 AND per.party_id = wfr.orig_system_id
1408 AND fg.menu_id = fm.menu_id
1409 AND (TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date)
1410 AND fm.menu_name IN ('PA_PRM_RES_AUTH', 'PA_PRM_PROJ_AUTH', 'PA_PRM_RES_PRMRY_CONTACT', 'PA_PRM_UTL_AUTH');
1411
1412 BEGIN
1413 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1414 x_msg_count := 0;
1415 x_msg_data := NULL;
1416
1417 IF p_debug_mode = 'Y' THEN
1418 PA_DEBUG.set_curr_function( p_function => 'PURGE_ORG_AUTHORITY', p_debug_mode => p_debug_mode);
1419 pa_debug.g_err_stage:= 'Inside PURGE_ORG_AUTHORITY API' ;
1420 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1421 END IF;
1422
1423 OPEN c_purge_org_authority;
1424 LOOP
1425 l_person_id := -1;
1426 l_orginzation_id := -1;
1427
1428 FETCH c_purge_org_authority INTO l_person_id, l_orginzation_id, l_menu_name;
1429 EXIT WHEN c_purge_org_authority%NOTFOUND;
1430
1431 BEGIN
1432 -- Delete Resource authority
1433 IF l_menu_name = 'PA_PRM_RES_AUTH' THEN
1434 pa_resource_utils.delete_grant( p_person_id => l_person_id
1435 ,p_org_id => l_orginzation_id
1436 ,p_role_name => 'PA_PRM_RES_AUTH'
1437 ,x_return_status => l_return_status);
1438 -- Delete project authority
1439 ELSIF l_menu_name = 'PA_PRM_PROJ_AUTH' THEN
1440 pa_resource_utils.delete_grant( p_person_id => l_person_id
1441 ,p_org_id => l_orginzation_id
1442 ,p_role_name => 'PA_PRM_PROJ_AUTH'
1443 ,x_return_status => l_return_status);
1444 -- Delete primary contact
1445 ELSIF l_menu_name = 'PA_PRM_RES_PRMRY_CONTACT' THEN
1446 pa_resource_utils.delete_grant( p_person_id => l_person_id
1447 ,p_org_id => l_orginzation_id
1448 ,p_role_name => 'PA_PRM_RES_PRMRY_CONTACT'
1449 ,x_return_status => l_return_status);
1450 -- Delete utilization authority
1451 ELSIF l_menu_name = 'PA_PRM_UTL_AUTH' THEN
1452 pa_resource_utils.delete_grant( p_person_id => l_person_id
1453 ,p_org_id => l_orginzation_id
1454 ,p_role_name => 'PA_PRM_UTL_AUTH'
1455 ,x_return_status => l_return_status);
1456 END IF;
1457
1458 IF (l_return_status IS NULL OR l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1459 l_grants_deleted := l_grants_deleted + 1;
1460 END IF;
1461
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 NULL ; -- Do nothing
1465 WHEN OTHERS THEN
1466 l_local_error_flag := 'Y';
1467 Fnd_Msg_Pub.add_exc_msg
1468 ( p_pkg_name => 'PA_PURGE_PUB'
1469 , p_procedure_name => 'PURGE_ORG_AUTHORITY'
1470 , p_error_text => SUBSTRB(SQLERRM,1,240));
1471 EXIT;
1472 END;
1473
1474 END LOOP;
1475 CLOSE c_purge_org_authority;
1476
1477 IF p_debug_mode = 'Y' THEN
1478 pa_debug.g_err_stage:= 'No. of grants deleted ' || l_grants_deleted;
1479 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1480 END IF;
1481
1482 PA_PURGE_PUB.INSERT_PURGE_LOG
1483 (
1484 p_request_id => p_request_id ,
1485 p_table_name => 'FND_GRANTS' ,
1486 p_rows_deleted => l_grants_deleted ,
1487 x_return_status => x_return_status ,
1488 x_msg_count => x_msg_count,
1489 x_msg_data => x_msg_data
1490 );
1491
1492 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1493 l_local_error_flag := 'Y';
1494 END IF;
1495
1496 IF l_local_error_flag = 'Y' THEN
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499
1500 IF p_debug_mode = 'Y' THEN
1501 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_ORG_AUTHORITY';
1502 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1503 pa_debug.g_err_stage:= 'Exiting PURGE_ORG_AUTHORITY';
1504 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1505 Pa_Debug.reset_curr_function;
1506 END IF;
1507
1508 EXCEPTION
1509 WHEN FND_API.G_EXC_ERROR THEN
1510
1511 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1512 x_msg_count := Fnd_Msg_Pub.count_msg;
1513
1514 IF c_purge_org_authority%ISOPEN THEN
1515 CLOSE c_purge_org_authority;
1516 END IF;
1517
1518 IF p_debug_mode = 'Y' THEN
1519 Pa_Debug.reset_curr_function;
1520 END IF;
1521
1522 WHEN OTHERS THEN
1523
1524 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1525 x_msg_count := 1;
1526 x_msg_data := SUBSTRB(SQLERRM,1,240);
1527
1528 IF c_purge_org_authority%ISOPEN THEN
1529 CLOSE c_purge_org_authority;
1530 END IF;
1531
1532 Fnd_Msg_Pub.add_exc_msg
1533 ( p_pkg_name => 'PA_PURGE_PUB'
1534 , p_procedure_name => 'PURGE_ORG_AUTHORITY'
1535 , p_error_text => x_msg_data);
1536
1537 x_msg_count := FND_MSG_PUB.count_msg;
1538
1539 IF p_debug_mode = 'Y' THEN
1540 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1541 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1542 Pa_Debug.reset_curr_function;
1543 END IF;
1544
1545 END PURGE_ORG_AUTHORITY;
1546
1547 --
1548 -- PROCEDURE
1549 -- PURGE_PJI_DEBUG
1550 -- PURPOSE
1551 -- This API purges the tables used by project performance summarization model to store
1552 -- debug information.
1553 --
1554 -- Parameter Name In/Out Data Type Null? Default Value Description
1555 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1556 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1557 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1558 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1559 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1560 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1561 --
1562 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1563 -- in the message stack.
1564 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1565 -- if only one error exists.
1566
1567 -- HISTORY
1568 -- bifernan 16-October-2007 Created
1569 -- bifernan 01-January-2008 Prefixed PJI tables with schema name for truncate statement
1570 -- bifernan 11-January-2008 Removed references to hard coded schemas names (GSCC)
1571 -- Replaced truncate statement with batch delete
1572 --
1573
1574 PROCEDURE PURGE_PJI_DEBUG
1575 (
1576 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
1577 p_commit_size IN NUMBER DEFAULT 10000 ,
1578 p_request_id IN NUMBER ,
1579 x_return_status OUT NOCOPY VARCHAR2 ,
1580 x_msg_count OUT NOCOPY NUMBER ,
1581 x_msg_data OUT NOCOPY VARCHAR2
1582 )
1583 IS
1584 l_debug_level3 NUMBER :=3;
1585 l_rows1 NUMBER :=0;
1586 l_rows2 NUMBER :=0;
1587 l_rows3 NUMBER :=0;
1588 l_msg_data VARCHAR2(2000);
1589 l_data VARCHAR2(2000);
1590 l_msg_count NUMBER;
1591 l_msg_index_out NUMBER;
1592 i NUMBER;
1593 l_local_error_flag VARCHAR2(1) :='N';
1594
1595 BEGIN
1596
1597 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1598 x_msg_count := 0;
1599 x_msg_data := NULL;
1600
1601 IF p_debug_mode = 'Y' THEN
1602 PA_DEBUG.set_curr_function( p_function => 'PURGE_PJI_DEBUG', p_debug_mode => p_debug_mode);
1603 pa_debug.g_err_stage:= 'Entering PURGE_PJI_DEBUG API';
1604 Pa_Debug.WRITE('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1605 END IF;
1606
1607 -- PJI_FM_EXTR_PLAN_LINES_DEBUG
1608 BEGIN
1609 LOOP
1610 DELETE FROM PJI_FM_EXTR_PLAN_LINES_DEBUG WHERE ROWNUM <= p_commit_size;
1611 EXIT WHEN sql%rowcount < p_commit_size;
1612
1613 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1614 COMMIT; -- Clear rollback segment
1615 END LOOP;
1616
1617 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1618 COMMIT;
1619 EXCEPTION
1620 WHEN NO_DATA_FOUND THEN
1621 NULL ; -- Do nothing
1622 WHEN OTHERS THEN
1623 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1624 l_local_error_flag := 'Y';
1625 Fnd_Msg_Pub.add_exc_msg
1626 ( p_pkg_name => 'PA_PURGE_PUB'
1627 , p_procedure_name => 'PURGE_PJI_DEBUG'
1628 , p_error_text => SUBSTRB(SQLERRM,1,240));
1629 END;
1630
1631 -- PJI_FM_XBS_ACCUM_TMP1_DEBUG
1632 BEGIN
1633 LOOP
1634 DELETE FROM PJI_FM_XBS_ACCUM_TMP1_DEBUG WHERE ROWNUM <= p_commit_size;
1635 EXIT WHEN sql%rowcount < p_commit_size;
1636
1637 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1638 COMMIT; -- Clear rollback segment
1639 END LOOP;
1640
1641 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1642 COMMIT;
1643 EXCEPTION
1644 WHEN NO_DATA_FOUND THEN
1645 NULL ; -- Do nothing
1646 WHEN OTHERS THEN
1647 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1648 l_local_error_flag := 'Y';
1649 Fnd_Msg_Pub.add_exc_msg
1650 ( p_pkg_name => 'PA_PURGE_PUB'
1651 , p_procedure_name => 'PURGE_PJI_DEBUG'
1652 , p_error_text => SUBSTRB(SQLERRM,1,240));
1653 END;
1654
1655 -- PJI_SYSTEM_DEBUG_MSG
1656 BEGIN
1657 LOOP
1658 DELETE FROM PJI_SYSTEM_DEBUG_MSG WHERE ROWNUM <= p_commit_size;
1659 EXIT WHEN sql%rowcount < p_commit_size;
1660
1661 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1662 COMMIT; -- Clear rollback segment
1663 END LOOP;
1664
1665 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1666 COMMIT;
1667 EXCEPTION
1668 WHEN NO_DATA_FOUND THEN
1669 NULL ; -- Do nothing
1670 WHEN OTHERS THEN
1671 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1672 l_local_error_flag := 'Y';
1673 Fnd_Msg_Pub.add_exc_msg
1674 ( p_pkg_name => 'PA_PURGE_PUB'
1675 , p_procedure_name => 'PURGE_PJI_DEBUG'
1676 , p_error_text => SUBSTRB(SQLERRM,1,240));
1677 END;
1678
1679 IF p_debug_mode = 'Y' THEN
1680 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_EXTR_PLAN_LINES_DEBUG ' || l_rows1 ;
1681 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1682 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_XBS_ACCUM_TMP1_DEBUG ' || l_rows2 ;
1683 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1684 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_SYSTEM_DEBUG_MSG ' || l_rows3 ;
1685 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1686 END IF;
1687
1688 PA_PURGE_PUB.INSERT_PURGE_LOG
1689 (
1690 p_request_id => p_request_id ,
1691 p_table_name => 'PJI_FM_EXTR_PLAN_LINES_DEBUG' ,
1692 p_rows_deleted => l_rows1 ,
1693 x_return_status => x_return_status ,
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data
1696 );
1697
1698 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1699 l_local_error_flag := 'Y';
1700 END IF;
1701
1702 PA_PURGE_PUB.INSERT_PURGE_LOG
1703 (
1704 p_request_id => p_request_id ,
1705 p_table_name => 'PJI_FM_XBS_ACCUM_TMP1_DEBUG',
1706 p_rows_deleted => l_rows2,
1707 x_return_status => x_return_status ,
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data
1710 );
1711
1712 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1713 l_local_error_flag := 'Y';
1714 END IF;
1715
1716 PA_PURGE_PUB.INSERT_PURGE_LOG
1717 (
1718 p_request_id => p_request_id ,
1719 p_table_name => 'PJI_SYSTEM_DEBUG_MSG',
1720 p_rows_deleted => l_rows3,
1721 x_return_status => x_return_status ,
1722 x_msg_count => x_msg_count,
1723 x_msg_data => x_msg_data
1724 );
1725
1726 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1727 l_local_error_flag := 'Y';
1728 END IF;
1729
1730 IF l_local_error_flag = 'Y' THEN
1731 RAISE FND_API.G_EXC_ERROR;
1732 END IF;
1733
1734
1735 IF p_debug_mode = 'Y' THEN
1736 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_PJI_DEBUG';
1737 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1738 pa_debug.g_err_stage:= 'Exiting PURGE_PJI_DEBUG';
1739 pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_PJI_DEBUG :' || pa_debug.g_err_stage,l_debug_level3);
1740 pa_debug.reset_curr_function;
1741 END IF;
1742
1743 EXCEPTION
1744 WHEN FND_API.G_EXC_ERROR THEN
1745 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1746 x_msg_count := Fnd_Msg_Pub.count_msg;
1747
1748 IF p_debug_mode = 'Y' THEN
1749 Pa_Debug.reset_curr_function;
1750 END IF;
1751
1752 WHEN OTHERS THEN
1753 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1754 x_msg_count := 1;
1755 x_msg_data := SUBSTRB(SQLERRM,1,240);
1756
1757 Fnd_Msg_Pub.add_exc_msg
1758 ( p_pkg_name => 'PA_PURGE_PUB'
1759 , p_procedure_name => 'PURGE_PJI_DEBUG'
1760 , p_error_text => x_msg_data);
1761
1762 x_msg_count := FND_MSG_PUB.count_msg;
1763
1764 IF p_debug_mode = 'Y' THEN
1765 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1766 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1767 Pa_Debug.reset_curr_function;
1768 END IF;
1769
1770 END PURGE_PJI_DEBUG;
1771
1772 --
1773 -- PROCEDURE
1774 -- PRINT_OUTPUT_REPORT
1775 -- PURPOSE
1776 -- This API will print the output report to concurrent log file.
1777 -- Parameter Name In/Out Data Type Null? Default Value Description
1778 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1779 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1780 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1781 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1782 --
1783 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1784 -- in the message stack.
1785 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1786 -- if only one error exists.
1787
1788 -- HISTORY
1789 -- avaithia 01-March-2006 Created
1790 --
1791
1792 PROCEDURE PRINT_OUTPUT_REPORT
1793 (
1794 p_request_id IN NUMBER ,
1795 x_return_status OUT NOCOPY VARCHAR2 ,
1796 x_msg_count OUT NOCOPY NUMBER ,
1797 x_msg_data OUT NOCOPY VARCHAR2
1798 )
1799 IS
1800
1801 CURSOR c_purge_details IS
1802 SELECT table_name, num_recs_purged
1803 FROM PA_PURGE_PRJ_DETAILS
1804 WHERE purge_batch_id = p_request_id
1805 AND project_id = 0
1806 ORDER BY table_name ;
1807
1808 l_table_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1809 l_rows_deleted_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1810 l_debug_mode VARCHAR2(1); -- 5201806
1811
1812 BEGIN
1813
1814 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1815 x_msg_count := 0;
1816 x_msg_data := NULL;
1817 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N'); -- 5201806
1818
1819 pa_debug.set_process('PLSQL','LOG','Y');
1820 pa_debug.set_curr_function( p_function => 'PRINT_OUTPUT_REPORT', p_debug_mode => 'Y');
1821
1822 pa_debug.g_err_stage:= '---------------------------------------------------';
1823 pa_debug.write_file(pa_debug.g_err_stage);
1824
1825 pa_debug.g_err_stage:= 'Purge Process Report : ADM: Purge Projects Obsolete Data ';
1826 pa_debug.write_file(pa_debug.g_err_stage);
1827
1828 pa_debug.g_err_stage:= '+---------------------------------------------------+';
1829 pa_debug.write_file(pa_debug.g_err_stage);
1830
1831 pa_debug.g_err_stage:= 'Current system time is ' || sysdate ;
1832 pa_debug.write_file(pa_debug.g_err_stage);
1833
1834 pa_debug.g_err_stage:= '+---------------------------------------------------+';
1835 pa_debug.write_file(pa_debug.g_err_stage);
1836
1837 OPEN c_purge_details;
1838 FETCH c_purge_details BULK COLLECT INTO l_table_name_tbl,l_rows_deleted_tbl;
1839 CLOSE c_purge_details;
1840
1841 IF nvl(l_table_name_tbl.LAST,0) > 0 THEN
1842 FOR i IN l_table_name_tbl.FIRST..l_table_name_tbl.LAST LOOP
1843 pa_debug.g_err_stage:= 'Purged '|| l_rows_deleted_tbl(i) ||' entries from ' || l_table_name_tbl(i) ;
1844 pa_debug.write_file(pa_debug.g_err_stage);
1845 pa_debug.g_err_stage:= ' ';
1846 pa_debug.write_file(pa_debug.g_err_stage);
1847 END LOOP ;
1848 END IF;
1849
1850 Pa_Debug.reset_curr_function;
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1855 x_msg_count := 1;
1856 x_msg_data := SUBSTRB(SQLERRM,1,240);
1857
1858 IF c_purge_details%ISOPEN THEN
1859 CLOSE c_purge_details ;
1860 END IF;
1861
1862 Fnd_Msg_Pub.add_exc_msg
1863 ( p_pkg_name => 'PA_PURGE_PUB'
1864 , p_procedure_name => 'PRINT_OUTPUT_REPORT'
1865 , p_error_text => x_msg_data);
1866
1867 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1868
1869 IF l_debug_mode = 'Y' THEN -- 5201806
1870 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1871 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1872 Pa_Debug.reset_curr_function;
1873 END IF;
1874 -- RAISE; 5201806 Do not raise in internal APIs
1875 END PRINT_OUTPUT_REPORT;
1876
1877 --
1878 -- PROCEDURE
1879 -- INSERT_PURGE_LOG
1880 -- PURPOSE
1881 -- This API will populate the log table for deleted table information.
1882 --
1883 -- Parameter Name In/Out Data Type Null? Default Value Description
1884 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1885 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1886 -- P_TABLE_NAME IN VARCHAR2 NOT NULL Indicates the table name deleted.
1887 -- P_ROWS_DELETED IN NUMBER NOT NULL Indicates the number of rows deleted.
1888 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1889 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1890 --
1891 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1892 -- in the message stack.
1893 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1894 -- if only one error exists.
1895
1896 -- HISTORY
1897 -- avaithia 01-March-2006 Created
1898 --
1899
1900 PROCEDURE INSERT_PURGE_LOG
1901 (
1902 p_request_id IN NUMBER ,
1903 p_table_name IN VARCHAR2 ,
1904 p_rows_deleted IN NUMBER ,
1905 x_return_status OUT NOCOPY VARCHAR2 ,
1906 x_msg_count OUT NOCOPY NUMBER ,
1907 x_msg_data OUT NOCOPY VARCHAR2
1908 )
1909 IS
1910 l_debug_mode VARCHAR2(1);
1911 l_debug_level3 NUMBER := 3;
1912 BEGIN
1913
1914 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1915 x_msg_count := 0;
1916 x_msg_data := NULL;
1917
1918 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1919
1920 IF l_debug_mode = 'Y' THEN
1921 pa_debug.set_curr_function( p_function => 'INSERT_PURGE_LOG', p_debug_mode => 'Y');
1922 Pa_Debug.WRITE('PA_PURGE_PUB','Before inserting into PA_PURGE_PRJ_DETAILS',l_debug_level3);
1923 END IF;
1924
1925 INSERT INTO PA_PURGE_PRJ_DETAILS
1926 (
1927 PURGE_BATCH_ID
1928 ,PROJECT_ID
1929 ,TABLE_NAME
1930 ,NUM_RECS_PURGED
1931 ,CREATED_BY
1932 ,LAST_UPDATE_DATE
1933 ,LAST_UPDATED_BY
1934 ,LAST_UPDATE_LOGIN
1935 ,CREATION_DATE
1936 ,PROGRAM_APPLICATION_ID
1937 ,PROGRAM_ID
1938 ,PROGRAM_UPDATE_DATE
1939 )
1940 VALUES
1941 (
1942 p_request_id
1943 ,0
1944 ,p_table_name
1945 ,p_rows_deleted
1946 ,fnd_global.user_id
1947 ,sysdate
1948 ,fnd_global.user_id
1949 ,fnd_global.login_id
1950 ,sysdate
1951 ,fnd_global.prog_appl_id
1952 ,fnd_global.conc_program_id
1953 ,sysdate
1954 );
1955
1956 IF l_debug_mode = 'Y' THEN
1957 Pa_Debug.g_err_stage:='Successfully Inserted Purge Log';
1958 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage,l_debug_level3);
1959 pa_debug.reset_curr_function;
1960 END IF;
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1965 x_msg_count := 1;
1966 x_msg_data := SUBSTRB(SQLERRM,1,240);
1967
1968 Fnd_Msg_Pub.add_exc_msg
1969 ( p_pkg_name => 'PA_PURGE_PUB'
1970 , p_procedure_name => 'INSERT_PURGE_LOG'
1971 , p_error_text => x_msg_data);
1972
1973 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1974
1975 IF l_debug_mode = 'Y' THEN
1976 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1977 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1978 Pa_Debug.reset_curr_function;
1979 END IF;
1980 -- RAISE; 5201806 Do not raise in internal APIs
1981 END INSERT_PURGE_LOG;
1982
1983 END PA_PURGE_PUB;