[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_COMPOUND_PKG
Source
1 PACKAGE BODY zx_tcm_compound_pkg AS
2 /* $Header: zxtaxgroupmigb.pls 120.23 2011/04/15 20:35:59 ssanka ship $ */
3 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
4 L_ORG_ID NUMBER(15);
5 PROCEDURE load_tax_relations IS
6 BEGIN
7
8 INSERT ALL
9 -- branch
10 -- check for same regime, it could be two diff intos
11 -- check if both are branches
12 WHEN (same_branch='Y') THEN
13 INTO zx_tax_relations_t
14 (parent_tax_code_id ,
15 parent_tax_code ,
16 parent_regime_code ,
17 parent_precedence ,
18 child_tax_code_id ,
19 child_tax_code,
20 child_regime_code ,
21 child_precedence ,
22 branch_flag ,
23 tax_group_id,
24 tax_group_code,
25 parent_taxable_basis,
26 child_taxable_basis,
27 content_owner_id,
28 parent_tax,
29 child_tax,
30 org_id,
31 created_by,
32 creation_date,
33 last_updated_by,
34 last_update_date
35 )
36 VALUES (
37 parent_tax_code_id,
38 parent_tax_code,
39 parent_regime_code,
40 parent_precedence,
41 child_tax_code_id,
42 child_tax_code,
43 child_regime_code,
44 child_precedence,
45 branch_flag,
46 rel_tax_group_id,
47 tax_group_code,
48 parent_taxable_basis,
49 child_taxable_basis,
50 content_owner_id,
51 parent_tax,
52 child_tax,
53 org_id,
54 created_by,
55 creation_date,
56 last_updated_by,
57 last_update_date
58 )
59 -- no branch but compunded
60 -- group with no tax decimal precedence
61 WHEN (same_branch='N' AND
62 NOT EXISTS ( SELECT 'Y'
63 FROM ar_tax_group_codes_all group_compound
64 WHERE group_compound.tax_group_id = rel_tax_group_id
65 AND trunc (group_compound.compounding_precedence) <> group_compound.compounding_precedence
66 )
67 ) THEN
68 INTO zx_tax_relations_t
69 (parent_tax_code_id ,
70 parent_tax_code ,
71 parent_regime_code ,
72 parent_precedence ,
73 child_tax_code_id ,
74 child_tax_code,
75 child_regime_code ,
76 child_precedence ,
77 branch_flag ,
78 tax_group_id,
79 tax_group_code,
80 parent_taxable_basis,
81 child_taxable_basis,
82 content_owner_id,
83 parent_tax,
84 child_tax,
85 org_id,
86 created_by,
87 creation_date,
88 last_updated_by,
89 last_update_date
90 )
91 VALUES (
92 parent_tax_code_id,
93 parent_tax_code,
94 parent_regime_code,
95 parent_precedence,
96 child_tax_code_id,
97 child_tax_code,
98 child_regime_code,
99 child_precedence,
100 branch_flag,
101 rel_tax_group_id,
102 tax_group_code,
103 parent_taxable_basis,
104 child_taxable_basis,
105 content_owner_id,
106 parent_tax,
107 child_tax,
108 org_id,
109 created_by,
110 creation_date,
111 last_updated_by,
112 last_update_date)
113 SELECT a.tax_group_code_id group_code_id,
114 a.tax_group_id rel_tax_group_id,
115 grp.tax_code tax_group_code,
116 a.tax_code_id parent_tax_code_id,
117 b.tax_group_code_id child_group_code,
118 b.tax_code_id child_tax_code_id,
119 CASE WHEN (b.compounding_precedence <> trunc(b.compounding_precedence) ) THEN 'Y'
120 WHEN (a.compounding_precedence <> trunc(a.compounding_precedence) ) THEN 'Y'
121 ELSE 'N'
122 END branch_flag,
123 CASE WHEN b.compounding_precedence <> trunc(b.compounding_precedence) THEN
124 CASE WHEN (trunc(a.compounding_precedence) <> trunc(b.compounding_precedence)) THEN
125 'N'
126 ELSE 'Y'
127 END
128
129 WHEN a.compounding_precedence <> trunc(a.compounding_precedence) THEN
130 CASE WHEN (trunc(a.compounding_precedence) <> trunc(b.compounding_precedence)) THEN
131 'N'
132 ELSE 'Y'
133 END
134 ELSE 'N'
135 END same_branch,
136 a.compounding_precedence parent_precedence,
137 b.compounding_precedence child_precedence,
138 CASE WHEN (b.compounding_precedence > a.compounding_precedence) THEN
139 a.tax_code_id
140 END parent_code_id,
141 bb.tax_code child_tax_code,
142 aa.tax_code parent_tax_code,
143 --rega.tax_type parent_regime_code,
144 rega.tax_regime_code parent_regime_code, --Bug 5691957
145
146 --regb.tax_type child_regime_code,
147 regb.tax_regime_code child_regime_code, --Bug 5691957
148
149 aa.taxable_basis parent_taxable_basis,
150 bb.taxable_basis child_taxable_basis,
151 ptp.party_tax_profile_id content_owner_id,
152 aa.tax_code parent_tax,
153 bb.tax_code child_tax,
154 decode(l_multi_org_flag,'N',l_org_id,a.org_id) org_id,
155 fnd_global.user_id created_by,
156 sysdate creation_date ,
157 fnd_global.user_id last_updated_by,
158 sysdate last_update_date
159 FROM ar_tax_group_codes_all a,
160 ar_tax_group_codes_all b,
161 ar_vat_tax_all_b aa,
162 ar_vat_tax_all_b bb,
163 zx_tax_priorities_t rega,
164 zx_tax_priorities_t regb,
165 zx_party_tax_profile ptp,
166 ar_vat_tax_all_b grp
167 WHERE decode(l_multi_org_flag,'N',l_org_id,a.org_id) = decode(l_multi_org_flag,'N',l_org_id,b.org_id)
168 AND decode(l_multi_org_flag,'N',l_org_id,a.org_id) = decode(l_multi_org_flag,'N',l_org_id,aa.org_id)
169 AND decode(l_multi_org_flag,'N',l_org_id,b.org_id) = decode(l_multi_org_flag,'N',l_org_id,bb.org_id)
170 AND a.tax_group_id=b.tax_group_id
171 AND (b.compounding_precedence > a.compounding_precedence)
172 AND ptp.party_type_code ='OU'
173 AND ptp.party_id=decode(l_multi_org_flag,'N',l_org_id,aa.org_id)
174 -- lookup condition to get tax info
175 AND aa.vat_tax_id = a.tax_code_id
176 AND bb.vat_tax_id = b.tax_code_id
177 AND aa.tax_type = rega.tax_type
178 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = rega.org_id
179 AND bb.tax_type = regb.tax_type
180 AND decode(l_multi_org_flag,'N',l_org_id,bb.org_id) = regb.org_id
181 AND rega.regime_or_tax_flag ='R'
182 AND regb.regime_or_tax_flag ='R'
183 AND decode(l_multi_org_flag,'N',l_org_id,grp.org_id)= decode(l_multi_org_flag,'N',l_org_id,b.org_id)
184 AND grp.vat_tax_id = b.tax_group_id
185 ORDER BY 3,4,6,7;
186
187
188 -- remove conflicting groups
189 -- logic is to assume duplicate rows by having a calculated id
190 DELETE FROM zx_tax_relations_t d
191 WHERE d.ROWID IN
192 ( SELECT min(a.ROWID)
193 FROM zx_tax_relations_t a,
194 zx_tax_relations_t b
195 WHERE a.org_id = b.org_id
196 AND a.parent_tax_code = b.child_tax_code
197 AND b.parent_tax_code = a.child_tax_code
198 GROUP BY a.parent_tax_code_id + a.child_tax_code_id, a.tax_group_id+b.tax_group_id);
199
200
201 -- this works when there is one parent only.
202 INSERT INTO zx_compound_errors_t(
203 tax_group_id ,
204 tax_group_code,
205 error_number,
206 error_message)
207 SELECT tax_group_id, grp.tax_code group_code,
208 ROWNUM, 'Conflicting priority group '
209 FROM ar_vat_tax_all grp,
210 (SELECT min(a.tax_group_id) tax_group_id
211 FROM zx_tax_relations_t a
212 , zx_tax_relations_t b
213 WHERE a.org_id = b.org_id
214 AND a.parent_tax_code = b.child_tax_code
215 AND b.parent_tax_code = a.child_tax_code
216 GROUP BY a.tax_group_id+b.tax_group_id )
217 WHERE tax_group_id = grp.vat_tax_id;
218
219 DELETE FROM zx_tax_relations_t d
220 WHERE d.tax_group_id IN
221 ( SELECT min(a.tax_group_id)
222 FROM zx_tax_relations_t a, zx_tax_relations_t b
223 WHERE a.org_id = b.org_id
224 AND a.parent_tax_code = b.child_tax_code
225 AND b.parent_tax_code = a.child_tax_code
226 GROUP BY a.tax_group_id+b.tax_group_id);
227
228 DELETE FROM zx_tax_relations_t a1
229 WHERE EXISTS (SELECT 1 FROM zx_tax_relations_t a2
230 WHERE a1.CHILD_TAX_CODE_ID = a2.CHILD_TAX_CODE_ID
231 AND a2.CHILD_PRECEDENCE = a1.CHILD_PRECEDENCE
232 AND a2.PARENT_TAX_CODE_ID <> a1.PARENT_TAX_CODE_ID
233 AND a2.PARENT_PRECEDENCE > a1.PARENT_PRECEDENCE
234 AND a2.tax_group_id = a1.tax_group_id);
235
236 END load_tax_relations;
237
238
239 PROCEDURE load_regime_list IS
240
241 BEGIN
242 /*
243 SELECT ALL THE posible tax TYPES AND THE ar organizations that can be used TO DEFINE taxes
244 these regimes will be used IN THE load tax procees TO provide THE regime FOR THE taxes.
245 also initialize regime precedence.
246 */
247
248 INSERT ALL INTO
249 zx_tax_priorities_t (
250 regime_or_tax_flag ,
251 tax_regime_code ,
252 regime_precedence,
253 org_id,
254 tax_type,
255 created_by,
256 creation_date,
257 last_updated_by,
258 last_update_date )
259 VALUES (
260 tax_regime_flag,
261 tax_regime_code ,
262 precedence,
263 org_id,
264 tax_type,
265 created_by,
266 creation_date,
267 last_updated_by,
268 last_update_date )
269 SELECT 'R' tax_regime_flag,
270 --zx_migrate_util.get_country(temp_regime.org_id) || '_' || temp_regime.tax_type tax_regime_code,
271 CASE WHEN temp_regime.tax_type <> 'SALES_TAX' then --Bug 5691957
272 Zx_Migrate_Util.Get_Country(temp_regime.Org_Id)||'-Tax'
273 ELSE
274
275 Zx_Migrate_Util.GET_TAX_REGIME(
276 temp_regime.tax_type,
277 temp_regime.org_id)
278 END tax_regime_code ,
279 ROWNUM precedence,
280 decode(l_multi_org_flag,'N',l_org_id,temp_regime.org_id) org_id,
281 temp_regime.tax_type tax_type,
282 fnd_global.user_id created_by,
283 sysdate creation_date ,
284 fnd_global.user_id last_updated_by,
285 sysdate last_update_date
286 FROM
287 (SELECT UNIQUE DECODE(l_multi_org_flag,'N',l_org_id,org_id) org_id , tax_type
288 FROM ar_vat_tax_all_b ) temp_regime;
289
290 END load_regime_list;
291
292
293 /***********************************
294
295 */
296 PROCEDURE load_tax_list IS
297
298 BEGIN
299
300 INSERT ALL INTO
301 zx_tax_priorities_t (
302 regime_or_tax_flag ,
303 tax_id ,
304 tax_code ,
305 tax_precedence ,
306 tax_regime_code ,
307 regime_precedence,
308 tax_type,
309 group_flag,
310 org_id,
311 created_by,
312 creation_date,
313 last_updated_by,
314 last_update_date)
315 VALUES (
316 tax_regime_flag,
317 parent_tax ,
318 parent_tax_code ,
319 precedence,
320 tax_regime_code,
321 NULL,
322 tax_type,
323 group_flag,
324 org_id,
325 fnd_global.user_id,
326 sysdate ,
327 fnd_global.user_id ,
328 sysdate )
329 -- select taxes (in a group) that have a precedence and give an initial of 1
330 SELECT UNIQUE 'T' tax_regime_flag,
331 reg.tax_regime_code tax_regime_code,
332 a.tax_code_id parent_tax,
333 aa.tax_code parent_tax_code,
334 decode (a.compounding_precedence,NULL,0,1) precedence,
335 aa.tax_type tax_type,
336 'Y' group_flag,
337 decode(l_multi_org_flag,'N',l_org_id,aa.org_id) org_id
338 FROM ar_tax_group_codes_all a,
339 ar_vat_tax_all_b aa,
340 zx_tax_priorities_t reg
341 WHERE aa.vat_tax_id = a.tax_code_id
342 AND aa.tax_type = reg.tax_type
343 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = reg.org_id
344 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = decode(l_multi_org_flag,'N',l_org_id,a.org_id)
345 AND NOT EXISTS (
346 SELECT 'Y', tax_code_id
347 FROM ar_tax_group_codes_all dup_tax
348 WHERE dup_tax.tax_code_id = a.tax_code_id
349 AND dup_tax.compounding_precedence IS NULL)
350 UNION ALL
351 -- select taxes with no precedence and give and initial of zero
352 -- those are taxes with no relations to other taxes
353 -- those should be the first taxes selected.
354 SELECT UNIQUE 'T' tax_regime_flag,
355 reg.tax_regime_code tax_regime_code,
356 a.tax_code_id parent_tax,
357 aa.tax_code parent_tax_code,
358 0 precedence,
359 aa.tax_type tax_type,
360 'N' group_flag,
361 decode(l_multi_org_flag,'N',l_org_id,aa.org_id) org_id
362 FROM ar_tax_group_codes_all a,
363 ar_vat_tax_all_b aa,
364 zx_tax_priorities_t reg
365 WHERE aa.vat_tax_id = a.tax_code_id
366 AND a.compounding_precedence IS NULL
367 AND aa.tax_type = reg.tax_type
368 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = reg.org_id
369 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = decode(l_multi_org_flag,'N',l_org_id,a.org_id);
370
371 END load_tax_list;
372
373
374 /***********************************/
375
376 PROCEDURE set_precedences IS
377
378 precedence_count NUMBER;
379 old_regime VARCHAR2(30);
380 old_org_id NUMBER;
381 current_regime VARCHAR2(30);
382 current_org_id NUMBER;
383
384 v_parent_precedence NUMBER;
385 v_child_precedence NUMBER;
386 v_aux_precedence NUMBER;
387 v_aux_reg_precedence NUMBER;
388
389 v_parent_rel_precedence NUMBER;
390 v_child_rel_precedence NUMBER;
391
392 v_child_tax NUMBER;
393 v_parent_tax NUMBER;
394
395 v_child_org NUMBER;
396 v_parent_org NUMBER;
397
398 v_child_regime VARCHAR2(30);
399 v_parent_regime VARCHAR2(30);
400
401 v_parent_reg_precedence NUMBER;
402 v_child_reg_precedence NUMBER;
403
404 v_child_tax_rowid UROWID;
405 v_parent_tax_rowid UROWID;
406
407 v_child_reg_rowid UROWID;
408 v_parent_reg_rowid UROWID;
409
410 /* cursors to set the taxes priorities */
411 CURSOR c_set_priorities IS
412 SELECT org_id, tax_regime_code
413 FROM zx_tax_priorities_t
414 WHERE regime_or_tax_flag ='T'
415 FOR UPDATE OF tax_precedence
416 ORDER BY tax_regime_code,org_id,tax_precedence; --Bug 4524324
417
418 CURSOR c_fix_tax_priorities IS
419 SELECT org_id, tax_precedence, tax_id, ROWID
420 FROM zx_tax_priorities_t
421 WHERE regime_or_tax_flag ='T'
422 ORDER BY tax_regime_code,tax_precedence;
423
424
425 CURSOR c_get_taxes_relation IS
426 SELECT org_id, parent_regime_code, parent_precedence, parent_tax_code_id,
427 child_regime_code, child_precedence
428 FROM zx_tax_relations_t
429 WHERE child_tax_code_id = v_child_tax
430 AND org_id = v_child_org;
431
432
433 CURSOR c_get_parent_priority IS
434 SELECT tax_precedence, ROWID
435 FROM zx_tax_priorities_t
436 WHERE tax_id = v_parent_tax
437 AND org_id = v_parent_org;
438
439 /* cursors to set the regimes priorities */
440
441 CURSOR c_get_parent_reg_prece IS
442 SELECT tax_precedence, ROWID
443 FROM zx_tax_priorities_t
444 WHERE regime_or_tax_flag ='R'
445 AND tax_regime_code = v_parent_regime;
446
447 CURSOR c_get_child_reg_prece IS
448 SELECT tax_precedence, ROWID
449 FROM zx_tax_priorities_t
450 WHERE regime_or_tax_flag ='R'
451 AND tax_regime_code = v_child_regime;
452
453 BEGIN
454 old_regime:='OLD';
455 old_org_id:=-1;
456
457 /* initialize taxes priorities per content owner (org id) and regime */
458 OPEN c_set_priorities;
459 LOOP
460 FETCH c_set_priorities INTO current_org_id, current_regime;
461 EXIT WHEN c_set_priorities%NOTFOUND;
462
463 IF (old_regime <> current_regime OR old_org_id <> current_org_id) THEN
464 old_regime:=current_regime;
465 old_org_id:=current_org_id;
466 precedence_count:=0;
467 END IF;
468
469 precedence_count:=precedence_count+1;
470
471 UPDATE zx_tax_priorities_t
472 SET tax_precedence = precedence_count
473 WHERE CURRENT OF c_set_priorities;
474
475 END LOOP;
476
477 CLOSE c_set_priorities;
478
479 /* sort the taxes using the relationship_t table as a helper */
480
481 OPEN c_fix_tax_priorities;
482 LOOP
483 FETCH c_fix_tax_priorities INTO v_child_org, v_child_precedence, v_child_tax, v_child_tax_rowid;
484 EXIT WHEN c_fix_tax_priorities%NOTFOUND;
485
486 OPEN c_get_taxes_relation;
487 /* find if the tax appears as child tax in the relations table, and compare precedence to parent */
488
489 LOOP
490 FETCH c_get_taxes_relation INTO v_parent_org, v_parent_regime, v_parent_rel_precedence,
491 v_parent_tax, v_child_regime, v_child_rel_precedence;
492 EXIT WHEN c_get_taxes_relation%NOTFOUND;
493
494 -- dbms_output.put_line('parent tax code ' || to_char(v_parent_tax) || ' child tax code ' || to_char(v_child_tax));
495
496 IF (v_parent_regime = v_child_regime AND v_parent_org = v_child_org ) THEN
497 OPEN c_get_parent_priority;
498 FETCH c_get_parent_priority INTO v_parent_precedence, v_parent_tax_rowid;
499
500 -- dbms_output.put_line('parent precedence' || v_parent_precedence || ' child precedence ' || v_child_precedence );
501
502 IF (v_parent_precedence > v_child_precedence) THEN
503 -- switch initialized precendeces
504 v_aux_precedence := v_parent_precedence;
505 v_parent_precedence := v_child_precedence;
506 v_child_precedence := v_aux_precedence;
507
508 UPDATE zx_tax_priorities_t
509 SET tax_precedence = v_parent_precedence
510 WHERE ROWID = v_parent_tax_rowid;
511
512 UPDATE zx_tax_priorities_t
513 SET tax_precedence = v_child_precedence
514 WHERE ROWID = v_child_tax_rowid;
515
516 END IF;
517
518 CLOSE c_get_parent_priority;
519 ELSE -- different regimes
520 -- switch regime precedences if needed.
521 -- we use the precedence of taxes as a helper to sort the regimes precedence.
522
523 OPEN c_get_parent_reg_prece;
524 OPEN c_get_child_reg_prece;
525 FETCH c_get_parent_reg_prece INTO v_parent_reg_precedence, v_parent_reg_rowid;
526 FETCH c_get_child_reg_prece INTO v_child_reg_precedence, v_child_reg_rowid;
527
528 IF (v_parent_reg_precedence > v_child_reg_precedence ) THEN
529
530 -- dbms_output.put_line('parent regime prece' || v_parent_reg_precedence || ' child regime prece ' || v_child_reg_precedence );
531
532 v_aux_reg_precedence := v_parent_reg_precedence;
533 v_parent_reg_precedence := v_child_reg_precedence;
534 v_child_reg_precedence := v_aux_reg_precedence;
535
536 UPDATE zx_tax_priorities_t
537 SET tax_precedence = v_parent_reg_precedence
538 WHERE ROWID = v_parent_reg_rowid;
539
540 UPDATE zx_tax_priorities_t
541 SET tax_precedence = v_child_reg_precedence
542 WHERE ROWID = v_child_reg_rowid;
543
544 END IF;
545
546 CLOSE c_get_parent_reg_prece;
547 CLOSE c_get_child_reg_prece;
548
549 END IF; -- compare regimes
550
551 END LOOP;
552
553 CLOSE c_get_taxes_relation;
554
555 END LOOP;
556
557 CLOSE c_fix_tax_priorities;
558
559 END set_precedences;
560
561 /* main program: calls procedures in order */
562 PROCEDURE main IS
563
564 BEGIN
565
566
567
568 DELETE zx_tax_relations_t;
569 DELETE zx_tax_priorities_t;
570 DELETE zx_compound_errors_t;
571 COMMIT;
572
573 load_regime_list;
574 load_tax_list;
575 load_tax_relations;
576 set_precedences;
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 arp_util_tax.debug('Exception in constructor of Tax Group Migration '||sqlerrm);
581
582 END main;
583 begin
584
585 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
586 FND_PRODUCT_GROUPS;
587
588 IF L_MULTI_ORG_FLAG = 'N' THEN
589
590 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
591
592 IF L_ORG_ID IS NULL THEN
593 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
594 END IF;
595 ELSE
596 L_ORG_ID := NULL;
597 END IF;
598 EXCEPTION
599 WHEN OTHERS THEN
600 arp_util_tax.debug('Exception in constructor of tax group migration '||sqlerrm);
601
602 END zx_tcm_compound_pkg;