[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_CREATE_VER_GLOBAL
Source
1 PACKAGE BODY pa_fin_plan_create_ver_global as
2 /* $Header: PAFPCVGB.pls 120.2 2005/08/19 16:26:03 mwasowic noship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_CREATE_VER_GLOBAL
5 Purpose : API's for Org Forecast: Create Versions Page
6 History :
7 NOTE :
8 End of Comments
9 */
10 /*
11 G_PROJECT_ID NUMBER;
12 G_FIN_PLAN_TYPE_ID NUMBER;
13 G_BUDGET_VERSION_ID NUMBER;
14 */
15 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
16
17 function get_project_id return NUMBER is
18 begin
19 return G_PROJECT_ID;
20 end get_project_id;
21
22 function get_fin_plan_type_id return NUMBER is
23 begin
24 return G_FIN_PLAN_TYPE_ID;
25 end get_fin_plan_type_id;
26
27 function get_budget_version_id return NUMBER is
28 begin
29 return G_BUDGET_VERSION_ID;
30 end get_budget_version_id;
31
32 function get_lookup_planning_level
33 (p_planning_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE)
34 return VARCHAR2 is
35 l_planning_level pa_lookups.meaning%type;
36 BEGIN
37 select meaning
38 into l_planning_level
39 from pa_lookups
40 where lookup_type = 'BUDGET ENTRY LEVEL' and
41 lookup_code = p_planning_level_code;
42 return l_planning_level;
43 END get_lookup_planning_level;
44
45 function get_lookup_time_phase
46 (p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE)
47 return VARCHAR2 is
48 l_time_phase pa_lookups.meaning%type;
49 BEGIN
50 select meaning
51 into l_time_phase
52 from pa_lookups
53 where lookup_type = 'BUDGET TIME PHASED TYPE' and
54 lookup_code = p_time_phased_code;
55 return l_time_phase;
56 END get_lookup_time_phase;
57
58 function get_resource_list_name
59 (p_resource_list_id IN pa_resource_lists.resource_list_id%TYPE)
60 return VARCHAR2 is
61 l_resource_list_name pa_resource_lists_tl.name%type;
62 BEGIN
63 select name
64 into l_resource_list_name
65 from pa_resource_lists
66 where resource_list_id = p_resource_list_id;
67 return l_resource_list_name;
68 END get_resource_list_name;
69
70 /* ==============================================================
71 9/11/02 ADDED FUNCTIONS TO RETRIEVE GL/PA START/END PERIOD
72 NAMES FOR CREATE VERSION PAGE
73 ============================================================== */
74 FUNCTION get_gl_current_start_period
75 (p_project_id IN pa_proj_period_profiles.project_id%TYPE)
76 return VARCHAR2 is
77
78 cursor l_csr is
79 select period_name1
80 from pa_proj_period_profiles
81 where project_id = p_project_id and
82 plan_period_type = 'GL' and
83 current_flag = 'Y';
84 l_rec l_csr%ROWTYPE;
85 BEGIN
86 open l_csr;
87 fetch l_csr into l_rec;
88 if l_csr%NOTFOUND then
89 return null;
90 else
91 return l_rec.period_name1;
92 end if;
93 close l_csr;
94 END get_gl_current_start_period;
95
96
97 FUNCTION get_gl_current_end_period
98 (p_project_id IN pa_proj_period_profiles.project_id%TYPE)
99 return VARCHAR2 is
100 cursor l_csr is
101 select profile_end_period_name
102 from pa_proj_period_profiles
103 where project_id = p_project_id and
104 plan_period_type = 'GL' and
105 current_flag = 'Y';
106 l_rec l_csr%ROWTYPE;
107 BEGIN
108 open l_csr;
109 fetch l_csr into l_rec;
110 if l_csr%NOTFOUND then
111 return null;
112 else
113 return l_rec.profile_end_period_name;
114 end if;
115 close l_csr;
116 END get_gl_current_end_period;
117
118
119 FUNCTION get_pa_current_start_period
120 (p_project_id IN pa_proj_period_profiles.project_id%TYPE)
121 return VARCHAR2 is
122 cursor l_csr is
123 select period_name1
124 from pa_proj_period_profiles
125 where project_id = p_project_id and
126 plan_period_type = 'PA' and
127 current_flag = 'Y';
128 l_rec l_csr%ROWTYPE;
129 BEGIN
130 open l_csr;
131 fetch l_csr into l_rec;
132 if l_csr%NOTFOUND then
133 return null;
134 else
135 return l_rec.period_name1;
136 end if;
137 close l_csr;
138 END get_pa_current_start_period;
139
140
141 FUNCTION get_pa_current_end_period
142 (p_project_id IN pa_proj_period_profiles.project_id%TYPE)
143 return VARCHAR2 is
144 cursor l_csr is
145 select profile_end_period_name
146 from pa_proj_period_profiles
147 where project_id = p_project_id and
148 plan_period_type = 'PA' and
149 current_flag = 'Y';
150 l_rec l_csr%ROWTYPE;
151 BEGIN
152 open l_csr;
153 fetch l_csr into l_rec;
154 if l_csr%NOTFOUND then
155 return null;
156 else
157 return l_rec.profile_end_period_name;
158 end if;
159 close l_csr;
160 END get_pa_current_end_period;
161
162
163
164 procedure set_project_id
165 (p_project_id IN pa_budget_versions.project_id%TYPE,
166 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
167 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
168 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
169 is
170 BEGIN
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 G_PROJECT_ID := p_project_id;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 x_msg_count := 1;
178 x_msg_data := SQLERRM;
179 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_CREATE_VER_GLOBAL',
180 p_procedure_name => 'set_project_id');
181 end set_project_id;
182
183 procedure set_fin_plan_type_id
184 (p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE,
185 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
186 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
187 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
188 is
189 BEGIN
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191 G_FIN_PLAN_TYPE_ID := p_fin_plan_type_id;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 x_msg_count := 1;
197 x_msg_data := SQLERRM;
198 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_CREATE_VER_GLOBAL',
199 p_procedure_name => 'set_fin_plan_type_id');
200 end set_fin_plan_type_id;
201
202 procedure set_budget_version_id
203 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
204 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
205 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
206 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
207 is
208 BEGIN
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 G_BUDGET_VERSION_ID := p_budget_version_id;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 x_msg_count := 1;
216 x_msg_data := SQLERRM;
217 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_CREATE_VER_GLOBAL',
218 p_procedure_name => 'set_budget_version_id');
219 end set_budget_version_id;
220
221 procedure set_global_values
222 (p_project_id IN pa_budget_versions.project_id%TYPE,
223 p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE,
224 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
225 p_user_id IN NUMBER,
226 x_locked_by_user_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
227 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
228 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
229 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
230 is
231 l_return_status VARCHAR2(1);
232 l_msg_count NUMBER(15);
233 l_msg_data VARCHAR2(2000);
234 l_locked_by_person_id pa_budget_versions.locked_by_person_id%TYPE;
235
236 BEGIN
237 IF P_PA_DEBUG_MODE = 'Y' THEN
238 pa_debug.init_err_stack('pa_fin_plan_create_ver_global.set_global_values');
239 END IF;
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241 G_PROJECT_ID := p_project_id;
242 G_FIN_PLAN_TYPE_ID := p_fin_plan_type_id;
243 G_BUDGET_VERSION_ID := p_budget_version_id;
244
245 -- bug 2961541: x_locked_by_user_flag
246 pa_fin_plan_utils.Check_Locked_By_User
247 (p_user_id => p_user_id,
248 p_budget_version_id => p_budget_version_id,
249 x_is_locked_by_userid => x_locked_by_user_flag,
250 x_locked_by_person_id => l_locked_by_person_id,
251 x_return_status => l_return_status,
252 x_msg_count => l_msg_count,
253 x_msg_data => l_msg_data);
254 if l_locked_by_person_id is null then
255 x_locked_by_user_flag := 'Y'; -- unlocked is equivalent to locked by user
256 end if;
257 pa_debug.reset_err_stack;
258 EXCEPTION
259 WHEN OTHERS THEN
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 x_msg_count := 1;
262 x_msg_data := SQLERRM;
263 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_CREATE_VER_GLOBAL',
264 p_procedure_name => 'set_global_values');
265 end set_global_values;
266
267 procedure get_start_end_period
268 (x_period_start_date OUT NOCOPY pa_proj_fp_options.fin_plan_start_date%TYPE, --File.Sql.39 bug 4440895
269 x_period_end_date OUT NOCOPY pa_proj_fp_options.fin_plan_end_date%TYPE, --File.Sql.39 bug 4440895
270 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
271 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
272 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
273 is
274 l_org_fcst_period_type pa_forecasting_options_all.org_fcst_period_type%TYPE;
275 l_period_set_name pa_implementations_all.period_set_name%TYPE;
276 l_act_period_type gl_periods.period_type%TYPE;
277 l_org_projfunc_currency_code gl_sets_of_books.currency_code%TYPE;
278 l_number_of_periods pa_forecasting_options_all.number_of_periods%TYPE;
279 l_org_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
280 l_weighted_or_full_code pa_forecasting_options_all.weighted_or_full_code%TYPE;
281 l_org_project_template_id pa_forecasting_options_all.org_fcst_project_template_id%TYPE;
282 l_org_structure_version_id pa_implementations_all.org_structure_version_id%TYPE;
283 l_fcst_start_date pa_proj_fp_options.fin_plan_start_date%TYPE;
284 l_fcst_end_date pa_proj_fp_options.fin_plan_end_date%TYPE;
285 l_org_id pa_forecasting_options_all.org_id%TYPE;
286 l_return_status VARCHAR2(2000);
287 l_error_msg_code VARCHAR2(30);
288
289 BEGIN
290 pa_fp_org_fcst_utils.get_forecast_option_details
291 (x_fcst_period_type => l_org_fcst_period_type,
292 x_period_set_name => l_period_set_name,
293 x_act_period_type => l_act_period_type,
294 x_org_projfunc_currency_code => l_org_projfunc_currency_code,
295 x_number_of_periods => l_number_of_periods,
296 x_weighted_or_full_code => l_weighted_or_full_code,
297 x_org_proj_template_id => l_org_project_template_id,
298 x_org_structure_version_id => l_org_structure_version_id,
299 x_fcst_start_date => l_fcst_start_date,
300 x_fcst_end_date => l_fcst_end_date,
301 x_org_id => l_org_id,
302 x_return_status => l_return_status,
303 x_err_code => l_error_msg_code);
304 if l_return_status=FND_API.G_RET_STS_SUCCESS then
305 x_period_start_date := l_fcst_start_date;
306 x_period_end_date := l_fcst_end_date;
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308 end if;
309 END get_start_end_period;
310
311 /* ========================================================
312 HISTORY:
313 8/21/02 -- added nvl to org_project_flag query: null --> 'N'
314 8/22/02 -- added x_fin_plan_pref_code
315 9/16/03 -- modified logic for x_plan_class_code: possible values:
316 FORECAST, APPROVED_BUDGET, NON_APPROVED_BUDGET
317 5/25/05 -- added x_approved_budget_flag param
318 ======================================================== */
319 procedure Create_Versions_Init
320 (p_project_id IN pa_projects_all.project_id%TYPE,
321 p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE,
322 x_org_project_flag OUT NOCOPY pa_project_types_all.org_project_flag%TYPE, --File.Sql.39 bug 4440895
323 x_proj_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
324 x_fin_plan_type_code OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_code%TYPE, --File.Sql.39 bug 4440895
325 x_plan_class_code OUT NOCOPY pa_fin_plan_types_b.plan_class_code%TYPE, --File.Sql.39 bug 4440895
326 x_approved_budget_flag OUT NOCOPY pa_proj_fp_options.approved_cost_plan_type_flag%TYPE, --File.Sql.39 bug 4440895
327 x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
328 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
329 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
330 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
331
332 l_org_project_flag pa_project_types_all.org_project_flag%TYPE;
333 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
334 l_fin_plan_type_code pa_fin_plan_types_b.fin_plan_type_code%TYPE;
335 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
336 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
337 l_approved_budget_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
338
339 l_msg_count NUMBER := 0;
340 l_msg_data VARCHAR2(2000);
341 l_data VARCHAR2(2000);
342 l_msg_index_out NUMBER;
343
344 begin
345 IF P_PA_DEBUG_MODE = 'Y' THEN
346 pa_debug.init_err_stack('pa_fin_plan_create_ver_global.Create_Versions_Init');
347 END IF;
348 x_msg_count := 0;
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 select
352 nvl(pt.org_project_flag, 'N')
353 into
354 l_org_project_flag
355 from
356 pa_projects_all p,
357 pa_project_types_all pt
358 where
359 p.project_id=p_project_id and
360 p.project_type=pt.project_type and
361 p.org_id = pt.org_id; -- R12 MOAC 4447573: nvl(p.org_id,-99) = nvl(pt.org_id, -99)
362
363 select fin_plan_type_code,
364 plan_class_code
365 into l_fin_plan_type_code,
366 l_plan_class_code
367 from pa_fin_plan_types_b
368 where fin_plan_type_id = p_fin_plan_type_id;
369
370 select proj_fp_options_id,
371 fin_plan_preference_code,
372 DECODE(fin_plan_preference_code,
373 'COST_ONLY', nvl(approved_cost_plan_type_flag, 'N'),
374 'REVENUE_ONLY', nvl(approved_rev_plan_type_flag, 'N'),
375 DECODE(approved_cost_plan_type_flag,
376 'Y', 'Y',
377 nvl(approved_rev_plan_type_flag, 'N')))
378 into l_proj_fp_options_id,
379 l_fin_plan_pref_code,
380 l_approved_budget_flag
381 from pa_proj_fp_options
382 where project_id = p_project_id and
383 fin_plan_type_id = p_fin_plan_type_id and
384 fin_plan_option_level_code = 'PLAN_TYPE';
385
386 x_org_project_flag := l_org_project_flag;
387 x_fin_plan_type_code := l_fin_plan_type_code;
388 x_proj_fp_options_id := l_proj_fp_options_id;
389 x_approved_budget_flag := l_approved_budget_flag;
390 /* BUG FIX 3144444: new logic for x_plan_class_code for Plan Class Security:
391 possible values: FORECAST, APPROVED_BUDGET, NON_APPROVED_BUDGET
392 */
393 --x_plan_class_code := l_plan_class_code;
394 x_plan_class_code := pa_fin_plan_type_global.plantype_to_planclass
395 (p_project_id, p_fin_plan_type_id);
396 x_fin_plan_pref_code := l_fin_plan_pref_code;
397
398 G_PROJECT_ID := p_project_id;
399 G_FIN_PLAN_TYPE_ID := p_fin_plan_type_id;
400 pa_debug.reset_err_stack;
401
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_create_ver_global',
408 p_procedure_name => 'Create_Versions_Init');
409 end Create_Versions_Init;
410
411
412
413 END pa_fin_plan_create_ver_global;