[Home] [Help]
PACKAGE BODY: APPS.AMV_D_ENT_ATTRIBUTES_PKG
Source
1 package body AMV_D_ENT_ATTRIBUTES_PKG as
2 /* $Header: amvtattb.pls 120.1 2005/06/21 17:47:22 appldev ship $ */
3 procedure LOAD_ROW (
4 X_ATTRIBUTE_ID in VARCHAR2,
5 X_OBJECT_VERSION_NUMBER in VARCHAR2,
6 X_ENTITY_ID in VARCHAR2,
7 X_DATA_TYPE in VARCHAR2,
8 X_STATUS in VARCHAR2,
9 X_COLUMN_NAME in VARCHAR2,
10 X_USAGE_INDICATOR in VARCHAR2,
11 X_VALIDATION_TYPE in VARCHAR2,
12 X_RANGE_LOW_VALUE in VARCHAR2,
13 X_RANGE_HIGH_VALUE in VARCHAR2,
14 X_FUNCTION_CALL in VARCHAR2,
15 X_ATTRIBUTE_NAME in VARCHAR2,
16 X_DESCRIPTION in VARCHAR2,
17 X_OWNER in VARCHAR2)
18 is
19 l_user_id number := 0;
20 l_attribute_id number := 0;
21 l_entity_id number := 0;
22 l_object_version_number number := 0;
23 l_row_id varchar2(64);
24 begin
25 if (X_OWNER = 'SEED') then
26 l_user_id := 1;
27 end if;
28 l_attribute_id := to_number(x_attribute_id);
29 l_object_version_number := to_number(x_object_version_number);
30 l_entity_id := to_number(x_entity_id);
31 --
32 AMV_D_ENT_ATTRIBUTES_PKG.UPDATE_ROW (
33 X_ATTRIBUTE_ID => l_attribute_id,
34 X_OBJECT_VERSION_NUMBER => l_object_version_number,
35 X_ENTITY_ID => l_entity_id,
36 X_DATA_TYPE => x_data_type,
37 X_STATUS => x_status,
38 X_COLUMN_NAME => x_column_name,
39 X_USAGE_INDICATOR => x_usage_indicator,
40 X_VALIDATION_TYPE => x_validation_type,
41 X_RANGE_HIGH_VALUE => x_range_high_value,
42 X_RANGE_LOW_VALUE => x_range_low_value,
43 X_FUNCTION_CALL => x_function_call,
44 X_ATTRIBUTE_NAME => x_attribute_name,
45 X_DESCRIPTION => x_description,
46 X_LAST_UPDATE_DATE => sysdate,
47 X_LAST_UPDATED_BY => l_user_id,
48 X_LAST_UPDATE_LOGIN => 0
49 );
50 exception
51 when NO_DATA_FOUND then
52 AMV_D_ENT_ATTRIBUTES_PKG.INSERT_ROW (
53 X_ROWID => l_row_id,
54 X_ATTRIBUTE_ID => l_attribute_id,
55 X_OBJECT_VERSION_NUMBER => l_object_version_number,
56 X_ENTITY_ID => l_entity_id,
57 X_DATA_TYPE => x_data_type,
58 X_STATUS => x_status,
59 X_COLUMN_NAME => x_column_name,
60 X_USAGE_INDICATOR => x_usage_indicator,
61 X_VALIDATION_TYPE => x_validation_type,
62 X_RANGE_HIGH_VALUE => x_range_high_value,
63 X_RANGE_LOW_VALUE => x_range_low_value,
64 X_FUNCTION_CALL => x_function_call,
65 X_ATTRIBUTE_NAME => x_attribute_name,
66 X_DESCRIPTION => x_description,
67 X_CREATION_DATE => sysdate,
68 X_CREATED_BY => l_user_id,
69 X_LAST_UPDATE_DATE => sysdate,
70 X_LAST_UPDATED_BY => l_user_id,
71 X_LAST_UPDATE_LOGIN => 0
72 );
73 end LOAD_ROW;
74
75 procedure TRANSLATE_ROW (
76 X_ATTRIBUTE_ID in NUMBER,
77 X_ATTRIBUTE_NAME in VARCHAR2,
78 X_DESCRIPTION in VARCHAR2,
79 X_OWNER in VARCHAR2)
80 is
81 begin
82 update AMV_D_ENT_ATTRIBUTES_TL set
83 ATTRIBUTE_NAME = x_attribute_name,
84 DESCRIPTION = x_description,
85 LAST_UPDATE_DATE = sysdate,
86 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
87 LAST_UPDATE_LOGIN = 0,
88 SOURCE_LANG = userenv('LANG')
89 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
90 and ATTRIBUTE_ID = x_attribute_id;
91 end TRANSLATE_ROW;
92
93 procedure INSERT_ROW (
94 X_ROWID in out NOCOPY VARCHAR2,
95 X_ATTRIBUTE_ID in NUMBER,
96 X_OBJECT_VERSION_NUMBER in NUMBER,
97 X_ENTITY_ID in NUMBER,
98 X_DATA_TYPE in VARCHAR2,
99 X_STATUS in VARCHAR2,
100 X_COLUMN_NAME in VARCHAR2,
101 X_USAGE_INDICATOR in VARCHAR2,
102 X_VALIDATION_TYPE in VARCHAR2,
103 X_RANGE_LOW_VALUE in VARCHAR2,
104 X_RANGE_HIGH_VALUE in VARCHAR2,
105 X_FUNCTION_CALL in VARCHAR2,
106 X_ATTRIBUTE_NAME in VARCHAR2,
107 X_DESCRIPTION in VARCHAR2,
108 X_CREATION_DATE in DATE,
109 X_CREATED_BY in NUMBER,
110 X_LAST_UPDATE_DATE in DATE,
111 X_LAST_UPDATED_BY in NUMBER,
112 X_LAST_UPDATE_LOGIN in NUMBER
113 ) is
114 cursor C is select ROWID from AMV_D_ENT_ATTRIBUTES_B
115 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
116 ;
117 begin
118 insert into AMV_D_ENT_ATTRIBUTES_B (
119 ATTRIBUTE_ID,
120 OBJECT_VERSION_NUMBER,
121 ENTITY_ID,
122 DATA_TYPE,
123 STATUS,
124 COLUMN_NAME,
125 USAGE_INDICATOR,
126 VALIDATION_TYPE,
127 RANGE_LOW_VALUE,
128 RANGE_HIGH_VALUE,
129 FUNCTION_CALL,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN
135 ) values (
136 X_ATTRIBUTE_ID,
137 X_OBJECT_VERSION_NUMBER,
138 X_ENTITY_ID,
139 X_DATA_TYPE,
140 X_STATUS,
141 X_COLUMN_NAME,
142 X_USAGE_INDICATOR,
143 X_VALIDATION_TYPE,
144 X_RANGE_LOW_VALUE,
145 X_RANGE_HIGH_VALUE,
146 X_FUNCTION_CALL,
147 X_CREATION_DATE,
148 X_CREATED_BY,
149 X_LAST_UPDATE_DATE,
150 X_LAST_UPDATED_BY,
151 X_LAST_UPDATE_LOGIN
152 );
153
154 insert into AMV_D_ENT_ATTRIBUTES_TL (
155 CREATION_DATE,
156 CREATED_BY,
157 LAST_UPDATE_LOGIN,
158 ATTRIBUTE_NAME,
159 DESCRIPTION,
160 ATTRIBUTE_ID,
161 LAST_UPDATE_DATE,
162 LAST_UPDATED_BY,
163 LANGUAGE,
164 SOURCE_LANG
165 ) select
166 X_CREATION_DATE,
167 X_CREATED_BY,
168 X_LAST_UPDATE_LOGIN,
169 X_ATTRIBUTE_NAME,
170 X_DESCRIPTION,
171 X_ATTRIBUTE_ID,
172 X_LAST_UPDATE_DATE,
173 X_LAST_UPDATED_BY,
174 L.LANGUAGE_CODE,
175 userenv('LANG')
176 from FND_LANGUAGES L
177 where L.INSTALLED_FLAG in ('I', 'B')
178 and not exists
179 (select NULL
180 from AMV_D_ENT_ATTRIBUTES_TL T
181 where T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
182 and T.LANGUAGE = L.LANGUAGE_CODE);
183
184 open c;
185 fetch c into X_ROWID;
186 if (c%notfound) then
187 close c;
188 raise no_data_found;
189 end if;
190 close c;
191
192 end INSERT_ROW;
193
194 procedure LOCK_ROW (
195 X_ATTRIBUTE_ID in NUMBER,
196 X_OBJECT_VERSION_NUMBER in NUMBER,
197 X_ENTITY_ID in NUMBER,
198 X_DATA_TYPE in VARCHAR2,
199 X_STATUS in VARCHAR2,
200 X_COLUMN_NAME in VARCHAR2,
201 X_USAGE_INDICATOR in VARCHAR2,
202 X_VALIDATION_TYPE in VARCHAR2,
203 X_RANGE_LOW_VALUE in VARCHAR2,
204 X_RANGE_HIGH_VALUE in VARCHAR2,
205 X_FUNCTION_CALL in VARCHAR2,
206 X_ATTRIBUTE_NAME in VARCHAR2,
207 X_DESCRIPTION in VARCHAR2
208 ) is
209 cursor c is select
210 OBJECT_VERSION_NUMBER,
211 ENTITY_ID,
212 DATA_TYPE,
213 STATUS,
214 COLUMN_NAME,
215 USAGE_INDICATOR,
216 VALIDATION_TYPE,
217 RANGE_LOW_VALUE,
218 RANGE_HIGH_VALUE,
219 FUNCTION_CALL
220 from AMV_D_ENT_ATTRIBUTES_B
221 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
222 for update of ATTRIBUTE_ID nowait;
223 recinfo c%rowtype;
224
225 cursor c1 is select
226 ATTRIBUTE_NAME,
227 DESCRIPTION,
228 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
229 from AMV_D_ENT_ATTRIBUTES_TL
230 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
231 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
232 for update of ATTRIBUTE_ID nowait;
233 begin
234 open c;
235 fetch c into recinfo;
236 if (c%notfound) then
237 close c;
238 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
239 app_exception.raise_exception;
240 end if;
241 close c;
242 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
243 AND (recinfo.ENTITY_ID = X_ENTITY_ID)
244 AND (recinfo.DATA_TYPE = X_DATA_TYPE)
245 AND (recinfo.STATUS = X_STATUS)
246 AND (recinfo.COLUMN_NAME = X_COLUMN_NAME)
247 AND (recinfo.USAGE_INDICATOR = X_USAGE_INDICATOR)
248 AND ((recinfo.VALIDATION_TYPE = X_VALIDATION_TYPE)
249 OR ((recinfo.VALIDATION_TYPE is null) AND (X_VALIDATION_TYPE is null)))
250 AND ((recinfo.RANGE_LOW_VALUE = X_RANGE_LOW_VALUE)
251 OR ((recinfo.RANGE_LOW_VALUE is null) AND (X_RANGE_LOW_VALUE is null)))
252 AND ((recinfo.RANGE_HIGH_VALUE = X_RANGE_HIGH_VALUE)
253 OR ((recinfo.RANGE_HIGH_VALUE is null) AND (X_RANGE_HIGH_VALUE is null)))
254 AND ((recinfo.FUNCTION_CALL = X_FUNCTION_CALL)
255 OR ((recinfo.FUNCTION_CALL is null) AND (X_FUNCTION_CALL is null)))
256 ) then
257 null;
258 else
259 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
260 app_exception.raise_exception;
261 end if;
262
263 for tlinfo in c1 loop
264 if (tlinfo.BASELANG = 'Y') then
265 if ( (tlinfo.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME)
266 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
267 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
268 ) then
269 null;
270 else
271 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272 app_exception.raise_exception;
273 end if;
274 end if;
275 end loop;
276 return;
277 end LOCK_ROW;
278
279 procedure UPDATE_ROW (
280 X_ATTRIBUTE_ID in NUMBER,
281 X_OBJECT_VERSION_NUMBER in NUMBER,
282 X_ENTITY_ID in NUMBER,
283 X_DATA_TYPE in VARCHAR2,
284 X_STATUS in VARCHAR2,
285 X_COLUMN_NAME in VARCHAR2,
286 X_USAGE_INDICATOR in VARCHAR2,
287 X_VALIDATION_TYPE in VARCHAR2,
288 X_RANGE_LOW_VALUE in VARCHAR2,
289 X_RANGE_HIGH_VALUE in VARCHAR2,
290 X_FUNCTION_CALL in VARCHAR2,
291 X_ATTRIBUTE_NAME in VARCHAR2,
292 X_DESCRIPTION in VARCHAR2,
293 X_LAST_UPDATE_DATE in DATE,
294 X_LAST_UPDATED_BY in NUMBER,
295 X_LAST_UPDATE_LOGIN in NUMBER
296 ) is
297 begin
298 update AMV_D_ENT_ATTRIBUTES_B set
299 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
300 ENTITY_ID = X_ENTITY_ID,
301 DATA_TYPE = X_DATA_TYPE,
302 STATUS = X_STATUS,
303 COLUMN_NAME = X_COLUMN_NAME,
304 USAGE_INDICATOR = X_USAGE_INDICATOR,
305 VALIDATION_TYPE = X_VALIDATION_TYPE,
306 RANGE_LOW_VALUE = X_RANGE_LOW_VALUE,
307 RANGE_HIGH_VALUE = X_RANGE_HIGH_VALUE,
308 FUNCTION_CALL = X_FUNCTION_CALL,
309 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
310 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
312 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
313
314 if (sql%notfound) then
315 raise no_data_found;
316 end if;
317
318 update AMV_D_ENT_ATTRIBUTES_TL set
319 ATTRIBUTE_NAME = X_ATTRIBUTE_NAME,
320 DESCRIPTION = X_DESCRIPTION,
321 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
322 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
323 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
324 SOURCE_LANG = userenv('LANG')
325 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
326 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
327
328 if (sql%notfound) then
329 raise no_data_found;
330 end if;
331 end UPDATE_ROW;
332
333 procedure DELETE_ROW (
334 X_ATTRIBUTE_ID in NUMBER
335 ) is
336 begin
337 delete from AMV_D_ENT_ATTRIBUTES_TL
338 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
339
340 if (sql%notfound) then
341 raise no_data_found;
342 end if;
343
344 delete from AMV_D_ENT_ATTRIBUTES_B
345 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
346
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350 end DELETE_ROW;
351
352 procedure ADD_LANGUAGE
353 is
354 begin
355 delete from AMV_D_ENT_ATTRIBUTES_TL T
356 where not exists
357 (select NULL
358 from AMV_D_ENT_ATTRIBUTES_B B
359 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
360 );
361
362 update AMV_D_ENT_ATTRIBUTES_TL T set (
363 ATTRIBUTE_NAME,
364 DESCRIPTION
365 ) = (select
366 B.ATTRIBUTE_NAME,
367 B.DESCRIPTION
368 from AMV_D_ENT_ATTRIBUTES_TL B
369 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
370 and B.LANGUAGE = T.SOURCE_LANG)
371 where (
372 T.ATTRIBUTE_ID,
373 T.LANGUAGE
374 ) in (select
375 SUBT.ATTRIBUTE_ID,
376 SUBT.LANGUAGE
377 from AMV_D_ENT_ATTRIBUTES_TL SUBB, AMV_D_ENT_ATTRIBUTES_TL SUBT
378 where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
379 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
380 and (SUBB.ATTRIBUTE_NAME <> SUBT.ATTRIBUTE_NAME
381 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
382 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
383 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
384 ));
385
386 insert into AMV_D_ENT_ATTRIBUTES_TL (
387 CREATION_DATE,
388 CREATED_BY,
389 LAST_UPDATE_LOGIN,
390 ATTRIBUTE_NAME,
391 DESCRIPTION,
392 ATTRIBUTE_ID,
393 LAST_UPDATE_DATE,
394 LAST_UPDATED_BY,
395 LANGUAGE,
396 SOURCE_LANG
397 ) select
398 B.CREATION_DATE,
399 B.CREATED_BY,
400 B.LAST_UPDATE_LOGIN,
401 B.ATTRIBUTE_NAME,
402 B.DESCRIPTION,
403 B.ATTRIBUTE_ID,
404 B.LAST_UPDATE_DATE,
405 B.LAST_UPDATED_BY,
406 L.LANGUAGE_CODE,
407 B.SOURCE_LANG
408 from AMV_D_ENT_ATTRIBUTES_TL B, FND_LANGUAGES L
409 where L.INSTALLED_FLAG in ('I', 'B')
410 and B.LANGUAGE = userenv('LANG')
411 and not exists
412 (select NULL
413 from AMV_D_ENT_ATTRIBUTES_TL T
414 where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
415 and T.LANGUAGE = L.LANGUAGE_CODE);
416 end ADD_LANGUAGE;
417
418 end AMV_D_ENT_ATTRIBUTES_PKG;