1 PACKAGE BODY MSC_GET_PROFILE AS -- body
2 /* $Header: MSCPROFB.pls 115.0 2004/05/21 13:13:47 rawasthi noship $ */
3
4 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
5 IS
6 BEGIN
7 IF fnd_global.conc_request_id > 0 THEN
8 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
9 ELSE
10 null;
11 --DBMS_OUTPUT.PUT_LINE( pBUFF);
12 END IF;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN;
16 END LOG_MESSAGE;
17
18 PROCEDURE GET_SPECIFIC_LEVEL_PROFILES(
19 name_z in varchar2,
20 level_id_z in number,
21 level_value_z in number,
22 level_value_application_z in number,
23 val_z out NOCOPY varchar2,
24 cached_z out NOCOPY boolean) is
25 pid number;
26 aid number;
27 tableIndex binary_integer;
28 contextLevelValue number;
29
30 --
31 -- this cursor fetches profile information that will
32 -- allow subsequent fetches to be more efficient
33 --
34 cursor profile_info is
35 select profile_option_id,
36 application_id
37 from fnd_profile_options
38 where profile_option_name = upper(name_z)
39 and start_date_active <= sysdate
40 and nvl(end_date_active, sysdate) >= sysdate;
41
42 --
43 -- this cursor fetches profile option values for site, application,
44 -- and user levels (10001/10002/10004)
45 --
46 cursor value_uas(pid number, aid number, lid number, lval number) is
47 select profile_option_value
48 from fnd_profile_option_values
49 where profile_option_id = pid
50 and application_id = aid
51 and level_id = lid
52 and level_value = lval;
53
54 --
55 -- this cursor fetches profile option values at the responsibility
56 -- level (10003)
57 --
58 cursor value_resp(pid number, aid number, lval number, laid number) is
59 select profile_option_value
60 from fnd_profile_option_values
61 where profile_option_id = pid
62 and application_id = aid
63 and level_id = 10003
64 and level_value = lval
65 and level_value_application_id = laid;
66
67 begin
68
69 val_z := NULL;
70 cached_z := FALSE;
71
72 -- get profile info from database --
73 open profile_info;
74 fetch profile_info into pid, aid;
75 if (profile_info%NOTFOUND) then
76 val_z := -27323;
77 cached_z := FALSE;
78 return;
79 end if;
80 close profile_info;
81
82 -- get profile value from database --
83 if (level_id_z = 10001 or level_id_z = 10002 or level_id_z = 10004 or
84 level_id_z = 10005 or level_id_z = 10006) then
85 for c1 in value_uas(pid,aid,level_id_z,level_value_z) loop
86 val_z := c1.profile_option_value;
87 cached_z := FALSE;
88 return;
89 end loop;
90 else
91 for c1 in value_resp(pid,aid,level_value_z,level_value_application_z) loop
92 val_z := c1.profile_option_value;
93 cached_z := FALSE;
94 return;
95 end loop;
96 end if;
97
98 val_z := NULL;
99 cached_z := FALSE;
100
101 END GET_SPECIFIC_LEVEL_PROFILES;
102
103
104 -- This procedure is to create the flat file for profile option values
105
106 PROCEDURE utl_debug(p_pref varchar2, p_level varchar2, p_level_val varchar2, p_prof_name varchar2, p_prof_val varchar2, p_appl varchar2 default null, log_file_handle UTL_FILE.FILE_TYPE) IS
107
108
109 BEGIN
110
111 utl_file.put(log_file_handle,p_pref);
112 utl_file.put(log_file_handle,'~');
113 utl_file.put(log_file_handle,p_level);
114 utl_file.put(log_file_handle,'~');
115 utl_file.put(log_file_handle,p_level_val);
116 utl_file.put(log_file_handle,'~');
117 utl_file.put(log_file_handle,p_prof_name);
118 utl_file.put(log_file_handle,'~');
119 utl_file.put(log_file_handle,p_prof_val);
120 utl_file.put(log_file_handle,'~');
121 utl_file.put(log_file_handle,p_appl);
122 utl_file.put(log_file_handle,'~');
123 utl_file.new_line(log_file_handle,1);
124
125 exception
126 when no_data_found then
127 utl_file.put_line(log_file_handle,'No locks');
128
129 when utl_file.invalid_path then
130 log_message('INvalid path');
131
132 when utl_file.invalid_filehandle then
133 log_message('INvalid filehandle');
134
135 when others then
136 log_message('EXCEPTION : '||sqlerrm);
137
138 END;
139
140
141 PROCEDURE GETPROF ( ERRBUF OUT NOCOPY VARCHAR2,
142 RETCODE OUT NOCOPY NUMBER,
143 preference_set_name IN VARCHAR2,
144 usr_name IN varchar2 default NULL,
145 application_name IN varchar2 default NULL,
146 resp_name IN varchar2 default NULL,
147 schema_name IN varchar2,
148 p_file_name IN varchar2)
149 IS
150
151 CURSOR get_profile_name(prod varchar2) is
152 select PROFILE_OPTION_NAME FROM FND_PROFILE_OPTIONS_VL
153 WHERE START_DATE_ACTIVE <= SYSDATE and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
154 AND (SITE_ENABLED_FLAG = 'Y' or USER_ENABLED_FLAG = 'Y')
155 AND UPPER(USER_PROFILE_OPTION_NAME) LIKE prod||'%';
156
157 CURSOR get_dir is
158 select nvl(substr(value,1,instr(value,',')-1),value) from v$parameter where name = 'utl_file_dir';
159
160 var1 varchar2(240);
161 var2 BOOLEAN;
162 var4 BOOLEAN :=TRUE;
163 prod_name varchar2(3);
164 l_start_index number := 1;
165 l_end_index number := 1;
166 l_next_start_index number;
167 usr_id number;
168 appl_id number;
169 resp_id number;
170 mydir varchar2(200) ;
171 log_file_handle UTL_FILE.FILE_TYPE;
172 lv_file_name VARCHAR2(1000):= '';
173
174
175 BEGIN
176
177 log_message('USER NAME=> '||usr_name);
178 log_message('APPLICATION NAME=> '||application_name);
179 log_message('RESPONSIBILITY NAME=> '||resp_name);
180
181 open get_dir;
182 fetch get_dir into mydir;
183 close get_dir;
184
185 IF instr(p_file_name, '.', -1) = 0 then
186 lv_file_name := p_file_name ||'.dat';
187 ELSE
188 lv_file_name := p_file_name ;
189 END IF;
190
191 IF usr_name is NOT NULL THEN
192 select user_id into usr_id from fnd_user where user_name=usr_name;
193 END IF;
194
195 IF application_name is NOT NULL THEN
196 select application_id into appl_id from fnd_application where application_short_name=application_name;
197 END IF;
198
199 IF resp_name is NOT NULL THEN
200 select responsibility_id into resp_id from fnd_responsibility_vl where responsibility_name=resp_name and application_id=appl_id;
201 END IF;
202
203 log_file_handle := utl_file.fopen(mydir, lv_file_name, 'w');
204
205
206 WHILE (var4) LOOP
207
208 l_next_start_index := instr(schema_name,',',l_end_index);
209 IF l_next_start_index = 0 THEN
210 prod_name := substr(schema_name,l_start_index);
211 FOR cur1 in get_profile_name(prod_name) LOOP
212 IF usr_name is NOT NULL THEN
213 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10004,usr_id,0,var1,var2);
214 utl_debug(preference_set_name,'USER',usr_name,cur1.profile_option_name,var1,NULL,log_file_handle);
215 END IF;
216 IF resp_name is NOT NULL THEN
217 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10003,resp_id,appl_id,var1,var2);
218 utl_debug(preference_set_name,'RESP',resp_name,cur1.profile_option_name,var1,application_name,log_file_handle);
219 END IF;
220 IF application_name is NOT NULL THEN
221 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10002,appl_id,0,var1,var2);
222 utl_debug(preference_set_name,'APPL',application_name,cur1.profile_option_name,var1,NULL,log_file_handle);
223
224 END IF;
225 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10001,0,0,var1,var2);
226 utl_debug(preference_set_name,'SITE','SITE',cur1.profile_option_name,var1,NULL,log_file_handle);
227 end LOOP;
228 var4 := FALSE;
229
230 ELSE
231
232 prod_name := substr(schema_name,l_start_index,l_next_start_index-l_start_index);
233 FOR cur1 in get_profile_name(prod_name) LOOP
234 IF usr_name is NOT NULL THEN
235 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10004,usr_id,0,var1,var2);
236 utl_debug(preference_set_name,'USER',usr_name,cur1.profile_option_name,var1,NULL,log_file_handle);
237 END IF;
238 IF resp_name is NOT NULL THEN
239 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10003,resp_id,appl_id,var1,var2);
240 utl_debug(preference_set_name,'RESP',resp_name,cur1.profile_option_name,var1,application_name,log_file_handle);
241 END IF;
242 IF application_name is NOT NULL THEN
243 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10002,appl_id,0,var1,var2);
244 utl_debug(preference_set_name,'APPL',application_name,cur1.profile_option_name,var1,NULL,log_file_handle);
245 END IF;
246 GET_SPECIFIC_LEVEL_PROFILES(cur1.PROFILE_OPTION_NAME,10001,0,0,var1,var2);
247 utl_debug(preference_set_name,'SITE','SITE',cur1.profile_option_name,var1,NULL,log_file_handle);
248
249 end LOOP;
250 l_start_index := l_next_start_index+1;
251 l_end_index := l_start_index;
252 END IF;
253
254 END LOOP;
255
256 utl_file.fclose(log_file_handle);
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 ROLLBACK;
261 ERRBUF := SQLERRM;
262 RETCODE := G_ERROR;
263 log_message(SQLERRM);
264 RAISE;
265
266 END GETPROF;
267 END MSC_GET_PROFILE;
[Home] [Help]