[Home] [Help]
PACKAGE BODY: APPS.PA_BASELINE_FUNDING_PKG
Source
1 PACKAGE BODY pa_baseline_funding_pkg AS
2 --$Header: PAXBBFPB.pls 120.5 2011/01/20 12:52:50 spokanat ship $
3
4 /*----------------------------------------------------------------------------------------+
5 | Procedure : create_draft |
6 | Purpose : |
7 | |
8 | Parameters : |
9 | ================================================================================== |
10 | Name Mode Description |
11 | ================================================================================== |
12 | x_multi_currency_billing_flag OUT Indicates multi_currency_billing_flag |
13 | is allowed for this OU |
14 | x_share_bill_rates_across_ou OUT Indicates sharing Bill rates schedules |
15 | across OU is allowed for this OU |
16 | x_allow_funding_across_ou OUT Indicates funding across OU is allowed for |
17 | this OU |
18 | x_default_exchange_rate_type OUT Default value for rate type |
19 | x_functional_currency OUT Functional currency of OU |
20 | x_return_status OUT Return status of this procedure |
21 | x_msg_count OUT Error message count |
22 | x_msg_data OUT Error message |
23 | ================================================================================== |
24 +----------------------------------------------------------------------------------------*/
25
26 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
27 g_module_name VARCHAR2(100) := 'pa.plsql.PA_BASELINE_FUNDING_PKG';
28
29 PROCEDURE create_draft (
30 p_project_id IN NUMBER,
31 p_start_date IN DATE,
32 p_end_date IN DATE,
33 p_resource_list_id IN NUMBER,
34 x_budget_version_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
35 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
36 x_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
37
38 IS
39
40
41 CURSOR budget_version IS
42 SELECT max(budget_version_id)
43 FROM pa_budget_versions
44 WHERE project_id = p_project_id
45 AND budget_type_code = 'AR'
46 AND budget_status_code = 'W'
47 AND version_number = 1;
48
49
50
51 l_budget_version_id NUMBER;
52 l_err_code NUMBER;
53 l_err_stage VARCHAR2(120);
54 l_funding_level VARCHAR2(1);
55 l_budget_entry_method_code VARCHAR2(30);
56 l_err_stack VARCHAR2(250);
57
58 lx_budget_version_id NUMBER;
59
60
61 BEGIN
62
63
64 /* ATG Changes */
65 lx_budget_version_id := x_budget_version_id;
66
67
68 x_err_code := 0;
69 IF p_pa_debug_mode = 'Y' THEN
70 pa_debug.set_err_stack('PA_BASELINE_FUNDING_PKG.CREATE_DRAFT');
71 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
72 END IF;
73
74 IF P_PA_DEBUG_MODE = 'Y' THEN
75 pa_debug.g_err_stage:= 'Calling check_funding_level';
76 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
77 END IF;
78
79 pa_billing_core.check_funding_level (
80 x_project_id => p_project_id,
81 x_funding_level => l_funding_level,
82 x_err_code => l_err_code,
83 x_err_stage => l_err_stage,
84 x_err_stack => l_err_stack);
85
86 IF P_PA_DEBUG_MODE = 'Y' THEN
87 pa_debug.g_err_stage:= 'After check_funding_level, funding level - '||l_funding_level;
88 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
89 pa_debug.g_err_stage:= 'error code - '||l_err_code;
90 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
91 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
92 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
93 END IF;
94
95 IF l_err_code <> 0 then
96 x_err_code := l_err_code;
97 x_status := l_err_stage;
98 END IF;
99
100
101 IF x_err_code = 0 then
102
103 IF l_funding_level = 'P' then
104 l_budget_entry_method_code := 'PA_PROJLVL_BASELINE';
105 ELSIF l_funding_level = 'T' then
106 l_budget_entry_method_code := 'PA_TASKLVL_BASELINE';
107 END IF;
108
109 IF P_PA_DEBUG_MODE = 'Y' THEN
110 pa_debug.g_err_stage:= 'Calling budget_utils create_draft';
111 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
112 END IF;
113
114 pa_budget_utils.create_draft
115 (x_project_id => p_project_id
116 ,x_budget_type_code => 'AR'
117 ,x_version_name => 'Revenue Budget 1'
118 ,x_description => 'Default Created by Projects'
119 ,x_resource_list_id => p_resource_list_id
120 ,x_change_reason_code => null
121 ,x_budget_entry_method_code => l_budget_entry_method_code
122 ,x_attribute_category => null
123 ,x_attribute1 => null
124 ,x_attribute2 => null
125 ,x_attribute3 => null
126 ,x_attribute4 => null
127 ,x_attribute5 => null
128 ,x_attribute6 => null
129 ,x_attribute7 => null
130 ,x_attribute8 => null
131 ,x_attribute9 => null
132 ,x_attribute10 => null
133 ,x_attribute11 => null
134 ,x_attribute12 => null
135 ,x_attribute13 => null
136 ,x_attribute14 => null
137 ,x_attribute15 => null
138 ,x_budget_version_id => l_budget_version_id
139 ,x_err_code => l_err_code
140 ,x_err_stage => l_err_stage
141 ,x_err_stack => l_err_stack);
142
143 x_err_code := l_err_code;
144
145 IF P_PA_DEBUG_MODE = 'Y' THEN
146 pa_debug.g_err_stage:= 'After create_draft , budget version id - '||l_budget_version_id;
147 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
148 pa_debug.g_err_stage:= 'error code is -'||l_err_code;
149 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
150 pa_debug.g_err_stage:= 'error code is -'||l_err_stage;
151 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
152 END IF;
153
154 IF x_err_code <> 0 THEN
155
156 x_status := l_err_stage;
157
158 END IF;
159
160 END IF ;
161
162 IF x_err_code = 0 then
163
164 IF P_PA_DEBUG_MODE = 'Y' THEN
165 pa_debug.g_err_stage:= 'Error Code is 0';
166 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
167 END IF;
168
169 OPEN budget_version;
170 FETCH budget_version into l_budget_version_id;
171 CLOSE budget_version;
172
173 IF P_PA_DEBUG_MODE = 'Y' THEN
174 pa_debug.g_err_stage:= 'Budget version ID in the table - '||l_budget_version_id;
175 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
176 END IF;
177 x_budget_version_id := l_budget_version_id;
178
179 END IF;
180
181 IF P_PA_DEBUG_MODE = 'Y' THEN
182 pa_debug.g_err_stage:= 'x_budget_version_id = '||x_budget_version_id;
183 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
184 END IF;
185
186 pa_debug.reset_err_stack;
187
188 EXCEPTION
189
190 WHEN OTHERS THEN
191
192 x_err_code := SQLCODE;
193 x_status := substr(SQLERRM,1,50);
194
195
196 /* ATG Changes */
197
198 x_budget_version_id := lx_budget_version_id;
199
200 IF P_PA_DEBUG_MODE = 'Y' THEN
201 pa_debug.g_err_stage:= 'Exception in create_draft, error - '||x_status;
202 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
203 END IF;
204 pa_debug.reset_err_stack;
205 END create_draft;
206
207
208 PROCEDURE create_line (
209 p_project_id IN NUMBER,
210 p_start_date IN DATE,
211 p_end_date IN DATE,
212 p_resource_list_member_id IN NUMBER,
213 p_budget_version_id IN NUMBER,
214 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
215 x_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
216
217 IS
218
219 CURSOR funding_amount (x_funding_level varchar) is
220 SELECT task_id, sum(nvl(projfunc_allocated_amount,0)) fund_amt
221 FROM pa_project_fundings
222 WHERE project_id = p_project_id
223 AND ( (budget_type_code IN ('DRAFT', 'BASELINE') AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N')
224 OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y' AND (
225 ( (budget_type_code ='BASELINE') OR
226 (budget_type_code ='DRAFT' AND funding_category=
227 'REVALUATION') ))))
228 AND ((x_funding_level = 'T' and task_id is not null)
229 or (x_funding_level = 'P' and task_id is null))
230 group by task_id;/*Bug 8718600*/
231
232 l_start_date DATE;
233 l_end_date DATE;
234 l_resource_assignment_id NUMBER;
235 l_err_stack VARCHAR2(250);
236 l_err_code NUMBER;
237 l_err_stage VARCHAR2(120);
238
239 l_quantity NUMBER;
240 l_raw_cost NUMBER;
241 l_burdened_cost NUMBER;
242 l_task_id NUMBER;
243
244 l_funding_level VARCHAR2(1);/*Bug 8718600*/
245
246 BEGIN
247
248 pa_billing_core.check_funding_level (
249 x_project_id => p_project_id,
250 x_funding_level => l_funding_level,
251 x_err_code => l_err_code,
252 x_err_stage => l_err_stage,
253 x_err_stack => l_err_stack);/*Bug 8718600*/
254
255
256
257 x_err_code := 0;
258 IF p_pa_debug_mode = 'Y' THEN
259 pa_debug.set_err_stack('PA_BASELINE_FUNDING_PKG.CREATE_LINE');
260 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
261 END IF;
262
263 FOR f1_rec in funding_amount (l_funding_level) loop /*Bug 8718600*/
264
265 IF P_PA_DEBUG_MODE = 'Y' THEN
266 pa_debug.g_err_stage:= 'In the loop of funding_amount';
267 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
268 END IF;
269
270 if f1_rec.task_id is not null then
271
272 select start_date, completion_date
273 into l_start_date, l_end_date
274 from pa_tasks
275 where task_id = f1_rec.task_id
276 and project_id = p_project_id;
277
278 if l_start_date is null then
279
280 l_start_date := p_start_date;
281
282 end if;
283
284 if l_end_date is null then
285
286 l_end_date := p_end_date;
287
288 end if;
289 l_task_id := f1_rec.task_id;
290
291 else
292
293 l_start_date := p_start_date;
294 l_end_date := p_end_date;
295 l_task_id := 0;
296
297 end if;
298
299 IF P_PA_DEBUG_MODE = 'Y' THEN
300 pa_debug.g_err_stage:= 'start date - '||l_start_date;
301 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
302 pa_debug.g_err_stage:= 'end date - '||l_end_date;
303 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
304 pa_debug.g_err_stage:= 'task id - '||l_task_id;
305 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
306 END IF;
307
308 --dbms_output.put_line ('call util crt line');
309 IF P_PA_DEBUG_MODE = 'Y' THEN
310 pa_debug.g_err_stage:= 'Calling budget_utils create_line';
311 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
312 END IF;
313 pa_budget_utils.create_line
314 (x_budget_version_id => p_budget_version_id
315 ,x_project_id => p_project_id
316 ,x_task_id => l_task_id
317 ,x_resource_list_member_id => p_resource_list_member_id
318 ,x_description => 'Default created by projects'
319 ,x_start_date => l_start_date
320 ,x_end_date => l_end_date
321 ,x_period_name => null
322 ,x_quantity => l_quantity
323 ,x_unit_of_measure => null
324 ,x_track_as_labor_flag => 'N'
325 ,x_raw_cost => l_raw_cost
326 ,x_burdened_cost => l_burdened_cost
327 ,x_revenue => f1_rec.fund_amt
328 ,x_change_reason_code => NULL
329 ,x_attribute_category => null
330 ,x_attribute1 => null
331 ,x_attribute2 => null
332 ,x_attribute3 => null
333 ,x_attribute4 => null
334 ,x_attribute5 => null
335 ,x_attribute6 => null
336 ,x_attribute7 => null
337 ,x_attribute8 => null
338 ,x_attribute9 => null
339 ,x_attribute10 => null
340 ,x_attribute11 => null
341 ,x_attribute12 => null
342 ,x_attribute13 => null
343 ,x_attribute14 => null
344 ,x_attribute15 => null
345 -- Bug Fix: 4569365. Removed MRC code.
346 -- ,x_mrc_flag => 'Y' /* FPB2 */
347 ,x_resource_assignment_id => l_resource_assignment_id
348 ,x_err_code => l_err_code
349 ,x_err_stage => l_err_stage
350 ,x_err_stack => l_err_stack);
351
352 IF P_PA_DEBUG_MODE = 'Y' THEN
353 pa_debug.g_err_stage:= 'After budget_utils create_line';
354 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
355 pa_debug.g_err_stage:= 'error code - '||l_err_code;
356 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
357 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
358 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
359 END IF;
360
361
362 if l_err_code <> 0 then
363
364 x_err_code := l_err_code;
365 x_status := l_err_stage;
366
367 exit;
368
369 end if;
370
371 END LOOP;
372
373 IF P_PA_DEBUG_MODE = 'Y' THEN
374 pa_debug.g_err_stage:= 'End of create_line procedure';
375 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
376 END IF;
377
378 pa_debug.reset_err_stack;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382
383 x_err_code := SQLCODE;
384 x_status := substr(SQLERRM,1,50);
385
386 IF P_PA_DEBUG_MODE = 'Y' THEN
387 pa_debug.g_err_stage:= 'In exception of create_line - '||x_status;
388 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
389 END IF;
390 pa_debug.reset_err_stack;
391 END create_line;
392
393
394
395 PROCEDURE create_budget_baseline (
396 p_project_id IN NUMBER,
397 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
398 x_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
399
400 IS
401
402
403 -- 09-JUN-04, FP.M Resource List Data Model Changes, jwhite --------------------------------
404
405 /*
406 -- Original Logic
407
408 CURSOR res_info IS
409 SELECT R1.resource_list_id resource_list_id,
410 M.resource_list_member_id resource_list_member_id
411 FROM pa_resource_lists R1, pa_implementations I,
412 pa_resource_list_members M
413 WHERE R1.uncategorized_flag = 'Y'
414 AND R1.business_group_id = I.business_group_id
415 AND R1.resource_list_id = M.resource_list_id;
416 */
417
418 -- FP.M Logic for Fetching Uncategorized Resoure List Member
419
420 CURSOR res_info IS
421 SELECT R1.resource_list_id resource_list_id,
422 M.resource_list_member_id resource_list_member_id
423 FROM pa_resource_lists R1, pa_implementations I,
424 pa_resource_list_members M
425 WHERE R1.uncategorized_flag = 'Y'
426 AND R1.business_group_id = I.business_group_id
427 AND R1.resource_list_id = M.resource_list_id
428 and m.resource_class_code = 'FINANCIAL_ELEMENTS';
429
430
431
432 -- ENd: 09-JUN-04, FP.M Resource List Data Model Changes -------------------------------------
433
434
435
436 CURSOR proj_dates IS
437 SELECT start_date, completion_date
438 FROM pa_projects_all
439 WHERE project_id = p_project_id;
440 /* Added for fp */
441 CURSOR funding_amount (x_funding_level varchar) is
442 SELECT task_id, sum(nvl(projfunc_allocated_amount,0)) pf_fund_amt,
443 sum(nvl(project_allocated_amount,0)) proj_fund_amt
444 FROM pa_project_fundings
445 WHERE project_id = p_project_id
446 AND ((budget_type_code IN ('DRAFT', 'BASELINE')
447 AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N')
448 OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y'
449 AND (((budget_type_code ='BASELINE')
450 OR (budget_type_code ='DRAFT'
451 AND funding_category= 'REVALUATION')))))
452 AND ((x_funding_level = 'T' and task_id is not null)
453 or (x_funding_level = 'P' and task_id is null))
454 AND nvl(PA_Funding_Core.G_FUND_BASELINE_FLAG,'N') ='N'
455 -- FP_M changes: Added UNION clause for Change Management enhancements
456 -- This cursor is modified to fetch only from the selected
457 -- funding lines that needs to be baselined ONLY when
458 -- a global value PA_Funding_Core.G_FUND_BASELINE_FLAG is enabled
459 group by task_id
460 UNION
461 SELECT task_id,
462 sum(nvl(projfunc_allocated_amount,0)) pf_fund_amt,
463 sum(nvl(project_allocated_amount,0)) proj_fund_amt
464 FROM pa_project_fundings
465 WHERE project_id = p_project_id
466 AND PA_Funding_Core.G_FUND_BASELINE_FLAG = 'Y'
467 AND (NVL(Submit_Baseline_Flag,'N') = 'Y' OR
468 budget_type_code = 'BASELINE' )
469 AND ((x_funding_level = 'T' and task_id is not null)
470 or ( x_funding_level = 'P' and task_id is null))
471 group by task_id;/*Bug 8718600*/
472
473
474 res_info_rec res_info%ROWTYPE;
475 proj_dates_rec proj_dates%ROWTYPE;
476
477 l_budget_version_id NUMBER;
478 l_err_code NUMBER;
479 l_err_stage VARCHAR2(120);
480 l_status VARCHAR2(120);
481 l_msg_count NUMBER;
482 l_err_stack VARCHAR2(250);
483
484 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
485 /* Added for FP */
486 l_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
487 l_msg_data VARCHAR2(2000);
488 l_funding_bl_tab pa_fp_auto_baseline_pkg.funding_bl_tab;
489 i NUMBER := 0;
490 l_funding_level VARCHAR2(1);
491 /* FP.K.B3 - Funding level to be passed to FPautobseline api. This variable is not used
492 l_fp_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
493 */
494 l_start_date DATE;
495 l_end_date DATE;
496 l_task_id NUMBER;
497 l_project_id NUMBER;
498 l_max_version NUMBER(15); --Added for Bug 9740299
499 l_mark_as_original VARCHAR2(1); --Added for Bug 9740299
500
501
502 BEGIN
503
504 savepoint temp_pt;
505 x_err_code := 0;
506
507 IF p_pa_debug_mode = 'Y' THEN
508 pa_debug.set_err_stack('PA_BASELINE_FUNDING_PKG.CREATE_BUDGET_BASELINE');
509 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
510 END IF;
511
512 OPEN res_info;
513 FETCH res_info INTO res_info_rec;
514 CLOSE res_info;
515
516
517 OPEN proj_dates;
518 FETCH proj_dates INTO proj_dates_rec;
519 CLOSE proj_dates;
520
521 --dbms_output.put_line ('start date - ' || proj_dates_rec.start_date);
522 --dbms_output.put_line ('end date - ' || proj_dates_rec.completion_date);
523
524 --dbms_output.put_line ('reslisid - ' || res_info_rec.resource_list_id);
525 --dbms_output.put_line ('resmemid - ' || res_info_rec.resource_list_member_id);
526
527 /* Fix for bug#4000821 code moved from the form to package as we can't release
528 the exlicit lock in form by rollback if the baseline fails due to validation
529 as done in the bug 3739353 starts here */
530 BEGIN
531 SELECT project_id
532 INTO l_project_id
533 FROM pa_projects_all
534 WHERE project_id = p_project_id
535 FOR UPDATE NOWAIT;
536
537 EXCEPTION
538 WHEN OTHERS THEN
539 x_err_code := 20;
540 x_status := 'PA_PROJECT_LOCK_TRY_LATER';
541 PA_UTILS.Add_Message
542 ( p_app_short_name => 'PA'
543 , p_msg_name => x_status
544 );
545 END;
546
547 /* Fix for bug#4000821 ends here */
548
549
550 IF proj_dates_rec.completion_date is null then
551 x_err_code := 30;
552 x_status := 'PA_BU_NO_PROJ_END_DATE';
553 PA_UTILS.Add_Message
554 ( p_app_short_name => 'PA'
555 , p_msg_name => x_status
556 );
557
558 --dbms_output.put_line ('stat 1 - ' || x_status);
559 END IF;
560
561 IF x_err_code = 0 then
562 /* Added for fp - start*/
563 pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
564 p_project_id =>p_project_id,
565 x_plan_type_id => l_plan_type_id,
566 x_return_status => l_return_status,
567 x_msg_count => l_msg_count,
568 x_msg_data => l_msg_data
569 );
570
571 IF l_plan_type_id is NULL THEN
572 IF P_PA_DEBUG_MODE = 'Y' THEN
573 pa_debug.g_err_stage:= 'No Plan type ID';
574 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
575 END IF;
576
577 /* Added for fp - end*/
578 --dbms_output.put_line ('calling create_draft ' );
579 create_draft (
580 p_project_id => p_project_id,
581 p_start_date => proj_dates_rec.start_date,
582 p_end_date => proj_dates_rec.completion_date,
583 p_resource_list_id => res_info_rec.resource_list_id,
584 x_budget_version_id => l_budget_version_id,
585 x_err_code => l_err_code,
586 x_status => l_status );
587
588 x_err_code := l_err_code;
589 x_status := l_status;
590 --dbms_output.put_line ('after calling create_draft -' || x_err_code);
591 IF P_PA_DEBUG_MODE = 'Y' THEN
592 pa_debug.g_err_stage:= 'After create_draft, budget version id is - '||l_budget_version_id;
593 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
594 pa_debug.g_err_stage:= 'error code - '||l_err_code;
595 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
596 pa_debug.g_err_stage:= 'error status - '||l_status;
597 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
598 END IF;
599
600 /* END IF; */
601
602 IF x_err_code = 0 then
603
604 --dbms_output.put_line ('calling create_line ' );
605 create_line (
606 p_project_id => p_project_id,
607 p_start_date => proj_dates_rec.start_date,
608 p_end_date => proj_dates_rec.completion_date,
609 p_resource_list_member_id => res_info_rec.resource_list_member_id,
610 p_budget_version_id => l_budget_version_id,
611 x_err_code => l_err_code,
612 x_status => l_status );
613
614 x_err_code := l_err_code;
615 x_status := l_status;
616
617 IF P_PA_DEBUG_MODE = 'Y' THEN
618 pa_debug.g_err_stage:= 'After create_line';
619 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
620 pa_debug.g_err_stage:= 'error code - '||l_err_code;
621 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
622 pa_debug.g_err_stage:= 'error status - '||l_status;
623 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
624 END IF;
625 /*
626 ELSE
627 x_status := l_status;
628 */
629 --dbms_output.put_line ('err 2 - ' || x_err_code);
630 --dbms_output.put_line ('stat 2 - ' || x_status);
631 IF P_PA_DEBUG_MODE = 'Y' THEN
632 pa_debug.g_err_stage:= 'After create_line - 1';
633 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
634 pa_debug.g_err_stage:= 'error code - '||l_err_code;
635 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
636 pa_debug.g_err_stage:= 'error status - '||l_status;
637 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
638 END IF;
639
640 END IF;
641 /* Added for fp */
642 ELSE /* plan type is null */
643
644 pa_billing_core.check_funding_level (
645 x_project_id => p_project_id,
646 x_funding_level => l_funding_level,
647 x_err_code => l_err_code,
648 x_err_stage => l_err_stage,
649 x_err_stack => l_err_stack);
650
651 IF l_err_code <> 0 then
652 x_err_code := l_err_code;
653 x_status := l_err_stage;
654 END IF;
655
659 IF l_funding_level = 'P' THEN
656 IF x_err_code = 0 THEN
657
658 /* FP.K.B3 - PA_FP_AUTO_BASELINE_PKG.CREATE_BASELINED_VERSION - Expects funding level only
660 l_fp_level_code := 'P';
661 ELSIF l_funding_level = 'T' THEN
662 l_fp_level_code := 'T';
663 END IF;
664 */
665 FOR fp_rec IN funding_amount (l_funding_level) /*Bug 8718600*/
666 LOOP
667
668 l_start_date := NULL;
669 l_end_date := NULL;
670
671 IF fp_rec.task_id is not null
672 THEN
673 select start_date, completion_date
674 into l_start_date, l_end_date
675 from pa_tasks
676 where task_id = fp_rec.task_id
677 and project_id = p_project_id;
678
679 if l_start_date is null then
680 l_start_date := proj_dates_rec.start_date;
681 end if;
682
683 if l_end_date is null then
684 l_end_date := proj_dates_rec.completion_date;
685 end if;
686
687 l_task_id := fp_rec.task_id;
688
689 ELSE
690
691 l_start_date := proj_dates_rec.start_date;
692 l_end_date := proj_dates_rec.completion_date;
693 l_task_id := 0;
694
695 END IF;
696
697 i := i + 1;
698
699 l_funding_bl_tab(i).task_id := l_task_id;
700 l_funding_bl_tab(i).description := 'Default Created by Projects';
701 l_funding_bl_tab(i).start_date := l_start_date;
702 l_funding_bl_tab(i).end_date := l_end_date;
703 l_funding_bl_tab(i).projfunc_revenue := fp_rec.pf_fund_amt;
704 l_funding_bl_tab(i).project_revenue := fp_rec.proj_fund_amt;
705
706 END LOOP;
707 /* FP.K.B3 - The name of the procedure is CREATE_BASELINED_VERSION
708 PA_FP_AUTO_BASELINE_PKG.CREATE_AUTO_BASELINE_VERSION (
709 */
710 PA_FP_AUTO_BASELINE_PKG.CREATE_BASELINED_VERSION (
711 p_project_id => p_project_id,
712 p_fin_plan_type_id => l_plan_type_id,
713 /* p_funding_level_code => l_fp_level_code, FP.K.B3 - Parameter name changed */
714 p_funding_level_code => l_funding_level, /* FP.K.B3 - Funding level to be passed */
715 p_version_name => 'Revenue Budget 1',
716 p_description => 'Default Created by Projects',
717 p_funding_bl_tab => l_funding_bl_tab,
718 x_budget_version_id => l_budget_version_id,
719 x_return_status => l_return_status,
720 x_msg_count => l_msg_count,
721 x_msg_data => l_msg_data );
722
723 END IF; /* x_err_code */
724
725 END IF; /* plan type is null */
726
727 END IF; /* x_err_code 1 */
728 /* Added for fp - end*/
729
730 IF P_PA_DEBUG_MODE = 'Y' THEN
731 pa_debug.g_err_stage:= 'After the Fin Plan Type check';
732 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
733 pa_debug.g_err_stage:= 'error code is - '||x_err_code;
734 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
735 pa_debug.g_err_stage:= 'return status is -'||l_return_status;
736 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
737 END IF;
738
739 IF x_err_code = 0 and l_return_status = FND_API.G_RET_STS_SUCCESS then
740 IF l_plan_type_id is NULL THEN /* For bug 4198840*/
741 --dbms_output.put_line ('calling summerize ' );
742 IF P_PA_DEBUG_MODE = 'Y' THEN
743 pa_debug.g_err_stage:= 'Calling budget_utils summarize_project_totals';
744 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
745 END IF;
746
747 pa_budget_utils.summerize_project_totals (
748 x_budget_version_id => l_budget_version_id,
749 x_err_code => l_err_code,
750 x_err_stage => l_err_stage,
751 x_err_stack => l_err_stack);
752
753 IF P_PA_DEBUG_MODE = 'Y' THEN
754 pa_debug.g_err_stage:= 'After summerize, error code - '||l_err_code;
755 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
756 pa_debug.g_err_stage:= 'After summerize, error stage - '||l_err_stage;
757 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
758 END IF;
759 x_err_code := l_err_code;
760 x_status := l_err_stage;
761 /*
762 ELSE
763 x_status := l_status;
764 */
765 IF P_PA_DEBUG_MODE = 'Y' THEN
766 pa_debug.g_err_stage:= 'Error status - '||l_status;
767 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
768 END IF;
769 --dbms_output.put_line ('err 3 - ' || x_err_code);
770 --dbms_output.put_line ('stat 3 - ' || x_status);
771 END IF; --For bug 4198840
772 END IF;
773
774
775 IF x_err_code = 0 and l_plan_type_id IS NULL then
776 --dbms_output.put_line ('calling baseline ' );
777 IF P_PA_DEBUG_MODE = 'Y' THEN
778 pa_debug.g_err_stage:= 'Calling budget_core.baseline';
779 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
780 END IF;
781
782 --Start Bug 9740299
783 select nvl(max(version_number), 0)
784 into l_max_version
785 from pa_budget_versions
786 where project_id = p_project_id
787 and budget_type_code = 'AR'
788 and budget_status_code = 'B';
789
790 IF l_max_version = 0 THEN
791 l_mark_as_original := 'Y';
792 ELSE
793 l_mark_as_original := 'N';
794 END IF;
795 --End Bug 9740299
796
797 pa_budget_core.baseline(
798 x_draft_version_id => l_budget_version_id,
799 x_mark_as_original => l_mark_as_original, --Changed for Bug 9740299
800 x_verify_budget_rules => 'Y',
801 x_err_code => l_err_code,
802 x_err_stage => l_err_stage,
803 x_err_stack => l_err_stack);
804
805 x_err_code := l_err_code;
806 x_status := l_err_stage;
807 IF P_PA_DEBUG_MODE = 'Y' THEN
808 pa_debug.g_err_stage:= 'After baseline, error code - '||l_err_code;
809 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
810 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
811 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
812 END IF;
813 /*
814
815 ELSE
816 IF P_PA_DEBUG_MODE = 'Y' THEN
817 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
818 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
819 END IF;
820 x_status := l_err_stage;
821 */
822 --dbms_output.put_line ('stat 4 - ' || x_status);
823
824 END IF;
825
826 IF P_PA_DEBUG_MODE = 'Y' THEN
827 pa_debug.g_err_stage:= 'x_err_code - '||x_err_code;
828 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
829 pa_debug.g_err_stage:= 'return status - '||l_return_status;
830 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
831 END IF;
832 if x_err_code <> 0 or l_return_status <> FND_API.G_RET_STS_SUCCESS then
833 rollback to temp_pt;
834 IF P_PA_DEBUG_MODE = 'Y' THEN
835 pa_debug.g_err_stage:= 'Rolling back';
836 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
837 END IF;
838 --dbms_output.put_line ('stat 5 - ' || x_status);
839 end if;
840 pa_debug.reset_err_stack;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844
845 IF P_PA_DEBUG_MODE = 'Y' THEN
846 pa_debug.g_err_stage:= 'In exception of create_budget_baseline';
847 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
848 END IF;
849 x_err_code := SQLCODE;
850 x_status := substr(SQLERRM,1,50);
851 IF P_PA_DEBUG_MODE = 'Y' THEN
852 pa_debug.g_err_stage:= 'error code - '||x_err_code;
853 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
854 pa_debug.g_err_stage:= 'status - '||x_status;
855 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
856 END IF;
857
858 END create_budget_baseline;
859
860 -- FP_M changes:
861 -- Following APIs Proj_Agreement_Baseline and Change_Management_Baseline
862 -- are created from FP_M onwards
863
864 -- This API is for baselining only required Project's agreement's
865 -- funding lines
866 Procedure Proj_Agreement_Baseline (
867 P_Project_ID IN NUMBER,
868 P_Agreement_ID IN NUMBER,
869 X_Err_Code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
870 X_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
871 ) IS
872 BEGIN
873
874 -- Set the global value
875 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'Y';
876
877 -- First update the submit_Baseline_Flag as 'Y' and then proceed for
878 -- further baselining of the funding lines
879 Update PA_Project_Fundings
880 SET Submit_Baseline_Flag = 'Y'
881 Where Project_ID = P_Project_ID
882 AND Agreement_ID = P_Agreement_ID
883 AND CI_ID is null
884 AND Budget_Type_Code = 'DRAFT'
885 AND NVL(Submit_Baseline_Flag,'N') <> 'Y';
886
887 -- Now call the regular baselining API Create_Budget_Baseline
888 create_budget_baseline (
889 p_project_id => P_Project_ID,
890 x_err_code => X_Err_Code,
891 x_status => X_Status
892 );
893
894 -- Unset the global value back to 'N'
895 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'N';
896
897 END Proj_Agreement_Baseline;
898
899 -- FP_M changes:
900 -- This API is for baselining only required Project's agreement's
901 -- funding lines that are created thru change order management page
902 Procedure Change_Management_Baseline (
903 P_Project_ID IN NUMBER,
904 P_CI_ID_Tab IN PA_PLSQL_DATATYPES.IdTabTyp,
905 X_Err_Code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
906 X_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
907 ) IS
908 BEGIN
909
910 -- Set the global value
911 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'Y';
912
913 -- First update the submit_Baseline_Flag as 'Y' and then proceed for
914 -- further baselining of the funding lines
915 FORALL ci_rec in P_CI_ID_Tab.FIRST..P_CI_ID_Tab.LAST
916 Update PA_Project_Fundings
917 SET Submit_Baseline_Flag = 'Y'
918 Where Project_ID = P_Project_ID
919 AND CI_ID = P_CI_ID_Tab(ci_rec)
920 AND Budget_Type_Code = 'DRAFT'
921 AND NVL(Submit_Baseline_Flag,'N') <> 'Y';
922
923 -- Now call the regular baselining API Create_Budget_Baseline
924 create_budget_baseline (
925 p_project_id => P_Project_ID,
926 x_err_code => X_Err_Code,
927 x_status => X_Status
928 );
929
930 -- Unset the global value back to 'N'
931 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'N';
932
933 END Change_Management_Baseline;
934
935 END PA_BASELINE_FUNDING_PKG;