1 PACKAGE BODY arp_addr_label_pkg as
2 /* $Header: AROADDLB.pls 120.8 2005/11/17 08:50:39 salladi ship $ */
3
4
5 /*=============================================================================
6 || PL/SQL table definition
7 =============================================================================*/
8 /* One Dimensional array - Number */
9 TYPE OneDimNum IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
10
11 /* One Dimensional array - Character */
12 TYPE OneDimChr IS TABLE OF VARCHAR2(300) INDEX BY BINARY_INTEGER;
13
14 /* bug2411369 Byte number of One Multi byte Character */
15
16 multi_char_byte Number;
17
18 PROCEDURE uptake_name_address_formatting (
19 p_address1 IN VARCHAR2,
20 p_address2 IN VARCHAR2,
21 p_address3 IN VARCHAR2,
22 p_address4 IN VARCHAR2,
23 p_city IN VARCHAR2,
24 p_county IN VARCHAR2,
25 p_state IN VARCHAR2,
26 p_province IN VARCHAR2,
27 p_postal_code IN VARCHAR2,
28 -- territory_short_name IN VARCHAR2,
29 p_country_code IN VARCHAR2,
30 p_customer_name IN VARCHAR2,
31 p_bill_to_location IN VARCHAR2,
32 p_first_name IN VARCHAR2,
33 p_last_name IN VARCHAR2,
34 p_mail_stop IN VARCHAR2,
35 p_default_country_code IN VARCHAR2,
36 -- default_country_desc IN VARCHAR2,
37 p_print_home_country_flag IN VARCHAR2,
38 x_formatted_result OUT NOCOPY VARCHAR2)
39 IS
40 l_from_territory_code VARCHAR2(2) := p_country_code;
41 l_formatted_address VARCHAR2(2000);
42 l_formatted_addr_lines_cnt NUMBER;
43 l_formatted_address_tbl hz_format_pub.string_tbl_type;
44 l_formatted_name VARCHAR2(100);
45 l_formatted_name_lines_cnt NUMBER;
46 l_formatted_name_tbl hz_format_pub.string_tbl_type;
47 l_return_status VARCHAR2(1);
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_country_code VARCHAR2(2) := p_country_code;
51 BEGIN
52
53 IF p_country_code is null THEN
54 l_country_code := p_default_country_code;
55 END IF;
56
57 IF p_default_country_code = p_country_code AND
58 p_print_home_country_flag = 'Y' THEN
59 l_from_territory_code := 'x'; -- force country short name be displayed
60 END IF;
61
62 hz_format_pub.format_address (
63 p_line_break => ' ',
64 p_from_territory_code => l_from_territory_code,
65 p_address_line_1 => p_address1,
66 p_address_line_2 => p_address2,
67 p_address_line_3 => p_address3,
68 p_address_line_4 => p_address4,
69 p_city => p_city,
70 p_postal_code => p_postal_code,
71 p_state => p_state,
72 p_province => p_province,
73 p_county => p_county,
74 p_country => l_country_code,
75 -- output parameters
76 x_return_status => l_return_status,
77 x_msg_count => l_msg_count,
78 x_msg_data => l_msg_data,
79 x_formatted_address => l_formatted_address,
80 x_formatted_lines_cnt => l_formatted_addr_lines_cnt,
81 x_formatted_address_tbl => l_formatted_address_tbl
82 );
83
87
84 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
85 RETURN;
86 END IF;
88 hz_format_pub.format_name (
89 p_ref_territory_code => l_country_code,
90 p_person_first_name => p_first_name,
91 p_person_last_name => p_last_name,
92 -- output parameters
93 x_return_status => l_return_status,
94 x_msg_count => l_msg_count,
95 x_msg_data => l_msg_data,
96 x_formatted_name => l_formatted_name,
97 x_formatted_lines_cnt => l_formatted_name_lines_cnt,
98 x_formatted_name_tbl => l_formatted_name_tbl );
99
100 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
101 RETURN;
102 END IF;
103
104 IF l_country_code = 'JP' THEN
105 x_formatted_result := l_formatted_address || ' ' ||
106 p_customer_name || ' ' ||
107 p_bill_to_location || ' ' ||
108 p_mail_stop || ' ' ||
109 l_formatted_name;
110 ELSE
111 x_formatted_result := l_formatted_name || ' ' ||
112 p_mail_stop || ' ' ||
113 p_customer_name || ' : ' ||
114 p_bill_to_location || ' ' ||
115 l_formatted_address;
116 END IF;
117
118 END uptake_name_address_formatting;
119
120 /*=============================================================================
121 || PRIVATE FUNCTION
122 || get_address_style
123 ||
124 || DESCRIPTION
125 || Returns the associated address style for the given country code
126 ||
127 || ARGUMENTS
128 || country_code : country code
129 ||
130 || RETURN
131 || NULL :
132 || if country code is not defined in FND_TERRITORIES_VL
133 ||
134 || retrieved value from FND_TERRITORIES_VL :
135 || else
136 ||
137 || NOTE
138 || retrieved value can be NULL
139 ||
140 =============================================================================*/
141 FUNCTION get_address_style( country_code VARCHAR2 ) return VARCHAR2 IS
142
143 st fnd_territories_vl.address_style%TYPE;
144
145 BEGIN
146
147 select address_style into st from fnd_territories_vl
148 where territory_code = country_code;
149
150 return( st );
151
152 EXCEPTION
153
154 WHEN NO_DATA_FOUND THEN
155
156 return( NULL );
157
158 END get_address_style;
159
160
161 /*=============================================================================
162 || PRIVATE FUNCTION
163 || get_country_desc
164 ||
165 || DESCRIPTION
166 || Returns the associated territory short name for the given country code
167 ||
168 || ARGUMENTS
169 || country_code : country code
170 || default_country_code : default country code
171 || default_country_desc : default country description
172 || print_home_country_flag : flag to control the printing of home country
173 ||
174 || RETURN
175 || NULL :
176 || 1) if country_code is NULL
177 || 2) if country_code = default_country_code AND
178 || print_home_country_flag = 'N'
179 ||
180 || country_code :
181 || 1) if the retrieved territory_short_name is NULL
182 || 2) if country_code is not defined in FND_TERRITORIES_vl table
183 ||
184 || default_country_desc :
185 || if country_code = default_country_code AND print_home_country_flag = 'Y'
186 ||
187 || retrieved value from FND_TERRITORIES_vl :
188 || else
189 ||
190 || NOTE
191 || This function simulates the report local function
192 || get_country_description in RAXINV.rdf and package function
193 || arp_add_.territory_short_name in ARPLBLOC.fxp.
194 ||
195 =============================================================================*/
196 FUNCTION get_country_desc( country_code VARCHAR2,
197 default_country_code VARCHAR2,
198 default_country_desc VARCHAR2,
199 print_home_country_flag VARCHAR2 )
200 return VARCHAR2 IS
201
202 description fnd_territories_vl.territory_short_name%TYPE;
203
204 BEGIN
205
206 IF country_code IS NULL THEN
207 description := NULL;
208 ELSE
209 IF country_code <> NVL(default_country_code, 'xxxxxx') THEN
210
211 select territory_short_name into description
212 from fnd_territories_vl
213 where territory_code = country_code;
214
215 IF description IS NULL THEN
216 description := country_code;
217 END IF;
218 ELSE
219 IF print_home_country_flag = 'Y' then
220 description := default_country_desc;
221 ELSE
222 description := '';
223 END IF;
224 END IF;
225 END IF;
226
227 return(description);
228
229 EXCEPTION
230
231 WHEN NO_DATA_FOUND THEN
232
233 return( country_code );
234
235 END get_country_desc;
236
237
238 /*=============================================================================
239 || PRIVATE FUNCTION
240 || get_default_attn
241 ||
242 || DESCRIPTION
243 || Returns the default 'attention' value
244 ||
245 || ARGUMENTS
246 ||
247 || RETURN
248 || 'Attn: Accounts Payable' :
249 || if lookup value is not defined
250 ||
251 || retrieved value from AR_LOOKUPS
252 || (lookup_type='ADDRESS_LABEL', lookup_code='ATTN_DEFAULT_MSG') :
253 || else
254 ||
255 || NOTES
256 || This function is AR specific and is used by format_address().
257 ||
258 =============================================================================*/
259 FUNCTION get_default_attn return VARCHAR2 IS
260
261 attn ar_lookups.meaning%TYPE;
262
263 BEGIN
264
265 select meaning
266 into attn
267 from ar_lookups
268 where lookup_type = 'ADDRESS_LABEL'
269 and lookup_code = 'ATTN_DEFAULT_MSG';
270
271 return( attn );
272
273 EXCEPTION
274
275 WHEN NO_DATA_FOUND THEN
276
277 return('Attn: Accounts Payable');
278
279 END get_default_attn;
280
281
282 /*=============================================================================
283 || PRIVATE FUNCTION
284 || get_mail_stop_label
285 ||
286 || DESCRIPTION
287 || Returns mail stop label
288 ||
289 || ARGUMENTS
290 ||
291 || RETURN
292 || 'Mail Stop: ' :
293 || if lookup value is not defined
294 ||
295 || retrieved value from AR_LOOKUPS
296 || (lookup_type='ADDRESS_LABEL', lookup_code='MAIL_STOP') :
297 || else
298 ||
299 || NOTES
300 || This function is AR specific and is used by format_address().
301 ||
302 =============================================================================*/
303 FUNCTION get_mail_stop_label return VARCHAR2 IS
304
305 label ar_lookups.meaning%TYPE;
306
307 BEGIN
308
309 select meaning into label from ar_lookups
310 where lookup_type = 'ADDRESS_LABEL' and
311 lookup_code = 'MAIL_STOP';
312
313 return( label );
314
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317
318 return('Mail Stop: ');
319
320 END get_mail_stop_label;
321
322
323 /*=============================================================================
324 || PRINVATE FUNCTION
325 || check_multibyte
326 ||
327 || DESCRIPTION
328 || Checks if a 'n'th byte in the given string 'str' is a first half of
329 || multi-byte, a last half of multi-byte, or a single-byte.
330 ||
331 || ARGUMENTS
332 || str : string to be examined
333 || n : byte position to be examined
334 ||
335 || RETURNS
336 || 3 : middle byte of a 3 byte character.
337 || 1 : if first half of multi-byte
338 || 2 : if last half of multi-byte
339 || 0 : if single-byte
340 ||
341 || NOTES
342 || If a character in target is null, also 0 is returned.
343 ||
344 =============================================================================*/
345 FUNCTION check_multibyte( str VARCHAR2, n NUMBER ) return NUMBER IS
346
347 len OneDimNum; -- numeric array that stores character attribute
348 c NUMBER(5); -- counter
349
350 BEGIN
351
352 len(n) := 0; -- need when str is NULL
353
354
355 /* obtain attribute of all characters in the string */
356
357 c := 1;
358 FOR i IN 1..nvl(length(str),0) LOOP
359 /* Bug 2259206 Added the ELSE clause so that it check for
360 3 BYTE multi-byte characters */
361 IF nvl(lengthb(substr(str,i,1)),1) = 1 THEN
362 len(c) := 0;
363 c := c + 1;
364 ELSIF nvl(lengthb(substr(str,i,1)),1) = 2 THEN
365 len(c) := 1;
366 len(c+1) := 2;
367 c := c+2;
368 ELSE
369 len(c) := 1;
370 len(c+1) := 3;
371 len(c+2) := 2;
372 c := c + 3;
373 END IF;
374 END LOOP;
375
376 return(len(n));
377
378 END check_multibyte;
379
380
381 /*=============================================================================
382 || PRIVATE PROCEDURE
383 || substrb_m
384 ||
385 || DESCRIPTION
386 || Multibyte version of a build-in "substrb" PL/SQL function.
387 || (Obtain a substring 'str_out' out of the given string 'str_in'.)
388 ||
389 || Starting byte position and including string length are given by 'p' and
390 || 'l_in', respectively.
391 ||
392 || If ending byte position is located at the first half byte of
393 || the multi-byte character, ending byte position is relocated at the last
394 || half of the previous multi-byte character or at the previous single-byte
395 || character.
396 ||
397 || A vertually used substring length is given by 'l_out'.
398 ||
399 || ARGUMENTS
400 ||
401 || str_in : string to be processesd
402 || p : starting byte position
403 || l_in : length(byte) of substring (intended)
404 || str_out : substring
405 || l_out : length(byte) of substring (vertually used)
406 ||
407 =============================================================================*/
408 PROCEDURE substrb_m( str_in in VARCHAR2, p in NUMBER, l_in in NUMBER,
409 str_out out NOCOPY VARCHAR2, l_out out NOCOPY NUMBER ) IS
410
411 l NUMBER(5); -- length (byte) of substring
412 last_byte NUMBER(5); -- indicator if the last byte of the string is
413 -- a single-byte or multi-byte character
414 BEGIN
415
416 last_byte := check_multibyte( str_in, p + l_in - 1 );
417
418 IF last_byte = 1 THEN
419
420 /* do not include the last character pointed by 'p',
421 if it is the first part of multibyte character */
422
423 l := l_in - 1;
424 /* Bug 2259206 Added the ELSE clause so that if the position
425 is the SECOND byte of a 3 BYTE character,set the position
426 to 2 BYTES before to avoid truncation of a multi-byte character */
427 ELSIF last_byte = 3 THEN
428 l := l_in - 2;
429 ELSE
430 l := l_in;
431 END IF;
432
433 str_out := substrb( str_in, p, l );
434 l_out := l;
435
436 END substrb_m;
437
438 /* bug2411369 */
439 /*=============================================================================
440 || PRIVATE PROCEDURE
441 || lengthb_m
442 ||
443 || DESCRIPTION
444 || Return width of 'str'
445 ||
446 || ARGUMENTS
447 ||
448 || str : string to be processesd
449 ||
450 =============================================================================*/
451 FUNCTION lengthb_m( str in VARCHAR2 ) return NUMBER IS
452
453 len NUMBER ;
454
455 BEGIN
456
457 len := 0 ;
458
459 /* If DB char set is UTF8 */
460 IF multi_char_byte = 3 THEN
461
462 FOR i IN 1..nvl(length(str),0) LOOP
463 IF nvl(lengthb(substr(str,i,1)),1) = 3 THEN
464 len := len + 2 ;
468 END LOOP;
465 ELSE
466 len := len + 1 ;
467 END IF;
469
470 /* If DB char set is not UTF8 */
471 ELSE
472
473 len := lengthb(str) ;
474
475 END IF;
476
477 return(len);
478
479 END lengthb_m ;
480
481 /* bug2411369 */
482 /*=============================================================================
483 || PRIVATE PROCEDURE
484 || width_m
485 ||
486 || DESCRIPTION
487 || Obtain a substring 'str_out' out of the given string 'str_in' and
488 || the width of 'str_out' should be equal or less than 'l_in'.
489 ||
490 || Starting byte position and including string length are given by 'p' and
491 || 'l_in', respectively.
492 ||
493 || A vertually used substring width is given by 'l_out'.
494 ||
495 || ARGUMENTS
496 ||
497 || str_in : string to be processesd
498 || p : starting byte position
499 || l_in : length(byte) of substring (intended)
500 || str_out : substring
501 || l_out : length(byte) of substring (vertually used)
502 ||
503 =============================================================================*/
504 PROCEDURE width_m( str_in in VARCHAR2, p in NUMBER, l_in in NUMBER,
505 str_out out NOCOPY VARCHAR2, l_out out NOCOPY NUMBER ) IS
506
507 l NUMBER; -- width of substring
508 p_c NUMBER ;
509
510 BEGIN
511
512 l := 0 ;
513 l_out := 0 ;
514 str_out := '' ;
515 p_c := length(substrb(str_in,1,p)) ;
516
517 LOOP
518 IF nvl(lengthb(substr(str_in,p_c,1)),1) = multi_char_byte THEN
519 /* width of multi byte character is 2 */
520 l := l + 2 ;
521 ELSE
522 /* width of single byte character is 1 */
523 l := l + 1 ;
524 END IF;
525
526 EXIT WHEN l > l_in ;
527
528 str_out := str_out || substr(str_in ,p_c, 1) ;
529 p_c := p_c + 1 ;
530 l_out := l ;
531
532 END LOOP;
533
534
535 END width_m ;
536
537
538 /*=============================================================================
539 || PRIVATE PROCEDURE
540 || insert_lbs
541 ||
542 || DESCRIPTION
543 || For the given string 'str_in', this procedure inserts a line break
544 || character at every 'w' bytes. The line break character insertion
545 || continues till the remaing string length becomes less than 'w'.
546 ||
547 || String 'str_out' contains the result of line insertion. The number of line
548 || breaks inserted and the remaining string length are stored in 'lb_num'
549 || and 'remaining', respectively.
550 ||
551 || ARGUMENTS
552 || str_in : string to be processed
553 || w : skip length for line break insertion
554 || str_out(out) : line break inserted string
555 || lb_num (out) : the number of line breaks inserted
556 || remaining (out): remaining (byte) string length
557 ||
558 =============================================================================*/
559 PROCEDURE insert_lbs( str_in in VARCHAR2, w in NUMBER,
560 str_out out NOCOPY VARCHAR2, lb_num out NOCOPY NUMBER,
561 remaining out NOCOPY NUMBER ) IS
562
563 len NUMBER(5); -- length of target string
564 s NUMBER(5); -- starting byte position
565 ww NUMBER(5); -- length of substring (vertually used)
566 str VARCHAR2(300); -- substring of 'str_in'
567 str_inserted VARCHAR2(4000); -- line break inserted string
568 lb_num_inserted NUMBER(5); -- the number of line breaks inserted
569
570 -- bug2411369
571 total_s NUMBER := 1 ; -- total length
572
573 BEGIN
574
575 str_inserted := '';
576 lb_num_inserted := 0;
577
578 s := 1;
579 -- bug2411369 : Changed to lengthb_m
580 -- len := nvl(lengthb(str_in),0);
581 len := nvl(lengthb_m(str_in),0);
582
583 IF w < len THEN
584
585 /* if there is space to put LB in the string
586 (LB is not inserted when w = len) */
587
588 LOOP
589 -- bug2411369 : Changed to width_m
590 -- substrb_m( str_in, s, w, str, ww );
591 width_m( str_in, s, w, str, ww );
592
593 str_inserted := str_inserted || str || '
594 ';
595
596 lb_num_inserted := lb_num_inserted + 1;
597
601 total_s := total_s + ww ; -- update total length
598 -- bug2411369
599 -- s := s + ww; -- update starting position
600 s := s + lengthb(str); -- update starting position
602
603 -- bug2411369
604 -- IF s+w-1 >= len THEN
605 IF total_s + w-1 >= len THEN
606
607 /* exit if there are no space to insert line breaks */
608
609 exit;
610
611 END IF;
612
613 END LOOP;
614
615 END IF;
616
617 str := substrb( str_in, s );
618
619 str_inserted := str_inserted || str; -- concatenate the rest of the string
620
621 str_out := str_inserted;
622 lb_num := lb_num_inserted;
623
624 -- bug2411369 : Changed to lengthb_m
625 -- remaining := w - nvl(lengthb(str),0);
626 remaining := w - nvl(lengthb_m(str),0);
627
628 END insert_lbs;
629
630
631 /*=============================================================================
632 || PUBLIC FUNCTION
633 || format_address_label
634 ||
635 || DESCRIPTION
636 || This function returns a formatted address string for the given address
637 || style. (If the address style is not given, the function can obtain the
638 || address style by itself via country code in the address information.)
639 || The string is a concatenated address segments in which a number of
640 || line break character codes and puncuation marks are inserted: the line
641 || break character code is insertd such that the address string can fit
642 || in the given address box size.
643 ||
644 || Defining the following 9 definitions for a desired address style should
645 || enable the function to generate an address label formatted for the
646 || desired address style:
647 ||
648 || 1) a list of address segments being displayed
649 || - define in display order
650 ||
651 || 2) the number of address segments defined
652 ||
653 || 3) a list of line break address segment candidates
654 || - define in preferable order
655 ||
656 || 4) the number of line break address segment candidates defined
657 ||
658 || 5) default punctuation mark for the address segments
659 ||
660 || 6) exceptional punctuations for the address segments
661 || - the former segment of an address segment pair
662 || - the latter segment of an address segment pair
663 || - punctuation mark for an address segment pair
664 ||
665 || Note exceptional punctuation mark can only be applied
666 || when both address segment pair are not NULL segments.
667 ||
668 || 7) the number of defined exceptional punctuations for the address
669 || segments
670 ||
671 || 8) punctuation marks for the address sub-segments
672 || - used to recognize punctuation mark in the address segment
673 || so that the segment can be decomposed into sub-segments
674 ||
675 || 9) the number of defined punctuation marks for address sub-segments
676 ||
677 ||
678 || These definitions are address style specific and are defined in
679 || 'set_definitions' local procedure; the definitions for the default format
680 || style and for the Japanese address style were defined when this package
681 || was created. If other address style needs to be incorporated,
682 || 'set_definitions' is the procedure where one should make modifications.
683 || Furthermore, if address style specific logic for retrieval of the address
684 || segment values is needed, 'get_segment_value' local procedure is the
685 || place where one should make modificatoins.
686 ||
687 ||
688 || These are the processes how a formatted address label is generated:
689 ||
690 || 1) Obtain address style if it is not given
691 ||
692 || 2) Set the pre-defined 9 address style specific definitions
693 || (by 'set_definitions' local procedure)
694 ||
695 || 3) Obtain the address segment values.
696 || (by 'get_segment_values' local procedure)
697 ||
698 || 4) Set punctuation marks for address segments
699 || (by 'set_punctuations' local procedure)
700 ||
701 || 5) Decompose address segment into sub-segments
702 || (by 'decompose_segment' local procedure)
703 ||
704 || 6) First determine places where line break character codes and puncuation
705 || marks are inserted/attached in concatenated address segments and
706 || then construct a formatted address string by inserting/attaching them
707 || into/to the address segments. This is achieved by two steps:
708 || the <Line Dividing Step> and the <Line Sub Dividing Step>.
709 ||
710 || The line break character code can be categorized into the following
711 || 5 types for its inserting place:
712 ||
713 || a) at the end of the line break address segments determind
714 || in the <Line Dividing Step>
715 || b) at the end of the extra line break address segments determind
716 || in the <Line Sub Dividing Step>
717 || c) at the end of the line break address sub-segments determined
718 || in the <Line Sub Dividing Step>
719 || d) inside the address segment whose length is longer than 'width':
720 || determined in the <Line Sub Dividing Step>
721 || e) inside the address sub-segment whose length is longer than 'width':
722 || determined in the <Line Sub Dividing Step>
723 ||
724 ||
725 || <Line Dividing Step>
726 || --------------------
727 || 1) This step first tries to put the address segments in
728 || 'width x height_min' area by breaking the line 'height_min-1' times:
729 || The top 'height_min-1' line break address segments in the candidate
730 || list are used for line breaking. However, a line break address
731 || segment whose line is NULL is not used for line breaking.
732 ||
733 || 2) Each divided line is then examined if the length exceeds 'width'.
734 || If line overflow is detected, the <Line Sub Dividing Step> is
735 || proceeded to sub-divide the overflowed line.
736 ||
737 || 3) When every line length becomes equal to or shorter than 'width',
738 || then the total number of line breaks (all line break character code
739 || types) is evaluated. If the number is equal to or less than
740 || 'height_max-1', then proceed to a segment concatenation process
741 || <A>. If not, restart the <Line Dividing Step> by selecting the top
742 || 'height_min-2' line break segments from the candidate list. (Note
743 || that line break address segment whose line is NULL is not used
744 || for line breaking.) This is continued till the <Line Dividing
745 || Step> can proceed to the process <A> or the number of selected
746 || candidate reaches 0. If the last trial with 0 line break segment
747 || fails, then another concatination process <B> is proceeded.
748 ||
749 ||
750 || <Line Sub Dividing Step>
751 || ------------------------
752 || 1) This step first searchs for an alternative line break address
753 || segment from the candidate list that has not yet been used as the
754 || line break address segment or as the extra line break address
755 || segment.
756 ||
757 || 2a) If the alternative segment is found, it is used as an extra line
758 || break address segment and allocate the next segment to the extra
759 || line break segment in a new line. If the allocated segment length
760 || exceeds than 'width', then a) split the segment (if the segment is
761 || not decomposable); b) arrange the location of the sub-segments
762 || such that as many sub-segments can stay in the overflowed line and
763 || as few sub-segments goes to a new line (if the segment is
764 || decomposable).
765 ||
766 || 2b) If the search for the alternative segment fails, see if the
767 || overflowed segment is decomposable. If so, arrange the location
768 || of the sub-segments in a same manner as 2a). If not, chose
769 || the previous segment to the overflowed segement as an extra line
770 || break segment and allocate the overflowed segement in a new line.
771 || If the allocated segment exceeds 'width', then split it.
772 ||
773 ||
774 || <A>
775 || ---
776 || 1) insert line break character codes (type e) into the address
777 || sub-segment
778 ||
779 || 2) attach a line break character code (type c) to the tail of the
780 || address sub-segment and attach a punctuation mark for the address
781 || sub-segments to the tail of non line break address sub-segments
782 ||
783 || 3) insert line break character codes (type d) into the address segment
784 ||
785 || 4) attach a line break character code (type a and b) to the tail of the
786 || address segment and attach a punctuation mark for the address
787 || segments to the tail of non line break address segments
788 ||
789 ||
790 || <B>
791 || ---
792 || 1) concatenate all address segments while inserting punctuation mark
793 || for the address segments in between the segments
794 ||
795 || 2) insert line break character code at every 'width'
796 ||
797 || 3) truncate the exceeded line beyond the address box size
798 ||
799 ||
800 ||
801 || where 'width', 'height_min', and 'height_max' are arguments to
802 || the function as can be seen below.
803 ||
804 ||
805 ||
806 || ARGUMENTS
807 || address_style : address format style
808 || address1 : address line 1
809 || address2 : address line 2
810 || address3 : address line 3
811 || address4 : address line 4
812 || city : name of city
813 || county : name of county
814 || state : name of state
815 || province : name of province
816 || postal_code : postal code
817 || territory_short_name : territory short name
818 || country_code : country code (mandatory)
819 || customer_name : customer name
820 || bill_to_location : bill to location
821 || first_name : contact first name
822 || last_name : contact last name
823 || mail_stop : mailing informatioin
824 || default_country_code : default country code (mandatory)
825 || default_country_desc : default territory short name (mandatory)
826 || print_home_country_flag : flag to control printing of home county
827 || (mandatory, 'Y': print, 'N': don't print)
828 || width : address box width (mandatory,not 0)
829 || height_min : address box desired height (mandatory,not 0)
830 || height_max : address box maximum height (mandatory,not 0)
831 ||
832 || RETURN
833 || formatted address string
834 ||
835 || NOTES
836 || Except those denoted as mandatory, any parameter to the function can
837 || have NULL value.
838 ||
839 || The function retrieves an address style value and a territory short name
840 || value via country_code if NULL is passed to the function for these
841 || parameters.
842 ||
843 || capable of handling multi-byte character as well as single-byte
844 || characters
845 ||
846 || word wrap algorithm is incorporated in the Line Dividing Step
847 || and the Line Sub Dividing Step
848 ||
849 ============================================================================*/
850 FUNCTION format_address_label( address_style IN VARCHAR2,
851 address1 IN VARCHAR2,
852 address2 IN VARCHAR2,
853 address3 IN VARCHAR2,
854 address4 IN VARCHAR2,
855 city IN VARCHAR2,
856 county IN VARCHAR2,
857 state IN VARCHAR2,
858 province IN VARCHAR2,
859 postal_code IN VARCHAR2,
860 territory_short_name IN VARCHAR2,
861 country_code IN VARCHAR2,
862 customer_name IN VARCHAR2,
863 bill_to_location IN VARCHAR2,
864 first_name IN VARCHAR2,
865 last_name IN VARCHAR2,
866 mail_stop IN VARCHAR2,
867 default_country_code IN VARCHAR2,
868 default_country_desc IN VARCHAR2,
869 print_home_country_flag IN VARCHAR2,
870 width IN NUMBER,
871 height_min IN NUMBER,
872 height_max IN NUMBER
873 )return VARCHAR2 IS
874
875
876 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
877 Constant Variables
878 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
879
880 /* Address segment INDEX */
881 IDX_ADDRESS1 CONSTANT NUMBER := 1;
882 IDX_ADDRESS2 CONSTANT NUMBER := 2;
883 IDX_ADDRESS3 CONSTANT NUMBER := 3;
884 IDX_ADDRESS4 CONSTANT NUMBER := 4;
885 IDX_CITY CONSTANT NUMBER := 5;
886 IDX_COUNTY CONSTANT NUMBER := 6;
887 IDX_STATE CONSTANT NUMBER := 7;
888 IDX_PROVINCE CONSTANT NUMBER := 8;
889 IDX_POSTAL_CODE CONSTANT NUMBER := 9;
890 IDX_TERRITORY_SHORT_NAME CONSTANT NUMBER := 10;
891 IDX_CUSTOMER_NAME CONSTANT NUMBER := 11;
892 IDX_BILL_TO_LOCATION CONSTANT NUMBER := 12;
893 IDX_FIRST_NAME CONSTANT NUMBER := 13;
894 IDX_LAST_NAME CONSTANT NUMBER := 14;
895 IDX_MAIL_STOP CONSTANT NUMBER := 15;
896 IDX_NUM CONSTANT NUMBER := 15;
897
898 /* maximum # of sub-segments */
899 SUBSEG_NUM CONSTANT NUMBER := 100;
900
901 /* maximum # of line break address sub-segments */
902 SUBSEG_LBS CONSTANT NUMBER := 100;
903
904
905 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
906 Record Definition
907 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
908 /* record that holds exceptional punctuation for address segment */
909 TYPE PuncException IS RECORD ( seg1 OneDimNum, -- address segment (index)
910 seg2 OneDimNum, -- address segment (index)
911 mark OneDimChr -- punctuation mark
912 );
913
914 /* record that holds splitted address segment/sub-segment information */
915 TYPE Splitted IS RECORD ( p OneDimNum, -- address segment (display order)
916 p_sub OneDimNum, -- address sub-segment (display order)
917 val OneDimChr -- splitted value
918 );
919
920
921 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
922 Local Variables
923 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
924
925 /*-----------------------------------------------------------------------------
926 Address Segments
927 -----------------------------------------------------------------------------*/
928 /* numerical array that holds address segment: the segments are designated
929 by index and are stored in display order */
930 addr_seg OneDimNum;
931
932 /* counter that holds # of defined address segments */
933 addr_seg_num NUMBER(5);
934
935
936 /*-----------------------------------------------------------------------------
937 Line Break Address Segments
938 -----------------------------------------------------------------------------*/
939 /* numerical array that holds line break address segment candidates:
940 the candidate segments are designated by index and are stored in
941 preferable order (note that the candidate segments disignator will be
942 changed from 'index' to 'display order' in the course of the program) */
943 lb_seg OneDimNum;
944
945 /* counter that holds # of line break address segment candidates */
946 lb_seg_num NUMBER(5);
947
948 /* numerical array that holds the filtered line break address segments:
949 the segments are designated by display order */
950 lb_seg_used OneDimNum;
951
952 /* counter that holds # of the filtered line break address segments */
953 lb_seg_used_num NUMBER(5);
954
955 /* numerical array that holds the extra line break address segments:
956 the segments are designated by display order */
957 lb_seg_extra OneDimNum;
958
959 /* counter that holds # of the extra line break address segments */
960 lb_seg_extra_num NUMBER(5);
961
962
963 /*-----------------------------------------------------------------------------
964 Address Segment Value
965 -----------------------------------------------------------------------------*/
966 /* string array that holds address segment value
967 address segment value is stored in pre-defined indexed order
968 (see Address Segment Index above) */
969 source OneDimChr;
970
971
972 /*-----------------------------------------------------------------------------
973 Punctuation Marks for Address Segment
974 -----------------------------------------------------------------------------*/
975 /* string that holds default punctuation mark for address segment */
976 punc_default VARCHAR2(10);
977
978 /* record that holds exceptional punctuation information for address segment */
979 punc_exception PuncException;
980
981 /* counter that holds # of exceptional punctuations */
982 punc_exception_num NUMBER(5);
983
984 /* string array that holds puntuation marks for all address segments */
985 punc OneDimChr;
986
987
988 /*-----------------------------------------------------------------------------
989 Address Sub-segemnt Values
990 -----------------------------------------------------------------------------*/
991 /* string array that holds address sub-segment value:
992 the array contains sub-segment values for all segments (SUBSEG_NUM
993 storage area is given to each segment). */
994 source_sub OneDimChr;
995
996 /* numerical array that holds # of address sub-segments: a single cell is
997 given to each segment */
998 source_sub_num OneDimNum;
999
1000
1001 /*-----------------------------------------------------------------------------
1002 Punctuation Marks for Address Sub-segment
1003 -----------------------------------------------------------------------------*/
1004 /* pre-defined punctuation mark for address sub-segment */
1005 punc_mark OneDimChr;
1006
1007 /* counter that holds the number of pre-defined punctuation mark for address
1008 sub-segment */
1009 punc_mark_num NUMBER(5);
1010
1011 /* string array that holds detected punctuation mark for address sub-segment:
1012 the array contains detected punctuation marks for all segments (SUBSEG_NUM
1013 storage area is given to each segment). */
1014 punc_sub OneDimChr;
1015
1016
1017 /*-----------------------------------------------------------------------------
1018 Line Break Address Sub-segments
1019 -----------------------------------------------------------------------------*/
1020 /* numerical array that holds line break address sub-segment:
1021 the line break address sub-segment are designated by order of appearance.
1022 the array contains line break address sub-segments designators for
1023 for all segments (SUBSEG_IDX storage area is given to each segment). */
1024 lb_subseg OneDimNum;
1025
1026 /* numerical array that holds # of line break address sub-segments: a single
1027 cell is given to each segment */
1028 lb_subseg_num OneDimNum;
1029
1030 /* counter that holds total number of line break address sub-segments */
1031 lb_subseg_total_num NUMBER(5);
1032
1033
1034 /*-----------------------------------------------------------------------------
1035 Splitted Address Segments/Sub-segments
1036 -----------------------------------------------------------------------------*/
1037 /* record that holds splitted address segment value */
1038 source_splitted Splitted;
1039
1040 /* counter that holds # of splitted address segments */
1041 source_splitted_num NUMBER(5);
1042
1043 /* record that holds splitted address sub-segment value */
1044 source_sub_splitted Splitted;
1045
1046 /* counter that holds # of splitted address sub-segment */
1047 source_sub_splitted_num NUMBER(5);
1048
1049 /* counter that holds # of line breaks inserted in the splitted address
1050 segments */
1051 lb_seg_splitted_num NUMBER(5);
1052
1053 /* counter that holds # of line breaks inserted in the splitted address
1054 sub-segment */
1055 lb_subseg_splitted_num NUMBER(5);
1056
1057
1058 /*-----------------------------------------------------------------------------
1059 Other Local Variables
1060 -----------------------------------------------------------------------------*/
1061 /* address style */
1062 style fnd_territories_vl.address_style%TYPE; --Bug2107418
1063
1064 /* display order of the first non-NULL segment in the entire address
1065 segments */
1066 first_nn_seg NUMBER(5);
1067
1068 /* display order of the last non-NULL segment in the entire address
1069 segments */
1070 last_nn_seg NUMBER(5);
1071
1072 /* display order of the first segment in the line */
1073 first_segment NUMBER(5);
1074
1075 /* display order of the last segment in the line */
1076 last_segment NUMBER(5);
1077
1078 /* the number of line breaks to start line dividing step */
1079 lb_seg_initial_num NUMBER(5);
1080
1081 /* the number of line breaks to be evaluated with */
1082 lb_seg_limit_num NUMBER(5);
1083
1084 /* flag to indicate whether or not the new name and address format routine should be called */
1085 l_fmt_bkwd_compatible VARCHAR2(10) :='Y';
1086 l_formatted_result VARCHAR2(4000);
1087
1088 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1089 Local Modules
1090 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
1091
1092 /*=============================================================================
1093 || LOCAL PROCEDURE
1094 || set_definitions
1095 ||
1096 || DESCRIPTION
1097 || As stated in format_address_label function description, this procedure sets
1098 || the following 9 definitions for the given address style:
1099 ||
1100 || 1) a list of address segments being displayed
1101 || (define in display order)
1102 || 2) the number of address segments defined
1103 || 3) a list of line break address segment candidates
1104 || (define in preferable order)
1105 || 4) the number of line break address segment candidates defined
1106 || 5) default punctuation mark for the address segments
1107 || 6) exceptioanl punctuations for the address segments
1108 || 7) the number of exceptional punctuations for the address segments
1109 || 8) punctuation marks for the address sub-segments
1110 || 9) the number of punctuation marks for the address sub-segments
1111 ||
1112 || This procedure should be modified when additional address style needs to
1113 || be incorporated. Simply adding new 9 definitions for a desired address
1114 || style in this procedure should allow the format_address_label function to
1115 || generate an address label formatted in the desired address style.
1116 || (If necessary, the subsequent procedure 'get_segment_values' should also
1117 || be modified.)
1118 ||
1119 || ARGUMENTS
1120 || st : address style
1121 ||
1122 || NOTE
1123 || Default address style definitions should be set when the given style
1124 || does not match with any pre-defind style, i.e. ensure always keep the
1125 || else clause for default address style definitions.
1126 ||
1127 =============================================================================*/
1128 PROCEDURE set_definitions( st VARCHAR2 ) IS
1129 BEGIN
1130
1131 IF st = 'JP' THEN /* Japanese Address Style */
1132
1133 /* a list of address segments being displayed
1134 (defined in display order) */
1135
1136 addr_seg(1) := IDX_POSTAL_CODE;
1137 addr_seg(2) := IDX_STATE;
1138 addr_seg(3) := IDX_CITY;
1139 addr_seg(4) := IDX_ADDRESS1;
1140 addr_seg(5) := IDX_ADDRESS2;
1141 addr_seg(6) := IDX_ADDRESS3;
1142 addr_seg(7) := IDX_TERRITORY_SHORT_NAME;
1143 addr_seg(8) := IDX_CUSTOMER_NAME;
1144 addr_seg(9) := IDX_BILL_TO_LOCATION;
1145 addr_seg(10) := IDX_MAIL_STOP;
1146 addr_seg(11) := IDX_LAST_NAME;
1147 addr_seg(12) := IDX_FIRST_NAME;
1148 addr_seg_num := 12; -- # of address segments defined
1149
1150
1151 /* a list of line break address segment candidates
1152 (defined in preferable order) */
1153
1154 lb_seg(1) := IDX_TERRITORY_SHORT_NAME;
1155 lb_seg(2) := IDX_ADDRESS3;
1156 lb_seg(3) := IDX_MAIL_STOP;
1157 lb_seg(4) := IDX_CUSTOMER_NAME;
1158 lb_seg(5) := IDX_BILL_TO_LOCATION;
1159 lb_seg_num := 5; -- # of line break address segment
1160 -- candidates defined
1161
1162
1163 /* default punctuation mark for the address segments */
1164
1165 punc_default := ' ';
1166
1167 /* exceptional punctuations for the address segments */
1168 /* bug 1673614 : Set space as exceptional punctuations */
1169
1170 punc_exception.seg1(1) := IDX_STATE;
1171 punc_exception.seg2(1) := IDX_CITY;
1172 punc_exception.mark(1) := ' ';
1173 punc_exception.seg1(2) := IDX_STATE;
1174 punc_exception.seg2(2) := IDX_ADDRESS1;
1175 punc_exception.mark(2) := ' ';
1176 punc_exception.seg1(3) := IDX_STATE;
1177 punc_exception.seg2(3) := IDX_ADDRESS2;
1178 punc_exception.mark(3) := ' ';
1179 punc_exception.seg1(4) := IDX_STATE;
1180 punc_exception.seg2(4) := IDX_ADDRESS3;
1181 punc_exception.mark(4) := ' ';
1182 punc_exception.seg1(5) := IDX_CITY;
1183 punc_exception.seg2(5) := IDX_ADDRESS1;
1184 punc_exception.mark(5) := ' ';
1185 punc_exception.seg1(6) := IDX_CITY;
1186 punc_exception.seg2(6) := IDX_ADDRESS2;
1187 punc_exception.mark(6) := ' ';
1188 punc_exception.seg1(7) := IDX_CITY;
1189 punc_exception.seg2(7) := IDX_ADDRESS3;
1190 punc_exception.mark(7) := ' ';
1191 punc_exception.seg1(8) := IDX_ADDRESS1;
1192 punc_exception.seg2(8) := IDX_ADDRESS2;
1193 punc_exception.mark(8) := ' ';
1194 punc_exception.seg1(9) := IDX_ADDRESS1;
1195 punc_exception.seg2(9) := IDX_ADDRESS3;
1196 punc_exception.mark(9) := ' ';
1197 punc_exception.seg1(10) := IDX_ADDRESS2;
1198 punc_exception.seg2(10) := IDX_ADDRESS3;
1199 punc_exception.mark(10) := ' ';
1200
1201 punc_exception_num := 10; -- # of exceptional punctuations
1202 -- for the address segments
1203
1204 /* punctuation marks for the address sub-segments */
1205
1206 punc_mark(1) := ' ';
1207 punc_mark_num := 1;
1208
1209 If to_multi_byte( ' ' ) <> ' ' THEN
1210
1211 /* use multi-byte space as a punctuation mark for address
1212 sub-segments only if it is defined in the character set */
1213
1214 punc_mark(2) := to_multi_byte( ' ' );
1215 punc_mark_num := punc_mark_num + 1;
1216
1217 END IF;
1218
1222 ELSIF st = 'NE' THEN /* Northern Europe Address Style */
1219 /* Bug 498053: added code to handle Northern Europe Address Style */
1220 /* Bug 766077: This style is also used for the German Address Style */
1221
1223
1224 addr_seg(1) := IDX_FIRST_NAME;
1225 addr_seg(2) := IDX_LAST_NAME;
1226 addr_seg(3) := IDX_MAIL_STOP;
1227 addr_seg(4) := IDX_CUSTOMER_NAME;
1228 addr_seg(5) := IDX_BILL_TO_LOCATION;
1229 addr_seg(6) := IDX_ADDRESS1;
1230 addr_seg(7) := IDX_ADDRESS2;
1231 addr_seg(8) := IDX_ADDRESS3;
1232 addr_seg(9) := IDX_POSTAL_CODE;
1233 addr_seg(10) := IDX_CITY;
1234 addr_seg(11) := IDX_TERRITORY_SHORT_NAME;
1235 addr_seg_num := 11;
1236
1237 lb_seg(1) := IDX_BILL_TO_LOCATION;
1238 lb_seg(2) := IDX_ADDRESS3;
1239 lb_seg(3) := IDX_CITY;
1240 lb_seg(4) := IDX_ADDRESS2;
1241 lb_seg(5) := IDX_ADDRESS1;
1242 lb_seg(6) := IDX_MAIL_STOP;
1243 lb_seg_num := 6;
1244
1245 punc_default := ' ';
1246 punc_exception.seg1(1) := IDX_FIRST_NAME;
1247 punc_exception.seg2(1) := IDX_LAST_NAME;
1248 punc_exception.mark(1) := ' ';
1249 punc_exception.seg1(2) := IDX_CUSTOMER_NAME;
1250 punc_exception.seg2(2) := IDX_BILL_TO_LOCATION;
1251 punc_exception.mark(2) := ' : ';
1252 punc_exception.seg1(3) := IDX_POSTAL_CODE;
1253 punc_exception.seg2(3) := IDX_CITY;
1254 punc_exception.mark(3) := ' ';
1255 punc_exception_num := 3;
1256
1257 punc_mark(1) := ' ';
1258 punc_mark_num := 1;
1259
1260 /* Bug 2927636: added code to handle Southern Europe Address Style */
1261 ELSIF st = 'SE' THEN /* Southern Europe Address Style */
1262
1263 addr_seg(1) := IDX_FIRST_NAME;
1264 addr_seg(2) := IDX_LAST_NAME;
1265 addr_seg(3) := IDX_MAIL_STOP;
1266 addr_seg(4) := IDX_CUSTOMER_NAME;
1267 addr_seg(5) := IDX_BILL_TO_LOCATION;
1268 addr_seg(6) := IDX_ADDRESS1;
1269 addr_seg(7) := IDX_ADDRESS2;
1270 addr_seg(8) := IDX_ADDRESS3;
1271 addr_seg(9) := IDX_POSTAL_CODE;
1272 addr_seg(10) := IDX_CITY;
1273 addr_seg(11) := IDX_STATE;
1274 addr_seg(12) := IDX_TERRITORY_SHORT_NAME;
1275 addr_seg_num := 12;
1276
1277 lb_seg(1) := IDX_BILL_TO_LOCATION;
1278 lb_seg(2) := IDX_ADDRESS3;
1279 lb_seg(3) := IDX_STATE;
1280 lb_seg(4) := IDX_ADDRESS2;
1281 lb_seg(5) := IDX_ADDRESS1;
1282 lb_seg(6) := IDX_MAIL_STOP;
1283 lb_seg_num := 6;
1284
1285 punc_default := ' ';
1286 punc_exception.seg1(1) := IDX_FIRST_NAME;
1287 punc_exception.seg2(1) := IDX_LAST_NAME;
1288 punc_exception.mark(1) := ' ';
1289 punc_exception.seg1(2) := IDX_CUSTOMER_NAME;
1290 punc_exception.seg2(2) := IDX_BILL_TO_LOCATION;
1291 punc_exception.mark(2) := ' : ';
1292 punc_exception.seg1(3) := IDX_POSTAL_CODE;
1293 punc_exception.seg2(3) := IDX_CITY;
1294 punc_exception.mark(3) := ' ';
1295 punc_exception_num := 3;
1296
1297 punc_mark(1) := ' ';
1298 punc_mark_num := 1;
1299
1300 ELSE /* Default Address Style (DO NOT DELETE !) */
1301
1302 addr_seg(1) := IDX_FIRST_NAME;
1303 addr_seg(2) := IDX_LAST_NAME;
1304 addr_seg(3) := IDX_MAIL_STOP;
1305 addr_seg(4) := IDX_CUSTOMER_NAME;
1306 addr_seg(5) := IDX_BILL_TO_LOCATION;
1307 addr_seg(6) := IDX_ADDRESS1;
1308 addr_seg(7) := IDX_ADDRESS2;
1309 addr_seg(8) := IDX_ADDRESS3;
1310 addr_seg(9) := IDX_ADDRESS4;
1311 addr_seg(10) := IDX_CITY;
1312 addr_seg(11) := IDX_COUNTY;
1313 addr_seg(12) := IDX_STATE;
1314 addr_seg(13) := IDX_PROVINCE;
1315 addr_seg(14) := IDX_POSTAL_CODE;
1316 addr_seg(15) := IDX_TERRITORY_SHORT_NAME;
1317 addr_seg_num := 15;
1318
1319 lb_seg(1) := IDX_BILL_TO_LOCATION;
1320 lb_seg(2) := IDX_ADDRESS4;
1321 lb_seg(3) := IDX_POSTAL_CODE;
1322 lb_seg(4) := IDX_MAIL_STOP;
1323 lb_seg(5) := IDX_ADDRESS3;
1324 lb_seg(6) := IDX_ADDRESS2;
1325 lb_seg(7) := IDX_ADDRESS1;
1326 lb_seg_num := 7;
1327
1328 punc_default := ' ';
1329 punc_exception.seg1(1) := IDX_FIRST_NAME;
1330 punc_exception.seg2(1) := IDX_LAST_NAME;
1331 punc_exception.mark(1) := ' ';
1332 punc_exception.seg1(2) := IDX_CUSTOMER_NAME;
1333 punc_exception.seg2(2) := IDX_BILL_TO_LOCATION;
1334 punc_exception.mark(2) := ' : ';
1335 punc_exception_num := 2;
1336
1337 punc_mark(1) := ' ';
1338 punc_mark_num := 1;
1339
1340 END IF;
1341
1342 END set_definitions;
1343
1344
1345 /*=============================================================================
1346 || LOCAL PROCEDURE
1347 || get_segment_values
1348 ||
1349 || DESCRIPTION
1350 || Obtains address segment values
1351 ||
1352 || ARGUMENTS
1353 || st : address style
1354 ||
1355 || NOTE
1356 || By using the argument 'st' for conditional statement, address style
1357 || specific logic can be included in this procedure.
1358 ||
1359 ||
1360 =============================================================================*/
1361 PROCEDURE get_segment_values( st VARCHAR2, wd NUMBER ) IS
1362 BEGIN
1363
1364 /*
1365 766077.
1366 The German address style requires a blank line between the last address line
1367 and the postal code as well as the fully defined country name. To that end we
1368 will make address3 a blank line and append as much of address line 3 as will
1369 fit on address line 2. We will also assure that all of the the address
1370 segments will fit in the address box with out wordwrapping.
1371 */
1372
1373 IF country_code = 'DE'
1374 THEN source(IDX_ADDRESS1) := SUBSTR(address1, 1, wd);
1375
1376 /*
1377 1887485
1378 Need to add condition if address2 and address3 is null.
1379 Otherwise, there are two rows between address 1 and postal code.
1380 */
1381 IF address2 is null and address3 is null and address4 is null
1382 THEN
1383 source(IDX_ADDRESS2) := NULL;
1384 ELSE
1385 source(IDX_ADDRESS2) := SUBSTR(LTRIM(address2||' '||address3||' '||address4), 1, wd);
1386 /*bug4466872included address4*/
1387 END IF;
1388
1389 source(IDX_ADDRESS3) := ' ';
1390 source(IDX_ADDRESS4) := NULL;
1391 source(IDX_BILL_TO_LOCATION) :=
1392 SUBSTR(bill_to_location, 1, wd-LENGTH(customer_name)-3);
1393 ELSE source(IDX_ADDRESS1) := address1;
1394 source(IDX_ADDRESS2) := address2;
1395 source(IDX_ADDRESS3) := address3;
1396 source(IDX_ADDRESS4) := address4;
1397 source(IDX_BILL_TO_LOCATION) := bill_to_location;
1398 END IF;
1399
1400 source(IDX_CUSTOMER_NAME) := customer_name;
1401 /*
1402 source(IDX_ADDRESS1) := address1;
1403 source(IDX_ADDRESS2) := address2;
1404 source(IDX_ADDRESS3) := address3;
1405 source(IDX_ADDRESS4) := address4;
1406 */
1407 source(IDX_CITY) := city;
1408 source(IDX_COUNTY) := county;
1409
1410 /* province can be an alternative for state */
1411
1412 source(IDX_STATE) := NVL(state,province);
1413
1414 /* if province is used for state's source, source for province
1415 is set to NULL. */
1416
1417 IF state IS NOT NULL THEN
1418 source(IDX_PROVINCE) := province;
1419 ELSE
1420 source(IDX_PROVINCE) := NULL;
1421 END IF;
1422
1423
1424 source(IDX_POSTAL_CODE) := postal_code;
1425
1426
1427 /* get territory short name if it's not given */
1428
1429 IF territory_short_name IS NULL THEN
1430 source(IDX_TERRITORY_SHORT_NAME) := get_country_desc(
1431 country_code,
1432 default_country_code,
1433 default_country_desc,
1434 print_home_country_flag );
1435 ELSE
1436
1437 IF country_code <> NVL(default_country_code, 'xxxxxx' ) THEN
1438 source(IDX_TERRITORY_SHORT_NAME) := territory_short_name;
1439 ELSE
1440 IF print_home_country_flag = 'Y' THEN
1441 source(IDX_TERRITORY_SHORT_NAME) := default_country_desc;
1442 ELSE
1443 source(IDX_TERRITORY_SHORT_NAME) := '';
1444 END IF;
1445 END IF;
1446
1447 END IF;
1448
1449 source(IDX_FIRST_NAME) := first_name;
1450 source(IDX_LAST_NAME) := last_name;
1451 source(IDX_MAIL_STOP) := mail_stop;
1452
1453 END get_segment_values;
1454
1455 /*=============================================================================
1456 || LOCAL PROCEDURE
1457 || set_punctuations
1458 ||
1459 || DESCRIPTION
1460 || Sets punctuation marks for each address segment.
1461 || For NULL value segment, the punctuation mark is set to NULL.
1462 || Exceptional punctuation mark for pre-defined address segment pair is set
1463 || only if both segments are not NULL.
1464 ||
1465 =============================================================================*/
1466 PROCEDURE set_punctuations IS
1467
1468 x NUMBER(5); -- identifies exceptional punctuation mark
1469
1470 BEGIN
1471
1472 addr_seg(0) := 0;
1473 punc( addr_seg(0) ) := ''; -- referenced in 'arange_segment'
1474
1475 FOR i IN 1..addr_seg_num LOOP -- loop counter needs to be available
1476 -- till the last segment in order to
1477 -- set NULL to the last non-NULL
1478 -- segment for its punctuation mark
1479
1480 IF source( addr_seg(i) ) IS NULL THEN
1481 punc( addr_seg(i) ) := ''; -- punctuation mark for the NULL
1482 -- segment is set to NULL
1483 ELSIF i = last_nn_seg THEN
1484 punc( addr_seg(i) ) := ''; -- punctuation mark for the last
1485 -- non-NULL segment is set to NULL
1486 ELSE
1487
1488 /* if subsequent segment is also NOT NULL,
1489 then see if exceptional punctuation can be applied.
1490 if it is not applicable, default punctuation mark is used */
1491
1492 IF source( addr_seg(i+1) ) IS NOT NULL THEN
1493
1494 /* x identifies the exceptional punctuation mark.
1495 x = 0 indicates that exceptional punctuation mark is not
1496 applied but default punctuation mark is used */
1497
1498 x := 0;
1499
1500 FOR j IN 1..punc_exception_num LOOP
1501
1502 IF addr_seg(i) = punc_exception.seg1(j) AND
1503 addr_seg(i+1) = punc_exception.seg2(j) THEN
1504 x := j;
1505 exit;
1506 END IF;
1507
1508 END LOOP;
1509
1510 IF x <> 0 THEN
1511 punc(addr_seg(i)) := punc_exception.mark(x);
1512 ELSE
1513 punc(addr_seg(i)) := punc_default;
1514 END IF;
1515
1516 ELSE
1517 punc(addr_seg(i)) := punc_default;
1518 END IF;
1519
1520 END IF;
1521
1522 END LOOP;
1523
1524 END set_punctuations;
1525
1526
1527 /*=============================================================================
1528 || LOCAL PROCEDURE
1529 || decompose_segment
1530 ||
1531 || DESCRIPTION
1532 || Decomposes address segment into sub-segments and their associated
1533 || punctuation mark. Consecutive punctuation marks are not considered as
1534 || separated punctuation marks but as a combined single punctuation: the
1535 || length of this punctuation equals to the number of combined punctuation
1536 || marks.
1537 ||
1538 || Note that the address segment with no punctuation mark is not decomposed.
1539 ||
1540 || NOTE
1541 || Counter for the punctuation mark is not maintained; It can be derived from
1542 || sub-segment counter. Extra punctuator at the tail of the segment is
1543 || ignored if exists.
1544 ||
1545 || punctuation marks appeared in the beginning of the segment are not
1546 || considered as punctuation marks but as part of address sub-segment
1547 ||
1548 =============================================================================*/
1549 PROCEDURE decompose_segment IS
1550
1551 str VARCHAR2(300); -- address segment value to be processed
1552 c VARCHAR2(10); -- a character in address segment value
1553 punc_flag VARCHAR2(5); -- flag to indicate if a character is
1554 -- puncuation mark or not
1555 p_punc_flag VARCHAR2(5); -- previous punc_flag
1556 sub_num NUMBER(5); -- counts # of sub-segments
1557 array_offset NUMBER(5); -- array offset
1558 start_with_punc BOOLEAN; -- flag to indicate the appearance of
1559 -- punctuation mark in the beginning of the
1560 -- segment
1561 BEGIN
1562
1563 FOR i IN 1..addr_seg_num LOOP -- process each address segment
1564
1565 str := source(addr_seg(i));
1566
1567 sub_num := 0;
1568 array_offset := i * SUBSEG_NUM;
1569 p_punc_flag := 'xxx';
1570 start_with_punc := FALSE;
1571
1572
1573 FOR j IN 1..nvl( length(str), 0 ) LOOP -- process each character
1574 -- (if segment is null,
1575 -- this loop is ignored)
1576 c := substr( str, j, 1 );
1577
1578 punc_flag := 'N';
1579
1580 FOR k IN 1..punc_mark_num LOOP
1581
1582 IF c = punc_mark(k) THEN
1583 punc_flag := 'Y'; -- found punctuation mark
1584 exit;
1585 END IF;
1586
1587 END LOOP;
1588
1589
1590 IF p_punc_flag <> punc_flag THEN
1591
1592 /* when punc_flag status varies */
1593
1594 IF punc_flag = 'Y' THEN
1595
1596 /* punc_flag status varies from non-punctuation mark
1597 to punctuation mark. */
1598
1599 IF sub_num <> 0 THEN
1600
1601 /* when punctuation does not appear in the beginning */
1602
1603 punc_sub(array_offset+sub_num) := c;
1604
1605 ELSE
1606 /* a punctuation mark appeared in the beginning of
1607 the segment goes into the first source_sub */
1608
1609 source_sub(array_offset+1) := c;
1610
1611 start_with_punc := TRUE;
1612
1613 END IF;
1614
1615 ELSIF punc_flag = 'N' THEN
1616
1617 /* punc_flag status varies from punctuation mark
1618 to non-punctuation mark */
1619
1620 sub_num := sub_num + 1;
1621
1622 IF start_with_punc = TRUE THEN
1623
1624 /* if punctuation mark is already in source_sub */
1625
1626 source_sub(array_offset+1) :=
1627 source_sub(array_offset+1) || c;
1628
1629 start_with_punc := FALSE;
1630
1631 ELSE
1632 source_sub(array_offset+sub_num) := c;
1633 END IF;
1634
1635 END IF;
1636
1637 ELSE
1638 /* when punc_flag status remains same */
1639
1640 IF punc_flag = 'Y' THEN
1641
1642 IF sub_num <> 0 THEN
1643 punc_sub(array_offset+sub_num) :=
1644 punc_sub(array_offset+sub_num) || c;
1645 ELSE
1646
1647 /* if the punctuation mark appears in the beginning
1648 of the segment */
1649
1650 source_sub(array_offset+1) :=
1651 source_sub(array_offset+1) || c;
1652 END IF;
1653
1654 ELSIF punc_flag = 'N' THEN
1655 source_sub(array_offset+sub_num) :=
1656 source_sub(array_offset+sub_num) || c;
1657 END IF;
1658
1659 END IF;
1660
1661 p_punc_flag := punc_flag; -- keep previous punc_flag status
1662
1663 END LOOP;
1664
1665 source_sub_num(i) := sub_num; -- set # of sub-segments for each segment
1666
1667 END LOOP;
1668
1669 END decompose_segment;
1670
1671
1672 /*=============================================================================
1673 || LOCAL FUNCTION
1674 || display_order
1675 ||
1676 || DESCRIPTION
1677 || Returns display order of the given address segment index
1678 ||
1679 || ARGUMENTS
1680 || idx : address segment index
1681 ||
1682 || RETURN
1683 || display order of the address segment
1684 ||
1685 =============================================================================*/
1686 FUNCTION display_order( idx NUMBER ) return NUMBER IS
1687 BEGIN
1688
1689 FOR i IN 1..addr_seg_num LOOP
1690 IF addr_seg(i) = idx THEN
1691 return(i);
1692 END IF;
1693 END LOOP;
1694
1695 END display_order;
1696
1697
1698 /*=============================================================================
1699 || LOCAL PROCEDURE
1700 || convert_lb_seg
1701 ||
1702 || DESCRIPTION
1703 || Converts designator for the line break address segment candidate
1704 || from 'index' to 'display order'. Also obtains the first and the
1705 || last non-NULL segment among the whole address segment: these segment
1706 || are designated by display order.
1707 ||
1708 =============================================================================*/
1709 PROCEDURE convert_lb_seg IS
1710 BEGIN
1711
1712 /* obtain the first and the last non-NULL segment
1713 among the whole address segment */
1714
1715 first_nn_seg := 0;
1716 last_nn_seg := 0;
1717
1718 FOR i IN 1..addr_seg_num LOOP
1719
1720 IF source(addr_seg(i)) IS NOT NULL THEN
1721
1722 IF first_nn_seg = 0 THEN
1723 first_nn_seg := i;
1724 END IF;
1725
1726 last_nn_seg := i;
1727
1728 END IF;
1729
1730 END LOOP;
1731
1732
1733 /* convert designator for the line break address segment candidate
1734 from 'index' to 'display order' */
1735
1736 FOR i IN 1..lb_seg_num LOOP
1737
1738 lb_seg(i) := display_order( lb_seg(i) );
1739
1740 END LOOP;
1741
1742 END convert_lb_seg;
1743
1744
1745 /*=============================================================================
1746 || LOCAL FUNCTION
1747 || split_segment
1748 ||
1749 || DESCRIPTION
1750 || Splits the longer segment than box width into several pieces
1751 || by inserting line break characters at every box width
1752 ||
1753 || ARGUMENTS
1754 || p : display order of target segment
1755 ||
1756 || RETURNS
1757 || remaining line length (byte) after the segment split
1758 ||
1759 =============================================================================*/
1760 FUNCTION split_segment( p NUMBER ) return NUMBER IS
1761
1762 str_splitted VARCHAR2(300); -- splitted string
1763 r NUMBER(5); -- remaining line length after this function
1764 lb_num NUMBER(5); -- the number of line breaks inserted
1765
1766 BEGIN
1767
1768 /* insert line break character codes at every 'width' */
1769
1770 insert_lbs( source(addr_seg(p)), width, str_splitted, lb_num, r );
1771
1772
1773 source_splitted_num := source_splitted_num + 1;
1774
1775 source_splitted.p(source_splitted_num) := p;
1776 source_splitted.val(source_splitted_num) := str_splitted;
1777
1778 lb_seg_splitted_num := lb_seg_splitted_num + lb_num; -- update line break
1779 -- counter
1780
1781 return( r );
1782
1783 END split_segment;
1784
1785
1786 /*=============================================================================
1787 || LOCAL FUNCTION
1788 || split_subsegment
1789 ||
1790 || DESCRIPTION
1791 || Splits the longer sub-segment than box width into several pieces
1792 || by inserting line break characters at every box width
1793 ||
1794 || ARGUMENTS
1795 || p : display order of target segment
1796 || p_sub : order of target sub-segment
1797 ||
1798 || RETURNS
1799 || remaining line length (byte) after the sub-segment split
1800 ||
1801 =============================================================================*/
1802 FUNCTION split_subsegment( p NUMBER, p_sub NUMBER ) return NUMBER IS
1803
1804 str_splitted VARCHAR2(300); -- splitted string
1805 r NUMBER(5); -- remaining line length after this function
1806 lb_num NUMBER(5); -- the number of line breaks inserted
1807
1808 BEGIN
1809
1810 /* insert line break character codes at every 'width' */
1811
1812 insert_lbs(source_sub(p*SUBSEG_NUM+p_sub),width,str_splitted,lb_num,r);
1813
1814
1815 source_sub_splitted_num := source_sub_splitted_num + 1;
1816
1817 source_sub_splitted.p(source_sub_splitted_num) := p;
1818 source_sub_splitted.p_sub(source_sub_splitted_num) := p_sub;
1819 source_sub_splitted.val(source_sub_splitted_num) := str_splitted;
1820
1821 lb_subseg_splitted_num := lb_subseg_splitted_num + lb_num; -- update line
1822 -- break counter
1823
1824 return( r );
1825
1826 END split_subsegment;
1827
1828
1829 /*=============================================================================
1830 || LOCAL FUNCTION
1831 || arrange_subsegment
1832 ||
1833 || DESCRIPTION
1834 || Arranges the location of the address sub-segments such that:
1835 ||
1836 || 1) overflowed sub-segment is allocated in the next line
1837 || as long as it is not the first sub-segment and is not a part of the
1838 || first non-NULL segment in the line
1839 || 2) if the allocated sub-segment has longer length than box width,
1840 || the aloocated sub-segment is splitted
1841 ||
1842 || ARGUMENTS
1843 || first_nn_segment : display order of the first non-NULL segment in the line
1844 || p : display order of the segment that needs sub-segment
1845 || arrangement
1846 || r_in : remaining line length (byte) before this function
1847 ||
1848 || RETURN
1849 || remaining line length (byte) after arrangement
1850 ||
1851 =============================================================================*/
1852 FUNCTION arrange_subsegment( first_nn_segment NUMBER, p NUMBER, r_in NUMBER )
1853 return NUMBER IS
1854
1855 r NUMBER(5); -- remaining line length
1856 punc_len NUMBER(5); -- length of punctuation mark
1857 source_len NUMBER(5); -- length of address sub-segment
1858 array_offset NUMBER(5); -- array offset
1859
1860 BEGIN
1861
1862 r := r_in;
1863
1864 array_offset := p * SUBSEG_NUM;
1865
1866 punc_sub(array_offset+0) := punc(addr_seg(p-1)); -- set previous segment's
1867 -- punctuation mark
1868
1869 FOR i IN 1..source_sub_num(p) LOOP
1870
1871 IF r <> width THEN
1872
1873 /* when there already exists pre-occupied (sub)segment
1874 in the line */
1875
1876 -- bug2411369 : Changed to lengthb_m
1877 -- punc_len := nvl(lengthb(punc_sub(array_offset+i-1)),0);
1878 punc_len := nvl(lengthb_m(punc_sub(array_offset+i-1)),0);
1879 r := r - punc_len;
1880
1881 ELSE
1882 punc_len := 0;
1883 END IF;
1884
1885 -- bug2411369 : Changed to lengthb_m
1886 -- source_len := nvl(lengthb(source_sub(array_offset+i)),0);
1887 source_len := nvl(lengthb_m(source_sub(array_offset+i)),0);
1888
1889 r := r - source_len;
1890
1891
1892 IF r < 0 THEN
1893
1894 IF i = 1 THEN
1895
1896 IF p <> first_nn_segment THEN
1897
1898 /* if the overflowd sub-segment is the first sub-segment
1899 and is not the part of the first non-NULL segment in
1900 the line, then the previoius segment is selected as an
1901 extra line break address segment */
1902
1903 lb_seg_extra_num := lb_seg_extra_num + 1;
1904 lb_seg_extra(lb_seg_extra_num) := p-1;
1905
1906 END IF;
1907 ELSE
1908
1909 /* if the overflowed sub-segment is not the first sub-segment,
1910 then the previous sub-segment to the overflowed sub-segment
1911 is selected as a line break address sub-segment */
1912
1913 lb_subseg_num(p) := lb_subseg_num(p) + 1;
1914 lb_subseg(p*SUBSEG_LBS+lb_subseg_num(p)) := i-1;
1915
1916 lb_subseg_total_num := lb_subseg_total_num + 1;
1917
1918 END IF;
1919
1920 IF source_len > width THEN
1921
1922 /* if the overflowed sub-segment length exceeds the box width,
1923 then it is to be splitted */
1924
1925 r := split_subsegment( p, i );
1926
1927 ELSE
1928 r := width - source_len;
1929 END IF;
1930
1931
1932 END IF;
1933
1934 END LOOP;
1935
1936 return( r );
1937
1938 END arrange_subsegment;
1939
1940
1941 /*=============================================================================
1942 || LOCAL FUNCTION
1943 || search_alt_lb_seg
1944 ||
1945 || DESCRIPTION
1946 || Searchs alternative line break segment in the target line:
1947 || the search starts from 'b-1' to 'a' th address segment.
1948 ||
1949 || Criterias to chose an alternative line break address segment are
1950 || as follows:
1951 || 1. should not be on the line break addres segment list
1952 || 2. should not be on the extra line break address segment list
1953 || 3. must be selected from the line break address segment candidate list
1954 ||
1955 || ARGUMENTS
1956 || a : display order of address segment where search ends
1957 || b-1 : display order of address segment where search starts
1958 ||
1959 || RETURNS
1960 || 0 :
1961 || if search fails (this includes the case when the line consists
1962 || of only one segment)
1963 ||
1964 || display order of address segment:
1965 || if search succeeds
1966 ||
1967 ||
1968 =============================================================================*/
1969 FUNCTION search_alt_lb_seg( a NUMBER, b NUMBER ) return NUMBER IS
1970
1971 not_used BOOLEAN := TRUE; -- flag that evaluates criteria 1
1972 not_used_extra BOOLEAN := TRUE; -- flag that evaluates criteria 2
1973 listed BOOLEAN := FALSE; -- flag that evaluates criteria 3
1974
1975 BEGIN
1976
1977 IF a = b THEN
1978
1979 /* when the line consists of only one address segment */
1980
1981 return( 0 );
1982
1983 ELSE
1984 FOR i IN REVERSE a..b-1 LOOP
1985
1986 /* check if the segment is not on the line break address
1987 segment list */
1988
1989 FOR j IN 1..lb_seg_used_num LOOP
1990 IF i = lb_seg_used(j) THEN
1991 not_used := FALSE;
1992 exit;
1993 END IF;
1994 END LOOP;
1995
1996
1997 /* check if the segment is not on the extra line break address
1998 segment list */
1999
2000 FOR j IN 1..lb_seg_extra_num LOOP
2001 IF i = lb_seg_extra(j) THEN
2002 not_used_extra := FALSE;
2003 exit;
2004 END IF;
2005 END LOOP;
2006
2007
2008 /* check if the segment is of the line break address segment
2009 candidate */
2010
2011 FOR j IN 1..lb_seg_num LOOP
2012 IF i = lb_seg(j) THEN
2013 listed := TRUE;
2014 exit;
2015 END IF;
2016 END LOOP;
2017
2018
2019 /* the last non-NULL segment and segments after the last
2020 non-NULL segement should not become line break address
2021 segment. */
2022
2023 IF not_used = TRUE AND not_used_extra = TRUE AND listed = TRUE
2024 AND i < last_nn_seg THEN
2025
2026 /* alternatvie segment can be a NULL segment, but at least
2027 one non-NULL segment needs to be located before the
2028 segment in the line or the segment itself needs to be
2029 non NULL segment */
2030
2031 FOR k in a..i LOOP -- target should contain i itself
2032 IF source(addr_seg(k)) IS NOT NULL THEN
2033 return( i );
2034 END IF;
2035 END LOOP;
2036
2037 END IF;
2038
2039 END LOOP;
2040
2041 return( 0 );
2042
2043 END IF;
2044
2045 END search_alt_lb_seg;
2046
2047
2048 /*=============================================================================
2049 || LOCAL PROCEDURE
2050 || arrange_segment
2051 ||
2052 || DESCRIPTION
2053 || Checks if the line overflows and if so arranges the location of address
2054 || segments. This procedrue is for the Line Sub Dividing Step; Extra line
2055 || break address segments and the locations of other line break type (see
2056 || format_address_label function description) are sought here.
2057 ||
2058 || Selection of the extra line break segment is made via 'search_alt_lb_seg'
2059 || functioin. But if this search fails, then the previous segment to the
2060 || overflowed segment is chosed as an extra line break address segment
2061 || as long as the segment is not the first non-NULL segment in the line.
2062 ||
2063 || ARGUMENTS
2064 || a : display order of the first segment in the line
2065 || b : display order of the last segment in the line
2066 ||
2067 =============================================================================*/
2068 PROCEDURE arrange_segment( a NUMBER, b NUMBER ) IS
2069
2070 r NUMBER(5); -- remaining length in the line
2071 previous_r NUMBER(5); -- previous 'r'
2072 p NUMBER(5); -- counter for character position
2073 source_len NUMBER(5); -- length of address segment
2074 punc_len NUMBER(5); -- length of punctuation mark
2075 alt_lb_seg_pos NUMBER(5); -- alternative line break address segment
2076 first_nn_segment NUMBER(5); -- the first non-NULL segment in the line
2077
2078 BEGIN
2079
2080 p := a; -- set first segment to be examined
2081
2082 previous_r := width;
2083 r := width;
2084
2085 first_nn_segment := 0;
2086
2087 LOOP
2088
2089 IF r <> width THEN
2090
2091 /* add length of punctuation mark when some segments already
2092 exist in the line. this part can be executed even if segment
2093 value is NULL */
2094
2095 -- bug2411369 : Changed to lengthb_m
2096 -- punc_len := nvl(lengthb(punc(addr_seg(p-1))),0);
2097 punc_len := nvl(lengthb_m(punc(addr_seg(p-1))),0);
2098
2099 r := r - punc_len;
2100
2101 ELSE
2102 punc_len := 0;
2103 END IF;
2104
2105 -- bug2411369 : Changed to lengthb_m
2106 -- source_len := nvl(lengthb(source(addr_seg(p))),0);
2107 source_len := nvl(lengthb_m(source(addr_seg(p))),0);
2108
2109 r := r - source_len;
2110
2111
2112 /* find the first non-NULL segment in the line */
2113
2114 IF first_nn_segment = 0 AND source_len <> 0 THEN
2115 first_nn_segment := p;
2116 END IF;
2117
2118
2119 IF r < 0 AND source_len > 0 THEN -- ignore the case when the
2120 -- line overflow is caused by
2121 -- punctuation mark
2122
2123 /* when the line is overflowed */
2124
2125 IF first_nn_segment = 0 THEN
2126
2127 /* if the line overflow is caused by punctuation mark
2128 and the first non-NULL segment is still not found */
2129
2130 first_nn_segment := a;
2131
2132 END IF;
2133
2134
2135 /* search for alternative line break address segment */
2136
2137 alt_lb_seg_pos := search_alt_lb_seg( first_nn_segment, p );
2138
2139 IF alt_lb_seg_pos <> 0 THEN
2140
2141 /* when alternative line break address segment is found,
2142 it becomes an extra line break address segment */
2143
2144 lb_seg_extra_num := lb_seg_extra_num + 1;
2145 lb_seg_extra(lb_seg_extra_num) := alt_lb_seg_pos;
2146
2147 p := alt_lb_seg_pos+1; -- update segment counter
2148
2149 -- bug2411369 : Changed to lengthb_m
2150 -- source_len:=nvl(lengthb(source(addr_seg(p))),0);
2151 source_len:=nvl(lengthb_m(source(addr_seg(p))),0);
2152
2153
2154 IF source_len > width THEN
2155
2156 /* when the length of the next segment to the alternative
2157 line break address segment exceeds the box width */
2158
2159 IF source_sub_num(p) = 1 THEN
2160
2161 /* when the segment can not be decomposed,
2162 it is to be splitted */
2163
2164 r := split_segment( p );
2165
2166 ELSE
2167 /* when the segment can be decomposed,
2168 the locations of the decomposed address
2169 sub-segments are to be arranged.
2170 in 'arrange_subsegment' function, the p-1 th segment
2171 should not be selected as an extra line break
2172 address segment since the p th segment is the first
2173 non-NULL segment in the line */
2174
2175 r := arrange_subsegment( p, p, width );
2176
2177 END IF;
2178 ELSE
2179 r := width - source_len;
2180 END IF;
2181
2182 ELSE
2183 /* when failed to find an alternative line break address
2184 segment */
2185
2186 IF source_sub_num(p) = 1 THEN
2187
2188 /* when the overflowed segment can not be decomposed */
2189
2190 IF p <> first_nn_segment THEN
2191
2192 /* only when the p th segment is not the first
2193 non-NULL segment in the line, the p-1 th segment
2194 can become an extra line break address segment */
2195
2196 lb_seg_extra_num := lb_seg_extra_num + 1;
2197 lb_seg_extra(lb_seg_extra_num) := p-1;
2198
2199 END IF;
2200
2201 IF source_len > width THEN
2202
2203 /* when the length of the overflowed segment exceeds
2204 the box width, the segment is to be splitted */
2205
2206 r := split_segment( p );
2207
2208 ELSE
2209 r := width - source_len;
2210 END IF;
2211
2212 ELSE
2213
2214 /* when the overflowed segment can be decomposed,
2215 the locations of the decomposed address sub-segments
2216 are to be arranged. in 'arrange_subsegment' function,
2217 the p th segment becomes an extra line break address
2218 segment only if the p th segment is not the first
2219 non-NULL segment in the line */
2220
2221 r := arrange_subsegment( first_nn_segment, p, previous_r );
2222
2223 END IF;
2224
2225 END IF;
2226
2227 END IF;
2228
2229
2230 previous_r := r;
2231
2232 p := p + 1;
2233
2234 IF p > b OR p > last_nn_seg THEN
2235
2236 /* exit loop when segment counter reaches the last segment
2237 in the line or the last non-NULL segment in the whole
2238 address segments */
2239
2240 exit;
2241 END IF;
2242
2243 END LOOP;
2244
2245 END arrange_segment;
2246
2247
2248 /*=============================================================================
2249 || LOCAL FUNCTION
2250 || search_nn_segment
2251 ||
2252 || DESCRIPTION
2253 || Searches the right most non-NULL value address segment in the line.
2254 ||
2255 || ARGUMENTS
2256 || a: position to end the search
2257 || b-1: position to start the search
2258 ||
2259 || RETURN
2260 || 0:
2261 || 1) if a = b
2262 || 2) if targeted line cosists of only null segments
2263 ||
2264 || display order of the right most non-NULL value address segment:
2265 || else
2266 ||
2267 =============================================================================*/
2268 FUNCTION search_nn_segment( a NUMBER, b NUMBER ) return NUMBER IS
2269 BEGIN
2270
2271 IF a = b THEN
2272 return( 0 );
2273 END IF;
2274
2275 FOR i IN REVERSE a..b-1 LOOP
2276 IF source(addr_seg(i)) IS NOT NULL THEN
2277 return(i);
2278 END IF;
2279 END LOOP;
2280
2281 return( 0 );
2282
2283 END search_nn_segment;
2284
2285
2286 /*=============================================================================
2287 || LOCAL FUNCTION
2288 || concatenate_segments
2289 ||
2290 || DESCRIPTION
2291 || 1) Substitute the longer address sub-segment than the box width with the
2292 || line break characters inserted one
2293 || 2) Add line break characters to the line break address sub-segment
2294 || 3) Substitute the longer address segment than the box width with the
2295 || line break characters inserted one
2296 || 4) Add line break characters to the line break address segments
2297 ||
2298 || RETURN
2299 || concatenated address string in which a number of punctuation marks and
2300 || line break character codes are inserted; places where line break character
2301 || code are inserted should be determined before this function.
2302 ||
2303 =============================================================================*/
2304 FUNCTION concatenate_segments return VARCHAR2 IS
2305
2306 lb_found BOOLEAN; -- flag to indicate the detection of line break
2307 -- address sub-segment
2308 alt_source VARCHAR2(300); -- line break character inserted address
2309 -- segment
2310 previous_lb_pos NUMBER(5); -- display order of the previous line break
2311 -- address segment
2312 nn_segment NUMBER(5); -- the right most non-NULL address segment
2313 -- in the line
2314 answer VARCHAR2(4000); -- concatenated address string
2315
2316 BEGIN
2317
2318 /* 1) substitute the longer address sub-segment than the box width with the
2319 line break characters inserted one */
2320
2321 FOR i IN 1..source_sub_splitted_num LOOP
2322
2323 source_sub(source_sub_splitted.p(i)*SUBSEG_NUM +
2324 source_sub_splitted.p_sub(i) ):=source_sub_splitted.val(i);
2325
2326 END LOOP;
2327
2328
2329 /* 2) Add line break characters to the line break address sub-segment */
2330
2331 FOR i IN first_nn_seg..last_nn_seg LOOP -- limit the range of target
2332 -- address segments
2333
2334 IF source_sub_num(i) > 1 THEN
2335
2336 /* when the address segment has multiple sub-segments */
2337
2338 alt_source := '';
2339
2340 /* look for line break address sub-segments;
2341 search for the last sub-segment is not necessary */
2342
2343 FOR j IN 1..source_sub_num(i)-1 LOOP -- 0 is NOT necessary as LB
2344 -- at the beginning of the
2345 -- sub-segment is not allowed
2346
2347 lb_found := FALSE;
2348
2349 FOR k IN 1..lb_subseg_num(i) LOOP
2350
2351 IF lb_subseg(i*SUBSEG_LBS+k) = j THEN
2352
2353 lb_found := TRUE; -- found line break address
2354 -- sub-segment
2355 exit;
2356
2357 END IF;
2358
2359 END LOOP;
2360
2361
2362 /* attach line break character to the line break sub-segment
2363 and punctuation mark to the rest of the sub-segments */
2364
2365 IF lb_found THEN
2366
2367 alt_source := alt_source || source_sub(i*SUBSEG_NUM+j) ||
2368 '
2369 ';
2370 ELSE
2371 alt_source := alt_source || source_sub(i*SUBSEG_NUM+j) ||
2372 punc_sub(i*SUBSEG_NUM+j);
2373 END IF;
2374
2375
2376 END LOOP;
2377
2378
2379 /* the last sub-segment should not have punctuation mark */
2380
2381 alt_source := alt_source ||
2382 source_sub(i*SUBSEG_NUM+source_sub_num(i));
2383
2384
2385 source(addr_seg(i)) := alt_source;
2386
2387 END IF;
2388
2389 END LOOP;
2390
2391
2392 /* 3) Substitute the longer address segment than the box width with the
2393 line break characters inserted one */
2394
2395 FOR i IN 1..source_splitted_num LOOP
2396 source(addr_seg(source_splitted.p(i))) := source_splitted.val(i);
2397 END LOOP;
2398
2399
2400 /* 4) Add line break characters to the line break address segments */
2401
2402 answer := '';
2403 previous_lb_pos := 0;
2404
2405 FOR i IN first_nn_seg..last_nn_seg LOOP -- limit the range of target
2406 -- address segments
2407
2408 lb_found := FALSE;
2409
2410
2411 /* examine line break segments obtained in the line
2412 dividing step */
2413
2414 FOR j IN 1..lb_seg_used_num LOOP
2415
2416 IF lb_seg_used(j) = i THEN
2417 lb_found := TRUE;
2418 exit;
2419 END IF;
2420
2421 END LOOP;
2422
2423
2424 /* examine line break segments obtained in the line
2425 sub-dividing step */
2426
2427 IF lb_found = FALSE THEN
2428 FOR j IN 1..lb_seg_extra_num LOOP
2429
2430 IF lb_seg_extra(j) = i THEN
2431 lb_found := TRUE;
2432 exit;
2433 END IF;
2434
2435 END LOOP;
2436 END IF;
2437
2438
2439 IF lb_found THEN
2440
2441 /* when line break address segments is found */
2442
2446 trim unnecessary punctuation mark at the end of the line */
2443 IF source(addr_seg(i)) IS NULL THEN
2444
2445 /* if line break address segment is a NULL segment, need to
2447
2448 /* search for the right most non-NULL segment in the line.
2449 if not found, LB is not inserted */
2450
2451 nn_segment := search_nn_segment( previous_lb_pos+1, i );
2452
2453 IF nn_segment <> 0 THEN
2454
2455 /* trim the right most non-null segment punctuation mark
2456 and add line break character at the end */
2457
2458 answer:=rtrim(answer,punc(addr_seg(nn_segment)))||'
2459 ';
2460
2461 previous_lb_pos := i; -- keep the last line break
2462 -- address segment
2463
2464 END IF;
2465
2466 ELSE
2467
2468 /* if the line break address segment is not NULL segment,
2469 simply add line break character at the end */
2470
2471 answer := answer || source(addr_seg(i)) || '
2472 ';
2473
2474 previous_lb_pos := i; -- keep the last line break
2475 -- address segment
2476
2477 END IF;
2478
2479 ELSE
2480
2481 /* for non line breake address segment, add punctuator at the end */
2482
2483 answer := answer || source(addr_seg(i)) || punc(addr_seg(i));
2484
2485 END IF;
2486
2487
2488 END LOOP;
2489
2490 return( answer );
2491
2492 END concatenate_segments;
2493
2494
2495 /*=============================================================================
2496 || LOCAL FUNCTION
2497 || truncate_segments
2498 ||
2499 || DESCRIPTION
2500 || 1) Concatenates all non-NULL address segment values while putting
2501 || punctuation mark in between the segments.
2502 || 2) Insert a line break character into the concatenated string at every
2503 || 'width'
2504 || 3) Truncate the line break inserted string such that it can fit in the
2505 || address box region
2506 ||
2507 || RETURN
2508 || string after 1),2) and 3) are processed
2509 ||
2510 =============================================================================*/
2511 FUNCTION truncate_segments return VARCHAR2 IS
2512
2513 lb_num NUMBER(5); -- # of line breaks inserted
2514 remaining NUMBER(5); -- remaining line length (byte)
2515 dummy_l NUMBER(5); -- dummy
2516 concatenated VARCHAR2(4000);-- concatenated address segment value
2517 tmp VARCHAR2(4000); -- temporary string
2518 answer VARCHAR2(4000); -- concatenated and line break inserted address
2519 -- string that can fit in the address box
2520 BEGIN
2521
2522 concatenated := '';
2523
2524 /* simply concatenate all non-NULL address segments while
2525 connecting each segment with associated punctuation mark */
2526
2527 FOR i IN first_nn_seg..last_nn_seg LOOP
2528 concatenated := concatenated||source(addr_seg(i))||punc(addr_seg(i));
2529 END LOOP;
2530
2531
2532 /* insert a line break character into the concatenated string at every
2533 'width' */
2534
2535 insert_lbs( concatenated, width, answer, lb_num, remaining );
2536
2537
2538 /* truncate the line break character inserted string such that it can
2539 fit in the address box */
2540
2541 IF lb_num > lb_seg_limit_num THEN
2542
2543 tmp := answer;
2544
2545 -- bug2411369 : Changed to width_m
2546 -- substrb_m( tmp, 1, instrb( tmp, '
2547 width_m( tmp, 1, instrb( tmp, '
2548 ', 1, lb_seg_limit_num+1 ) - 1,
2549 answer, dummy_l );
2550
2551 END IF;
2552
2553 return( answer );
2554
2555 END truncate_segments;
2556
2557
2558 /*=============================================================================
2559 || LOCAL PROCEDURE
2560 || init_counter
2561 ||
2562 || DESCRIPTION
2563 || Initializes PL/SQL tables, Records, and counter variables
2564 ||
2565 =============================================================================*/
2566 PROCEDURE init_counter IS
2567
2568 empty_num OneDimNum;
2569 empty_splitted Splitted;
2570
2571 BEGIN
2572
2573 lb_seg_extra_num := 0;
2574
2575 lb_seg_splitted_num := 0;
2576 lb_subseg_splitted_num := 0;
2577
2578 FOR i IN 1..addr_seg_num LOOP
2579 lb_subseg_num(i) := 0;
2580 END LOOP;
2581 lb_subseg_total_num := 0;
2582
2583 source_splitted_num := 0;
2584 source_sub_splitted_num := 0;
2585
2586 lb_seg_extra := empty_num;
2587 lb_seg_used := empty_num;
2588 lb_subseg := empty_num;
2589
2590 source_splitted := empty_splitted;
2591 source_sub_splitted := empty_splitted;
2592
2593 END init_counter;
2594
2595
2596 /*=============================================================================
2597 || LOCAL FUNCTION
2598 || filter_lb_seg
2599 ||
2600 || DESCRIPTION
2601 || Eliminates line break address segment candidates whose line
2602 || consist of NULL segment(s)
2603 ||
2604 || ARGUMENTS
2605 || n : the number of line break address segment candidates to be considered
2606 ||
2607 || RETURN
2608 || the number of line break address segment candidates after the elimination
2609 ||
2610 =============================================================================*/
2611 FUNCTION filter_lb_seg( n NUMBER ) return NUMBER IS
2612
2613 lb_seg_tmp OneDimNum; -- temporary table
2614 lb_pos NUMBER(5); -- display order of the line break address
2615 -- segment
2616 previous_lb_pos NUMBER(5); -- previous lb_pos
2617 non_null_line BOOLEAN; -- flag to indicate if the line is NULL
2618 tmp NUMBER(5); -- temporary variable used for sorting
2619 x NUMBER(5); -- new lb_seg_used_num
2620
2621 BEGIN
2622
2623 FOR i IN 1..n LOOP
2624 lb_seg_tmp(i) := lb_seg(i);
2625 END LOOP;
2626
2627 /* sort line break address segment */
2628
2629 FOR i IN 1..n-1 LOOP
2630 FOR j IN 1..n-1 LOOP
2631 IF lb_seg_tmp(j) > lb_seg_tmp(j+1) THEN
2632 tmp := lb_seg_tmp(j);
2633 lb_seg_tmp(j) := lb_seg_tmp(j+1);
2634 lb_seg_tmp(j+1) := tmp;
2635 END IF;
2636 END LOOP;
2637 END LOOP;
2638
2639
2640 /* exclude line break address segment candidates whose line consist of
2641 NULL segment(s) */
2642
2643 previous_lb_pos := 0;
2644 x := 0;
2645
2646 FOR i IN 1..n LOOP
2647
2648 lb_pos := lb_seg_tmp(i);
2649 non_null_line := FALSE;
2650
2651
2652 /* the last non-NULL segment and segments after the last non-NULL
2653 segment should not become a line break address segment */
2654
2655 IF lb_pos < last_nn_seg THEN
2656
2657 /* check if line has at least 1 non-NULL segment */
2658
2659 FOR j IN previous_lb_pos+1..lb_pos LOOP
2660
2661 IF source(addr_seg(j)) IS NOT NULL THEN
2662 non_null_line := TRUE; -- found non-NULL segment
2663 exit;
2664 END IF;
2665
2666 END LOOP;
2667
2668 IF non_null_line = TRUE THEN
2669
2670 x := x + 1;
2671
2672 lb_seg_used(x) := lb_pos; -- save LB position
2673
2674 previous_lb_pos := lb_pos; -- keep previous LB position
2675
2676 END IF;
2677
2678 ELSE
2679 previous_lb_pos := lb_pos; -- keep previous LB position
2680 END IF;
2681
2682 END LOOP;
2683
2684 return( x );
2685
2686 END filter_lb_seg;
2687
2688
2689 /*=============================================================================
2690 || format_address_label execution section
2691 =============================================================================*/
2692 BEGIN
2693
2694 /* uptake tca name and address formatting */
2695
2696 l_fmt_bkwd_compatible := FND_PROFILE.VALUE('HZ_FMT_BKWD_COMPATIBLE');
2697 IF l_fmt_bkwd_compatible <> 'Y' THEN
2698 uptake_name_address_formatting (
2699 p_address1 => address1,
2700 p_address2 => address2,
2701 p_address3 => address3,
2702 p_address4 => address4,
2703 p_city => city,
2704 p_county => county,
2705 p_state => state,
2706 p_province => province,
2707 p_postal_code => postal_code,
2708 p_country_code => country_code,
2709 p_customer_name => customer_name,
2710 p_bill_to_location => bill_to_location,
2711 p_first_name => first_name,
2712 p_last_name => last_name,
2713 p_mail_stop => mail_stop,
2714 p_default_country_code => default_country_code,
2715 p_print_home_country_flag => print_home_country_flag,
2716 x_formatted_result => l_formatted_result);
2717
2718 IF l_formatted_result is not null THEN
2719 return l_formatted_result;
2720 END IF;
2721
2722 END IF;
2723
2724 /* if address_style is not given, obtain from FND_TERRITORIES_vl
2725 database table via county_code: if country_code is NULL
2726 then use default_country_code. */
2727
2728 IF address_style IS NULL THEN
2729 style := get_address_style(NVL(country_code,default_country_code));
2730 ELSE
2731 style := address_style;
2732 END IF;
2733
2734
2735 set_definitions( style ); -- set address style specific definitions
2736
2737 get_segment_values( style, width ); -- get address segment values
2738
2739 convert_lb_seg; -- convert designator for the line break
2740 -- address segment candidate from 'index' to
2741 -- 'display order'. also find the first and
2742 -- last non-NULL address segment among the
2743 -- whole segments
2744
2745 /* abort the rest of the processes and return NULL if all address segments
2746 are NULL */
2747
2748 IF first_nn_seg = 0 AND last_nn_seg = 0 THEN
2749 return(NULL);
2750 END IF;
2751
2752 set_punctuations; -- set address segment punctuation marks
2753
2754 decompose_segment; -- decompose address segment into sub-segments
2755
2756
2757 /* define initial # of line breaks and the maximum # of line breaks */
2758
2759 lb_seg_initial_num := height_min - 1;
2760 lb_seg_limit_num := height_max - 1;
2761
2762
2763 /* # of breaks must be less than # of available line break candidates */
2764
2765 IF lb_seg_initial_num > lb_seg_num THEN
2766 lb_seg_initial_num := lb_seg_num;
2767 END IF;
2768
2769
2770
2771 /* Line Dividing Step */
2772
2773 FOR i IN REVERSE 0..lb_seg_initial_num LOOP
2774
2775 init_counter; -- initialize counter
2776
2777 lb_seg_used_num := filter_lb_seg( i ); -- filter line break address
2778 -- segmemt candidates
2779
2780 first_segment := first_nn_seg; -- the first segment being
2781 -- processed is the first
2782 -- non-NULL segment
2783
2784
2785 /* process lines except for the last line */
2786
2787 FOR j IN 1..lb_seg_used_num LOOP
2788
2789 last_segment := lb_seg_used(j); -- the last segment in the line
2790 -- is set to the line break
2791 -- address segment
2792
2793 /* Line Sub Dividing Step */
2794
2795 arrange_segment( first_segment, last_segment );
2796 first_segment := last_segment + 1; -- update the first segment
2797 -- in the line
2798
2799 END LOOP;
2800
2801 /* process the last line */
2802
2803 arrange_segment( first_segment, last_nn_seg );
2804
2805
2806 /* If resulted total # of line breaks is smaller or equal to
2807 the limit, then call the segment concatenation
2808 function and exit. Otherwise restart the line dividing
2809 step with a decreased number of line break address segments. */
2810
2811 IF lb_seg_used_num + lb_seg_extra_num + lb_seg_splitted_num +
2812 lb_subseg_total_num + lb_subseg_splitted_num
2813 <= lb_seg_limit_num THEN
2814
2815 return( concatenate_segments );
2816
2817 END IF;
2818
2819 END LOOP;
2820
2821 /* when every line dividing step fails, address segments are to be
2822 truncated */
2823
2824 return( truncate_segments );
2825
2826 END format_address_label;
2827
2828
2829 /*=============================================================================
2830 || PUBLIC FUNCTION
2831 || format_address
2832 ||
2833 || DESCRIPTION
2834 || This address formatting function is specific for AR. It has AR specific
2835 || parameter being passed to, print_default_attn and AR specific message
2836 || retrieval steps. The function calls format_address_label() to use the
2837 || address formatting core algorithm.
2838 ||
2839 || ARGUMENTS
2840 || address_style : address format style (if NULL is given,
2841 || the value is retrieved via country_code)
2842 || address1 : address line 1
2843 || address2 : address line 2
2844 || address3 : address line 3
2845 || address4 : address line 4
2846 || city : name of city
2847 || county : name of county
2848 || state : name of state
2849 || province : name of province
2850 || postal_code : postal code
2851 || territory_short_name : territory short name (if NULL is given,
2852 || the value is retrieved via country_code)
2853 || country_code : country code
2854 || customer_name : customer name
2855 || bill_to_location : bill to location
2856 || first_name : contact first name
2857 || last_name : contact last name
2858 || mail_stop : mailing informatioin
2859 || default_country_code : default country code
2860 || default_country_desc : default territory short name
2861 || print_home_country_flag : flag to control printing of home county
2862 || ('Y': print, 'N': don't print)
2863 || print_default_attn_flag : flag to control printing of default
2864 || attention message when both contact first
2865 || and last name are NULL
2866 || ('Y': print, 'N': don't print)
2867 || width : address box width
2868 || height_min : address box height (desired)
2869 || height_max : address box height (limit)
2870 ||
2871 || RETURN
2872 || formatted address string
2873 ||
2874 || NOTES
2875 || The format_address function was originally in ARP_ADDR_PKG being created
2876 || by AROADDRS.pls and AROADDRB.pls (which are part of 10SC patches).
2877 || Because the function was decided to be also used in the Japan
2878 || Localization Phase 1 patch (10.4, 10.5, and 10.6), it was necessary
2879 || to make the function independent from other 10SC packages and tables.
2880 || This is why this new PL/SQL script file has been created.
2881 ||
2882 || The format_address function in ARP_ADDR_PKG still remains but its
2883 || contents consists of only a single call to the 'format_address'
2884 || function in ARP_ADDR_LABEL_PKG.
2885 ||
2886 || > In version 70.18 of AROADDRB.pls, the 'format_address' function in
2887 || ARP_ADDR_PKG was changed such that it no longer calls the
2888 || 'format_address' in ARP_ADDR_LABEL_PKG.
2889 ||
2890 || > To support the call from version 70.13 to 70.17 of AROADDRB.pls,
2891 || the function API for versoin 70.1 of arp_addr_label_pkg.format_address()
2892 || is maintained by an overloaded function. (The API for
2893 || arp_addr_label_pkg.format_address() has been changed in version
2894 || 70.2 of AROADDLS.pls and AROADDLB.pls: a new parameter has been
2895 || added, see the format_address description below.)
2896 ||
2897 ||--------------------------------------------------------------------------
2898 || Modifications....
2899 ||
2900 || Dec 13 1997 Caroline M Clyde Changed f_name to a length of 80.
2901 || If the first name doesn't exist, the
2902 || code dumps AR_LOOKUPS.meaning into
2903 || the field (meaning is 80 chars long).
2904 ||
2905 ============================================================================*/
2906 FUNCTION format_address( address_style IN VARCHAR2,
2907 address1 IN VARCHAR2,
2908 address2 IN VARCHAR2,
2909 address3 IN VARCHAR2,
2910 address4 IN VARCHAR2,
2911 city IN VARCHAR2,
2912 county IN VARCHAR2,
2913 state IN VARCHAR2,
2914 province IN VARCHAR2,
2915 postal_code IN VARCHAR2,
2916 territory_short_name IN VARCHAR2,
2917 country_code IN VARCHAR2,
2918 customer_name IN VARCHAR2,
2919 bill_to_location IN VARCHAR2,
2920 first_name IN VARCHAR2,
2921 last_name IN VARCHAR2,
2922 mail_stop IN VARCHAR2,
2923 default_country_code IN VARCHAR2,
2924 default_country_desc IN VARCHAR2,
2925 print_home_country_flag IN VARCHAR2,
2926 print_default_attn_flag IN VARCHAR2,
2927 width IN NUMBER,
2928 height_min IN NUMBER,
2929 height_max IN NUMBER
2930 )return VARCHAR2 IS
2931
2932 style fnd_territories_vl.address_style%TYPE; --Bug2107418
2933 f_name VARCHAR2(80); -- contact first name
2934 l_name VARCHAR2(50); -- contact last name
2935 m_stop VARCHAR2(80); -- mail stop label
2936
2937 BEGIN
2938
2939 /* Bug 2411369 : Get character set of DB to handle UTF8 */
2940
2941 select decode(value , 'UTF8' , 3 , 2 )
2942 into multi_char_byte
2943 from nls_database_parameters
2944 where parameter = 'NLS_CHARACTERSET';
2945
2946
2947 /* if address_style is not given, obtain from FND_TERRITORIES_vl
2948 database table via county_code: if country_code is NULL
2949 then use default_country_code. */
2950
2951 IF address_style IS NULL THEN
2952 style := get_address_style(NVL(country_code,default_country_code));
2953 ELSE
2954 style := address_style;
2955 END IF;
2956
2957
2958 /* if both first_name and last_name are NULL and
2959 print_default_attn_flag is 'Y', then retreive default attn. */
2960
2961 IF first_name IS NULL AND last_name IS NULL AND
2962 print_default_attn_flag = 'Y' THEN
2963 f_name := get_default_attn;
2964 l_name := '';
2965 ELSE
2966 f_name := first_name;
2967 l_name := last_name;
2968 END IF;
2969
2970
2971 IF mail_stop IS NOT NULL THEN
2972 IF style = 'JP' THEN
2973 m_stop := mail_stop; -- No mail stop label for JP
2974 ELSE
2975 m_stop := get_mail_stop_label || mail_stop;
2976 END IF;
2977 ELSE
2978 m_stop := '';
2979 END IF;
2980
2981
2982 return( arp_addr_label_pkg.format_address_label(
2983 style,
2984 address1,
2985 address2,
2986 address3,
2987 address4,
2988 city,
2989 county,
2990 state,
2991 province,
2992 postal_code,
2993 territory_short_name,
2994 country_code,
2995 customer_name,
2996 bill_to_location,
2997 f_name,
2998 l_name,
2999 m_stop,
3000 default_country_code,
3001 default_country_desc,
3002 print_home_country_flag,
3003 width,
3004 height_min,
3005 height_max ));
3006
3007 END format_address;
3008
3009
3010 /*=============================================================================
3011 || PUBLIC FUNCTION
3012 || format_address
3013 ||
3014 || DESCRIPTION
3015 || Difference between this overloaded format_address function and the
3016 || above format_address function is 1) parameter default values have been
3017 || omitted and 2) a new parameter, 'bill_to_location', has been added to
3018 || the parameter list.
3019 ||
3020 || This overloaded format_address function exists to support the call
3021 || from arp_addr_pkg.format_address() being created by AROADDRB.pls (version
3022 || 70.13 to 70.17); Versoin 70.13 to 70.17 of AROADDRB.pls do not pass a
3023 || value for the new parameter 'bill_to_location'.
3024 ||
3025 || ARGUMENTS
3026 || address_style : address format style (if NULL is given,
3027 || the value is retrieved via country_code)
3028 || address1 : address line 1
3029 || address2 : address line 2
3030 || address3 : address line 3
3031 || address4 : address line 4
3032 || city : name of city
3033 || county : name of county
3034 || state : name of state
3035 || province : name of province
3036 || postal_code : postal code
3037 || territory_short_name : territory short name (if NULL is given,
3038 || the value is retrieved via country_code)
3039 || country_code : country code
3040 || customer_name : customer name
3041 || first_name : contact first name
3042 || last_name : contact last name
3043 || mail_stop : mailing informatioin
3044 || default_country_code : default country code
3045 || default_country_desc : default territory short name
3046 || print_home_country_flag : flag to control printing of home county
3047 || ('Y': print, 'N': don't print)
3048 || print_default_attn_flag : flag to control printing of default
3049 || attention message when both contact first
3050 || and last name are NULL
3051 || ('Y': print, 'N': don't print)
3052 || width : address box width
3053 || height_min : address box height (desired)
3054 || height_max : address box height (limit)
3055 ||
3056 || RETURN
3057 || formatted address string
3058 ||
3059 ============================================================================*/
3060 FUNCTION format_address( address_style IN VARCHAR2,
3061 address1 IN VARCHAR2,
3062 address2 IN VARCHAR2,
3063 address3 IN VARCHAR2,
3064 address4 IN VARCHAR2,
3065 city IN VARCHAR2,
3066 county IN VARCHAR2,
3067 state IN VARCHAR2,
3068 province IN VARCHAR2,
3069 postal_code IN VARCHAR2,
3070 territory_short_name IN VARCHAR2,
3071 country_code IN VARCHAR2 default NULL,
3072 customer_name IN VARCHAR2 default NULL,
3073 first_name IN VARCHAR2 default NULL,
3074 last_name IN VARCHAR2 default NULL,
3075 mail_stop IN VARCHAR2 default NULL,
3076 default_country_code IN VARCHAR2 default NULL,
3077 default_country_desc IN VARCHAR2 default NULL,
3078 print_home_country_flag IN VARCHAR2 default 'Y',
3079 print_default_attn_flag IN VARCHAR2 default 'N',
3080 width IN NUMBER default 1000,
3081 height_min IN NUMBER default 1,
3082 height_max IN NUMBER default 1
3083 )return VARCHAR2 IS
3084 BEGIN
3085
3086 return( arp_addr_label_pkg.format_address(
3087 address_style,
3088 address1,
3089 address2,
3090 address3,
3091 address4,
3092 city,
3093 county,
3094 state,
3095 province,
3096 postal_code,
3097 territory_short_name,
3098 country_code,
3099 customer_name,
3100 NULL,
3101 first_name,
3102 last_name,
3103 mail_stop,
3104 default_country_code,
3105 default_country_desc,
3106 print_home_country_flag,
3107 print_default_attn_flag,
3108 width,
3109 height_min,
3110 height_max ));
3111
3112 END format_address;
3113
3114
3115 END arp_addr_label_pkg;
[Home] [Help]