[Home] [Help]
PACKAGE BODY: APPS.HZ_FORMAT_PUB
Source
4 /*=========================================================================+
1 PACKAGE BODY hz_format_pub AS
2 /*$Header: ARHPFMTB.pls 120.24.12010000.2 2008/09/16 11:12:02 rgokavar ship $ */
3
5 |
6 | TYPE DEFINITION: layout_rec_type
7 |
8 | DESCRIPTION
9 |
10 | An internal type definition that is used for a pl/sql representation
11 | of the columns in HZ_STYLE_FMT_LAYOUTS. Numerous procedures in
12 | this package need access to this table, and having it cached
13 | in PL/SQL offers opportunity for performance improvements.
14 |
15 | An additional field, attribute_value, is included. This field
16 | will contain the actual value of the variable identified by
17 | attribute_code, for the particular record being formatted.
18 |
19 +=========================================================================*/
20 /*
21 TYPE layout_rec_type IS RECORD (
22 line_number hz_style_fmt_layouts_b.line_number%TYPE,
23 position hz_style_fmt_layouts_b.position%TYPE,
24 attribute_code hz_style_fmt_layouts_b.attribute_code%TYPE,
25 use_initial_flag hz_style_fmt_layouts_b.use_initial_flag%TYPE,
26 uppercase_flag hz_style_fmt_layouts_b.uppercase_flag%TYPE,
27 transform_function hz_style_fmt_layouts_b.transform_function%TYPE,
28 delimiter_before hz_style_fmt_layouts_b.delimiter_before%TYPE,
29 delimiter_after hz_style_fmt_layouts_b.delimiter_after%TYPE,
30 blank_lines_before hz_style_fmt_layouts_b.blank_lines_before%TYPE,
31 blank_lines_after hz_style_fmt_layouts_b.blank_lines_after%TYPE,
32 attribute_value VARCHAR2(240)
33 );
34 */
35 /*=========================================================================+
36 |
37 | TYPE DEFINITION: name_value_rec_type
38 |
39 | DESCRIPTION
40 |
41 | An internal type definition that is for name/value pairs.
42 | This is used in the formatting signatures that have the name and
43 | address elements as individual parameters. The parameter names and
44 | values are loaded into a PL/SQL table to be able to support dynamic
45 | formatting.
46 |
47 +=========================================================================*/
48
49 TYPE name_value_rec_type IS RECORD (
50 parm_name VARCHAR2(30),
51 parm_value VARCHAR2(240),
52 parm_type VARCHAR2(1) -- V=Varchar, N=Numeric, D=Date
53 );
54
55 /*=========================================================================+
56 |
57 | TABLE DEFINITION: layout_tbl_type
58 |
59 | DESCRIPTION
60 |
61 | A table of 'layout_rec_type' records, to be able to create an
62 | internal pl/sql table of these data.
63 |
64 +=========================================================================*/
65
66 -- TYPE layout_tbl_type IS TABLE OF layout_rec_type
67 -- INDEX BY BINARY_INTEGER;
68
69 /*=========================================================================+
70 |
71 | TABLE DEFINITION: name_value_tbl_type
72 |
73 | DESCRIPTION
74 |
75 | A table of 'name_value_rec_type' records, to be able to create
76 | an internal pl/sql table of these data.
77 |
78 +=========================================================================*/
79
80 TYPE name_value_tbl_type IS TABLE OF name_value_rec_type
81 INDEX BY BINARY_INTEGER;
82
83 -- remove 9i dependency
84 -- performance bug 4079490
85 --TYPE var_indexed_table IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(400);
86 --terr_short_name_tab var_indexed_table;
87
88 /******************** PACKAGE GLOBAL VARIABLES ****************************/
89
90 g_pkg_name VARCHAR2(30) := 'hz_format_pub';
91
92 g_caching BOOLEAN := TRUE;
93 g_cache_style_format_code hz_style_formats_b.style_format_code%TYPE;
94 g_cache_variation_number NUMBER;
95
96 g_layout_tbl layout_tbl_type;
97 g_layout_tbl_cnt NUMBER;
98
99 g_parm_tbl name_value_tbl_type;
100 g_parm_tbl_cnt NUMBER;
101
102 g_pk_tbl name_value_tbl_type;
103 g_pk_tbl_cnt NUMBER;
104
105 g_context context_rec_type;
106
107 -- performance bug 4079490
108 g_terr_code_exist NUMBER := 0;
109 g_territory_code fnd_territories.territory_code%TYPE;
110
111 --
112 -- Constants
113 --
114
115 k_profile_ref_lang CONSTANT VARCHAR2(30) := 'HZ_REF_LANG';
116 k_profile_ref_territory CONSTANT VARCHAR2(30) := 'HZ_REF_TERRITORY';
117 k_profile_country_lang CONSTANT VARCHAR2(30) := 'HZ_LANG_FOR_COUNTRY_DISPLAY';
118 k_profile_def_addr_style CONSTANT VARCHAR2(30) := 'HZ_DEFAULT_ADDR_STYLE';
119 k_profile_def_name_style CONSTANT VARCHAR2(30) := 'HZ_DEFAULT_NAME_STYLE';
120
121 g_icx_territory CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('ICX_TERRITORY');
122 g_profile_ref_lang CONSTANT VARCHAR2(4) := FND_PROFILE.VALUE('HZ_REF_LANG');
123 g_profile_ref_territory CONSTANT VARCHAR2(2) := FND_PROFILE.VALUE('HZ_REF_TERRITORY');
124 g_profile_country_lang CONSTANT VARCHAR2(4) := FND_PROFILE.VALUE('HZ_LANG_FOR_COUNTRY_DISPLAY');
125 g_profile_def_addr_style CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('HZ_DEFAULT_ADDR_STYLE');
126 g_profile_def_name_style CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('HZ_DEFAULT_NAME_STYLE');
127
128 k_addr_table_name CONSTANT VARCHAR2(30) := 'HZ_LOCATIONS';
129 k_addr_table_pk CONSTANT VARCHAR2(30) := 'LOCATION_ID';
133 /************* PRIVATE PROCEDURE/FUNCTION DECLARATIONS ********************/
130 k_name_table_name CONSTANT VARCHAR2(30) := 'HZ_PARTIES';
131 k_name_table_pk CONSTANT VARCHAR2(30) := 'PARTY_ID';
132
134
135
136 PROCEDURE get_default_style (
137 p_object_name IN hz_styles_b.database_object_name%TYPE,
138 x_style_code OUT NOCOPY hz_styles_b.style_code%TYPE
139 );
140
141 /*
142 PROCEDURE get_default_ref_territory (
143 x_ref_territory_code OUT NOCOPY fnd_territories.territory_code%TYPE
144 );
145 */
146 PROCEDURE get_default_eloc_ref_territory (
147 x_ref_territory_code OUT NOCOPY fnd_territories.territory_code%TYPE
148 );
149
150 PROCEDURE get_default_ref_language (
151 x_ref_language_code OUT NOCOPY fnd_languages.language_code%TYPE
152 );
153
154 PROCEDURE get_country_name_lang (
155 x_country_name_lang OUT NOCOPY fnd_languages.language_code%TYPE
156 );
157
158 PROCEDURE load_internal_format_table(
159 p_style_format_code IN VARCHAR2,
160 p_variation_num IN NUMBER DEFAULT NULL,
161 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
162 x_loaded_rows_cnt IN OUT NOCOPY NUMBER
163 );
164
165 PROCEDURE add_parm_table_row(
166 p_parm_name IN VARCHAR2,
167 p_parm_value IN VARCHAR2,
168 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
169 x_loaded_rows_cnt IN OUT NOCOPY NUMBER,
170 p_parm_type IN VARCHAR2 DEFAULT 'V'
171 );
172
173 PROCEDURE create_sql_string(
174 p_table_name IN VARCHAR2,
175 x_pk_tbl IN OUT NOCOPY name_value_tbl_type,
176 p_pk_tbl_cnt IN NUMBER,
177 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
178 p_layout_tbl_cnt IN NUMBER,
179 x_sql_string IN OUT NOCOPY VARCHAR2
180 );
181
182 PROCEDURE execute_query(
183 p_sql_string IN VARCHAR2,
184 x_pk_tbl IN OUT NOCOPY name_value_tbl_type,
185 p_pk_tbl_cnt IN NUMBER,
186 p_layout_tbl_cnt IN NUMBER,
187 x_layout_tbl IN OUT NOCOPY layout_tbl_type
188 );
189
190 PROCEDURE format_results (
191 p_space_replace IN VARCHAR2,
192 p_layout_tbl_cnt IN NUMBER,
193 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
194 x_formatted_lines_tbl IN OUT NOCOPY string_tbl_type,
195 x_formatted_lines_cnt IN OUT NOCOPY NUMBER
196 );
197
198 PROCEDURE determine_variation (
199 p_style_format_code IN VARCHAR2,
200 p_object_name IN VARCHAR2,
201 p_object_pk_name IN VARCHAR2,
202 p_object_pk_value IN VARCHAR2,
203 x_variation_num OUT NOCOPY NUMBER
204 );
205
206 PROCEDURE determine_variation (
207 p_style_format_code IN VARCHAR2,
208 p_parm_tbl_cnt IN NUMBER,
209 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
210 x_variation_num OUT NOCOPY NUMBER
211 );
212
213 PROCEDURE copy_attribute_values (
214 p_parm_tbl_cnt IN NUMBER,
215 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
216 p_layout_tbl_cnt IN NUMBER,
217 x_layout_tbl IN OUT NOCOPY layout_tbl_type
218 );
219
220 PROCEDURE substitute_tokens (
221 p_parm_tbl_cnt IN NUMBER,
222 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
223 x_string IN OUT NOCOPY VARCHAR2
224 );
225
226 PROCEDURE set_context (
227 p_style_code IN hz_styles_b.style_code%TYPE,
228 p_style_format_code IN hz_style_formats_b.style_format_code%TYPE,
229 p_to_territory_code IN fnd_territories.territory_code%TYPE,
230 p_to_language_code IN fnd_languages.language_code%TYPE,
231 p_from_territory_code IN fnd_territories.territory_code%TYPE,
232 p_from_language_code IN fnd_languages.language_code%TYPE,
233 p_country_name_lang IN fnd_languages.language_code%TYPE
234 );
235
236
237 /********************* PUBLIC FORMATTING APIs *****************************/
238
239
240 /*=========================================================================+
241 |
242 | PROCEDURE: format_address (signature #1)
243 |
244 | DESCRIPTION
245 |
246 | This procedure will format an address of a location that is
247 | stored in HZ_LOCATIONS.
248 |
249 | SCOPE: Public
250 |
251 | ARGUMENTS: (see definition in specification)
252 |
253 +=========================================================================*/
254
255
256 PROCEDURE format_address (
257 -- input parameters
258 p_location_id IN NUMBER,
259 p_style_code IN VARCHAR2,
260 p_style_format_code IN VARCHAR2,
261 p_line_break IN VARCHAR2,
262 p_space_replace IN VARCHAR2,
263 -- optional context parameters
267 -- output parameters
264 p_to_language_code IN VARCHAR2,
265 p_country_name_lang IN VARCHAR2,
266 p_from_territory_code IN VARCHAR2,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2,
271 x_formatted_address OUT NOCOPY VARCHAR2,
272 x_formatted_lines_cnt OUT NOCOPY NUMBER,
273 x_formatted_address_tbl OUT NOCOPY string_tbl_type
274 ) IS
275
276 l_api_name VARCHAR2(30) := 'format_address(1)';
277 l_sql_string VARCHAR2(2000);
278
279 l_style_code hz_styles_b.style_code%TYPE;
280 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
281
282 l_variation_num NUMBER;
283
284 CURSOR c_location_territory(p_location_id IN NUMBER)
285 IS SELECT country FROM HZ_LOCATIONS
286 WHERE LOCATION_ID = p_location_id;
287
288 -- Context Information
289
290 l_to_territory_code fnd_territories.territory_code%TYPE;
291 l_to_language_code fnd_languages.language_code%TYPE;
292 l_from_territory_code fnd_territories.territory_code%TYPE;
293 l_from_language_code fnd_languages.language_code%TYPE;
294 l_country_name_lang fnd_languages.language_code%TYPE;
295
296
297 BEGIN
298 --
299 -- Reset return status and messages
300 --
301
302 x_return_status := fnd_api.g_ret_sts_success;
303
304 --
305 -- Get the territory code of the location.
306 -- for address formatting.
307 --
308
309 OPEN c_location_territory(p_location_id);
310 FETCH c_location_territory INTO l_to_territory_code;
311 IF c_location_territory%NOTFOUND THEN
312 CLOSE c_location_territory;
313 fnd_message.set_name('AR','HZ_FMT_INVALID_PK');
314 fnd_message.set_token('OBJECT_CODE',k_addr_table_name);
315 fnd_message.set_token('COLUMN_NAME',k_addr_table_pk);
316 fnd_message.set_token('COLUMN_VALUE',to_char(p_location_id));
317 fnd_msg_pub.add;
318 RAISE fnd_api.g_exc_error;
319 ELSE
320 CLOSE c_location_territory;
321 END IF;
322
323 --
324 -- Determine/Default the Context Information
325 --
326
327 -- "from" territory
328
329 IF p_from_territory_code IS NOT NULL THEN
330 l_from_territory_code := p_from_territory_code;
331 ELSE
332 get_default_ref_territory (
333 x_ref_territory_code => l_from_territory_code
334 );
335 END IF;
336
337 -- "from" language
338
339 get_default_ref_language (
340 x_ref_language_code => l_from_language_code
341 );
342
343 -- "to" territory was already assigned
344
345 -- "to" language
346
347 IF p_to_language_code IS NOT NULL THEN
348 l_to_language_code := p_to_language_code;
349 ELSE
350 l_to_language_code := l_from_language_code;
351 END IF;
352
353 -- language for country line
354
355 IF p_country_name_lang IS NOT NULL THEN
356 l_country_name_lang := p_country_name_lang;
357 ELSE
358 get_country_name_lang (
359 x_country_name_lang => l_country_name_lang
360 );
361 END IF;
362
363 --
364 -- Figure out NOCOPY which Style Format to use
365 --
366
367 IF p_style_format_code IS NOT NULL THEN
368 -- bug 2656819 fix
369 -- l_style_format_code := p_style_format_code;
370 BEGIN
371 select style_format_code into l_style_format_code
372 from hz_style_formats_b
373 where style_format_code = p_style_format_code;
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 l_style_format_code := null;
377 END;
378
379 ELSE
380 IF p_style_code IS NOT NULL THEN
381 l_style_code := p_style_code;
382 ELSE
383 get_default_style (
384 p_object_name => k_addr_table_name,
385 x_style_code => l_style_code
386 );
387
388 END IF;
389 IF l_style_code IS NOT NULL THEN
390 get_style_format (
391 p_style_code => l_style_code,
392 p_territory_code => l_to_territory_code,
393 p_language_code => l_to_language_code,
394 x_return_status => x_return_status,
395 x_msg_count => x_msg_count,
396 x_msg_data => x_msg_data,
397 x_style_format_code => l_style_format_code
398 );
399 END IF;
400 IF x_return_status <> fnd_api.g_ret_sts_success THEN
401 RAISE fnd_api.g_exc_error;
402 END IF;
403 END IF;
404
405 IF l_style_format_code IS NULL THEN
406 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
407 fnd_msg_pub.add;
408 RAISE fnd_api.g_exc_error;
409 END IF;
410
411 --
412 -- Make the context information available to external functions
413 -- (which are dynamically invoked from the formatting routines)
414 -- for the duration of the invocation of this function.
415 --
416
417 set_context (
418 p_style_code => l_style_code,
419 p_style_format_code => l_style_format_code,
420 p_to_territory_code => l_to_territory_code,
421 p_to_language_code => l_to_language_code,
422 p_from_territory_code => l_from_territory_code,
423 p_from_language_code => l_from_language_code,
424 p_country_name_lang => l_country_name_lang
425 );
426
427 --
428 -- Determine which format variation to use
429 --
430
431 determine_variation (
432 p_style_format_code => l_style_format_code,
433 p_object_name => k_addr_table_name,
434 p_object_pk_name => k_addr_table_pk,
435 p_object_pk_value => p_location_id,
439 --
436 x_variation_num => l_variation_num
437 );
438
440 -- Load the format layout database table into
441 -- an internal pl/sql table.
442 --
443
444 load_internal_format_table(
445 p_style_format_code => l_style_format_code,
446 p_variation_num => l_variation_num,
447 x_layout_tbl => g_layout_tbl,
448 x_loaded_rows_cnt => g_layout_tbl_cnt
449 );
450
451 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
452 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
453 fnd_msg_pub.add;
454 RAISE fnd_api.g_exc_error;
455 END IF;
456
457 --
458 -- Create a dynamic SQL query to get the address elements
459 --
460
461 g_pk_tbl_cnt := 1;
462 g_pk_tbl(1).parm_name := k_addr_table_pk;
463 g_pk_tbl(1).parm_value := p_location_id;
464 g_pk_tbl(1).parm_type := 'N'; -- Numeric
465
466 create_sql_string(
467 p_table_name => k_addr_table_name,
468 x_pk_tbl => g_pk_tbl,
469 p_pk_tbl_cnt => g_pk_tbl_cnt,
470 x_layout_tbl => g_layout_tbl,
471 p_layout_tbl_cnt => g_layout_tbl_cnt,
472 x_sql_string => l_sql_string
473 );
474
475 --
476 -- Run the dynamic SQL query, and populate the internal table
477 -- with the queried data.
478 --
479
480 execute_query(
481 p_sql_string => l_sql_string,
482 p_pk_tbl_cnt => g_pk_tbl_cnt,
483 x_pk_tbl => g_pk_tbl,
484 p_layout_tbl_cnt => g_layout_tbl_cnt,
485 x_layout_tbl => g_layout_tbl
486 );
487
488 --
489 -- Apply the "formatting rules" and format the results
490 --
491
492 format_results (
493 p_space_replace => p_space_replace,
494 p_layout_tbl_cnt => g_layout_tbl_cnt,
495 x_layout_tbl => g_layout_tbl,
496 x_formatted_lines_tbl => x_formatted_address_tbl,
497 x_formatted_lines_cnt => x_formatted_lines_cnt
498 );
499
500 --
501 -- Build the single formatting string from the table
502 --
503
504 IF x_formatted_lines_cnt > 0 THEN
505 FOR i IN 1 .. x_formatted_lines_cnt
506 LOOP
507 IF i>1 THEN
508 x_formatted_address := x_formatted_address || p_line_break || x_formatted_address_tbl(i);
509 ELSE
510 x_formatted_address := x_formatted_address_tbl(i);
511 END IF;
512 END LOOP;
513 END IF;
514
515
516 EXCEPTION
517
518 WHEN fnd_api.g_exc_error THEN
519 x_return_status := fnd_api.g_ret_sts_error;
520 fnd_msg_pub.count_and_get (
521 p_encoded => fnd_api.g_false,
522 p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525
526 WHEN OTHERS THEN
527 x_return_status := fnd_api.g_ret_sts_unexp_error;
528 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
529 fnd_msg_pub.add_exc_msg(
530 g_pkg_name, l_api_name
531 );
532 END IF;
533 fnd_msg_pub.count_and_get (
534 p_encoded => fnd_api.g_false,
535 p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538
539 END format_address;
540
541
542 /*=========================================================================+
543 |
544 | PROCEDURE: format_address (signature #2)
545 |
546 | DESCRIPTION
547 |
548 | This procedure will format an address. Parameters are supplied for
549 | various address elements, therefore this procedure can be used to
550 | format an address from any data source.
551 |
552 | SCOPE: Public
553 |
554 | ARGUMENTS: (see definition in specification)
555 |
556 +=========================================================================*/
557
558 PROCEDURE format_address (
559 -- input parameters
560 p_style_code IN VARCHAR2,
561 p_style_format_code IN VARCHAR2,
562 p_line_break IN VARCHAR2,
563 p_space_replace IN VARCHAR2,
564 -- optional context parameters
565 p_to_language_code IN VARCHAR2,
566 p_country_name_lang IN VARCHAR2,
567 p_from_territory_code IN VARCHAR2,
568 -- address components
569 p_address_line_1 IN VARCHAR2,
570 p_address_line_2 IN VARCHAR2,
571 p_address_line_3 IN VARCHAR2,
572 p_address_line_4 IN VARCHAR2,
573 p_city IN VARCHAR2,
574 p_postal_code IN VARCHAR2,
575 p_state IN VARCHAR2,
576 p_province IN VARCHAR2,
577 p_county IN VARCHAR2,
578 p_country IN VARCHAR2,
579 p_address_lines_phonetic IN VARCHAR2,
580 -- output parameters
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 x_formatted_address OUT NOCOPY VARCHAR2,
585 x_formatted_lines_cnt OUT NOCOPY NUMBER,
586 x_formatted_address_tbl OUT NOCOPY string_tbl_type
587 ) IS
588 l_api_name VARCHAR2(30) := 'format_address(2)';
589
590 l_style_code hz_styles_b.style_code%TYPE;
591 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
592
593 l_variation_num NUMBER;
594
595 -- Context Information
596
597 l_to_territory_code fnd_territories.territory_code%TYPE;
598 l_to_language_code fnd_languages.language_code%TYPE;
599 l_from_territory_code fnd_territories.territory_code%TYPE;
600 l_from_language_code fnd_languages.language_code%TYPE;
601 l_country_name_lang fnd_languages.language_code%TYPE;
602
603 BEGIN
604
605 --
606 -- Reset return status
607 --
608
609 x_return_status := fnd_api.g_ret_sts_success;
613 -- Determine/Default the Context Information
610
611
612 --
614 --
615
616 -- "from" territory
617
618 IF p_from_territory_code IS NOT NULL THEN
619 l_from_territory_code := substrb(p_from_territory_code,1,2);
620 ELSE
621 get_default_ref_territory (
622 x_ref_territory_code => l_from_territory_code
623 );
624 END IF;
625
626 -- "from" language
627
628 get_default_ref_language (
629 x_ref_language_code => l_from_language_code
630 );
631
632 -- "to" territory
633
634 l_to_territory_code := substrb(p_country,1,2);
635
636 -- "to" language
637
638 IF p_to_language_code IS NOT NULL THEN
639 l_to_language_code := p_to_language_code;
640 ELSE
641 l_to_language_code := l_from_language_code;
642 END IF;
643
644 -- language for country line
645
646 IF p_country_name_lang IS NOT NULL THEN
647 l_country_name_lang := p_country_name_lang;
648 ELSE
649 get_country_name_lang (
650 x_country_name_lang => l_country_name_lang
651 );
652 END IF;
653
654
655 --
656 -- Figure out NOCOPY which Style Format to use
657 --
658
659
660 IF p_style_format_code IS NOT NULL THEN
661 -- bug 2656819 fix
662 -- l_style_format_code := p_style_format_code;
663 BEGIN
664 select style_format_code into l_style_format_code
665 from hz_style_formats_b
666 where style_format_code = p_style_format_code;
667 EXCEPTION
668 WHEN NO_DATA_FOUND THEN
669 l_style_format_code := null;
670 END;
671
672 ELSE
673 IF p_style_code IS NOT NULL THEN
674 l_style_code := p_style_code;
675 ELSE
676 get_default_style (
677 p_object_name => k_addr_table_name,
678 x_style_code => l_style_code
679 );
680
681 END IF;
682 IF l_style_code IS NOT NULL THEN
683 get_style_format (
684 p_style_code => l_style_code,
685 p_territory_code => l_to_territory_code,
686 p_language_code => l_to_language_code,
687 x_return_status => x_return_status,
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 x_style_format_code => l_style_format_code
691 );
692 END IF;
693 IF x_return_status <> fnd_api.g_ret_sts_success THEN
694 RAISE fnd_api.g_exc_error;
695 END IF;
696 END IF;
697
698 IF l_style_format_code IS NULL THEN
699 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
700 fnd_msg_pub.add;
701 RAISE fnd_api.g_exc_error;
702 END IF;
703
704 --
705 -- Make the context information available to external functions
706 -- (which are dynamically invoked from the formatting routines)
707 -- for the duration of the invocation of this function.
708 --
709
710 set_context (
711 p_style_code => l_style_code,
712 p_style_format_code => l_style_format_code,
713 p_to_territory_code => l_to_territory_code,
714 p_to_language_code => l_to_language_code,
715 p_from_territory_code => l_from_territory_code,
716 p_from_language_code => l_from_language_code,
717 p_country_name_lang => l_country_name_lang
718 );
719
720 --
721 -- Load a PL/SQL mapping table so that we can access
722 -- the parameter values dynamically
723 --
724
725 g_parm_tbl_cnt := 0;
726 add_parm_table_row('ADDRESS1', p_address_line_1, g_parm_tbl, g_parm_tbl_cnt);
727 add_parm_table_row('ADDRESS2', p_address_line_2, g_parm_tbl, g_parm_tbl_cnt);
728 add_parm_table_row('ADDRESS3', p_address_line_3, g_parm_tbl, g_parm_tbl_cnt);
729 add_parm_table_row('ADDRESS4', p_address_line_4, g_parm_tbl, g_parm_tbl_cnt);
730 add_parm_table_row('CITY', p_city, g_parm_tbl, g_parm_tbl_cnt);
731 add_parm_table_row('POSTAL_CODE', p_postal_code, g_parm_tbl, g_parm_tbl_cnt);
732 add_parm_table_row('STATE', p_state, g_parm_tbl, g_parm_tbl_cnt);
733 add_parm_table_row('PROVINCE', p_province, g_parm_tbl, g_parm_tbl_cnt);
734 add_parm_table_row('COUNTY', p_county, g_parm_tbl, g_parm_tbl_cnt);
735 add_parm_table_row('COUNTRY', p_country, g_parm_tbl, g_parm_tbl_cnt);
736 add_parm_table_row('ADDRESS_LINES_PHONETIC', p_address_lines_phonetic,
737 g_parm_tbl, g_parm_tbl_cnt);
738
739
740 --
741 -- Determine which format variation to use
742 --
743
744 determine_variation (
745 p_style_format_code => l_style_format_code,
746 p_parm_tbl_cnt => g_parm_tbl_cnt,
747 x_parm_tbl => g_parm_tbl,
748 x_variation_num => l_variation_num
749 );
750
751 --
752 -- Load the format layout database table into
753 -- an internal pl/sql table.
754 --
755
756 load_internal_format_table(
757 p_style_format_code => l_style_format_code,
758 p_variation_num => l_variation_num,
759 x_layout_tbl => g_layout_tbl,
760 x_loaded_rows_cnt => g_layout_tbl_cnt
761 );
762
763 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
764 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
765 --fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
766 fnd_msg_pub.add;
767 RAISE fnd_api.g_exc_error;
768 END IF;
769
770 --
771 -- Copy attribute values from parameter table to layout table
772 --
773
774 copy_attribute_values (
775 p_parm_tbl_cnt => g_parm_tbl_cnt,
779 );
776 x_parm_tbl => g_parm_tbl,
777 p_layout_tbl_cnt => g_layout_tbl_cnt,
778 x_layout_tbl => g_layout_tbl
780
781 --
782 -- Apply the "formatting rules" and format the results
783 --
784
785 format_results (
786 p_space_replace => p_space_replace,
787 p_layout_tbl_cnt => g_layout_tbl_cnt,
788 x_layout_tbl => g_layout_tbl,
789 x_formatted_lines_tbl => x_formatted_address_tbl,
790 x_formatted_lines_cnt => x_formatted_lines_cnt
791 );
792
793 -- Build the single formatting string from the table
794
795 IF x_formatted_lines_cnt > 0 THEN
796 FOR i IN 1 .. x_formatted_lines_cnt
797 LOOP
798 IF i>1 THEN
799 x_formatted_address := x_formatted_address || p_line_break || x_formatted_address_tbl(i);
800 ELSE
801 x_formatted_address := x_formatted_address_tbl(i);
802 END IF;
803 END LOOP;
804 END IF;
805
806
807 EXCEPTION
808
809 WHEN fnd_api.g_exc_error THEN
810 x_return_status := fnd_api.g_ret_sts_error;
811 fnd_msg_pub.count_and_get (
812 p_encoded => fnd_api.g_false,
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816
817 WHEN OTHERS THEN
818 x_return_status := fnd_api.g_ret_sts_unexp_error;
819 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
820 fnd_msg_pub.add_exc_msg(
821 g_pkg_name, l_api_name
822 );
823 END IF;
824 fnd_msg_pub.count_and_get (
825 p_encoded => fnd_api.g_false,
826 p_count => x_msg_count,
827 p_data => x_msg_data
828 );
829
830 END format_address;
831
832 /*=========================================================================+
833 |
834 | PROCEDURE: format_eloc_address
835 |
836 | DESCRIPTION
837 |
838 | This procedure will format an address. Parameters are supplied for
839 | various address elements, therefore this procedure can be used to
840 | format an address from any data source.
841 |
842 | SCOPE: Public
843 |
844 | ARGUMENTS: (see definition in specification)
845 |
846 +=========================================================================*/
847
848 PROCEDURE format_eloc_address (
849 p_style_code IN VARCHAR2,
850 p_style_format_code IN VARCHAR2,
851 p_line_break IN VARCHAR2,
852 p_space_replace IN VARCHAR2,
853 p_to_language_code IN VARCHAR2,
854 p_country_name_lang IN VARCHAR2,
855 p_from_territory_code IN VARCHAR2,
856 p_address_line_1 IN VARCHAR2,
857 p_address_line_2 IN VARCHAR2,
858 p_address_line_3 IN VARCHAR2,
859 p_address_line_4 IN VARCHAR2,
860 p_city IN VARCHAR2,
861 p_postal_code IN VARCHAR2,
862 p_state IN VARCHAR2,
863 p_province IN VARCHAR2,
864 p_county IN VARCHAR2,
865 p_country IN VARCHAR2,
866 p_address_lines_phonetic IN VARCHAR2,
867 x_return_status OUT NOCOPY VARCHAR2,
868 x_msg_count OUT NOCOPY NUMBER,
869 x_msg_data OUT NOCOPY VARCHAR2,
870 x_formatted_address OUT NOCOPY VARCHAR2,
871 x_formatted_lines_cnt OUT NOCOPY NUMBER,
872 x_formatted_address_tbl OUT NOCOPY string_tbl_type
873 ) IS
874 l_api_name VARCHAR2(30) := 'format_address(2)';
875
876 l_style_code hz_styles_b.style_code%TYPE;
877 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
878
879 l_variation_num NUMBER;
880
881 -- Context Information
882
883 l_to_territory_code fnd_territories.territory_code%TYPE;
884 l_to_language_code fnd_languages.language_code%TYPE;
885 l_from_territory_code fnd_territories.territory_code%TYPE;
886 l_from_language_code fnd_languages.language_code%TYPE;
887 l_country_name_lang fnd_languages.language_code%TYPE;
888
889 BEGIN
890
891 --
892 -- Reset return status
893 --
894
895 x_return_status := fnd_api.g_ret_sts_success;
896
897
898 --
899 -- Determine/Default the Context Information
900 --
901
902 -- "from" territory
903
904 IF p_from_territory_code IS NOT NULL THEN
905 l_from_territory_code := substrb(p_from_territory_code,1,2);
906 ELSE
907 get_default_eloc_ref_territory (
908 x_ref_territory_code => l_from_territory_code
909 );
910 END IF;
911
912 -- "from" language
913
914 get_default_ref_language (
915 x_ref_language_code => l_from_language_code
916 );
917
918 -- "to" territory
919
920 l_to_territory_code := substrb(p_country,1,2);
921
922 -- "to" language
923
924 IF p_to_language_code IS NOT NULL THEN
925 l_to_language_code := p_to_language_code;
926 ELSE
927 l_to_language_code := l_from_language_code;
928 END IF;
929
930 -- language for country line
931
932 IF p_country_name_lang IS NOT NULL THEN
933 l_country_name_lang := p_country_name_lang;
934 ELSE
935 get_country_name_lang (
936 x_country_name_lang => l_country_name_lang
937 );
938 END IF;
939
940
941 --
942 -- Figure out NOCOPY which Style Format to use
943 --
944
945
946 IF p_style_format_code IS NOT NULL THEN
947 -- bug 2656819 fix
948 -- l_style_format_code := p_style_format_code;
949 BEGIN
950 select style_format_code into l_style_format_code
951 from hz_style_formats_b
952 where style_format_code = p_style_format_code;
953 EXCEPTION
954 WHEN NO_DATA_FOUND THEN
958 ELSE
955 l_style_format_code := null;
956 END;
957
959 IF p_style_code IS NOT NULL THEN
960 l_style_code := p_style_code;
961 ELSE
962 get_default_style (
963 p_object_name => k_addr_table_name,
964 x_style_code => l_style_code
965 );
966
967 END IF;
968 IF l_style_code IS NOT NULL THEN
969 get_style_format (
970 p_style_code => l_style_code,
971 p_territory_code => l_to_territory_code,
972 p_language_code => l_to_language_code,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 x_style_format_code => l_style_format_code
977 );
978 END IF;
979 IF x_return_status <> fnd_api.g_ret_sts_success THEN
980 RAISE fnd_api.g_exc_error;
981 END IF;
982 END IF;
983
984 IF l_style_format_code IS NULL THEN
985 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
986 fnd_msg_pub.add;
987 RAISE fnd_api.g_exc_error;
988 END IF;
989
990 --
991 -- Make the context information available to external functions
992 -- (which are dynamically invoked from the formatting routines)
993 -- for the duration of the invocation of this function.
994 --
995
996 set_context (
997 p_style_code => l_style_code,
998 p_style_format_code => l_style_format_code,
999 p_to_territory_code => l_to_territory_code,
1000 p_to_language_code => l_to_language_code,
1001 p_from_territory_code => l_from_territory_code,
1002 p_from_language_code => l_from_language_code,
1003 p_country_name_lang => l_country_name_lang
1004 );
1005
1006 --
1007 -- Load a PL/SQL mapping table so that we can access
1008 -- the parameter values dynamically
1009 --
1010
1011 g_parm_tbl_cnt := 0;
1012 add_parm_table_row('ADDRESS1', p_address_line_1, g_parm_tbl, g_parm_tbl_cnt);
1013 add_parm_table_row('ADDRESS2', p_address_line_2, g_parm_tbl, g_parm_tbl_cnt);
1014 add_parm_table_row('ADDRESS3', p_address_line_3, g_parm_tbl, g_parm_tbl_cnt);
1015 add_parm_table_row('ADDRESS4', p_address_line_4, g_parm_tbl, g_parm_tbl_cnt);
1016 add_parm_table_row('CITY', p_city, g_parm_tbl, g_parm_tbl_cnt);
1017 add_parm_table_row('POSTAL_CODE', p_postal_code, g_parm_tbl, g_parm_tbl_cnt);
1018 add_parm_table_row('STATE', p_state, g_parm_tbl, g_parm_tbl_cnt);
1019 add_parm_table_row('PROVINCE', p_province, g_parm_tbl, g_parm_tbl_cnt);
1020 add_parm_table_row('COUNTY', p_county, g_parm_tbl, g_parm_tbl_cnt);
1021 add_parm_table_row('COUNTRY', p_country, g_parm_tbl, g_parm_tbl_cnt);
1022 add_parm_table_row('ADDRESS_LINES_PHONETIC', p_address_lines_phonetic,
1023 g_parm_tbl, g_parm_tbl_cnt);
1024
1025
1026 --
1027 -- Determine which format variation to use
1028 --
1029
1030 determine_variation (
1031 p_style_format_code => l_style_format_code,
1032 p_parm_tbl_cnt => g_parm_tbl_cnt,
1033 x_parm_tbl => g_parm_tbl,
1034 x_variation_num => l_variation_num
1035 );
1036
1037 --
1038 -- Load the format layout database table into
1039 -- an internal pl/sql table.
1040 --
1041
1042 load_internal_format_table(
1043 p_style_format_code => l_style_format_code,
1044 p_variation_num => l_variation_num,
1045 x_layout_tbl => g_layout_tbl,
1046 x_loaded_rows_cnt => g_layout_tbl_cnt
1047 );
1048
1049 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
1050 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
1051 --fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
1052 fnd_msg_pub.add;
1053 RAISE fnd_api.g_exc_error;
1054 END IF;
1055
1056 --
1057 -- Copy attribute values from parameter table to layout table
1058 --
1059
1060 copy_attribute_values (
1061 p_parm_tbl_cnt => g_parm_tbl_cnt,
1062 x_parm_tbl => g_parm_tbl,
1063 p_layout_tbl_cnt => g_layout_tbl_cnt,
1064 x_layout_tbl => g_layout_tbl
1065 );
1066
1067 --
1068 -- Apply the "formatting rules" and format the results
1069 --
1070
1071 format_results (
1072 p_space_replace => p_space_replace,
1073 p_layout_tbl_cnt => g_layout_tbl_cnt,
1074 x_layout_tbl => g_layout_tbl,
1075 x_formatted_lines_tbl => x_formatted_address_tbl,
1076 x_formatted_lines_cnt => x_formatted_lines_cnt
1077 );
1078
1079 -- Build the single formatting string from the table
1080
1081 IF x_formatted_lines_cnt > 0 THEN
1082 FOR i IN 1 .. x_formatted_lines_cnt
1083 LOOP
1084 IF i>1 THEN
1085 x_formatted_address := x_formatted_address || p_line_break || x_formatted_address_tbl(i);
1086 ELSE
1087 x_formatted_address := x_formatted_address_tbl(i);
1088 END IF;
1089 END LOOP;
1090 END IF;
1091
1092
1093 EXCEPTION
1094
1095 WHEN fnd_api.g_exc_error THEN
1096 x_return_status := fnd_api.g_ret_sts_error;
1097 fnd_msg_pub.count_and_get (
1098 p_encoded => fnd_api.g_false,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102
1103 WHEN OTHERS THEN
1104 x_return_status := fnd_api.g_ret_sts_unexp_error;
1105 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1106 fnd_msg_pub.add_exc_msg(
1107 g_pkg_name, l_api_name
1108 );
1109 END IF;
1110 fnd_msg_pub.count_and_get (
1111 p_encoded => fnd_api.g_false,
1112 p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115
1116 END format_eloc_address;
1117
1118 /*=========================================================================+
1122 | DESCRIPTION
1119 |
1120 | PROCEDURE: format_address_layout (signature #1)
1121 |
1123 |
1124 | This procedure will format an address layout of a location that is
1125 | stored in HZ_LOCATIONS.
1126 |
1127 | SCOPE: Public
1128 |
1129 | ARGUMENTS: (see definition in specification)
1130 |
1131 +=========================================================================*/
1132
1133
1134 PROCEDURE format_address_layout (
1135 -- input parameters
1136 p_location_id IN NUMBER,
1137 p_style_code IN VARCHAR2,
1138 p_style_format_code IN VARCHAR2,
1139 p_line_break IN VARCHAR2,
1140 p_space_replace IN VARCHAR2,
1141 -- optional context parameters
1142 p_to_language_code IN VARCHAR2,
1143 p_country_name_lang IN VARCHAR2,
1144 p_from_territory_code IN VARCHAR2,
1145 -- output parameters
1146 x_return_status OUT NOCOPY VARCHAR2,
1147 x_msg_count OUT NOCOPY NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 x_layout_tbl_cnt OUT NOCOPY NUMBER,
1150 x_layout_tbl OUT NOCOPY layout_tbl_type
1151 ) IS
1152
1153 l_api_name VARCHAR2(30) := 'format_address(1)';
1154 l_sql_string VARCHAR2(2000);
1155
1156 l_style_code hz_styles_b.style_code%TYPE;
1157 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
1158
1159 l_variation_num NUMBER;
1160
1161 CURSOR c_location_territory(p_location_id IN NUMBER)
1162 IS SELECT country FROM HZ_LOCATIONS
1163 WHERE LOCATION_ID = p_location_id;
1164
1165 -- Context Information
1166
1167 l_to_territory_code fnd_territories.territory_code%TYPE;
1168 l_to_language_code fnd_languages.language_code%TYPE;
1169 l_from_territory_code fnd_territories.territory_code%TYPE;
1170 l_from_language_code fnd_languages.language_code%TYPE;
1171 l_country_name_lang fnd_languages.language_code%TYPE;
1172
1173
1174 BEGIN
1175 --
1176 -- Reset return status and messages
1177 --
1178
1179 x_return_status := fnd_api.g_ret_sts_success;
1180
1181 --
1182 -- Get the territory code of the location.
1183 -- for address formatting.
1184 --
1185
1186 OPEN c_location_territory(p_location_id);
1187 FETCH c_location_territory INTO l_to_territory_code;
1188 IF c_location_territory%NOTFOUND THEN
1189 CLOSE c_location_territory;
1190 fnd_message.set_name('AR','HZ_FMT_INVALID_PK');
1191 fnd_message.set_token('OBJECT_CODE',k_addr_table_name);
1192 fnd_message.set_token('COLUMN_NAME',k_addr_table_pk);
1193 fnd_message.set_token('COLUMN_VALUE',to_char(p_location_id));
1194 fnd_msg_pub.add;
1195 RAISE fnd_api.g_exc_error;
1196 ELSE
1197 CLOSE c_location_territory;
1198 END IF;
1199
1200 --
1201 -- Determine/Default the Context Information
1202 --
1203
1204 -- "from" territory
1205
1206 IF p_from_territory_code IS NOT NULL THEN
1207 l_from_territory_code := p_from_territory_code;
1208 ELSE
1209 get_default_ref_territory (
1210 x_ref_territory_code => l_from_territory_code
1211 );
1212 END IF;
1213
1214 -- "from" language
1215
1216 get_default_ref_language (
1217 x_ref_language_code => l_from_language_code
1218 );
1219
1220 -- "to" territory was already assigned
1221
1222 -- "to" language
1223
1224 IF p_to_language_code IS NOT NULL THEN
1225 l_to_language_code := p_to_language_code;
1226 ELSE
1227 l_to_language_code := l_from_language_code;
1228 END IF;
1229
1230 -- language for country line
1231
1232 IF p_country_name_lang IS NOT NULL THEN
1233 l_country_name_lang := p_country_name_lang;
1234 ELSE
1235 get_country_name_lang (
1236 x_country_name_lang => l_country_name_lang
1237 );
1238 END IF;
1239
1240 --
1241 -- Figure out NOCOPY which Style Format to use
1242 --
1243
1244 IF p_style_format_code IS NOT NULL THEN
1245 -- bug 2656819 fix
1246 -- l_style_format_code := p_style_format_code;
1247 BEGIN
1248 select style_format_code into l_style_format_code
1249 from hz_style_formats_b
1250 where style_format_code = p_style_format_code;
1251 EXCEPTION
1252 WHEN NO_DATA_FOUND THEN
1253 l_style_format_code := null;
1254 END;
1255
1256 ELSE
1257 IF p_style_code IS NOT NULL THEN
1258 l_style_code := p_style_code;
1259 ELSE
1260 get_default_style (
1261 p_object_name => k_addr_table_name,
1262 x_style_code => l_style_code
1263 );
1264
1265 END IF;
1266 IF l_style_code IS NOT NULL THEN
1267 get_style_format (
1268 p_style_code => l_style_code,
1269 p_territory_code => l_to_territory_code,
1270 p_language_code => l_to_language_code,
1271 x_return_status => x_return_status,
1272 x_msg_count => x_msg_count,
1273 x_msg_data => x_msg_data,
1274 x_style_format_code => l_style_format_code
1275 );
1276 END IF;
1277 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1278 RAISE fnd_api.g_exc_error;
1279 END IF;
1280 END IF;
1281
1282 IF l_style_format_code IS NULL THEN
1283 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
1284 fnd_msg_pub.add;
1285 RAISE fnd_api.g_exc_error;
1286 END IF;
1287
1288 --
1289 -- Make the context information available to external functions
1290 -- (which are dynamically invoked from the formatting routines)
1291 -- for the duration of the invocation of this function.
1292 --
1293
1294 set_context (
1298 p_to_language_code => l_to_language_code,
1295 p_style_code => l_style_code,
1296 p_style_format_code => l_style_format_code,
1297 p_to_territory_code => l_to_territory_code,
1299 p_from_territory_code => l_from_territory_code,
1300 p_from_language_code => l_from_language_code,
1301 p_country_name_lang => l_country_name_lang
1302 );
1303
1304 --
1305 -- Determine which format variation to use
1306 --
1307
1308 determine_variation (
1309 p_style_format_code => l_style_format_code,
1310 p_object_name => k_addr_table_name,
1311 p_object_pk_name => k_addr_table_pk,
1312 p_object_pk_value => p_location_id,
1313 x_variation_num => l_variation_num
1314 );
1315
1316 --
1317 -- Load the format layout database table into
1318 -- an internal pl/sql table.
1319 --
1320
1321 load_internal_format_table(
1322 p_style_format_code => l_style_format_code,
1323 p_variation_num => l_variation_num,
1324 x_layout_tbl => g_layout_tbl,
1325 x_loaded_rows_cnt => g_layout_tbl_cnt
1326 );
1327
1328 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
1329 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
1330 -- fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
1331 fnd_msg_pub.add;
1332 RAISE fnd_api.g_exc_error;
1333 END IF;
1334
1335 --
1336 -- Create a dynamic SQL query to get the address elements
1337 --
1338
1339 g_pk_tbl_cnt := 1;
1340 g_pk_tbl(1).parm_name := k_addr_table_pk;
1341 g_pk_tbl(1).parm_value := p_location_id;
1342 g_pk_tbl(1).parm_type := 'N'; -- Numeric
1343
1344 create_sql_string(
1345 p_table_name => k_addr_table_name,
1346 x_pk_tbl => g_pk_tbl,
1347 p_pk_tbl_cnt => g_pk_tbl_cnt,
1348 x_layout_tbl => g_layout_tbl,
1349 p_layout_tbl_cnt => g_layout_tbl_cnt,
1350 x_sql_string => l_sql_string
1351 );
1352
1353 --
1354 -- Run the dynamic SQL query, and populate the internal table
1355 -- with the queried data.
1356 --
1357
1358 execute_query(
1359 p_sql_string => l_sql_string,
1360 p_pk_tbl_cnt => g_pk_tbl_cnt,
1361 x_pk_tbl => g_pk_tbl,
1362 p_layout_tbl_cnt => g_layout_tbl_cnt,
1363 x_layout_tbl => g_layout_tbl
1364 );
1365
1366 x_layout_tbl_cnt := g_layout_tbl_cnt;
1367 IF x_layout_tbl_cnt > 0 THEN
1368 FOR i IN 1 .. x_layout_tbl_cnt
1369 LOOP
1370 x_layout_tbl(i) := g_layout_tbl(i);
1371 END LOOP;
1372 END IF;
1373
1374 EXCEPTION
1375
1376 WHEN fnd_api.g_exc_error THEN
1377 x_return_status := fnd_api.g_ret_sts_error;
1378 fnd_msg_pub.count_and_get (
1379 p_encoded => fnd_api.g_false,
1380 p_count => x_msg_count,
1381 p_data => x_msg_data
1382 );
1383
1384 WHEN OTHERS THEN
1385 x_return_status := fnd_api.g_ret_sts_unexp_error;
1386 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1387 fnd_msg_pub.add_exc_msg(
1388 g_pkg_name, l_api_name
1389 );
1390 END IF;
1391 fnd_msg_pub.count_and_get (
1392 p_encoded => fnd_api.g_false,
1393 p_count => x_msg_count,
1394 p_data => x_msg_data
1395 );
1396
1397 END format_address_layout;
1398
1399 /*=========================================================================+
1400 |
1401 | PROCEDURE: format_address_layout (signature #2)
1402 |
1403 | DESCRIPTION
1404 |
1405 | This procedure will format an address layout. Parameters are supplied for
1406 | various address elements, therefore this procedure can be used to
1407 | format an address layout from any data source.
1408 |
1409 | SCOPE: Public
1410 |
1411 | ARGUMENTS: (see definition in specification)
1412 |
1413 +=========================================================================*/
1414
1415 PROCEDURE format_address_layout (
1416 -- input parameters
1417 p_style_code IN VARCHAR2,
1418 p_style_format_code IN VARCHAR2,
1419 p_line_break IN VARCHAR2,
1420 p_space_replace IN VARCHAR2,
1421 -- optional context parameters
1422 p_to_language_code IN VARCHAR2,
1423 p_country_name_lang IN VARCHAR2,
1424 p_from_territory_code IN VARCHAR2,
1425 -- address components
1426 p_address_line_1 IN VARCHAR2,
1427 p_address_line_2 IN VARCHAR2,
1428 p_address_line_3 IN VARCHAR2,
1429 p_address_line_4 IN VARCHAR2,
1430 p_city IN VARCHAR2,
1431 p_postal_code IN VARCHAR2,
1432 p_state IN VARCHAR2,
1433 p_province IN VARCHAR2,
1434 p_county IN VARCHAR2,
1435 p_country IN VARCHAR2,
1436 p_address_lines_phonetic IN VARCHAR2,
1437 -- output parameters
1438 x_return_status OUT NOCOPY VARCHAR2,
1439 x_msg_count OUT NOCOPY NUMBER,
1440 x_msg_data OUT NOCOPY VARCHAR2,
1441 x_layout_tbl_cnt OUT NOCOPY NUMBER,
1442 x_layout_tbl OUT NOCOPY layout_tbl_type
1443 ) IS
1444 l_api_name VARCHAR2(30) := 'format_address(2)';
1445
1446 l_style_code hz_styles_b.style_code%TYPE;
1447 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
1448
1449 l_variation_num NUMBER;
1450
1451 -- Context Information
1452
1453 l_to_territory_code fnd_territories.territory_code%TYPE;
1454 l_to_language_code fnd_languages.language_code%TYPE;
1458
1455 l_from_territory_code fnd_territories.territory_code%TYPE;
1456 l_from_language_code fnd_languages.language_code%TYPE;
1457 l_country_name_lang fnd_languages.language_code%TYPE;
1459 l_sql_string varchar2(200);
1460 l_var_counts number := 0;
1461
1462 BEGIN
1463
1464 --
1465 -- Reset return status
1466 --
1467
1468 x_return_status := fnd_api.g_ret_sts_success;
1469
1470
1471 --
1472 -- Determine/Default the Context Information
1473 --
1474
1475 -- "from" territory
1476
1477 IF p_from_territory_code IS NOT NULL THEN
1478 l_from_territory_code := substrb(p_from_territory_code,1,2);
1479 ELSE
1480 get_default_ref_territory (
1481 x_ref_territory_code => l_from_territory_code
1482 );
1483 END IF;
1484
1485 -- "from" language
1486
1487 get_default_ref_language (
1488 x_ref_language_code => l_from_language_code
1489 );
1490
1491 -- "to" territory
1492
1493 l_to_territory_code := substrb(p_country,1,2);
1494
1495 -- "to" language
1496
1497 IF p_to_language_code IS NOT NULL THEN
1498 l_to_language_code := p_to_language_code;
1499 ELSE
1500 l_to_language_code := l_from_language_code;
1501 END IF;
1502
1503 -- language for country line
1504
1505 IF p_country_name_lang IS NOT NULL THEN
1506 l_country_name_lang := p_country_name_lang;
1507 ELSE
1508 get_country_name_lang (
1509 x_country_name_lang => l_country_name_lang
1510 );
1511 END IF;
1512
1513
1514 --
1515 -- Figure out NOCOPY which Style Format to use
1516 --
1517
1518
1519 IF p_style_format_code IS NOT NULL THEN
1520 -- bug 2656819 fix
1521 -- l_style_format_code := p_style_format_code;
1522 BEGIN
1523 select style_format_code into l_style_format_code
1524 from hz_style_formats_b
1525 where style_format_code = p_style_format_code;
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 l_style_format_code := null;
1529 END;
1530
1531 ELSE
1532 IF p_style_code IS NOT NULL THEN
1533 l_style_code := p_style_code;
1534 ELSE
1535 get_default_style (
1536 p_object_name => k_addr_table_name,
1537 x_style_code => l_style_code
1538 );
1539
1540 END IF;
1541 IF l_style_code IS NOT NULL THEN
1542 get_style_format (
1543 p_style_code => l_style_code,
1544 p_territory_code => l_to_territory_code,
1545 p_language_code => l_to_language_code,
1546 x_return_status => x_return_status,
1547 x_msg_count => x_msg_count,
1548 x_msg_data => x_msg_data,
1549 x_style_format_code => l_style_format_code
1550 );
1551 END IF;
1552 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1553 RAISE fnd_api.g_exc_error;
1554 END IF;
1555 END IF;
1556
1557 IF l_style_format_code IS NULL THEN
1558 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
1559 fnd_msg_pub.add;
1560 RAISE fnd_api.g_exc_error;
1561 END IF;
1562
1563 --
1564 -- Make the context information available to external functions
1565 -- (which are dynamically invoked from the formatting routines)
1566 -- for the duration of the invocation of this function.
1567 --
1568
1569 set_context (
1570 p_style_code => l_style_code,
1571 p_style_format_code => l_style_format_code,
1572 p_to_territory_code => l_to_territory_code,
1573 p_to_language_code => l_to_language_code,
1574 p_from_territory_code => l_from_territory_code,
1575 p_from_language_code => l_from_language_code,
1576 p_country_name_lang => l_country_name_lang
1577 );
1578
1579 --log the context
1580 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1581 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:p_country:' || p_country);
1582 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_style_code:' || l_style_code);
1583 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_style_format_code:' || l_style_format_code);
1584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_to_territory_code:' || l_to_territory_code);
1585 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_to_language_code:' || l_to_language_code);
1586 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_from_territory_code:' || l_from_territory_code);
1587 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_from_language_code:' || l_from_language_code);
1588 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT:l_country_name_lang:' || l_country_name_lang);
1589 end if;
1590 --
1591 -- Load a PL/SQL mapping table so that we can access
1592 -- the parameter values dynamically
1593 --
1594
1595 g_parm_tbl_cnt := 0;
1596 add_parm_table_row('ADDRESS1', p_address_line_1, g_parm_tbl, g_parm_tbl_cnt);
1597 add_parm_table_row('ADDRESS2', p_address_line_2, g_parm_tbl, g_parm_tbl_cnt);
1598 add_parm_table_row('ADDRESS3', p_address_line_3, g_parm_tbl, g_parm_tbl_cnt);
1599 add_parm_table_row('ADDRESS4', p_address_line_4, g_parm_tbl, g_parm_tbl_cnt);
1600 add_parm_table_row('CITY', p_city, g_parm_tbl, g_parm_tbl_cnt);
1601 add_parm_table_row('POSTAL_CODE', p_postal_code, g_parm_tbl, g_parm_tbl_cnt);
1602 add_parm_table_row('STATE', p_state, g_parm_tbl, g_parm_tbl_cnt);
1603 add_parm_table_row('PROVINCE', p_province, g_parm_tbl, g_parm_tbl_cnt);
1604 add_parm_table_row('COUNTY', p_county, g_parm_tbl, g_parm_tbl_cnt);
1605 add_parm_table_row('COUNTRY', p_country, g_parm_tbl, g_parm_tbl_cnt);
1609
1606 add_parm_table_row('ADDRESS_LINES_PHONETIC', p_address_lines_phonetic,
1607 g_parm_tbl, g_parm_tbl_cnt);
1608
1610 --
1611 -- Determine which format variation to use
1612 --
1613
1614 determine_variation (
1615 p_style_format_code => l_style_format_code,
1616 p_parm_tbl_cnt => g_parm_tbl_cnt,
1617 x_parm_tbl => g_parm_tbl,
1618 x_variation_num => l_variation_num
1619 );
1620
1621 -- bug 3636389, county field for US address create/update
1622 -- temp workaround of the design/data model issue
1623 -- log the variation number
1624 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1625 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT: returned l_variation_num: ' || l_variation_num);
1626 end if;
1627
1628 if(p_country = 'US') then
1629 l_sql_string := 'select count(1) from HZ_STYLE_FMT_VARIATIONS ' ||
1630 'where STYLE_FORMAT_CODE = :1 ' ||
1631 'and VARIATION_NUMBER = 2 ' ||
1632 'and (SYSDATE > start_date_active ' ||
1633 'and SYSDATE <= NVL(end_date_active, SYSDATE))';
1634
1635 BEGIN
1636 EXECUTE IMMEDIATE l_sql_string INTO l_var_counts USING l_style_format_code;
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639 NULL;
1640 WHEN OTHERS THEN
1641 NULL;
1642 END;
1643 if (l_var_counts = 1) then
1644 l_variation_num := 2; --with county
1645 end if;
1646 end if;
1647 -- log the variation number
1648 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1649 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'ar.hz.plsql','ADDRESS FORMAT: using l_variation_num: ' || l_variation_num);
1650 end if;
1651 --
1652 -- Load the format layout database table into
1653 -- an internal pl/sql table.
1654 --
1655
1656 load_internal_format_table(
1657 p_style_format_code => l_style_format_code,
1658 p_variation_num => l_variation_num,
1659 x_layout_tbl => g_layout_tbl,
1660 x_loaded_rows_cnt => g_layout_tbl_cnt
1661 );
1662
1663 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
1664 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
1665 -- fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
1666 fnd_msg_pub.add;
1667 RAISE fnd_api.g_exc_error;
1668 END IF;
1669
1670 --
1671 -- Copy attribute values from parameter table to layout table
1672 --
1673
1674 copy_attribute_values (
1675 p_parm_tbl_cnt => g_parm_tbl_cnt,
1676 x_parm_tbl => g_parm_tbl,
1677 p_layout_tbl_cnt => g_layout_tbl_cnt,
1678 x_layout_tbl => g_layout_tbl
1679 );
1680
1681 x_layout_tbl_cnt := g_layout_tbl_cnt;
1682 IF x_layout_tbl_cnt > 0 THEN
1683 FOR i IN 1 .. x_layout_tbl_cnt
1684 LOOP
1685 x_layout_tbl(i) := g_layout_tbl(i);
1686 END LOOP;
1687 END IF;
1688
1689 EXCEPTION
1690
1691 WHEN fnd_api.g_exc_error THEN
1692 x_return_status := fnd_api.g_ret_sts_error;
1693 fnd_msg_pub.count_and_get (
1694 p_encoded => fnd_api.g_false,
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698
1699 WHEN OTHERS THEN
1700 x_return_status := fnd_api.g_ret_sts_unexp_error;
1701 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1702 fnd_msg_pub.add_exc_msg(
1703 g_pkg_name, l_api_name
1704 );
1705 END IF;
1706 fnd_msg_pub.count_and_get (
1707 p_encoded => fnd_api.g_false,
1708 p_count => x_msg_count,
1709 p_data => x_msg_data
1710 );
1711
1712 END format_address_layout;
1713
1714
1715 /*=========================================================================+
1716 |
1717 | PROCEDURE: format_name (signature #1)
1718 |
1719 | DESCRIPTION
1720 |
1721 | This procedure will format a name of a person party that is
1722 | stored in HZ_PARTIES.
1723 |
1724 |
1725 | SCOPE: Public
1726 |
1727 | ARGUMENTS: (see definition in specification)
1728 |
1729 +=========================================================================*/
1730
1731 PROCEDURE format_name (
1732 -- input parameters
1733 p_party_id IN NUMBER,
1734 p_style_code IN VARCHAR2,
1735 p_style_format_code IN VARCHAR2,
1736 p_line_break IN VARCHAR2,
1737 p_space_replace IN VARCHAR2,
1738 -- optional context parameters
1739 p_ref_language_code IN VARCHAR2,
1740 p_ref_territory_code IN VARCHAR2,
1741 -- output parameters
1742 x_return_status OUT NOCOPY VARCHAR2,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745 x_formatted_name OUT NOCOPY VARCHAR2,
1746 x_formatted_lines_cnt OUT NOCOPY NUMBER,
1747 x_formatted_name_tbl OUT NOCOPY string_tbl_type
1748 ) IS
1749 l_api_name VARCHAR2(30) := 'format_name(1)';
1750 l_sql_string VARCHAR2(2000);
1751
1752 l_style_code hz_styles_b.style_code%TYPE;
1753 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
1754 l_territory_code fnd_territories.territory_code%TYPE;
1755 l_language_code fnd_languages.language_code%TYPE;
1756
1757 l_variation_num NUMBER;
1758
1759 BEGIN
1760 --
1761 -- Reset return status and messages
1762 --
1763
1764 x_return_status := fnd_api.g_ret_sts_success;
1765
1766 --
1772 IF p_ref_territory_code IS NOT NULL THEN
1767 -- Determine/Default the Context Information
1768 --
1769
1770 -- territory
1771
1773 l_territory_code := p_ref_territory_code;
1774 ELSE
1775 get_default_ref_territory (
1776 x_ref_territory_code => l_territory_code
1777 );
1778 END IF;
1779
1780 -- language
1781
1782 IF p_ref_language_code IS NOT NULL THEN
1783 l_language_code := p_ref_language_code;
1784 ELSE
1785 get_default_ref_language (
1786 x_ref_language_code => l_language_code
1787 );
1788 END IF;
1789
1790 --
1791 -- Figure out NOCOPY which Style Format to use
1792 --
1793
1794 IF p_style_format_code IS NOT NULL THEN
1795 -- bug 2656819 fix
1796 -- l_style_format_code := p_style_format_code;
1797 BEGIN
1798 select style_format_code into l_style_format_code
1799 from hz_style_formats_b
1800 where style_format_code = p_style_format_code;
1801 EXCEPTION
1802 WHEN NO_DATA_FOUND THEN
1803 l_style_format_code := null;
1804 END;
1805
1806 ELSE
1807 IF p_style_code IS NOT NULL THEN
1808 l_style_code := p_style_code;
1809 ELSE
1810 get_default_style (
1811 p_object_name => k_name_table_name,
1812 x_style_code => l_style_code
1813 );
1814
1815 END IF;
1816 IF l_style_code IS NOT NULL THEN
1817 get_style_format (
1818 p_style_code => l_style_code,
1819 p_territory_code => l_territory_code,
1820 p_language_code => l_language_code,
1821 x_return_status => x_return_status,
1822 x_msg_count => x_msg_count,
1823 x_msg_data => x_msg_data,
1824 x_style_format_code => l_style_format_code
1825 );
1826 END IF;
1827 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1828 RAISE fnd_api.g_exc_error;
1829 END IF;
1830 END IF;
1831
1832
1833 IF l_style_format_code IS NULL THEN
1834 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
1835 fnd_msg_pub.add;
1836 RAISE fnd_api.g_exc_error;
1837 END IF;
1838
1839 --
1840 -- Make the context information available to external functions
1841 -- (which are dynamically invoked from the formatting routines)
1842 -- for the duration of the invocation of this function.
1843 --
1844
1845 set_context (
1846 p_style_code => l_style_code,
1847 p_style_format_code => l_style_format_code,
1848 p_to_territory_code => l_territory_code,
1849 p_to_language_code => l_language_code,
1850 p_from_territory_code => l_territory_code,
1851 p_from_language_code => l_language_code,
1852 p_country_name_lang => l_language_code
1853 );
1854
1855 --
1856 -- Determine which format variation to use
1857 --
1858
1859 determine_variation (
1860 p_style_format_code => l_style_format_code,
1861 p_object_name => k_name_table_name,
1862 p_object_pk_name => k_name_table_pk,
1863 p_object_pk_value => p_party_id,
1864 x_variation_num => l_variation_num
1865 );
1866
1867 --
1868 -- Load the format layout database table into
1869 -- an internal pl/sql table.
1870 --
1871
1872 load_internal_format_table(
1873 p_style_format_code => l_style_format_code,
1874 p_variation_num => l_variation_num,
1875 x_layout_tbl => g_layout_tbl,
1876 x_loaded_rows_cnt => g_layout_tbl_cnt
1877 );
1878
1879 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
1880 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
1881 -- fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
1882 fnd_msg_pub.add;
1883 RAISE fnd_api.g_exc_error;
1884 END IF;
1885
1886 --
1887 -- Create a dynamic SQL query to get the name elements
1888 --
1889
1890 g_pk_tbl_cnt := 1;
1894
1891 g_pk_tbl(1).parm_name := k_name_table_pk;
1892 g_pk_tbl(1).parm_value := p_party_id;
1893 g_pk_tbl(1).parm_type := 'N'; -- Numeric
1895 create_sql_string(
1896 p_table_name => k_name_table_name,
1897 x_pk_tbl => g_pk_tbl,
1898 p_pk_tbl_cnt => g_pk_tbl_cnt,
1899 x_layout_tbl => g_layout_tbl,
1900 p_layout_tbl_cnt => g_layout_tbl_cnt,
1901 x_sql_string => l_sql_string
1902 );
1903
1904 --
1905 -- Run the dynamic SQL query, and populate the internal table
1906 -- with the queried data.
1907 --
1908
1909 execute_query(
1910 p_sql_string => l_sql_string,
1911 p_pk_tbl_cnt => g_pk_tbl_cnt,
1912 x_pk_tbl => g_pk_tbl,
1913 p_layout_tbl_cnt => g_layout_tbl_cnt,
1914 x_layout_tbl => g_layout_tbl
1915 );
1916
1917 --
1918 -- Apply the "formatting rules" and format the results
1919 --
1920
1921 format_results (
1922 p_space_replace => p_space_replace,
1923 p_layout_tbl_cnt => g_layout_tbl_cnt,
1924 x_layout_tbl => g_layout_tbl,
1925 x_formatted_lines_tbl => x_formatted_name_tbl,
1926 x_formatted_lines_cnt => x_formatted_lines_cnt
1927 );
1928
1929 -- Build the single formatting string from the table
1930
1931 IF x_formatted_lines_cnt > 0 THEN
1932
1933 FOR i IN 1 .. x_formatted_lines_cnt
1934 LOOP
1935 IF i>1 THEN
1936 x_formatted_name := x_formatted_name || p_line_break || x_formatted_name_tbl(i);
1937 ELSE
1938 x_formatted_name := x_formatted_name_tbl(i);
1939 END IF;
1940 END LOOP;
1941
1942 END IF;
1943 EXCEPTION
1944
1945 WHEN fnd_api.g_exc_error THEN
1946 x_return_status := fnd_api.g_ret_sts_error;
1947 fnd_msg_pub.count_and_get (
1948 p_encoded => fnd_api.g_false,
1949 p_count => x_msg_count,
1950 p_data => x_msg_data
1951 );
1952
1953 WHEN OTHERS THEN
1954 x_return_status := fnd_api.g_ret_sts_unexp_error;
1955 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1956 fnd_msg_pub.add_exc_msg(
1957 g_pkg_name, l_api_name
1958 );
1959 END IF;
1960 fnd_msg_pub.count_and_get (
1961 p_encoded => fnd_api.g_false,
1962 p_count => x_msg_count,
1963 p_data => x_msg_data
1964 );
1965
1966 END format_name;
1967
1968 /*=========================================================================+
1969 |
1970 | PROCEDURE: format_name (signature #2)
1971 |
1972 | DESCRIPTION
1973 |
1974 | This procedure will format a person name. Parameters are supplied for
1975 | various name elements, therefore this procedure can be used to
1976 | format a person name from any data source.
1977 |
1981 |
1978 | SCOPE: Public
1979 |
1980 | ARGUMENTS: (see definition in specification)
1982 +=========================================================================*/
1983
1984 PROCEDURE format_name (
1985 -- input parameters
1986 p_style_code IN VARCHAR2,
1987 p_style_format_code IN VARCHAR2,
1988 p_line_break IN VARCHAR2,
1989 p_space_replace IN VARCHAR2,
1990 -- optional context parameters
1991 p_ref_language_code IN VARCHAR2,
1992 p_ref_territory_code IN VARCHAR2,
1993 -- person name components
1994 p_person_title IN VARCHAR2,
1998 p_person_name_suffix IN VARCHAR2,
1995 p_person_first_name IN VARCHAR2,
1996 p_person_middle_name IN VARCHAR2,
1997 p_person_last_name IN VARCHAR2,
1999 p_person_known_as IN VARCHAR2,
2000 p_first_name_phonetic IN VARCHAR2,
2001 p_middle_name_phonetic IN VARCHAR2,
2002 p_last_name_phonetic IN VARCHAR2,
2003 -- output parameters
2004 x_return_status OUT NOCOPY VARCHAR2,
2005 x_msg_count OUT NOCOPY NUMBER,
2006 x_msg_data OUT NOCOPY VARCHAR2,
2007 x_formatted_name OUT NOCOPY VARCHAR2,
2008 x_formatted_lines_cnt OUT NOCOPY NUMBER,
2009 x_formatted_name_tbl OUT NOCOPY string_tbl_type
2010 ) IS
2011 l_api_name VARCHAR2(30) := 'format_name(2)';
2012
2013 l_style_code hz_styles_b.style_code%TYPE;
2014 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
2015 l_territory_code fnd_territories.territory_code%TYPE;
2016 l_language_code fnd_languages.language_code%TYPE;
2017 l_variation_num NUMBER;
2018 BEGIN
2019 --
2020 -- Reset return status and messages
2021 --
2022
2023 x_return_status := fnd_api.g_ret_sts_success;
2024
2025 --
2026 -- Determine/Default the Context Information
2027 --
2028
2029 -- territory
2030
2031 IF p_ref_territory_code IS NOT NULL THEN
2032 l_territory_code := p_ref_territory_code;
2033 ELSE
2034 get_default_ref_territory (
2035 x_ref_territory_code => l_territory_code
2036 );
2037 END IF;
2038
2039 -- language
2040
2041 IF p_ref_language_code IS NOT NULL THEN
2042 l_language_code := p_ref_language_code;
2043 ELSE
2044 get_default_ref_language (
2045 x_ref_language_code => l_language_code
2046 );
2047 END IF;
2048
2049 --
2050 -- Figure out NOCOPY which Style Format to use
2051 --
2052
2053 IF p_style_format_code IS NOT NULL THEN
2054 -- bug 2656819 fix
2055 -- l_style_format_code := p_style_format_code;
2056 BEGIN
2057 select style_format_code into l_style_format_code
2058 from hz_style_formats_b
2059 where style_format_code = p_style_format_code;
2060 EXCEPTION
2061 WHEN NO_DATA_FOUND THEN
2062 l_style_format_code := null;
2063 END;
2064
2065 ELSE
2066 IF p_style_code IS NOT NULL THEN
2067 l_style_code := p_style_code;
2068 ELSE
2069 get_default_style (
2070 p_object_name => k_name_table_name,
2071 x_style_code => l_style_code
2072 );
2073
2074 END IF;
2075 IF l_style_code IS NOT NULL THEN
2076 get_style_format (
2077 p_style_code => l_style_code,
2078 p_territory_code => l_territory_code,
2079 p_language_code => l_language_code,
2080 x_return_status => x_return_status,
2081 x_msg_count => x_msg_count,
2082 x_msg_data => x_msg_data,
2083 x_style_format_code => l_style_format_code
2084 );
2085 END IF;
2086 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2087 RAISE fnd_api.g_exc_error;
2088 END IF;
2089 END IF;
2090
2091
2092 IF l_style_format_code IS NULL THEN
2093 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
2094 fnd_msg_pub.add;
2095 RAISE fnd_api.g_exc_error;
2096 END IF;
2097
2098 --
2099 -- Make the context information available to external functions
2100 -- (which are dynamically invoked from the formatting routines)
2101 -- for the duration of the invocation of this function.
2102 --
2103
2104 set_context (
2105 p_style_code => l_style_code,
2106 p_style_format_code => l_style_format_code,
2107 p_to_territory_code => l_territory_code,
2108 p_to_language_code => l_language_code,
2109 p_from_territory_code => l_territory_code,
2110 p_from_language_code => l_language_code,
2111 p_country_name_lang => l_language_code
2112 );
2113
2114 --
2115 -- Load a PL/SQL mapping table so that we can access
2116 -- the parameter values dynamically
2117 --
2118
2119 g_parm_tbl_cnt := 0;
2120 add_parm_table_row('PERSON_TITLE',
2121 p_person_title, g_parm_tbl, g_parm_tbl_cnt);
2122 add_parm_table_row('PERSON_FIRST_NAME',
2123 p_person_first_name, g_parm_tbl, g_parm_tbl_cnt);
2124 add_parm_table_row('PERSON_MIDDLE_NAME',
2125 p_person_middle_name, g_parm_tbl, g_parm_tbl_cnt);
2126 add_parm_table_row('PERSON_LAST_NAME',
2127 p_person_last_name, g_parm_tbl, g_parm_tbl_cnt);
2128 add_parm_table_row('PERSON_NAME_SUFFIX',
2129 p_person_name_suffix, g_parm_tbl, g_parm_tbl_cnt);
2130 add_parm_table_row('PERSON_KNOWN_AS',
2131 p_person_known_as, g_parm_tbl, g_parm_tbl_cnt);
2132 add_parm_table_row('PERSON_FIRST_NAME_PHONETIC',
2133 p_first_name_phonetic, g_parm_tbl, g_parm_tbl_cnt);
2134 add_parm_table_row('PERSON_LAST_NAME_PHONETIC',
2135 p_last_name_phonetic, g_parm_tbl, g_parm_tbl_cnt);
2136 add_parm_table_row('PERSON_MIDDLE_NAME_PHONETIC',
2137 p_middle_name_phonetic, g_parm_tbl, g_parm_tbl_cnt);
2138
2139 --
2140 -- Determine which format variation to use
2141 --
2142
2143 determine_variation (
2144 p_style_format_code => l_style_format_code,
2145 p_parm_tbl_cnt => g_parm_tbl_cnt,
2146 x_parm_tbl => g_parm_tbl,
2147 x_variation_num => l_variation_num
2148 );
2149
2150 --
2151 -- Load the format layout database table into
2155 load_internal_format_table(
2152 -- an internal pl/sql table.
2153 --
2154
2156 p_style_format_code => l_style_format_code,
2157 p_variation_num => l_variation_num,
2158 x_layout_tbl => g_layout_tbl,
2159 x_loaded_rows_cnt => g_layout_tbl_cnt
2160 );
2161
2162 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
2163 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
2164 -- fnd_message.set_token('STYLE_FORMAT',l_style_format_code);
2165 fnd_msg_pub.add;
2166 RAISE fnd_api.g_exc_error;
2167 END IF;
2168
2169 --
2170 -- Copy attribute values from parameter table to layout table
2171 --
2172
2173 copy_attribute_values (
2177 x_layout_tbl => g_layout_tbl
2174 p_parm_tbl_cnt => g_parm_tbl_cnt,
2175 x_parm_tbl => g_parm_tbl,
2176 p_layout_tbl_cnt => g_layout_tbl_cnt,
2178 );
2179
2180 --
2181 -- Apply the "formatting rules" and format the results
2182 --
2183
2184 format_results (
2185 p_space_replace => p_space_replace,
2186 p_layout_tbl_cnt => g_layout_tbl_cnt,
2187 x_layout_tbl => g_layout_tbl,
2188 x_formatted_lines_tbl => x_formatted_name_tbl,
2189 x_formatted_lines_cnt => x_formatted_lines_cnt
2190 );
2191
2192 -- Build the single formatting string from the table
2193
2194 IF x_formatted_lines_cnt > 0 THEN
2195 FOR i IN 1 .. x_formatted_lines_cnt
2196 LOOP
2197 IF i>1 THEN
2198 x_formatted_name := x_formatted_name || p_line_break || x_formatted_name_tbl(i);
2199 ELSE
2200 x_formatted_name := x_formatted_name_tbl(i);
2201 END IF;
2202 END LOOP;
2203 END IF;
2204
2205
2206 EXCEPTION
2207
2208 WHEN fnd_api.g_exc_error THEN
2209 x_return_status := fnd_api.g_ret_sts_error;
2210 fnd_msg_pub.count_and_get (
2211 p_encoded => fnd_api.g_false,
2212 p_count => x_msg_count,
2213 p_data => x_msg_data
2214 );
2215
2216 WHEN OTHERS THEN
2217 x_return_status := fnd_api.g_ret_sts_unexp_error;
2218 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2219 fnd_msg_pub.add_exc_msg(
2220 g_pkg_name, l_api_name
2221 );
2222 END IF;
2223 fnd_msg_pub.count_and_get (
2224 p_encoded => fnd_api.g_false,
2225 p_count => x_msg_count,
2226 p_data => x_msg_data
2227 );
2228
2229 END format_name;
2230
2231 /*=========================================================================+
2232 |
2233 | PROCEDURE: format_data
2234 |
2235 | DESCRIPTION
2236 |
2237 | A generic API that will format entities other than names and addresses
2238 | providing that the appropriate formatting metadata has been set up.
2239 |
2240 |
2241 | SCOPE: Public
2242 |
2243 | ARGUMENTS: (see definition in specification)
2244 |
2245 +=========================================================================*/
2246
2247 PROCEDURE format_data (
2248 -- input parameters
2249 p_object_code IN VARCHAR2,
2250 p_object_key_1 IN VARCHAR2,
2251 p_object_key_2 IN VARCHAR2,
2252 p_object_key_3 IN VARCHAR2,
2253 p_object_key_4 IN VARCHAR2,
2254 p_style_code IN VARCHAR2,
2255 p_style_format_code IN VARCHAR2,
2256 p_line_break IN VARCHAR2,
2257 p_space_replace IN VARCHAR2,
2258 -- optional context parameters
2259 p_ref_language_code IN VARCHAR2,
2260 p_ref_territory_code IN VARCHAR2,
2261 -- output parameters
2262 x_return_status OUT NOCOPY VARCHAR2,
2263 x_msg_count OUT NOCOPY NUMBER,
2264 x_msg_data OUT NOCOPY VARCHAR2,
2265 x_formatted_data OUT NOCOPY VARCHAR2,
2269 l_api_name VARCHAR2(30) := 'format_data';
2266 x_formatted_lines_cnt OUT NOCOPY NUMBER,
2267 x_formatted_data_tbl OUT NOCOPY string_tbl_type
2268 ) IS
2270
2271 l_style_code hz_styles_b.style_code%TYPE;
2272 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
2273 l_territory_code fnd_territories.territory_code%TYPE;
2274 l_language_code fnd_languages.language_code%TYPE;
2275 l_variation_num NUMBER;
2276 l_pk_column_count NUMBER := 0;
2277
2278 l_pk_name VARCHAR2(30);
2279 l_pk_value VARCHAR2(60);
2280
2281 l_key_value VARCHAR2(60);
2282 l_sql_string VARCHAR2(2000);
2283
2284 CURSOR c_primary_keys (p_table_name IN VARCHAR2) IS
2285 SELECT
2286 c.column_name,
2287 c.column_type,
2288 c.width
2289 FROM
2290 fnd_tables t,
2291 fnd_primary_keys pk,
2292 fnd_primary_key_columns pkc,
2293 fnd_columns c
2294 WHERE
2295 t.table_name = p_table_name
2296 AND pk.application_id = t.application_id
2297 AND pk.table_id = t.table_id
2298 AND pkc.application_id = pk.application_id
2299 AND pkc.table_id = pk.table_id
2300 AND pkc.primary_key_id = pk.primary_key_id
2301 AND c.application_id = pkc.application_id
2302 AND c.table_id = pkc.table_id
2303 AND c.column_id = pkc.column_id
2304 ORDER BY
2305 pkc.primary_key_sequence;
2306
2307 l_tab_count NUMBER;
2308
2309 BEGIN
2310
2311 --
2312 -- Reset return status and messages
2313 --
2314
2315 x_return_status := fnd_api.g_ret_sts_success;
2316
2317 --
2318 -- Validate the object code
2319 --
2320
2321 SELECT COUNT(TABLE_ID) INTO l_tab_count
2322 FROM FND_TABLES WHERE TABLE_NAME = p_object_code;
2323 IF l_tab_count = 0 THEN
2324 fnd_message.set_name('AR','HZ_INVALID_ENTITY_NAME');
2325 fnd_msg_pub.add;
2326 RAISE fnd_api.g_exc_error;
2327 END IF;
2328
2329 --
2330 -- Determine/Default the Context Information
2331 --
2332
2333 -- territory
2334
2335 IF p_ref_territory_code IS NOT NULL THEN
2336 l_territory_code := p_ref_territory_code;
2337 ELSE
2338 get_default_ref_territory (
2339 x_ref_territory_code => l_territory_code
2340 );
2341 END IF;
2342
2343 -- language
2344
2345 IF p_ref_language_code IS NOT NULL THEN
2346 l_language_code := p_ref_language_code;
2347 ELSE
2348 get_default_ref_language (
2349 x_ref_language_code => l_language_code
2350 );
2351 END IF;
2352
2353 --
2354 -- Figure out NOCOPY which Style Format to use
2355 --
2356
2357 IF p_style_format_code IS NOT NULL THEN
2358 -- bug 2656819 fix
2359 -- l_style_format_code := p_style_format_code;
2360 BEGIN
2361 select style_format_code into l_style_format_code
2362 from hz_style_formats_b
2363 where style_format_code = p_style_format_code;
2364 EXCEPTION
2365 WHEN NO_DATA_FOUND THEN
2366 l_style_format_code := null;
2367 END;
2368
2369 ELSE
2370 IF p_style_code IS NOT NULL THEN
2371 l_style_code := p_style_code;
2372 ELSE
2373 get_default_style (
2374 p_object_name => p_object_code,
2375 x_style_code => l_style_code
2376 );
2377
2378 END IF;
2379 IF l_style_code IS NOT NULL THEN
2380 get_style_format (
2381 p_style_code => l_style_code,
2382 p_territory_code => l_territory_code,
2383 p_language_code => l_language_code,
2384 x_return_status => x_return_status,
2388 );
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data,
2387 x_style_format_code => l_style_format_code
2389 END IF;
2390 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2391 RAISE fnd_api.g_exc_error;
2392 END IF;
2393 END IF;
2394
2395
2396 IF l_style_format_code IS NULL THEN
2397 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
2398 fnd_msg_pub.add;
2399 RAISE fnd_api.g_exc_error;
2400 END IF;
2401
2402 --
2403 -- Make the context information available to external functions
2404 -- (which are dynamically invoked from the formatting routines)
2405 -- for the duration of the invocation of this function.
2406 --
2407
2408 set_context (
2409 p_style_code => l_style_code,
2410 p_style_format_code => l_style_format_code,
2411 p_to_territory_code => l_territory_code,
2412 p_to_language_code => l_language_code,
2413 p_from_territory_code => l_territory_code,
2414 p_from_language_code => l_language_code,
2415 p_country_name_lang => l_language_code
2416 );
2417
2418 --
2419 -- Determine the primary key. In case of a multi-part primary key,
2420 -- construct a "where" clause.
2421 --
2422
2423 g_pk_tbl_cnt := 0;
2424 FOR l_primary_key IN c_primary_keys (p_object_code)
2425 LOOP
2426 l_pk_column_count := l_pk_column_count + 1;
2427
2428 IF l_pk_column_count = 1 THEN l_key_value := p_object_key_1;
2429 ELSIF l_pk_column_count = 2 THEN l_key_value := p_object_key_2;
2430 ELSIF l_pk_column_count = 3 THEN l_key_value := p_object_key_3;
2431 ELSIF l_pk_column_count = 4 THEN l_key_value := p_object_key_4;
2432 END IF;
2433
2434 add_parm_table_row(l_primary_key.column_name, l_key_value,
2435 g_pk_tbl, g_pk_tbl_cnt, l_primary_key.column_type);
2436
2437
2438 IF l_pk_column_count = 1 THEN
2439 l_pk_name := l_primary_key.column_name;
2440 l_pk_value := l_key_value;
2441 END IF;
2442
2443 END LOOP;
2444
2445 --
2446 -- Determine which format variation to use
2447 --
2448
2449 determine_variation (
2450 p_style_format_code => l_style_format_code,
2451 p_object_name => p_object_code,
2452 p_object_pk_name => l_pk_name,
2453 p_object_pk_value => l_pk_value,
2454 x_variation_num => l_variation_num
2455 );
2456
2457 --
2458 --
2459 -- Load the format layout database table into
2460 -- an internal pl/sql table.
2461 --
2462
2463 load_internal_format_table(
2464 p_style_format_code => l_style_format_code,
2465 p_variation_num => l_variation_num,
2466 x_layout_tbl => g_layout_tbl,
2467 x_loaded_rows_cnt => g_layout_tbl_cnt
2468 );
2469
2470 IF nvl(g_layout_tbl_cnt,0) = 0 THEN
2471 fnd_message.set_name('AR','HZ_FMT_NO_LAYOUT');
2472 fnd_msg_pub.add;
2473 RAISE fnd_api.g_exc_error;
2474 END IF;
2478 --
2475
2476 --
2477 -- Create a dynamic SQL query to get the name elements
2479
2480 create_sql_string(
2481 p_table_name => p_object_code,
2482 x_pk_tbl => g_pk_tbl,
2483 p_pk_tbl_cnt => g_pk_tbl_cnt,
2484 x_layout_tbl => g_layout_tbl,
2485 p_layout_tbl_cnt => g_layout_tbl_cnt,
2486 x_sql_string => l_sql_string
2487 );
2488
2489 --
2490 -- Run the dynamic SQL query, and populate the internal table
2491 -- with the queried data.
2492 --
2493
2494 execute_query(
2495 p_sql_string => l_sql_string,
2496 p_pk_tbl_cnt => g_pk_tbl_cnt,
2497 x_pk_tbl => g_pk_tbl,
2498 p_layout_tbl_cnt => g_layout_tbl_cnt,
2499 x_layout_tbl => g_layout_tbl
2500 );
2501
2502 --
2503 -- Apply the "formatting rules" and format the results
2504 --
2505
2506 format_results (
2507 p_space_replace => p_space_replace,
2508 p_layout_tbl_cnt => g_layout_tbl_cnt,
2509 x_layout_tbl => g_layout_tbl,
2510 x_formatted_lines_tbl => x_formatted_data_tbl,
2511 x_formatted_lines_cnt => x_formatted_lines_cnt
2512 );
2513
2514 -- Build the single formatting string from the table
2515
2516 IF x_formatted_lines_cnt > 0 THEN
2517
2518 FOR i IN 1 .. x_formatted_lines_cnt
2519 LOOP
2520 IF i>1 THEN
2521 x_formatted_data := x_formatted_data || p_line_break || x_formatted_data_tbl(i);
2522 ELSE
2523 x_formatted_data := x_formatted_data_tbl(i);
2524 END IF;
2525 END LOOP;
2526
2527 END IF;
2528
2529 EXCEPTION
2530
2531 WHEN fnd_api.g_exc_error THEN
2532 x_return_status := fnd_api.g_ret_sts_error;
2533 fnd_msg_pub.count_and_get (
2534 p_encoded => fnd_api.g_false,
2535 p_count => x_msg_count,
2536 p_data => x_msg_data
2537 );
2538
2539 WHEN OTHERS THEN
2540 x_return_status := fnd_api.g_ret_sts_unexp_error;
2541 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2542 fnd_msg_pub.add_exc_msg(
2543 g_pkg_name, l_api_name
2544 );
2545 END IF;
2546 fnd_msg_pub.count_and_get (
2547 p_encoded => fnd_api.g_false,
2548 p_count => x_msg_count,
2549 p_data => x_msg_data
2550 );
2551 END format_data;
2552
2553
2554 /********************** PUBLIC FUNCTION APIs ******************************/
2555
2556 /*=========================================================================+
2557 |
2558 | FUNCTION: format_address
2559 |
2560 | DESCRIPTION
2561 |
2562 | A function version of the format_address procedure that can be
2563 | used in a SQL statement. Returns back the address formatted into a
2564 | single line, with line breaks inserted.
2565 |
2566 | SCOPE: Public
2567 |
2568 +=========================================================================*/
2569
2570 FUNCTION format_address(
2571 p_location_id IN NUMBER,
2572 p_style_code IN VARCHAR2,
2573 p_style_format_code IN VARCHAR2,
2574 p_line_break IN VARCHAR2,
2575 p_space_replace IN VARCHAR2,
2576 p_to_language_code IN VARCHAR2,
2577 p_country_name_lang IN VARCHAR2,
2578 p_from_territory_code IN VARCHAR2
2579 ) RETURN VARCHAR2
2580 IS
2581 l_return_status VARCHAR2(1);
2582 l_msg_count NUMBER;
2583 -----Bug No.4145590
2584 l_msg_data VARCHAR2(2000);
2585 l_formatted_address VARCHAR2(360);
2586
2587 l_tbl_cnt NUMBER;
2588 l_tbl string_tbl_type;
2589 BEGIN
2590 -- fnd_msg_pub.initialize; /*Bug 3531172*/
2591 format_address (
2592 -- input parameters
2593 p_location_id => p_location_id,
2594 p_style_code => p_style_code,
2595 p_style_format_code => p_style_format_code,
2596 p_line_break => p_line_break,
2597 p_space_replace => p_space_replace,
2598 -- optional context
2599 p_to_language_code => p_to_language_code,
2600 p_country_name_lang => p_country_name_lang,
2601 p_from_territory_code => p_from_territory_code,
2602 -- output parameters
2603 x_return_status => l_return_status,
2604 x_msg_count => l_msg_count,
2605 x_msg_data => l_msg_data,
2606 x_formatted_address => l_formatted_address,
2607 x_formatted_lines_cnt => l_tbl_cnt,
2608 x_formatted_address_tbl => l_tbl
2609 );
2610 -----Bug No.4145590
2611 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2612 return NULL;
2613 END IF;
2614
2615 RETURN l_formatted_address;
2616
2617 EXCEPTION
2618 -----Bug No.4145590
2619 WHEN OTHERS THEN
2620 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
2621 fnd_message.set_token('ERROR',SQLERRM);
2622 fnd_msg_pub.add;
2623 return NULL;
2624
2625 END format_address;
2626
2627
2628 /*=========================================================================+
2629 |
2630 | FUNCTION: format_address_lov
2631 |
2632 | DESCRIPTION
2633 |
2634 | A function version of the format_address procedure that can be
2635 | is meant to be used for Party LOVs. This version accepts the
2636 | individual address components and is useful since these data are
2637 | denormalized onto HZ_PARTIES.
2638 |
2639 | SCOPE: For TCA internal use only - signature can change
2640 | without warning.
2641 |
2642 | ARGUMENTS:
2643 |
2644 +=========================================================================*/
2645
2649 p_address_line_3 IN VARCHAR2 DEFAULT NULL,
2646 FUNCTION format_address_lov(
2647 p_address_line_1 IN VARCHAR2 DEFAULT NULL,
2648 p_address_line_2 IN VARCHAR2 DEFAULT NULL,
2650 p_address_line_4 IN VARCHAR2 DEFAULT NULL,
2651 p_city IN VARCHAR2 DEFAULT NULL,
2652 p_postal_code IN VARCHAR2 DEFAULT NULL,
2653 p_state IN VARCHAR2 DEFAULT NULL,
2654 p_province IN VARCHAR2 DEFAULT NULL,
2655 p_county IN VARCHAR2 DEFAULT NULL,
2656 p_country IN VARCHAR2 DEFAULT NULL,
2657 p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL
2658 ) RETURN VARCHAR2
2659 IS
2660 l_return_status VARCHAR2(1);
2661 l_msg_count NUMBER;
2662 -----Bug No.4145590
2663 l_msg_data VARCHAR2(2000);
2664 l_formatted_address VARCHAR2(360);
2665
2666 l_tbl_cnt NUMBER;
2667 l_tbl string_tbl_type;
2668 BEGIN
2669
2670 format_address (
2671 p_style_code => 'POSTAL_ADDR',
2672 p_line_break => ', ',
2673 p_space_replace => ' ',
2674 -- optional context parameters
2675 -- p_to_language_code IN VARCHAR2 DEFAULT NULL,
2676 -- p_country_name_lang IN VARCHAR2 DEFAULT NULL,
2677 -- p_from_territory_code IN VARCHAR2 DEFAULT NULL,
2678 -- address components
2679 p_address_line_1 => p_address_line_1,
2680 p_address_line_2 => p_address_line_2,
2681 p_address_line_3 => p_address_line_3,
2682 p_address_line_4 => p_address_line_4,
2683 p_city => p_city,
2684 p_postal_code => p_postal_code,
2685 p_state => p_state,
2686 p_province => p_province,
2687 p_county => p_county,
2688 p_country => p_country,
2689 p_address_lines_phonetic => p_address_lines_phonetic,
2690 -- output parameters
2691 x_return_status => l_return_status,
2692 x_msg_count => l_msg_count,
2693 x_msg_data => l_msg_data,
2694 x_formatted_address => l_formatted_address,
2695 x_formatted_lines_cnt => l_tbl_cnt,
2696 x_formatted_address_tbl => l_tbl
2697 );
2698 -----Bug No.4145590
2699 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2700 return NULL;
2701 END IF;
2702 RETURN l_formatted_address;
2703
2704 EXCEPTION
2705 -----Bug No.4145590
2706 WHEN OTHERS THEN
2707 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
2708 fnd_message.set_token('ERROR',SQLERRM);
2709 fnd_msg_pub.add;
2710 return NULL;
2711
2712 END format_address_lov;
2713
2714
2715 /*=========================================================================+
2716 |
2717 | FUNCTION: format_name
2718 |
2719 | DESCRIPTION
2720 |
2721 | A function version of the format_name procedure that can be
2722 | used in a SQL statement. Returns back the name formatted into a
2723 | single line, with line breaks inserted.
2724 |
2725 | SCOPE: Public
2726 |
2727 | ARGUMENTS:
2728 |
2729 +=========================================================================*/
2730
2731 FUNCTION format_name (
2732 p_party_id IN NUMBER,
2733 p_style_code IN VARCHAR2,
2734 p_style_format_code IN VARCHAR2,
2735 p_line_break IN VARCHAR2,
2736 p_space_replace IN VARCHAR2,
2737 p_ref_language_code IN VARCHAR2,
2738 p_ref_territory_code IN VARCHAR2
2739 ) RETURN VARCHAR2
2740 IS
2741 l_return_status VARCHAR2(1);
2742 l_msg_count NUMBER;
2743 l_msg_data VARCHAR2(2000);
2744 l_formatted_name VARCHAR2(360);
2745 l_formatted_lines_cnt NUMBER;
2746 l_formatted_name_tbl string_tbl_type;
2747
2748 BEGIN
2749 -- fnd_msg_pub.initialize; /*Bug 3531172*/
2750 format_name (
2751 -- input parameters
2752 p_party_id => p_party_id,
2753 p_style_code => p_style_code,
2754 p_style_format_code => p_style_format_code,
2755 p_line_break => p_line_break,
2756 p_space_replace => p_space_replace,
2757 -- optional context
2758 p_ref_language_code => p_ref_language_code,
2759 p_ref_territory_code => p_ref_territory_code,
2760 -- output parameters
2761 x_return_status => l_return_status,
2762 x_msg_count => l_msg_count,
2763 x_msg_data => l_msg_data,
2764 x_formatted_name => l_formatted_name,
2765 x_formatted_lines_cnt => l_formatted_lines_cnt,
2766 x_formatted_name_tbl => l_formatted_name_tbl
2767 );
2768
2769 -----Bug No.4145590
2770 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2771 return NULL;
2772 END IF;
2773 RETURN l_formatted_name;
2774
2775 EXCEPTION
2776 -----Bug No.4145590
2777 WHEN OTHERS THEN
2778 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
2779 fnd_message.set_token('ERROR',SQLERRM);
2780 fnd_msg_pub.add;
2781 return NULL;
2782
2783 END format_name;
2784
2785 /******************** PUBLIC UTILITY PROCEDURES ***************************/
2786
2787
2788 /*=========================================================================+
2789 |
2790 | PROCEDURE: get_context
2791 |
2792 | DESCRIPTION
2793 |
2794 | Returns context information to caller.
2795 | See SPECIFICATION for usage details.
2796 |
2797 | SCOPE: Public (limited)
2798 |
2799 +=========================================================================*/
2800
2801 PROCEDURE get_context (
2802 x_context OUT NOCOPY context_rec_type
2803 ) IS
2804 BEGIN
2805 x_context := g_context;
2806 END;
2807
2811 |
2808 /*=========================================================================+
2809 |
2810 | PROCEDURE: get_style_format
2812 | DESCRIPTION
2813 |
2814 | Gets the appropriate localized Style Format Code for a given Style,
2815 | based on Territory or Language, or a combination of both.
2816 |
2817 | Styles (HZ_STYLES) can have multiple Style Formats (HZ_STYLE_FORMATS)
2818 | depending on Territory and Location ("locales"). The locales for
2819 | which a given Style Format is applicable is stored in table
2820 | HZ_STYLE_FMT_LOCALES.
2821 |
2822 | The following sequence occurs to find the matching Style Format:
2823 |
2824 | 1. Check for a match on BOTH Territory and Language Code
2825 | 2. If not found (or n/a) check for a match on Territory.
2826 | 3. If not found (or n/a) check for a match on Language.
2827 | 4. If not found then retrieve the DEFAULT Style Format
2828 | for the Style.
2829 |
2830 | For example, if Style Code 'POSTAL_ADDR' is passed, as well as
2831 | Territory of 'FR' (France), then this procedure should find the
2832 | matching Style Format of 'POSTAL_ADDR_N_EUR' (The Style Format
2833 | Northern Europe).
2834 |
2835 | SCOPE: Public
2836 |
2837 | ARGUMENTS: (IN)
2838 | p_style_code Style for which to find
2839 | the localized Style Format.
2840 | p_territory_code Territory for which you wish
2841 | to determine Style Format.
2842 | p_language_code Language for which you wish
2843 | to determine Style Format.
2844 |
2845 | (OUT)
2846 | x_return_status API Standard Return Status
2847 | x_msg_count API Standard Message Count
2848 | x_msg_data API Standard Message Data
2849 | x_style_format_code Style Format Code that was found.
2850 |
2851 +=========================================================================*/
2852
2853 PROCEDURE get_style_format (
2854 p_style_code IN VARCHAR2,
2855 p_territory_code IN fnd_territories.territory_code%TYPE,
2856 p_language_code IN fnd_languages.language_code%TYPE,
2857 x_return_status OUT NOCOPY VARCHAR2,
2858 x_msg_count OUT NOCOPY VARCHAR2,
2859 x_msg_data OUT NOCOPY VARCHAR2,
2860 x_style_format_code OUT NOCOPY VARCHAR2
2861 ) IS
2862
2863 l_api_name VARCHAR2(30) := 'get_style_format';
2864
2865 CURSOR c_style_formats (
2866 p_style_code IN VARCHAR2,
2867 p_territory_code IN VARCHAR2,
2868 p_language_code IN VARCHAR2
2869 ) IS
2870 SELECT
2871 hsf.style_format_code
2872 FROM
2873 hz_style_fmt_locales hsfl,
2874 hz_style_formats_b hsf
2875 WHERE
2876 nvl(hsfl.territory_code,'X') = nvl(p_territory_code,'X')
2877 AND nvl(hsfl.language_code,'X') = nvl(p_language_code,'X')
2878 AND hsfl.style_format_code = hsf.style_format_code
2879 AND hsf.style_code = p_style_code
2880 AND (SYSDATE BETWEEN hsfl.start_date_active
2881 AND NVL(hsfl.end_date_active, to_date('12/31/4712','MM/DD/YYYY')));
2882
2883
2884
2885 CURSOR c_def_style_format (
2886 p_style_code IN VARCHAR2
2887 ) IS
2888 SELECT
2889 hsf.style_format_code
2890 FROM
2891 hz_style_formats_b hsf
2892 WHERE
2893 hsf.default_flag = 'Y'
2894 AND hsf.style_code = p_style_code;
2895
2896 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
2897
2898 BEGIN
2899
2900 --
2901 -- Reset return status and messages
2902 --
2903
2904 x_return_status := fnd_api.g_ret_sts_success;
2905
2906
2907 --
2908 -- Look for a match on both
2909 --
2910
2911 IF (p_territory_code IS NOT NULL) AND (p_language_code IS NOT NULL) THEN
2912 OPEN c_style_formats(
2913 p_style_code => p_style_code,
2914 p_territory_code => p_territory_code,
2915 p_language_code => p_language_code
2916 );
2917 FETCH c_style_formats INTO l_style_format_code;
2918 IF c_style_formats%NOTFOUND THEN
2919 CLOSE c_style_formats;
2920 ELSE
2921 CLOSE c_style_formats;
2922 x_style_format_code := l_style_format_code;
2923 RETURN; -- match on both, exit procedure
2924 END IF;
2925
2926 END IF;
2927
2928 --
2929 -- Look for a match on territory (and ensure language is NULL)
2930 --
2931
2932 IF (p_territory_code IS NOT NULL) THEN
2933 OPEN c_style_formats(
2934 p_style_code => p_style_code,
2935 p_territory_code => p_territory_code,
2936 p_language_code => NULL
2937 );
2938 FETCH c_style_formats INTO l_style_format_code;
2939 IF c_style_formats%NOTFOUND THEN
2940 CLOSE c_style_formats;
2941 ELSE
2942 CLOSE c_style_formats;
2943 x_style_format_code := l_style_format_code;
2944 RETURN; -- match on TERRITORY exit procedure
2945 END IF;
2946 END IF;
2947
2948 --
2949 -- Look for a match on language (and ensure territory is NULL)
2950 --
2951
2952 IF (p_language_code IS NOT NULL) THEN
2953 OPEN c_style_formats(
2954 p_style_code => p_style_code,
2955 p_territory_code => NULL,
2956 p_language_code => p_language_code
2957 );
2958 FETCH c_style_formats INTO l_style_format_code;
2959 IF c_style_formats%NOTFOUND THEN
2960 CLOSE c_style_formats;
2961 ELSE
2962 CLOSE c_style_formats;
2963 x_style_format_code := l_style_format_code;
2964 RETURN; -- match on LANGUAGE exit procedure
2965 END IF;
2966 END IF;
2967
2971
2968 --
2969 -- Obtain the default
2970 --
2972 OPEN c_def_style_format(
2973 p_style_code => p_style_code
2974 );
2975
2976 FETCH c_def_style_format INTO l_style_format_code;
2977 IF c_def_style_format%NOTFOUND THEN
2978 -- Error condition... could not determine Style Format whatsoever.
2979 x_return_status := fnd_api.g_ret_sts_error;
2980 fnd_message.set_name('AR','HZ_FMT_CANNOT_GET_FORMAT');
2981 fnd_msg_pub.add;
2982 END IF;
2983 CLOSE c_def_style_format;
2984
2985 x_style_format_code := l_style_format_code;
2986
2987 --
2988 -- Populate return message parameters with message information
2989 --
2990
2991 fnd_msg_pub.count_and_get (
2992 p_encoded => fnd_api.g_false,
2993 p_count => x_msg_count,
2994 p_data => x_msg_data
2995 );
2996
2997 EXCEPTION
2998
2999 WHEN OTHERS THEN
3000 x_return_status := fnd_api.g_ret_sts_unexp_error;
3001 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3002 fnd_msg_pub.add_exc_msg(
3003 g_pkg_name, l_api_name
3004 );
3005 END IF;
3006 fnd_msg_pub.count_and_get (
3007 p_encoded => fnd_api.g_false,
3008 p_count => x_msg_count,
3009 p_data => x_msg_data
3010 );
3011
3012 END get_style_format;
3013
3014 /***************** PUBLIC (SEEDED) CALLOUT FUNCTIONS **********************/
3015 /* */
3016 /* The following functions are used by the seeded "selection condition" */
3017 /* definitions (for selecting a layout variation) and by the attribute */
3018 /* transformation functions. They are available for use in custom */
3019 /* layouts. */
3020 /* */
3021 /**************************************************************************/
3022
3023 /*=========================================================================+
3024 |
3025 | FUNCTION: use_neu_country_code
3026 |
3027 | DESCRIPTION
3028 |
3029 | This function determines if the "from country" (context information)
3030 | as well as the "to country" both use the Northern European
3031 | addressing format. This determines which of the Northern European
3032 | layout variations to use.
3033 |
3034 | SCOPE: Public (limited)
3035 | Intended to be used as a function for the "selection condition"
3036 | of a layout variation.
3037 |
3038 | ARGUMENTS: (IN)
3039 | p_territory_code Territory Code of the address ("to").
3040 +=========================================================================*/
3041
3042 FUNCTION use_neu_country_format (
3043 p_territory_code IN VARCHAR2
3044 ) RETURN VARCHAR2 -- boolean 'Y' or 'N' (cannot use BOOLEAN for SQL functions)
3045 IS
3046 l_context context_rec_type;
3047 BEGIN
3048
3049 --
3050 -- Get the current context information
3051 --
3052
3053 get_context(
3054 x_context => l_context
3055 );
3056
3057 --
3058 -- If the "from" country is the same as the "to" country, then
3059 -- don't need to check any further... same format
3060 --
3061
3062 IF l_context.from_territory_code = p_territory_code THEN
3063 RETURN 'Y';
3064 END IF;
3065
3066 --
3067 -- Determine the format that the "from" country uses.
3068 --
3069
3070 DECLARE
3071 l_style_format_code hz_style_formats_b.style_format_code%TYPE;
3072 l_return_status VARCHAR2(1);
3073 l_msg_count NUMBER;
3074 l_msg_data VARCHAR2(2000);
3075
3076 BEGIN
3077 get_style_format (
3078 p_style_code => l_context.style_code,
3079 p_territory_code => l_context.from_territory_code,
3080 p_language_code => l_context.from_language_code,
3081 x_return_status => l_return_status,
3082 x_msg_count => l_msg_count,
3083 x_msg_data => l_msg_data,
3084 x_style_format_code => l_style_format_code
3085 );
3086
3087 IF l_style_format_code = l_context.style_format_code THEN
3088 RETURN 'Y';
3089 END IF;
3090 END;
3091
3092 RETURN 'N';
3093
3094 EXCEPTION
3095 WHEN OTHERS THEN
3096 RETURN 'N'; -- Do not wish to fail transaction. Pick a "safe" answer.
3097 END;
3098
3099 /*=========================================================================+
3100 |
3101 | FUNCTION: get_neu_country_code
3102 |
3103 | DESCRIPTION
3104 |
3105 | This function will translate a Territory Code for Northern Europe
3106 | into the code that is used to preceed the postal code in a
3107 | formatted address. The code often differs from the ISO code.
3108 |
3109 | For example, France (FR) uses an "F" in front of the postal code
3110 | when using the Northern European address format.
3111 | Belgium (BE) uses "B", Germany (DE) uses "D", etc.
3112 |
3113 | SCOPE: Public (limited)
3114 | Intended to be used as a "transformation function" of an
3115 | attribute.
3116 |
3117 | ARGUMENTS: (IN)
3118 | p_territory_code Territory Code of the address ("to").
3119 +=========================================================================*/
3120
3121 FUNCTION get_neu_country_code (
3122 p_territory_code IN VARCHAR2
3123 ) RETURN VARCHAR2
3124 IS
3125 l_neu_code VARCHAR2(4);
3126 BEGIN
3127
3128 -- Phase II of this project will remove the following hardcoding.
3129 -- Timeframes did not permit this from being accomodated in Phase I. :-(
3130
3131 IF p_territory_code = 'AT' THEN l_neu_code := 'A';
3135 ELSIF p_territory_code = 'FO' THEN l_neu_code := 'FR';
3132 ELSIF p_territory_code = 'BE' THEN l_neu_code := 'B';
3133 ELSIF p_territory_code = 'DE' THEN l_neu_code := 'D';
3134 ELSIF p_territory_code = 'FI' THEN l_neu_code := 'FIN';
3136 ELSIF p_territory_code = 'FR' THEN l_neu_code := 'F';
3137 ELSIF p_territory_code = 'IT' THEN l_neu_code := 'I';
3138 ELSIF p_territory_code = 'LI' THEN l_neu_code := 'FL';
3139 ELSIF p_territory_code = 'LU' THEN l_neu_code := 'L';
3140 ELSIF p_territory_code = 'NO' THEN l_neu_code := 'N';
3141 ELSIF p_territory_code = 'PT' THEN l_neu_code := 'P';
3142 ELSIF p_territory_code = 'RO' THEN l_neu_code := 'R';
3143 ELSIF p_territory_code = 'SE' THEN l_neu_code := 'S';
3144 ELSIF p_territory_code = 'VA' THEN l_neu_code := 'I';
3145 ELSE
3146 l_neu_code := p_territory_code;
3147 END IF;
3148
3149 RETURN l_neu_code;
3150 END get_neu_country_code;
3151
3152
3153 /*=========================================================================+
3154 |
3155 | FUNCTION: get_tl_territory_name
3156 |
3157 | DESCRIPTION
3158 |
3159 | This function will translate a Territory Code (e.g. 'MX') into its
3160 | name (e.g. 'Mexico').
3161 |
3162 | The behavior of this function depends on the context in which
3163 | it was invoked.
3164 |
3165 | a) The Territory Name is retrieved in the language identified
3166 | by context attribute "country_name_lang".
3167 |
3168 | b) If the Territory Code is the same as the context attribute
3169 | "from_territory_code", then NULL will be returned.
3170 | This controls the suppression of the country name if
3171 | the "from" and "to" countries are the same.
3172 |
3173 |
3174 | SCOPE: Public (limited)
3175 | Intended to be used as a "transformation function" of an
3176 | attribute.
3177 |
3178 | ARGUMENTS: (IN)
3179 | p_territory_code Territory Code of the address ("to").
3180 +=========================================================================*/
3181
3182 FUNCTION get_tl_territory_name (
3183 p_territory_code IN VARCHAR2
3184 ) RETURN VARCHAR2
3185 IS
3186 l_territory_name fnd_territories_vl.territory_short_name%TYPE;
3187 l_language_code fnd_languages.language_code%TYPE;
3188
3189 CURSOR c_territory (
3190 p_territory_code IN VARCHAR2,
3191 p_language IN VARCHAR2
3192 ) IS
3193 SELECT territory_short_name
3194 FROM fnd_territories_tl
3195 WHERE territory_code = p_territory_code
3196 AND language=p_language;
3197
3198
3199 l_context context_rec_type;
3200
3201 BEGIN
3202
3203 --
3204 -- Get context information so we know what language
3205 -- to translate the country name into
3206 --
3207
3208 get_context(
3209 x_context => l_context
3210 );
3211
3212 --
3213 -- If the address territory is the same as the reference territory,
3214 -- then suppress the territory name and return null.
3215 --
3216
3217 IF l_context.from_territory_code = p_territory_code THEN
3218 RETURN NULL;
3219 END IF;
3220
3221 OPEN c_territory(p_territory_code, l_context.country_name_lang);
3222 FETCH c_territory INTO l_territory_name;
3223 IF c_territory%NOTFOUND THEN
3224 CLOSE c_territory;
3225 RETURN p_territory_code;
3226 END IF;
3227 CLOSE c_territory;
3228
3229 RETURN l_territory_name;
3230
3231 EXCEPTION
3232 WHEN OTHERS THEN
3233 RETURN p_territory_code;
3234
3235 END get_tl_territory_name;
3236
3237
3238 /****************** PRIVATE PROCEDURES/FUNCTIONS **************************/
3239
3240
3241 /*=========================================================================+
3242 |
3243 | PROCEDURE: get_default_style
3244 |
3245 | DESCRIPTION
3246 |
3247 | This is a utility procedure that tries to determine the default
3248 | style (if none is passed from the caller).
3249 |
3250 | For names and addresses, a profile options are checked.
3251 | If profile options are not set (or formatting data other than
3252 | names and addresses) then HZ_STYLES_B is examined to see if there
3253 | is one and only one style defined for the object, and that will
3254 | be selected as the default.
3255 |
3256 | SCOPE: Private
3257 |
3258 | ARGUMENTS: (IN)
3259 | p_object_name The object for which you want to default style.
3260 |
3261 | (OUT)
3262 | x_style_code Default Style. NULL if cannot determine.
3263 |
3264 +=========================================================================*/
3265
3266 PROCEDURE get_default_style (
3267 p_object_name IN hz_styles_b.database_object_name%TYPE,
3268 x_style_code OUT NOCOPY hz_styles_b.style_code%TYPE
3269 ) IS
3270 l_style_code hz_styles_b.style_code%TYPE;
3271
3272 CURSOR c_styles(p_object_name IN VARCHAR2)
3273 IS SELECT rownum, style_code FROM hz_styles_b WHERE database_object_name = p_object_name;
3274
3275 BEGIN
3276
3277
3278 IF p_object_name = k_name_table_name THEN
3279 /* Fix perf bug 3669930, 4220460, use cached profile option value
3280 -- check profile option
3281 fnd_profile.get(
3282 name => k_profile_def_name_style,
3283 val => l_style_code
3284 );
3285 */
3286 l_style_code := g_profile_def_name_style;
3287 ELSIF p_object_name = k_addr_table_name THEN
3288 /* Fix perf bug 3669930, 4220460, use cached profile option value
3289 -- check profile option
3290 fnd_profile.get(
3291 name => k_profile_def_addr_style,
3292 val => l_style_code
3293 );
3294 */
3295 l_style_code := g_profile_def_addr_style;
3296 END IF;
3297
3301 -- Our only resort now is to examine if there is only one style.
3298 IF l_style_code IS NULL THEN
3299
3300 -- Could not find the default style via profile options.
3302
3303 BEGIN
3304 SELECT style_code
3305 INTO l_style_code
3306 FROM hz_styles_b
3307 WHERE database_object_name = p_object_name;
3308 EXCEPTION
3309 WHEN NO_DATA_FOUND THEN
3310 NULL; -- cannot default style code
3311 WHEN TOO_MANY_ROWS THEN
3312 NULL; -- cannot default style code
3313 END;
3314 END IF;
3315
3316 x_style_code := l_style_code;
3317
3318 END get_default_style;
3319
3320 /*=========================================================================+
3321 |
3322 | PROCEDURE: get_default_ref_territory
3323 |
3324 | DESCRIPTION
3325 |
3326 | This is a utility procedure that determine's the user's reference
3327 | territory for name and address format purposes. This is required
3328 | in case the caller to the name/address formatting routines do
3329 | not pass in the territory where they are.
3330 |
3331 | The reference territory is obtained as follows:
3332 |
3333 | 1. Check for Profile Option HZ_REF_LANG
3334 | 2. If null, obtain the current session NLS territory setting
3335 |
3336 |
3337 | SCOPE: Private
3338 |
3339 | ARGUMENTS: (OUT)
3340 | x_ref_territory_code Default Reference Territory.
3341 |
3342 +=========================================================================*/
3343
3344 PROCEDURE get_default_ref_territory (
3345 x_ref_territory_code OUT NOCOPY fnd_territories.territory_code%TYPE
3346 ) IS
3347 l_territory_code fnd_territories.territory_code%TYPE;
3348 l_nls_territory varchar(30);
3349 BEGIN
3350
3351 ---Bug No. 5110275. First check HZ_REF_TERRITORY profile option and then ICX_TERRITORY profile option
3352
3353 -- Bug No. 7139602. The profile value must be read at runtime. Cached value should not be used.
3354 --l_territory_code := g_profile_ref_territory;
3355 l_territory_code := FND_PROFILE.VALUE('HZ_REF_TERRITORY');
3356
3357 -- Debug HZ_REF_TERRITORY
3358
3359 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3360 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_ref_territory, ' ||
3361 'Profile option HZ_REF_TERRITORY = ' || l_territory_code,
3362 p_prefix=>'',
3363 p_msg_level=>fnd_log.level_procedure);
3364 END IF;
3365
3366 --Now checking ICX_TERRITORY profile option
3367
3368 IF l_territory_code IS NULL THEN
3372
3369 -- Bug No. 7139602. The profile value must be read at runtime. Cached value should not be used.
3370 -- l_nls_territory := g_icx_territory;
3371 l_nls_territory := FND_PROFILE.VALUE('ICX_TERRITORY');
3373 -- Debug ICX_TERRITORY
3374 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3375 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_ref_territory, ' ||
3376 'Profile option ICX_TERRITORY = ' || l_nls_territory,
3377 p_prefix=>'',
3378 p_msg_level=>fnd_log.level_procedure);
3379 END IF;
3380
3381 if l_nls_territory is not null then
3382 select territory_code into l_territory_code
3383 from fnd_territories
3384 where nls_territory = l_nls_territory
3385 and OBSOLETE_FLAG = 'N'
3386 and rownum = 1;
3387 end if;
3388
3389 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3390 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_ref_territory, ' ||
3391 'TERRITORY_CODE = ' || l_territory_code,
3392 p_prefix=>'',
3393 p_msg_level=>fnd_log.level_procedure);
3394 END IF;
3395
3396
3397 END IF;
3398
3399 -- if not set, get current session NLS setting
3400
3401 IF l_territory_code IS NULL THEN
3402
3403 BEGIN
3404 if g_terr_code_exist = 0 then
3405 g_terr_code_exist :=1;
3406 SELECT fnd.territory_code
3407 INTO g_territory_code
3408 FROM nls_session_parameters nls,
3409 fnd_territories fnd
3410 WHERE nls.parameter = 'NLS_TERRITORY'
3411 AND fnd.nls_territory = nls.value;
3412 end if;
3413 if g_territory_code is not null then
3414 l_territory_code := g_territory_code;
3415 end if;
3416 EXCEPTION
3417 WHEN NO_DATA_FOUND THEN
3418 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_ref_territory,'||
3419 'NO_DATA_FOUND Error from query',
3423 END IF;
3420 p_prefix=>'',
3421 p_msg_level=>fnd_log.level_procedure);
3422 END;
3424
3425 --
3426 -- Set up return parameter
3427 --
3428
3429 x_ref_territory_code := l_territory_code;
3430
3431 END get_default_ref_territory;
3432 /*=========================================================================+
3433 |
3434 | PROCEDURE: get_default_eloc_ref_territory
3435 |
3436 | DESCRIPTION
3437 |
3438 | This is a utility procedure that determine's the user's reference
3439 | territory for name and address format purposes. This is required
3440 | in case the caller to the name/address formatting routines do
3441 | not pass in the territory where they are.
3442 |
3443 | The reference territory is obtained as follows:
3444 |
3445 | 1. Check for Profile Option HZ_REF_LANG
3446 | 2. If null, obtain the current session NLS territory setting
3447 |
3448 |
3449 | SCOPE: Private
3450 |
3451 | ARGUMENTS: (OUT)
3452 | x_ref_territory_code Default Reference Territory.
3453 |
3454 +=========================================================================*/
3455
3456 PROCEDURE get_default_eloc_ref_territory (
3457 x_ref_territory_code OUT NOCOPY fnd_territories.territory_code%TYPE
3458 ) IS
3459 l_territory_code fnd_territories.territory_code%TYPE;
3460 l_nls_territory varchar(30);
3461 BEGIN
3462
3463 ----Bug No. 5110275. First check for HZ_REF_TERRITORY and then for ICX_TERRITORY PROFILE OPTION
3464
3465 -- Bug No. 7139602. The profile value must be read at runtime. Cached value should not be used.
3466 --l_territory_code := g_profile_ref_territory;
3467 l_territory_code := FND_PROFILE.VALUE('HZ_REF_TERRITORY');
3468
3469
3470 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3471
3472 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_eloc_ref_territory, ' ||
3473 'Profile option HZ_REF_TERRITORY = ' ||l_territory_code,
3474 p_prefix=>'',
3475 p_msg_level=>fnd_log.level_procedure);
3476 END IF;
3477
3478 IF l_territory_code IS NULL THEN
3479 -- Bug No. 7139602. The profile value must be read at runtime. Cached value should not be used.
3480 -- l_nls_territory := g_icx_territory;
3481 l_nls_territory := FND_PROFILE.VALUE('ICX_TERRITORY');
3482
3483 -- Debug ICX_TERRITORY value
3484 --
3485 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3486
3487 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_eloc_ref_territory, ' ||
3488 'Profile option ICX_TERRITORY = ' || l_nls_territory,
3489 p_prefix=>'',
3490 p_msg_level=>fnd_log.level_procedure);
3491 END IF;
3492
3493
3494 if l_nls_territory is not null then
3495 select territory_code into l_territory_code
3496 from fnd_territories
3497 where nls_territory = l_nls_territory
3498 and OBSOLETE_FLAG = 'N'
3499 and rownum = 1;
3500 end if;
3501 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3502 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_eloc_ref_territory, ' ||
3503 'TERRITORY_CODE = ' || l_territory_code,
3504 p_prefix=>'',
3505 p_msg_level=>fnd_log.level_procedure);
3506 END IF;
3507
3508
3509
3510 END IF;
3511
3512 -- if not set, get current session NLS setting
3513
3514 IF l_territory_code IS NULL THEN
3515
3516 BEGIN
3517 if g_terr_code_exist = 0 then
3518 g_terr_code_exist :=1;
3519 SELECT fnd.territory_code
3520 INTO g_territory_code
3521 FROM nls_session_parameters nls,
3522 fnd_territories fnd
3523 WHERE nls.parameter = 'NLS_TERRITORY'
3524 AND fnd.nls_territory = nls.value;
3525 end if;
3526 if g_territory_code is not null then
3527 l_territory_code := g_territory_code;
3528 end if;
3529 EXCEPTION
3530 WHEN NO_DATA_FOUND THEN
3531 hz_utility_v2pub.debug(p_message=>'HZ_FORMAT_PUB.get_default_eloc_ref_territory,'||
3532 'NO_DATA_FOUND Error from query',
3533 p_prefix=>'',
3537
3534 p_msg_level=>fnd_log.level_procedure);
3535 END;
3536 END IF;
3538 x_ref_territory_code := l_territory_code;
3539
3540 END get_default_eloc_ref_territory;
3541 /*=========================================================================+
3542 |
3543 | PROCEDURE: get_default_ref_language
3544 |
3545 | DESCRIPTION
3546 |
3547 | This is a utility procedure that determine's the user's reference
3548 | language for name and address format purposes. This is required
3549 | in case the caller to the name/address formatting routines do
3550 | not pass in the language where they are.
3551 |
3552 | The reference territory is obtained as follows:
3553 |
3554 | 1. Check for Profile Option HZ_REF_LANG
3555 | 2. If null, obtain the current session NLS territory setting
3556 |
3557 |
3558 | SCOPE: Private
3559 |
3560 | ARGUMENTS: (OUT)
3561 | x_ref_language_code Default Reference Language.
3562 |
3563 +=========================================================================*/
3564
3565 PROCEDURE get_default_ref_language (
3566 x_ref_language_code OUT NOCOPY fnd_languages.language_code%TYPE
3567 ) IS
3568 l_language_code fnd_languages.language_code%TYPE;
3569 BEGIN
3570
3571 /* Fix perf bug 3669930, 4220460, use cached profile option value
3572 -- check profile option
3573 fnd_profile.get(
3574 name => k_profile_ref_lang,
3575 val => l_language_code
3576 );
3577 */
3578 l_language_code := g_profile_ref_lang;
3579 -- if not set, get current session NLS setting
3580 IF l_language_code IS NULL THEN
3581 BEGIN
3582 l_language_code := userenv('LANG');
3583 EXCEPTION
3584 WHEN NO_DATA_FOUND THEN NULL; -- cannot determine language
3585 END;
3586 END IF;
3587
3588
3589 x_ref_language_code := l_language_code;
3590
3591 END get_default_ref_language;
3592
3593
3594 /*=========================================================================+
3595 |
3596 | PROCEDURE: get_country_name_lang
3597 |
3598 | DESCRIPTION
3599 |
3600 | This is a utility procedure that determine's the language to display
3601 | the country name in.
3602 |
3603 | It is usually appropriate to display it in the language of the
3604 | country from where an item is mailed (reference language).
3605 |
3606 | However, in cases where this is not appropriate, a profile option
3607 | is available to choose an appropriate language.
3608 |
3609 | The "language for country line" is obtained as follows:
3610 |
3611 | 1. Check for the profile option HZ_LANG_FOR_COUNTRY_DISPLAY
3612 | 2. If null, use the Reference Language
3613 | 3. If null, obtain the current session NLS language setting
3614 |
3615 |
3616 | SCOPE: Public
3617 |
3618 | ARGUMENTS: (OUT)
3619 | x_country_name_lang Language for country display.
3620 |
3621 +=========================================================================*/
3622
3623 PROCEDURE get_country_name_lang (
3624 x_country_name_lang OUT NOCOPY fnd_languages.language_code%TYPE
3625 ) IS
3626 l_language_code fnd_languages.language_code%TYPE;
3627 BEGIN
3628
3629 --
3630 -- Language
3631 --
3632
3633 /* Fix perf bug 3669930, 4220460, use cached profile option value
3634 fnd_profile.get(
3635 name => k_profile_country_lang,
3636 val => l_language_code
3637 );
3638 */
3639 l_language_code := g_profile_country_lang;
3640 IF l_language_code IS NULL THEN
3641 /* Fix perf bug 3669930, 4220460, use cached profile option value
3642 fnd_profile.get(
3643 name => k_profile_ref_lang,
3644 val => l_language_code
3645 );
3646 */
3647 l_language_code := g_profile_ref_lang;
3648 IF l_language_code IS NULL THEN
3649 l_language_code := userenv('LANG');
3650 END IF;
3651 END IF;
3652
3653 --
3654 -- Set up return parameters
3655 --
3656
3657 x_country_name_lang := l_language_code;
3658
3659 END get_country_name_lang;
3660
3661
3662 /*=========================================================================+
3663 |
3664 | PROCEDURE: load_internal_format_table
3665 |
3666 | DESCRIPTION
3667 |
3668 | Loads table HZ_STYLE_FMT_LAYOUTS into an internal pl/sql table.
3669 |
3670 | SCOPE: Private
3671 |
3672 | ARGUMENTS: (IN) p_style_format_code Style Format Code for which
3673 | the layout is to be loaded.
3674 | p_variation_num Which variation to load?
3675 |
3676 | (IN/OUT) x_layout_tbl PL/SQL table where the data
3677 | should be loaded.
3678 | x_loaded_rows_cnt Number of rows loaded.
3679 |
3680 | !! THIS PROCEDURE CHECKS CACHE... ASSUMES GLOBAL TABLE IS USED !!!
3681 +=========================================================================*/
3682
3683 PROCEDURE load_internal_format_table(
3684 p_style_format_code IN VARCHAR2,
3685 p_variation_num IN NUMBER,
3686 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
3687 x_loaded_rows_cnt IN OUT NOCOPY NUMBER
3688 ) IS
3689 i NUMBER := 0;
3690
3691 CURSOR c_style_format_layouts(
3692 p_style_format_code IN VARCHAR2,
3693 p_variation_number IN NUMBER
3694 ) IS
3695 SELECT
3696 line_number,
3697 position,
3698 attribute_code,
3699 use_initial_flag,
3700 uppercase_flag,
3701 transform_function,
3705 blank_lines_after
3702 delimiter_before,
3703 delimiter_after,
3704 blank_lines_before,
3706 FROM hz_style_fmt_layouts_b
3707 WHERE
3708 style_format_code = p_style_format_code
3709 AND variation_number = p_variation_number
3710 AND (SYSDATE BETWEEN start_date_active
3711 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')))
3712 ORDER BY line_number, position;
3713
3714 BEGIN
3715
3716 IF g_caching AND
3717 g_cache_style_format_code = p_style_format_code AND
3718 g_cache_variation_number = p_variation_num
3719 THEN
3720 FOR i IN 1..x_loaded_rows_cnt
3721 LOOP
3722 x_layout_tbl(i).attribute_value := NULL;
3723 END LOOP;
3724 ELSE
3725
3726 x_loaded_rows_cnt := 0;
3727
3728 FOR l_layout_rec IN c_style_format_layouts(p_style_format_code, p_variation_num)
3729 LOOP
3730 i := i + 1;
3731 x_layout_tbl(i).line_number := l_layout_rec.line_number;
3732 x_layout_tbl(i).position := l_layout_rec.position;
3733 x_layout_tbl(i).attribute_code := l_layout_rec.attribute_code;
3737 x_layout_tbl(i).delimiter_before := l_layout_rec.delimiter_before;
3734 x_layout_tbl(i).use_initial_flag := l_layout_rec.use_initial_flag;
3735 x_layout_tbl(i).uppercase_flag := l_layout_rec.uppercase_flag;
3736 x_layout_tbl(i).transform_function := l_layout_rec.transform_function;
3738 x_layout_tbl(i).delimiter_after := l_layout_rec.delimiter_after;
3739 x_layout_tbl(i).blank_lines_before := l_layout_rec.blank_lines_before;
3740 x_layout_tbl(i).blank_lines_after := l_layout_rec.blank_lines_after;
3741 x_layout_tbl(i).attribute_value := NULL;
3742 END LOOP;
3743
3744 x_loaded_rows_cnt := i;
3745 g_cache_style_format_code := p_style_format_code;
3746 g_cache_variation_number := p_variation_num;
3747
3748 END IF;
3749
3750 END load_internal_format_table;
3751
3752 /*=========================================================================+
3753 |
3754 | PROCEDURE: add_parm_table_row
3755 |
3756 | DESCRIPTION
3757 |
3758 | Loads parameter names and values into a PL/SQL table.
3759 |
3760 | SCOPE: Private
3761 |
3762 | ARGUMENTS: (IN)
3763 | p_parm_name Name of the parameter
3764 | p_parm_value Value of the parameter
3765 |
3766 | (IN/OUT)
3767 | x_parm_tbl PL/SQL table where the data
3768 | should be loaded.
3769 | x_loaded_rows_cnt Number of rows loaded.
3770 |
3771 +=========================================================================*/
3772
3773 PROCEDURE add_parm_table_row(
3774 p_parm_name IN VARCHAR2,
3775 p_parm_value IN VARCHAR2,
3776 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
3777 x_loaded_rows_cnt IN OUT NOCOPY NUMBER,
3778 p_parm_type IN VARCHAR2
3779 ) IS
3780
3781 BEGIN
3782 IF p_parm_value IS NOT NULL AND
3783 p_parm_value <> fnd_api.g_miss_char
3784 THEN
3785 x_loaded_rows_cnt := x_loaded_rows_cnt + 1;
3786 x_parm_tbl(x_loaded_rows_cnt).parm_name := p_parm_name;
3787 x_parm_tbl(x_loaded_rows_cnt).parm_value := p_parm_value;
3788 x_parm_tbl(x_loaded_rows_cnt).parm_type := p_parm_type;
3789 END IF;
3790
3791 END add_parm_table_row;
3792
3793 /*=========================================================================+
3794 |
3795 | PROCEDURE: create_sql_string
3796 |
3797 | DESCRIPTION
3798 |
3799 | Creates a SQL query string, based on the columns identifed in
3800 | the internal layout table (based on HZ_STYLE_FMT_LAYOUTS).
3801 |
3802 | SCOPE: Private
3803 |
3804 | ARGUMENTS: (IN) p_table_name Name of the table to be queried.
3805 | p_pk_name Column name of the primary key
3806 | of the table.
3807 | x_layout_tbl Initialized internal layout table.
3808 | p_layout_tbl_cnt Number of rows in internal table.
3809 |
3810 | (IN/OUT) x_sql_string The constructed SQL Query.
3811 |
3812 +=========================================================================*/
3813
3814 PROCEDURE create_sql_string(
3815 p_table_name IN VARCHAR2,
3816 x_pk_tbl IN OUT NOCOPY name_value_tbl_type,
3817 p_pk_tbl_cnt IN NUMBER,
3818 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
3819 p_layout_tbl_cnt IN NUMBER,
3820 x_sql_string IN OUT NOCOPY VARCHAR2
3821 ) IS
3822 l_attribute VARCHAR2(240);
3823 BEGIN
3824
3825 --
3826 -- Create a Dynamic SQL query based on the columns identified
3827 -- in the internal layout table.
3828 --
3829
3830 x_sql_string := NULL;
3831 IF p_layout_tbl_cnt = 0 THEN
3835 FOR i IN 1 .. p_layout_tbl_cnt
3832 RETURN;
3833 END IF;
3834
3836 LOOP
3837 IF x_sql_string IS NOT NULL THEN
3838 x_sql_string := x_sql_string || ', ';
3839 END IF;
3840 l_attribute := x_layout_tbl(i).attribute_code;
3841
3842 -- apply transformation function
3843 IF x_layout_tbl(i).transform_function IS NOT NULL THEN
3844 l_attribute := x_layout_tbl(i).transform_function;
3845 -- || '(' || l_attribute || ')';
3846 END IF;
3847
3848 -- translate to upper case
3849 IF x_layout_tbl(i).uppercase_flag = 'Y' THEN
3850 l_attribute := 'NLS_UPPER(' || l_attribute || ')';
3851 END IF;
3852
3853 -- use initial flag
3854 IF x_layout_tbl(i).use_initial_flag = 'Y' THEN
3855 l_attribute := 'SUBSTRB(' || l_attribute || ',1,1)';
3856 END IF;
3857
3858 IF l_attribute <> x_layout_tbl(i).attribute_code THEN
3859 -- if functions applied, then use attribute name as the alias
3860 l_attribute := l_attribute || ' ' || x_layout_tbl(i).attribute_code;
3861 END IF;
3862
3863 x_sql_string := x_sql_string || l_attribute;
3864 END LOOP;
3865
3866 x_sql_string := 'SELECT ' || x_sql_string || ' FROM '
3867 || p_table_name;
3868
3869 FOR i IN 1..p_pk_tbl_cnt
3870 LOOP
3871 IF i=1 THEN
3872 x_sql_string := x_sql_string || ' WHERE '
3873 || x_pk_tbl(i).parm_name || ' = :' || x_pk_tbl(i).parm_name;
3874 ELSE
3875 x_sql_string := x_sql_string || ' AND '
3876 || x_pk_tbl(i).parm_name || ' = :' || x_pk_tbl(i).parm_name;
3877 END IF;
3878 END LOOP;
3879
3880
3881 END create_sql_string;
3882
3883 /*=========================================================================+
3884 |
3885 | PROCEDURE: execute_query
3886 |
3887 | DESCRIPTION
3888 |
3889 | Dynamically executes a SQL query, based on the string passed
3890 | in p_sql_string.
3891 |
3892 | THE RESULTS ARE RETURNED in the internal layout table, with
3893 | column values being placed in the field 'attribute_value'.
3894 |
3895 | THIS PROCEDURE ASSUMES that the SQL query being passed requires
3896 | a single BIND VARIABLE to be resolved, i.e. the primary key of
3897 | the object being formatted. The parameter p_pk_value contains
3898 | the value to be substituted for this bind variable.
3899 |
3900 | WHY IS DBMS_SQL being used instead of Native Dynamic SQL (NDS)?
3901 | Wouldn't it be easier to use EXECUTE IMMEDIATE? Well, the problem
3902 | here is that our column list is dynamic. NDS does not support
3903 | this type of Dynamic SQL, and we must revert to using DBMS_SQL.
3904 |
3905 | SCOPE: Private
3906 |
3907 | ARGUMENTS: (IN)
3908 | p_table_name Name of the table to be queried.
3909 | p_pk_value Primary Key value that identifies the
3910 | data to be retrieved.
3911 | p_layout_tbl_cnt Number of rows in the internal table.
3912 |
3913 | (IN/OUT) x_layout_tbl The internal layout table.
3914 | Only field 'attribute_value' is
3915 | populated by this procedure.
3916 |
3917 +=========================================================================*/
3918
3919 PROCEDURE execute_query(
3923 p_layout_tbl_cnt IN NUMBER,
3920 p_sql_string IN VARCHAR2,
3921 x_pk_tbl IN OUT NOCOPY name_value_tbl_type,
3922 p_pk_tbl_cnt IN NUMBER,
3924 x_layout_tbl IN OUT NOCOPY layout_tbl_type
3925 ) IS
3926 l_cursor_name INTEGER;
3927 l_ret_value INTEGER;
3928
3929 l_key_value_number NUMBER;
3930 l_key_value_date DATE;
3931 BEGIN
3932
3933 l_cursor_name := DBMS_SQL.OPEN_CURSOR;
3934
3935 DBMS_SQL.PARSE(l_cursor_name, p_sql_string, DBMS_SQL.NATIVE);
3936
3937 FOR i IN 1 .. p_layout_tbl_cnt
3938 LOOP
3939 DBMS_SQL.DEFINE_COLUMN(l_cursor_name, i, x_layout_tbl(i).attribute_value, 240);
3940 END LOOP;
3941
3942 --
3943 -- Bind Values to Variables
3944 --
3945
3946 FOR i IN 1..p_pk_tbl_cnt
3947 LOOP
3948 IF x_pk_tbl(i).parm_type = 'N' THEN -- numeric
3949 l_key_value_number := x_pk_tbl(i).parm_value;
3950 DBMS_SQL.BIND_VARIABLE(l_cursor_name,
3951 ':'||x_pk_tbl(i).parm_name, l_key_value_number);
3952 ELSIF x_pk_tbl(i).parm_type = 'V' THEN -- Varchar2
3953 DBMS_SQL.BIND_VARIABLE(l_cursor_name,
3954 ':'||x_pk_tbl(i).parm_name, x_pk_tbl(i).parm_value);
3955 ELSIF x_pk_tbl(i).parm_type = 'D' THEN -- Date
3956 l_key_value_date := to_date(x_pk_tbl(i).parm_value,'YYYY/MM/DD');
3957 DBMS_SQL.BIND_VARIABLE(l_cursor_name,
3958 ':'||x_pk_tbl(i).parm_name, l_key_value_date);
3959 END IF;
3960
3961 END LOOP;
3962
3966
3963 --DBMS_SQL.BIND_VARIABLE(l_cursor_name, ':'||p_pk_name, p_pk_value);
3964
3965 l_ret_value := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor_name);
3967 FOR i IN 1 .. p_layout_tbl_cnt
3968 LOOP
3969 DBMS_SQL.COLUMN_VALUE(l_cursor_name, i, x_layout_tbl(i).attribute_value);
3970 END LOOP;
3971
3972 DBMS_SQL.CLOSE_CURSOR(l_cursor_name);
3973
3974 END execute_query;
3975
3976
3977 /*=========================================================================+
3978 |
3979 | PROCEDURE: format_results
3980 |
3981 | DESCRIPTION
3982 |
3983 | Once the internal layout table is populated with the layout
3984 | definitions AND the column values of the object being formatted,
3985 | then this procedure can be called to apply the "layout rules"
3986 | and format the data.
3987 |
3988 | SCOPE: Private
3989 |
3990 | ARGUMENTS:
3991 |
3992 | (IN) p_layout_tbl_cnt Number of rows in internal table.
3993 | (IN/OUT) x_layout_tbl The internal layout table,
3994 | fully populated.
3995 | x_formatted_lines_tbl Formatted data, a table of
3996 | strings is returned.
3997 | x_formatted_lines_cnt Number of lines formatted.
3998 |
3999 +=========================================================================*/
4000
4001 PROCEDURE format_results (
4002 p_space_replace IN VARCHAR2,
4003 p_layout_tbl_cnt IN NUMBER,
4004 x_layout_tbl IN OUT NOCOPY layout_tbl_type,
4005 x_formatted_lines_tbl IN OUT NOCOPY string_tbl_type,
4006 x_formatted_lines_cnt IN OUT NOCOPY NUMBER
4007 ) IS
4008 l_current_line NUMBER := 1; -- this keeps track of line_number changes
4009 l_current_idx NUMBER := 0; -- this is used for output index
4010 l_current_text VARCHAR2(240);
4011 BEGIN
4012
4013 FOR i IN 1..p_layout_tbl_cnt
4014 LOOP
4015
4016 --
4017 -- Dump the previous line if we're onto the next line
4018 --
4019
4020 IF x_layout_tbl(i).line_number > l_current_line THEN
4021 IF l_current_text IS NOT NULL THEN
4022 l_current_idx := l_current_idx + 1;
4023 x_formatted_lines_tbl(l_current_idx) := rtrim(l_current_text);
4024 l_current_text := NULL;
4025 END IF;
4026 IF nvl(x_layout_tbl(i).blank_lines_before,0) > 0 THEN
4027 FOR j IN 1..x_layout_tbl(i).blank_lines_before
4028 LOOP
4029 l_current_idx := l_current_idx + 1;
4030 IF p_space_replace IS NOT NULL THEN
4031 x_formatted_lines_tbl(l_current_idx) := p_space_replace;
4032 ELSE
4033 x_formatted_lines_tbl(l_current_idx) := ' ';
4034 END IF;
4035 END LOOP;
4036 END IF;
4037 l_current_line := x_layout_tbl(i).line_number;
4038 END IF;
4039
4040 --
4041 -- Append the current attribute value onto the line,
4042 -- with delimeters.
4043 --
4044
4045 IF x_layout_tbl(i).attribute_value IS NOT NULL THEN
4046 IF p_space_replace IS NOT NULL THEN
4047 l_current_text := l_current_text
4048 || replace(x_layout_tbl(i).delimiter_before,' ',p_space_replace)
4049 || x_layout_tbl(i).attribute_value
4050 || replace(x_layout_tbl(i).delimiter_after,' ',p_space_replace);
4051 ELSE
4052 l_current_text := l_current_text
4053 || x_layout_tbl(i).delimiter_before
4054 || x_layout_tbl(i).attribute_value
4055 || x_layout_tbl(i).delimiter_after;
4056 END IF;
4057 END IF;
4058
4059
4060 END LOOP;
4061
4062 --
4063 -- Dump the last line
4064 --
4065
4066 IF l_current_text IS NOT NULL THEN
4067 l_current_idx := l_current_idx + 1;
4068 x_formatted_lines_tbl(l_current_idx) := rtrim(l_current_text);
4069 END IF;
4070
4071 x_formatted_lines_cnt := l_current_idx;
4072
4073 END format_results;
4074
4075 /*=========================================================================+
4076 |
4077 | PROCEDURE: determine_variation
4078 |
4079 | DESCRIPTION
4080 |
4081 | This procedure determines which format variation to use based
4082 | on the conditions defined.
4083 |
4084 | SCOPE: Private
4085 |
4086 | ARGUMENTS: (IN)
4087 | p_reset_territory True if you want the overridden
4088 | reference territory to be reset.
4089 | p_reset_language True if you want the overridden
4090 | reference language to be reset.
4091 | the localized Style Format.
4092 |
4093 +=========================================================================*/
4094
4095 PROCEDURE determine_variation (
4096 p_style_format_code IN VARCHAR2,
4097 p_object_name IN VARCHAR2,
4098 p_object_pk_name IN VARCHAR2,
4099 p_object_pk_value IN VARCHAR2,
4100 x_variation_num OUT NOCOPY NUMBER
4101 ) IS
4102
4103 CURSOR c_variations (p_style_format_code IN VARCHAR2) IS
4104 SELECT
4105 variation_number,
4106 selection_condition
4107 FROM
4108 hz_style_fmt_variations
4109 WHERE
4110 style_format_code = p_style_format_code
4111 AND (SYSDATE BETWEEN start_date_active
4112 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')))
4116 l_sql_string VARCHAR2(2000);
4113 ORDER BY
4114 variation_rank;
4115
4117
4118 l_result NUMBER;
4119
4120 BEGIN
4121
4122 FOR l_variation IN c_variations (p_style_format_code)
4123 LOOP
4124
4125 IF l_variation.selection_condition IS NULL THEN
4126
4127 -- this is it. no condition - have to take it.
4128 x_variation_num := l_variation.variation_number;
4129 RETURN;
4130 ELSE
4131 -- execute the condition.
4132 l_sql_string := 'SELECT 1 FROM ' || p_object_name
4133 || ' WHERE ' || p_object_pk_name || '=' || ' :1 '
4134 || ' AND ( ' || l_variation.selection_condition || ')';
4135
4136 BEGIN
4137 EXECUTE IMMEDIATE l_sql_string INTO l_result USING p_object_pk_value;
4138 EXCEPTION
4139 WHEN NO_DATA_FOUND THEN
4140 NULL; -- condition not true
4141 -- WHEN OTHERS THEN
4142
4143 END;
4144
4145 IF l_result = 1 THEN
4146 x_variation_num := l_variation.variation_number;
4147 RETURN;
4148 END IF;
4149 END IF;
4150 END LOOP;
4151
4152 END determine_variation;
4153
4154 /*=========================================================================+
4155 |
4156 | PROCEDURE: determine_variation
4157 |
4158 | DESCRIPTION
4159 |
4160 | This procedure determines which format variation to use based
4161 | on the conditions defined.
4162 |
4163 | SCOPE: Private
4164 |
4165 | ARGUMENTS: (IN)
4166 | p_reset_territory True if you want the overridden
4167 | reference territory to be reset.
4168 | p_reset_language True if you want the overridden
4169 | reference language to be reset.
4170 | the localized Style Format.
4171 |
4172 +=========================================================================*/
4173
4174 PROCEDURE determine_variation (
4175 p_style_format_code IN VARCHAR2,
4176 p_parm_tbl_cnt IN NUMBER,
4177 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
4178 x_variation_num OUT NOCOPY NUMBER
4179 ) IS
4180
4181 CURSOR c_variations (p_style_format_code IN VARCHAR2) IS
4182 SELECT
4183 variation_number,
4184 selection_condition
4185 FROM
4186 hz_style_fmt_variations
4187 WHERE
4188 style_format_code = p_style_format_code
4189 AND (SYSDATE BETWEEN start_date_active
4190 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')))
4191 ORDER BY
4192 variation_rank;
4193
4194 l_sql_string VARCHAR2(2000);
4195 l_result NUMBER;
4196 BEGIN
4197 FOR l_variation IN c_variations(p_style_format_code)
4198 LOOP
4199
4200 IF l_variation.selection_condition IS NULL THEN
4201 -- this is it. no condition - have to take it.
4202 x_variation_num := l_variation.variation_number;
4203 RETURN;
4204 END IF;
4205
4206 l_sql_string := l_variation.selection_condition;
4207
4208 substitute_tokens (
4209 p_parm_tbl_cnt => p_parm_tbl_cnt,
4210 x_parm_tbl => x_parm_tbl,
4211 x_string => l_sql_string
4212 );
4213
4214 --
4215 -- Test if the condition is true
4216 --
4217
4218 l_sql_string := 'SELECT 1 FROM DUAL WHERE ' || l_sql_string;
4219 BEGIN
4220 EXECUTE IMMEDIATE l_sql_string INTO l_result;
4221 EXCEPTION
4222 WHEN NO_DATA_FOUND THEN
4223 NULL; -- means the condition is false, so continue...
4224
4225 -- bug fix 2905180
4226 WHEN OTHERS THEN
4227 NULL;
4228
4229 END;
4230 IF l_result=1 THEN
4231 -- Condition is true
4232 x_variation_num := l_variation.variation_number;
4233 RETURN;
4234 END IF;
4235
4236 END LOOP;
4237
4238 END determine_variation;
4239
4240 /*=========================================================================+
4241 |
4242 | PROCEDURE: copy_attribute_values
4243 |
4244 | DESCRIPTION
4245 |
4246 | This procedure copies the attribute values from the parameter table
4247 | to the internal formatting table.
4248 |
4249 | SCOPE: Private
4250 |
4251 +=========================================================================*/
4252
4253 PROCEDURE copy_attribute_values (
4254 p_parm_tbl_cnt IN NUMBER,
4255 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
4256 p_layout_tbl_cnt IN NUMBER,
4257 x_layout_tbl IN OUT NOCOPY layout_tbl_type
4258 ) IS
4259 l_sql_string VARCHAR2(2000);
4260 BEGIN
4261 FOR i IN 1..p_parm_tbl_cnt
4262 LOOP
4263 IF x_parm_tbl(i).parm_value IS NOT NULL THEN
4264 <<layout>>
4265 FOR j IN 1..p_layout_tbl_cnt
4266 LOOP
4267 IF x_parm_tbl(i).parm_name = x_layout_tbl(j).attribute_code THEN
4268 l_sql_string := NULL;
4269
4270 IF x_layout_tbl(j).transform_function IS NOT NULL THEN
4271 l_sql_string := x_layout_tbl(j).transform_function;
4272 substitute_tokens(
4276 );
4273 p_parm_tbl_cnt => p_parm_tbl_cnt,
4274 x_parm_tbl => x_parm_tbl,
4275 x_string => l_sql_string
4277 END IF;
4278 IF x_layout_tbl(j).uppercase_flag = 'Y' THEN
4279 IF x_layout_tbl(j).transform_function IS NOT NULL THEN
4280 l_sql_string := 'NLS_UPPER('||nvl(l_sql_string,''''||x_parm_tbl(i).parm_value||'''')||')';
4281 END IF;
4282 END IF;
4283 IF x_layout_tbl(j).use_initial_flag = 'Y' THEN
4284 l_sql_string := 'SUBSTRB('||nvl(l_sql_string,''''||x_parm_tbl(i).parm_value||'''')||',1,1)';
4285 END IF;
4286
4287 IF l_sql_string IS NOT NULL THEN
4288 l_sql_string := 'SELECT ' || l_sql_string || 'FROM DUAL' ;
4289 EXECUTE IMMEDIATE l_sql_string INTO x_layout_tbl(j).attribute_value;
4290 ELSE
4291 IF x_layout_tbl(j).uppercase_flag = 'Y' THEN
4292 x_layout_tbl(j).attribute_value := upper(x_parm_tbl(i).parm_value);
4293 ELSE
4294 x_layout_tbl(j).attribute_value := x_parm_tbl(i).parm_value;
4295 END IF;
4296 END IF;
4297
4298 EXIT layout;
4299 END IF;
4300 END LOOP;
4301 END IF;
4302 END LOOP;
4303 END copy_attribute_values;
4304
4305
4306 PROCEDURE substitute_tokens (
4307 p_parm_tbl_cnt IN NUMBER,
4308 x_parm_tbl IN OUT NOCOPY name_value_tbl_type,
4309 x_string IN OUT NOCOPY VARCHAR2
4310 ) IS
4311 l_sub_string VARCHAR2(2000);
4312 BEGIN
4313
4314 l_sub_string := x_string;
4318 IF x_parm_tbl(i).parm_value IS NULL THEN
4315 FOR i IN 1..p_parm_tbl_cnt LOOP
4316
4317 IF instrb(l_sub_string, x_parm_tbl(i).parm_name) > 0 THEN
4319 l_sub_string := replace(l_sub_string,x_parm_tbl(i).parm_name,'NULL');
4320 ELSE
4321 l_sub_string := replace(l_sub_string,x_parm_tbl(i).parm_name,
4322 ''''||x_parm_tbl(i).parm_value||'''');
4323 END IF;
4324 END IF;
4325 END LOOP;
4326
4327 x_string := l_sub_string;
4328 END substitute_tokens;
4329
4330
4331
4332
4333 /*=========================================================================+
4334 |
4335 | PROCEDURE: set_context
4336 |
4337 | DESCRIPTION
4338 |
4339 | This procedure is called by the format_name, format_address and
4340 | format_data public APIs. It saves the "context" in which the
4341 | APIs were called in, so that it can be made available to any
4342 | routine that requires it later.
4343 |
4344 | For example, the style metadata allows functions to be defined
4345 | to "transform" an attribute (e.g. lookup country name from
4346 | country code). That translation function needs context information
4347 | to know what language to display the country name in, and also
4348 | whether to show or hide the country name.
4349 |
4350 | SCOPE: Private
4351 |
4352 +=========================================================================*/
4353
4354 PROCEDURE set_context (
4355 p_style_code IN hz_styles_b.style_code%TYPE,
4356 p_style_format_code IN hz_style_formats_b.style_format_code%TYPE,
4357 p_to_territory_code IN fnd_territories.territory_code%TYPE,
4358 p_to_language_code IN fnd_languages.language_code%TYPE,
4359 p_from_territory_code IN fnd_territories.territory_code%TYPE,
4360 p_from_language_code IN fnd_languages.language_code%TYPE,
4361 p_country_name_lang IN fnd_languages.language_code%TYPE
4362 ) IS
4363 BEGIN
4364
4365 -- Access the global context directly
4366 g_context.style_code := p_style_code;
4367 g_context.style_format_code := p_style_format_code;
4368 g_context.to_territory_code := p_to_territory_code;
4369 g_context.to_language_code := p_to_language_code;
4370 g_context.from_territory_code := p_from_territory_code;
4371 g_context.from_language_code := p_from_language_code;
4372 g_context.country_name_lang := p_country_name_lang;
4373 END;
4374
4375 END hz_format_pub;