[Home] [Help]
PACKAGE BODY: APPS.MSC_PROFILE_PRE_PROCESS
Source
1 PACKAGE BODY MSC_PROFILE_PRE_PROCESS AS -- body
2 /* $Header: MSCPFPPB.pls 115.0 2004/07/30 08:58:23 rawasthi noship $ */
3
4 v_pref_set varchar2(50);
5 v_usr_name varchar2(15);
6 v_current_user NUMBER := -1;
7 v_sql_stmt varchar2(100);
8
9 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
10 IS
11 BEGIN
12 IF fnd_global.conc_request_id > 0 THEN
13 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
14 ELSE
15 null;
16 --DBMS_OUTPUT.PUT_LINE( pBUFF);
17 END IF;
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN;
21 END LOG_MESSAGE;
22
23
24 PROCEDURE setprofile(ERRBUF OUT NOCOPY VARCHAR2,
25 RETCODE OUT NOCOPY NUMBER,
26 p_preference_set_name IN VARCHAR2,
27 p_upload_profile IN NUMBER) IS
28
29 CURSOR c1 is
30 select profile_option_name,profile_option_value,level_type,level_name,application_name from msc_profiles where preference_set_name=p_preference_set_name;
31
32 usr_flag_enabled varchar2(5);
33 appl_flag_enabled varchar2(5);
34 resp_flag_enabled varchar2(5);
35 usr_id NUMBER;
36 appl_id NUMBER;
37 resp_id NUMBER;
38 ret_value BOOLEAN;
39 x_prof_err EXCEPTION;
40 l_prof_name msc_profiles.profile_option_name%TYPE;
41 l_level_type msc_profiles.level_type%TYPE;
42 l_level_name msc_profiles.level_name%TYPE;
43
44
45 BEGIN
46
47
48 FOR cur_rec in c1 LOOP
49
50 l_level_type := cur_rec.level_type;
51 l_level_name := cur_rec.level_name;
52 l_prof_name := cur_rec.profile_option_name;
53
54
55 IF cur_rec.level_type='USER' THEN
56
57 v_sql_stmt := 1;
58
59 SELECT user_id into usr_id from fnd_user where user_name=cur_rec.level_name;
60
61 ret_value := FND_PROFILE.SAVE(cur_rec.profile_option_name,cur_rec.profile_option_value,'USER',usr_id);
62 IF ret_value = FALSE THEN
63 RAISE x_prof_err;
64 END IF;
65
66
67 ELSIF cur_rec.level_type='RESP' THEN
68
69 v_sql_stmt := 2;
70
71
72 SELECT application_id into appl_id from fnd_application where application_short_name=cur_rec.application_name;
73
74 v_sql_stmt := 3;
75
76 SELECT responsibility_id into resp_id from fnd_responsibility_vl where responsibility_name=cur_rec.level_name and application_id=appl_id;
77
78
79 ret_value := FND_PROFILE.SAVE(cur_rec.profile_option_name,cur_rec.profile_option_value,'RESP',resp_id,appl_id);
80 IF ret_value = FALSE THEN
81 RAISE x_prof_err;
82 END IF;
83
84 ELSIF cur_rec.level_type='APPL' THEN
85
86 v_sql_stmt := 4;
87
88 select application_id into appl_id from fnd_application where application_short_name=cur_rec.level_name;
89
90 ret_value := FND_PROFILE.SAVE(cur_rec.profile_option_name,cur_rec.profile_option_value,'APPL',appl_id);
91 IF ret_value = FALSE THEN
92 RAISE x_prof_err;
93 END IF;
94
95 ELSE
96 --Check if the user has selected the upload site level values to YES.
97 IF p_upload_profile=SYS_YES THEN
98 ret_value := FND_PROFILE.SAVE(cur_rec.profile_option_name,cur_rec.profile_option_value,'SITE');
99 IF ret_value = FALSE THEN
100 RAISE x_prof_err;
101 END IF;
102 END IF;
103
104 END IF;
105 END LOOP;
106 COMMIT;
107
108 EXCEPTION
109
110 WHEN NO_DATA_FOUND THEN
111 ROLLBACK;
112 ERRBUF := SQLERRM;
113 RETCODE := G_ERROR;
114 LOG_MESSAGE('The level value ' || l_level_name || ' has errors at ' || l_level_type || ' level');
115 LOG_MESSAGE('The error occured while processing the profile ' || l_prof_name);
116 LOG_MESSAGE('The error occured at stmt => ' || v_sql_stmt);
117 LOG_MESSAGE(SQLERRM);
118
119 WHEN OTHERS THEN
120 ROLLBACK;
121 ERRBUF := SQLERRM;
122 RETCODE := G_ERROR;
123 LOG_MESSAGE('The profile option ' ||l_prof_name || ' has errors');
124 LOG_MESSAGE(SQLERRM);
125
126 END setprofile;
127
128
129 PROCEDURE MSC_PROF_PRE_PROCESS (ERRBUF OUT NOCOPY VARCHAR2,
130 RETCODE OUT NOCOPY NUMBER,
131 p_preference_set_name IN VARCHAR2,
132 p_upload_profile IN NUMBER)
133 IS
134
135 CURSOR c2 IS
136 SELECT
137 preference_set_name,
138 level_type,
139 level_name,
140 profile_option_name,
141 profile_option_value,
142 application_name,
143 process_flag
144 FROM MSC_ST_PROFILES
145 WHERE preference_set_name = p_preference_set_name
146 AND process_flag=1;
147
148
149 BEGIN
150
151 v_sql_stmt := 5;
152
153 UPDATE MSC_ST_PROFILES msp1
154 SET PROCESS_FLAG = 3
155 WHERE EXISTS (select 1 from msc_st_profiles msp2
156 where msp2.preference_set_name=msp1.preference_set_name
157 and nvl(msp2.level_name,'-27323')=nvl(msp1.level_name,'-27323')
158 and nvl(msp2.level_type,'-27323')=nvl(msp1.level_type,'-27323')
159 and msp2.profile_option_name = msp1.profile_option_name
160 and nvl(msp2.application_name,'-27323') = nvl(msp1.application_name,'-27323')
161 and msp2.rowid <> msp1.rowid
162 and msp2.process_flag=1
163 )
164 AND PROCESS_FLAG = 1
165 AND PREFERENCE_SET_NAME = p_preference_set_name;
166
167
168 v_sql_stmt := 6;
169
170 DELETE FROM MSC_PROFILES
171 WHERE PREFERENCE_SET_NAME = p_preference_set_name ;
172
173 FOR c_rec in c2 LOOP
174
175
176 /* UPDATE MSC_PROFILES SET
177 PROFILE_OPTION_VALUE = c_rec.PROFILE_OPTION_VALUE
178 WHERE PREFERENCE_SET_NAME = c_rec.PREFERENCE_SET_NAME
179 AND nvl(level_name,'-27323') = nvl(c_rec.level_name,'-27323')
180 AND nvl(level_type,'-27323') = nvl(c_rec.level_type,'-27323')
181 AND PROFILE_OPTION_NAME = c_rec.PROFILE_OPTION_NAME
182 AND nvl(APPLICATION_NAME,'-27323') = nvl(c_rec.APPLICATION_NAME,'-27323')
183 AND c_rec.PROCESS_FLAG=1;
184
185 IF SQL%NOTFOUND THEN */
186 IF c_rec.process_flag=1 THEN
187
188 insert into msc_profiles
189 (
190 PREFERENCE_SET_NAME,
191 LEVEL_TYPE,
192 LEVEL_NAME,
193 PROFILE_OPTION_NAME,
194 PROFILE_OPTION_VALUE,
195 APPLICATION_NAME,
196 LAST_UPDATE_DATE,
197 LAST_UPDATED_BY,
198 CREATION_DATE,
199 CREATED_BY,
200 LAST_UPDATE_LOGIN
201 )
202 values
203 (
204 c_rec.PREFERENCE_SET_NAME,
205 c_rec.LEVEL_TYPE,
206 c_rec.LEVEL_NAME,
207 c_rec.PROFILE_OPTION_NAME,
208 c_rec.PROFILE_OPTION_VALUE,
209 c_rec.APPLICATION_NAME,
210 SYSDATE,
211 v_current_user,
212 SYSDATE,
213 v_current_user,
214 v_current_user
215 );
216
217 END IF;
218
219 UPDATE msc_st_profiles set process_flag = 5
220 WHERE PREFERENCE_SET_NAME = c_rec.PREFERENCE_SET_NAME
221 AND nvl(LEVEL_NAME,'-27223') = nvl(c_rec.LEVEL_NAME,'-27223')
222 AND nvl(level_type,'-27323') = nvl(c_rec.level_type,'-27323')
223 AND PROFILE_OPTION_NAME = c_rec.PROFILE_OPTION_NAME
224 AND nvl(APPLICATION_NAME,'-27323') = nvl(c_rec.APPLICATION_NAME,'-27323')
225 AND nvl(PROFILE_OPTION_VALUE,'-27223') = nvl(c_rec.PROFILE_OPTION_VALUE,'-27223')
226 AND process_flag = 1;
227
228
229 END LOOP;
230
231 v_sql_stmt := 7;
232
233 DELETE from msc_st_profiles where process_flag=5;
234
235 setprofile(ERRBUF => ERRBUF,
236 RETCODE => RETCODE,
237 p_preference_set_name => p_preference_set_name,
238 p_upload_profile => p_upload_profile);
239
240 EXCEPTION
241
242 WHEN NO_DATA_FOUND THEN
243 ROLLBACK;
244 ERRBUF := SQLERRM;
245 RETCODE := G_ERROR;
246 LOG_MESSAGE('The error occured at stmt ' || v_sql_stmt);
247 LOG_MESSAGE(SQLERRM);
248
249 WHEN OTHERS THEN
250 ROLLBACK;
251 ERRBUF := SQLERRM;
252 RETCODE := G_ERROR;
253 LOG_MESSAGE('The error occured at stmt ' || v_sql_stmt);
254 LOG_MESSAGE(SQLERRM);
255
256 END MSC_PROF_PRE_PROCESS;
257
258 END MSC_PROFILE_PRE_PROCESS;