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