1 package body pa_purge_icip as
2 /*$Header: PAICIPPB.pls 120.6.12010000.2 2009/06/23 14:25:58 atshukla ship $*/
3
4
5 l_commit_size NUMBER ;
6 l_mrc_flag VARCHAR2(1) := 'N';
7 l_pmy_commit_size NUMBER ;
8
9 /*
10
11 -- Start of comments
12 -- API name : PA_DraftInvDetails
13 -- Type : Private
14 -- Pre-reqs : None
15 -- Function : Archive and Purge data for table PA_DRAFT_INVOICE_DETAILS_ALL
16 -- Parameters : See common list above
17 -- End of comments
18 */
19
20 procedure PA_DraftInvDetails ( p_purge_batch_id in NUMBER,
21 p_project_id in NUMBER,
22 p_purge_release in VARCHAR2,
23 p_txn_to_date in DATE,
24 p_archive_flag in VARCHAR2,
25 p_commit_size in NUMBER,
26 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
27 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_err_code in OUT NOCOPY NUMBER ) is --File.Sql.39 bug 4440895
29
30
31 l_old_err_stage VARCHAR2(2000);
32 l_old_err_stack VARCHAR2(2000);
33 l_NoOfRecordsIns NUMBER;
34 l_NoOfRecordsDel NUMBER;
35 l_MRC_NoOfRecordsIns NUMBER := NULL;
36 l_MRC_NoOfRecordsDel NUMBER := NULL;
37
38 begin
39
40 l_old_err_stack := x_err_stack;
41
42 x_err_stack := x_err_stack || ' ->Entering PA_DRAFT_INVOICE_DETAILS_ALL' ;
43
44 pa_debug.debug(x_err_stack);
45
46 LOOP
47 l_NoOfRecordsDel := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
48 l_NoOfRecordsIns := 0; -- Value of l_NoOfRecordsDel is reset for BUg 4104133
49 if p_archive_flag = 'Y' then
50
51 -- If archive option is selected then the records are
52 -- inserted into the archived into the archive tables
53 -- before being purged. The where condition is such that
54 -- only the it inserts half the no. of records specified
55 -- in the commit size.
56
57 l_commit_size := pa_utils2.arpur_mrc_commit_size / 2 ;
58
59 pa_debug.debug( ' ->Before insert into PA_DRAFT_INV_DETS_AR') ;
60
61 if p_txn_to_date IS NOT NULL then
62 insert into PA_DRAFT_INV_DETS_AR (
63 PURGE_BATCH_ID,
64 PURGE_RELEASE,
65 PURGE_PROJECT_ID,
66 DRAFT_INVOICE_DETAIL_ID,
67 EXPENDITURE_ITEM_ID,
68 LINE_NUM,
69 PROJECT_ID,
70 DENOM_CURRENCY_CODE,
71 DENOM_BILL_AMOUNT,
72 ACCT_CURRENCY_CODE,
73 BILL_AMOUNT,
74 REQUEST_ID,
75 LAST_UPDATE_DATE,
76 LAST_UPDATED_BY,
77 CREATION_DATE,
78 CREATED_BY,
79 LAST_UPDATE_LOGIN,
80 ACCT_RATE_TYPE,
81 ACCT_RATE_DATE,
82 ACCT_EXCHANGE_RATE,
83 CC_PROJECT_ID,
84 CC_TAX_TASK_ID,
85 ORG_ID,
86 REV_CODE_COMBINATION_ID,
87 DRAFT_INVOICE_NUM,
88 DRAFT_INVOICE_LINE_NUM,
89 OUTPUT_VAT_TAX_ID,
90 OUTPUT_TAX_CLASSIFICATION_CODE,
91 OUTPUT_TAX_EXEMPT_FLAG,
92 OUTPUT_TAX_EXEMPT_REASON_CODE,
93 OUTPUT_TAX_EXEMPT_NUMBER,
94 LINE_NUM_REVERSED,
95 DETAIL_ID_REVERSED,
96 REVERSED_FLAG,
97 PROJACCT_CURRENCY_CODE,
98 PROJACCT_COST_AMOUNT,
99 PROJACCT_BILL_AMOUNT,
100 MARKUP_CALC_BASE_CODE,
101 IND_COMPILED_SET_ID,
102 RULE_PERCENTAGE,
103 BILL_RATE,
104 BILL_MARKUP_PERCENTAGE,
105 BASE_AMOUNT,
106 SCHEDULE_LINE_PERCENTAGE,
107 INVOICED_FLAG,
108 ORIG_DRAFT_INVOICE_NUM,
109 ORIG_DRAFT_INVOICE_LINE_NUM,
110 PROGRAM_APPLICATION_ID,
111 PROGRAM_ID,
112 PROGRAM_UPDATE_DATE,
113 TP_JOB_ID,
114 PROV_PROJ_BILL_JOB_ID,
115 PROJECT_TP_CURRENCY_CODE,
116 PROJECT_TP_RATE_DATE,
117 PROJECT_TP_RATE_TYPE,
118 PROJECT_TP_EXCHANGE_RATE,
119 PROJFUNC_TP_CURRENCY_CODE,
120 PROJFUNC_TP_RATE_DATE,
121 PROJFUNC_TP_RATE_TYPE,
122 PROJFUNC_TP_EXCHANGE_RATE,
123 PROJECT_TRANSFER_PRICE,
124 PROJFUNC_TRANSFER_PRICE,
125 TP_AMT_TYPE_CODE
126 )
127 select
128 p_purge_batch_id,
129 p_purge_release,
130 p_project_id,
131 DRAFT_INVOICE_DETAIL_ID,
132 EXPENDITURE_ITEM_ID,
133 LINE_NUM,
134 PROJECT_ID,
135 DENOM_CURRENCY_CODE,
136 DENOM_BILL_AMOUNT,
137 ACCT_CURRENCY_CODE,
138 BILL_AMOUNT,
139 REQUEST_ID,
140 LAST_UPDATE_DATE,
141 LAST_UPDATED_BY,
142 CREATION_DATE,
143 CREATED_BY,
144 LAST_UPDATE_LOGIN,
145 ACCT_RATE_TYPE,
146 ACCT_RATE_DATE,
147 ACCT_EXCHANGE_RATE,
148 CC_PROJECT_ID,
149 CC_TAX_TASK_ID,
150 ORG_ID,
151 REV_CODE_COMBINATION_ID,
152 DRAFT_INVOICE_NUM,
153 DRAFT_INVOICE_LINE_NUM,
154 OUTPUT_VAT_TAX_ID,
155 OUTPUT_TAX_CLASSIFICATION_CODE,
156 OUTPUT_TAX_EXEMPT_FLAG,
157 OUTPUT_TAX_EXEMPT_REASON_CODE,
158 OUTPUT_TAX_EXEMPT_NUMBER,
159 LINE_NUM_REVERSED,
160 DETAIL_ID_REVERSED,
161 REVERSED_FLAG,
162 PROJACCT_CURRENCY_CODE,
163 PROJACCT_COST_AMOUNT,
164 PROJACCT_BILL_AMOUNT,
165 MARKUP_CALC_BASE_CODE,
166 IND_COMPILED_SET_ID,
167 RULE_PERCENTAGE,
168 BILL_RATE,
169 BILL_MARKUP_PERCENTAGE,
170 BASE_AMOUNT,
171 SCHEDULE_LINE_PERCENTAGE,
172 INVOICED_FLAG,
173 ORIG_DRAFT_INVOICE_NUM,
174 ORIG_DRAFT_INVOICE_LINE_NUM,
175 PROGRAM_APPLICATION_ID,
176 PROGRAM_ID,
177 PROGRAM_UPDATE_DATE,
178 TP_JOB_ID,
179 PROV_PROJ_BILL_JOB_ID,
180 PROJECT_TP_CURRENCY_CODE,
181 PROJECT_TP_RATE_DATE,
182 PROJECT_TP_RATE_TYPE,
183 PROJECT_TP_EXCHANGE_RATE,
184 PROJFUNC_TP_CURRENCY_CODE,
185 PROJFUNC_TP_RATE_DATE,
186 PROJFUNC_TP_RATE_TYPE,
187 PROJFUNC_TP_EXCHANGE_RATE,
188 PROJECT_TRANSFER_PRICE,
189 PROJFUNC_TRANSFER_PRICE,
190 TP_AMT_TYPE_CODE
191 from pa_draft_invoice_details_all
192 where expenditure_item_id in
193 ( select ei.expenditure_item_id
194 from pa_tasks t,
195 pa_expenditure_items_all ei
196 where ei.expenditure_item_date <= p_txn_to_date
197 and ei.task_id = t.task_id
198 and t.project_id = p_project_id )
199 and rownum <= l_commit_size;
200 else
201 insert into PA_DRAFT_INV_DETS_AR (
202 PURGE_BATCH_ID,
203 PURGE_RELEASE,
204 PURGE_PROJECT_ID,
205 DRAFT_INVOICE_DETAIL_ID,
206 EXPENDITURE_ITEM_ID,
207 LINE_NUM,
208 PROJECT_ID,
209 DENOM_CURRENCY_CODE,
210 DENOM_BILL_AMOUNT,
211 ACCT_CURRENCY_CODE,
212 BILL_AMOUNT,
213 REQUEST_ID,
214 LAST_UPDATE_DATE,
215 LAST_UPDATED_BY,
216 CREATION_DATE,
217 CREATED_BY,
218 LAST_UPDATE_LOGIN,
219 ACCT_RATE_TYPE,
220 ACCT_RATE_DATE,
221 ACCT_EXCHANGE_RATE,
222 CC_PROJECT_ID,
223 CC_TAX_TASK_ID,
224 ORG_ID,
225 REV_CODE_COMBINATION_ID,
226 DRAFT_INVOICE_NUM,
227 DRAFT_INVOICE_LINE_NUM,
228 -- OUTPUT_VAT_TAX_ID,
229 OUTPUT_TAX_CLASSIFICATION_CODE,
230 OUTPUT_TAX_EXEMPT_FLAG,
231 OUTPUT_TAX_EXEMPT_REASON_CODE,
232 OUTPUT_TAX_EXEMPT_NUMBER,
233 LINE_NUM_REVERSED,
234 DETAIL_ID_REVERSED,
235 REVERSED_FLAG,
236 PROJACCT_CURRENCY_CODE,
237 PROJACCT_COST_AMOUNT,
238 PROJACCT_BILL_AMOUNT,
239 MARKUP_CALC_BASE_CODE,
240 IND_COMPILED_SET_ID,
241 RULE_PERCENTAGE,
242 BILL_RATE,
243 BILL_MARKUP_PERCENTAGE,
244 BASE_AMOUNT,
245 SCHEDULE_LINE_PERCENTAGE,
246 INVOICED_FLAG,
247 ORIG_DRAFT_INVOICE_NUM,
248 ORIG_DRAFT_INVOICE_LINE_NUM,
249 PROGRAM_APPLICATION_ID,
250 PROGRAM_ID,
251 PROGRAM_UPDATE_DATE,
252 TP_JOB_ID,
253 PROV_PROJ_BILL_JOB_ID,
254 PROJECT_TP_CURRENCY_CODE,
255 PROJECT_TP_RATE_DATE,
256 PROJECT_TP_RATE_TYPE,
257 PROJECT_TP_EXCHANGE_RATE,
258 PROJFUNC_TP_CURRENCY_CODE,
259 PROJFUNC_TP_RATE_DATE,
260 PROJFUNC_TP_RATE_TYPE,
261 PROJFUNC_TP_EXCHANGE_RATE,
262 PROJECT_TRANSFER_PRICE,
263 PROJFUNC_TRANSFER_PRICE,
264 TP_AMT_TYPE_CODE
265 )
266 select
267 p_purge_batch_id,
268 p_purge_release,
269 p_project_id,
270 DRAFT_INVOICE_DETAIL_ID,
271 EXPENDITURE_ITEM_ID,
272 LINE_NUM,
273 PROJECT_ID,
274 DENOM_CURRENCY_CODE,
275 DENOM_BILL_AMOUNT,
276 ACCT_CURRENCY_CODE,
277 BILL_AMOUNT,
278 REQUEST_ID,
279 LAST_UPDATE_DATE,
280 LAST_UPDATED_BY,
281 CREATION_DATE,
282 CREATED_BY,
283 LAST_UPDATE_LOGIN,
284 ACCT_RATE_TYPE,
285 ACCT_RATE_DATE,
286 ACCT_EXCHANGE_RATE,
287 CC_PROJECT_ID,
288 CC_TAX_TASK_ID,
289 ORG_ID,
290 REV_CODE_COMBINATION_ID,
291 DRAFT_INVOICE_NUM,
292 DRAFT_INVOICE_LINE_NUM,
293 -- OUTPUT_VAT_TAX_ID,
294 OUTPUT_TAX_CLASSIFICATION_CODE,
295 OUTPUT_TAX_EXEMPT_FLAG,
296 OUTPUT_TAX_EXEMPT_REASON_CODE,
297 OUTPUT_TAX_EXEMPT_NUMBER,
298 LINE_NUM_REVERSED,
299 DETAIL_ID_REVERSED,
300 REVERSED_FLAG,
301 PROJACCT_CURRENCY_CODE,
302 PROJACCT_COST_AMOUNT,
303 PROJACCT_BILL_AMOUNT,
304 MARKUP_CALC_BASE_CODE,
305 IND_COMPILED_SET_ID,
306 RULE_PERCENTAGE,
307 BILL_RATE,
308 BILL_MARKUP_PERCENTAGE,
309 BASE_AMOUNT,
310 SCHEDULE_LINE_PERCENTAGE,
311 INVOICED_FLAG,
312 ORIG_DRAFT_INVOICE_NUM,
313 ORIG_DRAFT_INVOICE_LINE_NUM,
314 PROGRAM_APPLICATION_ID,
315 PROGRAM_ID,
316 PROGRAM_UPDATE_DATE,
317 TP_JOB_ID,
318 PROV_PROJ_BILL_JOB_ID,
319 PROJECT_TP_CURRENCY_CODE,
320 PROJECT_TP_RATE_DATE,
321 PROJECT_TP_RATE_TYPE,
322 PROJECT_TP_EXCHANGE_RATE,
323 PROJFUNC_TP_CURRENCY_CODE,
324 PROJFUNC_TP_RATE_DATE,
325 PROJFUNC_TP_RATE_TYPE,
326 PROJFUNC_TP_EXCHANGE_RATE,
327 PROJECT_TRANSFER_PRICE,
328 PROJFUNC_TRANSFER_PRICE,
329 TP_AMT_TYPE_CODE
330 from pa_draft_invoice_details_all
331 where expenditure_item_id in
332 ( select ei.expenditure_item_id /* Bug#4943324 : Perf Issue : Removed the Task table */
333 from
334 pa_expenditure_items_all ei
335 where ei.project_id = p_project_id )
336 and rownum <= l_commit_size;
337 end if;
338
339 l_NoOfRecordsIns := SQL%ROWCOUNT ;
340 pa_debug.debug( ' ->After insert into PA_DRAFT_INV_DETS_AR') ;
341 /*Code Changes for Bug No.2984871 start */
342 if l_NoOfRecordsIns > 0 then
343 /*Code Changes for Bug No.2984871 end */
344 -- First call the MRC procedure to archive the MC table
345 -- We have a seperate delete statement if the archive option is
346 -- selected because if archive option is selected the the records
347 -- being purged will be those records which are already archived.
348 -- table and
349 /* Commented out for MRC migration to SLA
350 IF (l_mrc_flag = 'Y') THEN
351 PA_MC_DraftInvoiceDetails(
352 p_purge_batch_id,
353 p_project_id,
354 p_txn_to_date,
355 p_purge_release,
356 p_archive_flag,
357 l_commit_size,
358 x_err_code,
359 x_err_stack,
360 x_err_stage,
361 l_MRC_NoOfRecordsIns);
362 END IF; */
363
364 /* Each time thru the loop need to make sure that reset the
365 * counter tracking the number of records that deleted from
366 * the mrc table.
367 */
368 IF (l_mrc_flag = 'Y') THEN
369 pa_utils2.MRC_row_count := 0;
370 END IF;
371
372 x_err_stage := 'Before deleting records from PA_MC_DRAFT_INV_DETAILS_ALL';
373 /*delete from pa_mc_draft_inv_details_all mdi
374 where (mdi.draft_invoice_detail_id) in
375 ( select mdir.draft_invoice_detail_id
376 from pa_mc_draft_inv_dets_ar mdir
377 where mdir.purge_project_id = p_project_id ) ; */
378
379 /* Commented the above and added the following for bug 3611190 */
380
381 /* Commented out for MRC migration to SLA delete from pa_mc_draft_inv_details_all mdi
382 where mdi.set_of_books_id > 0
383 and exists ( select 1 from pa_mc_draft_inv_dets_ar mdir
384 where mdir.purge_project_id = p_project_id
385 and mdir.set_of_books_id > 0
386 and mdir.draft_invoice_detail_id = mdi.draft_invoice_detail_id);
387
388 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT; */
389
390 /* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
391 whenever any of the source project is purged. Since we have to show
392 appropriate message, in the case of Drilldown from Intercompany Invoice
393 to source Expenditure items, will not have a performance hit as we would
394 know upfront that the at least one of the source project has been purged.
395 */
396
397 update pa_draft_invoices_all di
398 set di.purge_flag = 'Y'
399 where ( di.project_id, di.draft_invoice_num ) in
400 ( select did.project_id, did.draft_invoice_num
401 from pa_draft_inv_dets_ar did
402 where did.cc_project_id = p_project_id
403 )
404 and rownum < l_commit_size;
405
406
407 pa_debug.debug( ' ->Before delete from pa_draft_invoice_details_all ') ;
408
409 delete from pa_draft_invoice_details_all did
410 where (did.project_id, did.draft_invoice_detail_id) in
411 ( select did2.project_id, did2.draft_invoice_detail_id
412 from PA_DRAFT_INV_DETS_AR did2
413 where did2.purge_project_id = p_project_id
414 ) ;
415 /* Bug 2984871: Moved the below statement above the pa_debug.debug api call */
416 l_NoOfRecordsDel := SQL%ROWCOUNT ;
417
418 pa_debug.debug( ' ->After delete from pa_draft_invoice_details_all ') ;
419
420
421 end if ;
422 else
423
424 l_commit_size := pa_utils2.arpur_mrc_commit_size;
425 l_pmy_commit_size := pa_utils2.arpur_commit_size;
426
427 /* Each time thru the loop need to make sure that reset the
428 * counter tracking the number of records that deleted from
429 * the mrc table.
430 */
431 IF (l_mrc_flag = 'Y') THEN
432 pa_utils2.MRC_row_count := 0;
433 END IF;
434
435 -- If the archive option is not selected then the delete will
436 -- be based on the commit size.
437
438 pa_debug.debug( ' ->Before delete from pa_draft_invoice_details_all ') ;
439
440 if p_txn_to_date is NOT NULL then
441
442 /*delete from pa_mc_draft_inv_details_all mdi
443 where (mdi.draft_invoice_detail_id ) in
444 ( select did.draft_invoice_detail_id
445 from pa_tasks t,
446 pa_expenditure_items_all ei,
447 pa_draft_invoice_details_all did
448 where ei.expenditure_item_date <= p_txn_to_date
449 and ei.task_id = t.task_id
450 and t.project_id = p_project_id
451 and t.project_id = did.project_id
452 )
453 and rownum < l_commit_size; */
454
455 /* commented the above and added the following for bug 3611190 */
456
457 /* Commented out for MRC migration to SLA
458 delete from pa_mc_draft_inv_details_all mdi where
459 mdi.set_of_books_id > 0
460 and exists
461 (select 1 from pa_draft_invoice_details_all did,
462 pa_expenditure_items_all ei
463 where ei.expenditure_item_id = did.expenditure_item_id
464 and ei.project_id = p_project_id
465 and ei.expenditure_item_date <= p_txn_to_date
466 and did.draft_invoice_detail_id = mdi.draft_invoice_detail_id)
467 and rownum < l_pmy_commit_size;
468
469 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT ; */
470
471 /* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
472 whenever any of the source project is purged. Since we have to show
473 appropriate message, in the case of Drilldown from Intercompany Invoice
474 to source Expenditure items, will not have a performance hit as we would
475 know upfront that the at least one of the source project has been purged.
476 */
477
478 update pa_draft_invoices_all di
479 set di.purge_flag = 'Y'
480 where ( di.project_id, di.draft_invoice_num ) in
481 ( select did.project_id, did.draft_invoice_num
482 from pa_draft_invoice_details_all did
483 where did.cc_project_id = p_project_id
484 and did.expenditure_item_id in
485 ( select ei.expenditure_item_id
486 from pa_tasks t,
487 pa_expenditure_items_all ei
488 where ei.expenditure_item_date <= p_txn_to_date
489 and ei.task_id = t.task_id
490 and t.project_id = p_project_id
491 )
492 )
493 and rownum < l_commit_size;
494
495
496 --Commenting out for bug#7701114 and taking out of loop
497 /* delete from pa_draft_invoice_details_all did
498 where (did.expenditure_item_id ) in
499 ( select ei.expenditure_item_id
500 from pa_tasks t,
501 pa_expenditure_items_all ei
502 where ei.expenditure_item_date <= p_txn_to_date
503 and ei.task_id = t.task_id
504 and t.project_id = p_project_id
505 )
506 and did.cc_project_id = p_project_id
507 and rownum < l_commit_size;
508
509 l_NoOfRecordsDel := SQL%ROWCOUNT ; */
510
511 else
512
513 /*delete from pa_mc_draft_inv_details_all mdi
514 where (mdi.draft_invoice_detail_id ) in
515 ( select did.draft_invoice_detail_id
516 from pa_tasks t,
517 pa_expenditure_items_all ei,
518 pa_draft_invoice_details_all did
519 where ei.task_id = t.task_id
520 and t.project_id = p_project_id
521 and t.project_id = did.project_id
522 )
523 and rownum < l_commit_size; */
524
525 /* commented the above and added the following for bug 3611190 */
526
527 /* Commented out for MRC migration to SLA
528 delete from pa_mc_draft_inv_details_all mdi where
529 mdi.set_of_books_id > 0
530 and exists
531 (select 1 from pa_draft_invoice_details_all did,
532 pa_expenditure_items_all ei
533 where ei.expenditure_item_id = did.expenditure_item_id
534 and ei.project_id = p_project_id
535 and did.draft_invoice_detail_id = mdi.draft_invoice_detail_id)
536 and rownum < l_pmy_commit_size;
537
538 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT ; */
539
540
541 /* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
542 whenever any of the source project is purged. Since we have to show
543 appropriate message, in the case of Drilldown from Intercompany Invoice
544 to source Expenditure items, will not have a performance hit as we would
545 know upfront that the at least one of the source project has been purged.
546 */
547
548 update pa_draft_invoices_all di
549 set di.purge_flag = 'Y'
550 where ( di.project_id, di.draft_invoice_num ) in
551 ( select did.project_id, did.draft_invoice_num
552 from pa_draft_invoice_details_all did
553 where did.cc_project_id = p_project_id
554 )
555 and rownum < l_commit_size;
556
557
558 --Commenting out for bug#7701114 and taking out of loop
559 /*delete from pa_draft_invoice_details_all did
560 where (did.expenditure_item_id ) in
561 ( select ei.expenditure_item_id /* Bug#4943324 : Perf Issue : Removed the Task table
562 from pa_expenditure_items_all ei
563 where ei.project_id = p_project_id
564 )
565 and did.cc_project_id = p_project_id
566 and rownum < l_commit_size;
567
568 l_NoOfRecordsDel := SQL%ROWCOUNT ; */
569
570 end if;
571
572
573 pa_debug.debug( ' ->After delete from pa_draft_invoice_details_all ') ;
574
575 end if ;
576
577 /* Bug 2984871: Changed sql%rowcount to l_NoOfRecordsDel in the if condition below */
578 if NVL(l_NoOfRecordsDel,0) = 0 then
579 -- Once the SqlCount becomes 0, which means that there are
580 -- no more records to be purged then we exit the loop.
581
582 exit ;
583
584 else
585 -- After "deleting" or "deleting and inserting" a set of records
586 -- the transaction is commited. This also creates a record in the
587 -- Pa_Purge_Project_details which will show the no. of records
588 -- that are purged from each table.
589
590 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
591 pa_purge.CommitProcess
592 (p_purge_batch_id => p_purge_batch_id,
593 p_project_id => p_project_id,
594 p_table_name => 'PA_DRAFT_INVOICE_DETAILS_ALL',
595 p_NoOfRecordsIns => l_NoOfRecordsIns,
596 p_NoOfRecordsDel => l_NoOfRecordsDel,
597 x_err_code => x_err_code,
598 x_err_stack => x_err_stack,
599 x_err_stage => x_err_stage,
600 p_MRC_table_name => 'PA_MC_DRAFT_INV_DETAILS_ALL',
601 p_MRC_NoOfRecordsIns => l_MRC_NoOfRecordsIns,
602 p_MRC_NoOfRecordsDel => l_MRC_NoOfRecordsDel
603 ) ;
604
605 PA_UTILS2.mrc_row_count := 0;
606
607 end if ;
608 END LOOP ;
609
610 x_err_stack := l_old_err_stack ;
611
612 --Fix for bug#7701114 , creating a separate loop to delete records from pa_draft_invoice_details_all
613 LOOP
614 IF p_archive_flag <> 'Y' THEN
615
616 l_commit_size := pa_utils2.arpur_mrc_commit_size;
617
618 IF p_txn_to_date is NOT NULL THEN
619 delete from pa_draft_invoice_details_all did
620 where (did.expenditure_item_id ) in
621 ( select ei.expenditure_item_id
622 from pa_tasks t,
623 pa_expenditure_items_all ei
624 where ei.expenditure_item_date <= p_txn_to_date
625 and ei.task_id = t.task_id
626 and t.project_id = p_project_id)
627 and did.cc_project_id = p_project_id
628 and rownum < l_commit_size;
629
630 l_NoOfRecordsDel := SQL%ROWCOUNT ;
631
632 ELSE
633
634 delete from pa_draft_invoice_details_all did
635 where (did.expenditure_item_id ) in
636 ( select ei.expenditure_item_id
637 from --pa_tasks t, /* Bug#4943324 : Perf Issue : Removed the Task table */
638 pa_expenditure_items_all ei
639 where -- ei.task_id = t.task_id and
640 ei.project_id = p_project_id)
641 and did.cc_project_id = p_project_id
642 and rownum < l_commit_size;
643
644 l_NoOfRecordsDel := SQL%ROWCOUNT ;
645
646 END IF;
647
648 END IF ;
649
650 IF NVL(l_NoOfRecordsDel,0) = 0 THEN
651 exit ;
652 ELSE
653 pa_purge.CommitProcess
654 (p_purge_batch_id => p_purge_batch_id,
655 p_project_id => p_project_id,
656 p_table_name => 'PA_DRAFT_INVOICE_DETAILS_ALL',
657 p_NoOfRecordsIns => l_NoOfRecordsIns,
658 p_NoOfRecordsDel => l_NoOfRecordsDel,
659 x_err_code => x_err_code,
660 x_err_stack => x_err_stack,
661 x_err_stage => x_err_stage) ;
662
663 END IF ;
664 END LOOP ;
665
666 EXCEPTION
667 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
668 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
669
670 WHEN OTHERS THEN
671 pa_debug.debug('Error Procedure Name := PA_PURGE_ICIP.PA_DraftInvDetails' );
672 pa_debug.debug('Error stage is '||x_err_stage );
673 pa_debug.debug('Error stack is '||x_err_stack );
674 pa_debug.debug(SQLERRM);
675
676 /* ATG Changes */
677 x_err_stack := l_old_err_stack ;
678
679 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
680
681 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
682
683 end PA_DraftInvDetails ;
684
685
686 -- Start of comments
687 -- API name : PA_MC_DraftInvoiceDetails
688 -- Type : Private
689 -- Pre-reqs : None
690 -- Function : Archive and Purge data for table pa_mc_draft_inv_details_all
691 -- Parameters : See common list above
692 -- End of comments
693
694 procedure PA_MC_DraftInvoiceDetails(
695 p_purge_batch_id IN NUMBER,
696 p_project_id IN NUMBER,
697 p_txn_to_date IN DATE,
698 p_purge_release IN VARCHAR2,
699 p_archive_flag IN VARCHAR2,
700 p_commit_size IN NUMBER,
701 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
702 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
703 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
704 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
705 IS
706
707 l_old_err_stage VARCHAR2(2000);
708 l_old_err_stack VARCHAR2(2000);
709
710 begin
711
712 l_old_err_stack := x_err_stack;
713
714 x_err_stack := x_err_stack || ' ->Entering PA_MC_DraftInvoiceDetails ';
715
716 pa_debug.debug(x_err_stack);
717
718 pa_debug.debug( ' ->Before insert into PA_MC_DRAFT_INV_DETS_AR') ;
719
720 /* Commented out for MRC migration to SLA insert into PA_MC_DRAFT_INV_DETS_AR
721 (
722 PURGE_BATCH_ID,
723 PURGE_RELEASE,
724 PURGE_PROJECT_ID,
725 SET_OF_BOOKS_ID,
726 DRAFT_INVOICE_DETAIL_ID,
727 PROJECT_ID,
728 INVOICED_FLAG,
729 ACCT_CURRENCY_CODE,
730 BILL_AMOUNT,
731 REQUEST_ID,
732 ACCT_RATE_TYPE,
733 ACCT_RATE_DATE,
734 ACCT_EXCHANGE_RATE,
735 PROGRAM_APPLICATION_ID,
736 PROGRAM_ID,
737 PROGRAM_UPDATE_DATE
738 )
739 select
740 p_purge_batch_id,
741 p_purge_release,
742 p_project_id,
743 mc.SET_OF_BOOKS_ID,
744 mc.DRAFT_INVOICE_DETAIL_ID,
745 mc.PROJECT_ID,
746 mc.INVOICED_FLAG,
747 mc.ACCT_CURRENCY_CODE,
748 mc.BILL_AMOUNT,
749 mc.REQUEST_ID,
750 mc.ACCT_RATE_TYPE,
751 mc.ACCT_RATE_DATE,
752 mc.ACCT_EXCHANGE_RATE,
753 mc.PROGRAM_APPLICATION_ID,
754 mc.PROGRAM_ID,
755 mc.PROGRAM_UPDATE_DATE
756 from pa_mc_draft_inv_details_all mc,
757 pa_draft_inv_dets_ar ar
758 where ar.purge_project_id = p_project_id
759 and mc.draft_invoice_detail_id = ar.draft_invoice_detail_id; */
760
761 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
762
763 pa_debug.debug( ' ->After insert into PA_MC_DRAFT_INV_DETS_AR') ;
764 x_err_stack := l_old_err_stack ;
765
766 EXCEPTION
767 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
768 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
769
770 WHEN OTHERS THEN
771 pa_debug.debug('Error Procedure Name := PA_PURGE_ICIP.PA_MC_DraftInvoiceDetails' );
772 pa_debug.debug('Error stage is '||x_err_stage );
773 pa_debug.debug('Error stack is '||x_err_stack );
774 pa_debug.debug(SQLERRM);
775 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
776
777 /* ATG Changes */
778 x_err_stack := l_old_err_stack ;
779 x_MRC_NoOfRecordsIns := null;
780
781 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
782
783 end PA_MC_DraftInvoiceDetails;
784
785 END pa_purge_icip;
[Home] [Help]