[Home] [Help]
PACKAGE BODY: APPS.ZX_RULES_PKG
Source
1 package body ZX_RULES_PKG as
2 /* $Header: zxdrulesb.pls 120.15.12010000.2 2008/12/01 11:29:04 ssanka ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_TAX_RULE_ID in NUMBER,
7 X_TAX_RULE_CODE in VARCHAR2,
8 X_TAX in VARCHAR2,
9 X_TAX_REGIME_CODE in VARCHAR2,
10 X_SERVICE_TYPE_CODE in VARCHAR2,
11 X_RECOVERY_TYPE_CODE in VARCHAR2,
12 X_PRIORITY in NUMBER,
13 X_System_Default_Flag in VARCHAR2,
14 X_EFFECTIVE_FROM in DATE,
15 X_EFFECTIVE_TO in DATE,
16 X_Record_Type_Code in VARCHAR2,
17 X_REQUEST_ID in NUMBER,
18 X_TAX_RULE_NAME in VARCHAR2,
19 X_TAX_RULE_DESC in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER,
25 X_PROGRAM_APPLICATION_ID in NUMBER,
26 X_PROGRAM_ID in NUMBER,
27 X_PROGRAM_LOGIN_ID in NUMBER,
28 X_Enabled_Flag in VARCHAR2,
29 X_APPLICATION_ID in NUMBER,
30 X_CONTENT_OWNER_ID in NUMBER,
31 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
32 X_EVENT_CLASS_MAPPING_ID in NUMBER,
33 X_TAX_EVENT_CLASS_CODE in VARCHAR2,
34 X_OBJECT_VERSION_NUMBER in NUMBER,
35 X_DETERMINING_FACTOR_CQ_CODE IN VARCHAR2,
36 X_GEOGRAPHY_TYPE IN VARCHAR2,
37 X_GEOGRAPHY_ID IN NUMBER,
38 X_TAX_LAW_REF_CODE IN VARCHAR2,
39 X_TAX_LAW_REF_DESC IN VARCHAR2,
40 X_LAST_UPDATE_MODE_FLAG IN VARCHAR2,
41 X_NEVER_BEEN_ENABLED_FLAG IN VARCHAR2
42 ) is
43 cursor C is select ROWID from ZX_RULES_B
44 where TAX_RULE_ID = X_TAX_RULE_ID;
45
46 l_tax_id NUMBER;
47
48 begin
49
50 -- Bug 5501831 - If no tax exists then Update the GCO Tax
51 --update zx_taxes_b entity for the flags, as per the latest UE changes
52 IF (X_SERVICE_TYPE_CODE = 'DET_PLACE_OF_SUPPLY') THEN
53
54 UPDATE ZX_TAXES_B
55 SET PLACE_OF_SUPPLY_RULE_FLAG = 'Y'
56 WHERE
57 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
58 TAX = X_TAX AND
59 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
60
61 IF (SQL%NOTFOUND) THEN
62 UPDATE ZX_TAXES_B
63 SET PLACE_OF_SUPPLY_RULE_FLAG = 'Y'
64 WHERE
65 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
66 TAX = X_TAX AND
67 CONTENT_OWNER_ID = -99;
68 END IF;
69
70 ELSIF (X_SERVICE_TYPE_CODE = 'DET_TAX_REGISTRATION') THEN
71
72 UPDATE ZX_TAXES_B
73 SET REGISTRATION_TYPE_RULE_FLAG = 'Y'
74 WHERE
75 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
76 TAX = X_TAX AND
77 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
78
79 IF (SQL%NOTFOUND) THEN
80 UPDATE ZX_TAXES_B
81 SET REGISTRATION_TYPE_RULE_FLAG = 'Y'
82 WHERE
83 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
84 TAX = X_TAX AND
85 CONTENT_OWNER_ID = -99;
86 END IF;
87
88 ELSIF(X_SERVICE_TYPE_CODE = 'CALCULATE_TAX_AMOUNTS') THEN
89
90 UPDATE ZX_TAXES_B
91 SET TAX_CALC_RULE_FLAG = 'Y'
92 WHERE
93 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
94 TAX = X_TAX AND
95 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
96
97 IF (SQL%NOTFOUND) THEN
98 UPDATE ZX_TAXES_B
99 SET TAX_CALC_RULE_FLAG = 'Y'
100 WHERE
101 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
102 TAX = X_TAX AND
103 CONTENT_OWNER_ID = -99;
104 END IF;
105
106 ELSIF (X_SERVICE_TYPE_CODE = 'DET_TAXABLE_BASIS') THEN
107
108 UPDATE ZX_TAXES_B
109 SET TAXABLE_BASIS_RULE_FLAG = 'Y'
110 WHERE
111 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
112 TAX = X_TAX AND
113 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
114
115 IF (SQL%NOTFOUND) THEN
116 UPDATE ZX_TAXES_B
117 SET TAXABLE_BASIS_RULE_FLAG = 'Y'
118 WHERE
119 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
120 TAX = X_TAX AND
121 CONTENT_OWNER_ID = -99;
122 END IF;
123
124 ELSIF (X_SERVICE_TYPE_CODE = 'DET_TAX_RATE') THEN
125
126 UPDATE ZX_TAXES_B
127 SET TAX_RATE_RULE_FLAG = 'Y'
128 WHERE
129 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
130 TAX = X_TAX AND
131 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
132
133 IF (SQL%NOTFOUND) THEN
134 UPDATE ZX_TAXES_B
135 SET TAX_RATE_RULE_FLAG = 'Y'
136 WHERE
137 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
138 TAX = X_TAX AND
139 CONTENT_OWNER_ID = -99;
140 END IF;
141
142 ELSIF (X_SERVICE_TYPE_CODE = 'DET_TAX_STATUS') THEN
143
144 UPDATE ZX_TAXES_B
145 SET TAX_STATUS_RULE_FLAG = 'Y'
146 WHERE
147 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
148 TAX = X_TAX AND
149 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
150
151 IF (SQL%NOTFOUND) THEN
152 UPDATE ZX_TAXES_B
153 SET TAX_STATUS_RULE_FLAG = 'Y'
154 WHERE
155 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
156 TAX = X_TAX AND
157 CONTENT_OWNER_ID = -99;
158 END IF;
159
160 ELSIF (X_SERVICE_TYPE_CODE = 'DET_APPLICABLE_TAXES') THEN
161
162 UPDATE ZX_TAXES_B
163 SET APPLICABILITY_RULE_FLAG = 'Y'
164 WHERE
165 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
166 TAX = X_TAX AND
167 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
168
169 -- Bug 5501831 - If no tax exists then Update the GCO Tax
170 /*IF (SQL%NOTFOUND) THEN
171 UPDATE ZX_TAXES_B
172 SET APPLICABILITY_RULE_FLAG = 'Y'
173 WHERE
174 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
175 TAX = X_TAX AND
176 CONTENT_OWNER_ID = -99;
177 END IF;*/
178 -- Bug 5548613 - Copying tax in this case
179 IF (SQL%NOTFOUND) THEN
180 SELECT zx_taxes_b_s.nextval INTO l_tax_id from dual;
181
182 INSERT INTO ZX_TAXES_B_TMP
183 (
184 TAX ,
185 EFFECTIVE_FROM ,
186 EFFECTIVE_TO ,
187 TAX_REGIME_CODE ,
188 TAX_TYPE_CODE ,
189 ALLOW_MANUAL_ENTRY_FLAG ,
190 ALLOW_TAX_OVERRIDE_FLAG ,
191 MIN_TXBL_BSIS_THRSHLD ,
192 MAX_TXBL_BSIS_THRSHLD ,
193 MIN_TAX_RATE_THRSHLD ,
194 MAX_TAX_RATE_THRSHLD ,
195 MIN_TAX_AMT_THRSHLD ,
196 MAX_TAX_AMT_THRSHLD ,
197 COMPOUNDING_PRECEDENCE ,
198 PERIOD_SET_NAME ,
199 EXCHANGE_RATE_TYPE ,
200 TAX_CURRENCY_CODE ,
201 TAX_PRECISION ,
202 MINIMUM_ACCOUNTABLE_UNIT ,
203 ROUNDING_RULE_CODE ,
204 TAX_STATUS_RULE_FLAG ,
205 TAX_RATE_RULE_FLAG ,
206 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
207 PLACE_OF_SUPPLY_RULE_FLAG ,
208 DIRECT_RATE_RULE_FLAG ,
209 APPLICABILITY_RULE_FLAG ,
210 TAX_CALC_RULE_FLAG ,
211 TXBL_BSIS_THRSHLD_FLAG ,
212 TAX_RATE_THRSHLD_FLAG ,
213 TAX_AMT_THRSHLD_FLAG ,
214 TAXABLE_BASIS_RULE_FLAG ,
215 DEF_INCLUSIVE_TAX_FLAG ,
216 THRSHLD_GROUPING_LVL_CODE ,
217 HAS_OTHER_JURISDICTIONS_FLAG ,
218 ALLOW_EXEMPTIONS_FLAG ,
219 ALLOW_EXCEPTIONS_FLAG ,
220 ALLOW_RECOVERABILITY_FLAG ,
221 DEF_TAX_CALC_FORMULA ,
222 TAX_INCLUSIVE_OVERRIDE_FLAG ,
223 DEF_TAXABLE_BASIS_FORMULA ,
224 DEF_REGISTR_PARTY_TYPE_CODE ,
225 REGISTRATION_TYPE_RULE_FLAG ,
226 REPORTING_ONLY_FLAG ,
227 AUTO_PRVN_FLAG ,
228 LIVE_FOR_PROCESSING_FLAG ,
229 LIVE_FOR_APPLICABILITY_FLAG ,
230 HAS_DETAIL_TB_THRSHLD_FLAG ,
231 HAS_TAX_DET_DATE_RULE_FLAG ,
232 HAS_EXCH_RATE_DATE_RULE_FLAG ,
233 HAS_TAX_POINT_DATE_RULE_FLAG ,
234 PRINT_ON_INVOICE_FLAG ,
235 USE_LEGAL_MSG_FLAG ,
236 CALC_ONLY_FLAG ,
237 PRIMARY_RECOVERY_TYPE_CODE ,
238 PRIMARY_REC_TYPE_RULE_FLAG ,
239 SECONDARY_RECOVERY_TYPE_CODE ,
240 SECONDARY_REC_TYPE_RULE_FLAG ,
241 PRIMARY_REC_RATE_DET_RULE_FLAG ,
242 SEC_REC_RATE_DET_RULE_FLAG ,
243 OFFSET_TAX_FLAG ,
244 RECOVERY_RATE_OVERRIDE_FLAG ,
245 ZONE_GEOGRAPHY_TYPE ,
246 REGN_NUM_SAME_AS_LE_FLAG ,
247 DEF_REC_SETTLEMENT_OPTION_CODE ,
248 RECORD_TYPE_CODE ,
249 ALLOW_ROUNDING_OVERRIDE_FLAG ,
250 SOURCE_TAX_FLAG ,
251 SPECIAL_INCLUSIVE_TAX_FLAG ,
252 ATTRIBUTE1 ,
253 ATTRIBUTE2 ,
254 ATTRIBUTE3 ,
255 ATTRIBUTE4 ,
256 ATTRIBUTE5 ,
257 ATTRIBUTE6 ,
258 ATTRIBUTE7 ,
259 ATTRIBUTE8 ,
260 ATTRIBUTE9 ,
261 ATTRIBUTE10 ,
262 ATTRIBUTE11 ,
263 ATTRIBUTE12 ,
264 ATTRIBUTE13 ,
265 ATTRIBUTE14 ,
266 ATTRIBUTE15 ,
267 ATTRIBUTE_CATEGORY ,
268 PARENT_GEOGRAPHY_TYPE ,
269 PARENT_GEOGRAPHY_ID ,
270 ALLOW_MASS_CREATE_FLAG ,
271 APPLIED_AMT_HANDLING_FLAG ,
272 TAX_ID ,
273 CONTENT_OWNER_ID ,
274 REP_TAX_AUTHORITY_ID ,
275 COLL_TAX_AUTHORITY_ID ,
276 THRSHLD_CHK_TMPLT_CODE ,
277 DEF_PRIMARY_REC_RATE_CODE ,
278 DEF_SECONDARY_REC_RATE_CODE ,
279 CREATED_BY ,
280 CREATION_DATE ,
281 LAST_UPDATED_BY ,
282 LAST_UPDATE_DATE ,
283 LAST_UPDATE_LOGIN ,
284 REQUEST_ID ,
285 PROGRAM_APPLICATION_ID ,
286 PROGRAM_ID ,
287 PROGRAM_LOGIN_ID ,
288 OVERRIDE_GEOGRAPHY_TYPE ,
289 OBJECT_VERSION_NUMBER ,
290 TAX_ACCOUNT_CREATE_METHOD_CODE ,
291 TAX_ACCOUNT_SOURCE_TAX ,
292 TAX_EXMPT_CR_METHOD_CODE ,
293 TAX_EXMPT_SOURCE_TAX ,
294 APPLICABLE_BY_DEFAULT_FLAG ,
295 ALLOW_DUP_REGN_NUM_FLAG ,
296 LEGAL_REPORTING_STATUS_DEF_VAL
297 )
298 SELECT
299 TAX ,
300 EFFECTIVE_FROM ,
301 EFFECTIVE_TO ,
302 TAX_REGIME_CODE ,
303 TAX_TYPE_CODE ,
304 ALLOW_MANUAL_ENTRY_FLAG ,
305 ALLOW_TAX_OVERRIDE_FLAG ,
306 MIN_TXBL_BSIS_THRSHLD ,
307 MAX_TXBL_BSIS_THRSHLD ,
308 MIN_TAX_RATE_THRSHLD ,
309 MAX_TAX_RATE_THRSHLD ,
310 MIN_TAX_AMT_THRSHLD ,
311 MAX_TAX_AMT_THRSHLD ,
312 COMPOUNDING_PRECEDENCE ,
313 PERIOD_SET_NAME ,
314 EXCHANGE_RATE_TYPE ,
315 TAX_CURRENCY_CODE ,
316 TAX_PRECISION ,
317 MINIMUM_ACCOUNTABLE_UNIT ,
318 ROUNDING_RULE_CODE ,
319 TAX_STATUS_RULE_FLAG ,
320 TAX_RATE_RULE_FLAG ,
321 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
322 PLACE_OF_SUPPLY_RULE_FLAG ,
323 DIRECT_RATE_RULE_FLAG ,
324 'Y' , -- APPLICABILITY_RULE_FLAG
325 TAX_CALC_RULE_FLAG ,
326 TXBL_BSIS_THRSHLD_FLAG ,
327 TAX_RATE_THRSHLD_FLAG ,
328 TAX_AMT_THRSHLD_FLAG ,
329 TAXABLE_BASIS_RULE_FLAG ,
330 DEF_INCLUSIVE_TAX_FLAG ,
331 THRSHLD_GROUPING_LVL_CODE ,
332 HAS_OTHER_JURISDICTIONS_FLAG ,
333 ALLOW_EXEMPTIONS_FLAG ,
334 ALLOW_EXCEPTIONS_FLAG ,
335 ALLOW_RECOVERABILITY_FLAG ,
336 DEF_TAX_CALC_FORMULA ,
337 TAX_INCLUSIVE_OVERRIDE_FLAG ,
338 DEF_TAXABLE_BASIS_FORMULA ,
339 DEF_REGISTR_PARTY_TYPE_CODE ,
340 REGISTRATION_TYPE_RULE_FLAG ,
341 REPORTING_ONLY_FLAG ,
342 AUTO_PRVN_FLAG ,
343 LIVE_FOR_PROCESSING_FLAG ,
344 LIVE_FOR_APPLICABILITY_FLAG ,
345 HAS_DETAIL_TB_THRSHLD_FLAG ,
346 HAS_TAX_DET_DATE_RULE_FLAG ,
347 HAS_EXCH_RATE_DATE_RULE_FLAG ,
348 HAS_TAX_POINT_DATE_RULE_FLAG ,
349 PRINT_ON_INVOICE_FLAG ,
350 USE_LEGAL_MSG_FLAG ,
351 CALC_ONLY_FLAG ,
352 PRIMARY_RECOVERY_TYPE_CODE ,
353 PRIMARY_REC_TYPE_RULE_FLAG ,
354 SECONDARY_RECOVERY_TYPE_CODE ,
355 SECONDARY_REC_TYPE_RULE_FLAG ,
356 PRIMARY_REC_RATE_DET_RULE_FLAG ,
357 SEC_REC_RATE_DET_RULE_FLAG ,
358 OFFSET_TAX_FLAG ,
359 RECOVERY_RATE_OVERRIDE_FLAG ,
360 ZONE_GEOGRAPHY_TYPE ,
361 REGN_NUM_SAME_AS_LE_FLAG ,
362 DEF_REC_SETTLEMENT_OPTION_CODE ,
363 RECORD_TYPE_CODE ,
364 ALLOW_ROUNDING_OVERRIDE_FLAG ,
365 SOURCE_TAX_FLAG ,
366 SPECIAL_INCLUSIVE_TAX_FLAG ,
367 ATTRIBUTE1 ,
368 ATTRIBUTE2 ,
369 ATTRIBUTE3 ,
370 ATTRIBUTE4 ,
371 ATTRIBUTE5 ,
375 ATTRIBUTE9 ,
372 ATTRIBUTE6 ,
373 ATTRIBUTE7 ,
374 ATTRIBUTE8 ,
376 ATTRIBUTE10 ,
377 ATTRIBUTE11 ,
378 ATTRIBUTE12 ,
379 ATTRIBUTE13 ,
380 ATTRIBUTE14 ,
381 ATTRIBUTE15 ,
382 ATTRIBUTE_CATEGORY ,
383 PARENT_GEOGRAPHY_TYPE ,
384 PARENT_GEOGRAPHY_ID ,
385 ALLOW_MASS_CREATE_FLAG ,
386 APPLIED_AMT_HANDLING_FLAG ,
387 l_tax_id , -- TAX_ID
388 x_content_owner_id , -- CONTENT_OWNER_ID
389 REP_TAX_AUTHORITY_ID ,
390 COLL_TAX_AUTHORITY_ID ,
391 THRSHLD_CHK_TMPLT_CODE ,
392 DEF_PRIMARY_REC_RATE_CODE ,
393 DEF_SECONDARY_REC_RATE_CODE ,
394 X_CREATED_BY ,
395 X_CREATION_DATE ,
396 X_LAST_UPDATED_BY ,
397 X_LAST_UPDATE_DATE ,
398 X_LAST_UPDATE_LOGIN ,
399 REQUEST_ID ,
400 PROGRAM_APPLICATION_ID ,
401 PROGRAM_ID ,
402 PROGRAM_LOGIN_ID ,
403 OVERRIDE_GEOGRAPHY_TYPE ,
404 1 , -- OBJECT_VERSION_NUMBER
405 TAX_ACCOUNT_CREATE_METHOD_CODE ,
406 TAX_ACCOUNT_SOURCE_TAX ,
407 TAX_EXMPT_CR_METHOD_CODE ,
408 TAX_EXMPT_SOURCE_TAX ,
409 APPLICABLE_BY_DEFAULT_FLAG ,
410 ALLOW_DUP_REGN_NUM_FLAG ,
411 LEGAL_REPORTING_STATUS_DEF_VAL
412 FROM ZX_TAXES_B
413 WHERE TAX_REGIME_CODE = X_TAX_REGIME_CODE
414 AND TAX = X_TAX
415 AND CONTENT_OWNER_ID = -99;
416 --RETURNING TAX_ID INTO l_tax_id;
417
418 IF (l_tax_id IS NOT NULL) THEN
419 INSERT INTO ZX_TAXES_TL
420 (
421 LANGUAGE ,
422 SOURCE_LANG ,
423 TAX_FULL_NAME ,
424 CREATED_BY ,
425 CREATION_DATE ,
426 LAST_UPDATED_BY ,
427 LAST_UPDATE_DATE ,
428 LAST_UPDATE_LOGIN ,
429 TAX_ID
430 )
431 SELECT
432 zttl.LANGUAGE ,
433 zttl.SOURCE_LANG ,
434 zttl.TAX_FULL_NAME ,
435 X_CREATED_BY ,
436 X_CREATION_DATE ,
437 X_LAST_UPDATED_BY ,
438 X_LAST_UPDATE_DATE ,
439 X_LAST_UPDATE_LOGIN ,
440 l_tax_id
441 FROM ZX_TAXES_TL zttl,
442 ZX_TAXES_B ztb
443 WHERE ztb.TAX_REGIME_CODE = X_TAX_REGIME_CODE
444 AND ztb.TAX = X_TAX
445 AND ztb.CONTENT_OWNER_ID = -99
446 AND zttl.TAX_ID = ztb.TAX_ID;
447 END IF;
448
449 END IF;
450
451 ELSIF (X_SERVICE_TYPE_CODE = 'DET_DIRECT_RATE') THEN
452
453 UPDATE ZX_TAXES_B
454 SET DIRECT_RATE_RULE_FLAG = 'Y'
455 WHERE
456 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
457 TAX = X_TAX AND
458 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID;
459
460 IF (SQL%NOTFOUND) THEN
461 UPDATE ZX_TAXES_B
462 SET DIRECT_RATE_RULE_FLAG = 'Y'
463 WHERE
464 TAX_REGIME_CODE = X_TAX_REGIME_CODE AND
465 TAX = X_TAX AND
466 CONTENT_OWNER_ID = -99;
467 END IF;
468
469 END IF;
470
471 insert into ZX_RULES_B (
472 TAX_RULE_ID,
473 TAX_RULE_CODE,
474 TAX,
475 TAX_REGIME_CODE,
476 SERVICE_TYPE_CODE,
477 RECOVERY_TYPE_CODE,
478 PRIORITY,
479 System_Default_Flag,
480 EFFECTIVE_FROM,
481 EFFECTIVE_TO,
482 Record_Type_Code,
483 REQUEST_ID,
484 CREATION_DATE,
485 CREATED_BY,
486 LAST_UPDATE_DATE,
487 LAST_UPDATED_BY,
488 LAST_UPDATE_LOGIN,
489 PROGRAM_APPLICATION_ID,
490 PROGRAM_ID,
491 PROGRAM_LOGIN_ID,
492 Enabled_Flag,
493 APPLICATION_ID,
494 CONTENT_OWNER_ID,
495 DET_FACTOR_TEMPL_CODE,
496 EVENT_CLASS_MAPPING_ID ,
497 TAX_EVENT_CLASS_CODE,
498 OBJECT_VERSION_NUMBER,
499 DETERMINING_FACTOR_CQ_CODE ,
500 GEOGRAPHY_TYPE ,
501 GEOGRAPHY_ID ,
502 TAX_LAW_REF_CODE ,
506 values (
503 LAST_UPDATE_MODE_FLAG ,
504 NEVER_BEEN_ENABLED_FLAG
505 )
507 X_TAX_RULE_ID,
508 X_TAX_RULE_CODE,
509 X_TAX,
510 X_TAX_REGIME_CODE,
511 X_SERVICE_TYPE_CODE,
512 X_RECOVERY_TYPE_CODE,
513 X_PRIORITY,
514 NVL(X_SYSTEM_DEFAULT_FLAG,'N'),
515 X_EFFECTIVE_FROM,
516 X_EFFECTIVE_TO,
517 X_Record_Type_Code,
518 X_REQUEST_ID,
519 X_CREATION_DATE,
520 X_CREATED_BY,
521 X_LAST_UPDATE_DATE,
522 X_LAST_UPDATED_BY,
523 X_LAST_UPDATE_LOGIN,
524 X_PROGRAM_APPLICATION_ID,
525 X_PROGRAM_ID,
526 X_PROGRAM_LOGIN_ID,
527 NVL(X_ENABLED_FLAG,'N'),
528 X_APPLICATION_ID,
529 X_CONTENT_OWNER_ID,
530 X_DET_FACTOR_TEMPL_CODE,
531 X_EVENT_CLASS_MAPPING_ID,
532 X_TAX_EVENT_CLASS_CODE,
533 X_OBJECT_VERSION_NUMBER,
534 X_DETERMINING_FACTOR_CQ_CODE ,
535 X_GEOGRAPHY_TYPE ,
536 X_GEOGRAPHY_ID ,
537 X_TAX_LAW_REF_CODE ,
538 X_LAST_UPDATE_MODE_FLAG ,
539 X_NEVER_BEEN_ENABLED_FLAG);
540 insert into ZX_RULES_TL (
541 TAX_RULE_ID,
542 TAX_RULE_NAME,
543 TAX_RULE_DESC,
544 CREATION_DATE,
545 CREATED_BY,
546 LAST_UPDATE_DATE,
547 LAST_UPDATED_BY,
548 LAST_UPDATE_LOGIN,
549 LANGUAGE,
550 SOURCE_LANG,
551 TAX_LAW_REF_DESC)
552 select
553 X_TAX_RULE_ID,
554 X_TAX_RULE_NAME,
555 X_TAX_RULE_DESC,
556 X_CREATION_DATE,
557 X_CREATED_BY,
558 X_LAST_UPDATE_DATE,
559 X_LAST_UPDATED_BY,
560 X_LAST_UPDATE_LOGIN,
561 L.LANGUAGE_CODE,
562 userenv('LANG') ,
563 X_TAX_LAW_REF_DESC
564 from FND_LANGUAGES L
565 where L.INSTALLED_FLAG in ('I', 'B')
566 and not exists
567 (select NULL
568 from ZX_RULES_TL T
569 where T.TAX_RULE_ID = X_TAX_RULE_ID
570 and T.LANGUAGE = L.LANGUAGE_CODE);
571
572 open c;
573 fetch c into X_ROWID;
574 if (c%notfound) then
575 close c;
576 raise no_data_found;
577 end if;
578 close c;
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 APP_EXCEPTION.RAISE_EXCEPTION;
583
584 end INSERT_ROW;
585
586 procedure LOCK_ROW (
587 X_TAX_RULE_ID in NUMBER,
588 X_TAX_RULE_CODE in VARCHAR2,
589 X_TAX in VARCHAR2,
590 X_TAX_REGIME_CODE in VARCHAR2,
591 X_SERVICE_TYPE_CODE in VARCHAR2,
592 X_RECOVERY_TYPE_CODE in VARCHAR2,
593 X_PRIORITY in NUMBER,
594 X_System_Default_Flag in VARCHAR2,
595 X_EFFECTIVE_FROM in DATE,
596 X_EFFECTIVE_TO in DATE,
597 X_Record_Type_Code in VARCHAR2,
598 X_REQUEST_ID in NUMBER,
599 X_TAX_RULE_NAME in VARCHAR2,
600 X_TAX_RULE_DESC in VARCHAR2,
601 X_PROGRAM_APPLICATION_ID in NUMBER,
602 X_PROGRAM_ID in NUMBER,
603 X_PROGRAM_LOGIN_ID in NUMBER,
604 X_Enabled_Flag in VARCHAR2,
605 X_APPLICATION_ID in NUMBER,
606 X_CONTENT_OWNER_ID in NUMBER,
607 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
608 X_EVENT_CLASS_MAPPING_ID in NUMBER,
609 X_TAX_EVENT_CLASS_CODE in VARCHAR2,
610 X_OBJECT_VERSION_NUMBER in NUMBER,
611 X_DETERMINING_FACTOR_CQ_CODE IN VARCHAR2,
612 X_GEOGRAPHY_TYPE IN VARCHAR2,
613 X_GEOGRAPHY_ID IN NUMBER,
614 X_TAX_LAW_REF_CODE IN VARCHAR2,
615 X_TAX_LAW_REF_DESC IN VARCHAR2,
616 X_LAST_UPDATE_MODE_FLAG IN VARCHAR2,
617 X_NEVER_BEEN_ENABLED_FLAG IN VARCHAR2
618
619 ) is
620
621 cursor c is select
622 TAX_RULE_CODE,
623 TAX,
624 TAX_REGIME_CODE,
625 SERVICE_TYPE_CODE,
626 RECOVERY_TYPE_CODE,
627 PRIORITY,
628 System_Default_Flag,
629 EFFECTIVE_FROM,
630 EFFECTIVE_TO,
631 Record_Type_Code,
632 REQUEST_ID,
633 PROGRAM_APPLICATION_ID,
634 PROGRAM_ID,
635 PROGRAM_LOGIN_ID,
636 Enabled_Flag,
637 APPLICATION_ID,
638 CONTENT_OWNER_ID,
639 DET_FACTOR_TEMPL_CODE,
640 EVENT_CLASS_MAPPING_ID ,
641 TAX_EVENT_CLASS_CODE,
642 OBJECT_VERSION_NUMBER,
643 DETERMINING_FACTOR_CQ_CODE ,
644 GEOGRAPHY_TYPE ,
645 GEOGRAPHY_ID ,
646 TAX_LAW_REF_CODE ,
647 LAST_UPDATE_MODE_FLAG ,
648 NEVER_BEEN_ENABLED_FLAG
649
650 from ZX_RULES_B
651 where TAX_RULE_ID = X_TAX_RULE_ID
652 for update of TAX_RULE_ID nowait;
653
654 recinfo c%rowtype;
655 cursor c1 is select
656 TAX_RULE_NAME,
657 TAX_RULE_DESC,
658 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG ,
659 TAX_LAW_REF_DESC
660 from ZX_RULES_TL
661 where TAX_RULE_ID = X_TAX_RULE_ID
662 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
663 for update of TAX_RULE_ID nowait;
664
665 begin
666 open c;
667 fetch c into recinfo;
668 if (c%notfound) then
672 end if;
669 close c;
670 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
671 app_exception.raise_exception;
673 close c;
674 if ((recinfo.TAX_RULE_CODE = X_TAX_RULE_CODE)
675 AND ((recinfo.TAX = X_TAX)
676 OR ((recinfo.TAX is null) AND (X_TAX is null)))
677 AND (recinfo.TAX_REGIME_CODE = X_TAX_REGIME_CODE)
678 AND (recinfo.SERVICE_TYPE_CODE = X_SERVICE_TYPE_CODE)
679 AND ((recinfo.RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE)
680 OR ((recinfo.RECOVERY_TYPE_CODE is null) AND (X_RECOVERY_TYPE_CODE is null)))
681 AND (recinfo.PRIORITY = X_PRIORITY)
682 AND ((recinfo.System_Default_Flag = X_System_Default_Flag)
683 OR ((recinfo.System_Default_Flag is null) AND (X_System_Default_Flag is null)))
684 AND (recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
685 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
686 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
687 AND (recinfo.Record_Type_Code = X_Record_Type_Code)
688 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
689 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
690 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
691 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
692 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID)
693 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
694 AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
695 OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
696 AND ((recinfo.Enabled_Flag = X_Enabled_Flag)
697 OR ((recinfo.Enabled_Flag is null) AND (X_Enabled_Flag is null)))
698 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
699 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
700 AND ((recinfo.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID)
701 OR ((recinfo.CONTENT_OWNER_ID is null) AND (X_CONTENT_OWNER_ID is null)))
702 AND ((recinfo.DET_FACTOR_TEMPL_CODE = X_DET_FACTOR_TEMPL_CODE)
703 OR ((recinfo.DET_FACTOR_TEMPL_CODE is null) AND (X_DET_FACTOR_TEMPL_CODE is null)))
704 AND ((recinfo.EVENT_CLASS_MAPPING_ID = X_EVENT_CLASS_MAPPING_ID)
705 OR ((recinfo.EVENT_CLASS_MAPPING_ID is null) AND (X_EVENT_CLASS_MAPPING_ID is null)))
706 AND ((recinfo.TAX_EVENT_CLASS_CODE = X_TAX_EVENT_CLASS_CODE)
707 OR ((recinfo.TAX_EVENT_CLASS_CODE is null) AND (X_TAX_EVENT_CLASS_CODE is null)))
708 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
709
710 AND ((recinfo.DETERMINING_FACTOR_CQ_CODE = X_DETERMINING_FACTOR_CQ_CODE)
711 OR ((recinfo.DETERMINING_FACTOR_CQ_CODE is null) AND (X_DETERMINING_FACTOR_CQ_CODE is null)))
712
713 AND ((recinfo.GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE)
714 OR ((recinfo.GEOGRAPHY_TYPE is null) AND (X_GEOGRAPHY_TYPE is null)))
715
716 AND ((recinfo.GEOGRAPHY_ID = X_GEOGRAPHY_ID)
717 OR ((recinfo.GEOGRAPHY_ID is null) AND (X_GEOGRAPHY_ID is null)))
718
719 AND ((recinfo.TAX_LAW_REF_CODE = X_TAX_LAW_REF_CODE)
720 OR ((recinfo.TAX_LAW_REF_CODE is null) AND (X_TAX_LAW_REF_CODE is null)))
721
722 AND ((recinfo.LAST_UPDATE_MODE_FLAG = X_LAST_UPDATE_MODE_FLAG)
723 OR ((recinfo.LAST_UPDATE_MODE_FLAG is null) AND (X_LAST_UPDATE_MODE_FLAG is null)))
724
725 AND ((recinfo.NEVER_BEEN_ENABLED_FLAG = X_NEVER_BEEN_ENABLED_FLAG)
726 OR ((recinfo.NEVER_BEEN_ENABLED_FLAG is null) AND (X_NEVER_BEEN_ENABLED_FLAG is null)))
727
728 ) then
729 null;
730 else
731 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
732 app_exception.raise_exception;
733 end if;
734 for tlinfo in c1 loop
735 if (tlinfo.BASELANG = 'Y') then
736 if ( (tlinfo.TAX_RULE_NAME = X_TAX_RULE_NAME)
737 AND ((tlinfo.TAX_RULE_DESC = X_TAX_RULE_DESC)
738 OR ((tlinfo.TAX_RULE_DESC is null) AND (X_TAX_RULE_DESC is null)))
739 ) then
740 null;
741 else
742 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
743 app_exception.raise_exception;
744 end if;
745 end if;
746 end loop;
747 return;
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 APP_EXCEPTION.RAISE_EXCEPTION;
752
753 end LOCK_ROW;
754
755 procedure UPDATE_ROW (
756 X_TAX_RULE_ID in NUMBER,
757 X_TAX_RULE_CODE in VARCHAR2,
758 X_TAX in VARCHAR2,
759 X_TAX_REGIME_CODE in VARCHAR2,
760 X_SERVICE_TYPE_CODE in VARCHAR2,
761 X_RECOVERY_TYPE_CODE in VARCHAR2,
762 X_PRIORITY in NUMBER,
763 X_System_Default_Flag in VARCHAR2,
764 X_EFFECTIVE_FROM in DATE,
765 X_EFFECTIVE_TO in DATE,
766 X_Record_Type_Code in VARCHAR2,
767 X_REQUEST_ID in NUMBER,
768 X_TAX_RULE_NAME in VARCHAR2,
769 X_TAX_RULE_DESC in VARCHAR2,
770 X_LAST_UPDATE_DATE in DATE,
771 X_LAST_UPDATED_BY in NUMBER,
772 X_LAST_UPDATE_LOGIN in NUMBER,
773 X_PROGRAM_APPLICATION_ID in NUMBER,
774 X_PROGRAM_ID in NUMBER,
775 X_PROGRAM_LOGIN_ID in NUMBER,
776 X_Enabled_Flag in VARCHAR2,
777 X_APPLICATION_ID in NUMBER,
778 X_CONTENT_OWNER_ID in NUMBER,
779 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
780 X_EVENT_CLASS_MAPPING_ID in NUMBER,
781 X_TAX_EVENT_CLASS_CODE in VARCHAR2,
782 X_OBJECT_VERSION_NUMBER in NUMBER,
783 X_DETERMINING_FACTOR_CQ_CODE IN VARCHAR2,
784 X_GEOGRAPHY_TYPE IN VARCHAR2,
788 X_LAST_UPDATE_MODE_FLAG IN VARCHAR2,
785 X_GEOGRAPHY_ID IN NUMBER,
786 X_TAX_LAW_REF_CODE IN VARCHAR2,
787 X_TAX_LAW_REF_DESC IN VARCHAR2,
789 X_NEVER_BEEN_ENABLED_FLAG IN VARCHAR2
790 ) is
791
792 begin
793 update ZX_RULES_B set
794 TAX_RULE_CODE = X_TAX_RULE_CODE,
795 TAX = X_TAX,
796 TAX_REGIME_CODE = X_TAX_REGIME_CODE,
797 SERVICE_TYPE_CODE = X_SERVICE_TYPE_CODE,
798 RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
799 PRIORITY = X_PRIORITY,
800 System_Default_Flag = NVL(X_SYSTEM_DEFAULT_FLAG,'N'),
801 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
802 EFFECTIVE_TO = X_EFFECTIVE_TO,
803 Record_Type_Code = X_Record_Type_Code,
804 REQUEST_ID = X_REQUEST_ID,
805 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
806 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
807 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
808 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
809 PROGRAM_ID = X_PROGRAM_ID,
810 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
811 Enabled_Flag = NVL(X_ENABLED_FLAG,'N'),
812 APPLICATION_ID = X_APPLICATION_ID,
813 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID,
814 DET_FACTOR_TEMPL_CODE = X_DET_FACTOR_TEMPL_CODE,
815 EVENT_CLASS_MAPPING_ID = X_EVENT_CLASS_MAPPING_ID,
816 TAX_EVENT_CLASS_CODE = X_TAX_EVENT_CLASS_CODE,
817 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
818 DETERMINING_FACTOR_CQ_CODE=X_DETERMINING_FACTOR_CQ_CODE ,
819 GEOGRAPHY_TYPE=X_GEOGRAPHY_TYPE ,
820 GEOGRAPHY_ID=X_GEOGRAPHY_ID ,
821 TAX_LAW_REF_CODE=X_TAX_LAW_REF_CODE ,
822 LAST_UPDATE_MODE_FLAG=X_LAST_UPDATE_MODE_FLAG ,
823 NEVER_BEEN_ENABLED_FLAG=X_NEVER_BEEN_ENABLED_FLAG
824 where TAX_RULE_ID = X_TAX_RULE_ID;
825
826 if (sql%notfound) then
827 raise no_data_found;
828 end if;
829
830 update ZX_RULES_TL set
831 TAX_RULE_NAME = X_TAX_RULE_NAME,
832 TAX_RULE_DESC = X_TAX_RULE_DESC,
833 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
834 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
835 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
836 SOURCE_LANG = userenv('LANG') ,
837 TAX_LAW_REF_DESC = X_TAX_LAW_REF_DESC
838 where TAX_RULE_ID = X_TAX_RULE_ID
839 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
840
841 if (sql%notfound) then
842 raise no_data_found;
843 end if;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 APP_EXCEPTION.RAISE_EXCEPTION;
848
849 end UPDATE_ROW;
850 procedure DELETE_ROW (
851 X_TAX_RULE_ID in NUMBER) is
852
853 begin
854 delete from ZX_RULES_TL
855 where TAX_RULE_ID = X_TAX_RULE_ID;
856
857 if (sql%notfound) then
858 raise no_data_found;
859 end if;
860
861 delete from ZX_RULES_B
862 where TAX_RULE_ID = X_TAX_RULE_ID;
863
864 if (sql%notfound) then
865 raise no_data_found;
866 end if;
867
868 EXCEPTION
869 WHEN OTHERS THEN
870 APP_EXCEPTION.RAISE_EXCEPTION;
871
872 end DELETE_ROW;
873
874 procedure ADD_LANGUAGE is
875 begin
876 delete from ZX_RULES_TL T
877 where not exists
878 (select NULL
879 from ZX_RULES_B B
880 where B.TAX_RULE_ID = T.TAX_RULE_ID);
881 update ZX_RULES_TL T set (
882 TAX_RULE_NAME,
883 TAX_RULE_DESC) = (select B.TAX_RULE_NAME,
884 B.TAX_RULE_DESC
885 from ZX_RULES_TL B
886 where B.TAX_RULE_ID = T.TAX_RULE_ID
887 and B.LANGUAGE = T.SOURCE_LANG)
888 where (T.TAX_RULE_ID, T.LANGUAGE) in
889 (select SUBT.TAX_RULE_ID,
890 SUBT.LANGUAGE
891 from ZX_RULES_TL SUBB, ZX_RULES_TL SUBT
892 where SUBB.TAX_RULE_ID = SUBT.TAX_RULE_ID
893 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
894 and (SUBB.TAX_RULE_NAME <> SUBT.TAX_RULE_NAME
895 or SUBB.TAX_RULE_DESC <> SUBT.TAX_RULE_DESC
896 or (SUBB.TAX_RULE_DESC is null and SUBT.TAX_RULE_DESC is not null)
897 or (SUBB.TAX_RULE_DESC is not null and SUBT.TAX_RULE_DESC is null) ));
898
899 insert into ZX_RULES_TL (
900 TAX_RULE_ID,
901 TAX_RULE_NAME,
902 TAX_RULE_DESC,
903 CREATION_DATE,
904 CREATED_BY,
905 LAST_UPDATE_DATE,
906 LAST_UPDATED_BY,
907 LAST_UPDATE_LOGIN,
908 LANGUAGE,
909 SOURCE_LANG)
910 select
911 B.TAX_RULE_ID,
912 B.TAX_RULE_NAME,
913 B.TAX_RULE_DESC,
914 B.CREATION_DATE,
915 B.CREATED_BY,
916 B.LAST_UPDATE_DATE,
917 B.LAST_UPDATED_BY,
918 B.LAST_UPDATE_LOGIN,
919 L.LANGUAGE_CODE,
920 B.SOURCE_LANG
921 from ZX_RULES_TL B, FND_LANGUAGES L
922 where L.INSTALLED_FLAG in ('I', 'B')
923 and B.LANGUAGE = userenv('LANG')
924 and not exists
925 (select NULL
926 from ZX_RULES_TL T
927 where T.TAX_RULE_ID = B.TAX_RULE_ID
928 and T.LANGUAGE = L.LANGUAGE_CODE);
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 APP_EXCEPTION.RAISE_EXCEPTION;
933 end ADD_LANGUAGE;
934
935 procedure bulk_insert_rules (
936 X_TAX_RULE_ID IN t_tax_rule_id,
937 X_TAX_RULE_CODE IN t_tax_rule_code,
938 X_TAX IN t_tax,
939 X_TAX_REGIME_CODE IN t_tax_regime_code,
940 X_SERVICE_TYPE_CODE IN t_service_type_code,
941 X_RECOVERY_TYPE_CODE IN t_recovery_type_code,
942 X_PRIORITY IN t_priority,
943 X_System_Default_Flag IN t_system_default_flg,
944 X_EFFECTIVE_FROM IN t_effective_from,
945 X_EFFECTIVE_TO IN t_effective_to,
946 X_Record_Type_Code IN t_record_type,
947 X_TAX_RULE_NAME IN t_tax_rule_name,
948 X_TAX_RULE_DESC IN t_tax_rule_desc,
949 X_Enabled_Flag IN t_enabled_flg,
950 X_APPLICATION_ID IN t_application_id,
951 X_CONTENT_OWNER_ID IN t_content_owner_id,
952 X_DET_FACTOR_TEMPL_CODE IN t_det_factor_templ_code) is
953
954 begin
955 If x_tax_rule_id.count <> 0 then
956 forall i in x_tax_rule_id.first..x_tax_rule_id.last
957 INSERT INTO ZX_RULES_B (TAX_RULE_ID,
958 TAX_RULE_CODE,
959 TAX,
960 TAX_REGIME_CODE,
961 SERVICE_TYPE_CODE,
962 RECOVERY_TYPE_CODE,
963 PRIORITY,
964 System_Default_Flag,
965 EFFECTIVE_FROM,
966 EFFECTIVE_TO,
967 Record_Type_Code,
968 Enabled_Flag,
969 APPLICATION_ID,
970 CONTENT_OWNER_ID,
971 DET_FACTOR_TEMPL_CODE,
972 CREATED_BY ,
973 CREATION_DATE ,
974 LAST_UPDATED_BY ,
975 LAST_UPDATE_DATE ,
976 LAST_UPDATE_LOGIN ,
977 REQUEST_ID ,
978 PROGRAM_APPLICATION_ID ,
979 PROGRAM_ID ,
980 PROGRAM_LOGIN_ID)
981 VALUES (X_TAX_RULE_ID(i),
982 X_TAX_RULE_CODE(i),
983 X_TAX(i),
984 X_TAX_REGIME_CODE(i),
985 X_SERVICE_TYPE_CODE(i),
986 X_RECOVERY_TYPE_CODE(i),
987 X_PRIORITY(i),
988 NVL(X_System_Default_Flag(i),'N'),
989 X_EFFECTIVE_FROM(i),
990 X_EFFECTIVE_TO(i),
991 X_Record_Type_Code(i),
992 NVL(X_Enabled_Flag(i),'N'),
993 X_APPLICATION_ID(i),
994 X_CONTENT_OWNER_ID(i),
995 X_DET_FACTOR_TEMPL_CODE(i),
996 fnd_global.user_id ,
997 sysdate ,
998 fnd_global.user_id ,
999 sysdate ,
1000 fnd_global.conc_login_id ,
1001 fnd_global.conc_request_id ,
1002 fnd_global.prog_appl_id ,
1003 fnd_global.conc_program_id ,
1004 fnd_global.conc_login_id
1005 );
1006
1007 forall i in x_tax_rule_id.first..x_tax_rule_id.last
1008 INSERT INTO ZX_RULES_TL (TAX_RULE_ID,
1009 TAX_RULE_NAME,
1010 TAX_RULE_DESC,
1011 CREATED_BY ,
1012 CREATION_DATE ,
1013 LAST_UPDATED_BY ,
1014 LAST_UPDATE_DATE ,
1015 LAST_UPDATE_LOGIN ,
1016 LANGUAGE,
1017 SOURCE_LANG)
1018 select
1019 X_TAX_RULE_ID(i),
1020 X_TAX_RULE_NAME(i),
1021 X_TAX_RULE_DESC(i),
1022 fnd_global.user_id ,
1023 sysdate ,
1024 fnd_global.user_id ,
1025 sysdate ,
1026 fnd_global.conc_login_id ,
1027 L.LANGUAGE_CODE,
1028 userenv('LANG')
1029 from FND_LANGUAGES L
1030 where L.INSTALLED_FLAG in ('I', 'B')
1031 and not exists
1032 (select 1
1033 from ZX_RULES_TL T
1034 where T.TAX_RULE_ID = X_TAX_RULE_ID(i)
1035 and T.LANGUAGE = L.LANGUAGE_CODE);
1036 end if;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 APP_EXCEPTION.RAISE_EXCEPTION;
1041
1042 end bulk_insert_rules;
1043
1044 end ZX_RULES_PKG;