[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_PUB
Source
1 PACKAGE BODY PA_PURGE_PUB AS
2 -- $Header: PAXPURGB.pls 120.5 2008/02/27 18:00:34 bifernan noship $
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')
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')
894 );
895
896 BEGIN
897 x_return_status := FND_API.G_RET_STS_SUCCESS ;
898 x_msg_count := 0;
899 x_msg_data := NULL;
900
901 IF p_debug_mode = 'Y' THEN
902 PA_DEBUG.set_curr_function( p_function => 'PURGE_PROJ_WORKFLOW', p_debug_mode => p_debug_mode);
903 pa_debug.g_err_stage:= 'Inside PURGE_PROJ_WORKFLOW API' ;
904 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
905 END IF;
906
907 OPEN c_purge_wf_details ;
908 LOOP
909 l_wf_item_type_tbl.delete; -- 5201806
910 l_wf_item_key_tbl.delete; -- 5201806
911 l_wf_type_code_tbl.delete; -- 5201806
912
913 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;
914 EXIT WHEN l_wf_item_type_tbl.COUNT <= 0;
915
916 -- Delete pa_wf_ntf_performers
917 BEGIN
918
919 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
920 DELETE FROM PA_WF_NTF_PERFORMERS
921 WHERE item_key = l_wf_item_key_tbl(i)
922 AND item_type = l_wf_item_type_tbl(i)
923 AND wf_type_code = l_wf_type_code_tbl(i);
924
925 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
926 COMMIT;
927 EXCEPTION
928 WHEN NO_DATA_FOUND THEN
929 NULL ; -- Do nothing
930 WHEN OTHERS THEN
931 -- RAISE;
932 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
933 -- successfully deleted so far.
934 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
935 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
936 Fnd_Msg_Pub.add_exc_msg
937 ( p_pkg_name => 'PA_PURGE_PUB'
938 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
939 , p_error_text => SUBSTRB(SQLERRM,1,240));
940 EXIT; -- 5201806 : exit the loop after discussion with Anders.
941 END;
942
943 -- Delete pa_wf_process_details
944 BEGIN
945
946 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
947 DELETE FROM pa_wf_process_details
948 WHERE item_key=l_wf_item_key_tbl(i)
949 AND item_type = l_wf_item_type_tbl(i)
950 AND wf_type_code = l_wf_type_code_tbl(i) ;
951
952 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
953 COMMIT;
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 NULL ; -- Do nothing
957 WHEN OTHERS THEN
958 -- RAISE;
959 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
960 -- successfully deleted so far.
961 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
962 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
963 Fnd_Msg_Pub.add_exc_msg
964 ( p_pkg_name => 'PA_PURGE_PUB'
965 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
966 , p_error_text => SUBSTRB(SQLERRM,1,240));
967 EXIT; -- 5201806 : exit the loop after discussion with Anders.
968 END;
969
970 -- Delete pa_wf_processes
971 BEGIN
972 FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
973 DELETE FROM pa_wf_processes
974 WHERE item_key=l_wf_item_key_tbl(i)
975 AND item_type = l_wf_item_type_tbl(i)
976 AND wf_type_code = l_wf_type_code_tbl(i);
977
978 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
979 COMMIT;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 NULL ; -- Do nothing
983 WHEN OTHERS THEN
984 -- RAISE;
985 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
986 -- successfully deleted so far.
987 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
988 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
989 Fnd_Msg_Pub.add_exc_msg
990 ( p_pkg_name => 'PA_PURGE_PUB'
991 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
992 , p_error_text => SUBSTRB(SQLERRM,1,240));
993 EXIT; -- 5201806 : exit the loop after discussion with Anders.
994 END;
995 END LOOP;
996 CLOSE c_purge_wf_details;
997
998 IF p_debug_mode = 'Y' THEN
999 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_processes ' || l_rows1 ;
1000 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1001 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_process_details ' || l_rows2 ;
1002 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1003 pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_ntf_performers ' || l_rows3 ;
1004 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1005 END IF;
1006
1007
1008 PA_PURGE_PUB.INSERT_PURGE_LOG
1009 (
1010 p_request_id => p_request_id ,
1011 p_table_name => 'PA_WF_PROCESSES' ,
1012 p_rows_deleted => l_rows1 ,
1013 x_return_status => x_return_status ,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data
1016 );
1017
1018 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1019 --RAISE FND_API.G_EXC_ERROR;
1020 l_local_error_flag := 'Y'; -- 5201806
1021 END IF;
1022
1023
1024 PA_PURGE_PUB.INSERT_PURGE_LOG
1025 (
1026 p_request_id => p_request_id ,
1027 p_table_name => 'PA_WF_PROCESS_DETAILS',
1028 p_rows_deleted => l_rows2,
1029 x_return_status => x_return_status ,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data
1032 );
1033
1034 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1035 --RAISE FND_API.G_EXC_ERROR;
1036 l_local_error_flag := 'Y'; -- 5201806
1037 END IF;
1038
1039 PA_PURGE_PUB.INSERT_PURGE_LOG
1040 (
1041 p_request_id => p_request_id ,
1042 p_table_name => 'PA_WF_NTF_PERFORMERS',
1043 p_rows_deleted => l_rows3,
1044 x_return_status => x_return_status ,
1045 x_msg_count => x_msg_count,
1046 x_msg_data => x_msg_data
1047 );
1048
1049 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1050 --RAISE FND_API.G_EXC_ERROR;
1051 l_local_error_flag := 'Y'; -- 5201806
1052 END IF;
1053
1054 IF l_local_error_flag = 'Y' THEN -- 5201806
1055 RAISE FND_API.G_EXC_ERROR;
1056 END IF;
1057
1058
1059 IF p_debug_mode = 'Y' THEN
1060 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_WF_NTF_PERFORMERS';
1061 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1062 pa_debug.g_err_stage:= 'Exiting PURGE_PROJ_WORKFLOW';
1063 pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_PROJ_WORKFLOW :' || pa_debug.g_err_stage,l_debug_level3);
1064 pa_debug.reset_curr_function;
1065 END IF;
1066
1067 EXCEPTION
1068 WHEN FND_API.G_EXC_ERROR THEN
1069 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1070 x_msg_count := Fnd_Msg_Pub.count_msg; -- 5201806 Changed to x_msg_count
1071
1072 IF c_purge_wf_details%ISOPEN THEN
1073 Close c_purge_wf_details;
1074 END IF;
1075
1076 -- 5201806 : Commented not needed...
1077 -- IF l_msg_count >= 1 AND x_msg_data IS NULL
1078 -- THEN
1079 -- Pa_Interface_Utils_Pub.get_messages
1080 -- ( p_encoded => Fnd_Api.G_TRUE
1081 -- , p_msg_index => 1
1082 -- , p_msg_count => l_msg_count
1083 -- , p_msg_data => l_msg_data
1084 -- , p_data => l_data
1085 -- , p_msg_index_out => l_msg_index_out);
1086 -- x_msg_data := l_data;
1087 -- x_msg_count := l_msg_count;
1088 -- ELSE
1089 -- x_msg_count := l_msg_count;
1090 -- END IF;
1091
1092 IF p_debug_mode = 'Y' THEN
1093 Pa_Debug.reset_curr_function;
1094 END IF;
1095
1096 WHEN OTHERS THEN
1097 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1098 x_msg_count := 1;
1099 x_msg_data := SUBSTRB(SQLERRM,1,240);
1100
1101 IF c_purge_wf_details%ISOPEN THEN
1102 Close c_purge_wf_details;
1103 END IF;
1104
1105 Fnd_Msg_Pub.add_exc_msg
1106 ( p_pkg_name => 'PA_PURGE_PUB'
1107 , p_procedure_name => 'PURGE_PROJ_WORKFLOW'
1108 , p_error_text => x_msg_data);
1109
1110 x_msg_count := FND_MSG_PUB.count_msg;--5201806
1111
1112 IF p_debug_mode = 'Y' THEN
1113 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1114 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1115 Pa_Debug.reset_curr_function;
1116 END IF;
1117 -- RAISE; 5201806 Do not raise in internal APIs
1118 END PURGE_PROJ_WORKFLOW;
1119
1120 --
1121 -- PROCEDURE
1122 -- PURGE_REPORTING_EXCEPTIONS
1123 -- PURPOSE
1124 -- This API will purge unused reporting exception data from pa_reporting_exceptions
1125 --
1126 -- Parameter Name In/Out Data Type Null? Default Value Description
1127 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1128 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1129 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1130 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1131 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1132 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1133 --
1134 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1135 -- in the message stack.
1136 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1137 -- if only one error exists.
1138
1139 -- HISTORY
1140 -- avaithia 01-March-2006 Created
1141 --
1142
1143 PROCEDURE PURGE_REPORTING_EXCEPTIONS
1144 (
1145 p_debug_mode IN VARCHAR2 default 'N' ,
1146 p_commit_size IN NUMBER default 10000 ,
1147 p_request_id IN NUMBER ,
1148 x_return_status OUT NOCOPY VARCHAR2 ,
1149 x_msg_count OUT NOCOPY NUMBER ,
1150 x_msg_data OUT NOCOPY VARCHAR2
1151 )
1152 IS
1153 l_request_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE ();
1154 l_debug_level3 NUMBER :=3;
1155 l_rows1 NUMBER :=0;
1156 l_msg_data VARCHAR2(2000);
1157 l_data VARCHAR2(2000);
1158 l_msg_count NUMBER;
1159 l_msg_index_out NUMBER;
1160 i NUMBER;
1161 l_local_error_flag VARCHAR2(1) :='N'; -- 5201806
1162
1163 CURSOR c_get_request_id IS
1164 SELECT request_id
1165 FROM pa_reporting_exceptions
1166 WHERE request_id not in
1167 (SELECT request_id
1168 FROM fnd_concurrent_requests)
1169 AND nvl(request_id,0) > 0;
1170
1171 --Technically if the pa_reporting_exceptions.request_id does not exist in fnd_concurrent_requests.request_id,
1172 --then the record is eligible to be purged. Note - the code must exclude request_id < 0 as the
1173 --mass assignment flows populate this table using request_id = -1.
1174
1175 BEGIN
1176
1177 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1178 x_msg_count := 0;
1179 x_msg_data := NULL;
1180
1181 IF p_debug_mode = 'Y' THEN
1182 PA_DEBUG.set_curr_function( p_function => 'PURGE_REPORTING_EXCEPTIONS', p_debug_mode => p_debug_mode);
1183 pa_debug.g_err_stage:= 'Entering PURGE_REPORTING_EXCEPTIONS API';
1184 Pa_Debug.WRITE('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1185 END IF;
1186
1187 OPEN c_get_request_id;
1188 LOOP
1189 l_request_id_tbl.delete; -- 5201806
1190 FETCH c_get_request_id BULK COLLECT INTO l_request_id_tbl LIMIT p_commit_size ;
1191 EXIT WHEN l_request_id_tbl.COUNT <= 0 ;
1192
1193 BEGIN
1194 FORALL i IN l_request_id_tbl.FIRST..l_request_id_tbl.LAST
1195 DELETE FROM pa_reporting_exceptions
1196 WHERE request_id = l_request_id_tbl (i);
1197
1198 l_rows1 :=l_rows1+nvl(sql%rowcount,0);
1199 COMMIT;
1200 EXCEPTION
1201 WHEN NO_DATA_FOUND THEN
1202 NULL ; -- Do nothing
1203 WHEN OTHERS THEN
1204 -- RAISE;
1205 -- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
1206 -- successfully deleted so far.
1207 l_rows1 :=l_rows1+nvl(sql%rowcount,0);
1208 l_local_error_flag := 'Y'; -- 5201806 : Populate the error in stack
1209 Fnd_Msg_Pub.add_exc_msg
1210 ( p_pkg_name => 'PA_PURGE_PUB'
1211 , p_procedure_name => 'PURGE_REPORTING_EXCEPTIONS'
1212 , p_error_text => SUBSTRB(SQLERRM,1,240));
1213 EXIT; -- 5201806 : exit the loop after discussion with Anders.
1214 END;
1215 END LOOP;
1216 CLOSE c_get_request_id;
1217
1218 IF p_debug_mode = 'Y' THEN
1219 pa_debug.g_err_stage:= 'No. of rows deleted from pa_reporting_exceptions ' || l_rows1 ;
1220 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1221 END IF;
1222
1223
1224 PA_PURGE_PUB.INSERT_PURGE_LOG
1225 (
1226 p_request_id => p_request_id ,
1227 p_table_name => 'PA_REPORTING_EXCEPTIONS' ,
1228 p_rows_deleted => l_rows1 ,
1229 x_return_status => x_return_status ,
1230 x_msg_count => x_msg_count,
1231 x_msg_data => x_msg_data
1232 );
1233
1234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1235 --RAISE FND_API.G_EXC_ERROR;
1236 l_local_error_flag := 'Y'; -- 5201806
1237 END IF;
1238
1239 IF l_local_error_flag = 'Y' THEN -- 5201806
1240 RAISE FND_API.G_EXC_ERROR;
1241 END IF;
1242
1243 IF p_debug_mode = 'Y' THEN
1244 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to pa_reporting_exceptions';
1245 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1246 pa_debug.g_err_stage:= 'Exiting PURGE_REPORTING_EXCEPTIONS';
1247 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1248 Pa_Debug.reset_curr_function;
1249 END IF;
1250 EXCEPTION
1251 WHEN FND_API.G_EXC_ERROR THEN
1252 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1253 x_msg_count := Fnd_Msg_Pub.count_msg; -- 5201806 Changed to x_msg_count
1254
1255 IF c_get_request_id%ISOPEN THEN
1256 CLOSE c_get_request_id;
1257 END IF;
1258
1259 -- 5201806 : Commented not needed...
1260 -- IF l_msg_count >= 1 AND x_msg_data IS NULL
1261 -- THEN
1262 -- Pa_Interface_Utils_Pub.get_messages
1263 -- ( p_encoded => Fnd_Api.G_TRUE
1264 -- , p_msg_index => 1
1265 -- , p_msg_count => l_msg_count
1266 -- , p_msg_data => l_msg_data
1267 -- , p_data => l_data
1268 -- , p_msg_index_out => l_msg_index_out);
1269 --
1270 -- x_msg_data := l_data;
1271 -- x_msg_count := l_msg_count;
1272 -- ELSE
1273 -- x_msg_count := l_msg_count;
1274 -- END IF;
1275
1276 IF p_debug_mode = 'Y' THEN
1277 Pa_Debug.reset_curr_function;
1278 END IF;
1279 WHEN OTHERS THEN
1280 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1281 x_msg_count := 1;
1282 x_msg_data := SUBSTRB(SQLERRM,1,240);
1283
1284 IF c_get_request_id%ISOPEN THEN
1285 CLOSE c_get_request_id;
1286 END IF;
1287
1288 Fnd_Msg_Pub.add_exc_msg
1289 ( p_pkg_name => 'PA_PURGE_PUB'
1290 , p_procedure_name => 'PURGE_REPORTING_EXCEPTIONS'
1291 , p_error_text => x_msg_data);
1292
1293 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1294
1295 IF p_debug_mode = 'Y' THEN
1296 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1297 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1298 Pa_Debug.reset_curr_function;
1299 END IF;
1300 -- RAISE; 5201806 Do not raise in internal APIs
1301 END PURGE_REPORTING_EXCEPTIONS;
1302
1303 --
1304 -- PROCEDURE
1305 -- PURGE_ORG_AUTHORITY
1306 -- PURPOSE
1307 -- This API purges organization authority records of all terminated employees or contingent
1308 -- workers whose termination date is earlier than system date
1309 --
1310 --
1311 -- Parameter Name In/Out Data Type Null? Default Value Description
1312 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1313 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1314 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1315 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1316 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1317 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1318 --
1319 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1320 -- in the message stack.
1321 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1322 -- if only one error exists.
1323
1324 -- HISTORY
1325 -- bifernan 16-October-2007 Created
1326 -- bifernan 05-February-2007 Bug 6749278: Performance changes.
1327 -- Added code to log number of grants deleted.
1328 --
1329
1330 PROCEDURE PURGE_ORG_AUTHORITY
1331 (
1332 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
1333 p_commit_size IN NUMBER DEFAULT 10000 ,
1334 p_request_id IN NUMBER ,
1335 x_return_status OUT NOCOPY VARCHAR2 ,
1336 x_msg_count OUT NOCOPY NUMBER ,
1337 x_msg_data OUT NOCOPY VARCHAR2
1338 )
1339 IS
1340
1341 l_person_id per_all_people_f.person_id%TYPE;
1342 l_orginzation_id NUMBER;
1343 l_menu_name fnd_menus.menu_name%TYPE;
1344 l_debug_level3 NUMBER := 3;
1345 i NUMBER;
1346 l_local_error_flag VARCHAR2(1) :='N';
1347 l_grants_deleted NUMBER := 0;
1348 l_return_status VARCHAR2(30);
1349
1350 CURSOR c_purge_org_authority IS
1351 SELECT DISTINCT per.person_id,
1352 (to_number(fg.instance_pk1_value)) organization_id,
1353 fm.menu_name
1354 FROM fnd_grants fg,
1355 fnd_objects fo,
1356 fnd_menus fm,
1357 per_all_people_f per,
1358 wf_roles wfr
1359 WHERE per.person_id IN ( SELECT person_id
1360 FROM per_periods_of_service ppos
1361 WHERE ppos.person_id = per.person_id
1362 AND ppos.actual_termination_date is not null
1363 AND NOT EXISTS (SELECT 1
1364 FROM per_periods_of_service
1365 WHERE person_id = ppos.person_id
1366 AND actual_termination_date IS NULL )
1367 GROUP BY person_id
1368 HAVING MAX(actual_termination_date) < SYSDATE
1369 UNION
1370 SELECT person_id
1371 FROM per_periods_of_placement ppop
1372 WHERE ppop.person_id = per.person_id
1373 AND ppop.actual_termination_date is not null
1374 AND NOT EXISTS (SELECT 1
1375 FROM per_periods_of_placement
1376 WHERE person_id = ppop.person_id
1377 AND actual_termination_date IS NULL )
1378 GROUP BY person_id
1379 HAVING MAX(actual_termination_date) < SYSDATE )
1380 AND fg.object_id = fo.object_id
1381 AND fo.obj_name = 'ORGANIZATION'
1382 AND fg.instance_type = 'INSTANCE'
1383 AND fg.instance_pk1_value is not null
1384 AND fg.grantee_key = wfr.NAME
1385 AND fg.grantee_type = 'USER'
1386 AND fg.instance_set_id is null
1387 AND wfr.orig_system = 'HZ_PARTY'
1388 AND per.party_id = wfr.orig_system_id
1389 AND fg.menu_id = fm.menu_id
1390 AND (TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date)
1391 AND fm.menu_name IN ('PA_PRM_RES_AUTH', 'PA_PRM_PROJ_AUTH', 'PA_PRM_RES_PRMRY_CONTACT', 'PA_PRM_UTL_AUTH');
1392
1393 BEGIN
1394 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1395 x_msg_count := 0;
1396 x_msg_data := NULL;
1397
1398 IF p_debug_mode = 'Y' THEN
1399 PA_DEBUG.set_curr_function( p_function => 'PURGE_ORG_AUTHORITY', p_debug_mode => p_debug_mode);
1400 pa_debug.g_err_stage:= 'Inside PURGE_ORG_AUTHORITY API' ;
1401 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1402 END IF;
1403
1404 OPEN c_purge_org_authority;
1405 LOOP
1406 l_person_id := -1;
1407 l_orginzation_id := -1;
1408
1409 FETCH c_purge_org_authority INTO l_person_id, l_orginzation_id, l_menu_name;
1410 EXIT WHEN c_purge_org_authority%NOTFOUND;
1411
1412 BEGIN
1413 -- Delete Resource authority
1414 IF l_menu_name = 'PA_PRM_RES_AUTH' THEN
1415 pa_resource_utils.delete_grant( p_person_id => l_person_id
1416 ,p_org_id => l_orginzation_id
1417 ,p_role_name => 'PA_PRM_RES_AUTH'
1418 ,x_return_status => l_return_status);
1419 -- Delete project authority
1420 ELSIF l_menu_name = 'PA_PRM_PROJ_AUTH' THEN
1421 pa_resource_utils.delete_grant( p_person_id => l_person_id
1422 ,p_org_id => l_orginzation_id
1423 ,p_role_name => 'PA_PRM_PROJ_AUTH'
1424 ,x_return_status => l_return_status);
1425 -- Delete primary contact
1426 ELSIF l_menu_name = 'PA_PRM_RES_PRMRY_CONTACT' THEN
1427 pa_resource_utils.delete_grant( p_person_id => l_person_id
1428 ,p_org_id => l_orginzation_id
1429 ,p_role_name => 'PA_PRM_RES_PRMRY_CONTACT'
1430 ,x_return_status => l_return_status);
1431 -- Delete utilization authority
1432 ELSIF l_menu_name = 'PA_PRM_UTL_AUTH' THEN
1433 pa_resource_utils.delete_grant( p_person_id => l_person_id
1434 ,p_org_id => l_orginzation_id
1435 ,p_role_name => 'PA_PRM_UTL_AUTH'
1436 ,x_return_status => l_return_status);
1437 END IF;
1438
1439 IF (l_return_status IS NULL OR l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1440 l_grants_deleted := l_grants_deleted + 1;
1441 END IF;
1442
1443 EXCEPTION
1444 WHEN NO_DATA_FOUND THEN
1445 NULL ; -- Do nothing
1446 WHEN OTHERS THEN
1447 l_local_error_flag := 'Y';
1448 Fnd_Msg_Pub.add_exc_msg
1449 ( p_pkg_name => 'PA_PURGE_PUB'
1450 , p_procedure_name => 'PURGE_ORG_AUTHORITY'
1451 , p_error_text => SUBSTRB(SQLERRM,1,240));
1452 EXIT;
1453 END;
1454
1455 END LOOP;
1456 CLOSE c_purge_org_authority;
1457
1458 IF p_debug_mode = 'Y' THEN
1459 pa_debug.g_err_stage:= 'No. of grants deleted ' || l_grants_deleted;
1460 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1461 END IF;
1462
1463 PA_PURGE_PUB.INSERT_PURGE_LOG
1464 (
1465 p_request_id => p_request_id ,
1466 p_table_name => 'FND_GRANTS' ,
1467 p_rows_deleted => l_grants_deleted ,
1468 x_return_status => x_return_status ,
1469 x_msg_count => x_msg_count,
1470 x_msg_data => x_msg_data
1471 );
1472
1473 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1474 l_local_error_flag := 'Y';
1475 END IF;
1476
1477 IF l_local_error_flag = 'Y' THEN
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480
1481 IF p_debug_mode = 'Y' THEN
1482 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_ORG_AUTHORITY';
1483 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1484 pa_debug.g_err_stage:= 'Exiting PURGE_ORG_AUTHORITY';
1485 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1486 Pa_Debug.reset_curr_function;
1487 END IF;
1488
1489 EXCEPTION
1490 WHEN FND_API.G_EXC_ERROR THEN
1491
1492 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1493 x_msg_count := Fnd_Msg_Pub.count_msg;
1494
1495 IF c_purge_org_authority%ISOPEN THEN
1496 CLOSE c_purge_org_authority;
1497 END IF;
1498
1499 IF p_debug_mode = 'Y' THEN
1500 Pa_Debug.reset_curr_function;
1501 END IF;
1502
1503 WHEN OTHERS THEN
1504
1505 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1506 x_msg_count := 1;
1507 x_msg_data := SUBSTRB(SQLERRM,1,240);
1508
1509 IF c_purge_org_authority%ISOPEN THEN
1510 CLOSE c_purge_org_authority;
1511 END IF;
1512
1513 Fnd_Msg_Pub.add_exc_msg
1514 ( p_pkg_name => 'PA_PURGE_PUB'
1515 , p_procedure_name => 'PURGE_ORG_AUTHORITY'
1516 , p_error_text => x_msg_data);
1517
1518 x_msg_count := FND_MSG_PUB.count_msg;
1519
1520 IF p_debug_mode = 'Y' THEN
1521 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1522 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1523 Pa_Debug.reset_curr_function;
1524 END IF;
1525
1526 END PURGE_ORG_AUTHORITY;
1527
1528 --
1529 -- PROCEDURE
1530 -- PURGE_PJI_DEBUG
1531 -- PURPOSE
1532 -- This API purges the tables used by project performance summarization model to store
1533 -- debug information.
1534 --
1535 -- Parameter Name In/Out Data Type Null? Default Value Description
1536 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1537 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
1538 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
1539 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1540 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1541 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1542 --
1543 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1544 -- in the message stack.
1545 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1546 -- if only one error exists.
1547
1548 -- HISTORY
1549 -- bifernan 16-October-2007 Created
1550 -- bifernan 01-January-2008 Prefixed PJI tables with schema name for truncate statement
1551 -- bifernan 11-January-2008 Removed references to hard coded schemas names (GSCC)
1552 -- Replaced truncate statement with batch delete
1553 --
1554
1555 PROCEDURE PURGE_PJI_DEBUG
1556 (
1557 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
1558 p_commit_size IN NUMBER DEFAULT 10000 ,
1559 p_request_id IN NUMBER ,
1560 x_return_status OUT NOCOPY VARCHAR2 ,
1561 x_msg_count OUT NOCOPY NUMBER ,
1562 x_msg_data OUT NOCOPY VARCHAR2
1563 )
1564 IS
1565 l_debug_level3 NUMBER :=3;
1566 l_rows1 NUMBER :=0;
1567 l_rows2 NUMBER :=0;
1568 l_rows3 NUMBER :=0;
1569 l_msg_data VARCHAR2(2000);
1570 l_data VARCHAR2(2000);
1571 l_msg_count NUMBER;
1572 l_msg_index_out NUMBER;
1573 i NUMBER;
1574 l_local_error_flag VARCHAR2(1) :='N';
1575
1576 BEGIN
1577
1578 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1579 x_msg_count := 0;
1580 x_msg_data := NULL;
1581
1582 IF p_debug_mode = 'Y' THEN
1583 PA_DEBUG.set_curr_function( p_function => 'PURGE_PJI_DEBUG', p_debug_mode => p_debug_mode);
1584 pa_debug.g_err_stage:= 'Entering PURGE_PJI_DEBUG API';
1585 Pa_Debug.WRITE('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1586 END IF;
1587
1588 -- PJI_FM_EXTR_PLAN_LINES_DEBUG
1589 BEGIN
1590 LOOP
1591 DELETE FROM PJI_FM_EXTR_PLAN_LINES_DEBUG WHERE ROWNUM <= p_commit_size;
1592 EXIT WHEN sql%rowcount < p_commit_size;
1593
1594 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1595 COMMIT; -- Clear rollback segment
1596 END LOOP;
1597
1598 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1599 COMMIT;
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND THEN
1602 NULL ; -- Do nothing
1603 WHEN OTHERS THEN
1604 l_rows1 := l_rows1 + nvl(sql%rowcount,0);
1605 l_local_error_flag := 'Y';
1606 Fnd_Msg_Pub.add_exc_msg
1607 ( p_pkg_name => 'PA_PURGE_PUB'
1608 , p_procedure_name => 'PURGE_PJI_DEBUG'
1609 , p_error_text => SUBSTRB(SQLERRM,1,240));
1610 END;
1611
1612 -- PJI_FM_XBS_ACCUM_TMP1_DEBUG
1613 BEGIN
1614 LOOP
1615 DELETE FROM PJI_FM_XBS_ACCUM_TMP1_DEBUG WHERE ROWNUM <= p_commit_size;
1616 EXIT WHEN sql%rowcount < p_commit_size;
1617
1618 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1619 COMMIT; -- Clear rollback segment
1620 END LOOP;
1621
1622 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1623 COMMIT;
1624 EXCEPTION
1625 WHEN NO_DATA_FOUND THEN
1626 NULL ; -- Do nothing
1627 WHEN OTHERS THEN
1628 l_rows2 := l_rows2 + nvl(sql%rowcount,0);
1629 l_local_error_flag := 'Y';
1630 Fnd_Msg_Pub.add_exc_msg
1631 ( p_pkg_name => 'PA_PURGE_PUB'
1632 , p_procedure_name => 'PURGE_PJI_DEBUG'
1633 , p_error_text => SUBSTRB(SQLERRM,1,240));
1634 END;
1635
1636 -- PJI_SYSTEM_DEBUG_MSG
1637 BEGIN
1638 LOOP
1639 DELETE FROM PJI_SYSTEM_DEBUG_MSG WHERE ROWNUM <= p_commit_size;
1640 EXIT WHEN sql%rowcount < p_commit_size;
1641
1642 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1643 COMMIT; -- Clear rollback segment
1644 END LOOP;
1645
1646 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1647 COMMIT;
1648 EXCEPTION
1649 WHEN NO_DATA_FOUND THEN
1650 NULL ; -- Do nothing
1651 WHEN OTHERS THEN
1652 l_rows3 := l_rows3 + nvl(sql%rowcount,0);
1653 l_local_error_flag := 'Y';
1654 Fnd_Msg_Pub.add_exc_msg
1655 ( p_pkg_name => 'PA_PURGE_PUB'
1656 , p_procedure_name => 'PURGE_PJI_DEBUG'
1657 , p_error_text => SUBSTRB(SQLERRM,1,240));
1658 END;
1659
1660 IF p_debug_mode = 'Y' THEN
1661 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_EXTR_PLAN_LINES_DEBUG ' || l_rows1 ;
1662 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1663 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_XBS_ACCUM_TMP1_DEBUG ' || l_rows2 ;
1664 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1665 pa_debug.g_err_stage:= 'No. of rows deleted from PJI_SYSTEM_DEBUG_MSG ' || l_rows3 ;
1666 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1667 END IF;
1668
1669 PA_PURGE_PUB.INSERT_PURGE_LOG
1670 (
1671 p_request_id => p_request_id ,
1672 p_table_name => 'PJI_FM_EXTR_PLAN_LINES_DEBUG' ,
1673 p_rows_deleted => l_rows1 ,
1674 x_return_status => x_return_status ,
1675 x_msg_count => x_msg_count,
1676 x_msg_data => x_msg_data
1677 );
1678
1679 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1680 l_local_error_flag := 'Y';
1681 END IF;
1682
1683 PA_PURGE_PUB.INSERT_PURGE_LOG
1684 (
1685 p_request_id => p_request_id ,
1686 p_table_name => 'PJI_FM_XBS_ACCUM_TMP1_DEBUG',
1687 p_rows_deleted => l_rows2,
1688 x_return_status => x_return_status ,
1689 x_msg_count => x_msg_count,
1690 x_msg_data => x_msg_data
1691 );
1692
1693 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1694 l_local_error_flag := 'Y';
1695 END IF;
1696
1697 PA_PURGE_PUB.INSERT_PURGE_LOG
1698 (
1699 p_request_id => p_request_id ,
1700 p_table_name => 'PJI_SYSTEM_DEBUG_MSG',
1701 p_rows_deleted => l_rows3,
1702 x_return_status => x_return_status ,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data
1705 );
1706
1707 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1708 l_local_error_flag := 'Y';
1709 END IF;
1710
1711 IF l_local_error_flag = 'Y' THEN
1712 RAISE FND_API.G_EXC_ERROR;
1713 END IF;
1714
1715
1716 IF p_debug_mode = 'Y' THEN
1717 pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_PJI_DEBUG';
1718 pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1719 pa_debug.g_err_stage:= 'Exiting PURGE_PJI_DEBUG';
1720 pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_PJI_DEBUG :' || pa_debug.g_err_stage,l_debug_level3);
1721 pa_debug.reset_curr_function;
1722 END IF;
1723
1724 EXCEPTION
1725 WHEN FND_API.G_EXC_ERROR THEN
1726 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1727 x_msg_count := Fnd_Msg_Pub.count_msg;
1728
1729 IF p_debug_mode = 'Y' THEN
1730 Pa_Debug.reset_curr_function;
1731 END IF;
1732
1733 WHEN OTHERS THEN
1734 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1735 x_msg_count := 1;
1736 x_msg_data := SUBSTRB(SQLERRM,1,240);
1737
1738 Fnd_Msg_Pub.add_exc_msg
1739 ( p_pkg_name => 'PA_PURGE_PUB'
1740 , p_procedure_name => 'PURGE_PJI_DEBUG'
1741 , p_error_text => x_msg_data);
1742
1743 x_msg_count := FND_MSG_PUB.count_msg;
1744
1745 IF p_debug_mode = 'Y' THEN
1746 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1747 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1748 Pa_Debug.reset_curr_function;
1749 END IF;
1750
1751 END PURGE_PJI_DEBUG;
1752
1753 --
1754 -- PROCEDURE
1755 -- PRINT_OUTPUT_REPORT
1756 -- PURPOSE
1757 -- This API will print the output report to concurrent log file.
1758 -- Parameter Name In/Out Data Type Null? Default Value Description
1759 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1760 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1761 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1762 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1763 --
1764 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1765 -- in the message stack.
1766 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1767 -- if only one error exists.
1768
1769 -- HISTORY
1770 -- avaithia 01-March-2006 Created
1771 --
1772
1773 PROCEDURE PRINT_OUTPUT_REPORT
1774 (
1775 p_request_id IN NUMBER ,
1776 x_return_status OUT NOCOPY VARCHAR2 ,
1777 x_msg_count OUT NOCOPY NUMBER ,
1778 x_msg_data OUT NOCOPY VARCHAR2
1779 )
1780 IS
1781
1782 CURSOR c_purge_details IS
1783 SELECT table_name, num_recs_purged
1784 FROM PA_PURGE_PRJ_DETAILS
1785 WHERE purge_batch_id = p_request_id
1786 AND project_id = 0
1787 ORDER BY table_name ;
1788
1789 l_table_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1790 l_rows_deleted_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1791 l_debug_mode VARCHAR2(1); -- 5201806
1792
1793 BEGIN
1794
1795 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1796 x_msg_count := 0;
1797 x_msg_data := NULL;
1798 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N'); -- 5201806
1799
1800 pa_debug.set_process('PLSQL','LOG','Y');
1801 pa_debug.set_curr_function( p_function => 'PRINT_OUTPUT_REPORT', p_debug_mode => 'Y');
1802
1803 pa_debug.g_err_stage:= '---------------------------------------------------';
1804 pa_debug.write_file(pa_debug.g_err_stage);
1805
1806 pa_debug.g_err_stage:= 'Purge Process Report : ADM: Purge Projects Obsolete Data ';
1807 pa_debug.write_file(pa_debug.g_err_stage);
1808
1809 pa_debug.g_err_stage:= '+---------------------------------------------------+';
1810 pa_debug.write_file(pa_debug.g_err_stage);
1811
1812 pa_debug.g_err_stage:= 'Current system time is ' || sysdate ;
1813 pa_debug.write_file(pa_debug.g_err_stage);
1814
1815 pa_debug.g_err_stage:= '+---------------------------------------------------+';
1816 pa_debug.write_file(pa_debug.g_err_stage);
1817
1818 OPEN c_purge_details;
1819 FETCH c_purge_details BULK COLLECT INTO l_table_name_tbl,l_rows_deleted_tbl;
1820 CLOSE c_purge_details;
1821
1822 IF nvl(l_table_name_tbl.LAST,0) > 0 THEN
1823 FOR i IN l_table_name_tbl.FIRST..l_table_name_tbl.LAST LOOP
1824 pa_debug.g_err_stage:= 'Purged '|| l_rows_deleted_tbl(i) ||' entries from ' || l_table_name_tbl(i) ;
1825 pa_debug.write_file(pa_debug.g_err_stage);
1826 pa_debug.g_err_stage:= ' ';
1827 pa_debug.write_file(pa_debug.g_err_stage);
1828 END LOOP ;
1829 END IF;
1830
1831 Pa_Debug.reset_curr_function;
1832
1833 EXCEPTION
1834 WHEN OTHERS THEN
1835 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1836 x_msg_count := 1;
1837 x_msg_data := SUBSTRB(SQLERRM,1,240);
1838
1839 IF c_purge_details%ISOPEN THEN
1840 CLOSE c_purge_details ;
1841 END IF;
1842
1843 Fnd_Msg_Pub.add_exc_msg
1844 ( p_pkg_name => 'PA_PURGE_PUB'
1845 , p_procedure_name => 'PRINT_OUTPUT_REPORT'
1846 , p_error_text => x_msg_data);
1847
1848 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1849
1850 IF l_debug_mode = 'Y' THEN -- 5201806
1851 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1852 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1853 Pa_Debug.reset_curr_function;
1854 END IF;
1855 -- RAISE; 5201806 Do not raise in internal APIs
1856 END PRINT_OUTPUT_REPORT;
1857
1858 --
1859 -- PROCEDURE
1860 -- INSERT_PURGE_LOG
1861 -- PURPOSE
1862 -- This API will populate the log table for deleted table information.
1863 --
1864 -- Parameter Name In/Out Data Type Null? Default Value Description
1865 -- ------------- ------ ---------- ------ ------------- ---------------------------------
1866 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
1867 -- P_TABLE_NAME IN VARCHAR2 NOT NULL Indicates the table name deleted.
1868 -- P_ROWS_DELETED IN NUMBER NOT NULL Indicates the number of rows deleted.
1869 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
1870 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
1871 --
1872 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
1873 -- in the message stack.
1874 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
1875 -- if only one error exists.
1876
1877 -- HISTORY
1878 -- avaithia 01-March-2006 Created
1879 --
1880
1881 PROCEDURE INSERT_PURGE_LOG
1882 (
1883 p_request_id IN NUMBER ,
1884 p_table_name IN VARCHAR2 ,
1885 p_rows_deleted IN NUMBER ,
1886 x_return_status OUT NOCOPY VARCHAR2 ,
1887 x_msg_count OUT NOCOPY NUMBER ,
1888 x_msg_data OUT NOCOPY VARCHAR2
1889 )
1890 IS
1891 l_debug_mode VARCHAR2(1);
1892 l_debug_level3 NUMBER := 3;
1893 BEGIN
1894
1895 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1896 x_msg_count := 0;
1897 x_msg_data := NULL;
1898
1899 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1900
1901 IF l_debug_mode = 'Y' THEN
1902 pa_debug.set_curr_function( p_function => 'INSERT_PURGE_LOG', p_debug_mode => 'Y');
1903 Pa_Debug.WRITE('PA_PURGE_PUB','Before inserting into PA_PURGE_PRJ_DETAILS',l_debug_level3);
1904 END IF;
1905
1906 INSERT INTO PA_PURGE_PRJ_DETAILS
1907 (
1908 PURGE_BATCH_ID
1909 ,PROJECT_ID
1910 ,TABLE_NAME
1911 ,NUM_RECS_PURGED
1912 ,CREATED_BY
1913 ,LAST_UPDATE_DATE
1914 ,LAST_UPDATED_BY
1915 ,LAST_UPDATE_LOGIN
1916 ,CREATION_DATE
1917 ,PROGRAM_APPLICATION_ID
1918 ,PROGRAM_ID
1919 ,PROGRAM_UPDATE_DATE
1920 )
1921 VALUES
1922 (
1923 p_request_id
1924 ,0
1925 ,p_table_name
1926 ,p_rows_deleted
1927 ,fnd_global.user_id
1928 ,sysdate
1929 ,fnd_global.user_id
1930 ,fnd_global.login_id
1931 ,sysdate
1932 ,fnd_global.prog_appl_id
1933 ,fnd_global.conc_program_id
1934 ,sysdate
1935 );
1936
1937 IF l_debug_mode = 'Y' THEN
1938 Pa_Debug.g_err_stage:='Successfully Inserted Purge Log';
1939 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage,l_debug_level3);
1940 pa_debug.reset_curr_function;
1941 END IF;
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1946 x_msg_count := 1;
1947 x_msg_data := SUBSTRB(SQLERRM,1,240);
1948
1949 Fnd_Msg_Pub.add_exc_msg
1950 ( p_pkg_name => 'PA_PURGE_PUB'
1951 , p_procedure_name => 'INSERT_PURGE_LOG'
1952 , p_error_text => x_msg_data);
1953
1954 x_msg_count := FND_MSG_PUB.count_msg; --5201806
1955
1956 IF l_debug_mode = 'Y' THEN
1957 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1958 Pa_Debug.WRITE('PA_PURGE_PUB',Pa_Debug.g_err_stage, 5);
1959 Pa_Debug.reset_curr_function;
1960 END IF;
1961 -- RAISE; 5201806 Do not raise in internal APIs
1962 END INSERT_PURGE_LOG;
1963
1964 END PA_PURGE_PUB;