[Home] [Help]
PACKAGE BODY: APPS.FND_OID_USERS
Source
1 package body fnd_oid_users as
2 /* $Header: AFSCOURB.pls 120.6 2005/11/03 16:12:34 ssallaka noship $ */
3 --
4 /*****************************************************************************/
5 -- Start of Package Globals
6
7 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_oid_users.';
8
9 -- End of Package Globals
10 --
11 -------------------------------------------------------------------------------
12 procedure hz_create(
13 p_ldap_message in fnd_oid_util.ldap_message_type
14 , x_return_status out nocopy varchar2
15 ) is
16
17 l_module_source varchar2(256);
18 l_tca_error exception;
19 x_location_id number;
20 l_party_return_status varchar2(1);
21
22 begin
23 l_module_source := G_MODULE_SOURCE || 'hz_create';
24
25 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
26
27 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
28 then
29 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
30 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
31 'p_ldap_message.sn = ' || p_ldap_message.sn
32 || ', p_ldap_message.givenName = ' || p_ldap_message.givenName
33 || ', p_ldap_message.telephoneNumber = ' || p_ldap_message.telephoneNumber
34 || ', p_ldap_message.homePhone = ' || p_ldap_message.homePhone
35 || ', p_ldap_message.mail = ' || p_ldap_message.mail
36 || ', p_ldap_message.c = ' || p_ldap_message.c
37 || ', p_ldap_message.street = ' || p_ldap_message.street);
38 end if;
39
40 if ( (p_ldap_message.sn is not null)
41 or (p_ldap_message.givenName is not null) )
42 then
43 fnd_oid_users.create_party(p_ldap_message => p_ldap_message,
44 x_return_status => x_return_status);
45 end if;
46
47 if (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) then
48 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
49 then
50 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Party ' ||
51 p_ldap_message.givenName || ', ' || p_ldap_message.sn || ' created successfully');
52 end if;
53
54 if (p_ldap_message.telephoneNumber is not null)
55 then
56 fnd_oid_users.create_phone_contact_point(
57 p_ldap_message => p_ldap_message,
58 p_contact_point_purpose => G_BUSINESS,
59 x_return_status => x_return_status);
60 end if;
61
62 if (p_ldap_message.homePhone is not null)
63 then
64 fnd_oid_users.create_phone_contact_point(
65 p_ldap_message => p_ldap_message,
66 p_contact_point_purpose => G_PERSONAL,
67 x_return_status => x_return_status);
68 end if;
69
70 if (p_ldap_message.mail is not null)
71 then
72 fnd_oid_users.create_email_contact_point(
73 p_ldap_message => p_ldap_message,
74 x_return_status => x_return_status);
75 end if;
76
77 if ( (p_ldap_message.c is not null) and (p_ldap_message.street is not null) )
78 then
79 fnd_oid_users.create_location(
80 p_ldap_message => p_ldap_message,
81 x_return_status => x_return_status);
82 end if;
83 else
84 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
85 then
86 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Party creation ' ||
87 p_ldap_message.givenName || ', ' || p_ldap_message.sn || ' failed.');
88 end if;
89 end if;
90 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
91 then
92 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
93 end if;
94
95 exception
96 when others then
97 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
98 then
99 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
100 end if;
101 end hz_create;
102 --
103 -------------------------------------------------------------------------------
104 procedure create_party(
105 p_ldap_message in fnd_oid_util.ldap_message_type
106 , x_return_status out nocopy varchar2
107 ) is
108
109 l_module_source varchar2(256);
110 l_orig_sys_rec hz_orig_system_ref_pub.orig_sys_reference_rec_type;
111 l_tca_error exception;
112 x_person_rec hz_party_v2pub.person_rec_type;
113 x_msg_count number;
114 x_msg_data varchar2(2000);
115 x_party_id number;
116 x_party_number varchar2(2000);
117 x_profile_id number;
118 x_customer_id number;
119 x_employee_id number;
120
121 begin
122 l_module_source := G_MODULE_SOURCE || 'create_party';
123
124 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
125 then
126 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
127 end if;
128
129 fnd_oid_users.get_person_rec(p_ldap_message => p_ldap_message,
130 p_action_type => G_CREATE,
131 x_person_rec => x_person_rec,
132 x_return_status => x_return_status);
133
134 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
135 then
136 raise l_tca_error;
137 end if;
138
139 hz_party_v2pub.create_person(p_init_msg_list => fnd_api.G_TRUE,
140 p_person_rec => x_person_rec,
141 x_party_id => x_party_id,
142 x_party_number => x_party_number,
143 x_profile_id => x_profile_id,
144 x_return_status => x_return_status,
145 x_msg_count => x_msg_count,
146 x_msg_data => x_msg_data);
147
148 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
149 then
150 raise l_tca_error;
151 end if;
152
153 fnd_oid_users.create_orig_system_reference(
154 p_ldap_message => p_ldap_message,
155 p_tag => G_PERSON,
156 p_owner_table_name => G_HZ_PARTIES,
157 p_owner_table_id => x_party_id,
158 p_status => G_ACTIVE,
159 x_return_status => x_return_status);
160
161 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
162 then
163 raise l_tca_error;
164 end if;
165
166 --Bug fix 4311778
167 fnd_user_pkg.DERIVE_CUSTOMER_EMPLOYEE_ID(
168 user_name => p_ldap_message.object_name,
169 person_party_id => x_party_id,
170 customer_id => x_customer_id,
171 employee_id => x_employee_id);
172
173 update fnd_user
174 set person_party_id = x_party_id, customer_id = x_customer_id,
175 employee_id = x_employee_id
176 where user_name = p_ldap_message.object_name;
177
178 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
179 then
180 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Linked user '
181 || p_ldap_message.object_name || ' to party number ' || x_party_id);
182 end if;
183
184 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
185 then
186 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
187 end if;
188
189 exception
190 when l_tca_error then
191 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
192 then
193 if (x_msg_count > 0)
194 then
195 for i in 1..x_msg_count
196 loop
197 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
198 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
199 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
200 end loop;
201 else
202 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
203 , 'x_return_status = ' || x_return_status);
204 end if;
205 end if;
206 when others then
207 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
208 then
209 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
210 end if;
211
212 end create_party;
213 --
214 -------------------------------------------------------------------------------
215 procedure create_phone_contact_point(
216 p_ldap_message in fnd_oid_util.ldap_message_type
217 , p_contact_point_purpose in varchar2
218 , x_return_status out nocopy varchar2
219 ) is
220
221 l_module_source varchar2(256);
222 x_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
223 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
224 l_tca_error exception;
225 x_contact_point_id number;
226 x_msg_count number;
227 x_msg_data varchar2(2000);
228
229 begin
230 l_module_source := G_MODULE_SOURCE || 'create_phone_contact_point';
231
232 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
233 then
234 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
235 end if;
236
237 fnd_oid_users.get_contact_point_rec(
238 p_ldap_message => p_ldap_message,
239 p_contact_point_type => G_PHONE,
240 p_contact_point_purpose => p_contact_point_purpose,
241 p_action_type => G_CREATE,
242 x_contact_point_rec => x_contact_point_rec,
243 x_return_status => x_return_status);
244
245 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
246 then
247 raise l_tca_error;
248 end if;
249
250 l_phone_rec.phone_line_type := G_GEN;
251
252 if (p_contact_point_purpose = G_BUSINESS)
253 then
254 l_phone_rec.raw_phone_number := p_ldap_message.telephoneNumber;
255 elsif (p_contact_point_purpose = G_PERSONAL)
256 then
257 l_phone_rec.phone_number := p_ldap_message.homePhone;
258 end if;
259
260 hz_contact_point_v2pub.create_phone_contact_point(
261 p_init_msg_list => fnd_api.G_TRUE,
262 p_contact_point_rec => x_contact_point_rec,
263 p_phone_rec => l_phone_rec,
264 x_contact_point_id => x_contact_point_id,
265 x_return_status => x_return_status,
266 x_msg_count => x_msg_count,
267 x_msg_data => x_msg_data);
268
269 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
270 then
271 raise l_tca_error;
272 end if;
273
274 fnd_oid_users.create_orig_system_reference(
275 p_ldap_message => p_ldap_message,
276 p_tag => p_contact_point_purpose,
277 p_owner_table_name => G_HZ_CONTACT_POINTS,
278 p_owner_table_id => x_contact_point_id,
279 p_status => G_ACTIVE,
280 x_return_status => x_return_status);
281
282 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
283 then
284 raise l_tca_error;
285 end if;
286
287 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
288 then
289 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
290 end if;
291
292 exception
293 when l_tca_error then
294 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
295 then
296 if (x_msg_count > 0)
297 then
298 for i in 1..x_msg_count
299 loop
300 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
301 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
302 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
303 end loop;
304 else
305 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
306 , 'x_return_status = ' || x_return_status);
307 end if;
308 end if;
309 when others then
310 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
311 then
312 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
313 end if;
314 end create_phone_contact_point;
315 --
316 -------------------------------------------------------------------------------
317 procedure create_email_contact_point(
318 p_ldap_message in fnd_oid_util.ldap_message_type
319 , x_return_status out nocopy varchar2
320 ) is
321
322 l_module_source varchar2(256);
323 p_init_msg_list varchar2(2000);
324 l_tca_error exception;
325 x_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
326 p_email_rec hz_contact_point_v2pub.email_rec_type;
327 x_contact_point_id number;
328 x_msg_count number;
329 x_msg_data varchar2(2000);
330
331 begin
332 l_module_source := G_MODULE_SOURCE || 'create_email_contact_point';
333
334 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
335 then
336 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
337 end if;
338
339 fnd_oid_users.get_contact_point_rec(
340 p_ldap_message => p_ldap_message,
341 p_contact_point_type => G_EMAIL,
342 p_contact_point_purpose => NULL,
343 p_action_type => G_CREATE,
344 x_contact_point_rec => x_contact_point_rec,
345 x_return_status => x_return_status);
346
347 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
348 then
349 raise l_tca_error;
350 end if;
351
352 p_email_rec.email_address := p_ldap_message.mail;
353
354 hz_contact_point_v2pub.create_email_contact_point(
355 p_init_msg_list => fnd_api.G_TRUE,
356 p_contact_point_rec => x_contact_point_rec,
357 p_email_rec => p_email_rec,
358 x_contact_point_id => x_contact_point_id,
359 x_return_status => x_return_status,
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data);
362
363 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
364 then
365 raise l_tca_error;
366 end if;
367
368 fnd_oid_users.create_orig_system_reference(
369 p_ldap_message => p_ldap_message,
370 p_tag => G_EMAIL,
371 p_owner_table_name => G_HZ_CONTACT_POINTS,
372 p_owner_table_id => x_contact_point_id,
373 p_status => G_ACTIVE,
374 x_return_status => x_return_status);
375
376 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
377 then
378 raise l_tca_error;
379 end if;
380
381 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
382 then
383 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
384 end if;
385
386 exception
387 when l_tca_error then
388 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
389 then
390 if (x_msg_count > 0)
391 then
392 for i in 1..x_msg_count
393 loop
394 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
395 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
396 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
397 end loop;
398 else
399 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
400 , 'x_return_status = ' || x_return_status);
401 end if;
402 end if;
403 when others then
404 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
405 then
406 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
407 end if;
408 end create_email_contact_point;
409 --
410 -------------------------------------------------------------------------------
411 procedure create_location(
412 p_ldap_message in fnd_oid_util.ldap_message_type
413 , x_return_status out nocopy varchar2
414 ) is
415
416 l_module_source varchar2(256);
417 p_init_msg_list varchar2(2000);
418 l_tca_error exception;
419 x_location_rec hz_location_v2pub.location_rec_type;
420 x_msg_count number;
421 x_msg_data varchar2(2000);
422 x_location_id number;
423
424 begin
425 l_module_source := G_MODULE_SOURCE || 'create_location';
426
427 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
428
429 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
430 then
431 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
432 end if;
433
434 fnd_oid_users.get_location_rec(p_ldap_message => p_ldap_message,
435 x_location_rec => x_location_rec);
436 --Verify whether country is null since get_location_rec may nullify country
437 if (x_location_rec.country is not null) then
438 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
439 then
440 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Country is not null' || x_location_rec.country);
441 end if;
442 hz_location_v2pub.create_location(p_init_msg_list => fnd_api.G_TRUE,
443 p_location_rec => x_location_rec,
444 x_location_id => x_location_id,
445 x_return_status => x_return_status,
446 x_msg_count => x_msg_count,
447 x_msg_data => x_msg_data);
448
449 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
450 then
451 raise l_tca_error;
452 end if;
453
454 fnd_oid_users.create_party_site(
455 p_ldap_message => p_ldap_message,
456 p_location_id => x_location_id,
457 x_return_status => x_return_status);
458
459 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
460 then
461 raise l_tca_error;
462 end if;
463 end if;
464
465 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
466 then
467 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
468 end if;
469
470 exception
471 when l_tca_error then
472 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
473 then
474 if (x_msg_count > 0)
475 then
476 for i in 1..x_msg_count
477 loop
478 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
479 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
480 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
481 end loop;
482 else
483 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
484 , 'x_return_status = ' || x_return_status);
485 end if;
486 end if;
487 when others then
488 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
489 then
490 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
491 end if;
492 end create_location;
493 --
494 -------------------------------------------------------------------------------
495 procedure create_party_site(
496 p_ldap_message in fnd_oid_util.ldap_message_type
497 , p_location_id in number
498 , x_return_status out nocopy varchar2
499 ) is
500
501 l_module_source varchar2(256);
502 p_init_msg_list varchar2(2000);
503 l_tca_error exception;
504 x_party_site_rec hz_party_site_v2pub.party_site_rec_type;
505 x_msg_count number;
506 x_msg_data varchar2(2000);
507 x_party_site_id number;
508 x_party_site_number varchar2(2000);
509
510 begin
511 l_module_source := G_MODULE_SOURCE || 'create_party_site';
512
513 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
514 then
515 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
516 end if;
517
518 fnd_oid_users.get_party_site_rec(
519 p_ldap_message => p_ldap_message,
520 p_action_type => G_CREATE,
521 x_party_site_rec => x_party_site_rec,
522 x_return_status => x_return_status);
523 x_party_site_rec.location_id := p_location_id;
524
525 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
526 then
527 raise l_tca_error;
528 end if;
529
530 hz_party_site_v2pub.create_party_site(
531 p_init_msg_list => fnd_api.G_TRUE,
532 P_party_site_rec => x_party_site_rec,
533 x_party_site_id => x_party_site_id,
534 x_party_site_number => x_party_site_number,
535 x_return_status => x_return_status,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data);
538
539 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
540 then
541 raise l_tca_error;
542 end if;
543
544 fnd_oid_users.create_orig_system_reference(
545 p_ldap_message => p_ldap_message,
546 p_tag => G_LOCATION,
547 p_owner_table_name => G_HZ_PARTY_SITES,
548 p_owner_table_id => x_party_site_id,
549 p_status => G_ACTIVE,
550 x_return_status => x_return_status);
551
552 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
553 then
554 raise l_tca_error;
555 end if;
556
557 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
558 then
559 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
560 end if;
561
562 exception
563 when l_tca_error then
564 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
565 then
566 if (x_msg_count > 0)
567 then
568 for i in 1..x_msg_count
569 loop
570 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
571 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
572 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
573 end loop;
574 else
575 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
576 , 'x_return_status = ' || x_return_status);
577 end if;
578 end if;
579 when others then
580 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
581 then
582 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
583 end if;
584 end create_party_site;
585 --
586 -------------------------------------------------------------------------------
587 procedure hz_update(
588 p_ldap_message in fnd_oid_util.ldap_message_type
589 , x_return_status out nocopy varchar2
590 ) is
591
592 l_module_source varchar2(256);
593 l_party_id number;
594
595 begin
596 l_module_source := G_MODULE_SOURCE || 'hz_update';
597
598 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
599 then
600 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
601 end if;
602
603 if ((p_ldap_message.sn is not null and p_ldap_message.sn <> G_UNKNOWN)
604 or (p_ldap_message.givenName is not null and p_ldap_message.givenName <> G_UNKNOWN))
605 then
606 fnd_oid_users.update_party(p_ldap_message => p_ldap_message,
607 x_return_status => x_return_status);
608 end if;
609 --adding a check for '*UNKNOWN*' string as well.
610 -- bug 4411121
611 if (p_ldap_message.telephoneNumber is not null and p_ldap_message.telephoneNumber <> G_UNKNOWN)
612 then
613 fnd_oid_users.update_phone_contact_point(
614 p_ldap_message => p_ldap_message,
615 p_contact_point_purpose => G_BUSINESS,
616 x_return_status => x_return_status);
617 end if;
618
619 if (p_ldap_message.homePhone is not null and p_ldap_message.homePhone <> G_UNKNOWN)
620 then
621 fnd_oid_users.update_phone_contact_point(
622 p_ldap_message => p_ldap_message,
623 p_contact_point_purpose => G_PERSONAL,
624 x_return_status => x_return_status);
625 end if;
626
627 if (p_ldap_message.mail is not null and p_ldap_message.mail <> G_UNKNOWN)
628 then
629 fnd_oid_users.update_email_contact_point(
630 p_ldap_message => p_ldap_message,
631 x_return_status => x_return_status);
632 end if;
633
634 if ( (p_ldap_message.c is not null and p_ldap_message.c <> G_UNKNOWN) or
635 (p_ldap_message.street is not null and p_ldap_message.street <> G_UNKNOWN) or
636 (p_ldap_message.postalCode is not null and p_ldap_message.postalCode <> G_UNKNOWN) or
637 (p_ldap_message.st is not null and p_ldap_message.st <> G_UNKNOWN) or
638 (p_ldap_message.l is not null and p_ldap_message.l <> G_UNKNOWN) or
639 (p_ldap_message.physicalDeliveryOfficeName is not null and p_ldap_message.physicalDeliveryOfficeName <> G_UNKNOWN)
640 )
641 then
642 fnd_oid_users.update_party_site(p_ldap_message => p_ldap_message,
643 x_return_status => x_return_status);
644
645 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
646 then
647 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Update party site status: ' || x_return_status);
648 end if;
649
650 end if;
651
652 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
653 then
654 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
655 end if;
656
657 exception
658 when others then
659 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
660 then
661 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
662 end if;
663
664 end hz_update;
665 --
666 -------------------------------------------------------------------------------
667 procedure update_party(
668 p_ldap_message in fnd_oid_util.ldap_message_type
669 , x_return_status out nocopy varchar2
670 ) is
671
672 l_module_source varchar2(256);
673 l_object_version_number number;
674 l_tca_error exception;
675 x_person_rec hz_party_v2pub.person_rec_type;
676 x_msg_count number;
677 x_msg_data varchar2(2000);
678 x_party_id number;
679 x_party_number varchar2(2000);
680 x_profile_id number;
681
682 begin
683 l_module_source := G_MODULE_SOURCE || 'update_party';
684
685 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
686 then
687 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
688 end if;
689
690 fnd_oid_users.get_person_rec(p_ldap_message => p_ldap_message,
691 p_action_type => G_UPDATE,
692 x_person_rec => x_person_rec,
693 x_return_status => x_return_status);
694
695 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
696 then
697 raise l_tca_error;
698 end if;
699
700 select object_version_number
701 into l_object_version_number
702 from hz_parties where party_id = x_person_rec.party_rec.party_id;
703
704 hz_party_v2pub.update_person(
705 p_init_msg_list => fnd_api.G_TRUE
706 , p_person_rec => x_person_rec
707 , p_party_object_version_number => l_object_version_number
708 , x_profile_id => x_profile_id
709 , x_return_status => x_return_status
710 , x_msg_count => x_msg_count
711 , x_msg_data => x_msg_data);
712
713 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
714 then
715 raise l_tca_error;
716 end if;
717
718 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
719 then
720 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
721 end if;
722
723 exception
724 when l_tca_error then
725 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
726 then
727 if (x_msg_count > 0)
728 then
729 for i in 1..x_msg_count
730 loop
731 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
732 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
733 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
734 end loop;
735 else
736 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
737 , 'x_return_status = ' || x_return_status);
738 end if;
739 end if;
740 when others then
741 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
742 then
743 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
744 end if;
745 end update_party;
746 --
747 -------------------------------------------------------------------------------
748 procedure update_phone_contact_point(
749 p_ldap_message in fnd_oid_util.ldap_message_type
750 , p_contact_point_purpose in varchar2
751 , x_return_status out nocopy varchar2
752 ) is
753
754 l_module_source varchar2(256);
755 l_object_version_number number;
756 l_tca_error exception;
757 x_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
758 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
759 x_contact_point_id number;
760 x_msg_count number;
761 x_msg_data varchar2(2000);
762
763 begin
764 l_module_source := G_MODULE_SOURCE || 'update_phone_contact_point';
765
766 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
767 then
768 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
769 end if;
770
771 fnd_oid_users.get_contact_point_rec(
772 p_ldap_message => p_ldap_message,
773 p_contact_point_type => G_PHONE,
774 p_contact_point_purpose => p_contact_point_purpose,
775 p_action_type => G_UPDATE,
776 x_contact_point_rec => x_contact_point_rec,
777 x_return_status => x_return_status);
778 /*
779 commented this code because anytime a phone record does not exist the return status is E
780 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
781 then
782 raise l_tca_error;
783 end if;
784 */
785 if (x_contact_point_rec.contact_point_id is not null)
786 then
787
788 select object_version_number
789 into l_object_version_number
790 from hz_contact_points
791 where contact_point_id = x_contact_point_rec.contact_point_id;
792
793
794 if (p_contact_point_purpose = G_BUSINESS)
795 then
796 l_phone_rec.raw_phone_number := p_ldap_message.telephoneNumber;
797 elsif (p_contact_point_purpose = G_PERSONAL)
798 then
799 l_phone_rec.phone_number := p_ldap_message.homePhone;
800 end if;
801
802
803 hz_contact_point_v2pub.update_phone_contact_point(
804 p_init_msg_list => fnd_api.G_TRUE,
805 p_contact_point_rec => x_contact_point_rec,
806 p_phone_rec => l_phone_rec,
807 p_object_version_number =>l_object_version_number,
808 x_return_status => x_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data);
811
812 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
813 then
814 raise l_tca_error;
815 end if;
816
817 else
818
819 fnd_oid_users.create_phone_contact_point(
820 p_ldap_message => p_ldap_message,
821 p_contact_point_purpose => p_contact_point_purpose,
822 x_return_status => x_return_status);
823 end if;
824
825 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
826 then
827 raise l_tca_error;
828 end if;
829
830 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
831 then
832 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
833 end if;
834
835 exception
836 when l_tca_error then
837 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
838 then
839 if (x_msg_count > 0)
840 then
841 for i in 1..x_msg_count
842 loop
843 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
844 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
845 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
846 end loop;
847 else
848 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
849 , 'x_return_status = ' || x_return_status);
850 end if;
851 end if;
852 when others then
853 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
854 then
855 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
856 end if;
857 end update_phone_contact_point;
858 --
859 -------------------------------------------------------------------------------
860 procedure update_email_contact_point(
861 p_ldap_message in fnd_oid_util.ldap_message_type
862 , x_return_status out nocopy varchar2
863 ) is
864
865 l_module_source varchar2(256);
866 l_object_version_number number;
867 l_tca_error exception;
868 x_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
869 p_email_rec hz_contact_point_v2pub.email_rec_type;
870 x_contact_point_id number;
871 x_msg_count number;
872 x_msg_data varchar2(2000);
873
874 begin
875 l_module_source := G_MODULE_SOURCE || 'update_email_contact_point';
876
877 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
878 then
879 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
880 end if;
881
882 fnd_oid_users.get_contact_point_rec(
883 p_ldap_message => p_ldap_message,
884 p_contact_point_type => G_EMAIL,
885 p_contact_point_purpose => NULL,
886 p_action_type => G_UPDATE,
887 x_contact_point_rec => x_contact_point_rec,
888 x_return_status => x_return_status);
889 /*
890 commented this out because an E is returned if the email does not exist
891 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
892 then
893 raise l_tca_error;
894 end if;
895 */
896 if (x_contact_point_rec.contact_point_id is not null)
897 then
898
899 select object_version_number
900 into l_object_version_number
901 from hz_contact_points
902 where contact_point_id = x_contact_point_rec.contact_point_id;
903
904 p_email_rec.email_address := p_ldap_message.mail;
905
906 hz_contact_point_v2pub.update_email_contact_point(
907 p_init_msg_list => fnd_api.G_TRUE,
908 p_contact_point_rec => x_contact_point_rec,
909 p_email_rec => p_email_rec,
910 p_object_version_number =>l_object_version_number,
911 x_return_status => x_return_status,
912 x_msg_count => x_msg_count,
913 x_msg_data => x_msg_data);
914
915 else
916
917 fnd_oid_users.create_email_contact_point(p_ldap_message => p_ldap_message,
918 x_return_status => x_return_status);
919 end if;
920
921 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
922 then
923 raise l_tca_error;
924 end if;
925
926 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
927 then
928 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
929 end if;
930
931 exception
932 when l_tca_error then
933 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
934 then
935 if (x_msg_count > 0)
936 then
937 for i in 1..x_msg_count
938 loop
939 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
940 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
941 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
942 end loop;
943 else
944 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
945 , 'x_return_status = ' || x_return_status);
946 end if;
947 end if;
948 when others then
949 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
950 then
951 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
952 end if;
953 end update_email_contact_point;
954 --
955 -------------------------------------------------------------------------------
956 procedure update_party_site(
957 p_ldap_message in fnd_oid_util.ldap_message_type
958 , x_return_status out nocopy varchar2
959 ) is
960
961 l_module_source varchar2(256);
962 l_object_version_number number;
963 p_object_version_number number;
964 l_tca_error exception;
965 x_party_site_rec hz_party_site_v2pub.party_site_rec_type;
966 x_party_site_id number;
967 x_msg_count number;
968 x_msg_data varchar2(2000);
969 x_location_rec hz_location_v2pub.location_rec_type;
970 l_location_id number;
971
972 begin
973 l_module_source := G_MODULE_SOURCE || 'update_party_site';
974
975 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
976 then
977 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
978 end if;
979
980 fnd_oid_users.get_party_site_rec(
981 p_ldap_message => p_ldap_message,
982 p_action_type => G_UPDATE,
983 x_party_site_rec => x_party_site_rec,
984 x_return_status => x_return_status);
985
986 /* commenting this out as TCA returns Error if the party site is not found
987 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
988 then
989 raise l_tca_error;
990 end if;
991 */
992
993 if (x_party_site_rec.party_site_id is not null)
994 then
995
996 select location_id
997 into l_location_id
998 from hz_party_sites
999 where party_site_id = x_party_site_rec.party_site_id;
1000
1001 if (l_location_id is not NULL) then
1002 select object_version_number
1003 into l_object_version_number
1004 from hz_party_sites
1005 where party_site_id = x_party_site_rec.party_site_id;
1006
1007
1008 select object_version_number
1009 into p_object_version_number
1010 from hz_locations
1011 where location_id = l_location_id;
1012
1013 fnd_oid_users.get_location_rec(p_ldap_message => p_ldap_message,
1014 x_location_rec => x_location_rec);
1015
1016 x_location_rec.location_id := l_location_id;
1017
1018 hz_location_v2pub.update_location (
1019 p_init_msg_list => fnd_api.G_TRUE,
1020 p_location_rec => x_location_rec,
1021 p_object_version_number =>p_object_version_number,
1022 x_return_status => x_return_status,
1023 x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data);
1025
1026
1027
1028 hz_party_site_v2pub.update_party_site(
1029 p_init_msg_list => fnd_api.G_TRUE,
1030 p_party_site_rec => x_party_site_rec,
1031 p_object_version_number =>l_object_version_number,
1032 x_return_status => x_return_status,
1033 x_msg_count => x_msg_count,
1034 x_msg_data => x_msg_data);
1035
1036 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1037 then
1038 raise l_tca_error;
1039 end if;
1040 else -- no location id
1041 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1042 then
1043 raise l_tca_error;
1044 end if;
1045 end if;
1046 else
1047
1048 fnd_oid_users.create_location(p_ldap_message => p_ldap_message,
1049 x_return_status => x_return_status);
1050
1051 end if;
1052 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1053 then
1054 raise l_tca_error;
1055 end if;
1056
1057 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1058 then
1059 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1060 end if;
1061
1062 exception
1063 when l_tca_error then
1064 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1065 then
1066 if (x_msg_count > 0)
1067 then
1068 for i in 1..x_msg_count
1069 loop
1070 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
1071 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
1072 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
1073 end loop;
1074 else
1075 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1076 , 'x_return_status = ' || x_return_status);
1077 end if;
1078 end if;
1079 when others then
1080 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1081 then
1082 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1083 end if;
1084 end update_party_site;
1085 --
1086 -------------------------------------------------------------------------------
1087 procedure get_person_rec(
1088 p_ldap_message in fnd_oid_util.ldap_message_type
1089 , p_action_type in varchar2
1090 , x_person_rec out nocopy hz_party_v2pub.person_rec_type
1091 , x_return_status out nocopy varchar2
1092 ) is
1093
1094 l_module_source varchar2(256);
1095 l_profile_defined boolean;
1096 l_party_number number;
1097 l_generate_party_number varchar2(1);
1098 l_party_rec hz_party_v2pub.party_rec_type;
1099 l_object_version_number number;
1100 l_owner_table_id number;
1101 l_orig_system_reference varchar2(200);
1102 l_tca_error exception;
1103
1104 begin
1105 l_module_source := G_MODULE_SOURCE || 'get_person_rec';
1106
1107 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1108 then
1109 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1110 end if;
1111
1112 if (p_action_type = G_CREATE)
1113 then
1114
1115 fnd_profile.get_specific(name_z => G_HZ_GENERATE_PARTY_NUMBER,
1116 val_z => l_generate_party_number,
1117 defined_z => l_profile_defined);
1118
1119 if (l_generate_party_number = G_NO)
1120 then
1121 select hz_party_number_s.nextval
1122 into l_party_number
1123 from dual;
1124 l_party_rec.party_number := l_party_number;
1125 end if;
1126
1127 x_person_rec.created_by_module := G_FND_OID_SYNCH;
1128
1129 elsif (p_action_type = G_UPDATE)
1130 then
1131 fnd_oid_users.get_orig_system_ref(
1132 p_ldap_message => p_ldap_message,
1133 p_tag => G_PERSON,
1134 x_reference => l_orig_system_reference);
1135 hz_orig_system_ref_pub.get_owner_table_id(
1136 p_orig_system => G_FND_OID_SYNCH
1137 , p_orig_system_reference => l_orig_system_reference
1138 , p_owner_table_name => G_HZ_PARTIES
1139 , x_owner_table_id => l_owner_table_id
1140 , x_return_status => x_return_status);
1141
1142 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1143 then
1144 raise l_tca_error;
1145 end if;
1146
1147 l_party_rec.party_id := l_owner_table_id;
1148 end if;
1149
1150 x_person_rec.party_rec := l_party_rec;
1151
1152 if (p_ldap_message.sn is not null)
1153 then
1154 x_person_rec.person_last_name := p_ldap_message.sn;
1155 end if;
1156
1157 if (p_ldap_message.givenName is not null)
1158 then
1159 x_person_rec.person_first_name := p_ldap_message.givenName;
1160 end if;
1161
1162 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1163
1164 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1165 then
1166 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1167 end if;
1168
1169 exception
1170 when l_tca_error then
1171 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1172 then
1173 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1174 , 'Error calling hz_orig_system_ref_pub.get_owner_table_id');
1175 end if;
1176 when others then
1177 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1178 then
1179 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1180 end if;
1181 end get_person_rec;
1182 --
1183 -------------------------------------------------------------------------------
1184 procedure get_contact_point_rec(
1185 p_ldap_message in fnd_oid_util.ldap_message_type
1186 , p_contact_point_type in varchar2
1187 , p_contact_point_purpose in varchar2
1188 , p_action_type in varchar2
1189 , x_contact_point_rec out nocopy
1190 hz_contact_point_v2pub.contact_point_rec_type
1191 , x_return_status out nocopy varchar2
1192 ) is
1193
1194 l_module_source varchar2(256);
1195 l_party_id number;
1196 l_object_version_number number;
1197 l_orig_system_reference varchar2(200);
1198 l_owner_table_id number;
1199 l_tca_error exception;
1200
1201 begin
1202 l_module_source := G_MODULE_SOURCE || 'get_contact_point_rec';
1203
1204 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1205 then
1206 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1207 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1208 , 'p_action_type = ' || p_action_type );
1209 end if;
1210
1211 if (p_action_type = G_CREATE)
1212 then
1213
1214 select person_party_id
1215 into l_party_id
1216 from fnd_user where user_name = p_ldap_message.object_name;
1217
1218 x_contact_point_rec.owner_table_id := l_party_id;
1219 x_contact_point_rec.contact_point_type := p_contact_point_type;
1220 if (p_contact_point_type = G_PHONE)
1221 then
1222 x_contact_point_rec.contact_point_purpose := p_contact_point_purpose;
1223 end if;
1224 x_contact_point_rec.owner_table_name := G_HZ_PARTIES;
1225 x_contact_point_rec.created_by_module := G_FND_OID_SYNCH;
1226 x_contact_point_rec.status := G_ACTIVE;
1227
1228 elsif (p_action_type = G_UPDATE)
1229 then
1230
1231 /* commenting out this line because we want to update the existing phone record and not inactivate it.
1232 x_contact_point_rec.status := G_INACTIVE;
1233 */
1234
1235 if (p_contact_point_type = G_PHONE)
1236 then
1237 fnd_oid_users.get_orig_system_ref(
1238 p_ldap_message => p_ldap_message,
1239 p_tag => p_contact_point_purpose,
1240 x_reference => l_orig_system_reference);
1241 else
1242 fnd_oid_users.get_orig_system_ref(
1243 p_ldap_message => p_ldap_message,
1244 p_tag => p_contact_point_type,
1245 x_reference => l_orig_system_reference);
1246 end if;
1247 hz_orig_system_ref_pub.get_owner_table_id(
1248 p_orig_system => G_FND_OID_SYNCH,
1249 p_orig_system_reference => l_orig_system_reference,
1250 p_owner_table_name => G_HZ_CONTACT_POINTS,
1251 x_owner_table_id => l_owner_table_id,
1252 x_return_status => x_return_status);
1253
1254 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1255 then
1256 raise l_tca_error;
1257 end if;
1258
1259 x_contact_point_rec.contact_point_id := l_owner_table_id;
1260
1261 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1262 then
1263 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1264 end if;
1265
1266 end if;
1267
1268 exception
1269 when l_tca_error then
1270 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1271 then
1272 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1273 , 'Error calling hz_orig_system_ref_pub.get_owner_table_id');
1274 end if;
1275 when others then
1276 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1277 then
1278 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1279 end if;
1280 end get_contact_point_rec;
1281 --
1282 -------------------------------------------------------------------------------
1283 procedure get_location_rec(
1284 p_ldap_message in fnd_oid_util.ldap_message_type
1285 , x_location_rec out nocopy hz_location_v2pub.location_rec_type
1286 ) is
1287
1288 cursor cur_fnd_territories is
1289 SELECT TERRITORY_CODE
1290 FROM FND_TERRITORIES_VL
1291 WHERE TERRITORY_CODE = p_ldap_message.c
1292 OR TERRITORY_SHORT_NAME = p_ldap_message.c
1293 OR DESCRIPTION = p_ldap_message.c;
1294
1295 l_module_source varchar2(256);
1296 l_territory_code fnd_territories.TERRITORY_CODE%type;
1297 l_found boolean;
1298
1299 begin
1300 l_module_source := G_MODULE_SOURCE || 'get_location_rec';
1301
1302 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1303 then
1304 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1305 end if;
1306
1307 l_found := false;
1308 l_territory_code := null;
1309 if (p_ldap_message.c is not null)
1310 then
1311 --The following logic should removed once OID ensures that they send the correct
1312 --"c" attribute based on the LDAP standard
1313 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1314 then
1315 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1316 'Country from OID: '||p_ldap_message.c);
1317 end if;
1318
1319 open cur_fnd_territories;
1320 fetch cur_fnd_territories into l_territory_code;
1321 l_found := cur_fnd_territories%found;
1322
1323 if(l_found)
1324 then
1325 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1326 then
1327 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1328 'Setting country: '||l_territory_code);
1329 end if;
1330 x_location_rec.country := l_territory_code;
1331 else
1332 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1333 then
1334 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1335 'Setting country to null');
1336 end if;
1337 x_location_rec.country := null;
1338 end if;
1339 close cur_fnd_territories;
1340 end if;
1341
1342 if (p_ldap_message.street is not null) then
1343 x_location_rec.address1 := p_ldap_message.street;
1344 end if;
1345
1346 if (p_ldap_message.postalCode is not null) then
1347 x_location_rec.postal_code := p_ldap_message.postalCode;
1348 end if;
1349
1350 if (p_ldap_message.st is not null) then
1351 x_location_rec.state := p_ldap_message.st;
1352 end if;
1353
1354 if (p_ldap_message.l is not null) then
1355 x_location_rec.city := p_ldap_message.l;
1356 end if;
1357
1358 x_location_rec.created_by_module := G_FND_OID_SYNCH;
1359
1360 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1361 then
1362 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1363 end if;
1364
1365 exception
1366 when others then
1367 if (cur_fnd_territories%isopen)
1368 then
1369 close cur_fnd_territories;
1370 end if;
1371
1372 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1373 then
1374 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1375 end if;
1376 end get_location_rec;
1377 --
1378 -------------------------------------------------------------------------------
1379 procedure get_party_site_rec(
1380 p_ldap_message in fnd_oid_util.ldap_message_type
1381 , p_action_type in varchar2
1382 , x_party_site_rec out nocopy hz_party_site_v2pub.party_site_rec_type
1383 , x_return_status out nocopy varchar2
1384 ) is
1385
1386 l_module_source varchar2(256);
1387 l_profile_defined boolean;
1388 l_party_id number;
1389 l_party_site_number number;
1390 l_generate_ps_number varchar2(1);
1391 l_object_version_number number;
1392 l_owner_table_id number;
1393 l_orig_system_reference varchar2(200);
1394 l_tca_error exception;
1395
1396 begin
1397 l_module_source := G_MODULE_SOURCE || 'get_party_site_rec';
1398
1399 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1400 then
1401 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1402 end if;
1403
1404 if (p_action_type = G_CREATE)
1405 then
1406 fnd_profile.get_specific(name_z => G_HZ_GENERATE_PS_NUMBER,
1407 val_z => l_generate_ps_number,
1408 defined_z => l_profile_defined);
1409
1410 if (l_generate_ps_number = G_NO)
1411 then
1412 select hz_party_site_number_s.nextval
1413 into l_party_site_number
1414 from dual;
1415 x_party_site_rec.party_site_number := l_party_site_number;
1416 end if;
1417
1418 select person_party_id
1419 into l_party_id
1420 from fnd_user where user_name = p_ldap_message.object_name;
1421
1422 x_party_site_rec.party_id := l_party_id;
1423
1424 elsif (p_action_type = G_UPDATE)
1425 then
1426 fnd_oid_users.get_orig_system_ref(
1427 p_ldap_message => p_ldap_message,
1428 p_tag => G_LOCATION,
1429 x_reference => l_orig_system_reference);
1430 hz_orig_system_ref_pub.get_owner_table_id(
1431 p_orig_system => G_FND_OID_SYNCH
1432 , p_orig_system_reference => l_orig_system_reference
1433 , p_owner_table_name => G_HZ_PARTY_SITES
1434 , x_owner_table_id => l_owner_table_id
1435 , x_return_status => x_return_status);
1436
1437 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1438 then
1439 raise l_tca_error;
1440 end if;
1441
1442 x_party_site_rec.party_site_id := l_owner_table_id;
1443 -- x_party_site_rec.status := G_INACTIVE;
1444 end if;
1445
1446 if (p_ldap_message.physicalDeliveryOfficeName is not null) then
1447 x_party_site_rec.mailstop := p_ldap_message.physicalDeliveryOfficeName;
1448 end if;
1449
1450 x_party_site_rec.created_by_module := G_FND_OID_SYNCH;
1451
1452 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1453
1454 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1455 then
1456 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1457 end if;
1458
1459 exception
1460 when l_tca_error then
1461 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1462 then
1463 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1464 , 'Error calling hz_orig_system_ref_pub.get_owner_table_id');
1465 end if;
1466 when others then
1467 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1468 then
1469 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1470 end if;
1471 end get_party_site_rec;
1472 --
1473 -------------------------------------------------------------------------------
1474 procedure get_orig_system_ref(
1475 p_ldap_message in fnd_oid_util.ldap_message_type
1476 , p_tag in varchar2
1477 , x_reference out nocopy varchar2
1478 ) is
1479
1480 l_module_source varchar2(256);
1481 l_user_id number;
1482
1483 begin
1484 l_module_source := G_MODULE_SOURCE || 'get_orig_system_ref';
1485
1486 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1487 then
1488 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1489 end if;
1490
1491 select user_id
1492 into l_user_id
1493 from fnd_user where user_name = upper(p_ldap_message.object_name);
1494
1495 x_reference := p_tag || l_user_id;
1496
1497 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1498 then
1499 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1500 , 'Orig System Reference = ' || x_reference);
1501 end if;
1502
1503 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1504 then
1505 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1506 end if;
1507
1508 exception
1509 when others then
1510 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1511 then
1512 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1513 end if;
1514 end get_orig_system_ref;
1515 --
1516 -------------------------------------------------------------------------------
1517 procedure create_orig_system_reference(
1518 p_ldap_message in fnd_oid_util.ldap_message_type
1519 , p_tag in varchar2
1520 , p_owner_table_name in varchar2
1521 , p_owner_table_id in number
1522 , p_status in varchar2
1523 , x_return_status out nocopy varchar2
1524 ) is
1525
1526 l_module_source varchar2(256);
1527 l_orig_sys_rec hz_orig_system_ref_pub.orig_sys_reference_rec_type;
1528 l_orig_system_reference varchar2(2000);
1529 l_owner_table_id number;
1530 l_tca_error exception;
1531 l_debug_level number;
1532 l_proc_level number;
1533 x_msg_count number;
1534 x_msg_data varchar2(2000);
1535
1536 begin
1537 l_module_source := G_MODULE_SOURCE || 'create_orig_sys_reference_rec';
1538 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1539 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
1540
1541 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1542 then
1543 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1544 end if;
1545
1546 fnd_oid_users.get_orig_system_ref(
1547 p_ldap_message => p_ldap_message,
1548 p_tag => p_tag,
1549 x_reference => l_orig_system_reference);
1550
1551 l_orig_sys_rec.orig_system := G_FND_OID_SYNCH;
1552 l_orig_sys_rec.orig_system_reference := l_orig_system_reference;
1553 l_orig_sys_rec.owner_table_name := p_owner_table_name;
1554 l_orig_sys_rec.owner_table_id := p_owner_table_id;
1555 l_orig_sys_rec.status := p_status;
1556 l_orig_sys_rec.start_date_active := sysdate;
1557 l_orig_sys_rec.created_by_module := G_FND_OID_SYNCH;
1558
1559 hz_orig_system_ref_pub.get_owner_table_id(
1560 p_orig_system => G_FND_OID_SYNCH,
1561 p_orig_system_reference => l_orig_system_reference,
1562 p_owner_table_name => p_owner_table_name,
1563 x_owner_table_id => l_owner_table_id,
1564 x_return_status => x_return_status);
1565
1566 if (x_return_status = fnd_api.G_RET_STS_UNEXP_ERROR)
1567 then
1568 raise l_tca_error;
1569 end if;
1570
1571 if (l_owner_table_id is not null)
1572 then
1573
1574 fnd_oid_users.update_orig_system_reference(
1575 p_ldap_message => p_ldap_message,
1576 p_tag => p_tag,
1577 p_owner_table_name => p_owner_table_name,
1578 p_owner_table_id => p_owner_table_id,
1579 p_status => G_ACTIVE,
1580 x_return_status => x_return_status);
1581
1582 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1583 then
1584 raise l_tca_error;
1585 end if;
1586
1587 else
1588
1589 hz_orig_system_ref_pub.create_orig_system_reference(
1590 p_init_msg_list => fnd_api.G_TRUE,
1591 p_orig_sys_reference_rec => l_orig_sys_rec,
1592 x_return_status => x_return_status,
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data);
1595
1596 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1597 then
1598 raise l_tca_error;
1599 end if;
1600
1601 end if;
1602
1603 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1604 then
1605 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1606 end if;
1607
1608 exception
1609 when l_tca_error then
1610 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1611 then
1612 if (x_msg_count > 0)
1613 then
1614 for i in 1..x_msg_count
1615 loop
1616 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
1617 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
1618 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
1619 end loop;
1620 else
1621 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1622 , 'x_return_status = ' || x_return_status);
1623 end if;
1624 end if;
1625 when others then
1626 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1627 then
1628 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1629 end if;
1630 end create_orig_system_reference;
1631 --
1632 -------------------------------------------------------------------------------
1633 procedure update_orig_system_reference(
1634 p_ldap_message in fnd_oid_util.ldap_message_type
1635 , p_tag in varchar2
1636 , p_owner_table_name in varchar2
1637 , p_owner_table_id in number
1638 , p_status in varchar2
1639 , x_return_status out nocopy varchar2
1640 ) is
1641
1642 l_module_source varchar2(256);
1643 l_orig_system_reference varchar2(2000);
1644 l_orig_sys_rec hz_orig_system_ref_pub.orig_sys_reference_rec_type;
1645 l_orig_system_ref_id number;
1646 l_object_version_number number;
1647 l_tca_error exception;
1648 x_msg_count number;
1649 x_msg_data varchar2(2000);
1650
1651 begin
1652 l_module_source := G_MODULE_SOURCE || 'get_orig_sys_reference_rec';
1653
1654 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1655 then
1656 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1657 end if;
1658
1659 l_orig_sys_rec.orig_system := G_FND_OID_SYNCH;
1660 fnd_oid_users.get_orig_system_ref(
1661 p_ldap_message => p_ldap_message,
1662 p_tag => p_tag,
1663 x_reference => l_orig_system_reference);
1664
1665 select orig_system_ref_id
1666 into l_orig_system_ref_id
1667 from hz_orig_sys_references
1668 where orig_system_reference = l_orig_system_reference;
1669
1670 select object_version_number
1671 into l_object_version_number
1672 from hz_orig_sys_references
1673 where orig_system_ref_id = l_orig_system_ref_id;
1674
1675 l_orig_sys_rec.orig_system_ref_id := l_orig_system_ref_id;
1676 l_orig_sys_rec.orig_system_reference := l_orig_system_reference;
1677 l_orig_sys_rec.owner_table_name := p_owner_table_name;
1678 l_orig_sys_rec.owner_table_id := p_owner_table_id;
1679
1680 hz_orig_system_ref_pub.update_orig_system_reference(
1681 p_init_msg_list => fnd_api.G_TRUE,
1682 p_orig_sys_reference_rec => l_orig_sys_rec,
1683 p_object_version_number => l_object_version_number,
1684 x_return_status => x_return_status,
1685 x_msg_count => x_msg_count,
1686 x_msg_data => x_msg_data);
1687
1688 if (x_return_status <> fnd_api.G_RET_STS_SUCCESS)
1689 then
1690 raise l_tca_error;
1691 end if;
1692
1693 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1694 then
1695 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1696 end if;
1697
1698 exception
1699 when l_tca_error then
1700 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1701 then
1702 if (x_msg_count > 0)
1703 then
1704 for i in 1..x_msg_count
1705 loop
1706 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, x_msg_data);
1707 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, i || '.'
1708 || SubStr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
1709 end loop;
1710 else
1711 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1712 , 'x_return_status = ' || x_return_status);
1713 end if;
1714 end if;
1715 when others then
1716 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1717 then
1718 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1719 end if;
1720 end update_orig_system_reference;
1721 --
1722 -------------------------------------------------------------------------------
1723 procedure test is
1724
1725 l_module_source varchar2(256);
1726 l_ldap_record fnd_oid_util.ldap_message_type;
1727 l_return_status varchar2(2000);
1728
1729 begin
1730 l_module_source := G_MODULE_SOURCE || 'test: ';
1731
1732 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1733 then
1734 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1735 end if;
1736
1737 l_ldap_record.sn := 'FAZIL';
1738 l_ldap_record.object_name := 'FND95.2';
1739 l_ldap_record.givenName := 'SAAD';
1740
1741 l_ldap_record.telephoneNumber := '333';
1742 l_ldap_record.homePhone := '222';
1743 l_ldap_record.mail := '[email protected]';
1744
1745 l_ldap_record.c := 'US';
1746 l_ldap_record.street := '600 orcl pkwy';
1747 l_ldap_record.postalCode := '94065';
1748 l_ldap_record.st := 'ca';
1749 l_ldap_record.l := 'rs';
1750
1751 --fnd_oid_users.hz_create(p_ldap_message => l_ldap_record,
1752 --x_return_status => l_return_status);
1753 fnd_oid_users.hz_update(p_ldap_message => l_ldap_record,
1754 x_return_status => l_return_status);
1755 commit;
1756
1757 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1758 then
1759 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1760 end if;
1761
1762 exception
1763 when others then
1764 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1765 then
1766 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
1767 end if;
1768 end test;
1769 --
1770 -------------------------------------------------------------------------------
1771 end fnd_oid_users;