[Home] [Help]
PACKAGE BODY: APPS.PA_DELETE_ACCUM_RECS
Source
1 PACKAGE BODY PA_DELETE_ACCUM_RECS AS
2 /* $Header: PAACDELB.pls 120.2 2005/09/26 15:12:32 jwhite noship $ */
3
4
5 -- This procedure deletes records from PA_PROJECT_ACCUM_COMMITMENTS
6 --
7 --
8 --Note:
9 --
10 -- With the advent of the Project-List application, the Copy_Project
11 -- PA_ACCUM_PROJ_LIST.Insert_Accum procedure inserts project-level
12 -- commitment records.
13 --
14 --
15 -- Project-level records are NOT deleted by this procedure. Instead, they are initialized
16 -- to zero amounts and NULL varchar2 columns. The corresponding lower-level records are
17 -- deleted, however.
18 --
19 --
20 --History:
21 -- xx-xxx-xxxx who? - Created
22 --
23 -- 23-OCT-2002 jwhite - Bug 2633920
24 -- Add logic to INIT, NOT Delete
25 -- project-level Project-List commitment records.
26 --
27 --
28 --
29 Procedure Delete_Project_Commitments (x_project_Id In Number,
30 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
31 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
32 x_err_code In Out NOCOPY Number ) Is --File.Sql.39 bug 4440895
33
34
35 V_Old_Stack Varchar2(630);
36 tot_recs_processed Number;
37
38 l_Prj_Lvl_Accum_Id NUMBER := NULL;
39 l_msg_count NUMBER := NULL;
40 l_msg_data VARCHAR2(2000) := NULL;
41 l_return_status VARCHAR2(1) := NULL;
42
43
44 Begin
45 V_Old_Stack := x_err_stack;
46 x_err_stack :=
47 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Commitments';
48 x_err_code := 0;
49 x_err_stage := 'Deleting PA_PROJECT_ACCUM_COMMITMENTS';
50 tot_recs_processed := 0;
51
52 pa_debug.debug(x_err_stack);
53
54
55 -- Get the Project-Level Project Accum Id.
56 -- Note: No error processing for this procedure as none expected
57 PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
58 , x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
59 , x_msg_count => l_msg_count
60 , x_msg_data => l_msg_data
61 , x_return_status => l_return_status);
62
63
64 Loop
65
66 -- Except for the Project-Level Record, Purge ALL Other Commitment Records --------------
67
68 Delete From PA_PROJECT_ACCUM_COMMITMENTS PAC
69 Where PAC.Project_Accum_id IN
70 (Select Project_Accum_id
71 from PA_PROJECT_ACCUM_HEADERS PAH
72 Where PAH.Project_Id = x_project_id
73 and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level record
74 )
75 AND rownum <= pa_proj_accum_main.x_commit_size;
76 if sql%rowcount < pa_proj_accum_main.x_commit_size then
77 /* Commented for Bug 2984871 Commit;*/
78 tot_recs_processed := tot_recs_processed + sql%rowcount;
79 Commit;
80 exit;
81 else
82 /* Commented for Bug 2984871 Commit;*/
83 tot_recs_processed := tot_recs_processed + sql%rowcount;
84 Commit;
85 end if;
86 End loop;
87
88
89 -- Initialize the Project-Level Commitments Records to Zeros/NULLs -----------
90
91 UPDATE pa_project_accum_commitments SET
92 CMT_RAW_COST_ITD = 0
93 ,CMT_RAW_COST_YTD = 0
94 ,CMT_RAW_COST_PP = 0
95 ,CMT_RAW_COST_PTD = 0
96 ,CMT_BURDENED_COST_ITD = 0
97 ,CMT_BURDENED_COST_YTD = 0
98 ,CMT_BURDENED_COST_PP = 0
99 ,CMT_BURDENED_COST_PTD = 0
100 ,CMT_QUANTITY_ITD = 0
101 ,CMT_QUANTITY_YTD = 0
102 ,CMT_QUANTITY_PP = 0
103 ,CMT_QUANTITY_PTD = 0
104 ,CMT_UNIT_OF_MEASURE = NULL
105 ,REQUEST_ID = pa_proj_accum_main.x_request_id
106 ,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
107 ,LAST_UPDATE_DATE = Trunc(sysdate)
108 ,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
109 WHERE Project_Accum_id = l_Prj_Lvl_Accum_Id;
110
111
112 COMMIT;
113
114
115 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
116
117 -- Restore the old x_err_stack;
118 x_err_stack := V_Old_Stack;
119 Exception
120 When Others Then
121 x_err_code := SQLCODE;
122 RAISE;
123 End Delete_Project_Commitments;
124
125 -- This procedure deletes records from PA_PROJECT_ACCUM_BUDGETS after
126 -- ensuring that the current Budget version has not been accumulated.
127 --
128 --
129 --Note:
130 --
131 -- With the advent of the Project-List application, the Copy_Project
132 -- PA_ACCUM_PROJ_LIST.Insert_Accum procedure inserts project-level
133 -- 'AC' and 'AR' budget_type_code budget records.
134 --
135 -- Also, with the advent of the FP model, AC/AR budget records may be inserted
136 -- for approved cost and revenue plan types.
137 --
138 -- AC/AR records are NOT deleted by this procedure. Instead, they are initialized
139 -- to zero amounts and NULL varchar2 columns. The corresponding lower-level records are
140 -- deleted, however.
141 --
142 -- If x_budget_Type_Code is specified, then it is primarily for r11.5.7 Budget Type processing,
143 -- BUT PLEASE NOTE the following:
144 --
145 -- 1) If the passed value is 'AC' or 'AR', then AC/AR records, whether created for
146 -- a r11.5.7 Budget or FP model will be processed.
147 --
148 -- 2) Otherwise, only matching r11.5.7 budget records will be processed.
149 --
150 --
151 --
152 --History:
153 -- xx-xxx-xxxx who? - Created
154 --
155 -- 26-SEP-2002 jwhite - Converted to support both r11.5.7 Budget and FP models.
156 -- If the x_budget_Type_Code is NULL,
157 -- THEN purge ALL qaulifying budget and FP records.
158 -- ELSE
159 -- only purge x_budget_Type_Code budget_type records.
160 --
161 -- 23-OCT-2002 jwhite - Bug 2633920
162 -- Add logic to INIT, NOT Delete
163 -- project-level Project-List budget records.
164 --
165 -- 16-SEP-2005 jwhite - Bug bug 4583454
166 -- Agumented purge functionality for "budget_type_code" support
167 -- of the following Financial Plan types:
168 -- 1) PRIMARY_COST_FORECAST_FLAG = FC
169 -- 2) PRIMARY_REV_FORECAST_FLAG = FR
170 --
171 --
172
173 Procedure Delete_Project_Budgets (x_project_Id In Number,
174 x_budget_Type_Code In Varchar2,
175 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
176 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
177 x_err_code In Out NOCOPY Number ) Is --File.Sql.39 bug 4440895
178
179
180
181
182 -- Use this Cursor to Retrieve the following:
183 --
184 -- 1) The specified r11.5.7 budget_type_code
185 --
186 -- 2) The FP model entities corresponding to r11.5.7 x_budget_type_code in ('AC', 'AR')
187 --
188 -- 3) A-L-L r11.5.7 Budget and FP Model budget records
189 --
190 -- Please Note:
191 -- Unlike the summarization pa_project_accum_budgets INSERT logic, this cursor logic does NOT need
192 -- to consider as many cases for the FP Model for the following reasons:
193 --
194 -- 1) This logic simply deletes records. It is not concerned with double-counting amounts.
195 --
196 -- 2) If to_char(fin_plan_type_id) returns a value that has
197 -- NOT been previously inserted as budget record, then the delete will simply not purge anything
198 -- for that cursor record. No harm.
199 --
200 --
201
202 Cursor Budget_ver_cur
203 IS
204 SELECT PAB.budget_type_code budget_type_code
205 FROM
206 (
207 SELECT pabv.budget_type_code budget_type_code
208 FROM PA_BUDGET_VERSIONS PABV
209 WHERE pabv.Project_id = x_project_id
210 AND pabv.Current_Flag = 'Y'
211 AND pabv.Resource_Accumulated_Flag = 'N'
212 and pabv.budget_type_code IS NOT NULL -- r11.5.7 Budget Model
213 and pabv.Budget_type_code = nvl(x_budget_type_code, pabv.Budget_type_code)
214 UNION ALL
215 SELECT to_char(fin_plan_type_id) budget_type_code
216 FROM PA_BUDGET_VERSIONS PABV
217 WHERE pabv.Project_id = x_project_id
218 AND pabv.Current_Flag = 'Y'
219 AND pabv.Resource_Accumulated_Flag = 'N'
220 and pabv.budget_type_code IS NULL -- Strictly FP model, NO AC/AR budget_type_codes
221 and x_budget_type_code IS NULL
222 UNION ALL
223 SELECT 'AC' budget_type_code
224 FROM PA_BUDGET_VERSIONS PABV
225 WHERE pabv.Project_id = x_project_id
226 AND pabv.Current_Flag = 'Y'
227 AND pabv.Resource_Accumulated_Flag = 'N'
228 and pabv.budget_type_code IS NULL -- FP model, Approved Cost
229 and nvl(pabv.approved_cost_plan_type_flag, 'N') = 'Y'
230 and 'AC' = nvl(x_budget_type_code, 'AC')
231 UNION ALL
232 SELECT 'AR' budget_type_code
233 FROM PA_BUDGET_VERSIONS PABV
234 WHERE pabv.Project_id = x_project_id
235 AND pabv.Current_Flag = 'Y'
236 AND pabv.Resource_Accumulated_Flag = 'N'
237 and pabv.budget_type_code IS NULL -- FP model, Approved Revenue
238 and nvl(pabv.approved_rev_plan_type_flag, 'N') = 'Y'
239 and 'AR' = nvl(x_budget_type_code, 'AR')
240 UNION ALL
241 SELECT 'FC' budget_type_code
242 FROM PA_BUDGET_VERSIONS PABV
243 WHERE pabv.Project_id = x_project_id
244 AND pabv.Current_Flag = 'Y'
245 AND pabv.Resource_Accumulated_Flag = 'N'
246 and pabv.budget_type_code IS NULL -- FP model, PRIMARY FORECAST Cost
247 and nvl(pabv.primary_cost_forecast_flag, 'N') = 'Y'
248 and 'FC' = nvl(x_budget_type_code, 'FC')
249 UNION ALL
250 SELECT 'FR' budget_type_code
251 FROM PA_BUDGET_VERSIONS PABV
252 WHERE pabv.Project_id = x_project_id
253 AND pabv.Current_Flag = 'Y'
254 AND pabv.Resource_Accumulated_Flag = 'N'
255 and pabv.budget_type_code IS NULL -- FP model, PRIMARY FORECAST Revenue
256 and nvl(pabv.primary_rev_forecast_flag, 'N') = 'Y'
257 and 'FR' = nvl(x_budget_type_code, 'FR')
258 ) PAB;
259
260
261 V_Old_Stack Varchar2(630);
262 budget_ver_rec Budget_ver_cur%ROWTYPE;
263
264 l_Prj_Lvl_Accum_Id NUMBER := NULL;
265 l_msg_count NUMBER := NULL;
266 l_msg_data VARCHAR2(2000) := NULL;
267 l_return_status VARCHAR2(1) := NULL;
268
269 Begin
270
271 V_Old_Stack := x_err_stack;
272 x_err_stack :=
273 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Budgets';
274 x_err_code := 0;
275 x_err_stage := 'deleteing pa_project_accum_budgets';
276
277 pa_debug.debug(x_err_stack);
278
279
280 -- Get the Project-Level Project Accum Id.
281 -- Note: No error processing for this procedure as none expected
282 PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
283 , x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
284 , x_msg_count => l_msg_count
285 , x_msg_data => l_msg_data
286 , x_return_status => l_return_status);
287
288
289
290 FOR Budget_ver_rec IN Budget_ver_cur
291
292 LOOP
293
294 IF (Budget_ver_rec.budget_type_code IN ('AC', 'AR')
295 )
296 THEN
297 -- Project-List AC/AR Required Budget Types -------------
298
299
300 -- Except for the AC and AR Project-Level records, Purge ALL Other Records
301 -- because specified tasks/resources may have changed
302 -- since last baseline.
303
304 Delete From PA_PROJECT_ACCUM_BUDGETS
305 Where Budget_Type_Code = Budget_ver_rec.budget_type_code
306 and Project_Accum_id IN
307 (Select Project_Accum_id
308 from PA_PROJECT_ACCUM_HEADERS PAH
309 Where PAH.Project_Id = x_project_id
310 and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level records
311 );
312
313 -- INIT AC/AR Project-Level Budget Record to Zeros/NULLs ----------
314
315 UPDATE pa_project_accum_budgets SET
316 BASE_RAW_COST_ITD = 0
317 ,BASE_RAW_COST_YTD = 0
318 ,BASE_RAW_COST_PP = 0
319 ,BASE_RAW_COST_PTD = 0
320 ,BASE_BURDENED_COST_ITD = 0
321 ,BASE_BURDENED_COST_YTD = 0
322 ,BASE_BURDENED_COST_PP = 0
323 ,BASE_BURDENED_COST_PTD = 0
324 ,ORIG_RAW_COST_ITD = 0
325 ,ORIG_RAW_COST_YTD = 0
326 ,ORIG_RAW_COST_PP = 0
327 ,ORIG_RAW_COST_PTD = 0
328 ,ORIG_BURDENED_COST_ITD = 0
329 ,ORIG_BURDENED_COST_YTD = 0
330 ,ORIG_BURDENED_COST_PP = 0
331 ,ORIG_BURDENED_COST_PTD = 0
332 ,BASE_QUANTITY_ITD = 0
333 ,BASE_QUANTITY_YTD = 0
334 ,BASE_QUANTITY_PP = 0
335 ,BASE_QUANTITY_PTD = 0
336 ,ORIG_QUANTITY_ITD = 0
337 ,ORIG_QUANTITY_YTD = 0
338 ,ORIG_QUANTITY_PP = 0
339 ,ORIG_QUANTITY_PTD = 0
340 ,BASE_LABOR_HOURS_ITD = 0
341 ,BASE_LABOR_HOURS_YTD = 0
342 ,BASE_LABOR_HOURS_PP = 0
343 ,BASE_LABOR_HOURS_PTD = 0
344 ,ORIG_LABOR_HOURS_ITD = 0
345 ,ORIG_LABOR_HOURS_YTD = 0
346 ,ORIG_LABOR_HOURS_PP = 0
347 ,ORIG_LABOR_HOURS_PTD = 0
348 ,BASE_REVENUE_ITD = 0
349 ,BASE_REVENUE_YTD = 0
350 ,BASE_REVENUE_PP = 0
351 ,BASE_REVENUE_PTD = 0
352 ,ORIG_REVENUE_ITD = 0
353 ,ORIG_REVENUE_YTD = 0
354 ,ORIG_REVENUE_PP = 0
355 ,ORIG_REVENUE_PTD = 0
356 ,BASE_UNIT_OF_MEASURE = NULL
357 ,ORIG_UNIT_OF_MEASURE = NULL
358 ,BASE_RAW_COST_TOT = 0
359 ,BASE_BURDENED_COST_TOT = 0
360 ,ORIG_RAW_COST_TOT = 0
361 ,ORIG_BURDENED_COST_TOT = 0
362 ,BASE_REVENUE_TOT = 0
363 ,ORIG_REVENUE_TOT = 0
364 ,BASE_LABOR_HOURS_TOT = 0
365 ,ORIG_LABOR_HOURS_TOT = 0
366 ,BASE_QUANTITY_TOT = 0
367 ,ORIG_QUANTITY_TOT = 0
368 ,REQUEST_ID = pa_proj_accum_main.x_request_id
369 ,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
370 ,LAST_UPDATE_DATE = Trunc(sysdate)
371 ,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
372 WHERE Budget_Type_Code = Budget_ver_rec.budget_type_code
373 AND Project_Accum_id = l_Prj_Lvl_Accum_Id;
374
375
376 ELSE
377
378 -- Purge ALL r11.5.7 Budget and/or FP Budget Records
379
380 Delete From PA_PROJECT_ACCUM_BUDGETS
381 Where Budget_Type_Code = Budget_ver_rec.budget_type_code
382 and Project_Accum_id IN
383 (Select Project_Accum_id
384 from PA_PROJECT_ACCUM_HEADERS PAH
385 Where PAH.Project_Id = x_project_id
386 );
387
388
389 END IF; -- AC/AR Project List Budget Type
390
391
392 /* Bug 2984871: Commented the commit and added it after the debug call
393 Commit;*/
394 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(SQL%ROWCOUNT));
395 Commit;
396
397 END LOOP;
398
399
400
401 -- Restore the old x_err_stack;
402 x_err_stack := V_Old_Stack;
403 Exception
404 When Others Then
405 x_err_code := SQLCODE;
406 RAISE;
407 End Delete_Project_Budgets;
408
409 -- This procedure deletes records from PA_PROJECT_ACCUM_ACTUALS
410 --
411 --
412 --Note:
413 --
414 -- With the advent of the Project-List application, the Copy_Project
415 -- PA_ACCUM_PROJ_LIST.Insert_Accum procedure inserts project-level
416 -- actuals records.
417 --
418 --
419 -- Project-level records are NOT deleted by this procedure. Instead, they are initialized
420 -- to zero amounts and NULL varchar2 columns. The corresponding lower-level records are
421 -- deleted, however.
422 --
423 --
424 --History:
425 -- xx-xxx-xxxx who? - Created
426 --
427 -- 23-OCT-2002 jwhite - Bug 2633920
428 -- Add logic to INIT, NOT Delete
429 -- project-level Project-List actuals records.
430 --
431 --
432 Procedure Delete_Project_Actuals (x_project_Id In Number,
433 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
434 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
435 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
436
437 V_Old_Stack Varchar2(630);
438 tot_recs_processed Number;
439
440
441 l_Prj_Lvl_Accum_Id NUMBER := NULL;
442 l_msg_count NUMBER := NULL;
443 l_msg_data VARCHAR2(2000) := NULL;
444 l_return_status VARCHAR2(1) := NULL;
445
446 Begin
447 V_Old_Stack := x_err_stack;
448 x_err_stack :=
449 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Actuals';
450 x_err_code := 0;
451 x_err_stage := 'deleting PA_PROJECT_ACCUM_ACTUALS';
452 tot_recs_processed := 0;
453
454 pa_debug.debug(x_err_stack);
455
456
457
458
459 -- Get the Project-Level Project Accum Id.
460 -- Note: No error processing for this procedure as none expected
461 PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
462 , x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
463 , x_msg_count => l_msg_count
464 , x_msg_data => l_msg_data
465 , x_return_status => l_return_status);
466
467
468
469 LOOP
470
471 -- Except for Project-Level Record, Purge ALL Other Commitment Records --------------
472
473 Delete From PA_PROJECT_ACCUM_ACTUALS PAA
474 Where PAA.Project_Accum_id IN
475 (Select Project_Accum_id
476 from PA_PROJECT_ACCUM_HEADERS PAH
477 Where PAH.Project_Id = x_project_id
478 and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level records
479 )
480 AND rownum <= pa_proj_accum_main.x_commit_size;
481 if sql%rowcount < pa_proj_accum_main.x_commit_size then
482 /* Commented for Bug 2984871 Commit;*/
483 tot_recs_processed := tot_recs_processed + sql%rowcount;
484 Commit;
485 exit;
486 else
487 /* Commented for Bug 2984871 Commit;*/
488 tot_recs_processed := tot_recs_processed + sql%rowcount;
489 Commit;
490 end if;
491
492 End loop;
493
494
495 -- Initialize the Project-Level Commitments Records to Zeros/NULLs -----------
496
497 UPDATE pa_project_accum_actuals SET
498 RAW_COST_ITD = 0
499 ,RAW_COST_YTD = 0
500 ,RAW_COST_PP = 0
501 ,RAW_COST_PTD = 0
502 ,BILLABLE_RAW_COST_ITD = 0
503 ,BILLABLE_RAW_COST_YTD = 0
504 ,BILLABLE_RAW_COST_PP = 0
505 ,BILLABLE_RAW_COST_PTD = 0
506 ,BURDENED_COST_ITD = 0
507 ,BURDENED_COST_YTD = 0
508 ,BURDENED_COST_PP = 0
509 ,BURDENED_COST_PTD = 0
510 ,BILLABLE_BURDENED_COST_ITD = 0
511 ,BILLABLE_BURDENED_COST_YTD = 0
512 ,BILLABLE_BURDENED_COST_PP = 0
513 ,BILLABLE_BURDENED_COST_PTD = 0
514 ,QUANTITY_ITD = 0
515 ,QUANTITY_YTD = 0
516 ,QUANTITY_PP = 0
517 ,QUANTITY_PTD = 0
518 ,LABOR_HOURS_ITD = 0
519 ,LABOR_HOURS_YTD = 0
520 ,LABOR_HOURS_PP = 0
521 ,LABOR_HOURS_PTD = 0
522 ,BILLABLE_QUANTITY_ITD = 0
523 ,BILLABLE_QUANTITY_YTD = 0
524 ,BILLABLE_QUANTITY_PP = 0
525 ,BILLABLE_QUANTITY_PTD = 0
526 ,BILLABLE_LABOR_HOURS_ITD = 0
527 ,BILLABLE_LABOR_HOURS_YTD = 0
528 ,BILLABLE_LABOR_HOURS_PP = 0
529 ,BILLABLE_LABOR_HOURS_PTD = 0
530 ,REVENUE_ITD = 0
531 ,REVENUE_YTD = 0
532 ,REVENUE_PP = 0
533 ,REVENUE_PTD = 0
534 ,TXN_UNIT_OF_MEASURE = NULL
535 ,REQUEST_ID = pa_proj_accum_main.x_request_id
536 ,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
537 ,LAST_UPDATE_DATE = Trunc(sysdate)
538 ,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
539 WHERE Project_Accum_id = l_Prj_Lvl_Accum_Id;
540
541
542 COMMIT;
543
544 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
545 -- Restore the old x_err_stack;
546
547 x_err_stack := V_Old_Stack;
548 Exception
549 When Others Then
550 x_err_code := SQLCODE;
551 RAISE;
552
553 End Delete_Project_Actuals;
554
555 -- This procedure deletes records from the PA_PROJECT_ACCUM_ACTUALS table
556 -- for the given Resource List
557 --
558 -- Note: This procedure does not require modification for Project List
559 -- functionality.
560 --
561
562 Procedure Delete_Res_List_Actuals (x_project_id In Number,
563 x_Resource_list_id In Number,
564 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
565 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
566 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
567
568
569 V_Old_Stack Varchar2(630);
570 tot_recs_processed Number;
571 Begin
572 V_Old_Stack := x_err_stack;
573 x_err_stack :=
574 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Res_List_Actuals';
575 x_err_code := 0;
576 x_err_stage := 'deleteing PA_PROJECT_ACCUM_ACTUALS';
577 tot_recs_processed := 0;
578 pa_debug.debug(x_err_stack);
579
580 Loop
581
582 Delete From PA_PROJECT_ACCUM_ACTUALS PAA
583 Where Project_Accum_id IN
584 (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
585 PAH.Project_Id = x_project_id and
586 PAH.resource_list_member_id <> 0 and
587 PAH.Resource_List_id = NVL(x_Resource_list_id,PAH.Resource_List_id))
588 and rownum <= pa_proj_accum_main.x_commit_size;
589
590 if sql%rowcount < pa_proj_accum_main.x_commit_size then
591 /* Commented for Bug 2984871 Commit;*/
592 tot_recs_processed := tot_recs_processed + sql%rowcount;
593 Commit;
594 exit;
595 else
596 /* Commented for Bug 2984871 Commit;*/
597 tot_recs_processed := tot_recs_processed + sql%rowcount;
598 Commit;
599 end if;
600 End loop;
601
602 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
603
604 -- Restore the old x_err_stack;
605
606 x_err_stack := V_Old_Stack;
607 Exception
608 When Others Then
609 x_err_code := SQLCODE;
610 RAISE;
611
612 End Delete_Res_List_Actuals;
613
614 -- This procedure deletes records from PA_PROJECT_ACCUM_COMMITMENTS for the
615 -- given Resource List
616 --
617 -- Note: This procedure does not require modification for Project List
618 -- functionality.
619 --
620
621 Procedure Delete_Res_List_Commitments (x_project_id In Number,
622 x_Resource_list_id In Number,
623 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
624 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
625 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
626
627
628 V_Old_Stack Varchar2(630);
629 tot_recs_processed Number;
630 Begin
631 V_Old_Stack := x_err_stack;
632 x_err_stack :=
633 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Res_List_Commitments';
634 x_err_code := 0;
635 x_err_stage := 'deleting PA_PROJECT_ACCUM_COMMITMENTS';
636 tot_recs_processed := 0;
637
638 pa_debug.debug(x_err_stack);
639
640 Loop
641
642 Delete From PA_PROJECT_ACCUM_COMMITMENTS PAC
643 Where Project_Accum_id IN
644 (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
645 PAH.Project_Id = x_project_id and
646 PAH.resource_list_member_id <> 0 and
647 PAH.Resource_List_id = NVL(x_Resource_list_id,PAH.Resource_List_id))
648 and rownum <= pa_proj_accum_main.x_commit_size;
649
650 if sql%rowcount < pa_proj_accum_main.x_commit_size then
651 /* Commented for Bug 2984871 Commit;*/
652 tot_recs_processed := tot_recs_processed + sql%rowcount;
653 Commit;
654 exit;
655 else
656 /* Commented for Bug 2984871 Commit;*/
657 tot_recs_processed := tot_recs_processed + sql%rowcount;
658 Commit;
659 end if;
660 End loop;
661
662
663 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
664
665 -- Restore the old x_err_stack;
666 x_err_stack := V_Old_Stack;
667 Exception
668 When Others Then
669 x_err_code := SQLCODE;
670 RAISE;
671 End Delete_Res_List_Commitments;
672
673 -- This procedure deletes records from PA_PROJECT_ACCUM_HEADERS if there
674 -- are NO corresponding records in ACTUALS,COMMITMENTS and BUDGETS tables
675 -- for the given project.
676 --
677 --
678 --Note:
679 --
680 -- With the advent of the Project-List application, the Copy_Project
681 -- PA_ACCUM_PROJ_LIST.Insert_Accum procedure inserts project-level
682 -- actuals records.
683 --
684 -- Therefore, PA_PROJECT_ACCUM_HEADER records for lower-level Project-level records are
685 -- NOT deleted by this procedure.
686 --
687 --
688 --History:
689 -- xx-xxx-xxxx who? - Created
690 --
691 -- 23-OCT-2002 jwhite - Bug 2633920
692 -- Add logic to prevent deletion of
693 -- project-level Project-List records.
694 --
695 --
696
697 Procedure Delete_Project_Accum_Headers (x_project_id In Number,
698 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
699 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
700 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
701
702
703 V_Old_Stack Varchar2(630);
704 tot_recs_processed Number;
705
706
707 l_Prj_Lvl_Accum_Id NUMBER := NULL;
708 l_msg_count NUMBER := NULL;
709 l_msg_data VARCHAR2(2000) := NULL;
710 l_return_status VARCHAR2(1) := NULL;
711
712 Begin
713 V_Old_Stack := x_err_stack;
714 x_err_stack :=
715 x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Accum_Headers';
716 x_err_code := 0;
717 x_err_stage := 'deleting PA_PROJECT_ACCUM_HEADERS';
718 tot_recs_processed := 0;
719
720 pa_debug.debug(x_err_stack);
721
722
723
724 -- Get the Project-Level Project Accum Id.
725 -- Note: No error processing for this procedure as none expected
726 PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
727 , x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
728 , x_msg_count => l_msg_count
729 , x_msg_data => l_msg_data
730 , x_return_status => l_return_status);
731
732
733 Loop
734
735
736 Delete From PA_PROJECT_ACCUM_HEADERS PAH Where
737 PAH.Project_Id = x_project_id
738 AND PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Don't delete project-level row details.
739 AND Not Exists
740 (Select 'Yes' from PA_PROJECT_ACCUM_ACTUALS PAA
741 Where PAH.PROJECT_ACCUM_ID = PAA.PROJECT_ACCUM_ID)
742 AND Not Exists
743 (Select 'Yes' from PA_PROJECT_ACCUM_COMMITMENTS PAC
744 Where PAH.PROJECT_ACCUM_ID = PAC.PROJECT_ACCUM_ID)
745 AND Not Exists
746 (Select 'Yes' from PA_PROJECT_ACCUM_BUDGETS PAB
747 Where PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID)
748 and rownum <= pa_proj_accum_main.x_commit_size;
749
750
751 if sql%rowcount < pa_proj_accum_main.x_commit_size then
752 /* Commented for Bug 2984871 Commit;*/
753 tot_recs_processed := tot_recs_processed + sql%rowcount;
754 Commit;
755 exit;
756 else
757 /* Commented for Bug 2984871 Commit;*/
758 tot_recs_processed := tot_recs_processed + sql%rowcount;
759 Commit;
760 end if;
761 End loop;
762
763 pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
764
765 -- Restore the old x_err_stack;
766
767 x_err_stack := V_Old_Stack;
768 Exception
769 When Others Then
770 x_err_code := SQLCODE;
771 RAISE;
772
773 End Delete_Project_Accum_Headers;
774
775
776
777 -- This procedure returns the project-level project_accum_id for the
778 -- passed p_project_id IN-parameter.
779 --
780 --Called subprograms: None.
781 --
782 --Note:
783 --
784 --
785 --History:
786 -- 23-OCT-2002 jwhite - Created per bug 2633920
787 --
788 --
789 PROCEDURE Get_Prj_Lvl_Accum_Id (p_project_id IN NUMBER
790 , x_Prj_Lvl_Accum_Id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
791 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
792 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
793 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
794 ) IS
795
796
797
798 l_Prj_Lvl_Accum_Id pa_project_accum_headers.project_accum_id%TYPE := NULL;
799
800
801 BEGIN
802
803 -- Assume Success
804 x_return_status := FND_API.G_RET_STS_SUCCESS;
805 x_msg_count := 0;
806 x_msg_data := NULL;
807
808
809 IF (p_project_id <> nvl(PA_DELETE_ACCUM_RECS.G_Prj_Lvl_project_id, '-99')
810 )
811 THEN
812
813 BEGIN
814
815 -- FETCH New Project-Level Project_Accum_Id
816
817 SELECT project_accum_id
818 INTO l_Prj_Lvl_Accum_Id
819 FROM pa_project_accum_headers
820 WHERE project_id = p_project_id
821 AND task_id = 0
822 AND resource_list_member_id = 0;
823
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 l_Prj_Lvl_Accum_Id := -99;
827
828 END;
829
830 -- Store Package Spec Globals for Future Calls to this Procedure.
831
832 PA_DELETE_ACCUM_RECS.G_Prj_Lvl_project_id := p_project_id;
833 PA_DELETE_ACCUM_RECS.G_Prj_Lvl_Accum_Id := l_Prj_Lvl_Accum_Id;
834
835
836
837 END IF;
838
839
840 -- Return Previously Stored Project-Level Project_Accum_Id
841
842 x_Prj_Lvl_Accum_Id := PA_DELETE_ACCUM_RECS.G_Prj_Lvl_Accum_Id;
843
844
845 EXCEPTION
846 WHEN OTHERS
847 THEN
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 FND_MSG_PUB.Add_Exc_Msg
850 ( p_pkg_name => 'PA_DELETE_ACCUM_RECS'
851 , p_procedure_name => 'GET_PRJ_LVL_ACCUM_ID'
852 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
853 );
854 FND_MSG_PUB.Count_And_Get
855 (p_count => x_msg_count ,
856 p_data => x_msg_data );
857 RETURN;
858
859
860 END Get_Prj_Lvl_Accum_Id;
861
862
863
864
865
866 End PA_DELETE_ACCUM_RECS;