[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_COMPOUND_PKG
Source
1 PACKAGE BODY zx_tcm_compound_pkg AS
2 /* $Header: zxtaxgroupmigb.pls 120.22 2006/12/27 20:38:51 svaze 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 END load_tax_relations;
229
230
231 PROCEDURE load_regime_list IS
232
233 BEGIN
234 /*
235 SELECT ALL THE posible tax TYPES AND THE ar organizations that can be used TO DEFINE taxes
236 these regimes will be used IN THE load tax procees TO provide THE regime FOR THE taxes.
237 also initialize regime precedence.
238 */
239
240 INSERT ALL INTO
241 zx_tax_priorities_t (
242 regime_or_tax_flag ,
243 tax_regime_code ,
244 regime_precedence,
245 org_id,
246 tax_type,
247 created_by,
248 creation_date,
249 last_updated_by,
250 last_update_date )
251 VALUES (
252 tax_regime_flag,
253 tax_regime_code ,
254 precedence,
255 org_id,
256 tax_type,
257 created_by,
258 creation_date,
259 last_updated_by,
260 last_update_date )
261 SELECT 'R' tax_regime_flag,
262 --zx_migrate_util.get_country(temp_regime.org_id) || '_' || temp_regime.tax_type tax_regime_code,
263 CASE WHEN temp_regime.tax_type <> 'SALES_TAX' then --Bug 5691957
264 Zx_Migrate_Util.Get_Country(temp_regime.Org_Id)||'-Tax'
265 ELSE
266
267 Zx_Migrate_Util.GET_TAX_REGIME(
268 temp_regime.tax_type,
269 temp_regime.org_id)
270 END tax_regime_code ,
271 ROWNUM precedence,
272 decode(l_multi_org_flag,'N',l_org_id,temp_regime.org_id) org_id,
273 temp_regime.tax_type tax_type,
274 fnd_global.user_id created_by,
275 sysdate creation_date ,
276 fnd_global.user_id last_updated_by,
277 sysdate last_update_date
278 FROM
279 (SELECT UNIQUE DECODE(l_multi_org_flag,'N',l_org_id,org_id) org_id , tax_type
280 FROM ar_vat_tax_all_b ) temp_regime;
281
282 END load_regime_list;
283
284
285 /***********************************
286
287 */
288 PROCEDURE load_tax_list IS
289
290 BEGIN
291
292 INSERT ALL INTO
293 zx_tax_priorities_t (
294 regime_or_tax_flag ,
295 tax_id ,
296 tax_code ,
297 tax_precedence ,
298 tax_regime_code ,
299 regime_precedence,
300 tax_type,
301 group_flag,
302 org_id,
303 created_by,
304 creation_date,
305 last_updated_by,
306 last_update_date)
307 VALUES (
308 tax_regime_flag,
309 parent_tax ,
310 parent_tax_code ,
311 precedence,
312 tax_regime_code,
313 NULL,
314 tax_type,
315 group_flag,
316 org_id,
317 fnd_global.user_id,
318 sysdate ,
319 fnd_global.user_id ,
320 sysdate )
321 -- select taxes (in a group) that have a precedence and give an initial of 1
322 SELECT UNIQUE 'T' tax_regime_flag,
323 reg.tax_regime_code tax_regime_code,
324 a.tax_code_id parent_tax,
325 aa.tax_code parent_tax_code,
326 decode (a.compounding_precedence,NULL,0,1) precedence,
327 aa.tax_type tax_type,
328 'Y' group_flag,
329 decode(l_multi_org_flag,'N',l_org_id,aa.org_id) org_id
330 FROM ar_tax_group_codes_all a,
331 ar_vat_tax_all_b aa,
332 zx_tax_priorities_t reg
333 WHERE aa.vat_tax_id = a.tax_code_id
334 AND aa.tax_type = reg.tax_type
335 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = reg.org_id
336 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)
337 AND NOT EXISTS (
338 SELECT 'Y', tax_code_id
339 FROM ar_tax_group_codes_all dup_tax
340 WHERE dup_tax.tax_code_id = a.tax_code_id
341 AND dup_tax.compounding_precedence IS NULL)
342 UNION ALL
343 -- select taxes with no precedence and give and initial of zero
344 -- those are taxes with no relations to other taxes
345 -- those should be the first taxes selected.
346 SELECT UNIQUE 'T' tax_regime_flag,
347 reg.tax_regime_code tax_regime_code,
348 a.tax_code_id parent_tax,
349 aa.tax_code parent_tax_code,
350 0 precedence,
351 aa.tax_type tax_type,
352 'N' group_flag,
353 decode(l_multi_org_flag,'N',l_org_id,aa.org_id) org_id
354 FROM ar_tax_group_codes_all a,
355 ar_vat_tax_all_b aa,
356 zx_tax_priorities_t reg
357 WHERE aa.vat_tax_id = a.tax_code_id
358 AND a.compounding_precedence IS NULL
359 AND aa.tax_type = reg.tax_type
360 AND decode(l_multi_org_flag,'N',l_org_id,aa.org_id) = reg.org_id
361 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);
362
363 END load_tax_list;
364
365
366 /***********************************/
367
368 PROCEDURE set_precedences IS
369
370 precedence_count NUMBER;
371 old_regime VARCHAR2(30);
372 old_org_id NUMBER;
373 current_regime VARCHAR2(30);
374 current_org_id NUMBER;
375
376 v_parent_precedence NUMBER;
377 v_child_precedence NUMBER;
378 v_aux_precedence NUMBER;
379 v_aux_reg_precedence NUMBER;
380
381 v_parent_rel_precedence NUMBER;
382 v_child_rel_precedence NUMBER;
383
384 v_child_tax NUMBER;
385 v_parent_tax NUMBER;
386
387 v_child_org NUMBER;
388 v_parent_org NUMBER;
389
390 v_child_regime VARCHAR2(30);
391 v_parent_regime VARCHAR2(30);
392
393 v_parent_reg_precedence NUMBER;
394 v_child_reg_precedence NUMBER;
395
396 v_child_tax_rowid UROWID;
397 v_parent_tax_rowid UROWID;
398
399 v_child_reg_rowid UROWID;
400 v_parent_reg_rowid UROWID;
401
402 /* cursors to set the taxes priorities */
403 CURSOR c_set_priorities IS
404 SELECT org_id, tax_regime_code
405 FROM zx_tax_priorities_t
406 WHERE regime_or_tax_flag ='T'
407 FOR UPDATE OF tax_precedence
408 ORDER BY tax_regime_code,org_id,tax_precedence; --Bug 4524324
409
410 CURSOR c_fix_tax_priorities IS
411 SELECT org_id, tax_precedence, tax_id, ROWID
412 FROM zx_tax_priorities_t
413 WHERE regime_or_tax_flag ='T'
414 ORDER BY tax_regime_code,tax_precedence;
415
416
417 CURSOR c_get_taxes_relation IS
418 SELECT org_id, parent_regime_code, parent_precedence, parent_tax_code_id,
419 child_regime_code, child_precedence
420 FROM zx_tax_relations_t
421 WHERE child_tax_code_id = v_child_tax
422 AND org_id = v_child_org;
423
424
425 CURSOR c_get_parent_priority IS
426 SELECT tax_precedence, ROWID
427 FROM zx_tax_priorities_t
428 WHERE tax_id = v_parent_tax
429 AND org_id = v_parent_org;
430
431 /* cursors to set the regimes priorities */
432
433 CURSOR c_get_parent_reg_prece IS
434 SELECT tax_precedence, ROWID
435 FROM zx_tax_priorities_t
436 WHERE regime_or_tax_flag ='R'
437 AND tax_regime_code = v_parent_regime;
438
439 CURSOR c_get_child_reg_prece IS
440 SELECT tax_precedence, ROWID
441 FROM zx_tax_priorities_t
442 WHERE regime_or_tax_flag ='R'
443 AND tax_regime_code = v_child_regime;
444
445 BEGIN
446 old_regime:='OLD';
447 old_org_id:=-1;
448
449 /* initialize taxes priorities per content owner (org id) and regime */
450 OPEN c_set_priorities;
451 LOOP
452 FETCH c_set_priorities INTO current_org_id, current_regime;
453 EXIT WHEN c_set_priorities%NOTFOUND;
454
455 IF (old_regime <> current_regime OR old_org_id <> current_org_id) THEN
456 old_regime:=current_regime;
457 old_org_id:=current_org_id;
458 precedence_count:=0;
459 END IF;
460
461 precedence_count:=precedence_count+1;
462
463 UPDATE zx_tax_priorities_t
464 SET tax_precedence = precedence_count
465 WHERE CURRENT OF c_set_priorities;
466
467 END LOOP;
468
469 CLOSE c_set_priorities;
470
471 /* sort the taxes using the relationship_t table as a helper */
472
473 OPEN c_fix_tax_priorities;
474 LOOP
475 FETCH c_fix_tax_priorities INTO v_child_org, v_child_precedence, v_child_tax, v_child_tax_rowid;
476 EXIT WHEN c_fix_tax_priorities%NOTFOUND;
477
478 OPEN c_get_taxes_relation;
479 /* find if the tax appears as child tax in the relations table, and compare precedence to parent */
480
481 LOOP
482 FETCH c_get_taxes_relation INTO v_parent_org, v_parent_regime, v_parent_rel_precedence,
486 -- dbms_output.put_line('parent tax code ' || to_char(v_parent_tax) || ' child tax code ' || to_char(v_child_tax));
483 v_parent_tax, v_child_regime, v_child_rel_precedence;
484 EXIT WHEN c_get_taxes_relation%NOTFOUND;
485
487
488 IF (v_parent_regime = v_child_regime AND v_parent_org = v_child_org ) THEN
489 OPEN c_get_parent_priority;
490 FETCH c_get_parent_priority INTO v_parent_precedence, v_parent_tax_rowid;
491
492 -- dbms_output.put_line('parent precedence' || v_parent_precedence || ' child precedence ' || v_child_precedence );
493
494 IF (v_parent_precedence > v_child_precedence) THEN
495 -- switch initialized precendeces
496 v_aux_precedence := v_parent_precedence;
497 v_parent_precedence := v_child_precedence;
498 v_child_precedence := v_aux_precedence;
499
500 UPDATE zx_tax_priorities_t
501 SET tax_precedence = v_parent_precedence
502 WHERE ROWID = v_parent_tax_rowid;
503
504 UPDATE zx_tax_priorities_t
505 SET tax_precedence = v_child_precedence
506 WHERE ROWID = v_child_tax_rowid;
507
508 END IF;
509
510 CLOSE c_get_parent_priority;
511 ELSE -- different regimes
512 -- switch regime precedences if needed.
513 -- we use the precedence of taxes as a helper to sort the regimes precedence.
514
515 OPEN c_get_parent_reg_prece;
516 OPEN c_get_child_reg_prece;
517 FETCH c_get_parent_reg_prece INTO v_parent_reg_precedence, v_parent_reg_rowid;
518 FETCH c_get_child_reg_prece INTO v_child_reg_precedence, v_child_reg_rowid;
519
520 IF (v_parent_reg_precedence > v_child_reg_precedence ) THEN
521
522 -- dbms_output.put_line('parent regime prece' || v_parent_reg_precedence || ' child regime prece ' || v_child_reg_precedence );
523
524 v_aux_reg_precedence := v_parent_reg_precedence;
525 v_parent_reg_precedence := v_child_reg_precedence;
526 v_child_reg_precedence := v_aux_reg_precedence;
527
528 UPDATE zx_tax_priorities_t
529 SET tax_precedence = v_parent_reg_precedence
530 WHERE ROWID = v_parent_reg_rowid;
531
532 UPDATE zx_tax_priorities_t
533 SET tax_precedence = v_child_reg_precedence
534 WHERE ROWID = v_child_reg_rowid;
535
536 END IF;
537
538 CLOSE c_get_parent_reg_prece;
539 CLOSE c_get_child_reg_prece;
540
541 END IF; -- compare regimes
542
543 END LOOP;
544
545 CLOSE c_get_taxes_relation;
546
547 END LOOP;
548
549 CLOSE c_fix_tax_priorities;
550
551 END set_precedences;
552
553 /* main program: calls procedures in order */
554 PROCEDURE main IS
555
556 BEGIN
557
558
559
560 DELETE zx_tax_relations_t;
561 DELETE zx_tax_priorities_t;
562 DELETE zx_compound_errors_t;
563 COMMIT;
564
565 load_regime_list;
566 load_tax_list;
567 load_tax_relations;
568 set_precedences;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 arp_util_tax.debug('Exception in constructor of Tax Group Migration '||sqlerrm);
573
574 END main;
575 begin
576
577 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
578 FND_PRODUCT_GROUPS;
579
580 IF L_MULTI_ORG_FLAG = 'N' THEN
581
582 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
583
584 IF L_ORG_ID IS NULL THEN
585 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
586 END IF;
587 ELSE
588 L_ORG_ID := NULL;
589 END IF;
590 EXCEPTION
591 WHEN OTHERS THEN
592 arp_util_tax.debug('Exception in constructor of tax group migration '||sqlerrm);
593
594 END zx_tcm_compound_pkg;