[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_CONTACTS_PVT
Source
1 PACKAGE BODY AS_SALES_LEAD_CONTACTS_PVT as
2 /* $Header: asxvslcb.pls 120.0.12010000.2 2010/02/15 10:17:17 sariff ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_CONTACTS_PVT
5 -- Purpose : Sales Leads Contacts
6 -- NOTE :
7 -- History :
8 -- 04/09/2001 FFANG Created.
9 --
10 -- END of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEAD_CONTACTS_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvslcb.pls';
15
16
17 -- *************************
18 -- Validation Procedures
19 -- *************************
20 --
21 -- Item level validation procedures
22 --
23
24 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
25 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
26 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
27 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
28
29 PROCEDURE Validate_CONTACT_ID (
30 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
31 P_Validation_mode IN VARCHAR2,
32 P_CUSTOMER_ID IN NUMBER,
33 P_CONTACT_ID IN NUMBER,
34 X_Return_Status OUT NOCOPY VARCHAR2,
35 X_Msg_Count OUT NOCOPY NUMBER,
36 X_Msg_Data OUT NOCOPY VARCHAR2
37 )
38 IS
39 -- solin, 02/01/2001, replace view AS_PARTY_ORG_CONTACTS_V with HZ tables
40 /*
41 CURSOR C_CONTACT_ID_Exists(c_contact_id NUMBER) IS
42 SELECT 'X'
43 FROM HZ_CONTACT_POINTS CONT_POINT,
44 HZ_PARTIES PARTY,
45 HZ_PARTIES PARTY2,
46 HZ_PARTY_RELATIONSHIPS REL,
47 HZ_ORG_CONTACTS ORG_CONT
48 WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = REL.PARTY_RELATIONSHIP_ID
49 AND REL.OBJECT_ID = PARTY.PARTY_ID
50 AND REL.PARTY_ID = PARTY2.PARTY_ID
51 AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
52 AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
53 AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
54 AND ORG_CONT.ORG_CONTACT_ID = c_contact_id
55 AND PARTY.PARTY_ID = P_CUSTOMER_ID;
56 */
57
58 -- ffang 100401, bug 2031450, HZ_RELATIONSHIPS should be used to replace
59 -- HZ_PARTY_RELATIONSHIPS
60 CURSOR C_CONTACT_ID_Exists(c_contact_id NUMBER) IS
61 SELECT 'X'
62 FROM -- HZ_CONTACT_POINTS CONT_POINT,
63 HZ_PARTIES PARTY,
64 HZ_PARTIES PARTY2,
65 HZ_RELATIONSHIPS REL,
66 HZ_ORG_CONTACTS ORG_CONT
67 WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
68 AND REL.OBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = PARTY2.PARTY_ID
69 -- AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
70 -- AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
71 -- AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
72 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
73 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
74 AND ORG_CONT.ORG_CONTACT_ID = c_contact_id
75 AND PARTY.PARTY_ID = P_CUSTOMER_ID --;
76 -- ffang 100901, bug 2039435, add checking on status
77 AND PARTY.STATUS IN ('A', 'I');
78
79
80 -- FROM AS_PARTY_ORG_CONTACTS_V
81 -- WHERE contact_id = c_contact_id
82 -- AND customer_id = P_CUSTOMER_ID;
83
84 l_val VARCHAR2(1);
85
86 BEGIN
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean( p_init_msg_list )
89 THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93 -- Initialize API return status to SUCCESS
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 IF (p_CONTACT_ID is NOT NULL) and
97 (p_CONTACT_ID <> FND_API.G_MISS_NUM)
98 THEN
99 OPEN C_CONTACT_ID_Exists (p_CONTACT_ID);
100 FETCH C_CONTACT_ID_Exists into l_val;
101 IF C_CONTACT_ID_Exists%NOTFOUND THEN
102 IF (AS_DEBUG_LOW_ON) THEN
103
104 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
105 'Private API: CONTACT_ID is invalid');
106 END IF;
107
108 AS_UTILITY_PVT.Set_Message(
109 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
110 p_msg_name => 'API_INVALID_ID',
111 p_token1 => 'COLUMN',
112 p_token1_value => 'CONTACT_ID',
113 p_token2 => 'VALUE',
114 p_token2_value => p_CONTACT_ID );
115
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 END IF;
118 CLOSE C_CONTACT_ID_Exists;
119 END IF;
120
121 -- Standard call to get message count and if count is 1, get message info.
122 FND_MSG_PUB.Count_And_Get
123 ( p_count => x_msg_count,
124 p_data => x_msg_data
125 );
126
127
128 END Validate_CONTACT_ID;
129
130
131 PROCEDURE Validate_CONTACT_PARTY_ID (
132 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
133 P_Validation_mode IN VARCHAR2,
134 P_CUSTOMER_ID IN NUMBER,
135 P_CONTACT_PARTY_ID IN NUMBER,
136 X_Return_Status OUT NOCOPY VARCHAR2,
137 X_Msg_Count OUT NOCOPY NUMBER,
138 X_Msg_Data OUT NOCOPY VARCHAR2
139 )
140 IS
141 CURSOR C_CONTACT_PARTY_ID_Exists (c_CONTACT_PARTY_ID NUMBER) IS
142 Select 'X'
143 from hz_relationships
144 where party_id = c_CONTACT_PARTY_ID
145 and object_id = P_CUSTOMER_ID
146 and subject_table_name = 'HZ_PARTIES'
147 and object_table_name = 'HZ_PARTIES' --;
148 --and relationship_code = 'CONTACT_OF';
149 -- ffang 100901, bug 2039435, add checking on status
150 AND STATUS IN ('A', 'I');
151
152 /* ffang 083001, use hz_relationships instead of hz_party_relationships
153 SELECT 'X'
154 FROM HZ_PARTY_RELATIONSHIPS
155 WHERE object_id = P_CUSTOMER_ID
156 AND party_id = c_CONTACT_PARTY_ID;
157 */
158
159 l_val VARCHAR2(1);
160 BEGIN
161 -- Initialize message list if p_init_msg_list is set to TRUE.
162 IF FND_API.to_Boolean( p_init_msg_list )
163 THEN
164 FND_MSG_PUB.initialize;
165 END IF;
166
167 -- Initialize API return status to SUCCESS
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 -- ffang 092000 for bug 1406761
171 -- validate NOT NULL column
172 IF (p_CONTACT_PARTY_ID is NULL)
173 THEN
174 IF (AS_DEBUG_LOW_ON) THEN
175
176 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
177 'Private API: Violate NOT NULL(CONTACT_PARTY_ID)');
178 END IF;
179
180 AS_UTILITY_PVT.Set_Message(
181 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
182 p_msg_name => 'API_MISSING_ID',
183 p_token1 => 'COLUMN',
184 p_token1_value => 'CONTACT_PARTY_ID');
185
186 x_return_status := FND_API.G_RET_STS_ERROR;
187
188 ELSE -- p_CONTACT_PARTY_ID is NOT NULL
189
190 -- Calling from Create APIs, CONTACT_PARTY_ID can not be G_MISS_NUM
191 IF (p_validation_mode = AS_UTILITY_PVT.G_CREATE) and
192 (p_CONTACT_PARTY_ID = FND_API.G_MISS_NUM)
193 THEN
194 -- IF (AS_DEBUG_ERROR_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
195 -- 'Private API: CONTACT_PARTY_ID is missing'); END IF;
196
197 AS_UTILITY_PVT.Set_Message(
198 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
199 p_msg_name => 'API_MISSING_CONTACT_PARTY_ID');
200
201 x_return_status := FND_API.G_RET_STS_ERROR;
202
203 -- If CONTACT_PARTY_ID <> G_MISS_NUM, check if it is valid
204 ELSIF (p_CONTACT_PARTY_ID <> FND_API.G_MISS_NUM)
205 THEN
206 OPEN C_CONTACT_PARTY_ID_Exists (p_CONTACT_PARTY_ID);
207 FETCH C_CONTACT_PARTY_ID_Exists into l_val;
208 IF C_CONTACT_PARTY_ID_Exists%NOTFOUND
209 THEN
210 IF (AS_DEBUG_LOW_ON) THEN
211
212 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
213 'Private API: CONTACT_PARTY_ID is invalid');
214 END IF;
215
216 AS_UTILITY_PVT.Set_Message(
217 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
218 p_msg_name => 'API_INVALID_ID',
219 p_token1 => 'COLUMN',
220 p_token1_value => 'CONTACT_PARTY_ID',
221 p_token2 => 'VALUE',
222 p_token2_value => p_CONTACT_PARTY_ID );
223
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 END IF;
226
227 -- ffang 092800: Forgot to close cursor
228 CLOSE C_CONTACT_PARTY_ID_Exists;
229 -- end ffang 092800
230 END IF;
231 END IF;
232 -- end ffang 092000 for bug 1406761
233
234 -- Standard call to get message count and if count is 1, get message info.
235
236 FND_MSG_PUB.Count_And_Get
237 ( p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240
241 END Validate_CONTACT_PARTY_ID;
242
243
244 PROCEDURE Validate_PHONE_ID (
245 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
246 P_Validation_mode IN VARCHAR2,
247 P_CONTACT_ID IN NUMBER,
248 P_CONTACT_PARTY_ID IN NUMBER,
249 P_PHONE_ID IN NUMBER,
250 X_Return_Status OUT NOCOPY VARCHAR2,
251 X_Msg_Count OUT NOCOPY NUMBER,
252 X_Msg_Data OUT NOCOPY VARCHAR2
253 )
254 IS
255 -- ffang012501, use HZ_CONTACT_POINTS instead of AS_PARTY_PHONES_V
256 CURSOR C_PHONE_ID_Exists(c_phone_id NUMBER) IS
257 SELECT 'X'
258 FROM HZ_CONTACT_POINTS
259 WHERE contact_point_id = c_phone_id
260 AND owner_table_name = 'HZ_PARTIES'
261 AND owner_table_id = P_CONTACT_PARTY_ID
262 AND CONTACT_POINT_TYPE IN ( 'PHONE', 'FAX') --;
263 -- ffang 100901, bug 2039435, add checking on status
264 AND STATUS IN ('A', 'I');
265
266 l_val VARCHAR2(1);
267
268 BEGIN
269
270 -- Initialize message list if p_init_msg_list is set to TRUE.
271 IF FND_API.to_Boolean( p_init_msg_list )
272 THEN
273 FND_MSG_PUB.initialize;
274 END IF;
275
276
277 -- Initialize API return status to SUCCESS
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 IF (p_PHONE_ID is NOT NULL) and
281 (p_PHONE_ID <> FND_API.G_MISS_NUM)
282 THEN
283 OPEN C_PHONE_ID_Exists (p_PHONE_ID);
284 FETCH C_PHONE_ID_Exists into l_val;
285 IF C_PHONE_ID_Exists%NOTFOUND THEN
286 IF (AS_DEBUG_LOW_ON) THEN
287
288 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
289 'Private API: PHONE_ID is invalid');
290 END IF;
291
292 AS_UTILITY_PVT.Set_Message(
293 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
294 p_msg_name => 'API_INVALID_ID',
295 p_token1 => 'COLUMN',
296 p_token1_value => 'PHONE_ID',
297 p_token2 => 'VALUE',
298 p_token2_value => p_PHONE_ID );
299
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 END IF;
302 CLOSE C_PHONE_ID_Exists;
303 END IF;
304
305 -- Standard call to get message count and if count is 1, get message info.
306 FND_MSG_PUB.Count_And_Get
307 ( p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310
311 END Validate_PHONE_ID;
312
313
314 PROCEDURE Validate_CONTACT_ROLE_CODE (
315 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
316 P_Validation_mode IN VARCHAR2,
317 P_CONTACT_ROLE_CODE IN VARCHAR2,
318 X_Return_Status OUT NOCOPY VARCHAR2,
319 X_Msg_Count OUT NOCOPY NUMBER,
320 X_Msg_Data OUT NOCOPY VARCHAR2
321 )
322 IS
323 CURSOR C_Lookup_Exists (X_Lookup_Code VARCHAR2, X_Lookup_Type VARCHAR2) IS
324 SELECT 'X'
325 FROM as_lookups
326 WHERE lookup_type = X_Lookup_Type
327 and lookup_code = X_Lookup_Code
328 -- ffang 012501
329 and ENABLED_FLAG = 'Y';
330
331 l_val VARCHAR2(1);
332 BEGIN
333 -- Initialize message list IF p_init_msg_list is set to TRUE.
334 IF FND_API.to_Boolean( p_init_msg_list )
335 THEN
336 FND_MSG_PUB.initialize;
337 END IF;
338
339 -- Initialize API return status to SUCCESS
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341
342 -- Debug Message
343 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
344 -- 'Validate contact role code'); END IF;
345
346 -- Validate contact role code
347 IF (p_contact_role_code is NOT NULL
348 AND p_contact_role_code <> FND_API.G_MISS_CHAR)
349 THEN
350 OPEN C_Lookup_Exists ( p_contact_role_code, 'LEAD_CONTACT_ROLE');
351 FETCH C_Lookup_Exists into l_val;
352
353 IF C_Lookup_Exists%NOTFOUND
354 THEN
355 AS_UTILITY_PVT.Set_Message(
356 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
357 p_msg_name => 'API_INVALID_ID',
358 p_token1 => 'COLUMN',
359 p_token1_value => 'CONTACT_ROLE_CODE',
360 p_token2 => 'VALUE',
361 p_token2_value => p_CONTACT_ROLE_CODE );
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END IF;
364 CLOSE C_Lookup_Exists;
365 END IF;
366
367 -- Standard call to get message count and IF count is 1, get message info.
368 FND_MSG_PUB.Count_And_Get
369 ( p_count => x_msg_count,
370 p_data => x_msg_data );
371 END Validate_CONTACT_ROLE_CODE;
372
373
374 --
375 -- Record Level Validation
376 --
377
378
379 --
380 -- Inter-record level validation
381 --
382
383
384 --
385 -- validation procedures
386 --
387
388 PROCEDURE Validate_sales_lead_contact(
389 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
390 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
391 P_Validation_mode IN VARCHAR2,
392 P_SALES_LEAD_CONTACT_Rec IN AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Rec_Type,
393 X_Return_Status OUT NOCOPY VARCHAR2,
394 X_Msg_Count OUT NOCOPY NUMBER,
395 X_Msg_Data OUT NOCOPY VARCHAR2
396 )
397 IS
398 l_api_name CONSTANT VARCHAR2(30) := 'Validate_sales_lead_contact';
399 l_Return_Status VARCHAR2(1);
400 BEGIN
401 -- Initialize message list IF p_init_msg_list is set to TRUE.
402 IF FND_API.to_Boolean( p_init_msg_list )
403 THEN
404 FND_MSG_PUB.initialize;
405 END IF;
406
407 -- Debug Message
408 IF (AS_DEBUG_LOW_ON) THEN
409
410 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
411 'PVT: ' || l_api_name || ' Start');
412 END IF;
413
414 -- Initialize API return status to SUCCESS
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417
418 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
419 THEN
420 -- Perform item level validation
421 AS_SALES_LEADS_PVT.Validate_CUSTOMER_ID(
422 p_init_msg_list => FND_API.G_FALSE,
423 p_validation_mode => p_validation_mode,
424 p_CUSTOMER_ID => P_SALES_LEAD_CONTACT_Rec.CUSTOMER_ID,
425 x_return_status => x_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data);
428 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
429 l_return_status := FND_API.G_RET_STS_ERROR;
430 -- raise FND_API.G_EXC_ERROR;
431 END IF;
432
433 -- ffang 081001, bug 1930170, if no addres_id, then skip validation
434 IF (P_SALES_LEAD_CONTACT_Rec.ADDRESS_ID IS NOT NULL and
435 P_SALES_LEAD_CONTACT_Rec.ADDRESS_ID <> FND_API.G_MISS_NUM)
436 THEN
437 AS_SALES_LEADS_PVT.Validate_ADDRESS_ID(
438 p_init_msg_list => FND_API.G_FALSE,
439 p_validation_mode => p_validation_mode,
440 p_CUSTOMER_ID => P_SALES_LEAD_CONTACT_Rec.CUSTOMER_ID,
441 p_ADDRESS_ID => P_SALES_LEAD_CONTACT_Rec.ADDRESS_ID,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data);
445 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
446 l_return_status := FND_API.G_RET_STS_ERROR;
447 -- raise FND_API.G_EXC_ERROR;
448 END IF;
449 END IF;
450
451 Validate_CONTACT_PARTY_ID(
452 p_init_msg_list => FND_API.G_FALSE,
453 p_validation_mode => p_validation_mode,
454 p_CUSTOMER_ID => P_SALES_LEAD_CONTACT_Rec.CUSTOMER_ID,
455 p_CONTACT_PARTY_ID => P_SALES_LEAD_CONTACT_Rec.CONTACT_PARTY_ID,
456 x_return_status => x_return_status,
457 x_msg_count => x_msg_count,
458 x_msg_data => x_msg_data);
459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
460 l_return_status := FND_API.G_RET_STS_ERROR;
461 -- raise FND_API.G_EXC_ERROR;
462 END IF;
463
464 Validate_CONTACT_ID(
465 p_init_msg_list => FND_API.G_FALSE,
466 p_validation_mode => p_validation_mode,
467 p_CUSTOMER_ID => P_SALES_LEAD_CONTACT_Rec.CUSTOMER_ID,
468 p_CONTACT_ID => P_SALES_LEAD_CONTACT_Rec.CONTACT_ID,
469 x_return_status => x_return_status,
470 x_msg_count => x_msg_count,
471 x_msg_data => x_msg_data);
472 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
473 l_return_status := FND_API.G_RET_STS_ERROR;
474 -- raise FND_API.G_EXC_ERROR;
475 END IF;
476
477 Validate_PHONE_ID(
478 p_init_msg_list => FND_API.G_FALSE,
479 p_validation_mode => p_validation_mode,
480 p_CONTACT_ID => P_SALES_LEAD_CONTACT_Rec.CONTACT_ID,
481 p_CONTACT_PARTY_ID => P_SALES_LEAD_CONTACT_Rec.CONTACT_PARTY_ID,
482 p_PHONE_ID => P_SALES_LEAD_CONTACT_Rec.PHONE_ID,
483 x_return_status => x_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data);
486 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
487 l_return_status := FND_API.G_RET_STS_ERROR;
488 -- raise FND_API.G_EXC_ERROR;
489 END IF;
490
491 Validate_CONTACT_ROLE_CODE(
492 p_init_msg_list => FND_API.G_FALSE,
493 p_validation_mode => p_validation_mode,
494 p_CONTACT_ROLE_CODE => P_SALES_LEAD_CONTACT_Rec.CONTACT_ROLE_CODE,
495 x_return_status => x_return_status,
496 x_msg_count => x_msg_count,
497 x_msg_data => x_msg_data);
498 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
499 l_return_status := FND_API.G_RET_STS_ERROR;
500 -- raise FND_API.G_EXC_ERROR;
501 END IF;
502
503 AS_SALES_LEADS_PVT.Validate_FLAGS(
504 p_init_msg_list => FND_API.G_FALSE,
505 p_validation_mode => p_validation_mode,
506 p_Flag_Value =>
507 P_SALES_LEAD_CONTACT_Rec.PRIMARY_CONTACT_FLAG,
508 p_Flag_Type => 'PRIMARY_CONTACT_FLAG',
509 x_return_status => x_return_status,
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data);
512 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
513 l_return_status := FND_API.G_RET_STS_ERROR;
514 -- raise FND_API.G_EXC_ERROR;
515 END IF;
516
517 END IF;
518
519 -- FFANG 112700 For bug 1512008, instead of erroring out once a invalid
520 -- column was found, raise the exception after all validation procedures
521 -- have been gone through.
522 x_return_status := l_return_status;
523 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
524 raise FND_API.G_EXC_ERROR;
525 END IF;
526 -- END FFANG 112700
527
528 -- Debug Message
529 IF (AS_DEBUG_LOW_ON) THEN
530
531 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
532 'PVT: ' || l_api_name || ' End');
533 END IF;
534 END Validate_sales_lead_contact;
535
536
537 -- ***************************
538 -- Sales Lead Contact APIs
539 -- ***************************
540
541 PROCEDURE Create_sales_lead_contacts(
542 P_Api_Version_Number IN NUMBER,
543 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
544 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
545 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
546 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
547 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
548 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
549 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
550 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
551 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
552 P_SALES_LEAD_CONTACT_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type
553 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_CONTACT_Tbl,
554 P_SALES_LEAD_ID IN NUMBER,
555 X_SALES_LEAD_CNT_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
556 X_Return_Status OUT NOCOPY VARCHAR2,
557 X_Msg_Count OUT NOCOPY NUMBER,
558 X_Msg_Data OUT NOCOPY VARCHAR2
559 )
560
561 IS
562 -- for bug 2098158 ckapoor - change cursor to retrieve the phone and party id of contact also
563 Cursor C_Get_Primary_Contact ( c_SALES_LEAD_ID NUMBER ) IS
564 SELECT slc.LEAD_CONTACT_ID, slc.CONTACT_PARTY_ID, slc.PHONE_ID, r.subject_id
565 FROM as_sales_lead_contacts slc, hz_relationships r
566 WHERE slc.sales_lead_id = c_SALES_LEAD_ID
567 and slc.enabled_flag = 'Y'
568 and slc.primary_contact_flag = 'Y'
569 and r.party_id = slc.contact_party_id
570 and r.object_id = slc.customer_id;
571
572 -- Bug 3357273 - MASS1R10:11510:FUNC:CONTACT NOT MARKED'PRIMARY'WHEN LEAD HAS MORE THAN 1 CONTACT
573
574 CURSOR C_Sales_Lead_Id_Exists ( c_SALES_LEAD_ID NUMBER ) IS
575 SELECT count(1)
576 FROM as_sales_lead_contacts
577 WHERE sales_lead_id = c_SALES_LEAD_ID;
578
579
580 l_val NUMBER; -- data type changed to Number for bug 9378908
581
582 -- end bug 3357273
583
584
585 l_api_name CONSTANT VARCHAR2(30) := 'Create_sales_lead_contacts';
586 l_api_version_number CONSTANT NUMBER := 2.0;
587 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
588 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
589 l_SALES_LEAD_CONTACT_rec AS_SALES_LEADS_PUB.sales_lead_contact_rec_type;
590 l_lead_contact_id NUMBER;
591 l_update_access_flag VARCHAR2(1);
592 l_member_role VARCHAR2(5);
593 l_member_access VARCHAR2(5);
594 l_contact_party_id NUMBER;
595 l_cnt_person_party_id NUMBER;
596 l_contact_phone_id NUMBER;
597 BEGIN
598 -- Standard Start of API savepoint
599 SAVEPOINT CREATE_SALES_LEAD_CONTACTS_PVT;
600 -- Standard call to check for call compatibility.
601 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
602 p_api_version_number,
603 l_api_name,
604 G_PKG_NAME)
605 THEN
606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607 END IF;
608
609
610 -- Initialize message list IF p_init_msg_list is set to TRUE.
611 IF FND_API.to_Boolean( p_init_msg_list )
612 THEN
613 FND_MSG_PUB.initialize;
614 END IF;
615
616 -- Debug Message
617 IF (AS_DEBUG_LOW_ON) THEN
618
619 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
620 'PVT: ' || l_api_name || ' Start');
621 END IF;
622
623 -- Initialize API return status to SUCCESS
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 --
627 -- API body
628 --
629 -- ******************************************************************
630 -- Validate Environment
631 -- ******************************************************************
632 IF FND_GLOBAL.User_Id IS NULL
633 THEN
634 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
635 THEN
636 AS_UTILITY_PVT.Set_Message(
637 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
638 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
639 p_token1 => 'PROFILE',
640 p_token1_value => 'USER_ID');
641 END IF;
642 RAISE FND_API.G_EXC_ERROR;
643 END IF;
644
645 IF (p_validation_level = fnd_api.g_valid_level_full)
646 THEN
647 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
648 p_api_version_number => 2.0
649 ,p_init_msg_list => p_init_msg_list
650 ,p_salesforce_id => P_Identity_Salesforce_Id
651 ,p_admin_group_id => p_admin_group_id
652 ,x_return_status => x_return_status
653 ,x_msg_count => x_msg_count
654 ,x_msg_data => x_msg_data
655 ,x_sales_member_rec => l_identity_sales_member_rec);
656 END IF;
657
658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662
663 -- Bug 3357273 - MASS1R10:11510:FUNC:CONTACT NOT MARKED'PRIMARY'WHEN LEAD HAS MORE THAN 1 CONTACT
664
665 OPEN C_Sales_Lead_Id_Exists(p_sales_lead_id) ;
666 FETCH C_Sales_Lead_Id_Exists into l_val;
667 CLOSE C_Sales_Lead_Id_Exists ;
668
669 -- end bug 3357273
670
671
672 FOR l_curr_row IN 1..p_sales_lead_contact_tbl.count LOOP
673 x_sales_lead_cnt_out_tbl(l_curr_row).return_status
674 := FND_API.G_RET_STS_SUCCESS;
675
676 -- Progress Message
677 --
678 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
679 THEN
680 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
681 FND_MESSAGE.Set_Token ('ROW', 'AS_SALES_LEAD_CONTACT', TRUE);
682 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
683 FND_MSG_PUB.Add;
684 END IF;
685
686 l_sales_lead_contact_rec := p_sales_lead_contact_tbl(l_curr_row);
687 l_sales_lead_contact_rec.sales_lead_id := p_sales_lead_id;
688
689
690 IF (l_val=0)
691 THEN
692 -- 11.5.10 ckapoor Primary contact changes for Rivendell
693
694 -- if ( (p_sales_lead_contact_tbl.count = 1) and (l_curr_row=1)) then
695 if (l_curr_row=1) then
696 if (l_sales_lead_contact_rec.primary_contact_flag <> 'Y')
697 then
698
699 IF (AS_DEBUG_LOW_ON) THEN
700
701 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
702 'CK:force setting primary contact flag');
703 END IF;
704
705
706 l_sales_lead_contact_rec.primary_contact_flag := 'Y';
707 end if;
708 end if;
709 end if; -- IF (l_val=0)
710
711 -- 11.5.10 end ckapoor
712
713
714
715
716 -- Debug message
717 IF (AS_DEBUG_LOW_ON) THEN
718
719 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
720 'Calling Validate_contact');
721 END IF;
722
723 -- Invoke validation procedures
724 Validate_sales_lead_contact(
725 p_init_msg_list => FND_API.G_FALSE,
726 p_validation_level => p_validation_level,
727 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
728 P_SALES_LEAD_CONTACT_Rec => l_SALES_LEAD_CONTACT_Rec,
729 x_return_status => x_return_status,
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data);
732
733 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
734 x_sales_lead_cnt_out_tbl(l_curr_row).return_status:=x_return_status;
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738 IF l_SALES_LEAD_CONTACT_rec.ENABLED_FLAG IS NULL OR
739 l_SALES_LEAD_CONTACT_rec.ENABLED_FLAG = FND_API.G_MISS_CHAR
740 THEN
741 l_SALES_LEAD_CONTACT_rec.ENABLED_FLAG := 'Y';
742 END IF;
743
744 IF(P_Check_Access_Flag = 'Y') THEN
745 -- Call Get_Access_Profiles to get access_profile_rec
746 IF (AS_DEBUG_LOW_ON) THEN
747
748 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
749 'Calling Get_Access_Profiles');
750 END IF;
751
752 AS_SALES_LEADS_PUB.Get_Access_Profiles(
753 p_profile_tbl => p_sales_lead_profile_tbl,
754 x_access_profile_rec => l_access_profile_rec);
755
756 IF (AS_DEBUG_LOW_ON) THEN
757
758
759
760 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
761 'Calling Has_updateLeadAccess');
762
763 END IF;
764
765 AS_ACCESS_PUB.Has_updateLeadAccess(
766 p_api_version_number => 2.0
767 ,p_init_msg_list => FND_API.G_FALSE
768 ,p_validation_level => p_validation_level
769 ,p_access_profile_rec => l_access_profile_rec
770 ,p_admin_flag => p_admin_flag
771 ,p_admin_group_id => p_admin_group_id
772 ,p_person_id => l_identity_sales_member_rec.employee_person_id
773 ,p_sales_lead_id => l_sales_lead_contact_rec.sales_lead_id
774 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
775 ,p_identity_salesforce_id => p_identity_salesforce_id
776 ,p_partner_cont_party_id => NULL
777 ,x_return_status => x_return_status
778 ,x_msg_count => x_msg_count
779 ,x_msg_data => x_msg_data
780 ,x_update_access_flag => l_update_access_flag);
781
782 IF l_update_access_flag <> 'Y' THEN
783 IF (AS_DEBUG_ERROR_ON) THEN
784
785 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
786 'API_NO_CREATE_PRIVILEGE');
787 END IF;
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790
791 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
792 RAISE FND_API.G_EXC_ERROR;
793 END IF;
794
795 END IF;
796
797 -- IF the record has primary_contact_flag set to 'Y' and there is
798 -- another primary contact record for the same sales_lead_id existed
799 -- then update the existing primary contact record's
800 -- primary_contact_flag to 'N'
801 IF l_SALES_LEAD_CONTACT_Rec.primary_contact_flag = 'Y'
802 THEN
803 OPEN C_Get_Primary_Contact(p_SALES_LEAD_ID);
804 FETCH C_Get_Primary_Contact into l_lead_contact_id,
805 l_contact_party_id, l_contact_phone_id, l_cnt_person_party_id;
806
807 IF C_Get_Primary_Contact%FOUND THEN
808 UPDATE AS_SALES_LEAD_CONTACTS
809 SET primary_contact_flag = 'N'
810 WHERE lead_contact_id = l_lead_contact_id;
811 END IF;
812 CLOSE C_Get_Primary_Contact;
813 END IF;
814
815 -- Debug Message
816 IF (AS_DEBUG_LOW_ON) THEN
817
818 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
819 'Calling CONTACTS_Insert_Row');
820 END IF;
821
822 l_lead_contact_id := l_sales_lead_contact_rec.lead_contact_id;
823
824 -- Invoke table handler
825 AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Insert_Row(
826 px_LEAD_CONTACT_ID => l_LEAD_CONTACT_ID,
827 p_SALES_LEAD_ID => l_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID,
828 p_CONTACT_ID => l_SALES_LEAD_CONTACT_rec.CONTACT_ID,
829 p_CONTACT_PARTY_ID => l_SALES_LEAD_CONTACT_rec.CONTACT_PARTY_ID,
830 p_LAST_UPDATE_DATE => SYSDATE,
831 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
832 p_CREATION_DATE => SYSDATE,
833 p_CREATED_BY => FND_GLOBAL.USER_ID,
834 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
835 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
836 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
837 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
838 p_PROGRAM_UPDATE_DATE => SYSDATE,
839 p_ENABLED_FLAG => l_SALES_LEAD_CONTACT_rec.ENABLED_FLAG,
840 p_RANK => l_SALES_LEAD_CONTACT_rec.RANK,
841 p_CUSTOMER_ID => l_SALES_LEAD_CONTACT_rec.CUSTOMER_ID,
842 p_ADDRESS_ID => l_SALES_LEAD_CONTACT_rec.ADDRESS_ID,
843 p_PHONE_ID => l_SALES_LEAD_CONTACT_rec.PHONE_ID,
844 p_CONTACT_ROLE_CODE => l_SALES_LEAD_CONTACT_rec.CONTACT_ROLE_CODE,
845 p_PRIMARY_CONTACT_FLAG =>
846 l_SALES_LEAD_CONTACT_rec.PRIMARY_CONTACT_FLAG,
847 p_ATTRIBUTE_CATEGORY =>l_SALES_LEAD_CONTACT_rec.ATTRIBUTE_CATEGORY,
848 p_ATTRIBUTE1 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE1,
849 p_ATTRIBUTE2 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE2,
850 p_ATTRIBUTE3 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE3,
851 p_ATTRIBUTE4 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE4,
852 p_ATTRIBUTE5 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE5,
853 p_ATTRIBUTE6 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE6,
854 p_ATTRIBUTE7 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE7,
855 p_ATTRIBUTE8 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE8,
856 p_ATTRIBUTE9 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE9,
857 p_ATTRIBUTE10 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE10,
858 p_ATTRIBUTE11 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE11,
859 p_ATTRIBUTE12 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE12,
860 p_ATTRIBUTE13 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE13,
861 p_ATTRIBUTE14 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE14,
862 p_ATTRIBUTE15 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE15);
863 -- p_SECURITY_GROUP_ID => l_SALES_LEAD_CONTACT_rec.SECURITY_GROUP_ID);
864
865 x_sales_lead_cnt_out_tbl(l_curr_row).lead_contact_id
866 := l_lead_contact_id;
867 x_sales_lead_cnt_out_tbl(l_curr_row).return_status := x_return_status;
868
869 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872
873 END LOOP;
874
875 -- Debug Message
876 IF (AS_DEBUG_LOW_ON) THEN
877
878 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
879 'Updating the header table for last updated');
880 END IF;
881
882 UPDATE as_sales_leads
883 SET last_update_date = SYSDATE,
884 last_updated_by = FND_GLOBAL.USER_ID,
885 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
886 WHERE sales_lead_id = p_sales_lead_id;
887
888 -- Debug Message
889 IF (AS_DEBUG_LOW_ON) THEN
890
891 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
892 'Calling Check_primary_contact');
893 END IF;
894
895 -- Check IF there is only one primary contact
896 Check_primary_contact (
897 P_Api_Version_Number => 2.0
898 ,P_Init_Msg_List => FND_API.G_FALSE
899 ,P_Commit => FND_API.G_FALSE
900 ,p_validation_level => P_Validation_Level
901 ,P_Check_Access_Flag => P_Check_Access_Flag
902 ,P_Admin_Flag => P_Admin_Flag
903 ,P_Admin_Group_Id => P_Admin_Group_Id
904 ,P_identity_salesforce_id => P_identity_salesforce_id
905 ,P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl
906 ,P_SALES_LEAD_ID => P_SALES_LEAD_ID
907 ,X_Return_Status => x_Return_Status
908 ,X_Msg_Count => X_Msg_Count
909 ,X_Msg_Data => X_Msg_Data
910 );
911
912 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915
916 -- ffang 082801, for bug 1939730, denorm primary contact's
917 -- contact_party_id to as_sales_leads.primary_contact_party_id
918 -- ckapoor 011102 , for bug 2098158, denorm the party id of the
919 -- primary contact (not the relationship) to as_sales_leads.primary_cnt_person_party_id
920 -- also denorm the phone_id of primary contact to as_sales_leads.primary_contact_phone_id
921
922 OPEN C_Get_Primary_Contact(p_SALES_LEAD_ID);
923 FETCH C_Get_Primary_Contact into l_lead_contact_id, l_contact_party_id, l_contact_phone_id, l_cnt_person_party_id;
924 IF C_Get_Primary_Contact%FOUND THEN
925 UPDATE AS_SALES_LEADS
926 SET PRIMARY_CONTACT_PARTY_ID = l_contact_party_id,
927 PRIMARY_CONTACT_PHONE_ID = l_contact_phone_id,
928 PRIMARY_CNT_PERSON_PARTY_ID = l_cnt_person_party_id
929 WHERE sales_lead_id = p_SALES_LEAD_ID;
930 ELSE -- no primary contact found
931 UPDATE AS_SALES_LEADS
932 SET PRIMARY_CONTACT_PARTY_ID = NULL,
933 PRIMARY_CONTACT_PHONE_ID = NULL,
934 PRIMARY_CNT_PERSON_PARTY_ID = NULL
935 WHERE sales_lead_id = p_SALES_LEAD_ID;
936 END IF;
937
938 CLOSE C_Get_Primary_Contact;
939
940 -- Debug Message
941 IF (AS_DEBUG_LOW_ON) THEN
942
943 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
944 'Primary Contact for '||p_SALES_LEAD_ID||':'
945 ||l_contact_party_id);
946 END IF;
947
948 --
949 -- END of API body
950 --
951
952 -- Standard check for p_commit
953 IF FND_API.to_Boolean( p_commit )
954 THEN
955 COMMIT WORK;
956 END IF;
957
958
959 -- Debug Message
960 IF (AS_DEBUG_LOW_ON) THEN
961
962 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
963 'PVT: ' || l_api_name || ' End');
964 END IF;
965
966
967 -- Standard call to get message count and IF count is 1, get message info.
968 FND_MSG_PUB.Count_And_Get
969 ( p_count => x_msg_count,
970 p_data => x_msg_data
971 );
972
973 EXCEPTION
974 WHEN FND_API.G_EXC_ERROR THEN
975 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
976 P_API_NAME => L_API_NAME
977 ,P_PKG_NAME => G_PKG_NAME
978 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
979 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
980 ,X_MSG_COUNT => X_MSG_COUNT
981 ,X_MSG_DATA => X_MSG_DATA
982 ,X_RETURN_STATUS => X_RETURN_STATUS);
983
984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
985 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
986 P_API_NAME => L_API_NAME
987 ,P_PKG_NAME => G_PKG_NAME
988 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
989 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
990 ,X_MSG_COUNT => X_MSG_COUNT
991 ,X_MSG_DATA => X_MSG_DATA
992 ,X_RETURN_STATUS => X_RETURN_STATUS);
993
994 WHEN OTHERS THEN
995 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
996 P_API_NAME => L_API_NAME
997 ,P_PKG_NAME => G_PKG_NAME
998 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
999 ,P_SQLCODE => SQLCODE
1000 ,P_SQLERRM => SQLERRM
1001 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1002 ,X_MSG_COUNT => X_MSG_COUNT
1003 ,X_MSG_DATA => X_MSG_DATA
1004 ,X_RETURN_STATUS => X_RETURN_STATUS);
1005 END Create_sales_lead_contacts;
1006
1007
1008 PROCEDURE Update_sales_lead_contacts(
1009 P_Api_Version_Number IN NUMBER,
1010 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1011 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1012 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1013 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1014 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1015 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1016 P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
1017 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1018 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1019 P_SALES_LEAD_CONTACT_Tbl IN
1020 AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type,
1021 X_SALES_LEAD_CNT_OUT_Tbl OUT
1022 AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
1023 X_Return_Status OUT NOCOPY VARCHAR2,
1024 X_Msg_Count OUT NOCOPY NUMBER,
1025 X_Msg_Data OUT NOCOPY VARCHAR2
1026 )
1027 IS
1028 Cursor C_Get_contact(c_LEAD_CONTACT_ID Number) IS
1029 Select LAST_UPDATE_DATE
1030 From AS_SALES_LEAD_CONTACTS
1031 Where lead_contact_id = c_LEAD_CONTACT_ID
1032 For Update NOWAIT;
1033
1034 l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_lead_contacts';
1035 l_api_version_number CONSTANT NUMBER := 2.0;
1036 -- Local Variables
1037 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1038 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1039 l_tar_SALES_LEAD_CONTACT_rec AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Rec_Type;
1040 l_sales_lead_id NUMBER;
1041 l_last_update_date DATE;
1042 l_update_access_flag VARCHAR2(1);
1043 l_member_role VARCHAR2(5);
1044 l_member_access VARCHAR2(5);
1045 l_contact_party_id NUMBER;
1046 l_contact_phone_id NUMBER;
1047 l_cnt_person_party_id NUMBER;
1048
1049
1050
1051 -- for bug 2098158 ckapoor - change cursor to retrieve the phone and party id of contact also
1052 Cursor C_Get_Pri_Contact ( c_SALES_LEAD_ID NUMBER ) IS
1053 SELECT slc.CONTACT_PARTY_ID, slc.PHONE_ID, r.subject_id
1054 FROM as_sales_lead_contacts slc, hz_relationships r
1055 WHERE slc.sales_lead_id = c_SALES_LEAD_ID
1056 and slc.enabled_flag = 'Y'
1057 and slc.primary_contact_flag = 'Y'
1058 and r.party_id = slc.contact_party_id
1059 and r.object_id = slc.customer_id;
1060
1061 BEGIN
1062 -- Standard Start of API savepoint
1063 SAVEPOINT UPDATE_SALES_LEAD_CONTACTS_PVT;
1064
1065 -- Standard call to check for call compatibility.
1066 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1067 p_api_version_number,
1068 l_api_name,
1069 G_PKG_NAME)
1070 THEN
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073
1074 -- Initialize message list IF p_init_msg_list is set to TRUE.
1075 IF FND_API.to_Boolean( p_init_msg_list )
1076 THEN
1077 FND_MSG_PUB.initialize;
1078 END IF;
1079
1080 -- Debug Message
1081 IF (AS_DEBUG_LOW_ON) THEN
1082
1083 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1084 'PVT: ' || l_api_name || ' Start');
1085 END IF;
1086
1087 -- Initialize API return status to SUCCESS
1088 x_return_status := FND_API.G_RET_STS_SUCCESS;
1089
1090 --
1091 -- Api body
1092 --
1093
1094 -- ******************************************************************
1095 -- Validate Environment
1096 -- ******************************************************************
1097 IF FND_GLOBAL.User_Id IS NULL
1098 THEN
1099 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1100 THEN
1101 AS_UTILITY_PVT.Set_Message(
1102 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1103 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1104 p_token1 => 'PROFILE',
1105 p_token1_value => 'USER_ID');
1106 END IF;
1107 RAISE FND_API.G_EXC_ERROR;
1108 END IF;
1109
1110 IF (p_validation_level = fnd_api.g_valid_level_full)
1111 THEN
1112 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1113 p_api_version_number => 2.0
1114 ,p_init_msg_list => p_init_msg_list
1115 ,p_salesforce_id => P_Identity_Salesforce_Id
1116 ,p_admin_group_id => p_admin_group_id
1117 ,x_return_status => x_return_status
1118 ,x_msg_count => x_msg_count
1119 ,x_msg_data => x_msg_data
1120 ,x_sales_member_rec => l_identity_sales_member_rec);
1121 END IF;
1122
1123 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1124 RAISE FND_API.G_EXC_ERROR;
1125 END IF;
1126
1127 FOR l_curr_row IN 1..p_sales_lead_contact_tbl.count LOOP
1128 x_sales_lead_cnt_out_tbl(l_curr_row).return_status
1129 := FND_API.G_RET_STS_SUCCESS;
1130
1131 -- Progress Message
1132 --
1133 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1134 THEN
1135 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1136 FND_MESSAGE.Set_Token ('ROW', 'SALES_LEAD_CONTACT', TRUE);
1137 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1138 FND_MSG_PUB.Add;
1139 END IF;
1140
1141 l_tar_sales_lead_contact_rec := p_sales_lead_contact_tbl(l_curr_row);
1142
1143 IF (AS_DEBUG_LOW_ON) THEN
1144
1145
1146
1147 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1148 'lead_contact_id: ' || l_tar_sales_lead_contact_rec.lead_contact_id);
1149
1150 END IF;
1151
1152 -- Debug message
1153 IF (AS_DEBUG_LOW_ON) THEN
1154
1155 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1156 'Calling Validate_sales_lead');
1157 END IF;
1158
1159 -- Debug Message
1160 IF (AS_DEBUG_LOW_ON) THEN
1161
1162 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1163 'Open C_Get_sales_lead_contact');
1164 END IF;
1165
1166 Open C_Get_contact(l_tar_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID);
1167 Fetch C_Get_contact into l_last_update_date;
1168
1169 IF ( C_Get_contact%NOTFOUND) THEN
1170 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1171 THEN
1172 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1173 FND_MESSAGE.Set_Token ('INFO', 'SALES_LEAD_CONTACT', FALSE);
1174 FND_MSG_PUB.Add;
1175 END IF;
1176 raise FND_API.G_EXC_ERROR;
1177 END IF;
1178
1179 -- Debug Message
1180 IF (AS_DEBUG_LOW_ON) THEN
1181
1182 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1183 'Close C_Get_sales_lead_contact');
1184 END IF;
1185 Close C_Get_contact;
1186
1187 -- Check Whether record has been changed by someone else
1188 IF (l_tar_SALES_LEAD_CONTACT_rec.last_update_date is NULL or
1189 l_tar_SALES_LEAD_CONTACT_rec.last_update_date = FND_API.G_MISS_Date )
1190 THEN
1191 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1192 THEN
1193 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1194 FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
1195 FND_MSG_PUB.ADD;
1196 END IF;
1197 raise FND_API.G_EXC_ERROR;
1198 END IF;
1199
1200 IF (l_tar_SALES_LEAD_CONTACT_rec.last_update_date <> l_last_update_date)
1201 THEN
1202 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1203 THEN
1204 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
1205 FND_MESSAGE.Set_Token('INFO', 'SALES_LEAD_CONTACT', FALSE);
1206 FND_MSG_PUB.ADD;
1207 END IF;
1208 raise FND_API.G_EXC_ERROR;
1209 END IF;
1210
1211 -- Debug message
1212 IF (AS_DEBUG_LOW_ON) THEN
1213
1214 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1215 'Calling Validate_sales_lead_contact');
1216 END IF;
1217
1218 -- Invoke validation procedures
1219 Validate_sales_lead_contact(
1220 p_init_msg_list => FND_API.G_FALSE,
1221 p_validation_level => p_validation_level,
1222 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
1223 P_SALES_LEAD_CONTACT_Rec => l_tar_SALES_LEAD_CONTACT_Rec,
1224 x_return_status => x_return_status,
1225 x_msg_count => x_msg_count,
1226 x_msg_data => x_msg_data);
1227
1228 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1229 x_sales_lead_cnt_out_tbl(l_curr_row).return_status:=x_return_status;
1230 RAISE FND_API.G_EXC_ERROR;
1231 END IF;
1232
1233 IF(P_Check_Access_Flag = 'Y') THEN
1234 -- Call Get_Access_Profiles to get access_profile_rec
1235 IF (AS_DEBUG_LOW_ON) THEN
1236
1237 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1238 'Calling Get_Access_Profiles');
1239 END IF;
1240
1241 AS_SALES_LEADS_PUB.Get_Access_Profiles(
1242 p_profile_tbl => p_sales_lead_profile_tbl,
1243 x_access_profile_rec => l_access_profile_rec);
1244
1245 IF (AS_DEBUG_LOW_ON) THEN
1246
1247
1248
1249 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1250 'Calling Has_updateLeadAccess');
1251
1252 END IF;
1253
1254 IF (AS_DEBUG_LOW_ON) THEN
1255
1256
1257
1258 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1259 'sales_lead_id: ' || l_tar_sales_lead_contact_rec.sales_lead_id);
1260
1261 END IF;
1262 AS_ACCESS_PUB.Has_updateLeadAccess(
1263 p_api_version_number => 2.0
1264 ,p_init_msg_list => FND_API.G_FALSE
1265 ,p_validation_level => p_validation_level
1266 ,p_access_profile_rec => l_access_profile_rec
1267 ,p_admin_flag => p_admin_flag
1268 ,p_admin_group_id => p_admin_group_id
1269 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1270 ,p_sales_lead_id => l_tar_sales_lead_contact_rec.sales_lead_id
1271 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
1272 ,p_identity_salesforce_id => p_identity_salesforce_id
1273 ,p_partner_cont_party_id => NULL
1274 ,x_return_status => x_return_status
1275 ,x_msg_count => x_msg_count
1276 ,x_msg_data => x_msg_data
1277 ,x_update_access_flag => l_update_access_flag);
1278
1279 IF l_update_access_flag <> 'Y' THEN
1280 IF (AS_DEBUG_ERROR_ON) THEN
1281
1282 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1283 'API_NO_CREATE_PRIVILEGE');
1284 END IF;
1285 RAISE FND_API.G_EXC_ERROR;
1286 END IF;
1287
1288 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1289 RAISE FND_API.G_EXC_ERROR;
1290 END IF;
1291
1292 END IF;
1293
1294 -- Check whether the record is marked as primary contact; IF yes,
1295 -- update the primary contact already in the table
1296 -- Debug Message
1297 IF (AS_DEBUG_LOW_ON) THEN
1298
1299 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1300 'Updating existing primary cont');
1301 END IF;
1302
1303 IF l_tar_SALES_LEAD_CONTACT_Rec.primary_contact_flag = 'Y'
1304 THEN
1305 UPDATE as_sales_lead_contacts
1306 SET primary_contact_flag = 'N'
1307 WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_Rec.sales_lead_id
1308 and PRIMARY_CONTACT_FLAG = 'Y'
1309 and enabled_flag = 'Y';
1310 END IF;
1311
1312 -- Debug Message
1313 IF (AS_DEBUG_LOW_ON) THEN
1314
1315 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1316 'Calling CONTACTS_Update_Row');
1317 END IF;
1318 -- Invoke table handler
1319 AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Update_Row(
1320 p_LEAD_CONTACT_ID => l_tar_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID,
1321 p_SALES_LEAD_ID => l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID,
1322 p_CONTACT_ID => l_tar_SALES_LEAD_CONTACT_rec.CONTACT_ID,
1323 p_CONTACT_PARTY_ID => l_tar_SALES_LEAD_CONTACT_rec.CONTACT_PARTY_ID,
1324 p_LAST_UPDATE_DATE => SYSDATE,
1325 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1326 p_CREATION_DATE => l_tar_SALES_LEAD_CONTACT_rec.CREATION_DATE,
1327 p_CREATED_BY => l_tar_SALES_LEAD_CONTACT_rec.CREATED_BY,
1328 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1329 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
1330 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
1331 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
1332 p_PROGRAM_UPDATE_DATE => SYSDATE,
1333 p_ENABLED_FLAG => NVL(l_tar_SALES_LEAD_CONTACT_rec.ENABLED_FLAG,
1334 'Y'),
1335 p_RANK => l_tar_SALES_LEAD_CONTACT_rec.RANK,
1336 p_CUSTOMER_ID => l_tar_SALES_LEAD_CONTACT_rec.CUSTOMER_ID,
1337 p_ADDRESS_ID => l_tar_SALES_LEAD_CONTACT_rec.ADDRESS_ID,
1338 p_PHONE_ID => l_tar_SALES_LEAD_CONTACT_rec.PHONE_ID,
1339 p_CONTACT_ROLE_CODE =>
1340 l_tar_SALES_LEAD_CONTACT_rec.CONTACT_ROLE_CODE,
1341 p_PRIMARY_CONTACT_FLAG =>
1342 l_tar_SALES_LEAD_CONTACT_rec.PRIMARY_CONTACT_FLAG,
1343 p_ATTRIBUTE_CATEGORY =>
1344 l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE_CATEGORY,
1345 p_ATTRIBUTE1 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE1,
1346 p_ATTRIBUTE2 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE2,
1347 p_ATTRIBUTE3 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE3,
1348 p_ATTRIBUTE4 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE4,
1349 p_ATTRIBUTE5 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE5,
1350 p_ATTRIBUTE6 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE6,
1351 p_ATTRIBUTE7 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE7,
1352 p_ATTRIBUTE8 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE8,
1353 p_ATTRIBUTE9 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE9,
1354 p_ATTRIBUTE10 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE10,
1355 p_ATTRIBUTE11 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE11,
1356 p_ATTRIBUTE12 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE12,
1357 p_ATTRIBUTE13 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE13,
1358 p_ATTRIBUTE14 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE14,
1359 p_ATTRIBUTE15 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE15);
1360 -- p_SECURITY_GROUP_ID =>
1361 -- l_tar_SALES_LEAD_CONTACT_rec.SECURITY_GROUP_ID);
1362
1363 x_sales_lead_cnt_out_tbl(l_curr_row).lead_contact_id
1364 := l_tar_sales_lead_contact_rec.lead_contact_id;
1365 x_sales_lead_cnt_out_tbl(l_curr_row).return_status := x_return_status;
1366
1367 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1368 RAISE FND_API.G_EXC_ERROR;
1369 END IF;
1370 END LOOP;
1371
1372 -- Debug Message
1373 IF (AS_DEBUG_LOW_ON) THEN
1374
1375 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1376 'Updating the header table for last updated');
1377 END IF;
1378
1379 UPDATE as_sales_leads
1380 SET last_update_date = SYSDATE,
1381 last_updated_by = FND_GLOBAL.USER_ID,
1382 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
1383 WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_Rec.sales_lead_id;
1384
1385
1386 -- Debug Message
1387 IF (AS_DEBUG_LOW_ON) THEN
1388
1389 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1390 'Calling Check_primary_contact');
1391 END IF;
1392
1393 -- Check IF there is only one primary contact
1394 Check_primary_contact (
1395 P_Api_Version_Number => 2.0
1396 ,P_Init_Msg_List => FND_API.G_FALSE
1397 ,P_Commit => FND_API.G_FALSE
1398 ,p_validation_level => P_Validation_Level
1399 ,P_Check_Access_Flag => P_Check_Access_Flag
1400 ,P_Admin_Flag => P_Admin_Flag
1401 ,P_Admin_Group_Id => P_Admin_Group_Id
1402 ,P_identity_salesforce_id => P_identity_salesforce_id
1403 ,P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl
1404 ,P_SALES_LEAD_ID => l_tar_SALES_LEAD_CONTACT_Rec.sales_lead_id
1405 ,X_Return_Status => x_Return_Status
1406 ,X_Msg_Count => X_Msg_Count
1407 ,X_Msg_Data => X_Msg_Data
1408 );
1409
1410 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1411 RAISE FND_API.G_EXC_ERROR;
1412 END IF;
1413
1414 -- ffang 082801, for bug 1939730, denorm primary contact's
1415 -- contact_party_id to as_sales_leads.primary_contact_party_id
1416
1417 -- ckapoor 011102 , for bug 2098158, denorm the party id of the
1418 -- primary contact (not the relationship) to as_sales_leads.primary_cnt_person_party_id
1419 -- also denorm the phone_id of primary contact to as_sales_leads.primary_contact_phone_id
1420
1421
1422 OPEN C_Get_Pri_Contact(l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID);
1423 FETCH C_Get_Pri_Contact into l_contact_party_id, l_contact_phone_id, l_cnt_person_party_id;
1424 IF C_Get_Pri_Contact%FOUND THEN
1425 UPDATE AS_SALES_LEADS
1426 SET PRIMARY_CONTACT_PARTY_ID = l_contact_party_id,
1427 PRIMARY_CONTACT_PHONE_ID = l_contact_phone_id,
1428 PRIMARY_CNT_PERSON_PARTY_ID = l_cnt_person_party_id
1429 WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID;
1430 ELSE -- no primary contact found
1431 UPDATE AS_SALES_LEADS
1432 SET PRIMARY_CONTACT_PARTY_ID = NULL,
1433 PRIMARY_CONTACT_PHONE_ID = NULL,
1434 PRIMARY_CNT_PERSON_PARTY_ID = NULL
1435
1436 WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID;
1437 END IF;
1438
1439 CLOSE C_Get_Pri_Contact;
1440
1441 -- Debug Message
1442 IF (AS_DEBUG_LOW_ON) THEN
1443
1444 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1445 'Primary Contact for '||
1446 l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID||':'
1447 ||l_contact_party_id);
1448 END IF;
1449
1450 --
1451 -- END of API body.
1452 --
1453
1454 -- Standard check for p_commit
1455 IF FND_API.to_Boolean( p_commit )
1456 THEN
1457 COMMIT WORK;
1458 END IF;
1459
1460 -- Debug Message
1461 IF (AS_DEBUG_LOW_ON) THEN
1462
1463 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1464 'PVT: ' || l_api_name || ' End');
1465 END IF;
1466
1467 -- Standard call to get message count and IF count is 1, get message info.
1468 FND_MSG_PUB.Count_And_Get
1469 ( p_count => x_msg_count,
1470 p_data => x_msg_data );
1471
1472 EXCEPTION
1473 WHEN FND_API.G_EXC_ERROR THEN
1474 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1475 P_API_NAME => L_API_NAME
1476 ,P_PKG_NAME => G_PKG_NAME
1477 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1478 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1479 ,X_MSG_COUNT => X_MSG_COUNT
1480 ,X_MSG_DATA => X_MSG_DATA
1481 ,X_RETURN_STATUS => X_RETURN_STATUS);
1482
1483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1484 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1485 P_API_NAME => L_API_NAME
1486 ,P_PKG_NAME => G_PKG_NAME
1487 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1488 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1489 ,X_MSG_COUNT => X_MSG_COUNT
1490 ,X_MSG_DATA => X_MSG_DATA
1491 ,X_RETURN_STATUS => X_RETURN_STATUS);
1492
1493 WHEN OTHERS THEN
1494 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1495 P_API_NAME => L_API_NAME
1496 ,P_PKG_NAME => G_PKG_NAME
1497 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1498 ,P_SQLCODE => SQLCODE
1499 ,P_SQLERRM => SQLERRM
1500 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1501 ,X_MSG_COUNT => X_MSG_COUNT
1502 ,X_MSG_DATA => X_MSG_DATA
1503 ,X_RETURN_STATUS => X_RETURN_STATUS);
1504 END Update_sales_lead_contacts;
1505
1506
1507 PROCEDURE Delete_sales_lead_contacts(
1508 P_Api_Version_Number IN NUMBER,
1509 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1510 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1511 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1512 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1513 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1514 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1515 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
1516 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1517 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1518 P_SALES_LEAD_CONTACT_Tbl IN
1519 AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type,
1520 X_SALES_LEAD_CNT_OUT_Tbl OUT
1521 AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
1522 X_Return_Status OUT NOCOPY VARCHAR2,
1523 X_Msg_Count OUT NOCOPY NUMBER,
1524 X_Msg_Data OUT NOCOPY VARCHAR2
1525 )
1526
1527 IS
1528 CURSOR C_Get_cont_del(c_LEAD_CONTACT_ID Number) IS
1529 SELECT
1530 SALES_LEAD_ID,
1531 PRIMARY_CONTACT_FLAG,
1532 CONTACT_PARTY_ID
1533 FROM AS_SALES_LEAD_CONTACTS
1534 WHERE lead_contact_id = c_LEAD_CONTACT_ID;
1535
1536 l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_lead_contacts';
1537 l_api_version_number CONSTANT NUMBER := 2.0;
1538 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1539 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1540 l_sales_lead_contact_rec AS_SALES_LEADS_PUB.Sales_Lead_Contact_Rec_Type;
1541 l_sales_lead_id NUMBER;
1542 l_primary_contact_flag VARCHAR2(1);
1543 l_contact_party_id NUMBER;
1544 l_update_access_flag VARCHAR2(1);
1545 l_member_role VARCHAR2(5);
1546 l_member_access VARCHAR2(5);
1547 BEGIN
1548 -- Standard Start of API savepoint
1549 SAVEPOINT DELETE_SALES_LEAD_CONTACTS_PVT;
1550
1551 -- Standard call to check for call compatibility.
1552 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1553 p_api_version_number,
1554 l_api_name,
1555 G_PKG_NAME)
1556 THEN
1557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1558 END IF;
1559
1560 -- Initialize message list IF p_init_msg_list is set to TRUE.
1561 IF FND_API.to_Boolean( p_init_msg_list )
1562 THEN
1563 FND_MSG_PUB.initialize;
1564 END IF;
1565
1566 -- Debug Message
1567 IF (AS_DEBUG_LOW_ON) THEN
1568
1569 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1570 'PVT:' || l_api_name || 'start');
1571 END IF;
1572
1573 -- Initialize API return status to SUCCESS
1574 x_return_status := FND_API.G_RET_STS_SUCCESS;
1575
1576 --
1577 -- Api body
1578 --
1579
1580 -- ******************************************************************
1581 -- Validate Environment
1582 -- ******************************************************************
1583 IF FND_GLOBAL.User_Id IS NULL
1584 THEN
1585 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1586 THEN
1587 AS_UTILITY_PVT.Set_Message(
1588 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1589 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1590 p_token1 => 'PROFILE',
1591 p_token1_value => 'USER_ID');
1592 END IF;
1593 RAISE FND_API.G_EXC_ERROR;
1594 END IF;
1595
1596 IF (p_validation_level = fnd_api.g_valid_level_full)
1597 THEN
1598 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1599 p_api_version_number => 2.0
1600 ,p_init_msg_list => p_init_msg_list
1601 ,p_salesforce_id => P_Identity_Salesforce_Id
1602 ,p_admin_group_id => p_admin_group_id
1603 ,x_return_status => x_return_status
1604 ,x_msg_count => x_msg_count
1605 ,x_msg_data => x_msg_data
1606 ,x_sales_member_rec => l_identity_sales_member_rec);
1607 END IF;
1608
1609 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612
1613
1614 FOR l_curr_row IN 1..p_sales_lead_contact_tbl.count LOOP
1615 x_sales_lead_cnt_out_tbl(l_curr_row).return_status
1616 := FND_API.G_RET_STS_SUCCESS;
1617
1618 -- Progress Message
1619 --
1620 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1621 THEN
1622 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1623 FND_MESSAGE.Set_Token ('ROW', 'SALES_LEAD_CONTACT', TRUE);
1624 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1625 FND_MSG_PUB.Add;
1626 END IF;
1627
1628 l_sales_lead_contact_rec := p_sales_lead_contact_tbl(l_curr_row);
1629
1630 -- Debug Message
1631 IF (AS_DEBUG_LOW_ON) THEN
1632
1633 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1634 'Open C_Get_cont_del');
1635 END IF;
1636
1637 -- Get the whole record
1638 Open C_Get_cont_del(l_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID);
1639 Fetch C_Get_cont_del into l_sales_lead_id, l_primary_contact_flag,
1640 l_contact_party_id;
1641
1642 IF ( C_Get_cont_del%NOTFOUND) THEN
1643 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1644 THEN
1645 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1646 FND_MESSAGE.Set_Token ('INFO', 'SALES_LEAD_CONTACT', FALSE);
1647 FND_MSG_PUB.Add;
1648 END IF;
1649 raise FND_API.G_EXC_ERROR;
1650 END IF;
1651
1652 -- Debug Message
1653 IF (AS_DEBUG_LOW_ON) THEN
1654
1655 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1656 'Close C_Get_cont_del');
1657 END IF;
1658 Close C_Get_cont_del;
1659
1660 IF(P_Check_Access_Flag = 'Y') THEN
1661 -- Call Get_Access_Profiles to get access_profile_rec
1662 IF (AS_DEBUG_LOW_ON) THEN
1663
1664 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1665 'Calling Get_Access_Profiles');
1666 END IF;
1667
1668 AS_SALES_LEADS_PUB.Get_Access_Profiles(
1669 p_profile_tbl => p_sales_lead_profile_tbl,
1670 x_access_profile_rec => l_access_profile_rec);
1671
1672 IF (AS_DEBUG_LOW_ON) THEN
1673
1674
1675
1676 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1677 'Calling Has_updateLeadAccess');
1678
1679 END IF;
1680
1681 AS_ACCESS_PUB.Has_updateLeadAccess(
1682 p_api_version_number => 2.0
1683 ,p_init_msg_list => FND_API.G_FALSE
1684 ,p_validation_level => p_validation_level
1685 ,p_access_profile_rec => l_access_profile_rec
1686 ,p_admin_flag => p_admin_flag
1687 ,p_admin_group_id => p_admin_group_id
1688 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1689 ,p_sales_lead_id => l_sales_lead_id
1690 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
1691 ,p_identity_salesforce_id => p_identity_salesforce_id
1692 ,p_partner_cont_party_id => NULL
1693 ,x_return_status => x_return_status
1694 ,x_msg_count => x_msg_count
1695 ,x_msg_data => x_msg_data
1696 ,x_update_access_flag => l_update_access_flag);
1697
1698 IF l_update_access_flag <> 'Y' THEN
1699 IF (AS_DEBUG_ERROR_ON) THEN
1700
1701 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1702 'API_NO_CREATE_PRIVILEGE');
1703 END IF;
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;
1706
1707 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1708 RAISE FND_API.G_EXC_ERROR;
1709 END IF;
1710
1711 END IF;
1712
1713 -- Debug Message
1714 IF (AS_DEBUG_LOW_ON) THEN
1715
1716 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1717 'Calling CONTACTS_Delete_Row');
1718 END IF;
1719
1720 -- Invoke table handler
1721 AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Delete_Row(
1722 p_LEAD_CONTACT_ID => l_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID);
1723
1724 x_sales_lead_cnt_out_tbl(l_curr_row).lead_contact_id
1725 := l_sales_lead_contact_rec.lead_contact_id;
1726 x_sales_lead_cnt_out_tbl(l_curr_row).return_status := x_return_status;
1727
1728 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1729 RAISE FND_API.G_EXC_ERROR;
1730 END IF;
1731
1732 -- Debug Message
1733 IF (AS_DEBUG_LOW_ON) THEN
1734
1735 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1736 'Updating the header table for last updated');
1737 END IF;
1738
1739 UPDATE as_sales_leads
1740 SET last_update_date = SYSDATE,
1741 last_updated_by = FND_GLOBAL.USER_ID,
1742 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
1743 WHERE sales_lead_id = l_sales_lead_id;
1744
1745
1746 -- ffang 090601, if primary contact is deleted, update as_sales_leads
1747 -- to clean up primary_contact_party_id
1748 -- ckapoor 011102 bug 2098158 clean up primary_contact_phone_id and primary_cnt_person_party_id
1749 IF l_PRIMARY_CONTACT_FLAG = 'Y' THEN
1750 IF (AS_DEBUG_LOW_ON) THEN
1751
1752 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1753 'Primary contact is deleted');
1754 END IF;
1755 update as_sales_leads
1756 set primary_contact_party_id = NULL,
1757 primary_contact_phone_id = NULL,
1758 primary_cnt_person_party_id = NULL
1759 where sales_lead_id = l_sales_lead_id
1760 and primary_contact_party_id = l_contact_party_id;
1761 END IF;
1762 END LOOP;
1763
1764 --
1765 -- END of API body
1766 --
1767
1768 -- Standard check for p_commit
1769 IF FND_API.to_Boolean( p_commit )
1770 THEN
1771 COMMIT WORK;
1772 END IF;
1773
1774
1775 -- Debug Message
1776 IF (AS_DEBUG_LOW_ON) THEN
1777
1778 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1779 'PVT: ' || l_api_name || ' End');
1780 END IF;
1781
1782 -- Standard call to get message count and IF count is 1, get message info.
1783 FND_MSG_PUB.Count_And_Get
1784 ( p_count => x_msg_count,
1785 p_data => x_msg_data );
1786
1787 EXCEPTION
1788 WHEN FND_API.G_EXC_ERROR THEN
1789 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1790 P_API_NAME => L_API_NAME
1791 ,P_PKG_NAME => G_PKG_NAME
1792 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1793 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1794 ,X_MSG_COUNT => X_MSG_COUNT
1795 ,X_MSG_DATA => X_MSG_DATA
1796 ,X_RETURN_STATUS => X_RETURN_STATUS);
1797
1798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1799 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1800 P_API_NAME => L_API_NAME
1801 ,P_PKG_NAME => G_PKG_NAME
1802 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1803 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1804 ,X_MSG_COUNT => X_MSG_COUNT
1805 ,X_MSG_DATA => X_MSG_DATA
1806 ,X_RETURN_STATUS => X_RETURN_STATUS);
1807
1808 WHEN OTHERS THEN
1809 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1810 P_API_NAME => L_API_NAME
1811 ,P_PKG_NAME => G_PKG_NAME
1812 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1813 ,P_SQLCODE => SQLCODE
1814 ,P_SQLERRM => SQLERRM
1815 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1816 ,X_MSG_COUNT => X_MSG_COUNT
1817 ,X_MSG_DATA => X_MSG_DATA
1818 ,X_RETURN_STATUS => X_RETURN_STATUS);
1819 END Delete_sales_lead_contacts;
1820
1821
1822 -- API Name: Check_primary_contact
1823
1824 PROCEDURE Check_primary_contact (
1825 P_Api_Version_Number IN NUMBER,
1826 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1827 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1828 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1829 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1830 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1831 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1832 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
1833 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1834 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1835 P_SALES_LEAD_ID IN NUMBER,
1836 X_Return_Status OUT NOCOPY VARCHAR2,
1837 X_Msg_Count OUT NOCOPY NUMBER,
1838 X_Msg_Data OUT NOCOPY VARCHAR2
1839 )
1840 IS
1841 Cursor C_Get_Primary_Contact_Count ( X_SALES_LEAD_ID NUMBER) IS
1842 SELECT count(*)
1843 FROM as_sales_lead_contacts
1844 WHERE sales_lead_id = X_SALES_LEAD_ID
1845 and enabled_flag = 'Y'
1846 and primary_contact_flag = 'Y';
1847 l_count NUMBER;
1848 l_api_name CONSTANT VARCHAR2(30) := 'Check_primary_contact';
1849 l_api_version_number CONSTANT NUMBER := 2.0;
1850 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1851 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1852 l_cnt varchar2(2);
1853 l_update_access_flag VARCHAR2(1);
1854 l_member_role VARCHAR2(5);
1855 l_member_access VARCHAR2(5);
1856
1857 BEGIN
1858 -- Initialize message list IF p_init_msg_list is set to TRUE.
1859 IF FND_API.to_Boolean( p_init_msg_list )
1860 THEN
1861 FND_MSG_PUB.initialize;
1862 END IF;
1863
1864 -- Initialize API return status to SUCCESS
1865 x_return_status := FND_API.G_RET_STS_SUCCESS;
1866
1867 --
1868 -- Api body
1869 --
1870
1871 -- ******************************************************************
1872 -- Validate Environment
1873 -- ******************************************************************
1874 IF FND_GLOBAL.User_Id IS NULL
1875 THEN
1876 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1877 THEN
1878 AS_UTILITY_PVT.Set_Message(
1879 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1880 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1881 p_token1 => 'PROFILE',
1882 p_token1_value => 'USER_ID');
1883 END IF;
1884 RAISE FND_API.G_EXC_ERROR;
1885 END IF;
1886
1887 IF (p_validation_level = fnd_api.g_valid_level_full)
1888 THEN
1889 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1890 p_api_version_number => 2.0
1891 ,p_init_msg_list => p_init_msg_list
1892 ,p_salesforce_id => P_Identity_Salesforce_Id
1893 ,p_admin_group_id => p_admin_group_id
1894 ,x_return_status => x_return_status
1895 ,x_msg_count => x_msg_count
1896 ,x_msg_data => x_msg_data
1897 ,x_sales_member_rec => l_identity_sales_member_rec);
1898 END IF;
1899
1900 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1901 RAISE FND_API.G_EXC_ERROR;
1902 END IF;
1903
1904 OPEN C_Get_Primary_Contact_Count (P_SALES_LEAD_ID);
1905 FETCH C_Get_Primary_Contact_Count into l_count;
1906 IF C_Get_Primary_Contact_Count%NOTFOUND THEN
1907 -- Debug Message
1908 IF (AS_DEBUG_LOW_ON) THEN
1909
1910 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1911 'Private API: No primary contact');
1912 END IF;
1913 END IF;
1914 CLOSE C_Get_Primary_Contact_Count;
1915
1916 IF l_count > 1 THEN
1917 -- Debug Message
1918 IF (AS_DEBUG_LOW_ON) THEN
1919
1920 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1921 'More than one primary contact');
1922 END IF;
1923 AS_UTILITY_PVT.Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1924 p_msg_name => 'API_PRIMARY_CONTACT_DUP_FOUND');
1925 RAISE FND_API.G_EXC_ERROR;
1926 END IF;
1927
1928 --
1929 -- END of API body
1930 --
1931
1932 -- Standard check for p_commit
1933 IF FND_API.to_Boolean( p_commit )
1934 THEN
1935 COMMIT WORK;
1936 END IF;
1937
1938
1939 -- Debug Message
1940 IF (AS_DEBUG_LOW_ON) THEN
1941
1942 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1943 'PVT: ' || l_api_name || ' End');
1944 END IF;
1945
1946 -- Standard call to get message count and IF count is 1, get message info.
1947 FND_MSG_PUB.Count_And_Get
1948 ( p_count => x_msg_count,
1949 p_data => x_msg_data
1950 );
1951
1952 EXCEPTION
1953 WHEN FND_API.G_EXC_ERROR THEN
1954 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1955 P_API_NAME => L_API_NAME
1956 ,P_PKG_NAME => G_PKG_NAME
1957 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1958 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1959 ,X_MSG_COUNT => X_MSG_COUNT
1960 ,X_MSG_DATA => X_MSG_DATA
1961 ,X_RETURN_STATUS => X_RETURN_STATUS);
1962
1963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1964 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1965 P_API_NAME => L_API_NAME
1966 ,P_PKG_NAME => G_PKG_NAME
1967 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1968 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1969 ,X_MSG_COUNT => X_MSG_COUNT
1970 ,X_MSG_DATA => X_MSG_DATA
1971 ,X_RETURN_STATUS => X_RETURN_STATUS);
1972
1973 WHEN OTHERS THEN
1974 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1975 P_API_NAME => L_API_NAME
1976 ,P_PKG_NAME => G_PKG_NAME
1977 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1978 ,P_SQLCODE => SQLCODE
1979 ,P_SQLERRM => SQLERRM
1980 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1981 ,X_MSG_COUNT => X_MSG_COUNT
1982 ,X_MSG_DATA => X_MSG_DATA
1983 ,X_RETURN_STATUS => X_RETURN_STATUS);
1984 END Check_primary_contact;
1985
1986
1987 END AS_SALES_LEAD_CONTACTS_PVT;