eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ZX_AR_FORMULA_MIG_PKG

Source


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