[Home] [Help]
Skip to content
PACKAGE BODY: APPS.CSF_RESOURCE_ADDRESS_PVT
Source
1 PACKAGE BODY csf_resource_address_pvt AS
2 /* $Header: CSFVADRB.pls 120.46.12020000.6 2013/04/18 07:04:03 rkamasam ship $ */
3
4 g_debug VARCHAR2(1);
5 g_debug_level NUMBER;
6 g_res_add_prof VARCHAR2(200);
7
8 -- Declaration of a private procedure for getting default trip location
9 PROCEDURE get_default_location(p_resource_id NUMBER,
10 p_resource_type VARCHAR2,
11 x_address_rec OUT NOCOPY address_rec_type);
12
13 g_emp_res_query CONSTANT VARCHAR2(1500) :=
14 ' SELECT p.party_id
15 , s.party_site_id
16 , l.location_id
17 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
18 , l.address1 street
19 , l.postal_code
20 , l.city
21 , l.state
22 , l.country
23 , t.territory_short_name
24 , l.geometry
25 , s.start_date_active
26 , s.end_date_active
27 FROM jtf_rs_resource_extns_vl r
28 , per_people_f pf
29 , hz_parties p
30 , hz_party_sites s
31 , hz_locations l
32 , fnd_territories_vl t
33 WHERE r.resource_id = :resource_id
34 AND pf.person_id = r.source_id
35 AND pf.party_id = p.party_id (+)
36 AND p.party_id = s.party_id (+)
37 AND NVL(s.status, ''A'') = ''A''
38 AND s.location_id = l.location_id (+)
39 AND l.country = t.territory_code(+)
40 ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
41
42 g_party_res_query CONSTANT VARCHAR2(1500) :=
43 ' SELECT r.source_id party_id
44 , s.party_site_id
45 , l.location_id
46 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
47 , l.address1 street
48 , l.postal_code
49 , l.city
50 , l.state
51 , l.country
52 , t.territory_short_name
53 , l.geometry
54 , s.start_date_active
55 , s.end_date_active
56 FROM jtf_rs_resource_extns_vl r
57 , hz_party_sites s
58 , hz_locations l
59 , fnd_territories_vl t
60 WHERE r.resource_id = :resource_id
61 AND r.source_id = s.party_id (+)
62 AND NVL(s.status, ''A'') = ''A''
63 AND s.location_id = l.location_id (+)
64 AND l.country = t.territory_code(+)
65 ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
66
67 g_other_res_query CONSTANT VARCHAR2(1000) :=
68 ' SELECT p.party_id
69 , s.party_site_id
70 , l.location_id
71 , SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
72 , l.address1 street
73 , l.postal_code
74 , l.city
75 , l.state
76 , l.country
77 , t.territory_short_name
78 , l.geometry
79 , s.start_date_active
80 , s.end_date_active
81 FROM hz_parties p
82 , hz_party_sites s
83 , hz_locations l
84 , fnd_territories_vl t
85 WHERE p.person_last_name = :res_type_id_string
86 AND p.person_first_name = :dep_arr_party_name
87 AND s.party_id = p.party_id
88 AND l.location_id = s.location_id
89 AND l.country = t.territory_code
90 ORDER BY s.last_update_date DESC';
91
92 g_emp_sub_inv_qry CONSTANT VARCHAR2(2000) :=
93 '
94 select hp.party_id
95 , hps.party_site_id
96 , hzl.location_id
97 , SUBSTR(hzl.short_description, INSTR(hzl.short_description, '' '',-1) + 1) address_id
98 , hzl.address1 street
99 , hzl.postal_code
100 , hzl.city
101 , hzl.state
102 , hzl.country
103 , t.territory_short_name
104 , hzl.geometry
105 , hps.start_date_active
106 , hps.end_date_active
107 from hz_locations hzl,
108 hz_party_sites hps,
109 hz_cust_acct_sites hzacs,
110 hz_cust_site_uses hzacus,
111 hz_parties hp,
112 hz_cust_accounts hzca,
113 csp_rs_cust_relations ccr
114 ,fnd_territories_vl t
115 where hzl.location_id =hps.location_id
116 and hzl.country = t.territory_code(+)
117 and hps.party_id=hp.party_id
118 and hzacs.party_site_id=hps.party_site_id
119 and hzacs.cust_acct_site_id= hzacus.cust_acct_site_id
120 and hzacus.site_use_code = ''SHIP_TO''
121 and hzacus.PRIMARY_FLAG=''Y''
122 and hp.party_id=hzca.party_id
123 and hzca.cust_account_id=ccr.customer_id
124 and ccr.resource_id=:resource_id
125 ORDER BY hps.party_site_id NULLS LAST, hps.last_update_date DESC';
126
127
128
129 PROCEDURE init_package IS
130 BEGIN
131 g_debug := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
132 g_debug_level := NVL(fnd_profile.value('AFLOG_LEVEL'), fnd_log.level_event);
133 END init_package;
134
135 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
136 BEGIN
137 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
138 IF fnd_file.log > 0 THEN
139 IF p_message = ' ' THEN
140 fnd_file.put_line(fnd_file.log, '');
141 ELSE
142 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
143 END IF;
144 ELSE
145 fnd_log.string(p_level, 'csf.plsql.CSF_RESOURCE_ADDRESS_PVT.' || p_module, p_message);
146 END IF;
147 END IF;
148 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
149 END debug;
150
151 /**
152 * Finds out whether the passed value is a Number or not.
153 */
154 FUNCTION is_number(p_num_char IN VARCHAR2) RETURN BOOLEAN AS
155 n NUMBER;
156 BEGIN
157 IF p_num_char IS NULL THEN
158 RETURN FALSE;
159 END IF;
160 n := to_number(p_num_char);
161 RETURN TRUE;
162 EXCEPTION
163 WHEN OTHERS THEN
164 RETURN FALSE;
165 END is_number;
166
167 /**
168 * This function finds out whether the first word is a Building Number or not.
169 * This is called if Address Line 2, 3 or 4 is also filled apart from Address Line 1.
170 * The logic followed is exactly as given in BuildingNum.isBuildingNumber (BuildingNum.java).
171 */
172 FUNCTION is_address_line_valid(p_address_line IN VARCHAR2, p_country_code VARCHAR2)
173 RETURN BOOLEAN IS
174 l_address_line hz_locations.address1%TYPE;
175 l_first_word hz_locations.address1%TYPE;
176 l_count_words NUMBER;
177 l_sep_index NUMBER;
178 BEGIN
179 l_address_line := trim(p_address_line);
180
181 -- Trim off multiple spaces inbetween
182 WHILE INSTR(l_address_line, ' ') <> 0 LOOP
183 l_address_line := REPLACE(l_address_line, ' ', ' ');
184 END LOOP;
185
186 -- Count the number of words
187 l_count_words := LENGTH(l_address_line) - LENGTH(REPLACE(l_address_line, ' ')) + 1;
188
189 IF p_country_code = 'US' THEN
190 IF l_count_words > 1 THEN
191 l_first_word := SUBSTR(l_address_line, 1, INSTR(l_address_line, ' ')-1);
192
193 -- Building Number in Numeric Format
194 IF is_number(l_first_word) THEN
195 RETURN TRUE;
196 END IF;
197
198 IF LENGTH(l_first_word) = 1 THEN -- One Letter word and not a number
199 RETURN FALSE;
200 END IF;
201
202 -- Xnum Format
203 IF is_number(SUBSTR(l_first_word, 2)) THEN
204 RETURN TRUE;
205 END IF;
206
207 -- numX Format
208 IF is_number(SUBSTR(l_first_word, 1, LENGTH(l_first_word) - 1)) THEN
209 RETURN TRUE;
210 END IF;
211
212 -- numXnum or XnumXnum Format
213 IF NOT is_number(SUBSTR(l_first_word, 1, 1)) THEN -- XnumXnum Format
214 l_first_word := SUBSTR(l_first_word, 2); -- Becomes numXnum Format
215 END IF;
216
217 l_sep_index := INSTR(
218 l_first_word
219 , REPLACE(TRANSLATE(l_first_word, '0123456789', '0'), '0')
220 );
221
222 -- Since the First Character is already removed, the first character
223 -- shouldnt be an alphabet Similarly last shouldnt be a character
224 IF l_sep_index = 1 OR l_sep_index = LENGTH(l_first_word) THEN
225 RETURN FALSE;
226 END IF;
227
228 l_first_word := SUBSTR(l_first_word, 1, l_sep_index - 1)
229 || SUBSTR(l_first_word, l_sep_index + 1);
230
231 IF is_number(l_first_word) THEN
232 RETURN TRUE;
233 END IF;
234 END IF;
235 END IF;
236
237 RETURN FALSE;
238 END is_address_line_valid;
239
240 FUNCTION choose_address_line(
241 p_address1 IN VARCHAR2
242 , p_address2 IN VARCHAR2 DEFAULT NULL
243 , p_address3 IN VARCHAR2 DEFAULT NULL
244 , p_address4 IN VARCHAR2 DEFAULT NULL
245 , p_country_code IN VARCHAR2
246 ) RETURN VARCHAR2 IS
247 BEGIN
248 IF NVL(p_address4, '_') <> '_' AND is_address_line_valid(p_address4, p_country_code) THEN
249 RETURN p_address4;
250 ELSIF NVL(p_address3, '_') <> '_' AND is_address_line_valid(p_address3, p_country_code) THEN
251 RETURN p_address3;
252 ELSIF NVL(p_address2, '_') <> '_' AND is_address_line_valid(p_address2, p_country_code) THEN
253 RETURN p_address2;
254 ELSE
255 RETURN p_address1;
256 END IF;
257 END choose_address_line;
258
259 PROCEDURE update_location (
260 p_location_rec IN hz_location_v2pub.LOCATION_REC_TYPE,
261 p_object_version_number IN OUT NOCOPY NUMBER,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2
265 ) IS
266 pragma autonomous_transaction;
267 BEGIN
268 hz_location_v2pub.update_location(
269 p_location_rec => p_location_rec
270 , p_object_version_number => p_object_version_number
271 , x_return_status => x_return_status
272 , x_msg_count => x_msg_count
273 , x_msg_data => x_msg_data );
274 commit;
275 END;
276
277 PROCEDURE resolve_address(
278 p_api_version IN NUMBER
279 , p_init_msg_list IN VARCHAR2
280 , p_commit IN VARCHAR2
281 , x_return_status OUT NOCOPY VARCHAR2
282 , x_msg_count OUT NOCOPY NUMBER
283 , x_msg_data OUT NOCOPY VARCHAR2
284 , p_location_id IN NUMBER
285 , p_building_num IN VARCHAR2
286 , p_address1 IN VARCHAR2
287 , p_address2 IN VARCHAR2
288 , p_address3 IN VARCHAR2
289 , p_address4 IN VARCHAR2
290 , p_city IN VARCHAR2
291 , p_state IN VARCHAR2
292 , p_postalcode IN VARCHAR2
293 , p_county IN VARCHAR2
294 , p_province IN VARCHAR2
295 , p_country IN VARCHAR2
296 , p_country_code IN VARCHAR2
297 , p_alternate IN VARCHAR2
298 , p_update_address IN VARCHAR2 DEFAULT 'F'
299 , x_geometry OUT NOCOPY mdsys.sdo_geometry
300 ) IS
301 l_api_name CONSTANT VARCHAR2(50) := 'RESOLVE_ADDRESS';
302 l_api_version CONSTANT NUMBER := 1.0;
303 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
304
305 l_resultarray csf_lf_pub.csf_lf_resultarray;
306 l_update_addr BOOLEAN;
307 l_update_geo BOOLEAN := FALSE;
308 l_call_lf BOOLEAN;
309 l_roadname hz_locations.address1%TYPE;
310 l_location_ovn NUMBER;
311 l_location_rec hz_location_v2pub.location_rec_type;
312 l_road VARCHAR2(200);
313 l_geometry MDSYS.SDO_GEOMETRY := NULL;
314 l_geom_status_code hz_locations.geometry_status_code%TYPE := NULL;
315 l_msg_data VARCHAR2(200);
316 l_existing_geom_seg_id NUMBER;
317
318 CURSOR c_location_locking_info IS
319 SELECT object_version_number, geometry, geometry_status_code
320 FROM HZ_LOCATIONS
321 WHERE LOCATION_ID = p_location_id;
322
323 BEGIN
324 SAVEPOINT resolve_address_pub;
325
326 -- Check for API Compatibility
327 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
328 RAISE fnd_api.g_exc_unexpected_error;
329 END IF;
330
331 -- Initialize Message Stack if required
332 IF fnd_api.to_boolean(p_init_msg_list) THEN
333 fnd_msg_pub.initialize;
334 END IF;
335
336 -- Initialize Return Status
337 x_return_status := fnd_api.g_ret_sts_success;
338 OPEN c_location_locking_info;
339 FETCH c_location_locking_info INTO l_location_ovn, l_geometry, l_geom_status_code ;
340 CLOSE c_location_locking_info;
341
342 IF l_debug THEN
343 debug('Resolving Address at this time: ' || to_char(sysdate, 'YYYYMMDD HH24:MI:SS '), l_api_name, fnd_log.level_procedure);
344 debug('Resolving the Address corresponding to Location #' || p_location_id, l_api_name, fnd_log.level_procedure);
345 debug(' --> Address1 = ' || p_address1, l_api_name, fnd_log.level_statement);
346 debug(' --> City = ' || p_city, l_api_name, fnd_log.level_statement);
347 debug(' --> State = ' || p_state, l_api_name, fnd_log.level_statement);
348 debug(' --> Zip = ' || p_postalcode, l_api_name, fnd_log.level_statement);
349 debug(' --> Country = ' || p_country, l_api_name, fnd_log.level_statement);
350 debug(' --> Country Code = ' || p_country_code, l_api_name, fnd_log.level_statement);
351 debug(' --> Update Addr = ' || p_update_address, l_api_name, fnd_log.level_statement);
352 debug(' --> Existing SegId = ' || csf_locus_pub.get_locus_segmentid(l_geometry), l_api_name, fnd_log.level_statement);
353 debug(' --> Existing Lon = ' || csf_locus_pub.get_locus_lon(l_geometry), l_api_name, fnd_log.level_statement);
354 debug(' --> Existing Lat = ' || csf_locus_pub.get_locus_lat(l_geometry), l_api_name, fnd_log.level_statement);
355 IF csf_locus_pub.is_manual_geometry(l_geometry)
356 THEN
357 debug(' --> Its a Manual Geometry', l_api_name, fnd_log.level_statement);
358 ELSE
359 debug(' --> Its a NAVTEQ Geometry', l_api_name, fnd_log.level_statement);
360 END IF;
361 END IF;
362
363 --Update the address details only when address is valid
364 l_update_addr := NVL(fnd_api.to_boolean(p_update_address), FALSE);
365
366 -- Location Finder Profiles. Check whether Resolve Address needs to be called.
367 IF l_debug THEN
368 debug('CSF: Location Finder Installed = ' || fnd_profile.VALUE('CSF_LF_INSTALLED'), l_api_name, fnd_log.level_statement);
369 debug('CSR: Create Location = ' || fnd_profile.VALUE('CREATELOCATION'), l_api_name, fnd_log.level_statement);
370 END IF;
371
372 l_call_lf := (NVL(fnd_profile.VALUE('CSF_LF_INSTALLED'),'N') = 'Y');
373 -- AND (NVL(fnd_profile.VALUE('CREATELOCATION'),'N') = 'Y'); -- Create Location profile is no more used.
374
375 IF (l_call_lf AND (NOT csf_locus_pub.is_manual_geometry(l_geometry)))
376 /*
377 IF NVL(p_address4,'_') <> '_' AND is_address_line_valid(p_address4, p_country_code) THEN
378 l_roadname := p_address4;
379 ELSIF NVL(p_address3,'_') <> '_' AND is_address_line_valid(p_address3, p_country_code) THEN
380 l_roadname := p_address3;
381 ELSIF NVL(p_address2,'_') <> '_' AND is_address_line_valid(p_address2, p_country_code) THEN
382 l_roadname := p_address2;
383 ELSE
384 l_roadname := p_address1;
385 END IF; */
386 THEN
387 --Fix: 9981216.
388 l_roadname := CASE nvl(fnd_profile.value('CSF_STREET_LINE_FOR_LF'), 'ADDR1')
389 WHEN 'ADDR1' THEN p_address1
390 WHEN 'ADDR2' THEN p_address2
391 WHEN 'ADDR3' THEN p_address3
392 WHEN 'ADDR4' THEN p_address4
393 END;
394
395 IF l_debug THEN
396 debug('Before call to csf_lf_pub.csf_lf_resolveaddress ', l_api_name, fnd_log.level_statement);
397 END IF;
398
399 csf_lf_pub.csf_lf_resolveaddress(
400 p_api_version => l_api_version
401 , x_return_status => x_return_status
402 , x_msg_count => x_msg_count
403 , x_msg_data => l_msg_data
404 , p_country => NVL(p_country, '_')
405 , p_state => NVL(p_state, '_')
406 , p_county => NVL(p_county, '_')
407 , p_province => NVL(p_province, '_')
408 , p_city => NVL(p_city, '_')
409 , p_postalcode => NVL(p_postalcode, '_')
410 , p_roadname => NVL(l_roadname, '_')
411 , p_buildingnum => NVL(p_building_num, '_')
412 , p_alternate => NVL(l_roadname, '_')
413 , x_resultsarray => l_resultarray
414 );
415
416 IF l_debug THEN
417 debug('After call to csf_lf_pub.csf_lf_resolveaddress ', l_api_name, fnd_log.level_statement);
418 END IF;
419
420 IF l_resultarray IS NOT NULL THEN
421 x_geometry := l_resultarray(1).locus;
422 l_update_geo := TRUE;
423 l_update_addr:= TRUE;
424 --if l_resultarray is not null then
425 IF(l_resultarray(1).buildingnum = '_') THEN
426 l_road := initcap(l_resultarray(1).road);
427 ELSE
428 l_road := l_resultarray(1).buildingnum || ' ' || initcap(l_resultarray(1).road);
429 END IF;
430 IF l_debug THEN
431 debug('Inside l_resultarray IS NOT NULL ', l_api_name, fnd_log.level_statement);
432 debug('l_road = ' || l_road, l_api_name, fnd_log.level_statement);
433 END IF;
434 END IF;
435
436 IF x_return_status <> fnd_api.g_ret_sts_success THEN
437 x_msg_data := l_msg_data;
438 debug('Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
439 fnd_message.set_name ('CSF', 'CSF_LF_RAISED_ERROR');
440 fnd_message.set_token ('LOCATION_ID',p_location_id);
441 END IF;
442
443
444 IF l_debug and x_geometry is not null THEN
445 debug(' --> Longitude = ' || x_geometry.sdo_ordinates(1), l_api_name, fnd_log.level_statement);
446 debug(' --> Latitude = ' || x_geometry.sdo_ordinates(2), l_api_name, fnd_log.level_statement);
447 debug(' --> Segment = ' || x_geometry.sdo_ordinates(5), l_api_name, fnd_log.level_statement);
448 END IF;
449 ELSE
450 l_roadname := p_address1;
451 l_road := p_address1;
452 IF l_debug THEN
453 debug('Considering the l_roadname and l_road as same as p_address1 = ' || l_roadname, l_api_name, fnd_log.level_procedure);
454 END IF;
455 END IF;
456
457 IF l_update_addr THEN
458 IF p_address1 = l_roadname THEN
459 l_location_rec.address1 := l_road;
460 IF l_debug THEN
461 debug('Considering the address1 for resolving street. Modified address1 = ' || l_location_rec.address1, l_api_name, fnd_log.level_procedure);
462 END IF;
463 ELSIF p_address2 = l_roadname THEN
464 l_location_rec.address2 := l_road;
465 IF l_debug THEN
466 debug('Considering the address2 for resolving street. Modified address2 = ' || l_location_rec.address2, l_api_name, fnd_log.level_procedure);
467 END IF;
468 ELSIF p_address3 = l_roadname THEN
469 l_location_rec.address3 := l_road;
470 IF l_debug THEN
471 debug('Considering the address3 for resolving street. Modified address3 = ' || l_location_rec.address3, l_api_name, fnd_log.level_procedure);
472 END IF;
473 ELSIF p_address4 = l_roadname THEN
474 l_location_rec.address4 := l_road;
475 IF l_debug THEN
476 debug('Considering the address4 for resolving street. Modified address4 = ' || l_location_rec.address4, l_api_name, fnd_log.level_procedure);
477 END IF;
478 END IF;
479 IF p_city = '_' THEN
480 l_location_rec.city := '';
481 ELSE
482 l_location_rec.city := p_city;
483 END IF;
484
485 IF p_state = '_' THEN
486 l_location_rec.state := '';
487 ELSE
488 l_location_rec.state := p_state;
489 END IF;
490
491 IF p_postalcode = '_' THEN
492 l_location_rec.postal_code := '';
493 ELSE
494 l_location_rec.postal_code := p_postalcode;
495 END IF;
496
497 l_location_rec.country := p_country_code;
498 END IF;
499
500 IF x_return_status = 'S' THEN
501 l_location_rec.geometry_status_code := 'GOOD';
502 ELSIF x_return_status = 'E' THEN
503 l_location_rec.geometry_status_code := 'ERROR';
504 l_update_geo := TRUE;
505 END IF;
506
507 IF l_debug THEN
508 debug('Assigned Geometry Status = ' || l_location_rec.geometry_status_code, l_api_name, fnd_log.level_procedure);
509 END IF;
510
511 IF l_debug THEN
512 debug('Existing geometry status code - ' || l_geom_status_code , l_api_name, fnd_log.level_procedure);
513 END IF;
514
515
516 IF l_update_geo THEN
517 IF l_debug THEN
518 debug('LF is installed..Assigning resolved geometry for updation', l_api_name, fnd_log.level_procedure);
519 END IF;
520 l_location_rec.geometry := x_geometry;
521 ELSE
522 IF l_debug THEN
523 debug('Geometry should not be updated..Need to retain existing geometry', l_api_name, fnd_log.level_procedure);
524 END IF;
525 l_location_rec.geometry := l_geometry;
526 l_location_rec.geometry_status_code := l_geom_status_code;
527 END IF;
528
529
530 IF l_update_addr OR l_update_geo THEN
531 IF l_debug THEN
532 debug('Updating Address ', l_api_name, fnd_log.level_statement);
533 END IF;
534
535 l_location_rec.location_id := p_location_id;
536 l_location_rec.created_by_module := null;
537
538 -- Updating the location record (it updates both hz_parties and
539 -- hz_locations)
540
541 update_location(
542 p_location_rec => l_location_rec
543 , p_object_version_number => l_location_ovn
544 , x_return_status => x_return_status
545 , x_msg_count => x_msg_count
546 , x_msg_data => l_msg_data );
547 END IF;
548
549 if x_return_status <> fnd_api.g_ret_sts_success then
550 x_msg_data := l_msg_data;
551 debug('Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
552 fnd_message.set_name ('CSF', 'CSF_HZ_UPD_LOC_ERROR');
553 fnd_message.set_token ('LOCATION_ID', p_location_id);
554 END IF;
555
556 IF (l_resultarray IS NULL OR l_resultarray.COUNT > 1) THEN
557 IF l_debug THEN
558 debug('CSF_LF_PUB.RESOLVE didnt return proper result.. So Error', l_api_name, fnd_log.level_error);
559 END IF;
560 RAISE fnd_api.g_exc_error;
561 END IF;
562
563 IF fnd_api.to_boolean(p_commit) THEN
564 COMMIT;
565 END IF;
566
567 IF l_debug THEN
568 debug('Resolving Address at this time: ' || to_char(sysdate, 'YYYYMMDD HH24:MI:SS '), l_api_name, fnd_log.level_procedure);
569 debug('Resolve address API completed with:' || x_return_status, l_api_name, fnd_log.level_procedure);
570 END IF;
571
572 EXCEPTION
573 WHEN fnd_api.g_exc_error THEN
574 if l_debug then
575 debug('Expected Error: ' || fnd_message.get, l_api_name, fnd_log.level_error);
576 END IF;
577 ROLLBACK TO resolve_address_pub;
578 x_return_status := fnd_api.g_ret_sts_error;
579 WHEN fnd_api.g_exc_unexpected_error THEN
580 IF l_debug THEN
581 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
582 END IF;
583 ROLLBACK TO resolve_address_pub;
584 x_return_status := fnd_api.g_ret_sts_unexp_error;
585 WHEN OTHERS THEN
586 IF l_debug THEN
587 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
588 END IF;
589 x_return_status := fnd_api.g_ret_sts_unexp_error;
590 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
591 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
592 END IF;
593 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
594 ROLLBACK TO resolve_address_pub;
595 END resolve_address;
596
597 FUNCTION are_addresses_equal(
598 p_address1 address_rec_type
599 , p_address2 address_rec_type
600 )
601 RETURN BOOLEAN IS
602 l_api_name CONSTANT VARCHAR2(50) := 'ARE_ADDRESSES_EQUAL';
603 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
604 BEGIN
605 IF l_debug THEN
606 debug('Checking for the Equality of two addresses', l_api_name, fnd_log.level_procedure);
607 debug('Address#1', l_api_name, fnd_log.level_statement);
608 debug(' --> Street : ' || p_address1.street, l_api_name, fnd_log.level_statement);
609 debug(' --> City : ' || p_address1.city, l_api_name, fnd_log.level_statement);
610 debug(' --> State : ' || p_address1.state, l_api_name, fnd_log.level_statement);
611 debug(' --> Zip : ' || p_address1.postal_code, l_api_name, fnd_log.level_statement);
612 debug(' --> Country : ' || p_address1.country, l_api_name, fnd_log.level_statement);
613 debug(' --> Terr SN : ' || p_address1.territory_short_name, l_api_name, fnd_log.level_statement);
614 debug('Address#2', l_api_name, fnd_log.level_statement);
615 debug(' --> Street : ' || p_address2.street, l_api_name, fnd_log.level_statement);
616 debug(' --> City : ' || p_address2.city, l_api_name, fnd_log.level_statement);
617 debug(' --> State : ' || p_address2.state, l_api_name, fnd_log.level_statement);
618 debug(' --> Zip : ' || p_address2.postal_code, l_api_name, fnd_log.level_statement);
619 debug(' --> Country : ' || p_address2.country, l_api_name, fnd_log.level_statement);
620 debug(' --> Terr SN : ' || p_address2.territory_short_name, l_api_name, fnd_log.level_statement);
621 END IF;
622
623 IF NVL(UPPER(p_address1.street), '@#$') <> NVL(UPPER(p_address2.street), '@#$') THEN
624 RETURN FALSE;
625 END IF;
626
627 IF NVL(p_address1.postal_code, '@#$') <> NVL(p_address2.postal_code, '@#$') THEN
628 RETURN FALSE;
629 END IF;
630
631 IF NVL(UPPER(p_address1.city), '@#$') <> NVL(UPPER(p_address2.city), '@#$') THEN
632 RETURN FALSE;
633 END IF;
634
635 IF NVL(UPPER(p_address1.state), '@#$') <> NVL(UPPER(p_address2.state), '@#$') THEN
636 RETURN FALSE;
637 END IF;
638
639 -- Value of country might be in Full Form or Short Form
640 IF ( NVL(UPPER(p_address1.country), '@#$') <> NVL(UPPER(p_address2.country), '@#$')
641 AND NVL(UPPER(p_address1.territory_short_name), '@#$') <> NVL(UPPER(p_address2.territory_short_name), '@#$')
642 AND NVL(UPPER(p_address1.country), '@#$') <> NVL(UPPER(p_address2.territory_short_name), '@#$')
643 AND NVL(UPPER(p_address1.territory_short_name), '@#$') <> NVL(UPPER(p_address2.country), '@#$') )
644 THEN
645 RETURN FALSE;
646 END IF;
647
648 IF l_debug THEN
649 debug('Addresses are equal', l_api_name, fnd_log.level_statement);
650 END IF;
651 RETURN TRUE;
652 END are_addresses_equal;
653
654 /**
655 * Get the Party Site Addresses for the Parties created for the Resource.
656 */
657 PROCEDURE get_party_addresses(
658 p_resource_id IN NUMBER
659 , p_resource_type IN VARCHAR2
660 , p_date IN DATE
661 , x_address_tbl OUT NOCOPY address_tbl_type
662 ) IS
663 l_api_name CONSTANT VARCHAR2(50) := 'GET_PARTY_ADDRESSES';
664 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
665
666 TYPE ref_cursor_type IS REF CURSOR;
667 c_parties ref_cursor_type;
668 l_address_rec address_rec_type;
669 BEGIN
670 IF l_debug THEN
671 debug('Finding the Parties associated with Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
672 END IF;
673
674 x_address_tbl := address_tbl_type();
675 IF g_res_add_prof is null
676 THEN
677 g_res_add_prof := 'RS_HRMS_ADD';
678 END IF;
679 -- Find out whether we can use the Parties already created by the Source Module
680 IF p_resource_type IN ('RS_EMPLOYEE', 'RS_PARTY','RS_SUPPLIER_CONTACT') THEN
681 IF p_resource_type = 'RS_EMPLOYEE' and g_res_add_prof = 'RS_HRMS_ADD' THEN
682 OPEN c_parties FOR g_emp_res_query USING p_resource_id;
683 ELSIF p_resource_type = 'RS_PARTY' and g_res_add_prof = 'RS_HRMS_ADD' THEN
684 OPEN c_parties FOR g_party_res_query USING p_resource_id;
685 -- following code was added for 6962522
686 ELSIF p_resource_type IN( 'RS_EMPLOYEE', 'RS_SUPPLIER_CONTACT', 'RS_PARTY') and g_res_add_prof = 'RS_SUBINV_ADD' THEN
687 OPEN c_parties FOR g_emp_sub_inv_qry USING p_resource_id;
688 END IF;
689
690 IF c_parties%ISOPEN
691 then
692 LOOP
693 FETCH c_parties INTO l_address_rec;
694 EXIT WHEN c_parties%NOTFOUND;
695 x_address_tbl.extend();
696 x_address_tbl(c_parties%ROWCOUNT) := l_address_rec;
697 END LOOP;
698 CLOSE c_parties;
699 END IF;
700 IF l_debug THEN
701 debug(' Number of Parties found = ' || x_address_tbl.COUNT, l_api_name, fnd_log.level_statement);
702 END IF;
703
704 END IF;
705
706 IF x_address_tbl.COUNT > 0 THEN
707 RETURN;
708 END IF;
709
710 -- No Parties created by the Source Module were fetched.
711 -- Search for Parties created by this Module.
712
713 OPEN c_parties FOR g_other_res_query USING (p_resource_type || ' ' || p_resource_id), g_st_party_fname;
714 LOOP
715 FETCH c_parties INTO l_address_rec;
716 EXIT WHEN c_parties%NOTFOUND;
717 x_address_tbl.extend();
718 x_address_tbl(c_parties%ROWCOUNT) := l_address_rec;
719 END LOOP;
720 CLOSE c_parties;
721
722 IF l_debug THEN
723 debug(' Number of Parties found upon retrying = ' || x_address_tbl.COUNT, l_api_name, fnd_log.level_statement);
724 END IF;
725
726 END get_party_addresses;
727
728 /**
729 * Get the Home Addresses of the Resource as defined in HRMS People
730 * Management form.
731 */
732 PROCEDURE get_home_addresses(
733 p_resource_id IN NUMBER
734 , p_resource_type IN VARCHAR2
735 , p_date IN DATE
736 , x_address OUT NOCOPY address_rec_type
737 ) IS
738 l_api_name CONSTANT VARCHAR2(50) := 'GET_HOME_ADDRESSES';
739 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
740
741 CURSOR c_home_addresses(b_resource_id NUMBER, b_date DATE) IS
742 SELECT a.address_id
743 , a.address_line1 street
744 , a.postal_code
745 , a.town_or_city city
746 , a.region_2 state
747 , a.country
748 , t.territory_short_name
749 , a.date_from start_date_active
750 , a.date_to end_date_active
751 FROM per_addresses a
752 , jtf_rs_resource_extns r
753 , fnd_territories_vl t
754 WHERE r.resource_id = b_resource_id
755 AND a.person_id = r.source_id
756 AND a.country = t.territory_code
757 AND TRUNC(a.date_from) <= TRUNC(b_date)
758 AND TRUNC(NVL(a.date_to, b_date + 1)) >= TRUNC(b_date)
759 ORDER BY a.primary_flag DESC, a.date_from DESC;
760
761 l_home_address c_home_addresses%ROWTYPE;
762 BEGIN
763 IF l_debug THEN
764 debug('Finding the addresses associated with Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
765 END IF;
766
767 OPEN c_home_addresses (p_resource_id, p_date);
768 FETCH c_home_addresses INTO l_home_address;
769 CLOSE c_home_addresses;
770
771 x_address.address_id := l_home_address.address_id;
772 x_address.street := l_home_address.street;
773 x_address.postal_code := l_home_address.postal_code;
774 x_address.city := l_home_address.city;
775 x_address.state := l_home_address.state;
776 x_address.country := l_home_address.country;
777 x_address.territory_short_name := l_home_address.territory_short_name;
778 x_address.start_date_active := l_home_address.start_date_active;
779 x_address.end_date_active := l_home_address.end_date_active;
780
781
782 IF l_debug THEN
783 IF x_address.address_id IS NOT NULL THEN
784 debug(' Found a Address: Address ID = ' || x_address.address_id, l_api_name, fnd_log.level_statement);
785 ELSE
786 debug(' Found no Home Address', l_api_name, fnd_log.level_statement);
787 END IF;
788 END IF;
789 END get_home_addresses;
790
791 PROCEDURE match_home_to_party(
792 p_home_addr_rec IN address_rec_type
793 , p_party_addr_tbl IN address_tbl_type
794 , x_matched_address_rec OUT NOCOPY address_rec_type
795 ) IS
796 l_api_name CONSTANT VARCHAR2(50) := 'MATCH_HOME_TO_PARTY';
797 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
798 BEGIN
799 x_matched_address_rec := p_home_addr_rec;
800
801 IF p_party_addr_tbl IS NULL OR p_party_addr_tbl.COUNT = 0 THEN
802 RETURN;
803 END IF;
804
805 -- If no Parties match exactly, atleast we can make use of the existing Party ID.
806 x_matched_address_rec.party_id := p_party_addr_tbl(1).party_id;
807
808 FOR i IN 1..p_party_addr_tbl.COUNT LOOP
809 IF p_home_addr_rec.address_id = p_party_addr_tbl(i).address_id THEN
810 x_matched_address_rec := p_party_addr_tbl(i);
811
812 x_matched_address_rec.start_date_active := p_home_addr_rec.start_date_active;
813 x_matched_address_rec.end_date_active := p_home_addr_rec.end_date_active;
814 EXIT;
815 END IF;
816 END LOOP;
817
818 IF l_debug THEN
819 IF x_matched_address_rec.party_site_id IS NULL THEN
820 debug('Best Address ID (#' || p_home_addr_rec.address_id || ') doesnt match with any Party Site', l_api_name, fnd_log.level_statement);
821 ELSE
822 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);
823 END IF;
824 END IF;
825 END match_home_to_party;
826
827 PROCEDURE create_resource_party_link(
828 p_api_version IN NUMBER
829 , p_init_msg_list IN VARCHAR2
830 , p_commit IN VARCHAR2
831 , x_return_status OUT NOCOPY VARCHAR2
832 , x_msg_count OUT NOCOPY NUMBER
833 , x_msg_data OUT NOCOPY VARCHAR2
834 , p_resource_id NUMBER
835 , p_resource_type VARCHAR2
836 , p_address IN OUT NOCOPY address_rec_type
837 ) IS
838 l_api_version CONSTANT NUMBER := 1.0;
839 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_RESOURCE_PARTY_LINK';
840 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
841
842 l_location_rec hz_location_v2pub.location_rec_type;
843 l_person_rec hz_party_v2pub.person_rec_type;
844 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
845
846 l_profile_id NUMBER;
847 l_party_number VARCHAR2(30);
848 l_party_site_number VARCHAR2(30);
849
850 -- Get an arbitrary country (territory) code
851 CURSOR c_terr IS
852 SELECT territory_code FROM fnd_territories WHERE ROWNUM = 1;
853 BEGIN
854 IF l_debug THEN
855 debug('Creating the Resource Party Link for Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
856 END IF;
857
858 -- Check for API Compatibility
859 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
860 RAISE fnd_api.g_exc_unexpected_error;
861 END IF;
862
863 -- Initialize Message Stack if required
864 IF fnd_api.to_boolean(p_init_msg_list) THEN
865 fnd_msg_pub.initialize;
866 END IF;
867
868 -- Initialize Return Status
869 x_return_status := fnd_api.g_ret_sts_success;
870
871 -- SAVEPOINT create_party;
872
873 -- Street and Country are NOT NULL columns in HZ_LOCATIONS
874 IF p_address.country IS NULL THEN
875 l_location_rec.address1 := '_';
876 OPEN c_terr;
877 FETCH c_terr INTO l_location_rec.country;
878 IF c_terr%NOTFOUND THEN
879 RAISE no_data_found;
880 END IF;
881 CLOSE c_terr;
882 ELSE
883 l_location_rec.short_description := p_resource_type || ' ' || p_resource_id || ' ' || p_address.address_id;
884 l_location_rec.address1 := NVL(p_address.street, '_');
885 l_location_rec.city := p_address.city;
886 l_location_rec.state := p_address.state;
887 l_location_rec.postal_code := p_address.postal_code;
888 l_location_rec.country := p_address.country;
889 -- l_location_rec.county := p_address.county;
890 -- l_location_rec.province := p_address.province;
891 END IF;
892
893 IF l_debug THEN
894 debug('Creating Location Record in HZ_LOCATIONS', l_api_name, fnd_log.level_statement);
895 debug(' --> Address1 = ' || l_location_rec.address1, l_api_name, fnd_log.level_statement);
896 debug(' --> City = ' || l_location_rec.city, l_api_name, fnd_log.level_statement);
897 debug(' --> State = ' || l_location_rec.state, l_api_name, fnd_log.level_statement);
898 debug(' --> Zip = ' || l_location_rec.postal_code, l_api_name, fnd_log.level_statement);
899 debug(' --> Country = ' || l_location_rec.country, l_api_name, fnd_log.level_statement);
900 END IF;
901
902 l_location_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
903
904 hz_location_v2pub.create_location(
905 p_init_msg_list => fnd_api.g_false
906 , p_location_rec => l_location_rec
907 , x_return_status => x_return_status
908 , x_msg_count => x_msg_count
909 , x_msg_data => x_msg_data
910 , x_location_id => p_address.location_id
911 );
912 IF x_return_status <> fnd_api.g_ret_sts_success THEN
913 IF l_debug THEN
914 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
915 debug('HZ_LOCATION_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
916 END IF;
917 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
918 RAISE fnd_api.g_exc_unexpected_error;
919 END IF;
920 RAISE fnd_api.g_exc_error;
921 ELSE
922 IF l_debug THEN
923 debug('HZ_LOCATION_V2PUB.CREATE was successful. Location ID = ' || p_address.location_id, l_api_name, fnd_log.level_statement);
924 END IF;
925 END IF;
926
927 IF p_address.party_id IS NULL THEN
928 l_person_rec.person_first_name := g_st_party_fname;
929 l_person_rec.person_last_name := p_resource_type || ' ' || p_resource_id;
930 l_person_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
931
932
933 -- If the profile "Generate Party Number" is No, then
934 -- TCA expects the caller to pass the party number.
935 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
936 SELECT hz_party_number_s.NEXTVAL INTO l_person_rec.party_rec.party_number
937 FROM dual;
938 END IF;
939
940 IF l_debug THEN
941 debug('Creating Party Record in HZ_PARTIES', l_api_name, fnd_log.level_statement);
942 debug(' --> Party Number = ' || l_person_rec.party_rec.party_number, l_api_name, fnd_log.level_statement);
943 debug(' --> First Name = ' || l_person_rec.person_first_name, l_api_name, fnd_log.level_statement);
944 debug(' --> Last Name = ' || l_person_rec.person_last_name, l_api_name, fnd_log.level_statement);
945 END IF;
946
947 hz_party_v2pub.create_person(
948 p_init_msg_list => fnd_api.g_false
949 , p_person_rec => l_person_rec
950 , x_return_status => x_return_status
951 , x_msg_count => x_msg_count
952 , x_msg_data => x_msg_data
953 , x_party_id => p_address.party_id
954 , x_party_number => l_party_number
955 , x_profile_id => l_profile_id
956 );
957
958 IF x_return_status <> fnd_api.g_ret_sts_success THEN
959 IF l_debug THEN
960 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
961 debug('HZ_PARTY_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
962 END IF;
963 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
964 RAISE fnd_api.g_exc_unexpected_error;
965 END IF;
966 RAISE fnd_api.g_exc_error;
967 ELSE
968 IF l_debug THEN
969 debug('HZ_PARTY_V2PUB.CREATE_P was successful: Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
970 END IF;
971 END IF;
972 ELSE
973 IF l_debug THEN
974 debug('Party already exists. Using it. Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
975 END IF;
976 END IF;
977
978 l_party_site_rec.location_id := p_address.location_id;
979 l_party_site_rec.party_id := p_address.party_id;
980 l_party_site_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
981
982 -- If the profile "Generate Party Number" is No, then
983 -- TCA expects the caller to pass the party number.
984 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER') = 'N' THEN
985 SELECT hz_party_site_number_s.NEXTVAL INTO l_party_site_rec.party_site_number
986 FROM dual;
987 END IF;
988
989 IF l_debug THEN
990 debug('Creating Party Site Record in HZ_PARTY_SITES', l_api_name, fnd_log.level_statement);
991 debug(' --> Party Site Number = ' || l_party_site_rec.party_site_number, l_api_name, fnd_log.level_statement);
992 debug(' --> Party ID = ' || l_party_site_rec.party_id, l_api_name, fnd_log.level_statement);
993 debug(' --> Location ID = ' || l_party_site_rec.location_id, l_api_name, fnd_log.level_statement);
994 END IF;
995
996 hz_party_site_v2pub.create_party_site(
997 p_init_msg_list => fnd_api.g_false
998 , p_party_site_rec => l_party_site_rec
999 , x_return_status => x_return_status
1000 , x_msg_count => x_msg_count
1001 , x_msg_data => x_msg_data
1002 , x_party_site_id => p_address.party_site_id
1003 , x_party_site_number => l_party_site_number
1004 );
1005
1006 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1007 IF l_debug THEN
1008 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1009 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);
1010 END IF;
1011 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1012 RAISE fnd_api.g_exc_unexpected_error;
1013 END IF;
1014 RAISE fnd_api.g_exc_error;
1015 ELSE
1016 IF l_debug THEN
1017 debug('HZ_PARTY_SITE_V2PUB.CREATE_PS was successful. Party Site ID = ' || p_address.party_site_id, l_api_name, fnd_log.level_error);
1018 END IF;
1019 END IF;
1020
1021 IF fnd_api.to_boolean(p_commit) THEN
1022 COMMIT;
1023 END IF;
1024 EXCEPTION
1025 WHEN fnd_api.g_exc_error THEN
1026 IF l_debug THEN
1027 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1028 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
1029 END IF;
1030
1031 -- ROLLBACK TO create_party;
1032 x_return_status := fnd_api.g_ret_sts_error;
1033 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1034 WHEN fnd_api.g_exc_unexpected_error THEN
1035 IF l_debug THEN
1036 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1037 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
1038 END IF;
1039
1040 -- ROLLBACK TO create_party;
1041 x_return_status := fnd_api.g_ret_sts_unexp_error;
1042 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1043 WHEN OTHERS THEN
1044 IF l_debug THEN
1045 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1046 END IF;
1047
1048 -- ROLLBACK TO create_party;
1049 x_return_status := fnd_api.g_ret_sts_unexp_error;
1050 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1051 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1052 END IF;
1053 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1054 END create_resource_party_link;
1055
1056 PROCEDURE get_resource_address(
1057 p_api_version IN NUMBER
1058 , p_init_msg_list IN VARCHAR2
1059 , p_commit IN VARCHAR2
1060 , x_return_status OUT NOCOPY VARCHAR2
1061 , x_msg_count OUT NOCOPY NUMBER
1062 , x_msg_data OUT NOCOPY VARCHAR2
1063 , p_resource_id IN NUMBER
1064 , p_resource_type IN VARCHAR2
1065 , p_res_shift_add IN VARCHAR2 DEFAULT NULL
1066 , p_date IN DATE
1067 , x_address_rec OUT NOCOPY address_rec_type
1068 ) IS
1069 l_api_version CONSTANT NUMBER := 1.0;
1070 l_api_name CONSTANT VARCHAR2(50) := 'GET_RESOURCE_PARTY_INFO';
1071 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1072
1073 l_home_address address_rec_type;
1074 l_party_addresses_tbl address_tbl_type;
1075 l_validate_address BOOLEAN;
1076 l_change_address VARCHAR2(1);
1077 l_call_lf BOOLEAN;
1078 l_skip_eloc BOOLEAN;
1079
1080 BEGIN
1081
1082 IF l_debug THEN
1083 debug( 'Getting the Party Information for Resource ID = '
1084 || p_resource_id || '( ' || p_resource_type || ') on ' || p_date
1085 , l_api_name, fnd_log.level_procedure);
1086 END IF;
1087
1088 -- Check for API Compatibility
1089 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1090 RAISE fnd_api.g_exc_unexpected_error;
1091 END IF;
1092
1093 -- Initialize Message Stack if required
1094 IF fnd_api.to_boolean(p_init_msg_list) THEN
1095 fnd_msg_pub.initialize;
1096 END IF;
1097
1098 -- Initialize Return Status
1099 x_return_status := fnd_api.g_ret_sts_success;
1100
1101 -- SAVEPOINT resource_party_info;
1102
1103 G_RES_ADD_PROF := p_res_shift_add;
1104
1105
1106
1107 IF g_res_add_prof is null OR g_res_add_prof = 'RS_DEFAULT_TRIP'
1108 THEN
1109
1110 get_default_location( p_resource_id => p_resource_id
1111 , p_resource_type => p_resource_type
1112 , x_address_rec => x_address_rec);
1113 END IF;
1114
1115 -- Get the Resource Home address as stored in HRMS for the Resource.
1116 IF x_address_rec.street is null
1117 THEN
1118 IF p_resource_type = 'RS_EMPLOYEE' and ( g_res_add_prof in ('RS_HRMS_ADD','RS_DEFAULT_TRIP')
1119 OR g_res_add_prof is null)
1120 THEN
1121
1122 get_home_addresses(
1123 p_resource_id => p_resource_id
1124 , p_resource_type => p_resource_type
1125 , p_date => p_date
1126 , x_address => l_home_address
1127 );
1128 IF l_home_address.address_id is null
1129 then
1130 get_default_location( p_resource_id => p_resource_id
1131 , p_resource_type => p_resource_type
1132 , x_address_rec => x_address_rec);
1133 ELSE
1134 g_res_add_prof := 'RS_HRMS_ADD';
1135 END IF;
1136 END IF;
1137 END IF;
1138 -- Get the Party Site Address corresponding to the Resource if there is no default trip location.
1139 IF x_address_rec.street is null and l_home_address.address_id is null
1140 THEN
1141
1142 get_party_addresses(
1143 p_resource_id => p_resource_id
1144 , p_resource_type => p_resource_type
1145 , p_date => p_date
1146 , x_address_tbl => l_party_addresses_tbl
1147 );
1148 END IF;
1149 -- The Resource has home addresses defined.
1150 IF l_home_address.address_id IS NOT NULL THEN
1151 -- Fetch the Party corresponding to the first Home Address.
1152 get_party_addresses(
1153 p_resource_id => p_resource_id
1154 , p_resource_type => p_resource_type
1155 , p_date => p_date
1156 , x_address_tbl => l_party_addresses_tbl
1157 );
1158 match_home_to_party(
1159 p_home_addr_rec => l_home_address
1160 , p_party_addr_tbl => l_party_addresses_tbl
1161 , x_matched_address_rec => x_address_rec
1162 );
1163
1164 ELSIF l_party_addresses_tbl IS NOT NULL AND l_party_addresses_tbl.COUNT > 0 THEN
1165 IF l_debug THEN
1166 debug('No Home Address found. But found a Party. Using it', l_api_name, fnd_log.level_statement);
1167 END IF;
1168
1169 -- There is no home address. Pick the first Party Fetched.
1170 x_address_rec := l_party_addresses_tbl(1);
1171 END IF;
1172
1173 -- If there is no Location created for the Address, create it.
1174 IF x_address_rec.location_id IS NULL THEN
1175 IF l_debug THEN
1176 debug('Since Location is not created.... Creating it', l_api_name, fnd_log.level_statement);
1177 END IF;
1178
1179 create_resource_party_link(
1180 p_api_version => l_api_version
1181 , p_init_msg_list => fnd_api.g_false
1182 , p_commit => fnd_api.g_false
1183 , x_return_status => x_return_status
1184 , x_msg_count => x_msg_count
1185 , x_msg_data => x_msg_data
1186 , p_resource_id => p_resource_id
1187 , p_resource_type => p_resource_type
1188 , p_address => x_address_rec
1189 );
1190
1191 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1192 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1193 RAISE fnd_api.g_exc_unexpected_error;
1194 END IF;
1195 RAISE fnd_api.g_exc_error;
1196 END IF;
1197
1198 l_validate_address := TRUE;
1199 ELSIF l_home_address.address_id IS NOT NULL AND NOT are_addresses_equal(l_home_address, x_address_rec) THEN
1200 IF l_debug THEN
1201 debug('Location is already created.... Needs Updation', l_api_name, fnd_log.level_statement);
1202 END IF;
1203 l_change_address := fnd_api.g_true;
1204 l_validate_address := TRUE;
1205
1206 x_address_rec.street := l_home_address.street;
1207 x_address_rec.city := l_home_address.city;
1208 x_address_rec.state := l_home_address.state;
1209 x_address_rec.postal_code := l_home_address.postal_code;
1210 x_address_rec.territory_short_name := l_home_address.territory_short_name;
1211 x_address_rec.country := l_home_address.country;
1212 ELSIF x_address_rec.geometry IS NULL THEN
1213 IF l_debug THEN
1214 debug('Location is already created.... Needs Geocoding', l_api_name, fnd_log.level_statement);
1215 END IF;
1216 l_validate_address := TRUE;
1217 END IF;
1218
1219 -- Resolve the address if its a new Resource or Address has changed.
1220 -- Right now only Employee Resource / Party Resource can have an Address. So Resolving only for them.
1221 IF l_validate_address AND p_resource_type IN ('RS_EMPLOYEE', 'RS_PARTY') THEN
1222 IF l_debug THEN
1223 debug('Resolving the Address again', l_api_name, fnd_log.level_statement);
1224 END IF;
1225 l_call_lf := (NVL(fnd_profile.VALUE('CSF_LF_INSTALLED'),'N') = 'Y');
1226 l_skip_eloc := (NVL(fnd_profile.VALUE('CSF_SPATIAL_SKIP_ELOCATION'),'N') = 'N');
1227 IF(l_call_lf OR l_skip_eloc) THEN
1228 resolve_address(
1229 p_api_version => l_api_version
1230 , x_return_status => x_return_status
1231 , x_msg_count => x_msg_count
1232 , x_msg_data => x_msg_data
1233 , p_location_id => x_address_rec.location_id
1234 , p_address1 => x_address_rec.street
1235 , p_city => x_address_rec.city
1236 , p_state => x_address_rec.state
1237 , p_postalcode => x_address_rec.postal_code
1238 , p_country => x_address_rec.territory_short_name
1239 , p_country_code => x_address_rec.country
1240 , p_update_address => l_change_address
1241 , x_geometry => x_address_rec.geometry
1242 );
1243 END IF;
1244 -- Dont error out . Scheduler will handle it appro.
1245 x_return_status := fnd_api.g_ret_sts_success;
1246 END IF;
1247
1248 IF l_debug THEN
1249 debug('Returning Resource Party Info', l_api_name, fnd_log.level_statement);
1250 debug(' --> Party Site ID = ' || x_address_rec.party_site_id, l_api_name, fnd_log.level_statement);
1251 END IF;
1252
1253 IF fnd_api.to_boolean(p_commit) THEN
1254 COMMIT;
1255 END IF;
1256 EXCEPTION
1257 WHEN fnd_api.g_exc_error THEN
1258 IF l_debug THEN
1259 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1260 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
1261 END IF;
1262
1263 -- ROLLBACK TO resource_party_info;
1264 x_return_status := fnd_api.g_ret_sts_error;
1265 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1266 WHEN fnd_api.g_exc_unexpected_error THEN
1267 IF l_debug THEN
1268 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1269 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
1270 END IF;
1271
1272 -- ROLLBACK TO resource_party_info;
1273 x_return_status := fnd_api.g_ret_sts_unexp_error;
1274 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1275 WHEN OTHERS THEN
1276 IF l_debug THEN
1277 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1278 END IF;
1279
1280 -- ROLLBACK TO resource_party_info;
1281 x_return_status := fnd_api.g_ret_sts_unexp_error;
1282 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1283 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1284 END IF;
1285 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1286 END get_resource_address;
1287
1288 PROCEDURE get_resource_party_info(
1289 p_api_version IN NUMBER
1290 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1291 , p_commit IN VARCHAR2 DEFAULT NULL
1292 , x_return_status OUT NOCOPY VARCHAR2
1293 , x_msg_count OUT NOCOPY NUMBER
1294 , x_msg_data OUT NOCOPY VARCHAR2
1295 , p_resource_id IN NUMBER
1296 , p_resource_type IN VARCHAR2
1297 , p_date IN DATE
1298 , x_party_id OUT NOCOPY NUMBER
1299 , x_party_site_id OUT NOCOPY NUMBER
1300 , x_location_id OUT NOCOPY NUMBER
1301 ) IS
1302 l_address address_rec_type;
1303 BEGIN
1304 get_resource_address(
1305 p_api_version => p_api_version
1306 , p_init_msg_list => p_init_msg_list
1307 , p_commit => p_commit
1308 , x_return_status => x_return_status
1309 , x_msg_count => x_msg_count
1310 , x_msg_data => x_msg_data
1311 , p_resource_id => p_resource_id
1312 , p_resource_type => p_resource_type
1313 , p_date => p_date
1314 , x_address_rec => l_address
1315 );
1316
1317 IF x_return_status = fnd_api.g_ret_sts_success THEN
1318 x_party_id := l_address.party_id;
1319 x_party_site_id := l_address.party_site_id;
1320 x_location_id := l_address.location_id;
1321 END IF;
1322 END get_resource_party_info;
1323
1324
1325
1326 PROCEDURE create_one_time_address(
1327 p_api_version IN NUMBER
1328 , p_init_msg_list IN VARCHAR2
1329 , p_commit IN VARCHAR2
1330 , x_return_status OUT NOCOPY VARCHAR2
1331 , x_msg_count OUT NOCOPY NUMBER
1332 , x_msg_data OUT NOCOPY VARCHAR2
1333 , p_resource_id NUMBER
1334 , p_resource_type VARCHAR2
1335 , p_address IN OUT NOCOPY address_rec_type1
1336 ) IS
1337 l_api_version CONSTANT NUMBER := 1.0;
1338 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_RESOURCE_PARTY_LINK';
1339 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1340
1341 l_location_rec hz_location_v2pub.location_rec_type;
1342 l_person_rec hz_party_v2pub.person_rec_type;
1343 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
1344
1345 l_profile_id NUMBER;
1346 l_party_number VARCHAR2(30);
1347 l_party_site_number VARCHAR2(30);
1348
1349 -- Get an arbitrary country (territory) code
1350 CURSOR c_terr IS
1351 SELECT territory_code FROM fnd_territories WHERE ROWNUM = 1;
1352
1353
1354 BEGIN
1355 IF l_debug THEN
1356 debug('Creating the Resource Party Link for Resource ID = ' || p_resource_id, l_api_name, fnd_log.level_procedure);
1357 END IF;
1358
1359
1360 -- Check for API Compatibility
1361 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1362 RAISE fnd_api.g_exc_unexpected_error;
1363 END IF;
1364
1365 -- Initialize Message Stack if required
1366 IF fnd_api.to_boolean(p_init_msg_list) THEN
1367 fnd_msg_pub.initialize;
1368 END IF;
1369
1370 -- Initialize Return Status
1371 x_return_status := fnd_api.g_ret_sts_success;
1372
1373 SAVEPOINT create_party;
1374
1375 -- Street and Country are NOT NULL columns in HZ_LOCATIONS
1376 IF p_address.country IS NULL THEN
1377 l_location_rec.address1 := '_';
1378 OPEN c_terr;
1379 FETCH c_terr INTO l_location_rec.country;
1380 IF c_terr%NOTFOUND THEN
1381 RAISE no_data_found;
1382 END IF;
1383 CLOSE c_terr;
1384
1385 ELSE
1386
1387 l_location_rec.short_description := p_resource_type || ' ' || p_resource_id || ' ' || p_address.address_id;
1388
1389 l_location_rec.address1 := NVL(p_address.street, '_');
1390
1391 l_location_rec.city := p_address.city;
1392
1393 l_location_rec.state := p_address.state;
1394
1395 l_location_rec.postal_code := p_address.postal_code;
1396
1397 l_location_rec.country := p_address.country;
1398
1399 l_location_rec.county := p_address.county;
1400
1401 l_location_rec.province := p_address.province;
1402
1403 END IF;
1404
1405 IF l_debug THEN
1406 debug('Creating Location Record in HZ_LOCATIONS', l_api_name, fnd_log.level_statement);
1407 debug(' --> Address1 = ' || l_location_rec.address1, l_api_name, fnd_log.level_statement);
1408 debug(' --> City = ' || l_location_rec.city, l_api_name, fnd_log.level_statement);
1409 debug(' --> State = ' || l_location_rec.state, l_api_name, fnd_log.level_statement);
1410 debug(' --> Zip = ' || l_location_rec.postal_code, l_api_name, fnd_log.level_statement);
1411 debug(' --> Country = ' || l_location_rec.country, l_api_name, fnd_log.level_statement);
1412 END IF;
1413
1414 l_location_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
1415
1416 hz_location_v2pub.create_location(
1417 p_init_msg_list => fnd_api.g_false
1418 , p_location_rec => l_location_rec
1419 , x_return_status => x_return_status
1420 , x_msg_count => x_msg_count
1421 , x_msg_data => x_msg_data
1422 , x_location_id => p_address.location_id
1423 );
1424
1425 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1426 IF l_debug THEN
1427 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1428 debug('HZ_LOCATION_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
1429 END IF;
1430
1431 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1432
1433 RAISE fnd_api.g_exc_unexpected_error;
1434 END IF;
1435 RAISE fnd_api.g_exc_error;
1436 ELSE
1437 IF l_debug THEN
1438 debug('HZ_LOCATION_V2PUB.CREATE was successful. Location ID = ' || p_address.location_id, l_api_name, fnd_log.level_statement);
1439 END IF;
1440 END IF;
1441
1442 IF p_address.party_id IS NULL THEN
1443 l_person_rec.person_first_name := g_st_party_fname;
1444 l_person_rec.person_last_name := p_resource_type || ' ' || p_resource_id;
1445 l_person_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
1446
1447
1448 -- If the profile "Generate Party Number" is No, then
1449 -- TCA expects the caller to pass the party number.
1450 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
1451 SELECT hz_party_number_s.NEXTVAL INTO l_person_rec.party_rec.party_number
1452 FROM dual;
1453 END IF;
1454
1455 IF l_debug THEN
1456 debug('Creating Party Record in HZ_PARTIES', l_api_name, fnd_log.level_statement);
1457 debug(' --> Party Number = ' || l_person_rec.party_rec.party_number, l_api_name, fnd_log.level_statement);
1458 debug(' --> First Name = ' || l_person_rec.person_first_name, l_api_name, fnd_log.level_statement);
1459 debug(' --> Last Name = ' || l_person_rec.person_last_name, l_api_name, fnd_log.level_statement);
1460 END IF;
1461
1462 hz_party_v2pub.create_person(
1463 p_init_msg_list => fnd_api.g_false
1464 , p_person_rec => l_person_rec
1465 , x_return_status => x_return_status
1466 , x_msg_count => x_msg_count
1467 , x_msg_data => x_msg_data
1468 , x_party_id => p_address.party_id
1469 , x_party_number => l_party_number
1470 , x_profile_id => l_profile_id
1471 );
1472
1473 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1474 IF l_debug THEN
1475 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1476 debug('HZ_PARTY_V2PUB.CREATE returned error: Error = ' || fnd_msg_pub.get(fnd_msg_pub.g_last), l_api_name, fnd_log.level_error);
1477 END IF;
1478 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1479 RAISE fnd_api.g_exc_unexpected_error;
1480 END IF;
1481 RAISE fnd_api.g_exc_error;
1482 ELSE
1483 IF l_debug THEN
1484 debug('HZ_PARTY_V2PUB.CREATE_P was successful: Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
1485 END IF;
1486 END IF;
1487 ELSE
1488 IF l_debug THEN
1489 debug('Party already exists. Using it. Party ID = ' || p_address.party_id, l_api_name, fnd_log.level_statement);
1490 END IF;
1491 END IF;
1492
1493 l_party_site_rec.location_id := p_address.location_id;
1494 l_party_site_rec.party_id := p_address.party_id;
1495 l_party_site_rec.created_by_module := 'CSFDEAR'; -- Calling Module 'CSF: Departure Arrival'
1496
1497 -- If the profile "Generate Party Number" is No, then
1498 -- TCA expects the caller to pass the party number.
1499 IF fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER') = 'N' THEN
1500 SELECT hz_party_site_number_s.NEXTVAL INTO l_party_site_rec.party_site_number
1501 FROM dual;
1502 END IF;
1503
1504 IF l_debug THEN
1505 debug('Creating Party Site Record in HZ_PARTY_SITES', l_api_name, fnd_log.level_statement);
1506 debug(' --> Party Site Number = ' || l_party_site_rec.party_site_number, l_api_name, fnd_log.level_statement);
1507 debug(' --> Party ID = ' || l_party_site_rec.party_id, l_api_name, fnd_log.level_statement);
1508 debug(' --> Location ID = ' || l_party_site_rec.location_id, l_api_name, fnd_log.level_statement);
1509 END IF;
1510
1511 hz_party_site_v2pub.create_party_site(
1512 p_init_msg_list => fnd_api.g_false
1513 , p_party_site_rec => l_party_site_rec
1514 , x_return_status => x_return_status
1515 , x_msg_count => x_msg_count
1516 , x_msg_data => x_msg_data
1517 , x_party_site_id => p_address.party_site_id
1518 , x_party_site_number => l_party_site_number
1519 );
1520
1521 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1522 IF l_debug THEN
1523 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1524 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);
1525 END IF;
1526 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1527 RAISE fnd_api.g_exc_unexpected_error;
1528 END IF;
1529 RAISE fnd_api.g_exc_error;
1530 ELSE
1531 IF l_debug THEN
1532 debug('HZ_PARTY_SITE_V2PUB.CREATE_PS was successful. Party Site ID = ' || p_address.party_site_id, l_api_name, fnd_log.level_error);
1533 END IF;
1534 END IF;
1535
1536 IF fnd_api.to_boolean(p_commit) THEN
1537 COMMIT;
1538 END IF;
1539
1540
1541 EXCEPTION
1542 WHEN fnd_api.g_exc_error THEN
1543 IF l_debug THEN
1544 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1545 debug('Expected Error: ' || x_msg_data, l_api_name, fnd_log.level_error);
1546 END IF;
1547
1548 ROLLBACK TO create_party;
1549 x_return_status := fnd_api.g_ret_sts_error;
1550 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1551 WHEN fnd_api.g_exc_unexpected_error THEN
1552 IF l_debug THEN
1553 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1554 debug('Unexpected Error: ' || x_msg_data, l_api_name, fnd_log.level_unexpected);
1555 END IF;
1556
1557 ROLLBACK TO create_party;
1558 x_return_status := fnd_api.g_ret_sts_unexp_error;
1559 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1560 WHEN OTHERS THEN
1561 IF l_debug THEN
1562 debug('Exception: SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1563 END IF;
1564
1565 ROLLBACK TO create_party;
1566 x_return_status := fnd_api.g_ret_sts_unexp_error;
1567 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1568 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1569 END IF;
1570 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1571 END create_one_time_address;
1572
1573
1574 procedure create_location(p_api_version in number
1575 , p_init_msg_list in varchar2
1576 , p_commit in varchar2
1577 , x_return_status out nocopy varchar2
1578 , x_msg_data out nocopy varchar2
1579 , x_msg_count out nocopy number
1580 , p_resource_id in number
1581 , p_resource_type in varchar2
1582 , x_address_rec in out nocopy csf_resource_address_pvt.address_rec_type1)
1583 is
1584 begin
1585
1586 create_one_time_address(
1587 p_api_version => p_api_version
1588 , p_init_msg_list => p_init_msg_list
1589 , p_commit => p_commit
1590 , x_return_status =>x_return_status
1591 , x_msg_count =>x_msg_count
1592 , x_msg_data =>x_msg_data
1593 , p_resource_id =>p_resource_id
1594 , p_resource_type =>p_resource_type
1595 , p_address => x_address_rec
1596 );
1597 end;
1598
1599 PROCEDURE get_default_location(p_resource_id NUMBER,
1600 p_resource_type VARCHAR2,
1601 x_address_rec OUT NOCOPY address_rec_type)
1602 IS
1603 CURSOR c_trip_location
1604 IS
1605 select hl.location_id,
1606 hps.party_site_id,
1607 hps.party_id,
1608 hl.address1,
1609 hl.postal_code,
1610 hl.city,
1611 hl.state,
1612 hl.country,
1613 t.territory_short_name,
1614 HPS.START_DATE_ACTIVE,
1615 HPS.END_DATE_ACTIVE
1616 FROM csp_rs_cust_relations csc
1617 , hz_locations hl
1618 , fnd_territories_vl t
1619 , hz_party_sites hps
1620 WHERE csc.resource_id=p_resource_id
1621 AND csc.resource_type = p_resource_type
1622 AND csc.default_trip_start = hl.location_id(+)
1623 AND hl.country = t.territory_code(+)
1624 AND hps.location_id = HL.location_id
1625 AND csc.default_trip_start is not null;
1626
1627 l_trip_location c_trip_location%rowtype;
1628
1629 BEGIN
1630
1631 open c_trip_location;
1632 fetch c_trip_location into l_trip_location;
1633 IF c_trip_location%found
1634 THEN
1635 x_address_rec.street := l_trip_location.address1;
1636 x_address_rec.postal_code := l_trip_location.postal_code;
1637 x_address_rec.city := l_trip_location.city;
1638 x_address_rec.state := l_trip_location.state;
1639 x_address_rec.country := l_trip_location.country;
1640 x_address_rec.territory_short_name := l_trip_location.territory_short_name;
1641 x_address_rec.start_date_active := l_trip_location.start_date_active;
1642 x_address_rec.end_date_active := l_trip_location.end_date_active;
1643 x_address_rec.location_id := l_trip_location.location_id;
1644 x_address_rec.party_site_id := l_trip_location.party_site_id;
1645 x_address_rec.party_id := l_trip_location.party_id;
1646 END IF;
1647 close c_trip_location;
1648
1649 END;
1650
1651 BEGIN
1652 init_package;
1653 END csf_resource_address_pvt;