[Home] [Help]
PACKAGE BODY: APPS.ZX_HR_LOCATIONS_GNR_HOOK
Source
1 PACKAGE BODY zx_hr_locations_gnr_hook AS
2 /* $Header: zxlocgnrb.pls 120.2 2006/07/14 21:45:09 akaran ship $*/
3
4 PROCEDURE create_geography(p_geography_type IN VARCHAR2,
5 p_geography_value IN VARCHAR2,
6 p_parent_geography_id IN NUMBER,
7 l_geography_id OUT NOCOPY NUMBER,
8 l_return_status OUT NOCOPY VARCHAR2) AS
9 l_master_geo_rec HZ_GEOGRAPHY_PUB.MASTER_GEOGRAPHY_REC_TYPE;
10 x_msg_count NUMBER;
11 x_msg_data VARCHAR2(2000);
12
13 BEGIN
14 l_master_geo_rec.geography_type := p_geography_type;
15 l_master_geo_rec.geography_name := p_geography_value;
16 IF p_geography_type in ('STATE','PROVINCE') THEN
17 l_master_geo_rec.geography_code := p_geography_value;
18 ELSE
19 l_master_geo_rec.geography_code := null;
20 END IF;
21 l_master_geo_rec.start_date := to_date('01-01-1879','MM-DD-YYYY');
22 l_master_geo_rec.end_date := to_date('12-31-4712','MM-DD-YYYY');
23 l_master_geo_rec.geography_code_type:= 'FIPS_CODE';
24 l_master_geo_rec.created_by_module := 'EBTAX_MIGRATION';
25 l_master_geo_rec.application_id := 235;
26 l_master_geo_rec.parent_geography_id(1):= p_parent_geography_id;
27
28 HZ_GEOGRAPHY_PUB.create_master_geography('T',
29 l_master_geo_rec,
30 l_geography_id,
31 l_return_status,
32 x_msg_count,
33 x_msg_data);
34 IF l_return_status = 'E' THEN
35 --dbms_output.put_line(x_msg_data);
36 null;
37 END IF;
38 END;
39
40 PROCEDURE check_geography( p_location_id IN NUMBER,
41 p_country IN VARCHAR2,
42 p_region_1 IN VARCHAR2,
43 p_region_2 IN VARCHAR2,
44 p_region_3 IN VARCHAR2,
45 p_town_or_city IN VARCHAR2,
46 p_postal_code IN VARCHAR2,
47 p_style IN VARCHAR2
48 ) IS
49 TYPE id_type IS TABLE OF NUMBER INDEX BY binary_integer;
50 TYPE code_type IS TABLE OF VARCHAR2(30) INDEX BY binary_integer;
51 l_loc_component_tbl code_type;
52 l_geography_type code_type;
53 l_geography_id_tbl id_type;
54 l_country_geography_id NUMBER;
55 l_geography_id NUMBER;
56 l_geography_value VARCHAR2(150);
57 l_return_status VARCHAR2(1);
58 BEGIN
59 IF p_location_id IS NOT NULL THEN
60 SELECT map_dtl.loc_component, map_dtl.geography_type
61 BULK COLLECT INTO l_loc_component_tbl, l_geography_type
62 FROM hz_geo_struct_map map, hz_geo_struct_map_dtl map_dtl, hz_address_usages usage, hz_address_usage_dtls usage_dtls
63 WHERE map.loc_tbl_name = 'HR_LOCATIONS_ALL'
64 AND map.address_style = p_style
65 AND map.country_code = p_country
66 AND map.map_id = map_dtl.map_id
67 AND map_dtl.map_id = usage.map_id
68 AND usage.usage_code = 'TAX'
69 AND usage.usage_id = usage_dtls.usage_id
70 AND map_dtl.geography_type = usage_dtls.geography_type
71 ORDER BY map_dtl.loc_seq_num;
72
73 SELECT geography_id
74 INTO l_country_geography_id
75 FROM hz_geographies
76 WHERE geography_type = 'COUNTRY'
77 AND geography_code = p_country;
78
79 FOR i IN l_loc_component_tbl.first..l_loc_component_tbl.last LOOP
80 IF l_geography_type(i) <> 'COUNTRY' THEN
81
82 IF l_country_geography_id IS NOT NULL THEN
83
84 IF l_loc_component_tbl(i) = 'REGION_1' THEN
85 l_geography_value := p_region_1;
86 ELSIF l_loc_component_tbl(i) = 'REGION_2' THEN
87 l_geography_value := p_region_2;
88 ELSIF l_loc_component_tbl(i) = 'REGION_3' THEN
89 l_geography_value := p_region_3;
90 ELSIF l_loc_component_tbl(i) = 'TOWN_OR_CITY' THEN
91 l_geography_value := p_town_or_city;
92 ELSIF l_loc_component_tbl(i) = 'POSTAL_CODE' THEN
93 l_geography_value := p_postal_code;
94 END IF;
95
96 BEGIN
97 SELECT child_id
98 INTO l_geography_id_tbl(i)
99 FROM hz_hierarchy_nodes nodes, hz_geographies geo
100 WHERE parent_id = l_geography_id_tbl(i-1)
101 AND parent_object_type = l_geography_type(i-1)
102 AND child_object_type = l_geography_type(i)
103 AND nodes.child_id = geo.geography_id
104 AND UPPER(geo.geography_name) = UPPER(l_geography_value)
105 AND nodes.level_number = 1;
106
107 EXCEPTION WHEN NO_DATA_FOUND THEN
108 l_geography_id_tbl(i) := null;
109
110 END;
111 IF l_geography_id_tbl(i) IS NULL AND l_geography_id_tbl(i-1) IS NOT NULL THEN
112 create_geography( l_geography_type(i),
113 l_geography_value,
114 l_geography_id_tbl(i-1),
115 l_geography_id,
116 l_return_status);
117 IF l_return_status = 'S' THEN
118 l_geography_id_tbl(i) := l_geography_id;
119 END IF;
120 END IF;
121
122 ELSE -- l_country_geography_id is null
123 null;
124 END IF;
125 ELSE
126 l_geography_id_tbl(i) := l_country_geography_id;
127 END IF;
128
129 END LOOP;
130 END IF;
131 EXCEPTION WHEN OTHERS THEN
132 null;
133 END;
134
135 PROCEDURE create_gnr (p_location_id IN NUMBER,
136 p_country IN VARCHAR2,
137 p_region_1 IN VARCHAR2,
138 p_region_2 IN VARCHAR2,
139 p_region_3 IN VARCHAR2,
140 p_town_or_city IN VARCHAR2,
141 p_postal_code IN VARCHAR2,
142 p_style IN VARCHAR2
143 ) IS
144 p_init_msg_list VARCHAR2(1);
145 x_return_status VARCHAR2(1);
146 x_msg_count NUMBER;
147 x_msg_data VARCHAR2(2000);
148 BEGIN
149 check_geography( p_location_id,
150 p_country,
151 p_region_1,
152 p_region_2,
153 p_region_3,
154 p_town_or_city,
155 p_postal_code,
156 p_style);
157
158 IF p_location_id IS NOT NULL THEN
159 HZ_GNR_PUB.process_gnr ('HR_LOCATIONS_ALL',
160 p_location_id,
161 'C',
162 p_init_msg_list,
163 x_return_status,
164 x_msg_count,
165 x_msg_data);
166 END IF;
167
168 END create_gnr;
169
170 PROCEDURE update_gnr (p_location_id IN NUMBER,
171 p_country IN VARCHAR2,
172 p_region_1 IN VARCHAR2,
173 p_region_2 IN VARCHAR2,
174 p_region_3 IN VARCHAR2,
175 p_town_or_city IN VARCHAR2,
176 p_postal_code IN VARCHAR2,
177 p_style_o IN VARCHAR2,
178 p_country_o IN VARCHAR2,
179 p_region_1_o IN VARCHAR2,
180 p_region_2_o IN VARCHAR2,
181 p_region_3_o IN VARCHAR2,
182 p_town_or_city_o IN VARCHAR2,
183 p_postal_code_o IN VARCHAR2) IS
184 p_init_msg_list VARCHAR2(1);
185 x_return_status VARCHAR2(1);
186 x_msg_count NUMBER;
187 x_msg_data VARCHAR2(2000);
188 BEGIN
189 IF p_location_id IS NOT NULL THEN
190 IF NVL(p_country, 'X') <> NVL(p_country_o, 'X') OR
191 NVL(p_region_1,'X') <> NVL(p_region_1_o,'X') OR
192 NVL(p_region_2,'X') <> NVL(p_region_2_o,'X') OR
193 NVL(p_region_3,'X') <> NVL(p_region_3_o,'X') OR
194 NVL(p_town_or_city,'X') <> NVL(p_town_or_city_o,'X') OR
195 NVL(p_postal_code, 'X') <> NVL(p_postal_code_o, 'X') THEN
196
197 check_geography( p_location_id,
198 p_country,
199 p_region_1,
200 p_region_2,
201 p_region_3,
202 p_town_or_city,
203 p_postal_code,
204 p_style_o);
205
206
207 HZ_GNR_PUB.process_gnr ('HR_LOCATIONS_ALL',
208 p_location_id,
209 'U',
210 p_init_msg_list,
211 x_return_status,
212 x_msg_count,
213 x_msg_data);
214
215 END IF;
216 END IF;
217 END update_gnr;
218
219 END zx_hr_locations_gnr_hook;