[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_MAINT_VER_GLOBAL
Source
1 PACKAGE BODY pa_fin_plan_maint_ver_global as
2 /* $Header: PAFPMVGB.pls 120.4.12010000.3 2009/06/25 11:02:08 rthumma ship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_MAINT_VER_GLOBAL
5 Purpose : API's for Org Forecast: Maintain Versions 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 G_PROJECT_ID;
16 end get_project_id;
17
18 function get_fin_plan_type_id return NUMBER is
19 begin
20 return G_FIN_PLAN_TYPE_ID;
21 end get_fin_plan_type_id;
22
23 function get_login_person_id return NUMBER is
24 begin
25 return G_LOGIN_PERSON_ID;
26 end get_login_person_id;
27
28 /* Added for bug 5629469 */
29 /*this function returns the security available to the user for submit/rework/baseline a version*/
30 function get_fin_plan_security(SecurityType IN VARCHAR2) return VARCHAR2 is
31 begin
32 IF(SecurityType = 'Submit') THEN return G_SECURITY_S;
33 ELSIF(SecurityType = 'Rework') THEN return G_SECURITY_R;
34 ELSIF(SecurityType = 'Baseline') THEN return G_SECURITY_B;
35 END IF;
36 end get_fin_plan_security;
37
38
39
40 /* ---------------------------------------------------------------- */ /* ---------------------------------------------------------------- */
41
42 /* Added for bug 5629469 */
43 /*this procedure sets the security available to the user for submit/rework/baseline a version*/
44 procedure set_global_finplan_security
45 ( paFinplanSecType IN VARCHAR2,
46 paFinplanSec IN VARCHAR2 := NULL,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2 ) IS
50 begin
51 x_msg_count := 0;
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 IF(paFinplanSecType = 'Submit') THEN pa_fin_plan_maint_ver_global.G_SECURITY_S := paFinplanSec;
55 ELSIF(paFinplanSecType = 'Rework') THEN pa_fin_plan_maint_ver_global.G_SECURITY_R := paFinplanSec;
56 ELSIF(paFinplanSecType = 'Baseline') THEN pa_fin_plan_maint_ver_global.G_SECURITY_B := paFinplanSec;
57 END IF;
58
59
60 exception
61 when others then
62 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
63 x_msg_count := 1;
64 x_msg_data := SQLERRM;
65 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FIN_PLAN_MAIN_VER_GLOBAL',
66 p_procedure_name => 'set_global_finplan_security');
67
68 end set_global_finplan_security;
69
70
71 procedure set_global_values
72 (p_project_id IN pa_projects_all.project_id%TYPE,
73 p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE,
74 p_user_id IN VARCHAR2,
75 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
76 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
77 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
78
79 l_person_id VARCHAR2(80);
80 l_resource_id VARCHAR2(80);
81 l_resource_name VARCHAR(240); /* Added this line for bug 3456811 */
82 begin
83 x_msg_count := 0;
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85 pa_fin_plan_maint_ver_global.G_PROJECT_ID := p_project_id;
86 pa_fin_plan_maint_ver_global.G_FIN_PLAN_TYPE_ID := p_fin_plan_type_id;
87
88 PA_COMP_PROFILE_PUB.GET_USER_INFO
89 (p_user_id => p_user_id,
90 x_person_id => l_person_id,
91 x_resource_id => l_resource_id, /* Added the ending comma for bug 3456811 */
92 x_resource_name => l_resource_name );/* Added this line for bug 3456811 */
93
94 pa_fin_plan_maint_ver_global.G_LOGIN_PERSON_ID := l_person_id;
95
96 exception
97 when others then
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99 x_msg_count := 1;
100 x_msg_data := SQLERRM;
101 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FIN_PLAN_MAIN_VER_GLOBAL',
102 p_procedure_name => 'set_global_values');
103 end set_global_values;
104 /* ---------------------------------------------------------------- */
105
106 -- HISTORY
107 -- 11/15/2002 - bug fix 2668857: when querying pa_project_types_all, need
108 -- to also use org_id to avoid returning multiple rows
109 procedure Maintain_Versions_Init
110 (p_project_id IN pa_projects_all.project_id%TYPE,
111 p_fin_plan_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE,
115 x_org_project_flag OUT NOCOPY pa_project_types_all.org_project_flag%TYPE, --File.Sql.39 bug 4440895
112 p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE,
113 x_fin_plan_type_name OUT NOCOPY pa_fin_plan_types_tl.name%TYPE, --File.Sql.39 bug 4440895
114 x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
116 x_currency_code OUT NOCOPY pa_projects_all.projfunc_currency_code%TYPE, --File.Sql.39 bug 4440895
117 x_proj_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
118 x_fin_plan_type_code OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_code%TYPE, --File.Sql.39 bug 4440895
119 x_fin_plan_class_code OUT NOCOPY pa_fin_plan_types_b.plan_class_code%TYPE, --File.Sql.39 bug 4440895
120 x_derive_margin_from_code OUT NOCOPY pa_proj_fp_options.margin_derived_from_code%TYPE, --File.Sql.39 bug 4440895
121 x_report_labor_hrs_code OUT NOCOPY pa_proj_fp_options.report_labor_hrs_from_code%TYPE, --File.Sql.39 bug 4440895
122 x_auto_baseline_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
123 x_ar_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
124 x_plan_type_processing_code OUT NOCOPY pa_proj_fp_options.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
125 x_navg_inc_co_code OUT NOCOPY VARCHAR2,--Bug 5845142
126 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
127 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
128 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
129
130 l_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE;
131 l_fin_plan_type_code pa_fin_plan_types_b.fin_plan_type_code%TYPE;
132 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
133 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
134 l_report_labor_hrs_from_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE;
135 l_derive_margin_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
136 l_org_project_flag pa_project_types_all.org_project_flag%TYPE;
137 l_currency_code pa_projects_all.projfunc_currency_code%TYPE;
138 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
139
140 l_msg_count NUMBER := 0;
141 l_msg_data VARCHAR2(2000);
142 l_data VARCHAR2(2000);
143 l_msg_index_out NUMBER;
144
145 cursor wbs_errored_versions_csr is
146 select budget_version_id
147 from pa_budget_versions
148 where project_id = p_project_id and
149 fin_plan_type_id = p_fin_plan_type_id and
150 plan_processing_code = 'WUE' and
151 process_update_wbs_flag = 'Y';
152 wbs_errored_versions_rec wbs_errored_versions_csr%ROWTYPE;
153
154 begin
155 IF P_PA_DEBUG_MODE = 'Y' THEN
156 pa_debug.init_err_stack('pa_fin_plan_maint_ver_global.Maintain_Versions_Init');
157 END IF;
158 x_msg_count := 0;
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 -- get AUTO BASELINED FLAG
162 x_auto_baseline_flag :=
163 Pa_Fp_Control_Items_Utils.IsFpAutoBaselineEnabled(p_project_id);
164
165 -- bug 2768332: get APPROVED REVENUE FLAG
166 select
167 fin_plan_preference_code,
168 margin_derived_from_code,
169 report_labor_hrs_from_code,
170 nvl(approved_rev_plan_type_flag, 'N')
171 into
172 l_fin_plan_pref_code,
173 l_derive_margin_from_code,
174 l_report_labor_hrs_from_code,
175 x_ar_flag
176 from
177 pa_proj_fp_options
178 where
179 proj_fp_options_id=p_fin_plan_options_id;
180 if (l_fin_plan_pref_code is null) then
181 l_msg_count := l_msg_count + 1;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 else
184 x_fin_plan_pref_code := l_fin_plan_pref_code;
185 x_derive_margin_from_code := l_derive_margin_from_code;
186 x_report_labor_hrs_code := l_report_labor_hrs_from_code;
187 end if;
188
189 -- populate x_plan_type_processing_code;
190 open wbs_errored_versions_csr;
191 fetch wbs_errored_versions_csr into wbs_errored_versions_rec;
192 if wbs_errored_versions_csr%ROWCOUNT > 0 then
193 x_plan_type_processing_code := 'WUE'; -- simulate error
194 else
195 x_plan_type_processing_code := 'WUS';
196 end if;
197 close wbs_errored_versions_csr;
198
199 select
200 p.projfunc_currency_code,
201 p.project_currency_code,
202 NVL(pt.org_project_flag, 'N')
203 into
204 l_currency_code,
205 l_proj_currency_code,
206 l_org_project_flag
207 from
208 pa_projects_all p,
209 pa_project_types_all pt
210 where
211 p.project_id=p_project_id and
212 p.project_type=pt.project_type and
213 p.org_id = pt.org_id; -- R12 MOAC 4447573: nvl(p.org_id, -99) = nvl(pt.org_id, -99)
214
215 if (l_currency_code is null) or (l_proj_currency_code is null) then
216 l_msg_count := l_msg_count + 1;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 else
219 x_currency_code := l_currency_code;
220 x_proj_currency_code := l_proj_currency_code;
221 end if;
222 x_org_project_flag := l_org_project_flag;
223
224 select
225 name
226 into
227 l_fin_plan_type_name
228 from
229 pa_fin_plan_types_tl
230 where
231 fin_plan_type_id = p_fin_plan_type_id
232 and language = USERENV('LANG');
233
234 if (l_fin_plan_type_name is null) then
235 l_msg_count := l_msg_count +1;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 else
238 x_fin_plan_type_name := l_fin_plan_type_name;
239 end if;
240
241 /* ================================================
242 Code added to select plan_type_code so that Maintain
243 Versions page can distinguish between ORG_FORECAST and
244 non-ORG_FORECAST.
245 Code added to select plan_class_code so that Maintain
249 plan_class_code
246 Versions page can set "Return to" link appropriately
247 ================================================ */
248 select fin_plan_type_code,
250 into l_fin_plan_type_code,
251 l_plan_class_code
252 from pa_fin_plan_types_b
253 where fin_plan_type_id = p_fin_plan_type_id;
254
255 x_fin_plan_type_code := l_fin_plan_type_code;
256 -- x_fin_plan_class_code := l_plan_class_code;
257 x_fin_plan_class_code :=
258 pa_fin_plan_type_global.plantype_to_planclass
259 (p_project_id, p_fin_plan_type_id);
260 -- Bug 5845142. Please refer to the bug for more details
261 x_navg_inc_co_code:='NONE';
262 IF Pa_Fp_Control_Items_Utils.check_valid_combo
263 ( p_project_id => p_project_id
264 ,p_targ_app_cost_flag => 'N'
265 ,p_targ_app_rev_flag => 'N') = 'N' THEN
266
267 x_navg_inc_co_code:='PA_FP_CANT_INCL_CO_UANPP_AMT';
268
269 END IF;
270 if l_msg_count > 0 then
271 x_msg_count := 1;
272 x_msg_data := SQLERRM;
273 pa_debug.reset_err_stack;
274 return;
275 end if;
276
277 pa_debug.reset_err_stack;
278
279 exception
280 when others then
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 x_msg_count := 1;
283 x_msg_data := SQLERRM;
284 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
285 p_procedure_name => 'Maintain_Versions_Init');
286 raise FND_API.G_EXC_UNEXPECTED_ERROR;
287 end Maintain_Versions_Init;
288
289
290 procedure Create_Working_Copy
291 (p_project_id IN pa_budget_versions.project_id%TYPE,
292 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
293 p_copy_mode IN VARCHAR2,
294 p_adj_percentage IN NUMBER DEFAULT 0,
295 p_calling_module IN VARCHAR2 DEFAULT PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_ORG_FORECAST,
296 px_target_version_id IN OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
297 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
298 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
299 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
300
301 l_version_name VARCHAR2(80);
302
303 -- error-handling variables
304 l_return_status VARCHAR2(1);
305 l_msg_count NUMBER;
306 l_msg_data VARCHAR2(1000);
307 l_msg_index_out NUMBER;
308
309 BEGIN
310 SAVEPOINT create_working_copy;
311 pa_fin_plan_pub.Copy_Version
312 (p_project_id => p_project_id,
313 p_source_version_id => p_source_version_id,
314 p_copy_mode => p_copy_mode,
315 p_calling_module => p_calling_module,
316 px_target_version_id => px_target_version_id,
317 x_return_status => l_return_status,
318 x_msg_count => l_msg_count,
319 x_msg_data => l_msg_data);
320 if l_return_status = FND_API.G_RET_STS_SUCCESS then
321 -- prepend 'Copy: ' to version name
322 select version_name
323 into l_version_name
324 from pa_budget_versions
325 where budget_version_id = px_target_version_id;
326 FND_MESSAGE.SET_NAME('PA','PA_FP_COPY_MESSAGE');
327 l_version_name:= FND_MESSAGE.GET || ': ' || l_version_name;
328 -- bug 3139862 the maximum value version_name column can hold is 60 characters
329 -- It could be that after appending "copy: " the version name exceeds 60 char
330 -- So, the version_name should be truncated
331 l_version_name := substr(l_version_name, 1, 60);
332 update pa_budget_versions
333 set version_name = l_version_name
334 where budget_version_id = px_target_version_id;
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336 else
337 rollback to create_working_copy;
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 x_msg_count := FND_MSG_PUB.Count_Msg;
340 if x_msg_count = 1 then
341 PA_INTERFACE_UTILS_PUB.get_messages
342 (p_encoded => FND_API.G_TRUE,
343 p_msg_index => 1,
344 p_data => x_msg_data,
345 p_msg_index_out => l_msg_index_out);
346 end if;
347 return;
348 end if; -- status=SUCCESS
349 exception
350 when others then
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 x_msg_count := 1;
353 x_msg_data := SQLERRM;
354 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
355 p_procedure_name => 'Create_Working_Copy');
356 raise FND_API.G_EXC_UNEXPECTED_ERROR;
357 END Create_Working_Copy;
358
359
360 PROCEDURE Resubmit_Concurrent_Process
361 (p_project_id IN pa_projects_all.project_id%TYPE,
362 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
363 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
364 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
365 IS
366 l_structure_version_id NUMBER;
367
368 -- error-handling variables
369 l_return_status VARCHAR2(1);
370 l_msg_count NUMBER;
371 l_msg_data VARCHAR2(1000);
372 l_msg_index_out NUMBER;
373
374 BEGIN
375 IF P_PA_DEBUG_MODE = 'Y' THEN
376 pa_debug.init_err_stack('pa_fin_plan_maint_ver_global.Maintain_Versions_Init');
377 END IF;
378 x_msg_count := 0;
379 x_return_status := FND_API.G_RET_STS_SUCCESS;
380
381 -- retrieve l_structure_version_id
382 l_structure_version_id :=
383 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id);
384 PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
385 (p_project_id => p_project_id,
386 p_structure_version_id => l_structure_version_id,
387 x_return_status => l_return_status,
388 x_msg_count => l_msg_count,
389 x_msg_data => l_msg_data);
390 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 x_msg_count := FND_MSG_PUB.Count_Msg;
393 if x_msg_count = 1 then
394 PA_INTERFACE_UTILS_PUB.get_messages
395 (p_encoded => FND_API.G_TRUE,
396 p_msg_index => 1,
397 p_data => x_msg_data,
398 p_msg_index_out => l_msg_index_out);
399 end if;
400 end if;
401 return;
402 exception
403 when others then
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 x_msg_count := 1;
406 x_msg_data := SQLERRM;
407 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
408 p_procedure_name => 'Resubmit_Concurrent_Process');
409 raise FND_API.G_EXC_UNEXPECTED_ERROR;
410 END Resubmit_Concurrent_Process;
411
412 END pa_fin_plan_maint_ver_global;