[Home] [Help]
PACKAGE BODY: APPS.AML_BUSINESS_EVENT_TYPES_PKG
Source
1 PACKAGE BODY AML_BUSINESS_EVENT_TYPES_PKG AS
2 /* $Header: amltbetb.pls 115.4 2003/11/13 23:47:02 solin ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_BUSINESS_EVENT_TYPE_ID in NUMBER,
6 X_ACTION_ID in NUMBER,
7 X_ACTION_ITEM_ID in NUMBER,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_MEANING in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from AML_BUSINESS_EVENT_TYPES_B
19 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
20 ;
21 begin
22 insert into AML_BUSINESS_EVENT_TYPES_B (
23 BUSINESS_EVENT_TYPE_ID,
24 ACTION_ID,
25 ACTION_ITEM_ID,
26 ENABLED_FLAG,
27 OBJECT_VERSION_NUMBER,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_BUSINESS_EVENT_TYPE_ID,
35 X_ACTION_ID,
36 X_ACTION_ITEM_ID,
37 X_ENABLED_FLAG,
38 X_OBJECT_VERSION_NUMBER,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY,
43 X_LAST_UPDATE_LOGIN
44 );
45
46 insert into AML_BUSINESS_EVENT_TYPES_TL (
47 BUSINESS_EVENT_TYPE_ID,
48 MEANING,
49 DESCRIPTION,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_BUSINESS_EVENT_TYPE_ID,
59 X_MEANING,
60 X_DESCRIPTION,
61 X_CREATION_DATE,
62 X_CREATED_BY,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_LOGIN,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from AML_BUSINESS_EVENT_TYPES_TL T
73 where T.BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_BUSINESS_EVENT_TYPE_ID in NUMBER,
88 X_ACTION_ID in NUMBER,
89 X_ACTION_ITEM_ID in NUMBER,
90 X_ENABLED_FLAG in VARCHAR2,
91 X_OBJECT_VERSION_NUMBER in NUMBER,
92 X_MEANING in VARCHAR2,
93 X_DESCRIPTION in VARCHAR2
94 ) is
95 cursor c is select
96 ACTION_ID,
97 ACTION_ITEM_ID,
98 ENABLED_FLAG,
99 OBJECT_VERSION_NUMBER
100 from AML_BUSINESS_EVENT_TYPES_B
101 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
102 for update of BUSINESS_EVENT_TYPE_ID nowait;
103 recinfo c%rowtype;
104
105 cursor c1 is select
106 MEANING,
107 DESCRIPTION,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from AML_BUSINESS_EVENT_TYPES_TL
110 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of BUSINESS_EVENT_TYPE_ID nowait;
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( (recinfo.ACTION_ID = X_ACTION_ID)
123 AND (recinfo.ACTION_ITEM_ID = X_ACTION_ITEM_ID)
124 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
125 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132
133 for tlinfo in c1 loop
134 if (tlinfo.BASELANG = 'Y') then
135 if ( (tlinfo.MEANING = X_MEANING)
136 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
137 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
138 ) then
139 null;
140 else
141 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
142 app_exception.raise_exception;
143 end if;
144 end if;
145 end loop;
146 return;
147 end LOCK_ROW;
148
149 procedure UPDATE_ROW (
150 X_BUSINESS_EVENT_TYPE_ID in NUMBER,
151 X_ACTION_ID in NUMBER,
152 X_ACTION_ITEM_ID in NUMBER,
153 X_ENABLED_FLAG in VARCHAR2,
154 X_OBJECT_VERSION_NUMBER in NUMBER,
155 X_MEANING in VARCHAR2,
156 X_DESCRIPTION in VARCHAR2,
157 X_LAST_UPDATE_DATE in DATE,
158 X_LAST_UPDATED_BY in NUMBER,
159 X_LAST_UPDATE_LOGIN in NUMBER
160 ) is
161 begin
162 update AML_BUSINESS_EVENT_TYPES_B set
163 ACTION_ID = X_ACTION_ID,
164 ACTION_ITEM_ID = X_ACTION_ITEM_ID,
165 ENABLED_FLAG = X_ENABLED_FLAG,
166 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
167 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
168 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
170 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 update AML_BUSINESS_EVENT_TYPES_TL set
177 MEANING = X_MEANING,
178 DESCRIPTION = X_DESCRIPTION,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
182 SOURCE_LANG = userenv('LANG')
183 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
184 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end UPDATE_ROW;
190
191 procedure DELETE_ROW (
192 X_BUSINESS_EVENT_TYPE_ID in NUMBER
193 ) is
194 begin
195 delete from AML_BUSINESS_EVENT_TYPES_TL
196 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201
202 delete from AML_BUSINESS_EVENT_TYPES_B
203 where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
204
205 if (sql%notfound) then
206 raise no_data_found;
207 end if;
208 end DELETE_ROW;
209
210 procedure ADD_LANGUAGE
211 is
212 begin
213 delete from AML_BUSINESS_EVENT_TYPES_TL T
214 where not exists
215 (select NULL
216 from AML_BUSINESS_EVENT_TYPES_B B
217 where B.BUSINESS_EVENT_TYPE_ID = T.BUSINESS_EVENT_TYPE_ID
218 );
219
220 update AML_BUSINESS_EVENT_TYPES_TL T set (
221 MEANING,
222 DESCRIPTION
223 ) = (select
224 B.MEANING,
225 B.DESCRIPTION
226 from AML_BUSINESS_EVENT_TYPES_TL B
227 where B.BUSINESS_EVENT_TYPE_ID = T.BUSINESS_EVENT_TYPE_ID
228 and B.LANGUAGE = T.SOURCE_LANG)
229 where (
230 T.BUSINESS_EVENT_TYPE_ID,
231 T.LANGUAGE
232 ) in (select
233 SUBT.BUSINESS_EVENT_TYPE_ID,
234 SUBT.LANGUAGE
235 from AML_BUSINESS_EVENT_TYPES_TL SUBB, AML_BUSINESS_EVENT_TYPES_TL SUBT
236 where SUBB.BUSINESS_EVENT_TYPE_ID = SUBT.BUSINESS_EVENT_TYPE_ID
237 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
238 and (SUBB.MEANING <> SUBT.MEANING
239 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
240 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
241 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
242 ));
243
244 insert into AML_BUSINESS_EVENT_TYPES_TL (
245 BUSINESS_EVENT_TYPE_ID,
246 MEANING,
247 DESCRIPTION,
248 CREATION_DATE,
249 CREATED_BY,
250 LAST_UPDATE_DATE,
251 LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN,
253 LANGUAGE,
254 SOURCE_LANG
255 ) select /*+ ORDERED */
256 B.BUSINESS_EVENT_TYPE_ID,
257 B.MEANING,
258 B.DESCRIPTION,
259 B.CREATION_DATE,
260 B.CREATED_BY,
261 B.LAST_UPDATE_DATE,
262 B.LAST_UPDATED_BY,
263 B.LAST_UPDATE_LOGIN,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANG
266 from AML_BUSINESS_EVENT_TYPES_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from AML_BUSINESS_EVENT_TYPES_TL T
272 where T.BUSINESS_EVENT_TYPE_ID = B.BUSINESS_EVENT_TYPE_ID
273 and T.LANGUAGE = L.LANGUAGE_CODE);
274 end ADD_LANGUAGE;
275
276 PROCEDURE LOAD_ROW (
277 px_BUSINESS_EVENT_TYPE_ID IN OUT NOCOPY NUMBER,
278 p_ACTION_ID IN NUMBER,
279 p_ACTION_ITEM_ID IN NUMBER,
280 p_ENABLED_FLAG IN VARCHAR2,
281 p_MEANING IN VARCHAR2,
282 p_DESCRIPTION IN VARCHAR2,
283 p_OWNER IN VARCHAR2)
284 IS
285 l_user_id NUMBER := 0;
286 l_row_id VARCHAR2(100);
287
288 CURSOR c_get_last_updated (c_BUSINESS_EVENT_TYPE_ID NUMBER) IS
289 SELECT last_updated_by, OBJECT_VERSION_NUMBER
290 FROM AML_BUSINESS_EVENT_TYPES_B
291 WHERE BUSINESS_EVENT_TYPE_ID = c_BUSINESS_EVENT_TYPE_ID;
292 l_last_updated_by NUMBER;
293 l_object_version_number NUMBER;
294
295 BEGIN
296 OPEN c_get_last_updated (px_BUSINESS_EVENT_TYPE_ID);
297 FETCH c_get_last_updated INTO l_last_updated_by, l_object_version_number;
298 CLOSE c_get_last_updated;
299
300 IF nvl(l_last_updated_by, 1) = 1
301 THEN
302 if (p_OWNER = 'SEED') then
303 l_user_id := 1;
304 end if;
305
306 Update_Row(x_BUSINESS_EVENT_TYPE_ID => px_BUSINESS_EVENT_TYPE_ID,
307 x_ACTION_ID => p_ACTION_ID,
308 x_ACTION_ITEM_ID => p_ACTION_ITEM_ID,
309 x_ENABLED_FLAG => p_ENABLED_FLAG,
310 x_OBJECT_VERSION_NUMBER => l_object_version_number,
311 x_MEANING => p_MEANING,
312 x_DESCRIPTION => p_DESCRIPTION,
313 x_LAST_UPDATE_DATE => sysdate,
314 x_LAST_UPDATED_BY => l_user_id,
315 x_LAST_UPDATE_LOGIN => 0
316 );
317 END IF;
318
319 EXCEPTION
320 when no_data_found then
321
322 Insert_Row(x_ROWID => l_row_id,
323 x_BUSINESS_EVENT_TYPE_ID => px_BUSINESS_EVENT_TYPE_ID,
324 x_ACTION_ID => p_ACTION_ID,
325 x_ACTION_ITEM_ID => p_ACTION_ITEM_ID,
326 x_ENABLED_FLAG => p_ENABLED_FLAG,
327 x_OBJECT_VERSION_NUMBER => 1,
328 x_MEANING => p_MEANING,
329 x_DESCRIPTION => p_DESCRIPTION,
330 x_CREATION_DATE => sysdate,
331 x_CREATED_BY => 0,
332 x_LAST_UPDATE_DATE => sysdate,
333 x_LAST_UPDATED_BY => l_user_id,
334 x_LAST_UPDATE_LOGIN => 0
335 );
336 END LOAD_ROW;
337
338 PROCEDURE TRANSLATE_ROW (
339 p_BUSINESS_EVENT_TYPE_ID IN NUMBER,
340 p_MEANING IN VARCHAR2,
341 p_DESCRIPTION IN VARCHAR2,
342 p_OWNER IN VARCHAR2
343 ) IS
344 BEGIN
345 -- only UPDATE rows that have not been altered by user
346 UPDATE AML_BUSINESS_EVENT_TYPES_TL
347 SET
348 MEANING = NVL(p_MEANING, MEANING),
349 DESCRIPTION = NVL(p_DESCRIPTION, DESCRIPTION),
350 SOURCE_LANG = userenv('LANG'),
351 LAST_UPDATE_DATE = SYSDATE,
352 LAST_UPDATED_BY = decode(p_owner, 'SEED', 1, 0),
353 LAST_UPDATE_LOGIN = 0
354 WHERE BUSINESS_EVENT_TYPE_ID = p_BUSINESS_EVENT_TYPE_ID
355 AND userenv('LANG') IN (language, source_lang);
356 END TRANSLATE_ROW;
357 end AML_BUSINESS_EVENT_TYPES_PKG;