1 PACKAGE BODY GMS_BUDG_CONT_SETUP AS
2 -- $Header: gmsbudcb.pls 120.4 2011/05/20 10:03:26 rrambati ship $
3
4 -- Bug 5162777: To check whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 procedure insert_rec(x_project_id NUMBER
8 ,x_funds_control_code VARCHAR2
9 ,x_award_id NUMBER
10 ,x_task_id NUMBER
11 ,x_parent_member_id NUMBER
12 ,x_resource_list_member_id NUMBER) IS
13
14 -- Bug 5162777 : This procedure is made autonomous.
15 PRAGMA AUTONOMOUS_TRANSACTION;
16
17 Begin
18 insert into gms_budgetary_controls ( BUDGETARY_CONTROLS_ID
19 ,PROJECT_ID
20 ,FUNDS_CONTROL_LEVEL_CODE
21 ,AWARD_ID
22 ,LAST_UPDATE_DATE
23 ,LAST_UPDATED_BY
24 ,CREATED_BY
25 ,CREATION_DATE
26 ,LAST_UPDATE_LOGIN
27 ,TASK_ID
28 ,PARENT_MEMBER_ID
29 ,RESOURCE_LIST_MEMBER_ID
30 )
31 values ( gms_budgetary_controls_s.nextval
32 ,x_project_id
33 ,x_funds_control_code
34 ,x_award_id
35 ,SYSDATE
36 ,FND_GLOBAL.USER_ID
37 ,FND_GLOBAL.USER_ID
38 ,SYSDATE
39 ,FND_GLOBAL.LOGIN_ID
40 ,x_task_id
41 ,x_parent_member_id
42 ,x_resource_list_member_id
43 );
44
45 COMMIT;
46 End;
47
48 -------------------------------------------------------------------------------
49 /* Bug 5162777 : The procedure del_bc_rec_for_reset_auto is created to delete BC records in autonomous mode
50 during budgetary control reset. */
51
52 PROCEDURE del_bc_rec_for_reset_auto
53 (p_project_id IN NUMBER
54 ,p_award_id IN NUMBER )
55 IS
56 PRAGMA AUTONOMOUS_TRANSACTION;
57 BEGIN
58
59 delete from gms_budgetary_controls
60 where project_id = p_project_id
61 and award_id = p_award_id;
62
63 COMMIT;
64 END del_bc_rec_for_reset_auto;
65
69
66 -------------------------------------------------------------------------
67 /* Bug 5162777 : The procedure del_bc_rec_for_task_auto is created to delete
68 all the dangling records from budgetary controls. */
70 PROCEDURE del_bc_rec_for_task_auto
71 (p_project_id IN NUMBER
72 ,p_award_id IN NUMBER
73 ,p_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type)
74 IS
75 PRAGMA AUTONOMOUS_TRANSACTION;
76 BEGIN
77
78 DELETE from gms_budgetary_controls bc
79 WHERE EXISTS
80 ( SELECT pt2.task_id
81 FROM pa_tasks pt2
82 WHERE bc.task_id=pt2.parent_task_id
83 AND ((p_entry_level_code = 'L') OR
84 (p_entry_level_code = 'M' AND exists (select 1 from pa_tasks pt1
85 where bc.task_id = pt1.task_id
86 and pt1.parent_task_id IS NOT NULL))))
87 AND bc.project_id = p_project_id
88 AND bc.award_id = p_award_id;
89
90 COMMIT;
91 END del_bc_rec_for_task_auto;
92
93 -----------------------------------------------------------------------------------
94
95 procedure create_records (x_project_id NUMBER
96 ,x_award_id NUMBER
97 ,x_entry_level_code VARCHAR2
98 ,x_resource_list_Id NUMBER
99 ,x_group_resource_type_id NUMBER
100 ,p_calling_mode IN VARCHAR2 DEFAULT 'BASELINE'
101 ,RETCODE OUT NOCOPY NUMBER
102 ,ERRBUF OUT NOCOPY VARCHAR2) IS
103 x_funds_control_code_awd varchar2(30);
104 x_funds_control_code_tsk varchar2(30);
105 x_funds_control_code_resgrp varchar2(30);
106 x_funds_control_code_res varchar2(30);
107
108 /* Bug 5132850 :
109 cursor c_tasks_no_bc fetches all the tasks for which there exists no records in pa_budgetary_controls.
110 cursor c_res_resgrp_no_bc fetches all the resources and resource groups for which there exists no records in pa_budgetary_controls.
111 cursor c_res_resgrp_all fetches all the resources and resource groups for a particular resource_list_id.
112 cursor c_tasks_for_new_resource fetches all the tasks for which there exists records in pa_budgetary_controls. */
113
114 cursor c_tasks_no_bc is
115 SELECT
116 pt1.task_id,
117 pt1.top_task_id
118 FROM pa_tasks pt1
119 WHERE pt1.project_id = x_project_id
120 AND
121 ( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
122 OR
123 (x_entry_level_code IN ('L','M')
124 AND NOT EXISTS
125 ( SELECT
126 pt2.task_id
127 FROM pa_tasks pt2
128 WHERE pt1.task_id=pt2.parent_task_id)
129 )
130 )
131 AND not exists
132 ( SELECT 1
133 FROM gms_budgetary_controls bc
134 WHERE bc.project_id = x_project_id
135 AND bc.award_id = x_award_id
136 AND bc.task_id = pt1.task_id
137 );
138
139 cursor c_res_resgrp_no_bc IS
140 SELECT br.resource_list_member_id,
141 DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
142 ,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
143 DECODE(br.parent_member_id,
144 NULL,decode(x_group_resource_type_id,
145 0,x_funds_control_code_res,
146 x_funds_control_code_resgrp),
147 x_funds_control_code_res
148 ) funds_control_level
149 FROM PA_RESOURCE_LIST_MEMBERS br
150 WHERE br.ENABLED_FLAG = 'Y'
151 AND br.RESOURCE_LIST_ID = x_resource_list_id
152 AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
153 and nvl(br.migration_code, 'M') = 'M'
154 and not exists (select 1 from gms_budgetary_controls bc
155 where bc.project_id = x_project_id
156 and bc.award_id = x_award_id
157 and bc.resource_list_member_id = br.resource_list_member_id);
158
159 cursor c_res_resgrp_all IS
160 SELECT br.resource_list_member_id,
161 DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
162 ,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
163 DECODE(br.parent_member_id,
164 NULL,decode(x_group_resource_type_id,
165 0,x_funds_control_code_res,
166 x_funds_control_code_resgrp),
167 x_funds_control_code_res
168 ) funds_control_level
169 FROM PA_RESOURCE_LIST_MEMBERS br
170 WHERE br.ENABLED_FLAG = 'Y'
171 AND br.RESOURCE_LIST_ID = x_resource_list_id
172 AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
173 and nvl(br.migration_code, 'M') = 'M';
174
175
176 cursor c_tasks_for_new_resource is
177 SELECT
178 pt1.task_id,
179 pt1.top_task_id
180 FROM pa_tasks pt1
181 WHERE pt1.project_id = x_project_id
182 AND
183 ( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
184 OR
185 (x_entry_level_code IN ('L','M')
186 AND NOT EXISTS
187 ( SELECT
188 pt2.task_id
189 FROM pa_tasks pt2
190 WHERE pt1.task_id=pt2.parent_task_id)
191 )
192 )
193 AND exists
194 ( SELECT 1
195 FROM gms_budgetary_controls bc
196 WHERE bc.project_id = x_project_id
197 and bc.award_id = x_award_id
198 AND bc.task_id = pt1.task_id
199 );
200
201 Begin
202
206 Note : x_calling_mode is
203 --===================================================================================
204 /*
205 Bug 5132850 :
207 'BASELINE' during first time baselining or during budgetary control reset
208 'REBASELINE' during all other scenarios.
209
210 Procedure Creates Budgetary Controls for
211 Award, Task, Resource Group and resources.
212
213 1. Derive the Default control levels from gms_awards.
214 2. Create Budgetary control for the Award only when x_calling_mode is 'BASELINE'.
215 3. If entry_level_code = 'P' Project then
216 Create budgetary control records for all resources or resource groups for which currently there exists no budgetary control records.
217 Else (i.e the entry_level_code <> 'P')
218 If calling mode is 'REBASELINE' then
219 For all newly added resources or resource groups Loop
220 For all the tasks existing in Budgetary controls Loop
221 Create budgetary control records for the resource and the resource group levels.
222 End Loop
223 End Loop
224 End if --- calling mode is 'REBASELINE'
225 Delete all the dangling records from budgetary controls
226 i.e if entry_level_code = 'L' , delete all the records where the task has some child tasks.
227 if entry_level_code = 'M' , delete all the records whose task is neither the top nor the lowest task.
228 For all newly added tasks Loop
229 Create budgetary control records for the task level.
230 For all the resources and resource groups Loop
231 Create budgetary control records for the resource and resource group levels.
232 End Loop
233 End Loop
234 End if
235
236
237 */
238 --===================================================================================
239
240 select fund_control_level_award
241 , fund_control_level_task
242 , fund_control_level_res_grp
243 , fund_control_level_res
244 into x_funds_control_code_awd
245 , x_funds_control_code_tsk
246 , x_funds_control_code_resgrp
247 , x_funds_control_code_res
248 from gms_awards
249 where award_id = x_award_id;
250
251 --creates default budgetary setup only for advisory or absolute controls
252 --if x_funds_control_code_awd <> 'N' then
253 --dbms_output.put_line('The last updated by is >>>>'||FND_GLOBAL.LOGIN_ID);
254
255 If (p_calling_mode = 'BASELINE') then
256 insert_rec(x_project_id, x_funds_control_code_awd, x_award_id, null, null, null);
257 end if;
258
259 IF x_entry_level_code = 'P' then
260
261 FOR rec_res_grps IN c_res_resgrp_no_bc LOOP
262
263 insert_rec( x_project_id
264 , rec_res_grps.funds_control_level
265 , x_award_id
266 , 0
267 , rec_res_grps.parent_member_id
268 , rec_res_grps.resource_list_member_id
269 );
270
271 end loop;
272 ELSE
273
274 If (p_calling_mode = 'REBASELINE') then -- first add if any new resources added to existing tasks
275
276 FOR rec_res_grps IN c_res_resgrp_no_bc LOOP
277
278 for task_rec in c_tasks_for_new_resource LOOP
279
280 insert_rec( x_project_id
281 , rec_res_grps.funds_control_level
282 , x_award_id
283 , task_rec.task_id
284 , rec_res_grps.parent_member_id
285 , rec_res_grps.resource_list_member_id
286 );
287
288 end loop;
289 end loop;
290
291 end if;
292
293 -- Bug 5162777 : Delete all the dangling records from budgetary controls in autonomous mode.
294
295 del_bc_rec_for_task_auto (p_project_id => x_project_id,
296 p_award_id => x_award_id,
297 p_entry_level_code => x_entry_level_code);
298
299 -- This gets fired for both first time baseling and for any new tasks
300
301 for task_rec in c_tasks_no_bc
302 LOOP
303
304 insert_rec(x_project_id, x_funds_control_code_tsk
305 , x_award_id, task_rec.task_id, null, null);
306
307 -- resources
308
309
310 FOR rec_res_grps IN c_res_resgrp_all
311 LOOP
312
313 insert_rec( x_project_id
314 , rec_res_grps.funds_control_level
315 , x_award_id
316 , task_rec.task_id
317 , rec_res_grps.parent_member_id
318 , rec_res_grps.resource_list_member_id
319 );
320
321 end loop;
322
323
324 END LOOP;
325 end if;
326
327 RETCODE := 0; -- R11i change
328 Exception
329 when NO_DATA_FOUND then
330 -- RETCODE := 'E';
331 RETCODE := 2; -- R11i change
332 ERRBUF := ('GMS_BUDG_CNTRL_REC_NOT_FOUND');
333 when OTHERS then
334 -- RETCODE := 'U';
335 RETCODE := 1; -- R11i change
336 ERRBUF := (SQLCODE||SQLERRM);
337 End;
338
339 -- Bug 5162777 : Created the procedure bud_ctrl_create
340 PROCEDURE bud_ctrl_create
341 (p_project_id IN NUMBER
342 ,p_award_id IN NUMBER
343 ,p_prev_entry_level_code IN pa_budget_entry_methods.entry_level_code%type
344 ,p_entry_level_code IN pa_budget_entry_methods.entry_level_code%type
345 ,p_resource_list_id IN NUMBER
346 ,p_group_resource_type_id IN NUMBER
347 ,x_err_code OUT NOCOPY NUMBER
348 ,x_err_stage OUT NOCOPY VARCHAR2) is
349
350
351 l_budctrl_exists VARCHAR2(1) := 'N';
355 CURSOR c_budctrl_exists checks if there exists any budgetary control records for the project and award. */
352 l_calling_mode VARCHAR2(30) := 'BASELINE';
353
354 /* Bug 5162777 :
356
357 CURSOR c_budctrl_exists IS
358 SELECT 'Y'
359 FROM DUAL
360 WHERE EXISTS (SELECT 1
361 FROM gms_budgetary_controls bc
362 where bc.project_id = p_project_id
363 and bc.award_id = p_award_id);
364
365 BEGIN
366
367 --====================================================================================
368 /*
369 Bug 5162777 :
370 If first time baselining then
371 l_calling_mode := 'BASELINE';
372 Else
373 If the entry_level_code of the budget has changed then
374 Call del_bc_rec_for_reset_auto to delete all the budgetary control records.
375 l_calling_mode := 'BASELINE'
376 Else
377 l_calling_mode := 'REBASELINE'
378 end if
379 End if
380 Then gms_budg_cont_setup.create_records is called with the appropriate calling mode
381 */
382
383 --====================================================================================
384
385
386
387 IF L_DEBUG = 'Y' THEN
388 gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - Setting up default budgetary_control', 'C');
389 END IF;
390
391 OPEN c_budctrl_exists;
392 FETCH c_budctrl_exists INTO l_budctrl_exists;
393 CLOSE c_budctrl_exists;
394
395
396 IF l_budctrl_exists <> 'Y' THEN -- First time baselining
397
398 IF L_DEBUG = 'Y' THEN
399 gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - First time baselining', 'C');
400 END IF;
401
402 ELSE -- rebaselining
403
404 IF nvl(p_prev_entry_level_code, p_entry_level_code) <> p_entry_level_code then
405 IF L_DEBUG = 'Y' THEN
406 gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - Re-baselining if the entry level code is changed', 'C');
407 END IF;
408
409 -- Bug 5162777 : Call del_bc_rec_for_reset_auto to delete BC records in autonomous mode during BC reset.
410 del_bc_rec_for_reset_auto (p_project_id => p_project_id,
411 p_award_id => p_award_id);
412
413 ELSE
414 IF L_DEBUG = 'Y' THEN
418
415 gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - Re-baselining if the entry level code is not changed', 'C');
416 END IF;
417 l_calling_mode := 'REBASELINE';
419 end if; -- If entry_level_code is changed then we need to refresh budgetary control
420 end if; --IF l_budctrl_exists <> 'Y'
421
422 /*gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - Calling gms_budg_cont_setup.create_records', 'C');
423 gms_budg_cont_setup.create_records(x_project_id => p_project_id
424 ,x_award_id => p_award_id
425 ,x_entry_level_code => p_entry_level_code
426 ,x_resource_list_id => p_resource_list_id
427 ,x_group_resource_type_id => p_group_resource_type_id
428 ,retcode => x_err_code
429 ,errbuf => x_err_stage
430 ,p_calling_mode => l_calling_mode); commented for bug 12432376*/
431
432 END bud_ctrl_create;
433
434 END GMS_BUDG_CONT_SETUP;
[Home] [Help]