[Home] [Help]
PACKAGE BODY: APPS.HZ_LOCATION_PROFILE_PVT
Source
1 PACKAGE BODY HZ_LOCATION_PROFILE_PVT AS
2 /*$Header: ARHLCPVB.pls 120.15 2006/06/28 17:41:23 baianand noship $*/
3
4 PROCEDURE log(
5 message IN VARCHAR2,
6 newline IN BOOLEAN DEFAULT TRUE);
7
8 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
9 RETURN VARCHAR2;
10
11 PROCEDURE set_profile_rec_type (
12 p_location_profile_rec IN OUT NOCOPY LOCATION_PROFILE_REC_TYPE
13 );
14
15 PROCEDURE set_profile_rec_type (
16 p_location_profile_rec IN OUT NOCOPY LOCATION_PROFILE_REC_TYPE
17 ) IS
18
19 -- cursor to get address component when inserting a new row to location profile
20 CURSOR get_address_component(l_location_id NUMBER) IS
21 SELECT hl.country, hl.address1, hl.address2, hl.address3,
22 hl.address4, hl.city, hl.postal_code, hl.state,
23 hl.province, hl.county, hl.actual_content_source,
24 hl.validation_status_code, hl.date_validated
25 FROM hz_locations hl
26 WHERE hl.location_id = l_location_id;
27
28 l_location_profile_rec location_profile_rec_type;
29 db_country HZ_LOCATIONS.COUNTRY%TYPE;
30 db_county HZ_LOCATIONS.COUNTY%TYPE;
31 db_validation_status_code HZ_LOCATIONS.VALIDATION_STATUS_CODE%TYPE;
32 db_date_validated HZ_LOCATIONS.DATE_VALIDATED%TYPE;
33 db_address1 HZ_LOCATIONS.ADDRESS1%TYPE;
34 db_address2 HZ_LOCATIONS.ADDRESS2%TYPE;
35 db_address3 HZ_LOCATIONS.ADDRESS3%TYPE;
36 db_address4 HZ_LOCATIONS.ADDRESS4%TYPE;
37 db_city HZ_LOCATIONS.CITY%TYPE;
38 db_postal_code HZ_LOCATIONS.POSTAL_CODE%TYPE;
39 db_state HZ_LOCATIONS.STATE%TYPE;
40 db_province HZ_LOCATIONS.PROVINCE%TYPE;
41 db_content_source HZ_LOCATIONS.ACTUAL_CONTENT_SOURCE%TYPE;
42
43 BEGIN
44
45 l_location_profile_rec := p_location_profile_rec;
46
47 OPEN get_address_component(l_location_profile_rec.location_id);
48 FETCH get_address_component INTO db_country, db_address1, db_address2, db_address3,
49 db_address4, db_city, db_postal_code, db_state, db_province, db_county,
50 db_content_source, db_validation_status_code, db_date_validated;
51 CLOSE get_address_component;
52
53 IF (l_location_profile_rec.validation_status_code IS NULL) THEN
54 l_location_profile_rec.validation_status_code := db_validation_status_code;
55 END IF;
56 IF (l_location_profile_rec.date_validated IS NULL) THEN
57 l_location_profile_rec.date_validated := db_date_validated;
58 END IF;
59 IF (l_location_profile_rec.address1 IS NULL) THEN
60 l_location_profile_rec.address1 := db_address1;
61 END IF;
62 IF (l_location_profile_rec.address2 IS NULL) THEN
63 l_location_profile_rec.address2 := db_address2;
64 END IF;
65 IF (l_location_profile_rec.address3 IS NULL) THEN
66 l_location_profile_rec.address3 := db_address3;
67 END IF;
68 IF (l_location_profile_rec.address4 IS NULL) THEN
69 l_location_profile_rec.address4 := db_address4;
70 END IF;
71 IF (l_location_profile_rec.city IS NULL) THEN
72 l_location_profile_rec.city := db_city;
73 END IF;
74 IF (l_location_profile_rec.postal_code IS NULL) THEN
75 l_location_profile_rec.postal_code := db_postal_code;
76 END IF;
77 IF (l_location_profile_rec.county IS NULL) THEN
78 l_location_profile_rec.county := db_county;
79 END IF;
80 IF (l_location_profile_rec.country IS NULL) THEN
81 l_location_profile_rec.country := db_country;
82 END IF;
83 IF (l_location_profile_rec.prov_state_admin_code IS NULL) THEN
84 IF (db_state IS NULL) THEN
85 l_location_profile_rec.prov_state_admin_code := db_province;
86 ELSE
87 l_location_profile_rec.prov_state_admin_code := db_state;
88 END IF;
89 END IF;
90
91 p_location_profile_rec := l_location_profile_rec;
92
93 END set_profile_rec_type;
94
95 PROCEDURE create_location_profile (
96 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
97 ,p_location_profile_rec IN location_profile_rec_type
98 ,x_location_profile_id OUT NOCOPY NUMBER
99 ,x_return_status OUT NOCOPY VARCHAR2
100 ,x_msg_count OUT NOCOPY NUMBER
101 ,x_msg_data OUT NOCOPY VARCHAR2
102 ) IS
103 l_location_profile_rec location_profile_rec_type;
104 l_end_date DATE;
105 l_start_date DATE;
106 BEGIN
107
108 savepoint create_location_profile_pub;
109
110 -- initialize message list if p_init_msg_list is set to TRUE.
111 IF FND_API.to_Boolean(p_init_msg_list) THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114
115 --Initialize API return status to success.
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 l_start_date := NVL(p_location_profile_rec.effective_start_date,sysdate);
119 l_end_date := NVL(p_location_profile_rec.effective_end_date,to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI'));
120
121 validate_mandatory_column(
122 p_create_update_flag => 'C'
123 ,p_location_profile_rec => p_location_profile_rec
124 ,x_return_status => x_return_status );
125
126 --Should check if profile already exist
127
128 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
129 RAISE FND_API.G_EXC_ERROR;
130 ELSIF(x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 l_location_profile_rec := p_location_profile_rec;
135
136 IF(l_location_profile_rec.validation_sst_flag = 'N') THEN
137 l_end_date := sysdate;
138 END IF;
139
140 HZ_LOCATION_PROFILES_PKG.Insert_Row (
141 x_location_profile_id => l_location_profile_rec.location_profile_id
142 ,x_location_id => l_location_profile_rec.location_id
143 ,x_actual_content_source => l_location_profile_rec.actual_content_source
144 ,x_effective_start_date => l_start_date
145 ,x_effective_end_date => l_end_date
146 ,x_validation_sst_flag => l_location_profile_rec.validation_sst_flag
147 ,x_validation_status_code => l_location_profile_rec.validation_status_code
148 ,x_date_validated => l_location_profile_rec.date_validated
149 ,x_address1 => l_location_profile_rec.address1
150 ,x_address2 => l_location_profile_rec.address2
151 ,x_address3 => l_location_profile_rec.address3
152 ,x_address4 => l_location_profile_rec.address4
153 ,x_city => l_location_profile_rec.city
154 ,x_postal_code => l_location_profile_rec.postal_code
155 ,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
156 ,x_county => l_location_profile_rec.county
157 ,x_country => l_location_profile_rec.country
158 ,x_object_version_number => 1
159 );
160
161 x_location_profile_id := l_location_profile_rec.location_profile_id;
162
163 -- denormalize validation_status_code to HZ_LOCATIONS
164
165 -- SSM SST Integration and Extension
166 -- Changed the hard coded value of DNB and instead will check if the content source is of type 'PURCHASED'.
167
168 --IF(NOT(l_location_profile_rec.actual_content_source in ('USER_ENTERED','DNB'))) THEN
169 IF(NOT(l_location_profile_rec.actual_content_source = 'USER_ENTERED' OR
170 HZ_UTILITY_V2PUB.is_purchased_content_source(l_location_profile_rec.actual_content_source) = 'Y' )
171 ) THEN
172 UPDATE HZ_LOCATIONS
173 SET date_validated = sysdate
174 , validation_status_code = l_location_profile_rec.validation_status_code
175 , last_update_date = hz_utility_v2pub.last_update_date
176 , last_updated_by = hz_utility_v2pub.last_updated_by
177 , last_update_login = hz_utility_v2pub.last_update_login
178 WHERE location_id = l_location_profile_rec.location_id;
179 END IF;
180
181 EXCEPTION
182
183 WHEN FND_API.G_EXC_ERROR THEN
184 ROLLBACK TO create_location_profile_pub;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 FND_MSG_PUB.Count_And_Get(
187 p_encoded => FND_API.G_FALSE,
188 p_count => x_msg_count,
189 p_data => x_msg_data);
190
191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
192 ROLLBACK TO create_location_profile_pub;
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 FND_MSG_PUB.Count_And_Get(
195 p_encoded => FND_API.G_FALSE,
196 p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 WHEN OTHERS THEN
200 ROLLBACK TO create_location_profile_pub;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
203 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
204 FND_MSG_PUB.ADD;
205 FND_MSG_PUB.Count_And_Get(
206 p_encoded => FND_API.G_FALSE,
207 p_count => x_msg_count,
208 p_data => x_msg_data);
209 END create_location_profile;
210
211 -- This procedure update a record in location profile
212 PROCEDURE update_location_profile (
213 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
214 ,p_location_profile_rec IN location_profile_rec_type
215 -- ,px_object_version_number IN OUT NOCOPY NUMBER
216 ,x_return_status OUT NOCOPY VARCHAR2
217 ,x_msg_count OUT NOCOPY NUMBER
218 ,x_msg_data OUT NOCOPY VARCHAR2
219 ) IS
220 l_object_version_number NUMBER;
221 l_location_profile_rec location_profile_rec_type;
222 l_validation_sst_flag VARCHAR2(1);
223 l_rowid ROWID := NULL;
224 l_dummy VARCHAR2(1);
225 l_maintain_history VARCHAR2(1);
226 l_allow_update_std VARCHAR2(1);
227 l_found_profile VARCHAR2(1);
228 l_end_date DATE;
229 l_orig_sst_flag VARCHAR2(1);
230
231 -- check if there exist profile record for the location from the same content source
232 -- Fix for bug 5189929 - Added .001 to sysdate while looking for active records
233 CURSOR is_profile_exist(l_location_id NUMBER, l_content_source VARCHAR2) IS
234 SELECT 'X'
235 FROM hz_location_profiles
236 WHERE location_id = l_location_id
237 AND actual_content_source = l_content_source
238 AND sysdate+.001 between effective_start_date and nvl(effective_end_date, sysdate)
239 AND rownum = 1;
240
241 -- check if the current location record has been validated
242 CURSOR is_standardized(l_location_id NUMBER) IS
243 SELECT 'X'
244 FROM hz_locations
245 WHERE location_id = l_location_id
246 AND date_validated IS NOT NULL
247 AND validation_status_code IS NOT NULL;
248
249 cursor c_all_active_loc_profiles(c_location_id NUMBER, c_location_profile_id NUMBER) is
250 select location_profile_id, rowid, object_version_number
251 from hz_location_profiles
252 where sysdate between effective_start_date and nvl(effective_end_date, sysdate)
253 and location_id = c_location_id
254 and location_profile_id <> c_location_profile_id;
255
256 l_enddate_other_active VARCHAR2(1);
257 l_enddate DATE;
258 l_startdate DATE;
259
260 BEGIN
261
262 savepoint update_location_profile_pub;
263
264 -- initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean(p_init_msg_list) THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 --Initialize API return status to success.
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 l_maintain_history := nvl(fnd_profile.value('HZ_MAINTAIN_LOC_HISTORY'),'Y');
273 l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
274 l_location_profile_rec := p_location_profile_rec;
275
276 l_end_date := NVL(p_location_profile_rec.effective_end_date,to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI'));
277 l_enddate_other_active := 'N';
278
279 OPEN is_standardized(l_location_profile_rec.location_id);
280 FETCH is_standardized INTO l_dummy;
281 CLOSE is_standardized;
282
283 -- location has been validated before and profile is set to 'N'
284 -- only if validation_sst_flag is not passed
285 IF(l_location_profile_rec.validation_sst_flag IS NULL) THEN
286 IF((l_allow_update_std = 'N') AND (l_dummy IS NOT NULL)) THEN
287 l_validation_sst_flag := 'N';
288 ELSE
289 l_validation_sst_flag := 'Y';
290 END IF;
291 ELSE
292 l_validation_sst_flag := l_location_profile_rec.validation_sst_flag;
293 END IF;
294
295 OPEN is_profile_exist(l_location_profile_rec.location_id,
296 l_location_profile_rec.actual_content_source);
297 FETCH is_profile_exist INTO l_found_profile;
298 CLOSE is_profile_exist;
299
300 validate_mandatory_column(
301 p_create_update_flag => 'U'
302 ,p_location_profile_rec => l_location_profile_rec
303 ,x_return_status => x_return_status );
304
305 -- find the profile of the content source
306 IF(l_found_profile IS NOT NULL) THEN
307 -- not maintain history
308 IF(l_maintain_history = 'N') THEN
309
310 -- Fix for bug 5189929 - Added .001 to sysdate while looking for active records
311 SELECT rowid, object_version_number, validation_sst_flag
312 INTO l_rowid, l_object_version_number, l_orig_sst_flag
313 FROM hz_location_profiles
314 WHERE location_id = l_location_profile_rec.location_id
315 AND actual_content_source = l_location_profile_rec.actual_content_source
316 AND sysdate+.001 between effective_start_date and nvl(effective_end_date,sysdate)
317 AND rownum = 1
318 FOR UPDATE NOWAIT;
319
320 -- only update location profile if the sst flag is set to 'Y'
321 IF(l_validation_sst_flag = 'Y') OR (l_orig_sst_flag = 'N') THEN
322
323 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
324 RAISE FND_API.G_EXC_ERROR;
325 ELSIF(x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329 HZ_LOCATION_PROFILES_PKG.Update_Row(
330 x_rowid => l_rowid
331 ,x_location_profile_id => NULL
332 ,x_location_id => NULL
333 ,x_actual_content_source => NULL
334 ,x_effective_start_date => NULL
335 ,x_effective_end_date => l_end_date
336 ,x_validation_sst_flag => l_validation_sst_flag
337 ,x_validation_status_code => l_location_profile_rec.validation_status_code
338 ,x_date_validated => l_location_profile_rec.date_validated
339 ,x_address1 => l_location_profile_rec.address1
340 ,x_address2 => l_location_profile_rec.address2
341 ,x_address3 => l_location_profile_rec.address3
342 ,x_address4 => l_location_profile_rec.address4
343 ,x_city => l_location_profile_rec.city
344 ,x_postal_code => l_location_profile_rec.postal_code
345 ,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
346 ,x_county => l_location_profile_rec.county
347 ,x_country => l_location_profile_rec.country
348 ,x_object_version_number => nvl(l_object_version_number,1)+1
349 );
350
351 END IF;
352
353 ELSE -- maintain history is 'Y'
354
355 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
356 RAISE FND_API.G_EXC_ERROR;
357 ELSIF(x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 -- need to check the existing sst flag of the location profile
362 -- if Y: do not update existing location profiles
363 -- if N: update existing location profiles
364 -- Fix for bug 5189929 - Added .001 to sysdate while looking for active records
365 SELECT rowid, object_version_number, validation_sst_flag
366 INTO l_rowid, l_object_version_number, l_orig_sst_flag
367 FROM hz_location_profiles
368 WHERE location_id = l_location_profile_rec.location_id
369 AND actual_content_source = l_location_profile_rec.actual_content_source
370 AND sysdate+.001 between effective_start_date and nvl(effective_end_date,sysdate)
371 AND rownum = 1
372 FOR UPDATE NOWAIT;
373
374 -- only end date the existing profile record if validation_sst_flag
375 -- is 'Y' or original sst flag is 'N'
376 -- otherwise, don't update existing profile record and then just
377 -- insert new location profile record. In this case, validation_sst_flag
378 -- is always 'N'.
379 IF(l_validation_sst_flag = 'Y') OR (l_orig_sst_flag = 'N') THEN
380 HZ_LOCATION_PROFILES_PKG.Update_Row(
381 x_rowid => l_rowid
382 ,x_location_profile_id => NULL
383 ,x_location_id => NULL
384 ,x_actual_content_source => NULL
385 ,x_effective_start_date => NULL
386 ,x_effective_end_date => sysdate
387 ,x_validation_sst_flag => NULL
388 ,x_validation_status_code => NULL
389 ,x_date_validated => NULL
390 ,x_address1 => NULL
391 ,x_address2 => NULL
392 ,x_address3 => NULL
393 ,x_address4 => NULL
394 ,x_city => NULL
395 ,x_postal_code => NULL
396 ,x_prov_state_admin_code => NULL
397 ,x_county => NULL
398 ,x_country => NULL
399 ,x_object_version_number => nvl(l_object_version_number,1)+1
400 );
401 ELSE
402 l_end_date := sysdate;
403 END IF;
404
405 -- get database value if caller program pass NULL to address component
406 -- when passing NULL, it means that caller does not want to update the
407 -- value
408 set_profile_rec_type(l_location_profile_rec);
409
410 HZ_LOCATION_PROFILES_PKG.Insert_Row (
411 x_location_profile_id => l_location_profile_rec.location_profile_id
412 ,x_location_id => l_location_profile_rec.location_id
413 ,x_actual_content_source => l_location_profile_rec.actual_content_source
414 ,x_effective_start_date => sysdate
415 ,x_effective_end_date => l_end_date
416 ,x_validation_sst_flag => l_validation_sst_flag
417 ,x_validation_status_code => l_location_profile_rec.validation_status_code
418 ,x_date_validated => l_location_profile_rec.date_validated
419 ,x_address1 => l_location_profile_rec.address1
420 ,x_address2 => l_location_profile_rec.address2
421 ,x_address3 => l_location_profile_rec.address3
422 ,x_address4 => l_location_profile_rec.address4
423 ,x_city => l_location_profile_rec.city
424 ,x_postal_code => l_location_profile_rec.postal_code
425 ,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
426 ,x_county => l_location_profile_rec.county
427 ,x_country => l_location_profile_rec.country
428 ,x_object_version_number => 1
429 );
430
431 END IF;
432
433 ELSE -- cannot find the content source in profile
434
435 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
436 RAISE FND_API.G_EXC_ERROR;
437 ELSIF(x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 END IF;
440
441 -- get database value if caller program pass NULL to address component
442 -- when passing NULL, it means that caller does not want to update the
443 -- value
444 set_profile_rec_type(l_location_profile_rec);
445
446 if l_validation_sst_flag = 'N' then
447 l_enddate_other_active := 'N';
448 l_startdate := sysdate-1;
449 l_enddate := sysdate-1;
450 else
451 l_enddate_other_active := 'Y';
452 l_startdate := sysdate;
453 l_enddate := l_end_date;
454 end if;
455
456 HZ_LOCATION_PROFILES_PKG.Insert_Row (
457 x_location_profile_id => l_location_profile_rec.location_profile_id
458 ,x_location_id => l_location_profile_rec.location_id
459 ,x_actual_content_source => l_location_profile_rec.actual_content_source
460 ,x_effective_start_date => l_startdate
461 ,x_effective_end_date => l_enddate
462 ,x_validation_sst_flag => l_validation_sst_flag
463 ,x_validation_status_code => l_location_profile_rec.validation_status_code
464 ,x_date_validated => l_location_profile_rec.date_validated
465 ,x_address1 => l_location_profile_rec.address1
466 ,x_address2 => l_location_profile_rec.address2
467 ,x_address3 => l_location_profile_rec.address3
468 ,x_address4 => l_location_profile_rec.address4
469 ,x_city => l_location_profile_rec.city
470 ,x_postal_code => l_location_profile_rec.postal_code
471 ,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
472 ,x_county => l_location_profile_rec.county
473 ,x_country => l_location_profile_rec.country
474 ,x_object_version_number => 1
475 );
476
477 END IF;
478
479 -- denormalize validation_status_code to HZ_LOCATIONS only if location can be updated
480
481 -- SSM SST Integration and Extension
482 -- Instead of hard-coding, check if source system is of type PURCHASED.
483
484 --IF(NOT(l_location_profile_rec.actual_content_source in ('USER_ENTERED','DNB'))) THEN
485 IF (NOT(l_location_profile_rec.actual_content_source = 'USER_ENTERED' OR
486 HZ_UTILITY_V2PUB.is_purchased_content_source(l_location_profile_rec.actual_content_source)= 'Y' )
487 )THEN
488 -- if update standardized is ok, then update HZ_LOCATIONS, otherwise do nothing
489 IF(l_validation_sst_flag = 'Y') THEN
490 UPDATE HZ_LOCATIONS
491 SET date_validated = sysdate,
492 validation_status_code = l_location_profile_rec.validation_status_code
493 WHERE location_id = l_location_profile_rec.location_id;
494
495 BEGIN
496 UPDATE HZ_LOCATION_PROFILES
497 SET validation_sst_flag = 'N'
498 WHERE validation_sst_flag = 'Y'
499 AND sysdate between effective_start_date and nvl(effective_end_date, sysdate)
500 /* SSM SST Integration and Extension
501 * Removed the hard-coded value of DNB and will instead check if the
502 * source system is not of type PURCHASED.
503
504 AND actual_content_source <> l_location_profile_rec.actual_content_source
505 AND actual_content_source not in ('USER_ENTERED','DNB');*/
506
507 AND actual_content_source NOT IN ( l_location_profile_rec.actual_content_source, 'USER_ENTERED')
508 AND HZ_UTILITY_V2PUB.is_purchased_content_source(actual_content_source) = 'N'
509 AND location_id = l_location_profile_rec.location_id;
510
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 NULL;
514 END;
515 END IF;
516 ELSE -- nullify if actual_content_source in ('USER_ENTERED', 'DNB')
517 IF(l_validation_sst_flag = 'Y') THEN
518 UPDATE HZ_LOCATIONS
519 SET date_validated = null,
520 validation_status_code = null
521 WHERE location_id = l_location_profile_rec.location_id;
522 END IF;
523 END IF;
524
525 IF (l_enddate_other_active = 'Y') then
526 for l_all_active_loc_profiles in c_all_active_loc_profiles(l_location_profile_rec.location_id,l_location_profile_rec.location_profile_id) loop
527 HZ_LOCATION_PROFILES_PKG.Update_Row(
528 x_rowid => l_all_active_loc_profiles.rowid
529 ,x_location_profile_id => l_all_active_loc_profiles.location_profile_id
530 ,x_location_id => NULL
531 ,x_actual_content_source => NULL
532 ,x_effective_start_date => NULL
533 ,x_effective_end_date => sysdate
534 ,x_validation_sst_flag => NULL
535 ,x_validation_status_code => NULL
536 ,x_date_validated => NULL
537 ,x_address1 => NULL
538 ,x_address2 => NULL
539 ,x_address3 => NULL
540 ,x_address4 => NULL
541 ,x_city => NULL
542 ,x_postal_code => NULL
543 ,x_prov_state_admin_code => NULL
544 ,x_county => NULL
545 ,x_country => NULL
546 ,x_object_version_number => nvl(l_all_active_loc_profiles.object_version_number,1)+1
547 );
548 end loop;
549 END IF;
550
551 EXCEPTION
552 WHEN FND_API.G_EXC_ERROR THEN
553 ROLLBACK TO update_location_profile_pub;
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 FND_MSG_PUB.Count_And_Get(
556 p_encoded => FND_API.G_FALSE,
557 p_count => x_msg_count,
558 p_data => x_msg_data);
559
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK TO update_location_profile_pub;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 FND_MSG_PUB.Count_And_Get(
564 p_encoded => FND_API.G_FALSE,
565 p_count => x_msg_count,
566 p_data => x_msg_data);
567
568 WHEN OTHERS THEN
569 ROLLBACK TO update_location_profile_pub;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
572 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
573 FND_MSG_PUB.ADD;
574 FND_MSG_PUB.Count_And_Get(
575 p_encoded => FND_API.G_FALSE,
576 p_count => x_msg_count,
577 p_data => x_msg_data);
578 END update_location_profile;
579
580 PROCEDURE validate_mandatory_column (
581 p_create_update_flag IN VARCHAR2,
582 p_location_profile_rec IN location_profile_rec_type,
583 x_return_status IN OUT NOCOPY VARCHAR2
584 ) IS
585
586 l_dummy VARCHAR2(1);
587 l_actual_content_source VARCHAR2(30);
588 l_location_id NUMBER;
589 l_location_profile_id NUMBER;
590 l_address1 VARCHAR2(240);
591 l_effective_start_date DATE;
592 l_validation_sst_flag VARCHAR2(1);
593 l_country VARCHAR2(2);
594
595 CURSOR check_lookup(l_lookup_code VARCHAR2, l_lookup_type VARCHAR2) IS
596 select 'X'
597 from AR_LOOKUPS
598 where lookup_type = l_lookup_type
599 and lookup_code = l_lookup_code;
600
601 BEGIN
602
603 l_actual_content_source := p_location_profile_rec.actual_content_source;
604 l_location_profile_id := p_location_profile_rec.location_profile_id;
605 l_location_id := p_location_profile_rec.location_id;
606 l_address1 := p_location_profile_rec.address1;
607 l_country := p_location_profile_rec.country;
608 l_effective_start_date := p_location_profile_rec.effective_start_date;
609 l_validation_sst_flag := p_location_profile_rec.validation_sst_flag;
610
611 IF(l_location_id IS NULL OR l_location_id = FND_API.G_MISS_NUM) THEN
612 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
613 FND_MESSAGE.SET_TOKEN('COLUMN' ,'LOCATION_ID');
614 FND_MSG_PUB.ADD;
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617
618 IF (p_create_update_flag = 'U') THEN
619 IF(l_actual_content_source = FND_API.G_MISS_CHAR) THEN
620 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
621 FND_MESSAGE.SET_TOKEN('COLUMN' ,'ACTUAL_CONTENT_SOURCE');
622 FND_MSG_PUB.ADD;
623 RAISE FND_API.G_EXC_ERROR;
624 END IF;
625
626 IF(l_address1 = FND_API.G_MISS_CHAR) THEN
627 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
628 FND_MESSAGE.SET_TOKEN('COLUMN' ,'ADDRESS1');
629 FND_MSG_PUB.ADD;
630 RAISE FND_API.G_EXC_ERROR;
631 END IF;
632
633 IF(l_country = FND_API.G_MISS_CHAR) THEN
634 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
635 FND_MESSAGE.SET_TOKEN('COLUMN' ,'COUNTRY');
636 FND_MSG_PUB.ADD;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639 ELSIF (p_create_update_flag = 'C') THEN
640 IF(l_actual_content_source IS NULL OR l_actual_content_source = FND_API.G_MISS_CHAR) THEN
641 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
642 FND_MESSAGE.SET_TOKEN('COLUMN' ,'ACTUAL_CONTENT_SOURCE');
643 FND_MSG_PUB.ADD;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 IF(l_address1 IS NULL OR l_address1 = FND_API.G_MISS_CHAR) THEN
648 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
649 FND_MESSAGE.SET_TOKEN('COLUMN' ,'ADDRESS1');
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653
654 IF(l_country IS NULL OR l_country = FND_API.G_MISS_CHAR) THEN
655 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
656 FND_MESSAGE.SET_TOKEN('COLUMN' ,'COUNTRY');
657 FND_MSG_PUB.ADD;
658 RAISE FND_API.G_EXC_ERROR;
659 END IF;
660 END IF;
661
662 EXCEPTION
663 WHEN FND_API.G_EXC_ERROR THEN
664 x_return_status := FND_API.G_RET_STS_ERROR;
665
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668
669 WHEN OTHERS THEN
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
671 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
672 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
673 FND_MSG_PUB.ADD;
674 END validate_mandatory_column;
675
676 PROCEDURE set_effective_end_date (
677 p_location_profile_id IN NUMBER
678 ,x_return_status IN OUT NOCOPY VARCHAR2
679 ) IS
680
681 l_dummy VARCHAR2(1);
682
683 CURSOR is_profile_exist(l_location_profile_id NUMBER) IS
684 SELECT 'X'
685 FROM HZ_LOCATION_PROFILES
686 WHERE location_profile_id = l_location_profile_id;
687
688 BEGIN
689
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691
692 OPEN is_profile_exist(p_location_profile_id);
693 FETCH is_profile_exist INTO l_dummy;
694 CLOSE is_profile_exist;
695
696 IF(l_dummy IS NOT NULL) THEN
697 BEGIN
698 UPDATE hz_location_profiles
699 SET effective_end_date = sysdate
700 WHERE location_profile_id = p_location_profile_id;
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
705 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
706 FND_MSG_PUB.ADD;
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END;
709 ELSE
710 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
711 FND_MESSAGE.SET_TOKEN( 'RECORD', 'Location Profile' );
712 FND_MESSAGE.SET_TOKEN( 'VALUE', p_location_profile_id);
713 FND_MSG_PUB.ADD;
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
717 EXCEPTION
718 WHEN FND_API.G_EXC_ERROR THEN
719 x_return_status := FND_API.G_RET_STS_ERROR;
720
721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
723
724 WHEN OTHERS THEN
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
727 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
728 FND_MSG_PUB.ADD;
729
730 END set_effective_end_date;
731
732 --
733 -- This procedure will update both HZ_LOCATIONS.validation_status_code
734 -- and HZ_LOCATION_PROFILES.validation_status_code
735 -- This procedure should not be invoked along. It should be called
736 -- after creating or updating location. It is required for those
737 -- content source which is NOT USER_ENTERED.
738 -- The reason is that when creating/updating location, there is no
739 -- parameter to pass validation_status_code in HZ_LOCATIONS_V2PUB
740 -- api. As location is being created/updated, location profile
741 -- will also be created/updated base on the profile
742 -- HZ_MAINTAIN_LOC_HISTORY and whether there is an existing
743 -- active profile record for that content source.
744 -- Therefore, after location profile is being created/updated,
745 -- the caller program that calls HZ_LOCATIONS_V2PUB should also
746 -- set the validation_status_code appropriately if the actual_content_source
747 -- passed in is NOT USER_ENTERED record.
748 --
749 PROCEDURE set_validation_status_code (
750 p_location_profile_id IN NUMBER
751 ,p_validation_status_code IN VARCHAR2
752 ,x_return_status IN OUT NOCOPY VARCHAR2
753 ) IS
754
755 l_dummy VARCHAR2(1);
756
757 CURSOR is_profile_exist(l_location_profile_id NUMBER) IS
758 SELECT 'X'
759 FROM HZ_LOCATION_PROFILES
760 WHERE location_profile_id = l_location_profile_id;
761
762 BEGIN
763
764 x_return_status := FND_API.G_RET_STS_SUCCESS;
765
766 OPEN is_profile_exist(p_location_profile_id);
767 FETCH is_profile_exist INTO l_dummy;
768 CLOSE is_profile_exist;
769
770 IF(l_dummy IS NOT NULL) THEN
771 BEGIN
772 UPDATE hz_location_profiles
773 SET validation_status_code = p_validation_status_code
774 WHERE location_profile_id = p_location_profile_id;
775
776 UPDATE hz_locations
777 SET validation_status_code = p_validation_status_code
778 WHERE location_id =
779 ( SELECT location_id
780 FROM HZ_LOCATION_PROFILES
781 WHERE location_profile_id = p_location_profile_id);
782 EXCEPTION
783 WHEN OTHERS THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
786 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
787 FND_MSG_PUB.ADD;
788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
789 END;
790 ELSE
791 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
792 FND_MESSAGE.SET_TOKEN( 'RECORD', 'Location Profile' );
793 FND_MESSAGE.SET_TOKEN( 'VALUE', p_location_profile_id);
794 FND_MSG_PUB.ADD;
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797
798 EXCEPTION
799 WHEN FND_API.G_EXC_ERROR THEN
800 x_return_status := FND_API.G_RET_STS_ERROR;
801
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804
805 WHEN OTHERS THEN
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
808 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
809 FND_MSG_PUB.ADD;
810
811 END set_validation_status_code;
812
813 PROCEDURE log(
814 message IN VARCHAR2,
815 newline IN BOOLEAN DEFAULT TRUE
816 ) IS
817 BEGIN
818 IF message = 'NEWLINE' THEN
819 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
820 ELSIF (newline) THEN
821 FND_FILE.put_line(fnd_file.log,message);
822 ELSE
823 FND_FILE.put(fnd_file.log,message);
824 END IF;
825 END log;
826
827 /*-----------------------------------------------------------------------
828 | Function to fetch messages of the stack and log the error
829 | Also returns the error
830 |-----------------------------------------------------------------------*/
831 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
832 RETURN VARCHAR2 IS
833 l_msg_data VARCHAR2(2000);
834 BEGIN
835 FND_MSG_PUB.Reset;
836
837 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
838 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
839 END LOOP;
840 IF (SQLERRM IS NOT NULL) THEN
841 l_msg_data := l_msg_data || SQLERRM;
842 END IF;
843 log(l_msg_data);
844 RETURN l_msg_data;
845 END logerror;
846
847 END HZ_LOCATION_PROFILE_PVT;