[Home] [Help]
PACKAGE: APPS.HZ_GNR_PVT
Source
1 PACKAGE HZ_GNR_PVT AUTHID CURRENT_USER AS
2 /* $Header: ARHGNRVS.pls 120.6 2006/02/09 21:10:06 nsinghai noship $ */
3
4 /****************************************************************************
5 Created By Nishant Singhai 02-Jun-2005 Address Suggestion API
6 ***************************************************************************/
7
8 /* Package variables */
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'HZ_GNR_PVT';
11
12 -- Public variable to keep track of number of times user is making attempt for
13 -- the address
14 G_USER_ATTEMPT_COUNT VARCHAR2(30);
15
16 -- Record for other output parameters. This can be extended in future for
17 -- other out variables (added on 14-Sep-2005 by Nishant)
18 TYPE geo_suggest_misc_rec IS RECORD
19 ( v_suggestion_msg_text VARCHAR2(1000)
20 );
21
22 TYPE geo_struct_rec IS RECORD
23 ( v_tab_col VARCHAR2(100),
24 v_geo_type VARCHAR2(100),
25 v_element_col VARCHAR2(100),
26 v_level NUMBER,
27 v_param_value VARCHAR2(255),
28 v_valid_for_usage VARCHAR2(10)
29 );
30
31 TYPE geo_struct_tbl_type IS TABLE OF geo_struct_rec INDEX BY BINARY_INTEGER;
32
33 TYPE geo_suggest_rec IS RECORD
34 (
35 country hz_locations.country%TYPE,
36 country_code hz_geographies.geography_code%TYPE,
37 country_geo_id hz_geographies.geography_id%TYPE,
38 country_geo_type hz_geographies.geography_type%TYPE,
39 state hz_locations.state%TYPE,
40 state_code hz_geographies.geography_code%TYPE,
41 state_geo_id hz_geographies.geography_id%TYPE,
42 state_geo_type hz_geographies.geography_type%TYPE,
43 province hz_locations.province%TYPE,
44 province_code hz_geographies.geography_code%TYPE,
45 province_geo_id hz_geographies.geography_id%TYPE,
46 province_geo_type hz_geographies.geography_type%TYPE,
47 county hz_locations.county%TYPE,
48 county_geo_id hz_geographies.geography_id%TYPE,
49 county_geo_type hz_geographies.geography_type%TYPE,
50 city hz_locations.city%TYPE,
51 city_geo_id hz_geographies.geography_id%TYPE,
52 city_geo_type hz_geographies.geography_type%TYPE,
53 postal_code hz_locations.postal_code%TYPE,
54 postal_code_geo_id hz_geographies.geography_id%TYPE,
55 postal_code_geo_type hz_geographies.geography_type%TYPE,
56 postal_plus4_code hz_locations.postal_plus4_code%TYPE,
57 postal_plus4_code_geo_id hz_geographies.geography_id%TYPE,
58 postal_plus4_code_geo_type hz_geographies.geography_type%TYPE,
59 attribute1 hz_locations.attribute1%TYPE,
60 attribute1_geo_id hz_geographies.geography_id%TYPE,
61 attribute1_geo_type hz_geographies.geography_type%TYPE,
62 attribute2 hz_locations.attribute2%TYPE,
63 attribute2_geo_id hz_geographies.geography_id%TYPE,
64 attribute2_geo_type hz_geographies.geography_type%TYPE,
65 attribute3 hz_locations.attribute3%TYPE,
66 attribute3_geo_id hz_geographies.geography_id%TYPE,
67 attribute3_geo_type hz_geographies.geography_type%TYPE,
68 attribute4 hz_locations.attribute4%TYPE,
69 attribute4_geo_id hz_geographies.geography_id%TYPE,
70 attribute4_geo_type hz_geographies.geography_type%TYPE,
71 attribute5 hz_locations.attribute5%TYPE,
72 attribute5_geo_id hz_geographies.geography_id%TYPE,
73 attribute5_geo_type hz_geographies.geography_type%TYPE,
74 attribute6 hz_locations.attribute6%TYPE,
75 attribute6_geo_id hz_geographies.geography_id%TYPE,
76 attribute6_geo_type hz_geographies.geography_type%TYPE,
77 attribute7 hz_locations.attribute7%TYPE,
78 attribute7_geo_id hz_geographies.geography_id%TYPE,
79 attribute7_geo_type hz_geographies.geography_type%TYPE,
80 attribute8 hz_locations.attribute8%TYPE,
81 attribute8_geo_id hz_geographies.geography_id%TYPE,
82 attribute8_geo_type hz_geographies.geography_type%TYPE,
83 attribute9 hz_locations.attribute9%TYPE,
84 attribute9_geo_id hz_geographies.geography_id%TYPE,
85 attribute9_geo_type hz_geographies.geography_type%TYPE,
86 attribute10 hz_locations.attribute10%TYPE,
87 attribute10_geo_id hz_geographies.geography_id%TYPE,
88 attribute10_geo_type hz_geographies.geography_type%TYPE,
89 suggestion_list VARCHAR2(4000)
90 );
91
92 TYPE geo_suggest_tbl_type IS TABLE OF geo_suggest_rec INDEX BY BINARY_INTEGER;
93
94 PROCEDURE search_geographies
95 (
96 p_table_name IN VARCHAR2 DEFAULT 'HZ_LOCATIONS',
97 p_address_style IN VARCHAR2 DEFAULT NULL,
98 p_address_usage IN VARCHAR2 DEFAULT 'GEOGRAPHY',
99 p_country_code IN HZ_LOCATIONS.COUNTRY%TYPE,
100 p_state IN HZ_LOCATIONS.STATE%TYPE DEFAULT NULL,
101 p_province IN HZ_LOCATIONS.PROVINCE%TYPE DEFAULT NULL,
102 p_county IN HZ_LOCATIONS.COUNTY%TYPE DEFAULT NULL,
103 p_city IN HZ_LOCATIONS.CITY%TYPE DEFAULT NULL,
104 p_postal_code IN HZ_LOCATIONS.POSTAL_CODE%TYPE DEFAULT NULL,
105 p_postal_plus4_code IN HZ_LOCATIONS.POSTAL_PLUS4_CODE%TYPE DEFAULT NULL,
106 p_attribute1 IN HZ_LOCATIONS.ATTRIBUTE1%TYPE DEFAULT NULL,
107 p_attribute2 IN HZ_LOCATIONS.ATTRIBUTE2%TYPE DEFAULT NULL,
108 p_attribute3 IN HZ_LOCATIONS.ATTRIBUTE3%TYPE DEFAULT NULL,
109 p_attribute4 IN HZ_LOCATIONS.ATTRIBUTE4%TYPE DEFAULT NULL,
110 p_attribute5 IN HZ_LOCATIONS.ATTRIBUTE5%TYPE DEFAULT NULL,
111 p_attribute6 IN HZ_LOCATIONS.ATTRIBUTE6%TYPE DEFAULT NULL,
112 p_attribute7 IN HZ_LOCATIONS.ATTRIBUTE7%TYPE DEFAULT NULL,
113 p_attribute8 IN HZ_LOCATIONS.ATTRIBUTE8%TYPE DEFAULT NULL,
114 p_attribute9 IN HZ_LOCATIONS.ATTRIBUTE9%TYPE DEFAULT NULL,
115 p_attribute10 IN HZ_LOCATIONS.ATTRIBUTE10%TYPE DEFAULT NULL,
116 x_mapped_struct_count OUT NOCOPY NUMBER,
117 x_records_count OUT NOCOPY NUMBER,
118 x_return_code OUT NOCOPY NUMBER,
119 x_validation_level OUT NOCOPY VARCHAR2,
120 x_geo_suggest_tbl OUT NOCOPY HZ_GNR_PVT.geo_suggest_tbl_type,
121 x_geo_struct_tbl OUT NOCOPY HZ_GNR_PVT.geo_struct_tbl_type,
122 x_geo_suggest_misc_rec OUT NOCOPY HZ_GNR_PVT.geo_suggest_misc_rec,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2
126 );
127
128 END HZ_GNR_PVT;