1 package body pa_purge_icip as
2 /*$Header: PAICIPPB.pls 120.6 2006/02/17 15:23:06 rmarcel noship $*/
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 delete from pa_draft_invoice_details_all did
496 where (did.expenditure_item_id ) in
497 ( select ei.expenditure_item_id
498 from pa_tasks t,
499 pa_expenditure_items_all ei
500 where ei.expenditure_item_date <= p_txn_to_date
501 and ei.task_id = t.task_id
502 and t.project_id = p_project_id
503 )
504 and did.cc_project_id = p_project_id
505 and rownum < l_commit_size;
506
507 l_NoOfRecordsDel := SQL%ROWCOUNT ;
508
509 else
510
511 /*delete from pa_mc_draft_inv_details_all mdi
512 where (mdi.draft_invoice_detail_id ) in
513 ( select did.draft_invoice_detail_id
514 from pa_tasks t,
515 pa_expenditure_items_all ei,
516 pa_draft_invoice_details_all did
517 where ei.task_id = t.task_id
518 and t.project_id = p_project_id
519 and t.project_id = did.project_id
520 )
521 and rownum < l_commit_size; */
522
523 /* commented the above and added the following for bug 3611190 */
524
525 /* Commented out for MRC migration to SLA
526 delete from pa_mc_draft_inv_details_all mdi where
527 mdi.set_of_books_id > 0
528 and exists
529 (select 1 from pa_draft_invoice_details_all did,
530 pa_expenditure_items_all ei
531 where ei.expenditure_item_id = did.expenditure_item_id
532 and ei.project_id = p_project_id
533 and did.draft_invoice_detail_id = mdi.draft_invoice_detail_id)
534 and rownum < l_pmy_commit_size;
535
536 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT ; */
537
538
539 /* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
540 whenever any of the source project is purged. Since we have to show
541 appropriate message, in the case of Drilldown from Intercompany Invoice
542 to source Expenditure items, will not have a performance hit as we would
543 know upfront that the at least one of the source project has been purged.
544 */
545
546 update pa_draft_invoices_all di
547 set di.purge_flag = 'Y'
548 where ( di.project_id, di.draft_invoice_num ) in
549 ( select did.project_id, did.draft_invoice_num
550 from pa_draft_invoice_details_all did
551 where did.cc_project_id = p_project_id
552 )
553 and rownum < l_commit_size;
554
555
556 delete from pa_draft_invoice_details_all did
557 where (did.expenditure_item_id ) in
558 ( select ei.expenditure_item_id /* Bug#4943324 : Perf Issue : Removed the Task table */
559 from pa_expenditure_items_all ei
560 where ei.project_id = p_project_id
561 )
562 and did.cc_project_id = p_project_id
563 and rownum < l_commit_size;
564
565 l_NoOfRecordsDel := SQL%ROWCOUNT ;
566
567 end if;
568
569
570 pa_debug.debug( ' ->After delete from pa_draft_invoice_details_all ') ;
571
572 end if ;
573
574 /* Bug 2984871: Changed sql%rowcount to l_NoOfRecordsDel in the if condition below */
575 if NVL(l_NoOfRecordsDel,0) = 0 then
576 -- Once the SqlCount becomes 0, which means that there are
577 -- no more records to be purged then we exit the loop.
578
579 exit ;
580
581 else
582 -- After "deleting" or "deleting and inserting" a set of records
583 -- the transaction is commited. This also creates a record in the
584 -- Pa_Purge_Project_details which will show the no. of records
585 -- that are purged from each table.
586
587 pa_debug.debug( ' ->Calling pa_purge.CommitProcess ') ;
588 pa_purge.CommitProcess
589 (p_purge_batch_id => p_purge_batch_id,
590 p_project_id => p_project_id,
591 p_table_name => 'PA_DRAFT_INVOICE_DETAILS_ALL',
592 p_NoOfRecordsIns => l_NoOfRecordsIns,
593 p_NoOfRecordsDel => l_NoOfRecordsDel,
594 x_err_code => x_err_code,
595 x_err_stack => x_err_stack,
596 x_err_stage => x_err_stage,
597 p_MRC_table_name => 'PA_MC_DRAFT_INV_DETAILS_ALL',
598 p_MRC_NoOfRecordsIns => l_MRC_NoOfRecordsIns,
599 p_MRC_NoOfRecordsDel => l_MRC_NoOfRecordsDel
600 ) ;
601
602 PA_UTILS2.mrc_row_count := 0;
603
604 end if ;
605 END LOOP ;
606
607
608 x_err_stack := l_old_err_stack ;
609 EXCEPTION
610 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
611 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
612
613 WHEN OTHERS THEN
614 pa_debug.debug('Error Procedure Name := PA_PURGE_ICIP.PA_DraftInvDetails' );
615 pa_debug.debug('Error stage is '||x_err_stage );
616 pa_debug.debug('Error stack is '||x_err_stack );
617 pa_debug.debug(SQLERRM);
618
619 /* ATG Changes */
620 x_err_stack := l_old_err_stack ;
621
622 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
623
624 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
625
626 end PA_DraftInvDetails ;
627
628
629 -- Start of comments
630 -- API name : PA_MC_DraftInvoiceDetails
631 -- Type : Private
632 -- Pre-reqs : None
633 -- Function : Archive and Purge data for table pa_mc_draft_inv_details_all
634 -- Parameters : See common list above
635 -- End of comments
636
637 procedure PA_MC_DraftInvoiceDetails(
638 p_purge_batch_id IN NUMBER,
639 p_project_id IN NUMBER,
640 p_txn_to_date IN DATE,
641 p_purge_release IN VARCHAR2,
642 p_archive_flag IN VARCHAR2,
643 p_commit_size IN NUMBER,
644 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
645 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
646 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
647 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
648 IS
649
650 l_old_err_stage VARCHAR2(2000);
651 l_old_err_stack VARCHAR2(2000);
652
653 begin
654
655 l_old_err_stack := x_err_stack;
656
657 x_err_stack := x_err_stack || ' ->Entering PA_MC_DraftInvoiceDetails ';
658
659 pa_debug.debug(x_err_stack);
660
661 pa_debug.debug( ' ->Before insert into PA_MC_DRAFT_INV_DETS_AR') ;
662
663 /* Commented out for MRC migration to SLA insert into PA_MC_DRAFT_INV_DETS_AR
664 (
665 PURGE_BATCH_ID,
666 PURGE_RELEASE,
667 PURGE_PROJECT_ID,
668 SET_OF_BOOKS_ID,
669 DRAFT_INVOICE_DETAIL_ID,
670 PROJECT_ID,
671 INVOICED_FLAG,
672 ACCT_CURRENCY_CODE,
673 BILL_AMOUNT,
674 REQUEST_ID,
675 ACCT_RATE_TYPE,
676 ACCT_RATE_DATE,
677 ACCT_EXCHANGE_RATE,
678 PROGRAM_APPLICATION_ID,
679 PROGRAM_ID,
680 PROGRAM_UPDATE_DATE
681 )
682 select
683 p_purge_batch_id,
684 p_purge_release,
685 p_project_id,
686 mc.SET_OF_BOOKS_ID,
687 mc.DRAFT_INVOICE_DETAIL_ID,
688 mc.PROJECT_ID,
689 mc.INVOICED_FLAG,
690 mc.ACCT_CURRENCY_CODE,
691 mc.BILL_AMOUNT,
692 mc.REQUEST_ID,
693 mc.ACCT_RATE_TYPE,
694 mc.ACCT_RATE_DATE,
695 mc.ACCT_EXCHANGE_RATE,
696 mc.PROGRAM_APPLICATION_ID,
697 mc.PROGRAM_ID,
698 mc.PROGRAM_UPDATE_DATE
699 from pa_mc_draft_inv_details_all mc,
700 pa_draft_inv_dets_ar ar
701 where ar.purge_project_id = p_project_id
702 and mc.draft_invoice_detail_id = ar.draft_invoice_detail_id; */
703
704 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
705
706 pa_debug.debug( ' ->After insert into PA_MC_DRAFT_INV_DETS_AR') ;
707 x_err_stack := l_old_err_stack ;
708
709 EXCEPTION
710 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
711 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
712
713 WHEN OTHERS THEN
714 pa_debug.debug('Error Procedure Name := PA_PURGE_ICIP.PA_MC_DraftInvoiceDetails' );
715 pa_debug.debug('Error stage is '||x_err_stage );
716 pa_debug.debug('Error stack is '||x_err_stack );
717 pa_debug.debug(SQLERRM);
718 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
719
720 /* ATG Changes */
721 x_err_stack := l_old_err_stack ;
722 x_MRC_NoOfRecordsIns := null;
723
724 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
725
726 end PA_MC_DraftInvoiceDetails;
727
728 END pa_purge_icip;
[Home] [Help]