[Home] [Help]
PACKAGE BODY: APPS.ZX_MIG_GL_TAX_OPTIONS_PKG
Source
1 PACKAGE BODY zx_mig_gl_tax_options_pkg AS
2 /* $Header: zxmiggltaxoptb.pls 120.9.12010000.1 2008/07/28 13:34:19 appldev ship $ */
3
4 PG_DEBUG CONSTANT VARCHAR(1) default
5 NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 PROCEDURE zx_mig_gl_tax_options_header(P_Ledger_Id IN NUMBER,
8 P_Org_id IN NUMBER);
9
10 PROCEDURE zx_mig_gl_tax_options_nt(P_Ledger_Id IN NUMBER,
11 P_Org_id IN NUMBER,
12 P_Account_Segment_Value IN VARCHAR2 );
13
14 PROCEDURE zx_mig_gl_tax_options_ap(P_Ledger_Id IN NUMBER,
15 P_Org_id IN NUMBER,
16 P_Account_Segment_Value IN VARCHAR2 );
17
18 PROCEDURE zx_mig_gl_tax_options_ar(P_Ledger_Id IN NUMBER,
19 P_Org_id IN NUMBER,
20 P_Account_Segment_Value IN VARCHAR2 );
21
22
23 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
24 L_ORG_ID NUMBER(15);
25
26 /*===========================================================================+
27 | PROCEDURE |
28 | zx_sync_gl_tax_options |
29 | |
30 | DESCRIPTION |
31 | This routine is a wrapper for synchroniztion of GL TAX OPTIONS SETUP. |
32 | |
33 | SCOPE - PUBLIC |
34 | |
35 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
36 | |
37 | CALLED FROM |
38 | Public Procedure |
39 | NOTES |
40 | |
41 | MODIFICATION HISTORY |
42 | 30-Mar-05 Vamshi/Vinit Created. |
43 | |
44 +==========================================================================*/
45
46
47 PROCEDURE zx_sync_gl_tax_options( P_Ledger_Id IN NUMBER,
48 P_Org_id IN NUMBER,
49 P_Account_Segment_Value IN VARCHAR2,
50 P_Tax_Type_Code IN VARCHAR2) is
51 BEGIN
52 IF PG_DEBUG = 'Y' THEN
53 arp_util_tax.debug('zx_sync_gl_tax_options(+)');
54 END IF;
55
56 if(P_Account_Segment_Value is NULL and p_tax_type_code is NULL ) then
57 zx_mig_gl_tax_options_header(P_Ledger_Id,P_Org_id);
58 elsif(P_Tax_Type_Code='N') then
59 zx_mig_gl_tax_options_nt(P_Ledger_Id,P_Org_id,P_Account_Segment_Value);
60 else
61 if(P_Tax_Type_Code='I' or P_Tax_Type_Code='B') then
62 zx_mig_gl_tax_options_ap(P_Ledger_Id,P_Org_id,P_Account_Segment_Value);
63 end if;
64 if(P_Tax_Type_Code='O' or P_Tax_Type_Code='B') then
65 zx_mig_gl_tax_options_ar(P_Ledger_Id,P_Org_id,P_Account_Segment_Value);
66 end if;
67 end if;
68
69 IF PG_DEBUG = 'Y' THEN
70 arp_util_tax.debug('zx_sync_gl_tax_options(-)');
71 END IF;
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF PG_DEBUG = 'Y' THEN
75 arp_util_tax.debug('EXCEPTION: zx_sync_gl_tax_options ');
76 arp_util_tax.debug(sqlerrm);
77 arp_util_tax.debug('zx_sync_gl_tax_options(-)');
78 END IF;
79 app_exception.raise_exception;
80 End zx_sync_gl_tax_options;
81
82 /*===========================================================================+
83 | PROCEDURE |
84 | zx_mig_gl_tax_options |
85 | |
86 | DESCRIPTION |
87 | This routine is a wrapper for migration of GL TAX OPTIONS SETUP. |
88 | |
89 | SCOPE - PUBLIC |
90 | |
91 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
92 | |
93 | CALLED FROM |
94 | Public Procedure |
95 | NOTES |
96 | |
97 | MODIFICATION HISTORY |
98 | 09-Feb-05 Vamshi/Vinit Created. |
99 | |
100 +==========================================================================*/
101
102
103 PROCEDURE zx_mig_gl_tax_options is
104 BEGIN
105 IF PG_DEBUG = 'Y' THEN
106 arp_util_tax.debug('zx_mig_gl_tax_options(+)');
107 END IF;
108
109 zx_mig_gl_tax_options_header(NULL,NULL);
110
111 zx_mig_gl_tax_options_nt(NULL,NULL,NULL);
112
113 IF ZX_MIGRATE_UTIL.IS_INSTALLED('AP') = 'Y' THEN
114 zx_mig_gl_tax_options_ap(NULL,NULL,NULL);
115 END IF;
116
117 IF ZX_MIGRATE_UTIL.IS_INSTALLED('AR') = 'Y' THEN
118 zx_mig_gl_tax_options_ar(NULL,NULL,NULL);
119 END IF;
120
121 IF PG_DEBUG = 'Y' THEN
122 arp_util_tax.debug('zx_mig_gl_tax_options(-)');
123 END IF;
124 EXCEPTION
125 WHEN OTHERS THEN
126 IF PG_DEBUG = 'Y' THEN
127 arp_util_tax.debug('EXCEPTION: zx_mig_gl_tax_options ');
128 arp_util_tax.debug(sqlerrm);
129 arp_util_tax.debug('zx_mig_gl_tax_options(-)');
130 END IF;
131 app_exception.raise_exception;
132 END zx_mig_gl_tax_options;
133
134 /*===========================================================================+
135 | PROCEDURE |
136 | zx_mig_gl_tax_options_header |
137 | |
138 | DESCRIPTION |
139 | This routine migrates GL TAX OPTIONS SETUP Header Information. |
140 | |
141 | SCOPE - PRIVATE |
142 | |
143 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
144 | |
145 | CALLED FROM |
146 | zx_mig_gl_tax_options |
147 | NOTES |
148 | |
149 | MODIFICATION HISTORY |
150 | 24-Mar-05 Vamshi/Vinit Created. |
151 | |
152 +==========================================================================*/
153
154
155 PROCEDURE zx_mig_gl_tax_options_header(P_Ledger_Id number,P_Org_id number) is
156 BEGIN
157 IF PG_DEBUG = 'Y' THEN
158 arp_util_tax.debug('zx_mig_gl_tax_options_header(+)');
159 END IF;
160
161 Insert ALL
162 Into zx_account_rates_tmp
163 (
164 LEDGER_ID ,
165 CONTENT_OWNER_ID ,
166 ACCOUNT_SEGMENT_VALUE ,
167 TAX_PRECISION ,
168 CALCULATION_LEVEL_CODE ,
169 ALLOW_RATE_OVERRIDE_FLAG ,
170 TAX_MAU ,
171 TAX_CURRENCY_CODE ,
172 TAX_CLASS ,
173 TAX_REGIME_CODE ,
174 TAX ,
175 TAX_STATUS_CODE ,
176 TAX_RATE_CODE ,
177 ROUNDING_RULE_CODE ,
178 AMT_INCL_TAX_FLAG ,
179 RECORD_TYPE_CODE ,
180 CREATION_DATE ,
181 CREATED_BY ,
182 LAST_UPDATED_BY ,
183 LAST_UPDATE_DATE ,
184 LAST_UPDATE_LOGIN ,
185 ATTRIBUTE_CATEGORY ,
186 ATTRIBUTE1 ,
187 ATTRIBUTE2 ,
188 ATTRIBUTE3 ,
189 ATTRIBUTE4 ,
190 ATTRIBUTE5 ,
191 ATTRIBUTE6 ,
192 ATTRIBUTE7 ,
193 ATTRIBUTE8 ,
194 ATTRIBUTE9 ,
195 ATTRIBUTE10 ,
196 ATTRIBUTE11 ,
197 ATTRIBUTE12 ,
198 ATTRIBUTE13 ,
199 ATTRIBUTE14 ,
200 ATTRIBUTE15 ,
201 ALLOW_ROUNDING_OVERRIDE_FLAG)
202 Values
203 (
204 LEDGER_ID ,
205 CONTENT_OWNER_ID ,
206 ACCOUNT_SEGMENT_VALUE ,
207 TAX_PRECISION ,
208 CALCULATION_LEVEL_CODE ,
209 ALLOW_RATE_OVERRIDE_FLAG ,
210 TAX_MAU ,
211 TAX_CURRENCY_CODE ,
212 TAX_CLASS ,
213 TAX_REGIME_CODE ,
214 TAX ,
215 TAX_STATUS_CODE ,
216 TAX_RATE_CODE ,
217 ROUNDING_RULE_CODE ,
218 AMT_INCL_TAX_FLAG ,
219 RECORD_TYPE_CODE ,
220 CREATION_DATE ,
221 CREATED_BY ,
222 LAST_UPDATED_BY ,
223 LAST_UPDATE_DATE ,
224 LAST_UPDATE_LOGIN ,
225 ATTRIBUTE_CATEGORY ,
226 ATTRIBUTE1 ,
227 ATTRIBUTE2 ,
228 ATTRIBUTE3 ,
229 ATTRIBUTE4 ,
230 ATTRIBUTE5 ,
231 ATTRIBUTE6 ,
232 ATTRIBUTE7 ,
233 ATTRIBUTE8 ,
234 ATTRIBUTE9 ,
235 ATTRIBUTE10 ,
236 ATTRIBUTE11 ,
237 ATTRIBUTE12 ,
238 ATTRIBUTE13 ,
239 ATTRIBUTE14 ,
240 ATTRIBUTE15 ,
241 ALLOW_ROUNDING_OVERRIDE_FLAG)
242 Select
243 opt.ledger_id LEDGER_ID,
244 ptp.PARTY_TAX_PROFILE_ID CONTENT_OWNER_ID,
245 NULL ACCOUNT_SEGMENT_VALUE ,
246 opt.TAX_PRECISION TAX_PRECISION,
247 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
248 NULL ALLOW_RATE_OVERRIDE_FLAG ,
249 opt.TAX_MAU TAX_MAU,
250 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
251 NULL TAX_CLASS,
252 NULL TAX_REGIME_CODE,
253 NULL TAX,
254 NULL TAX_STATUS_CODE,
255 NULL TAX_RATE_CODE,
256 opt.INPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
257 opt.INPUT_AMT_INCL_TAX_FLAG AMT_INCL_TAX_FLAG,
258 'MIGRATED' RECORD_TYPE_CODE,
259 SYSDATE CREATION_DATE,
260 fnd_global.user_id CREATED_BY,
261 fnd_global.user_id LAST_UPDATED_BY,
262 SYSDATE LAST_UPDATE_DATE,
263 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
264 NULL ATTRIBUTE_CATEGORY,
265 NULL ATTRIBUTE1,
266 NULL ATTRIBUTE2,
267 NULL ATTRIBUTE3,
268 NULL ATTRIBUTE4,
269 NULL ATTRIBUTE5,
270 NULL ATTRIBUTE6,
271 NULL ATTRIBUTE7,
272 NULL ATTRIBUTE8,
273 NULL ATTRIBUTE9,
274 NULL ATTRIBUTE10,
275 NULL ATTRIBUTE11,
276 NULL ATTRIBUTE12,
277 NULL ATTRIBUTE13,
278 NULL ATTRIBUTE14,
279 NULL ATTRIBUTE15,
280 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
281 From
282 GL_TAX_OPTIONS opt,
283 zx_party_tax_profile ptp
284 Where
285 opt.input_tax_code is NULL and
286 opt.output_tax_code is NULL and
287 ptp.party_id = decode(l_multi_org_flag,'N',l_org_id,opt.org_id) and
288 ptp.party_type_code = 'OU' and
289 opt.ledger_id =nvl(P_Ledger_Id,opt.ledger_id) and --added for sync
290 decode(l_multi_org_flag,'N',l_org_id,opt.org_id) =nvl(P_Org_id, decode(l_multi_org_flag,'N',l_org_id,opt.org_id)) and --added for Sync
291 NOT EXISTS ( Select 1
292 From zx_account_rates_tmp -- Bug 6671444
296 and tax_class is NULL );
293 Where ledger_id = opt.ledger_id
294 and content_owner_id = ptp.PARTY_TAX_PROFILE_ID
295 and account_segment_value is null
297
298 IF PG_DEBUG = 'Y' THEN
299 arp_util_tax.debug('zx_mig_gl_tax_options_header(-)');
300 END IF;
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF PG_DEBUG = 'Y' THEN
304 arp_util_tax.debug('EXCEPTION: zx_mig_gl_tax_options_header ');
305 arp_util_tax.debug(sqlerrm);
306 arp_util_tax.debug('zx_mig_gl_tax_options_header(-)');
307 END IF;
308 app_exception.raise_exception;
309 END zx_mig_gl_tax_options_header;
310
311 /*===========================================================================+
312 | PROCEDURE |
313 | zx_mig_gl_tax_options_nt |
314 | |
315 | DESCRIPTION |
316 | This routine migrates GL TAX OPTIONS SETUP for Non-Taxable Account |
317 | Type. |
318 | |
319 | SCOPE - PRIVATE |
320 | |
321 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
322 | |
323 | CALLED FROM |
324 | zx_mig_gl_tax_options |
325 | NOTES |
326 | |
327 | MODIFICATION HISTORY |
328 | 09-Feb-05 Vamshi/Vinit Created. |
329 | |
330 +==========================================================================*/
331
332
333 PROCEDURE zx_mig_gl_tax_options_nt(P_Ledger_Id number,P_Org_id number,P_Account_Segment_Value varchar2) is
334 BEGIN
335 IF PG_DEBUG = 'Y' THEN
336 arp_util_tax.debug('zx_mig_gl_tax_options_nt(+)');
337 END IF;
338
339 Insert ALL
340 Into zx_account_rates_tmp
341 (
342 LEDGER_ID ,
343 CONTENT_OWNER_ID ,
344 ACCOUNT_SEGMENT_VALUE ,
345 TAX_PRECISION ,
346 CALCULATION_LEVEL_CODE ,
347 ALLOW_RATE_OVERRIDE_FLAG ,
348 TAX_MAU ,
349 TAX_CURRENCY_CODE ,
350 TAX_CLASS ,
351 TAX_REGIME_CODE ,
352 TAX ,
353 TAX_STATUS_CODE ,
354 TAX_RATE_CODE ,
355 ROUNDING_RULE_CODE ,
356 AMT_INCL_TAX_FLAG ,
357 RECORD_TYPE_CODE ,
358 CREATION_DATE ,
359 CREATED_BY ,
360 LAST_UPDATED_BY ,
361 LAST_UPDATE_DATE ,
362 LAST_UPDATE_LOGIN ,
363 ATTRIBUTE_CATEGORY ,
364 ATTRIBUTE1 ,
365 ATTRIBUTE2 ,
366 ATTRIBUTE3 ,
367 ATTRIBUTE4 ,
368 ATTRIBUTE5 ,
369 ATTRIBUTE6 ,
370 ATTRIBUTE7 ,
371 ATTRIBUTE8 ,
372 ATTRIBUTE9 ,
373 ATTRIBUTE10 ,
374 ATTRIBUTE11 ,
375 ATTRIBUTE12 ,
376 ATTRIBUTE13 ,
377 ATTRIBUTE14 ,
378 ATTRIBUTE15 ,
379 ALLOW_ROUNDING_OVERRIDE_FLAG)
380 Values
381 (
382 LEDGER_ID ,
383 CONTENT_OWNER_ID ,
384 ACCOUNT_SEGMENT_VALUE ,
385 TAX_PRECISION ,
386 CALCULATION_LEVEL_CODE ,
387 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
388 TAX_MAU ,
389 TAX_CURRENCY_CODE ,
390 TAX_CLASS ,
391 TAX_REGIME_CODE ,
392 TAX ,
393 TAX_STATUS_CODE ,
394 TAX_RATE_CODE ,
395 ROUNDING_RULE_CODE ,
396 AMT_INCL_TAX_FLAG ,
397 RECORD_TYPE_CODE ,
398 CREATION_DATE ,
399 CREATED_BY ,
400 LAST_UPDATED_BY ,
401 LAST_UPDATE_DATE ,
402 LAST_UPDATE_LOGIN ,
403 ATTRIBUTE_CATEGORY ,
404 ATTRIBUTE1 ,
405 ATTRIBUTE2 ,
406 ATTRIBUTE3 ,
407 ATTRIBUTE4 ,
408 ATTRIBUTE5 ,
409 ATTRIBUTE6 ,
410 ATTRIBUTE7 ,
411 ATTRIBUTE8 ,
412 ATTRIBUTE9 ,
416 ATTRIBUTE13 ,
413 ATTRIBUTE10 ,
414 ATTRIBUTE11 ,
415 ATTRIBUTE12 ,
417 ATTRIBUTE14 ,
418 ATTRIBUTE15 ,
419 ALLOW_ROUNDING_OVERRIDE_FLAG)
420 Select
421 accounts.ledger_id LEDGER_ID,
422 ptp.PARTY_TAX_PROFILE_ID CONTENT_OWNER_ID,
423 ACCOUNT_SEGMENT_VALUE ACCOUNT_SEGMENT_VALUE,
424 'NON_TAXABLE' TAX_CLASS,
425 opt.TAX_PRECISION TAX_PRECISION,
426 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
427 opt.TAX_MAU TAX_MAU,
428 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
429 NULL TAX_CLASSIFICATION_CODE,
430 opt.INPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
431 NULL TAX_REGIME_CODE,
432 NULL TAX,
433 NULL TAX_STATUS_CODE,
434 NULL TAX_RATE_CODE,
435 accounts.ALLOW_TAX_CODE_OVERRIDE_FLAG ALLOW_TAX_CODE_OVERRIDE_FLAG,
436 accounts.AMOUNT_INCLUDES_TAX_FLAG AMT_INCL_TAX_FLAG,
437 'MIGRATED' RECORD_TYPE_CODE,
438 SYSDATE CREATION_DATE,
439 fnd_global.user_id CREATED_BY,
440 fnd_global.user_id LAST_UPDATED_BY,
441 SYSDATE LAST_UPDATE_DATE,
442 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
443 NULL ATTRIBUTE_CATEGORY,
444 NULL ATTRIBUTE1,
445 NULL ATTRIBUTE2,
446 NULL ATTRIBUTE3,
447 NULL ATTRIBUTE4,
448 NULL ATTRIBUTE5,
449 NULL ATTRIBUTE6,
450 NULL ATTRIBUTE7,
451 NULL ATTRIBUTE8,
452 NULL ATTRIBUTE9,
453 NULL ATTRIBUTE10,
454 NULL ATTRIBUTE11,
455 NULL ATTRIBUTE12,
456 NULL ATTRIBUTE13,
457 NULL ATTRIBUTE14,
458 NULL ATTRIBUTE15,
459 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
460 From
461 gl_tax_option_accounts accounts,
462 gl_tax_options opt,
463 zx_party_tax_profile ptp
464 Where
465 accounts.ledger_id = opt.ledger_id and
466 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) = decode(l_multi_org_flag,'N',l_org_id,opt.org_id) and
467 accounts.tax_type_code = 'N' and
468 ptp.party_id = decode(l_multi_org_flag,'N',l_org_id,opt.org_id) and
469 ptp.party_type_code = 'OU' and
470 accounts.ledger_id =nvl(P_Ledger_Id,accounts.ledger_id) and --added for sync
471 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) =nvl(P_Org_id,decode(l_multi_org_flag,'N',l_org_id,accounts.org_id)) and
472 --added for Sync
473 accounts.account_segment_value = nvl(P_Account_Segment_Value,accounts.account_segment_value) and --added for sync
474 NOT EXISTS ( Select 1
475 From zx_account_rates_tmp --Bug 6671444
476 Where ledger_id = opt.ledger_id
477 and content_owner_id = ptp.party_tax_profile_id
478 and account_segment_value = accounts.account_segment_value
479 and tax_class = 'NON_TAXABLE' );
480
481 IF PG_DEBUG = 'Y' THEN
482 arp_util_tax.debug('zx_mig_gl_tax_options_nt(-)');
483 END IF;
484 EXCEPTION
485 WHEN OTHERS THEN
486 IF PG_DEBUG = 'Y' THEN
487 arp_util_tax.debug('EXCEPTION: zx_mig_gl_tax_options_nt ');
488 arp_util_tax.debug(sqlerrm);
489 arp_util_tax.debug('zx_mig_gl_tax_options_nt(-)');
490 END IF;
491 app_exception.raise_exception;
492 END zx_mig_gl_tax_options_nt;
493
494 /*===========================================================================+
495 | PROCEDURE |
496 | zx_mig_gl_tax_options_ap |
497 | |
498 | DESCRIPTION |
499 | This routine migrates GL TAX OPTIONS SETUP for AP(Input Tax). |
500 | |
501 | SCOPE - PRIVATE |
502 | |
503 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
504 | |
505 | CALLED FROM |
506 | zx_mig_gl_tax_options |
507 | NOTES |
508 | |
509 | MODIFICATION HISTORY |
510 | 09-Feb-05 Vamshi/Vinit Created. |
511 | |
512 +==========================================================================*/
513
514
518 arp_util_tax.debug('zx_mig_gl_tax_options_ap(+)');
515 PROCEDURE zx_mig_gl_tax_options_ap(P_Ledger_Id number,P_Org_id number,P_Account_Segment_Value varchar2) is
516 BEGIN
517 IF PG_DEBUG = 'Y' THEN
519 END IF;
520
521 Insert ALL
522 Into zx_account_rates_tmp
523 (
524 LEDGER_ID ,
525 CONTENT_OWNER_ID ,
526 ACCOUNT_SEGMENT_VALUE ,
527 TAX_PRECISION ,
528 CALCULATION_LEVEL_CODE ,
529 ALLOW_RATE_OVERRIDE_FLAG ,
530 TAX_MAU ,
531 TAX_CURRENCY_CODE ,
532 TAX_CLASS ,
533 TAX_REGIME_CODE ,
534 TAX ,
535 TAX_STATUS_CODE ,
536 TAX_RATE_CODE ,
537 ROUNDING_RULE_CODE ,
538 AMT_INCL_TAX_FLAG ,
539 RECORD_TYPE_CODE ,
540 CREATION_DATE ,
541 CREATED_BY ,
542 LAST_UPDATED_BY ,
543 LAST_UPDATE_DATE ,
544 LAST_UPDATE_LOGIN ,
545 ATTRIBUTE_CATEGORY ,
546 ATTRIBUTE1 ,
547 ATTRIBUTE2 ,
548 ATTRIBUTE3 ,
549 ATTRIBUTE4 ,
550 ATTRIBUTE5 ,
551 ATTRIBUTE6 ,
552 ATTRIBUTE7 ,
553 ATTRIBUTE8 ,
554 ATTRIBUTE9 ,
555 ATTRIBUTE10 ,
556 ATTRIBUTE11 ,
557 ATTRIBUTE12 ,
558 ATTRIBUTE13 ,
559 ATTRIBUTE14 ,
560 ATTRIBUTE15 ,
561 ALLOW_ROUNDING_OVERRIDE_FLAG)
562 Values
563 (
564 LEDGER_ID ,
565 CONTENT_OWNER_ID ,
566 ACCOUNT_SEGMENT_VALUE ,
567 TAX_PRECISION ,
568 CALCULATION_LEVEL_CODE ,
569 ALLOW_RATE_OVERRIDE_FLAG ,
570 TAX_MAU ,
571 TAX_CURRENCY_CODE ,
572 TAX_CLASS ,
573 TAX_REGIME_CODE ,
574 TAX ,
575 TAX_STATUS_CODE ,
576 TAX_RATE_CODE ,
577 ROUNDING_RULE_CODE ,
578 AMT_INCL_TAX_FLAG ,
579 RECORD_TYPE_CODE ,
580 CREATION_DATE ,
581 CREATED_BY ,
582 LAST_UPDATED_BY ,
583 LAST_UPDATE_DATE ,
584 LAST_UPDATE_LOGIN ,
585 ATTRIBUTE_CATEGORY ,
586 ATTRIBUTE1 ,
587 ATTRIBUTE2 ,
588 ATTRIBUTE3 ,
589 ATTRIBUTE4 ,
590 ATTRIBUTE5 ,
591 ATTRIBUTE6 ,
592 ATTRIBUTE7 ,
593 ATTRIBUTE8 ,
594 ATTRIBUTE9 ,
595 ATTRIBUTE10 ,
596 ATTRIBUTE11 ,
597 ATTRIBUTE12 ,
598 ATTRIBUTE13 ,
599 ATTRIBUTE14 ,
600 ATTRIBUTE15 ,
601 ALLOW_ROUNDING_OVERRIDE_FLAG)
602 Select
603 opt.ledger_id LEDGER_ID,
604 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID,
605 NULL ACCOUNT_SEGMENT_VALUE ,
606 opt.TAX_PRECISION TAX_PRECISION,
607 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
608 NULL ALLOW_RATE_OVERRIDE_FLAG ,
609 opt.TAX_MAU TAX_MAU,
610 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
611 'INPUT' TAX_CLASS,
612 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
613 rates.TAX TAX,
614 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
615 rates.TAX_RATE_CODE TAX_RATE_CODE,
616 opt.INPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
617 opt.INPUT_AMT_INCL_TAX_FLAG AMT_INCL_TAX_FLAG,
618 'MIGRATED' RECORD_TYPE_CODE,
619 SYSDATE CREATION_DATE,
620 fnd_global.user_id CREATED_BY,
621 fnd_global.user_id LAST_UPDATED_BY,
622 SYSDATE LAST_UPDATE_DATE,
623 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
624 NULL ATTRIBUTE_CATEGORY,
625 NULL ATTRIBUTE1,
626 NULL ATTRIBUTE2,
627 NULL ATTRIBUTE3,
628 NULL ATTRIBUTE4,
629 NULL ATTRIBUTE5,
633 NULL ATTRIBUTE9,
630 NULL ATTRIBUTE6,
631 NULL ATTRIBUTE7,
632 NULL ATTRIBUTE8,
634 NULL ATTRIBUTE10,
635 NULL ATTRIBUTE11,
636 NULL ATTRIBUTE12,
637 NULL ATTRIBUTE13,
638 NULL ATTRIBUTE14,
639 NULL ATTRIBUTE15,
640 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
641 From
642 GL_TAX_OPTIONS opt,
643 ap_tax_codes_all aptax,
644 zx_RATES_B rates
645 Where
646 opt.ledger_id = aptax.set_of_books_id and
647 decode(l_multi_org_flag,'N',l_org_id,opt.org_id)= decode(l_multi_org_flag,'N',l_org_id,aptax.org_id) and
648 opt.input_tax_code = aptax.name and
649 sysdate between aptax.start_date and nvl(aptax.inactive_date,sysdate) and
650 aptax.tax_id=nvl(rates.source_id,rates.tax_rate_id) and
651 nvl(aptax.enabled_flag, 'Y') = 'Y' and
652 rates.record_type_code = 'MIGRATED' and
653 nvl(rates.tax_class,'INPUT') = 'INPUT' and
654 opt.ledger_id =nvl(P_Ledger_Id,opt.ledger_id) and --added for sync
655 decode(l_multi_org_flag,'N',l_org_id,opt.org_id)=nvl(P_Org_id,decode(l_multi_org_flag,'N',l_org_id,opt.org_id)) and --added for Sync
656 NOT EXISTS ( Select 1
657 From zx_account_rates_tmp --Bug 6671444
658 Where ledger_id = opt.ledger_id
659 and content_owner_id = rates.content_owner_id
660 and account_segment_value is null
661 and tax_class = 'INPUT' );
662
663 Insert ALL
664 Into zx_account_rates_tmp
665 (
666 LEDGER_ID ,
667 CONTENT_OWNER_ID ,
668 ACCOUNT_SEGMENT_VALUE ,
669 TAX_PRECISION ,
670 CALCULATION_LEVEL_CODE ,
671 ALLOW_RATE_OVERRIDE_FLAG ,
672 TAX_MAU ,
673 TAX_CURRENCY_CODE ,
674 TAX_CLASS ,
675 TAX_REGIME_CODE ,
676 TAX ,
677 TAX_STATUS_CODE ,
678 TAX_RATE_CODE ,
679 ROUNDING_RULE_CODE ,
680 AMT_INCL_TAX_FLAG ,
681 RECORD_TYPE_CODE ,
682 CREATION_DATE ,
683 CREATED_BY ,
684 LAST_UPDATED_BY ,
685 LAST_UPDATE_DATE ,
686 LAST_UPDATE_LOGIN ,
687 ATTRIBUTE_CATEGORY ,
688 ATTRIBUTE1 ,
689 ATTRIBUTE2 ,
690 ATTRIBUTE3 ,
691 ATTRIBUTE4 ,
692 ATTRIBUTE5 ,
693 ATTRIBUTE6 ,
694 ATTRIBUTE7 ,
695 ATTRIBUTE8 ,
696 ATTRIBUTE9 ,
697 ATTRIBUTE10 ,
698 ATTRIBUTE11 ,
699 ATTRIBUTE12 ,
700 ATTRIBUTE13 ,
701 ATTRIBUTE14 ,
702 ATTRIBUTE15 ,
703 ALLOW_ROUNDING_OVERRIDE_FLAG)
704 Values
705 (
706 LEDGER_ID ,
707 CONTENT_OWNER_ID ,
708 ACCOUNT_SEGMENT_VALUE ,
709 TAX_PRECISION ,
710 CALCULATION_LEVEL_CODE ,
711 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
712 TAX_MAU ,
713 TAX_CURRENCY_CODE ,
714 TAX_CLASS ,
715 TAX_REGIME_CODE ,
716 TAX ,
717 TAX_STATUS_CODE ,
718 TAX_RATE_CODE ,
719 ROUNDING_RULE_CODE ,
720 AMT_INCL_TAX_FLAG ,
721 RECORD_TYPE_CODE ,
722 CREATION_DATE ,
723 CREATED_BY ,
724 LAST_UPDATED_BY ,
725 LAST_UPDATE_DATE ,
726 LAST_UPDATE_LOGIN ,
727 ATTRIBUTE_CATEGORY ,
728 ATTRIBUTE1 ,
729 ATTRIBUTE2 ,
730 ATTRIBUTE3 ,
731 ATTRIBUTE4 ,
732 ATTRIBUTE5 ,
733 ATTRIBUTE6 ,
734 ATTRIBUTE7 ,
735 ATTRIBUTE8 ,
736 ATTRIBUTE9 ,
737 ATTRIBUTE10 ,
738 ATTRIBUTE11 ,
739 ATTRIBUTE12 ,
740 ATTRIBUTE13 ,
741 ATTRIBUTE14 ,
742 ATTRIBUTE15 ,
743 ALLOW_ROUNDING_OVERRIDE_FLAG)
744 INTO ZX_ACCT_TX_CLS_DEFS_ALL
745 (
746 LEDGER_ID ,
747 ORG_ID ,
748 ACCOUNT_SEGMENT_VALUE ,
749 TAX_CLASS ,
750 TAX_CLASSIFICATION_CODE ,
751 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
752 RECORD_TYPE_CODE ,
753 CREATION_DATE ,
754 CREATED_BY ,
755 LAST_UPDATED_BY ,
756 LAST_UPDATE_DATE ,
757 LAST_UPDATE_LOGIN ,
758 ATTRIBUTE_CATEGORY ,
762 ATTRIBUTE4 ,
759 ATTRIBUTE1 ,
760 ATTRIBUTE2 ,
761 ATTRIBUTE3 ,
763 ATTRIBUTE5 ,
764 ATTRIBUTE6 ,
765 ATTRIBUTE7 ,
766 ATTRIBUTE8 ,
767 ATTRIBUTE9 ,
768 ATTRIBUTE10 ,
769 ATTRIBUTE11 ,
770 ATTRIBUTE12 ,
771 ATTRIBUTE13 ,
772 ATTRIBUTE14 ,
773 ATTRIBUTE15 )
774 Values
775 (
776 LEDGER_ID ,
777 ORG_ID ,
778 ACCOUNT_SEGMENT_VALUE ,
779 TAX_CLASS ,
780 TAX_CLASSIFICATION_CODE ,
781 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
782 RECORD_TYPE_CODE ,
783 CREATION_DATE ,
784 CREATED_BY ,
785 LAST_UPDATED_BY ,
786 LAST_UPDATE_DATE ,
787 LAST_UPDATE_LOGIN ,
788 ATTRIBUTE_CATEGORY ,
789 ATTRIBUTE1 ,
790 ATTRIBUTE2 ,
791 ATTRIBUTE3 ,
792 ATTRIBUTE4 ,
793 ATTRIBUTE5 ,
794 ATTRIBUTE6 ,
795 ATTRIBUTE7 ,
796 ATTRIBUTE8 ,
797 ATTRIBUTE9 ,
798 ATTRIBUTE10 ,
799 ATTRIBUTE11 ,
800 ATTRIBUTE12 ,
801 ATTRIBUTE13 ,
802 ATTRIBUTE14 ,
803 ATTRIBUTE15 )
804 Select
805 accounts.ledger_id LEDGER_ID,
806 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID,
807 decode(l_multi_org_flag,'N',l_org_id,accounts.ORG_ID) ORG_ID,
808 ACCOUNT_SEGMENT_VALUE ACCOUNT_SEGMENT_VALUE,
809 'INPUT' TAX_CLASS,
810 opt.TAX_PRECISION TAX_PRECISION,
811 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
812 opt.TAX_MAU TAX_MAU,
813 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
814 accounts.TAX_CODE TAX_CLASSIFICATION_CODE,
815 opt.INPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
816 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
817 rates.TAX TAX,
818 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
819 rates.TAX_RATE_CODE TAX_RATE_CODE,
820 accounts.ALLOW_TAX_CODE_OVERRIDE_FLAG ALLOW_TAX_CODE_OVERRIDE_FLAG,
821 accounts.AMOUNT_INCLUDES_TAX_FLAG AMT_INCL_TAX_FLAG,
822 'MIGRATED' RECORD_TYPE_CODE,
823 SYSDATE CREATION_DATE,
824 fnd_global.user_id CREATED_BY,
825 fnd_global.user_id LAST_UPDATED_BY,
826 SYSDATE LAST_UPDATE_DATE,
827 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
828 NULL ATTRIBUTE_CATEGORY,
829 NULL ATTRIBUTE1,
830 NULL ATTRIBUTE2,
831 NULL ATTRIBUTE3,
832 NULL ATTRIBUTE4,
833 NULL ATTRIBUTE5,
834 NULL ATTRIBUTE6,
835 NULL ATTRIBUTE7,
836 NULL ATTRIBUTE8,
837 NULL ATTRIBUTE9,
838 NULL ATTRIBUTE10,
839 NULL ATTRIBUTE11,
840 NULL ATTRIBUTE12,
841 NULL ATTRIBUTE13,
842 NULL ATTRIBUTE14,
843 NULL ATTRIBUTE15,
844 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
845 From
846 gl_tax_option_accounts accounts,
847 ap_tax_codes_all aptax,
848 gl_tax_options opt,
849 zx_rates_b rates
850 Where
851 accounts.ledger_id = opt.ledger_id and
852 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) = decode(l_multi_org_flag,'N',l_org_id,opt.org_id) and
853 accounts.ledger_id = aptax.set_of_books_id and
854 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) = decode(l_multi_org_flag,'N',l_org_id,aptax.org_id) and
855 accounts.tax_type_code = 'I' and
856 accounts.tax_code = aptax.name and
857 sysdate between aptax.start_date and nvl(aptax.inactive_date,sysdate) and
858 nvl(aptax.enabled_flag, 'Y') = 'Y' and
859 aptax.tax_id = nvl(rates.source_id,rates.tax_rate_id) and
860 nvl(rates.tax_class,'INPUT') = 'INPUT' and
861 rates.record_type_code = 'MIGRATED' and
862 accounts.ledger_id =nvl(P_Ledger_Id,accounts.ledger_id) and --added for sync
863 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) =nvl(P_Org_id,decode(l_multi_org_flag,'N',l_org_id,accounts.org_id)) and
864 --added for Sync
865 accounts.account_segment_value = nvl(P_Account_Segment_Value,accounts.account_segment_value) and --added for sync
866 NOT EXISTS ( Select 1
867 From zx_account_rates_tmp --Bug 6671444
868 Where ledger_id = opt.ledger_id
869 and content_owner_id = rates.content_owner_id
870 and account_segment_value = accounts.account_segment_value
871 and tax_class = 'INPUT' );
872
876 EXCEPTION
873 IF PG_DEBUG = 'Y' THEN
874 arp_util_tax.debug('zx_mig_gl_tax_options_ap(-)');
875 END IF;
877 WHEN OTHERS THEN
878 IF PG_DEBUG = 'Y' THEN
879 arp_util_tax.debug('EXCEPTION: zx_mig_gl_tax_options_ap ');
880 arp_util_tax.debug(sqlerrm);
881 arp_util_tax.debug('zx_mig_gl_tax_options_ap(-)');
882 END IF;
883 app_exception.raise_exception;
884 END zx_mig_gl_tax_options_ap;
885
886 /*===========================================================================+
887 | PROCEDURE |
888 | zx_mig_gl_tax_options_ar |
889 | |
890 | DESCRIPTION |
891 | This routine migrates GL TAX OPTIONS SETUP for AR(Output Tax). |
892 | |
893 | SCOPE - PRIVATE |
894 | |
895 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
896 | |
897 | CALLED FROM |
898 | zx_mig_gl_tax_options |
899 | NOTES |
900 | |
901 | MODIFICATION HISTORY |
902 | 09-Feb-05 Vamshi/Vinit Created. |
903 | |
904 +==========================================================================*/
905
906 PROCEDURE zx_mig_gl_tax_options_ar(P_Ledger_Id number,P_Org_id number,P_Account_Segment_Value varchar2) is
907 BEGIN
908 IF PG_DEBUG = 'Y' THEN
909 arp_util_tax.debug('zx_mig_gl_tax_options_ar(+)');
910 END IF;
911
912 Insert ALL
913 Into zx_account_rates_tmp
914 (
915 LEDGER_ID ,
916 CONTENT_OWNER_ID ,
917 ACCOUNT_SEGMENT_VALUE ,
918 TAX_PRECISION ,
919 CALCULATION_LEVEL_CODE ,
920 ALLOW_RATE_OVERRIDE_FLAG ,
921 TAX_MAU ,
922 TAX_CURRENCY_CODE ,
923 TAX_CLASS ,
924 TAX_REGIME_CODE ,
925 TAX ,
926 TAX_STATUS_CODE ,
927 TAX_RATE_CODE ,
928 ROUNDING_RULE_CODE ,
929 AMT_INCL_TAX_FLAG ,
930 RECORD_TYPE_CODE ,
931 CREATION_DATE ,
932 CREATED_BY ,
933 LAST_UPDATED_BY ,
934 LAST_UPDATE_DATE ,
935 LAST_UPDATE_LOGIN ,
936 ATTRIBUTE_CATEGORY ,
937 ATTRIBUTE1 ,
938 ATTRIBUTE2 ,
939 ATTRIBUTE3 ,
940 ATTRIBUTE4 ,
941 ATTRIBUTE5 ,
942 ATTRIBUTE6 ,
943 ATTRIBUTE7 ,
944 ATTRIBUTE8 ,
945 ATTRIBUTE9 ,
946 ATTRIBUTE10 ,
947 ATTRIBUTE11 ,
948 ATTRIBUTE12 ,
949 ATTRIBUTE13 ,
950 ATTRIBUTE14 ,
951 ATTRIBUTE15 ,
952 ALLOW_ROUNDING_OVERRIDE_FLAG)
953 Values
954 (
955 LEDGER_ID ,
956 CONTENT_OWNER_ID ,
957 ACCOUNT_SEGMENT_VALUE ,
958 TAX_PRECISION ,
959 CALCULATION_LEVEL_CODE ,
960 ALLOW_RATE_OVERRIDE_FLAG ,
961 TAX_MAU ,
962 TAX_CURRENCY_CODE ,
963 TAX_CLASS ,
964 TAX_REGIME_CODE ,
965 TAX ,
966 TAX_STATUS_CODE ,
967 TAX_RATE_CODE ,
968 ROUNDING_RULE_CODE ,
969 AMT_INCL_TAX_FLAG ,
970 RECORD_TYPE_CODE ,
971 CREATION_DATE ,
972 CREATED_BY ,
973 LAST_UPDATED_BY ,
974 LAST_UPDATE_DATE ,
975 LAST_UPDATE_LOGIN ,
976 ATTRIBUTE_CATEGORY ,
977 ATTRIBUTE1 ,
978 ATTRIBUTE2 ,
979 ATTRIBUTE3 ,
980 ATTRIBUTE4 ,
981 ATTRIBUTE5 ,
982 ATTRIBUTE6 ,
983 ATTRIBUTE7 ,
984 ATTRIBUTE8 ,
988 ATTRIBUTE12 ,
985 ATTRIBUTE9 ,
986 ATTRIBUTE10 ,
987 ATTRIBUTE11 ,
989 ATTRIBUTE13 ,
990 ATTRIBUTE14 ,
991 ATTRIBUTE15 ,
992 ALLOW_ROUNDING_OVERRIDE_FLAG)
993 Select
994 opt.ledger_id LEDGER_ID,
995 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID,
996 NULL ACCOUNT_SEGMENT_VALUE,
997 opt.TAX_PRECISION TAX_PRECISION,
998 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
999 NULL ALLOW_RATE_OVERRIDE_FLAG,
1000 opt.TAX_MAU TAX_MAU,
1001 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
1002 'OUTPUT' TAX_CLASS,
1003 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
1004 rates.TAX TAX,
1005 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
1006 rates.TAX_RATE_CODE TAX_RATE_CODE,
1007 opt.OUTPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
1008 opt.OUTPUT_AMT_INCL_TAX_FLAG AMT_INCL_TAX_FLAG,
1009 'MIGRATED' RECORD_TYPE_CODE,
1010 SYSDATE CREATION_DATE,
1011 fnd_global.user_id CREATED_BY,
1012 fnd_global.user_id LAST_UPDATED_BY,
1013 SYSDATE LAST_UPDATE_DATE,
1014 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
1015 NULL ATTRIBUTE_CATEGORY,
1016 NULL ATTRIBUTE1,
1017 NULL ATTRIBUTE2,
1018 NULL ATTRIBUTE3,
1019 NULL ATTRIBUTE4,
1020 NULL ATTRIBUTE5,
1021 NULL ATTRIBUTE6,
1022 NULL ATTRIBUTE7,
1023 NULL ATTRIBUTE8,
1024 NULL ATTRIBUTE9,
1025 NULL ATTRIBUTE10,
1026 NULL ATTRIBUTE11,
1027 NULL ATTRIBUTE12,
1028 NULL ATTRIBUTE13,
1029 NULL ATTRIBUTE14,
1030 NULL ATTRIBUTE15,
1031 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
1032 From
1033 GL_TAX_OPTIONS opt,
1034 AR_VAT_TAX_ALL artax,
1035 zx_RATES_B rates
1036 Where
1037 opt.ledger_id = artax.set_of_books_id and
1038 decode(l_multi_org_flag,'N',l_org_id,opt.org_id) = decode(l_multi_org_flag,'N',l_org_id,artax.org_id) and
1039 opt.output_tax_code = artax.tax_code and
1040 sysdate between artax.start_date and nvl(artax.end_date,sysdate) and
1041 artax.vat_tax_id = nvl(rates.source_id,rates.tax_rate_id) and
1042 nvl(artax.enabled_flag, 'Y') = 'Y' and
1043 nvl(artax.tax_class, 'O') = 'O' and
1044 rates.record_type_code = 'MIGRATED' and
1045 nvl(rates.tax_class,'OUTPUT') = 'OUTPUT' and
1046 opt.ledger_id =nvl(P_Ledger_Id,opt.ledger_id) and --added for sync
1047 decode(l_multi_org_flag,'N',l_org_id,opt.org_id) =nvl(P_Org_id,decode(l_multi_org_flag,'N',l_org_id,opt.org_id)) and
1048 --added for Sync
1049 NOT EXISTS ( Select 1
1050 From zx_account_rates_tmp --Bug 6671444
1051 Where ledger_id = opt.ledger_id
1052 and content_owner_id = rates.content_owner_id
1053 and account_segment_value is null
1054 and tax_class = 'OUTPUT' );
1055
1056 Insert ALL
1057 Into zx_account_rates_tmp
1058 (
1059 LEDGER_ID ,
1060 CONTENT_OWNER_ID ,
1061 ACCOUNT_SEGMENT_VALUE ,
1062 TAX_PRECISION ,
1063 CALCULATION_LEVEL_CODE ,
1064 ALLOW_RATE_OVERRIDE_FLAG ,
1065 TAX_MAU ,
1066 TAX_CURRENCY_CODE ,
1067 TAX_CLASS ,
1068 TAX_REGIME_CODE ,
1069 TAX ,
1070 TAX_STATUS_CODE ,
1071 TAX_RATE_CODE ,
1072 ROUNDING_RULE_CODE ,
1073 AMT_INCL_TAX_FLAG ,
1074 RECORD_TYPE_CODE ,
1075 CREATION_DATE ,
1076 CREATED_BY ,
1077 LAST_UPDATED_BY ,
1078 LAST_UPDATE_DATE ,
1079 LAST_UPDATE_LOGIN ,
1080 ATTRIBUTE_CATEGORY ,
1081 ATTRIBUTE1 ,
1082 ATTRIBUTE2 ,
1083 ATTRIBUTE3 ,
1084 ATTRIBUTE4 ,
1085 ATTRIBUTE5 ,
1086 ATTRIBUTE6 ,
1087 ATTRIBUTE7 ,
1088 ATTRIBUTE8 ,
1089 ATTRIBUTE9 ,
1090 ATTRIBUTE10 ,
1091 ATTRIBUTE11 ,
1092 ATTRIBUTE12 ,
1093 ATTRIBUTE13 ,
1094 ATTRIBUTE14 ,
1095 ATTRIBUTE15 ,
1096 ALLOW_ROUNDING_OVERRIDE_FLAG)
1097 Values
1098 (
1099 LEDGER_ID ,
1100 CONTENT_OWNER_ID ,
1101 ACCOUNT_SEGMENT_VALUE ,
1102 TAX_PRECISION ,
1106 TAX_CURRENCY_CODE ,
1103 CALCULATION_LEVEL_CODE ,
1104 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
1105 TAX_MAU ,
1107 TAX_CLASS ,
1108 TAX_REGIME_CODE ,
1109 TAX ,
1110 TAX_STATUS_CODE ,
1111 TAX_RATE_CODE ,
1112 ROUNDING_RULE_CODE ,
1113 AMT_INCL_TAX_FLAG ,
1114 RECORD_TYPE_CODE ,
1115 CREATION_DATE ,
1116 CREATED_BY ,
1117 LAST_UPDATED_BY ,
1118 LAST_UPDATE_DATE ,
1119 LAST_UPDATE_LOGIN ,
1120 ATTRIBUTE_CATEGORY ,
1121 ATTRIBUTE1 ,
1122 ATTRIBUTE2 ,
1123 ATTRIBUTE3 ,
1124 ATTRIBUTE4 ,
1125 ATTRIBUTE5 ,
1126 ATTRIBUTE6 ,
1127 ATTRIBUTE7 ,
1128 ATTRIBUTE8 ,
1129 ATTRIBUTE9 ,
1130 ATTRIBUTE10 ,
1131 ATTRIBUTE11 ,
1132 ATTRIBUTE12 ,
1133 ATTRIBUTE13 ,
1134 ATTRIBUTE14 ,
1135 ATTRIBUTE15 ,
1136 ALLOW_ROUNDING_OVERRIDE_FLAG)
1137 INTO ZX_ACCT_TX_CLS_DEFS_ALL
1138 (
1139 LEDGER_ID ,
1140 ORG_ID ,
1141 ACCOUNT_SEGMENT_VALUE ,
1142 TAX_CLASS ,
1143 TAX_CLASSIFICATION_CODE ,
1144 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
1145 RECORD_TYPE_CODE ,
1146 CREATION_DATE ,
1147 CREATED_BY ,
1148 LAST_UPDATED_BY ,
1149 LAST_UPDATE_DATE ,
1150 LAST_UPDATE_LOGIN ,
1151 ATTRIBUTE_CATEGORY ,
1152 ATTRIBUTE1 ,
1153 ATTRIBUTE2 ,
1154 ATTRIBUTE3 ,
1155 ATTRIBUTE4 ,
1156 ATTRIBUTE5 ,
1157 ATTRIBUTE6 ,
1158 ATTRIBUTE7 ,
1159 ATTRIBUTE8 ,
1160 ATTRIBUTE9 ,
1161 ATTRIBUTE10 ,
1162 ATTRIBUTE11 ,
1163 ATTRIBUTE12 ,
1164 ATTRIBUTE13 ,
1165 ATTRIBUTE14 ,
1166 ATTRIBUTE15 )
1167 Values
1168 (
1169 LEDGER_ID ,
1170 ORG_ID ,
1171 ACCOUNT_SEGMENT_VALUE ,
1172 TAX_CLASS ,
1173 TAX_CLASSIFICATION_CODE ,
1174 ALLOW_TAX_CODE_OVERRIDE_FLAG ,
1175 RECORD_TYPE_CODE ,
1176 CREATION_DATE ,
1177 CREATED_BY ,
1178 LAST_UPDATED_BY ,
1179 LAST_UPDATE_DATE ,
1180 LAST_UPDATE_LOGIN ,
1181 ATTRIBUTE_CATEGORY ,
1182 ATTRIBUTE1 ,
1183 ATTRIBUTE2 ,
1184 ATTRIBUTE3 ,
1185 ATTRIBUTE4 ,
1186 ATTRIBUTE5 ,
1187 ATTRIBUTE6 ,
1188 ATTRIBUTE7 ,
1189 ATTRIBUTE8 ,
1190 ATTRIBUTE9 ,
1191 ATTRIBUTE10 ,
1192 ATTRIBUTE11 ,
1193 ATTRIBUTE12 ,
1194 ATTRIBUTE13 ,
1195 ATTRIBUTE14 ,
1196 ATTRIBUTE15 )
1197 Select
1198 accounts.ledger_id LEDGER_ID,
1199 rates.CONTENT_OWNER_ID CONTENT_OWNER_ID,
1200 decode(l_multi_org_flag,'N',l_org_id,accounts.ORG_ID ) ORG_ID,
1201 ACCOUNT_SEGMENT_VALUE ACCOUNT_SEGMENT_VALUE,
1202 'OUTPUT' TAX_CLASS,
1203 opt.TAX_PRECISION TAX_PRECISION,
1204 opt.CALCULATION_LEVEL_CODE CALCULATION_LEVEL_CODE,
1205 opt.TAX_MAU TAX_MAU,
1206 opt.TAX_CURRENCY_CODE TAX_CURRENCY_CODE,
1207 accounts.TAX_CODE TAX_CLASSIFICATION_CODE,
1208 opt.OUTPUT_ROUNDING_RULE_CODE ROUNDING_RULE_CODE,
1209 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
1210 rates.TAX TAX,
1211 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
1212 rates.TAX_RATE_CODE TAX_RATE_CODE,
1213 accounts.ALLOW_TAX_CODE_OVERRIDE_FLAG ALLOW_TAX_CODE_OVERRIDE_FLAG ,
1214 accounts.AMOUNT_INCLUDES_TAX_FLAG AMT_INCL_TAX_FLAG,
1215 'MIGRATED' RECORD_TYPE_CODE,
1216 SYSDATE CREATION_DATE,
1217 fnd_global.user_id CREATED_BY,
1218 fnd_global.user_id LAST_UPDATED_BY,
1219 SYSDATE LAST_UPDATE_DATE,
1220 fnd_global.conc_login_id LAST_UPDATE_LOGIN,
1221 NULL ATTRIBUTE_CATEGORY,
1222 NULL ATTRIBUTE1,
1223 NULL ATTRIBUTE2,
1224 NULL ATTRIBUTE3,
1225 NULL ATTRIBUTE4,
1226 NULL ATTRIBUTE5,
1227 NULL ATTRIBUTE6,
1228 NULL ATTRIBUTE7,
1229 NULL ATTRIBUTE8,
1230 NULL ATTRIBUTE9,
1231 NULL ATTRIBUTE10,
1232 NULL ATTRIBUTE11,
1236 NULL ATTRIBUTE15,
1233 NULL ATTRIBUTE12,
1234 NULL ATTRIBUTE13,
1235 NULL ATTRIBUTE14,
1237 opt.ALLOW_ROUNDING_OVERRIDE_FLAG ALLOW_ROUNDING_OVERRIDE_FLAG
1238 From
1239 gl_tax_option_accounts accounts,
1240 ar_vat_tax_all artax,
1241 gl_tax_options opt,
1242 zx_rates_b rates
1243 Where
1244 accounts.ledger_id = opt.ledger_id and
1245 decode(l_multi_org_flag,'N',l_org_id,opt.org_id) = decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) and
1246 accounts.ledger_id = artax.set_of_books_id and
1247 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) = decode(l_multi_org_flag,'N',l_org_id,artax.org_id) and
1248 accounts.tax_type_code = 'O' and
1249 accounts.tax_code = artax.tax_code and
1250 sysdate between artax.start_date and nvl(artax.end_date,sysdate) and
1251 nvl(artax.enabled_flag, 'Y') = 'Y' and
1252 nvl(artax.tax_class, 'O') = 'O' and
1253 artax.vat_tax_id = nvl(rates.source_id,rates.tax_rate_id) and
1254 nvl(rates.tax_class,'OUTPUT') = 'OUTPUT' and
1255 rates.record_type_code = 'MIGRATED' AND
1256 accounts.ledger_id =nvl(P_Ledger_Id,accounts.ledger_id) and --added for sync
1257 decode(l_multi_org_flag,'N',l_org_id,accounts.org_id) =nvl(P_Org_id,decode(l_multi_org_flag,'N',l_org_id,accounts.org_id)) and
1258 --added for Sync
1259 accounts.account_segment_value = nvl(P_Account_Segment_Value,accounts.account_segment_value) and --added for sync
1260 NOT EXISTS ( Select 1
1261 From zx_account_rates_tmp --Bug 6671444
1262 Where ledger_id = opt.ledger_id
1263 and content_owner_id = rates.content_owner_id
1264 and account_segment_value = accounts.account_segment_value
1265 and tax_class = 'OUTPUT' );
1266
1267 IF PG_DEBUG = 'Y' THEN
1268 arp_util_tax.debug('zx_mig_gl_tax_options_ar(-)');
1269 END IF;
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 IF PG_DEBUG = 'Y' THEN
1273 arp_util_tax.debug('EXCEPTION: zx_mig_gl_tax_options_ar ');
1274 arp_util_tax.debug(sqlerrm);
1275 arp_util_tax.debug('zx_mig_gl_tax_options_ar(-)');
1276 END IF;
1277 app_exception.raise_exception;
1278 END zx_mig_gl_tax_options_ar;
1279
1280 BEGIN
1281 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
1282 FND_PRODUCT_GROUPS;
1283
1284 IF L_MULTI_ORG_FLAG = 'N' THEN
1285
1286 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
1287
1288 IF L_ORG_ID IS NULL THEN
1289 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
1290 END IF;
1291 ELSE
1292 L_ORG_ID := NULL;
1293 END IF;
1294
1295
1296
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 arp_util_tax.debug('Exception in constructor of GL Tax Options Code '||sqlerrm);
1300 END zx_mig_gl_tax_options_pkg;