1 package body AHL_ITEM_GROUPS_PKG as
2 /* $Header: AHLLIGPB.pls 115.4 2003/08/29 17:42:49 cxcheng noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_ITEM_GROUP_ID in NUMBER,
7 X_TYPE_CODE in VARCHAR2,
8 X_STATUS_CODE in VARCHAR2,
9 X_SOURCE_ITEM_GROUP_ID in NUMBER,
10 X_NAME in VARCHAR2,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_ATTRIBUTE9 in VARCHAR2,
21 X_ATTRIBUTE10 in VARCHAR2,
22 X_ATTRIBUTE11 in VARCHAR2,
23 X_ATTRIBUTE12 in VARCHAR2,
24 X_ATTRIBUTE13 in VARCHAR2,
25 X_ATTRIBUTE14 in VARCHAR2,
26 X_ATTRIBUTE15 in VARCHAR2,
27 X_OBJECT_VERSION_NUMBER in NUMBER,
28 X_DESCRIPTION in VARCHAR2,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from AHL_ITEM_GROUPS_B
36 where ITEM_GROUP_ID = X_ITEM_GROUP_ID
37 ;
38 begin
39 insert into AHL_ITEM_GROUPS_B (
40 TYPE_CODE,
41 STATUS_CODE,
42 SOURCE_ITEM_GROUP_ID,
43 NAME,
44 ATTRIBUTE_CATEGORY,
45 ATTRIBUTE1,
46 ATTRIBUTE2,
47 ATTRIBUTE3,
48 ATTRIBUTE4,
49 ATTRIBUTE5,
50 ATTRIBUTE6,
51 ATTRIBUTE7,
52 ATTRIBUTE8,
53 ATTRIBUTE9,
54 ATTRIBUTE10,
55 ATTRIBUTE11,
56 ATTRIBUTE12,
57 ATTRIBUTE13,
58 ATTRIBUTE14,
59 ATTRIBUTE15,
60 ITEM_GROUP_ID,
61 OBJECT_VERSION_NUMBER,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_TYPE_CODE,
69 X_STATUS_CODE,
70 X_SOURCE_ITEM_GROUP_ID,
71 X_NAME,
72 X_ATTRIBUTE_CATEGORY,
73 X_ATTRIBUTE1,
74 X_ATTRIBUTE2,
75 X_ATTRIBUTE3,
76 X_ATTRIBUTE4,
77 X_ATTRIBUTE5,
78 X_ATTRIBUTE6,
79 X_ATTRIBUTE7,
80 X_ATTRIBUTE8,
81 X_ATTRIBUTE9,
82 X_ATTRIBUTE10,
83 X_ATTRIBUTE11,
84 X_ATTRIBUTE12,
85 X_ATTRIBUTE13,
86 X_ATTRIBUTE14,
87 X_ATTRIBUTE15,
88 X_ITEM_GROUP_ID,
89 X_OBJECT_VERSION_NUMBER,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN
95 );
96
97 insert into AHL_ITEM_GROUPS_TL (
98 ITEM_GROUP_ID,
99 LAST_UPDATE_DATE,
100 LAST_UPDATED_BY,
101 CREATION_DATE,
102 CREATED_BY,
103 LAST_UPDATE_LOGIN,
104 DESCRIPTION,
105 LANGUAGE,
106 SOURCE_LANG
107 ) select
108 X_ITEM_GROUP_ID,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 X_CREATION_DATE,
112 X_CREATED_BY,
113 X_LAST_UPDATE_LOGIN,
114 X_DESCRIPTION,
115 L.LANGUAGE_CODE,
116 userenv('LANG')
117 from FND_LANGUAGES L
118 where L.INSTALLED_FLAG in ('I', 'B')
119 and not exists
120 (select NULL
121 from AHL_ITEM_GROUPS_TL T
122 where T.ITEM_GROUP_ID = X_ITEM_GROUP_ID
123 and T.LANGUAGE = L.LANGUAGE_CODE);
124
125 open c;
126 fetch c into X_ROWID;
127 if (c%notfound) then
128 close c;
129 raise no_data_found;
130 end if;
131 close c;
132
133 end INSERT_ROW;
134
135 procedure LOCK_ROW (
136 X_ITEM_GROUP_ID in NUMBER,
137 X_TYPE_CODE in VARCHAR2,
138 X_STATUS_CODE in VARCHAR2,
139 X_SOURCE_ITEM_GROUP_ID in NUMBER,
140 X_NAME in VARCHAR2,
141 X_ATTRIBUTE_CATEGORY in VARCHAR2,
142 X_ATTRIBUTE1 in VARCHAR2,
143 X_ATTRIBUTE2 in VARCHAR2,
144 X_ATTRIBUTE3 in VARCHAR2,
145 X_ATTRIBUTE4 in VARCHAR2,
146 X_ATTRIBUTE5 in VARCHAR2,
147 X_ATTRIBUTE6 in VARCHAR2,
148 X_ATTRIBUTE7 in VARCHAR2,
149 X_ATTRIBUTE8 in VARCHAR2,
150 X_ATTRIBUTE9 in VARCHAR2,
151 X_ATTRIBUTE10 in VARCHAR2,
152 X_ATTRIBUTE11 in VARCHAR2,
153 X_ATTRIBUTE12 in VARCHAR2,
154 X_ATTRIBUTE13 in VARCHAR2,
155 X_ATTRIBUTE14 in VARCHAR2,
156 X_ATTRIBUTE15 in VARCHAR2,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_DESCRIPTION in VARCHAR2
159 ) is
160 cursor c is select
161 TYPE_CODE,
162 STATUS_CODE,
163 SOURCE_ITEM_GROUP_ID,
164 NAME,
165 ATTRIBUTE_CATEGORY,
166 ATTRIBUTE1,
167 ATTRIBUTE2,
168 ATTRIBUTE3,
169 ATTRIBUTE4,
170 ATTRIBUTE5,
171 ATTRIBUTE6,
172 ATTRIBUTE7,
173 ATTRIBUTE8,
174 ATTRIBUTE9,
175 ATTRIBUTE10,
176 ATTRIBUTE11,
177 ATTRIBUTE12,
178 ATTRIBUTE13,
179 ATTRIBUTE14,
180 ATTRIBUTE15,
181 OBJECT_VERSION_NUMBER
182 from AHL_ITEM_GROUPS_B
183 where ITEM_GROUP_ID = X_ITEM_GROUP_ID
184 for update of ITEM_GROUP_ID nowait;
185 recinfo c%rowtype;
186
187 cursor c1 is select
188 DESCRIPTION,
189 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
190 from AHL_ITEM_GROUPS_TL
191 where ITEM_GROUP_ID = X_ITEM_GROUP_ID
192 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
193 for update of ITEM_GROUP_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.TYPE_CODE = X_TYPE_CODE)
204 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
205 AND ((recinfo.SOURCE_ITEM_GROUP_ID = X_SOURCE_ITEM_GROUP_ID)
206 OR ((recinfo.SOURCE_ITEM_GROUP_ID is null) AND (X_SOURCE_ITEM_GROUP_ID is null)))
207 AND (recinfo.NAME = X_NAME)
208
209 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
210 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
211 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
212 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
213 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
214 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
215 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
216 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
217 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
218 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
219 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
220 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
221 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
222 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
223 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
224 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
225 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
226 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
227 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
228 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
229 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
230 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
231 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
232 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
233 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
234 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
235 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
236 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
237 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
238 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
239 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
240 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
241 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
242 ) then
243 null;
244 else
245 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
246 app_exception.raise_exception;
247 end if;
248
249 for tlinfo in c1 loop
250 if (tlinfo.BASELANG = 'Y') then
251 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
252 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
253 ) then
254 null;
255 else
256 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
257 app_exception.raise_exception;
258 end if;
259 end if;
260 end loop;
261 return;
262 end LOCK_ROW;
263
264 procedure UPDATE_ROW (
265 X_ITEM_GROUP_ID in NUMBER,
266 X_TYPE_CODE in VARCHAR2,
267 X_STATUS_CODE in VARCHAR2,
268 X_SOURCE_ITEM_GROUP_ID in NUMBER,
269 X_NAME in VARCHAR2,
270 X_ATTRIBUTE_CATEGORY in VARCHAR2,
271 X_ATTRIBUTE1 in VARCHAR2,
272 X_ATTRIBUTE2 in VARCHAR2,
273 X_ATTRIBUTE3 in VARCHAR2,
274 X_ATTRIBUTE4 in VARCHAR2,
275 X_ATTRIBUTE5 in VARCHAR2,
276 X_ATTRIBUTE6 in VARCHAR2,
277 X_ATTRIBUTE7 in VARCHAR2,
278 X_ATTRIBUTE8 in VARCHAR2,
279 X_ATTRIBUTE9 in VARCHAR2,
280 X_ATTRIBUTE10 in VARCHAR2,
281 X_ATTRIBUTE11 in VARCHAR2,
282 X_ATTRIBUTE12 in VARCHAR2,
283 X_ATTRIBUTE13 in VARCHAR2,
284 X_ATTRIBUTE14 in VARCHAR2,
285 X_ATTRIBUTE15 in VARCHAR2,
286 X_OBJECT_VERSION_NUMBER in NUMBER,
287 X_DESCRIPTION in VARCHAR2,
288 X_LAST_UPDATE_DATE in DATE,
289 X_LAST_UPDATED_BY in NUMBER,
290 X_LAST_UPDATE_LOGIN in NUMBER
291 ) is
292 begin
293 update AHL_ITEM_GROUPS_B set
294 TYPE_CODE = X_TYPE_CODE,
295 STATUS_CODE = X_STATUS_CODE,
296 SOURCE_ITEM_GROUP_ID = X_SOURCE_ITEM_GROUP_ID,
297 NAME = X_NAME,
298 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
299 ATTRIBUTE1 = X_ATTRIBUTE1,
300 ATTRIBUTE2 = X_ATTRIBUTE2,
301 ATTRIBUTE3 = X_ATTRIBUTE3,
302 ATTRIBUTE4 = X_ATTRIBUTE4,
303 ATTRIBUTE5 = X_ATTRIBUTE5,
304 ATTRIBUTE6 = X_ATTRIBUTE6,
305 ATTRIBUTE7 = X_ATTRIBUTE7,
306 ATTRIBUTE8 = X_ATTRIBUTE8,
307 ATTRIBUTE9 = X_ATTRIBUTE9,
308 ATTRIBUTE10 = X_ATTRIBUTE10,
309 ATTRIBUTE11 = X_ATTRIBUTE11,
310 ATTRIBUTE12 = X_ATTRIBUTE12,
311 ATTRIBUTE13 = X_ATTRIBUTE13,
312 ATTRIBUTE14 = X_ATTRIBUTE14,
313 ATTRIBUTE15 = X_ATTRIBUTE15,
314 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
315 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
316 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
317 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
318 where ITEM_GROUP_ID = X_ITEM_GROUP_ID;
319
320 if (sql%notfound) then
321 raise no_data_found;
322 end if;
323
324 update AHL_ITEM_GROUPS_TL set
325 DESCRIPTION = X_DESCRIPTION,
326 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
327 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
328 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
329 SOURCE_LANG = userenv('LANG')
330 where ITEM_GROUP_ID = X_ITEM_GROUP_ID
331 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
332
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336 end UPDATE_ROW;
337
338 procedure DELETE_ROW (
339 X_ITEM_GROUP_ID in NUMBER
340 ) is
341 begin
342 delete from AHL_ITEM_GROUPS_TL
343 where ITEM_GROUP_ID = X_ITEM_GROUP_ID;
344
345 if (sql%notfound) then
346 raise no_data_found;
347 end if;
348
349 delete from AHL_ITEM_GROUPS_B
350 where ITEM_GROUP_ID = X_ITEM_GROUP_ID;
351
352 if (sql%notfound) then
353 raise no_data_found;
354 end if;
355 end DELETE_ROW;
356
357 procedure ADD_LANGUAGE
358 is
359 begin
360 delete from AHL_ITEM_GROUPS_TL T
361 where not exists
362 (select NULL
363 from AHL_ITEM_GROUPS_B B
364 where B.ITEM_GROUP_ID = T.ITEM_GROUP_ID
365 );
366
367 update AHL_ITEM_GROUPS_TL T set (
368 DESCRIPTION
369 ) = (select
370 B.DESCRIPTION
371 from AHL_ITEM_GROUPS_TL B
372 where B.ITEM_GROUP_ID = T.ITEM_GROUP_ID
373 and B.LANGUAGE = T.SOURCE_LANG)
374 where (
375 T.ITEM_GROUP_ID,
376 T.LANGUAGE
377 ) in (select
378 SUBT.ITEM_GROUP_ID,
379 SUBT.LANGUAGE
380 from AHL_ITEM_GROUPS_TL SUBB, AHL_ITEM_GROUPS_TL SUBT
381 where SUBB.ITEM_GROUP_ID = SUBT.ITEM_GROUP_ID
382 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
383 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
384 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
385 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
386 ));
387
388 insert into AHL_ITEM_GROUPS_TL (
389 ITEM_GROUP_ID,
390 LAST_UPDATE_DATE,
391 LAST_UPDATED_BY,
392 CREATION_DATE,
393 CREATED_BY,
394 LAST_UPDATE_LOGIN,
395 DESCRIPTION,
396 LANGUAGE,
397 SOURCE_LANG
398 ) select
399 B.ITEM_GROUP_ID,
400 B.LAST_UPDATE_DATE,
401 B.LAST_UPDATED_BY,
402 B.CREATION_DATE,
403 B.CREATED_BY,
404 B.LAST_UPDATE_LOGIN,
405 B.DESCRIPTION,
406 L.LANGUAGE_CODE,
407 B.SOURCE_LANG
408 from AHL_ITEM_GROUPS_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 AHL_ITEM_GROUPS_TL T
414 where T.ITEM_GROUP_ID = B.ITEM_GROUP_ID
415 and T.LANGUAGE = L.LANGUAGE_CODE);
416 end ADD_LANGUAGE;
417
418 end AHL_ITEM_GROUPS_PKG;
[Home] [Help]