[Home] [Help]
PACKAGE BODY: APPS.ITA_SETUP_GROUPS_PKG
Source
1 package body ITA_SETUP_GROUPS_PKG as
2 /* $Header: itastgrb.pls 120.3 2006/01/17 12:12:30 adixit noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SETUP_GROUP_CODE in VARCHAR2,
6 X_TABLE_ID in NUMBER,
7 X_TABLE_APP_ID in NUMBER,
8 X_CONTEXT_PARAMETER_CODE in VARCHAR2,
9 X_CONTEXT_PARAMETER_CODE2 in VARCHAR2,
10 X_HIERARCHY_LEVEL in VARCHAR2,
11 X_AUDIT_START_DATE in DATE,
12 X_AUDIT_END_DATE in DATE,
13 X_OBJECT_VERSION_NUMBER in NUMBER,
14 X_SETUP_GROUP_NAME in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from ITA_SETUP_GROUPS_B
22 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
23 ;
24 begin
25 insert into ITA_SETUP_GROUPS_B (
26 TABLE_ID,
27 TABLE_APP_ID,
28 CONTEXT_PARAMETER_CODE,
29 CONTEXT_PARAMETER_CODE2,
30 HIERARCHY_LEVEL,
31 SETUP_GROUP_CODE,
32 AUDIT_START_DATE,
33 AUDIT_END_DATE,
34 OBJECT_VERSION_NUMBER,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 X_TABLE_ID,
42 X_TABLE_APP_ID,
43 X_CONTEXT_PARAMETER_CODE,
44 X_CONTEXT_PARAMETER_CODE2,
45 X_HIERARCHY_LEVEL,
46 X_SETUP_GROUP_CODE,
47 X_AUDIT_START_DATE,
48 X_AUDIT_END_DATE,
49 X_OBJECT_VERSION_NUMBER,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_LOGIN
55 );
56
57 insert into ITA_SETUP_GROUPS_TL (
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 OBJECT_VERSION_NUMBER,
64 SETUP_GROUP_CODE,
65 SETUP_GROUP_NAME,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_CREATED_BY,
70 X_CREATION_DATE,
71 X_LAST_UPDATED_BY,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATE_LOGIN,
74 X_OBJECT_VERSION_NUMBER,
75 X_SETUP_GROUP_CODE,
76 X_SETUP_GROUP_NAME,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from ITA_SETUP_GROUPS_TL T
84 where T.SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_SETUP_GROUP_CODE in VARCHAR2,
99 X_TABLE_ID in NUMBER,
100 X_TABLE_APP_ID in NUMBER,
101 X_CONTEXT_PARAMETER_CODE in VARCHAR2,
102 X_CONTEXT_PARAMETER_CODE2 in VARCHAR2,
103 X_HIERARCHY_LEVEL in VARCHAR2,
104 X_AUDIT_START_DATE in DATE,
105 X_AUDIT_END_DATE in DATE,
106 X_OBJECT_VERSION_NUMBER in NUMBER,
107 X_SETUP_GROUP_NAME in VARCHAR2
108 ) is
109 cursor c is select
110 TABLE_ID,
111 TABLE_APP_ID,
112 CONTEXT_PARAMETER_CODE,
113 CONTEXT_PARAMETER_CODE2,
114 HIERARCHY_LEVEL,
115 AUDIT_START_DATE,
116 AUDIT_END_DATE,
117 OBJECT_VERSION_NUMBER
118 from ITA_SETUP_GROUPS_B
119 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
120 for update of SETUP_GROUP_CODE nowait;
121 recinfo c%rowtype;
122
123 cursor c1 is select
124 SETUP_GROUP_NAME,
125 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
126 from ITA_SETUP_GROUPS_TL
127 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
128 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
129 for update of SETUP_GROUP_CODE nowait;
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( ((recinfo.TABLE_ID = X_TABLE_ID)
140 OR ((recinfo.TABLE_ID is null) AND (X_TABLE_ID is null)))
141 AND ((recinfo.TABLE_APP_ID = X_TABLE_APP_ID)
142 OR ((recinfo.TABLE_APP_ID is null) AND (X_TABLE_APP_ID is null)))
143 AND ((recinfo.CONTEXT_PARAMETER_CODE = X_CONTEXT_PARAMETER_CODE)
144 OR ((recinfo.CONTEXT_PARAMETER_CODE is null) AND (X_CONTEXT_PARAMETER_CODE is null)))
145 AND ((recinfo.CONTEXT_PARAMETER_CODE2 = X_CONTEXT_PARAMETER_CODE2)
146 OR ((recinfo.CONTEXT_PARAMETER_CODE2 is null) AND (X_CONTEXT_PARAMETER_CODE2 is null)))
147 AND ((recinfo.HIERARCHY_LEVEL = X_HIERARCHY_LEVEL)
148 OR ((recinfo.HIERARCHY_LEVEL is null) AND (X_HIERARCHY_LEVEL is null)))
149 AND ((recinfo.AUDIT_START_DATE = X_AUDIT_START_DATE)
150 OR ((recinfo.AUDIT_START_DATE is null) AND (X_AUDIT_START_DATE is null)))
151 AND ((recinfo.AUDIT_END_DATE = X_AUDIT_END_DATE)
152 OR ((recinfo.AUDIT_END_DATE is null) AND (X_AUDIT_END_DATE is null)))
153 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
154 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
155 ) then
156 null;
157 else
158 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159 app_exception.raise_exception;
160 end if;
161
162 for tlinfo in c1 loop
163 if (tlinfo.BASELANG = 'Y') then
164 if ( ((tlinfo.SETUP_GROUP_NAME = X_SETUP_GROUP_NAME)
165 OR ((tlinfo.SETUP_GROUP_NAME is null) AND (X_SETUP_GROUP_NAME is null)))
166 ) then
167 null;
168 else
169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
170 app_exception.raise_exception;
171 end if;
172 end if;
173 end loop;
174 return;
175 end LOCK_ROW;
176
177 procedure UPDATE_ROW (
178 X_SETUP_GROUP_CODE in VARCHAR2,
179 X_TABLE_ID in NUMBER,
180 X_TABLE_APP_ID in NUMBER,
181 X_CONTEXT_PARAMETER_CODE in VARCHAR2,
182 X_CONTEXT_PARAMETER_CODE2 in VARCHAR2,
183 X_HIERARCHY_LEVEL in VARCHAR2,
184 X_AUDIT_START_DATE in DATE,
185 X_AUDIT_END_DATE in DATE,
186 X_OBJECT_VERSION_NUMBER in NUMBER,
187 X_SETUP_GROUP_NAME in VARCHAR2,
188 X_LAST_UPDATE_DATE in DATE,
189 X_LAST_UPDATED_BY in NUMBER,
190 X_LAST_UPDATE_LOGIN in NUMBER
191 ) is
192 begin
193
194 fnd_file.put_line(fnd_file.log,'SG_PKG.UR: ' || X_AUDIT_END_DATE);
195 update ITA_SETUP_GROUPS_B set
196 TABLE_ID = X_TABLE_ID,
197 TABLE_APP_ID = X_TABLE_APP_ID,
198 CONTEXT_PARAMETER_CODE = X_CONTEXT_PARAMETER_CODE,
199 CONTEXT_PARAMETER_CODE2 = X_CONTEXT_PARAMETER_CODE2,
200 HIERARCHY_LEVEL = X_HIERARCHY_LEVEL,
201 AUDIT_START_DATE = X_AUDIT_START_DATE,
202 AUDIT_END_DATE = X_AUDIT_END_DATE,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
204 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
207 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212
213 update ITA_SETUP_GROUPS_TL set
214 SETUP_GROUP_NAME = X_SETUP_GROUP_NAME,
215 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
216 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
218 SOURCE_LANG = userenv('LANG')
219 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
220 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225 end UPDATE_ROW;
226
227 procedure DELETE_ROW (
228 X_SETUP_GROUP_CODE in VARCHAR2
229 ) is
230 begin
231 delete from ITA_SETUP_GROUPS_TL
232 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE;
233
234 if (sql%notfound) then
235 raise no_data_found;
236 end if;
237
238 delete from ITA_SETUP_GROUPS_B
239 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE;
240
241 if (sql%notfound) then
242 raise no_data_found;
243 end if;
244 end DELETE_ROW;
245
246 procedure ADD_LANGUAGE
247 is
248 begin
249 delete from ITA_SETUP_GROUPS_TL T
250 where not exists
251 (select NULL
252 from ITA_SETUP_GROUPS_B B
253 where B.SETUP_GROUP_CODE = T.SETUP_GROUP_CODE
254 );
255
256 update ITA_SETUP_GROUPS_TL T set (
257 SETUP_GROUP_NAME
258 ) = (select
259 B.SETUP_GROUP_NAME
260 from ITA_SETUP_GROUPS_TL B
261 where B.SETUP_GROUP_CODE = T.SETUP_GROUP_CODE
262 and B.LANGUAGE = T.SOURCE_LANG)
263 where (
264 T.SETUP_GROUP_CODE,
265 T.LANGUAGE
266 ) in (select
267 SUBT.SETUP_GROUP_CODE,
268 SUBT.LANGUAGE
269 from ITA_SETUP_GROUPS_TL SUBB, ITA_SETUP_GROUPS_TL SUBT
270 where SUBB.SETUP_GROUP_CODE = SUBT.SETUP_GROUP_CODE
271 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
272 and (SUBB.SETUP_GROUP_NAME <> SUBT.SETUP_GROUP_NAME
273 or (SUBB.SETUP_GROUP_NAME is null and SUBT.SETUP_GROUP_NAME is not null)
274 or (SUBB.SETUP_GROUP_NAME is not null and SUBT.SETUP_GROUP_NAME is null)
275 ));
276
277 insert into ITA_SETUP_GROUPS_TL (
278 CREATED_BY,
279 CREATION_DATE,
280 LAST_UPDATED_BY,
281 LAST_UPDATE_DATE,
282 LAST_UPDATE_LOGIN,
283 OBJECT_VERSION_NUMBER,
284 SETUP_GROUP_CODE,
285 SETUP_GROUP_NAME,
286 LANGUAGE,
287 SOURCE_LANG
288 ) select /*+ ORDERED */
289 B.CREATED_BY,
290 B.CREATION_DATE,
291 B.LAST_UPDATED_BY,
292 B.LAST_UPDATE_DATE,
293 B.LAST_UPDATE_LOGIN,
294 B.OBJECT_VERSION_NUMBER,
295 B.SETUP_GROUP_CODE,
296 B.SETUP_GROUP_NAME,
297 L.LANGUAGE_CODE,
298 B.SOURCE_LANG
299 from ITA_SETUP_GROUPS_TL B, FND_LANGUAGES L
300 where L.INSTALLED_FLAG in ('I', 'B')
301 and B.LANGUAGE = userenv('LANG')
302 and not exists
303 (select NULL
304 from ITA_SETUP_GROUPS_TL T
305 where T.SETUP_GROUP_CODE = B.SETUP_GROUP_CODE
306 and T.LANGUAGE = L.LANGUAGE_CODE);
307 end ADD_LANGUAGE;
308
309 procedure LOAD_ROW (
310 X_SETUP_GROUP_CODE in VARCHAR2,
311 X_TABLE_NAME in VARCHAR2,
312 X_TABLE_APP_SHORT_NAME in VARCHAR2,
313 X_CONTEXT_PARAMETER_CODE in VARCHAR2,
314 X_CONTEXT_PARAMETER_CODE2 in VARCHAR2,
315 X_AUDIT_END_DATE in DATE,
316 X_HIERARCHY_LEVEL in VARCHAR2,
317 X_OBJECT_VERSION_NUMBER in NUMBER,
318 X_SETUP_GROUP_NAME in VARCHAR2,
319 X_LAST_UPDATE_DATE in VARCHAR2,
320 X_OWNER in VARCHAR2,
321 X_CUSTOM_MODE IN VARCHAR2) is
322
323 view_appid number;
324 view_table_id number;
325 row_id varchar2(64);
326 l_audit_start_date date;
327 l_audit_end_date date;
328 f_luby number; -- entity owner in file
329 f_ludate date; -- entity update date in file
330 db_luby number; -- entity owner in db
331 db_ludate date; -- entity update date in db
332 begin
333
334 fnd_file.put_line(fnd_file.log,'Setup Group: ' || X_SETUP_GROUP_CODE);
335 fnd_file.put_line(fnd_file.log,'Custom mode: ' || X_CUSTOM_MODE);
336
337 -- translate values to IDs
338 select APPLICATION_ID
339 into view_appid
340 from FND_APPLICATION
341 where APPLICATION_SHORT_NAME = X_TABLE_APP_SHORT_NAME;
342
343 -- special handling for gl_sets_of_books and ce_system_parameters_all for R12 - bug 4958045
344 if (X_TABLE_NAME in ('GL_SETS_OF_BOOKS','CE_SYSTEM_PARAMETERS_ALL')) then
345 select table_id
346 into view_table_id
347 from ita_setup_groups_b
348 where setup_group_code = X_SETUP_GROUP_CODE
349 and table_app_id = view_appid;
350 else
351 select table_id
352 into view_table_id
353 from fnd_tables
354 where APPLICATION_id = view_appid and
355 table_name = X_TABLE_NAME;
356 end if;
357
358 -- Translate owner to file_last_updated_by
359 f_luby := fnd_load_util.owner_id(X_OWNER);
360
361 -- Translate char last_update_date to date
362 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
363 begin
364 select LAST_UPDATED_BY, LAST_UPDATE_DATE,
365 audit_start_date, audit_end_date -- should not be updated
366 into db_luby, db_ludate,
367 l_audit_start_date, l_audit_end_date
368 from ITA_SETUP_GROUPS_B
369 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE;
370
371 -- Test for customization and version
372 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
373 db_ludate, x_CUSTOM_MODE)) then
374 -- Update existing row
375 -- Changes made for R12, to set audit end date for obsolete tables
376 fnd_file.put_line(fnd_file.log,'Update Row: ' || l_audit_end_date || ': ' || X_AUDIT_END_DATE);
377 ITA_SETUP_GROUPS_PKG.UPDATE_ROW(
378 X_SETUP_GROUP_CODE => X_SETUP_GROUP_CODE,
379 X_TABLE_ID => view_table_id,
380 X_TABLE_APP_ID => view_appid,
381 X_CONTEXT_PARAMETER_CODE => X_CONTEXT_PARAMETER_CODE,
382 X_CONTEXT_PARAMETER_CODE2 => X_CONTEXT_PARAMETER_CODE2,
383 X_HIERARCHY_LEVEL => X_HIERARCHY_LEVEL,
384 X_AUDIT_START_DATE => l_audit_start_date,
385 X_AUDIT_END_DATE => nvl(l_audit_end_date,X_AUDIT_END_DATE),
386 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
387 X_SETUP_GROUP_NAME => X_SETUP_GROUP_NAME,
388 X_LAST_UPDATE_DATE => f_ludate,
389 X_LAST_UPDATED_BY => f_luby,
390 X_LAST_UPDATE_LOGIN => 0);
391 end if;
392
393 exception
394 when no_data_found then
395 -- Record doesn't exist - insert in all cases
396 fnd_file.put_line(fnd_file.log,'Insert');
397 ITA_SETUP_GROUPS_PKG.INSERT_ROW(
398 x_rowid => row_id,
399 X_SETUP_GROUP_CODE => X_SETUP_GROUP_CODE,
400 X_TABLE_ID => view_table_id,
401 X_TABLE_APP_ID => view_appid,
402 X_CONTEXT_PARAMETER_CODE => X_CONTEXT_PARAMETER_CODE,
403 X_CONTEXT_PARAMETER_CODE2 => X_CONTEXT_PARAMETER_CODE2,
404 X_HIERARCHY_LEVEL => X_HIERARCHY_LEVEL,
405 X_AUDIT_START_DATE => null,
406 X_AUDIT_END_DATE => X_AUDIT_END_DATE,
407 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
408 X_SETUP_GROUP_NAME => X_SETUP_GROUP_NAME,
409 x_creation_date => f_ludate,
410 x_created_by => f_luby,
411 X_LAST_UPDATE_DATE => f_ludate,
412 X_LAST_UPDATED_BY => f_luby,
413 X_LAST_UPDATE_LOGIN => 0);
414 end;
415 end LOAD_ROW;
416
417
418 procedure TRANSLATE_ROW (
419 X_SETUP_GROUP_CODE in VARCHAR2,
420 X_TABLE_NAME in VARCHAR2,
421 X_TABLE_APP_SHORT_NAME in VARCHAR2,
422 X_CONTEXT_PARAMETER_CODE in VARCHAR2,
423 X_CONTEXT_PARAMETER_CODE2 in VARCHAR2,
424 X_AUDIT_END_DATE in DATE,
425 X_HIERARCHY_LEVEL in VARCHAR2,
426 X_OBJECT_VERSION_NUMBER in NUMBER,
427 X_SETUP_GROUP_NAME in VARCHAR2,
428 X_LAST_UPDATE_DATE in VARCHAR2,
429 X_OWNER in VARCHAR2,
430 X_CUSTOM_MODE IN VARCHAR2) is
431
432 view_appid number;
433 view_table_id number;
434 row_id varchar2(64);
435 f_luby number; -- entity owner in file
436 f_ludate date; -- entity update date in file
437 db_luby number; -- entity owner in db
438 db_ludate date; -- entity update date in db
439 begin
440
441 -- Translate owner to file_last_updated_by
442 f_luby := fnd_load_util.owner_id(X_OWNER);
443
444 -- Translate char last_update_date to date
445 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
446
447 begin
448 select LAST_UPDATED_BY, LAST_UPDATE_DATE
449 into db_luby, db_ludate
450 from ITA_SETUP_GROUPS_TL
451 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
452 and LANGUAGE = userenv('LANG');
453
454 -- Test for customization and version
455 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
456 db_ludate, x_CUSTOM_MODE)) then
457 -- Update translations for this language
458 update ITA_SETUP_GROUPS_TL set
459 SETUP_GROUP_NAME = decode(x_SETUP_GROUP_NAME,
460 fnd_load_util.null_value, null, -- Real null
461 null, x_SETUP_GROUP_NAME, -- No change
462 x_SETUP_GROUP_NAME),
463 LAST_UPDATE_DATE = f_ludate,
464 LAST_UPDATED_BY = f_luby,
465 LAST_UPDATE_LOGIN = 0,
466 SOURCE_LANG = userenv('LANG')
467 where SETUP_GROUP_CODE = X_SETUP_GROUP_CODE
468 and LANGUAGE = userenv('LANG');
469 end if;
470 exception
471 when no_data_found then
472 -- Do not insert missing translations, skip this row
473 null;
474 end;
475
476
477 end TRANSLATE_ROW ;
478
479
480 end ITA_SETUP_GROUPS_PKG;