1 PACKAGE BODY AMS_List_Import_PUB AS
2 /* $Header: amspimlb.pls 120.7.12010000.3 2008/12/02 10:57:58 hbandi ship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_List_Import_PUB
7 --
8 -- PURPOSE
9 -- This purpose of this program is to create organization,person
10 -- ,party relationship, org contacts, locations , party sites,
11 -- email and phone records for B2B or B2C type customer's
12 --
13 --
14 -- For B2B creates the following using TCA API's
15 --
16 -- 1. Create organization
17 -- 2. Create Person
18 -- 3. Create Party Relation
19 -- 4. Create Party for Party Relationship
20 -- 5. Create Org contact
21 -- 6. Create Location (if address is available)
22 -- 7. Create Party Site (if address is available)
23 -- 8. Create Contact Points (if contact points are available)
24 --
25 --
26 --
27 --
28 -- For B2C creates the following using TCA API's
29 --
30 -- 1. Create Person
31 -- 2. Create Location (if address is available)
32 -- 3. Create Party Site (if address is available)
33 -- 4. Create Contact Points (if contact points are available)
34 -- ------------------------------------------------------------------------
35 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
36 g_pkg_name CONSTANT VARCHAR2(30):='AMS_LIST_IMPORT_PUB';
37 G_ERROR_THRESHOLD NUMBER := 0;
38 --
39 -- This procedure is used for existence checking for party.
40 --
41 --
42 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
43 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
44 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
45
46
47 PROCEDURE party_echeck(
48 p_party_id IN OUT NOCOPY NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_org_name IN VARCHAR2,
53 p_per_first_name IN VARCHAR2,
54 p_per_last_name IN VARCHAR2,
55 p_address1 IN VARCHAR2,
56 p_country IN VARCHAR2,
57 -- sranka 1/14/2003
58 -- added "p_orig_system_reference" for supporting the population of "orig_system_reference"
59 -- from CSV file
60 p_orig_system_reference IN VARCHAR2
61 );
62
63 --
64 -- This procedure is used for existence checking for party or person type.
65 --
66 --
67 PROCEDURE person_party_echeck(
68 p_party_id IN OUT NOCOPY NUMBER,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2,
72 p_per_first_name IN VARCHAR2,
73 p_per_last_name IN VARCHAR2,
74 p_address1 IN VARCHAR2,
75 p_country IN VARCHAR2,
76 p_email_address IN VARCHAR2,
77 p_ph_country_code IN VARCHAR2,
78 p_ph_area_code IN VARCHAR2,
79 p_ph_number IN VARCHAR2,
80 p_orig_system_reference IN VARCHAR2
81 ) ;
82
83 -- SOLIN, bug 4465931
84 PROCEDURE contact_person_party_echeck(
85 p_party_id IN OUT NOCOPY NUMBER,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2,
89 p_per_first_name IN VARCHAR2,
90 p_per_last_name IN VARCHAR2,
91 p_address1 IN VARCHAR2,
92 p_country IN VARCHAR2,
93 p_email_address IN VARCHAR2,
94 p_ph_country_code IN VARCHAR2,
95 p_ph_area_code IN VARCHAR2,
96 p_ph_number IN VARCHAR2,
97 p_orig_system_reference IN VARCHAR2
98 ) ;
99 -- SOLIN, end bug 4465931
100
101 PROCEDURE rented_person_party_echeck(
102 p_party_id IN OUT NOCOPY NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_per_first_name IN VARCHAR2,
107 p_per_last_name IN VARCHAR2,
108 p_address1 IN VARCHAR2,
109 p_country IN VARCHAR2,
110 p_email_address IN VARCHAR2,
111 p_ph_country_code IN VARCHAR2,
112 p_ph_area_code IN VARCHAR2,
113 p_ph_number IN VARCHAR2
114 ) ;
115
116
117 --
118 -- This procedure is used for existence checking for contact.
119 --
120 --
121 PROCEDURE contact_echeck(
122 p_party_id IN OUT NOCOPY NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2,
126 p_org_party_id IN NUMBER,
127 p_per_first_name IN VARCHAR2,
128 p_per_last_name IN VARCHAR2,
129 p_phone_area_code IN VARCHAR2,
130 p_phone_number IN VARCHAR2,
131 p_phone_extension IN VARCHAR2,
132 p_email_address IN VARCHAR2,
133 -- sranka 1/14/2003
134 -- added "p_orig_system_reference" for supporting the population of "orig_system_reference"
135 -- from CSV file
136 p_orig_system_reference IN VARCHAR2,
137 -- sranka 3/21/2003
138 -- made changes for supporting EMPLOYEE_OF" relationship
139 p_relationship_code IN VARCHAR2,
140 p_relationship_type IN VARCHAR2
141
142 );
143
144 PROCEDURE rented_contact_echeck(
145 p_party_id IN OUT NOCOPY NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 p_org_party_id IN NUMBER,
150 p_per_first_name IN VARCHAR2,
151 p_per_last_name IN VARCHAR2,
152 p_phone_area_code IN VARCHAR2,
153 p_phone_number IN VARCHAR2,
154 p_phone_extension IN VARCHAR2,
155 p_email_address IN VARCHAR2,
156 -- sranka 3/21/2003
157 -- made changes for supporting EMPLOYEE_OF" relationship
158 p_relationship_code IN VARCHAR2,
159 p_relationship_type IN VARCHAR2
160 );
161
162 -- This procedure is used to create party records in ams_party_sources table.
163 --
164 --
165 PROCEDURE create_party_source (
166 p_import_list_header_id IN NUMBER,
167 p_import_source_line_id IN NUMBER,
168 p_overlay IN VARCHAR2
169 );
170
171 -- ------------------------------------------------------------------------
172 -- PROCEDURE
173 -- Create_Customer
174 --
175 -- PURPOSE
176 -- Creates a new customer with other entities as mentioned above.
177 --
178 -- PARAMETERS
179 -- p_party_rec The New Record for party.
180 -- p_org_rec The New Record for organization.
181 -- p_person_rec The New Record for person.
182 -- p_location_rec The New Record for location.
183 -- p_psite_rec The New Record for party site.
184 -- p_cpoint_rec The New Record for contact point.
185 -- p_email_rec The New Record for email.
186 -- p_phone_rec The New Record for phone.
187 -- p_ocon_rec The New Record for org contact.
188 -- x_party_id The party_id for the record.
189 -- x_new_party The tells if the party is new.
190
191
192 PROCEDURE Create_Customer
193 ( p_api_version IN NUMBER,
194 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
195 p_commit IN VARCHAR2 := FND_API.G_FALSE,
196 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2,
200 p_party_id IN OUT NOCOPY NUMBER,
201 p_b2b_flag IN VARCHAR2,
202 p_import_list_header_id IN NUMBER,
203 p_party_rec IN hz_party_v2pub.party_rec_type,
204 p_org_rec IN hz_party_v2pub.organization_rec_type,
205 p_person_rec IN hz_party_v2pub.person_rec_type,
206 p_location_rec IN hz_location_v2pub.location_rec_type,
207 p_psite_rec IN hz_party_site_v2pub.party_site_rec_type,
208 p_cpoint_rec IN hz_contact_point_v2pub.contact_point_rec_type,
209 p_email_rec IN hz_contact_point_v2pub.email_rec_type,
210 p_phone_rec IN hz_contact_point_v2pub.phone_rec_type,
211 p_fax_rec IN hz_contact_point_v2pub.phone_rec_type,
212 p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type,
213 p_siteuse_rec IN hz_party_site_v2pub.party_site_use_rec_type,
214 p_web_rec IN hz_contact_point_v2pub.web_rec_type,
215 x_new_party OUT NOCOPY VARCHAR2,
216 p_component_name OUT NOCOPY VARCHAR2,
217 l_import_source_line_id IN NUMBER default null,
218 p_org_email_rec IN hz_contact_point_v2pub.email_rec_type default null,
219 p_org_phone_rec IN hz_contact_point_v2pub.phone_rec_type default null,
220 p_org_location_rec IN hz_location_v2pub.location_rec_type default null,
221 p_org_psite_rec IN hz_party_site_v2pub.party_site_rec_type default NULL,
222 p_language_rec IN hz_person_info_v2pub.person_language_rec_type DEFAULT null,
223 p_org_party_site_phone_rec IN hz_contact_point_v2pub.phone_rec_type default null
224 ) IS
225
226 l_api_name CONSTANT VARCHAR2(30) := 'Create_Customer';
227 l_api_version CONSTANT NUMBER := 1.0;
228 l_return_status VARCHAR2(1);
229 l_ret_status varchar(1);
230 l_rec_update_flag varchar(1) := 'N';
231
232 party_rec hz_party_v2pub.party_rec_type := p_party_rec;
233 org_rec hz_party_v2pub.organization_rec_type := p_org_rec;
234 org_rec_null hz_party_v2pub.organization_rec_type := null;
235 person_rec hz_party_v2pub.person_rec_type := p_person_rec;
236 location_rec hz_location_v2pub.location_rec_type := p_location_rec;
237 psite_rec hz_party_site_v2pub.party_site_rec_type := p_psite_rec;
238 psiteuse_rec hz_party_site_v2pub.party_site_use_rec_type := p_siteuse_rec;
239 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type := p_cpoint_rec;
240 email_rec hz_contact_point_v2pub.email_rec_type := p_email_rec;
241 phone_rec hz_contact_point_v2pub.phone_rec_type := p_phone_rec;
242 fax_rec hz_contact_point_v2pub.phone_rec_type := p_fax_rec;
243 ocon_rec hz_party_contact_v2pub.org_contact_rec_type := p_ocon_rec;
244 edi_rec hz_contact_point_v2pub.edi_rec_type;
245 telex_rec hz_contact_point_v2pub.telex_rec_type;
246 web_rec hz_contact_point_v2pub.web_rec_type := p_web_rec;
247
248 org_email_rec hz_contact_point_v2pub.email_rec_type := p_org_email_rec;
249 org_phone_rec hz_contact_point_v2pub.phone_rec_type := p_org_phone_rec;
250 org_location_rec hz_location_v2pub.location_rec_type := p_org_location_rec;
251 org_psite_rec hz_party_site_v2pub.party_site_rec_type := p_org_psite_rec;
252 language_rec hz_person_info_v2pub.person_language_rec_type := p_language_rec;
253 org_party_site_phone_rec hz_contact_point_v2pub.phone_rec_type := p_org_party_site_phone_rec;
254
255 l_address_key hz_locations.address_key%TYPE ;
256 l_address_key_count NUMBER ;
257
258 x_b2b varchar(1);
259 x_rented_list_flag varchar(1) := 0;
260 x_generate_party_number VARCHAR2(1);
261 x_gen_contact_number VARCHAR2(1);
262 x_gen_party_site_number VARCHAR2(1);
263 x_party_number VARCHAR2(30);
264 x_organization_profile_id number;
265 x_person_profile_id number;
266 x_org_party_id number;
267 x_tmp_var VARCHAR2(4000);
268 x_tmp_var1 VARCHAR2(4000);
269 x_per_party_id number;
270 x_party_relationship_id number;
271 x_contact_number VARCHAR2(30);
272 x_org_contact_id number;
273 x_party_rel_party_id number;
274 x_location_id number;
275 x_Party_site_id number;
276 x_party_site_number VARCHAR2(30);
277 x_contact_point_id number;
278 x_email_address varchar2(2000);
279 x_phone_country_code VARCHAR2(10);
280 x_phone_area_code VARCHAR2(10);
281 x_phone_number VARCHAR2(40);
282 x_phone_extention VARCHAR2(20);
283 x_party_name VARCHAR2(400);
284 l_return_status VARCHAR2(1);
285 i_import_source_line_id number;
286 i_number_of_rows_processed number := 0;
287 i_party_id number;
288 p_msg_count number;
289 -- p_party_id number;
290 p_msg_data varchar(2000);
291 P_DUPLICATE varchar(1);
292 L_COUNT NUMBER := 0;
293 l_max_party_id NUMBER := 0;
294 l_max_location_id NUMBER := 0;
295 p_pr_party_id number;
296 l_lp_psite_id number;
297
298 x_hz_dup_check VARCHAR2(60);
299 l_overlay VARCHAR2(1);
300 l_phone_exists VARCHAR2(1);
301 l_url_exists VARCHAR2(1);
302 l_fax_exists VARCHAR2(1);
303 l_email_exists VARCHAR2(1);
304 l_is_party_mapped VARCHAR2(1);
305 l_b2b_party_id number;
306 l_b2c_party_id number;
307 l_b2b_party_exists VARCHAR2(1);
308 l_b2c_party_exists VARCHAR2(1);
309 l_enabled_flag VARCHAR2(1);
310 l_phone_id number;
311 l_fax_id number;
312 l_xml_element_id number;
313 l_object_version1 number;
314 l_object_version2 number;
315 l_object_version3 number;
316 x_pty_site_id number;
317 x_party_site_use_id number;
318 x_fax_country_code VARCHAR2(10);
319 x_fax_area_code VARCHAR2(10);
320 x_fax_number VARCHAR2(40);
321 x_url VARCHAR2(2000);
322 L_URL_ID number;
323 l_party_obj_number number;
324 l_con_obj_number number;
325 l_pr_obj_number number;
326 l_loc_obj_number number;
327 l_ps_obj_number number;
328 l_cp_obj_number number;
329 l_email_id number;
330 l_rel_id number;
331
332 src_ORG_PARTY_ID number;
333 src_OCONT_PARTY_ID number;
334 src_PARTY_LOCATION_ID number;
335 src_org_LOCATION_ID number;
336 src_ORG_KEY varchar2(240);
337 src_person_PARTY_ID number;
338 l_transposed_phone_no varchar2(60);
339 x_phone_type VARCHAR2(30);
340 -- sranka Modified for COLT enhancememts
341
342 x_org_location_id number;
343 X_LANGUAGE_USE_REFERENCE_ID number;
344 L_LANGUAGE_OBJ_NUMBER number;
345 L_LANGUAGE_USE_REFERENCE_ID number;
346 x_org_party_site_id number;
347 l_org_lp_psite_id number;
348 x_org_email_address varchar2(2000);
349 l_org_transposed_phone_no varchar2(60);
350 l_org_ps_transposed_phone_no varchar2(60);
351
352
353
354 cursor c_rented is
355 select rented_list_flag, nvl(RECORD_UPDATE_FLAG,'N') from ams_imp_list_headers_all
356 where import_list_header_id = p_import_list_header_id;
357
358
359 cursor b2bxml is
360 select ORG_IMP_XML_ELEMENT_ID from ams_hz_b2b_mapping_v
361 where import_source_line_id = i_import_source_line_id;
362
363 cursor b2cxml is
364 select PER_IMP_XML_ELEMENT_ID from ams_hz_b2c_mapping_v
365 where import_source_line_id = i_import_source_line_id;
366
367 CURSOR PARTY_REL_EXISTS IS
368 SELECT party_id, relationship_id FROM hz_relationships
369 WHERE object_id = x_org_party_id
370 AND subject_id = x_per_party_id
371 AND subject_table_name = 'HZ_PARTIES'
372 AND subject_type = 'PERSON'
373 AND object_type = 'ORGANIZATION'
374 AND object_table_name = 'HZ_PARTIES'
375 -- sranka 3/4/2003
376 -- made changes for supporting EMPLOYEE_OF" relationship
377 -- AND relationship_code = 'CONTACT_OF';
378 AND relationship_code = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
379 AND relationship_type = NVL(ocon_rec.party_rel_rec.relationship_type,'CONTACT');
380
381 cursor b2bparty is
382 SELECT 'Y' FROM hz_parties
383 WHERE party_type = 'PARTY_RELATIONSHIP'
384 and status = 'A'
385 AND party_id = l_b2b_party_id;
386
387
388 cursor b2cparty is
389 SELECT 'Y' FROM hz_parties
390 WHERE party_type = 'PERSON'
391 and status = 'A'
392 AND party_id = l_b2c_party_id;
393
394 cursor orgpartyid is
395 SELECT object_id FROM hz_relationships
396 WHERE subject_type = 'PERSON'
397 AND subject_table_name = 'HZ_PARTIES'
398 AND object_type = 'ORGANIZATION'
399 AND object_table_name = 'HZ_PARTIES'
400 -- sranka 3/4/2003
401 -- made changes for supporting EMPLOYEE_OF" relationship
402 -- AND relationship_code = 'CONTACT_OF';
403 AND relationship_code = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
404 AND relationship_type = NVL(ocon_rec.party_rel_rec.relationship_type,'CONTACT')
405 AND party_id = l_b2b_party_id;
406
407 CURSOR LOCATION_EXISTS IS
408 SELECT party_site_id FROM hz_party_sites
409 WHERE party_id = x_org_party_id
410 AND location_id = x_location_id;
411
412 -- sranka 7/15/2003 made changes for COLt Enhancements
413 CURSOR ORG_LOCATION_EXISTS IS
414 SELECT party_site_id FROM hz_party_sites
415 WHERE party_id = x_org_party_id
416 AND location_id = x_org_location_id;
417
418 CURSOR PER_LOCATION_EXISTS IS
419 SELECT party_site_id FROM hz_party_sites
420 WHERE party_id = x_per_party_id
421 AND location_id = x_location_id;
422
423 CURSOR CHECK_PSITE_EXISTS IS
424 SELECT party_site_id FROM hz_party_sites
425 WHERE party_id = x_party_rel_party_id
426 AND location_id = x_location_id;
427
428
429 CURSOR phone_exists (x_hz_party_id number,x_phone_type varchar) IS
430 SELECT 'Y' FROM hz_contact_points
431 WHERE contact_point_type = 'PHONE'
432 AND phone_line_type = x_phone_type
433 AND owner_table_name = 'HZ_PARTIES'
434 AND owner_table_id = x_hz_party_id
435 and transposed_phone_number = l_transposed_phone_no;
436 -- AND phone_number = x_phone_number
437 -- AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
438 -- AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')
439 -- AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
440
441 -- srank a 7/31/2003 modified for COLT enhancements
442
443 CURSOR org_phone_exists (x_hz_party_id number,x_org_phone_type varchar) IS
444 SELECT 'Y' FROM hz_contact_points
445 WHERE contact_point_type = 'PHONE'
446 AND phone_line_type = x_org_phone_type
447 AND owner_table_name = 'HZ_PARTIES'
448 AND owner_table_id = x_hz_party_id
449 and transposed_phone_number = l_org_transposed_phone_no;
450
451
452 CURSOR org_party_site_phone_exists (x_hz_party_id number,x_org_ps_phone_type varchar) IS
453 SELECT 'Y' FROM hz_contact_points
454 WHERE contact_point_type = 'PHONE'
455 AND phone_line_type = x_org_ps_phone_type
456 AND owner_table_name = 'HZ_PARTIES'
457 AND owner_table_id = x_hz_party_id
458 and transposed_phone_number = l_org_ps_transposed_phone_no;
459 -- AND phone_number = x_phone_number
460 -- AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
461 -- AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')
462 -- AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
463
464
465 CURSOR fax_exists (x_hz_party_id number) IS
466 SELECT 'Y' FROM hz_contact_points
467 WHERE contact_point_type = 'PHONE'
468 AND phone_line_type = 'FAX'
469 AND owner_table_name = 'HZ_PARTIES'
470 AND owner_table_id = x_hz_party_id
471 AND phone_number = x_fax_number
472 AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
473 AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
474
475 CURSOR url_exists (x_hz_party_id number) IS
476 SELECT 'Y' FROM hz_contact_points
477 WHERE contact_point_type = 'WEB'
478 AND owner_table_name = 'HZ_PARTIES'
479 AND owner_table_id = x_hz_party_id
480 AND url = x_url;
481
482
483 CURSOR c_phone_id (x_hz_party_id number,x_phone_type varchar) IS
484 SELECT contact_point_id FROM hz_contact_points
485 WHERE contact_point_type = 'PHONE'
486 AND phone_line_type = x_phone_type
487 AND owner_table_name = 'HZ_PARTIES'
488 AND owner_table_id = x_hz_party_id
489 and transposed_phone_number = l_transposed_phone_no;
490 /*
491 AND phone_number = x_phone_number
492 AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
493 AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')
494 AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
495 */
496
497 -- sranka modified for colt enhancements 7/23/2003
498
499 CURSOR c_org_phone_id (x_hz_party_id number,x_org_phone_type varchar) IS
500 SELECT contact_point_id FROM hz_contact_points
501 WHERE contact_point_type = 'PHONE'
502 AND phone_line_type = x_org_phone_type
503 AND owner_table_name = 'HZ_PARTIES'
504 AND owner_table_id = x_hz_party_id
505 and transposed_phone_number = l_org_transposed_phone_no;
506
507 CURSOR c_org_party_site_phone_id (x_hz_party_id number,x_org_ps_phone_type varchar) IS
508 SELECT contact_point_id FROM hz_contact_points
509 WHERE contact_point_type = 'PHONE'
510 AND phone_line_type = x_org_ps_phone_type
511 AND owner_table_name = 'HZ_PARTIES'
512 AND owner_table_id = x_hz_party_id
513 and transposed_phone_number = l_org_ps_transposed_phone_no;
514
515
516
517 CURSOR c_url_id (x_hz_party_id number) IS
518 SELECT contact_point_id FROM hz_contact_points
519 WHERE contact_point_type = 'WEB'
520 AND owner_table_name = 'HZ_PARTIES'
521 AND owner_table_id = x_hz_party_id
522 AND url = x_url;
523
524 CURSOR c_fax_id (x_hz_party_id number) IS
525 SELECT contact_point_id FROM hz_contact_points
526 WHERE contact_point_type = 'PHONE'
527 AND phone_line_type = 'FAX'
528 AND owner_table_name = 'HZ_PARTIES'
529 AND owner_table_id = x_hz_party_id
530 AND phone_number = x_fax_number
531 AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
532 AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
533
534 CURSOR email_exists (x_hz_party_id number) IS
535 SELECT 'Y' FROM hz_contact_points
536 WHERE contact_point_type = 'EMAIL'
537 AND owner_table_name = 'HZ_PARTIES'
538 AND owner_table_id = x_hz_party_id
539 AND upper(email_address) = upper(x_email_address);
540
541
542 -- srank COLT Enhancements 7/19/2003
543
544 CURSOR org_email_exists (x_hz_party_id number) IS
545 SELECT 'Y' FROM hz_contact_points
546 WHERE contact_point_type = 'EMAIL'
547 AND owner_table_name = 'HZ_PARTIES'
548 AND owner_table_id = x_hz_party_id
549 AND upper(email_address) = upper(x_org_email_address);
550
551
552
553
554 cursor c_relationship is
555 SELECT OBJECT_VERSION_NUMBER FROM hz_relationships
556 WHERE RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id)
557 and subject_type = 'PERSON';
558
559 cursor c_org_cont_rel is
560 SELECT OBJECT_VERSION_NUMBER FROM hz_org_contacts
561 WHERE PARTY_RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id);
562
563 cursor c_party_rel is
564 SELECT OBJECT_VERSION_NUMBER FROM hz_parties
565 WHERE PARTY_ID = x_party_rel_party_id;
566
567 CURSOR c_email_id (x_hz_party_id number) IS
568 SELECT contact_point_id FROM hz_contact_points
569 WHERE contact_point_type = 'EMAIL'
570 AND owner_table_name = 'HZ_PARTIES'
571 AND owner_table_id = x_hz_party_id
572 AND email_address = x_email_address;
573
574 cursor c_b2b_source_rec is
575 select ORG_PARTY_ID,OCONT_PARTY_ID,PARTY_LOCATION_ID,ORG_KEY,org_location_id
576 from ams_hz_b2b_mapping_v
577 where import_source_line_id = i_import_source_line_id;
578
579 cursor c_b2c_source_rec is
580 select person_PARTY_ID,PARTY_LOCATION_ID
581 from ams_hz_b2c_mapping_v
582 where import_source_line_id = i_import_source_line_id;
583
584 cursor c_org_party is
585 select party_id from hz_parties
586 where customer_key = src_org_key
587 and status = 'A'
588 and party_type = 'ORGANIZATION';
589
590 cursor c_validate_b2b is
591 select 1 from hz_parties
592 where party_id = p_party_id
593 and party_type in ('PARTY_RELATIONSHIP','ORGANIZATION')
594 and status = 'A';
595
596 cursor c_validate_b2c is
597 select 1 from hz_parties
598 where party_id = p_party_id -- bug 5100612 mayjain
599 and party_type in ('PERSON')
600 and status = 'A';
601
602 l_validate number;
603
604 begin
605
606 SAVEPOINT create_customer_pub;
607 --Initialize message list if p_init_msg_list is set to TRUE.
608 IF FND_API.to_boolean(p_init_msg_list) THEN
609 FND_MSG_PUB.initialize;
610 END IF;
611 --Initialize API return status to success.
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613 x_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
614 x_gen_contact_number := fnd_profile.value('HZ_GENERATE_CONTACT_NUMBER');
615 x_gen_party_site_number := fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER');
616 x_hz_dup_check := fnd_profile.value('AMS_HZ_DEDUPE_RULE');
617 l_transposed_phone_no := hz_phone_number_pkg.transpose(phone_rec.phone_country_code
618 ||phone_rec.phone_area_code||phone_rec.phone_number);
619
620 x_email_address := email_rec.email_address;
621
622 -- sranka COLT Enahancement ##########
623 x_org_email_address := org_email_rec.email_address;
624
625
626
627 l_org_transposed_phone_no := hz_phone_number_pkg.transpose(org_phone_rec.phone_country_code
628 ||org_phone_rec.phone_area_code||org_phone_rec.phone_number);
629
630 l_org_ps_transposed_phone_no := hz_phone_number_pkg.transpose(org_party_site_phone_rec.phone_country_code
631 ||org_party_site_phone_rec.phone_area_code||org_party_site_phone_rec.phone_number);
632
633
634
635
636 x_phone_country_code := phone_rec.phone_country_code;
637 x_phone_area_code := phone_rec.phone_area_code;
638 x_phone_number := phone_rec.phone_number;
639 x_phone_extention := phone_rec.phone_extension;
640 x_phone_type := phone_rec.phone_line_type;
641
642 x_url := web_rec.url;
643 x_fax_country_code := fax_rec.phone_country_code;
644 x_fax_area_code := fax_rec.phone_area_code;
645 x_fax_number := fax_rec.phone_number;
646
647 email_rec.email_address := null;
648 phone_rec.phone_country_code := null;
649 phone_rec.phone_area_code := null;
650 phone_rec.phone_number := null;
651 phone_rec.phone_extension := null;
652 phone_rec.phone_line_type := null;
653
654 web_rec.url := null;
655 fax_rec.phone_country_code := null;
656 fax_rec.phone_area_code := null;
657 fax_rec.phone_number := null;
658
659
660
661
662 if x_hz_dup_check <> 'Y' then
663 x_hz_dup_check := 'N';
664 end if;
665
666 x_b2b := p_b2b_flag;
667 if p_import_list_header_id is not null then
668 -- Checks if party_id is mapped
669 l_is_party_mapped := AMS_ListImport_PVT.G_PARTY_MAPPED;
670 OPEN c_rented;
671 FETCH c_rented into x_rented_list_flag, l_rec_update_flag;
672 CLOSE c_rented;
673 if x_rented_list_flag is null then
674 x_rented_list_flag := 'X';
675 end if;
676 end if;
677
678
679
680
681 if x_b2b = 'Y' then
682 -- Creates Organization
683
684 -- sranka 1/14/2003
685 -- i_import_source_line_id := org_rec.party_rec.orig_system_reference; // original
686 i_import_source_line_id := l_import_source_line_id;
687
688 x_party_name := org_rec.organization_name;
689 x_org_party_id := null;
690 x_return_status := null;
691 x_msg_count := null;
692 x_msg_data := null;
693 l_b2b_party_id := p_party_id;
694
695 if l_is_party_mapped is NULL then
696 -- The following three are not created if party is mapped.
697 -- organization
698 -- person
699 -- org contact
700 if x_hz_dup_check = 'Y' then
701 if l_import_source_line_id is NULL then
702 l_overlay := null;
703 -- Use unencrypted name to find party in TCA table
704 party_echeck(
705 p_party_id => x_org_party_id,
706 x_return_status => x_return_status,
707 x_msg_count => x_msg_count,
708 x_msg_data => x_msg_data,
709 p_org_name => x_party_name,
710 p_per_first_name => NULL,
711 p_per_last_name => NULL,
712 p_address1 => location_rec.address1,
713 p_country => location_rec.country,
714 p_orig_system_reference => org_rec.party_rec.orig_system_reference
715 );
716 -- If Unencrypted party name doesn't exist in TCA and search by encrpted party name
717 if x_rented_list_flag = 'R' and x_org_party_id is NULL then
718 -- Encrypt party name
719 x_party_name := AMS_Import_Security_PVT.Get_DeEncrypt_String (
720 p_input_string => org_rec.organization_name,
721 p_header_id => null,
722 p_encrypt_flag => TRUE);
723
724 -- Search by encrpted party name
725 party_echeck(
726 p_party_id => x_org_party_id,
727 x_return_status => x_return_status,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data,
730 p_org_name => x_party_name,
731 p_per_first_name => NULL,
732 p_per_last_name => NULL,
733 p_address1 => location_rec.address1,
734 p_country => location_rec.country,
735 p_orig_system_reference => org_rec.party_rec.orig_system_reference
736 );
737 end if;
738 else
739 open c_b2b_source_rec;
740 fetch c_b2b_source_rec into src_ORG_PARTY_ID,src_OCONT_PARTY_ID,src_PARTY_LOCATION_ID,src_ORG_KEY,
741 src_org_LOCATION_ID;
742 close c_b2b_source_rec;
743
744 if p_import_list_header_id is NOT NULL then
745 AMS_Utility_PVT.Create_Log (
746 x_return_status => x_return_status,
747 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
748 p_log_used_by_id => p_import_list_header_id,
749 p_msg_data => 'mayjain src_ORG_PARTY_ID=' || src_ORG_PARTY_ID || ' : src_OCONT_PARTY_ID = ' || src_OCONT_PARTY_ID || ' : src_ORG_KEY = ' || src_ORG_KEY ,
750 p_msg_type => 'DEBUG');
751 end if;
752
753 x_org_party_id := src_ORG_PARTY_ID;
754 x_per_party_id := src_OCONT_PARTY_ID;
755 x_location_id := src_PARTY_LOCATION_ID;
756 x_org_location_id := src_org_LOCATION_ID;
757 if src_ORG_PARTY_ID is null and src_ORG_KEY is not NULL then
758 open c_org_party;
759 fetch c_org_party into x_org_party_id;
760 close c_org_party;
761 end if;
762
763 if p_party_id is not null then
764 l_validate := null;
765
766 open c_validate_b2b;
767 fetch c_validate_b2b into l_validate;
768 close c_validate_b2b;
769
770 if l_validate is null then
771 --Throw error as party_id that was passed is incorrect
772 AMS_List_Import_PUB.error_capture (
773 1,
774 'T',
775 'F',
776 null,
777 x_return_status,
778 x_msg_count,
779 x_msg_data,
780 p_import_list_header_id,
781 i_import_source_line_id,
782 null,
783 null,
784 'CONTACT',
785 null,
786 'PARTY_ID : Party does not exists for the mapped party_id '||to_char(p_party_id));
787 x_return_status := 'E';
788 return;
789 end if;
790 end if;
791 end if;
792
793 if x_return_status <> 'S' then
794 p_component_name := 'ORGANIZATION';
795 RETURN;
796 end if;
797 if x_org_party_id is not null then
798 l_overlay := 'Y';
799 end if;
800 end if; -- if l_is_party_mapped is NULL then
801
802
803 if x_org_party_id is NULL then
804 /*
805 x_party_number := null;
806 if x_generate_party_number = 'N' then
807 select hz_party_number_s.nextval into x_party_number from dual;
808 end if;
809 select hz_parties_s.nextval into x_org_party_id from dual;
810
811 org_rec.party_rec.party_number := x_party_number;
812 org_rec.party_rec.party_id := x_org_party_id;
813 */
814 if x_rented_list_flag = 'R' then
815 org_rec := org_rec_null;
816 -- org_rec.party_rec.orig_system_reference := i_import_source_line_id;
817 org_rec.organization_name := x_party_name;
818 org_rec.party_rec.status := 'I';
819 org_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
820
821 end if;
822 --org_rec.CREATED_BY_MODULE := 'Oracle Marketing';
823 --R12 tca mandate: bug 4587049: all who calls create_customer should
824 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
825 /* --caller must populate this
826 IF org_rec.CREATED_BY_MODULE is null THEN
827 org_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
828 END IF;
829 */
830 org_rec.application_id := 530;
831 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_org_party_id is NULL)) then
832 x_party_number := null;
833 if x_generate_party_number = 'N' then
834 select hz_party_number_s.nextval into x_party_number from dual;
835 end if;
836 select hz_parties_s.nextval into x_org_party_id from dual;
837 org_rec.party_rec.party_number := x_party_number;
838 org_rec.party_rec.party_id := x_org_party_id;
839 hz_party_v2pub.create_organization(
840 'F',
841 org_rec,
842 x_return_status,
843 x_msg_count,
844 x_msg_data,
845 x_org_party_id,
846 x_party_number,
847 x_organization_profile_id
848 );
849 end if;
850 if x_return_status <> 'S' then
851 p_component_name := 'ORGANIZATION';
852 RETURN;
853 end if;
854 else
855 if l_rec_update_flag = 'Y' and x_org_party_id is not NULL then
856 select OBJECT_VERSION_NUMBER into l_party_obj_number
857 from hz_parties
858 where party_id = x_org_party_id;
859 org_rec.party_rec.party_id := x_org_party_id;
860
861
862 AMS_Utility_PVT.Create_Log (
863 x_return_status => x_return_status,
864 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
865 p_log_used_by_id => p_import_list_header_id,
866 p_msg_data => 'org_rec.CREATED_BY_MODULE : '||org_rec.CREATED_BY_MODULE,
867 p_msg_type => 'DEBUG');
868
869
870 -- Set the CREATED_BY_MODULE as null because it is updating the orgnaizatio details
871 org_rec.CREATED_BY_MODULE := null ;
872
873 org_rec.party_rec.orig_system_reference := NULL;
874 hz_party_v2pub.update_organization(
875 'F',
876 org_rec,
877 l_party_obj_number,
878 x_organization_profile_id,
879 x_return_status,
880 x_msg_count,
881 x_msg_data
882 );
883 end if;
884 if x_return_status <> 'S' then
885 RETURN;
886 end if;
887 end if; -- x_org_party_id is NULL
888
889 -- Creates Person
890 x_party_number := null;
891 x_return_status := null;
892 x_msg_count := null;
893 x_msg_data := null;
894
895 if x_hz_dup_check = 'Y' then
896 if x_per_party_id is null and l_import_source_line_id is null then
897 -- Use unencrypted first and last name to find party in TCA table
898 IF x_rented_list_flag <> 'R' THEN
899 contact_echeck(
900 p_party_id => x_per_party_id,
901 x_return_status => x_return_status,
902 x_msg_count => x_msg_count,
903 x_msg_data => x_msg_data,
904 p_org_party_id => x_org_party_id,
905 p_per_first_name => person_rec.person_first_name,
906 p_per_last_name => person_rec.person_last_name,
907 p_phone_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
908 p_phone_number => x_phone_number, -- phone_rec.phone_number,
909 p_phone_extension => x_phone_extention, -- phone_rec.phone_extension,
910 p_email_address => x_email_address, -- email_rec.email_address
911 p_orig_system_reference => org_rec.party_rec.orig_system_reference,
912 p_relationship_code => ocon_rec.party_rel_rec.relationship_code,
913 p_relationship_type => ocon_rec.party_rel_rec.relationship_type
914
915 );
916 ELSE
917 rented_contact_echeck(
918 p_party_id => x_per_party_id,
919 x_return_status => x_return_status,
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data,
922 p_org_party_id => x_org_party_id,
923 p_per_first_name => person_rec.person_first_name,
924 p_per_last_name => person_rec.person_last_name,
925 p_phone_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
926 p_phone_number => x_phone_number, -- phone_rec.phone_number,
927 p_phone_extension => x_phone_extention, -- phone_rec.phone_extension,
928 p_email_address => x_email_address, -- email_rec.email_address
929 p_relationship_code => ocon_rec.party_rel_rec.relationship_code,
930 p_relationship_type => ocon_rec.party_rel_rec.relationship_type
931 );
932 END IF;
933 end if;
934 -- If Unencrypted party name doesn't exist in TCA and search by encrpted party name
935 -- SOLIN, bug 4224506
936 -- check first name and last name also
937 if x_rented_list_flag = 'R' and x_per_party_id is NULL AND
938 person_rec.person_first_name IS NOT NULL AND
939 person_rec.person_last_name IS NOT NULL
940 THEN
941 -- Encrypt name
942 person_rec.person_first_name := AMS_Import_Security_PVT.Get_DeEncrypt_String (
943 p_input_string => person_rec.person_first_name,
944 p_header_id => null,
945 p_encrypt_flag => TRUE);
946
947 person_rec.person_last_name := AMS_Import_Security_PVT.Get_DeEncrypt_String (
948 p_input_string => person_rec.person_last_name,
949 p_header_id => null,
950 p_encrypt_flag => TRUE);
951
952 -- Search by encrpted name
953
954 rented_contact_echeck(
955 p_party_id => x_per_party_id,
956 x_return_status => x_return_status,
957 x_msg_count => x_msg_count,
958 x_msg_data => x_msg_data,
959 p_org_party_id => x_org_party_id,
960 p_per_first_name => person_rec.person_first_name,
961 p_per_last_name => person_rec.person_last_name,
962 p_phone_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
963 p_phone_number => x_phone_number, -- phone_rec.phone_number,
964 p_phone_extension => x_phone_extention, -- phone_rec.phone_extension,
965 p_email_address => x_email_address, -- email_rec.email_address
966 -- sranka 3/21/2003
967 -- made changes for supporting EMPLOYEE_OF" relationship
968 p_relationship_code => ocon_rec.party_rel_rec.relationship_code,
969 p_relationship_type => ocon_rec.party_rel_rec.relationship_type
970 );
971 end if;
972 -- SOLIN, bug 4465931
973 -- If contact is not found, dedupe with existing person parties.
974
975 IF x_per_party_id IS NULL THEN
976 contact_person_party_echeck(
977 p_party_id => x_per_party_id,
978 x_return_status => x_return_status,
979 x_msg_count => x_msg_count,
980 x_msg_data => x_msg_data,
981 p_per_first_name => person_rec.person_first_name,
982 p_per_last_name => person_rec.person_last_name,
983 p_address1 => location_rec.address1,
984 p_country => location_rec.country,
985 p_email_address => x_email_address, -- email_rec.email_address,
986 p_ph_country_code => x_phone_country_code, -- phone_rec.phone_country_code,
987 p_ph_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
988 p_ph_number => x_phone_number, -- phone_rec.phone_number
989 p_orig_system_reference => org_rec.party_rec.orig_system_reference
990 );
991 -- ndadwal added if cond for bug 4966524
992 if p_import_list_header_id is NOT NULL then
993 AMS_Utility_PVT.Create_Log (
994 x_return_status => x_return_status,
995 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
996 p_log_used_by_id => p_import_list_header_id,
997 p_msg_data => 'SOLIN per_p_id=' || x_per_party_id,
998 p_msg_type => 'DEBUG');
999 end if;
1000
1001 END IF;
1002 -- SOLIN, end
1003 end if;
1004
1005 if x_return_status <> 'S' then
1006 p_component_name := 'CONTACT';
1007 RETURN;
1008 end if;
1009 if x_per_party_id is not null then
1010 x_new_party := 'Y';
1011 end if;
1012 if x_per_party_id is null then
1013 if person_rec.person_first_name is not null then
1014 /*
1015 if x_generate_party_number = 'N' then
1016 select hz_party_number_s.nextval into x_party_number from dual;
1017 end if;
1018 select hz_parties_s.nextval into x_per_party_id from dual;
1019
1020 person_rec.party_rec.party_number := x_party_number;
1021 person_rec.party_rec.party_id := x_per_party_id;
1022
1023 -- sranka 1/15/2003
1024 -- assigning the value for the "orig_system_reference" for proper population of data in TCA while importing
1025 person_rec.party_rec.orig_system_reference := org_rec.party_rec.orig_system_reference;
1026
1027 person_rec.CREATED_BY_MODULE := 'Oracle Marketing';
1028 person_rec.application_id := 530;
1029 */
1030 x_return_status := null;
1031 x_msg_count := 0;
1032 x_msg_data := null;
1033
1034 if x_rented_list_flag = 'R' then
1035 person_rec.party_rec.status := 'I';
1036 end if;
1037 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
1038 if x_generate_party_number = 'N' then
1039 select hz_party_number_s.nextval into x_party_number from dual;
1040 end if;
1041 select hz_parties_s.nextval into x_per_party_id from dual;
1042
1043 person_rec.party_rec.party_number := x_party_number;
1044 person_rec.party_rec.party_id := x_per_party_id;
1045
1046 --person_rec.CREATED_BY_MODULE := 'Oracle Marketing';
1047 --R12 tca mandate: bug 4587049: all who calls create_customer should
1048 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
1049 /* --caller must populate this
1050 IF person_rec.CREATED_BY_MODULE is null THEN
1051 person_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
1052 END IF;
1053 */
1054 person_rec.application_id := 530;
1055 person_rec.party_rec.orig_system_reference := org_rec.party_rec.orig_system_reference;
1056 hz_party_v2pub.create_person(
1057 'F',
1058 person_rec,
1059 x_per_party_id,
1060 x_party_number,
1061 x_person_profile_id,
1062 x_return_status,
1063 x_msg_count,
1064 x_msg_data
1065 );
1066 end if;
1067 if x_return_status <> 'S' then
1068 p_component_name := 'PERSON';
1069 Return;
1070 end if;
1071 else
1072 if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
1073 person_rec.party_rec.party_id := x_per_party_id;
1074 select OBJECT_VERSION_NUMBER into l_party_obj_number
1075 from hz_parties
1076 where party_id = x_per_party_id;
1077 person_rec.party_rec.orig_system_reference := null;
1078 hz_party_v2pub.update_person(
1079 'F',
1080 person_rec,
1081 l_party_obj_number,
1082 x_person_profile_id,
1083 x_return_status,
1084 x_msg_count,
1085 x_msg_data
1086 );
1087 end if;
1088 if x_return_status <> 'S' then
1089 RETURN;
1090 end if;
1091 end if;
1092 end if; -- x_per_party_id is NULL
1093
1094 -- Creates Org Contact ,party relationship and party for p rel
1095 x_party_number := null;
1096 x_contact_number := null;
1097 x_return_status := null;
1098 x_msg_count := null;
1099 x_msg_data := null;
1100
1101 if person_rec.person_first_name is not null then
1102 p_pr_party_id := null;
1103 open PARTY_REL_EXISTS;
1104 fetch PARTY_REL_EXISTS into p_pr_party_id,l_rel_id;
1105 close PARTY_REL_EXISTS;
1106 if p_pr_party_id is not null then
1107 x_party_rel_party_id := p_pr_party_id;
1108 end if;
1109 if p_pr_party_id is null then
1110 Select hz_org_contacts_s.nextval into x_org_contact_id from dual;
1111 if x_generate_party_number = 'N' then
1112 select hz_party_number_s.nextval into x_party_number from dual;
1113 end if;
1114
1115 ocon_rec.party_rel_rec.subject_id := x_per_party_id;
1116 ocon_rec.party_rel_rec.object_id := x_org_party_id;
1117 ocon_rec.org_contact_id := x_org_contact_id;
1118 ocon_rec.orig_system_reference := x_org_contact_id;
1119
1120 -- sranka 3/21/2003
1121 -- made changes for supporting EMPLOYEE_OF" relationship
1122
1123 --ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
1124 --ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
1125
1126 IF ocon_rec.party_rel_rec.relationship_type IS NULL THEN
1127 ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
1128 END IF;
1129 IF ocon_rec.party_rel_rec.relationship_code IS NULL THEN
1130 ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
1131 END IF;
1132
1133
1134 -- ocon_rec.party_rel_rec.directional_flag := 'Y';
1135 ocon_rec.party_rel_rec.start_date := sysdate;
1136
1137 --ocon_rec.CREATED_BY_MODULE := 'Oracle Marketing';
1138 --R12 tca mandate: bug 4587049: all who calls create_customer should
1139 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
1140 /* --caller must populate this
1141 IF ocon_rec.CREATED_BY_MODULE is null THEN
1142 ocon_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
1143 END IF;
1144 */
1145 ocon_rec.application_id := 530;
1146 ocon_rec.party_rel_rec.subject_type := 'PERSON';
1147 ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1148 ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1149 ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1150
1151
1152 -- sranka 1/15/2003
1153 -- assigning the value for the "orig_system_reference" for proper population of data in TCA while importing
1154 ocon_rec.orig_system_reference := org_rec.party_rec.orig_system_reference;
1155 ocon_rec.party_rel_rec.party_rec.orig_system_reference := org_rec.party_rec.orig_system_reference;
1156
1157
1158 IF x_generate_party_number = 'N' THEN
1159 ocon_rec.party_rel_rec.party_rec.party_number := x_party_number;
1160 END IF;
1161 IF x_gen_contact_number = 'N' THEN
1162 select hz_contact_numbers_s.nextval into x_contact_number from dual;
1163 end if;
1164 ocon_rec.contact_number := x_contact_number;
1165 -- ocon_rec.status := 'A';
1166 if x_rented_list_flag = 'R' then
1167 -- ocon_rec.status := 'I';
1168 ocon_rec.party_rel_rec.status := 'I';
1169 else
1170 -- ocon_rec.status := 'A';
1171 ocon_rec.party_rel_rec.status := 'A';
1172 end if;
1173 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and p_pr_party_id is NULL)) then
1174 hz_party_contact_v2pub.create_org_contact(
1175 'F',
1176 ocon_rec,
1177 x_org_contact_id,
1178 x_party_relationship_id,
1179 x_party_rel_party_id,
1180 x_party_number,
1181 x_return_status,
1182 x_msg_count,
1183 x_msg_data);
1184 end if;
1185 if x_return_status <> 'S' then
1186 p_component_name := 'CONTACT';
1187 Return;
1188 end if;
1189 else
1190 if l_rec_update_flag = 'Y' and p_pr_party_id is not NULL then
1191 select ORG_CONTACT_ID into x_org_contact_id from hz_org_contacts
1192 where PARTY_RELATIONSHIP_ID = l_rel_id ; -- x_party_rel_party_id;
1193
1194 ocon_rec.org_contact_id := x_org_contact_id;
1195 select OBJECT_VERSION_NUMBER into l_party_obj_number
1196 from hz_parties
1197 where party_id = x_org_party_id;
1198 select OBJECT_VERSION_NUMBER into l_pr_obj_number
1199 from hz_relationships
1200 where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
1201
1202 select OBJECT_VERSION_NUMBER into l_con_obj_number
1203 from hz_org_contacts
1204 where ORG_CONTACT_ID = x_org_contact_id;
1205 ocon_rec.orig_system_reference := NULL;
1206 ocon_rec.party_rel_rec.party_rec.orig_system_reference := NULL;
1207
1208 hz_party_contact_v2pub.update_org_contact(
1209 'F',
1210 ocon_rec,
1211 l_con_obj_number,
1212 l_pr_obj_number,
1213 l_party_obj_number,
1214 x_return_status,
1215 x_msg_count,
1216 x_msg_data);
1217 end if;
1218 if x_return_status <> 'S' then
1219 Return;
1220 end if;
1221 end if; -- if p_pr_party_id is null
1222 end if; -- if person_rec.first_name is not null
1223 -- The above three are not created if party is mapped.
1224 -- organization
1225 -- person
1226 -- org contact
1227 end if; -- l_is_party_mapped is NULL then
1228
1229 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1230
1231
1232 if language_rec.language_name is NOT null and x_per_party_id is not null then
1233 if language_rec.language_name is not NULL then
1234
1235 -- Create Language
1236
1237 x_return_status := null;
1238 x_msg_count := null;
1239 x_msg_data := null;
1240 language_rec.native_language := 'Y';
1241
1242 --language_rec.CREATED_BY_MODULE := 'Oracle Marketing';
1243 --R12 tca mandate: bug 4587049: all who calls create_customer should
1244 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
1245 /* --caller must populate this
1246 IF language_rec.CREATED_BY_MODULE is null THEN
1247 language_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
1248 END IF;
1249 */
1250 language_rec.application_id := 530;
1251 language_rec.party_id := x_per_party_id;
1252 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_language_use_reference_id is NULL)) then
1253 HZ_PERSON_INFO_V2PUB.create_person_language(
1254 FND_API.G_FALSE,
1255 language_rec,
1256 x_language_use_reference_id,
1257 x_return_status,
1258 x_msg_count,
1259 x_msg_data
1260 );
1261 end if;
1262 if x_return_status <> 'S' then
1263 p_component_name := 'LANGUAGE';
1264 return;
1265 end if;
1266 end if; -- if location_rec.address1 is not NULL
1267 else
1268 if language_rec.language_name is NOT NULL then
1269 if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
1270 select OBJECT_VERSION_NUMBER,language_use_reference_id into l_language_obj_number,l_language_use_reference_id
1271 from hz_person_language
1272 where party_id = x_per_party_id
1273 AND native_language = 'Y';
1274
1275 language_rec.language_use_reference_id := l_language_use_reference_id;
1276
1277 HZ_PERSON_INFO_V2PUB.update_person_language(
1278 FND_API.G_FALSE,
1279 language_rec,
1280 l_language_obj_number,
1281 x_return_status ,
1282 x_msg_count ,
1283 x_msg_data
1284 );
1285 end if;
1286 if x_return_status <> 'S' then
1287 return;
1288 end if;
1289 end if;-- inner language_rec.language_name is null;
1290 end if; -- language_rec.language_name is null;
1291
1292 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++
1293
1294 if l_is_party_mapped = 'Y' then
1295 open b2bparty;
1296 fetch b2bparty into l_b2b_party_exists;
1297 close b2bparty;
1298 if l_b2b_party_exists = 'Y' then
1299 open orgpartyid;
1300 fetch orgpartyid into x_org_party_id;
1301 close orgpartyid;
1302 x_party_rel_party_id := l_b2b_party_id;
1303 else
1304 AMS_List_Import_PUB.error_capture (
1305 1,
1306 'T',
1307 'F',
1308 null,
1309 x_return_status,
1310 x_msg_count,
1311 x_msg_data,
1312 p_import_list_header_id,
1313 i_import_source_line_id,
1314 null,
1315 null,
1316 'CONTACT',
1317 null,
1318 'PARTY_ID : Party does not exists for the mapped party_id '||to_char(l_b2b_party_id));
1319 x_return_status := 'E';
1320 return;
1321 end if;
1322 end if;
1323
1324
1325 if x_rented_list_flag <> 'R' then
1326
1327 if x_hz_dup_check = 'Y' then
1328 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1329 -- sranka Modified for COLt Enhancements 7/15/2003
1330 if org_location_rec.address1 is not NULL then
1331 if x_org_locatiON_Id is null and l_import_source_line_id is null then
1332 AMS_ListImport_PVT.address_echeck(
1333 p_party_id => x_org_party_id,
1334 x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data,
1337 p_location_id => x_org_locatiON_Id,
1338 p_address1 => org_location_rec.address1,
1339 p_city => org_location_rec.city,
1340 p_pcode => org_location_rec.postal_code,
1341 p_country => org_location_rec.country
1342 );
1343 end if;
1344 if x_return_status <> 'S' then
1345 p_component_name := 'ORG_ADDRESS';
1346 return;
1347 end if;
1348
1349 end if; -- org_location_rec.address1 is not NULL
1350 end if; -- SOLIN, bug 4423075, x_hz_dup_check = 'Y'
1351
1352 if x_org_location_id is null and x_org_party_id is not null then
1353 if org_location_rec.address1 is not NULL then
1354
1355 -- Create Location for Organization
1356
1357 AMS_Utility_PVT.Create_Log (
1358 x_return_status => x_return_status,
1359 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1360 p_log_used_by_id => p_import_list_header_id,
1361 p_msg_data => 'Create Location for Organization : Process starts ' ,
1362 p_msg_type => 'DEBUG');
1363
1364 x_return_status := null;
1365 x_msg_count := null;
1366 x_msg_data := null;
1367 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_org_location_id is NULL)) then
1368
1369 l_address_key :=hz_fuzzy_pub.Generate_Key (
1370 p_key_type => 'ADDRESS',
1371 p_address1 => org_location_rec.address1,
1372 p_postal_code => org_location_rec.postal_code);
1373
1374 AMS_Utility_PVT.Create_Log (
1375 x_return_status => x_return_status,
1376 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1377 p_log_used_by_id => p_import_list_header_id,
1378 p_msg_data => 'Create Location for Organization : Check existence for key '||l_address_key ,
1379 p_msg_type => 'DEBUG');
1380
1381
1382 select count(*) INTO l_address_key_count from hz_locations where address_key=l_address_key;
1383
1384 if l_address_key_count =0 then
1385
1386 AMS_Utility_PVT.Create_Log (
1387 x_return_status => x_return_status,
1388 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1389 p_log_used_by_id => p_import_list_header_id,
1390 p_msg_data => 'Create Location for Organization : Creating location ',
1391 p_msg_type => 'DEBUG');
1392
1393 AMS_ListImport_PVT.create_location (
1394 org_location_rec ,
1395 x_return_status ,
1396 x_msg_count ,
1397 x_msg_data ,
1398 x_org_location_id );
1399 end if;
1400 end if;
1401 if x_return_status <> 'S' then
1402 p_component_name := 'ORG_ADDRESS';
1403 return;
1404 end if;
1405 end if;
1406 else
1407 if l_rec_update_flag = 'Y' and x_org_location_id is not NULL then
1408 select OBJECT_VERSION_NUMBER into l_loc_obj_number
1409 from hz_locations
1410 where location_id = x_org_location_id;
1411 org_location_rec.location_id := x_org_location_id;
1412 hz_location_v2pub.update_location(
1413 'F',
1414 org_location_rec,
1415 l_loc_obj_number,
1416 x_return_status,
1417 x_msg_count,
1418 x_msg_data
1419 );
1420 end if;
1421 if x_return_status <> 'S' then
1422 return;
1423 end if;
1424 end if; -- x_org_location_id is null;
1425
1426 -- Creates party site for Org address
1427
1428 l_org_lp_psite_id := null;
1429 open ORG_LOCATION_EXISTS;
1430 fetch ORG_LOCATION_EXISTS into l_org_lp_psite_id;
1431 close ORG_LOCATION_EXISTS;
1432 if l_org_lp_psite_id is null and x_org_party_id is not null and x_org_location_id is not null then
1433 -- Create Party Site
1434 x_return_status := null;
1435 x_msg_count := null;
1436 x_msg_data := null;
1437 x_party_site_number := null;
1438
1439 org_psite_rec.party_id := x_org_party_id;
1440 org_psite_rec.location_id := x_org_location_id;
1441 org_psite_rec.status := 'A';
1442 -- x_pty_site_id := null;
1443 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_org_lp_psite_id is NULL)) then
1444 AMS_ListImport_PVT.create_party_site(
1445 org_psite_rec,
1446 x_return_status,
1447 x_msg_count,
1448 x_msg_data,
1449 x_org_party_site_id, -- sranka
1450 x_party_site_number
1451 );
1452 if x_return_status <> 'S' then
1453 p_component_name := 'ORG_ADDRESS';
1454 return;
1455 end if;
1456 end if;
1457 else
1458 if l_rec_update_flag = 'Y' and l_org_lp_psite_id is not NULL then
1459 org_psite_rec.party_site_id := l_org_lp_psite_id;
1460 select OBJECT_VERSION_NUMBER into l_ps_obj_number
1461 from hz_party_sites
1462 where party_site_id = l_org_lp_psite_id;
1463 hz_party_site_v2pub.update_party_site(
1464 'F',
1465 org_psite_rec,
1466 l_ps_obj_number,
1467 x_return_status,
1468 x_msg_count,
1469 x_msg_data
1470 );
1471 if x_return_status <> 'S' then
1472 return;
1473 end if;
1474 end if;
1475 end if; -- if l_org_lp_psite_id is null then
1476
1477 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1478
1479 if x_locatiON_Id is null and l_import_source_line_id is null then
1480 AMS_ListImport_PVT.address_echeck(
1481 p_party_id => x_org_party_id,
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_location_id => x_locatiON_Id,
1486 p_address1 => location_rec.address1,
1487 p_city => location_rec.city,
1488 p_pcode => location_rec.postal_code,
1489 p_country => location_rec.country
1490 );
1491 end if;
1492 if x_return_status <> 'S' then
1493 p_component_name := 'ADDRESS';
1494 return;
1495 end if;
1496
1497
1498 if x_location_id is null and x_org_party_id is not null then
1499 if location_rec.address1 is not NULL then
1500
1501 -- Create Location
1502
1503 x_return_status := null;
1504 x_msg_count := null;
1505 x_msg_data := null;
1506 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
1507 AMS_ListImport_PVT.create_location (
1508 location_rec ,
1509 x_return_status ,
1510 x_msg_count ,
1511 x_msg_data ,
1512 x_location_id );
1513 end if;
1514 if x_return_status <> 'S' then
1515 p_component_name := 'ADDRESS';
1516 return;
1517 end if;
1518 end if; -- if location_rec.address1 is not NULL
1519 else
1520 if l_rec_update_flag = 'Y' and x_location_id is not NULL then
1521 select OBJECT_VERSION_NUMBER into l_loc_obj_number
1522 from hz_locations
1523 where location_id = x_location_id;
1524 location_rec.location_id := x_location_id;
1525 hz_location_v2pub.update_location(
1526 'F',
1527 location_rec,
1528 l_loc_obj_number,
1529 x_return_status,
1530 x_msg_count,
1531 x_msg_data
1532 );
1533 end if;
1534 if x_return_status <> 'S' then
1535 return;
1536 end if;
1537 end if; -- x_location_id is null;
1538
1539
1540 l_lp_psite_id := null;
1541 open LOCATION_EXISTS;
1542 fetch LOCATION_EXISTS into l_lp_psite_id;
1543 close LOCATION_EXISTS;
1544 if l_lp_psite_id is null and x_org_party_id is not null and x_location_id is not null then
1545 -- Create Party Site
1546 x_return_status := null;
1547 x_msg_count := null;
1548 x_msg_data := null;
1549 x_party_site_number := null;
1550
1551 psite_rec.party_id := x_org_party_id;
1552 psite_rec.location_id := x_location_id;
1553 psite_rec.status := 'A';
1554 -- x_pty_site_id := null;
1555 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_lp_psite_id is NULL)) then
1556 AMS_ListImport_PVT.create_party_site(
1557 psite_rec,
1558 x_return_status,
1559 x_msg_count,
1560 x_msg_data,
1561 x_party_site_id,
1562 x_party_site_number
1563 );
1564 if x_return_status <> 'S' then
1565 p_component_name := 'ADDRESS';
1566 return;
1567 end if;
1568 end if;
1569 else
1570 if l_rec_update_flag = 'Y' and l_lp_psite_id is not NULL then
1571 psite_rec.party_site_id := l_lp_psite_id;
1572 select OBJECT_VERSION_NUMBER into l_ps_obj_number
1573 from hz_party_sites
1574 where party_site_id = l_lp_psite_id;
1575 hz_party_site_v2pub.update_party_site(
1576 'F',
1577 psite_rec,
1578 l_ps_obj_number,
1579 x_return_status,
1580 x_msg_count,
1581 x_msg_data
1582 );
1583 if x_return_status <> 'S' then
1584 return;
1585 end if;
1586 end if;
1587 end if; -- if l_lp_psite_id is null then
1588
1589 if x_party_site_id is not null and psiteuse_rec.site_use_type is not null then
1590 -- Create Party Site use
1591 x_return_status := null;
1592 x_msg_count := null;
1593 x_msg_data := null;
1594
1595 psiteuse_rec.party_site_id := x_party_site_id;
1596 psiteuse_rec.status := 'A';
1597 AMS_ListImport_PVT.create_party_site_use(
1598 psiteuse_rec,
1599 x_return_status,
1600 x_msg_count,
1601 x_msg_data,
1602 x_party_site_use_id
1603 );
1604 if x_return_status <> 'S' then
1605 p_component_name := 'ADDRESS';
1606 return;
1607 end if;
1608 end if;
1609
1610 -- Updates the org contacts party_site_id
1611 -- **************************************************
1612
1613 if (person_rec.person_first_name is not null and x_org_contact_id is not null) and
1614 (l_lp_psite_id is not null or x_party_site_id is not null) then
1615 open c_org_cont_rel;
1616 fetch c_org_cont_rel into l_object_version1;
1617 close c_org_cont_rel;
1618 open c_relationship;
1619 fetch c_relationship into l_object_version2;
1620 close c_relationship;
1621
1622 open c_party_rel;
1623 fetch c_party_rel into l_object_version3;
1624 close c_party_rel;
1625
1626 ocon_rec.org_contact_id := x_org_contact_id;
1627 -- ocon_rec.party_site_id := x_party_site_id;
1628 if l_lp_psite_id is not null then
1629 ocon_rec.party_site_id := l_lp_psite_id;
1630 end if;
1631 if x_party_site_id is not null then
1632 ocon_rec.party_site_id := x_party_site_id;
1633 end if;
1634
1635 ocon_rec.party_rel_rec.object_id := x_org_party_id;
1636 ocon_rec.orig_system_reference := NULL;
1637 ocon_rec.party_rel_rec.party_rec.orig_system_reference := NULL;
1638 hz_party_contact_v2pub.update_org_contact(
1639 'F',
1640 ocon_rec,
1641 l_object_version1,
1642 l_object_version2,
1643 l_object_version3,
1644 x_return_status,
1645 x_msg_count,
1646 x_msg_data);
1647 if x_msg_count > 1 then
1648 FOR i IN 1..x_msg_count LOOP
1649 x_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1650 x_tmp_var1 := substrb(x_tmp_var1 || ' '|| x_tmp_var,1,4000);
1651 END LOOP;
1652 x_msg_data := x_tmp_var1;
1653 END IF;
1654
1655 if x_return_status <> 'S' then
1656 p_component_name := 'CONTACT';
1657 x_return_status := x_return_status;
1658 x_msg_count := x_msg_count;
1659 x_msg_data := x_msg_data;
1660 return;
1661 end if;
1662
1663 end if;
1664
1665 -- ************************************************
1666
1667 -- Creating party_site for Contacts.
1668
1669 if person_rec.person_first_name is not null and x_party_rel_party_id is not null then
1670 if location_rec.address1 is not NULL and x_location_id is not null then
1671 l_lp_psite_id := null;
1672 open CHECK_PSITE_EXISTS;
1673 fetch CHECK_PSITE_EXISTS into l_lp_psite_id;
1674 close CHECK_PSITE_EXISTS;
1675 if l_lp_psite_id is null then
1676 -- Create Party Site
1677 x_return_status := null;
1678 x_msg_count := null;
1679 x_msg_data := null;
1680 x_party_site_number := null;
1681
1682 psite_rec.party_id := x_party_rel_party_id;
1683 psite_rec.location_id := x_location_id;
1684 psite_rec.status := 'A';
1685
1686 AMS_ListImport_PVT.create_party_site(
1687 psite_rec,
1688 x_return_status,
1689 x_msg_count,
1690 x_msg_data,
1691 x_party_site_id,
1692 x_party_site_number
1693 );
1694
1695 if x_return_status <> 'S' then
1696 p_component_name := 'ADDRESS';
1697 return;
1698 end if;
1699 if x_party_site_id is not null and psiteuse_rec.site_use_type is not null then
1700 -- Create Party Site use
1701 x_return_status := null;
1702 x_msg_count := null;
1703 x_msg_data := null;
1704
1705 psiteuse_rec.party_site_id := x_party_site_id;
1706 psiteuse_rec.status := 'A';
1707 AMS_ListImport_PVT.create_party_site_use(
1708 psiteuse_rec,
1709 x_return_status,
1710 x_msg_count,
1711 x_msg_data,
1712 x_party_site_use_id
1713 );
1714 if x_return_status <> 'S' then
1715 p_component_name := 'ADDRESS';
1716 return;
1717 end if;
1718 end if;
1719 end if;
1720 end if;
1721 end if;
1722
1723
1724 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1725 -- Create organization's Email
1726
1727 if org_email_rec.email_address is not NULL and x_org_party_id is not null then
1728
1729 x_return_status := null;
1730 x_msg_count := null;
1731 x_msg_data := null;
1732 cpoint_rec.contact_point_type := 'EMAIL';
1733 cpoint_rec.status := 'A';
1734 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1735 cpoint_rec.owner_table_id := x_org_party_id;
1736 email_rec.email_address := org_email_rec.email_address;
1737 l_email_exists := NULL;
1738 open org_email_exists(x_org_party_id);
1739 fetch org_email_exists into l_email_exists;
1740 close org_email_exists;
1741 if l_email_exists is NULL then
1742 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
1743 AMS_ListImport_PVT.create_contact_point(
1744 cpoint_rec,
1745 edi_rec,
1746 email_rec,
1747 phone_rec,
1748 telex_rec,
1749 web_rec,
1750 x_return_status,
1751 x_msg_count,
1752 x_msg_data,
1753 x_contact_point_id);
1754 end if;
1755 if x_return_status <> 'S' then
1756 p_component_name := 'EMAIL';
1757 return;
1758 end if;
1759 end if; -- l_email_exists is NULL then
1760 end if; -- org_email_rec.email_address
1761 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1762
1763
1764
1765 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1766 -- Create organization Phone -- sranka
1767
1768 if org_party_site_phone_rec.phone_number is not NULL and x_org_party_site_id is not null then
1769
1770 x_return_status := null;
1771 x_msg_count := null;
1772 x_msg_data := null;
1773 cpoint_rec.contact_point_type := 'PHONE';
1774 cpoint_rec.status := 'A';
1775 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1776 cpoint_rec.owner_table_id := x_org_party_site_id;
1777 org_party_site_phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1778 org_party_site_phone_rec.phone_number := org_phone_rec.phone_number;
1779 org_party_site_phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1780 org_party_site_phone_rec.phone_area_code := org_phone_rec.phone_area_code;
1781 org_party_site_phone_rec.phone_extension := org_phone_rec.phone_extension;
1782
1783 l_phone_exists := NULL;
1784 open org_party_site_phone_exists(x_org_party_site_id,org_party_site_phone_rec.phone_line_type);
1785 fetch org_party_site_phone_exists into l_phone_exists;
1786 close org_party_site_phone_exists;
1787 if l_phone_exists is not null then
1788 l_phone_id := NULL;
1789 open c_org_party_site_phone_id(cpoint_rec.owner_table_id,org_party_site_phone_rec.phone_line_type);
1790 fetch c_org_party_site_phone_id into l_phone_id;
1791 close c_org_party_site_phone_id;
1792 end if;
1793 if l_phone_exists is NULL then
1794 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
1795 AMS_ListImport_PVT.create_contact_point(
1796 cpoint_rec,
1797 edi_rec,
1798 email_rec,
1799 org_party_site_phone_rec,
1800 telex_rec,
1801 web_rec,
1802 x_return_status,
1803 x_msg_count,
1804 x_msg_data,
1805 x_contact_point_id);
1806 l_phone_id := x_contact_point_id;
1807 end if;
1808 if x_return_status <> 'S' then
1809 p_component_name := 'PHONE';
1810 return;
1811 end if;
1812 end if; -- l_phone_exists is NULL then
1813
1814 end if; -- org_phone_rec.phone_number
1815 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1816
1817
1818
1819
1820 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1821 -- Create organization Phone
1822
1823 if org_phone_rec.phone_number is not NULL and x_org_party_id is not null then
1824
1825 x_return_status := null;
1826 x_msg_count := null;
1827 x_msg_data := null;
1828 cpoint_rec.contact_point_type := 'PHONE';
1829 cpoint_rec.status := 'A';
1830 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1831 cpoint_rec.owner_table_id := x_org_party_id;
1832 phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1833 phone_rec.phone_number := org_phone_rec.phone_number;
1834 phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1835 phone_rec.phone_area_code := org_phone_rec.phone_area_code;
1836 phone_rec.phone_extension := org_phone_rec.phone_extension;
1837
1838 l_phone_exists := NULL;
1839 open org_phone_exists(x_org_party_id,phone_rec.phone_line_type);
1840 fetch org_phone_exists into l_phone_exists;
1841 close org_phone_exists;
1842 if l_phone_exists is not null then
1843 l_phone_id := NULL;
1844 open c_org_phone_id(cpoint_rec.owner_table_id,phone_rec.phone_line_type);
1845 fetch c_org_phone_id into l_phone_id;
1846 close c_org_phone_id;
1847 end if;
1848 if l_phone_exists is NULL then
1849 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
1850 AMS_ListImport_PVT.create_contact_point(
1851 cpoint_rec,
1852 edi_rec,
1853 email_rec,
1854 phone_rec,
1855 telex_rec,
1856 web_rec,
1857 x_return_status,
1858 x_msg_count,
1859 x_msg_data,
1860 x_contact_point_id);
1861 l_phone_id := x_contact_point_id;
1862 end if;
1863 if x_return_status <> 'S' then
1864 p_component_name := 'PHONE';
1865 return;
1866 end if;
1867 end if; -- l_phone_exists is NULL then
1868
1869 end if; -- org_phone_rec.phone_number
1870 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1871 -- Create contact points Phone
1872
1873 if x_phone_number is not NULL and (x_party_rel_party_id is not null or x_org_party_id is not null ) then
1874
1875 x_return_status := null;
1876 x_msg_count := null;
1877 x_msg_data := null;
1878 cpoint_rec.contact_point_type := 'PHONE';
1879 cpoint_rec.status := 'A';
1880 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1881 if x_org_party_id is not null then
1882 cpoint_rec.owner_table_id := x_org_party_id;
1883 end if;
1884 if x_party_rel_party_id is not null then
1885 cpoint_rec.owner_table_id := x_party_rel_party_id;
1886 end if;
1887 phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1888 phone_rec.phone_number := x_phone_number;
1889 phone_rec.phone_country_code := x_phone_country_code;
1890 phone_rec.phone_area_code := x_phone_area_code;
1891 phone_rec.phone_extension := x_phone_extention;
1892
1893 l_phone_exists := NULL;
1894 open phone_exists(x_party_rel_party_id,phone_rec.phone_line_type);
1895 fetch phone_exists into l_phone_exists;
1896 close phone_exists;
1897 if l_phone_exists is not null then
1898 l_phone_id := NULL;
1899 open c_phone_id(cpoint_rec.owner_table_id,phone_rec.phone_line_type);
1900 fetch c_phone_id into l_phone_id;
1901 close c_phone_id;
1902 end if;
1903 if l_phone_exists is NULL then
1904 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
1905 AMS_ListImport_PVT.create_contact_point(
1906 cpoint_rec,
1907 edi_rec,
1908 email_rec,
1909 phone_rec,
1910 telex_rec,
1911 web_rec,
1912 x_return_status,
1913 x_msg_count,
1914 x_msg_data,
1915 x_contact_point_id);
1916 l_phone_id := x_contact_point_id;
1917 end if;
1918 if x_return_status <> 'S' then
1919 p_component_name := 'PHONE';
1920 return;
1921 end if;
1922 end if; -- l_phone_exists is NULL then
1923
1924 end if; -- x_phone_number
1925
1926 -- Create contact points Fax
1927
1928 if x_fax_number is not NULL and (x_party_rel_party_id is not null or x_org_party_id is not null ) then
1929
1930 x_return_status := null;
1931 x_msg_count := null;
1932 x_msg_data := null;
1933 cpoint_rec.contact_point_type := 'PHONE';
1934 cpoint_rec.status := 'A';
1935 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1936 if x_org_party_id is not null then
1937 cpoint_rec.owner_table_id := x_org_party_id;
1938 end if;
1939 if x_party_rel_party_id is not null then
1940 cpoint_rec.owner_table_id := x_party_rel_party_id;
1941 end if;
1942 fax_rec.phone_line_type := 'FAX';--'GEN';
1943 fax_rec.phone_number := x_fax_number;
1944 fax_rec.phone_country_code := x_fax_country_code;
1945 fax_rec.phone_area_code := x_fax_area_code;
1946
1947 l_fax_exists := NULL;
1948 open fax_exists(x_party_rel_party_id);
1949 fetch fax_exists into l_fax_exists;
1950 close fax_exists;
1951 if l_fax_exists is not null then
1952 l_fax_id := NULL;
1953 open c_fax_id(cpoint_rec.owner_table_id);
1954 fetch c_fax_id into l_fax_id;
1955 close c_fax_id;
1956 end if;
1957 if l_fax_exists is NULL then
1958 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_fax_exists is NULL)) then
1959 AMS_ListImport_PVT.create_contact_point(
1960 cpoint_rec,
1961 edi_rec,
1962 email_rec,
1963 fax_rec,
1964 telex_rec,
1965 web_rec,
1966 x_return_status,
1967 x_msg_count,
1968 x_msg_data,
1969 x_contact_point_id);
1970 l_fax_id := x_contact_point_id;
1971 end if;
1972 if x_return_status <> 'S' then
1973 p_component_name := 'FAX';
1974 return;
1975 end if;
1976 end if; -- l_phone_exists is NULL then
1977
1978 end if; -- x_phone_number
1979
1980 -- B2B URL
1981
1982 if x_url is not NULL and length(x_url) > 1 and x_org_party_id is not null then
1983
1984 x_return_status := null;
1985 x_msg_count := null;
1986 x_msg_data := null;
1987 cpoint_rec.contact_point_type := 'WEB';
1988 cpoint_rec.status := 'A';
1989 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1990 if x_org_party_id is not null then
1991 cpoint_rec.owner_table_id := x_org_party_id;
1992 end if;
1993 web_rec.url := x_url;
1994 web_rec.web_type := 'com';
1995
1996 l_url_exists := NULL;
1997 open url_exists(x_org_party_id);
1998 fetch url_exists into l_url_exists;
1999 close url_exists;
2000 if l_url_exists is not null then
2001 l_url_id := NULL;
2002 open c_url_id(cpoint_rec.owner_table_id);
2003 fetch c_url_id into l_url_id;
2004 close c_url_id;
2005 end if;
2006 if l_url_exists is NULL then
2007 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_url_exists is NULL)) then
2008 AMS_ListImport_PVT.create_contact_point(
2009 cpoint_rec,
2010 edi_rec,
2011 email_rec,
2012 fax_rec,
2013 telex_rec,
2014 web_rec,
2015 x_return_status,
2016 x_msg_count,
2017 x_msg_data,
2018 x_contact_point_id);
2019 l_url_id := x_contact_point_id;
2020 end if;
2021 if x_return_status <> 'S' then
2022 p_component_name := 'PHONE';
2023 return;
2024 end if;
2025 end if; -- l_phone_exists is NULL then
2026
2027 end if; -- x_phone_number
2028
2029 -- Create contact points Email
2030
2031 if x_email_address is not NULL and (x_party_rel_party_id is not null or x_org_party_id is not null ) then
2032
2033 x_return_status := null;
2034 x_msg_count := null;
2035 x_msg_data := null;
2036 cpoint_rec.contact_point_type := 'EMAIL';
2037 cpoint_rec.status := 'A';
2038 cpoint_rec.owner_table_name := 'HZ_PARTIES';
2039 if x_org_party_id is not null then
2040 cpoint_rec.owner_table_id := x_org_party_id;
2041 end if;
2042 if x_party_rel_party_id is not null then
2043 cpoint_rec.owner_table_id := x_party_rel_party_id;
2044 end if;
2045 email_rec.email_address := x_email_address;
2046 l_email_exists := NULL;
2047 open email_exists(x_party_rel_party_id);
2048 fetch email_exists into l_email_exists;
2049 close email_exists;
2050 if l_email_exists is NULL then
2051 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
2052 AMS_ListImport_PVT.create_contact_point(
2053 cpoint_rec,
2054 edi_rec,
2055 email_rec,
2056 phone_rec,
2057 telex_rec,
2058 web_rec,
2059 x_return_status,
2060 x_msg_count,
2061 x_msg_data,
2062 x_contact_point_id);
2063 end if;
2064 if x_return_status <> 'S' then
2065 p_component_name := 'EMAIL';
2066 return;
2067 end if;
2068 end if; -- l_email_exists is NULL then
2069 end if; -- x_email_address
2070 /*
2071 if ((x_phone_number is not NULL OR x_email_address is not NULL ) and l_rec_update_flag = 'Y' and x_party_rel_party_id is not null) then
2072 phone_rec.phone_line_type := 'GEN';
2073 phone_rec.phone_number := x_phone_number;
2074 phone_rec.phone_country_code := x_phone_country_code;
2075 phone_rec.phone_area_code := x_phone_area_code;
2076 phone_rec.phone_extension := x_phone_extention;
2077
2078 l_phone_id := NULL;
2079 open c_phone_id(x_party_rel_party_id,phone_rec.phone_line_type);
2080 fetch c_phone_id into l_phone_id;
2081 close c_phone_id;
2082 if l_phone_id is not null then
2083 x_contact_point_id := l_phone_id;
2084 end if;
2085
2086 email_rec.email_address := x_email_address;
2087 l_email_id := NULL;
2088 open c_email_id(x_party_rel_party_id);
2089 fetch c_email_id into l_email_id;
2090 close c_email_id;
2091 if l_email_id is not null then
2092 x_contact_point_id := l_email_id;
2093 end if;
2094 cpoint_rec.contact_point_id := x_contact_point_id;
2095 select OBJECT_VERSION_NUMBER into l_cp_obj_number
2096 from hz_contact_points
2097 where contact_point_id = x_contact_point_id;
2098
2099 hz_contact_point_v2pub.update_contact_point(
2100 'F',
2101 cpoint_rec,
2102 edi_rec,
2103 email_rec,
2104 phone_rec,
2105 telex_rec,
2106 web_rec,
2107 l_cp_obj_number,
2108 x_return_status,
2109 x_msg_count,
2110 x_msg_data);
2111 if x_return_status <> 'S' then
2112 return;
2113 end if;
2114 end if;
2115 */
2116 end if; -- if x_rented_list_flag <> 'R'
2117
2118 -- Updates the marketing tables with the party_id
2119 if l_is_party_mapped is NULL then
2120 l_enabled_flag := 'Y';
2121 end if;
2122
2123 if l_is_party_mapped = 'Y' and l_b2b_party_exists = 'Y' then
2124 l_overlay := 'Y';
2125 l_enabled_flag := 'Y';
2126 end if;
2127
2128 if l_is_party_mapped = 'Y' and l_b2b_party_exists is NULL then
2129 l_enabled_flag := 'N';
2130 end if;
2131
2132 i_party_id := null;
2133
2134 if nvl(x_party_rel_party_id,0) > 0 then
2135 i_party_id := x_party_rel_party_id;
2136 elsif x_org_party_id is not null and x_per_party_id is not null then
2137 i_party_id := nvl(p_party_id, x_org_party_id);
2138 end if;
2139
2140 if i_party_id is null then
2141 i_party_id := x_org_party_id;
2142 end if;
2143
2144 if i_party_id is not null then
2145 p_party_id := i_party_id ;
2146 end if;
2147 if i_party_id is not null and p_import_list_header_id is not null then
2148 UPDATE ams_imp_source_lines
2149 SET party_id = i_party_id,
2150 organization_id = x_org_party_id,
2151 load_status = 'SUCCESS',
2152 contact_point_id = l_phone_id,
2153 location_id = x_location_id,
2154 enabled_flag = l_enabled_flag
2155 -- WHERE import_source_line_id = org_rec.party_rec.orig_system_reference
2156 -- sranka 1/14/2003
2157 WHERE import_source_line_id = l_import_source_line_id
2158 AND import_list_header_id = p_import_list_header_id;
2159 x_return_status := 'S';
2160 open b2bxml;
2161 fetch b2bxml into l_xml_element_id;
2162 close b2bxml;
2163 if l_xml_element_id is not null then
2164 process_element_success( p_import_list_header_id, l_xml_element_id) ;
2165 end if;
2166 end if;
2167 if i_party_id is not null and p_import_list_header_id is not null then
2168 create_party_source (p_import_list_header_id, i_import_source_line_id,l_overlay);
2169 end if;
2170
2171 end if; -- x_b2b = 'Y'
2172
2173
2174
2175 if x_b2b = 'N' then
2176
2177
2178
2179
2180
2181 -- Creates Person
2182 -- i_import_source_line_id := person_rec.party_rec.orig_system_reference; // original
2183 i_import_source_line_id := l_import_source_line_id;
2184
2185 x_return_status := null;
2186 x_msg_count := null;
2187 x_msg_data := null;
2188 x_party_number := null;
2189 l_b2c_party_id := p_party_id;
2190
2191 if l_is_party_mapped is NULL then
2192 -- The following three are not created if party is mapped.
2193 -- person
2194
2195 if x_hz_dup_check = 'Y' then
2196 l_overlay := null;
2197 -- Use unencrypted name to find party in TCA table
2198
2199
2200 if x_per_party_id is null and i_import_source_line_id is null then
2201 IF x_rented_list_flag <> 'R' THEN
2202
2203
2204
2205
2206 person_party_echeck(
2207 p_party_id => x_per_party_id,
2208 x_return_status => x_return_status,
2209 x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data,
2211 p_per_first_name => person_rec.person_first_name,
2212 p_per_last_name => person_rec.person_last_name,
2213 p_address1 => location_rec.address1,
2214 p_country => location_rec.country,
2215 p_email_address => x_email_address, -- email_rec.email_address,
2216 p_ph_country_code => x_phone_country_code, -- phone_rec.phone_country_code,
2217 p_ph_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
2218 p_ph_number => x_phone_number, -- phone_rec.phone_number
2219 p_orig_system_reference => org_rec.party_rec.orig_system_reference
2220
2221 );
2222
2223
2224 ELSE
2225 rented_person_party_echeck(
2226 p_party_id => x_per_party_id,
2227 x_return_status => x_return_status,
2228 x_msg_count => x_msg_count,
2229 x_msg_data => x_msg_data,
2230 p_per_first_name => person_rec.person_first_name,
2231 p_per_last_name => person_rec.person_last_name,
2232 p_address1 => location_rec.address1,
2233 p_country => location_rec.country,
2234 p_email_address => x_email_address, -- email_rec.email_address,
2235 p_ph_country_code => x_phone_country_code, -- phone_rec.phone_country_code,
2236 p_ph_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
2237 p_ph_number => x_phone_number -- phone_rec.phone_number
2238 );
2239 END IF;
2240 else
2241 open c_b2c_source_rec;
2242 fetch c_b2c_source_rec into src_person_PARTY_ID,src_PARTY_LOCATION_ID;
2243 close c_b2c_source_rec;
2244 x_per_party_id := src_person_PARTY_ID;
2245 x_location_id := src_PARTY_LOCATION_ID;
2246
2247 if p_party_id is not null then
2248 l_validate := null;
2249
2250 open c_validate_b2c;
2251 fetch c_validate_b2c into l_validate;
2252 close c_validate_b2c;
2253
2254 if l_validate is null then
2255 --Throw error as party_id that was passed is incorrect
2256 AMS_List_Import_PUB.error_capture (
2257 1,
2258 'T',
2259 'F',
2260 null,
2261 x_return_status,
2262 x_msg_count,
2263 x_msg_data,
2264 p_import_list_header_id,
2265 i_import_source_line_id,
2266 null,
2267 null,
2268 'PERSON',
2269 null,
2270 'PARTY_ID : Party does not exists for the mapped party_id '||to_char(p_party_id));
2271 x_return_status := 'E';
2272 return;
2273 end if;
2274 end if;
2275 end if;
2276
2277 -- Unencrypted party name doesn't exist in TCA and search by encrpted party name
2278 -- SOLIN, bug 4224506
2279 -- check first name and last name also
2280 if x_rented_list_flag = 'R' and x_per_party_id is NULL AND
2281 person_rec.person_first_name IS NOT NULL AND
2282 person_rec.person_last_name IS NOT NULL
2283 THEN
2284 -- Encrypt first name and last name
2285 person_rec.person_first_name := AMS_Import_Security_PVT.Get_DeEncrypt_String (
2286 p_input_string => person_rec.person_first_name,
2287 p_header_id => null,
2288 p_encrypt_flag => TRUE);
2289 person_rec.person_last_name := AMS_Import_Security_PVT.Get_DeEncrypt_String (
2290 p_input_string => person_rec.person_last_name,
2291 p_header_id => null,
2292 p_encrypt_flag => TRUE);
2293
2294
2295 rented_person_party_echeck(
2296 p_party_id => x_per_party_id,
2297 x_return_status => x_return_status,
2298 x_msg_count => x_msg_count,
2299 x_msg_data => x_msg_data,
2300 p_per_first_name => person_rec.person_first_name,
2301 p_per_last_name => person_rec.person_last_name,
2302 p_address1 => location_rec.address1,
2303 p_country => location_rec.country,
2304 p_email_address => x_email_address, -- email_rec.email_address,
2305 p_ph_country_code => x_phone_country_code, -- phone_rec.phone_country_code,
2306 p_ph_area_code => x_phone_area_code, -- phone_rec.phone_area_code,
2307 p_ph_number => x_phone_number -- phone_rec.phone_number
2308 );
2309 end if;
2310
2311
2312
2313 if x_return_status <> 'S' then
2314 p_component_name := 'PERSON';
2315 return;
2316 end if;
2317 if x_per_party_id is not null then
2318 l_overlay := 'Y';
2319 x_new_party := 'Y';
2320 end if;
2321 end if;
2322
2323
2324
2325
2326
2327 if x_per_party_id is null then
2328 /*
2329 if x_generate_party_number = 'N' then
2330 select hz_party_number_s.nextval into x_party_number from dual;
2331 end if;
2332 select hz_parties_s.nextval into x_per_party_id from dual;
2333
2334 person_rec.party_rec.party_number := x_party_number;
2335 person_rec.party_rec.party_id := x_per_party_id;
2336 */
2337 x_return_status := null;
2338 x_msg_count := 0;
2339 x_msg_data := null;
2340
2341 if x_rented_list_flag = 'R' then
2342 person_rec.party_rec.status := 'I';
2343 --person_rec.person_first_name := 'MKT PARTY FIRST NAME-'||person_rec.party_rec.orig_system_reference;
2344 --person_rec.person_last_name := 'MKT PARTY LAST NAME-'||person_rec.party_rec.orig_system_reference;
2345 end if;
2346
2347 --person_rec.CREATED_BY_MODULE := 'Oracle Marketing';
2348 --R12 tca mandate: bug 4587049: all who calls create_customer should
2349 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
2350 /* --caller must populate this
2351 IF person_rec.CREATED_BY_MODULE is null THEN
2352 person_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
2353 END IF;
2354 */
2355 person_rec.application_id := 530;
2356 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
2357 if x_generate_party_number = 'N' then
2358 select hz_party_number_s.nextval into x_party_number from dual;
2359 end if;
2360 select hz_parties_s.nextval into x_per_party_id from dual;
2361 person_rec.party_rec.party_number := x_party_number;
2362 person_rec.party_rec.party_id := x_per_party_id;
2363
2364
2365
2366 hz_party_v2pub.create_person(
2367 'F',
2368 person_rec,
2369 x_per_party_id,
2370 x_party_number,
2371 x_person_profile_id,
2372 x_return_status,
2373 x_msg_count,
2374 x_msg_data);
2375
2376
2377 end if;
2378
2379
2380 if x_return_status <> 'S' then
2381 p_component_name := 'PERSON';
2382 RETURN;
2383 end if;
2384 else
2385 if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
2386 person_rec.party_rec.party_id := x_per_party_id;
2387 select OBJECT_VERSION_NUMBER into l_party_obj_number
2388 from hz_parties
2389 where party_id = x_per_party_id;
2390 person_rec.party_rec.orig_system_reference := null;
2391 hz_party_v2pub.update_person(
2392 'F',
2393 person_rec,
2394 l_party_obj_number,
2395 x_person_profile_id,
2396 x_return_status,
2397 x_msg_count,
2398 x_msg_data
2399 );
2400 end if;
2401 if x_return_status <> 'S' then
2402 RETURN;
2403 end if;
2404 end if; -- x_per_party_id is null then
2405
2406
2407 -- The above is not created if party is mapped.
2408 -- person
2409 end if; -- l_is_party_mapped is NULL then
2410
2411 ---++++++++++++++++++++++++++++++++++++++++
2412
2413
2414 if language_rec.language_name is NOT null and x_per_party_id is not null then
2415 if language_rec.language_name is not NULL then
2416
2417 -- Create Language
2418
2419 x_return_status := null;
2420 x_msg_count := null;
2421 x_msg_data := null;
2422 language_rec.native_language := 'Y';
2423
2424 --language_rec.CREATED_BY_MODULE := 'Oracle Marketing';
2425 --R12 tca mandate: bug 4587049: all who calls create_customer should
2426 --populate created_by_module. List import populates in AMS_LISTIMPORT_PVT amsvimlb.pls
2427 /* --caller must populate this
2428 IF language_rec.CREATED_BY_MODULE is null THEN
2429 language_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
2430 END IF;
2431 */
2432 language_rec.application_id := 530;
2433 language_rec.party_id := x_per_party_id;
2434 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
2435
2436 HZ_PERSON_INFO_V2PUB.create_person_language(
2437 FND_API.G_FALSE,
2438 language_rec,
2439 x_language_use_reference_id,
2440 x_return_status,
2441 x_msg_count,
2442 x_msg_data
2443 );
2444
2445
2446
2447 end if;
2448 if x_return_status <> 'S' then
2449 p_component_name := 'LANGUAGE';
2450 return;
2451 end if;
2452 end if; -- if location_rec.address1 is not NULL
2453 else
2454
2455
2456
2457 if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
2458
2459
2460 IF person_rec.person_first_name IS NOT NULL AND language_rec.language_name is not NULL then
2461 select OBJECT_VERSION_NUMBER,language_use_reference_id into l_language_obj_number,l_language_use_reference_id
2462 from hz_person_language
2463 where party_id = x_per_party_id
2464 AND native_language = 'Y';
2465
2466 language_rec.language_use_reference_id := l_language_use_reference_id;
2467
2468 HZ_PERSON_INFO_V2PUB.update_person_language(
2469 FND_API.G_FALSE,
2470 language_rec,
2471 l_language_obj_number,
2472 x_return_status ,
2473 x_msg_count ,
2474 x_msg_data
2475 );
2476 END if;
2477 end if;
2478
2479
2480
2481
2482 if x_return_status <> 'S' then
2483 return;
2484 end if;
2485 end if; -- language_rec.language_name is null;
2486
2487 ---++++++++++++++++++++++++++++++++++++++++
2488
2489
2490 if l_is_party_mapped = 'Y' then
2491 open b2cparty;
2492 fetch b2cparty into l_b2c_party_exists;
2493 close b2cparty;
2494 if l_b2c_party_exists = 'Y' then
2495 x_per_party_id := l_b2c_party_id;
2496 else
2497 AMS_List_Import_PUB.error_capture (
2498 1,
2499 'T',
2500 'F',
2501 null,
2502 x_return_status,
2503 x_msg_count,
2504 x_msg_data,
2505 p_import_list_header_id,
2506 i_import_source_line_id,
2507 null,
2508 null,
2509 'PERSON',
2510 null,
2511 'PARTY_ID : Party does not exists for the mapped party_id '||to_char(l_b2c_party_id));
2512 x_return_status := 'E';
2513 return;
2514 end if;
2515 end if;
2516
2517 if x_rented_list_flag <> 'R' then
2518 if x_hz_dup_check = 'Y' then
2519 if x_location_id is null and i_import_source_line_id is null then
2520 AMS_ListImport_PVT.address_echeck(
2521 p_party_id => x_per_party_id,
2522 x_return_status => x_return_status,
2523 x_msg_count => x_msg_count,
2524 x_msg_data => x_msg_data,
2525 p_location_id => x_location_id,
2526 p_address1 => location_rec.address1,
2527 p_city => location_rec.city,
2528 p_pcode => location_rec.postal_code,
2529 p_country => location_rec.country
2530 );
2531 end if;
2532 if x_return_status <> 'S' then
2533 p_component_name := 'ADDRESS';
2534 return;
2535 end if;
2536 end if;
2537
2538 if x_location_id is null and x_per_party_id is not null then
2539 if location_rec.address1 is not NULL then
2540
2541 -- Create Location
2542
2543 x_return_status := null;
2544 x_msg_count := null;
2545 x_msg_data := null;
2546 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
2547 AMS_ListImport_PVT.create_location (
2548 location_rec ,
2549 x_return_status ,
2550 x_msg_count ,
2551 x_msg_data ,
2552 x_location_id );
2553 end if;
2554 if x_return_status <> 'S' then
2555 p_component_name := 'ADDRESS';
2556 return;
2557 end if;
2558 end if; -- if location_rec.address1 is not NULL
2559 else
2560 if l_rec_update_flag = 'Y' and x_location_id is not NULL then
2561 select OBJECT_VERSION_NUMBER into l_loc_obj_number
2562 from hz_locations
2563 where location_id = x_location_id;
2564 location_rec.location_id := x_location_id;
2565 hz_location_v2pub.update_location(
2566 'F',
2567 location_rec,
2568 l_loc_obj_number,
2569 x_return_status,
2570 x_msg_count,
2571 x_msg_data
2572 );
2573 if x_return_status <> 'S' then
2574 return;
2575 end if;
2576 end if;
2577 end if; -- x_location_id is null then
2578
2579 -- Party Site creation
2580 l_lp_psite_id := null;
2581 open PER_LOCATION_EXISTS;
2582 fetch PER_LOCATION_EXISTS into l_lp_psite_id;
2583 close PER_LOCATION_EXISTS;
2584 if l_lp_psite_id is null and x_per_party_id is not null and x_location_id is not null then
2585 -- Create Party Site
2586 x_return_status := null;
2587 x_msg_count := null;
2588 x_msg_data := null;
2589 x_party_site_number := null;
2590
2591 psite_rec.party_id := x_per_party_id;
2592 psite_rec.location_id := x_location_id;
2593 psite_rec.status := 'A';
2594 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_lp_psite_id is NULL)) then
2595 AMS_ListImport_PVT.create_party_site(
2596 psite_rec,
2597 x_return_status,
2598 x_msg_count,
2599 x_msg_data,
2600 x_party_site_id,
2601 x_party_site_number
2602 );
2603 end if;
2604 if x_return_status <> 'S' then
2605 p_component_name := 'ADDRESS';
2606 return;
2607 end if;
2608 else
2609 if l_rec_update_flag = 'Y' and l_lp_psite_id is not NULL then
2610 psite_rec.party_site_id := l_lp_psite_id;
2611 select OBJECT_VERSION_NUMBER into l_ps_obj_number
2612 from hz_party_sites
2613 where party_site_id = l_lp_psite_id;
2614 hz_party_site_v2pub.update_party_site(
2615 'F',
2616 psite_rec,
2617 l_ps_obj_number,
2618 x_return_status,
2619 x_msg_count,
2620 x_msg_data
2621 );
2622 if x_return_status <> 'S' then
2623 return;
2624 end if;
2625 end if;
2626 end if; -- if l_lp_psite_id is null then
2627
2628 if x_party_site_id is not null and psiteuse_rec.site_use_type is not null then
2629 -- Create Party Site use
2630 x_return_status := null;
2631 x_msg_count := null;
2632 x_msg_data := null;
2633
2634 psiteuse_rec.party_site_id := x_party_site_id;
2635 psiteuse_rec.status := 'A';
2636 AMS_ListImport_PVT.create_party_site_use(
2637 psiteuse_rec,
2638 x_return_status,
2639 x_msg_count,
2640 x_msg_data,
2641 x_party_site_use_id
2642 );
2643 if x_return_status <> 'S' then
2644 p_component_name := 'ADDRESS';
2645 return;
2646 end if;
2647 end if;
2648
2649
2650 -- Create contact points Phone
2651
2652 if x_phone_number is not NULL and x_per_party_id is not null then
2653
2654 x_return_status := null;
2655 x_msg_count := null;
2656 x_msg_data := null;
2657 cpoint_rec.contact_point_type := 'PHONE';
2658 cpoint_rec.status := 'A';
2659 cpoint_rec.owner_table_name := 'HZ_PARTIES';
2660 cpoint_rec.owner_table_id := x_per_party_id;
2661 phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
2662 phone_rec.phone_number := x_phone_number;
2663 phone_rec.phone_country_code := x_phone_country_code;
2664 phone_rec.phone_area_code := x_phone_area_code;
2665 phone_rec.phone_extension := x_phone_extention;
2666
2667
2668 l_phone_exists := NULL;
2669 open phone_exists(x_per_party_id,phone_rec.phone_line_type);
2670 fetch phone_exists into l_phone_exists;
2671 close phone_exists;
2672 if l_phone_exists is not null then
2673 l_phone_id := NULL;
2674 open c_phone_id(x_per_party_id,phone_rec.phone_line_type);
2675 fetch c_phone_id into l_phone_id;
2676 close c_phone_id;
2677 end if;
2678 if l_phone_exists is NULL then
2679 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
2680 AMS_ListImport_PVT.create_contact_point(
2681 cpoint_rec,
2682 edi_rec,
2683 email_rec,
2684 phone_rec,
2685 telex_rec,
2686 web_rec,
2687 x_return_status,
2688 x_msg_count,
2689 x_msg_data,
2690 x_contact_point_id);
2691 l_phone_id := x_contact_point_id;
2692 end if;
2693 if x_return_status <> 'S' then
2694 p_component_name := 'PHONE';
2695 return;
2696 end if;
2697 end if; -- l_phone_exists is NULL then
2698 end if; -- x_phone_number
2699
2700 -- Create contact points Fax
2701
2702 if x_fax_number is not NULL and x_per_party_id is not null then
2703
2704 x_return_status := null;
2705 x_msg_count := null;
2706 x_msg_data := null;
2707 cpoint_rec.contact_point_type := 'PHONE';
2708 cpoint_rec.status := 'A';
2709 cpoint_rec.owner_table_name := 'HZ_PARTIES';
2710 cpoint_rec.owner_table_id := x_per_party_id;
2711 fax_rec.phone_line_type := 'FAX';--'GEN';
2712 fax_rec.phone_number := x_fax_number;
2713 fax_rec.phone_country_code := x_fax_country_code;
2714 fax_rec.phone_area_code := x_fax_area_code;
2715
2716
2717 l_fax_exists := NULL;
2718 open fax_exists(x_per_party_id);
2719 fetch fax_exists into l_fax_exists;
2720 close fax_exists;
2721 if l_fax_exists is not null then
2722 l_fax_id := NULL;
2723 open c_fax_id(x_per_party_id);
2724 fetch c_fax_id into l_phone_id;
2725 close c_fax_id;
2726 end if;
2727 if l_fax_exists is NULL then
2728 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_fax_exists is NULL)) then
2729 AMS_ListImport_PVT.create_contact_point(
2730 cpoint_rec,
2731 edi_rec,
2732 email_rec,
2733 fax_rec,
2734 telex_rec,
2735 web_rec,
2736 x_return_status,
2737 x_msg_count,
2738 x_msg_data,
2739 x_contact_point_id);
2740 l_fax_id := x_contact_point_id;
2741 end if;
2742 if x_return_status <> 'S' then
2743 p_component_name := 'PHONE';
2744 return;
2745 end if;
2746 end if; -- l_phone_exists is NULL then
2747 end if; -- x_phone_number
2748
2749 -- B2C URL
2750
2751 if x_url is not NULL and length(x_url) > 1 and x_per_party_id is not null then
2752
2753 x_return_status := null;
2754 x_msg_count := null;
2755 x_msg_data := null;
2756 cpoint_rec.contact_point_type := 'WEB';
2757 cpoint_rec.status := 'A';
2758 cpoint_rec.owner_table_name := 'HZ_PARTIES';
2759 cpoint_rec.owner_table_id := x_per_party_id;
2760 web_rec.url := x_url;
2761 web_rec.web_type := 'com';
2762
2763 l_url_exists := NULL;
2764 open url_exists(x_per_party_id);
2765 fetch url_exists into l_url_exists;
2766 close url_exists;
2767 if l_url_exists is not null then
2768 l_url_id := NULL;
2769 open c_url_id(x_per_party_id);
2770 fetch c_url_id into l_url_id;
2771 close c_url_id;
2772 end if;
2773 if l_url_exists is NULL then
2774 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_url_exists is NULL)) then
2775 AMS_ListImport_PVT.create_contact_point(
2776 cpoint_rec,
2777 edi_rec,
2778 email_rec,
2779 fax_rec,
2780 telex_rec,
2781 web_rec,
2782 x_return_status,
2783 x_msg_count,
2784 x_msg_data,
2785 x_contact_point_id);
2786 l_url_id := x_contact_point_id;
2787 end if;
2788 if x_return_status <> 'S' then
2789 p_component_name := 'WEB';
2790 return;
2791 end if;
2792 end if; -- l_phone_exists is NULL then
2793 end if; -- x_phone_number
2794
2795 -- Create contact points Email
2796
2797 if x_email_address is not NULL and x_per_party_id is not null then
2798 -- SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
2799
2800 x_return_status := null;
2801 x_msg_count := null;
2802 x_msg_data := null;
2803 -- cpoint_rec.contact_point_id := x_contact_point_id;
2804 cpoint_rec.contact_point_type := 'EMAIL';
2805 cpoint_rec.status := 'A';
2806 cpoint_rec.owner_table_name := 'HZ_PARTIES';
2807 cpoint_rec.owner_table_id := x_per_party_id;
2808 -- cpoint_rec.orig_system_reference := x_contact_point_id;
2809 email_rec.email_address := x_email_address;
2810 l_email_exists := NULL;
2811 open email_exists(x_per_party_id);
2812 fetch email_exists into l_email_exists;
2813 close email_exists;
2814 if l_email_exists is NULL then
2815 if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
2816 AMS_ListImport_PVT.create_contact_point(
2817 cpoint_rec,
2818 edi_rec,
2819 email_rec,
2820 phone_rec,
2821 telex_rec,
2822 web_rec,
2823 x_return_status,
2824 x_msg_count,
2825 x_msg_data,
2826 x_contact_point_id);
2827 end if;
2828 if x_return_status <> 'S' then
2829 p_component_name := 'EMAIL';
2830 return;
2831 end if;
2832 end if; -- l_email_exists is NULL then
2833 end if; -- x_email_address
2834 /*
2835 if ((x_phone_number is not NULL OR x_email_address is not NULL ) and l_rec_update_flag = 'Y' and x_per_party_id is not null) then
2836 phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
2837 phone_rec.phone_number := x_phone_number;
2838 phone_rec.phone_country_code := x_phone_country_code;
2839 phone_rec.phone_area_code := x_phone_area_code;
2840 phone_rec.phone_extension := x_phone_extention;
2841
2842 l_phone_id := NULL;
2843 open c_phone_id(x_per_party_id,phone_rec.phone_line_type);
2844 fetch c_phone_id into l_phone_id;
2845 close c_phone_id;
2846 if l_phone_id is not null then
2847 x_contact_point_id := l_phone_id;
2848 end if;
2849
2850 email_rec.email_address := x_email_address;
2851 l_email_id := NULL;
2852 open c_email_id(x_per_party_id);
2853 fetch c_email_id into l_email_id;
2854 close c_email_id;
2855 if l_email_id is not null then
2856 x_contact_point_id := l_email_id;
2857 end if;
2858 cpoint_rec.contact_point_id := x_contact_point_id;
2859 select OBJECT_VERSION_NUMBER into l_cp_obj_number
2860 from hz_contact_points
2861 where contact_point_id = x_contact_point_id;
2862 hz_contact_point_v2pub.update_contact_point(
2863 'F',
2864 cpoint_rec,
2865 edi_rec,
2866 email_rec,
2867 phone_rec,
2868 telex_rec,
2869 web_rec,
2870 l_cp_obj_number,
2871 x_return_status,
2872 x_msg_count,
2873 x_msg_data);
2874 if x_return_status <> 'S' then
2875 return;
2876 end if;
2877 end if;
2878 */
2879 end if; -- if x_rented_list_flag <> 'R'
2880
2881 -- Updates the marketing tables with the party_id
2882
2883 if l_is_party_mapped is NULL then
2884 l_enabled_flag := 'Y';
2885 end if;
2886
2887 if l_is_party_mapped = 'Y' and l_b2c_party_exists = 'Y' then
2888 l_overlay := 'Y';
2889 l_enabled_flag := 'Y';
2890 end if;
2891
2892 if l_is_party_mapped = 'Y' and l_b2c_party_exists is NULL then
2893 l_enabled_flag := 'N';
2894 end if;
2895
2896 if x_per_party_id is not null then
2897 p_party_id := x_per_party_id;
2898 end if;
2899
2900
2901 if x_per_party_id is not null and p_import_list_header_id is not null then
2902 UPDATE ams_imp_source_lines
2903 SET party_id = x_per_party_id,
2904 load_status = 'SUCCESS',
2905 contact_point_id = l_phone_id,
2906 location_id = x_location_id,
2907 enabled_flag = l_enabled_flag
2908 -- sranka 1/14/2003
2909 -- WHERE import_source_line_id = person_rec.party_rec.orig_system_reference // original
2910 WHERE import_source_line_id = l_import_source_line_id
2911 AND import_list_header_id = p_import_list_header_id;
2912 x_return_status := 'S';
2913 open b2cxml;
2914 fetch b2cxml into l_xml_element_id;
2915 close b2cxml;
2916 if l_xml_element_id is not null then
2917 process_element_success( p_import_list_header_id, l_xml_element_id) ;
2918 end if;
2919 end if;
2920 if x_per_party_id is not null and p_import_list_header_id is not null then
2921 create_party_source (p_import_list_header_id, i_import_source_line_id,l_overlay);
2922 end if;
2923
2924
2925 end if; -- x_b2b = 'N'
2926
2927 EXCEPTION
2928
2929 WHEN FND_API.g_exc_error THEN
2930 ROLLBACK TO Create_Customer_pub;
2931 x_return_status := FND_API.g_ret_sts_error;
2932 FND_MSG_PUB.count_and_get(
2933 p_encoded => FND_API.g_false,
2934 p_count => x_msg_count,
2935 p_data => x_msg_data
2936 );
2937
2938 WHEN FND_API.g_exc_unexpected_error THEN
2939 -- ROLLBACK TO Create_Customer_pub;
2940 x_return_status := FND_API.g_ret_sts_unexp_error ;
2941 FND_MSG_PUB.count_and_get(
2942 p_encoded => FND_API.g_false,
2943 p_count => x_msg_count,
2944 p_data => x_msg_data
2945 );
2946
2947
2948 WHEN OTHERS THEN
2949 -- ROLLBACK TO Create_Customer_pub;
2950 x_return_status := FND_API.g_ret_sts_unexp_error ;
2951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2952 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2953 END IF;
2954
2955 FND_MSG_PUB.count_and_get(
2956 p_encoded => FND_API.g_false,
2957 p_count => x_msg_count,
2958 p_data => x_msg_data
2959 );
2960
2961 end Create_Customer;
2962 -- ***************************************************************
2963 --
2964 --
2965 PROCEDURE party_echeck(
2966 p_party_id IN OUT NOCOPY NUMBER,
2967 x_return_status OUT NOCOPY VARCHAR2,
2968 x_msg_count OUT NOCOPY NUMBER,
2969 x_msg_data OUT NOCOPY VARCHAR2,
2970 p_org_name IN VARCHAR2,
2971 p_per_first_name IN VARCHAR2,
2972 p_per_last_name IN VARCHAR2,
2973 p_address1 IN VARCHAR2,
2974 p_country IN VARCHAR2,
2975 -- sranka 1/13/2003 added p_orig_system_reference
2976 p_orig_system_reference IN VARCHAR2
2977 ) IS
2978
2979
2980
2981 l_party_key varchar2(1000);
2982 L_COUNT number;
2983 l_max_party_id number;
2984 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
2985 x_org_party_id number;
2986 l_ps_party_id number;
2987 l_cust_exists varchar2(1);
2988 l_ret_status varchar(1);
2989 x_per_party_id number;
2990 x_party_id number;
2991
2992 cursor c_address_country is
2993 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
2994 hz_parties party
2995 where psite.location_id = loc.location_id
2996 and loc.address1 = p_address1
2997 and loc.country = p_country
2998 and party.customer_key = l_party_key
2999 and party.party_type = 'ORGANIZATION'
3000 and party.status = 'A'
3001 and psite.party_id = party.party_id;
3002
3003 -- sranka 1/13/2003 created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3004
3005 cursor c_address_country_with_osr is
3006 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3007 hz_parties party
3008 where psite.location_id = loc.location_id
3009 and loc.address1 = p_address1
3010 and loc.country = p_country
3011 and party.customer_key = l_party_key
3012 and party.party_type = 'ORGANIZATION'
3013 and party.status = 'A'
3014 and psite.party_id = party.party_id
3015 and party.orig_system_reference = p_orig_system_reference;
3016
3017 cursor c_country is
3018 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3019 hz_parties party
3020 where psite.location_id = loc.location_id
3021 and loc.country = p_country
3022 and party.customer_key = l_party_key
3023 and party.party_type = 'ORGANIZATION'
3024 and party.status = 'A'
3025 and psite.party_id = party.party_id;
3026
3027 -- sranka 1/13/2003
3028 -- created new cursor c_country_with_osr for including the orig_system_reference for duplication check
3029
3030 cursor c_country_with_osr is
3031 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3032 hz_parties party
3033 where psite.location_id = loc.location_id
3034 and loc.country = p_country
3035 and party.customer_key = l_party_key
3036 and party.party_type = 'ORGANIZATION'
3037 and party.status = 'A'
3038 and psite.party_id = party.party_id
3039 and party.orig_system_reference = p_orig_system_reference;
3040
3041
3042 cursor c_customer_exists is
3043 select 'Y' from hz_parties
3044 where customer_key = l_party_key
3045 and status = 'A'
3046 and party_type = 'ORGANIZATION';
3047
3048 -- sranka 1/13/2003
3049 -- created new cursor c_customer_exists_with_osr for including the orig_system_reference for duplication check
3050 cursor c_customer_exists_with_osr is
3051 select 'Y' from hz_parties
3052 where customer_key = l_party_key
3053 and party_type = 'ORGANIZATION'
3054 and status = 'A'
3055 AND orig_system_reference = p_orig_system_reference;
3056
3057
3058 cursor c_max_party is
3059 select max(party_id) from hz_parties
3060 where customer_key = l_party_key
3061 and status = 'A'
3062 and party_type = 'ORGANIZATION';
3063
3064 -- sranka 1/13/2003
3065 -- created new cursor c_max_party_with_osr for including the orig_system_reference for duplication check
3066 cursor c_max_party_with_osr is
3067 select max(party_id) from hz_parties
3068 where customer_key = l_party_key
3069 and status = 'A'
3070 and party_type = 'ORGANIZATION'
3071 and orig_system_reference = p_orig_system_reference;
3072
3073
3074 begin
3075
3076 --
3077 -- Generates the customer key for ORGANIZATION
3078 --
3079 x_return_status := FND_API.g_ret_sts_success;
3080 if p_org_name is not null then
3081 l_party_key := hz_fuzzy_pub.Generate_Key (
3082 p_key_type => 'ORGANIZATION',
3083 p_party_name => p_org_name
3084 );
3085 -- sranka 1/13/2003
3086 -- Added the condition on based on the value of p_orig_system_reference,
3087 -- if p_orig_system_reference is NULL then the duplication will not be done based on the "p_orig_system_reference"
3088 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the duplication check.
3089
3090 IF p_orig_system_reference IS NULL then
3091 open c_customer_exists;
3092 fetch c_customer_exists into l_cust_exists;
3093 close c_customer_exists;
3094 else
3095 open c_customer_exists_with_osr;
3096 fetch c_customer_exists_with_osr into l_cust_exists;
3097 close c_customer_exists_with_osr;
3098 IF l_cust_exists IS NULL then
3099 open c_customer_exists;
3100 fetch c_customer_exists into l_cust_exists;
3101 close c_customer_exists;
3102 END if;
3103 END if;
3104 end if;
3105 --
3106 -- If customer does not exists then it's a new customer.
3107 --
3108 if l_cust_exists is NULL then
3109 return;
3110 end if;
3111
3112 --
3113 -- When address1 and country is provided
3114 --
3115
3116 -- sranka 1/13/2003
3117 -- Added the condition on based on the value of p_orig_system_reference,
3118 -- if p_orig_system_reference is NULL then the duplication will not be done based on the "p_orig_system_reference"
3119 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the duplication check.
3120
3121 if l_cust_exists = 'Y' and p_address1 is not null and p_country is not null then
3122
3123 IF p_orig_system_reference IS NULL then
3124 open c_address_country;
3125 fetch c_address_country into l_ps_party_id;
3126 close c_address_country;
3127 else
3128
3129 -- sranka 1/14/2003
3130 -- here we will check the existance with the "p_orig_system_reference", if the val returned is NULL
3131 -- than we will do the existanve checking with out the "p_orig_system_reference""
3132
3133 open c_address_country_with_osr;
3134 fetch c_address_country_with_osr into l_ps_party_id;
3135 close c_address_country_with_osr;
3136
3137 IF l_ps_party_id IS NULL then
3138 open c_address_country;
3139 fetch c_address_country into l_ps_party_id;
3140 close c_address_country;
3141 END IF;
3142 END IF;
3143
3144 -- if party site not found for this address and country then serch for only country
3145
3146
3147 if l_ps_party_id is NULL then
3148
3149 -- sranka 1/13/2003,
3150 -- added this check condition for the country duplication check.
3151 -- if p_orig_system_reference is NULL then the duplication will not be done based on the "p_orig_system_reference"
3152 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the duplication check.
3153
3154 IF p_orig_system_reference IS NULL then
3155 open c_country;
3156 fetch c_country into l_ps_party_id;
3157 close c_country;
3158 else
3159
3160 open c_country_with_osr;
3161 fetch c_country_with_osr into l_ps_party_id;
3162 close c_country_with_osr;
3163
3164 IF l_ps_party_id IS NULL then
3165 open c_address_country;
3166 fetch c_address_country into l_ps_party_id;
3167 close c_address_country;
3168 END IF;
3169
3170 END IF;
3171 end if;
3172 if l_ps_party_id is not NULL then
3173 p_party_id := l_ps_party_id;
3174 return;
3175 end if;
3176 end if;
3177
3178 --
3179 -- When customer exists and address1 and country is not provided
3180 -- OR party site does not exists
3181 -- then take the max party_id from the available records.
3182 --
3183 if l_cust_exists = 'Y' and
3184 (
3185 (p_address1 is null and p_country is null)
3186 or (l_ps_party_id is null)
3187 ) then
3188 --
3189 -- For ORGANIZATION get the max party_id
3190 --
3191 if p_org_name is not null then
3192
3193 -- sranka 1/13/2003,
3194 -- if p_orig_system_reference is NULL then the max party will not be done based on the "p_orig_system_reference"
3195 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the max party check.
3196
3197 IF p_orig_system_reference IS NULL then
3198 open c_max_party;
3199 fetch c_max_party into x_party_id;
3200 close c_max_party;
3201 else
3202 open c_max_party_with_osr;
3203 fetch c_max_party_with_osr into x_party_id;
3204 close c_max_party_with_osr;
3205
3206 IF x_party_id IS NULL THEN
3207 open c_max_party;
3208 fetch c_max_party into x_party_id;
3209 close c_max_party;
3210 END IF;
3211
3212 END if;
3213
3214 p_party_id := x_party_id;
3215 end if;
3216 end if;
3217
3218 --
3219 -- For PERSON get the max party_id
3220 --
3221 if p_per_last_name is not null and p_per_first_name is not null then
3222 L_COUNT := 0;
3223 l_max_party_id := 0;
3224 hz_fuzzy_pub.FUZZY_SEARCH_PARTY(
3225 'PERSON',
3226 null,
3227 p_per_first_name,
3228 p_per_last_name,
3229 l_party_tbl,
3230 L_COUNT);
3231 if L_COUNT > 0 then
3232 for i in 1..l_count loop
3233 if l_party_tbl(i) > l_max_party_id then
3234 l_max_party_id := l_party_tbl(i);
3235 end if;
3236 end loop;
3237 x_per_party_id := l_max_party_id;
3238 p_party_id := x_per_party_id;
3239 end if;
3240 end if;
3241
3242 exception
3243 when others then
3244 x_return_status := FND_API.g_ret_sts_unexp_error ;
3245 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3246 FND_MSG_PUB.add_exc_msg(g_pkg_name,'party_echeck');
3247 END IF;
3248 FND_MSG_PUB.count_and_get(
3249 p_encoded => FND_API.g_false,
3250 p_count => x_msg_count,
3251 p_data => x_msg_data
3252 );
3253 end party_echeck;
3254
3255 -- ---------------------------------------------
3256 --
3257 --
3258
3259 procedure error_capture (
3260 p_api_version IN NUMBER,
3261 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3262 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3263 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3264 x_return_status OUT NOCOPY VARCHAR2,
3265 x_msg_count OUT NOCOPY NUMBER,
3266 x_msg_data OUT NOCOPY VARCHAR2,
3267 p_import_list_header_id IN NUMBER,
3268 p_import_source_line_id IN NUMBER,
3269 p_imp_xml_element_id IN NUMBER,
3270 p_imp_xml_attribute_id IN NUMBER,
3271 p_component_name IN VARCHAR2,
3272 p_field_name IN VARCHAR2,
3273 p_error_text IN VARCHAR2
3274 ) IS
3275
3276 l_error_exist varchar2(1);
3277 l_imp_type varchar2(60);
3278 l_file_type varchar2(60);
3279 l_batch_id NUMBER;
3280 l_return_status VARCHAR2(1);
3281 l_imp_xml_element_id NUMBER;
3282 l_org_imp_xml_element_id NUMBER;
3283 l_per_imp_xml_element_id NUMBER;
3284 l_add_imp_xml_element_id NUMBER;
3285 l_ocont_imp_xml_element_id NUMBER;
3286 l_cp_imp_xml_element_id NUMBER;
3287 l_em_imp_xml_element_id NUMBER;
3288 L_ERROR_THRESHOLD number;
3289 l_lookup_code varchar2(60);
3290 l_user_status_id number;
3291
3292
3293 cursor c_imp_type is
3294 select import_type, batch_id, nvl(ERROR_THRESHOLD,0)
3295 from ams_imp_list_headers_all
3296 where import_list_header_id = p_import_list_header_id;
3297
3298 cursor c_error_exists is
3299 select 'Y'
3300 from ams_list_import_errors
3301 where import_list_header_id = p_import_list_header_id
3302 and import_source_line_id = p_import_source_line_id
3303 and batch_id = l_batch_id;
3304
3305 cursor c_file_type is
3306 select file_type from ams_imp_documents where import_list_header_id = p_import_list_header_id;
3307
3308 cursor c_b2b is
3309 select org_imp_xml_element_id, add_imp_xml_element_id, ocont_imp_xml_element_id, cp_imp_xml_element_id,
3310 em_imp_xml_element_id from ams_hz_b2b_mapping_v where import_list_header_id = p_import_list_header_id
3311 and import_source_line_id = p_import_source_line_id;
3312
3313
3314 cursor c_b2c is
3315 select per_imp_xml_element_id, add_imp_xml_element_id, cp_imp_xml_element_id, em_imp_xml_element_id
3316 from ams_hz_b2c_mapping_v where import_list_header_id = p_import_list_header_id
3317 and import_source_line_id = p_import_source_line_id;
3318
3319
3320 begin
3321
3322 SAVEPOINT error_capture_pub;
3323 -- initialize the message list;
3324 IF FND_API.to_boolean(p_init_msg_list) THEN
3325 FND_MSG_PUB.initialize;
3326 END IF;
3327 if nvl(p_component_name,'X') <> 'EVENTSUB' then
3328 open c_imp_type;
3329 fetch c_imp_type into l_imp_type, l_batch_id, L_ERROR_THRESHOLD;
3330 close c_imp_type;
3331 if p_import_source_line_id is not null then
3332 update ams_imp_source_lines
3333 set load_status = 'ERROR' , ENABLED_FLAG = null
3334 where import_list_header_id = p_import_list_header_id
3335 and import_source_line_id = p_import_source_line_id;
3336
3337 open c_error_exists;
3338 fetch c_error_exists into l_error_exist;
3339 close c_error_exists;
3340
3341 open c_file_type;
3342 fetch c_file_type into l_file_type;
3343 close c_file_type;
3344
3345 if l_error_exist = 'Y' then
3346 update ams_list_import_errors
3347 set col1 = substr(col1||','||p_error_text,1,4000)
3348 where import_list_header_id = p_import_list_header_id
3349 and import_source_line_id = p_import_source_line_id
3350 and batch_id = l_batch_id;
3351 end if;
3352
3353 if l_error_exist is null then
3354 INSERT INTO ams_list_import_errors
3355 (
3356 LIST_IMPORT_ERROR_ID,
3357 LAST_UPDATED_BY,
3358 LAST_UPDATE_DATE,
3359 CREATION_DATE,
3360 CREATED_BY,
3361 LAST_UPDATE_LOGIN,
3362 IMPORT_SOURCE_LINE_ID,
3363 IMPORT_LIST_HEADER_ID,
3364 IMPORT_TYPE,
3365 ERROR_TYPE,
3366 BATCH_ID,
3367 col1
3368 )
3369 VALUES
3370 (
3371 ams_list_import_errors_s.nextval, -- LIST_IMPORT_ERROR_ID,
3372 FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
3373 SYSDATE, -- LAST_UPDATE_DATE,
3374 SYSDATE, -- CREATION_DATE,
3375 FND_GLOBAL.User_ID, -- CREATED_BY,
3376 FND_GLOBAL.Conc_Login_ID, -- LAST_UPDATE_LOGIN,
3377 p_import_source_line_id,
3378 p_import_list_header_id,
3379 l_imp_type, -- IMPORT_TYPE,
3380 'E', -- ERROR_TYPE,
3381 l_batch_id,
3382 substr(p_error_text,1,4000)
3383 );
3384 end if;
3385 end if; -- p_import_source_line_id is not null then
3386
3387 if l_file_type = 'XML' then
3388 if p_component_name in ('ORGANIZATION','PERSON','ADDRESS','CONTACT','PHONE','EMAIL') THEN
3389 if l_imp_type = 'B2B' then
3390 open c_b2b;
3391 fetch c_b2b into l_org_imp_xml_element_id , l_add_imp_xml_element_id , l_ocont_imp_xml_element_id ,
3392 l_cp_imp_xml_element_id , l_em_imp_xml_element_id;
3393 close c_b2b;
3394 if p_component_name = 'ORGANIZATION' then
3395 l_imp_xml_element_id := l_org_imp_xml_element_id;
3396 end if;
3397 if p_component_name = 'CONTACT' then
3398 l_imp_xml_element_id := l_ocont_imp_xml_element_id;
3399 end if;
3400 if p_component_name = 'ADDRESS' then
3401 l_imp_xml_element_id := l_add_imp_xml_element_id;
3402 end if;
3403 if p_component_name = 'PERSON' then
3404 l_imp_xml_element_id := l_ocont_imp_xml_element_id;
3405 end if;
3406 if p_component_name = 'PHONE' then
3407 l_imp_xml_element_id := l_cp_imp_xml_element_id;
3408 end if;
3409 if p_component_name = 'EMAIL' then
3410 l_imp_xml_element_id := l_em_imp_xml_element_id;
3411 end if;
3412 end if;
3413
3414 if l_imp_type = 'B2C' then
3415 open c_b2c;
3416 fetch c_b2c into l_per_imp_xml_element_id , l_add_imp_xml_element_id ,
3417 l_cp_imp_xml_element_id , l_em_imp_xml_element_id;
3418 close c_b2c;
3419 if p_component_name = 'ADDRESS' then
3420 l_imp_xml_element_id := l_add_imp_xml_element_id;
3421 end if;
3422 if p_component_name = 'PERSON' then
3423 l_imp_xml_element_id := l_per_imp_xml_element_id;
3424 end if;
3425 if p_component_name = 'PHONE' then
3426 l_imp_xml_element_id := l_cp_imp_xml_element_id;
3427 end if;
3428 if p_component_name = 'EMAIL' then
3429 l_imp_xml_element_id := l_em_imp_xml_element_id;
3430 end if;
3431 end if;
3432
3433 end if;
3434 if p_field_name is null then
3435 update AMS_IMP_XML_ELEMENTS
3436 set ERROR_TEXT = substr(p_error_text,1,2000),
3437 LOAD_STATUS = 'ERROR'
3438 where imp_xml_element_id = l_imp_xml_element_id;
3439 else
3440 AMS_ListImport_PVT.update_element_error (
3441 p_import_list_header_id,l_imp_xml_element_id,
3442 p_field_name,upper(p_field_name)||' :'||substr(p_error_text,1,2000));
3443 end if;
3444 end if; -- l_file_type = 'XML'
3445
3446 if L_ERROR_THRESHOLD > 0 then
3447 G_ERROR_THRESHOLD := G_ERROR_THRESHOLD + 1;
3448 if G_ERROR_THRESHOLD >= L_ERROR_THRESHOLD then
3449 -- ndadwal added if cond for bug 4966524
3450 if p_import_list_header_id is NOT NULL then
3451 AMS_Utility_PVT.Create_Log (
3452 x_return_status => l_return_status,
3453 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
3454 p_log_used_by_id => p_import_list_header_id,
3455 p_msg_data => 'Import process is stoped because of Error Threshold has been reached .',
3456 p_msg_type => 'DEBUG'
3457 );
3458 end if;
3459 l_lookup_code := 'ERROR';
3460 l_user_status_id := null;
3461 SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
3462 WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
3463 system_status_code = 'ERROR' and default_flag = 'Y';
3464
3465 UPDATE ams_imp_list_headers_all
3466 set status_code = l_lookup_code,
3467 user_status_id = l_user_status_id,
3468 status_date = sysdate
3469 where import_list_header_id = p_import_list_header_id;
3470 x_return_status := 'E';
3471 x_msg_count := 1;
3472 x_msg_data := 'Threshold';
3473 return;
3474
3475 end if;
3476 end if;
3477
3478 end if; -- --if p_component_name <> 'EVENTSUB' then
3479
3480 if p_component_name = 'EVENTSUB' then
3481 if p_import_source_line_id is not null then
3482 update ams_imp_source_lines
3483 set load_status = 'ERROR' , ENABLED_FLAG = null
3484 where import_list_header_id = p_import_list_header_id
3485 and import_source_line_id = p_import_source_line_id;
3486 end if;
3487 l_error_exist := NULL;
3488 open c_error_exists;
3489 fetch c_error_exists into l_error_exist;
3490 close c_error_exists;
3491 if l_error_exist = 'Y' then
3492 update ams_list_import_errors
3493 set col350 = substr(col350||','||p_error_text,1,4000)
3494 where import_list_header_id = p_import_list_header_id
3495 and import_source_line_id = p_import_source_line_id;
3496 end if;
3497 if l_error_exist is null then
3498 INSERT INTO ams_list_import_errors
3499 (
3500 LIST_IMPORT_ERROR_ID,
3501 LAST_UPDATED_BY,
3502 LAST_UPDATE_DATE,
3503 CREATION_DATE,
3504 CREATED_BY,
3505 LAST_UPDATE_LOGIN,
3506 IMPORT_SOURCE_LINE_ID,
3507 IMPORT_LIST_HEADER_ID,
3508 IMPORT_TYPE,
3509 ERROR_TYPE,
3510 BATCH_ID,
3511 col1
3512 )
3513 VALUES
3514 (
3515 ams_list_import_errors_s.nextval, -- LIST_IMPORT_ERROR_ID,
3516 FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
3517 SYSDATE, -- LAST_UPDATE_DATE,
3518 SYSDATE, -- CREATION_DATE,
3519 FND_GLOBAL.User_ID, -- CREATED_BY,
3520 FND_GLOBAL.Conc_Login_ID, -- LAST_UPDATE_LOGIN,
3521 p_import_source_line_id,
3522 p_import_list_header_id,
3523 l_imp_type, -- IMPORT_TYPE,
3524 'E', -- ERROR_TYPE,
3525 l_batch_id,
3526 substr(p_error_text,1,4000)
3527 );
3528 end if;
3529
3530 end if;
3531
3532
3533 IF FND_API.to_boolean(p_commit) THEN
3534 COMMIT;
3535 END IF;
3536 x_return_status := FND_API.g_ret_sts_success;
3537
3538 EXCEPTION
3539
3540 WHEN FND_API.g_exc_error THEN
3541 ROLLBACK TO error_capture_pub;
3542 x_return_status := FND_API.g_ret_sts_error;
3543 FND_MSG_PUB.count_and_get(
3544 p_encoded => FND_API.g_false,
3545 p_count => x_msg_count,
3546 p_data => x_msg_data
3547 );
3548
3549 WHEN FND_API.g_exc_unexpected_error THEN
3550 ROLLBACK TO error_capture_pub;
3551 x_return_status := FND_API.g_ret_sts_unexp_error ;
3552 FND_MSG_PUB.count_and_get(
3553 p_encoded => FND_API.g_false,
3554 p_count => x_msg_count,
3555 p_data => x_msg_data
3556 );
3557
3558 WHEN OTHERS THEN
3559 -- ROLLBACK TO error_capture_pub;
3560 x_return_status := FND_API.g_ret_sts_unexp_error ;
3561
3562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3563 FND_MSG_PUB.add_exc_msg(g_pkg_name, 'error_capture');
3564 END IF;
3565
3566 FND_MSG_PUB.count_and_get(
3567 p_encoded => FND_API.g_false,
3568 p_count => x_msg_count,
3569 p_data => x_msg_data
3570 );
3571
3572 end error_capture;
3573 -- ---------------------------------
3574
3575 PROCEDURE contact_echeck(
3576 p_party_id IN OUT NOCOPY NUMBER,
3577 x_return_status OUT NOCOPY VARCHAR2,
3578 x_msg_count OUT NOCOPY NUMBER,
3579 x_msg_data OUT NOCOPY VARCHAR2,
3580 p_org_party_id IN NUMBER,
3581 p_per_first_name IN VARCHAR2,
3582 p_per_last_name IN VARCHAR2,
3583 p_phone_area_code IN VARCHAR2,
3584 p_phone_number IN VARCHAR2,
3585 p_phone_extension IN VARCHAR2,
3586 p_email_address IN VARCHAR2,
3587 -- sranka 1/14/2003
3588 -- added "p_orig_system_reference" for supporting the population of "orig_system_reference"
3589 -- from CSV file
3590 p_orig_system_reference IN VARCHAR2,
3591 -- sranka 3/21/2003
3592 -- made changes for supporting EMPLOYEE_OF" relationship
3593 p_relationship_code IN VARCHAR2,
3594 p_relationship_type IN VARCHAR2
3595
3596 ) IS
3597
3598 l_ret_status varchar(1);
3599 x_per_party_id number;
3600 l_party_key varchar(1000);
3601 l_cust_exists varchar(1);
3602 l_email_party_id number;
3603 l_phone_party_id number;
3604 L_COUNT number;
3605 l_max_party_id number;
3606 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3607 l_transposed_phone_no varchar(60);
3608
3609 cursor c_customer_exists is
3610 select 'Y' from hz_parties
3611 where customer_key = l_party_key
3612 and status = 'A'
3613 and party_type = 'PERSON';
3614
3615 -- sranka 1/15/2003
3616 -- created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3617
3618 cursor c_customer_exists_with_osr is
3619 select 'Y' from hz_parties
3620 where customer_key = l_party_key
3621 and party_type = 'PERSON'
3622 and status = 'A'
3623 and orig_system_reference = p_orig_system_reference;
3624
3625
3626 cursor c_cont_email is
3627 select max(per.party_id) from
3628 hz_parties org,
3629 hz_parties per,
3630 hz_relationships rel,
3631 hz_contact_points cpoint
3632 where org.party_id = p_org_party_id
3633 and org.party_type = 'ORGANIZATION'
3634 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3635 and rel.SUBJECT_TYPE = 'PERSON'
3636 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3637 -- sranka 3/21/2003
3638 -- made changes for supporting EMPLOYEE_OF" relationship
3639 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3640 -- and rel.RELATIONSHIP_CODE = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
3641 -- and rel.RELATIONSHIP_TYPE = NVL(ocon_rec.party_rel_rec.relationship_type,'CONTACT')
3642 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3643 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3644 and rel.OBJECT_ID = org.party_id
3645 and rel.SUBJECT_ID = per.PARTY_ID
3646 and per.customer_key = l_party_key
3647 and per.status = 'A'
3648 and cpoint.owner_table_id = rel.party_id
3649 and cpoint.owner_table_name = 'HZ_PARTIES'
3650 and cpoint.contact_point_type = 'EMAIL'
3651 and upper(cpoint.email_address) = upper(p_email_address)
3652 and cpoint.status = 'A';
3653
3654 -- sranka 1/15/2003 created new cursor c_cont_email_with_osr for including the orig_system_reference for duplication check
3655
3656 cursor c_cont_email_with_osr is
3657 select max(per.party_id) from
3658 hz_parties org,
3659 hz_parties per,
3660 hz_relationships rel,
3661 hz_contact_points cpoint
3662 where org.party_id = p_org_party_id
3663 and org.party_type = 'ORGANIZATION'
3664 and org.orig_system_reference = p_orig_system_reference
3665 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3666 and rel.SUBJECT_TYPE = 'PERSON'
3667 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3668 -- sranka 3/21/2003
3669 -- made changes for supporting EMPLOYEE_OF" relationship
3670 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3671 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3672 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3673 and rel.OBJECT_ID = org.party_id
3674 and rel.SUBJECT_ID = per.PARTY_ID
3675 and per.customer_key = l_party_key
3676 and per.status = 'A'
3677 and cpoint.owner_table_id = rel.party_id
3678 and cpoint.owner_table_name = 'HZ_PARTIES'
3679 and cpoint.contact_point_type = 'EMAIL'
3680 and upper(cpoint.email_address) = upper(p_email_address)
3681 and cpoint.status = 'A';
3682
3683
3684 cursor c_cont_email_phone is
3685 select max(per.party_id) from
3686 hz_parties org,
3687 hz_parties per,
3688 hz_relationships rel,
3689 hz_contact_points cpoint,
3690 hz_contact_points cpoint1
3691 where org.party_id = p_org_party_id
3692 and org.party_type = 'ORGANIZATION'
3693 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3694 and rel.SUBJECT_TYPE = 'PERSON'
3695 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3696 -- sranka 3/21/2003
3697 -- made changes for supporting EMPLOYEE_OF" relationship
3698 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3699 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3700 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3701 and rel.OBJECT_ID = org.party_id
3702 and rel.SUBJECT_ID = per.PARTY_ID
3703 and per.customer_key = l_party_key
3704 and per.status = 'A'
3705 and cpoint.owner_table_id = rel.party_id
3706 and cpoint.owner_table_name = 'HZ_PARTIES'
3707 and cpoint.contact_point_type = 'EMAIL'
3708 and upper(cpoint.email_address) = upper(p_email_address)
3709 and cpoint.status = 'A'
3710 and cpoint1.owner_table_id = rel.party_id
3711 and cpoint1.owner_table_name = 'HZ_PARTIES'
3712 and cpoint1.contact_point_type = 'PHONE'
3713 and cpoint1.transposed_phone_number = l_transposed_phone_no
3714 -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3715 -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3716 and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
3717 and cpoint1.status = 'A';
3718
3719 -- sranka 1/15/2003 created new cursor c_cont_email_phone_with_osr for including the orig_system_reference for duplication check
3720
3721 cursor c_cont_email_phone_with_osr is
3722 select max(per.party_id) from
3723 hz_parties org,
3724 hz_parties per,
3725 hz_relationships rel,
3726 hz_contact_points cpoint,
3727 hz_contact_points cpoint1
3728 where org.party_id = p_org_party_id
3729 and org.party_type = 'ORGANIZATION'
3730 and org.orig_system_reference = p_orig_system_reference
3731 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3732 and rel.SUBJECT_TYPE = 'PERSON'
3733 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3734 -- sranka 3/21/2003
3735 -- made changes for supporting EMPLOYEE_OF" relationship
3736 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3737 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3738 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3739 and rel.OBJECT_ID = org.party_id
3740 and rel.SUBJECT_ID = per.PARTY_ID
3741 and per.customer_key = l_party_key
3742 and per.status = 'A'
3743 and cpoint.owner_table_id = rel.party_id
3744 and cpoint.owner_table_name = 'HZ_PARTIES'
3745 and cpoint.contact_point_type = 'EMAIL'
3746 and upper(cpoint.email_address) = upper(p_email_address)
3747 and cpoint.status = 'A'
3748 and cpoint1.owner_table_id = rel.party_id
3749 and cpoint1.owner_table_name = 'HZ_PARTIES'
3750 and cpoint1.contact_point_type = 'PHONE'
3751 and cpoint1.transposed_phone_number = l_transposed_phone_no
3752 -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3753 -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3754 and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
3755 and cpoint1.status = 'A';
3756
3757
3758 cursor c_cont_phone is
3759 select max(per.party_id) from
3760 hz_parties org,
3761 hz_parties per,
3762 hz_relationships rel,
3763 hz_contact_points cpoint
3764 where org.party_id = p_org_party_id
3765 and org.party_type = 'ORGANIZATION'
3766 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3767 and rel.SUBJECT_TYPE = 'PERSON'
3768 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3769 -- sranka 3/21/2003
3770 -- made changes for supporting EMPLOYEE_OF" relationship
3771 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3772 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3773 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3774 and rel.OBJECT_ID = org.party_id
3775 and rel.SUBJECT_ID = per.PARTY_ID
3776 and per.customer_key = l_party_key
3777 and per.status = 'A'
3778 and cpoint.owner_table_id = rel.party_id
3779 and cpoint.owner_table_name = 'HZ_PARTIES'
3780 and cpoint.contact_point_type = 'PHONE'
3781 and cpoint.transposed_phone_number = l_transposed_phone_no
3782 -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3783 -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3784 and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
3785 and cpoint.status = 'A';
3786
3787 -- sranka 1/15/2003 created new cursor c_cont_phone_with_osr for including the orig_system_reference for duplication check
3788
3789
3790 cursor c_cont_phone_with_osr is
3791 select max(per.party_id) from
3792 hz_parties org,
3793 hz_parties per,
3794 hz_relationships rel,
3795 hz_contact_points cpoint
3796 where org.party_id = p_org_party_id
3797 and org.party_type = 'ORGANIZATION'
3798 and org.orig_system_reference = p_orig_system_reference
3799 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3800 and rel.SUBJECT_TYPE = 'PERSON'
3801 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3802 -- sranka 3/21/2003
3803 -- made changes for supporting EMPLOYEE_OF" relationship
3804 -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3805 and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3806 and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
3807 and rel.OBJECT_ID = org.party_id
3808 and rel.SUBJECT_ID = per.PARTY_ID
3809 and per.customer_key = l_party_key
3810 and per.status = 'A'
3811 and cpoint.owner_table_id = rel.party_id
3812 and cpoint.owner_table_name = 'HZ_PARTIES'
3813 and cpoint.contact_point_type = 'PHONE'
3814 and cpoint.transposed_phone_number = l_transposed_phone_no
3815 -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3816 -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3817 and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
3818 and cpoint.status = 'A';
3819
3820
3821 begin
3822 x_return_status := FND_API.g_ret_sts_success;
3823 --
3824 -- Generates the customer key for PERSON
3825 --
3826 if p_per_last_name is not null and p_per_first_name is not null then
3827 l_party_key := hz_fuzzy_pub.Generate_Key (
3828 p_key_type => 'PERSON',
3829 p_first_name => p_per_first_name,
3830 p_last_name => p_per_last_name
3831 );
3832 l_transposed_phone_no := hz_phone_number_pkg.transpose(p_phone_area_code||p_phone_number);
3833
3834 end if;
3835 --
3836 -- If customer does not exists then it's a new customer.
3837 --
3838
3839 -- sranka 1/15/2003,
3840 -- if p_orig_system_reference is NULL then the duplication check will not be done based on the "p_orig_system_reference"
3841 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the max party check.
3842
3843 IF p_orig_system_reference IS NULL then
3844 open c_customer_exists;
3845 fetch c_customer_exists into l_cust_exists;
3846 close c_customer_exists;
3847 else
3848 open c_customer_exists_with_osr;
3849 fetch c_customer_exists_with_osr into l_cust_exists;
3850 close c_customer_exists_with_osr;
3851
3852 IF l_cust_exists IS NULL then
3853 open c_customer_exists;
3854 fetch c_customer_exists into l_cust_exists;
3855 close c_customer_exists;
3856 END if;
3857
3858 END if;
3859
3860
3861
3862
3863 if l_cust_exists is NULL then
3864 return; -- ORG CONTACT DOES NOT EXISTS CHECKED WITH CUSTOMER_KEY.
3865 end if;
3866
3867
3868
3869 -- sranka 1/15/2003,
3870 -- if p_orig_system_reference is NULL then the the duplication check will not be done based on the "p_orig_system_reference"
3871 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the max party check.
3872
3873 IF p_orig_system_reference IS NULL then
3874 open c_cont_email_phone;
3875 fetch c_cont_email_phone into x_per_party_id;
3876 close c_cont_email_phone;
3877 else
3878 IF x_per_party_id IS NULL then
3879 open c_cont_email_phone_with_osr;
3880 fetch c_cont_email_phone_with_osr into x_per_party_id;
3881 close c_cont_email_phone_with_osr;
3882 END if;
3883 END if;
3884
3885
3886
3887 if x_per_party_id is not null then
3888 p_party_id := x_per_party_id; -- ORG CONTACT DOES NOT EXISTS WITH EMAIL AND PHONE NUMBER.
3889 return;
3890 end if;
3891 --
3892 -- Either email_address and phone number is available.
3893 --
3894 if l_cust_exists = 'Y' and (p_email_address is not null or p_phone_number is not null) then
3895 -- sranka 1/15/2003,
3896 -- if p_orig_system_reference is NULL then the the duplication check will not be done based on the "p_orig_system_reference"
3897 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the max party check.
3898 IF p_orig_system_reference IS NULL then
3899 open c_cont_email;
3900 fetch c_cont_email into l_email_party_id;
3901 close c_cont_email;
3902 else
3903 open c_cont_email_with_osr;
3904 fetch c_cont_email_with_osr into l_email_party_id;
3905 close c_cont_email_with_osr;
3906 IF l_email_party_id IS NULL then
3907 open c_cont_email;
3908 fetch c_cont_email into l_email_party_id;
3909 close c_cont_email;
3910 END if;
3911 END if;
3912
3913
3914 if l_email_party_id is not null then
3915 p_party_id := l_email_party_id;
3916 return;
3917 end if;
3918
3919 -- sranka 1/15/2003,
3920 -- if p_orig_system_reference is NULL then the the duplication check will not be done based on the "p_orig_system_reference"
3921 -- but if p_orig_system_reference is NOT NULL than "orig_system_reference" will be included for the max party check.
3922
3923 IF p_orig_system_reference IS NULL then
3924 open c_cont_phone;
3925 fetch c_cont_phone into l_phone_party_id;
3926 close c_cont_phone;
3927 else
3928 open c_cont_phone_with_osr;
3929 fetch c_cont_phone_with_osr into l_phone_party_id;
3930 close c_cont_phone_with_osr;
3931
3932 IF l_phone_party_id IS NULL then
3933 open c_cont_phone;
3934 fetch c_cont_phone into l_phone_party_id;
3935 close c_cont_phone;
3936 END if;
3937
3938 END if;
3939
3940 if l_phone_party_id is not null then
3941 p_party_id := l_phone_party_id;
3942 end if;
3943 end if;
3944
3945
3946 exception
3947 when others then
3948 x_return_status := FND_API.g_ret_sts_unexp_error ;
3949 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3950 FND_MSG_PUB.add_exc_msg(g_pkg_name,'contact_echeck');
3951 END IF;
3952 FND_MSG_PUB.count_and_get(
3953 p_encoded => FND_API.g_false,
3954 p_count => x_msg_count,
3955 p_data => x_msg_data
3956 );
3957
3958 end contact_echeck;
3959 -- ----------------------------------------------
3960 PROCEDURE rented_contact_echeck(
3961 p_party_id IN OUT NOCOPY NUMBER,
3962 x_return_status OUT NOCOPY VARCHAR2,
3963 x_msg_count OUT NOCOPY NUMBER,
3964 x_msg_data OUT NOCOPY VARCHAR2,
3965 p_org_party_id IN NUMBER,
3966 p_per_first_name IN VARCHAR2,
3967 p_per_last_name IN VARCHAR2,
3968 p_phone_area_code IN VARCHAR2,
3969 p_phone_number IN VARCHAR2,
3970 p_phone_extension IN VARCHAR2,
3971 p_email_address IN VARCHAR2,
3972 -- sranka 3/21/2003
3973 -- made changes for supporting EMPLOYEE_OF" relationship
3974 p_relationship_code IN VARCHAR2,
3975 p_relationship_type IN VARCHAR2
3976
3977 ) IS
3978
3979 l_ret_status varchar(1);
3980 x_per_party_id number;
3981 l_party_key varchar(1000);
3982 l_cust_exists varchar(1);
3983 l_email_party_id number;
3984 l_phone_party_id number;
3985 L_COUNT number;
3986 l_max_party_id number;
3987 l_rel_party_id number;
3988 l_per_party_id number;
3989 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3990
3991
3992 cursor c_customer_exists is
3993 select MAX(PARTY_ID) from hz_parties
3994 where customer_key = l_party_key
3995 and status = 'A'
3996 and party_type = 'PERSON';
3997
3998 cursor c_rel_party_id is
3999 select max(rel.party_id) from
4000 hz_parties org,
4001 hz_relationships rel
4002 where org.party_id = p_org_party_id
4003 and org.party_type = 'ORGANIZATION'
4004 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4005 and rel.SUBJECT_TYPE = 'PERSON'
4006 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4007 and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
4008 and rel.OBJECT_ID = org.party_id
4009 and rel.SUBJECT_ID = l_per_party_id;
4010
4011
4012
4013 cursor c_cont_email is
4014 SELECT PARTY_ID FROM AMS_PARTY_SOURCES
4015 WHERE PARTY_ID = l_rel_party_id
4016 and upper(COL26) = upper(p_email_address);
4017
4018
4019 cursor c_cont_email_phone is
4020
4021 SELECT PARTY_ID FROM AMS_PARTY_SOURCES
4022 WHERE PARTY_ID = l_rel_party_id
4023 and upper(COL26) = upper(p_email_address)
4024 and COL28 = p_phone_area_code
4025 and COL29 = p_phone_number
4026 and COL30 = p_phone_extension;
4027
4028 cursor c_cont_phone is
4029 SELECT PARTY_ID FROM AMS_PARTY_SOURCES
4030 WHERE PARTY_ID = l_rel_party_id
4031 and COL28 = p_phone_area_code
4032 and COL29 = p_phone_number
4033 and COL30 = p_phone_extension;
4034
4035 begin
4036 x_return_status := FND_API.g_ret_sts_success;
4037 --
4038 -- Generates the customer key for PERSON
4039 --
4040 if p_per_last_name is not null and p_per_first_name is not null then
4041 l_party_key := hz_fuzzy_pub.Generate_Key (
4042 p_key_type => 'PERSON',
4043 p_first_name => p_per_first_name,
4044 p_last_name => p_per_last_name
4045 );
4046 end if;
4047 --
4048 -- If customer does not exists then it's a new customer.
4049 --
4050 open c_customer_exists;
4051 fetch c_customer_exists into l_per_party_id;
4052 close c_customer_exists;
4053 if l_per_party_id is NULL then
4054 return; -- ORG CONTACT DOES NOT EXISTS CHECKED WITH CUSTOMER_KEY.
4055 end if;
4056
4057 -- find the relationship party id
4058 open c_rel_party_id;
4059 fetch c_rel_party_id into l_rel_party_id;
4060 close c_rel_party_id;
4061
4062 open c_cont_email_phone;
4063 fetch c_cont_email_phone into l_max_party_id;
4064 close c_cont_email_phone;
4065
4066 if l_max_party_id is not null then
4067 p_party_id := l_per_party_id; -- ORG CONTACT DOES NOT EXISTS WITH EMAIL AND PHONE NUMBER.
4068 return;
4069 end if;
4070 --
4071 -- Either email_address and phone number is available.
4072 --
4073 if l_max_party_id is not null and (p_email_address is not null or p_phone_number is not null) then
4074 open c_cont_email;
4075 fetch c_cont_email into l_email_party_id;
4076 close c_cont_email;
4077
4078
4079 if l_email_party_id is not null then
4080 p_party_id := l_per_party_id;
4081 return;
4082 end if;
4083
4084 open c_cont_phone;
4085 fetch c_cont_phone into l_phone_party_id;
4086 close c_cont_phone;
4087
4088
4089 if l_phone_party_id is not null then
4090 p_party_id := l_per_party_id;
4091 end if;
4092 end if;
4093
4094
4095 exception
4096 when others then
4097 x_return_status := FND_API.g_ret_sts_unexp_error ;
4098 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4099 FND_MSG_PUB.add_exc_msg(g_pkg_name,'contact_echeck');
4100 END IF;
4101 FND_MSG_PUB.count_and_get(
4102 p_encoded => FND_API.g_false,
4103 p_count => x_msg_count,
4104 p_data => x_msg_data
4105 );
4106
4107 end rented_contact_echeck;
4108 -------------------------------
4109
4110 PROCEDURE create_party_source (
4111 p_import_list_header_id IN NUMBER,
4112 p_import_source_line_id IN NUMBER,
4113 p_overlay IN VARCHAR2
4114 ) IS
4115 l_return_status varchar2(1);
4116 begin
4117 insert into ams_party_sources
4118 (
4119 party_sources_id,
4120 party_id,
4121 IMPORT_SOURCE_LINE_ID,
4122 OBJECT_VERSION_NUMBER,
4123 LAST_UPDATE_DATE,
4124 LAST_UPDATED_BY,
4125 CREATION_DATE,
4126 CREATED_BY,
4127 LAST_UPDATE_LOGIN,
4128 IMPORT_LIST_HEADER_ID,
4129 LIST_SOURCE_TYPE_ID,
4130 USED_FLAG,
4131 OVERLAY_FLAG,
4132 OVERLAY_DATE,
4133 COL1,
4134 COL2,
4135 COL3,
4136 COL4,
4137 COL5,
4138 COL6,
4139 COL7,
4140 COL8,
4141 COL9,
4142 COL10,
4143 COL11,
4144 COL12,
4145 COL13,
4146 COL14,
4147 COL15,
4148 COL16,
4149 COL17,
4150 COL18,
4151 COL19,
4152 COL20,
4153 COL21,
4154 COL22,
4155 COL23,
4156 COL24,
4157 COL25,
4158 COL26,
4159 COL27,
4160 COL28,
4161 COL29,
4162 COL30,
4163 COL31,
4164 COL32,
4165 COL33,
4166 COL34,
4167 COL35,
4168 COL36,
4169 COL37,
4170 COL201,
4171 COL202,
4172 COL203,
4173 COL204,
4174 COL205,
4175 COL206,
4176 COL207,
4177 COL208,
4178 COL209,
4179 COL210,
4180 COL211,
4181 COL212,
4182 COL213,
4183 COL214,
4184 COL215,
4185 COL216,
4186 COL217,
4187 COL218,
4188 COL219,
4189 COL220,
4190 COL221,
4191 COL222,
4192 COL223,
4193 COL224,
4194 COL225,
4195 COL226,
4196 COL227,
4197 COL228,
4198 COL229,
4199 COL230,
4200 COL231,
4201 COL232,
4202 COL233,
4203 COL234,
4204 COL235,
4205 COL236,
4206 COL237,
4207 COL238,
4208 COL239,
4209 COL240,
4210 COL241,
4211 COL242,
4212 COL243,
4213 COL244,
4214 COL245,
4215 COL246,
4216 COL247,
4217 COL248,
4218 COL249,
4219 COL250
4220 )
4221 select
4222 ams_party_sources_s.nextval,
4223 LINE.PARTY_ID,
4224 LINE.IMPORT_SOURCE_LINE_ID,
4225 LINE.OBJECT_VERSION_NUMBER,
4226 LINE.LAST_UPDATE_DATE,
4227 LINE.LAST_UPDATED_BY,
4228 LINE.CREATION_DATE,
4229 LINE.CREATED_BY,
4230 LINE.LAST_UPDATE_LOGIN,
4231 LINE.IMPORT_LIST_HEADER_ID,
4232 header.LIST_SOURCE_TYPE_ID,
4233 'N',
4234 nvl(p_overlay,'N'),
4235 SYSDATE,
4236 LINE.COL1,
4237 LINE.COL2,
4238 LINE.COL3,
4239 LINE.COL4,
4240 LINE.COL5,
4241 LINE.COL6,
4242 LINE.COL7,
4243 LINE.COL8,
4244 LINE.COL9,
4245 LINE.COL10,
4246 LINE.COL11,
4247 LINE.COL12,
4248 LINE.COL13,
4249 LINE.COL14,
4250 LINE.COL15,
4251 LINE.COL16,
4252 LINE.COL17,
4253 LINE.COL18,
4254 LINE.COL19,
4255 LINE.COL20,
4256 LINE.COL21,
4257 LINE.COL22,
4258 LINE.COL23,
4259 LINE.COL24,
4260 LINE.COL25,
4261 LINE.COL26,
4262 LINE.COL27,
4263 LINE.COL28,
4264 LINE.COL29,
4265 LINE.COL30,
4266 LINE.COL31,
4267 LINE.COL32,
4268 LINE.COL33,
4269 LINE.COL34,
4270 LINE.COL35,
4271 LINE.COL36,
4272 LINE.COL37,
4273 LINE.COL201,
4274 LINE.COL202,
4275 LINE.COL203,
4276 LINE.COL204,
4277 LINE.COL205,
4278 LINE.COL206,
4279 LINE.COL207,
4280 LINE.COL208,
4281 LINE.COL209,
4282 LINE.COL210,
4283 LINE.COL211,
4284 LINE.COL212,
4285 LINE.COL213,
4286 LINE.COL214,
4287 LINE.COL215,
4288 LINE.COL216,
4289 LINE.COL217,
4290 LINE.COL218,
4291 LINE.COL219,
4292 LINE.COL220,
4293 LINE.COL221,
4294 LINE.COL222,
4295 LINE.COL223,
4296 LINE.COL224,
4297 LINE.COL225,
4298 LINE.COL226,
4299 LINE.COL227,
4300 LINE.COL228,
4301 LINE.COL229,
4302 LINE.COL230,
4303 LINE.COL231,
4304 LINE.COL232,
4305 LINE.COL233,
4306 LINE.COL234,
4307 LINE.COL235,
4308 LINE.COL236,
4309 LINE.COL237,
4310 LINE.COL238,
4311 LINE.COL239,
4312 LINE.COL240,
4313 LINE.COL241,
4314 LINE.COL242,
4315 LINE.COL243,
4316 LINE.COL244,
4317 LINE.COL245,
4318 LINE.COL246,
4319 LINE.COL247,
4320 LINE.COL248,
4321 LINE.COL249,
4322 LINE.COL250
4323 from ams_imp_source_lines line,
4324 ams_imp_list_headers_all header
4325 where line.import_source_line_id = p_import_source_line_id
4326 and line.import_list_header_id = p_import_list_header_id
4327 and line.import_list_header_id = header.import_list_header_id;
4328
4329 EXCEPTION
4330 WHEN others THEN
4331 -- ndadwal added if cond for bug 4966524
4332 if p_import_list_header_id is NOT NULL then
4333 AMS_Utility_PVT.Create_Log (
4334 x_return_status => l_return_status,
4335 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
4336 p_log_used_by_id => p_import_list_header_id,
4337 p_msg_data => sqlerrm ,
4338 p_msg_type => 'DEBUG'
4339 );
4340 end if;
4341
4342 raise;
4343 end create_party_source;
4344 -- ------------------------------------------------------------
4345 PROCEDURE person_party_echeck(
4346 p_party_id IN OUT NOCOPY NUMBER,
4347 x_return_status OUT NOCOPY VARCHAR2,
4348 x_msg_count OUT NOCOPY NUMBER,
4349 x_msg_data OUT NOCOPY VARCHAR2,
4350 p_per_first_name IN VARCHAR2,
4351 p_per_last_name IN VARCHAR2,
4352 p_address1 IN VARCHAR2,
4353 p_country IN VARCHAR2,
4354 p_email_address IN VARCHAR2,
4355 p_ph_country_code IN VARCHAR2,
4356 p_ph_area_code IN VARCHAR2,
4357 p_ph_number IN VARCHAR2,
4358 -- sranka 1/13/2003 added p_orig_system_reference
4359 p_orig_system_reference IN VARCHAR2
4360
4361 ) IS
4362
4363
4364 l_party_key varchar2(1000);
4365 L_COUNT number;
4366 l_max_party_id number;
4367 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
4368 l_cust_exists varchar2(1);
4369 l_ret_status varchar(1);
4370 l_transposed_phone_no varchar2(60);
4371
4372
4373 cursor c_email_address is
4374 select max(p.party_id) from hz_contact_points cp,
4375 hz_parties p
4376 where p.customer_key = l_party_key
4377 and p.party_type = 'PERSON'
4378 and p.status = 'A'
4379 and cp.owner_table_id = p.party_id
4380 and cp.owner_table_name = 'HZ_PARTIES'
4381 and upper(cp.email_address) = upper(p_email_address);
4382
4383 cursor c_email_address_with_osr is
4384 select max(p.party_id) from hz_contact_points cp,
4385 hz_parties p
4386 where p.customer_key = l_party_key
4387 and p.party_type = 'PERSON'
4388 and p.status = 'A'
4389 and p.orig_system_reference = p_orig_system_reference
4390 and cp.owner_table_id = p.party_id
4391 and cp.owner_table_name = 'HZ_PARTIES'
4392 and upper(cp.email_address) = upper(p_email_address);
4393
4394
4395 cursor c_ph_number is
4396 select max(p.party_id) from hz_contact_points cp,
4397 hz_parties p
4398 where p.customer_key = l_party_key
4399 and p.party_type = 'PERSON'
4400 and p.status = 'A'
4401 and cp.owner_table_id = p.party_id
4402 and cp.owner_table_name = 'HZ_PARTIES'
4403 and cp.transposed_phone_number = l_transposed_phone_no;
4404 /*
4405 and cp.phone_number = p_ph_number
4406 and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4407 and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4408 */
4409
4410 cursor c_ph_number_with_osr is
4411 select max(p.party_id) from hz_contact_points cp,
4412 hz_parties p
4413 where p.customer_key = l_party_key
4414 and p.party_type = 'PERSON'
4415 and p.status = 'A'
4416 and p.orig_system_reference = p_orig_system_reference
4417 and cp.owner_table_id = p.party_id
4418 and cp.owner_table_name = 'HZ_PARTIES'
4419 and cp.transposed_phone_number = l_transposed_phone_no;
4420 /*
4421 and cp.phone_number = p_ph_number
4422 and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4423 and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4424 */
4425
4426 cursor c_address_country is
4427 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4428 hz_parties party
4429 where psite.location_id = loc.location_id
4430 and loc.address1 = p_address1
4431 and loc.country = p_country
4432 and party.customer_key = l_party_key
4433 and party.party_type = 'PERSON'
4434 and party.status = 'A'
4435 and psite.party_id = party.party_id;
4436
4437 cursor c_address_country_with_osr is
4438 select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4439 hz_parties party
4440 where psite.location_id = loc.location_id
4441 and loc.address1 = p_address1
4442 and loc.country = p_country
4443 and party.customer_key = l_party_key
4444 and party.party_type = 'PERSON'
4445 and party.status = 'A'
4446 and party.orig_system_reference = p_orig_system_reference
4447 and psite.party_id = party.party_id;
4448
4449
4450 cursor c_person_exists is
4451 select 'Y' from hz_parties
4452 where customer_key = l_party_key
4453 and status = 'A'
4454 and party_type = 'PERSON';
4455
4456 cursor c_person_exists_with_osr is
4457 select 'Y' from hz_parties
4458 where customer_key = l_party_key
4459 and party_type = 'PERSON'
4460 and status = 'A'
4461 and orig_system_reference = p_orig_system_reference;
4462
4463
4464 begin
4465
4466 x_return_status := FND_API.g_ret_sts_success;
4467 --
4468 -- Generates the customer key for PERSON
4469 --
4470 if p_per_last_name is not null and p_per_first_name is not null then
4471 l_party_key := hz_fuzzy_pub.Generate_Key (
4472 p_key_type => 'PERSON',
4473 p_first_name => p_per_first_name,
4474 p_last_name => p_per_last_name
4475 );
4476 l_transposed_phone_no := hz_phone_number_pkg.transpose(p_ph_country_code||p_ph_area_code||p_ph_number)
4477 ;
4478 IF p_orig_system_reference IS NULL then
4479 open c_person_exists;
4480 fetch c_person_exists into l_cust_exists;
4481 close c_person_exists;
4482 else
4483 open c_person_exists_with_osr;
4484 fetch c_person_exists_with_osr into l_cust_exists;
4485 close c_person_exists_with_osr;
4486
4487 IF l_cust_exists IS NULL then
4488 open c_person_exists;
4489 fetch c_person_exists into l_cust_exists;
4490 close c_person_exists;
4491 END if;
4492
4493 END if;
4494
4495 end if;
4496
4497 --
4498 -- If customer does not exists then it's a new customer.
4499 --
4500 if l_cust_exists is NULL then
4501 return;
4502 end if;
4503
4504 -- IF email address is provided
4505 if p_email_address is not null then
4506
4507 IF p_orig_system_reference IS NULL then
4508 open c_email_address;
4509 fetch c_email_address into p_party_id;
4510 close c_email_address;
4511 else
4512 open c_email_address_with_osr;
4513 fetch c_email_address_with_osr into p_party_id;
4514 close c_email_address_with_osr;
4515
4516 IF p_party_id IS NULL then
4517 open c_email_address;
4518 fetch c_email_address into p_party_id;
4519 close c_email_address;
4520 END if;
4521
4522 END if;
4523 if p_party_id is not null then
4524 return;
4525 end if;
4526 end if;
4527
4528 -- IF phone number is provided
4529 if p_ph_number is not null then
4530 IF p_orig_system_reference IS NULL then
4531 open c_ph_number;
4532 fetch c_ph_number into p_party_id;
4533 close c_ph_number;
4534 else
4535 open c_ph_number_with_osr;
4536 fetch c_ph_number_with_osr into p_party_id;
4537 close c_ph_number_with_osr;
4538
4539 IF p_party_id IS NULL THEN
4540 open c_ph_number;
4541 fetch c_ph_number into p_party_id;
4542 close c_ph_number;
4543 END IF ;
4544
4545 END IF;
4546 if p_party_id is not null then
4547 return;
4548 end if;
4549 end if;
4550
4551 --
4552 -- When address1 and country is provided
4553 --
4554 if p_address1 is not null and p_country is not null then
4555
4556 IF p_orig_system_reference IS NULL then
4557 open c_address_country;
4558 fetch c_address_country into p_party_id;
4559 close c_address_country;
4560 else
4561 open c_address_country_with_osr;
4562 fetch c_address_country_with_osr into p_party_id;
4563 close c_address_country_with_osr;
4564
4565 IF p_party_id IS NULL then
4566 open c_address_country;
4567 fetch c_address_country into p_party_id;
4568 close c_address_country;
4569 END if;
4570
4571 END IF;
4572
4573 if p_party_id is not null then
4574 return;
4575 end if;
4576 end if;
4577 exception
4578 when others then
4579 x_return_status := FND_API.g_ret_sts_unexp_error ;
4580 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4581 FND_MSG_PUB.add_exc_msg(g_pkg_name,'person_party_echeck');
4582 END IF;
4583 FND_MSG_PUB.count_and_get(
4584 p_encoded => FND_API.g_false,
4585 p_count => x_msg_count,
4586 p_data => x_msg_data
4587 );
4588
4589 end person_party_echeck;
4590 -- ------------------------------------------------------------
4591 -- SOLIN, bug 4465931
4592 PROCEDURE contact_person_party_echeck(
4593 p_party_id IN OUT NOCOPY NUMBER,
4594 x_return_status OUT NOCOPY VARCHAR2,
4595 x_msg_count OUT NOCOPY NUMBER,
4596 x_msg_data OUT NOCOPY VARCHAR2,
4597 p_per_first_name IN VARCHAR2,
4598 p_per_last_name IN VARCHAR2,
4599 p_address1 IN VARCHAR2,
4600 p_country IN VARCHAR2,
4601 p_email_address IN VARCHAR2,
4602 p_ph_country_code IN VARCHAR2,
4603 p_ph_area_code IN VARCHAR2,
4604 p_ph_number IN VARCHAR2,
4605 p_orig_system_reference IN VARCHAR2
4606
4607 ) IS
4608
4609
4610 l_party_key varchar2(1000);
4611 L_COUNT number;
4612 l_max_party_id number;
4613 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
4614 l_cust_exists varchar2(1);
4615 l_ret_status varchar(1);
4616 l_transposed_phone_no varchar2(60);
4617
4618
4619 cursor c_email_address is
4620 select max(p2.party_id) from hz_contact_points cp,
4621 hz_parties p1, hz_relationships hr, hz_parties p2
4622 where p2.customer_key = l_party_key
4623 and p1.party_type = 'PARTY_RELATIONSHIP'
4624 and p2.party_type = 'PERSON'
4625 and p1.status = 'A'
4626 and p2.status = 'A'
4627 and cp.owner_table_id = p1.party_id
4628 and cp.owner_table_name = 'HZ_PARTIES'
4629 and upper(cp.email_address) = upper(p_email_address)
4630 and p1.party_id = hr.party_id
4631 and hr.relationship_code = 'CONTACT_OF'
4632 and hr.subject_id = p2.party_id;
4633
4634 cursor c_email_address_with_osr is
4635 select max(p2.party_id) from hz_contact_points cp,
4636 hz_parties p1, hz_relationships hr, hz_parties p2
4637 where p2.customer_key = l_party_key
4638 and p1.party_type = 'PARTY_RELATIONSHIP'
4639 and p2.party_type = 'PERSON'
4640 and p1.status = 'A'
4641 and p2.status = 'A'
4642 and p1.orig_system_reference = p_orig_system_reference
4643 and cp.owner_table_id = p1.party_id
4644 and cp.owner_table_name = 'HZ_PARTIES'
4645 and upper(cp.email_address) = upper(p_email_address)
4646 and p1.party_id = hr.party_id
4647 and hr.relationship_code = 'CONTACT_OF'
4648 and hr.subject_id = p2.party_id;
4649
4650
4651 cursor c_ph_number is
4652 select max(p2.party_id) from hz_contact_points cp,
4653 hz_parties p1, hz_relationships hr, hz_parties p2
4654 where p2.customer_key = l_party_key
4655 and p1.party_type = 'PARTY_RELATIONSHIP'
4656 and p2.party_type = 'PERSON'
4657 and p1.status = 'A'
4658 and p2.status = 'A'
4659 and cp.owner_table_id = p1.party_id
4660 and cp.owner_table_name = 'HZ_PARTIES'
4661 and cp.transposed_phone_number = l_transposed_phone_no
4662 and p1.party_id = hr.party_id
4663 and hr.relationship_code = 'CONTACT_OF'
4664 and hr.subject_id = p2.party_id;
4665 /*
4666 and cp.phone_number = p_ph_number
4667 and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4668 and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4669 */
4670
4671 cursor c_ph_number_with_osr is
4672 select max(p2.party_id) from hz_contact_points cp,
4673 hz_parties p1, hz_relationships hr, hz_parties p2
4674 where p2.customer_key = l_party_key
4675 and p1.party_type = 'PARTY_RELATIONSHIP'
4676 and p2.party_type = 'PERSON'
4677 and p1.status = 'A'
4678 and p2.status = 'A'
4679 and p1.orig_system_reference = p_orig_system_reference
4680 and cp.owner_table_id = p1.party_id
4681 and cp.owner_table_name = 'HZ_PARTIES'
4682 and cp.transposed_phone_number = l_transposed_phone_no
4683 and p1.party_id = hr.party_id
4684 and hr.relationship_code = 'CONTACT_OF'
4685 and hr.subject_id = p2.party_id;
4686 /*
4687 and cp.phone_number = p_ph_number
4688 and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4689 and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4690 */
4691
4692 cursor c_address_country is
4693 select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4694 hz_parties party, hz_relationships hr, hz_parties p2
4695 where psite.location_id = loc.location_id
4696 and loc.address1 = p_address1
4697 and loc.country = p_country
4698 and party.customer_key = l_party_key
4699 and party.party_type = 'PARTY_RELATIONSHIP'
4700 and p2.party_type = 'PERSON'
4701 and party.status = 'A'
4702 and p2.status = 'A'
4703 and psite.party_id = party.party_id
4704 and party.party_id = hr.party_id
4705 and hr.relationship_code = 'CONTACT_OF'
4706 and hr.subject_id = p2.party_id;
4707
4708 cursor c_address_country_with_osr is
4709 select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4710 hz_parties party, hz_relationships hr, hz_parties p2
4711 where psite.location_id = loc.location_id
4712 and loc.address1 = p_address1
4713 and loc.country = p_country
4714 and party.customer_key = l_party_key
4715 and party.party_type = 'PARTY_RELATIONSHIP'
4716 and p2.party_type = 'PERSON'
4717 and party.status = 'A'
4718 and p2.status = 'A'
4719 and party.orig_system_reference = p_orig_system_reference
4720 and psite.party_id = party.party_id
4721 and party.party_id = hr.party_id
4722 and hr.relationship_code = 'CONTACT_OF'
4723 and hr.subject_id = p2.party_id;
4724
4725
4726 cursor c_person_exists is
4727 select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4728 where hp2. customer_key = l_party_key
4729 and hp1.status = 'A'
4730 and hp2.status = 'A'
4731 and hp1.party_type = 'PARTY_RELATIONSHIP'
4732 and hp1.party_id = hr.party_id
4733 and hr.relationship_code = 'CONTACT_OF'
4734 and hr.subject_id = hp2.party_id
4735 and hp2.party_type = 'PERSON';
4736
4737 cursor c_person_exists_with_osr is
4738 select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4739 where hp2. customer_key = l_party_key
4740 and hp1.status = 'A'
4741 and hp2.status = 'A'
4742 and hp1.party_type = 'PARTY_RELATIONSHIP'
4743 and hp1.party_id = hr.party_id
4744 and hr.relationship_code = 'CONTACT_OF'
4745 and hr.subject_id = hp2.party_id
4746 and hp2.party_type = 'PERSON'
4747 and hp2.orig_system_reference = p_orig_system_reference;
4748
4749 cursor c_get_party_id(c_rel_party_id NUMBER) IS
4750 select hr.subject_id
4751 from hz_relationships hr
4752 where hr.party_id = c_rel_party_id
4753 and hr.relationship_code = 'CONTACT_OF';
4754 begin
4755
4756 x_return_status := FND_API.g_ret_sts_success;
4757 --
4758 -- Generates the customer key for PERSON
4759 --
4760 if p_per_last_name is not null and p_per_first_name is not null then
4761 l_party_key := hz_fuzzy_pub.Generate_Key (
4762 p_key_type => 'PERSON',
4763 p_first_name => p_per_first_name,
4764 p_last_name => p_per_last_name
4765 );
4766 l_transposed_phone_no := hz_phone_number_pkg.transpose(p_ph_country_code||p_ph_area_code||p_ph_number)
4767 ;
4768 IF p_orig_system_reference IS NULL then
4769 open c_person_exists;
4770 fetch c_person_exists into l_cust_exists;
4771 close c_person_exists;
4772 else
4773 open c_person_exists_with_osr;
4774 fetch c_person_exists_with_osr into l_cust_exists;
4775 close c_person_exists_with_osr;
4776
4777 IF l_cust_exists IS NULL then
4778 open c_person_exists;
4779 fetch c_person_exists into l_cust_exists;
4780 close c_person_exists;
4781 END if;
4782
4783 END if;
4784
4785 end if;
4786
4787 --
4788 -- If customer does not exists then it's a new customer.
4789 --
4790 if l_cust_exists is NULL then
4791 return;
4792 end if;
4793
4794 -- IF email address is provided
4795 if p_email_address is not null then
4796
4797 IF p_orig_system_reference IS NULL then
4798 open c_email_address;
4799 fetch c_email_address into p_party_id;
4800 close c_email_address;
4801 else
4802 open c_email_address_with_osr;
4803 fetch c_email_address_with_osr into p_party_id;
4804 close c_email_address_with_osr;
4805
4806 IF p_party_id IS NULL then
4807 open c_email_address;
4808 fetch c_email_address into p_party_id;
4809 close c_email_address;
4810 END if;
4811
4812 END if;
4813 if p_party_id is not null then
4814 return;
4815 end if;
4816 end if;
4817
4818 -- IF phone number is provided
4819 if p_ph_number is not null then
4820 IF p_orig_system_reference IS NULL then
4821 open c_ph_number;
4822 fetch c_ph_number into p_party_id;
4823 close c_ph_number;
4824 else
4825 open c_ph_number_with_osr;
4826 fetch c_ph_number_with_osr into p_party_id;
4827 close c_ph_number_with_osr;
4828
4829 IF p_party_id IS NULL THEN
4830 open c_ph_number;
4831 fetch c_ph_number into p_party_id;
4832 close c_ph_number;
4833 END IF ;
4834
4835 END IF;
4836 if p_party_id is not null then
4837 return;
4838 end if;
4839 end if;
4840
4841 --
4842 -- When address1 and country is provided
4843 --
4844 if p_address1 is not null and p_country is not null then
4845
4846 IF p_orig_system_reference IS NULL then
4847 open c_address_country;
4848 fetch c_address_country into p_party_id;
4849 close c_address_country;
4850 else
4851 open c_address_country_with_osr;
4852 fetch c_address_country_with_osr into p_party_id;
4853 close c_address_country_with_osr;
4854
4855 IF p_party_id IS NULL then
4856 open c_address_country;
4857 fetch c_address_country into p_party_id;
4858 close c_address_country;
4859 END if;
4860
4861 END IF;
4862
4863 if p_party_id is not null then
4864 return;
4865 end if;
4866 end if;
4867 exception
4868 when others then
4869 x_return_status := FND_API.g_ret_sts_unexp_error ;
4870 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4871 FND_MSG_PUB.add_exc_msg(g_pkg_name,'contact_person_party_echeck');
4872 END IF;
4873 FND_MSG_PUB.count_and_get(
4874 p_encoded => FND_API.g_false,
4875 p_count => x_msg_count,
4876 p_data => x_msg_data
4877 );
4878
4879 end contact_person_party_echeck;
4880 -- SOLIN, end bug 4465931
4881 -----------------------------------------
4882 PROCEDURE rented_person_party_echeck(
4883 p_party_id IN OUT NOCOPY NUMBER,
4884 x_return_status OUT NOCOPY VARCHAR2,
4885 x_msg_count OUT NOCOPY NUMBER,
4886 x_msg_data OUT NOCOPY VARCHAR2,
4887 p_per_first_name IN VARCHAR2,
4888 p_per_last_name IN VARCHAR2,
4889 p_address1 IN VARCHAR2,
4890 p_country IN VARCHAR2,
4891 p_email_address IN VARCHAR2,
4892 p_ph_country_code IN VARCHAR2,
4893 p_ph_area_code IN VARCHAR2,
4894 p_ph_number IN VARCHAR2
4895 ) IS
4896
4897
4898 l_party_key varchar2(1000);
4899 L_COUNT number;
4900 l_max_party_id number;
4901 l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
4902 l_cust_exists varchar2(1);
4903 l_ret_status varchar(1);
4904
4905
4906 cursor c_email_address is
4907 SELECT PARTY_ID FROM AMS_PARTY_SOURCES
4908 WHERE PARTY_ID = p_party_id
4909 and upper(COL26) = upper(p_email_address);
4910
4911 cursor c_ph_number is
4912 select party_id from AMS_PARTY_SOURCES
4913 where party_id = p_party_id
4914 and col29 = p_ph_number
4915 and nvl(col27,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4916 and nvl(col28,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4917
4918 cursor c_address_country is
4919 select party_id from AMS_PARTY_SOURCES
4920 where party_id = p_party_id
4921 and col18 = p_address1
4922 and col17 = p_country;
4923
4924
4925 cursor c_person_exists is
4926 select MAX(PARTY_ID) from hz_parties
4927 where customer_key = l_party_key
4928 and party_type = 'PERSON';
4929 begin
4930
4931 x_return_status := FND_API.g_ret_sts_success;
4932 --
4933 -- Generates the customer key for PERSON
4934 --
4935 if p_per_last_name is not null and p_per_first_name is not null then
4936 l_party_key := hz_fuzzy_pub.Generate_Key (
4937 p_key_type => 'PERSON',
4938 p_first_name => p_per_first_name,
4939 p_last_name => p_per_last_name
4940 );
4941 open c_person_exists;
4942 fetch c_person_exists into p_party_id;
4943 close c_person_exists;
4944 end if;
4945
4946 --
4947 -- If customer does not exists then it's a new customer.
4948 --
4949 if l_max_party_id is null then
4950 return;
4951 end if;
4952
4953 -- IF email address is provided
4954 if p_email_address is not null then
4955 open c_email_address;
4956 fetch c_email_address into p_party_id;
4957 close c_email_address;
4958
4959 if p_party_id is not null then
4960 return;
4961 end if;
4962 end if;
4963
4964 -- IF phone number is provided
4965 if p_ph_number is not null then
4966 open c_ph_number;
4967 fetch c_ph_number into p_party_id;
4968 close c_ph_number;
4969 if p_party_id is not null then
4970 return;
4971 end if;
4972 end if;
4973
4974 --
4975 -- When address1 and country is provided
4976 --
4977 if p_address1 is not null and p_country is not null then
4978 open c_address_country;
4979 fetch c_address_country into p_party_id;
4980 close c_address_country;
4981 if p_party_id is not null then
4982 return;
4983 end if;
4984 end if;
4985 exception
4986 when others then
4987 x_return_status := FND_API.g_ret_sts_unexp_error ;
4988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4989 FND_MSG_PUB.add_exc_msg(g_pkg_name,'person_party_echeck');
4990 END IF;
4991 FND_MSG_PUB.count_and_get(
4992 p_encoded => FND_API.g_false,
4993 p_count => x_msg_count,
4994 p_data => x_msg_data
4995 );
4996
4997 end rented_person_party_echeck;
4998 -- -----------------------------------------------------
4999 --
5000 -- This progam updates the party for the rented list
5001 --
5002 PROCEDURE update_rented_list_party (
5003 p_api_version IN NUMBER,
5004 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5005 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5006 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
5007 x_return_status OUT NOCOPY VARCHAR2,
5008 x_msg_count OUT NOCOPY NUMBER,
5009 x_msg_data OUT NOCOPY VARCHAR2,
5010 p_party_id IN NUMBER
5011 ) IS
5012
5013 BEGIN
5014 SAVEPOINT update_rented_list_party_pub;
5015 -- initialize the message list;
5016 IF FND_API.to_boolean(p_init_msg_list) THEN
5017 FND_MSG_PUB.initialize;
5018 END IF;
5019
5020 --call private API procedure.
5021 AMS_ListImport_PVT.update_rented_list_party (
5022 p_party_id => p_party_id,
5023 p_return_status => x_return_status,
5024 p_msg_count => x_msg_count,
5025 p_msg_data => x_msg_data);
5026
5027 IF x_return_status = FND_API.g_ret_sts_error THEN
5028 RAISE FND_API.g_exc_error;
5029 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5030 RAISE FND_API.g_exc_unexpected_error;
5031 END IF;
5032
5033 IF FND_API.to_boolean(p_commit) THEN
5034 COMMIT;
5035 END IF;
5036 x_return_status := FND_API.g_ret_sts_success;
5037 FND_MSG_PUB.count_and_get(
5038 p_encoded => FND_API.g_false,
5039 p_count => x_msg_count,
5040 p_data => x_msg_data
5041 );
5042 EXCEPTION
5043
5044 WHEN FND_API.g_exc_error THEN
5045 ROLLBACK TO update_rented_list_party_pub;
5046 x_return_status := FND_API.g_ret_sts_error;
5047 FND_MSG_PUB.count_and_get(
5048 p_encoded => FND_API.g_false,
5049 p_count => x_msg_count,
5050 p_data => x_msg_data
5051 );
5052
5053 WHEN FND_API.g_exc_unexpected_error THEN
5054 ROLLBACK TO update_rented_list_party_pub;
5055 x_return_status := FND_API.g_ret_sts_unexp_error ;
5056 FND_MSG_PUB.count_and_get(
5057 p_encoded => FND_API.g_false,
5058 p_count => x_msg_count,
5059 p_data => x_msg_data
5060 );
5061
5062 WHEN OTHERS THEN
5063 ROLLBACK TO update_rented_list_party_pub;
5064 x_return_status := FND_API.g_ret_sts_unexp_error ;
5065
5066 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5067 FND_MSG_PUB.add_exc_msg(g_pkg_name, 'update_rented_list_party');
5068 END IF;
5069
5070 FND_MSG_PUB.count_and_get(
5071 p_encoded => FND_API.g_false,
5072 p_count => x_msg_count,
5073 p_data => x_msg_data
5074 );
5075
5076 end update_rented_list_party;
5077
5078 --
5079 -- for XML updates the success in the element table
5080 --
5081 PROCEDURE process_element_success(
5082 p_import_list_header_id IN NUMBER,
5083 p_xml_element_id IN NUMBER) IS
5084
5085 x_return_status VARCHAR2(1);
5086 x_msg_data VARCHAR2(2000);
5087 l_imp_type VARCHAR2(30);
5088
5089 l_per_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5090 l_org_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5091 l_loc_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5092 l_con_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5093 l_pho_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5094 l_eml_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5095
5096 cursor c_imp_type is
5097 select import_type from ams_imp_list_headers_all
5098 where import_list_header_id = p_import_list_header_id;
5099
5100 begin
5101 open c_imp_type;
5102 fetch c_imp_type into l_imp_type;
5103 close c_imp_type;
5104
5105 if l_imp_type = 'B2B' then
5106 update AMS_IMP_XML_ELEMENTS
5107 set LOAD_STATUS = 'SUCCESS'
5108 where imp_xml_element_id = p_xml_element_id;
5109 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5110 p_imp_xml_element_id => p_xml_element_id,
5111 x_child_set => l_org_record_in_tbl,
5112 x_return_status => x_return_status,
5113 x_msg_data => x_msg_data);
5114 -- FOR ORGANIZATION
5115 FOR i IN 1..l_org_record_in_tbl.COUNT
5116 LOOP
5117 if l_org_record_in_tbl(i).data_type = 'T' then
5118 update AMS_IMP_XML_ELEMENTS
5119 set LOAD_STATUS = 'SUCCESS'
5120 where imp_xml_element_id = l_org_record_in_tbl(i).imp_xml_element_id;
5121 -- FOR LOCATION
5122 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5123 p_imp_xml_element_id => l_org_record_in_tbl(i).imp_xml_element_id,
5124 x_child_set => l_loc_record_in_tbl,
5125 x_return_status => x_return_status,
5126 x_msg_data => x_msg_data);
5127 FOR j IN 1..l_loc_record_in_tbl.COUNT
5128 LOOP
5129 if l_loc_record_in_tbl(j).data_type = 'T' then
5130 update AMS_IMP_XML_ELEMENTS
5131 set LOAD_STATUS = 'SUCCESS'
5132 where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
5133 -- FOR ORG CONTACT
5134 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5135 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5136 x_child_set => l_con_record_in_tbl,
5137 x_return_status => x_return_status,
5138 x_msg_data => x_msg_data);
5139 FOR k IN 1..l_con_record_in_tbl.COUNT
5140 LOOP
5141 if l_con_record_in_tbl(k).data_type = 'T' then
5142 update AMS_IMP_XML_ELEMENTS
5143 set LOAD_STATUS = 'SUCCESS'
5144 where imp_xml_element_id = l_con_record_in_tbl(k).imp_xml_element_id;
5145 -- FOR PHONE
5146 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5147 p_imp_xml_element_id => l_con_record_in_tbl(k).imp_xml_element_id,
5148 x_child_set => l_pho_record_in_tbl,
5149 x_return_status => x_return_status,
5150 x_msg_data => x_msg_data);
5151 FOR l IN 1..l_pho_record_in_tbl.COUNT
5152 LOOP
5153 if l_pho_record_in_tbl(l).data_type = 'T' then
5154 update AMS_IMP_XML_ELEMENTS
5155 set LOAD_STATUS = 'SUCCESS'
5156 where imp_xml_element_id = l_pho_record_in_tbl(l).imp_xml_element_id;
5157 end if;
5158 END LOOP; -- FOR PHONE
5159 -- FOR EMAIL
5160 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5161 p_imp_xml_element_id => l_con_record_in_tbl(k).imp_xml_element_id,
5162 x_child_set => l_eml_record_in_tbl,
5163 x_return_status => x_return_status,
5164 x_msg_data => x_msg_data);
5165 FOR m IN 1..l_eml_record_in_tbl.COUNT
5166 LOOP
5167 if l_eml_record_in_tbl(m).data_type = 'T' then
5168 update AMS_IMP_XML_ELEMENTS
5169 set LOAD_STATUS = 'SUCCESS'
5170 where imp_xml_element_id = l_eml_record_in_tbl(m).imp_xml_element_id;
5171 end if;
5172 END LOOP; -- FOR EMAIL
5173 end if;
5174 END LOOP; -- FOR ORG CONTACT
5175 end if;
5176 END LOOP; -- FOR LOCATION
5177 end if;
5178 END LOOP; -- FOR ORGANIZATION
5179 end if;
5180
5181
5182 if l_imp_type = 'B2C' then
5183 update AMS_IMP_XML_ELEMENTS
5184 set LOAD_STATUS = 'SUCCESS'
5185 where imp_xml_element_id = p_xml_element_id;
5186 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5187 p_imp_xml_element_id => p_xml_element_id,
5188 x_child_set => l_per_record_in_tbl,
5189 x_return_status => x_return_status,
5190 x_msg_data => x_msg_data);
5191 -- FOR PERSON
5192 FOR i IN 1..l_per_record_in_tbl.COUNT
5193 LOOP
5194 if l_per_record_in_tbl(i).data_type = 'T' then
5195 update AMS_IMP_XML_ELEMENTS
5196 set LOAD_STATUS = 'SUCCESS'
5197 where imp_xml_element_id = l_per_record_in_tbl(i).imp_xml_element_id;
5198 -- FOR LOCATION
5199 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5200 p_imp_xml_element_id => l_per_record_in_tbl(i).imp_xml_element_id,
5201 x_child_set => l_loc_record_in_tbl,
5202 x_return_status => x_return_status,
5203 x_msg_data => x_msg_data);
5204 FOR j IN 1..l_loc_record_in_tbl.COUNT
5205 LOOP
5206 if l_loc_record_in_tbl(j).data_type = 'T' then
5207 update AMS_IMP_XML_ELEMENTS
5208 set LOAD_STATUS = 'SUCCESS'
5209 where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
5210 -- FOR PHONE
5211 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5212 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5213 x_child_set => l_pho_record_in_tbl,
5214 x_return_status => x_return_status,
5215 x_msg_data => x_msg_data);
5216 FOR k IN 1..l_pho_record_in_tbl.COUNT
5217 LOOP
5218 if l_pho_record_in_tbl(k).data_type = 'T' then
5219 update AMS_IMP_XML_ELEMENTS
5220 set LOAD_STATUS = 'SUCCESS'
5221 where imp_xml_element_id = l_pho_record_in_tbl(k).imp_xml_element_id;
5222 end if;
5223 END LOOP; -- FOR PHONE
5224 -- FOR EMAIL
5225 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5226 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5227 x_child_set => l_eml_record_in_tbl,
5228 x_return_status => x_return_status,
5229 x_msg_data => x_msg_data);
5230 FOR l IN 1..l_eml_record_in_tbl.COUNT
5231 LOOP
5232 if l_eml_record_in_tbl(l).data_type = 'T' then
5233 update AMS_IMP_XML_ELEMENTS
5234 set LOAD_STATUS = 'SUCCESS'
5235 where imp_xml_element_id = l_eml_record_in_tbl(l).imp_xml_element_id;
5236 end if;
5237 END LOOP; -- FOR EMAIL
5238 end if;
5239 END LOOP; -- FOR LOCATION
5240 end if;
5241 END LOOP; -- FOR PERSON
5242 end if;
5243
5244 exception
5245 WHEN OTHERS THEN
5246 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
5247 FND_MESSAGE.Set_Token('ROW','Error in process_element_success :' || SQLERRM||' '||SQLCODE);
5248 -- ndadwal added if cond for bug 4966524
5249 if p_import_list_header_id is NOT NULL then
5250 AMS_Utility_PVT.Create_Log (
5251 x_return_status => x_return_status,
5252 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
5253 p_log_used_by_id => p_import_list_header_id,
5254 p_msg_data => FND_MESSAGE.get,
5255 p_msg_type => 'DEBUG'
5256 );
5257 end if;
5258 end process_element_success;
5259
5260
5261 --
5262 -- for XML updates the duplicate in the element table
5263 --
5264 PROCEDURE process_element_duplicate(
5265 p_import_list_header_id IN NUMBER,
5266 p_xml_element_id IN NUMBER) IS
5267
5268 x_return_status VARCHAR2(1);
5269 x_msg_data VARCHAR2(2000);
5270 l_imp_type VARCHAR2(30);
5271
5272 l_per_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5273 l_org_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5274 l_loc_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5275 l_con_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5276 l_pho_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5277 l_eml_record_in_tbl AMS_IMPORT_XML_PVT.xml_element_set_type;
5278
5279 cursor c_imp_type is
5280 select import_type from ams_imp_list_headers_all
5281 where import_list_header_id = p_import_list_header_id;
5282
5283 begin
5284 open c_imp_type;
5285 fetch c_imp_type into l_imp_type;
5286 close c_imp_type;
5287
5288 if l_imp_type = 'B2B' then
5289 -- ndadwal added if cond for bug 4966524
5290 if p_import_list_header_id is NOT NULL then
5291 AMS_Utility_PVT.Create_Log (
5292 x_return_status => x_return_status,
5293 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
5294 p_log_used_by_id => p_import_list_header_id,
5295 p_msg_data => 'p_xml_element_id := '||p_xml_element_id,
5296 p_msg_type => 'DEBUG');
5297 end if;
5298 update AMS_IMP_XML_ELEMENTS
5299 set LOAD_STATUS = 'DUPLICATE'
5300 where imp_xml_element_id = p_xml_element_id;
5301 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5302 p_imp_xml_element_id => p_xml_element_id,
5303 x_child_set => l_org_record_in_tbl,
5304 x_return_status => x_return_status,
5305 x_msg_data => x_msg_data);
5306 -- FOR ORGANIZATION
5307 FOR i IN 1..l_org_record_in_tbl.COUNT
5308 LOOP
5309 if l_org_record_in_tbl(i).data_type = 'T' then
5310 update AMS_IMP_XML_ELEMENTS
5311 set LOAD_STATUS = 'DUPLICATE'
5312 where imp_xml_element_id = l_org_record_in_tbl(i).imp_xml_element_id;
5313 -- FOR LOCATION
5314 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5315 p_imp_xml_element_id => l_org_record_in_tbl(i).imp_xml_element_id,
5316 x_child_set => l_loc_record_in_tbl,
5317 x_return_status => x_return_status,
5318 x_msg_data => x_msg_data);
5319 FOR j IN 1..l_loc_record_in_tbl.COUNT
5320 LOOP
5321 if l_loc_record_in_tbl(j).data_type = 'T' then
5322 update AMS_IMP_XML_ELEMENTS
5323 set LOAD_STATUS = 'DUPLICATE'
5324 where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
5325 -- FOR ORG CONTACT
5326 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5327 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5328 x_child_set => l_con_record_in_tbl,
5329 x_return_status => x_return_status,
5330 x_msg_data => x_msg_data);
5331 FOR k IN 1..l_con_record_in_tbl.COUNT
5332 LOOP
5333 if l_con_record_in_tbl(k).data_type = 'T' then
5334 update AMS_IMP_XML_ELEMENTS
5335 set LOAD_STATUS = 'DUPLICATE'
5336 where imp_xml_element_id = l_con_record_in_tbl(k).imp_xml_element_id;
5337 -- FOR PHONE
5338 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5339 p_imp_xml_element_id => l_con_record_in_tbl(k).imp_xml_element_id,
5340 x_child_set => l_pho_record_in_tbl,
5341 x_return_status => x_return_status,
5342 x_msg_data => x_msg_data);
5343 FOR l IN 1..l_pho_record_in_tbl.COUNT
5344 LOOP
5345 if l_pho_record_in_tbl(l).data_type = 'T' then
5346 update AMS_IMP_XML_ELEMENTS
5347 set LOAD_STATUS = 'DUPLICATE'
5348 where imp_xml_element_id = l_pho_record_in_tbl(l).imp_xml_element_id;
5349 end if;
5350 END LOOP; -- FOR PHONE
5351 -- FOR EMAIL
5352 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5353 p_imp_xml_element_id => l_con_record_in_tbl(k).imp_xml_element_id,
5354 x_child_set => l_eml_record_in_tbl,
5355 x_return_status => x_return_status,
5356 x_msg_data => x_msg_data);
5357 FOR m IN 1..l_eml_record_in_tbl.COUNT
5358 LOOP
5359 if l_eml_record_in_tbl(m).data_type = 'T' then
5360 update AMS_IMP_XML_ELEMENTS
5361 set LOAD_STATUS = 'DUPLICATE'
5362 where imp_xml_element_id = l_eml_record_in_tbl(m).imp_xml_element_id;
5363 end if;
5364 END LOOP; -- FOR EMAIL
5365 end if;
5366 END LOOP; -- FOR ORG CONTACT
5367 end if;
5368 END LOOP; -- FOR LOCATION
5369 end if;
5370 END LOOP; -- FOR ORGANIZATION
5371 end if;
5372
5373
5374 if l_imp_type = 'B2C' then
5375 update AMS_IMP_XML_ELEMENTS
5376 set LOAD_STATUS = 'DUPLICATE'
5377 where imp_xml_element_id = p_xml_element_id;
5378 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5379 p_imp_xml_element_id => p_xml_element_id,
5380 x_child_set => l_per_record_in_tbl,
5381 x_return_status => x_return_status,
5382 x_msg_data => x_msg_data);
5383 -- FOR PERSON
5384 FOR i IN 1..l_per_record_in_tbl.COUNT
5385 LOOP
5386 if l_per_record_in_tbl(i).data_type = 'T' then
5387 update AMS_IMP_XML_ELEMENTS
5388 set LOAD_STATUS = 'DUPLICATE'
5389 where imp_xml_element_id = l_per_record_in_tbl(i).imp_xml_element_id;
5390 -- FOR LOCATION
5391 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5392 p_imp_xml_element_id => l_per_record_in_tbl(i).imp_xml_element_id,
5393 x_child_set => l_loc_record_in_tbl,
5394 x_return_status => x_return_status,
5395 x_msg_data => x_msg_data);
5396 FOR j IN 1..l_loc_record_in_tbl.COUNT
5397 LOOP
5398 if l_loc_record_in_tbl(j).data_type = 'T' then
5399 update AMS_IMP_XML_ELEMENTS
5400 set LOAD_STATUS = 'DUPLICATE'
5401 where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
5402 -- FOR PHONE
5403 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5404 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5405 x_child_set => l_pho_record_in_tbl,
5406 x_return_status => x_return_status,
5407 x_msg_data => x_msg_data);
5408 FOR k IN 1..l_pho_record_in_tbl.COUNT
5409 LOOP
5410 if l_pho_record_in_tbl(k).data_type = 'T' then
5411 update AMS_IMP_XML_ELEMENTS
5412 set LOAD_STATUS = 'DUPLICATE'
5413 where imp_xml_element_id = l_pho_record_in_tbl(k).imp_xml_element_id;
5414 end if;
5415 END LOOP; -- FOR PHONE
5416 -- FOR EMAIL
5417 AMS_IMPORT_XML_PVT.Get_Children_Nodes (
5418 p_imp_xml_element_id => l_loc_record_in_tbl(j).imp_xml_element_id,
5419 x_child_set => l_eml_record_in_tbl,
5420 x_return_status => x_return_status,
5421 x_msg_data => x_msg_data);
5422 FOR l IN 1..l_eml_record_in_tbl.COUNT
5423 LOOP
5424 if l_eml_record_in_tbl(l).data_type = 'T' then
5425 update AMS_IMP_XML_ELEMENTS
5426 set LOAD_STATUS = 'DUPLICATE'
5427 where imp_xml_element_id = l_eml_record_in_tbl(l).imp_xml_element_id;
5428 end if;
5429 END LOOP; -- FOR EMAIL
5430 end if;
5431 END LOOP; -- FOR LOCATION
5432 end if;
5433 END LOOP; -- FOR PERSON
5434 end if;
5435
5436 exception
5437 WHEN OTHERS THEN
5438 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
5439 FND_MESSAGE.Set_Token('ROW','Error in process_element_duplicate :' || SQLERRM||' '||SQLCODE);
5440 -- ndadwal added if cond for bug 4966524
5441 if p_import_list_header_id is NOT NULL then
5442 AMS_Utility_PVT.Create_Log (
5443 x_return_status => x_return_status,
5444 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
5445 p_log_used_by_id => p_import_list_header_id,
5446 p_msg_data => FND_MESSAGE.get,
5447 p_msg_type => 'DEBUG'
5448 );
5449 end if;
5450 end process_element_duplicate;
5451
5452
5453
5454 end AMS_List_Import_PUB;
[Home] [Help]