[Home] [Help]
PACKAGE BODY: APPS.AMS_REGISTRANTS_PVT
Source
1 PACKAGE BODY AMS_Registrants_PVT AS
2 /* $Header: amsvevrb.pls 120.5 2005/12/07 21:36:50 sikalyan ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30):='AMS_Registrants_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvevrb.pls';
5 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6
7 -----------------------------------------------------------
8 -- PACKAGE
9 -- AMS_Registrants_PVT
10 --
11 -- PURPOSE
12 --
13 -- PROCEDURES
14 -- find_a_party
15 --
16 -- PARAMETERS
17 -- INPUT
18 -- p_rec AMS_ListImport_PVT.party_detail_rec_type%TYPE;
19 --
20 -- OUTPUT
21
22 -- HISTORY
23 -- 15-JAN-2002 mukumar Created.
24 -- 25-Feb-2002 ptendulk Added Function Get_Event_Det
25 -- 07-MAR-2002 dcastlem Added person_party_echeck
26 -- 12-MAR-2002 dcastlem Added support for general Public API
27 -- (AMS_Registrants_PUB)
28 -- 18-MAR-2002 dcastlem Cleaned up some code in B2B and added
29 -- org party id as an out parameter
30 -- 05-APR-2002 dcastlem Rewrote party_detail_rec_type to include all fields
31 -- 11-dec-2002 soagrawa Modified get_party_id to call List Import's v2 apis for create_customer
32 -- 31-jan-2003 soagrawa Get_Event_det : Fixed P1 bug# 2779298 - canot register for CSCH of type events
33 -- 11-feb-2003 soagrawa Modified call to create_customer_API , now passing web_rec as well
34 -- 07-jan-2004 soagrawa Fixed bug# 2836598 about duplicate error msgs
35 -- 12-Aug-2004 sikalyan TCA V2 API update
36 -- 13-Sep-2005 sikalyan TCA Mandate Created_by_module
37 -- 28-Sep-2005 vmodur More fixes for Created_By_Module
38 -- 08-Dec-2005 sikalyan TCA Obsolete Columns BugFix 4665060
39 -- -------------------------------------------------------------------------------------------------------
40 --
41 --
42 -- This procedure is used to create party records in ams_party_sources table.
43 --
44 --
45
46
47 --
48 -- This procedure is used for existence checking for party of type person.
49 --
50 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
51 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
52 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
53
54
55 --=============================================================================
56 -- Start of Comment
57 --=============================================================================
58 --API Name
59 -- Write_log
60 --Type
61 -- Public
62 --Purpose
63 -- Used to write logs for this API
64 --Author
65 -- Dhirendra Singh
66 --=============================================================================
67 --
68 PROCEDURE Write_log (p_api_name IN VARCHAR2,
69 p_log_message IN VARCHAR2)
70 IS
71 l_api_name VARCHAR(30);
72 l_log_msg VARCHAR(2000);
73 BEGIN
74 l_api_name := p_api_name;
75 l_log_msg := p_log_message;
76
77 IF (AMS_DEBUG_HIGH_ON)
78 THEN AMS_Utility_PVT.debug_message(p_log_message);
79 END IF;
80
81 AMS_Utility_PVT.debug_message(
82 p_log_level => g_log_level,
83 p_module_name => G_FILE_NAME ||'.'||g_pkg_name||'.'||l_api_name||'.',
84 p_text => p_log_message
85 );
86
87 --EXCEPTION
88 -- currently no exception handled
89
90 END Write_log;
91
92
93 PROCEDURE person_party_echeck( p_party_id IN OUT NOCOPY NUMBER
94 , p_per_first_name IN VARCHAR2
95 , p_per_last_name IN VARCHAR2
96 , p_address1 IN VARCHAR2
97 , p_country IN VARCHAR2
98 , p_email_address IN VARCHAR2
99 , p_ph_country_code IN VARCHAR2
100 , p_ph_area_code IN VARCHAR2
101 , p_ph_number IN VARCHAR2
102 );
103
104 --
105 -- This procedure is used for existence checking for party of type organization.
106 --
107 PROCEDURE party_echeck(
108 --p_impt_list_header_id IN NUMBER,
109 p_party_id IN OUT NOCOPY NUMBER,
110 p_org_name IN VARCHAR2,
111 p_per_first_name IN VARCHAR2,
112 p_per_last_name IN VARCHAR2,
113 p_address1 IN VARCHAR2,
114 p_country IN VARCHAR2
115 );
116
117 --
118 -- This procedure is used for existence checking for contact.
119 --
120 --
121 PROCEDURE contact_echeck(
122 --p_impt_list_header_id IN NUMBER,
123 p_party_id IN OUT NOCOPY NUMBER,
124 p_org_party_id IN NUMBER,
125 p_per_first_name IN VARCHAR2,
126 p_per_last_name IN VARCHAR2,
127 p_phone_area_code IN VARCHAR2,
128 p_phone_number IN VARCHAR2,
129 p_phone_extension IN VARCHAR2,
130 p_email_address IN VARCHAR2
131 );
132
133 --
134 -- This procedure is used for existence checking for address.
135 --
136 --
137 PROCEDURE address_echeck(
138 --p_impt_list_header_id IN NUMBER,
139 p_party_id IN NUMBER,
140 p_location_id IN OUT NOCOPY NUMBER,
141 p_address1 IN VARCHAR2,
142 p_city IN VARCHAR2,
143 p_pcode IN VARCHAR2,
144 p_country IN VARCHAR2
145 );
146
147 -- ---------------------------------------------------------
148 -- This concurrent program populates the data to TCA tables
149 -- from OMO table.
150 --
151
152
153
154 /*===========================================================*/
155 PROCEDURE find_a_party(
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
158 p_rec IN party_detail_rec_type,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 x_party_id OUT NOCOPY NUMBER
163 ) IS
164
165 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
166 L_API_NAME CONSTANT VARCHAR2(30) := 'find_a_party';
167
168 l_ret_status varchar(1);
169 l_per_party_id number;
170 l_party_key varchar(1000);
171 l_cust_exists varchar(1);
172 l_email_party_id number;
173 l_phone_party_id number;
174 l_count number;
175 l_max_party_id number;
176 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
177 l_stat NUMBER := 0;
178 l_party_id NUMBER;
179
180 NO_EMAIL_FOUND CONSTANT NUMBER := 10;
181
182
183 CURSOR PARTY_REL_EXISTS(id_in in NUMBER) IS
184 SELECT subject_id FROM hz_relationships
185 WHERE relationship_id = id_in
186 AND subject_type = 'PERSON';
187
188
189 /*CURSOR LOCATION_EXISTS IS
190 SELECT party_site_id FROM hz_party_sites
191 WHERE party_id = x_org_party_id
192 AND location_id = x_location_id;
193
194 CURSOR PER_LOCATION_EXISTS IS
195 SELECT party_site_id FROM hz_party_sites
196 WHERE party_id = x_per_party_id
197 AND location_id = x_location_id;
198
199 CURSOR CHECK_PSITE_EXISTS IS
200 SELECT party_site_id FROM hz_party_sites
201 WHERE party_id = x_party_rel_party_id
202 AND location_id = x_location_id;
203 */
204 CURSOR phone_exists (party_id_in number, phone_number VARCHAR2, country_code in VARCHAR2, area_code in VARCHAR2,
205 extension in VARCHAR2 ) IS
206 SELECT 'Y' FROM hz_contact_points
207 WHERE contact_point_type = 'PHONE'
208 AND phone_line_type = 'GEN'
209 AND owner_table_name = 'HZ_PARTIES'
210 AND owner_table_id = party_id_in
211 AND phone_number = phone_number
212 AND NVL(phone_country_code,'x') = NVL(country_code,'x')
213 AND NVL(phone_area_code,'x') = NVL(area_code,'x')
214 AND NVL(phone_extension,'x') = NVL(extension,'x');
215
216
217 CURSOR email_exists (email_address_in VARCHAR2) IS
218 SELECT owner_table_id
219 FROM hz_contact_points
220 WHERE contact_point_type = 'EMAIL'
221 AND owner_table_name = 'HZ_PARTIES'
222 AND upper(email_address) = upper(email_address_in);
223
224 cursor c_person_exists(id_in IN NUMBER) is
225 select 'Y' from hz_parties
226 where customer_key = l_party_key
227 and party_type = 'PERSON'
228 and party_id = id_in;
229
230
231 l_count number;
232 l_max_party_id number;
233 l_per_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
234
235 begin
236 /*
237 IF (AMS_DEBUG_HIGH_ON) THEN
238 AMS_Utility_PVT.Debug_Message('Start of Get Party Id');
239 END IF;
240 */
241 Write_log(L_API_NAME, 'Start of Get Party Id');
242
243 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
244 p_api_version,
245 l_api_name,
246 G_PKG_NAME)
247 THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END IF;
250
251 -- Initialize message list if p_init_msg_list is set to TRUE.
252 IF FND_API.to_Boolean( p_init_msg_list )
253 THEN
254 FND_MSG_PUB.initialize;
255 END IF;
256
257
258 x_return_status := FND_API.g_ret_sts_success;
259 if p_rec.last_name is not null and p_rec.first_name is not null then
260 l_party_key := hz_fuzzy_pub.Generate_Key (
261 p_key_type => 'PERSON',
262 p_first_name => p_rec.first_name,
263 p_last_name => p_rec.last_name
264 );
265 else
266 FND_MESSAGE.set_name('AMS', 'AMS_NO_NAME_PROVIDED');
267 FND_MSG_PUB.add;
268 RAISE FND_API.g_exc_error;
269 end if;
270
271 if p_rec.EMAIL_ADDRESS is not null THEN
272 open email_exists(p_rec.EMAIL_ADDRESS);
273 fetch email_exists into l_per_party_id;
274 if email_exists%NOTFOUND THEN
275 /*
276 IF (AMS_DEBUG_HIGH_ON) THEN
277 AMS_Utility_PVT.Debug_Message('Email Not Found');
278 END IF;
279 */
280 Write_log(L_API_NAME, 'Email Not Found');
281 l_stat := NO_EMAIL_FOUND;
282 close email_exists;
283 else
284 LOOP
285 exit WHEN email_exists%NOTFOUND;
286 open PARTY_REL_EXISTS(l_per_party_id);
287 fetch PARTY_REL_EXISTS into l_party_id;
288 IF PARTY_REL_EXISTS%NOTFOUND THEN
289 /*
290 IF (AMS_DEBUG_HIGH_ON) THEN
291 AMS_Utility_PVT.Debug_Message('Relation ship does not exist');
292 END IF;
293 */
294 Write_log(L_API_NAME, 'Relation ship does not exist');
295
296 l_party_id := l_per_party_id;
297 END IF;
298 open c_person_exists(l_party_id);
299 fetch c_person_exists into l_cust_exists;
300 close c_person_exists;
301 if l_cust_exists = 'Y'
302 then
303 x_party_id := l_party_id;
304 /*
305 IF (AMS_DEBUG_HIGH_ON) THEN
306
307 AMS_Utility_PVT.Debug_Message('Person exist');
308 END IF;
309 */
310 Write_log(L_API_NAME, 'Person exist');
311 else
312 x_party_id := null;
313 /*
314 IF (AMS_DEBUG_HIGH_ON) THEN
315
316 AMS_Utility_PVT.Debug_Message('Person does not exist');
317 END IF;
318 */
319 Write_log(L_API_NAME, 'Person does not exist');
320 end if;
321 fetch email_exists into l_per_party_id;
322 END LOOP;
323 close email_exists;
324 end if;
325 else
326 FND_MESSAGE.set_name('AMS', 'AMS_NO_EMAIL_PROVIDED');
327 FND_MSG_PUB.add;
328 RAISE FND_API.g_exc_error;
329 end if;
330 EXCEPTION
331 WHEN FND_API.g_exc_error THEN
332 x_return_status := FND_API.g_ret_sts_error;
333 FND_MSG_PUB.count_and_get(
334 p_encoded => FND_API.g_false,
335 p_count => x_msg_count,
336 p_data => x_msg_data
337 );
338
339 WHEN FND_API.g_exc_unexpected_error THEN
340 x_return_status := FND_API.g_ret_sts_unexp_error ;
341 FND_MSG_PUB.count_and_get(
342 p_encoded => FND_API.g_false,
343 p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346 WHEN others THEN
347 FND_MSG_PUB.count_and_get(
348 p_encoded => FND_API.g_false,
349 p_count => x_msg_count,
350 p_data => x_msg_data
351 );
352 END find_a_party;
353
354 /*==========================================================*/
355
356 PROCEDURE create_registrant_party(
357 p_api_version IN NUMBER,
358 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
359 p_commit IN VARCHAR2 := FND_API.g_false,
360 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
361 p_rec IN party_detail_rec_type,
362
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_count OUT NOCOPY NUMBER,
365 x_msg_data OUT NOCOPY VARCHAR2,
366
367 x_new_party_id OUT NOCOPY NUMBER,
368 x_new_org_party_id OUT NOCOPY NUMBER
369
370 ) IS
371
372 x_generate_party_number VARCHAR2(1);
373 x_gen_contact_number VARCHAR2(1);
374 x_gen_party_site_number VARCHAR2(1);
375 x_party_number VARCHAR2(30);
376 x_organization_profile_id number;
377 x_person_profile_id number;
378 x_org_party_id number;
379 x_tmp_var VARCHAR2(2000);
380 x_tmp_var1 VARCHAR2(2000);
381 x_per_party_id number;
382 x_party_relationship_id number;
383 x_contact_number VARCHAR2(30);
384 x_org_contact_id number;
385 x_party_rel_party_id number;
386 x_location_id number;
387 x_Party_site_id number;
388 x_Party_site_use_id number;
389 x_party_site_number VARCHAR2(30);
390 x_contact_point_id number;
391 x_email_address varchar2(2000);
392 x_phone_country_code VARCHAR2(10);
393 x_phone_area_code VARCHAR2(10);
394 x_phone_number VARCHAR2(40);
395 x_phone_extention VARCHAR2(20);
396 x_party_name VARCHAR2(400);
397 p_party_id number;
398 p_pr_party_id number;
399 l_lp_psite_id number;
400 x_hz_dup_check VARCHAR2(60);
401 l_overlay VARCHAR2(1);
402 l_phone_exists VARCHAR2(1);
403 l_email_exists VARCHAR2(1);
404 l_is_party_mapped VARCHAR2(1);
405
406 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
410 -- Cursor for B2B party type
407 L_API_NAME CONSTANT VARCHAR2(30) := 'create_registrant_party';
408
409
411
412 -- Fixed bug#4654679
413 -- Replaced HZ_PARTY_RELATIONSHIPS with HZ_RELATIONSHIPS
414 -- Also, relaced PARTY_RELATIONSHIP_TYPE = 'CONTACT_OF'
415 -- with RELATIONSHIP_CODE = 'CONTACT_OF'
416 CURSOR PARTY_REL_EXISTS IS
417 SELECT party_id FROM hz_relationships
418 WHERE object_id = x_org_party_id
419 AND subject_id = x_per_party_id
420 AND relationship_code = 'CONTACT_OF';
421
422 CURSOR LOCATION_EXISTS IS
423 SELECT party_site_id FROM hz_party_sites
424 WHERE party_id = x_org_party_id
425 AND location_id = x_location_id;
426
427 CURSOR PER_LOCATION_EXISTS IS
428 SELECT party_site_id FROM hz_party_sites
429 WHERE party_id = x_per_party_id
430 AND location_id = x_location_id;
431
432 CURSOR CHECK_PSITE_EXISTS IS
433 SELECT party_site_id FROM hz_party_sites
434 WHERE party_id = x_party_rel_party_id
435 AND location_id = x_location_id;
436
437 CURSOR phone_exists (x_hz_party_id number) IS
438 SELECT 'Y' FROM hz_contact_points
439 WHERE contact_point_type = 'PHONE'
440 AND phone_line_type = 'GEN'
441 AND owner_table_name = 'HZ_PARTIES'
442 AND owner_table_id = x_hz_party_id
443 AND phone_number = x_phone_number
444 AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
445 AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')
446 AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
447
448
449 CURSOR email_exists (x_hz_party_id number) IS
450 SELECT 'Y' FROM hz_contact_points
451 WHERE contact_point_type = 'EMAIL'
452 AND owner_table_name = 'HZ_PARTIES'
453 AND owner_table_id = x_hz_party_id
454 AND email_address = x_email_address;
455 /*
456 party_rec hz_party_pub.party_rec_type;
457 org_rec hz_party_pub.organization_rec_type;
458 person_rec hz_party_pub.person_rec_type;
459 location_rec hz_location_pub.location_rec_type;
460 psite_rec hz_party_pub.party_site_rec_type;
461 psite_use_rec hz_party_pub.party_site_use_rec_type;
462 cpoint_rec hz_contact_point_pub.contact_points_rec_type;
463 email_rec hz_contact_point_pub.email_rec_type;
464 phone_rec hz_contact_point_pub.phone_rec_type;
465 ocon_rec hz_party_pub.org_contact_rec_type;
466 edi_rec hz_contact_point_pub.edi_rec_type;
467 telex_rec hz_contact_point_pub.telex_rec_type;
468 web_rec hz_contact_point_pub.web_rec_type;
469 */
470 -- sikalyan bugFix TCA V2 Uptake
471
472 party_rec hz_party_v2pub.party_rec_type;
473 org_rec hz_party_v2pub.organization_rec_type;
474 person_rec hz_party_v2pub.person_rec_type;
475 location_rec hz_location_v2pub.location_rec_type;
476 psite_rec hz_party_site_v2pub.party_site_rec_type;
477 psite_use_rec hz_party_site_v2pub.party_site_use_rec_type;
478 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
479 email_rec hz_contact_point_v2pub.email_rec_type;
480 phone_rec hz_contact_point_v2pub.phone_rec_type;
481 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
482 edi_rec hz_contact_point_v2pub.edi_rec_type;
483 telex_rec hz_contact_point_v2pub.telex_rec_type;
484 web_rec hz_contact_point_v2pub.web_rec_type;
485
486 begin
487 /*
488 IF (AMS_DEBUG_HIGH_ON) THEN
489
490 AMS_Utility_PVT.Debug_Message('Start');
491 END IF;
492 */
493 Write_log(L_API_NAME, 'Start');
494
495 -- Standard call to check for call compatibility.
496 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
497 p_api_version,
498 l_api_name,
499 G_PKG_NAME)
500 THEN
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502 END IF;
503
504 -- Initialize message list if p_init_msg_list is set to TRUE.
505 IF FND_API.to_Boolean( p_init_msg_list )
506 THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 -- RETURN;
511 x_return_status := FND_API.g_ret_sts_success;
512 x_new_org_party_id := null;
513 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'TCA import Concurrent Program(+)');
514 x_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
515 x_gen_contact_number := fnd_profile.value('HZ_GENERATE_CONTACT_NUMBER');
516 x_gen_party_site_number := fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER');
517 x_hz_dup_check := fnd_profile.value('AMS_HZ_DEDUPE_RULE');
518 if x_hz_dup_check <> 'Y' then
519 x_hz_dup_check := 'N';
520 end if;
521 /*
522 IF (AMS_DEBUG_HIGH_ON) THEN
523
524 AMS_Utility_PVT.Debug_Message('Party Name: ' || nvl(p_rec.party_name,'Null'));
525 END IF;
526 */
527 Write_log(L_API_NAME, 'Party Name: ' || nvl(p_rec.party_name,'Null'));
528
529 if p_rec.PARTY_NAME is NOT NULL then
530 /*IF (AMS_DEBUG_HIGH_ON) THEN
531
532 AMS_UTILITY_PVT.debug_message('In B2b');
533 END IF;*/
534 Write_log(L_API_NAME, 'In B2b');
535 --org_rec.party_rec.orig_system_reference,
536 org_rec.organization_name := p_rec.PARTY_NAME;
537 org_rec.created_by_module := 'AMS_EVENT';
541 person_rec.person_middle_name := p_rec.middle_name;
538 -- created_by_module TCA Mandate
539
540 person_rec.person_first_name := p_rec.first_name;
542 person_rec.person_last_name := p_rec.last_name;
543 person_rec.person_name_suffix := p_rec.name_suffix;
544 person_rec.person_title := p_rec.title;
545 -- BEST_TIME_CONTACT_BEGIN is obsolete in TCA V2
546 --person_rec.best_time_contact_begin := p_rec.BEST_TIME_CONTACT_BEGIN;
547 -- BEST_TIME_CONTACT_END is obsolete in TCA V2
548 -- person_rec.best_time_contact_end := p_rec.BEST_TIME_CONTACT_END;
549 person_rec.gender := p_rec.gender;
550 person_rec.jgzz_fiscal_code := p_rec.jgzz_fiscal_code;
551 -- TAX_NAME is obsolete in TCA V2 API
552 -- person_rec.tax_name := p_rec.tax_name;
553 person_rec.tax_reference := p_rec.tax_reference;
554 person_rec.created_by_module := 'AMS_EVENT';
555 -- created_by_module TCA Mandate
556 location_rec.country := p_rec.COUNTRY;
557 location_rec.address1 := p_rec.ADDRESS1;
558 location_rec.address2 := p_rec.ADDRESS2;
559 location_rec.city := p_rec.CITY;
560 location_rec.county := p_rec.COUNTRY;
561 location_rec.state := p_rec.STATE;
562 location_rec.province := p_rec.PROVINCE;
563 location_rec.postal_code := p_rec.POSTAL_CODE;
564 -- Starting TCA V2 time_zone ceases to exist, replaced by timezone_id
565 location_rec.timezone_id := p_rec.timezone;
566 location_rec.ADDRESS3 := p_rec.ADDRESS3;
567 location_rec.ADDRESS4 := p_rec.ADDRESS4;
568 location_rec.address_lines_phonetic := p_rec.address_line_phonetic;
569 -- The APARTMENT_FLAG field is obsolete in TCA V2 API
570 -- location_rec.APARTMENT_FLAG := p_rec.apt_flag;
571 -- location_rec.PO_BOX_NUMBER := p_rec.po_box_no;
572 -- location_rec.HOUSE_NUMBER := p_rec.HOUSE_NUMBER;
573 -- location_rec.STREET_SUFFIX := p_rec.STREET_SUFFIX;
574 -- BugFix 4665060
575 -- The SECONDARY_SUFFIX_ELEMENT field is obeolste in TCA V2
576 --location_rec.SECONDARY_SUFFIX_ELEMENT := p_rec.SECONDARY_SUFFIX_ELEMENT;
577 -- location_rec.STREET := p_rec.STREET;
578 -- The RURAL_ROUTE_TYPE field is obeolste in TCA V2
579 --location_rec.RURAL_ROUTE_TYPE := p_rec.RURAL_ROUTE_TYPE;
580 -- The RURAL_ROUTE_NUMBER field is obeolste in TCA V2
581 --location_rec.RURAL_ROUTE_NUMBER := p_rec.rural_route_no;
582 -- location_rec.STREET_NUMBER := p_rec.STREET_NUMBER;
583 -- location_rec.FLOOR := p_rec.FLOOR;
584 -- location_rec.SUITE := p_rec.SUITE;
585 location_rec.POSTAL_PLUS4_CODE := p_rec.POSTAL_PLUS4_CODE;
586 -- The 'OVERSEAS_ADDRESS_FLAG' is obsolete in TCA V2 API
587 -- location_rec.OVERSEAS_ADDRESS_FLAG := p_rec.OVERSEAS_ADDRESS_FLAG;
588 location_rec.created_by_module := 'AMS_EVENT';
589
590 x_email_address := p_rec.EMAIL_ADDRESS;
591 x_phone_country_code := p_rec.PHONE_COUNTRY_CODE;
592 x_phone_area_code := p_rec.PHONE_AREA_CODE;
593 x_phone_number := p_rec.PHONE_NUMBER;
594 x_phone_extention := p_rec.phone_extension;
595
596 ocon_rec.department := p_rec.DEPARTMENT;
597 ocon_rec.job_title := p_rec.JOB_TITLE;
598 ocon_rec.decision_maker_flag := p_rec.DECISION_MAKER_FLAG;
599 ocon_rec.created_by_module := 'AMS_EVENT';
600
601
602 -- Creates Organization
603
604 x_party_name := org_rec.organization_name;
605 x_org_party_id := null;
606 x_return_status := null;
607 x_msg_count := null;
608 x_msg_data := null;
609
610 if x_hz_dup_check = 'Y' then
611 party_echeck(
612 --p_impt_list_header_id => p_import_list_header_id,
613 p_party_id => x_org_party_id,
614 p_org_name => x_party_name,
615 p_per_first_name => NULL,
616 p_per_last_name => NULL,
617 p_address1 => location_rec.address1,
618 p_country => location_rec.country
619 );
620 end if;
621 /*IF (AMS_DEBUG_HIGH_ON) THEN
622
623 AMS_UTILITY_PVT.debug_message('B2B: After Party_echeck: ' || x_party_name || ', ' || x_org_party_id);
624 END IF;*/
625 Write_log(L_API_NAME, 'B2B: After Party_echeck: ' || x_party_name || ', ' || x_org_party_id);
626 if x_org_party_id is NULL then
627 /*IF (AMS_DEBUG_HIGH_ON) THEN
628
629 AMS_UTILITY_PVT.debug_message('B2B: Organization is NULL');
630 END IF;*/
631 Write_log(L_API_NAME, 'B2B: Organization is NULL');
632 x_party_number := null;
633 if x_generate_party_number = 'N' then
634 select hz_party_number_s.nextval into x_party_number from dual;
635 end if;
636 select hz_parties_s.nextval into x_org_party_id from dual;
637 org_rec.party_rec.party_number := x_party_number;
638 org_rec.party_rec.party_id := x_org_party_id;
639 org_rec.ceo_name := p_rec.ceo_name;
640 org_rec.curr_fy_potential_revenue := p_rec.current_fy_potential_rev;
641 org_rec.next_fy_potential_revenue := p_rec.next_fy_potential_rev;
642 org_rec.duns_number_c := p_rec.dun_no_c;
643 org_rec.employees_total := p_rec.employee_total;
647 org_rec.legal_status := p_rec.org_legal_status;
644 org_rec.fiscal_yearend_month := p_rec.fy_end_month;
645 org_rec.gsa_indicator_flag := p_rec.gsa_indicator_flag;
646 org_rec.jgzz_fiscal_code := p_rec.jgzz_fiscal_code;
648 org_rec.line_of_business := p_rec.line_of_business;
649 org_rec.mission_statement := p_rec.mission_statement;
650 org_rec.organization_name_phonetic := p_rec.org_name_phonetic;
651 org_rec.sic_code := p_rec.sic_code;
652 org_rec.sic_code_type := p_rec.sic_code_type;
653 -- org_rec.tax_name is obsolete in tca v2
654 -- org_rec.tax_name := p_rec.tax_name;
655 org_rec.tax_reference := p_rec.tax_reference;
656 org_rec.year_established := p_rec.year_established;
657 org_rec.created_by_module := 'AMS_EVENT';
658
659 hz_party_v2pub.create_organization(
660 'F',
661 org_rec,
662 x_return_status,
663 x_msg_count,
664 x_msg_data,
665 x_org_party_id,
666 x_party_number,
667 x_organization_profile_id
668 );
669
670 /*
671 if x_msg_count > 1 then
672 FOR i IN 1..x_msg_count LOOP
673 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
674 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
675 END LOOP;
676 x_msg_data := x_tmp_var1;
677 end if;
678 */
679 if x_return_status <> 'S'
680 then
681 --errbuf := 'ORG -'||substr(org_rec.organization_name,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
682 /*IF (AMS_DEBUG_HIGH_ON) THEN
683
684 AMS_UTILITY_PVT.debug_message('B2B: Errpr creating an organization');
685 END IF;*/
686 Write_log(L_API_NAME, 'B2B: Errpr creating an organization');
687 AMS_UTILITY_PVT.error_message( 'ORGANIZATION_CREATE_FAILURE'
688 , 'ROW'
689 , 'ORG - ' || substr(org_rec.organization_name,1,25) || '- ERROR-' || substr(x_msg_data,1,180)
690 );
691 /*
692 FND_MESSAGE.set_name('AMS', 'ORGANIZATION_CREATE_FAILURE');
693 FND_MESSAGE.Set_Token('ROW', 'ORG - ' || substr(org_rec.organization_name,1,25) || '- ERROR-' || substr(x_msg_data,1,180));
694 FND_MSG_PUB.add;
695 */
696 if x_return_status = FND_API.g_ret_sts_unexp_error THEN
697 RAISE FND_API.g_exc_unexpected_error;
698 elsif x_return_status = FND_API.g_ret_sts_error THEN
699 RAISE FND_API.g_exc_error;
700 end if;
701 end if;
702 /*IF (AMS_DEBUG_HIGH_ON) THEN
703
704 AMS_UTILITY_PVT.debug_message('Created party: ' || x_org_party_id);
705 END IF;*/
706 Write_log(L_API_NAME, 'Created party: ' || x_org_party_id);
707 end if; -- x_org_party_id is NULL
708 x_new_org_party_id := x_org_party_id;
709 -- Creates Person
710 x_party_number := null;
711 x_return_status := null;
712 x_msg_count := null;
713 x_msg_data := null;
714 if x_hz_dup_check = 'Y' then
715 contact_echeck(
716 --p_impt_list_header_id => p_import_list_header_id,
717 p_party_id => x_per_party_id,
718 p_org_party_id => x_org_party_id,
719 p_per_first_name => person_rec.person_first_name,
720 p_per_last_name => person_rec.person_last_name,
721 p_phone_area_code => x_phone_area_code,
722 p_phone_number => x_phone_number,
723 p_phone_extension => x_phone_extention,
724 p_email_address => x_email_address );
725 end if;
726 /*IF (AMS_DEBUG_HIGH_ON) THEN
727
728 AMS_UTILITY_PVT.debug_message(' B2B: After Person' || x_per_party_id);
729 END IF;*/
730 Write_log(L_API_NAME, ' B2B: After Person' || x_per_party_id);
731
732 x_new_party_id := x_per_party_id;
733 if x_per_party_id is null then
734 /*IF (AMS_DEBUG_HIGH_ON) THEN
735
736 AMS_UTILITY_PVT.debug_message('B2B: Creating Person' || person_rec.person_first_name);
737 END IF;*/
738 Write_log(L_API_NAME, 'B2B: Creating Person' || person_rec.person_first_name);
739 if person_rec.person_first_name is not null then
740 if x_generate_party_number = 'N' then
741 select hz_party_number_s.nextval into x_party_number from dual;
742 end if;
743 select hz_parties_s.nextval into x_per_party_id from dual;
744 person_rec.party_rec.party_number := x_party_number;
745 person_rec.party_rec.party_id := x_per_party_id;
746 x_return_status := null;
747 x_msg_count := 0;
748 x_msg_data := null;
749
750 hz_party_v2pub.create_person(
751 'F',
752 person_rec,
753 x_return_status,
754 x_msg_count,
758 x_person_profile_id
755 x_msg_data,
756 x_per_party_id,
757 x_party_number,
759 );
760 /*
761 if x_msg_count > 1 then
762 FOR i IN 1..x_msg_count LOOP
763 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
764 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
765 END LOOP;
766 x_msg_data := x_tmp_var1;
767 END IF;
768 */
769
770 if x_return_status <> 'S' then
771 --errbuf := 'PERSON - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160);
772 FND_MESSAGE.set_name('AMS', 'AMS_CREATE_PERSON_FAILED');
773 FND_MESSAGE.Set_Token('ROW', 'PERSON - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160));
774 FND_MSG_PUB.add;
775 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
776 RAISE FND_API.g_exc_unexpected_error;
777 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
778 RAISE FND_API.g_exc_error;
779 end if;
780 else
781 x_new_party_id := x_per_party_id;
782
783 /*IF (AMS_DEBUG_HIGH_ON) THEN
784
785 AMS_UTILITY_PVT.debug_message('B2B: Creating Person sucessful' || person_rec.person_first_name);
786 END IF;*/
787 Write_log(L_API_NAME, 'B2B: Creating Person sucessful' || person_rec.person_first_name);
788
789 end if;
790 end if;
791 end if; -- x_per_party_id is NULL
792
793 -- Creates Org Contact ,party relationship and party for p rel
794 x_party_number := null;
795 x_contact_number := null;
796 x_return_status := null;
797 x_msg_count := null;
798 x_msg_data := null;
799
800 if person_rec.person_first_name is not null then
801 p_pr_party_id := null;
802 open PARTY_REL_EXISTS;
803 fetch PARTY_REL_EXISTS into p_pr_party_id;
804 close PARTY_REL_EXISTS;
805 if p_pr_party_id is not null then
806 x_party_rel_party_id := p_pr_party_id;
807 end if;
808 if p_pr_party_id is null then
809 /*IF (AMS_DEBUG_HIGH_ON) THEN
810
811 AMS_UTILITY_PVT.debug_message('B2B :Creating Org Contact' || p_pr_party_id);
812 END IF;*/
813 Write_log(L_API_NAME, 'B2B :Creating Org Contact' || p_pr_party_id);
814
815 Select hz_org_contacts_s.nextval into x_org_contact_id from dual;
816 if x_generate_party_number = 'N' then
817 select hz_party_number_s.nextval into x_party_number from dual;
818 end if;
819
820 ocon_rec.party_rel_rec.subject_id := x_per_party_id;
821 ocon_rec.party_rel_rec.object_id := x_org_party_id;
822 ocon_rec.org_contact_id := x_org_contact_id;
823 ocon_rec.orig_system_reference := x_org_contact_id;
824 ocon_rec.party_rel_rec.relationship_type := 'CONTACT_OF';
825 -- directional_flag is obsolete in TCA V2
826 --ocon_rec.party_rel_rec.directional_flag := 'Y';
827 ocon_rec.party_rel_rec.start_date := sysdate;
828 ocon_rec.created_by_module := 'AMS_EVENT';
829 IF x_generate_party_number = 'N' THEN
830 ocon_rec.party_rel_rec.party_rec.party_number := x_party_number;
831 END IF;
832 IF x_gen_contact_number = 'N' THEN
833 select hz_contact_numbers_s.nextval into x_contact_number from dual;
834 end if;
835 ocon_rec.contact_number := x_contact_number;
836 -- Obsolete in TCA V2 ocon_rec.status
837 -- ocon_rec.status := 'A';
838 ocon_rec.party_rel_rec.status := 'A';
839 /*IF (AMS_DEBUG_HIGH_ON) THEN
840
841 AMS_UTILITY_PVT.debug_message('B2B: Creating org contact' || person_rec.person_first_name);
842 END IF;*/
843 Write_log(L_API_NAME, 'B2B: Creating org contact' || person_rec.person_first_name);
844
845
846 hz_party_contact_v2pub.create_org_contact(
847 'F',
848 ocon_rec,
849 x_return_status,
850 x_msg_count,
851 x_msg_data,
852 x_org_contact_id,
853 x_party_relationship_id,
854 x_party_rel_party_id,
855 x_party_number);
856
857
858 /*
859 if x_msg_count > 1 then
860 FOR i IN 1..x_msg_count LOOP
861 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
862 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
863 END LOOP;
864 x_msg_data := x_tmp_var1;
865 END IF;
866 */
867
868 if x_return_status <> 'S' then
869 --errbuf := 'ORG CONTACT - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160);
873 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
870 FND_MESSAGE.set_name('AMS', 'AMS_ORG_CONTACT_FAILURE');
871 FND_MESSAGE.Set_Token('ROW', 'ORG CONTACT - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160));
872 FND_MSG_PUB.add;
874 RAISE FND_API.g_exc_unexpected_error;
875 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
876 RAISE FND_API.g_exc_error;
877 end if;
878 end if;
879 end if; -- if p_pr_party_id is null
880 end if; -- if person_rec.person_first_name is not null
881
882 if x_hz_dup_check = 'Y' then
883 address_echeck(
884 --p_impt_list_header_id => p_import_list_header_id,
885 p_party_id => x_org_party_id,
886 p_location_id => x_location_id,
887 p_address1 => location_rec.address1,
888 p_city => location_rec.city,
889 p_pcode => location_rec.postal_code,
890 p_country => location_rec.country
891 );
892 end if;
893 if x_location_id is null and x_org_party_id is not null then
894 if location_rec.address1 is not NULL then
895 -- Create Location
896 /*IF (AMS_DEBUG_HIGH_ON) THEN
897
898 AMS_UTILITY_PVT.debug_message('B2B:Creating Location' || location_rec.address1);
899 END IF;*/
900 Write_log(L_API_NAME, 'B2B:Creating Location' || location_rec.address1);
901 x_return_status := null;
902 x_msg_count := null;
903 x_msg_data := null;
904 select hr_locations_s.nextval into x_location_id from dual;
905 location_rec.location_id := X_location_Id;
906 location_rec.orig_system_reference := x_location_id ;
907 location_rec.created_by_module := 'AMS_EVENT';
908
909
910 hz_location_v2pub.create_location(
911 'F',
912 location_rec,
913 x_return_status,
914 x_msg_count,
915 x_msg_data,
916 x_location_id
917 );
918 /*
919 if x_msg_count > 1 then
920 FOR i IN 1..x_msg_count LOOP
921 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
922 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
923 END LOOP;
924 x_msg_data := x_tmp_var1;
925 END IF;
926 */
927 if x_return_status <> 'S' then
928 --errbuf := 'LOCATION - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
929 FND_MESSAGE.set_name('AMS', 'AMS_LOCATION_CREATE_FAILURE');
930 FND_MESSAGE.Set_Token('ROW', 'LOCATION - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
931 FND_MSG_PUB.add;
932 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
933 RAISE FND_API.g_exc_unexpected_error;
934 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
935 RAISE FND_API.g_exc_error;
936 end if;
937 end if;
938 end if; -- if location_rec.address1 is not NULL
939 end if; -- x_location_id is null;
940
941 l_lp_psite_id := null;
942 open LOCATION_EXISTS;
943 fetch LOCATION_EXISTS into l_lp_psite_id;
944 close LOCATION_EXISTS;
945 if l_lp_psite_id is null and x_org_party_id is not null and x_location_id is not null then
946 -- Create Party Site
947 /*IF (AMS_DEBUG_HIGH_ON) THEN
948
949 AMS_UTILITY_PVT.debug_message('B2B:Creating Party_site' || l_lp_psite_id);
950 END IF;*/
951 Write_log(L_API_NAME, 'B2B:Creating Party_site' || l_lp_psite_id);
952 x_return_status := null;
953 x_msg_count := null;
954 x_msg_data := null;
955 x_party_site_number := null;
956 select hz_party_sites_s.nextval into x_party_site_id from dual;
957 if x_gen_party_site_number = 'N' then
958 select hz_party_site_number_s.nextval into x_party_site_number from dual;
959 end if;
960
961 psite_rec.party_site_id := x_party_site_id;
962 psite_rec.party_id := x_org_party_id;
963 psite_rec.location_id := x_location_id;
964 psite_rec.party_site_number := x_party_site_number;
965 psite_rec.orig_system_reference := x_party_site_id;
966 psite_rec.identifying_address_flag := p_rec.identifying_address_flag;
967 -- psite_rec.site_use_code := p_rec.site_use_code;
968 -- psite_rec.identifying_address_flag := 'Y';
969 psite_rec.status := 'A';
970 psite_rec.created_by_module := 'AMS_EVENT';
971
972
973 hz_party_site_v2pub.create_party_site(
974 p_init_msg_list => p_init_msg_list,
975 p_party_site_rec => psite_rec,
976 x_return_status => x_return_status,
977 x_msg_count => x_msg_count,
978 x_msg_data => x_msg_data,
979 x_party_site_id => x_party_site_id,
980 x_party_site_number => x_party_site_number
984 /*
981 );
982
983
985 if x_msg_count > 1 then
986 FOR i IN 1..x_msg_count LOOP
987 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
988 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
989 END LOOP;
990 x_msg_data := x_tmp_var1;
991 END IF;
992 */
993 if x_return_status <> 'S' then
994 --errbuf := 'PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
995 FND_MESSAGE.set_name('AMS', 'AMS_PART_SITE_CREATION_FAILURE');
996 FND_MESSAGE.Set_Token('ROW','PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
997 FND_MSG_PUB.add;
998 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
999 RAISE FND_API.g_exc_unexpected_error;
1000 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1001 RAISE FND_API.g_exc_error;
1002 end if;
1003 end if;
1004
1005 if (p_rec.site_use_code is not null)
1006 then
1007 select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;
1008
1009 psite_use_rec.site_use_type := p_rec.site_use_code;
1010 psite_use_rec.party_site_id := x_party_site_id;
1011 psite_use_rec.party_site_use_id := x_party_site_use_id;
1012 psite_use_rec.created_by_module := 'AMS_EVENT';
1013
1014
1015 hz_party_site_v2pub.create_party_site_use(
1016 p_init_msg_list => p_init_msg_list,
1017 p_party_site_use_rec => psite_use_rec,
1018 x_return_status => x_return_status,
1019 x_msg_count => x_msg_count,
1020 x_msg_data => x_msg_data,
1021 x_party_site_use_id => x_party_site_use_id
1022 );
1023
1024 /*
1025 if x_msg_count > 1 then
1026 FOR i IN 1..x_msg_count LOOP
1027 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1028 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1029 END LOOP;
1030 x_msg_data := x_tmp_var1;
1031 END IF;
1032 */
1033 if x_return_status <> 'S' then
1034 --errbuf := 'PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1035 FND_MESSAGE.set_name('AMS', 'AMS_PART_SITE_CREATION_FAILURE');
1036 FND_MESSAGE.Set_Token('ROW','PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1037 FND_MSG_PUB.add;
1038 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1039 RAISE FND_API.g_exc_unexpected_error;
1040 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1041 RAISE FND_API.g_exc_error;
1042 end if;
1043 end if;
1044 end if; -- site use code
1045 end if; -- if l_lp_psite_id is null then
1046
1047 -- ****************************************************
1048 -- Creating party_site for Contacts.
1049
1050 if person_rec.person_first_name is not null and x_party_rel_party_id is not null then
1051 if location_rec.address1 is not NULL and x_location_id is not null then
1052 l_lp_psite_id := null;
1053 open CHECK_PSITE_EXISTS;
1054 fetch CHECK_PSITE_EXISTS into l_lp_psite_id;
1055 close CHECK_PSITE_EXISTS;
1056 if l_lp_psite_id is null then
1057 -- Create Party Site
1058
1059 /*IF (AMS_DEBUG_HIGH_ON) THEN
1060
1061 AMS_UTILITY_PVT.debug_message('B2B:Creating Party_site for contact ' || l_lp_psite_id);
1062 END IF;*/
1063 Write_log(L_API_NAME, 'B2B:Creating Party_site for contact ' || l_lp_psite_id);
1064
1065 x_return_status := null;
1066 x_msg_count := null;
1067 x_msg_data := null;
1068 x_party_site_number := null;
1069 select hz_party_sites_s.nextval into x_party_site_id from dual;
1070 if x_gen_party_site_number = 'N' then
1071 select hz_party_site_number_s.nextval into x_party_site_number from dual;
1072 end if;
1073
1074 psite_rec.party_site_id := x_party_site_id;
1075 psite_rec.party_id := x_party_rel_party_id;
1076 psite_rec.location_id := x_location_id;
1077 psite_rec.party_site_number := x_party_site_number;
1078 psite_rec.orig_system_reference := x_party_site_id;
1079 psite_rec.identifying_address_flag := p_rec.identifying_address_flag;
1080 -- psite_rec.site_use_code := p_rec.site_use_code;
1081 -- psite_rec.identifying_address_flag := 'Y';
1082 psite_rec.status := 'A';
1083 psite_rec.created_by_module := 'AMS_EVENT';
1084
1085 hz_party_site_v2pub.create_party_site(
1086 p_init_msg_list => p_init_msg_list,
1087 p_party_site_rec => psite_rec,
1088 x_return_status => x_return_status,
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data,
1091 x_party_site_id => x_party_site_id,
1095 /*
1092 x_party_site_number => x_party_site_number
1093 );
1094
1096 if x_msg_count > 1 then
1097 FOR i IN 1..x_msg_count LOOP
1098 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1099 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1100 END LOOP;
1101 x_msg_data := x_tmp_var1;
1102 END IF;
1103 */
1104 if x_return_status <> 'S' then
1105 --errbuf := 'CONT PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1106 FND_MESSAGE.set_name('AMS', 'AMS_PSITE_CONTACT_CREATE_FAILURE');
1107 FND_MESSAGE.Set_Token('ROW','CONT PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1108 FND_MSG_PUB.add;
1109 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1110 RAISE FND_API.g_exc_unexpected_error;
1111 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1112 RAISE FND_API.g_exc_error;
1113 end if;
1114 end if;
1115
1116 if (p_rec.site_use_code is not null)
1117 then
1118 select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;
1119
1120 psite_use_rec.site_use_type := p_rec.site_use_code;
1121 psite_use_rec.party_site_id := x_party_site_id;
1122 psite_use_rec.party_site_use_id := x_party_site_use_id;
1123 psite_use_rec.created_by_module := 'AMS_EVENT';
1124
1125
1126 hz_party_site_v2pub.create_party_site_use(
1127 p_init_msg_list => p_init_msg_list,
1128 p_party_site_use_rec => psite_use_rec,
1129 x_return_status => x_return_status,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data,
1132 x_party_site_use_id => x_party_site_use_id);
1133
1134 /*
1135 if x_msg_count > 1 then
1136 FOR i IN 1..x_msg_count LOOP
1137 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1138 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1139 END LOOP;
1140 x_msg_data := x_tmp_var1;
1141 END IF;
1142 */
1143 if x_return_status <> 'S' then
1144 --errbuf := 'PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1145 FND_MESSAGE.set_name('AMS', 'AMS_PART_SITE_CREATION_FAILURE');
1146 FND_MESSAGE.Set_Token('ROW','PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1147 FND_MSG_PUB.add;
1148 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1149 RAISE FND_API.g_exc_unexpected_error;
1150 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1151 RAISE FND_API.g_exc_error;
1152 end if;
1153 end if;
1154 end if; -- site use code
1155
1156 end if; -- lp_psite_id
1157
1158 end if;
1159 end if; --person_rec.person_first_name is not null and x_party_rel_party_id is not null
1160
1161 -- ****************************************************
1162
1163 -- Create contact points Phone
1164
1165 if x_phone_number is not NULL and x_party_rel_party_id is not null then
1166 SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
1167 x_return_status := null;
1168 x_msg_count := null;
1169 x_msg_data := null;
1170 cpoint_rec.contact_point_id := x_contact_point_id;
1171 cpoint_rec.contact_point_type := 'PHONE';
1172 cpoint_rec.status := 'A';
1173 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1174 cpoint_rec.owner_table_id := x_party_rel_party_id;
1175 -- cpoint_rec.primary_flag := 'Y';
1176 cpoint_rec.orig_system_reference := x_contact_point_id;
1177 cpoint_rec.created_by_module := 'AMS_EVENT';
1178 phone_rec.phone_line_type := 'GEN';
1179 phone_rec.phone_number := x_phone_number;
1180 phone_rec.phone_country_code := x_phone_country_code;
1181 phone_rec.phone_area_code := x_phone_area_code;
1182 phone_rec.phone_extension := x_phone_extention;
1183
1184 l_phone_exists := NULL;
1185 open phone_exists(x_party_rel_party_id);
1186 fetch phone_exists into l_phone_exists;
1187 close phone_exists;
1188 if l_phone_exists is NULL then
1189 /*IF (AMS_DEBUG_HIGH_ON) THEN
1190
1191 AMS_UTILITY_PVT.debug_message('B2B:Creating PPhone contact ' || x_party_rel_party_id);
1192 END IF;*/
1193 Write_log(L_API_NAME, 'B2B:Creating PPhone contact ' || x_party_rel_party_id);
1194 hz_contact_point_v2pub.create_contact_point(
1195 'F',
1196 cpoint_rec,
1197 edi_rec,
1198 email_rec,
1199 phone_rec,
1200 telex_rec,
1201 web_rec,
1202 x_return_status,
1203 x_msg_count,
1207 if x_msg_count > 1 then
1204 x_msg_data,
1205 x_contact_point_id);
1206 /*
1208 FOR i IN 1..x_msg_count LOOP
1209 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1210 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1211 END LOOP;
1212 x_msg_data := x_tmp_var1;
1213 END IF;
1214 */
1215
1216 if x_return_status <> 'S' then
1217 --errbuf := 'PHONE - '||substr(x_phone_number,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1218 FND_MESSAGE.set_name('AMS', 'AMS_CONTACT_PHONE_ERROR');
1219 FND_MESSAGE.Set_Token('ROW','PHONE - '||substr(x_phone_number,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1220 FND_MSG_PUB.add;
1221 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1222 RAISE FND_API.g_exc_unexpected_error;
1223 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1224 RAISE FND_API.g_exc_error;
1225 end if;
1226 end if;
1227 end if; -- l_phone_exists is NULL then
1228 end if; -- x_phone_number
1229
1230 -- Create contact points Email
1231
1232 if x_email_address is not NULL and x_party_rel_party_id is not null then
1233 SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
1234
1235 x_return_status := null;
1236 x_msg_count := null;
1237 x_msg_data := null;
1238 cpoint_rec.contact_point_id := x_contact_point_id;
1239 cpoint_rec.contact_point_type := 'EMAIL';
1240 cpoint_rec.status := 'A';
1241 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1242 cpoint_rec.owner_table_id := x_party_rel_party_id;
1243 -- cpoint_rec.primary_flag := 'Y';
1244 cpoint_rec.orig_system_reference := x_contact_point_id;
1245 cpoint_rec.created_by_module := 'AMS_EVENT';
1246
1247 email_rec.email_address := x_email_address;
1248 l_email_exists := NULL;
1249 open email_exists(x_party_rel_party_id);
1250 fetch email_exists into l_email_exists;
1251 close email_exists;
1252 if l_email_exists is NULL then
1253 /*IF (AMS_DEBUG_HIGH_ON) THEN
1254
1255 AMS_UTILITY_PVT.debug_message('B2B:Creating Email contact ' || x_party_rel_party_id);
1256 END IF;*/
1257 Write_log(L_API_NAME, 'B2B:Creating Email contact ' || x_party_rel_party_id);
1258
1259 hz_contact_point_v2pub.create_contact_point(
1260 'F',
1261 cpoint_rec,
1262 edi_rec,
1263 email_rec,
1264 phone_rec,
1265 telex_rec,
1266 web_rec,
1267 x_return_status,
1268 x_msg_count,
1269 x_msg_data,
1270 x_contact_point_id);
1271 /*
1272 if x_msg_count > 1 then
1273 FOR i IN 1..x_msg_count LOOP
1274 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1275 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1276 END LOOP;
1277 x_msg_data := x_tmp_var1;
1278 END IF;
1279 */
1280 if x_return_status <> 'S' then
1281 --errbuf := 'EMAIL - '||substr(x_email_address,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1282 FND_MESSAGE.set_name('AMS', 'AMS_CONTACT_EMAIL_ERROR');
1283 FND_MESSAGE.Set_Token('ROW','EMAIL - '||substr(x_email_address,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1284 FND_MSG_PUB.add;
1285 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1286 RAISE FND_API.g_exc_unexpected_error;
1287 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1288 RAISE FND_API.g_exc_error;
1289 end if;
1290 end if;
1291 end if; -- l_email_exists is NULL then
1292 end if; -- x_email_address
1293
1294 else
1295
1296 /*IF (AMS_DEBUG_HIGH_ON) THEN
1297
1298 AMS_UTILITY_PVT.debug_message('In B2c ');
1299 END IF;*/
1300 Write_log(L_API_NAME, 'In B2c ');
1301
1302 person_rec.person_first_name := p_rec.first_name;
1303 person_rec.person_middle_name := p_rec.middle_name;
1304 person_rec.person_last_name := p_rec.last_name;
1305 person_rec.person_name_suffix := p_rec.name_suffix;
1306 person_rec.person_title := p_rec.title;
1307 -- best_time_contact_begin and best_time_contact_end are obsolete in TCA V2
1308 --person_rec.best_time_contact_begin := p_rec.BEST_TIME_CONTACT_BEGIN;
1309 --person_rec.best_time_contact_end := p_rec.BEST_TIME_CONTACT_END;
1310 person_rec.gender := p_rec.gender;
1311 person_rec.jgzz_fiscal_code := p_rec.jgzz_fiscal_code;
1312 -- tax_name is obsolete
1313 --person_rec.tax_name := p_rec.tax_name;
1314 person_rec.tax_reference := p_rec.tax_reference;
1315 person_rec.created_by_module := 'AMS_EVENT';
1316
1317 -- person_rec.pre_name_adjunct := p_rec.PRE_NAME_ADJUNCT ;
1318 -- person_rec.party_rec.SALUTATION := p_rec.SALUTATION;
1319
1320 location_rec.country := p_rec.COUNTRY;
1321 location_rec.address1 := p_rec.ADDRESS1;
1322 location_rec.address2 := p_rec.ADDRESS2;
1326 location_rec.province := p_rec.PROVINCE;
1323 location_rec.city := p_rec.CITY;
1324 location_rec.county := p_rec.COUNTRY;
1325 location_rec.state := p_rec.STATE;
1327 location_rec.postal_code := p_rec.POSTAL_CODE;
1328 -- time_zone is obsolete in TCA V2, its replaced by timezone_id
1329 location_rec.timezone_id := p_rec.timezone;
1330 location_rec.ADDRESS3 := p_rec.ADDRESS3;
1331 location_rec.ADDRESS4 := p_rec.ADDRESS4;
1332 location_rec.address_lines_phonetic := p_rec.address_line_phonetic;
1333 -- APARTMENT_FLAG is obsolete in TCA V2
1334 -- location_rec.APARTMENT_FLAG := p_rec.apt_flag;
1335 -- location_rec.PO_BOX_NUMBER := p_rec.po_box_no;
1336 -- location_rec.HOUSE_NUMBER := p_rec.HOUSE_NUMBER;
1337 -- location_rec.STREET_SUFFIX := p_rec.STREET_SUFFIX;
1338 -- SECONDARY_SUFFIX_ELEMENT is obsolete in TCA V2
1339 -- location_rec.SECONDARY_SUFFIX_ELEMENT := p_rec.SECONDARY_SUFFIX_ELEMENT;
1340 -- location_rec.STREET := p_rec.STREET;
1341 -- RURAL_ROUTE_TYPE is obsolete in TCA V2
1342 -- location_rec.RURAL_ROUTE_TYPE := p_rec.RURAL_ROUTE_TYPE;
1343 -- RURAL_ROUTE_NUMBER is obsolete in TCA V2
1344 -- location_rec.RURAL_ROUTE_NUMBER := p_rec.rural_route_no;
1345 -- location_rec.STREET_NUMBER := p_rec.STREET_NUMBER;
1346 -- location_rec.FLOOR := p_rec.FLOOR;
1347 -- location_rec.SUITE := p_rec.SUITE;
1348 location_rec.POSTAL_PLUS4_CODE := p_rec.POSTAL_PLUS4_CODE;
1349 -- OVERSEAS_ADDRESS_FLAG is obsolete in TCA V2
1350 --location_rec.OVERSEAS_ADDRESS_FLAG := p_rec.OVERSEAS_ADDRESS_FLAG;
1351 location_rec.created_by_module := 'AMS_EVENT';
1352
1353 x_email_address := p_rec.EMAIL_ADDRESS;
1354 x_phone_country_code := p_rec.PHONE_COUNTRY_CODE;
1355 x_phone_area_code := p_rec.PHONE_AREA_CODE;
1356 x_phone_number := p_rec.PHONE_NUMBER;
1357 x_phone_extention := p_rec.phone_extension;
1358
1359 -- Creates Person
1360 --i_import_source_line_id := person_rec.party_rec.orig_system_reference;
1361 x_return_status := null;
1362 x_msg_count := null;
1363 x_msg_data := null;
1364 x_party_number := null;
1365
1366 if (x_hz_dup_check = 'Y')
1367 then
1368 /*
1369 party_echeck(
1370 --p_impt_list_header_id => p_import_list_header_id,
1371 p_party_id => x_per_party_id,
1372 p_org_name => NULL,
1373 p_per_first_name => person_rec.person_first_name,
1374 p_per_last_name => person_rec.person_last_name,
1375 p_address1 => location_rec.address1,
1376 p_country => location_rec.country);
1377 */
1378 person_party_echeck( p_party_id => x_per_party_id
1379 , p_per_first_name => person_rec.person_first_name
1380 , p_per_last_name => person_rec.person_last_name
1381 , p_address1 => location_rec.address1
1382 , p_country => location_rec.country
1383 , p_email_address => x_email_address
1384 , p_ph_country_code => x_phone_country_code
1385 , p_ph_area_code => x_phone_area_code
1386 , p_ph_number => x_phone_number
1387 );
1388
1389 end if;
1390 x_new_party_id := x_per_party_id;
1391 /*IF (AMS_DEBUG_HIGH_ON) THEN
1392
1393 AMS_UTILITY_PVT.debug_message('B2c:Party_id exists ' || x_per_party_id);
1394 END IF;*/
1395 Write_log(L_API_NAME, 'B2c:Party_id exists ' || x_per_party_id);
1396
1397 if x_per_party_id is null then
1398 if x_generate_party_number = 'N' then
1399 select hz_party_number_s.nextval into x_party_number from dual;
1400 end if;
1401 select hz_parties_s.nextval into x_per_party_id from dual;
1402
1403 person_rec.party_rec.party_number := x_party_number;
1404 person_rec.party_rec.party_id := x_per_party_id;
1405 x_return_status := null;
1406 x_msg_count := 0;
1407 x_msg_data := null;
1408 /*IF (AMS_DEBUG_HIGH_ON) THEN
1409
1410 AMS_UTILITY_PVT.debug_message('B2c:creating Person Party_id ' || x_per_party_id);
1411 END IF;*/
1412 Write_log(L_API_NAME, 'B2c:creating Person Party_id ' || x_per_party_id);
1413
1414 hz_party_v2pub.create_person(
1415 'F',
1416 person_rec,
1417 x_return_status,
1418 x_msg_count,
1419 x_msg_data,
1420 x_per_party_id,
1421 x_party_number,
1422 x_person_profile_id);
1423 /*
1424 if x_msg_count > 1 then
1425 FOR i IN 1..x_msg_count LOOP
1426 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1427 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1428 END LOOP;
1429 x_msg_data := x_tmp_var1;
1430 END IF;
1431 */
1432
1433 if x_return_status <> 'S' then
1434 --errbuf := 'PERSON - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160);
1435 FND_MESSAGE.set_name('AMS', 'AMS_PERSON_ERROR');
1439 RAISE FND_API.g_exc_unexpected_error;
1436 FND_MESSAGE.Set_Token('ROW','PERSON - '||substr(person_rec.person_first_name,1,20)||substr(person_rec.person_last_name,1,20)||'- ERROR-'||substr(x_msg_data,1,160));
1437 FND_MSG_PUB.add;
1438 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1440 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1441 RAISE FND_API.g_exc_error;
1442 end if;
1443 else
1444 x_new_party_id := x_per_party_id;
1445 /*IF (AMS_DEBUG_HIGH_ON) THEN
1446
1447 AMS_UTILITY_PVT.debug_message('B2B: Creating Person sucessful' || person_rec.person_first_name);
1448 END IF;*/
1449 Write_log(L_API_NAME, 'B2B: Creating Person sucessful' || person_rec.person_first_name);
1450 end if;
1451 end if; -- x_per_party_id is null then
1452
1453
1454 if x_hz_dup_check = 'Y' then
1455 address_echeck(
1456 --p_impt_list_header_id => p_import_list_header_id,
1457 p_party_id => x_per_party_id,
1458 p_location_id => x_location_id,
1459 p_address1 => location_rec.address1,
1460 p_city => location_rec.city,
1461 p_pcode => location_rec.postal_code,
1462 p_country => location_rec.country
1463 );
1464 end if;
1465
1466 if x_location_id is null and x_per_party_id is not null then
1467 if location_rec.address1 is not NULL then
1468 -- Create Location
1469 x_return_status := null;
1470 x_msg_count := null;
1471 x_msg_data := null;
1472 select hr_locations_s.nextval into x_location_id from dual;
1473 location_rec.location_id := X_location_Id;
1474 location_rec.orig_system_reference := x_location_id ;
1475 /*IF (AMS_DEBUG_HIGH_ON) THEN
1476
1477 AMS_UTILITY_PVT.debug_message('B2c:crating Location ' || location_rec.address1);
1478 END IF;*/
1479 Write_log(L_API_NAME, 'B2c:crating Location ' || location_rec.address1);
1480
1481 hz_location_v2pub.create_location(
1482 'F',
1483 location_rec,
1484 x_return_status,
1485 x_msg_count,
1486 x_msg_data,
1487 x_location_id
1488 );
1489
1490 /*
1491 if x_msg_count > 1 then
1492 FOR i IN 1..x_msg_count LOOP
1493 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1494 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1495 END LOOP;
1496 x_msg_data := x_tmp_var1;
1497 END IF;
1498 */
1499
1500 if x_return_status <> 'S' then
1501 --errbuf := 'LOCATION - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1502 FND_MESSAGE.set_name('AMS', 'AMS_LOCATION_ERROR');
1503 FND_MESSAGE.Set_Token('ROW','LOCATION - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1504 FND_MSG_PUB.add;
1505 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1506 RAISE FND_API.g_exc_unexpected_error;
1507 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1508 RAISE FND_API.g_exc_error;
1509 end if;
1510 end if;
1511
1512 end if; -- if location_rec.address1 is not NULL
1513 end if; -- x_location_id is null then
1514
1515 l_lp_psite_id := null;
1516 open PER_LOCATION_EXISTS;
1517 fetch PER_LOCATION_EXISTS into l_lp_psite_id;
1518 close PER_LOCATION_EXISTS;
1519 if l_lp_psite_id is null and x_per_party_id is not null and x_location_id is not null then
1520 -- Create Party Site
1521
1522 /*IF (AMS_DEBUG_HIGH_ON) THEN
1523
1524 AMS_UTILITY_PVT.debug_message('B2c:crating party_site ' || l_lp_psite_id);
1525 END IF;*/
1526 Write_log(L_API_NAME, 'B2c:crating party_site ' || l_lp_psite_id);
1527
1528 x_return_status := null;
1529 x_msg_count := null;
1530 x_msg_data := null;
1531 x_party_site_number := null;
1532 select hz_party_sites_s.nextval into x_party_site_id from dual;
1533 if x_gen_party_site_number = 'N' then
1534 select hz_party_site_number_s.nextval into x_party_site_number from dual;
1535 end if;
1536
1537 psite_rec.party_site_id := x_party_site_id;
1538 psite_rec.party_id := x_per_party_id;
1539 psite_rec.location_id := x_location_id;
1540 psite_rec.party_site_number := x_party_site_number;
1541 psite_rec.orig_system_reference := x_party_site_id;
1542 psite_rec.identifying_address_flag := p_rec.identifying_address_flag;
1543 -- psite_rec.site_use_code := p_rec.site_use_code;
1544 -- psite_rec.identifying_address_flag := 'Y';
1545 psite_rec.status := 'A';
1546 psite_rec.created_by_module := 'AMS_EVENT';
1547
1548
1549 /*
1550 IF (AMS_DEBUG_HIGH_ON) THEN
1551
1552
1553
1554 AMS_Utility_PVT.Debug_Message('Attempting to create a Party Site');
1555
1556 END IF;
1560
1557 */
1558
1559 Write_log(L_API_NAME, 'Attempting to create a Party Site');
1561
1562 hz_party_site_v2pub.create_party_site(
1563 p_init_msg_list => p_init_msg_list,
1564 p_party_site_rec => psite_rec,
1565 x_return_status => x_return_status,
1566 x_msg_count => x_msg_count,
1567 x_msg_data => x_msg_data,
1568 x_party_site_id => x_party_site_id,
1569 x_party_site_number => x_party_site_number
1570 );
1571
1572 /*
1573 IF (AMS_DEBUG_HIGH_ON) THEN
1574
1575 AMS_Utility_PVT.Debug_Message('Party Site return status: ' || x_return_status);
1576 END IF;
1577 */
1578 Write_log(L_API_NAME, 'Party Site return status: ' || x_return_status);
1579
1580 /*
1581 if x_msg_count > 1 then
1582 FOR i IN 1..x_msg_count LOOP
1583 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1584 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1585 END LOOP;
1586 x_msg_data := x_tmp_var1;
1587 END IF;
1588
1589 /* IF (AMS_DEBUG_HIGH_ON) THEN
1590
1591 AMS_Utility_PVT.Debug_Message('Changed Party Site messages');
1592 END IF;*/
1593
1594 Write_log(L_API_NAME, 'Changed Party Site messages');
1595
1596 if x_return_status <> 'S' then
1597
1598 /*IF (AMS_DEBUG_HIGH_ON) THEN
1599
1600 AMS_Utility_PVT.Debug_Message('Error Creating Site');
1601 END IF;*/
1602
1603 Write_log(L_API_NAME, 'Error Creating Site');
1604
1605
1606 --errbuf := 'PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1607 FND_MESSAGE.set_name('AMS', 'AMS_PSITE_ERROR');
1608 FND_MESSAGE.Set_Token('ROW','PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1609 FND_MSG_PUB.add;
1610 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1611 RAISE FND_API.g_exc_unexpected_error;
1612 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1613 RAISE FND_API.g_exc_error;
1614 end if;
1615 end if;
1616 if (p_rec.site_use_code is not null)
1617 then
1618 select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;
1619
1620 psite_use_rec.site_use_type := p_rec.site_use_code;
1621 psite_use_rec.party_site_id := x_party_site_id;
1622 psite_use_rec.party_site_use_id := x_party_site_use_id;
1623
1624 hz_party_site_v2pub.create_party_site_use(
1625 p_init_msg_list => p_init_msg_list,
1626 p_party_site_use_rec => psite_use_rec,
1627 x_return_status => x_return_status,
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data,
1630 x_party_site_use_id => x_party_site_use_id);
1631
1632 /*
1633 if x_msg_count > 1 then
1634 FOR i IN 1..x_msg_count LOOP
1635 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1636 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1637 END LOOP;
1638 x_msg_data := x_tmp_var1;
1639 END IF;
1640 */
1641 if x_return_status <> 'S' then
1642 --errbuf := 'PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1643 FND_MESSAGE.set_name('AMS', 'AMS_PART_SITE_CREATION_FAILURE');
1644 FND_MESSAGE.Set_Token('ROW','PSITE - '||substr(location_rec.address1,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1645 FND_MSG_PUB.add;
1646 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1647 RAISE FND_API.g_exc_unexpected_error;
1648 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1649 RAISE FND_API.g_exc_error;
1650 end if;
1651 end if;
1652 end if; -- site use code
1653
1654 end if; -- if l_lp_psite_id is null then
1655
1656 -- ***************************************************8
1657 -- Create contact points Phone
1658
1659 if x_phone_number is not NULL and x_per_party_id is not null then
1660 SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
1661
1662 x_return_status := null;
1663 x_msg_count := null;
1664 x_msg_data := null;
1665 cpoint_rec.contact_point_id := x_contact_point_id;
1666 cpoint_rec.contact_point_type := 'PHONE';
1667 cpoint_rec.status := 'A';
1668 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1669 cpoint_rec.owner_table_id := x_per_party_id;
1670 -- cpoint_rec.primary_flag := 'Y';
1671 cpoint_rec.orig_system_reference := x_contact_point_id;
1672 cpoint_rec.created_by_module := 'AMS_EVENT';
1673 phone_rec.phone_line_type := 'GEN';
1674 phone_rec.phone_number := x_phone_number;
1675 phone_rec.phone_country_code := x_phone_country_code;
1676 phone_rec.phone_area_code := x_phone_area_code;
1677 phone_rec.phone_extension := x_phone_extention;
1678
1679 l_phone_exists := NULL;
1683 if l_phone_exists is NULL then
1680 open phone_exists(x_per_party_id);
1681 fetch phone_exists into l_phone_exists;
1682 close phone_exists;
1684 /*IF (AMS_DEBUG_HIGH_ON) THEN
1685
1686 AMS_UTILITY_PVT.debug_message('B2c:crating contact - Phone ' || x_per_party_id);
1687 END IF;*/
1688 Write_log(L_API_NAME, 'B2c:crating contact - Phone ' || x_per_party_id);
1689
1690 hz_contact_point_v2pub.create_contact_point(
1691 'F',
1692 cpoint_rec,
1693 edi_rec,
1694 email_rec,
1695 phone_rec,
1696 telex_rec,
1697 web_rec,
1698 x_return_status,
1699 x_msg_count,
1700 x_msg_data,
1701 x_contact_point_id);
1702 /*
1703 if x_msg_count > 1 then
1704 FOR i IN 1..x_msg_count LOOP
1705 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1706 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1707 END LOOP;
1708 x_msg_data := x_tmp_var1;
1709 END IF;
1710 */
1711 if x_return_status <> 'S' then
1712 --errbuf := 'PHONE - '||substr(x_phone_number,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1713 FND_MESSAGE.set_name('AMS', 'AMS_CONTACT_PHONE_ERROR');
1714 FND_MESSAGE.Set_Token('ROW','PHONE - '||substr(x_phone_number,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1715 FND_MSG_PUB.add;
1716 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1717 RAISE FND_API.g_exc_unexpected_error;
1718 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1719 RAISE FND_API.g_exc_error;
1720 end if;
1721 end if;
1722 end if; -- l_phone_exists is NULL then
1723 end if; -- x_phone_number
1724
1725
1726
1727
1728 -- Create contact points Email
1729
1730 if x_email_address is not NULL and x_per_party_id is not null then
1731 SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
1732
1733 x_return_status := null;
1734 x_msg_count := null;
1735 x_msg_data := null;
1736 cpoint_rec.contact_point_id := x_contact_point_id;
1737 cpoint_rec.contact_point_type := 'EMAIL';
1738 cpoint_rec.status := 'A';
1739 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1740 cpoint_rec.owner_table_id := x_per_party_id;
1741 -- cpoint_rec.primary_flag := 'Y';
1742 cpoint_rec.orig_system_reference := x_contact_point_id;
1743 cpoint_rec.created_by_module := 'AMS_EVENT';
1744
1745 email_rec.email_address := x_email_address;
1746 l_email_exists := NULL;
1747 open email_exists(x_per_party_id);
1748 fetch email_exists into l_email_exists;
1749 close email_exists;
1750 if l_email_exists is NULL then
1751 /*IF (AMS_DEBUG_HIGH_ON) THEN
1752
1753 AMS_UTILITY_PVT.debug_message('B2c:crating contact - Email ' || x_per_party_id);
1754 END IF;*/
1755 Write_log(L_API_NAME,'B2c:crating contact - Email ' || x_per_party_id);
1756 hz_contact_point_v2pub.create_contact_point(
1757 'F',
1758 cpoint_rec,
1759 edi_rec,
1760 email_rec,
1761 phone_rec,
1762 telex_rec,
1763 web_rec,
1764 x_return_status,
1765 x_msg_count,
1766 x_msg_data,
1767 x_contact_point_id);
1768
1769 /*
1770 if x_msg_count > 1 then
1771 FOR i IN 1..x_msg_count LOOP
1772 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1773 x_tmp_var1 := x_tmp_var1 || ' '|| x_tmp_var;
1774 END LOOP;
1775 x_msg_data := x_tmp_var1;
1776 END IF;
1777 */
1778 if x_return_status <> 'S' then
1779 --errbuf := 'EMAIL - '||substr(x_email_address,1,25)||'- ERROR-'||substr(x_msg_data,1,180);
1780 FND_MESSAGE.set_name('AMS', 'AMS_CONTACT_EMAIL_ERROR');
1781 FND_MESSAGE.Set_Token('ROW','EMAIL - '||substr(x_email_address,1,25)||'- ERROR-'||substr(x_msg_data,1,180));
1782 FND_MSG_PUB.add;
1783 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1784 RAISE FND_API.g_exc_unexpected_error;
1785 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1786 RAISE FND_API.g_exc_error;
1787 end if;
1788 end if;
1789 end if; -- l_email_exists is NULL then
1790 end if; -- x_email_address
1791 end if; -- b2c
1792 EXCEPTION
1793 WHEN FND_API.g_exc_error THEN
1794 x_return_status := FND_API.g_ret_sts_error;
1795 FND_MSG_PUB.count_and_get(
1796 p_encoded => FND_API.g_false,
1797 p_count => x_msg_count,
1798 p_data => x_msg_data
1799 );
1800
1801 WHEN FND_API.g_exc_unexpected_error THEN
1802 x_return_status := FND_API.g_ret_sts_unexp_error ;
1803 FND_MSG_PUB.count_and_get(
1804 p_encoded => FND_API.g_false,
1808
1805 p_count => x_msg_count,
1806 p_data => x_msg_data
1807 );
1809 WHEN OTHERS THEN
1810 x_return_status := FND_API.g_ret_sts_unexp_error ;
1811 FND_MSG_PUB.count_and_get(
1812 p_encoded => FND_API.g_false,
1813 p_count => x_msg_count,
1814 p_data => x_msg_data
1815 );
1816
1817
1818 end create_registrant_party;
1819 /*=========================================================================================*/
1820
1821 PROCEDURE person_party_echeck( p_party_id IN OUT NOCOPY NUMBER
1822 , p_per_first_name IN VARCHAR2
1823 , p_per_last_name IN VARCHAR2
1824 , p_address1 IN VARCHAR2
1825 , p_country IN VARCHAR2
1826 , p_email_address IN VARCHAR2
1827 , p_ph_country_code IN VARCHAR2
1828 , p_ph_area_code IN VARCHAR2
1829 , p_ph_number IN VARCHAR2
1830 )
1831
1832 IS
1833
1834 l_party_key varchar2(1000);
1835 L_COUNT number;
1836 l_max_party_id number;
1837 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
1838 l_cust_exists varchar2(1);
1839 l_ret_status varchar(1);
1840
1841 cursor c_email_address is
1842 select max(p.party_id)
1843 from hz_contact_points cp,
1844 hz_parties p
1845 where p.customer_key = l_party_key
1846 and p.party_type = 'PERSON'
1847 and cp.owner_table_id = p.party_id
1848 and cp.owner_table_name = 'HZ_PARTIES'
1849 and cp.email_address = p_email_address
1850 and cp.primary_flag = 'Y';
1851
1852 cursor c_ph_number is
1853 select max(p.party_id)
1854 from hz_contact_points cp,
1855 hz_parties p
1856 where p.customer_key = l_party_key
1857 and p.party_type = 'PERSON'
1858 and cp.owner_table_id = p.party_id
1859 and cp.owner_table_name = 'HZ_PARTIES'
1860 and cp.primary_flag = 'Y'
1861 and cp.phone_number = p_ph_number
1862 and nvl(cp.phone_country_code, nvl(p_ph_country_code, 'x')) = nvl(p_ph_country_code, 'x')
1863 and nvl(cp.phone_area_code, nvl(p_ph_area_code, 'x')) = nvl(p_ph_area_code, 'x');
1864
1865 cursor c_address_country is
1866 select max(psite.party_id)
1867 from hz_party_sites psite,
1868 hz_locations loc,
1869 hz_parties party
1870 where psite.location_id = loc.location_id
1871 and loc.address1 = p_address1
1872 and loc.country = p_country
1873 and party.customer_key = l_party_key
1874 and psite.party_id = party.party_id;
1875
1876 cursor c_person_exists is
1877 select 'Y'
1878 from hz_parties
1879 where customer_key = l_party_key
1880 and party_type = 'PERSON';
1881
1882 begin
1883
1884 -- Generates the customer key for PERSON
1885 if ( (p_per_last_name is not null)
1886 and (p_per_first_name is not null)
1887 )
1888 then
1889 l_party_key := hz_fuzzy_pub.Generate_Key ( p_key_type => 'PERSON'
1890 , p_first_name => p_per_first_name
1891 , p_last_name => p_per_last_name
1892 );
1893 /*IF (AMS_DEBUG_HIGH_ON) THEN
1894
1895 AMS_Utility_PVT.debug_message('person_party_echeck - party key: ' || l_party_key);
1896 END IF;*/
1897 Write_log('person_party_echeck','person_party_echeck - party key: ' || l_party_key);
1898
1899 open c_person_exists;
1900 fetch c_person_exists
1901 into l_cust_exists;
1902 close c_person_exists;
1903 end if; -- first/last name
1904
1905 -- If customer does not exist then it's a new customer.
1906 if (l_cust_exists is NULL) then
1907 return;
1908 end if;
1909
1910 -- If email address is provided
1911 if (p_email_address is not null)
1912 then
1913 open c_email_address;
1914 fetch c_email_address
1915 into p_party_id;
1916 close c_email_address;
1917 if (p_party_id is not null)
1918 then
1919 return;
1920 end if; -- party id
1921 end if; -- email address
1922
1923 -- If phone number is provided
1924 if (p_ph_number is not null)
1925 then
1926 open c_ph_number;
1927 fetch c_ph_number
1928 into p_party_id;
1929 close c_ph_number;
1930 if (p_party_id is not null)
1931 then
1932 return;
1933 end if; -- party id
1934 end if; -- phone number
1935
1936 -- When address1 and country is provided
1937 if ( (p_address1 is not null)
1938 and (p_country is not null)
1939 )
1940 then
1941 open c_address_country;
1942 fetch c_address_country
1943 into p_party_id;
1944 close c_address_country;
1945 if (p_party_id is not null)
1946 then
1947 return;
1948 end if; -- party id
1949 end if; -- address/country
1950
1951 exception
1952
1956
1953 when others
1954 then
1955 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1957 end person_party_echeck;
1958
1959 PROCEDURE party_echeck(
1960 --p_impt_list_header_id IN NUMBER,
1961 p_party_id IN OUT NOCOPY NUMBER,
1962 p_org_name IN VARCHAR2,
1963 p_per_first_name IN VARCHAR2,
1964 p_per_last_name IN VARCHAR2,
1965 p_address1 IN VARCHAR2,
1966 p_country IN VARCHAR2
1967 )
1968
1969 IS
1970
1971 l_party_key varchar2(1000);
1972 L_COUNT number;
1973 l_max_party_id number;
1974 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
1975 x_org_party_id number;
1976 l_ps_party_id number;
1977 l_cust_exists varchar2(1);
1978 l_ret_status varchar(1);
1979 x_per_party_id number;
1980 L_API_NAME CONSTANT VARCHAR2(30) := ' party_echeck';
1981
1982 cursor c_address_country is
1983 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
1984 hz_parties party
1985 where psite.location_id = loc.location_id
1986 and loc.address1 = p_address1
1987 and loc.country = p_country
1988 and party.customer_key = l_party_key
1989 and psite.party_id = party.party_id;
1990
1991 cursor c_country is
1992 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
1993 hz_parties party
1994 where psite.location_id = loc.location_id
1995 and loc.country = p_country
1996 and party.customer_key = l_party_key
1997 and psite.party_id = party.party_id;
1998
1999 cursor c_customer_exists is
2000 select 'Y' from hz_parties
2001 where customer_key = l_party_key
2002 and party_type = 'ORGANIZATION';
2003
2004 cursor c_person_exists is
2005 select 'Y' from hz_parties
2006 where customer_key = l_party_key
2007 and party_type = 'PERSON';
2008 begin
2009
2010 --
2011 -- Generates the customer key for ORGANIZATION
2012 --
2013 if p_org_name is not null then
2014 l_party_key := hz_fuzzy_pub.Generate_Key (
2015 p_key_type => 'ORGANIZATION',
2016 p_party_name => p_org_name
2017 );
2018 open c_customer_exists;
2019 fetch c_customer_exists into l_cust_exists;
2020 close c_customer_exists;
2021 end if;
2022 --
2023 -- Generates the customer key for PERSON
2024 --
2025 if p_per_last_name is not null and p_per_first_name is not null then
2026 l_party_key := hz_fuzzy_pub.Generate_Key (
2027 p_key_type => 'PERSON',
2028 p_first_name => p_per_first_name,
2029 p_last_name => p_per_last_name
2030 );
2031 open c_person_exists;
2032 fetch c_person_exists into l_cust_exists;
2033 close c_person_exists;
2034 end if;
2035
2036 --
2037 -- If customer does not exists then it's a new customer.
2038 --
2039 if l_cust_exists is NULL then
2040 return;
2041 end if;
2042
2043 --
2044 -- When address1 and country is provided
2045 --
2046 if l_cust_exists = 'Y' and p_address1 is not null and p_country is not null then
2047 /*IF (AMS_DEBUG_HIGH_ON) THEN
2048
2049 AMS_UTILITY_PVT.Debug_Message('Customer exists - checking address');
2050 END IF;*/
2051 Write_log(L_API_NAME, 'Customer exists - checking address');
2052 open c_address_country;
2053 fetch c_address_country into l_ps_party_id;
2054 close c_address_country;
2055
2056 -- if party site not found for this address and country then serch for only country
2057 if l_ps_party_id is NULL then
2058 /*IF (AMS_DEBUG_HIGH_ON) THEN
2059
2060 AMS_UTILITY_PVT.Debug_Message('Did not find an address - checking country...');
2061 END IF;*/
2062 Write_log(L_API_NAME, 'Did not find an address - checking country...');
2063 open c_country;
2064 fetch c_country into l_ps_party_id;
2065 close c_country;
2066 return;
2067 end if;
2068 if l_ps_party_id is not NULL then
2069 p_party_id := l_ps_party_id;
2070 return;
2071 end if;
2072 end if;
2073
2074
2075 /*IF (AMS_DEBUG_HIGH_ON) THEN
2076 AMS_UTILITY_PVT.Debug_Message('Moving on from address check...');
2077 END IF;*/
2078 Write_log(L_API_NAME, 'Moving on from address check...');
2079
2080 --
2081 -- When customer exists and address1 and country is not provided
2082 -- OR party site does not exists
2083 -- then take the max party_id from the available records.
2084 --
2085 if l_cust_exists = 'Y' and
2086 (
2087 (p_address1 is null and p_country is null)
2088 or (l_ps_party_id is null)
2089 ) then
2090 --
2091 -- For ORGANIZATION get the max party_id
2092 --
2093 if p_org_name is not null then
2094 L_COUNT := 0;
2095 l_max_party_id := 0;
2096 hz_fuzzy_pub.FUZZY_SEARCH_PARTY(
2097 'ORGANIZATION',
2098 p_org_name,
2099 null,
2100 null,
2101 l_party_tbl,
2102 L_COUNT);
2103 if L_COUNT > 0 then
2107 end if;
2104 for i in 1..l_count loop
2105 if l_party_tbl(i) > l_max_party_id then
2106 l_max_party_id := l_party_tbl(i);
2108 end loop;
2109 x_org_party_id := l_max_party_id;
2110 p_party_id := x_org_party_id;
2111 end if;
2112 end if;
2113 end if;
2114
2115 --
2116 -- For PERSON get the max party_id
2117 --
2118 if p_per_last_name is not null and p_per_first_name is not null then
2119 L_COUNT := 0;
2120 l_max_party_id := 0;
2121 hz_fuzzy_pub.FUZZY_SEARCH_PARTY(
2122 'PERSON',
2123 null,
2124 p_per_first_name,
2125 p_per_last_name,
2126 l_party_tbl,
2127 L_COUNT);
2128 if L_COUNT > 0 then
2129 for i in 1..l_count loop
2130 if l_party_tbl(i) > l_max_party_id then
2131 l_max_party_id := l_party_tbl(i);
2132 end if;
2133 end loop;
2134 x_per_party_id := l_max_party_id;
2135 p_party_id := x_per_party_id;
2136 end if;
2137 end if;
2138
2139 end party_echeck;
2140
2141 -- ---------------------------------------------
2142
2143 PROCEDURE contact_echeck(
2144 --p_impt_list_header_id IN NUMBER,
2145 p_party_id IN OUT NOCOPY NUMBER,
2146 p_org_party_id IN NUMBER,
2147 p_per_first_name IN VARCHAR2,
2148 p_per_last_name IN VARCHAR2,
2149 p_phone_area_code IN VARCHAR2,
2150 p_phone_number IN VARCHAR2,
2151 p_phone_extension IN VARCHAR2,
2152 p_email_address IN VARCHAR2
2153 ) IS
2154
2155 l_ret_status varchar(1);
2156 x_per_party_id number;
2157 l_party_key varchar(1000);
2158 l_cust_exists varchar(1);
2159 l_email_party_id number;
2160 l_phone_party_id number;
2161 L_COUNT number;
2162 l_max_party_id number;
2163 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
2164
2165
2166 cursor c_customer_exists is
2167 select 'Y' from hz_parties
2168 where customer_key = l_party_key
2169 and party_type = 'PERSON';
2170
2171 cursor c_cont_email is
2172 select max(per.party_id) from
2173 hz_parties org,
2174 hz_parties per,
2175 hz_relationships rel,
2176 hz_contact_points cpoint
2177 where org.party_id = p_org_party_id
2178 and org.party_type = 'ORGANIZATION'
2179 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2180 and rel.SUBJECT_TYPE = 'PERSON'
2181 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2182 and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2183 and rel.OBJECT_ID = org.party_id
2184 and rel.SUBJECT_ID = per.PARTY_ID
2185 and per.customer_key = l_party_key
2186 and cpoint.owner_table_id = rel.party_id
2187 and cpoint.owner_table_name = 'HZ_PARTIES'
2188 and cpoint.contact_point_type = 'EMAIL'
2189 and cpoint.email_address = p_email_address
2190 and cpoint.status = 'A';
2191
2192
2193 cursor c_cont_email_phone is
2194 select max(per.party_id) from
2195 hz_parties org,
2196 hz_parties per,
2197 hz_relationships rel,
2198 hz_contact_points cpoint,
2199 hz_contact_points cpoint1
2200 where org.party_id = p_org_party_id
2201 and org.party_type = 'ORGANIZATION'
2202 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2203 and rel.SUBJECT_TYPE = 'PERSON'
2204 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2205 and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2206 and rel.OBJECT_ID = org.party_id
2207 and rel.SUBJECT_ID = per.PARTY_ID
2208 and per.customer_key = l_party_key
2209 and cpoint.owner_table_id = rel.party_id
2210 and cpoint.owner_table_name = 'HZ_PARTIES'
2211 and cpoint.contact_point_type = 'EMAIL'
2212 and cpoint.email_address = p_email_address
2213 and cpoint.status = 'A'
2214 and cpoint1.owner_table_id = rel.party_id
2215 and cpoint1.owner_table_name = 'HZ_PARTIES'
2216 and cpoint1.contact_point_type = 'PHONE'
2217 and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
2218 p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
2219 and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
2220 and cpoint1.status = 'A';
2221
2222
2223 cursor c_cont_phone is
2224 select max(per.party_id) from
2225 hz_parties org,
2226 hz_parties per,
2227 hz_relationships rel,
2228 hz_contact_points cpoint
2229 where org.party_id = p_org_party_id
2230 and org.party_type = 'ORGANIZATION'
2231 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2232 and rel.SUBJECT_TYPE = 'PERSON'
2233 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2234 and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2235 and rel.OBJECT_ID = org.party_id
2236 and rel.SUBJECT_ID = per.PARTY_ID
2240 and cpoint.contact_point_type = 'PHONE'
2237 and per.customer_key = l_party_key
2238 and cpoint.owner_table_id = rel.party_id
2239 and cpoint.owner_table_name = 'HZ_PARTIES'
2241 and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
2242 p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
2243 and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
2244 and cpoint.status = 'A';
2245
2246 begin
2247 --
2248 -- Generates the customer key for PERSON
2249 --
2250 if p_per_last_name is not null and p_per_first_name is not null then
2251 l_party_key := hz_fuzzy_pub.Generate_Key (
2252 p_key_type => 'PERSON',
2253 p_first_name => p_per_first_name,
2254 p_last_name => p_per_last_name
2255 );
2256 end if;
2257 --
2258 -- If customer does not exists then it's a new customer.
2259 --
2260 open c_customer_exists;
2261 fetch c_customer_exists into l_cust_exists;
2262 close c_customer_exists;
2263 if l_cust_exists is NULL then
2264 return; -- ORG CONTACT DOES NOT EXISTS CHECKED WITH CUSTOMER_KEY.
2265 end if;
2266
2267 open c_cont_email_phone;
2268 fetch c_cont_email_phone into x_per_party_id;
2269 close c_cont_email_phone;
2270 if x_per_party_id is not null then
2271 p_party_id := x_per_party_id; -- ORG CONTACT DOES NOT EXISTS WITH EMAIL AND PHONE NUMBER.
2272 return;
2273 end if;
2274 --
2275 -- Either email_address and phone number is available.
2276 --
2277 if l_cust_exists = 'Y' and (p_email_address is not null or p_phone_number is not null) then
2278 open c_cont_email;
2279 fetch c_cont_email into l_email_party_id;
2280 close c_cont_email;
2281 if l_email_party_id is not null then
2282 p_party_id := l_email_party_id;
2283 return;
2284 end if;
2285
2286 open c_cont_phone;
2287 fetch c_cont_phone into l_phone_party_id;
2288 close c_cont_phone;
2289 if l_phone_party_id is not null then
2290 p_party_id := l_phone_party_id;
2291 end if;
2292 end if;
2293 end contact_echeck;
2294 --
2295 -- This procedure is used for existence checking for address.
2296 --
2297 --
2298 PROCEDURE address_echeck(
2299 --p_impt_list_header_id IN NUMBER,
2300 p_party_id IN NUMBER,
2301 p_location_id IN OUT NOCOPY NUMBER,
2302 p_address1 IN VARCHAR2,
2303 p_city IN VARCHAR2,
2304 p_pcode IN VARCHAR2,
2305 p_country IN VARCHAR2
2306 ) is
2307
2308 l_address_key varchar(1000);
2309 l_loc_id number;
2310 l_ret_status varchar(1);
2311
2312
2313 cursor c_addr_ps is
2314 select max(loc.location_id)
2315 from hz_party_sites ps, hz_locations loc
2316 where ps.party_id = p_party_id
2317 and ps.location_id = loc.location_id
2318 and loc.address_key = l_address_key
2319 and loc.country = p_country
2320 and nvl(loc.city,p_city) = p_city;
2321
2322 cursor c_addr is
2323 select max(loc.location_id)
2324 from hz_locations loc
2325 where loc.address_key = l_address_key
2326 and loc.country = p_country
2327 and nvl(loc.city,p_city) = p_city;
2328
2329 begin
2330
2331 l_address_key := hz_fuzzy_pub.Generate_Key (
2332 p_key_type => 'ADDRESS',
2333 p_address1 => p_address1,
2334 p_postal_code => p_pcode
2335 );
2336
2337 open c_addr_ps;
2338 fetch c_addr_ps into l_loc_id;
2339 close c_addr_ps;
2340 if l_loc_id is null then
2341 open c_addr;
2342 fetch c_addr into l_loc_id;
2343 close c_addr;
2344 end if;
2345 p_location_id := l_loc_id;
2346
2347 end address_echeck;
2348
2349 PROCEDURE get_party_id(
2350 p_api_version IN NUMBER,
2351 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2352 p_commit IN VARCHAR2 := FND_API.g_false,
2353 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2354 p_rec IN party_detail_rec_type,
2355
2356 x_return_status OUT NOCOPY VARCHAR2,
2357 x_msg_count OUT NOCOPY NUMBER,
2358 x_msg_data OUT NOCOPY VARCHAR2,
2359
2360 x_new_party_id OUT NOCOPY NUMBER,
2361 x_new_org_party_id OUT NOCOPY NUMBER
2362 ) IS
2363 l_party_id NUMBER;
2364 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2365 L_API_NAME CONSTANT VARCHAR2(30) := 'get_party_id';
2366 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2367
2368 -- all declarations starting here added by soagrawa on 11-dec-2002
2369 -- to move to v2apis
2370
2371 party_rec hz_party_v2pub.party_rec_type;
2372 org_rec hz_party_v2pub.organization_rec_type;
2376 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
2373 person_rec hz_party_v2pub.person_rec_type;
2374 location_rec hz_location_v2pub.location_rec_type;
2375 psite_rec hz_party_site_v2pub.party_site_rec_type;
2377 email_rec hz_contact_point_v2pub.email_rec_type;
2378 phone_rec hz_contact_point_v2pub.phone_rec_type;
2379 fax_rec hz_contact_point_v2pub.phone_rec_type;
2380 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
2381 edi_rec hz_contact_point_v2pub.edi_rec_type;
2382 telex_rec hz_contact_point_v2pub.telex_rec_type;
2383 web_rec hz_contact_point_v2pub.web_rec_type;
2384 psite_use_rec hz_party_site_v2pub.party_site_use_rec_type;
2385 x_new_party VARCHAR2(1);
2386 x_component_name VARCHAR2(60);
2387 l_b2b_flag VARCHAR2(1);
2388
2389 CURSOR c_relationship_det(p_id NUMBER) IS
2390 SELECT object_id, subject_id
2391 FROM hz_relationships
2392 WHERE party_id = p_id
2393 -- dbiswas 10 Jun 2003 fix for bug 2949603
2394 -- AND directional_flag = 'F';
2395 AND subject_Type = 'PERSON'
2396 AND object_Type = 'ORGANIZATION';
2397
2398
2399 BEGIN
2400 -- Standard Start of API savepoint
2401 SAVEPOINT Register_get_party_id_PVT;
2402
2403 /*IF (AMS_DEBUG_HIGH_ON) THEN
2404 AMS_Utility_PVT.debug_message(l_full_name || ': start');
2405 END IF;*/
2406 Write_log(L_API_NAME, l_full_name || ': start');
2407
2408
2409 -- Standard call to check for call compatibility.
2410 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2411 p_api_version,
2412 l_api_name,
2413 G_PKG_NAME)
2414 THEN
2415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2416 END IF;
2417
2418 -- Initialize message list if p_init_msg_list is set to TRUE.
2419 IF FND_API.to_Boolean( p_init_msg_list )
2420 THEN
2421 FND_MSG_PUB.initialize;
2422 END IF;
2423
2424 x_return_status := FND_API.g_ret_sts_success;
2425 /*
2426 AMS_Registrants_PVT.find_a_party(
2427 p_api_version => 1.0,
2428 p_init_msg_list => FND_API.g_false,
2429 p_rec => p_rec,
2430 x_return_status => x_return_status,
2431 x_msg_count => x_msg_count,
2432 x_msg_data => x_msg_data,
2433 x_party_id => x_new_party_id
2434 );
2435 IF x_return_status = FND_API.g_ret_sts_error THEN
2436 RAISE FND_API.g_exc_error;
2437 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2438 RAISE FND_API.g_exc_unexpected_error;
2439 END IF;
2440 if x_new_party_id is NULL THEN
2441 */
2442
2443 /*
2444 IF (AMS_DEBUG_HIGH_ON) THEN
2445 AMS_Utility_PVT.Debug_Message('Create Party');
2446 END IF;
2447 */
2448 Write_log(L_API_NAME, 'Create Party');
2449
2450
2451 -- replaced by soagrawa on 29-oct-2002
2452 -- now calling list import APIs instead
2453 /*
2454 AMS_Registrants_PVT.create_registrant_party(
2455 p_api_version => 1.0,
2456 p_init_msg_list => FND_API.g_false,
2457 p_commit => FND_API.g_false,
2458 p_validation_level => FND_API.g_valid_level_full,
2459 p_rec => p_rec,
2460
2461 x_return_status => x_return_status,
2462 x_msg_count => x_msg_count,
2463 x_msg_data => x_msg_data,
2464 x_new_party_id => x_new_party_id,
2465 x_new_org_party_id => x_new_org_party_id
2466 );
2467 */
2468
2469 -- gotta determine if it is B2B or B2C
2470
2471 IF p_rec.party_name IS NOT NULL
2472 THEN
2473 -- it is B2B
2474 l_b2b_flag := 'Y';
2475 /*
2476 IF (AMS_DEBUG_HIGH_ON) THEN
2477 AMS_Utility_PVT.Debug_Message('B2B');
2478 END IF;
2479 */
2480 Write_log(L_API_NAME, 'B2B');
2481 org_rec.organization_name := p_rec.party_name;
2482
2483 person_rec.person_first_name := p_rec.first_name;
2484 person_rec.person_middle_name := p_rec.middle_name;
2485 person_rec.person_last_name := p_rec.last_name;
2486 person_rec.person_name_suffix := p_rec.name_suffix;
2487 person_rec.person_title := p_rec.title;
2488 person_rec.tax_reference := p_rec.tax_reference;
2489 person_rec.jgzz_fiscal_code := p_rec.jgzz_fiscal_code;
2490 person_rec.gender := p_rec.gender;
2491 person_rec.created_by_module := 'AMS_EVENT';
2492
2493 location_rec.country := p_rec.country;
2494 location_rec.address1 := p_rec.address1;
2495 location_rec.address2 := p_rec.address2;
2496 location_rec.address3 := p_rec.address3;
2497 location_rec.address4 := p_rec.address4;
2498 location_rec.city := p_rec.city;
2499 location_rec.postal_code := p_rec.postal_code;
2503 location_rec.address_lines_phonetic := p_rec.address_line_phonetic;
2500 location_rec.state := p_rec.state;
2501 location_rec.province := p_rec.province;
2502 location_rec.county := p_rec.county;
2504 -- location_rec.po_box_number := p_rec.po_box_no;
2505 -- location_rec.house_number := p_rec.house_number;
2506 -- location_rec.street_suffix := p_rec.street_suffix;
2507 -- location_rec.street := p_rec.street;
2508 -- location_rec.street_number := p_rec.street_number;
2509 -- location_rec.floor := p_rec.floor;
2510 -- location_rec.suite := p_rec.suite;
2511 location_rec.postal_plus4_code := p_rec.postal_plus4_code;
2512 location_rec.timezone_id := p_rec.timezone;
2513 location_rec.created_by_module := 'AMS_EVENT';
2514
2515 phone_rec.phone_area_code := p_rec.phone_area_code;
2516 phone_rec.phone_country_code := p_rec.phone_country_code;
2517 phone_rec.phone_extension := p_rec.phone_extension;
2518 phone_rec.phone_number := p_rec.phone_number;
2519
2520 psite_rec.identifying_address_flag := p_rec.identifying_address_flag;
2521
2522 email_rec.email_address := p_rec.email_address;
2523
2524 web_rec.url := p_rec.url;
2525
2526 ocon_rec.department := p_rec.department;
2527 ocon_rec.title := p_rec.title;
2528 ocon_rec.job_title := p_rec.job_title;
2529 ocon_rec.decision_maker_flag := p_rec.decision_maker_flag;
2530 ocon_rec.created_by_module := 'AMS_EVENT';
2531
2532 ELSE
2533 -- it is B2C
2534 l_b2b_flag := 'N';
2535 /*
2536 IF (AMS_DEBUG_HIGH_ON) THEN
2537 AMS_Utility_PVT.Debug_Message('B2C');
2538 END IF;
2539 */
2540 Write_log(L_API_NAME, 'B2C');
2541
2542 person_rec.person_first_name := p_rec.first_name;
2543 person_rec.person_middle_name := p_rec.middle_name;
2544 person_rec.person_last_name := p_rec.last_name;
2545 person_rec.person_name_suffix := p_rec.name_suffix;
2546 person_rec.person_title := p_rec.title;
2547 person_rec.tax_reference := p_rec.tax_reference;
2548 person_rec.jgzz_fiscal_code := p_rec.jgzz_fiscal_code;
2549 person_rec.gender := p_rec.gender;
2550 person_rec.created_by_module := 'AMS_EVENT';
2551
2552 location_rec.country := p_rec.country;
2553 location_rec.address1 := p_rec.address1;
2554 location_rec.address2 := p_rec.address2;
2555 location_rec.address3 := p_rec.address3;
2556 location_rec.address4 := p_rec.address4;
2557 location_rec.city := p_rec.city;
2558 location_rec.postal_code := p_rec.postal_code;
2559 location_rec.state := p_rec.state;
2560 location_rec.province := p_rec.province;
2561 location_rec.county := p_rec.county;
2562 location_rec.address_lines_phonetic := p_rec.address_line_phonetic;
2563 -- location_rec.po_box_number := p_rec.po_box_no;
2564 -- location_rec.house_number := p_rec.house_number;
2565 -- location_rec.street_suffix := p_rec.street_suffix;
2566 -- location_rec.street := p_rec.street;
2567 -- location_rec.street_number := p_rec.street_number;
2568 -- location_rec.floor := p_rec.floor;
2569 -- location_rec.suite := p_rec.suite;
2570 location_rec.postal_plus4_code := p_rec.postal_plus4_code;
2571 location_rec.timezone_id := p_rec.timezone;
2572 location_rec.created_by_module := 'AMS_EVENT';
2573
2574 phone_rec.phone_area_code := p_rec.phone_area_code;
2575 phone_rec.phone_country_code := p_rec.phone_country_code;
2576 phone_rec.phone_extension := p_rec.phone_extension;
2577 phone_rec.phone_number := p_rec.phone_number;
2578
2579 -- soagrawa added web_rec on 11-feb-2003 as create_customer API signature changed
2580 web_rec.url := p_rec.url;
2581
2582 email_rec.email_address := p_rec.email_address;
2583
2584 /*
2585 IF (AMS_DEBUG_HIGH_ON) THEN
2586 AMS_Utility_PVT.Debug_Message('Getting email address: '||email_rec.email_address);
2587 END IF;
2588 */
2589 Write_log(L_API_NAME, 'Getting email address: '||email_rec.email_address);
2590
2591 END IF;
2592
2593 AMS_List_Import_PUB.Create_Customer (
2594 p_api_version => 1.0,
2595 p_init_msg_list => FND_API.g_false,
2596 p_commit => FND_API.g_false,
2597 x_return_status => x_return_status,
2598 x_msg_count => x_msg_count,
2599 x_msg_data => x_msg_data,
2600 p_party_id => l_party_id,
2601 p_b2b_flag => l_b2b_flag,
2602 p_import_list_header_id => NULL,
2603 p_party_rec => party_rec,
2604 p_org_rec => org_rec,
2605 p_person_rec => person_rec,
2606 p_location_rec => location_rec,
2607 p_psite_rec => psite_rec,
2608 p_cpoint_rec => cpoint_rec,
2609 p_email_rec => email_rec,
2610 p_phone_rec => phone_rec,
2611 p_siteuse_rec => psite_use_rec,
2612 p_fax_rec => fax_rec,
2613 p_ocon_rec => ocon_rec,
2614 -- soagrawa added web_rec on 11-feb-2003 as create_customer API signature changed
2615 p_web_rec => web_rec,
2616 x_new_party => x_new_party, -- will return Y or N
2617 p_component_name => x_component_name); -- component in which error occurred
2618
2619 /*
2620 IF (AMS_DEBUG_HIGH_ON) THEN
2621 AMS_Utility_PVT.Debug_Message('After create_Customer done');
2622 AMS_Utility_PVT.Debug_Message('Return Status: '||x_return_status);
2623 --AMS_Utility_PVT.Debug_Message('Return Status: '||x_new_party);
2624 --AMS_Utility_PVT.Debug_Message('Return Status: '||x_component_name);
2625 END IF;
2626 */
2627 Write_log(L_API_NAME, 'After create_Customer done');
2628 Write_log(L_API_NAME, 'Return Status: '||x_return_status);
2629
2630 IF x_return_status = FND_API.g_ret_sts_error THEN
2631
2632 /*IF (AMS_DEBUG_HIGH_ON) THEN
2633 AMS_Utility_PVT.Debug_Message('Error');
2634 END IF;*/
2635 Write_log(L_API_NAME, 'Error');
2636 /*
2637 FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
2638 -- AMS_Utility_PVT.Debug_Message(FND_MSG_PUB.get(i, FND_API.g_false));
2639 END LOOP;
2640 */
2641 RAISE FND_API.g_exc_error;
2642 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2643 /*
2644 IF (AMS_DEBUG_HIGH_ON) THEN
2645 AMS_Utility_PVT.Debug_Message('Unexp Error');
2646 END IF;
2647 */
2648 Write_log(L_API_NAME, 'Unexp Error');
2649 /*FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
2650 AMS_Utility_PVT.Debug_Message(FND_MSG_PUB.get(i, FND_API.g_false));
2651 END LOOP; */
2652 RAISE FND_API.g_exc_unexpected_error;
2653 end if;
2654 /*
2655 IF (AMS_DEBUG_HIGH_ON) THEN
2656 AMS_Utility_PVT.Debug_Message('After create_Customer error handling');
2657 AMS_Utility_PVT.Debug_Message('Return party id: '||l_party_id);
2658 END IF;
2659 */
2660 Write_log(L_API_NAME, 'After create_Customer error handling');
2661 Write_log(L_API_NAME, 'Return party id: '||l_party_id);
2662
2663 if l_party_id is NULL then
2664 x_return_status := FND_API.g_ret_sts_error;
2665 RAISE FND_API.g_exc_error;
2666 end if;
2667
2668
2669
2670 -- set return values
2671 IF l_b2b_flag = 'Y'
2672 THEN
2673 OPEN c_relationship_det(l_party_id);
2674 FETCH c_relationship_det INTO x_new_org_party_id, x_new_party_id;
2675 CLOSE c_relationship_det;
2676
2677 x_new_party_id := l_party_id;
2678 ELSE -- b2C
2679 x_new_party_id := l_party_id;
2680 x_new_org_party_id := l_party_id;
2681 END IF;
2682
2683 /* else
2684 AMS_Utility_PVT.Debug_Message('Party Exist');
2685 end if;
2686 ELSE
2687 Write_log(L_API_NAME, 'Party Exist');
2688 END IF;*/
2689
2690 EXCEPTION
2691 WHEN FND_API.g_exc_error THEN
2692 rollback to Register_get_party_id_PVT;
2693 x_return_status := FND_API.g_ret_sts_error;
2694 FND_MSG_PUB.count_and_get(
2695 p_encoded => FND_API.g_false,
2696 p_count => x_msg_count,
2697 p_data => x_msg_data
2698 );
2699
2700 WHEN FND_API.g_exc_unexpected_error THEN
2701 rollback to Register_get_party_id_PVT;
2702 x_return_status := FND_API.g_ret_sts_unexp_error ;
2703 FND_MSG_PUB.count_and_get(
2704 p_encoded => FND_API.g_false,
2705 p_count => x_msg_count,
2706 p_data => x_msg_data
2707 );
2708
2709 WHEN OTHERS THEN
2710 rollback to Register_get_party_id_PVT;
2711 x_return_status := FND_API.g_ret_sts_unexp_error ;
2712 FND_MSG_PUB.count_and_get(
2713 p_encoded => FND_API.g_false,
2714 p_count => x_msg_count,
2715 p_data => x_msg_data
2716 );
2717
2718
2719 END get_party_id;
2720
2721 --=================================================================================
2722 --Function
2723 -- Get_Event_Det
2724 --
2725 --Purpose
2726 -- Function will return the Event id for the source code passed.
2727 --
2728 -- History
2729 -- 24-Feb-2002 ptendulk Created
2730 -- 31-jan-2003 soagrawa Fixed P1 bug# 2779298 - canot register for CSCH of type events
2731 --=================================================================================
2732 FUNCTION Get_Event_Det(p_source_code IN VARCHAR2)
2733 RETURN NUMBER
2734 IS
2735 l_event_id NUMBER ;
2736
2737 CURSOR c_event_det IS
2738 SELECT event_offer_id
2739 FROM ams_event_offers_all_b
2740 WHERE source_code = p_source_code ;
2741
2742 -- soagrawa bug# 2779298 31-jan-2003
2743 CURSOR c_csch_event_det IS
2744 SELECT related_event_id
2745 FROM ams_campaign_schedules_b
2746 WHERE source_code = p_source_code ;
2747
2748 BEGIN
2749 /*
2750 IF (AMS_DEBUG_HIGH_ON) THEN
2751
2752 AMS_Utility_PVT.Debug_Message('Start Get Event Det ');
2753 END IF;
2754 */
2755 Write_log('Get_Event_Det', 'Start Get Event Det ');
2756
2757 OPEN c_event_det ;
2758 FETCH c_event_det INTO l_event_id ;
2759 IF c_event_det%NOTFOUND THEN
2760
2761 -- soagrawa bug# 2779298 31-jan-2003
2762 OPEN c_csch_event_det ;
2763 FETCH c_csch_event_det INTO l_event_id ;
2764 IF c_csch_event_det%NOTFOUND THEN
2765 AMS_Utility_PVT.Error_Message('AMS_INVALID_EVENT');
2766 CLOSE c_csch_event_det ;
2767 CLOSE c_event_det ;
2768 RAISE FND_API.g_exc_error;
2769 END IF ;
2770 CLOSE c_csch_event_det ;
2771 END IF ;
2772 CLOSE c_event_det;
2773
2774 RETURN l_event_id ;
2775 END Get_Event_Det ;
2776
2777 END AMS_Registrants_PVT;