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