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