[Home] [Help]
PACKAGE BODY: APPS.HZ_GNR_PUB
Source
1 PACKAGE BODY HZ_GNR_PUB AS
2 /*$Header: ARHGNRPB.pls 120.9 2006/06/16 18:34:36 nsinghai noship $ */
3
4 -----------------------------------------------------------------------------+
5 -- Package variables
6 -----------------------------------------------------------------------------+
7 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRPB:HZ_GNR_PUB';
8 l_module VARCHAR2(30) ;
9 l_debug_prefix VARCHAR2(30) ;
10
11 PROCEDURE process_gnr (
12 p_location_table_name IN VARCHAR2,
13 p_location_id IN NUMBER,
14 p_call_type IN VARCHAR2,
15 p_init_msg_list IN VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2) IS
19
20 l_return_status VARCHAR2(1);
21 l_addr_val_level VARCHAR2(30);
22 l_addr_warn_msg VARCHAR2(2000);
23 l_addr_val_status VARCHAR2(1);
24
25 CURSOR c_loc_hz (p_location_id in number) IS
26 SELECT
27 LOCATION_ID,
28 ADDRESS_STYLE,
29 COUNTRY,
30 STATE,
31 PROVINCE,
32 COUNTY,
33 CITY,
34 POSTAL_CODE,
35 POSTAL_PLUS4_CODE,
36 ATTRIBUTE1,
37 ATTRIBUTE2,
38 ATTRIBUTE3,
39 ATTRIBUTE4,
40 ATTRIBUTE5,
41 ATTRIBUTE6,
42 ATTRIBUTE7,
43 ATTRIBUTE8,
44 ATTRIBUTE9,
45 ATTRIBUTE10
46 FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
47
48 BEGIN
49 -- initializing the retun value
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 IF upper(p_location_table_name) = 'HR_LOCATIONS_ALL' THEN
53
54 IF p_call_type = 'U' THEN
55 HZ_GNR_PKG.delete_gnr(
56 p_locId => p_location_id,
57 p_locTbl => upper(p_location_table_name),
58 x_status => l_return_status
59 );
60 END IF;
61 HZ_GNR_PKG.validateHrLoc(
62 P_LOCATION_ID => p_location_id,
63 X_STATUS => l_return_status);
64
65 ELSIF upper(p_location_table_name) = 'HZ_LOCATIONS' THEN
66
67 IF p_call_type = 'U' THEN
68 HZ_GNR_PKG.delete_gnr(
69 p_locId => p_location_id,
70 p_locTbl => upper(p_location_table_name),
71 x_status => l_return_status
72 );
73 END IF;
74
75 FOR l_c_loc_hz in c_loc_hz(p_location_id) LOOP
76 HZ_GNR_PKG.validateLoc(
77 P_LOCATION_ID => l_c_loc_hz.LOCATION_ID,
78 P_USAGE_CODE => 'ALL',
79 P_ADDRESS_STYLE => l_c_loc_hz.ADDRESS_STYLE,
80 P_COUNTRY => l_c_loc_hz.COUNTRY,
81 P_STATE => l_c_loc_hz.STATE,
82 P_PROVINCE => l_c_loc_hz.PROVINCE,
83 P_COUNTY => l_c_loc_hz.COUNTY,
84 P_CITY => l_c_loc_hz.CITY,
85 P_POSTAL_CODE => l_c_loc_hz.POSTAL_CODE,
86 P_POSTAL_PLUS4_CODE => l_c_loc_hz.POSTAL_PLUS4_CODE,
87 P_ATTRIBUTE1 => l_c_loc_hz.ATTRIBUTE1,
88 P_ATTRIBUTE2 => l_c_loc_hz.ATTRIBUTE2,
89 P_ATTRIBUTE3 => l_c_loc_hz.ATTRIBUTE3,
90 P_ATTRIBUTE4 => l_c_loc_hz.ATTRIBUTE4,
91 P_ATTRIBUTE5 => l_c_loc_hz.ATTRIBUTE5,
92 P_ATTRIBUTE6 => l_c_loc_hz.ATTRIBUTE6,
93 P_ATTRIBUTE7 => l_c_loc_hz.ATTRIBUTE7,
94 P_ATTRIBUTE8 => l_c_loc_hz.ATTRIBUTE8,
95 P_ATTRIBUTE9 => l_c_loc_hz.ATTRIBUTE9,
96 P_ATTRIBUTE10 => l_c_loc_hz.ATTRIBUTE10,
97 P_CALLED_FROM => 'GNR',
98 X_ADDR_VAL_LEVEL => l_addr_val_level,
99 X_ADDR_WARN_MSG => l_addr_warn_msg,
100 X_ADDR_VAL_STATUS => l_addr_val_status,
101 X_STATUS => l_return_status);
102 END LOOP;
103
104 END IF;
105 EXCEPTION
106 WHEN FND_API.G_EXC_ERROR THEN
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108 WHEN OTHERS THEN
109 x_return_status := 'U';
110 END process_gnr;
111
112 -- Function to fetch address validation level after considering both product level
113 -- profile and country level setting.
114 -- If country level profile is not set or set to no validation, then application
115 -- level profile will be ignored.
116 FUNCTION get_addr_val_level(p_country_code IN VARCHAR2) RETURN VARCHAR2 IS
117
118 l_addr_val_level varchar2(30);
119 l_addr_val_level_temp varchar2(30);
120
121 CURSOR c_addr_val(p_country_code IN VARCHAR2) IS
122 select ADDR_VAL_LEVEL
123 from hz_geo_structure_levels
124 where PARENT_GEOGRAPHY_TYPE = 'COUNTRY'
125 and COUNTRY_CODE = p_country_code;
126 BEGIN
127 OPEN c_addr_val(p_country_code);
128 FETCH c_addr_val INTO l_addr_val_level;
129 CLOSE c_addr_val;
130
131 -- Fix for Bug 4970612 added by nsinghai on 25-Jan-2006
132 -- If country setting is NO VALIDATION, then ignore application level profile
133 IF ((l_addr_val_level IS NULL) OR (l_addr_val_level = 'NONE'))THEN
134 RETURN 'NONE';
135 END IF;
136 -- For validation level NONE, it will return from above. So do not need to
137 -- check the same in next condition
138 IF (l_addr_val_level IS NOT NULL) THEN
139 l_addr_val_level_temp := FND_PROFILE.value( 'HZ_APP_ADDR_VAL');
140 END IF;
141 IF l_addr_val_level_temp IS NOT NULL THEN
142 RETURN l_addr_val_level_temp;
143 END IF;
144 RETURN l_addr_val_level;
145 END get_addr_val_level;
146
147 PROCEDURE get_addr_val_status(
148 p_location_table_name IN VARCHAR2,
149 p_location_id IN NUMBER,
150 p_usage_code IN VARCHAR2,
151 x_is_validated OUT NOCOPY VARCHAR2,
152 x_address_status OUT NOCOPY VARCHAR2) IS
153
154 l_address_status VARCHAR2(30);
155
156 CURSOR c_gnr IS
157 SELECT MAP_STATUS
158 FROM HZ_GEO_NAME_REFERENCE_LOG
159 WHERE LOCATION_TABLE_NAME = p_location_table_name
160 AND LOCATION_ID = p_location_id
161 AND USAGE_CODE = p_usage_code;
162 BEGIN
163 OPEN c_gnr;
164 FETCH c_gnr INTO l_address_status;
165 IF c_gnr%NOTFOUND THEN
166 x_is_validated := FND_API.G_FALSE;
167 ELSE
168 x_is_validated := FND_API.G_TRUE;
169 x_address_status := l_address_status;
170 END IF;
171 CLOSE c_gnr;
172 END get_addr_val_status;
173
174 PROCEDURE validateLoc (
175 p_location_id IN NUMBER,
176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
177 x_addr_val_level OUT NOCOPY VARCHAR2,
178 x_addr_warn_msg OUT NOCOPY VARCHAR2,
179 x_addr_val_status OUT NOCOPY VARCHAR2,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 p_create_gnr_record IN VARCHAR2) IS
184
185 l_location_id NUMBER;
186
187 CURSOR c_loc (p_location_id in number) IS
188 SELECT
189 LOCATION_ID,
190 ADDRESS_STYLE,
191 COUNTRY,
192 STATE,
193 PROVINCE,
194 COUNTY,
195 CITY,
196 POSTAL_CODE,
197 POSTAL_PLUS4_CODE,
198 ATTRIBUTE1,
199 ATTRIBUTE2,
200 ATTRIBUTE3,
201 ATTRIBUTE4,
202 ATTRIBUTE5,
203 ATTRIBUTE6,
204 ATTRIBUTE7,
205 ATTRIBUTE8,
206 ATTRIBUTE9,
207 ATTRIBUTE10
208 FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
209
210 BEGIN
211
212 -- Fix for Bug 5262208 (07-JUN-2006 Nishant). Added new parameter p_create_gnr_record
213 -- If p_create_gnr_record is passed as N in this signature of validateLoc, then
214 -- we will pass location id as NULL, so that it does not create GNR record and
215 -- only does address validation. If Y is passed, GNR will be created.
216 IF (NVL(p_create_gnr_record,'Y') <> 'N') THEN
217 l_location_id := p_location_id;
218 ELSE
219 l_location_id := NULL;
220 END IF;
221
222 FOR l_c_loc in c_loc(p_location_id) LOOP
223 validateLoc(
224 P_LOCATION_ID => l_location_id,
225 P_USAGE_CODE => 'GEOGRAPHY',
226 P_ADDRESS_STYLE => l_c_loc.ADDRESS_STYLE,
227 P_COUNTRY => l_c_loc.COUNTRY,
228 P_STATE => l_c_loc.STATE,
229 P_PROVINCE => l_c_loc.PROVINCE,
230 P_COUNTY => l_c_loc.COUNTY,
231 P_CITY => l_c_loc.CITY,
232 P_POSTAL_CODE => l_c_loc.POSTAL_CODE,
233 P_POSTAL_PLUS4_CODE => l_c_loc.POSTAL_PLUS4_CODE,
234 P_ATTRIBUTE1 => l_c_loc.ATTRIBUTE1,
235 P_ATTRIBUTE2 => l_c_loc.ATTRIBUTE2,
236 P_ATTRIBUTE3 => l_c_loc.ATTRIBUTE3,
237 P_ATTRIBUTE4 => l_c_loc.ATTRIBUTE4,
238 P_ATTRIBUTE5 => l_c_loc.ATTRIBUTE5,
239 P_ATTRIBUTE6 => l_c_loc.ATTRIBUTE6,
240 P_ATTRIBUTE7 => l_c_loc.ATTRIBUTE7,
241 P_ATTRIBUTE8 => l_c_loc.ATTRIBUTE8,
242 P_ATTRIBUTE9 => l_c_loc.ATTRIBUTE9,
246 X_ADDR_VAL_STATUS => x_addr_val_status,
243 P_ATTRIBUTE10 => l_c_loc.ATTRIBUTE10,
244 X_ADDR_VAL_LEVEL => x_addr_val_level,
245 X_ADDR_WARN_MSG => x_addr_warn_msg,
247 X_RETURN_STATUS => x_return_status,
248 X_MSG_COUNT => x_msg_count,
249 X_MSG_DATA => x_msg_data);
250 END LOOP;
251 END validateLoc;
252
253 PROCEDURE validateLoc(
254 p_location_id IN NUMBER,
255 p_init_msg_list IN VARCHAR2,
256 p_usage_code IN VARCHAR2,
257 p_address_style IN VARCHAR2,
258 p_country IN VARCHAR2,
259 p_state IN VARCHAR2,
260 p_province IN VARCHAR2,
261 p_county IN VARCHAR2,
262 p_city IN VARCHAR2,
263 p_postal_code IN VARCHAR2,
264 p_postal_plus4_code IN VARCHAR2,
265 p_attribute1 IN VARCHAR2,
266 p_attribute2 IN VARCHAR2,
267 p_attribute3 IN VARCHAR2,
268 p_attribute4 IN VARCHAR2,
269 p_attribute5 IN VARCHAR2,
270 p_attribute6 IN VARCHAR2,
271 p_attribute7 IN VARCHAR2,
272 p_attribute8 IN VARCHAR2,
273 p_attribute9 IN VARCHAR2,
274 p_attribute10 IN VARCHAR2,
275 x_addr_val_level OUT NOCOPY VARCHAR2,
276 x_addr_warn_msg OUT NOCOPY VARCHAR2,
277 x_addr_val_status OUT NOCOPY VARCHAR2,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2) IS
281
282 l_status VARCHAR2(1);
283 l_msg_count_before NUMBER;
284 l_msg_count_after NUMBER;
285 l_message_text VARCHAR2(1000);
286
287 BEGIN
288 l_module := 'validateLoc';
289
290 -- read the count of messages already stacked before doing validation
291 l_msg_count_before := NVL(fnd_msg_pub.Count_Msg,0);
292
293 HZ_GNR_PKG.validateLoc(
294 P_LOCATION_ID => P_LOCATION_ID,
295 P_USAGE_CODE => P_USAGE_CODE,
296 P_ADDRESS_STYLE => P_ADDRESS_STYLE,
297 P_COUNTRY => P_COUNTRY,
298 P_STATE => P_STATE,
299 P_PROVINCE => P_PROVINCE,
300 P_COUNTY => P_COUNTY,
301 P_CITY => P_CITY,
302 P_POSTAL_CODE => P_POSTAL_CODE,
303 P_POSTAL_PLUS4_CODE => P_POSTAL_PLUS4_CODE,
304 P_ATTRIBUTE1 => P_ATTRIBUTE1,
305 P_ATTRIBUTE2 => P_ATTRIBUTE2,
306 P_ATTRIBUTE3 => P_ATTRIBUTE3,
307 P_ATTRIBUTE4 => P_ATTRIBUTE4,
308 P_ATTRIBUTE5 => P_ATTRIBUTE5,
309 P_ATTRIBUTE6 => P_ATTRIBUTE6,
310 P_ATTRIBUTE7 => P_ATTRIBUTE7,
311 P_ATTRIBUTE8 => P_ATTRIBUTE8,
312 P_ATTRIBUTE9 => P_ATTRIBUTE9,
313 P_ATTRIBUTE10 => P_ATTRIBUTE10,
314 P_CALLED_FROM => 'VALIDATE',
315 P_LOCK_FLAG => FND_API.G_TRUE,
316 X_ADDR_VAL_LEVEL => x_addr_val_level,
317 X_ADDR_WARN_MSG => x_addr_warn_msg,
318 X_ADDR_VAL_STATUS => x_addr_val_status,
319 X_STATUS => l_status);
320
324
321 l_msg_count_after := NVL(fnd_msg_pub.Count_Msg,0);
322 x_msg_count := l_msg_count_after;
323 x_return_status := l_status;
325 -- FND Logging for debug purpose
326 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
327 hz_utility_v2pub.debug
328 (p_message => 'After HZ_GNR_PKG.validateLoc. l_msg_count_before:'||
329 l_msg_count_before||':l_msg_count_after:'||l_msg_count_after||
330 ':x_return_status:'||x_return_status||':x_addr_val_status:'||
331 x_addr_val_status,
332 p_prefix => l_debug_prefix,
333 p_msg_level => fnd_log.level_statement,
334 p_module_prefix => l_module_prefix,
335 p_module => l_module
336 );
337 END IF;
338
339 -- Get warning as well as stacked message out in x_msg_data parameter
340 -- This is for ease of use for other teams which can read only x_msg_data
341 -- for output of message text for Warning as well as Error case.
342 -- Message stack is not being cleared, so that if some one wants to read that
343 -- data, it is still available.
344 -- Nishant (for Bug 5262208 + convenience method as discussion with Vivek and Vinoo)
345 -- (15-Jun-2006)
346 IF (l_status = FND_API.G_RET_STS_SUCCESS AND
347 x_addr_val_status = 'W') THEN
348 x_msg_data := x_addr_warn_msg;
349 ELSIF
350 (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
351
352 IF (l_msg_count_after > l_msg_count_before) THEN
353 FOR i IN l_msg_count_before+1..l_msg_count_after LOOP
354 l_message_text := SUBSTR(l_message_text||fnd_msg_pub.get(p_msg_index => i ,
355 p_encoded => 'F'),1,1000);
356 END LOOP;
357 x_msg_data := l_message_text;
358 END IF;
359 END IF;
360
361 IF (x_addr_val_status IS NULL) THEN
362 x_addr_val_status := l_status;
363 END IF;
364
365 -- FND Logging for debug purpose
366 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
367 hz_utility_v2pub.debug
368 (p_message => 'l_status:'||l_status||':x_msg_data:'||x_msg_data,
369 p_prefix => l_debug_prefix,
370 p_msg_level => fnd_log.level_statement,
371 p_module_prefix => l_module_prefix,
372 p_module => l_module
373 );
374 END IF;
375
376 EXCEPTION WHEN
377 OTHERS THEN
378 IF (l_status IS NOT NULL) THEN
379 x_return_status := l_status;
380 ELSE
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 END IF;
383
384 IF (x_addr_val_status IS NULL) THEN
385 x_addr_val_status := x_return_status;
386 END IF;
387
388 IF (l_status = FND_API.G_RET_STS_SUCCESS AND
389 x_addr_val_status = 'W') THEN
390 x_msg_data := x_addr_warn_msg;
391 ELSE
392
393 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
394 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
395 FND_MSG_PUB.ADD;
396
397 --Standard call to get message count and if count is 1, get message info.
398 FND_MSG_PUB.Count_And_Get(
399 p_encoded => FND_API.G_FALSE,
400 p_count => x_msg_count,
401 p_data => x_msg_data );
402
403 END IF;
404
405 -- FND Logging for debug purpose
406 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
407 hz_utility_v2pub.debug
408 (p_message => 'EXCEPTION :'||SQLERRM,
409 p_prefix => l_debug_prefix,
410 p_msg_level => fnd_log.level_exception,
411 p_module_prefix => l_module_prefix,
412 p_module => l_module
413 );
414 END IF;
415
416 END validateLoc;
417
418 END HZ_GNR_PUB;