1 package body pa_purge_summary as
2 /* $Header: PAXSUPRB.pls 120.1 2005/08/19 17:21:00 mwasowic noship $ */
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 DELETE FROM pa_project_accum_budgets pab
746 WHERE (pab.rowid) IN
747 ( SELECT pab.rowid
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 ) ;
755 /*Code Changes for Bug No.2984871 start */
756 l_NoOfRecordsDel := SQL%ROWCOUNT ;
757 /*Code Changes for Bug No.2984871 end */
758 pa_debug.debug( ' ->After delete from pa_project_accum_budgets ') ;
759 END IF ;
760
761 IF l_NoOfRecordsDel = 0 THEN
762 -- no more records to be purged then we exit the loop.
763
764 exit ;
765
766 ELSE
767 -- After "deleting" or "deleting and inserting" a set of records
768 -- the transaction is commited. This also creates a record in the
769 -- Pa_Purge_Project_details which will show the no. of records
770 -- that are purged from each table.
771
772 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
773 pa_purge.CommitProcess
774 (p_purge_batch_id => p_purge_batch_id,
775 p_project_id => p_project_id,
776 p_table_name => 'PA_PROJECT_ACCUM_BUDGETS',
777 p_NoOfRecordsIns => l_NoOfRecordsIns,
778 p_NoOfRecordsDel => l_NoOfRecordsDel,
779 x_err_code => x_err_code,
780 x_err_stack => x_err_stack,
781 x_err_stage => x_err_stage
782 ) ;
783 END IF ;
784 END LOOP ;
785
786
787 x_err_stack := l_old_err_stack ;
788 EXCEPTION
789 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
790 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
791
792 WHEN OTHERS THEN
793 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ProjAccumBudgets');
794 pa_debug.debug('Error stage is '||x_err_stage );
795 pa_debug.debug('Error stack is '||x_err_stack );
796 pa_debug.debug(SQLERRM);
797 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
798
799 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
800
801
802 END pa_projaccumbudgets ;
803
804 -- Start of comments
805 -- API name : PA_ProjAccumActuals
806 -- Type : Private
807 -- Pre-reqs : None
808 -- Function : Archive and Purge data for table PA_Project_Accum_Actuals
809 -- Parameters : See common list above
810 -- End of comments
811 PROCEDURE pa_projaccumactuals
812 ( p_purge_batch_id IN NUMBER,
813 p_project_id IN NUMBER,
814 p_txn_to_date IN DATE,
815 p_purge_release IN VARCHAR2,
816 p_archive_flag IN VARCHAR2,
817 p_commit_size IN NUMBER,
818 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
819 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
820 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
821 ) IS
822
823 l_old_err_stage VARCHAR2(2000);
824 l_old_err_stack VARCHAR2(2000);
825 l_NoOfRecordsIns NUMBER;
826 l_NoOfRecordsDel NUMBER;
827 BEGIN
828
829 l_old_err_stack := x_err_stack;
830
831 x_err_stack := x_err_stack || ' ->Entering PA_ProjAccumActuals' ;
832
833 pa_debug.debug(x_err_stack);
834
835 LOOP
836 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
837 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
838
839 IF p_archive_flag = 'Y' THEN
840 -- If archive option is selected then the records are
841 -- archived into the archive table before being purged.
842 -- The WHERE condition is such that half the no. of records
843 -- specified in commit size are inserted into the archive
844 -- table and same number deleted from the original table
845
846 l_commit_size := p_commit_size / 2 ;
847
848
849 pa_debug.debug( ' ->Before insert into PA_PRJ_ACCUM_ACTUALS_AR') ;
850
851 INSERT INTO PA_PRJ_ACCUM_ACTUALS_AR
852 (
853 PURGE_BATCH_ID,
854 PURGE_RELEASE,
855 PURGE_PROJECT_ID,
856 PROJECT_ACCUM_ID,
857 RAW_COST_ITD,
858 RAW_COST_YTD,
859 RAW_COST_PP,
860 RAW_COST_PTD,
861 BILLABLE_RAW_COST_ITD,
862 BILLABLE_RAW_COST_YTD,
863 BILLABLE_RAW_COST_PP,
864 BILLABLE_RAW_COST_PTD,
865 BURDENED_COST_ITD,
866 BURDENED_COST_YTD,
867 BURDENED_COST_PP,
868 BURDENED_COST_PTD,
869 BILLABLE_BURDENED_COST_ITD,
870 BILLABLE_BURDENED_COST_YTD,
871 BILLABLE_BURDENED_COST_PP,
872 BILLABLE_BURDENED_COST_PTD,
873 QUANTITY_ITD,
874 QUANTITY_YTD,
875 QUANTITY_PP,
876 QUANTITY_PTD,
877 LABOR_HOURS_ITD,
878 LABOR_HOURS_YTD,
879 LABOR_HOURS_PP,
880 LABOR_HOURS_PTD,
881 BILLABLE_QUANTITY_ITD,
882 BILLABLE_QUANTITY_YTD,
883 BILLABLE_QUANTITY_PP,
884 BILLABLE_QUANTITY_PTD,
885 BILLABLE_LABOR_HOURS_ITD,
886 BILLABLE_LABOR_HOURS_YTD,
887 BILLABLE_LABOR_HOURS_PP,
888 BILLABLE_LABOR_HOURS_PTD,
889 REVENUE_ITD,
890 REVENUE_YTD,
891 REVENUE_PP,
892 REVENUE_PTD,
893 TXN_UNIT_OF_MEASURE,
894 LAST_UPDATED_BY,
895 LAST_UPDATE_DATE,
896 CREATION_DATE,
897 CREATED_BY,
898 LAST_UPDATE_LOGIN,
899 REQUEST_ID,
900 PROGRAM_APPLICATION_ID,
901 PROGRAM_ID,
902 PROGRAM_UPDATE_DATE
903 )
904 SELECT
905 p_purge_batch_id,
906 p_purge_release,
907 p_project_id,
908 PROJECT_ACCUM_ID,
909 RAW_COST_ITD,
910 RAW_COST_YTD,
911 RAW_COST_PP,
912 RAW_COST_PTD,
913 BILLABLE_RAW_COST_ITD,
914 BILLABLE_RAW_COST_YTD,
915 BILLABLE_RAW_COST_PP,
916 BILLABLE_RAW_COST_PTD,
917 BURDENED_COST_ITD,
918 BURDENED_COST_YTD,
919 BURDENED_COST_PP,
920 BURDENED_COST_PTD,
921 BILLABLE_BURDENED_COST_ITD,
922 BILLABLE_BURDENED_COST_YTD,
923 BILLABLE_BURDENED_COST_PP,
924 BILLABLE_BURDENED_COST_PTD,
925 QUANTITY_ITD,
926 QUANTITY_YTD,
927 QUANTITY_PP,
928 QUANTITY_PTD,
929 LABOR_HOURS_ITD,
930 LABOR_HOURS_YTD,
931 LABOR_HOURS_PP,
932 LABOR_HOURS_PTD,
933 BILLABLE_QUANTITY_ITD,
934 BILLABLE_QUANTITY_YTD,
935 BILLABLE_QUANTITY_PP,
936 BILLABLE_QUANTITY_PTD,
937 BILLABLE_LABOR_HOURS_ITD,
938 BILLABLE_LABOR_HOURS_YTD,
939 BILLABLE_LABOR_HOURS_PP,
940 BILLABLE_LABOR_HOURS_PTD,
941 REVENUE_ITD,
942 REVENUE_YTD,
943 REVENUE_PP,
944 REVENUE_PTD,
945 TXN_UNIT_OF_MEASURE,
946 LAST_UPDATED_BY,
947 LAST_UPDATE_DATE,
948 CREATION_DATE,
949 CREATED_BY,
950 LAST_UPDATE_LOGIN,
951 REQUEST_ID,
952 PROGRAM_APPLICATION_ID,
953 PROGRAM_ID,
954 PROGRAM_UPDATE_DATE
955 FROM pa_Project_Accum_Actuals paa
956 /* commented and modified as below for performance reasons. Archive Purge 11.5
957 WHERE (paa.rowid) IN
958 (SELECT paa1.rowid FROM pa_project_accum_actuals paa1,
959 pa_project_accum_headers pah
960 WHERE paa1.project_accum_id=pah.project_accum_id
961 AND pah.project_id = p_project_id
962 AND rownum < l_commit_size
963 ) ;
964 */
965 WHERE (paa.project_accum_id) IN
966 (SELECT pah.project_accum_id
967 FROM pa_project_accum_headers pah
968 WHERE pah.project_id = p_project_id
969 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
970 AND rownum < l_commit_size
971 ) ;
972
973 /*Code Changes for Bug No.2984871 start */
974 l_NoOfRecordsIns := SQL%ROWCOUNT ;
975 /*Code Changes for Bug No.2984871 end */
976
977 pa_debug.debug( ' ->After insert into PA_Project_Accum_Actuals') ;
978
979 IF l_NoOfRecordsIns > 0 THEN
980 -- The algorithm for deleting records from original table
981 -- depends on whether records are being archived or not.
982 -- If records are archived before purging, then the WHERE clause
983 -- joins the original and the archived table on the basis of a
984 -- unique key and uses rowid of records in original table to hit
985 -- the records to be deleted
986
987 pa_debug.debug( ' ->Before delete from pa_project_accum_actuals ') ;
988 /* commented and modified as below for performance reasons. Archive Purge 11.5
989 DELETE FROM pa_project_accum_actuals paa
990 WHERE (paa.rowid) IN
991 ( SELECT paa1.rowid
992 FROM pa_project_accum_Actuals paa1,
993 PA_PRJ_ACCUM_ACTUALS_AR paa2
994 WHERE paa1.project_accum_id = paa2.project_accum_id
995 AND paa2.purge_project_id = p_project_id
996 ) ;
997 */
998 DELETE FROM pa_project_accum_actuals paa
999 WHERE (paa.project_accum_id) IN
1000 ( SELECT paa2.project_accum_id
1001 FROM PA_PRJ_ACCUM_ACTUALS_AR paa2
1002 WHERE paa2.purge_project_id = p_project_id
1003 ) ;
1004 /*Code Changes for Bug No.2984871 start */
1005 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1006 /*Code Changes for Bug No.2984871 end*/
1007
1008 pa_debug.debug( ' ->After delete from pa_project_accum_actuals ') ;
1009 END IF ;
1010 ELSE
1011
1012 l_commit_size := p_commit_size ;
1013
1014 -- If the archive option is not selected then the delete will
1015 -- be based on the commit size.
1016
1017 pa_debug.debug( ' ->Before delete from pa_project_accum_actuals ') ;
1018 /* commented and modified as below for performance reasons. Archive Purge 11.5
1019 DELETE from pa_project_accum_actuals paa
1020 WHERE (paa.rowid) IN
1021 ( SELECT paa1.rowid
1022 FROM pa_project_accum_actuals paa1,
1023 pa_project_accum_headers pah
1024 WHERE paa1.project_accum_id=pah.project_accum_id
1025 AND pah.project_id = p_project_id
1026 AND rownum <= l_commit_size
1027 ) ;
1028 */
1029 DELETE from pa_project_accum_actuals paa
1030 WHERE (paa.project_accum_id) IN
1031 ( SELECT pah.project_accum_id
1032 FROM pa_project_accum_headers pah
1033 WHERE pah.project_id = p_project_id
1034 /* 2485577 */ AND pah.project_accum_id <> g_def_proj_accum_id
1035 AND rownum <= l_commit_size
1036 ) ;
1037
1038 /*Code Changes for Bug No.2984871 start */
1039 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1040 /*Code Changes for Bug No.2984871 end */
1041
1042 pa_debug.debug( ' ->After delete from pa_project_accum_actuals ') ;
1043 END IF ;
1044
1045 IF l_NoOfRecordsDel = 0 THEN
1046
1047 exit ;
1048
1049 ELSE
1050 -- After "deleting" or "deleting and inserting" a set of records
1051 -- the transaction is commited. This also creates a record in the
1052 -- Pa_Purge_Project_details which will show the no. of records
1053 -- that are purged from each table.
1054
1055 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1056 pa_purge.CommitProcess
1057 (p_purge_batch_id => p_purge_batch_id,
1058 p_project_id => p_project_id,
1059 p_table_name => 'PA_PROJECT_ACCUM_ACTUALS',
1060 p_NoOfRecordsIns => l_NoOfRecordsIns,
1061 p_NoOfRecordsDel => l_NoOfRecordsDel,
1062 x_err_code => x_err_code,
1063 x_err_stack => x_err_stack,
1064 x_err_stage => x_err_stage
1065 ) ;
1066 END IF ;
1067 END LOOP ;
1068
1069
1070 x_err_stack := l_old_err_stack ;
1071 EXCEPTION
1072 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1073 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1074
1075 WHEN OTHERS THEN
1076 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ProjAccumActuals');
1077 pa_debug.debug('Error stage is '||x_err_stage );
1078 pa_debug.debug('Error stack is '||x_err_stack );
1079 pa_debug.debug(SQLERRM);
1080 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1081
1082 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1083
1084
1085 END pa_projaccumactuals ;
1086
1087 -- Start of comments
1088 -- API name : PA_ResAccumDetails
1089 -- Type : Private
1090 -- Pre-reqs : None
1091 -- Function : Archive and Purge data for table PA_Resource_Accum_Details
1092 -- Parameters : See common list above
1093 -- End of comments
1094 PROCEDURE pa_resaccumdetails
1095 ( p_purge_batch_id IN NUMBER,
1096 p_project_id IN NUMBER,
1097 p_txn_to_date IN DATE,
1098 p_purge_release IN VARCHAR2,
1099 p_archive_flag IN VARCHAR2,
1100 p_commit_size IN NUMBER,
1101 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1102 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1103 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1104 ) is
1105
1106 l_old_err_stage VARCHAR2(2000);
1107 l_old_err_stack VARCHAR2(2000);
1108 l_NoOfRecordsIns NUMBER;
1109 l_NoOfRecordsDel NUMBER;
1110 BEGIN
1111
1112 l_old_err_stack := x_err_stack;
1113
1114 x_err_stack := x_err_stack || ' ->Entering PA_ResAccumDetails' ;
1115
1116 pa_debug.debug(x_err_stack);
1117
1118 LOOP
1119 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1120 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1121 IF p_archive_flag = 'Y' THEN
1122 -- If archive option is selected then the records are
1123 -- archived into the archive table before being purged.
1124 -- The WHERE condition is such that half the no. of records
1125 -- specified in commit size are inserted into the archive
1126 -- table and same number deleted from the original table
1127
1128 l_commit_size := p_commit_size / 2 ;
1129
1130
1131 pa_debug.debug( ' ->Before insert into PA_RES_ACCUM_DETAILS_AR') ;
1132
1133 INSERT INTO PA_RES_ACCUM_DETAILS_AR
1134 (
1135 PURGE_BATCH_ID,
1136 PURGE_RELEASE,
1137 PURGE_PROJECT_ID,
1138 TXN_ACCUM_ID,
1139 RESOURCE_LIST_ASSIGNMENT_ID,
1140 RESOURCE_LIST_ID,
1141 RESOURCE_LIST_MEMBER_ID,
1142 RESOURCE_ID,
1143 PROJECT_ID,
1144 TASK_ID,
1145 LAST_UPDATED_BY,
1146 LAST_UPDATE_DATE,
1147 CREATION_DATE,
1148 CREATED_BY,
1149 LAST_UPDATE_LOGIN,
1150 REQUEST_ID,
1151 PROGRAM_APPLICATION_ID,
1152 PROGRAM_ID,
1153 PROGRAM_UPDATE_DATE,
1154 ADW_NOTIFY_FLAG
1155 )
1156 SELECT
1157 p_purge_batch_id,
1158 p_purge_release,
1159 p_project_id,
1160 TXN_ACCUM_ID,
1161 RESOURCE_LIST_ASSIGNMENT_ID,
1162 RESOURCE_LIST_ID,
1163 RESOURCE_LIST_MEMBER_ID,
1164 RESOURCE_ID,
1165 PROJECT_ID,
1166 TASK_ID,
1167 LAST_UPDATED_BY,
1168 LAST_UPDATE_DATE,
1169 CREATION_DATE,
1170 CREATED_BY,
1171 LAST_UPDATE_LOGIN,
1172 REQUEST_ID,
1173 PROGRAM_APPLICATION_ID,
1174 PROGRAM_ID,
1175 PROGRAM_UPDATE_DATE,
1176 ADW_NOTIFY_FLAG
1177 FROM pa_Resource_Accum_Details pad
1178 WHERE pad.project_id = p_project_id
1179 AND rownum < l_commit_size;
1180 /*Code Changes for Bug No.2984871 start */
1181 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1182 /*Code Changes for Bug No.2984871 end */
1183
1184 pa_debug.debug( ' ->After insert into PA_RES_ACCUM_DETAILS_AR') ;
1185
1186 /*Code Changes for Bug No.2984871 start */
1187 IF l_NoOfRecordsIns > 0 THEN
1188 /*Code Changes for Bug No.2984871 end */
1189 -- The algorithm for deleting records from original table
1190 -- depends on whether records are being archived or not.
1191 -- If records are archived before purging, then the WHERE clause
1192 -- joins the original and the archived table on the basis of a
1193 -- unique key and uses rowid of records in original table to hit
1194 -- the records to be deleted
1195
1196 pa_debug.debug( ' ->Before delete from pa_resource_accum_details ') ;
1197 /* commented and modified as below for performance reasons. Archive Purge 11.5
1198 DELETE FROM pa_resource_accum_details pad
1199 WHERE (pad.rowid) IN
1200 ( SELECT pad1.rowid
1201 FROM pa_resource_accum_details pad1,
1202 PA_RES_ACCUM_DETAILS_AR pad2
1203 WHERE pad1.txn_accum_id = pad2.txn_accum_id
1204 AND pad1.resource_list_assignment_id =
1205 pad2.resource_list_assignment_id
1206 AND pad2.purge_project_id=p_project_id
1207 ) ;
1208 */
1209 DELETE FROM pa_resource_accum_details pad
1210 WHERE (pad.txn_accum_id, pad.resource_list_assignment_id) IN
1211 ( SELECT pad2.txn_accum_id, pad2.resource_list_assignment_id
1212 FROM PA_RES_ACCUM_DETAILS_AR pad2
1213 WHERE pad2.purge_project_id=p_project_id
1214 ) ;
1215 /*Code Changes for Bug No.2984871 start */
1216 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1217 /*Code Changes for Bug No.2984871 end */
1218
1219 pa_debug.debug( ' ->After delete from pa_resource_accum_details ') ;
1220 END IF ;
1221 ELSE
1222
1223 l_commit_size := p_commit_size ;
1224
1225 -- If the archive option is not selected then the delete will
1226 -- be based on the commit size.
1227
1228 pa_debug.debug( ' ->Before delete from pa_resource_accum_details ') ;
1229 /* commented and modified as below for performance reasons. Archive Purge 11.5
1230 DELETE FROM pa_resource_accum_details pad
1231 WHERE (pad.rowid) IN
1232 ( SELECT pad.rowid
1233 FROM pa_resource_accum_details pad
1234 WHERE pad.project_id = p_project_id
1235 AND rownum <= l_commit_size
1236 ) ;
1237 */
1238 DELETE FROM pa_resource_accum_details pad
1239 WHERE pad.project_id = p_project_id
1240 AND rownum <= l_commit_size;
1241 /*Code Changes for Bug No.2984871 start */
1242 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1243 /*Code Changes for Bug No.2984871 end */
1244 pa_debug.debug( ' ->After delete from pa_resource_accum_details ') ;
1245 END IF ;
1246
1247 /*Code Changes for Bug No.2984871 start */
1248 IF l_NoOfRecordsDel = 0 THEN
1249 /*Code Changes for Bug No.2984871 end*/
1250 -- Once the SqlCount becomes 0, which means that there are
1251 -- no more records to be purged then we exit the loop.
1252
1253 exit ;
1254
1255 ELSE
1256 -- After "deleting" or "deleting and inserting" a set of records
1257 -- the transaction is commited. This also creates a record in the
1258 -- Pa_Purge_Project_details which will show the no. of records
1259 -- that are purged from each table.
1260
1261 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1262 pa_purge.CommitProcess
1263 (p_purge_batch_id => p_purge_batch_id,
1264 p_project_id => p_project_id,
1265 p_table_name => 'PA_RESOURCE_ACCUM_DETAILS',
1266 p_NoOfRecordsIns => l_NoOfRecordsIns,
1267 p_NoOfRecordsDel => l_NoOfRecordsDel,
1268 x_err_code => x_err_code,
1269 x_err_stack => x_err_stack,
1270 x_err_stage => x_err_stage
1271 ) ;
1272 END IF ;
1273 END LOOP ;
1274
1275
1276 x_err_stack := l_old_err_stack ;
1277 EXCEPTION
1278 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1279 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1280
1281 WHEN OTHERS THEN
1282 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_ResAccumDetails');
1283 pa_debug.debug('Error stage is '||x_err_stage );
1284 pa_debug.debug('Error stack is '||x_err_stack );
1285 pa_debug.debug(SQLERRM);
1286 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1287
1288 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1289
1290
1291 END pa_resaccumdetails ;
1292
1293
1294 -- Start of comments
1295 -- API name : PA_TxnAccum
1296 -- Type : Private
1297 -- Pre-reqs : None
1298 -- Function : Archive and Purge data for table PA_TxnAccum
1299 -- Parameters : See common list above
1300 -- End of comments
1301 PROCEDURE pa_txnaccum
1302 ( p_purge_batch_id IN NUMBER,
1303 p_project_id IN NUMBER,
1304 p_txn_to_date IN DATE,
1305 p_purge_release IN VARCHAR2,
1306 p_archive_flag IN VARCHAR2,
1307 p_commit_size IN NUMBER,
1308 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1309 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1310 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1311 ) IS
1312
1313 l_old_err_stage VARCHAR2(2000);
1314 l_old_err_stack VARCHAR2(2000);
1315 l_NoOfRecordsIns NUMBER;
1316 l_NoOfRecordsDel NUMBER;
1317 BEGIN
1318
1319 l_old_err_stack := x_err_stack;
1320
1321 x_err_stack := x_err_stack || ' ->Entering PA_TxnAccum' ;
1322
1323 pa_debug.debug(x_err_stack);
1324
1325 LOOP
1326 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1327 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1328 IF p_archive_flag = 'Y' THEN
1329 -- If archive option is selected then the records are
1330 -- archived into the archive table before being purged.
1331 -- The WHERE condition is such that half the no. of records
1332 -- specified in commit size are inserted into the archive
1333 -- table and same number deleted from the original table
1334
1335 l_commit_size := p_commit_size / 2 ;
1336
1337
1338 pa_debug.debug( ' ->Before insert into PA_Txn_Accum_AR') ;
1339
1340 INSERT INTO PA_Txn_Accum_AR
1341 (
1342 PURGE_BATCH_ID,
1343 PURGE_RELEASE,
1344 PURGE_PROJECT_ID,
1345 TXN_ACCUM_ID,
1346 PERSON_ID,
1347 JOB_ID,
1348 ORGANIZATION_ID,
1349 VENDOR_ID,
1350 EXPENDITURE_TYPE,
1351 EVENT_TYPE,
1352 NON_LABOR_RESOURCE,
1353 EXPENDITURE_CATEGORY,
1354 REVENUE_CATEGORY,
1355 NON_LABOR_RESOURCE_ORG_ID,
1356 EVENT_TYPE_CLASSIFICATION,
1357 SYSTEM_LINKAGE_FUNCTION,
1358 PROJECT_ID,
1359 TASK_ID,
1360 PA_PERIOD,
1361 GL_PERIOD,
1362 MONTH_ENDING_DATE,
1363 WEEK_ENDING_DATE,
1364 TOT_REVENUE,
1365 TOT_RAW_COST,
1366 TOT_BURDENED_COST,
1367 TOT_QUANTITY,
1368 TOT_LABOR_HOURS,
1369 TOT_BILLABLE_RAW_COST,
1370 TOT_BILLABLE_BURDENED_COST,
1371 TOT_BILLABLE_QUANTITY,
1372 TOT_BILLABLE_LABOR_HOURS,
1373 TOT_CMT_RAW_COST,
1374 TOT_CMT_BURDENED_COST,
1375 TOT_CMT_QUANTITY,
1376 I_TOT_REVENUE,
1377 I_TOT_RAW_COST,
1378 I_TOT_BURDENED_COST,
1379 I_TOT_QUANTITY,
1380 I_TOT_LABOR_HOURS,
1381 I_TOT_BILLABLE_RAW_COST,
1382 I_TOT_BILLABLE_BURDENED_COST,
1383 I_TOT_BILLABLE_QUANTITY,
1384 I_TOT_BILLABLE_LABOR_HOURS,
1385 COST_IND_COMPILED_SET_ID,
1386 REV_IND_COMPILED_SET_ID,
1387 INV_IND_COMPILED_SET_ID,
1388 CMT_IND_COMPILED_SET_ID,
1389 UNIT_OF_MEASURE,
1390 ACTUAL_COST_ROLLUP_FLAG,
1391 REVENUE_ROLLUP_FLAG,
1392 CMT_ROLLUP_FLAG,
1393 LAST_UPDATED_BY,
1394 LAST_UPDATE_DATE,
1395 CREATION_DATE,
1396 CREATED_BY,
1397 LAST_UPDATE_LOGIN,
1398 REQUEST_ID,
1399 PROGRAM_APPLICATION_ID,
1400 PROGRAM_ID,
1401 PROGRAM_UPDATE_DATE,
1402 ADW_NOTIFY_FLAG
1403 )
1404 SELECT
1405 p_purge_batch_id,
1406 p_purge_release,
1407 p_project_id,
1408 TXN_ACCUM_ID,
1409 PERSON_ID,
1410 JOB_ID,
1411 ORGANIZATION_ID,
1412 VENDOR_ID,
1413 EXPENDITURE_TYPE,
1414 EVENT_TYPE,
1415 NON_LABOR_RESOURCE,
1416 EXPENDITURE_CATEGORY,
1417 REVENUE_CATEGORY,
1418 NON_LABOR_RESOURCE_ORG_ID,
1419 EVENT_TYPE_CLASSIFICATION,
1420 SYSTEM_LINKAGE_FUNCTION,
1421 PROJECT_ID,
1422 TASK_ID,
1423 PA_PERIOD,
1424 GL_PERIOD,
1425 MONTH_ENDING_DATE,
1426 WEEK_ENDING_DATE,
1427 TOT_REVENUE,
1428 TOT_RAW_COST,
1429 TOT_BURDENED_COST,
1430 TOT_QUANTITY,
1431 TOT_LABOR_HOURS,
1432 TOT_BILLABLE_RAW_COST,
1433 TOT_BILLABLE_BURDENED_COST,
1434 TOT_BILLABLE_QUANTITY,
1435 TOT_BILLABLE_LABOR_HOURS,
1436 TOT_CMT_RAW_COST,
1437 TOT_CMT_BURDENED_COST,
1438 TOT_CMT_QUANTITY,
1439 I_TOT_REVENUE,
1440 I_TOT_RAW_COST,
1441 I_TOT_BURDENED_COST,
1442 I_TOT_QUANTITY,
1443 I_TOT_LABOR_HOURS,
1444 I_TOT_BILLABLE_RAW_COST,
1445 I_TOT_BILLABLE_BURDENED_COST,
1446 I_TOT_BILLABLE_QUANTITY,
1447 I_TOT_BILLABLE_LABOR_HOURS,
1448 COST_IND_COMPILED_SET_ID,
1449 REV_IND_COMPILED_SET_ID,
1450 INV_IND_COMPILED_SET_ID,
1451 CMT_IND_COMPILED_SET_ID,
1452 UNIT_OF_MEASURE,
1453 ACTUAL_COST_ROLLUP_FLAG,
1454 REVENUE_ROLLUP_FLAG,
1455 CMT_ROLLUP_FLAG,
1456 LAST_UPDATED_BY,
1457 LAST_UPDATE_DATE,
1458 CREATION_DATE,
1459 CREATED_BY,
1460 LAST_UPDATE_LOGIN,
1461 REQUEST_ID,
1462 PROGRAM_APPLICATION_ID,
1463 PROGRAM_ID,
1464 PROGRAM_UPDATE_DATE,
1465 ADW_NOTIFY_FLAG
1466 FROM pa_txn_accum ta
1467 WHERE ta.project_id = p_project_id
1468 AND rownum < l_commit_size;
1469 /*Code Changes for Bug No.2984871 start */
1470 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1471 /*Code Changes for Bug No.2984871 end */
1472
1473 pa_debug.debug( ' ->After insert into PA_Txn_Accum_AR') ;
1474
1475 /*Code Changes for Bug No.2984871 start */
1476 IF l_NoOfRecordsIns > 0 THEN
1477 /*Code Changes for Bug No.2984871 end */
1478 -- The algorithm for deleting records from original table
1479 -- depends on whether records are being archived or not.
1480 -- If records are archived before purging, then the WHERE clause
1481 -- joins the original and the archived table on the basis of a
1482 -- unique key and uses rowid of records in original table to hit
1483 -- the records to be deleted
1484
1485 pa_debug.debug( ' ->Before delete from pa_txn_accum ') ;
1486 /* commented and modified as below for performance reasons. Archive Purge 11.5
1487 DELETE FROM pa_txn_accum ta
1488 WHERE (ta.rowid) IN
1489 ( SELECT ta1.rowid
1490 FROM pa_txn_accum ta1,
1491 pa_txn_accum_ar ta2
1492 WHERE ta1.txn_accum_id = ta2.txn_accum_id
1493 AND ta2.purge_project_id = p_project_id
1494 ) ;
1495 */
1496 DELETE FROM pa_txn_accum ta
1497 WHERE (ta.txn_accum_id) IN
1498 ( SELECT ta2.txn_accum_id
1499 FROM pa_txn_accum_ar ta2
1500 WHERE ta2.purge_project_id = p_project_id
1501 ) ;
1502 /*Code Changes for Bug No.2984871 start */
1503 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1504 /*Code Changes for Bug No.2984871 end */
1505
1506 pa_debug.debug( ' ->After delete from pa_txn_accum ') ;
1507 END IF ;
1508 ELSE
1509
1510 l_commit_size := p_commit_size ;
1511
1512 -- If the archive option is not selected then the delete will
1513 -- be based on the commit size.
1514
1515 pa_debug.debug( ' ->Before delete from pa_txn_accum ') ;
1516 /* commented and modified as below for performance reasons. Archive Purge 11.5
1517 DELETE FROM pa_txn_accum ta
1518 WHERE (ta.rowid) IN
1519 ( SELECT ta.rowid
1520 FROM pa_txn_accum ta
1521 WHERE ta.project_id = p_project_id
1522 AND rownum <= l_commit_size
1523 ) ;
1524 */
1525 DELETE FROM pa_txn_accum ta
1526 WHERE ta.project_id = p_project_id
1527 AND rownum <= l_commit_size;
1528
1529 /*Code Changes for Bug No.2984871 start */
1530 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1531 /*Code Changes for Bug No.2984871 end */
1532 pa_debug.debug( ' ->After delete from pa_txn_accum ') ;
1533 END IF ;
1534
1535 IF l_NoOfRecordsDel = 0 THEN
1536 -- Once the SqlCount becomes 0, which means that there are
1537 -- no more records to be purged then we exit the loop.
1538
1539 exit ;
1540
1541 ELSE
1542 -- After "deleting" or "deleting and inserting" a set of records
1543 -- the transaction is commited. This also creates a record in the
1544 -- Pa_Purge_Project_details which will show the no. of records
1545 -- that are purged from each table.
1546
1547 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1548 pa_purge.CommitProcess
1549 (p_purge_batch_id => p_purge_batch_id,
1550 p_project_id => p_project_id,
1551 p_table_name => 'PA_TXN_ACCUM',
1552 p_NoOfRecordsIns => l_NoOfRecordsIns,
1553 p_NoOfRecordsDel => l_NoOfRecordsDel,
1554 x_err_code => x_err_code,
1555 x_err_stack => x_err_stack,
1556 x_err_stage => x_err_stage
1557 ) ;
1558 END IF ;
1559 END LOOP ;
1560
1561
1562 x_err_stack := l_old_err_stack ;
1563 EXCEPTION
1564 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
1565 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1566
1567 WHEN OTHERS THEN
1568 pa_debug.debug('Error Procedure Name := PA_PURGE_SUMMARY.PA_TxnAccum');
1569 pa_debug.debug('Error stage is '||x_err_stage );
1570 pa_debug.debug('Error stack is '||x_err_stack );
1571 pa_debug.debug(SQLERRM);
1572 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1573
1574 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1575
1576 END pa_txnaccum ;
1577
1578 -- Start of comments
1579 -- API name : PA_TxnAccumDetails
1580 -- Type : Private
1581 -- Pre-reqs : None
1582 -- Function : Archive and Purge data for table PA_TxnAccumDetails
1583 -- Parameters : See common list above
1584 -- End of comments
1585
1586
1587 PROCEDURE pa_txnaccumdetails
1588 ( p_purge_batch_id IN NUMBER,
1589 p_project_id IN NUMBER,
1590 p_txn_to_date IN DATE,
1591 p_purge_release IN VARCHAR2,
1592 p_archive_flag IN VARCHAR2,
1593 p_commit_size IN NUMBER,
1594 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1595 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1596 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1597 ) IS
1598
1599 l_old_err_stage VARCHAR2(2000);
1600 l_old_err_stack VARCHAR2(2000);
1601 l_NoOfRecordsIns NUMBER;
1602 l_NoOfRecordsDel NUMBER;
1603 BEGIN
1604
1605 l_old_err_stack := x_err_stack;
1606
1607 x_err_stack := x_err_stack || ' ->Entering PA_TxnAccumDetails' ;
1608
1609 pa_debug.debug(x_err_stack);
1610
1611 LOOP
1612 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1613 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1614 IF p_archive_flag = 'Y' THEN
1615 -- If archive option is selected then the records are
1616 -- archived into the archive table before being purged.
1617 -- The WHERE condition is such that half the no. of records
1618 -- specified in commit size are inserted into the archive
1619 -- table and same number deleted from the original table
1620
1621 l_commit_size := p_commit_size / 2 ;
1622
1623
1624 pa_debug.debug( ' ->Before insert into PA_Txn_Accum_Details_AR') ;
1625
1626 -- Modified insert statement to use project_id from pa_txn_accum to select rows from
1627 -- pa_txn_accum_details as project_id may be null for some detail lines
1628 -- project_id is a not null column in pa_txn_accum.
1629
1630 INSERT INTO PA_Txn_Accum_Details_AR
1631 (
1632 PURGE_BATCH_ID,
1633 PURGE_RELEASE,
1634 PURGE_PROJECT_ID,
1635 ORIGINAL_ROWID,
1636 TXN_ACCUM_ID,
1637 LINE_TYPE,
1638 EXPENDITURE_ITEM_ID,
1639 EVENT_NUM,
1640 LINE_NUM,
1641 PROJECT_ID,
1642 TASK_ID,
1643 CMT_LINE_ID,
1644 LAST_UPDATED_BY,
1645 LAST_UPDATE_DATE,
1646 CREATION_DATE,
1647 CREATED_BY,
1648 LAST_UPDATE_LOGIN,
1649 REQUEST_ID,
1650 PROGRAM_APPLICATION_ID,
1651 PROGRAM_ID,
1652 PROGRAM_UPDATE_DATE
1653 )
1654 SELECT
1655 p_purge_batch_id,
1656 p_purge_release,
1657 p_project_id,
1658 tad.ROWID,
1659 tad.TXN_ACCUM_ID,
1660 tad.LINE_TYPE,
1661 tad.EXPENDITURE_ITEM_ID,
1662 tad.EVENT_NUM,
1663 tad.LINE_NUM,
1664 tad.PROJECT_ID,
1665 tad.TASK_ID,
1666 tad.CMT_LINE_ID,
1667 tad.LAST_UPDATED_BY,
1668 tad.LAST_UPDATE_DATE,
1669 tad.CREATION_DATE,
1670 tad.CREATED_BY,
1671 tad.LAST_UPDATE_LOGIN,
1672 tad.REQUEST_ID,
1673 tad.PROGRAM_APPLICATION_ID,
1674 tad.PROGRAM_ID,
1675 tad.PROGRAM_UPDATE_DATE
1676 FROM pa_txn_accum_details tad,
1677 pa_txn_accum pta
1678 WHERE tad.txn_accum_id = pta.txn_accum_id
1679 AND pta.project_id = p_project_id
1680 AND rownum < l_commit_size;
1681
1682
1683 /*Code Changes for Bug No.2984871 start */
1684 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1685 /*Code Changes for Bug No.2984871 end */
1686
1687 pa_debug.debug( ' ->After insert into PA_Txn_Accum_Details_AR') ;
1688
1689 IF l_NoOfRecordsIns > 0 THEN
1690 -- The algorithm for deleting records from original table
1691 -- depends on whether records are being archived or not.
1692 -- If records are archived before purging, then the WHERE clause
1693 -- joins the original and the archived table on the basis of a
1694 -- unique key and uses rowid of records in original table to hit
1695 -- the records to be deleted
1696
1697 pa_debug.debug( ' ->Before delete from pa_txn_accum_details ') ;
1698
1699 DELETE FROM pa_txn_accum_details tad
1700 WHERE (tad.rowid) IN
1701 ( SELECT tad2.original_rowid
1702 FROM pa_txn_accum_details tad1,
1703 pa_txn_accum_details_ar tad2
1704 WHERE tad1.rowid = tad2.original_rowid
1705 -- AND tad1.project_id=tad2.project_id
1706 AND tad2.purge_project_id = p_project_id
1707 ) ;
1708 /*Code Changes for Bug No.2984871 start */
1709 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1710 /*Code Changes for Bug No.2984871 end */
1711 pa_debug.debug( ' ->After delete from pa_txn_accum_details ') ;
1712 END IF ;
1713 ELSE
1714
1715 l_commit_size := p_commit_size ;
1716
1717 -- If the archive option is not selected then the delete will
1718 -- be based on the commit size.
1719
1720 pa_debug.debug( ' ->Before delete from pa_txn_accum_details ') ;
1721 /* commented and modified as below for performance reasons. Archive Purge 11.5
1722 DELETE FROM pa_txn_accum_details tad
1723 WHERE (tad.rowid) IN
1724 ( SELECT tad1.rowid
1725 FROM pa_txn_accum_details tad1, pa_txn_accum pta
1726 WHERE tad1.txn_accum_id = pta.txn_accum_id
1727 AND pta.project_id = p_project_id
1728 AND rownum <= l_commit_size
1729 ) ;
1730 */
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;