[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.2.12000000.3 2007/07/10 10:30:30 paljain 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,
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
115 x_org_project_flag OUT NOCOPY pa_project_types_all.org_project_flag%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_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
126 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
127 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
128
129 l_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE;
130 l_fin_plan_type_code pa_fin_plan_types_b.fin_plan_type_code%TYPE;
131 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
132 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
133 l_report_labor_hrs_from_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE;
134 l_derive_margin_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
135 l_org_project_flag pa_project_types_all.org_project_flag%TYPE;
136 l_currency_code pa_projects_all.projfunc_currency_code%TYPE;
137 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
138
139 l_msg_count NUMBER := 0;
140 l_msg_data VARCHAR2(2000);
141 l_data VARCHAR2(2000);
142 l_msg_index_out NUMBER;
143
144 cursor wbs_errored_versions_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_fin_plan_type_id and
149 plan_processing_code = 'WUE' and
150 process_update_wbs_flag = 'Y';
151 wbs_errored_versions_rec wbs_errored_versions_csr%ROWTYPE;
152
153 begin
154 IF P_PA_DEBUG_MODE = 'Y' THEN
155 pa_debug.init_err_stack('pa_fin_plan_maint_ver_global.Maintain_Versions_Init');
156 END IF;
157 x_msg_count := 0;
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159
160 -- get AUTO BASELINED FLAG
161 x_auto_baseline_flag :=
162 Pa_Fp_Control_Items_Utils.IsFpAutoBaselineEnabled(p_project_id);
163
164 -- bug 2768332: get APPROVED REVENUE FLAG
165 select
166 fin_plan_preference_code,
167 margin_derived_from_code,
168 report_labor_hrs_from_code,
169 nvl(approved_rev_plan_type_flag, 'N')
170 into
171 l_fin_plan_pref_code,
172 l_derive_margin_from_code,
173 l_report_labor_hrs_from_code,
174 x_ar_flag
175 from
176 pa_proj_fp_options
177 where
178 proj_fp_options_id=p_fin_plan_options_id;
179 if (l_fin_plan_pref_code is null) then
180 l_msg_count := l_msg_count + 1;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 else
183 x_fin_plan_pref_code := l_fin_plan_pref_code;
184 x_derive_margin_from_code := l_derive_margin_from_code;
185 x_report_labor_hrs_code := l_report_labor_hrs_from_code;
186 end if;
187
188 -- populate x_plan_type_processing_code;
189 open wbs_errored_versions_csr;
190 fetch wbs_errored_versions_csr into wbs_errored_versions_rec;
191 if wbs_errored_versions_csr%ROWCOUNT > 0 then
192 x_plan_type_processing_code := 'WUE'; -- simulate error
193 else
194 x_plan_type_processing_code := 'WUS';
195 end if;
196 close wbs_errored_versions_csr;
197
198 select
199 p.projfunc_currency_code,
200 p.project_currency_code,
201 NVL(pt.org_project_flag, 'N')
202 into
203 l_currency_code,
204 l_proj_currency_code,
205 l_org_project_flag
206 from
207 pa_projects_all p,
208 pa_project_types_all pt
209 where
210 p.project_id=p_project_id and
211 p.project_type=pt.project_type and
212 p.org_id = pt.org_id; -- R12 MOAC 4447573: nvl(p.org_id, -99) = nvl(pt.org_id, -99)
213
214 if (l_currency_code is null) or (l_proj_currency_code is null) then
215 l_msg_count := l_msg_count + 1;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 else
218 x_currency_code := l_currency_code;
219 x_proj_currency_code := l_proj_currency_code;
220 end if;
221 x_org_project_flag := l_org_project_flag;
222
223 select
224 name
225 into
226 l_fin_plan_type_name
227 from
228 pa_fin_plan_types_tl
229 where
230 fin_plan_type_id = p_fin_plan_type_id
231 and language = USERENV('LANG');
232
233 if (l_fin_plan_type_name is null) then
234 l_msg_count := l_msg_count +1;
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 else
237 x_fin_plan_type_name := l_fin_plan_type_name;
238 end if;
239
240 /* ================================================
241 Code added to select plan_type_code so that Maintain
242 Versions page can distinguish between ORG_FORECAST and
243 non-ORG_FORECAST.
244 Code added to select plan_class_code so that Maintain
245 Versions page can set "Return to" link appropriately
246 ================================================ */
247 select fin_plan_type_code,
248 plan_class_code
249 into l_fin_plan_type_code,
250 l_plan_class_code
251 from pa_fin_plan_types_b
252 where fin_plan_type_id = p_fin_plan_type_id;
253
254 x_fin_plan_type_code := l_fin_plan_type_code;
255 -- x_fin_plan_class_code := l_plan_class_code;
256 x_fin_plan_class_code :=
257 pa_fin_plan_type_global.plantype_to_planclass
258 (p_project_id, p_fin_plan_type_id);
259 if l_msg_count > 0 then
260 x_msg_count := 1;
261 x_msg_data := SQLERRM;
262 pa_debug.reset_err_stack;
263 return;
264 end if;
265
266 pa_debug.reset_err_stack;
267
268 exception
269 when others then
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 x_msg_count := 1;
272 x_msg_data := SQLERRM;
273 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
274 p_procedure_name => 'Maintain_Versions_Init');
275 raise FND_API.G_EXC_UNEXPECTED_ERROR;
276 end Maintain_Versions_Init;
277
278
279 procedure Create_Working_Copy
280 (p_project_id IN pa_budget_versions.project_id%TYPE,
281 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
282 p_copy_mode IN VARCHAR2,
283 p_adj_percentage IN NUMBER DEFAULT 0,
284 p_calling_module IN VARCHAR2 DEFAULT PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_ORG_FORECAST,
285 px_target_version_id IN OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
286 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
287 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
288 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
289
290 l_version_name VARCHAR2(80);
291
292 -- error-handling variables
293 l_return_status VARCHAR2(1);
294 l_msg_count NUMBER;
295 l_msg_data VARCHAR2(1000);
296 l_msg_index_out NUMBER;
297
298 BEGIN
299 SAVEPOINT create_working_copy;
300 pa_fin_plan_pub.Copy_Version
301 (p_project_id => p_project_id,
302 p_source_version_id => p_source_version_id,
303 p_copy_mode => p_copy_mode,
304 p_calling_module => p_calling_module,
305 px_target_version_id => px_target_version_id,
306 x_return_status => l_return_status,
307 x_msg_count => l_msg_count,
308 x_msg_data => l_msg_data);
309 if l_return_status = FND_API.G_RET_STS_SUCCESS then
310 -- prepend 'Copy: ' to version name
311 select version_name
312 into l_version_name
313 from pa_budget_versions
314 where budget_version_id = px_target_version_id;
315 FND_MESSAGE.SET_NAME('PA','PA_FP_COPY_MESSAGE');
316 l_version_name:= FND_MESSAGE.GET || ': ' || l_version_name;
317 -- bug 3139862 the maximum value version_name column can hold is 60 characters
318 -- It could be that after appending "copy: " the version name exceeds 60 char
319 -- So, the version_name should be truncated
320 l_version_name := substr(l_version_name, 1, 60);
321 update pa_budget_versions
322 set version_name = l_version_name
323 where budget_version_id = px_target_version_id;
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325 else
326 rollback to create_working_copy;
327 x_return_status := FND_API.G_RET_STS_ERROR;
328 x_msg_count := FND_MSG_PUB.Count_Msg;
329 if x_msg_count = 1 then
330 PA_INTERFACE_UTILS_PUB.get_messages
331 (p_encoded => FND_API.G_TRUE,
332 p_msg_index => 1,
333 p_data => x_msg_data,
334 p_msg_index_out => l_msg_index_out);
335 end if;
336 return;
337 end if; -- status=SUCCESS
338 exception
339 when others then
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
341 x_msg_count := 1;
342 x_msg_data := SQLERRM;
343 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
344 p_procedure_name => 'Create_Working_Copy');
345 raise FND_API.G_EXC_UNEXPECTED_ERROR;
346 END Create_Working_Copy;
347
348
349 PROCEDURE Resubmit_Concurrent_Process
350 (p_project_id IN pa_projects_all.project_id%TYPE,
351 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
352 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
353 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
354 IS
355 l_structure_version_id NUMBER;
356
357 -- error-handling variables
358 l_return_status VARCHAR2(1);
359 l_msg_count NUMBER;
360 l_msg_data VARCHAR2(1000);
361 l_msg_index_out NUMBER;
362
363 BEGIN
364 IF P_PA_DEBUG_MODE = 'Y' THEN
365 pa_debug.init_err_stack('pa_fin_plan_maint_ver_global.Maintain_Versions_Init');
366 END IF;
367 x_msg_count := 0;
368 x_return_status := FND_API.G_RET_STS_SUCCESS;
369
370 -- retrieve l_structure_version_id
371 l_structure_version_id :=
372 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id);
373 PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
374 (p_project_id => p_project_id,
375 p_structure_version_id => l_structure_version_id,
376 x_return_status => l_return_status,
377 x_msg_count => l_msg_count,
378 x_msg_data => l_msg_data);
379 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 x_msg_count := FND_MSG_PUB.Count_Msg;
382 if x_msg_count = 1 then
383 PA_INTERFACE_UTILS_PUB.get_messages
384 (p_encoded => FND_API.G_TRUE,
385 p_msg_index => 1,
386 p_data => x_msg_data,
387 p_msg_index_out => l_msg_index_out);
388 end if;
389 end if;
390 return;
391 exception
392 when others then
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 x_msg_count := 1;
395 x_msg_data := SQLERRM;
396 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fin_plan_maint_ver_global',
397 p_procedure_name => 'Resubmit_Concurrent_Process');
398 raise FND_API.G_EXC_UNEXPECTED_ERROR;
399 END Resubmit_Concurrent_Process;
400
401 END pa_fin_plan_maint_ver_global;