1 package body ZX_STATUS_PKG as
2 /* $Header: zxcstatusb.pls 120.5 2005/03/17 12:18:32 shmangal ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_TAX_STATUS_ID in NUMBER,
7 X_TAX_STATUS_CODE in VARCHAR2,
8 X_EFFECTIVE_FROM in DATE,
9 X_EFFECTIVE_TO in DATE,
10 X_TAX in VARCHAR2,
11 X_TAX_REGIME_CODE in VARCHAR2,
12 X_Rule_Based_Rate_Flag in VARCHAR2,
13 X_Allow_Rate_Override_Flag in VARCHAR2,
14 X_Allow_Exemptions_Flag in VARCHAR2,
15 X_Allow_Exceptions_Flag in VARCHAR2,
16 X_Default_Status_Flag in VARCHAR2,
17 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
18 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
19 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
20 X_Record_Type_Code in VARCHAR2,
21 X_REQUEST_ID in NUMBER,
22 X_ATTRIBUTE1 in VARCHAR2,
23 X_ATTRIBUTE2 in VARCHAR2,
24 X_ATTRIBUTE3 in VARCHAR2,
25 X_ATTRIBUTE4 in VARCHAR2,
26 X_ATTRIBUTE5 in VARCHAR2,
27 X_ATTRIBUTE6 in VARCHAR2,
28 X_ATTRIBUTE7 in VARCHAR2,
29 X_ATTRIBUTE8 in VARCHAR2,
30 X_ATTRIBUTE9 in VARCHAR2,
31 X_ATTRIBUTE10 in VARCHAR2,
32 X_ATTRIBUTE11 in VARCHAR2,
33 X_ATTRIBUTE12 in VARCHAR2,
34 X_ATTRIBUTE13 in VARCHAR2,
35 X_ATTRIBUTE14 in VARCHAR2,
36 X_ATTRIBUTE15 in VARCHAR2,
37 X_ATTRIBUTE_CATEGORY in VARCHAR2,
38 X_TAX_STATUS_NAME in VARCHAR2,
39 X_CREATION_DATE in DATE,
40 X_CREATED_BY in NUMBER,
41 X_LAST_UPDATE_DATE in DATE,
42 X_LAST_UPDATED_BY in NUMBER,
43 X_LAST_UPDATE_LOGIN in NUMBER,
44 X_PROGRAM_APPLICATION_ID in NUMBER,
45 X_PROGRAM_ID in NUMBER,
46 X_PROGRAM_LOGIN_ID in NUMBER,
47 X_CONTENT_OWNER_ID in NUMBER,
48 X_OBJECT_VERSION_NUMBER in NUMBER
49 ) is
50 cursor C is select ROWID from ZX_STATUS_B
51 where TAX_STATUS_ID = X_TAX_STATUS_ID
52 ;
53 begin
54 insert into ZX_STATUS_B (
55 TAX_STATUS_ID,
56 TAX_STATUS_CODE,
57 EFFECTIVE_FROM,
58 EFFECTIVE_TO,
59 TAX,
60 TAX_REGIME_CODE,
61 Rule_Based_Rate_Flag,
62 Allow_Rate_Override_Flag,
63 Allow_Exemptions_Flag,
64 Allow_Exceptions_Flag,
65 Default_Status_Flag,
66 DEFAULT_FLG_EFFECTIVE_FROM,
67 DEFAULT_FLG_EFFECTIVE_TO,
68 Def_Rec_Settlement_Option_Code,
69 Record_Type_Code,
70 REQUEST_ID,
71 ATTRIBUTE1,
72 ATTRIBUTE2,
73 ATTRIBUTE3,
74 ATTRIBUTE4,
75 ATTRIBUTE5,
76 ATTRIBUTE6,
77 ATTRIBUTE7,
78 ATTRIBUTE8,
79 ATTRIBUTE9,
80 ATTRIBUTE10,
81 ATTRIBUTE11,
82 ATTRIBUTE12,
83 ATTRIBUTE13,
84 ATTRIBUTE14,
85 ATTRIBUTE15,
86 ATTRIBUTE_CATEGORY,
87 CREATION_DATE,
88 CREATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN,
92 PROGRAM_APPLICATION_ID,
93 PROGRAM_ID,
94 PROGRAM_LOGIN_ID,
95 CONTENT_OWNER_ID,
96 OBJECT_VERSION_NUMBER
97 ) values (
98 X_TAX_STATUS_ID,
99 X_TAX_STATUS_CODE,
100 X_EFFECTIVE_FROM,
101 X_EFFECTIVE_TO,
102 X_TAX,
103 X_TAX_REGIME_CODE,
104 X_Rule_Based_Rate_Flag,
105 X_Allow_Rate_Override_Flag,
106 X_Allow_Exemptions_Flag,
107 X_Allow_Exceptions_Flag,
108 X_Default_Status_Flag,
109 X_DEFAULT_FLG_EFFECTIVE_FROM,
110 X_DEFAULT_FLG_EFFECTIVE_TO,
111 X_Def_Rec_Settlement_Option_Co,
112 X_Record_Type_Code,
113 X_REQUEST_ID,
114 X_ATTRIBUTE1,
115 X_ATTRIBUTE2,
116 X_ATTRIBUTE3,
117 X_ATTRIBUTE4,
118 X_ATTRIBUTE5,
119 X_ATTRIBUTE6,
120 X_ATTRIBUTE7,
121 X_ATTRIBUTE8,
122 X_ATTRIBUTE9,
123 X_ATTRIBUTE10,
124 X_ATTRIBUTE11,
125 X_ATTRIBUTE12,
126 X_ATTRIBUTE13,
127 X_ATTRIBUTE14,
128 X_ATTRIBUTE15,
129 X_ATTRIBUTE_CATEGORY,
130 X_CREATION_DATE,
131 X_CREATED_BY,
132 X_LAST_UPDATE_DATE,
133 X_LAST_UPDATED_BY,
134 X_LAST_UPDATE_LOGIN,
135 X_PROGRAM_APPLICATION_ID,
136 X_PROGRAM_ID,
137 X_PROGRAM_LOGIN_ID,
138 X_CONTENT_OWNER_ID,
139 X_OBJECT_VERSION_NUMBER
140 );
141 insert into ZX_STATUS_TL (
142 TAX_STATUS_ID,
143 TAX_STATUS_NAME,
144 CREATED_BY,
145 CREATION_DATE,
146 LAST_UPDATED_BY,
147 LAST_UPDATE_DATE,
148 LAST_UPDATE_LOGIN,
149 LANGUAGE,
150 SOURCE_LANG
151 ) select
152 X_TAX_STATUS_ID,
153 X_TAX_STATUS_NAME,
154 X_CREATED_BY,
155 X_CREATION_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_DATE,
158 X_LAST_UPDATE_LOGIN,
159 L.LANGUAGE_CODE,
160 userenv('LANG')
161 from FND_LANGUAGES L
162 where L.INSTALLED_FLAG in ('I', 'B')
163 and not exists
164 (select NULL
165 from ZX_STATUS_TL T
166 where T.TAX_STATUS_ID = X_TAX_STATUS_ID
167 and T.LANGUAGE = L.LANGUAGE_CODE);
168 open c;
169 fetch c into X_ROWID;
170 if (c%notfound) then
171 close c;
172 raise no_data_found;
173 end if;
174 close c;
175 end INSERT_ROW;
176
177 procedure LOCK_ROW (
178 X_TAX_STATUS_ID in NUMBER,
179 X_TAX_STATUS_CODE in VARCHAR2,
180 X_EFFECTIVE_FROM in DATE,
181 X_EFFECTIVE_TO in DATE,
182 X_TAX in VARCHAR2,
183 X_TAX_REGIME_CODE in VARCHAR2,
184 X_Rule_Based_Rate_Flag in VARCHAR2,
185 X_Allow_Rate_Override_Flag in VARCHAR2,
186 X_Allow_Exemptions_Flag in VARCHAR2,
187 X_Allow_Exceptions_Flag in VARCHAR2,
188 X_Default_Status_Flag in VARCHAR2,
189 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
190 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
191 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
192 X_Record_Type_Code in VARCHAR2,
193 X_REQUEST_ID in NUMBER,
194 X_ATTRIBUTE1 in VARCHAR2,
195 X_ATTRIBUTE2 in VARCHAR2,
196 X_ATTRIBUTE3 in VARCHAR2,
197 X_ATTRIBUTE4 in VARCHAR2,
198 X_ATTRIBUTE5 in VARCHAR2,
199 X_ATTRIBUTE6 in VARCHAR2,
200 X_ATTRIBUTE7 in VARCHAR2,
201 X_ATTRIBUTE8 in VARCHAR2,
202 X_ATTRIBUTE9 in VARCHAR2,
203 X_ATTRIBUTE10 in VARCHAR2,
204 X_ATTRIBUTE11 in VARCHAR2,
205 X_ATTRIBUTE12 in VARCHAR2,
206 X_ATTRIBUTE13 in VARCHAR2,
207 X_ATTRIBUTE14 in VARCHAR2,
208 X_ATTRIBUTE15 in VARCHAR2,
209 X_ATTRIBUTE_CATEGORY in VARCHAR2,
210 X_TAX_STATUS_NAME in VARCHAR2,
211 X_PROGRAM_APPLICATION_ID in NUMBER,
212 X_PROGRAM_ID in NUMBER,
213 X_PROGRAM_LOGIN_ID in NUMBER,
214 X_CONTENT_OWNER_ID in NUMBER,
215 X_OBJECT_VERSION_NUMBER in NUMBER
216 ) is
217 cursor c is select
218 TAX_STATUS_CODE,
219 EFFECTIVE_FROM,
220 EFFECTIVE_TO,
221 TAX,
222 TAX_REGIME_CODE,
223 Rule_Based_Rate_Flag,
224 Allow_Rate_Override_Flag,
225 Allow_Exemptions_Flag,
226 Allow_Exceptions_Flag,
227 Default_Status_Flag,
228 DEFAULT_FLG_EFFECTIVE_FROM,
229 DEFAULT_FLG_EFFECTIVE_TO,
230 Def_Rec_Settlement_Option_Code,
231 Record_Type_Code,
232 REQUEST_ID,
233 ATTRIBUTE1,
234 ATTRIBUTE2,
235 ATTRIBUTE3,
236 ATTRIBUTE4,
237 ATTRIBUTE5,
238 ATTRIBUTE6,
239 ATTRIBUTE7,
240 ATTRIBUTE8,
241 ATTRIBUTE9,
242 ATTRIBUTE10,
243 ATTRIBUTE11,
244 ATTRIBUTE12,
245 ATTRIBUTE13,
246 ATTRIBUTE14,
247 ATTRIBUTE15,
248 ATTRIBUTE_CATEGORY,
249 PROGRAM_APPLICATION_ID,
250 PROGRAM_ID,
251 PROGRAM_LOGIN_ID,
252 CONTENT_OWNER_ID,
253 OBJECT_VERSION_NUMBER
254 from ZX_STATUS_B
255 where TAX_STATUS_ID = X_TAX_STATUS_ID
256 for update of TAX_STATUS_ID nowait;
257 recinfo c%rowtype;
258 cursor c1 is select
259 TAX_STATUS_NAME,
260 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
261 from ZX_STATUS_TL
262 where TAX_STATUS_ID = X_TAX_STATUS_ID
263 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
264 for update of TAX_STATUS_ID nowait;
265 begin
266 open c;
267 fetch c into recinfo;
268 if (c%notfound) then
269 close c;
270 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
271 app_exception.raise_exception;
272 end if;
273 close c;
274 if ( (recinfo.TAX_STATUS_CODE = X_TAX_STATUS_CODE)
275 AND ((recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
276 OR ((recinfo.EFFECTIVE_FROM is null) AND (X_EFFECTIVE_FROM is null)))
277 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
278 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
279 AND (recinfo.TAX = X_TAX)
280 AND (recinfo.TAX_REGIME_CODE = X_TAX_REGIME_CODE)
281 AND ((recinfo.Rule_Based_Rate_Flag = X_Rule_Based_Rate_Flag)
282 OR ((recinfo.Rule_Based_Rate_Flag is null) AND (X_Rule_Based_Rate_Flag is null)))
283 AND ((recinfo.Allow_Rate_Override_Flag = X_Allow_Rate_Override_Flag)
284 OR ((recinfo.Allow_Rate_Override_Flag is null) AND (X_Allow_Rate_Override_Flag is null)))
285 AND ((recinfo.Allow_Exemptions_Flag = X_Allow_Exemptions_Flag)
286 OR ((recinfo.Allow_Exemptions_Flag is null) AND (X_Allow_Exemptions_Flag is null)))
287 AND ((recinfo.Allow_Exceptions_Flag = X_Allow_Exceptions_Flag)
288 OR ((recinfo.Allow_Exceptions_Flag is null) AND (X_Allow_Exceptions_Flag is null)))
289 AND ((recinfo.Default_Status_Flag = X_Default_Status_Flag)
290 OR ((recinfo.Default_Status_Flag is null) AND (X_Default_Status_Flag is null)))
291 AND ((recinfo.DEFAULT_FLG_EFFECTIVE_FROM = X_DEFAULT_FLG_EFFECTIVE_FROM)
292 OR ((recinfo.DEFAULT_FLG_EFFECTIVE_FROM is null) AND (X_DEFAULT_FLG_EFFECTIVE_FROM is null)))
293 AND ((recinfo.DEFAULT_FLG_EFFECTIVE_TO = X_DEFAULT_FLG_EFFECTIVE_TO)
294 OR ((recinfo.DEFAULT_FLG_EFFECTIVE_TO is null) AND (X_DEFAULT_FLG_EFFECTIVE_TO is null)))
295 AND ((recinfo.Def_Rec_Settlement_Option_Code = X_Def_Rec_Settlement_Option_Co)
296 OR ((recinfo.Def_Rec_Settlement_Option_Code is null) AND (X_Def_Rec_Settlement_Option_Co is null)
297 ))
298 AND ((recinfo.Record_Type_Code = X_Record_Type_Code)
299 OR ((recinfo.Record_Type_Code is null) AND (X_Record_Type_Code is null)))
300 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
301 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
302 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
303 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
304 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
305 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
306 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
307 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
308 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
309 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
310 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
311 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
312 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
313 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
314 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
315 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
316 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
317 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
318 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
319 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
320 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
321 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
322 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
323 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
324 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
325 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
326 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
327 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
328 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
329 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
330 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
331 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
332 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
333 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
334 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
335 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
336 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID )
337 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
338 AND ((recinfo.Program_Login_Id = X_Program_Login_Id)
339 OR ((recinfo.Program_Login_Id is null) AND (X_Program_Login_Id is null)))
340 AND ((recinfo.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID)
341 OR ((recinfo.CONTENT_OWNER_ID is null) AND (X_CONTENT_OWNER_ID is null)))
342 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
343 ) then
344 null;
345 else
346 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
347 app_exception.raise_exception;
348 end if;
349 for tlinfo in c1 loop
350 if (tlinfo.BASELANG = 'Y') then
351 if ( ((tlinfo.TAX_STATUS_NAME = X_TAX_STATUS_NAME)
352 OR ((tlinfo.TAX_STATUS_NAME is null) AND (X_TAX_STATUS_NAME is null)))
353 ) then
354 null;
355 else
356 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
357 app_exception.raise_exception;
358 end if;
359 end if;
360 end loop;
361 return;
362 end LOCK_ROW;
363
364 procedure UPDATE_ROW (
365 X_TAX_STATUS_ID in NUMBER,
366 X_TAX_STATUS_CODE in VARCHAR2,
367 X_EFFECTIVE_FROM in DATE,
368 X_EFFECTIVE_TO in DATE,
369 X_TAX in VARCHAR2,
370 X_TAX_REGIME_CODE in VARCHAR2,
371 X_Rule_Based_Rate_Flag in VARCHAR2,
372 X_Allow_Rate_Override_Flag in VARCHAR2,
373 X_Allow_Exemptions_Flag in VARCHAR2,
374 X_Allow_Exceptions_Flag in VARCHAR2,
375 X_Default_Status_Flag in VARCHAR2,
376 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
377 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
378 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
379 X_Record_Type_Code in VARCHAR2,
380 X_REQUEST_ID in NUMBER,
381 X_ATTRIBUTE1 in VARCHAR2,
382 X_ATTRIBUTE2 in VARCHAR2,
383 X_ATTRIBUTE3 in VARCHAR2,
384 X_ATTRIBUTE4 in VARCHAR2,
385 X_ATTRIBUTE5 in VARCHAR2,
386 X_ATTRIBUTE6 in VARCHAR2,
387 X_ATTRIBUTE7 in VARCHAR2,
388 X_ATTRIBUTE8 in VARCHAR2,
389 X_ATTRIBUTE9 in VARCHAR2,
390 X_ATTRIBUTE10 in VARCHAR2,
391 X_ATTRIBUTE11 in VARCHAR2,
392 X_ATTRIBUTE12 in VARCHAR2,
393 X_ATTRIBUTE13 in VARCHAR2,
394 X_ATTRIBUTE14 in VARCHAR2,
395 X_ATTRIBUTE15 in VARCHAR2,
396 X_ATTRIBUTE_CATEGORY in VARCHAR2,
397 X_TAX_STATUS_NAME in VARCHAR2,
398 X_LAST_UPDATE_DATE in DATE,
399 X_LAST_UPDATED_BY in NUMBER,
400 X_LAST_UPDATE_LOGIN in NUMBER ,
401 X_PROGRAM_APPLICATION_ID in NUMBER,
402 X_PROGRAM_ID in NUMBER,
403 X_PROGRAM_LOGIN_ID in NUMBER,
404 X_CONTENT_OWNER_ID in NUMBER,
405 X_OBJECT_VERSION_NUMBER in NUMBER
406 ) is
407 begin
408 update ZX_STATUS_B set
409 TAX_STATUS_CODE = X_TAX_STATUS_CODE,
410 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
411 EFFECTIVE_TO = X_EFFECTIVE_TO,
412 TAX = X_TAX,
413 TAX_REGIME_CODE = X_TAX_REGIME_CODE,
414 Rule_Based_Rate_Flag = X_Rule_Based_Rate_Flag,
415 Allow_Rate_Override_Flag = X_Allow_Rate_Override_Flag,
416 Allow_Exemptions_Flag = X_Allow_Exemptions_Flag,
417 Allow_Exceptions_Flag = X_Allow_Exceptions_Flag,
418 Default_Status_Flag = X_Default_Status_Flag,
419 DEFAULT_FLG_EFFECTIVE_FROM = X_DEFAULT_FLG_EFFECTIVE_FROM,
420 DEFAULT_FLG_EFFECTIVE_TO = X_DEFAULT_FLG_EFFECTIVE_TO,
421 Def_Rec_Settlement_Option_Code = X_Def_Rec_Settlement_Option_Co,
422 Record_Type_Code = X_Record_Type_Code,
423 REQUEST_ID = X_REQUEST_ID,
424 ATTRIBUTE1 = X_ATTRIBUTE1,
425 ATTRIBUTE2 = X_ATTRIBUTE2,
426 ATTRIBUTE3 = X_ATTRIBUTE3,
427 ATTRIBUTE4 = X_ATTRIBUTE4,
428 ATTRIBUTE5 = X_ATTRIBUTE5,
429 ATTRIBUTE6 = X_ATTRIBUTE6,
430 ATTRIBUTE7 = X_ATTRIBUTE7,
431 ATTRIBUTE8 = X_ATTRIBUTE8,
432 ATTRIBUTE9 = X_ATTRIBUTE9,
433 ATTRIBUTE10 = X_ATTRIBUTE10,
434 ATTRIBUTE11 = X_ATTRIBUTE11,
435 ATTRIBUTE12 = X_ATTRIBUTE12,
436 ATTRIBUTE13 = X_ATTRIBUTE13,
437 ATTRIBUTE14 = X_ATTRIBUTE14,
438 ATTRIBUTE15 = X_ATTRIBUTE15,
439 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
440 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
441 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
442 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
443 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
444 PROGRAM_ID = X_PROGRAM_ID,
445 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
446 CONTENT_OWNER_ID=X_CONTENT_OWNER_ID,
447 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
448 where TAX_STATUS_ID = X_TAX_STATUS_ID;
449 if (sql%notfound) then
450 raise no_data_found;
451 end if;
452 update ZX_STATUS_TL set
453 TAX_STATUS_NAME = X_TAX_STATUS_NAME,
454 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
455 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
456 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
457 SOURCE_LANG = userenv('LANG')
458 where TAX_STATUS_ID = X_TAX_STATUS_ID
459 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
460 if (sql%notfound) then
461 raise no_data_found;
462 end if;
463 end UPDATE_ROW;
464
465 procedure DELETE_ROW (
466 X_TAX_STATUS_ID in NUMBER
467 ) is
468 begin
469 delete from ZX_STATUS_TL
470 where TAX_STATUS_ID = X_TAX_STATUS_ID;
471 if (sql%notfound) then
472 raise no_data_found;
473 end if;
474 delete from ZX_STATUS_B
475 where TAX_STATUS_ID = X_TAX_STATUS_ID;
476 if (sql%notfound) then
477 raise no_data_found;
478 end if;
479 end DELETE_ROW;
480
481 procedure ADD_LANGUAGE
482 is
483 begin
484 delete from ZX_STATUS_TL T
485 where not exists
486 (select NULL
487 from ZX_STATUS_B B
488 where B.TAX_STATUS_ID = T.TAX_STATUS_ID
489 );
490 update ZX_STATUS_TL T set (
491 TAX_STATUS_NAME
492 ) = (select
493 B.TAX_STATUS_NAME
494 from ZX_STATUS_TL B
495 where B.TAX_STATUS_ID = T.TAX_STATUS_ID
496 and B.LANGUAGE = T.SOURCE_LANG)
497 where (
498 T.TAX_STATUS_ID,
499 T.LANGUAGE
500 ) in (select
501 SUBT.TAX_STATUS_ID,
502 SUBT.LANGUAGE
503 from ZX_STATUS_TL SUBB, ZX_STATUS_TL SUBT
504 where SUBB.TAX_STATUS_ID = SUBT.TAX_STATUS_ID
505 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
506 and (SUBB.TAX_STATUS_NAME <> SUBT.TAX_STATUS_NAME
507 or (SUBB.TAX_STATUS_NAME is null and SUBT.TAX_STATUS_NAME is not null)
508 or (SUBB.TAX_STATUS_NAME is not null and SUBT.TAX_STATUS_NAME is null)
509 ));
510 insert into ZX_STATUS_TL (
511 TAX_STATUS_ID,
512 TAX_STATUS_NAME,
513 CREATED_BY,
514 CREATION_DATE,
515 LAST_UPDATED_BY,
516 LAST_UPDATE_DATE,
517 LAST_UPDATE_LOGIN,
518 LANGUAGE,
519 SOURCE_LANG
520 ) select
521 B.TAX_STATUS_ID,
522 B.TAX_STATUS_NAME,
523 B.CREATED_BY,
524 B.CREATION_DATE,
525 B.LAST_UPDATED_BY,
526 B.LAST_UPDATE_DATE,
527 B.LAST_UPDATE_LOGIN,
528 L.LANGUAGE_CODE,
529 B.SOURCE_LANG
530 from ZX_STATUS_TL B, FND_LANGUAGES L
531 where L.INSTALLED_FLAG in ('I', 'B')
532 and B.LANGUAGE = userenv('LANG')
533 and not exists
534 (select NULL
535 from ZX_STATUS_TL T
536 where T.TAX_STATUS_ID = B.TAX_STATUS_ID
537 and T.LANGUAGE = L.LANGUAGE_CODE);
538 end ADD_LANGUAGE;
539
540 end ZX_STATUS_PKG;
[Home] [Help]