[Home] [Help]
PACKAGE BODY: APPS.BIS_ACTUAL_VALIDATE_PVT
Source
1 PACKAGE BODY BIS_ACTUAL_VALIDATE_PVT AS
2 /* $Header: BISVAVVB.pls 115.8 2003/01/27 13:35:04 mahrao ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVAVVB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for validating items in the ACTUALs record
13 REM | NOTES |
14 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
15 REM | parameters.
16 REM | |
17 REM |
18 REM +=======================================================================+
19 */
20 --
21 --
22 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_ACTUAL_VALIDATE_PVT';
23 --
24 PROCEDURE Validate_Target_Level_ID
25 ( p_api_version IN NUMBER
26 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
27 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
28 , x_return_status OUT NOCOPY VARCHAR2
29 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
30 )
31 IS
32 --
33 l_Target_Level_ID NUMBER;
34 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
35 l_Actual_Rec BIS_ACTUAL_PUB.Actual_Rec_Type;
36 l_Target_Level_Rec_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
37 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
38 --
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 -- dbms_output.put_line( 'p_ACTUAL_Rec.TARGET_LEVEL_ID = '
43 -- || p_ACTUAL_Rec.TARGET_LEVEL_ID
44 -- );
45
46 l_Actual_Rec := p_Actual_Rec;
47 --
48 IF( BIS_UTILITIES_PUB.Value_Missing(l_ACTUAL_Rec.TARGET_LEVEL_ID)
49 = FND_API.G_TRUE
50 ) THEN
51 RAISE FND_API.G_EXC_ERROR;
52 ELSE
53 l_target_level_rec.Target_Level_ID := l_actual_rec.Target_Level_ID;
54 l_Target_Level_rec_p := l_Target_Level_rec;
55 BIS_Target_Level_PVT.Retrieve_Target_Level
56 ( p_api_version => p_api_version
57 , p_Target_Level_rec => l_Target_Level_rec_p
58 , p_all_info => FND_API.G_FALSE
59 , x_Target_Level_rec => l_Target_Level_rec
60 , x_return_status => x_return_status
61 , x_error_Tbl => x_error_Tbl
62 );
63
64 /*
65 SELECT bisbv_target_levels.TARGET_LEVEL_ID
66 INTO l_Target_Level_ID
67 FROM bisbv_target_levels bisbv_target_levels
68 WHERE bisbv_target_levels.TARGET_LEVEL_ID = p_ACTUAL_Rec.TARGET_LEVEL_ID;
69 */
70 END IF;
71 --
72
73 --commented out NOCOPY RAISE
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 x_return_status:= FND_API.G_RET_STS_ERROR;
77 --RAISE FND_API.G_EXC_ERROR;
78 WHEN FND_API.G_EXC_ERROR THEN
79 x_return_status:= FND_API.G_RET_STS_ERROR;
80 -- RAISE;
81 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
82 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
83 --RAISE;
84 WHEN OTHERS THEN
85 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
86 l_error_tbl := x_error_Tbl;
87 BIS_UTILITIES_PVT.Add_Error_Message
88 ( p_error_table => l_error_Tbl
89 , p_error_msg_id => SQLCODE
90 , p_error_description => SQLERRM
91 , x_error_table => x_error_Tbl
92 );
93 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END Validate_Target_Level_ID;
95 --
96 --
97 /*
98 PROCEDURE Validate_Time_Level_Value_ID
99 ( p_api_version IN NUMBER
100 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
101 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
102 , x_return_status OUT NOCOPY VARCHAR2
103 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
104 )
105 IS
106 --
107 --l_bisbv_target_levels BISBV_TARGET_LEVELS%ROWTYPE;
108 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
109 l_Org_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
110 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
111 --
112 BEGIN
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114 --
115 IF( BIS_UTILITIES_PUB.Value_Missing(p_ACTUAL_Rec.Time_Level_Value_ID)
116 = FND_API.G_TRUE
117 ) THEN
118 RAISE FND_API.G_EXC_ERROR;
119 ELSE
120 -- do validation here
121 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
122
123 BIS_Target_Level_PVT.Retrieve_Target_Level
124 ( p_api_version => p_api_version
125 , p_Target_Level_rec => l_bisbv_target_levels
126 , p_all_info => FND_API.G_FALSE
127 , x_Target_Level_rec => l_bisbv_target_levels
128 , x_return_status => x_return_status
129 , x_error_Tbl => x_error_Tbl
130 );
131
132
133 SELECT *
134 INTO l_bisbv_TARGET_levels
135 FROM BISBV_TARGET_LEVELS bisbv_TARGET_levels
136 WHERE bisbv_TARGET_levels.TARGET_LEVEL_ID = p_ACTUAL_Rec.TARGET_Level_ID;
137
138 --
139 l_Dim_Level_Value_Rec.Dimension_Level_ID
140 := l_bisbv_TARGET_levels.TIME_LEVEL_ID;
141 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
142 := p_ACTUAL_Rec.Time_Level_Value_ID;
143 l_Org_Level_Value_Rec.Dimension_Level_Value_ID
144 := p_ACTUAL_Rec.Org_Level_Value_ID;
145 --
146 BIS_DIM_LEVEL_VALUE_PVT.Time_ID_to_Value
147 ( p_api_version => 1.0
148 , p_Org_Level_Value_Rec => l_Org_Level_Value_Rec
149 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
150 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
151 , x_return_status => x_return_status
152 , x_error_Tbl => x_error_Tbl
153 );
154 END IF;
155 --
156 --commented out NOCOPY RAISE
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 x_return_status:= FND_API.G_RET_STS_ERROR;
160 --RAISE FND_API.G_EXC_ERROR;
161 WHEN FND_API.G_EXC_ERROR THEN
162 x_return_status:= FND_API.G_RET_STS_ERROR;
163 --RAISE;
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
166 --RAISE;
167 WHEN OTHERS THEN
168 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
169 BIS_UTILITIES_PVT.Add_Error_Message
170 ( p_error_table => x_error_Tbl
171 , p_error_msg_id => SQLCODE
172 , p_error_description => SQLERRM
173 , x_error_table => x_error_Tbl
174 );
175 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END Validate_Time_Level_Value_ID;
177 --
178 --
179 PROCEDURE Validate_Org_Level_Value_ID
180 ( p_api_version IN NUMBER
181 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
182 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
183 , x_return_status OUT NOCOPY VARCHAR2
184 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
185 )
186 IS
187 --
188 --l_bisbv_TARGET_levels BISBV_TARGET_LEVELS%ROWTYPE;
189 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
190 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
191 --
192 BEGIN
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194 --
195 IF( BIS_UTILITIES_PUB.Value_Missing(p_ACTUAL_Rec.ORG_LEVEL_VALUE_ID)
196 = FND_API.G_TRUE
197 ) THEN
198 RAISE FND_API.G_EXC_ERROR;
199 ELSE
200 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
201
202 BIS_Target_Level_PVT.Retrieve_Target_Level
203 ( p_api_version => p_api_version
204 , p_Target_Level_rec => l_bisbv_target_levels
205 , p_all_info => FND_API.G_FALSE
206 , x_Target_Level_rec => l_bisbv_target_levels
207 , x_return_status => x_return_status
208 , x_error_Tbl => x_error_Tbl
209 );
210
211
212
213 SELECT *
214 INTO l_bisbv_TARGET_levels
215 FROM BISBV_TARGET_LEVELS bisbv_TARGET_levels
216 WHERE bisbv_TARGET_levels.TARGET_LEVEL_ID = p_ACTUAL_Rec.TARGET_Level_ID;
217
218 --
219 l_Dim_Level_Value_Rec.Dimension_Level_ID
220 := l_bisbv_TARGET_levels.ORG_LEVEL_ID;
221 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
222 := p_ACTUAL_Rec.Org_Level_Value_ID;
223 --
224 BIS_DIM_LEVEL_VALUE_PVT.Org_ID_to_Value
225 ( p_api_version => 1.0
226 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
227 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
228 , x_return_status => x_return_status
229 , x_error_Tbl => x_error_Tbl
230 );
231 END IF;
232 --
233
234 --commented out NOCOPY RAISE
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 x_return_status:= FND_API.G_RET_STS_ERROR;
238 --RAISE FND_API.G_EXC_ERROR;
239 WHEN FND_API.G_EXC_ERROR THEN
240 x_return_status:= FND_API.G_RET_STS_ERROR;
241 -- RAISE;
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
244 -- RAISE;
245 WHEN OTHERS THEN
246 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
247 BIS_UTILITIES_PVT.Add_Error_Message
248 ( p_error_table => x_error_Tbl
249 , p_error_msg_id => SQLCODE
250 , p_error_description => SQLERRM
251 , x_error_table => x_error_Tbl
252 );
253 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END Validate_Org_Level_Value_ID;
255 */
256 --
257 --
258 PROCEDURE Validate_Dim1_Level_Value_ID
259 ( p_api_version IN NUMBER
260 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
261 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
262 , x_return_status OUT NOCOPY VARCHAR2
263 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
264 )
265 IS
266 --
267 --l_bisbv_TARGET_levels BISBV_TARGET_LEVELS%ROWTYPE;
268 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
269 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
270 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
271 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
272 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
273 --
274 BEGIN
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276 --
277 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
278 l_bisbv_target_levels_p := l_bisbv_target_levels;
279 BIS_Target_Level_PVT.Retrieve_Target_Level
280 ( p_api_version => p_api_version
281 , p_Target_Level_rec => l_bisbv_target_levels_p
282 , p_all_info => FND_API.G_FALSE
283 , x_Target_Level_rec => l_bisbv_target_levels
284 , x_return_status => x_return_status
285 , x_error_Tbl => x_error_Tbl
286 );
287
288 /*
289 SELECT *
290 INTO l_bisbv_TARGET_levels
291 FROM BISBV_TARGET_LEVELS bisbv_TARGET_levels
292 WHERE bisbv_TARGET_levels.TARGET_LEVEL_ID = p_ACTUAL_Rec.TARGET_Level_ID;
293 */
294 --
295 IF(l_bisbv_TARGET_levels.DIMENSION1_LEVEL_ID IS NOT NULL) THEN
296 l_Dim_Level_Value_Rec.Dimension_Level_ID
297 := l_bisbv_TARGET_levels.DIMENSION1_LEVEL_ID;
298 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
299 := p_ACTUAL_Rec.Dim1_Level_Value_ID;
300 --
301 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
302 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
303 ( p_api_version => 1.0
304 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
305 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
306 , x_return_status => x_return_status
307 , x_error_Tbl => x_error_Tbl
308 );
309 END IF;
310 --
311 --commented out NOCOPY RAISE
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 x_return_status:= FND_API.G_RET_STS_ERROR;
315 --RAISE FND_API.G_EXC_ERROR;
316 WHEN FND_API.G_EXC_ERROR THEN
317 x_return_status:= FND_API.G_RET_STS_ERROR;
318 -- RAISE;
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
321 --RAISE;
322 WHEN OTHERS THEN
323 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
324 l_error_tbl := x_error_Tbl;
325 BIS_UTILITIES_PVT.Add_Error_Message
326 ( p_error_table => l_error_Tbl
327 , p_error_msg_id => SQLCODE
328 , p_error_description => SQLERRM
329 , x_error_table => x_error_Tbl
330 );
331 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END Validate_Dim1_Level_Value_ID;
333 --
334 --
335 PROCEDURE Validate_Dim2_Level_Value_ID
336 ( p_api_version IN NUMBER
337 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
338 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
339 , x_return_status OUT NOCOPY VARCHAR2
340 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
341 )
342 IS
343 --
344 --l_bisbv_TARGET_levels BISBV_TARGET_LEVELS%ROWTYPE;
345 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
346 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
347 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
348 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
349 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
350 --
351 BEGIN
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 --
354 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
355
356 l_bisbv_target_levels_p := l_bisbv_target_levels;
357 BIS_Target_Level_PVT.Retrieve_Target_Level
358 ( p_api_version => p_api_version
359 , p_Target_Level_rec => l_bisbv_target_levels_p
360 , p_all_info => FND_API.G_FALSE
361 , x_Target_Level_rec => l_bisbv_target_levels
362 , x_return_status => x_return_status
363 , x_error_Tbl => x_error_Tbl
364 );
365
366 /*
367
368 SELECT *
369 INTO l_bisbv_TARGET_levels
370 FROM BISBV_TARGET_LEVELS bisbv_TARGET_levels
371 WHERE bisbv_TARGET_levels.TARGET_LEVEL_ID = p_ACTUAL_Rec.TARGET_Level_ID;
372 */
373 --
374 IF(l_bisbv_TARGET_levels.DIMENSION2_LEVEL_ID IS NOT NULL) THEN
375 l_Dim_Level_Value_Rec.Dimension_Level_ID
376 := l_bisbv_TARGET_levels.DIMENSION2_LEVEL_ID;
377 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
378 := p_ACTUAL_Rec.Dim2_Level_Value_ID;
379 --
380 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
381 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
382 ( p_api_version => 1.0
383 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
384 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
385 , x_return_status => x_return_status
386 , x_error_Tbl => x_error_Tbl
387 );
388 END IF;
389 --
390
391 --commented out NOCOPY RAISE
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 x_return_status:= FND_API.G_RET_STS_ERROR;
395 --RAISE FND_API.G_EXC_ERROR;
396 WHEN FND_API.G_EXC_ERROR THEN
397 x_return_status:= FND_API.G_RET_STS_ERROR;
398 -- RAISE;
399 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
400 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
401 --RAISE;
402 WHEN OTHERS THEN
403 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
407 , p_error_msg_id => SQLCODE
404 l_error_tbl := x_error_Tbl;
405 BIS_UTILITIES_PVT.Add_Error_Message
406 ( p_error_table => l_error_Tbl
408 , p_error_description => SQLERRM
409 , x_error_table => x_error_Tbl
410 );
411 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END Validate_Dim2_Level_Value_ID;
413 --
414 --
415 PROCEDURE Validate_Dim3_Level_Value_ID
416 ( p_api_version IN NUMBER
417 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
418 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
419 , x_return_status OUT NOCOPY VARCHAR2
420 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
421 )
422 IS
423 --
424 --l_bisbv_TARGET_levels bisbv_TARGET_LEVELS%ROWTYPE;
425 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
426 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
427 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
428 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
429 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
430 --
431 BEGIN
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433 --
434 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
435
436 l_bisbv_target_levels_p := l_bisbv_target_levels;
437 BIS_Target_Level_PVT.Retrieve_Target_Level
438 ( p_api_version => p_api_version
439 , p_Target_Level_rec => l_bisbv_target_levels_p
440 , p_all_info => FND_API.G_FALSE
441 , x_Target_Level_rec => l_bisbv_target_levels
442 , x_return_status => x_return_status
443 , x_error_Tbl => x_error_Tbl
444 );
445
446 /*
447 SELECT *
448 INTO l_bisbv_TARGET_levels
449 FROM bisbv_TARGET_LEVELS bisbv_TARGET_levels
450 WHERE bisbv_TARGET_levels.TARGET_level_ID = p_ACTUAL_Rec.TARGET_level_ID;
451 */
452 --
453 IF(l_bisbv_TARGET_levels.DIMENSION3_LEVEL_ID IS NOT NULL) THEN
454 l_Dim_Level_Value_Rec.Dimension_Level_ID
455 := l_bisbv_TARGET_levels.DIMENSION3_LEVEL_ID;
456 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
457 := p_ACTUAL_Rec.Dim3_Level_Value_ID;
458 --
459 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
460 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
461 ( p_api_version => 1.0
462 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
463 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
464 , x_return_status => x_return_status
465 , x_error_Tbl => x_error_Tbl
466 );
467 END IF;
468 --
469
470 --commented out NOCOPY RAISE
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 x_return_status:= FND_API.G_RET_STS_ERROR;
474 --RAISE FND_API.G_EXC_ERROR;
475 WHEN FND_API.G_EXC_ERROR THEN
476 x_return_status:= FND_API.G_RET_STS_ERROR;
477 -- RAISE;
478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
479 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
480 --RAISE;
481 WHEN OTHERS THEN
482 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
483 l_error_tbl := x_error_Tbl;
484 BIS_UTILITIES_PVT.Add_Error_Message
485 ( p_error_table => l_error_Tbl
486 , p_error_msg_id => SQLCODE
487 , p_error_description => SQLERRM
488 , x_error_table => x_error_Tbl
489 );
490 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END Validate_Dim3_Level_Value_ID;
492 --
493 --
494 PROCEDURE Validate_Dim4_Level_Value_ID
495 ( p_api_version IN NUMBER
496 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
497 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
498 , x_return_status OUT NOCOPY VARCHAR2
499 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
500 )
501 IS
502 --
503 --l_bisbv_TARGET_levels bisbv_TARGET_LEVELS%ROWTYPE;
504 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
505 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
506 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
507 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
508 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
509 --
510 BEGIN
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512 --
513 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
514
515 l_bisbv_target_levels_p := l_bisbv_target_levels;
516 BIS_Target_Level_PVT.Retrieve_Target_Level
517 ( p_api_version => p_api_version
518 , p_Target_Level_rec => l_bisbv_target_levels_p
519 , p_all_info => FND_API.G_FALSE
520 , x_Target_Level_rec => l_bisbv_target_levels
521 , x_return_status => x_return_status
522 , x_error_Tbl => x_error_Tbl
523 );
524
525 /*
526 SELECT *
527 INTO l_bisbv_TARGET_levels
528 FROM bisbv_TARGET_LEVELS bisbv_TARGET_levels
529 WHERE bisbv_TARGET_levels.TARGET_level_ID = p_ACTUAL_Rec.TARGET_level_ID;
530 */
531 --
535 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
532 IF(l_bisbv_TARGET_levels.DIMENSION4_LEVEL_ID IS NOT NULL) THEN
533 l_Dim_Level_Value_Rec.Dimension_Level_ID
534 := l_bisbv_TARGET_levels.DIMENSION4_LEVEL_ID;
536 := p_ACTUAL_Rec.Dim4_Level_Value_ID;
537 --
538 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
539 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
540 ( p_api_version => 1.0
541 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
542 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
543 , x_return_status => x_return_status
544 , x_error_Tbl => x_error_Tbl
545 );
546 END IF;
547 --
548 --commented out NOCOPY RAISE
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 x_return_status:= FND_API.G_RET_STS_ERROR;
552 --RAISE FND_API.G_EXC_ERROR;
553 WHEN FND_API.G_EXC_ERROR THEN
554 x_return_status:= FND_API.G_RET_STS_ERROR;
555 --RAISE;
556 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
557 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
558 --RAISE;
559 WHEN OTHERS THEN
560 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
561 l_error_tbl := x_error_Tbl;
562 BIS_UTILITIES_PVT.Add_Error_Message
563 ( p_error_table => l_error_Tbl
564 , p_error_msg_id => SQLCODE
565 , p_error_description => SQLERRM
566 , x_error_table => x_error_Tbl
567 );
568 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 END Validate_Dim4_Level_Value_ID;
570 --
571 --
572 PROCEDURE Validate_Dim5_Level_Value_ID
573 ( p_api_version IN NUMBER
574 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
575 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
576 , x_return_status OUT NOCOPY VARCHAR2
577 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
578 )
579 IS
580 --
581 --l_bisbv_TARGET_levels bisbv_TARGET_LEVELS%ROWTYPE;
582 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
583 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
584 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
585 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
586 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
587 --
588 BEGIN
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590 --
591 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
592
593 l_bisbv_target_levels_p := l_bisbv_target_levels;
594 BIS_Target_Level_PVT.Retrieve_Target_Level
595 ( p_api_version => p_api_version
596 , p_Target_Level_rec => l_bisbv_target_levels_p
597 , p_all_info => FND_API.G_FALSE
598 , x_Target_Level_rec => l_bisbv_target_levels
599 , x_return_status => x_return_status
600 , x_error_Tbl => x_error_Tbl
601 );
602
603 /*
604 SELECT *
605 INTO l_bisbv_TARGET_levels
606 FROM bisbv_TARGET_LEVELS bisbv_TARGET_levels
607 WHERE bisbv_TARGET_levels.TARGET_level_ID = p_ACTUAL_Rec.TARGET_level_ID;
608 */
609 --
610 IF(l_bisbv_TARGET_levels.DIMENSION5_LEVEL_ID IS NOT NULL) THEN
611 l_Dim_Level_Value_Rec.Dimension_Level_ID
612 := l_bisbv_TARGET_levels.DIMENSION5_LEVEL_ID;
613 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
614 := p_ACTUAL_Rec.Dim5_Level_Value_ID;
615 --
616 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
617 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
618 ( p_api_version => 1.0
619 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
620 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
621 , x_return_status => x_return_status
622 , x_error_Tbl => x_error_Tbl
623 );
624 END IF;
625 --
626
627 --commented out NOCOPY RAISE
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 x_return_status:= FND_API.G_RET_STS_ERROR;
631 -- RAISE FND_API.G_EXC_ERROR;
632 WHEN FND_API.G_EXC_ERROR THEN
633 x_return_status:= FND_API.G_RET_STS_ERROR;
634 -- RAISE;
635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
636 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
637 -- RAISE;
638 WHEN OTHERS THEN
639 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
640 l_error_tbl := x_error_Tbl;
641 BIS_UTILITIES_PVT.Add_Error_Message
642 ( p_error_table => l_error_Tbl
643 , p_error_msg_id => SQLCODE
644 , p_error_description => SQLERRM
645 , x_error_table => x_error_Tbl
646 );
647 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END Validate_Dim5_Level_Value_ID;
649 --
650 --
651 PROCEDURE Validate_Dim6_Level_Value_ID
652 ( p_api_version IN NUMBER
653 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
654 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
655 , x_return_status OUT NOCOPY VARCHAR2
656 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
657 )
658 IS
659 --
660 --l_bisbv_TARGET_levels bisbv_TARGET_LEVELS%ROWTYPE;
661 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
665 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
662 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
663 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
664 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
666 --
667 BEGIN
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669 --
670 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
671
672 l_bisbv_target_levels_p := l_bisbv_target_levels;
673 BIS_Target_Level_PVT.Retrieve_Target_Level
674 ( p_api_version => p_api_version
675 , p_Target_Level_rec => l_bisbv_target_levels_p
676 , p_all_info => FND_API.G_FALSE
677 , x_Target_Level_rec => l_bisbv_target_levels
678 , x_return_status => x_return_status
679 , x_error_Tbl => x_error_Tbl
680 );
681
682 /*
683 SELECT *
684 INTO l_bisbv_TARGET_levels
685 FROM bisbv_TARGET_LEVELS bisbv_TARGET_levels
686 WHERE bisbv_TARGET_levels.TARGET_level_ID = p_ACTUAL_Rec.TARGET_level_ID;
687 */
688 --
689 IF(l_bisbv_TARGET_levels.DIMENSION6_LEVEL_ID IS NOT NULL) THEN
690 l_Dim_Level_Value_Rec.Dimension_Level_ID
691 := l_bisbv_TARGET_levels.DIMENSION6_LEVEL_ID;
692 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
693 := p_ACTUAL_Rec.Dim6_Level_Value_ID;
694 --
695 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
696 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
697 ( p_api_version => 1.0
698 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
699 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
700 , x_return_status => x_return_status
701 , x_error_Tbl => x_error_Tbl
702 );
703 END IF;
704 --
705
706 --commented out NOCOPY RAISE
707 EXCEPTION
708 WHEN NO_DATA_FOUND THEN
709 x_return_status:= FND_API.G_RET_STS_ERROR;
710 --RAISE FND_API.G_EXC_ERROR;
711 WHEN FND_API.G_EXC_ERROR THEN
712 x_return_status:= FND_API.G_RET_STS_ERROR;
713 --RAISE;
714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
715 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
716 --RAISE;
717 WHEN OTHERS THEN
718 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
719 l_error_tbl := x_error_Tbl;
720 BIS_UTILITIES_PVT.Add_Error_Message
721 ( p_error_table => l_error_Tbl
722 , p_error_msg_id => SQLCODE
723 , p_error_description => SQLERRM
724 , x_error_table => x_error_Tbl
725 );
726 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 END Validate_Dim6_Level_Value_ID;
728 --
729 --
730 PROCEDURE Validate_Dim7_Level_Value_ID
731 ( p_api_version IN NUMBER
732 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
733 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
734 , x_return_status OUT NOCOPY VARCHAR2
735 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
736 )
737 IS
738 --
739 --l_bisbv_TARGET_levels bisbv_TARGET_LEVELS%ROWTYPE;
740 l_bisbv_target_levels BIS_Target_Level_PUB.Target_Level_Rec_Type;
741 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
742 l_bisbv_target_levels_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
743 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
744 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
745 --
746 BEGIN
747 x_return_status := FND_API.G_RET_STS_SUCCESS;
748 --
749 l_bisbv_target_levels.Target_Level_ID := p_actual_rec.Target_Level_ID;
750
751 l_bisbv_target_levels_p := l_bisbv_target_levels;
752 BIS_Target_Level_PVT.Retrieve_Target_Level
753 ( p_api_version => p_api_version
754 , p_Target_Level_rec => l_bisbv_target_levels_p
755 , p_all_info => FND_API.G_FALSE
756 , x_Target_Level_rec => l_bisbv_target_levels
757 , x_return_status => x_return_status
758 , x_error_Tbl => x_error_Tbl
759 );
760
761 /*
762 SELECT *
763 INTO l_bisbv_TARGET_levels
764 FROM bisbv_TARGET_LEVELS bisbv_TARGET_levels
765 WHERE bisbv_TARGET_levels.TARGET_level_ID = p_ACTUAL_Rec.TARGET_level_ID;
766 */
767 --
768 IF(l_bisbv_TARGET_levels.DIMENSION7_LEVEL_ID IS NOT NULL) THEN
769 l_Dim_Level_Value_Rec.Dimension_Level_ID
770 := l_bisbv_TARGET_levels.DIMENSION7_LEVEL_ID;
771 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
772 := p_ACTUAL_Rec.Dim7_Level_Value_ID;
773 --
774 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
775 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
776 ( p_api_version => 1.0
777 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
778 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
779 , x_return_status => x_return_status
780 , x_error_Tbl => x_error_Tbl
781 );
782 END IF;
783 --
784
785 --commented out NOCOPY RAISE
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 x_return_status:= FND_API.G_RET_STS_ERROR;
789 --RAISE FND_API.G_EXC_ERROR;
790 WHEN FND_API.G_EXC_ERROR THEN
791 x_return_status:= FND_API.G_RET_STS_ERROR;
795 --RAISE;
792 --RAISE;
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
796 WHEN OTHERS THEN
797 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
798 l_error_tbl := x_error_Tbl;
799 BIS_UTILITIES_PVT.Add_Error_Message
800 ( p_error_table => l_error_Tbl
801 , p_error_msg_id => SQLCODE
802 , p_error_description => SQLERRM
803 , x_error_table => x_error_Tbl
804 );
805 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END Validate_Dim7_Level_Value_ID;
807 --
808 --
809 PROCEDURE Validate_ACTUAL_Value
810 ( p_api_version IN NUMBER
811 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
812 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
813 , x_return_status OUT NOCOPY VARCHAR2
814 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
815 )
816 IS
817 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
818 BEGIN
819 x_return_status := FND_API.G_RET_STS_SUCCESS;
820 --
821 IF( BIS_UTILITIES_PUB.Value_Missing(p_ACTUAL_Rec.ACTUAL)
822 = FND_API.G_TRUE
823 ) THEN
824 RAISE FND_API.G_EXC_ERROR;
825 ELSE
826 NULL;
827 END IF;
828 --
829 --commented out NOCOPY RAISE
830 EXCEPTION
831 WHEN FND_API.G_EXC_ERROR THEN
832 x_return_status:= FND_API.G_RET_STS_ERROR;
833 --RAISE;
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
836 --RAISE;
837 WHEN OTHERS THEN
838 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
839 l_error_tbl := x_error_Tbl;
840 BIS_UTILITIES_PVT.Add_Error_Message
841 ( p_error_table => l_error_Tbl
842 , p_error_msg_id => SQLCODE
843 , p_error_description => SQLERRM
844 , x_error_table => x_error_Tbl
845 );
846 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
847 END Validate_ACTUAL_Value;
848 --
849 --
850 PROCEDURE Validate_Record
851 ( p_api_version IN NUMBER
852 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
853 , p_ACTUAL_Rec IN BIS_ACTUAL_PUB.ACTUAL_Rec_Type
854 , x_return_status OUT NOCOPY VARCHAR2
855 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
856 )
857 IS
858 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
859 BEGIN
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861 --
862 -- commented out NOCOPY as they are changed now with the ranges being %
863 -- dont know exact validations yet
864 /*
865 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE1_LOW)
866 = FND_API.G_TRUE
867 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE1_HIGH)
868 = FND_API.G_TRUE
869 AND p_ACTUAL_Rec.RANGE1_LOW > p_ACTUAL_Rec.RANGE1_HIGH
870 ) THEN
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
873 --
874 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE2_LOW)
875 = FND_API.G_TRUE
876 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE2_HIGH)
877 = FND_API.G_TRUE
878 AND p_ACTUAL_Rec.RANGE2_LOW > p_ACTUAL_Rec.RANGE2_HIGH
879 ) THEN
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882 --
883 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE3_LOW)
884 = FND_API.G_TRUE
885 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_ACTUAL_Rec.RANGE3_HIGH)
886 = FND_API.G_TRUE
887 AND p_ACTUAL_Rec.RANGE3_LOW > p_ACTUAL_Rec.RANGE3_HIGH
888 ) THEN
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 */
892 --
893 --commented out NOCOPY RAISE
894 EXCEPTION
895 WHEN FND_API.G_EXC_ERROR THEN
896 x_return_status:= FND_API.G_RET_STS_ERROR;
897 --RAISE;
898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
899 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
900 --RAISE;
901 WHEN OTHERS THEN
905 ( p_error_table => l_error_Tbl
902 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
903 l_error_tbl := x_error_Tbl;
904 BIS_UTILITIES_PVT.Add_Error_Message
906 , p_error_msg_id => SQLCODE
907 , p_error_description => SQLERRM
908 , x_error_table => x_error_Tbl
909 );
910 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END Validate_Record;
912 --
913 --
914 END BIS_ACTUAL_VALIDATE_PVT;