1 package body pa_purge_summary as
2 /* $Header: PAXSUPRB.pls 120.1.12010000.2 2009/06/23 14:24:29 atshukla ship $ */
3
4 l_commit_size NUMBER ;
5 g_def_proj_accum_id NUMBER;
6
7 -- private procedures
8 --
9 -- The list of parameters is common for all private procedures in the package
10 ------------------------------------------------------------------------------------------
11 -- Parameters p_batch_id IN NUMBER -- The purge batch id
12 -- for which rows have
13 -- to be purged/archived.
14 -- p_project_Id IN NUMBER -- The project id for
15 -- which records have
16 -- to be purged/archived.
17 -- p_Purge_Release IN VARCHAR2 -- Oracle Projects release(10.7,11.0)
18 -- p_Archive_Flag IN VARCHAR2 -- Archive table data
19 -- p_Txn_To_Date IN DATE -- Date on or before which all
20 -- transactions are to be purged
21 -- (Will be used by Costing only)
22 -- p_Commit_Size IN NUMBER -- The commit size
23 -- X_Err_Stack IN OUT VARCHAR2 -- Error stack
24 -- X_Err_Stage IN OUT VARCHAR2 -- Stage in the procedure where
25 -- error occurred
26 -- X_Err_Code IN OUT NUMBER -- Error code returned from the procedure
27 -- = 0 SUCCESS
28 -- > 0 Application error
29 -- < 0 Oracle error
30 -------------------------------------------------------------------------------------------
31 -- Start of comments
32 -- API name : PA_PROJACCUMHEADERS
33 -- Type : Private
34 -- Pre-reqs : None
35 -- Function : Archive and Purge data for table PA_PROJECT_ACCUM_HEADERS
36 -- Parameters : See common list above
37 -- End of comments
38
39 PROCEDURE pa_projaccumheaders
40 ( p_purge_batch_id IN NUMBER,
41 p_project_id IN NUMBER,
42 p_txn_to_date IN DATE,
43 p_purge_release IN VARCHAR2,
44 p_archive_flag IN VARCHAR2,
45 p_commit_size IN NUMBER,
46 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
47 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
48 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ) is
50
51 l_old_err_stage VARCHAR2(2000);
52 l_old_err_stack VARCHAR2(2000);
53 l_NoOfRecordsIns NUMBER;
54 l_NoOfRecordsDel NUMBER;
55
56 BEGIN
57
58 l_old_err_stack := x_err_stack;
59
60 x_err_stack := x_err_stack || ' ->Entering PA_PROJACCUMHEADERS ';
61
62 pa_debug.debug(x_err_stack);
63
64 LOOP
65 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
66 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
67
68 IF p_archive_flag = 'Y' THEN
69 -- If archive option is selected then the records are
70 -- archived into the archive table before being purged.
71 -- The WHERE condition is such that half the no. of records
72 -- specified in commit size are inserted into the archive
73 -- table and same number deleted from the original table
74
75 l_commit_size := p_commit_size / 2 ;
76
77
78 pa_debug.debug( ' ->Before insert into PA_PRJ_ACCUM_HEADERS_AR') ;
79
80 INSERT INTO PA_PRJ_ACCUM_HEADERS_AR
81 (
82 PURGE_BATCH_ID,
83 PURGE_RELEASE,
84 PURGE_PROJECT_ID,
85 PROJECT_ACCUM_ID,
86 PROJECT_ID,
87 TASK_ID,
88 ACCUM_PERIOD,
89 RESOURCE_ID,
90 RESOURCE_LIST_ASSIGNMENT_ID,
91 RESOURCE_LIST_ID,
92 RESOURCE_LIST_MEMBER_ID,
93 LAST_UPDATED_BY,
94 LAST_UPDATE_DATE,
95 CREATION_DATE,
96 CREATED_BY,
97 LAST_UPDATE_LOGIN,
98 REQUEST_ID,
99 PROGRAM_APPLICATION_ID,
100 PROGRAM_ID,
101 PROGRAM_UPDATE_DATE,
102 TASKS_RESTRUCTURED_FLAG,
103 SUM_EXCEPTION_CODE
104 )
105 SELECT
106 p_purge_batch_id,
107 p_purge_release,
108 p_project_id,
109 pah.PROJECT_ACCUM_ID,
110 pah.PROJECT_ID,
111 pah.TASK_ID,
112 pah.ACCUM_PERIOD,
113 pah.RESOURCE_ID,
114 pah.RESOURCE_LIST_ASSIGNMENT_ID,
115 pah.RESOURCE_LIST_ID,
116 pah.RESOURCE_LIST_MEMBER_ID,
117 pah.LAST_UPDATED_BY,
118 pah.LAST_UPDATE_DATE,
119 pah.CREATION_DATE,
120 pah.CREATED_BY,
121 pah.LAST_UPDATE_LOGIN,
122 pah.REQUEST_ID,
123 pah.PROGRAM_APPLICATION_ID,
124 pah.PROGRAM_ID,
125 pah.PROGRAM_UPDATE_DATE,
126 pah.TASKS_RESTRUCTURED_FLAG,
127 pah.SUM_EXCEPTION_CODE
128 FROM pa_project_accum_headers pah
129 WHERE ( pah.project_id = p_project_id
130 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
131 AND rownum <= l_commit_size
132 ) ;
133
134 /*Code Changes for Bug No.2984871 start */
135 l_NoOfRecordsIns := SQL%ROWCOUNT ;
136 /*Code Changes for Bug No.2984871 end */
137
138 pa_debug.debug( ' ->After insert into PA_PRJ_ACCUM_HEADERS_AR') ;
139
140 /* Commented for Bug 2984871
141
142 l_NoOfRecordsIns := SQL%ROWCOUNT ; */
143
144 /* Commented for Bug 2984871
145 IF SQL%ROWCOUNT > 0 THEN */
146
147 /*Code Changes for Bug No.2984871 start */
148 IF l_NoOfRecordsIns> 0 THEN
149 /*Code Changes for Bug No.2984871 end */
150 -- The algorithm for deleting records from original table
151 -- depends on whether records are being archived or not.
152
153 pa_debug.debug( ' ->Before delete from pa_project_accum_headers ') ;
154
155 /* commented and modified as below for performance reasons. Archive Purge 11.5
156 DELETE FROM pa_project_accum_headers pah
157 WHERE (pah.rowid) IN
158 ( SELECT pah1.rowid
159 FROM pa_project_accum_headers pah1,
160 PA_PRJ_ACCUM_HEADERS_AR pah2
161 WHERE pah1.project_accum_id = pah2.project_accum_id
162 AND pah2.purge_project_id = p_project_id
163 ) ;
164 */
165
166 DELETE FROM pa_project_accum_headers pah
167 WHERE (pah.project_accum_id) IN
168 ( SELECT pah2.project_accum_id
169 FROM PA_PRJ_ACCUM_HEADERS_AR pah2
170 WHERE pah2.purge_project_id = p_project_id
171 ) ;
172
173 /*Code Changes for Bug No.2984871 start */
174 l_NoOfRecordsDel := SQL%ROWCOUNT ;
175 /*Code Changes for Bug No.2984871 end */
176
177 pa_debug.debug( ' ->After delete from pa_project_accum_headers ') ;
178
179 END IF ;
180 ELSE
181
182 l_commit_size := p_commit_size ;
183
184 -- If the archive option is not selected then the delete will
185 -- be based on the commit size.
186
187
188 pa_debug.debug( ' ->Before delete from pa_project_accum_headers ') ;
189 /* commented and modified as below for performance reasons. Archive Purge 11.5
190 DELETE FROM pa_project_accum_headers pah
191 WHERE (pah.rowid) IN
192 ( SELECT pah.rowid
193 FROM pa_project_accum_headers pah
194 WHERE pah.project_id = p_project_id
195 AND rownum <= l_commit_size
196 ) ;
197 */
198
199 DELETE FROM pa_project_accum_headers pah
200 WHERE pah.project_id = p_project_id
201 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
202 AND rownum <= l_commit_size;
203
204
205 /*Code Changes for Bug No.2984871 start */
206 l_NoOfRecordsDel := SQL%ROWCOUNT ;
207 /*Code Changes for Bug No.2984871 end */
208 pa_debug.debug( ' ->After delete from pa_project_accum_headers ') ;
209 END IF ;
210
211 /* Commented for Bug 2984871
212 IF SQL%ROWCOUNT = 0 THEN*/
213
214 /*Code Changes for Bug No.2984871 start */
215 IF l_NoOfRecordsDel= 0 THEN
216 /*Code Changes for Bug No.2984871 end */
217 -- SqlCount = 0 means there are no more records to be purged
218 exit ;
219
220 ELSE
221 -- After "deleting" or "deleting and inserting" a set of records
222 -- the transaction is commited. This also creates a record in the
223 -- Pa_Purge_Project_details which will show the no. of records
224 -- that are purged from each table.
225
226 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
227
228 pa_purge.CommitProcess
229 (p_purge_batch_id => p_purge_batch_id,
230 p_project_id => p_project_id,
231 p_table_name => 'PA_PROJECT_ACCUM_HEADERS',
232 p_NoOfRecordsIns => l_NoOfRecordsIns,
233 p_NoOfRecordsDel => l_NoOfRecordsDel,
234 x_err_code => x_err_code,
235 x_err_stack => x_err_stack,
236 x_err_stage => x_err_stage
237 ) ;
238
239
240 END IF ;
241 END LOOP ;
242
243 x_err_stack := l_old_err_stack ;
244
245 EXCEPTION
246 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
247 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
248
249 WHEN OTHERS THEN
250 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_PROJACCUMHEADERS' );
251 pa_debug.debug('Error stage is '||x_err_stage );
252 pa_debug.debug('Error stack is '||x_err_stack );
253 pa_debug.debug(SQLERRM);
254 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
255
256 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
257
258 END pa_projaccumheaders ;
259
260 -- Start of comments
261 -- API name : PA_ProjAccumCommitments
262 -- Type : Private
263 -- Pre-reqs : None
264 -- Function : Archive and Purge data for table PA_PROJECT_ACCUM_COMMITMENTS
265 -- Parameters : See common list above
266 -- End of comments
267
268 PROCEDURE PA_ProjAccumCommitments
269 ( p_purge_batch_id IN NUMBER,
270 p_project_id IN NUMBER,
271 p_txn_to_date IN DATE,
272 p_purge_release IN VARCHAR2,
273 p_archive_flag IN VARCHAR2,
274 p_commit_size IN NUMBER,
275 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
276 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
277 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
278 ) is
279
280 l_old_err_stage VARCHAR2(2000);
281 l_old_err_stack VARCHAR2(2000);
282 l_NoOfRecordsIns NUMBER;
283 l_NoOfRecordsDel NUMBER;
284 BEGIN
285
286 l_old_err_stack := x_err_stack;
287
288 x_err_stack := x_err_stack || ' Entering PA_ProjAccumCommitments ' ;
289
290 pa_debug.debug(x_err_stack);
291
292 LOOP
293 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
294 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
295 IF p_archive_flag = 'Y' THEN
296 -- If archive option is selected then the records are
297 -- archived into the archive table before being purged.
298 -- The WHERE condition is such that half the no. of records
299 -- specified in commit size are inserted into the archive
300 -- table and same number deleted from the original table
301
302 l_commit_size := p_commit_size / 2 ;
303
304 pa_debug.debug( ' ->Before insert into PA_Project_Accum_Commitments') ;
305
306 -- The archive table name is different from the original table to limit
307 -- table name to 30 characters.
308
309 INSERT INTO PA_PRJ_ACCUM_COMMIT_AR
310 (
311 PURGE_BATCH_ID,
312 PURGE_RELEASE,
313 PURGE_PROJECT_ID,
314 PROJECT_ACCUM_ID,
315 CMT_RAW_COST_ITD,
316 CMT_RAW_COST_YTD,
317 CMT_RAW_COST_PP,
318 CMT_RAW_COST_PTD,
319 CMT_BURDENED_COST_ITD,
320 CMT_BURDENED_COST_YTD,
321 CMT_BURDENED_COST_PP,
322 CMT_BURDENED_COST_PTD,
323 CMT_QUANTITY_ITD,
324 CMT_QUANTITY_YTD,
325 CMT_QUANTITY_PP,
326 CMT_QUANTITY_PTD,
327 CMT_UNIT_OF_MEASURE,
328 LAST_UPDATED_BY,
329 LAST_UPDATE_DATE,
330 CREATION_DATE,
331 CREATED_BY,
332 LAST_UPDATE_LOGIN,
333 REQUEST_ID,
334 PROGRAM_APPLICATION_ID,
335 PROGRAM_ID,
336 PROGRAM_UPDATE_DATE
337 )
338 SELECT
339 p_purge_batch_id,
340 p_purge_release,
341 p_project_id,
342 pac.PROJECT_ACCUM_ID,
343 pac.CMT_RAW_COST_ITD,
344 pac.CMT_RAW_COST_YTD,
345 pac.CMT_RAW_COST_PP,
346 pac.CMT_RAW_COST_PTD,
347 pac.CMT_BURDENED_COST_ITD,
348 pac.CMT_BURDENED_COST_YTD,
349 pac.CMT_BURDENED_COST_PP,
350 pac.CMT_BURDENED_COST_PTD,
351 pac.CMT_QUANTITY_ITD,
352 pac.CMT_QUANTITY_YTD,
353 pac.CMT_QUANTITY_PP,
354 pac.CMT_QUANTITY_PTD,
355 pac.CMT_UNIT_OF_MEASURE,
356 pac.LAST_UPDATED_BY,
357 pac.LAST_UPDATE_DATE,
358 pac.CREATION_DATE,
359 pac.CREATED_BY,
360 pac.LAST_UPDATE_LOGIN,
361 pac.REQUEST_ID,
362 pac.PROGRAM_APPLICATION_ID,
363 pac.PROGRAM_ID,
364 pac.PROGRAM_UPDATE_DATE
365 FROM pa_project_accum_commitments pac
366 /* commented and modified as below for performance reasons. Archive Purge 11.5
367 WHERE (pac.rowid) IN
368 ( SELECT pac1.rowid
369 FROM pa_project_accum_commitments pac1,
370 pa_project_accum_headers pah
371 WHERE pac1.project_accum_id=pah.project_accum_id
372 AND pah.project_id = p_project_id
373 AND rownum < l_commit_size
374 ) ;
375 */
376 WHERE (pac.project_accum_id) in
377 ( SELECT pah.project_accum_id
378 FROM pa_project_accum_headers pah
379 WHERE pah.project_id = p_project_id
380 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
381 AND rownum < l_commit_size
382 ) ;
383
384 /*Code Changes for Bug No.2984871 start */
385 l_NoOfRecordsIns := SQL%ROWCOUNT ;
386 /*Code Changes for Bug No.2984871 end */
387
388 pa_debug.debug( ' ->After insert into PA_Project_Accum_Commitments') ;
389
390 /*Code Changes for Bug No.2984871 start */
391 IF l_NoOfRecordsIns > 0 THEN
392 /*Code Changes for Bug No.2984871 end*/
393 -- The algorithm for deleting records from original table
394 -- depends on whether records are being archived or not.
395 pa_debug.debug( ' ->Before delete from pa_project_accum_commitments ') ;
396 /* commented and modified as below for performance reasons. Archive Purge 11.5
397 DELETE FROM pa_project_accum_commitments PAC
398 WHERE (pac.rowid) IN
399 ( SELECT pac1.rowid
400 FROM pa_project_accum_commitments pac1,
401 PA_PRJ_ACCUM_COMMIT_AR pac2
402 WHERE pac1.project_accum_id = pac2.project_accum_id
403 AND pac2.purge_project_id=p_project_id
404 ) ;
405 */
406 DELETE FROM pa_project_accum_commitments PAC
407 WHERE (pac.project_accum_id) IN
408 ( SELECT pac2.project_accum_id
409 FROM PA_PRJ_ACCUM_COMMIT_AR pac2
410 WHERE pac2.purge_project_id=p_project_id
411 ) ;
412 /*Code Changes for Bug No.2984871 start */
413 l_NoOfRecordsDel := SQL%ROWCOUNT ;
414 /*Code Changes for Bug No.2984871 end */
415 pa_debug.debug( ' ->After delete from pa_project_accum_commitments ') ;
416 END IF ;
417 ELSE
418
419 l_commit_size := p_commit_size ;
420
421 -- If the archive option is not selected then the delete will
422 -- be based on the commit size.
423
424 pa_debug.debug( ' ->Before delete from pa_project_accum_commitments ') ;
425 /* commented and modified as below for performance reasons. Archive Purge 11.5
426 DELETE FROM pa_project_accum_commitments pac
427 WHERE (pac.rowid) IN
428 ( SELECT pac1.rowid
429 FROM pa_project_accum_commitments pac1,
430 pa_project_accum_headers pah
431 WHERE pac1.project_accum_id=pah.project_accum_id
432 AND pah.project_id = p_project_id
433 AND rownum <= l_commit_size
434 ) ;
435 */
436
437 DELETE FROM pa_project_accum_commitments pac
438 WHERE (pac.project_accum_id) IN
439 ( SELECT pah.project_accum_id
440 FROM pa_project_accum_headers pah
441 WHERE pah.project_id = p_project_id
442 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
443 AND rownum <= l_commit_size
444 ) ;
445 /*Code Changes for Bug No.2984871 start */
446 l_NoOfRecordsDel := SQL%ROWCOUNT ;
447 /*Code Changes for Bug No.2984871 end */
448
449 pa_debug.debug( ' ->After delete from pa_project_accum_commitments ') ;
450 END IF ;
451
452 /*Code Changes for Bug No.2984871 start */
453 IF l_NoOfRecordsDel = 0 THEN
454 /*Code Changes for Bug No.2984871 end */
455 -- SqlCount = 0 means there are no more records to be purged
456
457 exit ;
458
459 ELSE
460 -- After "deleting" or "deleting and inserting" a set of records
461 -- the transaction is commited. This also creates a record in the
462 -- Pa_Purge_Project_details which will show the no. of records
463 -- that are purged from each table.
464
465 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
466 pa_purge.CommitProcess
467 (p_purge_batch_id => p_purge_batch_id,
468 p_project_id => p_project_id,
469 p_table_name => 'PA_PROJECT_ACCUM_COMMITMENTS',
470 p_NoOfRecordsIns => l_NoOfRecordsIns,
471 p_NoOfRecordsDel => l_NoOfRecordsDel,
472 x_err_code => x_err_code,
473 x_err_stack => x_err_stack,
474 x_err_stage => x_err_stage
475 ) ;
476 END IF ;
477 END LOOP ;
478
479
480 x_err_stack := l_old_err_stack ;
481 EXCEPTION
482 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
483 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
484
485 WHEN OTHERS THEN
486 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ProjAccumCommitments' );
487 pa_debug.debug('Error stage is '||x_err_stage );
488 pa_debug.debug('Error stack is '||x_err_stack );
489 pa_debug.debug(SQLERRM);
490 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
491
492 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
493
494 END pa_projaccumcommitments ;
495
496 -- Start of comments
497 -- API name : PA_ProjAccumBudgets
498 -- Type : Private
499 -- Pre-reqs : None
500 -- Function : Archive and Purge data for table PA_Project_Accum_Budgets
501 -- Parameters : See common list above
502 -- End of comments
503 PROCEDURE pa_projaccumbudgets
504 ( p_purge_batch_id IN NUMBER,
505 p_project_id IN NUMBER,
506 p_txn_to_date IN DATE,
507 p_purge_release IN VARCHAR2,
508 p_archive_flag IN VARCHAR2,
509 p_commit_size IN NUMBER,
510 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
511 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
512 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
513 ) IS
514
515 l_old_err_stage VARCHAR2(2000);
516 l_old_err_stack VARCHAR2(2000);
517 l_NoOfRecordsIns NUMBER;
518 l_NoOfRecordsDel NUMBER;
519 BEGIN
520
521 l_old_err_stack := x_err_stack;
522
523 x_err_stack := x_err_stack || ' ->Entering PA_ProjAccumBudgets' ;
524
525 pa_debug.debug(x_err_stack);
526
527 LOOP
528 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
529 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
530 IF p_archive_flag = 'Y' THEN
531 -- If archive option is selected then the records are
532 -- archived into the archive table before being purged.
533 -- The WHERE condition is such that half the no. of records
534 -- specified in commit size are inserted into the archive
535 -- table and same number deleted from the original table
536
537 l_commit_size := p_commit_size / 2 ;
538
539
540 pa_debug.debug( ' ->Before insert into PA_PRJ_ACCUM_BUDGETS_AR') ;
541
542 INSERT INTO PA_PRJ_ACCUM_BUDGETS_AR
543 (
544 PURGE_BATCH_ID,
545 PURGE_RELEASE,
546 PURGE_PROJECT_ID,
547 PROJECT_ACCUM_ID,
548 BUDGET_TYPE_CODE,
549 BASE_RAW_COST_ITD,
550 BASE_RAW_COST_YTD,
551 BASE_RAW_COST_PP,
552 BASE_RAW_COST_PTD,
553 BASE_BURDENED_COST_ITD,
554 BASE_BURDENED_COST_YTD,
555 BASE_BURDENED_COST_PP,
556 BASE_BURDENED_COST_PTD,
557 ORIG_RAW_COST_ITD,
558 ORIG_RAW_COST_YTD,
559 ORIG_RAW_COST_PP,
560 ORIG_RAW_COST_PTD,
561 ORIG_BURDENED_COST_ITD,
562 ORIG_BURDENED_COST_YTD,
563 ORIG_BURDENED_COST_PP,
564 ORIG_BURDENED_COST_PTD,
565 BASE_REVENUE_ITD,
566 BASE_REVENUE_YTD,
567 BASE_REVENUE_PP,
568 BASE_REVENUE_PTD,
569 ORIG_REVENUE_ITD,
570 ORIG_REVENUE_YTD,
571 ORIG_REVENUE_PP,
572 ORIG_REVENUE_PTD,
573 ORIG_LABOR_HOURS_ITD,
574 ORIG_LABOR_HOURS_YTD,
575 ORIG_LABOR_HOURS_PP,
576 ORIG_LABOR_HOURS_PTD,
577 BASE_LABOR_HOURS_ITD,
578 BASE_LABOR_HOURS_YTD,
579 BASE_LABOR_HOURS_PP,
580 BASE_LABOR_HOURS_PTD,
581 ORIG_QUANTITY_YTD,
582 ORIG_QUANTITY_ITD,
583 ORIG_QUANTITY_PP,
584 ORIG_QUANTITY_PTD,
585 BASE_QUANTITY_YTD,
586 BASE_QUANTITY_ITD,
587 BASE_QUANTITY_PP,
588 BASE_QUANTITY_PTD,
589 ORIG_LABOR_HOURS_TOT,
590 BASE_LABOR_HOURS_TOT,
591 ORIG_QUANTITY_TOT,
592 BASE_QUANTITY_TOT,
593 BASE_RAW_COST_TOT,
594 BASE_BURDENED_COST_TOT,
595 ORIG_RAW_COST_TOT,
596 ORIG_BURDENED_COST_TOT,
597 BASE_REVENUE_TOT,
598 ORIG_REVENUE_TOT,
599 BASE_UNIT_OF_MEASURE,
600 ORIG_UNIT_OF_MEASURE,
601 LAST_UPDATED_BY,
602 LAST_UPDATE_DATE,
603 CREATION_DATE,
604 CREATED_BY,
605 LAST_UPDATE_LOGIN,
606 REQUEST_ID,
607 PROGRAM_APPLICATION_ID,
608 PROGRAM_ID,
609 PROGRAM_UPDATE_DATE
610 )
611 SELECT
612 p_purge_batch_id,
613 p_purge_release,
614 p_project_id,
615 PROJECT_ACCUM_ID,
616 BUDGET_TYPE_CODE,
617 BASE_RAW_COST_ITD,
618 BASE_RAW_COST_YTD,
619 BASE_RAW_COST_PP,
620 BASE_RAW_COST_PTD,
621 BASE_BURDENED_COST_ITD,
622 BASE_BURDENED_COST_YTD,
623 BASE_BURDENED_COST_PP,
624 BASE_BURDENED_COST_PTD,
625 ORIG_RAW_COST_ITD,
626 ORIG_RAW_COST_YTD,
627 ORIG_RAW_COST_PP,
628 ORIG_RAW_COST_PTD,
629 ORIG_BURDENED_COST_ITD,
630 ORIG_BURDENED_COST_YTD,
631 ORIG_BURDENED_COST_PP,
632 ORIG_BURDENED_COST_PTD,
633 BASE_REVENUE_ITD,
634 BASE_REVENUE_YTD,
635 BASE_REVENUE_PP,
636 BASE_REVENUE_PTD,
637 ORIG_REVENUE_ITD,
638 ORIG_REVENUE_YTD,
639 ORIG_REVENUE_PP,
640 ORIG_REVENUE_PTD,
641 ORIG_LABOR_HOURS_ITD,
642 ORIG_LABOR_HOURS_YTD,
643 ORIG_LABOR_HOURS_PP,
644 ORIG_LABOR_HOURS_PTD,
645 BASE_LABOR_HOURS_ITD,
646 BASE_LABOR_HOURS_YTD,
647 BASE_LABOR_HOURS_PP,
648 BASE_LABOR_HOURS_PTD,
649 ORIG_QUANTITY_YTD,
650 ORIG_QUANTITY_ITD,
651 ORIG_QUANTITY_PP,
652 ORIG_QUANTITY_PTD,
653 BASE_QUANTITY_YTD,
654 BASE_QUANTITY_ITD,
655 BASE_QUANTITY_PP,
656 BASE_QUANTITY_PTD,
657 ORIG_LABOR_HOURS_TOT,
658 BASE_LABOR_HOURS_TOT,
659 ORIG_QUANTITY_TOT,
660 BASE_QUANTITY_TOT,
661 BASE_RAW_COST_TOT,
662 BASE_BURDENED_COST_TOT,
663 ORIG_RAW_COST_TOT,
664 ORIG_BURDENED_COST_TOT,
665 BASE_REVENUE_TOT,
666 ORIG_REVENUE_TOT,
667 BASE_UNIT_OF_MEASURE,
668 ORIG_UNIT_OF_MEASURE,
669 LAST_UPDATED_BY,
670 LAST_UPDATE_DATE,
671 CREATION_DATE,
672 CREATED_BY,
673 LAST_UPDATE_LOGIN,
674 REQUEST_ID,
675 PROGRAM_APPLICATION_ID,
676 PROGRAM_ID,
677 PROGRAM_UPDATE_DATE
678 FROM pa_Project_Accum_Budgets pab
679 /* commented and modified as below for performance reasons. Archive Purge 11.5
680 WHERE (pab.rowid) IN
681 (SELECT pab1.rowid FROM pa_project_accum_budgets pab1,
682 Pa_project_accum_headers pah
683 WHERE pab1.project_accum_id=pah.project_accum_id
684 AND pah.project_id = p_project_id
685 AND rownum < l_commit_size
686 ) ;
687 */
688 WHERE (pab.project_accum_id) IN
689 (SELECT pah.project_accum_id
690 FROM Pa_project_accum_headers pah
691 WHERE pah.project_id = p_project_id
692 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
693 AND rownum < l_commit_size
694 ) ;
695 /*Code Changes for Bug No.2984871 start */
696 l_NoOfRecordsIns := SQL%ROWCOUNT ;
697 /*Code Changes for Bug No.2984871 end */
698
699 pa_debug.debug( ' ->After insert into PA_Project_Accum_Budgets') ;
700
701 /*Code Changes for Bug No.2984871 start */
702 IF l_NoOfRecordsIns > 0 THEN
703 /*Code Changes for Bug No.2984871 end */
704 -- The algorithm for deleting records from original table
705 -- depends on whether records are being archived or not.
706 -- If records are archived before purging, then the WHERE clause
707 -- joins the original and the archived table on the basis of a
708 -- unique key and uses rowid of records in original table to hit
709 -- the records to be deleted
710
711 pa_debug.debug( ' ->Before delete from pa_project_accum_budgets ') ;
712 /* commented and modified as below for performance reasons. Archive Purge 11.5
713 DELETE FROM pa_project_accum_budgets pab
714 WHERE (pab.rowid) IN
715 ( SELECT pab1.rowid
716 FROM pa_project_accum_budgets pab1,
717 PA_PRJ_ACCUM_BUDGETS_AR pab2
718 WHERE pab1.project_accum_id = pab2.project_accum_id
719 AND pab1.budget_type_code=pab2.budget_type_code
720 AND pab2.purge_project_id=p_project_id
721 ) ;
722 */
723
724 DELETE FROM pa_project_accum_budgets pab
725 WHERE (pab.project_accum_id) IN
726 ( SELECT pab2.project_accum_id
727 FROM PA_PRJ_ACCUM_BUDGETS_AR pab2
728 WHERE pab.budget_type_code=pab2.budget_type_code
729 AND pab2.purge_project_id=p_project_id
730 ) ;
731 /*Code Changes for Bug No.2984871 start */
732 l_NoOfRecordsDel := SQL%ROWCOUNT ;
733 /*Code Changes for Bug No.2984871 end */
734 pa_debug.debug( ' ->After delete from pa_project_accum_budgets ') ;
735 END IF ;
736 ELSE
737
738 l_commit_size := p_commit_size ;
739
740 -- If the archive option is not selected then the delete will
741 -- be based on the commit size.
742
743 pa_debug.debug( ' ->Before delete from pa_project_accum_budgets ') ;
744
745 --Fix for bug#7701114
746 DELETE FROM pa_project_accum_budgets ppab
747 WHERE (ppab.project_accum_id) IN (SELECT pab.project_accum_id
748 FROM pa_project_accum_commitments pab,
749 pa_project_accum_headers pah
750 WHERE pab.project_accum_id=pah.project_accum_id
751 AND pah.project_id = p_project_id
752 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id)
753 AND rownum <= l_commit_size;
754 /*Code Changes for Bug No.2984871 start */
755 l_NoOfRecordsDel := SQL%ROWCOUNT ;
756 /*Code Changes for Bug No.2984871 end */
757 pa_debug.debug( ' ->After delete from pa_project_accum_budgets ') ;
758 END IF ;
759
760 IF l_NoOfRecordsDel = 0 THEN
761 -- no more records to be purged then we exit the loop.
762
763 exit ;
764
765 ELSE
766 -- After "deleting" or "deleting and inserting" a set of records
767 -- the transaction is commited. This also creates a record in the
768 -- Pa_Purge_Project_details which will show the no. of records
769 -- that are purged from each table.
770
771 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
772 pa_purge.CommitProcess
773 (p_purge_batch_id => p_purge_batch_id,
774 p_project_id => p_project_id,
775 p_table_name => 'PA_PROJECT_ACCUM_BUDGETS',
776 p_NoOfRecordsIns => l_NoOfRecordsIns,
777 p_NoOfRecordsDel => l_NoOfRecordsDel,
778 x_err_code => x_err_code,
779 x_err_stack => x_err_stack,
780 x_err_stage => x_err_stage
781 ) ;
782 END IF ;
783 END LOOP ;
784
785
786 x_err_stack := l_old_err_stack ;
787 EXCEPTION
788 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
789 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
790
791 WHEN OTHERS THEN
792 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ProjAccumBudgets');
793 pa_debug.debug('Error stage is '||x_err_stage );
794 pa_debug.debug('Error stack is '||x_err_stack );
795 pa_debug.debug(SQLERRM);
796 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
797
798 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
799
800
801 END pa_projaccumbudgets ;
802
803 -- Start of comments
804 -- API name : PA_ProjAccumActuals
805 -- Type : Private
806 -- Pre-reqs : None
807 -- Function : Archive and Purge data for table PA_Project_Accum_Actuals
808 -- Parameters : See common list above
809 -- End of comments
810 PROCEDURE pa_projaccumactuals
811 ( p_purge_batch_id IN NUMBER,
812 p_project_id IN NUMBER,
813 p_txn_to_date IN DATE,
814 p_purge_release IN VARCHAR2,
815 p_archive_flag IN VARCHAR2,
816 p_commit_size IN NUMBER,
817 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
818 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
819 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
820 ) IS
821
822 l_old_err_stage VARCHAR2(2000);
823 l_old_err_stack VARCHAR2(2000);
824 l_NoOfRecordsIns NUMBER;
825 l_NoOfRecordsDel NUMBER;
826 BEGIN
827
828 l_old_err_stack := x_err_stack;
829
830 x_err_stack := x_err_stack || ' ->Entering PA_ProjAccumActuals' ;
831
832 pa_debug.debug(x_err_stack);
833
834 LOOP
835 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
836 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
837
838 IF p_archive_flag = 'Y' THEN
839 -- If archive option is selected then the records are
840 -- archived into the archive table before being purged.
841 -- The WHERE condition is such that half the no. of records
842 -- specified in commit size are inserted into the archive
843 -- table and same number deleted from the original table
844
845 l_commit_size := p_commit_size / 2 ;
846
847
848 pa_debug.debug( ' ->Before insert into PA_PRJ_ACCUM_ACTUALS_AR') ;
849
850 INSERT INTO PA_PRJ_ACCUM_ACTUALS_AR
851 (
852 PURGE_BATCH_ID,
853 PURGE_RELEASE,
854 PURGE_PROJECT_ID,
855 PROJECT_ACCUM_ID,
856 RAW_COST_ITD,
857 RAW_COST_YTD,
858 RAW_COST_PP,
859 RAW_COST_PTD,
860 BILLABLE_RAW_COST_ITD,
861 BILLABLE_RAW_COST_YTD,
862 BILLABLE_RAW_COST_PP,
863 BILLABLE_RAW_COST_PTD,
864 BURDENED_COST_ITD,
865 BURDENED_COST_YTD,
866 BURDENED_COST_PP,
867 BURDENED_COST_PTD,
868 BILLABLE_BURDENED_COST_ITD,
869 BILLABLE_BURDENED_COST_YTD,
870 BILLABLE_BURDENED_COST_PP,
871 BILLABLE_BURDENED_COST_PTD,
872 QUANTITY_ITD,
873 QUANTITY_YTD,
874 QUANTITY_PP,
875 QUANTITY_PTD,
876 LABOR_HOURS_ITD,
877 LABOR_HOURS_YTD,
878 LABOR_HOURS_PP,
879 LABOR_HOURS_PTD,
880 BILLABLE_QUANTITY_ITD,
881 BILLABLE_QUANTITY_YTD,
882 BILLABLE_QUANTITY_PP,
883 BILLABLE_QUANTITY_PTD,
884 BILLABLE_LABOR_HOURS_ITD,
885 BILLABLE_LABOR_HOURS_YTD,
886 BILLABLE_LABOR_HOURS_PP,
887 BILLABLE_LABOR_HOURS_PTD,
888 REVENUE_ITD,
889 REVENUE_YTD,
890 REVENUE_PP,
891 REVENUE_PTD,
892 TXN_UNIT_OF_MEASURE,
893 LAST_UPDATED_BY,
894 LAST_UPDATE_DATE,
895 CREATION_DATE,
896 CREATED_BY,
897 LAST_UPDATE_LOGIN,
898 REQUEST_ID,
899 PROGRAM_APPLICATION_ID,
900 PROGRAM_ID,
901 PROGRAM_UPDATE_DATE
902 )
903 SELECT
904 p_purge_batch_id,
905 p_purge_release,
906 p_project_id,
907 PROJECT_ACCUM_ID,
908 RAW_COST_ITD,
909 RAW_COST_YTD,
910 RAW_COST_PP,
911 RAW_COST_PTD,
912 BILLABLE_RAW_COST_ITD,
913 BILLABLE_RAW_COST_YTD,
914 BILLABLE_RAW_COST_PP,
915 BILLABLE_RAW_COST_PTD,
916 BURDENED_COST_ITD,
917 BURDENED_COST_YTD,
918 BURDENED_COST_PP,
919 BURDENED_COST_PTD,
920 BILLABLE_BURDENED_COST_ITD,
921 BILLABLE_BURDENED_COST_YTD,
922 BILLABLE_BURDENED_COST_PP,
923 BILLABLE_BURDENED_COST_PTD,
924 QUANTITY_ITD,
925 QUANTITY_YTD,
926 QUANTITY_PP,
927 QUANTITY_PTD,
928 LABOR_HOURS_ITD,
929 LABOR_HOURS_YTD,
930 LABOR_HOURS_PP,
931 LABOR_HOURS_PTD,
932 BILLABLE_QUANTITY_ITD,
933 BILLABLE_QUANTITY_YTD,
934 BILLABLE_QUANTITY_PP,
935 BILLABLE_QUANTITY_PTD,
936 BILLABLE_LABOR_HOURS_ITD,
937 BILLABLE_LABOR_HOURS_YTD,
938 BILLABLE_LABOR_HOURS_PP,
939 BILLABLE_LABOR_HOURS_PTD,
940 REVENUE_ITD,
941 REVENUE_YTD,
942 REVENUE_PP,
943 REVENUE_PTD,
944 TXN_UNIT_OF_MEASURE,
945 LAST_UPDATED_BY,
946 LAST_UPDATE_DATE,
947 CREATION_DATE,
948 CREATED_BY,
949 LAST_UPDATE_LOGIN,
950 REQUEST_ID,
951 PROGRAM_APPLICATION_ID,
952 PROGRAM_ID,
953 PROGRAM_UPDATE_DATE
954 FROM pa_Project_Accum_Actuals paa
955 /* commented and modified as below for performance reasons. Archive Purge 11.5
956 WHERE (paa.rowid) IN
957 (SELECT paa1.rowid FROM pa_project_accum_actuals paa1,
958 pa_project_accum_headers pah
959 WHERE paa1.project_accum_id=pah.project_accum_id
960 AND pah.project_id = p_project_id
961 AND rownum < l_commit_size
962 ) ;
963 */
964 WHERE (paa.project_accum_id) IN
965 (SELECT pah.project_accum_id
966 FROM pa_project_accum_headers pah
967 WHERE pah.project_id = p_project_id
968 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
969 AND rownum < l_commit_size
970 ) ;
971
972 /*Code Changes for Bug No.2984871 start */
973 l_NoOfRecordsIns := SQL%ROWCOUNT ;
974 /*Code Changes for Bug No.2984871 end */
975
976 pa_debug.debug( ' ->After insert into PA_Project_Accum_Actuals') ;
977
978 IF l_NoOfRecordsIns > 0 THEN
979 -- The algorithm for deleting records from original table
980 -- depends on whether records are being archived or not.
981 -- If records are archived before purging, then the WHERE clause
982 -- joins the original and the archived table on the basis of a
983 -- unique key and uses rowid of records in original table to hit
984 -- the records to be deleted
985
986 pa_debug.debug( ' ->Before delete from pa_project_accum_actuals ') ;
987 /* commented and modified as below for performance reasons. Archive Purge 11.5
988 DELETE FROM pa_project_accum_actuals paa
989 WHERE (paa.rowid) IN
990 ( SELECT paa1.rowid
991 FROM pa_project_accum_Actuals paa1,
992 PA_PRJ_ACCUM_ACTUALS_AR paa2
993 WHERE paa1.project_accum_id = paa2.project_accum_id
994 AND paa2.purge_project_id = p_project_id
995 ) ;
996 */
997 DELETE FROM pa_project_accum_actuals paa
998 WHERE (paa.project_accum_id) IN
999 ( SELECT paa2.project_accum_id
1000 FROM PA_PRJ_ACCUM_ACTUALS_AR paa2
1001 WHERE paa2.purge_project_id = p_project_id
1002 ) ;
1003 /*Code Changes for Bug No.2984871 start */
1004 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1005 /*Code Changes for Bug No.2984871 end*/
1006
1007 pa_debug.debug( ' ->After delete from pa_project_accum_actuals ') ;
1008 END IF ;
1009 ELSE
1010
1011 l_commit_size := p_commit_size ;
1012
1013 -- If the archive option is not selected then the delete will
1014 -- be based on the commit size.
1015
1016 pa_debug.debug( ' ->Before delete from pa_project_accum_actuals ') ;
1017 /* commented and modified as below for performance reasons. Archive Purge 11.5
1018 DELETE from pa_project_accum_actuals paa
1019 WHERE (paa.rowid) IN
1020 ( SELECT paa1.rowid
1021 FROM pa_project_accum_actuals paa1,
1022 pa_project_accum_headers pah
1023 WHERE paa1.project_accum_id=pah.project_accum_id
1024 AND pah.project_id = p_project_id
1025 AND rownum <= l_commit_size
1026 ) ;
1027 */
1028 DELETE from pa_project_accum_actuals paa
1029 WHERE (paa.project_accum_id) IN
1030 ( SELECT pah.project_accum_id
1031 FROM pa_project_accum_headers pah
1032 WHERE pah.project_id = p_project_id
1033 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
1034 AND rownum <= l_commit_size
1035 ) ;
1036
1037 /*Code Changes for Bug No.2984871 start */
1038 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1039 /*Code Changes for Bug No.2984871 end */
1040
1041 pa_debug.debug( ' ->After delete from pa_project_accum_actuals ') ;
1042 END IF ;
1043
1044 IF l_NoOfRecordsDel = 0 THEN
1045
1046 exit ;
1047
1048 ELSE
1049 -- After "deleting" or "deleting and inserting" a set of records
1050 -- the transaction is commited. This also creates a record in the
1051 -- Pa_Purge_Project_details which will show the no. of records
1052 -- that are purged from each table.
1053
1054 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1055 pa_purge.CommitProcess
1056 (p_purge_batch_id => p_purge_batch_id,
1057 p_project_id => p_project_id,
1058 p_table_name => 'PA_PROJECT_ACCUM_ACTUALS',
1059 p_NoOfRecordsIns => l_NoOfRecordsIns,
1060 p_NoOfRecordsDel => l_NoOfRecordsDel,
1061 x_err_code => x_err_code,
1062 x_err_stack => x_err_stack,
1063 x_err_stage => x_err_stage
1064 ) ;
1065 END IF ;
1066 END LOOP ;
1067
1068
1069 x_err_stack := l_old_err_stack ;
1070 EXCEPTION
1071 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1072 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1073
1074 WHEN OTHERS THEN
1075 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ProjAccumActuals');
1076 pa_debug.debug('Error stage is '||x_err_stage );
1077 pa_debug.debug('Error stack is '||x_err_stack );
1078 pa_debug.debug(SQLERRM);
1079 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1080
1081 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1082
1083
1084 END pa_projaccumactuals ;
1085
1086 -- Start of comments
1087 -- API name : PA_ResAccumDetails
1088 -- Type : Private
1089 -- Pre-reqs : None
1090 -- Function : Archive and Purge data for table PA_Resource_Accum_Details
1091 -- Parameters : See common list above
1092 -- End of comments
1093 PROCEDURE pa_resaccumdetails
1094 ( p_purge_batch_id IN NUMBER,
1095 p_project_id IN NUMBER,
1096 p_txn_to_date IN DATE,
1097 p_purge_release IN VARCHAR2,
1098 p_archive_flag IN VARCHAR2,
1099 p_commit_size IN NUMBER,
1100 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1101 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1102 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1103 ) is
1104
1105 l_old_err_stage VARCHAR2(2000);
1106 l_old_err_stack VARCHAR2(2000);
1107 l_NoOfRecordsIns NUMBER;
1108 l_NoOfRecordsDel NUMBER;
1109 BEGIN
1110
1111 l_old_err_stack := x_err_stack;
1112
1113 x_err_stack := x_err_stack || ' ->Entering PA_ResAccumDetails' ;
1114
1115 pa_debug.debug(x_err_stack);
1116
1117 LOOP
1118 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1119 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1120 IF p_archive_flag = 'Y' THEN
1121 -- If archive option is selected then the records are
1122 -- archived into the archive table before being purged.
1123 -- The WHERE condition is such that half the no. of records
1124 -- specified in commit size are inserted into the archive
1125 -- table and same number deleted from the original table
1126
1127 l_commit_size := p_commit_size / 2 ;
1128
1129
1130 pa_debug.debug( ' ->Before insert into PA_RES_ACCUM_DETAILS_AR') ;
1131
1132 INSERT INTO PA_RES_ACCUM_DETAILS_AR
1133 (
1134 PURGE_BATCH_ID,
1135 PURGE_RELEASE,
1136 PURGE_PROJECT_ID,
1137 TXN_ACCUM_ID,
1138 RESOURCE_LIST_ASSIGNMENT_ID,
1139 RESOURCE_LIST_ID,
1140 RESOURCE_LIST_MEMBER_ID,
1141 RESOURCE_ID,
1142 PROJECT_ID,
1143 TASK_ID,
1144 LAST_UPDATED_BY,
1145 LAST_UPDATE_DATE,
1146 CREATION_DATE,
1147 CREATED_BY,
1148 LAST_UPDATE_LOGIN,
1149 REQUEST_ID,
1150 PROGRAM_APPLICATION_ID,
1151 PROGRAM_ID,
1152 PROGRAM_UPDATE_DATE,
1153 ADW_NOTIFY_FLAG
1154 )
1155 SELECT
1156 p_purge_batch_id,
1157 p_purge_release,
1158 p_project_id,
1159 TXN_ACCUM_ID,
1160 RESOURCE_LIST_ASSIGNMENT_ID,
1161 RESOURCE_LIST_ID,
1162 RESOURCE_LIST_MEMBER_ID,
1163 RESOURCE_ID,
1164 PROJECT_ID,
1165 TASK_ID,
1166 LAST_UPDATED_BY,
1167 LAST_UPDATE_DATE,
1168 CREATION_DATE,
1169 CREATED_BY,
1170 LAST_UPDATE_LOGIN,
1171 REQUEST_ID,
1172 PROGRAM_APPLICATION_ID,
1173 PROGRAM_ID,
1174 PROGRAM_UPDATE_DATE,
1175 ADW_NOTIFY_FLAG
1176 FROM pa_Resource_Accum_Details pad
1177 WHERE pad.project_id = p_project_id
1178 AND rownum < l_commit_size;
1179 /*Code Changes for Bug No.2984871 start */
1180 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1181 /*Code Changes for Bug No.2984871 end */
1182
1183 pa_debug.debug( ' ->After insert into PA_RES_ACCUM_DETAILS_AR') ;
1184
1185 /*Code Changes for Bug No.2984871 start */
1186 IF l_NoOfRecordsIns > 0 THEN
1187 /*Code Changes for Bug No.2984871 end */
1188 -- The algorithm for deleting records from original table
1189 -- depends on whether records are being archived or not.
1190 -- If records are archived before purging, then the WHERE clause
1191 -- joins the original and the archived table on the basis of a
1192 -- unique key and uses rowid of records in original table to hit
1193 -- the records to be deleted
1194
1195 pa_debug.debug( ' ->Before delete from pa_resource_accum_details ') ;
1196 /* commented and modified as below for performance reasons. Archive Purge 11.5
1197 DELETE FROM pa_resource_accum_details pad
1198 WHERE (pad.rowid) IN
1199 ( SELECT pad1.rowid
1200 FROM pa_resource_accum_details pad1,
1201 PA_RES_ACCUM_DETAILS_AR pad2
1202 WHERE pad1.txn_accum_id = pad2.txn_accum_id
1203 AND pad1.resource_list_assignment_id =
1204 pad2.resource_list_assignment_id
1205 AND pad2.purge_project_id=p_project_id
1206 ) ;
1207 */
1208 DELETE FROM pa_resource_accum_details pad
1209 WHERE (pad.txn_accum_id, pad.resource_list_assignment_id) IN
1210 ( SELECT pad2.txn_accum_id, pad2.resource_list_assignment_id
1211 FROM PA_RES_ACCUM_DETAILS_AR pad2
1212 WHERE pad2.purge_project_id=p_project_id
1213 ) ;
1214 /*Code Changes for Bug No.2984871 start */
1215 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1216 /*Code Changes for Bug No.2984871 end */
1217
1218 pa_debug.debug( ' ->After delete from pa_resource_accum_details ') ;
1219 END IF ;
1220 ELSE
1221
1222 l_commit_size := p_commit_size ;
1223
1224 -- If the archive option is not selected then the delete will
1225 -- be based on the commit size.
1226
1227 pa_debug.debug( ' ->Before delete from pa_resource_accum_details ') ;
1228 /* commented and modified as below for performance reasons. Archive Purge 11.5
1229 DELETE FROM pa_resource_accum_details pad
1230 WHERE (pad.rowid) IN
1231 ( SELECT pad.rowid
1232 FROM pa_resource_accum_details pad
1233 WHERE pad.project_id = p_project_id
1234 AND rownum <= l_commit_size
1235 ) ;
1236 */
1237 DELETE FROM pa_resource_accum_details pad
1238 WHERE pad.project_id = p_project_id
1239 AND rownum <= l_commit_size;
1240 /*Code Changes for Bug No.2984871 start */
1241 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1242 /*Code Changes for Bug No.2984871 end */
1243 pa_debug.debug( ' ->After delete from pa_resource_accum_details ') ;
1244 END IF ;
1245
1246 /*Code Changes for Bug No.2984871 start */
1247 IF l_NoOfRecordsDel = 0 THEN
1248 /*Code Changes for Bug No.2984871 end*/
1249 -- Once the SqlCount becomes 0, which means that there are
1250 -- no more records to be purged then we exit the loop.
1251
1252 exit ;
1253
1254 ELSE
1255 -- After "deleting" or "deleting and inserting" a set of records
1256 -- the transaction is commited. This also creates a record in the
1257 -- Pa_Purge_Project_details which will show the no. of records
1258 -- that are purged from each table.
1259
1260 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1261 pa_purge.CommitProcess
1262 (p_purge_batch_id => p_purge_batch_id,
1263 p_project_id => p_project_id,
1264 p_table_name => 'PA_RESOURCE_ACCUM_DETAILS',
1265 p_NoOfRecordsIns => l_NoOfRecordsIns,
1266 p_NoOfRecordsDel => l_NoOfRecordsDel,
1267 x_err_code => x_err_code,
1268 x_err_stack => x_err_stack,
1269 x_err_stage => x_err_stage
1270 ) ;
1271 END IF ;
1272 END LOOP ;
1273
1274
1275 x_err_stack := l_old_err_stack ;
1276 EXCEPTION
1277 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1278 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1279
1280 WHEN OTHERS THEN
1281 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ResAccumDetails');
1282 pa_debug.debug('Error stage is '||x_err_stage );
1283 pa_debug.debug('Error stack is '||x_err_stack );
1284 pa_debug.debug(SQLERRM);
1285 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1286
1287 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1288
1289
1290 END pa_resaccumdetails ;
1291
1292
1293 -- Start of comments
1294 -- API name : PA_TxnAccum
1295 -- Type : Private
1296 -- Pre-reqs : None
1297 -- Function : Archive and Purge data for table PA_TxnAccum
1298 -- Parameters : See common list above
1299 -- End of comments
1300 PROCEDURE pa_txnaccum
1301 ( p_purge_batch_id IN NUMBER,
1302 p_project_id IN NUMBER,
1303 p_txn_to_date IN DATE,
1304 p_purge_release IN VARCHAR2,
1305 p_archive_flag IN VARCHAR2,
1306 p_commit_size IN NUMBER,
1307 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1308 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1309 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1310 ) IS
1311
1312 l_old_err_stage VARCHAR2(2000);
1313 l_old_err_stack VARCHAR2(2000);
1314 l_NoOfRecordsIns NUMBER;
1315 l_NoOfRecordsDel NUMBER;
1316 BEGIN
1317
1318 l_old_err_stack := x_err_stack;
1319
1320 x_err_stack := x_err_stack || ' ->Entering PA_TxnAccum' ;
1321
1322 pa_debug.debug(x_err_stack);
1323
1324 LOOP
1325 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1326 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1327 IF p_archive_flag = 'Y' THEN
1328 -- If archive option is selected then the records are
1329 -- archived into the archive table before being purged.
1330 -- The WHERE condition is such that half the no. of records
1331 -- specified in commit size are inserted into the archive
1332 -- table and same number deleted from the original table
1333
1334 l_commit_size := p_commit_size / 2 ;
1335
1336
1337 pa_debug.debug( ' ->Before insert into PA_Txn_Accum_AR') ;
1338
1339 INSERT INTO PA_Txn_Accum_AR
1340 (
1341 PURGE_BATCH_ID,
1342 PURGE_RELEASE,
1343 PURGE_PROJECT_ID,
1344 TXN_ACCUM_ID,
1345 PERSON_ID,
1346 JOB_ID,
1347 ORGANIZATION_ID,
1348 VENDOR_ID,
1349 EXPENDITURE_TYPE,
1350 EVENT_TYPE,
1351 NON_LABOR_RESOURCE,
1352 EXPENDITURE_CATEGORY,
1353 REVENUE_CATEGORY,
1354 NON_LABOR_RESOURCE_ORG_ID,
1355 EVENT_TYPE_CLASSIFICATION,
1356 SYSTEM_LINKAGE_FUNCTION,
1357 PROJECT_ID,
1358 TASK_ID,
1359 PA_PERIOD,
1360 GL_PERIOD,
1361 MONTH_ENDING_DATE,
1362 WEEK_ENDING_DATE,
1363 TOT_REVENUE,
1364 TOT_RAW_COST,
1365 TOT_BURDENED_COST,
1366 TOT_QUANTITY,
1367 TOT_LABOR_HOURS,
1368 TOT_BILLABLE_RAW_COST,
1369 TOT_BILLABLE_BURDENED_COST,
1370 TOT_BILLABLE_QUANTITY,
1371 TOT_BILLABLE_LABOR_HOURS,
1372 TOT_CMT_RAW_COST,
1373 TOT_CMT_BURDENED_COST,
1374 TOT_CMT_QUANTITY,
1375 I_TOT_REVENUE,
1376 I_TOT_RAW_COST,
1377 I_TOT_BURDENED_COST,
1378 I_TOT_QUANTITY,
1379 I_TOT_LABOR_HOURS,
1380 I_TOT_BILLABLE_RAW_COST,
1381 I_TOT_BILLABLE_BURDENED_COST,
1382 I_TOT_BILLABLE_QUANTITY,
1383 I_TOT_BILLABLE_LABOR_HOURS,
1384 COST_IND_COMPILED_SET_ID,
1385 REV_IND_COMPILED_SET_ID,
1386 INV_IND_COMPILED_SET_ID,
1387 CMT_IND_COMPILED_SET_ID,
1388 UNIT_OF_MEASURE,
1389 ACTUAL_COST_ROLLUP_FLAG,
1390 REVENUE_ROLLUP_FLAG,
1391 CMT_ROLLUP_FLAG,
1392 LAST_UPDATED_BY,
1393 LAST_UPDATE_DATE,
1394 CREATION_DATE,
1395 CREATED_BY,
1396 LAST_UPDATE_LOGIN,
1397 REQUEST_ID,
1398 PROGRAM_APPLICATION_ID,
1399 PROGRAM_ID,
1400 PROGRAM_UPDATE_DATE,
1401 ADW_NOTIFY_FLAG
1402 )
1403 SELECT
1404 p_purge_batch_id,
1405 p_purge_release,
1406 p_project_id,
1407 TXN_ACCUM_ID,
1408 PERSON_ID,
1409 JOB_ID,
1410 ORGANIZATION_ID,
1411 VENDOR_ID,
1412 EXPENDITURE_TYPE,
1413 EVENT_TYPE,
1414 NON_LABOR_RESOURCE,
1415 EXPENDITURE_CATEGORY,
1416 REVENUE_CATEGORY,
1417 NON_LABOR_RESOURCE_ORG_ID,
1418 EVENT_TYPE_CLASSIFICATION,
1419 SYSTEM_LINKAGE_FUNCTION,
1420 PROJECT_ID,
1421 TASK_ID,
1422 PA_PERIOD,
1423 GL_PERIOD,
1424 MONTH_ENDING_DATE,
1425 WEEK_ENDING_DATE,
1426 TOT_REVENUE,
1427 TOT_RAW_COST,
1428 TOT_BURDENED_COST,
1429 TOT_QUANTITY,
1430 TOT_LABOR_HOURS,
1431 TOT_BILLABLE_RAW_COST,
1432 TOT_BILLABLE_BURDENED_COST,
1433 TOT_BILLABLE_QUANTITY,
1434 TOT_BILLABLE_LABOR_HOURS,
1435 TOT_CMT_RAW_COST,
1436 TOT_CMT_BURDENED_COST,
1437 TOT_CMT_QUANTITY,
1438 I_TOT_REVENUE,
1439 I_TOT_RAW_COST,
1440 I_TOT_BURDENED_COST,
1441 I_TOT_QUANTITY,
1442 I_TOT_LABOR_HOURS,
1443 I_TOT_BILLABLE_RAW_COST,
1444 I_TOT_BILLABLE_BURDENED_COST,
1445 I_TOT_BILLABLE_QUANTITY,
1446 I_TOT_BILLABLE_LABOR_HOURS,
1447 COST_IND_COMPILED_SET_ID,
1448 REV_IND_COMPILED_SET_ID,
1449 INV_IND_COMPILED_SET_ID,
1450 CMT_IND_COMPILED_SET_ID,
1451 UNIT_OF_MEASURE,
1452 ACTUAL_COST_ROLLUP_FLAG,
1453 REVENUE_ROLLUP_FLAG,
1454 CMT_ROLLUP_FLAG,
1455 LAST_UPDATED_BY,
1456 LAST_UPDATE_DATE,
1457 CREATION_DATE,
1458 CREATED_BY,
1459 LAST_UPDATE_LOGIN,
1460 REQUEST_ID,
1461 PROGRAM_APPLICATION_ID,
1462 PROGRAM_ID,
1463 PROGRAM_UPDATE_DATE,
1464 ADW_NOTIFY_FLAG
1465 FROM pa_txn_accum ta
1466 WHERE ta.project_id = p_project_id
1467 AND rownum < l_commit_size;
1468 /*Code Changes for Bug No.2984871 start */
1469 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1470 /*Code Changes for Bug No.2984871 end */
1471
1472 pa_debug.debug( ' ->After insert into PA_Txn_Accum_AR') ;
1473
1474 /*Code Changes for Bug No.2984871 start */
1475 IF l_NoOfRecordsIns > 0 THEN
1476 /*Code Changes for Bug No.2984871 end */
1477 -- The algorithm for deleting records from original table
1478 -- depends on whether records are being archived or not.
1479 -- If records are archived before purging, then the WHERE clause
1480 -- joins the original and the archived table on the basis of a
1481 -- unique key and uses rowid of records in original table to hit
1482 -- the records to be deleted
1483
1484 pa_debug.debug( ' ->Before delete from pa_txn_accum ') ;
1485 /* commented and modified as below for performance reasons. Archive Purge 11.5
1486 DELETE FROM pa_txn_accum ta
1487 WHERE (ta.rowid) IN
1488 ( SELECT ta1.rowid
1489 FROM pa_txn_accum ta1,
1490 pa_txn_accum_ar ta2
1491 WHERE ta1.txn_accum_id = ta2.txn_accum_id
1492 AND ta2.purge_project_id = p_project_id
1493 ) ;
1494 */
1495 DELETE FROM pa_txn_accum ta
1496 WHERE (ta.txn_accum_id) IN
1497 ( SELECT ta2.txn_accum_id
1498 FROM pa_txn_accum_ar ta2
1499 WHERE ta2.purge_project_id = p_project_id
1500 ) ;
1501 /*Code Changes for Bug No.2984871 start */
1502 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1503 /*Code Changes for Bug No.2984871 end */
1504
1505 pa_debug.debug( ' ->After delete from pa_txn_accum ') ;
1506 END IF ;
1507 ELSE
1508
1509 l_commit_size := p_commit_size ;
1510
1511 -- If the archive option is not selected then the delete will
1512 -- be based on the commit size.
1513
1514 pa_debug.debug( ' ->Before delete from pa_txn_accum ') ;
1515 /* commented and modified as below for performance reasons. Archive Purge 11.5
1516 DELETE FROM pa_txn_accum ta
1517 WHERE (ta.rowid) IN
1518 ( SELECT ta.rowid
1519 FROM pa_txn_accum ta
1520 WHERE ta.project_id = p_project_id
1521 AND rownum <= l_commit_size
1522 ) ;
1523 */
1524 DELETE FROM pa_txn_accum ta
1525 WHERE ta.project_id = p_project_id
1526 AND rownum <= l_commit_size;
1527
1528 /*Code Changes for Bug No.2984871 start */
1529 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1530 /*Code Changes for Bug No.2984871 end */
1531 pa_debug.debug( ' ->After delete from pa_txn_accum ') ;
1532 END IF ;
1533
1534 IF l_NoOfRecordsDel = 0 THEN
1535 -- Once the SqlCount becomes 0, which means that there are
1536 -- no more records to be purged then we exit the loop.
1537
1538 exit ;
1539
1540 ELSE
1541 -- After "deleting" or "deleting and inserting" a set of records
1542 -- the transaction is commited. This also creates a record in the
1543 -- Pa_Purge_Project_details which will show the no. of records
1544 -- that are purged from each table.
1545
1546 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1547 pa_purge.CommitProcess
1548 (p_purge_batch_id => p_purge_batch_id,
1549 p_project_id => p_project_id,
1550 p_table_name => 'PA_TXN_ACCUM',
1551 p_NoOfRecordsIns => l_NoOfRecordsIns,
1552 p_NoOfRecordsDel => l_NoOfRecordsDel,
1553 x_err_code => x_err_code,
1554 x_err_stack => x_err_stack,
1555 x_err_stage => x_err_stage
1556 ) ;
1557 END IF ;
1558 END LOOP ;
1559
1560
1561 x_err_stack := l_old_err_stack ;
1562 EXCEPTION
1563 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1564 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1565
1566 WHEN OTHERS THEN
1567 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_TxnAccum');
1568 pa_debug.debug('Error stage is '||x_err_stage );
1569 pa_debug.debug('Error stack is '||x_err_stack );
1570 pa_debug.debug(SQLERRM);
1571 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1572
1573 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1574
1575 END pa_txnaccum ;
1576
1577 -- Start of comments
1578 -- API name : PA_TxnAccumDetails
1579 -- Type : Private
1580 -- Pre-reqs : None
1581 -- Function : Archive and Purge data for table PA_TxnAccumDetails
1582 -- Parameters : See common list above
1583 -- End of comments
1584
1585
1586 PROCEDURE pa_txnaccumdetails
1587 ( p_purge_batch_id IN NUMBER,
1588 p_project_id IN NUMBER,
1589 p_txn_to_date IN DATE,
1590 p_purge_release IN VARCHAR2,
1591 p_archive_flag IN VARCHAR2,
1592 p_commit_size IN NUMBER,
1593 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1594 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1595 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1596 ) IS
1597
1598 l_old_err_stage VARCHAR2(2000);
1599 l_old_err_stack VARCHAR2(2000);
1600 l_NoOfRecordsIns NUMBER;
1601 l_NoOfRecordsDel NUMBER;
1602 BEGIN
1603
1604 l_old_err_stack := x_err_stack;
1605
1606 x_err_stack := x_err_stack || ' ->Entering PA_TxnAccumDetails' ;
1607
1608 pa_debug.debug(x_err_stack);
1609
1610 LOOP
1611 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1612 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1613 IF p_archive_flag = 'Y' THEN
1614 -- If archive option is selected then the records are
1615 -- archived into the archive table before being purged.
1616 -- The WHERE condition is such that half the no. of records
1617 -- specified in commit size are inserted into the archive
1618 -- table and same number deleted from the original table
1619
1620 l_commit_size := p_commit_size / 2 ;
1621
1622
1623 pa_debug.debug( ' ->Before insert into PA_Txn_Accum_Details_AR') ;
1624
1625 -- Modified insert statement to use project_id from pa_txn_accum to select rows from
1626 -- pa_txn_accum_details as project_id may be null for some detail lines
1627 -- project_id is a not null column in pa_txn_accum.
1628
1629 INSERT INTO PA_Txn_Accum_Details_AR
1630 (
1631 PURGE_BATCH_ID,
1632 PURGE_RELEASE,
1633 PURGE_PROJECT_ID,
1634 ORIGINAL_ROWID,
1635 TXN_ACCUM_ID,
1636 LINE_TYPE,
1637 EXPENDITURE_ITEM_ID,
1638 EVENT_NUM,
1639 LINE_NUM,
1640 PROJECT_ID,
1641 TASK_ID,
1642 CMT_LINE_ID,
1643 LAST_UPDATED_BY,
1644 LAST_UPDATE_DATE,
1645 CREATION_DATE,
1646 CREATED_BY,
1647 LAST_UPDATE_LOGIN,
1648 REQUEST_ID,
1649 PROGRAM_APPLICATION_ID,
1650 PROGRAM_ID,
1651 PROGRAM_UPDATE_DATE
1652 )
1653 SELECT
1654 p_purge_batch_id,
1655 p_purge_release,
1656 p_project_id,
1657 tad.ROWID,
1658 tad.TXN_ACCUM_ID,
1659 tad.LINE_TYPE,
1660 tad.EXPENDITURE_ITEM_ID,
1661 tad.EVENT_NUM,
1662 tad.LINE_NUM,
1663 tad.PROJECT_ID,
1664 tad.TASK_ID,
1665 tad.CMT_LINE_ID,
1666 tad.LAST_UPDATED_BY,
1667 tad.LAST_UPDATE_DATE,
1668 tad.CREATION_DATE,
1669 tad.CREATED_BY,
1670 tad.LAST_UPDATE_LOGIN,
1671 tad.REQUEST_ID,
1672 tad.PROGRAM_APPLICATION_ID,
1673 tad.PROGRAM_ID,
1674 tad.PROGRAM_UPDATE_DATE
1675 FROM pa_txn_accum_details tad,
1676 pa_txn_accum pta
1677 WHERE tad.txn_accum_id = pta.txn_accum_id
1678 AND pta.project_id = p_project_id
1679 AND rownum < l_commit_size;
1680
1681
1682 /*Code Changes for Bug No.2984871 start */
1683 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1684 /*Code Changes for Bug No.2984871 end */
1685
1686 pa_debug.debug( ' ->After insert into PA_Txn_Accum_Details_AR') ;
1687
1688 IF l_NoOfRecordsIns > 0 THEN
1689 -- The algorithm for deleting records from original table
1690 -- depends on whether records are being archived or not.
1691 -- If records are archived before purging, then the WHERE clause
1692 -- joins the original and the archived table on the basis of a
1693 -- unique key and uses rowid of records in original table to hit
1694 -- the records to be deleted
1695
1696 pa_debug.debug( ' ->Before delete from pa_txn_accum_details ') ;
1697
1698 DELETE FROM pa_txn_accum_details tad
1699 WHERE (tad.rowid) IN
1700 ( SELECT tad2.original_rowid
1701 FROM pa_txn_accum_details tad1,
1702 pa_txn_accum_details_ar tad2
1703 WHERE tad1.rowid = tad2.original_rowid
1704 -- AND tad1.project_id=tad2.project_id
1705 AND tad2.purge_project_id = p_project_id
1706 ) ;
1707 /*Code Changes for Bug No.2984871 start */
1708 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1709 /*Code Changes for Bug No.2984871 end */
1710 pa_debug.debug( ' ->After delete from pa_txn_accum_details ') ;
1711 END IF ;
1712 ELSE
1713
1714 l_commit_size := p_commit_size ;
1715
1716 -- If the archive option is not selected then the delete will
1717 -- be based on the commit size.
1718
1719 pa_debug.debug( ' ->Before delete from pa_txn_accum_details ') ;
1720 /* commented and modified as below for performance reasons. Archive Purge 11.5
1721 DELETE FROM pa_txn_accum_details tad
1722 WHERE (tad.rowid) IN
1723 ( SELECT tad1.rowid
1724 FROM pa_txn_accum_details tad1, pa_txn_accum pta
1725 WHERE tad1.txn_accum_id = pta.txn_accum_id
1726 AND pta.project_id = p_project_id
1727 AND rownum <= l_commit_size
1728 ) ;
1729 */
1730 --Fix for bug#7701114
1731 DELETE FROM pa_txn_accum_details tad
1732 WHERE (tad.txn_accum_id) IN
1733 ( SELECT pta.txn_accum_id
1734 FROM pa_txn_accum pta
1735 WHERE pta.project_id = p_project_id)
1736 AND rownum <= l_commit_size;
1737
1738
1739 /*Code Changes for Bug No.2984871 start */
1740 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1741 /*Code Changes for Bug No.2984871 end */
1742 pa_debug.debug( ' ->After delete from pa_txn_accum_details ') ;
1743 END IF ;
1744
1745 /*Code Changes for Bug No.2984871 start */
1746 IF l_NoOfRecordsDel = 0 THEN
1747 /*Code Changes for Bug No.2984871 end*/
1748 -- Once the SqlCount becomes 0, which means that there are
1749 -- no more records to be purged then we exit the loop.
1750
1751 exit ;
1752
1753 ELSE
1754 -- After "deleting" or "deleting and inserting" a set of records
1755 -- the transaction is commited. This also creates a record in the
1756 -- Pa_Purge_Project_details which will show the no. of records
1757 -- that are purged from each table.
1758
1759 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1760 pa_purge.CommitProcess
1761 (p_purge_batch_id => p_purge_batch_id,
1762 p_project_id => p_project_id,
1763 p_table_name => 'PA_TXN_ACCUM_DETAILS',
1764 p_NoOfRecordsIns => l_NoOfRecordsIns,
1765 p_NoOfRecordsDel => l_NoOfRecordsDel,
1766 x_err_code => x_err_code,
1767 x_err_stack => x_err_stack,
1768 x_err_stage => x_err_stage
1769 ) ;
1770 END IF ;
1771 END LOOP ;
1772
1773
1774 x_err_stack := l_old_err_stack ;
1775 EXCEPTION
1776 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1777 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1778
1779 WHEN OTHERS THEN
1780 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_TxnAccumDetails');
1781 pa_debug.debug('Error stage is '||x_err_stage );
1782 pa_debug.debug('Error stack is '||x_err_stack );
1783 pa_debug.debug(SQLERRM);
1784 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1785
1786 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1787
1788 END pa_txnaccumdetails;
1789
1790
1791 -- Start of comments
1792 -- API name : PA_Summary_Main_Purge
1793 -- Type : Public
1794 -- Pre-reqs : None
1795 -- Function : Main purge procedure for summarization tables.
1796 -- Calls a separate procedure to purge each summary table
1797 -- Parameters :
1798 -- l p_purge_batch_id -> Purge batch Id
1799 -- p_project_id -> Project Id
1800 -- p_purge_release -> The release during which it is
1801 -- purged
1802 -- p_archive_flag -> This flag will indicate if the
1803 -- records need to be archived
1804 -- before they are purged.
1805 -- p_txn_to_date -> Date through which the transactions
1806 -- need to be purged. This value will
1807 -- be NULL if the purge batch is for
1808 -- active projects.
1809 -- p_archive_flag -> set to 'Y' if summarization data
1810 -- is to be archived
1811 -- p_commit_size -> The maximum number of records that
1812 -- can be allowed to remain uncommited.
1813 -- If the number of records processed
1814 -- goes beyond this number then the
1815 -- process is commited.
1816 -- End of comments
1817
1818 PROCEDURE pa_summary_main_purge ( p_purge_batch_id in NUMBER,
1819 p_project_id in NUMBER,
1820 p_purge_release in VARCHAR2,
1821 p_txn_to_date in DATE,
1822 p_archive_flag in VARCHAR2,
1823 p_commit_size in NUMBER,
1824 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1825 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1826 x_err_code in OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1827 ) IS
1828
1829 l_old_err_stack VARCHAR2(2000);
1830
1831 BEGIN
1832 l_old_err_stack := x_err_stack;
1833 g_def_proj_accum_id := 0; /* 2485577 */
1834
1835 /* 2485577. When a new project is created default records gets
1836 inserted into summarization tables. These records should not be
1837 purged. */
1838
1839 Select Project_Accum_Id
1840 INTO g_def_proj_accum_id
1841 FROM PA_Project_Accum_Headers
1842 WHERE Project_Id = p_project_id
1843 AND Task_Id = 0
1844 AND Resource_Id = 0
1845 AND Resource_List_Assignment_Id = 0
1846 AND Resource_List_Id = 0
1847 AND Resource_List_Member_Id = 0
1848 AND rownum = 1;
1849
1850 x_err_stack := x_err_stack || ' ->Before call to purge summary data ';
1851
1852 pa_debug.debug('*-> About to purge Summary data ') ;
1853
1854 -- Call the procedures to archive/purge data for each summary table
1855 --
1856 pa_debug.debug('*-> About to purge PA_Project_Accum_Commitments ') ;
1857 pa_purge_summary.PA_ProjAccumCommitments
1858 (p_purge_batch_id => p_purge_batch_id,
1859 p_project_id => p_project_id,
1860 p_txn_to_date => p_txn_to_date,
1861 p_purge_release => p_purge_release,
1862 p_archive_flag => p_archive_flag,
1863 p_commit_size => p_commit_size,
1864 x_err_code => x_err_code,
1865 x_err_stack => x_err_stack,
1866 x_err_stage => x_err_stage
1867 ) ;
1868
1869 pa_debug.debug('*-> About to purge PA_Project_Accum_Actuals') ;
1870 pa_purge_summary.PA_ProjAccumActuals
1871 (p_purge_batch_id => p_purge_batch_id,
1872 p_project_id => p_project_id,
1873 p_txn_to_date => p_txn_to_date,
1874 p_purge_release => p_purge_release,
1875 p_archive_flag => p_archive_flag,
1876 p_commit_size => p_commit_size,
1877 x_err_code => x_err_code,
1878 x_err_stack => x_err_stack,
1879 x_err_stage => x_err_stage
1880 ) ;
1881
1882 pa_debug.debug('*-> About to purge PA_Project_Accum_Budgets ') ;
1883 pa_purge_summary.PA_ProjAccumBudgets
1884 (p_purge_batch_id => p_purge_batch_id,
1885 p_project_id => p_project_id,
1886 p_txn_to_date => p_txn_to_date,
1887 p_purge_release => p_purge_release,
1888 p_archive_flag => p_archive_flag,
1889 p_commit_size => p_commit_size,
1890 x_err_code => x_err_code,
1891 x_err_stack => x_err_stack,
1892 x_err_stage => x_err_stage
1893 ) ;
1894
1895 pa_debug.debug('*-> About to purge PA_Resource_Accum_Details ') ;
1896 pa_purge_summary.PA_ResAccumDetails
1897 (p_purge_batch_id => p_purge_batch_id,
1898 p_project_id => p_project_id,
1899 p_txn_to_date => p_txn_to_date,
1900 p_purge_release => p_purge_release,
1901 p_archive_flag => p_archive_flag,
1902 p_commit_size => p_commit_size,
1903 x_err_code => x_err_code,
1904 x_err_stack => x_err_stack,
1905 x_err_stage => x_err_stage
1906 ) ;
1907
1908 pa_debug.debug('*-> About to purge PA_Project_Accum_Headers ') ;
1909 pa_purge_summary.PA_ProjAccumHeaders
1910 (p_purge_batch_id => p_purge_batch_id,
1911 p_project_id => p_project_id,
1912 p_txn_to_date => p_txn_to_date,
1913 p_purge_release => p_purge_release,
1914 p_archive_flag => p_archive_flag,
1915 p_commit_size => p_commit_size,
1916 x_err_code => x_err_code,
1917 x_err_stack => x_err_stack,
1918 x_err_stage => x_err_stage
1919 ) ;
1920
1921 pa_debug.debug('*-> About to purge PA_Txn_Accum_Details') ;
1922 pa_purge_summary.PA_TxnAccumDetails
1923 (p_purge_batch_id => p_purge_batch_id,
1924 p_project_id => p_project_id,
1925 p_txn_to_date => p_txn_to_date,
1926 p_purge_release => p_purge_release,
1927 p_archive_flag => p_archive_flag,
1928 p_commit_size => p_commit_size,
1929 x_err_code => x_err_code,
1930 x_err_stack => x_err_stack,
1931 x_err_stage => x_err_stage
1932 ) ;
1933
1934 pa_debug.debug('*-> About to purge PA_Txn_Accum') ;
1935 pa_purge_summary.PA_TxnAccum
1936 (p_purge_batch_id => p_purge_batch_id,
1937 p_project_id => p_project_id,
1938 p_txn_to_date => p_txn_to_date,
1939 p_purge_release => p_purge_release,
1940 p_archive_flag => p_archive_flag,
1941 p_commit_size => p_commit_size,
1942 x_err_code => x_err_code,
1943 x_err_stack => x_err_stack,
1944 x_err_stage => x_err_stage
1945 ) ;
1946
1947
1948 x_err_stack := l_old_err_stack;
1949
1950 EXCEPTION
1951 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1952 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1953
1954 WHEN OTHERS THEN
1955 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.pa_summary_main_purge' );
1956 pa_debug.debug('Error stage is '||x_err_stage );
1957 pa_debug.debug('Error stack is '||x_err_stack );
1958 pa_debug.debug(SQLERRM);
1959 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1960
1961 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1962
1963 END pa_summary_main_purge ;
1964
1965 END pa_purge_summary;
[Home] [Help]