[Home] [Help]
PACKAGE BODY: APPS.PA_FP_SPREAD_CURVES_UTILS
Source
1 PACKAGE BODY pa_fp_spread_curves_utils as
2 /* $Header: PAFPSCUB.pls 120.1.12010000.2 2010/01/29 00:18:17 snizam ship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_spread_curves_utils';
5
6 /* This function checks whether the spread curve is in use in Budgeting and Forecasting or not */
7
8 FUNCTION is_spread_curve_in_use ( p_spread_curve_id IN Pa_spread_curves_b.spread_curve_id%TYPE ) RETURN VARCHAR2 IS
9
10 /* Start of Commenting code for bug 9036322
11 Cursor C1 IS
12 Select 'Y' from pa_resource_assignments
13 where spread_curve_id = p_spread_curve_id;
14 End of Commenting code for bug 9036322 */
15
16 -- start of new code for bug 9036322
17 Cursor C1 IS
18 select distinct spread_curve_id
19 from pa_resource_assignments;
20 -- end of new code for bug 9036322
21
22 l_return_flag varchar2(1) := 'Y';
23 BEGIN
24 /* Start of Commenting code for bug 9036322
25 open C1;
26 fetch C1 into l_return_flag;
27
28 if C1%NOTFOUND then
29 l_return_flag := 'N';
30 else
31 close C1;
32 return 'Y';
33 end if;
34
35 close C1;
36 End of Commenting code for bug 9036322 */
37
38 -- start of new code for bug 9036322
39 if G_is_first_call = 'Y' then
40 OPEN c1;
41 FETCH c1 BULK COLLECT INTO G_curve_id_tbl;
42 CLOSE c1 ;
43
44 G_is_first_call := 'N' ;
45 end if;
46 for i in G_curve_id_tbl.first .. G_curve_id_tbl.last loop
47 if p_spread_curve_id = G_curve_id_tbl(i) then
48 return 'Y';
49 end if;
50 end loop;
51 -- End of new code for bug 9036322
52
53 /* flow will come only if spread curve is not used in resource assignment */
54 l_return_flag := 'N';
55
56 if (PA_PLANNING_RESOURCE_UTILS.chk_spread_curve_in_use( p_spread_curve_id)) then
57 return 'Y';
58 else
59 return 'N';
60 end if;
61 END is_spread_curve_in_use;
62
63 /*==================================================================
64 This api validates the following attributes of Spread Curves
65 before inserting or updating
66 1. Name - uniqueness.
67 2. Dates - Start date is entered
68 end date if entered should be later than start date
69 3. Amount in buckets should not be less than 0
70 ==================================================================*/
71
72 PROCEDURE validate (
73 p_spread_curve_id IN Pa_spread_curves_b.spread_curve_id%TYPE,
74 p_name IN Pa_spread_curves_tl.name%TYPE,
75 P_effective_from IN Pa_spread_curves_b.effective_Start_date%TYPE,
76 P_effective_to IN Pa_spread_curves_b.effective_end_date%TYPE,
77 P_point1 IN Pa_spread_curves_b.point1%TYPE,
78 P_point2 IN Pa_spread_curves_b.point2%TYPE,
79 P_point3 IN Pa_spread_curves_b.point3%TYPE,
80 P_point4 IN Pa_spread_curves_b.point4%TYPE,
81 P_point5 IN Pa_spread_curves_b.point5%TYPE,
82 P_point6 IN Pa_spread_curves_b.point6%TYPE,
83 P_point7 IN Pa_spread_curves_b.point7%TYPE,
84 P_point8 IN Pa_spread_curves_b.point8%TYPE,
85 P_point9 IN Pa_spread_curves_b.point9%TYPE,
86 P_point10 IN Pa_spread_curves_b.point10%TYPE,
87 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
88 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
89 X_msg_count OUT NOCOPY number ) --File.Sql.39 bug 4440895
90 AS
91 l_any_error_occurred_flag VARCHAR2(1) := NULL;
92 l_return_status VARCHAR2(1) := NULL;
93 l_msg_count NUMBER := 0;
94 l_data VARCHAR2(2000) := NULL;
95 l_msg_data VARCHAR2(2000) := NULL;
96 l_msg_index_out NUMBER;
97 l_debug_mode VARCHAR2(1) := Null;
98
99 BEGIN
100 x_msg_count := 0;
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
103 FND_MSG_PUB.initialize;
104
105 IF l_debug_mode = 'Y' THEN
106 pa_debug.set_curr_function( p_function => 'validate',
107 p_debug_mode => l_debug_mode );
108 END IF;
109
110 /* checking for business rules validate */
111
112 l_any_error_occurred_flag := 'N';
113
114 IF l_debug_mode = 'Y' THEN
115 pa_debug.g_err_stage:= 'Validating input parameters - Name';
116 pa_debug.write(g_module_name,pa_debug.g_err_stage,
117 pa_fp_constants_pkg.g_debug_level3);
118 END IF;
119
120 validate_name
121 (p_name => p_name,
122 p_spread_curve_id => p_spread_curve_id,
123 x_return_status => l_return_status,
124 x_msg_count => l_msg_count,
125 x_msg_data => l_msg_data);
126
127 if (l_return_status = FND_API.G_RET_STS_ERROR or
128 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
129
130 l_any_error_occurred_flag := 'Y';
131 end if;
132
133 IF l_debug_mode = 'Y' THEN
134 pa_debug.g_err_stage:= 'Validating input parameters - Effective dates';
135 pa_debug.write(g_module_name,pa_debug.g_err_stage,
136 pa_fp_constants_pkg.g_debug_level3);
137 END IF;
138
139 Pa_Fin_Plan_Utils.End_date_active_val
140 (p_start_date_active => p_effective_from,
141 p_end_date_active => p_effective_to,
142 x_return_status => l_return_status,
143 x_msg_count => l_msg_count,
144 x_msg_data => l_msg_data);
145
146 if (l_return_status = FND_API.G_RET_STS_ERROR or
147 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
148
149 l_any_error_occurred_flag := 'Y';
150 end if;
151
152 IF l_debug_mode = 'Y' THEN
153 pa_debug.g_err_stage:= 'Validating input parameters - Amount in buckets';
154 pa_debug.write(g_module_name,pa_debug.g_err_stage,
155 pa_fp_constants_pkg.g_debug_level3);
156 END IF;
157
158 validate_amount_in_buckets(
159 P_point1 => P_point1,
160 P_point2 => P_point2,
161 P_point3 => P_point3,
162 P_point4 => P_point4,
163 P_point5 => P_point5,
164 P_point6 => P_point6,
165 P_point7 => P_point7,
166 P_point8 => P_point8,
167 P_point9 => P_point9,
168 P_point10 => P_point10,
169 x_return_status => l_return_status,
170 x_msg_data => l_msg_data,
171 x_msg_count => l_msg_count);
172
173 if (l_return_status = FND_API.G_RET_STS_ERROR or
174 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
175
176 l_any_error_occurred_flag := 'Y';
177
178 end if;
179
180 l_msg_count := FND_MSG_PUB.count_msg;
181
182 if (l_any_error_occurred_flag = 'Y') then
183
184 IF l_debug_mode = 'Y' THEN
185 pa_debug.g_err_stage:= 'Error occured while validating parameters';
186 pa_debug.write(g_module_name,pa_debug.g_err_stage,
187 pa_fp_constants_pkg.g_debug_level3);
188 END IF;
189
190 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
191 else
192 RETURN;
193 end if;
194
195 EXCEPTION
196 WHEN pa_fp_constants_pkg.Invalid_Arg_Exc THEN
197
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 l_msg_count := FND_MSG_PUB.count_msg;
200
201 IF l_msg_count = 1 and x_msg_data IS NULL THEN
202 PA_INTERFACE_UTILS_PUB.get_messages
203 (p_encoded => FND_API.G_TRUE
204 ,p_msg_index => 1
205 ,p_msg_count => l_msg_count
206 ,p_msg_data => l_msg_data
207 ,p_data => l_data
208 ,p_msg_index_out => l_msg_index_out);
209 x_msg_data := l_data;
210 x_msg_count := l_msg_count;
211 ELSE
212 x_msg_count := l_msg_count;
213 END IF;
214
215 IF l_debug_mode = 'Y' THEN
216 pa_debug.reset_curr_function;
217 END IF;
218
219 RETURN;
220
221 WHEN others THEN
222
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 x_msg_count := 1;
225 x_msg_data := SQLERRM;
226
227 FND_MSG_PUB.add_exc_msg
228 ( p_pkg_name => 'PA_FP_SPREAD_CURVES_UTILS'
229 ,p_procedure_name => 'VALIDATE'
230 ,p_error_text => x_msg_data);
231
232 IF l_debug_mode = 'Y' THEN
233 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
234 pa_debug.write(g_module_name,pa_debug.g_err_stage,
235 pa_fp_constants_pkg.g_debug_level5);
236 pa_debug.reset_curr_function;
237 END IF;
238
239 RAISE;
240
241 END validate ;
242
243 /*==================================================================
244 This api validates the Spread Curve name for uniqueness.
245 ==================================================================*/
246
247 PROCEDURE validate_name
248 (p_name IN pa_spread_curves_tl.name%TYPE,
249 p_spread_curve_id IN pa_spread_curves_tl.spread_curve_id%TYPE,
250 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
251 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
252 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
253 is
254 l_exists VARCHAR2(1);
255 l_msg_count NUMBER;
256 l_msg_index_out NUMBER;
257 l_data VARCHAR2(2000);
258 l_msg_data VARCHAR2(2000);
259 l_debug_mode VARCHAR2(1) := Null;
260 begin
261
262 x_msg_count := 0;
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
265
266 IF l_debug_mode = 'Y' THEN
267 pa_debug.set_curr_function( p_function => 'Validate_Name',
268 p_debug_mode => l_debug_mode );
269 END IF;
270
271 IF l_debug_mode = 'Y' THEN
272 pa_debug.g_err_stage:= 'Validating Spread Curve Name uniqueness';
273 pa_debug.write(g_module_name,pa_debug.g_err_stage,
274 pa_fp_constants_pkg.g_debug_level3);
275 END IF;
276
277 if p_name is NULL then
278 /* Name must be entered */
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
281 p_msg_name => 'PA_MANDATORY_INFO_MISSING');
282
283 IF l_debug_mode = 'Y' THEN
284 pa_debug.g_err_stage:= 'Spread Curve Name is Null';
285 pa_debug.write(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level5);
286 END IF;
287
288 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
289 end if;
290
291 Begin
292 select 'Y'
293 into l_exists
294 from pa_spread_curves_vl
295 where upper(name) = upper(p_name)
296 and spread_curve_id <> nvl(p_spread_curve_id,-99)
297 and rownum < 2;
298
299 /* Duplicate Name should not be entered */
300
301 IF l_debug_mode = 'Y' THEN
302 pa_debug.g_err_stage:= 'Duplicate Spread Curve Name = '|| p_name;
303 pa_debug.write(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level5);
304 END IF;
305
306 x_return_status := FND_API.G_RET_STS_ERROR;
307
308 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
309 p_msg_name => 'PA_ALL_UNIQUE_NAME_EXISTS');
310
311 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
312
313 exception
314 when NO_DATA_FOUND then
315 null;
316 end;
317
318 RETURN;
319 EXCEPTION
320
321 WHEN pa_fp_constants_pkg.Invalid_Arg_Exc THEN
322
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 l_msg_count := FND_MSG_PUB.count_msg;
325
326 IF l_msg_count = 1 and x_msg_data IS NULL THEN
327 PA_INTERFACE_UTILS_PUB.get_messages
328 (p_encoded => FND_API.G_TRUE
329 ,p_msg_index => 1
330 ,p_msg_count => l_msg_count
331 ,p_msg_data => l_msg_data
332 ,p_data => l_data
333 ,p_msg_index_out => l_msg_index_out);
334 x_msg_data := l_data;
335 x_msg_count := l_msg_count;
336 ELSE
337 x_msg_count := l_msg_count;
338 END IF;
339
340 IF l_debug_mode = 'Y' THEN
341 pa_debug.reset_curr_function;
342 END IF;
343
344 RETURN;
345
346 WHEN others THEN
347
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 x_msg_count := 1;
350 x_msg_data := SQLERRM;
351
352 FND_MSG_PUB.add_exc_msg
353 ( p_pkg_name => 'PA_FP_SPREAD_CURVES_UTILS'
354 ,p_procedure_name => 'VALIDATE_NAME'
355 ,p_error_text => x_msg_data);
356
357 IF l_debug_mode = 'Y' THEN
358 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
359 pa_debug.write(g_module_name,pa_debug.g_err_stage,
360 pa_fp_constants_pkg.g_debug_level5);
361 pa_debug.reset_curr_function;
362 END IF;
363
364 RAISE;
365
366 END validate_name;
367
368 /*==================================================================
369 Amount in buckets total_weighting should not be < 0
370 ==================================================================*/
371
372 PROCEDURE validate_amount_in_buckets(
373 P_point1 IN Pa_spread_curves_b.point1%TYPE,
374 P_point2 IN Pa_spread_curves_b.point2%TYPE,
375 P_point3 IN Pa_spread_curves_b.point3%TYPE,
376 P_point4 IN Pa_spread_curves_b.point4%TYPE,
377 P_point5 IN Pa_spread_curves_b.point5%TYPE,
378 P_point6 IN Pa_spread_curves_b.point6%TYPE,
379 P_point7 IN Pa_spread_curves_b.point7%TYPE,
380 P_point8 IN Pa_spread_curves_b.point8%TYPE,
381 P_point9 IN Pa_spread_curves_b.point9%TYPE,
382 P_point10 IN Pa_spread_curves_b.point10%TYPE,
383 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
384 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
385 X_msg_count OUT NOCOPY number) --File.Sql.39 bug 4440895
386 IS
387 l_msg_count NUMBER;
388 l_msg_index_out NUMBER;
389 l_data VARCHAR2(2000);
390 l_msg_data VARCHAR2(2000);
391 l_debug_mode VARCHAR2(1);
392
393 begin
394 x_msg_count := 0;
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
397
398 IF l_debug_mode = 'Y' THEN
399 pa_debug.set_curr_function( p_function => 'validate_amount_in_buckets',
400 p_debug_mode => l_debug_mode );
401 END IF;
402
403 -- Check for business rules violations
404
405 IF l_debug_mode = 'Y' THEN
406 pa_debug.g_err_stage:= 'Validating Amount in buckets';
407 pa_debug.write(g_module_name,pa_debug.g_err_stage,
408 pa_fp_constants_pkg.g_debug_level3);
409 END IF;
410
411 if ( (nvl( p_point1 ,0) < 0 or (p_point1 <> ROUND(p_point1, 0 ))) or
412 (nvl( p_point2 ,0) < 0 or (p_point2 <> ROUND(p_point2, 0 ))) or
413 (nvl( p_point3 ,0) < 0 or (p_point3 <> ROUND(p_point3, 0 ))) or
414 (nvl( p_point4 ,0) < 0 or (p_point4 <> ROUND(p_point4, 0 ))) or
415 (nvl( p_point5 ,0) < 0 or (p_point5 <> ROUND(p_point5, 0 ))) or
416 (nvl( p_point6 ,0) < 0 or (p_point6 <> ROUND(p_point6, 0 ))) or
417 (nvl( p_point7 ,0) < 0 or (p_point7 <> ROUND(p_point7, 0 ))) or
418 (nvl( p_point8 ,0) < 0 or (p_point8 <> ROUND(p_point8, 0 ))) or
419 (nvl( p_point9 ,0) < 0 or (p_point9 <> ROUND(p_point9, 0 ))) or
420 (nvl( p_point10 ,0) < 0 or (p_point10 <> ROUND(p_point10, 0 )))
421 ) then
422
423 /* any point is not positive */
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
426 p_msg_name => 'PA_FP_SC_BUCK_VAL_LT_ZERO');
427
428 IF l_debug_mode = 'Y' THEN
429 pa_debug.g_err_stage:= 'Total Bucket Value is less than zero';
430 pa_debug.write(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level5);
431 END IF;
432
433 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
434
435 end if;
436
437 RETURN;
438 EXCEPTION
439
440 WHEN pa_fp_constants_pkg.Invalid_Arg_Exc THEN
441
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 l_msg_count := FND_MSG_PUB.count_msg;
444
445 IF l_msg_count = 1 and x_msg_data IS NULL THEN
446 PA_INTERFACE_UTILS_PUB.get_messages
447 (p_encoded => FND_API.G_TRUE
448 ,p_msg_index => 1
449 ,p_msg_count => l_msg_count
450 ,p_msg_data => l_msg_data
451 ,p_data => l_data
452 ,p_msg_index_out => l_msg_index_out);
453 x_msg_data := l_data;
454 x_msg_count := l_msg_count;
455 ELSE
456 x_msg_count := l_msg_count;
457 END IF;
458
459 IF l_debug_mode = 'Y' THEN
460 pa_debug.reset_curr_function;
461 END IF;
462
463 RETURN;
464
465 WHEN others THEN
466
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 x_msg_count := 1;
469 x_msg_data := SQLERRM;
470
471 FND_MSG_PUB.add_exc_msg
472 ( p_pkg_name => 'PA_FP_SPREAD_CURVES_UTILS'
473 ,p_procedure_name => 'VALIDATE_AMOUNT_IN_BUCKETS'
474 ,p_error_text => x_msg_data);
475
476 IF l_debug_mode = 'Y' THEN
477 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
478 pa_debug.write(g_module_name,pa_debug.g_err_stage,
479 pa_fp_constants_pkg.g_debug_level5);
480 pa_debug.reset_curr_function;
481 END IF;
482
483 RAISE;
484
485 END validate_amount_in_buckets;
486
487 END pa_fp_spread_curves_utils;