[Home] [Help]
PACKAGE BODY: APPS.BIS_TARGET_PVT
Source
1 PACKAGE BODY BIS_TARGET_PVT AS
2 /* $Header: BISVTARB.pls 120.1 2006/04/10 07:59:05 psomesul noship $ */
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 | BISVTARB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for creating and managing Targets for the |
13 REM | Key Performance Framework. |
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | 02-DEC-98 irchen Creation |
18 REM | 20-JAN-02 sashaik Added Retrieve_Org_level_value for 1740789 |
19 REM | 10-JAN-2003 rchandra for bug 2715432 , changed OUT parameter |
20 REM | x_Target_Level_Rec , x_Target_Rec to IN OUT |
21 REM | in API RETRIEVE_TARGET_FROM_SHNMS |
22 REM | and x_Target_Rec in API Value_ID_Conversion |
23 REM | 15-JAN-2003 mahrao for 2744792 |
24 REM | removed the BIS_UTILITIES_PUB.Value_Not_Missing |
25 REM | condition in Update_db_Target for owners and |
26 REM | tolerance ranges as user might want to update |
27 REM | them to NULL. |
28 REM | 23-JAN-03 sugopal For having different local variables for IN and OUT |
29 REM | parameters (bug#2758428) |
30 REM | 23-JUL-03 sashaik For bug 3064592: related to computing function id |
31 REM | and setting targets dynamically. |
32 REM | 26-JUL-04 ankgoel Bug#3756093 Returned role_id for role_short_names in|
33 REM | retrieve_targets API |
34 REM | 13-JAN-05 vtulasi Bug#4102897 - Change in size of variables |
35 REM | 21-MAR-05 ankagarw Bug#4235732 - changing count(*) to count(1) |
36 REM | 10-APR-05 psomesul Bug#5140269 - PERFORMANCE ISSUE WITH TARGET OWNER |
37 REM | LOV IN PMF PAGES - replaced WF_ROLES with WF_ROLE_LOV_VL |
38 REM +=======================================================================+
39 */
40
41 --
42 --
43 -- queries database to retrieve the target from the database
44 -- updates the record with the changes sent in
45 PROCEDURE Update_db_Target
46 ( p_Target_Rec BIS_TARGET_PUB.Target_Rec_Type
47 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
48 , x_return_status OUT NOCOPY VARCHAR2
49 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
50 );
51 --
52 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
53 -- by null
54 PROCEDURE Create_db_Target
55 ( p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
56 , x_Target_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
57 );
58
59 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
60 -- by null
61 --
62 PROCEDURE SetNULL
63 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
64 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
65 );
66
67 --
68 PROCEDURE SetNULL
69 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
70 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
71 )
72 IS
73 BEGIN
74
75 x_dimension_level_rec.Dimension_ID
76 := BIS_UTILITIES_PUB.G_NULL_NUM;
77 x_dimension_level_rec.Dimension_Short_Name
78 := BIS_UTILITIES_PUB.G_NULL_CHAR;
79 x_dimension_level_rec.Dimension_Name
80 := BIS_UTILITIES_PUB.G_NULL_CHAR;
81 x_dimension_level_rec.Dimension_Level_ID
82 := BIS_UTILITIES_PUB.G_NULL_NUM;
83 x_dimension_level_rec.Dimension_Level_Short_Name
84 := BIS_UTILITIES_PUB.G_NULL_CHAR;
85 x_dimension_level_rec.Dimension_Level_Name
86 := BIS_UTILITIES_PUB.G_NULL_CHAR;
87 x_dimension_level_rec.Description
88 := BIS_UTILITIES_PUB.G_NULL_CHAR;
89 x_dimension_level_rec.Level_Values_View_Name
90 := BIS_UTILITIES_PUB.G_NULL_CHAR;
91 x_dimension_level_rec.where_Clause
92 := BIS_UTILITIES_PUB.G_NULL_CHAR;
93 x_dimension_level_rec.source
94 := BIS_UTILITIES_PUB.G_NULL_CHAR;
95
96 EXCEPTION
97 WHEN FND_API.G_EXC_ERROR THEN
98 RAISE
99 ;
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 RAISE;
102 WHEN OTHERS THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104
105 END SetNULL;
106 -- Defines one target for a specific set of dimension values for
107 -- one target level
108 PROCEDURE Create_Target
109 ( p_api_version IN NUMBER
110 , p_is_dbimeasure IN NUMBER := 0 --added by gbhaloti #3148615
111 , p_commit IN VARCHAR2 := FND_API.G_FALSE
112 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
113 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
116 )
117 IS
118 --
119 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
120 l_count NUMBER;
121 l_user_id NUMBER;
122 l_login_id NUMBER;
123 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
124
125 --
126 BEGIN
127 --
128 l_target_rec := p_target_rec;
129
130 -- dbms_output.put_line( ' Inside pvt : 11 ' );
131
132 Validate_Target
133 ( p_api_version => p_api_version
134 , p_is_dbimeasure => p_is_dbimeasure --added by gbhaloti #3148615
135 , p_validation_level => p_validation_level
136 , p_Target_Rec => p_Target_Rec
137 , x_return_status => x_return_status
138 , x_error_Tbl => x_error_Tbl
139 );
140 --
141
142 -- dbms_output.put_line( ' Inside pvt : 12 ' );
143
144 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 --
148
149 Create_db_Target
150 ( p_Target_Rec => p_Target_Rec
151 , x_Target_Rec => l_Target_Rec
152 );
153
154 -- dbms_output.put_line( ' Inside pvt : 13 ' );
155
156 --
157 -- validate that record does not exist already
158 SELECT COUNT(1)
159 INTO l_count
160 FROM BISBV_TARGETS bisbv_targets
161 WHERE bisbv_targets.TARGET_LEVEL_ID = l_Target_Rec.Target_Level_ID
162 AND bisbv_targets.PLAN_ID = l_Target_Rec.Plan_ID
163 AND NVL(bisbv_targets.ORG_LEVEL_VALUE_ID, 'Y')
164 = NVL(l_Target_Rec.Org_Level_Value_ID, 'Y')
165 AND NVL(bisbv_targets.TIME_LEVEL_VALUE_ID, 'Y')
166 = NVL(l_Target_Rec.Time_Level_Value_ID, 'Y')
167 AND NVL(bisbv_targets.DIM1_LEVEL_VALUE_ID, 'Y')
168 = NVL(l_Target_Rec.Dim1_Level_Value_ID, 'Y')
169 AND NVL(bisbv_targets.DIM2_LEVEL_VALUE_ID, 'Y')
170 = NVL(l_Target_Rec.Dim2_Level_Value_ID, 'Y')
171 AND NVL(bisbv_targets.DIM3_LEVEL_VALUE_ID, 'Y')
172 = NVL(l_Target_Rec.Dim3_Level_Value_ID, 'Y')
173 AND NVL(bisbv_targets.DIM4_LEVEL_VALUE_ID, 'Y')
174 = NVL(l_Target_Rec.Dim4_Level_Value_ID, 'Y')
175 AND NVL(bisbv_targets.DIM5_LEVEL_VALUE_ID, 'Y')
176 = NVL(l_Target_Rec.Dim5_Level_Value_ID, 'Y')
177 AND NVL(bisbv_targets.DIM6_LEVEL_VALUE_ID, 'Y')
178 = NVL(l_Target_Rec.Dim6_Level_Value_ID, 'Y')
179 AND NVL(bisbv_targets.DIM7_LEVEL_VALUE_ID, 'Y')
180 = NVL(l_Target_Rec.Dim7_Level_Value_ID, 'Y');
181
182 -- dbms_output.put_line( ' Inside pvt : 14 ' );
183
184 --
185 IF(l_count <> 0) THEN
186 --added more params
187 l_error_tbl := x_error_tbl;
188 BIS_UTILITIES_PVT.Add_Error_Message
189 ( p_error_msg_name => 'BIS_RECORD_EXISTS'
190 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
191 , p_error_proc_name => 'BIS_TARGET_PVT.Create_Target'
192 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
193 , p_error_table => l_error_tbl
194 , x_error_table => x_error_tbl
195 );
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 -- dbms_output.put_line( ' Inside pvt : 15 ' );
200
201 --
202 l_user_id := fnd_global.USER_ID;
203 l_login_id := fnd_global.LOGIN_ID;
204 --
205 INSERT INTO bis_target_values
206 ( TARGET_ID
207 , TARGET_LEVEL_ID
208 , PLAN_ID
209 , ORG_LEVEL_VALUE
210 , TIME_LEVEL_VALUE
211 , DIMENSION1_LEVEL_VALUE
212 , DIMENSION2_LEVEL_VALUE
213 , DIMENSION3_LEVEL_VALUE
214 , DIMENSION4_LEVEL_VALUE
215 , DIMENSION5_LEVEL_VALUE
216 , DIMENSION6_LEVEL_VALUE
217 , DIMENSION7_LEVEL_VALUE
218 , TARGET
219 , RANGE1_LOW
220 , RANGE1_HIGH
221 , RANGE2_LOW
222 , RANGE2_HIGH
223 , RANGE3_LOW
224 , RANGE3_HIGH
225 , ROLE1_ID
226 , ROLE1
227 , ROLE2_ID
228 , ROLE2
229 , ROLE3_ID
230 , ROLE3
231 , CREATION_DATE
232 , CREATED_BY
233 , LAST_UPDATE_DATE
234 , LAST_UPDATED_BY
235 , LAST_UPDATE_LOGIN
236 )
237 VALUES
238 ( bis_target_values_s.NEXTVAL
239 , l_Target_Rec.Target_Level_ID
240 , l_Target_Rec.Plan_ID
241 , l_Target_Rec.Org_Level_Value_ID
242 , l_Target_Rec.Time_Level_Value_ID
243 , l_Target_Rec.Dim1_Level_Value_ID
244 , l_Target_Rec.Dim2_Level_Value_ID
245 , l_Target_Rec.Dim3_Level_Value_ID
246 , l_Target_Rec.Dim4_Level_Value_ID
247 , l_Target_Rec.Dim5_Level_Value_ID
248 , l_Target_Rec.Dim6_Level_Value_ID
249 , l_Target_Rec.Dim7_Level_Value_ID
250 , l_Target_Rec.Target
251 , l_Target_Rec.Range1_low
252 , l_Target_Rec.Range1_high
253 , l_Target_Rec.Range2_low
254 , l_Target_Rec.Range2_high
255 , l_Target_Rec.Range3_low
256 , l_Target_Rec.Range3_high
257 , l_Target_Rec.Notify_Resp1_ID
258 , l_Target_Rec.Notify_Resp1_Short_Name
259 , l_Target_Rec.Notify_Resp2_ID
260 , l_Target_Rec.Notify_Resp2_Short_Name
261 , l_Target_Rec.Notify_Resp3_ID
262 , l_Target_Rec.Notify_Resp3_Short_Name
263 , SYSDATE
264 , l_user_id
265 , SYSDATE
266 , l_user_id
267 , l_login_id
268 );
269
270 -- dbms_output.put_line( ' Inside pvt : 16 ' );
271
272 --
273 --added this
274 if (p_commit = FND_API.G_TRUE) then
275 COMMIT;
276 end if;
277
278 -- dbms_output.put_line( ' Inside pvt : 17 ' );
279
280 --commented RAISE
281 EXCEPTION
282 WHEN FND_API.G_EXC_ERROR THEN
283 x_return_status:= FND_API.G_RET_STS_ERROR;
284 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
285 htp.p('BIS_TARGET_PVT.Create_Target:G_EXC_ERROR'); htp.para;
286 END IF;
287 --RAISE;
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
290 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
291 htp.p('BIS_TARGET_PVT.Create_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
292 END IF;
293 --RAISE;
294 WHEN OTHERS THEN
295 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
296 l_error_Tbl := x_error_Tbl;
297 BIS_UTILITIES_PVT.Add_Error_Message
298 ( p_error_table => l_error_Tbl
299 , p_error_msg_id => SQLCODE
300 , p_error_description => SQLERRM
301 , x_error_table => x_error_Tbl
302 );
303 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
304 htp.p('BIS_TARGET_PVT.Create_Target:OTHERS'); htp.para;
305 END IF;
306 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 --
308 END Create_Target;
309 --
310 --
311 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
312 -- by null
313 PROCEDURE Create_db_Target
314 ( p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
315 , x_Target_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
316 )
317 IS
318 BEGIN
319 x_Target_Rec := p_Target_Rec;
320 x_Target_Rec.Target_Level_ID
321 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Target_Level_ID);
322
323 -- dbms_output.put_line( ' Inside pvt : 21 ' );
324
325 x_Target_Rec.Plan_ID
326 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Plan_ID);
327
328 -- dbms_output.put_line( ' Inside pvt : 22 ' );
329
330 x_Target_Rec.Org_Level_Value_ID
331 := BIS_UTILITIES_PVT.CheckMissChar
332 (p_char => x_Target_Rec.Org_Level_Value_ID);
333 x_Target_Rec.Time_Level_Value_ID
334 := BIS_UTILITIES_PVT.CheckMissChar
335 (p_char => x_Target_Rec.Time_Level_Value_ID);
336 x_Target_Rec.Dim1_Level_Value_ID
337 := BIS_UTILITIES_PVT.CheckMissChar
338 (p_char => x_Target_Rec.Dim1_Level_Value_ID);
339 x_Target_Rec.Dim2_Level_Value_ID
340 := BIS_UTILITIES_PVT.CheckMissChar
341 (p_char => x_Target_Rec.Dim2_Level_Value_ID);
342 x_Target_Rec.Dim3_Level_Value_ID
343 := BIS_UTILITIES_PVT.CheckMissChar
344 (p_char => x_Target_Rec.Dim3_Level_Value_ID);
345
346 -- dbms_output.put_line( ' Inside pvt : 23' );
347
348 x_Target_Rec.Dim4_Level_Value_ID
349 := BIS_UTILITIES_PVT.CheckMissChar
350 (p_char => x_Target_Rec.Dim4_Level_Value_ID);
351 x_Target_Rec.Dim5_Level_Value_ID
352 := BIS_UTILITIES_PVT.CheckMissChar
353 (p_char => x_Target_Rec.Dim5_Level_Value_ID);
354 x_Target_Rec.Dim6_Level_Value_ID
355 := BIS_UTILITIES_PVT.CheckMissChar
356 (p_char => x_Target_Rec.Dim6_Level_Value_ID);
357 x_Target_Rec.Dim7_Level_Value_ID
358 := BIS_UTILITIES_PVT.CheckMissChar
359 (p_char => x_Target_Rec.Dim7_Level_Value_ID);
360 x_Target_Rec.Target
361 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Target);
362 x_Target_Rec.Range1_low
363 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range1_low);
364 x_Target_Rec.Range1_high
365 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range1_high);
366
367 -- dbms_output.put_line( ' Inside pvt : 24 ' );
368
369 x_Target_Rec.Range2_low
370 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range2_low);
371 x_Target_Rec.Range2_high
372 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range2_high);
373 x_Target_Rec.Range3_low
374 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range3_low);
375 x_Target_Rec.Range3_high
376 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Range3_high);
377 x_Target_Rec.Notify_Resp1_ID
378 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Notify_Resp1_ID);
379 x_Target_Rec.Notify_Resp1_Short_Name
380 := BIS_UTILITIES_PVT.CheckMissChar
381 (p_char => x_Target_Rec.Notify_Resp1_Short_Name);
382 x_Target_Rec.Notify_Resp2_ID
383 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Notify_Resp2_ID);
384 x_Target_Rec.Notify_Resp2_Short_Name
385 := BIS_UTILITIES_PVT.CheckMissChar
386 (p_char => x_Target_Rec.Notify_Resp2_Short_Name);
387 x_Target_Rec.Notify_Resp3_ID
388 := BIS_UTILITIES_PVT.CheckMissNum(p_num => x_Target_Rec.Notify_Resp3_ID);
389 x_Target_Rec.Notify_Resp3_Short_Name
390 := BIS_UTILITIES_PVT.CheckMissChar
391 (p_char => x_Target_Rec.Notify_Resp3_Short_Name);
392
393 -- dbms_output.put_line( ' Inside pvt : 25 ' );
394
395 END Create_db_Target;
396 --
397 -- retrieve information for all taRGEts of the given target level
398 -- if information about dimension values are not required, set all_info
399 -- to FALSE.
400 PROCEDURE Retrieve_Targets
401 ( p_api_version IN NUMBER
402 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
403 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
404 , x_Target_Tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_Type
405 , x_return_status OUT NOCOPY VARCHAR2
406 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
407 )
408 IS
409 --
410 CURSOR c_all_targets(p_Target_Level_ID IN NUMBER) IS
411 SELECT *
412 FROM bisfv_targets bisfv_targets
413 WHERE bisfv_targets.TARGET_LEVEL_ID = p_Target_Level_ID;
414 --
415 l_ind NUMBER := 0;
416 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
417 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
418
419 --
420 BEGIN
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422 --
423 -- BIS_UTILITIES_PUB.put_line(p_text => ' ............................. ' );
424
425 FOR cr IN c_all_targets
426 (p_Target_Level_ID => p_Target_Level_Rec.TARGET_LEVEL_ID) LOOP
427 l_ind := l_ind + 1;
428 x_Target_Tbl(l_ind).Target_ID := cr.Target_ID;
429 x_Target_Tbl(l_ind).Target_Level_ID := cr.Target_Level_ID;
430 x_Target_Tbl(l_ind).Target_Level_Short_Name
431 := cr.Target_Level_Short_Name;
432 x_Target_Tbl(l_ind).Target_Level_Name := cr.Target_Level_Name;
433 x_Target_Tbl(l_ind).Plan_ID := cr.Plan_ID;
434 x_Target_Tbl(l_ind).Plan_Short_Name := cr.Plan_Short_Name;
435 x_Target_Tbl(l_ind).Plan_Name := cr.Plan_Name;
436 X_Target_Tbl(l_ind).Org_Level_Value_ID := cr.Org_Level_Value_ID;
437 X_Target_Tbl(l_ind).Time_Level_Value_ID := cr.Time_Level_Value_ID;
438 X_Target_Tbl(l_ind).Dim1_Level_Value_ID := cr.Dim1_Level_Value_ID;
439 x_Target_Tbl(l_ind).Dim2_Level_Value_ID := cr.Dim2_Level_Value_ID;
440 x_Target_Tbl(l_ind).Dim3_Level_Value_ID := cr.Dim3_Level_Value_ID;
441 x_Target_Tbl(l_ind).Dim4_Level_Value_ID := cr.Dim4_Level_Value_ID;
442 x_Target_Tbl(l_ind).Dim5_Level_Value_ID := cr.Dim5_Level_Value_ID;
443 x_Target_Tbl(l_ind).Dim6_Level_Value_ID := cr.Dim6_Level_Value_ID;
444 x_Target_Tbl(l_ind).Dim7_Level_Value_ID := cr.Dim7_Level_Value_ID;
445 x_Target_Tbl(l_ind).Target := cr.Target;
446 x_Target_Tbl(l_ind).Range1_low := cr.Range1_low;
447 x_Target_Tbl(l_ind).Range1_high := cr.Range1_high;
448 x_Target_Tbl(l_ind).Range2_low := cr.Range2_low;
449 x_Target_Tbl(l_ind).Range2_high := cr.Range2_high;
450 x_Target_Tbl(l_ind).Range3_low := cr.Range3_low;
451 x_Target_Tbl(l_ind).Range3_high := cr.Range3_high;
452 x_Target_Tbl(l_ind).Notify_Resp1_ID := NVL(cr.Notify_Resp1_ID, BIS_UTILITIES_PVT.get_role_id(cr.Notify_Resp1_Short_Name));
453 x_Target_Tbl(l_ind).Notify_Resp1_Short_Name := cr.Notify_Resp1_Short_Name;
454 x_Target_Tbl(l_ind).Notify_Resp1_Name := cr.Notify_Resp1_Name;
455 x_Target_Tbl(l_ind).Notify_Resp2_ID := NVL(cr.Notify_Resp2_ID, BIS_UTILITIES_PVT.get_role_id(cr.Notify_Resp2_Short_Name));
456 x_Target_Tbl(l_ind).Notify_Resp2_Short_Name := cr.Notify_Resp2_Short_Name;
457 x_Target_Tbl(l_ind).Notify_Resp2_Name := cr.Notify_Resp2_Name;
458 x_Target_Tbl(l_ind).Notify_Resp3_ID := NVL(cr.Notify_Resp3_ID, BIS_UTILITIES_PVT.get_role_id(cr.Notify_Resp3_Short_Name));
459 x_Target_Tbl(l_ind).Notify_Resp3_Short_Name := cr.Notify_Resp3_Short_Name;
460 x_Target_Tbl(l_ind).Notify_Resp3_Name := cr.Notify_Resp3_Name;
461
462 -- BIS_UTILITIES_PUB.put_line(p_text => ' Target id = ' || cr.Target_ID ) ;
463
464 -- if numeric target is missing, get the computed target
465 --
466 IF ((BIS_UTILITIES_PUB.Value_Missing(x_Target_tbl(l_ind).Target)
467 = FND_API.G_TRUE)
468 OR (BIS_UTILITIES_PUB.Value_Null(x_Target_tbl(l_ind).Target)
469 = FND_API.G_TRUE))
470 THEN
471 IF ((BIS_UTILITIES_PUB.Value_Missing
472 (p_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE)
473 OR (BIS_UTILITIES_PUB.Value_Null
474 (p_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE))
475 THEN
476 BIS_Target_Level_PVT.Retrieve_Target_Level
477 ( p_api_version => p_api_version
478 , p_Target_Level_Rec => p_Target_Level_Rec
479 , p_all_info => FND_API.G_FALSE
480 , x_Target_Level_Rec => l_Target_Level_Rec
481 , x_return_status => x_return_status
482 , x_error_Tbl => x_error_Tbl
483 );
484 ELSE
485 l_target_level_rec := p_target_level_rec;
486 END IF;
487
488 -- only compute target if found computing fn id
489 --
490 IF ((BIS_UTILITIES_PUB.Value_Not_Missing
491 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE)
492 AND (BIS_UTILITIES_PUB.Value_Not_Null
493 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE))
494 THEN
495 x_target_tbl(l_ind).target :=
496 Get_Target
497 ( p_computing_function_id => l_target_level_rec.computing_function_id
498 , p_target_rec => x_target_tbl(l_ind)
499 );
500 END IF;
501 END IF;
502
503 END LOOP;
504 -- BIS_UTILITIES_PUB.put_line(p_text => ' ............................. ' );
505 --
506
507 --added this check
508 if(l_ind = 0) then
509 l_error_tbl := x_error_tbl;
510 BIS_UTILITIES_PVT.Add_Error_Message
511 ( p_error_msg_name => 'BIS_INVALID_TAR_LEVEL_ID'
512 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
513 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Targets'
514 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
515 , p_error_table => l_error_tbl
516 , x_error_table => x_error_tbl
517 );
518 RAISE FND_API.G_EXC_ERROR;
519 end if;
520
521 --commented RAISE
522 EXCEPTION
523 WHEN FND_API.G_EXC_ERROR THEN
524 x_return_status:= FND_API.G_RET_STS_ERROR;
525 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
526 htp.p('BIS_TARGET_PVT.Retrieve_Targets:G_EXC_ERROR'); htp.para;
527 END IF;
528 --RAISE;
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
531 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
532 htp.p('BIS_TARGET_PVT.Retrieve_Targets:G_EXC_UNEXPECTED_ERROR');
533 htp.para;
534 END IF;
535 --RAISE;
536 WHEN OTHERS THEN
537 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
538 l_error_Tbl := x_error_Tbl;
539 BIS_UTILITIES_PVT.Add_Error_Message
540 ( p_error_table => l_error_Tbl
541 , p_error_msg_id => SQLCODE
542 , p_error_description => SQLERRM
543 , x_error_table => x_error_Tbl
544 );
545 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
546 htp.p('BIS_TARGET_PVT.Retrieve_Targets:OTHERS'); htp.para;
547 END IF;
548 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
549 --
550 END Retrieve_Targets;
551 --
552 --
553 -- retrieve information for one target
554 -- If information about dimension values are not required, set all_info
555 -- to FALSE.
556 PROCEDURE Retrieve_Target
557 ( p_api_version IN NUMBER
558 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
559 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
560 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
561 , x_return_status OUT NOCOPY VARCHAR2
562 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
563 )
564 IS
565 --
566 l_bisfv_targets_rec bisfv_targets%ROWTYPE;
567 l_bisbv_target_levels_rec bisbv_target_levels%ROWTYPE;
568 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
569 l_plan_id NUMBER;
570 l_Business_Plan_Rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
571 l_org_level_value_id VARCHAR2(250);
572 l_time_level_value_id VARCHAR2(250);
573 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
574
575 l_Business_Plan_Rec_p BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
576 l_Target_Level_Rec_p BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
577 --
578 BEGIN
579
580 x_target_Rec := p_target_rec;
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582 --
583 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Target_ID)
584 = FND_API.G_TRUE
585 AND p_Target_Rec.Target_ID IS NOT NULL
586 ) THEN
587 SELECT *
588 INTO l_bisfv_targets_rec
589 FROM bisfv_targets bisfv_targets
590 WHERE bisfv_targets.TARGET_ID = p_Target_Rec.Target_ID;
591
592
593 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Target_Level_ID)
594 = FND_API.G_TRUE
595 AND p_Target_Rec.Target_Level_ID IS NOT NULL
596 ) THEN
597 SELECT *
598 INTO l_bisbv_target_levels_rec
599 FROM bisbv_target_levels bisbv_target_levels
600 WHERE bisbv_target_levels.TARGET_LEVEL_ID
601 = p_Target_Rec.Target_Level_ID;
602
603 --
604 --
605 --Removed check for org and time
606
607
608 IF(l_bisbv_target_levels_rec.DIMENSION1_LEVEL_ID IS NOT NULL) THEN
609 IF( BIS_UTILITIES_PUB.Value_Missing
610 (p_Target_Rec.Dim1_Level_Value_ID)
611 = FND_API.G_TRUE
612 OR p_Target_Rec.Dim1_Level_Value_ID IS NULL
613 ) THEN
614
615 --added Error Msg--------
616 l_error_tbl := x_error_tbl;
617 BIS_UTILITIES_PVT.Add_Error_Message
618 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
619 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
620 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
621 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
622 , p_error_table => l_error_tbl
623 , x_error_table => x_error_tbl
624 );
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
628 (p_Target_Rec.Dim1_Level_Value_ID)
629 = FND_API.G_TRUE
630 AND p_Target_Rec.Dim1_Level_Value_ID IS NOT NULL
631 ) THEN
632
633 --added Error Msg--------
634 l_error_tbl := x_error_tbl;
635 BIS_UTILITIES_PVT.Add_Error_Message
636 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
637 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
638 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
639 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
640 , p_error_table => l_error_tbl
641 , x_error_table => x_error_tbl
642 );
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645 --
646 IF(l_bisbv_target_levels_rec.DIMENSION2_LEVEL_ID IS NOT NULL) THEN
647 IF( BIS_UTILITIES_PUB.Value_Missing
648 (p_Target_Rec.Dim2_Level_Value_ID)
649 = FND_API.G_TRUE
650 OR p_Target_Rec.Dim2_Level_Value_ID IS NULL
651 ) THEN
652 --added Error Msg--------
653 l_error_tbl := x_error_tbl;
654 BIS_UTILITIES_PVT.Add_Error_Message
655 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
656 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
657 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
658 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
659 , p_error_table => l_error_tbl
660 , x_error_table => x_error_tbl
661 );
662 RAISE FND_API.G_EXC_ERROR;
663 END IF;
664 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
665 (p_Target_Rec.Dim2_Level_Value_ID)
666 = FND_API.G_TRUE
667 AND p_Target_Rec.Dim2_Level_Value_ID IS NOT NULL
668 ) THEN
669 --added Error Msg--------
670 l_error_tbl := x_error_tbl;
671 BIS_UTILITIES_PVT.Add_Error_Message
672 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
673 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
674 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
675 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
676 , p_error_table => l_error_tbl
677 , x_error_table => x_error_tbl
678 );
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681 --
682 IF(l_bisbv_target_levels_rec.DIMENSION3_LEVEL_ID IS NOT NULL) THEN
683 IF( BIS_UTILITIES_PUB.Value_Missing
684 (p_Target_Rec.Dim3_Level_Value_ID)
685 = FND_API.G_TRUE
686 OR p_Target_Rec.Dim3_Level_Value_ID IS NULL
687 ) THEN
688 --added Error Msg--------
689 l_error_tbl := x_error_tbl;
690 BIS_UTILITIES_PVT.Add_Error_Message
691 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
692 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
693 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
694 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
695 , p_error_table => l_error_tbl
696 , x_error_table => x_error_tbl
697 );
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
701 (p_Target_Rec.Dim3_Level_Value_ID)
702 = FND_API.G_TRUE
703 AND p_Target_Rec.Dim3_Level_Value_ID IS NOT NULL
704 )
705 THEN
706 --added Error Msg--------
707 l_error_tbl := x_error_tbl;
708 BIS_UTILITIES_PVT.Add_Error_Message
709 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
710 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
711 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
712 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
713 , p_error_table => l_error_tbl
714 , x_error_table => x_error_tbl
715 );
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718 --
719 IF(l_bisbv_target_levels_rec.DIMENSION4_LEVEL_ID IS NOT NULL) THEN
720 IF( BIS_UTILITIES_PUB.Value_Missing
721 (p_Target_Rec.Dim4_Level_Value_ID)
722 = FND_API.G_TRUE
723 OR p_Target_Rec.Dim4_Level_Value_ID IS NULL
724 ) THEN
725 --added Error Msg--------
726 l_error_tbl := x_error_tbl;
727 BIS_UTILITIES_PVT.Add_Error_Message
728 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
729 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
730 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
731 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
732 , p_error_table => l_error_tbl
733 , x_error_table => x_error_tbl
734 );
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
738 (p_Target_Rec.Dim4_Level_Value_ID)
739 = FND_API.G_TRUE
740 AND p_Target_Rec.Dim4_Level_Value_ID IS NOT NULL
741 ) THEN
742 --added Error Msg--------
743 l_error_tbl := x_error_tbl;
744 BIS_UTILITIES_PVT.Add_Error_Message
745 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
746 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
747 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
748 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
749 , p_error_table => l_error_tbl
750 , x_error_table => x_error_tbl
751 );
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754 --
755 IF(l_bisbv_target_levels_rec.DIMENSION5_LEVEL_ID IS NOT NULL) THEN
756 IF( BIS_UTILITIES_PUB.Value_Missing
757 (p_Target_Rec.Dim5_Level_Value_ID)
758 = FND_API.G_TRUE
759 OR p_Target_Rec.Dim5_Level_Value_ID IS NULL
760 ) THEN
761 --added Error Msg--------
762 l_error_tbl := x_error_tbl;
763 BIS_UTILITIES_PVT.Add_Error_Message
764 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
765 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
766 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
767 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
768 , p_error_table => l_error_tbl
769 , x_error_table => x_error_tbl
770 );
771 RAISE FND_API.G_EXC_ERROR;
772 END IF;
773 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
774 (p_Target_Rec.Dim5_Level_Value_ID)
775 = FND_API.G_TRUE
776 AND p_Target_Rec.Dim5_Level_Value_ID IS NOT NULL
777 ) THEN
778 --added Error Msg--------
779 l_error_tbl := x_error_tbl;
780 BIS_UTILITIES_PVT.Add_Error_Message
781 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
782 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
783 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
784 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
785 , p_error_table => l_error_tbl
786 , x_error_table => x_error_tbl
787 );
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790 --
791 IF(l_bisbv_target_levels_rec.DIMENSION6_LEVEL_ID IS NOT NULL) THEN
792 IF( BIS_UTILITIES_PUB.Value_Missing
793 (p_Target_Rec.Dim6_Level_Value_ID)
794 = FND_API.G_TRUE
795 OR p_Target_Rec.Dim6_Level_Value_ID IS NULL
796 ) THEN
797 --added Error Msg--------
798 l_error_tbl := x_error_tbl;
799 BIS_UTILITIES_PVT.Add_Error_Message
800 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
801 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
802 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
803 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
804 , p_error_table => l_error_tbl
805 , x_error_table => x_error_tbl
806 );
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
810 (p_Target_Rec.Dim6_Level_Value_ID)
811 = FND_API.G_TRUE
812 AND p_Target_Rec.Dim6_Level_Value_ID IS NOT NULL
813 ) THEN
814 --added Error Msg--------
815 l_error_tbl := x_error_tbl;
816 BIS_UTILITIES_PVT.Add_Error_Message
817 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
818 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
819 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
820 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
821 , p_error_table => l_error_tbl
822 , x_error_table => x_error_tbl
823 );
824 RAISE FND_API.G_EXC_ERROR;
825 END IF;
826 --
827 --
828 IF(l_bisbv_target_levels_rec.DIMENSION7_LEVEL_ID IS NOT NULL) THEN
829 IF( BIS_UTILITIES_PUB.Value_Missing
830 (p_Target_Rec.Dim7_Level_Value_ID)
831 = FND_API.G_TRUE
832 OR p_Target_Rec.Dim7_Level_Value_ID IS NULL
833 ) THEN
834 --added Error Msg--------
835 l_error_tbl := x_error_tbl;
836 BIS_UTILITIES_PVT.Add_Error_Message
837 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
838 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
839 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
840 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
841 , p_error_table => l_error_tbl
842 , x_error_table => x_error_tbl
843 );
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing
847 (p_Target_Rec.Dim7_Level_Value_ID)
848 = FND_API.G_TRUE
849 AND p_Target_Rec.Dim7_Level_Value_ID IS NOT NULL
850 ) THEN
851 --added Error Msg--------
852 l_error_tbl := x_error_tbl;
853 BIS_UTILITIES_PVT.Add_Error_Message
854 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
855 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
856 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
857 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
858 , p_error_table => l_error_tbl
859 , x_error_table => x_error_tbl
860 );
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863 --
864
865
866
867 ---If Plan Id is not given, get Plan Id from Short name
868
869 if (BIS_UTILITIES_PUB.Value_Missing(p_Target_Rec.Plan_ID)
870 = FND_API.G_TRUE) then
871 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Plan_Short_Name)
872 = FND_API.G_TRUE) then
873 l_Business_Plan_Rec.Business_Plan_Short_Name := p_Target_Rec.Plan_Short_Name;
874 l_Business_Plan_Rec_p := l_Business_Plan_Rec;
875 BIS_BUSINESS_PLAN_PVT.Value_ID_Conversion
876 ( p_api_version => p_api_version
877 , p_Business_Plan_Rec => l_Business_Plan_Rec_p
878 , x_Business_Plan_Rec => l_Business_Plan_Rec
879 , x_return_status => x_return_status
880 , x_error_Tbl => x_error_tbl
881 );
882 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
883 l_plan_id := l_Business_Plan_Rec.Business_Plan_ID;
884 end if;
885 end if;
886 else
887 l_plan_id := p_Target_Rec.Plan_ID ;
888 end if;
889
890 if(BIS_UTILITIES_PUB.Value_Missing(p_Target_Rec.Org_Level_Value_ID) = FND_API.G_TRUE)
891 then l_org_level_value_id := NULL;
892 else
893 l_org_level_value_id := p_Target_Rec.Org_Level_Value_ID;
894 end if;
895
896
897 if(BIS_UTILITIES_PUB.Value_Missing(p_Target_Rec.Time_Level_Value_ID) = FND_API.G_TRUE)
898 then l_time_level_value_id := NULL;
899 else
900 l_time_level_value_id := p_Target_Rec.Time_Level_Value_ID;
901 end if;
902 --------------------------------------------
903
904
905
906 SELECT *
907 INTO l_bisfv_targets_rec
908 FROM bisfv_targets bisfv_targets
909 WHERE bisfv_targets.TARGET_LEVEL_ID = p_Target_Rec.Target_Level_ID
910 -- used to be p_Target_Rec.Plan_ID
911 AND bisfv_targets.PLAN_ID = l_plan_id
912
913 ---changed org and time logic
914 AND (l_org_level_value_id IS NULL
915 OR NVL(bisfv_targets.ORG_LEVEL_VALUE_ID,'T') = NVL(l_org_level_value_id, 'T'))
916
917 AND (l_time_level_value_id IS NULL
918 OR NVL(bisfv_targets.TIME_LEVEL_VALUE_ID,'T') = NVL(l_time_level_value_id, 'T'))
919
920 AND NVL(bisfv_targets.DIM1_LEVEL_VALUE_ID, 'T')
921 = DECODE( p_Target_Rec.Dim1_Level_Value_ID
922 , FND_API.G_MISS_CHAR
923 , 'T'
924 , NVL(p_Target_Rec.Dim1_Level_Value_ID, 'T')
925 )
926 AND NVL(bisfv_targets.DIM2_LEVEL_VALUE_ID, 'T')
927 = DECODE( p_Target_Rec.Dim2_Level_Value_ID
928 , FND_API.G_MISS_CHAR
929 , 'T'
930 , NVL(p_Target_Rec.Dim2_Level_Value_ID, 'T')
931 )
932 AND NVL(bisfv_targets.DIM3_LEVEL_VALUE_ID, 'T')
933 = DECODE( p_Target_Rec.Dim3_Level_Value_ID
934 , FND_API.G_MISS_CHAR
935 , 'T'
936 , NVL(p_Target_Rec.Dim3_Level_Value_ID, 'T')
937 )
938 AND NVL(bisfv_targets.DIM4_LEVEL_VALUE_ID, 'T')
939 = DECODE( p_Target_Rec.Dim4_Level_Value_ID
940 , FND_API.G_MISS_CHAR
941 , 'T'
942 , NVL(p_Target_Rec.Dim4_Level_Value_ID, 'T')
943 )
944 AND NVL(bisfv_targets.DIM5_LEVEL_VALUE_ID, 'T')
945 = DECODE( p_Target_Rec.Dim5_Level_Value_ID
946 , FND_API.G_MISS_CHAR
947 , 'T'
948 , NVL(p_Target_Rec.Dim5_Level_Value_ID, 'T')
949 )
950 AND NVL(bisfv_targets.DIM6_LEVEL_VALUE_ID, 'T')
951 = DECODE( p_Target_Rec.Dim6_Level_Value_ID
952 , FND_API.G_MISS_CHAR
953 , 'T'
954 , NVL(p_Target_Rec.Dim6_Level_Value_ID, 'T')
955 )
956 AND NVL(bisfv_targets.DIM7_LEVEL_VALUE_ID, 'T')
957 = DECODE( p_Target_Rec.Dim7_Level_Value_ID
958 , FND_API.G_MISS_CHAR
959 , 'T'
960 , NVL(p_Target_Rec.Dim7_Level_Value_ID, 'T')
961 )
962 ;
963 END IF;
964 --
965 x_Target_Rec.Target_ID := l_bisfv_targets_rec.Target_ID;
966 x_Target_Rec.Target_Level_ID := l_bisfv_targets_rec.Target_Level_ID;
967 x_Target_Rec.Target_Level_Short_Name
968 := l_bisfv_targets_rec.Target_Level_Short_Name;
969 x_Target_Rec.Target_Level_Name
970 := l_bisfv_targets_rec.Target_Level_Name;
971 x_Target_Rec.Plan_ID := l_bisfv_targets_rec.Plan_ID;
972 x_Target_Rec.Plan_Short_Name := l_bisfv_targets_rec.Plan_Short_Name;
973 x_Target_Rec.Plan_Name := l_bisfv_targets_rec.Plan_Name;
974 x_Target_Rec.Org_Level_Value_ID
975 := l_bisfv_targets_rec.Org_Level_Value_ID;
976 x_Target_Rec.Time_Level_Value_ID
977 := l_bisfv_targets_rec.Time_Level_Value_ID;
978 x_Target_Rec.Dim1_Level_Value_ID
979 := l_bisfv_targets_rec.Dim1_Level_Value_ID;
980 x_Target_Rec.Dim2_Level_Value_ID
981 := l_bisfv_targets_rec.Dim2_Level_Value_ID;
982 x_Target_Rec.Dim3_Level_Value_ID
983 := l_bisfv_targets_rec.Dim3_Level_Value_ID;
984 x_Target_Rec.Dim4_Level_Value_ID
985 := l_bisfv_targets_rec.Dim4_Level_Value_ID;
986 x_Target_Rec.Dim5_Level_Value_ID
987 := l_bisfv_targets_rec.Dim5_Level_Value_ID;
988 x_Target_Rec.Dim6_Level_Value_ID
989 := l_bisfv_targets_rec.Dim6_Level_Value_ID;
990 x_Target_Rec.Dim7_Level_Value_ID
991 := l_bisfv_targets_rec.Dim7_Level_Value_ID;
992 x_Target_Rec.Target := l_bisfv_targets_rec.Target;
993 x_Target_Rec.Range1_low := l_bisfv_targets_rec.Range1_low;
994 x_Target_Rec.Range1_high := l_bisfv_targets_rec.Range1_high;
995 x_Target_Rec.Range2_low := l_bisfv_targets_rec.Range2_low;
996 x_Target_Rec.Range2_high := l_bisfv_targets_rec.Range2_high;
997 x_Target_Rec.Range3_low := l_bisfv_targets_rec.Range3_low;
998 x_Target_Rec.Range3_high := l_bisfv_targets_rec.Range3_high;
999 x_Target_Rec.Notify_Resp1_ID := NVL(l_bisfv_targets_rec.Notify_Resp1_ID, BIS_UTILITIES_PVT.get_role_id(l_bisfv_targets_rec.Notify_Resp1_Short_Name));
1000 x_Target_Rec.Notify_Resp1_Short_Name
1001 := l_bisfv_targets_rec.Notify_Resp1_Short_Name;
1002 x_Target_Rec.Notify_Resp1_Name := l_bisfv_targets_rec.Notify_Resp1_Name;
1003 x_Target_Rec.Notify_Resp2_ID := NVL(l_bisfv_targets_rec.Notify_Resp2_ID, BIS_UTILITIES_PVT.get_role_id(l_bisfv_targets_rec.Notify_Resp2_Short_Name));
1004 x_Target_Rec.Notify_Resp2_Short_Name
1005 := l_bisfv_targets_rec.Notify_Resp2_Short_Name;
1006 x_Target_Rec.Notify_Resp2_Name := l_bisfv_targets_rec.Notify_Resp2_Name;
1007 x_Target_Rec.Notify_Resp3_ID := NVL(l_bisfv_targets_rec.Notify_Resp3_ID, BIS_UTILITIES_PVT.get_role_id(l_bisfv_targets_rec.Notify_Resp3_Short_Name));
1008 x_Target_Rec.Notify_Resp3_Short_Name
1009 := l_bisfv_targets_rec.Notify_Resp3_Short_Name;
1010 x_Target_Rec.Notify_Resp3_Name := l_bisfv_targets_rec.Notify_Resp3_Name;
1011
1012 -- if numeric target is missing, get the computed target
1013 --
1014 IF ((BIS_UTILITIES_PUB.Value_Missing(x_Target_Rec.Target) = FND_API.G_TRUE)
1015 OR (BIS_UTILITIES_PUB.Value_Null(x_Target_Rec.Target) = FND_API.G_TRUE))
1016 THEN
1017 IF ((BIS_UTILITIES_PUB.Value_Not_Missing
1018 (x_Target_Rec.target_level_ID) = FND_API.G_TRUE)
1019 AND (BIS_UTILITIES_PUB.Value_Not_Null
1020 (x_Target_Rec.target_level_ID) = FND_API.G_TRUE))
1021 THEN
1022 l_Target_Level_Rec.target_level_ID := x_Target_Rec.target_level_ID;
1023 l_Target_Level_Rec_p := l_Target_Level_Rec;
1024 BIS_Target_Level_PVT.Retrieve_Target_Level
1025 ( p_api_version => p_api_version
1026 , p_Target_Level_Rec => l_Target_Level_Rec_p
1027 , p_all_info => FND_API.G_FALSE
1028 , x_Target_Level_Rec => l_Target_Level_Rec
1029 , x_return_status => x_return_status
1030 , x_error_Tbl => x_error_Tbl
1031 );
1032 END IF;
1033
1034 -- only compute target if found computing fn id
1035 --
1036 IF ((BIS_UTILITIES_PUB.Value_Not_Missing
1037 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE)
1038 AND (BIS_UTILITIES_PUB.Value_Not_Null
1039 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE))
1040 THEN
1041 x_target_rec.target :=
1042 Get_Target
1043 ( p_computing_function_id => l_target_level_rec.computing_function_id
1044 , p_target_rec => x_target_rec
1045 );
1046 END IF;
1047 END IF;
1048
1049 --
1050 --commented RAISE
1051 EXCEPTION
1052 --added NO DATA FOUND
1053 WHEN NO_DATA_FOUND THEN
1054 --added this error message
1055
1056 l_error_tbl := x_error_tbl;
1057 BIS_UTILITIES_PVT.Add_Error_Message
1058 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
1059 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1060 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target'
1061 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1062 , p_error_table => l_error_tbl
1063 , x_error_table => x_error_tbl
1064 );
1065 x_return_status := FND_API.G_RET_STS_ERROR ;
1066 WHEN FND_API.G_EXC_ERROR THEN
1067 x_return_status:= FND_API.G_RET_STS_ERROR;
1068 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1069 htp.p('BIS_TARGET_PVT.Retrieve_Target:G_EXC_ERROR'); htp.para;
1070 END IF;
1071 -- RAISE;
1072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1073 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1074 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1075 htp.p('BIS_TARGET_PVT.Retrieve_Target:G_EXC_UNEXPECTED_ERROR');
1076 htp.para;
1077 END IF;
1078 -- RAISE;
1079 WHEN OTHERS THEN
1080 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1081 l_error_Tbl := x_error_Tbl;
1082 BIS_UTILITIES_PVT.Add_Error_Message
1083 ( p_error_table => l_error_Tbl
1084 , p_error_msg_id => SQLCODE
1085 , p_error_description => SQLERRM
1086 , x_error_table => x_error_Tbl
1087 );
1088 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1089 htp.p('BIS_TARGET_PVT.Retrieve_Target:OTHERS'); htp.para;
1090 END IF;
1091 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092 --
1093 END Retrieve_Target;
1094 --
1095 --
1096 -- retrieves the owners for one target
1097 -- If information about dimension values are not required, set all_info
1098 -- to FALSE.
1099 --
1100 PROCEDURE Retrieve_Target_Owners
1101 ( p_api_version IN NUMBER
1102 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
1103 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
1104 , x_Target_Owners_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Owners_Rec_Type
1105 , x_return_status OUT NOCOPY VARCHAR2
1106 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1107 )
1108 IS
1109
1110 l_Target_owners_rec BIS_TARGET_PUB.Target_Owners_Rec_Type;
1111 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
1112 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1113 l_Target_Rec_p BIS_TARGET_PUB.Target_Rec_Type;
1114
1115 BEGIN
1116
1117 l_target_rec := p_target_rec;
1118 l_target_owners_rec := x_target_owners_rec;
1119
1120 l_Target_Rec_p := l_Target_Rec;
1121 BIS_TARGET_PVT.Retrieve_Target
1122 ( p_api_version => 1.0
1123 , p_Target_Rec => l_Target_Rec_p
1124 , p_all_info => p_all_info
1125 , x_Target_rec => l_Target_rec
1126 , x_return_status => x_return_status
1127 , x_error_Tbl => x_error_Tbl
1128 );
1129
1130 l_target_owners_rec.Range1_Owner_ID := l_target_rec.Notify_Resp1_ID;
1131 l_target_owners_rec.Range1_Owner_Short_Name
1132 := l_target_rec.Notify_Resp1_Short_Name;
1133 l_target_owners_rec.Range1_Owner_Name := l_target_rec.Notify_Resp1_Name;
1134
1135 l_target_owners_rec.Range2_Owner_ID := l_target_rec.Notify_Resp2_ID;
1136 l_target_owners_rec.Range2_Owner_Short_Name
1137 := l_target_rec.Notify_Resp2_Short_Name;
1138 l_target_owners_rec.Range2_Owner_Name := l_target_rec.Notify_Resp2_Name;
1139
1140 l_target_owners_rec.Range3_Owner_ID := l_target_rec.Notify_Resp3_ID;
1141 l_target_owners_rec.Range3_Owner_Short_Name
1142 := l_target_rec.Notify_Resp3_Short_Name;
1143 l_target_owners_rec.Range3_Owner_Name := l_target_rec.Notify_Resp3_Name;
1144
1145 x_target_owners_rec := l_target_owners_rec;
1146
1147 --commented RAISE
1148 EXCEPTION
1149 WHEN FND_API.G_EXC_ERROR THEN
1150 x_return_status:= FND_API.G_RET_STS_ERROR;
1151 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1152 htp.p('BIS_TARGET_PVT.Retrieve_Target_owners:G_EXC_ERROR');
1153 htp.para;
1154 END IF;
1155 -- RAISE;
1156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1157 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1158 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1159 htp.p('BIS_TARGET_PVT.Retrieve_Target_owners:G_EXC_UNEXPECTED_ERROR');
1160 htp.para;
1161 END IF;
1162 -- RAISE;
1163 WHEN OTHERS THEN
1164 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1165 l_error_Tbl_p := x_error_Tbl;
1166 BIS_UTILITIES_PVT.Add_Error_Message
1167 ( p_error_table => l_error_Tbl_p
1168 , p_error_msg_id => SQLCODE
1169 , p_error_description => SQLERRM
1170 , x_error_table => x_error_Tbl
1171 );
1172 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1173 htp.p('BIS_TARGET_PVT.Retrieve_Target_owners:OTHERS'); htp.para;
1174 END IF;
1175 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176
1177 END Retrieve_Target_Owners;
1178 --
1179 -- Modifies one target for a specific set of dimension values for
1180 -- one target level
1181 PROCEDURE Update_Target
1182 ( p_api_version IN NUMBER
1183 , p_is_dbimeasure IN NUMBER := 0 --gbhaloti #3148615
1184 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1185 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1186 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
1187 , x_return_status OUT NOCOPY VARCHAR2
1188 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1189 )
1190 IS
1191 --
1192 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
1193 l_target_id NUMBER;
1194 l_user_id NUMBER;
1195 l_login_id NUMBER;
1196 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1197
1198 l_Target_Rec_p BIS_TARGET_PUB.Target_Rec_Type;
1199
1200 --
1201 BEGIN
1202 --
1203 -- need to validate if record exists in db for update
1204 --
1205 -- get the ID if its not given in the record
1206 GetID
1207 ( p_api_version => p_api_version
1208 , p_Target_Rec => p_Target_Rec
1209 , x_Target_Rec => l_Target_Rec
1210 , x_return_status => x_return_status
1211 , x_error_Tbl => x_error_Tbl
1212 );
1213 -- retrieve record from database and apply changes
1214 l_Target_Rec_p := l_Target_Rec;
1215 Update_db_Target
1216 ( p_Target_Rec => l_Target_Rec_p
1217 , x_Target_Rec => l_Target_Rec
1218 , x_return_status => x_return_status
1219 , x_error_Tbl => x_error_Tbl
1220 );
1221 --
1222
1223 Validate_Target
1224 ( p_api_version => p_api_version
1225 , p_is_dbimeasure => p_is_dbimeasure
1226 , p_validation_level => p_validation_level
1227 , p_Target_Rec => l_Target_Rec
1228 , x_return_status => x_return_status
1229 , x_error_Tbl => x_error_Tbl
1230 );
1231 --
1232 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1233
1234 RAISE FND_API.G_EXC_ERROR;
1235 END IF;
1236
1237 --
1238 BEGIN
1239
1240 SELECT bisbv_targets.TARGET_ID
1241 INTO l_target_id
1242 FROM BISBV_TARGETS bisbv_targets
1243 WHERE bisbv_targets.TARGET_LEVEL_ID = l_Target_Rec.Target_Level_ID
1244 AND bisbv_targets.PLAN_ID = l_Target_Rec.Plan_ID
1245 AND NVL(bisbv_targets.ORG_LEVEL_VALUE_ID, 'Y')
1246 = NVL(l_Target_Rec.Org_Level_Value_ID, 'Y')
1247 AND NVL(bisbv_targets.TIME_LEVEL_VALUE_ID, 'Y')
1248 = NVL(l_Target_Rec.Time_Level_Value_ID, 'Y')
1249 AND NVL(bisbv_targets.DIM1_LEVEL_VALUE_ID, 'Y')
1250 = NVL(l_Target_Rec.Dim1_Level_Value_ID, 'Y')
1251 AND NVL(bisbv_targets.DIM2_LEVEL_VALUE_ID, 'Y')
1252 = NVL(l_Target_Rec.Dim2_Level_Value_ID, 'Y')
1253 AND NVL(bisbv_targets.DIM3_LEVEL_VALUE_ID, 'Y')
1254 = NVL(l_Target_Rec.Dim3_Level_Value_ID, 'Y')
1255 AND NVL(bisbv_targets.DIM4_LEVEL_VALUE_ID, 'Y')
1256 = NVL(l_Target_Rec.Dim4_Level_Value_ID, 'Y')
1257 AND NVL(bisbv_targets.DIM5_LEVEL_VALUE_ID, 'Y')
1258 = NVL(l_Target_Rec.Dim5_Level_Value_ID, 'Y')
1259 AND NVL(bisbv_targets.DIM6_LEVEL_VALUE_ID, 'Y')
1260 = NVL(l_Target_Rec.Dim6_Level_Value_ID, 'Y')
1261 AND NVL(bisbv_targets.DIM7_LEVEL_VALUE_ID, 'Y')
1262 = NVL(l_Target_Rec.Dim7_Level_Value_ID, 'Y');
1263
1264 --
1265
1266 IF(l_target_id <> l_Target_Rec.Target_ID) THEN
1267 --added more params
1268 l_error_tbl := x_error_tbl;
1269 BIS_UTILITIES_PVT.Add_Error_Message
1270 ( p_error_msg_name => 'BIS_UNIQUE_INDEX_VIOLATION'
1271 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1272 , p_error_proc_name => 'BIS_TARGET_PVT.Update_Target'
1273 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1274 , p_error_table => l_error_tbl
1275 , x_error_table => x_error_tbl
1276 );
1277 RAISE FND_API.G_EXC_ERROR;
1278 END IF;
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1281
1282 -- Added this err message
1283 l_error_tbl := x_error_tbl;
1284 BIS_UTILITIES_PVT.Add_Error_Message
1285 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
1286 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1287 , p_error_proc_name => 'BIS_TARGET_PVT.Update_Target'
1288 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1289 , p_error_table => l_error_tbl
1290 , x_error_table => x_error_tbl
1291 );
1292 --NULL;
1293 RAISE FND_API.G_EXC_ERROR;
1294 WHEN OTHERS THEN
1295
1296 RAISE;
1297 END;
1298 --
1299 l_user_id := fnd_global.USER_ID;
1300 l_login_id := fnd_global.LOGIN_ID;
1301 --
1302
1303 UPDATE BIS_TARGET_VALUES SET
1304 TARGET = l_Target_Rec.TARGET
1305 , TARGET_LEVEL_ID = l_Target_Rec.TARGET_LEVEL_ID
1306 , PLAN_ID = l_Target_Rec.PLAN_ID
1307 , ORG_LEVEL_VALUE = l_Target_Rec.ORG_LEVEL_VALUE_ID
1308 , TIME_LEVEL_VALUE = l_Target_Rec.TIME_LEVEL_VALUE_ID
1309 , DIMENSION1_LEVEL_VALUE = l_Target_Rec.DIM1_LEVEL_VALUE_ID
1310 , DIMENSION2_LEVEL_VALUE = l_Target_Rec.DIM2_LEVEL_VALUE_ID
1311 , DIMENSION3_LEVEL_VALUE = l_Target_Rec.DIM3_LEVEL_VALUE_ID
1312 , DIMENSION4_LEVEL_VALUE = l_Target_Rec.DIM4_LEVEL_VALUE_ID
1313 , DIMENSION5_LEVEL_VALUE = l_Target_Rec.DIM5_LEVEL_VALUE_ID
1314 , DIMENSION6_LEVEL_VALUE = l_Target_Rec.DIM6_LEVEL_VALUE_ID
1315 , DIMENSION7_LEVEL_VALUE = l_Target_Rec.DIM7_LEVEL_VALUE_ID
1316 , RANGE1_LOW = l_Target_Rec.RANGE1_LOW
1317 , RANGE1_HIGH = l_Target_Rec.RANGE1_HIGH
1318 , RANGE2_LOW = l_Target_Rec.RANGE2_LOW
1319 , RANGE2_HIGH = l_Target_Rec.RANGE2_HIGH
1320 , RANGE3_LOW = l_Target_Rec.RANGE3_LOW
1321 , RANGE3_HIGH = l_Target_Rec.RANGE3_HIGH
1322 , ROLE1_ID = l_Target_Rec.NOTIFY_RESP1_ID
1323 , ROLE1 = l_Target_Rec.NOTIFY_RESP1_SHORT_NAME
1324 , ROLE2_ID = l_Target_Rec.NOTIFY_RESP2_ID
1325 , ROLE2 = l_Target_Rec.NOTIFY_RESP2_SHORT_NAME
1326 , ROLE3_ID = l_Target_Rec.NOTIFY_RESP3_ID
1327 , ROLE3 = l_Target_Rec.NOTIFY_RESP3_SHORT_NAME
1328 , LAST_UPDATE_DATE = SYSDATE
1329 , LAST_UPDATED_BY = l_user_id
1330 , LAST_UPDATE_LOGIN = l_login_id
1331 WHERE TARGET_ID = l_Target_Rec.TARGET_ID;
1332 --
1333
1334 --added this
1335 if (p_commit = FND_API.G_TRUE) then
1336 COMMIT;
1337 end if;
1338 --commented RAISE
1339 EXCEPTION
1340 WHEN FND_API.G_EXC_ERROR THEN
1341 x_return_status:= FND_API.G_RET_STS_ERROR;
1342 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1343 htp.p('BIS_TARGET_PVT.Update_Target:G_EXC_ERROR'); htp.para;
1344 END IF;
1345 --RAISE;
1346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1347 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1349 htp.p('BIS_TARGET_PVT.Update_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
1350 END IF;
1351 --RAISE;
1352 WHEN OTHERS THEN
1353 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1354 l_error_Tbl := x_error_Tbl;
1355 BIS_UTILITIES_PVT.Add_Error_Message
1356 ( p_error_table => l_error_Tbl
1357 , p_error_msg_id => SQLCODE
1358 , p_error_description => SQLERRM
1359 , x_error_table => x_error_Tbl
1360 );
1361 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1362 htp.p('BIS_TARGET_PVT.Update_Target:OTHERS'); htp.para;
1363 END IF;
1364 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1365 --
1366 END Update_Target;
1367 --
1368 --
1369 -- returns the record with the Target_ID populated
1370 PROCEDURE GetID
1371 ( p_api_version IN NUMBER
1372 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
1373 , x_Target_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
1374 , x_return_status OUT NOCOPY VARCHAR2
1375 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1376 )
1377 IS
1378 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1379 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1380 BEGIN
1381 x_Target_Rec := p_Target_Rec;
1382 IF( BIS_UTILITIES_PUB.Value_Missing(x_Target_Rec.TARGET_ID)
1383 = FND_API.G_TRUE
1384 OR x_Target_Rec.TARGET_ID IS NULL
1385 ) THEN
1386 SELECT TARGET_ID
1387 INTO x_Target_Rec.TARGET_ID
1388 FROM BISBV_TARGETS BISBV_TARGETS
1389 WHERE BISBV_TARGETS.TARGET_LEVEL_ID = x_Target_Rec.Target_Level_ID
1390 AND BISBV_TARGETS.PLAN_ID = x_Target_Rec.Plan_ID
1391 AND NVL(BISBV_TARGETS.ORG_LEVEL_VALUE_ID, 'T')
1392 = DECODE( x_Target_Rec.Org_Level_Value_ID
1393 , FND_API.G_MISS_CHAR
1394 , 'T'
1395 , NVL(x_Target_Rec.Org_Level_Value_ID, 'T')
1396 )
1397 AND NVL(BISBV_TARGETS.TIME_LEVEL_VALUE_ID, 'T')
1398 = DECODE( x_Target_Rec.Time_Level_Value_ID
1399 , FND_API.G_MISS_CHAR
1400 , 'T'
1401 , NVL(x_Target_Rec.Time_Level_Value_ID, 'T')
1402 )
1403 AND NVL(BISBV_TARGETS.DIM1_LEVEL_VALUE_ID, 'T')
1404 = DECODE( x_Target_Rec.Dim1_Level_Value_ID
1405 , FND_API.G_MISS_CHAR
1406 , 'T'
1407 , NVL(x_Target_Rec.Dim1_Level_Value_ID, 'T')
1408 )
1409 AND NVL(BISBV_TARGETS.DIM2_LEVEL_VALUE_ID, 'T')
1410 = DECODE( x_Target_Rec.Dim2_Level_Value_ID
1411 , FND_API.G_MISS_CHAR
1412 , 'T'
1413 , NVL(x_Target_Rec.Dim2_Level_Value_ID, 'T')
1414 )
1415 AND NVL(BISBV_TARGETS.DIM3_LEVEL_VALUE_ID, 'T')
1416 = DECODE( x_Target_Rec.Dim3_Level_Value_ID
1417 , FND_API.G_MISS_CHAR
1418 , 'T'
1419 , NVL(x_Target_Rec.Dim3_Level_Value_ID, 'T')
1420 )
1421 AND NVL(BISBV_TARGETS.DIM4_LEVEL_VALUE_ID, 'T')
1422 = DECODE( x_Target_Rec.Dim4_Level_Value_ID
1423 , FND_API.G_MISS_CHAR
1424 , 'T'
1425 , NVL(x_Target_Rec.Dim4_Level_Value_ID, 'T')
1426 )
1427 AND NVL(BISBV_TARGETS.DIM5_LEVEL_VALUE_ID, 'T')
1428 = DECODE( x_Target_Rec.Dim5_Level_Value_ID
1429 , FND_API.G_MISS_CHAR
1430 , 'T'
1431 , NVL(x_Target_Rec.Dim5_Level_Value_ID, 'T')
1432 )
1433 AND NVL(BISBV_TARGETS.DIM6_LEVEL_VALUE_ID, 'T')
1434 = DECODE( x_Target_Rec.Dim6_Level_Value_ID
1435 , FND_API.G_MISS_CHAR
1436 , 'T'
1437 , NVL(x_Target_Rec.Dim6_Level_Value_ID, 'T')
1438 )
1439 AND NVL(BISBV_TARGETS.DIM7_LEVEL_VALUE_ID, 'T')
1440 = DECODE( x_Target_Rec.Dim7_Level_Value_ID
1441 , FND_API.G_MISS_CHAR
1442 , 'T'
1443 , NVL(x_Target_Rec.Dim7_Level_Value_ID, 'T')
1444 )
1445 ;
1446 END IF;
1447 --
1448 x_return_status := FND_API.G_RET_STS_SUCCESS;
1449 --
1450 --commented RAISE
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 x_return_status:= FND_API.G_RET_STS_ERROR;
1454 --added more params and changed the message
1455 l_error_tbl := x_error_tbl;
1456 BIS_UTILITIES_PVT.Add_Error_Message
1457 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
1458 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1459 , p_error_proc_name => 'BIS_TARGET_PVT.GetID'
1460 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1461 , p_error_table => l_error_tbl
1462 , x_error_table => x_error_tbl
1463 );
1464 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1465 htp.p('BIS_TARGET_PVT.GetID:NO_DATA_FOUND'); htp.para;
1466 END IF;
1467 -- RAISE FND_API.G_EXC_ERROR;
1468 WHEN FND_API.G_EXC_ERROR THEN
1469 x_return_status:= FND_API.G_RET_STS_ERROR;
1470 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1471 htp.p('BIS_TARGET_PVT.GetID:G_EXC_ERROR'); htp.para;
1472 END IF;
1473 -- RAISE;
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1476 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1477 htp.p('BIS_TARGET_PVT.GetID:G_EXC_UNEXPECTED_ERROR'); htp.para;
1478 END IF;
1479 -- RAISE;
1480 WHEN OTHERS THEN
1481 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1482 l_error_Tbl_p := x_error_Tbl;
1483 BIS_UTILITIES_PVT.Add_Error_Message
1484 ( p_error_table => l_error_Tbl_p
1485 , p_error_msg_id => SQLCODE
1486 , p_error_description => SQLERRM
1487 , x_error_table => x_error_Tbl
1488 );
1489 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1490 htp.p('BIS_TARGET_PVT.GetID:OTHERS'); htp.para;
1491 END IF;
1492 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1493 --
1494 END GetID;
1495 --
1496 --
1497 -- Deletes one target for a specific set of dimension values for
1498 -- one target level
1499 PROCEDURE Delete_Target
1500 ( p_api_version IN NUMBER
1501 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1502 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
1503 , x_return_status OUT NOCOPY VARCHAR2
1504 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1505 )
1506 IS
1507 --
1508 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
1509 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1510 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1511 --
1512 BEGIN
1513 -- validate if record can be deleted (no children etc.)
1514 -- get the ID if its not given in the record
1515 GetID
1516 ( p_api_version => p_api_version
1517 , p_Target_Rec => p_Target_Rec
1518 , x_Target_Rec => l_Target_Rec
1519 , x_return_status => x_return_status
1520 , x_error_Tbl => x_error_Tbl
1521 );
1522 --
1523 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1524 RAISE FND_API.G_EXC_ERROR;
1525 END IF;
1526 --
1527 DELETE FROM BIS_TARGET_VALUES
1528 WHERE TARGET_ID = l_Target_Rec.TARGET_ID;
1529
1530 if SQL%NOTFOUND then
1531 RAISE NO_DATA_FOUND;
1532 end if;
1533 --
1534 --commented RAISE
1535 EXCEPTION
1536 --added this
1537 WHEN NO_DATA_FOUND THEN
1538 x_return_status:= FND_API.G_RET_STS_ERROR;
1539 --added more params
1540 l_error_tbl := x_error_tbl;
1541 BIS_UTILITIES_PVT.Add_Error_Message
1542 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
1543 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1544 , p_error_proc_name => 'BIS_TARGET_PVT.Delete_Target'
1545 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1546 , p_error_table => l_error_tbl
1547 , x_error_table => x_error_tbl
1548 );
1549 WHEN FND_API.G_EXC_ERROR THEN
1550 x_return_status:= FND_API.G_RET_STS_ERROR;
1551 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1552 htp.p('BIS_TARGET_PVT.Delete_Target:G_EXC_ERROR'); htp.para;
1553 END IF;
1554 -- RAISE;
1555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1557 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1558 htp.p('BIS_TARGET_PVT.Delete_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
1559 END IF;
1560 -- RAISE;
1561 WHEN OTHERS THEN
1562 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1563 l_error_Tbl_p := x_error_Tbl;
1564 BIS_UTILITIES_PVT.Add_Error_Message
1565 ( p_error_table => l_error_Tbl_p
1566 , p_error_msg_id => SQLCODE
1567 , p_error_description => SQLERRM
1568 , x_error_table => x_error_Tbl
1569 );
1570 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1571 htp.p('BIS_TARGET_PVT.Delete_Target:OTHERS'); htp.para;
1572 END IF;
1573 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1574 --
1575 END Delete_Target;
1576 --
1577 --
1578 -- Validates target record
1579 PROCEDURE Validate_Target
1580 ( p_api_version IN NUMBER
1581 , p_is_dbimeasure IN NUMBER := 0 --added by gbhaloti #3148615
1582 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1583 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
1584 , x_return_status OUT NOCOPY VARCHAR2
1585 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1586 )
1587 IS
1588 --
1589 l_return_status VARCHAR2(10);
1590 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1591 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1592 l_error VARCHAR2(10) := FND_API.G_FALSE;
1593 --
1594 BEGIN
1595 x_return_status := FND_API.G_RET_STS_SUCCESS;
1596 --
1597 -- htp.header(5,'in validate target '||p_target_rec.target_level_id||'<BR>');
1598 BEGIN
1599
1600 -- dbms_output.put_line( ' Inside pvt : 41 ' || l_error );
1601
1602 BIS_TARGET_VALIDATE_PVT.Validate_Target_Level_ID
1603 ( p_api_version => p_api_version
1604 , p_Target_Rec => p_Target_Rec
1605 , x_return_status => l_return_status
1606 , x_error_Tbl => x_error_Tbl
1607 );
1608 -- htp.header(5,'target level: '||l_return_status||'<BR>');
1609 --
1610
1611 -- dbms_output.put_line( ' Inside pvt : 42 ' || l_error );
1612
1613 --EXCEPTION
1614 --WHEN FND_API.G_EXC_ERROR THEN
1615 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1616 l_error := FND_API.G_TRUE;
1617 x_return_status:= FND_API.G_RET_STS_ERROR;
1618 l_error_Tbl_p := x_error_Tbl;
1619 BIS_UTILITIES_PVT.concatenateErrorTables
1620 ( p_error_Tbl1 => l_error_Tbl_p
1621 , p_error_Tbl2 => l_error_Tbl
1622 , x_error_Tbl => x_error_Tbl
1623 );
1624 END IF;
1625
1626 -- dbms_output.put_line( ' Inside pvt : 43 ' || l_error );
1627
1628 END;
1629 --
1630 BEGIN
1631 BIS_TARGET_VALIDATE_PVT.Validate_Plan_ID
1632 ( p_api_version => p_api_version
1633 , p_Target_Rec => p_Target_Rec
1634 , x_return_status => l_return_status
1635 , x_error_Tbl => l_error_Tbl
1636 );
1637
1638 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1639 l_error := FND_API.G_TRUE;
1640 x_return_status:= FND_API.G_RET_STS_ERROR;
1641 l_error_Tbl_p := x_error_Tbl;
1642 BIS_UTILITIES_PVT.concatenateErrorTables
1643 ( p_error_Tbl1 => l_error_Tbl_p
1644 , p_error_Tbl2 => l_error_Tbl
1645 , x_error_Tbl => x_error_Tbl
1646 );
1647 END IF;
1648 END;
1649
1650 -- dbms_output.put_line( ' Inside pvt : 44 ' || l_error );
1651
1652 --
1653 BEGIN
1654 BIS_TARGET_VALIDATE_PVT.Validate_Dim1_Level_Value_ID
1655 ( p_api_version => p_api_version
1656 , p_Target_Rec => p_Target_Rec
1657 , x_return_status => l_return_status
1658 , x_error_Tbl => l_error_Tbl
1659 );
1660 --
1661 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1662 l_error := FND_API.G_TRUE;
1663 x_return_status:= FND_API.G_RET_STS_ERROR;
1664 l_error_Tbl_p := x_error_Tbl;
1665 BIS_UTILITIES_PVT.concatenateErrorTables
1666 ( p_error_Tbl1 => l_error_Tbl_p
1667 , p_error_Tbl2 => l_error_Tbl
1668 , x_error_Tbl => x_error_Tbl
1669 );
1670 END IF;
1671 END;
1672
1673
1674 -- dbms_output.put_line( ' Inside pvt : 45 ' || l_error );
1675
1676 --
1677 BEGIN
1678 BIS_TARGET_VALIDATE_PVT.Validate_Dim2_Level_Value_ID
1679 ( p_api_version => p_api_version
1680 , p_Target_Rec => p_Target_Rec
1681 , x_return_status => l_return_status
1682 , x_error_Tbl => l_error_Tbl
1683 );
1684 --
1685
1686 --EXCEPTION
1687 -- WHEN FND_API.G_EXC_ERROR THEN
1688 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1689 l_error := FND_API.G_TRUE;
1690 x_return_status:= FND_API.G_RET_STS_ERROR;
1691 l_error_Tbl_p := x_error_Tbl;
1692 BIS_UTILITIES_PVT.concatenateErrorTables
1693 ( p_error_Tbl1 => l_error_Tbl_p
1694 , p_error_Tbl2 => l_error_Tbl
1695 , x_error_Tbl => x_error_Tbl
1696 );
1697 END IF;
1698 END;
1699
1700 -- dbms_output.put_line( ' Inside pvt : 46 ' || l_error );
1701
1702 --
1703 BEGIN
1704 BIS_TARGET_VALIDATE_PVT.Validate_Dim3_Level_Value_ID
1705 ( p_api_version => p_api_version
1706 , p_Target_Rec => p_Target_Rec
1707 , x_return_status => l_return_status
1708 , x_error_Tbl => l_error_Tbl
1709 );
1710 --
1711
1712 --EXCEPTION
1713 -- WHEN FND_API.G_EXC_ERROR THEN
1714 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1715 l_error := FND_API.G_TRUE;
1716 x_return_status:= FND_API.G_RET_STS_ERROR;
1717 l_error_Tbl_p := x_error_Tbl;
1718 BIS_UTILITIES_PVT.concatenateErrorTables
1719 ( p_error_Tbl1 => l_error_Tbl_p
1720 , p_error_Tbl2 => l_error_Tbl
1721 , x_error_Tbl => x_error_Tbl
1722 );
1723 END IF;
1724 END;
1725
1726 -- dbms_output.put_line( ' Inside pvt : 46.1 ' || l_error );
1727
1728 --
1729 BEGIN
1730 BIS_TARGET_VALIDATE_PVT.Validate_Dim4_Level_Value_ID
1731 ( p_api_version => p_api_version
1732 , p_Target_Rec => p_Target_Rec
1733 , x_return_status => l_return_status
1734 , x_error_Tbl => l_error_Tbl
1735 );
1736 --
1737
1738 --EXCEPTION
1739 --WHEN FND_API.G_EXC_ERROR THEN
1740 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1741 l_error := FND_API.G_TRUE;
1742 x_return_status:= FND_API.G_RET_STS_ERROR;
1743 l_error_Tbl_p := x_error_Tbl;
1744 BIS_UTILITIES_PVT.concatenateErrorTables
1745 ( p_error_Tbl1 => l_error_Tbl_p
1746 , p_error_Tbl2 => l_error_Tbl
1747 , x_error_Tbl => x_error_Tbl
1748 );
1749 END IF;
1750 END;
1751
1752
1753 -- dbms_output.put_line( ' Inside pvt : 46.2 ' || l_error );
1754
1755
1756 --
1757 BEGIN
1758 BIS_TARGET_VALIDATE_PVT.Validate_Dim5_Level_Value_ID
1759 ( p_api_version => p_api_version
1760 , p_Target_Rec => p_Target_Rec
1761 , x_return_status => l_return_status
1762 , x_error_Tbl => l_error_Tbl
1763 );
1764 --
1765
1766 -- EXCEPTION
1767 -- WHEN FND_API.G_EXC_ERROR THEN
1768 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1769 l_error := FND_API.G_TRUE;
1770 x_return_status:= FND_API.G_RET_STS_ERROR;
1771 l_error_Tbl_p := x_error_Tbl;
1772 BIS_UTILITIES_PVT.concatenateErrorTables
1773 ( p_error_Tbl1 => l_error_Tbl_p
1774 , p_error_Tbl2 => l_error_Tbl
1775 , x_error_Tbl => x_error_Tbl
1776 );
1777 END IF;
1778 END;
1779
1780
1781 -- dbms_output.put_line( ' Inside pvt : 46.3 ' || l_error );
1782
1783
1784
1785 --
1786 BEGIN
1787 BIS_TARGET_VALIDATE_PVT.Validate_Dim6_Level_Value_ID
1788 ( p_api_version => p_api_version
1789 , p_Target_Rec => p_Target_Rec
1790 , x_return_status => l_return_status
1791 , x_error_Tbl => l_error_Tbl
1792 );
1793 --
1794
1795 --EXCEPTION
1796 --WHEN FND_API.G_EXC_ERROR THEN
1797 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1798 l_error := FND_API.G_TRUE;
1799 x_return_status:= FND_API.G_RET_STS_ERROR;
1800 l_error_Tbl_p := x_error_Tbl;
1801 BIS_UTILITIES_PVT.concatenateErrorTables
1802 ( p_error_Tbl1 => l_error_Tbl_p
1803 , p_error_Tbl2 => l_error_Tbl
1804 , x_error_Tbl => x_error_Tbl
1805 );
1806 END IF;
1807 END;
1808
1809 -- dbms_output.put_line( ' Inside pvt : 46.4 ' || l_error );
1810
1811
1812 --
1813 BEGIN
1814 BIS_TARGET_VALIDATE_PVT.Validate_Dim7_Level_Value_ID
1815 ( p_api_version => p_api_version
1816 , p_Target_Rec => p_Target_Rec
1817 , x_return_status => l_return_status
1818 , x_error_Tbl => l_error_Tbl
1819 );
1820 --
1821
1822 -- EXCEPTION
1823 -- WHEN FND_API.G_EXC_ERROR THEN
1824 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1825 l_error := FND_API.G_TRUE;
1826 x_return_status:= FND_API.G_RET_STS_ERROR;
1827 l_error_Tbl_p := x_error_Tbl;
1828 BIS_UTILITIES_PVT.concatenateErrorTables
1829 ( p_error_Tbl1 => l_error_Tbl_p
1830 , p_error_Tbl2 => l_error_Tbl
1831 , x_error_Tbl => x_error_Tbl
1832 );
1833 END IF;
1834 END;
1835 --
1836 BEGIN
1837 BIS_TARGET_VALIDATE_PVT.Validate_Target_Value
1838 ( p_api_version => p_api_version
1839 , p_is_dbimeasure => p_is_dbimeasure --added by gbhaloti #3148615
1840 , p_Target_Rec => p_Target_Rec
1841 , x_return_status => l_return_status
1842 , x_error_Tbl => l_error_Tbl
1843 );
1844
1845 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1846 l_error := FND_API.G_TRUE;
1847 x_return_status:= FND_API.G_RET_STS_ERROR;
1848 l_error_Tbl_p := x_error_Tbl;
1849 BIS_UTILITIES_PVT.concatenateErrorTables
1850 ( p_error_Tbl1 => l_error_Tbl_p
1851 , p_error_Tbl2 => l_error_Tbl
1852 , x_error_Tbl => x_error_Tbl
1853 );
1854 END IF;
1855 END;
1856
1857 --
1858 BEGIN
1859 BIS_TARGET_VALIDATE_PVT.Validate_Range1_Low
1860 ( p_api_version => p_api_version
1861 , p_Target_Rec => p_Target_Rec
1862 , x_return_status => l_return_status
1863 , x_error_Tbl => l_error_Tbl
1864 );
1865 --
1866
1867 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1868 l_error := FND_API.G_TRUE;
1869 x_return_status:= FND_API.G_RET_STS_ERROR;
1870 l_error_Tbl_p := x_error_Tbl;
1871 BIS_UTILITIES_PVT.concatenateErrorTables
1872 ( p_error_Tbl1 => l_error_Tbl_p
1873 , p_error_Tbl2 => l_error_Tbl
1874 , x_error_Tbl => x_error_Tbl
1875 );
1876 END IF;
1877 END;
1878 --
1879 BEGIN
1880 BIS_TARGET_VALIDATE_PVT.Validate_Range1_High
1881 ( p_api_version => p_api_version
1882 , p_Target_Rec => p_Target_Rec
1883 , x_return_status => l_return_status
1884 , x_error_Tbl => l_error_Tbl
1885 );
1886 --
1887
1888 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1889 l_error := FND_API.G_TRUE;
1890 x_return_status:= FND_API.G_RET_STS_ERROR;
1891 l_error_Tbl_p := x_error_Tbl;
1892 BIS_UTILITIES_PVT.concatenateErrorTables
1893 ( p_error_Tbl1 => l_error_Tbl_p
1894 , p_error_Tbl2 => l_error_Tbl
1895 , x_error_Tbl => x_error_Tbl
1896 );
1897 END IF;
1898 END;
1899 --
1900 BEGIN
1901 BIS_TARGET_VALIDATE_PVT.Validate_Range2_Low
1902 ( p_api_version => p_api_version
1903 , p_Target_Rec => p_Target_Rec
1904 , x_return_status => l_return_status
1905 , x_error_Tbl => l_error_Tbl
1906 );
1907 --
1908 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1909 l_error := FND_API.G_TRUE;
1910 x_return_status:= FND_API.G_RET_STS_ERROR;
1911 l_error_Tbl_p := x_error_Tbl;
1912 BIS_UTILITIES_PVT.concatenateErrorTables
1913 ( p_error_Tbl1 => l_error_Tbl_p
1914 , p_error_Tbl2 => l_error_Tbl
1915 , x_error_Tbl => x_error_Tbl
1916 );
1917 END IF;
1918 END;
1919 --
1920 BEGIN
1921 BIS_TARGET_VALIDATE_PVT.Validate_Range2_High
1922 ( p_api_version => p_api_version
1923 , p_Target_Rec => p_Target_Rec
1924 , x_return_status => l_return_status
1925 , x_error_Tbl => l_error_Tbl
1926 );
1927 --
1928
1929 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1930 l_error := FND_API.G_TRUE;
1931 x_return_status:= FND_API.G_RET_STS_ERROR;
1932 l_error_Tbl_p := x_error_Tbl;
1933 BIS_UTILITIES_PVT.concatenateErrorTables
1934 ( p_error_Tbl1 => l_error_Tbl_p
1935 , p_error_Tbl2 => l_error_Tbl
1936 , x_error_Tbl => x_error_Tbl
1937 );
1938 END IF;
1939 END;
1940
1941 --
1942 BEGIN
1943 BIS_TARGET_VALIDATE_PVT.Validate_Range3_Low
1944 ( p_api_version => p_api_version
1945 , p_Target_Rec => p_Target_Rec
1946 , x_return_status => l_return_status
1947 , x_error_Tbl => l_error_Tbl
1948 );
1949 --
1950 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1951 l_error := FND_API.G_TRUE;
1952 x_return_status:= FND_API.G_RET_STS_ERROR;
1953 l_error_Tbl_p := x_error_Tbl;
1954 BIS_UTILITIES_PVT.concatenateErrorTables
1955 ( p_error_Tbl1 => l_error_Tbl_p
1956 , p_error_Tbl2 => l_error_Tbl
1957 , x_error_Tbl => x_error_Tbl
1958 );
1959 END IF;
1960 END;
1961 --
1962 BEGIN
1963 BIS_TARGET_VALIDATE_PVT.Validate_Range3_High
1964 ( p_api_version => p_api_version
1965 , p_Target_Rec => p_Target_Rec
1966 , x_return_status => l_return_status
1967 , x_error_Tbl => l_error_Tbl
1968 );
1969 --
1970 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1971 l_error := FND_API.G_TRUE;
1972 x_return_status:= FND_API.G_RET_STS_ERROR;
1973 l_error_Tbl_p := x_error_Tbl;
1974 BIS_UTILITIES_PVT.concatenateErrorTables
1975 ( p_error_Tbl1 => l_error_Tbl_p
1976 , p_error_Tbl2 => l_error_Tbl
1977 , x_error_Tbl => x_error_Tbl
1978 );
1979 END IF;
1980 END;
1981 --
1982 BEGIN
1983 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp1_ID
1984 ( p_api_version => p_api_version
1985 , p_Target_Rec => p_Target_Rec
1986 , x_return_status => l_return_status
1987 , x_error_Tbl => l_error_Tbl
1988 );
1989 --
1990 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1991 l_error := FND_API.G_TRUE;
1992 x_return_status:= FND_API.G_RET_STS_ERROR;
1993 l_error_Tbl_p := x_error_Tbl;
1994 BIS_UTILITIES_PVT.concatenateErrorTables
1995 ( p_error_Tbl1 => l_error_Tbl_p
1996 , p_error_Tbl2 => l_error_Tbl
1997 , x_error_Tbl => x_error_Tbl
1998 );
1999 END IF;
2000 END;
2001 --
2002 BEGIN
2003 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp1
2004 ( p_api_version => p_api_version
2005 , p_Target_Rec => p_Target_Rec
2006 , x_return_status => l_return_status
2007 , x_error_Tbl => l_error_Tbl
2008 );
2009 --
2010 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2011 l_error := FND_API.G_TRUE;
2012 x_return_status:= FND_API.G_RET_STS_ERROR;
2013 l_error_Tbl_p := x_error_Tbl;
2014 BIS_UTILITIES_PVT.concatenateErrorTables
2015 ( p_error_Tbl1 => l_error_Tbl_p
2016 , p_error_Tbl2 => l_error_Tbl
2017 , x_error_Tbl => x_error_Tbl
2018 );
2019 END IF;
2020 END;
2021 --
2022 BEGIN
2023 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp2_ID
2024 ( p_api_version => p_api_version
2025 , p_Target_Rec => p_Target_Rec
2026 , x_return_status => l_return_status
2027 , x_error_Tbl => l_error_Tbl
2028 );
2029 --
2030
2031 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2032 l_error := FND_API.G_TRUE;
2033 x_return_status:= FND_API.G_RET_STS_ERROR;
2034 l_error_Tbl_p := x_error_Tbl;
2035 BIS_UTILITIES_PVT.concatenateErrorTables
2036 ( p_error_Tbl1 => l_error_Tbl_p
2037 , p_error_Tbl2 => l_error_Tbl
2038 , x_error_Tbl => x_error_Tbl
2039 );
2040 END IF;
2041 END;
2042 --
2043 BEGIN
2044 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp2
2045 ( p_api_version => p_api_version
2046 , p_Target_Rec => p_Target_Rec
2047 , x_return_status => l_return_status
2048 , x_error_Tbl => l_error_Tbl
2049 );
2050 --
2051
2052 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2053 l_error := FND_API.G_TRUE;
2054 x_return_status:= FND_API.G_RET_STS_ERROR;
2055 l_error_Tbl_p := x_error_Tbl;
2056 BIS_UTILITIES_PVT.concatenateErrorTables
2057 ( p_error_Tbl1 => l_error_Tbl_p
2058 , p_error_Tbl2 => l_error_Tbl
2059 , x_error_Tbl => x_error_Tbl
2060 );
2061 END IF;
2062 END;
2063 --
2064 BEGIN
2065 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp3_ID
2066 ( p_api_version => p_api_version
2067 , p_Target_Rec => p_Target_Rec
2068 , x_return_status => l_return_status
2069 , x_error_Tbl => l_error_Tbl
2070 );
2071 --
2072
2073 -- EXCEPTION
2074 -- WHEN FND_API.G_EXC_ERROR THEN
2075 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2076 l_error := FND_API.G_TRUE;
2077 x_return_status:= FND_API.G_RET_STS_ERROR;
2078 l_error_Tbl_p := x_error_Tbl;
2079 BIS_UTILITIES_PVT.concatenateErrorTables
2080 ( p_error_Tbl1 => l_error_Tbl_p
2081 , p_error_Tbl2 => l_error_Tbl
2082 , x_error_Tbl => x_error_Tbl
2083 );
2084 END IF;
2085 END;
2086 --
2087 BEGIN
2088 BIS_TARGET_VALIDATE_PVT.Validate_Notify_Resp3
2089 ( p_api_version => p_api_version
2090 , p_Target_Rec => p_Target_Rec
2091 , x_return_status => l_return_status
2092 , x_error_Tbl => l_error_Tbl
2093 );
2094 --
2095 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2096 l_error := FND_API.G_TRUE;
2097 x_return_status:= FND_API.G_RET_STS_ERROR;
2098 l_error_Tbl_p := x_error_Tbl;
2099 BIS_UTILITIES_PVT.concatenateErrorTables
2100 ( p_error_Tbl1 => l_error_Tbl_p
2101 , p_error_Tbl2 => l_error_Tbl
2102 , x_error_Tbl => x_error_Tbl
2103 );
2104 END IF;
2105 END;
2106
2107 -- dbms_output.put_line( ' Inside pvt : 49 ' || l_error );
2108
2109 --added this check
2110 if (l_error = FND_API.G_TRUE) then
2111 RAISE FND_API.G_EXC_ERROR;
2112 end if;
2113 --
2114
2115 -- dbms_output.put_line( ' Inside pvt : 49.9 ' || l_error );
2116
2117 --commented RAISE
2118 EXCEPTION
2119 WHEN FND_API.G_EXC_ERROR THEN
2120 x_return_status:= FND_API.G_RET_STS_ERROR;
2121 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2122 htp.p('BIS_TARGET_PVT.Validate_Target:G_EXC_ERROR'); htp.para;
2123 END IF;
2124 --RAISE;
2125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2127 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2128 htp.p('BIS_TARGET_PVT.Validate_Target:G_EXC_UNEXPECTED_ERROR');
2129 htp.para;
2130 END IF;
2131 -- RAISE;
2132 WHEN OTHERS THEN
2133 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2134 l_error_Tbl_p := x_error_Tbl;
2135 BIS_UTILITIES_PVT.Add_Error_Message
2136 ( p_error_table => l_error_Tbl_p
2137 , p_error_msg_id => SQLCODE
2138 , p_error_description => SQLERRM
2139 , x_error_table => x_error_Tbl
2140 );
2141 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2142 htp.p('BIS_TARGET_PVT.Validate_Target:OTHERS'); htp.para;
2143 END IF;
2144 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2145 END Validate_Target;
2146 --
2147 --
2148 -- Value - ID conversion
2149 PROCEDURE Value_ID_Conversion
2150 ( p_api_version IN NUMBER
2151 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
2152 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
2153 , x_return_status OUT NOCOPY VARCHAR2
2154 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2155 )
2156 IS
2157 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2158 BEGIN
2159 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2160 x_Target_Rec := p_Target_Rec;
2161 --
2162 --commented RAISE
2163 EXCEPTION
2164 WHEN FND_API.G_EXC_ERROR THEN
2165 x_return_status:= FND_API.G_RET_STS_ERROR;
2166 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2167 htp.p('BIS_TARGET_PVT.Value_ID_Conversion:G_EXC_ERROR'); htp.para;
2168 END IF;
2169 -- RAISE;
2170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2171 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2172 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2173 htp.p('BIS_TARGET_PVT.Value_ID_Conversion:G_EXC_UNEXPECTED_ERROR');
2174 htp.para;
2175 END IF;
2176 -- RAISE;
2177 WHEN OTHERS THEN
2178 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2179 l_error_Tbl_p := x_error_Tbl;
2180 BIS_UTILITIES_PVT.Add_Error_Message
2181 ( p_error_table => l_error_Tbl_p
2182 , p_error_msg_id => SQLCODE
2183 , p_error_description => SQLERRM
2184 , x_error_table => x_error_Tbl
2185 );
2186 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2187 htp.p('BIS_TARGET_PVT.Value_ID_Conversion:OTHERS'); htp.para;
2188 END IF;
2189 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2190 END Value_ID_Conversion;
2191 --
2192 --
2193 PROCEDURE Update_db_Target
2194 ( p_Target_Rec BIS_TARGET_PUB.Target_Rec_Type
2195 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
2196 , x_return_status OUT NOCOPY VARCHAR2
2197 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2198 )
2199 IS
2200 --
2201 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
2202 l_return_status VARCHAR2(10);
2203 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2204 --
2205 BEGIN
2206 -- retrieve record from db
2207 BIS_TARGET_PVT.Retrieve_Target
2208 ( p_api_version => 1.0
2209 , p_Target_Rec => p_Target_Rec
2210 , x_Target_Rec => l_Target_Rec
2211 , x_return_status => l_return_status
2212 , x_error_Tbl => x_error_Tbl
2213 );
2214 -- apply changes
2215 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Target_Level_ID)
2216 = FND_API.G_TRUE
2217 ) THEN
2218 l_Target_Rec.Target_Level_ID := p_Target_Rec.Target_Level_ID;
2219 END IF;
2220 --
2221 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Plan_ID)
2222 = FND_API.G_TRUE
2223 ) THEN
2224 l_Target_Rec.Plan_ID := p_Target_Rec.Plan_ID;
2225 END IF;
2226 --
2227 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Org_Level_Value_ID)
2228 = FND_API.G_TRUE
2229 ) THEN
2230 l_Target_Rec.Org_Level_Value_ID := p_Target_Rec.Org_Level_Value_ID;
2231 END IF;
2232 --
2233 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Time_Level_Value_ID)
2234 = FND_API.G_TRUE
2235 ) THEN
2236 l_Target_Rec.Time_Level_Value_ID := p_Target_Rec.Time_Level_Value_ID;
2237 END IF;
2238 --
2239 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim1_Level_Value_ID)
2240 = FND_API.G_TRUE
2241 ) THEN
2242 l_Target_Rec.Dim1_Level_Value_ID := p_Target_Rec.Dim1_Level_Value_ID;
2243 END IF;
2244 --
2245 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim2_Level_Value_ID)
2246 = FND_API.G_TRUE
2247 ) THEN
2248 l_Target_Rec.Dim2_Level_Value_ID := p_Target_Rec.Dim2_Level_Value_ID;
2249 END IF;
2250 --
2251 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim3_Level_Value_ID)
2252 = FND_API.G_TRUE
2253 ) THEN
2254 l_Target_Rec.Dim3_Level_Value_ID := p_Target_Rec.Dim3_Level_Value_ID;
2255 END IF;
2256 --
2257 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim4_Level_Value_ID)
2258 = FND_API.G_TRUE
2259 ) THEN
2260 l_Target_Rec.Dim4_Level_Value_ID := p_Target_Rec.Dim4_Level_Value_ID;
2261 END IF;
2262 --
2263 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim5_Level_Value_ID)
2264 = FND_API.G_TRUE
2265 ) THEN
2266 l_Target_Rec.Dim5_Level_Value_ID := p_Target_Rec.Dim5_Level_Value_ID;
2267 END IF;
2268 --
2269 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim6_Level_Value_ID)
2270 = FND_API.G_TRUE
2271 ) THEN
2272 l_Target_Rec.Dim6_Level_Value_ID := p_Target_Rec.Dim6_Level_Value_ID;
2273 END IF;
2274 --
2275 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Dim7_Level_Value_ID)
2276 = FND_API.G_TRUE
2277 ) THEN
2278 l_Target_Rec.Dim7_Level_Value_ID := p_Target_Rec.Dim7_Level_Value_ID;
2279 END IF;
2280 --
2281 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Target)
2282 = FND_API.G_TRUE
2283 ) THEN
2284 l_Target_Rec.Target := p_Target_Rec.Target;
2285 END IF;
2286 --
2287 l_Target_Rec.Range1_low := p_Target_Rec.Range1_low;
2288 l_Target_Rec.Range1_high := p_Target_Rec.Range1_high;
2289 l_Target_Rec.Range2_low := p_Target_Rec.Range2_low;
2290 l_Target_Rec.Range2_high := p_Target_Rec.Range2_high;
2291 l_Target_Rec.Range3_low := p_Target_Rec.Range3_low;
2292 l_Target_Rec.Range3_high := p_Target_Rec.Range3_high;
2293 l_Target_Rec.Notify_Resp1_ID := p_Target_Rec.Notify_Resp1_ID;
2294 l_Target_Rec.Notify_Resp1_Short_Name := p_Target_Rec.Notify_Resp1_Short_Name;
2295 l_Target_Rec.Notify_Resp2_ID := p_Target_Rec.Notify_Resp2_ID;
2296 l_Target_Rec.Notify_Resp2_Short_Name := p_Target_Rec.Notify_Resp2_Short_Name;
2297 l_Target_Rec.Notify_Resp3_ID := p_Target_Rec.Notify_Resp3_ID;
2298 l_Target_Rec.Notify_Resp3_Short_Name := p_Target_Rec.Notify_Resp3_Short_Name;
2299 --
2300 --added this
2301 x_Target_Rec := l_Target_Rec;
2302 --commented RAISE
2303 EXCEPTION
2304 WHEN FND_API.G_EXC_ERROR THEN
2305 x_return_status:= FND_API.G_RET_STS_ERROR;
2306 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2307 htp.p('BIS_TARGET_PVT.Update_db_Target:G_EXC_ERROR'); htp.para;
2308 END IF;
2309 --RAISE;
2310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2311 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2312 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2313 htp.p('BIS_TARGET_PVT.Update_db_Target:G_EXC_UNEXPECTED_ERROR');
2314 htp.para;
2315 END IF;
2316 --RAISE;
2317 WHEN OTHERS THEN
2318 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2319 l_error_Tbl_p := x_error_Tbl;
2320 BIS_UTILITIES_PVT.Add_Error_Message
2321 ( p_error_table => l_error_Tbl_p
2322 , p_error_msg_id => SQLCODE
2323 , p_error_description => SQLERRM
2324 , x_error_table => x_error_Tbl
2325 );
2326 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2327 htp.p('BIS_TARGET_PVT.Update_db_Target:OTHERS'); htp.para;
2328 END IF;
2329 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2330 --
2331 END Update_db_Target;
2332 --
2333 --
2334 PROCEDURE Retrieve_Last_Update_Date
2335 ( p_api_version IN NUMBER
2336 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
2337 , x_last_update_date OUT NOCOPY DATE
2338 , x_return_status OUT NOCOPY VARCHAR2
2339 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2340 )
2341 IS
2342 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2343
2344 BEGIN
2345 x_return_status := FND_API.G_RET_STS_SUCCESS;
2346 --
2347 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Rec.Target_ID)
2348 = FND_API.G_TRUE
2349 AND p_Target_Rec.Target_ID IS NOT NULL
2350 ) THEN
2351 SELECT NVL(LAST_UPDATE_DATE, CREATION_DATE)
2352 INTO x_last_update_date
2353 FROM BIS_TARGET_VALUES bis_target_values
2354 WHERE bis_target_values.TARGET_ID = p_Target_Rec.Target_ID;
2355 ELSE
2356 RAISE FND_API.G_EXC_ERROR;
2357 END IF;
2358 --
2359
2360 --commented RAISE
2361 EXCEPTION
2362 WHEN NO_DATA_FOUND THEN
2363 --added this message
2364 l_error_tbl := x_error_tbl;
2365 BIS_UTILITIES_PVT.Add_Error_Message
2366 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
2367 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2368 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
2369 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2370 , p_error_table => l_error_tbl
2371 , x_error_table => x_error_tbl
2372 );
2373 x_return_status := FND_API.G_RET_STS_ERROR;
2374 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2375 htp.p('BIS_TARGET_PVT.Retrieve_Last_Update_Date:NO_DATA_FOUND');
2376 htp.para;
2377 END IF;
2378 --RAISE FND_API.G_EXC_ERROR;
2379 WHEN FND_API.G_EXC_ERROR THEN
2380 x_return_status:= FND_API.G_RET_STS_ERROR;
2381 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2382 htp.p('BIS_TARGET_PVT.Retrieve_Last_Update_Date:G_EXC_ERROR');
2383 htp.para;
2384 END IF;
2385 -- RAISE;
2386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2387 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2388 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2389 htp.p
2390 ('BIS_TARGET_PVT.Retrieve_Last_Update_Date:G_EXC_UNEXPECTED_ERROR');
2391 htp.para;
2392 END IF;
2393 -- RAISE;
2394 WHEN OTHERS THEN
2395 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2396 l_error_Tbl := x_error_Tbl;
2397 BIS_UTILITIES_PVT.Add_Error_Message
2398 ( p_error_table => l_error_Tbl
2399 , p_error_msg_id => SQLCODE
2400 , p_error_description => SQLERRM
2401 , x_error_table => x_error_Tbl
2402 );
2403 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2404 htp.p('BIS_TARGET_PVT.Retrieve_Last_Update_Date:OTHERS'); htp.para;
2405 END IF;
2406 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2407 --
2408 END Retrieve_Last_Update_Date;
2409 --
2410 --
2411 PROCEDURE Lock_Record
2412 ( p_api_version IN NUMBER
2413 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
2414 , p_timestamp IN VARCHAR := NULL
2415 , x_result OUT NOCOPY VARCHAR2
2416 , x_return_status OUT NOCOPY VARCHAR2
2417 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2418 )
2419 IS
2420 --
2421 l_form_date DATE;
2422 l_last_update_date DATE;
2423 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
2424 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2425 --
2426 BEGIN
2427 x_return_status := FND_API.G_RET_STS_SUCCESS;
2428 --
2429 l_Target_Rec.Target_ID := p_Target_Rec.Target_ID;
2430 BIS_TARGET_PVT.Retrieve_Last_Update_Date
2431 ( p_api_version => 1.0
2432 , p_Target_Rec => l_Target_Rec
2433 , x_last_update_date => l_last_update_date
2434 , x_return_status => x_return_status
2435 , x_error_Tbl => x_error_Tbl
2436 );
2437 IF(p_timestamp IS NOT NULL) THEN
2438 l_form_date := TO_DATE(p_timestamp, BIS_UTILITIES_PVT.G_DATE_FORMAT);
2439 IF(l_form_date = l_last_update_date) THEN
2440 x_result := FND_API.G_TRUE;
2441 ELSE
2442 x_result := FND_API.G_FALSE;
2443 END IF;
2444 ELSE
2445 x_result := FND_API.G_FALSE;
2446 END IF;
2447 --
2448
2449 --commented RAISE
2450 EXCEPTION
2451 WHEN FND_API.G_EXC_ERROR THEN
2452 x_return_status:= FND_API.G_RET_STS_ERROR;
2453 x_result := FND_API.G_FALSE;
2454 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2455 htp.p('BIS_TARGET_PVT.Lock_Record:G_EXC_ERROR'); htp.para;
2456 END IF;
2457 -- RAISE;
2458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2459 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2460 x_result := FND_API.G_FALSE;
2461 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2462 htp.p('BIS_TARGET_PVT.Lock_Record:G_EXC_UNEXPECTED_ERROR');
2463 htp.para;
2464 END IF;
2465 -- RAISE;
2466 WHEN OTHERS THEN
2467 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2468 l_error_Tbl_p := x_error_Tbl;
2469 BIS_UTILITIES_PVT.Add_Error_Message
2470 ( p_error_table => l_error_Tbl_p
2471 , p_error_msg_id => SQLCODE
2472 , p_error_description => SQLERRM
2473 , x_error_table => x_error_Tbl
2474 );
2475 x_result := FND_API.G_FALSE;
2476 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2477 htp.p('BIS_TARGET_PVT.Lock_Record:OTHERS'); htp.para;
2478 END IF;
2479 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2480 --
2481 END Lock_Record;
2482 --
2483 --
2484 PROCEDURE Retrieve_Measure_Dim_Values
2485 ( p_api_version IN NUMBER
2486 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
2487 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
2488 , p_User_ID IN NUMBER := NULL
2489 , p_User_Name IN VARCHAR2 := NULL
2490 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
2491 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
2492 , x_return_status OUT NOCOPY VARCHAR2
2493 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2494 )
2495 IS
2496 --
2497 l_ind2 NUMBER;
2498 l_user_id NUMBER(15);
2499 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
2500 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
2501 l_Measure_Security_Tbl BIS_MEASURE_SECURITY_PUB.Measure_Security_Tbl_Type;
2502 l_Responsibility_Tbl BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_Type;
2503 l_Dim_Level_Value_Tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
2504 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2505
2506 --
2507 BEGIN
2508 x_return_status := FND_API.G_RET_STS_SUCCESS;
2509 --
2510 IF(p_User_ID IS NULL AND p_User_Name IS NOT NULL) THEN
2511 SELECT user_id
2512 INTO l_user_id
2513 FROM FND_USER
2514 WHERE user_name = p_User_Name;
2515 ELSE
2516 l_user_id := p_User_ID;
2517 END IF;
2518 --
2519 BIS_TARGET_LEVEL_PUB.Retrieve_Target_Level
2520 ( p_api_version => 1.0
2521 , p_Target_Level_Rec => p_Target_Level_Rec
2522 , p_all_info => FND_API.G_FALSE
2523 , x_Target_Level_Rec => l_Target_Level_Rec
2524 , x_return_status => x_return_status
2525 , x_error_Tbl => x_error_Tbl
2526 );
2527 --
2528 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level
2529 ( p_api_version => 1.0
2530 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
2531 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
2532 , x_return_status => x_return_status
2533 , x_error_Tbl => x_error_Tbl
2534 );
2535 --
2536 BIS_DIM_LEVEL_VALUE_PVT.Get_DimensionX_Values
2537 ( p_api_version => 1.0
2538 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
2539 , x_Dim_Level_Value_Tbl => x_Dim_Level_Value_Tbl
2540 , x_return_status => x_return_status
2541 , x_error_Tbl => x_error_Tbl
2542 );
2543 --
2544 -- populate error table if there are no rows in the out NOCOPY table
2545 IF(x_Dim_Level_Value_Tbl.COUNT = 0) THEN
2546 --added more params
2547 l_error_tbl := x_error_tbl;
2548 BIS_UTILITIES_PVT.Add_Error_Message
2549 ( p_error_msg_name => 'BIS_NO_DIMX_ACCESS'
2550 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2551 , p_error_proc_name => 'BIS_TARGET_PVT.Retrieve_Measure_Dim_Values'
2552 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2553 , p_error_table => l_error_tbl
2554 , x_error_table => x_error_tbl
2555 );
2556 END IF;
2557
2558 --commented RAISE
2559 EXCEPTION
2560 WHEN NO_DATA_FOUND THEN
2561 x_return_status:= FND_API.G_RET_STS_ERROR;
2562 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2563 htp.p('BIS_TARGET_PVT.Retrieve_Measure_Dim_Values:NO_DATA_FOUND');
2564 htp.para;
2565 END IF;
2566 -- RAISE FND_API.G_EXC_ERROR;
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 x_return_status:= FND_API.G_RET_STS_ERROR;
2569 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2570 htp.p('BIS_TARGET_PVT.Retrieve_Measure_Dim_Values:G_EXC_ERROR');
2571 htp.para;
2572 END IF;
2573 -- RAISE;
2574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2576 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2577 htp.p
2578 ('BIS_TARGET_PVT.Retrieve_Measure_Dim_Values:G_EXC_UNEXPECTED_ERROR');
2579 htp.para;
2580
2581 END IF;
2582 -- RAISE;
2583 WHEN OTHERS THEN
2584 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2585 l_error_Tbl := x_error_Tbl;
2586 BIS_UTILITIES_PVT.Add_Error_Message
2587 ( p_error_table => l_error_Tbl
2588 , p_error_msg_id => SQLCODE
2589 , p_error_description => SQLERRM
2590 , x_error_table => x_error_Tbl
2591 );
2592 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2593 htp.p('BIS_TARGET_PVT.Retrieve_Measure_Dim_Values:OTHERS'); htp.para;
2594 END IF;
2595 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2596 --
2597 END Retrieve_Measure_Dim_Values;
2598 --
2599 --
2600 PROCEDURE GetQueryStatement
2601 ( p_Select_clause IN VARCHAR2
2602 , p_from_clause IN VARCHAR2
2603 , p_where_clause IN VARCHAR2
2604 , p_order_by_clause IN VARCHAR2
2605 , x_query_statement OUT NOCOPY VARCHAR2
2606 )
2607 IS
2608 BEGIN
2609 x_query_statement := p_select_clause
2610 || p_from_clause
2611 || p_where_clause
2612 || p_order_by_clause;
2613 --
2614 --commented RAISE
2615 EXCEPTION
2616 WHEN OTHERS THEN
2617 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2618 htp.p('BIS_TARGET_PVT.GetQueryStatement'); htp.para;
2619 END IF;
2620 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2621 END GetQueryStatement;
2622 --
2623 --
2624
2625 PROCEDURE GetViewNames
2626 ( p_target_level_id IN VARCHAR2
2627 , x_org_view_name OUT NOCOPY VARCHAR2
2628 , x_time_view_name OUT NOCOPY VARCHAR2
2629 , x_dim1_view_name OUT NOCOPY VARCHAR2
2630 , x_dim2_view_name OUT NOCOPY VARCHAR2
2631 , x_dim3_view_name OUT NOCOPY VARCHAR2
2632 , x_dim4_view_name OUT NOCOPY VARCHAR2
2633 , x_dim5_view_name OUT NOCOPY VARCHAR2
2634 , x_dim6_view_name OUT NOCOPY VARCHAR2
2635 , x_dim7_view_name OUT NOCOPY VARCHAR2
2636 )
2637 IS
2638 --
2639 l_bisbv_target_levels BISBV_TARGET_LEVELS%ROWTYPE;
2640 --
2641 BEGIN
2642 -- retrieve the target level record
2643 SELECT *
2644 INTO l_bisbv_target_levels
2645 FROM BISBV_TARGET_LEVELS
2646 WHERE TARGET_LEVEL_ID = TO_NUMBER(p_target_level_id);
2647 --
2648 -- retrieve the Org level view name
2649 IF(l_bisbv_target_levels.ORG_LEVEL_ID IS NOT NULL) THEN
2650 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2651 INTO x_org_view_name
2652 FROM
2653 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2654 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2655 = l_bisbv_target_levels.ORG_LEVEL_ID;
2656 END IF;
2657 --
2658 -- retrieve the Time level view name
2659 IF(l_bisbv_target_levels.TIME_LEVEL_ID IS NOT NULL) THEN
2660 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2661 INTO x_time_view_name
2662 FROM
2663 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2664 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2665 = l_bisbv_target_levels.TIME_LEVEL_ID;
2666 END IF;
2667 --
2668 -- retrieve the dim1 level view name
2669 IF(l_bisbv_target_levels.DIMENSION1_LEVEL_ID IS NOT NULL) THEN
2670 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2671 INTO x_dim1_view_name
2672 FROM
2673 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2674 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2675 = l_bisbv_target_levels.DIMENSION1_LEVEL_ID;
2676 END IF;
2677 --
2678 -- retrieve the dim2 level view name
2679 IF(l_bisbv_target_levels.DIMENSION2_LEVEL_ID IS NOT NULL) THEN
2680 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2681 INTO x_dim2_view_name
2682 FROM
2683 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2684 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2685 = l_bisbv_target_levels.DIMENSION2_LEVEL_ID;
2686 END IF;
2687 --
2688 -- retrieve the dim3 level view name
2689 IF(l_bisbv_target_levels.DIMENSION3_LEVEL_ID IS NOT NULL) THEN
2690 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2691 INTO x_dim3_view_name
2692 FROM
2693 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2694 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2695 = l_bisbv_target_levels.DIMENSION3_LEVEL_ID;
2696 END IF;
2697 --
2698 -- retrieve the dim4 level view name
2699 IF(l_bisbv_target_levels.DIMENSION4_LEVEL_ID IS NOT NULL) THEN
2700 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2701 INTO x_dim4_view_name
2702 FROM
2703 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2704 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2705 = l_bisbv_target_levels.DIMENSION4_LEVEL_ID;
2706 END IF;
2707 --
2708 -- retrieve the dim5 level view name
2709 IF(l_bisbv_target_levels.DIMENSION5_LEVEL_ID IS NOT NULL) THEN
2710 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2711 INTO x_dim5_view_name
2712 FROM
2713 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2714 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2715 = l_bisbv_target_levels.DIMENSION5_LEVEL_ID;
2716 END IF;
2717 -- retrieve the dim6 level view name
2718 IF(l_bisbv_target_levels.DIMENSION6_LEVEL_ID IS NOT NULL) THEN
2719 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2720 INTO x_dim6_view_name
2721 FROM
2722 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2723 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2724 = l_bisbv_target_levels.DIMENSION6_LEVEL_ID;
2725 END IF;
2726 -- retrieve the dim7 level view name
2727 IF(l_bisbv_target_levels.DIMENSION7_LEVEL_ID IS NOT NULL) THEN
2728 SELECT bisbv_dimension_levels.LEVEL_VALUES_VIEW_NAME
2729 INTO x_dim7_view_name
2730 FROM
2731 BISBV_DIMENSION_LEVELS bisbv_dimension_levels
2732 WHERE bisbv_dimension_levels.DIMENSION_LEVEL_ID
2733 = l_bisbv_target_levels.DIMENSION7_LEVEL_ID;
2734 END IF;
2735 --
2736 --
2737 --commented RAISE
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2741 htp.p('BIS_TARGET_PVT.GetViewNames'); htp.para;
2742 END IF;
2743 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2744 END GetViewNames;
2745 --
2746 --
2747 FUNCTION GetComputingUserFunctionName
2748 ( p_computing_function_id IN NUMBER
2749 )
2750 RETURN VARCHAR2
2751 IS
2752 --
2753 l_user_function_name VARCHAR2(1000);
2754 --
2755 BEGIN
2756 IF(p_computing_function_id IS NULL) THEN
2757 l_user_function_name := NULL;
2758 ELSE
2759 BEGIN
2760 SELECT fnd_form_functions_tl.USER_FUNCTION_NAME
2761 INTO l_user_function_name
2762 FROM FND_FORM_FUNCTIONS_TL fnd_form_functions_tl
2763 WHERE fnd_form_functions_tl.FUNCTION_ID = p_computing_function_id
2764 AND fnd_form_functions_tl.LANGUAGE = USERENV('LANG');
2765 EXCEPTION
2766 WHEN NO_DATA_FOUND THEN
2767 l_user_function_name := NULL;
2768 END;
2769 END IF;
2770 --
2771 RETURN l_user_function_name;
2772 --
2773
2774 --commented RAISE
2775 EXCEPTION
2776 WHEN OTHERS THEN
2777 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2778 htp.p('BIS_TARGET_PVT.GetComputingUserFunctionName:OTHERS');
2779 htp.para;
2780 END IF;
2781 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2782 END GetComputingUserFunctionName;
2783 --
2784 --
2785 FUNCTION GetNotifyResponsibilityName
2786 ( p_responsibility_short_name IN VARCHAR2
2787 )
2788 RETURN VARCHAR2
2789 IS
2790 --
2791 l_resp_name VARCHAR2(1000);
2792 --
2793 BEGIN
2794 IF(p_responsibility_short_name IS NULL) THEN
2795 l_resp_name := NULL;
2796 ELSE
2797 SELECT wf_roles.DISPLAY_NAME
2798 INTO l_resp_name
2799 FROM WF_ROLE_LOV_VL wf_roles
2800 WHERE wf_roles.NAME = p_responsibility_short_name;
2801 END IF;
2802 --
2803 RETURN l_resp_name;
2804 --
2805
2806 --commented RAISE
2807 EXCEPTION
2808 WHEN OTHERS THEN
2809 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
2810 htp.p('BIS_TARGET_PVT.GetNotifyResponsibilityName:OTHERS');
2811 htp.para;
2812 END IF;
2813 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2814 END GetNotifyResponsibilityName;
2815 --
2816 --
2817 FUNCTION GetSetOfBookID
2818 RETURN VARCHAR2
2819 IS
2820 BEGIN
2821
2822 Return BIS_TARGET_PVT.G_SET_OF_BOOK_ID;
2823
2824 END GetSetOfBookID;
2825 --
2826 --
2827 FUNCTION Get_Target
2828 ( p_computing_function_id IN NUMBER
2829 , p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type
2830 )
2831 RETURN NUMBER
2832 IS
2833 l_target NUMBER;
2834 l_computed_target_short_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
2835 l_target_level_short_name VARCHAR2(32000);
2836 l_target_level_name VARCHAR2(32000);
2837 l_plan_short_name VARCHAR2(32000);
2838 l_plan_name VARCHAR2(32000);
2839 l_Org_Level_Value_name VARCHAR2(32000);
2840 l_Time_Level_Value_name VARCHAR2(32000);
2841 l_Dim1_Level_Value_name VARCHAR2(32000);
2842 l_Dim2_Level_Value_name VARCHAR2(32000);
2843 l_Dim3_Level_Value_name VARCHAR2(32000);
2844 l_Dim4_Level_Value_name VARCHAR2(32000);
2845 l_Dim5_Level_Value_name VARCHAR2(32000);
2846 l_Dim6_Level_Value_name VARCHAR2(32000);
2847 l_Dim7_Level_Value_name VARCHAR2(32000);
2848 l_Org_Level_Value_id VARCHAR2(32000);
2849 l_Time_Level_Value_id VARCHAR2(32000);
2850 l_Dim1_Level_Value_id VARCHAR2(32000);
2851 l_Dim2_Level_Value_id VARCHAR2(32000);
2852 l_Dim3_Level_Value_id VARCHAR2(32000);
2853 l_Dim4_Level_Value_id VARCHAR2(32000);
2854 l_Dim5_Level_Value_id VARCHAR2(32000);
2855 l_Dim6_Level_Value_id VARCHAR2(32000);
2856 l_Dim7_Level_Value_id VARCHAR2(32000);
2857
2858 l_cursor INTEGER;
2859 l_stmt VARCHAR2(32000);
2860 l_sql_result INTEGER := 0;
2861
2862 BEGIN
2863
2864 Select FUNCTION_NAME
2865 into l_computed_target_short_name
2866 from fnd_form_functions_vl
2867 where function_id = p_computing_function_id;
2868
2869 BIS_UTILITIES_PVT.Replace_String
2870 ( p_target_rec.target_level_short_name
2871 , l_target_level_short_name
2872 );
2873 BIS_UTILITIES_PVT.Replace_String
2874 ( p_target_rec.target_level_name
2875 , l_target_level_name
2876 );
2877 BIS_UTILITIES_PVT.Replace_String
2878 ( p_target_rec.plan_short_name
2879 , l_plan_short_name
2880 );
2881 BIS_UTILITIES_PVT.Replace_String
2882 ( p_target_rec.plan_name
2883 , l_plan_name
2884 );
2885 BIS_UTILITIES_PVT.Replace_String
2886 ( p_target_rec.Org_Level_Value_ID
2887 , l_Org_Level_Value_ID
2888 );
2889 BIS_UTILITIES_PVT.Replace_String
2890 ( p_target_rec.Org_Level_Value_name
2891 , l_Org_Level_Value_name
2892 );
2893 BIS_UTILITIES_PVT.Replace_String
2894 ( p_target_rec.Time_Level_Value_ID
2895 , l_Time_Level_Value_ID
2896 );
2897 BIS_UTILITIES_PVT.Replace_String
2898 ( p_target_rec.Time_Level_Value_name
2899 , l_Time_Level_Value_name
2900 );
2901 BIS_UTILITIES_PVT.Replace_String
2902 ( p_target_rec.Dim1_Level_Value_ID
2903 , l_Dim1_Level_Value_ID
2904 );
2905 BIS_UTILITIES_PVT.Replace_String
2906 ( p_target_rec.Dim1_Level_Value_name
2907 , l_Dim1_Level_Value_name
2908 );
2909 BIS_UTILITIES_PVT.Replace_String
2910 ( p_target_rec.Dim2_Level_Value_ID
2911 , l_Dim2_Level_Value_ID
2912 );
2913 BIS_UTILITIES_PVT.Replace_String
2914 ( p_target_rec.Dim2_Level_Value_name
2915 , l_Dim2_Level_Value_name
2916 );
2917 BIS_UTILITIES_PVT.Replace_String
2918 ( p_target_rec.Dim3_Level_Value_ID
2919 , l_Dim3_Level_Value_ID
2920 );
2921 BIS_UTILITIES_PVT.Replace_String
2922 ( p_target_rec.Dim3_Level_Value_name
2923 , l_Dim3_Level_Value_name
2924 );
2925 BIS_UTILITIES_PVT.Replace_String
2926 ( p_target_rec.Dim4_Level_Value_ID
2927 , l_Dim4_Level_Value_ID
2928 );
2929 BIS_UTILITIES_PVT.Replace_String
2930 ( p_target_rec.Dim4_Level_Value_name
2931 , l_Dim4_Level_Value_name
2932 );
2933 BIS_UTILITIES_PVT.Replace_String
2934 ( p_target_rec.Dim5_Level_Value_ID
2935 , l_Dim5_Level_Value_ID
2936 );
2937 BIS_UTILITIES_PVT.Replace_String
2938 ( p_target_rec.Dim5_Level_Value_name
2939 , l_Dim5_Level_Value_name
2940 );
2941 BIS_UTILITIES_PVT.Replace_String
2942 ( p_target_rec.Dim6_Level_Value_ID
2943 , l_Dim6_Level_Value_ID
2944 );
2945 BIS_UTILITIES_PVT.Replace_String
2946 ( p_target_rec.Dim6_Level_Value_name
2947 , l_Dim6_Level_Value_name
2948 );
2949 BIS_UTILITIES_PVT.Replace_String
2950 ( p_target_rec.Dim7_Level_Value_ID
2951 , l_Dim7_Level_Value_ID
2952 );
2953 BIS_UTILITIES_PVT.Replace_String
2954 ( p_target_rec.Dim7_Level_Value_name
2955 , l_Dim7_Level_Value_name
2956 );
2957
2958 l_stmt :=
2959 'Declare '||
2960 ' l_target_rec BIS_TARGET_PUB.Target_Rec_Type; '||
2961 'Begin '||
2962 ' l_target_rec.Target_ID := ' || -- p_target_rec.Target_id||';'||
2963 nvl( to_char(p_target_rec.Target_id) , 'null') || ' ; ' ||
2964 ' l_target_rec.Target_Level_ID := '||p_target_rec.Target_Level_ID||';'||
2965 ' l_target_rec.Target_Level_Short_Name := '
2966 ||l_target_level_short_name||';'||
2967 ' l_target_rec.Target_Level_Name := '||l_Target_Level_Name||';'||
2968 ' l_target_rec.Plan_ID := '||p_target_rec.Plan_ID||';'||
2969 ' l_target_rec.Plan_Short_Name := '||l_Plan_Short_Name||';'||
2970 ' l_target_rec.Plan_Name := '||l_Plan_Name||';'||
2971 ' l_target_rec.Org_Level_Value_ID := '||l_Org_Level_Value_ID||';'||
2972 ' l_target_rec.Org_Level_Value_Name := '||l_Org_Level_Value_Name||';'||
2973 ' l_target_rec.Time_Level_Value_ID := '||l_Time_Level_Value_ID||';'||
2974 ' l_target_rec.Time_Level_Value_Name := '||l_Time_Level_Value_Name||';'||
2975 ' l_target_rec.Dim1_Level_Value_ID := '||l_Dim1_Level_Value_ID||';'||
2976 ' l_target_rec.Dim1_Level_Value_Name := '||l_Dim1_Level_Value_Name||';'||
2977 ' l_target_rec.Dim2_Level_Value_ID := '||l_Dim2_Level_Value_ID||';'||
2978 ' l_target_rec.Dim2_Level_Value_Name := '||l_Dim2_Level_Value_Name||';'||
2979 ' l_target_rec.Dim3_Level_Value_ID := '||l_Dim3_Level_Value_ID||';'||
2980 ' l_target_rec.Dim3_Level_Value_Name := '||l_Dim3_Level_Value_Name||';'||
2981 ' l_target_rec.Dim4_Level_Value_ID := '||l_Dim4_Level_Value_ID||';'||
2982 ' l_target_rec.Dim4_Level_Value_Name := '||l_Dim4_Level_Value_Name||';'||
2983 ' l_target_rec.Dim5_Level_Value_ID := '||l_Dim5_Level_Value_ID||';'||
2984 ' l_target_rec.Dim5_Level_Value_Name := '||l_Dim5_Level_Value_Name||';'||
2985 ' l_target_rec.Dim6_Level_Value_ID := '||l_Dim6_Level_Value_ID||';'||
2986 ' l_target_rec.Dim6_Level_Value_Name := '||l_Dim6_Level_Value_Name||';'||
2987 ' l_target_rec.Dim7_Level_Value_ID := '||l_Dim7_Level_Value_ID||';'||
2988 ' l_target_rec.Dim7_Level_Value_Name := '||l_Dim7_Level_Value_Name||';'||
2989 ' :l_target := '||l_computed_target_short_name||
2990 ' ( l_target_rec ); '||
2991 'End; ' ;
2992
2993 l_cursor := DBMS_SQL.OPEN_CURSOR;
2994
2995 DBMS_SQL.PARSE( c => l_cursor
2996 , statement => l_stmt
2997 , language_flag => DBMS_SQL.NATIVE
2998 );
2999 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_target', l_target);
3000 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
3001 DBMS_SQL.VARIABLE_VALUE(l_cursor, ':l_target', l_target);
3002
3003 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
3004 DBMS_SQL.CLOSE_CURSOR(l_cursor);
3005 END IF;
3006
3007 RETURN l_target;
3008
3009 --commented RAISE
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 -- htp.header(5,'Exception in Get Target');
3013 htp.p(SQLERRM);
3014 -- RAISE;
3015
3016 END Get_Target;
3017 --
3018 --
3019 FUNCTION Get_Target
3020 ( p_computing_function_id IN NUMBER
3021 , p_target_level_id IN NUMBER
3022 )
3023 RETURN NUMBER
3024 IS
3025 l_target NUMBER;
3026 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
3027 BEGIN
3028
3029 l_target_rec.target_level_id := p_target_level_id;
3030 l_target := Get_Target
3031 ( p_computing_function_id => p_computing_function_id
3032 , p_target_rec => l_target_rec
3033 );
3034 RETURN l_target;
3035
3036 END Get_Target;
3037 --
3038 --
3039 FUNCTION getValue
3040 ( p_id VARCHAR2
3041 , p_value_id_table BIS_LOV_PVT.Value_Id_Table
3042 )
3043 return varchar2
3044 IS
3045 BEGIN
3046 -- SHOULD USE BINARY SEARCH AS IDs ARE SORTED. BEING LAZY
3047 -- dbms_output.put_line('input id = '||p_id);
3048 for i in 1 .. p_value_id_table.count loop
3049 -- dbms_output.put_line('testing id '||p_value_id_table(i).id);
3050 if (p_id = p_value_id_table(i).id) then
3051 -- dbms_output.put_line('returning '||p_value_id_table(i).value);
3052 return p_value_id_table(i).value;
3053 exit;
3054 end if;
3055 end loop;
3056 return null;
3057 END getValue;
3058
3059 PROCEDURE RetrieveValues
3060 ( p_view_name IN VARCHAR2
3061 , p_where_clause IN VARCHAR2
3062 , x_value_id_table OUT NOCOPY BIS_LOV_PVT.Value_Id_Table
3063 )
3064 IS
3065 l_query VARCHAR2(32000);
3066 l_sql_result INTEGER := 0;
3067
3068 l_size NUMBER := 100000;
3069 l_retrieved NUMBER;
3070 l_cursor INTEGER;
3071 l_level_value DBMS_SQL.VARCHAR2_TABLE;
3072 l_level_id DBMS_SQL.VARCHAR2_TABLE;
3073 l_rec BIS_LOV_PVT.Value_Id_record;
3074 BEGIN
3075 l_query := 'select distinct id, value from '||p_view_name||' '||p_where_clause || ' order by id';
3076 --dbms_output.put_line(l_query);
3077
3078 l_cursor := DBMS_SQL.OPEN_CURSOR;
3079 DBMS_SQL.PARSE( c => l_cursor
3080 , statement => l_query
3081 , language_flag => DBMS_SQL.NATIVE
3082 );
3083 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_level_id, l_size, 1);
3084 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_level_value, l_size, 1);
3085 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
3086 LOOP
3087 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
3088 EXIT WHEN l_retrieved = 0;
3089 -- retrieve the values into vars
3090 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_level_id);
3091 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_level_value);
3092 --
3093 FOR l_ind1 IN 1..l_level_id.COUNT LOOP
3094 l_rec.id := l_level_id(l_ind1);
3095 l_rec.value := l_level_value(l_ind1);
3096 x_value_id_table(x_value_id_table.count+1) := l_rec;
3097 -- dbms_output.put_line(l_rec.id||' '||l_rec.value);
3098 END LOOP;
3099 --
3100 EXIT WHEN l_retrieved < l_size;
3101 END LOOP;
3102 END RetrieveValues;
3103
3104 Function getDate(p_query VARCHAR2)
3105 return DATE
3106 IS
3107 l_cursor INTEGER;
3108 l_date DATE;
3109 l_dummy INTEGER;
3110 BEGIN
3111 l_cursor := DBMS_SQL.OPEN_CURSOR;
3112 DBMS_SQL.PARSE( c => l_cursor
3113 , statement => p_query
3114 , language_flag => DBMS_SQL.NATIVE
3115 );
3116
3117 dbms_sql.define_column(l_cursor, 1, l_date);
3118 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
3119
3120 loop
3121 if (dbms_sql.fetch_rows(l_cursor) = 0) then
3122 exit;
3123 end if;
3124
3125 dbms_sql.column_value(l_cursor, 1, l_date);
3126 end loop;
3127
3128 return l_date;
3129 END getDate;
3130
3131 Function getStartDate
3132 ( p_view_name IN VARCHAR2
3133 , p_time_from IN VARCHAR2
3134 )
3135 return DATE
3136 IS
3137 l_query VARCHAR2(32000);
3138 BEGIN
3139 l_query := 'select start_date from '|| p_view_name;
3140 l_query := l_query || ' where id like ''' ||p_time_from||'''';
3141
3142 return getDate(l_query);
3143 END getStartDate;
3144
3145 Function getEndDate
3146 ( p_view_name IN VARCHAR2
3147 , p_time_to IN VARCHAR2
3148 )
3149 return DATE
3150 IS
3151 l_query VARCHAR2(32000);
3152 BEGIN
3153 l_query := 'select end_date from '|| p_view_name;
3154 l_query := l_query || ' where id like ''' ||p_time_to||'''';
3155
3156 return getDate(l_query);
3157 END getEndDate;
3158
3159 -- Retrieves the time level values for the given target
3160 --
3161 PROCEDURE Retrieve_Time_level_value
3162 ( p_api_version IN NUMBER
3163 , p_Target_Rec IN BIS_Target_PUB.Target_Rec_Type
3164 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
3165 , x_dimension_level_number OUT NOCOPY NUMBER
3166 , x_return_status OUT NOCOPY VARCHAR2
3167 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3168 )
3169 IS
3170
3171 l_dimension_level_number NUMBER;
3172 l_target_level_rec BIS_Target_Level_PUB.Target_Level_rec_Type;
3173 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
3174 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
3175 l_dim_level_value_tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
3176 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3177
3178
3179 CURSOR cr_tar_dim_value(p_target_id NUMBER) IS
3180 select
3181 dim1_level_value_id,
3182 dim2_level_value_id,
3183 dim3_level_value_id,
3184 dim4_level_value_id,
3185 dim5_level_value_id,
3186 dim6_level_value_id,
3187 dim7_level_value_id
3188 from bisbv_targets
3189 where target_id = p_target_id;
3190
3191 BEGIN
3192
3193 --BIS_UTILITIES_PUB.put_line(p_text =>'in Retrieve_Time_level_value. target level: '
3194 --||p_Target_Rec.target_level_id
3195 --||', target id: '||p_Target_Rec.target_id
3196 --);
3197
3198 l_target_level_rec.target_level_id := p_Target_Rec.target_level_id;
3199
3200 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
3201 ( p_api_version => 1.0
3202 , p_Target_Level_Rec => l_Target_Level_Rec
3203 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
3204 , x_dimension_level_number => l_dimension_level_number
3205 , x_return_status => x_return_status
3206 , x_error_Tbl => x_error_Tbl
3207 );
3208
3209 -- Following change is to get the Level info if there is no value too
3210
3211 x_Dim_Level_Value_Rec.Dimension_Level_id := l_Dimension_Level_Rec.Dimension_Level_id;
3212 x_Dim_Level_Value_Rec.Dimension_Level_short_name := l_Dimension_Level_Rec.Dimension_Level_short_name;
3213 x_Dim_Level_Value_Rec.Dimension_Level_name := l_Dimension_Level_Rec.Dimension_Level_name;
3214
3215 x_dimension_level_number := l_dimension_level_number;
3216
3217 --BIS_UTILITIES_PUB.put_line(p_text =>'retrieved time level: '||l_Dimension_Level_Rec.Dimension_Level_id
3218 --||', time level number: '||l_dimension_level_number);
3219
3220 IF (BIS_UTILITIES_PVT.Value_Not_Missing(p_Target_Rec.target_id)
3221 = FND_API.G_TRUE)
3222 AND (BIS_UTILITIES_PVT.Value_Not_Null(p_Target_Rec.target_id)
3223 = FND_API.G_TRUE)
3224 THEN
3225 OPEN cr_tar_dim_value(p_Target_Rec.target_id);
3226 FETCH cr_tar_dim_value INTO
3227 l_dim_level_value_tbl(1).dimension_level_value_id,
3228 l_dim_level_value_tbl(2).dimension_level_value_id,
3229 l_dim_level_value_tbl(3).dimension_level_value_id,
3230 l_dim_level_value_tbl(4).dimension_level_value_id,
3231 l_dim_level_value_tbl(5).dimension_level_value_id,
3232 l_dim_level_value_tbl(6).dimension_level_value_id,
3233 l_dim_level_value_tbl(7).dimension_level_value_id;
3234
3235 --added this
3236 if cr_tar_dim_value%NOTFOUND then
3237 --added this message
3238 l_error_tbl := x_error_tbl;
3239 BIS_UTILITIES_PVT.Add_Error_Message
3240 ( p_error_msg_name => 'BIS_INVALID_TARGET_VALUE'
3241 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3242 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Time_Level_Value'
3243 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3244 , p_error_table => l_error_tbl
3245 , x_error_table => x_error_tbl
3246 );
3247 x_return_status := FND_API.G_RET_STS_ERROR;
3248 RAISE FND_API.G_EXC_ERROR;
3249 end if;
3250 CLOSE cr_tar_dim_value;
3251
3252 END IF;
3253
3254 FOR i IN 1..l_dim_level_value_tbl.COUNT LOOP
3255 --BIS_UTILITIES_PUB.put_line(p_text =>'i: '||i);
3256 IF i = l_dimension_level_number THEN
3257 --BIS_UTILITIES_PUB.put_line(p_text =>'got period: '
3258 --||l_Dim_Level_Value_tbl(i).Dimension_Level_Value_id);
3259
3260 l_Dim_Level_Value_tbl(i).Dimension_Level_id
3261 := l_Dimension_Level_Rec.Dimension_Level_id;
3262 l_Dim_Level_Value_tbl(i).Dimension_Level_short_name
3263 := l_Dimension_Level_Rec.Dimension_Level_short_name;
3264 l_Dim_Level_Value_tbl(i).Dimension_Level_name
3265 := l_Dimension_Level_Rec.Dimension_Level_name;
3266 x_Dim_Level_Value_Rec := l_Dim_Level_Value_tbl(i);
3267 x_dimension_level_number := l_dimension_level_number;
3268 EXIT;
3269 END IF;
3270 END LOOP;
3271
3272 EXCEPTION
3273 --added this
3274 WHEN FND_API.G_EXC_ERROR THEN
3275 BIS_UTILITIES_PUB.put_line(p_text =>'Error 1 while getting time level value: '||SQLERRM);
3276 x_return_status := FND_API.G_RET_STS_ERROR;
3277 IF cr_tar_dim_value%ISOPEN THEN CLOSE cr_tar_dim_value; END IF;
3278 WHEN OTHERS THEN
3279 BIS_UTILITIES_PUB.put_line(p_text =>'Error 2 while getting time level value: '||SQLERRM);
3280 IF cr_tar_dim_value%ISOPEN THEN CLOSE cr_tar_dim_value; END IF;
3281 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3282 l_error_Tbl := x_error_Tbl;
3283 BIS_UTILITIES_PVT.Add_Error_Message
3284 ( p_error_table => l_error_Tbl
3285 , p_error_msg_id => SQLCODE
3286 , p_error_description => SQLERRM
3287 , x_error_table => x_error_Tbl
3288 );
3289 END Retrieve_Time_level_value;
3290
3291 -- Retrieves the time level for the given target level
3292 --
3293 PROCEDURE Retrieve_Time_level_value
3294 ( p_api_version IN NUMBER
3295 , p_Target_id IN NUMBER
3296 , x_Dim_Level_Value_ID OUT NOCOPY VARCHAR2
3297 , x_Dim_Level_Value_name OUT NOCOPY VARCHAR2
3298 , x_dimension_level_number OUT NOCOPY NUMBER
3299 , x_return_status OUT NOCOPY VARCHAR2
3300 )
3301 IS
3302
3303 l_Target_Rec BIS_Target_PUB.Target_Rec_Type;
3304 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
3305 l_dimension_level_number NUMBER;
3306 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3307 l_return_status VARCHAR2(32000);
3308
3309 BEGIN
3310
3311 l_Target_Rec.Target_id := p_Target_id;
3312
3313 Retrieve_Time_level_value
3314 ( p_api_version => 1.0
3315 , p_Target_Rec => l_Target_Rec
3316 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
3317 , x_dimension_level_number => l_dimension_level_number
3318 , x_return_status => x_return_status
3319 , x_error_Tbl => l_error_Tbl
3320 );
3321
3322 x_Dim_Level_Value_ID := l_Dim_Level_Value_Rec.dimension_Level_Value_ID;
3323 x_Dim_Level_Value_name := l_Dim_Level_Value_Rec.dimension_Level_Value_name;
3324 x_dimension_level_number := l_dimension_level_number;
3325
3326 END Retrieve_Time_level_value;
3327
3328
3329 --
3330 -- Retrieves the Org level values for the given target
3331 --
3332
3333 PROCEDURE Retrieve_Org_level_value
3334 ( p_api_version IN NUMBER
3335 , p_Target_Rec IN BIS_Target_PUB.Target_Rec_Type
3336 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
3337 , x_dimension_level_number OUT NOCOPY NUMBER
3338 , x_return_status OUT NOCOPY VARCHAR2
3339 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3340 )
3341 IS
3342
3343 l_dimension_level_number NUMBER;
3344 l_target_level_rec BIS_Target_Level_PUB.Target_Level_rec_Type;
3345 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
3346 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
3347 l_dim_level_value_tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
3348 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
3349
3350
3351 BEGIN
3352
3353
3354 l_target_level_rec.target_level_id := p_Target_Rec.target_level_id;
3355
3356 -- BIS_UTILITIES_PUB.put_line(p_text =>' Target level id inside Retrieve_Org_level_value = ' || l_target_level_rec.target_level_id ) ;
3357
3358 BIS_TARGET_LEVEL_PVT.Retrieve_Org_level
3359 ( p_api_version => 1.0
3360 , p_Target_Level_Rec => l_Target_Level_Rec
3361 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
3362 , x_dimension_level_number => l_dimension_level_number
3363 , x_return_status => x_return_status
3364 , x_error_Tbl => x_error_Tbl
3365 );
3366
3367
3368 -- Following change is to get the Level info if there is no value too
3369
3370 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside bisvtarb l_Dimension_Level_Rec.DIMENSION_ID = ' || l_Dimension_Level_Rec.DIMENSION_ID ) ;
3371
3372 x_Dim_Level_Value_Rec.Dimension_Level_id := l_Dimension_Level_Rec.Dimension_Level_id;
3373 x_Dim_Level_Value_Rec.Dimension_Level_short_name := l_Dimension_Level_Rec.Dimension_Level_short_name;
3374 x_Dim_Level_Value_Rec.Dimension_Level_name := l_Dimension_Level_Rec.Dimension_Level_name;
3375
3376 x_dimension_level_number := l_dimension_level_number;
3377
3378 l_dim_level_value_tbl(1).dimension_level_value_id := p_target_rec.dim1_level_value_id;
3379 l_dim_level_value_tbl(2).dimension_level_value_id := p_target_rec.dim2_level_value_id;
3380 l_dim_level_value_tbl(3).dimension_level_value_id := p_target_rec.dim3_level_value_id;
3381 l_dim_level_value_tbl(4).dimension_level_value_id := p_target_rec.dim4_level_value_id;
3382 l_dim_level_value_tbl(5).dimension_level_value_id := p_target_rec.dim5_level_value_id;
3383 l_dim_level_value_tbl(6).dimension_level_value_id := p_target_rec.dim6_level_value_id;
3384 l_dim_level_value_tbl(7).dimension_level_value_id := p_target_rec.dim7_level_value_id;
3385
3386
3387 FOR i IN 1..7 loop -- l_dim_level_value_tbl.COUNT LOOP
3388 --BIS_UTILITIES_PUB.put_line(p_text =>'i: '||i);
3389 IF i = l_dimension_level_number THEN
3390 --BIS_UTILITIES_PUB.put_line(p_text =>'got period: '
3391 --||l_Dim_Level_Value_tbl(i).Dimension_Level_Value_id);
3392
3393 l_Dim_Level_Value_tbl(i).Dimension_Level_id
3394 := l_Dimension_Level_Rec.Dimension_Level_id;
3395 l_Dim_Level_Value_tbl(i).Dimension_Level_short_name
3396 := l_Dimension_Level_Rec.Dimension_Level_short_name;
3397 l_Dim_Level_Value_tbl(i).Dimension_Level_name
3398 := l_Dimension_Level_Rec.Dimension_Level_name;
3399 x_Dim_Level_Value_Rec := l_Dim_Level_Value_tbl(i);
3400 x_dimension_level_number := l_dimension_level_number;
3401
3402 EXIT;
3403
3404 END IF;
3405 END LOOP;
3406
3407 EXCEPTION
3408
3409 --added this
3410 WHEN FND_API.G_EXC_ERROR THEN
3411
3412 BIS_UTILITIES_PUB.put_line(p_text =>'Error 1 while getting Org level value: '||SQLERRM);
3413 x_return_status := FND_API.G_RET_STS_ERROR;
3414
3415
3416 WHEN OTHERS THEN
3417 BIS_UTILITIES_PUB.put_line(p_text =>'Error 2 while getting Org level value: '||SQLERRM);
3418 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3419
3420 l_error_Tbl_p := x_error_Tbl;
3421 BIS_UTILITIES_PVT.Add_Error_Message
3422 ( p_error_table => l_error_Tbl_p
3423 , p_error_msg_id => SQLCODE
3424 , p_error_description => SQLERRM
3425 , x_error_table => x_error_Tbl
3426 );
3427
3428 END Retrieve_Org_level_value;
3429
3430
3431
3432 -- New Procedure to return TargetLevel given the DimensionLevel ShortNames in any sequence
3433 -- and the Measure Short Name
3434
3435 PROCEDURE Retrieve_Target_From_ShNms
3436 ( p_api_version IN NUMBER
3437 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3438 , p_Target_Rec IN BIS_TARGET_PUB.TARGET_REC_TYPE
3439 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
3440 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.TARGET_REC_TYPE
3441 , x_return_status OUT NOCOPY VARCHAR2
3442 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3443 )
3444 IS
3445 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
3446 l_measure_rec BIS_MEASURE_PUB.MEASURE_REC_TYPE;
3447 l_measure_rec_p BIS_MEASURE_PUB.MEASURE_REC_TYPE;
3448 l_dim_level_rec BIS_DIMENSION_LEVEL_PUB.DIMENSION_LEVEL_REC_TYPE;
3449 l_dim_level_value_rec BIS_DIM_lEVEL_VALUE_PUB.DIM_LEVEL_VALUE_REC_TYPE;
3450 l_target_level_id NUMBER;
3451 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3452 l_target_rec_p BIS_TARGET_PUB.TARGET_REC_TYPE;
3453 l_dim_level_rec_p BIS_DIMENSION_LEVEL_PUB.DIMENSION_LEVEL_REC_TYPE;
3454 l_dim_level_value_rec_p BIS_DIM_lEVEL_VALUE_PUB.DIM_LEVEL_VALUE_REC_TYPE;
3455
3456 l_dim1_id NUMBER;
3457 l_dim2_id NUMBER;
3458 l_dim3_id NUMBER;
3459 l_dim4_id NUMBER;
3460 l_dim5_id NUMBER;
3461 l_dim6_id NUMBER;
3462 l_dim7_id NUMBER;
3463 l_dim1_level_id NUMBER;
3464 l_dim2_level_id NUMBER;
3465 l_dim3_level_id NUMBER;
3466 l_dim4_level_id NUMBER;
3467 l_dim5_level_id NUMBER;
3468 l_dim6_level_id NUMBER;
3469 l_dim7_level_id NUMBER;
3470 l_dim1_level_value_id VARCHAR2(32000);
3471 l_dim2_level_value_id VARCHAR2(32000);
3472 l_dim3_level_value_id VARCHAR2(32000);
3473 l_dim4_level_value_id VARCHAR2(32000);
3474 l_dim5_level_value_id VARCHAR2(32000);
3475 l_dim6_level_value_id VARCHAR2(32000);
3476 l_dim7_level_value_id VARCHAR2(32000);
3477
3478 l_dim1_level_short_name VARCHAR2(32000);
3479 l_dim2_level_short_name VARCHAR2(32000);
3480 l_dim3_level_short_name VARCHAR2(32000);
3481 l_dim4_level_short_name VARCHAR2(32000);
3482 l_dim5_level_short_name VARCHAR2(32000);
3483 l_dim6_level_short_name VARCHAR2(32000);
3484 l_dim7_level_short_name VARCHAR2(32000);
3485
3486 l_dim1_level_name VARCHAR2(32000);
3487 l_dim2_level_name VARCHAR2(32000);
3488 l_dim3_level_name VARCHAR2(32000);
3489 l_dim4_level_name VARCHAR2(32000);
3490 l_dim5_level_name VARCHAR2(32000);
3491 l_dim6_level_name VARCHAR2(32000);
3492 l_dim7_level_name VARCHAR2(32000);
3493
3494
3495 CURSOR c_dim_lvl(p_dim_level_short_name in varchar2) IS
3496 SELECT level_id , NVL(BIS_DIMENSION_LEVEL_PUB.GET_CUSTOMIZED_NAME(level_id),name)
3497 FROM bis_levels_vl
3498 WHERE short_name=p_dim_level_short_name;
3499
3500 BEGIN
3501 IF (p_target_level_rec.measure_short_name IS NOT NULL
3502 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.measure_short_name) = FND_API.G_TRUE) THEN
3503 l_measure_rec.measure_short_name := p_target_level_rec.measure_short_name;
3504 END IF;
3505 IF (p_target_level_rec.measure_id IS NOT NULL
3506 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.measure_id) = FND_API.G_TRUE) THEN
3507 l_measure_rec.measure_id := p_target_level_rec.measure_id;
3508 END IF;
3509 --Populate the measure record with all the relevant values
3510 l_measure_rec_p := l_measure_rec;
3511 BIS_MEASURE_PUB.RETRIEVE_MEASURE( p_api_version => p_api_version
3512 ,p_measure_rec => l_measure_rec_p
3513 ,p_all_info =>FND_API.G_TRUE
3514 ,x_measure_rec => l_measure_rec
3515 ,x_return_status => x_return_status
3516 ,x_error_tbl => x_error_tbl
3517 );
3518
3519 IF (p_target_level_rec.dimension1_level_short_name IS NOT NULL
3520 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension1_level_short_name) = FND_API.G_TRUE) THEN
3521 OPEN c_dim_lvl(p_target_level_rec.dimension1_level_short_name);
3522 FETCH c_dim_lvl INTO x_target_level_rec.dimension1_level_id,x_target_level_rec.dimension1_level_name;
3523 CLOSE c_dim_lvl;
3524 END IF;
3525 IF (p_target_level_rec.dimension2_level_short_name IS NOT NULL
3526 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension2_level_short_name)= FND_API.G_TRUE) THEN
3527 OPEN c_dim_lvl(p_target_level_rec.dimension2_level_short_name);
3528 FETCH c_dim_lvl INTO x_target_level_rec.dimension2_level_id,x_target_level_rec.dimension2_level_name;
3529 CLOSE c_dim_lvl;
3530 END IF;
3531 IF (p_target_level_rec.dimension3_level_short_name IS NOT NULL
3532 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension3_level_short_name)= FND_API.G_TRUE) THEN
3533 OPEN c_dim_lvl(p_target_level_rec.dimension3_level_short_name);
3534 FETCH c_dim_lvl INTO x_target_level_rec.dimension3_level_id,x_target_level_rec.dimension3_level_name;
3535 CLOSE c_dim_lvl;
3536 END IF;
3537 IF (p_target_level_rec.dimension4_level_short_name IS NOT NULL
3538 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension4_level_short_name)= FND_API.G_TRUE) THEN
3539 OPEN c_dim_lvl(p_target_level_rec.dimension4_level_short_name);
3540 FETCH c_dim_lvl INTO x_target_level_rec.dimension4_level_id,x_target_level_rec.dimension4_level_name;
3541 CLOSE c_dim_lvl;
3542 END IF;
3543 IF (p_target_level_rec.dimension5_level_short_name IS NOT NULL
3544 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension5_level_short_name)= FND_API.G_TRUE) THEN
3545 OPEN c_dim_lvl(p_target_level_rec.dimension5_level_short_name);
3546 FETCH c_dim_lvl INTO x_target_level_rec.dimension5_level_id,x_target_level_rec.dimension5_level_name;
3547 CLOSE c_dim_lvl;
3548 END IF;
3549 IF (p_target_level_rec.dimension6_level_short_name IS NOT NULL
3550 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension6_level_short_name)= FND_API.G_TRUE) THEN
3551 OPEN c_dim_lvl(p_target_level_rec.dimension6_level_short_name);
3552 FETCH c_dim_lvl INTO x_target_level_rec.dimension6_level_id,x_target_level_rec.dimension6_level_name;
3553 CLOSE c_dim_lvl;
3554 END IF;
3555 IF (p_target_level_rec.dimension7_level_short_name IS NOT NULL
3556 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension7_level_short_name)= FND_API.G_TRUE) THEN
3557 OPEN c_dim_lvl(p_target_level_rec.dimension7_level_short_name);
3558 FETCH c_dim_lvl INTO x_target_level_rec.dimension7_level_id,x_target_level_rec.dimension7_level_name;
3559 CLOSE c_dim_lvl;
3560 END IF;
3561 x_target_level_rec.measure_name := l_measure_rec.measure_name;
3562 x_target_level_Rec.measure_id := l_measure_rec.measure_id;
3563
3564 --also return to UOM
3565 x_target_level_rec.Unit_Of_Measure := l_measure_rec.Unit_Of_Measure_Class;
3566
3567 --Get the dimension ids for all the dimension level ids. This will be later used to
3568 --sequence the dimension levels
3569 IF (x_target_level_rec.dimension1_level_id IS NOT NULL
3570 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension1_level_id)= FND_API.G_TRUE) THEN
3571 SetNULL(l_dim_level_rec,l_dim_level_rec);
3572 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension1_level_id;
3573 l_dim_level_rec_p := l_dim_level_rec;
3574 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3575 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3576 ,x_Dimension_Level_Rec => l_dim_level_rec
3577 ,x_return_status => x_return_status
3578 ,x_error_Tbl => x_error_tbl
3579 );
3580 l_dim1_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3581 END IF;
3582
3583 IF (x_target_level_rec.dimension2_level_id IS NOT NULL
3584 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension2_level_id)= FND_API.G_TRUE) THEN
3585 SetNULL(l_dim_level_rec,l_dim_level_rec);
3586 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension2_level_id;
3587 l_dim_level_rec_p := l_dim_level_rec;
3588 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3589 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3590 ,x_Dimension_Level_Rec => l_dim_level_rec
3591 ,x_return_status => x_return_status
3592 ,x_error_Tbl => x_error_tbl
3593 );
3594 l_dim2_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3595 END IF;
3596
3597 IF (x_target_level_rec.dimension3_level_id IS NOT NULL
3598 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension3_level_id)= FND_API.G_TRUE) THEN
3599 SetNULL(l_dim_level_rec,l_dim_level_rec);
3600 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension3_level_id;
3601 l_dim_level_rec_p := l_dim_level_rec;
3602 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3603 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3604 ,x_Dimension_Level_Rec => l_dim_level_rec
3605 ,x_return_status => x_return_status
3606 ,x_error_Tbl => x_error_tbl
3607 );
3608 l_dim3_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3609 END IF;
3610
3611 IF (x_target_level_rec.dimension4_level_id IS NOT NULL
3612 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension4_level_id)= FND_API.G_TRUE) THEN
3613 SetNULL(l_dim_level_rec,l_dim_level_rec);
3614 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension4_level_id;
3615 l_dim_level_rec_p := l_dim_level_rec;
3616 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3617 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3618 ,x_Dimension_Level_Rec => l_dim_level_rec
3619 ,x_return_status => x_return_status
3620 ,x_error_Tbl => x_error_tbl
3621 );
3622 l_dim4_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3623 END IF;
3624
3625 IF (x_target_level_rec.dimension5_level_id IS NOT NULL
3626 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension5_level_id)= FND_API.G_TRUE) THEN
3627 SetNULL(l_dim_level_rec,l_dim_level_rec);
3628 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension5_level_id;
3629 l_dim_level_rec_p := l_dim_level_rec;
3630 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3631 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3632 ,x_Dimension_Level_Rec => l_dim_level_rec
3633 ,x_return_status => x_return_status
3634 ,x_error_Tbl => x_error_tbl
3635 );
3636 l_dim5_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3637 END IF;
3638
3639 IF (x_target_level_rec.dimension6_level_id IS NOT NULL
3640 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension6_level_id)= FND_API.G_TRUE) THEN
3641 SetNULL(l_dim_level_rec,l_dim_level_rec);
3642 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension6_level_id;
3643 l_dim_level_rec_p := l_dim_level_rec;
3644 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3645 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3646 ,x_Dimension_Level_Rec => l_dim_level_rec
3647 ,x_return_status => x_return_status
3648 ,x_error_Tbl => x_error_tbl
3649 );
3650 l_dim6_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3651 END IF;
3652
3653 IF (x_target_level_rec.dimension7_level_id IS NOT NULL
3654 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension7_level_id)= FND_API.G_TRUE) THEN
3655 SetNULL(l_dim_level_rec,l_dim_level_rec);
3656 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension7_level_id;
3657 l_dim_level_rec_p := l_dim_level_rec;
3658 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3659 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3660 ,x_Dimension_Level_Rec => l_dim_level_rec
3661 ,x_return_status => x_return_status
3662 ,x_error_Tbl => x_error_tbl
3663 );
3664 l_dim7_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3665 END IF;
3666
3667 IF (l_measure_rec.dimension1_id = l_dim1_id) THEN
3668 l_dim1_level_id := x_target_level_rec.dimension1_level_id;
3669 l_dim1_level_value_id := p_target_rec.dim1_level_value_id;
3670 l_dim1_level_short_name := p_target_level_rec.dimension1_level_short_name;
3671 l_dim1_level_name := x_target_level_rec.dimension1_level_name;
3672 ELSIF (l_measure_rec.dimension2_id = l_dim1_id) THEN
3673 l_dim2_level_id := x_target_level_rec.dimension1_level_id;
3674 l_dim2_level_value_id := p_target_rec.dim1_level_value_id;
3675 l_dim2_level_short_name := p_target_level_rec.dimension1_level_short_name;
3676 l_dim2_level_name := x_target_level_rec.dimension1_level_name;
3677 ELSIF (l_measure_rec.dimension3_id = l_dim1_id) THEN
3678 l_dim3_level_id := x_target_level_rec.dimension1_level_id;
3679 l_dim3_level_value_id := p_target_rec.dim1_level_value_id;
3680 l_dim3_level_short_name := p_target_level_rec.dimension1_level_short_name;
3681 l_dim3_level_name := x_target_level_rec.dimension1_level_name;
3682 ELSIF (l_measure_rec.dimension4_id = l_dim1_id) THEN
3683 l_dim4_level_id := x_target_level_rec.dimension1_level_id;
3684 l_dim4_level_value_id := p_target_rec.dim1_level_value_id;
3685 l_dim4_level_short_name := p_target_level_rec.dimension1_level_short_name;
3686 l_dim4_level_name := x_target_level_rec.dimension1_level_name;
3687 ELSIF (l_measure_rec.dimension5_id = l_dim1_id) THEN
3688 l_dim5_level_id := x_target_level_rec.dimension1_level_id;
3689 l_dim5_level_value_id := p_target_rec.dim1_level_value_id;
3690 l_dim5_level_short_name := p_target_level_rec.dimension1_level_short_name;
3691 l_dim5_level_name := x_target_level_rec.dimension1_level_name;
3692 ELSIF (l_measure_rec.dimension6_id = l_dim1_id) THEN
3693 l_dim6_level_id := x_target_level_rec.dimension1_level_id;
3694 l_dim6_level_value_id := p_target_rec.dim1_level_value_id;
3695 l_dim6_level_short_name := p_target_level_rec.dimension1_level_short_name;
3696 l_dim6_level_name := x_target_level_rec.dimension1_level_name;
3697 ELSIF (l_measure_rec.dimension7_id = l_dim1_id) THEN
3698 l_dim7_level_id := x_target_level_rec.dimension1_level_id;
3699 l_dim7_level_value_id := p_target_rec.dim1_level_value_id;
3700 l_dim7_level_short_name := p_target_level_rec.dimension1_level_short_name;
3701 l_dim7_level_name := x_target_level_rec.dimension1_level_name;
3702 END IF;
3703 IF (l_measure_rec.dimension1_id = l_dim2_id) THEN
3704 l_dim1_level_id := x_target_level_rec.dimension2_level_id;
3705 l_dim1_level_value_id := p_target_rec.dim2_level_value_id;
3706 l_dim1_level_short_name := p_target_level_rec.dimension2_level_short_name;
3707 l_dim1_level_name := x_target_level_rec.dimension2_level_name;
3708 ELSIF (l_measure_rec.dimension2_id = l_dim2_id) THEN
3709 l_dim2_level_id := x_target_level_rec.dimension2_level_id;
3710 l_dim2_level_value_id := p_target_rec.dim2_level_value_id;
3711 l_dim2_level_short_name := p_target_level_rec.dimension2_level_short_name;
3712 l_dim2_level_name := x_target_level_rec.dimension2_level_name;
3713 ELSIF (l_measure_rec.dimension3_id = l_dim2_id) THEN
3714 l_dim3_level_id := x_target_level_rec.dimension2_level_id;
3715 l_dim3_level_value_id := p_target_rec.dim2_level_value_id;
3716 l_dim3_level_short_name := p_target_level_rec.dimension2_level_short_name;
3717 l_dim3_level_name := x_target_level_rec.dimension2_level_name;
3718 ELSIF (l_measure_rec.dimension4_id = l_dim2_id) THEN
3719 l_dim4_level_id := x_target_level_rec.dimension2_level_id;
3720 l_dim4_level_value_id := p_target_rec.dim2_level_value_id;
3721 l_dim4_level_short_name := p_target_level_rec.dimension2_level_short_name;
3722 l_dim4_level_name := x_target_level_rec.dimension2_level_name;
3723 ELSIF (l_measure_rec.dimension5_id = l_dim2_id) THEN
3724 l_dim5_level_id := x_target_level_rec.dimension2_level_id;
3725 l_dim5_level_value_id := p_target_rec.dim2_level_value_id;
3726 l_dim5_level_short_name := p_target_level_rec.dimension2_level_short_name;
3727 l_dim5_level_name := x_target_level_rec.dimension2_level_name;
3728 ELSIF (l_measure_rec.dimension6_id = l_dim2_id) THEN
3729 l_dim6_level_id := x_target_level_rec.dimension2_level_id;
3730 l_dim6_level_value_id := p_target_rec.dim2_level_value_id;
3731 l_dim6_level_short_name := p_target_level_rec.dimension2_level_short_name;
3732 l_dim6_level_name := x_target_level_rec.dimension2_level_name;
3733 ELSIF (l_measure_rec.dimension7_id = l_dim2_id) THEN
3734 l_dim7_level_id := x_target_level_rec.dimension2_level_id;
3735 l_dim7_level_value_id := p_target_rec.dim2_level_value_id;
3736 l_dim7_level_short_name := p_target_level_rec.dimension2_level_short_name;
3737 l_dim7_level_name := x_target_level_rec.dimension2_level_name;
3738 END IF;
3739 IF (l_measure_rec.dimension1_id = l_dim3_id) THEN
3740 l_dim1_level_id := x_target_level_rec.dimension3_level_id;
3741 l_dim1_level_value_id := p_target_rec.dim3_level_value_id;
3742 l_dim1_level_short_name := p_target_level_rec.dimension3_level_short_name;
3743 l_dim1_level_name := x_target_level_rec.dimension3_level_name;
3744 ELSIF (l_measure_rec.dimension2_id = l_dim3_id) THEN
3745 l_dim2_level_id := x_target_level_rec.dimension3_level_id;
3746 l_dim2_level_value_id := p_target_rec.dim3_level_value_id;
3747 l_dim2_level_short_name := p_target_level_rec.dimension3_level_short_name;
3748 l_dim2_level_name := x_target_level_rec.dimension3_level_name;
3749 ELSIF (l_measure_rec.dimension3_id = l_dim3_id) THEN
3750 l_dim3_level_id := x_target_level_rec.dimension3_level_id;
3751 l_dim3_level_value_id := p_target_rec.dim3_level_value_id;
3752 l_dim3_level_short_name := p_target_level_rec.dimension3_level_short_name;
3753 l_dim3_level_name := x_target_level_rec.dimension3_level_name;
3754 ELSIF (l_measure_rec.dimension4_id = l_dim3_id) THEN
3755 l_dim4_level_id := x_target_level_rec.dimension3_level_id;
3756 l_dim4_level_value_id := p_target_rec.dim3_level_value_id;
3757 l_dim4_level_short_name := p_target_level_rec.dimension3_level_short_name;
3758 l_dim4_level_name := x_target_level_rec.dimension3_level_name;
3759 ELSIF (l_measure_rec.dimension5_id = l_dim3_id) THEN
3760 l_dim5_level_id := x_target_level_rec.dimension3_level_id;
3761 l_dim5_level_value_id := p_target_rec.dim3_level_value_id;
3762 l_dim5_level_short_name := p_target_level_rec.dimension3_level_short_name;
3763 l_dim5_level_name := x_target_level_rec.dimension3_level_name;
3764 ELSIF (l_measure_rec.dimension6_id = l_dim3_id) THEN
3765 l_dim6_level_id := x_target_level_rec.dimension3_level_id;
3766 l_dim6_level_value_id := p_target_rec.dim3_level_value_id;
3767 l_dim6_level_short_name := p_target_level_rec.dimension3_level_short_name;
3768 l_dim6_level_name := x_target_level_rec.dimension3_level_name;
3769 ELSIF (l_measure_rec.dimension7_id = l_dim3_id) THEN
3770 l_dim7_level_id := x_target_level_rec.dimension3_level_id;
3771 l_dim7_level_value_id := p_target_rec.dim3_level_value_id;
3772 l_dim7_level_short_name := p_target_level_rec.dimension3_level_short_name;
3773 l_dim7_level_name := x_target_level_rec.dimension3_level_name;
3774 END IF;
3775 IF (l_measure_rec.dimension1_id = l_dim4_id) THEN
3776 l_dim1_level_id := x_target_level_rec.dimension4_level_id;
3777 l_dim1_level_value_id := p_target_rec.dim4_level_value_id;
3778 l_dim1_level_short_name := p_target_level_rec.dimension4_level_short_name;
3779 l_dim1_level_name := x_target_level_rec.dimension4_level_name;
3780 ELSIF (l_measure_rec.dimension2_id = l_dim4_id) THEN
3781 l_dim2_level_id := x_target_level_rec.dimension4_level_id;
3782 l_dim2_level_value_id := p_target_rec.dim4_level_value_id;
3783 l_dim2_level_short_name := p_target_level_rec.dimension4_level_short_name;
3784 l_dim2_level_name := x_target_level_rec.dimension4_level_name;
3785 ELSIF (l_measure_rec.dimension3_id = l_dim4_id) THEN
3786 l_dim3_level_id := x_target_level_rec.dimension4_level_id;
3787 l_dim3_level_value_id := p_target_rec.dim4_level_value_id;
3788 l_dim3_level_short_name := p_target_level_rec.dimension4_level_short_name;
3789 l_dim3_level_name := x_target_level_rec.dimension4_level_name;
3790 ELSIF (l_measure_rec.dimension4_id = l_dim4_id) THEN
3791 l_dim4_level_id := x_target_level_rec.dimension4_level_id;
3792 l_dim4_level_value_id := p_target_rec.dim4_level_value_id;
3793 l_dim4_level_short_name := p_target_level_rec.dimension4_level_short_name;
3794 l_dim4_level_name := x_target_level_rec.dimension4_level_name;
3795 ELSIF (l_measure_rec.dimension5_id = l_dim4_id) THEN
3796 l_dim5_level_id := x_target_level_rec.dimension4_level_id;
3797 l_dim5_level_value_id := p_target_rec.dim4_level_value_id;
3798 l_dim5_level_short_name := p_target_level_rec.dimension4_level_short_name;
3799 l_dim5_level_name := x_target_level_rec.dimension4_level_name;
3800 ELSIF (l_measure_rec.dimension6_id = l_dim4_id) THEN
3801 l_dim6_level_id := x_target_level_rec.dimension4_level_id;
3802 l_dim6_level_value_id := p_target_rec.dim4_level_value_id;
3803 l_dim6_level_short_name := p_target_level_rec.dimension4_level_short_name;
3804 l_dim6_level_name := x_target_level_rec.dimension4_level_name;
3805 ELSIF (l_measure_rec.dimension7_id = l_dim4_id) THEN
3806 l_dim7_level_id := x_target_level_rec.dimension4_level_id;
3807 l_dim7_level_value_id := p_target_rec.dim4_level_value_id;
3808 l_dim7_level_short_name := p_target_level_rec.dimension4_level_short_name;
3809 l_dim7_level_name := x_target_level_rec.dimension4_level_name;
3810 END IF;
3811 IF (l_measure_rec.dimension1_id = l_dim5_id) THEN
3812 l_dim1_level_id := x_target_level_rec.dimension5_level_id;
3813 l_dim1_level_value_id := p_target_rec.dim5_level_value_id;
3814 l_dim1_level_short_name := p_target_level_rec.dimension5_level_short_name;
3815 l_dim1_level_name := x_target_level_rec.dimension5_level_name;
3816 ELSIF (l_measure_rec.dimension2_id = l_dim5_id) THEN
3817 l_dim2_level_id := x_target_level_rec.dimension5_level_id;
3818 l_dim2_level_value_id := p_target_rec.dim5_level_value_id;
3819 l_dim2_level_short_name := p_target_level_rec.dimension5_level_short_name;
3820 l_dim2_level_name := x_target_level_rec.dimension5_level_name;
3821 ELSIF (l_measure_rec.dimension3_id = l_dim5_id) THEN
3822 l_dim3_level_id := x_target_level_rec.dimension5_level_id;
3823 l_dim3_level_value_id := p_target_rec.dim5_level_value_id;
3824 l_dim3_level_short_name := p_target_level_rec.dimension5_level_short_name;
3825 l_dim3_level_name := x_target_level_rec.dimension5_level_name;
3826 ELSIF (l_measure_rec.dimension4_id = l_dim5_id) THEN
3827 l_dim4_level_id := x_target_level_rec.dimension5_level_id;
3828 l_dim4_level_value_id := p_target_rec.dim5_level_value_id;
3829 l_dim4_level_short_name := p_target_level_rec.dimension5_level_short_name;
3830 l_dim4_level_name := x_target_level_rec.dimension5_level_name;
3831 ELSIF (l_measure_rec.dimension5_id = l_dim5_id) THEN
3832 l_dim5_level_id := x_target_level_rec.dimension5_level_id;
3833 l_dim5_level_value_id := p_target_rec.dim5_level_value_id;
3834 l_dim5_level_short_name := p_target_level_rec.dimension5_level_short_name;
3835 l_dim5_level_name := x_target_level_rec.dimension5_level_name;
3836 ELSIF (l_measure_rec.dimension6_id = l_dim5_id) THEN
3837 l_dim6_level_id := x_target_level_rec.dimension5_level_id;
3838 l_dim6_level_value_id := p_target_rec.dim5_level_value_id;
3839 l_dim6_level_short_name := p_target_level_rec.dimension5_level_short_name;
3840 l_dim6_level_name := x_target_level_rec.dimension5_level_name;
3841 ELSIF (l_measure_rec.dimension7_id = l_dim5_id) THEN
3842 l_dim7_level_id := x_target_level_rec.dimension5_level_id;
3843 l_dim7_level_value_id := p_target_rec.dim5_level_value_id;
3844 l_dim7_level_short_name := p_target_level_rec.dimension5_level_short_name;
3845 l_dim7_level_name := x_target_level_rec.dimension5_level_name;
3846 END IF;
3847 IF (l_measure_rec.dimension1_id = l_dim6_id) THEN
3848 l_dim1_level_id := x_target_level_rec.dimension6_level_id;
3849 l_dim1_level_value_id := p_target_rec.dim6_level_value_id;
3850 l_dim1_level_short_name := p_target_level_rec.dimension6_level_short_name;
3851 l_dim1_level_name := x_target_level_rec.dimension6_level_name;
3852 ELSIF (l_measure_rec.dimension2_id = l_dim6_id) THEN
3853 l_dim2_level_id := x_target_level_rec.dimension6_level_id;
3854 l_dim2_level_value_id := p_target_rec.dim6_level_value_id;
3855 l_dim2_level_short_name := p_target_level_rec.dimension6_level_short_name;
3856 l_dim2_level_name := x_target_level_rec.dimension6_level_name;
3857 ELSIF (l_measure_rec.dimension3_id = l_dim6_id) THEN
3858 l_dim3_level_id := x_target_level_rec.dimension6_level_id;
3859 l_dim3_level_value_id := p_target_rec.dim6_level_value_id;
3860 l_dim3_level_short_name := p_target_level_rec.dimension6_level_short_name;
3861 l_dim3_level_name := x_target_level_rec.dimension6_level_name;
3862 ELSIF (l_measure_rec.dimension4_id = l_dim6_id) THEN
3863 l_dim4_level_id := x_target_level_rec.dimension6_level_id;
3864 l_dim4_level_value_id := p_target_rec.dim6_level_value_id;
3865 l_dim4_level_short_name := p_target_level_rec.dimension6_level_short_name;
3866 l_dim4_level_name := x_target_level_rec.dimension6_level_name;
3867 ELSIF (l_measure_rec.dimension5_id = l_dim6_id) THEN
3868 l_dim5_level_id := x_target_level_rec.dimension6_level_id;
3869 l_dim5_level_value_id := p_target_rec.dim6_level_value_id;
3870 l_dim5_level_short_name := p_target_level_rec.dimension6_level_short_name;
3871 l_dim5_level_name := x_target_level_rec.dimension6_level_name;
3872 ELSIF (l_measure_rec.dimension6_id = l_dim6_id) THEN
3873 l_dim6_level_id := x_target_level_rec.dimension6_level_id;
3874 l_dim6_level_value_id := p_target_rec.dim6_level_value_id;
3875 l_dim6_level_short_name := p_target_level_rec.dimension6_level_short_name;
3876 l_dim6_level_name := x_target_level_rec.dimension6_level_name;
3877 ELSIF (l_measure_rec.dimension7_id = l_dim6_id) THEN
3878 l_dim7_level_id := x_target_level_rec.dimension6_level_id;
3879 l_dim7_level_value_id := p_target_rec.dim6_level_value_id;
3880 l_dim7_level_short_name := p_target_level_rec.dimension6_level_short_name;
3881 l_dim7_level_name := x_target_level_rec.dimension6_level_name;
3882 END IF;
3883 IF (l_measure_rec.dimension1_id = l_dim7_id) THEN
3884 l_dim1_level_id := x_target_level_rec.dimension7_level_id;
3885 l_dim1_level_value_id := p_target_rec.dim7_level_value_id;
3886 l_dim1_level_short_name := p_target_level_rec.dimension7_level_short_name;
3887 l_dim1_level_name := x_target_level_rec.dimension7_level_name;
3888 ELSIF (l_measure_rec.dimension2_id = l_dim7_id) THEN
3889 l_dim2_level_id := x_target_level_rec.dimension7_level_id;
3890 l_dim2_level_value_id := p_target_rec.dim7_level_value_id;
3891 l_dim2_level_short_name := p_target_level_rec.dimension7_level_short_name;
3892 l_dim2_level_name := x_target_level_rec.dimension7_level_name;
3893 ELSIF (l_measure_rec.dimension3_id = l_dim7_id) THEN
3894 l_dim3_level_id := x_target_level_rec.dimension7_level_id;
3895 l_dim3_level_value_id := p_target_rec.dim7_level_value_id;
3896 l_dim3_level_short_name := p_target_level_rec.dimension7_level_short_name;
3897 l_dim3_level_name := x_target_level_rec.dimension7_level_name;
3898 ELSIF (l_measure_rec.dimension4_id = l_dim7_id) THEN
3899 l_dim4_level_id := x_target_level_rec.dimension7_level_id;
3900 l_dim4_level_value_id := p_target_rec.dim7_level_value_id;
3901 l_dim4_level_short_name := p_target_level_rec.dimension7_level_short_name;
3902 l_dim4_level_name := x_target_level_rec.dimension7_level_name;
3903 ELSIF (l_measure_rec.dimension5_id = l_dim7_id) THEN
3904 l_dim5_level_id := x_target_level_rec.dimension7_level_id;
3905 l_dim5_level_value_id := p_target_rec.dim7_level_value_id;
3906 l_dim5_level_short_name := p_target_level_rec.dimension7_level_short_name;
3907 l_dim5_level_name := x_target_level_rec.dimension7_level_name;
3908 ELSIF (l_measure_rec.dimension6_id = l_dim7_id) THEN
3909 l_dim6_level_id := x_target_level_rec.dimension7_level_id;
3910 l_dim6_level_value_id := p_target_rec.dim7_level_value_id;
3911 l_dim6_level_short_name := p_target_level_rec.dimension7_level_short_name;
3912 l_dim6_level_name := x_target_level_rec.dimension7_level_name;
3913 ELSIF (l_measure_rec.dimension7_id = l_dim7_id) THEN
3914 l_dim7_level_id := x_target_level_rec.dimension7_level_id;
3915 l_dim7_level_value_id := p_target_rec.dim7_level_value_id;
3916 l_dim7_level_short_name := p_target_level_rec.dimension7_level_short_name;
3917 l_dim7_level_name := x_target_level_rec.dimension7_level_name;
3918 END IF;
3919
3920 x_Target_Level_Rec.Measure_ID := l_Measure_Rec.Measure_ID;
3921 x_Target_Level_Rec.Dimension1_Level_ID := NVL(l_DIM1_LEVEL_ID,FND_API.G_MISS_NUM);
3922 x_Target_Level_Rec.Dimension2_Level_ID := NVL(l_DIM2_LEVEL_ID,FND_API.G_MISS_NUM);
3923 x_Target_Level_Rec.Dimension3_Level_ID := NVL(l_DIM3_LEVEL_ID,FND_API.G_MISS_NUM);
3924 x_Target_Level_Rec.Dimension4_Level_ID := NVL(l_DIM4_LEVEL_ID,FND_API.G_MISS_NUM);
3925 x_Target_Level_Rec.Dimension5_Level_ID := NVL(l_DIM5_LEVEL_ID,FND_API.G_MISS_NUM);
3926 x_Target_Level_Rec.Dimension6_Level_ID := NVL(l_DIM6_LEVEL_ID,FND_API.G_MISS_NUM);
3927 x_Target_Level_Rec.Dimension7_Level_ID := NVL(l_DIM7_LEVEL_ID,FND_API.G_MISS_NUM);
3928 l_target_level_id := BIS_TARGET_LEVEL_PVT.Get_Level_Id_From_Dimlevels(x_target_level_rec);
3929 x_Target_Level_Rec.Target_Level_Id := l_target_level_id;
3930 x_Target_Level_Rec.Dimension1_Level_Short_Name := NVL(l_DIM1_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3931 x_Target_Level_Rec.Dimension2_Level_Short_Name := NVL(l_DIM2_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3932 x_Target_Level_Rec.Dimension3_Level_Short_Name := NVL(l_DIM3_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3933 x_Target_Level_Rec.Dimension4_Level_Short_Name := NVL(l_DIM4_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3934 x_Target_Level_Rec.Dimension5_Level_Short_Name := NVL(l_DIM5_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3935 x_Target_Level_Rec.Dimension6_Level_Short_Name := NVL(l_DIM6_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3936 x_Target_Level_Rec.Dimension7_Level_Short_Name := NVL(l_DIM7_LEVEL_SHORT_NAME,FND_API.G_MISS_CHAR);
3937 x_Target_Level_Rec.Dimension1_Level_Name := NVL(l_DIM1_LEVEL_NAME,FND_API.G_MISS_CHAR);
3938 x_Target_Level_Rec.Dimension2_Level_Name := NVL(l_DIM2_LEVEL_NAME,FND_API.G_MISS_CHAR);
3939 x_Target_Level_Rec.Dimension3_Level_Name := NVL(l_DIM3_LEVEL_NAME,FND_API.G_MISS_CHAR);
3940 x_Target_Level_Rec.Dimension4_Level_Name := NVL(l_DIM4_LEVEL_NAME,FND_API.G_MISS_CHAR);
3941 x_Target_Level_Rec.Dimension5_Level_Name := NVL(l_DIM5_LEVEL_NAME,FND_API.G_MISS_CHAR);
3942 x_Target_Level_Rec.Dimension6_Level_Name := NVL(l_DIM6_LEVEL_NAME,FND_API.G_MISS_CHAR);
3943 x_Target_Level_Rec.Dimension7_Level_Name := NVL(l_DIM7_LEVEL_NAME,FND_API.G_MISS_CHAR);
3944
3945
3946 --On to the Targets Stuff
3947 x_target_rec.target_level_id := l_target_level_id;
3948 x_target_rec.plan_id := p_target_rec.plan_id;
3949 x_target_rec.dim1_level_Value_id := l_dim1_level_value_id;
3950 x_target_rec.dim2_level_Value_id := l_dim2_level_value_id;
3951 x_target_rec.dim3_level_Value_id := l_dim3_level_value_id;
3952 x_target_rec.dim4_level_Value_id := l_dim4_level_value_id;
3953 x_target_rec.dim5_level_Value_id := l_dim5_level_value_id;
3954 x_target_rec.dim6_level_Value_id := l_dim6_level_value_id;
3955 x_target_rec.dim7_level_Value_id := l_dim7_level_value_id;
3956
3957 l_target_rec_p := x_target_rec;
3958 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
3959 p_Target_Rec => l_target_rec_p,
3960 p_all_info => FND_API.G_TRUE,
3961 x_Target_Rec => x_target_rec,
3962 x_return_status => x_return_status,
3963 x_error_Tbl => x_error_tbl);
3964
3965 --Dim Level Value Names
3966
3967 IF (x_target_level_rec.dimension1_level_id IS NOT NULL
3968 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension1_level_id)= FND_API.G_TRUE
3969 AND
3970 x_target_rec.dim1_level_value_id IS NOT NULL
3971 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim1_level_value_id)= FND_API.G_TRUE) THEN
3972
3973 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension1_level_id;
3974 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim1_level_value_id;
3975 l_dim_level_value_rec_p := l_dim_level_value_rec;
3976 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
3977 (p_api_version => 1.0
3978 ,p_dim_level_value_rec => l_dim_level_value_rec_p
3979 ,x_dim_level_value_rec => l_dim_level_value_rec
3980 ,x_return_status => x_return_status
3981 ,x_error_tbl => x_error_tbl
3982 );
3983 x_target_rec.dim1_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
3984 END IF;
3985
3986 IF (x_target_level_rec.dimension2_level_id IS NOT NULL
3987 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension2_level_id)= FND_API.G_TRUE
3988 AND
3989 x_target_rec.dim2_level_value_id IS NOT NULL
3990 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim2_level_value_id)= FND_API.G_TRUE) THEN
3991
3992 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension2_level_id;
3993 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim2_level_value_id;
3994 l_dim_level_value_rec_p := l_dim_level_value_rec;
3995 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
3996 (p_api_version => 1.0
3997 ,p_dim_level_value_rec => l_dim_level_value_rec_p
3998 ,x_dim_level_value_rec => l_dim_level_value_rec
3999 ,x_return_status => x_return_status
4000 ,x_error_tbl => x_error_tbl
4001 );
4002 x_target_rec.dim2_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4003 END IF;
4004
4005 IF (x_target_level_rec.dimension3_level_id IS NOT NULL
4006 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension3_level_id)= FND_API.G_TRUE
4007 AND
4008 x_target_rec.dim3_level_value_id IS NOT NULL
4009 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim3_level_value_id)= FND_API.G_TRUE) THEN
4010
4011 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension3_level_id;
4012 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim3_level_value_id;
4013 l_dim_level_value_rec_p := l_dim_level_value_rec;
4014 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
4015 (p_api_version => 1.0
4016 ,p_dim_level_value_rec => l_dim_level_value_rec_p
4017 ,x_dim_level_value_rec => l_dim_level_value_rec
4018 ,x_return_status => x_return_status
4019 ,x_error_tbl => x_error_tbl
4020 );
4021 x_target_rec.dim3_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4022 END IF;
4023
4024 IF (x_target_level_rec.dimension4_level_id IS NOT NULL
4025 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension4_level_id)= FND_API.G_TRUE
4026 AND
4027 x_target_rec.dim4_level_value_id IS NOT NULL
4028 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim4_level_value_id)= FND_API.G_TRUE) THEN
4029
4030 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension4_level_id;
4031 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim4_level_value_id;
4032 l_dim_level_value_rec_p := l_dim_level_value_rec;
4033 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
4034 (p_api_version => 1.0
4035 ,p_dim_level_value_rec => l_dim_level_value_rec_p
4036 ,x_dim_level_value_rec => l_dim_level_value_rec
4037 ,x_return_status => x_return_status
4038 ,x_error_tbl => x_error_tbl
4039 );
4040 x_target_rec.dim4_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4041 END IF;
4042
4043 IF (x_target_level_rec.dimension5_level_id IS NOT NULL
4044 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension5_level_id)= FND_API.G_TRUE
4045 AND
4046 x_target_rec.dim5_level_value_id IS NOT NULL
4047 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim5_level_value_id)= FND_API.G_TRUE) THEN
4048
4049 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension5_level_id;
4050 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim5_level_value_id;
4051 l_dim_level_value_rec_p := l_dim_level_value_rec;
4052 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
4053 (p_api_version => 1.0
4054 ,p_dim_level_value_rec => l_dim_level_value_rec_p
4055 ,x_dim_level_value_rec => l_dim_level_value_rec
4056 ,x_return_status => x_return_status
4057 ,x_error_tbl => x_error_tbl
4058 );
4059 x_target_rec.dim5_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4060 END IF;
4061
4062 IF (x_target_level_rec.dimension6_level_id IS NOT NULL
4063 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension6_level_id)= FND_API.G_TRUE
4064 AND
4065 x_target_rec.dim6_level_value_id IS NOT NULL
4066 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim6_level_value_id)= FND_API.G_TRUE) THEN
4067
4068 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension6_level_id;
4069 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim6_level_value_id;
4070 l_dim_level_value_rec_p := l_dim_level_value_rec;
4071 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
4072 (p_api_version => 1.0
4073 ,p_dim_level_value_rec => l_dim_level_value_rec_p
4074 ,x_dim_level_value_rec => l_dim_level_value_rec
4075 ,x_return_status => x_return_status
4076 ,x_error_tbl => x_error_tbl
4077 );
4078 x_target_rec.dim6_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4079 END IF;
4080
4081 IF (x_target_level_rec.dimension7_level_id IS NOT NULL
4082 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension7_level_id)= FND_API.G_TRUE
4083 AND
4084 x_target_rec.dim7_level_value_id IS NOT NULL
4085 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_rec.dim7_level_value_id)= FND_API.G_TRUE) THEN
4086
4087 l_dim_level_value_rec.Dimension_level_id := x_target_level_rec.dimension7_level_id;
4088 l_dim_level_value_rec.Dimension_level_value_id := x_target_rec.dim7_level_value_id;
4089 l_dim_level_value_rec_p := l_dim_level_value_rec;
4090 BIS_DIM_LEVEL_VALUE_PVT.DIMENSIONX_ID_TO_VALUE
4091 (p_api_version => 1.0
4092 ,p_dim_level_value_rec => l_dim_level_value_rec_p
4093 ,x_dim_level_value_rec => l_dim_level_value_rec
4094 ,x_return_status => x_return_status
4095 ,x_error_tbl => x_error_tbl
4096 );
4097 x_target_rec.dim7_level_value_name := l_dim_level_value_rec.Dimension_level_value_name;
4098 END IF;
4099
4100
4101 EXCEPTION
4102 when others then
4103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4104 --Added last two parameters
4105 l_error_tbl := x_error_tbl;
4106 BIS_UTILITIES_PVT.Add_Error_Message
4107 ( p_error_msg_id => SQLCODE
4108 , p_error_description => SQLERRM
4109 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
4110 , p_error_table => l_error_tbl
4111 , x_error_table => x_error_tbl
4112 );
4113 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4114
4115 END Retrieve_Target_From_ShNms;
4116
4117 --
4118 -- Package Initialization
4119 -- Initialize globle variable
4120 --
4121 BEGIN
4122 G_SET_OF_BOOK_ID := NULL;
4123
4124 END BIS_TARGET_PVT;