1 PACKAGE BODY pa_fin_plan_type_global as
2 /* $Header: PAFPPTGB.pls 120.3 2011/03/28 07:05:34 kkorrapo ship $
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 --Bug#11776072 - cusror to get rbs_version_id
274 CURSOR get_rbs_version_id_cur(c_project_id NUMBER, c_fin_plan_type_id NUMBER) IS
275 SELECT rbs_version_id
276 FROM pa_proj_fp_options
277 WHERE project_id = c_project_id
278 AND fin_plan_type_id=c_fin_plan_type_id
279 and fin_plan_option_level_code = 'PLAN_TYPE';
280
281 --Bug#11776072 - cusror to get rbs_header_id
282 CURSOR get_rbs_header_id_cur(c_rbs_version_id pa_rbs_versions_b.rbs_version_id%TYPE) IS
283 SELECT rbs_header_id
284 FROM pa_rbs_versions_b
285 WHERE rbs_version_id=c_rbs_version_id;
286
287 --Bug#11776072 - cusror to check rbs association in the project
288 CURSOR check_association_cur(c_rbs_version_id pa_rbs_versions_b.rbs_version_id%TYPE , c_project_id NUMBER) IS
289 SELECT 'X'
290 FROM pa_proj_fp_options
291 WHERE rbs_version_id = c_rbs_version_id
292 AND project_id=c_project_id
293 AND fin_plan_option_level_code = 'PLAN_TYPE';
294
295 l_def_gen_src_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
296 l_def_gen_src_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
297
298 -- error handling variables
299 l_msg_count NUMBER := 0;
300 l_data VARCHAR2(2000);
301 l_msg_data VARCHAR2(2000);
302 l_error_msg_code VARCHAR2(30);
303 l_msg_index_out NUMBER;
304 l_return_status VARCHAR2(2000);
305
306 -- Bug 10305516 skkoppul
307 TYPE x_proj_fp_options_id is table of pa_proj_fp_options.proj_fp_options_id%type;
308 x_proj_fp_options_id_array x_proj_fp_options_id;
309
310 --Bug#11776072
311 l_rbs_version_id pa_rbs_versions_b.rbs_version_id%TYPE;
312 l_rbs_header_id pa_rbs_headers_b.rbs_header_id%TYPE;
313 l_exists_association VARCHAR2(1);
314
315 BEGIN
316 FND_MSG_PUB.initialize;
317 IF P_PA_DEBUG_MODE = 'Y' THEN
318 pa_debug.init_err_stack('PA_FIN_PLAN_TYPE_GLOBAL.delete_plan_type_from_project');
319 END IF;
320 x_msg_count := 0;
321 /* CHECK FOR BUSINESS RULES VIOLATIONS */
322 -- If versions of the plan type exist in the project, cannot delete plan type
323 open plan_type_versions_csr;
324 fetch plan_type_versions_csr into plan_type_versions_rec;
325 if not (plan_type_versions_csr%NOTFOUND) then
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
328 p_msg_name => 'PA_FP_DELETE_PLAN_TYPE_ERROR');
329 end if;
330 close plan_type_versions_csr;
331
332 -- Cannot delete the Org Forecasting plan type
333 open plan_type_code_csr;
334 fetch plan_type_code_csr into plan_type_code_rec;
335 if not (plan_type_code_csr%NOTFOUND) then
336 if plan_type_code_rec.fin_plan_type_code = 'ORG_FORECAST' then
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
339 p_msg_name => 'PA_CANNOT_DELETE_ORGFCST');
340 end if;
341 end if;
342 close plan_type_code_csr;
343
344 /* If There are ANY Business Rules Violations , Then Do NOT Proceed: RETURN */
345 l_msg_count := FND_MSG_PUB.count_msg;
346 if l_msg_count > 0 then
347 if l_msg_count = 1 then
348 PA_INTERFACE_UTILS_PUB.get_messages
349 (p_encoded => FND_API.G_TRUE,
350 p_msg_index => 1,
351 p_msg_count => l_msg_count,
352 p_msg_data => l_msg_data,
353 p_data => l_data,
354 p_msg_index_out => l_msg_index_out);
355 x_msg_data := l_data;
356 x_msg_count := l_msg_count;
357 else
358 x_msg_count := l_msg_count;
359 end if;
360 pa_debug.reset_err_stack;
361 return;
362 end if;
363
364 /* IF NO BUSINESS RULES VIOLATIONS: PROCEED WITH DELETE PLAN TYPE */
365 SAVEPOINT PA_FP_DELETE_PLAN_TYPE;
366
367 -- Bug 3619687 if plan type id is the genration source plan type for any other option
368 -- update the column with default plan type. Default plan type is determined through
369 -- ordering all the other attached plan types in ascending order by name. The first
370 -- plan type in that arrangement is the default used for updating all such options
371
372 -- First fetch the default generation source plan type id
373 OPEN def_gen_src_plan_type_cur(p_project_id, p_fin_plan_type_id);
374 FETCH def_gen_src_plan_type_cur
375 INTO l_def_gen_src_plan_type_id,l_def_gen_src_plan_class_code;
376 CLOSE def_gen_src_plan_type_cur;
377
378 -- Check if any of the fp options exist with the input plan type as generation source
379 OPEN fp_options_cur(p_project_id, p_fin_plan_type_id);
380 LOOP
381 FETCH fp_options_cur INTO fp_options_rec;
382 EXIT WHEN fp_options_cur%NOTFOUND;
383
384 IF nvl(fp_options_rec.gen_src_cost_plan_type_id, -1) = p_fin_plan_type_id THEN
385
386 fp_options_rec.gen_src_cost_plan_type_id := l_def_gen_src_plan_type_id;
387
388 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
389 fp_options_rec.gen_src_cost_plan_version_id := null;
390 fp_options_rec.gen_src_cost_plan_ver_code := null;
391 ELSE
392 fp_options_rec.gen_src_cost_plan_version_id := null;
393 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
394 fp_options_rec.gen_src_cost_plan_ver_code := 'CURRENT_APPROVED';
395 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
396 fp_options_rec.gen_src_cost_plan_ver_code := 'CURRENT_BASELINED';
397 ELSE
398 fp_options_rec.gen_src_cost_plan_ver_code := null;
399 END IF;
400 END IF;
401
402 END IF;
403
404 IF nvl(fp_options_rec.gen_src_rev_plan_type_id, -1) = p_fin_plan_type_id THEN
405
406 fp_options_rec.gen_src_rev_plan_type_id := l_def_gen_src_plan_type_id;
407
408 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
409 fp_options_rec.gen_src_rev_plan_version_id := null;
410 fp_options_rec.gen_src_rev_plan_ver_code := null;
411 ELSE
412 fp_options_rec.gen_src_rev_plan_version_id := null;
413 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
414 fp_options_rec.gen_src_rev_plan_ver_code := 'CURRENT_APPROVED';
415 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
416 fp_options_rec.gen_src_rev_plan_ver_code := 'CURRENT_BASELINED';
417 ELSE
418 fp_options_rec.gen_src_rev_plan_ver_code := null;
419 END IF;
420 END IF;
421
422 END IF;
423
424 IF nvl(fp_options_rec.gen_src_all_plan_type_id, -1) = p_fin_plan_type_id THEN
425
426 fp_options_rec.gen_src_all_plan_type_id := l_def_gen_src_plan_type_id;
427
428 IF 'PLAN_VERSION' = fp_options_rec.fin_plan_option_level_code THEN
429 fp_options_rec.gen_src_all_plan_version_id := null;
430 fp_options_rec.gen_src_all_plan_ver_code := null;
431 ELSE
432 fp_options_rec.gen_src_all_plan_version_id := null;
433 IF 'FORECAST' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
434 fp_options_rec.gen_src_all_plan_ver_code := 'CURRENT_APPROVED';
435 ELSIF 'BUDGET' = nvl(l_def_gen_src_plan_class_code, '-99') THEN
436 fp_options_rec.gen_src_all_plan_ver_code := 'CURRENT_BASELINED';
437 ELSE
438 fp_options_rec.gen_src_all_plan_ver_code := null;
439 END IF;
440 END IF;
441
442 END IF;
443
444 -- Update pa_proj_fp_options table
445 UPDATE pa_proj_fp_options
446 SET gen_src_cost_plan_type_id = fp_options_rec.gen_src_cost_plan_type_id
447 , gen_src_cost_plan_version_id = fp_options_rec.gen_src_cost_plan_version_id
448 , gen_src_cost_plan_ver_code = fp_options_rec.gen_src_cost_plan_ver_code
449 , gen_src_rev_plan_type_id = fp_options_rec.gen_src_rev_plan_type_id
450 , gen_src_rev_plan_version_id = fp_options_rec.gen_src_rev_plan_version_id
451 , gen_src_rev_plan_ver_code = fp_options_rec.gen_src_rev_plan_ver_code
452 , gen_src_all_plan_type_id = fp_options_rec.gen_src_all_plan_type_id
453 , gen_src_all_plan_version_id = fp_options_rec.gen_src_all_plan_version_id
454 , gen_src_all_plan_ver_code = fp_options_rec.gen_src_all_plan_ver_code
455 , record_version_number = record_version_number + 1
456 , last_update_date = SYSDATE
457 , last_updated_by = FND_GLOBAL.user_id
458 , last_update_login = FND_GLOBAL.login_id
459 WHERE proj_fp_options_id = fp_options_rec.proj_fp_options_id;
460
461 END LOOP;
462 CLOSE fp_options_cur;
463 -- End of changes for Bug 3619687
464
465 -- delete from PA_FP_ELEMENTS
466
467 /*
468 Bug 3106741 For pa_fp_elements there is no index avaialable on project_id and plan_type_id
469 to avoid full table scan pa_proj_fp_options would be used to fetch all the relevant option_ids
470 */
471
472 /* commented for bug 10305516
473 delete from pa_fp_elements e
474 where e.proj_fp_options_id in (select o.proj_fp_options_id from pa_proj_fp_options o
475 where o.project_id = p_project_id and
476 o.fin_plan_type_id = p_fin_plan_type_id);
477 */
478 /*
479 Bug 3106741 For pa_fp_txn_currencies there is no index avaialable on project_id and plan_type_id
480 to avoid full table scan pa_proj_fp_options would be used to fetch all the relevant option_ids
481 */
482
483 -- Bug 10305516 skkoppul: replaced above delete statement with following 2 statements
484 SELECT proj_fp_options_id bulk collect
485 INTO x_proj_fp_options_id_array
486 FROM pa_proj_fp_options
487 WHERE project_id = p_project_id and fin_plan_type_id = p_fin_plan_type_id;
488
489 FORALL i IN x_proj_fp_options_id_array.first..x_proj_fp_options_id_array.last
490 DELETE from pa_fp_elements
491 WHERE proj_fp_options_id = x_proj_fp_options_id_array(i);
492
493 -- delete from PA_FP_TXN_CURRENCIES
494 delete from pa_fp_txn_currencies tc
495 where tc.proj_fp_options_id in (select o.proj_fp_options_id
496 from pa_proj_fp_options o
497 where o.project_id = p_project_id and --Replaced project_id with p_project_id
498 --for bug 2740553
499 o.fin_plan_type_id = p_fin_plan_type_id);
500
501 --For Bug 2976168. Should delete the records from pa_fp_excluded_elements also
502
503 IF P_PA_DEBUG_MODE = 'Y' THEN
504 pa_debug.g_err_stage:= 'About to delete from pa_fp_excluded_elements';
505 pa_debug.write('delete_plan_type_from_project : PA_FIN_PLAN_TYPE_GLOBAL',pa_debug.g_err_stage,3);
506 END IF;
507
508 /* Using proj_fp_options_id join instead of directly using project_id, fin_plan_type_id to take
509 advantage of pa_fp_excluded_index_u1 index on pa_fp_excluded_elements */
510
511 DELETE
512 FROM pa_fp_excluded_elements ee
513 WHERE ee.proj_fp_options_id IN (SELECT pfo.proj_fp_options_id
514 FROM pa_proj_fp_options pfo
515 WHERE pfo.project_id = p_project_id
516 AND pfo.fin_plan_type_id=p_fin_plan_type_id);
517
518 IF P_PA_DEBUG_MODE = 'Y' THEN
519 pa_debug.g_err_stage:= To_char(SQL%ROWCOUNT) || ' records deleted.';
520 pa_debug.write('delete_plan_type_from_project : PA_FIN_PLAN_TYPE_GLOBAL',pa_debug.g_err_stage,3);
521 END IF;
522
523 --Bug#11776072 - Addition starts
524 open get_rbs_version_id_cur(p_project_id,p_fin_plan_type_id);
525 fetch get_rbs_version_id_cur into l_rbs_version_id;
526 close get_rbs_version_id_cur;
527 --Bug#11776072 - Addition end
528
529 -- finally, delete from PA_PROJ_FP_OPTIONS
530 delete from pa_proj_fp_options
531 where project_id = p_project_id and
532 fin_plan_option_level_code = 'PLAN_TYPE' and
533 fin_plan_type_id = p_fin_plan_type_id;
534
535 --Bug#11776072 - Addition starts
536 IF l_rbs_version_id is not null THEN
537
538 open check_association_cur(l_rbs_version_id,p_project_id);
539 fetch check_association_cur into l_exists_association;
540 close check_association_cur;
541
542 IF l_exists_association is null or l_exists_association <> 'X' THEN
543
544 open get_rbs_header_id_cur(l_rbs_version_id);
545 fetch get_rbs_header_id_cur into l_rbs_header_id;
546 close get_rbs_header_id_cur;
547
548 UPDATE pa_rbs_prj_assignments
549 SET fp_usage_flag='N'
550 WHERE project_id=p_project_id
551 AND rbs_version_id=l_rbs_version_id
552 AND rbs_header_id=l_rbs_header_id
553 AND fp_usage_flag='Y';
554
555 END IF;
556
557 END IF;
558 --Bug#11776072 - Addition end
559
560 x_return_status := FND_API.G_RET_STS_SUCCESS;
561 pa_debug.reset_err_stack;
562
563 exception
564 when others then
565 rollback to PA_FP_DELETE_PLAN_TYPE;
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 x_msg_count := 1;
568 x_msg_data := SQLERRM;
569 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPE_GLOBAL',
570 p_procedure_name => 'delete_plan_type_from_project');
571 pa_debug.reset_err_stack;
572 raise FND_API.G_EXC_UNEXPECTED_ERROR;
573 END delete_plan_type_from_project;
574
575
576 -- CREATED FOR FP L
577 -- Created by: Danny Lai
578 -- Creation Date: 05/21/03
579 -- SUMMARY: Given a plan type id, this function returns one of the following:
580 -- FORECAST - if the plan type is a forecast plan class
581 -- APPROVED_BUDGET - if the plan type is an approved budget plan class
582 -- NON_APPROVED_BUDGET - if the plan type is a non-approved budget plan class
583 -- This is used for FUNCTION SECURITY checks in Budgeting/Forecasting OA pages
584 FUNCTION plantype_to_planclass
585 (p_project_id IN pa_proj_fp_options.project_id%TYPE,
586 p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE)
587 return VARCHAR2 is
588 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
589 l_approved_cost_pt_flag pa_fin_plan_types_b.approved_cost_plan_type_flag%TYPE;
590 l_approved_rev_pt_flag pa_fin_plan_types_b.approved_rev_plan_type_flag%TYPE;
591 l_return_value VARCHAR2(80);
592 BEGIN
593 select plan_class_code
594 into l_plan_class_code
595 from pa_fin_plan_types_b
596 where fin_plan_type_id = p_fin_plan_type_id;
597 select approved_cost_plan_type_flag,
598 approved_rev_plan_type_flag
599 into l_approved_cost_pt_flag,
600 l_approved_rev_pt_flag
601 from pa_proj_fp_options
602 where project_id = p_project_id and
603 fin_plan_type_id = p_fin_plan_type_id and
604 fin_plan_option_level_code = 'PLAN_TYPE';
605 if l_approved_cost_pt_flag = 'Y' or l_approved_rev_pt_flag = 'Y' then
606 l_return_value := 'APPROVED_BUDGET';
607 else
608 if l_plan_class_code = 'FORECAST' then
609 l_return_value := 'FORECAST';
610 else
611 l_return_value := 'NON_APPROVED_BUDGET';
612 end if;
613 end if;
614 /*
615 if l_plan_class_code = 'FORECAST' then
616 l_return_value := 'FORECAST';
617 else
618 if l_approved_cost_pt_flag = 'Y' or l_approved_rev_pt_flag = 'Y' then
619 l_return_value := 'APPROVED_BUDGET';
620 else
621 l_return_value := 'NON_APPROVED_BUDGET';
622 end if;
623 end if;
624 */
625 return l_return_value;
626 EXCEPTION
627 when NO_DATA_FOUND then
628 return 'INVALID_PLAN_TYPE';
629 when others then
630 return 'INVALID_PLAN_TYPE';
631 END plantype_to_planclass;
632
633
634 -- CREATED FOR FP L
635 -- Created by: Danny Lai
636 -- Creation Date: 05/21/03
637 -- SUMMARY: Given a budget version id, this function returns one of the following:
638 -- FORECAST - if the version is a forecast plan class
639 -- APPROVED_BUDGET - if the version is an approved budget plan class
640 -- NON_APPROVED_BUDGET - if the version is a non-approved budget plan class
641 -- This is used for FUNCTION SECURITY checks in Budgeting/Forecasting OA pages
642 FUNCTION planversion_to_planclass
643 (p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE)
644 return VARCHAR2 is
645 l_return_value VARCHAR2(80);
646 BEGIN
647 return l_return_value;
648 EXCEPTION
649 when NO_DATA_FOUND then
650 return 'INVALID_PLAN_TYPE';
651 when others then
652 return 'INVALID_PLAN_TYPE';
653 END planversion_to_planclass;
654
655 END pa_fin_plan_type_global;
[Home] [Help]