1 PACKAGE BODY pa_fin_plan_type_global as
2 /* $Header: PAFPPTGB.pls 120.1 2005/08/19 16:28:30 mwasowic noship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_TYPE_GLOBAL
5 Purpose : API's for Org Forecast: PLANS Page
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
12
13 function Get_Project_Id return NUMBER is
14 BEGIN
15 return pa_fin_plan_type_global.G_PROJECT_ID;
16 END Get_Project_Id;
17
18 /*
19 function Get_Plan_Class_Code return VARCHAR2 is
20 BEGIN
21 return pa_fin_plan_type_global.G_PLAN_CLASS_CODE;
22 END Get_Plan_Class_Code;
23 */
24
25 PROCEDURE set_global_variables
26 (p_project_id IN pa_budget_versions.project_id%TYPE,
27 -- p_plan_class_code IN pa_fin_plan_types_b.plan_class_code%TYPE,
28 x_factor_by_code OUT NOCOPY pa_proj_fp_options.factor_by_code%TYPE, --File.Sql.39 bug 4440895
29 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
30 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
32 is
33 BEGIN
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35 x_msg_count := 0;
36 pa_fin_plan_type_global.G_PROJECT_ID := p_project_id;
37 -- pa_fin_plan_type_global.G_PLAN_CLASS_CODE := p_plan_class_code;
38 -- *** bug fix 2770782: retrieve x_budget_status code from project-level row ***
39 select nvl(po.factor_by_code, 1)
40 into x_factor_by_code
41 from pa_proj_fp_options po
42 where po.project_id = p_project_id and
43 po.fin_plan_option_level_code = 'PROJECT';
44
45 EXCEPTION WHEN NO_DATA_FOUND THEN
46 x_factor_by_code := 1;
47 WHEN OTHERS THEN
48 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
49 x_msg_count := 1;
50 x_msg_data := SQLERRM;
51 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPE_GLOBAL',
52 p_procedure_name => 'set_global_variables');
53 END set_global_variables;
54
55 PROCEDURE pa_fp_get_orgfcst_version_id( p_project_id IN NUMBER,
56 p_plan_type_id IN NUMBER,
57 p_plan_status_code IN VARCHAR2,
58 x_orgfcst_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
59 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
60 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
61 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
62 )
63 IS
64 cursor cb_csr is
65 select budget_version_id
66 from pa_budget_versions
67 where project_id = p_project_id and
68 fin_plan_type_id = p_plan_type_id and
69 version_type = 'ORG_FORECAST' and
70 current_flag = 'Y';
71 cb_rec cb_csr%ROWTYPE;
72
73 cursor cw_csr is
74 select budget_version_id
75 from pa_budget_versions
76 where project_id = p_project_id and
77 fin_plan_type_id = p_plan_type_id and
78 version_type = 'ORG_FORECAST' and
79 current_working_flag = 'Y';
80 cw_rec cw_csr%ROWTYPE;
81
82 BEGIN
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84 IF p_plan_status_code = 'CB' THEN
85 open cb_csr;
86 fetch cb_csr into cb_rec;
87 if cb_csr%NOTFOUND then
88 x_orgfcst_version_id := -1;
89 else
90 x_orgfcst_version_id := cb_rec.budget_version_id;
91 end if;
92 close cb_csr;
93 ELSE
94 open cw_csr;
95 fetch cw_csr into cw_rec;
96 if cw_csr%NOTFOUND then
97 x_orgfcst_version_id := -1;
98 else
99 x_orgfcst_version_id := cw_rec.budget_version_id;
100 end if;
101 close cw_csr;
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
107 x_msg_count := 1;
108 x_msg_data := SQLERRM;
109 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPE_GLOBAL',
110 p_procedure_name => 'pa_fp_get_orgfcst_version_id');
111 END pa_fp_get_orgfcst_version_id;
112 /* ------------------------------------------------------------------- */
113
114 PROCEDURE pa_fp_get_finplan_version_id
115 (p_project_id IN NUMBER,
116 p_plan_type_id IN NUMBER,
117 p_plan_status_code IN VARCHAR2,
118 x_cost_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
119 x_rev_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
120 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
121 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
122 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
123 is
124
125 -- we will get only one version per cursor
126 cursor working_cost_csr is
127 select budget_version_id
128 from pa_budget_versions
129 where project_id = p_project_id and
130 fin_plan_type_id = p_plan_type_id and
131 version_type in ('COST', 'ALL') and
132 current_working_flag = 'Y';
133 working_cost_rec working_cost_csr%ROWTYPE;
134
135 cursor working_revenue_csr is
136 select budget_version_id
137 from pa_budget_versions
138 where project_id = p_project_id and
139 fin_plan_type_id = p_plan_type_id and
140 version_type in ('REVENUE', 'ALL') and
141 current_working_flag = 'Y';
142 working_revenue_rec working_revenue_csr%ROWTYPE;
143
144 cursor baselined_cost_csr is
145 select budget_version_id
146 from pa_budget_versions
147 where project_id = p_project_id and
148 fin_plan_type_id = p_plan_type_id and
149 version_type in ('COST', 'ALL') and
150 current_flag = 'Y';
151 baselined_cost_rec baselined_cost_csr%ROWTYPE;
152
153 cursor baselined_revenue_csr is
154 select budget_version_id
155 from pa_budget_versions
156 where project_id = p_project_id and
157 fin_plan_type_id = p_plan_type_id and
158 version_type in ('REVENUE', 'ALL') and
159 current_flag = 'Y';
160 baselined_revenue_rec baselined_revenue_csr%ROWTYPE;
161
162 BEGIN
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164 x_msg_count := 0;
165 -- looking for the current baselined cost/revenue versions
166 if p_plan_status_code = 'CB' then
167 open baselined_cost_csr;
168 fetch baselined_cost_csr into baselined_cost_rec;
169 if baselined_cost_csr%NOTFOUND then
170 x_cost_version_id := -1;
171 else
172 x_cost_version_id := baselined_cost_rec.budget_version_id;
173 end if; -- baselined_cost_csr%NOTFOUND
174 close baselined_cost_csr;
175 open baselined_revenue_csr;
176 fetch baselined_revenue_csr into baselined_revenue_rec;
177 if baselined_revenue_csr%NOTFOUND then
178 x_rev_version_id := -1;
179 else
180 x_rev_version_id := baselined_revenue_rec.budget_version_id;
181 end if; -- baselined_revenue_csr%NOTFOUND
182 close baselined_revenue_csr;
183 -- looking for the current working cost/revenue versions
184 else
185 open working_cost_csr;
186 fetch working_cost_csr into working_cost_rec;
187 if working_cost_csr%NOTFOUND then
188 x_cost_version_id := -1;
189 else
190 x_cost_version_id := working_cost_rec.budget_version_id;
191 end if; -- working_cost_csr%NOTFOUND
192 close working_cost_csr;
193 open working_revenue_csr;
194 fetch working_revenue_csr into working_revenue_rec;
195 if working_revenue_csr%NOTFOUND then
196 x_rev_version_id := -1;
197 else
198 x_rev_version_id := working_revenue_rec.budget_version_id;
199 end if; -- working_revenue_csr%NOTFOUND
200 close working_revenue_csr;
201 end if;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 x_msg_count := 1;
207 x_msg_data := SQLERRM;
208 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPE_GLOBAL',
209 p_procedure_name => 'pa_fp_get_finplan_version_id');
210
211 END pa_fp_get_finplan_version_id;
212 /* ------------------------------------------------------------------- */
213
214 PROCEDURE delete_plan_type_from_project
215 (p_project_id IN pa_budget_versions.project_id%TYPE,
216 p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE,
217 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
218 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
219 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
220 is
221
222 cursor plan_type_versions_csr is
223 select budget_version_id
224 from pa_budget_versions
225 where project_id = p_project_id and
226 fin_plan_type_id = p_fin_plan_type_id;
227 plan_type_versions_rec plan_type_versions_csr%ROWTYPE;
228
229 cursor plan_type_code_csr is
230 select fin_plan_type_code
231 from pa_fin_plan_types_b
232 where fin_plan_type_id = p_fin_plan_type_id;
233 plan_type_code_rec plan_type_code_csr%ROWTYPE;
234
235 -- Bug 3619687 Cursor to return all the fp options that need to be
236 -- updated up on delete
237 CURSOR fp_options_cur(c_project_id NUMBER, c_fin_plan_type_id NUMBER) IS
238 SELECT gen_src_cost_plan_type_id
239 , gen_src_cost_plan_version_id
240 , gen_src_cost_plan_ver_code
241 , gen_src_rev_plan_type_id
242 , gen_src_rev_plan_version_id
243 , gen_src_rev_plan_ver_code
244 , gen_src_all_plan_type_id
245 , gen_src_all_plan_version_id
246 , gen_src_all_plan_ver_code
247 , fin_plan_option_level_code
248 , proj_fp_options_id
249 FROM pa_proj_fp_options
250 WHERE project_id = c_project_id
251 AND fin_plan_type_id IS NOT NULL -- eliminates project level record
252 AND fin_plan_type_id <> c_fin_plan_type_id -- eliminates plan type being deleted
253 AND (gen_src_cost_plan_type_id = c_fin_plan_type_id OR
254 gen_src_rev_plan_type_id = c_fin_plan_type_id OR
255 gen_src_all_plan_type_id = c_fin_plan_type_id);
256
257 fp_options_rec fp_options_cur%ROWTYPE;
258
259 -- Bug 3619687 Cursor to return the default generation source plan type
260 CURSOR def_gen_src_plan_type_cur (c_project_id NUMBER, c_fin_plan_type_id NUMBER)IS
261 SELECT pt.fin_plan_type_id as fin_plan_type_id
262 ,pt.plan_class_code as plan_class_code
263 FROM pa_proj_fp_options o
264 ,pa_fin_plan_types_vl pt
265 WHERE o.project_id = c_project_id
266 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
267 AND o.fin_plan_type_id <> c_fin_plan_type_id
268 AND o.fin_plan_preference_code <> 'REVENUE_ONLY'
269 AND o.fin_plan_type_id = pt.fin_plan_type_id
270 AND nvl(pt.use_for_workplan_flag, 'N') = 'N'
271 ORDER BY name ASC;
272
273 l_def_gen_src_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
274 l_def_gen_src_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
275
276 -- error handling variables
277 l_msg_count NUMBER := 0;
278 l_data VARCHAR2(2000);
279 l_msg_data VARCHAR2(2000);
280 l_error_msg_code VARCHAR2(30);
281 l_msg_index_out NUMBER;
282 l_return_status VARCHAR2(2000);
283
284 BEGIN
285 FND_MSG_PUB.initialize;
286 IF P_PA_DEBUG_MODE = 'Y' THEN
287 pa_debug.init_err_stack('PA_FIN_PLAN_TYPE_GLOBAL.delete_plan_type_from_project');
288 END IF;
289 x_msg_count := 0;
290 /* CHECK FOR BUSINESS RULES VIOLATIONS */
291 -- If versions of the plan type exist in the project, cannot delete plan type
292 open plan_type_versions_csr;
293 fetch plan_type_versions_csr into plan_type_versions_rec;
294 if not (plan_type_versions_csr%NOTFOUND) then
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
297 p_msg_name => 'PA_FP_DELETE_PLAN_TYPE_ERROR');
298 end if;
299 close plan_type_versions_csr;
300
301 -- Cannot delete the Org Forecasting plan type
302 open plan_type_code_csr;
303 fetch plan_type_code_csr into plan_type_code_rec;
304 if not (plan_type_code_csr%NOTFOUND) then
305 if plan_type_code_rec.fin_plan_type_code = 'ORG_FORECAST' then
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
308 p_msg_name => 'PA_CANNOT_DELETE_ORGFCST');
309 end if;
310 end if;
311 close plan_type_code_csr;
312
313 /* If There are ANY Business Rules Violations , Then Do NOT Proceed: RETURN */
314 l_msg_count := FND_MSG_PUB.count_msg;
315 if l_msg_count > 0 then
316 if l_msg_count = 1 then
317 PA_INTERFACE_UTILS_PUB.get_messages
318 (p_encoded => FND_API.G_TRUE,
319 p_msg_index => 1,
320 p_msg_count => l_msg_count,
321 p_msg_data => l_msg_data,
322 p_data => l_data,
323 p_msg_index_out => l_msg_index_out);
324 x_msg_data := l_data;
325 x_msg_count := l_msg_count;
326 else
327 x_msg_count := l_msg_count;
328 end if;
329 pa_debug.reset_err_stack;
330 return;
331 end if;
332
333 /* IF NO BUSINESS RULES VIOLATIONS: PROCEED WITH DELETE PLAN TYPE */
334 SAVEPOINT PA_FP_DELETE_PLAN_TYPE;
335
336 -- Bug 3619687 if plan type id is the genration source plan type for any other option
337 -- update the column with default plan type. Default plan type is determined through
338 -- ordering all the other attached plan types in ascending order by name. The first
339 -- plan type in that arrangement is the default used for updating all such options
340
341 -- First fetch the default generation source plan type id
342 OPEN def_gen_src_plan_type_cur(p_project_id, p_fin_plan_type_id);
343 FETCH def_gen_src_plan_type_cur
344 INTO l_def_gen_src_plan_type_id,l_def_gen_src_plan_class_code;
345 CLOSE def_gen_src_plan_type_cur;
346
347 -- Check if any of the fp options exist with the input plan type as generation source
348 OPEN fp_options_cur(p_project_id, p_fin_plan_type_id);
349 LOOP
350 FETCH fp_options_cur INTO fp_options_rec;
351 EXIT WHEN fp_options_cur%NOTFOUND;
352
353 IF nvl(fp_options_rec.gen_src_cost_plan_type_id, -1) = p_fin_plan_type_id THEN
354
355 fp_options_rec.gen_src_cost_plan_type_id := l_def_gen_src_plan_type_id;
356
357 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
358 fp_options_rec.gen_src_cost_plan_version_id := null;
359 fp_options_rec.gen_src_cost_plan_ver_code := null;
360 ELSE
361 fp_options_rec.gen_src_cost_plan_version_id := null;
362 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
363 fp_options_rec.gen_src_cost_plan_ver_code := 'CURRENT_APPROVED';
364 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
365 fp_options_rec.gen_src_cost_plan_ver_code := 'CURRENT_BASELINED';
366 ELSE
367 fp_options_rec.gen_src_cost_plan_ver_code := null;
368 END IF;
369 END IF;
370
371 END IF;
372
373 IF nvl(fp_options_rec.gen_src_rev_plan_type_id, -1) = p_fin_plan_type_id THEN
374
375 fp_options_rec.gen_src_rev_plan_type_id := l_def_gen_src_plan_type_id;
376
380 ELSE
377 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
378 fp_options_rec.gen_src_rev_plan_version_id := null;
379 fp_options_rec.gen_src_rev_plan_ver_code := null;
381 fp_options_rec.gen_src_rev_plan_version_id := null;
382 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
383 fp_options_rec.gen_src_rev_plan_ver_code := 'CURRENT_APPROVED';
384 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
385 fp_options_rec.gen_src_rev_plan_ver_code := 'CURRENT_BASELINED';
386 ELSE
387 fp_options_rec.gen_src_rev_plan_ver_code := null;
388 END IF;
389 END IF;
390
391 END IF;
392
393 IF nvl(fp_options_rec.gen_src_all_plan_type_id, -1) = p_fin_plan_type_id THEN
394
395 fp_options_rec.gen_src_all_plan_type_id := l_def_gen_src_plan_type_id;
396
397 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
398 fp_options_rec.gen_src_all_plan_version_id := null;
399 fp_options_rec.gen_src_all_plan_ver_code := null;
400 ELSE
401 fp_options_rec.gen_src_all_plan_version_id := null;
402 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
403 fp_options_rec.gen_src_all_plan_ver_code := 'CURRENT_APPROVED';
404 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
405 fp_options_rec.gen_src_all_plan_ver_code := 'CURRENT_BASELINED';
406 ELSE
407 fp_options_rec.gen_src_all_plan_ver_code := null;
408 END IF;
409 END IF;
410
411 END IF;
412
413 -- Update pa_proj_fp_options table
414 UPDATE pa_proj_fp_options
415 SET gen_src_cost_plan_type_id = fp_options_rec.gen_src_cost_plan_type_id
416 , gen_src_cost_plan_version_id = fp_options_rec.gen_src_cost_plan_version_id
417 , gen_src_cost_plan_ver_code = fp_options_rec.gen_src_cost_plan_ver_code
418 , gen_src_rev_plan_type_id = fp_options_rec.gen_src_rev_plan_type_id
419 , gen_src_rev_plan_version_id = fp_options_rec.gen_src_rev_plan_version_id
420 , gen_src_rev_plan_ver_code = fp_options_rec.gen_src_rev_plan_ver_code
421 , gen_src_all_plan_type_id = fp_options_rec.gen_src_all_plan_type_id
422 , gen_src_all_plan_version_id = fp_options_rec.gen_src_all_plan_version_id
423 , gen_src_all_plan_ver_code = fp_options_rec.gen_src_all_plan_ver_code
424 , record_version_number = record_version_number + 1
425 , last_update_date = SYSDATE
426 , last_updated_by = FND_GLOBAL.user_id
427 , last_update_login = FND_GLOBAL.login_id
428 WHERE proj_fp_options_id = fp_options_rec.proj_fp_options_id;
429
430 END LOOP;
431 CLOSE fp_options_cur;
432 -- End of changes for Bug 3619687
433
434 -- delete from PA_FP_ELEMENTS
435
436 /*
437 Bug 3106741 For pa_fp_elements there is no index avaialable on project_id and plan_type_id
441 delete from pa_fp_elements e
438 to avoid full table scan pa_proj_fp_options would be used to fetch all the relevant option_ids
439 */
440
442 where e.proj_fp_options_id in (select o.proj_fp_options_id from pa_proj_fp_options o
443 where o.project_id = p_project_id and
444 o.fin_plan_type_id = p_fin_plan_type_id);
445 /*
446 Bug 3106741 For pa_fp_txn_currencies there is no index avaialable on project_id and plan_type_id
447 to avoid full table scan pa_proj_fp_options would be used to fetch all the relevant option_ids
448 */
449
450 -- delete from PA_FP_TXN_CURRENCIES
451 delete from pa_fp_txn_currencies tc
452 where tc.proj_fp_options_id in (select o.proj_fp_options_id
453 from pa_proj_fp_options o
454 where o.project_id = p_project_id and --Replaced project_id with p_project_id
455 --for bug 2740553
456 o.fin_plan_type_id = p_fin_plan_type_id);
457
458 --For Bug 2976168. Should delete the records from pa_fp_excluded_elements also
459
460 IF P_PA_DEBUG_MODE = 'Y' THEN
461 pa_debug.g_err_stage:= 'About to delete from pa_fp_excluded_elements';
462 pa_debug.write('delete_plan_type_from_project : PA_FIN_PLAN_TYPE_GLOBAL',pa_debug.g_err_stage,3);
463 END IF;
464
465 /* Using proj_fp_options_id join instead of directly using project_id, fin_plan_type_id to take
466 advantage of pa_fp_excluded_index_u1 index on pa_fp_excluded_elements */
467
468 DELETE
469 FROM pa_fp_excluded_elements ee
470 WHERE ee.proj_fp_options_id IN (SELECT pfo.proj_fp_options_id
471 FROM pa_proj_fp_options pfo
472 WHERE pfo.project_id = p_project_id
473 AND pfo.fin_plan_type_id=p_fin_plan_type_id);
474
475 IF P_PA_DEBUG_MODE = 'Y' THEN
476 pa_debug.g_err_stage:= To_char(SQL%ROWCOUNT) || ' records deleted.';
477 pa_debug.write('delete_plan_type_from_project : PA_FIN_PLAN_TYPE_GLOBAL',pa_debug.g_err_stage,3);
478 END IF;
479
480 -- finally, delete from PA_PROJ_FP_OPTIONS
481 delete from pa_proj_fp_options
482 where project_id = p_project_id and
483 fin_plan_option_level_code = 'PLAN_TYPE' and
484 fin_plan_type_id = p_fin_plan_type_id;
485
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487 pa_debug.reset_err_stack;
488
489 exception
490 when others then
491 rollback to PA_FP_DELETE_PLAN_TYPE;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 x_msg_count := 1;
494 x_msg_data := SQLERRM;
495 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPE_GLOBAL',
496 p_procedure_name => 'delete_plan_type_from_project');
497 pa_debug.reset_err_stack;
498 raise FND_API.G_EXC_UNEXPECTED_ERROR;
499 END delete_plan_type_from_project;
500
501
502 -- CREATED FOR FP L
503 -- Created by: Danny Lai
504 -- Creation Date: 05/21/03
505 -- SUMMARY: Given a plan type id, this function returns one of the following:
506 -- FORECAST - if the plan type is a forecast plan class
507 -- APPROVED_BUDGET - if the plan type is an approved budget plan class
508 -- NON_APPROVED_BUDGET - if the plan type is a non-approved budget plan class
509 -- This is used for FUNCTION SECURITY checks in Budgeting/Forecasting OA pages
510 FUNCTION plantype_to_planclass
511 (p_project_id IN pa_proj_fp_options.project_id%TYPE,
512 p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE)
513 return VARCHAR2 is
514 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
515 l_approved_cost_pt_flag pa_fin_plan_types_b.approved_cost_plan_type_flag%TYPE;
516 l_approved_rev_pt_flag pa_fin_plan_types_b.approved_rev_plan_type_flag%TYPE;
517 l_return_value VARCHAR2(80);
518 BEGIN
519 select plan_class_code
520 into l_plan_class_code
521 from pa_fin_plan_types_b
522 where fin_plan_type_id = p_fin_plan_type_id;
523 select approved_cost_plan_type_flag,
524 approved_rev_plan_type_flag
525 into l_approved_cost_pt_flag,
526 l_approved_rev_pt_flag
527 from pa_proj_fp_options
528 where project_id = p_project_id and
529 fin_plan_type_id = p_fin_plan_type_id and
530 fin_plan_option_level_code = 'PLAN_TYPE';
531 if l_approved_cost_pt_flag = 'Y' or l_approved_rev_pt_flag = 'Y' then
532 l_return_value := 'APPROVED_BUDGET';
533 else
534 if l_plan_class_code = 'FORECAST' then
535 l_return_value := 'FORECAST';
536 else
537 l_return_value := 'NON_APPROVED_BUDGET';
538 end if;
539 end if;
540 /*
541 if l_plan_class_code = 'FORECAST' then
542 l_return_value := 'FORECAST';
543 else
544 if l_approved_cost_pt_flag = 'Y' or l_approved_rev_pt_flag = 'Y' then
545 l_return_value := 'APPROVED_BUDGET';
546 else
547 l_return_value := 'NON_APPROVED_BUDGET';
548 end if;
549 end if;
550 */
551 return l_return_value;
552 EXCEPTION
553 when NO_DATA_FOUND then
554 return 'INVALID_PLAN_TYPE';
555 when others then
556 return 'INVALID_PLAN_TYPE';
557 END plantype_to_planclass;
558
559
560 -- CREATED FOR FP L
561 -- Created by: Danny Lai
562 -- Creation Date: 05/21/03
563 -- SUMMARY: Given a budget version id, this function returns one of the following:
564 -- FORECAST - if the version is a forecast plan class
565 -- APPROVED_BUDGET - if the version is an approved budget plan class
566 -- NON_APPROVED_BUDGET - if the version is a non-approved budget plan class
567 -- This is used for FUNCTION SECURITY checks in Budgeting/Forecasting OA pages
568 FUNCTION planversion_to_planclass
569 (p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE)
570 return VARCHAR2 is
571 l_return_value VARCHAR2(80);
572 BEGIN
573 return l_return_value;
574 EXCEPTION
575 when NO_DATA_FOUND then
576 return 'INVALID_PLAN_TYPE';
577 when others then
578 return 'INVALID_PLAN_TYPE';
579 END planversion_to_planclass;
580
581 END pa_fin_plan_type_global;
[Home] [Help]