[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_COSTING
Source
1 package body pa_purge_costing as
2 /* $Header: PAXCSPRB.pls 120.4 2005/08/03 14:38:31 aaggarwa noship $ */
3
4 -- forward declarations
5
6 l_commit_size NUMBER ;
7 p_active_flag VARCHAR2(1);
8 g_user NUMBER ;
9 l_mrc_flag VARCHAR2(1) := paapimp_pkg.get_mrc_flag;
10
11 -- Start of comments
12 -- API name : Pa_Costing_Main_Purge
13 -- Type : Public
14 -- Pre-reqs : None
15 -- Function : This procedure is the main purge procedure for costing
16 -- tables. This procedure calls a procedure that purges
17 -- each of the individual tables.
18 --
19 -- Parameters : p_batch_id IN NUMBER
20 -- The purge batch id for which rows have
21 -- to be purged/archived.
22 -- p_project_Id IN NUMBER,
23 -- The project id for which records have
24 -- to be purged/archived.
25 -- p_txn_to_date IN DATE,
26 -- If the purging is being done on projects
27 -- that are active then this parameter is
28 -- determine the date to which the transactions
29 -- need to be purged.
30 -- p_Commit_Size IN NUMBER,
31 -- The number of records that can be allowed to
32 -- remain uncommited. If the number of records
33 -- goes byond this number then the process is
34 -- commited.
35 -- p_Archive_Flag IN OUT VARCHAR2,
36 -- This flag determines if the records need to
37 -- be archived before they are purged
38 -- p_Purge_Release IN OUT VARCHAR2,
39 -- The version of the application on which the
40 -- purge process is run.
41 -- X_Err_Stack IN OUT VARCHAR2,
42 -- Error stack
43 -- X_Err_Stage IN OUT VARCHAR2,
44 -- Stage in the procedure where error occurred
45 -- X_Err_Code IN OUT NUMBER
46 -- Error code returned from the procedure
47 -- End of comments
48
49 procedure pa_costing_main_purge ( p_purge_batch_id in NUMBER,
50 p_project_id in NUMBER,
51 p_purge_release in VARCHAR2,
52 p_txn_to_date in DATE,
53 p_archive_flag in VARCHAR2,
54 p_commit_size in NUMBER,
55 x_err_stack in OUT NOCOPY VARCHAR2,
56 x_err_stage in OUT NOCOPY VARCHAR2,
57 x_err_code in OUT NOCOPY NUMBER ) is
58
59 l_old_err_stack VARCHAR2(2000);
60 l_err_stage VARCHAR2(500);
61 l_no_records_del NUMBER ;
62 l_no_records_ins NUMBER ;
63
64 BEGIN
65 l_old_err_stack := x_err_stack;
66
67 X_err_stack := 'Batch Id: '||p_purge_batch_id || 'Project Id: '||p_project_id ;
68 x_err_stack := x_err_stack || ' ->Before call to purge the data ';
69 g_user := FND_PROFILE.VALUE('USER_ID') ;
70
71 g_user := -1 ;
72 -- Call the procedure to delete CDLs
73
74 pa_debug.debug('*-> About to purge CDLs ') ;
75 x_err_stage := 'About to purge CDLs for project '||to_char(p_project_id) ;
76 pa_purge_costing.PA_CostDistLines(p_purge_batch_id => p_purge_batch_id,
77 p_project_id => p_project_id,
78 p_txn_to_date => p_txn_to_date ,
79 p_purge_release => p_purge_release,
80 p_archive_flag => p_archive_flag,
81 p_commit_size => p_commit_size,
82 x_err_code => x_err_code,
83 x_err_stack => x_err_stack,
84 x_err_stage => x_err_stage
85 ) ;
86
87 pa_debug.debug('*-> About to purge CCDIST lines ') ;
88 x_err_stage := 'About to purge CCDIST lines for project '||to_char(p_project_id) ;
89 pa_purge_costing.PA_CcDistLines(p_purge_batch_id => p_purge_batch_id,
90 p_project_id => p_project_id,
91 p_txn_to_date => p_txn_to_date ,
92 p_purge_release => p_purge_release,
93 p_archive_flag => p_archive_flag,
94 p_commit_size => p_commit_size,
95 x_err_code => x_err_code,
96 x_err_stack => x_err_stack,
97 x_err_stage => x_err_stage
98 ) ;
99
100 pa_debug.debug('*-> About to purge expenditure comments ') ;
101 x_err_stage := 'About to purge expenditure comments for project '||to_char(p_project_id) ;
102 pa_purge_costing.PA_ExpenditureComments(p_purge_batch_id => p_purge_batch_id,
103 p_project_id => p_project_id,
104 p_txn_to_date => p_txn_to_date ,
105 p_purge_release => p_purge_release,
106 p_archive_flag => p_archive_flag,
107 p_commit_size => p_commit_size,
108 x_err_code => x_err_code,
109 x_err_stack => x_err_stack,
110 x_err_stage => x_err_stage
111 ) ;
112
113 pa_debug.debug('*-> About ot purge expenditure adj acts ') ;
114 x_err_stage := 'About to purge audit record for project '||to_char(p_project_id) ;
115 pa_purge_costing.PA_ExpendItemAdjActivities(p_purge_batch_id => p_purge_batch_id,
116 p_project_id => p_project_id,
117 p_txn_to_date => p_txn_to_date ,
118 p_purge_release => p_purge_release,
119 p_archive_flag => p_archive_flag,
120 p_commit_size => p_commit_size,
121 x_err_code => x_err_code,
122 x_err_stack => x_err_stack,
123 x_err_stage => x_err_stage
124 ) ;
125
126 pa_debug.debug('*-> About to purge records from the denorm table');
127 x_err_stage := 'About to purge records from denorm table for project '||to_char(p_project_id) ;
128 pa_purge_costing.PA_EiDenorm(p_purge_batch_id => p_purge_batch_id,
129 p_project_id => p_project_id,
130 p_txn_to_date => p_txn_to_date ,
131 p_purge_release => p_purge_release,
132 p_archive_flag => p_archive_flag,
133 p_commit_size => p_commit_size,
134 x_err_code => x_err_code,
135 x_err_stack => x_err_stack,
136 x_err_stage => x_err_stage
137 ) ;
138
139 pa_debug.debug('*-> About to purge expenditure items that were transferred to another item');
140 x_err_stage := 'About to purge exp items transferred to another item for project '||to_char(p_project_id) ;
141 /*
142 pa_purge_costing.PA_ExpItemsSrcPurge() ;
143
144 */
145 pa_debug.debug('*-> About to purge expenditure items that were transferred from another item ');
146 x_err_stage := 'About to purge exp items transferred from another item for project '||to_char(p_project_id) ;
147 pa_purge_costing.PA_ExpItemsDestPurge(p_purge_batch_id => p_purge_batch_id,
148 p_project_id => p_project_id,
149 p_txn_to_date => p_txn_to_date ,
150 p_purge_release => p_purge_release,
151 p_archive_flag => p_archive_flag,
152 p_commit_size => p_commit_size,
153 x_err_code => x_err_code,
154 x_err_stack => x_err_stack,
155 x_err_stage => x_err_stage
156 ) ;
157
158
159 pa_debug.debug('*-> About to purge expenditure items ');
160 x_err_stage := 'About to purge expenditure items of project '||to_char(p_project_id);
161 pa_purge_costing.PA_ExpenditureItems(p_purge_batch_id => p_purge_batch_id,
162 p_project_id => p_project_id,
163 p_txn_to_date => p_txn_to_date ,
164 p_purge_release => p_purge_release,
165 p_archive_flag => p_archive_flag,
166 p_commit_size => p_commit_size,
167 x_err_code => x_err_code,
168 x_err_stack => x_err_stack,
169 x_err_stage => x_err_stage
170 ) ;
171
172 pa_debug.debug('*-> About to purge expenditure history records of expenditures ');
173 x_err_stage := 'About to purge expenditure history records of expenditures without any exp items ';
174 pa_purge_costing.PA_ExpenditureHistory(p_purge_batch_id => p_purge_batch_id,
175 p_project_id => p_project_id,
176 p_txn_to_date => p_txn_to_date ,
177 p_purge_release => p_purge_release,
178 p_archive_flag => p_archive_flag,
179 p_commit_size => p_commit_size,
180 x_err_code => x_err_code,
181 x_err_stack => x_err_stack,
182 x_err_stage => x_err_stage
183 ) ;
184
185 /* */
186 x_err_stack := l_old_err_stack; -- Added for bug 4227589
187 EXCEPTION
188 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
189 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
190
191 WHEN OTHERS THEN
192 x_err_stage := l_err_stage ;
193 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_COSTING_MAIN_PURGE' );
194 pa_debug.debug('Error stage is '||x_err_stage );
195 pa_debug.debug('Error stack is '||x_err_stack );
196 pa_debug.debug(SQLERRM);
197 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
198
199 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
200
201 END pa_costing_main_purge ;
202
203 -- Start of comments
204 -- API name : PA_CostDistLines
205 -- Type : Public
206 -- Pre-reqs : None
207 -- Function : This procedure purges all the cost distribution lines
208 --
209 -- Parameters : Refer to the comments of the previous procedure
210 --
211 -- End of comments
212
213 procedure PA_CostDistLines ( p_purge_batch_id IN NUMBER,
214 p_project_id IN NUMBER,
215 p_txn_to_date IN DATE,
216 p_purge_release IN VARCHAR2,
217 p_archive_flag IN VARCHAR2,
218 p_commit_size IN NUMBER,
219 x_err_code IN OUT NOCOPY NUMBER,
220 x_err_stack IN OUT NOCOPY VARCHAR2,
221 x_err_stage IN OUT NOCOPY VARCHAR2
222 ) is
223
224 l_old_err_stage VARCHAR2(2000);
225 l_old_err_stack VARCHAR2(2000);
226 l_NoOfRecordsIns NUMBER;
227 l_NoOfRecordsDel NUMBER;
228 l_MRC_NoOfRecordsDel NUMBER;
229 x_MRC_NoOfRecordsIns NUMBER;
230 l_commit_size NUMBER;
231 l_cdl_rowid_tab PA_PLSQL_DATATYPES.RowIdTabTyp;
232 l_cdl_rowid_tab_empty PA_PLSQL_DATATYPES.RowIdTabTyp;
233 exp_ind NUMBER;
234 l_fetch_complete BOOLEAN := FALSE;
235 cursor cdl_open_projects is
236 select cdl.rowid
237 from pa_cost_distribution_lines_all cdl,
238 pa_expenditure_items_all ei
239 where cdl.expenditure_item_id = ei.expenditure_item_id
240 and ei.expenditure_item_date <= p_txn_to_date
241 and ei.project_id = p_project_id;
242
243 cursor cdl_close_projects is
244 select cdl.rowid
245 from pa_cost_distribution_lines_all cdl
246 where cdl.project_id = p_project_id;
247
248 begin
249
250 l_old_err_stack := x_err_stack;
251 x_err_stack := x_err_stack || ' ->Before insert into Cost_Distribution_Lines_AR' ;
252
253 /* If mrc is enabled and being used then set the commit size based on the number
254 * of reporting currencies using PA_UTILS2.ARPUR_MRC_Commit_Size.
255 * Otherwise just set the commit using PA_UTILS2.ARPUR_Commit_Size.
256 */
257 IF (l_mrc_flag = 'Y') THEN
258 l_commit_size := PA_UTILS2.ARPUR_MRC_Commit_Size;
259 ELSE
260 l_commit_size := PA_UTILS2.ARPUR_Commit_Size;
261 END IF;
262 IF p_txn_to_date is not null THEN
263 OPEN cdl_open_projects;
264 ELSE
265 OPEN cdl_close_projects;
266 END IF;
267 LOOP
268 l_NoOfRecordsIns := 0;
269 l_NoOfRecordsDel := 0;
270 l_cdl_rowid_tab := l_cdl_rowid_tab_empty;
271
272 IF p_txn_to_date is not null THEN
273
274 FETCH cdl_open_projects BULK COLLECT INTO l_cdl_rowid_tab LIMIT l_commit_size;
275 IF cdl_open_projects%NOTFOUND THEN
276 CLOSE cdl_open_projects;
277 l_fetch_complete := TRUE;
278 END IF;
279 ELSE
280 FETCH cdl_close_projects BULK COLLECT INTO l_cdl_rowid_tab LIMIT l_commit_size;
281 IF cdl_close_projects%NOTFOUND THEN
282 CLOSE cdl_close_projects;
283 l_fetch_complete := TRUE;
284 END IF;
285 END IF;
286 if l_cdl_rowid_tab.last is not null then
287 if p_archive_flag = 'Y' then
288 x_err_stage := 'Before insert into Cost_Distribution_Lines_AR' ;
289 FORALL exp_ind IN l_cdl_rowid_tab.FIRST .. l_cdl_rowid_tab.LAST
290 insert into PA_COST_DIST_LINES_AR
291 (
292 project_id,
293 task_id,
294 denom_currency_code,
295 denom_raw_cost,
296 denom_burdened_cost,
297 acct_currency_code,
298 acct_rate_date,
299 acct_rate_type,
300 acct_exchange_rate,
301 acct_raw_cost,
302 acct_burdened_cost,
303 project_currency_code,
304 project_rate_date,
305 project_rate_type,
306 project_exchange_rate,
307 prc_generated_flag,
308 recvr_pa_date,
309 recvr_gl_date,
310 util_summarized_flag,
311 liquidate_encum_flag,
312 encumbrance_batch_name,
313 encumbrance_type_id,
314 encum_transfer_rej_reason,
315 budget_ccid,
316 encumbrance_amount,
317 projfunc_cost_exchange_rate,
318 project_raw_cost,
319 project_burdened_cost,
320 work_type_id,
321 gl_period_name,
322 recvr_gl_period_name,
323 pa_period_name,
324 projfunc_cost_rate_type,
325 projfunc_cost_rate_date,
326 recvr_pa_period_name,
327 projfunc_currency_code,
328 system_reference4,
329 pji_summarized_flag,
330 ind_compiled_set_id,
331 line_type,
332 burdened_cost,
333 resource_accumulated_flag,
334 org_id,
335 function_transaction_code,
336 code_combination_id,
337 expenditure_item_id,
338 line_num,
339 creation_date,
340 created_by,
341 transfer_status_code,
342 amount,
343 quantity,
344 billable_flag,
345 request_id,
346 program_application_id,
347 program_id,
348 program_update_date,
349 pa_date,
350 dr_code_combination_id,
351 gl_date,
352 transferred_date,
353 transfer_rejection_reason,
354 batch_name,
355 accumulated_flag,
356 reversed_flag,
357 line_num_reversed,
358 system_reference1,
359 system_reference2,
360 system_reference3,
361 cr_code_combination_id,
362 burden_sum_rejection_code,
363 burden_sum_source_run_id,
364 purge_batch_id,
365 purge_release,
366 purge_project_id
367 ,cost_rate_sch_id
368 ,org_labor_sch_rule_id
369 ,denom_burdened_change
370 ,project_burdened_change
371 ,projfunc_burdened_change
372 ,acct_burdened_change
373 ,parent_line_num
374 ,prev_ind_compiled_set_id
375 ,si_assets_addition_flag -- R12 change
376 ,system_reference5 -- R12 change
377 ,acct_event_id -- R12 change
378 ,acct_source_code -- R12 change
379 )
380 select cdl.project_id,
381 cdl.task_id,
382 cdl.denom_currency_code,
383 cdl.denom_raw_cost,
384 cdl.denom_burdened_cost,
385 cdl.acct_currency_code,
386 cdl.acct_rate_date,
387 cdl.acct_rate_type,
388 cdl.acct_exchange_rate,
389 cdl.acct_raw_cost,
390 cdl.acct_burdened_cost,
391 cdl.project_currency_code,
392 cdl.project_rate_date,
393 cdl.project_rate_type,
394 cdl.project_exchange_rate,
395 cdl.prc_generated_flag,
396 cdl.recvr_pa_date,
397 cdl.recvr_gl_date,
398 cdl.util_summarized_flag,
399 cdl.liquidate_encum_flag,
400 cdl.encumbrance_batch_name,
401 cdl.encumbrance_type_id,
402 cdl.encum_transfer_rej_reason,
403 cdl.budget_ccid,
404 cdl.encumbrance_amount,
405 cdl.projfunc_cost_exchange_rate,
406 cdl.project_raw_cost,
407 cdl.project_burdened_cost,
408 cdl.work_type_id,
409 cdl.gl_period_name,
410 cdl.recvr_gl_period_name,
411 cdl.pa_period_name,
412 cdl.projfunc_cost_rate_type,
413 cdl.projfunc_cost_rate_date,
414 cdl.recvr_pa_period_name,
415 cdl.projfunc_currency_code,
416 cdl.system_reference4,
417 cdl.pji_summarized_flag,
418 cdl.ind_compiled_set_id,
419 cdl.line_type,
420 cdl.burdened_cost,
421 cdl.resource_accumulated_flag,
422 cdl.org_id,
423 cdl.function_transaction_code,
424 cdl.code_combination_id,
425 cdl.expenditure_item_id,
426 cdl.line_num,
427 cdl.creation_date,
428 cdl.created_by,
429 cdl.transfer_status_code,
430 cdl.amount,
431 cdl.quantity,
432 cdl.billable_flag,
433 cdl.request_id,
434 cdl.program_application_id,
435 cdl.program_id,
436 cdl.program_update_date,
437 cdl.pa_date,
438 cdl.dr_code_combination_id,
439 cdl.gl_date,
440 cdl.transferred_date,
441 cdl.transfer_rejection_reason,
442 cdl.batch_name,
443 cdl.accumulated_flag,
444 cdl.reversed_flag,
445 cdl.line_num_reversed,
446 cdl.system_reference1,
447 cdl.system_reference2,
448 cdl.system_reference3,
449 cdl.cr_code_combination_id,
450 cdl.burden_sum_rejection_code,
451 cdl.burden_sum_source_run_id,
452 p_purge_batch_id,
453 p_purge_release,
454 p_project_id
455 ,cdl.cost_rate_sch_id
456 ,cdl.org_labor_sch_rule_id
457 ,cdl.denom_burdened_change
458 ,cdl.project_burdened_change
459 ,cdl.projfunc_burdened_change
460 ,cdl.acct_burdened_change
461 ,cdl.parent_line_num
462 ,cdl.prev_ind_compiled_set_id
463 ,cdl.si_assets_addition_flag -- R12 change
464 ,cdl.system_reference5 -- R12 change
465 ,cdl.acct_event_id -- R12 change
466 ,cdl.acct_source_code -- R12 change
467 from pa_cost_distribution_lines_all cdl
468 where cdl.rowid = l_cdl_rowid_tab(exp_ind);
469
470 l_NoOfRecordsIns := SQL%ROWCOUNT ;
471
472 end if;
473
474
475 /* Commented for the bug#2405916 and moved this to inside the if SQL%ROWCOUNT > 0 condition */
476 /* */
477
478 if l_NoOfRecordsIns > 0 then
479 -- We have a seperate delete statement if the archive option is
480 -- selected because if archive option is selected the the records
481 -- being purged will be those records which are already archived.
482 -- table and
483
484 IF (l_mrc_flag = 'Y') THEN
485 pa_purge_costing.PA_MRCCostDistLines(
486 p_purge_batch_id,
487 p_project_id,
488 p_txn_to_date,
489 p_purge_release,
490 p_archive_flag,
491 l_commit_size,
492 x_err_code,
493 x_err_stack,
494 x_err_stage,
495 x_MRC_NoOfRecordsIns);
496 END IF;
497 END IF;
498 /* Each time thru the loop need to make sure that reset the
499 * counter tracking the number of records that deleted from
500 * the mrc table.
501 */
502 IF (l_mrc_flag = 'Y') THEN
503 pa_utils2.MRC_row_count := 0;
504 END IF;
505 x_err_stage := 'Before deleting records from pa_cost_distribution_lines_all' ;
506 FORALL exp_ind in l_cdl_rowid_tab.FIRST .. l_cdl_rowid_tab.LAST
507 DELETE FROM PA_COST_DISTRIBUTION_LINES_ALL cdl
508 WHERE CDL.rowid = l_cdl_rowid_tab(exp_ind);
509
510 l_NoOfRecordsDel := SQL%ROWCOUNT;
511 l_MRC_NoOfRecordsDel := pa_utils2.MRC_row_count ;
512
513 IF l_NoOfRecordsDel > 0 THEN
514 -- After "deleting" or "deleting and inserting" a set of records
515 -- the transaction is commited. This also creates a record in the
516 -- Pa_Purge_Project_details which will show the no. of records
517 -- that are purged from each table.
518
519 x_err_stage := 'PA_CostDistLines: Commiting the transaction' ;
520 pa_purge.CommitProcess(p_purge_batch_id,
521 p_project_id,
522 'PA_COST_DISTRIBUTION_LINES',
523 l_NoOfRecordsIns,
524 l_NoOfRecordsDel,
525 x_err_code,
526 x_err_stack,
527 x_err_stage,
528 /* 'PA_MC_CDL_AR', */
529 'PA_MC_COST_DIST_LINES',
530 x_MRC_NoOfRecordsIns,
531 l_MRC_NoOfRecordsDel
532 ) ;
533 end if ;
534 end if;
535 IF (l_fetch_complete ) THEN
536 EXIT;
537 END IF;
538 END LOOP ;
539
540
541 x_err_stack := l_old_err_stack ;
542
543 EXCEPTION
544 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
545 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
546
547 WHEN OTHERS THEN
548 -- x_err_stage := l_err_stage ;
549 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_COSTDISTLINES' );
550 pa_debug.debug('Error stage is '||x_err_stage );
551 pa_debug.debug('Error stack is '||x_err_stack );
552 pa_debug.debug(SQLERRM);
553 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
554
555 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
556
557 end PA_CostDistLines ;
558
559
560 -- Start of comments
561 -- API name : PA_CcDistLines
562 -- Type : Public
563 -- Pre-reqs : None
564 -- Function : This procedure purges all the cc distribution lines
565 --
566 -- Parameters : Refer to the comments of the previous procedure
567 --
568 -- End of comments
569
570 procedure PA_CcDistLines ( p_purge_batch_id IN NUMBER,
571 p_project_id IN NUMBER,
572 p_txn_to_date IN DATE,
573 p_purge_release IN VARCHAR2,
574 p_archive_flag IN VARCHAR2,
575 p_commit_size IN NUMBER,
576 x_err_code IN OUT NOCOPY NUMBER,
577 x_err_stack IN OUT NOCOPY VARCHAR2,
578 x_err_stage IN OUT NOCOPY VARCHAR2
579 ) is
580
581 l_old_err_stage VARCHAR2(2000);
582 l_old_err_stack VARCHAR2(2000);
583 l_NoOfRecordsIns NUMBER;
584 l_NoOfRecordsDel NUMBER;
585 l_MRC_NoOfRecordsDel NUMBER;
586 x_MRC_NoOfRecordsIns NUMBER;
587 l_commit_size NUMBER;
588 /*performance changes starts*/
589 l_cc_dist_rowid_tab PA_PLSQL_DATATYPES.RowIdTabTyp;
590 l_cc_dist_rowid_tab_empty PA_PLSQL_DATATYPES.RowIdTabTyp;
591 exp_ind NUMBER;
592 l_fetch_complete BOOLEAN := FALSE;
593
594 cursor c_open_cc_lines is
595 select cdl1.rowid
596 from pa_expenditure_items_all ei,
597 pa_cc_dist_lines_all cdl1
598 where cdl1.expenditure_item_id = ei.expenditure_item_id
599 and ei.expenditure_item_date <= p_txn_to_date
600 and ei.project_id = p_project_id;
601
602 cursor c_close_cc_lines is
603 select cdl1.rowid
604 from pa_cc_dist_lines_all cdl1
605 where cdl1.project_id = p_project_id;
606
607 begin
608
609 l_old_err_stack := x_err_stack;
610
611 x_err_stack := x_err_stack || ' ->Before insert into Cost_Distribution_Lines_AR' ;
612
613 /* If mrc is enabled and being used then set the commit size based on the number
614 * of reporting currencies using PA_UTILS2.ARPUR_MRC_Commit_Size.
615 * Otherwise just set the commit using PA_UTILS2.ARPUR_Commit_Size.
616 */
617 IF (l_mrc_flag = 'Y') THEN
618 l_commit_size := PA_UTILS2.ARPUR_MRC_Commit_Size;
619 ELSE
620 l_commit_size := PA_UTILS2.ARPUR_Commit_Size;
621 END IF;
622
623 If p_txn_to_date is NOT NULL then
624 Open c_open_cc_lines;
625 Else
626 Open c_close_cc_lines;
627 End If;
628
629 LOOP
630 l_NoOfRecordsIns := 0;
631 l_NoOfRecordsDel := 0;
632 l_cc_dist_rowid_tab:= l_cc_dist_rowid_tab_empty;
633
634 IF p_txn_to_date is not null THEN
635
636 FETCH c_open_cc_lines BULK COLLECT INTO l_cc_dist_rowid_tab LIMIT l_commit_size;
637 IF c_open_cc_lines%NOTFOUND THEN
638 CLOSE c_open_cc_lines;
639 l_fetch_complete := TRUE;
640 END IF;
641 ELSE
642 FETCH c_close_cc_lines BULK COLLECT INTO l_cc_dist_rowid_tab LIMIT l_commit_size;
643 IF c_close_cc_lines%NOTFOUND THEN
644 CLOSE c_close_cc_lines;
645 l_fetch_complete := TRUE;
646 END IF;
647 END IF;
648 If l_cc_dist_rowid_tab.last is not null Then
649 if p_archive_flag = 'Y' then
650 x_err_stage := 'Before insert into Cc_Dist_Lines_AR' ;
651 FORALL exp_ind In l_cc_dist_rowid_tab.FIRST .. l_cc_dist_rowid_tab.LAST
652 insert into PA_Cc_Dist_Lines_AR
653 ( PURGE_BATCH_ID,
654 PURGE_RELEASE,
655 PURGE_PROJECT_ID,
656 CC_DIST_LINE_ID,
657 EXPENDITURE_ITEM_ID,
658 LINE_NUM,
659 LINE_TYPE,
660 CROSS_CHARGE_CODE,
661 ACCT_CURRENCY_CODE,
662 AMOUNT,
663 PROJECT_ID,
664 TASK_ID,
665 REQUEST_ID,
666 LAST_UPDATE_DATE,
667 LAST_UPDATED_BY,
668 CREATION_DATE,
669 CREATED_BY,
670 LAST_UPDATE_LOGIN,
671 ORG_ID,
672 LINE_NUM_REVERSED,
673 DIST_LINE_ID_REVERSED,
674 REVERSED_FLAG,
675 DENOM_TP_CURRENCY_CODE,
676 DENOM_TRANSFER_PRICE,
677 ACCT_TP_RATE_TYPE,
678 ACCT_TP_RATE_DATE,
679 ACCT_TP_EXCHANGE_RATE,
680 DR_CODE_COMBINATION_ID,
681 CR_CODE_COMBINATION_ID,
682 PA_DATE,
683 GL_DATE,
684 GL_BATCH_NAME,
685 TRANSFER_STATUS_CODE,
686 TRANSFERRED_DATE,
687 TRANSFER_REJECTION_CODE,
688 MARKUP_CALC_BASE_CODE,
689 IND_COMPILED_SET_ID,
690 BILL_RATE,
691 TP_BASE_AMOUNT,
692 BILL_MARKUP_PERCENTAGE,
693 SCHEDULE_LINE_PERCENTAGE,
694 RULE_PERCENTAGE,
695 REFERENCE_1,
696 REFERENCE_2,
697 PROGRAM_APPLICATION_ID,
698 PROGRAM_ID,
699 PROGRAM_UPDATE_DATE,
700 REFERENCE_3,
701 TP_JOB_ID,
702 PROJFUNC_TP_EXCHANGE_RATE,
703 PROJFUNC_TRANSFER_PRICE,
704 TP_AMT_TYPE_CODE,
705 PROJFUNC_TP_RATE_TYPE,
706 PROJFUNC_TP_RATE_DATE,
707 GL_PERIOD_NAME,
708 PA_PERIOD_NAME,
709 PROJECT_TP_CURRENCY_CODE,
710 PROJECT_TP_RATE_DATE,
711 PROJECT_TP_RATE_TYPE,
712 PROJECT_TP_EXCHANGE_RATE,
713 PROJECT_TRANSFER_PRICE,
714 PROJFUNC_TP_CURRENCY_CODE,
715 ACCT_EVENT_ID -- R12 change
716 )
717 select p_purge_batch_id,
718 p_purge_release,
719 p_project_id,
720 cdl.CC_DIST_LINE_ID,
721 cdl.EXPENDITURE_ITEM_ID,
722 cdl.LINE_NUM,
723 cdl.LINE_TYPE,
724 cdl.CROSS_CHARGE_CODE,
725 cdl.ACCT_CURRENCY_CODE,
726 cdl.AMOUNT,
727 cdl.PROJECT_ID,
728 cdl.TASK_ID,
729 cdl.REQUEST_ID,
730 cdl.LAST_UPDATE_DATE,
731 cdl.LAST_UPDATED_BY,
732 cdl.CREATION_DATE,
733 cdl.CREATED_BY,
734 cdl.LAST_UPDATE_LOGIN,
735 cdl.ORG_ID,
736 cdl.LINE_NUM_REVERSED,
737 cdl.DIST_LINE_ID_REVERSED,
738 cdl.REVERSED_FLAG,
739 cdl.DENOM_TP_CURRENCY_CODE,
740 cdl.DENOM_TRANSFER_PRICE,
741 cdl.ACCT_TP_RATE_TYPE,
742 cdl.ACCT_TP_RATE_DATE,
743 cdl.ACCT_TP_EXCHANGE_RATE,
744 cdl.DR_CODE_COMBINATION_ID,
745 cdl.CR_CODE_COMBINATION_ID,
746 cdl.PA_DATE,
747 cdl.GL_DATE,
748 cdl.GL_BATCH_NAME,
749 cdl.TRANSFER_STATUS_CODE,
750 cdl.TRANSFERRED_DATE,
751 cdl.TRANSFER_REJECTION_CODE,
752 cdl.MARKUP_CALC_BASE_CODE,
753 cdl.IND_COMPILED_SET_ID,
754 cdl.BILL_RATE,
755 cdl.TP_BASE_AMOUNT,
756 cdl.BILL_MARKUP_PERCENTAGE,
757 cdl.SCHEDULE_LINE_PERCENTAGE,
758 cdl.RULE_PERCENTAGE,
759 cdl.REFERENCE_1,
760 cdl.REFERENCE_2,
761 cdl.PROGRAM_APPLICATION_ID,
762 cdl.PROGRAM_ID,
763 cdl.PROGRAM_UPDATE_DATE,
764 cdl.REFERENCE_3,
765 cdl.TP_JOB_ID,
766 cdl.PROJFUNC_TP_EXCHANGE_RATE,
767 cdl.PROJFUNC_TRANSFER_PRICE,
768 cdl.TP_AMT_TYPE_CODE,
769 cdl.PROJFUNC_TP_RATE_TYPE,
770 cdl.PROJFUNC_TP_RATE_DATE,
771 cdl.GL_PERIOD_NAME,
772 cdl.PA_PERIOD_NAME,
773 cdl.PROJECT_TP_CURRENCY_CODE,
774 cdl.PROJECT_TP_RATE_DATE,
775 cdl.PROJECT_TP_RATE_TYPE,
776 cdl.PROJECT_TP_EXCHANGE_RATE,
777 cdl.PROJECT_TRANSFER_PRICE,
778 cdl.PROJFUNC_TP_CURRENCY_CODE,
779 cdl.ACCT_EVENT_ID -- R12 change
780 from pa_cc_dist_lines_all cdl
781 where cdl.rowid = l_cc_dist_rowid_tab(exp_ind);
782
783 l_NoOfRecordsIns := SQL%ROWCOUNT ;
784 end if;
785
786
787 if l_NoOfRecordsIns > 0 then
788 -- We have a seperate delete statement if the archive option is
789 -- selected because if archive option is selected the the records
790 -- being purged will be those records which are already archived.
791 -- table and
792
793 IF (l_mrc_flag = 'Y') THEN
794 pa_purge_costing.PA_MRCCcDistLines(
795 p_purge_batch_id,
796 p_project_id,
797 p_txn_to_date,
798 p_purge_release,
799 p_archive_flag,
800 l_commit_size,
801 x_err_code,
802 x_err_stack,
803 x_err_stage,
804 x_MRC_NoOfRecordsIns);
805 END IF;
806 END IF;
807
808 /* Each time thru the loop need to make sure that reset the
809 * counter tracking the number of records that deleted from
810 * the mrc table.
811 */
812 IF (l_mrc_flag = 'Y') THEN
813 pa_utils2.MRC_row_count := 0;
814 END IF;
815
816 If ( p_archive_flag = 'Y' and x_MRC_NoOfRecordsIns > 0 )Then
817 x_err_stage := 'Before deleting records from PA_MC_CC_DIST_LINES_ALL' ;
818 delete from pa_mc_cc_dist_lines_all cdl
819 where (cdl.cc_dist_line_id) in
820 ( select cdar.cc_dist_line_id
821 from pa_mc_cc_dist_lines_ar cdar
822 where cdar.purge_project_id = p_project_id ) ;
823 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT;
824 End if;
825
826 If ( l_mrc_flag = 'Y' and p_archive_flag <> 'Y' )Then
827 delete from pa_mc_cc_dist_lines_all cdl
828 where cdl.rowid in
829 ( select cdl1.rowid
830 from pa_expenditure_items_all ei,
831 pa_mc_cc_dist_lines_all cdl1
832 where cdl1.expenditure_item_id = ei.expenditure_item_id
833 and ei.project_id = p_project_id
834 and rownum < l_commit_size
835 ) ;
836
837 l_MRC_NoOfRecordsDel := SQL%ROWCOUNT ;
838 End If;
839
840 FORALL exp_ind In l_cc_dist_rowid_tab.FIRST .. l_cc_dist_rowid_tab.LAST
841 delete from pa_cc_dist_lines_all cdl
842 where cdl.rowid = l_cc_dist_rowid_tab(exp_ind);
843
844 l_NoOfRecordsDel := SQL%ROWCOUNT ;
845
846
847
848
849 /* if SQL%ROWCOUNT = 0 then
850 -- Once the SqlCount becomes 0, which means that there are
851 -- no more records to be purged then we exit the loop.
852
853 x_err_stage := 'PA_CostDistLines: No more records to archive / purge ' ;
854 -- exit ;
855
856 else */
857 -- After "deleting" or "deleting and inserting" a set of records
858 -- the transaction is commited. This also creates a record in the
859 -- Pa_Purge_Project_details which will show the no. of records
860 -- that are purged from each table.
861
862 x_err_stage := 'PA_CostDistLines: Commiting the transaction' ;
863 If l_NoOfRecordsDel > 0 Then
864 pa_purge.CommitProcess(p_purge_batch_id,
865 p_project_id,
866 'PA_CC_DIST_LINES',
867 l_NoOfRecordsIns,
868 l_NoOfRecordsDel,
869 x_err_code,
870 x_err_stack,
871 x_err_stage,
872 /* 'PA_MC_CC_DIST_LINES_AR', */
873 'PA_MC_CC_DIST_LINES',
874 x_MRC_NoOfRecordsIns,
875 l_MRC_NoOfRecordsDel
876 ) ;
877
878 end if ;
879 End if;
880 IF ( l_fetch_complete ) Then
881 exit;
882 END IF;
883 END LOOP ;
884
885
886 x_err_stack := l_old_err_stack ;
887
888 EXCEPTION
889 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
890 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
891
892 WHEN OTHERS THEN
893 -- x_err_stage := l_err_stage ;
894 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_CCDISTLINES' );
895 pa_debug.debug('Error stage is '||x_err_stage );
896 pa_debug.debug('Error stack is '||x_err_stack );
897 pa_debug.debug(SQLERRM);
898 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
899
900 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
901
902 end PA_CcDistLines ;
903
904
905 -- Start of comments
906 -- API name : PA_ExpenditureComments
907 -- Type : Public
908 -- Pre-reqs : None
909 -- Function : This procedure purges all the expenditure comments
910 --
911 -- Parameters : Refer to the comments of the previous procedure
912 --
913 -- End of comments
914
915 procedure PA_ExpenditureComments ( p_purge_batch_id IN NUMBER,
916 p_project_id IN NUMBER,
917 p_txn_to_date IN DATE,
918 p_purge_release IN VARCHAR2,
919 p_archive_flag IN VARCHAR2,
920 p_commit_size IN NUMBER,
921 x_err_code IN OUT NOCOPY NUMBER,
922 x_err_stack IN OUT NOCOPY VARCHAR2,
923 x_err_stage IN OUT NOCOPY VARCHAR2
924 ) is
925
926 l_old_err_stage VARCHAR2(2000);
927 l_old_err_stack VARCHAR2(2000);
928 l_NoOfRecordsIns NUMBER;
929 l_NoOfRecordsDel NUMBER;
930 /*performance changes starts*/
931 l_commit_size NUMBER;
932 l_exp_comm_rowid_tab PA_PLSQL_DATATYPES.RowIdTabTyp;
933 l_exp_comm_rowid_tab_empty PA_PLSQL_DATATYPES.RowIdTabTyp;
934 exp_ind NUMBER;
935 l_fetch_complete BOOLEAN := FALSE;
936
937 Cursor c_open_exp_comm is
938 Select ec1.rowid
939 From pa_expenditure_items_all ei,
940 pa_expenditure_comments ec1
941 Where ei.expenditure_item_id = ec1.expenditure_item_id
942 And ei.expenditure_item_date <= p_txn_to_date
943 And ei.project_id = p_project_id;
944
945 Cursor c_close_exp_comm is
946 Select ec1.rowid
947 From pa_expenditure_items_all ei,
948 pa_expenditure_comments ec1
949 Where ei.expenditure_item_id = ec1.expenditure_item_id
950 And ei.project_id = p_project_id;
951
952 begin
953
954 l_old_err_stack := x_err_stack;
955 x_err_stack := x_err_stack || ' ->Before insert into PA_EXP_COMMENTS_AR' ;
956
957 if p_archive_flag = 'Y' then
958 l_commit_size := p_commit_size/2 ;
959 else
960 l_commit_size := p_commit_size ;
961 end if;
962
963 If p_txn_to_date is NOT NULL then
964 Open c_open_exp_comm;
965 Else
966 Open c_close_exp_comm;
967 End If;
968
969 LOOP
970 l_NoOfRecordsIns := 0;
971 l_NoOfRecordsDel := 0;
972 l_exp_comm_rowid_tab:= l_exp_comm_rowid_tab_empty;
973
974 IF p_txn_to_date is not null THEN
975
976 FETCH c_open_exp_comm BULK COLLECT INTO l_exp_comm_rowid_tab LIMIT l_commit_size;
977 IF c_open_exp_comm%NOTFOUND THEN
978 CLOSE c_open_exp_comm;
979 l_fetch_complete := TRUE;
980 END IF;
981 ELSE
982 FETCH c_close_exp_comm BULK COLLECT INTO l_exp_comm_rowid_tab LIMIT l_commit_size;
983 IF c_close_exp_comm%NOTFOUND THEN
984 CLOSE c_close_exp_comm;
985 l_fetch_complete := TRUE;
986 END IF;
987 END IF;
988 If l_exp_comm_rowid_tab.last is not null then
989 if p_archive_flag = 'Y' then
990 -- If archive option is selected then the records are
991 -- inserted into the archived into the archive tables
992 -- before being purged. The where condition is such that
993 -- only the it inserts half the no. of records specified
994 -- in the commit size.
995
996 x_err_stage := 'PA_ExpenditureComments: Before inserting records into PA_EXP_COMMENTS_AR';
997
998 FORALL exp_ind IN l_exp_comm_rowid_tab.FIRST .. l_exp_comm_rowid_tab.LAST
999 insert into PA_EXP_COMMENTS_AR
1000 (
1001 expenditure_item_id,
1002 line_number,
1003 last_update_date,
1004 last_updated_by,
1005 creation_date,
1006 created_by,
1007 expenditure_comment,
1008 last_update_login,
1009 request_id,
1010 program_id,
1011 program_application_id,
1012 program_update_date ,
1013 purge_batch_id,
1014 purge_release,
1015 purge_project_id
1016 )
1017 select ec.expenditure_item_id,
1018 ec.line_number,
1019 ec.last_update_date,
1020 ec.last_updated_by,
1021 ec.creation_date,
1022 ec.created_by,
1023 ec.expenditure_comment,
1024 ec.last_update_login,
1025 ec.request_id,
1026 ec.program_id,
1027 ec.program_application_id,
1028 ec.program_update_date,
1029 p_purge_batch_id,
1030 p_purge_release,
1031 p_project_id
1032 from pa_expenditure_comments ec
1033 where ec.rowid = l_exp_comm_rowid_tab(exp_ind);
1034
1035 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1036 end if; /*if p_archive_flag = 'Y' */
1037
1038 FORALL exp_ind in l_exp_comm_rowid_tab.FIRST .. l_exp_comm_rowid_tab.LAST
1039 DELETE FROM pa_expenditure_comments ec
1040 WHERE ec.rowid = l_exp_comm_rowid_tab(exp_ind);
1041
1042 l_NoOfRecordsDel := SQL%ROWCOUNT;
1043 /* commented for performance changes
1044 if SQL%ROWCOUNT > 0 then
1045 -- We have a seperate delete statement if the archive option is
1046 -- selected because if archive option is selected the the records
1047 -- being purged will be those records which are already archived.
1048 -- table and
1049
1050 x_err_stage := 'PA_ExpenditureComments: Before deleting records from pa_expenditure_comments';
1051 delete from pa_expenditure_comments ec
1052 where ( ec.expenditure_item_id, ec.line_number )
1053 in ( select ecar.expenditure_item_id, ecar.line_number
1054 from PA_EXP_COMMENTS_AR ecar
1055 where ecar.purge_project_id = p_project_id
1056 ) ;
1057
1058 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1059
1060 end if;
1061
1062 l_commit_size := p_commit_size ;
1063
1064 -- If the archive option is not selected then the delete will
1065 -- be based on the commit size.
1066
1067 x_err_stage := 'PA_ExpenditureComments: Before deleting records from pa_expenditure_comments' ;
1068 if p_txn_to_date is NOT NULL then
1069 delete from pa_expenditure_comments ec
1070 where ( ec.rowid )
1071 in ( select ec1.rowid
1072 from pa_expenditure_items_all ei,
1073 pa_expenditure_comments ec1
1074 where ei.expenditure_item_id = ec1.expenditure_item_id
1075 and ei.expenditure_item_date <= p_txn_to_date
1076 and ei.project_id = p_project_id
1077 and rownum <= p_commit_size
1078 ) ;
1079 else
1080 delete from pa_expenditure_comments ec
1081 where ( ec.rowid )
1082 in ( select ec1.rowid
1083 from pa_expenditure_items_all ei,
1084 pa_expenditure_comments ec1
1085 where ei.expenditure_item_id = ec1.expenditure_item_id
1086 and ei.project_id = p_project_id
1087 and rownum <= p_commit_size
1088 ) ;
1089 end if;
1090
1091 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1092
1093 end of comment for performance changes*/
1094
1095 /* if SQL%ROWCOUNT = 0 then
1096
1097 -- Once the SqlCount becomes 0, which means that there are
1098 -- no more records to be purged then we exit the loop.
1099
1100 x_err_stage := 'PA_ExpenditureComments: No more records to archive / purge ' ;
1101 exit ;
1102
1103 else */
1104 -- After "deleting" or "deleting and inserting" a set of records
1105 -- the transaction is commited. This also creates a record in the
1106 -- Pa_Purge_Project_details which will show the no. of records
1107 -- that are purged from each table.
1108
1109 x_err_stage := 'PA_ExpenditureComments: Commiting the transaction' ;
1110 if l_NoOfRecordsDel > 0 Then
1111 pa_purge.CommitProcess(p_purge_batch_id,
1112 p_project_id,
1113 'PA_EXPENDITURE_COMMENTS',
1114 l_NoOfRecordsIns,
1115 l_NoOfRecordsDel,
1116 x_err_code,
1117 x_err_stack,
1118 x_err_stage
1119 ) ;
1120 end if;
1121
1122 end if ;
1123 If (l_fetch_complete ) Then
1124 exit;
1125 End If;
1126 END LOOP ;
1127
1128 x_err_stack := l_old_err_stack ;
1129
1130 EXCEPTION
1131 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1132 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1133
1134 WHEN OTHERS THEN
1135 -- x_err_stage := l_err_stage ;
1136 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPENDITURECOMMENTS' );
1137 pa_debug.debug('Error stage is '||x_err_stage );
1138 pa_debug.debug('Error stack is '||x_err_stack );
1139 pa_debug.debug(SQLERRM);
1140 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM;
1141
1142 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1143
1144 end PA_ExpenditureComments ;
1145
1146 -- Start of comments
1147 -- API name : PA_ExpendItemAdjActivities
1148 -- Type : Public
1149 -- Pre-reqs : None
1150 -- Function : This procedure purges all the audit records in the
1151 -- audit table for expenditure items.
1152 --
1153 -- Parameters : Refer to the comments of the previous procedure
1154 --
1155 -- End of comments
1156
1157 procedure PA_ExpendItemAdjActivities ( p_purge_batch_id IN NUMBER,
1158 p_project_id IN NUMBER,
1159 p_txn_to_date IN DATE,
1160 p_purge_release IN VARCHAR2,
1161 p_archive_flag IN VARCHAR2,
1162 p_commit_size IN NUMBER,
1163 x_err_code IN OUT NOCOPY NUMBER,
1164 x_err_stack IN OUT NOCOPY VARCHAR2,
1165 x_err_stage IN OUT NOCOPY VARCHAR2
1166 ) is
1167
1168 l_old_err_stage VARCHAR2(2000);
1169 l_old_err_stack VARCHAR2(2000);
1170 l_NoOfRecordsIns NUMBER;
1171 l_NoOfRecordsDel NUMBER;
1172 l_commit_size NUMBER;
1173 /*performance changes starts*/
1174 l_exp_adj_rowid_tab PA_PLSQL_DATATYPES.RowIdTabTyp;
1175 l_exp_adj_rowid_tab_empty PA_PLSQL_DATATYPES.RowIdTabTyp;
1176 exp_ind NUMBER;
1177 l_fetch_complete BOOLEAN := FALSE;
1178
1179 cursor c_open_exp_adj is
1180 Select eia1.rowid
1181 from pa_expenditure_items_all ei,
1182 pa_expend_item_adj_activities eia1
1183 where ei.expenditure_item_date <= p_txn_to_date
1184 and ei.expenditure_item_id = eia1.expenditure_item_id
1185 and ei.project_id = p_project_id;
1186
1187 cursor c_close_exp_adj is
1188 Select eia1.rowid
1189 from pa_expenditure_items_all ei,
1190 pa_expend_item_adj_activities eia1
1191 where ei.expenditure_item_id = eia1.expenditure_item_id
1192 and ei.project_id = p_project_id;
1193
1194 begin
1195 --s1
1196
1197 -- Added for bug 4227589
1198 l_old_err_stack := x_err_stack;
1199 x_err_stack := x_err_stack || ' ->Before insert into PA_ExpendItemAdjActivities' ;
1200
1201 if p_archive_flag = 'Y' then
1202 l_commit_size := p_commit_size/2 ;
1203 else
1204 l_commit_size := p_commit_size ;
1205 end if;
1206
1207 If p_txn_to_date is NOT NULL then
1208 Open c_open_exp_adj;
1209 Else
1210 Open c_close_exp_adj;
1211 End If;
1212
1213 LOOP
1214 l_NoOfRecordsIns := 0;
1215 l_NoOfRecordsDel := 0;
1216 l_exp_adj_rowid_tab:= l_exp_adj_rowid_tab_empty;
1217
1218 IF p_txn_to_date is not null THEN
1219
1220 FETCH c_open_exp_adj BULK COLLECT INTO l_exp_adj_rowid_tab LIMIT l_commit_size;
1221 IF c_open_exp_adj%NOTFOUND THEN
1222 CLOSE c_open_exp_adj;
1223 l_fetch_complete := TRUE;
1224 END IF;
1225 ELSE
1226 FETCH c_close_exp_adj BULK COLLECT INTO l_exp_adj_rowid_tab LIMIT l_commit_size;
1227 IF c_close_exp_adj%NOTFOUND THEN
1228 CLOSE c_close_exp_adj;
1229 l_fetch_complete := TRUE;
1230 END IF;
1231 END IF;
1232
1233
1234 -- Commented for bug 4227589 and moved outside LOOP
1235 -- l_old_err_stack := x_err_stack;
1236 -- x_err_stack := x_err_stack || ' ->Before insert into PA_EXP_COMMENTS_AR' ;
1237 If l_exp_adj_rowid_tab.last is not null Then
1238 if p_archive_flag = 'Y' then
1239 x_err_stage := 'PA_ExpendItemAdjActivities: Before inserting records into PA_EXP_ITEM_ADJ_ACT_AR';
1240 FORALL exp_ind IN l_exp_adj_rowid_tab.FIRST .. l_exp_adj_rowid_tab.LAST
1241 insert into PA_EXP_ITEM_ADJ_ACT_AR
1242 (
1243 expenditure_item_id,
1244 activity_date,
1245 last_update_date,
1246 last_updated_by,
1247 creation_date,
1248 created_by,
1249 exception_activity_code,
1250 module_code,
1251 description,
1252 last_update_login,
1253 request_id,
1254 program_application_id,
1255 program_id,
1256 program_update_date,
1257 purge_batch_id,
1258 purge_release,
1259 purge_project_id
1260 )
1261 select eia.expenditure_item_id,
1262 eia.activity_date,
1263 eia.last_update_date,
1264 eia.last_updated_by,
1265 eia.creation_date,
1266 eia.created_by,
1267 eia.exception_activity_code,
1268 eia.module_code,
1269 eia.description,
1270 eia.last_update_login,
1271 eia.request_id,
1272 eia.program_application_id,
1273 eia.program_id,
1274 eia.program_update_date,
1275 p_purge_batch_id,
1276 p_purge_release,
1277 p_project_id
1278 from pa_expend_item_adj_activities eia
1279 where eia.rowid = l_exp_adj_rowid_tab(exp_ind);
1280 End If; /* if p_archive_flag = 'Y' */
1281 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1282
1283
1284 x_err_stage := 'PA_ExpendItemAdjActivities: Before deleting records from pa_expend_item_adj_activities' ;
1285
1286 FORALL exp_ind IN l_exp_adj_rowid_tab.FIRST .. l_exp_adj_rowid_tab.LAST
1287 DELETE from pa_expend_item_adj_activities eia
1288 where eia.rowid = l_exp_adj_rowid_tab(exp_ind);
1289
1290 l_NoOfRecordsDel := SQL%ROWCOUNT;
1291 /*s22 start of comment for performance changes
1292 ( select eia1.rowid
1293 from pa_expenditure_items_all ei,
1294 pa_expend_item_adj_activities eia1
1295 where ei.expenditure_item_date <= p_txn_to_date
1296 and ei.expenditure_item_id = eia1.expenditure_item_id
1297 and ei.project_id = p_project_id
1298 and rownum < p_commit_size
1299 ) ;
1300
1301 else
1302 insert into PA_EXP_ITEM_ADJ_ACT_AR
1303 (
1304 expenditure_item_id,
1305 activity_date,
1306 last_update_date,
1307 last_updated_by,
1308 creation_date,
1309 created_by,
1310 exception_activity_code,
1311 module_code,
1312 description,
1313 last_update_login,
1314 request_id,
1315 program_application_id,
1316 program_id,
1317 program_update_date,
1318 purge_batch_id,
1319 purge_release,
1320 purge_project_id
1321 )
1322 select eia.expenditure_item_id,
1323 eia.activity_date,
1324 eia.last_update_date,
1325 eia.last_updated_by,
1326 eia.creation_date,
1327 eia.created_by,
1328 eia.exception_activity_code,
1329 eia.module_code,
1330 eia.description,
1331 eia.last_update_login,
1332 eia.request_id,
1333 eia.program_application_id,
1334 eia.program_id,
1335 eia.program_update_date,
1336 p_purge_batch_id,
1337 p_purge_release,
1338 p_project_id
1339 from pa_expend_item_adj_activities eia
1340 where (eia.rowid ) in
1341 ( select eia1.rowid
1342 from pa_expenditure_items_all ei,
1343 pa_expend_item_adj_activities eia1
1344 where ei.expenditure_item_id = eia1.expenditure_item_id
1345 and ei.project_id = p_project_id
1346 and rownum < p_commit_size
1347 ) ;
1348 end if;
1349
1350 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1351
1352 if SQL%ROWCOUNT > 0 then
1353 -- We have a seperate delete statement if the archive option is
1354 -- selected because if archive option is selected the the records
1355 -- being purged will be those records which are already archived.
1356 -- table and
1357
1358 x_err_stage := 'PA_ExpendItemAdjActivities: Before deleting records from pa_expend_item_adj_activities';
1359 delete from pa_expend_item_adj_activities eia
1360 where (eia.expenditure_item_id, eia.activity_date ) in
1361 ( select eiar.expenditure_item_id, eiar.activity_date
1362 from PA_EXP_ITEM_ADJ_ACT_AR eiar
1363 where eiar.purge_project_id = p_project_id
1364 ) ;
1365
1366 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1367
1368 end if;
1369 else
1370 l_commit_size := p_commit_size ;
1371
1372 -- If the archive option is not selected then the delete will
1373 -- be based on the commit size.
1374
1375 x_err_stage := 'PA_ExpendItemAdjActivities: Before deleting records from pa_expend_item_adj_activities' ;
1376 if p_txn_to_date is NOT NULL then
1377 delete from pa_expend_item_adj_activities eia
1378 where (eia.rowid ) in
1379 ( select eia1.rowid
1380 from pa_expenditure_items_all ei,
1381 pa_expend_item_adj_activities eia1
1382 where ei.expenditure_item_date <= p_txn_to_date
1383 and ei.expenditure_item_id = eia1.expenditure_item_id
1384 and ei.project_id = p_project_id
1385 and rownum < p_commit_size
1386 ) ;
1387
1388 else
1389 delete from pa_expend_item_adj_activities eia
1390 where (eia.rowid ) in
1391 ( select eia1.rowid
1392 from pa_expenditure_items_all ei,
1393 pa_expend_item_adj_activities eia1
1394 where ei.expenditure_item_id = eia1.expenditure_item_id
1395 and ei.project_id = p_project_id
1396 and rownum < p_commit_size
1397 ) ;
1398 end if;
1399
1400 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1401
1402 end if ;
1403
1404 end of comment for performance changes. s3 */
1405 /*if SQL%ROWCOUNT = 0 then
1406
1407 -- Once the SqlCount becomes 0, which means that there are
1408 -- no more records to be purged then we exit the loop.
1409
1410 x_err_stage := 'PA_ExpendItemAdjActivities: No more records to archive / purge ' ;
1411 exit ;
1412
1413 else */
1414 -- After "deleting" or "deleting and inserting" a set of records
1415 -- the transaction is commited. This also creates a record in the
1416 -- Pa_Purge_Project_details which will show the no. of records
1417 -- that are purged from each table.
1418
1419 x_err_stage := 'PA_ExpendItemAdjActivities: Commiting the transaction' ;
1420 If l_NoOfRecordsDel > 0 Then
1421 Pa_Purge.CommitProcess(p_purge_batch_id,
1422 p_project_id,
1423 'PA_EXPEND_ITEM_ADJ_ACTIVITIES',
1424 l_NoOfRecordsIns,
1425 l_NoOfRecordsDel,
1426 x_err_code,
1427 x_err_stack,
1428 x_err_stage
1429 ) ;
1430 End if;
1431
1432 end if ;
1433 If (l_fetch_complete) Then
1434 exit;
1435 End If;
1436 END LOOP ;
1437
1438 x_err_stack := l_old_err_stack ;
1439
1440 EXCEPTION
1441 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1442 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1443
1444 WHEN OTHERS THEN
1445 -- x_err_stage := l_err_stage ;
1446 pa_debug.debug('Error Procedure Name := pa_purge_costing.PA_ExpendItemAdjActivities' );
1447 pa_debug.debug('Error stage is '||x_err_stage );
1448 pa_debug.debug('Error stack is '||x_err_stack );
1449 pa_debug.debug(SQLERRM);
1450 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1451
1452 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1453
1454 end PA_ExpendItemAdjActivities ;
1455
1456 -- Start of comments
1457 -- API name : PA_EiDenorm
1458 -- Type : Public
1459 -- Pre-reqs : None
1460 -- Function : This procedure purges all the records from pa_ei_denorm
1461 --
1462 -- Parameters : Refer to the comments of the previous procedure
1463 --
1464 -- End of comments
1465
1466 --
1467 -- The following procedure PA_EiDenorm was coded in the extension as all the
1468 -- customers may not have installed the patch for Online Time and Expense entry.
1469 -- If the patch is not installed then the procedure is put in the regular
1470 -- package there will be compilation errors.
1471
1472 procedure PA_EiDenorm ( p_purge_batch_id IN NUMBER,
1473 p_project_id IN NUMBER,
1474 p_txn_to_date IN DATE,
1475 p_purge_release IN VARCHAR2,
1476 p_archive_flag IN VARCHAR2,
1477 p_commit_size IN NUMBER,
1478 x_err_code IN OUT NOCOPY NUMBER,
1479 x_err_stack IN OUT NOCOPY VARCHAR2,
1480 x_err_stage IN OUT NOCOPY VARCHAR2
1481 ) is
1482
1483 l_old_err_stage VARCHAR2(2000);
1484 l_old_err_stack VARCHAR2(2000);
1485 l_NoOfRecordsIns NUMBER;
1486 l_NoOfRecordsDel NUMBER;
1487 begin
1488
1489 --
1490 -- This procedure is called by the main costing purge procedure
1491 -- pa_purge_costing.pa_costing_main_purge. It purges the data from
1492 -- Pa_Ei_Denorm. This is a new table created for Online Time and
1493 -- Expense(OTE) entry. Once OTE patch is installed user should
1494 -- uncomment these code to allow the purging of data from Pa_Ei_Denorm
1495 -- which is also a transaction table.
1496
1497 l_old_err_stack := x_err_stack;
1498
1499 x_err_stack := x_err_stack || ' ->Before insert into Pa_Ei_Denorm_AR' ;
1500
1501 LOOP
1502 if p_archive_flag = 'Y' then
1503 -- If archive option is selected then the records are
1504 -- inserted into the archived into the archive tables
1505 -- before being purged. The where condition is such that
1506 -- only the it inserts half the no. of records specified
1507 -- in the commit size.
1508
1509 l_commit_size := p_commit_size / 2 ;
1510
1511 insert into Pa_Ei_Denorm_AR
1512 (
1513 Expenditure_Id,
1514 Denorm_Id,
1515 Person_Id,
1516 Project_Id,
1517 Task_Id,
1518 Billable_Flag,
1519 Expenditure_Type,
1520 Unit_Of_Measure_Code,
1521 Unit_Of_Measure,
1522 Expenditure_Item_Id_1,
1523 Expenditure_Item_Date_1,
1524 Quantity_1,
1525 System_Linkage_Function_1,
1526 Non_Labor_Resource_1,
1527 Organization_Id_1,
1528 Override_To_Organization_Id_1,
1529 Raw_Cost_1,
1530 Raw_Cost_Rate_1,
1531 Attribute_Category_1,
1532 Attribute1_1,
1533 Attribute1_2,
1534 Attribute1_3,
1535 Attribute1_4,
1536 Attribute1_5,
1537 Attribute1_6,
1538 Attribute1_7,
1539 Attribute1_8,
1540 Attribute1_9,
1541 Attribute1_10,
1542 Orig_Transaction_Reference_1,
1543 Adjusted_Expenditure_Item_Id_1,
1544 Net_Zero_Adjustment_Flag_1,
1545 Expenditure_Comment_1,
1546 Expenditure_Item_Id_2,
1547 Expenditure_Item_Date_2,
1548 Quantity_2,
1549 System_Linkage_Function_2,
1550 Non_Labor_Resource_2,
1551 Organization_Id_2,
1552 Override_To_Organization_Id_2,
1553 Raw_Cost_2,
1554 Raw_Cost_Rate_2,
1555 Attribute_Category_2,
1556 Attribute2_1,
1557 Attribute2_2,
1558 Attribute2_3,
1559 Attribute2_4,
1560 Attribute2_5,
1561 Attribute2_6,
1562 Attribute2_7,
1563 Attribute2_8,
1564 Attribute2_9,
1565 Attribute2_10,
1566 Orig_Transaction_Reference_2,
1567 Adjusted_Expenditure_Item_Id_2,
1568 Net_Zero_Adjustment_Flag_2,
1569 Expenditure_Comment_2,
1570 Expenditure_Item_Id_3,
1571 Expenditure_Item_Date_3,
1572 Quantity_3,
1573 System_Linkage_Function_3,
1574 Non_Labor_Resource_3,
1575 Organization_Id_3,
1576 Override_To_Organization_Id_3,
1577 Raw_Cost_3,
1578 Raw_Cost_Rate_3,
1579 Attribute_Category_3,
1580 Attribute3_1,
1581 Attribute3_2,
1582 Attribute3_3,
1583 Attribute3_4,
1584 Attribute3_5,
1585 Attribute3_6,
1586 Attribute3_7,
1587 Attribute3_8,
1588 Attribute3_9,
1589 Attribute3_10,
1590 Orig_Transaction_Reference_3,
1591 Adjusted_Expenditure_Item_Id_3,
1592 Net_Zero_Adjustment_Flag_3,
1593 Expenditure_Comment_3,
1594 Expenditure_Item_Id_4,
1595 Expenditure_Item_Date_4,
1596 Quantity_4,
1597 System_Linkage_Function_4,
1598 Non_Labor_Resource_4,
1599 Organization_Id_4,
1600 Override_To_Organization_Id_4,
1601 Raw_Cost_4,
1602 Raw_Cost_Rate_4,
1603 Attribute_Category_4,
1604 Attribute4_1,
1605 Attribute4_2,
1606 Attribute4_3,
1607 Attribute4_4,
1608 Attribute4_5,
1609 Attribute4_6,
1610 Attribute4_7,
1611 Attribute4_8,
1612 Attribute4_9,
1613 Attribute4_10,
1614 Orig_Transaction_Reference_4,
1615 Adjusted_Expenditure_Item_Id_4,
1616 Net_Zero_Adjustment_Flag_4,
1617 Expenditure_Comment_4,
1618 Expenditure_Item_Id_5,
1619 Expenditure_Item_Date_5,
1620 Quantity_5,
1621 System_Linkage_Function_5,
1622 Non_Labor_Resource_5,
1623 Organization_Id_5,
1624 Override_To_Organization_Id_5,
1625 Raw_Cost_5,
1626 Raw_Cost_Rate_5,
1627 Attribute_Category_5,
1628 Attribute5_1,
1629 Attribute5_2,
1630 Attribute5_3,
1631 Attribute5_4,
1632 Attribute5_5,
1633 Attribute5_6,
1634 Attribute5_7,
1635 Attribute5_8,
1636 Attribute5_9,
1637 Attribute5_10,
1638 Orig_Transaction_Reference_5,
1639 Adjusted_Expenditure_Item_Id_5,
1640 Net_Zero_Adjustment_Flag_5,
1641 Expenditure_Comment_5,
1642 Expenditure_Item_Id_6,
1643 Expenditure_Item_Date_6,
1644 Quantity_6,
1645 System_Linkage_Function_6,
1646 Non_Labor_Resource_6,
1647 Organization_Id_6,
1648 Override_To_Organization_Id_6,
1649 Raw_Cost_6,
1650 Raw_Cost_Rate_6,
1651 Attribute_Category_6,
1652 Attribute6_1,
1653 Attribute6_2,
1654 Attribute6_3,
1655 Attribute6_4,
1656 Attribute6_5,
1657 Attribute6_6,
1658 Attribute6_7,
1659 Attribute6_8,
1660 Attribute6_9,
1661 Attribute6_10,
1662 Orig_Transaction_Reference_6,
1663 Adjusted_Expenditure_Item_Id_6,
1664 Net_Zero_Adjustment_Flag_6,
1665 Expenditure_Comment_6,
1666 Expenditure_Item_Id_7,
1667 Expenditure_Item_Date_7,
1668 Quantity_7,
1669 System_Linkage_Function_7,
1670 Non_Labor_Resource_7,
1671 Organization_Id_7,
1672 Override_To_Organization_Id_7,
1673 Raw_Cost_7,
1674 Raw_Cost_Rate_7,
1675 Attribute_Category_7,
1676 Attribute7_1,
1677 Attribute7_2,
1678 Attribute7_3,
1679 Attribute7_4,
1680 Attribute7_5,
1681 Attribute7_6,
1682 Attribute7_7,
1683 Attribute7_8,
1684 Attribute7_9,
1685 Attribute7_10,
1686 Orig_Transaction_Reference_7,
1687 Adjusted_Expenditure_Item_Id_7,
1688 Net_Zero_Adjustment_Flag_7,
1689 Expenditure_Comment_7,
1690 Denorm_Total_Qty,
1691 Denorm_Total_Amount,
1692 Created_By,
1693 Creation_Date,
1694 Last_Update_Date,
1695 Last_Updated_By,
1696 Last_Update_Login,
1697 JOB_ID_1,
1698 JOB_ID_2,
1699 JOB_ID_3,
1700 JOB_ID_4,
1701 JOB_ID_5,
1702 JOB_ID_6,
1703 JOB_ID_7,
1704 ADJUSTED_DENORM_ID,
1705 BILLABLE_FLAG_1,
1706 BILLABLE_FLAG_2,
1707 BILLABLE_FLAG_3,
1708 BILLABLE_FLAG_4,
1709 BILLABLE_FLAG_5,
1710 BILLABLE_FLAG_6,
1711 BILLABLE_FLAG_7,
1712 purge_batch_id,
1713 purge_release,
1714 purge_project_id
1715 )
1716 select eid.Expenditure_Id,
1717 eid.Denorm_Id,
1718 eid.Person_Id,
1719 eid.Project_Id,
1720 eid.Task_Id,
1721 eid.Billable_Flag,
1722 eid.Expenditure_Type,
1723 eid.Unit_Of_Measure_Code,
1724 eid.Unit_Of_Measure,
1725 eid.Expenditure_Item_Id_1,
1726 eid.Expenditure_Item_Date_1,
1727 eid.Quantity_1,
1728 eid.System_Linkage_Function_1,
1729 eid.Non_Labor_Resource_1,
1730 eid.Organization_Id_1,
1731 eid.Override_To_Organization_Id_1,
1732 eid.Raw_Cost_1,
1733 eid.Raw_Cost_Rate_1,
1734 eid.Attribute_Category_1,
1735 eid.Attribute1_1,
1736 eid.Attribute1_2,
1737 eid.Attribute1_3,
1738 eid.Attribute1_4,
1739 eid.Attribute1_5,
1740 eid.Attribute1_6,
1741 eid.Attribute1_7,
1742 eid.Attribute1_8,
1743 eid.Attribute1_9,
1744 eid.Attribute1_10,
1745 eid.Orig_Transaction_Reference_1,
1746 eid.Adjusted_Expenditure_Item_Id_1,
1747 eid.Net_Zero_Adjustment_Flag_1,
1748 eid.Expenditure_Comment_1,
1749 eid.Expenditure_Item_Id_2,
1750 eid.Expenditure_Item_Date_2,
1751 eid.Quantity_2,
1752 eid.System_Linkage_Function_2,
1753 eid.Non_Labor_Resource_2,
1754 eid.Organization_Id_2,
1755 eid.Override_To_Organization_Id_2,
1756 eid.Raw_Cost_2,
1757 eid.Raw_Cost_Rate_2,
1758 eid.Attribute_Category_2,
1759 eid.Attribute2_1,
1760 eid.Attribute2_2,
1761 eid.Attribute2_3,
1762 eid.Attribute2_4,
1763 eid.Attribute2_5,
1764 eid.Attribute2_6,
1765 eid.Attribute2_7,
1766 eid.Attribute2_8,
1767 eid.Attribute2_9,
1768 eid.Attribute2_10,
1769 eid.Orig_Transaction_Reference_2,
1770 eid.Adjusted_Expenditure_Item_Id_2,
1771 eid.Net_Zero_Adjustment_Flag_2,
1772 eid.Expenditure_Comment_2,
1773 eid.Expenditure_Item_Id_3,
1774 eid.Expenditure_Item_Date_3,
1775 eid.Quantity_3,
1776 eid.System_Linkage_Function_3,
1777 eid.Non_Labor_Resource_3,
1778 eid.Organization_Id_3,
1779 eid.Override_To_Organization_Id_3,
1780 eid.Raw_Cost_3,
1781 eid.Raw_Cost_Rate_3,
1782 eid.Attribute_Category_3,
1783 eid.Attribute3_1,
1784 eid.Attribute3_2,
1785 eid.Attribute3_3,
1786 eid.Attribute3_4,
1787 eid.Attribute3_5,
1788 eid.Attribute3_6,
1789 eid.Attribute3_7,
1790 eid.Attribute3_8,
1791 eid.Attribute3_9,
1792 eid.Attribute3_10,
1793 eid.Orig_Transaction_Reference_3,
1794 eid.Adjusted_Expenditure_Item_Id_3,
1795 eid.Net_Zero_Adjustment_Flag_3,
1796 eid.Expenditure_Comment_3,
1797 eid.Expenditure_Item_Id_4,
1798 eid.Expenditure_Item_Date_4,
1799 eid.Quantity_4,
1800 eid.System_Linkage_Function_4,
1801 eid.Non_Labor_Resource_4,
1802 eid.Organization_Id_4,
1803 eid.Override_To_Organization_Id_4,
1804 eid.Raw_Cost_4,
1805 eid.Raw_Cost_Rate_4,
1806 eid.Attribute_Category_4,
1807 eid.Attribute4_1,
1808 eid.Attribute4_2,
1809 eid.Attribute4_3,
1810 eid.Attribute4_4,
1811 eid.Attribute4_5,
1812 eid.Attribute4_6,
1813 eid.Attribute4_7,
1814 eid.Attribute4_8,
1815 eid.Attribute4_9,
1816 eid.Attribute4_10,
1817 eid.Orig_Transaction_Reference_4,
1818 eid.Adjusted_Expenditure_Item_Id_4,
1819 eid.Net_Zero_Adjustment_Flag_4,
1820 eid.Expenditure_Comment_4,
1821 eid.Expenditure_Item_Id_5,
1822 eid.Expenditure_Item_Date_5,
1823 eid.Quantity_5,
1824 eid.System_Linkage_Function_5,
1825 eid.Non_Labor_Resource_5,
1826 eid.Organization_Id_5,
1827 eid.Override_To_Organization_Id_5,
1828 eid.Raw_Cost_5,
1829 eid.Raw_Cost_Rate_5,
1830 eid.Attribute_Category_5,
1831 eid.Attribute5_1,
1832 eid.Attribute5_2,
1833 eid.Attribute5_3,
1834 eid.Attribute5_4,
1835 eid.Attribute5_5,
1836 eid.Attribute5_6,
1837 eid.Attribute5_7,
1838 eid.Attribute5_8,
1839 eid.Attribute5_9,
1840 eid.Attribute5_10,
1841 eid.Orig_Transaction_Reference_5,
1842 eid.Adjusted_Expenditure_Item_Id_5,
1843 eid.Net_Zero_Adjustment_Flag_5,
1844 eid.Expenditure_Comment_5,
1845 eid.Expenditure_Item_Id_6,
1846 eid.Expenditure_Item_Date_6,
1847 eid.Quantity_6,
1848 eid.System_Linkage_Function_6,
1849 eid.Non_Labor_Resource_6,
1850 eid.Organization_Id_6,
1851 eid.Override_To_Organization_Id_6,
1852 eid.Raw_Cost_6,
1853 eid.Raw_Cost_Rate_6,
1854 eid.Attribute_Category_6,
1855 eid.Attribute6_1,
1856 eid.Attribute6_2,
1857 eid.Attribute6_3,
1858 eid.Attribute6_4,
1859 eid.Attribute6_5,
1860 eid.Attribute6_6,
1861 eid.Attribute6_7,
1862 eid.Attribute6_8,
1863 eid.Attribute6_9,
1864 eid.Attribute6_10,
1865 eid.Orig_Transaction_Reference_6,
1866 eid.Adjusted_Expenditure_Item_Id_6,
1867 eid.Net_Zero_Adjustment_Flag_6,
1868 eid.Expenditure_Comment_6,
1869 eid.Expenditure_Item_Id_7,
1870 eid.Expenditure_Item_Date_7,
1871 eid.Quantity_7,
1872 eid.System_Linkage_Function_7,
1873 eid.Non_Labor_Resource_7,
1874 eid.Organization_Id_7,
1875 eid.Override_To_Organization_Id_7,
1876 eid.Raw_Cost_7,
1877 eid.Raw_Cost_Rate_7,
1878 eid.Attribute_Category_7,
1879 eid.Attribute7_1,
1880 eid.Attribute7_2,
1881 eid.Attribute7_3,
1882 eid.Attribute7_4,
1883 eid.Attribute7_5,
1884 eid.Attribute7_6,
1885 eid.Attribute7_7,
1886 eid.Attribute7_8,
1887 eid.Attribute7_9,
1888 eid.Attribute7_10,
1889 eid.Orig_Transaction_Reference_7,
1890 eid.Adjusted_Expenditure_Item_Id_7,
1891 eid.Net_Zero_Adjustment_Flag_7,
1892 eid.Expenditure_Comment_7,
1893 eid.Denorm_Total_Qty,
1894 eid.Denorm_Total_Amount,
1895 eid.Created_By,
1896 eid.Creation_Date,
1897 eid.Last_Update_Date,
1898 eid.Last_Updated_By,
1899 eid.Last_Update_Login,
1900 eid.JOB_ID_1,
1901 eid.JOB_ID_2,
1902 eid.JOB_ID_3,
1903 eid.JOB_ID_4,
1904 eid.JOB_ID_5,
1905 eid.JOB_ID_6,
1906 eid.JOB_ID_7,
1907 eid.ADJUSTED_DENORM_ID,
1908 eid.BILLABLE_FLAG_1,
1909 eid.BILLABLE_FLAG_2,
1910 eid.BILLABLE_FLAG_3,
1911 eid.BILLABLE_FLAG_4,
1912 eid.BILLABLE_FLAG_5,
1913 eid.BILLABLE_FLAG_6,
1914 eid.BILLABLE_FLAG_7,
1915 p_purge_batch_id,
1916 p_purge_release,
1917 p_project_id
1918 from pa_ei_denorm eid
1919 where eid.project_id = p_project_id
1920 and (p_txn_to_date is null
1921 or ( trunc(eid.expenditure_item_date_1) <= trunc(p_txn_to_date )
1922 and trunc(eid.expenditure_item_date_2) <= trunc(p_txn_to_date )
1923 and trunc(eid.expenditure_item_date_3) <= trunc(p_txn_to_date )
1924 and trunc(eid.expenditure_item_date_4) <= trunc(p_txn_to_date )
1925 and trunc(eid.expenditure_item_date_5) <= trunc(p_txn_to_date )
1926 and trunc(eid.expenditure_item_date_6) <= trunc(p_txn_to_date )
1927 and trunc(eid.expenditure_item_date_7) <= trunc(p_txn_to_date )))
1928 and rownum < p_commit_size ;
1929
1930 l_NoOfRecordsIns := SQL%ROWCOUNT ;
1931
1932 if SQL%ROWCOUNT > 0 then
1933
1934 -- We have a seperate delete statement if the archive option is
1935 -- selected because if archive option is selected the the records
1936 -- being purged will be those records which are already archived.
1937 -- table and
1938
1939 delete from pa_ei_denorm eid
1940 where (eid.denorm_id, eid.expenditure_id) in
1941 ( select eid2.denorm_id, eid.expenditure_id
1942 from pa_ei_denorm_ar eid2
1943 where eid2.purge_project_id = p_project_id ) ;
1944
1945 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1946 end if;
1947 else
1948
1949 l_commit_size := p_commit_size ;
1950
1951 -- If the archive option is not selected then the delete will
1952 -- be based on the commit size.
1953
1954 delete from pa_ei_denorm eid
1955 where eid.project_id = p_project_id
1956 and (p_txn_to_date is null
1957 or ( trunc(eid.expenditure_item_date_1) <= trunc(p_txn_to_date )
1958 and trunc(eid.expenditure_item_date_2) <= trunc(p_txn_to_date )
1959 and trunc(eid.expenditure_item_date_3) <= trunc(p_txn_to_date )
1960 and trunc(eid.expenditure_item_date_4) <= trunc(p_txn_to_date )
1961 and trunc(eid.expenditure_item_date_5) <= trunc(p_txn_to_date )
1962 and trunc(eid.expenditure_item_date_6) <= trunc(p_txn_to_date )
1963 and trunc(eid.expenditure_item_date_7) <= trunc(p_txn_to_date )))
1964 and rownum < p_commit_size ;
1965
1966 l_NoOfRecordsDel := SQL%ROWCOUNT ;
1967
1968 end if ;
1969
1970 if SQL%ROWCOUNT = 0 then
1971
1972 -- Once the SqlCount becomes 0, which means that there are
1973 -- no more records to be purged then we exit the loop.
1974
1975 exit ;
1976
1977 else
1978 -- After "deleting" or "deleting and inserting" a set of records
1979 -- the transaction is commited. This also creates a record in the
1980 -- Pa_Purge_Project_details which will show the no. of records
1981 -- that are purged from each table.
1982
1983 pa_purge.CommitProcess(p_purge_batch_id,
1984 p_project_id,
1985 'PA_EI_DENORM',
1986 l_NoOfRecordsIns,
1987 l_NoOfRecordsDel,
1988 x_err_code,
1989 x_err_stack,
1990 x_err_stage
1991 ) ;
1992
1993 end if ;
1994
1995 END LOOP ;
1996
1997 x_err_stack := l_old_err_stack ;
1998
1999 EXCEPTION
2000 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2001 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2002
2003 WHEN OTHERS THEN
2004 -- x_err_stage := l_err_stage ;
2005 pa_debug.debug('Error Procedure Name := PA_PURGE_EXTN.PA_EIDENORM' );
2006 pa_debug.debug('Error stage is '||x_err_stage );
2007 pa_debug.debug('Error stack is '||x_err_stack );
2008 pa_debug.debug(SQLERRM);
2009 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2010
2011 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2012
2013 end PA_EiDenorm ;
2014
2015
2016 -- Start of comments
2017 -- API name : PA_ExpenditureHistory
2018 -- Type : Public
2019 -- Pre-reqs : None
2020 -- Function : This procedure purges all the records from pa_expenditure_history
2021 --
2022 -- Parameters : Refer to the comments of the previous procedure
2023 --
2024 -- End of comments
2025
2026 procedure PA_ExpenditureHistory ( p_purge_batch_id IN NUMBER,
2027 p_project_id IN NUMBER,
2028 p_txn_to_date IN DATE,
2029 p_purge_release IN VARCHAR2,
2030 p_archive_flag IN VARCHAR2,
2031 p_commit_size IN NUMBER,
2032 x_err_code IN OUT NOCOPY NUMBER,
2033 x_err_stack IN OUT NOCOPY VARCHAR2,
2034 x_err_stage IN OUT NOCOPY VARCHAR2
2035 ) is
2036
2037 l_old_err_stage VARCHAR2(2000);
2038 l_old_err_stack VARCHAR2(2000);
2039 l_NoOfRecordsIns NUMBER;
2040 l_NoOfRecordsDel NUMBER;
2041 begin
2042
2043 l_old_err_stack := x_err_stack;
2044
2045 x_err_stack := x_err_stack || ' ->Before insert into Expenditure_History_AR' ;
2046
2047 LOOP
2048 if p_archive_flag = 'Y' then
2049 -- If archive option is selected then the records are
2050 -- inserted into the archived into the archive tables
2051 -- before being purged. The where condition is such that
2052 -- only the it inserts half the no. of records specified
2053 -- in the commit size.
2054
2055 l_commit_size := p_commit_size / 2 ;
2056 if p_txn_to_date is NOT NULL then
2057 insert into PA_EXP_HISTORY_AR
2058 (
2059 Audit_Type_Code,
2060 Late_Entry_Code,
2061 Reason_Comment,
2062 Audit_Order,
2063 Incurred_By_Person_Id,
2064 Expenditure_Id,
2065 Denorm_Id,
2066 Project_Id,
2067 Task_Id,
2068 Expenditure_Class_Code,
2069 Expenditure_Source_Code,
2070 Expenditure_Type,
2071 System_Linkage_Function,
2072 Expenditure_Item_Date,
2073 Quantity,
2074 Attribute_Category,
2075 Attribute1,
2076 Attribute2,
2077 Attribute3,
2078 Attribute4,
2079 Attribute5,
2080 Attribute6,
2081 Attribute7,
2082 Attribute8,
2083 Attribute9,
2084 Attribute10,
2085 Expenditure_Item_Comment,
2086 Adjusted_Expenditure_Item_Id,
2087 Change_Code,
2088 Creation_Date,
2089 Created_By,
2090 Last_Update_Date,
2091 Last_Updated_By,
2092 Last_Update_Login,
2093 purge_batch_id,
2094 purge_release,
2095 purge_project_id
2096 )
2097 select xh.Audit_Type_Code,
2098 xh.Late_Entry_Code,
2099 xh.Reason_Comment,
2100 xh.Audit_Order,
2101 xh.Incurred_By_Person_Id,
2102 xh.Expenditure_Id,
2103 xh.Denorm_Id,
2104 xh.Project_Id,
2105 xh.Task_Id,
2106 xh.Expenditure_Class_Code,
2107 xh.Expenditure_Source_Code,
2108 xh.Expenditure_Type,
2109 xh.System_Linkage_Function,
2110 xh.Expenditure_Item_Date,
2111 xh.Quantity,
2112 xh.Attribute_Category,
2113 xh.Attribute1,
2114 xh.Attribute2,
2115 xh.Attribute3,
2116 xh.Attribute4,
2117 xh.Attribute5,
2118 xh.Attribute6,
2119 xh.Attribute7,
2120 xh.Attribute8,
2121 xh.Attribute9,
2122 xh.Attribute10,
2123 xh.Expenditure_Item_Comment,
2124 xh.Adjusted_Expenditure_Item_Id,
2125 xh.Change_Code,
2126 xh.Creation_Date,
2127 xh.Created_By,
2128 xh.Last_Update_Date,
2129 xh.Last_Updated_By,
2130 xh.Last_Update_Login,
2131 p_purge_batch_id,
2132 p_purge_release,
2133 p_project_id
2134 from pa_expenditure_history xh
2135 where xh.project_id = p_project_id
2136 and xh.expenditure_item_date <= p_txn_to_date
2137 and rownum < l_commit_size ;
2138 else
2139 insert into PA_EXP_HISTORY_AR
2140 (
2141 Audit_Type_Code,
2142 Late_Entry_Code,
2143 Reason_Comment,
2144 Audit_Order,
2145 Incurred_By_Person_Id,
2146 Expenditure_Id,
2147 Denorm_Id,
2148 Project_Id,
2149 Task_Id,
2150 Expenditure_Class_Code,
2151 Expenditure_Source_Code,
2152 Expenditure_Type,
2153 System_Linkage_Function,
2154 Expenditure_Item_Date,
2155 Quantity,
2156 Attribute_Category,
2157 Attribute1,
2158 Attribute2,
2159 Attribute3,
2160 Attribute4,
2161 Attribute5,
2162 Attribute6,
2163 Attribute7,
2164 Attribute8,
2165 Attribute9,
2166 Attribute10,
2167 Expenditure_Item_Comment,
2168 Adjusted_Expenditure_Item_Id,
2169 Change_Code,
2170 Creation_Date,
2171 Created_By,
2172 Last_Update_Date,
2173 Last_Updated_By,
2174 Last_Update_Login,
2175 purge_batch_id,
2176 purge_release,
2177 purge_project_id
2178 )
2179 select xh.Audit_Type_Code,
2180 xh.Late_Entry_Code,
2181 xh.Reason_Comment,
2182 xh.Audit_Order,
2183 xh.Incurred_By_Person_Id,
2184 xh.Expenditure_Id,
2185 xh.Denorm_Id,
2186 xh.Project_Id,
2187 xh.Task_Id,
2188 xh.Expenditure_Class_Code,
2189 xh.Expenditure_Source_Code,
2190 xh.Expenditure_Type,
2191 xh.System_Linkage_Function,
2192 xh.Expenditure_Item_Date,
2193 xh.Quantity,
2194 xh.Attribute_Category,
2195 xh.Attribute1,
2196 xh.Attribute2,
2197 xh.Attribute3,
2198 xh.Attribute4,
2199 xh.Attribute5,
2200 xh.Attribute6,
2201 xh.Attribute7,
2202 xh.Attribute8,
2203 xh.Attribute9,
2204 xh.Attribute10,
2205 xh.Expenditure_Item_Comment,
2206 xh.Adjusted_Expenditure_Item_Id,
2207 xh.Change_Code,
2208 xh.Creation_Date,
2209 xh.Created_By,
2210 xh.Last_Update_Date,
2211 xh.Last_Updated_By,
2212 xh.Last_Update_Login,
2213 p_purge_batch_id,
2214 p_purge_release,
2215 p_project_id
2216 from pa_expenditure_history xh
2217 where xh.project_id = p_project_id
2218 and rownum < l_commit_size ;
2219 end if;
2220
2221 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2222
2223 if SQL%ROWCOUNT > 0 then
2224 -- We have a seperate delete statement if the archive option is
2225 -- selected because if archive option is selected the the records
2226 -- being purged will be those records which are already archived.
2227 -- table and
2228 delete from pa_expenditure_history xh
2229 where (xh.expenditure_id, xh.denorm_id ) in
2230 ( select xhar.expenditure_id, xhar.denorm_id
2231 from PA_EXP_HISTORY_AR xhar
2232 where xhar.purge_project_id = p_project_id
2233 ) ;
2234
2235 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2236 end if ;
2237 else
2238
2239 l_commit_size := p_commit_size ;
2240
2241 -- If the archive option is not selected then the delete will
2242 -- be based on the commit size.
2243 if p_txn_to_date is NOT NULL then
2244 delete from pa_expenditure_history xh
2245 where xh.project_id = p_project_id
2246 and xh.expenditure_item_date <= p_txn_to_date
2247 and rownum < l_commit_size ;
2248 else
2249 delete from pa_expenditure_history xh
2250 where xh.project_id = p_project_id
2251 and rownum < l_commit_size ;
2252 end if;
2253
2254 l_NoOfRecordsDel := SQL%ROWCOUNT ;
2255 end if ;
2256
2257 if SQL%ROWCOUNT = 0 then
2258 -- Once the SqlCount becomes 0, which means that there are
2259 -- no more records to be purged then we exit the loop.
2260
2261 exit ;
2262
2263 else
2264 -- After "deleting" or "deleting and inserting" a set of records
2265 -- the transaction is commited. This also creates a record in the
2266 -- Pa_Purge_Project_details which will show the no. of records
2267 -- that are purged from each table.
2268
2269 pa_purge.CommitProcess(p_purge_batch_id,
2270 p_project_id,
2271 'PA_EXPENDITURE_HISTORY',
2272 l_NoOfRecordsIns,
2273 l_NoOfRecordsDel,
2274 x_err_code,
2275 x_err_stack,
2276 x_err_stage
2277 ) ;
2278
2279 end if ;
2280 END LOOP ;
2281
2282 x_err_stack := l_old_err_stack ;
2283
2284 EXCEPTION
2285 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2286 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2287
2288 WHEN OTHERS THEN
2289 -- x_err_stage := l_err_stage ;
2290 pa_debug.debug('Error Procedure Name := PA_PURGE_EXTN.PA_EXPENDITUREHISTORY' );
2291 pa_debug.debug('Error stage is '||x_err_stage );
2292 pa_debug.debug('Error stack is '||x_err_stack );
2293 pa_debug.debug(SQLERRM);
2294 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2295
2296 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2297
2298 end PA_ExpenditureHistory ;
2299
2300
2301 -- Start of comments
2302 -- API name : PA_ExpenditureItems
2303 -- Type : Public
2304 -- Pre-reqs : None
2305 -- Function : This procedure purges all the expenditure items that are
2306 -- not related to other expenditure items through
2307 -- transferred_from_exp_item_id.
2308 --
2309 -- Parameters : Refer to the comments of the previous procedure
2310 --
2311 -- End of comments
2312
2313 procedure PA_ExpenditureItems ( p_purge_batch_id IN NUMBER,
2314 p_project_id IN NUMBER,
2315 p_txn_to_date IN DATE,
2316 p_purge_release IN VARCHAR2,
2317 p_archive_flag IN VARCHAR2,
2318 p_commit_size IN NUMBER,
2319 x_err_code IN OUT NOCOPY NUMBER,
2320 x_err_stack IN OUT NOCOPY VARCHAR2,
2321 x_err_stage IN OUT NOCOPY VARCHAR2)
2322 is
2323
2324 l_old_err_stage VARCHAR2(2000);
2325 l_old_err_stack VARCHAR2(2000);
2326 l_NoOfRecordsIns NUMBER;
2327 l_NoOfRecordsDel NUMBER;
2328 l_MRC_NoOfRecordsDel NUMBER;
2329 x_MRC_NoOfRecordsIns NUMBER;
2330 l_commit_size NUMBER;
2331 l_ei_rowid_tab PA_PLSQL_DATATYPES.RowIDTabTyp;
2332 l_ei_rowid_tab_empty PA_PLSQL_DATATYPES.RowIDTabTyp;
2333 l_fetch_complete BOOLEAN := FALSE;
2334 exp_ind NUMBER;
2335 l_exp_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2336 l_exp_item_id_tab_empty PA_PLSQL_DATATYPES.IdTabTyp;
2337 l_request_id NUMBER;
2338
2339 cursor c_exp_open_projects is
2340 select rowid,expenditure_item_id from pa_expenditure_items_all ei
2341 where ei.expenditure_item_date <= p_txn_to_date
2342 and ei.project_id = p_project_id;
2343
2344 cursor c_exp_close_projects is
2345 select rowid,expenditure_item_id from pa_expenditure_items_all ei
2346 where ei.project_id = p_project_id;
2347
2348 begin
2349
2350
2351 l_old_err_stack := x_err_stack;
2352
2353 x_err_stack := x_err_stack || ' ->Before insert into PA_Expenditure_Items_AR' ;
2354
2355 /* If mrc is enabled and being used then set the commit size based on the number
2356 * of reporting currencies using PA_UTILS2.ARPUR_MRC_Commit_Size.
2357 * Otherwise just set the commit using PA_UTILS2.ARPUR_Commit_Size.
2358 */
2359 IF (l_mrc_flag = 'Y') THEN
2360 l_commit_size := trunc(PA_UTILS2.ARPUR_MRC_Commit_Size/3);
2361 ELSE
2362 l_commit_size := trunc(PA_UTILS2.ARPUR_Commit_Size/3);
2363 END IF;
2364
2365 IF p_txn_to_date is not null THEN
2366 OPEN c_exp_open_projects;
2367 ELSE
2368 OPEN c_exp_close_projects;
2369 END IF;
2370
2371 LOOP
2372 l_NoOfRecordsIns := 0;
2373 l_NoOfRecordsIns := 0;
2374 l_ei_rowid_tab := l_ei_rowid_tab_empty;
2375
2376 IF p_txn_to_date is not null THEN
2377
2378 FETCH c_exp_open_projects BULK COLLECT INTO
2379 l_ei_rowid_tab,
2380 l_exp_item_id_tab
2381 LIMIT l_commit_size;
2382 IF c_exp_open_projects%NOTFOUND THEN
2383 CLOSE c_exp_open_projects;
2384 l_fetch_complete := TRUE;
2385 END IF;
2386 ELSE
2387 FETCH c_exp_close_projects BULK COLLECT INTO
2388 l_ei_rowid_tab,
2389 l_exp_item_id_tab
2390 LIMIT l_commit_size;
2391 IF c_exp_close_projects%NOTFOUND THEN
2392 CLOSE c_exp_close_projects;
2393 l_fetch_complete := TRUE;
2394 END IF;
2395 END IF;
2396
2397 If l_ei_rowid_tab.last is not null Then
2398 if p_archive_flag = 'Y' then
2399
2400 x_err_stage := 'PA_ExpenditureItems: Before inserting records into PA_Expenditure_Items_AR';
2401 FORALL exp_ind IN l_ei_rowid_tab.FIRST .. l_ei_rowid_tab.LAST
2402 insert into PA_Expenditure_Items_AR
2403 (
2404 receipt_currency_amount,
2405 receipt_currency_code,
2406 receipt_exchange_rate,
2407 denom_currency_code,
2408 denom_raw_cost,
2409 denom_burdened_cost,
2410 acct_currency_code,
2411 acct_rate_date,
2412 acct_rate_type,
2413 acct_exchange_rate,
2414 acct_raw_cost,
2415 acct_burdened_cost,
2416 acct_exchange_rounding_limit,
2417 project_currency_code,
2418 project_rate_date,
2419 project_rate_type,
2420 project_exchange_rate,
2421 cc_cross_charge_code,
2422 cc_prvdr_organization_id,
2423 cc_recvr_organization_id,
2424 cc_rejection_code,
2425 denom_tp_currency_code,
2426 denom_transfer_price,
2427 acct_tp_rate_type,
2428 acct_tp_rate_date,
2429 acct_tp_exchange_rate,
2430 acct_transfer_price,
2431 projacct_transfer_price,
2432 cc_markup_base_code,
2433 tp_base_amount,
2434 cc_cross_charge_type,
2435 recvr_org_id,
2436 cc_bl_distributed_code,
2437 cc_ic_processed_code,
2438 tp_ind_compiled_set_id,
2439 tp_bill_rate,
2440 tp_bill_markup_percentage,
2441 tp_schedule_line_percentage,
2442 tp_rule_percentage,
2443 cc_prvdr_cost_reclass_code,
2444 crl_asset_creation_status_code,
2445 crl_asset_creation_rej_code,
2446 cost_job_id,
2447 tp_job_id,
2448 prov_proj_bill_job_id,
2449 cost_dist_warning_code,
2450 project_tp_rate_date,
2451 project_tp_rate_type,
2452 project_tp_exchange_rate,
2453 projfunc_tp_rate_date,
2454 projfunc_tp_rate_type,
2455 projfunc_tp_exchange_rate,
2456 projfunc_transfer_price,
2457 bill_trans_forecast_curr_code,
2458 bill_trans_forecast_revenue,
2459 projfunc_rev_rate_date,
2460 projfunc_rev_exchange_rate,
2461 projfunc_cost_rate_type,
2462 projfunc_cost_rate_date,
2463 projfunc_cost_exchange_rate,
2464 project_raw_cost,
2465 project_burdened_cost,
2466 assignment_id,
2467 work_type_id,
2468 projfunc_raw_revenue,
2469 project_bill_amount,
2470 projfunc_currency_code,
2471 project_raw_revenue,
2472 project_transfer_price,
2473 tp_amt_type_code,
2474 bill_trans_currency_code,
2475 bill_trans_raw_revenue,
2476 bill_trans_bill_amount,
2477 bill_trans_adjusted_revenue,
2478 revproc_currency_code,
2479 revproc_rate_type,
2480 revproc_rate_date,
2481 revproc_exchange_rate,
2482 invproc_currency_code,
2483 invproc_rate_type,
2484 invproc_rate_date,
2485 discount_percentage,
2486 labor_multiplier,
2487 amount_calculation_code,
2488 bill_markup_percentage,
2489 rate_source_id,
2490 invproc_exchange_rate,
2491 inv_gen_rejection_code,
2492 projfunc_bill_amount,
2493 project_rev_rate_type,
2494 project_rev_rate_date,
2495 project_rev_exchange_rate,
2496 projfunc_rev_rate_type,
2497 projfunc_inv_rate_type,
2498 projfunc_inv_rate_date,
2499 projfunc_inv_exchange_rate,
2500 project_inv_rate_type,
2501 project_inv_rate_date,
2502 project_inv_exchange_rate,
2503 projfunc_fcst_rate_type,
2504 projfunc_fcst_rate_date,
2505 projfunc_fcst_exchange_rate,
2506 prvdr_accrual_date,
2507 recvr_accrual_date,
2508 quantity,
2509 non_labor_resource,
2510 organization_id,
2511 override_to_organization_id,
2512 denorm_id,
2513 raw_cost,
2514 raw_cost_rate,
2515 burden_cost,
2516 burden_cost_rate,
2517 cost_dist_rejection_code,
2518 labor_cost_multiplier_name,
2519 raw_revenue,
2520 bill_rate,
2521 accrued_revenue,
2522 accrual_rate,
2523 adjusted_revenue,
2524 adjusted_rate,
2525 bill_amount,
2526 forecast_revenue,
2527 bill_rate_multiplier,
2528 rev_dist_rejection_code,
2529 event_num,
2530 event_task_id,
2531 bill_job_id,
2532 bill_job_billing_title,
2533 bill_employee_billing_title,
2534 adjusted_expenditure_item_id,
2535 net_zero_adjustment_flag,
2536 transferred_from_exp_item_id,
2537 converted_flag,
2538 last_update_login,
2539 request_id,
2540 program_application_id,
2541 program_id,
2542 program_update_date,
2543 attribute_category,
2544 attribute1,
2545 expenditure_item_id,
2546 last_update_date,
2547 last_updated_by,
2548 creation_date,
2549 created_by,
2550 expenditure_id,
2551 task_id,
2552 expenditure_item_date,
2553 expenditure_type,
2554 cost_distributed_flag,
2555 revenue_distributed_flag,
2556 billable_flag,
2557 bill_hold_flag,
2558 attribute2,
2559 attribute3,
2560 attribute4,
2561 attribute5,
2562 attribute6,
2563 attribute7,
2564 attribute8,
2565 attribute9,
2566 attribute10,
2567 cost_ind_compiled_set_id,
2568 rev_ind_compiled_set_id,
2569 inv_ind_compiled_set_id,
2570 cost_burden_distributed_flag,
2571 ind_cost_dist_rejection_code,
2572 orig_transaction_reference,
2573 transaction_source,
2574 project_id,
2575 source_expenditure_item_id,
2576 job_id,
2577 org_id,
2578 system_linkage_function,
2579 burden_sum_dest_run_id,
2580 purge_batch_id,
2581 purge_release,
2582 purge_project_id,
2583 RATE_DISC_REASON_CODE,
2584 capital_event_id,
2585 posted_denom_burdened_cost,
2586 posted_project_burdened_cost,
2587 posted_projfunc_burdened_cost,
2588 posted_acct_burdened_cost,
2589 adjustment_type,
2590 Po_Line_Id, -- CWK and FPM Changes
2591 Po_Price_Type, -- CWK and FPM Changes
2592 Inventory_Item_Id, -- CWK and FPM Changes
2593 Wip_Resource_Id, -- CWK and FPM Changes
2594 Unit_Of_Measure, -- CWK and FPM Changes
2595 document_header_id, -- R12 Change
2596 document_distribution_id, -- R12 Change
2597 document_line_number, -- R12 Change
2598 document_payment_id, -- R12 Change
2599 vendor_id, -- R12 Change
2600 document_type, -- R12 Change
2601 document_distribution_type -- R12 Change
2602 )
2603 select ei.receipt_currency_amount,
2604 ei.receipt_currency_code,
2605 ei.receipt_exchange_rate,
2606 ei.denom_currency_code,
2607 ei.denom_raw_cost,
2608 ei.denom_burdened_cost,
2609 ei.acct_currency_code,
2610 ei.acct_rate_date,
2611 ei.acct_rate_type,
2612 ei.acct_exchange_rate,
2613 ei.acct_raw_cost,
2614 ei.acct_burdened_cost,
2615 ei.acct_exchange_rounding_limit,
2616 ei.project_currency_code,
2617 ei.project_rate_date,
2618 ei.project_rate_type,
2619 ei.project_exchange_rate,
2620 ei.cc_cross_charge_code,
2621 ei.cc_prvdr_organization_id,
2622 ei.cc_recvr_organization_id,
2623 ei.cc_rejection_code,
2624 ei.denom_tp_currency_code,
2625 ei.denom_transfer_price,
2626 ei.acct_tp_rate_type,
2627 ei.acct_tp_rate_date,
2628 ei.acct_tp_exchange_rate,
2629 ei.acct_transfer_price,
2630 ei.projacct_transfer_price,
2631 ei.cc_markup_base_code,
2632 ei.tp_base_amount,
2633 ei.cc_cross_charge_type,
2634 ei.recvr_org_id,
2635 ei.cc_bl_distributed_code,
2636 ei.cc_ic_processed_code,
2637 ei.tp_ind_compiled_set_id,
2638 ei.tp_bill_rate,
2639 ei.tp_bill_markup_percentage,
2640 ei.tp_schedule_line_percentage,
2641 ei.tp_rule_percentage,
2642 ei.cc_prvdr_cost_reclass_code,
2643 ei.crl_asset_creation_status_code,
2644 ei.crl_asset_creation_rej_code,
2645 ei.cost_job_id,
2646 ei.tp_job_id,
2647 ei.prov_proj_bill_job_id,
2648 ei.cost_dist_warning_code,
2649 ei.project_tp_rate_date,
2650 ei.project_tp_rate_type,
2651 ei.project_tp_exchange_rate,
2652 ei.projfunc_tp_rate_date,
2653 ei.projfunc_tp_rate_type,
2654 ei.projfunc_tp_exchange_rate,
2655 ei.projfunc_transfer_price,
2656 ei.bill_trans_forecast_curr_code,
2657 ei.bill_trans_forecast_revenue,
2658 ei.projfunc_rev_rate_date,
2659 ei.projfunc_rev_exchange_rate,
2660 ei.projfunc_cost_rate_type,
2661 ei.projfunc_cost_rate_date,
2662 ei.projfunc_cost_exchange_rate,
2663 ei.project_raw_cost,
2664 ei.project_burdened_cost,
2665 ei.assignment_id,
2666 ei.work_type_id,
2667 ei.projfunc_raw_revenue,
2668 ei.project_bill_amount,
2669 ei.projfunc_currency_code,
2670 ei.project_raw_revenue,
2671 ei.project_transfer_price,
2672 ei.tp_amt_type_code,
2673 ei.bill_trans_currency_code,
2674 ei.bill_trans_raw_revenue,
2675 ei.bill_trans_bill_amount,
2676 ei.bill_trans_adjusted_revenue,
2677 ei.revproc_currency_code,
2678 ei.revproc_rate_type,
2679 ei.revproc_rate_date,
2680 ei.revproc_exchange_rate,
2681 ei.invproc_currency_code,
2682 ei.invproc_rate_type,
2683 ei.invproc_rate_date,
2684 ei.discount_percentage,
2685 ei.labor_multiplier,
2686 ei.amount_calculation_code,
2687 ei.bill_markup_percentage,
2688 ei.rate_source_id,
2689 ei.invproc_exchange_rate,
2690 ei.inv_gen_rejection_code,
2691 ei.projfunc_bill_amount,
2692 ei.project_rev_rate_type,
2693 ei.project_rev_rate_date,
2694 ei.project_rev_exchange_rate,
2695 ei.projfunc_rev_rate_type,
2696 ei.projfunc_inv_rate_type,
2697 ei.projfunc_inv_rate_date,
2698 ei.projfunc_inv_exchange_rate,
2699 ei.project_inv_rate_type,
2700 ei.project_inv_rate_date,
2701 ei.project_inv_exchange_rate,
2702 ei.projfunc_fcst_rate_type,
2703 ei.projfunc_fcst_rate_date,
2704 ei.projfunc_fcst_exchange_rate,
2705 ei.prvdr_accrual_date,
2706 ei.recvr_accrual_date,
2707 ei.quantity,
2708 ei.non_labor_resource,
2709 ei.organization_id,
2710 ei.override_to_organization_id,
2711 ei.denorm_id,
2712 ei.raw_cost,
2713 ei.raw_cost_rate,
2714 ei.burden_cost,
2715 ei.burden_cost_rate,
2716 ei.cost_dist_rejection_code,
2717 ei.labor_cost_multiplier_name,
2718 ei.raw_revenue,
2719 ei.bill_rate,
2720 ei.accrued_revenue,
2721 ei.accrual_rate,
2722 ei.adjusted_revenue,
2723 ei.adjusted_rate,
2724 ei.bill_amount,
2725 ei.forecast_revenue,
2726 ei.bill_rate_multiplier,
2727 ei.rev_dist_rejection_code,
2728 ei.event_num,
2729 ei.event_task_id,
2730 ei.bill_job_id,
2731 ei.bill_job_billing_title,
2732 ei.bill_employee_billing_title,
2733 ei.adjusted_expenditure_item_id,
2734 ei.net_zero_adjustment_flag,
2735 ei.transferred_from_exp_item_id,
2736 ei.converted_flag,
2737 ei.last_update_login,
2738 ei.request_id,
2739 ei.program_application_id,
2740 ei.program_id,
2741 ei.program_update_date,
2742 ei.attribute_category,
2743 ei.attribute1,
2744 ei.expenditure_item_id,
2745 ei.last_update_date,
2746 ei.last_updated_by,
2747 ei.creation_date,
2748 ei.created_by,
2749 ei.expenditure_id,
2750 ei.task_id,
2751 ei.expenditure_item_date,
2752 ei.expenditure_type,
2753 ei.cost_distributed_flag,
2754 ei.revenue_distributed_flag,
2755 ei.billable_flag,
2756 ei.bill_hold_flag,
2757 ei.attribute2,
2758 ei.attribute3,
2759 ei.attribute4,
2760 ei.attribute5,
2761 ei.attribute6,
2762 ei.attribute7,
2763 ei.attribute8,
2764 ei.attribute9,
2765 ei.attribute10,
2766 ei.cost_ind_compiled_set_id,
2767 ei.rev_ind_compiled_set_id,
2768 ei.inv_ind_compiled_set_id,
2769 ei.cost_burden_distributed_flag,
2770 ei.ind_cost_dist_rejection_code,
2771 ei.orig_transaction_reference,
2772 ei.transaction_source,
2773 ei.project_id,
2774 ei.source_expenditure_item_id,
2775 ei.job_id,
2776 ei.org_id,
2777 ei.system_linkage_function,
2778 ei.burden_sum_dest_run_id,
2779 p_purge_batch_id,
2780 p_purge_release,
2781 p_project_id,
2782 ei.RATE_DISC_REASON_CODE,
2783 ei.capital_event_id,
2784 ei.posted_denom_burdened_cost,
2785 ei.posted_project_burdened_cost,
2786 ei.posted_projfunc_burdened_cost,
2787 ei.posted_acct_burdened_cost,
2788 ei.adjustment_type,
2789 ei.Po_Line_Id, -- CWK and FPM Changes
2790 ei.Po_Price_Type, -- CWK and FPM Changes
2791 ei.Inventory_Item_Id, -- CWK and FPM Changes
2792 ei.Wip_Resource_Id, -- CWK and FPM Changes
2793 ei.Unit_Of_Measure, -- CWK and FPM Changes
2794 ei.document_header_id, -- R12 Change
2795 ei.document_distribution_id, -- R12 Change
2796 ei.document_line_number, -- R12 Change
2797 ei.document_payment_id, -- R12 Change
2798 ei.vendor_id, -- R12 Change
2799 ei.document_type, -- R12 Change
2800 ei.document_distribution_type -- R12 Change
2801 from pa_expenditure_items_all ei
2802 where ei.rowid = l_ei_rowid_tab(exp_ind);
2803 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2804 end if;
2805
2806
2807 /* Commented for the bug#2405916 and moved this to inside the if SQL%ROWCOUNT > 0 condition */
2808 /* */
2809
2810 if l_NoOfRecordsIns > 0 then
2811
2812 IF (l_mrc_flag = 'Y') THEN
2813 pa_purge_costing.PA_MRCExpenditureItems(
2814 p_purge_batch_id,
2815 p_project_id,
2816 p_txn_to_date,
2817 p_purge_release,
2818 p_archive_flag,
2819 l_commit_size,
2820 x_err_code,
2821 x_err_stack,
2822 x_err_stage,
2823 x_MRC_NoOfRecordsIns);
2824 END IF;
2825 END IF;
2826
2827 Select Pa_Expend_Item_Adj_Act_s.nextval
2828 into l_request_id
2829 from dual ;
2830
2831 FORALL exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
2832 insert into Pa_Expend_item_Adj_Activities
2833 ( expenditure_item_id,
2834 activity_date,
2835 last_update_date,
2836 last_updated_by,
2837 creation_date,
2838 created_by,
2839 exception_activity_code,
2840 module_code,
2841 last_update_login,
2842 request_id
2843 )
2844 select ei.expenditure_item_id,
2845 sysdate,
2846 sysdate,
2847 g_user,
2848 sysdate,
2849 g_user,
2850 'SOURCE PURGED',
2851 'PURGE PROCESS',
2852 g_user,
2853 l_request_id
2854 from pa_expenditure_items_all ei
2855 where ei.transferred_from_exp_item_id = l_exp_item_id_tab(exp_ind)
2856 and ei.transferred_from_exp_item_id is not null
2857 and not exists ( select pp.project_id
2858 from pa_purge_projects pp
2859 where pp.project_id = ei.project_id
2860 and pp.purge_batch_id = p_purge_batch_id ) ;
2861
2862 FORALL exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
2863 update pa_expenditure_items_all ei
2864 set ei.transferred_from_exp_item_id = NULL
2865 where ei.transferred_from_exp_item_id = l_exp_item_id_tab(exp_ind)
2866 and ei.transferred_from_exp_item_id is not null
2867 and not exists ( select pp.project_id
2868 from pa_purge_projects pp
2869 where pp.project_id = ei.project_id
2870 and pp.purge_batch_id = p_purge_batch_id ) ;
2871
2872 /* Each time thru the loop need to make sure that reset the
2873 * counter tracking the number of records that deleted from
2874 * the mrc table.
2875 */
2876 IF (l_mrc_flag = 'Y') THEN
2877 pa_utils2.MRC_row_count := 0;
2878 END IF;
2879
2880 -- We have a seperate delete statement if the archive option is
2881 -- selected because if archive option is selected the the records
2882 -- being purged will be those records which are already archived.
2883 -- table and
2884
2885 x_err_stage := 'PA_ExpenditureItems: Before deleting records from pa_expenditure_items_all';
2886 FORALL exp_ind IN l_ei_rowid_tab.FIRST .. l_ei_rowid_tab.LAST
2887 DELETE FROM PA_EXPENDITURE_ITEMS_ALL EI
2888 WHERE EI.ROWID = l_ei_rowid_tab(exp_ind);
2889
2890 l_NoOfRecordsDel := SQL%ROWCOUNT;
2891 l_MRC_NoOfRecordsDel := pa_utils2.MRC_row_count;
2892
2893
2894 IF l_NoOfRecordsDel > 0 THEN
2895 x_err_stage := 'PA_ExpenditureItems: Commiting the transaction' ;
2896 pa_purge.CommitProcess(p_purge_batch_id,
2897 p_project_id,
2898 'PA_EXPENDITURE_ITEMS',
2899 l_NoOfRecordsIns,
2900 l_NoOfRecordsDel,
2901 x_err_code,
2902 x_err_stack,
2903 x_err_stage,
2904 /* 'PA_MC_EXP_ITEMS_AR', */
2905 'PA_MC_EXP_ITEMS',
2906 x_MRC_NoOfRecordsIns,
2907 l_MRC_NoOfRecordsDel
2908 ) ;
2909
2910 end if ;
2911 end if ;
2912
2913 IF (l_fetch_complete) THEN
2914 Exit;
2915 END IF;
2916
2917 END LOOP ;
2918
2919 x_err_stack := l_old_err_stack ;
2920
2921 EXCEPTION
2922 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2923 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2924
2925 WHEN OTHERS THEN
2926 -- x_err_stage := l_err_stage ;
2927 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPENDITUREITEMS' );
2928 pa_debug.debug('Error stage is '||x_err_stage );
2929 pa_debug.debug('Error stack is '||x_err_stack );
2930 pa_debug.debug(SQLERRM);
2931 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2932
2933 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2934
2935 end PA_ExpenditureItems ;
2936
2937 -- Start of comments
2938 -- API name : PA_ExpItemsSrcPurge
2939 -- Type : Public
2940 -- Pre-reqs : None
2941 -- Function : This procedure all the expenditure items that are
2942 -- transferred to another expenditure item.
2943 --
2944 -- Parameters : Refer to the comments of the previous procedure
2945 --
2946 -- End of comments
2947
2948 procedure PA_ExpItemsSrcPurge ( p_purge_batch_id IN NUMBER,
2949 p_project_id IN NUMBER,
2950 p_txn_to_date IN DATE,
2951 p_purge_release IN VARCHAR2,
2952 p_archive_flag IN VARCHAR2,
2953 p_commit_size IN NUMBER,
2954 x_err_code IN OUT NOCOPY NUMBER,
2955 x_err_stack IN OUT NOCOPY VARCHAR2,
2956 x_err_stage IN OUT NOCOPY VARCHAR2
2957 ) is
2958
2959 l_old_err_stage VARCHAR2(2000);
2960 l_old_err_stack VARCHAR2(2000);
2961 l_NoOfRecordsIns NUMBER;
2962 l_NoOfRecordsDel NUMBER;
2963 l_Request_Id NUMBER;
2964
2965 begin
2966
2967
2968 l_old_err_stack := x_err_stack;
2969
2970 x_err_stack := x_err_stack || ' ->Before insert into PA_Expenditure_Items_AR' ;
2971
2972 if p_archive_flag = 'Y' then
2973 l_commit_size := trunc(p_commit_size / 4) ;
2974 else
2975
2976 l_commit_size := trunc(p_commit_size / 3) ;
2977 end if ;
2978
2979
2980 LOOP
2981 Select Pa_Expend_Item_Adj_Act_s.nextval
2982 into l_request_id
2983 from dual ;
2984 -- l_request_id := Pa_Expend_Item_Adj_Act_s.nextval ;
2985 x_err_stage := 'PA_ExpItemsSrcPurge: Before inserting audit records into Pa_Expend_item_Adj_Activities';
2986 if p_txn_to_date is NOT NULL then
2987 insert into Pa_Expend_item_Adj_Activities
2988 ( expenditure_item_id,
2989 activity_date,
2990 last_update_date,
2991 last_updated_by,
2992 creation_date,
2993 created_by,
2994 exception_activity_code,
2995 module_code,
2996 last_update_login,
2997 request_id
2998 )
2999 select ei.expenditure_item_id,
3000 sysdate,
3001 sysdate,
3002 g_user,
3003 sysdate,
3004 g_user,
3005 'SOURCE PURGED',
3006 'PURGE PROCESS',
3007 g_user,
3008 l_request_id
3009 from pa_expenditure_items_all ei
3010 where ei.transferred_from_exp_item_id in ( select ei1.expenditure_item_id
3011 from pa_expenditure_items_all ei1
3012 where ei1.expenditure_item_date <= p_txn_to_date
3013 and ei1.project_id = p_project_id )
3014 and ei.transferred_from_exp_item_id is not null
3015 and rownum < l_commit_size
3016 and not exists ( select pp.project_id
3017 from pa_purge_projects pp
3018 where pp.project_id = ei.project_id
3019 and pp.purge_batch_id = p_purge_batch_id ) ;
3020 else
3021 insert into Pa_Expend_item_Adj_Activities
3022 ( expenditure_item_id,
3023 activity_date,
3024 last_update_date,
3025 last_updated_by,
3026 creation_date,
3027 created_by,
3028 exception_activity_code,
3029 module_code,
3030 last_update_login,
3031 request_id
3032 )
3033 select ei.expenditure_item_id,
3034 sysdate,
3035 sysdate,
3036 g_user,
3037 sysdate,
3038 g_user,
3039 'SOURCE PURGED',
3040 'PURGE PROCESS',
3041 g_user,
3042 l_request_id
3043 from pa_tasks t,pa_expenditure_items_all ei
3044 where ei.transferred_from_exp_item_id in ( select ei1.expenditure_item_id
3045 from pa_expenditure_items_all ei1,
3046 pa_tasks t1
3047 where ei1.task_id = t1.task_id
3048 and t1.project_id = p_project_id )
3049 and ei.task_id = t.task_id
3050 and ei.transferred_from_exp_item_id is not null
3051 and rownum < l_commit_size
3052 and not exists ( select pp.project_id
3053 from pa_purge_projects pp
3054 where pp.project_id = t.project_id
3055 and pp.purge_batch_id = p_purge_batch_id ) ;
3056 end if;
3057
3058
3059
3060 if SQL%ROWCOUNT = 0 then
3061 exit ;
3062 else
3063
3064 /* Commented for archive purge performance....
3065 if p_archive_flag = 'Y' then
3066 -- If archive option is selected then the records are
3067 -- inserted into the archived into the archive tables
3068 -- before being purged. The where condition is such that
3069 -- only the it inserts half the no. of records specified
3070 -- in the commit size.
3071
3072 x_err_stage := 'PA_ExpItemsSrcPurge: Before inserting records into PA_Expenditure_Items_AR';
3073 insert into PA_Expenditure_Items_AR
3074 (
3075 receipt_currency_amount,
3076 receipt_currency_code,
3077 receipt_exchange_rate,
3078 denom_currency_code,
3079 denom_raw_cost,
3080 denom_burdened_cost,
3081 acct_currency_code,
3082 acct_rate_date,
3083 acct_rate_type,
3084 acct_exchange_rate,
3085 acct_raw_cost,
3086 acct_burdened_cost,
3087 acct_exchange_rounding_limit,
3088 project_currency_code,
3089 project_rate_date,
3090 project_rate_type,
3091 project_exchange_rate,
3092 cc_cross_charge_code,
3093 cc_prvdr_organization_id,
3094 cc_recvr_organization_id,
3095 cc_rejection_code,
3096 denom_tp_currency_code,
3097 denom_transfer_price,
3098 acct_tp_rate_type,
3099 acct_tp_rate_date,
3100 acct_tp_exchange_rate,
3101 acct_transfer_price,
3102 projacct_transfer_price,
3103 cc_markup_base_code,
3104 tp_base_amount,
3105 cc_cross_charge_type,
3106 recvr_org_id,
3107 cc_bl_distributed_code,
3108 cc_ic_processed_code,
3109 tp_ind_compiled_set_id,
3110 tp_bill_rate,
3111 tp_bill_markup_percentage,
3112 tp_schedule_line_percentage,
3113 tp_rule_percentage,
3114 cc_prvdr_cost_reclass_code,
3115 crl_asset_creation_status_code,
3116 crl_asset_creation_rej_code,
3117 cost_job_id,
3118 tp_job_id,
3119 prov_proj_bill_job_id,
3120 cost_dist_warning_code,
3121 project_tp_rate_date,
3122 project_tp_rate_type,
3123 project_tp_exchange_rate,
3124 projfunc_tp_rate_date,
3125 projfunc_tp_rate_type,
3126 projfunc_tp_exchange_rate,
3127 projfunc_transfer_price,
3128 bill_trans_forecast_curr_code,
3129 bill_trans_forecast_revenue,
3130 projfunc_rev_rate_date,
3131 projfunc_rev_exchange_rate,
3132 projfunc_cost_rate_type,
3133 projfunc_cost_rate_date,
3134 projfunc_cost_exchange_rate,
3135 project_raw_cost,
3136 project_burdened_cost,
3137 assignment_id,
3138 work_type_id,
3139 projfunc_raw_revenue,
3140 project_bill_amount,
3141 projfunc_currency_code,
3142 project_raw_revenue,
3143 project_transfer_price,
3144 tp_amt_type_code,
3145 bill_trans_currency_code,
3146 bill_trans_raw_revenue,
3147 bill_trans_bill_amount,
3148 bill_trans_adjusted_revenue,
3149 revproc_currency_code,
3150 revproc_rate_type,
3151 revproc_rate_date,
3152 revproc_exchange_rate,
3153 invproc_currency_code,
3154 invproc_rate_type,
3155 invproc_rate_date,
3156 discount_percentage,
3157 labor_multiplier,
3158 amount_calculation_code,
3159 bill_markup_percentage,
3160 rate_source_id,
3161 invproc_exchange_rate,
3162 inv_gen_rejection_code,
3163 projfunc_bill_amount,
3164 project_rev_rate_type,
3165 project_rev_rate_date,
3166 project_rev_exchange_rate,
3167 projfunc_rev_rate_type,
3168 projfunc_inv_rate_type,
3169 projfunc_inv_rate_date,
3170 projfunc_inv_exchange_rate,
3171 project_inv_rate_type,
3172 project_inv_rate_date,
3173 project_inv_exchange_rate,
3174 projfunc_fcst_rate_type,
3175 projfunc_fcst_rate_date,
3176 projfunc_fcst_exchange_rate,
3177 prvdr_accrual_date,
3178 recvr_accrual_date,
3179 quantity,
3180 non_labor_resource,
3181 organization_id,
3182 override_to_organization_id,
3183 denorm_id,
3184 raw_cost,
3185 raw_cost_rate,
3186 burden_cost,
3187 burden_cost_rate,
3188 cost_dist_rejection_code,
3189 labor_cost_multiplier_name,
3190 raw_revenue,
3191 bill_rate,
3192 accrued_revenue,
3193 accrual_rate,
3194 adjusted_revenue,
3195 adjusted_rate,
3196 bill_amount,
3197 forecast_revenue,
3198 bill_rate_multiplier,
3199 rev_dist_rejection_code,
3200 event_num,
3201 event_task_id,
3202 bill_job_id,
3203 bill_job_billing_title,
3204 bill_employee_billing_title,
3205 adjusted_expenditure_item_id,
3206 net_zero_adjustment_flag,
3207 transferred_from_exp_item_id,
3208 converted_flag,
3209 last_update_login,
3210 request_id,
3211 program_application_id,
3212 program_id,
3213 program_update_date,
3214 attribute_category,
3215 attribute1,
3216 expenditure_item_id,
3217 last_update_date,
3218 last_updated_by,
3219 creation_date,
3220 created_by,
3221 expenditure_id,
3222 task_id,
3223 expenditure_item_date,
3224 expenditure_type,
3225 cost_distributed_flag,
3226 revenue_distributed_flag,
3227 billable_flag,
3228 bill_hold_flag,
3229 attribute2,
3230 attribute3,
3231 attribute4,
3232 attribute5,
3233 attribute6,
3234 attribute7,
3235 attribute8,
3236 attribute9,
3237 attribute10,
3238 cost_ind_compiled_set_id,
3239 rev_ind_compiled_set_id,
3240 inv_ind_compiled_set_id,
3241 cost_burden_distributed_flag,
3242 ind_cost_dist_rejection_code,
3243 orig_transaction_reference,
3244 transaction_source,
3245 project_id,
3246 source_expenditure_item_id,
3247 job_id,
3248 org_id,
3249 System_Linkage_Function,
3250 Burden_Sum_Dest_Run_Id,
3251 purge_batch_id,
3252 purge_release,
3253 purge_project_id,
3254 RATE_DISC_REASON_CODE,
3255 posted_denom_burdened_cost,
3256 posted_project_burdened_cost,
3257 posted_projfunc_burdened_cost,
3258 posted_acct_burdened_cost,
3259 adjustment_type
3260 )
3261 select ei.receipt_currency_amount,
3262 ei.receipt_currency_code,
3263 ei.receipt_exchange_rate,
3264 ei.denom_currency_code,
3265 ei.denom_raw_cost,
3266 ei.denom_burdened_cost,
3267 ei.acct_currency_code,
3268 ei.acct_rate_date,
3269 ei.acct_rate_type,
3270 ei.acct_exchange_rate,
3271 ei.acct_raw_cost,
3272 ei.acct_burdened_cost,
3273 ei.acct_exchange_rounding_limit,
3274 ei.project_currency_code,
3275 ei.project_rate_date,
3276 ei.project_rate_type,
3277 ei.project_exchange_rate,
3278 ei.cc_cross_charge_code,
3279 ei.cc_prvdr_organization_id,
3280 ei.cc_recvr_organization_id,
3281 ei.cc_rejection_code,
3282 ei.denom_tp_currency_code,
3283 ei.denom_transfer_price,
3284 ei.acct_tp_rate_type,
3285 ei.acct_tp_rate_date,
3286 ei.acct_tp_exchange_rate,
3287 ei.acct_transfer_price,
3288 ei.projacct_transfer_price,
3289 ei.cc_markup_base_code,
3290 ei.tp_base_amount,
3291 ei.cc_cross_charge_type,
3292 ei.recvr_org_id,
3293 ei.cc_bl_distributed_code,
3294 ei.cc_ic_processed_code,
3295 ei.tp_ind_compiled_set_id,
3296 ei.tp_bill_rate,
3297 ei.tp_bill_markup_percentage,
3298 ei.tp_schedule_line_percentage,
3299 ei.tp_rule_percentage,
3300 ei.cc_prvdr_cost_reclass_code,
3301 ei.crl_asset_creation_status_code,
3302 ei.crl_asset_creation_rej_code,
3303 ei.cost_job_id,
3304 ei.tp_job_id,
3305 ei.prov_proj_bill_job_id,
3306 ei.cost_dist_warning_code,
3307 ei.project_tp_rate_date,
3308 ei.project_tp_rate_type,
3309 ei.project_tp_exchange_rate,
3310 ei.projfunc_tp_rate_date,
3311 ei.projfunc_tp_rate_type,
3312 ei.projfunc_tp_exchange_rate,
3313 ei.projfunc_transfer_price,
3314 ei.bill_trans_forecast_curr_code,
3315 ei.bill_trans_forecast_revenue,
3316 ei.projfunc_rev_rate_date,
3317 ei.projfunc_rev_exchange_rate,
3318 ei.projfunc_cost_rate_type,
3319 ei.projfunc_cost_rate_date,
3320 ei.projfunc_cost_exchange_rate,
3321 ei.project_raw_cost,
3322 ei.project_burdened_cost,
3323 ei.assignment_id,
3324 ei.work_type_id,
3325 ei.projfunc_raw_revenue,
3326 ei.project_bill_amount,
3327 ei.projfunc_currency_code,
3328 ei.project_raw_revenue,
3329 ei.project_transfer_price,
3330 ei.tp_amt_type_code,
3331 ei.bill_trans_currency_code,
3332 ei.bill_trans_raw_revenue,
3333 ei.bill_trans_bill_amount,
3334 ei.bill_trans_adjusted_revenue,
3335 ei.revproc_currency_code,
3336 ei.revproc_rate_type,
3337 ei.revproc_rate_date,
3338 ei.revproc_exchange_rate,
3339 ei.invproc_currency_code,
3340 ei.invproc_rate_type,
3341 ei.invproc_rate_date,
3342 ei.discount_percentage,
3343 ei.labor_multiplier,
3344 ei.amount_calculation_code,
3345 ei.bill_markup_percentage,
3346 ei.rate_source_id,
3347 ei.invproc_exchange_rate,
3348 ei.inv_gen_rejection_code,
3349 ei.projfunc_bill_amount,
3350 ei.project_rev_rate_type,
3351 ei.project_rev_rate_date,
3352 ei.project_rev_exchange_rate,
3353 ei.projfunc_rev_rate_type,
3354 ei.projfunc_inv_rate_type,
3355 ei.projfunc_inv_rate_date,
3356 ei.projfunc_inv_exchange_rate,
3357 ei.project_inv_rate_type,
3358 ei.project_inv_rate_date,
3359 ei.project_inv_exchange_rate,
3360 ei.projfunc_fcst_rate_type,
3361 ei.projfunc_fcst_rate_date,
3362 ei.projfunc_fcst_exchange_rate,
3363 ei.prvdr_accrual_date,
3364 ei.recvr_accrual_date,
3365 ei.quantity,
3366 ei.non_labor_resource,
3367 ei.organization_id,
3368 ei.override_to_organization_id,
3369 ei.denorm_id,
3370 ei.raw_cost,
3371 ei.raw_cost_rate,
3372 ei.burden_cost,
3373 ei.burden_cost_rate,
3374 ei.cost_dist_rejection_code,
3375 ei.labor_cost_multiplier_name,
3376 ei.raw_revenue,
3377 ei.bill_rate,
3378 ei.accrued_revenue,
3379 ei.accrual_rate,
3380 ei.adjusted_revenue,
3381 ei.adjusted_rate,
3382 ei.bill_amount,
3383 ei.forecast_revenue,
3384 ei.bill_rate_multiplier,
3385 ei.rev_dist_rejection_code,
3386 ei.event_num,
3387 ei.event_task_id,
3388 ei.bill_job_id,
3389 ei.bill_job_billing_title,
3390 ei.bill_employee_billing_title,
3391 ei.adjusted_expenditure_item_id,
3392 ei.net_zero_adjustment_flag,
3393 ei.transferred_from_exp_item_id,
3394 ei.converted_flag,
3395 ei.last_update_login,
3396 ei.request_id,
3397 ei.program_application_id,
3398 ei.program_id,
3399 ei.program_update_date,
3400 ei.attribute_category,
3401 ei.attribute1,
3402 ei.expenditure_item_id,
3403 ei.last_update_date,
3404 ei.last_updated_by,
3405 ei.creation_date,
3406 ei.created_by,
3407 ei.expenditure_id,
3408 ei.task_id,
3409 ei.expenditure_item_date,
3410 ei.expenditure_type,
3411 ei.cost_distributed_flag,
3412 ei.revenue_distributed_flag,
3413 ei.billable_flag,
3414 ei.bill_hold_flag,
3415 ei.attribute2,
3416 ei.attribute3,
3417 ei.attribute4,
3418 ei.attribute5,
3419 ei.attribute6,
3420 ei.attribute7,
3421 ei.attribute8,
3422 ei.attribute9,
3423 ei.attribute10,
3424 ei.cost_ind_compiled_set_id,
3425 ei.rev_ind_compiled_set_id,
3426 ei.inv_ind_compiled_set_id,
3427 ei.cost_burden_distributed_flag,
3428 ei.ind_cost_dist_rejection_code,
3429 ei.orig_transaction_reference,
3430 ei.transaction_source,
3431 ei.project_id,
3432 ei.source_expenditure_item_id,
3433 ei.job_id,
3434 ei.org_id,
3435 ei.System_Linkage_Function,
3436 ei.Burden_Sum_Dest_Run_Id,
3437 p_purge_batch_id,
3438 p_purge_release,
3439 p_project_id,
3440 ei.RATE_DISC_REASON_CODE
3441 ei.posted_denom_burdened_cost,
3442 ei.posted_project_burdened_cost,
3443 ei.posted_projfunc_burdened_cost,
3444 ei.posted_acct_burdened_cost,
3445 ei.adjustment_type
3446 from pa_expenditure_items_all ei
3447 where ei.expenditure_item_id in ( select ei1.transferred_from_exp_item_id
3448 from Pa_Expend_item_Adj_Activities eia,
3449 pa_expenditure_items_all ei1
3450 where ei1.expenditure_item_id = eia.expenditure_item_id
3451 and eia.request_id = l_request_id
3452 and eia.exception_activity_code= 'SOURCE PURGED'
3453 and ei1.transferred_from_exp_item_id is not null ) ;
3454
3455
3456 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3457
3458 end if ;
3459
3460 x_err_stage := 'PA_ExpItemsSrcPurge: Deleting records into pa_expenditure_items_all';
3461 delete from pa_expenditure_items_all ei
3462 where ei.expenditure_item_id in ( select ei1.transferred_from_exp_item_id
3463 from Pa_Expend_item_Adj_Activities eia,
3464 pa_expenditure_items_all ei1
3465 where ei1.expenditure_item_id = eia.expenditure_item_id
3466 and eia.request_id = l_request_id
3467 and eia.exception_activity_code= 'SOURCE PURGED'
3468 and ei1.transferred_from_exp_item_id is not null ) ;
3469
3470
3471 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3472 */
3473
3474 x_err_stage := 'PA_ExpItemsSrcPurge: Deleting the links between expenditure items' ;
3475 update pa_expenditure_items_all ei
3476 set ei.transferred_from_exp_item_id = NULL
3477 where ei.expenditure_item_id in ( select eia.expenditure_item_id
3478 from Pa_Expend_item_Adj_Activities eia
3479 where eia.request_id = l_request_id
3480 and eia.exception_activity_code= 'SOURCE PURGED')
3481 and ei.transferred_from_exp_item_id is not null ;
3482
3483
3484 -- After "deleting" or "deleting and inserting" a set of records
3485 -- the transaction is commited. This also creates a record in the
3486 -- Pa_Purge_Project_details which will show the no. of records
3487 -- that are purged from each table.
3488
3489 x_err_stage := 'PA_ExpenditureComments: Commiting the transaction' ;
3490
3491 /* */
3492 end if;
3493 END LOOP ;
3494
3495 x_err_stack := l_old_err_stack ;
3496
3497 EXCEPTION
3498 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3499 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3500
3501 WHEN OTHERS THEN
3502 -- x_err_stage := l_err_stage ;
3503 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPITEMSSRCPURGE' );
3504 pa_debug.debug('Error stage is '||x_err_stage );
3505 pa_debug.debug('Error stack is '||x_err_stack );
3506 pa_debug.debug(SQLERRM);
3507 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3508
3509 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3510
3511 end PA_ExpItemsSrcPurge ;
3512
3513 -- Start of comments
3514 -- API name : PA_ExpItemsDestPurge
3515 -- Type : Public
3516 -- Pre-reqs : None
3517 -- Function : This procedure purges all expenditure items that were
3518 -- transferred from some other expenditure item.
3519 --
3520 -- Parameters : Refer to the comments of the previous procedure
3521 --
3522 -- End of comments
3523
3524 procedure PA_ExpItemsDestPurge( p_purge_batch_id IN NUMBER,
3525 p_project_id IN NUMBER,
3526 p_txn_to_date IN DATE,
3527 p_purge_release IN VARCHAR2,
3528 p_archive_flag IN VARCHAR2,
3529 p_commit_size IN NUMBER,
3530 x_err_code IN OUT NOCOPY NUMBER,
3531 x_err_stack IN OUT NOCOPY VARCHAR2,
3532 x_err_stage IN OUT NOCOPY VARCHAR2
3533 ) is
3534
3535 l_old_err_stage VARCHAR2(2000);
3536 l_old_err_stack VARCHAR2(2000);
3537 l_NoOfRecordsIns NUMBER;
3538 l_NoOfRecordsDel NUMBER;
3539 l_Request_Id NUMBER;
3540 l_exp_ind NUMBER;
3541 l_fetch_complete BOOLEAN:= FALSE;
3542
3543 cursor c_exp_open_lines is
3544 select ei.transferred_from_exp_item_id
3545 from pa_expenditure_items_all ei
3546 where ei.expenditure_item_date <= p_txn_to_date
3547 and ei.transferred_from_exp_item_id is not null
3548 and ei.project_id = p_project_id;
3549
3550 cursor c_exp_close_lines is
3551 select ei.transferred_from_exp_item_id
3552 from pa_expenditure_items_all ei
3553 where ei.transferred_from_exp_item_id is not null
3554 and ei.project_id = p_project_id;
3555
3556 l_exp_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3557 l_exp_item_id_tab_emp PA_PLSQL_DATATYPES.IdTabTyp;
3558
3559 begin
3560
3561
3562 l_old_err_stack := x_err_stack;
3563
3564 x_err_stack := x_err_stack || ' ->Before insert into PA_Expenditure_Items_AR' ;
3565
3566 if p_archive_flag = 'Y' then
3567 l_commit_size := trunc(p_commit_size / 5) ;
3568 else
3569
3570 l_commit_size := trunc(p_commit_size / 3) ;
3571 end if ;
3572
3573 IF p_txn_to_date is not null THEN
3574 OPEN c_exp_open_lines;
3575 ELSE
3576 OPEN c_exp_close_lines;
3577 END IF;
3578
3579 LOOP
3580 l_exp_item_id_tab := l_exp_item_id_tab_emp;
3581 IF p_txn_to_date is not null THEN
3582 FETCH c_exp_open_lines BULK COLLECT INTO l_exp_item_id_tab LIMIT l_commit_size;
3583 IF c_exp_open_lines%NOTFOUND THEN
3584 CLOSE c_exp_open_lines;
3585 l_fetch_complete := TRUE;
3586 END IF;
3587 ELSE
3588 FETCH c_exp_close_lines BULK COLLECT INTO l_exp_item_id_tab LIMIT l_commit_size;
3589 IF c_exp_close_lines%NOTFOUND THEN
3590 CLOSE c_exp_close_lines;
3591 l_fetch_complete := TRUE;
3592 END IF;
3593 END IF;
3594
3595 IF (nvl(l_exp_item_id_tab.LAST,0) > 0 ) THEN
3596 Select Pa_Expend_Item_Adj_Act_s.nextval
3597 into l_request_id
3598 from dual ;
3599 -- l_request_id := Pa_Expend_Item_Adj_Act_s.nextval ;
3600 x_err_stage := 'PA_ExpItemsDestPurge: Before inserting audit records ' ;
3601 /* if p_txn_to_date is NOT NULL then */
3602 FORALL l_exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
3603 insert into Pa_Expend_item_Adj_Activities
3604 ( expenditure_item_id,
3605 activity_date,
3606 last_update_date,
3607 last_updated_by,
3608 creation_date,
3609 created_by,
3610 exception_activity_code,
3611 module_code,
3612 last_update_login,
3613 request_id
3614 )
3615 select ei.expenditure_item_id,
3616 sysdate,
3617 sysdate,
3618 g_user,
3619 sysdate,
3620 g_user,
3621 'DESTINATION PURGED',
3622 'PURGE PROCESS',
3623 g_user,
3624 l_Request_Id
3625 from pa_expenditure_items_all ei
3626 where ei.expenditure_item_id = l_exp_item_id_tab(l_exp_ind)
3627 /* in ( select ei1.transferred_from_exp_item_id
3628 from pa_expenditure_items_all ei1
3629 where ei1.expenditure_item_date <= p_txn_to_date
3630 and ei1.transferred_from_exp_item_id is not null
3631 and ei1.project_id = p_project_id )
3632 and rownum < l_commit_size */
3633 and not exists ( select pp.project_id
3634 from pa_purge_projects pp
3635 where pp.project_id = ei.project_id
3636 and pp.purge_batch_id = p_purge_batch_id );
3637 /* else */
3638 /* end if; */
3639
3640 /* if SQL%ROWCOUNT = 0 then
3641 exit ; */
3642
3643 /* Commented for performance issue..... */
3644 end if;
3645 IF ( l_fetch_complete ) THEN
3646 exit;
3647 END IF;
3648 END LOOP ;
3649
3650 x_err_stack := l_old_err_stack ;
3651
3652 EXCEPTION
3653 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3654 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3655
3656 WHEN OTHERS THEN
3657 -- x_err_stage := l_err_stage ;
3658 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPITEMSDESTPURGE' );
3659 pa_debug.debug('Error stage is '||x_err_stage );
3660 pa_debug.debug('Error stack is '||x_err_stack );
3661 pa_debug.debug(SQLERRM);
3662 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3663
3664 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3665
3666 end PA_ExpItemsDestPurge ;
3667
3668 -- Start of comments
3669 -- API name : PA_Routings1
3670 -- Type : Public
3671 -- Pre-reqs : None
3672 -- Function : This procedure purges all the routing records whose expenditures
3673 -- does not have any expenditure items.
3674 --
3675 -- Parameters : Refer to the comments of the previous procedure
3676 --
3677 -- End of comments
3678
3679 procedure PA_Routings1 ( p_purge_batch_id IN NUMBER,
3680 p_project_id IN NUMBER,
3681 p_purge_release IN VARCHAR2,
3682 p_archive_flag IN VARCHAR2,
3683 p_commit_size IN NUMBER,
3684 x_err_code IN OUT NOCOPY NUMBER,
3685 x_err_stack IN OUT NOCOPY VARCHAR2,
3686 x_err_stage IN OUT NOCOPY VARCHAR2
3687 ) is
3688
3689 l_old_err_stage VARCHAR2(2000);
3690 l_old_err_stack VARCHAR2(2000);
3691 l_NoOfRecordsIns NUMBER;
3692 l_NoOfRecordsDel NUMBER;
3693 begin
3694
3695 l_old_err_stack := x_err_stack;
3696
3697 x_err_stack := x_err_stack || ' ->Before insert into Routings_AR' ;
3698
3699 LOOP
3700 if p_archive_flag = 'Y' then
3701 -- If archive option is selected then the records are
3702 -- inserted into the archived into the archive tables
3703 -- before being purged. The where condition is such that
3704 -- only the it inserts half the no. of records specified
3705 -- in the commit size.
3706
3707 l_commit_size := p_commit_size / 2 ;
3708
3709 x_err_stage := 'PA_Routings1: Before inserting records into PA_Routings_AR' ;
3710 insert into PA_Routings_AR
3711 (
3712 Expenditure_Id,
3713 Routed_From_Person_Id,
3714 Start_Date,
3715 Routing_Status_Code,
3716 Creation_Date,
3717 Created_By,
3718 Last_Update_Date,
3719 Last_Updated_By,
3720 Last_Update_Login,
3721 Routed_To_Person_Id,
3722 End_Date,
3723 Routing_Comment,
3724 purge_batch_id,
3725 purge_release,
3726 purge_project_id
3727 )
3728 Select ro.Expenditure_Id,
3729 ro.Routed_From_Person_Id,
3730 ro.Start_Date,
3731 ro.Routing_Status_Code,
3732 ro.Creation_Date,
3733 ro.Created_By,
3734 ro.Last_Update_Date,
3735 ro.Last_Updated_By,
3736 ro.Last_Update_Login,
3737 ro.Routed_To_Person_Id,
3738 ro.End_Date,
3739 ro.Routing_Comment,
3740 p_purge_batch_id,
3741 p_purge_release,
3742 p_project_id
3743 from pa_routings ro
3744 where not exists
3745 ( select x.expenditure_id
3746 from pa_expenditures_all x
3747 where ro.expenditure_id = x.expenditure_id)
3748 and rownum < l_commit_size ;
3749
3750 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3751
3752 if SQL%ROWCOUNT > 0 then
3753 -- We have a seperate delete statement if the archive option is
3754 -- selected because if archive option is selected the the records
3755 -- being purged will be those records which are already archived.
3756 -- table and
3757
3758 x_err_stage := 'PA_Routings1: Before deleting records from pa_routings' ;
3759
3760 delete from pa_routings ro
3761 where (ro.expenditure_id, ro.start_date ) in
3762 ( select roar.expenditure_id, roar.start_date
3763 from pa_routings_ar roar
3764 where roar.purge_project_id = p_project_id
3765 ) ;
3766
3767 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3768 end if ;
3769 else
3770
3771 l_commit_size := p_commit_size ;
3772
3773 -- If the archive option is not selected then the delete will
3774 -- be based on the commit size.
3775
3776 x_err_stage := 'PA_Routings1: Before deleting records from pa_routings' ;
3777 delete from pa_routings ro
3778 where not exists
3779 ( select x.expenditure_id
3780 from pa_expenditures_all x
3781 where ro.expenditure_id = x.expenditure_id)
3782 and rownum < l_commit_size ;
3783
3784 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3785 end if ;
3786
3787 if SQL%ROWCOUNT = 0 then
3788 -- Once the SqlCount becomes 0, which means that there are
3789 -- no more records to be purged then we exit the loop.
3790
3791 x_err_stage := 'PA_Routings1: No more records to archive / purge ' ;
3792 exit ;
3793
3794 else
3795 -- After "deleting" or "deleting and inserting" a set of records
3796 -- the transaction is commited. This also creates a record in the
3797 -- Pa_Purge_Project_details which will show the no. of records
3798 -- that are purged from each table.
3799
3800 x_err_stage := 'PA_Routings1: Commiting the transaction' ;
3801 pa_purge.CommitProcess(p_purge_batch_id,
3802 p_project_id,
3803 'PA_ROUTINGS',
3804 l_NoOfRecordsIns,
3805 l_NoOfRecordsDel,
3806 x_err_code,
3807 x_err_stack,
3808 x_err_stage
3809 ) ;
3810
3811 end if ;
3812 END LOOP ;
3813
3814 x_err_stack := l_old_err_stack ;
3815
3816 EXCEPTION
3817 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3818 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3819
3820 WHEN OTHERS THEN
3821 -- x_err_stage := l_err_stage ;
3822 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_ROUTINGS1' );
3823 pa_debug.debug('Error stage is '||x_err_stage );
3824 pa_debug.debug('Error stack is '||x_err_stack );
3825 pa_debug.debug(SQLERRM);
3826 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3827
3828 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3829
3830 end PA_Routings1 ;
3831
3832 -- Start of comments
3833 -- API name : PA_Expenditures1
3834 -- Type : Public
3835 -- Pre-reqs : None
3836 -- Function : This procedure purges all the expenditures that does not
3837 -- have any expenditure items.
3838 --
3839 -- Parameters : Refer to the comments of the previous procedure
3840 --
3841 -- End of comments
3842
3843 procedure PA_Expenditures1 ( p_purge_batch_id IN NUMBER,
3844 p_project_id IN NUMBER,
3845 p_purge_release IN VARCHAR2,
3846 p_archive_flag IN VARCHAR2,
3847 p_commit_size IN NUMBER,
3848 x_err_code IN OUT NOCOPY NUMBER,
3849 x_err_stack IN OUT NOCOPY VARCHAR2,
3850 x_err_stage IN OUT NOCOPY VARCHAR2
3851 ) is
3852
3853 l_old_err_stage VARCHAR2(2000);
3854 l_old_err_stack VARCHAR2(2000);
3855 l_NoOfRecordsIns NUMBER;
3856 l_NoOfRecordsDel NUMBER;
3857 begin
3858
3859 l_old_err_stack := x_err_stack;
3860
3861 x_err_stack := x_err_stack || ' ->Before insert into Expenditures_AR' ;
3862
3863 LOOP
3864 if p_archive_flag = 'Y' then
3865 -- If archive option is selected then the records are
3866 -- inserted into the archived into the archive tables
3867 -- before being purged. The where condition is such that
3868 -- only the it inserts half the no. of records specified
3869 -- in the commit size.
3870
3871 l_commit_size := p_commit_size / 2 ;
3872
3873 x_err_stage := 'PA_Expenditures1: Before insert into PA_Expenditures_AR' ;
3874 insert into PA_Expenditures_AR
3875 (
3876 Expenditure_Id,
3877 Last_Update_Date,
3878 Last_Updated_By,
3879 Creation_Date,
3880 Created_By,
3881 Expenditure_Status_Code,
3882 Expenditure_Ending_Date,
3883 Expenditure_Class_Code,
3884 Incurred_By_Person_Id,
3885 Incurred_By_Organization_Id,
3886 Expenditure_Group,
3887 Control_Total_Amount,
3888 Entered_By_Person_Id,
3889 Description,
3890 Initial_Submission_Date,
3891 Last_Update_Login,
3892 Request_Id,
3893 Program_Id,
3894 Program_Application_Id,
3895 Program_Update_Date,
3896 Attribute_Category,
3897 Attribute1,
3898 Attribute2,
3899 Attribute3,
3900 Attribute4,
3901 Attribute5,
3902 Attribute6,
3903 Attribute7,
3904 Attribute8,
3905 Attribute9,
3906 Attribute10,
3907 Pte_Reference,
3908 Org_Id,
3909 OVERRIDING_APPROVER_PERSON_ID,
3910 WF_STATUS_CODE,
3911 TRANSFER_STATUS_CODE,
3912 ORIG_EXP_TXN_REFERENCE1,
3913 ORIG_USER_EXP_TXN_REFERENCE,
3914 ORIG_EXP_TXN_REFERENCE2,
3915 ORIG_EXP_TXN_REFERENCE3,
3916 USER_BATCH_NAME,
3917 DENOM_CURRENCY_CODE,
3918 ACCT_CURRENCY_CODE,
3919 ACCT_RATE_DATE,
3920 ACCT_RATE_TYPE,
3921 ACCT_EXCHANGE_RATE,
3922 VENDOR_ID,
3923 purge_batch_id,
3924 purge_release,
3925 purge_project_id,
3926 Person_Type -- CWK and FPM Changes
3927 )
3928 Select x.Expenditure_Id,
3929 x.Last_Update_Date,
3930 x.Last_Updated_By,
3931 x.Creation_Date,
3932 x.Created_By,
3933 x.Expenditure_Status_Code,
3934 x.Expenditure_Ending_Date,
3935 x.Expenditure_Class_Code,
3936 x.Incurred_By_Person_Id,
3937 x.Incurred_By_Organization_Id,
3938 x.Expenditure_Group,
3939 x.Control_Total_Amount,
3940 x.Entered_By_Person_Id,
3941 x.Description,
3942 x.Initial_Submission_Date,
3943 x.Last_Update_Login,
3944 x.Request_Id,
3945 x.Program_Id,
3946 x.Program_Application_Id,
3947 x.Program_Update_Date,
3948 x.Attribute_Category,
3949 x.Attribute1,
3950 x.Attribute2,
3951 x.Attribute3,
3952 x.Attribute4,
3953 x.Attribute5,
3954 x.Attribute6,
3955 x.Attribute7,
3956 x.Attribute8,
3957 x.Attribute9,
3958 x.Attribute10,
3959 x.Pte_Reference,
3960 x.Org_Id,
3961 x.OVERRIDING_APPROVER_PERSON_ID,
3962 x.WF_STATUS_CODE,
3963 x.TRANSFER_STATUS_CODE,
3964 x.ORIG_EXP_TXN_REFERENCE1,
3965 x.ORIG_USER_EXP_TXN_REFERENCE,
3966 x.ORIG_EXP_TXN_REFERENCE2,
3967 x.ORIG_EXP_TXN_REFERENCE3,
3968 x.USER_BATCH_NAME,
3969 x.DENOM_CURRENCY_CODE,
3970 x.ACCT_CURRENCY_CODE,
3971 x.ACCT_RATE_DATE,
3972 x.ACCT_RATE_TYPE,
3973 x.ACCT_EXCHANGE_RATE,
3974 x.VENDOR_ID,
3975 p_purge_batch_id,
3976 p_purge_release,
3977 p_project_id,
3978 x.Person_Type -- CWK and FPM Changes
3979
3980 from pa_expenditures_all x
3981 where (x.rowid ) in
3982 ( select x1.rowid
3983 from pa_expenditures_all x1
3984 where not exists ( select ei.expenditure_id
3985 from pa_expenditure_items_all ei
3986 where ei.expenditure_id = x1.expenditure_id)
3987 and x1.expenditure_status_code = 'APPROVED'
3988 and rownum < l_commit_size
3989 ) ;
3990
3991 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3992
3993 if SQL%ROWCOUNT > 0 then
3994 -- We have a seperate delete statement if the archive option is
3995 -- selected because if archive option is selected the the records
3996 -- being purged will be those records which are already archived.
3997 -- table and
3998
3999 x_err_stage := 'PA_Expenditures1: Before deleting records from pa_expenditures_all' ;
4000 delete from pa_expenditures_all x
4001 where (x.rowid ) in
4002 ( select x1.rowid
4003 from pa_expenditures_all x1,
4004 pa_expenditures_ar x2
4005 where x2.expenditure_id = x1.expenditure_id
4006 and x2.purge_project_id = p_project_id
4007 ) ;
4008
4009 l_NoOfRecordsDel := SQL%ROWCOUNT ;
4010 end if ;
4011 else
4012
4013 l_commit_size := p_commit_size ;
4014
4015 -- If the archive option is not selected then the delete will
4016 -- be based on the commit size.
4017
4018 x_err_stage := 'PA_Expenditures1: Before deleting records from pa_expenditures_all' ;
4019 delete from pa_expenditures_all x
4020 where (x.rowid ) in
4021 ( select x1.rowid
4022 from pa_expenditures_all x1
4023 where not exists ( select ei.expenditure_id
4024 from pa_expenditure_items_all ei
4025 where ei.expenditure_id = x1.expenditure_id)
4026 and x1.expenditure_status_code = 'APPROVED'
4027 and rownum < l_commit_size
4028 ) ;
4029
4030 l_NoOfRecordsDel := SQL%ROWCOUNT ;
4031 end if ;
4032
4033 if SQL%ROWCOUNT = 0 then
4034 -- Once the SqlCount becomes 0, which means that there are
4035 -- no more records to be purged then we exit the loop.
4036
4037 x_err_stage := 'PA_Expenditures1: No more records to archive / purge ' ;
4038 exit ;
4039
4040 else
4041 -- After "deleting" or "deleting and inserting" a set of records
4042 -- the transaction is commited. This also creates a record in the
4043 -- Pa_Purge_Project_details which will show the no. of records
4044 -- that are purged from each table.
4045
4046 x_err_stage := 'PA_Expenditures1: Commiting the transaction' ;
4047 pa_purge.CommitProcess(p_purge_batch_id,
4048 p_project_id,
4049 'PA_EXPENDITURES_ALL',
4050 l_NoOfRecordsIns,
4051 l_NoOfRecordsDel,
4052 x_err_code,
4053 x_err_stack,
4054 x_err_stage
4055 ) ;
4056
4057 end if ;
4058 END LOOP ;
4059
4060
4061 x_err_stack := l_old_err_stack ;
4062
4063 EXCEPTION
4064 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4065 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4066
4067 WHEN OTHERS THEN
4068 -- x_err_stage := l_err_stage ;
4069 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPENDITURES1' );
4070 pa_debug.debug('Error stage is '||x_err_stage );
4071 pa_debug.debug('Error stack is '||x_err_stack );
4072 pa_debug.debug(SQLERRM);
4073 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4074
4075 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4076
4077 end PA_Expenditures1 ;
4078
4079 -- Start of comments
4080 -- API name : PA_MRCExpenditureItems
4081 -- Type : Public
4082 -- Pre-reqs : None
4083 -- Function : This procedure purges all the MRCexpenditure items that
4084 -- are not related to other expenditure items through
4085 -- transferred_from_exp_item_id.
4086 --
4087 -- Parameters : Refer to the comments of the previous procedure
4088 --
4089 -- End of comments
4090
4091 procedure PA_MRCExpenditureItems(
4092 p_purge_batch_id IN NUMBER,
4093 p_project_id IN NUMBER,
4094 p_txn_to_date IN DATE,
4095 p_purge_release IN VARCHAR2,
4096 p_archive_flag IN VARCHAR2,
4097 p_commit_size IN NUMBER,
4098 x_err_code IN OUT NOCOPY NUMBER,
4099 x_err_stack IN OUT NOCOPY VARCHAR2,
4100 x_err_stage IN OUT NOCOPY VARCHAR2,
4101 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4102 IS
4103
4104 l_old_err_stage VARCHAR2(2000);
4105 l_old_err_stack VARCHAR2(2000);
4106
4107 BEGIN
4108
4109 l_old_err_stack := x_err_stack;
4110
4111 x_err_stack := x_err_stack || ' ->Before insert into PA_MC_EXP_ITEMS_AR' ;
4112
4113 x_err_stage := 'PA_MRCExpenditureItems: Before inserting records into PA_MC_EXP_ITEMS_AR';
4114
4115 /* Note that purged_project_id in table PA_EXPENDITURE_ITEMS_AR is index
4116 * Will also need index on PA_MC_EXP_ITEMS_AR columns set_of_books_id and
4117 * expenditure_item_id.
4118 * The NOT EXISTS section is to make sure that no attempt is made to insert a
4119 * duplicate record in table PA_MC_EXP_ITEMS_AR.
4120 */
4121 INSERT INTO PA_MC_EXP_ITEMS_AR
4122 ( SET_OF_BOOKS_ID,
4123 EXPENDITURE_ITEM_ID,
4124 RAW_COST,
4125 RAW_COST_RATE,
4126 BURDEN_COST,
4127 BURDEN_COST_RATE,
4128 RAW_REVENUE,
4129 BILL_RATE,
4130 ACCRUED_REVENUE,
4131 ACCRUAL_RATE,
4132 ADJUSTED_REVENUE,
4133 ADJUSTED_RATE,
4134 BILL_AMOUNT,
4135 FORECAST_REVENUE,
4136 NET_ZERO_ADJUSTMENT_FLAG,
4137 TRANSFERRED_FROM_EXP_ITEM_ID,
4138 PRC_ASSIGNMENT_ID,
4139 CURRENCY_CODE,
4140 COST_EXCHANGE_RATE,
4141 COST_CONVERSION_DATE,
4142 COST_RATE_TYPE,
4143 REVENUE_EXCHANGE_RATE,
4144 REVENUE_CONVERSION_DATE,
4145 REVENUE_RATE_TYPE,
4146 TRANSFER_PRICE,
4147 TP_EXCHANGE_RATE,
4148 TP_CONVERSION_DATE,
4149 TP_RATE_TYPE,
4150 PROJFUNC_INV_RATE_TYPE,
4151 PROJFUNC_INV_RATE_DATE,
4152 PROJFUNC_INV_EXCHANGE_RATE,
4153 PROJFUNC_FCST_RATE_TYPE,
4154 PROJFUNC_FCST_RATE_DATE,
4155 PROJFUNC_FCST_EXCHANGE_RATE,
4156 PURGE_PROJECT_ID,
4157 PURGE_RELEASE,
4158 PURGE_BATCH_ID )
4159 SELECT
4160 MCEI.SET_OF_BOOKS_ID,
4161 MCEI.EXPENDITURE_ITEM_ID,
4162 MCEI.RAW_COST,
4163 MCEI.RAW_COST_RATE,
4164 MCEI.BURDEN_COST,
4165 MCEI.BURDEN_COST_RATE,
4166 MCEI.RAW_REVENUE,
4167 MCEI.BILL_RATE,
4168 MCEI.ACCRUED_REVENUE,
4169 MCEI.ACCRUAL_RATE,
4170 MCEI.ADJUSTED_REVENUE,
4171 MCEI.ADJUSTED_RATE,
4172 MCEI.BILL_AMOUNT,
4173 MCEI.FORECAST_REVENUE,
4174 MCEI.NET_ZERO_ADJUSTMENT_FLAG,
4175 MCEI.TRANSFERRED_FROM_EXP_ITEM_ID,
4176 MCEI.PRC_ASSIGNMENT_ID,
4177 MCEI.CURRENCY_CODE,
4178 MCEI.COST_EXCHANGE_RATE,
4179 MCEI.COST_CONVERSION_DATE,
4180 MCEI.COST_RATE_TYPE,
4181 MCEI.REVENUE_EXCHANGE_RATE,
4182 MCEI.REVENUE_CONVERSION_DATE,
4183 MCEI.REVENUE_RATE_TYPE,
4184 MCEI.TRANSFER_PRICE,
4185 MCEI.TP_EXCHANGE_RATE,
4186 MCEI.TP_CONVERSION_DATE,
4187 MCEI.TP_RATE_TYPE,
4188 MCEI.PROJFUNC_INV_RATE_TYPE,
4189 MCEI.PROJFUNC_INV_RATE_DATE,
4190 MCEI.PROJFUNC_INV_EXCHANGE_RATE,
4191 MCEI.PROJFUNC_FCST_RATE_TYPE,
4192 MCEI.PROJFUNC_FCST_RATE_DATE,
4193 MCEI.PROJFUNC_FCST_EXCHANGE_RATE,
4194 P_PROJECT_ID,
4195 P_PURGE_RELEASE,
4196 P_PURGE_BATCH_ID
4197 FROM
4198 PA_EXPENDITURE_ITEMS_AR EI,
4199 PA_MC_EXP_ITEMS_ALL MCEI
4200 WHERE
4201 EI.PURGE_PROJECT_ID = P_PROJECT_ID
4202 AND MCEI.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
4203 AND NOT EXISTS ( SELECT expenditure_item_id
4204 FROM
4205 PA_MC_EXP_ITEMS_AR
4206 WHERE
4207 purge_project_id = P_PROJECT_ID
4208 AND expenditure_item_id = mcei.expenditure_item_id
4209 AND set_of_books_id = mcei.set_of_books_id ) ;
4210
4211 x_MRC_NoOfRecordsIns := NVL(SQL%ROWCOUNT,0) ;
4212
4213 x_err_stack := l_old_err_stack ;
4214
4215 EXCEPTION
4216 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4217 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4218
4219 WHEN OTHERS THEN
4220 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCExpenditureItems' );
4221 pa_debug.debug('Error stage is '||x_err_stage );
4222 pa_debug.debug('Error stack is '||x_err_stack );
4223 pa_debug.debug(SQLERRM);
4224 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4225
4226 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4227
4228 END PA_MRCExpenditureItems ;
4229
4230 -- Start of comments
4231 -- API name : PA_MRCCostDistLines
4232 -- Type : Public
4233 -- Pre-reqs : None
4234 -- Function : This procedure purges all the Cost Distribution Lines that are
4235 -- not related to other expenditure items through
4236 -- transferred_from_exp_item_id.
4237 --
4238 -- Parameters : Refer to the comments of the previous procedure
4239 --
4240 -- End of comments
4241
4242
4243 PROCEDURE PA_MRCCostDistLines (
4244 p_purge_batch_id IN NUMBER,
4245 p_project_id IN NUMBER,
4246 p_txn_to_date IN DATE,
4247 p_purge_release IN VARCHAR2,
4248 p_archive_flag IN VARCHAR2,
4249 p_commit_size IN NUMBER,
4250 x_err_code IN OUT NOCOPY NUMBER,
4251 x_err_stack IN OUT NOCOPY VARCHAR2,
4252 x_err_stage IN OUT NOCOPY VARCHAR2,
4253 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4254 IS
4255
4256 l_old_err_stage VARCHAR2(2000);
4257 l_old_err_stack VARCHAR2(2000);
4258
4259 BEGIN
4260
4261 l_old_err_stack := x_err_stack;
4262
4263 x_err_stack := x_err_stack || ' ->Before insert into MRC PA_MC_CDL_AR ' ;
4264
4265 x_err_stage := 'Before insert into PA_MC_CDL_AR' ;
4266 INSERT INTO PA_MC_CDL_AR
4267 (SET_OF_BOOKS_ID,
4268 EXPENDITURE_ITEM_ID,
4269 LINE_NUM,
4270 LINE_TYPE,
4271 TRANSFER_STATUS_CODE,
4272 AMOUNT,
4273 QUANTITY,
4274 REQUEST_ID,
4275 PROGRAM_APPLICATION_ID,
4276 PROGRAM_ID,
4277 PROGRAM_UPDATE_DATE,
4278 TRANSFERRED_DATE,
4279 TRANSFER_REJECTION_REASON,
4280 BATCH_NAME,
4281 BURDENED_COST,
4282 CURRENCY_CODE,
4283 EXCHANGE_RATE,
4284 CONVERSION_DATE,
4285 PRC_ASSIGNMENT_ID,
4286 RATE_TYPE,
4287 PURGE_PROJECT_ID,
4288 PURGE_RELEASE ,
4289 PURGE_BATCH_ID )
4290 SELECT
4291 MC_CDL.SET_OF_BOOKS_ID,
4292 MC_CDL.EXPENDITURE_ITEM_ID,
4293 MC_CDL.LINE_NUM,
4294 MC_CDL.LINE_TYPE,
4295 MC_CDL.TRANSFER_STATUS_CODE,
4296 MC_CDL.AMOUNT,
4297 MC_CDL.QUANTITY,
4298 MC_CDL.REQUEST_ID,
4299 MC_CDL.PROGRAM_APPLICATION_ID,
4300 MC_CDL.PROGRAM_ID,
4301 MC_CDL.PROGRAM_UPDATE_DATE,
4302 MC_CDL.TRANSFERRED_DATE,
4303 MC_CDL.TRANSFER_REJECTION_REASON,
4304 MC_CDL.BATCH_NAME,
4305 MC_CDL.BURDENED_COST,
4306 MC_CDL.CURRENCY_CODE,
4307 MC_CDL.EXCHANGE_RATE,
4308 MC_CDL.CONVERSION_DATE,
4309 MC_CDL.PRC_ASSIGNMENT_ID,
4310 MC_CDL.RATE_TYPE,
4311 P_PURGE_BATCH_ID,
4312 P_PURGE_RELEASE,
4313 P_PROJECT_ID
4314 FROM
4315 PA_MC_COST_DIST_LINES_ALL MC_CDL,
4316 PA_COST_DIST_LINES_AR AR_CDL
4317 WHERE
4318 MC_CDL.EXPENDITURE_ITEM_ID = AR_CDL.EXPENDITURE_ITEM_ID
4319 AND MC_CDL.LINE_NUM = AR_CDL.LINE_NUM
4320 AND AR_CDL.PURGE_PROJECT_ID = P_PROJECT_ID
4321 AND NOT EXISTS (
4322 SELECT MC_CDL.expenditure_item_id
4323 FROM
4324 PA_MC_CDL_AR MC_AR_CDL
4325 WHERE
4326 MC_AR_CDL.purge_project_id = P_PROJECT_ID
4327 AND MC_AR_CDL.expenditure_item_id = MC_CDL.expenditure_item_id
4328 AND MC_AR_CDL.line_num = MC_CDL.line_num
4329 AND MC_AR_CDL.set_of_books_id = MC_CDL.set_of_books_id ) ;
4330
4331 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
4332
4333 x_err_stack := l_old_err_stack ;
4334
4335 EXCEPTION
4336 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4337 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4338
4339 WHEN OTHERS THEN
4340 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCCOSTDISTLINES');
4341 pa_debug.debug('Error stage is '|| x_err_stage );
4342 pa_debug.debug('Error stack is '|| x_err_stack );
4343 pa_debug.debug(SQLERRM);
4344 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4345
4346 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4347
4348 END PA_MRCCostDistLines ;
4349
4350
4351 -- Start of comments
4352 -- API name : PA_MRCCcDistLines
4353 -- Type : Public
4354 -- Pre-reqs : None
4355 -- Function : This procedure purges all the CC Distribution Lines in MRC.
4356 --
4357 -- Parameters : Refer to the comments of the previous procedure
4358 --
4359 -- End of comments
4360
4361
4362 PROCEDURE PA_MRCCcDistLines( p_purge_batch_id IN NUMBER,
4363 p_project_id IN NUMBER,
4364 p_txn_to_date IN DATE,
4365 p_purge_release IN VARCHAR2,
4366 p_archive_flag IN VARCHAR2,
4367 p_commit_size IN NUMBER,
4368 x_err_code IN OUT NOCOPY NUMBER,
4369 x_err_stack IN OUT NOCOPY VARCHAR2,
4370 x_err_stage IN OUT NOCOPY VARCHAR2,
4371 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4372 IS
4373
4374 l_old_err_stage VARCHAR2(2000);
4375 l_old_err_stack VARCHAR2(2000);
4376
4377 BEGIN
4378
4379 l_old_err_stack := x_err_stack;
4380
4381 x_err_stack := x_err_stack || ' ->Before insert into MRC PA_MC_CC_DIST_LINES_AR ' ;
4382
4383 x_err_stage := 'Before insert into PA_MC_CC_DIST_LINES_AR' ;
4384 INSERT INTO PA_MC_CC_DIST_LINES_AR
4385 (PURGE_BATCH_ID,
4386 PURGE_RELEASE,
4387 PURGE_PROJECT_ID,
4388 SET_OF_BOOKS_ID,
4389 PRC_ASSIGNMENT_ID,
4390 CC_DIST_LINE_ID,
4391 EXPENDITURE_ITEM_ID,
4392 LINE_NUM,
4393 LINE_TYPE,
4394 ACCT_CURRENCY_CODE,
4395 AMOUNT,
4396 PROGRAM_ID,
4397 PROGRAM_APPLICATION_ID,
4398 PROGRAM_UPDATE_DATE,
4399 REQUEST_ID,
4400 TRANSFER_STATUS_CODE,
4401 ACCT_TP_RATE_TYPE,
4402 ACCT_TP_RATE_DATE,
4403 ACCT_TP_EXCHANGE_RATE,
4404 GL_BATCH_NAME,
4405 TRANSFERRED_DATE,
4406 TRANSFER_REJECTION_CODE)
4407 SELECT
4408 P_PURGE_BATCH_ID,
4409 P_PURGE_RELEASE,
4410 P_PROJECT_ID,
4411 MC_CDL.SET_OF_BOOKS_ID,
4412 MC_CDL.PRC_ASSIGNMENT_ID,
4413 MC_CDL.CC_DIST_LINE_ID,
4414 MC_CDL.EXPENDITURE_ITEM_ID,
4415 MC_CDL.LINE_NUM,
4416 MC_CDL.LINE_TYPE,
4417 MC_CDL.ACCT_CURRENCY_CODE,
4418 MC_CDL.AMOUNT,
4419 MC_CDL.PROGRAM_ID,
4420 MC_CDL.PROGRAM_APPLICATION_ID,
4421 MC_CDL.PROGRAM_UPDATE_DATE,
4422 MC_CDL.REQUEST_ID,
4423 MC_CDL.TRANSFER_STATUS_CODE,
4424 MC_CDL.ACCT_TP_RATE_TYPE,
4425 MC_CDL.ACCT_TP_RATE_DATE,
4426 MC_CDL.ACCT_TP_EXCHANGE_RATE,
4427 MC_CDL.GL_BATCH_NAME,
4428 MC_CDL.TRANSFERRED_DATE,
4429 MC_CDL.TRANSFER_REJECTION_CODE
4430 FROM
4431 PA_MC_CC_DIST_LINES_ALL MC_CDL,
4432 PA_CC_DIST_LINES_AR AR_CDL
4433 WHERE
4434 MC_CDL.EXPENDITURE_ITEM_ID = AR_CDL.EXPENDITURE_ITEM_ID
4435 AND MC_CDL.LINE_NUM = AR_CDL.LINE_NUM
4436 AND AR_CDL.PURGE_PROJECT_ID = P_PROJECT_ID
4437 AND NOT EXISTS (
4438 SELECT MC_CDL.expenditure_item_id
4439 FROM
4440 PA_MC_CC_DIST_LINES_AR MC_AR_CDL
4441 WHERE
4442 MC_AR_CDL.purge_project_id = P_PROJECT_ID
4443 AND MC_AR_CDL.expenditure_item_id = MC_CDL.expenditure_item_id
4444 AND MC_AR_CDL.line_num = MC_CDL.line_num
4445 AND MC_AR_CDL.set_of_books_id = MC_CDL.set_of_books_id ) ;
4446
4447 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
4448
4449 x_err_stack := l_old_err_stack ;
4450
4451 EXCEPTION
4452 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4453 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4454
4455 WHEN OTHERS THEN
4456 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCCCDISTLINES');
4457 pa_debug.debug('Error stage is '|| x_err_stage );
4458 pa_debug.debug('Error stack is '|| x_err_stack );
4459 pa_debug.debug(SQLERRM);
4460 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4461
4462 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4463
4464 END PA_MRCCcDistLines ;
4465
4466 END pa_purge_costing;