[Home] [Help]
PACKAGE BODY: APPS.AMS_DS_TCA_ENTITY_COLS_PKG
Source
1 package body AMS_DS_TCA_ENTITY_COLS_PKG as
2 /* $Header: amsltecb.pls 120.0 2005/05/31 13:59:07 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ENTITY_COLUMN_ID in NUMBER,
6 X_SOURCE_CATEGORY in VARCHAR2,
7 X_ENTITY_ID in NUMBER,
8 X_COLUMN_NAME in VARCHAR2,
9 X_DATATYPE in VARCHAR2,
10 X_FIELD_SIZE in VARCHAR2,
11 X_RECORD_STUCTURE in VARCHAR2,
12 X_ENABLED_FLAG in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from AMS_DS_TCA_ENTITY_COLS
21 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
22 ;
23 begin
24 insert into AMS_DS_TCA_ENTITY_COLS (
25 SOURCE_CATEGORY,
26 ENTITY_COLUMN_ID,
27 ENTITY_ID,
28 COLUMN_NAME,
29 DATATYPE,
30 FIELD_SIZE,
31 RECORD_STUCTURE,
32 ENABLED_FLAG,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_SOURCE_CATEGORY,
40 X_ENTITY_COLUMN_ID,
41 X_ENTITY_ID,
42 X_COLUMN_NAME,
43 X_DATATYPE,
44 X_FIELD_SIZE,
45 X_RECORD_STUCTURE,
46 X_ENABLED_FLAG,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into AMS_DS_TCA_ENTITY_COLS_TL (
55 LAST_UPDATED_BY,
56 ENTITY_COLUMN_ID,
57 LAST_UPDATE_DATE,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_LOGIN,
61 DESCRIPTION,
62 LANGUAGE,
63 SOURCE_LANG
64 ) select
65 X_LAST_UPDATED_BY,
66 X_ENTITY_COLUMN_ID,
67 X_LAST_UPDATE_DATE,
68 X_CREATION_DATE,
69 X_CREATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_DESCRIPTION,
72 L.LANGUAGE_CODE,
73 userenv('LANG')
74 from FND_LANGUAGES L
75 where L.INSTALLED_FLAG in ('I', 'B')
76 and not exists
77 (select NULL
78 from AMS_DS_TCA_ENTITY_COLS_TL T
79 where T.ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
80 and T.LANGUAGE = L.LANGUAGE_CODE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_ENTITY_COLUMN_ID in NUMBER,
94 X_SOURCE_CATEGORY in VARCHAR2,
95 X_ENTITY_ID in NUMBER,
96 X_COLUMN_NAME in VARCHAR2,
97 X_DATATYPE in VARCHAR2,
98 X_FIELD_SIZE in VARCHAR2,
99 X_RECORD_STUCTURE in VARCHAR2,
100 X_ENABLED_FLAG in VARCHAR2,
101 X_DESCRIPTION in VARCHAR2
102 ) is
103 cursor c is select
104 SOURCE_CATEGORY,
105 ENTITY_ID,
106 COLUMN_NAME,
107 DATATYPE,
108 FIELD_SIZE,
109 RECORD_STUCTURE,
110 ENABLED_FLAG
111 from AMS_DS_TCA_ENTITY_COLS
112 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
113 for update of ENTITY_COLUMN_ID nowait;
114 recinfo c%rowtype;
115
116 cursor c1 is select
117 DESCRIPTION,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from AMS_DS_TCA_ENTITY_COLS_TL
120 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of ENTITY_COLUMN_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( ((recinfo.SOURCE_CATEGORY = X_SOURCE_CATEGORY)
133 OR ((recinfo.SOURCE_CATEGORY is null) AND (X_SOURCE_CATEGORY is null)))
134 AND (recinfo.ENTITY_ID = X_ENTITY_ID)
135 AND (recinfo.COLUMN_NAME = X_COLUMN_NAME)
136 AND ((recinfo.DATATYPE = X_DATATYPE)
137 OR ((recinfo.DATATYPE is null) AND (X_DATATYPE is null)))
138 AND ((recinfo.FIELD_SIZE = X_FIELD_SIZE)
139 OR ((recinfo.FIELD_SIZE is null) AND (X_FIELD_SIZE is null)))
140 AND ((recinfo.RECORD_STUCTURE = X_RECORD_STUCTURE)
141 OR ((recinfo.RECORD_STUCTURE is null) AND (X_RECORD_STUCTURE is null)))
142 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
143 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
144 ) then
145 null;
146 else
147 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148 app_exception.raise_exception;
149 end if;
150
151 for tlinfo in c1 loop
152 if (tlinfo.BASELANG = 'Y') then
153 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
154 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION 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 end if;
162 end loop;
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 X_ENTITY_COLUMN_ID in NUMBER,
168 X_SOURCE_CATEGORY in VARCHAR2,
169 X_ENTITY_ID in NUMBER,
170 X_COLUMN_NAME in VARCHAR2,
171 X_DATATYPE in VARCHAR2,
172 X_FIELD_SIZE in VARCHAR2,
173 X_RECORD_STUCTURE in VARCHAR2,
174 X_ENABLED_FLAG in VARCHAR2,
175 X_DESCRIPTION in VARCHAR2,
176 X_LAST_UPDATE_DATE in DATE,
177 X_LAST_UPDATED_BY in NUMBER,
178 X_LAST_UPDATE_LOGIN in NUMBER
179 ) is
180 begin
181 update AMS_DS_TCA_ENTITY_COLS set
182 SOURCE_CATEGORY = X_SOURCE_CATEGORY,
183 ENTITY_ID = X_ENTITY_ID,
184 COLUMN_NAME = X_COLUMN_NAME,
185 DATATYPE = X_DATATYPE,
186 FIELD_SIZE = X_FIELD_SIZE,
187 RECORD_STUCTURE = X_RECORD_STUCTURE,
188 ENABLED_FLAG = X_ENABLED_FLAG,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
192 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197
198 update AMS_DS_TCA_ENTITY_COLS_TL set
199 DESCRIPTION = X_DESCRIPTION,
200 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
201 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
202 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
203 SOURCE_LANG = userenv('LANG')
204 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
205 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end UPDATE_ROW;
211
212 procedure DELETE_ROW (
213 X_ENTITY_COLUMN_ID in NUMBER
214 ) is
215 begin
216 delete from AMS_DS_TCA_ENTITY_COLS_TL
217 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID;
218
219 if (sql%notfound) then
220 raise no_data_found;
221 end if;
222
223 delete from AMS_DS_TCA_ENTITY_COLS
224 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229 end DELETE_ROW;
230
231
232 procedure LOAD_ROW (
233 X_ENTITY_COLUMN_ID in NUMBER,
234 X_SOURCE_CATEGORY in VARCHAR2,
235 X_ENTITY_ID in NUMBER,
236 X_COLUMN_NAME in VARCHAR2,
237 X_DATATYPE in VARCHAR2,
238 X_FIELD_SIZE in VARCHAR2,
239 X_RECORD_STUCTURE in VARCHAR2,
240 X_ENABLED_FLAG in VARCHAR2,
241 X_DESCRIPTION in VARCHAR2,
242 X_OWNER in VARCHAR2,
243 x_custom_mode IN VARCHAR2,
244 X_CREATION_DATE in DATE,
245 X_CREATED_BY in NUMBER,
246 X_LAST_UPDATE_DATE in DATE,
247 X_LAST_UPDATED_BY in NUMBER,
248 X_LAST_UPDATE_LOGIN in NUMBER
249 ) IS
250
251 l_user_id number := 0;
252 l_colmap_id number;
253 l_obj_verno number;
254 l_dummy_char varchar2(1);
255 l_row_id varchar2(100);
256 l_db_luby_id number;
257
258 cursor c_chk_col_exists is
259 select 'x'
260 from AMS_DS_TCA_ENTITY_COLS
261 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID;
262
263 cursor c_get_col_mapping_id is
264 select AMS_DS_TCA_ENTITY_COLS_s.nextval
265 from dual;
266
267 cursor c_get_luby is
268 select last_updated_by
269 from AMS_DS_TCA_ENTITY_COLS
270 where ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID;
271
272 BEGIN
273 if X_OWNER = 'SEED' then
274 l_user_id := 1;
275 elsif X_OWNER = 'ORACLE' then
276 l_user_id := 2;
277 elsif X_OWNER = 'SYSADMIN' then
278 l_user_id := 0;
279
280 end if;
281 open c_chk_col_exists;
282 fetch c_chk_col_exists into l_dummy_char;
283 if c_chk_col_exists%notfound
284 then
285 close c_chk_col_exists;
286 if X_ENTITY_COLUMN_ID is null
287 then
288 open c_get_col_mapping_id;
289 fetch c_get_col_mapping_id into l_colmap_id;
290 close c_get_col_mapping_id;
291 else
292 l_colmap_id := X_ENTITY_COLUMN_ID;
293 end if;
294 AMS_DS_TCA_ENTITY_COLS_PKG.INSERT_ROW (
295 X_ROWID => l_row_id,
296 X_ENTITY_COLUMN_ID => l_colmap_id,
297 X_SOURCE_CATEGORY => X_SOURCE_CATEGORY,
298 X_ENTITY_ID => X_ENTITY_ID,
299 X_COLUMN_NAME => X_COLUMN_NAME,
300 X_DATATYPE => X_DATATYPE,
301 X_FIELD_SIZE => X_FIELD_SIZE,
302 X_RECORD_STUCTURE => X_RECORD_STUCTURE,
303 X_ENABLED_FLAG => X_ENABLED_FLAG,
304 X_DESCRIPTION => X_DESCRIPTION,
305 X_CREATION_DATE => X_CREATION_DATE, -- sysdate,
306 X_CREATED_BY => l_user_id,
307 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE, -- sysdate,
308 X_LAST_UPDATED_BY => l_user_id,
309 X_LAST_UPDATE_LOGIN => 1);
310
311 else
312 close c_chk_col_exists;
313
314 OPEN c_get_luby;
315 FETCH c_get_luby INTO l_db_luby_id;
316 CLOSE c_get_luby;
317
318
319 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
320 then
321
322
323 l_colmap_id := X_ENTITY_COLUMN_ID ;
324 AMS_DS_TCA_ENTITY_COLS_PKG.UPDATE_ROW (
325 X_ENTITY_COLUMN_ID => X_ENTITY_COLUMN_ID,
326 X_SOURCE_CATEGORY => X_SOURCE_CATEGORY,
327 X_ENTITY_ID => X_ENTITY_ID,
328 X_COLUMN_NAME => X_COLUMN_NAME,
329 X_DATATYPE => X_DATATYPE,
330 X_FIELD_SIZE => X_FIELD_SIZE,
331 X_RECORD_STUCTURE => X_RECORD_STUCTURE,
332 X_ENABLED_FLAG => X_ENABLED_FLAG,
333 X_DESCRIPTION => X_DESCRIPTION,
334 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE, -- sysdate,
335 X_LAST_UPDATED_BY => l_user_id,
336 X_LAST_UPDATE_LOGIN => 1
337 );
338
339 end if;
340 end if;
341
342 end LOAD_ROW;
343
344 procedure ADD_LANGUAGE
345 is
346 begin
347 delete from AMS_DS_TCA_ENTITY_COLS_TL T
348 where not exists
349 (select NULL
350 from AMS_DS_TCA_ENTITY_COLS B
351 where B.ENTITY_COLUMN_ID= T.ENTITY_COLUMN_ID
352 );
353
354 update AMS_DS_TCA_ENTITY_COLS_TL T set (
355 DESCRIPTION
356 ) = (select
357 B.DESCRIPTION
358 from AMS_DS_TCA_ENTITY_COLS_TL B
359 where B.ENTITY_COLUMN_ID= T.ENTITY_COLUMN_ID
360 and B.LANGUAGE = T.SOURCE_LANG)
361 where (
362 T.ENTITY_COLUMN_ID,
363 T.LANGUAGE
364 ) in (select
365 SUBT.ENTITY_COLUMN_ID,
366 SUBT.LANGUAGE
367 from AMS_DS_TCA_ENTITY_COLS_TL SUBB, AMS_DS_TCA_ENTITY_COLS_TL SUBT
368 where SUBB.ENTITY_COLUMN_ID= SUBT.ENTITY_COLUMN_ID
369 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
370 and (
371 SUBB.DESCRIPTION <> SUBT.DESCRIPTION
372 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
373 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
374 ));
375
376 insert into AMS_DS_TCA_ENTITY_COLS_TL (
377 LAST_UPDATED_BY,
378 ENTITY_COLUMN_ID,
379 LAST_UPDATE_DATE,
380 CREATION_DATE,
381 CREATED_BY,
382 LAST_UPDATE_LOGIN,
383 DESCRIPTION,
384 LANGUAGE,
385 SOURCE_LANG
386 ) select
387 B.LAST_UPDATED_BY,
388 B.ENTITY_COLUMN_ID,
389 B.LAST_UPDATE_DATE,
390 B.CREATION_DATE,
391 B.CREATED_BY,
392 B.LAST_UPDATE_LOGIN,
393 B.DESCRIPTION,
394 L.LANGUAGE_CODE,
395 B.SOURCE_LANG
396 from FND_LANGUAGES L, AMS_DS_TCA_ENTITY_COLS_TL B
397 where L.INSTALLED_FLAG in ('I', 'B')
398 and B.LANGUAGE = userenv('LANG')
399 and not exists
400 (select NULL
401 from AMS_DS_TCA_ENTITY_COLS_TL T
402 where T.ENTITY_COLUMN_ID = B.ENTITY_COLUMN_ID
403 and T.LANGUAGE = L.LANGUAGE_CODE);
404
405 end ADD_LANGUAGE;
406
407 PROCEDURE TRANSLATE_ROW (
408 X_ENTITY_COLUMN_ID IN NUMBER,
409 X_DESCRIPTION IN VARCHAR2,
410 X_OWNER IN VARCHAR2,
411 x_custom_mode IN VARCHAR2
412 ) IS
413
414 cursor c_last_updated_by is
415 select last_updated_by
416 from AMS_DS_TCA_ENTITY_COLS_TL
417 where ENTITY_COLUMN_ID = x_ENTITY_COLUMN_ID
418 and USERENV('LANG') = LANGUAGE;
419
420 l_luby number; --last updated by
421
422 BEGIN
423 -- only UPDATE rows that have not been altered by user
424 open c_last_updated_by;
428 if (l_luby IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
425 fetch c_last_updated_by into l_luby;
426 close c_last_updated_by;
427
429 then
430
431 UPDATE AMS_DS_TCA_ENTITY_COLS_TL
432 SET
433 DESCRIPTION = NVL(X_DESCRIPTION, DESCRIPTION),
434 SOURCE_LANG = userenv('LANG'),
435 LAST_UPDATE_DATE = SYSDATE,
436 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
437 LAST_UPDATE_LOGIN = 0
438 WHERE ENTITY_COLUMN_ID = X_ENTITY_COLUMN_ID
439 AND userenv('LANG') IN (language, source_lang);
440
441 end if;
442 END TRANSLATE_ROW;
443
444 end AMS_DS_TCA_ENTITY_COLS_PKG;