[Home] [Help]
PACKAGE BODY: APPS.PA_FP_ORG_FCST_UTILS
Source
1 PACKAGE BODY pa_fp_org_fcst_utils as
2 /* $Header: PAFPORUB.pls 120.4 2007/02/06 10:02:46 dthakker ship $ */
3 -- Start of Comments
4 -- Package name : PA_FP_ORG_FCST_UTILS
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 /* 20-Mar-2002 SManivannan Added Procedure Get_Tp_Amount_Type */
11
12 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
13
14 PROCEDURE get_forecast_option_details
15 ( x_fcst_period_type OUT NOCOPY pa_forecasting_options_all.org_fcst_period_type%TYPE --File.Sql.39 bug 4440895
16 ,x_period_set_name OUT NOCOPY pa_implementations_all.period_set_name%TYPE --File.Sql.39 bug 4440895
17 ,x_act_period_type OUT NOCOPY gl_periods.period_type%TYPE --File.Sql.39 bug 4440895
18 ,x_org_projfunc_currency_code OUT NOCOPY gl_sets_of_books.currency_code%TYPE --File.Sql.39 bug 4440895
19 ,x_number_of_periods OUT NOCOPY pa_forecasting_options_all.number_of_periods%TYPE --File.Sql.39 bug 4440895
20 ,x_weighted_or_full_code OUT NOCOPY pa_forecasting_options_all.weighted_or_full_code%TYPE --File.Sql.39 bug 4440895
21 ,x_org_proj_template_id OUT NOCOPY pa_forecasting_options_all.org_fcst_project_template_id%TYPE --File.Sql.39 bug 4440895
22 ,x_org_structure_version_id OUT NOCOPY pa_implementations_all.org_structure_version_id%TYPE --File.Sql.39 bug 4440895
23 ,x_fcst_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
24 ,x_fcst_end_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
25 ,x_org_id OUT NOCOPY pa_implementations_all.org_id%TYPE --File.Sql.39 bug 4440895
26 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 ,x_err_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
28
29 l_org_id pa_implementations_all.org_id%TYPE;
30 l_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
31 l_org_struc_version_id pa_implementations_all.org_structure_version_id%TYPE;
32 l_start_organization pa_implementations_all.start_organization_id%TYPE;
33 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
34 l_fcst_period_type pa_forecasting_options_all.org_fcst_period_type%TYPE;
35 l_start_period_name pa_forecasting_options_all.start_period_name%TYPE;
36 l_number_of_periods pa_forecasting_options_all.number_of_periods%TYPE;
37 l_weighted_or_full_code pa_forecasting_options_all.weighted_or_full_code%TYPE;
38 l_org_proj_template_id pa_forecasting_options_all.org_fcst_project_template_id%TYPE;
39
40 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
41 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
42 l_org_projfunc_currency_code gl_sets_of_books.currency_code%TYPE;
43
44 l_fcst_start_date date;
45 l_fcst_end_date date;
46
47 l_stage number := 0;
48 l_debug_mode VARCHAR2(30);
49
50 CURSOR gl_periods IS
51 SELECT gp.end_date
52 FROM gl_periods gp
53 WHERE gp.period_set_name = l_period_set_name
54 AND gp.period_type = l_accounted_period_type
55 AND gp.start_date >= l_fcst_start_date
56 AND gp.adjustment_period_flag = 'N'
57 ORDER BY gp.start_date;
58
59 CURSOR pa_periods IS
60 SELECT gp.end_date
61 FROM gl_periods gp
62 WHERE gp.period_set_name = l_period_set_name
63 AND gp.period_type = l_pa_period_type
64 AND gp.start_date >= l_fcst_start_date
65 AND gp.adjustment_period_flag = 'N'
66 ORDER BY gp.start_date;
67
68 BEGIN
69 IF P_PA_DEBUG_MODE = 'Y' THEN
70 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_forecast_option_details');
71 END IF;
72
73 --fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
74 l_debug_mode := 'Y';
75
76 IF P_PA_DEBUG_MODE = 'Y' THEN
77 pa_debug.set_process('get_forecast_option_details: ' || 'PLSQL','LOG',l_debug_mode);
78 END IF;
79
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 IF P_PA_DEBUG_MODE = 'Y' THEN
83 pa_debug.write_file('Entered PA_FP_ORG_FCST_UTILS.get_forecast_option_details');
84 pa_debug.write_file('get_forecast_option_details: ' || pa_debug.g_err_stage);
85 END IF;
86 l_stage := 100;
87
88 SELECT nvl(org_id,-99),
89 set_of_books_id,
90 org_structure_version_id,
91 start_organization_id,
92 pa_period_type
93 INTO l_org_id,
94 l_set_of_books_id,
95 l_org_struc_version_id,
96 l_start_organization,
97 l_pa_period_type
98 FROM pa_implementations;
99
100 l_stage := 200;
101
102 SELECT org_fcst_period_type,
103 start_period_name,
104 number_of_periods,
105 weighted_or_full_code,
106 org_fcst_project_template_id
107 INTO l_fcst_period_type,
108 l_start_period_name,
109 l_number_of_periods,
110 l_weighted_or_full_code,
111 l_org_proj_template_id
112 FROM pa_forecasting_options;
113
114 l_stage := 300;
115
116 SELECT period_set_name
117 ,accounted_period_type
118 ,currency_code
119 INTO l_period_set_name
120 ,l_accounted_period_type
121 ,l_org_projfunc_currency_code
122 FROM gl_sets_of_books
123 WHERE set_of_books_id = l_set_of_books_id;
124
125 l_stage := 400;
126
127 IF l_fcst_period_type = 'GL' THEN
128
129 l_stage := 500;
130
131 x_act_period_type := l_accounted_period_type;
132
133 SELECT start_date, end_date
134 INTO l_fcst_start_date, l_fcst_end_date
135 FROM gl_periods
136 WHERE period_set_name = l_period_set_name
137 AND period_type = l_accounted_period_type
138 AND period_name = l_start_period_name
139 AND adjustment_period_flag = 'N';
140
141 l_stage := 600;
142
143 OPEN gl_periods;
144 FOR i IN 1..l_number_of_periods LOOP
145 FETCH gl_periods INTO l_fcst_end_date;
146 END LOOP;
147 CLOSE gl_periods;
148 l_stage := 700;
149 ELSE -- PA period
150 l_stage := 800;
151 x_act_period_type := l_pa_period_type;
152
153 SELECT start_date, end_date
154 INTO l_fcst_start_date, l_fcst_end_date
155 FROM gl_periods
156 WHERE period_set_name = l_period_set_name
157 AND period_type = l_pa_period_type
158 AND period_name = l_start_period_name
159 AND adjustment_period_flag = 'N';
160
161 l_stage := 900;
162
163 OPEN pa_periods;
164 FOR i IN 1..l_number_of_periods LOOP
165 FETCH pa_periods INTO l_fcst_end_date;
166 END LOOP;
167 CLOSE pa_periods;
168 l_stage := 1000;
169
170 END IF;
171
172 x_fcst_period_type := l_fcst_period_type;
173 x_period_set_name := l_period_set_name;
174 x_org_projfunc_currency_code := l_org_projfunc_currency_code;
175 x_number_of_periods := l_number_of_periods;
176 x_weighted_or_full_code := l_weighted_or_full_code;
177 x_org_proj_template_id := l_org_proj_template_id;
178 x_org_structure_version_id := l_org_struc_version_id;
179 x_fcst_start_date := l_fcst_start_date;
180 x_fcst_end_date := l_fcst_end_date;
181 x_org_id := l_org_id;
182
183 IF l_fcst_period_type IS NULL OR
184 l_period_set_name IS NULL OR
185 l_org_projfunc_currency_code IS NULL OR
186 l_number_of_periods IS NULL OR
187 l_weighted_or_full_code IS NULL OR
188 l_org_proj_template_id IS NULL OR
189 l_org_struc_version_id IS NULL OR
190 l_fcst_start_date IS NULL OR
191 l_fcst_end_date IS NULL OR
192 l_org_id IS NULL THEN
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 x_err_code := -1;
195 END IF;
196 IF P_PA_DEBUG_MODE = 'Y' THEN
197 pa_debug.write_file('Leaving PA_FP_ORG_FCST_UTILS.get_forecast_option_details');
198 pa_debug.write_file('get_forecast_option_details: ' || pa_debug.g_err_stage);
199 END IF;
200 pa_debug.reset_err_stack;
201 EXCEPTION
202 WHEN OTHERS THEN
203 FND_MSG_PUB.add_exc_msg(
204 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.get_forecast_options_details'
205 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
206
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 x_err_code := SQLERRM;
209 IF P_PA_DEBUG_MODE = 'Y' THEN
210 pa_debug.write_file('get_forecast_option_details: ' || SQLERRM);
211 END IF;
212 pa_debug.reset_err_stack;
213 END get_forecast_option_details;
214
215 PROCEDURE get_org_project_info
216 ( p_organization_id IN hr_organization_units.organization_id%TYPE
217 := NULL
218 ,x_org_project_id OUT NOCOPY pa_projects_all.project_id%TYPE --File.Sql.39 bug 4440895
219 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
220 ,x_err_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
221 ) is
222 l_stage NUMBER := 0;
223 l_debug_mode VARCHAR2(30);
224
225 BEGIN
226 IF P_PA_DEBUG_MODE = 'Y' THEN
227 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_org_project_info');
228 END IF;
229
230 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
231 l_debug_mode := NVL(l_debug_mode, 'Y');
232
233 IF P_PA_DEBUG_MODE = 'Y' THEN
234 pa_debug.set_process('get_org_project_info: ' || 'PLSQL','LOG',l_debug_mode);
235 END IF;
236
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 l_stage := 100;
240 pa_debug.g_err_stage := 'Entered PA_FP_ORG_FCST_UTILS.get_org_project_info for Org ['||p_organization_id ||
241 ']';
242 IF P_PA_DEBUG_MODE = 'Y' THEN
243 pa_debug.write_file('get_org_project_info: ' || pa_debug.g_err_stage);
244 END IF;
245
246 /* bug 3106741 though template_flag is a nullable column care has been taken to populate the column always.
247 so removed NVL for performance reasons
248 */
249 SELECT pp.project_id
250 INTO x_org_project_id
251 FROM pa_projects pp
252 WHERE pp.carrying_out_organization_id = p_organization_id
253 AND pp.project_type in ( SELECT ppt.project_type
254 FROM pa_project_types ppt
255 WHERE ppt.org_project_flag = 'Y')
256 AND pp.template_flag = 'N';
257 -- bug 3106741 AND NVL(pp.template_flag,'N') = 'N';
258
259 pa_debug.g_err_stage := 'Leaving PA_FP_ORG_FCST_UTILS.get_org_project_info';
260 IF P_PA_DEBUG_MODE = 'Y' THEN
261 pa_debug.write_file('get_org_project_info: ' || pa_debug.g_err_stage);
262 END IF;
263 pa_debug.reset_err_stack;
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 x_org_project_id := -9999;
267 IF P_PA_DEBUG_MODE = 'Y' THEN
268 pa_debug.write_file('get_org_project_info: ' || 'Org Project Not Found for organization Id = '
269 ||p_organization_id);
270 END IF;
271 pa_debug.reset_err_stack;
272 WHEN OTHERS THEN
273 FND_MSG_PUB.add_exc_msg(
274 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.org_project_exists'
275 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
276
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 x_err_code := SQLERRM;
279 IF P_PA_DEBUG_MODE = 'Y' THEN
280 pa_debug.write_file('get_org_project_info: ' || SQLERRM);
281 END IF;
282 pa_debug.reset_err_stack;
283 END get_org_project_info;
284
285 PROCEDURE get_org_task_info
286 ( p_project_id IN pa_projects_all.project_id%TYPE
287 := NULL
288 ,x_org_task_id OUT NOCOPY pa_tasks.task_id%TYPE --File.Sql.39 bug 4440895
289 ,x_organization_id OUT NOCOPY hr_organization_units.organization_id%TYPE --File.Sql.39 bug 4440895
290 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
291 ,x_err_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
292 ) is
293
294 l_debug_mode VARCHAR2(30);
295
296 CURSOR own_task IS
297 SELECT pt.task_id,
298 pt.carrying_out_organization_id
299 FROM pa_tasks pt
300 WHERE pt.project_id = p_project_id;
301
302 l_stage NUMBER := 0;
303
304 BEGIN
305 BEGIN
306 IF P_PA_DEBUG_MODE = 'Y' THEN
307 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_org_task_info');
308 END IF;
309
310 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
311 l_debug_mode := NVL(l_debug_mode, 'Y');
312
313 IF P_PA_DEBUG_MODE = 'Y' THEN
314 pa_debug.set_process('get_org_task_info: ' || 'PLSQL','LOG',l_debug_mode);
315 END IF;
316
317 x_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 l_stage := 100;
320 pa_debug.g_err_stage := 'Entered PA_FP_ORG_FCST_UTILS.get_org_task_info for Project ['||p_project_id || ']';
321 IF P_PA_DEBUG_MODE = 'Y' THEN
322 pa_debug.write_file('get_org_task_info: ' || pa_debug.g_err_stage);
323 END IF;
324
325 OPEN own_task;
326 FETCH own_task into x_org_task_id, x_organization_id;
327 CLOSE own_task;
328 l_stage := 200;
329 pa_debug.g_err_stage := 'Leaving PA_FP_ORG_FCST_UTILS.get_org_task_info';
330 IF P_PA_DEBUG_MODE = 'Y' THEN
331 pa_debug.write_file('get_org_task_info: ' || pa_debug.g_err_stage);
332 END IF;
333 pa_debug.reset_err_stack;
334
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 x_org_task_id := -9999;
338 x_organization_id := -9999;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 x_err_code := SQLERRM;
341 IF P_PA_DEBUG_MODE = 'Y' THEN
342 pa_debug.write_file('get_org_task_info: ' || 'Org Own Task Not Found');
343 END IF;
344 pa_debug.reset_err_stack;
345 END;
346 EXCEPTION
347 WHEN OTHERS THEN
348 FND_MSG_PUB.add_exc_msg(
349 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.org_project_exists'
350 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
351
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 x_err_code := SQLERRM;
354 IF P_PA_DEBUG_MODE = 'Y' THEN
355 pa_debug.write_file('get_org_task_info: ' || SQLERRM);
356 END IF;
357 pa_debug.reset_err_stack;
358 END get_org_task_info;
359
360 PROCEDURE get_utilization_details
361 ( p_org_id IN pa_implementations_all.org_id%TYPE
362 := NULL
363 ,p_organization_id IN hr_organization_units.organization_id%TYPE
364 := NULL
365 ,p_period_type IN pa_forecasting_options_all.org_fcst_period_type%TYPE
366 := NULL
367 ,p_period_set_name IN gl_periods.period_set_name%TYPE
368 := NULL
369 ,p_period_name IN gl_periods.period_name%TYPE
370 := NULL
371 ,x_utl_hours OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
372 ,x_utl_capacity OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
373 ,x_utl_percent OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
374 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
375 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
376 IS
377 l_debug_mode VARCHAR2(30);
378
379 CURSOR get_utilization_amts IS
380 SELECT psb.amount_type_id,
381 nvl(sum(psb.period_balance),0)
382 FROM pa_objects po,
383 pa_summ_balances psb
384 WHERE po.object_type_code = 'ORG'
385 AND po.expenditure_org_id = p_org_id
386 AND po.project_org_id = -1
387 AND po.expenditure_organization_id = p_organization_id
388 AND po.project_organization_id = -1
389 AND po.project_id = -1
390 AND po.task_id = -1
391 AND po.person_id = -1
392 AND po.work_type_id = -1
393 AND po.org_util_category_id = -1
394 AND po.res_util_category_id = -1
395 AND po.balance_type_code = 'FORECAST'
396 AND po.assignment_id = -1
397 AND psb.object_id = po.object_id
398 AND psb.period_type = p_period_type
399 AND psb.object_type_code = 'ORG'
400 AND psb.version_id = -1
401 AND psb.period_set_name = p_period_set_name
402 AND psb.period_name = p_period_name
403 AND psb.global_exp_period_end_date = trunc(to_date('01/01/1420','MM/DD/YYYY'))
404 AND psb.amount_type_id in (32,37,38) /* 32-Weighted hours, 37-capacity, 38-Reduced Capacity */
405 GROUP BY amount_type_id;
406
407 l_amount_type_id pa_summ_balances.amount_type_id%TYPE;
408 l_period_balance pa_summ_balances.period_balance%TYPE :=0;
409 l_hours pa_summ_balances.period_balance%TYPE :=0;
410 l_capacity pa_summ_balances.period_balance%TYPE :=0;
411 l_reduced_capacity pa_summ_balances.period_balance%TYPE :=0;
412
413 l_stage NUMBER := 0;
414
415 BEGIN
416 IF P_PA_DEBUG_MODE = 'Y' THEN
417 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_utilization_details');
418 END IF;
419
420 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
421 l_debug_mode := NVL(l_debug_mode, 'Y');
422
423 IF P_PA_DEBUG_MODE = 'Y' THEN
424 pa_debug.set_process('get_utilization_details: ' || 'PLSQL','LOG',l_debug_mode);
425 END IF;
426
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 l_stage := 100;
430 -- hr_utility.trace('p_org_id ['||p_org_id ||']');
431 -- hr_utility.trace('p_organization_id ['||p_organization_id ||']');
432 -- hr_utility.trace('p_period_type ['||p_period_type ||']');
433 -- hr_utility.trace('p_period_set_name ['||p_period_set_name ||']');
434 -- hr_utility.trace('p_period_name ['||p_period_name ||']');
435
436 OPEN get_utilization_amts;
437 -- hr_utility.trace('ROWS = '||to_char(SQL%ROWCOUNT));
438 LOOP
439 FETCH get_utilization_amts INTO l_amount_type_id, l_period_balance;
440 EXIT WHEN get_utilization_amts%NOTFOUND;
441 -- hr_utility.trace('Amount Type Id = '||to_char(l_amount_type_id));
442 -- hr_utility.trace('Period Balance = '||to_char(l_period_balance));
443 l_stage := 200;
444
445 IF l_amount_type_id = 32 THEN
446 l_hours := l_period_balance;
447 ELSIF l_amount_type_id = 37 THEN
448 l_capacity := l_period_balance;
449 ELSIF l_amount_type_id = 38 THEN
450 l_reduced_capacity := l_period_balance;
451 END IF;
452 END LOOP;
453 l_stage := 300;
454 CLOSE get_utilization_amts;
455
456 IF ((l_hours = 0) OR (l_capacity - l_reduced_capacity <=0 )) THEN
457 l_stage := 400;
458 x_utl_hours := 0;
459 x_utl_capacity := 0;
460 x_utl_percent := 0;
461 ELSE
462 l_stage := 400;
463 x_utl_hours := l_hours;
464 x_utl_capacity := l_capacity - l_reduced_capacity;
465 x_utl_percent := (l_hours/(l_capacity-l_reduced_capacity))*100;
466 END IF;
467 /*
468 pa_debug.g_err_stage := 'x_utl_hours ['||x_utl_hours ||
469 '] x_utl_capacity ['||x_utl_capacity ||
470 '] x_utl_percent ['||x_utl_percent ||
471 ']';
472 IF P_PA_DEBUG_MODE = 'Y' THEN
473 pa_debug.write_file('get_utilization_details: ' || pa_debug.g_err_stage);
474 END IF;
475 */
476 pa_debug.reset_err_stack;
477 EXCEPTION
478 WHEN OTHERS THEN
479 FND_MSG_PUB.add_exc_msg(
480 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.get_utilization_details'
481 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
482
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 x_err_code := SQLERRM;
485 IF P_PA_DEBUG_MODE = 'Y' THEN
486 pa_debug.write_file('get_utilization_details: ' || SQLERRM);
487 END IF;
488 pa_debug.reset_err_stack;
489 END get_utilization_details;
490
491 PROCEDURE get_headcount
492 ( p_organization_id IN hr_organization_units.organization_id%TYPE
493 := NULL
494 ,p_effective_date IN DATE
495 := NULL
496 ,x_headcount OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
497 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
498 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
499 IS
500 l_stage NUMBER := 0;
501 l_debug_mode VARCHAR2(30);
502
503 CURSOR get_headcount IS
504 SELECT COUNT(*)
505 FROM per_assignments_f paf
506 ,per_all_people_f ppf
507 ,per_periods_of_service pps
508 WHERE paf.organization_id = p_organization_id
509 AND p_effective_date BETWEEN paf.effective_start_date
510 AND paf.effective_end_date
511 AND paf.assignment_type in ('E','C') /*Bug#2911451*/
512 AND paf.primary_flag = 'Y'
513 AND ppf.person_id = paf.person_id
514 AND p_effective_date BETWEEN ppf.effective_start_date
515 AND ppf.effective_end_date
516 AND pps.person_id = ppf.person_id
517 AND nvl(pps.actual_termination_date,p_effective_date) >= p_effective_date;
518
519
520 BEGIN
521 IF P_PA_DEBUG_MODE = 'Y' THEN
522 pa_debug.init_err_stack('get_headcount: ' || 'PA_FP_ORG_FCST_UTILS.get_utilization_details');
523 END IF;
524
525 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
526 l_debug_mode := NVL(l_debug_mode, 'Y');
527
528 IF P_PA_DEBUG_MODE = 'Y' THEN
529 pa_debug.set_process('get_headcount: ' || 'PLSQL','LOG',l_debug_mode);
530 END IF;
531
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533 /*
534 pa_debug.g_err_stage := 'p_organization_id ['||p_organization_id ||
535 '] p_effective_date ['||p_effective_date ||
536 ']';
537 IF P_PA_DEBUG_MODE = 'Y' THEN
538 pa_debug.write_file('get_headcount: ' || pa_debug.g_err_stage);
539 END IF;
540 */
541
542 OPEN get_headcount;
543 FETCH get_headcount INTO x_headcount;
544 CLOSE get_headcount;
545
546 /*
547 pa_debug.g_err_stage := 'x_headcount ['||x_headcount ||
548 ']';
549 IF P_PA_DEBUG_MODE = 'Y' THEN
550 pa_debug.write_file('get_headcount: ' || pa_debug.g_err_stage);
551 END IF;
552 */
553 pa_debug.reset_err_stack;
554 EXCEPTION
555 WHEN OTHERS THEN
556 FND_MSG_PUB.add_exc_msg(
557 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.get_headcount'
558 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
559
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 x_err_code := SQLERRM;
562 IF P_PA_DEBUG_MODE = 'Y' THEN
563 pa_debug.write_file('get_headcount: ' || SQLERRM);
564 END IF;
565 pa_debug.reset_err_stack;
566 END get_headcount;
567
568 PROCEDURE get_probability_percent
569 ( p_project_id IN pa_projects_all.project_id%TYPE
570 := NULL
571 ,x_prob_percent OUT NOCOPY pa_probability_members.probability_percentage%TYPE --File.Sql.39 bug 4440895
572 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
573 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
574 IS
575 l_debug_mode VARCHAR2(30);
576 l_probability_member_id pa_projects_all.probability_member_id%TYPE;
577 BEGIN
578 IF P_PA_DEBUG_MODE = 'Y' THEN
579 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_probability_percent');
580 END IF;
581
582 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
583 l_debug_mode := NVL(l_debug_mode, 'Y');
584
585 IF P_PA_DEBUG_MODE = 'Y' THEN
586 pa_debug.set_process('get_probability_percent: ' || 'PLSQL','LOG',l_debug_mode);
587 END IF;
588
589 SELECT nvl(probability_member_id,-99)
590 INTO l_probability_member_id
591 FROM pa_projects_all
592 WHERE project_id = p_project_id;
593
594 IF l_probability_member_id < 0 THEN
595 x_prob_percent := 100;
596 ELSE
597 SELECT nvl(probability_percentage,100)
598 INTO x_prob_percent
599 FROM pa_probability_members
600 WHERE probability_member_id = l_probability_member_id;
601 END IF;
602 pa_debug.g_err_stage := 'Txn Project_id = ['||p_project_id ||']';
603 IF P_PA_DEBUG_MODE = 'Y' THEN
604 pa_debug.write_file('get_probability_percent: ' || pa_debug.g_err_stage);
605 END IF;
606 pa_debug.g_err_stage := 'Probability = ['||x_prob_percent||']';
607 IF P_PA_DEBUG_MODE = 'Y' THEN
608 pa_debug.write_file('get_probability_percent: ' || pa_debug.g_err_stage);
609 END IF;
610 pa_debug.reset_err_stack;
611 EXCEPTION
612 WHEN OTHERS THEN
613 FND_MSG_PUB.add_exc_msg(
614 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.get_probability_percent'
615 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
616
617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
618 IF P_PA_DEBUG_MODE = 'Y' THEN
619 pa_debug.write_file('get_probability_percent: ' || SQLERRM);
620 END IF;
621 x_err_code := SQLERRM;
622 pa_debug.reset_err_stack;
623 END get_probability_percent;
624
625 PROCEDURE get_period_profile
626 ( p_project_id IN pa_projects_all.project_id%TYPE
627 := NULL
628 ,p_period_profile_type IN pa_proj_period_profiles.period_profile_type%TYPE
629 := NULL
630 ,p_plan_period_type IN pa_forecasting_options.org_fcst_period_type%TYPE
631 := NULL
632 ,p_period_set_name IN gl_periods.period_set_name%TYPE
633 := NULL
634 ,p_act_period_type IN gl_periods.period_type%TYPE
635 := NULL
636 ,p_start_date IN gl_periods.start_date%TYPE
637 := NULL
638 ,p_number_of_periods IN pa_forecasting_options.number_of_periods%TYPE
639 ,x_period_profile_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
640 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
641 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
642 IS
643 l_debug_mode VARCHAR2(30);
644 BEGIN
645 IF P_PA_DEBUG_MODE = 'Y' THEN
646 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.get_period_profile');
647 END IF;
648
649 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
650 l_debug_mode := NVL(l_debug_mode, 'Y');
651
652 IF P_PA_DEBUG_MODE = 'Y' THEN
653 pa_debug.set_process('get_period_profile: ' || 'PLSQL','LOG',l_debug_mode);
654 END IF;
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657 BEGIN
658 SELECT ppp.period_profile_id
659 INTO x_period_profile_id
660 FROM pa_proj_period_profiles ppp
661 WHERE ppp.project_id = p_project_id
662 AND ppp.period_profile_type = p_period_profile_type
663 AND ppp.plan_period_type = p_plan_period_type
664 AND ppp.period_set_name = p_period_set_name
665 AND ppp.gl_period_type = p_act_period_type
666 AND ppp.period1_start_date = p_start_date
667 AND ppp.number_of_periods = p_number_of_periods;
668
669 --pa_debug.write_file('Period_profile Found');
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 x_period_profile_id := -99;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 IF P_PA_DEBUG_MODE = 'Y' THEN
675 pa_debug.write_file('get_period_profile: ' || 'Period_profile Not Found');
676 END IF;
677 x_err_code := SQLERRM;
678 END;
679 /*
680 pa_debug.g_err_stage := 'x_period_profile_id ['||x_period_profile_id ||
681 ']';
682 IF P_PA_DEBUG_MODE = 'Y' THEN
683 pa_debug.write_file('get_period_profile: ' || pa_debug.g_err_stage);
684 END IF;
685 */
686 pa_debug.reset_err_stack;
687 EXCEPTION
688 WHEN OTHERS THEN
689 FND_MSG_PUB.add_exc_msg(
690 p_pkg_name => 'PA_FP_ORG_FCST_UTILS.get_period_profile'
691 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 x_err_code := SQLERRM;
694 IF P_PA_DEBUG_MODE = 'Y' THEN
695 pa_debug.write_file('get_period_profile: ' || SQLERRM);
696 END IF;
697 pa_debug.reset_err_stack;
698 END get_period_profile;
699
700 FUNCTION check_org_proj_template
701 ( p_project_id IN pa_projects_all.project_id%TYPE
702 := NULL
703 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
704 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
705 RETURN VARCHAR2 IS
706
707 l_org_proj_template_exists varchar2(1);
708
709 BEGIN
710 IF P_PA_DEBUG_MODE = 'Y' THEN
711 pa_debug.write_file('Entered PA_FP_ORG_FCST_UTILS.check_org_proj_template');
712 END IF;
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714
715 l_org_proj_template_exists := 'N';
716
717 SELECT 'Y'
718 INTO l_org_proj_template_exists
719 FROM pa_forecasting_options
720 WHERE org_fcst_project_template_id = p_project_id;
721
722 x_return_status := FND_API.G_RET_STS_ERROR;
723
724 IF P_PA_DEBUG_MODE = 'Y' THEN
725 pa_debug.write_file('Leaving PA_FP_ORG_FCST_UTILS.check_org_proj_template');
726 END IF;
727 pa_debug.reset_err_stack;
728 return(l_org_proj_template_exists);
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 l_org_proj_template_exists := 'N';
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734 return(l_org_proj_template_exists);
735 WHEN OTHERS THEN
736 FND_MSG_PUB.add_exc_msg(
737 p_pkg_name => 'PA_FP_ORG_FCST_UTILS'
738 ,p_procedure_name => 'check_org_proj_template');
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 x_err_code := SQLERRM;
741 IF P_PA_DEBUG_MODE = 'Y' THEN
742 pa_debug.write_file('check_org_proj_template: ' || SQLERRM);
743 END IF;
744 pa_debug.reset_err_stack;
745 END check_org_proj_template;
746
747 PROCEDURE Get_Tp_Amount_Type(p_project_id IN NUMBER,
748 p_work_type_id IN NUMBER,
749 x_tp_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
750 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
751 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
752 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
753 l_org_id NUMBER;
754 BEGIN
755
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 x_tp_amount_type := NULL;
759
760 IF p_work_type_id IS NOT NULL THEN
761 SELECT Tp_Amt_Type_Code
762 INTO x_tp_amount_type
763 FROM Pa_Work_Types_B
764 WHERE Work_Type_Id = p_work_type_id;
765
766 IF x_tp_amount_type IS NOT NULL THEN
767 RETURN;
768 END IF;
769 END IF;
770
771 IF p_project_id IS NOT NULL THEN
772 BEGIN
773 SELECT NVL(Org_Id,-99)
774 INTO l_org_id
775 FROM Pa_Projects_All
776 WHERE Project_Id = p_project_id;
777
778 SELECT Default_Tp_Amount_Type
779 INTO x_tp_amount_type
780 FROM Pa_Forecasting_Options_All
781 WHERE nvl(Org_Id,-99) = l_org_id;
782 EXCEPTION
783 WHEN NO_DATA_FOUND THEN
784 PA_UTILS.Add_Message(
785 p_app_short_name => 'PA'
786 ,p_msg_name =>'PA_FORECAST_OPTIONS_NOT_SETUP');
787 x_return_status := FND_API.G_RET_STS_ERROR;
788 x_msg_data := 'PA_FORECAST_OPTIONS_NOT_SETUP';
789 x_msg_count := FND_MSG_PUB.Count_Msg;
790 END;
791 END IF;
792 RETURN;
793 EXCEPTION
794 WHEN OTHERS THEN
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 x_msg_count := 1;
797 x_msg_data := SUBSTR(SQLERRM,1,30);
798 FND_MSG_PUB.add_exc_msg
799 ( p_pkg_name => 'PA_FP_ORG_FCST_UTILS',
800 p_procedure_name => 'Get_Tp_Amount_Type');
801 RAISE;
802 END Get_Tp_Amount_Type;
803
804 FUNCTION check_org_project
805 ( p_project_id IN pa_projects_all.project_id%TYPE
806 := NULL
807 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
808 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
809 RETURN VARCHAR2 IS
810
811 l_org_project_exists varchar2(1);
812
813 BEGIN
814 IF P_PA_DEBUG_MODE = 'Y' THEN
815 pa_debug.write_file('Entered PA_FP_ORG_FCST_UTILS.check_org_project');
816 END IF;
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819 l_org_project_exists := 'N';
820
821 SELECT 'Y'
822 INTO l_org_project_exists
823 FROM pa_projects pp,
824 pa_project_types pt
825 WHERE pp.project_id = p_project_id
826 AND pt.project_type = pp.project_type
827 AND pt.org_project_flag = 'Y';
828
829 IF P_PA_DEBUG_MODE = 'Y' THEN
830 pa_debug.write_file('Leaving PA_FP_ORG_FCST_UTILS.check_org_project');
831 END IF;
832 pa_debug.reset_err_stack;
833 return(l_org_project_exists);
834
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 l_org_project_exists := 'N';
838 x_return_status := FND_API.G_RET_STS_SUCCESS;
839 return(l_org_project_exists);
840 WHEN OTHERS THEN
841 FND_MSG_PUB.add_exc_msg(
842 p_pkg_name => 'PA_FP_ORG_FCST_UTILS'
843 ,p_procedure_name => 'check_org_project');
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 --x_err_code := SQLERRM; bug 4338407
846 x_err_code := SQLCODE;
847 IF P_PA_DEBUG_MODE = 'Y' THEN
848 pa_debug.write_file('check_org_project: ' || SQLERRM);
849 END IF;
850 pa_debug.reset_err_stack;
851 return('E'); -- bug 4338407
852 END check_org_project;
853
854 FUNCTION calculate_gl_amount
855 ( p_amount_code IN pa_amount_types_b.amount_type_code%TYPE
856 := NULL)
857 RETURN NUMBER IS
858
859 Revenue number := 0;
860 Cost number := 0;
861 Headcount number := 0;
862 Util_Hours number := 0;
863 Util_capacity number := 0;
864 Util_percent number := 0;
865
866 BEGIN
867
868 IF p_amount_code = 'MARGIN_PERCENT' THEN
869
870 SELECT
871 (
872 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
873 'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
874 nvl(pppd.period_amount1,0)
875 )
876 )+
877 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
878 'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
879 nvl(pppd.period_amount2,0)
880 )
881 )+
882 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
883 'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
884 nvl(pppd.period_amount3,0)
885 )
886 )+
887 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
888 'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
889 nvl(pppd.period_amount4,0)
890 )
891 )+
892 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
893 'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
894 nvl(pppd.period_amount5,0)
895 )
896 )+
897 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
898 'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
899 nvl(pppd.period_amount6,0)
900 )
901 )
902 ) as Total
903 INTO revenue
904 FROM pa_resource_assignments pra,
905 pa_budget_versions pbv,
906 pa_fp_period_values_v pppd
907 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
908 AND pra.project_id = pbv.project_id
909 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
910 AND pra.resource_assignment_type='PROJECT'
911 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
912 AND pppd.resource_assignment_id=pra.resource_assignment_id
913 AND pppd.amount_type_code = 'REVENUE'
914 ORDER BY pppd.amount_type_id;
915
916 SELECT
917 (
918 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
919 'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
920 nvl(pppd.period_amount1,0)
921 )
922 )+
923 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
924 'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
925 nvl(pppd.period_amount2,0)
926 )
927 )+
928 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
929 'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
930 nvl(pppd.period_amount3,0)
931 )
932 )+
933 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
934 'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
935 nvl(pppd.period_amount4,0)
936 )
937 )+
938 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
939 'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
940 nvl(pppd.period_amount5,0)
941 )
942 )+
943 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
944 'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
945 nvl(pppd.period_amount6,0)
946 )
947 )
948 ) as Total
949 INTO cost
950 FROM pa_resource_assignments pra,
951 pa_budget_versions pbv,
952 pa_fp_period_values_v pppd
953 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
954 AND pra.project_id = pbv.project_id
955 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
956 AND pra.resource_assignment_type='PROJECT'
957 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
958 AND pppd.resource_assignment_id=pra.resource_assignment_id
959 AND pppd.amount_type_code = 'COST'
960 ORDER BY pppd.amount_type_id;
961
962 IF revenue = 0 THEN
963 return 0;
964 ELSE
965 return ((revenue-cost)/revenue) * 100;
966 END IF;
967
968 END IF;
969
970 IF p_amount_code = 'HEADCOUNT' THEN
971
972 SELECT
973 round((sum(nvl(pppd.period_amount1,0))+
974 sum(nvl(pppd.period_amount2,0))+
975 sum(nvl(pppd.period_amount3,0))+
976 sum(nvl(pppd.period_amount4,0))+
977 sum(nvl(pppd.period_amount5,0))+
978 sum(nvl(pppd.period_amount6,0)))/6,0)
979 INTO headcount
980 FROM pa_resource_assignments pra,
981 pa_budget_versions pbv,
982 pa_fp_period_values_v pppd
983 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
984 AND pra.project_id = pbv.project_id
985 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
986 AND pra.resource_assignment_type='PROJECT'
987 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
988 AND pppd.resource_assignment_id=pra.resource_assignment_id
989 AND pppd.amount_type_code = 'HEADCOUNT'
990 ORDER BY pppd.amount_type_id;
991
992 return headcount;
993
994 END IF;
995
996 IF p_amount_code = 'HEADCOUNT_ADJUSTMENTS' THEN
997
998 SELECT
999 round((nvl(pppd.period_amount1,0)+
1000 nvl(pppd.period_amount2,0)+
1001 nvl(pppd.period_amount3,0)+
1002 nvl(pppd.period_amount4,0)+
1003 nvl(pppd.period_amount5,0)+
1004 nvl(pppd.period_amount6,0))/6,0)
1005 INTO headcount
1006 FROM pa_resource_assignments pra,
1007 pa_budget_versions pbv,
1008 pa_fp_period_values_v pppd
1009 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1010 AND pra.project_id = pbv.project_id
1011 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1012 AND pra.resource_assignment_type='PROJECT'
1013 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1014 AND pppd.resource_assignment_id=pra.resource_assignment_id
1015 AND pppd.object_type_code = 'RES_ASSIGNMENT'
1016 AND pppd.object_id = pra.resource_assignment_id
1017 AND pppd.amount_subtype_code = 'HEADCOUNT_ADJUSTMENTS';
1018
1019 return headcount;
1020
1021 END IF;
1022
1023 IF p_amount_code = 'BEGIN_HEADCOUNT' THEN
1024
1025 SELECT
1026 round((nvl(pppd.period_amount1,0)+
1027 nvl(pppd.period_amount2,0)+
1028 nvl(pppd.period_amount3,0)+
1029 nvl(pppd.period_amount4,0)+
1030 nvl(pppd.period_amount5,0)+
1031 nvl(pppd.period_amount6,0))/6,0)
1032 INTO headcount
1033 FROM pa_resource_assignments pra,
1034 pa_budget_versions pbv,
1035 pa_fp_period_values_v pppd
1036 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1037 AND pra.project_id = pbv.project_id
1038 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1039 AND pra.resource_assignment_type='PROJECT'
1040 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1041 AND pppd.resource_assignment_id=pra.resource_assignment_id
1042 AND pppd.amount_subtype_code = 'BEGIN_HEADCOUNT';
1043
1044 return headcount;
1045
1046 END IF;
1047
1048 IF p_amount_code = 'UTILIZATION_PERCENT' THEN
1049
1050 SELECT
1051 (nvl(pppd.period_amount1,0)+
1052 nvl(pppd.period_amount2,0)+
1053 nvl(pppd.period_amount3,0)+
1054 nvl(pppd.period_amount4,0)+
1055 nvl(pppd.period_amount5,0)+
1056 nvl(pppd.period_amount6,0))/6
1057 INTO util_percent
1058 FROM pa_resource_assignments pra,
1059 pa_budget_versions pbv,
1060 pa_fp_period_values_v pppd
1061 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1062 AND pra.project_id = pbv.project_id
1063 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1064 AND pra.resource_assignment_type='PROJECT'
1065 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1066 AND pppd.resource_assignment_id=pra.resource_assignment_id
1067 AND pppd.amount_subtype_code = 'UTILIZATION_PERCENT';
1068
1069 return util_percent;
1070
1071 END IF;
1072 IF p_amount_code = 'UTILIZATION_ADJUSTMENTS' THEN
1073
1074 SELECT
1075 (nvl(pppd.period_amount1,0)+
1076 nvl(pppd.period_amount2,0)+
1077 nvl(pppd.period_amount3,0)+
1078 nvl(pppd.period_amount4,0)+
1079 nvl(pppd.period_amount5,0)+
1080 nvl(pppd.period_amount6,0))/6
1081 INTO util_percent
1082 FROM pa_resource_assignments pra,
1083 pa_budget_versions pbv,
1084 pa_fp_period_values_v pppd
1085 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1086 AND pra.project_id = pbv.project_id
1087 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1088 AND pra.resource_assignment_type='PROJECT'
1089 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1090 AND pppd.resource_assignment_id=pra.resource_assignment_id
1091 AND pppd.object_type_code = 'RES_ASSIGNMENT'
1092 AND pppd.object_id = pra.resource_assignment_id
1093 AND pppd.amount_subtype_code = 'UTILIZATION_ADJUSTMENTS';
1094
1095 return util_percent;
1096
1097 END IF;
1098
1099 IF p_amount_code = 'UTILIZATION' THEN
1100
1101 SELECT
1102 (sum(nvl(pppd.period_amount1,0))+
1103 sum(nvl(pppd.period_amount2,0))+
1104 sum(nvl(pppd.period_amount3,0))+
1105 sum(nvl(pppd.period_amount4,0))+
1106 sum(nvl(pppd.period_amount5,0))+
1107 sum(nvl(pppd.period_amount6,0)))/6
1108 INTO util_percent
1109 FROM pa_resource_assignments pra,
1110 pa_budget_versions pbv,
1111 pa_fp_period_values_v pppd
1112 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1113 AND pra.project_id = pbv.project_id
1114 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1115 AND pra.resource_assignment_type='PROJECT'
1116 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1117 AND pppd.resource_assignment_id=pra.resource_assignment_id
1118 AND pppd.amount_type_code = 'UTILIZATION';
1119
1120
1121 return util_percent;
1122
1123 END IF;
1124
1125
1126 END calculate_gl_amount;
1127
1128 FUNCTION calculate_pa_amount
1129 ( p_amount_code IN pa_amount_types_b.amount_type_code%TYPE
1130 := NULL)
1131 RETURN NUMBER IS
1132 Revenue number := 0;
1133 Cost number := 0;
1134 Headcount number := 0;
1135 Util_hours number := 0;
1136 Util_capacity number := 0;
1137 Util_percent number := 0;
1138
1139 BEGIN
1140
1141 IF p_amount_code = 'MARGIN_PERCENT' THEN
1142
1143 SELECT
1144 (
1145 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
1146 'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
1147 nvl(pppd.period_amount1,0)
1148 )
1149 )+
1150 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
1151 'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
1152 nvl(pppd.period_amount2,0)
1153 )
1154 )+
1155 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
1156 'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
1157 nvl(pppd.period_amount3,0)
1158 )
1159 )+
1160 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
1161 'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
1162 nvl(pppd.period_amount4,0)
1163 )
1164 )+
1165 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
1166 'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
1167 nvl(pppd.period_amount5,0)
1168 )
1169 )+
1170 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
1171 'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
1172 nvl(pppd.period_amount6,0)
1173 )
1174 )+
1175 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount7,0)*-1,
1176 'TP_COST_OUT', nvl(pppd.period_amount7,0)*-1,
1177 nvl(pppd.period_amount7,0)
1178 )
1179 )+
1180 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount8,0)*-1,
1181 'TP_COST_OUT', nvl(pppd.period_amount8,0)*-1,
1182 nvl(pppd.period_amount8,0)
1183 )
1184 )+
1185 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount9,0)*-1,
1186 'TP_COST_OUT', nvl(pppd.period_amount9,0)*-1,
1187 nvl(pppd.period_amount9,0)
1188 )
1189 )+
1190 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount10,0)*-1,
1191 'TP_COST_OUT', nvl(pppd.period_amount10,0)*-1,
1192 nvl(pppd.period_amount10,0)
1193 )
1194 )+
1195 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount11,0)*-1,
1196 'TP_COST_OUT', nvl(pppd.period_amount11,0)*-1,
1197 nvl(pppd.period_amount11,0)
1198 )
1199 )+
1200 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount12,0)*-1,
1201 'TP_COST_OUT', nvl(pppd.period_amount12,0)*-1,
1202 nvl(pppd.period_amount12,0)
1203 )
1204 )+
1205 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount13,0)*-1,
1206 'TP_COST_OUT', nvl(pppd.period_amount13,0)*-1,
1207 nvl(pppd.period_amount13,0)
1208 )
1209 )
1210 )
1211 INTO revenue
1212 FROM pa_resource_assignments pra,
1213 pa_budget_versions pbv,
1214 pa_fp_period_values_v pppd
1215 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1216 AND pra.project_id = pbv.project_id
1217 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1218 AND pra.resource_assignment_type='PROJECT'
1219 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1220 AND pppd.resource_assignment_id=pra.resource_assignment_id
1221 AND pppd.amount_type_code = 'REVENUE'
1222 ORDER BY pppd.amount_type_id;
1223
1224 SELECT
1225 (
1226 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
1227 'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
1228 nvl(pppd.period_amount1,0)
1229 )
1230 )+
1231 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
1232 'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
1233 nvl(pppd.period_amount2,0)
1234 )
1235 )+
1236 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
1237 'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
1238 nvl(pppd.period_amount3,0)
1239 )
1240 )+
1241 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
1242 'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
1243 nvl(pppd.period_amount4,0)
1244 )
1245 )+
1246 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
1247 'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
1248 nvl(pppd.period_amount5,0)
1249 )
1250 )+
1251 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
1252 'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
1253 nvl(pppd.period_amount6,0)
1254 )
1255 )+
1256 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount7,0)*-1,
1257 'TP_COST_OUT', nvl(pppd.period_amount7,0)*-1,
1258 nvl(pppd.period_amount7,0)
1259 )
1260 )+
1261 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount8,0)*-1,
1262 'TP_COST_OUT', nvl(pppd.period_amount8,0)*-1,
1263 nvl(pppd.period_amount8,0)
1264 )
1265 )+
1266 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount9,0)*-1,
1267 'TP_COST_OUT', nvl(pppd.period_amount9,0)*-1,
1268 nvl(pppd.period_amount9,0)
1269 )
1270 )+
1271 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount10,0)*-1,
1272 'TP_COST_OUT', nvl(pppd.period_amount10,0)*-1,
1273 nvl(pppd.period_amount10,0)
1274 )
1275 )+
1276 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount11,0)*-1,
1277 'TP_COST_OUT', nvl(pppd.period_amount11,0)*-1,
1278 nvl(pppd.period_amount11,0)
1279 )
1280 )+
1281 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount12,0)*-1,
1282 'TP_COST_OUT', nvl(pppd.period_amount12,0)*-1,
1283 nvl(pppd.period_amount12,0)
1284 )
1285 )+
1286 sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount13,0)*-1,
1287 'TP_COST_OUT', nvl(pppd.period_amount13,0)*-1,
1288 nvl(pppd.period_amount13,0)
1289 )
1290 )
1291 )
1292 INTO cost
1293 FROM pa_resource_assignments pra,
1294 pa_budget_versions pbv,
1295 pa_fp_period_values_v pppd
1296 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1297 AND pra.project_id = pbv.project_id
1298 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1299 AND pra.resource_assignment_type='PROJECT'
1300 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1301 AND pppd.resource_assignment_id=pra.resource_assignment_id
1302 AND pppd.amount_type_code = 'COST'
1303 ORDER BY pppd.amount_type_id;
1304
1305 IF revenue = 0 THEN
1306 return 0;
1307 ELSE
1308 return ((revenue-cost)/revenue) * 100;
1309 END IF;
1310
1311 END IF;
1312
1313 IF p_amount_code = 'HEADCOUNT' THEN
1314
1315 SELECT
1316 round((sum(nvl(pppd.period_amount1,0))+
1317 sum(nvl(pppd.period_amount2,0))+
1318 sum(nvl(pppd.period_amount3,0))+
1319 sum(nvl(pppd.period_amount4,0))+
1320 sum(nvl(pppd.period_amount5,0))+
1321 sum(nvl(pppd.period_amount6,0))+
1322 sum(nvl(pppd.period_amount7,0))+
1323 sum(nvl(pppd.period_amount8,0))+
1324 sum(nvl(pppd.period_amount9,0))+
1325 sum(nvl(pppd.period_amount10,0))+
1326 sum(nvl(pppd.period_amount11,0))+
1327 sum(nvl(pppd.period_amount12,0))+
1328 sum(nvl(pppd.period_amount13,0)))/13,0)
1329 INTO headcount
1330 FROM pa_resource_assignments pra,
1331 pa_budget_versions pbv,
1332 pa_fp_period_values_v pppd
1333 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1334 AND pra.project_id = pbv.project_id
1335 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1336 AND pra.resource_assignment_type='PROJECT'
1337 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1338 AND pppd.resource_assignment_id=pra.resource_assignment_id
1339 AND pppd.amount_type_code = 'HEADCOUNT';
1340
1341 return headcount;
1342
1343 END IF;
1344
1345 IF p_amount_code = 'HEADCOUNT_ADJUSTMENTS' THEN
1346
1347 SELECT
1348 round((nvl(pppd.period_amount1,0)+
1349 nvl(pppd.period_amount2,0)+
1350 nvl(pppd.period_amount3,0)+
1351 nvl(pppd.period_amount4,0)+
1352 nvl(pppd.period_amount5,0)+
1353 nvl(pppd.period_amount6,0)+
1354 nvl(pppd.period_amount7,0)+
1355 nvl(pppd.period_amount8,0)+
1356 nvl(pppd.period_amount9,0)+
1357 nvl(pppd.period_amount10,0)+
1358 nvl(pppd.period_amount11,0)+
1359 nvl(pppd.period_amount12,0)+
1360 nvl(pppd.period_amount13,0))/13,0)
1361 INTO headcount
1362 FROM pa_resource_assignments pra,
1363 pa_budget_versions pbv,
1364 pa_fp_period_values_v pppd
1365 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1366 AND pra.project_id = pbv.project_id
1367 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1368 AND pra.resource_assignment_type='PROJECT'
1369 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1370 AND pppd.object_type_code = 'RES_ASSIGNMENT'
1371 AND pppd.object_id = pra.resource_assignment_id
1372 AND pppd.resource_assignment_id=pra.resource_assignment_id
1373 AND pppd.amount_subtype_code = 'HEADCOUNT_ADJUSTMENTS';
1374
1375 return headcount;
1376
1377 END IF;
1378
1379 IF p_amount_code = 'BEGIN_HEADCOUNT' THEN
1380
1381 SELECT
1382 round((nvl(pppd.period_amount1,0)+
1383 nvl(pppd.period_amount2,0)+
1384 nvl(pppd.period_amount3,0)+
1385 nvl(pppd.period_amount4,0)+
1386 nvl(pppd.period_amount5,0)+
1387 nvl(pppd.period_amount6,0)+
1388 nvl(pppd.period_amount7,0)+
1389 nvl(pppd.period_amount8,0)+
1390 nvl(pppd.period_amount9,0)+
1391 nvl(pppd.period_amount10,0)+
1392 nvl(pppd.period_amount11,0)+
1393 nvl(pppd.period_amount12,0)+
1394 nvl(pppd.period_amount13,0))/13,0)
1395 INTO headcount
1396 FROM pa_resource_assignments pra,
1397 pa_budget_versions pbv,
1398 pa_fp_period_values_v pppd
1399 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1400 AND pra.project_id = pbv.project_id
1401 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1402 AND pra.resource_assignment_type='PROJECT'
1403 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1404 AND pppd.resource_assignment_id=pra.resource_assignment_id
1405 AND pppd.amount_subtype_code = 'BEGIN_HEADCOUNT'
1406 ORDER BY pppd.amount_type_id;
1407
1408 return headcount;
1409
1410 END IF;
1411
1412 IF p_amount_code = 'UTILIZATION_PERCENT' THEN
1413
1414 SELECT
1415 (nvl(pppd.period_amount1,0)+
1416 nvl(pppd.period_amount2,0)+
1417 nvl(pppd.period_amount3,0)+
1418 nvl(pppd.period_amount4,0)+
1419 nvl(pppd.period_amount5,0)+
1420 nvl(pppd.period_amount6,0)+
1421 nvl(pppd.period_amount7,0)+
1422 nvl(pppd.period_amount8,0)+
1423 nvl(pppd.period_amount9,0)+
1424 nvl(pppd.period_amount10,0)+
1425 nvl(pppd.period_amount11,0)+
1426 nvl(pppd.period_amount12,0)+
1427 nvl(pppd.period_amount13,0)) /13
1428 INTO util_percent
1429 FROM pa_resource_assignments pra,
1430 pa_budget_versions pbv,
1431 pa_fp_period_values_v pppd
1432 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1433 AND pra.project_id = pbv.project_id
1434 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1435 AND pra.resource_assignment_type='PROJECT'
1436 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1437 AND pppd.resource_assignment_id=pra.resource_assignment_id
1438 AND pppd.amount_subtype_code = 'UTILIZATION_PERCENT'
1439 ORDER BY pppd.amount_type_id;
1440
1441 return util_percent;
1442
1443 END IF;
1444 IF p_amount_code = 'UTILIZATION_ADJUSTMENTS' THEN
1445
1446 SELECT
1447 (nvl(pppd.period_amount1,0)+
1448 nvl(pppd.period_amount2,0)+
1449 nvl(pppd.period_amount3,0)+
1450 nvl(pppd.period_amount4,0)+
1451 nvl(pppd.period_amount5,0)+
1452 nvl(pppd.period_amount6,0)+
1453 nvl(pppd.period_amount7,0)+
1454 nvl(pppd.period_amount8,0)+
1455 nvl(pppd.period_amount9,0)+
1456 nvl(pppd.period_amount10,0)+
1457 nvl(pppd.period_amount11,0)+
1458 nvl(pppd.period_amount12,0)+
1459 nvl(pppd.period_amount13,0)) /13
1460 INTO util_percent
1461 FROM pa_resource_assignments pra,
1462 pa_budget_versions pbv,
1463 pa_fp_period_values_v pppd
1464 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1465 AND pra.project_id = pbv.project_id
1466 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1467 AND pra.resource_assignment_type='PROJECT'
1468 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1469 AND pppd.resource_assignment_id=pra.resource_assignment_id
1470 AND pppd.object_type_code = 'RES_ASSIGNMENT'
1471 AND pppd.object_id = pra.resource_assignment_id
1472 AND pppd.amount_subtype_code = 'UTILIZATION_ADJUSTMENTS';
1473
1474 return util_percent;
1475
1476 END IF;
1477 IF p_amount_code = 'UTILIZATION' THEN
1478
1479 SELECT
1480 (sum(nvl(pppd.period_amount1,0))+
1481 sum(nvl(pppd.period_amount2,0))+
1482 sum(nvl(pppd.period_amount3,0))+
1483 sum(nvl(pppd.period_amount4,0))+
1484 sum(nvl(pppd.period_amount5,0))+
1485 sum(nvl(pppd.period_amount6,0))+
1486 sum(nvl(pppd.period_amount7,0))+
1487 sum(nvl(pppd.period_amount8,0))+
1488 sum(nvl(pppd.period_amount9,0))+
1489 sum(nvl(pppd.period_amount10,0))+
1490 sum(nvl(pppd.period_amount11,0))+
1491 sum(nvl(pppd.period_amount12,0))+
1492 sum(nvl(pppd.period_amount13,0))) /13
1493 INTO util_percent
1494 FROM pa_resource_assignments pra,
1495 pa_budget_versions pbv,
1496 pa_fp_period_values_v pppd
1497 WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
1498 AND pra.project_id = pbv.project_id
1499 AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1500 AND pra.resource_assignment_type='PROJECT'
1501 AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
1502 AND pppd.resource_assignment_id=pra.resource_assignment_id
1503 AND pppd.amount_type_code = 'UTILIZATION';
1504
1505 return util_percent;
1506
1507 END IF;
1508
1509 END calculate_pa_amount;
1510
1511 /* dlai - added detect_org_project for use with project assignments 04/21/02 */
1512 procedure detect_org_project
1513 ( p_project_id IN pa_projects_all.project_id%TYPE
1514 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1515 ,x_err_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1516 is
1517
1518 l_is_org_project VARCHAR2(1);
1519 l_return_status VARCHAR2(1);
1520 l_err_code VARCHAR2(30);
1521
1522 BEGIN
1523 IF P_PA_DEBUG_MODE = 'Y' THEN
1524 pa_debug.write_file('Entered PA_FIN_PLAN_UTILS.detect_org_project');
1525 END IF;
1526 x_return_status := FND_API.G_RET_STS_SUCCESS;
1527 l_is_org_project := PA_FP_ORG_FCST_UTILS.is_org_project
1528 (p_project_id => p_project_id);
1529 if x_return_status = FND_API.G_RET_STS_SUCCESS then
1530 if l_is_org_project = 'Y' then
1531 x_return_status := FND_API.G_RET_STS_ERROR;
1532 x_err_code := 'PA_FP_ORG_FCST_MSG';
1533 return;
1534 else
1535 return;
1536 end if;
1537 else
1538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1539 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1540 p_msg_name => l_err_code);
1541 return;
1542 end if;
1543
1544 pa_debug.reset_err_stack;
1545 return;
1546
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 FND_MSG_PUB.add_exc_msg(
1550 p_pkg_name => 'PA_FP_ORG_FCST_UTILS'
1551 ,p_procedure_name => 'detect_org_project');
1552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1553 x_err_code := SQLERRM;
1554 IF P_PA_DEBUG_MODE = 'Y' THEN
1555 pa_debug.write_file('detect_org_project: ' || SQLERRM);
1556 END IF;
1557 pa_debug.reset_err_stack;
1558 END detect_org_project;
1559
1560 /* dlai 04/25/02 added function same_org_id and procedure check_same_org_id
1561 * to ensure user login org_id is the same as the project org_id he/she is
1562 * trying to modify
1563 * dlai 07/13/05 -- commented out same_org_id and check_same_org_id, because
1564 * in R12, CLIENT_INFO should no longer be used
1565 */
1566 /*
1567 FUNCTION same_org_id
1568 (p_project_id IN pa_projects_all.project_id%TYPE)
1569 return VARCHAR2 is
1570 l_login_org_id pa_projects_all.org_id%TYPE;
1571 l_project_org_id pa_projects_all.org_id%TYPE;
1572 l_login_org_id_data VARCHAR2(1000);
1573 l_same_org_id VARCHAR2(1);
1574 org_id_endpoint NUMBER(15);
1575 BEGIN
1576 IF P_PA_DEBUG_MODE = 'Y' THEN
1577 pa_debug.init_err_stack('PA_FP_ORG_FCST_UTILS.same_org_id');
1578 pa_debug.set_process('same_org_id: ' || 'PLSQL','LOG', 'Y');
1579 pa_debug.write('plsql.pa.pa_fp_org_fcst_utils.same_org_id', 'Entered PA_FP_ORG_FCST_UTILS.same_org_id', 1);
1580 END IF;
1581 l_same_org_id := 'N';
1582
1583 select org_id, USERENV('CLIENT_INFO')
1584 into l_project_org_id, l_login_org_id_data
1585 from pa_projects_all
1586 where
1587 project_id=p_project_id;
1588
1589 org_id_endpoint := INSTR(l_login_org_id_data, ' ');
1590 l_login_org_id := TO_NUMBER(RTRIM(SUBSTR(l_login_org_id_data,1,org_id_endpoint)));
1591 if nvl(l_login_org_id,-99) = nvl(l_project_org_id,-99) then -- added nvl for bug # 2890558
1592 l_same_org_id := 'Y';
1593 end if;
1594 pa_debug.reset_err_stack;
1595 return(l_same_org_id);
1596 EXCEPTION
1597 WHEN NO_DATA_FOUND THEN
1598 IF P_PA_DEBUG_MODE = 'Y' THEN
1599 pa_debug.write_file('same_org_id: ' || 'NO DATA FOUND EXCEPTION');
1600 END IF;
1601 l_same_org_id := 'E';
1602 return(l_same_org_id);
1603 WHEN OTHERS THEN
1604 FND_MSG_PUB.add_exc_msg(
1605 p_pkg_name => 'PA_FP_ORG_FCST_UTILS'
1606 ,p_procedure_name => 'same_org_id');
1607 pa_debug.reset_err_stack;
1608 return('U');
1609 END same_org_id;
1610
1611 PROCEDURE check_same_org_id
1612 (p_project_id IN pa_projects_all.project_id%TYPE,
1613 x_return_status OUT VARCHAR2,
1614 x_msg_count OUT NUMBER,
1615 x_msg_data OUT VARCHAR2)
1616 is
1617 l_same_org_id VARCHAR2(1);
1618 l_return_status VARCHAR2(1);
1619 l_err_code VARCHAR2(2000);
1620 l_msg_count NUMBER;
1621 l_msg_data VARCHAR2(2000);
1622 l_data VARCHAR2(2000);
1623 l_msg_index_out NUMBER;
1624 BEGIN
1625 x_return_status := FND_API.G_RET_STS_SUCCESS;
1626 l_same_org_id := pa_fp_org_fcst_utils.same_org_id
1627 (p_project_id => p_project_id);
1628 if l_same_org_id = 'N' then
1629 x_return_status := FND_API.G_RET_STS_ERROR;
1630 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1631 p_msg_name => 'PA_FP_ORG_ID_MISMATCH');
1632 l_msg_count := FND_MSG_PUB.count_msg;
1633 if l_msg_count = 1 then
1634 PA_INTERFACE_UTILS_PUB.get_messages
1635 (p_encoded => FND_API.G_TRUE,
1636 p_msg_index => 1,
1637 p_msg_count => l_msg_count,
1638 p_msg_data => l_msg_data,
1639 p_data => l_data,
1640 p_msg_index_out => l_msg_index_out);
1641 x_msg_data := l_data;
1642 end if;
1643 x_msg_count := l_msg_count;
1644 return;
1645 end if;
1646 EXCEPTION
1647 when others then
1648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1649 x_msg_count := 1;
1650 x_msg_data := SQLERRM;
1651 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ORG_FCST_UTILS',
1652 p_procedure_name => 'check_same_org_id');
1653 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1654 END check_same_org_id;
1655 */
1656
1657 /* for Sheenie: function which takes in only 1 argument:
1658 * returns 'Y' if project is an org project
1659 * returns 'N' if project is not an org project
1660 * returns 'E' if there is an exception error
1661 */
1662 FUNCTION is_org_project
1663 ( p_project_id IN pa_projects_all.project_id%TYPE
1664 := NULL)
1665 RETURN VARCHAR2
1666 is
1667 l_org_project_exists varchar2(1);
1668 BEGIN
1669
1670 l_org_project_exists := 'N';
1671 SELECT 'Y'
1672 INTO l_org_project_exists
1673 FROM pa_projects_all pp,
1674 pa_project_types_all pt
1675 WHERE pp.project_id = p_project_id
1676 AND pt.project_type = pp.project_type
1677 AND pp.org_id = pt.org_id
1678 AND pt.org_project_flag = 'Y';
1679 return(l_org_project_exists);
1680 EXCEPTION
1681 WHEN NO_DATA_FOUND THEN
1682 l_org_project_exists := 'N';
1683 return(l_org_project_exists);
1684 WHEN OTHERS THEN
1685 l_org_project_exists := 'E';
1686 return(l_org_project_exists);
1687 END is_org_project;
1688
1689
1690 END pa_fp_org_fcst_utils;