[Home] [Help]
PACKAGE BODY: APPS.CSF_RESOURCE_ADDRESS_PVT
Source
1 PACKAGE BODY csf_resource_address_pvt AS
2 /* $Header: CSFVADRB.pls 120.13.12010000.7 2008/11/18 08:44:51 ppillai ship $ */
3
4 g_debug VARCHAR2(1);
5 g_debug_level NUMBER;
6
7 g_emp_res_query CONSTANT VARCHAR2(1500) :=
8 ' SELECT p.party_id
9 , s.party_site_id
10 , l.location_id
11 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
12 , l.address1 street
13 , l.postal_code
14 , l.city
15 , l.state
16 , l.country
17 , t.territory_short_name
18 , l.geometry
19 , s.start_date_active
20 , s.end_date_active
21 FROM jtf_rs_resource_extns_vl r
22 , per_people_f pf
23 , hz_parties p
24 , hz_party_sites s
25 , hz_locations l
26 , fnd_territories_vl t
27 WHERE r.resource_id = :resource_id
28 AND pf.person_id = r.source_id
29 AND pf.party_id = p.party_id (+)
30 AND p.party_id = s.party_id (+)
31 AND NVL(s.status, ''A'') = ''A''
32 AND s.location_id = l.location_id (+)
33 AND l.country = t.territory_code(+)
34 ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
35
36 g_party_res_query CONSTANT VARCHAR2(1500) :=
37 ' SELECT r.source_id party_id
38 , s.party_site_id
39 , l.location_id
40 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
41 , l.address1 street
42 , l.postal_code
43 , l.city
44 , l.state
45 , l.country
46 , t.territory_short_name
47 , l.geometry
48 , s.start_date_active
49 , s.end_date_active
50 FROM jtf_rs_resource_extns_vl r
51 , hz_party_sites s
52 , hz_locations l
53 , fnd_territories_vl t
54 WHERE r.resource_id = :resource_id
55 AND r.source_id = s.party_id (+)
56 AND NVL(s.status, ''A'') = ''A''
57 AND s.location_id = l.location_id (+)
58 AND l.country = t.territory_code(+)
59 ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
60
61 g_other_res_query CONSTANT VARCHAR2(1000) :=
62 ' SELECT p.party_id
63 , s.party_site_id
64 , l.location_id
65 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
66 , l.address1 street
67 , l.postal_code
68 , l.city
69 , l.state
70 , l.country
71 , t.territory_short_name
72 , l.geometry
73 , s.start_date_active
74 , s.end_date_active
75 FROM hz_parties p
76 , hz_party_sites s
77 , hz_locations l
78 , fnd_territories_vl t
79 WHERE p.person_last_name = :res_type_id_string
80 AND p.person_first_name = :dep_arr_party_name
81 AND s.party_id = p.party_id
82 AND l.location_id = s.location_id
83 AND l.country = t.territory_code
84 ORDER BY s.last_update_date DESC';
85
86
87
88 PROCEDURE init_package IS
89 BEGIN
90 g_debug := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
91 g_debug_level := NVL(fnd_profile.value('AFLOG_LEVEL'), fnd_log.level_event);
92 END init_package;
93
94 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
95 BEGIN
96 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
97 IF fnd_file.log > 0 THEN
98 IF p_message = ' ' THEN
99 fnd_file.put_line(fnd_file.log, '');
100 ELSE
101 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
102 END IF;
103 ELSE
104 fnd_log.string(p_level, 'csf.plsql.CSF_RESOURCE_ADDRESS_PVT.' || p_module, p_message);
105 END IF;
106 END IF;
107 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
108 END debug;
109
110 /**
111 * Finds out whether the passed value is a Number or not.
112 */
113 FUNCTION is_number(p_num_char IN VARCHAR2) RETURN BOOLEAN AS
114 n NUMBER;
115 BEGIN
116 IF p_num_char IS NULL THEN
117 RETURN FALSE;
118 END IF;
119 n := to_number(p_num_char);
120 RETURN TRUE;
121 EXCEPTION
122 WHEN OTHERS THEN
123 RETURN FALSE;
124 END is_number;
125
126 /**
127 * This function finds out whether the first word is a Building Number or not.
128 * This is called if Address Line 2, 3 or 4 is also filled apart from Address Line 1.
129 * The logic followed is exactly as given in BuildingNum.isBuildingNumber (BuildingNum.java).
130 */
131 FUNCTION is_address_line_valid(p_address_line IN VARCHAR2, p_country_code VARCHAR2)
132 RETURN BOOLEAN IS
133 l_address_line hz_locations.address1%TYPE;
134 l_first_word hz_locations.address1%TYPE;
135 l_count_words NUMBER;
136 l_sep_index NUMBER;
137 BEGIN
138 l_address_line := trim(p_address_line);
139
140 -- Trim off multiple spaces inbetween
141 WHILE INSTR(l_address_line, ' ') <> 0 LOOP
142 l_address_line := REPLACE(l_address_line, ' ', ' ');
143 END LOOP;
144
145 -- Count the number of words
146 l_count_words := LENGTH(l_address_line) - LENGTH(REPLACE(l_address_line, ' ')) + 1;
147
148 IF p_country_code = 'US' THEN
149 IF l_count_words > 1 THEN
150 l_first_word := SUBSTR(l_address_line, 1, INSTR(l_address_line, ' ')-1);
151
152 -- Building Number in Numeric Format
153 IF is_number(l_first_word) THEN
154 RETURN TRUE;
155 END IF;
156
157 IF LENGTH(l_first_word) = 1 THEN -- One Letter word and not a number
158 RETURN FALSE;
159 END IF;
160
161 -- Xnum Format
162 IF is_number(SUBSTR(l_first_word, 2)) THEN
163 RETURN TRUE;
164 END IF;
165
166 -- numX Format
167 IF is_number(SUBSTR(l_first_word, 1, LENGTH(l_first_word) - 1)) THEN
168 RETURN TRUE;
169 END IF;
170
171 -- numXnum or XnumXnum Format
172 IF NOT is_number(SUBSTR(l_first_word, 1, 1)) THEN -- XnumXnum Format
173 l_first_word := SUBSTR(l_first_word, 2); -- Becomes numXnum Format
174 END IF;
175
176 l_sep_index := INSTR(
177 l_first_word
178 , REPLACE(TRANSLATE(l_first_word, '0123456789', '0'), '0')
179 );
180
181 -- Since the First Character is already removed, the first character
182 -- shouldnt be an alphabet Similarly last shouldnt be a character
183 IF l_sep_index = 1 OR l_sep_index = LENGTH(l_first_word) THEN
184 RETURN FALSE;
185 END IF;
186
187 l_first_word := SUBSTR(l_first_word, 1, l_sep_index - 1)
188 || SUBSTR(l_first_word, l_sep_index + 1);
189
190 IF is_number(l_first_word) THEN
191 RETURN TRUE;
192 END IF;
193 END IF;
194 END IF;
195
196 RETURN FALSE;
197 END is_address_line_valid;
198
199 FUNCTION choose_address_line(
200 p_address1 IN VARCHAR2
201 , p_address2 IN VARCHAR2 DEFAULT NULL
202 , p_address3 IN VARCHAR2 DEFAULT NULL
203 , p_address4 IN VARCHAR2 DEFAULT NULL
204 , p_country_code IN VARCHAR2
205 ) RETURN VARCHAR2 IS
206 BEGIN
207 IF NVL(p_address4, '_') <> '_' AND is_address_line_valid(p_address4, p_country_code) THEN
208 RETURN p_address4;
209 ELSIF NVL(p_address3, '_') <> '_' AND is_address_line_valid(p_address3, p_country_code) THEN
210 RETURN p_address3;
211 ELSIF NVL(p_address2, '_') <> '_' AND is_address_line_valid(p_address2, p_country_code) THEN
212 RETURN p_address2;
213 ELSE
214 RETURN p_address1;
215 END IF;
216 END choose_address_line;
217
218 PROCEDURE resolve_address(
219 p_api_version IN NUMBER
220 , p_init_msg_list IN VARCHAR2
221 , p_commit IN VARCHAR2
222 , x_return_status OUT NOCOPY VARCHAR2
223 , x_msg_count OUT NOCOPY NUMBER
224 , x_msg_data OUT NOCOPY VARCHAR2
225 , p_location_id IN NUMBER
226 , p_building_num IN VARCHAR2
227 , p_address1 IN VARCHAR2
228 , p_address2 IN VARCHAR2
229 , p_address3 IN VARCHAR2
230 , p_address4 IN VARCHAR2
231 , p_city IN VARCHAR2
232 , p_state IN VARCHAR2
233 , p_postalcode IN VARCHAR2
234 , p_county IN VARCHAR2
235 , p_province IN VARCHAR2
236 , p_country IN VARCHAR2
237 , p_country_code IN VARCHAR2
238 , p_alternate IN VARCHAR2
239 , p_update_address IN VARCHAR2
240 , x_geometry OUT NOCOPY mdsys.sdo_geometry
241 ) IS
242 l_api_name CONSTANT VARCHAR2(50) := 'RESOLVE_ADDRESS';
243 l_api_version CONSTANT NUMBER := 1.0;
244 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
245
246 l_resultarray csf_lf_pub.csf_lf_resultarray;
247 l_update_addr BOOLEAN;
248 l_update_geo BOOLEAN;
249 l_call_lf BOOLEAN;
250 l_roadname hz_locations.address1%TYPE;
251 l_location_ovn NUMBER;
252 l_location_rec hz_location_v2pub.location_rec_type;
253 l_road VARCHAR2(200);
254
255 CURSOR c_location_locking_info IS
256 SELECT object_version_number
257 FROM HZ_LOCATIONS
258 WHERE LOCATION_ID = p_location_id;
259
260 BEGIN
261 SAVEPOINT resolve_address_pub;
262
263 -- Check for API Compatibility
264 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
265 RAISE fnd_api.g_exc_unexpected_error;
266 END IF;
267
268 -- Initialize Message Stack if required
269 IF fnd_api.to_boolean(p_init_msg_list) THEN
270 fnd_msg_pub.initialize;
271 END IF;
272
273 -- Initialize Return Status
274 x_return_status := fnd_api.g_ret_sts_success;
275
276 IF l_debug THEN
277 debug('Resolving the Address corresponding to Location #' || p_location_id, l_api_name, fnd_log.level_procedure);
278 debug(' --> Address1 = ' || p_address1, l_api_name, fnd_log.level_statement);
279 debug(' --> City = ' || p_city, l_api_name, fnd_log.level_statement);
280 debug(' --> State = ' || p_state, l_api_name, fnd_log.level_statement);
281 debug(' --> Zip = ' || p_postalcode, l_api_name, fnd_log.level_statement);
282 debug(' --> Country = ' || p_country, l_api_name, fnd_log.level_statement);
283 debug(' --> Country Code = ' || p_country_code, l_api_name, fnd_log.level_statement);
284 debug(' --> Update Addr = ' || p_update_address, l_api_name, fnd_log.level_statement);
285 END IF;
286
287 l_update_addr := NVL(fnd_api.to_boolean(p_update_address), FALSE);
288 l_update_geo := FALSE;
289
290 -- Location Finder Profiles. Check whether Resolve Address needs to be called.
291 IF l_debug THEN
292 debug('CSF: Location Finder Installed = ' || fnd_profile.VALUE('CSF_LF_INSTALLED'), l_api_name, fnd_log.level_statement);
293 debug('CSR: Create Location = ' || fnd_profile.VALUE('CREATELOCATION'), l_api_name, fnd_log.level_statement);
294 END IF;
295
296 l_call_lf := (NVL(fnd_profile.VALUE('CSF_LF_INSTALLED'),'N') = 'Y')
297 AND (NVL(fnd_profile.VALUE('CREATELOCATION'),'N') = 'Y');
298
299 IF l_call_lf THEN
300 IF NVL(p_address4,'_') <> '_' AND is_address_line_valid(p_address4, p_country_code) THEN
301 l_roadname := p_address4;
302 ELSIF NVL(p_address3,'_') <> '_' AND is_address_line_valid(p_address3, p_country_code) THEN
303 l_roadname := p_address3;
304 ELSIF NVL(p_address2,'_') <> '_' AND is_address_line_valid(p_address2, p_country_code) THEN
305 l_roadname := p_address2;
306 ELSE
307 l_roadname := p_address1;
308 END IF;
309
310 csf_lf_pub.csf_lf_resolveaddress(
311 p_api_version => l_api_version
312 , x_return_status => x_return_status
313 , x_msg_count => x_msg_count
314 , x_msg_data => x_msg_data
315 , p_country => NVL(p_country, '_')
316 , p_state => NVL(p_state, '_')
317 , p_county => NVL(p_county, '_')
318 , p_province => NVL(p_province, '_')
319 , p_city => NVL(p_city, '_')
320 , p_postalcode => NVL(p_postalcode, '_')
321 , p_roadname => NVL(l_roadname, '_')
322 , p_buildingnum => NVL(p_building_num, '_')
323 , p_alternate => NVL(l_roadname, '_')
324 , x_resultsarray => l_resultarray
325 );
326
327 IF x_return_status <> fnd_api.g_ret_sts_success THEN
328 IF l_debug THEN
329 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
330 debug('CSF_LF_PUB.RESOLVE returned error: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
331 END IF;
332 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
333 RAISE fnd_api.g_exc_unexpected_error;
334 END IF;
335 RAISE fnd_api.g_exc_error;
336 END IF;
337
338 IF (l_resultarray IS NULL OR l_resultarray.COUNT > 1) THEN
339 IF l_debug THEN
340 debug('CSF_LF_PUB.RESOLVE didnt return proper result.. So Error', l_api_name, fnd_log.level_error);
341 END IF;
342 RAISE fnd_api.g_exc_error;
343 END IF;
344
345 x_geometry := l_resultarray(1).locus;
346 l_update_geo := TRUE;
347 l_update_addr:= TRUE;
348 IF(l_resultarray(1).buildingnum = '_') THEN
349 l_road := initcap(l_resultarray(1).road);
350 ELSE
351 l_road := l_resultarray(1).buildingnum || ' ' || initcap(l_resultarray(1).road);
352 END IF;
353
354 IF l_debug THEN
355 debug(' --> Longitude = ' || x_geometry.sdo_ordinates(1), l_api_name, fnd_log.level_statement);
356 debug(' --> Latitude = ' || x_geometry.sdo_ordinates(2), l_api_name, fnd_log.level_statement);
357 debug(' --> Segment = ' || x_geometry.sdo_ordinates(5), l_api_name, fnd_log.level_statement);
358 END IF;
359 END IF;
360
361
362 IF l_update_addr THEN
366 IF l_debug THEN
363 --l_location_rec.address1 := p_address1;
364 IF p_address1 = l_roadname THEN
365 l_location_rec.address1 := l_road;
367 debug('Considering the address1 for resolving street. Modified address1 = ' || l_location_rec.address1, l_api_name, fnd_log.level_procedure);
368 END IF;
369 ELSIF p_address2 = l_roadname THEN
370 l_location_rec.address2 := l_road;
371 IF l_debug THEN
372 debug('Considering the address2 for resolving street. Modified address2 = ' || l_location_rec.address2, l_api_name, fnd_log.level_procedure);
373 END IF;
374 ELSIF p_address3 = l_roadname THEN
375 l_location_rec.address3 := l_road;
376 IF l_debug THEN
377 debug('Considering the address3 for resolving street. Modified address3 = ' || l_location_rec.address3, l_api_name, fnd_log.level_procedure);
378 END IF;
379 ELSIF p_address4 = l_roadname THEN
380 l_location_rec.address4 := l_road;
381 IF l_debug THEN
382 debug('Considering the address4 for resolving street. Modified address4 = ' || l_location_rec.address4, l_api_name, fnd_log.level_procedure);
383 END IF;
384 END IF;
385 IF p_city = '_' THEN
386 l_location_rec.city := '';
387 ELSE
388 l_location_rec.city := p_city;
389 END IF;
390
391 IF p_state = '_' THEN
392 l_location_rec.state := '';
393 ELSE
394 l_location_rec.state := p_state;
395 END IF;
396
397 IF p_postalcode = '_' THEN
398 l_location_rec.postal_code := '';
399 ELSE
400 l_location_rec.postal_code := p_postalcode;
401 END IF;
402
403 l_location_rec.country := p_country_code;
404 END IF;
405
406 IF l_update_geo THEN
407 l_location_rec.geometry := x_geometry;
408 END IF;
409
410 IF l_update_addr OR l_update_geo THEN
411 IF l_debug THEN
412 debug('Updating Address ', l_api_name, fnd_log.level_statement);
413 END IF;
414
415 l_location_rec.location_id := p_location_id;
416 l_location_rec.created_by_module := null;
417
418 OPEN c_location_locking_info;
419 FETCH c_location_locking_info INTO l_location_ovn;
420 CLOSE c_location_locking_info;
421
422 -- Updating the location record (it updates both hz_parties and
423 -- hz_locations)
424 hz_location_v2pub.update_location(
425 p_location_rec => l_location_rec
426 , p_object_version_number => l_location_ovn
427 , x_return_status => x_return_status
428 , x_msg_count => x_msg_count
429 , x_msg_data => x_msg_data );
430
431 END IF;
432
433 IF fnd_api.to_boolean(p_commit) THEN
434 COMMIT;
435 END IF;
436
437 EXCEPTION
438 WHEN fnd_api.g_exc_error THEN
439 IF l_debug THEN
440 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
441 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
442 END IF;
443
444 ROLLBACK TO resolve_address_pub;
445 x_return_status := fnd_api.g_ret_sts_error;
446 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
447 WHEN fnd_api.g_exc_unexpected_error THEN
448 IF l_debug THEN
449 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
450 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
451 END IF;
452
453 ROLLBACK TO resolve_address_pub;
454 x_return_status := fnd_api.g_ret_sts_unexp_error;
455 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
456 WHEN OTHERS THEN
457 IF l_debug THEN
458 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
459 END IF;
460
461 x_return_status := fnd_api.g_ret_sts_unexp_error;
462 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
463 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
464 END IF;
465 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
466 ROLLBACK TO resolve_address_pub;
467 END resolve_address;
468
469 FUNCTION are_addresses_equal(
470 p_address1 address_rec_type
471 , p_address2 address_rec_type
472 )
473 RETURN BOOLEAN IS
474 l_api_name CONSTANT VARCHAR2(50) := 'ARE_ADDRESSES_EQUAL';
475 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
476 BEGIN
477 IF l_debug THEN
478 debug('Checking for the Equality of two addresses', l_api_name, fnd_log.level_procedure);
479 debug('Address#1', l_api_name, fnd_log.level_statement);
480 debug(' --> Street : ' || p_address1.street, l_api_name, fnd_log.level_statement);
481 debug(' --> City : ' || p_address1.city, l_api_name, fnd_log.level_statement);
482 debug(' --> State : ' || p_address1.state, l_api_name, fnd_log.level_statement);
483 debug(' --> Zip : ' || p_address1.postal_code, l_api_name, fnd_log.level_statement);
484 debug(' --> Country : ' || p_address1.country, l_api_name, fnd_log.level_statement);
485 debug(' --> Terr SN : ' || p_address1.territory_short_name, l_api_name, fnd_log.level_statement);
486 debug('Address#2', l_api_name, fnd_log.level_statement);
490 debug(' --> Zip : ' || p_address2.postal_code, l_api_name, fnd_log.level_statement);
487 debug(' --> Street : ' || p_address2.street, l_api_name, fnd_log.level_statement);
488 debug(' --> City : ' || p_address2.city, l_api_name, fnd_log.level_statement);
489 debug(' --> State : ' || p_address2.state, l_api_name, fnd_log.level_statement);
491 debug(' --> Country : ' || p_address2.country, l_api_name, fnd_log.level_statement);
492 debug(' --> Terr SN : ' || p_address2.territory_short_name, l_api_name, fnd_log.level_statement);
493 END IF;
494
495 IF NVL(UPPER(p_address1.street), '@#$') <> NVL(UPPER(p_address2.street), '@#$') THEN
496 RETURN FALSE;
497 END IF;
498
499 IF NVL(p_address1.postal_code, '@#$') <> NVL(p_address2.postal_code, '@#$') THEN
500 RETURN FALSE;
501 END IF;
502
503 IF NVL(UPPER(p_address1.city), '@#$') <> NVL(UPPER(p_address2.city), '@#$') THEN
504 RETURN FALSE;
505 END IF;
506
507 IF NVL(UPPER(p_address1.state), '@#$') <> NVL(UPPER(p_address2.state), '@#$') THEN
508 RETURN FALSE;
509 END IF;
510
511 -- Value of country might be in Full Form or Short Form
512 IF ( NVL(UPPER(p_address1.country), '@#$') <> NVL(UPPER(p_address2.country), '@#$')
513 AND NVL(UPPER(p_address1.territory_short_name), '@#$') <> NVL(UPPER(p_address2.territory_short_name), '@#$')
514 AND NVL(UPPER(p_address1.country), '@#$') <> NVL(UPPER(p_address2.territory_short_name), '@#$')
515 AND NVL(UPPER(p_address1.territory_short_name), '@#$') <> NVL(UPPER(p_address2.country), '@#$') )
516 THEN
517 RETURN FALSE;
518 END IF;
519
520 IF l_debug THEN
521 debug('Addresses are equal', l_api_name, fnd_log.level_statement);
522 END IF;
523 RETURN TRUE;
524 END are_addresses_equal;
525
526 /**
527 * Get the Party Site Addresses for the Parties created for the Resource.
528 */
529 PROCEDURE get_party_addresses(
530 p_resource_id IN NUMBER
531 , p_resource_type IN VARCHAR2
532 , p_date IN DATE
533 , x_address_tbl OUT NOCOPY address_tbl_type
534 ) IS
535 l_api_name CONSTANT VARCHAR2(50) := 'GET_PARTY_ADDRESSES';
536 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
537
538 TYPE ref_cursor_type IS REF CURSOR;
539 c_parties ref_cursor_type;
540 l_address_rec address_rec_type;
541 BEGIN
542 IF l_debug THEN
543 debug('Finding the Parties associated with Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
544 END IF;
545
546 x_address_tbl := address_tbl_type();
547
548 -- Find out whether we can use the Parties already created by the Source Module
549 IF p_resource_type IN ('RS_EMPLOYEE', 'RS_PARTY') THEN
550 IF p_resource_type = 'RS_EMPLOYEE' THEN
551 OPEN c_parties FOR g_emp_res_query USING p_resource_id;
552 ELSIF p_resource_type = 'RS_PARTY' THEN
553 OPEN c_parties FOR g_party_res_query USING p_resource_id;
554 END IF;
555
556 LOOP
557 FETCH c_parties INTO l_address_rec;
558 EXIT WHEN c_parties%NOTFOUND;
559 x_address_tbl.extend();
560 x_address_tbl(c_parties%ROWCOUNT) := l_address_rec;
561 END LOOP;
562 CLOSE c_parties;
563
564 IF l_debug THEN
565 debug(' Number of Parties found = ' || x_address_tbl.COUNT, l_api_name, fnd_log.level_statement);
566 END IF;
567
568 END IF;
569
570 IF x_address_tbl.COUNT > 0 THEN
571 RETURN;
572 END IF;
573
574 -- No Parties created by the Source Module were fetched.
575 -- Search for Parties created by this Module.
576
577 OPEN c_parties FOR g_other_res_query USING (p_resource_type || ' ' || p_resource_id), g_st_party_fname;
578 LOOP
579 FETCH c_parties INTO l_address_rec;
580 EXIT WHEN c_parties%NOTFOUND;
581 x_address_tbl.extend();
582 x_address_tbl(c_parties%ROWCOUNT) := l_address_rec;
583 END LOOP;
584 CLOSE c_parties;
585
586 IF l_debug THEN
587 debug(' Number of Parties found upon retrying = ' || x_address_tbl.COUNT, l_api_name, fnd_log.level_statement);
588 END IF;
589
590 END get_party_addresses;
591
592 /**
593 * Get the Home Addresses of the Resource as defined in HRMS People
594 * Management form.
595 */
596 PROCEDURE get_home_addresses(
597 p_resource_id IN NUMBER
598 , p_resource_type IN VARCHAR2
599 , p_date IN DATE
600 , x_address OUT NOCOPY address_rec_type
601 ) IS
602 l_api_name CONSTANT VARCHAR2(50) := 'GET_HOME_ADDRESSES';
603 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
604
605 CURSOR c_home_addresses(b_resource_id NUMBER, b_date DATE) IS
606 SELECT a.address_id
607 , a.address_line1 street
608 , a.postal_code
609 , a.town_or_city city
610 , a.region_2 state
611 , a.country
612 , t.territory_short_name
613 , a.date_from start_date_active
614 , a.date_to end_date_active
615 FROM per_addresses a
616 , jtf_rs_resource_extns r
620 AND a.country = t.territory_code
617 , fnd_territories_vl t
618 WHERE r.resource_id = b_resource_id
619 AND a.person_id = r.source_id
621 AND TRUNC(a.date_from) <= TRUNC(b_date)
622 AND TRUNC(NVL(a.date_to, b_date + 1)) >= TRUNC(b_date)
623 ORDER BY a.primary_flag DESC, a.date_from DESC;
624
625 l_home_address c_home_addresses%ROWTYPE;
626 BEGIN
627 IF l_debug THEN
628 debug('Finding the addresses associated with Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
629 END IF;
630
631 OPEN c_home_addresses (p_resource_id, p_date);
632 FETCH c_home_addresses INTO l_home_address;
633 CLOSE c_home_addresses;
634
635 x_address.address_id := l_home_address.address_id;
636 x_address.street := l_home_address.street;
637 x_address.postal_code := l_home_address.postal_code;
638 x_address.city := l_home_address.city;
639 x_address.state := l_home_address.state;
640 x_address.country := l_home_address.country;
641 x_address.territory_short_name := l_home_address.territory_short_name;
642 x_address.start_date_active := l_home_address.start_date_active;
643 x_address.end_date_active := l_home_address.end_date_active;
644
645 IF l_debug THEN
646 IF x_address.address_id IS NOT NULL THEN
647 debug(' Found a Address: Address ID = ' || x_address.address_id, l_api_name, fnd_log.level_statement);
648 ELSE
649 debug(' Found no Home Address', l_api_name, fnd_log.level_statement);
650 END IF;
651 END IF;
652 END get_home_addresses;
653
654 PROCEDURE match_home_to_party(
655 p_home_addr_rec IN address_rec_type
656 , p_party_addr_tbl IN address_tbl_type
657 , x_matched_address_rec OUT NOCOPY address_rec_type
658 ) IS
659 l_api_name CONSTANT VARCHAR2(50) := 'MATCH_HOME_TO_PARTY';
660 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
661 BEGIN
662 x_matched_address_rec := p_home_addr_rec;
663
664 IF p_party_addr_tbl IS NULL OR p_party_addr_tbl.COUNT = 0 THEN
665 RETURN;
666 END IF;
667
668 -- If no Parties match exactly, atleast we can make use of the existing Party ID.
669 x_matched_address_rec.party_id := p_party_addr_tbl(1).party_id;
670
671 FOR i IN 1..p_party_addr_tbl.COUNT LOOP
672 IF p_home_addr_rec.address_id = p_party_addr_tbl(i).address_id THEN
673 x_matched_address_rec := p_party_addr_tbl(i);
674
675 x_matched_address_rec.start_date_active := p_home_addr_rec.start_date_active;
676 x_matched_address_rec.end_date_active := p_home_addr_rec.end_date_active;
677 EXIT;
678 END IF;
679 END LOOP;
680
681 IF l_debug THEN
682 IF x_matched_address_rec.party_site_id IS NULL THEN
683 debug('Best Address ID (#' || p_home_addr_rec.address_id || ') doesnt match with any Party Site', l_api_name, fnd_log.level_statement);
684 ELSE
685 debug('Best Address ID (#' || p_home_addr_rec.address_id || ') matches with Party Site ID = ' || x_matched_address_rec.party_site_id, l_api_name, fnd_log.level_statement);
686 END IF;
687 END IF;
688 END match_home_to_party;
689
690 PROCEDURE create_resource_party_link(
691 p_api_version IN NUMBER
692 , p_init_msg_list IN VARCHAR2
693 , p_commit IN VARCHAR2
694 , x_return_status OUT NOCOPY VARCHAR2
695 , x_msg_count OUT NOCOPY NUMBER
696 , x_msg_data OUT NOCOPY VARCHAR2
697 , p_resource_id NUMBER
698 , p_resource_type VARCHAR2
699 , p_address IN OUT NOCOPY address_rec_type
700 ) IS
701 l_api_version CONSTANT NUMBER := 1.0;
702 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_RESOURCE_PARTY_LINK';
703 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
704
705 l_location_rec hz_location_v2pub.location_rec_type;
706 l_person_rec hz_party_v2pub.person_rec_type;
707 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
708
709 l_profile_id NUMBER;
710 l_party_number VARCHAR2(30);
711 l_party_site_number VARCHAR2(30);
712
713 -- Get an arbitrary country (territory) code
714 CURSOR c_terr IS
715 SELECT territory_code FROM fnd_territories WHERE ROWNUM = 1;
716 BEGIN
717 IF l_debug THEN
718 debug('Creating the Resource Party Link for Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
719 END IF;
720
721 -- Check for API Compatibility
722 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
723 RAISE fnd_api.g_exc_unexpected_error;
724 END IF;
725
726 -- Initialize Message Stack if required
727 IF fnd_api.to_boolean(p_init_msg_list) THEN
728 fnd_msg_pub.initialize;
729 END IF;
730
731 -- Initialize Return Status
732 x_return_status := fnd_api.g_ret_sts_success;
733
734 SAVEPOINT create_party;
735
736 -- Street and Country are NOT NULL columns in HZ_LOCATIONS
737 IF p_address.country IS NULL THEN
738 l_location_rec.address1 := '_';
739 OPEN c_terr;
743 END IF;
740 FETCH c_terr INTO l_location_rec.country;
741 IF c_terr%NOTFOUND THEN
742 RAISE no_data_found;
744 CLOSE c_terr;
745 ELSE
746 l_location_rec.short_description := p_resource_type || ' ' || p_resource_id || ' ' || p_address.address_id;
747 l_location_rec.address1 := NVL(p_address.street, '_');
748 l_location_rec.city := p_address.city;
749 l_location_rec.state := p_address.state;
750 l_location_rec.postal_code := p_address.postal_code;
751 l_location_rec.country := p_address.country;
752 END IF;
753
754 IF l_debug THEN
755 debug('Creating Location Record in HZ_LOCATIONS', l_api_name, fnd_log.level_statement);
756 debug(' --> Address1 = ' || l_location_rec.address1, l_api_name, fnd_log.level_statement);
757 debug(' --> City = ' || l_location_rec.city, l_api_name, fnd_log.level_statement);
758 debug(' --> State = ' || l_location_rec.state, l_api_name, fnd_log.level_statement);
759 debug(' --> Zip = ' || l_location_rec.postal_code, l_api_name, fnd_log.level_statement);
760 debug(' --> Country = ' || l_location_rec.country, l_api_name, fnd_log.level_statement);
761 END IF;
762
763 l_location_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
764
765 hz_location_v2pub.create_location(
766 p_init_msg_list => fnd_api.g_false
767 , p_location_rec => l_location_rec
768 , x_return_status => x_return_status
769 , x_msg_count => x_msg_count
770 , x_msg_data => x_msg_data
771 , x_location_id => p_address.location_id
772 );
773 IF x_return_status <> fnd_api.g_ret_sts_success THEN
774 IF l_debug THEN
775 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
776 debug('HZ_LOCATION_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
777 END IF;
778 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
779 RAISE fnd_api.g_exc_unexpected_error;
780 END IF;
781 RAISE fnd_api.g_exc_error;
782 ELSE
783 IF l_debug THEN
784 debug('HZ_LOCATION_V2PUB.CREATE was successful. Location ID = ' || p_address.location_id, l_api_name, fnd_log.level_statement);
785 END IF;
786 END IF;
787
788 IF p_address.party_id IS NULL THEN
789 l_person_rec.person_first_name := g_st_party_fname;
790 l_person_rec.person_last_name := p_resource_type || ' ' || p_resource_id;
791 l_person_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
792
793
794 -- If the profile "Generate Party Number" is No, then
795 -- TCA expects the caller to pass the party number.
796 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
797 SELECT hz_party_number_s.NEXTVAL INTO l_person_rec.party_rec.party_number
798 FROM dual;
799 END IF;
800
801 IF l_debug THEN
802 debug('Creating Party Record in HZ_PARTIES', l_api_name, fnd_log.level_statement);
803 debug(' --> Party Number = ' || l_person_rec.party_rec.party_number, l_api_name, fnd_log.level_statement);
804 debug(' --> First Name = ' || l_person_rec.person_first_name, l_api_name, fnd_log.level_statement);
805 debug(' --> Last Name = ' || l_person_rec.person_last_name, l_api_name, fnd_log.level_statement);
806 END IF;
807
808 hz_party_v2pub.create_person(
809 p_init_msg_list => fnd_api.g_false
810 , p_person_rec => l_person_rec
811 , x_return_status => x_return_status
812 , x_msg_count => x_msg_count
813 , x_msg_data => x_msg_data
814 , x_party_id => p_address.party_id
815 , x_party_number => l_party_number
816 , x_profile_id => l_profile_id
817 );
818
819 IF x_return_status <> fnd_api.g_ret_sts_success THEN
820 IF l_debug THEN
821 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
822 debug('HZ_PARTY_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
823 END IF;
824 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
825 RAISE fnd_api.g_exc_unexpected_error;
826 END IF;
827 RAISE fnd_api.g_exc_error;
828 ELSE
829 IF l_debug THEN
830 debug('HZ_PARTY_V2PUB.CREATE_P was successful: Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
831 END IF;
832 END IF;
833 ELSE
834 IF l_debug THEN
835 debug('Party already exists. Using it. Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
836 END IF;
837 END IF;
838
839 l_party_site_rec.location_id := p_address.location_id;
840 l_party_site_rec.party_id := p_address.party_id;
841 l_party_site_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
842
843 -- If the profile "Generate Party Number" is No, then
844 -- TCA expects the caller to pass the party number.
845 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER') = 'N' THEN
849
846 SELECT hz_party_site_number_s.NEXTVAL INTO l_party_site_rec.party_site_number
847 FROM dual;
848 END IF;
850 IF l_debug THEN
851 debug('Creating Party Site Record in HZ_PARTY_SITES', l_api_name, fnd_log.level_statement);
852 debug(' --> Party Site Number = ' || l_party_site_rec.party_site_number, l_api_name, fnd_log.level_statement);
853 debug(' --> Party ID = ' || l_party_site_rec.party_id, l_api_name, fnd_log.level_statement);
854 debug(' --> Location ID = ' || l_party_site_rec.location_id, l_api_name, fnd_log.level_statement);
855 END IF;
856
857 hz_party_site_v2pub.create_party_site(
858 p_init_msg_list => fnd_api.g_false
859 , p_party_site_rec => l_party_site_rec
860 , x_return_status => x_return_status
861 , x_msg_count => x_msg_count
862 , x_msg_data => x_msg_data
863 , x_party_site_id => p_address.party_site_id
864 , x_party_site_number => l_party_site_number
865 );
866
867 IF x_return_status <> fnd_api.g_ret_sts_success THEN
868 IF l_debug THEN
869 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
870 debug('HZ_PARTY_SITE_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
871 END IF;
872 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
873 RAISE fnd_api.g_exc_unexpected_error;
874 END IF;
875 RAISE fnd_api.g_exc_error;
876 ELSE
877 IF l_debug THEN
878 debug('HZ_PARTY_SITE_V2PUB.CREATE_PS was successful. Party Site ID = ' || p_address.party_site_id, l_api_name, fnd_log.level_error);
879 END IF;
880 END IF;
881
882 IF fnd_api.to_boolean(p_commit) THEN
883 COMMIT;
884 END IF;
885 EXCEPTION
886 WHEN fnd_api.g_exc_error THEN
887 IF l_debug THEN
888 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
889 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
890 END IF;
891
892 ROLLBACK TO create_party;
893 x_return_status := fnd_api.g_ret_sts_error;
894 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
895 WHEN fnd_api.g_exc_unexpected_error THEN
896 IF l_debug THEN
897 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
898 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
899 END IF;
900
901 ROLLBACK TO create_party;
902 x_return_status := fnd_api.g_ret_sts_unexp_error;
903 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
904 WHEN OTHERS THEN
905 IF l_debug THEN
906 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
907 END IF;
908
909 ROLLBACK TO create_party;
910 x_return_status := fnd_api.g_ret_sts_unexp_error;
911 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
912 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
913 END IF;
914 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
915 END create_resource_party_link;
916
917 PROCEDURE get_resource_address(
918 p_api_version IN NUMBER
919 , p_init_msg_list IN VARCHAR2
920 , p_commit IN VARCHAR2
921 , x_return_status OUT NOCOPY VARCHAR2
922 , x_msg_count OUT NOCOPY NUMBER
923 , x_msg_data OUT NOCOPY VARCHAR2
924 , p_resource_id IN NUMBER
925 , p_resource_type IN VARCHAR2
926 , p_date IN DATE
927 , x_address_rec OUT NOCOPY address_rec_type
928 ) IS
929 l_api_version CONSTANT NUMBER := 1.0;
930 l_api_name CONSTANT VARCHAR2(50) := 'GET_RESOURCE_PARTY_INFO';
931 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
932
933 l_home_address address_rec_type;
934 l_party_addresses_tbl address_tbl_type;
935 l_validate_address BOOLEAN;
936 l_change_address VARCHAR2(1);
937 BEGIN
938
939 IF l_debug THEN
940 debug( 'Getting the Party Information for Resource ID = '
941 || p_resource_id || '( ' || p_resource_type || ') on ' || p_date
942 , l_api_name, fnd_log.level_procedure);
943 END IF;
944
945 -- Check for API Compatibility
946 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
947 RAISE fnd_api.g_exc_unexpected_error;
948 END IF;
949
950 -- Initialize Message Stack if required
951 IF fnd_api.to_boolean(p_init_msg_list) THEN
952 fnd_msg_pub.initialize;
953 END IF;
954
955 -- Initialize Return Status
956 x_return_status := fnd_api.g_ret_sts_success;
957
958 SAVEPOINT resource_party_info;
959
960 -- Get the Resource Home address as stored in HRMS for the Resource.
961 IF p_resource_type = 'RS_EMPLOYEE' THEN
962 get_home_addresses(
963 p_resource_id => p_resource_id
964 , p_resource_type => p_resource_type
965 , p_date => p_date
969
966 , x_address => l_home_address
967 );
968 END IF;
970 -- Get the Party Site Address corresponding to the Resource.
971 get_party_addresses(
972 p_resource_id => p_resource_id
973 , p_resource_type => p_resource_type
974 , p_date => p_date
975 , x_address_tbl => l_party_addresses_tbl
976 );
977
978 -- The Resource has home addresses defined.
979 IF l_home_address.address_id IS NOT NULL THEN
980 -- Fetch the Party corresponding to the first Home Address.
981 match_home_to_party(
982 p_home_addr_rec => l_home_address
983 , p_party_addr_tbl => l_party_addresses_tbl
984 , x_matched_address_rec => x_address_rec
985 );
986 ELSIF l_party_addresses_tbl IS NOT NULL AND l_party_addresses_tbl.COUNT > 0 THEN
987 IF l_debug THEN
988 debug('No Home Address found. But found a Party. Using it', l_api_name, fnd_log.level_statement);
989 END IF;
990 -- There is no home address. Pick the first Party Fetched.
991 x_address_rec := l_party_addresses_tbl(1);
992 END IF;
993
994 -- If there is no Location created for the Address, create it.
995 IF x_address_rec.location_id IS NULL THEN
996 IF l_debug THEN
997 debug('Since Location is not created.... Creating it', l_api_name, fnd_log.level_statement);
998 END IF;
999 create_resource_party_link(
1000 p_api_version => l_api_version
1001 , p_init_msg_list => fnd_api.g_false
1002 , p_commit => fnd_api.g_false
1003 , x_return_status => x_return_status
1004 , x_msg_count => x_msg_count
1005 , x_msg_data => x_msg_data
1006 , p_resource_id => p_resource_id
1007 , p_resource_type => p_resource_type
1008 , p_address => x_address_rec
1009 );
1010
1011 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1012 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1013 RAISE fnd_api.g_exc_unexpected_error;
1014 END IF;
1015 RAISE fnd_api.g_exc_error;
1016 END IF;
1017
1018 l_validate_address := TRUE;
1019 ELSIF l_home_address.address_id IS NOT NULL AND NOT are_addresses_equal(l_home_address, x_address_rec) THEN
1020 IF l_debug THEN
1021 debug('Location is already created.... Needs Updation', l_api_name, fnd_log.level_statement);
1022 END IF;
1023 l_change_address := fnd_api.g_true;
1024 l_validate_address := TRUE;
1025
1026 x_address_rec.street := l_home_address.street;
1027 x_address_rec.city := l_home_address.city;
1028 x_address_rec.state := l_home_address.state;
1029 x_address_rec.postal_code := l_home_address.postal_code;
1030 x_address_rec.territory_short_name := l_home_address.territory_short_name;
1031 x_address_rec.country := l_home_address.country;
1032 ELSIF x_address_rec.geometry IS NULL THEN
1033 IF l_debug THEN
1034 debug('Location is already created.... Needs Geocoding', l_api_name, fnd_log.level_statement);
1035 END IF;
1036 l_validate_address := TRUE;
1037 END IF;
1038
1039 -- Resolve the address if its a new Resource or Address has changed.
1040 -- Right now only Employee Resource / Party Resource can have an Address. So Resolving only for them.
1041 IF l_validate_address AND p_resource_type IN ('RS_EMPLOYEE', 'RS_PARTY') THEN
1042 IF l_debug THEN
1043 debug('Resolving the Address again', l_api_name, fnd_log.level_statement);
1044 END IF;
1045 resolve_address(
1046 p_api_version => l_api_version
1047 , x_return_status => x_return_status
1048 , x_msg_count => x_msg_count
1049 , x_msg_data => x_msg_data
1050 , p_location_id => x_address_rec.location_id
1051 , p_address1 => x_address_rec.street
1052 , p_city => x_address_rec.city
1053 , p_state => x_address_rec.state
1054 , p_postalcode => x_address_rec.postal_code
1055 , p_country => x_address_rec.territory_short_name
1056 , p_country_code => x_address_rec.country
1057 , p_update_address => l_change_address
1058 , x_geometry => x_address_rec.geometry
1059 );
1060
1061 -- Dont error out . Scheduler will handle it appro.
1062 x_return_status := fnd_api.g_ret_sts_success;
1063 END IF;
1064
1065 IF l_debug THEN
1066 debug('Returning Resource Party Info', l_api_name, fnd_log.level_statement);
1067 debug(' --> Party Site ID = ' || x_address_rec.party_site_id, l_api_name, fnd_log.level_statement);
1068 END IF;
1069
1070 IF fnd_api.to_boolean(p_commit) THEN
1071 COMMIT;
1072 END IF;
1073 EXCEPTION
1074 WHEN fnd_api.g_exc_error THEN
1075 IF l_debug THEN
1076 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1077 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
1078 END IF;
1079
1080 ROLLBACK TO resource_party_info;
1081 x_return_status := fnd_api.g_ret_sts_error;
1082 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1083 WHEN fnd_api.g_exc_unexpected_error THEN
1084 IF l_debug THEN
1085 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1086 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
1087 END IF;
1088
1089 ROLLBACK TO resource_party_info;
1090 x_return_status := fnd_api.g_ret_sts_unexp_error;
1091 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1092 WHEN OTHERS THEN
1093 IF l_debug THEN
1094 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1095 END IF;
1096
1097 ROLLBACK TO resource_party_info;
1098 x_return_status := fnd_api.g_ret_sts_unexp_error;
1099 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1100 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1101 END IF;
1105 PROCEDURE get_resource_party_info(
1102 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1103 END get_resource_address;
1104
1106 p_api_version IN NUMBER
1107 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1108 , p_commit IN VARCHAR2 DEFAULT NULL
1109 , x_return_status OUT NOCOPY VARCHAR2
1110 , x_msg_count OUT NOCOPY NUMBER
1111 , x_msg_data OUT NOCOPY VARCHAR2
1112 , p_resource_id IN NUMBER
1113 , p_resource_type IN VARCHAR2
1114 , p_date IN DATE
1115 , x_party_id OUT NOCOPY NUMBER
1116 , x_party_site_id OUT NOCOPY NUMBER
1117 , x_location_id OUT NOCOPY NUMBER
1118 ) IS
1119 l_address address_rec_type;
1120 BEGIN
1121 get_resource_address(
1122 p_api_version => p_api_version
1123 , p_init_msg_list => p_init_msg_list
1124 , p_commit => p_commit
1125 , x_return_status => x_return_status
1126 , x_msg_count => x_msg_count
1127 , x_msg_data => x_msg_data
1128 , p_resource_id => p_resource_id
1129 , p_resource_type => p_resource_type
1130 , p_date => p_date
1131 , x_address_rec => l_address
1132 );
1133
1134 IF x_return_status = fnd_api.g_ret_sts_success THEN
1135 x_party_id := l_address.party_id;
1136 x_party_site_id := l_address.party_site_id;
1137 x_location_id := l_address.location_id;
1138 END IF;
1139 END get_resource_party_info;
1140
1141 BEGIN
1142 init_package;
1143 END csf_resource_address_pvt;