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