[Home] [Help]
PACKAGE BODY: APPS.ARP_ADDS_MINUS99
Source
1 PACKAGE BODY ARP_ADDS_MINUS99 AS
2 /* $Header: ARPLXLOC.txt 115.5 2004/03/18 16:28:47 rpalani ship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PRIVATE EXCEPTIONS |
7 | |
8 +-------------------------------------------------------------------------*/
9
10 /*-------------------------------------------------------------------------+
11 | |
12 | PRIVATE DATATYPES |
13 | |
14 +-------------------------------------------------------------------------*/
15
16 type location_rates_type is RECORD
17 (
18 location_ccid number,
19 location_id_segment_1 number,
20 location_id_segment_2 number,
21 location_id_segment_3 number,
22 total_tax_rate number,
23 location1_rate number,
24 location2_rate number,
25 location3_rate number,
26 location4_rate number,
27 location5_rate number,
28 location6_rate number,
29 location7_rate number,
30 location8_rate number,
31 location9_rate number,
32 location10_rate number,
33 from_postal_code varchar2(60),
34 to_postal_code varchar2(60),
35 start_date date,
36 end_date date );
37
38 type tab_id_type is table of binary_integer index by binary_integer;
39
40 /*-------------------------------------------------------------------------+
41 | |
42 | PRIVATE VARIABLES |
43 | |
44 +-------------------------------------------------------------------------*/
45
46 NULL_SEGMENT_QUALIFIER varchar2(60) := NULL;
47
48 previous_territory_code fnd_territories.territory_code%TYPE := NULL;
49 previous_territory_short_name VARCHAR2(80) := NULL;
50 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
51 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
52
53 /*-------------------------------------------------------------------------+
54 | PRIVATE CURSOR |
55 | ar_location_combations_s_c |
56 | |
57 | DESCRIPTION |
58 | Return the next value from the sequence AR_LOCATION_COMBINATINOS_S |
59 | |
60 | REQUIRES |
61 | |
62 | RETURNS |
63 | Sequence ID + large constant used for debugging |
64 | |
65 | EXCEPTIONS RAISED |
66 | |
67 | NOTES |
68 | |
69 | EXAMPLE |
70 | |
71 +-------------------------------------------------------------------------*/
72
73
74
75 CURSOR ar_location_combinations_s_c is
76 select ar_location_combinations_s.nextval + arp_standard.sequence_offset
77 from dual;
78
79
80
81
82 /*-------------------------------------------------------------------------+
83 | PRIVATE CURSOR |
84 | ar_location_values_s_c |
85 | |
86 | DESCRIPTION |
87 | Return the next value from the sequence AR_LOCATION_VALUES_S |
88 | |
89 | REQUIRES |
90 | |
91 | RETURNS |
92 | Sequence ID + large constant used for debugging |
93 | |
94 | EXCEPTIONS RAISED |
95 | |
96 | NOTES |
97 | |
98 | EXAMPLE |
99 | |
100 +-------------------------------------------------------------------------*/
101
102
103 CURSOR ar_location_values_s_c IS
104 select ar_location_values_s.nextval + arp_standard.sequence_offset
105 from dual;
106
107
108
109 /*-------------------------------------------------------------------------+
110 | PRIVATE CURSOR |
111 | ar_location_tax_account_c
112 | |
113 | DESCRIPTION |
114 | Return the tax account id from ar_vat_tax where tax type is LOCATION
115 | |
116 | REQUIRES |
117 | |
118 | RETURNS |
119 | location tax code's tax account id
120 | |
121 | EXCEPTIONS RAISED |
122 | |
123 | NOTES |
124 | 19-Jun-00 NIPATEL Modified the cursor to select from multi-org |
125 | table AR_VAT_TAX_ALL using C_ORG_ID for |
126 | Location Flexfield Sharing project. |
127 | EXAMPLE |
128 | |
129 +-------------------------------------------------------------------------*/
130
131
132 CURSOR ar_location_tax_account_c (c_org_id in number) IS
133 select
134 tax_account_id,
135 INTERIM_TAX_CCID,
136 ADJ_CCID,
137 EDISC_CCID,
138 UNEDISC_CCID,
139 FINCHRG_CCID,
140 ADJ_NON_REC_TAX_CCID,
141 EDISC_NON_REC_TAX_CCID,
142 UNEDISC_NON_REC_TAX_CCID,
143 FINCHRG_NON_REC_TAX_CCID
144 from ar_vat_tax_all vat
145 where tax_type='LOCATION'
146 and org_id = c_org_id
147 and trunc(sysdate) between start_date and nvl(end_date, trunc(sysdate));
148
149
150
151 /*-------------------------------------------------------------------------+
152 | PRIVATE CURSOR |
153 | ar_location_accounts_s_c |
154 | |
155 | DESCRIPTION |
156 | Return the next value from the sequence AR_LOCATION_ACCOUNTS_S |
157 | |
158 | REQUIRES |
159 | |
160 | RETURNS |
161 | Sequence ID + large constant used for debugging |
162 | |
163 | EXCEPTIONS RAISED |
164 | |
165 | MODIFICATION HISTORY |
166 | 19-Jun-00 NIPATEL Created for Location Flexfield Sharing project. |
167 | |
168 +-------------------------------------------------------------------------*/
169
170 CURSOR ar_location_accounts_s_c IS
171 select ar_location_accounts_s.nextval + arp_standard.sequence_offset
172 from dual;
173
174
175
176 /*------------------------------------------------------------------------+
177 | PRIVATE CURSOR |
178 | organization_id_c |
179 | |
180 | DESCRIPTION |
181 | Used to select distinct ORG_ID from AR_SYSTEM_PARAMETERS_ALL |
182 | to create one record per ORG_ID for a new location_id in |
183 | AR_LOCATION_ACCOUNTS_ALL |
184 | |
185 | REQUIRES |
186 | |
187 | RETURNS |
188 | Returns distinct ORG_ID from AR_SYSTEM_PARAMETERS_ALL |
189 | |
190 | EXCEPTIONS RAISED |
191 | |
192 | MODIFICATION HISTORY |
193 | 19-Jun-00 NIPATEL Created for Location Flexfield Sharing project. |
194 | |
195 | |
196 +-------------------------------------------------------------------------*/
197
198 -- Organization Ids -3113 and -3114 are seeded in ar_system_parameters
199 -- and we do not want to create records in ar_location_accounts_all table
200 -- for these org_id's. Hence we do not select them in the cursor.
201
202 CURSOR organization_id_c is
203 select nvl(org_id, -99), location_structure_id
204 from ar_system_parameters_all
205 where nvl(org_id, -99) not in (-3113, -3114)
206 and set_of_books_id <> -1;
207
208
209 /*-------------------------------------------------------------------------+
210 | PRIVATE CURSOR |
211 | ar_location_rates_s_c |
212 | |
213 | DESCRIPTION |
214 | Return the next value from the sequence AR_LOCATION_RATES_S |
215 | |
216 | REQUIRES |
217 | |
218 | RETURNS |
219 | Sequence ID + large constant used for debugging |
220 | |
221 | EXCEPTIONS RAISED |
222 | |
223 | NOTES |
224 | |
225 | EXAMPLE |
226 | |
227 +-------------------------------------------------------------------------*/
228
229
230 CURSOR ar_location_rates_s_c IS
231 select ar_location_rates_s.nextval + arp_standard.sequence_offset
232 from dual;
233
234
235 /*-------------------------------------------------------------------------+
236 | PUBLIC FUNCTION |
237 | find_missing_parent_in_loc |
238 | |
239 | DESCRIPTION |
240 | This function Returns a parent value in AR_LOCATION_VALUES for any |
241 | given segment of a location flexfield. This is required because it |
242 | is quite normal not to specifiy the county for any address within the |
243 | United states of America. |
244 | |
245 | First of all, try to find the records without using postal code |
246 | and if this fails because the same child occurs in two different |
247 | different states, then find the record using postal code to restrict |
248 | the states that can be selected. |
249 | |
250 | REQUIRES |
251 | location_segment_qualifier Identifies which segment this is |
252 | value Name of Child segment, eg the city of Belmont |
253 | |
254 | RETURNS |
255 | Segment value for parent, EG County of "San Mateo" |
256 | |
257 | EXCEPTIONS RAISED |
258 | Oracle Error If any given child( city ) has two or more |
259 | parents with different names, and postal codes |
260 | cannot be be used to resolve the naming conflict. |
261 | NO_DATA_FOUND If no parent can be found. |
262 | |
263 | NOTES |
264 | |
265 | EXAMPLE |
266 | |
267 | |
268 | MODIFICATION HISTORY |
269 | 22-Jan-93 Nigel Smith Created. |
270 | 04-Mar-93 Nigel Smith BUGFIX, find_missing_parent_in_loc, |
271 | now cheks parent zip range not child |
272 +-------------------------------------------------------------------------*/
273
274
275 FUNCTION find_missing_parent_in_loc( p_location_segment_qualifier in varchar2,
276 p_value in varchar2,
277 p_postal_code in varchar2 )
278 return varchar2 IS
279
280 parent_value varchar2(60);
281
282 BEGIN
283
284
285 SELECT DISTINCT v1.location_segment_value into parent_value
286 from ar_location_values v2,
287 ar_location_values v1
288 WHERE v1.location_segment_id = v2.parent_segment_id
289 and v2.location_segment_value = rtrim(ltrim(upper(p_value)))
290 and v2.location_segment_qualifier = p_location_segment_qualifier
291 and v1.location_structure_id = arp_standard.sysparm.location_structure_id
292 and v2.location_structure_id = arp_standard.sysparm.location_structure_id;
293
294 RETURN( parent_value );
295
296 EXCEPTION
297
298 WHEN TOO_MANY_ROWS
299 THEN
300 BEGIN
301
302 /*--------------------------------------------------------------------+
303 | There are multiple parents ( counties ) with the same name, we are |
304 | forced to use postal code to distinguish one from another. This |
305 | could have been done intitially, but would have a performance |
306 | impact for the majority of cases |
307 +--------------------------------------------------------------------*/
308
309 if ( p_postal_code is not null )
310 then
311
312 SELECT DISTINCT v1.location_segment_value into parent_value
313 from ar_location_values v2,
314 ar_location_values v1,
315 ar_location_rates r1
316 WHERE v1.location_segment_id = v2.parent_segment_id
317 and v2.location_segment_value = ltrim(rtrim(upper(p_value)))
318 and v2.location_segment_qualifier = p_location_segment_qualifier
319 and v2.location_segment_id = r1.location_segment_id
320 and v1.location_structure_id = arp_standard.sysparm.location_structure_id
321 and v2.location_structure_id = arp_standard.sysparm.location_structure_id
322 and p_postal_code between r1.from_postal_code and r1.to_postal_code
323 and sysdate between r1.start_date and r1.end_date;
324
325 else RAISE NO_DATA_FOUND;
326 end if;
327
328 RETURN( parent_value );
329
330 EXCEPTION
331 WHEN TOO_MANY_ROWS
332 /*--------------------------------------------------------------------+
333 | Postal Codes cannot help us resolve the name conflict, report the |
334 | error back to the user |
335 *--------------------------------------------------------------------*/
336 THEN arp_standard.fnd_message( 'AR_PP_ADDS_TOO_MANY_PARENTS', 'CHILD', rtrim(p_value, ' ') );
337
338 IF PG_DEBUG = 'Y' THEN
339 arp_util_tax.debug('TOO MANY PARENTS FOR THE GIVEN POSTAL CODE');
340 END IF;
341
342 END;
343
344 END find_missing_parent_in_loc;
345
346
347 /*-------------------------------------------------------------------------+
348 | PUBLIC FUNCTION |
349 | find_location_segment_id |
350 | |
351 | DESCRIPTION |
352 | This function Returns a segment value id for any given value of a |
353 | location flexfield segment. |
354 | |
355 | If the given value cannot be found in the table, it is inserted |
356 | and the new location_segment_id is returned from the sequence |
357 | AR_LOCATION_VALUES_S.nextval |
358 | |
359 | REQUIRES |
360 | |
361 | location_segment_qualifier Value set name used of this segment,eg City|
362 | segment_value The value to be found/inserted, eg Belmont |
363 | parent_segment_id The unique ID of the parent that owns this |
364 | segment. |
365 | OPTIONAL |
366 | |
367 | Descriptive Flexfield Attribute_category and Descriptive Flexfield |
368 | information, used if this calls inserts a |
369 | new row in ar_location_values |
370 | |
371 | Search_Precission If an existing locaion could not be found |
372 | and the supplied location is this number of |
373 | characters wide, then reattempt the search |
374 | using just this precission. Allows user to |
375 | manually correct truncated Cities, if the |
376 | data they upload through the interface table |
377 | only supplies the first N character of a city|
378 | name. |
379 | |
380 | RETURNS |
381 | LOCATION_SEGMENT_ID for the value. |
382 | |
383 | EXCEPTIONS RAISED |
384 | |
385 | NOTES |
386 | |
387 | EXAMPLE |
388 | |
389 | MODIFICATION HISTORY |
390 | 22-Jan-93 Nigel Smith Created. |
391 | 17-Feb-93 Nigel Smith Now stores all segment values in uppercase |
392 | comparisons are case independent |
393 | 23-Aug-93 Nigel Smith Added support for Descriptive Flexfields |
394 | used by Tax Interface program |
395 +-------------------------------------------------------------------------*/
396
397
398 FUNCTION
399 find_location_segment_id( location_segment_qualifier in varchar2,
400 segment_value in varchar2,
401 segment_description in varchar2,
402 parent_segment_id in number,
403 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
404 ATTRIBUTE1 in varchar2 default null,
405 ATTRIBUTE2 in varchar2 default null,
406 ATTRIBUTE3 in varchar2 default null,
407 ATTRIBUTE4 in varchar2 default null,
408 ATTRIBUTE5 in varchar2 default null,
409 ATTRIBUTE6 in varchar2 default null,
410 ATTRIBUTE7 in varchar2 default null,
411 ATTRIBUTE8 in varchar2 default null,
412 ATTRIBUTE9 in varchar2 default null,
413 ATTRIBUTE10 in varchar2 default null,
414 ATTRIBUTE11 in varchar2 default null,
415 ATTRIBUTE12 in varchar2 default null,
416 ATTRIBUTE13 in varchar2 default null,
417 ATTRIBUTE14 in varchar2 default null,
418 ATTRIBUTE15 in varchar2 default null,
419 SEARCH_PRECISSION in number default null )
420 return number IS
421
422 /*------------------------------------------------------------------------+
423 | PRIVATE CURSOR |
424 | location_value_given_parent_c |
425 | |
426 | DESCRIPTION |
427 | Check AR_LOCATION_VALUES for existing location_segment_id given |
428 | parent_segment_id and location_segment_value. |
429 | |
430 | WARNING |
431 | DO NOT MODIFY THIS CURSOR ADDING ANY SELECT COLUMNS. |
432 | The select does not perform any table accesses, utilising index |
433 | only access from AR_LOCATION_VALUES_U2. |
434 | |
435 | EXPLAIN PLAN |
436 | OPERATION OPTIONS OBJECT_NAME |
437 | ---------------------- --------------- ----------------------------- |
438 | INDEX RANGE SCAN AR_LOCATION_VALUES_U2 |
439 | |
440 | MODIFICATION HISTORY |
441 | 22-Jan-93 Nigel Smith Created. |
442 +------------------------------------------------------------------------*/
443
444
445 CURSOR location_value_given_parent_c( p_location_segment_qualifier in varchar2,
446 p_segment_value in varchar2,
447 p_parent_segment_id in number) is
448 select location_segment_id
449 from ar_location_values
450 where location_segment_qualifier = p_location_segment_qualifier
451 and location_structure_id = arp_standard.sysparm.location_structure_id
452 and parent_segment_id = p_parent_segment_id
453 and location_segment_value = rtrim(upper(p_segment_value), ' ');
454
455
456 CURSOR location_value_max_width_c( p_location_segment_qualifier in varchar2,
457 p_segment_value in varchar2,
458 p_parent_segment_id in number,
459 p_search_precission in number) is
460 select location_segment_id
461 from ar_location_values
462 where location_segment_qualifier = p_location_segment_qualifier
463 and location_structure_id = arp_standard.sysparm.location_structure_id
464 and parent_segment_id = p_parent_segment_id
465 and substr(location_segment_value,1,p_search_precission)
466 = substr(rtrim(upper(p_segment_value), ' '),1,p_search_precission);
467 /*** MB skip for above substr, because the idea is to compare ***/
468 /*** two strings, so it is ok and the compare length is same ***/
469
470
471 CURSOR location_value_no_parent_c( p_location_segment_qualifier in varchar2,
472 p_segment_value in varchar2 ) IS
473 select location_segment_id
474 from ar_location_values
475 where location_segment_qualifier = p_location_segment_qualifier
476 and location_structure_id = arp_standard.sysparm.location_structure_id
477 and location_segment_value = rtrim(upper(p_segment_value), ' ' );
478
479 location_segment_id number;
480 override number;
481
482 BEGIN
483 IF PG_DEBUG = 'Y' THEN
484 arp_util_tax.debug( '>> FIND_LOCATION_SEGMENT_ID( ' ||
485 location_segment_qualifier || ', ' ||
486 segment_value || ', ' ||
487 segment_description || ', ' ||
488 parent_segment_id || ' ) ' );
489 END IF;
490
491 location_segment_inserted := FALSE;
492
493 IF segment_value is not null
494 THEN
495 BEGIN
496 IF parent_segment_id is null
497 THEN
498 /*----------------------------------------------------------------------+
499 | This is the first segment of the location flexfield and as such has |
500 | no parent. Open a cursor pasing in just the value set name and |
501 | segment value. |
502 *----------------------------------------------------------------------*/
503 OPEN location_value_no_parent_c( location_segment_qualifier, ltrim(rtrim(segment_value)) );
504 FETCH location_value_no_parent_c into location_segment_id;
505 IF location_value_no_parent_c%NOTFOUND
506 THEN
507 CLOSE location_value_no_parent_c;
508 GOTO location_not_found;
509 END IF;
510 CLOSE location_value_no_parent_c;
511 ELSE
512 /*---------------------------------------------------------------------+
513 | This is a dependent segment, and as such find a location value with |
514 | the named parent. |
515 +---------------------------------------------------------------------*/
516
517 OPEN location_value_given_parent_c( location_segment_qualifier,
518 ltrim(rtrim(segment_value)),
519 parent_segment_id );
520
521 FETCH location_value_given_parent_c into location_segment_id;
522 IF location_value_given_parent_c%NOTFOUND
523 THEN
524 CLOSE location_value_given_parent_c;
525 goto location_not_found;
526 END IF;
527 CLOSE location_value_given_parent_c;
528 END IF;
529 IF PG_DEBUG = 'Y' THEN
530 arp_util_tax.debug( '<< FIND_LOCATION_SEGMENT_ID: EXISTING ROW '
531 || to_char(location_segment_id) );
532 END IF;
533
534 return( location_segment_id );
535
536 END;
537 ELSE
538
539 /*----------------------------------------------------------------------------+
540 | Oracle Receivables has found a null value for a segment when this location |
541 | has nevever been used before. It is impossible to deduce what should be |
542 | the correct value to use. |
543 +----------------------------------------------------------------------------*/
544
545 /*----------------------------------------------------------------------------+
546 | Normal operation requires that an exception be raised if this package |
547 | cannot guess the correct value of any missing segment |
548 *----------------------------------------------------------------------------*/
549
550 NULL_SEGMENT_QUALIFIER := LOCATION_SEGMENT_QUALIFIER;
551 RAISE LOCATION_SEGMENT_NULL_VALUE;
552
553 END IF;
554
555 <<location_not_found>>
556
557 -- EXCEPTIONS are not used even though initial design called for them.
558 -- Attempting to illeminate end-of-file error on communication channel
559 -- returned from RDBMS.
560
561 /*-------------------------------------------------------------------------+
562 | Use only the first n characters of the existing location_value database |
563 | and re-attempt to find the location using this search precission. |
564 +-------------------------------------------------------------------------*/
565
566 -- Check if the width of this location is equal to the search precission
567 -- if any was specificed, if so re-attempt this search using just the
568 -- first nn character in ar_location_values.location_segment_value
569 -- BUGFIX: INC: 27093
570
571 /*** MB skip, we want to see if the character length of segment_value ***/
572 /*** >= search_precission ***/
573 IF length( segment_value ) >= search_precission
574 and parent_segment_id is not null
575 THEN
576 BEGIN
577
578 OPEN location_value_max_width_c( location_segment_qualifier,
579 ltrim(rtrim(segment_value)),
580 parent_segment_id,
581 search_precission );
582
583 FETCH location_value_max_width_c into location_segment_id;
584 IF location_value_max_width_c%NOTFOUND
585 THEN
586 CLOSE location_value_max_width_c;
587 goto insert_new_location;
588 END IF;
589 CLOSE location_value_max_width_c;
590 IF PG_DEBUG = 'Y' THEN
591 arp_util_tax.debug( '<< FIND_LOCATION_SEGMENT_ID: EXISTING ROW(PRECISSION) '
592 || to_char(location_segment_id) );
593 END IF;
594 return( location_segment_id );
595
596 END;
597 END IF;
598
599 <<insert_new_location>>
600
601 /*-----------------------------------------------------------------------+
602 | This value has never been used before, insert it into the value sets |
603 | table and return the unique id assoicated with this value |
604 +-----------------------------------------------------------------------*/
605
606 location_segment_inserted := TRUE;
607 location_segment_id := ins_location_values( location_segment_qualifier,
608 ltrim(rtrim(segment_value)),
609 segment_description,
610 parent_segment_id,
611 attribute_category,
612 attribute1,
613 attribute2,
614 attribute3,
615 attribute4,
616 attribute5,
617 attribute6,
618 attribute7,
619 attribute8,
620 attribute9,
621 attribute10,
622 attribute11,
623 attribute12,
624 attribute13,
625 attribute14,
626 attribute15 );
627
628
629 if ARP_ADDS_MINUS99.populate_location_rates
630 then
631
632 /*--------------------------------------------------------------------+
633 | If this flag is set, then new location_value records automatically |
634 | get a rate record assigned to them. |
635 +--------------------------------------------------------------------*/
636
637 /*--------------------------------------------------+
638 | CITY SEGMENTS MUST HAVE AN OVERRIDE STRUCTURE ID |
639 | COLUMN ASSIGNED TO THEM. |
640 +--------------------------------------------------*/
641
642 if location_segment_qualifier = 'CITY'
643 then
644 override := arp_standard.sysparm.location_structure_id;
645 else
646 override := null;
647 end if;
648
649 ins_location_rates( location_segment_id,
650 arp_standard.sysparm.from_postal_code,
651 arp_standard.sysparm.to_postal_code,
652 arp_standard.min_start_date,
653 arp_standard.max_end_date,
654 0,
655 'TRIGGER',
656 attribute1,
657 attribute2,
658 attribute3,
659 attribute4,
660 attribute5,
661 attribute6,
662 attribute7,
663 attribute8,
664 attribute9,
665 attribute10,
666 attribute11,
667 attribute12,
668 attribute13,
669 attribute14,
670 attribute15,
671 OVERRIDE_STRUCTURE_ID => override
672 );
673 end if;
674
675 IF PG_DEBUG = 'Y' THEN
676 arp_util_tax.debug( '<< FIND_LOCATION_SEGMENT_ID: NEW ROW ' || to_char(location_segment_id) );
677 END IF;
678
679 return( location_segment_id );
680
681 END find_location_segment_id;
682
683
684
685 /*-------------------------------------------------------------------------+
686 | PUBLIC PROCEDURE |
687 | location_information |
688 | |
689 | DESCRIPTION |
690 | This function Returns a location description given either: |
691 | locaiton_segment_id, or |
692 | locaiton_segment_qualifier and location_segment_value |
693 | |
694 | REQUIRES |
695 | location_segment_qualifier Value set name used on this segment,eg City|
696 | segment_value The value to be found/inserted, eg Belmont |
697 | or: |
698 | location_segment_id Unique id for this location |
699 | |
700 | RETURNS |
701 | location_segment_id |
702 | location_segment_value |
703 | location_segment_description |
704 | location_segment_qualifier |
705 | parent_segment_id |
706 | |
707 | EXCEPTIONS RAISED |
708 | |
709 | NOTES |
710 | |
711 | EXAMPLE |
712 | |
713 | MODIFICATION HISTORY |
714 | 17-JUN-93 Nigel Smith Created. |
715 +-------------------------------------------------------------------------*/
716
717
718 procedure location_information( location_segment_id in number,
719 location_segment_qualifier out NOCOPY varchar2,
720 location_segment_value out NOCOPY varchar2,
721 location_segment_description out NOCOPY varchar2,
722 parent_segment_id out NOCOPY number ) is
723
724
725 begin
726
727 select location_segment_value,
728 location_segment_description,
729 location_segment_qualifier,
730 parent_segment_id
731 into location_segment_value,
732 location_segment_description,
733 location_segment_qualifier,
734 parent_segment_id
735 from ar_location_values
736 where location_segment_id = location_information.location_segment_id;
737
738 end;
739
740
741 procedure location_information( location_segment_qualifier in varchar2,
742 location_segment_value in varchar2,
743 location_segment_description out NOCOPY varchar2,
744 parent_segment_id out NOCOPY number ) is
745
746
747 begin
748
749 select location_segment_description,
750 parent_segment_id
751 into location_segment_description,
752 parent_segment_id
753 from ar_location_values v1
754 where v1.location_segment_qualifier = location_information.location_segment_qualifier
755 and v1.location_segment_value = ltrim(rtrim(upper(location_information.location_segment_value)))
756 and v1.location_structure_id = arp_standard.sysparm.location_structure_id;
757
758 EXCEPTION
759 WHEN NO_DATA_FOUND
760 THEN
761 BEGIN
762 select max(location_segment_description), max(parent_segment_id)
763 into location_segment_description, parent_segment_id
764 from ar_location_values v1
765 where v1.location_segment_qualifier = location_information.location_segment_qualifier
766 and v1.location_structure_id = arp_standard.sysparm.location_structure_id
767 and v1.location_segment_value = ltrim(rtrim(location_information.location_segment_value));
768 END;
769
770 end;
771
772
773 function location_description( location_segment_qualifier in varchar2,
774 location_segment_value in varchar2 )
775 return varchar2 is
776
777
778 location_segment_description varchar2(60);
779 parent_segment_id number;
780 location_segment_id number;
781
782 begin
783
784 location_information( location_segment_qualifier, location_segment_value,
785 location_segment_description,
786 location_segment_id );
787
788 return( location_segment_description );
789
790 end;
791
792
793
794 function location_description( location_segment_id in number )
795 return varchar2 is
796
797 location_segment_description varchar2(60);
798 parent_segment_id number;
799 location_segment_value varchar2(60);
800 location_segment_qualifier varchar2(30);
801
802 begin
803
804 location_information( location_segment_id,
805 location_segment_qualifier,
806 location_segment_value,
807 location_segment_description,
808 parent_segment_id );
809
810 return( location_segment_description );
811
812 end;
813
814
815
816 /*-------------------------------------------------------------------------+
817 | PUBLIC PROCEDURE |
818 | enable_triggers / disable_triggers |
819 | |
820 | DESCRIPTION |
821 | |
822 | Control the execution of database triggers associated with the |
823 | customer address functions, disabling or enabling there actions. |
824 | |
825 | This is used to enhance performance of certain batch operations |
826 | such as the Sales Tax Interface programs, when the row by row |
827 | nature of database triggers would degrade performance of the system |
828 | |
829 | MODIFIES |
830 | |
831 | adds.triggers_enabled |
832 | |
833 | EXCEPTIONS RAISED |
834 | |
835 | NOTES |
836 | |
837 | EXAMPLE |
838 | |
839 +-------------------------------------------------------------------------*/
840
841 procedure enable_triggers is
842 begin
843 triggers_enabled := TRUE;
844 end;
845
846 procedure disable_triggers is
847 begin
848 triggers_enabled := FALSE;
849 end;
850
851
852
853
854 /*-------------------------------------------------------------------------+
855 | PUBLIC FUNCTION |
856 | ins_location_values |
857 | |
858 | DESCRIPTION |
859 | This function generates a new record in the table: AR_LOCATION_VALUES |
860 | and returns the LOCATION_SEGMENT_ID of this new record |
861 | |
862 | REQUIRES |
863 | location_segment_qualifier Value set name used of this segment |
864 | segment_value The value to be inserted, eg Belmont |
865 | parent_segment_id The unique ID of the parent that owns this |
866 | segment. |
867 | |
868 | RETURNS |
869 | LOCATION_SEGMENT_ID for the value. |
870 | |
871 | EXCEPTIONS RAISED |
872 | |
873 | NOTES |
874 | |
875 | EXAMPLE |
876 | |
877 +-------------------------------------------------------------------------*/
878
879
880
881 FUNCTION ins_location_values( location_segment_qualifier in varchar2,
882 segment_value in varchar2,
883 segment_description in varchar2,
884 parent_segment_id in varchar2,
885 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
886 ATTRIBUTE1 in varchar2 default null,
887 ATTRIBUTE2 in varchar2 default null,
888 ATTRIBUTE3 in varchar2 default null,
889 ATTRIBUTE4 in varchar2 default null,
890 ATTRIBUTE5 in varchar2 default null,
891 ATTRIBUTE6 in varchar2 default null,
892 ATTRIBUTE7 in varchar2 default null,
893 ATTRIBUTE8 in varchar2 default null,
894 ATTRIBUTE9 in varchar2 default null,
895 ATTRIBUTE10 in varchar2 default null,
896 ATTRIBUTE11 in varchar2 default null,
897 ATTRIBUTE12 in varchar2 default null,
898 ATTRIBUTE13 in varchar2 default null,
899 ATTRIBUTE14 in varchar2 default null,
900 ATTRIBUTE15 in varchar2 default null
901 ) return number IS
902
903 location_id number;
904 l_organization_id number;
905 location_value_account_id number;
906 location_segment_value ar_location_values.location_segment_value%TYPE;
907 location_segment_user_value ar_location_values.location_segment_user_value%TYPE;
908 location_segment_description ar_location_values.location_segment_description%TYPE;
909
910 BEGIN
911 IF PG_DEBUG = 'Y' THEN
912 arp_util_tax.debug( '>> INS_LOCATION_VALUES( ' ||
913 location_segment_qualifier || ', ' ||
914 segment_value || ', ' ||
915 segment_description || ', ' ||
916 parent_segment_id || ' ) ' );
917 END IF;
918 location_segment_value := ltrim(rtrim(upper( segment_value )));
919 location_segment_user_value := ltrim(rtrim( segment_value ));
920 location_segment_description := initcap( segment_description );
921
922 OPEN ar_location_values_s_c;
923 FETCH ar_location_values_s_c into location_id;
924 CLOSE ar_location_values_s_c;
925
926 insert into ar_location_values( location_structure_id,
927 location_segment_qualifier,
928 location_segment_id,
929 location_segment_value,
930 location_segment_user_value,
931 location_segment_description,
932 parent_segment_id,
933 request_id,
934 program_application_id,
935 program_id,
936 program_update_date,
937 created_by,
938 creation_date,
939 last_updated_by,
940 last_update_date,
941 last_update_login,
942 attribute_category,
943 attribute1,
944 attribute2,
945 attribute3,
946 attribute4,
947 attribute5,
948 attribute6,
949 attribute7,
950 attribute8,
951 attribute9,
952 attribute10,
953 attribute11,
954 attribute12,
955 attribute13,
956 attribute14,
957 attribute15 )
958 VALUES ( arp_standard.sysparm.location_structure_id,
959 location_segment_qualifier,
960 location_id,
961 location_segment_value,
962 location_segment_user_value,
963 location_segment_description,
964 parent_segment_id,
965 arp_standard.PROFILE.request_id,
966 arp_standard.PROFILE.program_application_id,
967 arp_standard.PROFILE.program_id,
968 sysdate,
969 arp_standard.profile.user_id,
970 sysdate,
971 arp_standard.profile.user_id,
972 sysdate,
973 arp_standard.PROFILE.last_update_login,
974 attribute_category,
975 attribute1,
976 attribute2,
977 attribute3,
978 attribute4,
979 attribute5,
980 attribute6,
981 attribute7,
982 attribute8,
983 attribute9,
984 attribute10,
985 attribute11,
986 attribute12,
987 attribute13,
988 attribute14,
989 attribute15 );
990
991 -- Insert accounting information into ar_location_accounts_all
992 -- One record will be inserted for each Org_id.
993
994 ins_location_accounts
995 ( location_id,
996 location_segment_qualifier);
997 IF PG_DEBUG = 'Y' THEN
998 arp_util_tax.debug( '<< INS_LOCATION_VALUES: ' || to_char(location_id) );
999 END IF;
1000 return( location_id );
1001
1002 END ins_location_values;
1003
1004
1005 /*-------------------------------------------------------------------------+
1006 | PUBLIC PROCEDURE |
1007 | ins_location_accounts |
1008 | |
1009 | DESCRIPTION |
1010 | This procedure generates new records in the table: |
1011 | AR_LOCATION_ACCOUNTS. One record is created for each ORG_ID |
1012 | so as to Accounting information in this table is Organization |
1013 | independent and so that location structure can be shared across |
1014 | Organizations. |
1015 | |
1016 | REQUIRES |
1017 | location_segment_qualifier Value set name used of this segment |
1018 | location_segment_id Foreign Key to AR_LOCATION_VALUES |
1019 | |
1020 | EXCEPTIONS RAISED |
1021 | |
1022 | MODIFICATION HISTORY |
1023 | 19-Jun-00 NIPATEL Created for Location Flexfield Sharing project. |
1024 | |
1025 | EXAMPLE |
1026 | |
1027 +-------------------------------------------------------------------------*/
1028
1029 PROCEDURE ins_location_accounts
1030 ( location_segment_id in number,
1031 location_segment_qualifier in varchar2)
1032 IS
1033
1034 l_location_value_account_id number;
1035 location_tax_account number;
1036 l_INTERIM_TAX_CCID NUMBER;
1037 l_ADJ_CCID NUMBER;
1038 l_EDISC_CCID NUMBER;
1039 l_UNEDISC_CCID NUMBER;
1040 l_FINCHRG_CCID NUMBER;
1041 l_ADJ_NON_REC_TAX_CCID NUMBER;
1042 l_EDISC_NON_REC_TAX_CCID NUMBER;
1043 l_UNEDISC_NON_REC_TAX_CCID NUMBER;
1044 l_FINCHRG_NON_REC_TAX_CCID NUMBER;
1045
1046 type num_tab is table of number index by binary_integer;
1047 type date_tab is table of date index by binary_integer;
1048 org_id_tab num_tab;
1049 loc_structure_id_tab num_tab;
1050
1051 location_account_id_tab num_tab;
1052 location_segment_id_tab num_tab;
1053 tax_account_ccid_tab num_tab;
1054 interim_tax_ccid_tab num_tab;
1055 adj_ccid_tab num_tab;
1056 edisc_ccid_tab num_tab;
1057 unedisc_ccid_tab num_tab;
1058 finchrg_ccid_tab num_tab;
1059 adj_non_rec_tax_ccid_tab num_tab;
1060 edisc_non_rec_tax_ccid_tab num_tab;
1061 unedisc_non_rec_tax_ccid_tab num_tab;
1062 finchrg_non_rec_tax_ccid_tab num_tab;
1063 created_by_tab num_tab;
1064 creation_date_tab date_tab;
1065 last_updated_by_tab num_tab;
1066 last_update_date_tab date_tab;
1067 request_id_tab num_tab;
1068 program_application_id_tab num_tab;
1069 program_id_tab num_tab;
1070 program_update_date_tab date_tab;
1071 last_update_login_tab num_tab;
1072 organization_id_tab num_tab;
1073
1074 BEGIN
1075 IF PG_DEBUG = 'Y' THEN
1076 arp_util_tax.debug( '>> INS_LOCATION_ACCOUNTS( ' ||
1077 location_segment_qualifier ||', '||
1078 to_char(Location_segment_id)||' ) ');
1079 END IF;
1080 -- Records will be inserted into AR_LOCATION_ACCOUNTS_ALL only if
1081 -- the segment has qualifier 'Tax Account' enabled.
1082
1083 if location_segment_qualifier = 'STATE'
1084 then
1085
1086 org_id_tab.delete;
1087 loc_structure_id_tab.delete;
1088
1089 location_account_id_tab.delete;
1090 location_segment_id_tab.delete;
1091 tax_account_ccid_tab.delete;
1092 interim_tax_ccid_tab.delete;
1093 adj_ccid_tab.delete;
1094 edisc_ccid_tab.delete;
1095 unedisc_ccid_tab.delete;
1096 finchrg_ccid_tab.delete;
1097 adj_non_rec_tax_ccid_tab.delete;
1098 edisc_non_rec_tax_ccid_tab.delete;
1099 unedisc_non_rec_tax_ccid_tab.delete;
1100 finchrg_non_rec_tax_ccid_tab.delete;
1101 created_by_tab.delete;
1102 creation_date_tab.delete;
1103 last_updated_by_tab.delete;
1104 last_update_date_tab.delete;
1105 request_id_tab.delete;
1106 program_application_id_tab.delete;
1107 program_id_tab.delete;
1108 program_update_date_tab.delete;
1109 last_update_login_tab.delete;
1110 organization_id_tab.delete;
1111
1112
1113 open organization_id_c ;
1114 fetch organization_id_c bulk collect into
1115 org_id_tab, loc_structure_id_tab;
1116 close organization_id_c ;
1117
1118 -- Insert records into ar_location_accounts_all
1119 for I in 1..org_id_tab.last loop
1120
1121 location_tax_account := NULL;
1122 l_INTERIM_TAX_CCID := NULL;
1123 l_ADJ_CCID := NULL;
1124 l_EDISC_CCID := NULL;
1125 l_UNEDISC_CCID := NULL;
1126 l_FINCHRG_CCID := NULL;
1127 l_ADJ_NON_REC_TAX_CCID := NULL;
1128 l_EDISC_NON_REC_TAX_CCID := NULL;
1129 l_UNEDISC_NON_REC_TAX_CCID := NULL;
1130 l_FINCHRG_NON_REC_TAX_CCID := NULL;
1131
1132 OPEN ar_location_tax_account_c(org_id_tab(I));
1133 FETCH ar_location_tax_account_c into
1134 location_tax_account,
1135 l_INTERIM_TAX_CCID,
1136 l_ADJ_CCID,
1137 l_EDISC_CCID,
1138 l_UNEDISC_CCID,
1139 l_FINCHRG_CCID,
1140 l_ADJ_NON_REC_TAX_CCID,
1141 l_EDISC_NON_REC_TAX_CCID,
1142 l_UNEDISC_NON_REC_TAX_CCID,
1143 l_FINCHRG_NON_REC_TAX_CCID;
1144
1145 if ar_location_tax_account_c%NOTFOUND
1146 then
1147 location_tax_account:=arp_standard.sysparm.location_tax_account;
1148 end if;
1149 CLOSE ar_location_tax_account_c;
1150
1151
1152 OPEN ar_location_accounts_s_c;
1153 FETCH ar_location_accounts_s_c into
1154 l_location_value_account_id;
1155 CLOSE ar_location_accounts_s_c;
1156
1157 location_account_id_tab(i) := l_location_value_account_id;
1158 location_segment_id_tab(i) := location_segment_id;
1159 tax_account_ccid_tab(i) := location_tax_account;
1160 interim_tax_ccid_tab(i) := l_INTERIM_TAX_CCID;
1161 adj_ccid_tab(i) := l_ADJ_CCID;
1162 edisc_ccid_tab(i) := l_EDISC_CCID;
1163 unedisc_ccid_tab(i) := l_UNEDISC_CCID;
1164 finchrg_ccid_tab(i) := l_FINCHRG_CCID;
1165 adj_non_rec_tax_ccid_tab(i) := l_ADJ_NON_REC_TAX_CCID;
1166 edisc_non_rec_tax_ccid_tab(i) := l_EDISC_NON_REC_TAX_CCID;
1167 unedisc_non_rec_tax_ccid_tab(i) := l_UNEDISC_NON_REC_TAX_CCID;
1168 finchrg_non_rec_tax_ccid_tab(i) := l_FINCHRG_NON_REC_TAX_CCID;
1169 created_by_tab(i) := arp_standard.profile.user_id;
1170 creation_date_tab(i) := sysdate;
1171 last_updated_by_tab(i) := arp_standard.profile.user_id;
1172 last_update_date_tab(i) := sysdate;
1173 request_id_tab(i) := arp_standard.PROFILE.request_id;
1174 program_application_id_tab(i) :=
1175 arp_standard.PROFILE.program_application_id;
1176 program_id_tab(i) := arp_standard.PROFILE.program_id;
1177 program_update_date_tab(i) := sysdate;
1178 last_update_login_tab(i) := arp_standard.PROFILE.last_update_login;
1179 organization_id_tab(i) := org_id_tab(I);
1180
1181 end loop;
1182
1183 forall I in 1.. organization_id_tab.last
1184 insert into ar_location_accounts_all
1185 ( location_value_account_id,
1186 location_segment_id,
1187 tax_account_ccid,
1188 interim_tax_ccid,
1189 adj_ccid,
1190 edisc_ccid,
1191 unedisc_ccid,
1192 finchrg_ccid,
1193 adj_non_rec_tax_ccid,
1194 edisc_non_rec_tax_ccid,
1195 unedisc_non_rec_tax_ccid,
1196 finchrg_non_rec_tax_ccid,
1197 created_by,
1198 creation_date,
1199 last_updated_by,
1200 last_update_date,
1201 request_id,
1202 program_application_id,
1203 program_id,
1204 program_update_date,
1205 last_update_login,
1206 org_id)
1207 VALUES
1208 ( location_account_id_tab(i),
1209 location_segment_id_tab(i),
1210 tax_account_ccid_tab(i),
1211 interim_tax_ccid_tab(i),
1212 adj_ccid_tab(i),
1213 edisc_ccid_tab(i),
1214 unedisc_ccid_tab(i),
1215 finchrg_ccid_tab(i),
1216 adj_non_rec_tax_ccid_tab(i),
1217 edisc_non_rec_tax_ccid_tab(i),
1218 unedisc_non_rec_tax_ccid_tab(i),
1219 finchrg_non_rec_tax_ccid_tab(i),
1220 created_by_tab(i),
1221 creation_date_tab(i),
1222 last_updated_by_tab(i),
1223 last_update_date_tab(i),
1224 request_id_tab(i),
1225 program_application_id_tab(i),
1226 program_id_tab(i),
1227 program_update_date_tab(i),
1228 last_update_login_tab(i),
1229 organization_id_tab(i) );
1230
1231 end if; -- location_segment_qualifier = 'AR_TAX_ACCOUNT_SEGMENT'
1232 IF PG_DEBUG = 'Y' THEN
1233 arp_util_tax.debug( '<< INS_LOCATION_ACCOUNTS: ' ||
1234 to_char(l_location_value_account_id) );
1235 END IF;
1236
1237 Exception
1238 when others then
1239 IF PG_DEBUG = 'Y' THEN
1240 arp_util_tax.debug(' Exception in ARP_ADDS.ins_location_Accounts '||
1241 SQLCODE||' ; '||SQLERRM );
1242 END IF;
1243
1244 if organization_id_c%isopen then
1245 close organization_id_c;
1246 end if;
1247
1248 if ar_location_tax_account_c%isopen then
1249 close ar_location_tax_account_c;
1250 end if;
1251
1252 if ar_location_accounts_s_c%isopen then
1253 close ar_location_accounts_s_c;
1254 end if;
1255
1256 End ins_location_accounts;
1257
1258
1259 /*-------------------------------------------------------------------------+
1260 | PUBLIC FUNCTION |
1261 | ins_location_rates |
1262 | |
1263 | DESCRIPTION |
1264 | This function generates a new record in the table: AR_LOCATION_RATES |
1265 | and returns the LOCATION_RATE_ID of this new record |
1266 | |
1267 | REQUIRES |
1268 | |
1269 | RETURNS |
1270 | LOCATION_RATE_ID for the value. |
1271 | |
1272 | EXCEPTIONS RAISED |
1273 | |
1274 | NOTES |
1275 | |
1276 | EXAMPLE |
1277 | |
1278 +-------------------------------------------------------------------------*/
1279
1280
1281
1282 FUNCTION ins_location_rates( location_segment_id in number,
1283 from_postal_code in varchar2,
1284 to_postal_code in varchar2,
1285 start_date in date,
1286 end_date in date,
1287 tax_rate in number,
1288 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
1289 ATTRIBUTE1 in varchar2 default null,
1290 ATTRIBUTE2 in varchar2 default null,
1291 ATTRIBUTE3 in varchar2 default null,
1292 ATTRIBUTE4 in varchar2 default null,
1293 ATTRIBUTE5 in varchar2 default null,
1294 ATTRIBUTE6 in varchar2 default null,
1295 ATTRIBUTE7 in varchar2 default null,
1296 ATTRIBUTE8 in varchar2 default null,
1297 ATTRIBUTE9 in varchar2 default null,
1298 ATTRIBUTE10 in varchar2 default null,
1299 ATTRIBUTE11 in varchar2 default null,
1300 ATTRIBUTE12 in varchar2 default null,
1301 ATTRIBUTE13 in varchar2 default null,
1302 ATTRIBUTE14 in varchar2 default null,
1303 ATTRIBUTE15 in varchar2 default null,
1304 OVERRIDE_STRUCTURE_ID in NUMBER default null,
1305 OVERRIDE_RATE1 in NUMBER default null,
1306 OVERRIDE_RATE2 in NUMBER default null,
1307 OVERRIDE_RATE3 in NUMBER default null,
1308 OVERRIDE_RATE4 in NUMBER default null,
1309 OVERRIDE_RATE5 in NUMBER default null,
1310 OVERRIDE_RATE6 in NUMBER default null,
1311 OVERRIDE_RATE7 in NUMBER default null,
1312 OVERRIDE_RATE8 in NUMBER default null,
1313 OVERRIDE_RATE9 in NUMBER default null,
1314 OVERRIDE_RATE10 in NUMBER default null
1315 ) return number is
1316
1317 location_rate_id number;
1318
1319 BEGIN
1320
1321 IF PG_DEBUG = 'Y' THEN
1322 arp_util_tax.debug( '>> INS_LOCATION_RATES( ' ||
1323 location_segment_id || ', ' ||
1324 from_postal_code || ', ' ||
1325 to_postal_code || ', ' ||
1326 start_date || ', ' ||
1327 end_date || ', ' ||
1328 tax_rate || ' ) ' );
1329 END IF;
1330
1331 if location_segment_id is null
1332 then
1333 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'LOCATION_SEGMENT_ID' );
1334 IF PG_DEBUG = 'Y' THEN
1335 arp_util_tax.debug('Location segment id is null');
1336 END IF;
1337 end if;
1338 if from_postal_code is null
1339 then
1340 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'FROM_POSTAL_CODE' );
1341
1342 IF PG_DEBUG = 'Y' THEN
1343 arp_util_tax.debug('From postal code is null');
1344 END IF;
1345
1346 end if;
1347 if to_postal_code is null
1348 then
1349 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'TO_POSTAL_CODE' );
1350
1351 IF PG_DEBUG = 'Y' THEN
1352 arp_util_tax.debug('To postal code is null');
1353 END IF;
1354
1355 end if;
1356 if start_date is null
1357 then
1358 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'START_DATE' );
1359
1360 IF PG_DEBUG = 'Y' THEN
1361 arp_util_tax.debug('start date is null');
1362 END IF;
1363
1364 end if;
1365 if end_date is null
1366 then
1367 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'END_DATE' );
1368
1369 IF PG_DEBUG = 'Y' THEN
1370 arp_util_tax.debug('End date is null');
1371 END IF;
1372
1373 end if;
1374
1375 OPEN ar_location_rates_s_c;
1376 FETCH ar_location_rates_s_c into location_rate_id;
1377 CLOSE ar_location_rates_s_c;
1378
1379 insert into ar_location_rates( location_rate_id,
1380 location_segment_id,
1381 from_postal_code,
1382 to_postal_code,
1383 start_date,
1384 end_date,
1385 tax_rate,
1386 request_id,
1387 program_application_id,
1388 program_id,
1389 program_update_date,
1390 created_by,
1391 creation_date,
1392 last_updated_by,
1393 last_update_date,
1394 last_update_login,
1395 attribute_category,
1396 attribute1,
1397 attribute2,
1398 attribute3,
1399 attribute4,
1400 attribute5,
1401 attribute6,
1402 attribute7,
1403 attribute8,
1404 attribute9,
1405 attribute10,
1406 attribute11,
1407 attribute12,
1408 attribute13,
1409 attribute14,
1410 attribute15,
1411 override_structure_id,
1412 override_rate1,
1413 override_rate2,
1414 override_rate3,
1415 override_rate4,
1416 override_rate5,
1417 override_rate6,
1418 override_rate7,
1419 override_rate8,
1420 override_rate9,
1421 override_rate10 )
1422 VALUES ( location_rate_id,
1423 location_segment_id,
1424 from_postal_code,
1425 to_postal_code,
1426 start_date,
1427 end_date,
1428 tax_rate,
1429 arp_standard.PROFILE.request_id,
1430 arp_standard.PROFILE.program_application_id,
1431 arp_standard.PROFILE.program_id,
1432 sysdate,
1433 arp_standard.profile.user_id,
1434 sysdate,
1435 arp_standard.profile.user_id,
1436 sysdate,
1437 arp_standard.PROFILE.last_update_login,
1438 attribute_category,
1439 attribute1,
1440 attribute2,
1441 attribute3,
1442 attribute4,
1443 attribute5,
1444 attribute6,
1445 attribute7,
1446 attribute8,
1447 attribute9,
1448 attribute10,
1449 attribute11,
1450 attribute12,
1451 attribute13,
1452 attribute14,
1453 attribute15,
1454 override_structure_id,
1455 override_rate1,
1456 override_rate2,
1457 override_rate3,
1458 override_rate4,
1459 override_rate5,
1460 override_rate6,
1461 override_rate7,
1462 override_rate8,
1463 override_rate9,
1464 override_rate10 );
1465
1466 IF PG_DEBUG = 'Y' THEN
1467 arp_util_tax.debug( '<< INS_LOCATION_RATES: ' || to_char(location_rate_id) );
1468 END IF;
1469
1470 return( location_rate_id );
1471
1472 END ins_location_rates;
1473
1474 PROCEDURE ins_location_rates( location_segment_id in number,
1475 from_postal_code in varchar2,
1476 to_postal_code in varchar2,
1477 start_date in date,
1478 end_date in date,
1479 tax_rate in number,
1480 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
1481 ATTRIBUTE1 in varchar2 default null,
1482 ATTRIBUTE2 in varchar2 default null,
1483 ATTRIBUTE3 in varchar2 default null,
1484 ATTRIBUTE4 in varchar2 default null,
1485 ATTRIBUTE5 in varchar2 default null,
1486 ATTRIBUTE6 in varchar2 default null,
1487 ATTRIBUTE7 in varchar2 default null,
1488 ATTRIBUTE8 in varchar2 default null,
1489 ATTRIBUTE9 in varchar2 default null,
1490 ATTRIBUTE10 in varchar2 default null,
1491 ATTRIBUTE11 in varchar2 default null,
1492 ATTRIBUTE12 in varchar2 default null,
1493 ATTRIBUTE13 in varchar2 default null,
1494 ATTRIBUTE14 in varchar2 default null,
1495 ATTRIBUTE15 in varchar2 default null,
1496 OVERRIDE_STRUCTURE_ID in NUMBER default null,
1497 OVERRIDE_RATE1 in NUMBER default null,
1498 OVERRIDE_RATE2 in NUMBER default null,
1499 OVERRIDE_RATE3 in NUMBER default null,
1500 OVERRIDE_RATE4 in NUMBER default null,
1501 OVERRIDE_RATE5 in NUMBER default null,
1502 OVERRIDE_RATE6 in NUMBER default null,
1503 OVERRIDE_RATE7 in NUMBER default null,
1504 OVERRIDE_RATE8 in NUMBER default null,
1505 OVERRIDE_RATE9 in NUMBER default null,
1506 OVERRIDE_RATE10 in NUMBER default null) is
1507
1508 BEGIN
1509 IF PG_DEBUG = 'Y' THEN
1510 arp_util_tax.debug( '>> INS_LOCATION_RATES( ' ||
1511 location_segment_id || ', ' ||
1512 from_postal_code || ', ' ||
1513 to_postal_code || ', ' ||
1514 start_date || ', ' ||
1515 end_date || ', ' ||
1516 tax_rate || ' ) ' );
1517 END IF;
1518
1519 if location_segment_id is null
1520 then
1521 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'LOCATION_SEGMENT_ID' );
1522
1523 IF PG_DEBUG = 'Y' THEN
1524 arp_util_tax.debug('Location segment id is null');
1525 END IF;
1526
1527 end if;
1528 if from_postal_code is null
1529 then
1530 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'FROM_POSTAL_CODE' );
1531 IF PG_DEBUG = 'Y' THEN
1532 arp_util_tax.debug('From postal code is null');
1533 END IF;
1534
1535 end if;
1536 if to_postal_code is null
1537 then
1538 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'TO_POSTAL_CODE' );
1539
1540 IF PG_DEBUG = 'Y' THEN
1541 arp_util_tax.debug('To postal code is null');
1542 END IF;
1543
1544 end if;
1545 if start_date is null
1546 then
1547 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'START_DATE' );
1548 IF PG_DEBUG = 'Y' THEN
1549 arp_util_tax.debug('start date is null');
1550 END IF;
1551
1552 end if;
1553 if end_date is null
1554 then
1555 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'ARP_ADDS_MINUS99INS_LOCATION_RATES', 'PARAMETER', 'END_DATE' );
1556
1557 IF PG_DEBUG = 'Y' THEN
1558 arp_util_tax.debug('End date is null');
1559 END IF;
1560 end if;
1561
1562 insert into ar_location_rates( location_rate_id,
1563 location_segment_id,
1564 from_postal_code,
1565 to_postal_code,
1566 start_date,
1567 end_date,
1568 tax_rate,
1569 request_id,
1570 program_application_id,
1571 program_id,
1572 program_update_date,
1573 created_by,
1574 creation_date,
1575 last_updated_by,
1576 last_update_date,
1577 last_update_login,
1578 attribute_category,
1579 attribute1,
1580 attribute2,
1581 attribute3,
1582 attribute4,
1583 attribute5,
1584 attribute6,
1585 attribute7,
1586 attribute8,
1587 attribute9,
1588 attribute10,
1589 attribute11,
1590 attribute12,
1591 attribute13,
1592 attribute14,
1593 attribute15,
1594 override_structure_id,
1595 override_rate1,
1596 override_rate2,
1597 override_rate3,
1598 override_rate4,
1599 override_rate5,
1600 override_rate6,
1601 override_rate7,
1602 override_rate8,
1603 override_rate9,
1604 override_rate10 )
1605 VALUES ( ar_location_rates_s.nextval + arp_standard.sequence_offset,
1606 location_segment_id,
1607 from_postal_code,
1608 to_postal_code,
1609 start_date,
1610 end_date,
1611 tax_rate,
1612 arp_standard.PROFILE.request_id,
1613 arp_standard.PROFILE.program_application_id,
1614 arp_standard.PROFILE.program_id,
1615 sysdate,
1616 arp_standard.profile.user_id,
1617 sysdate,
1618 arp_standard.profile.user_id,
1619 sysdate,
1620 arp_standard.PROFILE.last_update_login,
1621 attribute_category,
1622 attribute1,
1623 attribute2,
1624 attribute3,
1625 attribute4,
1626 attribute5,
1627 attribute6,
1628 attribute7,
1629 attribute8,
1630 attribute9,
1631 attribute10,
1632 attribute11,
1633 attribute12,
1634 attribute13,
1635 attribute14,
1636 attribute15,
1637 override_structure_id,
1638 override_rate1,
1639 override_rate2,
1640 override_rate3,
1641 override_rate4,
1642 override_rate5,
1643 override_rate6,
1644 override_rate7,
1645 override_rate8,
1646 override_rate9,
1647 override_rate10 );
1648 IF PG_DEBUG = 'Y' THEN
1649 arp_util_tax.debug( '<< INS_LOCATION_RATES' );
1650 END IF;
1651 END ins_location_rates; /* Procedure */
1652
1653
1654
1655
1656
1657
1658 /*-------------------------------------------------------------------------+
1659 | PUBLIC FUNCTION |
1660 | ins_location_combinations |
1661 | |
1662 | DESCRIPTION |
1663 | This function generates a new record in the table: |
1664 | AR_LOCATON_COMBINATIONS and returns the location code combinations id |
1665 | for this new record. |
1666 | |
1667 | REQUIRES |
1668 | location_structure_id Multiflex structure ID in use |
1669 | start_date_active Date at which Code combination becomes active |
1670 | end_date_active Date at which Code combinatino becomes inactive|
1671 | location_id_segment_1 Location_segment_id for segment 1 or null |
1672 | location_id_segment_2 Location_segment_id for segment 2 or null |
1673 | location_id_segment_3 Location_segment_id for segment 3 or null |
1674 | location_id_segment_4 Location_segment_id for segment 4 or null |
1675 | location_id_segment_5 Location_segment_id for segment 5 or null |
1676 | location_id_segment_6 Location_segment_id for segment 6 or null |
1677 | location_id_segment_7 Location_segment_id for segment 7 or null |
1678 | location_id_segment_8 Location_segment_id for segment 8 or null |
1679 | location_id_segment_9 Location_segment_id for segment 9 or null |
1680 | location_id_segment_10 Location_segment_id for segment 10 or null |
1681 | |
1682 | RETURNS |
1683 | LOCATION_CODE_COMBINATION_ID of the new record. |
1684 | |
1685 | EXCEPTIONS RAISED |
1686 | |
1687 | NOTES |
1688 | |
1689 | EXAMPLE |
1690 | |
1691 +-------------------------------------------------------------------------*/
1692
1693
1694 FUNCTION ins_location_combinations( LOCATION_STRUCTURE_ID NUMBER,
1695 START_DATE_ACTIVE DATE,
1696 END_DATE_ACTIVE DATE,
1697 LOCATION_ID_SEGMENT_1 NUMBER,
1698 LOCATION_ID_SEGMENT_2 NUMBER,
1699 LOCATION_ID_SEGMENT_3 NUMBER,
1700 LOCATION_ID_SEGMENT_4 NUMBER,
1701 LOCATION_ID_SEGMENT_5 NUMBER,
1702 LOCATION_ID_SEGMENT_6 NUMBER,
1703 LOCATION_ID_SEGMENT_7 NUMBER,
1704 LOCATION_ID_SEGMENT_8 NUMBER,
1705 LOCATION_ID_SEGMENT_9 NUMBER,
1706 LOCATION_ID_SEGMENT_10 NUMBER,
1707 ENABLED_FLAG varchar2 )
1708 return number IS
1709 location_id number;
1710
1711 BEGIN
1712 IF PG_DEBUG = 'Y' THEN
1713 arp_util_tax.debug( '>> INS_LOCATION_COMBINATIONS' );
1714 END IF;
1715
1716 OPEN ar_location_combinations_s_c;
1717 FETCH ar_location_combinations_s_c into location_id;
1718 CLOSE ar_location_combinations_s_c;
1719
1720 insert into ar_location_combinations( LOCATION_ID,
1721 LOCATION_STRUCTURE_ID,
1722 ENABLED_FLAG,
1723 LAST_UPDATED_BY,
1724 LAST_UPDATE_DATE,
1725 SUMMARY_FLAG,
1726 PROGRAM_APPLICATION_ID,
1727 PROGRAM_ID,
1728 PROGRAM_UPDATE_DATE,
1729 REQUEST_ID,
1730 START_DATE_ACTIVE,
1731 END_DATE_ACTIVE,
1732 LOCATION_ID_SEGMENT_1,
1733 LOCATION_ID_SEGMENT_2,
1734 LOCATION_ID_SEGMENT_3,
1735 LOCATION_ID_SEGMENT_4,
1736 LOCATION_ID_SEGMENT_5,
1737 LOCATION_ID_SEGMENT_6,
1738 LOCATION_ID_SEGMENT_7,
1739 LOCATION_ID_SEGMENT_8,
1740 LOCATION_ID_SEGMENT_9,
1741 LOCATION_ID_SEGMENT_10,
1742 CREATED_BY,
1743 CREATION_DATE)
1744 VALUES
1745 (
1746 LOCATION_ID,
1747 LOCATION_STRUCTURE_ID,
1748 ENABLED_FLAG,
1749 arp_standard.PROFILE.USER_ID,
1750 sysdate,
1751 'N',
1752 arp_standard.PROFILE.PROGRAM_APPLICATION_ID,
1753 arp_standard.PROFILE.PROGRAM_ID,
1754 sysdate,
1755 arp_standard.PROFILE.REQUEST_ID,
1756 START_DATE_ACTIVE,
1757 END_DATE_ACTIVE,
1758 LOCATION_ID_SEGMENT_1,
1759 LOCATION_ID_SEGMENT_2,
1760 LOCATION_ID_SEGMENT_3,
1761 LOCATION_ID_SEGMENT_4,
1762 LOCATION_ID_SEGMENT_5,
1763 LOCATION_ID_SEGMENT_6,
1764 LOCATION_ID_SEGMENT_7,
1765 LOCATION_ID_SEGMENT_8,
1766 LOCATION_ID_SEGMENT_9,
1767 LOCATION_ID_SEGMENT_10,
1768 arp_standard.PROFILE.USER_ID,
1769 sysdate );
1770
1771 location_combination_inserted := TRUE;
1772 IF PG_DEBUG = 'Y' THEN
1773 arp_util_tax.debug( '<< INS_LOCATION_COMBINATIONS: ' || to_char(location_id) );
1774 END IF;
1775 return( location_id );
1776
1777 END ins_location_combinations;
1778
1779 /*-------------------------------------------------------------------------+
1780 | PRIVATE FUNCTION |
1781 | find_location_ccid |
1782 | |
1783 | DESCRIPTION |
1784 | This functions attempts to find the LOCATION_CCID from the table |
1785 | AR_LOCATION_COMBINATIONS given each of the possible values for |
1786 | segments in the LOCATION flexfield structure. |
1787 | If no such record exists, this record will, if each of the values |
1788 | exist, create a new record and return the LOCATION_CCID of the new |
1789 | record. |
1790 | |
1791 | REQUIRES |
1792 | location Record type, which contains |
1793 | State, County, City, Postal Code |
1794 | Each of the fields from the users descriptive |
1795 | flexfield. |
1796 | |
1797 | RETURNS |
1798 | LOCATION_CODE_COMBINATION_ID for this structure |
1799 | |
1800 | EXCEPTIONS RAISED |
1801 | |
1802 | NOTES |
1803 | |
1804 | EXAMPLE |
1805 | |
1806 +-------------------------------------------------------------------------*/
1807
1808
1809
1810 FUNCTION find_location_ccid( param in LOCATION_TYPE ) return number IS
1811
1812 /* Cursor: ra_addresses_c */
1813 /* */
1814 /* Find each of the address segment id's that are used in this location flexfield */
1815
1816
1817 CURSOR ra_addresses_c( param in LOCATION_TYPE ) is
1818
1819 select v1.location_segment_id,
1820 v2.location_segment_id,
1821 v3.location_segment_id
1822 from ar_location_values v1,
1823 ar_location_values v2,
1824 ar_location_values v3
1825 where v1.location_segment_value = upper(param.STATE)
1826 and v2.location_segment_value = upper(param.COUNTY)
1827 and v3.location_segment_value = upper(param.CITY)
1828 and v2.parent_segment_id = v1.location_segment_id
1829 and v3.parent_segment_id = v2.location_segment_id
1830 and v1.location_structure_id = arp_standard.sysparm.location_structure_id and
1831 v2.location_structure_id = arp_standard.sysparm.location_structure_id and
1832 v3.location_structure_id = arp_standard.sysparm.location_structure_id
1833 and v1.location_segment_qualifier = 'STATE'
1834 and v2.location_segment_qualifier = 'COUNTY'
1835 and v3.location_segment_qualifier = 'CITY' ;
1836
1837 cursor sel_bad_rates( p_location_id in number,
1838 p_location_id_segment_1 in number,
1839 p_location_id_segment_2 in number,
1840 p_location_id_segment_3 in number,
1841 p_location_id_segment_4 in number,
1842 p_location_id_segment_5 in number,
1843 p_location_id_segment_6 in number,
1844 p_location_id_segment_7 in number,
1845 p_location_id_segment_8 in number,
1846 p_location_id_segment_9 in number,
1847 p_location_id_segment_10 in number ) is
1848 select rowid
1849 from ar_sales_tax tax
1850 where tax.location_id = p_location_id
1851 and tax.enabled_flag = 'Y'
1852 and not exists (
1853 select
1854 'x'
1855 from ar_location_rates r1,
1856 ar_location_rates r2,
1857 ar_location_rates r3
1858 where r1.location_segment_id = p_location_id_segment_1
1859 and r2.location_segment_id = p_location_id_segment_2
1860 and r3.location_segment_id = p_location_id_segment_3
1861 and greatest( r1.from_postal_code,
1862 r2.from_postal_code,
1863 r3.from_postal_code ) <= least( r1.to_postal_code,
1864 r2.to_postal_code,
1865 r3.to_postal_code )
1866 and greatest( r1.start_date ,
1867 r2.start_date ,
1868 r3.start_date ) <= least( r1.end_date ,
1869 r2.end_date ,
1870 r3.end_date )
1871 and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
1872 and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
1873 and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
1874 and tax.from_postal_code = greatest( r1.from_postal_code,
1875 r2.from_postal_code,
1876 r3.from_postal_code )
1877 and tax.to_postal_code = least( r1.to_postal_code,
1878 r2.to_postal_code,
1879 r3.to_postal_code )
1880 and tax.start_date = greatest( r1.start_date ,
1881 r2.start_date ,
1882 r3.start_date )
1883 and tax.end_date = least( r1.end_date ,
1884 r2.end_date ,
1885 r3.end_date ));
1886
1887 CURSOR loc_ccid_c(p_location_id_segment_1 in number,
1888 p_location_id_segment_2 in number,
1889 p_location_id_segment_3 in number,
1890 p_location_id_segment_4 in number,
1891 p_location_id_segment_5 in number,
1892 p_location_id_segment_6 in number,
1893 p_location_id_segment_7 in number,
1894 p_location_id_segment_8 in number,
1895 p_location_id_segment_9 in number,
1896 p_location_id_segment_10 in number ) IS
1897 select location_id
1898 from ar_location_combinations cc
1899 where location_structure_id = arp_standard.sysparm.location_structure_id
1900 and cc.LOCATION_ID_SEGMENT_1 = p_LOCATION_ID_SEGMENT_1
1901 and cc.LOCATION_ID_SEGMENT_2 = p_LOCATION_ID_SEGMENT_2
1902 and cc.LOCATION_ID_SEGMENT_3 = p_LOCATION_ID_SEGMENT_3;
1903
1904
1905 location_id number;
1906 loc_id_1 number := null;
1907 loc_id_2 number := null;
1908 loc_id_3 number := null;
1909 loc_id_4 number := null;
1910 loc_id_5 number := null;
1911 loc_id_6 number := null;
1912 loc_id_7 number := null;
1913 loc_id_8 number := null;
1914 loc_id_9 number := null;
1915 loc_id_10 number := null;
1916
1917
1918
1919 BEGIN
1920 IF PG_DEBUG = 'Y' THEN
1921 arp_util_tax.debug( '>> FIND_LOCATION_CCID' );
1922 END IF;
1923
1924 OPEN ra_addresses_c( param );
1925 FETCH ra_addresses_c into loc_id_1 ,
1926 loc_id_2 ,
1927 loc_id_3 ;
1928 IF ra_addresses_c%FOUND
1929 THEN /* we have ids for each value, do we have ccid's? */
1930 OPEN loc_ccid_c( loc_id_1, loc_id_2, loc_id_3, loc_id_4, loc_id_5, loc_id_6, loc_id_7, loc_id_8, loc_id_9, loc_id_10 );
1931 FETCH loc_ccid_c into location_id;
1932 if loc_ccid_c%NOTFOUND
1933 THEN
1934
1935 location_id := ins_location_combinations(
1936 arp_standard.sysparm.location_structure_id,
1937 null,
1938 null,
1939 loc_id_1,
1940 loc_id_2,
1941 loc_id_3,
1942 loc_id_4,
1943 loc_id_5,
1944 loc_id_6,
1945 loc_id_7,
1946 loc_id_8,
1947 loc_id_9,
1948 loc_id_10,
1949 'Y' );
1950 END IF;
1951 CLOSE loc_ccid_c;
1952 ELSE
1953 CLOSE ra_addresses_c;
1954 IF PG_DEBUG = 'Y' THEN
1955 arp_util_tax.debug( '<< FIND_LOCATION_CCID: NO_DATA_FOUND' );
1956 END IF;
1957 RETURN(NULL);
1958 END IF;
1959 CLOSE ra_addresses_c;
1960
1961 IF PG_DEBUG = 'Y' THEN
1962 arp_util_tax.debug( '<< FIND_LOCATION_CCID: ' || to_char(location_id) );
1963 END IF;
1964 RETURN( location_id );
1965 END find_location_ccid;
1966
1967
1968 /*-------------------------------------------------------------------------+
1969 | PUBLIC FUNCTION |
1970 | TERR_SHORT_NAME |
1971 | |
1972 | CALLED BY TRIGGER AR_LOCATION_VALUES_BRIU |
1973 | |
1974 | DESCRIPTION |
1975 | |
1976 | Given a territory code, this function returns the description for that |
1977 | territory. |
1978 | |
1979 | REQUIRES |
1980 | territory_code FK to FND_TERRITORIES.TERRITORY_CODE |
1981 | |
1982 | RETURNS |
1983 | Description FND_TERRITORIES.DESCRIPTION |
1984 | |
1985 | EXCEPTIONS RAISED |
1986 | NO_DATA_FOUND If the FK is in error |
1987 | |
1988 | NOTES |
1989 | Package varicables: PREVIOUS_TERRITORY_CODE and PREVIOUS_TERRITORY_ |
1990 | SHORT_NAME cache the most recent database access. |
1991 | |
1992 | EXAMPLE |
1993 | |
1994 +-------------------------------------------------------------------------*/
1995
1996
1997 function terr_short_name( territory_code in varchar2 ) return varchar2 is
1998
1999 short_name VARCHAR2(80);
2000
2001 begin
2002 IF PG_DEBUG = 'Y' THEN
2003 arp_util_tax.debug( '>> TERRITORY_SHORT_NAME( ' || territory_code || ' ) ' );
2004 END IF;
2005
2006 if territory_code = previous_territory_code
2007 then return( previous_territory_short_name );
2008 else
2009
2010 select territory_short_name into short_name from fnd_territories_vl
2011 where territory_code = terr_short_name.territory_code;
2012
2013 previous_territory_code := territory_code;
2014 previous_territory_short_name := short_name;
2015 IF PG_DEBUG = 'Y' THEN
2016 arp_util_tax.debug( '<< TERRITORY_SHORT_NAME: ' || short_name );
2017 END IF;
2018 return( short_name );
2019
2020 end if;
2021
2022 EXCEPTION
2023 WHEN NO_DATA_FOUND
2024 THEN arp_standard.fnd_message(gl_public_sector.get_message_name(
2025 p_message_name => 'AR_BAD_TERRITORY',
2026 p_app_short_name => 'AR'),
2027 'TERRITORY', territory_code);
2028 IF PG_DEBUG = 'Y' THEN
2029 arp_util_tax.debug( 'Territory not available' );
2030 END IF;
2031
2032 end terr_short_name;
2033
2034
2035 /*-------------------------------------------------------------------------+
2036 | PUBLIC PROCEDURE |
2037 | Set_Location_CCID |
2038 | |
2039 | CALLED BY TRIGGER RA_ADDRESSES_BRIU |
2040 | |
2041 | DESCRIPTION |
2042 | |
2043 | Given Each of the key components of this address, find and set the |
2044 | Location_ID column to the Code Combinations ID applicable to this |
2045 | address. |
2046 | |
2047 | In order to do this, it may be necessary to insert new items into |
2048 | the tables: AR_LOCATION_VALUES and AR_LOCATION_COMBINATIONS |
2049 | |
2050 | REQUIRES |
2051 | City City column of RA_ADDRESSES |
2052 | State State column of RA_ADDRESSES |
2053 | County County column of RA_ADDRESSES |
2054 | Province Province column of RA_ADDRESSES |
2055 | Country Country column of RA_ADDRESSES |
2056 | Postal Code Postal Code column of RA_ADDRESSES |
2057 | DF1 .. 10 User Descriptive Flexfields 1 through 10 |
2058 | |
2059 | RETURNS |
2060 | Location_ID Set the Location Code Combination id |
2061 | applicable to this address given each of the |
2062 | segment values described above. |
2063 | MODIFIES |
2064 | Each of the address components may have the value NULL, even though |
2065 | it is used as part of the location flexfield. If so, this code will |
2066 | determine the missing value using the other segments and postal code |
2067 | and update this column to hold the correct value. |
2068 | |
2069 | EXCEPTIONS RAISED |
2070 | |
2071 | NOTES |
2072 | |
2073 | EXAMPLE |
2074 | |
2075 +-------------------------------------------------------------------------*/
2076
2077
2078 PROCEDURE Set_Location_CCID( Country in out NOCOPY varchar2,
2079 City in out NOCOPY varchar2,
2080 State in out NOCOPY varchar2,
2081 County in out NOCOPY varchar2,
2082 Province in out NOCOPY varchar2,
2083 Postal_code in out NOCOPY varchar2,
2084 attribute1 in out NOCOPY varchar2,
2085 attribute2 in out NOCOPY varchar2,
2086 attribute3 in out NOCOPY varchar2,
2087 attribute4 in out NOCOPY varchar2,
2088 attribute5 in out NOCOPY varchar2,
2089 attribute6 in out NOCOPY varchar2,
2090 attribute7 in out NOCOPY varchar2,
2091 attribute8 in out NOCOPY varchar2,
2092 attribute9 in out NOCOPY varchar2,
2093 attribute10 in out NOCOPY varchar2,
2094 location_ccid in out NOCOPY number,
2095 address_id in number default null ) IS
2096
2097 location LOCATION_TYPE;
2098 location_id TAB_ID_TYPE;
2099 description varchar2(60);
2100 loc_struct varchar2(512);
2101
2102
2103
2104 BEGIN
2105 IF PG_DEBUG = 'Y' THEN
2106 arp_util_tax.debug( '>> SET_LOCATION_CCID( ' || Country || ', ' ||
2107 City || ', ' ||
2108 State || ', ' ||
2109 County || ', ' ||
2110 Province || ', ' ||
2111 Postal_code || ', ' ||
2112 attribute1 || ', ' ||
2113 attribute2 || ', ' ||
2114 attribute3 || ', ' ||
2115 attribute4 || ', ' ||
2116 attribute5 || ', ' ||
2117 attribute6 || ', ' ||
2118 attribute7 || ', ' ||
2119 attribute8 || ', ' ||
2120 attribute9 || ', ' ||
2121 attribute10 || ', ' ||
2122 to_char(address_id) || ') ' );
2123 END IF;
2124 location_combination_inserted := FALSE;
2125 location_segment_inserted := FALSE;
2126
2127 IF country = nvl(arp_standard.sysparm.default_country, country )
2128 THEN
2129 BEGIN
2130 BEGIN
2131
2132 if ltrim(rtrim(STATE)) is null then STATE := find_missing_parent_in_loc( 'COUNTY', COUNTY, postal_code ); END IF;
2133 if ltrim(rtrim(COUNTY)) is null then COUNTY := find_missing_parent_in_loc( 'CITY', CITY, postal_code ); END IF;
2134
2135 location.country := ltrim(rtrim(country));
2136 location.state := ltrim(rtrim(state));
2137 location.county := ltrim(rtrim(county));
2138 location.province := ltrim(rtrim(province));
2139 location.city := ltrim(rtrim(city));
2140 location.postal_code := ltrim(rtrim(postal_code));
2141 location.attribute1 := ltrim(rtrim(attribute1));
2142 location.attribute2 := ltrim(rtrim(attribute2));
2143 location.attribute3 := ltrim(rtrim(attribute3));
2144 location.attribute4 := ltrim(rtrim(attribute4));
2145 location.attribute5 := ltrim(rtrim(attribute5));
2146 location.attribute6 := ltrim(rtrim(attribute6));
2147 location.attribute7 := ltrim(rtrim(attribute7));
2148 location.attribute8 := ltrim(rtrim(attribute8));
2149 location.attribute9 := ltrim(rtrim(attribute9));
2150 location.attribute10 := ltrim(rtrim(attribute10));
2151
2152 location_ccid := find_location_ccid( location );
2153
2154 EXCEPTION
2155 WHEN NO_DATA_FOUND THEN location_ccid := NULL;
2156 END;
2157
2158 if location_ccid is null
2159 THEN /* One or more of the address componets is missing from the values table */
2160 BEGIN
2161
2162 IF arp_standard.sysparm.address_validation = 'ERR'
2163 THEN
2164
2165 loc_struct := 'STATE COUNTY CITY';
2166
2167 /* Report The Error back to the user */
2168 -- arp_standard.fnd_message( 'AR_PP_ADDS_NO_ADDRESS', 'LOCATION', STATE || ' ' ||COUNTY || ' ' ||CITY );
2169 -- Bug 3179554
2170 arp_standard.fnd_message( 'AR_PP_ADDS_NO_ADDRESS', 'LOCATION', loc_struct || ' ' || STATE || ' ' ||COUNTY || ' ' ||CITY );
2171
2172
2173 ELSE
2174
2175
2176 /* Find Missing Values, insert them and then generate Code Combination ID */
2177
2178 for i in 0 .. 10
2179 loop
2180 location_id(i) := null;
2181 end loop;
2182
2183
2184 location_id(1) := find_location_segment_id( 'STATE', STATE, initcap( STATE ), location_id( 0 ), attribute1 => to_char(address_id) );
2185 location_id(2) := find_location_segment_id( 'COUNTY', COUNTY, initcap( COUNTY ), location_id( 1 ), attribute1 => to_char(address_id) );
2186 location_id(3) := find_location_segment_id( 'CITY', CITY, initcap( CITY ), location_id( 2 ), attribute1 => to_char(address_id) );
2187
2188 location_ccid := ins_location_combinations(
2189 arp_standard.sysparm.location_structure_id,
2190 NULL,
2191 NULL,
2192 location_id(1),
2193 location_id(2),
2194 location_id(3),
2195 location_id(4),
2196 location_id(5),
2197 location_id(6),
2198 location_id(7),
2199 location_id(8),
2200 location_id(9),
2201 location_id(10),
2202 'Y');
2203
2204 END IF;
2205 END;
2206 END IF;
2207 END;
2208 ELSE location_ccid := null;
2209 END IF;
2210 IF PG_DEBUG = 'Y' THEN
2211 arp_util_tax.debug( '<< SET_LOCATION_CCID: ' || to_char(location_ccid) );
2212 END IF;
2213 EXCEPTION
2214
2215 /*---------------------------------------------------------------------------+
2216 | Oracle Receivables cannot accept a null value for a segment when this |
2217 | location has never been used before. It is not possible to create a new |
2218 | location combination when one of the segments is null. |
2219 +---------------------------------------------------------------------------*/
2220
2221 WHEN LOCATION_SEGMENT_NULL_VALUE
2222 THEN arp_standard.fnd_message( 'AR_PP_ADDS_LOC_NULL_VALUE', 'SEGMENT', NULL_SEGMENT_QUALIFIER );
2223
2224 END Set_Location_CCID ;
2225
2226
2227 /*-------------------------------------------------------------------------+
2228 | PUBLIC PROCEDURE |
2229 | return_location_defaults |
2230 | |
2231 | DESCRIPTION |
2232 | |
2233 | Return the default values for the columns:- |
2234 | start_date, end_date, from_postal_code and to_postal_code |
2235 | in the table AR_LOCATION_RATES. |
2236 | |
2237 | MODIFIES |
2238 | from_postal_code |
2239 | to_postal_code |
2240 | min_start_date |
2241 | max_end_date |
2242 | |
2243 | EXCEPTIONS RAISED |
2244 | |
2245 | NOTES |
2246 | |
2247 | EXAMPLE |
2248 | |
2249 +-------------------------------------------------------------------------*/
2250
2251
2252
2253 procedure return_location_defaults( from_postal_code out NOCOPY varchar2,
2254 to_postal_code out NOCOPY varchar2,
2255 min_start_date out NOCOPY date,
2256 max_end_date out NOCOPY date ) is
2257 begin
2258
2259 from_postal_code := arp_standard.sysparm.from_postal_code;
2260 to_postal_code := arp_standard.sysparm.to_postal_code;
2261 min_start_date := arp_standard.min_start_date;
2262 max_end_date := arp_standard.max_end_date;
2263
2264 end;
2265
2266 BEGIN
2267
2268 /*---------------------------------------------------------------------------+
2269 | Initialise Sales Tax Location Flexfield qualifiers so that the database |
2270 | trigger, AR_LOCATION_VALUES_BRIU has access to this information for |
2271 | validation purposes. |
2272 *---------------------------------------------------------------------------*/
2273
2274 ARP_ADDS_MINUS99.first_segment_qualifier := 'STATE';
2275 ARP_ADDS_MINUS99.all_segment_qualifiers := 'STATE COUNTY CITY';
2276 ARP_ADDS_MINUS99.last_segment_qualifier := 'CITY';
2277 --PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
2278 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2279
2280 if arp_standard.sysparm.from_postal_code is null
2281 or arp_standard.sysparm.to_postal_code is null
2282 or arp_standard.sysparm.from_postal_code > arp_standard.sysparm.to_postal_code
2283 then
2284 arp_standard.fnd_message( 'AR_SYSPARM_POSTAL_CODES', 'FROM', arp_standard.sysparm.from_postal_code,
2285 'TO', arp_standard.sysparm.to_postal_code );
2286 end if;
2287
2288 END ARP_ADDS_MINUS99;