[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_UNASGN_FI
Source
1 Package body PA_PURGE_UNASGN_FI AS
2 /* $Header: PAXUSGNB.pls 120.1.12000000.2 2007/03/06 14:02:34 rthumma ship $ */
3
4 -- Start of comments
5 -- Type : Public
6 -- Pre-reqs : None
7 -- Function : Procedure for Purging records related to unassigned time forecast_items for resources
8 -- Parameters :
9 -- p_archive_flag -> This flag will indicate if the
10 -- records need to be archived
11 -- before they are purged.
12 -- p_txn_to_date -> Date through which the transactions
13 -- need to be purged. This value will
14 -- be NULL if the purge batch is for
15 -- active projects.
16 -- End of comments
17
18 Procedure PA_FORECASTITEM (
19 errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 p_txn_to_date in VARCHAR2,
22 p_archive_flag in varchar2) IS
23
24
25 l_forecast_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
26 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp; --Added for bug 5870223
27 I PLS_INTEGER;
28 l_last_fetch VARCHAR2(1):='N';
29 l_this_fetch NUMBER:=0;
30 l_totally_fetched NUMBER:=0;
31 p_commit_size NUMBER:=1000;
32 l_purge_batch_id NUMBER;
33 x_err_stack VARCHAR2(5000);
34 x_err_stage VARCHAR2(5000);
35 x_err_code NUMBER;
36
37 /*The below cursor will select unassigned time forecast_items whose item date <=purge till date. */
38
39 CURSOR Cur_forecast_items IS
40 SELECT forecast_item_id ,project_id --Added for bug 5870223
41 FROM pa_forecast_items
42 WHERE forecast_item_type='U'
43 AND item_date <= fnd_date.canonical_to_date(p_txn_to_date) /* Bug#2510609 */
44 ORDER BY project_id; -- Added for bug 5870223
45
46 Begin
47 arpr_log('p_txn_to_date: '||p_txn_to_date);
48 arpr_log('p_archive_flag: '||p_archive_flag);
49 x_err_stack := x_err_stack || ' ->Before call to purge unassigned forecast items ';
50
51 arpr_log(' About to purge unassigned time forecast items ') ;
52 x_err_stage := 'About to start purge unassigned time forecast items' ;
53
54 select pa_purge_batches_s.nextval into l_purge_batch_id from dual;
55
56 arpr_log(' l_purge_batch_id: '||l_purge_batch_id);
57
58 OPEN CUR_forecast_items;
59 LOOP
60
61 /*Clear PL/SQL table before start */
62 l_forecast_item_id_tab.DELETE;
63 l_project_id_tab.DELETE; --Added for bug 5870223
64
65 FETCH cur_forecast_items BULK COLLECT
66 INTO l_forecast_item_id_tab , l_project_id_tab LIMIT p_commit_size; --Added for bug 5870223
67
68 /* To check the rows fetched in this fetch */
69
70 l_this_fetch := cur_forecast_items%ROWCOUNT - l_totally_fetched;
71 l_totally_fetched := cur_forecast_items%ROWCOUNT;
72
73 arpr_log(' l_this_fetch: '||l_this_fetch);
74 arpr_log(' l_totally_fetched: '||l_totally_fetched);
75
76 /*
77 * Check if this fetch has 0 rows returned (ie last fetch was even p_commit_size)
78 * This could happen in 2 cases
79 * 1) this fetch is the very first fetch with 0 rows returned
80 * OR 2) the last fetch returned an even p_commit_size rows
81 * If either then EXIT without any processing
82 */
83 IF l_this_fetch = 0 then
84 arpr_log(' Exiting from the program as l_this_fetch = 0');
85 EXIT;
86 END IF;
87
88 /*
89 * Check if this fetch is the last fetch
90 * If so then set the flag l_last_fetch so as to exit after processing
91 */
92 IF l_this_fetch < p_commit_size then
93 l_last_fetch := 'Y';
94 ELSE
95 l_last_fetch := 'N';
96 END IF;
97
98 /* arpr_log(' Before call to PA_PURGE_UNASGN_FI.Delete_fi'); */
99
100 PA_PURGE_UNASGN_FI.Delete_fi(p_forecast_item_id_tab =>l_forecast_item_id_tab,
101 p_project_id_tab => l_project_id_tab, --Added for bug 5870223
102 p_archive_flag =>p_archive_flag,
103 p_purge_batch_id =>l_purge_batch_id,
104 x_err_stack =>x_err_stack,
105 x_err_stage =>x_err_stage,
106 x_err_code =>x_err_code);
107
108 /* Check if this loop is the last set of p_commit_size If so then EXIT; */
109
110 IF l_last_fetch='Y' THEN
111 EXIT;
112 END IF;
113
114 END LOOP;
115
116
117 CLOSE cur_forecast_items;
118
119 /* Bug#2510609 */
120 arpr_out( p_txn_to_date,
121 p_archive_flag,
122 l_purge_batch_id);
123
124 EXCEPTION
125 /* Bug#2510609 */
126 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
127 errbuf := PA_PROJECT_UTILS2.g_sqlerrm ;
128 retcode := -1 ;
129 WHEN OTHERS THEN
130 errbuf := SQLERRM ;
131 retcode := -1 ;
132 arpr_log('errbuf in exception: '||errbuf);
133
134 /* Bug#2510609
135 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
136 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
137
138 WHEN OTHERS THEN
139 arpr_log('Error Procedure Name := PA_PURGE_UNASGN_FI.PA_FORECASTITEMS' );
140 arpr_log('Error stage is '||x_err_stage );
141 arpr_log('Error stack is '||x_err_stack );
142 arpr_log(SQLERRM);
143 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
144
145 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
146 */
147
148 End PA_FORECASTITEM;
149
150 -- Start of comments
151 -- API name : DELETE_FI
152 -- Type : Public
153 -- Pre-reqs : None
154 -- Function : Archive/purge records for pa_forecast_items, pa_forecast_items_details and pa_fi_amount_details table.
155 -- Parameters :
156 -- records need to be archived
157 -- p_forecast_item_id_id_tab -> forecast items tab
158 -- End of comments
159
160 Procedure Delete_FI (p_forecast_item_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
161 p_project_id_tab in PA_PLSQL_DATATYPES.IdTabTyp, --Added for bug 5870223
162 p_archive_flag in VARCHAR2,
163 p_purge_batch_id in NUMBER,
164 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
165 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
166 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
167
168 l_forecast_item_id Pa_forecast_items.forecast_item_id%TYPE;
169 I PLS_INTEGER;
170 l_nos_fi_inserted NUMBER ;
171 l_nos_fid_inserted NUMBER ;
172 l_nos_fi_deleted NUMBER ;
173 l_nos_fid_deleted NUMBER ;
174 l_nos_fi_amt_inserted NUMBER;
175 l_nos_fi_amt_deleted NUMBER;
176 l_purge_release VARCHAR2(50) := '11.5';
177 l_project_id pa_projects_all.project_id%TYPE;
178
179 --Added for bug 5870223
180 l_max_count NUMBER;
181 l_first_index NUMBER;
182 l_last_index NUMBER;
183 l_call_commit VARCHAR2(1) := 'N';
184
185 l_forecast_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
186 J PLS_INTEGER := 0;
187
188 --End for bug 5870223
189
190
191 Begin
192
193 /* x_err_stack := x_err_stack || ' ->Before call to purge unassigned time Forecast Item records '; */
194
195 /*Initialize the no of record variables for each call */
196
197 /* arpr_log(' Inside Procedure to purge unassigned time Forecast Items,Forecast Item Details and fi_amount details ') ; */
198 x_err_stage := 'Start purging forecast items for id ';
199
200 l_nos_fi_inserted :=0;
201 l_nos_fid_inserted :=0;
202 l_nos_fi_deleted :=0;
203 l_nos_fid_deleted :=0;
204 l_nos_fi_amt_deleted :=0;
205 l_nos_fi_amt_inserted :=0;
206
207 --Added for bug 5870223
208 l_max_count := p_forecast_item_id_tab.count;
209 l_first_index := p_forecast_item_id_tab.FIRST;
210 l_last_index := p_forecast_item_id_tab.LAST;
211 J := l_first_index;
212 --End for bug 5870223
213
214 FOR I in p_forecast_item_id_tab.FIRST .. p_forecast_item_id_tab.LAST LOOP
215 l_forecast_item_id :=p_forecast_item_id_tab(I);
216 l_forecast_item_id_tab(J) := p_forecast_item_id_tab(I); --Added for bug 5870223
217
218 --Added for bug 5870223
219 IF I = l_last_index OR l_max_count = 1 THEN
220 l_call_commit := 'Y';
221 ELSE
222 IF p_project_id_tab(I) <> p_project_id_tab(I + 1) THEN
223 l_call_commit := 'Y';
224 END IF;
225 END IF;
226 --End for bug 5870223
227
228 /*Commented for bug 5870223
229 SELECT project_id into l_project_id
230 from pa_forecast_items
231 where forecast_item_id=l_forecast_item_id;*/
232
233 l_project_id := p_project_id_tab(I); --Added for bug 5870223
234
235 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
236
237 IF l_call_commit = 'Y' THEN -- Bug 5870223
238
239 IF p_archive_flag='Y' THEN
240
241 /* arpr_log('Inserting Records into pa_forecast_items_AR table ') ; */
242 x_err_stage := 'Inserting Records into pa_forecast_items_AR table for forecast item '||to_char(l_forecast_item_id) ;
243
244 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
245 INSERT INTO pa_frcst_items_AR
246 (PURGE_BATCH_ID,
247 PURGE_RELEASE,
248 PURGE_PROJECT_ID,
249 FORECAST_ITEM_ID,
250 FORECAST_ITEM_TYPE,
251 PROJECT_ORG_ID,
252 EXPENDITURE_ORG_ID,
253 EXPENDITURE_ORGANIZATION_ID,
254 PROJECT_ORGANIZATION_ID,
255 PROJECT_ID,
256 PROJECT_TYPE_CLASS,
257 PERSON_ID,
258 RESOURCE_ID,
259 BORROWED_FLAG,
260 ASSIGNMENT_ID,
261 ITEM_DATE,
262 ITEM_UOM,
263 ITEM_QUANTITY,
264 PVDR_PERIOD_SET_NAME,
265 PVDR_PA_PERIOD_NAME,
266 PVDR_GL_PERIOD_NAME,
267 RCVR_PERIOD_SET_NAME,
268 RCVR_PA_PERIOD_NAME,
269 RCVR_GL_PERIOD_NAME,
270 GLOBAL_EXP_PERIOD_END_DATE,
271 EXPENDITURE_TYPE,
272 EXPENDITURE_TYPE_CLASS,
273 COST_REJECTION_CODE,
274 REV_REJECTION_CODE,
275 TP_REJECTION_CODE,
276 BURDEN_REJECTION_CODE,
277 OTHER_REJECTION_CODE,
278 DELETE_FLAG,
279 ERROR_FLAG,
280 PROVISIONAL_FLAG,
281 CREATION_DATE,
282 CREATED_BY,
283 LAST_UPDATE_DATE,
284 LAST_UPDATED_BY,
285 LAST_UPDATE_LOGIN,
286 REQUEST_ID,
287 PROGRAM_APPLICATION_ID,
288 PROGRAM_ID,
289 PROGRAM_UPDATE_DATE,
290 ASGMT_SYS_STATUS_CODE,
291 CAPACITY_QUANTITY,
292 OVERCOMMITMENT_QUANTITY,
293 AVAILABILITY_QUANTITY,
294 OVERCOMMITMENT_FLAG,
295 AVAILABILITY_FLAG,
296 TP_AMOUNT_TYPE,
297 FORECAST_AMT_CALC_FLAG,
298 COST_TXN_CURRENCY_CODE,
299 TXN_RAW_COST,
300 TXN_BURDENED_COST,
301 REVENUE_TXN_CURRENCY_CODE,
302 TXN_REVENUE,
303 TP_TXN_CURRENCY_CODE,
304 TXN_TRANSFER_PRICE,
305 PROJECT_CURRENCY_CODE,
306 PROJECT_RAW_COST,
307 PROJECT_BURDENED_COST,
308 PROJECT_REVENUE,
309 PROJECT_TRANSFER_PRICE,
310 PROJFUNC_CURRENCY_CODE,
311 PROJFUNC_RAW_COST,
312 PROJFUNC_BURDENED_COST,
313 PROJFUNC_REVENUE,
314 PROJFUNC_TRANSFER_PRICE,
315 EXPFUNC_CURRENCY_CODE,
316 EXPFUNC_RAW_COST,
317 EXPFUNC_BURDENED_COST,
318 EXPFUNC_TRANSFER_PRICE,
319 OVERPROVISIONAL_QTY,
320 OVER_PROV_CONF_QTY,
321 CONFIRMED_QTY,
322 PROVISIONAL_QTY,
323 JOB_ID)
324
325 SELECT p_purge_batch_id,
326 l_purge_release,
327 project_id,
328 Forecast_Item_Id,
329 Forecast_Item_Type,
330 Project_Org_Id,
331 Expenditure_Org_Id,
332 Expenditure_Organization_Id,
333 Project_Organization_Id,
334 Project_Id,
335 Project_Type_Class,
336 Person_Id,
337 Resource_Id,
338 Borrowed_Flag,
339 Assignment_Id,
340 Item_Date,
341 Item_Uom,
342 Item_Quantity,
343 Pvdr_Period_Set_Name,
344 Pvdr_Pa_Period_Name,
345 Pvdr_Gl_Period_Name,
346 Rcvr_Period_Set_Name,
347 Rcvr_Pa_Period_Name,
348 Rcvr_Gl_Period_Name,
349 Global_Exp_Period_End_Date,
350 Expenditure_Type,
351 Expenditure_Type_Class,
352 Cost_Rejection_Code,
353 Rev_Rejection_Code,
354 Tp_Rejection_Code,
355 Burden_Rejection_Code,
356 Other_Rejection_Code,
357 Delete_Flag,
358 Error_Flag,
359 Provisional_Flag,
360 Creation_Date,
361 Created_By,
362 Last_Update_Date,
363 Last_Updated_By,
364 Last_Update_Login,
365 Request_Id,
366 Program_Application_Id,
367 Program_Id,
368 Program_Update_Date,
369 Asgmt_Sys_Status_Code,
370 Capacity_Quantity,
371 Overcommitment_Quantity,
372 Availability_Quantity,
373 Overcommitment_Flag,
374 Availability_Flag,
375 Tp_Amount_Type,
376 Forecast_Amt_Calc_Flag,
380 Revenue_Txn_Currency_Code,
377 Cost_Txn_Currency_Code,
378 Txn_Raw_Cost,
379 Txn_Burdened_Cost,
381 Txn_Revenue,
382 Tp_Txn_Currency_Code,
383 Txn_Transfer_Price,
384 Project_Currency_Code,
385 Project_Raw_Cost,
386 Project_Burdened_Cost,
387 Project_Revenue,
388 Project_Transfer_Price,
389 Projfunc_Currency_Code,
390 projfunc_Raw_Cost,
391 Projfunc_Burdened_Cost,
392 Projfunc_Revenue,
393 Projfunc_Transfer_Price,
394 Expfunc_Currency_Code,
395 Expfunc_Raw_Cost,
396 Expfunc_Burdened_Cost,
397 Expfunc_Transfer_Price,
398 Overprovisional_Qty,
399 Over_Prov_Conf_Qty,
400 Confirmed_Qty,
401 Provisional_Qty,
402 Job_Id
403 FROM pa_forecast_items
404 WHERE forecast_item_id = l_forecast_item_id_tab(K);--l_forecast_item_id; 5870223
405
406 /*Increase the value of l_nos_fi_inserted to indicate number of records inserted in forecast_items table.
407 The value will increase for each loop(forecast item id*/
408 l_nos_fi_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
409
410 /* arpr_log('Inserting Records into pa_forecast_item_DETAILS_AR table ') ; */
411 x_err_stage := 'Inserting Records into forecast_item_detail table for forecast item '||to_char(l_forecast_item_id) ;
412
413 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
414 INSERT INTO PA_FRCST_ITEM_DTLS_AR
415 (PURGE_BATCH_ID,
416 PURGE_RELEASE,
417 PURGE_PROJECT_ID,
418 FORECAST_ITEM_ID,
419 AMOUNT_TYPE_ID,
420 LINE_NUM,
421 RESOURCE_TYPE_CODE,
422 PERSON_BILLABLE_FLAG,
423 ITEM_DATE,
424 ITEM_UOM,
425 ITEM_QUANTITY,
426 EXPENDITURE_ORG_ID,
427 PROJECT_ORG_ID,
428 PVDR_ACCT_CURR_CODE,
429 PVDR_ACCT_AMOUNT,
430 RCVR_ACCT_CURR_CODE,
431 RCVR_ACCT_AMOUNT,
432 PROJ_CURRENCY_CODE,
433 PROJ_AMOUNT,
434 DENOM_CURRENCY_CODE,
435 DENOM_AMOUNT,
436 TP_AMOUNT_TYPE,
437 BILLABLE_FLAG,
438 FORECAST_SUMMARIZED_CODE,
439 UTIL_SUMMARIZED_CODE,
440 WORK_TYPE_ID,
441 RESOURCE_UTIL_CATEGORY_ID,
442 ORG_UTIL_CATEGORY_ID,
443 RESOURCE_UTIL_WEIGHTED,
444 ORG_UTIL_WEIGHTED,
445 PROVISIONAL_FLAG,
446 REVERSED_FLAG,
447 NET_ZERO_FLAG,
448 REDUCE_CAPACITY_FLAG,
449 LINE_NUM_REVERSED,
450 CREATION_DATE,
451 CREATED_BY,
452 LAST_UPDATE_DATE,
453 LAST_UPDATED_BY,
454 LAST_UPDATE_LOGIN,
455 REQUEST_ID,
456 PROGRAM_APPLICATION_ID,
457 PROGRAM_ID,
458 PROGRAM_UPDATE_DATE,
459 CAPACITY_QUANTITY,
460 OVERCOMMITMENT_QTY,
461 OVERPROVISIONAL_QTY,
462 OVER_PROV_CONF_QTY,
463 CONFIRMED_QTY,
464 PROVISIONAL_QTY,
465 JOB_ID,
466 PROJECT_ID,
467 RESOURCE_ID,
468 EXPENDITURE_ORGANIZATION_ID,
469 PJI_SUMMARIZED_FLAG)
470
471 SELECT p_purge_batch_id,
472 l_Purge_Release,
473 l_Project_Id,
474 Forecast_Item_Id,
475 Amount_Type_Id,
476 Line_Num,
477 Resource_Type_Code,
478 Person_Billable_Flag,
479 Item_Date,
480 Item_Uom,
481 Item_Quantity,
482 Expenditure_Org_Id,
483 Project_Org_Id,
484 Pvdr_Acct_Curr_Code,
485 Pvdr_Acct_Amount,
486 Rcvr_Acct_Curr_Code,
487 Rcvr_Acct_Amount,
488 Proj_Currency_Code,
489 Proj_Amount,
490 Denom_Currency_Code,
491 Denom_Amount,
492 Tp_Amount_Type,
493 Billable_Flag,
494 Forecast_Summarized_Code,
495 Util_Summarized_Code,
496 Work_Type_Id,
497 Resource_Util_Category_Id,
498 Org_Util_Category_Id,
499 Resource_Util_Weighted,
500 Org_Util_Weighted,
501 Provisional_Flag,
505 Line_Num_Reversed,
502 Reversed_Flag,
503 Net_Zero_Flag,
504 Reduce_Capacity_Flag,
506 Creation_Date,
507 Created_By,
508 Last_Update_Date,
509 Last_Updated_By,
510 Last_Update_Login,
511 Request_Id,
512 Program_Application_Id,
513 Program_Id,
514 Program_Update_Date,
515 CAPACITY_QUANTITY,
516 OVERCOMMITMENT_QTY,
517 OVERPROVISIONAL_QTY,
518 OVER_PROV_CONF_QTY,
519 CONFIRMED_QTY,
520 PROVISIONAL_QTY,
521 JOB_ID,
522 PROJECT_ID,
523 RESOURCE_ID,
524 EXPENDITURE_ORGANIZATION_ID,
525 PJI_SUMMARIZED_FLAG
526 FROM PA_forecast_item_details
527 WHERE forecast_item_id=l_forecast_item_id_tab(K);--l_forecast_item_id; bug 5870223
528
529 /*Increase the value of l_nos_fis_inserted to indicate number of records inserted in forecast_items detail table.
530 The value will increase for each loop(forecast item id*/
531 l_nos_fid_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
532
533 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
534 INSERT INTO PA_FI_AMOUNT_DETAILS_AR
535 (PURGE_BATCH_ID,
536 PURGE_RELEASE,
537 PURGE_PROJECT_ID,
538 FORECAST_ITEM_ID,
539 LINE_NUM,
540 ITEM_DATE,
541 ITEM_UOM,
542 ITEM_QUANTITY,
543 REVERSED_FLAG,
544 NET_ZERO_FLAG,
545 LINE_NUM_REVERSED,
546 CREATION_DATE,
547 CREATED_BY,
548 LAST_UPDATE_DATE,
549 LAST_UPDATED_BY,
550 LAST_UPDATE_LOGIN,
551 REQUEST_ID,
552 PROGRAM_APPLICATION_ID,
553 PROGRAM_ID,
554 PROGRAM_UPDATE_DATE,
555 COST_TXN_CURRENCY_CODE,
556 TXN_RAW_COST,
557 TXN_BURDENED_COST,
558 REVENUE_TXN_CURRENCY_CODE,
559 TXN_REVENUE,
560 TP_TXN_CURRENCY_CODE,
561 TXN_TRANSFER_PRICE,
562 PROJECT_CURRENCY_CODE,
563 PROJECT_COST_RATE_DATE,
564 PROJECT_COST_RATE_TYPE,
565 PROJECT_COST_EXCHANGE_RATE,
566 PROJECT_RAW_COST,
567 PROJECT_BURDENED_COST,
568 PROJECT_REVENUE_RATE_DATE,
569 PROJECT_REVENUE_RATE_TYPE,
570 PROJECT_REVENUE_EXCHANGE_RATE,
571 PROJECT_REVENUE,
572 PROJECT_TP_RATE_DATE,
573 PROJECT_TP_RATE_TYPE,
574 PROJECT_TP_EXCHANGE_RATE,
575 PROJECT_TRANSFER_PRICE,
576 PROJFUNC_CURRENCY_CODE,
577 PROJFUNC_COST_RATE_DATE,
578 PROJFUNC_COST_RATE_TYPE,
579 PROJFUNC_COST_EXCHANGE_RATE,
580 PROJFUNC_RAW_COST,
581 PROJFUNC_BURDENED_COST,
582 PROJFUNC_REVENUE,
583 PROJFUNC_TRANSFER_PRICE,
584 --PROJFUNC_RATE_DATE,
585 --PROJFUNC_RATE_TYPE,
586 --PROJFUNC_EXCHANGE_RATE,
587 EXPFUNC_CURRENCY_CODE,
588 EXPFUNC_COST_RATE_DATE,
589 EXPFUNC_COST_RATE_TYPE,
590 EXPFUNC_COST_EXCHANGE_RATE,
591 EXPFUNC_RAW_COST,
592 EXPFUNC_BURDENED_COST,
593 EXPFUNC_TP_RATE_DATE,
594 EXPFUNC_TP_RATE_TYPE,
595 EXPFUNC_TP_EXCHANGE_RATE,
596 EXPFUNC_TRANSFER_PRICE)
597
598 SELECT p_purge_batch_id,
599 l_purge_release,
600 l_project_id,
601 Forecast_Item_Id,
602 Line_Num,
603 Item_Date,
604 Item_Uom,
605 Item_Quantity,
606 Reversed_Flag,
607 Net_Zero_Flag,
608 Line_Num_Reversed,
609 Creation_Date,
610 Created_By,
611 Last_Update_Date,
612 Last_Updated_By,
613 Last_Update_Login,
614 Request_Id,
615 Program_Application_Id,
616 Program_Id,
617 Program_Update_Date,
618 Cost_Txn_Currency_Code,
619 Txn_Raw_Cost,
620 Txn_Burdened_Cost,
621 Revenue_Txn_Currency_Code,
622 Txn_Revenue,
623 Tp_Txn_Currency_Code,
624 Txn_Transfer_Price,
625 Project_Currency_Code,
626 Project_Cost_Rate_Date,
627 Project_Cost_Rate_Type,
628 Project_Cost_Exchange_Rate,
629 Project_Raw_Cost,
630 Project_Burdened_Cost,
631 Project_Revenue_Rate_Date,
632 Project_Revenue_Rate_Type,
633 Project_Revenue_Exchange_Rate,
634 Project_Revenue,
635 Project_Tp_Rate_Date,
636 Project_Tp_Rate_Type,
637 Project_Tp_Exchange_Rate,
638 Project_Transfer_Price,
639 Projfunc_Currency_Code,
640 Projfunc_Cost_Rate_Date,
641 Projfunc_Cost_Rate_Type,
642 Projfunc_Cost_Exchange_Rate,
643 Projfunc_Raw_Cost,
644 Projfunc_Burdened_Cost,
645 Projfunc_Revenue,
646 Projfunc_Transfer_Price,
647 --Projfunc_Rate_Date,
648 --Projfunc_Rate_Type,
649 --Projfunc_Exchange_Rate,
650 Expfunc_Currency_Code,
651 Expfunc_Cost_Rate_Date,
652 Expfunc_Cost_Rate_Type,
653 Expfunc_Cost_Exchange_Rate,
654 Expfunc_Raw_Cost,
655 Expfunc_Burdened_Cost,
656 Expfunc_Tp_Rate_Date,
657 Expfunc_Tp_Rate_Type,
658 Expfunc_Tp_Exchange_Rate,
662 /*Increase the value of l_nos_fi_amt_inserted to reflct the number of records inserted */
659 Expfunc_Transfer_Price
660 FROM PA_FI_AMOUNT_DETAILS Where forecast_item_id=l_forecast_item_id_tab(K); --l_forecast_item_id; Bug 5870223
661
663
664 l_nos_fi_amt_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
665
666 END IF;
667
668 /*To keep the count of no os records deleted from pa_forecast_items and pa_forecast_item_details, manipulate the
669 count of l_nos_of fi_deleted and l_nos_fis_deleted. */
670
671 /* arpr_log('Deleting Records from pa_fi_amount_details table ') ; */
672 x_err_stage := 'Deleting Records from pa_fi_amount_details table for id '||to_char(l_forecast_item_id) ;
673
674 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
675
676 DELETE PA_FI_AMOUNT_DETAILS
677 WHERE forecast_item_id = l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
678
679 l_nos_fi_amt_deleted := SQL%ROWCOUNT; /* Bug#2510609 */
680
681 /* arpr_log('Deleting Records from pa_forecast_item_details table ') ; */
682 x_err_stage := 'Deleting Records from pa_forecast_item_details table for id '||to_char(l_forecast_item_id) ;
683
684 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
685
686 DELETE PA_FORECAST_ITEM_DETAILS
687 WHERE forecast_item_id =l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
688
689 l_nos_fid_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
690
691 /* arpr_log('Deleting Records from pa_forecast_items table ') ; */
692 x_err_stage := 'Deleting Records from pa_forecast_items table for id '||to_char(l_forecast_item_id) ;
693
694 FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
695
696 DELETE PA_FORECAST_ITEMS
697 WHERE forecast_item_id=l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
698
699 l_nos_fi_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
700
701 -- END LOOP; /* Bug#2510609 */
702
703 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
704 The procedure is called once for pa_forecast_items and once for pa_forecast_item_details */
705
706
707 pa_purge.CommitProcess (p_purge_batch_id,
708 l_project_id,
709 'PA_FORECAST_ITEMS',
710 l_nos_fi_inserted,
711 l_nos_fi_deleted,
712 x_err_code,
713 x_err_stack,
714 x_err_stage
715 ) ;
716 pa_purge.CommitProcess(p_purge_batch_id,
717 l_project_id,
718 'PA_FORECAST_ITEM_DETAILS',
719 l_nos_fid_inserted,
720 l_nos_fid_deleted,
721 x_err_code,
722 x_err_stack,
723 x_err_stage
724 ) ;
725
726 pa_purge.CommitProcess(p_purge_batch_id,
727 l_project_id,
728 'PA_FI_AMOUNT_DETAILS',
729 l_nos_fi_amt_inserted,
730 l_nos_fi_amt_deleted,
731 x_err_code,
732 x_err_stack,
733 x_err_stage
734 ) ;
735
736 l_call_commit := 'N';
737
738 l_forecast_item_id_tab.DELETE;
739 J := 0;
740
741 END IF; --end for l_call_commit = 'Y'
742
743 J:= J + 1; --Added for bug 5870223
744
745 END LOOP; /* Bug#2510609 */
746
747
748 EXCEPTION
749 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
750 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
751
752 WHEN OTHERS THEN
753 arpr_log('Error Procedure Name := PA_PURGE_UNASGN_FI.DELETE_FI' );
754 arpr_log('Error stage is '||x_err_stage );
755 arpr_log('Error stack is '||x_err_stack );
756 arpr_log(SQLERRM);
757 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
758
759 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
760
761 End delete_fi;
762
763
764 PROCEDURE arpr_log (p_message IN VARCHAR2) IS
765
766 begin
767
768 -- IF ( G_DEBUG_MODE = 'Y') THEN
769
770 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
771
772 -- END IF;
773
774 EXCEPTION
775
776 WHEN OTHERS THEN
777 raise;
778
779 END arpr_log;
780
781
782 PROCEDURE arpr_out ( p_txn_to_date in VARCHAR2,
783 p_archive_flag in varchar2,
784 p_purge_batch_id in number) IS
785
786 cursor c_arpr_details is
787 select table_name,
788 sum(nvl(num_recs_archived,0)) num_recs_archived,
789 sum(nvl(num_recs_purged,0)) num_recs_purged
790 from PA_PURGE_PRJ_DETAILS
791 where purge_batch_id = p_purge_batch_id
792 group by table_name
793 order by table_name;
794
795 l_sob_id NUMBER;
796 l_sob_name VARCHAR2(30);
797 l_tblock VARCHAR2(132);
798 l_tmp_str VARCHAR2(132);
799 l_tmp_str2 VARCHAR2(132);
800 l_tmp_str3 VARCHAR2(132);
801
802 begin
803
804 SELECT IMP.Set_Of_Books_ID
805 INTO l_sob_id
806 FROM PA_Implementations IMP;
807
808 SELECT SUBSTRB(GL.Name, 1, 30)
809 INTO l_sob_name
810 FROM GL_Sets_Of_Books GL
811 WHERE GL.Set_Of_Books_ID = l_sob_id;
812
813 SELECT meaning
814 INTO l_tmp_str
815 FROM PA_LOOKUPS
816 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
817 AND lookup_code = 'PA_R_UNASS_TIME_01';
818
819 SELECT rpad(l_sob_name,30,' ')||lpad(l_tmp_str,75,' ')||sysdate
820 INTO l_tblock
821 FROM DUAL;
822
823 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_tblock);
824 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 2);
825
826 SELECT meaning
827 INTO l_tmp_str
828 FROM PA_LOOKUPS
829 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
830 AND lookup_code = 'PA_R_UNASS_TIME_02';
831
832 SELECT lpad(l_tmp_str,66+length(l_tmp_str)/2,' ')
833 INTO l_tblock
834 FROM DUAL;
835
836 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_tblock);
837 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 3);
838
839 SELECT lpad(meaning,32,' ')
840 INTO l_tmp_str
841 FROM PA_LOOKUPS
842 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
843 AND lookup_code = 'PA_R_UNASS_TIME_03';
844
845 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_tmp_str||p_archive_flag);
846 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
847
848 SELECT lpad(meaning,32,' ')
849 INTO l_tmp_str
850 FROM PA_LOOKUPS
851 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
852 AND lookup_code = 'PA_R_UNASS_TIME_04';
853
854 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_tmp_str||p_txn_to_date);
855 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 3);
856
857 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------------------------------------');
858 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
859
860 SELECT ' '||rpad(meaning,20,' ')
861 INTO l_tmp_str
862 FROM PA_LOOKUPS
863 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
864 AND lookup_code = 'PA_R_UNASS_TIME_05';
865
866 SELECT rpad(meaning,52,' ')
867 INTO l_tmp_str2
868 FROM PA_LOOKUPS
869 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
870 AND lookup_code = 'PA_R_UNASS_TIME_06';
871
872 SELECT rpad(meaning,48,' ')
873 INTO l_tmp_str3
874 FROM PA_LOOKUPS
875 WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
876 AND lookup_code = 'PA_R_UNASS_TIME_07';
877
878 SELECT l_tmp_str||' '||l_tmp_str2||l_tmp_str3
879 INTO l_tblock
880 FROM DUAL;
881
882 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_tblock);
883
884 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
885 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------------------------------------');
886 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
887 For i in c_arpr_details LOOP
888
889 SELECT ' '||rpad(i.table_name,30,' ')||lpad(i.num_recs_archived,26,' ')||' '||lpad(i.num_recs_purged,24,' ')
890 INTO
891 l_tmp_str
892 FROM DUAL;
893
894 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_tmp_str);
895
896 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
897
898 END LOOP;
899
900 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------------------------------------');
901
902 EXCEPTION
903
904 WHEN OTHERS THEN
905 raise;
906
907 END arpr_out;
908
909
910 END ;