[Home] [Help]
PACKAGE BODY: APPS.JTF_AE_PROFMAPS
Source
1 package body JTF_AE_PROFMAPS as
2 /* $Header: JTFAEPMB.pls 120.1 2005/07/02 01:59:54 appldev ship $ */
3 procedure INSERT_ROW (
4 X_PROFILE_MAPPINGS_ID in NUMBER,
5 X_SECURITY_GROUP_ID in NUMBER,
6 X_OWNERID in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_BASETABLE in VARCHAR2,
9 X_PROFILENAME 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_MAPPINGS_B
17 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_ID
18 ;
19 begin
20 insert into JTF_PROFILE_MAPPINGS_B (
21 SECURITY_GROUP_ID,
22 PROFILE_MAPPINGS_ID,
23 OWNERID,
24 OBJECT_VERSION_NUMBER,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_SECURITY_GROUP_ID,
32 X_PROFILE_MAPPINGS_ID,
33 X_OWNERID,
34 X_OBJECT_VERSION_NUMBER,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41
42 insert into JTF_PROFILE_MAPPINGS_TL (
43 LAST_UPDATED_BY,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 SECURITY_GROUP_ID,
48 PROFILE_MAPPINGS_ID,
49 BASETABLE,
50 PROFILENAME,
51 LAST_UPDATE_LOGIN,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_LAST_UPDATED_BY,
56 X_CREATION_DATE,
57 X_CREATED_BY,
58 X_LAST_UPDATE_DATE,
59 X_SECURITY_GROUP_ID,
60 X_PROFILE_MAPPINGS_ID,
61 X_BASETABLE,
62 X_PROFILENAME,
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_MAPPINGS_TL T
71 where T.PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_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_MAPPINGS_ID in NUMBER,
85 X_SECURITY_GROUP_ID in NUMBER,
86 X_OWNERID in VARCHAR2,
87 X_OBJECT_VERSION_NUMBER in NUMBER,
88 X_BASETABLE in VARCHAR2,
89 X_PROFILENAME in VARCHAR2
90 ) is
91 cursor c is select
92 SECURITY_GROUP_ID,
93 OWNERID,
94 OBJECT_VERSION_NUMBER
95 from JTF_PROFILE_MAPPINGS_B
96 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_ID
97 for update of PROFILE_MAPPINGS_ID nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 BASETABLE,
102 PROFILENAME,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from JTF_PROFILE_MAPPINGS_TL
105 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of PROFILE_MAPPINGS_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.OWNERID = X_OWNERID)
120 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
121 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 for tlinfo in c1 loop
130 if (tlinfo.BASELANG = 'Y') then
131 if ( ((tlinfo.BASETABLE = X_BASETABLE)
132 OR ((tlinfo.BASETABLE is null) AND (X_BASETABLE is null)))
133 AND ((tlinfo.PROFILENAME = X_PROFILENAME)
134 OR ((tlinfo.PROFILENAME is null) AND (X_PROFILENAME 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_MAPPINGS_ID in NUMBER,
148 X_SECURITY_GROUP_ID in NUMBER,
149 X_OWNERID in VARCHAR2,
150 X_OBJECT_VERSION_NUMBER in NUMBER,
151 X_BASETABLE in VARCHAR2,
152 X_PROFILENAME 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_MAPPINGS_B set
159 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
160 OWNERID = X_OWNERID,
161 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
162 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
165 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_ID;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170
171 update JTF_PROFILE_MAPPINGS_TL set
172 BASETABLE = X_BASETABLE,
173 PROFILENAME = X_PROFILENAME,
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_MAPPINGS_ID = X_PROFILE_MAPPINGS_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_MAPPINGS_ID in NUMBER
188 ) is
189 begin
190 delete from JTF_PROFILE_MAPPINGS_TL
191 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_ID;
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 delete from JTF_PROFILE_MAPPINGS_B
198 where PROFILE_MAPPINGS_ID = X_PROFILE_MAPPINGS_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_MAPPINGS_TL T
209 where not exists
210 (select NULL
211 from JTF_PROFILE_MAPPINGS_B B
212 where B.PROFILE_MAPPINGS_ID = T.PROFILE_MAPPINGS_ID
213 );
214
215 update JTF_PROFILE_MAPPINGS_TL T set (
216 BASETABLE,
217 PROFILENAME
218 ) = (select
219 B.BASETABLE,
220 B.PROFILENAME
221 from JTF_PROFILE_MAPPINGS_TL B
222 where B.PROFILE_MAPPINGS_ID = T.PROFILE_MAPPINGS_ID
223 and B.LANGUAGE = T.SOURCE_LANG)
224 where (
225 T.PROFILE_MAPPINGS_ID,
226 T.LANGUAGE
227 ) in (select
228 SUBT.PROFILE_MAPPINGS_ID,
229 SUBT.LANGUAGE
230 from JTF_PROFILE_MAPPINGS_TL SUBB, JTF_PROFILE_MAPPINGS_TL SUBT
231 where SUBB.PROFILE_MAPPINGS_ID = SUBT.PROFILE_MAPPINGS_ID
232 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
233 and (SUBB.BASETABLE <> SUBT.BASETABLE
234 or (SUBB.BASETABLE is null and SUBT.BASETABLE is not null)
235 or (SUBB.BASETABLE is not null and SUBT.BASETABLE is null)
236 or SUBB.PROFILENAME <> SUBT.PROFILENAME
237 or (SUBB.PROFILENAME is null and SUBT.PROFILENAME is not null)
238 or (SUBB.PROFILENAME is not null and SUBT.PROFILENAME is null)
239 ));
240
241 insert into JTF_PROFILE_MAPPINGS_TL (
242 LAST_UPDATED_BY,
243 CREATION_DATE,
244 CREATED_BY,
245 LAST_UPDATE_DATE,
246 SECURITY_GROUP_ID,
247 PROFILE_MAPPINGS_ID,
248 BASETABLE,
249 PROFILENAME,
250 DESCRIPTION,
251 LAST_UPDATE_LOGIN,
252 LANGUAGE,
253 SOURCE_LANG
254 ) select
255 B.LAST_UPDATED_BY,
256 B.CREATION_DATE,
257 B.CREATED_BY,
258 B.LAST_UPDATE_DATE,
259 B.SECURITY_GROUP_ID,
260 B.PROFILE_MAPPINGS_ID,
261 B.BASETABLE,
262 B.PROFILENAME,
263 B.DESCRIPTION,
264 B.LAST_UPDATE_LOGIN,
265 L.LANGUAGE_CODE,
266 B.SOURCE_LANG
267 from JTF_PROFILE_MAPPINGS_TL B, FND_LANGUAGES L
268 where L.INSTALLED_FLAG in ('I', 'B')
269 and B.LANGUAGE = userenv('LANG')
270 and not exists
271 (select NULL
272 from JTF_PROFILE_MAPPINGS_TL T
273 where T.PROFILE_MAPPINGS_ID = B.PROFILE_MAPPINGS_ID
274 and T.LANGUAGE = L.LANGUAGE_CODE);
275 end ADD_LANGUAGE;
276
277 procedure TRANSLATE_ROW(
278 X_PROFILE_MAPPINGS_ID in NUMBER,
279 X_BASETABLE in VARCHAR2,
280 X_PROFILENAME in VARCHAR2,
281 X_OWNER in VARCHAR2
282 ) is
283 begin
284
285 update JTF_PROFILE_MAPPINGS_TL set
286 BASETABLE = X_BASETABLE,
287 PROFILENAME = X_PROFILENAME,
288 SOURCE_LANG = userenv('LANG'),
289 last_update_date = sysdate,
290 last_updated_by = decode(X_OWNER,'SEED',1,0),
291 last_update_login = 0
292 where PROFILE_MAPPINGS_ID = to_number(X_PROFILE_MAPPINGS_ID)
293 and userenv('LANG') in (language, source_lang);
294
295 end TRANSLATE_ROW;
296
297 procedure LOAD_ROW(
298 X_PROFILE_MAPPINGS_ID in NUMBER,
299 X_SECURITY_GROUP_ID in NUMBER,
300 X_OWNERID in VARCHAR2,
301 X_OBJECT_VERSION_NUMBER in NUMBER,
302 X_BASETABLE in VARCHAR2,
303 X_PROFILENAME in VARCHAR2,
304 X_OWNER in VARCHAR2
305 ) is
306
307 l_rowid VARCHAR2(64);
308 l_user_id NUMBER := 0;
309
310 begin
311 if(x_owner = 'SEED') then
312 l_user_id := 1;
313 end if;
314
315 -- Update row if present
316 JTF_AE_PROFMAPS.UPDATE_ROW (
317 X_PROFILE_MAPPINGS_ID => X_PROFILE_MAPPINGS_ID,
318 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
319 X_OWNERID => X_OWNERID,
320 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
321 X_BASETABLE => X_BASETABLE,
322 X_PROFILENAME => X_PROFILENAME,
323 X_LAST_UPDATE_DATE => sysdate,
324 X_LAST_UPDATED_BY => l_user_id,
325 X_LAST_UPDATE_LOGIN => 0);
326 exception
327 when NO_DATA_FOUND then
328 -- Insert a row
329 JTF_AE_PROFMAPS.INSERT_ROW (
330 X_PROFILE_MAPPINGS_ID => X_PROFILE_MAPPINGS_ID,
331 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
332 X_OWNERID => X_OWNERID,
333 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
334 X_BASETABLE => X_BASETABLE,
335 X_PROFILENAME => X_PROFILENAME,
336 X_CREATION_DATE => sysdate,
337 X_CREATED_BY => l_user_id,
338 X_LAST_UPDATE_DATE => sysdate,
339 X_LAST_UPDATED_BY => l_user_id,
340 X_LAST_UPDATE_LOGIN => 0
341 );
342
343
344 end LOAD_ROW;
345
346 end JTF_AE_PROFMAPS;