[Home] [Help]
PACKAGE BODY: APPS.BIS_WEIGHTED_MEASURE_PUB
Source
1 PACKAGE BODY BIS_WEIGHTED_MEASURE_PUB AS
2 /* $Header: BISPWMEB.pls 120.3.12000000.3 2007/02/01 11:09:34 akoduri ship $ */
3 /*======================================================================================+
4 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +======================================================================================+
7 | FILENAME |
8 | BISPWMEB.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 | 05/12/05 jxyu added Set_Weights_Data API. |
20 | 07/12/05 sawu Bug#4482736: added Get_Dep_KPI_Format_Mask |
21 | 08-AUG-05 ashankar Bug#4517812 Modified the method Delete_WM_Dependency |
22 | 15-SEP-05 jxyu Modified Set_Weights_Data API for bug#4427932 |
23 | 11-JAN-07 akoduri Bug# 5594225: Performance issue in Mass Update UI |
24 +======================================================================================*/
25
26 -- Abbreviation Used"
27 -- WM -> Weighted Measure
28 -- SN -> Short Name
29
30 /************************************************************************************
31 -- API name : Delete_Bulk_Weights_Scores
32 -- Type : Private
33 -- Deletes the scores and weights data for given the parameter combinations
34 -- Restricts the criteria to one dependent measure if p_dependent_measure_id
35 -- is non - null value
36
37 -- Logic :
38 -- 1. Fetch the weight ids corresponding to the p_Param_Ids
39 -- 2. Delete the entries from weights and scores tables using the
40 -- entries in Step1
41 ************************************************************************************/
42 PROCEDURE Delete_Bulk_Weights_Scores(
43 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
44 ,p_Param_Ids IN FND_TABLE_OF_NUMBER
45 ,p_dependent_measure_id IN NUMBER := NULL
46 ,x_return_status OUT NOCOPY VARCHAR2
47 ,x_msg_count OUT NOCOPY NUMBER
48 ,x_msg_data OUT NOCOPY VARCHAR2
49 ) IS
50 l_Weight_Ids FND_TABLE_OF_NUMBER;
51 BEGIN
52 SAVEPOINT BisDeleteBulkWeightsScores;
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 IF p_Param_Ids.COUNT > 0 THEN
56 IF p_dependent_measure_id IS NULL THEN
57 SELECT
58 weights.weight_id
59 BULK COLLECT INTO
60 l_Weight_Ids
61 FROM
62 bis_weighted_measure_weights weights
63 WHERE
64 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(p_Param_Ids AS FND_TABLE_OF_NUMBER)));
65 ELSE
66 SELECT
67 weights.weight_id
68 BULK COLLECT INTO
69 l_Weight_Ids
70 FROM
71 bis_weighted_measure_weights weights
72 WHERE
73 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(p_Param_Ids AS FND_TABLE_OF_NUMBER))) AND
74 dependent_measure_id = p_dependent_measure_id;
75 END IF;
76
77 FORALL i in 1..l_Weight_Ids.COUNT
78 DELETE FROM
79 bis_weighted_measure_scores
80 WHERE
81 weight_id = l_Weight_Ids(i);
82
83 FORALL i in 1..l_Weight_Ids.COUNT
84 DELETE FROM
85 bis_weighted_measure_weights
86 WHERE
87 weight_id = l_Weight_Ids(i);
88 END IF;
89
90 IF FND_API.To_Boolean( p_commit ) THEN
91 COMMIT;
92 END IF;
93
94 EXCEPTION
95 WHEN FND_API.G_EXC_ERROR THEN
96 ROLLBACK TO BisDeleteBulkWeightsScores;
97 IF (x_msg_data IS NULL) THEN
98 FND_MSG_PUB.Count_And_Get
99 ( p_encoded => FND_API.G_FALSE
100 , p_count => x_msg_count
101 , p_data => x_msg_data
102 );
103 END IF;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 raise;
106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
107 ROLLBACK TO BisDeleteBulkWeightsScores;
108 IF (x_msg_data IS NULL) THEN
109 FND_MSG_PUB.Count_And_Get
110 ( p_encoded => FND_API.G_FALSE
111 , p_count => x_msg_count
112 , p_data => x_msg_data
113 );
114 END IF;
115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
116 raise;
117 WHEN OTHERS THEN
118 ROLLBACK TO BisDeleteBulkWeightsScores;
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
120 IF (x_msg_data IS NOT NULL) THEN
121 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_Bulk_Weights_Scores ';
122 ELSE
123 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_Bulk_Weights_Scores ';
124 END IF;
125 raise;
126 END Delete_Bulk_Weights_Scores;
127
128 /************************************************************************************
129 -- API name : Delete_Cascade_WM_Parameters
130 -- Type : Private
131 -- Deletes the parameter combinations , weights and scores for a particular
132 -- definition
133 -- This API will be called in two scenarios
134 -- 1. When a WAM KPI is deleted or the report corresponding to that is deleted
135 -- 2. When the filter dimension object is changed
136
137 -- Logic :
138 -- 1. Fetch all the parameter ids corresponding to the given definition id
139 -- 2. Retrieve all the weight ids that are defined for the above parameter
140 -- ids
141 -- 3. Delete the weights and scores corresponding to the above weight ids
142 -- 4. Delete all the parameter combinations corresponding to that definition
143 ************************************************************************************/
144 PROCEDURE Delete_Cascade_WM_Parameters(
145 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
146 ,p_weighted_definition_id IN NUMBER
147 ,x_return_status OUT NOCOPY VARCHAR2
148 ,x_msg_count OUT NOCOPY NUMBER
149 ,x_msg_data OUT NOCOPY VARCHAR2
150 ) IS
151 l_Param_Ids FND_TABLE_OF_NUMBER;
152 l_Weight_Ids FND_TABLE_OF_NUMBER;
153 BEGIN
154 SAVEPOINT BisDeleteCascadeWMParameters;
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156
157 SELECT
158 params.weighted_parameter_id
159 BULK COLLECT INTO
160 l_Param_Ids
161 FROM
162 bis_weighted_measure_params params
163 WHERE
164 params.weighted_definition_id = p_weighted_definition_id;
165
166 IF l_Param_Ids.COUNT > 0 THEN
167 SELECT
168 weights.weight_id
169 BULK COLLECT INTO
170 l_Weight_Ids
171 FROM
172 bis_weighted_measure_weights weights
173 WHERE
174 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER)));
175
176 Delete_Bulk_Weights_Scores (
177 p_commit => FND_API.G_FALSE
178 ,p_Param_Ids => l_Param_Ids
179 ,x_return_status => x_return_status
180 ,x_msg_count => x_msg_count
181 ,x_msg_data => x_msg_data
182 );
183 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185 END IF;
186
187 FORALL i in 1..l_Param_Ids.COUNT
188 DELETE FROM
189 bis_weighted_measure_params
190 WHERE
191 weighted_parameter_id = l_Param_Ids(i);
192
193 END IF;
194
195 IF FND_API.To_Boolean( p_commit ) THEN
196 COMMIT;
197 END IF;
198
199 EXCEPTION
200 WHEN FND_API.G_EXC_ERROR THEN
201 ROLLBACK TO BisDeleteCascadeWMParameters;
202 IF (x_msg_data IS NULL) THEN
203 FND_MSG_PUB.Count_And_Get
204 ( p_encoded => FND_API.G_FALSE
205 , p_count => x_msg_count
206 , p_data => x_msg_data
207 );
208 END IF;
209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 raise;
212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
213 ROLLBACK TO BisDeleteCascadeWMParameters;
214 IF (x_msg_data IS NULL) THEN
215 FND_MSG_PUB.Count_And_Get
216 ( p_encoded => FND_API.G_FALSE
217 , p_count => x_msg_count
218 , p_data => x_msg_data
219 );
220 END IF;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
223 raise;
224 WHEN OTHERS THEN
225 ROLLBACK TO BisDeleteCascadeWMParameters;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 IF (x_msg_data IS NOT NULL) THEN
228 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_Cascade_WM_Parameters ';
229 ELSE
230 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_Cascade_WM_Parameters ';
231 END IF;
232 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
233 raise;
234 END Delete_Cascade_WM_Parameters;
235 /*******************************************************************
236 *******************************************************************/
237 FUNCTION Is_More
238 ( p_names IN OUT NOCOPY VARCHAR2
239 , p_name OUT NOCOPY VARCHAR2
240 ) RETURN BOOLEAN
241 IS
242 l_pos_ids NUMBER;
243 l_pos_rel_types NUMBER;
244 l_pos_rel_columns NUMBER;
245 BEGIN
246 IF (p_names IS NOT NULL) THEN
247 l_pos_ids := INSTR(p_names, ',');
248 IF (l_pos_ids > 0) THEN
249 p_name := TRIM(SUBSTR(p_names, 1, l_pos_ids - 1));
250 p_names := TRIM(SUBSTR(p_names, l_pos_ids + 1));
251 ELSE
252 p_name := TRIM(p_names);
253 p_names := NULL;
254 END IF;
255 RETURN TRUE;
256 ELSE
257 RETURN FALSE;
258 END IF;
259 END Is_More;
260
261 ------- APIs for tables BIS_WEIGHTED_MEASURE_DEPENDS
262 /*******************************************************************
263 *******************************************************************/
264 PROCEDURE Create_WM_Dependency(
265 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
266 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
267 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
268 ,x_return_status OUT NOCOPY VARCHAR2
269 ,x_msg_count OUT NOCOPY NUMBER
270 ,x_msg_data OUT NOCOPY VARCHAR2
271 ) IS
272 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
273 BEGIN
274 SAVEPOINT CreateWMDependencyPUB;
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276 l_Bis_WM_Rec :=p_Bis_WM_Rec;
277
278 --DBMS_OUTPUT.PUT_LINE(' *** Create_WM_Dependency ***');
279 --DBMS_OUTPUT.PUT_LINE('p_Bis_WM_Rec.weighted_measure_id = '|| p_Bis_WM_Rec.weighted_measure_id);
280 --DBMS_OUTPUT.PUT_LINE('p_Bis_WM_Rec.dependent_measure_id = '|| p_Bis_WM_Rec.dependent_measure_id);
281 --DBMS_OUTPUT.PUT_LINE('p_Bis_WM_Rec.Created_By = '|| p_Bis_WM_Rec.Created_By );
282
283 -- METADATA COLUMNS:
284 --weighted_measure_id
285 IF BIS_WEIGHTED_MEASURE_PVT.validate_measure_id(p_Bis_WM_Rec.weighted_measure_id) = FND_API.G_FALSE THEN
286 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_W_MEASURE_ID');
287 FND_MESSAGE.SET_TOKEN('MEASURE', p_Bis_WM_Rec.weighted_measure_id);
288 FND_MSG_PUB.ADD;
289 RAISE FND_API.G_EXC_ERROR;
290 end if;
291 --dependent_measure_id
292 IF BIS_WEIGHTED_MEASURE_PVT.validate_measure_id(p_Bis_WM_Rec.dependent_measure_id) = FND_API.G_FALSE THEN
293 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_D_MEASURE_ID');
294 FND_MESSAGE.SET_TOKEN('MEASURE', p_Bis_WM_Rec.weighted_measure_id);
295 FND_MSG_PUB.ADD;
296 RAISE FND_API.G_EXC_ERROR;
297 end if;
298
299 -- WHO COLUMNS
300 l_Bis_WM_Rec.Creation_Date := sysdate;
301 l_Bis_WM_Rec.Created_By := FND_GLOBAL.USER_ID;
302 l_Bis_WM_Rec.Last_Update_Date := sysdate;
303 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
304 IF l_Bis_WM_Rec.Last_Update_Login IS NULL THEN
305 l_Bis_WM_Rec.Last_Update_Login := 0;
306 END IF;
307
308
309 BIS_WEIGHTED_MEASURE_PVT.Create_WM_Dependency(
310 p_commit => p_commit
311 ,p_Bis_WM_Rec => l_Bis_WM_Rec
312 ,x_Bis_WM_Rec => x_Bis_WM_Rec
313 ,x_return_status => x_return_status
314 ,x_msg_count => x_msg_count
315 ,x_msg_data => x_msg_data
316 );
317
318
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 ROLLBACK TO CreateWMDependencyPUB;
322 IF (x_msg_data IS NULL) THEN
323 FND_MSG_PUB.Count_And_Get
324 ( p_encoded => FND_API.G_FALSE
325 , p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328 END IF;
329 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 raise;
332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333 ROLLBACK TO CreateWMDependencyPUB;
334 IF (x_msg_data IS NULL) THEN
335 FND_MSG_PUB.Count_And_Get
336 ( p_encoded => FND_API.G_FALSE
337 , p_count => x_msg_count
338 , p_data => x_msg_data
339 );
340 END IF;
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
343 raise;
344 WHEN OTHERS THEN
345 ROLLBACK TO CreateWMDependencyPUB;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 IF (x_msg_data IS NOT NULL) THEN
348 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency ';
349 ELSE
350 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency ';
351 END IF;
352 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
353 raise;
354
355 END Create_WM_Dependency;
356
357 /*******************************************************************
358 *******************************************************************/
359 PROCEDURE Retrieve_WM_Dependency(
360 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
361 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
362 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
363 ,x_return_status OUT NOCOPY VARCHAR2
364 ,x_msg_count OUT NOCOPY NUMBER
365 ,x_msg_data OUT NOCOPY VARCHAR2
366 ) IS
367 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
368 BEGIN
369 SAVEPOINT Retrieve_WM_Dependency_pub;
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371
372 BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Dependency(
373 p_commit => p_commit
374 ,p_Bis_WM_Rec => p_Bis_WM_Rec
375 ,x_Bis_WM_Rec => x_Bis_WM_Rec
376 ,x_return_status => x_return_status
377 ,x_msg_count => x_msg_count
378 ,x_msg_data => x_msg_data
379 );
380
381 EXCEPTION
382 WHEN FND_API.G_EXC_ERROR THEN
383 ROLLBACK TO Retrieve_WM_Dependency_pub;
384 IF (x_msg_data IS NULL) THEN
385 FND_MSG_PUB.Count_And_Get
386 ( p_encoded => FND_API.G_FALSE
387 , p_count => x_msg_count
388 , p_data => x_msg_data
389 );
390 END IF;
391 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
392 x_return_status := FND_API.G_RET_STS_ERROR;
393 raise;
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 ROLLBACK TO Retrieve_WM_Dependency_pub;
396 IF (x_msg_data IS NULL) THEN
397 FND_MSG_PUB.Count_And_Get
398 ( p_encoded => FND_API.G_FALSE
399 , p_count => x_msg_count
400 , p_data => x_msg_data
401 );
402 END IF;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 raise;
405 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
406 WHEN OTHERS THEN
407 ROLLBACK TO Retrieve_WM_Dependency_pub;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 IF (x_msg_data IS NOT NULL) THEN
410 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Dependency ';
411 ELSE
412 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Dependency ';
413 END IF;
414 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
415 raise;
416
417 END Retrieve_WM_Dependency;
418
419 /*******************************************************************
420 *******************************************************************/
421 PROCEDURE Update_WM_Dependency(
422 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
423 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
424 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
425 ,x_return_status OUT NOCOPY VARCHAR2
426 ,x_msg_count OUT NOCOPY NUMBER
427 ,x_msg_data OUT NOCOPY VARCHAR2
428 ) IS
429 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
430 BEGIN
431 SAVEPOINT bis_Update_WM_Dependency_pub;
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433
434 BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Dependency(
435 p_commit => p_commit
436 ,p_Bis_WM_Rec => p_Bis_WM_Rec
437 ,x_Bis_WM_Rec => l_Bis_WM_Rec
438 ,x_return_status => x_return_status
439 ,x_msg_count => x_msg_count
440 ,x_msg_data => x_msg_data
441 );
442 -- This Procedure really does not applied
443 --weighted_measure_id
444 --dependent_measure_id
445 -- WHO COLUMNS
446 -- l_Bis_WM_Rec.Last_Update_Date := sysdate;
447 -- l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
448
449 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Dependency(
450 p_commit => p_commit
451 ,p_Bis_WM_Rec => l_Bis_WM_Rec
452 ,x_Bis_WM_Rec => x_Bis_WM_Rec
453 ,x_return_status => x_return_status
454 ,x_msg_count => x_msg_count
455 ,x_msg_data => x_msg_data
456 );
457
458 EXCEPTION
459 WHEN FND_API.G_EXC_ERROR THEN
460 ROLLBACK TO bis_Update_WM_Dependency_pub;
461 IF (x_msg_data IS NULL) THEN
462 FND_MSG_PUB.Count_And_Get
463 ( p_encoded => FND_API.G_FALSE
464 , p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467 END IF;
468 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 raise;
471 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
472 ROLLBACK TO bis_Update_WM_Dependency_pub;
473 IF (x_msg_data IS NULL) THEN
474 FND_MSG_PUB.Count_And_Get
475 ( p_encoded => FND_API.G_FALSE
476 , p_count => x_msg_count
477 , p_data => x_msg_data
478 );
479 END IF;
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
482 raise;
483 WHEN OTHERS THEN
484 ROLLBACK TO bis_Update_WM_Dependency_pub;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 IF (x_msg_data IS NOT NULL) THEN
487 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Dependency ';
488 ELSE
489 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Dependency ';
490 END IF;
491 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
492 raise;
493 END Update_WM_Dependency;
494
495 /*******************************************************************
496 *******************************************************************/
497 PROCEDURE Delete_WM_Dependency(
498 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
499 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
500 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
501 ,x_return_status OUT NOCOPY VARCHAR2
502 ,x_msg_count OUT NOCOPY NUMBER
503 ,x_msg_data OUT NOCOPY VARCHAR2
504 ) IS
505 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
506 l_measure_name BIS_DISPLAY_MEASURES_V.name%TYPE;
507
508 CURSOR c_measure_name IS
509 SELECT V.name
510 FROM bis_display_measures_v V,
511 bis_indicators B
512 WHERE B.short_name =V.short_name
513 AND B.indicator_id = p_Bis_WM_Rec.dependent_measure_id;
514
515 BEGIN
516 SAVEPOINT Delete_WM_Dependency_pub;
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 -- Ckeck that Dependent_Measure_id has not Weights Defined
520
521 IF get_Defined_Weights_Status( p_Bis_WM_Rec.weighted_measure_id
522 ,p_Bis_WM_Rec.dependent_measure_id ) = BIS_WEIGHTED_MEASURE_PUB.G_POSITIVE_WEIGHTS THEN
523
524 IF(c_measure_name%ISOPEN)THEN
525 CLOSE c_measure_name;
526 END IF;
527
528 OPEN c_measure_name;
529 FETCH c_measure_name INTO l_measure_name;
530 CLOSE c_measure_name;
531
532 FND_MESSAGE.SET_NAME('BIS','BIS_REMOVE_VALID_WKPI');
533 FND_MESSAGE.SET_TOKEN('MEASURE_NAME', l_measure_name);
534 FND_MSG_PUB.ADD;
535 RAISE FND_API.G_EXC_ERROR;
536 END IF;
537
538 -- Delete the Dependent Measure
539 BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Dependency(
540 p_commit => p_commit
541 ,p_Bis_WM_Rec => p_Bis_WM_Rec
542 ,x_Bis_WM_Rec => x_Bis_WM_Rec
543 ,x_return_status => x_return_status
544 ,x_msg_count => x_msg_count
545 ,x_msg_data => x_msg_data
546 );
547
548 EXCEPTION
549 WHEN FND_API.G_EXC_ERROR THEN
550 IF(c_measure_name%ISOPEN)THEN
551 CLOSE c_measure_name;
552 END IF;
553
554 ROLLBACK TO Delete_WM_Dependency_pub;
555 IF (x_msg_data IS NULL) THEN
556 FND_MSG_PUB.Count_And_Get
557 ( p_encoded => FND_API.G_FALSE
558 , p_count => x_msg_count
559 , p_data => x_msg_data
560 );
561 END IF;
562 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 raise;
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 IF(c_measure_name%ISOPEN)THEN
567 CLOSE c_measure_name;
568 END IF;
569
570 ROLLBACK TO Delete_WM_Dependency_pub;
571 IF (x_msg_data IS NULL) THEN
572 FND_MSG_PUB.Count_And_Get
573 ( p_encoded => FND_API.G_FALSE
574 , p_count => x_msg_count
575 , p_data => x_msg_data
576 );
577 END IF;
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
579 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
580 raise;
581 WHEN OTHERS THEN
582 IF(c_measure_name%ISOPEN)THEN
583 CLOSE c_measure_name;
584 END IF;
585
586 ROLLBACK TO Delete_WM_Dependency_pub;
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF (x_msg_data IS NOT NULL) THEN
589 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency ';
590 ELSE
591 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency ';
592 END IF;
593 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
594 raise;
595 END Delete_WM_Dependency;
596
597 ------- APIs for table BIS_WEIGHTED_MEASURE_DEFNS
598
599 /*******************************************************************
600 *******************************************************************/
601
602 PROCEDURE Create_WM_Definition(
603 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
604 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
605 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
606 ,x_return_status OUT NOCOPY VARCHAR2
607 ,x_msg_count OUT NOCOPY NUMBER
608 ,x_msg_data OUT NOCOPY VARCHAR2
609 ) IS
610 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
611 BEGIN
612 SAVEPOINT CreateWMDefinitionPUB;
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614 l_Bis_WM_Rec :=p_Bis_WM_Rec;
615
616 -- l_Bis_WM_Rec.weighted_measure_id
617 IF BIS_WEIGHTED_MEASURE_PVT.validate_measure_id(p_Bis_WM_Rec.weighted_measure_id) = FND_API.G_FALSE THEN
618 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_W_MEASURE_ID');
619 FND_MESSAGE.SET_TOKEN('MEASURE', p_Bis_WM_Rec.weighted_measure_id);
620 FND_MSG_PUB.ADD;
621 RAISE FND_API.G_EXC_ERROR;
622 end if;
623
624 -- l_Bis_WM_Rec.VIEWBY_dimension_SN
625 -- l_Bis_WM_Rec.VIEWBY_dim_level_SN
626 -- l_Bis_WM_Rec.FILTER_dimension_SN
627 -- l_Bis_WM_Rec.FILTER_dim_level_SN
628 -- l_Bis_WM_Rec.time_dimension_short_name
629 -- l_Bis_WM_Rec.time_dim_level_short_name
630
631 -- Set the weighted_definition_id
632 IF l_Bis_WM_Rec.weighted_definition_id IS NULL THEN
633 SELECT BIS_WEIGHTED_MEASURE_DEFNS_S.NEXTVAL
634 INTO l_Bis_WM_Rec.weighted_definition_id
635 FROM DUAL;
636 END IF;
637
638 -- WHO COLUMNS
639 l_Bis_WM_Rec.Creation_Date := sysdate;
640 l_Bis_WM_Rec.Created_By := FND_GLOBAL.USER_ID;
641 l_Bis_WM_Rec.Last_Update_Date := sysdate;
642 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
643 IF l_Bis_WM_Rec.Last_Update_Login IS NULL THEN
644 l_Bis_WM_Rec.Last_Update_Login := 0;
645 END IF;
646
647 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.weighted_measure_id = ' || l_Bis_WM_Rec.weighted_measure_id);
648
649 -- Create Definition
650 BIS_WEIGHTED_MEASURE_PVT.Create_WM_Definition(
651 p_commit => p_commit
652 ,p_Bis_WM_Rec => l_Bis_WM_Rec
653 ,x_Bis_WM_Rec => x_Bis_WM_Rec
654 ,x_return_status => x_return_status
655 ,x_msg_count => x_msg_count
656 ,x_msg_data => x_msg_data
657 );
658
659 -- Create tbe Default Paramerer for the definition
660 l_Bis_WM_Rec.weighted_definition_id := x_Bis_WM_Rec.weighted_definition_id;
661 l_Bis_WM_Rec.time_level_value_id := BIS_WEIGHTED_MEASURE_PUB.DEFAULT_TIME_LEVEL_VALUE;
662 l_Bis_WM_Rec.filter_level_value_id := BIS_WEIGHTED_MEASURE_PUB.DEFAULT_FILTER_LEVEL_VALUE;
663 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter(
664 p_commit => p_commit
665 ,p_Bis_WM_Rec => l_Bis_WM_Rec
666 ,x_Bis_WM_Rec => x_Bis_WM_Rec
667 ,x_return_status => x_return_status
668 ,x_msg_count => x_msg_count
669 ,x_msg_data => x_msg_data
670 );
671
672 EXCEPTION
673 WHEN FND_API.G_EXC_ERROR THEN
674 ROLLBACK TO CreateWMDefinitionPUB;
675 IF (x_msg_data IS NULL) THEN
676 FND_MSG_PUB.Count_And_Get
677 ( p_encoded => FND_API.G_FALSE
678 , p_count => x_msg_count
679 , p_data => x_msg_data
680 );
681 END IF;
682 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 raise;
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 ROLLBACK TO CreateWMDefinitionPUB;
687 IF (x_msg_data IS NULL) THEN
688 FND_MSG_PUB.Count_And_Get
689 ( p_encoded => FND_API.G_FALSE
690 , p_count => x_msg_count
691 , p_data => x_msg_data
692 );
693 END IF;
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
696 raise;
697 WHEN OTHERS THEN
698 ROLLBACK TO CreateWMDefinitionPUB;
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 IF (x_msg_data IS NOT NULL) THEN
701 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition ';
702 ELSE
703 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition ';
704 END IF;
705 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
706 raise;
707 END Create_WM_Definition;
708
709 /*******************************************************************
710 *******************************************************************/
711 PROCEDURE Retrieve_WM_Definition(
712 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
713 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
714 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
715 ,x_return_status OUT NOCOPY VARCHAR2
716 ,x_msg_count OUT NOCOPY NUMBER
717 ,x_msg_data OUT NOCOPY VARCHAR2
718 ) IS
719 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
720 BEGIN
721 SAVEPOINT Retrieve_WM_Definition_pub;
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Definition(
725 p_commit => p_commit
726 ,p_Bis_WM_Rec => p_Bis_WM_Rec
727 ,x_Bis_WM_Rec => x_Bis_WM_Rec
728 ,x_return_status => x_return_status
729 ,x_msg_count => x_msg_count
730 ,x_msg_data => x_msg_data
731 );
732
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 ROLLBACK TO Retrieve_WM_Definition_pub;
736 IF (x_msg_data IS NULL) THEN
737 FND_MSG_PUB.Count_And_Get
738 ( p_encoded => FND_API.G_FALSE
739 , p_count => x_msg_count
740 , p_data => x_msg_data
741 );
742 END IF;
743 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 raise;
746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747 ROLLBACK TO Retrieve_WM_Definition_pub;
748 IF (x_msg_data IS NULL) THEN
749 FND_MSG_PUB.Count_And_Get
750 ( p_encoded => FND_API.G_FALSE
751 , p_count => x_msg_count
752 , p_data => x_msg_data
753 );
754 END IF;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 raise;
757 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
758 WHEN OTHERS THEN
759 ROLLBACK TO Retrieve_WM_Definition_pub;
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761 IF (x_msg_data IS NOT NULL) THEN
762 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition ';
763 ELSE
764 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition ';
765 END IF;
766 raise;
767 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
768 END Retrieve_WM_Definition;
769
770 /*******************************************************************
771 *******************************************************************/
772 PROCEDURE Update_WM_Definition(
773 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
774 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
775 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
776 ,x_return_status OUT NOCOPY VARCHAR2
777 ,x_msg_count OUT NOCOPY NUMBER
778 ,x_msg_data OUT NOCOPY VARCHAR2
779 ) IS
780 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
781 l_cascade_delete_flag boolean ;
782
783 BEGIN
784 SAVEPOINT bis_Update_WM_Definition_pub;
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786 l_cascade_delete_flag := FALSE;
787
788 -- l_Bis_WM_Rec.weighted_measure_id
789 IF BIS_WEIGHTED_MEASURE_PVT.validate_measure_id(p_Bis_WM_Rec.weighted_measure_id) = FND_API.G_FALSE THEN
790 FND_MESSAGE.SET_NAME('BIS','BIS_INVALIDE_WEIGHTED_MEASURE');
791 FND_MESSAGE.SET_TOKEN('MEASURE_ID', p_Bis_WM_Rec.weighted_measure_id);
792 FND_MSG_PUB.ADD;
793 RAISE FND_API.G_EXC_ERROR;
794 end if;
795
796 BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition(
797 p_commit => p_commit
798 ,p_Bis_WM_Rec => p_Bis_WM_Rec
799 ,x_Bis_WM_Rec => l_Bis_WM_Rec
800 ,x_return_status => x_return_status
801 ,x_msg_count => x_msg_count
802 ,x_msg_data => x_msg_data
803 );
804
805 -- l_Bis_WM_Rec.VIEWBY_dimension_SN
806 -- l_Bis_WM_Rec.VIEWBY_dim_level_SN
807 -- l_Bis_WM_Rec.FILTER_dimension_SN
808 -- l_Bis_WM_Rec.FILTER_dim_level_SN
809 -- l_Bis_WM_Rec.time_dimension_short_name
810 -- l_Bis_WM_Rec.time_dim_level_short_name
811
812 IF p_Bis_WM_Rec.VIEWBY_dimension_SN IS NOT NULL THEN
813 l_Bis_WM_Rec.VIEWBY_dimension_SN := p_Bis_WM_Rec.VIEWBY_dimension_SN;
814 END IF;
815 IF p_Bis_WM_Rec.VIEWBY_dim_level_SN IS NOT NULL THEN
816 l_Bis_WM_Rec.VIEWBY_dim_level_SN := p_Bis_WM_Rec.VIEWBY_dim_level_SN;
817 END IF;
818 IF p_Bis_WM_Rec.FILTER_dimension_SN IS NOT NULL THEN
819 IF l_Bis_WM_Rec.FILTER_dimension_SN <> p_Bis_WM_Rec.FILTER_dimension_SN THEN
820 l_cascade_delete_flag := TRUE;
821 l_Bis_WM_Rec.FILTER_dimension_SN := p_Bis_WM_Rec.FILTER_dimension_SN;
822 END IF;
823 END IF;
824 IF p_Bis_WM_Rec.FILTER_dim_level_SN IS NOT NULL THEN
825 IF l_Bis_WM_Rec.FILTER_dim_level_SN <> p_Bis_WM_Rec.FILTER_dim_level_SN THEN
826 l_cascade_delete_flag := TRUE;
827 l_Bis_WM_Rec.FILTER_dim_level_SN := p_Bis_WM_Rec.FILTER_dim_level_SN;
828 END IF;
829 END IF;
830 IF p_Bis_WM_Rec.time_dimension_short_name IS NOT NULL THEN
831 IF l_Bis_WM_Rec.time_dimension_short_name <> p_Bis_WM_Rec.time_dimension_short_name THEN
832 l_cascade_delete_flag := TRUE;
833 l_Bis_WM_Rec.time_dimension_short_name := p_Bis_WM_Rec.time_dimension_short_name;
834 END IF;
835 END IF;
836 IF p_Bis_WM_Rec.time_dim_level_short_name IS NOT NULL THEN
837 IF l_Bis_WM_Rec.time_dim_level_short_name <>p_Bis_WM_Rec.time_dim_level_short_name THEN
838 l_cascade_delete_flag := TRUE;
839 l_Bis_WM_Rec.time_dim_level_short_name := p_Bis_WM_Rec.time_dim_level_short_name;
840 END IF;
841 END IF;
842
843 -- WHO COLUMNS
844 l_Bis_WM_Rec.Last_Update_Date := sysdate;
845 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
846
847 -- execute Cascding Delete for Weighted Measure Parameter
848 IF l_cascade_delete_flag = TRUE THEN
849 Delete_Cascade_WM_Parameters(
850 p_commit => p_commit
851 ,p_weighted_definition_id => l_Bis_WM_Rec.weighted_definition_id
852 ,x_return_status => x_return_status
853 ,x_msg_count => x_msg_count
854 ,x_msg_data => x_msg_data
855 );
856 END IF;
857
858 -- Delete the Weighted Measure Definition
859 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Definition(
860 p_commit => p_commit
861 ,p_Bis_WM_Rec => l_Bis_WM_Rec
862 ,x_Bis_WM_Rec => x_Bis_WM_Rec
863 ,x_return_status => x_return_status
864 ,x_msg_count => x_msg_count
865 ,x_msg_data => x_msg_data
866 );
867
868 IF l_cascade_delete_flag = TRUE THEN
869 -- Create tbe Default Parameter Again
870 l_Bis_WM_Rec.time_level_value_id := BIS_WEIGHTED_MEASURE_PUB.DEFAULT_TIME_LEVEL_VALUE;
871 l_Bis_WM_Rec.filter_level_value_id := BIS_WEIGHTED_MEASURE_PUB.DEFAULT_FILTER_LEVEL_VALUE;
872 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter(
873 p_commit => p_commit
874 ,p_Bis_WM_Rec => l_Bis_WM_Rec
875 ,x_Bis_WM_Rec => x_Bis_WM_Rec
876 ,x_return_status => x_return_status
877 ,x_msg_count => x_msg_count
878 ,x_msg_data => x_msg_data
879 );
880 END IF;
881
882 EXCEPTION
883 WHEN FND_API.G_EXC_ERROR THEN
884 ROLLBACK TO bis_Update_WM_Definition_pub;
885 IF (x_msg_data IS NULL) THEN
886 FND_MSG_PUB.Count_And_Get
887 ( p_encoded => FND_API.G_FALSE
888 , p_count => x_msg_count
889 , p_data => x_msg_data
890 );
891 END IF;
892 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 raise;
895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 ROLLBACK TO bis_Update_WM_Definition_pub;
897 IF (x_msg_data IS NULL) THEN
898 FND_MSG_PUB.Count_And_Get
899 ( p_encoded => FND_API.G_FALSE
900 , p_count => x_msg_count
901 , p_data => x_msg_data
902 );
903 END IF;
904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
905 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
906 raise;
907 WHEN OTHERS THEN
908 ROLLBACK TO bis_Update_WM_Definition_pub;
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 IF (x_msg_data IS NOT NULL) THEN
911 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Definition ';
912 ELSE
913 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Definition ';
914 END IF;
915 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
916 raise;
917 END Update_WM_Definition;
918
919 /*******************************************************************
920 *******************************************************************/
921 PROCEDURE Delete_WM_Definition(
922 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
923 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
924 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
925 ,x_return_status OUT NOCOPY VARCHAR2
926 ,x_msg_count OUT NOCOPY NUMBER
927 ,x_msg_data OUT NOCOPY VARCHAR2
928 ) IS
929 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
930
931 BEGIN
932 SAVEPOINT Delete_WM_Definition_pub;
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934
935 -- Weights Cascading delete when a Parameter is deleted
936 Delete_Cascade_WM_Parameters(
937 p_commit => p_commit
938 ,p_weighted_definition_id => p_Bis_WM_Rec.weighted_definition_id
939 ,x_return_status => x_return_status
940 ,x_msg_count => x_msg_count
941 ,x_msg_data => x_msg_data
942 );
943
944 -- Delete the Weighted Mesure Definition
945 BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Definition(
946 p_commit => p_commit
947 ,p_Bis_WM_Rec => p_Bis_WM_Rec
948 ,x_Bis_WM_Rec => x_Bis_WM_Rec
949 ,x_return_status => x_return_status
950 ,x_msg_count => x_msg_count
951 ,x_msg_data => x_msg_data
952 );
953
954 EXCEPTION
955 WHEN FND_API.G_EXC_ERROR THEN
956 ROLLBACK TO Delete_WM_Definition_pub;
957 IF (x_msg_data IS NULL) THEN
958 FND_MSG_PUB.Count_And_Get
959 ( p_encoded => FND_API.G_FALSE
960 , p_count => x_msg_count
961 , p_data => x_msg_data
962 );
963 END IF;
964 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 raise;
967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
968 ROLLBACK TO Delete_WM_Definition_pub;
969 IF (x_msg_data IS NULL) THEN
970 FND_MSG_PUB.Count_And_Get
971 ( p_encoded => FND_API.G_FALSE
972 , p_count => x_msg_count
973 , p_data => x_msg_data
974 );
975 END IF;
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
977 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
978 raise;
979 WHEN OTHERS THEN
980 ROLLBACK TO Delete_WM_Definition_pub;
981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982 IF (x_msg_data IS NOT NULL) THEN
983 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition ';
984 ELSE
985 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition ';
986 END IF;
987 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
988 raise;
989 END Delete_WM_Definition;
990
991 ------- APIs for table BIS_WEIGHTED_MEASURE_PARAMS
992
993 /*******************************************************************
994 *******************************************************************/
995 PROCEDURE Create_WM_Parameter(
996 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
997 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
998 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
999 ,x_return_status OUT NOCOPY VARCHAR2
1000 ,x_msg_count OUT NOCOPY NUMBER
1001 ,x_msg_data OUT NOCOPY VARCHAR2
1002 ) IS
1003 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1004 l_count NUMBER;
1005 BEGIN
1006 SAVEPOINT CreateWMParameterPUB;
1007 x_return_status := FND_API.G_RET_STS_SUCCESS;
1008 l_Bis_WM_Rec := p_Bis_WM_Rec;
1009
1010 -- weighted_parameter_id
1011 -- weighted_definition_id
1012 -- time_level_value_id
1013 -- filter_level_value_id
1014
1015 SELECT COUNT(1)
1016 INTO l_count
1017 FROM BIS_WEIGHTED_MEASURE_DEFNS
1018 WHERE WEIGHTED_DEFINITION_ID = p_Bis_WM_Rec.weighted_definition_id;
1019 IF l_count = 0 THEN
1020 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_DEF');
1021 FND_MESSAGE.SET_TOKEN('DEF', p_Bis_WM_Rec.weighted_measure_id);
1022 FND_MSG_PUB.ADD;
1023 RAISE FND_API.G_EXC_ERROR;
1024 END IF;
1025
1026 -- Set the weighted_parameter_id
1027 IF p_Bis_WM_Rec.weighted_parameter_id IS NULL THEN
1028 SELECT BIS_WEIGHTED_MEASURE_PARAMS_S.NEXTVAL
1029 INTO l_Bis_WM_Rec.weighted_parameter_id
1030 FROM DUAL;
1031 END IF;
1032
1033 -- WHO COLUMNS
1034 l_Bis_WM_Rec.Creation_Date := sysdate;
1035 l_Bis_WM_Rec.Created_By := FND_GLOBAL.USER_ID;
1036 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1037 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1038 IF l_Bis_WM_Rec.Last_Update_Login IS NULL THEN
1039 l_Bis_WM_Rec.Last_Update_Login := 0;
1040 END IF;
1041
1042 BIS_WEIGHTED_MEASURE_PVT.Create_WM_Parameter(
1043 p_commit => p_commit
1044 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1045 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1046 ,x_return_status => x_return_status
1047 ,x_msg_count => x_msg_count
1048 ,x_msg_data => x_msg_data
1049 );
1050
1051 EXCEPTION
1052 WHEN FND_API.G_EXC_ERROR THEN
1053 ROLLBACK TO CreateWMParameterPUB;
1054 IF (x_msg_data IS NULL) THEN
1055 FND_MSG_PUB.Count_And_Get
1056 ( p_encoded => FND_API.G_FALSE
1057 , p_count => x_msg_count
1058 , p_data => x_msg_data
1059 );
1060 END IF;
1061 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1062 x_return_status := FND_API.G_RET_STS_ERROR;
1063 raise;
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 ROLLBACK TO CreateWMParameterPUB;
1066 IF (x_msg_data IS NULL) THEN
1067 FND_MSG_PUB.Count_And_Get
1068 ( p_encoded => FND_API.G_FALSE
1069 , p_count => x_msg_count
1070 , p_data => x_msg_data
1071 );
1072 END IF;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1075 raise;
1076 WHEN OTHERS THEN
1077 ROLLBACK TO CreateWMParameterPUB;
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 IF (x_msg_data IS NOT NULL) THEN
1080 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter ';
1081 ELSE
1082 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter ';
1083 END IF;
1084 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1085 raise;
1086 END Create_WM_Parameter;
1087
1088 /*******************************************************************
1089 *******************************************************************/
1090 PROCEDURE Retrieve_WM_Parameter(
1091 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1092 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1093 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1094 ,x_return_status OUT NOCOPY VARCHAR2
1095 ,x_msg_count OUT NOCOPY NUMBER
1096 ,x_msg_data OUT NOCOPY VARCHAR2
1097 ) IS
1098 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1099 BEGIN
1100 SAVEPOINT Retrieve_WM_Parameter_pub;
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102 BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Parameter(
1103 p_commit => p_commit
1104 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1105 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1106 ,x_return_status => x_return_status
1107 ,x_msg_count => x_msg_count
1108 ,x_msg_data => x_msg_data
1109 );
1110
1111 EXCEPTION
1112 WHEN FND_API.G_EXC_ERROR THEN
1113 ROLLBACK TO Retrieve_WM_Parameter_pub;
1114 IF (x_msg_data IS NULL) THEN
1115 FND_MSG_PUB.Count_And_Get
1116 ( p_encoded => FND_API.G_FALSE
1117 , p_count => x_msg_count
1118 , p_data => x_msg_data
1119 );
1120 END IF;
1121 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 raise;
1124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1125 ROLLBACK TO Retrieve_WM_Parameter_pub;
1126 IF (x_msg_data IS NULL) THEN
1127 FND_MSG_PUB.Count_And_Get
1128 ( p_encoded => FND_API.G_FALSE
1129 , p_count => x_msg_count
1130 , p_data => x_msg_data
1131 );
1132 END IF;
1133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 raise;
1135 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1136 WHEN OTHERS THEN
1137 ROLLBACK TO Retrieve_WM_Parameter_pub;
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139 IF (x_msg_data IS NOT NULL) THEN
1140 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Parameter ';
1141 ELSE
1142 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Parameter ';
1143 END IF;
1144 raise;
1145 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1146 END Retrieve_WM_Parameter;
1147
1148 /*******************************************************************
1149 *******************************************************************/
1150 PROCEDURE Update_WM_Parameter(
1151 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1152 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1153 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1154 ,x_return_status OUT NOCOPY VARCHAR2
1155 ,x_msg_count OUT NOCOPY NUMBER
1156 ,x_msg_data OUT NOCOPY VARCHAR2
1157 ) IS
1158 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1159 BEGIN
1160 SAVEPOINT bis_Update_WM_Parameter_pub;
1161 x_return_status := FND_API.G_RET_STS_SUCCESS;
1162 l_Bis_WM_Rec :=p_Bis_WM_Rec;
1163
1164 -- weighted_parameter_id
1165 -- weighted_definition_id
1166 -- time_level_value_id
1167 -- filter_level_value_id
1168
1169 IF p_Bis_WM_Rec.weighted_definition_id IS NOT NULL THEN
1170 l_Bis_WM_Rec.weighted_definition_id := p_Bis_WM_Rec.weighted_definition_id;
1171 END IF;
1172 IF p_Bis_WM_Rec.time_level_value_id IS NOT NULL THEN
1173 l_Bis_WM_Rec.time_level_value_id := p_Bis_WM_Rec.time_level_value_id;
1174 END IF;
1175 IF p_Bis_WM_Rec.filter_level_value_id IS NOT NULL THEN
1176 l_Bis_WM_Rec.filter_level_value_id := p_Bis_WM_Rec.filter_level_value_id;
1177 END IF;
1178
1179 -- WHO COLUMNS
1180 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1181 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1182
1183 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Parameter(
1184 p_commit => p_commit
1185 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1186 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1187 ,x_return_status => x_return_status
1188 ,x_msg_count => x_msg_count
1189 ,x_msg_data => x_msg_data
1190 );
1191
1192 EXCEPTION
1193 WHEN FND_API.G_EXC_ERROR THEN
1194 ROLLBACK TO bis_Update_WM_Parameter_pub;
1195 IF (x_msg_data IS NULL) THEN
1196 FND_MSG_PUB.Count_And_Get
1197 ( p_encoded => FND_API.G_FALSE
1198 , p_count => x_msg_count
1199 , p_data => x_msg_data
1200 );
1201 END IF;
1202 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204 raise;
1205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1206 ROLLBACK TO bis_Update_WM_Parameter_pub;
1207 IF (x_msg_data IS NULL) THEN
1208 FND_MSG_PUB.Count_And_Get
1209 ( p_encoded => FND_API.G_FALSE
1210 , p_count => x_msg_count
1211 , p_data => x_msg_data
1212 );
1213 END IF;
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1216 raise;
1217 WHEN OTHERS THEN
1218 ROLLBACK TO bis_Update_WM_Parameter_pub;
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1220 IF (x_msg_data IS NOT NULL) THEN
1221 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Parameter ';
1222 ELSE
1223 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Parameter ';
1224 END IF;
1225 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1226 raise;
1227 END Update_WM_Parameter;
1228
1229 /*******************************************************************
1230 *******************************************************************/
1231 PROCEDURE Delete_WM_Parameter(
1232 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1233 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1234 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1235 ,x_return_status OUT NOCOPY VARCHAR2
1236 ,x_msg_count OUT NOCOPY NUMBER
1237 ,x_msg_data OUT NOCOPY VARCHAR2
1238 ) IS
1239
1240 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1241
1242 -- Cursor to get the Weights associated to the Parameter
1243 CURSOR c_Weights IS
1244 SELECT WEIGHT_ID
1245 FROM BIS_WEIGHTED_MEASURE_WEIGHTS
1246 WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
1247 BEGIN
1248 SAVEPOINT Delete_WM_Parameter_pub;
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251 -- Weights Cascading delete when a Parameter is deleted
1252 FOR CD IN c_Weights LOOP
1253 l_Bis_WM_Rec.weight_id := CD.WEIGHT_ID;
1254 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight(
1255 p_commit => p_commit
1256 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1257 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1258 ,x_return_status => x_return_status
1259 ,x_msg_count => x_msg_count
1260 ,x_msg_data => x_msg_data
1261 );
1262 END LOOP;
1263
1264 -- Delete the Parameter
1265 BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Parameter(
1266 p_commit => p_commit
1267 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1268 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1269 ,x_return_status => x_return_status
1270 ,x_msg_count => x_msg_count
1271 ,x_msg_data => x_msg_data
1272 );
1273
1274 EXCEPTION
1275 WHEN FND_API.G_EXC_ERROR THEN
1276 ROLLBACK TO Delete_WM_Parameter_pub;
1277 IF (x_msg_data IS NULL) THEN
1278 FND_MSG_PUB.Count_And_Get
1279 ( p_encoded => FND_API.G_FALSE
1280 , p_count => x_msg_count
1281 , p_data => x_msg_data
1282 );
1283 END IF;
1284 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1285 x_return_status := FND_API.G_RET_STS_ERROR;
1286 raise;
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288 ROLLBACK TO Delete_WM_Parameter_pub;
1289 IF (x_msg_data IS NULL) THEN
1290 FND_MSG_PUB.Count_And_Get
1291 ( p_encoded => FND_API.G_FALSE
1292 , p_count => x_msg_count
1293 , p_data => x_msg_data
1294 );
1295 END IF;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1298 raise;
1299 WHEN OTHERS THEN
1300 ROLLBACK TO Delete_WM_Parameter_pub;
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 IF (x_msg_data IS NOT NULL) THEN
1303 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Parameter ';
1304 ELSE
1305 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Parameter ';
1306 END IF;
1307 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1308 raise;
1309 END Delete_WM_Parameter;
1310
1311 ------- APIs for table BIS_WEIGHTED_MEASURE_WEIGHTS
1312
1313 /*******************************************************************
1314 *******************************************************************/
1315 PROCEDURE Create_WM_Weight(
1316 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1317 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1318 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1319 ,x_return_status OUT NOCOPY VARCHAR2
1320 ,x_msg_count OUT NOCOPY NUMBER
1321 ,x_msg_data OUT NOCOPY VARCHAR2
1322 ) IS
1323 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1324 l_count NUMBER;
1325 BEGIN
1326 SAVEPOINT Create_WM_Weight_PUB;
1327 x_return_status := FND_API.G_RET_STS_SUCCESS;
1328 l_Bis_WM_Rec := p_Bis_WM_Rec;
1329
1330 --weight_id
1331 --weighted_parameter_id
1332 --dependent_measure_id
1333 --weight
1334
1335
1336 SELECT COUNT(1)
1337 INTO l_count
1338 FROM BIS_WEIGHTED_MEASURE_PARAMS
1339 WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
1340 IF l_count = 0 THEN
1341 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_PARAM');
1342 FND_MESSAGE.SET_TOKEN('PARAM', p_Bis_WM_Rec.weighted_parameter_id);
1343 FND_MSG_PUB.ADD;
1344 RAISE FND_API.G_EXC_ERROR;
1345 END IF;
1346
1347
1348 -- Set the weighted_definition_id
1349 IF p_Bis_WM_Rec.weight_id IS NULL THEN
1350 SELECT BIS_WEIGHTED_MEASURE_WEIGHTS_S.NEXTVAL
1351 INTO l_Bis_WM_Rec.weight_id
1352 FROM DUAL;
1353 END IF;
1354
1355 -- WHO COLUMNS
1356 l_Bis_WM_Rec.Creation_Date := sysdate;
1357 l_Bis_WM_Rec.Created_By := FND_GLOBAL.USER_ID;
1358 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1359 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1360 IF l_Bis_WM_Rec.Last_Update_Login IS NULL THEN
1361 l_Bis_WM_Rec.Last_Update_Login := 0;
1362 END IF;
1363
1364
1365 BIS_WEIGHTED_MEASURE_PVT.Create_WM_Weight(
1366 p_commit => p_commit
1367 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1368 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1369 ,x_return_status => x_return_status
1370 ,x_msg_count => x_msg_count
1371 ,x_msg_data => x_msg_data
1372 );
1373
1374 EXCEPTION
1375 WHEN FND_API.G_EXC_ERROR THEN
1376 ROLLBACK TO Create_WM_Weight_PUB;
1377 IF (x_msg_data IS NULL) THEN
1378 FND_MSG_PUB.Count_And_Get
1379 ( p_encoded => FND_API.G_FALSE
1380 , p_count => x_msg_count
1381 , p_data => x_msg_data
1382 );
1383 END IF;
1384 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1385 x_return_status := FND_API.G_RET_STS_ERROR;
1386 raise;
1387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1388 ROLLBACK TO Create_WM_Weight_PUB;
1389 IF (x_msg_data IS NULL) THEN
1390 FND_MSG_PUB.Count_And_Get
1391 ( p_encoded => FND_API.G_FALSE
1392 , p_count => x_msg_count
1393 , p_data => x_msg_data
1394 );
1395 END IF;
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1398 raise;
1399 WHEN OTHERS THEN
1400 ROLLBACK TO Create_WM_Weight_PUB;
1401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1402 IF (x_msg_data IS NOT NULL) THEN
1403 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Weight ';
1404 ELSE
1405 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Weight ';
1406 END IF;
1407 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1408 raise;
1409 END Create_WM_Weight;
1410
1411 /*******************************************************************
1412 *******************************************************************/
1413 PROCEDURE Retrieve_WM_Weight(
1414 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1415 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1416 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1417 ,x_return_status OUT NOCOPY VARCHAR2
1418 ,x_msg_count OUT NOCOPY NUMBER
1419 ,x_msg_data OUT NOCOPY VARCHAR2
1420 ) IS
1421 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1422 BEGIN
1423 SAVEPOINT bis_Retrieve_WM_Weight_pub;
1424 x_return_status := FND_API.G_RET_STS_SUCCESS;
1425 BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Weight(
1426 p_commit => p_commit
1427 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1428 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1429 ,x_return_status => x_return_status
1430 ,x_msg_count => x_msg_count
1431 ,x_msg_data => x_msg_data
1432 );
1433
1434 EXCEPTION
1435 WHEN FND_API.G_EXC_ERROR THEN
1436 ROLLBACK TO bis_Retrieve_WM_Weight_pub;
1437 IF (x_msg_data IS NULL) THEN
1438 FND_MSG_PUB.Count_And_Get
1439 ( p_encoded => FND_API.G_FALSE
1440 , p_count => x_msg_count
1441 , p_data => x_msg_data
1442 );
1443 END IF;
1444 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1445 x_return_status := FND_API.G_RET_STS_ERROR;
1446 raise;
1447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1448 ROLLBACK TO bis_Retrieve_WM_Weight_pub;
1449 IF (x_msg_data IS NULL) THEN
1450 FND_MSG_PUB.Count_And_Get
1451 ( p_encoded => FND_API.G_FALSE
1452 , p_count => x_msg_count
1453 , p_data => x_msg_data
1454 );
1455 END IF;
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1458 raise;
1459 WHEN OTHERS THEN
1460 ROLLBACK TO bis_Retrieve_WM_Weight_pub;
1461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1462 IF (x_msg_data IS NOT NULL) THEN
1463 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Weight ';
1464 ELSE
1465 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Weight ';
1466 END IF;
1467 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1468 raise;
1469 END Retrieve_WM_Weight;
1470
1471 /*******************************************************************
1472 *******************************************************************/
1473 PROCEDURE Update_WM_Weight(
1474 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1475 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1476 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1477 ,x_return_status OUT NOCOPY VARCHAR2
1478 ,x_msg_count OUT NOCOPY NUMBER
1479 ,x_msg_data OUT NOCOPY VARCHAR2
1480 ) IS
1481 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1482 BEGIN
1483
1484 SAVEPOINT bis_Update_WM_Weight_pub;
1485 x_return_status := FND_API.G_RET_STS_SUCCESS;
1486
1487 BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Weight(
1488 p_commit => p_commit
1489 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1490 ,x_Bis_WM_Rec => l_Bis_WM_Rec
1491 ,x_return_status => x_return_status
1492 ,x_msg_count => x_msg_count
1493 ,x_msg_data => x_msg_data
1494 );
1495
1496 -- p_Bis_WM_Rec.weight_id
1497 /* it is not posible to update this to coluns are really primary key
1498 IF p_Bis_WM_Rec.weighted_parameter_id IS NOT NULL THEN
1499 l_Bis_WM_Rec.weighted_parameter_id := p_Bis_WM_Rec.weighted_parameter_id;
1500 END IF;
1501 IF p_Bis_WM_Rec.dependent_measure_id IS NOT NULL THEN
1502 l_Bis_WM_Rec.dependent_measure_id := p_Bis_WM_Rec.dependent_measure_id;
1503 END IF;
1504 */
1505 IF p_Bis_WM_Rec.weight IS NOT NULL THEN
1506 l_Bis_WM_Rec.weight := p_Bis_WM_Rec.weight;
1507 END IF;
1508
1509 -- WHO COLUMNS
1510 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1511 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1512
1513 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Weight(
1514 p_commit => p_commit
1515 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1516 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1517 ,x_return_status => x_return_status
1518 ,x_msg_count => x_msg_count
1519 ,x_msg_data => x_msg_data
1520 );
1521
1522 EXCEPTION
1523 WHEN FND_API.G_EXC_ERROR THEN
1524 ROLLBACK TO bis_Update_WM_Weight_pub;
1525 IF (x_msg_data IS NULL) THEN
1526 FND_MSG_PUB.Count_And_Get
1527 ( p_encoded => FND_API.G_FALSE
1528 , p_count => x_msg_count
1529 , p_data => x_msg_data
1530 );
1531 END IF;
1532 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1533 x_return_status := FND_API.G_RET_STS_ERROR;
1534 raise;
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 ROLLBACK TO bis_Update_WM_Weight_pub;
1537 IF (x_msg_data IS NULL) THEN
1538 FND_MSG_PUB.Count_And_Get
1539 ( p_encoded => FND_API.G_FALSE
1540 , p_count => x_msg_count
1541 , p_data => x_msg_data
1542 );
1543 END IF;
1544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1545 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1546 raise;
1547 WHEN OTHERS THEN
1548 ROLLBACK TO bis_Update_WM_Weight_pub;
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550 IF (x_msg_data IS NOT NULL) THEN
1551 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Weight ';
1552 ELSE
1553 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Weight ';
1554 END IF;
1555 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1556 raise;
1557 END Update_WM_Weight;
1558
1559 /*******************************************************************
1560 *******************************************************************/
1561 PROCEDURE Delete_WM_Weight(
1562 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1563 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1564 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1565 ,x_return_status OUT NOCOPY VARCHAR2
1566 ,x_msg_count OUT NOCOPY NUMBER
1567 ,x_msg_data OUT NOCOPY VARCHAR2
1568 ) IS
1569 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1570 BEGIN
1571 SAVEPOINT Delete_WM_Weight_pub;
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573
1574 -- Cascading delete when a Weight_id is deleted
1575 l_Bis_WM_Rec.weight_id := p_Bis_WM_Rec.weight_id;
1576 l_Bis_WM_Rec.low_range := null;
1577 l_Bis_WM_Rec.high_range := null;
1578
1579 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score(
1580 p_commit => p_commit
1581 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1582 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1583 ,x_return_status => x_return_status
1584 ,x_msg_count => x_msg_count
1585 ,x_msg_data => x_msg_data
1586 );
1587
1588 -- Delete the Weight
1589 BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Weight(
1590 p_commit => p_commit
1591 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1592 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1593 ,x_return_status => x_return_status
1594 ,x_msg_count => x_msg_count
1595 ,x_msg_data => x_msg_data
1596 );
1597
1598
1599 EXCEPTION
1600 WHEN FND_API.G_EXC_ERROR THEN
1601 ROLLBACK TO Delete_WM_Weight_pub;
1602 IF (x_msg_data IS NULL) THEN
1603 FND_MSG_PUB.Count_And_Get
1604 ( p_encoded => FND_API.G_FALSE
1605 , p_count => x_msg_count
1606 , p_data => x_msg_data
1607 );
1608 END IF;
1609 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1610 x_return_status := FND_API.G_RET_STS_ERROR;
1611 raise;
1612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1613 ROLLBACK TO Delete_WM_Weight_pub;
1614 IF (x_msg_data IS NULL) THEN
1615 FND_MSG_PUB.Count_And_Get
1616 ( p_encoded => FND_API.G_FALSE
1617 , p_count => x_msg_count
1618 , p_data => x_msg_data
1619 );
1620 END IF;
1621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1622 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1623 raise;
1624 WHEN OTHERS THEN
1625 ROLLBACK TO Delete_WM_Weight_pub;
1626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1627 IF (x_msg_data IS NOT NULL) THEN
1628 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight ';
1629 ELSE
1630 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight ';
1631 END IF;
1632 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1633 raise;
1634 END Delete_WM_Weight;
1635
1636 ------- APIs for table BIS_WEIGHTED_MEASURE_SCORES
1637 /*******************************************************************
1638 *******************************************************************/
1639 PROCEDURE Create_WM_Score(
1640 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1641 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1642 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1643 ,x_return_status OUT NOCOPY VARCHAR2
1644 ,x_msg_count OUT NOCOPY NUMBER
1645 ,x_msg_data OUT NOCOPY VARCHAR2
1646 ) IS
1647 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1648 l_count NUMBER;
1649 BEGIN
1650 SAVEPOINT Create_WM_Score_PUB;
1651 x_return_status := FND_API.G_RET_STS_SUCCESS;
1652 l_Bis_WM_Rec :=p_Bis_WM_Rec;
1653
1654 SELECT COUNT(1)
1655 INTO l_count
1656 FROM BIS_WEIGHTED_MEASURE_WEIGHTS
1657 WHERE WEIGHT_ID = p_Bis_WM_Rec.weight_id;
1658 IF l_count = 0 THEN
1659 FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_WEIGHT_ID');
1660 FND_MESSAGE.SET_TOKEN('WEIGHT_ID', p_Bis_WM_Rec.weight_id);
1661 FND_MSG_PUB.ADD;
1662 RAISE FND_API.G_EXC_ERROR;
1663 END IF;
1664
1665 -- WHO COLUMNS
1666 l_Bis_WM_Rec.Creation_Date := sysdate;
1667 l_Bis_WM_Rec.Created_By := FND_GLOBAL.USER_ID;
1668 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1669 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1670 IF l_Bis_WM_Rec.Last_Update_Login IS NULL THEN
1671 l_Bis_WM_Rec.Last_Update_Login := 0;
1672 END IF;
1673
1674
1675 BIS_WEIGHTED_MEASURE_PVT.Create_WM_Score(
1676 p_commit => p_commit
1677 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1678 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1679 ,x_return_status => x_return_status
1680 ,x_msg_count => x_msg_count
1681 ,x_msg_data => x_msg_data
1682 );
1683
1684 EXCEPTION
1685 WHEN FND_API.G_EXC_ERROR THEN
1686 ROLLBACK TO Create_WM_Score_PUB;
1687 IF (x_msg_data IS NULL) THEN
1688 FND_MSG_PUB.Count_And_Get
1689 ( p_encoded => FND_API.G_FALSE
1690 , p_count => x_msg_count
1691 , p_data => x_msg_data
1692 );
1693 END IF;
1694 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1695 x_return_status := FND_API.G_RET_STS_ERROR;
1696 raise;
1697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1698 ROLLBACK TO Create_WM_Score_PUB;
1699 IF (x_msg_data IS NULL) THEN
1700 FND_MSG_PUB.Count_And_Get
1701 ( p_encoded => FND_API.G_FALSE
1702 , p_count => x_msg_count
1703 , p_data => x_msg_data
1704 );
1705 END IF;
1706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1707 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1708 raise;
1709 WHEN OTHERS THEN
1710 ROLLBACK TO Create_WM_Score_PUB;
1711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1712 IF (x_msg_data IS NOT NULL) THEN
1713 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Score ';
1714 ELSE
1715 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Score ';
1716 END IF;
1717 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1718 raise;
1719 END Create_WM_Score;
1720
1721 /*******************************************************************
1722 *******************************************************************/
1723 PROCEDURE Retrieve_WM_Score(
1724 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1725 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1726 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1727 ,x_return_status OUT NOCOPY VARCHAR2
1728 ,x_msg_count OUT NOCOPY NUMBER
1729 ,x_msg_data OUT NOCOPY VARCHAR2
1730 ) IS
1731 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1732 BEGIN
1733 SAVEPOINT bis_Retrieve_WM_Score_pub;
1734 x_return_status := FND_API.G_RET_STS_SUCCESS;
1735 BIS_WEIGHTED_MEASURE_PVT.Retrieve_WM_Score(
1736 p_commit => p_commit
1737 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1738 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1739 ,x_return_status => x_return_status
1740 ,x_msg_count => x_msg_count
1741 ,x_msg_data => x_msg_data
1742 );
1743
1744 EXCEPTION
1745 WHEN FND_API.G_EXC_ERROR THEN
1746 ROLLBACK TO bis_Retrieve_WM_Score_pub;
1747 IF (x_msg_data IS NULL) THEN
1748 FND_MSG_PUB.Count_And_Get
1749 ( p_encoded => FND_API.G_FALSE
1750 , p_count => x_msg_count
1751 , p_data => x_msg_data
1752 );
1753 END IF;
1754 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1755 x_return_status := FND_API.G_RET_STS_ERROR;
1756 raise;
1757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1758 ROLLBACK TO bis_Retrieve_WM_Score_pub;
1759 IF (x_msg_data IS NULL) THEN
1760 FND_MSG_PUB.Count_And_Get
1761 ( p_encoded => FND_API.G_FALSE
1762 , p_count => x_msg_count
1763 , p_data => x_msg_data
1764 );
1765 END IF;
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1767 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1768 raise;
1769 WHEN OTHERS THEN
1770 ROLLBACK TO bis_Retrieve_WM_Score_pub;
1771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1772 IF (x_msg_data IS NOT NULL) THEN
1773 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Score ';
1774 ELSE
1775 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Score ';
1776 END IF;
1777 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1778 raise;
1779 END Retrieve_WM_Score;
1780
1781 /*******************************************************************
1782 *******************************************************************/
1783 PROCEDURE Update_WM_Score(
1784 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1785 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1786 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1787 ,x_return_status OUT NOCOPY VARCHAR2
1788 ,x_msg_count OUT NOCOPY NUMBER
1789 ,x_msg_data OUT NOCOPY VARCHAR2
1790 ) IS
1791 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1792 BEGIN
1793 SAVEPOINT bis_Update_WM_Score_pub;
1794 x_return_status := FND_API.G_RET_STS_SUCCESS;
1795 l_Bis_WM_Rec :=p_Bis_WM_Rec;
1796
1797 BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Score(
1798 p_commit => p_commit
1799 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1800 ,x_Bis_WM_Rec => l_Bis_WM_Rec
1801 ,x_return_status => x_return_status
1802 ,x_msg_count => x_msg_count
1803 ,x_msg_data => x_msg_data
1804 );
1805
1806 --weight_id
1807 IF p_Bis_WM_Rec.low_range IS NOT NULL THEN
1808 l_Bis_WM_Rec.low_range := p_Bis_WM_Rec.low_range;
1809 END IF;
1810 IF p_Bis_WM_Rec.high_range IS NOT NULL THEN
1811 l_Bis_WM_Rec.high_range := p_Bis_WM_Rec.high_range;
1812 END IF;
1813 IF p_Bis_WM_Rec.score IS NOT NULL THEN
1814 l_Bis_WM_Rec.score := p_Bis_WM_Rec.score;
1815 END IF;
1816
1817 -- WHO COLUMNS
1818 l_Bis_WM_Rec.Last_Update_Date := sysdate;
1819 l_Bis_WM_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
1820
1821 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Score(
1822 p_commit => p_commit
1823 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1824 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1825 ,x_return_status => x_return_status
1826 ,x_msg_count => x_msg_count
1827 ,x_msg_data => x_msg_data
1828 );
1829
1830
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 ROLLBACK TO bis_Update_WM_Score_pub;
1834 IF (x_msg_data IS NULL) THEN
1835 FND_MSG_PUB.Count_And_Get
1836 ( p_encoded => FND_API.G_FALSE
1837 , p_count => x_msg_count
1838 , p_data => x_msg_data
1839 );
1840 END IF;
1841 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1842 x_return_status := FND_API.G_RET_STS_ERROR;
1843 raise;
1844 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1845 ROLLBACK TO bis_Update_WM_Score_pub;
1846 IF (x_msg_data IS NULL) THEN
1847 FND_MSG_PUB.Count_And_Get
1848 ( p_encoded => FND_API.G_FALSE
1849 , p_count => x_msg_count
1850 , p_data => x_msg_data
1851 );
1852 END IF;
1853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1854 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1855 raise;
1856 WHEN OTHERS THEN
1857 ROLLBACK TO bis_Update_WM_Score_pub;
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859 IF (x_msg_data IS NOT NULL) THEN
1860 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Score ';
1861 ELSE
1862 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Score ';
1863 END IF;
1864 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1865 raise;
1866 END Update_WM_Score;
1867
1868 /*******************************************************************
1869 *******************************************************************/
1870 PROCEDURE Delete_WM_Score(
1871 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1872 ,p_Bis_WM_Rec IN BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1873 ,x_Bis_WM_Rec OUT NOCOPY BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec
1874 ,x_return_status OUT NOCOPY VARCHAR2
1875 ,x_msg_count OUT NOCOPY NUMBER
1876 ,x_msg_data OUT NOCOPY VARCHAR2
1877 ) IS
1878 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1879 BEGIN
1880 SAVEPOINT Delete_WM_Score_pub;
1881 x_return_status := FND_API.G_RET_STS_SUCCESS;
1882 BIS_WEIGHTED_MEASURE_PVT.Delete_WM_Score(
1883 p_commit => p_commit
1884 ,p_Bis_WM_Rec => p_Bis_WM_Rec
1885 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1886 ,x_return_status => x_return_status
1887 ,x_msg_count => x_msg_count
1888 ,x_msg_data => x_msg_data
1889 );
1890
1891 EXCEPTION
1892 WHEN FND_API.G_EXC_ERROR THEN
1893 ROLLBACK TO Delete_WM_Score_pub;
1894 IF (x_msg_data IS NULL) THEN
1895 FND_MSG_PUB.Count_And_Get
1896 ( p_encoded => FND_API.G_FALSE
1897 , p_count => x_msg_count
1898 , p_data => x_msg_data
1899 );
1900 END IF;
1901 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1902 x_return_status := FND_API.G_RET_STS_ERROR;
1903 raise;
1904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1905 ROLLBACK TO Delete_WM_Score_pub;
1906 IF (x_msg_data IS NULL) THEN
1907 FND_MSG_PUB.Count_And_Get
1908 ( p_encoded => FND_API.G_FALSE
1909 , p_count => x_msg_count
1910 , p_data => x_msg_data
1911 );
1912 END IF;
1913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1914 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1915 raise;
1916 WHEN OTHERS THEN
1917 ROLLBACK TO Delete_WM_Score_pub;
1918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1919 IF (x_msg_data IS NOT NULL) THEN
1920 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score ';
1921 ELSE
1922 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score ';
1923 END IF;
1924 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1925 raise;
1926 END Delete_WM_Score;
1927 /* ------------------------------------------------------------------------
1928
1929 ********* WRAPPER APIS FOR RECORD APIS **************************
1930
1931 --------------------------------------------------------------------------*/
1932
1933 PROCEDURE Create_WM_Definition(
1934 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1935 ,p_weighted_definition_id IN NUMBER
1936 ,p_weighted_measure_id IN NUMBER
1937 ,p_viewby_dimension_sn IN VARCHAR2
1938 ,p_viewby_dim_level_sn IN VARCHAR2
1939 ,p_filter_dimension_sn IN VARCHAR2
1940 ,p_filter_dim_level_sn IN VARCHAR2
1941 ,p_time_dimension_sn IN VARCHAR2
1942 ,p_time_dim_level_sn IN VARCHAR2
1943 ,x_weighted_definition_id OUT NOCOPY NUMBER
1944 ,x_return_status OUT NOCOPY VARCHAR2
1945 ,x_msg_count OUT NOCOPY NUMBER
1946 ,x_msg_data OUT NOCOPY VARCHAR2
1947 ) IS
1948 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1949 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
1950
1951 BEGIN
1952 -- THIS IS JUST A WRAPPER PLEASE DO NOT ADD ANY BUSINESS LOGIC TO THIS PROCEDURE
1953 x_return_status := FND_API.G_RET_STS_SUCCESS;
1954
1955 l_Bis_WM_Rec.weighted_definition_id := p_weighted_definition_id;
1956 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
1957 l_Bis_WM_Rec.viewby_dimension_sn := p_viewby_dimension_sn;
1958 l_Bis_WM_Rec.viewby_dim_level_sn := p_viewby_dim_level_sn;
1959 l_Bis_WM_Rec.filter_dimension_sn := p_filter_dimension_sn;
1960 l_Bis_WM_Rec.filter_dim_level_sn := p_filter_dim_level_sn;
1961 l_Bis_WM_Rec.time_dimension_short_name := p_time_dimension_sn;
1962 l_Bis_WM_Rec.time_dim_level_short_name := p_time_dim_level_sn;
1963
1964 -- Create Definition
1965 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition(
1966 p_commit => p_commit
1967 ,p_Bis_WM_Rec => l_Bis_WM_Rec
1968 ,x_Bis_WM_Rec => x_Bis_WM_Rec
1969 ,x_return_status => x_return_status
1970 ,x_msg_count => x_msg_count
1971 ,x_msg_data => x_msg_data
1972 );
1973 x_weighted_definition_id := x_Bis_WM_Rec.weighted_definition_id;
1974
1975 EXCEPTION
1976 WHEN FND_API.G_EXC_ERROR THEN
1977 IF (x_msg_data IS NULL) THEN
1978 FND_MSG_PUB.Count_And_Get
1979 ( p_encoded => FND_API.G_FALSE
1980 , p_count => x_msg_count
1981 , p_data => x_msg_data
1982 );
1983 END IF;
1984 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1985 x_return_status := FND_API.G_RET_STS_ERROR;
1986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1987 IF (x_msg_data IS NULL) THEN
1988 FND_MSG_PUB.Count_And_Get
1989 ( p_encoded => FND_API.G_FALSE
1990 , p_count => x_msg_count
1991 , p_data => x_msg_data
1992 );
1993 END IF;
1994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1995 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1996 WHEN OTHERS THEN
1997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1998 IF (x_msg_data IS NOT NULL) THEN
1999 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition ';
2000 ELSE
2001 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition ';
2002 END IF;
2003 END Create_WM_Definition;
2004
2005 /*******************************************************************
2006 *******************************************************************/
2007 PROCEDURE Retrieve_WM_Definition(
2008 p_weighted_definition_id IN NUMBER
2009 ,x_weighted_measure_id OUT NOCOPY NUMBER
2010 ,x_viewby_dimension_sn OUT NOCOPY VARCHAR2
2011 ,x_viewby_dim_level_sn OUT NOCOPY VARCHAR2
2012 ,x_filter_dimension_sn OUT NOCOPY VARCHAR2
2013 ,x_filter_dim_level_sn OUT NOCOPY VARCHAR2
2014 ,x_time_dimension_sn OUT NOCOPY VARCHAR2
2015 ,x_time_dim_level_sn OUT NOCOPY VARCHAR2
2016 ,x_return_status OUT NOCOPY VARCHAR2
2017 ,x_msg_count OUT NOCOPY NUMBER
2018 ,x_msg_data OUT NOCOPY VARCHAR2
2019 ) IS
2020 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2021 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2022 BEGIN
2023 x_return_status := FND_API.G_RET_STS_SUCCESS;
2024
2025 l_Bis_WM_Rec.weighted_definition_id := p_weighted_definition_id;
2026
2027 BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition(
2028 p_commit => NULL
2029 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2030 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2031 ,x_return_status => x_return_status
2032 ,x_msg_count => x_msg_count
2033 ,x_msg_data => x_msg_data
2034 );
2035
2036 x_weighted_measure_id := x_Bis_WM_Rec.weighted_measure_id;
2037 x_viewby_dimension_sn := x_Bis_WM_Rec.viewby_dimension_sn;
2038 x_viewby_dim_level_sn := x_Bis_WM_Rec.viewby_dim_level_sn;
2039 x_filter_dimension_sn := x_Bis_WM_Rec.filter_dimension_sn;
2040 x_filter_dim_level_sn := x_Bis_WM_Rec.filter_dim_level_sn;
2041 x_time_dimension_sn := x_Bis_WM_Rec.time_dimension_short_name ;
2042 x_time_dim_level_sn := x_Bis_WM_Rec.time_dim_level_short_name;
2043
2044 EXCEPTION
2045 WHEN FND_API.G_EXC_ERROR THEN
2046 IF (x_msg_data IS NULL) THEN
2047 FND_MSG_PUB.Count_And_Get
2048 ( p_encoded => FND_API.G_FALSE
2049 , p_count => x_msg_count
2050 , p_data => x_msg_data
2051 );
2052 END IF;
2053 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2054 x_return_status := FND_API.G_RET_STS_ERROR;
2055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2056 IF (x_msg_data IS NULL) THEN
2057 FND_MSG_PUB.Count_And_Get
2058 ( p_encoded => FND_API.G_FALSE
2059 , p_count => x_msg_count
2060 , p_data => x_msg_data
2061 );
2062 END IF;
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2065 WHEN OTHERS THEN
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 IF (x_msg_data IS NOT NULL) THEN
2068 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition ';
2069 ELSE
2070 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Retrieve_WM_Definition ';
2071 END IF;
2072 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2073 END Retrieve_WM_Definition;
2074
2075 /*******************************************************************
2076 *******************************************************************/
2077 PROCEDURE Update_WM_Definition(
2078 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2079 ,p_weighted_definition_id IN NUMBER
2080 ,p_weighted_measure_id IN NUMBER
2081 ,p_viewby_dimension_sn IN VARCHAR2
2082 ,p_viewby_dim_level_sn IN VARCHAR2
2083 ,p_filter_dimension_sn IN VARCHAR2
2084 ,p_filter_dim_level_sn IN VARCHAR2
2085 ,p_time_dimension_sn IN VARCHAR2
2086 ,p_time_dim_level_sn IN VARCHAR2
2087 ,x_return_status OUT NOCOPY VARCHAR2
2088 ,x_msg_count OUT NOCOPY NUMBER
2089 ,x_msg_data OUT NOCOPY VARCHAR2
2090
2091 ) IS
2092 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2093 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2094
2095 BEGIN
2096 x_return_status := FND_API.G_RET_STS_SUCCESS;
2097
2098 l_Bis_WM_Rec.weighted_definition_id := p_weighted_definition_id;
2099 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
2100 l_Bis_WM_Rec.viewby_dimension_sn := p_viewby_dimension_sn;
2101 l_Bis_WM_Rec.viewby_dim_level_sn := p_viewby_dim_level_sn;
2102 l_Bis_WM_Rec.filter_dimension_sn := p_filter_dimension_sn;
2103 l_Bis_WM_Rec.filter_dim_level_sn := p_filter_dim_level_sn;
2104 l_Bis_WM_Rec.time_dimension_short_name := p_time_dimension_sn;
2105 l_Bis_WM_Rec.time_dim_level_short_name := p_time_dim_level_sn;
2106
2107 -- Update the Weighted Measure Definition
2108 BIS_WEIGHTED_MEASURE_PUB.Update_WM_Definition(
2109 p_commit => p_commit
2110 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2111 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2112 ,x_return_status => x_return_status
2113 ,x_msg_count => x_msg_count
2114 ,x_msg_data => x_msg_data
2115 );
2116
2117 EXCEPTION
2118 WHEN FND_API.G_EXC_ERROR THEN
2119 ROLLBACK TO bis_Update_WM_Definition_pub;
2120 IF (x_msg_data IS NULL) THEN
2121 FND_MSG_PUB.Count_And_Get
2122 ( p_encoded => FND_API.G_FALSE
2123 , p_count => x_msg_count
2124 , p_data => x_msg_data
2125 );
2126 END IF;
2127 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2128 x_return_status := FND_API.G_RET_STS_ERROR;
2129 raise;
2130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2131 ROLLBACK TO bis_Update_WM_Definition_pub;
2132 IF (x_msg_data IS NULL) THEN
2133 FND_MSG_PUB.Count_And_Get
2134 ( p_encoded => FND_API.G_FALSE
2135 , p_count => x_msg_count
2136 , p_data => x_msg_data
2137 );
2138 END IF;
2139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2140 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2141 raise;
2142 WHEN OTHERS THEN
2143 ROLLBACK TO bis_Update_WM_Definition_pub;
2144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2145 IF (x_msg_data IS NOT NULL) THEN
2146 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Definition ';
2147 ELSE
2148 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Definition ';
2149 END IF;
2150 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2151 raise;
2152 END Update_WM_Definition;
2153
2154 /*******************************************************************
2155 *******************************************************************/
2156 PROCEDURE Delete_WM_Definition(
2157 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2158 ,p_weighted_definition_id IN NUMBER
2159 ,x_return_status OUT NOCOPY VARCHAR2
2160 ,x_msg_count OUT NOCOPY NUMBER
2161 ,x_msg_data OUT NOCOPY VARCHAR2
2162 ) IS
2163 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2164 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2165
2166 BEGIN
2167 -- THIS IS JUST A WRAPPER PLEASE DO NOT ADD ANY BUSINESS LOGIC TO THIS PROCEDURE
2168 x_return_status := FND_API.G_RET_STS_SUCCESS;
2169
2170 l_Bis_WM_Rec.weighted_definition_id := p_weighted_definition_id;
2171
2172 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition(
2173 p_commit => p_commit
2174 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2175 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2176 ,x_return_status => x_return_status
2177 ,x_msg_count => x_msg_count
2178 ,x_msg_data => x_msg_data
2179 );
2180
2181 EXCEPTION
2182 WHEN FND_API.G_EXC_ERROR THEN
2183 IF (x_msg_data IS NULL) THEN
2184 FND_MSG_PUB.Count_And_Get
2185 ( p_encoded => FND_API.G_FALSE
2186 , p_count => x_msg_count
2187 , p_data => x_msg_data
2188 );
2189 END IF;
2190 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2191 x_return_status := FND_API.G_RET_STS_ERROR;
2192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2193 IF (x_msg_data IS NULL) THEN
2194 FND_MSG_PUB.Count_And_Get
2195 ( p_encoded => FND_API.G_FALSE
2196 , p_count => x_msg_count
2197 , p_data => x_msg_data
2198 );
2199 END IF;
2200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2201 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2202 WHEN OTHERS THEN
2203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2204 IF (x_msg_data IS NOT NULL) THEN
2205 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition ';
2206 ELSE
2207 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition ';
2208 END IF;
2209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2210 END Delete_WM_Definition;
2211
2212 /*******************************************************************
2213 *******************************************************************/
2214
2215 PROCEDURE Create_WM_Dependency(
2216 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2217 ,p_weighted_measure_id IN NUMBER
2218 ,p_dependent_measure_id IN NUMBER
2219 ,x_return_status OUT NOCOPY VARCHAR2
2220 ,x_msg_count OUT NOCOPY NUMBER
2221 ,x_msg_data OUT NOCOPY VARCHAR2
2222 ) IS
2223 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2224 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2225 BEGIN
2226 -- THIS IS JUST A WRAPPER PLEASE DO NOT ADD ANY BUSINESS LOGIC TO THIS PROCEDURE
2227
2228 x_return_status := FND_API.G_RET_STS_SUCCESS;
2229
2230 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
2231 l_Bis_WM_Rec.dependent_measure_id := p_dependent_measure_id;
2232
2233 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency(
2234 p_commit => p_commit
2235 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2236 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2237 ,x_return_status => x_return_status
2238 ,x_msg_count => x_msg_count
2239 ,x_msg_data => x_msg_data
2240 );
2241
2242 EXCEPTION
2243 WHEN FND_API.G_EXC_ERROR THEN
2244 IF (x_msg_data IS NULL) THEN
2245 FND_MSG_PUB.Count_And_Get
2246 ( p_encoded => FND_API.G_FALSE
2247 , p_count => x_msg_count
2248 , p_data => x_msg_data
2249 );
2250 END IF;
2251 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2252 x_return_status := FND_API.G_RET_STS_ERROR;
2253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2254 IF (x_msg_data IS NULL) THEN
2255 FND_MSG_PUB.Count_And_Get
2256 ( p_encoded => FND_API.G_FALSE
2257 , p_count => x_msg_count
2258 , p_data => x_msg_data
2259 );
2260 END IF;
2261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2263 WHEN OTHERS THEN
2264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2265 IF (x_msg_data IS NOT NULL) THEN
2266 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency ';
2267 ELSE
2268 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency ';
2269 END IF;
2270 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2271
2272 END Create_WM_Dependency;
2273
2274 /*******************************************************************
2275 *******************************************************************/
2276
2277 PROCEDURE Delete_WM_Dependency(
2278 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2279 ,p_weighted_measure_id IN NUMBER
2280 ,p_dependent_measure_id IN NUMBER
2281 ,x_return_status OUT NOCOPY VARCHAR2
2282 ,x_msg_count OUT NOCOPY NUMBER
2283 ,x_msg_data OUT NOCOPY VARCHAR2
2284 ) IS
2285 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2286 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2287 BEGIN
2288 -- THIS IS JUST A WRAPPER PLEASE DO NOT ADD ANY BUSINESS LOGIC TO THIS PROCEDURE
2289 x_return_status := FND_API.G_RET_STS_SUCCESS;
2290 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
2291 l_Bis_WM_Rec.dependent_measure_id := p_dependent_measure_id;
2292
2293 -- Delete the Dependent Measure
2294 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency(
2295 p_commit => p_commit
2296 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2297 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2298 ,x_return_status => x_return_status
2299 ,x_msg_count => x_msg_count
2300 ,x_msg_data => x_msg_data
2301 );
2302
2303 EXCEPTION
2304 WHEN FND_API.G_EXC_ERROR THEN
2305 IF (x_msg_data IS NULL) THEN
2306 FND_MSG_PUB.Count_And_Get
2307 ( p_encoded => FND_API.G_FALSE
2308 , p_count => x_msg_count
2309 , p_data => x_msg_data
2310 );
2311 END IF;
2312 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2313 x_return_status := FND_API.G_RET_STS_ERROR;
2314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2315 IF (x_msg_data IS NULL) THEN
2316 FND_MSG_PUB.Count_And_Get
2317 ( p_encoded => FND_API.G_FALSE
2318 , p_count => x_msg_count
2319 , p_data => x_msg_data
2320 );
2321 END IF;
2322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2323 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2324 WHEN OTHERS THEN
2325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2326 IF (x_msg_data IS NOT NULL) THEN
2327 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency ';
2328 ELSE
2329 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency ';
2330 END IF;
2331 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2332 END Delete_WM_Dependency;
2333
2334 PROCEDURE Create_WM_Parameter(
2335 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2336 ,p_weighted_parameter_id IN NUMBER
2337 ,p_weighted_definition_id IN NUMBER
2338 ,p_time_level_value_id IN VARCHAR2
2339 ,p_filter_level_value_id IN VARCHAR2
2340 ,x_weighted_parameter_id OUT NOCOPY NUMBER
2341 ,x_return_status OUT NOCOPY VARCHAR2
2342 ,x_msg_count OUT NOCOPY NUMBER
2343 ,x_msg_data OUT NOCOPY VARCHAR2
2344 ) IS
2345 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2346 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2347 BEGIN
2348 SAVEPOINT CreateWMParameterPUB;
2349 x_return_status := FND_API.G_RET_STS_SUCCESS;
2350
2351 l_Bis_WM_Rec.weighted_parameter_id := p_weighted_parameter_id;
2352 l_Bis_WM_Rec.weighted_definition_id := p_weighted_definition_id;
2353 l_Bis_WM_Rec.time_level_value_id := p_time_level_value_id;
2354 l_Bis_WM_Rec.filter_level_value_id := p_filter_level_value_id;
2355
2356 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter(
2357 p_commit => p_commit
2358 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2359 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2360 ,x_return_status => x_return_status
2361 ,x_msg_count => x_msg_count
2362 ,x_msg_data => x_msg_data
2363 );
2364
2365 x_weighted_parameter_id := x_Bis_WM_Rec.weighted_parameter_id;
2366
2367 EXCEPTION
2368 WHEN FND_API.G_EXC_ERROR THEN
2369 IF (x_msg_data IS NULL) THEN
2370 FND_MSG_PUB.Count_And_Get
2371 ( p_encoded => FND_API.G_FALSE
2372 , p_count => x_msg_count
2373 , p_data => x_msg_data
2374 );
2375 END IF;
2376 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2377 x_return_status := FND_API.G_RET_STS_ERROR;
2378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2379 IF (x_msg_data IS NULL) THEN
2380 FND_MSG_PUB.Count_And_Get
2381 ( p_encoded => FND_API.G_FALSE
2382 , p_count => x_msg_count
2383 , p_data => x_msg_data
2384 );
2385 END IF;
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2387 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2388 WHEN OTHERS THEN
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2390 IF (x_msg_data IS NOT NULL) THEN
2391 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter ';
2392 ELSE
2393 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Parameter ';
2394 END IF;
2395 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2396 END Create_WM_Parameter;
2397
2398 /*******************************************************************
2399 *******************************************************************/
2400 PROCEDURE Delete_WM_Parameter(
2401 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2402 ,p_weighted_parameter_id IN NUMBER
2403 ,x_return_status OUT NOCOPY VARCHAR2
2404 ,x_msg_count OUT NOCOPY NUMBER
2405 ,x_msg_data OUT NOCOPY VARCHAR2
2406 ) IS
2407
2408 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2409 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2410
2411 BEGIN
2412 x_return_status := FND_API.G_RET_STS_SUCCESS;
2413
2414 l_Bis_WM_Rec.weighted_parameter_id := p_weighted_parameter_id;
2415
2416 -- Delete the Parameter
2417 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Parameter(
2418 p_commit => p_commit
2419 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2420 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2421 ,x_return_status => x_return_status
2422 ,x_msg_count => x_msg_count
2423 ,x_msg_data => x_msg_data
2424 );
2425
2426 EXCEPTION
2427 WHEN FND_API.G_EXC_ERROR THEN
2428 ROLLBACK TO Delete_WM_Parameter_pub;
2429 IF (x_msg_data IS NULL) THEN
2430 FND_MSG_PUB.Count_And_Get
2431 ( p_encoded => FND_API.G_FALSE
2432 , p_count => x_msg_count
2433 , p_data => x_msg_data
2434 );
2435 END IF;
2436 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2437 x_return_status := FND_API.G_RET_STS_ERROR;
2438 raise;
2439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2440 ROLLBACK TO Delete_WM_Parameter_pub;
2441 IF (x_msg_data IS NULL) THEN
2442 FND_MSG_PUB.Count_And_Get
2443 ( p_encoded => FND_API.G_FALSE
2444 , p_count => x_msg_count
2445 , p_data => x_msg_data
2446 );
2447 END IF;
2448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2449 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2450 raise;
2451 WHEN OTHERS THEN
2452 ROLLBACK TO Delete_WM_Parameter_pub;
2453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2454 IF (x_msg_data IS NOT NULL) THEN
2455 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Parameter ';
2456 ELSE
2457 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Parameter ';
2458 END IF;
2459 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2460 raise;
2461 END Delete_WM_Parameter;
2462
2463 ------- APIs for table BIS_WEIGHTED_MEASURE_WEIGHTS
2464
2465 /*******************************************************************
2466 *******************************************************************/
2467 PROCEDURE Create_WM_Weight(
2468 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2469 ,P_weight_id IN NUMBER
2470 ,p_weighted_parameter_id IN NUMBER
2471 ,p_dependent_measure_id IN NUMBER
2472 ,p_weight IN NUMBER
2473 ,x_weight_id OUT NOCOPY NUMBER
2474 ,x_return_status OUT NOCOPY VARCHAR2
2475 ,x_msg_count OUT NOCOPY NUMBER
2476 ,x_msg_data OUT NOCOPY VARCHAR2
2477 ) IS
2478 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2479 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2480 BEGIN
2481 x_return_status := FND_API.G_RET_STS_SUCCESS;
2482
2483 l_Bis_WM_Rec.weight_id := p_weight_id;
2484 l_Bis_WM_Rec.weighted_parameter_id := p_weighted_parameter_id;
2485 l_Bis_WM_Rec.dependent_measure_id := p_dependent_measure_id;
2486 l_Bis_WM_Rec.weight := p_weight;
2487
2488 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Weight(
2489 p_commit => p_commit
2490 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2491 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2492 ,x_return_status => x_return_status
2493 ,x_msg_count => x_msg_count
2494 ,x_msg_data => x_msg_data
2495 );
2496
2497 x_weight_id := x_Bis_WM_Rec.weight_id ;
2498
2499 EXCEPTION
2500 WHEN FND_API.G_EXC_ERROR THEN
2501 IF (x_msg_data IS NULL) THEN
2502 FND_MSG_PUB.Count_And_Get
2503 ( p_encoded => FND_API.G_FALSE
2504 , p_count => x_msg_count
2505 , p_data => x_msg_data
2506 );
2507 END IF;
2508 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2509 x_return_status := FND_API.G_RET_STS_ERROR;
2510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2511 IF (x_msg_data IS NULL) THEN
2512 FND_MSG_PUB.Count_And_Get
2513 ( p_encoded => FND_API.G_FALSE
2514 , p_count => x_msg_count
2515 , p_data => x_msg_data
2516 );
2517 END IF;
2518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2519 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2520 WHEN OTHERS THEN
2521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2522 IF (x_msg_data IS NOT NULL) THEN
2523 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Weight ';
2524 ELSE
2525 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Weight ';
2526 END IF;
2527 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2528 END Create_WM_Weight;
2529
2530 /*******************************************************************
2531 *******************************************************************/
2532 PROCEDURE Update_WM_Weight(
2533 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2534 ,p_weight_id IN NUMBER
2535 ,p_weight IN NUMBER
2536 ,x_return_status OUT NOCOPY VARCHAR2
2537 ,x_msg_count OUT NOCOPY NUMBER
2538 ,x_msg_data OUT NOCOPY VARCHAR2
2539 ) IS
2540 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2541 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2542 BEGIN
2543 x_return_status := FND_API.G_RET_STS_SUCCESS;
2544
2545 l_Bis_WM_Rec.weight_id := p_weight_id;
2546 l_Bis_WM_Rec.weight := p_weight;
2547
2548 BIS_WEIGHTED_MEASURE_PUB.Update_WM_Weight(
2549 p_commit => p_commit
2550 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2551 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2552 ,x_return_status => x_return_status
2553 ,x_msg_count => x_msg_count
2554 ,x_msg_data => x_msg_data
2555 );
2556
2557 EXCEPTION
2558 WHEN FND_API.G_EXC_ERROR THEN
2559 ROLLBACK TO bis_Update_WM_Weight_pub;
2560 IF (x_msg_data IS NULL) THEN
2561 FND_MSG_PUB.Count_And_Get
2562 ( p_encoded => FND_API.G_FALSE
2563 , p_count => x_msg_count
2564 , p_data => x_msg_data
2565 );
2566 END IF;
2567 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 raise;
2570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2571 ROLLBACK TO bis_Update_WM_Weight_pub;
2572 IF (x_msg_data IS NULL) THEN
2573 FND_MSG_PUB.Count_And_Get
2574 ( p_encoded => FND_API.G_FALSE
2575 , p_count => x_msg_count
2576 , p_data => x_msg_data
2577 );
2578 END IF;
2579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2580 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2581 raise;
2582 WHEN OTHERS THEN
2583 ROLLBACK TO bis_Update_WM_Weight_pub;
2584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2585 IF (x_msg_data IS NOT NULL) THEN
2586 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Weight ';
2587 ELSE
2588 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Weight ';
2589 END IF;
2590 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2591 raise;
2592 END Update_WM_Weight;
2593
2594 /*******************************************************************
2595 *******************************************************************/
2596 PROCEDURE Delete_WM_Weight(
2597 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2598 ,p_weight_id IN NUMBER
2599 ,x_return_status OUT NOCOPY VARCHAR2
2600 ,x_msg_count OUT NOCOPY NUMBER
2601 ,x_msg_data OUT NOCOPY VARCHAR2
2602 ) IS
2603 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2604 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2605 BEGIN
2606 SAVEPOINT Delete_WM_Weight_pub;
2607 x_return_status := FND_API.G_RET_STS_SUCCESS;
2608
2609 l_Bis_WM_Rec.weight_id := p_weight_id;
2610 -- Delete the Weight
2611 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight(
2612 p_commit => p_commit
2613 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2614 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2615 ,x_return_status => x_return_status
2616 ,x_msg_count => x_msg_count
2617 ,x_msg_data => x_msg_data
2618 );
2619
2620 EXCEPTION
2621 WHEN FND_API.G_EXC_ERROR THEN
2622 IF (x_msg_data IS NULL) THEN
2623 FND_MSG_PUB.Count_And_Get
2624 ( p_encoded => FND_API.G_FALSE
2625 , p_count => x_msg_count
2626 , p_data => x_msg_data
2627 );
2628 END IF;
2629 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2632 IF (x_msg_data IS NULL) THEN
2633 FND_MSG_PUB.Count_And_Get
2634 ( p_encoded => FND_API.G_FALSE
2635 , p_count => x_msg_count
2636 , p_data => x_msg_data
2637 );
2638 END IF;
2639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2640 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2641 WHEN OTHERS THEN
2642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2643 IF (x_msg_data IS NOT NULL) THEN
2644 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight ';
2645 ELSE
2646 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Weight ';
2647 END IF;
2648 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2649 END Delete_WM_Weight;
2650
2651 PROCEDURE Create_WM_Score(
2652 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2653 ,p_weight_id IN NUMBER
2654 ,p_low_range IN NUMBER
2655 ,p_high_range IN NUMBER
2656 ,p_score IN NUMBER
2657 ,x_return_status OUT NOCOPY VARCHAR2
2658 ,x_msg_count OUT NOCOPY NUMBER
2659 ,x_msg_data OUT NOCOPY VARCHAR2
2660 ) IS
2661 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2662 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2663 BEGIN
2664 SAVEPOINT Create_WM_Score_PUB;
2665 x_return_status := FND_API.G_RET_STS_SUCCESS;
2666
2667 l_Bis_WM_Rec.weight_id := p_weight_id;
2668 l_Bis_WM_Rec.low_range := p_low_range;
2669 l_Bis_WM_Rec.high_range := p_high_range;
2670 l_Bis_WM_Rec.score := p_score;
2671
2672 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Score(
2673 p_commit => p_commit
2674 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2675 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2676 ,x_return_status => x_return_status
2677 ,x_msg_count => x_msg_count
2678 ,x_msg_data => x_msg_data
2679 );
2680
2681 EXCEPTION
2682 WHEN FND_API.G_EXC_ERROR THEN
2683 IF (x_msg_data IS NULL) THEN
2684 FND_MSG_PUB.Count_And_Get
2685 ( p_encoded => FND_API.G_FALSE
2686 , p_count => x_msg_count
2687 , p_data => x_msg_data
2688 );
2689 END IF;
2690 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2691 x_return_status := FND_API.G_RET_STS_ERROR;
2692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2693 IF (x_msg_data IS NULL) THEN
2694 FND_MSG_PUB.Count_And_Get
2695 ( p_encoded => FND_API.G_FALSE
2696 , p_count => x_msg_count
2697 , p_data => x_msg_data
2698 );
2699 END IF;
2700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2701 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2702 WHEN OTHERS THEN
2703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2704 IF (x_msg_data IS NOT NULL) THEN
2705 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_WM_Score ';
2706 ELSE
2707 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_WM_Score ';
2708 END IF;
2709 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2710 END Create_WM_Score;
2711
2712 /*******************************************************************
2713 *******************************************************************/
2714 PROCEDURE Delete_WM_Score(
2715 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2716 ,p_weight_id IN NUMBER
2717 ,x_return_status OUT NOCOPY VARCHAR2
2718 ,x_msg_count OUT NOCOPY NUMBER
2719 ,x_msg_data OUT NOCOPY VARCHAR2
2720 ) IS
2721 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2722 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2723 BEGIN
2724 x_return_status := FND_API.G_RET_STS_SUCCESS;
2725
2726 l_Bis_WM_Rec.weight_id := p_weight_id;
2727
2728 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score(
2729 p_commit => p_commit
2730 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2731 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2732 ,x_return_status => x_return_status
2733 ,x_msg_count => x_msg_count
2734 ,x_msg_data => x_msg_data
2735 );
2736
2737 EXCEPTION
2738 WHEN FND_API.G_EXC_ERROR THEN
2739 IF (x_msg_data IS NULL) THEN
2740 FND_MSG_PUB.Count_And_Get
2741 ( p_encoded => FND_API.G_FALSE
2742 , p_count => x_msg_count
2743 , p_data => x_msg_data
2744 );
2745 END IF;
2746 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2747 x_return_status := FND_API.G_RET_STS_ERROR;
2748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2749 IF (x_msg_data IS NULL) THEN
2750 FND_MSG_PUB.Count_And_Get
2751 ( p_encoded => FND_API.G_FALSE
2752 , p_count => x_msg_count
2753 , p_data => x_msg_data
2754 );
2755 END IF;
2756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2757 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2758 WHEN OTHERS THEN
2759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2760 IF (x_msg_data IS NOT NULL) THEN
2761 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score ';
2762 ELSE
2763 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Score ';
2764 END IF;
2765 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2766 END Delete_WM_Score;
2767
2768
2769 /*******************************************************************
2770 Check_Defined_Weights
2771 Check if some weight hadbeen defined for the Weighted Measure
2772 or for a scpecific dependint Measure.
2773
2774 Input Parameters:
2775
2776 p_weighted_measure_id -> This is the Weighted Measure Id
2777 p_dependent_measure_Id -> if null is passed it check for the whole
2778 Weighted Measure
2779 Return
2780 P -> Positive Weights Defined
2781 Z -> Zero Wheioghts Defined
2782 N -> No Weights Defined
2783 NULL -> It is returned when and error happen or p_weighted_measure_id
2784 is passed as null
2785
2786 *******************************************************************/
2787 FUNCTION get_Defined_Weights_Status (
2788 p_weighted_measure_id IN NUMBER
2789 ,p_dependent_measure_Id IN NUMBER
2790 ) RETURN VARCHAR IS
2791 -- Cursor to count Weights <> 0 associated to a Dependent Measure
2792 CURSOR c_weights_by_dep_measure IS
2793 SELECT COUNT(C.WEIGHT_ID) C1
2794 FROM BIS_WEIGHTED_MEASURE_DEFNS A
2795 ,BIS_WEIGHTED_MEASURE_PARAMS B
2796 ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2797 WHERE A.weighted_measure_id = p_weighted_measure_id
2798 AND A.weighted_definition_id = B.weighted_definition_id
2799 AND B.weighted_parameter_id = C.weighted_parameter_id
2800 AND C.DEPENDENT_MEASURE_ID = p_dependent_measure_Id
2801 AND C.WEIGHT <> 0 ;
2802
2803 -- Cursor to count Weights associated to a Dependent Measure
2804 CURSOR c_weights0_by_dep_measure IS
2805 SELECT COUNT(WEIGHT_ID) C1
2806 FROM BIS_WEIGHTED_MEASURE_DEFNS A
2807 ,BIS_WEIGHTED_MEASURE_PARAMS B
2808 ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2809 WHERE A.weighted_measure_id = p_weighted_measure_id
2810 AND A.weighted_definition_id = B.weighted_definition_id
2811 AND B.weighted_parameter_id = C.weighted_parameter_id
2812 AND C.DEPENDENT_MEASURE_ID = p_dependent_measure_Id
2813 AND C.WEIGHT = 0 ;
2814
2815 -- Cursor to count the Weights <> 0 associated to Weighted Measure
2816 CURSOR c_weights IS
2817 SELECT COUNT(WEIGHT_ID) C1
2818 FROM BIS_WEIGHTED_MEASURE_DEFNS A
2819 ,BIS_WEIGHTED_MEASURE_PARAMS B
2820 ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2821 WHERE A.weighted_measure_id = p_weighted_measure_id
2822 AND A.weighted_definition_id = B.weighted_definition_id
2823 AND B.weighted_parameter_id = C.weighted_parameter_id
2824 AND C.WEIGHT <> 0 ;
2825
2826 -- Cursor to count the Weights <> 0 associated to Weighted Measure
2827 CURSOR c_weights0 IS
2828 SELECT COUNT(WEIGHT_ID) C1
2829 FROM BIS_WEIGHTED_MEASURE_DEFNS A
2830 ,BIS_WEIGHTED_MEASURE_PARAMS B
2831 ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2832 WHERE A.weighted_measure_id = p_weighted_measure_id
2833 AND A.weighted_definition_id = B.weighted_definition_id
2834 AND B.weighted_parameter_id = C.weighted_parameter_id
2835 AND C.WEIGHT = 0 ;
2836
2837 l_status VARCHAR2(1);
2838 l_count NUMBER;
2839
2840 BEGIN
2841 l_status := G_NO_WEIGHTS;
2842
2843 -- Query for Count the Weights Defined for the current dependent measure
2844 IF p_weighted_measure_id IS NOT NULL THEN
2845
2846 IF p_dependent_Measure_Id IS NOT NULL THEN
2847 -- For the dependent Measure only
2848 FOR CD IN c_weights_by_dep_measure LOOP
2849 l_count := CD.C1;
2850 END LOOP;
2851 IF l_count > 0 THEN
2852 l_status := G_POSITIVE_WEIGHTS;
2853 ELSE
2854 FOR CD IN c_weights0_by_dep_measure LOOP
2855 l_count := CD.C1;
2856 END LOOP;
2857 IF l_count > 0 THEN
2858 l_status := G_ZERO_WEIGHTS;
2859 END IF;
2860 END IF;
2861
2862 ELSE
2863 -- For the Weighted Measure
2864 FOR CD IN c_weights LOOP
2865 l_count := CD.C1;
2866 END LOOP;
2867 IF l_count > 0 THEN
2868 l_status := G_POSITIVE_WEIGHTS;
2869 ELSE
2870 FOR CD IN c_weights0 LOOP
2871 l_count := CD.C1;
2872 END LOOP;
2873 IF l_count > 0 THEN
2874 l_status := G_ZERO_WEIGHTS;
2875 END IF;
2876 END IF;
2877 END IF;
2878
2879 RETURN l_status;
2880
2881 ELSE
2882 RETURN NULL;
2883 END IF;
2884
2885 EXCEPTION
2886 WHEN OTHERS THEN
2887 RETURN NULL;
2888
2889 end get_Defined_Weights_Status;
2890
2891 /*******************************************************************
2892 *******************************************************************/
2893 PROCEDURE Delete_Weighted_Measure_data(
2894 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2895 ,p_weighted_measure_id IN NUMBER
2896 ,x_return_status OUT NOCOPY VARCHAR2
2897 ,x_msg_count OUT NOCOPY NUMBER
2898 ,x_msg_data OUT NOCOPY VARCHAR2
2899 ) IS
2900 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2901 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
2902
2903 -- Cursor to get the Weighted Parameters associated to the Weighted Definition
2904 CURSOR c_WM_Definitions IS
2905 SELECT WEIGHTED_DEFINITION_ID
2906 FROM BIS_WEIGHTED_MEASURE_DEFNS
2907 WHERE WEIGHTED_MEASURE_ID = p_weighted_measure_id;
2908
2909 -- Cursor to get the Weighted Parameters associated to the Weighted Definition
2910 CURSOR c_WM_Dependent IS
2911 SELECT DEPENDENT_MEASURE_ID
2912 FROM BIS_WEIGHTED_MEASURE_DEPENDS
2913 WHERE WEIGHTED_MEASURE_ID = p_weighted_measure_id;
2914
2915 BEGIN
2916 x_return_status := FND_API.G_RET_STS_SUCCESS;
2917
2918 -- Dependent Measures Cascading delete when the Weighted Measure is deleted
2919 FOR CD IN c_WM_Dependent LOOP
2920 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
2921 l_Bis_WM_Rec.dependent_measure_id := CD.DEPENDENT_MEASURE_ID;
2922 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Dependency(
2923 p_commit => FND_API.G_FALSE
2924 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2925 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2926 ,x_return_status => x_return_status
2927 ,x_msg_count => x_msg_count
2928 ,x_msg_data => x_msg_data
2929 );
2930 END LOOP;
2931
2932 -- Definitions Cascading delete when the Weighted Measure is deleted
2933 FOR CD IN c_WM_Definitions LOOP
2934 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
2935 l_Bis_WM_Rec.weighted_definition_id := CD.WEIGHTED_DEFINITION_ID;
2936 BIS_WEIGHTED_MEASURE_PUB.Delete_WM_Definition(
2937 p_commit => FND_API.G_FALSE
2938 ,p_Bis_WM_Rec => l_Bis_WM_Rec
2939 ,x_Bis_WM_Rec => x_Bis_WM_Rec
2940 ,x_return_status => x_return_status
2941 ,x_msg_count => x_msg_count
2942 ,x_msg_data => x_msg_data
2943 );
2944 END LOOP;
2945
2946 IF p_commit = FND_API.G_TRUE THEN
2947 COMMIT;
2948 END IF;
2949
2950 EXCEPTION
2951 WHEN FND_API.G_EXC_ERROR THEN
2952 ROLLBACK TO Delete_WM_Definition_pub;
2953 IF (x_msg_data IS NULL) THEN
2954 FND_MSG_PUB.Count_And_Get
2955 ( p_encoded => FND_API.G_FALSE
2956 , p_count => x_msg_count
2957 , p_data => x_msg_data
2958 );
2959 END IF;
2960 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2961 x_return_status := FND_API.G_RET_STS_ERROR;
2962 -- raise;
2963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2964 ROLLBACK TO Delete_WM_Definition_pub;
2965 IF (x_msg_data IS NULL) THEN
2966 FND_MSG_PUB.Count_And_Get
2967 ( p_encoded => FND_API.G_FALSE
2968 , p_count => x_msg_count
2969 , p_data => x_msg_data
2970 );
2971 END IF;
2972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2973 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2974 -- raise;
2975 WHEN OTHERS THEN
2976 ROLLBACK TO Delete_WM_Definition_pub;
2977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2978 IF (x_msg_data IS NOT NULL) THEN
2979 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_Weighted_Measure_data ';
2980 ELSE
2981 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_Weighted_Measure_data ';
2982 END IF;
2983 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2984 -- raise;
2985 END Delete_Weighted_Measure_data;
2986 /*******************************************************************
2987 *******************************************************************/
2988
2989 PROCEDURE Create_Weighted_Measure_data(
2990 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
2991 ,p_weighted_measure_id IN NUMBER
2992 ,p_dependent_measure_ids IN VARCHAR2
2993 ,p_viewby_dimension_short_name IN VARCHAR2
2994 ,p_viewby_dim_level_short_name IN VARCHAR2
2995 ,p_filter_dimension_short_name IN VARCHAR2
2996 ,p_filter_dim_level_short_name IN VARCHAR2
2997 ,p_time_dimension_short_name IN VARCHAR2
2998 ,p_time_dim_level_short_names IN VARCHAR2
2999 ,x_return_status OUT NOCOPY VARCHAR2
3000 ,x_msg_count OUT NOCOPY NUMBER
3001 ,x_msg_data OUT NOCOPY VARCHAR2
3002 ) IS
3003
3004 l_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
3005 x_Bis_WM_Rec BIS_WEIGHTED_MEASURE_PUB.Bis_WM_Rec;
3006
3007 l_dependent_measure_ids VARCHAR2(3000);
3008 l_dependent_measure_id NUMBER;
3009 l_time_dim_level_short_names VARCHAR2 (6000);
3010 l_time_dim_level_short_name VARCHAR2 (300);
3011 l_count NUMBER;
3012
3013 BEGIN
3014 SAVEPOINT BisCreateWeightedMeasure;
3015 x_return_status := FND_API.G_RET_STS_SUCCESS;
3016
3017 -- Check if the Weighted Measure metada already exist
3018 SELECT COUNT(1)
3019 INTO l_count
3020 FROM BIS_WEIGHTED_MEASURE_DEFNS
3021 WHERE WEIGHTED_MEASURE_ID = p_weighted_measure_id;
3022
3023 -- Pending to check if the Creation_By will be added to the API
3024 l_Bis_WM_Rec.Created_By := 1;
3025
3026 IF l_count = 0 THEN
3027
3028 -- Create the Definitions for each Time Dim Level
3029 l_time_dim_level_short_names := p_time_dim_level_short_names;
3030 WHILE (is_more( p_names => l_time_dim_level_short_names
3031 , p_name => l_time_dim_level_short_name))
3032 LOOP
3033
3034 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
3035 l_Bis_WM_Rec.viewby_dimension_sn := p_viewby_dimension_short_name;
3036 l_Bis_WM_Rec.viewby_dim_level_sn := p_viewby_dim_level_short_name;
3037 l_Bis_WM_Rec.filter_dimension_sn := p_filter_dimension_short_name;
3038 l_Bis_WM_Rec.filter_dim_level_sn := p_filter_dim_level_short_name;
3039 l_Bis_WM_Rec.time_dimension_short_name := p_time_dimension_short_name;
3040 l_Bis_WM_Rec.time_dim_level_short_name := l_time_dim_level_short_name;
3041
3042 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.weighted_measure_id = ' || l_Bis_WM_Rec.weighted_measure_id);
3043 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.viewby_dimension_sn = ' || l_Bis_WM_Rec.viewby_dimension_sn);
3044 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.viewby_dim_level_sn = ' || l_Bis_WM_Rec.viewby_dim_level_sn);
3045 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.filter_dimension_sn = ' || l_Bis_WM_Rec.filter_dimension_sn);
3046 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.filter_dim_level_sn = ' || l_Bis_WM_Rec.filter_dim_level_sn);
3047 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.time_dimension_short_name = ' || l_Bis_WM_Rec.time_dimension_short_name);
3048 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.time_dim_level_short_name = ' || l_Bis_WM_Rec.time_dim_level_short_name);
3049
3050 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Definition(
3051 p_commit => FND_API.G_FALSE
3052 ,p_Bis_WM_Rec => l_Bis_WM_Rec
3053 ,x_Bis_WM_Rec => x_Bis_WM_Rec
3054 ,x_return_status => x_return_status
3055 ,x_msg_count => x_msg_count
3056 ,x_msg_data => x_msg_data
3057 );
3058 END LOOP;
3059
3060 -- Create the Dependencies of each dependent mesure
3061 l_dependent_measure_ids := p_dependent_measure_ids;
3062 WHILE (is_more( p_names => l_dependent_measure_ids
3063 , p_name => l_dependent_measure_id))
3064 LOOP
3065 l_Bis_WM_Rec.weighted_measure_id := p_weighted_measure_id;
3066 l_Bis_WM_Rec.dependent_measure_id := l_dependent_measure_id;
3067
3068 --DBMS_OUTPUT.PUT_LINE('l_Bis_WM_Rec.weighted_measure_id' || l_Bis_WM_Rec.weighted_measure_id);
3069 --DBMS_OUTPUT.PUT_LINE('l_dependent_measure_id = '||l_dependent_measure_id);
3070
3071 BIS_WEIGHTED_MEASURE_PUB.Create_WM_Dependency(
3072 p_commit => FND_API.G_FALSE
3073 ,p_Bis_WM_Rec => l_Bis_WM_Rec
3074 ,x_Bis_WM_Rec => x_Bis_WM_Rec
3075 ,x_return_status => x_return_status
3076 ,x_msg_count => x_msg_count
3077 ,x_msg_data => x_msg_data
3078 );
3079 END LOOP;
3080
3081
3082 ELSE
3083 /*
3084 Update_Weighted_Measure_data(
3085 p_commit => p_commit
3086 ,p_weighted_measure_id => p_weighted_measure_id
3087 ,p_dependent_measure_ids => p_dependent_measure_ids
3088 ,p_viewby_dimension_short_name => p_viewby_dimension_short_name
3089 ,p_viewby_dim_level_short_name => p_viewby_dim_level_short_name
3090 ,p_filter_dimension_short_name => p_filter_dimension_short_name
3091 ,p_filter_dim_level_short_name => p_filter_dim_level_short_name
3092 ,p_time_dimension_short_name => p_time_dimension_short_name
3093 ,p_time_dim_level_short_names => p_time_dim_level_short_names
3094 ,x_return_status => x_return_status
3095 ,x_msg_count => x_msg_count
3096 ,x_msg_data => x_msg_data
3097 );
3098 */
3099 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3101 END IF;
3102
3103 END IF;
3104
3105 IF p_commit = FND_API.G_TRUE THEN
3106 COMMIT;
3107 END IF;
3108
3109
3110 EXCEPTION
3111 WHEN FND_API.G_EXC_ERROR THEN
3112 ROLLBACK TO BisCreateWeightedMeasure;
3113 IF (x_msg_data IS NULL) THEN
3114 FND_MSG_PUB.Count_And_Get
3115 ( p_encoded => FND_API.G_FALSE
3116 , p_count => x_msg_count
3117 , p_data => x_msg_data
3118 );
3119 END IF;
3120 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3121 x_return_status := FND_API.G_RET_STS_ERROR;
3122 -- raise;
3123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3124 ROLLBACK TO BisCreateWeightedMeasure;
3125 IF (x_msg_data IS NULL) THEN
3126 FND_MSG_PUB.Count_And_Get
3127 ( p_encoded => FND_API.G_FALSE
3128 , p_count => x_msg_count
3129 , p_data => x_msg_data
3130 );
3131 END IF;
3132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3133 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3134 -- raise;
3135 WHEN OTHERS THEN
3136 ROLLBACK TO BisCreateWeightedMeasure;
3137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3138 IF (x_msg_data IS NOT NULL) THEN
3139 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Create_Weighted_Measure_data ';
3140 ELSE
3141 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Create_Weighted_Measure_data ';
3142 END IF;
3143 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3144 -- raise;
3145 END Create_Weighted_Measure_data;
3146
3147 /************************************************************************************
3148 -- API name : Set_Weight_Data
3149 -- Type : Public
3150 -- This API gets called we initially set weight for dependent measures.
3151 -- This also gets called when we change the weights for dependent measures
3152 -- Logic :
3153 -- 1. Get all the definition ids using the weighted_measure_id
3154 -- 2. Retrieve all the parameter ids corresponding to the definition ids
3155 -- retrieved in step1
3156 -- 3. If the weight is non-null value update the old weight entries
3157 -- 4. If the weight is null remove the corresponding weights and score
3158 -- 5. Check if there are any parameter combinations that do not have weight
3159 -- defined . If so create weight entries for these combinations
3160 -- entries (Only if the weight is not null)
3161 ************************************************************************************/
3162 PROCEDURE Set_Weight_Data(
3163 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
3164 ,p_weighted_measure_id NUMBER
3165 ,p_dependent_measure_id NUMBER
3166 ,p_weight NUMBER
3167 ,x_return_status OUT NOCOPY VARCHAR2
3168 ,x_msg_count OUT NOCOPY NUMBER
3169 ,x_msg_data OUT NOCOPY VARCHAR2
3170 ) IS
3171 l_Definition_Ids FND_TABLE_OF_NUMBER;
3172 l_Param_Ids FND_TABLE_OF_NUMBER;
3173 l_Insert_Param_Ids FND_TABLE_OF_NUMBER;
3174 l_Update_Param_Ids FND_TABLE_OF_NUMBER;
3175 l_count NUMBER;
3176 l_User_Id NUMBER;
3177 l_Login_Id NUMBER;
3178
3179 BEGIN
3180 SAVEPOINT BisSetWeightDataPUB1;
3181 x_return_status := FND_API.G_RET_STS_SUCCESS;
3182
3183
3184 -- Check that dependent measure exist
3185 SELECT COUNT(WEIGHTED_MEASURE_ID)
3186 INTO l_count
3187 FROM BIS_WEIGHTED_MEASURE_DEPENDS
3188 WHERE WEIGHTED_MEASURE_ID = p_weighted_measure_id
3189 AND DEPENDENT_MEASURE_ID = p_dependent_measure_id;
3190
3191 IF l_count = 0 THEN
3192 FND_MESSAGE.SET_NAME('BIS','BIS_WM_DEP_MEASURE_NO_FOUND');
3193 FND_MESSAGE.SET_TOKEN('DEP_MEASURE',p_dependent_measure_id);
3194 FND_MESSAGE.SET_TOKEN('MEASURE',p_weighted_measure_id);
3195 FND_MSG_PUB.ADD;
3196 RAISE FND_API.G_EXC_ERROR;
3197 END IF;
3198
3199 l_User_Id := FND_GLOBAL.user_id;
3200 l_Login_Id := FND_GLOBAL.login_id;
3201 -- Create the Weights for all the Definitions/Parameter
3202
3203 SELECT
3204 defns.weighted_definition_id
3205 BULK COLLECT INTO
3206 l_Definition_Ids
3207 FROM
3208 bis_weighted_measure_defns defns
3209 WHERE
3210 defns.weighted_measure_id = p_weighted_measure_id;
3211
3212 IF l_Definition_Ids.COUNT > 0 THEN
3213 SELECT
3214 params.weighted_parameter_id
3215 BULK COLLECT INTO
3216 l_Param_Ids
3217 FROM
3218 bis_weighted_measure_params params
3219 WHERE
3220 params.weighted_definition_id in (SELECT column_value FROM TABLE(CAST(l_Definition_Ids AS FND_TABLE_OF_NUMBER)));
3221
3222 IF l_Param_Ids.COUNT > 0 THEN
3223 SELECT
3224 column_value
3225 BULK COLLECT INTO
3226 l_Insert_Param_Ids
3227 FROM
3228 (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER))
3229 MINUS
3230 SELECT
3231 weights.weighted_parameter_id
3232 FROM
3233 bis_weighted_measure_weights weights
3234 WHERE
3235 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER))) AND
3236 weights.dependent_measure_id = p_dependent_measure_id);
3237
3238 SELECT
3239 weights.weighted_parameter_id
3240 BULK COLLECT INTO
3241 l_Update_Param_Ids
3242 FROM
3243 bis_weighted_measure_weights weights
3244 WHERE
3245 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER))) AND
3246 weights.dependent_measure_id = p_dependent_measure_id;
3247
3248 IF l_Update_Param_Ids.COUNT > 0 THEN
3249 IF p_Weight IS NOT NULL THEN
3250 FORALL i in 1..l_Update_Param_Ids.COUNT
3251 UPDATE bis_weighted_measure_weights SET
3252 weight = p_weight
3253 ,last_update_date = SYSDATE
3254 ,last_updated_by = l_User_Id
3255 ,last_update_login = l_Login_Id
3256 WHERE
3257 weighted_parameter_id = l_Update_Param_Ids(i) AND
3258 dependent_measure_id = p_dependent_measure_id;
3259 ELSE
3260 Delete_Bulk_Weights_Scores (
3261 p_commit => FND_API.G_FALSE
3262 ,p_Param_Ids => l_Update_Param_Ids
3263 ,p_dependent_measure_id => p_dependent_measure_id
3264 ,x_return_status => x_return_status
3265 ,x_msg_count => x_msg_count
3266 ,x_msg_data => x_msg_data
3267 );
3268 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3270 END IF;
3271 END IF;
3272 END IF;
3273
3274 IF p_Weight IS NOT NULL AND l_Insert_Param_Ids.COUNT > 0 THEN
3275 FORALL i in 1..l_Insert_Param_Ids.COUNT
3276 INSERT INTO bis_weighted_measure_weights(
3277 weight_id
3278 ,weighted_parameter_id
3279 ,dependent_measure_id
3280 ,weight
3281 ,creation_date
3282 ,created_by
3283 ,last_update_date
3284 ,last_updated_by
3285 ,last_update_login
3286 ) VALUES (
3287 bis_weighted_measure_weights_s.nextval
3288 ,l_Insert_Param_Ids(i)
3289 ,p_dependent_measure_id
3290 ,p_weight
3291 ,SYSDATE
3292 ,l_User_Id
3293 ,SYSDATE
3294 ,l_User_Id
3295 ,l_Login_Id
3296 );
3297 END IF;
3298 END IF;
3299 END IF;
3300
3301 IF p_commit = FND_API.G_TRUE THEN
3302 COMMIT;
3303 END IF;
3304
3305 EXCEPTION
3306 WHEN FND_API.G_EXC_ERROR THEN
3307 ROLLBACK TO BisSetWeightDataPUB1;
3308 IF (x_msg_data IS NULL) THEN
3309 FND_MSG_PUB.Count_And_Get
3310 ( p_encoded => FND_API.G_FALSE
3311 , p_count => x_msg_count
3312 , p_data => x_msg_data
3313 );
3314 END IF;
3315 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3316 x_return_status := FND_API.G_RET_STS_ERROR;
3317 -- raise;
3318 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3319 ROLLBACK TO BisSetWeightDataPUB1;
3320 IF (x_msg_data IS NULL) THEN
3321 FND_MSG_PUB.Count_And_Get
3322 ( p_encoded => FND_API.G_FALSE
3323 , p_count => x_msg_count
3324 , p_data => x_msg_data
3325 );
3326 END IF;
3327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3328 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3329 -- raise;
3330 WHEN OTHERS THEN
3331 ROLLBACK TO BisSetWeightDataPUB1;
3332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3333 IF (x_msg_data IS NOT NULL) THEN
3334 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Set_Weight_Data ';
3335 ELSE
3336 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Set_Weight_Data ';
3337 END IF;
3338 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3339 -- raise;
3340 END Set_Weight_Data;
3341
3342 Procedure Set_Weights_Data(
3343 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
3344 ,p_weighted_measure_id IN NUMBER
3345 ,p_depend_measure_short_names IN FND_TABLE_OF_VARCHAR2_30
3346 ,p_weights IN FND_TABLE_OF_NUMBER
3347 ,x_return_status OUT NOCOPY VARCHAR2
3348 ,x_msg_count OUT NOCOPY NUMBER
3349 ,x_msg_data OUT NOCOPY VARCHAR2
3350 )is
3351
3352 l_measure_rec BIS_Measure_PUB.Measure_Rec_Type;
3353 l_measure_id NUMBER;
3354
3355 begin
3356
3357 fnd_msg_pub.initialize;
3358
3359 FOR i in 1..p_depend_measure_short_names.COUNT LOOP
3360
3361 l_measure_rec.measure_short_name := p_depend_measure_short_names(i);
3362 l_Measure_Id := BIS_Measure_PVT.Get_Measure_Id_From_Short_Name(l_measure_rec);
3363
3364 Set_Weight_Data (
3365 p_commit => p_commit,
3366 p_weighted_measure_id => p_weighted_measure_id,
3367 p_dependent_measure_id => l_Measure_Id,
3368 p_weight => p_weights(i),
3369 x_return_status => x_return_status,
3370 x_msg_count => x_msg_count,
3371 x_msg_data => x_msg_data);
3372
3373 END LOOP;
3374
3375 BIS_WEIGHTED_MEASURE_PUB.Update_WM_Last_Update_Info(
3376 p_commit => p_commit
3377 ,p_Weighted_Measure_Id => p_weighted_measure_id
3378 ,x_return_status => x_return_status
3379 ,x_msg_count => x_msg_count
3380 ,x_msg_data => x_msg_data
3381 );
3382
3383 EXCEPTION
3384 WHEN FND_API.G_EXC_ERROR THEN
3385 x_return_status := FND_API.G_RET_STS_ERROR;
3386 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3387 ,p_data => x_msg_data);
3388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3390 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3391 ,p_data => x_msg_data);
3392 WHEN NO_DATA_FOUND THEN
3393 x_return_status := FND_API.G_RET_STS_ERROR;
3394 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3395 ,p_data => x_msg_data);
3396 WHEN OTHERS THEN
3397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3398 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3399 ,p_data => x_msg_data);
3400 if (x_msg_data is null) then
3401 x_msg_data := SQLERRM;
3402 end if;
3403
3404 end Set_Weights_Data;
3405
3406 FUNCTION Get_Dep_KPI_Format_Mask (
3407 p_wkpi_id IN BIS_INDICATORS.INDICATOR_ID%TYPE,
3408 p_dep_kpi_id IN BIS_INDICATORS.INDICATOR_ID%TYPE
3409 ) RETURN AK_REGION_ITEMS_VL.ATTRIBUTE7%TYPE IS
3410 CURSOR format_mask_cur IS
3411 SELECT attribute7
3412 FROM bis_indicators wkpi, bis_indicators dkpi, ak_region_items_vl r
3413 WHERE wkpi.indicator_id = p_wkpi_id
3414 AND dkpi.indicator_id = p_dep_kpi_id
3415 AND BSC_BIS_MEASURE_PUB.Get_Primary_Data_Source(p_wkpi_id) = r.region_code
3416 AND dkpi.short_name = r.attribute2
3417 AND r.attribute1 IN ('MEASURE', 'MEASURE_NOTARGET');
3418 l_format_mask_cur format_mask_cur%ROWTYPE;
3419
3420 l_format_mask AK_REGION_ITEMS_VL.ATTRIBUTE7%TYPE;
3421 BEGIN
3422 OPEN format_mask_cur;
3423 FETCH format_mask_cur INTO l_format_mask_cur;
3424 IF format_mask_cur%FOUND THEN
3425 l_format_mask := l_format_mask_cur.attribute7;
3426 END IF;
3427 CLOSE format_mask_cur;
3428 RETURN l_format_mask;
3429 EXCEPTION
3430 WHEN OTHERS THEN
3431 IF format_mask_cur%ISOPEN THEN
3432 CLOSE format_mask_cur;
3433 END IF;
3434 RETURN l_format_mask;
3435 END Get_Dep_KPI_Format_Mask;
3436
3437
3438 /*******************************************************************
3439 *******************************************************************/
3440 PROCEDURE Update_WM_Last_Update_Info(
3441 p_commit IN VARCHAR2 := FND_API.G_FALSE
3442 ,p_weighted_measure_id IN NUMBER
3443 ,x_return_status OUT NOCOPY VARCHAR2
3444 ,x_msg_count OUT NOCOPY NUMBER
3445 ,x_msg_data OUT NOCOPY VARCHAR2
3446 ) IS
3447 BEGIN
3448 SAVEPOINT Update_WM_LU_Info_pub;
3449
3450 x_return_status := FND_API.G_RET_STS_SUCCESS;
3451
3452 BIS_WEIGHTED_MEASURE_PVT.Update_WM_Last_Update_Info(
3453 p_commit => p_commit
3454 ,p_weighted_measure_id => p_weighted_measure_id
3455 ,x_return_status => x_return_status
3456 ,x_msg_count => x_msg_count
3457 ,x_msg_data => x_msg_data
3458 );
3459
3460 EXCEPTION
3461 WHEN FND_API.G_EXC_ERROR THEN
3462 ROLLBACK TO Update_WM_LU_Info_pub;
3463 IF (x_msg_data IS NULL) THEN
3464 FND_MSG_PUB.Count_And_Get
3465 ( p_encoded => FND_API.G_FALSE
3466 , p_count => x_msg_count
3467 , p_data => x_msg_data
3468 );
3469 END IF;
3470 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3471 x_return_status := FND_API.G_RET_STS_ERROR;
3472 raise;
3473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3474 ROLLBACK TO Update_WM_LU_Info_pub;
3475 IF (x_msg_data IS NULL) THEN
3476 FND_MSG_PUB.Count_And_Get
3477 ( p_encoded => FND_API.G_FALSE
3478 , p_count => x_msg_count
3479 , p_data => x_msg_data
3480 );
3481 END IF;
3482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3483 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3484 raise;
3485 WHEN OTHERS THEN
3486 ROLLBACK TO Update_WM_LU_Info_pub;
3487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3488 IF (x_msg_data IS NOT NULL) THEN
3489 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Update_WM_Last_Update_Info ';
3490 ELSE
3491 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Update_WM_Last_Update_Info ';
3492 END IF;
3493 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3494 raise;
3495 END Update_WM_Last_Update_Info;
3496
3497 /************************************************************************************
3498 -- API name : Delete_Mass_Update_Data
3499 -- Type : Private
3500 -- Deletes the old scores and the dependent information for the current
3501 -- combinations
3502 -- Logic :
3503 -- 1. Fetch parameter ids for all the combinations that were defined earlier
3504 -- 2. Fetch the corresponding weight ids for the current dependent measure
3505 -- 3. Delete all the weights and scores corresponding to the above weight ids
3506 ************************************************************************************/
3507
3508 PROCEDURE Delete_Mass_Update_Data(
3509 p_commit IN VARCHAR2 := FND_API.G_FALSE
3510 ,p_weighted_definition_id IN NUMBER
3511 ,p_dependent_measure_id IN NUMBER
3512 ,p_Selected_Period_Ids IN BIS_TABLE_OF_VARCHAR
3513 ,p_Selected_DimObj_Ids IN BIS_TABLE_OF_VARCHAR
3514 ,x_return_status OUT NOCOPY VARCHAR2
3515 ,x_msg_count OUT NOCOPY NUMBER
3516 ,x_msg_data OUT NOCOPY VARCHAR2
3517 )
3518 IS
3519 l_Param_Ids FND_TABLE_OF_NUMBER;
3520 l_Weight_Ids FND_TABLE_OF_NUMBER;
3521 BEGIN
3522 x_return_status := FND_API.G_RET_STS_SUCCESS;
3523 SAVEPOINT Bis_Delete_Mass_Update_Data;
3524
3525 SELECT
3526 params.weighted_parameter_id
3527 BULK COLLECT INTO
3528 l_Param_Ids
3529 FROM
3530 bis_weighted_measure_params params,
3531 (SELECT
3532 a.column_value filter_level_value_id,
3533 b.column_value time_level_value_id
3534 FROM
3535 (SELECT column_value FROM TABLE(CAST(p_Selected_DimObj_Ids AS BIS_TABLE_OF_VARCHAR))) A,
3536 (SELECT column_value FROM TABLE(CAST(p_Selected_Period_Ids AS BIS_TABLE_OF_VARCHAR))) B) curParams
3537 WHERE
3538 params.weighted_definition_id = p_weighted_definition_id AND
3539 params.time_level_value_id <> 'DEFAULT' AND
3540 params.filter_level_value_id <> 'DEFAULT' AND
3541 params.time_level_value_id = curParams.time_level_value_id AND
3542 params.filter_level_value_id = curParams.filter_level_value_id;
3543
3544 IF l_Param_Ids.COUNT > 0 THEN
3545 SELECT
3546 weights.weight_id
3547 BULK COLLECT INTO
3548 l_Weight_Ids
3549 FROM
3550 bis_weighted_measure_weights weights
3551 WHERE
3552 weights.dependent_measure_id = p_dependent_measure_id AND
3553 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER)));
3554
3555 IF l_Weight_Ids.COUNT > 0 THEN
3556 Delete_Bulk_Weights_Scores (
3557 p_commit => FND_API.G_FALSE
3558 ,p_Param_Ids => l_Param_Ids
3559 ,p_dependent_measure_id => p_dependent_measure_id
3560 ,x_return_status => x_return_status
3561 ,x_msg_count => x_msg_count
3562 ,x_msg_data => x_msg_data
3563 );
3564 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3566 END IF;
3567 END IF;
3568 END IF;
3569
3570 IF FND_API.To_Boolean( p_commit ) THEN
3571 COMMIT;
3572 END IF;
3573
3574 EXCEPTION
3575 WHEN OTHERS THEN
3576 ROLLBACK TO Bis_Delete_Mass_Update_Data;
3577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3578 IF (x_msg_data IS NOT NULL) THEN
3579 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Delete_Mass_Update_Data ';
3580 ELSE
3581 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Delete_Mass_Update_Data ';
3582 END IF;
3583 END Delete_Mass_Update_Data;
3584
3585 /************************************************************************************
3586 -- API name : Insert_Mass_Update_Data
3587 -- Type : Private
3588 -- Saves the new scores and the dependent information
3589 -- This API assumes that scores and weights corresponding to the parameter
3590 -- combinations are completely deleted before calling this
3591 -- Logic :
3592 -- 1. Finds the list of filter,time level combinations that are
3593 -- defined for the first time.
3594 -- 2. Inserts the entries found in step1 into bis_weighted_measure_params
3595 -- 3. Fetch parameter ids for all the combinations(inserted + updated)
3596 -- 4. Fetch the default weights for the corresponding definition
3597 -- 5. For all the combinations found in Step3 insert weights
3598 -- 6. Fetch the weights ids that are defined in step5
3599 -- 7. For all those weight ids that are retrieved in step6 create scores.
3600 ************************************************************************************/
3601
3602 PROCEDURE Insert_Mass_Update_Data(
3603 p_commit IN VARCHAR2 := FND_API.G_FALSE
3604 ,p_weighted_measure_id IN NUMBER
3605 ,p_weighted_definition_id IN NUMBER
3606 ,p_dependent_measure_id IN NUMBER
3607 ,p_Time_Level IN VARCHAR2
3608 ,p_Filter_Level IN VARCHAR2
3609 ,p_Selected_Period_Ids IN BIS_TABLE_OF_VARCHAR
3610 ,p_Selected_DimObj_Ids IN BIS_TABLE_OF_VARCHAR
3611 ,p_Score_Values IN FND_TABLE_OF_NUMBER
3612 ,p_Lower_Ranges IN FND_TABLE_OF_NUMBER
3613 ,p_Upper_Ranges IN FND_TABLE_OF_NUMBER
3614 ,x_return_status OUT NOCOPY VARCHAR2
3615 ,x_msg_count OUT NOCOPY NUMBER
3616 ,x_msg_data OUT NOCOPY VARCHAR2
3617 )
3618 IS
3619 l_Create_Time_Values BIS_TABLE_OF_VARCHAR;
3620 l_Create_Filter_Values BIS_TABLE_OF_VARCHAR;
3621 l_depend_Measure_Ids FND_TABLE_OF_NUMBER;
3622 l_Param_Ids FND_TABLE_OF_NUMBER;
3623 l_Weight_Ids FND_TABLE_OF_NUMBER;
3624 l_Default_Weight NUMBER;
3625 l_Count NUMBER := 0;
3626 l_Is_Update BOOLEAN;
3627 l_User_Id NUMBER;
3628 l_Login_Id NUMBER;
3629 l_Weighted_Definition_Id bis_weighted_measure_params.weighted_definition_id%type;
3630
3631 BEGIN
3632
3633 SAVEPOINT SAVE_Insert_Mass_Update_Data;
3634 x_return_status := FND_API.G_RET_STS_SUCCESS;
3635
3636 l_Count := 0;
3637
3638 l_User_Id := FND_GLOBAL.user_id;
3639 l_Login_Id := FND_GLOBAL.login_id;
3640
3641
3642 SELECT
3643 *
3644 BULK COLLECT INTO
3645 l_Create_Filter_Values,
3646 l_Create_Time_Values
3647 FROM
3648 ( SELECT
3649 a.column_value filter_level_value_id,
3650 b.column_value time_level_value_id
3651 FROM
3652 (SELECT column_value FROM TABLE(CAST(p_Selected_DimObj_Ids AS BIS_TABLE_OF_VARCHAR))) A,
3653 (SELECT column_value FROM TABLE(CAST(p_Selected_Period_Ids AS BIS_TABLE_OF_VARCHAR))) B
3654 MINUS
3655 SELECT
3656 params.filter_level_value_id ,
3657 params.time_level_value_id
3658 FROM
3659 bis_weighted_measure_params params
3660 WHERE
3661 params.weighted_definition_id = p_weighted_definition_id AND
3662 params.time_level_value_id <> 'DEFAULT' AND
3663 params.filter_level_value_id <> 'DEFAULT') insertedParams;
3664
3665
3666 FORALL i IN 1..l_Create_Time_Values.COUNT
3667 INSERT INTO bis_weighted_measure_params(
3668 weighted_parameter_id
3669 ,weighted_definition_id
3670 ,time_level_value_id
3671 ,filter_level_value_id
3672 ,creation_date
3673 ,created_by
3674 ,last_update_date
3675 ,last_updated_by
3676 ,last_update_login
3677 ) VALUES (
3678 bis_weighted_measure_params_s.nextval
3679 ,p_weighted_definition_id
3680 ,l_Create_Time_Values(i)
3681 ,l_Create_Filter_Values(i)
3682 ,SYSDATE
3683 ,l_User_Id
3684 ,SYSDATE
3685 ,l_User_Id
3686 ,l_Login_Id
3687 );
3688
3689 SELECT
3690 params.weighted_parameter_id
3691 BULK COLLECT INTO
3692 l_Param_Ids
3693 FROM
3694 bis_weighted_measure_params params,
3695 (SELECT
3696 a.column_value filter_level_value_id,
3697 b.column_value time_level_value_id
3698 FROM
3699 (SELECT column_value FROM TABLE(CAST(p_Selected_DimObj_Ids AS BIS_TABLE_OF_VARCHAR))) A,
3700 (SELECT column_value FROM TABLE(CAST(p_Selected_Period_Ids AS BIS_TABLE_OF_VARCHAR))) B) curParams
3701 WHERE
3702 params.weighted_definition_id = p_weighted_definition_id AND
3703 params.time_level_value_id <> 'DEFAULT' AND
3704 params.filter_level_value_id <> 'DEFAULT' AND
3705 params.time_level_value_id = curParams.time_level_value_id AND
3706 params.filter_level_value_id = curParams.filter_level_value_id;
3707
3708 SELECT
3709 w.weight
3710 INTO
3711 l_Default_Weight
3712 FROM
3713 bis_weighted_measure_params p,
3714 bis_weighted_measure_weights w
3715 WHERE
3716 p.weighted_parameter_id = w.weighted_parameter_id AND
3717 p.time_level_value_id = 'DEFAULT' AND
3718 p.filter_level_value_id = 'DEFAULT' AND
3719 p.weighted_definition_id = p_weighted_definition_id AND
3720 w.dependent_measure_id = p_dependent_measure_id;
3721
3722
3723 FORALL i IN 1..l_Param_Ids.COUNT
3724 INSERT INTO bis_weighted_measure_weights(
3725 weight_id
3726 , weighted_parameter_id
3727 , dependent_measure_id
3728 , weight
3729 , creation_date
3730 , created_by
3731 , last_update_date
3732 , last_updated_by
3733 , last_update_login
3734 ) VALUES (
3735 bis_weighted_measure_weights_s.nextval
3736 , l_Param_Ids(i)
3737 , p_dependent_measure_id
3738 , l_Default_Weight
3739 , SYSDATE
3740 , l_User_Id
3741 , SYSDATE
3742 , l_User_Id
3743 , l_Login_Id
3744 );
3745
3746 SELECT
3747 weights.weight_id
3748 BULK COLLECT INTO
3749 l_Weight_Ids
3750 FROM
3751 bis_weighted_measure_weights weights
3752 WHERE
3753 weights.weighted_parameter_id IN (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER))) AND
3754 weights.dependent_measure_id = p_dependent_measure_id;
3755
3756
3757 FOR i in 1..p_Score_Values.COUNT LOOP
3758 IF (p_Lower_Ranges(i) IS NOT NULL OR p_Upper_Ranges(i) IS NOT NULL) THEN
3759 FORALL j IN 1..l_Weight_Ids.COUNT
3760 INSERT INTO bis_weighted_measure_scores(
3761 weight_id
3762 ,low_range
3763 ,high_range
3764 ,score
3765 ,creation_date
3766 ,created_by
3767 ,last_update_date
3768 ,last_updated_by
3769 ,last_update_login
3770 ) VALUES (
3771 l_Weight_Ids(j)
3772 ,p_Lower_Ranges(i)
3773 ,p_Upper_Ranges(i)
3774 ,p_Score_Values(i)
3775 ,SYSDATE
3776 ,l_User_Id
3777 ,SYSDATE
3778 ,l_User_Id
3779 ,l_Login_Id
3780 );
3781 END IF;
3782 END LOOP;
3783
3784 IF FND_API.To_Boolean( p_commit ) THEN
3785 COMMIT;
3786 END IF;
3787
3788 EXCEPTION
3789 WHEN OTHERS THEN
3790 ROLLBACK TO SAVE_Insert_Mass_Update_Data;
3791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3792 IF (x_msg_data IS NOT NULL) THEN
3793 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Insert_Mass_Update_Data ';
3794 ELSE
3795 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Insert_Mass_Update_Data';
3796 END IF;
3797 END Insert_Mass_Update_Data;
3798
3799 /************************************************************************************
3800 -- API name : Save_Mass_Update_Values
3801 -- Type : Public
3802 -- Saves the parameter combinations, corresponding weights and scores for the
3803 -- current depdendent measure
3804 -- Logic :
3805 -- 1. Delete the old weights,scores for the existing parameter combinations
3806 -- 2. Insert the new scores . Also inserts the weights by getting the
3807 -- weights from the default parameter combination.
3808 ************************************************************************************/
3809
3810 PROCEDURE Save_Mass_Update_Values(
3811 p_commit IN VARCHAR2
3812 ,p_weighted_measure_id IN NUMBER
3813 ,p_dependent_measure_id IN NUMBER
3814 ,p_Time_Level IN VARCHAR2
3815 ,p_Filter_Level IN VARCHAR2
3816 ,p_Selected_Period_Ids IN BIS_TABLE_OF_VARCHAR
3817 ,p_Selected_DimObj_Ids IN BIS_TABLE_OF_VARCHAR
3818 ,p_Score_Values IN FND_TABLE_OF_NUMBER
3819 ,p_Lower_Ranges IN FND_TABLE_OF_NUMBER
3820 ,p_Upper_Ranges IN FND_TABLE_OF_NUMBER
3821 ,x_return_status OUT NOCOPY VARCHAR2
3822 ,x_msg_count OUT NOCOPY NUMBER
3823 ,x_msg_data OUT NOCOPY VARCHAR2
3824 )
3825 IS
3826 l_Weighted_Definition_Id bis_weighted_measure_params.weighted_definition_id%type;
3827 BEGIN
3828
3829 SAVEPOINT BisSave_Mass_Update_Values_Pub;
3830 x_return_status := FND_API.G_RET_STS_SUCCESS;
3831
3832 SELECT
3833 defns.weighted_definition_id
3834 INTO
3835 l_Weighted_Definition_Id
3836 FROM
3837 bis_weighted_measure_defns defns
3838 WHERE
3839 defns.weighted_measure_id = p_weighted_measure_id AND
3840 defns.time_dimension_short_name || '+' || defns.time_dim_level_short_name = p_Time_Level;
3841
3842 Delete_Mass_Update_Data(
3843 p_commit => FND_API.G_FALSE
3844 , p_weighted_definition_id => l_Weighted_Definition_Id
3845 , p_dependent_measure_id => p_dependent_measure_id
3846 , p_Selected_Period_Ids => p_Selected_Period_Ids
3847 , p_Selected_DimObj_Ids => p_Selected_DimObj_Ids
3848 , x_return_status => x_return_status
3849 , x_msg_count => x_msg_count
3850 , x_msg_data => x_msg_data
3851 );
3852 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3854 END IF;
3855
3856 Insert_Mass_Update_Data (
3857 p_commit => FND_API.G_FALSE
3858 , p_weighted_measure_id => p_weighted_measure_id
3859 , p_dependent_measure_id => p_dependent_measure_id
3860 , p_weighted_definition_id => l_Weighted_Definition_Id
3861 , p_Time_Level => p_Time_Level
3862 , p_Filter_Level => p_Filter_Level
3863 , p_Selected_Period_Ids => p_Selected_Period_Ids
3864 , p_Selected_DimObj_Ids => p_Selected_DimObj_Ids
3865 , p_Score_Values => p_Score_Values
3866 , p_Lower_Ranges => p_Lower_Ranges
3867 , p_Upper_Ranges => p_Upper_Ranges
3868 , x_return_status => x_return_status
3869 , x_msg_count => x_msg_count
3870 , x_msg_data => x_msg_data
3871 );
3872 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3874 END IF;
3875
3876 IF FND_API.To_Boolean( p_commit ) THEN
3877 COMMIT;
3878 END IF;
3879
3880 EXCEPTION
3881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3882 ROLLBACK TO BisSave_Mass_Update_Values_Pub;
3883 IF (x_msg_data IS NULL) THEN
3884 FND_MSG_PUB.Count_And_Get
3885 ( p_encoded => FND_API.G_FALSE
3886 , p_count => x_msg_count
3887 , p_data => x_msg_data
3888 );
3889 END IF;
3890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3891 WHEN OTHERS THEN
3892 ROLLBACK TO BisSave_Mass_Update_Values_Pub;
3893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3894 IF (x_msg_data IS NOT NULL) THEN
3895 x_msg_data := x_msg_data||' -> BIS_WEIGHTED_MEASURE_PUB.Save_Mass_Update_Values ';
3896 ELSE
3897 x_msg_data := SQLERRM||' at BIS_WEIGHTED_MEASURE_PUB.Save_Mass_Update_Values ';
3898 END IF;
3899 END Save_Mass_Update_Values;
3900
3901 /************************************************************************************
3902 -- API name : Validate_Overwrite_Scores
3903 -- Type : Public
3904 -- Checks whether the current filter, time level combinations have scores defined
3905 -- previously.
3906 -- Logic :
3907 -- 1. Get all the parameter ids for filter-time combinations that were defined
3908 -- previously
3909 -- 2. Using the parameter ids in step 1 retrieve the weight_ids
3910 -- 3. Using the weight_ids in step 2 retrieve the scores
3911 ************************************************************************************/
3912 PROCEDURE Validate_Overwrite_Scores(
3913 p_weighted_measure_id IN NUMBER
3914 ,p_dependent_measure_id IN NUMBER
3915 ,p_Selected_Period_Ids IN BIS_TABLE_OF_VARCHAR
3916 ,p_Selected_DimObj_Ids IN BIS_TABLE_OF_VARCHAR
3917 ,x_Param_Count OUT NOCOPY NUMBER
3918 ) IS
3919
3920 l_Param_Ids FND_TABLE_OF_NUMBER;
3921
3922 BEGIN
3923 SELECT
3924 params.weighted_parameter_id
3925 BULK COLLECT INTO
3926 l_Param_Ids
3927 FROM
3928 bis_weighted_measure_params params,
3929 bis_weighted_measure_defns defns,
3930 (SELECT
3931 a.column_value filter_level_value_id,
3932 b.column_value time_level_value_id
3933 FROM
3934 (SELECT column_value FROM TABLE(CAST(p_Selected_DimObj_Ids AS BIS_TABLE_OF_VARCHAR))) A,
3935 (SELECT column_value FROM TABLE(CAST(p_Selected_Period_Ids AS BIS_TABLE_OF_VARCHAR))) B) curParams
3936 WHERE
3937 defns.weighted_measure_id = p_weighted_measure_id AND
3938 params.weighted_definition_id = defns.weighted_definition_id AND
3939 params.time_level_value_id <> 'DEFAULT' AND
3940 params.filter_level_value_id <> 'DEFAULT' AND
3941 params.time_level_value_id = curParams.time_level_value_id AND
3942 params.filter_level_value_id = curParams.filter_level_value_id;
3943
3944 IF l_Param_Ids.COUNT > 0 THEN
3945 SELECT
3946 COUNT(1)
3947 INTO
3948 x_Param_Count
3949 FROM
3950 bis_weighted_measure_scores a,
3951 bis_weighted_measure_weights b
3952 WHERE
3953 a.weight_id = b.weight_id AND
3954 b.weighted_parameter_id in (SELECT column_value FROM TABLE(CAST(l_Param_Ids AS FND_TABLE_OF_NUMBER))) AND
3955 b.dependent_measure_id = p_dependent_measure_id;
3956 END IF;
3957
3958 EXCEPTION
3959 WHEN OTHERS THEN
3960 x_Param_Count := 0;
3961 END Validate_Overwrite_Scores;
3962
3963 END BIS_WEIGHTED_MEASURE_PUB;