1 package body ZX_RATES_PKG as
2 /* $Header: zxcratesb.pls 120.20.12010000.3 2009/01/15 12:46:04 nisinha ship $ */
3
4 -- start bug#6992215
5 ----------------------------------------------------------------------------------------------------------
6 -- Start of comments
7
8 -- Procedure Name : UPDATE_LOOKUP_VALUES
9 -- Description : Updating the table: FND_LOOKUP_VALUES for columns: Description and Meaning
10 -- Business Rules :
11 -- Parameters : LOOKUP_TYPE,TAX_RATE_CODE,DESCRIPTION, MEANING and one OUT parameter RETURN_STATUS
12 -- Version :
13 -- End of comments
14 -----------------------------------------------------------------------------------------------------------
15
16
17
18 PROCEDURE UPDATE_LOOKUP_VALUES (
19 P_LOOKUP_TYPE in VARCHAR2,
20 P_TAX_RATE_CODE in VARCHAR2,
21 P_DESCRIPTION IN VARCHAR2,
22 P_MEANING IN VARCHAR2,
23 X_RETURN_STATUS OUT NOCOPY VARCHAR2
24 ) IS
25
26 BEGIN
27 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
28
29 -- updating the description field in fnd_lookup values
30 UPDATE FND_LOOKUP_VALUES
31 SET description=P_DESCRIPTION,
32 meaning = Nvl(P_MEANING, P_TAX_RATE_CODE),
33 last_update_date = SYSDATE,
34 last_updated_by = FND_GLOBAL.user_id,
35 last_update_login = FND_GLOBAL.login_id
36
37 WHERE
38 LOOKUP_TYPE = P_LOOKUP_TYPE and
39 LOOKUP_CODE = NVL(TAG,P_TAX_RATE_CODE)and
40 LANGUAGE =USERENV('LANG');
41
42 EXCEPTION
43 WHEN OTHERS
44 THEN
45 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
46
47 END UPDATE_LOOKUP_VALUES;
48
49 -- end bug#6992215
50
51
52 procedure INSERT_ROW (
53 X_ROWID in out nocopy VARCHAR2,
54 X_TAX_RATE_ID in NUMBER,
55 X_TAX_RATE_CODE in VARCHAR2,
56 X_CONTENT_OWNER_ID in NUMBER,
57 X_EFFECTIVE_FROM in DATE,
58 X_EFFECTIVE_TO in DATE,
59 X_TAX_REGIME_CODE in VARCHAR2,
60 X_TAX in VARCHAR2,
61 X_TAX_STATUS_CODE in VARCHAR2,
62 X_Schedule_Based_Rate_Flag in VARCHAR2,
63 X_Rate_Type_Code in VARCHAR2,
64 X_PERCENTAGE_RATE in NUMBER,
65 X_QUANTITY_RATE in NUMBER,
66 X_UOM_CODE in VARCHAR2,
67 X_TAX_JURISDICTION_CODE in VARCHAR2,
68 X_RECOVERY_TYPE_CODE in VARCHAR2,
69 X_Active_Flag in VARCHAR2,
70 X_Default_Rate_Flag in VARCHAR2,
71 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
72 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
73 X_DEFAULT_REC_TYPE_CODE in VARCHAR2,
74 X_DEFAULT_REC_RATE_CODE in VARCHAR2,
75 X_OFFSET_TAX in VARCHAR2,
76 X_OFFSET_STATUS_CODE in VARCHAR2,
77 X_OFFSET_TAX_RATE_CODE in VARCHAR2,
78 X_RECOVERY_RULE_CODE in VARCHAR2,
79 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
80 X_Vat_Transaction_Type_Code in VARCHAR2,
81 X_Record_Type_Code in VARCHAR2,
82 X_REQUEST_ID in NUMBER,
83 X_ATTRIBUTE1 in VARCHAR2,
84 X_ATTRIBUTE2 in VARCHAR2,
85 X_ATTRIBUTE3 in VARCHAR2,
86 X_ATTRIBUTE4 in VARCHAR2,
87 X_ATTRIBUTE5 in VARCHAR2,
88 X_ATTRIBUTE6 in VARCHAR2,
89 X_ATTRIBUTE7 in VARCHAR2,
90 X_ATTRIBUTE8 in VARCHAR2,
91 X_ATTRIBUTE9 in VARCHAR2,
92 X_ATTRIBUTE10 in VARCHAR2,
93 X_ATTRIBUTE11 in VARCHAR2,
94 X_ATTRIBUTE12 in VARCHAR2,
95 X_ATTRIBUTE13 in VARCHAR2,
96 X_ATTRIBUTE14 in VARCHAR2,
97 X_ATTRIBUTE15 in VARCHAR2,
98 X_ATTRIBUTE_CATEGORY in VARCHAR2,
99 X_TAX_RATE_NAME in VARCHAR2,
100 X_CREATION_DATE in DATE,
101 X_CREATED_BY in NUMBER,
102 X_LAST_UPDATE_DATE in DATE,
103 X_LAST_UPDATED_BY in NUMBER,
104 X_LAST_UPDATE_LOGIN in NUMBER,
105 X_PROGRAM_APPLICATION_ID in NUMBER,
106 X_PROGRAM_ID in NUMBER,
107 X_Program_Login_Id in NUMBER,
108 X_ALLOW_ADHOC_TAX_RATE_FLAG in VARCHAR2,
109 X_ADJ_FOR_ADHOC_AMT_CODE in VARCHAR2,
110 X_INCLUSIVE_TAX_FLAG in VARCHAR2,
111 X_TAX_INCLUSIVE_OVERRIDE_FLAG VARCHAR2,
112 X_TAX_CLASS VARCHAR2,
113 X_OBJECT_VERSION_NUMBER in NUMBER,
114 X_ALLOW_EXEMPTIONS_FLAG in VARCHAR2,
115 X_ALLOW_EXCEPTIONS_FLAG in VARCHAR2,
116 X_SOURCE_ID in NUMBER,
117 X_DESCRIPTION IN VARCHAR2,
118 X_TAXABLE_BASIS_FORMULA_CODE in VARCHAR2
119
120 ) is
121 X_ORG_ID NUMBER;
122 X_TAX_TYPE VARCHAR2(30);
123 cursor C is select ROWID from ZX_RATES_B
124 where TAX_RATE_ID = X_TAX_RATE_ID ;
125 begin
126 insert into ZX_RATES_B (
127 TAX_RATE_ID,
128 TAX_RATE_CODE,
129 CONTENT_OWNER_ID,
130 EFFECTIVE_FROM,
131 EFFECTIVE_TO,
132 TAX_REGIME_CODE,
133 TAX,
134 TAX_STATUS_CODE,
135 Schedule_Based_Rate_Flag,
136 Rate_Type_Code,
137 PERCENTAGE_RATE,
138 QUANTITY_RATE,
139 UOM_CODE,
140 TAX_JURISDICTION_CODE,
141 RECOVERY_TYPE_CODE,
142 Active_Flag,
143 Default_Rate_Flag,
144 DEFAULT_FLG_EFFECTIVE_FROM,
145 DEFAULT_FLG_EFFECTIVE_TO,
146 DEFAULT_REC_TYPE_CODE,
147 DEFAULT_REC_RATE_CODE,
148 OFFSET_TAX,
149 OFFSET_STATUS_CODE,
150 OFFSET_TAX_RATE_CODE,
151 RECOVERY_RULE_CODE,
152 Def_Rec_Settlement_Option_Code,
153 Vat_Transaction_Type_Code,
154 Record_Type_Code,
155 REQUEST_ID,
156 ATTRIBUTE1,
157 ATTRIBUTE2,
158 ATTRIBUTE3,
159 ATTRIBUTE4,
160 ATTRIBUTE5,
161 ATTRIBUTE6,
162 ATTRIBUTE7,
163 ATTRIBUTE8,
164 ATTRIBUTE9,
165 ATTRIBUTE10,
166 ATTRIBUTE11,
167 ATTRIBUTE12,
168 ATTRIBUTE13,
169 ATTRIBUTE14,
170 ATTRIBUTE15,
171 ATTRIBUTE_CATEGORY,
172 CREATION_DATE,
173 CREATED_BY,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN,
177 PROGRAM_APPLICATION_ID,
178 PROGRAM_ID,
179 Program_Login_Id,
180 ALLOW_ADHOC_TAX_RATE_FLAG,
181 ADJ_FOR_ADHOC_AMT_CODE,
182 INCLUSIVE_TAX_FLAG,
183 TAX_INCLUSIVE_OVERRIDE_FLAG,
184 TAX_CLASS,
185 OBJECT_VERSION_NUMBER,
186 ALLOW_EXEMPTIONS_FLAG,
187 ALLOW_EXCEPTIONS_FLAG,
188 SOURCE_ID,
189 -- DESCRIPTION, commented as part of fix for bug# 6820043
190 TAXABLE_BASIS_FORMULA_CODE
191 ) values (
192 X_TAX_RATE_ID,
193 X_TAX_RATE_CODE,
194 X_CONTENT_OWNER_ID,
195 X_EFFECTIVE_FROM,
196 X_EFFECTIVE_TO,
197 X_TAX_REGIME_CODE,
198 X_TAX,
199 X_TAX_STATUS_CODE,
200 X_Schedule_Based_Rate_Flag,
201 X_Rate_Type_Code,
202 X_PERCENTAGE_RATE,
203 X_QUANTITY_RATE,
204 X_UOM_CODE,
205 X_TAX_JURISDICTION_CODE,
206 X_RECOVERY_TYPE_CODE,
207 X_Active_Flag,
208 X_Default_Rate_Flag,
209 X_DEFAULT_FLG_EFFECTIVE_FROM,
210 X_DEFAULT_FLG_EFFECTIVE_TO,
211 X_DEFAULT_REC_TYPE_CODE,
212 X_DEFAULT_REC_RATE_CODE,
213 X_OFFSET_TAX,
214 X_OFFSET_STATUS_CODE,
215 X_OFFSET_TAX_RATE_CODE,
216 X_RECOVERY_RULE_CODE,
217 X_Def_Rec_Settlement_Option_Co,
218 X_Vat_Transaction_Type_Code,
219 X_Record_Type_Code,
220 X_REQUEST_ID,
221 X_ATTRIBUTE1,
222 X_ATTRIBUTE2,
223 X_ATTRIBUTE3,
224 X_ATTRIBUTE4,
225 X_ATTRIBUTE5,
226 X_ATTRIBUTE6,
227 X_ATTRIBUTE7,
228 X_ATTRIBUTE8,
229 X_ATTRIBUTE9,
230 X_ATTRIBUTE10,
231 X_ATTRIBUTE11,
232 X_ATTRIBUTE12,
233 X_ATTRIBUTE13,
234 X_ATTRIBUTE14,
235 X_ATTRIBUTE15,
236 X_ATTRIBUTE_CATEGORY,
237 X_CREATION_DATE,
238 X_CREATED_BY,
239 X_LAST_UPDATE_DATE,
240 X_LAST_UPDATED_BY,
241 X_LAST_UPDATE_LOGIN,
242 X_PROGRAM_APPLICATION_ID,
243 X_PROGRAM_ID,
244 X_Program_Login_Id,
245 X_ALLOW_ADHOC_TAX_RATE_FLAG,
246 X_ADJ_FOR_ADHOC_AMT_CODE,
247 X_INCLUSIVE_TAX_FLAG,
248 X_TAX_INCLUSIVE_OVERRIDE_FLAG,
249 X_TAX_CLASS,
250 X_OBJECT_VERSION_NUMBER,
251 X_ALLOW_EXEMPTIONS_FLAG,
252 X_ALLOW_EXCEPTIONS_FLAG,
253 X_SOURCE_ID,
254 -- X_DESCRIPTION, commented as part of fix for bug# 6820043
255 X_TAXABLE_BASIS_FORMULA_CODE
256 );
257 insert into ZX_RATES_TL (
258 TAX_RATE_ID,
259 TAX_RATE_NAME,
260 CREATED_BY,
261 CREATION_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN,
265 LANGUAGE,
266 SOURCE_LANG,
267 description -- added as part of fix for bug# 6820043
268 ) select
269 X_TAX_RATE_ID,
270 X_TAX_RATE_NAME,
271 X_CREATED_BY,
272 X_CREATION_DATE,
273 X_LAST_UPDATED_BY,
274 X_LAST_UPDATE_DATE,
275 X_LAST_UPDATE_LOGIN,
276 L.LANGUAGE_CODE,
277 userenv('LANG'),
278 x_description -- added as part of fix for bug# 6820043
279 from FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and not exists
282 (select NULL
283 from ZX_RATES_TL T
284 where T.TAX_RATE_ID = X_TAX_RATE_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 open c;
287 fetch c into X_ROWID;
288 if (c%notfound) then
289 close c;
290 raise no_data_found;
291 end if;
292 close c;
293 --************Added to create lookup types,as per bug 4313618*************
294 /*The INSERT_ROW procedure was creating Lookups even when Tax Recovery Rate Code
295 * was being created. We did not want this to happen. Lookups should be created
296 * only for Tax Rates, not for Tax Recovery Rates. Hence this caondition ws
297 * added as fix for Bug 5052500
298 */
299 if (X_RATE_TYPE_CODE <> 'RECOVERY') then
300 INSERT_LOOKUP_VALUES(
301 'ZX_INPUT_CLASSIFICATIONS' ,
302 X_TAX_RATE_CODE ,
303 X_EFFECTIVE_FROM,
304 X_EFFECTIVE_TO,
305 -- start bug#6992215
306 X_DESCRIPTION,
307 X_TAX_RATE_NAME
308 --end bug#6992215
309 );
310
311 INSERT_LOOKUP_VALUES(
312 'ZX_OUTPUT_CLASSIFICATIONS' ,
313 X_TAX_RATE_CODE ,
314 X_EFFECTIVE_FROM,
315 X_EFFECTIVE_TO,
316 -- start bug#6992215
317 X_DESCRIPTION,
318 X_TAX_RATE_NAME
319 -- end bug#6992215
320 );
321 end if;
322
323 /*Getting the value of X_ORG_ID on the basis of X_CONTENT_OWNER_ID
324 * and tax_type_code to be passed
325 *
326 */
327
328 SELECT decode(c.party_type_code,'OU',c.party_id,-99) into X_ORG_ID
329 FROM
330 zx_party_tax_profile c
331 WHERE
332 c.party_tax_profile_id = X_CONTENT_OWNER_ID;
333
334
335 BEGIN
336 SELECT TAX_TYPE_CODE INTO X_TAX_TYPE
337 FROM
338 ZX_TAXES_B A
339 WHERE
340 A.TAX = X_TAX
341 AND A.TAX_REGIME_CODE = X_TAX_REGIME_CODE
342 AND A.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
343
344
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347
348 SELECT TAX_TYPE_CODE INTO X_TAX_TYPE
349 FROM
350 ZX_TAXES_B A
351 WHERE
352 A.TAX = X_TAX
353 AND A.TAX_REGIME_CODE = X_TAX_REGIME_CODE
354 AND A.CONTENT_OWNER_ID = -99;
355 END;
356
357
358 /* Calling procedure POPULATE_ID_TCC_MAPPING_ALL to populate the ZX_ID_TCC_MAPPING_ALL table. */
359
360 POPULATE_ID_TCC_MAPPING_ALL (
361 X_TAX_RATE_ID,
362 X_TAX_RATE_CODE,
363 X_ORG_ID,
364 X_EFFECTIVE_FROM,
365 X_EFFECTIVE_TO,
366 X_TAX_TYPE,
367 NULL,
368 X_Active_Flag,
369 NULL,
370 NULL
371 );
372
373 end INSERT_ROW;
374
375 procedure LOCK_ROW (
376 X_TAX_RATE_ID in NUMBER,
377 X_TAX_RATE_CODE in VARCHAR2,
378 X_CONTENT_OWNER_ID in NUMBER,
379 X_EFFECTIVE_FROM in DATE,
380 X_EFFECTIVE_TO in DATE,
381 X_TAX_REGIME_CODE in VARCHAR2,
382 X_TAX in VARCHAR2,
383 X_TAX_STATUS_CODE in VARCHAR2,
384 X_Schedule_Based_Rate_Flag in VARCHAR2,
385 X_Rate_Type_Code in VARCHAR2,
386 X_PERCENTAGE_RATE in NUMBER,
387 X_QUANTITY_RATE in NUMBER,
388 X_UOM_CODE in VARCHAR2,
389 X_TAX_JURISDICTION_CODE in VARCHAR2,
390 X_RECOVERY_TYPE_CODE in VARCHAR2,
391 X_Active_Flag in VARCHAR2,
392 X_Default_Rate_Flag in VARCHAR2,
393 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
394 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
395 X_DEFAULT_REC_TYPE_CODE in VARCHAR2,
396 X_DEFAULT_REC_RATE_CODE in VARCHAR2,
397 X_OFFSET_TAX in VARCHAR2,
398 X_OFFSET_STATUS_CODE in VARCHAR2,
399 X_OFFSET_TAX_RATE_CODE in VARCHAR2,
400 X_RECOVERY_RULE_CODE in VARCHAR2,
401 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
402 X_Vat_Transaction_Type_Code in VARCHAR2,
403 X_Record_Type_Code in VARCHAR2,
404 X_REQUEST_ID in NUMBER,
405 X_ATTRIBUTE1 in VARCHAR2,
406 X_ATTRIBUTE2 in VARCHAR2,
407 X_ATTRIBUTE3 in VARCHAR2,
408 X_ATTRIBUTE4 in VARCHAR2,
409 X_ATTRIBUTE5 in VARCHAR2,
410 X_ATTRIBUTE6 in VARCHAR2,
411 X_ATTRIBUTE7 in VARCHAR2,
412 X_ATTRIBUTE8 in VARCHAR2,
413 X_ATTRIBUTE9 in VARCHAR2,
414 X_ATTRIBUTE10 in VARCHAR2,
415 X_ATTRIBUTE11 in VARCHAR2,
416 X_ATTRIBUTE12 in VARCHAR2,
417 X_ATTRIBUTE13 in VARCHAR2,
418 X_ATTRIBUTE14 in VARCHAR2,
419 X_ATTRIBUTE15 in VARCHAR2,
420 X_ATTRIBUTE_CATEGORY in VARCHAR2,
421 X_TAX_RATE_NAME in VARCHAR2,
422 X_PROGRAM_APPLICATION_ID in NUMBER,
423 X_PROGRAM_ID in NUMBER,
424 X_Program_Login_Id in NUMBER,
425 X_ALLOW_ADHOC_TAX_RATE_FLAG in VARCHAR2,
426 X_ADJ_FOR_ADHOC_AMT_CODE in VARCHAR2,
427 X_INCLUSIVE_TAX_FLAG in VARCHAR2,
428 X_TAX_INCLUSIVE_OVERRIDE_FLAG VARCHAR2,
429 X_TAX_CLASS VARCHAR2,
430 X_OBJECT_VERSION_NUMBER in NUMBER,
431 X_ALLOW_EXEMPTIONS_FLAG in VARCHAR2,
432 X_ALLOW_EXCEPTIONS_FLAG in VARCHAR2,
433 X_SOURCE_ID in NUMBER,
434 X_DESCRIPTION VARCHAR2,
435 X_TAXABLE_BASIS_FORMULA_CODE in VARCHAR2
436
437 ) is
438 cursor c is select
439 TAX_RATE_CODE,
440 CONTENT_OWNER_ID,
441 EFFECTIVE_FROM,
442 EFFECTIVE_TO,
443 TAX_REGIME_CODE,
444 TAX,
445 TAX_STATUS_CODE,
446 Schedule_Based_Rate_Flag,
447 Rate_Type_Code,
448 PERCENTAGE_RATE,
449 QUANTITY_RATE,
450 UOM_CODE,
451 TAX_JURISDICTION_CODE,
452 RECOVERY_TYPE_CODE,
453 Active_Flag,
454 Default_Rate_Flag,
455 DEFAULT_FLG_EFFECTIVE_FROM,
456 DEFAULT_FLG_EFFECTIVE_TO,
457 DEFAULT_REC_TYPE_CODE,
458 DEFAULT_REC_RATE_CODE,
459 OFFSET_TAX,
460 OFFSET_STATUS_CODE,
461 OFFSET_TAX_RATE_CODE,
462 RECOVERY_RULE_CODE,
463 Def_Rec_Settlement_Option_Code,
464 Vat_Transaction_Type_Code,
465 Record_Type_Code,
466 REQUEST_ID,
467 ATTRIBUTE1,
468 ATTRIBUTE2,
469 ATTRIBUTE3,
470 ATTRIBUTE4,
471 ATTRIBUTE5,
472 ATTRIBUTE6,
473 ATTRIBUTE7,
474 ATTRIBUTE8,
475 ATTRIBUTE9,
476 ATTRIBUTE10,
477 ATTRIBUTE11,
478 ATTRIBUTE12,
479 ATTRIBUTE13,
480 ATTRIBUTE14,
481 ATTRIBUTE15,
482 ATTRIBUTE_CATEGORY,
483 ALLOW_ADHOC_TAX_RATE_FLAG,
484 ADJ_FOR_ADHOC_AMT_CODE,
485 INCLUSIVE_TAX_FLAG,
486 TAX_INCLUSIVE_OVERRIDE_FLAG,
487 TAX_CLASS,
488 OBJECT_VERSION_NUMBER,
489 ALLOW_EXEMPTIONS_FLAG,
490 ALLOW_EXCEPTIONS_FLAG,
491 SOURCE_ID,
492 -- DESCRIPTION, commented as part of fix for bug# 6820043
493 TAXABLE_BASIS_FORMULA_CODE
494 from ZX_RATES_B
495 where TAX_RATE_ID = X_TAX_RATE_ID
496 for update of TAX_RATE_ID nowait;
497 recinfo c%rowtype;
498 cursor c1 is select
499 TAX_RATE_NAME,
500 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
501 from ZX_RATES_TL
502 where TAX_RATE_ID = X_TAX_RATE_ID
503 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
504 for update of TAX_RATE_ID nowait;
505 begin
506 open c;
507 fetch c into recinfo;
508 if (c%notfound) then
509 close c;
510 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
511 app_exception.raise_exception;
512 end if;
513 close c;
514 if ( (recinfo.TAX_RATE_CODE = X_TAX_RATE_CODE)
515 AND ((recinfo.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID)
516 OR ((recinfo.CONTENT_OWNER_ID is null) AND (X_CONTENT_OWNER_ID is null)))
517 AND ((recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
518 OR ((recinfo.EFFECTIVE_FROM is null) AND (X_EFFECTIVE_FROM is null)))
519 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
520 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
521 AND (recinfo.TAX_REGIME_CODE = X_TAX_REGIME_CODE)
522 AND (recinfo.TAX = X_TAX)
523 AND ((recinfo.TAX_STATUS_CODE = X_TAX_STATUS_CODE)
524 OR ((recinfo.TAX_STATUS_CODE is null) AND (X_TAX_STATUS_CODE is null)))
525 AND ((recinfo.Schedule_Based_Rate_Flag = X_Schedule_Based_Rate_Flag)
526 OR ((recinfo.Schedule_Based_Rate_Flag is null) AND (X_Schedule_Based_Rate_Flag is null)))
527 AND ((recinfo.Rate_Type_Code = X_Rate_Type_Code)
528 OR ((recinfo.Rate_Type_Code is null) AND (X_Rate_Type_Code is null)))
529 AND ((recinfo.PERCENTAGE_RATE = X_PERCENTAGE_RATE)
530 OR ((recinfo.PERCENTAGE_RATE is null) AND (X_PERCENTAGE_RATE is null)))
531 AND ((recinfo.QUANTITY_RATE = X_QUANTITY_RATE)
532 OR ((recinfo.QUANTITY_RATE is null) AND (X_QUANTITY_RATE is null)))
533 AND ((recinfo.UOM_CODE = X_UOM_CODE)
534 OR ((recinfo.UOM_CODE is null) AND (X_UOM_CODE is null)))
535 AND ((recinfo.TAX_JURISDICTION_CODE = X_TAX_JURISDICTION_CODE)
536 OR ((recinfo.TAX_JURISDICTION_CODE is null) AND (X_TAX_JURISDICTION_CODE is null)))
537 AND ((recinfo.RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE)
538 OR ((recinfo.RECOVERY_TYPE_CODE is null) AND (X_RECOVERY_TYPE_CODE is null)))
539 AND ((recinfo.Active_Flag = X_Active_Flag)
540 OR ((recinfo.Active_Flag is null) AND (X_Active_Flag is null)))
541 AND ((recinfo.Default_Rate_Flag = X_Default_Rate_Flag)
542 OR ((recinfo.Default_Rate_Flag is null) AND (X_Default_Rate_Flag is null)))
543 AND ((recinfo.DEFAULT_FLG_EFFECTIVE_FROM = X_DEFAULT_FLG_EFFECTIVE_FROM)
544 OR ((recinfo.DEFAULT_FLG_EFFECTIVE_FROM is null) AND (X_DEFAULT_FLG_EFFECTIVE_FROM is null)))
545 AND ((recinfo.DEFAULT_FLG_EFFECTIVE_TO = X_DEFAULT_FLG_EFFECTIVE_TO)
546 OR ((recinfo.DEFAULT_FLG_EFFECTIVE_TO is null) AND (X_DEFAULT_FLG_EFFECTIVE_TO is null)))
547 AND ((recinfo.DEFAULT_REC_TYPE_CODE = X_DEFAULT_REC_TYPE_CODE)
548 OR ((recinfo.DEFAULT_REC_TYPE_CODE is null) AND (X_DEFAULT_REC_TYPE_CODE is null)))
549 AND ((recinfo.DEFAULT_REC_RATE_CODE = X_DEFAULT_REC_RATE_CODE)
550 OR ((recinfo.DEFAULT_REC_RATE_CODE is null) AND (X_DEFAULT_REC_RATE_CODE is null)))
551 AND ((recinfo.OFFSET_TAX = X_OFFSET_TAX)
552 OR ((recinfo.OFFSET_TAX is null) AND (X_OFFSET_TAX is null)))
553 AND ((recinfo.OFFSET_STATUS_CODE = X_OFFSET_STATUS_CODE)
554 OR ((recinfo.OFFSET_STATUS_CODE is null) AND (X_OFFSET_STATUS_CODE is null)))
555 AND ((recinfo.OFFSET_TAX_RATE_CODE = X_OFFSET_TAX_RATE_CODE)
556 OR ((recinfo.OFFSET_TAX_RATE_CODE is null) AND (X_OFFSET_TAX_RATE_CODE is null)))
557 AND ((recinfo.RECOVERY_RULE_CODE = X_RECOVERY_RULE_CODE)
558 OR ((recinfo.RECOVERY_RULE_CODE is null) AND (X_RECOVERY_RULE_CODE is null)))
559 AND ((recinfo.Def_Rec_Settlement_Option_Code = X_Def_Rec_Settlement_Option_Co)
560 OR ((recinfo.Def_Rec_Settlement_Option_Code is null) AND (X_Def_Rec_Settlement_Option_Co is null)))
561 AND ((recinfo.Vat_Transaction_Type_Code = X_Vat_Transaction_Type_Code)
562 OR ((recinfo.Vat_Transaction_Type_Code is null) AND (X_Vat_Transaction_Type_Code is null)))
563 AND ((recinfo.Record_Type_Code = X_Record_Type_Code)
564 OR ((recinfo.Record_Type_Code is null) AND (X_Record_Type_Code is null)))
565 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
566 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
567 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
568 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
569 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
570 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
571 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
572 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
573 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
574 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
575 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
576 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
577 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
578 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
579 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
580 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
581 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
582 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
583 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
584 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
585 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
586 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
587 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
588 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
589 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
590 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
591 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
592 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
593 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
594 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
595 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
596 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
597 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
598 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
599 AND ((recinfo.ALLOW_ADHOC_TAX_RATE_FLAG = X_ALLOW_ADHOC_TAX_RATE_FLAG)
600 OR ((recinfo.ALLOW_ADHOC_TAX_RATE_FLAG is null) AND (X_ALLOW_ADHOC_TAX_RATE_FLAG is null)))
601 AND ((recinfo.ADJ_FOR_ADHOC_AMT_CODE = X_ADJ_FOR_ADHOC_AMT_CODE)
602 OR ((recinfo.ADJ_FOR_ADHOC_AMT_CODE is null) AND (X_ADJ_FOR_ADHOC_AMT_CODE is null)))
603 AND ((recinfo.INCLUSIVE_TAX_FLAG = X_INCLUSIVE_TAX_FLAG)
604 OR ((recinfo.INCLUSIVE_TAX_FLAG is null) AND (X_INCLUSIVE_TAX_FLAG is null)))
605 AND ((recinfo.TAX_INCLUSIVE_OVERRIDE_FLAG = X_TAX_INCLUSIVE_OVERRIDE_FLAG)
606 OR ((recinfo.TAX_INCLUSIVE_OVERRIDE_FLAG is null) AND (X_TAX_INCLUSIVE_OVERRIDE_FLAG is null)))
607 AND ((recinfo.TAX_CLASS= X_TAX_CLASS)
608 OR ((recinfo.TAX_CLASS is null) AND (X_TAX_CLASS is null)))
609 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
610 AND ((recinfo.ALLOW_EXEMPTIONS_FLAG = X_ALLOW_EXEMPTIONS_FLAG)
611 OR ((recinfo.ALLOW_EXEMPTIONS_FLAG is null) AND (X_ALLOW_EXEMPTIONS_FLAG is null)))
612 AND ((recinfo.ALLOW_EXCEPTIONS_FLAG = X_ALLOW_EXCEPTIONS_FLAG)
613 OR ((recinfo.ALLOW_EXCEPTIONS_FLAG is null) AND (X_ALLOW_EXCEPTIONS_FLAG is null)))
614 AND ((recinfo.SOURCE_ID = X_SOURCE_ID)
615 OR ((recinfo.SOURCE_ID is null) AND (X_SOURCE_ID is null)))
616 /* AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
617 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null))) */ /* commented as part of fix for bug# 6820043 */
618 AND ((recinfo.TAXABLE_BASIS_FORMULA_CODE = X_TAXABLE_BASIS_FORMULA_CODE)
619 OR ((recinfo.TAXABLE_BASIS_FORMULA_CODE is null) AND (X_TAXABLE_BASIS_FORMULA_CODE is null)))
620 ) then
621 null;
622 else
623 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
624 app_exception.raise_exception;
625 end if;
626 for tlinfo in c1 loop
627 if (tlinfo.BASELANG = 'Y') then
628 if ( ((tlinfo.TAX_RATE_NAME = X_TAX_RATE_NAME)
629 OR ((tlinfo.TAX_RATE_NAME is null) AND (X_TAX_RATE_NAME is null)))
630 ) then
631 null;
632 else
633 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
634 app_exception.raise_exception;
635 end if;
636 end if;
637 end loop;
638 return;
639 end LOCK_ROW;
640
641 procedure UPDATE_ROW (
642 X_TAX_RATE_ID in NUMBER,
643 X_TAX_RATE_CODE in VARCHAR2,
644 X_CONTENT_OWNER_ID in NUMBER,
645 X_EFFECTIVE_FROM in DATE,
646 X_EFFECTIVE_TO in DATE,
647 X_TAX_REGIME_CODE in VARCHAR2,
648 X_TAX in VARCHAR2,
649 X_TAX_STATUS_CODE in VARCHAR2,
650 X_Schedule_Based_Rate_Flag in VARCHAR2,
651 X_Rate_Type_Code in VARCHAR2,
652 X_PERCENTAGE_RATE in NUMBER,
653 X_QUANTITY_RATE in NUMBER,
654 X_UOM_CODE in VARCHAR2,
655 X_TAX_JURISDICTION_CODE in VARCHAR2,
656 X_RECOVERY_TYPE_CODE in VARCHAR2,
657 X_Active_Flag in VARCHAR2,
658 X_Default_Rate_Flag in VARCHAR2,
659 X_DEFAULT_FLG_EFFECTIVE_FROM in DATE,
660 X_DEFAULT_FLG_EFFECTIVE_TO in DATE,
661 X_DEFAULT_REC_TYPE_CODE in VARCHAR2,
662 X_DEFAULT_REC_RATE_CODE in VARCHAR2,
663 X_OFFSET_TAX in VARCHAR2,
664 X_OFFSET_STATUS_CODE in VARCHAR2,
665 X_OFFSET_TAX_RATE_CODE in VARCHAR2,
666 X_RECOVERY_RULE_CODE in VARCHAR2,
667 X_Def_Rec_Settlement_Option_Co in VARCHAR2,
668 X_Vat_Transaction_Type_Code in VARCHAR2,
669 X_Record_Type_Code in VARCHAR2,
670 X_REQUEST_ID in NUMBER,
671 X_ATTRIBUTE1 in VARCHAR2,
672 X_ATTRIBUTE2 in VARCHAR2,
673 X_ATTRIBUTE3 in VARCHAR2,
674 X_ATTRIBUTE4 in VARCHAR2,
675 X_ATTRIBUTE5 in VARCHAR2,
676 X_ATTRIBUTE6 in VARCHAR2,
677 X_ATTRIBUTE7 in VARCHAR2,
678 X_ATTRIBUTE8 in VARCHAR2,
679 X_ATTRIBUTE9 in VARCHAR2,
680 X_ATTRIBUTE10 in VARCHAR2,
681 X_ATTRIBUTE11 in VARCHAR2,
682 X_ATTRIBUTE12 in VARCHAR2,
683 X_ATTRIBUTE13 in VARCHAR2,
684 X_ATTRIBUTE14 in VARCHAR2,
685 X_ATTRIBUTE15 in VARCHAR2,
686 X_ATTRIBUTE_CATEGORY in VARCHAR2,
687 X_TAX_RATE_NAME in VARCHAR2,
688 X_LAST_UPDATE_DATE in DATE,
689 X_LAST_UPDATED_BY in NUMBER,
690 X_LAST_UPDATE_LOGIN in NUMBER,
691 X_PROGRAM_APPLICATION_ID in NUMBER,
692 X_PROGRAM_ID in NUMBER,
693 X_Program_Login_Id in NUMBER,
694 X_ALLOW_ADHOC_TAX_RATE_FLAG in VARCHAR2,
695 X_ADJ_FOR_ADHOC_AMT_CODE in VARCHAR2,
696 X_INCLUSIVE_TAX_FLAG in VARCHAR2,
697 X_TAX_INCLUSIVE_OVERRIDE_FLAG VARCHAR2,
698 X_TAX_CLASS VARCHAR2,
699 X_OBJECT_VERSION_NUMBER in NUMBER,
700 X_ALLOW_EXEMPTIONS_FLAG in VARCHAR2,
701 X_ALLOW_EXCEPTIONS_FLAG in VARCHAR2,
702 X_SOURCE_ID in NUMBER,
703 X_DESCRIPTION IN VARCHAR2,
704 X_TAXABLE_BASIS_FORMULA_CODE in VARCHAR2
705 ) is
706 X_ORG_ID NUMBER;
707 X_TAX_TYPE VARCHAR2(30);
708 X_RETURN_STATUS VARCHAR2(1) ; --bug#6992215
709
710 begin
711 update ZX_RATES_B set
712 TAX_RATE_CODE = X_TAX_RATE_CODE,
713 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID,
714 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
715 EFFECTIVE_TO = X_EFFECTIVE_TO,
716 TAX_REGIME_CODE = X_TAX_REGIME_CODE,
717 TAX = X_TAX,
718 TAX_STATUS_CODE = X_TAX_STATUS_CODE,
719 Schedule_Based_Rate_Flag = X_Schedule_Based_Rate_Flag,
720 Rate_Type_Code = X_Rate_Type_Code,
721 PERCENTAGE_RATE = X_PERCENTAGE_RATE,
722 QUANTITY_RATE = X_QUANTITY_RATE,
723 UOM_CODE = X_UOM_CODE,
724 TAX_JURISDICTION_CODE = X_TAX_JURISDICTION_CODE,
725 RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
726 Active_Flag = X_Active_Flag,
727 Default_Rate_Flag = X_Default_Rate_Flag,
728 DEFAULT_FLG_EFFECTIVE_FROM = X_DEFAULT_FLG_EFFECTIVE_FROM,
729 DEFAULT_FLG_EFFECTIVE_TO = X_DEFAULT_FLG_EFFECTIVE_TO,
730 DEFAULT_REC_TYPE_CODE = X_DEFAULT_REC_TYPE_CODE,
731 DEFAULT_REC_RATE_CODE = X_DEFAULT_REC_RATE_CODE,
732 OFFSET_TAX = X_OFFSET_TAX,
733 OFFSET_STATUS_CODE = X_OFFSET_STATUS_CODE,
734 OFFSET_TAX_RATE_CODE = X_OFFSET_TAX_RATE_CODE,
735 RECOVERY_RULE_CODE = X_RECOVERY_RULE_CODE,
736 Def_Rec_Settlement_Option_Code = X_Def_Rec_Settlement_Option_Co,
737 Vat_Transaction_Type_Code = X_Vat_Transaction_Type_Code,
738 Record_Type_Code = X_Record_Type_Code,
739 REQUEST_ID = X_REQUEST_ID,
740 ATTRIBUTE1 = X_ATTRIBUTE1,
741 ATTRIBUTE2 = X_ATTRIBUTE2,
742 ATTRIBUTE3 = X_ATTRIBUTE3,
743 ATTRIBUTE4 = X_ATTRIBUTE4,
744 ATTRIBUTE5 = X_ATTRIBUTE5,
745 ATTRIBUTE6 = X_ATTRIBUTE6,
746 ATTRIBUTE7 = X_ATTRIBUTE7,
747 ATTRIBUTE8 = X_ATTRIBUTE8,
751 ATTRIBUTE12 = X_ATTRIBUTE12,
748 ATTRIBUTE9 = X_ATTRIBUTE9,
749 ATTRIBUTE10 = X_ATTRIBUTE10,
750 ATTRIBUTE11 = X_ATTRIBUTE11,
752 ATTRIBUTE13 = X_ATTRIBUTE13,
753 ATTRIBUTE14 = X_ATTRIBUTE14,
754 ATTRIBUTE15 = X_ATTRIBUTE15,
755 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
756 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
757 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
758 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
759 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
760 PROGRAM_ID = X_PROGRAM_ID,
761 Program_Login_Id = X_Program_Login_Id,
762 ALLOW_ADHOC_TAX_RATE_FLAG = X_ALLOW_ADHOC_TAX_RATE_FLAG,
763 ADJ_FOR_ADHOC_AMT_CODE = X_ADJ_FOR_ADHOC_AMT_CODE,
764 INCLUSIVE_TAX_FLAG = X_INCLUSIVE_TAX_FLAG,
765 TAX_INCLUSIVE_OVERRIDE_FLAG = X_TAX_INCLUSIVE_OVERRIDE_FLAG,
766 TAX_CLASS = X_TAX_CLASS,
767 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
768 ALLOW_EXEMPTIONS_FLAG = X_ALLOW_EXEMPTIONS_FLAG,
769 ALLOW_EXCEPTIONS_FLAG = X_ALLOW_EXCEPTIONS_FLAG,
770 SOURCE_ID = X_SOURCE_ID,
771 -- DESCRIPTION = X_DESCRIPTION,/* commented as part of fix for bug# 6820043 */
772 TAXABLE_BASIS_FORMULA_CODE = X_TAXABLE_BASIS_FORMULA_CODE
773 where TAX_RATE_ID = X_TAX_RATE_ID;
774 if (sql%notfound) then
775 raise no_data_found;
776 end if;
777 update ZX_RATES_TL set
778 TAX_RATE_NAME = X_TAX_RATE_NAME,
779 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
780 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
781 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
782 DESCRIPTION = X_DESCRIPTION, /* added as part of fix for bug# 6820043 */
783 SOURCE_LANG = userenv('LANG')
784 where TAX_RATE_ID = X_TAX_RATE_ID
785 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
786 if (sql%notfound) then
787 raise no_data_found;
788 end if;
789
790
791 --START bug#6992215
792 /*
793 Updating the description column in the fnd_lookup_values. For rate code of type:RECOVERY, it will not update.
794 */
795 IF (X_RATE_TYPE_CODE <> 'RECOVERY') then
796 UPDATE_LOOKUP_VALUES(
797 'ZX_INPUT_CLASSIFICATIONS' ,
798 X_TAX_RATE_CODE ,
799 X_DESCRIPTION,
800 X_TAX_RATE_NAME,
801 X_RETURN_STATUS
802 );
803
804 UPDATE_LOOKUP_VALUES(
805 'ZX_OUTPUT_CLASSIFICATIONS' ,
806 X_TAX_RATE_CODE ,
807 X_DESCRIPTION,
808 X_TAX_RATE_NAME,
809 X_RETURN_STATUS
810 );
811 END IF;
812 --END bug#6992215
813
814 /*Getting the value of X_ORG_ID on the basis of X_CONTENT_OWNER_ID
815 * and tax_type_code to be passed
816 *
817 */
818
819
820
821 SELECT decode(c.party_type_code,'OU',c.party_id,-99) into X_ORG_ID
822 FROM
823 zx_party_tax_profile c
824 WHERE
825 c.party_tax_profile_id = X_CONTENT_OWNER_ID;
826
827
828
829 BEGIN
830 SELECT TAX_TYPE_CODE INTO X_TAX_TYPE
831 FROM
832 ZX_TAXES_B A
833 WHERE
834 A.TAX = X_TAX
835 AND A.TAX_REGIME_CODE = X_TAX_REGIME_CODE
836 AND A.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
837
838
839
840 EXCEPTION
841 WHEN NO_DATA_FOUND THEN
842
843 SELECT TAX_TYPE_CODE INTO X_TAX_TYPE
844 FROM
845 ZX_TAXES_B A
846 WHERE
847 A.TAX = X_TAX
848 AND A.TAX_REGIME_CODE = X_TAX_REGIME_CODE
849 AND A.CONTENT_OWNER_ID = -99;
850 END;
851
852 /* Calling procedure POPULATE_ID_TCC_MAPPING_ALL to populate the ZX_ID_TCC_MAPPING_ALL table. */
853
854 POPULATE_ID_TCC_MAPPING_ALL (
855 X_TAX_RATE_ID,
856 X_TAX_RATE_CODE,
857 X_ORG_ID,
858 X_EFFECTIVE_FROM,
859 X_EFFECTIVE_TO,
860 X_TAX_TYPE,
861 NULL,
862 X_Active_Flag,
863 NULL,
864 NULL
865 );
866
867 end UPDATE_ROW;
868
869 procedure DELETE_ROW (
870 X_TAX_RATE_ID in NUMBER
871 ) is
872 begin
873 delete from ZX_RATES_TL
874 where TAX_RATE_ID = X_TAX_RATE_ID;
875 if (sql%notfound) then
876 raise no_data_found;
877 end if;
878 delete from ZX_RATES_B
879 where TAX_RATE_ID = X_TAX_RATE_ID;
880 if (sql%notfound) then
881 raise no_data_found;
882 end if;
883 end DELETE_ROW;
884
885 procedure ADD_LANGUAGE
886 is
887 begin
888 delete from ZX_RATES_TL T
889 where not exists
890 (select NULL
891 from ZX_RATES_B B
892 where B.TAX_RATE_ID = T.TAX_RATE_ID
893 );
894 update ZX_RATES_TL T set (
895 TAX_RATE_NAME
896 ) = (select
897 B.TAX_RATE_NAME
898 from ZX_RATES_TL B
899 where B.TAX_RATE_ID = T.TAX_RATE_ID
900 and B.LANGUAGE = T.SOURCE_LANG)
901 where (
902 T.TAX_RATE_ID,
903 T.LANGUAGE
904 ) in (select
905 SUBT.TAX_RATE_ID,
906 SUBT.LANGUAGE
907 from ZX_RATES_TL SUBB, ZX_RATES_TL SUBT
908 where SUBB.TAX_RATE_ID = SUBT.TAX_RATE_ID
909 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
910 and (SUBB.TAX_RATE_NAME <> SUBT.TAX_RATE_NAME
914 insert into ZX_RATES_TL (
911 or (SUBB.TAX_RATE_NAME is null and SUBT.TAX_RATE_NAME is not null)
912 or (SUBB.TAX_RATE_NAME is not null and SUBT.TAX_RATE_NAME is null)
913 ));
915 TAX_RATE_ID,
916 TAX_RATE_NAME,
917 CREATED_BY,
918 CREATION_DATE,
919 LAST_UPDATED_BY,
920 LAST_UPDATE_DATE,
921 LAST_UPDATE_LOGIN,
922 LANGUAGE,
923 description,/* added as part of fix for bug# 6820043 */
924 SOURCE_LANG
925 ) select
926 B.TAX_RATE_ID,
927 B.TAX_RATE_NAME,
928 B.CREATED_BY,
929 B.CREATION_DATE,
930 B.LAST_UPDATED_BY,
931 B.LAST_UPDATE_DATE,
932 B.LAST_UPDATE_LOGIN,
933 L.LANGUAGE_CODE,
934 B.DESCRIPTION,/* added as part of fix for bug# 6820043 */
935 B.SOURCE_LANG
936 from ZX_RATES_TL B, FND_LANGUAGES L
937 where L.INSTALLED_FLAG in ('I', 'B')
938 and B.LANGUAGE = userenv('LANG')
939 and not exists
940 (select NULL
941 from ZX_RATES_TL T
942 where T.TAX_RATE_ID = B.TAX_RATE_ID
943 and T.LANGUAGE = L.LANGUAGE_CODE);
944 end ADD_LANGUAGE;
945
946 Procedure INSERT_LOOKUP_VALUES (
947 X_LOOKUP_TYPE in VARCHAR2,
948 X_TAX_RATE_CODE in VARCHAR2,
949 X_EFFECTIVE_FROM in DATE,
950 X_EFFECTIVE_TO in DATE,
951 --start bug#6992215
952 X_DESCRIPTION in VARCHAR2 DEFAULT NULL , --bug#7274382 added default
953 X_TAX_RATE_NAME IN VARCHAR2 DEFAULT NULL --bug#7274382 added default
954 -- end bug#6992215
955 ) is
956
957 begin
958
959
960
961 MERGE INTO FND_LOOKUP_VALUES
962 USING (SELECT INSTALLED_FLAG,LANGUAGE_CODE FROM FND_LANGUAGES where INSTALLED_FLAG in ('I', 'B')) L
963 ON ( LOOKUP_TYPE = X_LOOKUP_TYPE and
964 LOOKUP_CODE = NVL(TAG,X_TAX_RATE_CODE)and
965 VIEW_APPLICATION_ID='0' and
966 SECURITY_GROUP_ID='0' and
967 LANGUAGE = L.LANGUAGE_CODE
968 )
969 WHEN MATCHED THEN UPDATE SET END_DATE_ACTIVE = NULL
970
971
972
973 WHEN NOT MATCHED THEN INSERT
974 (
975 LOOKUP_TYPE ,
976 LANGUAGE ,
977 LOOKUP_CODE ,
978 MEANING ,
979 DESCRIPTION ,
980 ENABLED_FLAG ,
981 TAG,
982 START_DATE_ACTIVE ,
983 END_DATE_ACTIVE ,
984 SOURCE_LANG ,
985 SECURITY_GROUP_ID ,
986 VIEW_APPLICATION_ID ,
987 CREATION_DATE ,
988 CREATED_BY ,
989 LAST_UPDATE_DATE ,
990 LAST_UPDATED_BY ,
991 LAST_UPDATE_LOGIN
992 )
993 VALUES(
994 X_LOOKUP_TYPE,
995 LANGUAGE_CODE,
996 (SELECT CASE WHEN LENGTHB(X_TAX_RATE_CODE) > 30
997 THEN SUBSTRB(X_TAX_RATE_CODE, 1, 24) ||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_TAXES_B_S')
998 ELSE X_TAX_RATE_CODE
999 END LOOKUP_CODE
1000 FROM DUAL) ,
1001 -- start bug#6992215
1002 Nvl(X_TAX_RATE_NAME, X_TAX_RATE_CODE),
1003 X_DESCRIPTION,
1004 -- end bug#6992215
1005 'Y',
1006 (SELECT CASE WHEN LENGTHB(X_TAX_RATE_CODE) > 30
1007 THEN X_TAX_RATE_CODE
1008 ELSE
1009 NULL
1010 END TAG
1011 FROM DUAL) ,
1012 X_EFFECTIVE_FROM,
1013 X_EFFECTIVE_TO,
1014 'US',
1015 '0',
1016 '0',
1017 SYSDATE,
1018 fnd_global.user_id,
1019 SYSDATE,
1020 fnd_global.user_id,
1021 FND_GLOBAL.CONC_LOGIN_ID);
1022
1023
1024 end INSERT_LOOKUP_VALUES;
1025
1026
1027
1028
1029
1030
1031 Procedure POPULATE_ID_TCC_MAPPING_ALL (
1032 X_TAX_RATE_ID in NUMBER,
1033 X_TAX_RATE_CODE in VARCHAR2,
1034 X_ORG_ID in NUMBER,
1035 X_EFFECTIVE_FROM in DATE,
1036 X_EFFECTIVE_TO in DATE,
1037 X_TAX_TYPE in VARCHAR2,
1038 X_TAX_CLASS in varchar2,
1039 X_Active_Flag in VARCHAR2,
1040 X_LEDGER_ID in NUMBER,
1041 X_SOURCE in VARCHAR2
1042 ) is
1043
1044 L_EFFECTIVE_FROM DATE;
1045 L_EFFECTIVE_TO DATE;
1046
1047 begin
1048 /* Insert into ZX_ID_TCC_MAPPING_ALL table when ever the new rate is being created;
1049 * And if the record is already present then it will update the existing record.
1050 * If content owner is OU, add a record in zx_id_tcc_mapping_all table for each new tax rate
1051 * code created (tax_class = NULL and source = NULL).
1052 * Also note that we can create records in this table from the Conditions
1053 * flow too. In that case we stamp the TaxRateCodeId as the negative of
1054 * ConditionGroupId: Bug 5249603
1055 */
1056
1057 --Copy the values to local variable.
1058 L_EFFECTIVE_FROM := X_EFFECTIVE_FROM;
1059 L_EFFECTIVE_TO := X_EFFECTIVE_TO;
1060
1061 -- Bug # 5559151. If this procedure is called from Tax Rules/Condition Set UI then we are not passing the
1062 -- Effective From and To date. So get the Max(Start Date) and Min(End Date) from fnd_lookups
1063 IF L_EFFECTIVE_FROM IS NULL THEN
1064
1065 SELECT MAX(START_DATE_ACTIVE) INTO L_EFFECTIVE_FROM
1066 FROM FND_LOOKUPS
1067 WHERE LOOKUP_TYPE IN('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS' , 'ZX_OUTPUT_CLASSIFICATIONS')
1068 AND ENABLED_FLAG = 'Y'
1069 AND SYSDATE BETWEEN START_DATE_ACTIVE
1070 AND NVL(END_DATE_ACTIVE,SYSDATE)
1071 AND LOOKUP_CODE = X_TAX_RATE_CODE;
1072
1073 BEGIN
1074
1075 SELECT END_DATE_ACTIVE INTO L_EFFECTIVE_TO
1076 FROM FND_LOOKUPS
1077 WHERE LOOKUP_TYPE IN('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS' , 'ZX_OUTPUT_CLASSIFICATIONS')
1078 AND ENABLED_FLAG = 'Y'
1079 AND SYSDATE BETWEEN START_DATE_ACTIVE
1080 AND NVL(END_DATE_ACTIVE,SYSDATE)
1081 AND LOOKUP_CODE = X_TAX_RATE_CODE
1082 AND END_DATE_ACTIVE IS NULL
1083 AND ROWNUM = 1;
1084
1085 EXCEPTION
1086
1087 WHEN NO_DATA_FOUND THEN
1088 SELECT MAX(END_DATE_ACTIVE) INTO L_EFFECTIVE_TO
1089 FROM FND_LOOKUPS
1090 WHERE LOOKUP_TYPE IN('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS' , 'ZX_OUTPUT_CLASSIFICATIONS')
1091 AND ENABLED_FLAG = 'Y'
1092 AND SYSDATE BETWEEN START_DATE_ACTIVE
1093 AND NVL(END_DATE_ACTIVE,SYSDATE)
1094 AND LOOKUP_CODE = X_TAX_RATE_CODE;
1095 WHEN OTHERS THEN
1096 NULL;
1097 END;
1098
1099 END IF;
1100
1101 UPDATE ZX_ID_TCC_MAPPING_ALL
1102 SET
1103 EFFECTIVE_TO = L_EFFECTIVE_TO, --effective_to
1104 ACTIVE_FLAG = X_Active_Flag, -- Active_flag
1105 LAST_UPDATED_BY = fnd_global.user_id, --last_updated_by
1106 LAST_UPDATE_DATE = SYSDATE, --last_update_date
1107 LAST_UPDATE_LOGIN = fnd_global.user_id --last_update_login
1108 WHERE TAX_CLASSIFICATION_CODE = X_TAX_RATE_CODE
1109 AND ORG_ID = X_ORG_ID
1110 AND ((TAX_RATE_CODE_ID = X_TAX_RATE_ID) OR
1111 (X_TAX_RATE_ID < 0));
1112
1113
1114 IF (SQL%NOTFOUND)
1115 then
1116 INSERT INTO ZX_ID_TCC_MAPPING_ALL
1117 (
1118 TCC_MAPPING_ID ,
1119 ORG_ID ,
1120 TAX_CLASS ,
1121 TAX_RATE_CODE_ID ,
1122 TAX_CLASSIFICATION_CODE ,
1123 TAX_TYPE ,
1124 EFFECTIVE_FROM ,
1125 EFFECTIVE_TO ,
1126 SOURCE ,
1127 CREATED_BY ,
1128 CREATION_DATE ,
1129 LAST_UPDATED_BY ,
1130 LAST_UPDATE_DATE ,
1131 LAST_UPDATE_LOGIN ,
1132 REQUEST_ID ,
1133 PROGRAM_APPLICATION_ID ,
1134 PROGRAM_ID ,
1135 PROGRAM_LOGIN_ID ,
1136 LEDGER_ID ,
1137 ACTIVE_FLAG
1138 )
1139 select
1140 ZX_ID_TCC_MAPPING_ALL_S.nextval , --tcc_mapping_id
1141 X_ORG_ID , --org_id
1142 X_TAX_CLASS , --tax_class
1143 X_TAX_RATE_ID , --tax_rate_code_id
1144 X_TAX_RATE_CODE , --tax_classification_code
1145 X_TAX_TYPE , --tax_type
1146 L_EFFECTIVE_FROM , --effective_from
1147 L_EFFECTIVE_TO , --effective_to
1148 X_SOURCE , --source
1149 fnd_global.user_id , --created_by
1150 SYSDATE , --creation_date
1151 fnd_global.user_id , --last_updated_by
1152 SYSDATE , --last_update_date
1153 fnd_global.user_id , --last_update_login
1154 fnd_global.conc_request_id , --request_id
1155 fnd_global.prog_appl_id , --program_application_id
1156 fnd_global.conc_program_id , --program_id
1157 fnd_global.conc_login_id , --program_login_id
1158 X_LEDGER_ID , --ledger_id
1159 X_Active_Flag -- Active_flag )
1160 FROM
1161 dual
1162 WHERE
1163 NOT EXISTS
1164 (SELECT NULL FROM ZX_ID_TCC_MAPPING_ALL
1165 WHERE TAX_CLASSIFICATION_CODE = X_TAX_RATE_CODE
1166 AND ORG_ID = X_ORG_ID
1167 AND TAX_RATE_CODE_ID = X_TAX_RATE_ID
1168 );
1169 END IF;
1170 end POPULATE_ID_TCC_MAPPING_ALL;
1171
1172 end ZX_RATES_PKG;
[Home] [Help]