1 PACKAGE BODY AP_TERMS_PKG AS
2 /* $Header: apsumvtb.pls 120.3.12000000.2 2007/03/26 18:28:20 gagrawal 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)
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)))
212 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
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 NAME = X_NAME,
298 DESCRIPTION = X_DESCRIPTION,
299 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
300 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
302 SOURCE_LANG = userenv('LANG')
303 where TERM_ID = X_TERM_ID;
304 --and userenv('LANG') in (LANGUAGE, SOURCE_LANG); --bug5579307
305
306 if (sql%notfound) then
307 raise no_data_found;
308 end if;
309 end UPDATE_ROW;
310
311 procedure DELETE_ROW (
312 X_TERM_ID in NUMBER
313 ) is
314 begin
315 delete from AP_TERMS_TL
316 where TERM_ID = X_TERM_ID;
317
318 if (sql%notfound) then
319 raise no_data_found;
320 end if;
321
322 end DELETE_ROW;
323
324 procedure ADD_LANGUAGE
325 is
326 begin
327 update AP_TERMS_TL T set (
328 NAME,
329 DESCRIPTION
330 ) = (select
331 B.NAME,
332 B.DESCRIPTION
333 from AP_TERMS_TL B
334 where B.TERM_ID = T.TERM_ID
335 and B.LANGUAGE = T.SOURCE_LANG)
336 where (
337 T.TERM_ID,
338 T.LANGUAGE
339 ) in (select
340 SUBT.TERM_ID,
341 SUBT.LANGUAGE
342 from AP_TERMS_TL SUBB, AP_TERMS_TL SUBT
343 where SUBB.TERM_ID = SUBT.TERM_ID
344 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
345 and (SUBB.NAME <> SUBT.NAME
346 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
347 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
348 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
349 ));
350
351 insert into AP_TERMS_TL (
352 TERM_ID,
353 LAST_UPDATE_DATE,
354 LAST_UPDATED_BY,
355 CREATION_DATE,
356 CREATED_BY,
357 LAST_UPDATE_LOGIN,
358 NAME,
359 ENABLED_FLAG,
360 DUE_CUTOFF_DAY,
361 DESCRIPTION,
362 TYPE,
363 START_DATE_ACTIVE,
364 END_DATE_ACTIVE,
365 RANK,
366 ATTRIBUTE_CATEGORY,
367 ATTRIBUTE1,
368 ATTRIBUTE2,
369 ATTRIBUTE3,
370 ATTRIBUTE4,
371 ATTRIBUTE5,
372 ATTRIBUTE6,
373 ATTRIBUTE7,
374 ATTRIBUTE8,
375 ATTRIBUTE9,
376 ATTRIBUTE10,
377 ATTRIBUTE11,
378 ATTRIBUTE12,
379 ATTRIBUTE13,
380 ATTRIBUTE14,
381 ATTRIBUTE15,
382 LANGUAGE,
383 SOURCE_LANG
384 ) select
385 B.TERM_ID,
386 B.LAST_UPDATE_DATE,
387 B.LAST_UPDATED_BY,
388 B.CREATION_DATE,
389 B.CREATED_BY,
390 B.LAST_UPDATE_LOGIN,
391 B.NAME,
392 B.ENABLED_FLAG,
393 B.DUE_CUTOFF_DAY,
394 B.DESCRIPTION,
395 B.TYPE,
396 B.START_DATE_ACTIVE,
397 B.END_DATE_ACTIVE,
398 B.RANK,
399 B.ATTRIBUTE_CATEGORY,
400 B.ATTRIBUTE1,
401 B.ATTRIBUTE2,
402 B.ATTRIBUTE3,
403 B.ATTRIBUTE4,
404 B.ATTRIBUTE5,
405 B.ATTRIBUTE6,
406 B.ATTRIBUTE7,
407 B.ATTRIBUTE8,
408 B.ATTRIBUTE9,
409 B.ATTRIBUTE10,
410 B.ATTRIBUTE11,
411 B.ATTRIBUTE12,
412 B.ATTRIBUTE13,
413 B.ATTRIBUTE14,
414 B.ATTRIBUTE15,
415 L.LANGUAGE_CODE,
416 B.SOURCE_LANG
417 from AP_TERMS_TL B, FND_LANGUAGES L
418 where L.INSTALLED_FLAG in ('I', 'B')
419 and B.LANGUAGE = userenv('LANG')
420 and not exists
421 (select NULL
422 from AP_TERMS_TL T
423 where T.TERM_ID = B.TERM_ID
424 and T.LANGUAGE = L.LANGUAGE_CODE);
425 end ADD_LANGUAGE;
426
427 end AP_TERMS_PKG;
[Home] [Help]