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