[Home] [Help]
PACKAGE BODY: APPS.BIS_RESPONSIBILITY_PVT
Source
1 PACKAGE BODY BIS_RESPONSIBILITY_PVT AS
2 /* $Header: BISVRSPB.pls 120.1 2006/04/10 07:57:30 psomesul noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVRSPB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Private API for managing Responsibilities for PMF
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 15-MAR-99 Ansingha Creation
19 REM | 19-MAY-2005 visuri GSCC Issues bug 4363854 |
20 REM | 10-APR-05 psomesul Bug#5140269 - PERFORMANCE ISSUE WITH TARGET OWNER |
21 REM | LOV IN PMF PAGES - replaced WF_ROLES with WF_ROLE_LOV_VL |
22 REM +=======================================================================+
23 */
24 --
25 -- PROCEDUREs
26 --
27 G_PKG_NAME CONSTANT varchar2(30) := 'BIS_RESPONSIBILITY_PVT';
28
29
30 Procedure Retrieve_User_Responsibilities
31 ( p_api_version IN NUMBER
32 , p_user_id IN NUMBER Default BIS_COMMON_UTILS.G_DEF_NUM
33 , x_Responsibility_Tbl OUT NOCOPY BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_Type
34 , x_return_status OUT NOCOPY VARCHAR2
35 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_tbl_Type
36 )
37 IS
38 cursor rsp_cur is
39 select a.responsibility_id,
40 a.responsibility_key,
41 a.responsibility_name
42 from fnd_responsibility_vl a,
43 fnd_user_resp_groups b
44 where b.user_id = p_user_id
45 and b.responsibility_id = a.responsibility_id
46 and b.start_date <= sysdate
47 and (b.end_date is null or b.end_date >= sysdate)
48 and a.start_date <= sysdate
49 and (a.end_date is null or a.end_date >= sysdate)
50 and a.version = 'W'
51 order by responsibility_name;
52
53 l_rec BIS_RESPONSIBILITY_PVT.Responsibility_Rec_Type;
54
55 BEGIN
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57
58 FOR cr in rsp_cur LOOP
59 l_rec.Responsibility_ID := cr.RESPONSIBILITY_ID;
60 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
61 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME;
62 x_Responsibility_Tbl(x_Responsibility_Tbl.COUNT+1) := l_rec;
63 END LOOP;
64
65
66 EXCEPTION
67 when FND_API.G_EXC_ERROR then
68 x_return_status := FND_API.G_RET_STS_ERROR ;
69
70 RAISE FND_API.G_EXC_ERROR;
71 when FND_API.G_EXC_UNEXPECTED_ERROR then
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 when others then
75 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
76 BIS_UTILITIES_PVT.Add_Error_Message
77 ( p_error_msg_id => SQLCODE
78 , p_error_description => SQLERRM
79 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User_Responsibilities'
80 );
81
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83
84 END Retrieve_User_Responsibilities;
85 --
86 Procedure Retrieve_User_Responsibilities
87 ( p_api_version IN NUMBER
88 , p_user_id IN NUMBER Default BIS_COMMON_UTILS.G_DEF_NUM
89 , p_Responsibility_version IN VARCHAR
90 , x_Responsibility_Tbl OUT NOCOPY BIS_Responsibility_PVT.Responsibility_Tbl_Type
91 , x_return_status OUT NOCOPY VARCHAR2
92 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_tbl_Type
93 )
94 IS
95 cursor rsp_cur(p_version VARCHAR2) is
96 select a.responsibility_id,
97 a.responsibility_key,
98 a.responsibility_name
99 from fnd_responsibility_vl a,
100 fnd_user_resp_groups b
101 where b.user_id = p_user_id
102 and b.responsibility_id = a.responsibility_id
103 and b.start_date <= sysdate
104 and (b.end_date is null or b.end_date >= sysdate)
105 and a.start_date <= sysdate
106 and (a.end_date is null or a.end_date >= sysdate)
107 and a.version like p_version
108 order by responsibility_name;
109
110 l_rec BIS_RESPONSIBILITY_PVT.Responsibility_Rec_Type;
111 l_version VARCHAR2(1);
112
113 BEGIN
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 l_version := p_Responsibility_version;
117
118 IF BIS_UTILITIES_PUB.Value_Missing(l_version) = FND_API.G_TRUE
119 OR BIS_UTILITIES_PUB.Value_Null(l_version) = FND_API.G_TRUE
120 THEN l_version := '%';
121 END IF;
122
123 FOR cr in rsp_cur(l_version) LOOP
124 l_rec.Responsibility_ID := cr.RESPONSIBILITY_ID;
125 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
126 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME;
127 x_Responsibility_Tbl(x_Responsibility_Tbl.COUNT+1) := l_rec;
128 END LOOP;
129
130 EXCEPTION
131 when FND_API.G_EXC_ERROR then
132 x_return_status := FND_API.G_RET_STS_ERROR ;
133 RAISE FND_API.G_EXC_ERROR;
134 when FND_API.G_EXC_UNEXPECTED_ERROR then
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
137 when others then
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
139 BIS_UTILITIES_PVT.Add_Error_Message
140 ( p_error_msg_id => SQLCODE
141 , p_error_description => SQLERRM
142 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User_Responsibilities'
143 );
144
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146
147 END Retrieve_User_Responsibilities;
148 --
149 Procedure Retrieve_All_Responsibilities
150 ( p_api_version IN NUMBER
151 , x_Responsibility_Tbl OUT NOCOPY BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_Type
152 , x_return_status OUT NOCOPY VARCHAR2
153 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_tbl_Type
154 )
155 IS
156 cursor resp_cur is
157 select RESPONSIBILITY_ID
158 , RESPONSIBILITY_KEY
159 , RESPONSIBILITY_NAME
160 from fnd_responsibility_vl
161 where VERSION='W'
162 and start_date <= sysdate
163 and nvl(end_date, sysdate) >= sysdate
164 order by RESPONSIBILITY_NAME;
165 l_rec BIS_RESPONSIBILITY_PVT.Responsibility_Rec_Type;
166 BEGIN
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169 for cr in resp_cur LOOP
170 l_rec.Responsibility_ID := cr.RESPONSIBILITY_ID;
171 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
172 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME;
173 x_Responsibility_Tbl(x_Responsibility_Tbl.COUNT+1) := l_rec;
174 END LOOP;
175
176 EXCEPTION
177 when FND_API.G_EXC_ERROR then
178 x_return_status := FND_API.G_RET_STS_ERROR ;
179
180 RAISE FND_API.G_EXC_ERROR;
181 when FND_API.G_EXC_UNEXPECTED_ERROR then
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 when others then
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
186 BIS_UTILITIES_PVT.Add_Error_Message
187 ( p_error_msg_id => SQLCODE
188 , p_error_description => SQLERRM
189 , p_error_proc_name => G_PKG_NAME||'.Retrieve_All_Responsibilities'
190 );
191
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193
194 END Retrieve_All_Responsibilities;
195 --
196 Procedure Retrieve_Responsibility
197 ( p_api_version IN NUMBER
198 , p_Responsibility_Rec IN BIS_Responsibility_PVT.Responsibility_rec_Type
199 , x_Responsibility_Rec OUT NOCOPY BIS_Responsibility_PVT.Responsibility_rec_Type
200 , x_return_status OUT NOCOPY VARCHAR2
201 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_tbl_Type
202 )
203 IS
204 BEGIN
205
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 BEGIN
209 select RESPONSIBILITY_ID
210 , RESPONSIBILITY_KEY
211 , RESPONSIBILITY_NAME
212 into x_Responsibility_Rec.RESPONSIBILITY_ID
213 , x_Responsibility_Rec.RESPONSIBILITY_SHORT_NAME
214 , x_Responsibility_Rec.RESPONSIBILITY_NAME
215 from fnd_responsibility_vl
216 where VERSION='W'
217 and start_date <= sysdate
218 and nvl(end_date, sysdate) >= sysdate
219 and RESPONSIBILITY_ID=p_Responsibility_Rec.RESPONSIBILITY_ID;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 x_return_status := FND_API.G_RET_STS_ERROR ;
223 --Added last two params
224 BIS_UTILITIES_PVT.Add_Error_Message
225 ( p_error_msg_id => SQLCODE
226 , p_error_description => SQLERRM
227 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Responsibility'
228 , p_error_table => x_error_tbl
229 , x_error_table => x_error_tbl
230 );
231 END;
232
233 --commented RAISE
234 EXCEPTION
235
236 when FND_API.G_EXC_ERROR then
237 x_return_status := FND_API.G_RET_STS_ERROR ;
238 --RAISE FND_API.G_EXC_ERROR;
239 when FND_API.G_EXC_UNEXPECTED_ERROR then
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
241 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 when others then
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
244 --Added last two params
245 BIS_UTILITIES_PVT.Add_Error_Message
246 ( p_error_msg_id => SQLCODE
247 , p_error_description => SQLERRM
248 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Responsibility'
249 , p_error_table => x_error_tbl
250 , x_error_table => x_error_tbl
251 );
252
253 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254
255 END Retrieve_Responsibility;
256
257 PROCEDURE Validate_Def_Notify_Resp_Id
258 ( p_api_version IN NUMBER
259 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
260 , p_Def_Notify_Resp_Id IN NUMBER
261 , x_return_status OUT NOCOPY VARCHAR2
262 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
263 )
264 IS
265 BEGIN
266
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268 NULL;
269 EXCEPTION
270 when FND_API.G_EXC_ERROR then
271 x_return_status := FND_API.G_RET_STS_ERROR ;
272 RAISE FND_API.G_EXC_ERROR;
273 when FND_API.G_EXC_UNEXPECTED_ERROR then
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 when others then
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278 BIS_UTILITIES_PVT.Add_Error_Message
279 ( p_error_msg_id => SQLCODE
280 , p_error_description => SQLERRM
281 , p_error_proc_name => G_PKG_NAME||'.DFR_Value_ID_Conversion'
282 );
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284
285 END Validate_Def_Notify_Resp_Id;
286 --
287 PROCEDURE Retrieve_Notify_Resp_Name
288 ( p_api_version IN NUMBER
289 , p_Notify_resp_short_name IN VARCHAR2
290 , x_Notify_resp_name OUT NOCOPY VARCHAR2
291 , x_return_status OUT NOCOPY VARCHAR2
292 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
293 )
294 IS
295
296 CURSOR cr_wf_role IS
297 SELECT DISPLAY_NAME
298 FROM wf_role_lov_vl
299 WHERE NAME = p_Notify_resp_short_name;
300
301 BEGIN
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 OPEN cr_wf_role;
305 FETCH cr_wf_role INTO x_notify_resp_name;
306 CLOSE cr_wf_role;
307
308 EXCEPTION
309 when FND_API.G_EXC_ERROR then
310 x_return_status := FND_API.G_RET_STS_ERROR ;
311 RAISE FND_API.G_EXC_ERROR;
312 when FND_API.G_EXC_UNEXPECTED_ERROR then
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 when others then
316
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318 BIS_UTILITIES_PVT.Add_Error_Message
319 ( p_error_msg_id => SQLCODE
320 , p_error_description => SQLERRM
321 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Notify_Resp_Name'
322 );
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324
325 END Retrieve_Notify_Resp_Name;
326 --
327 PROCEDURE Validate_Notify_Resp_ID
328
329 ( p_api_version IN NUMBER
330 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
331 , p_Notify_Resp_ID IN NUMBER
332 , x_return_status OUT NOCOPY VARCHAR2
333 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
334 )
335 IS
336 BEGIN
337 --added the status
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339 NULL;
340 EXCEPTION
341 when FND_API.G_EXC_ERROR then
342 x_return_status := FND_API.G_RET_STS_ERROR ;
343 RAISE FND_API.G_EXC_ERROR;
344
345 when FND_API.G_EXC_UNEXPECTED_ERROR then
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
348 when others then
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
350 BIS_UTILITIES_PVT.Add_Error_Message
351 ( p_error_msg_id => SQLCODE
352 , p_error_description => SQLERRM
353 , p_error_proc_name => G_PKG_NAME||'.Validate_Notify_Resp_ID'
354 );
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356
357
358 END Validate_Notify_Resp_ID;
359 --
360 PROCEDURE Value_ID_Conversion
361 ( p_api_version IN NUMBER
362 , p_Responsibility_Short_Name IN VARCHAR2
363 , p_Responsibility_Name IN VARCHAR2
364 , x_Responsibility_ID OUT NOCOPY NUMBER
365 , x_return_status OUT NOCOPY VARCHAR2
366 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
367 )
368 IS
369 BEGIN
370
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372 NULL;
373 EXCEPTION
374 when FND_API.G_EXC_ERROR then
375 x_return_status := FND_API.G_RET_STS_ERROR ;
376 RAISE FND_API.G_EXC_ERROR;
377 when FND_API.G_EXC_UNEXPECTED_ERROR then
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 when others then
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
382 BIS_UTILITIES_PVT.Add_Error_Message
383 ( p_error_msg_id => SQLCODE
384 , p_error_description => SQLERRM
385 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
386 );
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388
389 END Value_ID_Conversion;
390 --
391 PROCEDURE DFR_Value_ID_Conversion
392 ( p_api_version IN NUMBER
393 , p_DF_Responsibility_Short_Name IN VARCHAR2
394 , p_DF_Responsibility_Name IN VARCHAR2
395 , x_DF_Responsibility_ID OUT NOCOPY NUMBER
396
397 , x_return_status OUT NOCOPY VARCHAR2
398 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
399 )
400 IS
401 BEGIN
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403 NULL;
404 EXCEPTION
405 when FND_API.G_EXC_ERROR then
406 x_return_status := FND_API.G_RET_STS_ERROR ;
407 RAISE FND_API.G_EXC_ERROR;
408 when FND_API.G_EXC_UNEXPECTED_ERROR then
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
411
412 when others then
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
414 BIS_UTILITIES_PVT.Add_Error_Message
415 ( p_error_msg_id => SQLCODE
416 , p_error_description => SQLERRM
417 , p_error_proc_name => G_PKG_NAME||'.DFR_Value_ID_Conversion'
418 );
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420
421 END DFR_Value_ID_Conversion;
422 --
423 -- removes the responsibilities from p_all_security
424 -- which are in p_security
425 PROCEDURE RemoveDuplicates
426 ( p_security in BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_type
427 , p_all_security in BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_type
428 , x_all_security out NOCOPY BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_type
429 )
430 is
431 l_unique BOOLEAN;
432 l_rec BIS_RESPONSIBILITY_PVT.Responsibility_Rec_Type;
433 begin
434 --
435 for i in 1 .. p_all_security.count loop
436 l_rec := p_all_security(i);
437 l_unique := true;
438 --
439 for j in 1 .. p_security.count loop
440 if (p_security(j).Responsibility_ID = l_rec.Responsibility_ID) then
441 l_unique := false;
442 exit;
443 end if;
444 end loop;
445 --
446 if (l_unique) then
447 x_all_security(x_all_security.count + 1) := l_rec;
448 end if;
449 --
450 end loop;
451 --
452 end RemoveDuplicates;
453 --
454 Procedure Get_Notify_Resp_AK_Info
455 ( p_notify_responsibility_rec
456 IN BIS_Responsibility_PVT.Notify_Responsibility_Rec_type
457 , x_attribute_app_id OUT NOCOPY NUMBER
458 , x_attribute_code OUT NOCOPY VARCHAR2
459 , x_attribute_name OUT NOCOPY VARCHAR2
460 , x_region_app_id OUT NOCOPY NUMBER
461 , x_region_code OUT NOCOPY VARCHAR2
462 )
463 IS
464
465 l_notify_responsibility_rec
466 BIS_Responsibility_PVT.Notify_Responsibility_Rec_type;
467 l_attribute_app_id NUMBER := BIS_UTILITIES_PVT.G_BIS_APPLICATION_ID;
468 l_attribute_code VARCHAR2(32000);
469 l_attribute_name VARCHAR2(32000);
470 l_region_app_id NUMBER := BIS_UTILITIES_PVT.G_BIS_APPLICATION_ID;
471 l_region_code VARCHAR2(32000);
472
473 CURSOR cr_ak_data IS
474 SELECT
475 attribute_code
476 FROM ak_region_items_vl
477 WHERE region_code = l_region_code
478 AND region_application_id = l_region_app_id
479 AND attribute_application_id = l_attribute_app_id;
480
481 BEGIN
482
483 -- htp.p('Get_Notify_resp_AK_Info'||g_br);
484
485 l_region_code := G_WF_ROLE_AK_REGION;
486
487 FOR cr IN cr_ak_data LOOP
488 -- htp.p('cr.attribute_code: '||cr.attribute_code||g_br);
489 IF UPPER(cr.attribute_code) = G_WF_ROLE_SHORT_NAME_AK
490 THEN
491 l_attribute_code := cr.attribute_code;
492 ELSE
493 l_attribute_name := cr.attribute_code;
494 END IF;
495 END LOOP;
496
497 x_attribute_app_id := l_attribute_app_id;
498 x_attribute_code := l_attribute_code;
499 x_attribute_name := l_attribute_name;
500 x_region_app_id := l_region_app_id;
501 x_region_code := l_region_code;
502
503 /*
504 htp.p('notify role: '||
505 l_notify_responsibility_rec.notify_resp_short_name||
506 ' - region code: '||l_region_code||
507 ' - attribute code: '||l_attribute_code||
508 ' - attribute_name: '||l_attribute_name||g_br);
509 */
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 htp.p('Error in Get_Notify_Resp_AK_Info: '||SQLERRM);
514
515 END Get_Notify_Resp_AK_Info;
516
517 --
518 END BIS_RESPONSIBILITY_PVT;