1 package body FUN_TRX_TYPES_PKG as
2 /* $Header: funtrxtypetbhb.pls 120.2 2003/06/10 19:53:48 yingli noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_TRX_TYPE_ID in NUMBER,
7 X_ATTRIBUTE3 in VARCHAR2,
8 X_ATTRIBUTE4 in VARCHAR2,
9 X_ATTRIBUTE5 in VARCHAR2,
10 X_ATTRIBUTE6 in VARCHAR2,
11 X_ATTRIBUTE7 in VARCHAR2,
12 X_ATTRIBUTE8 in VARCHAR2,
13 X_ATTRIBUTE9 in VARCHAR2,
14 X_ATTRIBUTE10 in VARCHAR2,
15 X_ATTRIBUTE11 in VARCHAR2,
16 X_ATTRIBUTE12 in VARCHAR2,
17 X_ATTRIBUTE13 in VARCHAR2,
18 X_ATTRIBUTE14 in VARCHAR2,
19 X_ATTRIBUTE15 in VARCHAR2,
20 X_ATTRIBUTE_CATEGORY in VARCHAR2,
21 X_TRX_TYPE_CODE in NUMBER,
22 X_MANUAL_APPROVE_FLAG in VARCHAR2,
23 X_ENABLED_FLAG in VARCHAR2,
24 X_ALLOW_INVOICING_FLAG in VARCHAR2,
25 X_VAT_TAXABLE_FLAG in VARCHAR2,
26 X_ALLOW_INTEREST_ACCRUAL_FLAG in VARCHAR2,
27 X_ATTRIBUTE1 in VARCHAR2,
28 X_ATTRIBUTE2 in VARCHAR2,
29 X_TRX_TYPE_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 FUN_TRX_TYPES_B
38 where TRX_TYPE_ID = X_TRX_TYPE_ID
39 ;
40 begin
41 insert into FUN_TRX_TYPES_B (
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 ATTRIBUTE_CATEGORY,
56 TRX_TYPE_ID,
57 TRX_TYPE_CODE,
58 MANUAL_APPROVE_FLAG,
59 ENABLED_FLAG,
60 ALLOW_INVOICING_FLAG,
61 VAT_TAXABLE_FLAG,
62 ALLOW_INTEREST_ACCRUAL_FLAG,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
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_ATTRIBUTE_CATEGORY,
85 X_TRX_TYPE_ID,
86 X_TRX_TYPE_CODE,
87 X_MANUAL_APPROVE_FLAG,
88 X_ENABLED_FLAG,
89 X_ALLOW_INVOICING_FLAG,
90 X_VAT_TAXABLE_FLAG,
91 X_ALLOW_INTEREST_ACCRUAL_FLAG,
92 X_ATTRIBUTE1,
93 X_ATTRIBUTE2,
94 X_CREATION_DATE,
95 X_CREATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATED_BY,
98 X_LAST_UPDATE_LOGIN
99 );
100
101 insert into FUN_TRX_TYPES_TL (
102 TRX_TYPE_ID,
103 TRX_TYPE_NAME,
104 DESCRIPTION,
105 CREATED_BY,
106 CREATION_DATE,
107 LAST_UPDATED_BY,
108 LAST_UPDATE_DATE,
109 LAST_UPDATE_LOGIN,
110 LANGUAGE,
111 SOURCE_LANG
112 ) select
113 X_TRX_TYPE_ID,
114 X_TRX_TYPE_NAME,
115 X_DESCRIPTION,
116 X_CREATED_BY,
117 X_CREATION_DATE,
118 X_LAST_UPDATED_BY,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATE_LOGIN,
121 L.LANGUAGE_CODE,
122 userenv('LANG')
123 from FND_LANGUAGES L
124 where L.INSTALLED_FLAG in ('I', 'B')
125 and not exists
126 (select NULL
127 from FUN_TRX_TYPES_TL T
128 where T.TRX_TYPE_ID = X_TRX_TYPE_ID
129 and T.LANGUAGE = L.LANGUAGE_CODE);
130
131 open c;
132 fetch c into X_ROWID;
133 if (c%notfound) then
134 close c;
135 raise no_data_found;
136 end if;
137 close c;
138
139 end INSERT_ROW;
140
141 procedure LOCK_ROW (
142 X_TRX_TYPE_ID in NUMBER,
143 X_ATTRIBUTE3 in VARCHAR2,
144 X_ATTRIBUTE4 in VARCHAR2,
145 X_ATTRIBUTE5 in VARCHAR2,
146 X_ATTRIBUTE6 in VARCHAR2,
147 X_ATTRIBUTE7 in VARCHAR2,
148 X_ATTRIBUTE8 in VARCHAR2,
149 X_ATTRIBUTE9 in VARCHAR2,
150 X_ATTRIBUTE10 in VARCHAR2,
151 X_ATTRIBUTE11 in VARCHAR2,
152 X_ATTRIBUTE12 in VARCHAR2,
153 X_ATTRIBUTE13 in VARCHAR2,
154 X_ATTRIBUTE14 in VARCHAR2,
155 X_ATTRIBUTE15 in VARCHAR2,
156 X_ATTRIBUTE_CATEGORY in VARCHAR2,
157 X_TRX_TYPE_CODE in NUMBER,
158 X_MANUAL_APPROVE_FLAG in VARCHAR2,
159 X_ENABLED_FLAG in VARCHAR2,
160 X_ALLOW_INVOICING_FLAG in VARCHAR2,
161 X_VAT_TAXABLE_FLAG in VARCHAR2,
162 X_ALLOW_INTEREST_ACCRUAL_FLAG in VARCHAR2,
163 X_ATTRIBUTE1 in VARCHAR2,
164 X_ATTRIBUTE2 in VARCHAR2,
165 X_TRX_TYPE_NAME in VARCHAR2,
166 X_DESCRIPTION in VARCHAR2
167 ) is
168 cursor c is select
169 ATTRIBUTE3,
170 ATTRIBUTE4,
171 ATTRIBUTE5,
172 ATTRIBUTE6,
173 ATTRIBUTE7,
174 ATTRIBUTE8,
175 ATTRIBUTE9,
176 ATTRIBUTE10,
177 ATTRIBUTE11,
178 ATTRIBUTE12,
179 ATTRIBUTE13,
180 ATTRIBUTE14,
181 ATTRIBUTE15,
182 ATTRIBUTE_CATEGORY,
183 TRX_TYPE_CODE,
184 MANUAL_APPROVE_FLAG,
185 ENABLED_FLAG,
186 ALLOW_INVOICING_FLAG,
187 VAT_TAXABLE_FLAG,
188 ALLOW_INTEREST_ACCRUAL_FLAG,
189 ATTRIBUTE1,
190 ATTRIBUTE2
191 from FUN_TRX_TYPES_B
192 where TRX_TYPE_ID = X_TRX_TYPE_ID
193 for update of TRX_TYPE_ID nowait;
194 recinfo c%rowtype;
195
196 cursor c1 is select
197 TRX_TYPE_NAME,
198 DESCRIPTION,
199 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
200 from FUN_TRX_TYPES_TL
201 where TRX_TYPE_ID = X_TRX_TYPE_ID
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
203 for update of TRX_TYPE_ID nowait;
204 begin
205 open c;
206 fetch c into recinfo;
207 if (c%notfound) then
208 close c;
209 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
210 app_exception.raise_exception;
211 end if;
212 close c;
213 if ( ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
214 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
215 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
216 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
217 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
218 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
219 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
220 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
221 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
222 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
223 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
224 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
225 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
226 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
227 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
228 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
229 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
230 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
231 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
232 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
233 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
234 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
235 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
236 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
237 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
238 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
239 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
240 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
241 AND (recinfo.TRX_TYPE_CODE = X_TRX_TYPE_CODE)
242 AND ((recinfo.MANUAL_APPROVE_FLAG = X_MANUAL_APPROVE_FLAG)
243 OR ((recinfo.MANUAL_APPROVE_FLAG is null) AND (X_MANUAL_APPROVE_FLAG is null)))
244 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
245 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
246 AND ((recinfo.ALLOW_INVOICING_FLAG = X_ALLOW_INVOICING_FLAG)
247 OR ((recinfo.ALLOW_INVOICING_FLAG is null) AND (X_ALLOW_INVOICING_FLAG is null)))
248 AND ((recinfo.VAT_TAXABLE_FLAG = X_VAT_TAXABLE_FLAG)
249 OR ((recinfo.VAT_TAXABLE_FLAG is null) AND (X_VAT_TAXABLE_FLAG is null)))
250 AND ((recinfo.ALLOW_INTEREST_ACCRUAL_FLAG = X_ALLOW_INTEREST_ACCRUAL_FLAG)
251 OR ((recinfo.ALLOW_INTEREST_ACCRUAL_FLAG is null) AND (X_ALLOW_INTEREST_ACCRUAL_FLAG is null)))
252 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
253 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
254 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
255 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
256 ) then
257 null;
258 else
259 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
260 app_exception.raise_exception;
261 end if;
262
263 for tlinfo in c1 loop
264 if (tlinfo.BASELANG = 'Y') then
265 if ( ((tlinfo.TRX_TYPE_NAME = X_TRX_TYPE_NAME)
266 OR ((tlinfo.TRX_TYPE_NAME is null) AND (X_TRX_TYPE_NAME is null)))
267 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
268 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
269 ) then
270 null;
271 else
272 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
273 app_exception.raise_exception;
274 end if;
275 end if;
276 end loop;
277 return;
278 end LOCK_ROW;
279
280 procedure UPDATE_ROW (
281 X_TRX_TYPE_ID in NUMBER,
282 X_ATTRIBUTE3 in VARCHAR2,
283 X_ATTRIBUTE4 in VARCHAR2,
284 X_ATTRIBUTE5 in VARCHAR2,
285 X_ATTRIBUTE6 in VARCHAR2,
286 X_ATTRIBUTE7 in VARCHAR2,
287 X_ATTRIBUTE8 in VARCHAR2,
288 X_ATTRIBUTE9 in VARCHAR2,
289 X_ATTRIBUTE10 in VARCHAR2,
293 X_ATTRIBUTE14 in VARCHAR2,
290 X_ATTRIBUTE11 in VARCHAR2,
291 X_ATTRIBUTE12 in VARCHAR2,
292 X_ATTRIBUTE13 in VARCHAR2,
294 X_ATTRIBUTE15 in VARCHAR2,
295 X_ATTRIBUTE_CATEGORY in VARCHAR2,
296 X_TRX_TYPE_CODE in NUMBER,
297 X_MANUAL_APPROVE_FLAG in VARCHAR2,
298 X_ENABLED_FLAG in VARCHAR2,
299 X_ALLOW_INVOICING_FLAG in VARCHAR2,
300 X_VAT_TAXABLE_FLAG in VARCHAR2,
301 X_ALLOW_INTEREST_ACCRUAL_FLAG in VARCHAR2,
302 X_ATTRIBUTE1 in VARCHAR2,
303 X_ATTRIBUTE2 in VARCHAR2,
304 X_TRX_TYPE_NAME in VARCHAR2,
305 X_DESCRIPTION in VARCHAR2,
306 X_LAST_UPDATE_DATE in DATE,
307 X_LAST_UPDATED_BY in NUMBER,
308 X_LAST_UPDATE_LOGIN in NUMBER
309 ) is
310 begin
311 update FUN_TRX_TYPES_B set
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 TRX_TYPE_CODE = X_TRX_TYPE_CODE,
327 MANUAL_APPROVE_FLAG = X_MANUAL_APPROVE_FLAG,
328 ENABLED_FLAG = X_ENABLED_FLAG,
329 ALLOW_INVOICING_FLAG = X_ALLOW_INVOICING_FLAG,
330 VAT_TAXABLE_FLAG = X_VAT_TAXABLE_FLAG,
331 ALLOW_INTEREST_ACCRUAL_FLAG = X_ALLOW_INTEREST_ACCRUAL_FLAG,
332 ATTRIBUTE1 = X_ATTRIBUTE1,
333 ATTRIBUTE2 = X_ATTRIBUTE2,
334 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
335 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
336 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
337 where TRX_TYPE_ID = X_TRX_TYPE_ID;
338
339 if (sql%notfound) then
340 raise no_data_found;
341 end if;
342
343 update FUN_TRX_TYPES_TL set
344 TRX_TYPE_NAME = X_TRX_TYPE_NAME,
345 DESCRIPTION = X_DESCRIPTION,
346 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
347 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
348 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
349 SOURCE_LANG = userenv('LANG')
350 where TRX_TYPE_ID = X_TRX_TYPE_ID
351 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
352
353 if (sql%notfound) then
354 raise no_data_found;
355 end if;
356 end UPDATE_ROW;
357
358 procedure DELETE_ROW (
359 X_TRX_TYPE_ID in NUMBER
360 ) is
361 begin
362 delete from FUN_TRX_TYPES_TL
363 where TRX_TYPE_ID = X_TRX_TYPE_ID;
364
365 if (sql%notfound) then
366 raise no_data_found;
367 end if;
368
369 delete from FUN_TRX_TYPES_B
370 where TRX_TYPE_ID = X_TRX_TYPE_ID;
371
372 if (sql%notfound) then
373 raise no_data_found;
374 end if;
375 end DELETE_ROW;
376
377 procedure ADD_LANGUAGE
378 is
379 begin
380 delete from FUN_TRX_TYPES_TL T
381 where not exists
382 (select NULL
383 from FUN_TRX_TYPES_B B
384 where B.TRX_TYPE_ID = T.TRX_TYPE_ID
385 );
386
387 update FUN_TRX_TYPES_TL T set (
388 TRX_TYPE_NAME,
389 DESCRIPTION
390 ) = (select
391 B.TRX_TYPE_NAME,
392 B.DESCRIPTION
393 from FUN_TRX_TYPES_TL B
394 where B.TRX_TYPE_ID = T.TRX_TYPE_ID
395 and B.LANGUAGE = T.SOURCE_LANG)
396 where (
397 T.TRX_TYPE_ID,
398 T.LANGUAGE
399 ) in (select
400 SUBT.TRX_TYPE_ID,
401 SUBT.LANGUAGE
402 from FUN_TRX_TYPES_TL SUBB, FUN_TRX_TYPES_TL SUBT
403 where SUBB.TRX_TYPE_ID = SUBT.TRX_TYPE_ID
404 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
405 and (SUBB.TRX_TYPE_NAME <> SUBT.TRX_TYPE_NAME
406 or (SUBB.TRX_TYPE_NAME is null and SUBT.TRX_TYPE_NAME is not null)
407 or (SUBB.TRX_TYPE_NAME is not null and SUBT.TRX_TYPE_NAME is null)
408 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
409 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
410 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
411 ));
412
413 insert into FUN_TRX_TYPES_TL (
414 TRX_TYPE_ID,
415 TRX_TYPE_NAME,
416 DESCRIPTION,
417 CREATED_BY,
418 CREATION_DATE,
419 LAST_UPDATED_BY,
420 LAST_UPDATE_DATE,
421 LAST_UPDATE_LOGIN,
422 LANGUAGE,
423 SOURCE_LANG
424 ) select
425 B.TRX_TYPE_ID,
426 B.TRX_TYPE_NAME,
427 B.DESCRIPTION,
428 B.CREATED_BY,
429 B.CREATION_DATE,
430 B.LAST_UPDATED_BY,
431 B.LAST_UPDATE_DATE,
432 B.LAST_UPDATE_LOGIN,
433 L.LANGUAGE_CODE,
434 B.SOURCE_LANG
435 from FUN_TRX_TYPES_TL B, FND_LANGUAGES L
436 where L.INSTALLED_FLAG in ('I', 'B')
437 and B.LANGUAGE = userenv('LANG')
438 and not exists
439 (select NULL
440 from FUN_TRX_TYPES_TL T
441 where T.TRX_TYPE_ID = B.TRX_TYPE_ID
442 and T.LANGUAGE = L.LANGUAGE_CODE);
443 end ADD_LANGUAGE;
444
445 end FUN_TRX_TYPES_PKG;
[Home] [Help]