[Home] [Help]
PACKAGE BODY: APPS.ZX_AR_FORMULA_MIG_PKG
Source
1 PACKAGE BODY ZX_AR_FORMULA_MIG_PKG AS
2 /* $Header: zxarformulamigb.pls 120.16.12010000.1 2008/07/28 13:28:26 appldev ship $ */
3
4 PROCEDURE FORMULA_MIGRATION_MAIN
5 (x_return_status OUT NOCOPY VARCHAR2) IS
6 i number;
7
8 l_child_taxable_basis ar_vat_tax_all.taxable_basis%TYPE;
9 l_tax_code ar_vat_tax_all.tax_code%TYPE;
10 l_enabled_flag ar_vat_tax_all.enabled_flag%TYPE;
11 l_start_date ar_vat_tax_all.start_date%TYPE;
12 l_end_date ar_vat_tax_all.end_date%TYPE;
13 l_tax_constraint_id ar_vat_tax_all.tax_constraint_id%TYPE;
14
15 l_tax zx_rates_b.tax%TYPE;
16 l_tax_regime_code zx_rates_b.tax_regime_code%TYPE;
17
18 l_content_owner_id zx_tax_relations_t.content_owner_id%TYPE;
19 l_group_id zx_tax_relations_t.tax_group_id%TYPE;
20 l_tax_group_code zx_tax_relations_t.tax_group_code%TYPE;
21 l_parent_tax_code zx_tax_relations_t.parent_tax_code %TYPE;
22 l_parent_precedence zx_tax_relations_t.parent_precedence%TYPE;
23 l_parent_regime_code zx_tax_relations_t.parent_regime_code %TYPE;
24 l_child_tax_code zx_tax_relations_t.child_tax_code %TYPE;
25 l_child_precedence zx_tax_relations_t.child_precedence%TYPE;
26 l_child_regime_code zx_tax_relations_t.child_regime_code %TYPE;
27 l_branch zx_tax_relations_t.branch_flag%TYPE;
28
29 l_tax_condition_id ar_tax_group_codes_all.tax_condition_id%TYPE;
30 l_tax_exception_id ar_tax_group_codes_all.tax_exception_id%TYPE;
31 l_tax_group_id ar_tax_group_codes_all.tax_group_id%TYPE;
32
33
34 l_formula_code zx_formula_b.formula_code%TYPE;
35
36 l_discount_flag CHAR;
37 l_charge_flag CHAR;
38
39 l_alphanumeric_result zx_process_results.alphanumeric_result%TYPE;
40
41 g_group_id zx_tax_relations_t.tax_group_id%TYPE;
42 g_child_tax_code zx_tax_relations_t.child_tax_code%TYPE;
43 g_child_regime_code zx_tax_relations_t.child_regime_code%TYPE;
44
45 l_condition_grp_id zx_condition_groups_b.condition_group_id%TYPE;
46 l_condition_grp_cd zx_condition_groups_b.condition_group_code%TYPE;
47
48 x_msg_data VARCHAR2(30);
49
50
51
52 --(case 1 cur)-----------------------------------
53 -- Tax codes with taxable basis 'AFTER_EPD' or 'QUANTITY' and tax_class 'O' and which do not have tax_type = 'TAX_GROUP'
54 -- added join with zx_rates to fetch tax, tax_regime_code
55 -- which are passed to create_rules()
56 CURSOR get_tax_cur IS
57 SELECT vat.taxable_basis ,
58 vat.tax_code ,
59 vat.enabled_flag ,
60 vat.start_date ,
61 vat.end_date ,
62 rate.tax ,
63 rate.tax_regime_code ,
64 rate.content_owner_id
65 FROM ar_vat_tax_all vat, zx_rates_b rate
66 WHERE vat.taxable_basis IN ('AFTER_EPD', 'QUANTITY')
67 AND vat.tax_class = 'O'
68 AND vat.tax_type <> 'TAX_GROUP'
69 AND vat.vat_tax_id = NVL(rate.source_id, rate.tax_rate_id);
70
71
72
73
74
75 --(case 3 cur)-----------------------------------
76 -- Tax groups which have compounding precedence but do not have compounding branches
77 CURSOR get_taxgrp_cur IS
78 SELECT tax_rel_upg.tax_group_id ,
79 tax_rel_upg.tax_group_code ,
80 tax_rel_upg.parent_tax_code ,
81 tax_rel_upg.parent_precedence ,
82 tax_rel_upg.parent_regime_code ,
83 tax_rel_upg.child_tax_code ,
84 tax_rel_upg.child_precedence ,
85 tax_rel_upg.child_regime_code ,
86 tax_rel_upg.child_taxable_basis ,
87 tax_rel_upg.branch_flag ,
88 tax_rel_upg.content_owner_id ,
89 grp.enabled_flag , -- for create_formula() and create_rules()
90 grp.start_date , -- for create_rules
91 grp.end_date , -- for create_rules
92 grp.tax_condition_id ,
93 grp.tax_exception_id ,
94 grp.tax_group_id ,
95 zx_rate.tax_regime_code ,
96 zx_rate.tax
97 FROM zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate
98 WHERE tax_rel_upg.tax_group_id NOT IN (SELECT tax_group_id
99 FROM zx_tax_relations_t
100 WHERE TRUNC(child_precedence) <> child_precedence )
101 AND grp.tax_group_id = tax_rel_upg.tax_group_id
102 AND grp.tax_code_id = NVL(zx_rate.source_id, zx_rate.tax_rate_id)
103 AND grp.tax_code_id = tax_rel_upg.child_tax_code_id --* new condition added
104 ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;
105
106 --(case 4 cur)-----------------------------------
107 -- Tax groups which have compounding branches
108 CURSOR get_taxgrp_cmp_cur IS
109
110 SELECT tax_rel_upg.tax_group_id ,
111 tax_rel_upg.tax_group_code ,
112 tax_rel_upg.Parent_tax_code ,
113 tax_rel_upg.Parent_precedence ,
114 tax_rel_upg.Parent_regime_code ,
115 tax_rel_upg.Child_tax_code ,
116 tax_rel_upg.Child_precedence ,
117 tax_rel_upg.Child_regime_code ,
118 tax_rel_upg.Child_Taxable_basis ,
119 tax_rel_upg.branch_flag ,
120 tax_rel_upg.content_owner_id ,
121 grp.enabled_flag ,
122 grp.start_date ,
123 grp.end_date ,
124 grp.tax_condition_id ,
125 grp.tax_exception_id ,
126 grp.tax_group_id ,
127 zx_rate.tax_regime_code ,
128 zx_rate.tax
129 FROM zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate
130 WHERE tax_rel_upg.tax_group_id IN (SELECT tax_group_id
131 FROM zx_tax_relations_t
132 WHERE child_precedence > TRUNC(child_precedence) )
133 AND grp.tax_group_id = tax_rel_upg.tax_group_id
134 AND grp.tax_code_id = NVL(zx_rate.source_id, zx_rate.tax_rate_id)
135 AND grp.tax_code_id = tax_rel_upg.child_tax_code_id
136 ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;
137
138
139
140 /****************(dropped this hierarchical query)********************
141 SELECT group_id ,
142 tax_group_code ,
143 Parent_tax_code ,
144 Parent_precedence ,
145 Parent_regime_code ,
146 Child_tax_id ,
147 Child_tax_code ,
148 Child_precedence ,
149 Child_regime_code ,
150 Child_Taxable_basis ,
151 branch ,
152 content_owner_id
153 FROM zx_tax_relations_t
154 -- START WITH Parent_Regime_code IS NULL --* changed crsr
155 CONNECT BY PRIOR child_tax_code = parent_Tax_code
156 ORDER BY group_id, child_tax_code, child_precedence DESC;
157 */
158
159 -- for conflicting tax groups---------------------
160
161 BEGIN
162
163 arp_util_tax.debug('ZX_AR_FORMULA_MIG_PKG.FORMULA_MIGRATION_MAIN(+)');
164
165 -- Initialize API return status to success
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167
168 -- Case 1 Tax Codes in AR_VAT_TAX
169
170 OPEN get_tax_cur;
171 i := 0;
172 arp_util_tax.debug('CASE 1: open cursor');
173
174 LOOP
175
176 arp_util_tax.debug('i='||i);
177 i := i+1;
178
179 FETCH get_tax_cur INTO
180 l_child_taxable_basis ,
181 l_tax_code ,
182 l_enabled_flag ,
183 l_start_date ,
184 l_end_date ,
185 l_tax ,
186 l_tax_regime_code ,
187 l_content_owner_id ;
188
189
190 -- Check if the condition group code exists with condition group code as follows
191 -- if exists populate it in local variables
192
193 l_condition_grp_cd := NULL;
194 l_condition_grp_id := NULL;
195
196 arp_util_tax.debug('case1:l_child_taxable_basis:'||l_child_taxable_basis);
197 arp_util_tax.debug('case1:l_tax_code:'||l_tax_code);
198 arp_util_tax.debug('case1:l_tax:'||l_tax);
199 arp_util_tax.debug('case1:l_tax_regime_code:'||l_tax_regime_code);
200 arp_util_tax.debug('case1:l_content_owner_id:'||l_content_owner_id);
201
202
203 arp_util_tax.debug('CASE 1: before select');
204
205
206 BEGIN
207
208 SELECT condition_group_id, condition_group_code
209 INTO l_condition_grp_id, l_condition_grp_cd
210 FROM zx_condition_groups_b
211 WHERE condition_group_code = l_tax_code;
212
213
214 EXCEPTION WHEN OTHERS THEN
215 arp_util_tax.debug('Case1: error for tax '||l_tax_code||' Error:'||sqlerrm);
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 END;
218
219
220 arp_util_tax.debug('case1:l_condition_grp_id:'||l_condition_grp_id);
221 arp_util_tax.debug('case1:l_condition_grp_cd:'||l_condition_grp_cd);
222
223 IF l_child_taxable_basis = 'AFTER_EPD' THEN
224
225 l_alphanumeric_result := 'STANDARD_TB_DISCOUNT';
226
227 ELSIF l_child_taxable_basis = 'QUANTITY' THEN
228
229 l_alphanumeric_result := 'STANDARD_QUANTITY';
230
231 END IF;
232
233
234
235 IF (l_child_taxable_basis = 'AFTER_EPD' OR l_child_taxable_basis = 'QUANTITY')
236 AND (l_condition_grp_cd IS NOT NULL) THEN
237
238 arp_util_tax.debug('CASE 1: in if.before create_rules');
239
240 create_rules ( l_tax ,
241 l_tax_regime_code ,
242 l_start_date ,
243 l_end_date ,
244 l_enabled_flag ,
245 l_content_owner_id ,
246 l_condition_grp_cd ,
247 l_alphanumeric_result , --'STANDARD_TB_DISCOUNT' or 'STANDARD_QUANTITY'
248 l_condition_grp_id ,
249 NULL ,-- for tax_condition_id
250 NULL , -- for tax_exception_id
251 x_msg_data
252 );
253 arp_util_tax.debug('CASE 1:after create_rules');
254 END IF;
255
256 IF l_child_taxable_basis = 'PL/SQL formula' THEN
257
258 -- open issue
259 NULL;
260
261 END IF;
262
263 EXIT WHEN get_tax_cur%NOTFOUND;
264
265 END LOOP;
266
267 CLOSE get_tax_cur;
268
269
270 -- Case 2 Tax Groups, which have, null precedence
271 /**********************(This case is not required)***************/
272
273
274 -- Case 3 Tax groups, which have compounding precedence but do not have compounding branches
275 arp_util_tax.debug('ZX_AR_FORMULA_MIG_PKG.fORMULA_MIGRATION_MAIN()--> in CASE 3');
276 i:=0 ;
277 OPEN get_taxgrp_cur;
278
279 -- Assigning variable g_tax_group to null
280 g_group_id := NULL;
281
282 LOOP
283
284 FETCH get_taxgrp_cur INTO
285 l_group_id ,
286 l_tax_group_code ,
287 l_parent_tax_code ,
288 l_parent_precedence ,
289 l_parent_regime_code ,
290 l_child_tax_code ,
291 l_child_precedence ,
292 l_child_regime_code ,
293 l_child_taxable_basis ,
294 l_branch ,
295 l_content_owner_id ,
296 l_enabled_flag ,
297 l_start_date ,
298 l_end_date ,
299 l_tax_condition_id ,
300 l_tax_exception_id ,
301 l_tax_group_id ,
302 l_tax_regime_code ,
303 l_tax ;
304
305 --(for precedence 1, there is no need for formula, we can use the STANDARD_TB formula)
306 -- so working for precedence <> 1
307
308 arp_util_tax.debug('i='||i);
309 arp_util_tax.debug('l_group_id'||l_group_id);
310 arp_util_tax.debug('l_tax_group_code '||l_tax_group_code);
311 arp_util_tax.debug('l_parent_tax_code '||l_parent_tax_code);
312 arp_util_tax.debug('l_parent_precedence '||l_parent_precedence);
313 arp_util_tax.debug('l_parent_regime_code '||l_parent_regime_code);
314 arp_util_tax.debug('l_child_tax_code '||l_child_tax_code );
315 arp_util_tax.debug('l_child_precedence '||l_child_precedence );
316 arp_util_tax.debug('l_child_regime_code '||l_child_regime_code);
317 arp_util_tax.debug('l_child_taxable_basis '||l_child_taxable_basis);
318 arp_util_tax.debug('l_branch '||l_branch);
319 arp_util_tax.debug('l_enabled_flag '||l_enabled_flag);
320 arp_util_tax.debug('l_start_date '||l_start_date);
321 arp_util_tax.debug('l_end_date '||l_end_date);
322 arp_util_tax.debug('l_tax_condition_id '||l_tax_condition_id);
323 arp_util_tax.debug('l_tax_exception_id '||l_tax_exception_id);
324 arp_util_tax.debug('l_content_owner_id '||l_content_owner_id);
325 arp_util_tax.debug('l_tax_regime_code '||l_tax_regime_code);
326 arp_util_tax.debug('l_tax '||l_tax);
327 i := i+1;
328
329 IF l_child_precedence <> 1 THEN
330
331 SELECT DECODE( SIGN( LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
332 1,
333 SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S'),
334 l_tax_group_code||'_'||l_child_tax_code||'_TB')
335 INTO l_formula_code
336 FROM DUAL;
337
338 -- For width Issue (formula_code is VARCHAR2(30) only)
339
340 arp_util_tax.debug('case 3:l_formula_code:'||l_formula_code);
341
342 l_charge_flag := 'N';
343
344 IF l_child_taxable_basis = 'AFTER_EPD' THEN
345 -- If the taxable_basis is After Discount then set the
346 -- flag 'Discounts' in Formula header to Y.
347 l_discount_flag := 'Y';
348 ELSE
349
350 l_discount_flag := 'N';
351
352 END IF;
353
354 arp_util_tax.debug('case 3:before create_formula');
355
356 create_formula (l_child_taxable_basis , -- values can be equal to or <> to 'PRIOR_TAX'
357 l_formula_code ,
358 l_child_regime_code ,
359 l_child_tax_code ,
360 l_enabled_flag ,
361 l_discount_flag ,
362 l_charge_flag ,
363 l_parent_regime_code ,
364 l_parent_tax_code ,
365 l_group_id ,
366 l_content_owner_id ,
367 l_start_date ,
368 l_end_date ,
369 l_tax_regime_code ,
370 l_tax ,
371 x_msg_data
372 );
373
374 arp_util_tax.debug('case 3:after create_formula');
375
376 -- For creation of rules
377
378
382 l_condition_grp_cd := NULL;
379 --Check if the condition group code exists with condition group code as follows
380 -- if exists populate it in local variables
381
383 l_condition_grp_id := NULL;
384
385
386 BEGIN
387 arp_util_tax.debug('case 3:bef select');
388
389 SELECT vat.tax_constraint_id
390 INTO l_tax_constraint_id
391 FROM ar_vat_tax_all vat
392 WHERE vat.vat_tax_id = l_tax_group_id;
393
394 SELECT condition_group_id, condition_group_code
395 INTO l_condition_grp_id, l_condition_grp_cd
396 FROM zx_condition_groups_b
397 WHERE condition_group_code = l_tax_group_code|| decode(l_tax_constraint_id,NULL, '', '~'||l_tax_constraint_id);
398
399
400 EXCEPTION WHEN OTHERS THEN
401 arp_util_tax.debug('Case 3:error for tax '||l_child_tax_code||' error:'||sqlerrm);
402 x_return_status := FND_API.G_RET_STS_ERROR;
403
404 END;
405
406 -- result of taxable basis det is l_formula_code determined just before create_formula()
407 l_alphanumeric_result := l_formula_code;
408
409
410
411 IF l_condition_grp_cd IS NOT NULL THEN
412
413 arp_util_tax.debug('case 3:before create_rules ');
414 create_rules ( l_tax ,
415 l_tax_regime_code ,
416 l_start_date ,
417 l_end_date ,
418 l_enabled_flag ,
419 l_content_owner_id ,
420 l_condition_grp_cd ,
421 l_alphanumeric_result ,-- l_formula_code
422 l_condition_grp_id ,
423 l_tax_condition_id ,-- for tax_condition_id
424 l_tax_exception_id ,-- for tax_exception_id
425 x_msg_data
426 );
427 END IF;
428
429 arp_util_tax.debug('case 3: ');
430 END IF;
431 EXIT WHEN get_taxgrp_cur%NOTFOUND;
432 END LOOP;
433
434 CLOSE get_taxgrp_cur;
435
436
437
438
439
440
441
442 -- Case 4 Tax groups which have compounding branches
443
444
445 OPEN get_taxgrp_cmp_cur;
446
447 i:=0;
448 arp_util_tax.debug('case 4: IN CASE4');
449 -- Assigning variable g_group_id to null
450 g_group_id := NULL;
451 g_child_tax_code := NULL;
452 g_child_regime_code := NULL;
453
454 LOOP
455
456 arp_util_tax.debug('case 4: i='||i);
457 i := i+1;
458
459 FETCH get_taxgrp_cmp_cur INTO
460 l_group_id ,
461 l_tax_group_code ,
462 l_parent_tax_code ,
463 l_parent_precedence ,
464 l_parent_regime_code ,
465 l_child_tax_code ,
466 l_child_precedence ,
467 l_child_regime_code ,
468 l_child_taxable_basis ,
469 l_branch ,
470 l_content_owner_id ,
471 l_enabled_flag ,
472 l_start_date ,
473 l_end_date ,
474 l_tax_condition_id ,
475 l_tax_exception_id ,
476 l_tax_group_id ,
477 l_tax_regime_code ,
478 l_tax;
479
480
481
482 arp_util_tax.debug('case 4: l_group_id:'||l_group_id);
483 arp_util_tax.debug('case 4: l_child_tax_code:'||l_child_tax_code);
484 arp_util_tax.debug('case 4: l_child_regime_code:'||l_child_regime_code);
485 arp_util_tax.debug('case 4: l_child_precedence:'||l_child_precedence);
486
487
488
489 --Assigning value for new group
490 IF l_group_id <> g_group_id THEN
491 arp_util_tax.debug('case 4:in if g_group_id IS NULL->'||g_group_id);
492 g_group_id := l_group_id;
493 END IF;
494
495 arp_util_tax.debug('case 4:g_group_id:'||g_group_id);
496 arp_util_tax.debug('case 4:g_child_tax_code:'||g_child_tax_code);
497 arp_util_tax.debug('case 4:g_child_regime_code:'||g_child_regime_code);
498
499 IF (l_group_id = g_group_id) AND
500 (l_child_tax_code = g_child_tax_code) AND
501 (l_child_regime_code = g_child_regime_code) THEN
502
503 -- formula and formula details are already created for this childtax
504 -- when the previous row was processed. process the next row
505 arp_util_tax.debug('case 4: 1st if');
506 NULL;
507
508 -- added OR condition "g_group_id IS NULL" in ELSIF
509 -- for running the first record of cursor
510 ELSIF (l_group_id = g_group_id OR g_group_id IS NULL)
511 AND ((l_child_tax_code <> g_child_tax_code)
512 OR (g_child_tax_code IS NULL AND g_child_regime_code IS NULL)) THEN
513
514 -- If taxgroup is the same as previous taxgroup but child tax and child regime is different
515 -- from previous childtax. hence create a formula header
516
517 arp_util_tax.debug('case 4: 2nd if');
518
519 l_condition_grp_cd := NULL;
520 l_condition_grp_id := NULL;
521
522
523 BEGIN
524
525 arp_util_tax.debug('case 4:before select of cond grp');
526
527 SELECT vat.tax_constraint_id
528 INTO l_tax_constraint_id
529 FROM ar_vat_tax_all vat
530 WHERE vat.vat_tax_id = l_tax_group_id;
531
532 SELECT condition_group_id, condition_group_code
533 INTO l_condition_grp_id, l_condition_grp_cd
534 FROM zx_condition_groups_b
535 WHERE condition_group_code = l_tax_group_code|| decode(l_tax_constraint_id,NULL, '', '~'||l_tax_constraint_id);
536
537 EXCEPTION WHEN OTHERS THEN
541 END;
538 arp_util_tax.debug('Case 4:error for tax '||l_child_tax_code||' error:'||sqlerrm);
539 x_return_status := FND_API.G_RET_STS_ERROR;
540
542
543 arp_util_tax.debug('Case 4:l_condition_grp_id->'||l_condition_grp_id);
544 arp_util_tax.debug('Case 4:l_condition_grp_cd->'||l_condition_grp_cd);
545
546 IF l_child_taxable_basis = 'PRIOR_TAX' THEN
547 -- the taxable_basis_type in the formula will be prior_tax and
548 -- there will be only one record in the Formula Details
549 -- which will be that of the ParentRegime / ParentTax.
550
551 SELECT DECODE(SIGN(LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
552 1,
553 SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S' ),
554 l_tax_group_code||'_'||l_child_tax_code||'_TB')
555 INTO l_formula_code
556 FROM DUAL;
557
558 arp_util_tax.debug('case 4: l_formula_code1'||l_formula_code);
559
560
561 l_discount_flag := 'N';
562 l_charge_flag := 'N';
563
564 arp_util_tax.debug('Case 4:before create_formula');
565
566 create_formula (l_child_taxable_basis ,
567 l_formula_code ,
568 l_child_regime_code ,
569 l_child_tax_code ,
570 l_enabled_flag ,
571 l_discount_flag ,
572 l_charge_flag ,
573 l_parent_regime_code ,
574 l_parent_tax_code ,
575 l_group_id ,
576 l_content_owner_id ,
577 l_start_date ,
578 l_end_date ,
579 l_tax_regime_code ,
580 l_tax ,
581 x_msg_data
582 );
583
584 arp_util_tax.debug('Case 4:after create_formula');
585
586 l_alphanumeric_result := l_formula_code;
587
588 IF l_condition_grp_cd IS NOT NULL THEN
589
590 arp_util_tax.debug('Case 4:before create_rules');
591
592 create_rules ( l_tax ,
593 l_tax_regime_code ,
594 l_start_date ,
595 l_end_date ,
596 l_enabled_flag ,
597 l_content_owner_id ,
598 l_condition_grp_cd ,
599 l_alphanumeric_result ,-- l_formula_code
600 l_condition_grp_id ,
601 l_tax_condition_id ,-- for tax_condition_id
602 l_tax_exception_id ,-- for tax_exception_id
603 x_msg_data
604 );
605 END IF;
606
607
608 arp_util_tax.debug('Case 4:after create_rules');
609
610 ELSIF l_child_taxable_basis <> 'PRIOR_TAX' THEN
611 -- then create formula header with taxable basis type as LineAmount.
612 -- INSERT INTO zx_formula_b with taxable_basis_type = 'LINE_AMOUNT';
613 -- it is in decode of create_formula()
614
615 SELECT DECODE(SIGN(LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
616 1,
617 SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S' ),
618 l_tax_group_code||'_'||l_child_tax_code||'_TB')
619 INTO l_formula_code
620 FROM DUAL;
621
622 l_discount_flag := 'N';
623 l_charge_flag := 'N';
624
625 arp_util_tax.debug('case 4: l_formula_code2'||l_formula_code);
626
627 IF l_child_taxable_basis = 'AFTER_EPD' THEN
628
629 -- If the taxable_basis is After Discount then set the
630 -- flag 'Discounts' in Formula header to Y.
631
632 l_discount_flag := 'Y';
633
634
635 END IF; -- end l_taxable_basis = 'AFTER_EPD'
636
637
638 arp_util_tax.debug('Case 4:before create_formula');
639 create_formula (l_child_taxable_basis ,
640 l_formula_code ,
641 l_child_regime_code ,
642 l_child_tax_code ,
643 l_enabled_flag ,
644 l_discount_flag ,
645 l_charge_flag ,
646 l_parent_regime_code ,
647 l_parent_tax_code ,
648 l_group_id ,
649 l_content_owner_id ,
650 l_start_date ,
651 l_end_date ,
652 l_tax_regime_code ,
653 l_tax ,
654 x_msg_data
655 );
656
657
658 arp_util_tax.debug('Case 4:after create_formula');
659
660
661
662
663 l_alphanumeric_result := l_formula_code;
664
665 IF l_condition_grp_cd IS NOT NULL THEN
666
667 arp_util_tax.debug('Case 4:before create_rules');
668
669 create_rules ( l_tax ,
670 l_tax_regime_code ,
671 l_start_date ,
672 l_end_date ,
673 l_enabled_flag ,
674 l_content_owner_id ,
675 l_condition_grp_cd ,
676 l_alphanumeric_result ,-- l_formula_code
677 l_condition_grp_id ,
678 l_tax_condition_id ,-- for tax_condition_id
679 l_tax_exception_id ,-- for tax_exception_id
680 x_msg_data
681 );
682 END IF;
683
684 arp_util_tax.debug('Case 4:after create_rules');
685 END IF; --end l_taxable_basis = 'PRIOR_TAX'
686
687 arp_util_tax.debug('Case 4:aftr end if of --> l_taxable_basis ');
688
689 END IF;
690
691 -- Assigning values to variables
692 g_group_id := l_group_id;
693 g_child_tax_code := l_child_tax_code;
694 g_child_regime_code := l_child_regime_code;
695
699 END LOOP; -- end Main Loop
696 EXIT WHEN get_taxgrp_cmp_cur%NOTFOUND;
697
698
700
701 CLOSE get_taxgrp_cmp_cur;
702
703 arp_util_tax.debug('LAST');
704
705 EXCEPTION WHEN OTHERS THEN
706
707 x_return_status := FND_API.G_RET_STS_ERROR;
708
709 END FORMULA_MIGRATION_MAIN;
710
711
712 /****************************************************************************************
713 * Procedure: CREATE_FORMULA *
714 * Based on the input parameters, row(s) would be created in to , *
715 * ZX_FORMULA_TL and ZX_FORMULA_DETAILS. *
716 *****************************************************************************************/
717
718
719 PROCEDURE CREATE_FORMULA
720 (--for zx_formula_b
721 p_taxable_basis IN ar_vat_tax_all.taxable_basis%TYPE,
722 p_formula_code IN zx_formula_b.formula_code%TYPE,
723 p_child_regime_code IN zx_tax_relations_t.child_regime_code %TYPE,
724 p_child_tax_code IN zx_tax_relations_t.child_tax_code %TYPE,
725 p_enabled_flag IN ar_vat_tax_all.enabled_flag%TYPE,
726 p_discount_flag IN CHAR,
727 p_charge_flag IN CHAR,
728 -- for zx_formula_details
729 p_parent_regime_code IN zx_tax_relations_t.parent_regime_code %TYPE,
730 p_parent_tax_code IN zx_tax_relations_t.parent_tax_code %TYPE,
731 p_group_id IN zx_tax_relations_t.tax_group_id%TYPE, --(used in where condition)
732 p_content_owner_id IN zx_rates_b.content_owner_id%TYPE,
733 p_start_date IN ar_vat_tax_all.start_date%TYPE,
734 p_end_date IN ar_vat_tax_all.end_date%TYPE,
735 p_tax_regime_code IN zx_rates_b.tax_regime_code %TYPE,
736 p_tax_code IN zx_rates_b.tax %TYPE,
737 x_return_status OUT NOCOPY VARCHAR2) IS
738
739 l_formula_id zx_formula_b.formula_id%TYPE;
740
741 BEGIN
742
743 -- Inserting values in table zx_formula_b
744
745 arp_util_tax.debug('in create_formula() for : '||p_tax_code);
746 BEGIN
747 --bug# 4610260 : Changed the ZX_FORMULA_B_TMP to ZX_FORMULA_B
748 INSERT INTO zx_formula_b_tmp (
749 formula_type_code ,
750 formula_code ,
751 tax_regime_code ,
752 tax ,
753 effective_from ,
754 effective_to ,
755 enabled_flag ,
756 taxable_basis_type_code ,
757 record_type_code ,
758 base_rate_modifier ,
759 cash_discount_appl_flag ,
760 volume_discount_appl_flag ,
761 trading_discount_appl_flag ,
762 transfer_charge_appl_flag ,
763 transport_charge_appl_flag ,
764 insurance_charge_appl_flag ,
765 other_charge_appl_flag ,
766 formula_id ,
767 content_owner_id ,
768 created_by ,
769 creation_date ,
770 last_updated_by ,
771 last_update_date ,
772 last_update_login ,
773 request_id ,
774 program_application_id ,
775 program_id ,
776 program_login_id ,
777 object_version_number)
778 SELECT
779 'TAXABLE_BASIS' ,
780 p_formula_code , --tax_group_code||'_'||tax_code||'_TB'
781 p_tax_regime_code ,
782 p_tax_code ,
783 p_start_date ,
784 p_end_date ,
785 p_enabled_flag ,
786 DECODE(p_taxable_basis,'PRIOR_TAX','PRIOR_TAX','LINE_AMOUNT'), --Bug Fix 5691957
787 'MIGRATED' ,
788 0 ,-- bug6718736
789 DECODE(p_taxable_basis, 'AFTER_EPD','Y','N'),
790 p_discount_flag ,
791 p_discount_flag ,
792 p_charge_flag ,
793 p_charge_flag ,
794 p_charge_flag ,
795 p_charge_flag ,
796 zx_formula_b_s.NEXTVAL l_formula_id,
797 p_content_owner_id ,
798 fnd_global.user_id ,
799 SYSDATE ,
800 fnd_global.user_id ,
801 SYSDATE ,
802 fnd_global.conc_login_id ,
803 fnd_global.conc_request_id ,--Request Id
804 fnd_global.prog_appl_id ,--Program Application ID
805 fnd_global.conc_program_id ,--Program Id
806 fnd_global.conc_login_id ,--Program Login ID
807 1
808 FROM DUAL
809 WHERE
810 --Re-runnability
811 NOT EXISTS ( SELECT 1
812 FROM zx_formula_b
813 WHERE SUBSTRB(formula_code,1,24) = SUBSTRB(p_formula_code,1,24)
814 AND (effective_from BETWEEN
815 p_start_date and nvl(p_end_date,SYSDATE)
816 OR
817 NVL(effective_to,sysdate) BETWEEN
818 p_start_date and nvl(p_end_date,sysdate)
819 )
820 AND enabled_flag = 'Y'
821 );
822
823 EXCEPTION WHEN OTHERS THEN
824 arp_util_tax.debug('In create_formula().formula_b: error-'||sqlerrm);
825 x_return_status := FND_API.G_RET_STS_ERROR;
826
827 END ;
828
829 -- Inserting values in table zx_formula_tl
830 BEGIN
831
832 INSERT INTO zx_formula_tl (
833 formula_id ,
834 formula_name ,
835 created_by ,
836 creation_date ,
837 last_updated_by ,
838 last_update_date ,
839 last_update_login ,
840 language ,
841 source_lang
842 )
843 SELECT
844 formula_id ,
845 CASE WHEN formula_code = UPPER(formula_code)
846 THEN Initcap(formula_code)
850 fnd_global.user_id ,
847 ELSE
848 formula_code
849 END,
851 SYSDATE ,
852 fnd_global.user_id ,
853 SYSDATE ,
854 fnd_global.conc_login_id,
855 l.language_code ,
856 userenv('LANG')
857 FROM fnd_languages l, zx_formula_b formula
858 WHERE l.installed_flag IN ('I', 'B')
859 AND formula.record_type_code = 'MIGRATED'
860 AND formula.formula_code = p_formula_code
861 AND NOT EXISTS(SELECT NULL
862 FROM zx_formula_tl t
863 WHERE t.formula_id = formula.formula_id
864 AND t.language = l.language_code);
865
866 EXCEPTION WHEN OTHERS THEN
867 arp_util_tax.debug('In create_formula().formula_tl: error-'||sqlerrm);
868 x_return_status := FND_API.G_RET_STS_ERROR;
869
870 END;
871
872
873 -- Inserting values in table zx_formula_details
874
875 arp_util_tax.debug('l_formula_id:'||l_formula_id);
876 IF p_taxable_basis = 'PRIOR_TAX' THEN
877
878 BEGIN
879
880 INSERT INTO zx_formula_details (
881 formula_detail_id ,
882 formula_id ,
883 compounding_tax_regime_code ,
884 compounding_tax ,
885 compounding_type_code ,
886 record_type_code ,
887 creation_date ,
888 last_update_date ,
889 created_by ,
890 last_updated_by ,
891 last_update_login ,
892 request_id ,
893 program_application_id ,
894 program_id ,
895 program_login_id ,
896 object_version_number
897 )
898 SELECT
899 zx_formula_details_s.NEXTVAL ,
900 zx_formula_b_s.CURRVAL ,
901 p_parent_regime_code ,
902 p_parent_tax_code ,
903 'ADD' ,
904 'MIGRATED' ,
905 SYSDATE ,
906 SYSDATE ,
907 fnd_global.user_id ,
908 fnd_global.user_id ,
909 fnd_global.conc_login_id ,
910 fnd_global.conc_request_id ,--Request Id
911 fnd_global.prog_appl_id ,--Program Application ID
912 fnd_global.conc_program_id ,--Program Id
913 fnd_global.conc_login_id ,--Program Login ID
914 1
915
916 FROM DUAL
917 WHERE
918 --Re-runnability
919 NOT EXISTS (SELECT 1
920 FROM zx_formula_details, zx_formula_b
921 WHERE zx_formula_details.formula_id = zx_formula_b.formula_id
922 AND compounding_tax_regime_code = p_parent_regime_code
923 AND compounding_tax = p_parent_tax_code);
924
925 EXCEPTION WHEN OTHERS THEN
926 arp_util_tax.debug('In create_formula().formula_details: error-'||sqlerrm);
927
928 x_return_status := FND_API.G_RET_STS_ERROR;
929
930 END;
931
932
933
934 ELSIF p_taxable_basis <> 'PRIOR_TAX' THEN
935
936 BEGIN
937 INSERT INTO zx_formula_details (
938 formula_detail_id ,
939 formula_id ,
940 compounding_tax ,
941 compounding_tax_regime_code ,
942 compounding_type_code ,
943 record_type_code ,
944 creation_date ,
945 last_update_date ,
946 created_by ,
947 last_updated_by ,
948 last_update_login ,
949 request_id ,
950 program_application_id ,
951 program_id ,
952 program_login_id ,
953 object_version_number
954 )
955 SELECT zx_formula_details_s.NEXTVAL ,
956 zx_formula_b_s.CURRVAL ,
957 parent_tax_code ,
958 parent_regime_code ,
959 'ADD' ,
960 'MIGRATED' ,
961 SYSDATE ,
962 SYSDATE ,
963 fnd_global.user_id ,
964 fnd_global.user_id ,
965 fnd_global.conc_login_id ,
966 fnd_global.conc_request_id ,--Request Id
967 fnd_global.prog_appl_id ,--Program Application ID
968 fnd_global.conc_program_id ,--Program Id
969 fnd_global.conc_login_id ,--Program Login ID
970 1
971 FROM zx_tax_relations_t t
972 WHERE t.child_regime_code = p_child_regime_code
973 AND t.child_tax_code = p_child_tax_code
974 AND t.tax_group_id = p_group_id
975 AND NOT EXISTS (SELECT 1
976 FROM zx_formula_details, zx_formula_b
977 WHERE zx_formula_details.formula_id = zx_formula_b.formula_id
978 AND compounding_tax_regime_code = p_parent_regime_code
979 AND compounding_tax = p_parent_tax_code
980 AND
981 contains(zx_formula_b.formula_code, t.tax_group_code) > 0
982 )
983 and rownum = 1;
984
985 -- the above contains clause is introduced as part of 6718736
986
987 EXCEPTION WHEN OTHERS THEN
988 arp_util_tax.debug('In create_formula().formula_b: error-'||sqlerrm);
989 x_return_status := FND_API.G_RET_STS_ERROR;
990
991 END;
992
993 END IF;
994
995 END CREATE_FORMULA;
996
997 /****************************************************************************************
998 * Procedure: CREATE_RULES *
999 * Based on the input parameters, row(s) would be created in to ZX_RULES_B, *
1003 PROCEDURE CREATE_RULES
1000 * ZX_RULES_TL and ZX_PROCESS_RESULTS. *
1001 *****************************************************************************************/
1002
1004 ( --parameters rqrd for zx_rules_b
1005 p_tax IN zx_rates_b.tax%TYPE ,
1006 p_tax_regime_code IN zx_rates_b.tax_regime_code%TYPE ,
1007 p_effective_from IN ar_vat_tax_all.start_date%TYPE ,
1008 p_effective_to IN ar_vat_tax_all.end_date%TYPE ,
1009 p_enabled_flag IN ar_tax_group_codes.enabled_flag%TYPE ,
1010 p_content_owner_id IN zx_rates_b.content_owner_id%TYPE, -- for zx_process_results
1011 p_condition_grp_cd IN fnd_lookups.lookup_code%TYPE ,
1012 p_alphanumeric_result IN zx_process_results.alphanumeric_result%TYPE,
1013 p_condition_group_id IN zx_condition_groups_b.condition_group_id%TYPE,
1014 p_tax_condition_id IN ar_tax_group_codes_all.tax_condition_id%TYPE,
1015 p_tax_exception_id IN ar_tax_group_codes_all.tax_exception_id%TYPE,
1016 x_return_status OUT NOCOPY VARCHAR2 ) IS
1017
1018 --l_tax_rule_id zx_rules_b.tax_rule_id%TYPE;
1019 l_tax_rule_code zx_rules_b.tax_rule_code%TYPE;
1020
1021 BEGIN
1022
1023 arp_util_tax.debug('in create_rules() for : '||p_tax);
1024
1025
1026
1027
1028 SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
1029 1,
1030 SUBSTRB('O_TB_' || p_tax,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_RULES_B_S'),
1031 'O_TB_' || p_tax)
1032 INTO l_tax_rule_code
1033 FROM DUAL;
1034
1035 arp_util_tax.debug('l_tax_rule_code'||l_tax_rule_code);
1036
1037 -- Inserting values in table zx_rules_b
1038
1039 BEGIN
1040 --bug# 4610260 : Changed the ZX_RULES_B to ZX_RULES_B_TMP
1041 INSERT INTO zx_rules_b_tmp
1042 (tax_rule_code ,
1043 tax ,
1044 tax_regime_code ,
1045 service_type_code ,
1046 application_id ,
1047 recovery_type_code ,
1048 priority ,
1049 system_default_flag ,
1050 effective_from ,
1051 effective_to ,
1052 enabled_flag ,
1053 record_type_code ,
1054 det_factor_templ_code ,
1055 content_owner_id ,
1056 tax_rule_id ,
1057 created_by ,
1058 creation_date ,
1059 last_updated_by ,
1060 last_update_date ,
1061 last_update_login ,
1062 request_id ,
1063 program_application_id,
1064 program_id ,
1065 program_login_id ,
1066 object_version_number)
1067
1068 SELECT
1069 l_tax_rule_code ,
1070 p_tax ,
1071 p_tax_regime_code ,
1072 'DET_TAXABLE_BASIS' ,
1073 NULL ,
1074 NULL ,
1075 1 ,
1076 'N' , -- Bug 4590290
1077 p_effective_from ,
1078 p_effective_to ,
1079 p_enabled_flag ,
1080 'MIGRATED' ,
1081 'STCC' ,
1082 p_content_owner_id ,
1083 zx_rules_b_s.NEXTVAL ,
1084 fnd_global.user_id ,
1085 SYSDATE ,
1086 fnd_global.user_id ,
1087 SYSDATE ,
1088 fnd_global.conc_login_id,
1089 fnd_global.conc_request_id ,--Request Id
1090 fnd_global.prog_appl_id ,--Program Application ID
1091 fnd_global.conc_program_id ,--Program Id
1092 fnd_global.conc_login_id ,--Program Login ID
1093 1
1094 FROM DUAL
1095 WHERE
1096 --Re-runnability
1097
1098 NOT EXISTS (SELECT 1
1099 FROM zx_rules_B
1100 WHERE substrb(tax_rule_code,1,24) = (SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
1101 1,
1102 SUBSTRB('O_TB_' || p_tax,1,24),
1103 SUBSTRB('O_TB_' || p_tax,1,24))
1104 FROM DUAL)
1105 AND content_owner_id = p_content_owner_id
1106 AND tax_regime_code = p_tax_regime_code
1107 AND (effective_from BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
1108 OR
1109 NVL(effective_to,SYSDATE) BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
1110 )
1111 AND enabled_flag = 'Y'
1112 );
1113
1114 EXCEPTION WHEN OTHERS THEN
1115 arp_util_tax.debug('In create_rules().rules_b: error-'||sqlerrm);
1116 x_return_status := FND_API.G_RET_STS_ERROR;
1117
1118 END;
1119
1120 -- Inserting values in table zx_rules_tl
1121 BEGIN
1122 INSERT INTO zx_rules_tl (
1123 tax_rule_id ,
1124 tax_rule_name ,
1125 created_by ,
1126 creation_date ,
1127 last_updated_by ,
1128 last_update_date ,
1129 last_update_login ,
1130 language ,
1131 source_lang
1132
1133 )
1134 SELECT
1135 tax_rule_id ,
1136 CASE WHEN tax_rule_code = UPPER(tax_rule_code)
1137 THEN Initcap(tax_rule_code)
1138 ELSE
1139 tax_rule_code
1140 END,
1141 fnd_global.user_id ,
1142 SYSDATE ,
1143 fnd_global.user_id ,
1144 SYSDATE ,
1145 fnd_global.conc_login_id,
1146 l.language_code ,
1147 userenv('LANG')
1148 FROM fnd_languages l, zx_rules_b rules
1149 WHERE l.installed_flag IN ('I', 'B')
1150 AND rules.record_type_code = 'MIGRATED'
1151 AND rules.tax_rule_code = l_tax_rule_code
1152 AND NOT EXISTS (SELECT NULL
1153 FROM zx_rules_tl t
1154 WHERE t.tax_rule_id = rules.tax_rule_id
1158 arp_util_tax.debug('In create_rules().rules_tl: error-'||sqlerrm);
1155 AND t.language = l.language_code);
1156
1157 EXCEPTION WHEN OTHERS THEN
1159 x_return_status := FND_API.G_RET_STS_ERROR;
1160
1161 END;
1162
1163 -- Inserting values in table zx_process_results
1164
1165 BEGIN
1166
1167 INSERT INTO zx_process_results
1168 (condition_group_code ,
1169 priority ,
1170 result_type_code ,
1171 tax_status_code ,
1172 numeric_result ,
1173 alphanumeric_result ,
1174 status_result ,
1175 rate_result ,
1176 legal_message_code ,
1177 min_tax_amt ,
1178 max_tax_amt ,
1179 min_taxable_basis ,
1180 max_taxable_basis ,
1181 min_tax_rate ,
1182 max_tax_rate ,
1183 enabled_flag ,
1184 allow_exemptions_flag ,
1185 allow_exceptions_flag ,
1186 record_type_code ,
1187 result_api ,
1188 result_id ,
1189 content_owner_id ,
1190 condition_group_id ,
1191 tax_rule_id ,
1192 condition_set_id ,
1193 exception_set_id ,
1194 created_by ,
1195 creation_date ,
1196 last_updated_by ,
1197 last_update_date ,
1198 last_update_login ,
1199 request_id ,
1200 program_application_id ,
1201 program_id ,
1202 program_login_id ,
1203 object_version_number)
1204 SELECT
1205 p_condition_grp_cd ,
1206 1 ,
1207 'CODE' ,
1208 NULL ,
1209 NULL ,
1210 p_alphanumeric_result ,-- STANDARD_TB_DISCOUNT,STANDARD_QUANTITY or TAX_GROUP_CODE||'_'||TAX_CODE||'_TB'
1211 NULL ,
1212 NULL ,
1213 NULL ,
1214 NULL ,
1215 NULL ,
1216 NULL ,
1217 NULL ,
1218 NULL ,
1219 NULL ,
1220 p_enabled_flag , -- also used in insert of zx_rules_b
1221 'N' ,
1222 'N' ,
1223 'MIGRATED' ,
1224 NULL ,
1225 zx_process_results_s.NEXTVAL ,
1226 p_content_owner_id , -- also used in insert of zx_rules_b also
1227 p_condition_group_id ,
1228 zx_rules_b_s.CURRVAL , -- zx_rules_b.tax_rule_id(based on rule created above)
1229 p_tax_condition_id , --condition set id
1230 p_tax_exception_id , --exception set id
1231 fnd_global.user_id ,
1232 SYSDATE ,
1233 fnd_global.user_id ,
1234 SYSDATE ,
1235 fnd_global.conc_login_id ,
1236 fnd_global.conc_request_id ,--Request Id
1237 fnd_global.prog_appl_id ,--Program Application ID
1238 fnd_global.conc_program_id ,--Program Id
1239 fnd_global.conc_login_id ,--Program Login ID
1240 1
1241
1242 FROM dual
1243 WHERE
1244 --Re-runnability
1245 NOT EXISTS (SELECT 1
1246 FROM zx_process_results, zx_rules_b
1247 WHERE zx_process_results.tax_rule_id = zx_rules_b.tax_rule_id
1248 AND zx_process_results.content_owner_id = p_content_owner_id
1249 AND zx_process_results.condition_group_code = p_condition_grp_cd
1250 AND zx_process_results.alphanumeric_result = p_alphanumeric_result
1251 );
1252
1253
1254 EXCEPTION WHEN OTHERS THEN
1255 arp_util_tax.debug('In create_rules().process_results: error-'||sqlerrm);
1256 x_return_status := FND_API.G_RET_STS_ERROR;
1257
1258 END;
1259
1260 END CREATE_RULES;
1261
1262 END ZX_AR_FORMULA_MIG_PKG;