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