[Home] [Help]
PACKAGE BODY: APPS.PA_FP_SHORTCUTS_PKG
Source
1 PACKAGE BODY pa_fp_shortcuts_pkg AS
2 /* $Header: PAFPSHPB.pls 120.3 2006/06/12 06:25:26 nkumbi noship $ */
3 p_pa_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4 l_module VARCHAR2(50) := 'pa_fp_shortcuts_pkg';
5 /* Calling Module : Project Home shortcut Financial dummy page
6 Budgeting and Forecasting page
7 When called from dummy page, this pakcage identify the
8 plan type and returns the budget versiond id along with the URL.
9 When called from Budgeting and Forecasting page the plan type id is
10 always passed. This API checks the validity of the plan type and the option
11 selected and returns the budget version id and the URL */
12 /* 07/13/2005 dlai - added parameter p_same_org_id_flag for R12 MOAC effort to remove
13 * dependency on pa_fp_org_fcst_utils.same_org_id
14 */
15 PROCEDURE identify_plan_version_id(
16 p_project_id IN pa_projects_all.project_id%TYPE,
17 p_function_code IN VARCHAR2,
18 p_context IN VARCHAR2 DEFAULT NULL,
19 p_user_id IN NUMBER,
20 p_same_org_id_flag IN VARCHAR2, -- Bug 5276024: Making this field mandatory -- DEFAULT 'N',
21 px_fin_plan_type_id IN OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
22 x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
23 x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE, --File.Sql.39 bug 4440895
25 x_plan_processing_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
26 x_proj_fp_option_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
27 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
29 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
30 l_version_type pa_budget_versions.version_type%type;
31 l_plan_class_code pa_fin_plan_types_b.plan_class_code%type;
32 l_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%type;
33 l_edit_in_excel_flag VARCHAR2(1) := 'N';
34 l_no_of_fcst_plan_types NUMBER;
35 l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%type;
36 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(2000);
39 l_data VARCHAR2(2000);
40 l_msg_index_out NUMBER;
41 l_cost_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%type;
42 l_rev_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%type;
43 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
44 l_temp_pref_code pa_proj_fp_options.fin_plan_preference_code%type;
45 l_same_org_id VARCHAR2(1);
46 l_bdgt_version_id pa_budget_versions.budget_version_id%TYPE; -- Added for bug 4089561
47 l_approved_rev_plan_type_flag VARCHAR2(1);
48
49
50 BEGIN
51 FND_MSG_PUB.initialize;
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 IF P_PA_DEBUG_MODE = 'Y' THEN
55 pa_debug.init_err_stack('PA_FP_SHORTCUTS_PKG.identify_plan_version_id');
56 pa_debug.write( x_module => l_module,
57 x_msg => 'proj id :'||p_project_id||' fn code :'||p_function_code,
58 x_log_level => 3);
59 pa_debug.write( x_module => l_module,
60 x_msg => 'context :'||p_context||' uid :'||p_user_id,
61 x_log_level => 3);
62 pa_debug.write( x_module => l_module,
63 x_msg => 'ptype id :'||px_fin_plan_type_id,
64 x_log_level => 3);
65 END IF;
66 l_same_org_id := p_same_org_id_flag;
67 --Added for bug 4117017.
68 IF p_function_code IN ( 'PA_FP_EDIT_REV_BUDGET', 'PA_FP_EDIT_REV_BUDGET_EXCEL', 'PA_FP_EDIT_COST_BUDGET', 'PA_FP_EDIT_COST_BUDGET_EXCEL',
69 'PA_FP_EDIT_REV_FCST', 'PA_FP_EDIT_REV_FCST_EXCEL', 'PA_FP_EDIT_COST_FCST', 'PA_FP_EDIT_COST_FCST_EXCEL' ) THEN
70 if l_same_org_id = 'N' then
71 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
72 || '&paProjectId=' || p_project_id
73 || '&pMsg=PA_CROSSOU_NO_UPDATE';
74 RETURN;
75 END if;
76 END IF;
77
78 --If the financial structure is not enabled for the project then the budget creation/updation
79 --should not be possible
80 IF pa_project_structure_utils.get_fin_struc_ver_id( p_project_id => p_project_id) IS NULL THEN
81
82 IF P_PA_DEBUG_MODE = 'Y' THEN
83 pa_debug.write( x_module => l_module,
84 x_msg => 'Financial structure is not enabled for the project '||p_project_id,
85 x_log_level => 5);
86 END IF;
87
88 --Now the user should be re-directed to Budgets and Forecasts Page where the error message will be
89 --displayed
90 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275';
91
92 IF p_context IS NOT NULL THEN
93 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
94 END IF;
95 IF p_pa_debug_mode = 'Y' THEN
96 PA_DEBUG.Reset_Err_stack;
97 END IF;
98 RETURN;
99
100 END IF;
101
102
103 /* bug 2959269 check for auto baseline enabled projects. If any of the Revenue
104 shortcut option is selected in Project Home page, then error should be
105 raised in the Budgets and Forecasts page as editing the Revenue version is
106 not allowed for these types or projects. */
107
108 l_baseline_funding_flag := 'N';
109
110 BEGIN
111 SELECT NVL(Baseline_Funding_Flag,'N')
112 INTO
113 l_baseline_funding_flag
114 FROM
115 Pa_Projects_All WHERE Project_Id = p_project_id;
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 x_return_status := FND_API.G_RET_STS_ERROR;
119 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
120 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
121 IF fnd_msg_pub.count_msg = 1 THEN
122 PA_INTERFACE_UTILS_PUB.Get_Messages (
123 p_encoded => FND_API.G_TRUE,
124 p_msg_index => 1,
125 p_msg_count => 1 ,
126 p_msg_data => l_msg_data ,
127 p_data => l_data,
128 p_msg_index_out => l_msg_index_out );
129 x_msg_data := l_data;
130 x_msg_count := 1;
131 ELSE
132 x_msg_count := fnd_msg_pub.count_msg;
133 END IF;
134 IF p_pa_debug_mode = 'Y' THEN
135 PA_DEBUG.Reset_Err_stack;
136 END IF;
137 RETURN;
138 END;
139
140 --Added the code to allow editing of the version in case the revenue version is not of an approved plan type. This will happen when Edit Revenue
141 -- is chosen from the Budgets and Forecasts page.
142
143 IF p_context IS NULL THEN
144
145 SELECT NVL(approved_rev_plan_type_flag,'N')
146 INTO l_approved_rev_plan_type_flag
147 FROM pa_proj_fp_options
148 WHERE Project_Id = p_project_id
149 AND fin_plan_type_id = px_fin_plan_type_id
150 AND fin_plan_version_id IS NULL
151 AND fin_plan_option_level_code = 'PLAN_TYPE';
152
153 ELSE
154 l_approved_rev_plan_type_flag := 'Y';
155 END IF;
156
157 IF l_baseline_funding_flag = 'Y' AND l_approved_rev_plan_type_flag = 'Y' AND
158 p_function_code IN ( 'PA_FP_EDIT_REV_BUDGET',
159 'PA_FP_EDIT_REV_BUDGET_EXCEL' ) THEN
160 /* setting url for Budgets and Forecasts page */
161
162 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
163 -- || '&paProjectId=' || p_project_id
164 || '&pMsg=PA_FP_AB_REV_SH_OPT';
165
166 IF p_context IS NOT NULL THEN
167 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
168 END IF;
169 IF p_pa_debug_mode = 'Y' THEN
170 PA_DEBUG.Reset_Err_stack;
171 END IF;
172 RETURN;
173
174 END IF;
175 /* bug 2959269 */
176
177 l_fin_plan_type_id := px_fin_plan_type_id;
178
179 IF p_function_code IN ( 'PA_FP_EDIT_COST_BUDGET',
180 'PA_FP_EDIT_REV_BUDGET',
181 'PA_FP_EDIT_COST_BUDGET_EXCEL',
182 'PA_FP_EDIT_REV_BUDGET_EXCEL',
183 'PJI_VIEW_BDGT_TASK_SUMMARY') THEN
184 l_plan_class_code := 'BUDGET';
185 IF p_function_code IN ( 'PA_FP_EDIT_COST_BUDGET',
186 'PA_FP_EDIT_COST_BUDGET_EXCEL' ) THEN
187 l_version_type := 'COST';
188 ELSIF p_function_code IN ( 'PA_FP_EDIT_REV_BUDGET',
189 'PA_FP_EDIT_REV_BUDGET_EXCEL' ) THEN
190 l_version_type := 'REVENUE';
191 ELSIF p_function_code = 'PJI_VIEW_BDGT_TASK_SUMMARY' THEN
192 l_version_type := 'BOTH';
193 END IF;
194 END IF;
195 IF p_function_code IN ( 'PA_FP_EDIT_COST_FCST',
196 'PA_FP_EDIT_REV_FCST',
197 'PA_FP_EDIT_COST_FCST_EXCEL',
198 'PA_FP_EDIT_REV_FCST_EXCEL',
199 'PJI_VIEW_FCST_TASK_SUMMARY') THEN
200 l_plan_class_code := 'FORECAST';
201 IF p_function_code IN ( 'PA_FP_EDIT_COST_FCST',
202 'PA_FP_EDIT_COST_FCST_EXCEL' ) THEN
203 l_version_type := 'COST';
204 ELSIF p_function_code IN ( 'PA_FP_EDIT_REV_FCST',
205 'PA_FP_EDIT_REV_FCST_EXCEL' ) THEN
206 l_version_type := 'REVENUE';
207 ELSIF p_function_code = 'PJI_VIEW_FCST_TASK_SUMMARY' THEN
208 l_version_type := 'BOTH';
209 END IF;
210 END IF;
211 IF p_function_code like '%EXCEL' THEN
212 l_edit_in_excel_flag := 'Y';
213 END IF;
214 IF px_fin_plan_type_id IS NULL THEN
215 IF l_version_type = 'BOTH' THEN
216
217 IF l_plan_class_code = 'BUDGET' THEN
218 IF P_PA_DEBUG_MODE = 'Y' THEN
219 pa_debug.write( x_module => l_module,
220 x_msg => 'calling get_app_budget_pt_id api',
221 x_log_level => 3);
222 END IF;
223
224 pa_fp_shortcuts_pkg.get_app_budget_pt_id(
225 p_project_id => p_project_id,
226 p_version_type => 'COST',
227 p_context => p_context,
228 p_function_code => p_function_code,
229 x_fin_plan_type_id => l_cost_fin_plan_type_id,
230 x_redirect_url => x_redirect_url,
231 x_return_status => x_return_status,
232 x_msg_count => x_msg_count,
233 x_msg_data => x_msg_data );
234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
235 IF p_pa_debug_mode = 'Y' THEN
236 PA_DEBUG.Reset_Err_stack;
237 END IF;
238 RETURN;
239 END IF;
240
241 pa_fp_shortcuts_pkg.get_app_budget_pt_id(
242 p_project_id => p_project_id,
243 p_version_type => 'REVENUE',
244 p_context => p_context,
245 p_function_code => p_function_code,
246 x_fin_plan_type_id => l_rev_fin_plan_type_id,
247 x_redirect_url => x_redirect_url,
248 x_return_status => x_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data );
251 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
252 IF p_pa_debug_mode = 'Y' THEN
253 PA_DEBUG.Reset_Err_stack;
254 END IF;
255 RETURN;
256 END IF;
257
258 IF l_cost_fin_plan_type_id is null AND
259 l_rev_fin_plan_type_id is null THEN
260 /* setting url for Budgets and forecasts page */
261 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
262 || '&pMsg=PA_FP_NO_AB_PLAN_TYPE';
263
264 IF p_context IS NOT NULL THEN
265 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
266 END IF;
267 IF p_pa_debug_mode = 'Y' THEN
268 PA_DEBUG.Reset_Err_stack;
269 END IF;
270 RETURN;
271 END IF;
272
273 ELSIF l_plan_class_code = 'FORECAST' THEN
274 IF P_PA_DEBUG_MODE = 'Y' THEN
275 pa_debug.write( x_module => l_module,
276 x_msg => 'calling get_fcst_plan_type_id api',
277 x_log_level => 3);
278 END IF;
279 pa_fp_shortcuts_pkg.get_fcst_plan_type_id(
280 p_project_id => p_project_id,
281 p_version_type => 'COST',
282 p_context => p_context,
283 p_function_code => p_function_code,
284 x_fin_plan_type_id => l_cost_fin_plan_type_id,
285 x_redirect_url => x_redirect_url,
286 x_return_status => x_return_status,
287 x_msg_count => x_msg_count,
288 x_msg_data => x_msg_data );
289 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
290 IF p_pa_debug_mode = 'Y' THEN
291 PA_DEBUG.Reset_Err_stack;
292 END IF;
293 RETURN;
294 END IF;
295
296 pa_fp_shortcuts_pkg.get_fcst_plan_type_id(
297 p_project_id => p_project_id,
298 p_version_type => 'REVENUE',
299 p_context => p_context,
300 p_function_code => p_function_code,
301 x_fin_plan_type_id => l_rev_fin_plan_type_id,
302 x_redirect_url => x_redirect_url,
303 x_return_status => x_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data );
306 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
307 IF p_pa_debug_mode = 'Y' THEN
308 PA_DEBUG.Reset_Err_stack;
309 END IF;
310 RETURN;
311 END IF;
312
313 IF l_cost_fin_plan_type_id is null AND
314 l_rev_fin_plan_type_id is null THEN
315 /* setting url for Budgets and forecasts page */
316 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
317 || '&pMsg=PA_FP_NO_FCST_PLAN_TYPE';
318
319 IF p_context IS NOT NULL THEN
320 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
321 END IF;
322 IF p_pa_debug_mode = 'Y' THEN
323 PA_DEBUG.Reset_Err_stack;
324 END IF;
325 RETURN;
326 END IF;
327
328 END IF; -- l_plan_class_code
329
330 ELSE -- l_version_type
331
332 IF l_plan_class_code = 'BUDGET' THEN
333 IF P_PA_DEBUG_MODE = 'Y' THEN
334 pa_debug.write( x_module => l_module,
335 x_msg => 'calling get_app_budget_pt_id api',
336 x_log_level => 3);
337 END IF;
338
339 pa_fp_shortcuts_pkg.get_app_budget_pt_id(
340 p_project_id => p_project_id,
341 p_version_type => l_version_type,
342 p_context => p_context,
343 p_function_code => p_function_code,
344 x_fin_plan_type_id => l_fin_plan_type_id,
345 x_redirect_url => x_redirect_url,
346 x_return_status => x_return_status,
347 x_msg_count => x_msg_count,
348 x_msg_data => x_msg_data );
349 px_fin_plan_type_id := l_fin_plan_type_id;
350 IF x_redirect_url IS NOT NULL OR
351 x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
352 IF p_pa_debug_mode = 'Y' THEN
353 PA_DEBUG.Reset_Err_stack;
354 END IF;
355 RETURN;
356 END IF;
357 ELSIF l_plan_class_code = 'FORECAST' THEN
358 IF P_PA_DEBUG_MODE = 'Y' THEN
359 pa_debug.write( x_module => l_module,
360 x_msg => 'calling get_fcst_plan_type_id api',
361 x_log_level => 3);
362 END IF;
363 pa_fp_shortcuts_pkg.get_fcst_plan_type_id(
364 p_project_id => p_project_id,
365 p_version_type => l_version_type,
366 p_context => p_context,
367 p_function_code => p_function_code,
368 x_fin_plan_type_id => l_fin_plan_type_id,
369 x_redirect_url => x_redirect_url,
370 x_return_status => x_return_status,
371 x_msg_count => x_msg_count,
372 x_msg_data => x_msg_data );
373 px_fin_plan_type_id := l_fin_plan_type_id;
374 IF x_redirect_url IS NOT NULL OR
375 x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
376 IF p_pa_debug_mode = 'Y' THEN
377 PA_DEBUG.Reset_Err_stack;
378 END IF;
379 RETURN;
380 END IF;
381 END IF;
382 END IF; -- l_version_type
383 ELSE
384 IF P_PA_DEBUG_MODE = 'Y' THEN
385 pa_debug.write( x_module => l_module,
386 x_msg => 'plan type id passed, validating plan type pref code',
387 x_log_level => 3);
388 END IF;
389
390 SELECT po.fin_plan_preference_code INTO l_fin_plan_preference_code
391 FROM pa_proj_fp_options po
392 WHERE po.project_id = p_project_id AND
393 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
394 po.fin_plan_type_id = px_fin_plan_type_id;
395
396 IF l_version_type = 'COST' AND
397 l_fin_plan_preference_code = 'REVENUE_ONLY' THEN
398 /* setting url for Budgets and forecasts page */
399 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
400 -- || '&paProjectId=' || p_project_id
401 || '&pMsg=PA_FP_REV_ONLY_PT';
402
403 IF p_context IS NOT NULL THEN
404 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
405 END IF;
406 IF p_pa_debug_mode = 'Y' THEN
407 PA_DEBUG.Reset_Err_stack;
408 END IF;
409 RETURN;
410 ELSIF l_version_type = 'REVENUE' AND
411 l_fin_plan_preference_code = 'COST_ONLY' THEN
412 /* setting url for Budgets and forecasts page */
413 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
414 -- || '&paProjectId=' || p_project_id
415 || '&pMsg=PA_FP_COST_ONLY_PT';
416
417 IF p_context IS NOT NULL THEN
418 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
419 END IF;
420 IF p_pa_debug_mode = 'Y' THEN
421 PA_DEBUG.Reset_Err_stack;
422 END IF;
423 RETURN;
424 END IF;
425 END IF;
426
427 IF P_PA_DEBUG_MODE = 'Y' THEN
428 pa_debug.write( x_module => l_module,
429 x_msg => 'getting current working version',
430 x_log_level => 3);
431 END IF;
432
433 IF l_version_type = 'BOTH' THEN
434 IF l_cost_fin_plan_type_id is NOT NULL THEN
435 /* Bug 3658139: Getting the preference_code to check for the
436 * case of approved budget with COST_AND_REV_SAME.
437 */
438 IF P_PA_DEBUG_MODE = 'Y' THEN
439 pa_debug.write( x_module => l_module,
440 x_msg => 'getting the preference code',
441 x_log_level => 3);
442 END IF;
443 BEGIN
444 SELECT fin_plan_preference_code
445 INTO l_temp_pref_code
446 FROM pa_proj_fp_options
447 WHERE project_id = p_project_id
448 AND fin_plan_type_id = l_cost_fin_plan_type_id
449 AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
450 EXCEPTION
451 WHEN OTHERS THEN
452 RAISE;
453 END;
454 IF l_temp_pref_code = 'COST_AND_REV_SAME' THEN
455 pa_fin_plan_utils.get_curr_working_version_info(
456 p_project_id => p_project_id,
457 p_fin_plan_type_id => l_cost_fin_plan_type_id,
458 p_version_type => 'ALL',
459 x_fp_options_id => l_fp_options_id,
460 x_fin_plan_version_id => x_budget_version_id,
461 x_return_status => x_return_status,
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data );
464
465 l_bdgt_version_id := x_budget_version_id; /*Added for bug 4089561. If its a 'COST_AND_REV_SAME' case then
466 paRevContextVersionId should also be set equal to x_budget_version_id derived above.*/
467 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
468 IF p_pa_debug_mode = 'Y' THEN
469 PA_DEBUG.Reset_Err_stack;
470 END IF;
471 RETURN;
472 END IF;
473 ELSE
474 pa_fin_plan_utils.get_curr_working_version_info(
475 p_project_id => p_project_id,
476 p_fin_plan_type_id => l_cost_fin_plan_type_id,
477 p_version_type => 'COST',
478 x_fp_options_id => l_fp_options_id,
479 x_fin_plan_version_id => x_budget_version_id,
480 x_return_status => x_return_status,
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data );
483
484 l_bdgt_version_id := -99; /*Added for bug 4089561. If its not 'COST_AND_REV_SAME' case then
485 paRevContextVersionId should be set equal to -99*/
486 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
487 IF p_pa_debug_mode = 'Y' THEN
488 PA_DEBUG.Reset_Err_stack;
489 END IF;
490 RETURN;
491 END IF;
492 END IF;
493 IF x_budget_version_id is NOT NULL THEN
494 /* setting url for View Plan page */
495 IF l_plan_class_code = 'BUDGET' THEN --'PJI_VIEW_BDGT_TASK_SUMMARY' THEN -- Changed hkulkarn
496 x_redirect_url := 'OA.jsp?page=/oracle/apps/pji/viewplan/reporting/webui/VPBudgetTaskSumPG'
497 ||'&paProjectId=' || p_project_id
498 ||'&paFinTypeId=' || l_cost_fin_plan_type_id
499 ||'&paCstContextVersionId=' || x_budget_version_id
500 ||'&paRevContextVersionId=' || l_bdgt_version_id; --changed for bug 4089561
501
502 IF p_pa_debug_mode = 'Y' THEN
503 PA_DEBUG.Reset_Err_stack;
504 END IF;
505 RETURN;
506 ELSIF l_plan_class_code = 'FORECAST' THEN --'PJI_VIEW_FCST_TASK_SUMMARY' THEN -- Changed hkulkarn
507 x_redirect_url := 'OA.jsp?page=/oracle/apps/pji/viewplan/reporting/webui/ForecastTaskSummaryPG'
508 ||'&paProjectId=' || p_project_id
509 ||'&paFinTypeId=' || l_cost_fin_plan_type_id
510 ||'&paCstContextVersionId=' || x_budget_version_id
511 ||'&paRevContextVersionId=' || l_bdgt_version_id; --changed for bug 4089561
512
513 IF p_pa_debug_mode = 'Y' THEN
514 PA_DEBUG.Reset_Err_stack;
515 END IF;
516 RETURN;
517 END IF;
518 END IF; -- x_budget_version_id
519
520 END IF; -- l_cost_fin_plan_type_id
521
522 IF x_budget_version_id is NULL AND
523 l_rev_fin_plan_type_id is NOT NULL THEN
524
525 pa_fin_plan_utils.get_curr_working_version_info(
526 p_project_id => p_project_id,
527 p_fin_plan_type_id => l_rev_fin_plan_type_id,
528 p_version_type => 'REVENUE',
529 x_fp_options_id => l_fp_options_id,
530 x_fin_plan_version_id => x_budget_version_id,
531 x_return_status => x_return_status,
532 x_msg_count => x_msg_count,
533 x_msg_data => x_msg_data );
534
535 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
536 IF p_pa_debug_mode = 'Y' THEN
537 PA_DEBUG.Reset_Err_stack;
538 END IF;
539 RETURN;
540 END IF;
541
542 IF x_budget_version_id is NOT NULL THEN
543 /* setting url for View Plan page */
544 IF l_plan_class_code = 'BUDGET' THEN --'PJI_VIEW_BDGT_TASK_SUMMARY' THEN -- Changed hkulkarn
545 x_redirect_url := 'OA.jsp?page=/oracle/apps/pji/viewplan/reporting/webui/VPBudgetTaskSumPG'
546 ||'&paProjectId=' || p_project_id
547 ||'&paFinTypeId=' || l_rev_fin_plan_type_id
548 ||'&paCstContextVersionId=-99'
549 ||'&paRevContextVersionId=' || x_budget_version_id;
550
551 IF p_pa_debug_mode = 'Y' THEN
552 PA_DEBUG.Reset_Err_stack;
553 END IF;
554 RETURN;
555 ELSIF l_plan_class_code = 'FORECAST' THEN --'PJI_VIEW_FCST_TASK_SUMMARY' THEN -- Changed hkulkarn
556 x_redirect_url := 'OA.jsp?page=/oracle/apps/pji/viewplan/reporting/webui/ForecastTaskSummaryPG'
557 ||'&paProjectId=' || p_project_id
558 ||'&paFinTypeId=' || l_rev_fin_plan_type_id
559 ||'&paCstContextVersionId=-99'
560 ||'&paRevContextVersionId=' || x_budget_version_id;
561
562 IF p_pa_debug_mode = 'Y' THEN
563 PA_DEBUG.Reset_Err_stack;
564 END IF;
565 RETURN;
566 END IF;
567 END IF; -- x_budget_version_id
568
569 END IF; -- l_rev_fin_plan_type_id
570
571 IF x_budget_version_id is null THEN
572 /* setting url for Budgets and Forecasts page */
573 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
574 || '&pMsg=PA_FP_NO_CW_VER';
575
576 IF p_context IS NOT NULL THEN
577 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
578 END IF;
579 IF p_pa_debug_mode = 'Y' THEN
580 PA_DEBUG.Reset_Err_stack;
581 END IF;
582 RETURN;
583
584 END IF; -- x_budget_version_id
585
586 ELSE
587
588 pa_fp_shortcuts_pkg.get_cw_version(
589 p_project_id => p_project_id,
590 p_plan_class_code => l_plan_class_code,
591 p_version_type => l_version_type,
592 p_fin_plan_type_id => px_fin_plan_type_id,
593 p_edit_in_excel_Flag => l_edit_in_excel_Flag,
594 p_user_id => p_user_id,
595 p_context => p_context,
596 x_budget_version_id => x_budget_version_id,
597 x_redirect_url => x_redirect_url,
598 x_request_id => x_request_id,
599 x_plan_processing_code => x_plan_processing_code,
600 x_proj_fp_option_id => x_proj_fp_option_id,
601 x_return_status => x_return_status,
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data );
604
605
606 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
607 IF p_pa_debug_mode = 'Y' THEN
608 PA_DEBUG.Reset_Err_stack;
609 END IF;
610 RETURN;
611 END IF;
612 -- S.N. hkulkarn
613 IF p_pa_debug_mode = 'Y' THEN
614 PA_DEBUG.Reset_Err_stack;
615 END IF;
616 -- E.N. hkulkarn
617
618 END IF; -- l_version_type
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF p_pa_debug_mode = 'Y' THEN
623 PA_DEBUG.Reset_Err_stack;
624 END IF;
625
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_SHORTCUTS_PKG',
628 p_procedure_name => 'IDENTIFY_PLAN_VERSION_ID',
629 p_error_text => SUBSTRB(SQLERRM,1,240));
630
631 fnd_msg_pub.count_and_get(p_count => x_msg_count,
632 p_data => x_msg_data);
633 END identify_plan_version_id;
634
635 /* This API returns the approved budget plan type id based on the shortcut
636 option selected in the Project Home. If the approved budget plan type does not
637 exist, then the URL will be returned with the error message and the information
638 for page. */
639
640 PROCEDURE get_app_budget_pt_id(
641 p_project_id IN pa_projects_all.project_id%TYPE,
642 p_version_type IN pa_budget_versions.version_type%TYPE,
643 p_context IN VARCHAR2,
644 p_function_code IN VARCHAR2 DEFAULT NULL,
645 x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
646 x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
647 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
648 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
649 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
650 CURSOR approved_budget_csr IS
651 SELECT pt.fin_plan_type_id,
652 po.fin_plan_preference_code
653 FROM pa_proj_fp_options po,
654 pa_fin_plan_types_b pt
655 WHERE po.project_id = p_project_id AND
656 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
657 po.fin_plan_type_id = pt.fin_plan_type_id AND
658 (pt.approved_cost_plan_type_flag = 'Y' OR
659 pt.approved_rev_plan_type_flag = 'Y');
660
661 approved_budget_rec approved_budget_csr%ROWTYPE;
662
663
664 CURSOR approved_cost_csr IS
665 SELECT pt.fin_plan_type_id,
666 po.fin_plan_preference_code
667 FROM pa_proj_fp_options po,
668 pa_fin_plan_types_b pt
669 WHERE po.project_id = p_project_id AND
670 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
671 po.fin_plan_type_id = pt.fin_plan_type_id AND
672 po.fin_plan_preference_code <> 'REVENUE_ONLY' AND
673 pt.approved_cost_plan_type_flag = 'Y';
674
675 approved_cost_rec approved_cost_csr%ROWTYPE;
676
677 CURSOR approved_revenue_csr IS
678 SELECT pt.fin_plan_type_id,
679 po.fin_plan_preference_code
680 FROM pa_proj_fp_options po,
681 pa_fin_plan_types_b pt
682 WHERE po.project_id = p_project_id AND
683 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
684 po.fin_plan_type_id = pt.fin_plan_type_id AND
685 po.fin_plan_preference_code <> 'COST_ONLY' AND
686 pt.approved_rev_plan_type_flag = 'Y';
687
688 approved_revenue_rec approved_revenue_csr%ROWTYPE;
689
690 BEGIN
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692 IF P_PA_DEBUG_MODE = 'Y' THEN
693 pa_debug.init_err_stack('PA_FP_SHORTCUTS_PKG.get_app_budget_pt_id');
694 pa_debug.write( x_module => l_module,
695 x_msg => 'checking for approved budget',
696 x_log_level => 3);
697 END IF;
698 OPEN approved_budget_csr;
699 FETCH approved_budget_csr INTO approved_budget_rec;
700 IF approved_budget_csr%NOTFOUND THEN
701 IF p_function_code = 'PJI_VIEW_BDGT_TASK_SUMMARY' THEN
702 NULL;
703 IF p_pa_debug_mode = 'Y' THEN
704 PA_DEBUG.Reset_Err_stack;
705 END IF;
706 RETURN;
707 ELSE
708 /* setting url for Budgets and Forecasts page */
709 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
710 -- || '&paProjectId=' || p_project_id
711 || '&pMsg=PA_FP_NO_AB_PLAN_TYPE';
712
713 IF p_context IS NOT NULL THEN
714 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
715 END IF;
716 CLOSE approved_budget_csr;
717 IF p_pa_debug_mode = 'Y' THEN
718 PA_DEBUG.Reset_Err_stack;
719 END IF;
720 RETURN;
721 END IF;
722 END IF;
723 CLOSE approved_budget_csr;
724
725 IF p_version_type = 'COST' THEN
726 -- COST BUDGET
727 -- Validate: Approved Budget Plan type allows for cost numbers
728 IF P_PA_DEBUG_MODE = 'Y' THEN
729 pa_debug.write( x_module => l_module,
730 x_msg => 'checking for cost approved budget',
731 x_log_level => 3);
732 END IF;
733 OPEN approved_cost_csr;
734 FETCH approved_cost_csr INTO approved_cost_rec;
735 IF approved_cost_csr%NOTFOUND THEN
736 /* setting url for Budgets and Forecasts page */
737 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
738 -- || '&paProjectId=' || p_project_id
739 || '&pMsg=PA_FP_REV_ONLY_PT';
740
741 IF p_context IS NOT NULL THEN
742 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
743 END IF;
744 IF p_pa_debug_mode = 'Y' THEN
745 PA_DEBUG.Reset_Err_stack;
746 END IF;
747 RETURN;
748 ELSE
749 x_fin_plan_type_id := approved_cost_rec.fin_plan_type_id;
750 END IF;
751 CLOSE approved_cost_csr;
752 ELSIF p_version_type = 'REVENUE' THEN
753 IF P_PA_DEBUG_MODE = 'Y' THEN
754 pa_debug.write( x_module => l_module,
755 x_msg => 'checking for revenue approved budget',
756 x_log_level => 3);
757 END IF;
758 OPEN approved_revenue_csr;
759 FETCH approved_revenue_csr INTO approved_revenue_rec;
760 IF approved_revenue_csr%NOTFOUND THEN
761 /* setting url for Budgets and Forecasts page */
762 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
763 -- || '&paProjectId=' || p_project_id
764 || '&pMsg=PA_FP_COST_ONLY_PT';
765
766 IF p_context IS NOT NULL THEN
767 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
768 END IF;
769 CLOSE approved_revenue_csr;
770 IF p_pa_debug_mode = 'Y' THEN
771 PA_DEBUG.Reset_Err_stack;
772 END IF;
773 RETURN;
774 ELSE
775 x_fin_plan_type_id := approved_revenue_rec.fin_plan_type_id;
776 END IF;
777 CLOSE approved_revenue_csr;
778 END IF;
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 IF p_pa_debug_mode = 'Y' THEN
783 PA_DEBUG.Reset_Err_stack;
784 END IF;
785
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_SHORTCUTS_PKG',
788 p_procedure_name => 'GET_APP_BUDGET_PT_ID',
789 p_error_text => SUBSTRB(SQLERRM,1,240));
790
791 fnd_msg_pub.count_and_get(p_count => x_msg_count,
792 p_data => x_msg_data);
793 END get_app_budget_pt_id;
794
795 /* This API identifies the FORECAST budget plan type based on the shortcut
796 option selected in the Project Home. If the plan type does not exist, then
797 the URL will be returned with appropriate information. */
798 PROCEDURE get_fcst_plan_type_id(
799 p_project_id IN pa_projects_all.project_id%TYPE,
800 p_version_type IN pa_budget_versions.version_type%TYPE,
801 p_context IN VARCHAR2,
802 p_function_code IN VARCHAR2 DEFAULT NULL,
803 x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
804 x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
805 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
806 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
807 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
808
809 CURSOR primary_budget_csr IS
810 SELECT pt.fin_plan_type_id,
811 po.fin_plan_preference_code
812 FROM pa_proj_fp_options po,
813 pa_fin_plan_types_b pt
814 WHERE po.project_id = p_project_id AND
815 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
816 po.fin_plan_type_id = pt.fin_plan_type_id AND
817 (pt.primary_cost_forecast_flag = 'Y' OR
818 pt.primary_rev_forecast_flag = 'Y');
819
820 primary_budget_rec primary_budget_csr%ROWTYPE;
821
822
823 CURSOR primary_cost_csr IS
824 SELECT pt.fin_plan_type_id,
825 po.fin_plan_preference_code
826 FROM pa_proj_fp_options po,
827 pa_fin_plan_types_b pt
828 WHERE po.project_id = p_project_id AND
829 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
830 po.fin_plan_type_id = pt.fin_plan_type_id AND
831 po.fin_plan_preference_code <> 'REVENUE_ONLY' AND
832 pt.primary_cost_forecast_flag = 'Y';
833
834 primary_cost_rec primary_cost_csr%ROWTYPE;
835
836 CURSOR primary_revenue_csr IS
837 SELECT pt.fin_plan_type_id,
838 po.fin_plan_preference_code
839 FROM pa_proj_fp_options po,
840 pa_fin_plan_types_b pt
841 WHERE po.project_id = p_project_id AND
842 po.fin_plan_option_level_code = 'PLAN_TYPE' AND
843 po.fin_plan_type_id = pt.fin_plan_type_id AND
844 po.fin_plan_preference_code <> 'COST_ONLY' AND
845 pt.primary_rev_forecast_flag = 'Y';
846
847 primary_revenue_rec primary_revenue_csr%ROWTYPE;
848
849 BEGIN
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851 IF P_PA_DEBUG_MODE = 'Y' THEN
852 pa_debug.init_err_stack('PA_FP_SHORTCUTS_PKG.get_fcst_plan_type_id');
853 pa_debug.write( x_module => l_module,
854 x_msg => 'checking for primary forecast plan type',
855 x_log_level => 3);
856 END IF;
857 OPEN primary_budget_csr;
858 FETCH primary_budget_csr INTO primary_budget_rec;
859 IF primary_budget_csr%NOTFOUND THEN
860 IF p_function_code = 'PJI_VIEW_FCST_TASK_SUMMARY' THEN
861 NULL;
862 IF p_pa_debug_mode = 'Y' THEN
863 PA_DEBUG.Reset_Err_stack;
864 END IF;
865 RETURN;
866 ELSE
867 /* setting url for Budgets and Forecasts page */
868 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
869 -- || '&paProjectId=' || p_project_id
870 || '&pMsg=PA_FP_NO_FCST_PLAN_TYPE';
871
872 IF p_context IS NOT NULL THEN
873 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
874 END IF;
875 CLOSE primary_budget_csr;
876 IF p_pa_debug_mode = 'Y' THEN
877 PA_DEBUG.Reset_Err_stack;
878 END IF;
879 RETURN;
880 END IF;
881 END IF;
882 CLOSE primary_budget_csr;
883 IF p_version_type = 'COST' THEN
884 -- COST BUDGET
885 -- Validate: primary Budget Plan type allows for cost numbers
886 IF P_PA_DEBUG_MODE = 'Y' THEN
887 pa_debug.write( x_module => l_module,
888 x_msg => 'checking for primary cost forecast',
889 x_log_level => 3);
890 END IF;
891 OPEN primary_cost_csr;
892 FETCH primary_cost_csr INTO primary_cost_rec;
893 IF primary_cost_csr%NOTFOUND THEN
894 /* setting url for Budgets and Forecasts page */
895 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
896 -- || '&paProjectId=' || p_project_id
897 || '&pMsg=PA_FP_REV_ONLY_PT';
898
899 IF p_context IS NOT NULL THEN
900 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
901 END IF;
902 IF p_pa_debug_mode = 'Y' THEN
903 PA_DEBUG.Reset_Err_stack;
904 END IF;
905 RETURN;
906 ELSE
907 x_fin_plan_type_id := primary_cost_rec.fin_plan_type_id;
908 END IF;
909 CLOSE primary_cost_csr;
910 ELSIF p_version_type = 'REVENUE' THEN
911 IF P_PA_DEBUG_MODE = 'Y' THEN
912 pa_debug.write( x_module => l_module,
913 x_msg => 'checking for primary revenue forecast',
914 x_log_level => 3);
915 END IF;
916 OPEN primary_revenue_csr;
917 FETCH primary_revenue_csr INTO primary_revenue_rec;
918 IF primary_revenue_csr%NOTFOUND THEN
919 /* setting url for Budgets and Forecasts page */
920 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
921 -- || '&paProjectId=' || p_project_id
922 || '&pMsg=PA_FP_COST_ONLY_PT';
923
924 IF p_context IS NOT NULL THEN
925 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
926 END IF;
927 CLOSE primary_revenue_csr;
928 IF p_pa_debug_mode = 'Y' THEN
929 PA_DEBUG.Reset_Err_stack;
930 END IF;
931 RETURN;
932 ELSE
933 x_fin_plan_type_id := primary_revenue_rec.fin_plan_type_id;
934 END IF;
935 CLOSE primary_revenue_csr;
936 END IF;
937 EXCEPTION
938 WHEN OTHERS THEN
939 IF p_pa_debug_mode = 'Y' THEN
940 PA_DEBUG.Reset_Err_stack;
941 END IF;
942
943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
944 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_SHORTCUTS_PKG',
945 p_procedure_name => 'GET_FCST_PLAN_TYPE_ID',
946 p_error_text => SUBSTRB(SQLERRM,1,240));
947
948 fnd_msg_pub.count_and_get(p_count => x_msg_count,
949 p_data => x_msg_data);
950 END get_fcst_plan_type_id;
951
952 /* This API returns the current working budget version id for the given plan type.
953 If there is no current workgin version available, then URL will be set with the
954 error message and appropriate page information. If the CW version is available, but
955 if the version is either locked or in submitted status, then the URL will be set
956 for Budgeting and Forecasting page with appropriate error message. */
957
958 PROCEDURE get_cw_version( p_project_id IN pa_projects_all.project_id%TYPE,
959 p_plan_class_code in pa_fin_plan_types_b.plan_class_Code%type,
960 p_version_type IN pa_budget_versions.version_type%TYPE,
961 p_fin_plan_type_id in pa_fin_plan_types_b.fin_plan_type_id%TYPE,
962 p_edit_in_excel_Flag IN varchar2,
963 p_user_id in number,
964 p_context IN VARCHAR2,
965 x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%type, --File.Sql.39 bug 4440895
966 x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
967 x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE, --File.Sql.39 bug 4440895
968 x_plan_processing_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
969 x_proj_fp_option_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
970 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
971 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
972 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
973 l_locked_by_person_id pa_budget_versions.locked_by_person_id%type;
974 l_budget_status_code pa_budget_versions.budget_status_code%type;
975 l_rec_ver_number pa_budget_versions.record_version_number%type;
976 l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%type;
977 l_person_id NUMBER;
978 l_resource_id NUMBER;
979 l_resource_name VARCHAR2(200);
980 l_webadi_enabled_flag VARCHAR2(30);
981 l_msg_code varchar2(30);
982 l_fp_opt_id pa_proj_fp_options.proj_fp_options_id%type;
983 /* l_process_wbs_flag pa_budget_versions.process_update_wbs_flag%TYPE; * 3604167 */
984 l_editable_flag VARCHAR2(1) := 'Y';
985 BEGIN
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987 l_webadi_enabled_flag := nvl(FND_PROFILE.value('PA_FP_WEBADI_ENABLE'), 'N');
988
989 SAVEPOINT lock_cw_version_PH;
990
991 SELECT fin_plan_preference_code,
992 proj_fp_options_id
993 INTO l_fin_plan_preference_code,l_fp_opt_id
994 FROM pa_proj_fp_options
995 WHERE
996 project_id = p_project_id AND
997 fin_plan_option_level_code = 'PLAN_TYPE' AND
998 fin_plan_type_id = p_fin_plan_type_id;
999
1000 IF P_PA_DEBUG_MODE = 'Y' THEN
1001 pa_debug.init_err_stack('PA_FP_SHORTCUTS_PKG.get_cw_version');
1002 pa_debug.write( x_module => l_module,
1003 x_msg => 'selecting the CW version',
1004 x_log_level => 3);
1005 END IF;
1006
1007 x_proj_fp_option_id := l_fp_opt_id;
1008
1009 BEGIN
1010 Select bv.budget_Version_id,
1011 bv.locked_by_person_id,bv.budget_status_code,record_version_number,bv.request_id,bv.plan_processing_code
1012 -- nvl(bv.process_update_wbs_flag,'N')
1013 INTO x_budget_version_id,
1014 l_locked_by_person_id,
1015 l_budget_status_code,
1016 l_rec_ver_number,
1017 x_request_id,
1018 x_plan_processing_code
1019 -- l_process_wbs_flag
1020 FROM pa_budget_versions bv
1021 WHERE project_id = p_project_id AND
1022 fin_plan_type_id = p_fin_plan_type_id AND
1023 current_working_Flag ='Y' AND
1024 version_type IN (p_version_type,'ALL');
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 IF P_PA_DEBUG_MODE = 'Y' THEN
1028 pa_debug.write( x_module => l_module,
1029 x_msg => 'no CW version',
1030 x_log_level => 3);
1031 END IF;
1032 IF p_version_type = 'COST' THEN
1033 IF l_fin_plan_preference_code IN ( 'COST_ONLY',
1034 'COST_AND_REV_SEP' ) THEN
1035 l_msg_code := 'PA_FP_NO_CW_VER_COST';
1036 ELSIF l_fin_plan_preference_code = 'COST_AND_REV_SAME' THEN
1037 l_msg_code := 'PA_FP_NO_CW_VER_ALL';
1038 END IF;
1039 END IF;
1040 IF p_version_type = 'REVENUE' THEN
1041 IF l_fin_plan_preference_code IN ( 'REVENUE_ONLY',
1042 'COST_AND_REV_SEP' ) THEN
1043 l_msg_code := 'PA_FP_NO_CW_VER_REV';
1044 ELSIF l_fin_plan_preference_code = 'COST_AND_REV_SAME' THEN
1045 l_msg_code := 'PA_FP_NO_CW_VER_ALL';
1046 END IF;
1047 END IF;
1048 /* setting url for Create Version page */
1049 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_CV_LAYOUT&akRegionApplicationId=275'
1050 || '&paFinPlanTypeId=' || p_fin_plan_type_id
1051 || '&pMsg='||l_msg_Code
1052 || '&paFinPlanOptionsId='||l_fp_opt_id;
1053
1054 IF p_context IS NOT NULL THEN
1055 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1056 END IF;
1057 IF l_fin_plan_preference_code = 'COST_AND_REV_SEP' THEN
1058 x_redirect_url := x_redirect_url ||'&paCostOrRev='||
1059 initcap(p_version_type);
1060 END IF;
1061 IF p_pa_debug_mode = 'Y' THEN
1062 PA_DEBUG.Reset_Err_stack;
1063 END IF;
1064 RETURN;
1065 END;
1066
1067 IF l_budget_Status_code = 'S' THEN
1068 /* setting url for Budgets and Forecasts page */
1069 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
1070 -- || '&paProjectId=' || p_project_id
1071 || '&pMsg=PA_FP_VERSION_SUBMITTED';
1072
1073 IF p_context IS NOT NULL THEN
1074 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1075 END IF;
1076 IF p_pa_debug_mode = 'Y' THEN
1077 PA_DEBUG.Reset_Err_stack;
1078 END IF;
1079 RETURN;
1080 END IF;
1081
1082 IF P_PA_DEBUG_MODE = 'Y' THEN
1083 pa_debug.write( x_module => l_module,
1084 x_msg => 'calling Pa_fin_plan_utils.Check_if_plan_type_editable',
1085 x_log_level => 3);
1086 END IF;
1087
1088 PA_FIN_PLAN_UTILS.CHECK_IF_PLAN_TYPE_EDITABLE
1089 ( P_project_id => p_project_id,
1090 P_fin_plan_type_id => p_fin_plan_type_id,
1091 P_version_type => p_version_type,
1092 X_editable_flag => l_editable_flag,
1093 X_return_status => x_return_status,
1094 X_msg_count => x_msg_count,
1095 X_msg_data => x_msg_data);
1096
1097 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1098 IF p_pa_debug_mode = 'Y' THEN
1099 PA_DEBUG.Reset_Err_stack;
1100 END IF;
1101 RETURN;
1102 END IF;
1103
1104 IF l_editable_flag = 'N' THEN
1105 /* setting url for Budgets and Forecasts page */
1106 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
1107 || '&pMsg=PA_FP_PLAN_TYPE_NON_EDITABLE';
1108
1109 IF p_context IS NOT NULL THEN
1110 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1111 END IF;
1112 IF p_pa_debug_mode = 'Y' THEN
1113 PA_DEBUG.Reset_Err_stack;
1114 END IF;
1115 RETURN;
1116 END IF;
1117
1118 IF P_PA_DEBUG_MODE = 'Y' THEN
1119 pa_debug.write( x_module => l_module,
1120 x_msg => 'calling PA_COMP_PROFILE_PUB.get_user_info api for person id',
1121 x_log_level => 3);
1122 END IF;
1123 PA_COMP_PROFILE_PUB.GET_USER_INFO
1124 (p_user_id => p_user_id,
1125 x_person_id => l_person_id,
1126 x_resource_id => l_resource_id,
1127 x_resource_name => l_resource_name);
1128
1129 IF l_locked_by_person_id IS NOT NULL AND
1130 l_locked_by_person_id <> l_person_id THEN
1131
1132 /* Bug fix 3079388: if locked for processing, go to Edit Plan page,
1133 where it will be caught.
1134 NOTE: use locked_by_person_id=-98 instead of update_wbs_flag=Y so that this
1135 will catch ALL concurrent process in progress scenarios */
1136 if l_locked_by_person_id = -98 then
1137 /* setting url for Edit Plan page */
1138 x_redirect_url := 'OA.jsp?page=/oracle/apps/pa/finplan/webui/FpEditPlanPG'
1139 || '&paBvId=' || x_budget_version_id
1140 || '&paContextLevel=VERSION'
1141 || '&pMsg=-1';
1142
1143 IF p_context IS NOT NULL THEN
1144 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1145 END IF;
1146 IF p_pa_debug_mode = 'Y' THEN
1147 PA_DEBUG.Reset_Err_stack;
1148 END IF;
1149 RETURN;
1150 else
1151 /* setting url for Budgets and Forecasts page */
1152 x_redirect_url := 'OA.jsp?akRegionCode=PA_FIN_PLAN_LAYOUT&akRegionApplicationId=275'
1153 || '&pMsg=PA_FP_VERSION_LOCKED_BY_USER';
1154
1155 IF p_context IS NOT NULL THEN
1156 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1157 END IF;
1158 IF p_pa_debug_mode = 'Y' THEN
1159 PA_DEBUG.Reset_Err_stack;
1160 END IF;
1161 RETURN;
1162 end if;
1163 END IF;
1164
1165 IF l_locked_by_person_id IS NULL THEN
1166 IF P_PA_DEBUG_MODE = 'Y' THEN
1167 pa_debug.write( x_module => l_module,
1168 x_msg => 'calling pa_fin_plan_pvt.lock_unlock_version api',
1169 x_log_level => 3);
1170 END IF;
1171 pa_fin_plan_pvt.lock_unlock_version
1172 (p_budget_version_id => x_budget_version_id,
1173 p_record_version_number => l_rec_Ver_number,
1174 p_action => 'L',
1175 p_user_id => p_user_id,
1176 p_person_id => l_person_id,
1177 x_return_status => x_return_status,
1178 x_msg_count => x_msg_count,
1179 x_msg_data => x_msg_data);
1180
1181 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1182 IF p_pa_debug_mode = 'Y' THEN
1183 PA_DEBUG.Reset_Err_stack;
1184 END IF;
1185 RETURN;
1186 END IF;
1187
1188 IF P_PA_DEBUG_MODE = 'Y' THEN
1189 pa_debug.write( x_module => l_module,
1190 x_msg => 'version locked successfully.',
1191 x_log_level => 3);
1192 END IF;
1193 END IF;
1194
1195
1196 IF p_edit_in_excel_flag='Y' THEN
1197 IF l_webadi_enabled_flag='Y' THEN
1198 -- FP L build 2: WBS UPDATE VALIDATION CHECK: If undergoing WBS validation,
1199 -- simply redirect to Edit Plan page
1200 /* IF l_process_wbs_flag = 'Y' THEN
1201 ** x_redirect_url = EDIT PLAN
1202 x_redirect_url := 'OA.jsp?page=/oracle/apps/pa/finplan/webui/FpEditPlanPG'||
1203 '&paBvId=' || x_budget_version_id
1204 || '&paContextLevel=VERSION'
1205 || '&pMsg=-1'; ** 2979654: use -1 to eliminate URL persistence
1206 IF p_context IS NOT NULL THEN ** Bug 3079328 **
1207 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1208 END IF;
1209
1210 ELSE ** 3604167 */
1211 x_redirect_url := 'WEBADI';
1212 IF p_pa_debug_mode = 'Y' THEN
1213 PA_DEBUG.Reset_Err_stack;
1214 END IF;
1215 -- END IF;
1216 RETURN;
1217 ELSE
1218 /* setting url for Edit Plan page */
1219 x_redirect_url := 'OA.jsp?page=/oracle/apps/pa/finplan/webui/FpEditPlanPG'
1220 ||'&paBvId=' || x_budget_version_id
1221 || '&paContextLevel=VERSION'
1222 || '&pMsg=PA_FP_WEBADI_NOT_ENABLED';
1223
1224 IF p_context IS NOT NULL THEN
1225 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1226 END IF;
1227 IF p_pa_debug_mode = 'Y' THEN
1228 PA_DEBUG.Reset_Err_stack;
1229 END IF;
1230 RETURN;
1231 END IF;
1232 ELSE
1233 /* setting url for Edit Plan page */
1234 x_redirect_url := 'OA.jsp?page=/oracle/apps/pa/finplan/webui/FpEditPlanPG'||
1235 '&paBvId=' || x_budget_version_id
1236 || '&paContextLevel=VERSION'
1237 || '&pMsg=-1';
1238 -- 2979654: use -1 to eliminate URL persistence
1239 IF p_context IS NOT NULL THEN
1240 x_redirect_url := x_redirect_url ||'&pContext='||p_context;
1241 END IF;
1242 IF p_pa_debug_mode = 'Y' THEN
1243 PA_DEBUG.Reset_Err_stack;
1244 END IF;
1245 RETURN;
1246 END IF;
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 ROLLBACK TO lock_cw_version_PH;
1250 IF p_pa_debug_mode = 'Y' THEN
1251 PA_DEBUG.Reset_Err_stack;
1252 END IF;
1253
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_SHORTCUTS_PKG',
1256 p_procedure_name => 'GET_CW_VERSION',
1257 p_error_text => SUBSTRB(SQLERRM,1,240));
1258
1259 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1260 p_data => x_msg_data);
1261
1262
1263
1264 END get_cw_version;
1265
1266
1267 END pa_fp_shortcuts_pkg;