[Home] [Help]
PACKAGE BODY: APPS.JTF_AE_PROFRULES
Source
1 package body JTF_AE_PROFRULES as
2 /* $Header: JTFAEPRB.pls 120.1 2005/07/02 02:00:09 appldev ship $ */
3 procedure INSERT_ROW (
4 X_PROFILE_RULES_ID in NUMBER,
5 X_SECURITY_GROUP_ID in NUMBER,
6 X_PROFILE_METADATA_ID in NUMBER,
7 X_RULE in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_BASE_PROPERTY_VALUE in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from JTF_PROFILE_RULES_B
17 where PROFILE_RULES_ID = X_PROFILE_RULES_ID
18 ;
19 begin
20 insert into JTF_PROFILE_RULES_B (
21 SECURITY_GROUP_ID,
22 PROFILE_RULES_ID,
23 PROFILE_METADATA_ID,
24 RULE,
25 OBJECT_VERSION_NUMBER,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_SECURITY_GROUP_ID,
33 X_PROFILE_RULES_ID,
34 X_PROFILE_METADATA_ID,
35 X_RULE,
36 X_OBJECT_VERSION_NUMBER,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 insert into JTF_PROFILE_RULES_TL (
45 SECURITY_GROUP_ID,
46 PROFILE_RULES_ID,
47 BASE_PROPERTY_VALUE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_SECURITY_GROUP_ID,
57 X_PROFILE_RULES_ID,
58 X_BASE_PROPERTY_VALUE,
59 X_CREATION_DATE,
60 X_CREATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_LOGIN,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from JTF_PROFILE_RULES_TL T
71 where T.PROFILE_RULES_ID = X_PROFILE_RULES_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 open c;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_PROFILE_RULES_ID in NUMBER,
85 X_SECURITY_GROUP_ID in NUMBER,
86 X_PROFILE_METADATA_ID in NUMBER,
87 X_RULE in VARCHAR2,
88 X_OBJECT_VERSION_NUMBER in NUMBER,
89 X_BASE_PROPERTY_VALUE in VARCHAR2
90 ) is
91 cursor c is select
92 SECURITY_GROUP_ID,
93 PROFILE_METADATA_ID,
94 RULE,
95 OBJECT_VERSION_NUMBER
96 from JTF_PROFILE_RULES_B
97 where PROFILE_RULES_ID = X_PROFILE_RULES_ID
98 for update of PROFILE_RULES_ID nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 BASE_PROPERTY_VALUE,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from JTF_PROFILE_RULES_TL
105 where PROFILE_RULES_ID = X_PROFILE_RULES_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of PROFILE_RULES_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
118 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
119 AND (recinfo.PROFILE_METADATA_ID = X_PROFILE_METADATA_ID)
120 AND ((recinfo.RULE = X_RULE)
121 OR ((recinfo.RULE is null) AND (X_RULE is null)))
122 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
123 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130
131 for tlinfo in c1 loop
132 if (tlinfo.BASELANG = 'Y') then
133 if ( ((tlinfo.BASE_PROPERTY_VALUE = X_BASE_PROPERTY_VALUE)
134 OR ((tlinfo.BASE_PROPERTY_VALUE is null) AND (X_BASE_PROPERTY_VALUE is null)))
135 ) then
136 null;
137 else
138 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
139 app_exception.raise_exception;
140 end if;
141 end if;
142 end loop;
143 return;
144 end LOCK_ROW;
145
146 procedure UPDATE_ROW (
147 X_PROFILE_RULES_ID in NUMBER,
148 X_SECURITY_GROUP_ID in NUMBER,
149 X_PROFILE_METADATA_ID in NUMBER,
150 X_RULE in VARCHAR2,
151 X_OBJECT_VERSION_NUMBER in NUMBER,
152 X_BASE_PROPERTY_VALUE in VARCHAR2,
153 X_LAST_UPDATE_DATE in DATE,
154 X_LAST_UPDATED_BY in NUMBER,
155 X_LAST_UPDATE_LOGIN in NUMBER
156 ) is
157 begin
158 update JTF_PROFILE_RULES_B set
159 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
160 PROFILE_METADATA_ID = X_PROFILE_METADATA_ID,
161 RULE = X_RULE,
162 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
166 where PROFILE_RULES_ID = X_PROFILE_RULES_ID;
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171
172 update JTF_PROFILE_RULES_TL set
173 BASE_PROPERTY_VALUE = X_BASE_PROPERTY_VALUE,
174 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
175 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
177 SOURCE_LANG = userenv('LANG')
178 where PROFILE_RULES_ID = X_PROFILE_RULES_ID
179 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 end UPDATE_ROW;
185
186 procedure DELETE_ROW (
187 X_PROFILE_RULES_ID in NUMBER
188 ) is
189 begin
190 delete from JTF_PROFILE_RULES_TL
191 where PROFILE_RULES_ID = X_PROFILE_RULES_ID;
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 delete from JTF_PROFILE_RULES_B
198 where PROFILE_RULES_ID = X_PROFILE_RULES_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end DELETE_ROW;
204
205 procedure ADD_LANGUAGE
206 is
207 begin
208 delete from JTF_PROFILE_RULES_TL T
209 where not exists
210 (select NULL
211 from JTF_PROFILE_RULES_B B
212 where B.PROFILE_RULES_ID = T.PROFILE_RULES_ID
213 );
214
215 update JTF_PROFILE_RULES_TL T set (
216 BASE_PROPERTY_VALUE
217 ) = (select
218 B.BASE_PROPERTY_VALUE
219 from JTF_PROFILE_RULES_TL B
220 where B.PROFILE_RULES_ID = T.PROFILE_RULES_ID
221 and B.LANGUAGE = T.SOURCE_LANG)
222 where (
223 T.PROFILE_RULES_ID,
224 T.LANGUAGE
225 ) in (select
226 SUBT.PROFILE_RULES_ID,
227 SUBT.LANGUAGE
228 from JTF_PROFILE_RULES_TL SUBB, JTF_PROFILE_RULES_TL SUBT
229 where SUBB.PROFILE_RULES_ID = SUBT.PROFILE_RULES_ID
230 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
231 and (SUBB.BASE_PROPERTY_VALUE <> SUBT.BASE_PROPERTY_VALUE
232 or (SUBB.BASE_PROPERTY_VALUE is null and SUBT.BASE_PROPERTY_VALUE is not null)
233 or (SUBB.BASE_PROPERTY_VALUE is not null and SUBT.BASE_PROPERTY_VALUE is null)
234 ));
235
236 insert into JTF_PROFILE_RULES_TL (
237 SECURITY_GROUP_ID,
238 PROFILE_RULES_ID,
239 BASE_PROPERTY_VALUE,
240 DESCRIPTION,
241 CREATION_DATE,
242 CREATED_BY,
243 LAST_UPDATE_DATE,
244 LAST_UPDATED_BY,
245 LAST_UPDATE_LOGIN,
246 LANGUAGE,
247 SOURCE_LANG
248 ) select
249 B.SECURITY_GROUP_ID,
250 B.PROFILE_RULES_ID,
251 B.BASE_PROPERTY_VALUE,
252 B.DESCRIPTION,
253 B.CREATION_DATE,
254 B.CREATED_BY,
255 B.LAST_UPDATE_DATE,
256 B.LAST_UPDATED_BY,
257 B.LAST_UPDATE_LOGIN,
258 L.LANGUAGE_CODE,
259 B.SOURCE_LANG
260 from JTF_PROFILE_RULES_TL B, FND_LANGUAGES L
261 where L.INSTALLED_FLAG in ('I', 'B')
262 and B.LANGUAGE = userenv('LANG')
263 and not exists
264 (select NULL
265 from JTF_PROFILE_RULES_TL T
266 where T.PROFILE_RULES_ID = B.PROFILE_RULES_ID
267 and T.LANGUAGE = L.LANGUAGE_CODE);
268 end ADD_LANGUAGE;
269
270 procedure TRANSLATE_ROW(
271 X_PROFILE_RULES_ID in NUMBER,
272 X_BASE_PROPERTY_VALUE in VARCHAR2,
273 X_OWNER in VARCHAR2
274 ) is
275
276 begin
277 update JTF_PROFILE_RULES_TL set
278 BASE_PROPERTY_VALUE = X_BASE_PROPERTY_VALUE,
279 SOURCE_LANG = userenv('LANG'),
280 last_update_date = sysdate,
281 last_updated_by = decode(X_OWNER,'SEED',1,0),
282 last_update_login = 0
283 where PROFILE_RULES_ID = to_number(X_PROFILE_RULES_ID) and
284 userenv('LANG') in (language, source_lang);
285
286 end TRANSLATE_ROW;
287
288 procedure LOAD_ROW(
289 X_PROFILE_RULES_ID in NUMBER,
290 X_SECURITY_GROUP_ID in NUMBER,
291 X_PROFILE_METADATA_ID in NUMBER,
292 X_RULE in VARCHAR2,
293 X_BASE_PROPERTY_VALUE in VARCHAR2,
294 X_OBJECT_VERSION_NUMBER in NUMBER,
295 X_OWNER in VARCHAR2
296 ) is
297
298 l_rowid VARCHAR2(64);
299 l_user_id NUMBER := 0;
300
301 begin
302 if(x_owner = 'SEED') then
303 l_user_id := 1;
304 end if;
305
306 -- Update row if present
307 JTF_AE_PROFRULES.UPDATE_ROW (
308 X_PROFILE_RULES_ID => X_PROFILE_RULES_ID,
309 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
310 X_PROFILE_METADATA_ID => X_PROFILE_METADATA_ID,
311 X_RULE => X_RULE,
312 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
313 X_BASE_PROPERTY_VALUE => X_BASE_PROPERTY_VALUE,
314 X_LAST_UPDATE_DATE => sysdate,
315 X_LAST_UPDATED_BY => l_user_id,
316 X_LAST_UPDATE_LOGIN => 0);
317 exception
318 when NO_DATA_FOUND then
319 -- Insert a row
320 JTF_AE_PROFRULES.INSERT_ROW (
321 X_PROFILE_RULES_ID => X_PROFILE_RULES_ID,
322 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
323 X_PROFILE_METADATA_ID => X_PROFILE_METADATA_ID,
324 X_RULE => X_RULE,
325 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
326 X_BASE_PROPERTY_VALUE => X_BASE_PROPERTY_VALUE,
327 X_CREATION_DATE => sysdate,
328 X_CREATED_BY => l_user_id,
329 X_LAST_UPDATE_DATE => sysdate,
330 X_LAST_UPDATED_BY => l_user_id,
331 X_LAST_UPDATE_LOGIN => 0
332 );
333
334 end LOAD_ROW;
335
336 end JTF_AE_PROFRULES;