[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select address_style into st from fnd_territories_vl
where territory_code = country_code;
select territory_short_name into description
from fnd_territories_vl
where territory_code = country_code;
select meaning
into attn
from ar_lookups
where lookup_type = 'ADDRESS_LABEL'
and lookup_code = 'ATTN_DEFAULT_MSG';
select meaning into label from ar_lookups
where lookup_type = 'ADDRESS_LABEL' and
lookup_code = 'MAIL_STOP';
|| insert_lbs
||
|| DESCRIPTION
|| For the given string 'str_in', this procedure inserts a line break
|| character at every 'w' bytes. The line break character insertion
|| continues till the remaing string length becomes less than 'w'.
||
|| String 'str_out' contains the result of line insertion. The number of line
|| breaks inserted and the remaining string length are stored in 'lb_num'
|| and 'remaining', respectively.
||
|| ARGUMENTS
|| str_in : string to be processed
|| w : skip length for line break insertion
|| str_out(out) : line break inserted string
|| lb_num (out) : the number of line breaks inserted
|| remaining (out): remaining (byte) string length
||
=============================================================================*/
PROCEDURE insert_lbs( str_in in VARCHAR2, w in NUMBER,
str_out out NOCOPY VARCHAR2, lb_num out NOCOPY NUMBER,
remaining out NOCOPY NUMBER ) IS
len NUMBER(5); -- length of target string
str_inserted VARCHAR2(4000); -- line break inserted string
lb_num_inserted NUMBER(5); -- the number of line breaks inserted
str_inserted := '';
lb_num_inserted := 0;
(LB is not inserted when w = len) */
LOOP
-- bug2411369 : Changed to width_m
-- substrb_m( str_in, s, w, str, ww );
str_inserted := str_inserted || str || '
';
lb_num_inserted := lb_num_inserted + 1;
s := s + lengthb(str); -- update starting position
total_s := total_s + ww ; -- update total length
/* exit if there are no space to insert line breaks */
exit;
str_inserted := str_inserted || str; -- concatenate the rest of the string
str_out := str_inserted;
lb_num := lb_num_inserted;
END insert_lbs;
|| line break character codes and puncuation marks are inserted: the line
|| break character code is insertd such that the address string can fit
|| in the given address box size.
||
|| Defining the following 9 definitions for a desired address style should
|| enable the function to generate an address label formatted for the
|| desired address style:
||
|| 1) a list of address segments being displayed
|| - define in display order
||
|| 2) the number of address segments defined
||
|| 3) a list of line break address segment candidates
|| - define in preferable order
||
|| 4) the number of line break address segment candidates defined
||
|| 5) default punctuation mark for the address segments
||
|| 6) exceptional punctuations for the address segments
|| - the former segment of an address segment pair
|| - the latter segment of an address segment pair
|| - punctuation mark for an address segment pair
||
|| Note exceptional punctuation mark can only be applied
|| when both address segment pair are not NULL segments.
||
|| 7) the number of defined exceptional punctuations for the address
|| segments
||
|| 8) punctuation marks for the address sub-segments
|| - used to recognize punctuation mark in the address segment
|| so that the segment can be decomposed into sub-segments
||
|| 9) the number of defined punctuation marks for address sub-segments
||
||
|| These definitions are address style specific and are defined in
|| 'set_definitions' local procedure; the definitions for the default format
|| marks are inserted/attached in concatenated address segments and
|| then construct a formatted address string by inserting/attaching them
|| into/to the address segments. This is achieved by two steps:
|| the and the .
||
|| The line break character code can be categorized into the following
|| 5 types for its inserting place:
||
|| a) at the end of the line break address segments determind
|| in the
|| b) at the end of the extra line break address segments determind
|| in the
|| c) at the end of the line break address sub-segments determined
|| in the
|| d) inside the address segment whose length is longer than 'width':
|| determined in the
|| e) inside the address sub-segment whose length is longer than 'width':
|| determined in the
||
||
||
|| --------------------
|| 1) This step first tries to put the address segments in
|| 'width x height_min' area by breaking the line 'height_min-1' times:
|| The top 'height_min-1' line break address segments in the candidate
|| list are used for line breaking. However, a line break address
|| segment whose line is NULL is not used for line breaking.
||
|| 2) Each divided line is then examined if the length exceeds 'width'.
|| If line overflow is detected, the is
|| proceeded to sub-divide the overflowed line.
||
|| 3) When every line length becomes equal to or shorter than 'width',
|| then the total number of line breaks (all line break character code
|| types) is evaluated. If the number is equal to or less than
|| 'height_max-1', then proceed to a segment concatenation process
|| . If not, restart the by selecting the top
|| 'height_min-2' line break segments from the candidate list. (Note
|| that line break address segment whose line is NULL is not used
|| for line breaking.) This is continued till the can proceed to the process or the number of selected
|| candidate reaches 0. If the last trial with 0 line break segment
|| fails, then another concatination process is proceeded.
||
||
||
|| ------------------------
|| 1) This step first searchs for an alternative line break address
|| segment from the candidate list that has not yet been used as the
|| line break address segment or as the extra line break address
|| segment.
||
|| 2a) If the alternative segment is found, it is used as an extra line
|| break address segment and allocate the next segment to the extra
|| line break segment in a new line. If the allocated segment length
|| exceeds than 'width', then a) split the segment (if the segment is
|| not decomposable); b) arrange the location of the sub-segments
|| 1) insert line break character codes (type e) into the address
|| sub-segment
||
|| 2) attach a line break character code (type c) to the tail of the
|| address sub-segment and attach a punctuation mark for the address
|| sub-segments to the tail of non line break address sub-segments
||
|| 3) insert line break character codes (type d) into the address segment
||
|| 4) attach a line break character code (type a and b) to the tail of the
|| address segment and attach a punctuation mark for the address
|| segments to the tail of non line break address segments
||
||
||
|| ---
|| 1) concatenate all address segments while inserting punctuation mark
|| for the address segments in between the segments
||
|| 2) insert line break character code at every 'width'
||
|| 3) truncate the exceeded line beyond the address box size
||
||
||
|| where 'width', 'height_min', and 'height_max' are arguments to
|| the function as can be seen below.
||
||
||
|| ARGUMENTS
|| address_style : address format style
|| address1 : address line 1
|| address2 : address line 2
|| address3 : address line 3
|| address4 : address line 4
|| city : name of city
|| county : name of county
|| state : name of state
|| province : name of province
|| postal_code : postal code
|| territory_short_name : territory short name
|| country_code : country code (mandatory)
|| customer_name : customer name
|| bill_to_location : bill to location
|| first_name : contact first name
|| last_name : contact last name
|| mail_stop : mailing informatioin
|| default_country_code : default country code (mandatory)
|| default_country_desc : default territory short name (mandatory)
|| print_home_country_flag : flag to control printing of home county
|| (mandatory, 'Y': print, 'N': don't print)
|| width : address box width (mandatory,not 0)
|| height_min : address box desired height (mandatory,not 0)
|| height_max : address box maximum height (mandatory,not 0)
||
|| RETURN
|| formatted address string
||
|| NOTES
|| Except those denoted as mandatory, any parameter to the function can
|| have NULL value.
||
|| The function retrieves an address style value and a territory short name
|| value via country_code if NULL is passed to the function for these
|| parameters.
||
|| capable of handling multi-byte character as well as single-byte
|| characters
||
|| word wrap algorithm is incorporated in the Line Dividing Step
|| and the Line Sub Dividing Step
||
============================================================================*/
FUNCTION format_address_label( address_style IN VARCHAR2,
address1 IN VARCHAR2,
address2 IN VARCHAR2,
address3 IN VARCHAR2,
address4 IN VARCHAR2,
city IN VARCHAR2,
county IN VARCHAR2,
state IN VARCHAR2,
province IN VARCHAR2,
postal_code IN VARCHAR2,
territory_short_name IN VARCHAR2,
country_code IN VARCHAR2,
customer_name IN VARCHAR2,
bill_to_location IN VARCHAR2,
first_name IN VARCHAR2,
last_name IN VARCHAR2,
mail_stop IN VARCHAR2,
default_country_code IN VARCHAR2,
default_country_desc IN VARCHAR2,
print_home_country_flag IN VARCHAR2,
width IN NUMBER,
height_min IN NUMBER,
height_max IN NUMBER
)return VARCHAR2 IS
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Constant Variables
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
/* Address segment INDEX */
IDX_ADDRESS1 CONSTANT NUMBER := 1;
/* counter that holds # of line breaks inserted in the splitted address
segments */
lb_seg_splitted_num NUMBER(5);
/* counter that holds # of line breaks inserted in the splitted address
sub-segment */
lb_subseg_splitted_num NUMBER(5);
ELSE /* Default Address Style (DO NOT DELETE !) */
addr_seg(1) := IDX_FIRST_NAME;
|| by inserting line break characters at every box width
||
|| ARGUMENTS
|| p : display order of target segment
||
|| RETURNS
|| remaining line length (byte) after the segment split
||
=============================================================================*/
FUNCTION split_segment( p NUMBER ) return NUMBER IS
str_splitted VARCHAR2(300); -- splitted string
lb_num NUMBER(5); -- the number of line breaks inserted
/* insert line break character codes at every 'width' */
insert_lbs( source(addr_seg(p)), width, str_splitted, lb_num, r );
lb_seg_splitted_num := lb_seg_splitted_num + lb_num; -- update line break
|| by inserting line break characters at every box width
||
|| ARGUMENTS
|| p : display order of target segment
|| p_sub : order of target sub-segment
||
|| RETURNS
|| remaining line length (byte) after the sub-segment split
||
=============================================================================*/
FUNCTION split_subsegment( p NUMBER, p_sub NUMBER ) return NUMBER IS
str_splitted VARCHAR2(300); -- splitted string
lb_num NUMBER(5); -- the number of line breaks inserted
/* insert line break character codes at every 'width' */
insert_lbs(source_sub(p*SUBSEG_NUM+p_sub),width,str_splitted,lb_num,r);
lb_subseg_splitted_num := lb_subseg_splitted_num + lb_num; -- update line
the line, then the previoius segment is selected as an
extra line break address segment */
lb_seg_extra_num := lb_seg_extra_num + 1;
is selected as a line break address sub-segment */
lb_subseg_num(p) := lb_subseg_num(p) + 1;
|| 3. must be selected from the line break address segment candidate list
||
|| ARGUMENTS
|| a : display order of address segment where search ends
|| b-1 : display order of address segment where search starts
||
|| RETURNS
|| 0 :
|| if search fails (this includes the case when the line consists
|| of only one segment)
||
|| display order of address segment:
|| if search succeeds
||
||
=============================================================================*/
FUNCTION search_alt_lb_seg( a NUMBER, b NUMBER ) return NUMBER IS
not_used BOOLEAN := TRUE; -- flag that evaluates criteria 1
|| Selection of the extra line break segment is made via 'search_alt_lb_seg'
|| functioin. But if this search fails, then the previous segment to the
|| overflowed segment is chosed as an extra line break address segment
|| as long as the segment is not the first non-NULL segment in the line.
||
|| ARGUMENTS
|| a : display order of the first segment in the line
|| b : display order of the last segment in the line
||
=============================================================================*/
PROCEDURE arrange_segment( a NUMBER, b NUMBER ) IS
r NUMBER(5); -- remaining length in the line
p := alt_lb_seg_pos+1; -- update segment counter
should not be selected as an extra line break
address segment since the p th segment is the first
non-NULL segment in the line */
r := arrange_subsegment( p, p, width );
|| line break characters inserted one
|| 2) Add line break characters to the line break address sub-segment
|| 3) Substitute the longer address segment than the box width with the
|| line break characters inserted one
|| 4) Add line break characters to the line break address segments
||
|| RETURN
|| concatenated address string in which a number of punctuation marks and
|| line break character codes are inserted; places where line break character
|| code are inserted should be determined before this function.
||
=============================================================================*/
FUNCTION concatenate_segments return VARCHAR2 IS
lb_found BOOLEAN; -- flag to indicate the detection of line break
alt_source VARCHAR2(300); -- line break character inserted address
line break characters inserted one */
FOR i IN 1..source_sub_splitted_num LOOP
source_sub(source_sub_splitted.p(i)*SUBSEG_NUM +
source_sub_splitted.p_sub(i) ):=source_sub_splitted.val(i);
line break characters inserted one */
FOR i IN 1..source_splitted_num LOOP
source(addr_seg(source_splitted.p(i))) := source_splitted.val(i);
if not found, LB is not inserted */
nn_segment := search_nn_segment( previous_lb_pos+1, i );
|| 2) Insert a line break character into the concatenated string at every
|| 'width'
|| 3) Truncate the line break inserted string such that it can fit in the
|| address box region
||
|| RETURN
|| string after 1),2) and 3) are processed
||
=============================================================================*/
FUNCTION truncate_segments return VARCHAR2 IS
lb_num NUMBER(5); -- # of line breaks inserted
answer VARCHAR2(4000); -- concatenated and line break inserted address
/* insert a line break character into the concatenated string at every
'width' */
insert_lbs( concatenated, width, answer, lb_num, remaining );
/* truncate the line break character inserted string such that it can
fit in the address box */
IF lb_num > lb_seg_limit_num THEN
tmp := answer;
first_segment := last_segment + 1; -- update the first segment
select decode(value , 'UTF8' , 3 , 2 )
into multi_char_byte
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';