[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_BILLING
Source
1 package body pa_purge_billing as
2 /*$Header: PAXBIPRB.pls 120.5 2007/02/07 10:46:49 rgandhi ship $*/
3
4 l_commit_size NUMBER ;
5
6 -- private procedures
7 --
8 -- The list of parameters is common for all private procedures in the package
9 ------------------------------------------------------------------------------------------
10 -- Parameters p_batch_id IN NUMBER
11 -- The purge batch id for which rows have
12 -- to be purged/archived.
13 -- p_project_Id IN NUMBER,
14 -- The project id for which records have
15 -- to be purged/archived.
16 -- p_Purge_Release IN VARCHAR2,
17 -- Oracle Projects release (10.7 , 11.0)
18 -- p_Archive_Flag IN VARCHAR2,
19 -- Archive table data
20 -- p_Txn_To_Date IN DATE,
21 -- Date on or before which all transactions are to be purged
22 -- (Will be used by Costing only)
23 -- p_Commit_Size IN NUMBER,
24 -- The commit size
25 -- X_Err_Stack IN OUT VARCHAR2,
26 -- Error stack
27 -- X_Err_Stage IN OUT VARCHAR2,
28 -- Stage in the procedure where error occurred
29 -- X_Err_Code IN OUT NUMBER
30 -- Error code returned from the procedure
31 -- = 0 SUCCESS
32 -- > 0 Application error
33 -- < 0 Oracle error
34 -------------------------------------------------------------------------------------------
35 -- Start of comments
36 -- API name : PA_MC_CUSTREVDISTLINES
37 -- Type : Private
38 -- Pre-reqs : None
39 -- Function : Archive and Purge data for table pa_mc_cust_rdl_AR
40 -- Parameters : See common list above
41 -- End of comments
42 procedure PA_MC_CUSTREVDISTLINES
43 ( p_purge_batch_id IN NUMBER,
44 p_project_id IN NUMBER,
45 p_txn_to_date IN DATE,
46 p_purge_release IN VARCHAR2,
47 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
48 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
49 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
50 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
51 ) is
52
53 l_old_err_stage VARCHAR2(2000);
54 l_old_err_stack VARCHAR2(2000);
55
56
57 begin
58
59
60 l_old_err_stack := x_err_stack;
61
62 x_err_stack := x_err_stack || ' ->Entering PA_MC_CUSTREVDISTLINES ';
63
64 pa_debug.debug(x_err_stack);
65
66 pa_debug.debug( ' ->Before insert into PA_MC_Rev_Distribution_Lines_AR') ;
67 /* Commented out for MRC migration to SLA
68 insert into Pa_MC_Cust_Rdl_ar
69 (
70 PURGE_BATCH_ID,
71 PURGE_RELEASE,
72 PURGE_PROJECT_ID,
73 REQUEST_ID,
74 PROGRAM_APPLICATION_ID,
75 PROGRAM_ID,
76 PROGRAM_UPDATE_DATE,
77 BATCH_NAME,
78 RAW_COST,
79 PROJECT_ID,
80 DRAFT_REVENUE_NUM,
81 DRAFT_REVENUE_ITEM_LINE_NUM,
82 DRAFT_INVOICE_NUM,
83 DRAFT_INVOICE_ITEM_LINE_NUM,
84 CURRENCY_CODE,
85 EXCHANGE_RATE,
86 CONVERSION_DATE,
87 SET_OF_BOOKS_ID,
88 EXPENDITURE_ITEM_ID,
89 LINE_NUM,
90 AMOUNT,
91 BILL_AMOUNT,
92 PRC_ASSIGNMENT_ID,
93 RATE_TYPE,
94 PROJFUNC_INV_RATE_TYPE,
95 PROJFUNC_INV_RATE_DATE,
96 PROJFUNC_INV_EXCHANGE_RATE
97 )
98 select
99 p_purge_batch_id,
100 p_purge_release,
101 p_project_id,
102 mc.REQUEST_ID,
103 mc.PROGRAM_APPLICATION_ID,
104 mc.PROGRAM_ID,
105 mc.PROGRAM_UPDATE_DATE,
106 mc.BATCH_NAME,
107 mc.RAW_COST,
108 mc.PROJECT_ID,
109 mc.DRAFT_REVENUE_NUM,
110 mc.DRAFT_REVENUE_ITEM_LINE_NUM,
111 mc.DRAFT_INVOICE_NUM,
112 mc.DRAFT_INVOICE_ITEM_LINE_NUM,
113 mc.CURRENCY_CODE,
114 mc.EXCHANGE_RATE,
115 mc.CONVERSION_DATE,
116 mc.SET_OF_BOOKS_ID,
117 mc.EXPENDITURE_ITEM_ID,
118 mc.LINE_NUM,
119 mc.AMOUNT,
120 mc.BILL_AMOUNT,
121 mc.PRC_ASSIGNMENT_ID,
122 mc.RATE_TYPE,
123 mc.PROJFUNC_INV_RATE_TYPE,
124 mc.PROJFUNC_INV_RATE_DATE,
125 mc.PROJFUNC_INV_EXCHANGE_RATE
126 from Pa_Mc_Cust_Rdl_All mc,
127 PA_CUST_RDL_AR ar
128 where ar.purge_project_id = p_project_id
129 and mc.expenditure_item_id = ar.expenditure_item_id
130 and mc.line_num = ar.line_num;
131
132 */
133 p_mcnoofrecordsins := SQL%ROWCOUNT ;
134
135 pa_debug.debug( ' ->After insert into PA_Revenue_Distribution_Lines_AR') ;
136 x_err_stack := l_old_err_stack ;
137
138 EXCEPTION
139 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
140 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
141
142 WHEN OTHERS THEN
143 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_CUSTREVDISTLINES' );
144 pa_debug.debug('Error stage is '||x_err_stage );
145 pa_debug.debug('Error stack is '||x_err_stack );
146 pa_debug.debug(SQLERRM);
147 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
148
149 /* ATG NOCOPY changes */
150 p_mcnoofrecordsins := null;
151 x_err_stack := l_old_err_stack ;
152
153 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
154
155 end PA_MC_CUSTREVDISTLINES ;
156
157 -- Start of comments
158 -- API name : PA_CUSTREVDISTLINES
159 -- Type : Private
160 -- Pre-reqs : None
161 -- Function : Archive and Purge data for table PA_CUST_REV_DIST_LINES_ALL
162 -- Parameters : See common list above
163 -- End of comments
164 procedure PA_CUSTREVDISTLINES
165 ( p_purge_batch_id IN NUMBER,
166 p_project_id IN NUMBER,
167 p_txn_to_date IN DATE,
168 p_purge_release IN VARCHAR2,
169 p_archive_flag IN VARCHAR2,
170 p_commit_size IN NUMBER,
171 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
172 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
173 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
174 ) is
175
176 l_old_err_stage VARCHAR2(2000);
177 l_old_err_stack VARCHAR2(2000);
178 l_NoOfRecordsIns NUMBER;
179 l_NoOfRecordsDel NUMBER;
180 l_MC_NoOfRecordsIns NUMBER := NULL;
181 l_MC_NoOfRecordsDel NUMBER := NULL;
182
183 l_crdl_rowid_tab PA_PLSQL_DATATYPES.RowIDTabTyp;
184 l_crdl_rowid_tab_empty PA_PLSQL_DATATYPES.RowIDTabTyp;
185 l_fetch_complete BOOLEAN := FALSE;
186 l_crdl_ind NUMBER;
187
188 cursor c_crdl_records is
189 select crdl.rowid from pa_cust_rev_dist_lines_all crdl
190 where crdl.project_id = p_project_id;
191
192 begin
193
194
195 l_old_err_stack := x_err_stack;
196
197 x_err_stack := x_err_stack || ' ->Entering PA_CUSTREVDISTLINES ';
198
199 pa_debug.debug(x_err_stack);
200
201 OPEN c_crdl_records;
202
203 if p_archive_flag = 'Y' then
204 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
205 else
206 l_commit_size := pa_utils2.arpur_mrc_commit_size ;
207 end if;
208
209 LOOP
210
211 l_NoOfRecordsIns := 0;
212 l_NoOfRecordsDel := 0;
213 l_crdl_rowid_tab := l_crdl_rowid_tab_empty;
214
215 FETCH c_crdl_records BULK COLLECT INTO l_crdl_rowid_tab LIMIT l_commit_size;
216 IF c_crdl_records%NOTFOUND Then
217 CLOSE c_crdl_records;
218 l_fetch_complete := TRUE;
219 END IF;
220 /* if p_archive_flag = 'Y' then */
221 -- If archive option is selected then the records are
222 -- inserted into the archive tables
223 -- before being purged. The where condition is such that
224 -- only the it inserts half the no. of records specified
225 -- in the commit size.
226
227 /* l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ; */
228
229 pa_debug.debug( ' ->Before insert into PA_Revenue_Distribution_Lines_AR') ;
230 IF l_crdl_rowid_tab.LAST is not null THEN
231 IF p_archive_flag = 'Y' THEN
232 FORALL l_crdl_ind in l_crdl_rowid_tab.FIRST .. l_crdl_rowid_tab.LAST
233 insert into PA_CUST_RDL_AR
234 (
235 PURGE_BATCH_ID,
236 PURGE_RELEASE,
237 PURGE_PROJECT_ID,
238 ORG_ID,
239 EXPENDITURE_ITEM_ID,
240 LINE_NUM,
241 CREATION_DATE,
242 CREATED_BY,
243 PROJECT_ID,
244 DRAFT_REVENUE_NUM,
245 DRAFT_REVENUE_ITEM_LINE_NUM,
246 AMOUNT,
247 CODE_COMBINATION_ID,
248 BILL_AMOUNT,
249 FUNCTION_CODE,
250 FUNCTION_TRANSACTION_CODE,
251 REQUEST_ID,
252 PROGRAM_APPLICATION_ID,
253 PROGRAM_ID,
254 PROGRAM_UPDATE_DATE,
255 BATCH_NAME,
256 ADDITIONAL_REVENUE_FLAG,
257 INVOICE_ELIGIBLE_FLAG,
258 DRAFT_INVOICE_NUM,
259 DRAFT_INVOICE_ITEM_LINE_NUM,
260 REVERSED_FLAG,
261 LINE_NUM_REVERSED,
262 REV_IND_COMPILED_SET_ID,
263 INV_IND_COMPILED_SET_ID,
264 RAW_COST,
265 OUTPUT_VAT_TAX_ID,
266 OUTPUT_TAX_CLASSIFICATION_CODE,
267 OUTPUT_TAX_EXEMPT_FLAG,
268 OUTPUT_TAX_EXEMPT_REASON_CODE,
269 OUTPUT_TAX_EXEMPT_NUMBER,
270 PRC_GENERATED_FLAG,
271 BILL_TRANS_CURRENCY_CODE,
272 BILL_TRANS_AMOUNT,
273 BILL_TRANS_BILL_AMOUNT,
274 BILL_RATE,
275 REVPROC_CURRENCY_CODE,
276 REVPROC_RATE_TYPE,
277 REVPROC_RATE_DATE,
278 REVPROC_EXCHANGE_RATE,
279 INVPROC_CURRENCY_CODE,
280 INVPROC_RATE_TYPE,
281 INVPROC_RATE_DATE,
282 INVPROC_EXCHANGE_RATE,
283 PROJECT_CURRENCY_CODE,
284 PROJECT_REVENUE_AMOUNT,
285 PROJECT_REV_RATE_TYPE,
286 PROJECT_REV_RATE_DATE,
287 PROJECT_REV_EXCHANGE_RATE,
288 PROJECT_BILL_AMOUNT,
289 PROJECT_INV_RATE_TYPE,
290 PROJECT_INV_RATE_DATE,
291 PROJECT_INV_EXCHANGE_RATE,
292 PROJFUNC_CURRENCY_CODE,
293 PROJFUNC_REVENUE_AMOUNT,
294 PROJFUNC_REV_RATE_TYPE,
295 PROJFUNC_REV_RATE_DATE,
296 PROJFUNC_REV_EXCHANGE_RATE,
297 PROJFUNC_BILL_AMOUNT,
298 PROJFUNC_INV_RATE_TYPE,
299 PROJFUNC_INV_RATE_DATE,
300 PROJFUNC_INV_EXCHANGE_RATE,
301 FUNDING_CURRENCY_CODE,
302 FUNDING_REVENUE_AMOUNT,
303 FUNDING_REV_RATE_TYPE,
304 FUNDING_REV_RATE_DATE,
305 FUNDING_REV_EXCHANGE_RATE,
306 FUNDING_BILL_AMOUNT,
307 FUNDING_INV_RATE_TYPE,
308 FUNDING_INV_RATE_DATE,
309 FUNDING_INV_EXCHANGE_RATE,
310 LABOR_MULTIPLIER,
311 DISCOUNT_PERCENTAGE,
312 AMOUNT_CALCULATION_CODE,
313 BILL_MARKUP_PERCENTAGE,
314 RATE_SOURCE_ID,
315 INV_GEN_REJECTION_CODE,
316 RETN_DRAFT_INVOICE_NUM,
317 RETN_DRAFT_INVOICE_LINE_NUM,
318 RETAINED_AMOUNT,
319 RETENTION_RULE_ID,
320 RATE_DISC_REASON_CODE
321 )
322 select
323 p_purge_batch_id,
324 p_purge_release,
325 p_project_id,
326 ORG_ID,
327 EXPENDITURE_ITEM_ID,
328 LINE_NUM,
329 CREATION_DATE,
330 CREATED_BY,
331 PROJECT_ID,
332 DRAFT_REVENUE_NUM,
333 DRAFT_REVENUE_ITEM_LINE_NUM,
334 AMOUNT,
335 CODE_COMBINATION_ID,
336 BILL_AMOUNT,
337 FUNCTION_CODE,
338 FUNCTION_TRANSACTION_CODE,
339 REQUEST_ID,
340 PROGRAM_APPLICATION_ID,
341 PROGRAM_ID,
342 PROGRAM_UPDATE_DATE,
343 BATCH_NAME,
344 ADDITIONAL_REVENUE_FLAG,
345 INVOICE_ELIGIBLE_FLAG,
346 DRAFT_INVOICE_NUM,
347 DRAFT_INVOICE_ITEM_LINE_NUM,
348 REVERSED_FLAG,
349 LINE_NUM_REVERSED,
350 REV_IND_COMPILED_SET_ID,
351 INV_IND_COMPILED_SET_ID,
352 RAW_COST,
353 OUTPUT_VAT_TAX_ID,
354 OUTPUT_TAX_CLASSIFICATION_CODE,
355 OUTPUT_TAX_EXEMPT_FLAG,
356 OUTPUT_TAX_EXEMPT_REASON_CODE,
357 OUTPUT_TAX_EXEMPT_NUMBER,
358 PRC_GENERATED_FLAG,
359 BILL_TRANS_CURRENCY_CODE,
360 BILL_TRANS_AMOUNT,
361 BILL_TRANS_BILL_AMOUNT,
362 BILL_RATE,
363 REVPROC_CURRENCY_CODE,
364 REVPROC_RATE_TYPE,
365 REVPROC_RATE_DATE,
366 REVPROC_EXCHANGE_RATE,
367 INVPROC_CURRENCY_CODE,
368 INVPROC_RATE_TYPE,
369 INVPROC_RATE_DATE,
370 INVPROC_EXCHANGE_RATE,
371 PROJECT_CURRENCY_CODE,
372 PROJECT_REVENUE_AMOUNT,
373 PROJECT_REV_RATE_TYPE,
374 PROJECT_REV_RATE_DATE,
375 PROJECT_REV_EXCHANGE_RATE,
376 PROJECT_BILL_AMOUNT,
377 PROJECT_INV_RATE_TYPE,
378 PROJECT_INV_RATE_DATE,
379 PROJECT_INV_EXCHANGE_RATE,
380 PROJFUNC_CURRENCY_CODE,
381 PROJFUNC_REVENUE_AMOUNT,
382 PROJFUNC_REV_RATE_TYPE,
383 PROJFUNC_REV_RATE_DATE,
384 PROJFUNC_REV_EXCHANGE_RATE,
385 PROJFUNC_BILL_AMOUNT,
386 PROJFUNC_INV_RATE_TYPE,
387 PROJFUNC_INV_RATE_DATE,
388 PROJFUNC_INV_EXCHANGE_RATE,
389 FUNDING_CURRENCY_CODE,
390 FUNDING_REVENUE_AMOUNT,
391 FUNDING_REV_RATE_TYPE,
392 FUNDING_REV_RATE_DATE,
393 FUNDING_REV_EXCHANGE_RATE,
394 FUNDING_BILL_AMOUNT,
395 FUNDING_INV_RATE_TYPE,
396 FUNDING_INV_RATE_DATE,
397 FUNDING_INV_EXCHANGE_RATE,
398 LABOR_MULTIPLIER,
399 DISCOUNT_PERCENTAGE,
400 AMOUNT_CALCULATION_CODE,
401 BILL_MARKUP_PERCENTAGE,
402 RATE_SOURCE_ID,
403 INV_GEN_REJECTION_CODE,
404 RETN_DRAFT_INVOICE_NUM,
405 RETN_DRAFT_INVOICE_LINE_NUM,
406 RETAINED_AMOUNT,
407 RETENTION_RULE_ID,
408 RATE_DISC_REASON_CODE
409 from pa_cust_rev_dist_lines_all crdl
410 where crdl.rowid = l_crdl_rowid_tab(l_crdl_ind);
411 /*where (
412 project_id = p_project_id
413 and rownum <= l_commit_size
414 ) ;*/
415
416 l_NoOfRecordsIns := SQL%ROWCOUNT ;
417 end if;
418
419
420 pa_debug.debug( ' ->After insert into PA_Revenue_Distribution_Lines_AR') ;
421
422 if l_NoOfRecordsIns > 0 then
423 -- First call the MRC procedure to archive the MC table
424 -- We have a seperate delete statement if the archive option is
425 -- selected because if archive option is selected the the records
426 -- being purged will be those records which are already archived.
427 -- table and
428
429 PA_MC_CUSTREVDISTLINES
430 ( p_purge_batch_id => p_purge_batch_id,
431 p_project_id => p_project_id,
432 p_txn_to_date => p_txn_to_date,
433 p_purge_release => p_purge_release,
434 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
435 x_err_code => x_err_code,
436 x_err_stack => x_err_stack,
437 x_err_stage => x_err_stage
438 ) ;
439
440 end if;
441 pa_debug.debug( ' ->Before delete from pa_cust_rev_dist_lines_all ') ;
442 /* commented and modified as below for performance reasons. Archive Purge 11.5
443 delete from pa_cust_rev_dist_lines_all crdl
444 where (crdl.rowid) in
445 ( select crdl1.rowid
446 from pa_cust_rev_dist_lines_all crdl1,
447 PA_CUST_RDL_AR crdl2
448 where crdl2.expenditure_item_id = crdl1.expenditure_item_id
449 and crdl2.line_num = crdl1.line_num
450 and crdl2.purge_project_id = p_project_id
451 ) ;
452 */
453
454 /* delete from pa_cust_rev_dist_lines_all crdl
455 where (crdl.expenditure_item_id, crdl.line_num) in
456 ( select crdl2.expenditure_item_id, crdl2.line_num
457 from PA_CUST_RDL_AR crdl2
458 where crdl2.purge_project_id = p_project_id
459 ) ;
460
461
462 l_NoOfRecordsDel := SQL%ROWCOUNT ;
463 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
464
465 pa_debug.debug( ' ->After delete from pa_cust_rev_dist_lines_all ') ;
466
467
468 end if ;
469 else
470
471 l_commit_size := pa_utils2.arpur_mrc_commit_size; */
472
473 -- If the archive option is not selected then the delete will
474 -- be based on the commit size.
475
476
477 pa_debug.debug( ' ->Before delete from pa_cust_rev_dist_lines_all ') ;
478 /* commented and modified as below for performance reasons. Archive Purge 11.5
479 delete from pa_cust_rev_dist_lines_all crdl
480 where (crdl.rowid) in
481 ( select crdl1.rowid
482 from pa_cust_rev_dist_lines_all crdl1
483 where crdl1.project_id = p_project_id
484 and rownum <= l_commit_size
485 ) ;
486 */
487 /* delete from pa_cust_rev_dist_lines_all crdl
488 where crdl.project_id = p_project_id
489 and rownum <= l_commit_size; */
490 FORALL l_crdl_ind IN l_crdl_rowid_tab.FIRST ..l_crdl_rowid_tab.LAST
491 DELETE FROM pa_cust_rev_dist_lines_all crdl
492 WHERE crdl.rowid = l_crdl_rowid_tab(l_crdl_ind);
493
494 /*Code Changes for Bug No.2984871 start */
495 l_NoOfRecordsDel := SQL%ROWCOUNT ;
496 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
497 /*Code Changes for Bug No.2984871 end */
498
499 pa_debug.debug( ' ->After delete from pa_cust_rev_dist_lines_all ') ;
500 /* end if ; */
501
502 /* if SQL%ROWCOUNT = 0 then
503 -- Once the SqlCount becomes 0, which means that there are
504 -- no more records to be purged then we exit the loop.
505
506 exit ;
507
508 else */
509 -- After "deleting" or "deleting and inserting" a set of records
510 -- the transaction is commited. This also creates a record in the
511 -- Pa_Purge_Project_details which will show the no. of records
512 -- that are purged from each table.
513
514 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
515
516 IF l_NoOfRecordsDel > 0 Then
517 pa_purge.CommitProcess
518 (p_purge_batch_id => p_purge_batch_id,
519 p_project_id => p_project_id,
520 p_table_name => 'PA_CUST_REV_DIST_LINES_ALL',
521 p_NoOfRecordsIns => l_NoOfRecordsIns,
522 p_NoOfRecordsDel => l_NoOfRecordsDel,
523 x_err_code => x_err_code,
524 x_err_stack => x_err_stack,
525 x_err_stage => x_err_stage,
526 p_MRC_table_name => 'PA_MC_CUST_RDL_ALL',
527 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
528 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
529 ) ;
530
531 PA_UTILS2.mrc_row_count := 0;
532 END IF;
533 end if ;
534 IF ( l_fetch_complete) THEN
535 exit;
536 END IF;
537 END LOOP ;
538
539 x_err_stack := l_old_err_stack ;
540
541 EXCEPTION
542 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
543 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
544
545 WHEN OTHERS THEN
546 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_CUSTREVDISTLINES' );
547 pa_debug.debug('Error stage is '||x_err_stage );
548 pa_debug.debug('Error stack is '||x_err_stack );
549 pa_debug.debug(SQLERRM);
550 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
551
552 /* ATG NOCOPY changes */
553 x_err_stack := l_old_err_stack ;
554
555
556 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
557
558 end PA_CUSTREVDISTLINES ;
559
560 -- Start of comments
561 -- API name : PA_MC_EventRevDistLines
562 -- Type : Private
563 -- Pre-reqs : None
564 -- Function : Archive and Purge data for table Pa_MC_Cust_Event_Rdl_ar
565 -- Parameters : See common list above
566 -- End of comments
567 procedure PA_MC_EventRevDistLines
568 ( p_purge_batch_id IN NUMBER,
569 p_project_id IN NUMBER,
570 p_txn_to_date IN DATE,
571 p_purge_release IN VARCHAR2,
572 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
573 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
574 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
575 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
576 ) is
577
578 l_old_err_stage VARCHAR2(2000);
579 l_old_err_stack VARCHAR2(2000);
580
581 begin
582
583 l_old_err_stack := x_err_stack;
584
585 x_err_stack := x_err_stack || ' ->Entering PA_MC_EventRevDistLines ';
586
587 pa_debug.debug(x_err_stack);
588
589 pa_debug.debug( ' ->Before insert into Pa_MC_Cust_Event_Rdl_AR') ;
590
591 /* Commented out for MRC migration to SLA
592 insert into Pa_MC_Cust_Event_Rdl_AR
593 (
594 Purge_Batch_Id,
595 Purge_Release,
596 Purge_Project_Id,
597 Draft_Invoice_Item_Line_Num,
598 Currency_Code,
599 Exchange_Rate,
600 Conversion_Date,
601 Set_Of_Books_Id,
602 Project_Id,
603 Task_Id,
604 Event_Num,
605 Line_Num,
606 Amount,
607 Request_Id,
608 Program_Application_Id,
609 Program_Id,
610 Program_Update_Date,
611 Batch_Name,
612 Draft_Revenue_Num,
613 Draft_Revenue_Item_Line_Num,
614 Draft_Invoice_Num,
615 Prc_Assignment_Id,
616 Rate_Type
617 )
618 select
619 p_purge_batch_id,
620 p_purge_release,
621 p_project_id,
622 mc.Draft_Invoice_Item_Line_Num,
623 mc.Currency_Code,
624 mc.Exchange_Rate,
625 mc.Conversion_Date,
626 mc.Set_Of_Books_Id,
627 mc.Project_Id,
628 mc.Task_Id,
629 mc.Event_Num,
630 mc.Line_Num,
631 mc.Amount,
632 mc.Request_Id,
633 mc.Program_Application_Id,
634 mc.Program_Id,
635 mc.Program_Update_Date,
636 mc.Batch_Name,
637 mc.Draft_Revenue_Num,
638 mc.Draft_Revenue_Item_Line_Num,
639 mc.Draft_Invoice_Num,
640 mc.Prc_Assignment_Id,
641 mc.Rate_Type
642 from pa_mc_cust_event_rdl_all mc,
643 PA_Cust_Event_RDL_AR ar
644 where ar.Purge_Project_Id = p_project_id
645 and mc.project_id = ar.Purge_Project_Id
646 and mc.event_num = ar.event_num
647 and nvl(mc.task_id,-99) = nvl(ar.task_id,-99)
648 and mc.line_num = ar.line_num;
649
650 */
651 p_mcnoofrecordsins := SQL%ROWCOUNT ;
652
653 pa_debug.debug( ' ->After insert into Pa_MC_Cust_Event_Rdl_AR') ;
654 x_err_stack := l_old_err_stack ;
655
656 EXCEPTION
657 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
658 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
659
660 WHEN OTHERS THEN
661 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_EventRevDistLines');
662 pa_debug.debug('Error stage is '||x_err_stage );
663 pa_debug.debug('Error stack is '||x_err_stack );
664 pa_debug.debug(SQLERRM);
665 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM;
666
667 /* ATG Changes */
668 p_mcnoofrecordsins := null;
669 x_err_stack := l_old_err_stack ;
670
671 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
672
673 end PA_MC_EventRevDistLines ;
674
675 -- Start of comments
676 -- API name : PA_EventRevDistLines
677 -- Type : Private
678 -- Pre-reqs : None
679 -- Function : Archive and Purge data for table PA_CUST_EVENT_RDL_ALL
680 -- Parameters : See common list above
681 -- End of comments
682 procedure PA_EventRevDistLines
683 ( p_purge_batch_id IN NUMBER,
684 p_project_id IN NUMBER,
685 p_txn_to_date IN DATE,
686 p_purge_release IN VARCHAR2,
687 p_archive_flag IN VARCHAR2,
688 p_commit_size IN NUMBER,
689 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
690 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
691 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
692 ) is
693
694 l_old_err_stage VARCHAR2(2000);
695 l_old_err_stack VARCHAR2(2000);
696 l_NoOfRecordsIns NUMBER;
697 l_NoOfRecordsDel NUMBER;
698 l_MC_NoOfRecordsIns NUMBER := NULL;
699 l_MC_NoOfRecordsDel NUMBER := NULL;
700 l_erdl_rowid_tab PA_PLSQL_DATATYPES.RowIDTabTyp;
701 l_erdl_rowid_tab_empty PA_PLSQL_DATATYPES.RowIDTabTyp;
702 l_fetch_complete BOOLEAN := FALSE;
703 l_erdl_ind NUMBER;
704
705 cursor c_erdl_records is
706 select erdl.rowid from pa_cust_event_rdl_all erdl
707 where erdl.project_id = p_project_id;
708
709 begin
710
711 l_old_err_stack := x_err_stack;
712
713 x_err_stack := x_err_stack || ' Entering PA_EventRevDistLines ' ;
714
715 pa_debug.debug(x_err_stack);
716 IF p_archive_flag = 'Y' THEN
717 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
718 ELSE
719 l_commit_size := pa_utils2.arpur_mrc_commit_size ;
720 END IF;
721
722 OPEN c_erdl_records;
723
724
725 LOOP
726 l_NoOfRecordsIns := 0;
727 l_NoOfRecordsDel := 0;
728 l_erdl_rowid_tab := l_erdl_rowid_tab_empty;
729 FETCH c_erdl_records BULK COLLECT INTO l_erdl_rowid_tab LIMIT l_commit_size;
730
731 IF c_erdl_records%NOTFOUND THEN
732 CLOSE c_erdl_records;
733 l_fetch_complete := TRUE;
734 END IF;
735 IF l_erdl_rowid_tab.LAST is not null Then
736 if p_archive_flag = 'Y' then
737 -- If archive option is selected then the records are
738 -- inserted into the archived into the archive tables
739 -- before being purged. The where condition is such that
740 -- only the it inserts half the no. of records specified
741 -- in the commit size.
742
743 /* l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ; */
744
745 pa_debug.debug( ' ->Before insert into PA_Cust_Event_RDL_AR') ;
746 FORALL l_erdl_ind IN l_erdl_rowid_tab.FIRST .. l_erdl_rowid_tab.LAST
747 insert into PA_Cust_Event_RDL_AR
748 (
749 PURGE_BATCH_ID,
750 PURGE_RELEASE,
751 PURGE_PROJECT_ID,
752 TASK_ID,
753 EVENT_NUM,
754 LINE_NUM,
755 AMOUNT,
756 CREATION_DATE,
757 CREATED_BY,
758 PROJECT_ID,
759 DRAFT_REVENUE_NUM,
760 DRAFT_REVENUE_ITEM_LINE_NUM,
761 CODE_COMBINATION_ID,
762 REQUEST_ID,
763 PROGRAM_APPLICATION_ID,
764 PROGRAM_ID,
765 PROGRAM_UPDATE_DATE,
766 DRAFT_INVOICE_NUM,
767 DRAFT_INVOICE_ITEM_LINE_NUM,
768 BATCH_NAME,
769 LINE_NUM_REVERSED,
770 REVERSED_FLAG,
771 ORG_ID,
772 OUTPUT_VAT_TAX_ID,
773 OUTPUT_TAX_CLASSIFICATION_CODE,
774 OUTPUT_TAX_EXEMPT_FLAG,
775 OUTPUT_TAX_EXEMPT_REASON_CODE,
776 OUTPUT_TAX_EXEMPT_NUMBER,
777 PRC_GENERATED_FLAG,
778 BILL_TRANS_CURRENCY_CODE,
779 BILL_TRANS_AMOUNT,
780 REVPROC_CURRENCY_CODE,
781 REVPROC_RATE_TYPE,
782 REVPROC_RATE_DATE,
783 REVPROC_EXCHANGE_RATE,
784 INVPROC_CURRENCY_CODE,
785 INVPROC_RATE_TYPE,
786 INVPROC_RATE_DATE,
787 INVPROC_EXCHANGE_RATE,
788 PROJECT_CURRENCY_CODE,
789 PROJECT_REVENUE_AMOUNT,
790 PROJECT_REV_RATE_TYPE,
791 PROJECT_REV_RATE_DATE,
792 PROJECT_REV_EXCHANGE_RATE,
793 PROJECT_BILL_AMOUNT,
794 PROJECT_INV_RATE_TYPE,
795 PROJECT_INV_RATE_DATE,
796 PROJECT_INV_EXCHANGE_RATE,
797 PROJFUNC_CURRENCY_CODE,
798 PROJFUNC_REVENUE_AMOUNT,
799 PROJFUNC_REV_RATE_TYPE,
800 PROJFUNC_REV_RATE_DATE,
801 PROJFUNC_REV_EXCHANGE_RATE,
802 PROJFUNC_BILL_AMOUNT,
803 PROJFUNC_INV_RATE_TYPE,
804 PROJFUNC_INV_RATE_DATE,
805 PROJFUNC_INV_EXCHANGE_RATE,
806 FUNDING_CURRENCY_CODE,
807 FUNDING_REVENUE_AMOUNT,
808 FUNDING_REV_RATE_TYPE,
809 FUNDING_REV_RATE_DATE,
810 FUNDING_REV_EXCHANGE_RATE,
811 FUNDING_BILL_AMOUNT,
812 FUNDING_INV_RATE_TYPE,
813 FUNDING_INV_RATE_DATE,
814 FUNDING_INV_EXCHANGE_RATE,
815 INV_GEN_REJECTION_CODE,
816 RETN_DRAFT_INVOICE_NUM,
817 RETN_DRAFT_INVOICE_LINE_NUM,
818 RETAINED_AMOUNT,
819 RETENTION_RULE_ID
820 )
821 select
822 p_purge_batch_id,
823 p_purge_release,
824 p_project_id,
825 TASK_ID,
826 EVENT_NUM,
827 LINE_NUM,
828 AMOUNT,
829 CREATION_DATE,
830 CREATED_BY,
831 PROJECT_ID,
832 DRAFT_REVENUE_NUM,
833 DRAFT_REVENUE_ITEM_LINE_NUM,
834 CODE_COMBINATION_ID,
835 REQUEST_ID,
836 PROGRAM_APPLICATION_ID,
837 PROGRAM_ID,
838 PROGRAM_UPDATE_DATE,
839 DRAFT_INVOICE_NUM,
840 DRAFT_INVOICE_ITEM_LINE_NUM,
841 BATCH_NAME,
842 LINE_NUM_REVERSED,
843 REVERSED_FLAG,
844 ORG_ID,
845 OUTPUT_VAT_TAX_ID,
846 OUTPUT_TAX_CLASSIFICATION_CODE,
847 OUTPUT_TAX_EXEMPT_FLAG,
848 OUTPUT_TAX_EXEMPT_REASON_CODE,
849 OUTPUT_TAX_EXEMPT_NUMBER,
850 PRC_GENERATED_FLAG,
851 BILL_TRANS_CURRENCY_CODE,
852 BILL_TRANS_AMOUNT,
853 REVPROC_CURRENCY_CODE,
854 REVPROC_RATE_TYPE,
855 REVPROC_RATE_DATE,
856 REVPROC_EXCHANGE_RATE,
857 INVPROC_CURRENCY_CODE,
858 INVPROC_RATE_TYPE,
859 INVPROC_RATE_DATE,
860 INVPROC_EXCHANGE_RATE,
861 PROJECT_CURRENCY_CODE,
862 PROJECT_REVENUE_AMOUNT,
863 PROJECT_REV_RATE_TYPE,
864 PROJECT_REV_RATE_DATE,
865 PROJECT_REV_EXCHANGE_RATE,
866 PROJECT_BILL_AMOUNT,
867 PROJECT_INV_RATE_TYPE,
868 PROJECT_INV_RATE_DATE,
869 PROJECT_INV_EXCHANGE_RATE,
870 PROJFUNC_CURRENCY_CODE,
871 PROJFUNC_REVENUE_AMOUNT,
872 PROJFUNC_REV_RATE_TYPE,
873 PROJFUNC_REV_RATE_DATE,
874 PROJFUNC_REV_EXCHANGE_RATE,
875 PROJFUNC_BILL_AMOUNT,
876 PROJFUNC_INV_RATE_TYPE,
877 PROJFUNC_INV_RATE_DATE,
878 PROJFUNC_INV_EXCHANGE_RATE,
879 FUNDING_CURRENCY_CODE,
880 FUNDING_REVENUE_AMOUNT,
881 FUNDING_REV_RATE_TYPE,
882 FUNDING_REV_RATE_DATE,
883 FUNDING_REV_EXCHANGE_RATE,
884 FUNDING_BILL_AMOUNT,
885 FUNDING_INV_RATE_TYPE,
886 FUNDING_INV_RATE_DATE,
887 FUNDING_INV_EXCHANGE_RATE,
888 INV_GEN_REJECTION_CODE,
889 RETN_DRAFT_INVOICE_NUM,
890 RETN_DRAFT_INVOICE_LINE_NUM,
891 RETAINED_AMOUNT,
892 RETENTION_RULE_ID
893 from pa_cust_event_rdl_all erdl
894 where erdl.rowid = l_erdl_rowid_tab(l_erdl_ind);
895
896 l_NoOfRecordsIns := SQL%ROWCOUNT ;
897 END IF;
898 /*
899 where (
900 project_id = p_project_id
901 and rownum < l_commit_size
902 ) ;
903 */
904
905 pa_debug.debug( ' ->After insert into PA_Cust_Event_RDL_AR') ;
906 /* l_NoOfRecordsIns := SQL%ROWCOUNT ; */
907
908 if l_NoOfRecordsIns > 0 then
909 -- First call the MRC procedure to archive the MC table
910 -- We have a seperate delete statement if the archive option is
911 -- selected because if archive option is selected the the records
912 -- being purged will be those records which are already archived.
913 -- table and
914
915 PA_MC_EventRevDistLines
916 ( p_purge_batch_id => p_purge_batch_id,
917 p_project_id => p_project_id,
918 p_txn_to_date => p_txn_to_date,
919 p_purge_release => p_purge_release,
920 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
921 x_err_code => x_err_code,
922 x_err_stack => x_err_stack,
923 x_err_stage => x_err_stage
924 ) ;
925 end if;
926 pa_debug.debug( ' ->Before delete from pa_cust_event_rdl_all ') ;
927 /* commented and modified as below for performance reasons. Archive Purge 11.5
928 delete from pa_cust_event_rdl_all erdl
929 where (erdl.rowid)
930 in
931 ( select erdl1.rowid
932 from pa_cust_event_rdl_all erdl1,
933 pa_cust_event_rdl_ar erdl2
934 where nvl(erdl2.task_id,-99) = nvl(erdl1.task_id,-99)
935 and erdl2.event_num = erdl1.event_num
936 and erdl2.line_num = erdl1.line_num
937 and erdl1.project_id = erdl2.project_id
938 and erdl2.purge_project_id = p_project_id
939 ) ;
940 */
941 /* delete from pa_cust_event_rdl_all erdl
942 where (erdl.project_id, erdl.event_num) in
943 ( select erdl2.project_id, erdl2.event_num
944 from pa_cust_event_rdl_ar erdl2
945 where nvl(erdl2.task_id,-99) = nvl(erdl.task_id,-99)
946 and erdl2.line_num = erdl.line_num
947 and erdl2.purge_project_id = p_project_id
948 )
949 and erdl.project_id = p_project_id; -- Perf Bug 2695202
950
951 pa_debug.debug( ' ->After delete from pa_cust_event_rdl_all ') ;
952 */
953 FORALL l_erdl_ind IN l_erdl_rowid_tab.FIRST .. l_erdl_rowid_tab.LAST
954 DELETE FROM PA_CUST_EVENT_RDL_ALL ERDL
955 WHERE ERDL.rowid = l_erdl_rowid_tab(l_erdl_ind);
956
957 l_NoOfRecordsDel := SQL%ROWCOUNT ;
958 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
959 /* end if ;
960 else
961
962 l_commit_size := pa_utils2.arpur_mrc_commit_size;
963
964 -- If the archive option is not selected then the delete will
965 -- be based on the commit size.
966
967 pa_debug.debug( ' ->Before delete from pa_cust_event_rdl_all ') ;
968 */
969 /* commented and modified as below for performance reasons. Archive Purge 11.5
970 delete from pa_cust_event_rdl_all erdl
971 where (erdl.rowid)
972 in
973 ( select erdl1.rowid
974 from pa_cust_event_rdl_all erdl1
975 where erdl1.project_id = p_project_id
976 and rownum <= l_commit_size
977 ) ;
978 */
979 /*
980 delete from pa_cust_event_rdl_all erdl
981 where erdl.project_id = p_project_id
982 and rownum <= l_commit_size;
983 l_NoOfRecordsDel := SQL%ROWCOUNT ;
984 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
985 pa_debug.debug( ' ->After delete from pa_cust_event_rdl_all ') ;
986 end if ;
987
988
989 if SQL%ROWCOUNT = 0 then
990 -- Once the SqlCount becomes 0, which means that there are
991 -- no more records to be purged then we exit the loop.
992
993 exit ;
994
995 else */
996 -- After "deleting" or "deleting and inserting" a set of records
997 -- the transaction is commited. This also creates a record in the
998 -- Pa_Purge_Project_details which will show the no. of records
999 -- that are purged from each table.
1000
1001 If l_NoOfRecordsDel > 0 Then
1002 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1003 pa_purge.CommitProcess
1004 (p_purge_batch_id => p_purge_batch_id,
1005 p_project_id => p_project_id,
1006 p_table_name => 'PA_CUST_EVENT_RDL_ALL',
1007 p_NoOfRecordsIns => l_NoOfRecordsIns,
1008 p_NoOfRecordsDel => l_NoOfRecordsDel,
1009 x_err_code => x_err_code,
1010 x_err_stack => x_err_stack,
1011 x_err_stage => x_err_stage,
1012 p_MRC_table_name => 'PA_MC_CUST_EVENT_RDL_ALL',
1013 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
1014 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
1015 ) ;
1016
1017 End If;
1018 PA_UTILS2.mrc_row_count := 0;
1019 end if ;
1020 IF (l_fetch_complete) THEN
1021 exit;
1022 END IF;
1023 END LOOP ;
1024
1025
1026 x_err_stack := l_old_err_stack ;
1027 EXCEPTION
1028 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1029 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1030
1031 WHEN OTHERS THEN
1032 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_EventRevDistLines' );
1033 pa_debug.debug('Error stage is '||x_err_stage );
1034 pa_debug.debug('Error stack is '||x_err_stack );
1035 pa_debug.debug(SQLERRM);
1036 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1037
1038 /* ATG NOCOPY changes */
1039 x_err_stack := l_old_err_stack ;
1040
1041 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1042
1043 end PA_EventRevDistLines ;
1044
1045 -- Start of comments
1046 -- API name : PA_MC_Events_Trx
1047 -- Type : Private
1048 -- Pre-reqs : None
1049 -- Function : Archive and Purge data for table PA_MC_Events_AR
1050 -- Parameters : See common list above
1051 -- End of comments
1052 procedure PA_MC_Events_Trx
1053 ( p_purge_batch_id IN NUMBER,
1054 p_project_id IN NUMBER,
1055 p_txn_to_date IN DATE,
1056 p_purge_release IN VARCHAR2,
1057 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1058 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1059 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1060 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1061 ) is
1062
1063 l_old_err_stage VARCHAR2(2000);
1064 l_old_err_stack VARCHAR2(2000);
1065
1066 begin
1067
1068 l_old_err_stack := x_err_stack;
1069
1070 x_err_stack := x_err_stack || ' ->Entering PA_MC_Events_Trx ';
1071
1072 pa_debug.debug(x_err_stack);
1073
1074 pa_debug.debug( ' ->Before insert into PA_MC_Events_AR') ;
1075
1076 /* Funding MRC : Added the column description */
1077 /* Commented out for MRC migration to SLA
1078 insert into Pa_Mc_Events_AR
1079 (
1080 Purge_Batch_Id,
1081 Purge_Release,
1082 Purge_Project_Id,
1083 Set_Of_Books_Id,
1084 Event_Id,
1085 Project_Id,
1086 Task_Id,
1087 Event_Num,
1088 Revenue_Amount,
1089 Bill_Amount,
1090 Currency_Code,
1091 Exchange_Rate,
1092 Conversion_Date,
1093 Prc_Assignment_Id,
1094 Rate_Type,
1095 Projfunc_Inv_Rate_Date,
1096 Projfunc_Inv_Exchange_Rate,
1097 description
1098 )
1099 select
1100 p_purge_batch_id,
1101 p_purge_release,
1102 p_project_id,
1103 mc.Set_Of_Books_Id,
1104 mc.Event_Id,
1105 mc.Project_Id,
1106 mc.Task_Id,
1107 mc.Event_Num,
1108 mc.Revenue_Amount,
1109 mc.Bill_Amount,
1110 mc.Currency_Code,
1111 mc.Exchange_Rate,
1112 mc.Conversion_Date,
1113 mc.Prc_Assignment_Id,
1114 mc.Rate_Type,
1115 mc.Projfunc_Inv_Rate_Date,
1116 mc.Projfunc_Inv_Exchange_Rate,
1117 mc.description
1118 from Pa_Mc_Events mc,
1119 PA_Events_AR ar
1120 where ar.Purge_Project_Id = p_project_id
1121 and mc.event_id = ar.event_id;
1122 */
1123
1124 p_mcnoofrecordsins := SQL%ROWCOUNT ;
1125
1126 pa_debug.debug( ' ->After insert into PA_MC_Events_AR') ;
1127 x_err_stack := l_old_err_stack ;
1128
1129 EXCEPTION
1130 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1131 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1132
1133 WHEN OTHERS THEN
1134 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_Events_Trx');
1135 pa_debug.debug('Error stage is '||x_err_stage );
1136 pa_debug.debug('Error stack is '||x_err_stack );
1137 pa_debug.debug(SQLERRM);
1138 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM;
1139
1140 /* ATG NOCOPY changes */
1141 p_mcnoofrecordsins := null;
1142 x_err_stack := l_old_err_stack ;
1143
1144 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1145
1146 end PA_MC_Events_Trx ;
1147
1148 -- Start of comments
1149 -- API name : PA_Event
1150 -- Type : Private
1151 -- Pre-reqs : None
1152 -- Function : Archive and Purge data for table PA_EVENTS
1153 -- Parameters : See common list above
1154 -- End of comments
1155 procedure PA_Event
1156 ( p_purge_batch_id IN NUMBER,
1157 p_project_id IN NUMBER,
1158 p_txn_to_date IN DATE,
1159 p_purge_release IN VARCHAR2,
1160 p_archive_flag IN VARCHAR2,
1161 p_commit_size IN NUMBER,
1162 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1163 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1164 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1165 ) is
1166
1167 l_old_err_stage VARCHAR2(2000);
1168 l_old_err_stack VARCHAR2(2000);
1169 l_NoOfRecordsIns NUMBER := 0; --Initialized to zero for bug 3583748
1170 l_NoOfRecordsDel NUMBER := 0; --Initialized to zero for bug 3583748
1171 l_MC_NoOfRecordsIns NUMBER := NULL;
1172 l_MC_NoOfRecordsDel NUMBER := NULL;
1173 begin
1174
1175 l_old_err_stack := x_err_stack;
1176
1177 x_err_stack := x_err_stack || ' ->Entering PA_EVENT' ;
1178
1179 pa_debug.debug(x_err_stack);
1180
1181 LOOP
1182 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1183 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1184 if p_archive_flag = 'Y' then
1185 -- First call the MRC procedure to archive the MC table
1186 -- If archive option is selected then the records are
1187 -- inserted into the archived into the archive tables
1188 -- before being purged. The where condition is such that
1189 -- only the it inserts half the no. of records specified
1190 -- in the commit size.
1191
1192 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
1193
1194 pa_debug.debug( ' ->Before insert into PA_Events_AR') ;
1195
1196 /* Funding MRC Changes : Added the column zero_revenue_amount_flag */
1197
1198 insert into PA_Events_AR
1199 (
1200 PURGE_BATCH_ID,
1201 PURGE_RELEASE,
1202 PURGE_PROJECT_ID,
1203 ATTRIBUTE10,
1204 PROJECT_ID,
1205 ORGANIZATION_ID,
1206 BILLING_ASSIGNMENT_ID,
1207 EVENT_NUM_REVERSED,
1208 CALLING_PLACE,
1209 CALLING_PROCESS,
1210 TASK_ID,
1211 EVENT_NUM,
1212 LAST_UPDATE_DATE,
1213 LAST_UPDATED_BY,
1214 CREATION_DATE,
1215 CREATED_BY,
1216 LAST_UPDATE_LOGIN,
1217 EVENT_TYPE,
1218 DESCRIPTION,
1219 BILL_AMOUNT,
1220 REVENUE_AMOUNT,
1221 REVENUE_DISTRIBUTED_FLAG,
1222 BILL_HOLD_FLAG,
1223 COMPLETION_DATE,
1224 REV_DIST_REJECTION_CODE,
1225 REQUEST_ID,
1226 PROGRAM_APPLICATION_ID,
1227 PROGRAM_ID,
1228 PROGRAM_UPDATE_DATE,
1229 ATTRIBUTE_CATEGORY,
1230 ATTRIBUTE1,
1231 ATTRIBUTE2,
1232 ATTRIBUTE3,
1233 ATTRIBUTE4,
1234 ATTRIBUTE5,
1235 ATTRIBUTE6,
1236 ATTRIBUTE7,
1237 ATTRIBUTE8,
1238 ATTRIBUTE9,
1239 Event_Id,
1240 Audit_Amount1,
1241 Audit_Amount2,
1242 Audit_Amount3,
1243 Audit_Amount4,
1244 Audit_Amount5,
1245 Audit_Amount6,
1246 Audit_Amount7,
1247 Audit_Amount8,
1248 Audit_Amount9,
1249 Audit_Amount10,
1250 AUDIT_COST_BUDGET_TYPE_CODE,
1251 AUDIT_REV_BUDGET_TYPE_CODE,
1252 INVENTORY_ORG_ID,
1253 INVENTORY_ITEM_ID,
1254 QUANTITY_BILLED,
1255 UOM_CODE,
1256 UNIT_PRICE,
1257 REFERENCE1,
1258 REFERENCE2,
1259 REFERENCE3,
1260 REFERENCE4,
1261 REFERENCE5,
1262 REFERENCE6,
1263 REFERENCE7,
1264 REFERENCE8,
1265 REFERENCE9,
1266 REFERENCE10,
1267 BILLED_FLAG,
1268 BILL_TRANS_CURRENCY_CODE,
1269 BILL_TRANS_BILL_AMOUNT,
1270 BILL_TRANS_REV_AMOUNT,
1271 PROJECT_CURRENCY_CODE,
1272 PROJECT_RATE_TYPE,
1273 PROJECT_RATE_DATE,
1274 PROJECT_EXCHANGE_RATE,
1275 PROJECT_REV_RATE_DATE,
1276 PROJECT_REV_EXCHANGE_RATE,
1277 PROJECT_REVENUE_AMOUNT,
1278 PROJECT_INV_RATE_DATE,
1279 PROJECT_INV_EXCHANGE_RATE,
1280 PROJECT_BILL_AMOUNT,
1281 PROJFUNC_CURRENCY_CODE,
1282 PROJFUNC_RATE_TYPE,
1283 PROJFUNC_RATE_DATE,
1284 PROJFUNC_EXCHANGE_RATE,
1285 PROJFUNC_REV_RATE_DATE,
1286 PROJFUNC_REV_EXCHANGE_RATE,
1287 PROJFUNC_REVENUE_AMOUNT,
1288 PROJFUNC_INV_RATE_DATE,
1289 PROJFUNC_INV_EXCHANGE_RATE,
1290 PROJFUNC_BILL_AMOUNT,
1291 FUNDING_RATE_TYPE,
1292 FUNDING_RATE_DATE,
1293 FUNDING_EXCHANGE_RATE,
1294 REVPROC_CURRENCY_CODE,
1295 REVPROC_RATE_TYPE,
1296 REVPROC_RATE_DATE,
1297 REVPROC_EXCHANGE_RATE,
1298 INVPROC_CURRENCY_CODE,
1299 INVPROC_RATE_TYPE,
1300 INVPROC_RATE_DATE,
1301 INVPROC_EXCHANGE_RATE,
1302 INV_GEN_REJECTION_CODE,
1303 ADJUSTING_REVENUE_FLAG,
1304 zero_revenue_amount_flag,
1305 project_funding_id,
1306 revenue_hold_flag,
1307 non_updateable_flag,
1308 audit_rev_plan_type_id,
1309 audit_cost_plan_type_id,
1310 pm_product_code,
1311 pm_event_reference,
1312 deliverable_id,
1313 action_id,
1314 record_version_number,
1315 agreement_id /*Federal*/
1316 )
1317 select
1318 p_purge_batch_id,
1319 p_purge_release,
1320 p_project_id,
1321 ATTRIBUTE10,
1322 PROJECT_ID,
1323 ORGANIZATION_ID,
1324 BILLING_ASSIGNMENT_ID,
1325 EVENT_NUM_REVERSED,
1326 CALLING_PLACE,
1327 CALLING_PROCESS,
1328 TASK_ID,
1329 EVENT_NUM,
1330 LAST_UPDATE_DATE,
1331 LAST_UPDATED_BY,
1332 CREATION_DATE,
1333 CREATED_BY,
1334 LAST_UPDATE_LOGIN,
1335 EVENT_TYPE,
1336 DESCRIPTION,
1337 BILL_AMOUNT,
1338 REVENUE_AMOUNT,
1339 REVENUE_DISTRIBUTED_FLAG,
1340 BILL_HOLD_FLAG,
1341 COMPLETION_DATE,
1342 REV_DIST_REJECTION_CODE,
1343 REQUEST_ID,
1344 PROGRAM_APPLICATION_ID,
1345 PROGRAM_ID,
1346 PROGRAM_UPDATE_DATE,
1347 ATTRIBUTE_CATEGORY,
1348 ATTRIBUTE1,
1349 ATTRIBUTE2,
1350 ATTRIBUTE3,
1351 ATTRIBUTE4,
1352 ATTRIBUTE5,
1353 ATTRIBUTE6,
1354 ATTRIBUTE7,
1355 ATTRIBUTE8,
1356 ATTRIBUTE9,
1357 Event_Id,
1358 Audit_Amount1,
1359 Audit_Amount2,
1360 Audit_Amount3,
1361 Audit_Amount4,
1362 Audit_Amount5,
1363 Audit_Amount6,
1364 Audit_Amount7,
1365 Audit_Amount8,
1366 Audit_Amount9,
1367 Audit_Amount10,
1368 AUDIT_COST_BUDGET_TYPE_CODE,
1369 AUDIT_REV_BUDGET_TYPE_CODE,
1370 INVENTORY_ORG_ID,
1371 INVENTORY_ITEM_ID,
1372 QUANTITY_BILLED,
1373 UOM_CODE,
1374 UNIT_PRICE,
1375 REFERENCE1,
1376 REFERENCE2,
1377 REFERENCE3,
1378 REFERENCE4,
1379 REFERENCE5,
1380 REFERENCE6,
1381 REFERENCE7,
1382 REFERENCE8,
1383 REFERENCE9,
1384 REFERENCE10,
1385 BILLED_FLAG,
1386 BILL_TRANS_CURRENCY_CODE,
1387 BILL_TRANS_BILL_AMOUNT,
1388 BILL_TRANS_REV_AMOUNT,
1389 PROJECT_CURRENCY_CODE,
1390 PROJECT_RATE_TYPE,
1391 PROJECT_RATE_DATE,
1392 PROJECT_EXCHANGE_RATE,
1393 PROJECT_REV_RATE_DATE,
1394 PROJECT_REV_EXCHANGE_RATE,
1395 PROJECT_REVENUE_AMOUNT,
1396 PROJECT_INV_RATE_DATE,
1397 PROJECT_INV_EXCHANGE_RATE,
1398 PROJECT_BILL_AMOUNT,
1399 PROJFUNC_CURRENCY_CODE,
1400 PROJFUNC_RATE_TYPE,
1401 PROJFUNC_RATE_DATE,
1402 PROJFUNC_EXCHANGE_RATE,
1403 PROJFUNC_REV_RATE_DATE,
1404 PROJFUNC_REV_EXCHANGE_RATE,
1405 PROJFUNC_REVENUE_AMOUNT,
1406 PROJFUNC_INV_RATE_DATE,
1407 PROJFUNC_INV_EXCHANGE_RATE,
1408 PROJFUNC_BILL_AMOUNT,
1409 FUNDING_RATE_TYPE,
1410 FUNDING_RATE_DATE,
1411 FUNDING_EXCHANGE_RATE,
1412 REVPROC_CURRENCY_CODE,
1413 REVPROC_RATE_TYPE,
1414 REVPROC_RATE_DATE,
1415 REVPROC_EXCHANGE_RATE,
1416 INVPROC_CURRENCY_CODE,
1417 INVPROC_RATE_TYPE,
1418 INVPROC_RATE_DATE,
1419 INVPROC_EXCHANGE_RATE,
1420 INV_GEN_REJECTION_CODE,
1421 ADJUSTING_REVENUE_FLAG,
1422 zero_revenue_amount_flag,
1423 project_funding_id,
1424 revenue_hold_flag,
1425 non_updateable_flag,
1426 audit_rev_plan_type_id,
1427 audit_cost_plan_type_id ,
1428 pm_product_code,
1429 pm_event_reference,
1430 deliverable_id,
1431 action_id,
1432 record_version_number,
1433 agreement_id
1434 from pa_events
1435 where (
1436 project_id = p_project_id
1437 and rownum <= l_commit_size
1438 ) ;
1439
1440 l_NoOfRecordsIns := SQL%ROWCOUNT;
1441
1442 pa_debug.debug( ' ->After insert into PA_Events_AR') ;
1443
1444
1445 /*Code Changes for Bug No.2984871 start */
1446 if l_NoOfRecordsIns > 0 then
1447 /*Code Changes for Bug No.2984871 end */
1448
1449 -- First call the MRC procedure to archive the MC table
1450 -- We have a seperate delete statement if the archive option is
1451 -- selected because if archive option is selected the the records
1452 -- being purged will be those records which are already archived.
1453 -- table and
1454
1455 PA_MC_Events_Trx
1456 ( p_purge_batch_id => p_purge_batch_id,
1457 p_project_id => p_project_id,
1458 p_txn_to_date => p_txn_to_date,
1459 p_purge_release => p_purge_release,
1460 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
1461 x_err_code => x_err_code,
1462 x_err_stack => x_err_stack,
1463 x_err_stage => x_err_stage
1464 ) ;
1465
1466 pa_debug.debug( ' ->Before delete from pa_events ') ;
1467 /* commented and modified as below for performance reasons. Archive Purge 11.5
1468 delete from pa_events ev
1469 where (ev.rowid)
1470 in
1471 ( select ev1.rowid
1472 from pa_events ev1,
1473 pa_events_ar ev2
1474 where nvl(ev2.task_id,-99) = nvl(ev1.task_id,-99)
1475 and ev2.event_num = ev1.event_num
1476 and ev2.project_id = ev1.project_id
1477 and ev2.purge_project_id = p_project_id
1478 ) ;
1479 */
1480 delete from pa_events ev
1481 where (ev.project_id, ev.event_num) in
1482 ( select ev2.project_id, ev2.event_num
1483 from pa_events_ar ev2
1484 where nvl(ev2.task_id,-99) = nvl(ev.task_id,-99)
1485 and ev2.purge_project_id = p_project_id
1486 )
1487 and ev.project_id = p_project_id; -- Perf Bug 2695202
1488
1489 /*Code Changes for Bug No.2984871 start */
1490 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1491 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
1492 /*Code Changes for Bug No.2984871 end */
1493
1494 pa_debug.debug( ' ->After delete from pa_events ') ;
1495
1496
1497 end if ;
1498 else
1499
1500 l_commit_size := pa_utils2.arpur_mrc_commit_size;
1501
1502 -- If the archive option is not selected then the delete will
1503 -- be based on the commit size.
1504
1505 pa_debug.debug( ' ->Before delete from pa_events ') ;
1506 /* commented and modified as below for performance reasons. Archive Purge 11.5
1507 delete from pa_events ev
1508 where (ev.rowid)
1509 in
1510 ( select ev1.rowid
1511 from pa_events ev1
1512 where ev1.project_id = p_project_id
1513 and rownum <= l_commit_size
1514 ) ;
1515 */
1516 delete from pa_events ev
1517 where ev.project_id = p_project_id
1518 and rownum <= l_commit_size;
1519 /*Code Changes for Bug No.2984871 start */
1520 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1521 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
1522 /*Code Changes for Bug No.2984871 end */
1523
1524 pa_debug.debug( ' ->After delete from pa_events ') ;
1525
1526 end if ;
1527
1528 /*Code Changes for Bug No.2984871 start */
1529 if l_NoOfRecordsDel = 0 then
1530 /*Code Changes for Bug No.2984871 end*/
1531
1532 -- Once the SqlCount becomes 0, which means that there are
1533 -- no more records to be purged then we exit the loop.
1534
1535 exit ;
1536
1537 else
1538 -- After "deleting" or "deleting and inserting" a set of records
1539 -- the transaction is commited. This also creates a record in the
1540 -- Pa_Purge_Project_details which will show the no. of records
1541 -- that are purged from each table.
1542
1543 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1544 pa_purge.CommitProcess
1545 (p_purge_batch_id => p_purge_batch_id,
1546 p_project_id => p_project_id,
1547 p_table_name => 'PA_EVENTS',
1548 p_NoOfRecordsIns => l_NoOfRecordsIns,
1549 p_NoOfRecordsDel => l_NoOfRecordsDel,
1550 x_err_code => x_err_code,
1551 x_err_stack => x_err_stack,
1552 x_err_stage => x_err_stage,
1553 p_MRC_table_name => 'PA_MC_EVENTS',
1554 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
1555 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
1556 ) ;
1557
1558 PA_UTILS2.mrc_row_count := 0;
1559
1560 end if ;
1561 END LOOP ;
1562
1563
1564 x_err_stack := l_old_err_stack ;
1565 EXCEPTION
1566 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1567 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1568
1569 WHEN OTHERS THEN
1570 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_Event' );
1571 pa_debug.debug('Error stage is '||x_err_stage );
1572 pa_debug.debug('Error stack is '||x_err_stack );
1573 pa_debug.debug(SQLERRM);
1574 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1575
1576 /* ATG NOCOPY changes */
1577 x_err_stack := l_old_err_stack ;
1578
1579 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1580
1581 end PA_Event ;
1582
1583 -- Start of comments
1584 -- API name : PA_DraftRevItems
1585 -- Type : Private
1586 -- Pre-reqs : None
1587 -- Function : Archive and Purge data for table PA_Draft_Revenue_Items
1588 -- Parameters : See common list above
1589 -- End of comments
1590 procedure PA_DraftRevItems
1591 ( p_purge_batch_id IN NUMBER,
1592 p_project_id IN NUMBER,
1593 p_txn_to_date IN DATE,
1594 p_purge_release IN VARCHAR2,
1595 p_archive_flag IN VARCHAR2,
1596 p_commit_size IN NUMBER,
1597 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1598 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1599 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1600 ) is
1601
1602 l_old_err_stage VARCHAR2(2000);
1603 l_old_err_stack VARCHAR2(2000);
1604 l_NoOfRecordsIns NUMBER := 0; --Initialized to zero for bug 3583748
1605 l_NoOfRecordsDel NUMBER := 0; --Initialized to zero for bug 3583748
1606 begin
1607
1608 l_old_err_stack := x_err_stack;
1609
1610 x_err_stack := x_err_stack || ' ->Entering PA_DRAFTREVITEMS ' ;
1611
1612 pa_debug.debug(x_err_stack);
1613
1614 LOOP
1615 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1616 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1617 if p_archive_flag = 'Y' then
1618 -- If archive option is selected then the records are
1619 -- inserted into the archived into the archive tables
1620 -- before being purged. The where condition is such that
1621 -- only the it inserts half the no. of records specified
1622 -- in the commit size.
1623
1624 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
1625
1626 pa_debug.debug( ' ->Before insert into PA_DRAFT_REV_ITEMS_AR') ;
1627 insert into PA_DRAFT_REV_ITEMS_AR (
1628 PURGE_BATCH_ID,
1629 PURGE_RELEASE,
1630 PURGE_PROJECT_ID,
1631 PROJECT_ID,
1632 DRAFT_REVENUE_NUM,
1633 LINE_NUM,
1634 LAST_UPDATE_DATE,
1635 LAST_UPDATED_BY,
1636 CREATION_DATE,
1637 CREATED_BY,
1638 TASK_ID,
1639 AMOUNT,
1640 REVENUE_SOURCE,
1641 REVENUE_CATEGORY_CODE,
1642 LAST_UPDATE_LOGIN,
1643 REQUEST_ID,
1644 PROGRAM_APPLICATION_ID,
1645 PROGRAM_ID,
1646 PROGRAM_UPDATE_DATE,
1647 REVPROC_CURRENCY_CODE,
1648 PROJFUNC_CURRENCY_CODE,
1649 PROJFUNC_REVENUE_AMOUNT,
1650 PROJECT_CURRENCY_CODE,
1651 PROJECT_REVENUE_AMOUNT,
1652 FUNDING_CURRENCY_CODE,
1653 FUNDING_REVENUE_AMOUNT
1654 )
1655 select
1656 p_purge_batch_id,
1657 p_purge_release,
1658 p_project_id,
1659 PROJECT_ID,
1660 DRAFT_REVENUE_NUM,
1661 LINE_NUM,
1662 LAST_UPDATE_DATE,
1663 LAST_UPDATED_BY,
1664 CREATION_DATE,
1665 CREATED_BY,
1666 TASK_ID,
1667 AMOUNT,
1668 REVENUE_SOURCE,
1669 REVENUE_CATEGORY_CODE,
1670 LAST_UPDATE_LOGIN,
1671 REQUEST_ID,
1672 PROGRAM_APPLICATION_ID,
1673 PROGRAM_ID,
1674 PROGRAM_UPDATE_DATE,
1675 REVPROC_CURRENCY_CODE,
1676 PROJFUNC_CURRENCY_CODE,
1677 PROJFUNC_REVENUE_AMOUNT,
1678 PROJECT_CURRENCY_CODE,
1679 PROJECT_REVENUE_AMOUNT,
1680 FUNDING_CURRENCY_CODE,
1681 FUNDING_REVENUE_AMOUNT
1682 from pa_draft_revenue_items dri
1683 where (
1684 dri.project_id = p_project_id
1685 and rownum <= l_commit_size
1686 ) ;
1687
1688 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1689
1690 pa_debug.debug( ' ->After insert into PA_DRAFT_REV_ITEMS_AR') ;
1691
1692 /*Code Changes for Bug No.2984871 start */
1693 if l_NoOfRecordsIns > 0 then
1694 /*Code Changes for Bug No.2984871 end */
1695
1696 -- We have a seperate delete statement if the archive option is
1697 -- selected because if archive option is selected the the records
1698 -- being purged will be those records which are already archived.
1699 -- table and
1700
1701 pa_debug.debug( ' ->Before delete from pa_draft_revenue_items ') ;
1702 /* commented and modified as below for performance reasons. Archive Purge 11.5
1703 delete from pa_draft_revenue_items dri
1704 where (dri.rowid)
1705 in
1706 ( select dri1.rowid
1707 from pa_draft_revenue_items dri1,
1708 PA_DRAFT_REV_ITEMS_AR dri2
1709 where dri2.draft_revenue_num = dri1.draft_revenue_num
1710 and dri2.line_num = dri1.line_num
1711 and dri2.project_id = dri1.project_id
1712 and dri2.purge_project_id = p_project_id
1713 ) ;
1714 */
1715 /* Commented the delete statement and added the modified code below not to correlate queries */
1716 /* delete from pa_draft_revenue_items dri
1717 where (dri.project_id, dri.draft_revenue_num) in
1718 ( select dri2.project_id, dri2.draft_revenue_num
1719 from PA_DRAFT_REV_ITEMS_AR dri2
1720 where dri2.line_num = dri.line_num
1721 and dri2.purge_project_id = p_project_id
1722 ) ;
1723 */
1724 delete from pa_draft_revenue_items dri
1725 where (dri.project_id, dri.draft_revenue_num, dri.line_num) in
1726 ( select dri2.project_id, dri2.draft_revenue_num, dri2.line_num
1727 from PA_DRAFT_REV_ITEMS_AR dri2
1728 where dri2.purge_project_id = p_project_id
1729 ) ;
1730 /*Code Changes for Bug No.2984871 start */
1731 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1732 /*Code Changes for Bug No.2984871 end */
1733
1734 pa_debug.debug( ' ->After delete from pa_draft_revenue_items ') ;
1735
1736 end if ;
1737 else
1738
1739 l_commit_size := pa_utils2.arpur_mrc_commit_size;
1740
1741 -- If the archive option is not selected then the delete will
1742 -- be based on the commit size.
1743
1744 pa_debug.debug( ' ->Before delete from pa_draft_revenue_items ') ;
1745 /* commented and modified as below for performance reasons. Archive Purge 11.5
1746 delete from pa_draft_revenue_items dri
1747 where (dri.rowid)
1748 in
1749 ( select dri1.rowid
1750 from pa_draft_revenue_items dri1
1751 where dri1.project_id = p_project_id
1752 and rownum <= l_commit_size
1753 ) ;
1754 */
1755 delete from pa_draft_revenue_items dri
1756 where dri.project_id = p_project_id
1757 and rownum <= l_commit_size;
1758 /*Code Changes for Bug No.2984871 start */
1759 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1760 /*Code Changes for Bug No.2984871 end */
1761
1762 pa_debug.debug( ' ->After delete from pa_draft_revenue_items ') ;
1763 end if ;
1764
1765 /*Code Changes for Bug No.2984871 start */
1766 if l_NoOfRecordsDel= 0 then
1767 /*Code Changes for Bug No.2984871 end */
1768 -- Once the SqlCount becomes 0, which means that there are
1769 -- no more records to be purged then we exit the loop.
1770
1771 exit ;
1772
1773 else
1774 -- After "deleting" or "deleting and inserting" a set of records
1775 -- the transaction is commited. This also creates a record in the
1776 -- Pa_Purge_Project_details which will show the no. of records
1777 -- that are purged from each table.
1778
1779 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
1780 pa_purge.CommitProcess
1781 (p_purge_batch_id => p_purge_batch_id,
1782 p_project_id => p_project_id,
1783 p_table_name => 'PA_DRAFT_REVENUE_ITEMS',
1784 p_NoOfRecordsIns => l_NoOfRecordsIns,
1785 p_NoOfRecordsDel => l_NoOfRecordsDel,
1786 x_err_code => x_err_code,
1787 x_err_stack => x_err_stack,
1788 x_err_stage => x_err_stage
1789 ) ;
1790
1791 end if ;
1792 END LOOP ;
1793
1794
1795 x_err_stack := l_old_err_stack ;
1796 EXCEPTION
1797 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1798 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1799
1800 WHEN OTHERS THEN
1801 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_DraftRevItems' );
1802 pa_debug.debug('Error stage is '||x_err_stage );
1803 pa_debug.debug('Error stack is '||x_err_stack );
1804 pa_debug.debug(SQLERRM);
1805 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1806
1807 /* ATG NOCOPY changes */
1808 x_err_stack := l_old_err_stack ;
1809
1810 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1811
1812 end PA_DraftRevItems ;
1813
1814 -- Start of comments
1815 -- API name : PA_MC_DRAFTINVOICEITEMS
1816 -- Type : Private
1817 -- Pre-reqs : None
1818 -- Function : Archive and Purge data for table PA_MC_DRAFT_INV_ITMS_AR
1819 -- Parameters : See common list above
1820 -- End of comments
1821 procedure PA_MC_DraftInvoiceItems
1822 ( p_purge_batch_id IN NUMBER,
1823 p_project_id IN NUMBER,
1824 p_txn_to_date IN DATE,
1825 p_purge_release IN VARCHAR2,
1826 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1827 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1828 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1829 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1830 ) is
1831
1832 l_old_err_stage VARCHAR2(2000);
1833 l_old_err_stack VARCHAR2(2000);
1834
1835 begin
1836
1837
1838 l_old_err_stack := x_err_stack;
1839
1840 x_err_stack := x_err_stack || ' ->Entering PA_MC_DRAFTINVOICEITEMS ';
1841
1842 pa_debug.debug(x_err_stack);
1843
1844 pa_debug.debug( ' ->Before insert into PA_MC_DRAFT_INV_ITMS_AR') ;
1845
1846 /* Commented out for MRC migration to SLA insert into PA_MC_DRAFT_INV_ITMS_AR
1847 (
1848 Purge_Batch_Id,
1849 Purge_Release,
1850 Purge_Project_Id,
1851 Set_Of_Books_Id,
1852 Project_Id,
1853 Draft_Invoice_Num,
1854 Line_Num,
1855 Amount,
1856 Unbilled_Receivable_Dr,
1857 Unearned_Revenue_Cr,
1858 Prc_Assignment_Id,
1859 Currency_Code,
1860 Exchange_Rate,
1861 Conversion_Date,
1862 Rate_Type
1863 )
1864 select
1865 p_purge_batch_id,
1866 p_purge_release,
1867 p_project_id,
1868 mc.Set_Of_Books_Id,
1869 mc.Project_Id,
1870 mc.Draft_Invoice_Num,
1871 mc.Line_Num,
1872 mc.Amount,
1873 mc.Unbilled_Receivable_Dr,
1874 mc.Unearned_Revenue_Cr,
1875 mc.Prc_Assignment_Id,
1876 mc.Currency_Code,
1877 mc.Exchange_Rate,
1878 mc.Conversion_Date,
1879 mc.Rate_Type
1880 from Pa_Mc_Draft_Inv_Items mc,
1881 -- PA_MC_DRAFT_INV_ITMS_AR ar Bug 2590517
1882 PA_DRAFT_INV_ITEMS_AR ar
1883 where ar.Purge_Project_Id = p_project_id
1884 and mc.Project_Id = ar.Purge_Project_Id
1885 and mc.Draft_Invoice_Num = ar.Draft_Invoice_Num
1886 and mc.Line_Num = ar.Line_Num;
1887 */
1888 p_mcnoofrecordsins := SQL%ROWCOUNT ;
1889
1890 pa_debug.debug( ' ->After insert into PA_MC_DRAFT_INV_ITMS_AR') ;
1891 x_err_stack := l_old_err_stack ;
1892
1893 EXCEPTION
1894 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1895 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1896
1897 WHEN OTHERS THEN
1898 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_DRAFTINVOICEITEMS' );
1899 pa_debug.debug('Error stage is '||x_err_stage );
1900 pa_debug.debug('Error stack is '||x_err_stack );
1901 pa_debug.debug(SQLERRM);
1902 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1903
1904 /* ATG NOCOPY changes */
1905 p_mcnoofrecordsins := null;
1906 x_err_stack := l_old_err_stack ;
1907
1908
1909 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1910
1911 end PA_MC_DRAFTINVOICEITEMS ;
1912
1913 -- Start of comments
1914 -- API name : PA_DraftInvItems
1915 -- Type : Private
1916 -- Pre-reqs : None
1917 -- Function : Archive and Purge data for table PA_DRAFT_INVOICE_ITEMS
1918 -- Parameters : See common list above
1919 -- End of comments
1920 procedure PA_DraftInvItems
1921 ( p_purge_batch_id IN NUMBER,
1922 p_project_id IN NUMBER,
1923 p_txn_to_date IN DATE,
1924 p_purge_release IN VARCHAR2,
1925 p_archive_flag IN VARCHAR2,
1926 p_commit_size IN NUMBER,
1927 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1928 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1929 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1930 ) is
1931
1932 l_old_err_stage VARCHAR2(2000);
1933 l_old_err_stack VARCHAR2(2000);
1934 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
1935 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
1936 l_MC_NoOfRecordsIns NUMBER := NULL;
1937 l_MC_NoOfRecordsDel NUMBER := NULL;
1938 begin
1939
1940 l_old_err_stack := x_err_stack;
1941
1942 x_err_stack := x_err_stack || ' ->Entering PA_DRAFT_INVOICE_ITEMS' ;
1943
1944 pa_debug.debug(x_err_stack);
1945
1946 LOOP
1947 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
1948 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
1949 if p_archive_flag = 'Y' then
1950 -- If archive option is selected then the records are
1951 -- inserted into the archived into the archive tables
1952 -- before being purged. The where condition is such that
1953 -- only the it inserts half the no. of records specified
1954 -- in the commit size.
1955
1956 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
1957
1958 pa_debug.debug( ' ->Before insert into PA_DRAFT_INV_ITEMS_AR') ;
1959 insert into PA_DRAFT_INV_ITEMS_AR (
1960 PURGE_BATCH_ID,
1961 PURGE_RELEASE,
1962 PURGE_PROJECT_ID,
1963 PROJECT_ID,
1964 DRAFT_INVOICE_NUM,
1965 LINE_NUM,
1966 LAST_UPDATE_DATE,
1967 LAST_UPDATED_BY,
1968 CREATION_DATE,
1969 CREATED_BY,
1970 AMOUNT,
1971 TEXT,
1972 INVOICE_LINE_TYPE,
1973 REQUEST_ID,
1974 PROGRAM_APPLICATION_ID,
1975 PROGRAM_ID,
1976 PROGRAM_UPDATE_DATE,
1977 UNEARNED_REVENUE_CR,
1978 UNBILLED_RECEIVABLE_DR,
1979 TASK_ID,
1980 EVENT_TASK_ID,
1981 EVENT_NUM,
1982 SHIP_TO_ADDRESS_ID,
1983 TAXABLE_FLAG,
1984 DRAFT_INV_LINE_NUM_CREDITED,
1985 LAST_UPDATE_LOGIN,
1986 INV_AMOUNT,
1987 OUTPUT_VAT_TAX_ID,
1988 OUTPUT_TAX_CLASSIFICATION_CODE,
1989 OUTPUT_TAX_EXEMPT_FLAG,
1990 OUTPUT_TAX_EXEMPT_REASON_CODE,
1991 OUTPUT_TAX_EXEMPT_NUMBER,
1992 ACCT_AMOUNT,
1993 ROUNDING_AMOUNT,
1994 UNBILLED_ROUNDING_AMOUNT_DR,
1995 UNEARNED_ROUNDING_AMOUNT_CR,
1996 TRANSLATED_TEXT,
1997 CC_REV_CODE_COMBINATION_ID,
1998 CC_PROJECT_ID,
1999 CC_TAX_TASK_ID,
2000 PROJFUNC_CURRENCY_CODE,
2001 PROJFUNC_BILL_AMOUNT,
2002 PROJECT_CURRENCY_CODE,
2003 PROJECT_BILL_AMOUNT,
2004 FUNDING_CURRENCY_CODE,
2005 FUNDING_BILL_AMOUNT,
2006 FUNDING_RATE_DATE,
2007 FUNDING_EXCHANGE_RATE,
2008 FUNDING_RATE_TYPE,
2009 INVPROC_CURRENCY_CODE,
2010 BILL_TRANS_CURRENCY_CODE,
2011 BILL_TRANS_BILL_AMOUNT,
2012 RETN_BILLING_METHOD,
2013 RETN_PERCENT_COMPLETE,
2014 RETN_TOTAL_RETENTION,
2015 RETN_BILLING_CYCLE_ID,
2016 RETN_CLIENT_EXTENSION_FLAG,
2017 RETN_BILLING_PERCENTAGE,
2018 RETN_BILLING_AMOUNT,
2019 RETENTION_RULE_ID,
2020 RETAINED_AMOUNT,
2021 RETN_DRAFT_INVOICE_NUM,
2022 RETN_DRAFT_INVOICE_LINE_NUM
2023 )
2024 select
2025 p_purge_batch_id,
2026 p_purge_release,
2027 p_project_id,
2028 PROJECT_ID,
2029 DRAFT_INVOICE_NUM,
2030 LINE_NUM,
2031 LAST_UPDATE_DATE,
2032 LAST_UPDATED_BY,
2033 CREATION_DATE,
2034 CREATED_BY,
2035 AMOUNT,
2036 TEXT,
2037 INVOICE_LINE_TYPE,
2038 REQUEST_ID,
2039 PROGRAM_APPLICATION_ID,
2040 PROGRAM_ID,
2041 PROGRAM_UPDATE_DATE,
2042 UNEARNED_REVENUE_CR,
2043 UNBILLED_RECEIVABLE_DR,
2044 TASK_ID,
2045 EVENT_TASK_ID,
2046 EVENT_NUM,
2047 SHIP_TO_ADDRESS_ID,
2048 TAXABLE_FLAG,
2049 DRAFT_INV_LINE_NUM_CREDITED,
2050 LAST_UPDATE_LOGIN,
2051 INV_AMOUNT,
2052 OUTPUT_VAT_TAX_ID,
2053 OUTPUT_TAX_CLASSIFICATION_CODE,
2054 OUTPUT_TAX_EXEMPT_FLAG,
2055 OUTPUT_TAX_EXEMPT_REASON_CODE,
2056 OUTPUT_TAX_EXEMPT_NUMBER,
2057 ACCT_AMOUNT,
2058 ROUNDING_AMOUNT,
2059 UNBILLED_ROUNDING_AMOUNT_DR,
2060 UNEARNED_ROUNDING_AMOUNT_CR,
2061 TRANSLATED_TEXT,
2062 CC_REV_CODE_COMBINATION_ID,
2063 CC_PROJECT_ID,
2064 CC_TAX_TASK_ID,
2065 PROJFUNC_CURRENCY_CODE,
2066 PROJFUNC_BILL_AMOUNT,
2067 PROJECT_CURRENCY_CODE,
2068 PROJECT_BILL_AMOUNT,
2069 FUNDING_CURRENCY_CODE,
2070 FUNDING_BILL_AMOUNT,
2071 FUNDING_RATE_DATE,
2072 FUNDING_EXCHANGE_RATE,
2073 FUNDING_RATE_TYPE,
2074 INVPROC_CURRENCY_CODE,
2075 BILL_TRANS_CURRENCY_CODE,
2076 BILL_TRANS_BILL_AMOUNT,
2077 RETN_BILLING_METHOD,
2078 RETN_PERCENT_COMPLETE,
2079 RETN_TOTAL_RETENTION,
2080 RETN_BILLING_CYCLE_ID,
2081 RETN_CLIENT_EXTENSION_FLAG,
2082 RETN_BILLING_PERCENTAGE,
2083 RETN_BILLING_AMOUNT,
2084 RETENTION_RULE_ID,
2085 RETAINED_AMOUNT,
2086 RETN_DRAFT_INVOICE_NUM,
2087 RETN_DRAFT_INVOICE_LINE_NUM
2088 from pa_draft_invoice_items
2089 where (
2090 project_id = p_project_id
2091 and rownum <= l_commit_size
2092 ) ;
2093 /*Code Changes for Bug No.2984871 start */
2094 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2095 /*Code Changes for Bug No.2984871 end */
2096
2097
2098 pa_debug.debug( ' ->After insert into PA_DRAFT_INV_ITEMS_AR') ;
2099
2100 /*Code Changes for Bug No.2984871 start */
2101 if l_NoOfRecordsIns > 0 then
2102 /*Code Changes for Bug No.2984871 end */
2103 -- First call the MRC procedure to archive the MC table
2104 -- We have a seperate delete statement if the archive option is
2105 -- selected because if archive option is selected the the records
2106 -- being purged will be those records which are already archived.
2107 -- table and
2108
2109 PA_MC_DraftInvoiceItems
2110 ( p_purge_batch_id => p_purge_batch_id,
2111 p_project_id => p_project_id,
2112 p_txn_to_date => p_txn_to_date,
2113 p_purge_release => p_purge_release,
2114 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
2115 x_err_code => x_err_code,
2116 x_err_stack => x_err_stack,
2117 x_err_stage => x_err_stage
2118 ) ;
2119
2120 pa_debug.debug( ' ->Before delete from pa_draft_invoice_items ') ;
2121 /* commented and modified as below for performance reasons. Archive Purge 11.5
2122 delete from pa_draft_invoice_items dii
2123 where (dii.rowid)
2124 in
2125 ( select dii1.rowid
2126 from pa_draft_invoice_items dii1,
2127 PA_DRAFT_INV_ITEMS_AR dii2
2128 where dii2.draft_invoice_num = dii1.draft_invoice_num
2129 and dii2.line_num = dii1.line_num
2130 and dii2.project_id = dii1.project_id
2131 and dii2.purge_project_id = p_project_id
2132 ) ;
2133 */
2134 /* Commented the delete statement and added the modified code below not to correlate queries */
2135 /* delete from pa_draft_invoice_items dii
2136 where (dii.project_id, dii.draft_invoice_num) in
2137 ( select dii2.project_id, dii2.draft_invoice_num
2138 from PA_DRAFT_INV_ITEMS_AR dii2
2139 where dii2.line_num = dii.line_num
2140 and dii2.purge_project_id = p_project_id
2141 ) ;
2142 */
2143 delete from pa_draft_invoice_items dii
2144 where (dii.project_id, dii.draft_invoice_num, dii.line_num) in
2145 ( select dii2.project_id, dii2.draft_invoice_num, dii2.line_num
2146 from PA_DRAFT_INV_ITEMS_AR dii2
2147 where dii2.purge_project_id = p_project_id
2148 ) ;
2149
2150 /*Code Changes for Bug No.2984871 start */
2151 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2152 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
2153 /*Code Changes for Bug No.2984871 end */
2154
2155 pa_debug.debug( ' ->After delete from pa_draft_invoice_items ') ;
2156
2157 end if ;
2158 else
2159
2160 l_commit_size := pa_utils2.arpur_mrc_commit_size;
2161
2162 -- If the archive option is not selected then the delete will
2163 -- be based on the commit size.
2164
2165 pa_debug.debug( ' ->Before delete from pa_draft_invoice_items ') ;
2166 /* commented and modified as below for performance reasons. Archive Purge 11.5
2167 delete from pa_draft_invoice_items dii
2168 where (dii.rowid)
2169 in
2170 ( select dii1.rowid
2171 from pa_draft_invoice_items dii1
2172 where dii1.project_id = p_project_id
2173 and rownum <= l_commit_size
2174 ) ;
2175 */
2176
2177 delete from pa_draft_invoice_items dii
2178 where dii.project_id = p_project_id
2179 and rownum <= l_commit_size;
2180 /*Code Changes for Bug No.2984871 start */
2181 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2182 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
2183 /*Code Changes for Bug No.2984871 end */
2184
2185 pa_debug.debug( ' ->After delete from pa_draft_invoice_items ') ;
2186
2187 end if ;
2188
2189 /*Code Changes for Bug No.2984871 start */
2190 if l_NoOfRecordsDel= 0 then
2191 /*Code Changes for Bug No.2984871 end */
2192 -- Once the SqlCount becomes 0, which means that there are
2193 -- no more records to be purged then we exit the loop.
2194
2195 exit ;
2196
2197 else
2198 -- After "deleting" or "deleting and inserting" a set of records
2199 -- the transaction is commited. This also creates a record in the
2200 -- Pa_Purge_Project_details which will show the no. of records
2201 -- that are purged from each table.
2202
2203 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
2204 pa_purge.CommitProcess
2205 (p_purge_batch_id => p_purge_batch_id,
2206 p_project_id => p_project_id,
2207 p_table_name => 'PA_DRAFT_INVOICE_ITEMS',
2208 p_NoOfRecordsIns => l_NoOfRecordsIns,
2209 p_NoOfRecordsDel => l_NoOfRecordsDel,
2210 x_err_code => x_err_code,
2211 x_err_stack => x_err_stack,
2212 x_err_stage => x_err_stage,
2213 p_MRC_table_name => 'PA_MC_DRAFT_INV_ITEMS',
2214 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
2215 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
2216 ) ;
2217
2218 PA_UTILS2.mrc_row_count := 0;
2219
2220 end if ;
2221 END LOOP ;
2222
2223
2224 x_err_stack := l_old_err_stack ;
2225 EXCEPTION
2226 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2227 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2228
2229 WHEN OTHERS THEN
2230 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_DraftInvItems' );
2231 pa_debug.debug('Error stage is '||x_err_stage );
2232 pa_debug.debug('Error stack is '||x_err_stack );
2233 pa_debug.debug(SQLERRM);
2234 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2235
2236 /* ATG NOCOPY changes */
2237 x_err_stack := l_old_err_stack ;
2238
2239
2240 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2241
2242 end PA_DraftInvItems ;
2243
2244 -- Start of comments
2245 -- API name : PA_MC_RETNINVDETAILS
2246 -- Type : Private
2247 -- Pre-reqs : None
2248 -- Function : Archive and Purge data for table PA_MC_RETN_INV_DETLS_AR
2249 -- Parameters : See common list above
2250 -- End of comments
2251 procedure PA_MC_RetnInvDetails
2252 ( p_purge_batch_id IN NUMBER,
2253 p_project_id IN NUMBER,
2254 p_txn_to_date IN DATE,
2255 p_purge_release IN VARCHAR2,
2256 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2257 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2258 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2259 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2260 ) is
2261
2262 l_old_err_stage VARCHAR2(2000);
2263 l_old_err_stack VARCHAR2(2000);
2264
2265 begin
2266
2267 l_old_err_stack := x_err_stack;
2268
2269 x_err_stack := x_err_stack || ' ->Entering PA_MC_RETNINVDETAILS ';
2270
2271 pa_debug.debug(x_err_stack);
2272
2273 pa_debug.debug( ' ->Before insert into PA_MC_RETN_INV_DETLS_AR') ;
2274
2275 /* Commented out for MRC migration to SLA
2276 insert into PA_MC_RETN_INV_DETLS_AR
2277 (
2278 Purge_Batch_Id,
2279 Purge_Release,
2280 Purge_Project_Id,
2281 RETN_INVOICE_DETAIL_ID,
2282 SET_OF_BOOKS_ID,
2283 PROJECT_ID,
2284 DRAFT_INVOICE_NUM,
2285 LINE_NUM,
2286 TOTAL_RETAINED,
2287 ACCT_CURRENCY_CODE,
2288 ACCT_RATE_TYPE,
2289 ACCT_RATE_DATE,
2290 ACCT_EXCHANGE_RATE,
2291 PROGRAM_APPLICATION_ID,
2292 PROGRAM_ID,
2293 PROGRAM_UPDATE_DATE,
2294 REQUEST_ID,
2295 CREATION_DATE,
2296 CREATED_BY,
2297 LAST_UPDATE_DATE,
2298 LAST_UPDATED_BY
2299 )
2300 select
2301 p_purge_batch_id,
2302 p_purge_release,
2303 p_project_id,
2304 mc.RETN_INVOICE_DETAIL_ID,
2305 mc.SET_OF_BOOKS_ID,
2306 mc.PROJECT_ID,
2307 mc.DRAFT_INVOICE_NUM,
2308 mc.LINE_NUM,
2309 mc.TOTAL_RETAINED,
2310 mc.ACCT_CURRENCY_CODE,
2311 mc.ACCT_RATE_TYPE,
2312 mc.ACCT_RATE_DATE,
2313 mc.ACCT_EXCHANGE_RATE,
2314 mc.PROGRAM_APPLICATION_ID,
2315 mc.PROGRAM_ID,
2316 mc.PROGRAM_UPDATE_DATE,
2317 mc.REQUEST_ID,
2318 mc.CREATION_DATE,
2319 mc.CREATED_BY,
2320 mc.LAST_UPDATE_DATE,
2321 mc.LAST_UPDATED_BY
2322 from Pa_MC_Retn_Inv_Details mc,
2323 PA_RETN_INV_DETAILS_AR ar
2324 where ar.Purge_Project_Id = p_project_id
2325 and mc.Project_Id = ar.purge_Project_Id
2326 and mc.Draft_Invoice_Num = ar.Draft_Invoice_Num
2327 and mc.Line_Num = ar.Line_Num;
2328
2329 */
2330 p_mcnoofrecordsins := SQL%ROWCOUNT ;
2331
2332 pa_debug.debug( ' ->After insert into PA_MC_RETN_INV_DETLS_AR') ;
2333 x_err_stack := l_old_err_stack ;
2334
2335 EXCEPTION
2336 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2337 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2338
2339 WHEN OTHERS THEN
2340 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_RETNINVDETAILS' );
2341 pa_debug.debug('Error stage is '||x_err_stage );
2342 pa_debug.debug('Error stack is '||x_err_stack );
2343 pa_debug.debug(SQLERRM);
2344 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2345
2346 /* ATG NOCOPY changes */
2347 p_mcnoofrecordsins := null;
2348 x_err_stack := l_old_err_stack ;
2349
2350 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2351
2352 end PA_MC_RETNINVDETAILS;
2353
2354 -- Start of comments
2355 -- API name : PA_RetnInvDetails
2356 -- Type : Private
2357 -- Pre-reqs : None
2358 -- Function : Archive and Purge data for table PA_RETN_INVOICE_DETAILS
2359 -- Parameters : See common list above
2360 -- End of comments
2361 procedure PA_RetnInvDetails
2362 ( p_purge_batch_id IN NUMBER,
2363 p_project_id IN NUMBER,
2364 p_txn_to_date IN DATE,
2365 p_purge_release IN VARCHAR2,
2366 p_archive_flag IN VARCHAR2,
2367 p_commit_size IN NUMBER,
2368 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2369 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2370 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2371 ) is
2372
2373 l_old_err_stage VARCHAR2(2000);
2374 l_old_err_stack VARCHAR2(2000);
2375 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
2376 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
2377 l_MC_NoOfRecordsIns NUMBER := NULL;
2378 l_MC_NoOfRecordsDel NUMBER := NULL;
2379 begin
2380
2381 l_old_err_stack := x_err_stack;
2382
2383 x_err_stack := x_err_stack || ' ->Entering PA_RETN_INVOICE_DETAILS' ;
2384
2385 pa_debug.debug(x_err_stack);
2386
2387 LOOP
2388 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
2389 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
2390 if p_archive_flag = 'Y' then
2391 -- If archive option is selected then the records are
2392 -- inserted into the archived into the archive tables
2393 -- before being purged. The where condition is such that
2394 -- only the it inserts half the no. of records specified
2395 -- in the commit size.
2396
2397 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
2398
2399 pa_debug.debug( ' ->Before insert into PA_RETN_INV_DETAILS_AR') ;
2400 insert into PA_RETN_INV_DETAILS_AR (
2401 PURGE_BATCH_ID,
2402 PURGE_RELEASE,
2403 PURGE_PROJECT_ID,
2404 RETN_INVOICE_DETAIL_ID,
2405 PROJECT_ID,
2406 DRAFT_INVOICE_NUM,
2407 LINE_NUM,
2408 PROJECT_RETENTION_ID,
2409 INVPROC_CURRENCY_CODE,
2410 TOTAL_RETAINED,
2411 PROJFUNC_CURRENCY_CODE,
2412 PROJFUNC_TOTAL_RETAINED,
2413 PROJECT_CURRENCY_CODE,
2414 PROJECT_TOTAL_RETAINED,
2415 FUNDING_CURRENCY_CODE,
2416 FUNDING_TOTAL_RETAINED,
2417 PROGRAM_APPLICATION_ID,
2418 PROGRAM_ID,
2419 PROGRAM_UPDATE_DATE,
2420 REQUEST_ID,
2421 CREATION_DATE,
2422 CREATED_BY,
2423 LAST_UPDATE_DATE,
2424 LAST_UPDATED_BY
2425 )
2426 select
2427 P_PURGE_BATCH_ID,
2428 P_PURGE_RELEASE,
2429 P_PROJECT_ID,
2430 RETN_INVOICE_DETAIL_ID,
2431 PROJECT_ID,
2432 DRAFT_INVOICE_NUM,
2433 LINE_NUM,
2434 PROJECT_RETENTION_ID,
2435 INVPROC_CURRENCY_CODE,
2436 TOTAL_RETAINED,
2437 PROJFUNC_CURRENCY_CODE,
2438 PROJFUNC_TOTAL_RETAINED,
2439 PROJECT_CURRENCY_CODE,
2440 PROJECT_TOTAL_RETAINED,
2441 FUNDING_CURRENCY_CODE,
2442 FUNDING_TOTAL_RETAINED,
2443 PROGRAM_APPLICATION_ID,
2444 PROGRAM_ID,
2445 PROGRAM_UPDATE_DATE,
2446 REQUEST_ID,
2447 CREATION_DATE,
2448 CREATED_BY,
2449 LAST_UPDATE_DATE,
2450 LAST_UPDATED_BY
2451 from PA_Retn_Invoice_Details
2452 where (
2453 project_id = p_project_id
2454 and rownum <= l_commit_size
2455 ) ;
2456
2457 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2458
2459 pa_debug.debug( ' ->After insert into PA_RETN_INV_DETAILS_AR') ;
2460
2461 /*Code Changes for Bug No.2984871 start */
2462 if l_NoOfRecordsIns > 0 then
2463 /*Code Changes for Bug No.2984871 end */
2464
2465 -- First call the MRC procedure to archive the MC table
2466 -- We have a seperate delete statement if the archive option is
2467 -- selected because if archive option is selected the the records
2468 -- being purged will be those records which are already archived.
2469 -- table and
2470
2471 PA_MC_RETNINVDETAILS
2472 ( p_purge_batch_id => p_purge_batch_id,
2473 p_project_id => p_project_id,
2474 p_txn_to_date => p_txn_to_date,
2475 p_purge_release => p_purge_release,
2476 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
2477 x_err_code => x_err_code,
2478 x_err_stack => x_err_stack,
2479 x_err_stage => x_err_stage
2480 ) ;
2481
2482 /* Added the code to delete the records from the MC table of Pa_Retn_Invoice_Details for bug#2272487, starts here*/
2483 pa_debug.debug( ' ->Before delete from Pa_MC_Retn_Inv_Details ') ;
2484
2485 /* Commented out for MRC migration to SLA delete from Pa_MC_Retn_Inv_Details rid
2486 where (rid.project_id, rid.draft_invoice_num) in
2487 ( select rid2.project_id, rid2.draft_invoice_num
2488 from PA_MC_RETN_INV_DETLS_AR rid2
2489 where rid2.line_num = rid.line_num
2490 and rid2.purge_project_id = p_project_id
2491 ) ; */
2492 /*Code Changes for Bug No.2984871 start */
2493 l_MC_NoOfRecordsDel := SQL%ROWCOUNT;
2494 /*Code Changes for Bug No.2984871 end */
2495
2496 pa_debug.debug( ' ->After delete from Pa_MC_Retn_Inv_Details ') ;
2497
2498 /* Added the code to delete the records from the MC table of Pa_Retn_Invoice_Details for bug#2272487, ends here*/
2499
2500 pa_debug.debug( ' ->Before delete from PA_Retn_Invoice_Details ') ;
2501 /* commented and modified as below for performance reasons. Archive Purge 11.5
2502 delete from PA_Retn_Invoice_Details rid
2503 where (rid.rowid)
2504 in
2505 ( select rid1.rowid
2506 from PA_Retn_Invoice_Details rid1,
2507 PA_RETN_INV_DETAILS_AR rid2
2508 where rid2.draft_invoice_num = rid1.draft_invoice_num
2509 and rid2.line_num = rid1.line_num
2510 and rid2.project_id = rid1.project_id
2511 and rid2.purge_project_id = p_project_id
2512 ) ;
2513 */
2514 delete from PA_Retn_Invoice_Details rid
2515 where (rid.project_id, rid.draft_invoice_num) in
2516 ( select rid2.project_id, rid2.draft_invoice_num
2517 from PA_RETN_INV_DETAILS_AR rid2
2518 where rid2.line_num = rid.line_num
2519 and rid2.purge_project_id = p_project_id
2520 ) ;
2521 /*Code Changes for Bug No.2984871 start */
2522 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2523 /*Code Changes for Bug No.2984871 end */
2524
2525 pa_debug.debug( ' ->After delete from PA_Retn_Invoice_Details ') ;
2526
2527 end if ;
2528 else
2529
2530 l_commit_size := pa_utils2.arpur_mrc_commit_size;
2531
2532 -- If the archive option is not selected then the delete will
2533 -- be based on the commit size.
2534
2535
2536 /* Added the code to delete the records from the MC table of Pa_Retn_Invoice_Details for bug#2272487, starts here */
2537 pa_debug.debug( ' ->Before delete from Pa_MC_Retn_Inv_Details ') ;
2538
2539 /* Commented out for MRC migration to SLA delete from Pa_MC_Retn_Inv_Details rid
2540 where rid.project_id = p_project_id
2541 and rownum <= l_commit_size; */
2542 -- Code Changes for Bug No.2984871 start
2543 l_MC_NoOfRecordsDel := SQL%ROWCOUNT;
2544 -- Code Changes for Bug No.2984871 end
2545
2546 pa_debug.debug( ' ->After delete from Pa_MC_Retn_Inv_Details ') ;
2547
2548 /* Added the code to delete the records from the MC table of Pa_Retn_Invoice_Details for bug#2272487, ends here */
2549
2550 pa_debug.debug( ' ->Before delete from PA_Retn_Invoice_Details ') ;
2551 /* commented and modified as below for performance reasons. Archive Purge 11.5
2552 delete from PA_Retn_Invoice_Details rid
2553 where (rid.rowid)
2554 in
2555 ( select rid1.rowid
2556 from PA_Retn_Invoice_Details rid1
2557 where rid1.project_id = p_project_id
2558 and rownum <= l_commit_size
2559 ) ;
2560 */
2561 delete from PA_Retn_Invoice_Details rid
2562 where rid.project_id = p_project_id
2563 and rownum <= l_commit_size;
2564 /*Code Changes for Bug No.2984871 start */
2565 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2566 /*Code Changes for Bug No.2984871 end */
2567
2568 pa_debug.debug( ' ->After delete from PA_Retn_Invoice_Details ') ;
2569
2570 end if ;
2571
2572 if l_NoOfRecordsDel = 0 then
2573 -- Once the SqlCount becomes 0, which means that there are
2574 -- no more records to be purged then we exit the loop.
2575
2576 exit ;
2577
2578 else
2579 -- After "deleting" or "deleting and inserting" a set of records
2580 -- the transaction is commited. This also creates a record in the
2581 -- Pa_Purge_Project_details which will show the no. of records
2582 -- that are purged from each table.
2583
2584 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
2585 pa_purge.CommitProcess
2586 (p_purge_batch_id => p_purge_batch_id,
2587 p_project_id => p_project_id,
2588 p_table_name => 'PA_RETN_INVOICE_DETAILS',
2589 p_NoOfRecordsIns => l_NoOfRecordsIns,
2590 p_NoOfRecordsDel => l_NoOfRecordsDel,
2591 x_err_code => x_err_code,
2592 x_err_stack => x_err_stack,
2593 x_err_stage => x_err_stage,
2594 p_MRC_table_name => 'Pa_MC_Retn_Inv_Details',
2595 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
2596 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
2597 ) ;
2598
2599 PA_UTILS2.mrc_row_count := 0;
2600
2601 end if ;
2602 END LOOP ;
2603
2604
2605 x_err_stack := l_old_err_stack ;
2606 EXCEPTION
2607 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2608 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2609
2610 WHEN OTHERS THEN
2611 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_RetnInvDetails' );
2612 pa_debug.debug('Error stage is '||x_err_stage );
2613 pa_debug.debug('Error stack is '||x_err_stack );
2614 pa_debug.debug(SQLERRM);
2615 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2616
2617 /* ATG NOCOPY changes */
2618 x_err_stack := l_old_err_stack ;
2619
2620
2621 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2622
2623 end PA_RetnInvDetails;
2624
2625 -- Start of comments
2626 -- API name : PA_MC_DRAFTREVENUES
2627 -- Type : Private
2628 -- Pre-reqs : None
2629 -- Function : Archive and Purge data for table Pa_MC_Draft_Revs_AR
2630 -- Parameters : See common list above
2631 -- End of comments
2632 procedure PA_MC_DraftRevenues
2633 ( p_purge_batch_id IN NUMBER,
2634 p_project_id IN NUMBER,
2635 p_txn_to_date IN DATE,
2636 p_purge_release IN VARCHAR2,
2637 p_mcnoofrecordsins OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2638 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2639 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2640 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2641 ) is
2642
2643 l_old_err_stage VARCHAR2(2000);
2644 l_old_err_stack VARCHAR2(2000);
2645
2646 begin
2647
2648
2649 l_old_err_stack := x_err_stack;
2650
2651 x_err_stack := x_err_stack || ' ->Entering PA_MC_DRAFTREVENUES ';
2652
2653 pa_debug.debug(x_err_stack);
2654
2655 pa_debug.debug( ' ->Before insert into Pa_MC_Draft_Revs_AR') ;
2656
2657 /* Commented out for MRC migration to SLA insert into Pa_MC_Draft_Revs_AR
2658 (
2659 Purge_Batch_Id,
2660 Purge_Release,
2661 Purge_Project_Id,
2662 Set_Of_Books_Id,
2663 Project_Id,
2664 Draft_Revenue_Num,
2665 Transfer_Status_Code,
2666 Request_Id,
2667 Program_Application_Id,
2668 Program_Id,
2669 Program_Update_Date,
2670 Transferred_Date,
2671 Transfer_Rejection_Reason,
2672 Unbilled_Receivable_Dr,
2673 Unearned_Revenue_Cr,
2674 Unbilled_Batch_Name,
2675 Unearned_Batch_Name,
2676 Last_Update_Date,
2677 Last_Updated_By,
2678 Last_Update_Login,
2679 REALIZED_GAINS_AMOUNT,
2680 REALIZED_LOSSES_AMOUNT,
2681 REALIZED_GAINS_BATCH_NAME,
2682 REALIZED_LOSSES_BATCH_NAME
2683 )
2684 select
2685 p_purge_batch_id,
2686 p_purge_release,
2687 p_project_id,
2688 mc.Set_Of_Books_Id,
2689 mc.Project_Id,
2690 mc.Draft_Revenue_Num,
2691 mc.Transfer_Status_Code,
2692 mc.Request_Id,
2693 mc.Program_Application_Id,
2694 mc.Program_Id,
2695 mc.Program_Update_Date,
2696 mc.Transferred_Date,
2697 mc.Transfer_Rejection_Reason,
2698 mc.Unbilled_Receivable_Dr,
2699 mc.Unearned_Revenue_Cr,
2700 mc.Unbilled_Batch_Name,
2701 mc.Unearned_Batch_Name,
2702 mc.Last_Update_Date,
2703 mc.Last_Updated_By,
2704 mc.Last_Update_Login,
2705 mc.REALIZED_GAINS_AMOUNT,
2706 mc.REALIZED_LOSSES_AMOUNT,
2707 mc.REALIZED_GAINS_BATCH_NAME,
2708 mc.REALIZED_LOSSES_BATCH_NAME
2709 from Pa_MC_Draft_Revs_All mc,
2710 PA_Draft_Revenues_AR ar
2711 where ar.Purge_Project_Id = p_project_id
2712 and mc.Project_Id = ar.Purge_Project_Id
2713 and mc.Draft_Revenue_Num = ar.Draft_Revenue_Num;
2714
2715 */
2716 p_mcnoofrecordsins := SQL%ROWCOUNT ;
2717
2718 pa_debug.debug( ' ->After insert into Pa_MC_Draft_Revs_AR') ;
2719 x_err_stack := l_old_err_stack ;
2720
2721 EXCEPTION
2722 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2723 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2724
2725 WHEN OTHERS THEN
2726 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_MC_DRAFTREVENUES' );
2727 pa_debug.debug('Error stage is '||x_err_stage );
2728 pa_debug.debug('Error stack is '||x_err_stack );
2729 pa_debug.debug(SQLERRM);
2730 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2731
2732 /* ATG NOCOPY CHANGES */
2733 p_mcnoofrecordsins := NULL;
2734 x_err_stack := l_old_err_stack ;
2735
2736 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2737
2738 end PA_MC_DRAFTREVENUES;
2739
2740 -- Start of comments
2741 -- API name : PA_DraftRevenues
2742 -- Type : Private
2743 -- Pre-reqs : None
2744 -- Function : Archive and Purge data for table PA_DRAFT_REVENUES_ALL
2745 -- Parameters : See common list above
2746 -- End of comments
2747 procedure PA_DraftRevenues
2748 ( p_purge_batch_id IN NUMBER,
2749 p_project_id IN NUMBER,
2750 p_txn_to_date IN DATE,
2751 p_purge_release IN VARCHAR2,
2752 p_archive_flag IN VARCHAR2,
2753 p_commit_size IN NUMBER,
2754 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2755 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2756 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2757 ) is
2758
2759 l_old_err_stage VARCHAR2(2000);
2760 l_old_err_stack VARCHAR2(2000);
2761 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
2762 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
2763 l_MC_NoOfRecordsIns NUMBER := NULL;
2764 l_MC_NoOfRecordsDel NUMBER := NULL;
2765 begin
2766
2767 l_old_err_stack := x_err_stack;
2768
2769 x_err_stack := x_err_stack || ' ->Entering PA_DRAFTREVENUES' ;
2770
2771 pa_debug.debug(x_err_stack);
2772 LOOP
2773 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
2774 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
2775 if p_archive_flag = 'Y' then
2776 -- If archive option is selected then the records are
2777 -- inserted into the archived into the archive tables
2778 -- before being purged. The where condition is such that
2779 -- only the it inserts half the no. of records specified
2780 -- in the commit size.
2781
2782 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
2783
2784 pa_debug.debug( ' ->Before insert into PA_Draft_Revenues_AR') ;
2785 insert into PA_Draft_Revenues_AR (
2786 PURGE_BATCH_ID,
2787 PURGE_RELEASE,
2788 PURGE_PROJECT_ID,
2789 LAST_UPDATE_LOGIN,
2790 RESOURCE_ACCUMULATED_FLAG,
2791 ORG_ID,
2792 PROJECT_ID,
2793 DRAFT_REVENUE_NUM,
2794 LAST_UPDATE_DATE,
2795 LAST_UPDATED_BY,
2796 CREATION_DATE,
2797 CREATED_BY,
2798 AGREEMENT_ID,
2799 TRANSFER_STATUS_CODE,
2800 GENERATION_ERROR_FLAG,
2801 PA_DATE,
2802 REQUEST_ID,
2803 PROGRAM_APPLICATION_ID,
2804 PROGRAM_ID,
2805 PROGRAM_UPDATE_DATE,
2806 CUSTOMER_BILL_SPLIT,
2807 ACCRUE_THROUGH_DATE,
2808 RELEASED_DATE,
2809 TRANSFERRED_DATE,
2810 TRANSFER_REJECTION_REASON,
2811 UNBILLED_RECEIVABLE_DR,
2812 UNEARNED_REVENUE_CR,
2813 UNBILLED_CODE_COMBINATION_ID,
2814 UNEARNED_CODE_COMBINATION_ID,
2815 UNBILLED_BATCH_NAME,
2816 UNEARNED_BATCH_NAME,
2817 GL_DATE,
2818 ACCUMULATED_FLAG,
2819 DRAFT_REVENUE_NUM_CREDITED,
2820 GL_PERIOD_NAME,
2821 PA_PERIOD_NAME,
2822 ADJUSTING_REVENUE_FLAG,
2823 UBR_SUMMARY_ID,
2824 UER_SUMMARY_ID,
2825 UBR_UER_PROCESS_FLAG,
2826 PJI_SUMMARIZED_FLAG,
2827 REALIZED_GAINS_AMOUNT,
2828 REALIZED_LOSSES_AMOUNT,
2829 REALIZED_GAINS_CCID,
2830 REALIZED_LOSSES_CCID,
2831 REALIZED_GAINS_BATCH_NAME,
2832 REALIZED_LOSSES_BATCH_NAME
2833 )
2834 select
2835 p_purge_batch_id,
2836 p_purge_release,
2837 p_project_id,
2838 LAST_UPDATE_LOGIN,
2839 RESOURCE_ACCUMULATED_FLAG,
2840 ORG_ID,
2841 PROJECT_ID,
2842 DRAFT_REVENUE_NUM,
2843 LAST_UPDATE_DATE,
2844 LAST_UPDATED_BY,
2845 CREATION_DATE,
2846 CREATED_BY,
2847 AGREEMENT_ID,
2848 TRANSFER_STATUS_CODE,
2849 GENERATION_ERROR_FLAG,
2850 PA_DATE,
2851 REQUEST_ID,
2852 PROGRAM_APPLICATION_ID,
2853 PROGRAM_ID,
2854 PROGRAM_UPDATE_DATE,
2855 CUSTOMER_BILL_SPLIT,
2856 ACCRUE_THROUGH_DATE,
2857 RELEASED_DATE,
2858 TRANSFERRED_DATE,
2859 TRANSFER_REJECTION_REASON,
2860 UNBILLED_RECEIVABLE_DR,
2861 UNEARNED_REVENUE_CR,
2862 UNBILLED_CODE_COMBINATION_ID,
2863 UNEARNED_CODE_COMBINATION_ID,
2864 UNBILLED_BATCH_NAME,
2865 UNEARNED_BATCH_NAME,
2866 GL_DATE,
2867 ACCUMULATED_FLAG,
2868 DRAFT_REVENUE_NUM_CREDITED,
2869 GL_PERIOD_NAME,
2870 PA_PERIOD_NAME,
2871 ADJUSTING_REVENUE_FLAG,
2872 UBR_SUMMARY_ID,
2873 UER_SUMMARY_ID,
2874 UBR_UER_PROCESS_FLAG,
2875 PJI_SUMMARIZED_FLAG,
2876 REALIZED_GAINS_AMOUNT,
2877 REALIZED_LOSSES_AMOUNT,
2878 REALIZED_GAINS_CCID,
2879 REALIZED_LOSSES_CCID,
2880 REALIZED_GAINS_BATCH_NAME,
2881 REALIZED_LOSSES_BATCH_NAME
2882 from pa_draft_revenues_all
2883 where (
2884 project_id = p_project_id
2885 and rownum <= l_commit_size
2886 ) ;
2887
2888 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2889 pa_debug.debug( ' ->After insert into PA_Draft_Revenues_AR') ;
2890
2891 if l_NoOfRecordsIns > 0 then
2892 -- First call the MRC procedure to archive the MC table
2893 -- We have a seperate delete statement if the archive option is
2894 -- selected because if archive option is selected the the records
2895 -- being purged will be those records which are already archived.
2896 -- table and
2897
2898 PA_MC_DraftRevenues
2899 ( p_purge_batch_id => p_purge_batch_id,
2900 p_project_id => p_project_id,
2901 p_txn_to_date => p_txn_to_date,
2902 p_purge_release => p_purge_release,
2903 p_mcnoofrecordsins => l_MC_NoOfRecordsIns,
2904 x_err_code => x_err_code,
2905 x_err_stack => x_err_stack,
2906 x_err_stage => x_err_stage
2907 ) ;
2908
2909 pa_debug.debug( ' ->Before delete from pa_draft_revenues_all ') ;
2910 /* commented and modified as below for performance reasons. Archive Purge 11.5
2911 delete from pa_draft_revenues_all dr
2912 where (dr.rowid)
2913 in
2914 ( select dr1.rowid
2915 from pa_draft_revenues_all dr1,
2916 pa_draft_revenues_ar dr2
2917 where dr2.draft_revenue_num = dr1.draft_revenue_num
2918 and dr1.project_id = dr2.project_id
2919 and dr2.purge_project_id = p_project_id
2920 ) ;
2921 */
2922 delete from pa_draft_revenues_all dr
2923 where (dr.project_id, dr.draft_revenue_num) in
2924 ( select dr2.project_id, dr2.draft_revenue_num
2925 from pa_draft_revenues_ar dr2
2926 where dr2.purge_project_id = p_project_id
2927 ) ;
2928
2929 /*Code Changes for Bug No.2984871 start */
2930 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2931 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
2932 /*Code Changes for Bug No.2984871 end */
2933 pa_debug.debug( ' ->After delete from pa_draft_revenues_all ') ;
2934
2935 end if ;
2936 else
2937
2938 l_commit_size := pa_utils2.arpur_mrc_commit_size;
2939
2940 -- If the archive option is not selected then the delete will
2941 -- be based on the commit size.
2942
2943 pa_debug.debug( ' ->Before delete from pa_draft_revenues_all ') ;
2944 /* commented and modified as below for performance reasons. Archive Purge 11.5
2945 delete from pa_draft_revenues_all dr
2946 where (dr.rowid)
2947 in
2948 ( select dr1.rowid
2949 from pa_draft_revenues_all dr1
2950 where dr1.project_id = p_project_id
2951 and rownum <= l_commit_size
2952 ) ;
2953 */
2954
2955 delete from pa_draft_revenues_all dr
2956 where dr.project_id = p_project_id
2957 and rownum <= l_commit_size;
2958
2959 /*Code Changes for Bug No.2984871 start */
2960 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2961 l_MC_NoOfRecordsDel := PA_UTILS2.mrc_row_count;
2962 /*Code Changes for Bug No.2984871 end */
2963 pa_debug.debug( ' ->After delete from pa_draft_revenues_all ') ;
2964 end if ;
2965
2966 if l_NoOfRecordsDel = 0 then
2967 -- Once the SqlCount becomes 0, which means that there are
2968 -- no more records to be purged then we exit the loop.
2969
2970 exit ;
2971
2972 else
2973 -- After "deleting" or "deleting and inserting" a set of records
2974 -- the transaction is commited. This also creates a record in the
2975 -- Pa_Purge_Project_details which will show the no. of records
2976 -- that are purged from each table.
2977
2978 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
2979 pa_purge.CommitProcess
2980 (p_purge_batch_id => p_purge_batch_id,
2981 p_project_id => p_project_id,
2982 p_table_name => 'PA_DRAFT_REVENUES_ALL',
2983 p_NoOfRecordsIns => l_NoOfRecordsIns,
2984 p_NoOfRecordsDel => l_NoOfRecordsDel,
2985 x_err_code => x_err_code,
2986 x_err_stack => x_err_stack,
2987 x_err_stage => x_err_stage,
2988 p_MRC_table_name => 'PA_MC_DRAFT_REVS_ALL',
2989 p_MRC_NoOfRecordsIns => l_MC_NoOfRecordsIns,
2990 p_MRC_NoOfRecordsDel => l_MC_NoOfRecordsDel
2991 ) ;
2992
2993 PA_UTILS2.mrc_row_count := 0;
2994
2995 end if ;
2996 END LOOP ;
2997
2998
2999 x_err_stack := l_old_err_stack ;
3000 EXCEPTION
3001 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3002 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3003
3004 WHEN OTHERS THEN
3005 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_DraftRevenues' );
3006 pa_debug.debug('Error stage is '||x_err_stage );
3007 pa_debug.debug('Error stack is '||x_err_stack );
3008 pa_debug.debug(SQLERRM);
3009 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3010
3011 /* ATG NOPCOPY changes */
3012
3013 x_err_stack := l_old_err_stack ;
3014
3015 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3016
3017 end PA_DraftRevenues ;
3018
3019 -- Start of comments
3020 -- API name : PA_DraftInvoices
3021 -- Type : Private
3022 -- Pre-reqs : None
3023 -- Function : Archive and Purge data for table PA_DRAFT_INVOICES_ALL
3024 -- Parameters : See common list above
3025 -- End of comments
3026 procedure PA_DraftInvoices
3027 ( p_purge_batch_id IN NUMBER,
3028 p_project_id IN NUMBER,
3029 p_txn_to_date IN DATE,
3030 p_purge_release IN VARCHAR2,
3031 p_archive_flag IN VARCHAR2,
3032 p_commit_size IN NUMBER,
3033 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3034 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3035 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3036 ) is
3037
3038 l_old_err_stage VARCHAR2(2000);
3039 l_old_err_stack VARCHAR2(2000);
3040 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
3041 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
3042 begin
3043
3044 l_old_err_stack := x_err_stack;
3045
3046 x_err_stack := x_err_stack || ' ->Entering PA_DRAFTINVOICES' ;
3047
3048 pa_debug.debug(x_err_stack);
3049
3050 LOOP
3051 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
3052 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
3053 if p_archive_flag = 'Y' then
3054 -- If archive option is selected then the records are
3055 -- inserted into the archived into the archive tables
3056 -- before being purged. The where condition is such that
3057 -- only the it inserts half the no. of records specified
3058 -- in the commit size.
3059
3060 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
3061
3062 pa_debug.debug( ' ->Before insert into PA_Draft_Invoices_AR') ;
3063 insert into PA_Draft_Invoices_AR (
3064 PURGE_BATCH_ID,
3065 PURGE_RELEASE,
3066 PURGE_PROJECT_ID,
3067 SYSTEM_REFERENCE,
3068 DRAFT_INVOICE_NUM_CREDITED,
3069 CANCELED_FLAG,
3070 CANCEL_CREDIT_MEMO_FLAG,
3071 WRITE_OFF_FLAG,
3072 CONVERTED_FLAG,
3073 EXTRACTED_DATE,
3074 LAST_UPDATE_LOGIN,
3075 ATTRIBUTE_CATEGORY,
3076 ATTRIBUTE1,
3077 ATTRIBUTE2,
3078 ATTRIBUTE3,
3079 ATTRIBUTE4,
3080 ATTRIBUTE5,
3081 ATTRIBUTE6,
3082 ATTRIBUTE7,
3083 ATTRIBUTE8,
3084 ATTRIBUTE9,
3085 ATTRIBUTE10,
3086 RETENTION_PERCENTAGE,
3087 INVOICE_SET_ID,
3088 ORG_ID,
3089 PROJECT_ID,
3090 DRAFT_INVOICE_NUM,
3091 LAST_UPDATE_DATE,
3092 LAST_UPDATED_BY,
3093 CREATION_DATE,
3094 CREATED_BY,
3095 TRANSFER_STATUS_CODE,
3096 GENERATION_ERROR_FLAG,
3097 AGREEMENT_ID,
3098 PA_DATE,
3099 REQUEST_ID,
3100 PROGRAM_APPLICATION_ID,
3101 PROGRAM_ID,
3102 PROGRAM_UPDATE_DATE,
3103 CUSTOMER_BILL_SPLIT,
3104 BILL_THROUGH_DATE,
3105 INVOICE_COMMENT,
3106 APPROVED_DATE,
3107 APPROVED_BY_PERSON_ID,
3108 RELEASED_DATE,
3109 RELEASED_BY_PERSON_ID,
3110 INVOICE_DATE,
3111 RA_INVOICE_NUMBER,
3112 TRANSFERRED_DATE,
3113 TRANSFER_REJECTION_REASON,
3114 UNEARNED_REVENUE_CR,
3115 UNBILLED_RECEIVABLE_DR,
3116 GL_DATE,
3117 INV_CURRENCY_CODE,
3118 INV_RATE_TYPE,
3119 INV_RATE_DATE,
3120 INV_EXCHANGE_RATE,
3121 BILL_TO_ADDRESS_ID,
3122 SHIP_TO_ADDRESS_ID,
3123 PRC_GENERATED_FLAG,
3124 RECEIVABLE_CODE_COMBINATION_ID,
3125 ROUNDING_CODE_COMBINATION_ID,
3126 UNBILLED_CODE_COMBINATION_ID,
3127 UNEARNED_CODE_COMBINATION_ID,
3128 WOFF_CODE_COMBINATION_ID,
3129 ACCTD_CURR_CODE,
3130 ACCTD_RATE_TYPE,
3131 ACCTD_RATE_DATE,
3132 ACCTD_EXCHG_RATE,
3133 LANGUAGE,
3134 CC_INVOICE_GROUP_CODE,
3135 CC_PROJECT_ID,
3136 IB_AP_TRANSFER_STATUS_CODE,
3137 IB_AP_TRANSFER_ERROR_CODE,
3138 INVPROC_CURRENCY_CODE,
3139 PROJFUNC_INVTRANS_RATE_TYPE,
3140 PROJFUNC_INVTRANS_RATE_DATE,
3141 PROJFUNC_INVTRANS_EX_RATE,
3142 PA_PERIOD_NAME,
3143 GL_PERIOD_NAME,
3144 UBR_SUMMARY_ID,
3145 UER_SUMMARY_ID,
3146 UBR_UER_PROCESS_FLAG,
3147 PJI_SUMMARIZED_FLAG,
3148 RETENTION_INVOICE_FLAG,
3149 RETN_CODE_COMBINATION_ID
3150 )
3151 select
3152 p_purge_batch_id,
3153 p_purge_release,
3154 p_project_id,
3155 SYSTEM_REFERENCE,
3156 DRAFT_INVOICE_NUM_CREDITED,
3157 CANCELED_FLAG,
3158 CANCEL_CREDIT_MEMO_FLAG,
3159 WRITE_OFF_FLAG,
3160 CONVERTED_FLAG,
3161 EXTRACTED_DATE,
3162 LAST_UPDATE_LOGIN,
3163 ATTRIBUTE_CATEGORY,
3164 ATTRIBUTE1,
3165 ATTRIBUTE2,
3166 ATTRIBUTE3,
3167 ATTRIBUTE4,
3168 ATTRIBUTE5,
3169 ATTRIBUTE6,
3170 ATTRIBUTE7,
3171 ATTRIBUTE8,
3172 ATTRIBUTE9,
3173 ATTRIBUTE10,
3174 RETENTION_PERCENTAGE,
3175 INVOICE_SET_ID,
3176 ORG_ID,
3177 PROJECT_ID,
3178 DRAFT_INVOICE_NUM,
3179 LAST_UPDATE_DATE,
3180 LAST_UPDATED_BY,
3181 CREATION_DATE,
3182 CREATED_BY,
3183 TRANSFER_STATUS_CODE,
3184 GENERATION_ERROR_FLAG,
3185 AGREEMENT_ID,
3186 PA_DATE,
3187 REQUEST_ID,
3188 PROGRAM_APPLICATION_ID,
3189 PROGRAM_ID,
3190 PROGRAM_UPDATE_DATE,
3191 CUSTOMER_BILL_SPLIT,
3192 BILL_THROUGH_DATE,
3193 INVOICE_COMMENT,
3194 APPROVED_DATE,
3195 APPROVED_BY_PERSON_ID,
3196 RELEASED_DATE,
3197 RELEASED_BY_PERSON_ID,
3198 INVOICE_DATE,
3199 RA_INVOICE_NUMBER,
3200 TRANSFERRED_DATE,
3201 TRANSFER_REJECTION_REASON,
3202 UNEARNED_REVENUE_CR,
3203 UNBILLED_RECEIVABLE_DR,
3204 GL_DATE,
3205 INV_CURRENCY_CODE,
3206 INV_RATE_TYPE,
3207 INV_RATE_DATE,
3208 INV_EXCHANGE_RATE,
3209 BILL_TO_ADDRESS_ID,
3210 SHIP_TO_ADDRESS_ID,
3211 PRC_GENERATED_FLAG,
3212 RECEIVABLE_CODE_COMBINATION_ID,
3213 ROUNDING_CODE_COMBINATION_ID,
3214 UNBILLED_CODE_COMBINATION_ID,
3215 UNEARNED_CODE_COMBINATION_ID,
3216 WOFF_CODE_COMBINATION_ID,
3217 ACCTD_CURR_CODE,
3218 ACCTD_RATE_TYPE,
3219 ACCTD_RATE_DATE,
3220 ACCTD_EXCHG_RATE,
3221 LANGUAGE,
3222 CC_INVOICE_GROUP_CODE,
3223 CC_PROJECT_ID,
3224 IB_AP_TRANSFER_STATUS_CODE,
3225 IB_AP_TRANSFER_ERROR_CODE,
3226 INVPROC_CURRENCY_CODE,
3227 PROJFUNC_INVTRANS_RATE_TYPE,
3228 PROJFUNC_INVTRANS_RATE_DATE,
3229 PROJFUNC_INVTRANS_EX_RATE,
3230 PA_PERIOD_NAME,
3231 GL_PERIOD_NAME,
3232 UBR_SUMMARY_ID,
3233 UER_SUMMARY_ID,
3234 UBR_UER_PROCESS_FLAG,
3235 PJI_SUMMARIZED_FLAG,
3236 RETENTION_INVOICE_FLAG,
3237 RETN_CODE_COMBINATION_ID
3238 from pa_draft_invoices_all
3239 where (
3240 project_id = p_project_id
3241 and rownum <= l_commit_size
3242 ) ;
3243
3244 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3245
3246 pa_debug.debug( ' ->After insert into PA_Draft_Invoices_AR') ;
3247 if l_NoOfRecordsIns > 0 then
3248 -- We have a seperate delete statement if the archive option is
3249 -- selected because if archive option is selected the the records
3250 -- being purged will be those records which are already archived.
3251 -- table and
3252
3253 pa_debug.debug( ' ->Before delete from pa_draft_invoices_all ') ;
3254 /* commented and modified as below for performance reasons. Archive Purge 11.5
3255 delete from pa_draft_invoices_all di
3256 where (di.rowid)
3257 in
3258 ( select di1.rowid
3259 from pa_draft_invoices_all di1,
3260 pa_draft_invoices_ar di2
3261 where di2.draft_invoice_num = di1.draft_invoice_num
3262 and di1.project_id = di2.project_id
3263 and di2.purge_project_id = p_project_id
3264 ) ;
3265 */
3266
3267 delete from pa_draft_invoices_all di
3268 where (di.project_id, di.draft_invoice_num) in
3269 ( select di2.project_id, di2.draft_invoice_num
3270 from pa_draft_invoices_ar di2
3271 where di2.purge_project_id = p_project_id
3272 ) ;
3273
3274 pa_debug.debug( ' ->After delete from pa_draft_invoices_all ') ;
3275
3276 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3277 end if ;
3278 else
3279
3280 l_commit_size := pa_utils2.arpur_mrc_commit_size;
3281
3282 -- If the archive option is not selected then the delete will
3283 -- be based on the commit size.
3284
3285 pa_debug.debug( ' ->Before delete from pa_draft_invoices_all ') ;
3286 /* commented and modified as below for performance reasons. Archive Purge 11.5
3287 delete from pa_draft_invoices_all di
3288 where (di.rowid)
3289 in
3290 ( select di1.rowid
3291 from pa_draft_invoices_all di1
3292 where di1.project_id = p_project_id
3293 and rownum <= l_commit_size
3294 ) ;
3295 */
3296
3297 delete from pa_draft_invoices_all di
3298 where di.project_id = p_project_id
3299 and rownum <= l_commit_size;
3300
3301 /*Code Changes for Bug No.2984871 start */
3302 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3303 /*Code Changes for Bug No.2984871 end */
3304 pa_debug.debug( ' ->After delete from pa_draft_invoices_all ') ;
3305 end if ;
3306
3307 if l_NoOfRecordsDel = 0 then
3308 -- Once the SqlCount becomes 0, which means that there are
3309 -- no more records to be purged then we exit the loop.
3310
3311 exit ;
3312
3313 else
3314 -- After "deleting" or "deleting and inserting" a set of records
3315 -- the transaction is commited. This also creates a record in the
3316 -- Pa_Purge_Project_details which will show the no. of records
3317 -- that are purged from each table.
3318
3319 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
3320 pa_purge.CommitProcess
3321 (p_purge_batch_id => p_purge_batch_id,
3322 p_project_id => p_project_id,
3323 p_table_name => 'PA_DRAFT_INVOICES_ALL',
3324 p_NoOfRecordsIns => l_NoOfRecordsIns,
3325 p_NoOfRecordsDel => l_NoOfRecordsDel,
3326 x_err_code => x_err_code,
3327 x_err_stack => x_err_stack,
3328 x_err_stage => x_err_stage
3329 ) ;
3330 end if ;
3331 END LOOP ;
3332
3333
3334 x_err_stack := l_old_err_stack ;
3335 EXCEPTION
3336 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3337 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3338
3339 WHEN OTHERS THEN
3340 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_DraftInvoices' );
3341 pa_debug.debug('Error stage is '||x_err_stage );
3342 pa_debug.debug('Error stack is '||x_err_stack );
3343 pa_debug.debug(SQLERRM);
3344 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3345
3346 /* ATG NOCOPY CHANGES */
3347 x_err_stack := l_old_err_stack;
3348
3349 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3350
3351 end PA_DraftInvoices ;
3352
3353 -- Start of comments
3354 -- API name : PA_DistWarnings
3355 -- Type : Private
3356 -- Pre-reqs : None
3357 -- Function : Archive and Purge data for table PA_Distribution_Warnings
3358 -- Parameters : See common list above
3359 -- End of comments
3360 procedure PA_DistWarnings
3361 ( p_purge_batch_id IN NUMBER,
3362 p_project_id IN NUMBER,
3363 p_txn_to_date IN DATE,
3364 p_purge_release IN VARCHAR2,
3365 p_archive_flag IN VARCHAR2,
3366 p_commit_size IN NUMBER,
3367 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3368 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3369 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3370 ) is
3371
3372 l_old_err_stage VARCHAR2(2000);
3373 l_old_err_stack VARCHAR2(2000);
3374 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
3375 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
3376 begin
3377
3378 l_old_err_stack := x_err_stack;
3379
3380 x_err_stack := x_err_stack || ' ->Entering PA_DISTWARNINGS' ;
3381
3382 pa_debug.debug(x_err_stack);
3383
3384 LOOP
3385 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
3386 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
3387 if p_archive_flag = 'Y' then
3388 -- If archive option is selected then the records are
3389 -- inserted into the archived into the archive tables
3390 -- before being purged. The where condition is such that
3391 -- only the it inserts half the no. of records specified
3392 -- in the commit size.
3393
3394 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
3395
3396 pa_debug.debug( ' ->Before insert into PA_DIST_WARNINGS_AR') ;
3397 insert into PA_DIST_WARNINGS_AR (
3398 PURGE_BATCH_ID,
3399 PURGE_RELEASE,
3400 PURGE_PROJECT_ID,
3401 PROJECT_ID,
3402 DRAFT_REVENUE_NUM,
3403 DRAFT_INVOICE_NUM,
3404 LAST_UPDATE_DATE,
3405 LAST_UPDATED_BY,
3406 CREATION_DATE,
3407 CREATED_BY,
3408 LAST_UPDATE_LOGIN,
3409 REQUEST_ID,
3410 PROGRAM_APPLICATION_ID,
3411 PROGRAM_ID,
3412 PROGRAM_UPDATE_DATE,
3413 WARNING_MESSAGE,
3414 WARNING_MESSAGE_CODE,
3415 agreement_id,
3416 task_id
3417 )
3418 select
3419 p_purge_batch_id,
3420 p_purge_release,
3421 p_project_id,
3422 PROJECT_ID,
3423 DRAFT_REVENUE_NUM,
3424 DRAFT_INVOICE_NUM,
3425 LAST_UPDATE_DATE,
3426 LAST_UPDATED_BY,
3427 CREATION_DATE,
3428 CREATED_BY,
3429 LAST_UPDATE_LOGIN,
3430 REQUEST_ID,
3431 PROGRAM_APPLICATION_ID,
3432 PROGRAM_ID,
3433 PROGRAM_UPDATE_DATE,
3434 WARNING_MESSAGE,
3435 WARNING_MESSAGE_CODE,
3436 AGREEMENT_ID,
3437 TASK_ID
3438 from pa_distribution_warnings dw
3439 where (
3440 dw.project_id = p_project_id
3441 and rownum <= l_commit_size
3442 ) ;
3443
3444 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3445 pa_debug.debug( ' ->After insert into PA_DIST_WARNINGS_AR') ;
3446
3447 /*Code Changes for Bug No.2984871 start */
3448 if l_NoOfRecordsIns > 0 then
3449 /*Code Changes for Bug No.2984871 end */
3450 -- We have a seperate delete statement if the archive option is
3451 -- selected because if archive option is selected the the records
3452 -- being purged will be those records which are already archived.
3453 -- table and
3454
3455 pa_debug.debug( ' ->Before delete from pa_distribution_warnings ') ;
3456 /* commented and modified as below for performance reasons. Archive Purge 11.5
3457 delete from pa_distribution_warnings dw
3458 where (dw.rowid)
3459 in
3460 ( select dw1.rowid
3461 from pa_distribution_warnings dw1,
3462 PA_DIST_WARNINGS_AR dw2
3463 where dw1.project_id = dw2.project_id
3464 and dw2.purge_project_id = p_project_id
3465 and nvl(dw1.draft_revenue_num,-99)
3466 = nvl(dw2.draft_revenue_num,-99)
3467 and nvl(dw1.draft_invoice_num,-99)
3468 = nvl(dw2.draft_invoice_num, -99)
3469 ) ;
3470 */
3471
3472 delete from pa_distribution_warnings dw
3473 where (dw.project_id) in
3474 ( select dw2.project_id
3475 from PA_DIST_WARNINGS_AR dw2
3476 where dw2.purge_project_id = p_project_id
3477 and nvl(dw.draft_revenue_num,-99)
3478 = nvl(dw2.draft_revenue_num,-99)
3479 and nvl(dw.draft_invoice_num,-99)
3480 = nvl(dw2.draft_invoice_num, -99)
3481 )
3482 and dw.project_id = p_project_id; -- Perf Bug 2695202
3483
3484 /*Code Changes for Bug No.2984871 start */
3485 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3486 /*Code Changes for Bug No.2984871 end */
3487 pa_debug.debug( ' ->After delete from pa_distribution_warnings ') ;
3488
3489 end if ;
3490 else
3491
3492 l_commit_size := pa_utils2.arpur_mrc_commit_size;
3493
3494 -- If the archive option is not selected then the delete will
3495 -- be based on the commit size.
3496
3497 pa_debug.debug( ' ->Before delete from pa_distribution_warnings ') ;
3498 /* commented and modified as below for performance reasons. Archive Purge 11.5
3499 delete from pa_distribution_warnings dw
3500 where (dw.rowid)
3501 in
3502 ( select dw1.rowid
3503 from pa_distribution_warnings dw1
3504 where dw1.project_id = p_project_id
3505 and rownum <= l_commit_size
3506 ) ;
3507 */
3508 delete from pa_distribution_warnings dw
3509 where dw.project_id = p_project_id
3510 and rownum <= l_commit_size;
3511 /*Code Changes for Bug No.2984871 start */
3512 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3513 /*Code Changes for Bug No.2984871 end */
3514 pa_debug.debug( ' ->After delete from pa_distribution_warnings ') ;
3515
3516 end if ;
3517
3518 if l_NoOfRecordsDel = 0 then
3519 -- Once the SqlCount becomes 0, which means that there are
3520 -- no more records to be purged then we exit the loop.
3521
3522 exit ;
3523
3524 else
3525 -- After "deleting" or "deleting and inserting" a set of records
3526 -- the transaction is commited. This also creates a record in the
3527 -- Pa_Purge_Project_details which will show the no. of records
3528 -- that are purged from each table.
3529
3530 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
3531 pa_purge.CommitProcess
3532 (p_purge_batch_id => p_purge_batch_id,
3533 p_project_id => p_project_id,
3534 p_table_name => 'PA_DISTRIBUTION_WARNINGS',
3535 p_NoOfRecordsIns => l_NoOfRecordsIns,
3536 p_NoOfRecordsDel => l_NoOfRecordsDel,
3537 x_err_code => x_err_code,
3538 x_err_stack => x_err_stack,
3539 x_err_stage => x_err_stage
3540 ) ;
3541
3542 end if ;
3543 END LOOP ;
3544
3545
3546 x_err_stack := l_old_err_stack ;
3547 EXCEPTION
3548 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3549 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3550
3551 WHEN OTHERS THEN
3552 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_DistWarnings' );
3553 pa_debug.debug('Error stage is '||x_err_stage );
3554 pa_debug.debug('Error stack is '||x_err_stack );
3555 pa_debug.debug(SQLERRM);
3556 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3557
3558 /* ATG NOCOPY changes */
3559 x_err_stack := l_old_err_stack ;
3560
3561
3562 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3563
3564 end PA_DistWarnings ;
3565
3566 -- Start of comments
3567 -- API name : PA_BillingMessages
3568 -- Type : Private
3569 -- Pre-reqs : None
3570 -- Function : Archive and Purge data for table PA_Billing_Messages
3571 -- Parameters : See common list above
3572 -- End of comments
3573 procedure PA_BillingMessages
3574 ( p_purge_batch_id IN NUMBER,
3575 p_project_id IN NUMBER,
3576 p_txn_to_date IN DATE,
3577 p_purge_release IN VARCHAR2,
3578 p_archive_flag IN VARCHAR2,
3579 p_commit_size IN NUMBER,
3580 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3581 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3582 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3583 ) is
3584
3585 l_old_err_stage VARCHAR2(2000);
3586 l_old_err_stack VARCHAR2(2000);
3587 l_NoOfRecordsIns NUMBER:= 0; --Initialized to zero for bug 3583748
3588 l_NoOfRecordsDel NUMBER:= 0; --Initialized to zero for bug 3583748
3589 begin
3590
3591 l_old_err_stack := x_err_stack;
3592
3593 x_err_stack := x_err_stack || ' ->Entering PA_BillingMessages' ;
3594
3595 pa_debug.debug(x_err_stack);
3596
3597 LOOP
3598 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
3599 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsIns is reset for BUg 4104133
3600 if p_archive_flag = 'Y' then
3601 -- If archive option is selected then the records are
3602 -- inserted into the archived into the archive tables
3603 -- before being purged. The where condition is such that
3604 -- only the it inserts half the no. of records specified
3605 -- in the commit size.
3606
3607 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
3608
3609 pa_debug.debug( ' ->Before insert into PA_Billing_Messages_AR') ;
3610 insert into PA_Billing_Messages_AR (
3611 PURGE_BATCH_ID,
3612 PURGE_RELEASE,
3613 PURGE_PROJECT_ID,
3614 INSERTING_PROCEDURE_NAME,
3615 BILLING_ASSIGNMENT_ID,
3616 PROJECT_ID,
3617 TASK_ID,
3618 CALLING_PROCESS,
3619 CALLING_PLACE,
3620 REQUEST_ID,
3621 MESSAGE,
3622 LAST_UPDATE_DATE,
3623 LAST_UPDATED_BY,
3624 CREATION_DATE,
3625 CREATED_BY,
3626 LAST_UPDATE_LOGIN,
3627 ATTRIBUTE1,
3628 ATTRIBUTE2,
3629 ATTRIBUTE3,
3630 ATTRIBUTE4,
3631 ATTRIBUTE5,
3632 ATTRIBUTE6,
3633 ATTRIBUTE7,
3634 ATTRIBUTE8,
3635 ATTRIBUTE9,
3636 ATTRIBUTE10,
3637 ATTRIBUTE11,
3638 ATTRIBUTE12,
3639 ATTRIBUTE13,
3640 ATTRIBUTE14,
3641 ATTRIBUTE15,
3642 LINE_NUM,
3643 PROGRAM_APPLICATION_ID,
3644 PROGRAM_ID,
3645 PROGRAM_UPDATE_DATE
3646 )
3647 select
3648 p_purge_batch_id,
3649 p_purge_release,
3650 p_project_id,
3651 INSERTING_PROCEDURE_NAME,
3652 BILLING_ASSIGNMENT_ID,
3653 PROJECT_ID,
3654 TASK_ID,
3655 CALLING_PROCESS,
3656 CALLING_PLACE,
3657 REQUEST_ID,
3658 MESSAGE,
3659 LAST_UPDATE_DATE,
3660 LAST_UPDATED_BY,
3661 CREATION_DATE,
3662 CREATED_BY,
3663 LAST_UPDATE_LOGIN,
3664 ATTRIBUTE1,
3665 ATTRIBUTE2,
3666 ATTRIBUTE3,
3667 ATTRIBUTE4,
3668 ATTRIBUTE5,
3669 ATTRIBUTE6,
3670 ATTRIBUTE7,
3671 ATTRIBUTE8,
3672 ATTRIBUTE9,
3673 ATTRIBUTE10,
3674 ATTRIBUTE11,
3675 ATTRIBUTE12,
3676 ATTRIBUTE13,
3677 ATTRIBUTE14,
3678 ATTRIBUTE15,
3679 LINE_NUM,
3680 PROGRAM_APPLICATION_ID,
3681 PROGRAM_ID,
3682 PROGRAM_UPDATE_DATE
3683 from pa_billing_messages bm
3684 where (
3685 bm.project_id = p_project_id
3686 and rownum <= l_commit_size
3687 ) ;
3688 /*Code Changes for Bug No.2984871 start */
3689 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3690 /*Code Changes for Bug No.2984871 end */
3691
3692 pa_debug.debug( ' ->After insert into pa_billing_messages_AR') ;
3693
3694 /* Commented for Bug 2984871
3695 if SQL%ROWCOUNT > 0 then */
3696
3697 /*Code Changes for Bug No.2984871 start */
3698 if l_NoOfRecordsIns > 0 then
3699 /*Code Changes for Bug No.2984871 end */
3700 -- We have a seperate delete statement if the archive option is
3701 -- selected because if archive option is selected the the records
3702 -- being purged will be those records which are already archived.
3703 -- table and
3704
3705 pa_debug.debug( ' ->Before delete from pa_billing_messages ') ;
3706 /* commented and modified as below for performance reasons. Archive Purge 11.5
3707 delete from pa_billing_messages bm
3708 where (bm.rowid)
3709 in
3710 ( select bm1.rowid
3711 from pa_billing_messages bm1,
3712 pa_billing_messages_ar bm2
3713 where bm2.purge_project_id = p_project_id
3714 and bm1.project_id = bm2.project_id
3715 ) ;
3716 */
3717 delete from pa_billing_messages bm
3718 where (bm.project_id) in
3719 ( select bm2.project_id
3720 from pa_billing_messages_ar bm2
3721 where bm2.purge_project_id = p_project_id
3722 ) ;
3723 /*Code Changes for Bug No.2984871 start */
3724 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3725 /*Code Changes for Bug No.2984871 end */
3726 pa_debug.debug( ' ->After delete from pa_billing_messages ') ;
3727
3728 end if ;
3729 else
3730
3731 l_commit_size := pa_utils2.arpur_mrc_commit_size;
3732
3733 -- If the archive option is not selected then the delete will
3734 -- be based on the commit size.
3735
3736 pa_debug.debug( ' ->Before delete from pa_billing_messages ') ;
3737
3738 /* commented and modified as below for performance reasons. Archive Purge 11.5
3739 delete from pa_billing_messages bm
3740 where (bm.rowid)
3741 in
3742 ( select bm1.rowid
3743 from pa_billing_messages bm1
3744 where bm1.project_id = p_project_id
3745 and rownum <= l_commit_size
3746 ) ;
3747 */
3748 delete from pa_billing_messages bm
3749 where bm.project_id = p_project_id
3750 and rownum <= l_commit_size;
3751
3752 /*Code Changes for Bug No.2984871 start */
3753 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3754 /*Code Changes for Bug No.2984871 end */
3755 pa_debug.debug( ' ->After delete from pa_billing_messages ') ;
3756
3757 end if ;
3758
3759 if l_NoOfRecordsDel = 0 then
3760 -- Once the SqlCount becomes 0, which means that there are
3761 -- no more records to be purged then we exit the loop.
3762
3763 exit ;
3764
3765 else
3766 -- After "deleting" or "deleting and inserting" a set of records
3767 -- the transaction is commited. This also creates a record in the
3768 -- Pa_Purge_Project_details which will show the no. of records
3769 -- that are purged from each table.
3770
3771 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
3772 pa_purge.CommitProcess
3773 (p_purge_batch_id => p_purge_batch_id,
3774 p_project_id => p_project_id,
3775 p_table_name => 'PA_BILLING_MESSAGES',
3776 p_NoOfRecordsIns => l_NoOfRecordsIns,
3777 p_NoOfRecordsDel => l_NoOfRecordsDel,
3778 x_err_code => x_err_code,
3779 x_err_stack => x_err_stack,
3780 x_err_stage => x_err_stage
3781 ) ;
3782
3783 end if ;
3784 END LOOP ;
3785
3786
3787 x_err_stack := l_old_err_stack ;
3788 EXCEPTION
3789 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3790 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3791
3792 WHEN OTHERS THEN
3793 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.PA_BillingMessages' );
3794 pa_debug.debug('Error stage is '||x_err_stage );
3795 pa_debug.debug('Error stack is '||x_err_stack );
3796 pa_debug.debug(SQLERRM);
3797 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3798
3799 /* ATG NOCOPY changes */
3800 x_err_stack := l_old_err_stack ;
3801
3802 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3803
3804 end PA_BillingMessages;
3805
3806 -- Start of comments
3807 -- API name : PA_Billing_Main_Purge
3808 -- Type : Public
3809 -- Pre-reqs : None
3810 -- Function : Main purge procedure for billing tables.
3811 -- Calls a seperate procedure to purge each billing table
3812 -- Parameters :
3813 -- l p_purge_batch_id -> Purge batch Id
3814 -- p_project_id -> Project Id
3815 -- p_purge_release -> The release during which it is
3816 -- purged
3817 -- p_archive_flag -> This flag will indicate if the
3818 -- records need to be archived
3819 -- before they are purged.
3820 -- p_txn_to_date -> Date through which the transactions
3821 -- need to be purged. This value will
3822 -- be NULL if the purge batch is for
3823 -- active projects.
3824 -- p_commit_size -> The maximum number of records that
3825 -- can be allowed to remain uncommited.
3826 -- If the number of records processed
3827 -- goes byond this number then the
3828 -- process is commited.
3829 -- End of comments
3830
3831 procedure pa_billing_main_purge ( p_purge_batch_id in NUMBER,
3832 p_project_id in NUMBER,
3833 p_purge_release in VARCHAR2,
3834 p_txn_to_date in DATE,
3835 p_archive_flag in VARCHAR2,
3836 p_commit_size in NUMBER,
3837 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3838 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3839 x_err_code in OUT NOCOPY NUMBER ) is --File.Sql.39 bug 4440895
3840
3841 l_old_err_stack VARCHAR2(2000);
3842
3843 BEGIN
3844 l_old_err_stack := x_err_stack;
3845 PA_UTILS2.mrc_row_count := 0;
3846
3847 x_err_stack := x_err_stack || ' ->Before call to purge the data ';
3848
3849 -- Call the procedures to archive/purge data for each billing table
3850 --
3851 pa_debug.debug('*-> About to purge CRDLs ') ;
3852 pa_purge_billing.PA_CustRevDistLines
3853 (p_purge_batch_id => p_purge_batch_id,
3854 p_project_id => p_project_id,
3855 p_txn_to_date => p_txn_to_date,
3856 p_purge_release => p_purge_release,
3857 p_archive_flag => p_archive_flag,
3858 p_commit_size => p_commit_size,
3859 x_err_code => x_err_code,
3860 x_err_stack => x_err_stack,
3861 x_err_stage => x_err_stage
3862 ) ;
3863
3864 pa_debug.debug('*-> About to purge ERDLs ') ;
3865 pa_purge_billing.PA_EventRevDistLines
3866 (p_purge_batch_id => p_purge_batch_id,
3867 p_project_id => p_project_id,
3868 p_txn_to_date => p_txn_to_date,
3869 p_purge_release => p_purge_release,
3870 p_archive_flag => p_archive_flag,
3871 p_commit_size => p_commit_size,
3872 x_err_code => x_err_code,
3873 x_err_stack => x_err_stack,
3874 x_err_stage => x_err_stage
3875 ) ;
3876
3877 pa_debug.debug('*-> About to purge Events ') ;
3878 pa_purge_billing.PA_Event
3879 (p_purge_batch_id => p_purge_batch_id,
3880 p_project_id => p_project_id,
3881 p_txn_to_date => p_txn_to_date,
3882 p_purge_release => p_purge_release,
3883 p_archive_flag => p_archive_flag,
3884 p_commit_size => p_commit_size,
3885 x_err_code => x_err_code,
3886 x_err_stack => x_err_stack,
3887 x_err_stage => x_err_stage
3888 ) ;
3889
3890 pa_debug.debug('*-> About to purge Draft Revenue Items ') ;
3891 pa_purge_billing.PA_DraftRevItems
3892 (p_purge_batch_id => p_purge_batch_id,
3893 p_project_id => p_project_id,
3894 p_txn_to_date => p_txn_to_date,
3895 p_purge_release => p_purge_release,
3896 p_archive_flag => p_archive_flag,
3897 p_commit_size => p_commit_size,
3898 x_err_code => x_err_code,
3899 x_err_stack => x_err_stack,
3900 x_err_stage => x_err_stage
3901 ) ;
3902
3903 pa_debug.debug('*-> About to purge Draft Invoice Items ') ;
3904 pa_purge_billing.PA_DraftInvItems
3905 (p_purge_batch_id => p_purge_batch_id,
3906 p_project_id => p_project_id,
3907 p_txn_to_date => p_txn_to_date,
3908 p_purge_release => p_purge_release,
3909 p_archive_flag => p_archive_flag,
3910 p_commit_size => p_commit_size,
3911 x_err_code => x_err_code,
3912 x_err_stack => x_err_stack,
3913 x_err_stage => x_err_stage
3914 ) ;
3915
3916 pa_debug.debug('*-> About to purge Draft Revenues ') ;
3917 pa_purge_billing.PA_DraftRevenues
3918 (p_purge_batch_id => p_purge_batch_id,
3919 p_project_id => p_project_id,
3920 p_txn_to_date => p_txn_to_date,
3921 p_purge_release => p_purge_release,
3922 p_archive_flag => p_archive_flag,
3923 p_commit_size => p_commit_size,
3924 x_err_code => x_err_code,
3925 x_err_stack => x_err_stack,
3926 x_err_stage => x_err_stage
3927 ) ;
3928
3929 pa_debug.debug('*-> About to purge Draft Invoices ') ;
3930 pa_purge_billing.PA_DraftInvoices
3931 (p_purge_batch_id => p_purge_batch_id,
3932 p_project_id => p_project_id,
3933 p_txn_to_date => p_txn_to_date,
3934 p_purge_release => p_purge_release,
3935 p_archive_flag => p_archive_flag,
3936 p_commit_size => p_commit_size,
3937 x_err_code => x_err_code,
3938 x_err_stack => x_err_stack,
3939 x_err_stage => x_err_stage
3940 ) ;
3941
3942 pa_debug.debug('*-> About to purge distribution warnings ');
3943 pa_purge_billing.PA_DistWarnings
3944 (p_purge_batch_id => p_purge_batch_id,
3945 p_project_id => p_project_id,
3946 p_txn_to_date => p_txn_to_date,
3947 p_purge_release => p_purge_release,
3948 p_archive_flag => p_archive_flag,
3949 p_commit_size => p_commit_size,
3950 x_err_code => x_err_code,
3951 x_err_stack => x_err_stack,
3952 x_err_stage => x_err_stage
3953 ) ;
3954
3955 pa_debug.debug('*-> About to purge billing messages ');
3956 pa_purge_billing.PA_BillingMessages
3957 (p_purge_batch_id => p_purge_batch_id,
3958 p_project_id => p_project_id,
3959 p_txn_to_date => p_txn_to_date,
3960 p_purge_release => p_purge_release,
3961 p_archive_flag => p_archive_flag,
3962 p_commit_size => p_commit_size,
3963 x_err_code => x_err_code,
3964 x_err_stack => x_err_stack,
3965 x_err_stage => x_err_stage
3966 ) ;
3967
3968 pa_debug.debug('*-> About to purge retention details ');
3969 pa_purge_billing.PA_RetnInvDetails
3970 ( p_purge_batch_id => p_purge_batch_id,
3971 p_project_id => p_project_id,
3972 p_txn_to_date => p_txn_to_date,
3973 p_purge_release => p_purge_release,
3974 p_archive_flag => p_archive_flag,
3975 p_commit_size => p_commit_size,
3976 x_err_code => x_err_code,
3977 x_err_stack => x_err_stack,
3978 x_err_stage => x_err_stage
3979 );
3980
3981 x_err_stack := l_old_err_stack;
3982
3983 exception
3984 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3985 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3986
3987 WHEN OTHERS THEN
3988 pa_debug.debug('Error Procedure Name := PA_PURGE_BILLING.pa_billing_main_purge' );
3989 pa_debug.debug('Error stage is '||x_err_stage );
3990 pa_debug.debug('Error stack is '||x_err_stack );
3991 pa_debug.debug(SQLERRM);
3992 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3993
3994 /* ATG NOCOPY changes */
3995 x_err_stack := l_old_err_stack;
3996
3997
3998 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3999
4000 END pa_billing_main_purge ;
4001
4002
4003 END pa_purge_billing;