[Home] [Help]
PACKAGE BODY: APPS.ARP_TAX_INTERFACE
Source
1 PACKAGE BODY ARP_TAX_INTERFACE AS
2 /* $Header: ARPLTXIB.pls 115.17 2003/12/05 02:12:13 sachandr ship $ */
3
4
5 /*---------------------------------------------------------------------------+
6 | |
7 | PRIVATE EXCEPTIONS |
8 | |
9 +---------------------------------------------------------------------------*/
10
11 /*---------------------------------------------------------------------------+
12 | |
13 | PRIVATE DATATYPES |
14 | |
15 +---------------------------------------------------------------------------*/
16
17
18 type tab_id_type is table of number index by binary_integer;
19
20 type tab_value_type is table of varchar2(60) index by binary_integer;
21
22 type tab_date_type is table of date index by binary_integer;
23
24 type tab_rate_type is table of number index by binary_integer;
25
26 type tab_boolean_type is table of boolean index by binary_integer;
27
28 /*---------------------------------------------------------------------------+
29 | |
30 | PRIVATE CONSTANTS |
31 | |
32 +---------------------------------------------------------------------------*/
33
34 /*---------------------------------------------------------------------------+
35 | |
36 | PRIVATE VARIABLES |
37 | |
38 +---------------------------------------------------------------------------*/
39
40
41 qualifier tab_value_type; -- Segment Qualifiers, EG:-
42
43 -- STATE
44 -- COUNTY
45 -- CITY
46
47 segment number; -- Count of number of segments in location
48 -- flexfield structure.
49
50 LOCATION_SEGMENTS varchar2(4096); -- Space separated list of segment qualifiers
51
52 error_count number ;
53
54 max_location_width number ; -- Maximum precission that a name held in the
55 -- interface table has. Only this many
56 -- Characters of the location database will be
57 -- checked before determing if a new row
58 -- needs to be created in ar_location_values
59
60 qual_adjust number; -- Allows optional creation of Country segment
61 -- and rates.
62
63
64 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
65 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
66
67 /*---------------------------------------------------------------------------+
68 | PRIVATE FUNCTION |
69 | ELIMINATE_OVERLAPPED_RANGE |
70 | |
71 | DESCRIPTION |
72 | This function looks for records which are overlapped each other and |
73 | update STATUS column of AR_TAX_INTERFACE with |
74 | IGNORED-NARROWER-ZIP for the record which zip range is narrower than |
75 | the the other. |
76 | |
77 | MODIFICATION HISTORY |
78 | 20-Oct-98 Toru Kawamura Created. |
79 | 05-AUG-02 Satyadeep Bugfix 2377918 |
80 | Chandrashekar |
81 | |-----------| |
82 | |-----| |
83 | Such records in the interface will not |
84 | be marked as ignore-narrower-zip |
85 ----------------------------------------------------------------------------*/
86 PROCEDURE ELIMINATE_OVERLAPPED_RANGE (
87 senior_segment in varchar2 default null,
88 default_start_date in date default to_date('01-01-1900', 'dd-mm-yyyy'))IS
89
90 cursor c_overlap_candidate(senior_segment in varchar2 ) IS
91 select distinct
92 ci.interface_line_id,
93 ci.location_segment_id,
94 ci.segment_qualifier,
95 trunc(nvl(ci.start_date, default_start_date)) start_date,
96 nvl(ci.end_date, arp_standard.max_end_date) end_date,
97 nvl(ci.from_postal_code,arp_standard.sysparm.from_postal_code ) from_postal_code,
98 nvl(ci.to_postal_code,arp_standard.sysparm.to_postal_code ) to_postal_code,
99 ci.rate_type,
100 ci.tax_rate,
101 ci.parent_location_id,
102 ci.location_id,
103 ci.location_value,
104 ci.status
105 from
106 ar_tax_interface ci,
107 ar_tax_interface co,
108 ar_tax_interface st
109 where ci.interface_line_id in (
110 select
111 distinct t1.interface_line_id
112 from
113 ar_tax_interface t1,
114 ar_tax_interface t2
115 where t1.location_value = t2.location_value
116 and t1.segment_qualifier = 'CITY'
117 and t1.parent_location_id = t2.parent_location_id
118 and t1.interface_line_id <> t2.interface_line_id
119 and ( ((( nvl(t1.from_postal_code,arp_standard.sysparm.from_postal_code)
120 >= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
121 and ( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
122 <= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ))
123 or (( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
124 >= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
125 and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
126 <= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) )))
127 and not (( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
128 <= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
129 and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
130 >= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ))
131 and not (( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
132 >= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
133 and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
134 <= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) )) )
135 and ( trunc(nvl(t1.start_date, default_start_date)) = trunc(nvl(t2.start_date, default_start_date)) )
136 and not( ( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
137 = nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code))
138 and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
139 = nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ) )
140 )
141 and st.location_value like nvl(senior_segment,'%')
142 and co.parent_location_id = st.location_id
143 and ci.parent_location_id = co.location_id
144 and ci.status is null
145 order by ci.parent_location_id, ci.location_value, ci.location_id;
146
147 cursor c_zip_overlap(p_interface_line_id in number,
148 p_segment_qualifier in varchar2,
149 p_start_date in date,
150 p_from_postal_code in varchar2,
151 p_to_postal_code in varchar2,
152 p_rate_type in varchar2,
153 p_parent_location_id in number,
154 p_location_value in varchar2) IS
155 select distinct
156 interface_line_id,
157 location_segment_id,
158 segment_qualifier,
159 nvl(start_date, default_start_date) start_date,
160 nvl(end_date, arp_standard.max_end_date) end_date,
161 nvl(from_postal_code, arp_standard.sysparm.from_postal_code ) from_postal_code,
162 nvl(to_postal_code, arp_standard.sysparm.to_postal_code ) to_postal_code,
163 rate_type,
164 tax_rate,
165 parent_location_id,
166 location_value
167 from
168 ar_tax_interface
169 where interface_line_id <> p_interface_line_id
170 and segment_qualifier = p_segment_qualifier
171 and parent_location_id = p_parent_location_id
172 and location_value = p_location_value
173 and rate_type = p_rate_type
174 and status is null
175 and ( ((nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
176 >= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
177 and nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
178 <= nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
179 or (nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
180 >= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
181 and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
182 <= nvl(to_postal_code, arp_standard.sysparm.to_postal_code)))
183 and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
184 >= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
185 and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
186 <= nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
187 and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
188 <= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
189 and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
190 >= nvl(to_postal_code, arp_standard.sysparm.to_postal_code)))
191 and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
192 = nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
193 and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
194 = nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
195 and trunc(nvl(p_start_date,default_start_date)) = trunc(nvl(start_date, default_start_date));
196
197 l_interface_line_id number;
198 l_location_segment_id number;
199 l_segment_qualifier varchar2(30);
200 l_start_date date;
201 l_end_date date;
202 l_from_postal_code varchar2(60);
203 l_to_postal_code varchar2(60);
204 l_rate_type varchar2(30);
205 l_tax_rate number;
206 l_parent_location_id number;
207 l_location_value varchar2(60);
208 l_status varchar2(60);
209 in_clause varchar2(20000);
210 dis_in_clause varchar2(200);
211
212 l_count binary_integer := 0;
213 s_count binary_integer := 0;
214
215 start_pos binary_integer;
216 end_pos binary_integer;
217
218 l_ignore binary_integer;
219 l_cursor binary_integer;
220 sqlstmt varchar2(20000);
221
222 prog_loc binary_integer;/* Indicator to find out where
223 exception is raised */
224 sel_count binary_integer;
225
226 BEGIN
227 if pg_debug='Y' then
228 arp_util_tax.debug('ARP_TAX_INTERFACE.ELIMINATE_OVERLAPPED_RANGE('||
229 senior_segment||':'||to_char(default_start_date)||
230 to_char(sysdate, 'YYYY-MON-DD: HH:MI:SS')||')+');
231 end if;
232
233 prog_loc := 1;
234
235 l_interface_line_id := NULL;
236 l_location_segment_id := NULL;
237 l_segment_qualifier := NULL;
238 l_start_date := NULL;
239 l_end_date := NULL;
240 l_from_postal_code := NULL;
241 l_to_postal_code := NULL;
242 l_rate_type := NULL;
243 l_tax_rate := NULL;
244 l_parent_location_id := NULL;
245 l_location_value := NULL;
246 l_status := NULL;
247
248 for c1 in c_overlap_candidate(senior_segment)
249 loop
250 prog_loc := 2;
251 l_count := l_count + 1;
252
253 l_interface_line_id := c1.interface_line_id;
254 l_location_segment_id := c1.location_segment_id;
255 l_segment_qualifier := c1.segment_qualifier;
256 l_start_date := c1.start_date;
257 l_end_date := c1.end_date;
258 l_from_postal_code := c1.from_postal_code;
259 l_to_postal_code := c1.to_postal_code;
260 l_rate_type := c1.rate_type;
261 l_tax_rate := c1.tax_rate;
262 l_parent_location_id := c1.parent_location_id;
263 l_location_value := c1.location_value;
264
265 if pg_debug='Y' then
266 arp_util_tax.debug(to_char(l_count)||':'||to_char(l_interface_line_id)||
267 ':'||to_char(l_location_segment_id));
268 arp_util_tax.debug('---'||l_segment_qualifier||':'||to_char(l_start_date)||':'||l_from_postal_code);
269 arp_util_tax.debug('---'||l_rate_type||':'||to_char(l_parent_location_id)||':'||l_location_value);
270 end if;
271 s_count := 0;
272 for c2 in c_zip_overlap(c1.interface_line_id,
273 c1.segment_qualifier,
274 c1.start_date,
275 c1.from_postal_code,
276 c1.to_postal_code,
277 c1.rate_type,
278 c1.parent_location_id,
279 c1.location_value)
280 loop
281
282 prog_loc := 3;
283 s_count := s_count + 1;
284
285 if (l_from_postal_code >= c2.from_postal_code) and
286 (l_to_postal_code <= c2.to_postal_code) then
287 if pg_debug='Y' then
288 arp_util_tax.debug('Postal Code current: '||l_from_postal_code||' : '||l_to_postal_code);
289 arp_util_tax.debug(' smaller than ');
290 arp_util_tax.debug('Postal Code in: '||c2.from_postal_code||' : '||c2.to_postal_code);
291 end if;
292 l_interface_line_id := c2.interface_line_id;
293 l_location_segment_id := c2.location_segment_id;
294 l_segment_qualifier := c2.segment_qualifier;
295 l_start_date := c2.start_date;
296 l_end_date := c2.end_date;
297 l_from_postal_code := c2.from_postal_code;
298 l_to_postal_code := c2.to_postal_code;
299 l_rate_type := c2.rate_type;
300 l_tax_rate := c2.tax_rate;
301 l_parent_location_id := c2.parent_location_id;
302 l_location_value := c2.location_value;
303
304 else
305 if pg_debug='Y' then
306 arp_util_tax.debug('Postal Code current: '||l_from_postal_code||' : '||l_to_postal_code);
307 arp_util_tax.debug(' larger than ');
308 arp_util_tax.debug('Postal Code in: '||c2.from_postal_code||' : '||c2.to_postal_code);
309 end if;
310 in_clause := in_clause||','||c2.interface_line_id;
311
312 end if;
313 if pg_debug='Y' then
314 arp_util_tax.debug('************');
315 end if;
316 end loop;
317
318 prog_loc := 4;
319 if pg_debug='Y' then
320 arp_util_tax.debug('Largest Zip Range is : '||l_from_postal_code||' ~~ '||l_to_postal_code);
321 arp_util_tax.debug('Location value is :'||l_location_value);
322 arp_util_tax.debug('Interface_line_id is :'||to_char(l_interface_line_id));
323 arp_util_tax.debug('Small Count is: '||s_count);
324 arp_util_tax.debug('');
325 arp_util_tax.debug('-----------------------------------------------------');
326 arp_util_tax.debug('');
327 end if;
328
329 end loop;
330
331 prog_loc := 5;
332
333 if in_clause is not null then
334
335 in_clause := substrb(in_clause, 2);
336
337 start_pos := 1;
338 end_pos := 200;
339
340 dis_in_clause := substrb(in_clause, start_pos, end_pos);
341 if pg_debug='Y' then
342 arp_util_tax.debug('STATUS column of records with following interface_line_id');
343 arp_util_tax.debug('in ar_tax_interface table, will be updated with IGNORED-NARROWER-ZIP');
344 end if;
345
346 while dis_in_clause is not null loop
347 prog_loc := 6;
348 if pg_debug='Y' then
349 arp_util_tax.debug(dis_in_clause);
350 end if;
351 start_pos:= start_pos + 200;
352 end_pos := end_pos + 200;
353 dis_in_clause := substrb(in_clause, start_pos, end_pos);
354 end loop;
355 if pg_debug='Y' then
356 arp_util_tax.debug('');
357 end if;
358
359 --
360 -- Prepareing Dynamic SQL to update overlapped records.
361 --
362 l_cursor := dbms_sql.open_cursor;
363 prog_loc := 7;
364 sqlstmt := 'update ar_tax_interface set status =' ||''''||
365 'IGNORED-NARROWER-ZIP'||''''||' where interface_line_id in ('||in_clause||')';
366 if pg_debug='Y' then
367 arp_util_tax.debug('Update statement is <><><><><><>');
368 arp_util_tax.debug(substrb(sqlstmt, 1, 250));
369 end if;
370
371 dbms_sql.parse(l_cursor, sqlstmt, dbms_sql.native);
372 prog_loc := 8;
373
374 l_ignore := dbms_sql.execute(l_cursor);
375
376 dbms_sql.close_cursor( l_cursor );
377 prog_loc := 9;
378
379 end if;
380 prog_loc := 10;
381
382 select count(*) into sel_count from ar_tax_interface where status = 'IGNORED-NARROWER-ZIP';
383 if pg_debug='Y' then
384 arp_util_tax.debug('ARP_TAX_INTERFACE.ELIMINATE_OVERLAPPED_RANGE('||': '||
385 to_char(sel_count)||' :'||to_char(sysdate, 'YYYY-MON-DD: HH:MI:SS')||')-');
386 end if;
387 EXCEPTION
388 WHEN OTHERS THEN
389 if pg_debug='Y' then
390 arp_util_tax.debug('Error Occured in ARP_TAX_INTERFACE.ELIMINATE_OVERLAPPED_RANGE'||to_char(prog_loc));
391 arp_util_tax.debug('SQL CODE is :'||to_char(SQLCODE));
392 arp_util_tax.debug('SQL ERRM is :'||SQLERRM);
393 end if;
394
395 if prog_loc in (7, 8) then
396 dbms_sql.close_cursor( l_cursor );
397 end if;
398 END ELIMINATE_OVERLAPPED_RANGE;
399
400 /*---------------------------------------------------------------------------+
401 | PRIVATE FUNCTION |
402 | date_adjust |
403 | |
404 | DESCRIPTION |
405 | Adjust existing or new date ranges so that they do not overlap |
406 | Each date range can start at the beginning of time ( min_start_date ) |
407 | and/or end at the end of time ( max_end_date ). |
408 | Date ranges that have these boundaries are considered adjustable. |
409 | Ranges that continue to overlap when each boundary is fixed have to be |
410 | either replaced or ignored, dependent upon a runtime parameters |
411 | |
412 | Existing or old data is first adjusted, then new data. |
413 | After each adjustment, a check is made to see if the ranges still |
414 | overlap, in this way the minimum around of data changes to make the |
415 | the date ranges compatible. |
416 | |
417 | Adjustment Order |
418 | old_end |
419 | old_start |
420 | new_start |
421 | new_end |
422 | |
423 | REQUIRES |
424 | old_start Existing start date, can also be min_start_date |
425 | old_end Existing end date, can also be max_end_date |
426 | new_start Proposed start date, can also be min_start_date |
427 | new_end Proposed end date, can also be max_end_date |
428 | |
429 | RETURNS |
430 | Date Ranges Each of the parameter values are modifiable |
431 | ACTION |
432 | UPDATE-INSERT Old record updates, new inserted as it stands |
433 | IGNORE Reject new record |
434 | ADJUST New record only needs adjusting |
435 | OVERRIDE New record replace old record |
436 | |
437 | EXCEPTIONS RAISED |
438 | |
439 | NOTES |
440 | |
441 | EXAMPLE |
442 | |
443 | MODIFICATION HISTORY |
444 | 25-Feb-93 Nigel Smith Created. |
445 | |
446 +---------------------------------------------------------------------------*/
447 FUNCTION dates_overlap( old_start in out NOCOPY date,
448 old_end in out NOCOPY date,
449 new_start in out NOCOPY date,
450 new_end in out NOCOPY date ) return BOOLEAN IS
451 begin -- return true if the old and new dates overlap
452
453 if ( old_start between new_start and new_end ) or
454 ( old_end between new_start and new_end ) then
455 return(TRUE);
456 else
457 return(FALSE);
458 end if;
459
460 end;
461
462
463 FUNCTION date_adjust( old_start in out NOCOPY date,
464 old_end in out NOCOPY date,
465 new_start in out NOCOPY date,
466 new_end in out NOCOPY date ) return VARCHAR2 IS
467
468 action varchar2(4096) := NULL;
469
470 BEGIN
471
472 if dates_overlap( old_start, old_end, new_start, new_end ) then
473
474 /*----------------------------------------------------------------------+
475 | Date Ranges Overlap |
476 | |
477 | Attempt to eliminate any overlap by adjusting any NULL values for |
478 | existing, or new date boundaries. |
479 | |
480 +----------------------------------------------------------------------*/
481
482 /*----------------------------------------------------------------------+
483 | Attempt to ADJUST original Dates, first end date, then start date |
484 +----------------------------------------------------------------------*/
485
486 --
487 -- old: |-------------------------->
488 -- new: |------------------------->
489 -- Becomes
490 -- old: |-----------|
491 -- new: |------------------------->
492 -- Action: UPDATE-INSERT
493 --
494 if ( old_end >= trunc(arp_standard.max_end_date) ) and
495 ( trunc(new_start) > arp_standard.min_start_date ) then
496
497 if new_start < old_start then
498
499 /* The new record starts before the existing record, update the new */
500 /* record so that it has an end date */
501
502 new_end := arp_standard.ceil(trunc( old_start -1 ));
503 action := 'INSERT';
504
505 else
506
507 /* The new record, requires that the old record is terminated, before */
508 /* it can be inserted */
509
510 old_end := arp_standard.ceil(new_start - 1);
511 action := 'UPDATE-INSERT';
512
513 end if;
514
515 elsif ( trunc(old_start) = trunc(new_start) ) and
516 ( trunc(old_end) = trunc(new_end) ) then
517
518 action := 'RATE-ADJUST';
519
520 else
521
522 action := 'ALREADY-EXISTS'; -- Used to be ignore; but already exists is a clearer
523 -- Statement of the problem.
524 end if;
525
526 else /* Dates do not overlap, insert new date, as it stands */
527
528 action := 'INSERT';
529
530 end if; /* Do dates overlap? */
531
532 return(action);
533
534 end;
535
536 /*---------------------------------------------------------------------------+
537 | PRIVATE PROCEDURE |
538 | load_segment_values |
539 | |
540 | DESCRIPTION |
541 | |
542 | REQUIRES |
543 | |
544 | RETURNS |
545 | |
546 | EXCEPTIONS RAISED |
547 | |
548 | NOTES |
549 | |
550 | EXAMPLE |
551 | |
552 | MODIFICATION HISTORY |
553 | 25-Feb-93 Nigel Smith Created. |
554 | 01-Feb-94 Martin Johnson Incident 35495. Removed upper for join |
555 | to senior_segment |
556 | 05-Jul-94 Nigel SMith BUGFIX: 224123, uploads overlapping |
557 | location_rate_records. |
558 | 05-OCT-02 Satyadeep Bug 2609220 added parameter |
559 | Chandrashekar p_rate_attribute1 to location_rates_c |
560 | cursor (parameter for geocode) |
561 +---------------------------------------------------------------------------*/
562
563
564 PROCEDURE load_segment_values( change_control in varchar2,
565 default_start_date in date,
566 senior_segment in varchar2,
567 max_error_count in number,
568 commit_on_each_senior_segment in varchar2 ) IS
569
570 cursor interface_c( request_id in number,
571 senior_segment in varchar2 ) IS
572 select DISTINCT
573 i.interface_line_id,
574 i.segment_qualifier,
575 level,
576 i.location_id,
577 upper(i.location_value) location_value,
578 i.location_value location_user_value,
579 nvl(i.location_description, initcap(i.location_value)) location_description,
580 i.parent_location_id,
581 nvl(i.from_postal_code, arp_standard.sysparm.from_postal_code ) from_postal_code,
582 nvl(i.to_postal_code, arp_standard.sysparm.to_postal_code ) to_postal_code,
583 trunc(nvl(i.start_date, default_start_date)) start_date,
584 nvl(i.end_date, arp_standard.max_end_date) end_date,
585 i.tax_rate,
586 i.location_segment_id,
587 i.status,
588 location_attribute_category,
589 location_attribute1,
590 location_attribute2,
591 location_attribute3,
592 location_attribute4,
593 location_attribute5,
594 location_attribute6,
595 location_attribute7,
596 location_attribute8,
597 location_attribute9,
598 location_attribute10,
599 location_attribute11,
600 location_attribute12,
601 location_attribute13,
602 location_attribute14,
603 location_attribute15,
604 rate_attribute_category,
605 rate_attribute1,
606 rate_attribute2,
607 rate_attribute3,
608 rate_attribute4,
609 rate_attribute5,
610 rate_attribute6,
611 rate_attribute7,
612 rate_attribute8,
613 rate_attribute9,
614 rate_attribute10,
615 rate_attribute11,
616 rate_attribute12,
617 rate_attribute13,
618 rate_attribute14,
619 rate_attribute15,
620 decode(i.segment_qualifier, 'CITY',
621 nvl(i.override_structure_id,
622 arp_standard.sysparm.location_structure_id),
623 null) override_structure_id,
624 override_rate1,
625 override_rate2,
626 override_rate3,
627 override_rate4,
628 override_rate5,
629 override_rate6,
630 override_rate7,
631 override_rate8,
632 override_rate9,
633 override_rate10
634 from ar_tax_interface i
635 where i.rate_type = 'SALES'
636 start with parent_location_id is null
637 and i.location_value like nvl(senior_segment,'%')
638 and i.rate_type = 'SALES'
639 and i.status is null
640 connect by prior location_id = parent_location_id
641 and i.rate_type = 'SALES'
642 order by rpad( location_id, 15, '0'), start_date; /*trunc(nvl(i.start_date, default_start_date));*/
643 --BugFix:2170824 Modified the above order by clause,used the alias instead of the
644 --truncate expression.
645 /*** MB skip ... Note the above rpad had not been changed ***/
646 -- Bug 2609220 added parameter p_rate_attribute1
647 cursor location_rates_c( p_location_segment_id in number,
648 p_from_postal_code in varchar2,
649 p_to_postal_code in varchar2,
650 p_start_date in date,
651 P_end_date in date,
652 p_rate_attribute1 in varchar2) IS
653
654 select r.location_rate_id,
655 r.tax_rate,
656 r.from_postal_code,
657 r.to_postal_code,
658 r.start_date,
659 r.end_date,
660 r.override_rate1,
661 r.override_rate2,
662 r.override_rate3,
663 r.override_rate4,
664 r.override_rate5,
665 r.override_rate6,
666 r.override_rate7,
667 r.override_rate8,
668 r.override_rate9,
669 r.override_rate10,
670 r.attribute_category,
671 r.attribute1,
672 r.attribute2,
673 r.attribute3,
674 r.attribute4,
675 r.attribute5,
676 r.attribute6,
677 r.attribute7,
678 r.attribute8,
679 r.attribute9,
680 r.attribute10,
681 r.attribute11,
682 r.attribute12,
683 r.attribute13,
684 r.attribute14,
685 r.attribute15
686 from ar_location_rates r
687 where r.location_segment_id = p_location_segment_id
688 and ( p_from_postal_code between r.from_postal_code and r.to_postal_code
689 or p_to_postal_code between r.from_postal_code and r.to_postal_code
690 or (p_from_postal_code <= r.from_postal_code and
691 p_to_postal_code >= r.to_postal_code))
692 and ( p_start_date between r.start_date and nvl(r.end_date, r.start_date)
693 or p_end_date between r.start_date and nvl(r.end_date, r.start_date)
694 or (p_start_date <= r.start_date and
695 p_end_date >= nvl(r.end_date, p_end_date)))
696 and nvl(r.attribute1, -99) = nvl(p_rate_attribute1, -99)
697 order by from_postal_code, start_date
698 for update of tax_rate, from_postal_code, to_postal_code, start_date, end_date;
699
700 current_level NUMBER;
701 location_id TAB_ID_TYPE;
702 location_value TAB_VALUE_TYPE;
703 location_from_postal_code TAB_VALUE_TYPE;
704 location_to_postal_code TAB_VALUE_TYPE;
705 location_start_date TAB_DATE_TYPE;
706 location_end_date TAB_DATE_TYPE;
707 location_rate TAB_RATE_TYPE;
708 new_location BOOLEAN := FALSE;
709 rate_id NUMBER;
710 action AR_TAX_INTERFACE.STATUS%TYPE;
711 this_location NUMBER(15);
712 error_code number;
713 error_text varchar2(4096);
714
715 begin
716
717 /*** Update STATUS column of AR_TAX_INTERFACE with NARROWER-ZIP-RANGE for the ***/
718 /*** records which are narrower than the other. ***/
719 ELIMINATE_OVERLAPPED_RANGE(senior_segment,
720 default_start_date);
721 current_level := 0;
722
723 /* COUNTRY Segments are not loaded via the interface table */
724 if qualifier( 0 ) = 'COUNTRY' then
725 begin
726
727 /* Defaults the country correctly */
728 location_id( current_level ) := arp_adds.find_location_segment_id(
729 'COUNTRY',
730 nvl( arp_standard.sysparm.default_country, 'US'),
731 nvl( arp_standard.sysparm.default_country, 'US'),
732 null,
733 attribute_category => 'TRIGGER' );
734
735 if arp_adds.location_segment_inserted then
736
737 arp_adds.ins_location_rates( location_id(current_level),
738 arp_standard.sysparm.from_postal_code,
739 arp_standard.sysparm.to_postal_code,
740 arp_standard.min_start_date,
741 arp_standard.max_end_date,
742 0,
743 attribute_category => 'TRIGGER' );
744 end if;
745
746 location_value( current_level ) := nvl( arp_standard.sysparm.default_country, 'US' );
747 qual_adjust := 0;
748
749 end;
750
751 else
752
753 location_value( current_level ) := null;
754 qual_adjust := 1;
755 location_id( current_level ) := null;
756
757 end if;
758
759 location_from_postal_code( current_level ) := arp_standard.sysparm.from_postal_code;
760 location_to_postal_code( current_level ) := arp_standard.sysparm.to_postal_code;
761 location_start_date( current_level ) := arp_standard.min_start_date;
762 location_end_date( current_level ) := arp_standard.max_end_date;
763
764 /*--------------------------------------------------------------------------+
765 | Loop over each record of the tax_interface table, selecting the rows |
766 | in the hierarchy of the location flexfield. |
767 | The Tree Walk Query Ensures that every parent value is read before |
768 | reading any new children. It also ensures that all children for a given |
769 | parent are found next to each other. |
770 | |
771 | Example Data |
772 | Qual LEVEL LOCATION_VALUE ZIP_START ZIP_END TAX_RATE |
773 | -------- ----- ------------------------ ---------- ---------- --------- |
774 | STATE 1 CA 90000 96699 6.25 |
775 | COUNTY 2 SACRAMENTO 1.5 |
776 | CITY 3 ARDEN ARCADE 95825 95825 0 |
777 | |
778 | If any records are orphans, the declared parent does not exist, they |
779 | will not be found by this cursor, and so the status of each will remain |
780 | unchanged. |
781 +--------------------------------------------------------------------------*/
782
783
784 FOR interface in interface_c( arp_standard.profile.request_id, senior_segment )
785 LOOP
786 BEGIN
787 interface.start_date := trunc( interface.start_date );
788 interface.end_date := arp_standard.ceil( interface.end_date );
789
790 if pg_debug='Y' then
791 arp_util_tax.debug( 'INTERFACE: ' || to_char( interface.level, 0)
792 || '.' || interface.location_value || ' '
793 || to_char( interface.start_date, 'DD-MON-YYYY HH24:MI:SS') ||
794 ' .. ' || to_char(interface.end_date, 'DD-MON-YYYY HH24:MI:SS' ) ||
795 ' =' || to_char( interface.tax_rate, '990.00' ) );
796 end if;
797
798 action := 'READ'; -- Initial value of Action
799 error_text := null;
800
801 /*------------------------------------------------------------------------+
802 | Check interface.qualifier and interface.level to ensure that the user |
803 | has not missed a segment in the flexfield structure, take for example: |
804 | The Default installation of COUNTRY.STATE.COUNTY.CITY then:- |
805 | level: 0 records (not loaded from the interface ) should be COUNTRY. |
806 | level: 1 records should be marked as STATE |
807 | level: 2 records should be marked as COUNTY |
808 | level: 3 records should be marked as CITY |
809 | Any records deeper than level 3 should be errored, any qualifiers that |
810 | are found but dont match the expected qualifier for that level should |
811 | raise an error. |
812 *------------------------------------------------------------------------*/
813
814 if interface.level > segment then
815 arp_standard.fnd_message( 'AR_TAXI_LEVEL_TOO_DEEP' );
816 end if;
817
818 if qualifier( interface.level - qual_adjust ) <> interface.segment_qualifier then
819
820 arp_standard.fnd_message( 'AR_TAXI_UNEXPECTED_QUALIFIER',
821 'EXPECTED', qualifier( interface.level - qual_adjust ),
822 'FOUND', interface.segment_qualifier );
823
824 end if;
825
826 if interface.location_segment_id is not null then
827 arp_standard.fnd_message( 'AR_TAXI_LOC_SEG_ID_HAS_VALUE' );
828 end if;
829
830 /*-----------------------------------------------------------------------+
831 | Populate table: AR_LOCATION_VALUES |
832 *-----------------------------------------------------------------------*/
833
834 /*-----------------------------------------------------------------------+
835 | If the segment level has changed, reset the 'old' values kept for |
836 | segments at this level |
837 *-----------------------------------------------------------------------*/
838
839 if interface.level <> current_level then
840 current_level := interface.level;
841 location_id( current_level ) := NULL;
842 location_value( current_level ) := ' '; /* Dummy Value */
843 end if;
844
845 /*-----------------------------------------------------------------------+
846 | Default the from and to postal codes based on the most recent parent |
847 +-----------------------------------------------------------------------*/
848
849 location_from_postal_code( current_level ) :=
850 nvl( interface.from_postal_code, location_from_postal_code( current_level-1 ));
851 interface.from_postal_code := location_from_postal_code( current_level ) ;
852
853 location_to_postal_code( current_level ) :=
854 nvl( interface.to_postal_code, location_to_postal_code( current_level-1 ));
855 interface.to_postal_code := location_to_postal_code( current_level ) ;
856
857 /*-----------------------------------------------------------------------+
858 | Has the location segment value changed since the previous record at |
859 | this level? |
860 *-----------------------------------------------------------------------*/
861
862 IF interface.location_value <> location_value(current_level) THEN
863
864 /*-------------------------------------------------------------------+
865 | We have a new location segment value |
866 +-------------------------------------------------------------------*/
867
868 location_value( current_level ) := interface.location_value;
869
870 /*------------------------------------------------------------------+
871 | Check that the the new location id still contains the parent |
872 | location id in the leading n character positions. |
873 | This is needed to ensure that the select distinct, and order by |
874 | clauses added to interface_c will return the row data in the |
875 | correct order. |
876 +------------------------------------------------------------------*/
877
878 /* MB conversion - substr to substrb, converted because this is
879 basically a cmparsion */
880 if substrb( interface.location_id, 1, lengthb(interface.parent_location_id ) )
881 <> interface.parent_location_id then
882
883 arp_standard.fnd_message( 'AR_TAXI_LOCATION_BAD_PARENT',
884 'LOCATION_ID', interface.location_id,
885 'PARENT_LOCATION_ID', interface.parent_location_id );
886 end if;
887
888 /*------------------------------------------------------------------+
889 | Check the database to see if this value has been identified with |
890 | this parent before, if not insert the new value. |
891 | find_location_segment_id returns with the internal id for this |
892 | segment. |
893 +------------------------------------------------------------------*/
894
895 this_location := null;
896
897 /*----------------------------------------------------------------+
898 | current_level: Parent is segment id at previous level |
899 | Location_Attributes: Upload Descriptive Flexfield Information |
900 | From Tax Interface table |
901 | |
902 | The data in this interface table may not contain all of the |
903 | characters used to define a City, Vertex(tm) for example only |
904 | lists the first 25 characters of a city name. These truncated |
905 | cities can be manually corrected using the Define Location |
906 | values form, after correction this search_precission parameter |
907 | ensures that new versions of the cities tax rate will not be |
908 | assigned to a new (truncated) city but to the existing |
909 | corrected city. |
910 +----------------------------------------------------------------*/
911 location_id( current_level ) := arp_adds.find_location_segment_id(
912 interface.segment_qualifier,
913 interface.location_user_value,
914 interface.location_description,
915 location_id( current_level -1 ),
916 interface.location_attribute_category,
917 interface.location_attribute1,
918 interface.location_attribute2,
919 interface.location_attribute3,
920 interface.location_attribute4,
921 interface.location_attribute5,
922 interface.location_attribute6,
923 interface.location_attribute7,
924 interface.location_attribute8,
925 interface.location_attribute9,
926 interface.location_attribute10,
927 interface.location_attribute11,
928 interface.location_attribute12,
929 interface.location_attribute13,
930 interface.location_attribute14,
931 interface.location_attribute15,
932 search_precission => max_location_width );
933
934
935 this_location := location_id( current_level );
936
937 new_location := arp_adds.location_segment_inserted;
938
939
940 END IF; /* Segment Value has not changed, dont attempt to re-insert it */
941
942 /*-----------------------------------------------------------------------+
943 | Populate table: AR_LOCATION_RATES |
944 *-----------------------------------------------------------------------*/
945
946 if interface.status = 'IGNORED-NARROWER-ZIP' then
947 action := 'IGNORED-NARROWER-ZIP';
948 new_location := FALSE;
949 else
950 action := 'INSERT';
951 end if;
952
953 if new_location then
954
955 if interface.tax_rate is NULL and
956 interface.from_postal_code = arp_standard.sysparm.from_postal_code and
957 interface.to_postal_code = arp_standard.sysparm.to_postal_code then
958 action := 'NEW-LOCATION';
959 else
960 action := 'NEW-LOCATION-INSERT';
961 end if;
962
963 new_location := FALSE;
964
965 else
966
967 /* for each existing location_rate assigned to sales tax see if the
968 record needs updating or inserting */
969 -- Bug 2609220 added parameter rate_attribute1 to cursor
970 for rates in location_rates_c( location_id(current_level),
971 nvl(interface.from_postal_code,arp_standard.sysparm.from_postal_code),
972 nvl(interface.to_postal_code,arp_standard.sysparm.to_postal_code),
973 interface.start_date,
974 interface.end_date,
975 interface.rate_attribute1 )
976
977 loop
978
979 if interface.status = 'IGNORED-NARROWER-ZIP' then
980 action := 'IGNORED-NARROWER-ZIP';
981 exit when true;
982 end if;
983
984 /* Overlapping data Exists */
985
986 if pg_debug='Y' then
987 arp_util_tax.debug( 'OVERLAP: ' || to_char( rates.start_date, 'DD-MON-YYYY' ) ||
988 ' .. ' || to_char( rates.end_date, 'DD-MON-YYYY' ) ||
989 ' =' || to_char( rates.tax_rate, '990.00' ) );
990
991 arp_util_tax.debug('');
992 arp_util_tax.debug('Segment ID: '||to_char(location_id(current_level)));
993 arp_util_tax.debug('Value: '||interface.location_value);
994 arp_util_tax.debug('Postal Code Old: '||
995 nvl(rates.from_postal_code,arp_standard.sysparm.from_postal_code) ||
996 ' - '||nvl(rates.to_postal_code,arp_standard.sysparm.to_postal_code));
997 arp_util_tax.debug('Postal Code New: '||
998 nvl(interface.from_postal_code,arp_standard.sysparm.from_postal_code) ||
999 ' - '||nvl(interface.to_postal_code,arp_standard.sysparm.to_postal_code));
1000 arp_util_tax.debug('Effective Date Old: '||to_char(trunc(rates.start_date))||
1001 ' - '||to_char(trunc(rates.end_date)));
1002 arp_util_tax.debug('Effective Date New: '||to_char(trunc(interface.start_date))||
1003 ' - '||to_char(trunc(interface.end_date)));
1004 end if;
1005
1006 /* Checking Postal Code Range */
1007 IF ( rates.from_postal_code = interface.from_postal_code ) and
1008 ( rates.to_postal_code = interface.to_postal_code ) THEN
1009 --arp_util_tax.debug('Interface postal code range identical to existing one');
1010
1011 /* Checking Date Range */
1012 IF ( trunc(rates.start_date) = trunc(interface.start_date) ) and
1013 ( arp_standard.ceil(rates.end_date) = arp_standard.ceil(interface.end_date) ) THEN
1014
1015 /* Compare Tax Rates */
1016 IF ( rates.tax_rate > interface.tax_rate ) THEN
1017
1018 /* Bugfix 406993: For the same State.County.City.Zip.Date
1019 combination, Upload the highest tax rate as this is the
1020 safest amount. */
1021 ACTION := 'ALREADY-EXISTS';
1022
1023 if pg_debug='Y' then
1024 arp_util_tax.debug('Rate is smaller than existing one');
1025 end if;
1026
1027 ELSE
1028
1029 /* Checking Tax Rate and it's attributes */
1030 IF ( rates.tax_rate = interface.tax_rate ) and
1031 ( nvl(rates.attribute_category,'x') = nvl(interface.rate_attribute_category,'x')) and
1032 ( nvl(rates.attribute1,'x') = nvl(interface.rate_attribute1,'x')) and
1033 ( nvl(rates.attribute2,'x') = nvl(interface.rate_attribute2,'x')) and
1034 ( nvl(rates.attribute3,'x') = nvl(interface.rate_attribute3,'x')) and
1035 ( nvl(rates.attribute4,'x') = nvl(interface.rate_attribute4,'x')) and
1036 ( nvl(rates.attribute5,'x') = nvl(interface.rate_attribute5,'x')) and
1037 ( nvl(rates.attribute6,'x') = nvl(interface.rate_attribute6,'x')) and
1038 ( nvl(rates.attribute7,'x') = nvl(interface.rate_attribute7,'x')) and
1039 ( nvl(rates.attribute8,'x') = nvl(interface.rate_attribute8,'x')) and
1040 ( nvl(rates.attribute9,'x') = nvl(interface.rate_attribute9,'x')) and
1041 ( nvl(rates.attribute10,'x') = nvl(interface.rate_attribute10,'x')) and
1042 ( nvl(rates.attribute11,'x') = nvl(interface.rate_attribute11,'x')) and
1043 ( nvl(rates.attribute12,'x') = nvl(interface.rate_attribute12,'x')) and
1044 ( nvl(rates.attribute13,'x') = nvl(interface.rate_attribute13,'x')) and
1045 ( nvl(rates.attribute14,'x') = nvl(interface.rate_attribute14,'x')) and
1046 ( nvl(rates.attribute15,'x') = nvl(interface.rate_attribute15,'x')) and
1047 ( nvl(rates.override_rate1,-99) = nvl(interface.override_rate1,-99)) and
1048 ( nvl(rates.override_rate2,-99) = nvl(interface.override_rate2,-99)) and
1049 ( nvl(rates.override_rate3,-99) = nvl(interface.override_rate3,-99)) and
1050 ( nvl(rates.override_rate4,-99) = nvl(interface.override_rate4,-99)) and
1051 ( nvl(rates.override_rate5,-99) = nvl(interface.override_rate5,-99)) and
1052 ( nvl(rates.override_rate6,-99) = nvl(interface.override_rate6,-99)) and
1053 ( nvl(rates.override_rate7,-99) = nvl(interface.override_rate7,-99)) and
1054 ( nvl(rates.override_rate8,-99) = nvl(interface.override_rate8,-99)) and
1055 ( nvl(rates.override_rate9,-99) = nvl(interface.override_rate9,-99)) and
1056 ( nvl(rates.override_rate10,-99) = nvl(interface.override_rate10,-99)) THEN
1057
1058 ACTION := 'ALREADY-EXISTS';
1059 if pg_debug='Y' then
1060 arp_util_tax.debug('Everythings is the same');
1061 end if;
1062 ELSE
1063 ACTION := 'RATE-ADJUST';
1064 rates.tax_rate := interface.tax_rate;
1065 if pg_debug='Y' then
1066 arp_util_tax.debug('Rate is bigger than existing one');
1067 end if;
1068 END IF; -- Checking Tax Rate and it's attributes
1069
1070 END IF; -- Compare Tax Rates
1071
1072 ELSE
1073
1074 ACTION := date_adjust( rates.start_date,
1075 rates.end_date,
1076 interface.start_date,
1077 interface.end_date );
1078 if pg_debug='Y' then
1079 arp_util_tax.debug('Date ranges are overlapping');
1080 end if;
1081
1082 END IF; -- Checking Date Range
1083
1084 ELSIF ( ( interface.from_postal_code >= rates.from_postal_code ) and
1085 ( interface.to_postal_code <= rates.to_postal_code ) ) THEN
1086
1087 if pg_debug='Y' then
1088 arp_util_tax.debug('Postal Code Range in interface table is Narrower than existing one.');
1089 end if;
1090
1091 IF trunc(rates.start_date) = trunc(interface.start_date) and
1092 arp_standard.ceil(rates.end_date) = arp_standard.ceil(interface.end_date) THEN
1093 ACTION := 'ZIP-RANGE-UPDATED'; -- logic to end date broader range later in the code
1094 if pg_debug='Y' then
1095 arp_util_tax.debug('Narrower zip range with same geocode and dates same');
1096 end if;
1097
1098 ELSE
1099 ACTION := date_adjust( rates.start_date,
1100 rates.end_date,
1101 interface.start_date,
1102 interface.end_date );
1103 if pg_debug='Y' then
1104 arp_util_tax.debug('Narrower zip range with same geocode and dates different');
1105 end if;
1106
1107
1108 IF ACTION = 'ALREADY-EXISTS' THEN
1109 IF trunc(rates.start_date) = trunc(rates.end_date) and
1110 trunc(rates.start_date) = trunc(interface.start_date) then
1111 interface.start_date := interface.start_date + 1;
1112 action := 'INSERT';
1113 END IF;
1114 END IF;
1115
1116 END IF; -- Checking Date Range
1117
1118 ELSE
1119 if pg_debug='Y' then
1120 arp_util_tax.debug('Postal Code Range in AR_LOCATION_RATES is included in the one in Interface.');
1121 end if;
1122
1123 /* Checking Date Range */
1124 IF trunc(rates.start_date) = trunc(interface.start_date) and
1125 arp_standard.ceil(rates.end_date) = arp_standard.ceil(interface.end_date) THEN
1126 ACTION := 'ZIP-RANGE-UPDATED';
1127
1128 if pg_debug='Y' then
1129 arp_util_tax.debug('Zip ranges are overlapping');
1130 end if;
1131
1132 ELSE
1133 ACTION := date_adjust( rates.start_date,
1134 rates.end_date,
1135 interface.start_date,
1136 interface.end_date );
1137 if pg_debug='Y' then
1138 arp_util_tax.debug('Zip and date ranges are overlapping');
1139 end if;
1140 END IF; -- Checking Date Range
1141 END IF; -- Checking Postal Code Range
1142
1143 if action = 'UPDATE-INSERT' then
1144 if pg_debug='Y' then
1145 arp_util_tax.debug( 'UPDATE old data: ' || rates.location_rate_id || ' ' ||
1146 rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
1147 rates.start_date || '->' || rates.end_date ||
1148 ' = ' || rates.tax_rate );
1149 end if;
1150
1151 /* BUGFIX: 256136, Updating old records should affect the effective date values
1152 of the old record; no other columns should be touched */
1153
1154 update ar_location_rates
1155 set start_date = rates.start_date,
1156 end_date = rates.end_date,
1157 program_id = arp_standard.profile.program_id,
1158 program_application_id = arp_standard.profile.program_application_id,
1159 program_update_date = sysdate,
1160 request_id = arp_standard.profile.request_id,
1161 LAST_UPDATED_BY = arp_standard.profile.user_id,
1162 LAST_UPDATE_DATE = sysdate
1163 where current of location_rates_c;
1164 /* BUGFIX 1965591 : Continue in loop till all overlapping records handled */
1165 --exit when true;
1166
1167 elsif action = 'RATE-ADJUST' then
1168 if pg_debug='Y' then
1169 arp_util_tax.debug( 'UPDATE RATE-ADJUST old data: ' || rates.location_rate_id || ' ' ||
1170 rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
1171 rates.start_date || '->' || rates.end_date ||
1172 ' = ' || rates.tax_rate );
1173 end if;
1174
1175 update ar_location_rates
1176 set from_postal_code = rates.from_postal_code,
1177 to_postal_code = rates.to_postal_code,
1178 start_date = rates.start_date,
1179 end_date = rates.end_date,
1180 tax_rate = rates.tax_rate,
1181 override_rate1 = interface.override_rate1,
1182 override_rate2 = interface.override_rate2,
1183 override_rate3 = interface.override_rate3,
1184 override_rate4 = interface.override_rate4,
1185 override_rate5 = interface.override_rate5,
1186 override_rate6 = interface.override_rate6,
1187 override_rate7 = interface.override_rate7,
1188 override_rate8 = interface.override_rate8,
1189 override_rate9 = interface.override_rate9,
1190 override_rate10 = interface.override_rate10,
1191 program_id = arp_standard.profile.program_id,
1192 program_application_id = arp_standard.profile.program_application_id,
1193 program_update_date = sysdate,
1194 request_id = arp_standard.profile.request_id,
1195 LAST_UPDATED_BY = arp_standard.profile.user_id,
1196 LAST_UPDATE_DATE = sysdate,
1197 attribute_category = interface.rate_attribute_category,
1198 attribute1 = interface.rate_attribute1,
1199 attribute2 = interface.rate_attribute2,
1200 attribute3 = interface.rate_attribute3,
1201 attribute4 = interface.rate_attribute4,
1202 attribute5 = interface.rate_attribute5,
1203 attribute6 = interface.rate_attribute6,
1204 attribute7 = interface.rate_attribute7,
1205 attribute8 = interface.rate_attribute8,
1206 attribute9 = interface.rate_attribute9,
1207 attribute10 = interface.rate_attribute10,
1208 attribute11 = interface.rate_attribute11,
1209 attribute12 = interface.rate_attribute12,
1210 attribute13 = interface.rate_attribute13,
1211 attribute14 = interface.rate_attribute14,
1212 attribute15 = interface.rate_attribute15
1213 where current of location_rates_c;
1214 /* BUGFIX 1965591 : Continue in loop till all overlapping records handled */
1215 --exit when true;
1216 elsif action = 'ZIP-RANGE-UPDATED' then
1217 if pg_debug='Y' then
1218 arp_util_tax.debug( 'ZIP-RANGE-UPDATED old data: ' || rates.location_rate_id || ' ' ||
1219 rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
1220 rates.start_date || '->' || rates.end_date ||
1221 ' = ' || rates.tax_rate );
1222
1223 end if;
1224 update ar_location_rates
1225 set end_date = start_date,
1226 program_id = arp_standard.profile.program_id,
1227 program_application_id = arp_standard.profile.program_application_id,
1228 program_update_date = sysdate,
1229 request_id = arp_standard.profile.request_id,
1230 LAST_UPDATED_BY = arp_standard.profile.user_id,
1231 LAST_UPDATE_DATE = sysdate
1232 where current of location_rates_c;
1233 -- exit when true;
1234
1235 end if;
1236 end loop; /* Overlapping Rates */
1237 end if; /* Check on new location */
1238
1239 if action = 'ZIP-RANGE-UPDATED' then
1240 if (arp_standard.ceil(interface.end_date) = interface.start_date) then
1241 if interface.end_date is not null then
1242 interface.end_date := interface.end_date +1;
1243 end if;
1244 end if;
1245 interface.start_date := interface.start_date +1;
1246 action := 'UPDATE-INSERT';
1247 end if;
1248
1249 /*************************************************************************************
1250 * Final Check on database; this extra loop is required to ensure that the database *
1251 * never gets duplicate or overlapping rows created *
1252 * Bugfix 2377918 narrower overlapping zip ranges will be allowed if geocodes are *
1253 * different *
1254 *************************************************************************************/
1255
1256 if action = 'INSERT' or
1257 action = 'UPDATE-INSERT' or
1258 action = 'NEW-LOCATION-INSERT' then
1259 -- Bug 2609220 added parameter p_rate_attribute1
1260 for rates in location_rates_c( location_id(current_level),
1261 interface.from_postal_code,
1262 interface.to_postal_code,
1263 interface.start_date,
1264 interface.end_date,
1265 interface.rate_attribute1 )
1266 loop
1267 action := 'ALREADY-EXISTS';
1268 exit when true;
1269 end loop;
1270
1271 end if;
1272
1273 if pg_debug='Y' then
1274 arp_util_tax.debug('Action: '||action);
1275 end if;
1276
1277 if action = 'INSERT' or
1278 action = 'UPDATE-INSERT' or
1279 action = 'NEW-LOCATION-INSERT' then
1280
1281 if pg_debug='Y' then
1282 arp_util_tax.debug( 'Row inserted: ' || location_id(current_level) || ' ' ||
1283 interface.from_postal_code || '->' || interface.to_postal_code || ' ' ||
1284 interface.start_date || '->' || interface.end_date );
1285 end if;
1286
1287
1288 /*---------------------------------------------+
1289 | Upload Basic Information about the Rate, |
1290 | A Rate exists within A postal code range |
1291 | and an effectivity date range. |
1292 | Upload Descriptive Flexfield Information |
1293 | From Tax Interface table |
1294 | Upload Rate Information Overrides, supports |
1295 | State and County Override. |
1296 +---------------------------------------------*/
1297
1298 arp_adds.ins_location_rates(
1299 location_id(current_level),
1300 interface.from_postal_code,
1301 interface.to_postal_code,
1302 interface.start_date,
1303 interface.end_date,
1304 interface.tax_rate,
1305 interface.rate_attribute_category,
1306 interface.rate_attribute1,
1307 interface.rate_attribute2,
1308 interface.rate_attribute3,
1309 interface.rate_attribute4,
1310 interface.rate_attribute5,
1311 interface.rate_attribute6,
1312 interface.rate_attribute7,
1313 interface.rate_attribute8,
1314 interface.rate_attribute9,
1315 interface.rate_attribute10,
1316 interface.rate_attribute11,
1317 interface.rate_attribute12,
1318 interface.rate_attribute13,
1319 interface.rate_attribute14,
1320 interface.rate_attribute15,
1321 interface.override_structure_id,
1322 interface.override_rate1,
1323 interface.override_rate2,
1324 interface.override_rate3,
1325 interface.override_rate4,
1326 interface.override_rate5,
1327 interface.override_rate6,
1328 interface.override_rate7,
1329 interface.override_rate8,
1330 interface.override_rate9,
1331 interface.override_rate10 );
1332
1333 if interface.from_postal_code <> arp_standard.sysparm.from_postal_code then
1334
1335 if interface.from_postal_code < location_from_postal_code( current_level -1 ) and
1336 interface.level > 1 then
1337
1338 error_text := arp_standard.fnd_message( arp_standard.md_msg_text,
1339 'AR_TAXI_BAD_FROM_POSTAL_CODE',
1340 'FOUND',
1341 interface.from_postal_code,
1342 'EXPECTED',
1343 location_from_postal_code(current_level-1));
1344 end if;
1345 end if;
1346
1347 if interface.to_postal_code <> arp_standard.sysparm.to_postal_code then
1348
1349 if interface.to_postal_code > location_to_postal_code( current_level -1 ) and
1350 interface.level > 1 then
1351
1352 error_text := arp_standard.fnd_message( arp_standard.md_msg_text,
1353 'AR_TAXI_BAD_TO_POSTAL_CODE',
1354 'FOUND',
1355 interface.to_postal_code,
1356 'EXPECTED',
1357 location_to_postal_code( current_level -1 ));
1358 end if;
1359 end if;
1360
1361 end if;
1362
1363 if pg_debug='Y' then
1364 arp_util_tax.debug( 'ACTION: ' || action || ' Location Segment ID = ' || this_location );
1365 end if;
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 BEGIN
1370 error_code := sqlcode;
1371 error_text := sqlerrm;
1372 if pg_debug='Y' then
1373 arp_util_tax.debug(sqlcode);
1374 arp_util_tax.debug(sqlerrm);
1375 arp_util_tax.debug( 'ERROR: ' || action || ' Location Segment ID = ' || this_location );
1376 end if;
1377
1378 if error_code = arp_standard.ar_error_number then
1379 error_text := arp_standard.fnd_message( arp_standard.md_msg_text );
1380 end if;
1381
1382 ACTION := 'ORA' || to_char( error_code, '09999' );
1383 error_count := error_count + 1;
1384
1385 /*Too Many Extents in table, index or rollback, failed to extend rollback segment, */
1386 if error_code in ( 1562, 1631, 1630, 1632, 1629 ) THEN
1387 /**** MB conversion substr to substrb ***/
1388 arp_standard.fnd_message( 'AR_ALL_SQL_ERROR', 'ROUTINE', 'TAX_INTERFACE',
1389 'ERR_NUMBER', to_char(error_code, '09999' ),
1390 'SQL_ERROR', substrb(error_text,1,60) );
1391 end if;
1392
1393 if error_count > max_error_count then
1394 /*** MB conversion, substr to substrb, Note - This could have ***/
1395 /*** left alone ***/
1396 if upper(substrb(commit_on_each_senior_segment,1,1)) = 'Y' then
1397 commit work;
1398 end if;
1399
1400 /**** MB conversion substr to substrb ***/
1401 arp_standard.fnd_message( 'AR_STAX_TOO_MANY_ERRORS',
1402 'SQLCODE', to_char(error_code, '09999'),
1403 'SQLERRM', substrb(error_text,1,60),
1404 'MAX_ERRORS', to_char(max_error_count, '9999999999')) ;
1405 end if;
1406
1407 END; /* EXCEPTION HANDLER BLOCK */
1408
1409 END; /* EXCEPTION PROTECTED BLOCK */
1410
1411 UPDATE AR_TAX_INTERFACE
1412 SET STATUS = action,
1413 /*** MB conversion substr to substrb ***/
1414 ERROR_MESSAGE = substrb(error_text,1,240),
1415 LOCATION_SEGMENT_ID = this_location,
1416 LAST_UPDATED_BY = arp_standard.profile.user_id,
1417 LAST_UPDATE_DATE = sysdate,
1418 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id,
1419 PROGRAM_ID = arp_standard.profile.program_id,
1420 REQUEST_ID = arp_standard.profile.request_id
1421 WHERE interface_line_id = interface.interface_line_id;
1422
1423 END LOOP; /* For Each line of the Tax Interface Table */
1424
1425 END load_segment_values;
1426
1427
1428 PROCEDURE Upload_Sales_Tax( commit_on_each_senior_segment in varchar2 default 'Y',
1429 change_control in varchar2 default 'N',
1430 default_start_date in date default to_date('01-01-1900', 'dd-mm-yyyy'),
1431 senior_segment in varchar2 default null,
1432 max_error_count in number default 1000
1433 ) is
1434 cursor sel_segments_null( senior_segment in varchar2 ) is
1435 select distinct location_value
1436 from ar_tax_interface
1437 where parent_location_id is null
1438 and rate_type = 'SALES'
1439 and location_value like nvl(senior_segment,location_value)
1440 order by location_value;
1441
1442 cursor sel_segments( senior_segment in varchar2 ) is
1443 select distinct location_value
1444 from ar_tax_interface
1445 where parent_location_id is null
1446 and rate_type = 'SALES'
1447 and location_value like senior_segment
1448 order by location_value;
1449
1450 BEGIN
1451 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1452 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1453
1454 error_count := 0;
1455 if senior_segment is null
1456 then
1457
1458 for segment in sel_segments_null(senior_segment)
1459 loop
1460 load_segment_values( change_control, default_start_date,
1461 segment.location_value, max_error_count, commit_on_each_senior_segment );
1462
1463 /*** MB conversion, substr to substrb, Note - This could have ***/
1464 /*** left alone ***/
1465 if upper(substrb(commit_on_each_senior_segment,1,1)) = 'Y'
1466 then
1467 commit work;
1468 end if;
1469
1470 end loop;
1471
1472 else
1473 for segment in sel_segments(senior_segment)
1474 loop
1475 load_segment_values( change_control, default_start_date,
1476 segment.location_value, max_error_count, commit_on_each_senior_segment );
1477
1478 /*** MB conversion, substr to substrb, Note - This could have ***/
1479 /*** left alone ***/
1480 if upper(substrb(commit_on_each_senior_segment,1,1)) = 'Y'
1481 then
1482 commit work;
1483 end if;
1484
1485 end loop;
1486
1487 end if;
1488
1489 END;
1490
1491 BEGIN /* Initialisation Section */
1492
1493 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
1494 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1495
1496 /*----------------------------------------------------------------------+
1497 | Setup the data structure: qualifier( 1.. max_number_of_segments ) so |
1498 | that each entry in the array contains the qualifier for the segment |
1499 | in that position of the Sales Tax Location Flexfield. |
1500 +----------------------------------------------------------------------*/
1501
1502 /*----------------------------------------------------------------------+
1503 | Get Each Segment Qualifier, in order segment order for the Key |
1504 | flexfield: Sales Tax Location Flexfield. |
1505 | EG: COUNTRY.STATE.COUNTY.CITY |
1506 +----------------------------------------------------------------------*/
1507
1508 LOCATION_SEGMENTS := ltrim(rtrim(
1509 replace(replace(
1510 replace(replace(
1511 arp_flex.expand( arp_flex.location, 'ALL', ' ' ,
1512 '%QUALIFIER%' ),
1513 ' TAX_ACCOUNT',null), 'TAX_ACCOUNT ',null),
1514 ' EXEMPT_LEVEL', NULL), 'EXEMPT_LEVEL ')));
1515
1516 segment := 0;
1517 qualifier( segment ) := null;
1518
1519 while arp_standard.get_next_word( LOCATION_SEGMENTS, qualifier( segment ) )
1520 loop
1521 segment := segment + 1;
1522 qualifier( segment ) := null;
1523 end loop;
1524
1525 /*------------------------------------------------------------------------+
1526 | Determine the Maximum width of incomming City names, this then becomes |
1527 | the precission that is used when find_location_segment_id is called |
1528 | given a Parent ID and Segment Value. |
1529 | BUGFIX: INC: 27093 |
1530 +------------------------------------------------------------------------*/
1531
1532 BEGIN
1533 /*** MB skip, we want the character length ***/
1534 select max(lengthb(location_value))
1535 into max_location_width
1536 from ar_tax_interface;
1537 EXCEPTION
1538 WHEN NO_DATA_FOUND
1539 THEN max_location_width := null;
1540 END;
1541
1542
1543 END ARP_TAX_INTERFACE;