[Home] [Help]
PACKAGE BODY: APPS.CN_SFP_SRP_UTIL_PVT
Source
1 PACKAGE BODY CN_SFP_SRP_UTIL_PVT AS
2 -- $Header: cnvsfsrb.pls 115.4 2004/01/27 02:24:07 fmburu noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SFP_SRP_UTIL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsfsrb.pls';
6
7 FUNCTION contains(value NUMBER, collection DBMS_SQL.NUMBER_TABLE)
8 RETURN BOOLEAN
9 IS
10 BEGIN
11 IF collection IS NULL THEN
12 RETURN FALSE ;
13 END IF ;
14 IF collection.count > 0 THEN
15 FOR i IN collection.first..collection.last LOOP
16 IF value = collection(i) THEN
17 RETURN TRUE;
18 END IF ;
19 END LOOP ;
20 END IF ;
21 RETURN FALSE ;
22 END ;
23
24 FUNCTION getString(prefix VARCHAR2,collection DBMS_SQL.NUMBER_TABLE, cond BOOLEAN)
25 RETURN VARCHAR2
26 IS
27 l_ret_val VARCHAR2(2000) := NULL ;
28 l_count NUMBER := 0 ;
29 BEGIN
30 IF collection IS NULL THEN
31 RETURN NULL ;
32 END IF ;
33 IF collection.count > 0 THEN
34 l_count := l_count + 1 ;
35 FOR i IN collection.FIRST..collection.LAST LOOP
36 IF i > 1 THEN
37 l_ret_val := l_ret_val || ',' ;
38 END IF ;
39 IF cond THEN
40 l_ret_val := l_ret_val || collection(i) ;
41 ELSE
42 l_ret_val := l_ret_val || ':' || prefix || i ;
43 END IF ;
44 END LOOP ;
45 ELSE
46 RETURN NULL ;
47 END IF ;
48 RETURN l_ret_val ;
49 END ;
50
51 PROCEDURE addBindVariables( csr NUMBER, prefix VARCHAR2, collection DBMS_SQL.NUMBER_TABLE)
52 IS
53 BEGIN
54 IF collection IS NULL OR collection.count < 1 THEN
55 return ;
56 END IF ;
57 FOR z IN collection.FIRST..collection.LAST LOOP
58 DBMS_SQL.bind_variable(csr, prefix || z ,collection(z)) ;
59 END LOOP ;
60 END ;
61
62
63 PROCEDURE Get_Groups_In_Hierarchy
64 (
65 p_include_array IN DBMS_SQL.NUMBER_TABLE ,
66 p_exclude_array IN DBMS_SQL.NUMBER_TABLE ,
67 p_date IN DATE := SYSDATE,
68 x_hierarchy_groups OUT NOCOPY DBMS_SQL.NUMBER_TABLE
69 )
70 IS
71 l_ret_array DBMS_SQL.NUMBER_TABLE ;
72 l_include_string VARCHAR2(2000) := 'X' ;
73 l_exclude_string VARCHAR2(2000) := ' ' ;
74 l_exclude_string_2 VARCHAR2(2000) := ' ' ;
75 l_sql VARCHAR2(4000) := ' ' ;
76 l_date DATE := NULL ;
77
78 select_cursor NUMBER := 0 ;
79 l_match_rows NUMBER := 0 ;
80
81 l_valid_1 VARCHAR2(500) := ' grl.delete_flag = ''N'' AND :DATE1 BETWEEN Trunc(grl.start_date_active) AND NVL(Trunc(grl.end_date_active), :DATE2 ) ' ;
82 l_valid_2 VARCHAR2(500) := ' grl.delete_flag = ''N'' AND :DATE3 BETWEEN Trunc(grl.start_date_active) AND NVL(Trunc(grl.end_date_active), :DATE4 ) ' ;
83 l_valid_3 VARCHAR2(500) := ' grl.delete_flag = ''N'' AND :DATE5 BETWEEN Trunc(grl.start_date_active) AND NVL(Trunc(grl.end_date_active), :DATE6 ) ' ;
84 BEGIN
85
86 l_date := NVL(p_date, SYSDATE) ;
87 x_hierarchy_groups := l_ret_array;
88
89 IF p_include_array IS NOT NULL AND p_include_array.count > 0 THEN
90 l_include_string := getString('INCBIND', p_include_array,FALSE) ;
91 l_exclude_string := ' ' ;
92 l_exclude_string_2 := ' ' ;
93
94 IF p_exclude_array IS NOT NULL AND p_exclude_array.count > 0 THEN
95 l_exclude_string := getString('EXBIND' , p_exclude_array,FALSE) ;
96 l_exclude_string_2 := getString('EX2BIND' ,p_exclude_array,FALSE) ;
97 l_exclude_string := ' AND GROUP_ID NOT IN ( ' || l_exclude_string || ' ) ' ;
98 l_exclude_string_2 := ' AND RELATED_GROUP_ID NOT IN ( ' || l_exclude_string_2 || ' ) ' ;
99 END IF ;
100
101 l_sql :=
102 'SELECT DISTINCT group_id ' ||
103 'FROM jtf_rs_grp_relations grl ' ||
104 'WHERE ' || l_valid_1 || ' ' || l_exclude_string ||
105 'START WITH related_group_id IN (' || l_include_string || ') AND ' || l_valid_2 ||
106 'CONNECT BY ' || l_valid_3 || ' ' || l_exclude_string_2 ||
107 ' AND PRIOR GROUP_ID = RELATED_GROUP_ID ' ;
108
109 --insert into fam_temp(attr1,time)values(l_sql,sysdate) ;
110 --commit ;
111
112 select_cursor := DBMS_SQL.OPEN_CURSOR;
113 DBMS_SQL.parse(select_cursor, l_sql, DBMS_SQL.NATIVE);
114 DBMS_SQL.define_array (select_cursor, 1, x_hierarchy_groups, 10, 1);
115
116 DBMS_SQL.bind_variable(select_cursor, ':DATE1', l_date);
117 DBMS_SQL.bind_variable(select_cursor, ':DATE2', l_date);
118 DBMS_SQL.bind_variable(select_cursor, ':DATE3', l_date);
119 DBMS_SQL.bind_variable(select_cursor, ':DATE4', l_date);
120 DBMS_SQL.bind_variable(select_cursor, ':DATE5', l_date);
121 DBMS_SQL.bind_variable(select_cursor, ':DATE6', l_date);
122
123 addBindVariables(select_cursor,'INCBIND',p_include_array) ;
124 addBindVariables(select_cursor,'EXBIND', p_exclude_array) ;
125 addBindVariables(select_cursor,'EX2BIND',p_exclude_array) ;
126 -- execute
127 l_match_rows := DBMS_SQL.EXECUTE(select_cursor);
128
129 LOOP
130 l_match_rows := DBMS_SQL.fetch_rows(select_cursor);
131 DBMS_SQL.column_value (select_cursor, 1, x_hierarchy_groups);
132 EXIT WHEN l_match_rows <> 10 ;
133 END LOOP ;
134
135 DBMS_SQL.close_cursor(select_cursor);
136 END IF ;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 IF (DBMS_SQL.is_open(select_cursor)) THEN
141 DBMS_SQL.close_cursor(select_cursor);
142 END IF;
143 RAISE ;
144 END ;
145
146 -- Start of comments
147 -- API name : Get_Valid_Plan_Statuses
148 -- Type : Private.
149 -- Function :
150 -- Pre-reqs : None.
151 -- Parameters :
152 -- IN : p_api_version IN NUMBER Required
153 -- p_init_msg_list IN VARCHAR2 Optional
154 -- Default = FND_API.G_FALSE
155 -- p_commit IN VARCHAR2 Optional
156 -- Default = FND_API.G_FALSE
157 -- p_validation_level IN NUMBER Optional
158 -- Default = FND_API.G_VALID_LEVEL_FULL
159 -- p_default_all
160 -- p_type
161 -- OUT : x_return_status OUT VARCHAR2(1)
162 -- x_msg_count OUT NUMBER
163 -- x_msg_data OUT VARCHAR2(2000)
164 -- x_values_tab OUT string_tabletype
165 -- x_meanings_tab OUT string_tabletype
166 -- Version : Current version 1.0
167 --
168 --
169 --
170 -- Notes : This procedure gets valid statuses of the salesrep/fm/pa/sm.
171 --
172 -- End of comments
173
174 PROCEDURE Get_Valid_Plan_Statuses
175 ( p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
177 p_commit IN VARCHAR2 := FND_API.G_FALSE,
178 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
179 p_default_all IN VARCHAR2 := FND_API.G_FALSE,
180 p_type IN VARCHAR2 := 'COMPPLANPROCESS',
181 x_values_tab OUT NOCOPY string_tabletype,
182 x_meanings_tab OUT NOCOPY string_tabletype,
183 x_return_status OUT NOCOPY VARCHAR2 ,
184 x_msg_count OUT NOCOPY NUMBER ,
185 x_msg_data OUT NOCOPY VARCHAR2
186 ) IS
187 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Seasonalities';
188 l_api_version CONSTANT NUMBER := 1.0;
189 l_error_code NUMBER;
190 l_values_tab string_tabletype;
191 l_meanings_tab string_tabletype;
192 l_resp_group VARCHAR2(80);
193 finalquery VARCHAR2(4000);
194
195 TYPE RC_TYPE IS REF CURSOR;
196 RC RC_TYPE;
197
198 compPlanQuery VARCHAR2(4000) := 'SELECT LOOKUP_CODE,MEANING FROM CN_LOOKUPS WHERE
199 LOOKUP_TYPE = ''PLAN_TYPE_STATUS'' ';
200 compPlanExtra VARCHAR2(4000);
201 compPlanOrder VARCHAR2(4000) := ' ORDER BY MEANING';
202 l_counter NUMBER := 0;
203 l_value CN_LOOKUPS.LOOKUP_CODE%TYPE;
204 l_meaning CN_LOOKUPS.MEANING%TYPE;
205 BEGIN
206
207 SAVEPOINT Get_Valid_Plan_Statuses;
208 -- Standard call to check for call compatibility.
209 IF NOT FND_API.compatible_api_call
210 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
211 THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215 -- Initialize message list if p_init_msg_list is set to TRUE.
216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
217 FND_MSG_PUB.initialize;
218 END IF;
219 -- Initialize API return status to success
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221
222 -- API body
223 l_resp_group := FND_PROFILE.VALUE('CN_SFP_RESP_GROUP');
224
225 IF (l_resp_group is null) THEN
226 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
227 THEN
228 FND_MESSAGE.SET_NAME('CN', 'CN_QM_NO_RESP_GROUP');
229 FND_MSG_PUB.Add;
230 END IF;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 -- Comp Plan Process (Many types can be added)
235
236 IF (p_type = 'COMPPLANPROCESS') THEN
237 -- Planning Analyst
238 IF (l_resp_group = 'CN_SF_SUPER_USER') THEN
239 compPlanExtra := ' AND LOOKUP_CODE IN (''SUBMITTED'',''APPROVED'',''ISSUED'',''ACCEPTED'') ';
240 END IF;
241
242 -- Contract Approver
243 IF (l_resp_group = 'CN_SF_CONTRACT_APPROVER') THEN
244 compPlanExtra := ' AND LOOKUP_CODE IN (''SUBMITTED'',''APPROVED'',''ISSUED'',''ACCEPTED'')';
245 END IF;
246
247 -- Finance Manager
248 IF (l_resp_group = 'CN_SF_FINANCE_MGR') THEN
249 compPlanExtra := ' AND LOOKUP_CODE IN (''SUBMITTED'',''APPROVED'',''ISSUED'',''ACCEPTED'')';
250 END IF;
251
252
253 -- Sales Manager
254 IF (l_resp_group = 'CN_SF_SALES_MGR') THEN
255 compPlanExtra := ' AND LOOKUP_CODE IN (''SUBMITTED'',''APPROVED'',''ISSUED'',''ACCEPTED'') ';
256 END IF;
257
258 compPlanQuery := compPlanQuery || compPlanExtra || compPlanOrder;
259 finalquery := compPlanQuery;
260 END IF;
261
262
263 -- Rest of the logic should remain the same
264
265 IF (p_default_all = FND_API.G_TRUE) THEN
266 x_values_tab(l_counter) := '%';
267 x_meanings_tab(l_counter) := FND_MESSAGE.GET_STRING('CN','CN_ALL');
268 END IF;
269
270 OPEN RC FOR finalquery;
271 LOOP
272
273 FETCH RC INTO l_value,l_meaning;
274 EXIT WHEN RC%NOTFOUND;
275 l_counter := l_counter + 1;
276 x_values_tab(l_counter) := l_value;
277 x_meanings_tab(l_counter) := l_meaning;
278
279 END LOOP;
280
281 -- End of API body.
282 << end_Get_Valid_Plan_Statuses >>
283 NULL;
284
285 -- Standard check of p_commit.
286 IF FND_API.To_Boolean( p_commit ) THEN
287 COMMIT WORK;
288 END IF;
289 -- Standard call to get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get
291 (
292 p_count => x_msg_count ,
293 p_data => x_msg_data ,
294 p_encoded => FND_API.G_FALSE
295 );
296
297 EXCEPTION
298 WHEN FND_API.G_EXC_ERROR THEN
299 ROLLBACK TO Get_Valid_Plan_Statuses ;
300 x_return_status := FND_API.G_RET_STS_ERROR ;
301 FND_MSG_PUB.Count_And_Get
302 (
303 p_count => x_msg_count ,
304 p_data => x_msg_data ,
305 p_encoded => FND_API.G_FALSE
306 );
307
308 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
309 ROLLBACK TO Get_Valid_Plan_Statuses ;
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
311 FND_MSG_PUB.Count_And_Get
312 (
313 p_count => x_msg_count ,
314 p_data => x_msg_data ,
315 p_encoded => FND_API.G_FALSE
316 );
317 WHEN OTHERS THEN
318 ROLLBACK TO Get_Valid_Plan_Statuses ;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 FND_MSG_PUB.Count_And_Get
321 (
322 p_count => x_msg_count ,
323 p_data => x_msg_data ,
324 p_encoded => FND_API.G_FALSE
325 );
326 END Get_Valid_Plan_Statuses;
327
328
329
330
331 -- Start of comments
332 -- API name : Get_All_Groups_Access
333 -- Type : Private.
334 -- Function :
335 -- Prereqs : None.
336 -- Parameters :
337 -- IN : p_api_version IN NUMBER Required
338 -- p_init_msg_list IN VARCHAR2 Optional
339 -- Default = FND_API.G_FALSE
340 -- p_commit IN VARCHAR2 Optional
341 -- Default = FND_API.G_FALSE
342 -- p_validation_level IN NUMBER Optional
343 -- Default = FND_API.G_VALID_LEVEL_FULL
344 -- x_update_groups OUT DBMS_SQL.NUMBER_TABLE,
345 -- x_view_groups OUT DBMS_SQL.NUMBER_TABLE,
346 -- OUT : x_return_status OUT VARCHAR2(1)
347 -- x_msg_count OUT NUMBER
348 -- x_msg_data OUT VARCHAR2(2000)
349 -- Version : Current version 1.0
350 --
351 --
352 --
353 -- Notes : This procedure gets the user_access for all groups
354 -- in cn_user_access
355 --
356 -- End of comments
357
358 PROCEDURE Get_All_Groups_Access
359 ( p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361 p_commit IN VARCHAR2 := FND_API.G_FALSE,
362 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
363 p_org_code IN VARCHAR2 := NULL,
364 p_date IN VARCHAR2,
365 x_update_groups OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
366 x_view_groups OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2
370 )
371 IS
372 l_api_name CONSTANT VARCHAR2(30) := 'Get_All_Groups_Access';
373 l_api_version CONSTANT NUMBER := 1.0;
374
375 VIEW_ARRAY DBMS_SQL.NUMBER_TABLE;
376 UPDATE_ARRAY DBMS_SQL.NUMBER_TABLE;
377 L_REG_GROUPS DBMS_SQL.NUMBER_TABLE ;
378 hier_update_groups DBMS_SQL.NUMBER_TABLE ;
379 hier_view_groups DBMS_SQL.NUMBER_TABLE ;
380
381 select_cursor NUMBER ;
382 l_match_rows NUMBER := 0 ;
383 l_sql VARCHAR2(4000) := '' ;
384
385 l_date DATE := SYSDATE ;
386 l_org_code CN_USER_ACCESSES.ORG_CODE%TYPE := NULL ;
387 l_user_id NUMBER ;
388 l_include_string VARCHAR2(2000) := ' ' ;
389 l_exclude_string VARCHAR2(2000) := ' ' ;
390 l_exclude_string_2 VARCHAR2(2000) := ' ' ;
391 l_include_string_2 VARCHAR2(2000) := ' ' ;
392 l_all_string VARCHAR2(2000) := ' ' ;
393 l_exc_count INTEGER := 0 ;
394 l_inc_count INTEGER := 0 ;
395 l_count INTEGER := 0 ;
396 l_num NUMBER := NULL ;
397 l_resp_group VARCHAR2(240) := NULL ;
398
399
400 CURSOR Get_Access(c_user_id NUMBER ,c_date DATE ,p_org_code VARCHAR) IS
401 SELECT u.comp_group_id group_id , u.access_code access_code
402 FROM cn_qm_comp_groups g, cn_user_accesses u
403 WHERE u.user_id = c_user_id
404 AND g.comp_group_id = u.comp_group_id
405 AND c_date BETWEEN Trunc(g.start_date_active) AND
406 Nvl(Trunc(g.end_date_active), c_date )
407 AND ((u.org_code LIKE p_org_code) OR (p_org_code IS NULL)) ;
408
409 BEGIN
410 SAVEPOINT Get_All_Groups_Access;
411 -- Standard call to check for call compatibility.
412 IF NOT FND_API.compatible_api_call
413 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417
418 -- Initialize message list if p_init_msg_list is set to TRUE.
419 IF FND_API.to_Boolean( p_init_msg_list ) THEN
420 FND_MSG_PUB.initialize;
421 END IF;
422 -- Initialize API return status to success
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 l_resp_group := fnd_profile.value('CN_SFP_RESP_GROUP');
426 IF l_resp_group IS null THEN
427 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
428 THEN
429 FND_MESSAGE.SET_NAME('CN', 'CN_QM_NO_RESP_GROUP');
430 FND_MSG_PUB.Add;
431 END IF;
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434
435 IF l_resp_group <> 'CN_SF_FINANCE_MGR' THEN
436 RETURN ;
437 END IF ;
438
439 l_date := p_date ;
440 l_org_code := p_org_code ;
441 l_user_id := fnd_global.user_id ;
442
443 --dbms_output.put_line( 'Entering') ;
444
445 FOR retCsr IN Get_Access(l_user_id,l_date,l_org_code) LOOP
446 IF retCsr.access_code = 'UPDATE' THEN
447 l_inc_count := l_inc_count + 1 ;
448 UPDATE_ARRAY(l_inc_count) := retCsr.group_id ;
449 X_UPDATE_GROUPS(l_inc_count) := retCsr.group_id ;
450 ELSIF retCsr.access_code = 'VIEW' THEN
451 l_exc_count := l_exc_count + 1 ;
452 VIEW_ARRAY(l_exc_count) := retCsr.group_id ;
453 X_VIEW_GROUPS(l_exc_count) := retCsr.group_id ;
454 END IF ;
455 l_count := l_count + 1 ;
456 L_REG_GROUPS(l_count) := retCsr.group_id ;
457 END LOOP ;
458
459 IF L_REG_GROUPS IS NULL OR L_REG_GROUPS.count < 1 THEN
460 RETURN ;
461 END IF ;
462
463
464 -- GET VIEW/UPDATE GROUPS
465 Get_Groups_In_Hierarchy(VIEW_ARRAY,UPDATE_ARRAY,l_date,hier_view_groups) ;
466 Get_Groups_In_Hierarchy(UPDATE_ARRAY,VIEW_ARRAY,l_date,hier_update_groups) ;
467
468
469 -- ADD GROUPS UNDER THE ROOT
470 IF hier_view_groups.count > 0 THEN
471 FOR i IN hier_view_groups.first..hier_view_groups.last LOOP
472 IF hier_view_groups.exists(i) THEN
473 X_VIEW_GROUPS(l_inc_count+i) := hier_view_groups(i) ;
474 END IF ;
475 END LOOP ;
476 END IF ;
477
478 IF hier_update_groups.count > 0 THEN
479 FOR i IN hier_update_groups.first..hier_update_groups.last LOOP
480 X_UPDATE_GROUPS(l_inc_count+i) := hier_update_groups(i) ;
481 END LOOP ;
482 END IF ;
483
484
485 EXCEPTION
486 WHEN FND_API.G_EXC_ERROR THEN
487 ROLLBACK TO Get_All_Groups_Access ;
488 x_return_status := FND_API.G_RET_STS_ERROR ;
489 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
490 p_data => x_msg_data ,
491 p_encoded => FND_API.G_FALSE);
492
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494 ROLLBACK TO Get_All_Groups_Access ;
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
497 p_data => x_msg_data ,
498 p_encoded => FND_API.G_FALSE);
499 WHEN OTHERS THEN
500 ROLLBACK TO Get_All_Groups_Access ;
501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
502 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
503 THEN
504 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name) ;
505 END IF ;
506 cn_message_pkg.debug(G_PKG_NAME || ' ' || l_api_name ||' '||TO_CHAR(SQLCODE)||': '||SQLERRM) ;
507 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
508 p_data => x_msg_data ,
509 p_encoded => FND_API.G_FALSE);
510 END Get_All_Groups_Access;
511
512
513 -- Start of comments
514 -- API name : Get_Group_Access
515 -- Type : Private.
516 -- Function :
517 -- Prereqs : None.
518 -- Parameters :
519 -- IN : p_api_version IN NUMBER Required
520 -- p_init_msg_list IN VARCHAR2 Optional
521 -- Default = FND_API.G_FALSE
522 -- p_commit IN VARCHAR2 Optional
523 -- Default = FND_API.G_FALSE
524 -- p_validation_level IN NUMBER Optional
525 -- Default = FND_API.G_VALID_LEVEL_FULL
526 -- p_default_all
527 -- p_group_id
528 -- p_update_groups IN DBMS_SQL.NUMBER_TABLE,
529 -- p_view_groups IN DBMS_SQL.NUMBER_TABLE,
530 -- OUT : x_return_status OUT VARCHAR2(1)
531 -- x_msg_count OUT NUMBER
532 -- x_msg_data OUT VARCHAR2(2000)
533 -- x_privilege OUT VARCHAR2,
534 -- Version : Current version 1.0
535 --
536 --
537 -- Notes : This procedure gets valid statuses of the salesrep/fm/pa/sm.
538 --
539 -- End of comments
540
541 PROCEDURE Get_Group_Access
542 ( p_api_version IN NUMBER,
543 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
544 p_commit IN VARCHAR2 := FND_API.G_FALSE,
545 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
546 p_group_id IN NUMBER,
547 p_update_groups IN DBMS_SQL.NUMBER_TABLE,
548 p_view_groups IN DBMS_SQL.NUMBER_TABLE,
549 x_privilege OUT NOCOPY VARCHAR2,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2
553 )
554 IS
555 l_api_name CONSTANT VARCHAR2(30) := 'Get_Group_Access';
556 l_api_version CONSTANT NUMBER := 1.0;
557
558 BEGIN
559 SAVEPOINT Get_Group_Access;
560 -- Standard call to check for call compatibility.
561 IF NOT FND_API.compatible_api_call
562 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
563 THEN
564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
565 END IF;
566
567 -- Initialize message list if p_init_msg_list is set to TRUE.
568 IF FND_API.to_Boolean( p_init_msg_list ) THEN
569 FND_MSG_PUB.initialize;
570 END IF;
571 -- Initialize API return status to success
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
573
574 IF contains(p_group_id,p_update_groups) THEN
575 --dbms_output.put_line('IRead Only');
576 x_privilege := 'WRITE' ;
577 RETURN ;
578 END IF ;
579
580 IF contains(p_group_id,p_view_groups) THEN
581 --dbms_output.put_line('IRead Only');
582 x_privilege := 'READ' ;
583 RETURN ;
584 END IF ;
585
586 x_privilege := 'NO_READ' ;
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 ROLLBACK TO Get_Group_Access ;
591 x_return_status := FND_API.G_RET_STS_ERROR ;
592 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
593 p_data => x_msg_data ,
594 p_encoded => FND_API.G_FALSE);
595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
596 ROLLBACK TO Get_Group_Access ;
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
598 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
599 p_data => x_msg_data ,
600 p_encoded => FND_API.G_FALSE);
601 WHEN OTHERS THEN
602 ROLLBACK TO Get_Group_Access ;
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
604 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
605 THEN
606 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name) ;
607 END IF ;
608 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
609 p_data => x_msg_data ,
610 p_encoded => FND_API.G_FALSE);
611 END Get_Group_Access;
612
613 END CN_SFP_SRP_UTIL_PVT;