1 package body pa_budget_utils2 as
2 -- $Header: PAXBUTLB.pls 120.3 2006/04/04 00:55:46 psingara noship $
3
4 ------------------------------------------------------------------------------
5 -- This function checks if a budget-at-completion exists at the task level
6 -- or at the project level (if task id is passed as null)
7 ------------------------------------------------------------------------------
8 --
9 --
10 --History:
11 -- xx-xxx-xx who? - Created
12 --
13 -- 22-AUG-02 jwhite Adapted procedure to support the new FP model.
14 --
15 --
16 --
17 function check_budget_at_compl_exists (x_project_id in number,
18 x_task_id in number)
19 return varchar2
20 is
21 l_return_code varchar2(1) := 'N';
22 l_entry_level varchar2(1) := 'P';
23 l_baseline_funding pa_projects_all.baseline_funding_flag%TYPE := 'N'; --Bug 5098809.
24
25 /* Changes for FP.M, Tracking Bug No - 3354518, we have to split
26 the cursor c_chk_bud below into two different cursors, in accord
27 to the permissible values of _plan_level_code columns
28 The Permissible values for plan level codes are
29 For Project - 'P'
30 and for Task - 'T','L' */
31
32 /* Commenting out code for FP.M, Tracking Bug No - 3354518 : Starts */
33 /* cursor c_chk_bud(p_entry_level varchar2) is
34 select 'Y'
35 from pa_budget_versions v,
36 pa_budget_entry_methods m,
37 pa_budget_types bt
38 where v.project_id = x_project_id
39 and v.budget_status_code = 'W'
40 and m.entry_level_code = p_entry_level
41 and m.time_phased_type_code='N'
42 and v.budget_entry_method_code = m.budget_entry_method_code
43 and v.budget_type_code = bt.budget_type_Code
44 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
45 UNION ALL
46 select 'Y'
47 from pa_budget_versions v
48 ,pa_proj_fp_options fo
49 where v.project_id = x_project_id
50 and v.budget_version_id = fo.fin_plan_version_id
51 and (fo.all_time_phased_code = 'N'
52 OR fo.cost_time_phased_code = 'N'
53 OR fo.revenue_time_phased_code = 'N')
54 and (fo.all_fin_plan_level_code = p_entry_level
55 OR fo.cost_fin_plan_level_code = p_entry_level
56 OR fo.revenue_fin_plan_level_code = p_entry_level); */
57
58 /* Commenting out code for FP.M, Tracking Bug No - 3354518 : Ends */
59
60 /* Defining cursor below for checking if a budget-at-completion exists at the Project level */
61 /* Changes for FP.M, Tracking Bug No - 3354518 */
62 cursor c_chk_bud_p is
63 select 'Y'
64 from pa_budget_versions v,
65 pa_budget_entry_methods m,
66 pa_budget_types bt
67 where v.project_id = x_project_id
68 and v.budget_status_code = 'W'
69 and m.entry_level_code = 'P'
70 and m.time_phased_type_code='N'
71 and v.budget_entry_method_code = m.budget_entry_method_code
72 and v.budget_type_code = bt.budget_type_Code
73 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
74 and nvl(v.wp_version_flag,'N') = 'N'
75 and 'X' = DECODE(l_baseline_funding, 'Y', DECODE(v.budget_type_code, 'AR', 'Z', 'X'), 'X') --Bug 5098809.
76 UNION ALL
77 select 'Y'
78 from pa_budget_versions v
79 ,pa_proj_fp_options fo
80 where v.project_id = x_project_id
81 and v.budget_version_id = fo.fin_plan_version_id
82 and fo.project_id = v.project_id -- raja perf bug 3683360
83 and fo.fin_plan_type_id = v.fin_plan_type_id -- raja perf bug 3683360
84 and (fo.all_time_phased_code = 'N'
85 OR fo.cost_time_phased_code = 'N'
86 OR fo.revenue_time_phased_code = 'N')
87 and (fo.all_fin_plan_level_code = 'P'
88 OR fo.cost_fin_plan_level_code = 'P'
89 OR fo.revenue_fin_plan_level_code = 'P')
90 and nvl(v.wp_version_flag,'N') = 'N'
91 and 'X' = DECODE(l_baseline_funding, 'Y', DECODE(v.approved_rev_plan_type_flag, 'Y', 'Z', 'X'), 'X'); --Bug 5098809.
92 /* Defining cursor below for checking if a budget-at-completion exists at the Task level */
93 /* Changes for FP.M, Tracking Bug No - 3354518 */
94 cursor c_chk_bud_t is
95 select 'Y'
96 from pa_budget_versions v,
97 pa_budget_entry_methods m,
98 pa_budget_types bt
99 where v.project_id = x_project_id
100 and v.budget_status_code = 'W'
101 and m.entry_level_code in ('T','L','M')
102 and m.time_phased_type_code='N'
103 and v.budget_entry_method_code = m.budget_entry_method_code
104 and v.budget_type_code = bt.budget_type_Code
105 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
106 and nvl(v.wp_version_flag,'N') = 'N'
107 and 'X' = DECODE(l_baseline_funding, 'Y', DECODE(v.budget_type_code, 'AR', 'Z', 'X'), 'X') --Bug 5098809.
108 UNION ALL
109 select 'Y'
110 from pa_budget_versions v
111 ,pa_proj_fp_options fo
112 where v.project_id = x_project_id
113 and v.budget_version_id = fo.fin_plan_version_id
114 and fo.project_id = v.project_id -- raja perf bug 3683360
115 and fo.fin_plan_type_id = v.fin_plan_type_id -- raja perf bug 3683360
116 and (fo.all_time_phased_code = 'N'
117 OR fo.cost_time_phased_code = 'N'
118 OR fo.revenue_time_phased_code = 'N')
119 and (fo.all_fin_plan_level_code in ('T','L')
120 OR fo.cost_fin_plan_level_code in ('T','L')
121 OR fo.revenue_fin_plan_level_code in ('T','L'))
122 and nvl(v.wp_version_flag,'N') = 'N'
123 and 'X' = DECODE(l_baseline_funding, 'Y', DECODE(v.approved_rev_plan_type_flag, 'Y', 'Z', 'X'), 'X'); --Bug 5098809.
124 begin
125
126 if (x_project_id is null) then
127 return null;
128 end if;
129
130 -- Bug 5098809.
131 SELECT nvl(baseline_funding_flag, 'N')
132 INTO l_baseline_funding
133 FROM pa_projects_all
134 WHERE project_id = x_project_id;
135
136 /* Changes for FP.M, Tracking Bug No - 3354518 ,
137 Since we no longer have a single cursor definition
138 such as c_chk_bud - and we have split the cursor into
139 two different cursors, c_chk_bud_p for project check,
140 and c_chk_bud_t for task check, we modify the logic
141 to derive the value of l_return_code accordingly below*/
142
143 /* Commenting out code for FP.M, Tracking Bug No - 3354518 : Ends */
144 /* if (x_task_id is null) then -- Find Project Level Budget
145 l_entry_level := 'P';
146 else
147 l_entry_level := 'T'; -- Find Task Level Budgets
148 end if;
149
150 open c_chk_bud(l_entry_level);
151
152 fetch c_chk_bud into l_return_code;
153
154 if c_chk_bud%found then
155 l_return_code := 'Y';
156 else
157 l_return_code := 'N';
158 end if;
159
160 close c_chk_bud; */
161 /* Commenting out code for FP.M, Tracking Bug No - 3354518 : Ends */
162
163 /* If x_task_id is null then we fetch value into l_return_code
164 from cursor c_chk_bud_p else we fetch value from c_chk_bud_r */
165 if (x_task_id is null) then -- Find Project Level Budget
166 open c_chk_bud_p;
167 fetch c_chk_bud_p into l_return_code;
168 if c_chk_bud_p%notfound then
169 l_return_code := 'N';
170 end if;
171 close c_chk_bud_p;
172
173 else -- Find Task Level Budgets
174 open c_chk_bud_t;
175 fetch c_chk_bud_t into l_return_code;
176 if c_chk_bud_t%notfound then --Bug 5093908.
177 l_return_code := 'N';
178 end if;
179 close c_chk_bud_t;
180 end if;
181
182 return l_return_code;
183
184 exception
185 when others then
186 return NULL;
187
188 end check_budget_at_compl_exists;
189
190 ------------------------------------------------------------------------------
191 -- This procedure changes the status of a budget to submitted
192 ------------------------------------------------------------------------------
193 procedure submit_budget(x_budget_version_id in number,
194 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
195 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
196 x_err_stack in out NOCOPY varchar2 ) --File.Sql.39 bug 4440895
197 is
198 l_old_stack varchar2(630);
199
200 begin
201 x_err_code := 0;
202 l_old_stack := x_err_stack;
203 x_err_stack := x_err_stack ||'->pa_budget_utils2.submit_budget';
204
205 -- Set the budget_status_code to 'S' (Submit).
206
207 UPDATE pa_budget_versions
208 SET budget_status_code = 'S'
209 WHERE budget_version_id = x_budget_version_id;
210
211 x_err_stack := l_old_stack;
212
213 exception
214 when others then
215 x_err_code := SQLCODE;
216 x_err_stage := 'PA_SQL_ERROR';
217
218 end submit_budget;
219
220 ------------------------------------------------------------------------------
221 -- This procedure changes the status of a budget to working
222 ------------------------------------------------------------------------------
223 procedure rework_budget(x_budget_version_id in number,
224 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
225 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
226 x_err_stack in out NOCOPY varchar2 ) --File.Sql.39 bug 4440895
227 is
228 l_old_stack varchar2(630);
229
230 begin
231 x_err_code := 0;
232 l_old_stack := x_err_stack;
233 x_err_stack := x_err_stack ||'->pa_budget_utils2.rework_budget';
234
235 -- Set the budget_status_code to 'W' (Working).
236
237 UPDATE pa_budget_versions
238 SET budget_status_code = 'W'
239 WHERE budget_version_id = x_budget_version_id;
240
241 x_err_stack := l_old_stack;
242
243 exception
244 when others then
245 x_err_code := SQLCODE;
246 x_err_stage := 'PA_SQL_ERROR';
247
248 end rework_budget;
249
250 ------------------------------------------------------------------------------
251 -- This procedure checks if task is a lowest-level task in budgets .
252 --
253 -- For the old budgets model and org fcst projects, the check should be based
254 -- upon the resource assignments.
255 -- Task is considered as a lowest-level task in a budget in the following cases:
256 -- 1. Task exists in budgets with BEM "By lowest task".
257 -- 2. Task has a parent task in the WBS, and exists in budgets with BEM
258 -- "By top/lowest task"
259 --
260 -- For the fin-plan model, the check should be based upon the fp elements.
261 -- Subtask creation is not okay for a lowest task in the following cases:
262 -- When x_validation_mode is U (Unrestricted mode)
263 -- . The task has been referred to in any of the baselined budget versions.
264 -- . The option has been planned at lowest task level and amounts have been
265 -- entered against this task.
266 -- . The option has been planned at top and lowest task level, the top task
267 -- of this task is marked to be plannable at lowest task and amounts have
268 -- been entered against this task
269 -- When x_validation_mode is R (Restricted mode),
270 -- . The task is a plannable element in any budget version.
271 --
272 -- Note: The api is called only for a task that has no children. So, the check
273 -- needn't be done again for looking at the child tasks.
274 ------------------------------------------------------------------------------
275 --
276 --History:
277 -- xx-xxx-xx who? - Created
278 --
279 -- 22-AUG-02 jwhite Adapted procedure to support the new FP model.
280 -- 24-APR-03 vejayara Bug 2920954- Post K changes to support finplan
281 -- 05-MAY-03 vejayara Bug 2920954- Added x_call_mode input paramter.
282 -- 12-MAY-03 vejayara Bug 2920954- Changed x_call_mode input paramter
283 -- to x_valiation_mode
284 -- 05-JUN-03 rravipat Bug 2993894-
285 -- In 'Restricted' mode Subtask creation is not okay
286 -- for a lowest task if the task is referenced for
287 -- any of the options(project/plantype/planversion)
288 -- in pa_fp_elements table.
289 -- Changed c_chk_tsk_bud_R_mode cursor
290 -- so that in restricted mode the query is not
291 -- restricted to plan_versions alone.
292 --
293 --
294
295 function check_task_lowest_in_budgets (x_task_id in number,
296 x_top_task_id in number,
297 x_validation_mode in varchar2)
298 return number
299 is
300 l_return_code number;
301
302
303
304 cursor c_chk_tsk_bud_R_mode is
305 select 1
306 from dual
307 where exists
308 (select 'x' /* Old budgets model */
309 from pa_budget_versions v,
310 pa_budget_entry_methods m,
311 pa_resource_assignments r
312 where r.task_id = x_task_id
313 and ( (m.entry_level_code = 'L')
314 or
315 (m.entry_level_code = 'M'
316 and x_task_id <> x_top_task_id)
317 )
318 and v.budget_entry_method_code = m.budget_entry_method_code
319 and v.budget_version_id = r.budget_version_id
320 and v.budget_type_code is not null
321 union all
322 select 'x' /* Org Forcast Versions */
323 from pa_budget_versions v,
324 pa_resource_assignments r,
325 pa_fin_plan_types_b pft
326 where r.task_id = x_task_id
327 and v.budget_type_code is null
328 and v.budget_version_id = r.budget_version_id
329 and v.fin_plan_type_id = pft.fin_plan_type_id
330 and pft.fin_plan_type_code = 'ORG_FORECAST');
331 /* Commenting out the check for New model. FPM Dev changes - Tracking Bug - 3354518 - Starts*/
332 /* union all
333 select 'x' -- Financial plan versions
334 from pa_fp_elements fe
335 -- Bug 2993894 ,pa_budget_versions bv
336 where fe.task_id = x_task_id
337 -- Bug 2993894 and fe.fin_plan_version_id = bv.budget_version_id
338 and (x_task_id <> x_top_task_id OR fe.top_task_planning_level = 'LOWEST')); */
339 /* Commenting out the check for New model. FPM Dev changes - Tracking Bug - 3354518 - Ends*/
340
341 cursor c_chk_tsk_bud_U_mode is
342 select 1
343 from dual
344 where exists
345 (select 'x' /* Old budgets model */
346 from pa_budget_versions v,
347 pa_budget_entry_methods m,
348 pa_resource_assignments r
349 where r.task_id = x_task_id
350 and ( (m.entry_level_code = 'L')
351 or
352 (m.entry_level_code = 'M'
353 and x_task_id <> x_top_task_id)
354 )
355 and v.budget_entry_method_code = m.budget_entry_method_code
356 and v.budget_version_id = r.budget_version_id
357 and v.budget_type_code is not null
358 union all
359 select 'x' /* Org Forcast Versions */
360 from pa_budget_versions v,
361 pa_resource_assignments r,
362 pa_fin_plan_types_b pft
363 where r.task_id = x_task_id
364 and v.budget_type_code is null
365 and v.budget_version_id = r.budget_version_id
366 and v.fin_plan_type_id = pft.fin_plan_type_id
367 and pft.fin_plan_type_code = 'ORG_FORECAST');
368 /* Commenting out the check for New model. FPM Dev changes - Tracking Bug - 3354518 - Starts*/
369 /* union all
370 select 'x'-- Financial plan versions
371 from pa_fp_elements fe,
372 pa_budget_versions bv
373 where fe.task_id = x_task_id
374 and fe.fin_plan_version_id = bv.budget_version_id
375 and (fe.plan_amount_exists_flag = 'Y' or bv.budget_status_code = 'B')
376 and (x_task_id <> x_top_task_id OR fe.top_task_planning_level = 'LOWEST'));*/
377 /* Commenting out the check for New model. FPM Dev changes - Tracking Bug - 3354518 - Ends*/
378
379
380 begin
381 if x_validation_mode = 'R' then
382
383 open c_chk_tsk_bud_R_mode;
384
385 fetch c_chk_tsk_bud_R_mode into l_return_code;
386
387 if c_chk_tsk_bud_R_mode%found then
388 l_return_code := 1; /* Task budget exists */
389 else
390 l_return_code := 0; /* Task budget doesnt exists */
391 end if;
392
393 close c_chk_tsk_bud_R_mode;
394
395 elsif x_validation_mode = 'U' then
396
397 open c_chk_tsk_bud_U_mode;
398
399 fetch c_chk_tsk_bud_U_mode into l_return_code;
400
401 if c_chk_tsk_bud_U_mode%found then
402 l_return_code := 1; /* Task budget exists */
403 else
404 l_return_code := 0; /* Task budget doesnt exists */
405 end if;
406
407 close c_chk_tsk_bud_U_mode;
408
409 else
410
411 /* Invalid arguments passed */
412
413 raise pa_fp_constants_pkg.invalid_arg_exc;
414
415 end if;
416
417 return l_return_code;
418
419 exception
420 when others then
421 l_return_code := NULL;
422 raise;
423
424 end check_task_lowest_in_budgets;
425
426
427 end pa_budget_utils2 ;
[Home] [Help]