[Home] [Help]
PACKAGE BODY: APPS.BIS_INDICATOR_RESPS_PKG
Source
1 PACKAGE BODY BIS_INDICATOR_RESPS_PKG AS
2 /* $Header: BISINRSB.pls 115.8 2003/08/15 22:21:42 wleung noship $ */
3
4
5 PROCEDURE check_security_exists
6 ( p_responsibility_id IN NUMBER
7 , p_target_level_id IN NUMBER
8 , x_exists OUT NOCOPY BOOLEAN
9 );
10
11
12 PROCEDURE resp_value_id_conversion
13 ( p_responsibility_key IN VARCHAR
14 , x_responsibility_id OUT NOCOPY NUMBER
15 , x_return_status OUT NOCOPY VARCHAR
16 , x_return_msg OUT NOCOPY VARCHAR
17 ) ;
18
19
20 PROCEDURE tgt_level_value_id_conversion
21 ( p_target_level_short_name IN VARCHAR
22 , x_target_level_id OUT NOCOPY NUMBER
23 , x_return_status OUT NOCOPY VARCHAR
24 , x_return_msg OUT NOCOPY VARCHAR
25 );
26
27 PROCEDURE Update_Row(
28 x_indicator_resp_id in number
29 ,x_target_level_id in number
30 ,x_responsibility_id in number
31 ,x_last_updated_by in number
32 ,x_last_update_date in date
33 ,x_last_update_login in number
34 );
35
36 -----------------------------------------------------------------------------------
37 -----------------------------------------------------------------------------------
38
39
40
41 PROCEDURE Load_Row
42 ( p_target_level_short_name IN VARCHAR
43 , p_responsibility_short_name IN VARCHAR
44 , p_created_by IN NUMBER
45 , p_last_updated_by IN NUMBER
46 , p_owner IN VARCHAR
47 , x_return_status OUT NOCOPY VARCHAR
48 , x_return_msg OUT NOCOPY VARCHAR
49 ) IS
50
51 l_responsibility_id NUMBER;
52 l_target_level_id NUMBER;
53 l_row_id VARCHAR2(30);
54 l_return_status1 VARCHAR2(30);
55 l_return_status2 VARCHAR2(30);
56 l_return_msg1 fnd_new_messages.message_text%TYPE; -- VARCHAR2(2000);
57 l_return_msg2 VARCHAR2(80);
58 l_exists BOOLEAN;
59 l_indicator_resp_id NUMBER;
60 l_login_id NUMBER;
61 l_user_id NUMBER;
62 l_created_by NUMBER;
63 l_last_updated_by NUMBER;
64 -- l_temp_msg VARCHAR2(100);
65 l_error_msg VARCHAR2(100);
66
67 BEGIN
68
69 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 1 ' ) ;
70
71 resp_value_id_conversion
72 ( p_responsibility_key => p_responsibility_short_name
73 ,x_responsibility_id => l_responsibility_id
74 ,x_return_status => l_return_status1
75 ,x_return_msg => l_return_msg1
76 ) ;
77
78 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 1.1 ' || l_return_status1 ) ;
79
80 tgt_level_value_id_conversion
81 ( p_target_level_short_name => p_target_level_short_name
82 , x_target_level_id => l_target_level_id
83 , x_return_status => l_return_status2
84 , x_return_msg => l_return_msg2
85 );
86
87 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 1.2 ' ) ;
88
89 IF ( (l_return_status1 = 'S') AND (l_return_status2 = 'S') ) THEN -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 2 ' ) ;
90
91 /*
92 SELECT BIS_INDICATOR_RESPS_S.nextval
93 INTO l_indicator_resp_id
94 FROM DUAL;
95 */
96
97 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
98 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
99 ELSE
100 l_user_id := fnd_global.user_id;
101 END IF;
102
103 IF (p_created_by IS NULL) THEN
104 l_created_by := l_user_id;
105 ELSE
106 l_created_by := p_created_by;
107 END IF;
108
109 IF (p_last_updated_by IS NULL) THEN
110 l_last_updated_by := l_user_id;
111 ELSE
112 l_last_updated_by := p_last_updated_by;
113 END IF;
114
115
116 l_login_id := fnd_global.LOGIN_ID;
117
118
119 check_security_exists
120 ( p_responsibility_id => l_responsibility_id
121 , p_target_level_id => l_target_level_id
122 , x_exists => l_exists
123 );
124
125
126 IF ( l_exists = FALSE ) THEN -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 3 ' ) ;
127
128 Insert_Row
129 (
130 x_rowid => l_row_id
131 ,x_indicator_resp_id => l_indicator_resp_id
132 ,x_target_level_id => l_target_level_id
133 ,x_responsibility_id => l_responsibility_id
134 ,x_created_by => l_created_by
135 ,x_creation_date => sysdate
136 ,x_last_updated_by => l_last_updated_by
137 ,x_last_update_date => sysdate
138 ,x_last_update_login => l_login_id
139 );
140
141 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 3.1 ' ) ;
142
143 ELSE
144
145 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 4 ' ) ;
146
147 Update_Row(
148 x_indicator_resp_id => l_target_level_id
149 ,x_target_level_id => l_target_level_id
150 ,x_responsibility_id => l_responsibility_id
151 ,x_last_updated_by => l_last_updated_by
152 ,x_last_update_date => sysdate
153 ,x_last_update_login => l_login_id
154 );
155
156 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 4.1 ' ) ;
157
158 END IF;
159
160 ELSE
161
162
163 l_error_msg := substr(bis_utilities_pvt.Get_FND_Message (
164 p_message_name => 'BISPMF_SCRTY_UPLD_FAIL'
165 , p_msg_param1 => 'RESP_KEY'
166 , p_msg_param1_val => p_responsibility_short_name
167 , p_msg_param2 => 'SUM_LVL'
168 , p_msg_param2_val => p_target_level_short_name
169 ), 1, 100) ;
170
171 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
172
173 END IF;
174
175 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside load row 5 ' ) ;
176
177 EXCEPTION
178
179 WHEN OTHERS THEN
180
181 l_error_msg := bis_utilities_pvt.Get_FND_Message (
182 p_message_name => 'BISPMF_SCRTY_ERR_UNHNDLD'
183 , p_msg_param1 => 'RESP_KEY'
184 , p_msg_param1_val => p_responsibility_short_name
185 , p_msg_param2 => 'SUM_LVL'
186 , p_msg_param2_val => p_target_level_short_name
187 ) ;
188
189 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
190
191
192 END;
193
194
195
196 PROCEDURE check_security_exists
197 ( p_responsibility_id IN NUMBER
198 , p_target_level_id IN NUMBER
199 , x_exists OUT NOCOPY BOOLEAN
200 ) IS
201
202 l_exists BOOLEAN;
203 l_count NUMBER;
204
205 BEGIN
206
207 SELECT count(1)
208 INTO l_count
209 FROM bis_indicator_resps
210 WHERE target_level_id = p_target_level_id
211 AND responsibility_id = p_responsibility_id; -- BIS_UTILITIES_PUB.put_line(p_text => ' l_count = ' || l_count ) ;
212
213 IF ( l_count = 0 ) THEN
214 x_exists := FALSE;
215 ELSE -- IF (l_count = 1 ) THEN
216 x_exists := TRUE;
217 -- ELSE
218 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in CHECK_SECURITY_EXISTS: One Summary level has the same responsibility more than once ' ) ;
219 END IF;
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in CHECK_SECURITY_EXISTS: in when others ' ) ;
224 x_exists := TRUE; -- We don't want to upload it.
225
226 END;
227
228
229
230 PROCEDURE resp_value_id_conversion
231 ( p_responsibility_key IN VARCHAR
232 , x_responsibility_id OUT NOCOPY NUMBER
233 , x_return_status OUT NOCOPY VARCHAR
234 , x_return_msg OUT NOCOPY VARCHAR
235 ) IS
236
237 l_responsibility_id NUMBER;
238 l_temp_msg VARCHAR2(100);
239 l_error_msg VARCHAR2(100);
240
241 BEGIN
242
243 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside resp val id conv 1 ' || p_responsibility_key ) ;
244
245 SELECT responsibility_id
246 INTO l_responsibility_id
247 FROM fnd_responsibility
248 WHERE responsibility_key = p_responsibility_key ;
249
250 -- BIS_UTILITIES_PUB.put_line(p_text => ' inside resp val id conv 2 ' ) ;
251
252 x_responsibility_id := l_responsibility_id ;
253 x_return_status := 'S';
254
255
256 EXCEPTION
257
258 WHEN NO_DATA_FOUND THEN
259
260 l_error_msg := bis_utilities_pvt.Get_FND_Message (
261 p_message_name => 'BISPMF_SCRTY_RESP_NO_EXIST'
262 , p_msg_param1 => 'RESP_KEY'
263 , p_msg_param1_val => p_responsibility_key
264 ) ;
265
266 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
267
268 x_return_msg := l_error_msg ;
269 x_return_status := 'E';
270
271 WHEN OTHERS THEN
272
273 l_error_msg := bis_utilities_pvt.Get_FND_Message (
274 p_message_name => 'BISPMF_SCRTY_RESP_NO_EXIST'
275 , p_msg_param1 => 'RESP_KEY'
276 , p_msg_param1_val => p_responsibility_key
277 ) ;
278
279 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
280
281 x_return_msg := l_error_msg ;
282 x_return_status := 'U';
283
284 END;
285
286
287
288
289 PROCEDURE tgt_level_value_id_conversion
290 ( p_target_level_short_name IN VARCHAR
291 , x_target_level_id OUT NOCOPY NUMBER
292 , x_return_status OUT NOCOPY VARCHAR
293 , x_return_msg OUT NOCOPY VARCHAR
294 ) IS
295
296 l_target_level_id NUMBER;
297 l_temp_msg VARCHAR2(100);
298 l_error_msg VARCHAR2(100);
299
300 BEGIN
301
302 SELECT target_level_id
303 INTO l_target_level_id
304 FROM bis_target_levels
305 WHERE short_name = p_target_level_short_name ;
306
307 x_target_level_id := l_target_level_id ;
308 x_return_status := 'S' ;
309
310
311 EXCEPTION
312
313 WHEN NO_DATA_FOUND THEN
314
315 l_error_msg := substr(bis_utilities_pvt.Get_FND_Message (
316 p_message_name => 'BISPMF_SCRTY_SUMLVL_NO_EXIST'
317 , p_msg_param1 => 'SUM_LVL'
318 , p_msg_param1_val => p_target_level_short_name
319 ),1,80) ;
320
321 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
322
323 x_return_msg := l_error_msg ;
324 x_return_status := 'E';
325
326 WHEN OTHERS THEN
327
328 l_error_msg := bis_utilities_pvt.Get_FND_Message (
329 p_message_name => 'BISPMF_SCRTY_SUMLVL_NO_EXIST'
330 , p_msg_param1 => 'SUM_LVL'
331 , p_msg_param1_val => p_target_level_short_name
332 ) ;
333
334 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
335
336 x_return_msg := l_error_msg ;
337 x_return_status := 'U';
338
339 END;
340
341
342 ---------------------------------------------------------------------------------
343
344 PROCEDURE Insert_Row(
345 x_rowid in out NOCOPY varchar2
346 ,x_indicator_resp_id in out NOCOPY number
347 ,x_target_level_id in number
348 ,x_responsibility_id in number
349 ,x_created_by in number
350 ,x_creation_date in date
351 ,x_last_updated_by in number
352 ,x_last_update_date in date
353 ,x_last_update_login in number
354 )
355 IS
356
357 CURSOR CROWID IS SELECT rowid FROM bis_indicator_resps
358 WHERE indicator_resp_id = x_indicator_resp_id;
359
360 CURSOR CID IS SELECT bis_indicator_resps_s.nextval
361 FROM sys.dual;
362 BEGIN
363 Open CID;
364 Fetch CID into x_indicator_resp_id;
365 if (CID%NOTFOUND) then
366 CLOSE CID;
367 RAISE NO_DATA_FOUND;
368 end if;
369
370 Close CID;
371
372 INSERT INTO bis_indicator_resps (
373 indicator_resp_id
374 ,target_level_id
375 ,responsibility_id
376 ,created_by
377 ,creation_date
378 ,last_updated_by
379 ,last_update_date
380 ,last_update_login
381 )
382 Values
383 (
384 x_indicator_resp_id
385 ,x_target_level_id
386 ,x_responsibility_id
387 ,x_created_by
388 ,x_creation_date
389 ,x_last_updated_by
390 ,x_last_update_date
391 ,x_last_update_login
392 );
393
394 Open CROWID;
395 Fetch CROWID into x_rowid;
396 if (CROWID%NOTFOUND) then
397 CLOSE CROWID;
398 RAISE NO_DATA_FOUND;
399 end if;
400 CLOSE CROWID;
401
402 END Insert_Row;
403
404
408 x_rowid in varchar2
405 ----------------------------------------------------
406
407 PROCEDURE Lock_Row(
409 ,x_indicator_resp_id in number
410 ,x_target_level_id in number
411 ,x_responsibility_id in number
412 ,x_created_by in number
413 ,x_creation_date in date
414 ,x_last_updated_by in number
415 ,x_last_update_date in date
416 ,x_last_update_login in number
417 )
418 IS
419
420 CURSOR C IS
421 SELECT *
422 FROM bis_indicator_resps
423 WHERE rowid = x_rowid
424 FOR UPDATE OF indicator_resp_id NOWAIT;
425
426 Recinfo C%ROWTYPE;
427 BEGIN
428 Open C;
429 Fetch C into Recinfo;
430 if (C%NOTFOUND) then
431 Close C;
432 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
433 APP_EXCEPTION.Raise_Exception;
434 end if;
435 Close C;
436 if (
437 (Recinfo.indicator_resp_id = x_indicator_resp_id)
438 AND (Recinfo.target_level_id = x_target_level_id)
439 AND (Recinfo.responsibility_id = x_responsibility_id)
440 AND (Recinfo.created_by = x_created_by)
441 AND (Recinfo.creation_date = x_creation_date)
442 AND (Recinfo.last_updated_by = x_last_updated_by)
443 AND (Recinfo.last_update_date = x_last_update_date)
444 AND ( (Recinfo.last_update_login = x_last_update_login)
445 OR ( (recinfo.last_update_login IS NULL)
446 AND(x_last_update_login IS NULL)))
447 ) then
448 return;
449 else
450 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
451 APP_EXCEPTION.Raise_Exception;
452 end if;
453 END Lock_Row;
454 ----------------------------------------------------
455
456
457 PROCEDURE Update_Row(
458 x_rowid in varchar2
459 ,x_indicator_resp_id in number
460 ,x_target_level_id in number
461 ,x_responsibility_id in number
462 ,x_created_by in number
463 ,x_creation_date in date
464 ,x_last_updated_by in number
465 ,x_last_update_date in date
466 ,x_last_update_login in number
467 )
468 IS
469
470 l_temp_msg VARCHAR2(100);
471 l_error_msg VARCHAR2(100);
472
473 BEGIN
474 UPDATE bis_indicator_resps
475 SET
476 target_level_id = x_target_level_id
477 ,responsibility_id = x_responsibility_id
478 ,last_updated_by = x_last_updated_by
479 ,last_update_date = x_last_update_date
480 ,last_update_login = x_last_update_login
481 WHERE rowid = x_rowid;
482 if (SQL%NOTFOUND) then
483 RAISE NO_DATA_FOUND;
484 end if;
485
486 END Update_Row;
487
488 ----------------------------------------------------
489
490 PROCEDURE Update_Row(
491 x_indicator_resp_id in number
492 ,x_target_level_id in number
493 ,x_responsibility_id in number
494 ,x_last_updated_by in number
495 ,x_last_update_date in date
496 ,x_last_update_login in number
497 )
498 IS
499
500 l_temp_msg VARCHAR2(100);
501 l_error_msg VARCHAR2(100);
502
503 BEGIN
504 UPDATE bis_indicator_resps
505 SET
506 last_updated_by = x_last_updated_by
507 ,last_update_date = x_last_update_date
508 ,last_update_login = x_last_update_login
509 WHERE
510 target_level_id = x_target_level_id
511 AND responsibility_id = x_responsibility_id ;
512
513 if (SQL%NOTFOUND) then
514 RAISE NO_DATA_FOUND;
515 end if;
516
517 END Update_Row;
518
519 ------------------------------------------------------------------------
520
521 PROCEDURE Delete_Row(
522 x_rowid in varchar2
523 )
524 IS
525 BEGIN
526 DELETE FROM bis_indicator_resps
527 WHERE rowid = x_rowid;
528
529 if (SQL%NOTFOUND) then
530 RAISE NO_DATA_FOUND;
531 end if;
532 END Delete_Row;
533 ----------------------------------------------------
534
535 PROCEDURE Check_Unique(
536 x_rowid in varchar2
537 ,x_target_level_id in number
538 ,x_responsibility_id in number
539 )
540 IS
541 CURSOR C IS
542 SELECT COUNT(1)
543 FROM bis_indicator_resps
544 WHERE target_level_id = x_target_level_id
545 AND responsibility_id = x_responsibility_id
546 AND ((x_rowid is null) OR (rowid <> x_rowid));
547
548 dummy number;
549 BEGIN
550 OPEN C;
551 Fetch C into dummy;
552 Close C;
553
554 if (dummy >= 1) then
555 fnd_message.set_name('BIS', 'BIS_DUP_INDRESP');
556 APP_EXCEPTION.Raise_Exception;
557 end if;
558 END Check_Unique;
559 ----------------------------------------------------
560
561
562 END BIS_INDICATOR_RESPS_PKG;