[Home] [Help]
PACKAGE BODY: APPS.INVPROFL
Source
1 package body INVPROFL as
2 /*$Header: INVPROFB.pls 120.1 2005/06/21 05:00:16 appldev ship $*/
3
4 procedure inv_pr_get_profile
5 (
6 appl_short_name IN VARCHAR2,
7 profile_name IN VARCHAR2,
8 user_id IN NUMBER,
9 resp_appl_id IN NUMBER,
10 resp_id IN NUMBER,
11 profile_value OUT NOCOPY VARCHAR2,
12 return_code OUT NOCOPY NUMBER,
13 return_message OUT NOCOPY VARCHAR2
14 )
15 IS
16 l_pr_option_id NUMBER;
17 l_pr_level_id NUMBER;
18 l_pr_level_value NUMBER;
19 l_pr_value VARCHAR2(30);
20 no_profile_found EXCEPTION;
21 CURSOR get_profile_cursor(c_appl_short_name IN VARCHAR2,
22 c_profile_name IN VARCHAR2,
23 c_user_id IN NUMBER,
24 c_resp_appl_id IN NUMBER,
25 c_resp_id IN NUMBER) IS
26 SELECT opt.profile_option_id,
27 level_id,
28 level_value,
29 substrb(profile_option_value,1,30)
30 FROM fnd_profile_options opt,
31 fnd_application appl,
32 fnd_profile_option_values val
33 WHERE opt.application_id = val.application_id
34 AND opt.profile_option_id = val.profile_option_id
35 AND opt.application_id = appl.application_id
36 AND appl.application_short_name = c_appl_short_name
37 AND opt.profile_option_name = c_profile_name
38 AND (
39 (val.level_id = 10001) /* Site */
40 OR
41 (val.level_id = 10002 /* Application */
42 and val.level_value = c_resp_appl_id )
43 OR
44 (val.level_id = 10003 /* Responsibility */
45 and val.level_value_application_id = c_resp_appl_id
46 and val.level_value = c_resp_id)
47 OR
48 (val.level_id = 10004 /* User */
49 and val.level_value = c_user_id)
50 )
51 ORDER BY opt.profile_option_id, level_id desc;
52
53 BEGIN
54 /* Set appropriate defaults */
55
56 profile_value := NULL;
57 OPEN get_profile_cursor(appl_short_name, profile_name,
58 user_id, resp_appl_id,
59 resp_id);
60 FETCH get_profile_cursor INTO l_pr_option_id,
61 l_pr_level_id,
62 l_pr_level_value,
63 l_pr_value;
64 IF get_profile_cursor%NOTFOUND THEN
65 RAISE no_profile_found;
66 END IF;
67
68 CLOSE get_profile_cursor;
69 --
70 profile_value := l_pr_value;
71 return_code := 0;
72 return_message := 'Profile value found';
73 --
74 EXCEPTION
75 WHEN no_profile_found THEN
76 return_code := -9999;
77 return_message := 'INV_NO_PROFILE_VALUE';
78 WHEN OTHERS THEN
79 return_code := SQLCODE;
80 return_message := 'INVPRFIL.inv_pr_get_profile ' || SUBSTRB(SQLERRM,1,100);
81
82 END inv_pr_get_profile;
83
84 end INVPROFL;