1 PACKAGE hz_format_pub AUTHID CURRENT_USER AS
2 /*$Header: ARHPFMTS.pls 120.13 2006/08/17 10:18:42 idali noship $ */
3 /*#
4 * This package contains the public APIs for Global Name and Address formatting. You can
5 * use this package to format name and address data regardless of where it is stored.
6 * @rep:scope public
7 * @rep:product HZ
8 * @rep:displayname Name and Address Formatting
9 * @rep:category BUSINESS_ENTITY HZ_ORGANIZATION
10 * @rep:category BUSINESS_ENTITY HZ_PERSON
11 * @rep:category BUSINESS_ENTITY HZ_ADDRESS
12 * @rep:lifecycle active
13 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
14 */
15
16 /******************** PUBLIC TYPE DECLARATIONS ****************************/
17
18 /*=========================================================================+
19 |
20 | TYPE DEFINITION: string_tbl_type
21 |
22 | DESCRIPTION
23 |
24 | A table or array of strings, that allow the formatting routines
25 | to return back multiple formatted lines.
26 |
27 +=========================================================================*/
28
29 TYPE string_tbl_type
30 IS TABLE OF VARCHAR2(240)
31 INDEX BY BINARY_INTEGER;
32
33 /*=========================================================================+
34 |
35 | TYPE DEFINITION: layout_rec_type
36 |
37 | DESCRIPTION
38 |
39 | An internal type definition that is used for a pl/sql representation
40 | of the columns in HZ_STYLE_FMT_LAYOUTS. Numerous procedures in
41 | this package need access to this table, and having it cached
42 | in PL/SQL offers opportunity for performance improvements.
43 |
44 | An additional field, attribute_value, is included. This field
45 | will contain the actual value of the variable identified by
46 | attribute_code, for the particular record being formatted.
47 |
48 +=========================================================================*/
49
50 TYPE layout_rec_type IS RECORD (
51 line_number hz_style_fmt_layouts_b.line_number%TYPE,
52 position hz_style_fmt_layouts_b.position%TYPE,
53 attribute_code hz_style_fmt_layouts_b.attribute_code%TYPE,
54 use_initial_flag hz_style_fmt_layouts_b.use_initial_flag%TYPE,
55 uppercase_flag hz_style_fmt_layouts_b.uppercase_flag%TYPE,
56 transform_function hz_style_fmt_layouts_b.transform_function%TYPE,
57 delimiter_before hz_style_fmt_layouts_b.delimiter_before%TYPE,
58 delimiter_after hz_style_fmt_layouts_b.delimiter_after%TYPE,
59 blank_lines_before hz_style_fmt_layouts_b.blank_lines_before%TYPE,
60 blank_lines_after hz_style_fmt_layouts_b.blank_lines_after%TYPE,
61 attribute_value VARCHAR2(240)
62 );
63
64 /*=========================================================================+
65 |
66 | TABLE DEFINITION: layout_tbl_type
67 |
68 | DESCRIPTION
69 |
70 | A table of 'layout_rec_type' records, to be able to create an
71 | internal pl/sql table of these data.
72 |
73 +=========================================================================*/
74
75 TYPE layout_tbl_type IS TABLE OF layout_rec_type
76 INDEX BY BINARY_INTEGER;
77
78 /*=========================================================================+
79 |
80 | TYPE DEFINITION: context_rec_type
81 |
82 | DESCRIPTION
83 |
84 | An internal type definition that is used to keep track of the
85 | "context" in which a formatting function was called in.
86 |
87 | It essentially contains the context related parameters passed from
88 | the caller (or the defaulted values).
89 |
90 | It reason for existance is so that PL/SQL functions can be created
91 | to (e.g. for variation selection conditions or attribute transformation
92 | functions) and allow them to have acess to the context of which the
93 | formatting API was invoked.
94 |
95 +=========================================================================*/
96
97 TYPE context_rec_type IS RECORD (
98 style_code hz_styles_b.style_code%TYPE,
99 style_format_code hz_style_formats_b.style_format_code%TYPE,
100 to_territory_code fnd_territories.territory_code%TYPE,
101 to_language_code fnd_languages.language_code%TYPE,
102 from_territory_code fnd_territories.territory_code%TYPE,
103 from_language_code fnd_languages.language_code%TYPE,
104 country_name_lang fnd_languages.language_code%TYPE
105 );
106
107 /********************* PUBLIC FORMATTING APIs *****************************/
108 /* */
109 /* For detailed information, please refer to the HLD */
110 /* */
111 /* GENERAL COMMENTS- */
112 /* */
113 /* 1. format_name and format_address procedures have two signatures */
114 /* each. The first requires an 'id' to be supplied (i.e. party_id */
115 /* or location_id) and the procedure will retrieve the data and */
116 /* format accordingly. The second set of signatures have parameters */
117 /* for the individual name and address components. This set can be */
118 /* called to format name and address data regardless of where it is */
119 /* stored. */
120 /* */
121 /* 2. Function definitions are also available which allow the */
122 /* formatting routines to be used directly in SELECT statements. */
123 /* */
124 /* STYLES- */
125 /* */
126 /* 1. Each entity might be able to be formatted in a number of styles */
127 /* providing sufficient formatting setup data has been defined. */
128 /* Typically, when calling these routines, you would identify the */
129 /* generic style (e.g. 'POSTAL_ADDR'), and the formatting routines */
130 /* will identify the specific format (e.g. 'POSTAL_ADDR_US') based */
131 /* on context information. */
132 /* */
133 /* 2. If you do not identify the style, the formatting routine will */
134 /* attempt to pick a default, but may not always be successful. */
135 /* */
136 /* 3. For flexibility, the formatting routines also allow you to */
137 /* optionally indicate the specific style format you wish to use */
138 /* in those cases where you want to override the defaulting */
139 /* behavior and don't want the formatting routines to pick one */
140 /* for you. */
141 /* */
142 /* CONTEXT- */
143 /* */
144 /* 1. The name and address formatting routines need to know the context */
148 /* 2. Name formatting requires a reference "locale" (language + */
145 /* in which they are being run, because that could affect how the */
146 /* information is formatted. */
147 /* */
149 /* territory) in order to pick an appropriate format. */
150 /* */
151 /* 3. There is additional context information that MAY affect how */
152 /* addresses are formatted. Context information for addresses are: */
153 /* */
154 /* (a) from country (i.e. where the sender is) */
155 /* (b) from language (i.e. where the sender is) */
156 /* (c) "to" country (i.e. the country of the address) */
157 /* (d) "to" language (i.e. the language used at the address) */
158 /* (e) language to display country name in (in some cases, this */
159 /* may be standardized (e.g. English) and may be different */
160 /* than the "from" language and the "to" language. */
161 /* */
162 /* 4. Wherever possible, the APIs will default this context information */
163 /* however, they are exposed as parameters so you can override. */
164 /* */
165 /* RETURN STATUS- */
166 /* */
167 /* 1. The public APIs return back an x_return_status parameter, which */
168 /* will contain one of the following values: */
169 /* FND_API.G_RET_STS_SUCCESS (success) */
170 /* FND_API.G_RET_STS_ERROR (error detected by the API) */
171 /* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error/exception) */
172 /* */
173 /**************************************************************************/
174
175
176 /*=========================================================================+
177 |
178 | PROCEDURE: format_address (signature #1)
179 |
180 | DESCRIPTION
181 |
182 | This procedure will format an address of a location that is
183 | stored in HZ_LOCATIONS.
184 |
185 |
186 | SCOPE: Public
187 |
188 | ARGUMENTS:
189 |
190 | (IN)
191 |
192 | p_location_id Location ID identifying the row in
193 | HZ_LOCATIONS for which you wish the
194 | address formatted.
195 | p_style_code The general style in which you wish the
196 | address formatted.
197 | p_style_format_code The specific format in which you wish the
198 | address formatted.
199 | p_line_break Character(s) to use to indicate a line
200 | break in x_formatted_address.
201 | p_space_replace Characters(s) to substitute for "blanks"
202 | p_to_language_code The language code of the destination.
203 | p_country_name_lang Language that the country name should be
204 | translated into.
205 | p_from_territory_code Territory code from where something is sent.
206 |
207 | (OUT)
208 |
209 | x_return_status Standard API return status.
210 | x_msg_count Number of messages on the message stack.
211 | x_formatted_address Single string formatted address (with line
212 | breaks inserted).
213 | x_formatted_lines_cnt Number of lines in the formatted address.
214 | x_formatted_address_tbl The formatted address returned as multiple
215 | strings, one for each line.
216 +=========================================================================*/
217
218 /*#
219 * Use this procedure to format the address of a location stored in the HZ_LOCATIONS table.
220 * @rep:scope public
221 * @rep:lifecycle active
222 * @rep:displayname Format Address (known location ID)
223 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
224 */
225 PROCEDURE format_address (
226 -- input parameters
227 p_location_id IN NUMBER,
228 p_style_code IN VARCHAR2 DEFAULT NULL,
229 p_style_format_code IN VARCHAR2 DEFAULT NULL,
230 p_line_break IN VARCHAR2 DEFAULT NULL,
231 p_space_replace IN VARCHAR2 DEFAULT NULL,
232 -- optional context parameters
233 p_to_language_code IN VARCHAR2 DEFAULT NULL,
234 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
235 p_from_territory_code IN VARCHAR2 DEFAULT NULL,
236 -- output parameters
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 x_formatted_address OUT NOCOPY VARCHAR2,
241 x_formatted_lines_cnt OUT NOCOPY NUMBER,
242 x_formatted_address_tbl OUT NOCOPY string_tbl_type
243 );
244
245 /*=========================================================================+
246 |
247 | PROCEDURE: format_address (signature #2)
248 |
249 | DESCRIPTION
250 |
251 | This procedure will format an address. Parameters are supplied for
252 | various address elements, therefore this procedure can be used to
253 | format an address from any data source.
254 |
255 |
256 | SCOPE: Public
257 |
258 | ARGUMENTS:
259 |
260 | (IN)
261 |
262 | p_style_code The general style in which you wish the
263 | address formatted.
267 | break in x_formatted_address.
264 | p_style_format_code The specific format in which you wish the
265 | address formatted.
266 | p_line_break Character(s) to use to indicate a line
268 | p_space_replace Characters(s) to substitute for "blanks"
269 | p_to_language_code The language code of the destination.
270 | p_country_name_lang Language that the country name should be
271 | translated into.
272 | p_from_territory_code Territory code from where something is sent.
273 |
274 | p_address_line_1 Line 1 of address
275 | p_address_line_2 Line 2 of address
279 | p_postal_code Postal Code or ZIP
276 | p_address_line_3 Line 3 of address
277 | p_address_line_4 Line 4 of address
278 | p_city City/Town
280 | p_state State Code
281 | p_province Province Code
282 | p_county County
283 | p_country Country (Territory Code)
284 | p_address_lines_phonetic Phonetic representation of address
285 |
286 | (OUT)
287 |
288 | x_return_status Standard API return status.
289 | x_msg_count Number of messages on the message stack.
290 | x_formatted_address Single string formatted address (with line
291 | breaks inserted).
292 | x_formatted_lines_cnt Number of lines in the formatted address.
293 | x_formatted_address_tbl The formatted address returned as multiple
294 | strings, one for each line.
295 +=========================================================================*/
296
297 /*#
298 * Use this procedure to format an address. Parameters are supplied for various
299 * address elements. Therefore, you can use this procedure to format an address from any
300 * data source.
301 * @rep:scope public
302 * @rep:lifecycle active
303 * @rep:displayname Format Address (unknown location ID)
304 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
305 */
306 PROCEDURE format_address (
307 -- input parameters
308 p_style_code IN VARCHAR2 DEFAULT NULL,
309 p_style_format_code IN VARCHAR2 DEFAULT NULL,
310 p_line_break IN VARCHAR2 DEFAULT NULL,
311 p_space_replace IN VARCHAR2 DEFAULT NULL,
312 -- optional context parameters
313 p_to_language_code IN VARCHAR2 DEFAULT NULL,
314 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
315 p_from_territory_code IN VARCHAR2 DEFAULT NULL,
316 -- address components
317 p_address_line_1 IN VARCHAR2 DEFAULT NULL,
318 p_address_line_2 IN VARCHAR2 DEFAULT NULL,
319 p_address_line_3 IN VARCHAR2 DEFAULT NULL,
320 p_address_line_4 IN VARCHAR2 DEFAULT NULL,
321 p_city IN VARCHAR2 DEFAULT NULL,
322 p_postal_code IN VARCHAR2 DEFAULT NULL,
323 p_state IN VARCHAR2 DEFAULT NULL,
324 p_province IN VARCHAR2 DEFAULT NULL,
325 p_county IN VARCHAR2 DEFAULT NULL,
326 p_country IN VARCHAR2 DEFAULT NULL,
327 p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL,
328 -- output parameters
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 x_formatted_address OUT NOCOPY VARCHAR2,
333 x_formatted_lines_cnt OUT NOCOPY NUMBER,
334 x_formatted_address_tbl OUT NOCOPY string_tbl_type
335 );
336
337 /*=========================================================================+
338 |
339 | PROCEDURE: format_eloc_address
340 |
341 | DESCRIPTION
342 |
343 | This procedure will format an address for elocation program.
344 | Parameters are supplied for various address elements, therefore
345 | this procedure can be used to format an address from any data source.
346 |
347 |
348 | SCOPE: Public
349 |
350 | ARGUMENTS:
351 |
352 | (IN)
353 |
354 | p_style_code The general style in which you wish the
355 | address formatted.
356 | p_style_format_code The specific format in which you wish the
357 | address formatted.
358 | p_line_break Character(s) to use to indicate a line
359 | break in x_formatted_address.
360 | p_space_replace Characters(s) to substitute for "blanks"
361 | p_to_language_code The language code of the destination.
362 | p_country_name_lang Language that the country name should be
363 | translated into.
364 | p_from_territory_code Territory code from where something is sent.
365 |
366 | p_address_line_1 Line 1 of address
367 | p_address_line_2 Line 2 of address
368 | p_address_line_3 Line 3 of address
369 | p_address_line_4 Line 4 of address
370 | p_city City/Town
371 | p_postal_code Postal Code or ZIP
372 | p_state State Code
373 | p_province Province Code
374 | p_county County
375 | p_country Country (Territory Code)
379 |
376 | p_address_lines_phonetic Phonetic representation of address
377 |
378 | (OUT)
380 | x_return_status Standard API return status.
381 | x_msg_count Number of messages on the message stack.
382 | x_formatted_address Single string formatted address (with line
383 | breaks inserted).
384 | x_formatted_lines_cnt Number of lines in the formatted address.
385 | x_formatted_address_tbl The formatted address returned as multiple
386 | strings, one for each line.
387 +=========================================================================*/
388
389 PROCEDURE format_eloc_address (
390 p_style_code IN VARCHAR2 DEFAULT NULL,
391 p_style_format_code IN VARCHAR2 DEFAULT NULL,
392 p_line_break IN VARCHAR2 DEFAULT NULL,
393 p_space_replace IN VARCHAR2 DEFAULT NULL,
394 p_to_language_code IN VARCHAR2 DEFAULT NULL,
395 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
396 p_from_territory_code IN VARCHAR2 DEFAULT NULL,
397 p_address_line_1 IN VARCHAR2 DEFAULT NULL,
398 p_address_line_2 IN VARCHAR2 DEFAULT NULL,
399 p_address_line_3 IN VARCHAR2 DEFAULT NULL,
400 p_address_line_4 IN VARCHAR2 DEFAULT NULL,
401 p_city IN VARCHAR2 DEFAULT NULL,
402 p_postal_code IN VARCHAR2 DEFAULT NULL,
403 p_state IN VARCHAR2 DEFAULT NULL,
404 p_province IN VARCHAR2 DEFAULT NULL,
405 p_county IN VARCHAR2 DEFAULT NULL,
406 p_country IN VARCHAR2 DEFAULT NULL,
407 p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 x_formatted_address OUT NOCOPY VARCHAR2,
412 x_formatted_lines_cnt OUT NOCOPY NUMBER,
413 x_formatted_address_tbl OUT NOCOPY string_tbl_type
414 );
415
416 /*=========================================================================+
417 |
418 | PROCEDURE: format_address_layout (signature #1)
419 |
420 | DESCRIPTION
421 |
422 | This procedure will format an address layout of a location that is
423 | stored in HZ_LOCATIONS.
424 |
425 |
426 | SCOPE: Public
427 |
428 | ARGUMENTS:
429 |
430 | (IN)
431 |
432 | p_location_id Location ID identifying the row in
433 | HZ_LOCATIONS for which you wish the
434 | address formatted.
435 | p_style_code The general style in which you wish the
436 | address formatted.
437 | p_style_format_code The specific format in which you wish the
441 | p_space_replace Characters(s) to substitute for "blanks"
438 | address formatted.
439 | p_line_break Character(s) to use to indicate a line
440 | break in x_formatted_address.
442 | p_to_language_code The language code of the destination.
443 | p_country_name_lang Language that the country name should be
444 | translated into.
445 | p_from_territory_code Territory code from where something is sent.
446 |
447 | (OUT)
448 |
449 | x_return_status Standard API return status.
450 | x_msg_count Number of messages on the message stack.
451 | x_layout_tbl_cnt Number of lines in the formatted address layout.
452 | x_layout_tbl The formatted address layout returned as multiple
453 | strings, one for each line.
454 +=========================================================================*/
455
456 PROCEDURE format_address_layout (
457 -- input parameters
458 p_location_id IN NUMBER,
459 p_style_code IN VARCHAR2 DEFAULT NULL,
460 p_style_format_code IN VARCHAR2 DEFAULT NULL,
461 p_line_break IN VARCHAR2 DEFAULT NULL,
462 p_space_replace IN VARCHAR2 DEFAULT NULL,
463 -- optional context parameters
464 p_to_language_code IN VARCHAR2 DEFAULT NULL,
465 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
469 x_msg_count OUT NOCOPY NUMBER,
466 p_from_territory_code IN VARCHAR2 DEFAULT NULL,
467 -- output parameters
468 x_return_status OUT NOCOPY VARCHAR2,
470 x_msg_data OUT NOCOPY VARCHAR2,
471 x_layout_tbl_cnt OUT NOCOPY NUMBER,
472 x_layout_tbl OUT NOCOPY layout_tbl_type
473 );
474
475 /*=========================================================================+
476 |
477 | PROCEDURE: format_address_layout (signature #2)
478 |
479 | DESCRIPTION
480 |
481 | This procedure will format an address layout. Parameters are supplied for
482 | various address elements, therefore this procedure can be used to
483 | format an address layout from any data source.
484 |
485 |
486 | SCOPE: Public
487 |
488 | ARGUMENTS:
489 |
490 | (IN)
491 |
492 | p_style_code The general style in which you wish the
493 | address formatted.
494 | p_style_format_code The specific format in which you wish the
495 | address formatted.
496 | p_line_break Character(s) to use to indicate a line
497 | break in x_formatted_address.
498 | p_space_replace Characters(s) to substitute for "blanks"
499 | p_to_language_code The language code of the destination.
500 | p_country_name_lang Language that the country name should be
501 | translated into.
502 | p_from_territory_code Territory code from where something is sent.
503 |
504 | p_address_line_1 Line 1 of address
505 | p_address_line_2 Line 2 of address
506 | p_address_line_3 Line 3 of address
507 | p_address_line_4 Line 4 of address
508 | p_city City/Town
509 | p_postal_code Postal Code or ZIP
510 | p_state State Code
511 | p_province Province Code
512 | p_county County
513 | p_country Country (Territory Code)
514 | p_address_lines_phonetic Phonetic representation of address
515 |
516 | (OUT)
517 |
518 | x_return_status Standard API return status.
519 | x_msg_count Number of messages on the message stack.
520 | x_layout_tbl_cnt Number of lines in the formatted address layout.
521 | x_layout_tbl The formatted address layout returned as multiple
522 | strings, one for each line.
523 +=========================================================================*/
524
525 PROCEDURE format_address_layout (
526 -- input parameters
527 p_style_code IN VARCHAR2 DEFAULT NULL,
528 p_style_format_code IN VARCHAR2 DEFAULT NULL,
529 p_line_break IN VARCHAR2 DEFAULT NULL,
530 p_space_replace IN VARCHAR2 DEFAULT NULL,
531 -- optional context parameters
532 p_to_language_code IN VARCHAR2 DEFAULT NULL,
533 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
534 p_from_territory_code IN VARCHAR2 DEFAULT NULL,
535 -- address components
536 p_address_line_1 IN VARCHAR2 DEFAULT NULL,
537 p_address_line_2 IN VARCHAR2 DEFAULT NULL,
538 p_address_line_3 IN VARCHAR2 DEFAULT NULL,
539 p_address_line_4 IN VARCHAR2 DEFAULT NULL,
540 p_city IN VARCHAR2 DEFAULT NULL,
541 p_postal_code IN VARCHAR2 DEFAULT NULL,
542 p_state IN VARCHAR2 DEFAULT NULL,
543 p_province IN VARCHAR2 DEFAULT NULL,
544 p_county IN VARCHAR2 DEFAULT NULL,
545 p_country IN VARCHAR2 DEFAULT NULL,
546 p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL,
547 -- output parameters
548 x_return_status OUT NOCOPY VARCHAR2,
549 x_msg_count OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2,
551 x_layout_tbl_cnt OUT NOCOPY NUMBER,
552 x_layout_tbl OUT NOCOPY layout_tbl_type
553 );
554
555
556 /*=========================================================================+
557 |
558 | PROCEDURE: format_name (signature #1)
559 |
560 | DESCRIPTION
561 |
562 | This procedure will format a name of a person party that is
563 | stored in HZ_PARTIES.
564 |
565 |
566 | SCOPE: Public
567 |
568 | ARGUMENTS:
569 |
570 | (IN)
571 |
572 | p_party_id Party ID identifying the row in
573 | HZ_PARTIES for which you wish the
574 | name formatted.
575 | p_style_code The general style in which you wish the
576 | address formatted.
577 | p_style_format_code The specific format in which you wish the
578 | address formatted.
579 | p_line_break Character(s) to use to indicate a line
580 | break in x_formatted_address.
581 | p_space_replace Characters(s) to substitute for "blanks"
582 | p_ref_language_code The "reference" language (context).
583 | p_ref_territory_code The "refernece" territory (context).
584 |
585 | (OUT)
586 |
587 | x_return_status Standard API return status.
588 | x_msg_count Number of messages on the message stack.
589 | x_formatted_name Single string formatted name (with line
590 | breaks inserted).
591 | x_formatted_lines_cnt Number of lines in the formatted name.
592 | x_formatted_name_tbl The formatted name returned as multiple
593 | strings, one for each line.
594 +=========================================================================*/
595
596 /*#
597 * Use the Name Formatting procedure to format the name of a person using a specific style
598 * format. To use this procedure you must use the party_id of the name that you are
602 * @rep:displayname Format Name (known party ID)
599 * formatting. The procedure queries for the party and formats the name.
600 * @rep:scope public
601 * @rep:lifecycle active
603 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
604 */
605 PROCEDURE format_name (
606 -- input parameters
607 p_party_id IN NUMBER,
608 p_style_code IN VARCHAR2 DEFAULT NULL,
609 p_style_format_code IN VARCHAR2 DEFAULT NULL,
610 p_line_break IN VARCHAR2 DEFAULT NULL,
611 p_space_replace IN VARCHAR2 DEFAULT NULL,
612 -- optional context parameters
613 p_ref_language_code IN VARCHAR2 DEFAULT NULL,
614 p_ref_territory_code IN VARCHAR2 DEFAULT NULL,
615 -- output parameters
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_msg_count OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2,
619 x_formatted_name OUT NOCOPY VARCHAR2,
620 x_formatted_lines_cnt OUT NOCOPY NUMBER,
621 x_formatted_name_tbl OUT NOCOPY string_tbl_type
622 );
623
624 /*=========================================================================+
625 |
626 | PROCEDURE: format_name (signature #2)
627 |
628 | DESCRIPTION
629 |
630 | This procedure will format a person name. Parameters are supplied for
631 | various name elements, therefore this procedure can be used to
632 | format a person name from any data source.
633 |
634 | SCOPE: Public
635 |
636 | ARGUMENTS:
637 |
638 | (IN)
639 |
640 | p_style_code The general style in which you wish the
641 | address formatted.
642 | p_style_format_code The specific format in which you wish the
643 | address formatted.
644 | p_line_break Character(s) to use to indicate a line
645 | break in x_formatted_address.
646 | p_space_replace Characters(s) to substitute for "blanks"
647 | p_ref_language_code The "reference" language (context).
648 | p_ref_territory_code The "refernece" territory (context).
649 |
650 | p_person_title Title of the person.
651 | p_person_first_name First name of the person.
652 | p_person_middle_name Middle name of the person.
653 | p_person_title Title of the person.
654 | p_person_first_name First name of the person.
655 | p_person_middle_name Middle name of the person.
656 | p_person_last_name Last name of the person.
657 | p_person_name_suffix Suffix of the person name.
658 | p_person_known_as "Known as" or a.k.a. or alias of the person.
659 | p_first_name_phonetic Phonetic representation of first name.
660 | p_middle_name_phonetic Phonetic representation of middle name.
661 | p_last_name_phonetic Phonetic representation of last name.
662 |
663 | (OUT)
664 |
665 | x_return_status Standard API return status.
666 | x_msg_count Number of messages on the message stack.
667 | x_formatted_name Single string formatted name (with line
668 | breaks inserted).
669 | x_formatted_lines_cnt Number of lines in the formatted name.
670 | x_formatted_name_tbl The formatted name returned as multiple
671 | strings, one for each line.
672 +=========================================================================*/
673
674 /*#
675 * Use the Name Formatting procedure to format the name of a person using a particular
676 * style format. Use this procedure if you do not know the party_id of the name that you
677 * are formatting. This procedure accepts the individual components of a person's name as
678 * input.
679 * @rep:scope public
680 * @rep:lifecycle active
681 * @rep:displayname Format Name (unknown party ID)
682 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
683 */
684 PROCEDURE format_name (
685 -- input parameters
686 p_style_code IN VARCHAR2 DEFAULT NULL,
687 p_style_format_code IN VARCHAR2 DEFAULT NULL,
688 p_line_break IN VARCHAR2 DEFAULT NULL,
689 p_space_replace IN VARCHAR2 DEFAULT NULL,
690 -- optional context parameters
691 p_ref_language_code IN VARCHAR2 DEFAULT NULL,
692 p_ref_territory_code IN VARCHAR2 DEFAULT NULL,
693 -- person name components
694 p_person_title IN VARCHAR2 DEFAULT NULL,
695 p_person_first_name IN VARCHAR2 DEFAULT NULL,
696 p_person_middle_name IN VARCHAR2 DEFAULT NULL,
697 p_person_last_name IN VARCHAR2 DEFAULT NULL,
698 p_person_name_suffix IN VARCHAR2 DEFAULT NULL,
699 p_person_known_as IN VARCHAR2 DEFAULT NULL,
700 p_first_name_phonetic IN VARCHAR2 DEFAULT NULL,
701 p_middle_name_phonetic IN VARCHAR2 DEFAULT NULL,
702 p_last_name_phonetic IN VARCHAR2 DEFAULT NULL,
703 -- output parameters
704 x_return_status OUT NOCOPY VARCHAR2,
705 x_msg_count OUT NOCOPY NUMBER,
706 x_msg_data OUT NOCOPY VARCHAR2,
707 x_formatted_name OUT NOCOPY VARCHAR2,
708 x_formatted_lines_cnt OUT NOCOPY NUMBER,
709 x_formatted_name_tbl OUT NOCOPY string_tbl_type
710 );
711
712 /*=========================================================================+
713 |
714 | PROCEDURE: format_data
715 |
716 | DESCRIPTION
717 |
718 | A generic API that will format entities other than names and addresses
722 | SCOPE: Public
719 | providing that the appropriate formatting metadata has been set up.
720 |
721 |
723 |
724 | ARGUMENTS:
725 |
726 | (IN)
727 |
728 | p_object_code Object code (e.g. table name or view name)
729 | for which you want the data formatted.
730 | p_object_key Primary key of the object for which you
731 | wish the data formatted.
732 | p_style_code The general style in which you wish the
733 | address formatted.
734 | p_style_format_code The specific format in which you wish the
735 | address formatted.
736 | p_line_break Character(s) to use to indicate a line
737 | break in x_formatted_address.
738 | p_space_replace Characters(s) to substitute for "blanks"
739 | p_ref_language_code The "reference" language (context).
740 | p_ref_territory_code The "refernece" territory (context).
741 |
742 | (OUT)
743 |
744 | x_return_status Standard API return status.
745 | x_msg_count Number of messages on the message stack.
746 | x_formatted_data Single string formatted data (with line
747 | breaks inserted).
748 | x_formatted_lines_cnt Number of lines in the formatted data.
749 | x_formatted_data_tbl The formatted data returned as multiple
750 | strings, one for each line.
751 +=========================================================================*/
752
753 /*#
757 * @rep:scope public
754 * Use the solutions provided for name and address formatting to format any type of
755 * information from any data source. This generic formatting routine provides a method that
756 * you can use to set up the Style Metadata for the data that you are formatting.
758 * @rep:lifecycle active
759 * @rep:displayname Format General Data
760 * @rep:doccd 120hztig.pdf Name and Address Formatting APIs, Oracle Trading Community Architecture Technical Implementation Guide
761 */
762 PROCEDURE format_data (
763 -- input parameters
764 p_object_code IN VARCHAR2,
765 p_object_key_1 IN VARCHAR2,
766 p_object_key_2 IN VARCHAR2 DEFAULT NULL,
767 p_object_key_3 IN VARCHAR2 DEFAULT NULL,
768 p_object_key_4 IN VARCHAR2 DEFAULT NULL,
769 p_style_code IN VARCHAR2 DEFAULT NULL,
770 p_style_format_code IN VARCHAR2 DEFAULT NULL,
771 p_line_break IN VARCHAR2 DEFAULT NULL,
772 p_space_replace IN VARCHAR2 DEFAULT NULL,
773 -- optional context parameters
774 p_ref_language_code IN VARCHAR2 DEFAULT NULL,
775 p_ref_territory_code IN VARCHAR2 DEFAULT NULL,
776 -- output parameters
777 x_return_status OUT NOCOPY VARCHAR2,
778 x_msg_count OUT NOCOPY NUMBER,
779 x_msg_data OUT NOCOPY VARCHAR2,
780 x_formatted_data OUT NOCOPY VARCHAR2,
781 x_formatted_lines_cnt OUT NOCOPY NUMBER,
782 x_formatted_data_tbl OUT NOCOPY string_tbl_type
783 );
784
785 /********************** PUBLIC FUNCTION APIs ******************************/
786
787
788 /*=========================================================================+
789 |
790 | FUNCTION: format_address
791 |
792 | DESCRIPTION
793 |
794 | A function version of the format_address procedure that can be
795 | used in a SQL statement. Returns back the address formatted into a
796 | single line, with line breaks inserted.
797 |
798 | SCOPE: Public
799 |
800 | ARGUMENTS:
801 |
802 +=========================================================================*/
803
804 FUNCTION format_address(
805 p_location_id IN NUMBER,
806 p_style_code IN VARCHAR2 DEFAULT NULL,
807 p_style_format_code IN VARCHAR2 DEFAULT NULL,
808 p_line_break IN VARCHAR2 DEFAULT '/',
809 p_space_replace IN VARCHAR2 DEFAULT NULL,
810 p_to_language_code IN VARCHAR2 DEFAULT NULL,
814
811 p_country_name_lang IN VARCHAR2 DEFAULT NULL,
812 p_from_territory_code IN VARCHAR2 DEFAULT NULL
813 ) RETURN VARCHAR2;
815
816 /*=========================================================================+
817 |
818 | FUNCTION: format_address_lov
819 |
820 | DESCRIPTION
821 |
822 | A function version of the format_address procedure that can be
823 | is meant to be used for Party LOVs. This version accepts the
824 | individual address components and is useful since these data are
825 | denormalized onto HZ_PARTIES.
826 |
827 | SCOPE: For TCA internal use only - signature can change
828 | without warning.
829 |
830 | ARGUMENTS:
831 |
832 +=========================================================================*/
833
834 FUNCTION format_address_lov(
835 p_address_line_1 IN VARCHAR2 DEFAULT NULL,
836 p_address_line_2 IN VARCHAR2 DEFAULT NULL,
837 p_address_line_3 IN VARCHAR2 DEFAULT NULL,
838 p_address_line_4 IN VARCHAR2 DEFAULT NULL,
839 p_city IN VARCHAR2 DEFAULT NULL,
840 p_postal_code IN VARCHAR2 DEFAULT NULL,
841 p_state IN VARCHAR2 DEFAULT NULL,
842 p_province IN VARCHAR2 DEFAULT NULL,
843 p_county IN VARCHAR2 DEFAULT NULL,
844 p_country IN VARCHAR2 DEFAULT NULL,
845 p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL
846 ) RETURN VARCHAR2;
847
848
849 /*=========================================================================+
850 |
851 | FUNCTION: format_name
852 |
853 | DESCRIPTION
854 |
855 | A function version of the format_name procedure that can be
856 | used in a SQL statement. Returns back the name formatted into a
857 | single line, with line breaks inserted.
858 |
859 | SCOPE: Public
860 |
861 | ARGUMENTS:
862 |
863 +=========================================================================*/
864
865
866 FUNCTION format_name (
867 p_party_id IN NUMBER,
868 p_style_code IN VARCHAR2 DEFAULT NULL,
869 p_style_format_code IN VARCHAR2 DEFAULT NULL,
870 p_line_break IN VARCHAR2 DEFAULT '/',
871 p_space_replace IN VARCHAR2 DEFAULT NULL,
872 p_ref_language_code IN VARCHAR2 DEFAULT NULL,
873 p_ref_territory_code IN VARCHAR2 DEFAULT NULL
874 ) RETURN VARCHAR2;
875
876 /*********************** PUBLIC UTILITY APIs ******************************/
877
878 /*=========================================================================+
879 |
880 | PROCEDURE: get_context
881 |
882 | DESCRIPTION
883 |
884 | This procedure is invoked to obtain "context" information under
885 | which a format_name, format_address, or format_data public API
886 | was invoked.
887 |
888 | It is available so that product teams or customer can
889 | develop their own attribute transformation or variation selection
890 | functions, and have access to the context information under which
891 | a format_xxxx procedure was invoked within.
892 |
893 | !!! IMPORTANT !!!
894 |
895 | This procedure is only meant to be called by those functions
896 | which are dynamically invoked by the formatting routines.
897 |
898 | Specificially, this means the following:
899 |
900 | 1. Any functions defined for the "selection condition" of
901 | a Style Format Layout Variation.
902 |
903 | 2. Any functions defined as "transformation functions" for
904 | attributes defined in the Style Format Layouts.
905 |
906 | This context information should not be invoked by any other code
907 | because the state of the context information is not guaranteed
908 | outside the invocation of a format_name, format_address, or
909 | format_data routine.
910 |
911 | SCOPE: Public (limited)
912 |
913 +=========================================================================*/
914
915 PROCEDURE get_context (
916 x_context OUT NOCOPY context_rec_type
917 );
918
919 /*=========================================================================+
920 |
921 | PROCEDURE: get_style_format
922 |
923 | DESCRIPTION
924 |
925 | Gets the appropriate localized Style Format Code for a given Style,
929 | depending on Territory and Location ("locales"). The locales for
926 | based on Territory or Language, or a combination of both.
927 |
928 | Styles (HZ_STYLES) can have multiple Style Formats (HZ_STYLE_FORMATS)
930 | which a given Style Format is applicable is stored in table
931 | HZ_STYLE_FMT_LOCALES.
932 |
933 | The following sequence occurs to find the matching Style Format:
934 |
935 | 1. Check for a match on BOTH Territory and Language Code
936 | 2. If not found (or n/a) check for a match on Territory.
937 | 3. If not found (or n/a) check for a match on Language.
938 | 4. If not found then retrieve the DEFAULT Style Format
939 | for the Style.
940 |
941 | For example, if Style Code 'POSTAL_ADDR' is passed, as well as
942 | Territory of 'FR' (France), then this procedure should find the
943 | matching Style Format of 'POSTAL_ADDR_N_EUR' (The Style Format
944 | Northern Europe).
945 |
946 | SCOPE: Public
947 |
948 | ARGUMENTS: (IN)
949 | p_style_code Style for which to find
950 | the localized Style Format.
951 | p_territory_code Territory for which you wish
952 | to determine Style Format.
953 | p_language_code Language for which you wish
954 | to determine Style Format.
955 |
956 | (OUT)
957 | x_return_status API Standard Return Status
958 | x_msg_count API Standard Message Count
959 | x_msg_data API Standard Message Data
960 | x_style_format_code Style Format Code that was found.
961 |
962 +=========================================================================*/
963
964 PROCEDURE get_style_format (
965 p_style_code IN VARCHAR2,
966 p_territory_code IN fnd_territories.territory_code%TYPE DEFAULT NULL,
967 p_language_code IN fnd_languages.language_code%TYPE DEFAULT NULL,
968 x_return_status OUT NOCOPY VARCHAR2,
969 x_msg_count OUT NOCOPY VARCHAR2,
970 x_msg_data OUT NOCOPY VARCHAR2,
971 x_style_format_code OUT NOCOPY VARCHAR2
972 );
973
974 /***************** PUBLIC (SEEDED) CALLOUT FUNCTIONS **********************/
975 /* */
976 /* The following functions are used by the seeded "selection condition" */
977 /* definitions (for selecting a layout variation) and by the attribute */
978 /* transformation functions. They are available for use in custom */
979 /* layouts. */
980 /* */
981 /**************************************************************************/
982
983 /*=========================================================================+
984 |
985 | FUNCTION: use_neu_country_code
986 |
987 | DESCRIPTION
988 |
989 | This function determines if the "from country" (context information)
990 | as well as the "to country" both use the Northern European
991 | addressing format. This determines which of the Northern European
992 | layout variations to use.
993 |
994 | SCOPE: Public (limited)
995 | Intended to be used as a function for the "selection condition"
996 | of a layout variation.
997 |
998 | ARGUMENTS: (IN)
999 | p_territory_code Territory Code of the address ("to").
1000 +=========================================================================*/
1001
1002 FUNCTION use_neu_country_format (
1003 p_territory_code IN VARCHAR2
1004 ) RETURN VARCHAR2; -- boolean 'Y' or 'N'
1005
1006 /*=========================================================================+
1007 |
1008 | FUNCTION: get_neu_country_code
1009 |
1010 | DESCRIPTION
1011 |
1012 | This function will translate a Territory Code for Northern Europe
1013 | into the code that is used to preceed the postal code in a
1014 | formatted address. The code often differs from the ISO code.
1015 |
1016 | For example, France (FR) uses an "F" in front of the postal code
1017 | when using the Northern European address format.
1018 | Belgium (BE) uses "B", Germany (DE) uses "D", etc.
1019 |
1020 | SCOPE: Public (limited)
1021 | Intended to be used as a "transformation function" of an
1022 | attribute.
1023 |
1024 | ARGUMENTS: (IN)
1025 | p_territory_code Territory Code of the address ("to").
1026 +=========================================================================*/
1027
1028 FUNCTION get_neu_country_code (
1029 p_territory_code IN VARCHAR2
1030 ) RETURN VARCHAR2;
1031
1032 /*=========================================================================+
1033 |
1034 | FUNCTION: get_tl_territory_name
1035 |
1036 | DESCRIPTION
1037 |
1038 | This function will translate a Territory Code (e.g. 'MX') into its
1039 | name (e.g. 'Mexico').
1040 |
1044 | a) The Territory Name is retrieved in the language identified
1041 | The behavior of this function depends on the context in which
1042 | it was invoked.
1043 |
1045 | by context attribute "country_name_lang".
1046 |
1047 | b) If the Territory Code is the same as the context attribute
1048 | "from_territory_code", then NULL will be returned.
1049 | This controls the suppression of the country name if
1050 | the "from" and "to" countries are the same.
1051 |
1052 |
1053 | SCOPE: Public (limited)
1054 | Intended to be used as a "transformation function" of an
1055 | attribute.
1056 |
1057 | ARGUMENTS: (IN)
1058 | p_territory_code Territory Code of the address ("to").
1059 +=========================================================================*/
1060
1061 FUNCTION get_tl_territory_name (
1062 p_territory_code IN VARCHAR2
1063 ) RETURN VARCHAR2;
1064
1065
1066 PROCEDURE get_default_ref_territory (
1067 x_ref_territory_code OUT NOCOPY fnd_territories.territory_code%TYPE
1068 );
1069
1070 END hz_format_pub;