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