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