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