[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_COSTING
Source
1 package body pa_purge_costing as
2 /* $Header: PAXCSPRB.pls 120.5 2010/11/23 06:45:10 jjgeorge ship $ */
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 ,location_id /*Payroll Intergration*/
2603 ,pay_element_type_id
2604 )
2605 select ei.receipt_currency_amount,
2606 ei.receipt_currency_code,
2607 ei.receipt_exchange_rate,
2608 ei.denom_currency_code,
2609 ei.denom_raw_cost,
2610 ei.denom_burdened_cost,
2611 ei.acct_currency_code,
2612 ei.acct_rate_date,
2613 ei.acct_rate_type,
2614 ei.acct_exchange_rate,
2615 ei.acct_raw_cost,
2616 ei.acct_burdened_cost,
2617 ei.acct_exchange_rounding_limit,
2618 ei.project_currency_code,
2619 ei.project_rate_date,
2620 ei.project_rate_type,
2621 ei.project_exchange_rate,
2622 ei.cc_cross_charge_code,
2623 ei.cc_prvdr_organization_id,
2624 ei.cc_recvr_organization_id,
2625 ei.cc_rejection_code,
2626 ei.denom_tp_currency_code,
2627 ei.denom_transfer_price,
2628 ei.acct_tp_rate_type,
2629 ei.acct_tp_rate_date,
2630 ei.acct_tp_exchange_rate,
2631 ei.acct_transfer_price,
2632 ei.projacct_transfer_price,
2633 ei.cc_markup_base_code,
2634 ei.tp_base_amount,
2635 ei.cc_cross_charge_type,
2636 ei.recvr_org_id,
2637 ei.cc_bl_distributed_code,
2638 ei.cc_ic_processed_code,
2639 ei.tp_ind_compiled_set_id,
2640 ei.tp_bill_rate,
2641 ei.tp_bill_markup_percentage,
2642 ei.tp_schedule_line_percentage,
2643 ei.tp_rule_percentage,
2644 ei.cc_prvdr_cost_reclass_code,
2645 ei.crl_asset_creation_status_code,
2646 ei.crl_asset_creation_rej_code,
2647 ei.cost_job_id,
2648 ei.tp_job_id,
2649 ei.prov_proj_bill_job_id,
2650 ei.cost_dist_warning_code,
2651 ei.project_tp_rate_date,
2652 ei.project_tp_rate_type,
2653 ei.project_tp_exchange_rate,
2654 ei.projfunc_tp_rate_date,
2655 ei.projfunc_tp_rate_type,
2656 ei.projfunc_tp_exchange_rate,
2657 ei.projfunc_transfer_price,
2658 ei.bill_trans_forecast_curr_code,
2659 ei.bill_trans_forecast_revenue,
2660 ei.projfunc_rev_rate_date,
2661 ei.projfunc_rev_exchange_rate,
2662 ei.projfunc_cost_rate_type,
2663 ei.projfunc_cost_rate_date,
2664 ei.projfunc_cost_exchange_rate,
2665 ei.project_raw_cost,
2666 ei.project_burdened_cost,
2667 ei.assignment_id,
2668 ei.work_type_id,
2669 ei.projfunc_raw_revenue,
2670 ei.project_bill_amount,
2671 ei.projfunc_currency_code,
2672 ei.project_raw_revenue,
2673 ei.project_transfer_price,
2674 ei.tp_amt_type_code,
2675 ei.bill_trans_currency_code,
2676 ei.bill_trans_raw_revenue,
2677 ei.bill_trans_bill_amount,
2678 ei.bill_trans_adjusted_revenue,
2679 ei.revproc_currency_code,
2680 ei.revproc_rate_type,
2681 ei.revproc_rate_date,
2682 ei.revproc_exchange_rate,
2683 ei.invproc_currency_code,
2684 ei.invproc_rate_type,
2685 ei.invproc_rate_date,
2686 ei.discount_percentage,
2687 ei.labor_multiplier,
2688 ei.amount_calculation_code,
2689 ei.bill_markup_percentage,
2690 ei.rate_source_id,
2691 ei.invproc_exchange_rate,
2692 ei.inv_gen_rejection_code,
2693 ei.projfunc_bill_amount,
2694 ei.project_rev_rate_type,
2695 ei.project_rev_rate_date,
2696 ei.project_rev_exchange_rate,
2697 ei.projfunc_rev_rate_type,
2698 ei.projfunc_inv_rate_type,
2699 ei.projfunc_inv_rate_date,
2700 ei.projfunc_inv_exchange_rate,
2701 ei.project_inv_rate_type,
2702 ei.project_inv_rate_date,
2703 ei.project_inv_exchange_rate,
2704 ei.projfunc_fcst_rate_type,
2705 ei.projfunc_fcst_rate_date,
2706 ei.projfunc_fcst_exchange_rate,
2707 ei.prvdr_accrual_date,
2708 ei.recvr_accrual_date,
2709 ei.quantity,
2710 ei.non_labor_resource,
2711 ei.organization_id,
2712 ei.override_to_organization_id,
2713 ei.denorm_id,
2714 ei.raw_cost,
2715 ei.raw_cost_rate,
2716 ei.burden_cost,
2717 ei.burden_cost_rate,
2718 ei.cost_dist_rejection_code,
2719 ei.labor_cost_multiplier_name,
2720 ei.raw_revenue,
2721 ei.bill_rate,
2722 ei.accrued_revenue,
2723 ei.accrual_rate,
2724 ei.adjusted_revenue,
2725 ei.adjusted_rate,
2726 ei.bill_amount,
2727 ei.forecast_revenue,
2728 ei.bill_rate_multiplier,
2729 ei.rev_dist_rejection_code,
2730 ei.event_num,
2731 ei.event_task_id,
2732 ei.bill_job_id,
2733 ei.bill_job_billing_title,
2734 ei.bill_employee_billing_title,
2735 ei.adjusted_expenditure_item_id,
2736 ei.net_zero_adjustment_flag,
2737 ei.transferred_from_exp_item_id,
2738 ei.converted_flag,
2739 ei.last_update_login,
2740 ei.request_id,
2741 ei.program_application_id,
2742 ei.program_id,
2743 ei.program_update_date,
2744 ei.attribute_category,
2745 ei.attribute1,
2746 ei.expenditure_item_id,
2747 ei.last_update_date,
2748 ei.last_updated_by,
2749 ei.creation_date,
2750 ei.created_by,
2751 ei.expenditure_id,
2752 ei.task_id,
2753 ei.expenditure_item_date,
2754 ei.expenditure_type,
2755 ei.cost_distributed_flag,
2756 ei.revenue_distributed_flag,
2757 ei.billable_flag,
2758 ei.bill_hold_flag,
2759 ei.attribute2,
2760 ei.attribute3,
2761 ei.attribute4,
2762 ei.attribute5,
2763 ei.attribute6,
2764 ei.attribute7,
2765 ei.attribute8,
2766 ei.attribute9,
2767 ei.attribute10,
2768 ei.cost_ind_compiled_set_id,
2769 ei.rev_ind_compiled_set_id,
2770 ei.inv_ind_compiled_set_id,
2771 ei.cost_burden_distributed_flag,
2772 ei.ind_cost_dist_rejection_code,
2773 ei.orig_transaction_reference,
2774 ei.transaction_source,
2775 ei.project_id,
2776 ei.source_expenditure_item_id,
2777 ei.job_id,
2778 ei.org_id,
2779 ei.system_linkage_function,
2780 ei.burden_sum_dest_run_id,
2781 p_purge_batch_id,
2782 p_purge_release,
2783 p_project_id,
2784 ei.RATE_DISC_REASON_CODE,
2785 ei.capital_event_id,
2786 ei.posted_denom_burdened_cost,
2787 ei.posted_project_burdened_cost,
2788 ei.posted_projfunc_burdened_cost,
2789 ei.posted_acct_burdened_cost,
2790 ei.adjustment_type,
2791 ei.Po_Line_Id, -- CWK and FPM Changes
2792 ei.Po_Price_Type, -- CWK and FPM Changes
2793 ei.Inventory_Item_Id, -- CWK and FPM Changes
2794 ei.Wip_Resource_Id, -- CWK and FPM Changes
2795 ei.Unit_Of_Measure, -- CWK and FPM Changes
2796 ei.document_header_id, -- R12 Change
2797 ei.document_distribution_id, -- R12 Change
2798 ei.document_line_number, -- R12 Change
2799 ei.document_payment_id, -- R12 Change
2800 ei.vendor_id, -- R12 Change
2801 ei.document_type, -- R12 Change
2802 ei.document_distribution_type -- R12 Change
2803 ,ei.location_id /*Payroll Intergration*/
2804 ,ei.pay_element_type_id
2805 from pa_expenditure_items_all ei
2806 where ei.rowid = l_ei_rowid_tab(exp_ind);
2807 l_NoOfRecordsIns := SQL%ROWCOUNT ;
2808 end if;
2809
2810
2811 /* Commented for the bug#2405916 and moved this to inside the if SQL%ROWCOUNT > 0 condition */
2812 /* */
2813
2814 if l_NoOfRecordsIns > 0 then
2815
2816 IF (l_mrc_flag = 'Y') THEN
2817 pa_purge_costing.PA_MRCExpenditureItems(
2818 p_purge_batch_id,
2819 p_project_id,
2820 p_txn_to_date,
2821 p_purge_release,
2822 p_archive_flag,
2823 l_commit_size,
2824 x_err_code,
2825 x_err_stack,
2826 x_err_stage,
2827 x_MRC_NoOfRecordsIns);
2828 END IF;
2829 END IF;
2830
2831 Select Pa_Expend_Item_Adj_Act_s.nextval
2832 into l_request_id
2833 from dual ;
2834
2835 FORALL exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
2836 insert into Pa_Expend_item_Adj_Activities
2837 ( expenditure_item_id,
2838 activity_date,
2839 last_update_date,
2840 last_updated_by,
2841 creation_date,
2842 created_by,
2843 exception_activity_code,
2844 module_code,
2845 last_update_login,
2846 request_id
2847 )
2848 select ei.expenditure_item_id,
2849 sysdate,
2850 sysdate,
2851 g_user,
2852 sysdate,
2853 g_user,
2854 'SOURCE PURGED',
2855 'PURGE PROCESS',
2856 g_user,
2857 l_request_id
2858 from pa_expenditure_items_all ei
2859 where ei.transferred_from_exp_item_id = l_exp_item_id_tab(exp_ind)
2860 and ei.transferred_from_exp_item_id is not null
2861 and not exists ( select pp.project_id
2862 from pa_purge_projects pp
2863 where pp.project_id = ei.project_id
2864 and pp.purge_batch_id = p_purge_batch_id ) ;
2865
2866 FORALL exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
2867 update pa_expenditure_items_all ei
2868 set ei.transferred_from_exp_item_id = NULL
2869 where ei.transferred_from_exp_item_id = l_exp_item_id_tab(exp_ind)
2870 and ei.transferred_from_exp_item_id is not null
2871 and not exists ( select pp.project_id
2872 from pa_purge_projects pp
2873 where pp.project_id = ei.project_id
2874 and pp.purge_batch_id = p_purge_batch_id ) ;
2875
2876 /* Each time thru the loop need to make sure that reset the
2877 * counter tracking the number of records that deleted from
2878 * the mrc table.
2879 */
2880 IF (l_mrc_flag = 'Y') THEN
2881 pa_utils2.MRC_row_count := 0;
2882 END IF;
2883
2884 -- We have a seperate delete statement if the archive option is
2885 -- selected because if archive option is selected the the records
2886 -- being purged will be those records which are already archived.
2887 -- table and
2888
2889 x_err_stage := 'PA_ExpenditureItems: Before deleting records from pa_expenditure_items_all';
2890 FORALL exp_ind IN l_ei_rowid_tab.FIRST .. l_ei_rowid_tab.LAST
2891 DELETE FROM PA_EXPENDITURE_ITEMS_ALL EI
2892 WHERE EI.ROWID = l_ei_rowid_tab(exp_ind);
2893
2894 l_NoOfRecordsDel := SQL%ROWCOUNT;
2895 l_MRC_NoOfRecordsDel := pa_utils2.MRC_row_count;
2896
2897
2898 IF l_NoOfRecordsDel > 0 THEN
2899 x_err_stage := 'PA_ExpenditureItems: Commiting the transaction' ;
2900 pa_purge.CommitProcess(p_purge_batch_id,
2901 p_project_id,
2902 'PA_EXPENDITURE_ITEMS',
2903 l_NoOfRecordsIns,
2904 l_NoOfRecordsDel,
2905 x_err_code,
2906 x_err_stack,
2907 x_err_stage,
2908 /* 'PA_MC_EXP_ITEMS_AR', */
2909 'PA_MC_EXP_ITEMS',
2910 x_MRC_NoOfRecordsIns,
2911 l_MRC_NoOfRecordsDel
2912 ) ;
2913
2914 end if ;
2915 end if ;
2916
2917 IF (l_fetch_complete) THEN
2918 Exit;
2919 END IF;
2920
2921 END LOOP ;
2922
2923 x_err_stack := l_old_err_stack ;
2924
2925 EXCEPTION
2926 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2927 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2928
2929 WHEN OTHERS THEN
2930 -- x_err_stage := l_err_stage ;
2931 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPENDITUREITEMS' );
2932 pa_debug.debug('Error stage is '||x_err_stage );
2933 pa_debug.debug('Error stack is '||x_err_stack );
2934 pa_debug.debug(SQLERRM);
2935 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2936
2937 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2938
2939 end PA_ExpenditureItems ;
2940
2941 -- Start of comments
2942 -- API name : PA_ExpItemsSrcPurge
2943 -- Type : Public
2944 -- Pre-reqs : None
2945 -- Function : This procedure all the expenditure items that are
2946 -- transferred to another expenditure item.
2947 --
2948 -- Parameters : Refer to the comments of the previous procedure
2949 --
2950 -- End of comments
2951
2952 procedure PA_ExpItemsSrcPurge ( p_purge_batch_id IN NUMBER,
2953 p_project_id IN NUMBER,
2954 p_txn_to_date IN DATE,
2955 p_purge_release IN VARCHAR2,
2956 p_archive_flag IN VARCHAR2,
2957 p_commit_size IN NUMBER,
2958 x_err_code IN OUT NOCOPY NUMBER,
2959 x_err_stack IN OUT NOCOPY VARCHAR2,
2960 x_err_stage IN OUT NOCOPY VARCHAR2
2961 ) is
2962
2963 l_old_err_stage VARCHAR2(2000);
2964 l_old_err_stack VARCHAR2(2000);
2965 l_NoOfRecordsIns NUMBER;
2966 l_NoOfRecordsDel NUMBER;
2967 l_Request_Id NUMBER;
2968
2969 begin
2970
2971
2972 l_old_err_stack := x_err_stack;
2973
2974 x_err_stack := x_err_stack || ' ->Before insert into PA_Expenditure_Items_AR' ;
2975
2976 if p_archive_flag = 'Y' then
2977 l_commit_size := trunc(p_commit_size / 4) ;
2978 else
2979
2980 l_commit_size := trunc(p_commit_size / 3) ;
2981 end if ;
2982
2983
2984 LOOP
2985 Select Pa_Expend_Item_Adj_Act_s.nextval
2986 into l_request_id
2987 from dual ;
2988 -- l_request_id := Pa_Expend_Item_Adj_Act_s.nextval ;
2989 x_err_stage := 'PA_ExpItemsSrcPurge: Before inserting audit records into Pa_Expend_item_Adj_Activities';
2990 if p_txn_to_date is NOT NULL then
2991 insert into Pa_Expend_item_Adj_Activities
2992 ( expenditure_item_id,
2993 activity_date,
2994 last_update_date,
2995 last_updated_by,
2996 creation_date,
2997 created_by,
2998 exception_activity_code,
2999 module_code,
3000 last_update_login,
3001 request_id
3002 )
3003 select ei.expenditure_item_id,
3004 sysdate,
3005 sysdate,
3006 g_user,
3007 sysdate,
3008 g_user,
3009 'SOURCE PURGED',
3010 'PURGE PROCESS',
3011 g_user,
3012 l_request_id
3013 from pa_expenditure_items_all ei
3014 where ei.transferred_from_exp_item_id in ( select ei1.expenditure_item_id
3015 from pa_expenditure_items_all ei1
3016 where ei1.expenditure_item_date <= p_txn_to_date
3017 and ei1.project_id = p_project_id )
3018 and ei.transferred_from_exp_item_id is not null
3019 and rownum < l_commit_size
3020 and not exists ( select pp.project_id
3021 from pa_purge_projects pp
3022 where pp.project_id = ei.project_id
3023 and pp.purge_batch_id = p_purge_batch_id ) ;
3024 else
3025 insert into Pa_Expend_item_Adj_Activities
3026 ( expenditure_item_id,
3027 activity_date,
3028 last_update_date,
3029 last_updated_by,
3030 creation_date,
3031 created_by,
3032 exception_activity_code,
3033 module_code,
3034 last_update_login,
3035 request_id
3036 )
3037 select ei.expenditure_item_id,
3038 sysdate,
3039 sysdate,
3040 g_user,
3041 sysdate,
3042 g_user,
3043 'SOURCE PURGED',
3044 'PURGE PROCESS',
3045 g_user,
3046 l_request_id
3047 from pa_tasks t,pa_expenditure_items_all ei
3048 where ei.transferred_from_exp_item_id in ( select ei1.expenditure_item_id
3049 from pa_expenditure_items_all ei1,
3050 pa_tasks t1
3051 where ei1.task_id = t1.task_id
3052 and t1.project_id = p_project_id )
3053 and ei.task_id = t.task_id
3054 and ei.transferred_from_exp_item_id is not null
3055 and rownum < l_commit_size
3056 and not exists ( select pp.project_id
3057 from pa_purge_projects pp
3058 where pp.project_id = t.project_id
3059 and pp.purge_batch_id = p_purge_batch_id ) ;
3060 end if;
3061
3062
3063
3064 if SQL%ROWCOUNT = 0 then
3065 exit ;
3066 else
3067
3068 /* Commented for archive purge performance....
3069 if p_archive_flag = 'Y' then
3070 -- If archive option is selected then the records are
3071 -- inserted into the archived into the archive tables
3072 -- before being purged. The where condition is such that
3073 -- only the it inserts half the no. of records specified
3074 -- in the commit size.
3075
3076 x_err_stage := 'PA_ExpItemsSrcPurge: Before inserting records into PA_Expenditure_Items_AR';
3077 insert into PA_Expenditure_Items_AR
3078 (
3079 receipt_currency_amount,
3080 receipt_currency_code,
3081 receipt_exchange_rate,
3082 denom_currency_code,
3083 denom_raw_cost,
3084 denom_burdened_cost,
3085 acct_currency_code,
3086 acct_rate_date,
3087 acct_rate_type,
3088 acct_exchange_rate,
3089 acct_raw_cost,
3090 acct_burdened_cost,
3091 acct_exchange_rounding_limit,
3092 project_currency_code,
3093 project_rate_date,
3094 project_rate_type,
3095 project_exchange_rate,
3096 cc_cross_charge_code,
3097 cc_prvdr_organization_id,
3098 cc_recvr_organization_id,
3099 cc_rejection_code,
3100 denom_tp_currency_code,
3101 denom_transfer_price,
3102 acct_tp_rate_type,
3103 acct_tp_rate_date,
3104 acct_tp_exchange_rate,
3105 acct_transfer_price,
3106 projacct_transfer_price,
3107 cc_markup_base_code,
3108 tp_base_amount,
3109 cc_cross_charge_type,
3110 recvr_org_id,
3111 cc_bl_distributed_code,
3112 cc_ic_processed_code,
3113 tp_ind_compiled_set_id,
3114 tp_bill_rate,
3115 tp_bill_markup_percentage,
3116 tp_schedule_line_percentage,
3117 tp_rule_percentage,
3118 cc_prvdr_cost_reclass_code,
3119 crl_asset_creation_status_code,
3120 crl_asset_creation_rej_code,
3121 cost_job_id,
3122 tp_job_id,
3123 prov_proj_bill_job_id,
3124 cost_dist_warning_code,
3125 project_tp_rate_date,
3126 project_tp_rate_type,
3127 project_tp_exchange_rate,
3128 projfunc_tp_rate_date,
3129 projfunc_tp_rate_type,
3130 projfunc_tp_exchange_rate,
3131 projfunc_transfer_price,
3132 bill_trans_forecast_curr_code,
3133 bill_trans_forecast_revenue,
3134 projfunc_rev_rate_date,
3135 projfunc_rev_exchange_rate,
3136 projfunc_cost_rate_type,
3137 projfunc_cost_rate_date,
3138 projfunc_cost_exchange_rate,
3139 project_raw_cost,
3140 project_burdened_cost,
3141 assignment_id,
3142 work_type_id,
3143 projfunc_raw_revenue,
3144 project_bill_amount,
3145 projfunc_currency_code,
3146 project_raw_revenue,
3147 project_transfer_price,
3148 tp_amt_type_code,
3149 bill_trans_currency_code,
3150 bill_trans_raw_revenue,
3151 bill_trans_bill_amount,
3152 bill_trans_adjusted_revenue,
3153 revproc_currency_code,
3154 revproc_rate_type,
3155 revproc_rate_date,
3156 revproc_exchange_rate,
3157 invproc_currency_code,
3158 invproc_rate_type,
3159 invproc_rate_date,
3160 discount_percentage,
3161 labor_multiplier,
3162 amount_calculation_code,
3163 bill_markup_percentage,
3164 rate_source_id,
3165 invproc_exchange_rate,
3166 inv_gen_rejection_code,
3167 projfunc_bill_amount,
3168 project_rev_rate_type,
3169 project_rev_rate_date,
3170 project_rev_exchange_rate,
3171 projfunc_rev_rate_type,
3172 projfunc_inv_rate_type,
3173 projfunc_inv_rate_date,
3174 projfunc_inv_exchange_rate,
3175 project_inv_rate_type,
3176 project_inv_rate_date,
3177 project_inv_exchange_rate,
3178 projfunc_fcst_rate_type,
3179 projfunc_fcst_rate_date,
3180 projfunc_fcst_exchange_rate,
3181 prvdr_accrual_date,
3182 recvr_accrual_date,
3183 quantity,
3184 non_labor_resource,
3185 organization_id,
3186 override_to_organization_id,
3187 denorm_id,
3188 raw_cost,
3189 raw_cost_rate,
3190 burden_cost,
3191 burden_cost_rate,
3192 cost_dist_rejection_code,
3193 labor_cost_multiplier_name,
3194 raw_revenue,
3195 bill_rate,
3196 accrued_revenue,
3197 accrual_rate,
3198 adjusted_revenue,
3199 adjusted_rate,
3200 bill_amount,
3201 forecast_revenue,
3202 bill_rate_multiplier,
3203 rev_dist_rejection_code,
3204 event_num,
3205 event_task_id,
3206 bill_job_id,
3207 bill_job_billing_title,
3208 bill_employee_billing_title,
3209 adjusted_expenditure_item_id,
3210 net_zero_adjustment_flag,
3211 transferred_from_exp_item_id,
3212 converted_flag,
3213 last_update_login,
3214 request_id,
3215 program_application_id,
3216 program_id,
3217 program_update_date,
3218 attribute_category,
3219 attribute1,
3220 expenditure_item_id,
3221 last_update_date,
3222 last_updated_by,
3223 creation_date,
3224 created_by,
3225 expenditure_id,
3226 task_id,
3227 expenditure_item_date,
3228 expenditure_type,
3229 cost_distributed_flag,
3230 revenue_distributed_flag,
3231 billable_flag,
3232 bill_hold_flag,
3233 attribute2,
3234 attribute3,
3235 attribute4,
3236 attribute5,
3237 attribute6,
3238 attribute7,
3239 attribute8,
3240 attribute9,
3241 attribute10,
3242 cost_ind_compiled_set_id,
3243 rev_ind_compiled_set_id,
3244 inv_ind_compiled_set_id,
3245 cost_burden_distributed_flag,
3246 ind_cost_dist_rejection_code,
3247 orig_transaction_reference,
3248 transaction_source,
3249 project_id,
3250 source_expenditure_item_id,
3251 job_id,
3252 org_id,
3253 System_Linkage_Function,
3254 Burden_Sum_Dest_Run_Id,
3255 purge_batch_id,
3256 purge_release,
3257 purge_project_id,
3258 RATE_DISC_REASON_CODE,
3259 posted_denom_burdened_cost,
3260 posted_project_burdened_cost,
3261 posted_projfunc_burdened_cost,
3262 posted_acct_burdened_cost,
3263 adjustment_type
3264 )
3265 select ei.receipt_currency_amount,
3266 ei.receipt_currency_code,
3267 ei.receipt_exchange_rate,
3268 ei.denom_currency_code,
3269 ei.denom_raw_cost,
3270 ei.denom_burdened_cost,
3271 ei.acct_currency_code,
3272 ei.acct_rate_date,
3273 ei.acct_rate_type,
3274 ei.acct_exchange_rate,
3275 ei.acct_raw_cost,
3276 ei.acct_burdened_cost,
3277 ei.acct_exchange_rounding_limit,
3278 ei.project_currency_code,
3279 ei.project_rate_date,
3280 ei.project_rate_type,
3281 ei.project_exchange_rate,
3282 ei.cc_cross_charge_code,
3283 ei.cc_prvdr_organization_id,
3284 ei.cc_recvr_organization_id,
3285 ei.cc_rejection_code,
3286 ei.denom_tp_currency_code,
3287 ei.denom_transfer_price,
3288 ei.acct_tp_rate_type,
3289 ei.acct_tp_rate_date,
3290 ei.acct_tp_exchange_rate,
3291 ei.acct_transfer_price,
3292 ei.projacct_transfer_price,
3293 ei.cc_markup_base_code,
3294 ei.tp_base_amount,
3295 ei.cc_cross_charge_type,
3296 ei.recvr_org_id,
3297 ei.cc_bl_distributed_code,
3298 ei.cc_ic_processed_code,
3299 ei.tp_ind_compiled_set_id,
3300 ei.tp_bill_rate,
3301 ei.tp_bill_markup_percentage,
3302 ei.tp_schedule_line_percentage,
3303 ei.tp_rule_percentage,
3304 ei.cc_prvdr_cost_reclass_code,
3305 ei.crl_asset_creation_status_code,
3306 ei.crl_asset_creation_rej_code,
3307 ei.cost_job_id,
3308 ei.tp_job_id,
3309 ei.prov_proj_bill_job_id,
3310 ei.cost_dist_warning_code,
3311 ei.project_tp_rate_date,
3312 ei.project_tp_rate_type,
3313 ei.project_tp_exchange_rate,
3314 ei.projfunc_tp_rate_date,
3315 ei.projfunc_tp_rate_type,
3316 ei.projfunc_tp_exchange_rate,
3317 ei.projfunc_transfer_price,
3318 ei.bill_trans_forecast_curr_code,
3319 ei.bill_trans_forecast_revenue,
3320 ei.projfunc_rev_rate_date,
3321 ei.projfunc_rev_exchange_rate,
3322 ei.projfunc_cost_rate_type,
3323 ei.projfunc_cost_rate_date,
3324 ei.projfunc_cost_exchange_rate,
3325 ei.project_raw_cost,
3326 ei.project_burdened_cost,
3327 ei.assignment_id,
3328 ei.work_type_id,
3329 ei.projfunc_raw_revenue,
3330 ei.project_bill_amount,
3331 ei.projfunc_currency_code,
3332 ei.project_raw_revenue,
3333 ei.project_transfer_price,
3334 ei.tp_amt_type_code,
3335 ei.bill_trans_currency_code,
3336 ei.bill_trans_raw_revenue,
3337 ei.bill_trans_bill_amount,
3338 ei.bill_trans_adjusted_revenue,
3339 ei.revproc_currency_code,
3340 ei.revproc_rate_type,
3341 ei.revproc_rate_date,
3342 ei.revproc_exchange_rate,
3343 ei.invproc_currency_code,
3344 ei.invproc_rate_type,
3345 ei.invproc_rate_date,
3346 ei.discount_percentage,
3347 ei.labor_multiplier,
3348 ei.amount_calculation_code,
3349 ei.bill_markup_percentage,
3350 ei.rate_source_id,
3351 ei.invproc_exchange_rate,
3352 ei.inv_gen_rejection_code,
3353 ei.projfunc_bill_amount,
3354 ei.project_rev_rate_type,
3355 ei.project_rev_rate_date,
3356 ei.project_rev_exchange_rate,
3357 ei.projfunc_rev_rate_type,
3358 ei.projfunc_inv_rate_type,
3359 ei.projfunc_inv_rate_date,
3360 ei.projfunc_inv_exchange_rate,
3361 ei.project_inv_rate_type,
3362 ei.project_inv_rate_date,
3363 ei.project_inv_exchange_rate,
3364 ei.projfunc_fcst_rate_type,
3365 ei.projfunc_fcst_rate_date,
3366 ei.projfunc_fcst_exchange_rate,
3367 ei.prvdr_accrual_date,
3368 ei.recvr_accrual_date,
3369 ei.quantity,
3370 ei.non_labor_resource,
3371 ei.organization_id,
3372 ei.override_to_organization_id,
3373 ei.denorm_id,
3374 ei.raw_cost,
3375 ei.raw_cost_rate,
3376 ei.burden_cost,
3377 ei.burden_cost_rate,
3378 ei.cost_dist_rejection_code,
3379 ei.labor_cost_multiplier_name,
3380 ei.raw_revenue,
3381 ei.bill_rate,
3382 ei.accrued_revenue,
3383 ei.accrual_rate,
3384 ei.adjusted_revenue,
3385 ei.adjusted_rate,
3386 ei.bill_amount,
3387 ei.forecast_revenue,
3388 ei.bill_rate_multiplier,
3389 ei.rev_dist_rejection_code,
3390 ei.event_num,
3391 ei.event_task_id,
3392 ei.bill_job_id,
3393 ei.bill_job_billing_title,
3394 ei.bill_employee_billing_title,
3395 ei.adjusted_expenditure_item_id,
3396 ei.net_zero_adjustment_flag,
3397 ei.transferred_from_exp_item_id,
3398 ei.converted_flag,
3399 ei.last_update_login,
3400 ei.request_id,
3401 ei.program_application_id,
3402 ei.program_id,
3403 ei.program_update_date,
3404 ei.attribute_category,
3405 ei.attribute1,
3406 ei.expenditure_item_id,
3407 ei.last_update_date,
3408 ei.last_updated_by,
3409 ei.creation_date,
3410 ei.created_by,
3411 ei.expenditure_id,
3412 ei.task_id,
3413 ei.expenditure_item_date,
3414 ei.expenditure_type,
3415 ei.cost_distributed_flag,
3416 ei.revenue_distributed_flag,
3417 ei.billable_flag,
3418 ei.bill_hold_flag,
3419 ei.attribute2,
3420 ei.attribute3,
3421 ei.attribute4,
3422 ei.attribute5,
3423 ei.attribute6,
3424 ei.attribute7,
3425 ei.attribute8,
3426 ei.attribute9,
3427 ei.attribute10,
3428 ei.cost_ind_compiled_set_id,
3429 ei.rev_ind_compiled_set_id,
3430 ei.inv_ind_compiled_set_id,
3431 ei.cost_burden_distributed_flag,
3432 ei.ind_cost_dist_rejection_code,
3433 ei.orig_transaction_reference,
3434 ei.transaction_source,
3435 ei.project_id,
3436 ei.source_expenditure_item_id,
3437 ei.job_id,
3438 ei.org_id,
3439 ei.System_Linkage_Function,
3440 ei.Burden_Sum_Dest_Run_Id,
3441 p_purge_batch_id,
3442 p_purge_release,
3443 p_project_id,
3444 ei.RATE_DISC_REASON_CODE
3445 ei.posted_denom_burdened_cost,
3446 ei.posted_project_burdened_cost,
3447 ei.posted_projfunc_burdened_cost,
3448 ei.posted_acct_burdened_cost,
3449 ei.adjustment_type
3450 from pa_expenditure_items_all ei
3451 where ei.expenditure_item_id in ( select ei1.transferred_from_exp_item_id
3452 from Pa_Expend_item_Adj_Activities eia,
3453 pa_expenditure_items_all ei1
3454 where ei1.expenditure_item_id = eia.expenditure_item_id
3455 and eia.request_id = l_request_id
3456 and eia.exception_activity_code= 'SOURCE PURGED'
3457 and ei1.transferred_from_exp_item_id is not null ) ;
3458
3459
3460 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3461
3462 end if ;
3463
3464 x_err_stage := 'PA_ExpItemsSrcPurge: Deleting records into pa_expenditure_items_all';
3465 delete from pa_expenditure_items_all ei
3466 where ei.expenditure_item_id in ( select ei1.transferred_from_exp_item_id
3467 from Pa_Expend_item_Adj_Activities eia,
3468 pa_expenditure_items_all ei1
3469 where ei1.expenditure_item_id = eia.expenditure_item_id
3470 and eia.request_id = l_request_id
3471 and eia.exception_activity_code= 'SOURCE PURGED'
3472 and ei1.transferred_from_exp_item_id is not null ) ;
3473
3474
3475 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3476 */
3477
3478 x_err_stage := 'PA_ExpItemsSrcPurge: Deleting the links between expenditure items' ;
3479 update pa_expenditure_items_all ei
3480 set ei.transferred_from_exp_item_id = NULL
3481 where ei.expenditure_item_id in ( select eia.expenditure_item_id
3482 from Pa_Expend_item_Adj_Activities eia
3483 where eia.request_id = l_request_id
3484 and eia.exception_activity_code= 'SOURCE PURGED')
3485 and ei.transferred_from_exp_item_id is not null ;
3486
3487
3488 -- After "deleting" or "deleting and inserting" a set of records
3489 -- the transaction is commited. This also creates a record in the
3490 -- Pa_Purge_Project_details which will show the no. of records
3491 -- that are purged from each table.
3492
3493 x_err_stage := 'PA_ExpenditureComments: Commiting the transaction' ;
3494
3495 /* */
3496 end if;
3497 END LOOP ;
3498
3499 x_err_stack := l_old_err_stack ;
3500
3501 EXCEPTION
3502 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3503 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3504
3505 WHEN OTHERS THEN
3506 -- x_err_stage := l_err_stage ;
3507 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPITEMSSRCPURGE' );
3508 pa_debug.debug('Error stage is '||x_err_stage );
3509 pa_debug.debug('Error stack is '||x_err_stack );
3510 pa_debug.debug(SQLERRM);
3511 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3512
3513 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3514
3515 end PA_ExpItemsSrcPurge ;
3516
3517 -- Start of comments
3518 -- API name : PA_ExpItemsDestPurge
3519 -- Type : Public
3520 -- Pre-reqs : None
3521 -- Function : This procedure purges all expenditure items that were
3522 -- transferred from some other expenditure item.
3523 --
3524 -- Parameters : Refer to the comments of the previous procedure
3525 --
3526 -- End of comments
3527
3528 procedure PA_ExpItemsDestPurge( p_purge_batch_id IN NUMBER,
3529 p_project_id IN NUMBER,
3530 p_txn_to_date IN DATE,
3531 p_purge_release IN VARCHAR2,
3532 p_archive_flag IN VARCHAR2,
3533 p_commit_size IN NUMBER,
3534 x_err_code IN OUT NOCOPY NUMBER,
3535 x_err_stack IN OUT NOCOPY VARCHAR2,
3536 x_err_stage IN OUT NOCOPY VARCHAR2
3537 ) is
3538
3539 l_old_err_stage VARCHAR2(2000);
3540 l_old_err_stack VARCHAR2(2000);
3541 l_NoOfRecordsIns NUMBER;
3542 l_NoOfRecordsDel NUMBER;
3543 l_Request_Id NUMBER;
3544 l_exp_ind NUMBER;
3545 l_fetch_complete BOOLEAN:= FALSE;
3546
3547 cursor c_exp_open_lines is
3548 select ei.transferred_from_exp_item_id
3549 from pa_expenditure_items_all ei
3550 where ei.expenditure_item_date <= p_txn_to_date
3551 and ei.transferred_from_exp_item_id is not null
3552 and ei.project_id = p_project_id;
3553
3554 cursor c_exp_close_lines is
3555 select ei.transferred_from_exp_item_id
3556 from pa_expenditure_items_all ei
3557 where ei.transferred_from_exp_item_id is not null
3558 and ei.project_id = p_project_id;
3559
3560 l_exp_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3561 l_exp_item_id_tab_emp PA_PLSQL_DATATYPES.IdTabTyp;
3562
3563 begin
3564
3565
3566 l_old_err_stack := x_err_stack;
3567
3568 x_err_stack := x_err_stack || ' ->Before insert into PA_Expenditure_Items_AR' ;
3569
3570 if p_archive_flag = 'Y' then
3571 l_commit_size := trunc(p_commit_size / 5) ;
3572 else
3573
3574 l_commit_size := trunc(p_commit_size / 3) ;
3575 end if ;
3576
3577 IF p_txn_to_date is not null THEN
3578 OPEN c_exp_open_lines;
3579 ELSE
3580 OPEN c_exp_close_lines;
3581 END IF;
3582
3583 LOOP
3584 l_exp_item_id_tab := l_exp_item_id_tab_emp;
3585 IF p_txn_to_date is not null THEN
3586 FETCH c_exp_open_lines BULK COLLECT INTO l_exp_item_id_tab LIMIT l_commit_size;
3587 IF c_exp_open_lines%NOTFOUND THEN
3588 CLOSE c_exp_open_lines;
3589 l_fetch_complete := TRUE;
3590 END IF;
3591 ELSE
3592 FETCH c_exp_close_lines BULK COLLECT INTO l_exp_item_id_tab LIMIT l_commit_size;
3593 IF c_exp_close_lines%NOTFOUND THEN
3594 CLOSE c_exp_close_lines;
3595 l_fetch_complete := TRUE;
3596 END IF;
3597 END IF;
3598
3599 IF (nvl(l_exp_item_id_tab.LAST,0) > 0 ) THEN
3600 Select Pa_Expend_Item_Adj_Act_s.nextval
3601 into l_request_id
3602 from dual ;
3603 -- l_request_id := Pa_Expend_Item_Adj_Act_s.nextval ;
3604 x_err_stage := 'PA_ExpItemsDestPurge: Before inserting audit records ' ;
3605 /* if p_txn_to_date is NOT NULL then */
3606 FORALL l_exp_ind IN l_exp_item_id_tab.FIRST .. l_exp_item_id_tab.LAST
3607 insert into Pa_Expend_item_Adj_Activities
3608 ( expenditure_item_id,
3609 activity_date,
3610 last_update_date,
3611 last_updated_by,
3612 creation_date,
3613 created_by,
3614 exception_activity_code,
3615 module_code,
3616 last_update_login,
3617 request_id
3618 )
3619 select ei.expenditure_item_id,
3620 sysdate,
3621 sysdate,
3622 g_user,
3623 sysdate,
3624 g_user,
3625 'DESTINATION PURGED',
3626 'PURGE PROCESS',
3627 g_user,
3628 l_Request_Id
3629 from pa_expenditure_items_all ei
3630 where ei.expenditure_item_id = l_exp_item_id_tab(l_exp_ind)
3631 /* in ( select ei1.transferred_from_exp_item_id
3632 from pa_expenditure_items_all ei1
3633 where ei1.expenditure_item_date <= p_txn_to_date
3634 and ei1.transferred_from_exp_item_id is not null
3635 and ei1.project_id = p_project_id )
3636 and rownum < l_commit_size */
3637 and not exists ( select pp.project_id
3638 from pa_purge_projects pp
3639 where pp.project_id = ei.project_id
3640 and pp.purge_batch_id = p_purge_batch_id );
3641 /* else */
3642 /* end if; */
3643
3644 /* if SQL%ROWCOUNT = 0 then
3645 exit ; */
3646
3647 /* Commented for performance issue..... */
3648 end if;
3649 IF ( l_fetch_complete ) THEN
3650 exit;
3651 END IF;
3652 END LOOP ;
3653
3654 x_err_stack := l_old_err_stack ;
3655
3656 EXCEPTION
3657 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3658 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3659
3660 WHEN OTHERS THEN
3661 -- x_err_stage := l_err_stage ;
3662 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPITEMSDESTPURGE' );
3663 pa_debug.debug('Error stage is '||x_err_stage );
3664 pa_debug.debug('Error stack is '||x_err_stack );
3665 pa_debug.debug(SQLERRM);
3666 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3667
3668 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3669
3670 end PA_ExpItemsDestPurge ;
3671
3672 -- Start of comments
3673 -- API name : PA_Routings1
3674 -- Type : Public
3675 -- Pre-reqs : None
3676 -- Function : This procedure purges all the routing records whose expenditures
3677 -- does not have any expenditure items.
3678 --
3679 -- Parameters : Refer to the comments of the previous procedure
3680 --
3681 -- End of comments
3682
3683 procedure PA_Routings1 ( p_purge_batch_id IN NUMBER,
3684 p_project_id IN NUMBER,
3685 p_purge_release IN VARCHAR2,
3686 p_archive_flag IN VARCHAR2,
3687 p_commit_size IN NUMBER,
3688 x_err_code IN OUT NOCOPY NUMBER,
3689 x_err_stack IN OUT NOCOPY VARCHAR2,
3690 x_err_stage IN OUT NOCOPY VARCHAR2
3691 ) is
3692
3693 l_old_err_stage VARCHAR2(2000);
3694 l_old_err_stack VARCHAR2(2000);
3695 l_NoOfRecordsIns NUMBER;
3696 l_NoOfRecordsDel NUMBER;
3697 begin
3698
3699 l_old_err_stack := x_err_stack;
3700
3701 x_err_stack := x_err_stack || ' ->Before insert into Routings_AR' ;
3702
3703 LOOP
3704 if p_archive_flag = 'Y' then
3705 -- If archive option is selected then the records are
3706 -- inserted into the archived into the archive tables
3707 -- before being purged. The where condition is such that
3708 -- only the it inserts half the no. of records specified
3709 -- in the commit size.
3710
3711 l_commit_size := p_commit_size / 2 ;
3712
3713 x_err_stage := 'PA_Routings1: Before inserting records into PA_Routings_AR' ;
3714 insert into PA_Routings_AR
3715 (
3716 Expenditure_Id,
3717 Routed_From_Person_Id,
3718 Start_Date,
3719 Routing_Status_Code,
3720 Creation_Date,
3721 Created_By,
3722 Last_Update_Date,
3723 Last_Updated_By,
3724 Last_Update_Login,
3725 Routed_To_Person_Id,
3726 End_Date,
3727 Routing_Comment,
3728 purge_batch_id,
3729 purge_release,
3730 purge_project_id
3731 )
3732 Select ro.Expenditure_Id,
3733 ro.Routed_From_Person_Id,
3734 ro.Start_Date,
3735 ro.Routing_Status_Code,
3736 ro.Creation_Date,
3737 ro.Created_By,
3738 ro.Last_Update_Date,
3739 ro.Last_Updated_By,
3740 ro.Last_Update_Login,
3741 ro.Routed_To_Person_Id,
3742 ro.End_Date,
3743 ro.Routing_Comment,
3744 p_purge_batch_id,
3745 p_purge_release,
3746 p_project_id
3747 from pa_routings ro
3748 where not exists
3749 ( select x.expenditure_id
3750 from pa_expenditures_all x
3751 where ro.expenditure_id = x.expenditure_id)
3752 and rownum < l_commit_size ;
3753
3754 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3755
3756 if SQL%ROWCOUNT > 0 then
3757 -- We have a seperate delete statement if the archive option is
3758 -- selected because if archive option is selected the the records
3759 -- being purged will be those records which are already archived.
3760 -- table and
3761
3762 x_err_stage := 'PA_Routings1: Before deleting records from pa_routings' ;
3763
3764 delete from pa_routings ro
3765 where (ro.expenditure_id, ro.start_date ) in
3766 ( select roar.expenditure_id, roar.start_date
3767 from pa_routings_ar roar
3768 where roar.purge_project_id = p_project_id
3769 ) ;
3770
3771 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3772 end if ;
3773 else
3774
3775 l_commit_size := p_commit_size ;
3776
3777 -- If the archive option is not selected then the delete will
3778 -- be based on the commit size.
3779
3780 x_err_stage := 'PA_Routings1: Before deleting records from pa_routings' ;
3781 delete from pa_routings ro
3782 where not exists
3783 ( select x.expenditure_id
3784 from pa_expenditures_all x
3785 where ro.expenditure_id = x.expenditure_id)
3786 and rownum < l_commit_size ;
3787
3788 l_NoOfRecordsDel := SQL%ROWCOUNT ;
3789 end if ;
3790
3791 if SQL%ROWCOUNT = 0 then
3792 -- Once the SqlCount becomes 0, which means that there are
3793 -- no more records to be purged then we exit the loop.
3794
3795 x_err_stage := 'PA_Routings1: No more records to archive / purge ' ;
3796 exit ;
3797
3798 else
3799 -- After "deleting" or "deleting and inserting" a set of records
3800 -- the transaction is commited. This also creates a record in the
3801 -- Pa_Purge_Project_details which will show the no. of records
3802 -- that are purged from each table.
3803
3804 x_err_stage := 'PA_Routings1: Commiting the transaction' ;
3805 pa_purge.CommitProcess(p_purge_batch_id,
3806 p_project_id,
3807 'PA_ROUTINGS',
3808 l_NoOfRecordsIns,
3809 l_NoOfRecordsDel,
3810 x_err_code,
3811 x_err_stack,
3812 x_err_stage
3813 ) ;
3814
3815 end if ;
3816 END LOOP ;
3817
3818 x_err_stack := l_old_err_stack ;
3819
3820 EXCEPTION
3821 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3822 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3823
3824 WHEN OTHERS THEN
3825 -- x_err_stage := l_err_stage ;
3826 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_ROUTINGS1' );
3827 pa_debug.debug('Error stage is '||x_err_stage );
3828 pa_debug.debug('Error stack is '||x_err_stack );
3829 pa_debug.debug(SQLERRM);
3830 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3831
3832 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3833
3834 end PA_Routings1 ;
3835
3836 -- Start of comments
3837 -- API name : PA_Expenditures1
3838 -- Type : Public
3839 -- Pre-reqs : None
3840 -- Function : This procedure purges all the expenditures that does not
3841 -- have any expenditure items.
3842 --
3843 -- Parameters : Refer to the comments of the previous procedure
3844 --
3845 -- End of comments
3846
3847 procedure PA_Expenditures1 ( p_purge_batch_id IN NUMBER,
3848 p_project_id IN NUMBER,
3849 p_purge_release IN VARCHAR2,
3850 p_archive_flag IN VARCHAR2,
3851 p_commit_size IN NUMBER,
3852 x_err_code IN OUT NOCOPY NUMBER,
3853 x_err_stack IN OUT NOCOPY VARCHAR2,
3854 x_err_stage IN OUT NOCOPY VARCHAR2
3855 ) is
3856
3857 l_old_err_stage VARCHAR2(2000);
3858 l_old_err_stack VARCHAR2(2000);
3859 l_NoOfRecordsIns NUMBER;
3860 l_NoOfRecordsDel NUMBER;
3861 begin
3862
3863 l_old_err_stack := x_err_stack;
3864
3865 x_err_stack := x_err_stack || ' ->Before insert into Expenditures_AR' ;
3866
3867 LOOP
3868 if p_archive_flag = 'Y' then
3869 -- If archive option is selected then the records are
3870 -- inserted into the archived into the archive tables
3871 -- before being purged. The where condition is such that
3872 -- only the it inserts half the no. of records specified
3873 -- in the commit size.
3874
3875 l_commit_size := p_commit_size / 2 ;
3876
3877 x_err_stage := 'PA_Expenditures1: Before insert into PA_Expenditures_AR' ;
3878 insert into PA_Expenditures_AR
3879 (
3880 Expenditure_Id,
3881 Last_Update_Date,
3882 Last_Updated_By,
3883 Creation_Date,
3884 Created_By,
3885 Expenditure_Status_Code,
3886 Expenditure_Ending_Date,
3887 Expenditure_Class_Code,
3888 Incurred_By_Person_Id,
3889 Incurred_By_Organization_Id,
3890 Expenditure_Group,
3891 Control_Total_Amount,
3892 Entered_By_Person_Id,
3893 Description,
3894 Initial_Submission_Date,
3895 Last_Update_Login,
3896 Request_Id,
3897 Program_Id,
3898 Program_Application_Id,
3899 Program_Update_Date,
3900 Attribute_Category,
3901 Attribute1,
3902 Attribute2,
3903 Attribute3,
3904 Attribute4,
3905 Attribute5,
3906 Attribute6,
3907 Attribute7,
3908 Attribute8,
3909 Attribute9,
3910 Attribute10,
3911 Pte_Reference,
3912 Org_Id,
3913 OVERRIDING_APPROVER_PERSON_ID,
3914 WF_STATUS_CODE,
3915 TRANSFER_STATUS_CODE,
3916 ORIG_EXP_TXN_REFERENCE1,
3917 ORIG_USER_EXP_TXN_REFERENCE,
3918 ORIG_EXP_TXN_REFERENCE2,
3919 ORIG_EXP_TXN_REFERENCE3,
3920 USER_BATCH_NAME,
3921 DENOM_CURRENCY_CODE,
3922 ACCT_CURRENCY_CODE,
3923 ACCT_RATE_DATE,
3924 ACCT_RATE_TYPE,
3925 ACCT_EXCHANGE_RATE,
3926 VENDOR_ID,
3927 purge_batch_id,
3928 purge_release,
3929 purge_project_id,
3930 Person_Type -- CWK and FPM Changes
3931 )
3932 Select x.Expenditure_Id,
3933 x.Last_Update_Date,
3934 x.Last_Updated_By,
3935 x.Creation_Date,
3936 x.Created_By,
3937 x.Expenditure_Status_Code,
3938 x.Expenditure_Ending_Date,
3939 x.Expenditure_Class_Code,
3940 x.Incurred_By_Person_Id,
3941 x.Incurred_By_Organization_Id,
3942 x.Expenditure_Group,
3943 x.Control_Total_Amount,
3944 x.Entered_By_Person_Id,
3945 x.Description,
3946 x.Initial_Submission_Date,
3947 x.Last_Update_Login,
3948 x.Request_Id,
3949 x.Program_Id,
3950 x.Program_Application_Id,
3951 x.Program_Update_Date,
3952 x.Attribute_Category,
3953 x.Attribute1,
3954 x.Attribute2,
3955 x.Attribute3,
3956 x.Attribute4,
3957 x.Attribute5,
3958 x.Attribute6,
3959 x.Attribute7,
3960 x.Attribute8,
3961 x.Attribute9,
3962 x.Attribute10,
3963 x.Pte_Reference,
3964 x.Org_Id,
3965 x.OVERRIDING_APPROVER_PERSON_ID,
3966 x.WF_STATUS_CODE,
3967 x.TRANSFER_STATUS_CODE,
3968 x.ORIG_EXP_TXN_REFERENCE1,
3969 x.ORIG_USER_EXP_TXN_REFERENCE,
3970 x.ORIG_EXP_TXN_REFERENCE2,
3971 x.ORIG_EXP_TXN_REFERENCE3,
3972 x.USER_BATCH_NAME,
3973 x.DENOM_CURRENCY_CODE,
3974 x.ACCT_CURRENCY_CODE,
3975 x.ACCT_RATE_DATE,
3976 x.ACCT_RATE_TYPE,
3977 x.ACCT_EXCHANGE_RATE,
3978 x.VENDOR_ID,
3979 p_purge_batch_id,
3980 p_purge_release,
3981 p_project_id,
3982 x.Person_Type -- CWK and FPM Changes
3983
3984 from pa_expenditures_all x
3985 where (x.rowid ) in
3986 ( select x1.rowid
3987 from pa_expenditures_all x1
3988 where not exists ( select ei.expenditure_id
3989 from pa_expenditure_items_all ei
3990 where ei.expenditure_id = x1.expenditure_id)
3991 and x1.expenditure_status_code = 'APPROVED'
3992 and rownum < l_commit_size
3993 ) ;
3994
3995 l_NoOfRecordsIns := SQL%ROWCOUNT ;
3996
3997 if SQL%ROWCOUNT > 0 then
3998 -- We have a seperate delete statement if the archive option is
3999 -- selected because if archive option is selected the the records
4000 -- being purged will be those records which are already archived.
4001 -- table and
4002
4003 x_err_stage := 'PA_Expenditures1: Before deleting records from pa_expenditures_all' ;
4004 delete from pa_expenditures_all x
4005 where (x.rowid ) in
4006 ( select x1.rowid
4007 from pa_expenditures_all x1,
4008 pa_expenditures_ar x2
4009 where x2.expenditure_id = x1.expenditure_id
4010 and x2.purge_project_id = p_project_id
4011 ) ;
4012
4013 l_NoOfRecordsDel := SQL%ROWCOUNT ;
4014 end if ;
4015 else
4016
4017 l_commit_size := p_commit_size ;
4018
4019 -- If the archive option is not selected then the delete will
4020 -- be based on the commit size.
4021
4022 x_err_stage := 'PA_Expenditures1: Before deleting records from pa_expenditures_all' ;
4023 delete from pa_expenditures_all x
4024 where (x.rowid ) in
4025 ( select x1.rowid
4026 from pa_expenditures_all x1
4027 where not exists ( select ei.expenditure_id
4028 from pa_expenditure_items_all ei
4029 where ei.expenditure_id = x1.expenditure_id)
4030 and x1.expenditure_status_code = 'APPROVED'
4031 and rownum < l_commit_size
4032 ) ;
4033
4034 l_NoOfRecordsDel := SQL%ROWCOUNT ;
4035 end if ;
4036
4037 if SQL%ROWCOUNT = 0 then
4038 -- Once the SqlCount becomes 0, which means that there are
4039 -- no more records to be purged then we exit the loop.
4040
4041 x_err_stage := 'PA_Expenditures1: No more records to archive / purge ' ;
4042 exit ;
4043
4044 else
4045 -- After "deleting" or "deleting and inserting" a set of records
4046 -- the transaction is commited. This also creates a record in the
4047 -- Pa_Purge_Project_details which will show the no. of records
4048 -- that are purged from each table.
4049
4050 x_err_stage := 'PA_Expenditures1: Commiting the transaction' ;
4051 pa_purge.CommitProcess(p_purge_batch_id,
4052 p_project_id,
4053 'PA_EXPENDITURES_ALL',
4054 l_NoOfRecordsIns,
4055 l_NoOfRecordsDel,
4056 x_err_code,
4057 x_err_stack,
4058 x_err_stage
4059 ) ;
4060
4061 end if ;
4062 END LOOP ;
4063
4064
4065 x_err_stack := l_old_err_stack ;
4066
4067 EXCEPTION
4068 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4069 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4070
4071 WHEN OTHERS THEN
4072 -- x_err_stage := l_err_stage ;
4073 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_EXPENDITURES1' );
4074 pa_debug.debug('Error stage is '||x_err_stage );
4075 pa_debug.debug('Error stack is '||x_err_stack );
4076 pa_debug.debug(SQLERRM);
4077 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4078
4079 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4080
4081 end PA_Expenditures1 ;
4082
4083 -- Start of comments
4084 -- API name : PA_MRCExpenditureItems
4085 -- Type : Public
4086 -- Pre-reqs : None
4087 -- Function : This procedure purges all the MRCexpenditure items that
4088 -- are not related to other expenditure items through
4089 -- transferred_from_exp_item_id.
4090 --
4091 -- Parameters : Refer to the comments of the previous procedure
4092 --
4093 -- End of comments
4094
4095 procedure PA_MRCExpenditureItems(
4096 p_purge_batch_id IN NUMBER,
4097 p_project_id IN NUMBER,
4098 p_txn_to_date IN DATE,
4099 p_purge_release IN VARCHAR2,
4100 p_archive_flag IN VARCHAR2,
4101 p_commit_size IN NUMBER,
4102 x_err_code IN OUT NOCOPY NUMBER,
4103 x_err_stack IN OUT NOCOPY VARCHAR2,
4104 x_err_stage IN OUT NOCOPY VARCHAR2,
4105 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4106 IS
4107
4108 l_old_err_stage VARCHAR2(2000);
4109 l_old_err_stack VARCHAR2(2000);
4110
4111 BEGIN
4112
4113 l_old_err_stack := x_err_stack;
4114
4115 x_err_stack := x_err_stack || ' ->Before insert into PA_MC_EXP_ITEMS_AR' ;
4116
4117 x_err_stage := 'PA_MRCExpenditureItems: Before inserting records into PA_MC_EXP_ITEMS_AR';
4118
4119 /* Note that purged_project_id in table PA_EXPENDITURE_ITEMS_AR is index
4120 * Will also need index on PA_MC_EXP_ITEMS_AR columns set_of_books_id and
4121 * expenditure_item_id.
4122 * The NOT EXISTS section is to make sure that no attempt is made to insert a
4123 * duplicate record in table PA_MC_EXP_ITEMS_AR.
4124 */
4125 INSERT INTO PA_MC_EXP_ITEMS_AR
4126 ( SET_OF_BOOKS_ID,
4127 EXPENDITURE_ITEM_ID,
4128 RAW_COST,
4129 RAW_COST_RATE,
4130 BURDEN_COST,
4131 BURDEN_COST_RATE,
4132 RAW_REVENUE,
4133 BILL_RATE,
4134 ACCRUED_REVENUE,
4135 ACCRUAL_RATE,
4136 ADJUSTED_REVENUE,
4137 ADJUSTED_RATE,
4138 BILL_AMOUNT,
4139 FORECAST_REVENUE,
4140 NET_ZERO_ADJUSTMENT_FLAG,
4141 TRANSFERRED_FROM_EXP_ITEM_ID,
4142 PRC_ASSIGNMENT_ID,
4143 CURRENCY_CODE,
4144 COST_EXCHANGE_RATE,
4145 COST_CONVERSION_DATE,
4146 COST_RATE_TYPE,
4147 REVENUE_EXCHANGE_RATE,
4148 REVENUE_CONVERSION_DATE,
4149 REVENUE_RATE_TYPE,
4150 TRANSFER_PRICE,
4151 TP_EXCHANGE_RATE,
4152 TP_CONVERSION_DATE,
4153 TP_RATE_TYPE,
4154 PROJFUNC_INV_RATE_TYPE,
4155 PROJFUNC_INV_RATE_DATE,
4156 PROJFUNC_INV_EXCHANGE_RATE,
4157 PROJFUNC_FCST_RATE_TYPE,
4158 PROJFUNC_FCST_RATE_DATE,
4159 PROJFUNC_FCST_EXCHANGE_RATE,
4160 PURGE_PROJECT_ID,
4161 PURGE_RELEASE,
4162 PURGE_BATCH_ID )
4163 SELECT
4164 MCEI.SET_OF_BOOKS_ID,
4165 MCEI.EXPENDITURE_ITEM_ID,
4166 MCEI.RAW_COST,
4167 MCEI.RAW_COST_RATE,
4168 MCEI.BURDEN_COST,
4169 MCEI.BURDEN_COST_RATE,
4170 MCEI.RAW_REVENUE,
4171 MCEI.BILL_RATE,
4172 MCEI.ACCRUED_REVENUE,
4173 MCEI.ACCRUAL_RATE,
4174 MCEI.ADJUSTED_REVENUE,
4175 MCEI.ADJUSTED_RATE,
4176 MCEI.BILL_AMOUNT,
4177 MCEI.FORECAST_REVENUE,
4178 MCEI.NET_ZERO_ADJUSTMENT_FLAG,
4179 MCEI.TRANSFERRED_FROM_EXP_ITEM_ID,
4180 MCEI.PRC_ASSIGNMENT_ID,
4181 MCEI.CURRENCY_CODE,
4182 MCEI.COST_EXCHANGE_RATE,
4183 MCEI.COST_CONVERSION_DATE,
4184 MCEI.COST_RATE_TYPE,
4185 MCEI.REVENUE_EXCHANGE_RATE,
4186 MCEI.REVENUE_CONVERSION_DATE,
4187 MCEI.REVENUE_RATE_TYPE,
4188 MCEI.TRANSFER_PRICE,
4189 MCEI.TP_EXCHANGE_RATE,
4190 MCEI.TP_CONVERSION_DATE,
4191 MCEI.TP_RATE_TYPE,
4192 MCEI.PROJFUNC_INV_RATE_TYPE,
4193 MCEI.PROJFUNC_INV_RATE_DATE,
4194 MCEI.PROJFUNC_INV_EXCHANGE_RATE,
4195 MCEI.PROJFUNC_FCST_RATE_TYPE,
4196 MCEI.PROJFUNC_FCST_RATE_DATE,
4197 MCEI.PROJFUNC_FCST_EXCHANGE_RATE,
4198 P_PROJECT_ID,
4199 P_PURGE_RELEASE,
4200 P_PURGE_BATCH_ID
4201 FROM
4202 PA_EXPENDITURE_ITEMS_AR EI,
4203 PA_MC_EXP_ITEMS_ALL MCEI
4204 WHERE
4205 EI.PURGE_PROJECT_ID = P_PROJECT_ID
4206 AND MCEI.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
4207 AND NOT EXISTS ( SELECT expenditure_item_id
4208 FROM
4209 PA_MC_EXP_ITEMS_AR
4210 WHERE
4211 purge_project_id = P_PROJECT_ID
4212 AND expenditure_item_id = mcei.expenditure_item_id
4213 AND set_of_books_id = mcei.set_of_books_id ) ;
4214
4215 x_MRC_NoOfRecordsIns := NVL(SQL%ROWCOUNT,0) ;
4216
4217 x_err_stack := l_old_err_stack ;
4218
4219 EXCEPTION
4220 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4221 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4222
4223 WHEN OTHERS THEN
4224 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCExpenditureItems' );
4225 pa_debug.debug('Error stage is '||x_err_stage );
4226 pa_debug.debug('Error stack is '||x_err_stack );
4227 pa_debug.debug(SQLERRM);
4228 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4229
4230 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4231
4232 END PA_MRCExpenditureItems ;
4233
4234 -- Start of comments
4235 -- API name : PA_MRCCostDistLines
4236 -- Type : Public
4237 -- Pre-reqs : None
4238 -- Function : This procedure purges all the Cost Distribution Lines that are
4239 -- not related to other expenditure items through
4240 -- transferred_from_exp_item_id.
4241 --
4242 -- Parameters : Refer to the comments of the previous procedure
4243 --
4244 -- End of comments
4245
4246
4247 PROCEDURE PA_MRCCostDistLines (
4248 p_purge_batch_id IN NUMBER,
4249 p_project_id IN NUMBER,
4250 p_txn_to_date IN DATE,
4251 p_purge_release IN VARCHAR2,
4252 p_archive_flag IN VARCHAR2,
4253 p_commit_size IN NUMBER,
4254 x_err_code IN OUT NOCOPY NUMBER,
4255 x_err_stack IN OUT NOCOPY VARCHAR2,
4256 x_err_stage IN OUT NOCOPY VARCHAR2,
4257 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4258 IS
4259
4260 l_old_err_stage VARCHAR2(2000);
4261 l_old_err_stack VARCHAR2(2000);
4262
4263 BEGIN
4264
4265 l_old_err_stack := x_err_stack;
4266
4267 x_err_stack := x_err_stack || ' ->Before insert into MRC PA_MC_CDL_AR ' ;
4268
4269 x_err_stage := 'Before insert into PA_MC_CDL_AR' ;
4270 INSERT INTO PA_MC_CDL_AR
4271 (SET_OF_BOOKS_ID,
4272 EXPENDITURE_ITEM_ID,
4273 LINE_NUM,
4274 LINE_TYPE,
4275 TRANSFER_STATUS_CODE,
4276 AMOUNT,
4277 QUANTITY,
4278 REQUEST_ID,
4279 PROGRAM_APPLICATION_ID,
4280 PROGRAM_ID,
4281 PROGRAM_UPDATE_DATE,
4282 TRANSFERRED_DATE,
4283 TRANSFER_REJECTION_REASON,
4284 BATCH_NAME,
4285 BURDENED_COST,
4286 CURRENCY_CODE,
4287 EXCHANGE_RATE,
4288 CONVERSION_DATE,
4289 PRC_ASSIGNMENT_ID,
4290 RATE_TYPE,
4291 PURGE_PROJECT_ID,
4292 PURGE_RELEASE ,
4293 PURGE_BATCH_ID )
4294 SELECT
4295 MC_CDL.SET_OF_BOOKS_ID,
4296 MC_CDL.EXPENDITURE_ITEM_ID,
4297 MC_CDL.LINE_NUM,
4298 MC_CDL.LINE_TYPE,
4299 MC_CDL.TRANSFER_STATUS_CODE,
4300 MC_CDL.AMOUNT,
4301 MC_CDL.QUANTITY,
4302 MC_CDL.REQUEST_ID,
4303 MC_CDL.PROGRAM_APPLICATION_ID,
4304 MC_CDL.PROGRAM_ID,
4305 MC_CDL.PROGRAM_UPDATE_DATE,
4306 MC_CDL.TRANSFERRED_DATE,
4307 MC_CDL.TRANSFER_REJECTION_REASON,
4308 MC_CDL.BATCH_NAME,
4309 MC_CDL.BURDENED_COST,
4310 MC_CDL.CURRENCY_CODE,
4311 MC_CDL.EXCHANGE_RATE,
4312 MC_CDL.CONVERSION_DATE,
4313 MC_CDL.PRC_ASSIGNMENT_ID,
4314 MC_CDL.RATE_TYPE,
4315 P_PURGE_BATCH_ID,
4316 P_PURGE_RELEASE,
4317 P_PROJECT_ID
4318 FROM
4319 PA_MC_COST_DIST_LINES_ALL MC_CDL,
4320 PA_COST_DIST_LINES_AR AR_CDL
4321 WHERE
4322 MC_CDL.EXPENDITURE_ITEM_ID = AR_CDL.EXPENDITURE_ITEM_ID
4323 AND MC_CDL.LINE_NUM = AR_CDL.LINE_NUM
4324 AND AR_CDL.PURGE_PROJECT_ID = P_PROJECT_ID
4325 AND NOT EXISTS (
4326 SELECT MC_CDL.expenditure_item_id
4327 FROM
4328 PA_MC_CDL_AR MC_AR_CDL
4329 WHERE
4330 MC_AR_CDL.purge_project_id = P_PROJECT_ID
4331 AND MC_AR_CDL.expenditure_item_id = MC_CDL.expenditure_item_id
4332 AND MC_AR_CDL.line_num = MC_CDL.line_num
4333 AND MC_AR_CDL.set_of_books_id = MC_CDL.set_of_books_id ) ;
4334
4335 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
4336
4337 x_err_stack := l_old_err_stack ;
4338
4339 EXCEPTION
4340 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4341 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4342
4343 WHEN OTHERS THEN
4344 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCCOSTDISTLINES');
4345 pa_debug.debug('Error stage is '|| x_err_stage );
4346 pa_debug.debug('Error stack is '|| x_err_stack );
4347 pa_debug.debug(SQLERRM);
4348 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4349
4350 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4351
4352 END PA_MRCCostDistLines ;
4353
4354
4355 -- Start of comments
4356 -- API name : PA_MRCCcDistLines
4357 -- Type : Public
4358 -- Pre-reqs : None
4359 -- Function : This procedure purges all the CC Distribution Lines in MRC.
4360 --
4361 -- Parameters : Refer to the comments of the previous procedure
4362 --
4363 -- End of comments
4364
4365
4366 PROCEDURE PA_MRCCcDistLines( p_purge_batch_id IN NUMBER,
4367 p_project_id IN NUMBER,
4368 p_txn_to_date IN DATE,
4369 p_purge_release IN VARCHAR2,
4370 p_archive_flag IN VARCHAR2,
4371 p_commit_size IN NUMBER,
4372 x_err_code IN OUT NOCOPY NUMBER,
4373 x_err_stack IN OUT NOCOPY VARCHAR2,
4374 x_err_stage IN OUT NOCOPY VARCHAR2,
4375 x_MRC_NoOfRecordsIns OUT NOCOPY NUMBER )
4376 IS
4377
4378 l_old_err_stage VARCHAR2(2000);
4379 l_old_err_stack VARCHAR2(2000);
4380
4381 BEGIN
4382
4383 l_old_err_stack := x_err_stack;
4384
4385 x_err_stack := x_err_stack || ' ->Before insert into MRC PA_MC_CC_DIST_LINES_AR ' ;
4386
4387 x_err_stage := 'Before insert into PA_MC_CC_DIST_LINES_AR' ;
4388 INSERT INTO PA_MC_CC_DIST_LINES_AR
4389 (PURGE_BATCH_ID,
4390 PURGE_RELEASE,
4391 PURGE_PROJECT_ID,
4392 SET_OF_BOOKS_ID,
4393 PRC_ASSIGNMENT_ID,
4394 CC_DIST_LINE_ID,
4395 EXPENDITURE_ITEM_ID,
4396 LINE_NUM,
4397 LINE_TYPE,
4398 ACCT_CURRENCY_CODE,
4399 AMOUNT,
4400 PROGRAM_ID,
4401 PROGRAM_APPLICATION_ID,
4402 PROGRAM_UPDATE_DATE,
4403 REQUEST_ID,
4404 TRANSFER_STATUS_CODE,
4405 ACCT_TP_RATE_TYPE,
4406 ACCT_TP_RATE_DATE,
4407 ACCT_TP_EXCHANGE_RATE,
4408 GL_BATCH_NAME,
4409 TRANSFERRED_DATE,
4410 TRANSFER_REJECTION_CODE)
4411 SELECT
4412 P_PURGE_BATCH_ID,
4413 P_PURGE_RELEASE,
4414 P_PROJECT_ID,
4415 MC_CDL.SET_OF_BOOKS_ID,
4416 MC_CDL.PRC_ASSIGNMENT_ID,
4417 MC_CDL.CC_DIST_LINE_ID,
4418 MC_CDL.EXPENDITURE_ITEM_ID,
4419 MC_CDL.LINE_NUM,
4420 MC_CDL.LINE_TYPE,
4421 MC_CDL.ACCT_CURRENCY_CODE,
4422 MC_CDL.AMOUNT,
4423 MC_CDL.PROGRAM_ID,
4424 MC_CDL.PROGRAM_APPLICATION_ID,
4425 MC_CDL.PROGRAM_UPDATE_DATE,
4426 MC_CDL.REQUEST_ID,
4427 MC_CDL.TRANSFER_STATUS_CODE,
4428 MC_CDL.ACCT_TP_RATE_TYPE,
4429 MC_CDL.ACCT_TP_RATE_DATE,
4430 MC_CDL.ACCT_TP_EXCHANGE_RATE,
4431 MC_CDL.GL_BATCH_NAME,
4432 MC_CDL.TRANSFERRED_DATE,
4433 MC_CDL.TRANSFER_REJECTION_CODE
4434 FROM
4435 PA_MC_CC_DIST_LINES_ALL MC_CDL,
4436 PA_CC_DIST_LINES_AR AR_CDL
4437 WHERE
4438 MC_CDL.EXPENDITURE_ITEM_ID = AR_CDL.EXPENDITURE_ITEM_ID
4439 AND MC_CDL.LINE_NUM = AR_CDL.LINE_NUM
4440 AND AR_CDL.PURGE_PROJECT_ID = P_PROJECT_ID
4441 AND NOT EXISTS (
4442 SELECT MC_CDL.expenditure_item_id
4443 FROM
4444 PA_MC_CC_DIST_LINES_AR MC_AR_CDL
4445 WHERE
4446 MC_AR_CDL.purge_project_id = P_PROJECT_ID
4447 AND MC_AR_CDL.expenditure_item_id = MC_CDL.expenditure_item_id
4448 AND MC_AR_CDL.line_num = MC_CDL.line_num
4449 AND MC_AR_CDL.set_of_books_id = MC_CDL.set_of_books_id ) ;
4450
4451 x_MRC_NoOfRecordsIns := nvl(SQL%ROWCOUNT,0) ;
4452
4453 x_err_stack := l_old_err_stack ;
4454
4455 EXCEPTION
4456 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
4457 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4458
4459 WHEN OTHERS THEN
4460 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_MRCCCDISTLINES');
4461 pa_debug.debug('Error stage is '|| x_err_stage );
4462 pa_debug.debug('Error stack is '|| x_err_stack );
4463 pa_debug.debug(SQLERRM);
4464 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
4465
4466 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
4467
4468 END PA_MRCCcDistLines ;
4469
4470 END pa_purge_costing;