1 PACKAGE BODY PA_BUDGETARY_CONTROLS_PKG AS
2 -- $Header: PAXBCCRB.pls 120.2 2006/04/18 05:22:08 cmishra noship $
3 PROCEDURE insert_rows
4 (x_project_id IN PA_PROJECTS_ALL.PROJECT_ID%type
5 ,x_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
6 ,x_funds_control_level_code IN PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type
7 ,x_top_task_id IN PA_TASKS.TASK_ID%type
8 ,x_task_id IN PA_TASKS.TASK_ID%type
9 ,x_parent_member_id IN PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%type
10 ,x_resource_list_member_id IN PA_RESOURCE_LIST_MEMBERS.resource_list_member_id%type
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
14
15 l_return_status VARCHAR2(10);
16 l_msg_count NUMBER(15);
17 l_msg_data VARCHAR2(2000);
18
19 -- Bug 5162775 : This procedure is made autonomous.
20 PRAGMA AUTONOMOUS_TRANSACTION;
21
22 --===================================================================================
23 -- Inserts Records into PA_BUDGETARY_CONTROLS
24 -- Called from other routines in this package for the following ...
25 -- records inserted for Project, Tasks, Resource Groups and Resources
26 --===================================================================================
27
28 begin
29
30 l_return_status := FND_API.G_RET_STS_SUCCESS;
31
32
33
34 insert into PA_BUDGETARY_CONTROLS ( BUDGETARY_CONTROLS_ID
35 ,PROJECT_ID
36 ,FUNDS_CONTROL_LEVEL_CODE
37 ,LAST_UPDATE_DATE
38 ,LAST_UPDATED_BY
39 ,CREATED_BY
40 ,CREATION_DATE
41 ,LAST_UPDATE_LOGIN
42 ,TOP_TASK_ID
43 ,TASK_ID
44 ,PARENT_MEMBER_ID
45 ,RESOURCE_LIST_MEMBER_ID
46 ,BUDGET_TYPE_CODE
47 )
48 values (
49 PA_BUDGETARY_CONTROLS_s.nextval
50 ,x_project_id
51 ,x_funds_control_level_code
52 ,SYSDATE
53 ,FND_GLOBAL.USER_ID
54 ,FND_GLOBAL.USER_ID
55 ,SYSDATE
56 ,FND_GLOBAL.LOGIN_ID
57 ,x_top_task_id
58 ,x_task_id
59 ,x_parent_member_id
60 ,x_resource_list_member_id
61 ,x_budget_type_code
62 );
63
64
65 --Output Parameters are set before passing the values back
66
67 x_msg_count := l_msg_count ;
68 x_msg_data := l_msg_data ;
69 x_return_status := l_return_status;
70
71 COMMIT;
72
73
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 x_msg_count := 1;
78 x_msg_data := SUBSTR(SQLERRM, 1, 240);
79 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
80
81 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
82 , p_procedure_name => 'insert_rows');
83
84 END;
85
86 ------------------------------------------------------------------------------------
87 /* Bug 5162775 : The procedure del_bc_rec_for_reset_auto is created to delete BC records
88 during budgetary control reset. */
89
90 PROCEDURE del_bc_rec_for_reset_auto
91 (p_project_id IN NUMBER
92 ,p_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type )
93 IS
94 PRAGMA AUTONOMOUS_TRANSACTION;
95 BEGIN
96
97 delete from pa_budgetary_controls
98 where project_id = p_project_id
99 and budget_type_code = p_budget_type_code;
100
101 COMMIT;
102 EXCEPTION
103 WHEN OTHERS THEN
104 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
105 , p_procedure_name => 'del_bc_rec_for_reset_auto');
106 END del_bc_rec_for_reset_auto;
107
108 -----------------------------------------------------------------------------------
109 /* Bug 5162775 : The procedure del_bc_rec_for_task_auto is created to delete
110 all the dangling records from budgetary controls. */
111
112 PROCEDURE del_bc_rec_for_task_auto
113 (p_project_id IN NUMBER
114 ,p_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
115 ,p_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type)
116 IS
117 PRAGMA AUTONOMOUS_TRANSACTION;
118 BEGIN
119
120 DELETE from pa_budgetary_controls bc
121 WHERE EXISTS
122 ( SELECT pt2.task_id
123 FROM pa_tasks pt2
124 WHERE bc.task_id=pt2.parent_task_id
125 AND ((p_entry_level_code = 'L') OR
126 (p_entry_level_code = 'M' AND exists (select 1 from pa_tasks pt1
127 where bc.task_id = pt1.task_id
128 and pt1.parent_task_id IS NOT NULL))))
129 AND bc.project_id = p_project_id
130 AND bc.budget_type_code = p_budget_type_code;
131
132 COMMIT;
133 EXCEPTION
134 WHEN OTHERS THEN
135 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
136 , p_procedure_name => 'del_bc_rec_for_task_auto');
137 END del_bc_rec_for_task_auto;
138
139 -----------------------------------------------------------------------------------
140
141
142 PROCEDURE create_bc_levels
143 (x_project_id IN PA_PROJECTS_ALL.PROJECT_ID%type
144 ,x_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
145 ,x_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type
146 ,x_resource_list_id IN PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_ID%type
147 ,x_group_resource_type_id IN PA_RESOURCE_LISTS_ALL_BG.GROUP_RESOURCE_TYPE_ID%type
148 ,x_calling_mode IN VARCHAR2
149 ,x_return_status OUT NOCOPY VARCHAR2
150 ,x_msg_count OUT NOCOPY NUMBER
151 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
152
153 v_funds_control_level_project PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type;
154 v_funds_control_level_task PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type;
155 v_funds_control_level_res_grp PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type;
156 v_funds_control_level_res PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type;
157
158 l_return_status VARCHAR2(10);
159 l_msg_count NUMBER(15);
160 l_msg_data VARCHAR2(2000);
161
162 l_debug_mode varchar2(1) := 'N';
163 l_top_task_id NUMBER(15);
164
165 /* Bug 4551528 :
166 cursor c_tasks_no_bc fetches all the tasks for which there exists no records in pa_budgetary_controls.
167 cursor c_res_resgrp_no_bc fetches all the resources and resource groups for which there exists no records in pa_budgetary_controls.
168 cursor c_res_resgrp_all fetches all the resources and resource groups for a particular resource_list_id.
169 cursor c_tasks_for_new_resource fetches all the tasks for which there exists records in pa_budgetary_controls. */
170
171 cursor c_tasks_no_bc is
172 SELECT
173 pt1.task_id,
174 pt1.top_task_id
175 FROM pa_tasks pt1
176 WHERE pt1.project_id = x_project_id
177 AND
178 ( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
179 OR
180 (x_entry_level_code IN ('L','M')
181 AND NOT EXISTS
182 ( SELECT
183 pt2.task_id
184 FROM pa_tasks pt2
185 WHERE pt1.task_id=pt2.parent_task_id)
186 )
187 )
188 AND not exists
189 ( SELECT 1
190 FROM pa_budgetary_controls bc
191 WHERE bc.project_id = x_project_id
192 AND bc.budget_type_code = x_budget_type_code
193 AND bc.task_id = pt1.task_id
194 );
195
196 cursor c_res_resgrp_no_bc IS
197 SELECT br.resource_list_member_id,
198 DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
199 ,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
200 DECODE(br.parent_member_id,
201 NULL,decode(x_group_resource_type_id,
202 0,decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res),
203 decode(v_funds_control_level_res_grp,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res_grp)),
204 decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res)
205 ) funds_control_level
206 FROM PA_RESOURCE_LIST_MEMBERS br
207 WHERE br.ENABLED_FLAG = 'Y'
208 AND br.RESOURCE_LIST_ID = x_resource_list_id
209 AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
210 and nvl(br.migration_code, 'M') = 'M'
211 and not exists (select 1 from pa_budgetary_controls bc
212 where bc.project_id = x_project_id
213 and bc.budget_type_code = x_budget_type_code
214 and bc.resource_list_member_id = br.resource_list_member_id);
215
216
217 cursor c_res_resgrp_all IS
218 SELECT br.resource_list_member_id,
219 DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
220 ,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
221 DECODE(br.parent_member_id,
222 NULL,decode(x_group_resource_type_id,
223 0,decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res),
224 decode(v_funds_control_level_res_grp,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res_grp)),
225 decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res)
226 ) funds_control_level
227 FROM PA_RESOURCE_LIST_MEMBERS br
228 WHERE br.ENABLED_FLAG = 'Y'
229 AND br.RESOURCE_LIST_ID = x_resource_list_id
230 AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
231 and nvl(br.migration_code, 'M') = 'M';
232
233
234 cursor c_tasks_for_new_resource is
235 SELECT
236 pt1.task_id,
237 pt1.top_task_id
238 FROM pa_tasks pt1
239 WHERE pt1.project_id = x_project_id
240 AND
241 ( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
242 OR
243 (x_entry_level_code IN ('L','M')
244 AND NOT EXISTS
245 ( SELECT
246 pt2.task_id
247 FROM pa_tasks pt2
248 WHERE pt1.task_id=pt2.parent_task_id)
249 )
250 )
251 AND exists
252 ( SELECT 1
253 FROM pa_budgetary_controls bc
254 WHERE bc.project_id = x_project_id
255 AND bc.budget_type_code = x_budget_type_code
256 AND bc.task_id = pt1.task_id
257 );
258
259 --===================================================================================
260 /*
261 Bug 4551528 :
262 Note : x_calling_mode is
263 'BASELINE' during first time baselining,
264 'R' during budgetary control reset
265 or during rebaselining if the entry_level_code of the budget is changed,
266 'REBASELINE' during all other scenarios.
267
268 Procedure Creates Budgetary Controls for
269 Project, Task, Resource Group and resources.
270
271 1. Derive the Default control levels from PA_BUDGETARY_CONTROL_OPTIONS.
272 2. Create Budgetary control for the Project only when x_calling_mode is 'BASELINE' or 'R'.
273 2.1 if the Project Budget is linked to external budget, the control
274 level should always be set to Absolute 'B'
275 3. If entry_level_code = 'P' Project then
276 Create budgetary control records for all resources or resource groups for which currently there exists no budgetary control records.
277 Else (i.e the entry_level_code <> 'P')
278 If calling mode is 'REBASELINE' then
279 For all newly added resources or resource groups Loop
280 For all the tasks existing in Budgetary controls Loop
281 Create budgetary control records for the resource and the resource group levels.
282 End Loop
283 End Loop
284 End if --- calling mode is 'REBASELINE'
285 Call del_bc_rec_for_task_auto to delete all the dangling records from budgetary controls in autonomous mode.
286 i.e if entry_level_code = 'L' , delete all the records where the task has some child tasks.
287 if entry_level_code = 'M' , delete all the records whose task is neither the top nor the lowest task.
288 For all newly added tasks Loop
289 Create budgetary control records for the task level.
290 For all the resources and resource groups Loop
291 Create budgetary control records for the resource and resource group levels.
292 End Loop
293 End Loop
294 End if
295
296
297 */
298 --===================================================================================
299
300
301 BEGIN
302
303
304 PA_DEBUG.init_err_stack('PA_BUDGETARY_CONTROLS_PKG.CREATE_BC_LEVELS');
305
306 l_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 --PLSQL Message stack is initialized
309
310 FND_MSG_PUB.initialize;
311
312
313 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
314 l_debug_mode := NVL(l_debug_mode, 'N');
315
316 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
317
318
319 PA_DEBUG.g_err_stage := 'PA_BC_Log: Create Budgetary Controls - start';
320
321 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
322
323
324 -- write log messages into FND_LOG_MESSAGES for debugging purposes
325
326 PA_DEBUG.write_log (x_module => 'PA_BUDGETARY_CONTROLS_PKG.create_bc_levels start'
327 ,x_msg => 'Creating Default Budgetary Control Levels'
328 ,x_log_level => 5);
329
330
331 PA_FCK_UTIL.debug_msg('PA_BC_Log: Create Budgetary Controls - start');
332
333
334
335
336 --Select Funds control level for the Project,Tasks, Resource groups and resources.
337
338
339 select
340 FUND_CONTROL_LEVEL_PROJECT,
341 FUND_CONTROL_LEVEL_TASK,
342 FUND_CONTROL_LEVEL_RES_GRP,
343 FUND_CONTROL_LEVEL_RES
344 into
345 v_funds_control_level_project,
346 v_funds_control_level_task,
347 v_funds_control_level_res_grp,
348 v_funds_control_level_res
349 from
350 pa_budgetary_control_options
351 where
352 project_id = x_project_id and budget_type_code = x_budget_type_code;
353
354 PA_FCK_UTIL.debug_msg('PA_BC_Log: Control levels derived for ' || x_project_id);
355
356 PA_FCK_UTIL.debug_msg('PA_BC_Log: Derived Default Budgetary Control levels from PA_BUDGETARY_CONTROL_OPTIONS');
357
358 PA_DEBUG.g_err_stage := 'PA_BC_Log: Derive default control levels';
359 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
360
361
362 If (x_calling_mode = 'BASELINE') or (x_calling_mode = 'R') then
363
364 -- Creates BC Record for the Project
365 -- ?? Do we check for the link to external budget here?
366 -- If the Project budget is linked to external budget, then
367 -- Funds control level for the project should be Absolute.
368
369 PA_FCK_UTIL.debug_msg('PA_BC_Log: Call INSERT_ROWS -- Create BC for PROJECT ');
370
371 PA_DEBUG.g_err_stage := 'PA_BC_Log:PA_BC_Log: Call INSERT_ROWS -- Create BC for PROJECT ';
372 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
373
374
375 insert_rows (x_project_id
376 ,x_budget_type_code
377 ,v_funds_control_level_project
378 ,null
379 ,null
380 ,null
381 ,null
382 ,l_return_status
383 ,l_msg_count
384 ,l_msg_data
385 );
386
387
388 PA_FCK_UTIL.debug_msg('PA_BC_Log: Created BC for PROJECT Level');
389
390 PA_DEBUG.g_err_stage := 'PA_BC_Log: Created BC for PROJECT Level ';
391 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
392
393 end if;
394
395 IF x_entry_level_code = 'P' then
396
397 FOR rec_res_grps IN c_res_resgrp_no_bc LOOP
398
399 insert_rows( x_project_id
400 , x_budget_type_code
401 , rec_res_grps.funds_control_level
402 , 0
403 , 0
404 , rec_res_grps.parent_member_id
405 , rec_res_grps.resource_list_member_id
406 ,l_return_status
407 ,l_msg_count
408 ,l_msg_data
409 );
410
411 end loop;
412
413 ELSE
414
415 If (x_calling_mode = 'REBASELINE') then -- first add if any new resources added to existing tasks
416
417 FOR rec_res_grps IN c_res_resgrp_no_bc LOOP
418
419 for task_rec in c_tasks_for_new_resource LOOP
420
421 if (x_entry_level_code = 'T') or (x_entry_level_code = 'M') then
422 l_top_task_id := task_rec.task_id;
423 elsif x_entry_level_code = 'L' THEN
424 l_top_task_id :=task_rec.top_task_id;
425 END IF;
426
427 insert_rows( x_project_id
428 , x_budget_type_code
429 , rec_res_grps.funds_control_level
430 , l_top_task_id
431 , task_rec.task_id
432 , rec_res_grps.parent_member_id
433 , rec_res_grps.resource_list_member_id
434 ,l_return_status
435 ,l_msg_count
436 ,l_msg_data
437 );
438
439 end loop;
440 end loop;
441
442 end if;
443 -- Bug 5162775 : Delete all the dangling records from budgetary controls in autonomous mode.
444 del_bc_rec_for_task_auto (p_project_id => x_project_id,
445 p_budget_type_code => x_budget_type_code,
446 p_entry_level_code => x_entry_level_code);
447
448 -- This gets fired for both first time baseling and for any new tasks
449
450 for task_rec in c_tasks_no_bc
451 LOOP
452
453 if (x_entry_level_code = 'T') or (x_entry_level_code = 'L') then
454 l_top_task_id := NULL;
455 elsif x_entry_level_code = 'M' THEN
456 l_top_task_id := task_rec.task_id;
457 END IF;
458
459 insert_rows(x_project_id
460 ,x_budget_type_code
461 ,v_funds_control_level_task
462 ,l_top_task_id
463 ,task_rec.task_id
464 ,null
465 ,null
466 ,l_return_status
467 ,l_msg_count
468 ,l_msg_data);
469
470
471 -- resources
472
473 if (x_entry_level_code = 'T') or (x_entry_level_code = 'M') then
474 l_top_task_id := task_rec.task_id;
475 elsif x_entry_level_code = 'L' THEN
476 l_top_task_id :=task_rec.top_task_id;
477 END IF;
478
479
480 FOR rec_res_grps IN c_res_resgrp_all
481 LOOP
482
483
484 insert_rows( x_project_id
485 , x_budget_type_code
486 , rec_res_grps.funds_control_level
487 , l_top_task_id
488 , task_rec.task_id
489 , rec_res_grps.parent_member_id
490 , rec_res_grps.resource_list_member_id
491 ,l_return_status
492 ,l_msg_count
493 ,l_msg_data
494 );
495
496 end loop;
497
498
499 END LOOP;
500 end if;
501
502 --Output Parameters are set before passing the values back
503
504 x_msg_count := l_msg_count ;
505 x_msg_data := l_msg_data ;
506 x_return_status := l_return_status;
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 x_msg_count := 1;
511 x_msg_data := SUBSTR(SQLERRM, 1, 240);
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513
514 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
515 , p_procedure_name => 'CREATE_BC_LEVELS');
516
517 end; -- Procedure create_bc_levels
518
519 ------------------------------------------------------------------------------------
520
521 PROCEDURE bud_ctrl_create
522 (x_budget_version_id IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%type
523 ,x_calling_mode IN VARCHAR2
524 ,x_return_status OUT NOCOPY VARCHAR2
525 ,x_msg_count OUT NOCOPY NUMBER
526 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
527
528 l_return_status VARCHAR2(10);
529 l_msg_count NUMBER(15);
530 l_msg_data VARCHAR2(2000);
531 l_debug_mode VARCHAR2(1) := 'N';
532 CREATE_BC_REC_ERROR EXCEPTION;
533 l_budctrl_exists VARCHAR2(1) := 'N';
534 l_calling_mode VARCHAR2(30);
535
536 /* Bug 4551528 :
537 CURSOR c_budctrl_exists checks if there exists any budgetary control records for the project and budget type code.
538 cursor c_prev_entry_level_code is used to get the entry_level_code of the previous budget version. */
539
540 CURSOR c_budctrl_exists ( p_budget_version_id NUMBER) IS
541 SELECT 'Y'
542 FROM DUAL
543 WHERE EXISTS (SELECT 1
544 FROM pa_budgetary_controls bc,
545 PA_BUDGET_VERSIONS pbv
546 WHERE BUDGET_VERSION_ID = p_budget_version_id
547 and bc.project_id = pbv.project_id
548 and bc.budget_type_code = pbv.budget_type_code);
549
550 -- Cursor retrives values for Parameters to be passed while calling create_bc_levels;
551
552 cursor c_bud_ctrl_params(p_budget_version_id number) is
553 select bv.project_id
554 ,bv.budget_type_code
555 ,bv.resource_list_id
556 ,rl.group_resource_type_id
557 ,bem.entry_level_code
558 from PA_BUDGET_VERSIONS bv
559 ,PA_BUDGET_ENTRY_METHODS bem
560 ,PA_RESOURCE_LISTS_ALL_BG rl
561 where bv.budget_version_id = p_budget_version_id
562 and bv.budget_entry_method_code = bem.budget_entry_method_code
563 and bv.resource_list_id = rl.resource_list_id
564 --FP M changes
565 and nvl(rl.migration_code, 'M') = 'M' ;
566
567 bud_ctrl_params_rec c_bud_ctrl_params%ROWTYPE;
568
569 -- Bug 4551528 : This cursor is used to retrieve the entry_level_code for the previous version of the budget.
570 cursor c_prev_entry_level_code(p_budget_version_id number) is
571 select bem.entry_level_code
572 from pa_budget_versions bv,
573 pa_budget_entry_methods bem
574 where bv.budget_version_id = p_budget_version_id
575 and bv. budget_entry_method_code = bem. budget_entry_method_code;
576
577 l_prev_entry_level_code pa_budget_entry_methods.entry_level_code%type;
578
579 --===================================================================================
580 /* Called from Budget Baselining / Tieback API
581 The Budget Version has BUDGET_STATUS_CODE = 'B' if Baseline process was successful
582
583 Bug 4551528 :
584 If first time baselining then
585 l_calling_mode := x_calling_mode;
586 Else
587 If the entry_level_code of the budget has changed then
588 delete all the budgetary control records.
589 l_calling_mode := 'R'
590 Else
591 l_calling_mode := 'REBASELINE'
592 end if
593 End if
594 Then pa_budgetary_controls_pkg.create_bc_levels is called with the appropriate calling mode
595 */
596
597 --====================================================================================
598
599 begin
600
601 l_return_status := FND_API.G_RET_STS_SUCCESS;
602 l_calling_mode := x_calling_mode;
603
604 -- Setting debug variables
605 PA_DEBUG.init_err_stack('PA_BUDGETARY_CONTROLS_PKG.BUD_CTRL_CREATE');
606 FND_MSG_PUB.initialize; --PLSQL Message stack is initialized
607 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
608 l_debug_mode := NVL(l_debug_mode, 'N');
609 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
610
611
612 PA_DEBUG.g_err_stage := 'PA_BC_Log: Baseline Process calls Budg. control creation proc. - start';
613 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
614 -- write log messages into FND_LOG_MESSAGES for debugging purposes
615 PA_DEBUG.write_log (x_module => 'PA_BUDGETARY_CONTROLS_PKG.BUD_CTRL_CREATE start'
616 ,x_msg => 'Creating Default Budgetary Control Levels'
617 ,x_log_level => 5);
618 PA_FCK_UTIL.debug_msg('PA_BC_Log: Call CREATE_BC_LEVELS procedure:- start');
619
620
621 OPEN c_budctrl_exists(x_budget_version_id);
622 FETCH c_budctrl_exists INTO l_budctrl_exists;
623 CLOSE c_budctrl_exists;
624
625 PA_FCK_UTIL.debug_msg('PA_BC_Log:Budgetary control record exists ? '||l_budctrl_exists);
626 PA_FCK_UTIL.debug_msg('PA_BC_Log:Opening c_bud_ctrl_params cursor');
627
628 open c_bud_ctrl_params(x_budget_version_id);
629 fetch c_bud_ctrl_params into bud_ctrl_params_rec;
630
631 IF (c_bud_ctrl_params%FOUND) THEN
632
633 IF l_budctrl_exists <> 'Y' THEN -- First time baselining
634
635 PA_FCK_UTIL.debug_msg('PA_BC_Log:Calling CREATE_BC_LEVELS during first time baselining');
636 PA_DEBUG.g_err_stage := 'PA_BC_Log Calling CREATE_BC_LEVELS during first time baselining';
637 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
638
639 l_calling_mode := x_calling_mode;
640
641 ELSE -- rebaselining
642
643 -- Bug 4551528 : Fetch the entry_level_code for the previous version of the budget.
644 open c_prev_entry_level_code (PA_BUDGET_FUND_PKG.Get_previous_bvid(p_project_id => bud_ctrl_params_rec.project_id,
645 p_budget_type_code => bud_ctrl_params_rec.budget_type_code,
646 p_curr_budget_status_code => 'B'));
647 fetch c_prev_entry_level_code into l_prev_entry_level_code;
648 close c_prev_entry_level_code;
649
650 -- If entry_level_code is changed then we nedd to refresh budgetary control
651 IF nvl(l_prev_entry_level_code, bud_ctrl_params_rec.entry_level_code) <> bud_ctrl_params_rec.entry_level_code THEN
652
653 PA_DEBUG.g_err_stage := 'PA_BC_Log Calling CREATE_BC_LEVELS during re-baselining if the entry level code is changed';
654 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
655 PA_FCK_UTIL.debug_msg('PA_BC_Log: Calling CREATE_BC_LEVELS during re-baselining if the entry level code is changed');
656
657 -- Bug 5162775 : Call del_bc_rec_for_reset_auto to delete BC records in autonomous mode during BC reset.
658 del_bc_rec_for_reset_auto (p_project_id => bud_ctrl_params_rec.project_id,
659 p_budget_type_code => bud_ctrl_params_rec.budget_type_code);
660
661 l_calling_mode := 'R'; --reset mode;
662
663 ELSE
664
665 PA_DEBUG.g_err_stage := 'PA_BC_Log Calling CREATE_BC_LEVELS during re-baselining if the entry level code is not changed';
666 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
667 PA_FCK_UTIL.debug_msg('PA_BC_Log: Calling CREATE_BC_LEVELS during re-baselining if the entry level code is not changed');
668
669 l_calling_mode := 'REBASELINE';
670
671 END IF; -- If entry_level_code is changed then we need to refresh budgetary control
672
673 END IF; --IF l_budctrl_exists <> 'Y' THEN
674
675 pa_budgetary_controls_pkg.create_bc_levels
676 (bud_ctrl_params_rec.project_id
677 ,bud_ctrl_params_rec.budget_type_code
678 ,bud_ctrl_params_rec.entry_level_code
679 ,bud_ctrl_params_rec.resource_list_id
680 ,bud_ctrl_params_rec.group_resource_type_id
681 ,l_calling_mode
682 ,l_return_status
683 ,l_msg_count
684 ,l_msg_data );
685
686 END IF; -- IF (c_bud_ctrl_params%FOUND) THEN
687
688 PA_FCK_UTIL.debug_msg('PA_BC_Log:5');
689 PA_DEBUG.g_err_stage := 'PA_BC_Log Executed BUD_CTRL_CREATE';
690 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
691 PA_FCK_UTIL.debug_msg('PA_BC_Log: Executed BUD_CTRL_CREATE');
692
693 CLOSE c_bud_ctrl_params;
694
695 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
696 RAISE CREATE_BC_REC_ERROR;
697 end if;
698
699 --Output Parameters are set before passing the values back
700
701 x_msg_count := l_msg_count ;
702 x_msg_data := l_msg_data ;
703 x_return_status := l_return_status;
704
705 EXCEPTION
706 WHEN CREATE_BC_REC_ERROR then
707 PA_UTILS.add_message('PA', 'PA_BC_REC_FAIL');
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 x_msg_count := 1;
710 x_msg_data := 'PA_BC_REC_FAIL';
711
712
713 WHEN OTHERS THEN
714 x_msg_count := 1;
715 x_msg_data := SUBSTR(SQLERRM, 1, 240);
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717
718 PA_FCK_UTIL.debug_msg('PA_BC_Log:8' || SQLERRM);
719
720 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
721 , p_procedure_name => 'BUD_CTRL_CREATE');
722
723 END;
724
725 ------------------------------------------------------------------------------------
726
727
728 PROCEDURE budg_control_reset
729 (x_project_id IN PA_PROJECTS_ALL.PROJECT_ID%type
730 ,x_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
731 ,x_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type
732 ,x_resource_list_id IN PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_ID%type
733 ,x_group_resource_type_id IN PA_RESOURCE_LISTS_ALL_BG.GROUP_RESOURCE_TYPE_ID%type
734 ,x_calling_mode IN VARCHAR2
735 ,x_return_status OUT NOCOPY VARCHAR2
736 ,x_msg_count OUT NOCOPY NUMBER
737 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
738
739 l_return_status VARCHAR2(10);
740 l_msg_count NUMBER(15);
741 l_msg_data VARCHAR2(2000);
742 l_debug_mode varchar2(1) := 'N';
743 RESET_BC_ERROR EXCEPTION;
744
745 begin
746
747 PA_DEBUG.init_err_stack('PA_BUDGETARY_CONTROLS_PKG.BUDG_CTRL_RESET');
748
749 l_return_status := FND_API.G_RET_STS_SUCCESS;
750
751 --PLSQL Message stack is initialized
752
753 FND_MSG_PUB.initialize;
754
755
756 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
757 l_debug_mode := NVL(l_debug_mode, 'N');
758
759 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
760
761
762 PA_DEBUG.g_err_stage := 'PA_BC_Log: Called from Form . Budg. control RESET proc. - start';
763 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
764
765
766 -- write log messages into FND_LOG_MESSAGES for debugging purposes
767
768 PA_DEBUG.write_log (x_module => 'PA_BUDGETARY_CONTROLS_PKG.BUDG_CTRL_RESET start'
769 ,x_msg => 'Reset Budgetary Control Levels'
770 ,x_log_level => 5);
771
772
773 PA_FCK_UTIL.debug_msg('PA_BC_Log: Call CREATE_BC_LEVELS procedure:- start');
774
775
776 -- Deletes Budgetary Control records from PA_BUDGETARY_CONTROLS
777
778
779 PA_DEBUG.g_err_stage := 'PA_BC_Log: Called from Form . Budg. control RESET proc. - start';
780 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
781
782
783 delete from pa_budgetary_controls
784 where project_id = x_project_id
785 and budget_type_code = x_budget_type_code;
786
787 PA_FCK_UTIL.debug_msg('PA_BC_Log: delete budgetary Controls');
788
789 PA_DEBUG.g_err_stage := 'PA_BC_Log: Deleted Budgetary Control Records: '||'PROJECT ID = '||x_project_id;
790 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
791
792 pa_budgetary_controls_pkg.create_bc_levels
793 (x_project_id
794 ,x_budget_type_code
795 ,x_entry_level_code
796 ,x_resource_list_id
797 ,x_group_resource_type_id
798 ,'R'
799 ,l_return_status
800 ,l_msg_count
801 ,l_msg_data );
802
803 if l_return_status = FND_API.G_RET_STS_SUCCESS then
804 PA_FCK_UTIL.debug_msg('PA_BC_Log: BC Levels recreated');
805 -- Budgetary Control levels successfully created. Commit can be issued
806 commit;
807 PA_DEBUG.g_err_stage := 'PA_BC_Log: Budgetary Control levels successfully created';
808 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
809 else
810 -- Budgetary Controls were not created. Roll back issued and exception be raised in the Form.
811 PA_FCK_UTIL.debug_msg('PA_BC_Log: rollback');
812 rollback;
813 end if;
814
815
816 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
817 RAISE RESET_BC_ERROR;
818 end if;
819
820 --Output Parameters are set before passing the values back
821
822 x_msg_count := l_msg_count ;
823 x_msg_data := l_msg_data ;
824 x_return_status := l_return_status;
825
826 EXCEPTION
827 WHEN RESET_BC_ERROR then
828 PA_UTILS.add_message('PA', 'PA_BC_RESET_ERROR');
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 x_msg_count := 1;
831 x_msg_data := 'PA_BC_RESET_ERROR';
832
833
834 WHEN OTHERS THEN
835 rollback;
836 x_msg_count := 1;
837 x_msg_data := SUBSTR(SQLERRM, 1, 240);
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839
840 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGETARY_CONTROLS_PKG'
841 , p_procedure_name => 'BUD_CTRL_CREATE');
842
843 END;
844
845
846 -------------------------------------------------------------------------------------
847
848
849 FUNCTION budget_ctrl_exists
850 (x_project_id IN PA_PROJECTS_ALL.PROJECT_ID%type
851 ,x_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
852 )
853 return varchar2 is
854
855
856 l_fck_req_flag VARCHAR2(1);
857 l_bdgt_intg_flag pa_budgetary_control_options.external_budget_code%TYPE ;
858 l_bdgt_ver_id pa_budget_versions.budget_version_id%TYPE ;
859 l_encum_type_id pa_budgetary_control_options.encumbrance_type_id%TYPE ;
860 l_balance_type pa_budgetary_control_options.balance_type%TYPE;
861 l_return_status VARCHAR2(10);
862 l_msg_count NUMBER(15);
863 l_msg_data VARCHAR2(2000);
864 v_bud_ctrl_exist VARCHAR2(1);
865 bc_rec_exists varchar2(1);
866 invalid_args_passed EXCEPTION;
867
868 --============================================================================
869 --Function is called from Budgets form -- before the Menu Item
870 --for BC form is activated.
871
872 --1. Check Budgetary Controls enabled for this project
873 --2. Check Budgetary control records exist for given project_id
874 -- and budget_type_code. At the time of invoking the Budgetary Controls
875 -- form, BC records should have been created by Baselining process.
876 --3. The Function returns 'Y' if
877 -- a) Budgetary Control enabled for the Project and Budget Type AND
878 -- b) the Project budget has been successfully
879 -- initial-baselined and BC records created.
880 -- Function returns 'N' if
881 -- a) Budgetary Control is not enabled for the Project and Budget Type OR
882 -- b) the Project budget has never been baselined.
883
884 --============================================================================
885
886 begin
887
888 if ((x_project_id is null) or (x_budget_type_code is null)) then
889 raise invalid_args_passed;
890 end if;
891
892 /*
893 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
894 (p_project_id => x_project_id
895 ,p_budget_type_code => x_budget_type_code
896 ,p_calling_mode => 'BUDGET'
897 ,x_fck_req_flag => l_fck_req_flag
898 ,x_bdgt_intg_flag => l_bdgt_intg_flag
899 ,x_bdgt_ver_id => l_bdgt_ver_id
900 ,x_encum_type_id => l_encum_type_id
901 ,x_balance_type => l_balance_type
902 ,x_return_status => l_return_status
903 ,x_msg_count => l_msg_count
904 ,x_msg_data => l_msg_data);
905 */
906
907 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
908 (x_project_id
909 ,x_budget_type_code
910 ,'BUDGET'
911 ,l_fck_req_flag
912 ,l_bdgt_intg_flag
913 ,l_bdgt_ver_id
914 ,l_encum_type_id
915 ,l_balance_type
916 ,l_return_status
917 ,l_msg_count
918 ,l_msg_data);
919
920
921 -- check whether budgetary Controls set up for this project
922 if l_fck_req_flag = 'Y' then
923 -- Check whether BC records exist for this project and budget type
924
925 select 'Y' into bc_rec_exists from SYS.DUAL
926 where exists
927 (select' Y'
928 from pa_budgetary_controls
929 where project_id = x_project_id and
930 budget_type_code = x_budget_type_code and
931 rownum=1);
932
933 /* select count(*) into bc_rec_count
934 from pa_budgetary_controls
935 where project_id = x_project_id and
936 budget_type_code = x_budget_type_code;
937 */
938 if bc_rec_exists = 'Y' then
939 v_bud_ctrl_exist := 'Y';
940 else
941 v_bud_ctrl_exist := 'N';
942 end if;
943 else
944 v_bud_ctrl_exist := 'N';
945 end if;
946
947 return v_bud_ctrl_exist;
948
949 EXCEPTION
950
951
952 WHEN INVALID_ARGS_PASSED then
953
954 FND_MESSAGE.SET_NAME('PA','PA_BC_NULL_ARGS_PASSED');
955 APP_EXCEPTION.RAISE_EXCEPTION;
956 fnd_msg_pub.add;
957 RAISE FND_API.G_EXC_ERROR;
958
959 /*
960 PA_UTILS.add_message ( p_app_short_name => 'PA'
961 ,p_msg_name => 'PA_BC_NULL_ARGS_PASSED');
962 APP_EXCEPTION.RAISE_EXCEPTION;
963 */
964
965 WHEN OTHERS THEN
966 RETURN NULL;
967
968 end ;
969
970 ------------------------------------------------------------------------------------
971
972 FUNCTION budg_control_enabled
973 ( x_budget_version_id IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%type)
974 return varchar2 is
975
976 l_project_id PA_PROJECTS_ALL.PROJECT_ID%type;
977 l_budget_type_code PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type;
978 l_return_status VARCHAR2(10);
979 l_msg_count NUMBER(15);
980 l_msg_data VARCHAR2(2000);
981
982 l_bdgt_ver_id pa_budget_versions.budget_version_id%TYPE ;
983 l_encum_type_id pa_budgetary_control_options.encumbrance_type_id%TYPE ;
984 l_balance_type pa_budgetary_control_options.balance_type%TYPE;
985 l_fck_req_flag VARCHAR2(1);
986 l_bdgt_intg_flag VARCHAR2(1);
987 v_bc_enabled VARCHAR2(1);
988 invalid_args_passed EXCEPTION;
989
990 --============================================================================
991 --Called from Budgets Form
992 --Returns 'Y' if Budgetary Controls enabled for given PROJECT_ID and BUDGET_TYPE_CODE
993 -- Derive the PROJECT_ID and BUDGET_TYPE_CODE from PA_BUDGET_VERSIONS
994 -- if Budgetary Controls enabled (Call to PA_BUDGET_FUND API) and BALANCE_TYPE in ('B','E')
995 -- return 'Y'
996 -- else
997 -- return 'N'
998 --
999 --============================================================================
1000
1001 begin
1002 --Derive the PROJECT_ID and BUDGET_TYPE_CODE for the Budget version.
1003
1004
1005 if x_budget_version_id IS NULL then
1006 raise INVALID_ARGS_PASSED;
1007 end if;
1008
1009 select PROJECT_ID,BUDGET_TYPE_CODE
1010 into l_project_id, l_budget_type_code
1011 from PA_BUDGET_VERSIONS
1012 where BUDGET_VERSION_ID = x_budget_version_id;
1013
1014
1015
1016 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
1017 (p_project_id => l_project_id
1018 ,p_budget_type_code => l_budget_type_code
1019 ,p_calling_mode => 'BUDGET'
1020 ,x_fck_req_flag => l_fck_req_flag
1021 ,x_bdgt_intg_flag => l_bdgt_intg_flag
1022 ,x_bdgt_ver_id => l_bdgt_ver_id
1023 ,x_encum_type_id => l_encum_type_id
1024 ,x_balance_type => l_balance_type
1025 ,x_return_status => l_return_status
1026 ,x_msg_count => l_msg_count
1027 ,x_msg_data => l_msg_data);
1028
1029
1030 --Bug 1969577: Removed l_balance_type check since for budgetary control
1031 -- enabled, non integrated budget the balance_type will be null.
1032
1033 if l_fck_req_flag = 'Y'
1034 --and ((l_balance_type = 'E') OR ( l_balance_type = 'B'))
1035 then
1036 v_bc_enabled := 'Y';
1037 else
1038 v_bc_enabled := 'N';
1039 end if;
1040
1041 return v_bc_enabled;
1042
1043 EXCEPTION
1044
1045 WHEN INVALID_ARGS_PASSED then
1046
1047 /* PA_UTILS.add_message ( p_app_short_name => 'PA'
1048 ,p_msg_name => 'PA_BC_NULL_ARGS_PASSED');
1049 */
1050 FND_MESSAGE.SET_NAME('PA','PA_BC_NULL_ARGS_PASSED');
1051 APP_EXCEPTION.RAISE_EXCEPTION;
1052 fnd_msg_pub.add;
1053 RAISE FND_API.G_EXC_ERROR;
1054
1055 -- APP_EXCEPTION.RAISE_EXCEPTION;
1056
1057 WHEN OTHERS THEN
1058 RETURN NULL;
1059
1060 end ; -- end of Function
1061
1062
1063 FUNCTION get_budget_status
1064 (p_budget_version_id IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%type)
1065 return varchar2 is
1066
1067 v_budget_status_code PA_BUDGET_VERSIONS.BUDGET_STATUS_CODE%type;
1068
1069 cursor c_budget_status is
1070 select budget_status_code from PA_BUDGET_VERSIONS where budget_version_id = p_budget_version_id;
1071
1072 begin
1073
1074 if (nvl(G_Budget_Version_ID,-9999) = p_budget_version_id ) then /*4219400*/
1075 return G_Budget_Status_Code; /*4219400*/
1076
1077 else /*4219400*/
1078
1079 if c_budget_status%ISOPEN then
1080 close c_budget_status;
1081 end if;
1082
1083 open c_budget_status;
1084 fetch c_budget_status into v_budget_status_code;
1085 G_Budget_Version_ID := p_budget_version_id; /*4219400*/
1086 G_Budget_Status_Code := v_budget_status_code; /*4219400*/
1087 close c_budget_status;
1088
1089 return v_budget_status_code;
1090
1091 end if; /*4219400*/
1092
1093 END get_budget_status;
1094
1095
1096 end PA_BUDGETARY_CONTROLS_PKG; -- Package
[Home] [Help]