[Home] [Help]
PACKAGE BODY: APPS.ZX_MIGRATE_TAX_DEFAULT_HIER
Source
1 PACKAGE BODY Zx_Migrate_Tax_Default_Hier AS
2 /*$Header: zxtaxhiermigb.pls 120.40.12010000.2 2008/12/24 15:24:18 ssanka ship $ */
3
4 PG_DEBUG CONSTANT VARCHAR(1) default
5 NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
8 L_ORG_ID NUMBER(15);
9
10 /*Private procedure forward declarations*/
11 PROCEDURE create_template;
12
13
14 /*=========================================================================+
15 | PROCEDURE |
16 | migrate_default_hierarchy |
17 | |
18 | DESCRIPTION |
19 | This routine is a wrapper for migration of current AP/PO Default |
20 | Hierarchy functionality to eBTax rules model. |
21 | |
22 | SCOPE - PUBLIC |
23 | |
24 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
25 | |
26 | CALLED FROM |
27 | |
28 | NOTES |
29 | |
30 | MODIFICATION HISTORY |
31 | 15-Jan-04 Srinivas Lokam Created. |
32 | |
33 |=========================================================================*/
34
35
36 PROCEDURE migrate_default_hierarchy is
37 BEGIN
38 IF PG_DEBUG = 'Y' THEN
39 arp_util_tax.debug('Migrate_Default_Hierarchy(+)');
40 END IF;
41 Savepoint Default_Setup;
42 create_template; --Bug 4935978
43 create_condition_groups;
44 create_rules;
45 create_process_results;
46 IF PG_DEBUG = 'Y' THEN
47 arp_util_tax.debug('Migrate_Default_Hierarchy(-)');
48 END IF;
49 EXCEPTION
50 WHEN OTHERS THEN
51 IF PG_DEBUG = 'Y' THEN
52 arp_util_tax.debug('EXCEPTION: Migrate_default_hierarchy ');
53 arp_util_tax.debug(sqlerrm);
54 arp_util_tax.debug('Migrate_Default_Hierarchy(-)');
55 END IF;
56 Rollback To Default_Setup;
57 --app_exception.raise_exception;
58 END migrate_default_hierarchy;
59
60
61 /*=========================================================================+
62 | PROCEDURE |
63 | create_template |
64 | |
65 | DESCRIPTION |
66 | This procedure is used to create determining factor templates |
67 | explicitly for the purpose of rules determination |
68 | |
69 | SCOPE - PUBLIC |
70 | |
71 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
72 | |
73 | CALLED FROM |
74 | migrate_default_hierarchy |
75 | NOTES |
76 | |
77 | MODIFICATION HISTORY |
78 | 23-Jan-06 Arnab Sengupta Created as part of bug 4935978 |
79 |=========================================================================*/
80
81 PROCEDURE create_template IS
82 BEGIN
83 IF PG_DEBUG = 'Y' THEN
84 arp_util_tax.debug('create_template(+)');
85 END IF;
86
87 -- Determining factor template: STCC
88 INSERT INTO ZX_DET_FACTOR_TEMPL_B
89 (
90 DET_FACTOR_TEMPL_CODE ,
91 TAX_REGIME_CODE ,
92 TEMPLATE_USAGE_CODE ,
93 RECORD_TYPE_CODE ,
94 LEDGER_ID ,
95 CHART_OF_ACCOUNTS_ID ,
96 DET_FACTOR_TEMPL_ID ,
97 CREATED_BY ,
98 CREATION_DATE ,
99 LAST_UPDATED_BY ,
100 LAST_UPDATE_DATE ,
101 LAST_UPDATE_LOGIN ,
102 REQUEST_ID ,
103 PROGRAM_APPLICATION_ID ,
104 PROGRAM_ID ,
105 PROGRAM_LOGIN_ID ,
106 OBJECT_VERSION_NUMBER
107 )
108 SELECT
109 'STCC' , --DET_FACTOR_TEMPL_CODE
110 NULL , --TAX_REGIME_CODE
111 'TAX_RULES' , --TEMPLATE_USAGE_CODE
112 'MIGRATED' , --RECORD_TYPE_CODE
113 NULL , --LEDGER_ID
114 NULL , --CHART_OF_ACCOUNTS_ID
115 zx_det_factor_templ_b_s.nextval , --DET_FACTOR_TEMPL_ID
116 fnd_global.user_id , --CREATED_BY
117 SYSDATE , --CREATION_DATE
118 fnd_global.user_id , --LAST_UPDATED_BY
119 SYSDATE , --LAST_UPDATE_DATE
120 fnd_global.conc_login_id , --LAST_UPDATE_LOGIN
121 fnd_global.conc_request_id , --Request Id
122 fnd_global.prog_appl_id , --Program Application ID
123 fnd_global.conc_program_id , --Program Id
124 fnd_global.conc_login_id , --Program Login ID
125 1
126 FROM DUAL
127 WHERE not exists (select 1
128 from ZX_DET_FACTOR_TEMPL_B
129 where DET_FACTOR_TEMPL_CODE = 'STCC'
130 );
131
132 --Determining factor template: LEASE_MGT_RATE_DET_TEMPL
133 INSERT INTO ZX_DET_FACTOR_TEMPL_B
134 (
135 DET_FACTOR_TEMPL_CODE ,
136 TAX_REGIME_CODE ,
137 TEMPLATE_USAGE_CODE ,
138 RECORD_TYPE_CODE ,
139 LEDGER_ID ,
140 CHART_OF_ACCOUNTS_ID ,
141 DET_FACTOR_TEMPL_ID ,
142 CREATED_BY ,
143 CREATION_DATE ,
144 LAST_UPDATED_BY ,
145 LAST_UPDATE_DATE ,
146 LAST_UPDATE_LOGIN ,
147 REQUEST_ID ,
148 PROGRAM_APPLICATION_ID ,
149 PROGRAM_ID ,
150 PROGRAM_LOGIN_ID ,
151 OBJECT_VERSION_NUMBER
152 )
153 SELECT
154 'LEASE_MGT_RATE_DET_TEMPL' , --DET_FACTOR_TEMPL_CODE
155 NULL , --TAX_REGIME_CODE
156 'TAX_RULES' , --TEMPLATE_USAGE_CODE
157 'MIGRATED' , --RECORD_TYPE_CODE
158 NULL , --LEDGER_ID
159 NULL , --CHART_OF_ACCOUNTS_ID
160 zx_det_factor_templ_b_s.nextval , --DET_FACTOR_TEMPL_ID
161 fnd_global.user_id , --CREATED_BY
162 SYSDATE , --CREATION_DATE
163 fnd_global.user_id , --LAST_UPDATED_BY
164 SYSDATE , --LAST_UPDATE_DATE
165 fnd_global.conc_login_id , --LAST_UPDATE_LOGIN
166 fnd_global.conc_request_id , --Request Id
167 fnd_global.prog_appl_id , --Program Application ID
168 fnd_global.conc_program_id , --Program Id
169 fnd_global.conc_login_id , --Program Login ID
170 1
171 FROM DUAL
172 WHERE not exists (select 1
173 from ZX_DET_FACTOR_TEMPL_B
174 where DET_FACTOR_TEMPL_CODE = 'LEASE_MGT_RATE_DET_TEMPL'
175 );
176
177 INSERT INTO ZX_DET_FACTOR_TEMPL_TL
178 (
179 LANGUAGE ,
180 SOURCE_LANG ,
181 DET_FACTOR_TEMPL_NAME ,
182 DET_FACTOR_TEMPL_DESC ,
183 DET_FACTOR_TEMPL_ID ,
184 CREATION_DATE ,
185 CREATED_BY ,
186 LAST_UPDATE_DATE ,
187 LAST_UPDATED_BY ,
188 LAST_UPDATE_LOGIN
189 )
190 SELECT
191 L.LANGUAGE_CODE ,--LANGUAGE
192 userenv('LANG') ,--SOURCE_LANG
193 Initcap(B.DET_FACTOR_TEMPL_CODE) ,--DET_FACTOR_TEMPL_NAME
194 B.DET_FACTOR_TEMPL_CODE ,--DET_FACTOR_TEMPL_DESC
195 B.DET_FACTOR_TEMPL_ID ,--DET_FACTOR_TEMPL_ID
196 SYSDATE ,--CREATION_DATE
197 fnd_global.user_id ,--CREATED_BY
198 SYSDATE ,--LAST_UPDATE_DATE
199 fnd_global.user_id ,--LAST_UPDATED_BY
200 fnd_global.conc_login_id --LAST_UPDATE_LOGIN
201 FROM
202 FND_LANGUAGES L,
203 ZX_DET_FACTOR_TEMPL_B B
204 WHERE
205 L.INSTALLED_FLAG in ('I', 'B')
206 AND B.DET_FACTOR_TEMPL_CODE IN ('STCC' , 'LEASE_MGT_RATE_DET_TEMPL')
207 AND not exists
208 (select 1
209 from ZX_DET_FACTOR_TEMPL_TL T
210 where T.DET_FACTOR_TEMPL_ID = B.DET_FACTOR_TEMPL_ID
211 and T.LANGUAGE = L.LANGUAGE_CODE);
212
213 -- Determining factor code of input factor tax_classification_code is part of
214 -- seed data
215
216 -- Create determining factor code for input factor PRODUCT_FISCAL_CLASS
217 INSERT INTO ZX_DETERMINING_FACTORS_B
218 (
219 DETERMINING_FACTOR_CODE ,
220 DETERMINING_FACTOR_CLASS_CODE,
221 VALUE_SET ,
222 TAX_PARAMETER_CODE ,
223 DATA_TYPE_CODE ,
224 TAX_FUNCTION_CODE ,
225 RECORD_TYPE_CODE ,
226 TAX_REGIME_DET_FLAG ,
227 TAX_SUMMARIZATION_FLAG ,
228 TAX_RULES_FLAG ,
229 TAXABLE_BASIS_FLAG ,
230 TAX_CALCULATION_FLAG ,
231 INTERNAL_FLAG ,
232 RECORD_ONLY_FLAG ,
233 CREATION_DATE ,
234 LAST_UPDATE_DATE ,
235 REQUEST_ID ,
236 PROGRAM_APPLICATION_ID ,
237 PROGRAM_ID ,
238 PROGRAM_LOGIN_ID ,
239 DETERMINING_FACTOR_ID ,
240 CREATED_BY ,
241 LAST_UPDATED_BY ,
242 LAST_UPDATE_LOGIN ,
243 OBJECT_VERSION_NUMBER )
244
245 SELECT
246 'LEASE_MGT_PROD_FISC_CLASS' DETERMINING_FACTOR_CODE,
247 'PRODUCT_FISCAL_CLASS' DETERMINING_FACTOR_CLASS_CODE,
248 NULL VALUE_SET,
249 NULL TAX_PARAMETER_CODE,
250 'ALPHANUMERIC' DATA_TYPE_CODE,
251 NULL TAX_FUNCTION_CODE,
252 'MIGRATED' RECORD_TYPE_CODE,
253 'N' TAX_REGIME_DET_FLAG,
254 'N' TAX_SUMMARIZATION_FLAG,
255 'Y' TAX_RULES_FLAG,
256 'N' TAXABLE_BASIS_FLAG,
257 'N' TAX_CALCULATION_FLAG,
258 'N' INTERNAL_FLAG,
259 'N' RECORD_ONLY_FLAG,
260 SYSDATE , --CREATION_DATE
261 SYSDATE , --LAST_UPDATE_DATE
262 fnd_global.conc_request_id , --Request Id
263 fnd_global.prog_appl_id , --Program Application ID
264 fnd_global.conc_program_id , --Program Id
265 fnd_global.conc_login_id , --Program Login ID
266 ZX_DETERMINING_FACTORS_B_S.nextval DETERMINING_FACTOR_ID ,
267 fnd_global.user_id , --CREATED_BY
268 fnd_global.user_id , --LAST_UPDATED_BY
269 fnd_global.conc_login_id , --LAST_UPDATE_LOGIN
270 1 OBJECT_VERSION_NUMBER
271 FROM DUAL
272 WHERE NOT EXISTS (SELECT 1
273 FROM ZX_DETERMINING_FACTORS_B
274 WHERE DETERMINING_FACTOR_CLASS_CODE ='PRODUCT_FISCAL_CLASS'
275 AND DETERMINING_FACTOR_CODE = 'LEASE_MGT_PROD_FISC_CLASS');
276
277
278 -- Create determining factor code for PARTY_FISCAL_CLASS
279 INSERT INTO ZX_DETERMINING_FACTORS_B
280 (
281 DETERMINING_FACTOR_CODE ,
282 DETERMINING_FACTOR_CLASS_CODE,
283 VALUE_SET ,
284 TAX_PARAMETER_CODE ,
285 DATA_TYPE_CODE ,
286 TAX_FUNCTION_CODE ,
287 RECORD_TYPE_CODE ,
288 TAX_REGIME_DET_FLAG ,
289 TAX_SUMMARIZATION_FLAG ,
290 TAX_RULES_FLAG ,
291 TAXABLE_BASIS_FLAG ,
292 TAX_CALCULATION_FLAG ,
293 INTERNAL_FLAG ,
294 RECORD_ONLY_FLAG ,
295 CREATION_DATE ,
296 LAST_UPDATE_DATE ,
297 REQUEST_ID ,
298 PROGRAM_APPLICATION_ID ,
299 PROGRAM_ID ,
300 PROGRAM_LOGIN_ID ,
301 DETERMINING_FACTOR_ID ,
302 CREATED_BY ,
303 LAST_UPDATED_BY ,
304 LAST_UPDATE_LOGIN ,
305 OBJECT_VERSION_NUMBER )
306
307 SELECT
308 'LEASE_MGT_PTY_FISC_CLASS' DETERMINING_FACTOR_CODE,
309 'PARTY_FISCAL_CLASS' DETERMINING_FACTOR_CLASS_CODE,
310 NULL VALUE_SET,
311 NULL TAX_PARAMETER_CODE,
312 'ALPHANUMERIC' DATA_TYPE_CODE,
313 NULL TAX_FUNCTION_CODE,
314 'MIGRATED' RECORD_TYPE_CODE,
315 'N' TAX_REGIME_DET_FLAG,
316 'N' TAX_SUMMARIZATION_FLAG,
317 'Y' TAX_RULES_FLAG,
318 'N' TAXABLE_BASIS_FLAG,
319 'N' TAX_CALCULATION_FLAG,
320 'N' INTERNAL_FLAG,
321 'N' RECORD_ONLY_FLAG,
322 SYSDATE , --CREATION_DATE
323 SYSDATE , --LAST_UPDATE_DATE
324 fnd_global.conc_request_id , --Request Id
325 fnd_global.prog_appl_id , --Program Application ID
326 fnd_global.conc_program_id , --Program Id
327 fnd_global.conc_login_id , --Program Login ID
328 ZX_DETERMINING_FACTORS_B_S.nextval DETERMINING_FACTOR_ID ,
332 1 OBJECT_VERSION_NUMBER
329 fnd_global.user_id , --CREATED_BY
330 fnd_global.user_id , --LAST_UPDATED_BY
331 fnd_global.conc_login_id , --LAST_UPDATE_LOGIN
333 FROM DUAL
334 WHERE NOT EXISTS (SELECT 1
335 FROM ZX_DETERMINING_FACTORS_B
336 WHERE DETERMINING_FACTOR_CLASS_CODE ='PARTY_FISCAL_CLASS'
337 AND DETERMINING_FACTOR_CODE = 'LEASE_MGT_PTY_FISC_CLASS');
338
339 -- Determining factor code of input factor USER_DEFINED_FISC_CLASS is part of seed
340 -- data
341
342 -- Insert into the determining factors tl table
343 INSERT INTO ZX_DET_FACTORS_TL
344 (
345 LANGUAGE ,
346 SOURCE_LANG ,
347 DETERMINING_FACTOR_NAME,
348 DETERMINING_FACTOR_DESC,
349 CREATION_DATE ,
350 LAST_UPDATE_DATE ,
351 DETERMINING_FACTOR_ID ,
352 CREATED_BY ,
353 LAST_UPDATED_BY ,
354 LAST_UPDATE_LOGIN
355 )
356 SELECT
357 L.LANGUAGE_CODE ,--LANGUAGE
358 userenv('LANG') ,--SOURCE_LANG
359 Initcap(B.DETERMINING_FACTOR_CODE),--DETERMINING_FACTOR_NAME
360 B.DETERMINING_FACTOR_CODE,--DETERMINING_FACTOR_DESC
361 SYSDATE ,--CREATION_DATE
362 SYSDATE ,--LAST_UPDATE_DATE
363 B.DETERMINING_FACTOR_ID ,--DETERMINING_FACTOR_ID
364 fnd_global.user_id ,--CREATED_BY
365 fnd_global.user_id ,--LAST_UPDATED_BY
366 fnd_global.conc_login_id --LAST_UPDATE_LOGIN
367 FROM
368 FND_LANGUAGES L,
369 ZX_DETERMINING_FACTORS_B B
370 WHERE
371 L.INSTALLED_FLAG in ('I', 'B')
372 AND ((B.DETERMINING_FACTOR_CLASS_CODE ='PARTY_FISCAL_CLASS'
373 AND B.DETERMINING_FACTOR_CODE = 'LEASE_MGT_PTY_FISC_CLASS')
374 OR (B.DETERMINING_FACTOR_CLASS_CODE ='PRODUCT_FISCAL_CLASS'
375 AND B.DETERMINING_FACTOR_CODE = 'LEASE_MGT_PROD_FISC_CLASS'))
376 AND NOT EXISTS
377 (SELECT 1
378 FROM ZX_DET_FACTORS_TL T
379 WHERE T.DETERMINING_FACTOR_ID = B.DETERMINING_FACTOR_ID
380 AND T.LANGUAGE = L.LANGUAGE_CODE);
381
382 -- insert the template detail table for STCC
383 INSERT INTO ZX_DET_FACTOR_TEMPL_DTL
384 (
385 DETERMINING_FACTOR_CLASS_CODE,
386 DETERMINING_FACTOR_CQ_CODE ,
387 DETERMINING_FACTOR_CODE ,
388 REQUIRED_FLAG ,
389 RECORD_TYPE_CODE ,
390 CREATION_DATE ,
391 LAST_UPDATE_DATE ,
392 REQUEST_ID ,
393 PROGRAM_APPLICATION_ID ,
394 PROGRAM_ID ,
395 TAX_REGIME_DET_LEVEL_CODE ,
396 TAX_PARAMETER_CODE ,
397 PROGRAM_LOGIN_ID ,
398 DET_FACTOR_TEMPL_DTL_ID ,
399 DET_FACTOR_TEMPL_ID ,
400 CREATED_BY ,
401 LAST_UPDATED_BY ,
402 LAST_UPDATE_LOGIN ,
403 OBJECT_VERSION_NUMBER
404 )
405 SELECT
406 factor.DETERMINING_FACTOR_CLASS_CODE, --DETERMINING_FACTOR_CLASS_CODE
407 NULL , --DETERMINING_FACTOR_CQ_CODE
408 factor.DETERMINING_FACTOR_CODE , --DETERMINING_FACTOR_CODE
409 'Y' , --REQUIRED_FLAG
410 'MIGRATED' , --RECORD_TYPE_CODE
411 SYSDATE , --CREATION_DATE
412 SYSDATE , --LAST_UPDATE_DATE
413 factor.REQUEST_ID , --REQUEST_ID
414 factor.PROGRAM_APPLICATION_ID , --PROGRAM_APPLICATION_ID
415 factor.PROGRAM_ID , --PROGRAM_ID
416 NULL , --TAX_REGIME_DET_LEVEL_CODE
417 factor.TAX_PARAMETER_CODE , --TAX_PARAMETER_CODE
418 factor.PROGRAM_LOGIN_ID , --PROGRAM_LOGIN_ID
419 ZX_DET_FACTOR_TEMPL_DTL_S.nextval , --DET_FACTOR_TEMPL_DTL_ID
420 templ.DET_FACTOR_TEMPL_ID , --DET_FACTOR_TEMPL_ID
421 factor.CREATED_BY , --CREATED_BY
422 factor.LAST_UPDATED_BY , --LAST_UPDATED_BY
423 factor.LAST_UPDATE_LOGIN , --LAST_UPDATE_LOGIN
424 factor.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
425
426 FROM ZX_DET_FACTOR_TEMPL_B templ,
427 ZX_DETERMINING_FACTORS_B factor
428 WHERE templ.DET_FACTOR_TEMPL_CODE = 'STCC'
429 AND factor.DETERMINING_FACTOR_CLASS_CODE ='TRX_INPUT_FACTOR'
430 AND factor.DETERMINING_FACTOR_CODE = 'TAX_CLASSIFICATION_CODE'
431 AND NOT EXISTS
432 (select 1 from ZX_DET_FACTOR_TEMPL_DTL DTL_TEMP2
433 where DET_FACTOR_TEMPL_ID = templ.DET_FACTOR_TEMPL_ID
434 and DETERMINING_FACTOR_CLASS_CODE = factor.DETERMINING_FACTOR_CLASS_CODE
435 and DETERMINING_FACTOR_CODE = factor.DETERMINING_FACTOR_CODE);
436
437 -- insert the template detail table for LEASE_MGT_RATE_DET_TEMPL
438 INSERT INTO ZX_DET_FACTOR_TEMPL_DTL
439 (
440 DETERMINING_FACTOR_CLASS_CODE,
441 DETERMINING_FACTOR_CQ_CODE ,
442 DETERMINING_FACTOR_CODE ,
443 REQUIRED_FLAG ,
444 RECORD_TYPE_CODE ,
445 CREATION_DATE ,
446 LAST_UPDATE_DATE ,
447 REQUEST_ID ,
448 PROGRAM_APPLICATION_ID ,
449 PROGRAM_ID ,
450 TAX_REGIME_DET_LEVEL_CODE ,
451 TAX_PARAMETER_CODE ,
452 PROGRAM_LOGIN_ID ,
453 DET_FACTOR_TEMPL_DTL_ID ,
454 DET_FACTOR_TEMPL_ID ,
458 OBJECT_VERSION_NUMBER
455 CREATED_BY ,
456 LAST_UPDATED_BY ,
457 LAST_UPDATE_LOGIN ,
459 )
460 SELECT
461 factor.DETERMINING_FACTOR_CLASS_CODE, --DETERMINING_FACTOR_CLASS_CODE
462 decode(factor.DETERMINING_FACTOR_CLASS_CODE,
463 'PARTY_FISCAL_CLASS', 'BILL_TO_PARTY',
464 NULL) , --DETERMINING_FACTOR_CQ_CODE
465 factor.DETERMINING_FACTOR_CODE , --DETERMINING_FACTOR_CODE
466 decode(factor.DETERMINING_FACTOR_CODE,
467 'TAX_CLASSIFICATION_CODE', 'Y',
468 'N') , --REQUIRED_FLAG
469 'MIGRATED' , --RECORD_TYPE_CODE
470 SYSDATE , --CREATION_DATE
471 SYSDATE , --LAST_UPDATE_DATE
472 factor.REQUEST_ID , --REQUEST_ID
473 factor.PROGRAM_APPLICATION_ID , --PROGRAM_APPLICATION_ID
474 factor.PROGRAM_ID , --PROGRAM_ID
475 NULL , --TAX_REGIME_DET_LEVEL_CODE
476 factor.TAX_PARAMETER_CODE , --TAX_PARAMETER_CODE
477 factor.PROGRAM_LOGIN_ID , --PROGRAM_LOGIN_ID
478 ZX_DET_FACTOR_TEMPL_DTL_S.nextval , --DET_FACTOR_TEMPL_DTL_ID
479 templ.DET_FACTOR_TEMPL_ID , --DET_FACTOR_TEMPL_ID
480 factor.CREATED_BY , --CREATED_BY
481 factor.LAST_UPDATED_BY , --LAST_UPDATED_BY
482 factor.LAST_UPDATE_LOGIN , --LAST_UPDATE_LOGIN
483 factor.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
484
485 FROM ZX_DET_FACTOR_TEMPL_B templ,
486 ZX_DETERMINING_FACTORS_B factor
487 WHERE templ.DET_FACTOR_TEMPL_CODE = 'LEASE_MGT_RATE_DET_TEMPL'
488 AND ((factor.DETERMINING_FACTOR_CLASS_CODE ='TRX_INPUT_FACTOR'
489 AND factor.DETERMINING_FACTOR_CODE IN ('TAX_CLASSIFICATION_CODE',
490 'USER_DEFINED_FISC_CLASS'))
491 OR (factor.DETERMINING_FACTOR_CLASS_CODE ='PARTY_FISCAL_CLASS'
492 AND factor.DETERMINING_FACTOR_CODE = 'LEASE_MGT_PTY_FISC_CLASS')
493 OR (factor.DETERMINING_FACTOR_CLASS_CODE ='PRODUCT_FISCAL_CLASS'
494 AND factor.DETERMINING_FACTOR_CODE = 'LEASE_MGT_PROD_FISC_CLASS')
495 )
496 AND NOT EXISTS
497 (select 1 from ZX_DET_FACTOR_TEMPL_DTL DTL_TEMP2
498 where DET_FACTOR_TEMPL_ID = templ.DET_FACTOR_TEMPL_ID
499 and DETERMINING_FACTOR_CLASS_CODE = factor.DETERMINING_FACTOR_CLASS_CODE
500 -- and DETERMINING_FACTOR_CQ_CODE = cqtemp.DETERMINING_FACTOR_CQ_CODE
501 and DETERMINING_FACTOR_CODE = factor.DETERMINING_FACTOR_CODE);
502
503 END create_template;
504
505 /*=========================================================================+
506 | PROCEDURE |
507 | create_condition_groups |
508 | |
509 | DESCRIPTION |
510 | This routine inserts data INTO ZX_CONDITION_GROUPS_B,_TL for each of |
511 | AP/PO default Hierarchy options defined in AP,PO system parameters |
512 | This routine has number of INSERT...SELECTs based on the AP,PO |
513 | Hierarchy setup.Each of the INSERT..SELECT is having UNION ALL of two|
514 | SELECT statements, |
515 | one for AP setup and another for PO setup |
516 | For Example,while processing the Supplier options check in the AP/PO |
517 | Hierarchy process,in INSERT..SELECT,one select statement will be for |
518 | AP supplier option and another for PO supplier option. |
519 | |
520 | SCOPE - PUBLIC |
521 | |
522 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
523 | |
524 | CALLED FROM |
525 | migrate_default_hierarchy |
526 | NOTES |
527 | |
528 | MODIFICATION HISTORY |
529 | 15-Jan-04 Srinivas Lokam Created. |
530 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions|
531 | in SELECT statements for handling |
532 | SYNC process. |
533 |=========================================================================*/
534
535 PROCEDURE create_condition_groups(p_name IN VARCHAR2 DEFAULT NULL) IS
536 BEGIN
537 IF PG_DEBUG = 'Y' THEN
538 arp_util_tax.debug('Create_Condition_Groups(+)');
539 END IF;
540
541 --Insert records into both CONDITION_GROUPS and CONDITIONS.
542 --Insert of Input tax classification codes
543 --Bug 4935978
544 --As part of this bug we are not going to prefix I_ to any condition group code
545 --We will directly insert the LOOKUP CODE for INPUT CLASSIFICATION lookup types
546
547 INSERT ALL
548 WHEN ( not exists
549 (select 1 from zx_condition_groups_b
550 where condition_group_code=l_condition_group_code)
551 ) THEN
552 INTO ZX_CONDITION_GROUPS_B
556 COUNTRY_CODE ,
553 (
554 CONDITION_GROUP_CODE ,
555 DET_FACTOR_TEMPL_CODE ,
557 MORE_THAN_MAX_COND_FLAG ,
558 ENABLED_FLAG ,
559 DETERMINING_FACTOR_CODE1 ,
560 TAX_PARAMETER_CODE1 ,
561 DATA_TYPE1_CODE ,
562 DETERMINING_FACTOR_CLASS1_CODE ,
563 DETERMINING_FACTOR_CQ1_CODE ,
564 OPERATOR1_CODE ,
565 ALPHANUMERIC_VALUE1 ,
566 RECORD_TYPE_CODE ,
567 CONDITION_GROUP_ID ,
568 CONSTRAINT_ID ,
569 CREATED_BY ,
570 CREATION_DATE ,
571 LAST_UPDATED_BY ,
572 LAST_UPDATE_DATE ,
573 LAST_UPDATE_LOGIN ,
574 REQUEST_ID ,
575 PROGRAM_APPLICATION_ID ,
576 PROGRAM_ID ,
577 PROGRAM_LOGIN_ID ,
578 OBJECT_VERSION_NUMBER
579 )
580 VALUES
581 (
582 l_condition_group_code ,
583 DET_FACTOR_TEMPL_CODE ,
584 COUNTRY_CODE ,
585 MORE_THAN_MAX_COND_FLAG ,
586 ENABLED_FLAG ,
587 DETERMINING_FACTOR_CODE1 ,
588 TAX_PARAMETER_CODE1 ,
589 DATA_TYPE1_CODE ,
590 DETERMINING_FACTOR_CLASS1_CODE ,
591 DETERMINING_FACTOR_CQ1_CODE ,
592 OPERATOR1_CODE ,
593 ALPHANUMERIC_VALUE1 ,
594 'MIGRATED' ,
595 zx_condition_groups_b_s.nextval ,
596 CONSTRAINT_ID ,
597 fnd_global.user_id ,
598 SYSDATE ,
599 fnd_global.user_id ,
600 SYSDATE ,
601 fnd_global.conc_login_id ,
602 fnd_global.conc_request_id ,
603 fnd_global.prog_appl_id ,
604 fnd_global.conc_program_id ,
605 fnd_global.conc_login_id ,
606 1
607 )
608 WHEN (not exists
609 (select 1 from zx_conditions
610 where condition_group_code = l_condition_group_code
611 and determining_factor_code = determining_factor_code1
612 and determining_factor_class_code =
613 determining_factor_class1_code)
614 ) THEN
615 INTO ZX_CONDITIONS
616 (
617 DETERMINING_FACTOR_CODE ,
618 CONDITION_GROUP_CODE ,
619 TAX_PARAMETER_CODE ,
620 DATA_TYPE_CODE ,
621 DETERMINING_FACTOR_CLASS_CODE ,
622 DETERMINING_FACTOR_CQ_CODE ,
623 OPERATOR_CODE ,
624 RECORD_TYPE_CODE ,
625 IGNORE_FLAG ,
626 ALPHANUMERIC_VALUE ,
627 CONDITION_ID ,
628 CREATED_BY ,
629 CREATION_DATE ,
630 LAST_UPDATED_BY ,
631 LAST_UPDATE_DATE ,
632 LAST_UPDATE_LOGIN ,
633 REQUEST_ID ,
634 PROGRAM_APPLICATION_ID ,
635 PROGRAM_ID ,
636 PROGRAM_LOGIN_ID ,
637 OBJECT_VERSION_NUMBER
638 )
639 VALUES
640 (
641 DETERMINING_FACTOR_CODE1 ,
642 l_condition_group_code ,
643 TAX_PARAMETER_CODE1 ,
644 DATA_TYPE1_CODE ,
645 DETERMINING_FACTOR_CLASS1_CODE ,
646 DETERMINING_FACTOR_CQ1_CODE ,
647 OPERATOR1_CODE ,
648 'MIGRATED' ,
649 'N' ,
650 ALPHANUMERIC_VALUE1 ,
651 zx_conditions_s.nextval ,
652 fnd_global.user_id ,
653 SYSDATE ,
654 fnd_global.user_id ,
655 SYSDATE ,
656 fnd_global.conc_login_id ,
657 fnd_global.conc_request_id ,
658 fnd_global.prog_appl_id ,
659 fnd_global.conc_program_id ,
660 fnd_global.conc_login_id ,
661 1
662 )
663 SELECT distinct
664 codes.name l_condition_group_code ,
665 'STCC' DET_FACTOR_TEMPL_CODE ,
666 NULL COUNTRY_CODE ,
667 'N' MORE_THAN_MAX_COND_FLAG,
668 'Y' ENABLED_FLAG , --Bug 5090631
669 'TAX_CLASSIFICATION_CODE' DETERMINING_FACTOR_CODE1 ,
670 'TAX_CLASSIFICATION_CODE' TAX_PARAMETER_CODE1 ,
671 'ALPHANUMERIC' DATA_TYPE1_CODE ,
672 'TRX_INPUT_FACTOR' DETERMINING_FACTOR_CLASS1_CODE,
673 NULL DETERMINING_FACTOR_CQ1_CODE ,
674 '=' OPERATOR1_CODE ,
675 codes.name
676 ALPHANUMERIC_VALUE1 ,
677 NULL CONSTRAINT_ID
678 FROM
679 ap_tax_codes_all codes --Bug 5061471
680 WHERE
681 codes.tax_type = 'TAX_GROUP'
685 --Bug 4935978
682 AND codes.name = nvl(p_name,codes.name);
683
684 -- Insert of Output tax classification codes
686 --As part of this bug we are not going to prefix I_ to any condition group code
687 --We will directly insert the LOOKUP CODE for OUTPUT CLASSIFICATION lookup types
688 --Tax Constraint Id will be appended if it is not null
689
690 INSERT ALL
691 WHEN ( not exists
692 (select 1 from zx_condition_groups_b
693 where condition_group_code=l_condition_group_code)
694 ) THEN
695 INTO ZX_CONDITION_GROUPS_B
696 (
697 CONDITION_GROUP_CODE ,
698 DET_FACTOR_TEMPL_CODE ,
699 COUNTRY_CODE ,
700 MORE_THAN_MAX_COND_FLAG ,
701 ENABLED_FLAG ,
702 DETERMINING_FACTOR_CODE1 ,
703 TAX_PARAMETER_CODE1 ,
704 DATA_TYPE1_CODE ,
705 DETERMINING_FACTOR_CLASS1_CODE ,
706 DETERMINING_FACTOR_CQ1_CODE ,
707 OPERATOR1_CODE ,
708 ALPHANUMERIC_VALUE1 ,
709 RECORD_TYPE_CODE ,
710 CONDITION_GROUP_ID ,
711 CONSTRAINT_ID ,
712 CREATED_BY ,
713 CREATION_DATE ,
714 LAST_UPDATED_BY ,
715 LAST_UPDATE_DATE ,
716 LAST_UPDATE_LOGIN ,
717 REQUEST_ID ,
718 PROGRAM_APPLICATION_ID ,
719 PROGRAM_ID ,
720 PROGRAM_LOGIN_ID ,
721 OBJECT_VERSION_NUMBER
722 )
723 VALUES
724 (
725 l_condition_group_code ,
726 DET_FACTOR_TEMPL_CODE ,
727 COUNTRY_CODE ,
728 MORE_THAN_MAX_COND_FLAG ,
729 ENABLED_FLAG ,
730 DETERMINING_FACTOR_CODE1 ,
731 TAX_PARAMETER_CODE1 ,
732 DATA_TYPE1_CODE ,
733 DETERMINING_FACTOR_CLASS1_CODE ,
734 DETERMINING_FACTOR_CQ1_CODE ,
735 OPERATOR1_CODE ,
736 ALPHANUMERIC_VALUE1 ,
737 'MIGRATED' ,
738 zx_condition_groups_b_s.nextval ,
739 CONSTRAINT_ID ,
740 fnd_global.user_id ,
741 SYSDATE ,
742 fnd_global.user_id ,
743 SYSDATE ,
744 fnd_global.conc_login_id ,
745 fnd_global.conc_request_id ,
746 fnd_global.prog_appl_id ,
747 fnd_global.conc_program_id ,
748 fnd_global.conc_login_id ,
749 1
750 )
751 WHEN (not exists
752 (select 1 from zx_conditions
753 where condition_group_code = l_condition_group_code
754 and determining_factor_code = determining_factor_code1
755 and determining_factor_class_code =
756 determining_factor_class1_code)
757 ) THEN
758 INTO ZX_CONDITIONS
759 (
760 DETERMINING_FACTOR_CODE ,
761 CONDITION_GROUP_CODE ,
762 TAX_PARAMETER_CODE ,
763 DATA_TYPE_CODE ,
764 DETERMINING_FACTOR_CLASS_CODE ,
765 DETERMINING_FACTOR_CQ_CODE ,
766 OPERATOR_CODE ,
767 RECORD_TYPE_CODE ,
768 IGNORE_FLAG ,
769 ALPHANUMERIC_VALUE ,
770 CONDITION_ID ,
771 CREATED_BY ,
772 CREATION_DATE ,
773 LAST_UPDATED_BY ,
774 LAST_UPDATE_DATE ,
775 LAST_UPDATE_LOGIN ,
776 REQUEST_ID ,
777 PROGRAM_APPLICATION_ID ,
778 PROGRAM_ID ,
779 PROGRAM_LOGIN_ID ,
780 OBJECT_VERSION_NUMBER
781 )
782 VALUES
783 (
784 DETERMINING_FACTOR_CODE1 ,
785 l_condition_group_code ,
786 TAX_PARAMETER_CODE1 ,
787 DATA_TYPE1_CODE ,
788 DETERMINING_FACTOR_CLASS1_CODE ,
789 DETERMINING_FACTOR_CQ1_CODE ,
790 OPERATOR1_CODE ,
791 'MIGRATED' ,
792 'N' ,
793 ALPHANUMERIC_VALUE1 ,
794 zx_conditions_s.nextval ,
795 fnd_global.user_id ,
796 SYSDATE ,
797 fnd_global.user_id ,
798 SYSDATE ,
799 fnd_global.conc_login_id ,
800 fnd_global.conc_request_id ,
801 fnd_global.prog_appl_id ,
802 fnd_global.conc_program_id ,
803 fnd_global.conc_login_id ,
804 1
805 )
806 SELECT
807 DISTINCT
808 SUBSTRB(ar_vat.tax_code,1, 40)
809 || decode(ar_vat.tax_constraint_id,
810 NULL, '', '~'||ar_vat.tax_constraint_id) l_condition_group_code,
811 'STCC' DET_FACTOR_TEMPL_CODE,
812 NULL COUNTRY_CODE ,
813 'N' MORE_THAN_MAX_COND_FLAG,
814 'Y' ENABLED_FLAG , --Bug 5090631
818 'TRX_INPUT_FACTOR' DETERMINING_FACTOR_CLASS1_CODE,
815 'TAX_CLASSIFICATION_CODE' DETERMINING_FACTOR_CODE1 ,
816 'TAX_CLASSIFICATION_CODE' TAX_PARAMETER_CODE1 ,
817 'ALPHANUMERIC' DATA_TYPE1_CODE ,
819 NULL DETERMINING_FACTOR_CQ1_CODE ,
820 '=' OPERATOR1_CODE ,
821 ar_vat.tax_code
822 ALPHANUMERIC_VALUE1 ,
823 ar_vat.TAX_CONSTRAINT_ID CONSTRAINT_ID
824 FROM
825 AR_VAT_TAX_ALL_B ar_vat --Bug 5061471
826 WHERE
827 ar_vat.tax_type IN ( 'TAX_GROUP','LOCATION')
828 OR EXISTS ( SELECT 1
829 FROM ar_system_parameters_all sys
830 WHERE ar_vat.set_of_books_id = sys.set_of_books_id
831 AND ar_vat.org_id = sys.org_id
832 AND sys.tax_method = 'SALES_TAX')
833 --Added following AND condition for Sync process
834 AND ar_vat.tax_code = nvl(p_name,ar_vat.tax_Code);
835
836 -- create condition set and conditions for the OKL migration
837 -- creat the separate condition sets for BILL_TO_PARTY
838 -- det_factor_cq_code
839
840 INSERT ALL
841 WHEN ( not exists
842 (select 1 from zx_condition_groups_b
843 where SUBSTR(condition_group_code, 1, 44) = SUBSTR(l_condition_group_code, 1,44)
844 and DET_FACTOR_TEMPL_CODE = l_det_factor_templ_code)
845 ) THEN
846 INTO ZX_CONDITION_GROUPS_B
847 (
848 CONDITION_GROUP_CODE ,
849 DET_FACTOR_TEMPL_CODE ,
850 COUNTRY_CODE ,
851 MORE_THAN_MAX_COND_FLAG ,
852 ENABLED_FLAG ,
853 DETERMINING_FACTOR_CODE1 ,
854 TAX_PARAMETER_CODE1 ,
855 DATA_TYPE1_CODE ,
856 DETERMINING_FACTOR_CLASS1_CODE ,
857 DETERMINING_FACTOR_CQ1_CODE ,
858 OPERATOR1_CODE ,
859 ALPHANUMERIC_VALUE1 ,
860
861 DETERMINING_FACTOR_CODE2 ,
862 TAX_PARAMETER_CODE2 ,
863 DATA_TYPE2_CODE ,
864 DETERMINING_FACTOR_CLASS2_CODE ,
865 DETERMINING_FACTOR_CQ2_CODE ,
866 OPERATOR2_CODE ,
867 ALPHANUMERIC_VALUE2 ,
868
869 DETERMINING_FACTOR_CODE3 ,
870 TAX_PARAMETER_CODE3 ,
871 DATA_TYPE3_CODE ,
872 DETERMINING_FACTOR_CLASS3_CODE ,
873 DETERMINING_FACTOR_CQ3_CODE ,
874 OPERATOR3_CODE ,
875 ALPHANUMERIC_VALUE3 ,
876
877 DETERMINING_FACTOR_CODE4 ,
878 TAX_PARAMETER_CODE4 ,
879 DATA_TYPE4_CODE ,
880 DETERMINING_FACTOR_CLASS4_CODE ,
881 DETERMINING_FACTOR_CQ4_CODE ,
882 OPERATOR4_CODE ,
883 ALPHANUMERIC_VALUE4 ,
884
885 RECORD_TYPE_CODE ,
886 CONDITION_GROUP_ID ,
887 CONSTRAINT_ID ,
888 CREATED_BY ,
889 CREATION_DATE ,
890 LAST_UPDATED_BY ,
891 LAST_UPDATE_DATE ,
892 LAST_UPDATE_LOGIN ,
893 REQUEST_ID ,
894 PROGRAM_APPLICATION_ID ,
895 PROGRAM_ID ,
896 PROGRAM_LOGIN_ID ,
897 OBJECT_VERSION_NUMBER
898 )
899 VALUES
900 (
901 l_condition_group_code ,
902 l_det_factor_templ_code ,
903 COUNTRY_CODE ,
904 MORE_THAN_MAX_COND_FLAG ,
905 ENABLED_FLAG ,
906 -- create first condition
907 DETERMINING_FACTOR_CODE1 ,
908 TAX_PARAMETER_CODE1 ,
909 DATA_TYPE1_CODE ,
910 DETERMINING_FACTOR_CLASS1_CODE ,
911 DETERMINING_FACTOR_CQ1_CODE ,
912 OPERATOR1_CODE ,
913 ALPHANUMERIC_VALUE1 ,
914
915 -- create second condition
916
917 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
918 THEN DETERMINING_FACTOR_CODE2
919 WHEN ALPHANUMERIC_VALUE2 IS NULL
920 AND ALPHANUMERIC_VALUE3 IS NOT NULL
921 THEN DETERMINING_FACTOR_CODE3
922 ELSE DETERMINING_FACTOR_CODE4
923 END), --DETERMINING_FACTOR_CODE2,
924
925 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
926 THEN TAX_PARAMETER_CODE2
927 WHEN ALPHANUMERIC_VALUE2 IS NULL
928 AND ALPHANUMERIC_VALUE3 IS NOT NULL
929 THEN TAX_PARAMETER_CODE3
930 ELSE TAX_PARAMETER_CODE4
931 END), --TAX_PARAMETER_CODE2,
932
933 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
934 THEN DATA_TYPE2_CODE
935 WHEN ALPHANUMERIC_VALUE2 IS NULL
936 AND ALPHANUMERIC_VALUE3 IS NOT NULL
937 THEN DATA_TYPE3_CODE
938 ELSE DATA_TYPE4_CODE
939 END), --DATA_TYPE2_CODE,
940
941 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
942 THEN DETERMINING_FACTOR_CLASS2_CODE
943 WHEN ALPHANUMERIC_VALUE2 IS NULL
944 AND ALPHANUMERIC_VALUE3 IS NOT NULL
945 THEN DETERMINING_FACTOR_CLASS3_CODE
946 ELSE DETERMINING_FACTOR_CLASS4_CODE
950 THEN DETERMINING_FACTOR_CQ2_CODE
947 END), --DETERMINING_FACTOR_CLASS2_CODE,
948
949 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
951 WHEN ALPHANUMERIC_VALUE2 IS NULL
952 AND ALPHANUMERIC_VALUE3 IS NOT NULL
953 THEN DETERMINING_FACTOR_CQ3_CODE
954 ELSE DETERMINING_FACTOR_CQ4_CODE
955 END), --DETERMINING_FACTOR_CQ2_CODE,
956
957 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
958 THEN OPERATOR2_CODE
959 WHEN ALPHANUMERIC_VALUE2 IS NULL
960 AND ALPHANUMERIC_VALUE3 IS NOT NULL
961 THEN OPERATOR3_CODE
962 ELSE OPERATOR4_CODE
963 END), --OPERATOR2_CODE,
964
965 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
966 THEN ALPHANUMERIC_VALUE2
967 WHEN ALPHANUMERIC_VALUE2 IS NULL
968 AND ALPHANUMERIC_VALUE3 IS NOT NULL
969 THEN ALPHANUMERIC_VALUE3
970 ELSE ALPHANUMERIC_VALUE4
971 END), --ALPHANUMERIC_VALUE2,
972
973 -- create third condition
974
975 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
976 AND ALPHANUMERIC_VALUE3 IS NOT NULL
977 THEN DETERMINING_FACTOR_CODE3
978 WHEN ALPHANUMERIC_VALUE2 IS NULL
979 AND ALPHANUMERIC_VALUE3 IS NULL
980 THEN NULL
981 ELSE DETERMINING_FACTOR_CODE4
982 END), --DETERMINING_FACTOR_CODE3,
983
984 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
985 AND ALPHANUMERIC_VALUE3 IS NOT NULL
986 THEN TAX_PARAMETER_CODE3
987 WHEN ALPHANUMERIC_VALUE2 IS NULL
988 AND ALPHANUMERIC_VALUE3 IS NULL
989 THEN NULL
990 ELSE TAX_PARAMETER_CODE4
991 END), --TAX_PARAMETER_CODE3,
992
993 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
994 AND ALPHANUMERIC_VALUE3 IS NOT NULL
995 THEN DATA_TYPE3_CODE
996 WHEN ALPHANUMERIC_VALUE2 IS NULL
997 AND ALPHANUMERIC_VALUE3 IS NULL
998 THEN NULL
999 ELSE DATA_TYPE4_CODE
1000 END), --DATA_TYPE3_CODE,
1001
1002 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1003 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1004 THEN DETERMINING_FACTOR_CLASS3_CODE
1005 WHEN ALPHANUMERIC_VALUE2 IS NULL
1006 AND ALPHANUMERIC_VALUE3 IS NULL
1007 THEN NULL
1008 ELSE DETERMINING_FACTOR_CLASS4_CODE
1009 END), --DETERMINING_FACTOR_CLASS3_CODE,
1010
1011 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1012 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1013 THEN DETERMINING_FACTOR_CQ3_CODE
1014 WHEN ALPHANUMERIC_VALUE2 IS NULL
1015 AND ALPHANUMERIC_VALUE3 IS NULL
1016 THEN NULL
1017 ELSE DETERMINING_FACTOR_CQ4_CODE
1018 END), --DETERMINING_FACTOR_CQ3_CODE,
1019
1020 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1021 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1022 THEN OPERATOR3_CODE
1023 WHEN ALPHANUMERIC_VALUE2 IS NULL
1024 AND ALPHANUMERIC_VALUE3 IS NULL
1025 THEN NULL
1026 ELSE OPERATOR4_CODE
1027 END), --OPERATOR3_CODE,
1028
1029 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1030 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1031 THEN ALPHANUMERIC_VALUE3
1032 WHEN ALPHANUMERIC_VALUE2 IS NULL
1033 AND ALPHANUMERIC_VALUE3 IS NULL
1034 THEN NULL
1035 ELSE ALPHANUMERIC_VALUE4
1036 END), --ALPHANUMERIC_VALUE3,
1037
1038 -- create forth condition
1039
1040 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1041 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1042 THEN DETERMINING_FACTOR_CODE4
1043 ELSE NULL
1044 END), --DETERMINING_FACTOR_CODE4,
1045
1046 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1047 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1048 THEN TAX_PARAMETER_CODE4
1049 ELSE NULL
1050 END), --TAX_PARAMETER_CODE4,
1051
1052 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1053 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1054 THEN DATA_TYPE4_CODE
1055 ELSE NULL
1056 END), --DATA_TYPE4_CODE,
1057
1058 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1059 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1060 THEN DETERMINING_FACTOR_CLASS4_CODE
1061 ELSE NULL
1062 END), --DETERMINING_FACTOR_CLASS4_CODE,
1063
1064 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1065 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1066 THEN DETERMINING_FACTOR_CQ4_CODE
1067 ELSE NULL
1068 END), --DETERMINING_FACTOR_CQ4_CODE,
1069
1070 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1071 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1072 THEN OPERATOR4_CODE
1073 ELSE NULL
1074 END), --OPERATOR4_CODE,
1075
1079 ELSE NULL
1076 (CASE WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1077 AND ALPHANUMERIC_VALUE3 IS NOT NULL
1078 THEN ALPHANUMERIC_VALUE4
1080 END), --ALPHANUMERIC_VALUE4,
1081
1082
1083 'MIGRATED' ,
1084 zx_condition_groups_b_s.nextval ,
1085 CONSTRAINT_ID ,
1086 fnd_global.user_id ,
1087 SYSDATE ,
1088 fnd_global.user_id ,
1089 SYSDATE ,
1090 fnd_global.conc_login_id ,
1091 fnd_global.conc_request_id ,
1092 fnd_global.prog_appl_id ,
1093 fnd_global.conc_program_id ,
1094 fnd_global.conc_login_id ,
1095 1
1096 )
1097 -- create conditions for tax_classification_code
1098 WHEN (not exists
1099 (select 1 from zx_conditions
1100 where condition_group_code = l_condition_group_code
1101 and determining_factor_code = determining_factor_code1
1102 and determining_factor_class_code =
1103 determining_factor_class1_code)
1104 ) THEN
1105 INTO ZX_CONDITIONS
1106 (
1107 DETERMINING_FACTOR_CODE ,
1108 CONDITION_GROUP_CODE ,
1109 TAX_PARAMETER_CODE ,
1110 DATA_TYPE_CODE ,
1111 DETERMINING_FACTOR_CLASS_CODE ,
1112 DETERMINING_FACTOR_CQ_CODE ,
1113 OPERATOR_CODE ,
1114 RECORD_TYPE_CODE ,
1115 IGNORE_FLAG ,
1116 ALPHANUMERIC_VALUE ,
1117 CONDITION_ID ,
1118 CREATED_BY ,
1119 CREATION_DATE ,
1120 LAST_UPDATED_BY ,
1121 LAST_UPDATE_DATE ,
1122 LAST_UPDATE_LOGIN ,
1123 REQUEST_ID ,
1124 PROGRAM_APPLICATION_ID ,
1125 PROGRAM_ID ,
1126 PROGRAM_LOGIN_ID ,
1127 OBJECT_VERSION_NUMBER
1128 )
1129 VALUES
1130 (
1131 DETERMINING_FACTOR_CODE1 ,
1132 l_condition_group_code ,
1133 TAX_PARAMETER_CODE1 ,
1134 DATA_TYPE1_CODE ,
1135 DETERMINING_FACTOR_CLASS1_CODE ,
1136 DETERMINING_FACTOR_CQ1_CODE ,
1137 OPERATOR1_CODE ,
1138 'MIGRATED' ,
1139 'N' ,
1140 ALPHANUMERIC_VALUE1 ,
1141 zx_conditions_s.nextval ,
1142 fnd_global.user_id ,
1143 SYSDATE ,
1144 fnd_global.user_id ,
1145 SYSDATE ,
1146 fnd_global.conc_login_id ,
1147 fnd_global.conc_request_id ,
1148 fnd_global.prog_appl_id ,
1149 fnd_global.conc_program_id ,
1150 fnd_global.conc_login_id ,
1151 1
1152 )
1153 -- create condition for product_fisc_classification
1154 WHEN ALPHANUMERIC_VALUE2 IS NOT NULL
1155 AND (not exists
1156 (select 1 from zx_conditions
1157 where condition_group_code = l_condition_group_code
1158 and determining_factor_code = determining_factor_code2
1159 and determining_factor_class_code =
1160 determining_factor_class2_code)
1161 ) THEN
1162 INTO ZX_CONDITIONS
1163 (
1164 DETERMINING_FACTOR_CODE ,
1165 CONDITION_GROUP_CODE ,
1166 TAX_PARAMETER_CODE ,
1167 DATA_TYPE_CODE ,
1168 DETERMINING_FACTOR_CLASS_CODE ,
1169 DETERMINING_FACTOR_CQ_CODE ,
1170 OPERATOR_CODE ,
1171 RECORD_TYPE_CODE ,
1172 IGNORE_FLAG ,
1173 ALPHANUMERIC_VALUE ,
1174 CONDITION_ID ,
1175 CREATED_BY ,
1176 CREATION_DATE ,
1177 LAST_UPDATED_BY ,
1178 LAST_UPDATE_DATE ,
1179 LAST_UPDATE_LOGIN ,
1180 REQUEST_ID ,
1181 PROGRAM_APPLICATION_ID ,
1182 PROGRAM_ID ,
1183 PROGRAM_LOGIN_ID ,
1184 OBJECT_VERSION_NUMBER
1185 )
1186 VALUES
1187 (
1188 DETERMINING_FACTOR_CODE2 ,
1189 l_condition_group_code ,
1190 TAX_PARAMETER_CODE2 ,
1191 DATA_TYPE2_CODE ,
1192 DETERMINING_FACTOR_CLASS2_CODE ,
1193 DETERMINING_FACTOR_CQ2_CODE ,
1194 OPERATOR2_CODE ,
1195 'MIGRATED' ,
1196 'N' ,
1197 ALPHANUMERIC_VALUE2 ,
1198 zx_conditions_s.nextval ,
1199 fnd_global.user_id ,
1200 SYSDATE ,
1201 fnd_global.user_id ,
1202 SYSDATE ,
1203 fnd_global.conc_login_id ,
1204 fnd_global.conc_request_id ,
1205 fnd_global.prog_appl_id ,
1206 fnd_global.conc_program_id ,
1207 fnd_global.conc_login_id ,
1208 1
1212 WHEN ALPHANUMERIC_VALUE3 IS NOT NULL
1209 )
1210
1211 -- create condition for trx_business_category_code
1213 AND (not exists
1214 (select 1 from zx_conditions
1215 where condition_group_code = l_condition_group_code
1216 and determining_factor_code = determining_factor_code3
1217 and determining_factor_class_code =
1218 determining_factor_class3_code)
1219 ) THEN
1220 INTO ZX_CONDITIONS
1221 (
1222 DETERMINING_FACTOR_CODE ,
1223 CONDITION_GROUP_CODE ,
1224 TAX_PARAMETER_CODE ,
1225 DATA_TYPE_CODE ,
1226 DETERMINING_FACTOR_CLASS_CODE ,
1227 DETERMINING_FACTOR_CQ_CODE ,
1228 OPERATOR_CODE ,
1229 RECORD_TYPE_CODE ,
1230 IGNORE_FLAG ,
1231 ALPHANUMERIC_VALUE ,
1232 CONDITION_ID ,
1233 CREATED_BY ,
1234 CREATION_DATE ,
1235 LAST_UPDATED_BY ,
1236 LAST_UPDATE_DATE ,
1237 LAST_UPDATE_LOGIN ,
1238 REQUEST_ID ,
1239 PROGRAM_APPLICATION_ID ,
1240 PROGRAM_ID ,
1241 PROGRAM_LOGIN_ID ,
1242 OBJECT_VERSION_NUMBER
1243 )
1244 VALUES
1245 (
1246 DETERMINING_FACTOR_CODE3 ,
1247 l_condition_group_code ,
1248 TAX_PARAMETER_CODE3 ,
1249 DATA_TYPE3_CODE ,
1250 DETERMINING_FACTOR_CLASS3_CODE ,
1251 DETERMINING_FACTOR_CQ3_CODE ,
1252 OPERATOR3_CODE ,
1253 'MIGRATED' ,
1254 'N' ,
1255 ALPHANUMERIC_VALUE3 ,
1256 zx_conditions_s.nextval ,
1257 fnd_global.user_id ,
1258 SYSDATE ,
1259 fnd_global.user_id ,
1260 SYSDATE ,
1261 fnd_global.conc_login_id ,
1262 fnd_global.conc_request_id ,
1263 fnd_global.prog_appl_id ,
1264 fnd_global.conc_program_id ,
1265 fnd_global.conc_login_id ,
1266 1
1267 )
1268
1269 -- create condition for party_fisc_classification
1270 WHEN ALPHANUMERIC_VALUE4 IS NOT NULL
1271 AND (not exists
1272 (select 1 from zx_conditions
1273 where condition_group_code = l_condition_group_code
1274 and determining_factor_code = determining_factor_code4
1275 and determining_factor_class_code =
1276 determining_factor_class4_code)
1277 ) THEN
1278 INTO ZX_CONDITIONS
1279 (
1280 DETERMINING_FACTOR_CODE ,
1281 CONDITION_GROUP_CODE ,
1282 TAX_PARAMETER_CODE ,
1283 DATA_TYPE_CODE ,
1284 DETERMINING_FACTOR_CLASS_CODE ,
1285 DETERMINING_FACTOR_CQ_CODE ,
1286 OPERATOR_CODE ,
1287 RECORD_TYPE_CODE ,
1288 IGNORE_FLAG ,
1289 ALPHANUMERIC_VALUE ,
1290 CONDITION_ID ,
1291 CREATED_BY ,
1292 CREATION_DATE ,
1293 LAST_UPDATED_BY ,
1294 LAST_UPDATE_DATE ,
1295 LAST_UPDATE_LOGIN ,
1296 REQUEST_ID ,
1297 PROGRAM_APPLICATION_ID ,
1298 PROGRAM_ID ,
1299 PROGRAM_LOGIN_ID ,
1300 OBJECT_VERSION_NUMBER
1301 )
1302 VALUES
1303 (
1304 DETERMINING_FACTOR_CODE4 ,
1305 l_condition_group_code ,
1306 TAX_PARAMETER_CODE4 ,
1307 DATA_TYPE4_CODE ,
1308 DETERMINING_FACTOR_CLASS4_CODE ,
1309 DETERMINING_FACTOR_CQ4_CODE ,
1310 OPERATOR4_CODE ,
1311 'MIGRATED' ,
1312 'N' ,
1313 ALPHANUMERIC_VALUE4 ,
1314 zx_conditions_s.nextval ,
1315 fnd_global.user_id ,
1316 SYSDATE ,
1317 fnd_global.user_id ,
1318 SYSDATE ,
1319 fnd_global.conc_login_id ,
1320 fnd_global.conc_request_id ,
1321 fnd_global.prog_appl_id ,
1322 fnd_global.conc_program_id ,
1323 fnd_global.conc_login_id ,
1324 1
1325 )
1326
1327 SELECT
1328 -- DISTINCT
1329 SUBSTRB(ar_vat.tax_code,1, 44)
1330 ||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_CONDITION_GROUPS_B_S') l_condition_group_code,
1331 'LEASE_MGT_RATE_DET_TEMPL' l_det_factor_templ_code,
1332 NULL COUNTRY_CODE ,
1333 'N' MORE_THAN_MAX_COND_FLAG,
1334 'Y' ENABLED_FLAG , --Bug 5090631
1335
1336 'TAX_CLASSIFICATION_CODE' DETERMINING_FACTOR_CODE1 ,
1337 'TAX_CLASSIFICATION_CODE' TAX_PARAMETER_CODE1 ,
1338 'ALPHANUMERIC' DATA_TYPE1_CODE ,
1342 ar_grp_tax.tax_code ALPHANUMERIC_VALUE1 ,
1339 'TRX_INPUT_FACTOR' DETERMINING_FACTOR_CLASS1_CODE,
1340 NULL DETERMINING_FACTOR_CQ1_CODE ,
1341 '=' OPERATOR1_CODE ,
1343
1344 NVL2(ar_grp.product_fisc_classification,'LEASE_MGT_PROD_FISC_CLASS', NULL) DETERMINING_FACTOR_CODE2 ,
1345 NVL2(ar_grp.product_fisc_classification,'PRODUCT_ID' , NULL) TAX_PARAMETER_CODE2 ,
1346 NVL2(ar_grp.product_fisc_classification,'ALPHANUMERIC', NULL) DATA_TYPE2_CODE ,
1347 NVL2(ar_grp.product_fisc_classification,'PRODUCT_FISCAL_CLASS', NULL) DETERMINING_FACTOR_CLASS2_CODE,
1348 NULL DETERMINING_FACTOR_CQ2_CODE ,
1349 NVL2(ar_grp.product_fisc_classification,'=' , NULL) OPERATOR2_CODE ,
1350 ar_grp.product_fisc_classification ALPHANUMERIC_VALUE2 ,
1351
1352 NVL2(ar_grp.trx_business_category_code,'USER_DEFINED_FISC_CLASS', NULL) DETERMINING_FACTOR_CODE3 ,
1353 NVL2(ar_grp.trx_business_category_code,'USER_DEFINED_FISC_CLASS', NULL) TAX_PARAMETER_CODE3 ,
1354 NVL2(ar_grp.trx_business_category_code, 'ALPHANUMERIC', NULL) DATA_TYPE3_CODE ,
1355 NVL2(ar_grp.trx_business_category_code,'TRX_INPUT_FACTOR', NULL) DETERMINING_FACTOR_CLASS3_CODE,
1356 NULL DETERMINING_FACTOR_CQ3_CODE ,
1357 NVL2(ar_grp.trx_business_category_code,'=', NULL) OPERATOR3_CODE ,
1358 ar_grp.trx_business_category_code ALPHANUMERIC_VALUE3 ,
1359
1360 NVL2(ar_grp.party_fisc_classification,'LEASE_MGT_PTY_FISC_CLASS', NULL) DETERMINING_FACTOR_CODE4 ,
1361 NVL2(ar_grp.party_fisc_classification,'BILL_TO_PARTY_TAX_PROF_ID', NULL) TAX_PARAMETER_CODE4 ,
1362 NVL2(ar_grp.party_fisc_classification,'ALPHANUMERIC', NULL) DATA_TYPE4_CODE ,
1363 NVL2(ar_grp.party_fisc_classification,'PARTY_FISCAL_CLASS', NULL) DETERMINING_FACTOR_CLASS4_CODE,
1364 NVL2(ar_grp.party_fisc_classification,'BILL_TO_PARTY', NULL) DETERMINING_FACTOR_CQ4_CODE ,
1365 NVL2(ar_grp.party_fisc_classification,'=', NULL) OPERATOR4_CODE ,
1366 ar_grp.party_fisc_classification ALPHANUMERIC_VALUE4 ,
1367
1368 ar_grp_tax.TAX_CONSTRAINT_ID CONSTRAINT_ID
1369 FROM
1370 AR_VAT_TAX_ALL_B ar_vat,
1371 AR_TAX_GROUP_CODES_ALL ar_grp,
1372 ar_vat_tax_all_b ar_grp_tax
1373 WHERE ar_grp_tax.tax_type = 'TAX_GROUP'
1374 AND ar_grp_tax.vat_tax_id = ar_grp.TAX_GROUP_ID
1375 AND ar_vat.vat_tax_id = ar_grp.tax_code_id
1376 AND ar_vat.tax_type <> 'TAX_GROUP'
1377 AND ar_grp.product_fisc_classification ||
1378 ar_grp.trx_business_category_code||
1379 ar_grp.party_fisc_classification IS NOT NULL
1380 --Added following AND condition for Sync process
1381 AND ar_vat.tax_code = nvl(p_name,ar_vat.tax_Code);
1382
1383
1384 INSERT INTO ZX_CONDITION_GROUPS_TL
1385 (
1386 LANGUAGE ,
1387 SOURCE_LANG ,
1388 CONDITION_GROUP_NAME ,
1389 CONDITION_GROUP_ID ,
1390 CREATION_DATE ,
1391 CREATED_BY ,
1392 LAST_UPDATE_DATE ,
1393 LAST_UPDATED_BY ,
1394 LAST_UPDATE_LOGIN
1395 )
1396 SELECT
1397 L.LANGUAGE_CODE ,
1398 userenv('LANG') ,
1399 CASE WHEN B.CONDITION_GROUP_CODE = UPPER(B.CONDITION_GROUP_CODE)
1400 THEN Initcap(B.CONDITION_GROUP_CODE)
1401 ELSE
1402 B.CONDITION_GROUP_CODE
1403 END
1404 ,
1405 B.CONDITION_GROUP_ID ,
1406 SYSDATE ,
1407 fnd_global.user_id ,
1408 SYSDATE ,
1409 fnd_global.user_id ,
1410 fnd_global.conc_login_id
1411 FROM
1412 FND_LANGUAGES L,
1413 ZX_CONDITION_GROUPS_B B
1414 WHERE
1415 L.INSTALLED_FLAG in ('I', 'B')
1416 AND RECORD_TYPE_CODE = 'MIGRATED'
1417 AND not exists
1418 (select 1
1419 from ZX_CONDITION_GROUPS_TL T
1420 where T.CONDITION_GROUP_ID = B.CONDITION_GROUP_ID
1421 and T.LANGUAGE = L.LANGUAGE_CODE);
1422 IF PG_DEBUG = 'Y' THEN
1423 arp_util_tax.debug('Create_Condition_Groups(-)');
1424 END IF;
1425 EXCEPTION
1426 WHEN OTHERS THEN
1427 IF PG_DEBUG = 'Y' THEN
1428 arp_util_tax.debug('EXCEPTION: Create_condition_groups ');
1429 arp_util_tax.debug(sqlerrm);
1430 arp_util_tax.debug('Create_Condition_Groups(-)');
1431 END IF;
1432 --app_exception.raise_exception;
1433 END create_condition_groups;
1434
1435
1436 /*=========================================================================+
1437 | PROCEDURE |
1438 | create_rules |
1439 | |
1440 | DESCRIPTION |
1441 | This routine inserts data into ZX_RULES_B/_TL by following the same |
1442 | logic used while inserting the data in ZX_CONDITION_GROUPS_B. |
1443 | |
1447 | |
1444 | SCOPE - PUBLIC |
1445 | |
1446 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1448 | CALLED FROM |
1449 | migrate_default_hierarchy |
1450 | NOTES |
1451 | |
1452 | MODIFICATION HISTORY |
1453 | 15-Jan-04 Srinivas Lokam Created. |
1454 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions|
1455 | in SELECT statements for handling |
1456 | SYNC process. |
1457 | |
1458 |=========================================================================*/
1459
1460 PROCEDURE create_rules(p_tax IN VARCHAR2 DEFAULT NULL) IS
1461 BEGIN
1462 IF PG_DEBUG = 'Y' THEN
1463 arp_util_tax.debug('Create_Rules(+)');
1464 END IF;
1465
1466
1467 --Rules for AP, AR Tax setup
1468
1469 /* Commented Bug : 5061471
1470 INSERT ALL
1471 WHEN (exists (select 1
1472 from zx_rates_b rates, FND_LOOKUP_VALUES codes
1473 where codes.language = userenv('LANG')
1474 and codes.view_application_id = 0
1475 and rates.tax_rate_code = NVL(codes.tag,codes.lookup_code)
1476 and codes.lookup_type IN('ZX_INPUT_CLASSIFICATIONS', 'ZX_OUTPUT_CLASSIFICATIONS')
1477 and rates.tax = L_TAX
1478 and rates.tax_regime_code = L_TAX_REGIME_CODE
1479 and rates.record_type_code = 'MIGRATED'
1480 )
1481 )
1482 THEN
1483 INTO ZX_RULES_B_TMP
1484 (
1485 TAX_RULE_ID ,
1486 TAX_RULE_CODE ,
1487 TAX ,
1488 TAX_REGIME_CODE ,
1489 SERVICE_TYPE_CODE ,
1490 APPLICATION_ID ,
1491 RECOVERY_TYPE_CODE ,
1492 PRIORITY ,
1493 SYSTEM_DEFAULT_FLAG ,
1494 EFFECTIVE_FROM ,
1495 EFFECTIVE_TO ,
1496 ENABLED_FLAG ,
1497 RECORD_TYPE_CODE ,
1498 DET_FACTOR_TEMPL_CODE ,
1499 CONTENT_OWNER_ID ,
1500 CREATED_BY ,
1501 CREATION_DATE ,
1502 LAST_UPDATED_BY ,
1503 LAST_UPDATE_DATE ,
1504 LAST_UPDATE_LOGIN ,
1505 REQUEST_ID ,
1506 PROGRAM_APPLICATION_ID ,
1507 PROGRAM_ID ,
1508 PROGRAM_LOGIN_ID ,
1509 OBJECT_VERSION_NUMBER
1510 )
1511 VALUES
1512 (
1513 zx_rules_b_s.nextval,--TAX_RULE_ID
1514 L_TAX ,--TAX_RULE_CODE
1515 L_TAX ,--TAX
1516 L_TAX_REGIME_CODE ,--TAX_REGIME_CODE
1517 'DET_DIRECT_RATE' ,--SERVICE_TYPE_CODE
1518 NULL ,--APPLICATION_ID
1519 NULL ,--RECOVERY_TYPE_CODE
1520 1 ,--PRIORITY
1521 'N' ,--SYSTEM_DEFAULT_FLAG : Bug 4590290
1522 EFFECTIVE_FROM ,
1523 EFFECTIVE_TO ,
1524 'Y' ,--ENABLED_FLAG
1525 'MIGRATED' ,--RECORD_TYPE_CODE
1526 'STCC' ,--DET_FACTOR_TEMPL_CODE
1527 CONTENT_OWNER_ID ,
1528 fnd_global.user_id ,
1529 SYSDATE ,
1530 fnd_global.user_id ,
1531 SYSDATE ,
1532 fnd_global.conc_login_id ,
1533 fnd_global.conc_request_id ,--Request Id
1534 fnd_global.prog_appl_id ,--Program Application ID
1535 fnd_global.conc_program_id ,--Program Id
1536 fnd_global.conc_login_id , --Program Login ID
1537 1
1538 )
1539 SELECT
1540 taxes.TAX L_TAX ,
1541 taxes.TAX_REGIME_CODE L_TAX_REGIME_CODE ,
1542 taxes.EFFECTIVE_FROM EFFECTIVE_FROM ,
1543 taxes.EFFECTIVE_TO EFFECTIVE_TO ,
1544 taxes.CONTENT_OWNER_ID
1545 FROM
1546 ZX_TAXES_B taxes
1547 WHERE
1548 taxes.TAX_TYPE_CODE NOT IN ('AWT','OFFSET')
1549 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
1550 AND taxes.tax_type_code <> 'LOCATION' --Bug Fix 4626074
1551 AND NOT EXISTS ( select 1
1552 from ZX_RULES_B_TMP rule
1553 where
1554 rule.CONTENT_OWNER_ID = taxes.CONTENT_OWNER_ID
1555 and rule.TAX_REGIME_CODE = taxes.TAX_REGIME_CODE
1556 and rule.TAX = taxes.TAX
1557 and rule.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE'
1558 and rule.RECOVERY_TYPE_CODE IS NULL
1559 and rule.TAX_RULE_CODE = taxes.TAX
1560 and rule.EFFECTIVE_FROM = taxes.EFFECTIVE_FROM
1561 and rule.PRIORITY = 1
1562 ) ;
1563 */
1564 --Bug : 5061471
1565 -- Create Direct Rate Rule for distinct tax_regime, tax and content_owner_id combination for both AP and AR
1566 INSERT ALL INTO ZX_RULES_B_TMP
1567 (
1568 TAX_RULE_ID ,
1569 TAX_RULE_CODE ,
1573 APPLICATION_ID ,
1570 TAX ,
1571 TAX_REGIME_CODE ,
1572 SERVICE_TYPE_CODE ,
1574 RECOVERY_TYPE_CODE ,
1575 PRIORITY ,
1576 SYSTEM_DEFAULT_FLAG ,
1577 EFFECTIVE_FROM ,
1578 EFFECTIVE_TO ,
1579 ENABLED_FLAG ,
1580 RECORD_TYPE_CODE ,
1581 DET_FACTOR_TEMPL_CODE ,
1582 CONTENT_OWNER_ID ,
1583 CREATED_BY ,
1584 CREATION_DATE ,
1585 LAST_UPDATED_BY ,
1586 LAST_UPDATE_DATE ,
1587 LAST_UPDATE_LOGIN ,
1588 REQUEST_ID ,
1589 PROGRAM_APPLICATION_ID ,
1590 PROGRAM_ID ,
1591 PROGRAM_LOGIN_ID ,
1592 OBJECT_VERSION_NUMBER
1593 )
1594 VALUES
1595 (
1596 zx_rules_b_s.nextval,--TAX_RULE_ID
1597 L_TAX ,--TAX_RULE_CODE
1598 L_TAX ,--TAX
1599 L_TAX_REGIME_CODE ,--TAX_REGIME_CODE
1600 'DET_DIRECT_RATE' ,--SERVICE_TYPE_CODE
1601 NULL ,--APPLICATION_ID
1602 NULL ,--RECOVERY_TYPE_CODE
1603 PRIORITY ,
1604 'N' ,--SYSTEM_DEFAULT_FLAG : Bug 4590290
1605 EFFECTIVE_FROM ,
1606 EFFECTIVE_TO ,
1607 'Y' ,--ENABLED_FLAG
1608 'MIGRATED' ,--RECORD_TYPE_CODE
1609 'STCC' ,--DET_FACTOR_TEMPL_CODE
1610 CONTENT_OWNER_ID ,
1611 fnd_global.user_id ,
1612 SYSDATE ,
1613 fnd_global.user_id ,
1614 SYSDATE ,
1615 fnd_global.conc_login_id ,
1616 fnd_global.conc_request_id ,--Request Id
1617 fnd_global.prog_appl_id ,--Program Application ID
1618 fnd_global.conc_program_id ,--Program Id
1619 fnd_global.conc_login_id , --Program Login ID
1620 1
1621 ) --Bug 5090631
1622 --Bug 5572117
1623 SELECT DISTINCT
1624 taxes.TAX L_TAX ,
1625 taxes.TAX_REGIME_CODE L_TAX_REGIME_CODE ,
1626 taxgrp.START_DATE EFFECTIVE_FROM ,
1627 taxgrp.END_DATE EFFECTIVE_TO ,
1628 taxes.CONTENT_OWNER_ID,
1629 (taxgrp.tax_group_id * 2) + taxgrp.DISPLAY_ORDER PRIORITY
1630 FROM
1631 ZX_TAXES_B taxes ,
1632 ZX_RATES_B rates,
1633 AR_TAX_GROUP_CODES_ALL taxgrp,
1634 ZX_ID_TCC_MAPPING_ALL idmap
1635 WHERE
1636 taxgrp.tax_code_id = idmap.tax_rate_code_id
1637 AND taxgrp.tax_group_type =idmap.source
1638 and taxgrp.org_id = idmap.org_id
1639 and idmap.tax_rate_code_id = decode(idmap.source, 'AR', rates.tax_rate_id, 'AP', rates.source_id)
1640 and rates.tax = taxes.TAX
1641 and rates.tax_regime_code = taxes.TAX_REGIME_CODE
1642 AND rates.content_owner_id = taxes.content_owner_id
1643 and rates.record_type_code = 'MIGRATED'
1644 AND taxes.TAX_TYPE_CODE NOT IN ('AWT','OFFSET')
1645 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
1646 AND taxes.tax_type_code <> 'LOCATION' --Bug Fix 4626074
1647 AND NOT EXISTS ( select 1
1648 from ZX_RULES_B_TMP rule
1649 where
1650 rule.CONTENT_OWNER_ID = taxes.CONTENT_OWNER_ID
1651 and rule.TAX_REGIME_CODE = taxes.TAX_REGIME_CODE
1652 and rule.TAX = taxes.TAX
1653 and rule.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE'
1654 and rule.RECOVERY_TYPE_CODE IS NULL
1655 and rule.TAX_RULE_CODE = taxes.TAX
1656 and rule.EFFECTIVE_FROM = taxgrp.START_DATE
1657 and rule.PRIORITY = (taxgrp.tax_group_id * 2) + taxgrp.DISPLAY_ORDER
1658 )
1659 union
1660 SELECT DISTINCT
1661 taxes.TAX L_TAX ,
1662 taxes.TAX_REGIME_CODE L_TAX_REGIME_CODE ,
1663 taxes.EFFECTIVE_FROM EFFECTIVE_FROM ,
1664 taxes.EFFECTIVE_TO EFFECTIVE_TO ,
1665 taxes.CONTENT_OWNER_ID ,
1666 1 PRIORITY
1667
1668 FROM
1669 ZX_TAXES_B taxes
1670 WHERE
1671 taxes.TAX_TYPE_CODE NOT IN ('AWT','OFFSET')
1672 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
1673 AND taxes.tax_type_code <> 'LOCATION' --Bug Fix 4626074
1674 and exists ( select 1
1675 from zx_rates_b rates,zx_id_tcc_mapping_all idmap,ar_system_parameters_all sys
1676 where idmap.ledger_id = sys.set_of_books_id
1677 AND idmap.org_id = sys.org_id
1678 AND idmap.source = 'AR'
1679 AND sys.tax_method = 'SALES_TAX'
1680 and idmap.tax_rate_code_id = rates.tax_rate_id
1681 and rates.tax = taxes.TAX
1682 and rates.tax_regime_code = taxes.TAX_REGIME_CODE
1683 AND rates.content_owner_id = taxes.content_owner_id
1684 and rates.record_type_code = 'MIGRATED' )
1685 AND NOT EXISTS ( select 1
1686 from ZX_RULES_B_TMP rule
1687 where
1688 rule.CONTENT_OWNER_ID = taxes.CONTENT_OWNER_ID
1689 and rule.TAX_REGIME_CODE = taxes.TAX_REGIME_CODE
1690 and rule.TAX = taxes.TAX
1691 and rule.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE'
1692 and rule.RECOVERY_TYPE_CODE IS NULL
1693 and rule.TAX_RULE_CODE = taxes.TAX
1694 and rule.EFFECTIVE_FROM = taxes.EFFECTIVE_FROM
1695 and rule.PRIORITY = 1
1696 )
1697 ;
1701
1698
1699 --Create Applicability Rules For Location Based Taxes , Refer Bug 4910386
1700 --Refer Bug 4935978 for further modificatiions
1702 INSERT ALL
1703 INTO ZX_RULES_B_TMP
1704 (
1705 TAX_RULE_ID ,
1706 TAX_RULE_CODE ,
1707 TAX ,
1708 TAX_REGIME_CODE ,
1709 SERVICE_TYPE_CODE ,
1710 APPLICATION_ID ,
1711 RECOVERY_TYPE_CODE ,
1712 PRIORITY ,
1713 SYSTEM_DEFAULT_FLAG ,
1714 EFFECTIVE_FROM ,
1715 EFFECTIVE_TO ,
1716 ENABLED_FLAG ,
1717 RECORD_TYPE_CODE ,
1718 DET_FACTOR_TEMPL_CODE ,
1719 CONTENT_OWNER_ID ,
1720 CREATED_BY ,
1721 CREATION_DATE ,
1722 LAST_UPDATED_BY ,
1723 LAST_UPDATE_DATE ,
1724 LAST_UPDATE_LOGIN ,
1725 REQUEST_ID ,
1726 PROGRAM_APPLICATION_ID ,
1727 PROGRAM_ID ,
1728 PROGRAM_LOGIN_ID ,
1729 OBJECT_VERSION_NUMBER
1730 )
1731 VALUES
1732 (
1733 zx_rules_b_s.nextval,--TAX_RULE_ID
1734 TAX ,--TAX_RULE_CODE
1735 TAX ,--TAX
1736 TAX_REGIME_CODE ,--REGIME
1737 'DET_DIRECT_RATE' , -- SERVICE_TYPE_CODE --Bug 5385949
1738 NULL ,--APPLICATION_ID
1739 NULL ,--RECOVERY_TYPE_CODE
1740 PRIORITY ,
1741 'N' ,--SYSTEM_DEFAULT_FLAG : Bug 4590290
1742 EFFECTIVE_FROM ,
1743 EFFECTIVE_TO ,
1744 ENABLED_FLAG ,
1745 'MIGRATED' ,--RECORD_TYPE_CODE
1746 'STCC' ,--DET_FACTOR_TEMPL_CODE
1747 CONTENT_OWNER_ID ,
1748 fnd_global.user_id ,
1749 SYSDATE ,
1750 fnd_global.user_id ,
1751 SYSDATE ,
1752 fnd_global.conc_login_id ,
1753 fnd_global.conc_request_id ,--Request Id
1754 fnd_global.prog_appl_id ,--Program Application ID
1755 fnd_global.conc_program_id ,--Program Id
1756 fnd_global.conc_login_id ,--Program Login ID
1757 1
1758 )
1759 SELECT
1760 taxes.TAX TAX ,
1761 taxes.TAX_REGIME_CODE TAX_REGIME_CODE ,
1762 taxes.EFFECTIVE_FROM EFFECTIVE_FROM ,
1763 taxes.EFFECTIVE_TO EFFECTIVE_TO ,
1764 ptp.party_tax_profile_id CONTENT_OWNER_ID,
1765 -- nvl(vat.enabled_flag,'Y') ENABLED_FLAG
1766 'Y' ENABLED_FLAG, -- Bug 5209434
1767 1 PRIORITY
1768 FROM
1769 ZX_TAXES_B taxes,
1770 -- AR_VAT_TAX_ALL_B vat,
1771 zx_party_tax_profile ptp,
1772 ar_system_parameters_all sys
1773 WHERE
1774 taxes.RECORD_TYPE_CODE = 'MIGRATED'
1775 AND taxes.tax_type_code = 'LOCATION'
1776 AND taxes.live_for_applicability_flag = 'Y'
1777 AND taxes.content_owner_id = -99
1778 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
1779 /*AND vat.tax_type = 'LOCATION'
1780 AND vat.set_of_books_id = sys.set_of_books_id
1781 AND vat.org_id = sys.org_id*/
1782 AND sys.org_id = ptp.party_id
1783 AND ptp.party_type_code = 'OU'
1784 -- Added following AND condition for Sync process
1785 AND taxes.tax = nvl(p_tax,taxes.tax)
1786 -- Bug 5209434
1787 AND EXISTS (
1788 SELECT 1 FROM ar_vat_tax_all_b vat WHERE vat.tax_type = 'LOCATION'
1789 AND vat.set_of_books_id = sys.set_of_books_id
1790 AND vat.org_id = sys.org_id
1791 AND vat.enabled_flag = 'Y'
1792 )
1793 AND not exists (select 1
1794 from zx_rules_b
1795 where tax_rule_code = taxes.tax
1796 and effective_from = taxes.effective_from
1797 and content_owner_id = ptp.party_tax_profile_id
1798 and service_type_code = 'DET_DIRECT_RATE' --Bug 5385949
1799 and tax_regime_code = taxes.tax_regime_code
1800 and tax = taxes.tax
1801 and recovery_type_code IS NULL
1802 and priority = 1
1803 )
1804 UNION
1805 SELECT
1806 taxes.TAX TAX ,
1807 taxes.TAX_REGIME_CODE TAX_REGIME_CODE ,
1808 taxgrp.START_DATE EFFECTIVE_FROM ,
1809 taxgrp.END_DATE EFFECTIVE_TO ,
1810 ptp.party_tax_profile_id CONTENT_OWNER_ID,
1811 -- nvl(vat.enabled_flag,'Y') ENABLED_FLAG
1812 'Y' ENABLED_FLAG, -- Bug 5209434
1813 (taxgrp.tax_group_id * 2) + taxgrp.DISPLAY_ORDER PRIORITY
1814 FROM
1815 ZX_TAXES_B taxes,
1816 AR_VAT_TAX_ALL_B vat,
1817 zx_party_tax_profile ptp,
1818 ar_system_parameters_all sys,
1819 AR_TAX_GROUP_CODES_ALL taxgrp
1820
1821 WHERE
1822 taxes.RECORD_TYPE_CODE = 'MIGRATED'
1823 AND taxes.tax_type_code = 'LOCATION'
1824 AND taxes.live_for_applicability_flag = 'Y'
1825 AND taxes.content_owner_id = -99
1826 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
1827 AND vat.tax_type = 'LOCATION'
1828 AND vat.set_of_books_id = sys.set_of_books_id
1829 AND vat.org_id = sys.org_id
1830 AND vat.vat_tax_id = taxgrp.tax_code_id
1831 AND vat.enabled_flag = 'Y'
1835 AND taxes.tax = nvl(p_tax,taxes.tax)
1832 AND sys.org_id = ptp.party_id
1833 AND ptp.party_type_code = 'OU'
1834 -- Added following AND condition for Sync process
1836 -- Bug 5209434
1837 /*AND EXISTS (
1838 SELECT 1 FROM ar_vat_tax_all_b vat
1839 WHERE vat.tax_type = 'LOCATION'
1840 AND vat.set_of_books_id = sys.set_of_books_id
1841 AND vat.org_id = sys.org_id
1842 AND vat.enabled_flag = 'Y'
1843 AND vat.vat_tax_id = taxgrp.tax_code_id
1844 )
1845 */
1846 AND not exists (select 1
1847 from zx_rules_b
1848 where tax_rule_code = taxes.tax
1849 and effective_from = taxes.effective_from
1850 and content_owner_id = ptp.party_tax_profile_id
1851 and service_type_code = 'DET_DIRECT_RATE' --Bug 5385949
1852 and tax_regime_code = taxes.tax_regime_code
1853 and tax = taxes.tax
1854 and recovery_type_code IS NULL
1855 and PRIORITY = (taxgrp.tax_group_id * 2) + taxgrp.DISPLAY_ORDER
1856 );
1857
1858 -- Create Applicablity Rule for all the tax codes in the leasing tax group with
1859 -- at least one not NULL PFC, PTFC, TBC
1860
1861 -- Bug : 5147341
1862 -- Create Rate Determination Rule for location based taxes for OKL migration
1863 -- even though there can be VAT taxes with leasing flag as 'Y', but for these taxes
1864 -- no multiple rate will be defined, hence no need to create the rate det rules.
1865
1866 INSERT ALL INTO ZX_RULES_B_TMP
1867 (
1868 TAX_RULE_ID ,
1869 TAX_RULE_CODE ,
1870 TAX ,
1871 TAX_REGIME_CODE ,
1872 SERVICE_TYPE_CODE ,
1873 APPLICATION_ID ,
1874 RECOVERY_TYPE_CODE ,
1875 PRIORITY ,
1876 SYSTEM_DEFAULT_FLAG ,
1877 EFFECTIVE_FROM ,
1878 EFFECTIVE_TO ,
1879 ENABLED_FLAG ,
1880 RECORD_TYPE_CODE ,
1881 DET_FACTOR_TEMPL_CODE ,
1882 CONTENT_OWNER_ID ,
1883 CREATED_BY ,
1884 CREATION_DATE ,
1885 LAST_UPDATED_BY ,
1886 LAST_UPDATE_DATE ,
1887 LAST_UPDATE_LOGIN ,
1888 REQUEST_ID ,
1889 PROGRAM_APPLICATION_ID ,
1890 PROGRAM_ID ,
1891 PROGRAM_LOGIN_ID ,
1892 OBJECT_VERSION_NUMBER
1893 )
1894 VALUES
1895 (
1896 zx_rules_b_s.nextval,--TAX_RULE_ID
1897 L_TAX ,--TAX_RULE_CODE
1898 L_TAX ,--TAX
1899 L_TAX_REGIME_CODE ,--TAX_REGIME_CODE
1900 l_service_type_code ,--SERVICE_TYPE_CODE
1901 NULL ,--APPLICATION_ID
1902 NULL ,--RECOVERY_TYPE_CODE
1903 1 ,--PRIORITY
1904 'N' ,--SYSTEM_DEFAULT_FLAG : Bug 4590290
1905 EFFECTIVE_FROM ,
1906 EFFECTIVE_TO ,
1907 'Y' ,--ENABLED_FLAG
1908 'MIGRATED' ,--RECORD_TYPE_CODE
1909 'LEASE_MGT_RATE_DET_TEMPL' ,--DET_FACTOR_TEMPL_CODE
1910 CONTENT_OWNER_ID ,
1911 fnd_global.user_id ,
1912 SYSDATE ,
1913 fnd_global.user_id ,
1914 SYSDATE ,
1915 fnd_global.conc_login_id ,
1916 fnd_global.conc_request_id ,--Request Id
1917 fnd_global.prog_appl_id ,--Program Application ID
1918 fnd_global.conc_program_id ,--Program Id
1919 fnd_global.conc_login_id , --Program Login ID
1920 1
1921 ) --Bug 5090631
1922 SELECT DISTINCT
1923 taxes.TAX L_TAX ,
1924 taxes.TAX_REGIME_CODE L_TAX_REGIME_CODE ,
1925 taxes.EFFECTIVE_FROM EFFECTIVE_FROM ,
1926 taxes.EFFECTIVE_TO EFFECTIVE_TO ,
1927 taxes.CONTENT_OWNER_ID ,
1928 srvtype.service_type_code l_service_type_code
1929 FROM
1930 ZX_TAXES_B taxes,
1931 (SELECT 'DET_APPLICABLE_TAXES' service_type_code
1932 FROM DUAL
1933 UNION
1934 SELECT 'DET_TAX_RATE' service_type_code
1935 FROM DUAL ) srvtype
1936 WHERE
1937 taxes.TAX_TYPE_CODE NOT IN ('AWT','OFFSET','LOCATION')
1938 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
1939 AND EXISTS (SELECT 1
1940 FROM zx_rates_b rates,
1941 ar_tax_group_codes_all taxgrp,
1942 ar_vat_tax_all tax
1943 WHERE taxgrp.tax_group_type = 'AR'
1944 AND taxgrp.tax_code_id = tax.vat_tax_id
1945 AND taxgrp.org_id = tax.org_id
1946 AND tax.vat_tax_id = rates.tax_rate_id
1947 AND rates.tax = taxes.tax
1948 AND rates.tax_regime_code = taxes.tax_regime_code
1949 AND rates.content_owner_id = taxes.content_owner_id
1950 AND rates.record_type_code = 'MIGRATED'
1951 AND taxgrp.product_fisc_classification ||
1952 taxgrp.trx_business_category_code ||
1953 taxgrp.party_fisc_classification IS NOT NULL
1954 )
1955 AND NOT EXISTS ( select 1
1956 from ZX_RULES_B_TMP rule
1957 where
1958 rule.CONTENT_OWNER_ID = taxes.CONTENT_OWNER_ID
1959 and rule.TAX_REGIME_CODE = taxes.TAX_REGIME_CODE
1963 and rule.TAX_RULE_CODE = taxes.TAX
1960 and rule.TAX = taxes.TAX
1961 and rule.SERVICE_TYPE_CODE = srvtype.service_type_code
1962 and rule.RECOVERY_TYPE_CODE IS NULL
1964 and rule.EFFECTIVE_FROM = taxes.EFFECTIVE_FROM
1965 and rule.PRIORITY = 1
1966 )
1967 UNION
1968 SELECT DISTINCT
1969 taxes.TAX L_TAX ,
1970 taxes.TAX_REGIME_CODE L_TAX_REGIME_CODE ,
1971 taxes.EFFECTIVE_FROM EFFECTIVE_FROM ,
1972 taxes.EFFECTIVE_TO EFFECTIVE_TO ,
1973 taxes.CONTENT_OWNER_ID,
1974 srvtype.service_type_code l_service_type_code
1975 FROM
1976 ZX_TAXES_B taxes,
1977 zx_party_tax_profile ptp,
1978 ar_system_parameters_all sys,
1979 (SELECT 'DET_APPLICABLE_TAXES' service_type_code
1980 FROM DUAL
1981 UNION
1982 SELECT 'DET_TAX_RATE' service_type_code
1983 FROM DUAL ) srvtype
1984
1985 WHERE taxes.RECORD_TYPE_CODE = 'MIGRATED'
1986 AND taxes.live_for_applicability_flag = 'Y' -- add to filter location taxes defined in 11i
1987 AND taxes.tax_type_code = 'LOCATION'
1988 AND taxes.content_owner_id = -99
1989 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
1990 AND sys.org_id = ptp.party_id
1991 AND ptp.party_type_code = 'OU'
1992 -- Added following AND condition for Sync process
1993 AND taxes.tax = nvl(p_tax,taxes.tax)
1994 -- only create the rate determining rules for the tax codes in the tax group with
1995 -- at least one not NULL PFC, PTFC, TBC and not the migrated disabled leasing location taxes
1996 AND EXISTS (SELECT 1
1997 FROM zx_rates_b rates,
1998 ar_tax_group_codes_all taxgrp,
1999 ar_vat_tax_all tax
2000 WHERE taxgrp.tax_group_type = 'AR'
2001 AND taxgrp.tax_code_id = tax.vat_tax_id
2002 AND taxgrp.org_id = tax.org_id
2003 AND tax.vat_tax_id = rates.tax_rate_id
2004 AND tax.tax_type = 'LOCATION'
2005 AND rates.tax <> taxes.tax -- not create rule for the disabled taxes migrated for the location based tax code
2006 AND rates.tax_regime_code = taxes.tax_regime_code
2007 AND rates.content_owner_id = taxes.content_owner_id
2008 AND rates.record_type_code = 'MIGRATED'
2009 AND taxgrp.product_fisc_classification ||
2010 taxgrp.trx_business_category_code ||
2011 taxgrp.party_fisc_classification IS NOT NULL
2012 )
2013 AND not exists (select 1
2014 from zx_rules_b
2015 where tax_rule_code = taxes.tax
2016 and effective_from = taxes.effective_from
2017 and content_owner_id = ptp.party_tax_profile_id
2018 and service_type_code = srvtype.service_type_code
2019 and tax_regime_code = taxes.tax_regime_code
2020 and tax = taxes.tax
2021 and recovery_type_code IS NULL
2022 and priority = 1
2023 );
2024
2025
2026 INSERT INTO ZX_RULES_TL
2027 (
2028 LANGUAGE ,
2029 SOURCE_LANG ,
2030 TAX_RULE_NAME ,
2031 TAX_RULE_ID ,
2032 CREATION_DATE ,
2033 CREATED_BY ,
2034 LAST_UPDATE_DATE ,
2035 LAST_UPDATED_BY ,
2036 LAST_UPDATE_LOGIN
2037 )
2038
2039 SELECT
2040 L.LANGUAGE_CODE ,
2041 userenv('LANG') ,
2042 CASE WHEN B.TAX_RULE_CODE = UPPER(B.TAX_RULE_CODE)
2043 THEN Initcap(B.TAX_RULE_CODE)
2044 ELSE
2045 B.TAX_RULE_CODE
2046 END ,
2047 B.TAX_RULE_ID ,
2048 SYSDATE ,
2049 fnd_global.user_id ,
2050 SYSDATE ,
2051 fnd_global.user_id ,
2052 fnd_global.conc_login_id
2053 FROM
2054 FND_LANGUAGES L,
2055 ZX_RULES_B B
2056 WHERE
2057 L.INSTALLED_FLAG in ('I', 'B')
2058 AND RECORD_TYPE_CODE = 'MIGRATED'
2059 AND not exists
2060 (select NULL
2061 from ZX_RULES_TL T
2062 where T.TAX_RULE_ID = B.TAX_RULE_ID
2063 and T.LANGUAGE = L.LANGUAGE_CODE);
2064
2065
2066 --Bug : 5090631 : Added to update the DIRECT_RATE_RULE_FLAG to 'Y' for all tax,regime,contentOwners for which
2067 -- direct rate rules have been created.
2068
2069 update zx_taxes_b_tmp tax
2070 set tax.DIRECT_RATE_RULE_FLAG = 'Y'
2071 where exists
2072 ( select 1
2073 from zx_rules_b rule
2074 where rule.content_owner_id = tax.content_owner_id
2075 and rule.tax_regime_code = tax.tax_regime_code
2076 and rule.tax = tax.tax
2077 and rule.record_type_code = 'MIGRATED'
2078 and rule.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE'
2079 and rule.recovery_type_code is NULL
2080 and rule.tax_rule_code = tax.tax );
2081 -- and rule.priority = 1);
2082
2083 -- bug fix: 5548613 update the DIRECT_RATE_RULE_FLAG to 'Y' for location based taxes which have the direct rate rule migrated
2084 update zx_taxes_b_tmp tax
2085 set tax.DIRECT_RATE_RULE_FLAG = 'Y'
2086 where tax.tax_type_code = 'LOCATION'
2087 and tax.RECORD_TYPE_CODE = 'MIGRATED'
2091 ( select 1
2088 and tax.live_for_applicability_flag = 'Y' -- add to filter location taxes defined in 11i
2089 and tax.content_owner_id = -99
2090 and exists
2092 from zx_rules_b rule
2093 where rule.tax_regime_code = tax.tax_regime_code
2094 and rule.tax = tax.tax
2095 and rule.record_type_code = 'MIGRATED'
2096 and rule.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE'
2097 and rule.recovery_type_code is NULL
2098 and rule.tax_rule_code = tax.tax);
2099 -- and rule.priority = 1);
2100 -- bug fix: 5548613 end
2101
2102 -- Added to update the TAX_RATE_RULE_FLAG to 'Y' for all tax,regime,contentOwners for which
2103 -- direct rate rules have been created.
2104
2105 update zx_taxes_b_tmp tax
2106 set tax.TAX_RATE_RULE_FLAG = 'Y'
2107 where exists
2108 ( select 1
2109 from zx_rules_b rule
2110 where rule.content_owner_id = tax.content_owner_id
2111 and rule.tax_regime_code = tax.tax_regime_code
2112 and rule.tax = tax.tax
2113 and rule.record_type_code = 'MIGRATED'
2114 and rule.SERVICE_TYPE_CODE = 'DET_TAX_RATE'
2115 and rule.recovery_type_code is NULL
2116 and rule.tax_rule_code = tax.tax
2117 and rule.priority = 1);
2118
2119 --
2120 -- Added to update the APPLICABILITY_RULE to 'Y' for all tax,regime,contentOwners for which
2121 -- applicability rules have been created.
2122
2123 update zx_taxes_b_tmp tax
2124 set tax.APPLICABILITY_RULE_FLAG = 'Y'
2125 where exists
2126 ( select 1
2127 from zx_rules_b rule
2128 where rule.content_owner_id = tax.content_owner_id
2129 and rule.tax_regime_code = tax.tax_regime_code
2130 and rule.tax = tax.tax
2131 and rule.record_type_code = 'MIGRATED'
2132 and rule.SERVICE_TYPE_CODE = 'DET_APPLICABLE_TAXES'
2133 and rule.recovery_type_code is NULL
2134 and rule.tax_rule_code = tax.tax
2135 and rule.priority = 1);
2136
2137 -- bug fix: 5548613: copy the location based taxes
2138 -- which has an applicability rule defined.
2139
2140 INSERT INTO ZX_TAXES_B (
2141 TAX
2142 ,EFFECTIVE_FROM
2143 ,EFFECTIVE_TO
2144 ,TAX_REGIME_CODE
2145 ,TAX_TYPE_CODE
2146 ,ALLOW_MANUAL_ENTRY_FLAG
2147 ,ALLOW_TAX_OVERRIDE_FLAG
2148 ,MIN_TXBL_BSIS_THRSHLD
2149 ,MAX_TXBL_BSIS_THRSHLD
2150 ,MIN_TAX_RATE_THRSHLD
2151 ,MAX_TAX_RATE_THRSHLD
2152 ,MIN_TAX_AMT_THRSHLD
2153 ,MAX_TAX_AMT_THRSHLD
2154 ,COMPOUNDING_PRECEDENCE
2155 ,PERIOD_SET_NAME
2156 ,EXCHANGE_RATE_TYPE
2157 ,TAX_CURRENCY_CODE
2158 ,TAX_PRECISION
2159 ,MINIMUM_ACCOUNTABLE_UNIT
2160 ,ROUNDING_RULE_CODE
2161 ,TAX_STATUS_RULE_FLAG
2162 ,TAX_RATE_RULE_FLAG
2163 ,DEF_PLACE_OF_SUPPLY_TYPE_CODE
2164 ,PLACE_OF_SUPPLY_RULE_FLAG
2165 ,DIRECT_RATE_RULE_FLAG
2166 ,APPLICABILITY_RULE_FLAG
2167 ,TAX_CALC_RULE_FLAG
2168 ,TXBL_BSIS_THRSHLD_FLAG
2169 ,TAX_RATE_THRSHLD_FLAG
2170 ,TAX_AMT_THRSHLD_FLAG
2171 ,TAXABLE_BASIS_RULE_FLAG
2172 ,DEF_INCLUSIVE_TAX_FLAG
2173 ,THRSHLD_GROUPING_LVL_CODE
2174 ,HAS_OTHER_JURISDICTIONS_FLAG
2175 ,ALLOW_EXEMPTIONS_FLAG
2176 ,ALLOW_EXCEPTIONS_FLAG
2177 ,ALLOW_RECOVERABILITY_FLAG
2178 ,DEF_TAX_CALC_FORMULA
2179 ,TAX_INCLUSIVE_OVERRIDE_FLAG
2180 ,DEF_TAXABLE_BASIS_FORMULA
2181 ,DEF_REGISTR_PARTY_TYPE_CODE
2182 ,REGISTRATION_TYPE_RULE_FLAG
2183 ,REPORTING_ONLY_FLAG
2184 ,AUTO_PRVN_FLAG
2185 ,LIVE_FOR_PROCESSING_FLAG
2186 ,HAS_DETAIL_TB_THRSHLD_FLAG
2187 ,HAS_TAX_DET_DATE_RULE_FLAG
2188 ,HAS_EXCH_RATE_DATE_RULE_FLAG
2189 ,HAS_TAX_POINT_DATE_RULE_FLAG
2190 ,PRINT_ON_INVOICE_FLAG
2191 ,USE_LEGAL_MSG_FLAG
2192 ,CALC_ONLY_FLAG
2193 ,PRIMARY_RECOVERY_TYPE_CODE
2194 ,PRIMARY_REC_TYPE_RULE_FLAG
2195 ,SECONDARY_RECOVERY_TYPE_CODE
2196 ,SECONDARY_REC_TYPE_RULE_FLAG
2197 ,PRIMARY_REC_RATE_DET_RULE_FLAG
2198 ,SEC_REC_RATE_DET_RULE_FLAG
2199 ,OFFSET_TAX_FLAG
2200 ,RECOVERY_RATE_OVERRIDE_FLAG
2201 ,ZONE_GEOGRAPHY_TYPE
2202 ,REGN_NUM_SAME_AS_LE_FLAG
2203 ,DEF_REC_SETTLEMENT_OPTION_CODE
2204 ,PARENT_GEOGRAPHY_TYPE
2205 ,PARENT_GEOGRAPHY_ID
2206 ,ALLOW_MASS_CREATE_FLAG
2207 ,APPLIED_AMT_HANDLING_FLAG
2208 ,CREATED_BY
2209 ,CREATION_DATE
2210 ,LAST_UPDATED_BY
2211 ,LAST_UPDATE_DATE
2212 ,LAST_UPDATE_LOGIN
2213 ,REQUEST_ID
2214 ,PROGRAM_APPLICATION_ID
2215 ,PROGRAM_ID
2216 ,RECORD_TYPE_CODE
2217 ,ALLOW_ROUNDING_OVERRIDE_FLAG
2218 ,ATTRIBUTE1
2219 ,ATTRIBUTE2
2220 ,ATTRIBUTE3
2221 ,ATTRIBUTE4
2222 ,ATTRIBUTE5
2223 ,ATTRIBUTE6
2224 ,ATTRIBUTE7
2225 ,ATTRIBUTE8
2226 ,ATTRIBUTE9
2227 ,ATTRIBUTE10
2228 ,ATTRIBUTE11
2229 ,ATTRIBUTE12
2230 ,ATTRIBUTE13
2231 ,ATTRIBUTE14
2232 ,ATTRIBUTE15
2233 ,ATTRIBUTE_CATEGORY
2237 ,DEF_SECONDARY_REC_RATE_CODE
2234 ,SOURCE_TAX_FLAG
2235 ,DEF_PRIMARY_REC_RATE_CODE
2236 ,ALLOW_DUP_REGN_NUM_FLAG
2238 ,SPECIAL_INCLUSIVE_TAX_FLAG
2239 ,PROGRAM_LOGIN_ID
2240 ,TAX_ID
2241 ,CONTENT_OWNER_ID
2242 ,REP_TAX_AUTHORITY_ID
2243 ,COLL_TAX_AUTHORITY_ID
2244 ,THRSHLD_CHK_TMPLT_CODE
2245 ,TAX_ACCOUNT_SOURCE_TAX
2246 ,TAX_ACCOUNT_CREATE_METHOD_CODE
2247 ,OVERRIDE_GEOGRAPHY_TYPE
2248 ,LIVE_FOR_APPLICABILITY_FLAG
2249 ,OBJECT_VERSION_NUMBER
2250 ,TAX_EXMPT_CR_METHOD_CODE
2251 ,TAX_EXMPT_SOURCE_TAX
2252 ,APPLICABLE_BY_DEFAULT_FLAG
2253 ,LEGAL_REPORTING_STATUS_DEF_VAL )
2254 SELECT tax.TAX
2255 ,tax.EFFECTIVE_FROM
2256 ,tax.EFFECTIVE_TO
2257 ,tax.TAX_REGIME_CODE
2258 ,tax.TAX_TYPE_CODE
2259 ,tax.ALLOW_MANUAL_ENTRY_FLAG
2260 ,tax.ALLOW_TAX_OVERRIDE_FLAG
2261 ,tax.MIN_TXBL_BSIS_THRSHLD
2262 ,tax.MAX_TXBL_BSIS_THRSHLD
2263 ,tax.MIN_TAX_RATE_THRSHLD
2264 ,tax.MAX_TAX_RATE_THRSHLD
2265 ,tax.MIN_TAX_AMT_THRSHLD
2266 ,tax.MAX_TAX_AMT_THRSHLD
2267 ,tax.COMPOUNDING_PRECEDENCE
2268 ,tax.PERIOD_SET_NAME
2269 ,tax.EXCHANGE_RATE_TYPE
2270 ,tax.TAX_CURRENCY_CODE
2271 ,tax.TAX_PRECISION
2272 ,tax.MINIMUM_ACCOUNTABLE_UNIT
2273 ,tax.ROUNDING_RULE_CODE
2274 ,tax.TAX_STATUS_RULE_FLAG
2275 ,tax.TAX_RATE_RULE_FLAG
2276 ,tax.DEF_PLACE_OF_SUPPLY_TYPE_CODE
2277 ,tax.PLACE_OF_SUPPLY_RULE_FLAG
2278 ,tax.DIRECT_RATE_RULE_FLAG
2279 ,'Y' --APPLICABILITY_RULE_FLAG
2280 ,tax.TAX_CALC_RULE_FLAG
2281 ,tax.TXBL_BSIS_THRSHLD_FLAG
2282 ,tax.TAX_RATE_THRSHLD_FLAG
2283 ,tax.TAX_AMT_THRSHLD_FLAG
2284 ,tax.TAXABLE_BASIS_RULE_FLAG
2285 ,tax.DEF_INCLUSIVE_TAX_FLAG
2286 ,tax.THRSHLD_GROUPING_LVL_CODE
2287 ,tax.HAS_OTHER_JURISDICTIONS_FLAG
2288 ,tax.ALLOW_EXEMPTIONS_FLAG
2289 ,tax.ALLOW_EXCEPTIONS_FLAG
2290 ,tax.ALLOW_RECOVERABILITY_FLAG
2291 ,tax.DEF_TAX_CALC_FORMULA
2292 ,tax.TAX_INCLUSIVE_OVERRIDE_FLAG
2293 ,tax.DEF_TAXABLE_BASIS_FORMULA
2294 ,tax.DEF_REGISTR_PARTY_TYPE_CODE
2295 ,tax.REGISTRATION_TYPE_RULE_FLAG
2296 ,tax.REPORTING_ONLY_FLAG
2297 ,tax.AUTO_PRVN_FLAG
2298 ,tax.LIVE_FOR_PROCESSING_FLAG
2299 ,tax.HAS_DETAIL_TB_THRSHLD_FLAG
2300 ,tax.HAS_TAX_DET_DATE_RULE_FLAG
2301 ,tax.HAS_EXCH_RATE_DATE_RULE_FLAG
2302 ,tax.HAS_TAX_POINT_DATE_RULE_FLAG
2303 ,tax.PRINT_ON_INVOICE_FLAG
2304 ,tax.USE_LEGAL_MSG_FLAG
2305 ,tax.CALC_ONLY_FLAG
2306 ,tax.PRIMARY_RECOVERY_TYPE_CODE
2307 ,tax.PRIMARY_REC_TYPE_RULE_FLAG
2308 ,tax.SECONDARY_RECOVERY_TYPE_CODE
2309 ,tax.SECONDARY_REC_TYPE_RULE_FLAG
2310 ,tax.PRIMARY_REC_RATE_DET_RULE_FLAG
2311 ,tax.SEC_REC_RATE_DET_RULE_FLAG
2312 ,tax.OFFSET_TAX_FLAG
2313 ,tax.RECOVERY_RATE_OVERRIDE_FLAG
2314 ,tax.ZONE_GEOGRAPHY_TYPE
2315 ,tax.REGN_NUM_SAME_AS_LE_FLAG
2316 ,tax.DEF_REC_SETTLEMENT_OPTION_CODE
2317 ,tax.PARENT_GEOGRAPHY_TYPE
2318 ,tax.PARENT_GEOGRAPHY_ID
2319 ,tax.ALLOW_MASS_CREATE_FLAG
2320 ,tax.APPLIED_AMT_HANDLING_FLAG
2321 ,fnd_global.user_id --CREATED_BY
2322 ,SYSDATE --CREATION_DATE
2323 ,fnd_global.user_id --LAST_UPDATED_BY
2324 ,SYSDATE --LAST_UPDATE_DATE
2325 ,fnd_global.conc_login_id --LAST_UPDATE_LOGIN
2326 ,tax.REQUEST_ID
2327 ,tax.PROGRAM_APPLICATION_ID
2328 ,tax.PROGRAM_ID
2329 ,tax.RECORD_TYPE_CODE
2330 ,tax.ALLOW_ROUNDING_OVERRIDE_FLAG
2331 ,tax.ATTRIBUTE1
2332 ,tax.ATTRIBUTE2
2333 ,tax.ATTRIBUTE3
2334 ,tax.ATTRIBUTE4
2335 ,tax.ATTRIBUTE5
2336 ,tax.ATTRIBUTE6
2337 ,tax.ATTRIBUTE7
2338 ,tax.ATTRIBUTE8
2339 ,tax.ATTRIBUTE9
2340 ,tax.ATTRIBUTE10
2341 ,tax.ATTRIBUTE11
2342 ,tax.ATTRIBUTE12
2343 ,tax.ATTRIBUTE13
2344 ,tax.ATTRIBUTE14
2345 ,tax.ATTRIBUTE15
2346 ,tax.ATTRIBUTE_CATEGORY
2347 ,tax.SOURCE_TAX_FLAG
2348 ,tax.DEF_PRIMARY_REC_RATE_CODE
2349 ,tax.ALLOW_DUP_REGN_NUM_FLAG
2350 ,tax.DEF_SECONDARY_REC_RATE_CODE
2351 ,tax.SPECIAL_INCLUSIVE_TAX_FLAG
2352 ,tax.PROGRAM_LOGIN_ID
2353 ,ZX_TAXES_B_S.NEXTVAL
2354 ,rule.CONTENT_OWNER_ID
2355 ,tax.REP_TAX_AUTHORITY_ID
2356 ,tax.COLL_TAX_AUTHORITY_ID
2357 ,tax.THRSHLD_CHK_TMPLT_CODE
2358 ,tax.TAX_ACCOUNT_SOURCE_TAX
2359 ,tax.TAX_ACCOUNT_CREATE_METHOD_CODE
2360 ,tax.OVERRIDE_GEOGRAPHY_TYPE
2361 ,tax.LIVE_FOR_APPLICABILITY_FLAG
2362 ,tax.OBJECT_VERSION_NUMBER
2363 ,tax.TAX_EXMPT_CR_METHOD_CODE
2364 ,tax.TAX_EXMPT_SOURCE_TAX
2368 WHERE tax.tax_type_code ='LOCATION'
2365 ,tax.APPLICABLE_BY_DEFAULT_FLAG
2366 ,tax.LEGAL_REPORTING_STATUS_DEF_VAL
2367 FROM ZX_TAXES_B tax, zx_rules_b rule
2369 AND tax.RECORD_TYPE_CODE = 'MIGRATED'
2370 AND tax.live_for_applicability_flag = 'Y' -- add to filter location taxes defined in 11i
2371 AND tax.content_owner_id = -99
2372 AND rule.tax_regime_code = tax.tax_regime_code
2373 AND rule.tax = tax.tax
2374 AND rule.record_type_code = 'MIGRATED'
2375 AND rule.SERVICE_TYPE_CODE = 'DET_APPLICABLE_TAXES'
2376 AND rule.recovery_type_code is NULL
2377 AND rule.tax_rule_code = tax.tax
2378 AND rule.priority = 1
2379 AND NOT EXISTS (
2380 SELECT 1
2381 FROM zx_taxes_b tax2
2382 WHERE tax2.tax = tax.tax
2383 AND tax2.tax_regime_code = tax.tax_regime_code
2384 AND tax2.content_owner_id = tax.content_owner_id);
2385
2386 -- bug fix: 5548613 end
2387
2388 IF PG_DEBUG = 'Y' THEN
2389 arp_util_tax.debug('Create_Rules(-)');
2390 END IF;
2391 EXCEPTION
2392 WHEN OTHERS THEN
2393 IF PG_DEBUG = 'Y' THEN
2394 arp_util_tax.debug('EXCEPTION: Create_rules ');
2395 arp_util_tax.debug(sqlerrm);
2396 arp_util_tax.debug('Create_Rules(-)');
2397 END IF;
2398 --app_exception.raise_exception;
2399 END create_rules;
2400
2401 /*=========================================================================+
2402 | PROCEDURE |
2403 | create_process_results |
2404 | |
2405 | DESCRIPTION |
2406 | This routine inserts data into ZX_PROCESS_RESULTS by following same |
2407 | logic used while inserting the data in ZX_CONDITION_GROUPS_B. |
2408 | |
2409 | SCOPE - PUBLIC |
2410 | |
2411 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2412 | |
2413 | CALLED FROM |
2414 | migrate_default_hierarchy |
2415 | NOTES |
2416 | |
2417 | MODIFICATION HISTORY |
2418 | 15-Jan-04 Srinivas Lokam Created. |
2419 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions|
2420 | in SELECT statements for handling |
2421 | SYNC process. |
2422 | |
2423 |=========================================================================*/
2424
2425 PROCEDURE create_process_results(p_tax_id IN NUMBER DEFAULT NULL,
2426 p_sync_module IN VARCHAR2 DEFAULT NULL
2427 ) IS
2428 BEGIN
2429 IF PG_DEBUG = 'Y' THEN
2430 arp_util_tax.debug('Create_Process_Results(+)');
2431 END IF;
2432
2433 IF (nvl(p_sync_module,'AP') = 'AP') THEN
2434 --Process_Results for AP Tax codes and Tax Groups Setup
2435 INSERT INTO ZX_PROCESS_RESULTS
2436 (
2437 CONDITION_GROUP_CODE ,
2438 PRIORITY ,
2439 RESULT_TYPE_CODE ,
2440 TAX_STATUS_CODE ,
2441 NUMERIC_RESULT ,
2442 ALPHANUMERIC_RESULT ,
2443 STATUS_RESULT ,
2444 RATE_RESULT ,
2445 LEGAL_MESSAGE_CODE ,
2446 MIN_TAX_AMT ,
2447 MAX_TAX_AMT ,
2448 MIN_TAXABLE_BASIS ,
2449 MAX_TAXABLE_BASIS ,
2450 MIN_TAX_RATE ,
2451 MAX_TAX_RATE ,
2452 ENABLED_FLAG ,
2453 ALLOW_EXEMPTIONS_FLAG ,
2454 ALLOW_EXCEPTIONS_FLAG ,
2455 RECORD_TYPE_CODE ,
2456 RESULT_API ,
2457 RESULT_ID ,
2458 CONTENT_OWNER_ID ,
2459 CONDITION_GROUP_ID ,
2460 TAX_RULE_ID ,
2461 CREATED_BY ,
2462 CREATION_DATE ,
2463 LAST_UPDATED_BY ,
2464 LAST_UPDATE_DATE ,
2465 LAST_UPDATE_LOGIN ,
2466 REQUEST_ID ,
2467 PROGRAM_APPLICATION_ID ,
2468 PROGRAM_ID ,
2469 PROGRAM_LOGIN_ID ,
2470 OBJECT_VERSION_NUMBER
2471
2472 )
2473 SELECT
2474 CONDITION_GROUP_CODE ,
2475 nvl(PRIORITY,ap_tax_codes_s.nextval),
2476 'CODE' ,--RESULT_TYPE_CODE
2477 NULL ,--TAX_STATUS_CODE
2478 NULL ,--NUMERIC_RESULT
2479 'APPLICABLE' ,--ALPHANUMERIC_RESULT
2480 STATUS_RESULT ,--STATUS_RESULT
2481 RATE_RESULT ,
2482 NULL ,--LEGAL_MESSAGE_CODE
2486 NULL ,--MAX_TAXABLE_BASIS
2483 NULL ,--MIN_TAX_AMT
2484 NULL ,--MAX_TAX_AMT
2485 NULL ,--MIN_TAXABLE_BASIS
2487 NULL ,--MIN_TAX_RATE
2488 NULL ,--MAX_TAX_RATE
2489 ENABLED_FLAG ,
2490 'N' ,--ALLOW_EXEMPTIONS_FLAG
2491 'N' ,--ALLOW_EXCEPTIONS_FLAG
2492 'MIGRATED' ,--RECORD_TYPE_CODE
2493 NULL ,--RESULT_API
2494 zx_process_results_s.nextval ,--RESULT_ID
2495 CONTENT_OWNER_ID ,
2496 CONDITION_GROUP_ID ,
2497 TAX_RULE_ID ,
2498 fnd_global.user_id ,
2499 SYSDATE ,
2500 fnd_global.user_id ,
2501 SYSDATE ,
2502 fnd_global.conc_login_id ,
2503 fnd_global.conc_request_id ,--Request Id
2504 fnd_global.prog_appl_id ,--Program Application ID
2505 fnd_global.conc_program_id ,--Program Id
2506 fnd_global.conc_login_id ,--Program Login ID
2507 1
2508 FROM
2509 (
2510 /* Bug 5061471 : Commenting this as we no longer require creation of process results for simple taxes
2511 SELECT
2512 cond_groups.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
2513 max(rates.TAX_RATE_ID) PRIORITY ,
2514 rates.TAX_STATUS_CODE STATUS_RESULT ,
2515 rates.TAX_RATE_CODE RATE_RESULT ,
2516 rates.ACTIVE_FLAG ENABLED_FLAG ,
2517 rules.CONTENT_OWNER_ID CONTENT_OWNER_ID ,
2518 cond_groups.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
2519 rules.TAX_RULE_ID TAX_RULE_ID
2520 FROM
2521 ZX_RATES_B rates,
2522 FND_LOOKUPS fnd,
2523 ZX_RULES_B rules,
2524 ZX_CONDITION_GROUPS_B cond_groups,
2525 AP_TAX_CODES_ALL codes
2526 WHERE
2527 rates.record_type_code = 'MIGRATED'
2528 AND nvl(rates.source_id, rates.tax_rate_id) = codes.tax_id
2529 AND rates.tax_rate_code = fnd.lookup_code
2530 AND fnd.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
2531 AND codes.tax_type NOT IN('AWT','OFFSET','TAX_GROUP')
2532 AND rules.tax_rule_code = rates.TAX
2533 AND rules.content_owner_id = rates.content_owner_id
2534 AND cond_groups.condition_group_code = rates.tax_rate_code
2535 AND cond_groups.enabled_flag = codes.enabled_flag
2536 --Added following AND condition for Sync process
2537 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
2538 AND not exists (select 1 from zx_process_results where
2539 tax_rule_id = rules.tax_rule_id
2540 and condition_group_code = cond_groups.condition_group_code
2541 and rate_result = rates.tax_rate_code
2542 )
2543 GROUP BY
2544 cond_groups.CONDITION_GROUP_CODE,
2545 cond_groups.CONDITION_GROUP_ID,
2546 rules.TAX_RULE_ID,
2547 rules.CONTENT_OWNER_ID,
2548 rates.TAX_RATE_CODE,
2549 rates.ACTIVE_FLAG,
2550 rates.TAX_STATUS_CODE
2551 UNION ALL */
2552 SELECT
2553 cond_groups.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
2554 NULL PRIORITY ,
2555 rates.TAX_STATUS_CODE STATUS_RESULT ,
2556 rates.TAX_RATE_CODE RATE_RESULT ,
2557 groups.enabled_flag ENABLED_FLAG ,
2558 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID ,
2559 cond_groups.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
2560 rules.TAX_RULE_ID TAX_RULE_ID
2561 FROM
2562 AR_TAX_GROUP_CODES_ALL GROUPS,
2563 AP_TAX_CODES_ALL GROUP_CODES,
2564 AP_TAX_CODES_ALL CODES,
2565 ZX_RATES_B rates,
2566 ZX_RULES_B rules,
2567 ZX_CONDITION_GROUPS_B cond_groups
2568 WHERE
2569 GROUP_CODES.TAX_ID = GROUPS.TAX_GROUP_ID
2570 AND GROUPS.TAX_GROUP_TYPE = 'AP'
2571 AND GROUPS.TAX_CODE_ID = CODES.TAX_ID
2572 AND rates.RECORD_TYPE_CODE = 'MIGRATED'
2573 AND nvl(rates.source_id, rates.tax_rate_id) = codes.TAX_ID
2574 --Added following AND condition for Sync process
2575 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
2576 AND rules.tax_regime_code = rates.TAX_REGIME_CODE
2577 AND rules.service_type_code = 'DET_DIRECT_RATE'
2578 AND rules.recovery_type_code IS NULL
2579 AND rules.priority = (groups.tax_group_id * 2) + groups.display_order
2580 AND rules.tax_rule_code = rates.TAX
2581 AND rules.content_owner_id = rates.content_owner_id
2582 AND cond_groups.CONDITION_GROUP_CODE = group_codes.NAME
2583 --AND cond_groups.enabled_flag = codes.enabled_flag --Bug 5061471
2584 AND rules.effective_from = GROUPS.start_date -- bug 6680676
2585 AND NVL(rules.effective_to, sysdate) = NVL(GROUPS.end_date, sysdate)
2586 AND not exists (select 1 from zx_process_results where
2587 tax_rule_id = rules.tax_rule_id
2588 and condition_group_code = cond_groups.condition_group_code
2589 and rate_result = rates.tax_rate_code
2590 )
2591 );
2592 END IF;
2593
2594
2595 IF (nvl(p_sync_module,'AR') = 'AR') THEN
2596 --Process_Results for AR Tax codes and Tax Groups Setup
2597 INSERT INTO ZX_PROCESS_RESULTS
2598 (
2599 CONDITION_GROUP_CODE ,
2600 PRIORITY ,
2601 RESULT_TYPE_CODE ,
2602 TAX_STATUS_CODE ,
2606 RATE_RESULT ,
2603 NUMERIC_RESULT ,
2604 ALPHANUMERIC_RESULT ,
2605 STATUS_RESULT ,
2607 LEGAL_MESSAGE_CODE ,
2608 MIN_TAX_AMT ,
2609 MAX_TAX_AMT ,
2610 MIN_TAXABLE_BASIS ,
2611 MAX_TAXABLE_BASIS ,
2612 MIN_TAX_RATE ,
2613 MAX_TAX_RATE ,
2614 ENABLED_FLAG ,
2615 ALLOW_EXEMPTIONS_FLAG ,
2616 ALLOW_EXCEPTIONS_FLAG ,
2617 RECORD_TYPE_CODE ,
2618 RESULT_API ,
2619 RESULT_ID ,
2620 CONTENT_OWNER_ID ,
2621 CONDITION_GROUP_ID ,
2622 TAX_RULE_ID ,
2623 CONDITION_SET_ID ,
2624 EXCEPTION_SET_ID ,
2625 CREATED_BY ,
2626 CREATION_DATE ,
2627 LAST_UPDATED_BY ,
2628 LAST_UPDATE_DATE ,
2629 LAST_UPDATE_LOGIN ,
2630 REQUEST_ID ,
2631 PROGRAM_APPLICATION_ID ,
2632 PROGRAM_ID ,
2633 PROGRAM_LOGIN_ID ,
2634 OBJECT_VERSION_NUMBER
2635 )
2636 SELECT
2637 CONDITION_GROUP_CODE ,
2638 nvl(PRIORITY,ar_vat_tax_s.nextval),--slokam
2639 'CODE' ,--RESULT_TYPE_CODE
2640 NULL ,--TAX_STATUS_CODE
2641 NULL ,--NUMERIC_RESULT
2642 'APPLICABLE' ,--ALPHANUMERIC_RESULT
2643 STATUS_RESULT ,--STATUS_RESULT
2644 RATE_RESULT ,
2645 NULL ,--LEGAL_MESSAGE_CODE
2646 NULL ,--MIN_TAX_AMT
2647 NULL ,--MAX_TAX_AMT
2648 NULL ,--MIN_TAXABLE_BASIS
2649 NULL ,--MAX_TAXABLE_BASIS
2650 NULL ,--MIN_TAX_RATE
2651 NULL ,--MAX_TAX_RATE
2652 ENABLED_FLAG ,
2653 'N' ,--ALLOW_EXEMPTIONS_FLAG
2654 'N' ,--ALLOW_EXCEPTIONS_FLAG
2655 'MIGRATED' ,--RECORD_TYPE_CODE
2656 NULL ,--RESULT_API
2657 zx_process_results_s.nextval ,--RESULT_ID
2658 CONTENT_OWNER_ID ,
2659 CONDITION_GROUP_ID ,
2660 TAX_RULE_ID ,
2661 CONDITION_SET_ID ,
2662 EXCEPTION_SET_ID ,
2663 fnd_global.user_id ,
2664 SYSDATE ,
2665 fnd_global.user_id ,
2666 SYSDATE ,
2667 fnd_global.conc_login_id ,
2668 fnd_global.conc_request_id ,--Request Id
2669 fnd_global.prog_appl_id ,--Program Application ID
2670 fnd_global.conc_program_id ,--Program Id
2671 fnd_global.conc_login_id ,--Program Login ID
2672 1
2673 FROM
2674 (
2675 SELECT
2676 cond_groups.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
2677 max(rates.TAX_RATE_ID) PRIORITY ,
2678 rates.TAX_STATUS_CODE STATUS_RESULT ,
2679 rates.TAX_RATE_CODE RATE_RESULT ,
2680 rates.ACTIVE_FLAG ENABLED_FLAG ,
2681 rules.CONTENT_OWNER_ID CONTENT_OWNER_ID ,
2682 cond_groups.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
2683 rules.TAX_RULE_ID TAX_RULE_ID ,
2684 NULL CONDITION_SET_ID ,
2685 NULL EXCEPTION_SET_ID
2686 FROM
2687 ZX_RATES_B rates,
2688 ZX_RULES_B rules,
2689 ZX_CONDITION_GROUPS_B cond_groups,
2690 AR_VAT_TAX_ALL_B codes,
2691 ar_system_parameters_all sys
2692 WHERE
2693 rates.RECORD_TYPE_CODE = 'MIGRATED'
2694 AND codes.vat_tax_id = rates.tax_rate_id
2695 AND codes.tax_type NOT IN('AWT','OFFSET','TAX_GROUP')
2696 AND rules.tax_regime_code = rates.TAX_REGIME_CODE
2697 AND rules.tax = rates.TAX
2698 AND rules.service_type_code = 'DET_DIRECT_RATE'
2699 AND rules.recovery_type_code IS NULL
2700 AND rules.priority = 1
2701 AND rules.tax_rule_code = rates.TAX
2702 AND rules.content_owner_id = rates.content_owner_id
2703 AND cond_groups.condition_group_code = SUBSTRB(codes.tax_code,1, 40)
2704 ||DECODE(codes.tax_constraint_id,
2705 NULL, '', '~'||codes.tax_constraint_id)
2706 AND codes.set_of_books_id = sys.set_of_books_id --Bug 5090631
2707 AND codes.org_id = sys.org_id --Bug 5090631
2708 AND sys.tax_method ='SALES_TAX'
2709 --Added following AND condition for Sync process
2710 AND codes.vat_tax_id = nvl(p_tax_id,codes.vat_tax_id)
2711 AND not exists (select 1 from zx_process_results where
2712 tax_rule_id = rules.tax_rule_id
2713 and condition_group_code = cond_groups.condition_group_code
2714 and rate_result = rates.tax_rate_code
2715 )
2716 GROUP BY
2717 cond_groups.CONDITION_GROUP_CODE,
2718 rates.TAX_STATUS_CODE,
2719 cond_groups.CONDITION_GROUP_ID,
2720 rules.TAX_RULE_ID,
2721 rules.CONTENT_OWNER_ID,
2722 rates.TAX_RATE_CODE,
2723 rates.ACTIVE_FLAG,
2724 NULL,
2725 NULL
2726 UNION ALL
2727 SELECT
2728 cond_groups.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
2732 decode(gc.enabled_flag,'N','N',gvat.enabled_flag) ENABLED_FLAG, --bug 6684262
2729 NULL PRIORITY ,--slokam
2730 rates.TAX_STATUS_CODE STATUS_RESULT ,
2731 rates.TAX_RATE_CODE RATE_RESULT ,
2733 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID ,
2734 cond_groups.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
2735 rules.TAX_RULE_ID TAX_RULE_ID ,
2736 gc.TAX_CONDITION_ID CONDITION_SET_ID ,
2737 gc.TAX_EXCEPTION_ID EXCEPTION_SET_ID
2738 FROM AR_VAT_TAX_ALL_B gvat,
2739 AR_TAX_GROUP_CODES_ALL gc,
2740 AR_VAT_TAX_ALL_B vat,
2741 AR_TAX_CONDITIONS_ALL cond,
2742 AR_TAX_CONDITIONS_ALL excp,
2743 ZX_RATES_B rates,
2744 ZX_RULES_B rules,
2745 ZX_CONDITION_GROUPS_B cond_groups
2746 WHERE
2747 gvat.vat_tax_id = gc.tax_group_id
2748 AND gc.tax_group_type = 'AR'
2749 AND gvat.tax_type = 'TAX_GROUP'
2750 AND vat.tax_class = 'O'
2751 AND vat.vat_tax_id = gc.tax_code_id
2752 AND vat.tax_type <> 'TAX_GROUP'
2753 AND gc.tax_condition_id = cond.tax_condition_id (+)
2754 AND gc.tax_exception_id = excp.tax_condition_id (+)
2755 AND rates.RECORD_TYPE_CODE = 'MIGRATED'
2756 AND vat.vat_tax_id = rates.tax_rate_id
2757 AND rules.effective_from = gc.start_date --6718736
2758 --Added following AND condition for Sync process
2759 AND vat.vat_tax_id = nvl(p_tax_id,vat.vat_tax_id)
2760 AND rules.tax_regime_code = rates.TAX_REGIME_CODE
2761 AND rules.tax = rates.TAX
2762 AND rules.service_type_code = 'DET_DIRECT_RATE'
2763 AND rules.recovery_type_code IS NULL
2764 AND rules.priority = (gc.tax_group_id * 2) + gc.display_order
2765 AND rules.tax_rule_code = rates.TAX
2766 AND rules.content_owner_id = rates.content_owner_id
2767 AND cond_groups.condition_group_code = SUBSTRB(gvat.tax_code,1,40)
2768 ||DECODE(gvat.tax_constraint_id,
2769 NULL, '', '~'||gvat.tax_constraint_id)
2770 --AND cond_groups.enabled_flag = vat.enabled_flag --Bug 5061471
2771 AND not exists (select 1 from zx_process_results where
2772 tax_rule_id = rules.tax_rule_id
2773 and condition_group_code = cond_groups.condition_group_code
2774 and rate_result = rates.tax_rate_code
2775 )
2776 );
2777
2778
2779 --Create process results for Location Based Taxes
2780
2781 INSERT INTO ZX_PROCESS_RESULTS (
2782 CONDITION_GROUP_CODE ,
2783 PRIORITY ,
2784 RESULT_TYPE_CODE ,
2785 TAX_STATUS_CODE ,
2786 NUMERIC_RESULT ,
2787 ALPHANUMERIC_RESULT ,
2788 STATUS_RESULT ,
2789 RATE_RESULT ,
2790 LEGAL_MESSAGE_CODE ,
2791 MIN_TAX_AMT ,
2792 MAX_TAX_AMT ,
2793 MIN_TAXABLE_BASIS ,
2794 MAX_TAXABLE_BASIS ,
2795 MIN_TAX_RATE ,
2796 MAX_TAX_RATE ,
2797 ENABLED_FLAG ,
2798 ALLOW_EXEMPTIONS_FLAG ,
2799 ALLOW_EXCEPTIONS_FLAG ,
2800 RECORD_TYPE_CODE ,
2801 CREATION_DATE ,
2802 LAST_UPDATE_DATE ,
2803 REQUEST_ID ,
2804 PROGRAM_APPLICATION_ID,
2805 PROGRAM_ID ,
2806 CONDITION_SET_ID ,
2807 EXCEPTION_SET_ID ,
2808 PROGRAM_LOGIN_ID ,
2809 RESULT_ID ,
2810 CONTENT_OWNER_ID ,
2811 CONDITION_GROUP_ID ,
2812 TAX_RULE_ID ,
2813 CREATED_BY ,
2814 LAST_UPDATED_BY ,
2815 LAST_UPDATE_LOGIN ,
2816 RESULT_API ,
2817 OBJECT_VERSION_NUMBER
2818 )
2819 SELECT
2820 CONDITION_GROUP_CODE ,
2821 nvl(PRIORITY,ar_vat_tax_s.nextval),--slokam
2822 'CODE' , --RESULT_TYPE_CODE , --Bug 5385949
2823 NULL , --TAX_STATUS_CODE ,
2824 NULL , --NUMERIC_RESULT ,
2825 'APPLICABLE' , --ALPHANUMERIC_RESULT ,
2826 'STANDARD' , --STATUS_RESULT , --Bug 5385949
2827 RATE_RESULT ,
2828 NULL , --LEGAL_MESSAGE_CODE ,
2829 NULL , --MIN_TAX_AMT ,
2830 NULL , --MAX_TAX_AMT ,
2831 NULL , --MIN_TAXABLE_BASIS ,
2832 NULL , --MAX_TAXABLE_BASIS ,
2833 NULL , --MIN_TAX_RATE ,
2834 NULL , --MAX_TAX_RATE ,
2835 ENABLED_FLAG ,
2836 'Y' , --ALLOW_EXEMPTIONS_FLAG ,
2837 'Y' , --ALLOW_EXCEPTIONS_FLAG ,
2838 'MIGRATED' , --RECORD_TYPE_CODE ,
2839 sysdate , -- CREATION_DATE ,
2840 sysdate , -- LAST_UPDATE_DATE ,
2841 fnd_global.conc_request_id, -- REQUEST_ID ,
2842 fnd_global.prog_appl_id, --PROGRAM_APPLICATION_ID,
2843 NULL, -- PROGRAM_ID ,
2844 CONDITION_SET_ID ,
2845 EXCEPTION_SET_ID ,
2846 NULL , -- PROGRAM_LOGIN_ID ,
2847 zx_process_results_s.nextval,
2848 CONTENT_OWNER_ID ,
2849 condition_group_id,
2850 tax_rule_id,
2851 fnd_global.user_id , --CREATED_BY ,
2852 fnd_global.user_id , --LAST_UPDATED_BY ,
2853 fnd_global.conc_login_id, --LAST_UPDATE_LOGIN
2854 NULL , -- RESULT_API ,
2855 1 --OBJECT_VERSION_NUMBER
2856 FROM
2857 (
2858 SELECT
2859 CONDITION_GROUP_CODE,
2863 CONDITION_GROUP_ID,
2860 1 PRIORITY ,
2861 rules.ENABLED_FLAG ENABLED_FLAG,
2862 PTP.party_tax_profile_id CONTENT_OWNER_ID ,
2864 rules.tax_rule_id TAX_RULE_ID ,
2865 NULL CONDITION_SET_ID ,
2866 NULL EXCEPTION_SET_ID ,
2867 decode(vat.leasing_flag,'Y',vat.tax_code,'STANDARD') RATE_RESULT
2868 FROM ZX_TAXES_B TAXES,
2869 ZX_CONDITION_GROUPS_B CG,
2870 ZX_PARTY_TAX_PROFILE PTP,
2871 AR_VAT_TAX_ALL_B VAT ,
2872 ZX_RULES_B RULES,
2873 ar_system_parameters_all sys
2874 WHERE
2875 taxes.RECORD_TYPE_CODE = 'MIGRATED'
2876 AND taxes.tax_type_code = 'LOCATION'
2877 AND taxes.live_for_applicability_flag = 'Y'
2878 AND taxes.content_owner_id = -99
2879 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
2880 AND vat.tax_type = 'LOCATION'
2881 AND vat.enabled_flag = 'Y'
2882 AND vat.set_of_books_id = sys.set_of_books_id
2883 AND vat.org_id = sys.org_id
2884 AND vat.org_id = ptp.party_id
2885 AND ptp.party_type_code = 'OU'
2886 AND ptp.party_tax_profile_id = rules.content_owner_id
2887 AND taxes.tax_regime_code = rules.tax_regime_code
2888 AND taxes.tax = rules.tax
2889 AND rules.service_type_code ='DET_DIRECT_RATE' --Bug 5385949
2890 AND rules.RECOVERY_TYPE_CODE IS NULL
2891 AND rules.tax_rule_code = taxes.TAX
2892 -- AND rules.effective_from = taxes.effective_from
2893 AND rules.priority = 1
2894 AND CG.condition_group_code = SUBSTRB(vat.tax_code,1,40)
2895 ||DECODE(vat.tax_constraint_id,
2896 NULL, '', '~'||vat.tax_constraint_id)
2897 --Added following AND condition for Sync process
2898 AND vat.vat_tax_id = nvl(p_tax_id,vat.vat_tax_id)
2899 AND not exists (select 1 from zx_process_results where
2900 tax_rule_id = rules.tax_rule_id
2901 and condition_group_code = cg.condition_group_code
2902 and result_type_code ='CODE' --Bug 5385949
2903 and rate_result is null
2904 )
2905 UNION ALL
2906 SELECT
2907 cond_groups.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
2908 NULL PRIORITY ,--slokam
2909 gvat.enabled_flag ENABLED_FLAG ,
2910 ptp.party_tax_profile_id CONTENT_OWNER_ID ,
2911 cond_groups.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
2912 rules.TAX_RULE_ID TAX_RULE_ID ,
2913 gc.TAX_CONDITION_ID CONDITION_SET_ID ,
2914 gc.TAX_EXCEPTION_ID EXCEPTION_SET_ID ,
2915 NULL RATE_RESULT
2916 FROM AR_VAT_TAX_ALL_B gvat,
2917 AR_TAX_GROUP_CODES_ALL gc,
2918 AR_VAT_TAX_ALL_B vat,
2919 AR_TAX_CONDITIONS_ALL cond,
2920 AR_TAX_CONDITIONS_ALL excp,
2921 ZX_TAXES_B TAXES,
2922 ZX_RULES_B rules,
2923 ZX_CONDITION_GROUPS_B cond_groups,
2924 ZX_PARTY_TAX_PROFILE PTP,
2925 ar_system_parameters_all sys
2926 WHERE
2927 gvat.vat_tax_id = gc.tax_group_id
2928 AND gc.tax_group_type = 'AR'
2929 AND gvat.tax_type = 'TAX_GROUP'
2930 AND vat.tax_class = 'O'
2931 AND vat.vat_tax_id = gc.tax_code_id
2932 AND vat.tax_type <> 'TAX_GROUP'
2933 AND gc.tax_condition_id = cond.tax_condition_id (+)
2934 AND gc.tax_exception_id = excp.tax_condition_id (+)
2935 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
2936 AND taxes.tax_type_code = 'LOCATION'
2937 AND taxes.live_for_applicability_flag = 'Y'
2938 AND taxes.content_owner_id = -99
2939 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
2940 AND vat.tax_type = 'LOCATION'
2941 AND vat.set_of_books_id = sys.set_of_books_id
2942 AND vat.org_id = sys.org_id
2943 AND vat.org_id = ptp.party_id
2944 AND ptp.party_type_code = 'OU'
2945 AND ptp.party_tax_profile_id = rules.content_owner_id
2946 AND taxes.tax_regime_code = rules.tax_regime_code
2947 AND taxes.tax = rules.tax
2948 AND rules.service_type_code ='DET_DIRECT_RATE' --Bug 5385949
2949 AND rules.RECOVERY_TYPE_CODE IS NULL
2950 AND rules.tax_rule_code = taxes.TAX
2951 -- AND rules.effective_from = taxes.effective_from
2952 AND rules.priority = (gc.tax_group_id * 2) + gc.display_order
2953 --Added following AND condition for Sync process
2954 AND vat.vat_tax_id = nvl(p_tax_id,vat.vat_tax_id)
2955 AND cond_groups.condition_group_code = SUBSTRB(gvat.tax_code,1,40)
2956 ||DECODE(gvat.tax_constraint_id,
2957 NULL, '', '~'||gvat.tax_constraint_id)
2958 AND cond_groups.enabled_flag = vat.enabled_flag
2959 AND not exists (select 1 from zx_process_results where
2960 tax_rule_id = rules.tax_rule_id
2961 and condition_group_code = cond_groups.condition_group_code
2962 AND ALPHANUMERIC_RESULT = 'APPLICABLE'
2963 )
2964 );
2965
2966 -- Create applicability rule process results for the tax codes in the OKL tax group with at lease one
2967 -- not null PFC, PTFC, TBC
2968
2969 INSERT INTO ZX_PROCESS_RESULTS (
2970 CONDITION_GROUP_CODE ,
2971 PRIORITY ,
2972 RESULT_TYPE_CODE ,
2973 TAX_STATUS_CODE ,
2974 NUMERIC_RESULT ,
2975 ALPHANUMERIC_RESULT ,
2976 STATUS_RESULT ,
2977 RATE_RESULT ,
2978 LEGAL_MESSAGE_CODE ,
2979 MIN_TAX_AMT ,
2980 MAX_TAX_AMT ,
2981 MIN_TAXABLE_BASIS ,
2985 ENABLED_FLAG ,
2982 MAX_TAXABLE_BASIS ,
2983 MIN_TAX_RATE ,
2984 MAX_TAX_RATE ,
2986 ALLOW_EXEMPTIONS_FLAG ,
2987 ALLOW_EXCEPTIONS_FLAG ,
2988 RECORD_TYPE_CODE ,
2989 CREATION_DATE ,
2990 LAST_UPDATE_DATE ,
2991 REQUEST_ID ,
2992 PROGRAM_APPLICATION_ID,
2993 PROGRAM_ID ,
2994 CONDITION_SET_ID ,
2995 EXCEPTION_SET_ID ,
2996 PROGRAM_LOGIN_ID ,
2997 RESULT_ID ,
2998 CONTENT_OWNER_ID ,
2999 CONDITION_GROUP_ID ,
3000 TAX_RULE_ID ,
3001 CREATED_BY ,
3002 LAST_UPDATED_BY ,
3003 LAST_UPDATE_LOGIN ,
3004 RESULT_API ,
3005 OBJECT_VERSION_NUMBER
3006 )
3007 SELECT
3008 CONDITION_GROUP_CODE ,
3009 nvl(PRIORITY,ar_vat_tax_s.nextval),--slokam
3010 'APPLICABILITY' , --RESULT_TYPE_CODE ,
3011 NULL , --TAX_STATUS_CODE ,
3012 NULL , --NUMERIC_RESULT ,
3013 'APPLICABLE' , --ALPHANUMERIC_RESULT ,
3014 NULL , --STATUS_RESULT ,
3015 NULL , --RATE_RESULT ,
3016 NULL , --LEGAL_MESSAGE_CODE ,
3017 NULL , --MIN_TAX_AMT ,
3018 NULL , --MAX_TAX_AMT ,
3019 NULL , --MIN_TAXABLE_BASIS ,
3020 NULL , --MAX_TAXABLE_BASIS ,
3021 NULL , --MIN_TAX_RATE ,
3022 NULL , --MAX_TAX_RATE ,
3023 ENABLED_FLAG ,
3024 NULL , --ALLOW_EXEMPTIONS_FLAG ,
3025 NULL , --ALLOW_EXCEPTIONS_FLAG ,
3026 'MIGRATED' , --RECORD_TYPE_CODE ,
3027 sysdate , -- CREATION_DATE ,
3028 sysdate , -- LAST_UPDATE_DATE ,
3029 fnd_global.conc_request_id, -- REQUEST_ID ,
3030 fnd_global.prog_appl_id, --PROGRAM_APPLICATION_ID,
3031 NULL, -- PROGRAM_ID ,
3032 CONDITION_SET_ID ,
3033 EXCEPTION_SET_ID ,
3034 NULL , -- PROGRAM_LOGIN_ID ,
3035 zx_process_results_s.nextval,
3036 CONTENT_OWNER_ID ,
3037 condition_group_id,
3038 tax_rule_id,
3039 fnd_global.user_id , --CREATED_BY ,
3040 fnd_global.user_id , --LAST_UPDATED_BY ,
3041 fnd_global.conc_login_id, --LAST_UPDATE_LOGIN
3042 NULL , -- RESULT_API ,
3043 1 --OBJECT_VERSION_NUMBER
3044 FROM
3045 (
3046 SELECT
3047 cg.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
3048 NULL PRIORITY ,--slokam
3049 gvat.enabled_flag ENABLED_FLAG ,
3050 rules.content_owner_id CONTENT_OWNER_ID ,
3051 cg.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
3052 rules.TAX_RULE_ID TAX_RULE_ID ,
3053 taxgrp.TAX_CONDITION_ID CONDITION_SET_ID ,
3054 taxgrp.TAX_EXCEPTION_ID EXCEPTION_SET_ID
3055
3056 FROM AR_VAT_TAX_ALL_B gvat,
3057 AR_TAX_GROUP_CODES_ALL taxgrp,
3058 ZX_TAXES_B TAXES,
3059 ZX_RULES_B rules,
3060 ZX_CONDITION_GROUPS_B cg
3061 WHERE gvat.vat_tax_id = taxgrp.tax_group_id
3062 AND taxgrp.tax_group_type = 'AR'
3063 AND gvat.tax_type = 'TAX_GROUP'
3064 AND taxgrp.product_fisc_classification ||
3065 taxgrp.trx_business_category_code ||
3066 taxgrp.party_fisc_classification IS NOT NULL
3067 AND taxes.TAX_TYPE_CODE NOT IN ('AWT','OFFSET', 'LOCATION')
3068 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
3069 AND EXISTS (SELECT 1
3070 FROM zx_rates_b rates,
3071 ar_vat_tax_all vat
3072 WHERE taxgrp.tax_code_id = vat.vat_tax_id
3073 AND taxgrp.org_id = vat.org_id
3074 AND vat.vat_tax_id = rates.tax_rate_id
3075 AND rates.tax = taxes.tax
3076 AND rates.tax_regime_code = taxes.tax_regime_code
3077 AND rates.content_owner_id = taxes.content_owner_id
3078 AND rates.record_type_code = 'MIGRATED'
3079 )
3080 AND rules.tax_regime_code = taxes.tax_regime_code
3081 AND rules.tax = taxes.tax
3082 AND rules.content_owner_id = taxes.content_owner_id
3083 AND rules.service_type_code = 'DET_APPLICABLE_TAXES'
3084 AND rules.recovery_type_code IS NULL
3085 AND rules.tax_rule_code = taxes.TAX
3086 --AND rules.effective_from = taxes.effective_from
3087 AND rules.priority = 1
3088 --Added following AND condition for Sync process
3089 AND gvat.vat_tax_id = nvl(p_tax_id,gvat.vat_tax_id)
3090 AND SUBSTRB(cg.condition_group_code,1,44) = SUBSTRB(gvat.tax_code,1,44)
3091 AND cg.ALPHANUMERIC_VALUE2||cg.ALPHANUMERIC_VALUE3||cg.ALPHANUMERIC_VALUE4
3092 = taxgrp.product_fisc_classification ||
3093 taxgrp.trx_business_category_code ||
3094 taxgrp.party_fisc_classification
3095 AND cg.enabled_flag = 'Y'
3096 AND not exists (select 1 from zx_process_results where
3097 tax_rule_id = rules.tax_rule_id
3098 and condition_group_code = cg.condition_group_code
3099 and result_type_code = 'APPLICABILITY'
3100 )
3101 UNION ALL
3102 SELECT
3103 cg.condition_group_code CONDITION_GROUP_CODE,
3104 NULL PRIORITY ,--slokam
3105 gvat.enabled_flag ENABLED_FLAG ,
3106 rules.content_owner_id CONTENT_OWNER_ID ,
3107 cg.condition_group_id CONDITION_GROUP_ID ,
3111
3108 rules.TAX_RULE_ID TAX_RULE_ID ,
3109 taxgrp.tax_condition_id CONDITION_SET_ID ,
3110 taxgrp.tax_exception_id EXCEPTION_SET_ID
3112 FROM AR_VAT_TAX_ALL_B gvat,
3113 AR_TAX_GROUP_CODES_ALL taxgrp,
3114 ZX_TAXES_B TAXES,
3115 ZX_RULES_B rules,
3116 ZX_CONDITION_GROUPS_B cg,
3117 ar_system_parameters_all sys,
3118 zx_party_tax_profile ptp
3119
3120 WHERE taxgrp.tax_group_type = 'AR'
3121 AND taxgrp.product_fisc_classification ||
3122 taxgrp.trx_business_category_code ||
3123 taxgrp.party_fisc_classification IS NOT NULL
3124 AND gvat.vat_tax_id = taxgrp.tax_group_id
3125 AND gvat.tax_type = 'TAX_GROUP'
3126 AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
3127 AND taxes.live_for_applicability_flag = 'Y' -- add to filter location taxes defined in 11i
3128 AND taxes.tax_type_code = 'LOCATION'
3129 AND taxes.content_owner_id = -99
3130 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
3131 AND gvat.set_of_books_id = sys.set_of_books_id
3132 AND gvat.org_id = sys.org_id
3133 AND sys.org_id = ptp.party_id
3134 AND ptp.party_type_code = 'OU'
3135 /* AND EXISTS (SELECT 1
3136 FROM ar_vat_tax_all_b vat
3137 WHERE vat.tax_type = 'LOCATION'
3138 AND vat.set_of_books_id = sys.set_of_books_id
3139 AND vat.org_id = sys.org_id
3140 AND vat.enabled_flag = 'Y'
3141 ) */
3142 -- find the migrationed location based taxes in the tax group and
3143 -- filter the disabled location based tax
3144 AND EXISTS (SELECT 1
3145 FROM zx_rates_b rates,
3146 ar_vat_tax_all tax
3147 WHERE taxgrp.tax_code_id = tax.vat_tax_id
3148 AND taxgrp.org_id = tax.org_id
3149 AND tax.vat_tax_id = rates.tax_rate_id
3150 AND tax.tax_type = 'LOCATION'
3151 AND rates.tax <> taxes.tax
3152 AND rates.tax_regime_code = taxes.tax_regime_code
3153 AND rates.content_owner_id = taxes.content_owner_id
3154 AND rates.record_type_code = 'MIGRATED'
3155 )
3156 AND rules.tax_regime_code = taxes.tax_regime_code
3157 AND rules.tax = taxes.tax
3158 AND rules.content_owner_id = ptp.party_tax_profile_id
3159 AND rules.service_type_code ='DET_APPLICABLE_TAXES'
3160 AND rules.recovery_type_code IS NULL
3161 AND rules.tax_rule_code = taxes.tax
3162 --AND rules.effective_from = taxes.effective_from
3163 AND rules.priority = 1
3164 --Added following AND condition for Sync process
3165 AND gvat.vat_tax_id = nvl(p_tax_id,gvat.vat_tax_id)
3166 AND SUBSTRB(cg.condition_group_code, 1, 44) = SUBSTRB(gvat.tax_code,1,44)
3167 AND cg.ALPHANUMERIC_VALUE2||cg.ALPHANUMERIC_VALUE3||cg.ALPHANUMERIC_VALUE4
3168 = taxgrp.product_fisc_classification ||
3169 taxgrp.trx_business_category_code ||
3170 taxgrp.party_fisc_classification
3171 AND cg.enabled_flag = 'Y'
3172 AND not exists (select 1 from zx_process_results where
3173 tax_rule_id = rules.tax_rule_id
3174 and condition_group_code = cg.condition_group_code
3175 and result_type_code ='APPLICABILITY'
3176 )
3177 );
3178
3179
3180 -- Create rate det rule process results for the tax codes in the OKL tax group with at lease one
3181 -- not null PFC, PTFC, TBC
3182
3183 INSERT INTO ZX_PROCESS_RESULTS (
3184 CONDITION_GROUP_CODE ,
3185 PRIORITY ,
3186 RESULT_TYPE_CODE ,
3187 TAX_STATUS_CODE ,
3188 NUMERIC_RESULT ,
3189 ALPHANUMERIC_RESULT ,
3190 STATUS_RESULT ,
3191 RATE_RESULT ,
3192 LEGAL_MESSAGE_CODE ,
3193 MIN_TAX_AMT ,
3194 MAX_TAX_AMT ,
3195 MIN_TAXABLE_BASIS ,
3196 MAX_TAXABLE_BASIS ,
3197 MIN_TAX_RATE ,
3198 MAX_TAX_RATE ,
3199 ENABLED_FLAG ,
3200 ALLOW_EXEMPTIONS_FLAG ,
3201 ALLOW_EXCEPTIONS_FLAG ,
3202 RECORD_TYPE_CODE ,
3203 CREATION_DATE ,
3204 LAST_UPDATE_DATE ,
3205 REQUEST_ID ,
3206 PROGRAM_APPLICATION_ID,
3207 PROGRAM_ID ,
3208 CONDITION_SET_ID ,
3209 EXCEPTION_SET_ID ,
3210 PROGRAM_LOGIN_ID ,
3211 RESULT_ID ,
3212 CONTENT_OWNER_ID ,
3213 CONDITION_GROUP_ID ,
3214 TAX_RULE_ID ,
3215 CREATED_BY ,
3216 LAST_UPDATED_BY ,
3217 LAST_UPDATE_LOGIN ,
3218 RESULT_API ,
3219 OBJECT_VERSION_NUMBER
3220 )
3221 SELECT
3222 CONDITION_GROUP_CODE ,
3223 nvl(PRIORITY,ar_vat_tax_s.nextval),--slokam
3224 'CODE' , --RESULT_TYPE_CODE ,
3225 NULL , --TAX_STATUS_CODE ,
3226 NULL , --NUMERIC_RESULT ,
3227 'APPLICABLE' , --ALPHANUMERIC_RESULT ,
3228 STATUS_RESULT , --STATUS_RESULT ,
3229 RATE_RESULT , --RATE_RESULT ,
3230 NULL , --LEGAL_MESSAGE_CODE ,
3231 NULL , --MIN_TAX_AMT ,
3232 NULL , --MAX_TAX_AMT ,
3233 NULL , --MIN_TAXABLE_BASIS ,
3234 NULL , --MAX_TAXABLE_BASIS ,
3235 NULL , --MIN_TAX_RATE ,
3236 NULL , --MAX_TAX_RATE ,
3237 ENABLED_FLAG ,
3238 NULL , --ALLOW_EXEMPTIONS_FLAG ,
3242 sysdate , -- LAST_UPDATE_DATE ,
3239 NULL , --ALLOW_EXCEPTIONS_FLAG ,
3240 'MIGRATED' , --RECORD_TYPE_CODE ,
3241 sysdate , -- CREATION_DATE ,
3243 fnd_global.conc_request_id, -- REQUEST_ID ,
3244 fnd_global.prog_appl_id, --PROGRAM_APPLICATION_ID,
3245 NULL, -- PROGRAM_ID ,
3246 CONDITION_SET_ID ,
3247 EXCEPTION_SET_ID ,
3248 NULL , -- PROGRAM_LOGIN_ID ,
3249 zx_process_results_s.nextval,
3250 CONTENT_OWNER_ID ,
3251 condition_group_id,
3252 tax_rule_id,
3253 fnd_global.user_id , --CREATED_BY ,
3254 fnd_global.user_id , --LAST_UPDATED_BY ,
3255 fnd_global.conc_login_id, --LAST_UPDATE_LOGIN
3256 NULL , -- RESULT_API ,
3257 1 --OBJECT_VERSION_NUMBER
3258 FROM
3259 (
3260 SELECT
3261 cg.CONDITION_GROUP_CODE CONDITION_GROUP_CODE,
3262 NULL PRIORITY ,--slokam
3263 gvat.enabled_flag ENABLED_FLAG ,
3264 rules.content_owner_id CONTENT_OWNER_ID ,
3265 cg.CONDITION_GROUP_ID CONDITION_GROUP_ID ,
3266 rules.TAX_RULE_ID TAX_RULE_ID ,
3267 taxgrp.TAX_CONDITION_ID CONDITION_SET_ID ,
3268 taxgrp.TAX_EXCEPTION_ID EXCEPTION_SET_ID ,
3269 rates.tax_status_code STATUS_RESULT ,
3270 rates.tax_rate_code RATE_RESULT
3271
3272 FROM AR_VAT_TAX_ALL_B gvat,
3273 AR_TAX_GROUP_CODES_ALL taxgrp,
3274 AR_VAT_TAX_ALL_B vat,
3275 -- ZX_TAXES_B TAXES,
3276 ZX_RULES_B rules,
3277 ZX_CONDITION_GROUPS_B cg,
3278 ZX_RATES_B rates
3279 WHERE gvat.vat_tax_id = taxgrp.tax_group_id
3280 AND taxgrp.tax_group_type = 'AR'
3281 AND gvat.tax_type = 'TAX_GROUP'
3282 AND taxgrp.product_fisc_classification ||
3283 taxgrp.trx_business_category_code ||
3284 taxgrp.party_fisc_classification IS NOT NULL
3285 AND vat.tax_class = 'O'
3286 AND vat.vat_tax_id = taxgrp.tax_code_id
3287 AND vat.vat_tax_id = rates.tax_rate_id
3288 AND vat.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
3289 AND rates.RECORD_TYPE_CODE = 'MIGRATED'
3290 --AND rates.tax_regime_code = taxes.tax_regime_code
3291 --AND rates.tax = taxes.tax
3292 --AND taxes.RECORD_TYPE_CODE = 'MIGRATED'
3293 --AND rates.content_owner_id = taxes.content_owner_id
3294 AND rates.tax_regime_code = rules.tax_regime_code
3295 AND rates.tax = rules.tax
3296 AND rates.content_owner_id = rules.content_owner_id
3297 AND rules.service_type_code = 'DET_TAX_RATE'
3298 AND rules.recovery_type_code IS NULL
3299 AND rules.tax_rule_code = rates.TAX
3300 --AND rules.effective_from = taxes.effective_from
3301 AND rules.priority = 1
3302 --Added following AND condition for Sync process
3303 AND vat.vat_tax_id = nvl(p_tax_id,vat.vat_tax_id)
3304 AND SUBSTRB(cg.condition_group_code,1,44) = SUBSTRB(gvat.tax_code,1,44)
3305 AND cg.ALPHANUMERIC_VALUE2||cg.ALPHANUMERIC_VALUE3||cg.ALPHANUMERIC_VALUE4
3306 = taxgrp.product_fisc_classification ||
3307 taxgrp.trx_business_category_code ||
3308 taxgrp.party_fisc_classification
3309 AND cg.enabled_flag = vat.enabled_flag
3310 AND not exists (select 1 from zx_process_results where
3311 tax_rule_id = rules.tax_rule_id
3312 and condition_group_code = cg.condition_group_code
3313 and result_type_code = 'CODE'
3314 )
3315 UNION ALL
3316 SELECT
3317 cg.condition_group_code CONDITION_GROUP_CODE,
3318 NULL PRIORITY ,--slokam
3319 gvat.enabled_flag ENABLED_FLAG ,
3320 rules.content_owner_id CONTENT_OWNER_ID ,
3321 cg.condition_group_id CONDITION_GROUP_ID ,
3322 rules.TAX_RULE_ID TAX_RULE_ID ,
3323 taxgrp.tax_condition_id CONDITION_SET_ID ,
3324 taxgrp.tax_exception_id EXCEPTION_SET_ID ,
3325 rates.tax_status_code STATUS_RESULT ,
3326 rates.tax_rate_code RATE_RESULT
3327
3328 FROM AR_VAT_TAX_ALL_B gvat,
3329 AR_TAX_GROUP_CODES_ALL taxgrp,
3330 AR_VAT_TAX_ALL_B vat,
3331 ZX_TAXES_B TAXES,
3332 ZX_RULES_B rules,
3333 ZX_CONDITION_GROUPS_B cg,
3334 ar_system_parameters_all sys,
3335 zx_party_tax_profile ptp,
3336 ZX_RATES_B oklrates,
3337 ZX_RATES_B rates
3338
3339 WHERE taxgrp.tax_group_type = 'AR'
3340 AND taxgrp.product_fisc_classification ||
3341 taxgrp.trx_business_category_code ||
3342 taxgrp.party_fisc_classification IS NOT NULL
3343 AND gvat.vat_tax_id = taxgrp.tax_group_id
3344 AND gvat.tax_type = 'TAX_GROUP'
3345 AND vat.vat_tax_id = taxgrp.tax_code_id
3346 AND vat.tax_class = 'O'
3347 AND vat.vat_tax_id = oklrates.tax_rate_id -- not create rule for the disabled location based taxes
3348 AND vat.tax_type = 'LOCATION'
3349 AND oklrates.record_type_code = 'MIGRATED'
3350 AND rates.tax_regime_code = oklrates.tax_regime_code
3351 AND rates.tax = oklrates.tax
3352 AND rates.tax_status_code = oklrates.tax_status_code
3353 AND rates.tax_rate_code <> oklrates.tax_rate_code
3354 AND rates.record_type_code = 'MIGRATED'
3355 AND rates.tax_regime_code = taxes.tax_regime_code
3356 AND rates.tax = taxes.tax
3357 AND taxes.record_type_code = 'MIGRATED'
3358 AND taxes.tax_type_code = 'LOCATION'
3359 AND taxes.live_for_applicability_flag = 'Y'
3360 AND taxes.content_owner_id = -99
3361 AND taxes.tax_regime_code = sys.default_country||'-SALES-TAX-'||sys.location_structure_id
3362 AND gvat.set_of_books_id = sys.set_of_books_id
3363 AND gvat.org_id = sys.org_id
3364 AND ptp.party_id = sys.org_id
3365 AND ptp.party_type_code = 'OU'
3366 AND rules.tax_regime_code = taxes.tax_regime_code
3367 AND rules.tax = taxes.tax
3368 AND rules.content_owner_id = ptp.party_tax_profile_id
3369 AND rules.service_type_code ='DET_TAX_RATE'
3370 AND rules.recovery_type_code IS NULL
3371 AND rules.tax_rule_code = taxes.tax
3372 -- AND rules.effective_from = taxes.effective_from
3373 AND rules.priority = 1
3374 --Added following AND condition for Sync process
3375 AND vat.vat_tax_id = nvl(p_tax_id,vat.vat_tax_id)
3376 AND SUBSTRB(cg.condition_group_code, 1, 44) = SUBSTRB(gvat.tax_code,1,44)
3377 AND cg.ALPHANUMERIC_VALUE2||cg.ALPHANUMERIC_VALUE3||cg.ALPHANUMERIC_VALUE4
3378 = taxgrp.product_fisc_classification ||
3379 taxgrp.trx_business_category_code ||
3380 taxgrp.party_fisc_classification
3381 AND cg.enabled_flag = vat.enabled_flag
3382 AND not exists (select 1 from zx_process_results where
3383 tax_rule_id = rules.tax_rule_id
3384 and condition_group_code = cg.condition_group_code
3385 and result_type_code ='CODE'
3386 )
3387 );
3388
3389
3390 END IF;
3391
3392 IF PG_DEBUG = 'Y' THEN
3393 arp_util_tax.debug('Create_Process_Results(-)');
3394 END IF;
3395 EXCEPTION
3396 WHEN OTHERS THEN
3397 IF PG_DEBUG = 'Y' THEN
3398 arp_util_tax.debug('EXCEPTION: Create_process_results ');
3399 arp_util_tax.debug(sqlerrm);
3400 arp_util_tax.debug('Create_Process_Results(-)');
3401 END IF;
3402 --app_exception.raise_exception;
3403 END create_process_results;
3404
3405 BEGIN
3406 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
3407 FND_PRODUCT_GROUPS;
3408
3409 IF L_MULTI_ORG_FLAG = 'N' THEN
3410
3411 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
3412
3413 IF L_ORG_ID IS NULL THEN
3414 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
3415 END IF;
3416 ELSE
3417 L_ORG_ID := NULL;
3418 END IF;
3419
3420
3421 EXCEPTION
3422 WHEN OTHERS THEN
3423 arp_util_tax.debug('Exception in constructor of Tax Hierarchy Migration '||sqlerrm);
3424
3425
3426
3427 END Zx_Migrate_Tax_Default_Hier;