1 Package body pa_purge as
2 /* $Header: PAXPRMNB.pls 120.4 2007/12/09 10:24:43 vvjoshi ship $ */
3
4 -- Start of comments
5 -- API name : Purge_Project
6 -- Type : Private
7 -- Pre-reqs : None
8 -- Function : Invokes the procedure for purge for a specific project for the
9 -- various modules ( Costing , billing ,Project tracking , capital
10 -- projects) based on the option selection during the purge batch
11 -- creation.
12 -- In addition also invokes a client extension
13 -- for any customer specific purge procedures
14 --
15 -- Parameters p_batch_id IN NUMBER
16 -- The purge batch id for which rows have
17 -- to be purged/archived.
18 -- p_project_Id IN NUMBER,
19 -- The project id for which records have
20 -- to be purged/archived.
21 -- p_Active_Closed_Flag IN VARCHAR2,
22 -- Indicates if batch contains ACTIVE or CLOSED projects
23 -- ( 'A' - Active , 'C' - Closed)
24 -- p_Purge_Release IN VARCHAR2,
25 -- Oracle Projects release (10.7 , 11.0)
26 -- p_Purge_Summary_Flag IN VARCHAR2,
27 -- Purge Summary tables data
28 -- p_Purge_Capital_Flag IN VARCHAR2,
29 -- Purge Capital projects tables data
30 -- p_Purge_Budgets_Flag IN VARCHAR2,
31 -- Purge Budget tables data
32 -- p_Purge_Actuals_Flag IN VARCHAR2,
33 -- Purge Actuals tables data i.e. Costing and Billing tables
34 -- p_Archive_Summary_Flag IN VARCHAR2,
35 -- Archive Summary tables data
36 -- p_Archive_Capital_Flag IN VARCHAR2,
37 -- Purge Capital projects tables data
38 -- p_Archive_Budgets_Flag IN VARCHAR2,
39 -- Archive Budget tables data
40 -- p_Archive_Actuals_Flag IN VARCHAR2,
41 -- Archive Actuals tables data i.e. Costing and Billing tables
42 -- p_Txn_To_Date IN DATE,
43 -- Date on or before which all transactions are to be purged
44 -- (Will be used by Costing only)
45 -- p_Commit_Size IN NUMBER,
46 -- The commit size
47 -- X_Err_Stack IN OUT VARCHAR2,
48 -- Error stack
49 -- X_Err_Stage IN OUT VARCHAR2,
50 -- Stage in the procedure where error occurred
51 -- X_Err_Code IN OUT NUMBER
52 -- Error code returned from the procedure
53 -- = 0 SUCCESS
54 -- > 0 Application error
55 -- < 0 Oracle error
56 -- End of comments
57 Procedure Purge_Project(
58 p_batch_id IN NUMBER,
59 p_project_Id IN NUMBER,
60 p_Active_Closed_Flag IN VARCHAR2,
61 p_Purge_Release IN VARCHAR2,
62 p_Purge_Summary_Flag IN VARCHAR2,
63 p_Purge_Capital_Flag IN VARCHAR2,
64 p_Purge_Budgets_Flag IN VARCHAR2,
65 p_Purge_Actuals_Flag IN VARCHAR2,
66 p_Archive_Summary_Flag IN VARCHAR2,
67 p_Archive_Capital_Flag IN VARCHAR2,
68 p_Archive_Budgets_Flag IN VARCHAR2,
69 p_Archive_Actuals_Flag IN VARCHAR2,
70 p_Txn_To_Date IN DATE,
71 p_Commit_Size IN NUMBER,
72 X_Err_Stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
73 X_Err_Stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
74 X_Err_Code IN OUT NOCOPY NUMBER) is --File.Sql.39 bug 4440895
75
76 l_old_err_stack VARCHAR2(2000); -- Bug 4227589. Changed size from 1024 to 2000
77 l_project_status_code VARCHAR2(30);
78 l_project_type_class pa_project_types_all.project_type_class_code%TYPE; -- Added for bug 3583748
79 begin
80 l_old_err_stack := X_err_stack;
81 X_err_stack := X_err_stack ||'->pa_purge.purge_project';
82 X_err_stack := X_err_stack ||'Batch Id: '||p_batch_id || 'Project Id: '||p_project_id ;
83 X_err_code := 0;
84
85 pa_debug.debug(X_err_stack);
86
87 -- Call user defined extension to purge customer specific tables
88 pa_debug.debug('--Calling client extension procedure');
89 pa_purge_extn.pa_purge_client_extn(
90 p_purge_batch_id => p_batch_id,
91 p_project_id => p_project_id,
92 p_purge_release => p_purge_release,
93 p_txn_through_date => p_txn_to_date,
94 p_archive_flag => p_archive_actuals_flag,
95 p_calling_place => 'BEFORE_PURGE',
96 p_Commit_Size => p_Commit_Size,
97 X_Err_Stack => X_Err_Stack,
98 X_Err_Stage => X_Err_Stage,
99 X_Err_Code => X_Err_Code);
100
101
102 -- Call the procedures conditionally based on the various flag values
103 --
104 -- If summarization need to be purged and project is closed then call
105 -- main summarization purge procedure
106 --
107
108 IF P_purge_summary_flag = 'Y' THEN
109 if P_active_closed_flag = 'C' then
110 pa_debug.debug('--Calling procedure pa_purge_summary.pa_summary_main_purge');
111 pa_purge_summary.pa_summary_main_purge (
112 p_purge_batch_id => p_batch_id,
113 p_project_id => p_project_id,
114 p_purge_release => p_purge_release,
115 p_txn_to_date => p_txn_to_date,
116 p_archive_flag => p_archive_summary_flag,
117 p_Commit_Size => p_Commit_Size,
118 X_Err_Stack => X_Err_Stack,
119 X_Err_Stage => X_Err_Stage,
120 X_Err_Code => X_Err_Code);
121 end if ;
122 END IF;
123
124 -- If budgets need to be purged and project is closed then call
125 -- main budgets purge procedure
126 --
127 /***
128 IF P_purge_budgets_flag = 'Y' THEN
129 if P_active_closed_flag = 'C' then
130 pa_debug.debug('--Calling procedure pa_purge_budget.pa_budget_main_purge');
131 pa_purge_budget.pa_budget_main_purge (
132 p_batch_id,
133 p_project_id,
134 p_purge_release,
135 p_txn_to_date,
136 p_archive_budgets_flag,
137 p_Commit_Size,
138 X_Err_Stack,
139 X_Err_Stage,
140 X_Err_Code);
141
142 end if ;
143 END IF;
144 **/
145 -- If capital tables need to be purged and project is closed then call
146 -- the main capital purge procedure
147 --
148
149 IF P_purge_capital_flag = 'Y' THEN
150 if P_active_closed_flag = 'C' then
151 pa_debug.debug('--Calling procedure pa_purge_capital.pa_capital_main_purge');
152 pa_purge_capital.pa_capital_main_purge (
153 p_purge_batch_id => p_batch_id,
154 p_project_id => p_project_id,
155 p_purge_release => p_purge_release,
156 p_txn_to_date => p_txn_to_date,
157 p_archive_flag => p_archive_capital_flag,
158 p_Commit_Size => p_Commit_Size,
159 p_Err_Stack => X_Err_Stack,
160 p_Err_Stage => X_Err_Stage,
161 p_Err_Code => X_Err_Code);
162 end if ;
163 END IF;
164
165
166
167 IF p_purge_actuals_flag = 'Y' THEN
168
169 -- Billing purge procedure to be called only in case of 'CLOSED' projects
170 --
171 -- Added for bug 3583748
172 select project_type_class_code
173 into l_project_type_class
174 from pa_project_types_all ppt
175 ,pa_projects_all ppa
176 where ppt.project_type = ppa.project_type
177 and ppt.org_id = ppa.org_id /* added for Bug 5099516*/ -- Removed NVL for bug#5908179 by vvjoshi
178 and project_id = p_project_id;
179
180 If p_active_closed_flag = 'C'
181 and l_project_type_class = 'CONTRACT' Then -- added for bug 3583748
182 pa_debug.debug('--Calling procedure pa_purge_billing.pa_billing_main_purge ');
183 pa_purge_billing.pa_billing_main_purge (
184 p_purge_batch_id => p_batch_id,
185 p_project_id => p_project_id,
186 p_purge_release => p_purge_release,
187 p_txn_to_date => p_txn_to_date,
188 p_archive_flag => p_archive_actuals_flag,
189 p_Commit_Size => p_Commit_Size,
190 X_Err_Stack => X_Err_Stack,
191 X_Err_Stage => X_Err_Stage,
192 X_Err_Code => X_Err_Code);
193 end if ;
194
195 /* Bug#2416385 Code added for the Phase III of Archive and Purge, starts here */
196
197 -- Call the Intercomapany and Interproject Billing purge procedure
198 --
199 pa_debug.debug('--Calling procedure pa_purge_icip.PA_DraftInvDetails');
200 pa_purge_icip.PA_DraftInvDetails (
201 p_purge_batch_id => p_batch_id,
202 p_project_id => p_project_id,
203 p_purge_release => p_purge_release,
204 p_txn_to_date => p_txn_to_date,
205 p_archive_flag => p_archive_actuals_flag,
206 p_Commit_Size => p_Commit_Size,
207 X_Err_Stack => X_Err_Stack,
208 X_Err_Stage => X_Err_Stage,
209 X_Err_Code => X_Err_Code);
210
211 /* Bug#2441479 Code added for the PJR Archive and Purge, starts here */
212
213 pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_REQUIREMENTS_PURGE');
214 pa_purge_pjr_txns.pa_requirements_purge (
215 p_purge_batch_id => p_batch_id,
216 p_project_id => p_project_id,
217 p_purge_release => p_purge_release,
218 p_txn_to_date => p_txn_to_date,
219 p_archive_flag => p_archive_actuals_flag,
220 p_Commit_Size => p_Commit_Size,
221 X_Err_Stack => X_Err_Stack,
222 X_Err_Stage => X_Err_Stage,
223 X_Err_Code => X_Err_Code);
224
225 pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_ASSIGNMENTS_PURGE');
226 pa_purge_pjr_txns.pa_assignments_purge (
227 p_purge_batch_id => p_batch_id,
228 p_project_id => p_project_id,
229 p_purge_release => p_purge_release,
230 p_txn_to_date => p_txn_to_date,
231 p_archive_flag => p_archive_actuals_flag,
232 p_Commit_Size => p_Commit_Size,
233 X_Err_Stack => X_Err_Stack,
237
234 X_Err_Stage => X_Err_Stage,
235 X_Err_Code => X_Err_Code);
236
238 /* Bug#2416385 Code added for the Phase III of Archive and Purge, ends here */
239
240 -- Call the main costing purge procedure
241 --
242 pa_debug.debug('--Calling procedure pa_purge_costing.pa_costing_main_purge');
243 pa_purge_costing.pa_costing_main_purge (
244 p_purge_batch_id => p_batch_id,
245 p_project_id => p_project_id,
246 p_purge_release => p_purge_release,
247 p_txn_to_date => p_txn_to_date,
248 p_archive_flag => p_archive_actuals_flag,
249 p_Commit_Size => p_Commit_Size,
250 X_Err_Stack => X_Err_Stack,
251 X_Err_Stage => X_Err_Stage,
252 X_Err_Code => X_Err_Code);
253
254 END IF;
255
256 -- Call user defined extension to purge customer specific tables after the
257 -- main tables are purged
258 pa_debug.debug('--Calling client extension procedure');
259 pa_purge_extn.pa_purge_client_extn(
260 p_purge_batch_id => p_batch_id,
261 p_project_id => p_project_id,
262 p_purge_release => p_purge_release,
263 p_txn_through_date => p_txn_to_date,
264 p_archive_flag => p_archive_actuals_flag,
265 p_calling_place => 'AFTER_PURGE',
266 p_Commit_Size => p_Commit_Size,
267 X_Err_Stack => X_Err_Stack,
268 X_Err_Stage => X_Err_Stage,
269 X_Err_Code => X_Err_Code);
270
271
272 -- Set completed status for the project
273 pa_debug.debug('--Before update of pa_purge_projects batch id :
274 '|| p_batch_id || ' project id : '|| p_project_id);
275
276 update pa_purge_projects
277 set
278 purged_date=sysdate
279 ,last_update_date=sysdate
280 ,last_updated_by=fnd_global.user_id
281 ,last_update_login=fnd_global.login_id
282 ,program_id = fnd_global.conc_program_id
283 ,program_application_id = fnd_global.prog_appl_id
284 ,request_id = fnd_global.conc_request_id
285 ,program_update_date = sysdate
286 where purge_batch_id = p_batch_id
287 and project_id = p_project_id;
288
289 commit;
290
291 pa_debug.debug('--After update of pa_purge_projects batch id :
292 '|| p_batch_id || ' project id : '|| p_project_id);
293
294 -- For closed projects set the project status to fully purged or partially purged
295 -- For active projects set the project status code to old project status code
296
297 pa_debug.debug('--Before update of pa_projects_all batch id :
298 '|| p_batch_id || ' project id : '|| p_project_id);
299
300
301 l_project_status_code := pa_purge.get_post_purge_status(p_project_id,p_batch_id);
302
303 update pa_projects_all
304 set project_status_code = l_project_status_code
305 where project_id = p_project_id;
306 commit;
307 pa_debug.debug('--After update of pa_projects_all batch id :
308 '|| p_batch_id || ' project id : '|| p_project_id);
309
310 X_err_stack := l_old_err_stack;
311
312 Exception
313 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
314 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
315
316 WHEN OTHERS THEN
317 pa_debug.debug('Error Procedure Name := PA_PURGE.PURGE_PROJECT' );
318 pa_debug.debug('Error stage is '||x_err_stage );
319 pa_debug.debug('Error stack is '||x_err_stack );
320 pa_debug.debug(SQLERRM);
321 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
322
323 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
324
325 End purge_project;
326
327 -- Start of comments
328 -- API name : Purge
329 -- Type : Public
330 -- Pre-reqs : None
331 -- Function : This procedure is called from the form.
332 -- Main purge procedure.
333 -- Invokes the purge_project procedure for each project
334 -- in the purge batch
335 -- Parameters p_batch_id IN NUMBER
336 -- The purge batch id for which rows have
337 -- to be purged/archived.
338 -- p_Commit_Size IN NUMBER,
339 -- The commit size
340 -- errbuf IN OUT VARCHAR2,
341 -- error buffer containing the SQLERRM
342 -- retcode IN OUT NUMBER
343 -- Standard error code returned from the procedure
344 -- = 0 SUCCESS
345 -- < 0 Oracle error
346 -- End of comments
347 Procedure Purge (
348 p_Batch_Id IN NUMBER ,
349 p_Commit_Size IN NUMBER ,
350 ret_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
351 errbuf IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
355 l_err_stack VARCHAR2(2000);
352
353 l_active_closed_flag VARCHAR2(1);
354 l_purge_release VARCHAR2(30);
356 l_err_stage VARCHAR2(2000);
357 l_err_code NUMBER;
358 l_project_id NUMBER;
359 COUNT_MRC_SET_OF_BOOKS NUMBER;
360
361 Begin
362
363 l_err_stack := '->pa_purge.purge';
364 l_err_code := 0;
365 l_err_stage := 'Purge for batch_id: ' || to_char(p_batch_id);
366 l_project_id := -9999;
367
368 pa_debug.debug(l_err_stage);
369
370 -- Count MRC reporting set of books
371 -- and set ARPUR_MRC_COMMIT_SIZE.
372
373 PA_UTILS2.ARPUR_Commit_Size := p_commit_size;
374 PA_UTILS2.ARPUR_MRC_Commit_Size := p_commit_size;
375
376 SELECT count(*)
377 INTO COUNT_MRC_SET_OF_BOOKS
378 FROM pa_implementations pi, gl_alc_ledger_rships_v gal --gl_mc_reporting_options glm. Bug 4468366.
379 WHERE (gal.org_id = -99 OR pi.org_id = gal.org_id) --pi.org_id = glm.org_id. Bug 4468366.
380 AND gal.relationship_enabled_flag = 'Y' --glm.enabled_flag = 'Y'. Bug 4468366.
381 AND gal.application_id = 275; --glm.application_Id = 275; Bug 4468366.
382 PA_UTILS2.ARPUR_MRC_Commit_Size := PA_UTILS2.ARPUR_Commit_size/(1+COUNT_MRC_SET_OF_BOOKS);
383 pa_debug.debug('Calculate ARPUR_Commit_Size and ARPUR_MRC_Commit_Size');
384
385
386 -- Set working status for the batch
387 pa_debug.debug('Before update of pa_purge_batches to pending');
388 Update pa_purge_batches
389 Set batch_status_code='P' ,
390 request_id = fnd_global.conc_request_id
391 Where purge_batch_id = p_batch_id;
392
393 Commit;
394 pa_debug.debug('After update of pa_purge_batches to pending');
395
396 -- Lock the batch row
397 pa_debug.debug('Before locking table pa_purge_batches');
398 Select active_closed_flag , purge_release
399 Into l_active_closed_flag , l_purge_release
400 From pa_purge_batches
401 Where purge_batch_id = p_batch_id
402 and request_id = fnd_global.conc_request_id
403 For update;
404
405 pa_debug.debug('After locking table pa_purge_batches');
406
407 -- Select the projects in the batch that have not been purged
408 -- (Purged date is Null)
409
410 pa_debug.debug('Before FOR loop');
411 For pp_rec In
412 (
413 Select
414 project_id,
415 purge_summary_flag,
416 purge_capital_flag,
417 purge_budgets_flag,
418 purge_actuals_flag,
419 archive_summary_flag,
420 archive_capital_flag,
421 archive_budgets_flag,
422 archive_actuals_flag,
423 txn_to_date,
424 last_project_status_code,
425 next_pp_project_status_code,
426 next_p_project_status_code
427 From pa_purge_projects
428 Where purge_batch_id = p_Batch_Id
429 And purged_date is NULL
430 Order By project_id
431 )
432 Loop
433
434 l_project_id := pp_rec.project_id ;
435
436 -- For each project call procedure to purge
437 pa_debug.debug('Calling pa_purge.purge_project with project_id : '||pp_rec.project_id);
438 pa_purge.purge_project(
439 p_batch_id => p_batch_id,
440 p_project_id => pp_rec.project_id,
441 p_active_closed_flag => l_active_closed_flag,
442 p_purge_release => l_purge_release,
443 p_purge_summary_flag => pp_rec.purge_summary_flag,
444 p_purge_capital_flag => pp_rec.purge_capital_flag,
445 p_purge_budgets_flag => pp_rec.purge_budgets_flag,
446 p_purge_actuals_flag => pp_rec.purge_actuals_flag,
447 p_archive_summary_flag => pp_rec.archive_summary_flag,
448 p_archive_capital_flag => pp_rec.archive_capital_flag,
449 p_archive_budgets_flag => pp_rec.archive_budgets_flag,
450 p_archive_actuals_flag => pp_rec.archive_actuals_flag,
451 p_txn_to_date => pp_rec.txn_to_date,
452 p_Commit_Size => p_commit_size,
453 X_Err_Stack => l_Err_Stack,
454 X_Err_Stage => l_Err_Stage,
455 X_Err_Code => l_Err_Code);
456
457 End Loop;
458
459 -- The following three procedure calls purges pa_routings, pa_expenditures_all
460 -- and pa-expenditure_history. It was originally in the call for costing
461 -- tables purge. But there it will be called for every project. Here it is called
462 -- after purging all the other tables for all the projects. So it will be called
463 -- only once.
464
465 -- Purging of PA_EXPENDITURES_ALL and PA_ROUTINGS are removed for the time being. It
466 -- will be done at a later point of time
467 /*
468 pa_debug.debug('*-> About to purge expenditures ');
469 l_err_stage := 'About to purge expenditures without any expenditure items ';
470 pa_purge_costing.PA_Expenditures1(p_purge_batch_id => p_batch_id,
471 p_project_id => l_project_id,
472 p_purge_release => l_purge_release,
473 p_archive_flag => 'Y',
477 x_err_stage => l_err_stage
474 p_commit_size => p_commit_size,
475 x_err_code => l_err_code,
476 x_err_stack => l_err_stack,
478 ) ;
479
480 pa_debug.debug('*-> About to purge routings ');
481 l_err_stage := 'About to purge routing records ';
482 pa_purge_costing.PA_Routings1(p_purge_batch_id => p_batch_id,
483 p_project_id => l_project_id,
484 p_purge_release => l_purge_release,
485 p_archive_flag => 'Y',
486 p_commit_size => p_commit_size,
487 x_err_code => l_err_code,
488 x_err_stack => l_err_stack,
489 x_err_stage => l_err_stage
490 ) ;
491
492 */
493 -- Set completed status for the batch
494 pa_debug.debug('Before update of pa_purge_batches to complete ');
495 Update pa_purge_batches
496 Set batch_status_code='C'
497 ,purged_date=sysdate
498 ,last_update_date=sysdate
499 ,last_updated_by=fnd_global.user_id
500 ,last_update_login=fnd_global.login_id
501 Where purge_batch_id = p_batch_id;
502
503 Commit;
504 pa_debug.debug('After update of pa_purge_batches to complete');
505
506
507 Exception
508
509 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
510 errbuf := PA_PROJECT_UTILS2.g_sqlerrm ;
511 ret_code := -1 ;
512 WHEN OTHERS THEN
513 errbuf := SQLERRM ;
514 ret_code := -1 ;
515
516 End purge;
517
518 -- Start of comments
519 -- API name : CommitProcess
520 -- Type : Public
521 -- Pre-reqs : None
522 -- Function : Common procedure for commit.
523 -- Will be invoked from the various purge procedures
524 --
525 -- Parameters p_batch_id IN NUMBER
526 -- The purge batch id for which rows have
527 -- been purged/archived.
528 -- p_project_Id IN NUMBER,
529 -- The project id for which records have
530 -- been purged/archived.
531 -- p_table_name IN VARCHAR2,
532 -- The table for which rows have been purged
533 -- p_NoOfRecordsIns IN NUMBER,
534 -- No. of records inserted into the archive table
535 -- p_NoOfRecordsDel IN NUMBER,
536 -- No. of records deleted from table
537 -- X_Err_Stack IN OUT VARCHAR2,
538 -- Error stack
539 -- X_Err_Stage IN OUT VARCHAR2,
540 -- Stage in the procedure where error occurred
541 -- X_Err_Code IN OUT NUMBER,
542 -- Error code returned from the procedure
543 -- = 0 SUCCESS
544 -- > 0 Application error
545 -- < 0 Oracle error
546 -- p_MRC_table_name IN VARCHAR2,
547 -- The MRC table for which rows have been purged
548 -- p_MRC_NoOfRecordsIns IN NUMBER,
549 -- No. of records inserted into the MRC archive table
550 -- p_MRC_NoOfRecordsDel IN NUMBER
551 -- No. of records deleted from MRC table
552 -- End of comments
553 Procedure CommitProcess(p_purge_batch_id IN NUMBER,
554 p_project_id IN NUMBER,
555 p_table_name IN VARCHAR2,
556 p_NoOfRecordsIns IN NUMBER,
557 p_NoOfRecordsDel IN NUMBER,
558 x_err_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
559 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
560 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
561 p_MRC_table_name IN VARCHAR2 DEFAULT NULL,
562 p_MRC_NoOfRecordsIns IN NUMBER DEFAULT NULL,
563 p_MRC_NoOfRecordsDel IN NUMBER DEFAULT NULL
564 ) Is
565
566 l_old_err_stack VARCHAR2(2000); -- Increased the size from 1024 to 2000. Bug 4104182.
567 l_dummy VARCHAR2(1);
568 Begin
569
570 l_old_err_stack := x_err_stack;
571
572 X_err_stack := X_err_stack ||'->pa_purge.CommitProcess';
573 X_err_stack := X_err_stack ||'Batch Id: '||p_purge_batch_id || 'Project Id: '||p_project_id ;
574
575 pa_debug.debug(X_err_stack);
576
577 -- update the pa_project_details with statistics
578 pa_debug.debug('Before update of pa_project_details' );
579
580 Update PA_PURGE_PRJ_DETAILS
581 Set num_recs_purged = nvl(num_recs_purged,0) + nvl(p_NoOfRecordsDel,0)
582 ,num_recs_archived = nvl(num_recs_archived,0) + nvl(p_NoOfRecordsIns,0)
586 ,program_id = fnd_global.conc_program_id
583 ,last_update_date=sysdate
584 ,last_updated_by=fnd_global.user_id
585 ,last_update_login=fnd_global.login_id
587 ,program_application_id = fnd_global.prog_appl_id
588 ,request_id = fnd_global.conc_request_id
589 ,program_update_date = sysdate
590 Where purge_batch_id = p_purge_batch_id
591 And project_id = p_project_id
592 And Table_name = p_table_name;
593
594 pa_debug.debug('After update of pa_project_details' );
595 -- If row doesnt exist then insert new row with statistics
596 --
597 IF SQL%Rowcount = 0 Then
598
599 pa_debug.debug('Before insert into pa_project_details' );
600 Insert into PA_PURGE_PRJ_DETAILS
601 (
602 purge_batch_id,
603 project_id,
604 table_name,
605 num_recs_purged,
606 num_recs_archived,
607 created_by,
608 last_update_date,
609 last_updated_by,
610 last_update_login,
611 creation_date,
612 program_id,
613 program_application_id,
614 request_id,
615 program_update_date
616 )
617 values
618 (
619 p_purge_batch_id,
620 p_project_id,
621 p_table_name,
622 p_NoOfRecordsDel,
623 p_NoOfRecordsIns,
624 fnd_global.user_id,
625 sysdate,
626 fnd_global.user_id,
627 fnd_global.user_id,
628 sysdate,
629 fnd_global.conc_program_id,
630 fnd_global.prog_appl_id,
631 fnd_global.conc_request_id,
632 sysdate
633 );
634
635 pa_debug.debug('After insert into pa_project_details' );
636 END IF;
637
638 IF p_MRC_table_name is not null then
639 X_err_stack := X_err_stack ||'->pa_purge.CommitProcess';
640 X_err_stack := X_err_stack ||'Batch Id: '||p_purge_batch_id || 'Project Id: '||
641 p_project_id || 'MRC details' ;
642
643 pa_debug.debug(X_err_stack);
644
645 -- update the pa_project_details with MRC statistics
646 pa_debug.debug('Before update of pa_project_details for MRC' );
647
648 Update PA_PURGE_PRJ_DETAILS
649 Set num_recs_purged = nvl(num_recs_purged,0) + nvl(p_MRC_NoOfRecordsDel,0)
650 ,num_recs_archived = nvl(num_recs_archived,0) + nvl(p_MRC_NoOfRecordsIns,0)
651 ,last_update_date=sysdate
652 ,last_updated_by=fnd_global.user_id
653 ,last_update_login=fnd_global.login_id
654 ,program_id = fnd_global.conc_program_id
655 ,program_application_id = fnd_global.prog_appl_id
656 ,request_id = fnd_global.conc_request_id
657 ,program_update_date = sysdate
658 Where purge_batch_id = p_purge_batch_id
659 And project_id = p_project_id
660 And Table_name = p_MRC_table_name;
661
662 pa_debug.debug('After MRC update of pa_project_details' );
663 -- If row doesnt exist then insert new row with statistics
664 --
665 IF SQL%Rowcount = 0 Then
666
667 pa_debug.debug('Before insert MRC into pa_project_details' );
668 Insert into PA_PURGE_PRJ_DETAILS
669 (
670 purge_batch_id,
671 project_id,
672 table_name,
673 num_recs_purged,
674 num_recs_archived,
675 created_by,
676 last_update_date,
677 last_updated_by,
678 last_update_login,
679 creation_date,
680 program_id,
681 program_application_id,
682 request_id,
683 program_update_date
684 )
685 values
686 (
687 p_purge_batch_id,
688 p_project_id,
689 p_MRC_table_name,
690 p_MRC_NoOfRecordsDel,
691 p_MRC_NoOfRecordsIns,
692 fnd_global.user_id,
693 sysdate,
694 fnd_global.user_id,
695 fnd_global.user_id,
696 sysdate,
697 fnd_global.conc_program_id,
698 fnd_global.prog_appl_id,
699 fnd_global.conc_request_id,
700 sysdate
701 );
702
703 pa_debug.debug('After insert MRC into pa_project_details' );
704 END IF;
705 END IF;
706
707 commit;
708
709 -- Get the lock again after commit
710 --
711 /* Added the condition for the bug#2510609 */
712 if p_table_name not in ('PA_FORECAST_ITEMS', 'PA_FORECAST_ITEM_DETAILS', 'PA_FI_AMOUNT_DETAILS') then
713
714 pa_debug.debug('Before locking pa_purge_batches again');
715
716 Select 'x'
717 Into l_dummy
718 From pa_purge_batches
719 Where purge_batch_id = p_purge_batch_id
720 and request_id = fnd_global.conc_request_id
721 For update;
722
723 pa_debug.debug('After locking pa_purge_batches again');
724
725 end if;
726
730 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
727 x_err_stack := l_old_err_stack;
728
729 Exception
731 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
732
733 WHEN OTHERS THEN
734 pa_debug.debug('Error Procedure Name := PA_PURGE.CommitProcess' );
735 pa_debug.debug('Error stage is '||x_err_stage );
736 pa_debug.debug('Error stack is '||x_err_stack );
737 pa_debug.debug(SQLERRM);
738 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
739
740 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
741 end CommitProcess;
742
743 -- Start of comments
744 -- API name : get_post_purge_status
745 -- Type : Private
746 -- Pre-reqs : None
747 -- Function : This function checks if the project is fully purged or
748 -- partially purged for close projects and returns
749 -- Returns the project status code ' Fully_Purged' or 'Partially_Purged'
750 -- For active projects returns the old project status code
751 -- Parameters
752 -- p_project_Id IN NUMBER,
753 -- The project id for which purge status is to be determined
754 -- p_batch_id IN NUMBER,
755 -- The purge batch id
756 -- End of comments
757 Function get_post_purge_status ( p_project_id IN NUMBER, p_batch_id IN NUMBER)
758 Return VARCHAR2 IS
759
760 l_purge_actuals_flag VARCHAR2(1);
761 l_purge_capital_flag VARCHAR2(1);
762 l_purge_budgets_flag VARCHAR2(1);
763 l_purge_summary_flag VARCHAR2(1);
764 l_active_closed_flag VARCHAR2(1);
765
766 /* Changed the data length of l_next_p_project_status_code and l_next_pp_project_status_code to 90 for UTF8.
767 l_last_project_status_code VARCHAR2(30);
768 */
769 l_last_project_status_code pa_purge_projects.Last_project_status_code%TYPE;
770 l_project_type_class_code VARCHAR2(30);
771
772
773 CURSOR next_p_pp_status IS
774 SELECT next_p_project_status_code, next_pp_project_status_code
775 FROM pa_purge_projects
776 WHERE purge_batch_id = p_batch_id
777 AND project_id = p_project_id;
778
779 /* Changed the data length of l_next_p_project_status_code and l_next_pp_project_status_code to 90 for UTF8.
780 l_next_p_project_status_code VARCHAR2(30);
781 l_next_pp_project_status_code VARCHAR2(30);
782 */
783
784 l_next_p_project_status_code pa_purge_projects.next_p_project_status_code%TYPE;
785 l_next_pp_project_status_code pa_purge_projects.next_pp_project_status_code%TYPE;
786
787 BEGIN
788
789 pa_debug.debug(' In function get_post_purge_status ');
790
791 Select active_closed_flag
792 into l_active_closed_flag
793 from pa_purge_batches
794 where purge_batch_id = p_batch_id;
795
796 IF l_active_closed_flag = 'C' THEN
797 -- Select MAX of the flags for all rows of the project
798 -- across all the purge batches that have been processed
799 -- If the all the flags are 'Y' then the status is 'PURGED'
800 -- else 'PARTIALLY_PURGED'
801 --
802 Select MAX(purge_actuals_flag),
803 MAX(purge_capital_flag),
804 MAX(purge_budgets_flag),
805 MAX(purge_summary_flag)
806 Into l_purge_actuals_flag,
807 l_purge_capital_flag,
808 l_purge_budgets_flag,
809 l_purge_summary_flag
810 From pa_purge_projects
811 Where project_id = p_project_id
812 And purged_date is not null;
813
814 OPEN next_p_pp_status;
815 FETCH next_p_pp_status into l_next_p_project_status_code,
816 l_next_pp_project_status_code;
817 CLOSE next_p_pp_status;
818
819 -- Here if the Project is not a Capital Project then "purge_capital_flag" On pa_purge_projects
820 -- Will not be Y and will return the status as 'PARTIALLY PURGED' as there is no question
821 -- of purging any capital data for non capital project. So is project is NON CAPITAL then
822 -- traeting this as it has been purged for capital data completely.
823
824 Select pt.project_type_class_code
825 into l_project_type_class_code
826 From pa_projects_all p, pa_project_types_all pt
827 where p.project_type = pt.project_type
828 and p.org_id = pt.org_id -- Removed NVL for bug#5908179 by vvjoshi
829 and p.project_id = p_project_id;
830
831 IF l_project_type_class_code <> 'CAPITAL' THEN
832 l_purge_capital_flag := 'Y';
833 END IF;
834
835 If (l_purge_actuals_flag = 'N'
836 OR l_purge_capital_flag = 'N'
837 OR l_purge_summary_flag = 'N') THEN
838
839 Return(l_next_pp_project_status_code);
840
841 Else
842
843 Return(l_next_p_project_status_code);
844
845
846 End if;
847 ELSE
848 Select LAST_PROJECT_STATUS_CODE
849 Into l_last_project_status_code
850 From pa_purge_projects
851 Where project_id = p_project_id
852 And purge_batch_id = p_batch_id;
853
854 Return (l_last_project_status_code);
855
856 END IF;
857
858 exception
859 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
860 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
861
862 WHEN OTHERS THEN
863 pa_debug.debug('Error functio Name := PA_PURGE.get_post_purge_status ' );
864 pa_debug.debug(SQLERRM);
865 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
866
867 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
868
869 end get_post_purge_status ;
870
871 end pa_purge ; /*Package Body*/
[Home] [Help]