[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_ACCESS_PKG
Source
1 PACKAGE BODY IGC_CC_ACCESS_PKG AS
2 /*$Header: IGCCACCB.pls 120.3.12000000.1 2007/08/20 12:10:32 mbremkum ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_ACCESS_PKG';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1) := 'N' ;
8
9
10 /*=======================================================================+
11 | PROCEDURE Insert_Row |
12 +=======================================================================*/
13 PROCEDURE Insert_Row
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22
23 p_row_id IN OUT NOCOPY VARCHAR2,
24 p_CC_HEADER_ID NUMBER,
25 p_USER_ID NUMBER,
26 p_CC_GROUP_ID NUMBER,
27 p_CC_ACCESS_ID IN OUT NOCOPY NUMBER,
28 p_CC_ACCESS_LEVEL VARCHAR2,
29 p_CC_ACCESS_TYPE VARCHAR2,
30 p_LAST_UPDATE_DATE DATE,
31 p_LAST_UPDATED_BY NUMBER,
32 p_CREATION_DATE DATE,
33 p_CREATED_BY NUMBER,
34 p_LAST_UPDATE_LOGIN NUMBER
35 )
36 IS
37
38 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
39 l_api_version CONSTANT NUMBER := 1.0;
40
41 CURSOR C IS
42 SELECT rowid
43 FROM igc_cc_access
44 WHERE cc_access_id = p_cc_access_id;
45
46 BEGIN
47
48 SAVEPOINT Insert_Row_Pvt ;
49
50 IF NOT FND_API.Compatible_API_Call ( l_api_version,
51 p_api_version,
52 l_api_name,
53 G_PKG_NAME )
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
56 END IF;
57
58
59 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
60 FND_MSG_PUB.initialize ;
61 END IF;
62
63 x_return_status := FND_API.G_RET_STS_SUCCESS ;
64
65 INSERT INTO igc_cc_access(
66 CC_HEADER_ID,
67 USER_ID,
68 CC_GROUP_ID,
69 CC_ACCESS_ID,
70 CC_ACCESS_LEVEL,
71 CC_ACCESS_TYPE,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_LOGIN
77 )
78 VALUES
79 (
80 p_CC_HEADER_ID,
81 p_USER_ID,
82 p_CC_GROUP_ID,
83 NVL(p_CC_ACCESS_ID, igc_cc_access_s.NEXTVAL),
84 p_CC_ACCESS_LEVEL,
85 p_CC_ACCESS_TYPE,
86 p_LAST_UPDATE_DATE,
87 p_LAST_UPDATED_BY,
88 p_CREATION_DATE,
89 p_CREATED_BY,
90 p_LAST_UPDATE_LOGIN
91 )
92 RETURNING cc_access_id INTO p_CC_ACCESS_ID;
93 OPEN C;
94 FETCH C INTO p_row_id;
95 IF (C%NOTFOUND) THEN
96 CLOSE C;
97 RAISE FND_API.G_EXC_ERROR ;
98 END IF;
99 CLOSE C;
100
101
102 IF FND_API.To_Boolean ( p_commit ) THEN
103 COMMIT WORK;
104 END iF;
105
106 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
107 p_data => x_msg_data );
108
109 EXCEPTION
110
111 WHEN FND_API.G_EXC_ERROR THEN
112
113 ROLLBACK TO Insert_Row_Pvt ;
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
116 p_data => x_msg_data );
117
118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
119
120 ROLLBACK TO Insert_Row_Pvt ;
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
123 p_data => x_msg_data );
124
125 WHEN OTHERS THEN
126
127 ROLLBACK TO Insert_Row_Pvt ;
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129
130 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
131 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
132 l_api_name);
133 END if;
134
135 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
136 p_data => x_msg_data );
137
138 END Insert_Row;
139 /*-------------------------------------------------------------------------*/
140
141
142
143 /*==========================================================================+
144 | PROCEDURE Lock_Row |
145 +==========================================================================*/
146 PROCEDURE Lock_Row
147 (
148 p_api_version IN NUMBER,
149 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
150 p_commit IN VARCHAR2 := FND_API.G_FALSE,
151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155
156 p_row_id VARCHAR2,
157 p_CC_HEADER_ID NUMBER,
158 p_USER_ID NUMBER,
159 p_CC_GROUP_ID NUMBER,
160 p_CC_ACCESS_ID NUMBER,
161 p_CC_ACCESS_LEVEL VARCHAR2,
162 p_CC_ACCESS_TYPE VARCHAR2,
163
164 p_row_locked OUT NOCOPY VARCHAR2
165 )
166 IS
167
168 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
169 l_api_version CONSTANT NUMBER := 1.0;
170
171 Counter NUMBER;
172 CURSOR C IS
173 SELECT *
174 FROM igc_cc_access
175 WHERE rowid = p_row_id
176 FOR UPDATE NOWAIT;
177 Recinfo C%ROWTYPE;
178
179 BEGIN
180
181 SAVEPOINT Lock_Row_Pvt ;
182
183 IF NOT FND_API.Compatible_API_Call ( l_api_version,
184 p_api_version,
185 l_api_name,
186 G_PKG_NAME )
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
189 END IF;
190
191
192 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
193 FND_MSG_PUB.initialize ;
194 END IF;
195
196 x_return_status := FND_API.G_RET_STS_SUCCESS ;
197 p_row_locked := FND_API.G_TRUE ;
198
199 OPEN C;
200
201 FETCH C INTO Recinfo;
202 IF (C%NOTFOUND) then
203 CLOSE C;
204 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
205 FND_MSG_PUB.Add;
206 RAISE FND_API.G_EXC_ERROR ;
207 END IF;
208 CLOSE C;
209
210 IF (
211 (recinfo.CC_HEADER_ID = p_CC_HEADER_ID)
212 AND (recinfo.CC_ACCESS_ID = p_CC_ACCESS_ID)
213 /* AND ((recinfo.USER_ID = p_USER_ID)
214 OR ((recinfo.USER_ID IS NULL)
215 AND (p_USER_ID IS NULL)))
216 AND ((recinfo.CC_GROUP_ID = p_CC_GROUP_ID)
217 OR ((recinfo.CC_GROUP_ID IS NULL)
218 AND (p_CC_GROUP_ID IS NULL))) */
219 AND ((recinfo.CC_ACCESS_LEVEL = p_CC_ACCESS_LEVEL)
220 OR ((recinfo.CC_ACCESS_LEVEL IS NULL)
221 AND (p_CC_ACCESS_LEVEL IS NULL)))
222 AND ((recinfo.CC_ACCESS_TYPE = p_CC_ACCESS_TYPE)
223 OR ((recinfo.CC_ACCESS_TYPE IS NULL)
224 AND (p_CC_ACCESS_TYPE IS NULL)))
225 )
226
227 THEN
228 Null;
229 ELSE
230 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
231 FND_MSG_PUB.Add;
232 RAISE FND_API.G_EXC_ERROR ;
233 END IF;
234
235
236 IF FND_API.To_Boolean ( p_commit ) THEN
237 COMMIT WORK;
238 END iF;
239
240 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
241 p_data => x_msg_data );
242
243 EXCEPTION
244
245 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
246
247 ROLLBACK TO Lock_Row_Pvt ;
248 p_row_locked := FND_API.G_FALSE;
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
251 p_data => x_msg_data );
252
253 WHEN FND_API.G_EXC_ERROR THEN
254
255 ROLLBACK TO Lock_Row_Pvt ;
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
258 p_data => x_msg_data );
259
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261
262 ROLLBACK TO Lock_Row_Pvt ;
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
265 p_data => x_msg_data );
266
267 WHEN OTHERS THEN
268
269 ROLLBACK TO Lock_Row_Pvt ;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271
272 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
273 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
274 l_api_name);
275 END if;
276
277 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
278 p_data => x_msg_data );
279
280 END Lock_Row;
281 /* ----------------------------------------------------------------------- */
282
283
284
285
286 /*==========================================================================+
287 | PROCEDURE Update_Row |
288 +==========================================================================*/
289 PROCEDURE Update_Row
290 (
291 p_api_version IN NUMBER,
292 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
293 p_commit IN VARCHAR2 := FND_API.G_FALSE,
294 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298
299 p_row_id VARCHAR2,
300 p_CC_HEADER_ID NUMBER,
301 p_USER_ID NUMBER,
302 p_CC_GROUP_ID NUMBER,
303 p_CC_ACCESS_ID NUMBER,
304 p_CC_ACCESS_LEVEL VARCHAR2,
305 p_CC_ACCESS_TYPE VARCHAR2,
306 p_LAST_UPDATE_DATE DATE,
307 p_LAST_UPDATED_BY NUMBER,
308 p_LAST_UPDATE_LOGIN NUMBER
309 )
310 IS
311
312 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
313 l_api_version CONSTANT NUMBER := 1.0;
314
315 BEGIN
316
317 SAVEPOINT Update_Row_Pvt ;
318
319 IF NOT FND_API.Compatible_API_Call ( l_api_version,
320 p_api_version,
321 l_api_name,
322 G_PKG_NAME )
323 THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
325 END IF;
326
327
328 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
329 FND_MSG_PUB.initialize ;
330 END IF;
331
332 x_return_status := FND_API.G_RET_STS_SUCCESS ;
333
334
335 UPDATE igc_cc_access
336 SET
337 CC_HEADER_ID = p_CC_HEADER_ID,
338 USER_ID = p_USER_ID,
339 CC_GROUP_ID = p_CC_GROUP_ID,
340 CC_ACCESS_ID = p_CC_ACCESS_ID,
341 CC_ACCESS_LEVEL = p_CC_ACCESS_LEVEL,
342 CC_ACCESS_TYPE = p_CC_ACCESS_TYPE,
343 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
344 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
346 WHERE rowid = p_row_id;
347
348 IF (SQL%NOTFOUND) THEN
349 RAISE NO_DATA_FOUND ;
350 END IF;
351
352
353 IF FND_API.To_Boolean ( p_commit ) THEN
354 COMMIT WORK;
355 END iF;
356
357 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
358 p_data => x_msg_data );
359
360 EXCEPTION
361
362 WHEN FND_API.G_EXC_ERROR THEN
363
364 ROLLBACK TO Update_Row_Pvt ;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
367 p_data => x_msg_data );
368
369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
370
371 ROLLBACK TO Update_Row_Pvt ;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 WHEN OTHERS THEN
373 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
374 p_data => x_msg_data );
375
377
378 ROLLBACK TO Update_Row_Pvt ;
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380
381 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
382 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
383 l_api_name);
384 END if;
385
386 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
387 p_data => x_msg_data );
388
389 END Update_Row;
390 /* ----------------------------------------------------------------------- */
391
392
393
394
395 /*==========================================================================+
396 | PROCEDURE Delete_Row |
397 +==========================================================================*/
398 PROCEDURE Delete_Row
399 (
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
402 p_commit IN VARCHAR2 := FND_API.G_FALSE,
403 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2,
407
408 p_row_id IN VARCHAR2
409 )
410 IS
411
412 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
413 l_api_version CONSTANT NUMBER := 1.0;
414
415 l_return_status VARCHAR2(1) ;
416 l_msg_count NUMBER ;
417 l_msg_data VARCHAR2(2000) ;
418
419 BEGIN
420
421 SAVEPOINT Delete_Row_Pvt ;
422
423 IF NOT FND_API.Compatible_API_Call ( l_api_version,
424 p_api_version,
425 l_api_name,
426 G_PKG_NAME )
427 THEN
428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
429 END IF;
430
431
432 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
433 FND_MSG_PUB.initialize ;
434 END IF ;
435
436 x_return_status := FND_API.G_RET_STS_SUCCESS ;
437
438 -- Deleting the record in psb_dss_fdi_filters.
439
440 DELETE FROM igc_cc_access
441 WHERE rowid = p_row_id;
442
443
444 IF (SQL%NOTFOUND) THEN
445 RAISE NO_DATA_FOUND ;
446 END IF;
447
448
449 IF FND_API.To_Boolean ( p_commit ) THEN
450 COMMIT WORK;
451 END iF;
452
453 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
454 p_data => x_msg_data );
455
456 EXCEPTION
457
458 WHEN FND_API.G_EXC_ERROR THEN
459
460 ROLLBACK TO Delete_Row_Pvt ;
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
463 p_data => x_msg_data );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466
467 ROLLBACK TO Delete_Row_Pvt ;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
470 p_data => x_msg_data );
471
472 WHEN OTHERS THEN
473
474 ROLLBACK TO Delete_Row_Pvt ;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476
477 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
478 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
479 l_api_name);
480 END if;
481
482 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
483 p_data => x_msg_data );
484
485 END Delete_Row;
486
487
488
489 /*==========================================================================+
490 | PROCEDURE Check_Unique |
491 +==========================================================================*/
492 PROCEDURE Check_Unique
493 (
494 p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_commit IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 --
502 p_row_id IN VARCHAR2,
503 p_cc_access_id IN NUMBER,
504 p_return_value IN OUT NOCOPY VARCHAR2
505 )
506 IS
507
508 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
509 l_api_version CONSTANT NUMBER := 1.0;
510
511 l_tmp VARCHAR2(1);
512
513 CURSOR c IS
514 SELECT '1'
515 FROM igc_cc_access
516 WHERE cc_access_id = p_cc_access_id
517 AND (
518 p_row_id IS NULL
519 OR
520 rowid <> p_row_id
521 );
522
523 BEGIN
524
525 SAVEPOINT Check_Unique_Pvt ;
526
527 IF NOT FND_API.Compatible_API_Call ( l_api_version,
528 p_api_version,
529 l_api_name,
530 G_PKG_NAME )
531 THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
533 END IF;
534
535
539
536 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
537 FND_MSG_PUB.initialize ;
538 END IF;
540 x_return_status := FND_API.G_RET_STS_SUCCESS ;
541
542 -- Checking the Psb_dss_fni_data_items table for references.
543 OPEN c;
544 FETCH c INTO l_tmp;
545
546 -- p_Return_Value specifies whether unique value exists or not.
547 IF l_tmp IS NULL THEN
548 p_Return_Value := 'FALSE';
549 ELSE
550 p_Return_Value := 'TRUE';
551 END IF;
552
553 CLOSE c;
554
555 IF FND_API.To_Boolean ( p_commit ) THEN
556 COMMIT WORK;
557 END iF;
558
559 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
560 p_data => x_msg_data );
561
562 EXCEPTION
563
564 WHEN FND_API.G_EXC_ERROR THEN
565
566 ROLLBACK TO Check_Unique_Pvt ;
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
569 p_data => x_msg_data );
570
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572
573 ROLLBACK TO Check_Unique_Pvt ;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
576 p_data => x_msg_data );
577
578 WHEN OTHERS THEN
579
580 ROLLBACK TO Check_Unique_Pvt ;
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582
583 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
584 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
585 l_api_name);
586 END if;
587
588 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
589 p_data => x_msg_data );
590
591 END Check_Unique;
592 /* ----------------------------------------------------------------------- */
593
594 Function get_access_level
595 (
596 p_header_id IN NUMBER,
597 p_user_id IN NUMBER,
598 p_preparer_id IN NUMBER,
599 p_owner_id IN NUMBER
600 ) RETURN CHAR IS
601
602 l_u_access_level char;
603 l_g_access_level char;
604
605 Begin
606
607
608 if p_user_id = p_preparer_id then
609 return('M');
610 end if;
611
612 if p_user_id = p_owner_id then
613 return('M');
614 end if;
615
616 Begin
617 Select max(cc_access_level)
618 Into l_u_access_level
619 From IGC_CC_ACCESS
620 Where user_id = p_user_id
621 and cc_access_type like 'U'
622 and cc_header_id = p_header_id
623 Group By cc_header_id;
624
625 Exception When No_Data_Found then
626 Null;
627 End;
628
629 Begin
630 Select max(cc_access_level)
631 Into l_g_access_level
632 From IGC_CC_ACCESS a,
633 IGC_CC_GROUP_USERS b
634 Where b.user_id = p_user_id
635 and a.cc_access_type like 'G'
636 and a.cc_header_id = p_header_id
637 and a.cc_group_id = b.cc_group_id
638 Group By cc_header_id;
639
640 Exception When No_Data_Found then
641 Null;
642 End;
643
644 if l_g_access_level = 'M' then
645 return('M');
646 elsif l_u_access_level = 'M' then
647 return('M');
648 elsif (l_u_access_level = 'R') OR (l_g_access_level = 'R') then
649 return('R');
650 else
651 return('N');
652 end if;
653
654
655 End get_access_level;
656
657
658 END IGC_CC_ACCESS_PKG;