[Home] [Help]
PACKAGE BODY: APPS.PV_CONTACT_USER_BATCH_PUB
Source
1 PACKAGE BODY PV_CONTACT_USER_BATCH_PUB AS
2 /* $Header: pvxpldcb.pls 120.24 2006/02/12 19:48 svnathan noship $ */
3
4 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7
8
9
10 L_LOG_FILE utl_file.file_type;
11
12
13
14
15 PROCEDURE Write_Error
16 (
17 p_errors_tbl IN LOG_MESSAGE_TBL_TYPE
18
19 )
20 IS
21 l_errors_tbl LOG_MESSAGE_TBL_TYPE:=p_errors_tbl;
22 BEGIN
23 -- dbms_output.put_line('Error Count: ' || l_errors_tbl.count);
24 if l_errors_tbl.count > 0 then
25 for i in 1..l_errors_tbl.count
26 loop
27 utl_file.put_line(L_LOG_FILE, rpad(' ',160) || l_errors_tbl(i));
28 -- dbms_output.put_line('Error: ' || l_errors_tbl(i));
29 end loop;
30 l_errors_tbl.delete;
31 end if;
32
33 END Write_Error;
34
35
36
37 PROCEDURE upsert_attributes(
38 p_api_version_number IN NUMBER
39 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
40 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
41 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
42 ,p_attr_details_tbl IN PV_CONTACT_USER_BATCH_PUB.attr_details_tbl_type
43 ,p_contact_rel_id IN NUMBER
44 ,x_log_msg OUT NOCOPY LOG_MESSAGE_TBL_TYPE
45 ,x_attribute_creation_status OUT NOCOPY VARCHAR2
46 )
47 IS
48 l_attr_details_tbl PV_CONTACT_USER_BATCH_PUB.attr_details_tbl_type := p_attr_details_tbl;
49 l_version NUMBER;
50 l_upsert_attr_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
51 l_attribute_details_rec PV_CONTACT_USER_BATCH_PUB.attribute_details_rec_type;
52 l_contact_rel_id NUMBER := p_contact_rel_id;
53 l_return_status VARCHAR2(1);
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(2000);
56 L_API_NAME CONSTANT VARCHAR2(30) := 'upsert_attributes';
57 l_status VARCHAR2(100) := 'SUCCESS';
58 l_log_msg LOG_MESSAGE_TBL_TYPE;
59 l_log_count NUMBER :=0;
60 CURSOR get_attr_version(cv_entity_id IN NUMBER, cv_attr_id IN Number) IS
61 SELECT
62 max(version)
63 FROM
64 pv_enty_attr_values
65 WHERE
66 attribute_id = cv_attr_id and
67 entity_id = cv_entity_id;
68
69
70
71 BEGIN
72
73 -- dbms_output.put_line ('Entered Attribute values upsert');
74 x_attribute_creation_status:='ERROR';
75 for j in l_attr_details_tbl.first..l_attr_details_tbl.last
76 loop
77 -- dbms_output.put_line ('Entered for loop');
78
79 l_attribute_details_rec:=l_attr_details_tbl(j);
80 -- dbms_output.put_line ('Assigned the attr details rec');
81
82 OPEN get_attr_version(l_contact_rel_id,l_attribute_details_rec.attribute_id);
83 FETCH get_attr_version INTO l_version;
84 close get_attr_version;
85
86 -- dbms_output.put_line ('After the cursor');
87 if l_version is null then
88 l_version := 0;
89 end if;
90 -- dbms_output.put_line ('After the version '|| l_version );
91 -- dbms_output.put_line ('l_contact_rel_id '|| l_contact_rel_id );
92 -- dbms_output.put_line ('attribute id '|| l_attribute_details_rec.attribute_id );
93
94 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value (
95 p_api_version_number=> 1.0
96 ,p_init_msg_list => FND_API.g_true
97 ,p_commit => FND_API.g_false
98 ,p_validation_level => FND_API.g_valid_level_full
99 ,x_return_status => l_return_status
100 ,x_msg_count => l_msg_count
101 ,x_msg_data => l_msg_data
102 ,p_attribute_id => l_attribute_details_rec.attribute_id
103 ,p_entity => 'PARTNER_CONTACT'
104 ,p_entity_id => l_contact_rel_id
105 ,p_version => l_version
106 ,p_attr_val_tbl => l_attribute_details_rec.attr_values_tbl
107 );
108
109 -- dbms_output.put_line ('After API call'|| l_return_status);
110 -- dbms_output.put_line('Message count : '||l_msg_count);
111 -- dbms_output.put_line('Before message data : ');
112 -- dbms_output.put_line(' API msgdata=> '|| l_msg_data);
113
114
115 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
116 x_attribute_creation_status:='SUCCESS';
117 END IF;
118
119
120 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
121 x_attribute_creation_status:='ERROR';
122
123
124 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
125 /*FOR l_msg_index IN 1..l_msg_count LOOP
126 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
127 -- dbms_output.put_line(fnd_message.get);
128 l_log_count:= l_log_count + 1;
129 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
130 END LOOP;
131 */
132
133 l_log_count:= l_log_count + 1;
134 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_count));
135 x_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
136
137 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
138 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
139 FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value');
140 l_log_count:= l_log_count + 1;
141 x_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
142 END IF;
143 END IF;
144
145
146 end loop;
147
148
149 -- dbms_output.put_line ('Done with Attribute values upsert');
150
151
152
153
154
155 EXCEPTION
156
157
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159 -- dbms_output.put_line ('entered unexpected error in attributes');
160 x_attribute_creation_status:='ERROR';
161
162 WHEN OTHERS THEN
163 -- dbms_output.put_line ('entered other error in attributes');
164 x_attribute_creation_status:='ERROR';
165 END upsert_attributes;
166
167
168
169
170
171
172
173
174
175
176
177 PROCEDURE contact_create (
178 p_api_version_number IN NUMBER
179 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
180 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
181 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
182 ,p_contact_details_rec IN CONTACT_DETAILS_REC_TYPE
183 ,p_update_flag IN VARCHAR2
184 ,x_contact_rel_id OUT NOCOPY NUMBER
185 ,x_contact_output_rec OUT NOCOPY CONTACT_OUTPUT_REC_TYPE
186 ,x_log_msg OUT NOCOPY LOG_MESSAGE_TBL_TYPE
187 ,x_return_status OUT NOCOPY VARCHAR2
188 ,x_msg_data OUT NOCOPY VARCHAR2
189 ,x_msg_count OUT NOCOPY NUMBER
190 )
191 IS
192
193
194 L_API_NAME CONSTANT VARCHAR2(30) := 'contact_create';
195 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
196 l_contact_create_ok VARCHAR(10) := 'TRUE';
197 l_sso_enabled VARCHAR2(100);
198 l_test_user_return_code VARCHAR2(100);
199 l_org_id NUMBER;
200 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_Type;
201 l_update_allowed VARCHAR2(1000) := p_update_flag;
202 l_api_version_number NUMBER ;
203 l_init_msg_list VARCHAR2(1000);
204 l_commit VARCHAR2(1000);
205 l_validation_level NUMBER ;
206 l_contact_details_rec CONTACT_DETAILS_REC_TYPE := p_contact_details_rec;
207 l_contact_output_rec CONTACT_OUTPUT_REC_TYPE;
208 l_user_name VARCHAR2(1000):=l_contact_details_rec.user_name;
209 l_password VARCHAR2(1000):= l_contact_details_rec.password;
210 l_email_id VARCHAR2(1000):= l_contact_details_rec.email_rec.email_address;
211 l_partner_party_id NUMBER := l_contact_details_rec.partner_party_id;
212 l_party_number NUMBER;
213 l_partner_user_rec PV_USER_MGMT_PVT.Partner_User_Rec_type;
214 l_person_party_id NUMBER := l_contact_details_rec.person_party_id ;
215 l_rel_party_id NUMBER ;
216 l_org_party_id NUMBER;
217 l_org_contact_party_id NUMBER;
218 l_rel_id NUMBER;
219 l_rel_party_number NUMBER;
220 l_user_type_key VARCHAR(1000):=l_contact_details_rec.User_type;
221 l_user_type VARCHAR(1000);
222 l_user_id NUMBER;
223 l_user_type_id NUMBER;
224 l_user_reg_id NUMBER;
225 l_approval_id NUMBER;
226 l_enrollment_id NUMBER;
227 l_enrollment_reg_id NUMBER;
228 l_exists_user_name VARCHAR2(1000);
229 l_principal_name VARCHAR2(1000);
230 -- Other OUT parameters returned by the API.
231 l_return_status VARCHAR2(1);
232 l_msg_count NUMBER;
233 l_msg_data VARCHAR2(2000);
234 l_usertype_resp_id NUMBER;
235 l_usertype_app_id NUMBER;
236 l_last_update_date VARCHAR2(2000);
237 l_gen_password VARCHAR2(2000);
238 l_version NUMBER;
239 l_pass_length NUMBER;
240 l_wf_item_type varchar2(200);
241 l_LOGIN_USER_ID NUMBER := FND_GLOBAL.USER_ID;
242 l_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
243 --x_usertype_reg_id NUMBER;
244 l_respKey varchar2(50) := 'JTF_PENDING_APPROVAL';
245 l_status varchar2(10) := 'PENDING';
246 l_application_id number := 690;
247 l_usertype_key varchar2(100);
248 l_resp_id NUMBER;
249 l_app_id NUMBER;
250 l_partner_id NUMBER;
251 l_mode VARCHAR(50) := 'CREATE';
252 l_party_site_id NUMBER;
253 l_contact_point_id NUMBER;
254 l_party_site_number VARCHAR2(1000);
255 l_cust_acct_role_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
256 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
257 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
258 l_location_id NUMBER;
259 l_log_msg LOG_MESSAGE_TBL_TYPE;
260 l_log_count NUMBER:=0;
261 l_attr_log_msg LOG_MESSAGE_TBL_TYPE;
262 l_attr_status VARCHAR2(100);
263 l_partner_cust_acct VARCHAR2(100);
264 l_contact_cust_acct VARCHAR2(100);
265 l_cust_account_role_id NUMBER;
266 l_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
267 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
268 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
269 l_cust_account_id NUMBER;
270 l_cust_account_number VARCHAR2(1000);
271 l_cust_party_id NUMBER;
272 l_cust_party_number VARCHAR2(1000);
273 l_cust_profile_id NUMBER;
274 l_account_number VARCHAR2(100);
275 l_party_name VARCHAR2(1000);
276
277
278
279
280
281 CURSOR user_type_id (user_type VARCHAR2) IS
282 select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
283
284
285
286 CURSOR enrollment_id (user_type VARCHAR2) IS
287 select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
288 where a.subscription_id=b.subscription_id
289 and c.usertype_key=user_type
290 and a.usertype_id=c.usertype_id;
291
292 cursor getLUDFromUserReg(l_user_reg_id VARCHAR2) is
293 select to_char (last_update_date, 'mmddyyyyhh24miss')
294 from jtf_um_usertype_reg
295 where usertype_reg_id = to_number (l_user_reg_id);
296
297 CURSOR CHECK_PARTNER(l_party_id VARCHAR2) IS
298 select partner_id, party_name from pv_partner_profiles,hz_parties where partner_party_id=l_party_id and party_id=partner_party_id;
299
300 CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
301 FROM JTF_UM_USERTYPE_RESP UT,
302 FND_RESPONSIBILITY_VL FR,
303 JTF_UM_USERTYPES_B c
304 WHERE c.usertype_key=user_type
305 and UT.USERTYPE_ID = c.usertype_id
306 AND FR.APPLICATION_ID = UT.APPLICATION_ID
307 AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
308 AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
309 AND UT.EFFECTIVE_START_DATE < SYSDATE;
310
311 CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
312 FROM JTF_UM_USERTYPE_ROLE
313 WHERE USERTYPE_ID = usertype_id
314 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
315 AND EFFECTIVE_START_DATE < SYSDATE;
316
317 CURSOR GET_PARTNER_CUST_ACCT (l_party_id VARCHAR2) IS
318
319 SELECT CUST_ACCOUNT_ID from hz_cust_Accounts where party_id=l_party_id;
320
321 CURSOR GET_CONTACT_CUST_ROLE (l_party_id VARCHAR2) IS
322
323 SELECT CUST_ACCOUNT_ROLE_ID from hz_cust_account_roles where party_id=l_party_id;
324
325
326 CURSOR CHECK_RELATIONSHIP_EXIST(p_person_party_id NUMBER, p_partner_party_id NUMBER) IS
327 select hzrp.party_id from hz_org_contacts hzoc, hz_relationships hzrp
328 where hzoc.party_relationship_id = hzrp.relationship_id and
329 hzrp.relationship_code='EMPLOYEE_OF' and
330 hzrp.subject_id=p_person_party_id and
331 hzrp.object_id=p_partner_party_id
332 and hzrp.start_date <= sysdate
333 and (hzrp.end_date is null or hzrp.end_date > sysdate);
334
335
336
337 CURSOR get_party_id_from_ref(orig_system IN VARCHAR2, orig_system_ref IN VARCHAR2, l_party_type IN VARCHAR2) IS
338 SELECT
339 HZ_PARTIES.PARTY_ID
340 FROM
341 HZ_ORIG_SYS_REFERENCES,
342 HZ_PARTIES
343 WHERE
344 HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
345 HZ_PARTIES.PARTY_TYPE = l_party_type AND
346 HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
347 HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
348 HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
349 HZ_PARTIES.STATUS = 'A';
350
351
352
353 CURSOR get_party_id_all_data(orig_system IN VARCHAR2, orig_system_ref IN VARCHAR2,l_party_id VARCHAR2, l_party_type IN VARCHAR2) IS
354 SELECT
355 HZ_PARTIES.PARTY_ID
356 FROM
357 HZ_ORIG_SYS_REFERENCES,
358 HZ_PARTIES
359 WHERE
360 HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
361 HZ_PARTIES.PARTY_TYPE = l_party_type AND
362 HZ_PARTIES.party_id = l_party_id and
363 HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
364 HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
365 HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
366 HZ_PARTIES.STATUS = 'A';
367
368
369
370
371
372 CURSOR get_party_id_only_sys(orig_system IN VARCHAR2, l_party_id VARCHAR2, l_party_type IN VARCHAR2) IS
373 SELECT
374 HZ_PARTIES.PARTY_ID
375 FROM
376 HZ_ORIG_SYS_REFERENCES,
377 HZ_PARTIES
378 WHERE
379 HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
380 HZ_PARTIES.PARTY_TYPE = l_party_type AND
381 HZ_PARTIES.party_id = l_party_id and
382 HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
383 HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
384 HZ_PARTIES.STATUS = 'A';
385
386
387 CURSOR get_party_id_only_ref( orig_system_ref IN VARCHAR2,l_party_id VARCHAR2, l_party_type IN VARCHAR2) IS
388 SELECT
389 HZ_PARTIES.PARTY_ID
390 FROM
391 HZ_ORIG_SYS_REFERENCES,
392 HZ_PARTIES
393 WHERE
394 HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
395 HZ_PARTIES.PARTY_TYPE = l_party_type AND
396 HZ_PARTIES.party_id = l_party_id and
397 HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
398 HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
399 HZ_PARTIES.STATUS = 'A';
400
401
402
403
404
405 CURSOR USER_EXISTS_FOR_CONTACT (l_rel_party_id VARCHAR2) IS
406 select user_name from fnd_user where customer_id=l_rel_party_id;
407
408 CURSOR USER_EXISTS_FOR_PERSON (l_person_party_id VARCHAR2) IS
409 select user_name from fnd_user where person_party_id=l_person_party_id;
410
411
412 BEGIN
413
414
415 SAVEPOINT contact_create_pvt;
416
417 -- dbms_output.put_line('Entered the Contact create API');
418 l_msg_count:=0;
419 l_msg_data:= null;
420 l_contact_create_ok := 'TRUE';
421 x_contact_output_rec.Prtnr_orig_system:=p_contact_details_rec.Prtnr_orig_system;
422 x_contact_output_rec.Prtnr_orig_system_reference:=p_contact_details_rec.Prtnr_orig_system_reference;
423 x_contact_output_rec.partner_party_id:=p_contact_details_rec.partner_party_id;
424 x_contact_output_rec.Cnt_orig_system:=p_contact_details_rec.Cnt_orig_system;
425 x_contact_output_rec.Cnt_orig_system_reference:=p_contact_details_rec.Cnt_orig_system_reference;
426 x_contact_output_rec.person_party_id:=p_contact_details_rec.person_party_id;
427 x_contact_output_rec.return_status:='NOT_PROCESSED';
428
429
430 /***************** CREATE CONTACT DETAILS ***********************************************/
431
432
433 /***************** GET PARTNER PARTY ID from original system ref ********************************/
434
435 if l_partner_party_id is null then
436
437
438 if p_contact_details_rec.Prtnr_orig_system is null OR p_contact_details_rec.Prtnr_orig_system_reference is null then
439 l_contact_create_ok:= 'FALSE';
440 fnd_message.set_name('PV', 'PV_INVALID_PARTNER_ORG_REF');
441 l_log_count:= l_log_count + 1;
442 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
443 else
444 OPEN get_party_id_from_ref(p_contact_details_rec.Prtnr_orig_system, p_contact_details_rec.Prtnr_orig_system_reference,'ORGANIZATION' ) ;
445 FETCH get_party_id_from_ref INTO l_partner_party_id;
446 CLOSE get_party_id_from_ref;
447 end if;
448
449
450 elsif l_partner_party_id is not null then
451
452 if p_contact_details_rec.Prtnr_orig_system is not null and p_contact_details_rec.Prtnr_orig_system_reference is not null then
453 OPEN get_party_id_all_data(p_contact_details_rec.Prtnr_orig_system, p_contact_details_rec.Prtnr_orig_system_reference ,l_partner_party_id,'ORGANIZATION' ) ;
454 FETCH get_party_id_all_data INTO l_partner_party_id;
455 CLOSE get_party_id_all_data;
456
457 elsif p_contact_details_rec.Prtnr_orig_system is not null then
458 OPEN get_party_id_only_sys(p_contact_details_rec.Prtnr_orig_system, l_partner_party_id ,'ORGANIZATION') ;
459 FETCH get_party_id_only_sys INTO l_partner_party_id;
460 CLOSE get_party_id_only_sys;
461
462 elsif p_contact_details_rec.Prtnr_orig_system_reference is not null then
463 OPEN get_party_id_only_ref(p_contact_details_rec.Prtnr_orig_system_reference, l_partner_party_id,'ORGANIZATION' ) ;
464 FETCH get_party_id_only_ref INTO l_partner_party_id;
465 CLOSE get_party_id_only_ref;
466
467
468 end if;
469
470
471
472 end if;
473
474
475 /****************** End get party id ****************************************************/
476
477 -- dbms_output.put_line('Partner Party ID is '|| l_partner_party_id);
478
479 /***************** GET PERSON PARTY ID from original system ref ********************************/
480
481 if l_person_party_id is null then
482
483
484 if p_contact_details_rec.Cnt_orig_system is null OR p_contact_details_rec.Cnt_orig_system_reference is null then
485 l_contact_create_ok:= 'FALSE';
486 fnd_message.set_name('PV', 'PV_INVALID_CONTACT_REF');
487 l_log_count:= l_log_count + 1;
488 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
489 else
490 OPEN get_party_id_from_ref(p_contact_details_rec.Cnt_orig_system, p_contact_details_rec.Cnt_orig_system_reference,'PERSON' ) ;
491 FETCH get_party_id_from_ref INTO l_person_party_id;
492 CLOSE get_party_id_from_ref;
493 end if;
494
495
496 elsif l_person_party_id is not null then
497
498 if p_contact_details_rec.Cnt_orig_system is not null and p_contact_details_rec.Cnt_orig_system_reference is not null then
499 OPEN get_party_id_all_data(p_contact_details_rec.Cnt_orig_system, p_contact_details_rec.Cnt_orig_system_reference ,l_person_party_id,'PERSON' ) ;
500 FETCH get_party_id_all_data INTO l_person_party_id;
501 CLOSE get_party_id_all_data;
502
503 elsif p_contact_details_rec.Cnt_orig_system is not null then
504 OPEN get_party_id_only_sys(p_contact_details_rec.Cnt_orig_system, l_person_party_id ,'PERSON') ;
505 FETCH get_party_id_only_sys INTO l_person_party_id;
506 CLOSE get_party_id_only_sys;
507
508 elsif p_contact_details_rec.Cnt_orig_system_reference is not null then
509 OPEN get_party_id_only_ref(p_contact_details_rec.Cnt_orig_system_reference, l_person_party_id,'PERSON' ) ;
510 FETCH get_party_id_only_ref INTO l_person_party_id;
511 CLOSE get_party_id_only_ref;
512
513
514 end if;
515
516
517
518 end if;
519
520 /****************** End get party id ****************************************************/
521 -- dbms_output.put_line('Person Party ID is '|| l_person_party_id);
522
523
524
525 if l_partner_party_id is null then
526 l_contact_create_ok:= 'FALSE';
527 fnd_message.set_name('PV', 'PV_INVALID_PARTNER_ORG_REF');
528 l_log_count:= l_log_count + 1;
529 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
530
531 END IF;
532
533 if l_person_party_id is null then
534 l_contact_create_ok:= 'FALSE';
535 fnd_message.set_name('PV', 'PV_INVALID_CONTACT_REF');
536 l_log_count:= l_log_count + 1;
537 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
538 END IF;
539
540
541 -- dbms_output.put_line('Check if partner exists');
542
543 OPEN CHECK_PARTNER(l_partner_party_id) ;
544 FETCH CHECK_PARTNER INTO l_partner_id,l_party_name;
545 CLOSE CHECK_PARTNER;
546
547
548 if l_partner_id is null then
549 l_contact_create_ok:= 'FALSE';
550 fnd_message.set_name('PV', 'PV_MISSING_PARTNER_ID');
551 l_log_count:= l_log_count + 1;
552 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
553
554 END IF;
555
556 -- dbms_output.put_line('Before check relationship');
557
558
559 OPEN CHECK_RELATIONSHIP_EXIST(l_person_party_id,l_partner_party_id) ;
560 FETCH CHECK_RELATIONSHIP_EXIST INTO l_rel_party_id;
561 CLOSE CHECK_RELATIONSHIP_EXIST;
562
563 -- dbms_output.put_line('after check relationship' || l_rel_party_id);
564
565 if(l_rel_party_id is not NULL ) THEN
566 l_mode:='UPDATE';
567 END IF;
568
569
570 if l_mode='CREATE' then
571
572 if p_contact_details_rec.email_rec.email_address is null then
573 l_contact_create_ok:= 'FALSE';
574 fnd_message.set_name('PV', 'PV_MISSING_EMAIL_ID');
575 l_log_count:= l_log_count + 1;
576 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
577
578 END IF;
579
580
581 if p_contact_details_rec.business_phone_rec.phone_number is null then
582 l_contact_create_ok:= 'FALSE';
583 fnd_message.set_name('PV', 'PV_MISSING_PHONE');
584 l_log_count:= l_log_count + 1;
585 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
586
587 END IF;
588
589 end if;
590
591 if l_contact_create_ok = 'FALSE' THEN
592 x_contact_output_rec.return_status:='ERROR';
593 x_log_msg:= l_log_msg;
594 RETURN;
595 END IF;
596
597
598
599
600
601
602 -- dbms_output.put_line('Mode = ' || l_mode);
603 -- dbms_output.put_line('l_update_allowed = ' || l_update_allowed);
604
605 if l_mode = 'UPDATE' and l_update_allowed = FND_API.G_FALSE then
606 l_contact_create_ok:= 'FALSE';
607 fnd_message.set_name('PV', 'PV_CONTACT_EXISTS_ALREADY');
608 l_log_count:= l_log_count + 1;
609 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
610
611 x_contact_output_rec.return_status:='ERROR';
612 x_log_msg:=l_log_msg;
613 RETURN;
614 end if;
615
616 if l_rel_party_id is null then
617
618 l_party_rel_rec.subject_id := l_person_party_ID;
619 l_party_rel_rec.subject_type := 'PERSON';
620 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
621
622 -- pass organization_party_id as object_id
623 l_party_rel_rec.object_id := l_partner_party_ID;
624 l_party_rel_rec.object_type := 'ORGANIZATION';
625 l_party_rel_rec.object_table_name := 'HZ_PARTIES';
626
627 l_party_rel_rec.relationship_type := 'EMPLOYMENT';
628 l_party_rel_rec.relationship_code := 'EMPLOYEE_OF';
629
630 l_party_rel_rec.start_date:= sysdate;
631 l_party_rel_rec.created_by_module := 'PV';
632 l_party_rel_rec.application_id := 691;
633
634 l_org_contact_rec.party_rel_rec := l_party_rel_rec;
635 l_org_contact_rec.created_by_module := 'PV';
636 l_org_contact_rec.application_id := 691;
637
638
639 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
640 p_org_contact_rec => l_org_contact_rec,
641 x_org_contact_id => l_org_contact_party_id,
642 x_party_rel_id => l_rel_id,
643 x_party_id => l_rel_party_id,
644 x_party_number => l_rel_party_number,
645 x_return_status => l_return_status,
646 x_msg_count => l_msg_count,
647 x_msg_data => l_msg_data);
648
649 -- dbms_output.put_line('after create org contact' || l_rel_party_id);
650 -- dbms_output.put_line('after create org contact status' || l_return_status);
651
652
653
654
655 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
656
657 l_contact_create_ok := 'FALSE';
658 x_contact_output_rec.return_status:='ERROR';
659
660
661 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
662 /* FOR l_msg_index IN 1..l_msg_count LOOP
663 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
664 -- dbms_output.put_line(fnd_message.get);
665 l_log_count:= l_log_count + 1;
666 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
667 END LOOP;
668 */
669
670 l_log_count:= l_log_count + 1;
671 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_count));
672 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
673
674
675 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
676 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
677 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_PARTY_CONTACT_V2PUB.create_org_contact');
678 l_log_count:= l_log_count + 1;
679 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
680 END IF;
681
682
683
684
685
686 END IF;
687
688
689
690
691
692
693
694
695 /**************CREATE LOCATION ******************************************************/
696
697
698 if ( l_contact_details_rec.location_rec.address1 is not null and l_contact_details_rec.location_rec.country is not null) then
699
700 l_contact_details_rec.location_rec.created_by_module:='PV';
701 l_contact_details_rec.location_rec.application_id:=691;
702
703 HZ_LOCATION_V2PUB.create_location (
704 p_init_msg_list =>FND_API.g_false,
705 p_location_rec =>l_contact_details_rec.location_rec,
706 x_location_id =>l_location_id,
707 x_return_status =>l_return_status,
708 x_msg_count =>l_msg_count,
709 x_msg_data =>l_msg_data
710 );
711
712
713
714 -- dbms_output.put_line('after create location' || l_location_id);
715 -- dbms_output.put_line('after create location' || l_return_status);
716
717
718
719 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
720 l_contact_create_ok := 'FALSE';
721 x_contact_output_rec.return_status:='ERROR';
722 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
723 FOR l_msg_index IN 1..l_msg_count LOOP
724 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
725 -- dbms_output.put_line(fnd_message.get);
726 l_log_count:= l_log_count + 1;
727 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
728 END LOOP;
729 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
730 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
731 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_LOCATION_V2PUB.create_location');
732 l_log_count:= l_log_count + 1;
733 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
734 END IF;
735 END IF;
736
737
738 l_party_site_rec.party_id:=l_rel_party_id;
739 l_party_site_rec.location_id:=l_location_id;
740 l_party_site_rec.created_by_module:='PV';
741 l_party_site_rec.application_id:=691;
742 l_party_site_rec.identifying_address_flag:='Y';
743 l_party_site_rec.status:='A';
744
745
746
747 HZ_PARTY_SITE_V2PUB.create_party_site (
748 p_init_msg_list =>FND_API.g_false,
749 p_party_site_rec =>l_party_site_rec,
750 x_party_site_id =>l_party_site_id,
751 x_party_site_number =>l_party_site_number,
752 x_return_status =>l_return_status,
753 x_msg_count =>l_msg_count,
754 x_msg_data =>l_msg_data
755 );
756
757
758 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
759 l_contact_create_ok := 'FALSE';
760 x_contact_output_rec.return_status:='ERROR';
761 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
762 FOR l_msg_index IN 1..l_msg_count LOOP
763 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
764 -- dbms_output.put_line(fnd_message.get);
765 l_log_count:= l_log_count + 1;
766 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
767 END LOOP;
768 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
769 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
770 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_PARTY_SITE_V2PUB.create_party_site');
771 l_log_count:= l_log_count + 1;
772 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
773 END IF;
774 END IF;
775
776
777 -- dbms_output.put_line('after create party site' || l_party_site_id);
778 -- dbms_output.put_line('after create party site' || l_return_status);
779
780
781 END IF; -- address1 and country are passed.
782
783 /****************END LOCATION *******************************************************/
784
785
786
787
788 /********************CREATE PHONE CONTACT POINT *************************************/
789
790
791
792
793
794
795 l_contact_details_rec.phone_contact_point_rec.status := 'A';
796 l_contact_details_rec.phone_contact_point_rec.owner_table_name := 'HZ_PARTIES';
797 l_contact_details_rec.phone_contact_point_rec.owner_table_id := l_rel_party_id;
798 l_contact_details_rec.phone_contact_point_rec.created_by_module := 'PV';
799 l_contact_details_rec.phone_contact_point_rec.application_id := 691;
800 l_contact_details_rec.phone_contact_point_rec.primary_flag :='Y';
801 l_contact_details_rec.phone_contact_point_rec.contact_point_purpose := 'BUSINESS';
802
803
804 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
805 p_contact_point_rec => l_contact_details_rec.phone_contact_point_rec,
806 p_phone_rec => l_contact_details_rec.business_phone_rec,
807 x_contact_point_id => l_contact_point_id,
808 x_return_status => l_return_status,
809 x_msg_count => l_msg_count,
810 x_msg_data => l_msg_data );
811
812
813
814
815 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
816 l_contact_create_ok := 'FALSE';
817 x_contact_output_rec.return_status:='ERROR';
818 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
819 FOR l_msg_index IN 1..l_msg_count LOOP
820 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
821 -- -- dbms_output.put_line('Printing first time' || fnd_message.get);
822 l_log_count:= l_log_count + 1;
823 -- l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
824 l_log_msg(l_log_count):=fnd_message.get;
825 -- dbms_output.put_line('printing log ' || l_log_msg(l_log_count));
826 END LOOP;
827 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
828 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
829 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_CONTACT_POINT_V2PUB.create_phone_contact_point');
830 l_log_count:= l_log_count + 1;
831 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
832 END IF;
833 END IF;
834
835 -- dbms_output.put_line('after create phone' || l_contact_point_id);
836 -- dbms_output.put_line('after create phone' || l_return_status);
837
838 /*********************END PHONE CONTACT POINT ******************************************/
839
840
841
842
843
844 /*********************CREATE EMAIL CONTACT POINT ***************************************/
845
846
847
848 l_contact_details_rec.email_contact_point_rec.status := 'A';
849 l_contact_details_rec.email_contact_point_rec.owner_table_name := 'HZ_PARTIES';
850 l_contact_details_rec.email_contact_point_rec.owner_table_id := l_rel_party_id;
851 l_contact_details_rec.email_contact_point_rec.created_by_module := 'PV';
852 l_contact_details_rec.email_contact_point_rec.application_id := 691;
853 l_contact_details_rec.email_contact_point_rec.primary_flag :='Y';
854 l_contact_details_rec.email_contact_point_rec.contact_point_type :='EMAIL';
855 l_contact_details_rec.email_contact_point_rec.contact_point_purpose :='';
856
857
858 HZ_CONTACT_POINT_V2PUB.create_email_contact_point (
859 p_contact_point_rec => l_contact_details_rec.email_contact_point_rec,
860 p_email_rec => l_contact_details_rec.email_rec,
861 x_contact_point_id => l_contact_point_id,
862 x_return_status => l_return_status,
863 x_msg_count => l_msg_count,
864 x_msg_data => l_msg_data );
865
866
867 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
868 l_contact_create_ok := 'FALSE';
869 x_contact_output_rec.return_status:='ERROR';
870 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
871 FOR l_msg_index IN 1..l_msg_count LOOP
872 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
873 -- dbms_output.put_line(fnd_message.get);
874 l_log_count:= l_log_count + 1;
875 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
876 END LOOP;
877 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
878 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
879 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_CONTACT_POINT_V2PUB.create_email_contact_point');
880 l_log_count:= l_log_count + 1;
881 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
882 END IF;
883 END IF;
884
885 -- dbms_output.put_line('after create email' || l_contact_point_id);
886 -- dbms_output.put_line('after create email' || l_return_status);
887
888 /**********************END EMAIL CONTACT POINT ******************************************/
889
890
891
892
893
894 END IF; --END OF CREATE MODE FOR RELATIONSHIP
895
896
897 /********************************CREATE CUST Account Role **********************************/
898
899
900
901 -- dbms_output.put_line('Partner party id before cust account is ' || l_partner_party_id);
902
903 OPEN GET_PARTNER_CUST_ACCT(l_partner_party_id) ;
904 FETCH GET_PARTNER_CUST_ACCT INTO l_partner_cust_acct;
905 CLOSE GET_PARTNER_CUST_ACCT;
906
907
908
909
910 OPEN GET_CONTACT_CUST_ROLE(l_rel_party_id) ;
911 FETCH GET_CONTACT_CUST_ROLE INTO l_contact_cust_acct;
912 CLOSE GET_CONTACT_CUST_ROLE;
913
914 -- dbms_output.put_line('Partner party cust account' || l_partner_cust_acct);
915
916
917
918 OPEN GET_PARTNER_CUST_ACCT(l_partner_id) ;
919 FETCH GET_PARTNER_CUST_ACCT INTO l_partner_cust_acct;
920 CLOSE GET_PARTNER_CUST_ACCT;
921
922
923 -- dbms_output.put_line('Partner cust account' || l_partner_cust_acct);
924
925
926
927
928
929
930
931
932 if l_partner_cust_acct is null then
933 l_account_rec.Created_by_Module := 'PV';
934 l_account_rec.application_id := 691;
935 l_organization_rec.Created_by_Module := 'PV';
936 l_organization_rec.application_id := 691;
937 l_cust_profile_rec.Created_by_Module := 'PV';
938 l_cust_profile_rec.application_id := 691;
939 l_account_rec.account_name := l_party_name;
940 l_organization_rec.party_rec.party_id := l_partner_party_id;
941
942
943
944 -- dbms_output.put_line('Just before the Partner cust account API' );
945
946 HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
947 p_init_msg_list => FND_API.G_TRUE,
948 p_cust_account_rec => l_account_rec,
949 p_organization_rec => l_organization_rec,
950 p_customer_profile_rec => l_cust_profile_rec,
951 p_create_profile_amt => FND_API.G_TRUE,
952 x_cust_account_id => l_partner_cust_acct,
953 x_account_number => l_account_number,
954 x_party_id => l_cust_party_id ,
955 x_party_number => l_cust_party_number,
956 x_profile_id => l_cust_profile_id,
957 x_return_status => l_return_status,
958 x_msg_count => l_msg_count,
959 x_msg_data => l_msg_data );
960
961
962
963 -- dbms_output.put_line('Partner cust account Return status' || l_return_status);
964 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
965 l_contact_create_ok := 'FALSE';
966 x_contact_output_rec.return_status:='ERROR';
967 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
968 FOR l_msg_index IN 1..l_msg_count LOOP
969 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
970 -- dbms_output.put_line(fnd_message.get);
971 l_log_count:= l_log_count + 1;
972 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
973 END LOOP;
974 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
975 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
976 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_CONTACT_POINT_V2PUB.create_email_contact_point');
977 l_log_count:= l_log_count + 1;
978 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
979 END IF;
980 END IF;
981
982
983
984
985
986 end if;
987
988
989
990
991
992
993 if l_contact_cust_acct is null and l_partner_cust_acct is not null then
994
995 l_cust_acct_role_rec.party_id:=l_rel_party_id;
996 l_cust_acct_role_rec.cust_account_id:= l_partner_cust_acct;
997 l_cust_acct_role_rec.role_type:='CONTACT';
998 l_cust_acct_role_rec.primary_flag:='N';
999 l_cust_acct_role_rec.created_by_module:='PV';
1000
1001
1002
1003 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
1004 p_init_msg_list =>'T',
1005 p_cust_account_role_rec => l_cust_acct_role_rec,
1006 x_cust_account_role_id =>l_cust_account_role_id,
1007 x_return_status => l_return_status,
1008 x_msg_count => l_msg_count,
1009 x_msg_data => l_msg_data );
1010
1011
1012 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1013 l_contact_create_ok := 'FALSE';
1014 x_contact_output_rec.return_status:='ERROR';
1015 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1016 FOR l_msg_index IN 1..l_msg_count LOOP
1017 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
1018 -- dbms_output.put_line(fnd_message.get);
1019 l_log_count:= l_log_count + 1;
1020 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1021 END LOOP;
1022 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1023 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1024 FND_MESSAGE.SET_TOKEN('API_NAME', 'HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role');
1025 l_log_count:= l_log_count + 1;
1026 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1027 END IF;
1028 END IF;
1029
1030
1031 end if ; -- Create contact cust account.
1032
1033 /******************************** END CREATE CUST Account Role **********************************/
1034
1035
1036 /************************************************************* CREATE ATTRIBUTES *****************************************************/
1037
1038
1039 -- dbms_output.put_line('before attributes');
1040
1041 if(l_contact_details_rec.attribute_details_tbl.count > 0 ) then
1042
1043 PV_CONTACT_USER_BATCH_PUB.upsert_attributes(
1044 p_api_version_number =>1.0
1045 ,p_init_msg_list =>'T'
1046 ,p_commit =>'F'
1047 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1048 ,p_attr_details_tbl => l_contact_details_rec.attribute_details_tbl
1049 ,p_contact_rel_id => l_rel_party_id
1050 ,x_log_msg => l_attr_log_msg
1051 ,x_attribute_creation_status => l_attr_status
1052 );
1053
1054 end if;
1055
1056
1057 -- dbms_output.put_line('just after attributes ' ||l_attr_status );
1058
1059
1060 IF (l_attr_status = 'ERROR') THEN
1061 l_contact_create_ok := 'FALSE';
1062 x_contact_output_rec.return_status:='ERROR';
1063
1064 FOR l_msg_index IN 1..l_attr_log_msg.LAST LOOP
1065 l_log_count:= l_log_count + 1;
1066 l_log_msg(l_log_count):=l_attr_log_msg(l_msg_index);
1067 END LOOP;
1068 END IF;
1069 -- dbms_output.put_line('after logging attributes');
1070
1071
1072
1073
1074 /************************************************************** END CREATE ATTRIBUTES ***********************************************/
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090 -- dbms_output.put_line('user name not null check ');
1091 if(l_contact_details_rec.user_name is not null) THEN
1092
1093 /**************** CHECK FOR SSO ************************************************************************/
1094 l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
1095 if l_sso_enabled = 'N' OR l_sso_enabled = 'FTTT' OR l_sso_enabled = 'FFFF' then
1096 l_contact_create_ok:= 'FALSE';
1097 x_contact_output_rec.return_status:='ERROR';
1098 fnd_message.set_name('PV', 'PV_SSO_CREATE_USER_NOT_ALLOWED');
1099 l_log_count:= l_log_count + 1;
1100 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1101
1102 end if;
1103
1104 -- dbms_output.put_line('after sso check ');
1105
1106 /**************** CHECK IF USER EXISTS ALREADY FOR CONTACT ************************************************************************/
1107
1108
1109 l_exists_user_name:= null;
1110 OPEN USER_EXISTS_FOR_CONTACT(l_rel_party_id) ;
1111 FETCH USER_EXISTS_FOR_CONTACT INTO l_exists_user_name;
1112 CLOSE USER_EXISTS_FOR_CONTACT;
1113
1114 if l_mode = 'UPDATE' and l_update_allowed = FND_API.G_FALSE then
1115
1116 if l_exists_user_name is not null then
1117 l_contact_create_ok:= 'FALSE';
1118 x_contact_output_rec.return_status:='ERROR';
1119 fnd_message.set_name('PV', 'PV_CONTACT_USER_EXISTS');
1120 l_log_count:= l_log_count + 1;
1121 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1122
1123
1124 end if;
1125
1126 elsif l_mode ='UPDATE' and l_update_allowed =FND_API.G_TRUE then
1127
1128 if l_contact_details_rec.user_name <> l_exists_user_name then
1129 l_contact_create_ok:= 'FALSE';
1130 x_contact_output_rec.return_status:='ERROR';
1131 fnd_message.set_name('PV', 'PV_USER_DIFFERENT');
1132 l_log_count:= l_log_count + 1;
1133 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1134 end if;
1135 end if;
1136 -- dbms_output.put_line('after user exists for contact check ');
1137
1138 /**************** CHECK IF USER TYPE IS PASSED ************************************************************************/
1139
1140 -- dbms_output.put_line('after user type check ');
1141
1142 l_user_type:=l_contact_details_rec.user_type;
1143
1144 if l_user_type is null then
1145 l_contact_create_ok:= 'FALSE';
1146 x_contact_output_rec.return_status:='ERROR';
1147
1148 fnd_message.set_name('PV', 'PV_USER_TYPE_REQUIRED');
1149 l_log_count:= l_log_count + 1;
1150 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1151 end if;
1152
1153 -- dbms_output.put_line('after user type check ');
1154
1155
1156 /**********************TEST USER NAME ***********************************************************************************/
1157
1158 -- dbms_output.put_line('Test user name');
1159 if l_update_allowed = FND_API.G_FALSE OR l_mode='CREATE' then
1160
1161 l_test_user_return_code := FND_USER_PKG.TestUserName(l_contact_details_rec.user_name);
1162 if l_test_user_return_code = 1 then
1163 l_contact_create_ok:= 'FALSE';
1164 x_contact_output_rec.return_status:='ERROR';
1165 fnd_message.set_name('FND','INVALID_USER_NAME');
1166 l_log_count:= l_log_count + 1;
1167 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1168
1169 elsif l_test_user_return_code = 2 then
1170 l_contact_create_ok:= 'FALSE';
1171 x_contact_output_rec.return_status:='ERROR';
1172 fnd_message.set_name('FND','FND_USER_EXISTS_IN_FND');
1173 l_log_count:= l_log_count + 1;
1174 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1175
1176 elsif l_test_user_return_code = 4 then
1177 l_contact_create_ok:= 'FALSE';
1178 x_contact_output_rec.return_status:='ERROR';
1179 fnd_message.set_name('FND','FND_USER_EXISTS_NO_LINK');
1180 l_log_count:= l_log_count + 1;
1181 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1182
1183 end if;
1184 end if;
1185
1186 /************************END TEST USER NAME *****************************************************************************/
1187
1188
1189
1190
1191
1192
1193 /**************** IF ANYTHING HAS FAILED SO FAR ROLLBACK ************************************************************************/
1194
1195 if l_contact_create_ok= 'FALSE' then
1196 x_contact_output_rec.return_status:='ERROR';
1197 -- dbms_output.put_line('going to rollback ');
1198 ROLLBACK TO contact_create_pvt;
1199 x_log_msg:=l_log_msg;
1200 RETURN;
1201 end if;
1202
1203
1204 -- dbms_output.put_line('user type = ' || l_user_type);
1205
1206
1207
1208 if l_exists_user_name is null then
1209 l_password:= l_contact_details_rec.password;
1210 PV_CONTACT_USER_BATCH_PUB.user_create (
1211 p_api_version_number =>1.0
1212 ,p_init_msg_list =>'T'
1213 ,p_commit =>'F'
1214 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1215 ,p_user_name => l_contact_details_rec.user_name
1216 ,p_password => l_password
1217 ,p_user_type_key =>l_user_type
1218 ,p_contact_rel_id =>l_rel_party_id
1219 ,x_return_status => l_return_status
1220 ,x_msg_data => l_msg_data
1221 ,x_msg_count => l_msg_count
1222 ) ;
1223
1224 else
1225
1226 PV_CONTACT_USER_BATCH_PUB.user_update (
1227 p_api_version_number =>1.0
1228 ,p_init_msg_list => FND_API.G_TRUE
1229 ,p_commit => FND_API.G_FALSE
1230 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1231 ,p_user_name => l_contact_details_rec.user_name
1232 ,p_user_type_key => l_user_type
1233 ,p_contact_rel_id => l_rel_party_id
1234 ,x_return_status => l_return_status
1235 ,x_msg_data => l_msg_data
1236 ,x_msg_count => l_msg_count
1237
1238 );
1239
1240 end if;
1241 -- dbms_output.put_line('after user creation ' || l_return_status);
1242
1243
1244 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1245 l_contact_create_ok := 'FALSE';
1246 x_contact_output_rec.return_status:='ERROR';
1247 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1248 FOR l_msg_index IN 1..l_msg_count LOOP
1249 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
1250 -- dbms_output.put_line(fnd_message.get);
1251 l_log_count:= l_log_count + 1;
1252 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1253 END LOOP;
1254 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1255 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1256 FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_CONTACT_USER_BATCH_PUB.user_create');
1257 l_log_count:= l_log_count + 1;
1258 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1259 END IF;
1260 END IF;
1261
1262
1263 END IF ; --- USER CREATION ENDS
1264
1265
1266
1267
1268 if l_contact_create_ok= 'FALSE' THEN
1269 rollback to contact_create_pvt;
1270 ELSE
1271 x_contact_rel_id:= l_rel_party_id;
1272 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1273 x_contact_output_rec.contact_rel_party_id:=l_rel_party_id;
1274 x_contact_output_rec.return_status:='SUCCESS';
1275 x_contact_output_rec.user_name:=l_contact_details_rec.user_name;
1276 x_contact_output_rec.password:=l_password;
1277 fnd_message.set_name('PV', 'PV_CONTACT_USER_SUCCESS');
1278 l_log_count:= l_log_count + 1;
1279 l_log_msg(l_log_count):=substrb(fnd_message.get, 1, 1000);
1280 END IF;
1281
1282 x_log_msg:=l_log_msg;
1283 RETURN;
1284
1285 EXCEPTION
1286
1287
1288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1289 l_contact_create_ok := 'FALSE';
1290 x_contact_output_rec.return_status:='ERROR';
1291 ROLLBACK TO contact_create_pvt;
1292 x_log_msg:=l_log_msg;
1293 RETURN;
1294 WHEN OTHERS THEN
1295 l_contact_create_ok:= 'FALSE';
1296 x_contact_output_rec.return_status:='ERROR';
1297 ROLLBACK TO contact_create_pvt;
1298 x_log_msg:=l_log_msg;
1299 RETURN;
1300 END contact_create;
1301
1302
1303
1304
1305
1306
1307
1308
1309 PROCEDURE user_create (
1310 p_api_version_number IN NUMBER
1311 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1312 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1313 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1314 ,p_user_name IN VARCHAR2
1315 ,p_password IN OUT NOCOPY VARCHAR2
1316 ,p_user_type_key IN VARCHAR2
1317 ,p_contact_rel_id IN NUMBER
1318 ,x_return_status OUT NOCOPY VARCHAR2
1319 ,x_msg_data OUT NOCOPY VARCHAR2
1320 ,x_msg_count OUT NOCOPY NUMBER
1321 ) IS
1322
1323 L_API_NAME CONSTANT VARCHAR2(30) := 'user_create';
1324 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1325
1326
1327
1328
1329 l_api_version_number NUMBER ;
1330 l_init_msg_list VARCHAR2(1000);
1331 l_commit VARCHAR2(1000);
1332 l_validation_level NUMBER ;
1333 l_password_date DATE;
1334 l_user_name VARCHAR2(1000):=p_user_name;
1335 l_password VARCHAR2(1000):= p_password;
1336 l_contact_rel_id NUMBER :=p_contact_rel_id;
1337 l_user_type_key VARCHAR2(1000):=p_user_type_key;
1338 -- l_user_language VARCHAR2(1000) := p_user_language;
1339
1340 l_partner_id NUMBER;
1341 l_partner_group_id NUMBER;
1342 l_person_first_name VARCHAR2(1000);
1343 l_approval_id NUMBER;
1344 l_enrollment_id NUMBER;
1345 l_enrollment_reg_id NUMBER;
1346 l_person_last_name VARCHAR2(1000);
1347 l_org_contact_id NUMBER;
1348 l_party_name VARCHAR2(1000);
1349 l_email_address VARCHAR2(1000);
1350
1351
1352
1353
1354 l_partner_party_id NUMBER;
1355 l_contact_party_id NUMBER;
1356
1357 l_partner_user_rec PV_USER_MGMT_PVT.Partner_User_Rec_type;
1358 l_person_party_id NUMBER ;
1359 l_rel_party_id NUMBER ;
1360 l_org_party_id NUMBER;
1361 l_org_contact_party_id NUMBER;
1362 l_rel_id NUMBER;
1363 l_rel_party_number NUMBER;
1364
1365 l_user_id NUMBER;
1366 l_user_type_id NUMBER;
1367 l_user_reg_id NUMBER;
1368 l_principal_name VARCHAR2(1000);
1369 -- Other OUT parameters returned by the API.
1370 l_return_status VARCHAR2(1);
1371 l_msg_count NUMBER;
1372 l_msg_data VARCHAR2(2000);
1373 l_usertype_resp_id NUMBER;
1374 l_usertype_app_id NUMBER;
1375 l_last_update_date VARCHAR2(2000);
1376 l_gen_password VARCHAR2(2000);
1377 l_version NUMBER;
1378 l_pass_length NUMBER;
1379
1380 l_sso_enabled VARCHAR2(100);
1381 l_respKey varchar2(50) := 'JTF_PENDING_APPROVAL';
1382 l_status varchar2(10) := 'PENDING';
1383 l_application_id number := 690;
1384 l_usertype_key varchar2(100);
1385 l_resp_id NUMBER;
1386 l_contact_exist_id NUMBER;
1387 l_app_id NUMBER;
1388
1389 l_relationship_code VARCHAR2(1000);
1390 l_party_status VARCHAR2(1000);
1391
1392
1393 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
1394 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
1395
1396 CURSOR user_type_id (user_type VARCHAR2) IS
1397 select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
1398
1399 CURSOR enrollment_id (user_type VARCHAR2) IS
1400 select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
1401 where a.subscription_id=b.subscription_id
1402 and c.usertype_key=user_type
1403 and a.usertype_id=c.usertype_id;
1404
1405 cursor getLUDFromUserReg(l_user_reg_id VARCHAR2) is
1406 select to_char (last_update_date, 'mmddyyyyhh24miss')
1407 from jtf_um_usertype_reg
1408 where usertype_reg_id = to_number (l_user_reg_id);
1409
1410 CURSOR CHECK_PARTNER(l_party_id VARCHAR2) IS
1411 select partner_id from pv_partner_profiles where partner_party_id=l_party_id;
1412
1413
1414
1415
1416 CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
1417 FROM JTF_UM_USERTYPE_RESP UT,
1418 FND_RESPONSIBILITY_VL FR,
1419 JTF_UM_USERTYPES_B c
1420 WHERE c.usertype_key=user_type
1421 and UT.USERTYPE_ID = c.usertype_id
1422 AND FR.APPLICATION_ID = UT.APPLICATION_ID
1423 AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
1424 AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
1425 AND UT.EFFECTIVE_START_DATE < SYSDATE;
1426
1427 CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
1428 FROM JTF_UM_USERTYPE_ROLE
1429 WHERE USERTYPE_ID = usertype_id
1430 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
1431 AND EFFECTIVE_START_DATE < SYSDATE;
1432
1433
1434 CURSOR get_relationship_code(rel_party_id NUMBER) IS
1435 select relationship_code from hz_relationships hzr where hzr.party_id=rel_party_id and hzr.directional_flag='F';
1436
1437
1438 CURSOR get_status(rel_party_id NUMBER) IS
1439 select hzp.status from hz_parties hzp where hzp.party_id=rel_party_id ;
1440
1441
1442 CURSOR get_email_address(rel_party_id NUMBER) IS
1443 select email_address from hz_parties hzp where hzp.party_id=rel_party_id ;
1444
1445
1446
1447
1448 cursor get_contact_details(rel_party_id NUMBER) IS
1449 select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name,
1450 hzoc.org_contact_id, org_hzp.party_name, rel_hzp.email_address
1451 from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP,
1452 hz_parties REL_HZP
1453 where HZR.party_id = rel_party_id
1454 and HZR.directional_flag = 'F'
1455 and hzr.relationship_code = 'EMPLOYEE_OF'
1456 and HZR.subject_table_name ='HZ_PARTIES'
1457 and HZR.object_table_name ='HZ_PARTIES'
1458 and hzr.start_date <= SYSDATE
1459 and (hzr.end_date is null or hzr.end_date > SYSDATE)
1460 and hzr.status = 'A'
1461 and hzr.subject_id = person_hzp.party_id
1462 and person_hzp.status = 'A'
1463 and hzr.object_id = pvpp.partner_party_id
1464 and pvpp.partner_group_id is not null
1465 and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
1466 and hzr.object_id = org_hzp.party_id and
1467 rel_hzp.party_id=hzr.party_id;
1468
1469 CURSOR CHECK_CONTACT(rel_party_id VARCHAR2) is
1470 select hzr.subject_id from hz_parties rel,hz_relationships hzr ,pv_partner_profiles pvpp
1471 where rel.party_id=rel_party_id and
1472 hzr.party_id=rel.party_id and
1473 hzr.relationship_code in ('EMPLOYEE_OF' ) and
1474 hzr.object_id=pvpp.partner_party_id;
1475
1476
1477 cursor resp_list(l_usertype_id VARCHAR2) is
1478 select responsibility_id, application_id
1479 from fnd_responsibility
1480 where responsibility_key in
1481 (select responsibility_key
1482 from jtf_um_usertype_resp jtur
1483 where jtur.usertype_id = l_usertype_id
1484 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
1485 union
1486 select responsibility_key
1487 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_resp jtsr
1488 where jtus.usertype_id = l_usertype_id
1489 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
1490 and jtus.subscription_flag = 'IMPLICIT'
1491 and jtus.subscription_id = jtsr.subscription_id
1492 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate));
1493
1494 cursor role_list(l_usertype_id VARCHAR2) is
1495 select principal_name
1496 from jtf_um_usertype_role jtur
1497 where jtur.usertype_id = l_usertype_id
1498 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
1499 union
1500 select jtsr.principal_name
1501 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
1502 where jtus.usertype_id = l_usertype_id
1503 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
1504 and jtus.subscription_flag = 'IMPLICIT'
1505 and jtus.subscription_id = jtsr.subscription_id
1506 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate);
1507
1508 CURSOR get_user_id(l_user_name VARCHAR2) is
1509 select user_id from fnd_user where user_name=l_user_name;
1510
1511
1512 BEGIN
1513
1514 savepoint user_create_pvt;
1515
1516 -- dbms_output.put_line('l_contact_rel_id ' || l_contact_rel_id);
1517
1518
1519
1520
1521 if l_contact_rel_id is null then
1522 fnd_message.SET_NAME ('PV', 'PV_CONTACT_ID_INVALID');
1523 fnd_msg_pub.ADD;
1524 raise FND_API.G_EXC_ERROR;
1525 END IF;
1526
1527
1528 OPEN CHECK_CONTACT(l_contact_rel_id) ;
1529 FETCH CHECK_CONTACT INTO l_contact_party_id;
1530 CLOSE CHECK_CONTACT;
1531
1532
1533 if l_contact_party_id is null then
1534 fnd_message.SET_NAME ('PV', 'PV_CONTACT_ID_INVALID');
1535 fnd_msg_pub.ADD;
1536 raise FND_API.G_EXC_ERROR;
1537
1538 END IF;
1539
1540
1541
1542 if l_user_name is null then
1543 fnd_message.SET_NAME ('PV', 'PV_USER_NAME_MISSING');
1544 fnd_msg_pub.ADD;
1545 raise FND_API.G_EXC_ERROR;
1546
1547 END IF;
1548
1549 -- dbms_output.put_line('after user name check');
1550
1551
1552
1553 /**************** CHECK FOR SSO ************************************************************************/
1554 l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
1555 if l_sso_enabled = 'N' OR l_sso_enabled = 'FTTT' OR l_sso_enabled = 'FFFF' then
1556 fnd_message.set_name('PV', 'PV_SSO_CREATE_USER_NOT_ALLOWED');
1557 fnd_msg_pub.ADD;
1558 raise FND_API.G_EXC_ERROR;
1559 end if;
1560
1561 -- dbms_output.put_line('after sso check ');
1562
1563 /**************** CHECK IF USER EXISTS ALREADY FOR CONTACT ************************************************************************/
1564
1565
1566
1567
1568
1569
1570 OPEN get_relationship_code(l_contact_rel_id);
1571 FETCH get_relationship_code INTO l_relationship_code;
1572 CLOSE get_relationship_code;
1573
1574
1575
1576
1577
1578 if l_relationship_code is null or l_relationship_code <> 'EMPLOYEE_OF' then
1579 fnd_message.SET_NAME ('PV', 'PV_NOT_EMPLOYEE_REL');
1580 fnd_msg_pub.ADD;
1581 RAISE FND_API.G_EXC_ERROR;
1582 END IF;
1583
1584 -- dbms_output.put_line('After relationship check');
1585
1586
1587
1588
1589 OPEN get_status(l_contact_rel_id);
1590 FETCH get_status INTO l_party_status;
1591 CLOSE get_status;
1592
1593 -- dbms_output.put_line('before party status check');
1594
1595 if l_party_status is null or l_party_status <> 'A' then
1596 fnd_message.SET_NAME ('PV', 'PV_CONTACT_NOT_ACTIVE');
1597 fnd_msg_pub.ADD;
1598 RAISE FND_API.G_EXC_ERROR;
1599
1600 END IF;
1601
1602
1603 -- dbms_output.put_line('BEFORE email address check');
1604
1605 OPEN get_email_address(l_contact_rel_id);
1606 FETCH get_email_address INTO l_email_address;
1607 CLOSE get_email_address;
1608
1609 -- dbms_output.put_line('After email address check1' || l_email_address);
1610
1611
1612
1613
1614
1615
1616 -- dbms_output.put_line('After email address check2' || l_email_address);
1617
1618 if l_email_address is null or l_email_address='' then
1619 l_return_status:='E';
1620 l_msg_count:=1;
1621 l_msg_data:='PV_EMAIL_ID_NEEDED';
1622 RAISE FND_API.G_EXC_ERROR;
1623 END IF;
1624
1625
1626 -- dbms_output.put_line('BEFORE get contact details');
1627
1628 OPEN get_contact_details(l_contact_rel_id);
1629 FETCH get_contact_details INTO l_partner_id, l_PARTNER_GROUP_ID , l_PERSON_FIRST_NAME, l_person_last_name, l_org_contact_id, l_party_name, l_email_address;
1630 CLOSE get_contact_details;
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642 -- dbms_output.put_line('user name not null check ' || l_user_name);
1643 if(l_user_name is not null) THEN
1644
1645
1646
1647
1648 -- dbms_output.put_line('user name IS not null ');
1649
1650 l_password_date:=sysdate;
1651 if(l_password is null OR l_password = '') then
1652
1653 JTF_UM_PASSWORD_PVT.generate_password (
1654
1655 p_api_version_number =>1.0
1656 ,p_init_msg_list =>FND_API.g_false
1657 ,p_commit =>FND_API.g_false
1658 , p_validation_level =>FND_API.G_VALID_LEVEL_FULL
1659 , x_password =>l_gen_password,
1660 x_return_status =>l_return_status,
1661 x_msg_count =>l_msg_count,
1662 x_msg_data =>l_msg_data
1663 );
1664
1665
1666 -- dbms_output.put_line ('After API call'|| l_return_status);
1667 -- dbms_output.put_line('Message count : '||l_msg_count);
1668 -- dbms_output.put_line('password = :' || l_gen_password);
1669 l_password := l_gen_password;
1670 l_password_date := null;
1671 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1672 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1673 RAISE FND_API.G_EXC_ERROR;
1674 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1675 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1676 FND_MESSAGE.SET_TOKEN('API_NAME', 'JTF_UM_PASSWORD_PVT.generate_password ');
1677 FND_MSG_PUB.Add;
1678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1679 END IF;
1680 END IF;
1681
1682
1683 ELSE
1684 l_pass_length:= FND_PROFILE.VALUE('SIGNON_PASSWORD_LENGTH');
1685 if length(l_password) < l_pass_length THEN
1686 l_return_status:='E';
1687 l_msg_count:=1;
1688 l_msg_data:='Password length should be a minimum of '|| l_pass_length;
1689 RAISE FND_API.G_EXC_ERROR;
1690 END IF;
1691
1692 END IF;
1693
1694
1695
1696
1697
1698 FND_USER_PKG.CreateUser(x_user_name => l_user_name,
1699 x_owner => 'SYSTEM',
1700 x_unencrypted_password => l_password,
1701 x_start_date => sysdate,
1702 x_end_date => null,
1703 x_password_date => l_password_date,
1704 x_email_address => l_email_address,
1705 x_customer_id => l_contact_rel_id
1706 );
1707
1708 OPEN get_user_id(l_user_name);
1709 FETCH get_user_id INTO l_user_id;
1710 CLOSE get_user_id;
1711
1712 OPEN user_type_id(l_user_type_key);
1713 FETCH user_type_id INTO l_user_type_id,l_approval_id;
1714 CLOSE user_type_id;
1715
1716
1717
1718 -- dbms_output.put_line('User type id new is '||l_user_type_id);
1719 -- dbms_output.put_line('approval id new is '||l_approval_id);
1720 -- dbms_output.put_line('User id new is '||l_user_id);
1721 -- dbms_output.put_line('User id new is '||l_user_name);
1722
1723
1724
1725
1726 l_partner_user_rec.user_id :=l_user_id;
1727 l_partner_user_rec.person_rel_party_id :=l_contact_rel_id;
1728 l_partner_user_rec.user_name:=l_user_name;
1729 l_partner_user_rec.user_type_id :=l_user_type_id;
1730
1731 PV_USER_MGMT_PVT.register_partner_user
1732 (
1733 p_api_version_number =>1.0
1734 ,p_init_msg_list =>FND_API.g_false
1735 ,p_commit =>FND_API.g_false
1736 ,p_partner_user_rec =>l_partner_user_rec
1737 --,p_isPartial => FND_API.g_false
1738 ,x_return_status => l_return_status ,
1739 x_msg_count => l_msg_count ,
1740 x_msg_data => l_msg_data
1741 );
1742
1743 -- dbms_output.put_line ('After API call'|| l_return_status);
1744 -- dbms_output.put_line('Message count : '||l_msg_count);
1745 -- dbms_output.put_line('In user API msgdata=> '|| l_msg_data);
1746
1747
1748 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1749 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1750 RAISE FND_API.G_EXC_ERROR;
1751 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1752 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1753 FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_USER_MGMT_PVT.register_partner_user ');
1754 FND_MSG_PUB.Add;
1755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1756 END IF;
1757 END IF;
1758
1759
1760 -- dbms_output.put_line ('After register partner user'|| l_return_status);
1761
1762
1763
1764
1765
1766 OPEN resp_list(l_user_type_id);
1767 LOOP
1768 FETCH resp_list INTO l_resp_id,l_app_id;
1769 -- dbms_output.put_line(' resp = '|| l_resp_id); -- recompile succeeded
1770 EXIT WHEN resp_list%NOTFOUND;
1771
1772
1773
1774 pv_user_resp_pvt.assign_resp(
1775 p_api_version_number => '1.0'
1776 ,p_init_msg_list => FND_API.G_FALSE
1777 ,p_commit => FND_API.G_true
1778 ,p_user_id => l_user_id
1779 ,p_resp_id => l_resp_id
1780 ,p_app_id => l_app_id
1781 ,x_return_status => l_return_status
1782 ,x_msg_count => l_msg_count
1783 ,x_msg_data => l_msg_data
1784 );
1785
1786 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1787 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1788 RAISE FND_API.G_EXC_ERROR;
1789 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1790 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1791 FND_MESSAGE.SET_TOKEN('API_NAME', 'pv_user_resp_pvt.assign_resp');
1792 FND_MSG_PUB.Add;
1793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794 END IF;
1795 END IF;
1796
1797 END LOOP; -- invalid program objects
1798 CLOSE resp_list;
1799 -- dbms_output.put_line('reached after responsibility :' );
1800
1801
1802
1803 OPEN role_list(l_user_type_id);
1804 LOOP
1805 FETCH role_list INTO l_principal_name;
1806 -- dbms_output.put_line(' resp = '|| l_resp_id); -- recompile succeeded
1807 EXIT WHEN role_list%NOTFOUND;
1808
1809
1810
1811 jtf_auth_bulkload_pkg.assign_role
1812 ( USER_NAME => l_user_name,
1813 ROLE_NAME => l_principal_name);
1814
1815
1816
1817 END LOOP; -- invalid program objects
1818 CLOSE role_list;
1819 -- dbms_output.put_line('reached after roles :' );
1820
1821 --- Will remove this once the Register_partner_user start date is fixed.
1822
1823
1824 fnd_user_pkg.updateUser(
1825 x_user_name => l_user_name,
1826 x_owner => null,
1827 x_start_date => sysdate
1828 );
1829
1830
1831
1832 END IF ; --- USER CREATION ENDS
1833
1834 x_return_status := FND_API.G_RET_STS_SUCCESS;
1835
1836
1837
1838
1839 EXCEPTION
1840
1841 WHEN FND_API.G_EXC_ERROR THEN
1842 ROLLBACK TO user_create_pvt;
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844 x_msg_data:= l_msg_data;
1845 x_msg_count:= l_msg_count;
1846 -- Standard call to get message count and if count=1, get the message
1847 FND_MSG_PUB.Count_And_Get (
1848 p_encoded => FND_API.G_FALSE,
1849 p_count => x_msg_count,
1850 p_data => x_msg_data
1851 );
1852
1853 -- Debug Message
1854 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1855 hz_utility_v2pub.debug_return_messages (
1856 x_msg_count, x_msg_data, 'ERROR');
1857 hz_utility_v2pub.debug('user_create (-)');
1858 END IF;
1859
1860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1861 ROLLBACK TO user_create_pvt;
1862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1863
1864 -- Standard call to get message count and if count=1, get the message
1865 FND_MSG_PUB.Count_And_Get (
1866 p_encoded => FND_API.G_FALSE,
1867 p_count => x_msg_count,
1868 p_data => x_msg_data
1869 );
1870
1871 -- Debug Message
1872 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1873 hz_utility_v2pub.debug_return_messages (
1874 x_msg_count, x_msg_data, 'UNEXPECTED ERROR');
1875 hz_utility_v2pub.debug('user_create (-)');
1876 END IF;
1877
1878 WHEN OTHERS THEN
1879 ROLLBACK TO user_create_pvt;
1880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1881 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1882 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1883 END IF;
1884
1885 -- Standard call to get message count and if count=1, get the message
1886 FND_MSG_PUB.Count_And_Get (
1887 p_encoded => FND_API.G_FALSE,
1888 p_count => x_msg_count,
1889 p_data => x_msg_data );
1890
1891 -- Debug Message
1892 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1893 hz_utility_v2pub.debug_return_messages (
1894 x_msg_count, x_msg_data, 'SQL ERROR');
1895 hz_utility_v2pub.debug('user_create (-)');
1896 END IF;
1897
1898
1899
1900 END user_create;
1901
1902
1903
1904
1905
1906 PROCEDURE user_update (
1907 p_api_version_number IN NUMBER
1908 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1909 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1910 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1911 ,p_user_name IN VARCHAR2
1912 ,p_user_type_key IN VARCHAR2
1913 ,p_contact_rel_id IN NUMBER
1914 ,x_return_status OUT NOCOPY VARCHAR2
1915 ,x_msg_data OUT NOCOPY VARCHAR2
1916 ,x_msg_count OUT NOCOPY NUMBER
1917 ) IS
1918
1919 L_API_NAME CONSTANT VARCHAR2(30) := 'user_update';
1920 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1921
1922
1923
1924
1925 l_api_version_number NUMBER ;
1926 l_init_msg_list VARCHAR2(1000);
1927 l_commit VARCHAR2(1000);
1928 l_validation_level NUMBER ;
1929
1930 l_user_name VARCHAR2(1000):=p_user_name;
1931 -- l_password VARCHAR2(1000):= p_password;
1932 l_contact_rel_id NUMBER :=p_contact_rel_id;
1933 l_user_type_key VARCHAR2(1000):=p_user_type_key;
1934 -- l_user_language VARCHAR2(1000) := p_user_language;
1935 l_sso_enabled VARCHAR2(100);
1936 l_partner_id NUMBER;
1937 l_partner_group_id NUMBER;
1938 l_person_first_name VARCHAR2(1000);
1939 l_approval_id NUMBER;
1940 l_enrollment_id NUMBER;
1941 l_enrollment_reg_id NUMBER;
1942 l_person_last_name VARCHAR2(1000);
1943 l_org_contact_id NUMBER;
1944 l_party_name VARCHAR2(1000);
1945 l_email_address VARCHAR2(1000);
1946
1947
1948 l_partner_party_id NUMBER;
1949 l_contact_party_id NUMBER;
1950
1951 l_partner_user_rec PV_USER_MGMT_PVT.Partner_User_Rec_type;
1952 l_person_party_id NUMBER ;
1953 l_rel_party_id NUMBER ;
1954 l_org_party_id NUMBER;
1955 l_org_contact_party_id NUMBER;
1956 l_rel_id NUMBER;
1957 l_rel_party_number NUMBER;
1958
1959 l_user_id NUMBER;
1960 l_user_type_id NUMBER;
1961 l_user_reg_id NUMBER;
1962 l_principal_name VARCHAR2(1000);
1963 -- Other OUT parameters returned by the API.
1964 l_return_status VARCHAR2(1);
1965 l_msg_count NUMBER;
1966 l_msg_data VARCHAR2(2000);
1967 l_usertype_resp_id NUMBER;
1968 l_usertype_app_id NUMBER;
1969 l_last_update_date VARCHAR2(2000);
1970 l_gen_password VARCHAR2(2000);
1971 l_version NUMBER;
1972 l_pass_length NUMBER;
1973
1974
1975 l_respKey varchar2(50) := 'JTF_PENDING_APPROVAL';
1976 l_status varchar2(10) := 'PENDING';
1977 l_application_id number := 690;
1978 l_usertype_key varchar2(100);
1979 l_resp_id NUMBER;
1980 l_contact_exist_id NUMBER;
1981 l_app_id NUMBER;
1982
1983 l_relationship_code VARCHAR2(1000);
1984 l_party_status VARCHAR2(1000);
1985
1986
1987 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
1988 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
1989
1990 CURSOR user_type_id (user_type VARCHAR2) IS
1991 select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
1992
1993 CURSOR user_type_reg_id (USERNAME VARCHAR2 , USERTYPE VARCHAR2) IS
1994 select USERTYPE_REG_ID from jtf_um_usertype_reg reg, fnd_user fu, JTF_UM_USERTYPES_B type where
1995 fu.user_id=reg.user_id and fu.user_name=username and
1996 type.usertype_key=USERTYPE and
1997 reg.usertype_id=type.usertype_id and
1998 reg.user_id=fu.user_id;
1999 CURSOR get_user_id(l_user_name VARCHAR2) is
2000 select user_id from fnd_user where user_name=l_user_name;
2001
2002 CURSOR enrollment_id (user_type VARCHAR2) IS
2003 select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
2004 where a.subscription_id=b.subscription_id
2005 and c.usertype_key=user_type
2006 and a.usertype_id=c.usertype_id;
2007
2008 CURSOR get_subscription(userid VARCHAR2, subscriptionid VARCHAR2) IS
2009 select SUBSCRIPTION_REG_ID from jtf_um_subscription_reg where user_id=userid and subscription_id=subscriptionid;
2010
2011 cursor getLUDFromUserReg(l_user_reg_id VARCHAR2) is
2012 select to_char (last_update_date, 'mmddyyyyhh24miss')
2013 from jtf_um_usertype_reg
2014 where usertype_reg_id = to_number (l_user_reg_id);
2015
2016 CURSOR CHECK_PARTNER(l_party_id VARCHAR2) IS
2017 select partner_id from pv_partner_profiles where partner_party_id=l_party_id;
2018
2019
2020
2021
2022 CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
2023 FROM JTF_UM_USERTYPE_RESP UT,
2024 FND_RESPONSIBILITY_VL FR,
2025 JTF_UM_USERTYPES_B c
2026 WHERE c.usertype_key=user_type
2027 and UT.USERTYPE_ID = c.usertype_id
2028 AND FR.APPLICATION_ID = UT.APPLICATION_ID
2029 AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
2030 AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
2031 AND UT.EFFECTIVE_START_DATE < SYSDATE;
2032
2033 CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
2034 FROM JTF_UM_USERTYPE_ROLE
2035 WHERE USERTYPE_ID = usertype_id
2036 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
2037 AND EFFECTIVE_START_DATE < SYSDATE;
2038
2039
2040 CURSOR get_relationship_code(rel_party_id NUMBER) IS
2041 select relationship_code from hz_relationships hzr where hzr.party_id=rel_party_id and hzr.directional_flag='F';
2042
2043
2044 CURSOR get_status(rel_party_id NUMBER) IS
2045 select hzp.status from hz_parties hzp where hzp.party_id=rel_party_id ;
2046
2047
2048 CURSOR get_email_address(rel_party_id NUMBER) IS
2049 select email_address from hz_parties hzp where hzp.party_id=rel_party_id ;
2050
2051
2052
2053
2054 cursor get_contact_details(rel_party_id NUMBER) IS
2055 select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name,
2056 hzoc.org_contact_id, org_hzp.party_name, rel_hzp.email_address
2057 from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP,
2058 hz_parties REL_HZP
2059 where HZR.party_id = rel_party_id
2060 and HZR.directional_flag = 'F'
2061 and hzr.relationship_code = 'EMPLOYEE_OF'
2062 and HZR.subject_table_name ='HZ_PARTIES'
2063 and HZR.object_table_name ='HZ_PARTIES'
2064 and hzr.start_date <= SYSDATE
2065 and (hzr.end_date is null or hzr.end_date > SYSDATE)
2066 and hzr.status = 'A'
2067 and hzr.subject_id = person_hzp.party_id
2068 and person_hzp.status = 'A'
2069 and hzr.object_id = pvpp.partner_party_id
2070 and pvpp.partner_group_id is not null
2071 and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
2072 and hzr.object_id = org_hzp.party_id and
2073 rel_hzp.party_id=hzr.party_id;
2074
2075 CURSOR CHECK_CONTACT(rel_party_id VARCHAR2,l_user_name VARCHAR2) is
2076 select hzr.subject_id from hz_parties rel,hz_relationships hzr ,pv_partner_profiles pvpp, fnd_user fu
2077 where rel.party_id=rel_party_id and
2078 hzr.party_id=rel.party_id and
2079 hzr.relationship_code in ('EMPLOYEE_OF') and
2080 fu.user_name=l_user_name and
2081 hzr.party_id=fu.customer_id and
2082 hzr.object_id=pvpp.partner_party_id;
2083
2084
2085 cursor resp_list(l_usertype_id VARCHAR2) is
2086 select responsibility_id, application_id
2087 from fnd_responsibility
2088 where responsibility_key in
2089 (select responsibility_key
2090 from jtf_um_usertype_resp jtur
2091 where jtur.usertype_id = l_usertype_id
2092 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
2093 union
2094 select responsibility_key
2095 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_resp jtsr
2096 where jtus.usertype_id = l_usertype_id
2097 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
2098 and jtus.subscription_flag = 'IMPLICIT'
2099 and jtus.subscription_id = jtsr.subscription_id
2100 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate));
2101
2102 cursor role_list(l_usertype_id VARCHAR2) is
2103 select principal_name
2104 from jtf_um_usertype_role jtur
2105 where jtur.usertype_id = l_usertype_id
2106 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
2107 union
2108 select jtsr.principal_name
2109 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
2110 where jtus.usertype_id = l_usertype_id
2111 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
2112 and jtus.subscription_flag = 'IMPLICIT'
2113 and jtus.subscription_id = jtsr.subscription_id
2114 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate);
2115
2116
2117 BEGIN
2118
2119 savepoint user_update_pvt;
2120
2121 -- dbms_output.put_line('l_contact_rel_id ' || l_contact_rel_id);
2122
2123 if l_contact_rel_id is null then
2124 fnd_message.SET_NAME ('PV', 'PV_CONTACT_ID_INVALID');
2125 fnd_msg_pub.ADD;
2126 raise FND_API.G_EXC_ERROR;
2127 END IF;
2128
2129
2130 OPEN CHECK_CONTACT(l_contact_rel_id,l_user_name) ;
2131 FETCH CHECK_CONTACT INTO l_contact_party_id;
2132 CLOSE CHECK_CONTACT;
2133
2134
2135 if l_contact_party_id is null then
2136 fnd_message.SET_NAME ('PV', 'PV_CONTACT_ID_INVALID');
2137 fnd_msg_pub.ADD;
2138 raise FND_API.G_EXC_ERROR;
2139
2140 END IF;
2141
2142
2143
2144 if l_user_name is null then
2145 fnd_message.SET_NAME ('PV', 'PV_USER_NAME_MISSING');
2146 fnd_msg_pub.ADD;
2147 raise FND_API.G_EXC_ERROR;
2148
2149 END IF;
2150
2151 -- dbms_output.put_line('after user name check');
2152
2153
2154
2155 /**************** CHECK FOR SSO ************************************************************************/
2156 l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
2157 if l_sso_enabled = 'N' OR l_sso_enabled = 'FTTT' OR l_sso_enabled = 'FFFF' then
2158 fnd_message.set_name('PV', 'PV_SSO_CREATE_USER_NOT_ALLOWED');
2159 fnd_msg_pub.ADD;
2160 raise FND_API.G_EXC_ERROR;
2161 end if;
2162
2163 -- dbms_output.put_line('after sso check ');
2164
2165 /**************** CHECK IF USER EXISTS ALREADY FOR CONTACT ************************************************************************/
2166
2167
2168 OPEN get_relationship_code(l_contact_rel_id);
2169 FETCH get_relationship_code INTO l_relationship_code;
2170 CLOSE get_relationship_code;
2171
2172
2173
2174
2175
2176 if l_relationship_code is null or l_relationship_code <> 'EMPLOYEE_OF' then
2177 fnd_message.SET_NAME ('PV', 'PV_NOT_EMPLOYEE_REL');
2178 fnd_msg_pub.ADD;
2179 RAISE FND_API.G_EXC_ERROR;
2180 END IF;
2181
2182 -- dbms_output.put_line('After relationship check');
2183
2184
2185
2186
2187 OPEN get_status(l_contact_rel_id);
2188 FETCH get_status INTO l_party_status;
2189 CLOSE get_status;
2190
2191 -- dbms_output.put_line('before party status check');
2192
2193 if l_party_status is null or l_party_status <> 'A' then
2194 fnd_message.SET_NAME ('PV', 'PV_CONTACT_NOT_ACTIVE');
2195 fnd_msg_pub.ADD;
2196 RAISE FND_API.G_EXC_ERROR;
2197
2198 END IF;
2199
2200
2201 -- dbms_output.put_line('BEFORE email address check');
2202
2203 OPEN get_email_address(l_contact_rel_id);
2204 FETCH get_email_address INTO l_email_address;
2205 CLOSE get_email_address;
2206
2207 -- dbms_output.put_line('After email address check1' || l_email_address);
2208
2209
2210
2211
2212
2213
2214 -- dbms_output.put_line('After email address check2' || l_email_address);
2215
2216 if l_email_address is null or l_email_address='' then
2217 l_return_status:='E';
2218 l_msg_count:=1;
2219 l_msg_data:='PV_EMAIL_ID_NEEDED';
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222
2223
2224 -- dbms_output.put_line('BEFORE get contact details');
2225
2226 OPEN get_contact_details(l_contact_rel_id);
2227 FETCH get_contact_details INTO l_partner_id, l_PARTNER_GROUP_ID , l_PERSON_FIRST_NAME, l_person_last_name, l_org_contact_id, l_party_name, l_email_address;
2228 CLOSE get_contact_details;
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240 -- dbms_output.put_line('user name not null check ' || l_user_name);
2241 if(l_user_name is not null) THEN
2242
2243
2244 OPEN GET_USER_ID(l_user_name);
2245 FETCH GET_USER_ID INTO l_user_id;
2246 CLOSE GET_USER_ID;
2247
2248
2249 OPEN user_type_id(l_user_type_key);
2250 FETCH user_type_id INTO l_user_type_id,l_approval_id;
2251 CLOSE user_type_id;
2252
2253
2254
2255
2256 l_partner_user_rec.user_id :=l_user_id;
2257 l_partner_user_rec.person_rel_party_id :=l_contact_rel_id;
2258 l_partner_user_rec.user_name:=l_user_name;
2259 l_partner_user_rec.user_type_id :=l_user_type_id;
2260
2261 PV_USER_MGMT_PVT.register_partner_user
2262 (
2263 p_api_version_number =>1.0
2264 ,p_init_msg_list =>FND_API.g_false
2265 ,p_commit =>FND_API.g_false
2266 ,p_partner_user_rec =>l_partner_user_rec
2267 --,p_isPartial => FND_API.g_false
2268 ,x_return_status => l_return_status ,
2269 x_msg_count => l_msg_count ,
2270 x_msg_data => l_msg_data
2271 );
2272
2273 -- dbms_output.put_line ('After API call'|| l_return_status);
2274 -- dbms_output.put_line('Message count : '||l_msg_count);
2275 -- dbms_output.put_line('In user API msgdata=> '|| l_msg_data);
2276
2277
2278 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2279 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2280 RAISE FND_API.G_EXC_ERROR;
2281 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2282 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
2283 FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_USER_MGMT_PVT.register_partner_user ');
2284 FND_MSG_PUB.Add;
2285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2286 END IF;
2287 END IF;
2288
2289
2290 -- dbms_output.put_line ('After register partner user'|| l_return_status);
2291
2292
2293
2294
2295 OPEN resp_list(l_user_type_id);
2296 LOOP
2297 FETCH resp_list INTO l_resp_id,l_app_id;
2298 EXIT WHEN resp_list%NOTFOUND;
2299
2300
2301 -- dbms_output.put_line(' resp = '|| l_resp_id); -- recompile succeeded
2302 pv_user_resp_pvt.assign_resp(
2303 p_api_version_number => '1.0'
2304 ,p_init_msg_list => FND_API.G_FALSE
2305 ,p_commit => FND_API.G_true
2306 ,p_user_id => l_user_id
2307 ,p_resp_id => l_resp_id
2308 ,p_app_id => l_app_id
2309 ,x_return_status => l_return_status
2310 ,x_msg_count => l_msg_count
2311 ,x_msg_data => l_msg_data
2312 );
2313
2314 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2315 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2316 RAISE FND_API.G_EXC_ERROR;
2317 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2318 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
2319 FND_MESSAGE.SET_TOKEN('API_NAME', 'pv_user_resp_pvt.assign_resp');
2320 FND_MSG_PUB.Add;
2321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322 END IF;
2323 END IF;
2324
2325 END LOOP; -- invalid program objects
2326 CLOSE resp_list;
2327 -- dbms_output.put_line('reached after responsibility :' );
2328
2329
2330
2331
2332
2333 OPEN role_list(l_user_type_id);
2334 LOOP
2335 FETCH role_list INTO l_principal_name;
2336 -- dbms_output.put_line(' resp = '|| l_principal_name); -- recompile succeeded
2337 EXIT WHEN role_list%NOTFOUND;
2338
2339
2340
2341 jtf_auth_bulkload_pkg.assign_role
2342 ( USER_NAME => l_user_name,
2343 ROLE_NAME => l_principal_name);
2344
2345
2346
2347 END LOOP; -- invalid program objects
2348 CLOSE role_list;
2349 -- dbms_output.put_line('reached after roles :' );
2350
2351 --- Will remove this once the Register_partner_user start date is fixed.
2352
2353
2354 fnd_user_pkg.updateUser(
2355 x_user_name => l_user_name,
2356 x_owner => null,
2357 x_start_date => sysdate
2358 );
2359
2360
2361 END IF ; --- USER CREATION ENDS
2362
2363 x_return_status := FND_API.G_RET_STS_SUCCESS;
2364
2365
2366
2367
2368 EXCEPTION
2369
2370 WHEN FND_API.G_EXC_ERROR THEN
2371 ROLLBACK TO user_update_pvt;
2372 x_return_status := FND_API.G_RET_STS_ERROR;
2373 x_msg_data:= l_msg_data;
2374 x_msg_count:= l_msg_count;
2375 -- Standard call to get message count and if count=1, get the message
2376 FND_MSG_PUB.Count_And_Get (
2377 p_encoded => FND_API.G_FALSE,
2378 p_count => x_msg_count,
2379 p_data => x_msg_data
2380 );
2381
2382 -- Debug Message
2383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2384 hz_utility_v2pub.debug_return_messages (
2385 x_msg_count, x_msg_data, 'ERROR');
2386 hz_utility_v2pub.debug('user_update (-)');
2387 END IF;
2388
2389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2390 ROLLBACK TO user_update_pvt;
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392
2393 -- Standard call to get message count and if count=1, get the message
2394 FND_MSG_PUB.Count_And_Get (
2395 p_encoded => FND_API.G_FALSE,
2396 p_count => x_msg_count,
2397 p_data => x_msg_data
2398 );
2399
2400 -- Debug Message
2401 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2402 hz_utility_v2pub.debug_return_messages (
2403 x_msg_count, x_msg_data, 'UNEXPECTED ERROR');
2404 hz_utility_v2pub.debug('user_update (-)');
2405 END IF;
2406
2407 WHEN OTHERS THEN
2408 ROLLBACK TO user_update_pvt;
2409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2410 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2411 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2412 END IF;
2413
2414 -- Standard call to get message count and if count=1, get the message
2415 FND_MSG_PUB.Count_And_Get (
2416 p_encoded => FND_API.G_FALSE,
2417 p_count => x_msg_count,
2418 p_data => x_msg_data );
2419
2420 -- Debug Message
2421 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2422 hz_utility_v2pub.debug_return_messages (
2423 x_msg_count, x_msg_data, 'SQL ERROR');
2424 hz_utility_v2pub.debug('user_update (-)');
2425 END IF;
2426
2427
2428
2429 END user_update;
2430
2431 PROCEDURE Load_Contacts (
2432 p_api_version_number IN NUMBER
2433 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2434 ,p_mode IN VARCHAR2 := 'EVALUATION'
2435 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2436 ,x_return_status OUT NOCOPY VARCHAR2
2437 ,x_msg_data OUT NOCOPY VARCHAR2
2438 ,x_msg_count OUT NOCOPY NUMBER
2439 ,p_contact_details_tbl IN CONTACT_DETAILS_TBL_TYPE
2440 ,p_update_if_exists IN VARCHAR2
2441 ,p_data_block_size IN NUMBER
2442 ,x_contact_output_tbl OUT NOCOPY CONTACT_OUTPUT_TBL_TYPE
2443 ,x_file_name OUT NOCOPY VARCHAR2
2444 )
2445 IS
2446
2447 L_API_NAME CONSTANT VARCHAR2(30) := 'Load_Contacts';
2448 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2449 l_contact_details_rec CONTACT_DETAILS_REC_TYPE ;
2450 l_commit VARCHAR2(100) := FND_API.G_FALSE;
2451 l_return_status VARCHAR2(1);
2452 l_msg_count NUMBER;
2453 l_msg_data VARCHAR2(2000);
2454 l_contact_rel_id NUMBER;
2455 l_location_id NUMBER;
2456 l_first_rec NUMBER;
2457 l_last_rec NUMBER;
2458 l_current_rec NUMBER;
2459 l_contact_details_tbl CONTACT_DETAILS_TBL_TYPE := p_contact_details_tbl;
2460 l_contact_output_tbl CONTACT_OUTPUT_TBL_TYPE;
2461 l_contact_output_rec CONTACT_OUTPUT_REC_TYPE;
2462 l_update_allowed varchar2(1000);
2463 l_update_if_exists varchar2(1000) :=p_update_if_exists;
2464 l_log_msg LOG_MESSAGE_TBL_TYPE;
2465 l_file_name varchar2(20);
2466 l_log_dir varchar2(100);
2467 l_out_dir varchar2(100);
2468 l_prof VARCHAR2(100);
2469 l_commit_size NUMBER := p_data_block_size;
2470 log_return_status VARCHAR2(1000);
2471
2472
2473
2474 CURSOR l_get_file_dir IS
2475 select
2476 trim(substr(value,0,(instr(value,',') - 1))),
2477 trim(substr(value,(instr(value,',') + 1)))
2478 from v$parameter where name = 'utl_file_dir';
2479
2480 BEGIN
2481
2482 -- dbms_output.put_line ('Entered Load_contacts method' );
2483
2484
2485
2486 if p_mode = 'EXECUTION' then
2487 l_commit:=FND_API.G_TRUE;
2488 else
2489 l_commit:=FND_API.G_FALSE;
2490 end if;
2491
2492
2493
2494
2495 /************** Start of file data ************************************************************/
2496 select to_char(systimestamp,'yyddmmsssss') || '.log' into l_file_name from dual;
2497
2498 open l_get_file_dir;
2499 fetch l_get_file_dir into l_out_dir, l_log_dir;
2500 close l_get_file_dir;
2501
2502 l_log_file := utl_file.fopen(trim(l_out_dir), l_file_name, 'w');
2503
2504
2505
2506 /************** End of file data ************************************************************/
2507
2508
2509 if l_contact_details_tbl.count = 0 then
2510
2511 fnd_message.set_name('PV', 'PV_IMP_NO_CONTACT');
2512 utl_file.put_line(L_LOG_FILE, substrb(fnd_message.get, 1, 1000));
2513 x_return_status:='S';
2514
2515
2516
2517 /************************************ CLOSING FILE ********************************/
2518 x_file_name := l_out_dir || '/' || l_file_name;
2519 -- dbms_output.put_line('output file name ' || x_file_name);
2520 utl_file.fclose(L_LOG_FILE);
2521 /**********************************************************************************/
2522
2523 else
2524
2525 /********************** Disable all HZ EVENTS **********************************************/
2526
2527 l_prof:= fnd_profile.value('HZ_EXECUTE_API_CALLOUTS');
2528 if l_prof <> 'N' then
2529 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS','N');
2530 end if ;
2531
2532 /***********************End disable events**************************************************/
2533
2534 if l_commit_size is null then
2535 l_commit_size:=50;
2536
2537 end if;
2538 -- dbms_output.put_line ('after setting commit size' );
2539 l_first_rec:=l_contact_details_tbl.FIRST;
2540 -- dbms_output.put_line ('after setting first record' );
2541 -- dbms_output.put_line ('last record is ' || l_contact_details_tbl.LAST);
2542
2543 WHILE l_first_rec <= l_contact_details_tbl.LAST
2544 LOOP
2545 savepoint contacts_batch;
2546 -- dbms_output.put_line ('Entered while loop of contact batch' );
2547
2548
2549 l_last_rec:= l_first_rec + l_commit_size -1;
2550
2551
2552 if l_contact_details_tbl.LAST < l_last_rec then
2553 l_last_rec := l_contact_details_tbl.LAST;
2554 end if ;
2555
2556 fnd_message.set_name('PV', 'PV_CONTACT_IMPORT_LOG');
2557 utl_file.put_line(L_LOG_FILE, substrb(fnd_message.get, 1, 1000));
2558
2559 utl_file.put_line(L_LOG_FILE, '-----------------------------------------------------------------------------------------------------'
2560 || '-----------------------------------------------------------------------------------------------------------------------------------------------');
2561 -- dbms_output.put_line('just after writing to file');
2562
2563
2564 for l_current_rec in l_first_rec .. l_last_rec
2565 LOOP
2566 savepoint contact_record;
2567 -- dbms_output.put_line ('Entered for loop of contact record' );
2568
2569 l_contact_details_rec:=p_contact_details_tbl(l_current_rec);
2570
2571
2572
2573 if l_contact_details_rec.update_if_exists is not null then
2574 l_update_allowed := l_contact_details_rec.update_if_exists;
2575
2576 elsif l_update_if_exists is not null then
2577
2578 l_update_allowed := l_update_if_exists;
2579 end if ;
2580
2581 l_log_msg.delete;
2582 PV_CONTACT_USER_BATCH_PUB.contact_create (
2583 p_api_version_number =>1.0
2584 ,p_init_msg_list =>'T'
2585 ,p_commit =>'F'
2586 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2587 ,p_contact_details_rec => l_contact_details_rec
2588 ,p_update_flag => l_update_allowed
2589 ,x_contact_rel_id => l_contact_rel_id
2590 ,x_contact_output_rec => l_contact_output_rec
2591 ,x_log_msg => l_log_msg
2592 ,x_return_status => l_return_status
2593 ,x_msg_data => l_msg_data
2594 ,x_msg_count => l_msg_count
2595 ) ;
2596
2597 x_contact_output_tbl(l_current_rec):=l_contact_output_rec;
2598
2599
2600 -- dbms_output.put_line('Contact LOG MESSAGE first ' || l_log_msg.FIRST);
2601 -- dbms_output.put_line('Contact LOG MESSAGE last ' || l_log_msg.LAST);
2602 /*
2603 for i in l_log_msg.FIRST .. l_log_msg.LAST
2604 LOOP
2605 -- dbms_output.put_line('Log - '||l_log_msg(i));
2606 END LOOP;
2607
2608 */
2609 log_return_status:=null;
2610 if l_contact_output_rec.return_status = 'SUCCESS' then
2611 fnd_message.set_name('PV', 'PV_CONTACT_REC_SUCCESS');
2612 elsif l_contact_output_rec.return_status = 'ERROR' then
2613 fnd_message.set_name('PV', 'PV_CONTACT_REC_ERROR');
2614 elsif l_contact_output_rec.return_status = 'NOT_PROCESSED' then
2615 fnd_message.set_name('PV', 'PV_CONTACT_REC_NOT_PROCESSED');
2616 end if;
2617 log_return_status:=substrb(fnd_message.get, 1, 1000);
2618
2619
2620
2621 -- dbms_output.put_line('just before writing to file');
2622 utl_file.put_line(L_LOG_FILE, rpad(nvl(l_contact_details_rec.contact_name,'N/A'),20) || rpad(nvl(l_contact_output_rec.Prtnr_orig_system,'N/A'),20)
2623 || rpad(nvl(l_contact_output_rec.Prtnr_orig_system_reference,'N/A'),20) || rpad(nvl(l_contact_output_rec.Partner_party_id,0),20)
2624 || rpad(nvl(l_contact_output_rec.Cnt_orig_system,'N/A'),20) || rpad(nvl(l_contact_output_rec.Cnt_orig_system_reference,'N/A'),20)
2625 || rpad(nvl(l_contact_output_rec.Person_party_id,0),20) || rpad(nvl(l_contact_output_rec.Contact_rel_party_id,0),20)
2626 || rpad(nvl(log_return_status,'N/A'),20));
2627 Write_Error(l_log_msg);
2628 utl_file.put_line(L_LOG_FILE, '-----------------------------------------------------------------------------------------------------'
2629 || '-----------------------------------------------------------------------------------------------------------------------------------------------');
2630 -- dbms_output.put_line('just after writing to file');
2631
2632
2633
2634 END LOOP; -- END FOR LOOP
2635
2636 -- dbms_output.put_line ('Just before commit' );
2637
2638 if l_commit = FND_API.G_TRUE then
2639 -- dbms_output.put_line ('I am going to commit' );
2640 Commit;
2641 else
2642 -- dbms_output.put_line ('I am going to rollback data' );
2643 rollback to contacts_batch;
2644 end if;
2645
2646 -- dbms_output.put_line ('Just after commit' );
2647
2648
2649
2650 l_first_rec := l_first_rec + l_commit_size;
2651 END LOOP; -- END WHILE LOOP.
2652
2653
2654
2655
2656 /********************** Reset HZ EVENTS **********************************************/
2657
2658 if l_prof <> 'N' then
2659 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
2660 end if ;
2661
2662 /***********************End reset events**************************************************/
2663
2664
2665
2666 -- dbms_output.put_line('just before closing file');
2667
2668 /************************************ CLOSING FILE ********************************/
2669 x_file_name := l_out_dir || '/' || l_file_name;
2670 -- dbms_output.put_line('output file name ' || x_file_name);
2671 utl_file.fclose(L_LOG_FILE);
2672 /**********************************************************************************/
2673 -- dbms_output.put_line('just after closing file');
2674
2675
2676 x_return_status:='S';
2677
2678
2679
2680 end if;
2681
2682
2683 EXCEPTION
2684 when utl_file.invalid_path then
2685 raise_application_error(-20100,'Invalid Path');
2686 when utl_file.invalid_mode then
2687 raise_application_error(-20101,'Invalid Mode');
2688 when utl_file.invalid_operation then
2689 raise_application_error(-20102,'Invalid Operation');
2690 when utl_file.invalid_filehandle then
2691 raise_application_error(-20103,'Invalid FileHandle');
2692 when utl_file.write_error then
2693 utl_file.fclose(l_log_file);
2694 raise_application_error(-20104,'Write Error');
2695 when utl_file.read_error then
2696 raise_application_error(-20105,'Read Error');
2697 when utl_file.internal_error then
2698 raise_application_error(-20106,'Internal Error');
2699
2700 WHEN OTHERS THEN
2701
2702
2703 ROLLBACK;
2704 /* utl_file.put_line(L_LOG_FILE,SQLERRM);
2705 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
2706 utl_file.put_line(L_LOG_FILE,Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,1000));
2707 END LOOP;
2708 utl_file.fclose(l_log_file);
2709 */
2710
2711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2712 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2713 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2714 END IF;
2715
2716 -- Standard call to get message count and if count=1, get the message
2717 FND_MSG_PUB.Count_And_Get (
2718 p_encoded => FND_API.G_FALSE,
2719 p_count => x_msg_count,
2720 p_data => x_msg_data );
2721
2722 -- Debug Message
2723 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2724 hz_utility_v2pub.debug_return_messages (
2725 x_msg_count, x_msg_data, 'SQL ERROR');
2726 hz_utility_v2pub.debug('Load_contacts (-)');
2727 END IF;
2728
2729
2730
2731
2732
2733 END Load_Contacts ;
2734
2735
2736
2737 END PV_CONTACT_USER_BATCH_PUB;
2738