1 package body FND_PROFILE_CATS_PKG as
2 /* $Header: FNDPRCAB.pls 120.4 2005/08/16 05:25:36 stadepal noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAME in VARCHAR2,
6 X_ENABLED in VARCHAR2,
7 X_APPLICATION_ID in NUMBER,
8 X_DISPLAY_NAME in VARCHAR2,
9 X_DESCRIPTION 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 FND_PROFILE_CATS
17 where NAME = upper(X_NAME)
18 and APPLICATION_ID = X_APPLICATION_ID
19 ;
20 begin
21 insert into FND_PROFILE_CATS (
22 ENABLED,
23 NAME,
24 APPLICATION_ID,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_ENABLED,
32 upper(X_NAME),
33 X_APPLICATION_ID,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into FND_PROFILE_CATS_TL (
42 DISPLAY_NAME,
43 NAME,
44 APPLICATION_ID,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 DESCRIPTION,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_DISPLAY_NAME,
55 upper(X_NAME),
56 X_APPLICATION_ID,
57 X_CREATED_BY,
58 X_CREATION_DATE,
59 X_LAST_UPDATED_BY,
60 X_LAST_UPDATE_DATE,
61 X_LAST_UPDATE_LOGIN,
62 X_DESCRIPTION,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from FND_PROFILE_CATS_TL T
70 where T.NAME = upper(X_NAME)
71 and T.APPLICATION_ID = X_APPLICATION_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 open c;
75 fetch c into X_ROWID;
76 if (c%notfound) then
77 close c;
78 raise no_data_found;
79 end if;
80 close c;
81
82 end INSERT_ROW;
83
84 procedure LOCK_ROW (
85 X_NAME in VARCHAR2,
86 X_ENABLED in VARCHAR2,
87 X_APPLICATION_ID in NUMBER,
88 X_DISPLAY_NAME in VARCHAR2,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 ENABLED
93 from FND_PROFILE_CATS
94 where NAME = X_NAME
95 and APPLICATION_ID = X_APPLICATION_ID
96 for update of NAME nowait;
97 recinfo c%rowtype;
98
99 cursor c1 is select
100 DISPLAY_NAME,
101 DESCRIPTION,
102 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
103 from FND_PROFILE_CATS_TL
104 where NAME = X_NAME
105 and APPLICATION_ID = X_APPLICATION_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of NAME 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.ENABLED = X_ENABLED)
118 OR ((recinfo.ENABLED is null) AND (X_ENABLED is null)))
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125
126 for tlinfo in c1 loop
127 if (tlinfo.BASELANG = 'Y') then
128 if ( ((tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
129 OR ((tlinfo.DISPLAY_NAME is null) AND (X_DISPLAY_NAME is null)))
130 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
131 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138 end if;
139 end loop;
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 X_NAME in VARCHAR2,
145 X_ENABLED in VARCHAR2,
146 X_APPLICATION_ID in NUMBER,
147 X_DISPLAY_NAME in VARCHAR2,
148 X_DESCRIPTION in VARCHAR2,
149 X_LAST_UPDATE_DATE in DATE,
150 X_LAST_UPDATED_BY in NUMBER,
151 X_LAST_UPDATE_LOGIN in NUMBER
152 ) is
153 begin
154 update FND_PROFILE_CATS set
155 ENABLED = X_ENABLED,
156 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
157 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
158 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
159 where NAME = upper(X_NAME)
160 and APPLICATION_ID = X_APPLICATION_ID;
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165
166 update FND_PROFILE_CATS_TL set
167 DISPLAY_NAME = X_DISPLAY_NAME,
168 DESCRIPTION = X_DESCRIPTION,
169 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
170 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
171 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
172 SOURCE_LANG = userenv('LANG')
173 where NAME = upper(X_NAME)
174 and APPLICATION_ID = X_APPLICATION_ID
175 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180 end UPDATE_ROW;
181
182 procedure DELETE_ROW (
183 X_NAME in VARCHAR2,
184 X_APPLICATION_ID in NUMBER
185 ) is
186 begin
187 delete from FND_PROFILE_CATS_TL
188 where NAME = upper(X_NAME)
189 and APPLICATION_ID = X_APPLICATION_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 delete from FND_PROFILE_CATS
196 where NAME = upper(X_NAME)
197 and APPLICATION_ID = X_APPLICATION_ID;
198
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203
204 delete from FND_PROFILE_CAT_OPTIONS
205 where CATEGORY_NAME = upper(X_NAME)
206 and APPLICATION_ID = X_APPLICATION_ID;
207 end DELETE_ROW;
208
209 procedure ADD_LANGUAGE
210 is
211 begin
212 delete from FND_PROFILE_CATS_TL T
213 where not exists
214 (select NULL
215 from FND_PROFILE_CATS B
216 where B.NAME = T.NAME
217 and B.APPLICATION_ID = T.APPLICATION_ID
218 );
219
220 update FND_PROFILE_CATS_TL T set (
221 DISPLAY_NAME,
222 DESCRIPTION
223 ) = (select
224 B.DISPLAY_NAME,
225 B.DESCRIPTION
226 from FND_PROFILE_CATS_TL B
227 where B.NAME = T.NAME
228 and B.APPLICATION_ID = T.APPLICATION_ID
229 and B.LANGUAGE = T.SOURCE_LANG)
230 where (
231 T.NAME,
232 T.APPLICATION_ID,
233 T.LANGUAGE
234 ) in (select
235 SUBT.NAME,
236 SUBT.APPLICATION_ID,
237 SUBT.LANGUAGE
238 from FND_PROFILE_CATS_TL SUBB, FND_PROFILE_CATS_TL SUBT
239 where SUBB.NAME = SUBT.NAME
240 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
241 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
242 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
243 or (SUBB.DISPLAY_NAME is null and SUBT.DISPLAY_NAME is not null)
244 or (SUBB.DISPLAY_NAME is not null and SUBT.DISPLAY_NAME is null)
245 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
246 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
247 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
248 ));
249
250 insert into FND_PROFILE_CATS_TL (
251 DISPLAY_NAME,
252 NAME,
253 APPLICATION_ID,
254 CREATED_BY,
255 CREATION_DATE,
256 LAST_UPDATED_BY,
257 LAST_UPDATE_DATE,
258 LAST_UPDATE_LOGIN,
259 DESCRIPTION,
260 LANGUAGE,
261 SOURCE_LANG
262 ) select /*+ ORDERED */
263 B.DISPLAY_NAME,
264 B.NAME,
265 B.APPLICATION_ID,
266 B.CREATED_BY,
267 B.CREATION_DATE,
268 B.LAST_UPDATED_BY,
269 B.LAST_UPDATE_DATE,
270 B.LAST_UPDATE_LOGIN,
271 B.DESCRIPTION,
272 L.LANGUAGE_CODE,
273 B.SOURCE_LANG
274 from FND_PROFILE_CATS_TL B, FND_LANGUAGES L
275 where L.INSTALLED_FLAG in ('I', 'B')
276 and B.LANGUAGE = userenv('LANG')
277 and not exists
278 (select NULL
279 from FND_PROFILE_CATS_TL T
280 where T.NAME = B.NAME
281 and T.APPLICATION_ID = B.APPLICATION_ID
282 and T.LANGUAGE = L.LANGUAGE_CODE);
283 end ADD_LANGUAGE;
284
285 procedure TRANSLATE_ROW (
286 X_NAME in VARCHAR2,
287 X_APPLICATION_SHORT_NAME in VARCHAR2,
288 X_DESCRIPTION in VARCHAR2,
289 X_DISPLAY_NAME in VARCHAR2,
290 X_CUSTOM_MODE in VARCHAR2,
291 X_OWNER in VARCHAR2,
292 X_LAST_UPDATE_DATE in VARCHAR2)
293 is
294 f_luby number; -- entity owner in file
295 f_ludate date; -- entity update date in file
296 db_luby number; -- entity owner in db
297 db_ludate date; -- entity update date in db
298 x_appl_id number;
299 begin
300
301 -- Translate owner to file_last_updated_by
302 f_luby := fnd_load_util.owner_id(x_owner);
303
304 -- Translate char last_update_date to date
305 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
306
307 select APPLICATION_ID
308 into x_appl_id
309 from FND_APPLICATION
310 where APPLICATION_SHORT_NAME = upper(x_application_short_name);
311
312 begin
313 select LAST_UPDATED_BY, LAST_UPDATE_DATE
314 into db_luby, db_ludate
315 from FND_PROFILE_CATS_TL
316 where NAME = upper(x_name)
317 and APPLICATION_ID = x_appl_id
318 and LANGUAGE = userenv('LANG');
319
320 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
321 db_ludate, X_CUSTOM_MODE)) then
322
323 update FND_PROFILE_CATS_TL set
324 DESCRIPTION = X_DESCRIPTION,
325 DISPLAY_NAME = X_DISPLAY_NAME,
326 LAST_UPDATE_DATE = f_ludate,
327 LAST_UPDATED_BY = f_luby,
328 LAST_UPDATE_LOGIN = f_luby,
329 SOURCE_LANG = userenv('LANG')
330 where NAME = upper(X_NAME)
331 and APPLICATION_ID = x_appl_id
332 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
333 end if;
334 exception
335 when no_data_found then
336 null;
337 end;
338 end TRANSLATE_ROW;
339
340 procedure LOAD_ROW (
341 X_NAME in VARCHAR2,
342 X_DESCRIPTION in VARCHAR2,
343 X_DISPLAY_NAME in VARCHAR2,
344 X_ENABLED in VARCHAR2,
345 X_APPLICATION_SHORT_NAME in VARCHAR2,
346 X_OWNER in VARCHAR2,
347 X_CUSTOM_MODE in VARCHAR2,
348 X_LAST_UPDATE_DATE in VARCHAR2)
349 is
350 row_id varchar2(64);
351 app_id number;
352 f_luby number; -- entity owner in file
353 f_ludate date; -- entity update date in file
354 db_luby number; -- entity owner in db
355 db_ludate date; -- entity update date in db
356
357 begin
358
359 select application_id into app_id
360 from fnd_application
361 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
362
363 -- Translate owner to file_last_updated_by
364 f_luby := fnd_load_util.owner_id(x_owner);
365
366 -- Translate char last_update_date to date
367 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
368
369 begin
370 select LAST_UPDATED_BY, LAST_UPDATE_DATE
371 into db_luby, db_ludate
372 from FND_PROFILE_CATS
373 where NAME = upper(x_name)
374 and APPLICATION_ID = app_id;
375
376 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
377 db_ludate, X_CUSTOM_MODE)) then
378
379 FND_PROFILE_CATS_PKG.UPDATE_ROW (
380 X_NAME => X_NAME,
381 X_DESCRIPTION => X_DESCRIPTION,
382 X_DISPLAY_NAME => X_DISPLAY_NAME,
383 X_ENABLED => X_ENABLED,
384 X_APPLICATION_ID => app_id,
385 X_LAST_UPDATE_DATE => f_ludate,
386 X_LAST_UPDATED_BY => f_luby,
387 X_LAST_UPDATE_LOGIN => f_luby);
388 end if;
389 exception
390 when no_data_found then
391
392 FND_PROFILE_CATS_PKG.INSERT_ROW (
393 X_ROWID => row_id,
394 X_NAME => X_NAME,
395 X_DESCRIPTION => X_DESCRIPTION,
396 X_DISPLAY_NAME => X_DISPLAY_NAME,
397 X_ENABLED => X_ENABLED,
398 X_APPLICATION_ID => app_id,
399 X_CREATION_DATE => f_ludate,
400 X_CREATED_BY => f_luby,
401 X_LAST_UPDATE_DATE => f_ludate,
402 X_LAST_UPDATED_BY => f_luby,
403 X_LAST_UPDATE_LOGIN => f_luby);
404 end;
405 end LOAD_ROW;
406
407 end FND_PROFILE_CATS_PKG;
[Home] [Help]