[Home] [Help]
PACKAGE BODY: APPS.PA_BASELINE_FUNDING_PKG
Source
1 PACKAGE BODY pa_baseline_funding_pkg AS
2 --$Header: PAXBBFPB.pls 120.3 2005/10/03 10:14:25 rnamburi noship $
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 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 group by task_id;
229
230 l_start_date DATE;
231 l_end_date DATE;
232 l_resource_assignment_id NUMBER;
233 l_err_stack VARCHAR2(250);
234 l_err_code NUMBER;
235 l_err_stage VARCHAR2(120);
236
237 l_quantity NUMBER;
238 l_raw_cost NUMBER;
239 l_burdened_cost NUMBER;
240 l_task_id NUMBER;
241
242
243 BEGIN
244
245
246 x_err_code := 0;
247 IF p_pa_debug_mode = 'Y' THEN
248 pa_debug.set_err_stack('PA_BASELINE_FUNDING_PKG.CREATE_LINE');
249 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
250 END IF;
251
252 FOR f1_rec in funding_amount loop
253
254 IF P_PA_DEBUG_MODE = 'Y' THEN
255 pa_debug.g_err_stage:= 'In the loop of funding_amount';
256 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
257 END IF;
258
259 if f1_rec.task_id is not null then
260
261 select start_date, completion_date
262 into l_start_date, l_end_date
263 from pa_tasks
264 where task_id = f1_rec.task_id
265 and project_id = p_project_id;
266
267 if l_start_date is null then
268
269 l_start_date := p_start_date;
270
271 end if;
272
273 if l_end_date is null then
274
275 l_end_date := p_end_date;
276
277 end if;
278 l_task_id := f1_rec.task_id;
279
280 else
281
282 l_start_date := p_start_date;
283 l_end_date := p_end_date;
284 l_task_id := 0;
285
286 end if;
287
288 IF P_PA_DEBUG_MODE = 'Y' THEN
289 pa_debug.g_err_stage:= 'start date - '||l_start_date;
290 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
291 pa_debug.g_err_stage:= 'end date - '||l_end_date;
292 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
293 pa_debug.g_err_stage:= 'task id - '||l_task_id;
294 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
295 END IF;
296
297 --dbms_output.put_line ('call util crt line');
298 IF P_PA_DEBUG_MODE = 'Y' THEN
299 pa_debug.g_err_stage:= 'Calling budget_utils create_line';
300 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
301 END IF;
302 pa_budget_utils.create_line
303 (x_budget_version_id => p_budget_version_id
304 ,x_project_id => p_project_id
305 ,x_task_id => l_task_id
306 ,x_resource_list_member_id => p_resource_list_member_id
307 ,x_description => 'Default created by projects'
308 ,x_start_date => l_start_date
309 ,x_end_date => l_end_date
310 ,x_period_name => null
311 ,x_quantity => l_quantity
312 ,x_unit_of_measure => null
313 ,x_track_as_labor_flag => 'N'
314 ,x_raw_cost => l_raw_cost
315 ,x_burdened_cost => l_burdened_cost
316 ,x_revenue => f1_rec.fund_amt
317 ,x_change_reason_code => NULL
318 ,x_attribute_category => null
319 ,x_attribute1 => null
320 ,x_attribute2 => null
321 ,x_attribute3 => null
322 ,x_attribute4 => null
323 ,x_attribute5 => null
324 ,x_attribute6 => null
325 ,x_attribute7 => null
326 ,x_attribute8 => null
327 ,x_attribute9 => null
328 ,x_attribute10 => null
329 ,x_attribute11 => null
330 ,x_attribute12 => null
331 ,x_attribute13 => null
332 ,x_attribute14 => null
333 ,x_attribute15 => null
334 -- Bug Fix: 4569365. Removed MRC code.
335 -- ,x_mrc_flag => 'Y' /* FPB2 */
336 ,x_resource_assignment_id => l_resource_assignment_id
337 ,x_err_code => l_err_code
338 ,x_err_stage => l_err_stage
339 ,x_err_stack => l_err_stack);
340
344 pa_debug.g_err_stage:= 'error code - '||l_err_code;
341 IF P_PA_DEBUG_MODE = 'Y' THEN
342 pa_debug.g_err_stage:= 'After budget_utils create_line';
343 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
345 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
346 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
347 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL1);
348 END IF;
349
350
351 if l_err_code <> 0 then
352
353 x_err_code := l_err_code;
354 x_status := l_err_stage;
355
356 exit;
357
358 end if;
359
360 END LOOP;
361
362 IF P_PA_DEBUG_MODE = 'Y' THEN
363 pa_debug.g_err_stage:= 'End of create_line procedure';
364 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
365 END IF;
366
367 pa_debug.reset_err_stack;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371
372 x_err_code := SQLCODE;
373 x_status := substr(SQLERRM,1,50);
374
375 IF P_PA_DEBUG_MODE = 'Y' THEN
376 pa_debug.g_err_stage:= 'In exception of create_line - '||x_status;
377 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
378 END IF;
379 pa_debug.reset_err_stack;
380 END create_line;
381
382
383
384 PROCEDURE create_budget_baseline (
385 p_project_id IN NUMBER,
386 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
387 x_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
388
389 IS
390
391
392 -- 09-JUN-04, FP.M Resource List Data Model Changes, jwhite --------------------------------
393
394 /*
395 -- Original Logic
396
397 CURSOR res_info IS
398 SELECT R1.resource_list_id resource_list_id,
399 M.resource_list_member_id resource_list_member_id
400 FROM pa_resource_lists R1, pa_implementations I,
401 pa_resource_list_members M
402 WHERE R1.uncategorized_flag = 'Y'
403 AND R1.business_group_id = I.business_group_id
404 AND R1.resource_list_id = M.resource_list_id;
405 */
406
407 -- FP.M Logic for Fetching Uncategorized Resoure List Member
408
409 CURSOR res_info IS
410 SELECT R1.resource_list_id resource_list_id,
411 M.resource_list_member_id resource_list_member_id
412 FROM pa_resource_lists R1, pa_implementations I,
413 pa_resource_list_members M
414 WHERE R1.uncategorized_flag = 'Y'
415 AND R1.business_group_id = I.business_group_id
416 AND R1.resource_list_id = M.resource_list_id
417 and m.resource_class_code = 'FINANCIAL_ELEMENTS';
418
419
420
421 -- ENd: 09-JUN-04, FP.M Resource List Data Model Changes -------------------------------------
422
423
424
425 CURSOR proj_dates IS
426 SELECT start_date, completion_date
427 FROM pa_projects_all
428 WHERE project_id = p_project_id;
429 /* Added for fp */
430 CURSOR funding_amount is
431 SELECT task_id, sum(nvl(projfunc_allocated_amount,0)) pf_fund_amt,
432 sum(nvl(project_allocated_amount,0)) proj_fund_amt
433 FROM pa_project_fundings
434 WHERE project_id = p_project_id
435 AND ((budget_type_code IN ('DRAFT', 'BASELINE')
436 AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N')
437 OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y'
438 AND (((budget_type_code ='BASELINE')
439 OR (budget_type_code ='DRAFT'
440 AND funding_category= 'REVALUATION')))))
441 AND nvl(PA_Funding_Core.G_FUND_BASELINE_FLAG,'N') ='N'
442 -- FP_M changes: Added UNION clause for Change Management enhancements
443 -- This cursor is modified to fetch only from the selected
444 -- funding lines that needs to be baselined ONLY when
445 -- a global value PA_Funding_Core.G_FUND_BASELINE_FLAG is enabled
446 group by task_id
447 UNION
448 SELECT task_id,
449 sum(nvl(projfunc_allocated_amount,0)) pf_fund_amt,
450 sum(nvl(project_allocated_amount,0)) proj_fund_amt
451 FROM pa_project_fundings
452 WHERE project_id = p_project_id
453 AND PA_Funding_Core.G_FUND_BASELINE_FLAG = 'Y'
454 AND (NVL(Submit_Baseline_Flag,'N') = 'Y' OR
455 budget_type_code = 'BASELINE' )
456 group by task_id;
457
458 res_info_rec res_info%ROWTYPE;
459 proj_dates_rec proj_dates%ROWTYPE;
460
461 l_budget_version_id NUMBER;
462 l_err_code NUMBER;
463 l_err_stage VARCHAR2(120);
464 l_status VARCHAR2(120);
465 l_msg_count NUMBER;
469 /* Added for FP */
466 l_err_stack VARCHAR2(250);
467
468 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
470 l_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
471 l_msg_data VARCHAR2(2000);
472 l_funding_bl_tab pa_fp_auto_baseline_pkg.funding_bl_tab;
473 i NUMBER := 0;
474 l_funding_level VARCHAR2(1);
475 /* FP.K.B3 - Funding level to be passed to FPautobseline api. This variable is not used
476 l_fp_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
477 */
478 l_start_date DATE;
479 l_end_date DATE;
480 l_task_id NUMBER;
481 l_project_id NUMBER;
482
483 BEGIN
484
485 savepoint temp_pt;
486 x_err_code := 0;
487
488 IF p_pa_debug_mode = 'Y' THEN
489 pa_debug.set_err_stack('PA_BASELINE_FUNDING_PKG.CREATE_BUDGET_BASELINE');
490 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
491 END IF;
492
493 OPEN res_info;
494 FETCH res_info INTO res_info_rec;
495 CLOSE res_info;
496
497
498 OPEN proj_dates;
499 FETCH proj_dates INTO proj_dates_rec;
500 CLOSE proj_dates;
501
502 --dbms_output.put_line ('start date - ' || proj_dates_rec.start_date);
503 --dbms_output.put_line ('end date - ' || proj_dates_rec.completion_date);
504
505 --dbms_output.put_line ('reslisid - ' || res_info_rec.resource_list_id);
506 --dbms_output.put_line ('resmemid - ' || res_info_rec.resource_list_member_id);
507
508 /* Fix for bug#4000821 code moved from the form to package as we can't release
509 the exlicit lock in form by rollback if the baseline fails due to validation
510 as done in the bug 3739353 starts here */
511 BEGIN
512 SELECT project_id
513 INTO l_project_id
514 FROM pa_projects_all
515 WHERE project_id = p_project_id
516 FOR UPDATE NOWAIT;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 x_err_code := 20;
521 x_status := 'PA_PROJECT_LOCK_TRY_LATER';
522 PA_UTILS.Add_Message
523 ( p_app_short_name => 'PA'
524 , p_msg_name => x_status
525 );
526 END;
527
528 /* Fix for bug#4000821 ends here */
529
530
531 IF proj_dates_rec.completion_date is null then
532 x_err_code := 30;
533 x_status := 'PA_BU_NO_PROJ_END_DATE';
534 PA_UTILS.Add_Message
535 ( p_app_short_name => 'PA'
536 , p_msg_name => x_status
537 );
538
539 --dbms_output.put_line ('stat 1 - ' || x_status);
540 END IF;
541
542 IF x_err_code = 0 then
543 /* Added for fp - start*/
544 pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
545 p_project_id =>p_project_id,
546 x_plan_type_id => l_plan_type_id,
547 x_return_status => l_return_status,
548 x_msg_count => l_msg_count,
549 x_msg_data => l_msg_data
550 );
551
552 IF l_plan_type_id is NULL THEN
553 IF P_PA_DEBUG_MODE = 'Y' THEN
554 pa_debug.g_err_stage:= 'No Plan type ID';
555 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
556 END IF;
557
558 /* Added for fp - end*/
559 --dbms_output.put_line ('calling create_draft ' );
560 create_draft (
561 p_project_id => p_project_id,
562 p_start_date => proj_dates_rec.start_date,
563 p_end_date => proj_dates_rec.completion_date,
564 p_resource_list_id => res_info_rec.resource_list_id,
565 x_budget_version_id => l_budget_version_id,
566 x_err_code => l_err_code,
567 x_status => l_status );
568
569 x_err_code := l_err_code;
570 x_status := l_status;
571 --dbms_output.put_line ('after calling create_draft -' || x_err_code);
572 IF P_PA_DEBUG_MODE = 'Y' THEN
573 pa_debug.g_err_stage:= 'After create_draft, budget version id is - '||l_budget_version_id;
574 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
575 pa_debug.g_err_stage:= 'error code - '||l_err_code;
576 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
577 pa_debug.g_err_stage:= 'error status - '||l_status;
578 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
579 END IF;
580
581 /* END IF; */
582
583 IF x_err_code = 0 then
584
585 --dbms_output.put_line ('calling create_line ' );
586 create_line (
587 p_project_id => p_project_id,
588 p_start_date => proj_dates_rec.start_date,
592 x_err_code => l_err_code,
589 p_end_date => proj_dates_rec.completion_date,
590 p_resource_list_member_id => res_info_rec.resource_list_member_id,
591 p_budget_version_id => l_budget_version_id,
593 x_status => l_status );
594
595 x_err_code := l_err_code;
596 x_status := l_status;
597
598 IF P_PA_DEBUG_MODE = 'Y' THEN
599 pa_debug.g_err_stage:= 'After create_line';
600 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
601 pa_debug.g_err_stage:= 'error code - '||l_err_code;
602 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
603 pa_debug.g_err_stage:= 'error status - '||l_status;
604 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
605 END IF;
606 /*
607 ELSE
608 x_status := l_status;
609 */
610 --dbms_output.put_line ('err 2 - ' || x_err_code);
611 --dbms_output.put_line ('stat 2 - ' || x_status);
612 IF P_PA_DEBUG_MODE = 'Y' THEN
613 pa_debug.g_err_stage:= 'After create_line - 1';
614 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
615 pa_debug.g_err_stage:= 'error code - '||l_err_code;
616 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
617 pa_debug.g_err_stage:= 'error status - '||l_status;
618 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
619 END IF;
620
621 END IF;
622 /* Added for fp */
623 ELSE /* plan type is null */
624
625 pa_billing_core.check_funding_level (
626 x_project_id => p_project_id,
627 x_funding_level => l_funding_level,
628 x_err_code => l_err_code,
629 x_err_stage => l_err_stage,
630 x_err_stack => l_err_stack);
631
632 IF l_err_code <> 0 then
633 x_err_code := l_err_code;
634 x_status := l_err_stage;
635 END IF;
636
637 IF x_err_code = 0 THEN
638
639 /* FP.K.B3 - PA_FP_AUTO_BASELINE_PKG.CREATE_BASELINED_VERSION - Expects funding level only
640 IF l_funding_level = 'P' THEN
641 l_fp_level_code := 'P';
642 ELSIF l_funding_level = 'T' THEN
643 l_fp_level_code := 'T';
644 END IF;
645 */
646 FOR fp_rec IN funding_amount
647 LOOP
648
649 l_start_date := NULL;
650 l_end_date := NULL;
651
652 IF fp_rec.task_id is not null
653 THEN
654 select start_date, completion_date
655 into l_start_date, l_end_date
656 from pa_tasks
657 where task_id = fp_rec.task_id
658 and project_id = p_project_id;
659
660 if l_start_date is null then
661 l_start_date := proj_dates_rec.start_date;
662 end if;
663
664 if l_end_date is null then
665 l_end_date := proj_dates_rec.completion_date;
666 end if;
667
668 l_task_id := fp_rec.task_id;
669
670 ELSE
671
672 l_start_date := proj_dates_rec.start_date;
673 l_end_date := proj_dates_rec.completion_date;
674 l_task_id := 0;
675
676 END IF;
677
678 i := i + 1;
679
680 l_funding_bl_tab(i).task_id := l_task_id;
681 l_funding_bl_tab(i).description := 'Default Created by Projects';
682 l_funding_bl_tab(i).start_date := l_start_date;
683 l_funding_bl_tab(i).end_date := l_end_date;
684 l_funding_bl_tab(i).projfunc_revenue := fp_rec.pf_fund_amt;
685 l_funding_bl_tab(i).project_revenue := fp_rec.proj_fund_amt;
686
687 END LOOP;
688 /* FP.K.B3 - The name of the procedure is CREATE_BASELINED_VERSION
689 PA_FP_AUTO_BASELINE_PKG.CREATE_AUTO_BASELINE_VERSION (
690 */
691 PA_FP_AUTO_BASELINE_PKG.CREATE_BASELINED_VERSION (
692 p_project_id => p_project_id,
693 p_fin_plan_type_id => l_plan_type_id,
694 /* p_funding_level_code => l_fp_level_code, FP.K.B3 - Parameter name changed */
695 p_funding_level_code => l_funding_level, /* FP.K.B3 - Funding level to be passed */
696 p_version_name => 'Revenue Budget 1',
697 p_description => 'Default Created by Projects',
698 p_funding_bl_tab => l_funding_bl_tab,
699 x_budget_version_id => l_budget_version_id,
700 x_return_status => l_return_status,
701 x_msg_count => l_msg_count,
702 x_msg_data => l_msg_data );
703
704 END IF; /* x_err_code */
705
706 END IF; /* plan type is null */
707
708 END IF; /* x_err_code 1 */
709 /* Added for fp - end*/
710
711 IF P_PA_DEBUG_MODE = 'Y' THEN
712 pa_debug.g_err_stage:= 'After the Fin Plan Type check';
713 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
717 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
714 pa_debug.g_err_stage:= 'error code is - '||x_err_code;
715 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
716 pa_debug.g_err_stage:= 'return status is -'||l_return_status;
718 END IF;
719
720 IF x_err_code = 0 and l_return_status = FND_API.G_RET_STS_SUCCESS then
721 IF l_plan_type_id is NULL THEN /* For bug 4198840*/
722 --dbms_output.put_line ('calling summerize ' );
723 IF P_PA_DEBUG_MODE = 'Y' THEN
724 pa_debug.g_err_stage:= 'Calling budget_utils summarize_project_totals';
725 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
726 END IF;
727
728 pa_budget_utils.summerize_project_totals (
729 x_budget_version_id => l_budget_version_id,
730 x_err_code => l_err_code,
731 x_err_stage => l_err_stage,
732 x_err_stack => l_err_stack);
733
734 IF P_PA_DEBUG_MODE = 'Y' THEN
735 pa_debug.g_err_stage:= 'After summerize, error code - '||l_err_code;
736 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
737 pa_debug.g_err_stage:= 'After summerize, error stage - '||l_err_stage;
738 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
739 END IF;
740 x_err_code := l_err_code;
741 x_status := l_err_stage;
742 /*
743 ELSE
744 x_status := l_status;
745 */
746 IF P_PA_DEBUG_MODE = 'Y' THEN
747 pa_debug.g_err_stage:= 'Error status - '||l_status;
748 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
749 END IF;
750 --dbms_output.put_line ('err 3 - ' || x_err_code);
751 --dbms_output.put_line ('stat 3 - ' || x_status);
752 END IF; --For bug 4198840
753 END IF;
754
755
756 IF x_err_code = 0 and l_plan_type_id IS NULL then
757 --dbms_output.put_line ('calling baseline ' );
758 IF P_PA_DEBUG_MODE = 'Y' THEN
759 pa_debug.g_err_stage:= 'Calling budget_core.baseline';
760 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
761 END IF;
762 pa_budget_core.baseline(
763 x_draft_version_id => l_budget_version_id,
764 x_mark_as_original => 'Y',
765 x_verify_budget_rules => 'Y',
766 x_err_code => l_err_code,
767 x_err_stage => l_err_stage,
768 x_err_stack => l_err_stack);
769
770 x_err_code := l_err_code;
771 x_status := l_err_stage;
772 IF P_PA_DEBUG_MODE = 'Y' THEN
773 pa_debug.g_err_stage:= 'After baseline, error code - '||l_err_code;
774 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
775 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
776 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
777 END IF;
778 /*
779
780 ELSE
781 IF P_PA_DEBUG_MODE = 'Y' THEN
782 pa_debug.g_err_stage:= 'error stage - '||l_err_stage;
783 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
784 END IF;
785 x_status := l_err_stage;
786 */
787 --dbms_output.put_line ('stat 4 - ' || x_status);
788
789 END IF;
790
791 IF P_PA_DEBUG_MODE = 'Y' THEN
792 pa_debug.g_err_stage:= 'x_err_code - '||x_err_code;
793 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
794 pa_debug.g_err_stage:= 'return status - '||l_return_status;
795 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
796 END IF;
797 if x_err_code <> 0 or l_return_status <> FND_API.G_RET_STS_SUCCESS then
798 rollback to temp_pt;
799 IF P_PA_DEBUG_MODE = 'Y' THEN
800 pa_debug.g_err_stage:= 'Rolling back';
801 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
802 END IF;
803 --dbms_output.put_line ('stat 5 - ' || x_status);
804 end if;
805 pa_debug.reset_err_stack;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809
810 IF P_PA_DEBUG_MODE = 'Y' THEN
811 pa_debug.g_err_stage:= 'In exception of create_budget_baseline';
812 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
813 END IF;
814 x_err_code := SQLCODE;
815 x_status := substr(SQLERRM,1,50);
816 IF P_PA_DEBUG_MODE = 'Y' THEN
820 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
817 pa_debug.g_err_stage:= 'error code - '||x_err_code;
818 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
819 pa_debug.g_err_stage:= 'status - '||x_status;
821 END IF;
822
823 END create_budget_baseline;
824
825 -- FP_M changes:
826 -- Following APIs Proj_Agreement_Baseline and Change_Management_Baseline
827 -- are created from FP_M onwards
828
829 -- This API is for baselining only required Project's agreement's
830 -- funding lines
831 Procedure Proj_Agreement_Baseline (
832 P_Project_ID IN NUMBER,
833 P_Agreement_ID IN NUMBER,
834 X_Err_Code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
835 X_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
836 ) IS
837 BEGIN
838
839 -- Set the global value
840 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'Y';
841
842 -- First update the submit_Baseline_Flag as 'Y' and then proceed for
843 -- further baselining of the funding lines
844 Update PA_Project_Fundings
845 SET Submit_Baseline_Flag = 'Y'
846 Where Project_ID = P_Project_ID
847 AND Agreement_ID = P_Agreement_ID
848 AND CI_ID is null
849 AND Budget_Type_Code = 'DRAFT'
850 AND NVL(Submit_Baseline_Flag,'N') <> 'Y';
851
852 -- Now call the regular baselining API Create_Budget_Baseline
853 create_budget_baseline (
854 p_project_id => P_Project_ID,
855 x_err_code => X_Err_Code,
856 x_status => X_Status
857 );
858
859 -- Unset the global value back to 'N'
860 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'N';
861
862 END Proj_Agreement_Baseline;
863
864 -- FP_M changes:
865 -- This API is for baselining only required Project's agreement's
866 -- funding lines that are created thru change order management page
867 Procedure Change_Management_Baseline (
868 P_Project_ID IN NUMBER,
869 P_CI_ID_Tab IN PA_PLSQL_DATATYPES.IdTabTyp,
870 X_Err_Code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
871 X_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
872 ) IS
873 BEGIN
874
875 -- Set the global value
876 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'Y';
877
878 -- First update the submit_Baseline_Flag as 'Y' and then proceed for
879 -- further baselining of the funding lines
880 FORALL ci_rec in P_CI_ID_Tab.FIRST..P_CI_ID_Tab.LAST
881 Update PA_Project_Fundings
882 SET Submit_Baseline_Flag = 'Y'
883 Where Project_ID = P_Project_ID
884 AND CI_ID = P_CI_ID_Tab(ci_rec)
885 AND Budget_Type_Code = 'DRAFT'
886 AND NVL(Submit_Baseline_Flag,'N') <> 'Y';
887
888 -- Now call the regular baselining API Create_Budget_Baseline
889 create_budget_baseline (
890 p_project_id => P_Project_ID,
891 x_err_code => X_Err_Code,
892 x_status => X_Status
893 );
894
895 -- Unset the global value back to 'N'
896 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'N';
897
898 END Change_Management_Baseline;
899
900 END PA_BASELINE_FUNDING_PKG;