[Home] [Help]
PACKAGE BODY: APPS.BIS_TARGET_PUB
Source
1 PACKAGE BODY BIS_TARGET_PUB AS
2 /* $Header: BISPTARB.pls 115.29 2003/01/27 13:34:00 mahrao ship $ */
3 --
4 /*
5 REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
6 REM dbdrv: checkfile:~PROD:~PATH:~FILE
7 REM +=======================================================================+
8 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
9 REM | All rights reserved. |
10 REM +=======================================================================+
11 REM | FILENAME |
12 REM | BISPTARS.pls |
13 REM | |
14 REM | DESCRIPTION |
15 REM | Public API for creating and managing Targets for the
16 REM | Key Performance Framework.
17 REM | NOTES |
18 REM | |
19 REM | HISTORY |
20 REM | 02-DEC-98 irchen Creation
21 REM | 10-JAN-2003 rchandra for bug 2715432 , changed OUT parameter |
22 REM | x_Target_Level_Rec , x_Target_Rec to IN OUT |
23 REM | in API RETRIEVE_TARGET_FROM_SHNMS |
24 REM |
25 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
26 REM | parameters.
27 REM +=======================================================================+
28 */
29 --
30 --
31 --
32 -- Defines one target for a specific set of dimension values for
33 -- one target level
34 PROCEDURE Create_Target
35 ( p_api_version IN NUMBER
36 , p_commit IN VARCHAR2 := FND_API.G_FALSE
37 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
40 )
41 IS
42 --
43 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
44 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
45 --
46 BEGIN
47 -- do value - id conversions
48 BIS_TARGET_PVT.Value_ID_Conversion
49 ( p_api_version => p_api_version
50 , p_Target_Rec => p_Target_Rec
51 , x_Target_Rec => l_Target_Rec
52 , x_return_status => x_return_status
53 , x_error_Tbl => x_error_Tbl
54 );
55 --
56 -- call pvt create
57 BIS_TARGET_PVT.Create_Target
58 ( p_api_version => p_api_version
59 , p_commit => p_commit
60 , p_Target_Rec => l_Target_Rec
61 , x_return_status => x_return_status
62 , x_error_Tbl => x_error_Tbl
63 );
64 --
65 EXCEPTION
66 WHEN FND_API.G_EXC_ERROR THEN
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
69 htp.p('BIS_TARGET_PUB.Create_Target:G_EXC_ERROR'); htp.para;
70 END IF;
71 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
73 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
74 htp.p('BIS_TARGET_PUB.Create_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
75 END IF;
76 WHEN OTHERS THEN
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 l_error_tbl := x_error_Tbl;
79 BIS_UTILITIES_PVT.Add_Error_Message
80 ( p_error_table => l_error_tbl
81 , p_error_msg_id => SQLCODE
82 , p_error_description => SQLERRM
83 , x_error_table => x_error_Tbl
84 );
85 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
86 htp.p('BIS_TARGET_PUB.Create_Target:OTHERS'); htp.para;
87 END IF;
88 --
89 END Create_Target;
90 --
91 --
92 -- retrieve information for all targets of the given target level
93 -- if information about dimension values are not required, set all_info
94 -- to FALSE.
95 PROCEDURE Retrieve_Targets
96 ( p_api_version IN NUMBER
97 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
98 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
99 , x_Target_Tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_Type
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
102 )
103 IS
104 --
105 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
106 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
107 l_target_tbl BIS_TARGET_PUB.Target_Tbl_Type;
108 --
109 BEGIN
110 -- do value - id conversions
111 BIS_TARGET_LEVEL_PVT.Value_ID_Conversion
112 ( p_api_version => p_api_version
113 , p_Target_Level_Rec => p_Target_Level_Rec
114 , x_Target_Level_Rec => l_Target_Level_Rec
115 , x_return_status => x_return_status
116 , x_error_Tbl => x_error_Tbl
117 );
118 --
119 -- call pvt retrieve
120 BIS_TARGET_PVT.Retrieve_Targets
121 ( p_api_version => p_api_version
122 , p_Target_Level_Rec => p_Target_Level_Rec
123 , p_all_info => p_all_info
124 , x_Target_Tbl => x_Target_Tbl
125 , x_return_status => x_return_status
126 , x_error_Tbl => x_error_Tbl
127 );
128 -- For product teams still using the Org and Time values populate those fields
129 --added this check
130
131 IF (x_target_tbl.COUNT > 0) THEN
132 l_target_tbl := x_target_tbl;
133 FOR l_count IN 1..l_target_tbl.COUNT LOOP
134 BIS_UTILITIES_PVT.resequence_dim_level_values
135 (l_target_tbl(l_count)
136 ,'R'
137 ,x_target_tbl(l_count)
138 ,x_Error_tbl
139 );
140 END LOOP;
141 END IF;
142
143
144 --
145 EXCEPTION
146 WHEN FND_API.G_EXC_ERROR THEN
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
149 htp.p('BIS_TARGET_PUB.Retrieve_Targets:G_EXC_ERROR'); htp.para;
150 END IF;
151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
154 htp.p('BIS_TARGET_PUB.Retrieve_Targets:G_EXC_UNEXPECTED_ERROR'); htp.para;
155 END IF;
156 WHEN OTHERS THEN
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 l_error_tbl := x_error_Tbl;
159 BIS_UTILITIES_PVT.Add_Error_Message
160 ( p_error_table => l_error_tbl
161 , p_error_msg_id => SQLCODE
162 , p_error_description => SQLERRM
163 , x_error_table => x_error_Tbl
164 );
165 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
166 htp.p('BIS_TARGET_PUB.Retrieve_Targets:OTHERS'); htp.para;
167 END IF;
168 --
169 END Retrieve_Targets;
170 --
171 --
172 -- retrieve information for one target
173 -- If information about dimension values are not required, set all_info
174 -- to FALSE.
175 PROCEDURE Retrieve_Target
176 ( p_api_version IN NUMBER
177 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
178 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
179 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
180 , x_return_status OUT NOCOPY VARCHAR2
181 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
182 )
183 IS
184 --
185 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
186 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
187 l_Target_Rec_p BIS_TARGET_PUB.Target_Rec_Type;
188 --
189 BEGIN
190 -- do value - id conversions
191 BIS_TARGET_PVT.Value_ID_Conversion
192 ( p_api_version => p_api_version
193 , p_Target_Rec => p_Target_Rec
194 , x_Target_Rec => l_Target_Rec
195 , x_return_status => x_return_status
196 , x_error_Tbl => x_error_Tbl
197 );
198
199 --
200 --Resequence the dimensions. This is for backward compatibility for product teams
201 --still using Org and Time
202 l_Target_Rec_p := l_Target_Rec;
203 BIS_UTILITIES_PVT.resequence_dim_level_values
204 (l_Target_Rec_p
205 ,'N'
206 ,l_target_rec
207 ,x_Error_tbl
208 );
209 -- call pvt retrieve
210 BIS_TARGET_PVT.Retrieve_Target
211 ( p_api_version => p_api_version
212 , p_Target_Rec => l_Target_Rec
213 , x_Target_Rec => x_Target_Rec
214 , x_return_status => x_return_status
215 , x_error_Tbl => x_error_Tbl
216 );
217 --
218 --Put the values back in Org and Time for the product teams still using them
219 --added this check
220 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
221 l_Target_Rec_p := x_Target_Rec;
222 BIS_UTILITIES_PVT.resequence_dim_level_values
223 (l_Target_Rec_p
224 ,'R'
225 ,x_target_rec
226 ,x_Error_tbl
227 );
228 end if;
229 EXCEPTION
230 WHEN FND_API.G_EXC_ERROR THEN
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
233 htp.p('BIS_TARGET_PUB.Retrieve_Target:G_EXC_ERROR'); htp.para;
234 END IF;
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
238 htp.p('BIS_TARGET_PUB.Retrieve_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
239 END IF;
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 l_error_tbl := x_error_Tbl;
243 BIS_UTILITIES_PVT.Add_Error_Message
244 ( p_error_table => l_error_tbl
245 , p_error_msg_id => SQLCODE
246 , p_error_description => SQLERRM
247 , x_error_table => x_error_Tbl
248 );
249 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
250 htp.p('BIS_TARGET_PUB.Retrieve_Target:OTHERS'); htp.para;
251 END IF;
252 --
253 END Retrieve_Target;
254 --
255 -- Modifies one target for a specific set of dimension values for
256 -- one target level
257 PROCEDURE Update_Target
258 ( p_api_version IN NUMBER
259 , p_commit IN VARCHAR2 := FND_API.G_FALSE
260 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
261 , x_return_status OUT NOCOPY VARCHAR2
262 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
263 )
264 IS
265 --
266 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
267 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
268 --
269 BEGIN
270 -- do value - id conversions
271 BIS_TARGET_PVT.Value_ID_Conversion
272 ( p_api_version => p_api_version
273 , p_Target_Rec => p_Target_Rec
274 , x_Target_Rec => l_Target_Rec
275 , x_return_status => x_return_status
276 , x_error_Tbl => x_error_Tbl
277 );
278 --
279 -- call pvt update
280 --added the p_commit
281 BIS_TARGET_PVT.Update_Target
282 ( p_api_version => p_api_version
283 , p_commit => p_commit
284 , p_Target_Rec => l_Target_Rec
285 , x_return_status => x_return_status
286 , x_error_Tbl => x_error_Tbl
287 );
288 --
289 EXCEPTION
290 WHEN FND_API.G_EXC_ERROR THEN
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
293 htp.p('BIS_TARGET_PUB.Update_Target:G_EXC_ERROR'); htp.para;
294 END IF;
295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
298 htp.p('BIS_TARGET_PUB.Update_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
299 END IF;
300 WHEN OTHERS THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 l_error_tbl := x_error_Tbl;
303 BIS_UTILITIES_PVT.Add_Error_Message
304 ( p_error_table => l_error_tbl
305 , p_error_msg_id => SQLCODE
306 , p_error_description => SQLERRM
307 , x_error_table => x_error_Tbl
308 );
309 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
310 htp.p('BIS_TARGET_PUB.Update_Target:OTHERS'); htp.para;
311 END IF;
312 --
313 END Update_Target;
314 --
315 -- Deletes one target for a specific set of dimension values for
316 -- one target level
317 PROCEDURE Delete_Target
318 ( p_api_version IN NUMBER
319 , p_commit IN VARCHAR2 := FND_API.G_FALSE
320 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
321 , x_return_status OUT NOCOPY VARCHAR2
322 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
323 )
324 IS
325 --
326 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
327 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
328 --
329 BEGIN
330 -- do value - id conversions
331 BIS_TARGET_PVT.Value_ID_Conversion
332 ( p_api_version => p_api_version
333 , p_Target_Rec => p_Target_Rec
334 , x_Target_Rec => l_Target_Rec
335 , x_return_status => x_return_status
336 , x_error_Tbl => x_error_Tbl
337 );
338 --
339 -- call pvt delete
340 BIS_TARGET_PVT.Delete_Target
341 ( p_api_version => p_api_version
342 , p_commit => p_commit
343 , p_Target_Rec => l_Target_Rec
344 , x_return_status => x_return_status
345 , x_error_Tbl => x_error_Tbl
346 );
347 --
348 EXCEPTION
349 WHEN FND_API.G_EXC_ERROR THEN
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
352 htp.p('BIS_TARGET_PUB.Delete_Target:G_EXC_ERROR'); htp.para;
353 END IF;
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
357 htp.p('BIS_TARGET_PUB.Delete_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
358 END IF;
359 WHEN OTHERS THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 l_error_tbl := x_error_Tbl;
362 BIS_UTILITIES_PVT.Add_Error_Message
363 ( p_error_table => l_error_tbl
364 , p_error_msg_id => SQLCODE
365 , p_error_description => SQLERRM
366 , x_error_table => x_error_Tbl
367 );
368 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
369 htp.p('BIS_TARGET_PUB.Delete_Target:OTHERS'); htp.para;
370 END IF;
371 --
372 END Delete_Target;
373 --
374 -- Validates target record
375 PROCEDURE Validate_Target
376 ( p_api_version IN NUMBER
377 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
378 , x_return_status OUT NOCOPY VARCHAR2
379 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
380 )
381 IS
382 --
383 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
384 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
385 --
386 BEGIN
387 -- dbms_output.put_line('> Validate_Target');
388 -- do value - id conversions
389 BIS_TARGET_PVT.Value_ID_Conversion
390 ( p_api_version => p_api_version
391 , p_Target_Rec => p_Target_Rec
392 , x_Target_Rec => l_Target_Rec
393 , x_return_status => x_return_status
394 , x_error_Tbl => x_error_Tbl
395 );
396 --
397 -- call pvt validate
398 BIS_TARGET_PVT.Validate_Target
399 ( p_api_version => p_api_version
400 , p_Target_Rec => l_Target_Rec
401 , x_return_status => x_return_status
402 , x_error_Tbl => x_error_Tbl
403 );
404 --
405 EXCEPTION
406 WHEN FND_API.G_EXC_ERROR THEN
407 x_return_status:= FND_API.G_RET_STS_ERROR;
408 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
409 htp.p('BIS_TARGET_PUB.Validate_Target:G_EXC_ERROR'); htp.para;
410 END IF;
411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
413 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
414 htp.p('BIS_TARGET_PUB.Validate_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
415 END IF;
416 WHEN OTHERS THEN
417 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
418 l_error_tbl := x_error_Tbl;
419 BIS_UTILITIES_PVT.Add_Error_Message
420 ( p_error_table => l_error_tbl
421 , p_error_msg_id => SQLCODE
422 , p_error_description => SQLERRM
423 , x_error_table => x_error_Tbl
424 );
425 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
426 htp.p('BIS_TARGET_PUB.Validate_Target:OTHERS'); htp.para;
427 END IF;
428 END Validate_Target;
429 --
430
431 -- New Procedure to return TargetLevel and Target given the DimensionLevel ShortNames in any sequence
432 -- and the Measure Short Name
433 PROCEDURE Retrieve_Target_From_ShNms
434 ( p_api_version IN NUMBER
435 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
436 , p_Target_Rec IN BIS_TARGET_PUB.TARGET_REC_TYPE
437 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
438 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.TARGET_REC_TYPE
439 , x_return_status OUT NOCOPY VARCHAR2
440 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
441 )
442 IS
443 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
444 BEGIN
445 BIS_Target_PVT.Retrieve_Target_From_ShNms
446 (
447 p_api_version => p_api_version
448 , p_Target_Level_Rec => p_Target_Level_Rec
449 ,p_Target_Rec => p_Target_Rec
450 , x_Target_Level_Rec => x_Target_Level_Rec
451 , x_Target_Rec => x_Target_Rec
452 , x_return_status => x_return_status
453 , x_error_Tbl => x_error_Tbl
454 );
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
460 htp.p('BIS_TARGET_PUB.Retrieve_Target:G_EXC_ERROR'); htp.para;
461 END IF;
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
465 htp.p('BIS_TARGET_PUB.Retrieve_Target:G_EXC_UNEXPECTED_ERROR'); htp.para;
466 END IF;
467 WHEN OTHERS THEN
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 l_error_tbl := x_error_Tbl;
470 BIS_UTILITIES_PVT.Add_Error_Message
471 ( p_error_table => l_error_tbl
472 , p_error_msg_id => SQLCODE
473 , p_error_description => SQLERRM
474 , x_error_table => x_error_Tbl
475 );
476 END Retrieve_Target_From_ShNms;
477 --
478 END BIS_TARGET_PUB;