1 package body CS_SR_TYPE_CATEGORIES_PKG as
2 /* $Header: cssrtctb.pls 120.0 2005/06/01 14:01:06 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SR_TYPE_CATEGORY_ID in NUMBER,
6 X_DISPLAY_ORDER in NUMBER,
7 X_IMAGE_FILE_NAME in VARCHAR2,
8 X_START_DATE in DATE,
9 X_END_DATE in DATE,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2,
27 X_NAME in VARCHAR2,
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 CS_SR_TYPE_CATEGORIES_B
36 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID
37 ;
38 begin
39 insert into CS_SR_TYPE_CATEGORIES_B (
40 SR_TYPE_CATEGORY_ID,
41 DISPLAY_ORDER,
42 IMAGE_FILE_NAME,
43 START_DATE,
44 END_DATE,
45 OBJECT_VERSION_NUMBER,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15,
61 ATTRIBUTE_CATEGORY,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_SR_TYPE_CATEGORY_ID,
69 X_DISPLAY_ORDER,
70 X_IMAGE_FILE_NAME,
71 X_START_DATE,
72 X_END_DATE,
73 X_OBJECT_VERSION_NUMBER,
74 X_ATTRIBUTE1,
75 X_ATTRIBUTE2,
76 X_ATTRIBUTE3,
77 X_ATTRIBUTE4,
78 X_ATTRIBUTE5,
79 X_ATTRIBUTE6,
80 X_ATTRIBUTE7,
81 X_ATTRIBUTE8,
82 X_ATTRIBUTE9,
83 X_ATTRIBUTE10,
84 X_ATTRIBUTE11,
85 X_ATTRIBUTE12,
86 X_ATTRIBUTE13,
87 X_ATTRIBUTE14,
88 X_ATTRIBUTE15,
89 X_ATTRIBUTE_CATEGORY,
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 CS_SR_TYPE_CATEGORIES_TL (
98 SR_TYPE_CATEGORY_ID,
99 NAME,
100 DESCRIPTION,
101 OBJECT_VERSION_NUMBER,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 LANGUAGE,
108 SOURCE_LANG
109 ) select
110 X_SR_TYPE_CATEGORY_ID,
111 X_NAME,
112 X_DESCRIPTION,
113 X_OBJECT_VERSION_NUMBER,
114 X_CREATED_BY,
115 X_CREATION_DATE,
116 X_LAST_UPDATE_DATE,
117 X_LAST_UPDATED_BY,
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 CS_SR_TYPE_CATEGORIES_TL T
126 where T.SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_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_SR_TYPE_CATEGORY_ID in NUMBER,
141 X_DISPLAY_ORDER in NUMBER,
142 X_IMAGE_FILE_NAME in VARCHAR2,
143 X_START_DATE in DATE,
144 X_END_DATE in DATE,
145 X_OBJECT_VERSION_NUMBER in NUMBER,
146 X_ATTRIBUTE1 in VARCHAR2,
147 X_ATTRIBUTE2 in VARCHAR2,
148 X_ATTRIBUTE3 in VARCHAR2,
149 X_ATTRIBUTE4 in VARCHAR2,
150 X_ATTRIBUTE5 in VARCHAR2,
151 X_ATTRIBUTE6 in VARCHAR2,
152 X_ATTRIBUTE7 in VARCHAR2,
153 X_ATTRIBUTE8 in VARCHAR2,
154 X_ATTRIBUTE9 in VARCHAR2,
155 X_ATTRIBUTE10 in VARCHAR2,
156 X_ATTRIBUTE11 in VARCHAR2,
157 X_ATTRIBUTE12 in VARCHAR2,
158 X_ATTRIBUTE13 in VARCHAR2,
159 X_ATTRIBUTE14 in VARCHAR2,
160 X_ATTRIBUTE15 in VARCHAR2,
161 X_ATTRIBUTE_CATEGORY in VARCHAR2,
162 X_NAME in VARCHAR2,
163 X_DESCRIPTION in VARCHAR2
164 ) is
165 cursor c is select
166 DISPLAY_ORDER,
167 IMAGE_FILE_NAME,
168 START_DATE,
169 END_DATE,
170 OBJECT_VERSION_NUMBER,
171 ATTRIBUTE1,
172 ATTRIBUTE2,
173 ATTRIBUTE3,
174 ATTRIBUTE4,
175 ATTRIBUTE5,
176 ATTRIBUTE6,
177 ATTRIBUTE7,
178 ATTRIBUTE8,
179 ATTRIBUTE9,
180 ATTRIBUTE10,
181 ATTRIBUTE11,
182 ATTRIBUTE12,
183 ATTRIBUTE13,
184 ATTRIBUTE14,
185 ATTRIBUTE15,
186 ATTRIBUTE_CATEGORY
187 from CS_SR_TYPE_CATEGORIES_B
188 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID
189 for update of SR_TYPE_CATEGORY_ID nowait;
190 recinfo c%rowtype;
191
192 cursor c1 is select
193 NAME,
194 DESCRIPTION,
195 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
196 from CS_SR_TYPE_CATEGORIES_TL
197 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID
198 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
199 for update of SR_TYPE_CATEGORY_ID nowait;
200 begin
201 open c;
202 fetch c into recinfo;
203 if (c%notfound) then
204 close c;
205 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 end if;
208 close c;
209 if ( ((recinfo.DISPLAY_ORDER = X_DISPLAY_ORDER)
210 OR ((recinfo.DISPLAY_ORDER is null) AND (X_DISPLAY_ORDER is null)))
211 AND ((recinfo.IMAGE_FILE_NAME = X_IMAGE_FILE_NAME)
212 OR ((recinfo.IMAGE_FILE_NAME is null) AND (X_IMAGE_FILE_NAME is null)))
213 AND ((recinfo.START_DATE = X_START_DATE)
214 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
215 AND ((recinfo.END_DATE = X_END_DATE)
216 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
217 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
218 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
219 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
220 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
221 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
222 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
223 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
224 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
225 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
226 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
227 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
228 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
229 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
230 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
231 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
232 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
233 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
234 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
235 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
236 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
237 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
238 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
239 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
240 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
241 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
242 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
243 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
244 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
245 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
246 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
247 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
248 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
249 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
250 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
251 ) then
252 null;
253 else
254 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
255 app_exception.raise_exception;
256 end if;
257
258 for tlinfo in c1 loop
259 if (tlinfo.BASELANG = 'Y') then
260 if ( (tlinfo.NAME = X_NAME)
261 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
262 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
263 ) then
264 null;
265 else
266 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
267 app_exception.raise_exception;
268 end if;
269 end if;
270 end loop;
271 return;
272 end LOCK_ROW;
273
274 procedure UPDATE_ROW (
275 X_SR_TYPE_CATEGORY_ID in NUMBER,
276 X_DISPLAY_ORDER in NUMBER,
277 X_IMAGE_FILE_NAME in VARCHAR2,
278 X_START_DATE in DATE,
279 X_END_DATE in DATE,
280 X_OBJECT_VERSION_NUMBER in NUMBER,
281 X_ATTRIBUTE1 in VARCHAR2,
282 X_ATTRIBUTE2 in VARCHAR2,
283 X_ATTRIBUTE3 in VARCHAR2,
284 X_ATTRIBUTE4 in VARCHAR2,
285 X_ATTRIBUTE5 in VARCHAR2,
286 X_ATTRIBUTE6 in VARCHAR2,
287 X_ATTRIBUTE7 in VARCHAR2,
288 X_ATTRIBUTE8 in VARCHAR2,
289 X_ATTRIBUTE9 in VARCHAR2,
290 X_ATTRIBUTE10 in VARCHAR2,
291 X_ATTRIBUTE11 in VARCHAR2,
292 X_ATTRIBUTE12 in VARCHAR2,
293 X_ATTRIBUTE13 in VARCHAR2,
294 X_ATTRIBUTE14 in VARCHAR2,
295 X_ATTRIBUTE15 in VARCHAR2,
296 X_ATTRIBUTE_CATEGORY in VARCHAR2,
297 X_NAME in VARCHAR2,
298 X_DESCRIPTION in VARCHAR2,
299 X_LAST_UPDATE_DATE in DATE,
300 X_LAST_UPDATED_BY in NUMBER,
301 X_LAST_UPDATE_LOGIN in NUMBER
302 ) is
303 begin
304 update CS_SR_TYPE_CATEGORIES_B set
305 DISPLAY_ORDER = X_DISPLAY_ORDER,
306 IMAGE_FILE_NAME = X_IMAGE_FILE_NAME,
307 START_DATE = X_START_DATE,
308 END_DATE = X_END_DATE,
309 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
310 ATTRIBUTE1 = X_ATTRIBUTE1,
311 ATTRIBUTE2 = X_ATTRIBUTE2,
312 ATTRIBUTE3 = X_ATTRIBUTE3,
313 ATTRIBUTE4 = X_ATTRIBUTE4,
314 ATTRIBUTE5 = X_ATTRIBUTE5,
315 ATTRIBUTE6 = X_ATTRIBUTE6,
316 ATTRIBUTE7 = X_ATTRIBUTE7,
317 ATTRIBUTE8 = X_ATTRIBUTE8,
318 ATTRIBUTE9 = X_ATTRIBUTE9,
319 ATTRIBUTE10 = X_ATTRIBUTE10,
320 ATTRIBUTE11 = X_ATTRIBUTE11,
321 ATTRIBUTE12 = X_ATTRIBUTE12,
322 ATTRIBUTE13 = X_ATTRIBUTE13,
323 ATTRIBUTE14 = X_ATTRIBUTE14,
324 ATTRIBUTE15 = X_ATTRIBUTE15,
325 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
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 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID;
330
331 if (sql%notfound) then
332 raise no_data_found;
333 end if;
334
335 update CS_SR_TYPE_CATEGORIES_TL set
336 NAME = X_NAME,
337 DESCRIPTION = X_DESCRIPTION,
338 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
339 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
340 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
341 SOURCE_LANG = userenv('LANG')
342 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID
343 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
344
345 if (sql%notfound) then
346 raise no_data_found;
347 end if;
348 end UPDATE_ROW;
349
350 procedure DELETE_ROW (
351 X_SR_TYPE_CATEGORY_ID in NUMBER
352 ) is
353 begin
354 delete from CS_SR_TYPE_CATEGORIES_TL
355 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID;
356
357 if (sql%notfound) then
358 raise no_data_found;
359 end if;
360
361 delete from CS_SR_TYPE_CATEGORIES_B
362 where SR_TYPE_CATEGORY_ID = X_SR_TYPE_CATEGORY_ID;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367 end DELETE_ROW;
368
369 procedure ADD_LANGUAGE
370 is
371 begin
372 delete from CS_SR_TYPE_CATEGORIES_TL T
373 where not exists
374 (select NULL
375 from CS_SR_TYPE_CATEGORIES_B B
376 where B.SR_TYPE_CATEGORY_ID = T.SR_TYPE_CATEGORY_ID
377 );
378
379 update CS_SR_TYPE_CATEGORIES_TL T set (
380 NAME,
381 DESCRIPTION
382 ) = (select
383 B.NAME,
384 B.DESCRIPTION
385 from CS_SR_TYPE_CATEGORIES_TL B
386 where B.SR_TYPE_CATEGORY_ID = T.SR_TYPE_CATEGORY_ID
387 and B.LANGUAGE = T.SOURCE_LANG)
388 where (
389 T.SR_TYPE_CATEGORY_ID,
393 SUBT.LANGUAGE
390 T.LANGUAGE
391 ) in (select
392 SUBT.SR_TYPE_CATEGORY_ID,
394 from CS_SR_TYPE_CATEGORIES_TL SUBB, CS_SR_TYPE_CATEGORIES_TL SUBT
395 where SUBB.SR_TYPE_CATEGORY_ID = SUBT.SR_TYPE_CATEGORY_ID
396 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
397 and (SUBB.NAME <> SUBT.NAME
398 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
399 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
400 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
401 ));
402
403 insert into CS_SR_TYPE_CATEGORIES_TL (
404 SR_TYPE_CATEGORY_ID,
405 NAME,
406 DESCRIPTION,
407 OBJECT_VERSION_NUMBER,
408 CREATED_BY,
409 CREATION_DATE,
410 LAST_UPDATE_DATE,
411 LAST_UPDATED_BY,
412 LAST_UPDATE_LOGIN,
413 LANGUAGE,
414 SOURCE_LANG
415 ) select /*+ ORDERED */
416 B.SR_TYPE_CATEGORY_ID,
417 B.NAME,
418 B.DESCRIPTION,
419 B.OBJECT_VERSION_NUMBER,
420 B.CREATED_BY,
421 B.CREATION_DATE,
422 B.LAST_UPDATE_DATE,
423 B.LAST_UPDATED_BY,
424 B.LAST_UPDATE_LOGIN,
425 L.LANGUAGE_CODE,
426 B.SOURCE_LANG
427 from CS_SR_TYPE_CATEGORIES_TL B, FND_LANGUAGES L
428 where L.INSTALLED_FLAG in ('I', 'B')
429 and B.LANGUAGE = userenv('LANG')
430 and not exists
431 (select NULL
432 from CS_SR_TYPE_CATEGORIES_TL T
433 where T.SR_TYPE_CATEGORY_ID = B.SR_TYPE_CATEGORY_ID
434 and T.LANGUAGE = L.LANGUAGE_CODE);
435 end ADD_LANGUAGE;
436
437
438 procedure TRANSLATE_ROW (X_SR_TYPE_CATEGORY_ID in number,
439 X_NAME in varchar2,
440 X_DESCRIPTION in varchar2,
441 X_LAST_UPDATE_DATE in date,
442 X_OWNER in varchar2,
443 X_LAST_UPDATE_LOGIN in number)
444 is
445 l_user_id number;
446 begin
447
448 if (X_OWNER = 'SEED') then
449 l_user_id := 1;
450 else
451 l_user_id := 0;
452 end if;
453
454 update cs_sr_type_categories_tl set
455 name = nvl(x_name,name),
456 description = nvl(x_description,description),
457 last_update_date = nvl(x_last_update_date,sysdate),
458 last_updated_by = l_user_id,
459 last_update_login = 0,
460 source_lang = userenv('LANG')
461 where sr_type_category_id = x_sr_type_category_id
462 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
463
464 end TRANSLATE_ROW;
465
466
467
468 end CS_SR_TYPE_CATEGORIES_PKG;
[Home] [Help]