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