1 PACKAGE BODY ARP_STAX AS
2 /* $Header: ARPLSTXB.pls 120.8 2005/09/02 02:32:07 sachandr ship $ */
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 compile_error EXCEPTION;
14 PRAGMA EXCEPTION_INIT(compile_error, -6550);
15
16 /*-------------------------------------------------------------------------+
17 | |
18 | PRIVATE VARIABLES |
19 | |
20 +-------------------------------------------------------------------------*/
21
22 TYPE find_tax_exempt_info_rec_type IS RECORD
23 (
24 bill_to_customer_id NUMBER,
25 ship_to_customer_id NUMBER,
26 ship_to_site_id NUMBER,
27 tax_code VARCHAR2(50),
28 inventory_item_id NUMBER,
29 trx_date DATE,
30 tax_exempt_flag VARCHAR2(1),
31 insert_allowed VARCHAR2(10),
32 reason_code VARCHAR2(30),
33 certificate VARCHAR2(80),
34 percent_exempt NUMBER,
35 inserted_flag VARCHAR2(1),
36 tax_exemption_id NUMBER,
37 exemption_type VARCHAR2(30),
38 hash_string VARCHAR2(1000)
39 );
40
41 find_tax_exempt_info_rec find_tax_exempt_info_rec_type;
42
43 TYPE find_tax_exempt_info_tbl_type is TABLE of find_tax_exempt_info_rec_type index by
44 binary_integer;
45
46 find_tax_exempt_info_tbl find_tax_exempt_info_tbl_type;
47
48 pg_max_index BINARY_INTEGER :=0;
49 TABLE_SIZE BINARY_INTEGER := 65636;
50 cached_org_id integer;
51 cached_org_append varchar2(100);
52
53 /* Bugfix887926 */
54 c_get_exempt integer;
55
56 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
57 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
58
59
60 /* private procedures */
61
62 procedure std_other_error( cursor_id in out NOCOPY number,
63 sql_statement in varchar2 ) is
64 stmt_len integer;
65 loop_var integer;
66 begin
67 null;
68 end std_other_error;
69
70 procedure std_compile_error( cursor_id in out NOCOPY number,
71 sql_statement in varchar2 ) is
72 begin
73 null;
74 end std_compile_error;
75
76 /*-------------------------------------------------------------------------+
77 | PUBLIC FUNCTION |
78 | ins_sales_tax |
79 | |
80 | DESCRIPTION |
81 | This function generates a new record in the table: AR_SALES_TAX |
82 | and returns the SALES_TAX_ID of this new record |
83 | |
84 | REQUIRES |
85 | location_id CCID of this Location |
86 | location_structure_id Multiflex structure ID in use |
87 | total_tax_rate sum of all components of the tax rate |
88 | location1_rate Tax rate for segment 1 of the location |
89 | location2_rate Tax rate for segment 2 of the location |
90 | location3_rate Tax rate for segment 3 of the location |
91 | location4_rate Tax rate for segment 4 of the location |
92 | location5_rate Tax rate for segment 5 of the location |
93 | location6_rate Tax rate for segment 6 of the location |
94 | location7_rate Tax rate for segment 7 of the location |
95 | location8_rate Tax rate for segment 8 of the location |
96 | location9_rate Tax rate for segment 9 of the location |
97 | location10_rate Tax rate for segment 10 of the location |
98 | from_postal_code The same location may have multiple rates |
99 | to_postal_code assigned to it, depending upon postal code |
100 | start_date and transaction date. |
101 | end_date |
102 | |
103 | RETURNS |
104 | SALES_TAX_ID of the new record |
105 | |
106 | EXCEPTIONS RAISED |
107 | |
108 | NOTES |
109 | |
110 | EXAMPLE |
111 | |
112 +-------------------------------------------------------------------------*/
113
114
115 FUNCTION ins_sales_tax(
116 location_id in number,
117 location_structure_id in number,
118 total_tax_rate in number,
119 location1_rate in number,
120 location2_rate in number,
121 location3_rate in number,
122 location4_rate in number,
123 location5_rate in number,
124 location6_rate in number,
125 location7_rate in number,
126 location8_rate in number,
127 location9_rate in number,
128 location10_rate in number,
129 from_postal_code in varchar2,
130 to_postal_code in varchar2,
131 start_date in date,
132 end_date in date) return number IS
133
134 sales_tax_id number;
135 c integer;
136 rows_processed integer;
137 statement varchar2(1000);
138
139 BEGIN
140 -- Stubbed out for R12
141 null;
142
143 end ins_sales_tax;
144
145 /*-------------------------------------------------------------------------+
146 | PUBLIC PROCEDURE |
147 | implement_transfer_rates |
148 | |
149 | CALLED BY TRIGGER AR_LOCATION_RATES_ASIU |
150 | |
151 | REQUIRES |
152 | |
153 | PL/SQL table: LOCATION_SEGMENT_ID to be populated with the |
154 | location_segment_id of each row that has changed in the table |
155 | AR_LOCATION_RATES. |
156 | |
157 | The PUBLIC variable: loc_rate is a count of the number of rows in |
158 | the PL/SQL table: location_segment_id that we can expect and is |
159 | maintained by the trigger: AR_LOCATION_RATES_BRIU |
160 | |
161 | DESCRIPTION |
162 | |
163 | Will take each distinct location_segment_id used during updates or |
164 | inserts to the table: AR_LOCATION_RATES and propogate these sales |
165 | tax rate changes into the table: AR_SALES_TAX |
166 | |
167 | This procedures fires the cursor: sel_cc to find each Location Code |
168 | Combination that uses a particular location_segment_id and then |
169 | updates each of the rows in ar_sales_tax for that specific location. |
170 | |
171 | To optimise performance of the code a note of every code combination |
172 | is made so that the same set of records in ar_sales_tax is only ever |
173 | visited once. |
174 | |
175 | EXAMPLE |
176 | |
177 | If the following Code Combinations were active on a system: |
178 | CA.SAN MATEO.BELMONT CCID: 1000, LOCATIONS: 4.6.8 |
179 | CA.SAN MATEO.FOSTER CITY CCID: 1001, LOCATIONS: 4.6.10 |
180 | CA.FREEMONT.FREEMONT CCID: 1002, LOCATIONS: 4.12.14 |
181 | FL.MIAMI.MIAMI CCID: 1003, LOCATIONS: 16.18.20 |
182 | |
183 | And the user updates AR_LOCATION_RATES for ( CA, and SAN MATEO ) |
184 | There would be two rows set up by the TRIGGER: AR_LOCATION_RATES_BRIU |
185 | in the PL/SQL table: location_segment_id, these rows would be: |
186 | location_segment_id(0) = 4 |
187 | location_segment_id(1) = 6 |
188 | This procedure would fire the cursor: sel_cc which for location 4 |
189 | would return: 3 rows, CCIDS: 1000, 10001, and 1002. |
190 | Sales Tax Rate records would then be regenerated for all 3 different |
191 | location code combinations. |
192 | Cursor sel_cc would then be re-fired for location 6 and return the |
193 | following two rows: 1000, 1001. Since both of these rows have already |
194 | been updated no further work is required and the procedure completes. |
195 | |
196 | MODIFICATION HISTORY |
197 | 22-Jan-93 Nigel Smith Created. |
198 | |
199 +-------------------------------------------------------------------------*/
200
201 PROCEDURE Implement_Transfer_Rates is
202 l_transfer_rates_initialised varchar2(5);
203 l_transfer_rates_manual varchar2(5);
204 c integer;
205 rows_processed integer;
206 statement varchar2(1000);
207
208 BEGIN
209 -- Stubbed out for R12
210 null;
211 end implement_transfer_rates;
212
213 /*-------------------------------------------------------------------------+
214 | PUBLIC PROCEDURE |
215 | enable_triggers / disable_triggers |
216 | |
217 | DESCRIPTION |
218 | |
219 | Control the execution of database triggers associated with the sales |
220 | tax functions, enabling or disabling there actions. |
221 | |
222 | This is used to enhance performance of certain batch operations |
223 | such as the Sales Tax Interface programs, when the row by row |
224 | nature of database triggers would degrade performance of the system |
225 | |
226 | MODIFIES |
227 | |
228 | ARP_STAX.triggers_enabled |
229 | |
230 | EXCEPTIONS RAISED |
231 | |
232 | NOTES |
233 | |
234 | EXAMPLE |
235 | |
236 +-------------------------------------------------------------------------*/
237
238 procedure enable_triggers is
239 c integer;
240 rows_processed integer;
241 statement varchar2(1000);
242
243 BEGIN
244
245 -- Stubbed out for R12
246 null;
247 END enable_triggers;
248
249 procedure disable_triggers is
250 c integer;
251 rows_processed integer;
252 statement varchar2(1000);
253
254 BEGIN
255
256 -- Stubbed out for R12
257 null;
258 END disable_triggers;
259
260 /*-------------------------------------------------------------------------+
261 | PUBLIC FUNCTION |
262 | Site_Use_Sales_Tax |
263 | |
264 | CALLED BY TRIGGER RA_SITE_USES_BRIU |
265 | |
266 | DESCRIPTION |
267 | |
268 | Find the location CCID for the address used by this site use, and all |
269 | of the segment id's in use by this locaiton code combination. |
270 | |
271 | Re-Populates AR_SALES_TAX with rate information. |
272 | |
273 | MODIFIES |
274 | |
275 | EXCEPTIONS RAISED |
276 | |
277 | NOTES |
278 | |
279 | EXAMPLE |
280 | |
281 +-------------------------------------------------------------------------*/
282
283
284 PROCEDURE Site_Use_Sales_Tax( address_id in number ) IS
285 c integer;
286 rows_processed integer;
287 statement varchar2(1000);
288
289 BEGIN
290 -- Stubbed out for R12
291 null;
292 end site_use_sales_tax;
293
294 /*-------------------------------------------------------------------------+
295 | PUBLIC FUNCTION |
296 | Initialise_Transfer_Rates |
297 | |
298 | CALLED BY TRIGGER AR_LOCATION_RATES_BSIU |
299 | |
300 | DESCRIPTION |
301 | |
302 | Initialise the public variable: location_rates_transfer_id with the |
303 | value from the sequence: AR_LOCATION_RATES_TRASNFER_S |
304 | |
305 | For each set of inserted or updateed rows in the table AR_LOCATION_RATES|
309 | records across into the table: AR_SALES_TAX |
306 | a distinct value is placed in the column: AR_TRANSFER_CONTROL_ID |
307 | When all records have been inserted or updated, the after statement |
308 | trigger: AR_LOCATION_RATES_ASIU fires, and refetchs each of these new |
310 | |
311 | This works around the kernel limitation of MUTATING tables. |
312 | |
313 | MODIFIES |
314 | Public variable: LOCATION_RATES_TRANSFER_ID |
315 | |
316 | EXCEPTIONS RAISED |
317 | |
318 | NOTES |
319 | |
320 | EXAMPLE |
321 | |
322 +-------------------------------------------------------------------------*/
323
324
325 PROCEDURE Initialise_Transfer_Rates is
326 c integer;
327 rows_processed integer;
328 statement varchar2(1000);
329
330 begin
331
332 -- Stubbed out for R12
333 null;
334 end initialise_transfer_rates;
335
336 /*-------------------------------------------------------------------------+
337 | PUBLIC FUNCTION |
338 | Populate_Sales_Tax |
339 | |
340 | CALLED BY TRIGGER RA_LOCATION_COMBINATIONS_BRIU |
341 | |
342 | DESCRIPTION |
343 | |
344 | Generate records in the table: AR_SALES_TAX for the new location_CCID |
345 | These records will be derived from multiple records in the table |
346 | AR_LOCATION_RATES. |
347 | |
348 | For each enabled segment in the "Tax Location Flexfield", find |
349 | every sales tax rate, zip and date range. Note if two rates |
350 | have different, mutually exclusive date or zip code ranges |
351 | then reject this record. |
352 | |
353 | Example:- |
354 | |
355 | Segment From To Start End Tax |
356 | Value Zip Zip Date Date Rate |
357 | ------------ ----- ------ --------- ---------- ----- |
358 | CA A C 01-JAN-90 31-DEC-90 5 |
359 | CA D Z 01-JAN-90 31-DEC-90 10 |
360 | CA A Z 01-JAN-91 31-JAN-91 12 |
361 | |
362 | San Mateo A Z 01-JAN-91 31-JAN-91 2 |
363 | San Mateo A Z 07-JUL-90 07-JUL-90 0 |
364 | |
365 | Foster City A Z 01-JAN-91 31-JAN-91 1 |
366 | Belmont A Z 01-JAN-90 31-JAN-91 3 |
367 | |
368 | If the following flexfield combinations were created, the |
369 | sales tax rate assignments generated would be:- |
370 | |
371 | Flexfield Combination Rate Start End From To |
372 | Date Date Zip Zip |
373 | ------------------------ ----- --------- --------- ----- ------ |
374 | CA.San Mateo.Foster City 12+2+1 01-jan-91 31-jan-91 A Z |
375 | CA.San Mateo.Belmont 12+2+3 01-jan-91 31-jan-91 A Z |
376 | CA.San Matro.Belmont 5+0+3 07-jul-90 07-jul-90 A C |
377 | CA.San Matro.Belmont 10+0+3 07-jul-90 07-jul-90 D Z |
378 | |
379 | Note. |
380 | |
381 | Because CA has two different tax rates in 1990, the first |
382 | for zip codes ( A-C ), the second for zip codes ( D-Z ) |
383 | it must have two separate entries in the sales tax rates table |
384 | whenever assignments are created for 1990. |
385 | |
386 | Rate assignments for 1990 are not available for Foster City |
387 | because the city component has no tax rate for this date range |
391 | days in 1990, the County of San Mateo only has a valid rate |
388 | even though the State and County both have tax rates available. |
389 | |
390 | Even though CA and Belmont have tax rates available across many |
392 | for the 7th July, 1990 so the combined rate assignment is only |
393 | valid for 07-Jul-1990. |
394 | |
395 | The Same set of restrictions also applies to Zip code ranges. |
396 | |
397 | Location_ID column to the Code Combinations ID applicable to this |
398 | address. |
399 | |
400 | In order to do this, it may be necessary to insert new items into |
401 | the tables: AR_LOCATION_VALUES and AR_LOCATION_COMBINATIONS |
402 | |
403 | REQUIRES |
404 | Location_CCID Location ID for ths entry in Sales Tax |
405 | location_id_segments 1 .. 10, Location_segment_id for each segment |
406 | in the location flexfield structure. |
407 | |
408 | STATEMENT_TYPE |
409 | |
410 | INSERT New Location CCID Created, there will be *NO* |
411 | pre-existing sales tax data for this location |
412 | DELETE A Locatoin Code Combination has been deleted |
413 | purge ununsed sales tax rates. |
414 | UPDATE Existing Data May Exist, some of whic may now |
415 | be invalid. Purge Invalid data, creating new |
416 | valid data in its place. |
417 | EXCEPTIONS RAISED |
418 | |
419 | NOTES |
420 | |
421 | EXAMPLE |
422 | |
423 +-------------------------------------------------------------------------*/
424
425 PROCEDURE Populate_Sales_Tax( statement_type in varchar2,
426 location_ccid in number,
427 p_location_id_segment_1 in number,
428 p_location_id_segment_2 in number,
429 p_location_id_segment_3 in number,
430 p_location_id_segment_4 in number,
431 p_location_id_segment_5 in number,
432 p_location_id_segment_6 in number,
433 p_location_id_segment_7 in number,
434 p_location_id_segment_8 in number,
435 p_location_id_segment_9 in number,
436 p_location_id_segment_10 in number ) is
437 c integer;
438 rows_processed integer;
439 statement varchar2(1000);
440
441 BEGIN
442
443 -- Stubbed out for R12
444 null;
445 end populate_sales_tax;
446
447
448 PROCEDURE propogate_sales_tax IS
449 c integer;
450 rows_processed integer;
451 statement varchar2(1000);
452
453 begin
454
455 -- Stubbed out for R12
456 null;
457 end propogate_sales_tax;
458
459
460 PROCEDURE Purge_Sales_Tax IS
461 c integer;
462 rows_processed integer;
463 statement varchar2(1000);
464
465 begin
466
467 -- Stubbed out for R12
468 null;
469 end purge_sales_tax;
470
471 /*-------------------------------------------------------------------------+
472 | PUBLIC PROCEDURE |
473 | renumber_tax_lines( customer_trx_id in number ) |
474 | |
475 | CALLED BY USER EXIT: #AR SALESTAX MODE=UPDATE |
476 | |
477 | DESCRIPTION |
478 | Will renumber each Tax line belonging to an invoice so that duplicate |
479 | line numbers no longer exist. |
480 | For Example:- if Original Invoice had the following lines, this |
481 | procedure would update each of the line numbers to be:- |
482 | |
483 | OLD INVOICE NEW_INVOICE |
484 | 1 ITEM LINE 1 ITEM LINE |
485 | 1 TAX LINE 1 TAX LINE |
486 | 2 TAX LINE 2 TAX LINE |
487 | 1 TAX LINE 3 TAX LINE |
491 | recalculated. |
488 | |
489 | Duplicate Line numbers can occur when a header level change is made |
490 | for an invoice and all but Adhoc Tax lines are deleted and then |
492 | |
493 | REQUIRES: CUSTOMER_TRX_ID |
494 | |
495 | MODIFIES: RA_CUSTOMER_TRX_LINES.LINE_NUMBER |
496 | |
497 +-------------------------------------------------------------------------*/
498
499 PROCEDURE renumber_tax_lines( customer_trx_id in number,
500 trx_type in varchar2 default 'TAX' ) is
501 c integer;
502 rows_processed integer;
503 statement varchar2(1000);
504
505 begin
506
507 -- Stubbed out for R12
508 null;
509 end renumber_tax_lines;
510
511 FUNCTION FIND_HASH ( TAB_INDEX in out NOCOPY binary_integer, VALUE IN varchar2, nameTable in find_tax_exempt_info_tbl_type) return boolean is
512 HASH_VALUE binary_integer;
513
514 begin
515
516 -- Stubbed out for R12
517 null;
518
519 end FIND_HASH;
520
521 /*------------------------------------------------------------------------+
522 | PUBLIC PROCEDURE |
523 | find_tax_exemption_id |
524 | |
525 | CALLED BY sales tax engine |
526 | |
527 | DESCRIPTION |
528 | Each transaction line may be forced exempt from taxes by the user. |
529 | When exempted; "find_tax_exemption_id" is called, passing in the |
530 | Exemption Certificate Number and Reason Code. If no "Unapproved", |
531 | "Manual" or "Primary" Exemption exists for this Customer, Location, |
532 | Tax Code and Reason this routine will optionally create an Automatic |
533 | exemption with a status of "Unapproved" and a location that matches |
534 | the flexfield qualifier: "EXEMPT_LEVEL". |
535 | |
536 | REQUIRES |
537 | BILL TO_CUSTOMER_ID Bill To Customer ID (mandatory) |
538 | SHIP_TO_CUSTOMER_ID Ship To Customer ID |
539 | SHIP_TO_SITE_ID Identifies the ship to site from which we |
540 | can deduce the State, County and City |
541 | Or other segments applicable to the sales |
542 | tax location flexfield. |
543 | INVENTORY_ITEM_ID Item exemptions are if found, used |
544 | TRX_DATE Tax Date for this transaction |
545 | TAX_CODE Tax Code for this transaction |
546 | TAX_EXEMPT_FLAG "S"tandard; "E"xempt or "R"equire |
547 | REASON_CODE Mandatory for all Exempt transactions |
548 | CERTIFICATE Optional, used in Exempt transactions |
549 | PERCENT_EXEMPT Exemption, Percentage of Tax that is |
550 | Exempt; or NULL if no Exemption is |
551 | applicable. |
552 | INSERT_ALLOWED If False and "E" is called but not |
553 | valid exemption is on file; this routine |
554 | will return an error. |
555 | RETURNS |
556 | TAX_EXEMPTION_ID Foreign Key to "RA_TAX_EXEMPTIONS" |
557 | If NULL, this transaction is NOT exempt |
558 | CERTIFICATE Certificate Number |
559 | REASON Reason Code for exemption |
560 | INSERTED_FLAG TRUE if this call forced an insert |
561 | EXEMPTION_TYPE CUSTOMER or ITEM |
562 | |
563 | DATABASE REQUIREMENTS |
564 | View: TAX_EXEMPTIONS_QP_V This view must be installed before |
565 | this database package can be installed |
566 | |
567 | MODIFICATION HISTORY |
568 | |
569 | 17-May-94 Nigel Smith Created. |
570 | 3 Aug, 94 Nigel Smith BUGFIX: 228807, Exemptions are now |
571 | managed by Bill To Customer and Ship |
572 | To Site. |
573 | 13 Oct 94 Nigel Smith Bugfix: 227953, Exemptions with No |
574 | certificate number were not shown by Order|
575 | Entry. |
579 | exempt. |
576 | 19 Oct 94 Nigel Smith Bugfix: 244306, Error Validating Address |
577 | during order entry; or invoice creation on|
578 | export orders/invoices that are marked as |
580 | |
581 +------------------------------------------------------------------------*/
582
583
584 PROCEDURE find_tax_exemption_id(
585 bill_to_customer_id in number,
586 ship_to_customer_id in number,
587 ship_to_site_id in number,
588 tax_code in varchar2,
589 inventory_item_id in number,
590 trx_date in date,
591 tax_exempt_flag in varchar2,
592 insert_allowed in varchar2 default 'TRUE',
593 reason_code in out NOCOPY varchar2,
594 certificate in out NOCOPY varchar2,
595 percent_exempt out NOCOPY number,
596 inserted_flag out NOCOPY varchar2,
597 tax_exemption_id out NOCOPY number,
598 exemption_type out NOCOPY varchar2
599 ) is
600 dummy_number number;
601 dummy_varchar2 varchar2(2);
602 rows_processed integer;
603 statement varchar2(1000);
604 found_in_cache boolean := FALSE;
605 hash_string varchar2(1000);
606 TABLEIDX binary_integer;
607
608 begin
609 -- Stubbed out for R12
610 null;
611 end find_tax_exemption_id;
612
613 /*------------------------------------------------------------------------+
614 | PUBLIC PROCEDURE |
615 | combine_tax_rates |
616 | |
617 | CALLED BY package upgrade_sales_tax |
618 | |
619 | DESCRIPTION |
620 | The tax rates will be combined and stored as the sales tax during |
621 | the upgrade. The rates are taken from the AR_LOCATION_RATES table |
622 | and combined as the combination is defined in AR_LOCATION_ |
623 | COMBINATIONS |
624 | |
625 | |
626 | |
627 | REQUIRES: no arguments |
628 | |
629 | MODIFIES: inserts combined rates into AR_SALES_TAX |
630 | |
631 +------------------------------------------------------------------------*/
632
633 PROCEDURE combine_tax_rates IS
634 c integer;
635 rows_processed integer;
636 statement varchar2(1000);
637
638 begin
639
640 -- Stubbed out for R12
641 null;
642 end combine_tax_rates;
643
644
645 PROCEDURE populate_segment_array( loc_segment_id in number ) is
646 c integer;
647 rows_processed integer;
648 statement varchar2(1000);
649
650 begin
651
652 -- Stubbed out for R12
653 null;
654 end populate_segment_array;
655
656 /*------------------------------------------------------------------------+
657 | PUBLIC PROCEDURE |
658 | close_open_cusor |
659 | |
660 | CALLED BY package arp_process_tax.calculate_tax_f_sql |
661 | |
662 | DESCRIPTION |
663 | Close all open cursor opened in this file before the end of tax |
664 | calculation. |
665 | |
666 +------------------------------------------------------------------------*/
667 PROCEDURE close_open_cursor IS
668 BEGIN
669 -- Stubbed out for R12
670 null;
671 END close_open_cursor;
672
673 /* global package initialization */
674
675 BEGIN
676 -- Stubbed out for R12
677 null;
678
679 END ARP_STAX;
[Home] [Help]