1 PACKAGE BODY ARP_STAX_MINUS99 AS
2 /* $Header: ARPLXSTX.txt 115.6 2004/04/30 11:34:24 rpalani noship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PRIVATE EXCEPTIONS |
7 | |
8 +-------------------------------------------------------------------------*/
9
10 AR_STAX_FK_ERROR EXCEPTION;
11 PRAGMA EXCEPTION_INIT( AR_STAX_FK_ERROR, -2292 );
12
13 /*-------------------------------------------------------------------------+
14 | |
15 | PRIVATE VARIABLES |
16 | |
17 +-------------------------------------------------------------------------*/
18 TYPE tax_exempt_info_rec_type IS RECORD
19 (
20 percent_exempt NUMBER,
21 tax_exemption_id NUMBER,
22 tax_exempt_reason_code VARCHAR2(30),
23 tax_exempt_number VARCHAR2(80),
24 bill_to_customer_id NUMBER,
25 ship_to_site_use_id NUMBER,
26 tax_code VARCHAR2(50),
27 status_code VARCHAR2(30),
28 start_date DATE,
29 end_date DATE
30 );
31
32 tax_exempt_info_rec tax_exempt_info_rec_type;
33
34 TYPE tax_exempt_info_rec_tbl is TABLE of tax_exempt_info_rec_type index by
35 binary_integer;
36
37 tax_exempt_info_tbl tax_exempt_info_rec_tbl;
38
39 pg_max_index BINARY_INTEGER :=0;
40
41 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
42 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
43
44 /*-------------------------------------------------------------------------+
45 | PUBLIC FUNCTION |
46 | ins_sales_tax |
47 | |
48 | DESCRIPTION |
49 | This function generates a new record in the table: AR_SALES_TAX |
50 | and returns the SALES_TAX_ID of this new record |
51 | |
52 | REQUIRES |
53 | location_id CCID of this Location |
54 | location_structure_id Multiflex structure ID in use |
55 | total_tax_rate sum of all components of the tax rate |
56 | location1_rate Tax rate for segment 1 of the location |
57 | location2_rate Tax rate for segment 2 of the location |
58 | location3_rate Tax rate for segment 3 of the location |
59 | location4_rate Tax rate for segment 4 of the location |
60 | location5_rate Tax rate for segment 5 of the location |
61 | location6_rate Tax rate for segment 6 of the location |
62 | location7_rate Tax rate for segment 7 of the location |
63 | location8_rate Tax rate for segment 8 of the location |
64 | location9_rate Tax rate for segment 9 of the location |
65 | location10_rate Tax rate for segment 10 of the location |
66 | from_postal_code The same location may have multiple rates |
67 | to_postal_code assigned to it, depending upon postal code |
68 | start_date and transaction date. |
69 | end_date |
70 | |
71 | RETURNS |
72 | SALES_TAX_ID of the new record |
73 | |
74 | EXCEPTIONS RAISED |
75 | |
76 | NOTES |
77 | |
78 | EXAMPLE |
79 | |
80 +-------------------------------------------------------------------------*/
81
82
83 FUNCTION ins_sales_tax(
84 location_id in number,
85 location_structure_id in number,
86 total_tax_rate in number,
87 location1_rate in number,
88 location2_rate in number,
89 location3_rate in number,
90 location4_rate in number,
91 location5_rate in number,
92 location6_rate in number,
93 location7_rate in number,
94 location8_rate in number,
95 location9_rate in number,
96 location10_rate in number,
97 from_postal_code in varchar2,
98 to_postal_code in varchar2,
99 start_date in date,
100 end_date in date) return number IS
101
102 CURSOR ar_sales_tax_s_c is
103 select ar_sales_tax_s.nextval + arp_standard.sequence_offset
104 from dual;
105
106 sales_tax_id NUMBER;
107
108
109 BEGIN
110 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
111 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
112 IF PG_DEBUG = 'Y' THEN
113 arp_util_tax.debug( '>> INS_SALES_TAX' );
114 END IF;
115
116 OPEN ar_sales_tax_s_c;
117 FETCH ar_sales_tax_s_c into sales_tax_id;
118 CLOSE ar_sales_tax_s_c;
119
120 insert into ar_sales_tax(
121 SALES_TAX_ID,
122 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
123 CREATED_BY, CREATION_DATE,
124 LOCATION_ID,
125 rate_context,
126 tax_rate,
127 LOCATION1_RATE,
128 LOCATION2_RATE,
129 LOCATION3_RATE,
130 from_postal_code,
131 to_postal_code,
132 start_date,
133 end_date,
134 enabled_flag)
135 VALUES
136 (
137 sales_tax_id,
138 sysdate,
139 arp_standard.profile.user_id,
140 null,
141 arp_standard.profile.user_id,
142 sysdate,
143 location_id,
144 location_structure_id,
145 total_tax_rate,
146 LOCATION1_RATE,
147 LOCATION2_RATE,
148 LOCATION3_RATE,
149 from_postal_code,
150 to_postal_code,
151 start_date,
152 end_date,
153 'Y');
154 IF PG_DEBUG = 'Y' THEN
155 arp_util_tax.debug( '<< INS_SALES_TAX:' || sales_tax_id );
156 END IF;
157 return(sales_tax_id);
158 END ins_sales_tax;
159
160
161 /*-------------------------------------------------------------------------+
162 | PUBLIC PROCEDURE |
163 | implement_transfer_rates |
164 | |
165 | CALLED BY TRIGGER AR_LOCATION_RATES_ASIU |
166 | |
167 | REQUIRES |
168 | |
169 | PL/SQL table: LOCATION_SEGMENT_ID to be populated with the |
170 | location_segment_id of each row that has changed in the table |
171 | AR_LOCATION_RATES. |
172 | |
173 | The PUBLIC variable: loc_rate is a count of the number of rows in |
174 | the PL/SQL table: location_segment_id that we can expect and is |
175 | maintained by the trigger: AR_LOCATION_RATES_BRIU |
176 | |
177 | DESCRIPTION |
178 | |
179 | Will take each distinct location_segment_id used during updates or |
180 | inserts to the table: AR_LOCATION_RATES and propogate these sales |
181 | tax rate changes into the table: AR_SALES_TAX |
182 | |
183 | This procedures fires the cursor: sel_cc to find each Location Code |
184 | Combination that uses a particular location_segment_id and then |
185 | updates each of the rows in ar_sales_tax for that specific location. |
186 | |
187 | To optimise performance of the code a note of every code combination |
188 | is made so that the same set of records in ar_sales_tax is only ever |
189 | visited once. |
190 | |
191 | EXAMPLE |
192 | |
193 | If the following Code Combinations were active on a system: |
194 | CA.SAN MATEO.BELMONT CCID: 1000, LOCATIONS: 4.6.8 |
195 | CA.SAN MATEO.FOSTER CITY CCID: 1001, LOCATIONS: 4.6.10 |
196 | CA.FREEMONT.FREEMONT CCID: 1002, LOCATIONS: 4.12.14 |
197 | FL.MIAMI.MIAMI CCID: 1003, LOCATIONS: 16.18.20 |
198 | |
199 | And the user updates AR_LOCATION_RATES for ( CA, and SAN MATEO ) |
200 | There would be two rows set up by the TRIGGER: AR_LOCATION_RATES_BRIU |
201 | in the PL/SQL table: location_segment_id, these rows would be: |
202 | location_segment_id(0) = 4 |
203 | location_segment_id(1) = 6 |
204 | This procedure would fire the cursor: sel_cc which for location 4 |
205 | would return: 3 rows, CCIDS: 1000, 10001, and 1002. |
206 | Sales Tax Rate records would then be regenerated for all 3 different |
207 | location code combinations. |
208 | Cursor sel_cc would then be re-fired for location 6 and return the |
209 | following two rows: 1000, 1001. Since both of these rows have already |
210 | been updated no further work is required and the procedure completes. |
211 | |
212 | MODIFICATION HISTORY |
213 | 22-Jan-93 Nigel Smith Created. |
214 | |
215 +-------------------------------------------------------------------------*/
216
217 PROCEDURE Implement_Transfer_Rates is
218
219
220 cursor sel_cc( location_segment_id in number ) IS
221 select
222 struct.location_id location_ccid,
223 struct.location_id_segment_1,
224 struct.location_id_segment_2,
225 struct.location_id_segment_3,
226 struct.location_id_segment_4,
227 struct.location_id_segment_5,
228 struct.location_id_segment_6,
229 struct.location_id_segment_7,
230 struct.location_id_segment_8,
231 struct.location_id_segment_9,
232 struct.location_id_segment_10
233 from ar_location_combinations struct
234 where LOCATION_ID_SEGMENT_1 = location_segment_id
235 or LOCATION_ID_SEGMENT_2 = location_segment_id
236 or LOCATION_ID_SEGMENT_3 = location_segment_id; -- PL/SQL Flexfield Pre-Processor
237 -- Refer to token file: token.pls
238
239 i number;
240 j number;
241 k number;
242
243 location_ccid TAB_ID_TYPE;
244 ccids NUMBER := 0;
245 do_transfer_flag BOOLEAN;
246 BEGIN
247
248 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
249 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
250
251 IF PG_DEBUG = 'Y' THEN
252 arp_util_tax.debug( '>> IMPLEMENT_TRANSFER_RATES' );
253 END IF;
254
255 if not transfer_rates_initialised
256 then
257 arp_standard.fnd_message( 'AR_TRIG_NOT_INITIALISED', 'TRIGGER', 'LOCATION_RATES' );
258
259 IF PG_DEBUG = 'Y' THEN
260 arp_util_tax.debug( '>> AR TRIGGER LOCATION RATES NOT INITIALIZED' );
261 END IF;
262
263 end if;
264 transfer_rates_initialised := FALSE;
265 transfer_rates_manual := FALSE;
266
267 /*------------------------------------------------------------------------+
268 | For each distinct location_segment_id that had a rate insert or update |
269 | assocaited with it, find each distinct location_ccid and update |
270 | the table: AR_SALES_TAX for this location_ccid, updating rate |
271 | assignments |
272 +------------------------------------------------------------------------*/
273
274 FOR i in 1 .. loc_rate
275 LOOP
276 IF location_segment_id( i ) is not null
277 THEN
278
279 /*------------------------------------------------------------------+
280 | We may have updated or inserted rates for this location multiple |
281 | times, in which case find any occurances of this location in the |
282 | array and reset this element to null so that we do not repeat |
283 | any work. |
284 +------------------------------------------------------------------*/
285
286 FOR j in i+1 .. loc_rate
287 LOOP
288 IF location_segment_id( i ) = location_segment_id( j )
289 THEN
290 location_segment_id( j ) := null;
291 END IF;
292 END LOOP;
293
294 /*------------------------------------------------------------------+
295 | Update the Sales Tax table for this location, deleting any |
296 | invalid rates, adding any new ones. |
297 +------------------------------------------------------------------*/
298
299 FOR rates in sel_cc( location_segment_id(i) )
300 LOOP
301
302 /*---------------------------------------------------------------+
303 | Confirm that this location_ccid has not been updated before |
304 +---------------------------------------------------------------*/
305
306 do_transfer_flag := TRUE;
307
308 FOR k in 1 .. ccids
309 LOOP
310 if ( rates.location_ccid = location_ccid(k) )
311 THEN
312 do_transfer_flag := FALSE; /* Already done */
313 END IF;
314 EXIT WHEN do_transfer_flag = FALSE;
315 END LOOP;
316
317 IF do_transfer_flag
318 THEN
319 Populate_Sales_Tax( 'Update',
320 rates.location_ccid,
321 rates.location_id_segment_1,
322 rates.location_id_segment_2,
323 rates.location_id_segment_3,
324 rates.location_id_segment_4,
325 rates.location_id_segment_5,
326 rates.location_id_segment_6,
327 rates.location_id_segment_7,
328 rates.location_id_segment_8,
329 rates.location_id_segment_9,
330 rates.location_id_segment_10 );
331 ccids := ccids + 1;
332 location_ccid( ccids ) := rates.location_ccid;
333 END IF;
334 END LOOP;
335 END IF;
336 location_segment_id(i) := NULL; /* Once this is used, clear it down */
337 END LOOP;
338
339 IF PG_DEBUG = 'Y' THEN
340 arp_util_tax.debug( '<< IMPLEMENT_TRANSFER_RATES: ' || ccids );
341 END IF;
342
343 END Implement_Transfer_Rates;
344
345
346 /*-------------------------------------------------------------------------+
347 | PUBLIC PROCEDURE |
348 | enable_triggers / disable_triggers |
349 | |
350 | DESCRIPTION |
351 | |
352 | Control the execution of database triggers associated with the sales |
353 | tax functions, enabling or disabling there actions. |
354 | |
355 | This is used to enhance performance of certain batch operations |
356 | such as the Sales Tax Interface programs, when the row by row |
357 | nature of database triggers would degrade performance of the system |
358 | |
359 | MODIFIES |
360 | |
361 | ARP_STAX.triggers_enabled |
362 | |
363 | EXCEPTIONS RAISED |
364 | |
365 | NOTES |
366 | |
367 | EXAMPLE |
368 | |
369 +-------------------------------------------------------------------------*/
370
371 procedure enable_triggers is
372 begin
373 triggers_enabled := TRUE;
374 end;
375
376
377
378 procedure disable_triggers is
379 begin
380 triggers_enabled := FALSE;
381 end;
382
383
384 /*-------------------------------------------------------------------------+
385 | PUBLIC FUNCTION |
386 | Site_Use_Sales_Tax |
387 | |
388 | CALLED BY TRIGGER RA SITE_USES_BRIU |
389 | |
390 | DESCRIPTION |
391 | |
392 | Find the location CCID for the address used by this site use, and all |
393 | of the segment id's in use by this locaiton code combination. |
394 | |
395 | Re-Populates AR_SALES_TAX with rate information. |
396 | |
397 | MODIFIES |
398 | |
399 | EXCEPTIONS RAISED |
400 | |
401 | NOTES |
402 | |
403 | EXAMPLE |
404 | |
405 +-------------------------------------------------------------------------*/
406
407
408 PROCEDURE Site_Use_Sales_Tax( address_id in number ) IS
409
410 cursor sel_cc( address_id in number) is
411 select
412 struct.location_id location_ccid,
413 struct.location_id_segment_1,
414 struct.location_id_segment_2,
415 struct.location_id_segment_3,
416 struct.location_id_segment_4,
417 struct.location_id_segment_5,
418 struct.location_id_segment_6,
419 struct.location_id_segment_7,
420 struct.location_id_segment_8,
421 struct.location_id_segment_9,
422 struct.location_id_segment_10
423 from ar_location_combinations struct,
424 hz_party_sites party_site,
425 hz_loc_assignments loc_assign,
426 hz_locations loc,
427 hz_cust_acct_sites acct_site
428 where struct.location_id = loc_assign.loc_id
429 and acct_site.party_site_id = party_site.party_site_id
430 and loc.location_id = party_site.location_id
431 and loc.location_id = loc_assign.location_id
432 and NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99);
433
434 cc sel_cc%ROWTYPE;
435 BEGIN
436 -- PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
437 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
438 IF PG_DEBUG = 'Y' THEN
439 arp_util_tax.debug( '>> SITE_USE_SALES_TAX( ' || address_id || ' )' );
440 END IF;
441
442 OPEN sel_cc( address_id );
443 FETCH sel_cc into cc;
444 CLOSE sel_cc;
445
446 Populate_Sales_Tax( 'Update',
447 cc.location_ccid ,
448 cc.location_id_segment_1 ,
449 cc.location_id_segment_2 ,
450 cc.location_id_segment_3 ,
451 cc.location_id_segment_4 ,
452 cc.location_id_segment_5 ,
453 cc.location_id_segment_6 ,
454 cc.location_id_segment_7 ,
455 cc.location_id_segment_8 ,
456 cc.location_id_segment_9 ,
457 cc.location_id_segment_10 ) ;
458
459 IF PG_DEBUG = 'Y' THEN
460 arp_util_tax.debug( '<< SITE_USE_SALES_TAX' );
461 END IF;
462
463 END;
464
465
466
467 /*-------------------------------------------------------------------------+
468 | PUBLIC FUNCTION |
469 | Initialise_Transfer_Rates |
470 | |
471 | CALLED BY TRIGGER AR_LOCATION_RATES_BSIU |
472 | |
473 | DESCRIPTION |
474 | |
475 | Initialise the public variable: location_rates_transfer_id with the |
476 | value from the sequence: AR_LOCATION_RATES_TRASNFER_S |
477 | |
478 | For each set of inserted or updateed rows in the table AR_LOCATION_RATES|
479 | a distinct value is placed in the column: AR_TRANSFER_CONTROL_ID |
480 | When all records have been inserted or updated, the after statement |
481 | trigger: AR_LOCATION_RATES_ASIU fires, and refetchs each of these new |
482 | records across into the table: AR_SALES_TAX |
483 | |
484 | This works around the kernel limitation of MUTATING tables. |
485 | |
486 | MODIFIES |
487 | Public variable: LOCATION_RATES_TRANSFER_ID |
488 | |
489 | EXCEPTIONS RAISED |
490 | |
491 | NOTES |
492 | |
493 | EXAMPLE |
494 | |
495 +-------------------------------------------------------------------------*/
496
497
498 PROCEDURE Initialise_Transfer_Rates is
499 BEGIN
500 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
501 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
502 if transfer_rates_initialised
503 then
504 arp_standard.fnd_message( 'AR_TRIG_ALREADY_INITIALISED', 'TRIGGER', 'LOCATION_RATES' );
505
506 IF PG_DEBUG = 'Y' THEN
507 arp_util_tax.debug('AR_TRIGGER LOCATION RATES ALREADY EXIST');
508 END IF;
509
510 end if;
511
512 loc_rate := 0;
513 ARP_STAX_MINUS99.location_segment_id := ARP_STAX_MINUS99.empty_id_table;
514 transfer_rates_initialised := TRUE;
515
516 END Initialise_Transfer_Rates;
517
518
519
520
521
522
523
524 /*-------------------------------------------------------------------------+
525 | PUBLIC FUNCTION |
526 | Populate_Sales_Tax |
527 | |
528 | CALLED BY TRIGGER RA_LOCATION_COMBINATIONS_BRIU |
529 | |
530 | DESCRIPTION |
531 | |
532 | Generate records in the table: AR_SALES_TAX for the new location_CCID |
533 | These records will be derived from multiple records in the table |
534 | AR_LOCATION_RATES. |
535 | |
536 | For each enabled segment in the "Tax Location Flexfield", find |
537 | every sales tax rate, zip and date range. Note if two rates |
538 | have different, mutually exclusive date or zip code ranges |
539 | then reject this record. |
540 | |
541 | Example:- |
542 | |
543 | Segment From To Start End Tax |
544 | Value Zip Zip Date Date Rate |
545 | ------------ ----- ------ --------- ---------- ----- |
546 | CA A C 01-JAN-90 31-DEC-90 5 |
547 | CA D Z 01-JAN-90 31-DEC-90 10 |
548 | CA A Z 01-JAN-91 31-JAN-91 12 |
549 | |
550 | San Mateo A Z 01-JAN-91 31-JAN-91 2 |
551 | San Mateo A Z 07-JUL-90 07-JUL-90 0 |
552 | |
553 | Foster City A Z 01-JAN-91 31-JAN-91 1 |
554 | Belmont A Z 01-JAN-90 31-JAN-91 3 |
555 | |
556 | If the following flexfield combinations were created, the |
557 | sales tax rate assignments generated would be:- |
558 | |
559 | Flexfield Combination Rate Start End From To |
560 | Date Date Zip Zip |
561 | ------------------------ ----- --------- --------- ----- ------ |
562 | CA.San Mateo.Foster City 12+2+1 01-jan-91 31-jan-91 A Z |
563 | CA.San Mateo.Belmont 12+2+3 01-jan-91 31-jan-91 A Z |
564 | CA.San Matro.Belmont 5+0+3 07-jul-90 07-jul-90 A C |
565 | CA.San Matro.Belmont 10+0+3 07-jul-90 07-jul-90 D Z |
566 | |
567 | Note. |
568 | |
569 | Because CA has two different tax rates in 1990, the first |
570 | for zip codes ( A-C ), the second for zip codes ( D-Z ) |
571 | it must have two separate entries in the sales tax rates table |
572 | whenever assignments are created for 1990. |
573 | |
574 | Rate assignments for 1990 are not available for Foster City |
575 | because the city component has no tax rate for this date range |
576 | even though the State and County both have tax rates available. |
577 | |
578 | Even though CA and Belmont have tax rates available across many |
579 | days in 1990, the County of San Mateo only has a valid rate |
580 | for the 7th July, 1990 so the combined rate assignment is only |
581 | valid for 07-Jul-1990. |
582 | |
583 | The Same set of restrictions also applies to Zip code ranges. |
584 | |
585 | Location_ID column to the Code Combinations ID applicable to this |
586 | address. |
587 | |
588 | In order to do this, it may be necessary to insert new items into |
589 | the tables: AR_LOCATION_VALUES and AR_LOCATION_COMBINATIONS |
590 | |
591 | REQUIRES |
592 | Location_CCID Location ID for ths entry in Sales Tax |
593 | location_id_segments 1 .. 10, Location_segment_id for each segment |
594 | in the location flexfield structure. |
595 | |
596 | STATEMENT_TYPE |
597 | |
598 | INSERT New Location CCID Created, there will be *NO* |
599 | pre-existing sales tax data for this location |
600 | DELETE A Locatoin Code Combination has been deleted |
601 | purge ununsed sales tax rates. |
602 | UPDATE Existing Data May Exist, some of whic may now |
603 | be invalid. Purge Invalid data, creating new |
604 | valid data in its place. |
605 | EXCEPTIONS RAISED |
606 | |
607 | NOTES |
608 | |
609 | EXAMPLE |
610 | |
611 +-------------------------------------------------------------------------*/
612
613 PROCEDURE Populate_Sales_Tax( statement_type in varchar2,
614 location_ccid in number,
615 p_location_id_segment_1 in number,
616 p_location_id_segment_2 in number,
617 p_location_id_segment_3 in number,
618 p_location_id_segment_4 in number,
619 p_location_id_segment_5 in number,
620 p_location_id_segment_6 in number,
621 p_location_id_segment_7 in number,
622 p_location_id_segment_8 in number,
623 p_location_id_segment_9 in number,
624 p_location_id_segment_10 in number ) is
625 --
626 cursor sel_bad_rates( p_location_id in number,
627 p_location_id_segment_1 in number,
628 p_location_id_segment_2 in number,
629 p_location_id_segment_3 in number,
630 p_location_id_segment_4 in number,
631 p_location_id_segment_5 in number,
632 p_location_id_segment_6 in number,
633 p_location_id_segment_7 in number,
634 p_location_id_segment_8 in number,
635 p_location_id_segment_9 in number,
636 p_location_id_segment_10 in number ) is
637
638 select rowid, sales_tax_id
639 from ar_sales_tax tax
640 where tax.location_id = p_location_id
641 and tax.enabled_flag = 'Y'
642 and not exists (
643 select
644 'x'
645 from ar_location_rates r1,
646 ar_location_rates r2,
647 ar_location_rates r3
648 where r1.location_segment_id = p_location_id_segment_1
649 and r2.location_segment_id = p_location_id_segment_2
650 and r3.location_segment_id = p_location_id_segment_3
651 And R1.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
652 AND R2.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
653 AND R3.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
654 And R1.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
655 AND R2.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
656 AND R3.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
657 And R1.START_DATE <= TAX.START_DATE
658 AND R2.START_DATE <= TAX.START_DATE
659 AND R3.START_DATE <= TAX.START_DATE
660 And R1.END_DATE <= TAX.END_DATE
661 AND R2.END_DATE <= TAX.END_DATE
662 AND R3.END_DATE <= TAX.END_DATE
663 and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
664 and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
665 and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
666 and tax.from_postal_code = greatest( r1.from_postal_code,
667 r2.from_postal_code,
668 r3.from_postal_code )
669 and tax.to_postal_code = least( r1.to_postal_code,
670 r2.to_postal_code,
671 r3.to_postal_code )
672 and tax.start_date = greatest( r1.start_date ,
673 r2.start_date ,
674 r3.start_date )
675 and tax.end_date = least( r1.end_date ,
676 r2.end_date ,
677 r3.end_date )
678 and greatest( r1.from_postal_code,
679 r2.from_postal_code,
680 r3.from_postal_code ) <= least( r1.to_postal_code,
681 r2.to_postal_code,
682 r3.to_postal_code )
683 and greatest( r1.start_date ,
684 r2.start_date ,
685 r3.start_date ) <= least( r1.end_date ,
686 r2.end_date ,
687 r3.end_date ));
688
689 --
690 CURSOR RateUsed( p_sales_tax_id IN NUMBER ) IS
691 SELECT 'x' from dual
692 WHERE exists (
693 select 'x'
694 from ra_customer_trx_lines l
695 where l.sales_tax_id = p_sales_tax_id );
696 --
697 dummy varchar2(30);
698 --
699 BEGIN
700 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
701 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
702 IF PG_DEBUG = 'Y' THEN
703 arp_util_tax.debug( '>> POPULATE_SALES_TAX ( ' || statement_type || ', '
704 || location_ccid || ' )' );
705 END IF;
706
707 /*------------------------------------------------------------------------+
708 | Validate every sales tax rate assignment for this location, ensuring |
709 | that valid location_rates still exist. |
710 | If any rate is no longer valid, attempt to delete the record, and if |
711 | this fails, disable the record so that it will not be available for |
712 | future use. |
713 | |
714 | This is only applicable is we are updating existing locations, simply |
715 | defining new locations means that there can be no records in the |
716 | sales tax table. |
717 +------------------------------------------------------------------------*/
718
719 IF statement_type = 'Update' or statement_type = 'Delete'
720 THEN
721 FOR tax_rate_rec in sel_bad_rates(
722 location_ccid, p_location_id_segment_1, p_location_id_segment_2, p_location_id_segment_3,
723 p_location_id_segment_4, p_location_id_segment_5, p_location_id_segment_6,
724 p_location_id_segment_7, p_location_id_segment_8, p_location_id_segment_9,
725 p_location_id_segment_10 )
726 LOOP
727 BEGIN
728 OPEN RateUsed( tax_rate_rec.sales_tax_id );
729 --
730 FETCH RateUsed
731 INTO dummy;
732 --
733 -- If rate not used cursor does not return a row
734 -- This means the sales tax is NOT used,
735 -- So we can delete it.
736 --
737 IF RateUsed%NOTFOUND THEN
738 --
739 DELETE FROM ar_sales_tax
740 WHERE rowid = tax_rate_rec.rowid;
741 --
742 ELSE
743 --
744 -- If rate not used cursor returns a row
745 -- This means the sales tax is being used,
746 -- So we do not want to delete it,intead, set enabled to 'N'
747 -- (This part is added because the AR foreign key constraint
748 -- is not shipped to customers )
749 --
750 UPDATE ar_sales_tax SET enabled_flag = 'N',
751 last_update_date = sysdate,
752 last_updated_by = arp_standard.profile.user_id
753 WHERE rowid = tax_rate_rec.rowid;
754 END IF;
755 CLOSE RateUsed;
756 END ;
757 END LOOP;
758 END IF;
759
760
761 /*-----------------------------------------------------------------------+
762 | Generate new sales tax records, by combining location rates from each |
763 | of the segment values of the location flexfield. |
764 | Only location rates that do not already exist in the ar_sales_tax |
765 | table are inserted. |
766 +-----------------------------------------------------------------------*/
767
768 IF PG_DEBUG = 'Y' THEN
769 arp_util_tax.debug( 'I: Populate_sales_tax: ' || p_location_id_segment_1 || ' ' ||
770 p_location_id_segment_2 || ' ' ||
771 p_location_id_segment_3 || ' ' ||
772 p_location_id_segment_4 );
773 END IF;
774
775
776 IF statement_type <> 'Delete'
777 THEN
778
779 if statement_type = 'Update'
780 THEN
781
782 insert into ar_sales_tax(
783 SALES_TAX_ID,
784 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
785 CREATED_BY, CREATION_DATE,
786 LOCATION_ID,
787 rate_context,
788 tax_rate,
789 LOCATION1_RATE,
790 LOCATION2_RATE,
791 LOCATION3_RATE,
792 from_postal_code,
793 to_postal_code,
794 start_date,
795 end_date,
796 enabled_flag)
797 select
798 AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
799 sysdate,
800 arp_standard.profile.user_id,
801 null,
802 arp_standard.profile.user_id,
803 sysdate,
804 location_ccid,
805 arp_standard.sysparm.location_structure_id,
806 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
807 + decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
808 + decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
809 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
810 decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
811 decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
812 greatest( r1.from_postal_code,
813 r2.from_postal_code,
814 r3.from_postal_code ),
815 least( r1.to_postal_code,
816 r2.to_postal_code,
817 r3.to_postal_code ),
818 greatest( r1.start_date ,
819 r2.start_date ,
820 r3.start_date ),
821 least( r1.end_date ,
822 r2.end_date ,
823 r3.end_date ),
824 'Y' /* Enabled Flag */
825 from ar_location_rates r1,
826 ar_location_rates r2,
827 ar_location_rates r3
828 where r1.location_segment_id = p_location_id_segment_1
829 and r2.location_segment_id = p_location_id_segment_2
830 and r3.location_segment_id = p_location_id_segment_3
831 and greatest( r1.from_postal_code,
832 r2.from_postal_code,
833 r3.from_postal_code ) <= least( r1.to_postal_code,
834 r2.to_postal_code,
835 r3.to_postal_code )
836 and greatest( r1.start_date ,
837 r2.start_date ,
838 r3.start_date ) <= least( r1.end_date ,
839 r2.end_date ,
840 r3.end_date )
841 and not exists (
842 select 'x' from ar_sales_tax tax
843 where tax.location_id = location_ccid
844 and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
845 and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
846 and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
847 and tax.from_postal_code = greatest( r1.from_postal_code,
848 r2.from_postal_code,
849 r3.from_postal_code )
850 and tax.to_postal_code = least( r1.to_postal_code,
851 r2.to_postal_code,
852 r3.to_postal_code )
853 and tax.start_date = greatest( r1.start_date ,
854 r2.start_date ,
855 r3.start_date )
856 and tax.end_date = least( r1.end_date ,
857 r2.end_date ,
858 r3.end_date )
859 and tax.enabled_flag = 'Y' );
860
861 ELSE /* Statement type is INSERT, dont us: not exists clause */
862
863 insert into ar_sales_tax(
864 SALES_TAX_ID,
865 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
866 CREATED_BY, CREATION_DATE,
867 LOCATION_ID,
868 rate_context,
869 tax_rate,
870 LOCATION1_RATE,
871 LOCATION2_RATE,
872 LOCATION3_RATE,
873 from_postal_code,
874 to_postal_code,
875 start_date,
876 end_date,
877 enabled_flag)
878 select
879 AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
880 sysdate,
881 arp_standard.profile.user_id,
882 null,
883 arp_standard.profile.user_id,
884 sysdate,
885 location_ccid,
886 arp_standard.sysparm.location_structure_id,
887 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
888 + decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
889 + decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
890 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
891 decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
892 decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
893 greatest( r1.from_postal_code,
894 r2.from_postal_code,
895 r3.from_postal_code ),
896 least( r1.to_postal_code,
897 r2.to_postal_code,
898 r3.to_postal_code ),
899 greatest( r1.start_date ,
900 r2.start_date ,
901 r3.start_date ),
902 least( r1.end_date ,
903 r2.end_date ,
904 r3.end_date ),
905 'Y' /* Enabled Flag */
906 from ar_location_rates r1,
907 ar_location_rates r2,
908 ar_location_rates r3
909 where r1.location_segment_id = p_location_id_segment_1
910 and r2.location_segment_id = p_location_id_segment_2
911 and r3.location_segment_id = p_location_id_segment_3
912 and greatest( r1.from_postal_code,
913 r2.from_postal_code,
914 r3.from_postal_code ) <= least( r1.to_postal_code,
915 r2.to_postal_code,
916 r3.to_postal_code )
917 and greatest( r1.start_date ,
918 r2.start_date ,
919 r3.start_date ) <= least( r1.end_date ,
920 r2.end_date ,
921 r3.end_date );
922
923 END IF; /* Insert or Update Mode */
924
925 END IF; /* Dont call if in delete mode */
926
927 IF PG_DEBUG = 'Y' THEN
928 arp_util_tax.debug( '<< POPULATE_SALES_TAX' );
929 END IF;
930
931 END Populate_Sales_Tax;
932
933
934 PROCEDURE propogate_sales_tax IS
935 BEGIN
936 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
937 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
938
939 IF PG_DEBUG = 'Y' THEN
940 arp_util_tax.debug( '>> PROPOGATE_SALES_TAX' );
941 END IF;
942
943 update ar_location_combinations
944 set last_update_date = sysdate ,
945 last_updated_by = arp_standard.profile.user_id,
946 program_id = arp_standard.profile.program_id,
947 program_application_id = arp_standard.profile.program_application_id
948 where location_id in ( select loc_assign.loc_id
949 from hz_party_sites party_site,
950 hz_loc_assignments loc_assign,
951 hz_locations loc,
952 hz_cust_acct_sites acct_site,
953 hz_cust_site_uses site_uses
954 where site_uses.cust_acct_site_id =
955 acct_site.cust_acct_site_id
956 and acct_site.party_site_id =
957 party_site.party_site_id
958 and loc.location_id = party_site.location_id
959 and loc.location_id = loc_assign.location_id
960 and nvl(acct_site.org_id,-99) =
961 nvl(loc_assign.org_id,-99)
962 and site_uses.site_use_code = 'SHIP_TO' );
963 IF PG_DEBUG = 'Y' THEN
964 arp_util_tax.debug( '<< PROPOGATE_SALES_TAX' );
965 END IF;
966
967 END;
968
969
970 PROCEDURE Purge_Sales_Tax IS
971
972 CURSOR sel_rates_c IS
973 SELECT rowid, sales_tax_id from ar_sales_tax;
974
975 CURSOR PurgeRateUsed( p_sales_tax_id IN NUMBER ) IS
976 SELECT 'x' from dual
977 WHERE exists (
978 select 'x'
979 from ra_customer_trx_lines l
980 where l.sales_tax_id = p_sales_tax_id );
981 --
982 dummy varchar2(30);
983 --
984 BEGIN
985 -- PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
986 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
987 ARP_UTIL_TAX.DEBUG( '>> PURGE_SALES_TAX' );
988
989 FOR tax in sel_rates_c
990 LOOP
991 BEGIN
992 OPEN PurgeRateUsed( tax.sales_tax_id );
993 --
994 FETCH PurgeRateUsed
995 INTO dummy;
996 --
997 -- If rate not used cursor returns a row
998 -- This means the sales tax is NOT used,
999 -- So we can delete it.
1000 --
1001 IF PurgeRateUsed%NOTFOUND THEN
1002 --
1003 DELETE FROM ar_sales_tax
1004 WHERE rowid = tax.rowid;
1005 --
1006 ELSE
1007 --
1008 -- If rate not used cursor returns no row
1009 -- This means the sales tax is being used,
1010 -- So we do not want to delete it,intead, set enabled to 'N'
1011 -- (This part is added because the AR foreign key constraint
1012 -- is not shipped to customers )
1013 --
1014 UPDATE ar_sales_tax SET enabled_flag = 'N',
1015 last_update_date = sysdate,
1016 last_updated_by = arp_standard.profile.user_id
1017 WHERE rowid = tax.rowid;
1018 END IF;
1019 CLOSE PurgeRateUsed;
1020 END ;
1021 END LOOP;
1022
1023 ARP_UTIL_TAX.DEBUG( '<< PURGE_SALES_TAX' );
1024
1025 END Purge_Sales_Tax;
1026
1027 /*-------------------------------------------------------------------------+
1028 | PUBLIC PROCEDURE |
1029 | renumber_tax_lines( customer_trx_id in number ) |
1030 | |
1031 | CALLED BY USER EXIT: #AR SALESTAX MODE=UPDATE |
1032 | |
1033 | DESCRIPTION |
1034 | Will renumber each Tax line belonging to an invoice so that duplicate |
1035 | line numbers no longer exist. |
1036 | For Example:- if Original Invoice had the following lines, this |
1037 | procedure would update each of the line numbers to be:- |
1038 | |
1039 | OLD INVOICE NEW_INVOICE |
1040 | 1 ITEM LINE 1 ITEM LINE |
1041 | 1 TAX LINE 1 TAX LINE |
1042 | 2 TAX LINE 2 TAX LINE |
1043 | 1 TAX LINE 3 TAX LINE |
1044 | |
1045 | Duplicate Line numbers can occur when a header level change is made |
1046 | for an invoice and all but Adhoc Tax lines are deleted and then |
1047 | recalculated. |
1048 | |
1049 | REQUIRES: CUSTOMER_TRX_ID |
1050 | |
1051 | MODIFIES: RA_CUSTOMER_TRX_LINES.LINE_NUMBER |
1052 | |
1053 +-------------------------------------------------------------------------*/
1054
1055 PROCEDURE renumber_tax_lines( customer_trx_id in number,
1056 trx_type in varchar2 default 'TAX' ) is
1057 cursor c_tax_lines( cust_trx_id in number, cust_trx_type in varchar2 ) is
1058 select customer_trx_line_id,
1059 line_number,
1060 link_to_cust_trx_line_id
1061 from ra_customer_trx_lines
1062 where customer_trx_id = cust_trx_id
1063 and line_type = cust_trx_type
1064 and link_to_cust_trx_line_id is not null
1065 order by link_to_cust_trx_line_id, customer_trx_line_id
1066 for update of line_number;
1067
1068
1069 previous_parent_line_id number := 0;
1070 new_line_number number := 0;
1071
1072 BEGIN
1073 -- PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1074 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1075
1076 for trx_line in c_tax_lines( customer_trx_id, trx_type )
1077 Loop
1078 if trx_line.link_to_cust_trx_line_id <> previous_parent_line_id
1079 then
1080 previous_parent_line_id :=
1081 trx_line.link_to_cust_trx_line_id;
1082 new_line_number := 0;
1083 end if;
1084 new_line_number := new_line_number + 1;
1085 update ra_customer_trx_lines set line_number = new_line_number
1086 where current of c_tax_lines;
1087 end loop;
1088
1089 END;
1090
1091
1092 /*------------------------------------------------------------------------+
1093 | PUBLIC PROCEDURE |
1094 | period_date_range |
1095 | |
1096 | CALLED BY find_tax_exemption_id |
1097 | |
1098 | DESCRIPTION |
1099 | This function returns the start date in the period associated |
1100 | any given trx_date. |
1101 | |
1102 | REQUIRES |
1103 | TRX_DATE Transaction Date |
1104 | |
1105 | RETURNS |
1106 | START_DATE First date in period identified by trxdate |
1107 | END_DATE Last date in period identified by trxdate |
1108 | |
1109 | MODIFICATION HISTORY |
1110 | 24 May, 1994 Nigel Smith Created. |
1111 | |
1112 +------------------------------------------------------------------------*/
1113
1114 procedure period_date_range( trx_date in date,
1115 start_date out NOCOPY date,
1116 end_date out NOCOPY date ) is
1117
1118 cursor sel_date( trx_date in date ) is
1119 select p.start_date, p.end_date
1120 from gl_period_statuses p, gl_sets_of_books g
1121 where p.application_id = arp_standard.application_id
1122 and p.set_of_books_id = arp_standard.sysparm.set_of_books_id
1123 and trunc(trx_date) between p.start_date and p.end_date
1124 and g.set_of_books_id = p.set_of_books_id
1125 and g.accounted_period_type = p.period_type;
1126
1127 begin
1128 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1129 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1130
1131
1132 IF PG_DEBUG = 'Y' THEN
1133 arp_util_tax.debug('period_date_range ( '||to_char(trx_date)||')');
1134 END IF;
1135
1136 open sel_date( trx_date );
1137 fetch sel_date into start_date, end_date;
1138
1139 if sel_date%notfound
1140 then
1141 close sel_date;
1142 arp_standard.fnd_message( 'AR_TW_NO_PERIOD_DEFINED','DATE',to_char(trx_date));
1143 IF PG_DEBUG = 'Y' THEN
1144 arp_util_tax.debug('AR ACCOUNTING PERIOD IS NOT DEFINED');
1145 END IF;
1146
1147 end if;
1148 if sel_date%isopen then
1149 close sel_date;
1150 end if;
1151
1152 end;
1153
1154
1155
1156 /*------------------------------------------------------------------------+
1157 | PUBLIC PROCEDURE |
1158 | find_tax_exemption_id |
1159 | |
1160 | CALLED BY sales tax engine |
1161 | |
1162 | DESCRIPTION |
1163 | Each transaction line may be forced exempt from taxes by the user. |
1164 | When exempted; "find_tax_exemption_id" is called, passing in the |
1165 | Exemption Certificate Number and Reason Code. If no "Unapproved", |
1166 | "Manual" or "Primary" Exemption exists for this Customer, Location, |
1167 | Tax Code and Reason this routine will optionally create an Automatic |
1168 | exemption with a status of "Unapproved" and a location that matches |
1169 | the flexfield qualifier: "EXEMPT_LEVEL". |
1170 | |
1171 | REQUIRES |
1172 | BILL TO_CUSTOMER_ID Bill To Customer ID (mandatory) |
1173 | SHIP_TO_CUSTOMER_ID Ship To Customer ID |
1174 | SHIP_TO_SITE_ID Identifies the ship to site from which we |
1175 | can deduce the State, County and City |
1176 | Or other segments applicable to the sales |
1177 | tax location flexfield. |
1178 | INVENTORY_ITEM_ID Item exemptions are if found, used |
1179 | TRX_DATE Tax Date for this transaction |
1180 | TAX_CODE Tax Code for this transaction |
1181 | TAX_EXEMPT_FLAG "S"tandard; "E"xempt or "R"equire |
1182 | REASON_CODE Mandatory for all Exempt transactions |
1183 | CERTIFICATE Optional, used in Exempt transactions |
1184 | PERCENT_EXEMPT Exemption, Percentage of Tax that is |
1185 | Exempt; or NULL if no Exemption is |
1186 | applicable. |
1187 | INSERT_ALLOWED If False and "E" is called but not |
1188 | valid exemption is on file; this routine |
1189 | will return an error. |
1190 | RETURNS |
1191 | TAX_EXEMPTION_ID Foreign Key to "RA_TAX_EXEMPTIONS" |
1192 | If NULL, this transaction is NOT exempt |
1193 | CERTIFICATE Certificate Number |
1194 | REASON Reason Code for exemption |
1195 | INSERTED_FLAG TRUE if this call forced an insert |
1196 | EXEMPTION_TYPE CUSTOMER or ITEM |
1197 | |
1198 | DATABASE REQUIREMENTS |
1199 | View: TAX_EXEMPTIONS_V This view must be installed before |
1200 | this database package can be installed |
1201 | |
1202 | MODIFICATION HISTORY |
1203 | |
1204 | 17-May-94 Nigel Smith Created. |
1205 | 3 Aug, 94 Nigel Smith BUGFIX: 228807, Exemptions are now |
1206 | managed by Bill To Customer and Ship |
1207 | To Site. |
1208 | 13 Oct 94 Nigel Smith Bugfix: 227953, Exemptions with No |
1209 | certificate number were not shown by Order|
1210 | Entry. |
1211 | 19 Oct 94 Nigel Smith Bugfix: 244306, Error Validating Address |
1212 | during order entry; or invoice creation on|
1213 | export orders/invoices that are marked as |
1214 | exempt. |
1215 | 30-Jul-97 M Sabapathy Bugfix 520228: Changed cursor |
1216 | sel_item_exemption to look for exemptions |
1217 | with status PRIMARY only. |
1218 | 28-Mar-00 Helen Si Bugfix 1039662: View Performance. |
1219 | Changed view TAX_EXEMPTIONS_QP_V to |
1220 | TAX_EXEMPTIONS_V. |
1221 | |
1222 +------------------------------------------------------------------------*/
1223
1224
1225 PROCEDURE find_tax_exemption_id(
1226 bill_to_customer_id in number,
1227 ship_to_customer_id in number,
1228 ship_to_site_id in number,
1229 tax_code in varchar2,
1230 inventory_item_id in number,
1231 trx_date in date,
1232 tax_exempt_flag in varchar2,
1233 insert_allowed in varchar2 default 'TRUE',
1234 reason_code in out NOCOPY varchar2,
1235 certificate in out NOCOPY varchar2,
1236 percent_exempt out NOCOPY number,
1237 inserted_flag out NOCOPY varchar2,
1238 tax_exemption_id out NOCOPY number,
1239 exemption_type out NOCOPY varchar2
1240 ) is
1241
1242 --
1243 -- CURSOR: chk_customer_exemption
1244 --
1245 -- Check to see if this customer has an exemption at any level
1246 --
1247 -- The Explain Plan from this cursor, is very light weight and will for the
1248 -- majority of calls to the tax engine, result in no data found forcing the
1249 -- larger cursor not to be executed. This is based on the assumption
1250 -- that the majority of Customers are not Tax Exempt; if they are an
1251 -- Installation site should consider using Receivable Transactions
1252 -- with a "Calculation Tax: No"; and then "Requiring Tax" in the
1253 -- exception case.
1254 --
1255 -- EXPLAIN PLAN
1256 --
1257 -- OPERATION OPTIONS OBJECT_NAME
1258 -- -------------------------------------- --------------- ------------------
1259 -- FILTER
1260 -- TABLE ACCESS FULL DUAL
1261 -- INDEX RANGE SCAN RA_TAX_EXEMPTIONS_N1
1262 --
1263 -- The Where Exists clause ensures that the database stops the search on
1264 -- the index RAX_TAX_EXMEPTIONS_N1 for the first row found.
1265 --
1266
1267 cursor chk_customer_exemption(customer in number) is
1268 select 'x' from dual where exists
1269 ( select 'x' from ra_tax_exemptions where
1270 customer_id = customer );
1271
1272 --
1273 -- CURSOR: sel_customer_exemption
1274 --
1275 -- Find Customer Exemption, for this transaction line
1276 --
1277 cursor sel_customer_exemption(customer in number, site in number,
1278 trxdate in date, taxcode in varchar2,
1279 tax_exempt_flag in varchar2,
1280 certificate_number in varchar2,
1281 reason_code in varchar2 ) is
1282 select
1283 x.percent_exempt,
1284 x.tax_exemption_id,
1285 x.reason_code,
1286 rtrim(ltrim(nvl(x.customer_exemption_number,' '))) tax_exempt_number,
1287 x.status,
1288 x.start_date,
1289 x.end_date,
1290 decode( x.site_use_id, null,
1291 decode(x.location_id_segment_10, null,
1292 decode(x.location_id_segment_9, null,
1293 decode(x.location_id_segment_8, null,
1294 decode(x.location_id_segment_7, null,
1295 decode(x.location_id_segment_6, null,
1296 decode(x.location_id_segment_5, null,
1297 decode(x.location_id_segment_4, null,
1298 decode(x.location_id_segment_3, null,
1299 decode(x.location_id_segment_2, null,
1300 decode(x.location_id_segment_1, null,
1301 11, 10), 9), 8), 7), 6), 5), 4), 3), 2), 1), 0 )
1302 +decode( x.status, 'PRIMARY', 0,
1303 'MANUAL', 1000,
1304 'UNAPPROVED', 2000,
1305 'EXPIRED', 3000, 4000 )
1306 DISPLAY_ORDER
1307 from
1308 hz_cust_site_uses s,
1309 hz_cust_acct_sites a,
1310 hz_party_sites p,
1311 hz_loc_assignments la,
1312 ra_tax_exemptions x,
1313 ar_location_combinations c
1314 WHERE la.loc_id = c.location_id(+)
1315 and a.party_site_id = p.party_site_id
1316 and p.location_id = la.location_id
1317 and nvl(a.org_id, -99) = nvl(la.org_id, -99)
1318 and s.cust_acct_site_id = a.cust_acct_site_id
1319 and nvl(x.location_id_segment_1, nvl(c.location_id_segment_1,-1)) = nvl(c.location_id_segment_1,-1)
1320 and nvl(x.location_id_segment_2, nvl(c.location_id_segment_2,-1)) = nvl(c.location_id_segment_2,-1)
1321 and nvl(x.location_id_segment_3, nvl(c.location_id_segment_3,-1)) = nvl(c.location_id_segment_3,-1)
1322 and nvl(x.location_id_segment_4, nvl(c.location_id_segment_4,-1)) = nvl(c.location_id_segment_4,-1)
1323 and nvl(x.location_id_segment_5, nvl(c.location_id_segment_5,-1)) = nvl(c.location_id_segment_5,-1)
1324 and nvl(x.location_id_segment_6, nvl(c.location_id_segment_6,-1)) = nvl(c.location_id_segment_6,-1)
1325 and nvl(x.location_id_segment_7, nvl(c.location_id_segment_7,-1)) = nvl(c.location_id_segment_7,-1)
1326 and nvl(x.location_id_segment_8, nvl(c.location_id_segment_8,-1)) = nvl(c.location_id_segment_8,-1)
1327 and nvl(x.location_id_segment_9, nvl(c.location_id_segment_9,-1)) = nvl(c.location_id_segment_9,-1)
1328 and nvl(x.location_id_segment_10, nvl(c.location_id_segment_10,-1)) = nvl(c.location_id_segment_10,-1)
1329 and x.exemption_type = 'CUSTOMER'
1330 and nvl( x.site_use_id, s.site_use_id ) = s.site_use_id
1331 and x.customer_id = customer
1332 and s.site_use_id = site
1333 and x.tax_code = taxcode
1334 /*
1335 * Standard Tax rules can only search for Exemptions that are marked as
1336 * PRIMARY. All other exemptions are ignored.
1337 *
1338 */
1339 AND (( tax_exempt_flag = 'S' and x.status = 'PRIMARY' )
1340 /*
1341 * Transactions that are forced exempt, should only ever use an existing certificate
1342 * number if:-
1343 * The Certificate is not rejected or expired.
1344 * The user supplied reason codes, and exemption numbers match those on the certificate
1345 * (note the supplied exemption number can be null)
1346 *
1347 * If these conditions are NOT met, a new Unapproved certificate will be created
1348 * to support this.
1349 *
1350 */
1351 OR ( tax_exempt_flag = 'E'
1352 AND x.STATUS IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
1353 AND x.REASON_CODE = reason_code
1354 AND ( (rtrim(ltrim(x.customer_exemption_number)) = certificate_number)
1355 or (x.customer_exemption_number IS NULL AND
1356 certificate_number IS NULL)) ))
1357 AND trxdate between x.start_date and nvl(x.end_date, trx_date)
1358 ORDER BY DISPLAY_ORDER;
1359
1360 cursor sel_item_exemption( item in number, taxcode in varchar2, trxdate in date ) is
1361 SELECT percent_exempt, tax_exemption_id,
1362 reason_code tax_exempt_reason_code,
1363 rtrim(ltrim(customer_exemption_number)) tax_exempt_number
1364 FROM ra_tax_exemptions
1365 WHERE inventory_item_id = item
1366 AND tax_code = taxcode
1367 AND trxdate between start_date and nvl(end_date, trx_date)
1368 AND exemption_type = 'ITEM'
1369 AND status = 'PRIMARY'; /* Bugfix 520228 */
1370
1371 CURSOR sel_tax_exemptions_s is
1372 SELECT ra_tax_exemptions_s.nextval from dual;
1373
1374 CURSOR sel_location_ids( site_id in number ) is
1375 select c.location_id_segment_1,
1376 c.location_id_segment_2,
1377 c.location_id_segment_3,
1378 c.location_id_segment_4,
1379 c.location_id_segment_5,
1380 c.location_id_segment_6,
1381 c.location_id_segment_7,
1382 c.location_id_segment_8,
1383 c.location_id_segment_9,
1384 c.location_id_segment_10
1385 from ar_location_combinations c,
1386 hz_cust_acct_sites acct_site,
1387 hz_loc_assignments loc_assign,
1388 hz_locations loc,
1389 hz_party_sites party_site,
1390 hz_cust_site_uses site_uses
1391 where site_uses.site_use_id = site_id
1392 and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
1393 and acct_site.party_site_id = party_site.party_site_id
1394 and loc.location_id = party_site.location_id
1395 and loc.location_id = loc_assign.location_id
1396 and loc_assign.loc_id = c.location_id(+); /* BUGFIX: 244306 */
1397
1398 l_percent_exempt number := 0;
1399 l_exemption_id number := NULL ;
1400 l_tax_exempt_number varchar2(80) := NULL;
1401 l_tax_exempt_reason_code varchar2(30) := NULL;
1402 l_inserted boolean := FALSE;
1403 l_exemption_type varchar2(30) := NULL;
1404 l_period_start_date date;
1405 l_period_end_date date;
1406 l_find_customer_exemption boolean;
1407
1408 l_bill_to_customer_id NUMBER := NULL;
1409 l_status_code VARCHAR2(30) := NULL;
1410 l_start_date DATE;
1411 l_end_date DATE;
1412
1413 l_location_id_segment TAB_ID_TYPE;
1414 dummy dual.dummy%type;
1415 l_display_order NUMBER;
1416
1417 BEGIN
1418 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1419 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1420
1421 ARP_UTIL_TAX.DEBUG( '>> FIND_TAX_EXEMPTION_ID: ' || bill_to_customer_id || ', ' ||
1422 ship_to_site_id || ', ' || tax_code || ', ' ||
1423 inventory_item_id || ', ' || to_char(trx_date, 'DD-MON-YYYY') || ', ' ||
1424 tax_exempt_flag || ', ' || reason_code || ', ' || certificate );
1425
1426
1427 l_inserted := FALSE;
1428 l_exemption_type := NULL;
1429 l_tax_exempt_number := ltrim(rtrim(certificate));
1430 l_tax_exempt_reason_code := reason_code;
1431 l_find_customer_exemption := FALSE;
1432
1433 --
1434 -- Assign null values to each candidate location segment id
1435 -- so that the region descriptive flexfield will be
1436 -- populated correctly in the ra_tax_exemptions table
1437 --
1438 for i in 1 .. 10
1439 loop
1440 l_location_id_segment(i) := NULL;
1441 end loop;
1442
1443 if l_tax_exempt_reason_code is null and tax_exempt_flag = 'E'
1444 then
1445 arp_standard.fnd_message( 'AR_NO_REASON_FOR_EXEMPTION' );
1446 IF PG_DEBUG = 'Y' THEN
1447 arp_util_tax.debug('NO REASON FOR EXEMPTION ');
1448 END IF;
1449
1450 end if;
1451
1452 if arp_standard.sysparm.tax_use_customer_exempt_flag = 'Y' and
1453 tax_exempt_flag <> 'R' then
1454
1455 /* Standard Tax Rules May have a customer Exemption */
1456 if tax_exempt_flag = 'S' then
1457
1458 OPEN chk_customer_exemption( bill_to_customer_id );
1459 FETCH chk_customer_exemption into dummy;
1460
1461 if chk_customer_exemption%FOUND then
1462
1463 l_find_customer_exemption := TRUE;
1464
1465 IF PG_DEBUG = 'Y' THEN
1466 arp_util_tax.debug( 'I: A Customer Exemption Exists (chk1)' );
1467 END IF;
1468
1469 else
1470
1471 l_find_customer_exemption := FALSE;
1472
1473 IF PG_DEBUG = 'Y' THEN
1474 arp_util_tax.debug( 'I: No Customer Exemption found (chk1)' );
1475 END IF;
1476
1477 end if;
1478
1479 CLOSE chk_customer_exemption;
1480
1481 else
1482
1483 /* Exempt Transactions have to use the larger cursor: sel_customer_exemption */
1484 l_find_customer_exemption := TRUE;
1485
1486 end if; -- tax_exempt_flag = 'S'
1487
1488 if l_find_customer_exemption then
1489
1490 FOR j IN 1 .. pg_max_index
1491 LOOP
1492
1493 if tax_exempt_info_tbl(j).bill_to_customer_id = bill_to_customer_id
1494 and tax_exempt_info_tbl(j).ship_to_site_use_id = ship_to_site_id
1495 and tax_exempt_info_tbl(j).tax_code = tax_code
1496 and ( tax_exempt_flag = 'S' and ( tax_exempt_info_tbl(j).status_code = 'PRIMARY'
1497 or tax_exempt_info_tbl(j).status_code is null)
1498 or ( tax_exempt_flag = 'E'
1499 and ( tax_exempt_info_tbl(j).status_code in ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
1500 or tax_exempt_info_tbl(j).status_code is null)
1501 and ( tax_exempt_info_tbl(j).tax_exempt_reason_code = l_tax_exempt_reason_code
1502 or ( tax_exempt_info_tbl(j).tax_exempt_reason_code is null
1503 and l_tax_exempt_reason_code is null))
1504 and ( (rtrim(ltrim(tax_exempt_info_tbl(j).tax_exempt_number)) = l_tax_exempt_number)
1505 or ( rtrim(ltrim(tax_exempt_info_tbl(j).tax_exempt_number)) is NULL
1506 and l_tax_exempt_number is null
1507 )
1508 )
1509 )
1510 )
1514 l_bill_to_customer_id := bill_to_customer_id;
1511 and trx_date >= tax_exempt_info_tbl(j).start_date
1512 and trx_date <= nvl(tax_exempt_info_tbl(j).end_date, trx_date) then
1513
1515 l_percent_exempt := tax_exempt_info_tbl(j).percent_exempt;
1516 l_exemption_id := tax_exempt_info_tbl(j).tax_exemption_id;
1517 l_tax_exempt_reason_code := tax_exempt_info_tbl(j).tax_exempt_reason_code;
1518 l_tax_exempt_number := tax_exempt_info_tbl(j).tax_exempt_number;
1519 l_status_code := tax_exempt_info_tbl(j).status_code;
1520
1521 IF PG_DEBUG = 'Y' THEN
1522 arp_util_tax.debug('INSIDE OF LOOP:Found exemption in cache('||to_char(j)||')');
1523 END IF;
1524
1525 EXIT;
1526
1527 end if;
1528
1529 END LOOP; -- FOR j IN 1 .. pg_max_index
1530
1531 if l_bill_to_customer_id is null then
1532
1533 IF PG_DEBUG = 'Y' THEN
1534 arp_util_tax.debug('No matching record in cache.');
1535 END IF;
1536
1537 OPEN sel_customer_exemption( bill_to_customer_id, ship_to_site_id,
1538 trx_date, tax_code, tax_exempt_flag,
1539 l_tax_exempt_number, l_tax_exempt_reason_code );
1540
1541 FETCH sel_customer_exemption into l_percent_exempt,
1542 l_exemption_id,
1543 l_tax_exempt_reason_code,
1544 l_tax_exempt_number,
1545 l_status_code,
1546 l_start_date,
1547 l_end_date,
1548 l_display_order;
1549
1550 if sel_customer_exemption%NOTFOUND then
1551
1552 l_percent_exempt := 100;
1553
1554 /* Reset these values, they may now be null */
1555 l_tax_exempt_reason_code := reason_code;
1556 l_tax_exempt_number := rtrim(ltrim(certificate));
1557
1558 if tax_exempt_flag = 'E' and
1559 insert_allowed = 'TRUE' and
1560 l_tax_exempt_reason_code is not null then
1561
1562 /*********************************************************************/
1563 /* Using: EXEMPT_LEVEL qualifier, find each location_segment_id */
1564 /* that must be populated in ra_tax_exemptions.location_id_segment_n */
1565 /*********************************************************************/
1566
1567 IF PG_DEBUG = 'Y' THEN
1568 arp_util_tax.debug( 'I: Location Qualifiers');
1569 arp_util_tax.debug( 'EXEMPT_LEVEL STATE TAX_ACCOUNT, COUNTY, CITY' );
1570 END IF;
1571
1572 /*** MB skip, hardcode search for EXEMPT_LEVEL ***/
1573 if instr( 'EXEMPT_LEVEL STATE TAX_ACCOUNT, COUNTY, CITY', 'EXEMPT_LEVEL' ) <> 0 then
1574
1575 IF PG_DEBUG = 'Y' THEN
1576 arp_util_tax.debug( 'I: FIND_TAX_EXEMPTION_ID: USING EXEMT_LEVEL SEGMENT QUALIFIER FOR NEW EXEMPTION');
1577 END IF;
1578
1579 OPEN sel_location_ids( ship_to_site_id );
1580
1581 FETCH sel_location_ids into
1582 l_location_id_segment(1),
1583 l_location_id_segment(2),
1584 l_location_id_segment(3),
1585 l_location_id_segment(4),
1586 l_location_id_segment(5),
1587 l_location_id_segment(6),
1588 l_location_id_segment(7),
1589 l_location_id_segment(8),
1590 l_location_id_segment(9),
1591 l_location_id_segment(10);
1592
1593 if sel_location_ids%NOTFOUND then
1594 CLOSE sel_location_ids;
1595 arp_standard.fnd_message( 'AR_STAX_NO_LOCATION_ID', 'SITE_USE_ID', ship_to_site_id );
1596 IF PG_DEBUG = 'Y' THEN
1597 arp_util_tax.debug('AR STAX NO LOCATION ID');
1598 END IF;
1599 end if;
1600 CLOSE sel_location_ids;
1601
1602 /***************************************************************************/
1603 /* Mark as null all trailing location segment id's that follow the segment */
1604 /* at which automatic exemptions are created for. */
1605 /***************************************************************************/
1606
1607 BEGIN
1608 FOR i in arp_flex.expand( arp_flex.location, 'EXEMPT_LEVEL', null, '%NUMBER%')+1 .. 10
1609 LOOP
1610 l_location_id_segment(i) := NULL;
1611 END LOOP;
1612 EXCEPTION
1613 /*********************************************************************************/
1614 /* arp_flex.expand can raise error message if the sales tax location flexfield */
1615 /* has been reconfigured without then rerunning the pl/sql flexfield precompiler */
1616 /*********************************************************************************/
1617 WHEN OTHERS THEN
1618 arp_standard.fnd_message( 'AR_STAX_NOT_INSTALLED' );
1619 IF PG_DEBUG = 'Y' THEN
1620 arp_util_tax.debug('STAX NOT INSTALLED');
1621 END IF;
1622
1623 END;
1624
1625 end if; -- instr( 'EXEMPT_LEVEL STATE TAX_ACCOUNT, COUNTY, CITY', 'EXEMPT_LEVEL' ) <> 0
1626
1627
1628 /***************************************/
1629 /* Insert automatic customer exemption */
1630 /***************************************/
1631
1632 period_date_range( trx_date,
1633 l_period_start_date,
1634 l_period_end_date );
1635
1636 OPEN sel_tax_exemptions_s;
1637 FETCH sel_tax_exemptions_s into l_exemption_id;
1638 CLOSE sel_tax_exemptions_s;
1639
1640
1641 insert into ra_tax_exemptions(
1642 TAX_EXEMPTION_ID,
1643 CREATED_BY,
1644 CREATION_DATE,
1645 EXEMPTION_TYPE,
1646 IN_USE_FLAG,
1647 LAST_UPDATED_BY,
1648 LAST_UPDATE_DATE,
1649 PERCENT_EXEMPT,
1650 START_DATE,
1651 TAX_CODE,
1652 CUSTOMER_ID,
1653 END_DATE,
1654 PROGRAM_APPLICATION_ID,
1655 PROGRAM_ID,
1656 PROGRAM_UPDATE_DATE,
1657 REQUEST_ID,
1658 CUSTOMER_EXEMPTION_NUMBER,
1659 REASON_CODE,
1660 STATUS,
1661 LOCATION_CONTEXT,
1662 LOCATION_ID_SEGMENT_1,
1663 LOCATION_ID_SEGMENT_2,
1664 LOCATION_ID_SEGMENT_3,
1665 LOCATION_ID_SEGMENT_4,
1666 LOCATION_ID_SEGMENT_5,
1667 LOCATION_ID_SEGMENT_6,
1668 LOCATION_ID_SEGMENT_7,
1669 LOCATION_ID_SEGMENT_8,
1670 LOCATION_ID_SEGMENT_9,
1671 LOCATION_ID_SEGMENT_10
1672 )
1673 values
1674 (
1675 l_exemption_id,
1676 arp_standard.profile.user_id,
1677 sysdate,
1678 'CUSTOMER',
1679 'Y',
1680 arp_standard.profile.user_id,
1681 sysdate,
1682 100.00,
1683 l_period_start_date,
1684 tax_code,
1685 bill_to_customer_id,
1686 null,
1687 arp_standard.application_id,
1688 arp_standard.profile.program_id,
1689 sysdate,
1690 arp_standard.profile.request_id,
1691 l_tax_exempt_number,
1692 l_tax_exempt_reason_code,
1693 'UNAPPROVED',
1694 arp_standard.sysparm.location_structure_id,
1695 l_location_id_segment(1),
1696 l_location_id_segment(2),
1697 l_location_id_segment(3),
1698 l_location_id_segment(4),
1699 l_location_id_segment(5),
1700 l_location_id_segment(6),
1701 l_location_id_segment(7),
1702 l_location_id_segment(8),
1703 l_location_id_segment(9),
1704 l_location_id_segment(10)
1705 );
1706
1707 l_exemption_type := 'CUSTOMER';
1708 l_inserted := TRUE;
1709
1710 IF PG_DEBUG = 'Y' THEN
1711 arp_util_tax.debug('Inserting into cache after inserting into ra_tax_exemptions');
1712 END IF;
1713
1714 pg_max_index := pg_max_index + 1;
1715 tax_exempt_info_tbl(pg_max_index).percent_exempt := 100.00;
1716 tax_exempt_info_tbl(pg_max_index).tax_exemption_id := l_exemption_id;
1717 tax_exempt_info_tbl(pg_max_index).tax_exempt_reason_code := l_tax_exempt_reason_code;
1718 tax_exempt_info_tbl(pg_max_index).tax_exempt_number := l_tax_exempt_number;
1719 tax_exempt_info_tbl(pg_max_index).bill_to_customer_id := bill_to_customer_id;
1720 tax_exempt_info_tbl(pg_max_index).ship_to_site_use_id := ship_to_site_id;
1721 tax_exempt_info_tbl(pg_max_index).tax_code := tax_code;
1722 tax_exempt_info_tbl(pg_max_index).status_code := 'UNAPPROVED';
1723 tax_exempt_info_tbl(pg_max_index).start_date := l_period_start_date;
1724 tax_exempt_info_tbl(pg_max_index).end_date := NULL;
1725
1726 else
1727
1728 IF PG_DEBUG = 'Y' THEN
1729 arp_util_tax.debug('Inserting into cache. Exemption is not found.');
1730 END IF;
1731 pg_max_index := pg_max_index + 1;
1732 tax_exempt_info_tbl(pg_max_index).percent_exempt := l_percent_exempt;
1733 tax_exempt_info_tbl(pg_max_index).tax_exemption_id := l_exemption_id;
1734 tax_exempt_info_tbl(pg_max_index).tax_exempt_reason_code := l_tax_exempt_reason_code;
1735 tax_exempt_info_tbl(pg_max_index).tax_exempt_number := l_tax_exempt_number;
1736 tax_exempt_info_tbl(pg_max_index).bill_to_customer_id := bill_to_customer_id;
1737 tax_exempt_info_tbl(pg_max_index).ship_to_site_use_id := ship_to_site_id;
1738 tax_exempt_info_tbl(pg_max_index).tax_code := tax_code;
1739 tax_exempt_info_tbl(pg_max_index).status_code := l_status_code;
1740 tax_exempt_info_tbl(pg_max_index).start_date := nvl(l_start_date, trx_date);
1741 tax_exempt_info_tbl(pg_max_index).end_date := l_end_date;
1742
1743 end if; -- tax_exempt_flag = 'E' and .....
1744
1745 else
1746
1747 /* Exemption ID was found in the cursor: sel_customer_exemption */
1748 l_exemption_type := 'CUSTOMER';
1749 l_inserted := FALSE;
1750
1751 IF PG_DEBUG = 'Y' THEN
1752 arp_util_tax.debug('Inserting into cache. Exemption is found by query. ');
1753 END IF;
1754 pg_max_index := pg_max_index + 1;
1755 tax_exempt_info_tbl(pg_max_index).percent_exempt := l_percent_exempt;
1756 tax_exempt_info_tbl(pg_max_index).tax_exemption_id := l_exemption_id;
1757 tax_exempt_info_tbl(pg_max_index).tax_exempt_reason_code := l_tax_exempt_reason_code;
1758 tax_exempt_info_tbl(pg_max_index).tax_exempt_number := l_tax_exempt_number;
1759 tax_exempt_info_tbl(pg_max_index).bill_to_customer_id := bill_to_customer_id;
1760 tax_exempt_info_tbl(pg_max_index).ship_to_site_use_id := ship_to_site_id;
1761 tax_exempt_info_tbl(pg_max_index).tax_code := tax_code;
1762 tax_exempt_info_tbl(pg_max_index).status_code := l_status_code;
1763 tax_exempt_info_tbl(pg_max_index).start_date := l_start_date;
1764 tax_exempt_info_tbl(pg_max_index).end_date := l_end_date;
1765
1766 end if; -- sel_customer_exemption%NOTFOUND
1767
1768 CLOSE sel_customer_exemption;
1769
1770 elsif l_status_code is not null then
1771
1772 /* Exemption is found in cache */
1773 IF PG_DEBUG = 'Y' THEN
1774 arp_util_tax.debug('Found Exemption in cache.');
1775 END IF;
1776
1777 l_exemption_type := 'CUSTOMER';
1778 l_inserted := FALSE;
1779
1780 end if; -- l_bill_to_customer_id is null
1781
1782 end if; -- l_find_customer_exemption
1783
1784
1785 else
1786 IF PG_DEBUG = 'Y' THEN
1787 arp_util_tax.debug( 'I: FIND_TAX_EXEMPTION_ID: ARP_STANDARD.SYSPARM.TAX_USE_CUSTOMER_EXEMPT_FLAG = ' ||
1788 arp_standard.sysparm.tax_use_customer_exempt_flag );
1789 END IF;
1790 end if; -- arp_standard.sysparm.tax_use_customer_exempt_flag
1791
1792
1793 if arp_standard.sysparm.tax_use_product_exempt_flag = 'Y'
1794 and l_exemption_type is null THEN
1795
1796 OPEN sel_item_exemption( inventory_item_id,
1797 tax_code, trx_date );
1798 FETCH sel_item_exemption into
1799 l_percent_exempt, l_exemption_id,
1800 l_tax_exempt_reason_code, l_tax_exempt_number;
1801
1802
1803 IF sel_item_exemption%NOTFOUND
1804 THEN
1805 l_exemption_type := NULL;
1806 l_tax_exempt_reason_code := NULL;
1807 l_tax_exempt_number := NULL;
1808 l_exemption_id := NULL;
1809 l_percent_exempt := 0;
1810 ELSE
1811 l_exemption_type := 'ITEM';
1812 END IF;
1813 CLOSE sel_item_exemption;
1814 else
1815
1816 /* Product Exemptions are not to be checked */
1817
1818
1819 IF l_exemption_type IS NULL
1820 THEN
1821
1822 --
1823 -- Product Exmeptions must be turned off in the system parameters form
1824 --
1825 IF PG_DEBUG = 'Y' THEN
1826 arp_util_tax.debug( 'I: FIND_TAX_EXEMPTION_ID: ARP_STANDARD.TAX_USE_PRODUCT_EXEMPT_FLAG = ' ||
1827 arp_standard.sysparm.TAX_USE_PRODUCT_EXEMPT_FLAG );
1828 END IF;
1829 END IF;
1830
1831 end if; --arp_standard.sysparm.tax_use_product_exempt_flag = 'Y'
1832
1833 percent_exempt := l_percent_exempt;
1834 tax_exemption_id := l_exemption_id;
1835 reason_code := l_tax_exempt_reason_code;
1836 certificate := l_tax_exempt_number;
1837 exemption_type := l_exemption_type;
1838
1839 if l_inserted
1840 then
1841 inserted_flag := 'Y';
1842 else
1843 inserted_flag := 'N';
1844 end if;
1845
1846
1847 if l_exemption_type is not null
1848 then
1849 if l_inserted
1850 then
1851 IF PG_DEBUG = 'Y' THEN
1852 arp_util_tax.debug( '<< FIND_TAX_EXEMPTION_ID( INSERTED, ' || l_exemption_type || ' ' || l_exemption_id
1853 || ', ' || l_percent_exempt ||' )' );
1854 END IF;
1855 else
1856 inserted_flag := 'N';
1857 IF PG_DEBUG = 'Y' THEN
1858 arp_util_tax.debug( '<< FIND_TAX_EXEMPTION_ID( FOUND, ' || l_exemption_type || ', '
1859 || l_exemption_id || ', ' || l_percent_exempt ||' )' );
1860 arp_util_tax.debug( ' Updating In_Use_Flag in ra_tax_exemptions' ) ;
1861 END IF;
1862 -- Bug 3159438: To Update In_Use_Flag in Exemptions
1863 Update ra_tax_exemptions_all
1864 set in_use_flag = 'Y'
1865 where tax_exemption_id = l_exemption_id;
1866 end if;
1867
1868 else
1869
1870 IF PG_DEBUG = 'Y' THEN
1871 arp_util_tax.debug( '<< FIND_TAX_EXEMPTION_ID( NO EXEMPTION )' );
1872 END IF;
1873
1874 end if;
1875
1876
1877 END;
1878
1879
1880 /*------------------------------------------------------------------------+
1881 | PUBLIC PROCEDURE |
1882 | combine_tax_rates |
1883 | |
1884 | CALLED BY package upgrade_sales_tax |
1885 | |
1886 | DESCRIPTION |
1887 | The tax rates will be combined and stored as the sales tax during |
1888 | the upgrade. The rates are taken from the AR_LOCATION_RATES table |
1889 | and combined as the combination is defined in AR_LOCATION_ |
1890 | COMBINATIONS |
1891 | |
1892 | |
1893 | |
1894 | REQUIRES: no arguments |
1895 | |
1896 | MODIFIES: inserts combined rates into AR_SALES_TAX |
1897 | |
1898 +------------------------------------------------------------------------*/
1899
1900 PROCEDURE combine_tax_rates IS
1901 BEGIN
1902 insert into ar_sales_tax(
1903 SALES_TAX_ID,
1904 LAST_UPDATE_DATE,
1905 LAST_UPDATED_BY,
1906 LAST_UPDATE_LOGIN,
1907 CREATED_BY,
1908 CREATION_DATE,
1909 LOCATION_ID,
1910 rate_context,
1911 tax_rate,
1912 LOCATION1_RATE,
1913 LOCATION2_RATE,
1914 LOCATION3_RATE,
1915 from_postal_code,
1916 to_postal_code,
1917 start_date,
1918 end_date,
1919 enabled_flag)
1920 select
1921 AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
1922 sysdate,
1923 arp_standard.profile.user_id,
1924 null,
1925 arp_standard.profile.user_id,
1926 sysdate,
1927 ccid.location_id,
1928 arp_standard.sysparm.location_structure_id,
1929 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
1930 + decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
1931 + decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
1932 decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
1933 decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
1934 decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
1935 greatest( r1.from_postal_code,
1936 r2.from_postal_code,
1937 r3.from_postal_code ),
1938 least( r1.to_postal_code,
1939 r2.to_postal_code,
1940 r3.to_postal_code ),
1941 greatest( r1.start_date ,
1942 r2.start_date ,
1943 r3.start_date ),
1944 least( r1.end_date ,
1945 r2.end_date ,
1946 r3.end_date ),
1947 'Y'
1948 from ar_location_rates r1,
1949 ar_location_rates r2,
1950 ar_location_rates r3,
1951 AR_LOCATION_COMBINATIONS ccid
1952 where ccid.LOCATION_ID_SEGMENT_1 = r1.location_segment_id and
1953 ccid.LOCATION_ID_SEGMENT_2 = r2.location_segment_id and
1954 ccid.LOCATION_ID_SEGMENT_3 = r3.location_segment_id
1955 and greatest( r1.from_postal_code,
1956 r2.from_postal_code,
1957 r3.from_postal_code ) <= least( r1.to_postal_code,
1958 r2.to_postal_code,
1959 r3.to_postal_code )
1960 and greatest( r1.start_date ,
1961 r2.start_date ,
1962 r3.start_date ) <= least( r1.end_date ,
1963 r2.end_date ,
1964 r3.end_date )
1965 and not exists (
1966 select 'x'
1967 from ar_sales_tax tax
1968 where tax.location_id = ccid.location_id
1969 and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
1970 and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
1971 and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
1972 and tax.from_postal_code =
1973 greatest( r1.from_postal_code,
1974 r2.from_postal_code,
1975 r3.from_postal_code )
1976 and tax.to_postal_code =
1977 least( r1.to_postal_code,
1978 r2.to_postal_code,
1979 r3.to_postal_code )
1980 and tax.start_date =
1981 greatest( r1.start_date ,
1982 r2.start_date ,
1983 r3.start_date )
1984 and tax.end_date =
1985 least( r1.end_date ,
1986 r2.end_date ,
1987 r3.end_date )
1988 and tax.enabled_flag = 'Y' );
1989 END;
1990
1991 PROCEDURE populate_segment_array( loc_segment_id in number ) is
1992 begin
1993 ARP_STAX_MINUS99.loc_rate := ARP_STAX_MINUS99.loc_rate + 1;
1994 ARP_STAX_MINUS99.location_segment_id(ARP_STAX_MINUS99.loc_rate) :=
1995 loc_segment_id;
1996 -- PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1997 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1998
1999 end populate_segment_array;
2000
2001 END ARP_STAX_MINUS99;
[Home] [Help]