[Home] [Help]
PACKAGE BODY: APPS.BIS_WEIGHTED_MEASURE_PVT
Source
1 PACKAGE BODY BIS_WEIGHTED_MEASURE_PVT AS
2 /* $Header: BISVWMEB.pls 120.1 2005/09/16 17:02:02 jxyu noship $ */
3 /*======================================================================================+
4 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +======================================================================================+
7 | FILENAME |
8 | BISVWMB.pls |
9 | |
10 | Creation Date: |
11 | April 11, 2005 |
12 | Creator: |
13 | William Cano |
14 | |
15 | Description: |
16 | Public version. |
17 | This package Handle Weighted Measures |
18 | |
19 | |
20 | History: |
21 | 04/11/05 wcano Created. |
22 | 09/15/05 jxyu Added Update_WM_Last_Update_Info API for bug#4427932. |
23 | |
24 +======================================================================================*/
25
26 -- Abbreviation Used"
27 -- WM -> Weighted Measure
28 -- SN -> Short Name
29
30 ------- APIs for tables BIS_WEIGHTED_MEASURE_DEPENDS
31 /*******************************************************************
32 *******************************************************************/
33 PROCEDURE Create_WM_Dependency(
34 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
35 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
36 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ) IS
41 BEGIN
42 SAVEPOINT CreateWMDependencyPVT;
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44
45 INSERT INTO BIS_WEIGHTED_MEASURE_DEPENDS(
46 WEIGHTED_MEASURE_ID
47 ,DEPENDENT_MEASURE_ID
48 ,CREATION_DATE
49 ,CREATED_BY
50 ,LAST_UPDATE_DATE
51 ,LAST_UPDATED_BY
52 ,LAST_UPDATE_LOGIN
53 ) VALUES (
54 p_Bis_WM_Rec.weighted_measure_id
55 ,p_Bis_WM_Rec.dependent_measure_id
56 ,p_Bis_WM_Rec.Creation_Date
57 ,p_Bis_WM_Rec.Created_By
58 ,p_Bis_WM_Rec.Last_Update_Date
59 ,p_Bis_WM_Rec.Last_Updated_By
60 ,p_Bis_WM_Rec.Last_Update_Login
61 );
62 IF p_commit = FND_API.G_TRUE THEN
63 COMMIT;
64 END IF;
65 x_Bis_WM_Rec := p_Bis_WM_Rec;
66
67 EXCEPTION
68 WHEN FND_API.G_EXC_ERROR THEN
69 ROLLBACK TO CreateWMDependencyPVT;
70 IF (x_msg_data IS NULL) THEN
71 FND_MSG_PUB.Count_And_Get
72 ( p_encoded => FND_API.G_FALSE
73 , p_count => x_msg_count
74 , p_data => x_msg_data
75 );
76 END IF;
77 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
78 x_return_status := FND_API.G_RET_STS_ERROR;
79 raise;
80 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
81 ROLLBACK TO CreateWMDependencyPVT;
82 IF (x_msg_data IS NULL) THEN
83 FND_MSG_PUB.Count_And_Get
84 ( p_encoded => FND_API.G_FALSE
85 , p_count => x_msg_count
86 , p_data => x_msg_data
87 );
88 END IF;
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
90 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
91 raise;
92 WHEN OTHERS THEN
93 ROLLBACK TO CreateWMDependencyPVT;
94 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
95 IF (x_msg_data IS NOT NULL) THEN
96 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Create_WM_Dependency ';
97 ELSE
98 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Create_WM_Dependency ';
99 END IF;
100 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
101 raise;
102 END Create_WM_Dependency;
103
104
105 /*******************************************************************
106 *******************************************************************/
107 PROCEDURE Retrieve_WM_Dependency(
108 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
109 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
110 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
111 ,x_return_status OUT NOCOPY VARCHAR2
112 ,x_msg_count OUT NOCOPY NUMBER
113 ,x_msg_data OUT NOCOPY VARCHAR2
114 ) IS
115 BEGIN
116 SAVEPOINT Retrieve_WM_Dependency_PVT;
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118
119 SELECT
120 WEIGHTED_MEASURE_ID
121 ,DEPENDENT_MEASURE_ID
122 ,CREATION_DATE
123 ,CREATED_BY
124 ,LAST_UPDATE_DATE
125 ,LAST_UPDATED_BY
126 ,LAST_UPDATE_LOGIN
127 INTO
128 x_Bis_WM_Rec.weighted_measure_id
129 ,x_Bis_WM_Rec.dependent_measure_id
130 ,x_Bis_WM_Rec.Creation_Date
131 ,x_Bis_WM_Rec.Created_By
132 ,x_Bis_WM_Rec.Last_Update_Date
133 ,x_Bis_WM_Rec.Last_Updated_By
134 ,x_Bis_WM_Rec.Last_Update_Login
135 FROM BIS_WEIGHTED_MEASURE_DEPENDS
136 WHERE WEIGHTED_MEASURE_ID = p_Bis_WM_Rec.weighted_measure_id
137 AND DEPENDENT_MEASURE_ID = p_Bis_WM_Rec.dependent_measure_id;
138
139
140 EXCEPTION
141 WHEN FND_API.G_EXC_ERROR THEN
142 ROLLBACK TO Retrieve_WM_Dependency_PVT;
143 IF (x_msg_data IS NULL) THEN
144 FND_MSG_PUB.Count_And_Get
145 ( p_encoded => FND_API.G_FALSE
146 , p_count => x_msg_count
147 , p_data => x_msg_data
148 );
149 END IF;
150 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 raise;
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 ROLLBACK TO Retrieve_WM_Dependency_pub;
155 IF (x_msg_data IS NULL) THEN
156 FND_MSG_PUB.Count_And_Get
157 ( p_encoded => FND_API.G_FALSE
158 , p_count => x_msg_count
159 , p_data => x_msg_data
160 );
161 END IF;
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 raise;
164 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
165 WHEN OTHERS THEN
166 ROLLBACK TO Retrieve_WM_Dependency_PVT;
167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
168 IF (x_msg_data IS NOT NULL) THEN
169 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Dependency ';
170 ELSE
171 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Dependency ';
172 END IF;
173 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
174 raise;
175
176 END Retrieve_WM_Dependency;
177
178 /*******************************************************************
179 *******************************************************************/
180 PROCEDURE Update_WM_Dependency(
181 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
182 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
183 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER
186 ,x_msg_data OUT NOCOPY VARCHAR2
187 ) IS
188 BEGIN
189 SAVEPOINT bis_Update_WM_Dependency_PVT;
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 UPDATE BIS_WEIGHTED_MEASURE_DEPENDS SET
193 LAST_UPDATE_DATE = p_Bis_WM_Rec.Last_Update_Date
194 ,LAST_UPDATED_BY = p_Bis_WM_Rec.Last_Updated_By
195 ,LAST_UPDATE_LOGIN = p_Bis_WM_Rec.Last_Update_Login
196 WHERE WEIGHTED_MEASURE_ID = p_Bis_WM_Rec.weighted_measure_id
197 AND DEPENDENT_MEASURE_ID = p_Bis_WM_Rec.dependent_measure_id;
198
199 x_Bis_WM_Rec := p_Bis_WM_Rec;
200 IF p_commit = FND_API.G_TRUE THEN
201 COMMIT;
202 END IF;
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 ROLLBACK TO bis_Update_WM_Dependency_PVT;
207 IF (x_msg_data IS NULL) THEN
208 FND_MSG_PUB.Count_And_Get
209 ( p_encoded => FND_API.G_FALSE
210 , p_count => x_msg_count
211 , p_data => x_msg_data
212 );
213 END IF;
214 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 raise;
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 ROLLBACK TO bis_Update_WM_Dependency_PVT;
219 IF (x_msg_data IS NULL) THEN
220 FND_MSG_PUB.Count_And_Get
221 ( p_encoded => FND_API.G_FALSE
222 , p_count => x_msg_count
223 , p_data => x_msg_data
224 );
225 END IF;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
228 raise;
229 WHEN OTHERS THEN
230 ROLLBACK TO bis_Update_WM_Dependency_PVT;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 IF (x_msg_data IS NOT NULL) THEN
233 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Dependency ';
234 ELSE
235 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Dependency ';
236 END IF;
237 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
238 raise;
239 END Update_WM_Dependency;
240
241 /*******************************************************************
242 *******************************************************************/
243 PROCEDURE Delete_WM_Dependency(
244 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
245 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
246 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
247 ,x_return_status OUT NOCOPY VARCHAR2
248 ,x_msg_count OUT NOCOPY NUMBER
249 ,x_msg_data OUT NOCOPY VARCHAR2
250 ) IS
251 BEGIN
252 SAVEPOINT Delete_WM_Dependency_PVT;
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 DELETE BIS_WEIGHTED_MEASURE_DEPENDS
256 WHERE WEIGHTED_MEASURE_ID = p_Bis_WM_Rec.weighted_measure_id
257 AND DEPENDENT_MEASURE_ID = p_Bis_WM_Rec.dependent_measure_id;
258
259
260 IF p_commit = FND_API.G_TRUE THEN
261 COMMIT;
262 END IF;
263 x_Bis_WM_Rec := p_Bis_WM_Rec;
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 ROLLBACK TO Delete_WM_Dependency_PVT;
268 IF (x_msg_data IS NULL) THEN
269 FND_MSG_PUB.Count_And_Get
270 ( p_encoded => FND_API.G_FALSE
271 , p_count => x_msg_count
272 , p_data => x_msg_data
273 );
274 END IF;
275 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 raise;
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 ROLLBACK TO Delete_WM_Dependency_PVT;
280 IF (x_msg_data IS NULL) THEN
281 FND_MSG_PUB.Count_And_Get
282 ( p_encoded => FND_API.G_FALSE
283 , p_count => x_msg_count
284 , p_data => x_msg_data
285 );
286 END IF;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
289 raise;
290 WHEN OTHERS THEN
291 ROLLBACK TO Delete_WM_Dependency_PVT;
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 IF (x_msg_data IS NOT NULL) THEN
294 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Dependency ';
295 ELSE
296 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Dependency ';
297 END IF;
298 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
299 raise;
300 END Delete_WM_Dependency;
301
302 ------- APIs for table BIS_WEIGHTED_MEASURE_DEFNS
303
304 /*******************************************************************
305 *******************************************************************/
306 PROCEDURE Create_WM_Definition(
307 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
308 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
309 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
310 ,x_return_status OUT NOCOPY VARCHAR2
311 ,x_msg_count OUT NOCOPY NUMBER
312 ,x_msg_data OUT NOCOPY VARCHAR2
313 ) IS
314 l_weighted_definition_id NUMBER;
315 BEGIN
316 SAVEPOINT CreateWMDefinitionPVT;
317 x_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 INSERT INTO BIS_WEIGHTED_MEASURE_DEFNS(
320 WEIGHTED_DEFINITION_ID
321 ,WEIGHTED_MEASURE_ID
322 ,VIEWBY_DIMENSION_SHORT_NAME
323 ,VIEWBY_DIM_LEVEL_SHORT_NAME
324 ,FILTER_DIMENSION_SHORT_NAME
325 ,FILTER_DIM_LEVEL_SHORT_NAME
326 ,TIME_DIMENSION_SHORT_NAME
327 ,TIME_DIM_LEVEL_SHORT_NAME
328 ,CREATION_DATE
329 ,CREATED_BY
330 ,LAST_UPDATE_DATE
331 ,LAST_UPDATED_BY
332 ,LAST_UPDATE_LOGIN
333 ) VALUES (
334 p_Bis_WM_Rec.weighted_definition_id
335 ,p_Bis_WM_Rec.weighted_measure_id
336 ,p_Bis_WM_Rec.VIEWBY_dimension_SN
337 ,p_Bis_WM_Rec.VIEWBY_dim_level_SN
338 ,p_Bis_WM_Rec.FILTER_dimension_SN
339 ,p_Bis_WM_Rec.FILTER_dim_level_SN
340 ,p_Bis_WM_Rec.time_dimension_short_name
341 ,p_Bis_WM_Rec.time_dim_level_short_name
342 ,p_Bis_WM_Rec.Creation_Date
343 ,p_Bis_WM_Rec.Created_By
344 ,p_Bis_WM_Rec.Last_Update_Date
345 ,p_Bis_WM_Rec.Last_Updated_By
346 ,p_Bis_WM_Rec.Last_Update_Login
347 );
348
349 x_Bis_WM_Rec := p_Bis_WM_Rec;
353
350 IF p_commit = FND_API.G_TRUE THEN
351 COMMIT;
352 END IF;
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 ROLLBACK TO CreateWMDefinitionPVT;
357 IF (x_msg_data IS NULL) THEN
358 FND_MSG_PUB.Count_And_Get
359 ( p_encoded => FND_API.G_FALSE
360 , p_count => x_msg_count
361 , p_data => x_msg_data
362 );
363 END IF;
364 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 raise;
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 ROLLBACK TO CreateWMDefinitionPVT;
369 IF (x_msg_data IS NULL) THEN
370 FND_MSG_PUB.Count_And_Get
371 ( p_encoded => FND_API.G_FALSE
372 , p_count => x_msg_count
373 , p_data => x_msg_data
374 );
375 END IF;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
378 raise;
379 WHEN OTHERS THEN
380 ROLLBACK TO CreateWMDefinitionPVT;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 IF (x_msg_data IS NOT NULL) THEN
383 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Create_WM_Definition ';
384 ELSE
385 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Create_WM_Definition ';
386 END IF;
387 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
388 raise;
389 END Create_WM_Definition;
390
391 /*******************************************************************
392 *******************************************************************/
393 PROCEDURE Retrieve_WM_Definition(
394 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
395 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
396 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
397 ,x_return_status OUT NOCOPY VARCHAR2
398 ,x_msg_count OUT NOCOPY NUMBER
399 ,x_msg_data OUT NOCOPY VARCHAR2
400 ) IS
401 BEGIN
402 SAVEPOINT Retrieve_WM_Definition_PVT;
403 x_return_status := FND_API.G_RET_STS_SUCCESS;
404
405 SELECT
406 WEIGHTED_DEFINITION_ID
407 ,WEIGHTED_MEASURE_ID
408 ,VIEWBY_DIMENSION_SHORT_NAME
409 ,VIEWBY_DIM_LEVEL_SHORT_NAME
410 ,FILTER_DIMENSION_SHORT_NAME
411 ,FILTER_DIM_LEVEL_SHORT_NAME
412 ,TIME_DIMENSION_SHORT_NAME
413 ,TIME_DIM_LEVEL_SHORT_NAME
414 ,CREATION_DATE
415 ,CREATED_BY
416 ,LAST_UPDATE_DATE
417 ,LAST_UPDATED_BY
418 ,LAST_UPDATE_LOGIN
419 INTO
420 x_Bis_WM_Rec.weighted_definition_id
421 ,x_Bis_WM_Rec.weighted_measure_id
422 ,x_Bis_WM_Rec.VIEWBY_dimension_SN
423 ,x_Bis_WM_Rec.VIEWBY_dim_level_SN
424 ,x_Bis_WM_Rec.FILTER_dimension_SN
425 ,x_Bis_WM_Rec.FILTER_dim_level_SN
426 ,x_Bis_WM_Rec.time_dimension_short_name
427 ,x_Bis_WM_Rec.time_dim_level_short_name
428 ,x_Bis_WM_Rec.Creation_Date
429 ,x_Bis_WM_Rec.Created_By
430 ,x_Bis_WM_Rec.Last_Update_Date
431 ,x_Bis_WM_Rec.Last_Updated_By
432 ,x_Bis_WM_Rec.Last_Update_Login
433 FROM BIS_WEIGHTED_MEASURE_DEFNS
434 WHERE WEIGHTED_DEFINITION_ID = p_Bis_WM_Rec.weighted_definition_id;
435
436 EXCEPTION
437 WHEN FND_API.G_EXC_ERROR THEN
438 ROLLBACK TO Retrieve_WM_Definition_PVT;
439 IF (x_msg_data IS NULL) THEN
440 FND_MSG_PUB.Count_And_Get
441 ( p_encoded => FND_API.G_FALSE
442 , p_count => x_msg_count
443 , p_data => x_msg_data
444 );
445 END IF;
446 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 raise;
449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
450 ROLLBACK TO Retrieve_WM_Definition_PVT;
451 IF (x_msg_data IS NULL) THEN
452 FND_MSG_PUB.Count_And_Get
453 ( p_encoded => FND_API.G_FALSE
454 , p_count => x_msg_count
455 , p_data => x_msg_data
456 );
457 END IF;
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 raise;
460 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
461 WHEN NO_DATA_FOUND THEN
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 IF (x_msg_data IS NOT NULL) THEN
464 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Definition ';
465 ELSE
466 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Definition ';
467 END IF;
468 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
469 RAISE;
470 WHEN OTHERS THEN
471 ROLLBACK TO Retrieve_WM_Definition_PVT;
472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
473 IF (x_msg_data IS NOT NULL) THEN
474 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Definition ';
475 ELSE
476 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Definition ';
477 END IF;
478 raise;
479 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
480 END Retrieve_WM_Definition;
481
482 /*******************************************************************
483 *******************************************************************/
484 PROCEDURE Update_WM_Definition(
485 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
489 ,x_msg_count OUT NOCOPY NUMBER
486 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
487 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
488 ,x_return_status OUT NOCOPY VARCHAR2
490 ,x_msg_data OUT NOCOPY VARCHAR2
491 ) IS
492 BEGIN
493 SAVEPOINT bis_Update_WM_Definition_PVT;
494 x_return_status := FND_API.G_RET_STS_SUCCESS;
495
496 UPDATE BIS_WEIGHTED_MEASURE_DEFNS SET
497 WEIGHTED_MEASURE_ID = p_Bis_WM_Rec.weighted_measure_id
498 ,VIEWBY_DIMENSION_SHORT_NAME = p_Bis_WM_Rec.VIEWBY_dimension_SN
499 ,VIEWBY_DIM_LEVEL_SHORT_NAME = p_Bis_WM_Rec.VIEWBY_dim_level_SN
500 ,FILTER_DIMENSION_SHORT_NAME = p_Bis_WM_Rec.FILTER_dimension_SN
501 ,FILTER_DIM_LEVEL_SHORT_NAME = p_Bis_WM_Rec.FILTER_dim_level_SN
502 ,TIME_DIMENSION_SHORT_NAME = p_Bis_WM_Rec.time_dimension_short_name
503 ,TIME_DIM_LEVEL_SHORT_NAME = p_Bis_WM_Rec.time_dim_level_short_name
504 ,LAST_UPDATE_DATE = p_Bis_WM_Rec.Last_Update_Date
505 ,LAST_UPDATED_BY = p_Bis_WM_Rec.Last_Updated_By
506 ,LAST_UPDATE_LOGIN = p_Bis_WM_Rec.Last_Update_Login
507 WHERE
508 WEIGHTED_DEFINITION_ID = p_Bis_WM_Rec.weighted_definition_id;
509
510 x_Bis_WM_Rec := p_Bis_WM_Rec;
511 IF p_commit = FND_API.G_TRUE THEN
512 COMMIT;
513 END IF;
514
515 EXCEPTION
516 WHEN FND_API.G_EXC_ERROR THEN
517 ROLLBACK TO bis_Update_WM_Definition_PVT;
518 IF (x_msg_data IS NULL) THEN
519 FND_MSG_PUB.Count_And_Get
520 ( p_encoded => FND_API.G_FALSE
521 , p_count => x_msg_count
522 , p_data => x_msg_data
523 );
524 END IF;
525 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 raise;
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 ROLLBACK TO bis_Update_WM_Definition_PVT;
530 IF (x_msg_data IS NULL) THEN
531 FND_MSG_PUB.Count_And_Get
532 ( p_encoded => FND_API.G_FALSE
533 , p_count => x_msg_count
534 , p_data => x_msg_data
535 );
536 END IF;
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
539 raise;
540 WHEN OTHERS THEN
541 ROLLBACK TO bis_Update_WM_Definition_PVT;
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 IF (x_msg_data IS NOT NULL) THEN
544 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Definition ';
545 ELSE
546 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Definition ';
547 END IF;
548 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
549 raise;
550 END Update_WM_Definition;
551
552 /*******************************************************************
553 *******************************************************************/
554 PROCEDURE Delete_WM_Definition(
555 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
556 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
557 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
558 ,x_return_status OUT NOCOPY VARCHAR2
559 ,x_msg_count OUT NOCOPY NUMBER
560 ,x_msg_data OUT NOCOPY VARCHAR2
561 ) IS
562 BEGIN
563 SAVEPOINT Delete_WM_Definition_PVT;
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566 DELETE BIS_WEIGHTED_MEASURE_DEFNS
567 WHERE WEIGHTED_DEFINITION_ID = p_Bis_WM_Rec.weighted_definition_id;
568
569 IF p_commit = FND_API.G_TRUE THEN
570 COMMIT;
571 END IF;
572 x_Bis_WM_Rec := p_Bis_WM_Rec;
573
574 EXCEPTION
575 WHEN FND_API.G_EXC_ERROR THEN
576 ROLLBACK TO Delete_WM_Definition_PVT;
577 IF (x_msg_data IS NULL) THEN
578 FND_MSG_PUB.Count_And_Get
579 ( p_encoded => FND_API.G_FALSE
580 , p_count => x_msg_count
581 , p_data => x_msg_data
582 );
583 END IF;
584 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
585 x_return_status := FND_API.G_RET_STS_ERROR;
586 raise;
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 ROLLBACK TO Delete_WM_Definition_PVT;
589 IF (x_msg_data IS NULL) THEN
590 FND_MSG_PUB.Count_And_Get
591 ( p_encoded => FND_API.G_FALSE
592 , p_count => x_msg_count
593 , p_data => x_msg_data
594 );
595 END IF;
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
597 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
598 raise;
599 WHEN OTHERS THEN
600 ROLLBACK TO Delete_WM_Definition_PVT;
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 IF (x_msg_data IS NOT NULL) THEN
603 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Definition ';
604 ELSE
605 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Definition ';
606 END IF;
607 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
608 raise;
609 END Delete_WM_Definition;
610
611 ------- APIs for table BIS_WEIGHTED_MEASURE_PARAMS
612
613 /*******************************************************************
614 *******************************************************************/
615 PROCEDURE Create_WM_Parameter(
616 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
617 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
618 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
619 ,x_return_status OUT NOCOPY VARCHAR2
620 ,x_msg_count OUT NOCOPY NUMBER
621 ,x_msg_data OUT NOCOPY VARCHAR2
622 ) IS
623 l_count NUMBER;
624 BEGIN
625 SAVEPOINT CreateWMParameterPVT;
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 INSERT INTO BIS_WEIGHTED_MEASURE_PARAMS(
629 WEIGHTED_PARAMETER_ID
630 ,WEIGHTED_DEFINITION_ID
631 ,TIME_LEVEL_VALUE_ID
632 ,FILTER_LEVEL_VALUE_ID
633 ,CREATION_DATE
634 ,CREATED_BY
635 ,LAST_UPDATE_DATE
636 ,LAST_UPDATED_BY
637 ,LAST_UPDATE_LOGIN
638 ) VALUES (
639 p_Bis_WM_Rec.weighted_parameter_id
640 ,p_Bis_WM_Rec.weighted_definition_id
641 ,p_Bis_WM_Rec.time_level_value_id
642 ,p_Bis_WM_Rec.filter_level_value_id
643 ,p_Bis_WM_Rec.Creation_Date
644 ,p_Bis_WM_Rec.Created_By
645 ,p_Bis_WM_Rec.Last_Update_Date
646 ,p_Bis_WM_Rec.Last_Updated_By
647 ,p_Bis_WM_Rec.Last_Update_Login
648 );
649
650 IF p_commit = FND_API.G_TRUE THEN
651 COMMIT;
652 END IF;
653 x_Bis_WM_Rec := p_Bis_WM_Rec;
654
655 EXCEPTION
656 WHEN FND_API.G_EXC_ERROR THEN
657 ROLLBACK TO CreateWMParameterPVT;
658 IF (x_msg_data IS NULL) THEN
659 FND_MSG_PUB.Count_And_Get
660 ( p_encoded => FND_API.G_FALSE
661 , p_count => x_msg_count
662 , p_data => x_msg_data
663 );
664 END IF;
665 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 raise;
668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
669 ROLLBACK TO CreateWMParameterPVT;
670 IF (x_msg_data IS NULL) THEN
671 FND_MSG_PUB.Count_And_Get
672 ( p_encoded => FND_API.G_FALSE
673 , p_count => x_msg_count
674 , p_data => x_msg_data
675 );
676 END IF;
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
679 raise;
680 WHEN OTHERS THEN
681 ROLLBACK TO CreateWMParameterPVT;
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683 IF (x_msg_data IS NOT NULL) THEN
684 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Create_WM_Parameter ';
685 ELSE
686 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Create_WM_Parameter ';
687 END IF;
688 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
689 raise;
690 END Create_WM_Parameter;
691
692 /*******************************************************************
693 *******************************************************************/
694 PROCEDURE Retrieve_WM_Parameter(
695 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
696 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
697 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
698 ,x_return_status OUT NOCOPY VARCHAR2
699 ,x_msg_count OUT NOCOPY NUMBER
700 ,x_msg_data OUT NOCOPY VARCHAR2
701 ) IS
702 BEGIN
706 SELECT
703 SAVEPOINT Retrieve_WM_Parameter_PVT;
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705
707 WEIGHTED_PARAMETER_ID
708 ,WEIGHTED_DEFINITION_ID
709 ,time_level_value_id
710 ,FILTER_LEVEL_VALUE_ID
711 ,CREATION_DATE
712 ,CREATED_BY
713 ,LAST_UPDATE_DATE
714 ,LAST_UPDATED_BY
715 ,LAST_UPDATE_LOGIN
716 INTO
717 x_Bis_WM_Rec.weighted_parameter_id
718 ,x_Bis_WM_Rec.weighted_definition_id
719 ,x_Bis_WM_Rec.time_level_value_id
720 ,x_Bis_WM_Rec.FILTER_level_value_id
721 ,x_Bis_WM_Rec.Creation_Date
722 ,x_Bis_WM_Rec.Created_By
723 ,x_Bis_WM_Rec.Last_Update_Date
724 ,x_Bis_WM_Rec.Last_Updated_By
725 ,x_Bis_WM_Rec.Last_Update_Login
726 FROM BIS_WEIGHTED_MEASURE_PARAMS
727 WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
728
729
730
731 EXCEPTION
732 WHEN FND_API.G_EXC_ERROR THEN
733 ROLLBACK TO Retrieve_WM_Parameter_PVT;
734 IF (x_msg_data IS NULL) THEN
735 FND_MSG_PUB.Count_And_Get
736 ( p_encoded => FND_API.G_FALSE
737 , p_count => x_msg_count
738 , p_data => x_msg_data
739 );
740 END IF;
741 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
742 x_return_status := FND_API.G_RET_STS_ERROR;
743 raise;
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 ROLLBACK TO Retrieve_WM_Parameter_PVT;
746 IF (x_msg_data IS NULL) THEN
747 FND_MSG_PUB.Count_And_Get
748 ( p_encoded => FND_API.G_FALSE
749 , p_count => x_msg_count
750 , p_data => x_msg_data
751 );
752 END IF;
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 raise;
755 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
756 WHEN OTHERS THEN
757 ROLLBACK TO Retrieve_WM_Parameter_PVT;
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 IF (x_msg_data IS NOT NULL) THEN
760 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Parameter ';
761 ELSE
762 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Parameter ';
763 END IF;
764 raise;
765 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
766 END Retrieve_WM_Parameter;
767
768 /*******************************************************************
769 *******************************************************************/
770 PROCEDURE Update_WM_Parameter(
771 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
772 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
773 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
774 ,x_return_status OUT NOCOPY VARCHAR2
775 ,x_msg_count OUT NOCOPY NUMBER
776 ,x_msg_data OUT NOCOPY VARCHAR2
777 ) IS
778 BEGIN
779 SAVEPOINT bis_Update_WM_Parameter_PVT;
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782 UPDATE BIS_WEIGHTED_MEASURE_PARAMS SET
783 WEIGHTED_DEFINITION_ID = p_Bis_WM_Rec.weighted_definition_id
784 ,TIME_LEVEL_VALUE_ID = p_Bis_WM_Rec.time_level_value_id
785 ,FILTER_LEVEL_VALUE_ID = p_Bis_WM_Rec.filter_level_value_id
786 ,LAST_UPDATE_DATE = p_Bis_WM_Rec.Last_Update_Date
787 ,LAST_UPDATED_BY = p_Bis_WM_Rec.Last_Updated_By
788 ,LAST_UPDATE_LOGIN = p_Bis_WM_Rec.Last_Update_Login
789 WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
790
791 IF p_commit = FND_API.G_TRUE THEN
792 COMMIT;
793 END IF;
794 x_Bis_WM_Rec := p_Bis_WM_Rec;
795
796 IF p_commit = FND_API.G_TRUE THEN
797 COMMIT;
798 END IF;
799
800 EXCEPTION
801 WHEN FND_API.G_EXC_ERROR THEN
802 ROLLBACK TO bis_Update_WM_Parameter_PVT;
803 IF (x_msg_data IS NULL) THEN
804 FND_MSG_PUB.Count_And_Get
805 ( p_encoded => FND_API.G_FALSE
806 , p_count => x_msg_count
807 , p_data => x_msg_data
808 );
809 END IF;
810 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 raise;
813 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
814 ROLLBACK TO bis_Update_WM_Parameter_PVT;
815 IF (x_msg_data IS NULL) THEN
816 FND_MSG_PUB.Count_And_Get
817 ( p_encoded => FND_API.G_FALSE
818 , p_count => x_msg_count
819 , p_data => x_msg_data
820 );
821 END IF;
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
824 raise;
825 WHEN OTHERS THEN
826 ROLLBACK TO bis_Update_WM_Parameter_PVT;
827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828 IF (x_msg_data IS NOT NULL) THEN
829 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Parameter ';
830 ELSE
831 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Parameter ';
832 END IF;
833 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
834 raise;
835 END Update_WM_Parameter;
836
837 /*******************************************************************
838 *******************************************************************/
839 PROCEDURE Delete_WM_Parameter(
840 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
841 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
845 ,x_msg_data OUT NOCOPY VARCHAR2
842 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
843 ,x_return_status OUT NOCOPY VARCHAR2
844 ,x_msg_count OUT NOCOPY NUMBER
846 ) IS
847 BEGIN
848 SAVEPOINT Delete_WM_Parameter_PVT;
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850
851 DELETE BIS_WEIGHTED_MEASURE_PARAMS
852 WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
853
854 IF p_commit = FND_API.G_TRUE THEN
855 COMMIT;
856 END IF;
857 x_Bis_WM_Rec := p_Bis_WM_Rec;
858
859 EXCEPTION
860 WHEN FND_API.G_EXC_ERROR THEN
861 ROLLBACK TO Delete_WM_Parameter_PVT;
862 IF (x_msg_data IS NULL) THEN
863 FND_MSG_PUB.Count_And_Get
864 ( p_encoded => FND_API.G_FALSE
865 , p_count => x_msg_count
866 , p_data => x_msg_data
867 );
868 END IF;
869 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
870 x_return_status := FND_API.G_RET_STS_ERROR;
871 raise;
872 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
873 ROLLBACK TO Delete_WM_Parameter_PVT;
874 IF (x_msg_data IS NULL) THEN
875 FND_MSG_PUB.Count_And_Get
876 ( p_encoded => FND_API.G_FALSE
877 , p_count => x_msg_count
878 , p_data => x_msg_data
879 );
880 END IF;
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
883 raise;
884 WHEN OTHERS THEN
885 ROLLBACK TO Delete_WM_Parameter_PVT;
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 IF (x_msg_data IS NOT NULL) THEN
888 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Parameter ';
889 ELSE
890 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Parameter ';
891 END IF;
892 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
893 raise;
894 END Delete_WM_Parameter;
895
896 ------- APIs for table BIS_WEIGHTED_MEASURE_WEIGHTS
897
898 /*******************************************************************
899 *******************************************************************/
900 PROCEDURE Create_WM_Weight(
901 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
902 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
903 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
904 ,x_return_status OUT NOCOPY VARCHAR2
905 ,x_msg_count OUT NOCOPY NUMBER
906 ,x_msg_data OUT NOCOPY VARCHAR2
907 ) IS
908 BEGIN
909 SAVEPOINT CreateWMDependencyPVT;
910 x_return_status := FND_API.G_RET_STS_SUCCESS;
911
912 INSERT INTO BIS_WEIGHTED_MEASURE_WEIGHTS(
913 WEIGHT_ID
914 ,WEIGHTED_PARAMETER_ID
915 ,DEPENDENT_MEASURE_ID
916 ,WEIGHT
917 ,CREATION_DATE
918 ,CREATED_BY
919 ,LAST_UPDATE_DATE
920 ,LAST_UPDATED_BY
921 ,LAST_UPDATE_LOGIN
922 ) VALUES (
923 p_Bis_WM_Rec.weight_id
924 ,p_Bis_WM_Rec.weighted_parameter_id
925 ,p_Bis_WM_Rec.dependent_measure_id
926 ,p_Bis_WM_Rec.weight
927 ,p_Bis_WM_Rec.Creation_Date
928 ,p_Bis_WM_Rec.Created_By
929 ,p_Bis_WM_Rec.Last_Update_Date
930 ,p_Bis_WM_Rec.Last_Updated_By
931 ,p_Bis_WM_Rec.Last_Update_Login
932 );
933 IF p_commit = FND_API.G_TRUE THEN
934 COMMIT;
935 END IF;
936 x_Bis_WM_Rec := p_Bis_WM_Rec;
937
938 EXCEPTION
939 WHEN FND_API.G_EXC_ERROR THEN
940 ROLLBACK TO CreateWMDependencyPVT;
941 IF (x_msg_data IS NULL) THEN
942 FND_MSG_PUB.Count_And_Get
943 ( p_encoded => FND_API.G_FALSE
944 , p_count => x_msg_count
945 , p_data => x_msg_data
946 );
947 END IF;
948 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 raise;
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 ROLLBACK TO CreateWMDependencyPVT;
953 IF (x_msg_data IS NULL) THEN
954 FND_MSG_PUB.Count_And_Get
955 ( p_encoded => FND_API.G_FALSE
956 , p_count => x_msg_count
957 , p_data => x_msg_data
958 );
959 END IF;
960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
961 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
962 raise;
963 WHEN OTHERS THEN
964 ROLLBACK TO CreateWMDependencyPVT;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 IF (x_msg_data IS NOT NULL) THEN
967 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Create_WM_Weight ';
968 ELSE
969 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Create_WM_Weight ';
970 END IF;
971 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
972 raise;
973 END Create_WM_Weight;
974
975 /*******************************************************************
976 *******************************************************************/
977 PROCEDURE Retrieve_WM_Weight(
978 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
979 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
980 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
981 ,x_return_status OUT NOCOPY VARCHAR2
982 ,x_msg_count OUT NOCOPY NUMBER
986 SAVEPOINT bis_Retrieve_WM_Weight_PVT;
983 ,x_msg_data OUT NOCOPY VARCHAR2
984 ) IS
985 BEGIN
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 SELECT
990 WEIGHT_ID
991 ,WEIGHTED_PARAMETER_ID
992 ,DEPENDENT_MEASURE_ID
993 ,WEIGHT
994 ,CREATION_DATE
995 ,CREATED_BY
996 ,LAST_UPDATE_DATE
997 ,LAST_UPDATED_BY
998 ,LAST_UPDATE_LOGIN
999 INTO
1000 x_Bis_WM_Rec.weight_id
1001 ,x_Bis_WM_Rec.weighted_parameter_id
1002 ,x_Bis_WM_Rec.dependent_measure_id
1003 ,x_Bis_WM_Rec.weight
1004 ,x_Bis_WM_Rec.Creation_Date
1005 ,x_Bis_WM_Rec.Created_By
1006 ,x_Bis_WM_Rec.Last_Update_Date
1007 ,x_Bis_WM_Rec.Last_Updated_By
1008 ,x_Bis_WM_Rec.Last_Update_Login
1009 FROM BIS_WEIGHTED_MEASURE_WEIGHTS
1010 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id;
1011
1012
1013 EXCEPTION
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 IF (x_msg_data IS NULL) THEN
1016 FND_MSG_PUB.Count_And_Get
1017 ( p_encoded => FND_API.G_FALSE
1018 , p_count => x_msg_count
1019 , p_data => x_msg_data
1020 );
1021 END IF;
1022 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1023 x_return_status := FND_API.G_RET_STS_ERROR;
1024 raise;
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026 IF (x_msg_data IS NULL) THEN
1027 FND_MSG_PUB.Count_And_Get
1028 ( p_encoded => FND_API.G_FALSE
1029 , p_count => x_msg_count
1030 , p_data => x_msg_data
1031 );
1032 END IF;
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1035 raise;
1036 WHEN NO_DATA_FOUND THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 IF (x_msg_data IS NOT NULL) THEN
1039 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Weight ';
1040 ELSE
1041 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Weight ';
1042 END IF;
1043 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1044 RAISE;
1045 WHEN OTHERS THEN
1046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1047 IF (x_msg_data IS NOT NULL) THEN
1048 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Weight ';
1049 ELSE
1050 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Weight ';
1051 END IF;
1052 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1053 -- raise;
1054 END Retrieve_WM_Weight;
1055
1056 /*******************************************************************
1057 *******************************************************************/
1058 PROCEDURE Update_WM_Weight(
1059 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1060 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1061 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1062 ,x_return_status OUT NOCOPY VARCHAR2
1063 ,x_msg_count OUT NOCOPY NUMBER
1064 ,x_msg_data OUT NOCOPY VARCHAR2
1065 ) IS
1066 BEGIN
1067
1068 SAVEPOINT bis_Update_WM_Weight_PVT;
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070
1071 UPDATE BIS_WEIGHTED_MEASURE_WEIGHTS SET
1072 WEIGHT_ID = p_Bis_WM_Rec.weight_id
1073 ,WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id
1074 ,DEPENDENT_MEASURE_ID = p_Bis_WM_Rec.dependent_measure_id
1075 ,WEIGHT = p_Bis_WM_Rec.weight
1076 ,LAST_UPDATE_DATE = p_Bis_WM_Rec.Last_Update_Date
1077 ,LAST_UPDATED_BY = p_Bis_WM_Rec.Last_Updated_By
1078 ,LAST_UPDATE_LOGIN = p_Bis_WM_Rec.Last_Update_Login
1079 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id;
1080
1081 x_Bis_WM_Rec := p_Bis_WM_Rec;
1082 IF p_commit = FND_API.G_TRUE THEN
1083 COMMIT;
1084 END IF;
1085
1086 EXCEPTION
1087 WHEN FND_API.G_EXC_ERROR THEN
1088 ROLLBACK TO bis_Update_WM_Weight_PVT;
1089 IF (x_msg_data IS NULL) THEN
1090 FND_MSG_PUB.Count_And_Get
1091 ( p_encoded => FND_API.G_FALSE
1092 , p_count => x_msg_count
1093 , p_data => x_msg_data
1094 );
1095 END IF;
1096 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 raise;
1099 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1100 ROLLBACK TO bis_Update_WM_Weight_PVT;
1101 IF (x_msg_data IS NULL) THEN
1102 FND_MSG_PUB.Count_And_Get
1103 ( p_encoded => FND_API.G_FALSE
1104 , p_count => x_msg_count
1105 , p_data => x_msg_data
1106 );
1107 END IF;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1110 raise;
1111 WHEN OTHERS THEN
1112 ROLLBACK TO bis_Update_WM_Weight_PVT;
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1114 IF (x_msg_data IS NOT NULL) THEN
1115 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Weight ';
1116 ELSE
1117 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Weight ';
1118 END IF;
1119 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1120 raise;
1121 END Update_WM_Weight;
1122
1123 /*******************************************************************
1127 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1124 *******************************************************************/
1125 PROCEDURE Delete_WM_Weight(
1126 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1128 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1129 ,x_return_status OUT NOCOPY VARCHAR2
1130 ,x_msg_count OUT NOCOPY NUMBER
1131 ,x_msg_data OUT NOCOPY VARCHAR2
1132 ) IS
1133 BEGIN
1134 SAVEPOINT Delete_WM_Weight_PVT;
1135 x_return_status := FND_API.G_RET_STS_SUCCESS;
1136
1137 DELETE BIS_WEIGHTED_MEASURE_WEIGHTS
1138 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id;
1139
1140 IF p_commit = FND_API.G_TRUE THEN
1141 COMMIT;
1142 END IF;
1143 x_Bis_WM_Rec := p_Bis_WM_Rec;
1144
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 ROLLBACK TO Delete_WM_Weight_PVT;
1148 IF (x_msg_data IS NULL) THEN
1149 FND_MSG_PUB.Count_And_Get
1150 ( p_encoded => FND_API.G_FALSE
1151 , p_count => x_msg_count
1152 , p_data => x_msg_data
1153 );
1154 END IF;
1155 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1156 x_return_status := FND_API.G_RET_STS_ERROR;
1157 raise;
1158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1159 ROLLBACK TO Delete_WM_Weight_PVT;
1160 IF (x_msg_data IS NULL) THEN
1161 FND_MSG_PUB.Count_And_Get
1162 ( p_encoded => FND_API.G_FALSE
1163 , p_count => x_msg_count
1164 , p_data => x_msg_data
1165 );
1166 END IF;
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1169 raise;
1170 WHEN OTHERS THEN
1171 ROLLBACK TO Delete_WM_Weight_PVT;
1172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1173 IF (x_msg_data IS NOT NULL) THEN
1174 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Weight ';
1175 ELSE
1176 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Weight ';
1177 END IF;
1178 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1179 raise;
1180 END Delete_WM_Weight;
1181
1182 ------- APIs for table BIS_WEIGHTED_MEASURE_SCORES
1183 /*******************************************************************
1184 *******************************************************************/
1185 PROCEDURE Create_WM_Score(
1186 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1187 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1188 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1189 ,x_return_status OUT NOCOPY VARCHAR2
1190 ,x_msg_count OUT NOCOPY NUMBER
1191 ,x_msg_data OUT NOCOPY VARCHAR2
1192 ) IS
1193 BEGIN
1194 SAVEPOINT bisCreateWMScorePVT;
1195 x_return_status := FND_API.G_RET_STS_SUCCESS;
1196
1197 INSERT INTO BIS_WEIGHTED_MEASURE_SCORES(
1198 WEIGHT_ID
1199 ,LOW_RANGE
1200 ,HIGH_RANGE
1201 ,SCORE
1202 ,CREATION_DATE
1203 ,CREATED_BY
1204 ,LAST_UPDATE_DATE
1205 ,LAST_UPDATED_BY
1206 ,LAST_UPDATE_LOGIN
1207 ) VALUES (
1208 p_Bis_WM_Rec.weight_id
1209 ,p_Bis_WM_Rec.low_range
1210 ,p_Bis_WM_Rec.high_range
1211 ,p_Bis_WM_Rec.score
1212 ,p_Bis_WM_Rec.Creation_Date
1213 ,p_Bis_WM_Rec.Created_By
1214 ,p_Bis_WM_Rec.Last_Update_Date
1215 ,p_Bis_WM_Rec.Last_Updated_By
1216 ,p_Bis_WM_Rec.Last_Update_Login
1217 );
1218 IF p_commit = FND_API.G_TRUE THEN
1219 COMMIT;
1220 END IF;
1221 x_Bis_WM_Rec := p_Bis_WM_Rec;
1222
1223 EXCEPTION
1224 WHEN FND_API.G_EXC_ERROR THEN
1225 ROLLBACK TO bisCreateWMScorePVT;
1226 IF (x_msg_data IS NULL) THEN
1227 FND_MSG_PUB.Count_And_Get
1228 ( p_encoded => FND_API.G_FALSE
1229 , p_count => x_msg_count
1230 , p_data => x_msg_data
1231 );
1232 END IF;
1233 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 raise;
1236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1237 ROLLBACK TO bisCreateWMScorePVT;
1238 IF (x_msg_data IS NULL) THEN
1239 FND_MSG_PUB.Count_And_Get
1240 ( p_encoded => FND_API.G_FALSE
1241 , p_count => x_msg_count
1242 , p_data => x_msg_data
1243 );
1244 END IF;
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1247 raise;
1248 WHEN OTHERS THEN
1249 ROLLBACK TO bisCreateWMScorePVT;
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 IF (x_msg_data IS NOT NULL) THEN
1252 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Create_WM_Score ';
1253 ELSE
1254 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Create_WM_Score ';
1255 END IF;
1256 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1257 raise;
1258 END Create_WM_Score;
1259
1260 /*******************************************************************
1261 *******************************************************************/
1262 PROCEDURE Retrieve_WM_Score(
1263 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1264 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1265 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1269 ) IS
1266 ,x_return_status OUT NOCOPY VARCHAR2
1267 ,x_msg_count OUT NOCOPY NUMBER
1268 ,x_msg_data OUT NOCOPY VARCHAR2
1270 BEGIN
1271 SAVEPOINT bis_Retrieve_WM_Score_PVT;
1272 x_return_status := FND_API.G_RET_STS_SUCCESS;
1273
1274 SELECT
1275 WEIGHT_ID
1276 ,LOW_RANGE
1277 ,HIGH_RANGE
1278 ,SCORE
1279 ,CREATION_DATE
1280 ,CREATED_BY
1281 ,LAST_UPDATE_DATE
1282 ,LAST_UPDATED_BY
1283 ,LAST_UPDATE_LOGIN
1284 INTO
1285 x_Bis_WM_Rec.weight_id
1286 ,x_Bis_WM_Rec.low_range
1287 ,x_Bis_WM_Rec.high_range
1288 ,x_Bis_WM_Rec.score
1289 ,x_Bis_WM_Rec.Creation_Date
1290 ,x_Bis_WM_Rec.Created_By
1291 ,x_Bis_WM_Rec.Last_Update_Date
1292 ,x_Bis_WM_Rec.Last_Updated_By
1293 ,x_Bis_WM_Rec.Last_Update_Login
1294 FROM BIS_WEIGHTED_MEASURE_SCORES
1295 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id
1296 AND LOW_RANGE = p_Bis_WM_Rec.low_range
1297 AND HIGH_RANGE = p_Bis_WM_Rec.high_range;
1298
1299
1300 EXCEPTION
1301 WHEN FND_API.G_EXC_ERROR THEN
1302 ROLLBACK TO bis_Retrieve_WM_Score_PVT;
1303 IF (x_msg_data IS NULL) THEN
1304 FND_MSG_PUB.Count_And_Get
1305 ( p_encoded => FND_API.G_FALSE
1306 , p_count => x_msg_count
1307 , p_data => x_msg_data
1308 );
1309 END IF;
1310 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1311 x_return_status := FND_API.G_RET_STS_ERROR;
1312 raise;
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 ROLLBACK TO bis_Retrieve_WM_Score_PVT;
1315 IF (x_msg_data IS NULL) THEN
1316 FND_MSG_PUB.Count_And_Get
1317 ( p_encoded => FND_API.G_FALSE
1318 , p_count => x_msg_count
1319 , p_data => x_msg_data
1320 );
1321 END IF;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1324 raise;
1325 WHEN NO_DATA_FOUND THEN
1326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1327 IF (x_msg_data IS NOT NULL) THEN
1328 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Score ';
1329 ELSE
1330 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Score ';
1331 END IF;
1332 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1333 RAISE;
1334 WHEN OTHERS THEN
1335 ROLLBACK TO bis_Retrieve_WM_Score_PVT;
1336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337 IF (x_msg_data IS NOT NULL) THEN
1338 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Score ';
1339 ELSE
1340 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Score ';
1341 END IF;
1342 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1343 raise;
1344 END Retrieve_WM_Score;
1345
1346 /*******************************************************************
1347 *******************************************************************/
1348 PROCEDURE Update_WM_Score(
1349 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1350 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1351 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1352 ,x_return_status OUT NOCOPY VARCHAR2
1353 ,x_msg_count OUT NOCOPY NUMBER
1354 ,x_msg_data OUT NOCOPY VARCHAR2
1355 ) IS
1356 BEGIN
1357 SAVEPOINT bis_Update_WM_Score_PVT;
1358 x_return_status := FND_API.G_RET_STS_SUCCESS;
1359
1360 UPDATE BIS_WEIGHTED_MEASURE_SCORES SET
1361 WEIGHT_ID = p_Bis_WM_Rec.weight_id
1362 ,LOW_RANGE = p_Bis_WM_Rec.low_range
1363 ,HIGH_RANGE = p_Bis_WM_Rec.high_range
1364 ,SCORE = p_Bis_WM_Rec.score
1365 ,LAST_UPDATE_DATE = p_Bis_WM_Rec.Last_Update_Date
1366 ,LAST_UPDATED_BY = p_Bis_WM_Rec.Last_Updated_By
1367 ,LAST_UPDATE_LOGIN = p_Bis_WM_Rec.Last_Update_Login
1368 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id
1369 AND LOW_RANGE = p_Bis_WM_Rec.low_range
1370 AND HIGH_RANGE = p_Bis_WM_Rec.high_range;
1371
1372 x_Bis_WM_Rec := p_Bis_WM_Rec;
1373 IF p_commit = FND_API.G_TRUE THEN
1374 COMMIT;
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN FND_API.G_EXC_ERROR THEN
1379 ROLLBACK TO bis_Update_WM_Score_PVT;
1380 IF (x_msg_data IS NULL) THEN
1381 FND_MSG_PUB.Count_And_Get
1382 ( p_encoded => FND_API.G_FALSE
1383 , p_count => x_msg_count
1384 , p_data => x_msg_data
1385 );
1386 END IF;
1387 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1388 x_return_status := FND_API.G_RET_STS_ERROR;
1389 raise;
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 ROLLBACK TO bis_Update_WM_Score_PVT;
1392 IF (x_msg_data IS NULL) THEN
1393 FND_MSG_PUB.Count_And_Get
1394 ( p_encoded => FND_API.G_FALSE
1395 , p_count => x_msg_count
1396 , p_data => x_msg_data
1397 );
1398 END IF;
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1401 raise;
1402 WHEN OTHERS THEN
1403 ROLLBACK TO bis_Update_WM_Score_PVT;
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405 IF (x_msg_data IS NOT NULL) THEN
1406 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Score ';
1407 ELSE
1411 raise;
1408 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Score ';
1409 END IF;
1410 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1412 END Update_WM_Score;
1413
1414 /*******************************************************************
1415 *******************************************************************/
1416 PROCEDURE Delete_WM_Score(
1417 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1418 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1419 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1420 ,x_return_status OUT NOCOPY VARCHAR2
1421 ,x_msg_count OUT NOCOPY NUMBER
1422 ,x_msg_data OUT NOCOPY VARCHAR2
1423 ) IS
1424 BEGIN
1425 SAVEPOINT Delete_WM_Score_PVT;
1426 x_return_status := FND_API.G_RET_STS_SUCCESS;
1427
1428 IF p_Bis_WM_Rec.low_range IS NULL AND p_Bis_WM_Rec.low_range IS NULL THEN
1429 -- delete all the Scores for a specific Weight_id
1430 -- Use for Cascading
1431 DELETE BIS_WEIGHTED_MEASURE_SCORES
1432 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id;
1433 ELSE
1434 -- Delete jus one Score
1435 DELETE BIS_WEIGHTED_MEASURE_SCORES
1436 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id
1437 AND LOW_RANGE = p_Bis_WM_Rec.low_range
1438 AND HIGH_RANGE = p_Bis_WM_Rec.high_range;
1439 END IF;
1440
1441 IF p_commit = FND_API.G_TRUE THEN
1442 COMMIT;
1443 END IF;
1444 x_Bis_WM_Rec := p_Bis_WM_Rec;
1445
1446
1447 EXCEPTION
1448 WHEN FND_API.G_EXC_ERROR THEN
1449 ROLLBACK TO Delete_WM_Score_PVT;
1450 IF (x_msg_data IS NULL) THEN
1451 FND_MSG_PUB.Count_And_Get
1452 ( p_encoded => FND_API.G_FALSE
1453 , p_count => x_msg_count
1454 , p_data => x_msg_data
1455 );
1456 END IF;
1457 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1458 x_return_status := FND_API.G_RET_STS_ERROR;
1459 raise;
1460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1461 ROLLBACK TO Delete_WM_Score_PVT;
1462 IF (x_msg_data IS NULL) THEN
1463 FND_MSG_PUB.Count_And_Get
1464 ( p_encoded => FND_API.G_FALSE
1465 , p_count => x_msg_count
1466 , p_data => x_msg_data
1467 );
1468 END IF;
1469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1470 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1471 raise;
1472 WHEN OTHERS THEN
1473 ROLLBACK TO Delete_WM_Score_PVT;
1474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475 IF (x_msg_data IS NOT NULL) THEN
1476 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Score ';
1477 ELSE
1478 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Score ';
1479 END IF;
1480 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1481 raise;
1482 END Delete_WM_Score;
1483
1484 /*******************************************************************
1485 *******************************************************************/
1486 function validate_measure_id(
1487 measure_id IN NUMBER
1488 ) RETURN VARCHAR2 IS
1489 l_return_value VARCHAR2 (1);
1490 l_count NUMBER;
1491 BEGIN
1492 l_return_value := FND_API.G_TRUE;
1493 -- Pending code the validation
1494 SELECT COUNT(INDICATOR_ID)
1495 INTO l_count
1496 FROM BIS_INDICATORS
1497 WHERE INDICATOR_ID = measure_id;
1498
1499 --DBMS_OUTPUT.PUT_LINE('validate_measure_id: measure_id = '|| measure_id);
1500 --DBMS_OUTPUT.PUT_LINE('validate_measure_id: l_count = '||l_count);
1501
1502 IF l_count = 0 THEN
1503 l_return_value := FND_API.G_FALSE;
1504 END IF;
1505
1506 -- l_return_value := FND_API.G_TRUE; -- This need to be deleted
1507
1508 RETURN l_return_value;
1509
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 return l_return_value;
1514 end validate_measure_id;
1515
1516
1517 /*******************************************************************
1518 *******************************************************************/
1519 PROCEDURE Update_WM_Last_Update_Info(
1520 p_commit IN VARCHAR2 := FND_API.G_FALSE
1521 ,p_weighted_measure_id IN NUMBER
1522 ,x_return_status OUT NOCOPY VARCHAR2
1523 ,x_msg_count OUT NOCOPY NUMBER
1524 ,x_msg_data OUT NOCOPY VARCHAR2
1525 ) IS
1526 l_user_id NUMBER;
1527 l_login_id NUMBER;
1528 l_dataset_id NUMBER;
1529 BEGIN
1530
1531 SAVEPOINT Update_WM_Last_Update_Info;
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 l_user_id := fnd_global.USER_ID;
1535 l_login_id := fnd_global.LOGIN_ID;
1536
1537 UPDATE bis_indicators
1538 SET
1539 last_update_date = sysdate
1540 ,last_updated_by = l_user_id
1541 ,last_update_login = l_login_id
1542 WHERE indicator_id = p_weighted_measure_id;
1543
1544 SELECT dataset_id
1545 INTO l_dataset_id
1546 FROM bis_indicators
1547 WHERE indicator_id = p_weighted_measure_id;
1548
1549 UPDATE bsc_sys_datasets_b
1550 SET
1551 last_update_date = sysdate
1552 ,last_updated_by = l_user_id
1553 ,last_update_login = l_login_id
1554 WHERE dataset_id = l_dataset_id;
1555
1556 IF p_commit = FND_API.G_TRUE THEN
1557 COMMIT;
1558 END IF;
1559
1560 EXCEPTION
1561 WHEN FND_API.G_EXC_ERROR THEN
1562 ROLLBACK TO Update_WM_Last_Update_Info;
1563 IF (x_msg_data IS NULL) THEN
1564 FND_MSG_PUB.Count_And_Get
1565 ( p_encoded => FND_API.G_FALSE
1566 , p_count => x_msg_count
1567 , p_data => x_msg_data
1568 );
1569 END IF;
1570 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1571 x_return_status := FND_API.G_RET_STS_ERROR;
1572 raise;
1573 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1574 ROLLBACK TO Update_WM_Last_Update_Info;
1575 IF (x_msg_data IS NULL) THEN
1576 FND_MSG_PUB.Count_And_Get
1577 ( p_encoded => FND_API.G_FALSE
1578 , p_count => x_msg_count
1579 , p_data => x_msg_data
1580 );
1581 END IF;
1582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1583 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1584 raise;
1585 WHEN OTHERS THEN
1586 ROLLBACK TO Update_WM_Last_Update_Info;
1587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588 IF (x_msg_data IS NOT NULL) THEN
1589 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PVT.Update_WM_Last_Update_Info ';
1590 ELSE
1591 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PVT.Update_WM_Last_Update_Info ';
1592 END IF;
1593 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1594 raise;
1595 END Update_WM_Last_Update_Info;
1596
1597
1598 END BIS_WEIGHTED_MEASURE_PVT;