[Home] [Help]
PACKAGE BODY: APPS.AS_INTEREST_PVT
Source
1 PACKAGE BODY AS_INTEREST_PVT as
2 /* $Header: asxvintb.pls 120.2 2005/08/04 22:16:50 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_INTEREST_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvintb.pls';
6
7 /* Remove dependency on global variable, use FND_GLOBAL.xxx directly
8 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
9 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
10 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
11 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
12 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
13 */
14 --
15 -- NAME
16 -- AS_INTEREST_PVT
17 --
18 -- PURPOSE
19 -- This is a private API used to create interests (Company Classifications,
20 -- Contact Interests, or Lead Classifications).
21 --
22 -- NOTES
23 -- Create_Interest is a private OSM routine, that should not be called by modules
24 -- outside of OSM
25 --
26 -- HISTORY
27 -- 11/12/96 JKORNBER Created
28 -- 08/28/98 AWU Add update_interest
29 -- Add interest_id, customer_id, address_id,
30 -- contact_id and lead_id into
31 -- interest record
32 -- Changed interest rec default value NULL to
33 -- FND_API.G_MISS for update purpose
34 --
35
36
37 /*************************** PRIVATE ROUTINES *********************************/
38
39 -- Conversion Routines
40 PROCEDURE convert_miss_interest_rec(p_interest_rec IN INTEREST_REC_TYPE,
41 x_interest_rec OUT NOCOPY INTEREST_REC_TYPE) is
42 l_interest_rec INTEREST_REC_TYPE := p_interest_rec;
43 Begin
44 if (l_interest_rec.interest_id = FND_API.G_MISS_NUM)
45 then
46 l_interest_rec.interest_id := NULL;
47 end if;
48 if (l_interest_rec.customer_id = FND_API.G_MISS_NUM)
49 then
50 l_interest_rec.customer_id := NULL;
51 end if;
52 if (l_interest_rec.address_id = FND_API.G_MISS_NUM)
53 then
54 l_interest_rec.address_id := NULL;
55 end if;
56 if (l_interest_rec.contact_id = FND_API.G_MISS_NUM)
57 then
58 l_interest_rec.contact_id := NULL;
59 end if;
60 if (l_interest_rec.lead_id = FND_API.G_MISS_NUM)
61 then
62 l_interest_rec.lead_id := NULL;
63 end if;
64 if (l_interest_rec.interest_type_id = FND_API.G_MISS_NUM)
65 then
66 l_interest_rec.interest_type_id := NULL;
67 end if;
68 if (l_interest_rec.primary_interest_code_id = FND_API.G_MISS_NUM)
69 then
70 l_interest_rec.primary_interest_code_id := NULL;
71 end if;
72 if (l_interest_rec.secondary_interest_code_id = FND_API.G_MISS_NUM)
73 then
74 l_interest_rec.secondary_interest_code_id := NULL;
75 end if;
76 if (l_interest_rec.status_code = FND_API.G_MISS_CHAR)
77 then
78 l_interest_rec.status_code := NULL;
79 end if;
80 if (l_interest_rec.status = FND_API.G_MISS_CHAR)
81 then
82 l_interest_rec.status := NULL;
83 end if;
84 if (l_interest_rec.description = FND_API.G_MISS_CHAR)
85 then
86 l_interest_rec.description := NULL;
87 end if;
88 if (l_interest_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR)
89 then
90 l_interest_rec.ATTRIBUTE_CATEGORY := NULL;
91 end if;
92 if (l_interest_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR)
93 then
94 l_interest_rec.ATTRIBUTE1 := NULL;
95 end if;
96 if (l_interest_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR)
97 then
98 l_interest_rec.ATTRIBUTE2 := NULL;
99 end if;
100 if (l_interest_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR)
101 then
102 l_interest_rec.ATTRIBUTE3 := NULL;
103 end if;
104 if (l_interest_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR)
105 then
106 l_interest_rec.ATTRIBUTE4 := NULL;
107 end if;
108 if (l_interest_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR)
109 then
110 l_interest_rec.ATTRIBUTE5 := NULL;
111 end if;
112 if (l_interest_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR)
113 then
114 l_interest_rec.ATTRIBUTE6 := NULL;
115 end if;
116 if (l_interest_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR)
117 then
118 l_interest_rec.ATTRIBUTE7 := NULL;
119 end if;
120 if (l_interest_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR)
121 then
122 l_interest_rec.ATTRIBUTE8 := NULL;
123 end if;
124 if (l_interest_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR)
125 then
126 l_interest_rec.ATTRIBUTE9 := NULL;
127 end if;
128 if (l_interest_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR)
129 then
130 l_interest_rec.ATTRIBUTE10 := NULL;
131 end if;
132 if (l_interest_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR)
133 then
134 l_interest_rec.ATTRIBUTE11 := NULL;
135 end if;
136 if (l_interest_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR)
137 then
138 l_interest_rec.ATTRIBUTE12 := NULL;
139 end if;
140 if (l_interest_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR)
141 then
142 l_interest_rec.ATTRIBUTE13 := NULL;
143 end if;
144 if (l_interest_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR)
145 then
146 l_interest_rec.ATTRIBUTE14 := NULL;
147 end if;
148 if (l_interest_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR)
149 then
150 l_interest_rec.ATTRIBUTE15 := NULL;
151 end if;
152 if (l_interest_rec.product_category_id = FND_API.G_MISS_NUM)
153 then
154 l_interest_rec.product_category_id := NULL;
155 end if;
156 if (l_interest_rec.product_cat_set_id = FND_API.G_MISS_NUM)
157 then
158 l_interest_rec.product_cat_set_id := NULL;
159 end if;
160
161 x_interest_rec := l_interest_rec;
162
163 end convert_miss_interest_rec;
164
165 -- Name
166 -- Invalid_Use
167 --
168 -- Purpose
169 -- Function to determine if the interest_use_code is consistent with
170 -- the ids passed into the create_interest procedure.
171 -- Returns True if the interest_use_code and ids are inconsistent,
172 -- True if consistent otherwise.
173 --
174 FUNCTION INVALID_USE (p_interest_use_code VARCHAR2,
175 p_customer_id NUMBER,
176 p_address_id NUMBER,
177 p_contact_id NUMBER,
178 p_lead_id NUMBER) RETURN BOOLEAN IS
179 BEGIN
180 IF ( (p_interest_use_code = 'LEAD_CLASSIFICATION')
181 and (p_customer_id is NOT NULL
182 -- and p_address_id is NOT NULL
183 and p_lead_id is NOT NULL) )
184 THEN
185 return FALSE;
186
187 ELSIF ( (p_interest_use_code = 'COMPANY_CLASSIFICATION')
188 and ( p_customer_id is NOT NULL))
189 -- and p_address_id is NOT NULL) )
190 THEN
191 return FALSE;
192
193 ELSIF ( (p_interest_use_code = 'CONTACT_INTEREST')
194 and ( p_customer_id is NOT NULL))
195 -- and p_address_id is NOT NULL) )
196 THEN
197 return FALSE;
198
199 ELSE
200 return TRUE;
201 END IF;
202 END INVALID_USE;
203
204
205 PROCEDURE Validate_party_id (
206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
207 p_party_id IN NUMBER,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2
211 ) IS
212
213 l_val VARCHAR2(1);
214 l_return_status VARCHAR2(1);
215
216 CURSOR C_Party_Exists (X_Party_Id NUMBER) IS
217 SELECT 1
218 FROM HZ_PARTIES CUST
219 WHERE CUST.PARTY_TYPE in ('PERSON', 'ORGANIZATION', 'PARTY_RELATIONSHIP')
220 AND CUST.STATUS IN ('A','I')
221 AND party_id = X_Party_Id;
222
223 BEGIN
224
225 -- initialize message list if p_init_msg_list is set to TRUE;
226
227 IF FND_API.to_Boolean(p_init_msg_list) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 l_return_status := FND_API.G_RET_STS_SUCCESS;
232 open C_Party_Exists(p_party_id);
233 fetch C_Party_Exists into l_val;
234 IF (C_Party_Exists%NOTFOUND) THEN
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
237 THEN
238 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
239 FND_MESSAGE.Set_Token('COLUMN', 'PARTY_ID', FALSE);
240 FND_MSG_PUB.ADD;
241 END IF;
242 END IF;
243 close C_Party_Exists;
244
245 FND_MSG_PUB.Count_And_Get
246 ( p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249
250 END Validate_party_id;
251
252 PROCEDURE Validate_Product_Category ( p_interest_id IN NUMBER,
253 p_product_category_id IN NUMBER,
254 p_product_cat_set_id IN NUMBER,
255 p_interest_status_code IN VARCHAR2,
256 p_return_status OUT NOCOPY VARCHAR2
257 );
258
259 PROCEDURE Validate_Interest_Type ( p_interest_type_id IN NUMBER,
260 p_primary_interest_code_id IN NUMBER,
261 p_secondary_interest_code_id IN NUMBER,
262 p_interest_status_code IN VARCHAR2,
263 p_return_status OUT NOCOPY VARCHAR2
264 );
265
266
267 PROCEDURE Validate_Interest ( p_interest_use_code IN VARCHAR2,
268 p_interest_rec IN INTEREST_REC_TYPE,
269 p_return_status OUT NOCOPY VARCHAR2
270 )
271 IS
272 l_return_status VARCHAR2(1);
273 l_interest_fields_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
274 l_interest_rec INTEREST_REC_TYPE := p_interest_rec;
275 BEGIN
276 l_return_status := FND_API.G_RET_STS_SUCCESS;
277
278 --convert_miss_interest_rec(l_interest_rec,l_interest_rec);
279
280 -- Interest Validation
281 --
282
283 IF (p_interest_use_code = 'CONTACT_INTEREST')
284 THEN
285 Validate_Product_Category( p_interest_id => l_interest_rec.interest_id,
286 p_product_category_id => l_interest_rec.product_category_id,
287 p_product_cat_set_id => l_interest_rec.product_cat_set_id,
288 p_interest_status_code => l_interest_rec.status_code,
289 p_return_status => l_interest_fields_status
290 );
291 ELSE
292 Validate_Interest_Type( p_interest_type_id => l_interest_rec.interest_type_id,
293 p_primary_interest_code_id => l_interest_rec.primary_interest_code_id,
294 p_secondary_interest_code_id => l_interest_rec.secondary_interest_code_id,
295 p_interest_status_code => l_interest_rec.status_code,
296 p_return_status => l_interest_fields_status
297 );
298 END IF;
299
300 IF l_interest_fields_status <> FND_API.G_RET_STS_SUCCESS
301 THEN
302 l_return_status := FND_API.G_RET_STS_ERROR;
303
304 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
305 THEN
306 FND_MESSAGE.Set_Name('AS', 'API_ROW_NOT_PROCESSED');
307 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
308 FND_MSG_PUB.ADD;
309 END IF;
310 END IF;
311
312 p_return_status := l_return_status;
313
314 END Validate_Interest;
315
316
317 /*************************** PUBLIC ROUTINES *********************************/
318
319 PROCEDURE Create_Interest( p_api_version_number IN NUMBER,
320 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
321 p_commit IN VARCHAR2 := FND_API.G_FALSE,
322 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
323 p_interest_tbl IN INTEREST_TBL_TYPE := G_MISS_INTEREST_TBL,
324 p_customer_id IN NUMBER,
325 p_address_id IN NUMBER,
326 p_contact_id IN NUMBER,
327 p_lead_id IN NUMBER,
328 p_interest_use_code IN VARCHAR2,
329 p_check_access_flag IN VARCHAR2,
330 p_admin_flag IN VARCHAR2,
331 p_admin_group_id IN NUMBER,
332 p_identity_salesforce_id IN NUMBER,
333 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
334 p_return_status OUT NOCOPY VARCHAR2,
335 p_msg_count OUT NOCOPY NUMBER,
336 p_msg_data OUT NOCOPY VARCHAR2,
337 p_interest_out_tbl OUT NOCOPY INTEREST_OUT_TBL_TYPE
338 )
339 IS
340 l_api_name CONSTANT VARCHAR2(30) := 'Create_Interest';
341 l_api_version_number CONSTANT NUMBER := 2.0;
342
343 l_interest_count CONSTANT NUMBER := p_interest_tbl.count;
344 l_return_status VARCHAR2(1); -- Local return status equal to p_return_status
345 l_interests_inserted NUMBER := 0; -- Number of successful inserts
346 l_interest_tbl INTEREST_TBL_TYPE;
347
348 l_msg_count NUMBER;
349 l_msg_data VARCHAR2(2000);
350 l_count NUMBER := 0;
351
352 -- Local insert variables
353 l_rowid ROWID;
354 l_interest_id NUMBER;
355 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
356 l_update_access_flag VARCHAR2(1);
357
358 -- Local status table
359 TYPE l_interest_status_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
360 l_return_status_tbl l_interest_status_tbl;
361 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
362 x_lead_id NUMBER;
363
364 CURSOR duplicate_cat_cur(p_customer_id IN NUMBER,
365 p_product_category_id IN NUMBER,
366 p_product_cat_set_id IN NUMBER) IS
367 select 1
368 from AS_INTERESTS_ALL
369 where customer_id = p_customer_id
370 and interest_use_code = 'CONTACT_INTEREST'
371 and product_category_id = p_product_category_id
372 and product_cat_set_id = p_product_cat_set_id;
373
374 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Create_Interest';
375
376 BEGIN
377
378 -- Standard Start of API savepoint
379 SAVEPOINT CREATE_INTEREST_PVT;
380
381 -- Standard call to check for call compatibility.
382 IF NOT FND_API.Compatible_API_Call( l_api_version_number,
383 p_api_version_number,
384 l_api_name,
385 G_PKG_NAME)
386 THEN
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389
390
391 -- Initialize message list if p_init_msg_list is set to TRUE.
392 IF FND_API.to_Boolean( p_init_msg_list )
393 THEN
394 FND_MSG_PUB.initialize;
395 END IF;
396
397 -- Debug Message
398 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
399 THEN
400 FND_MESSAGE.Set_Name('AS', 'Pvt Interest API: Start');
401 FND_MSG_PUB.Add;
402 END IF;
403
404 -- Initialize API return status to success
405 p_return_status := FND_API.G_RET_STS_SUCCESS;
406 l_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 --
409 -- API body
410 --
411
412 -- ******************************************************************
413 -- Validate Environment
414 -- ******************************************************************
415 IF FND_GLOBAL.User_Id IS NULL
416 THEN
417 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
418 THEN
419 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
420 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
421 FND_MSG_PUB.ADD;
422 END IF;
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425 -- ******************************************************************
426
427 IF(p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
428
429 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser
430 ( p_api_version_number => 2.0
431 ,p_salesforce_id => p_identity_salesforce_id
432 ,p_admin_group_id => p_admin_group_id
433 ,x_return_status => l_return_status
434 ,x_msg_count => l_msg_count
435 ,x_msg_data => l_msg_data
436 ,x_sales_member_rec => l_identity_sales_member_rec);
437
438 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
439 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
440 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Get_CurrentUser fail');
441 END IF;
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444 END IF;
445
446 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) and
447 l_interest_count <> 0
448 THEN
449
450 -- Insure that all required parameters exist
451 --
452 IF (p_customer_id is NULL or p_customer_id = FND_API.G_MISS_NUM)
453 THEN
454 p_return_status := FND_API.G_RET_STS_ERROR;
455
456 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
457 THEN
458 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
459 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ID', FALSE);
460 FND_MSG_PUB.ADD;
461 END IF;
462 RAISE FND_API.G_EXC_ERROR;
463
464 /* ELSIF (p_address_id is NULL or p_address_id = FND_API.G_MISS_NUM)
465 THEN
466 p_return_status := FND_API.G_RET_STS_ERROR;
467
468 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
469 THEN
470 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
471 FND_MESSAGE.Set_Token('COLUMN', 'ADDRESS_ID', FALSE);
472 FND_MSG_PUB.ADD;
473 END IF;
474 RAISE FND_API.G_EXC_ERROR; */
475
476 END IF;
477
478 -- validate customer_id
479 validate_party_id(
480 p_init_msg_list => FND_API.G_FALSE,
481 p_party_id => p_customer_id,
482 x_return_status => l_return_status,
483 x_msg_count => l_msg_count,
484 x_msg_data => l_msg_data);
485 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486 raise FND_API.G_EXC_ERROR;
487 END IF;
488
489 -- check to see if the address_id and customer_id passed are valid.
490 if (p_address_id is not NULL and p_address_id <> FND_API.G_MISS_NUM)
491 then
492 AS_TCA_PVT.VALIDATE_PARTY_SITE_ID(
493 p_init_msg_list => p_init_msg_list
494 ,p_party_id => p_customer_id
495 ,p_party_site_id => p_address_id
496 ,x_return_status => l_return_status
497 ,x_msg_count => l_msg_count
498 ,x_msg_data => l_msg_data);
499
500 if l_return_status = FND_API.G_RET_STS_ERROR then
501 raise FND_API.G_EXC_ERROR;
502 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
503 raise FND_API.G_EXC_UNEXPECTED_ERROR;
504 end if;
505 end if;
506
507 -- if the contact_id is passed check to see if it is valid.
508 if (p_contact_id is not null and p_contact_id <> FND_API.G_MISS_NUM)
509 then
510 AS_TCA_PVT.VALIDATE_CONTACT_ID(
511 p_init_msg_list => p_init_msg_list
512 ,p_party_id => p_customer_id
513 ,p_contact_id => p_contact_id
514 ,x_return_status => l_return_status
515 ,x_msg_count => l_msg_count
516 ,x_msg_data => l_msg_data);
517
518 if l_return_status = FND_API.G_RET_STS_ERROR then
519 raise FND_API.G_EXC_ERROR;
520 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
521 raise FND_API.G_EXC_UNEXPECTED_ERROR;
522 end if;
523 end if;
524
525 -- If the interest use code is not consistent with the ids that are passed in
526 -- then return an error
527 IF INVALID_USE (p_interest_use_code, p_customer_id, p_address_id,
528 p_contact_id, p_lead_id)
529 THEN
530 p_return_status := FND_API.G_RET_STS_ERROR;
531
532 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
533 THEN
534 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
535 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_USE_CODE', FALSE);
536 FND_MESSAGE.Set_Token('VALUE', p_interest_use_code, FALSE);
537 FND_MSG_PUB.ADD;
538 END IF;
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541
542 END IF;
543
544 -- check access here
545
546 IF(p_check_access_flag = 'Y') THEN
547 IF (p_lead_id is NULL or p_lead_id = FND_API.G_MISS_NUM) THEN
548 AS_ACCESS_PUB.has_updateCustomerAccess
549 ( p_api_version_number => 2.0
550 ,p_init_msg_list => p_init_msg_list
551 ,p_validation_level => p_validation_level
552 ,p_access_profile_rec => p_access_profile_rec
553 ,p_admin_flag => p_admin_flag
554 ,p_admin_group_id => p_admin_group_id
555 ,p_person_id => l_identity_sales_member_rec.employee_person_id
556 ,p_customer_id => p_customer_id
557 ,p_check_access_flag => 'Y'
558 ,p_identity_salesforce_id => p_identity_salesforce_id
559 ,p_partner_cont_party_id => NULL
560 ,x_return_status => l_return_status
561 ,x_msg_count => l_msg_count
562 ,x_msg_data => l_msg_data
563 ,x_update_access_flag => l_update_access_flag
564 );
565
566 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
567 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
568 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateCustomerAccess fail');
569 END IF;
570 RAISE FND_API.G_EXC_ERROR;
571 END IF;
572
573 IF (l_update_access_flag <> 'Y') THEN
574 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
575 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
576 FND_MSG_PUB.ADD;
577 END IF;
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580 ELSE
581 AS_ACCESS_PUB.has_updateOpportunityAccess
582 ( p_api_version_number => 2.0
583 ,p_init_msg_list => p_init_msg_list
584 ,p_validation_level => p_validation_level
585 ,p_access_profile_rec => p_access_profile_rec
586 ,p_admin_flag => p_admin_flag
587 ,p_admin_group_id => p_admin_group_id
588 ,p_person_id => l_identity_sales_member_rec.employee_person_id
589 ,p_opportunity_id => p_lead_id
590 ,p_check_access_flag => 'Y'
591 ,p_identity_salesforce_id => p_identity_salesforce_id
592 ,p_partner_cont_party_id => Null
593 ,x_return_status => p_return_status
594 ,x_msg_count => l_msg_count
595 ,x_msg_data => l_msg_data
596 ,x_update_access_flag => l_update_access_flag
597 );
598
599 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
601 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
602 END IF;
603 RAISE FND_API.G_EXC_ERROR;
604 END IF;
605
606 IF (l_update_access_flag <> 'Y') THEN
607 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
608 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
609 FND_MSG_PUB.ADD;
610 END IF;
611 p_return_status := FND_API.G_RET_STS_ERROR;
612 RAISE FND_API.G_EXC_ERROR;
613 END IF;
614 END IF;
615 END IF;
616
617 --
618 -- Loop through the pl/sql interest table, and insert the records into
619 -- AS_INTERESTS
620 --
621 FOR l_curr_row IN 1..l_interest_count
622 LOOP
623
624 ----------------- Start of Processing Interest Record -----------------------
625 BEGIN
626 -- Progress Message
627 --
628 /* IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
629 THEN
630 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
631 FND_MESSAGE.Set_Token ('ROW', 'AS_INTEREST', TRUE);
632 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
633 FND_MSG_PUB.Add;
634 END IF;
635 */
636 -- Row savepoint
637 SAVEPOINT CREATE_INTEREST_PVT_ROW;
638
639 l_return_status_tbl(l_curr_row) := FND_API.G_RET_STS_SUCCESS;
640
641 -- If the validation level is full, then validate the interest record
642 --
643 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
644 THEN
645
646 -- Debug Message
647 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
648 THEN
649 FND_MESSAGE.Set_Name('AS', 'Validating Record');
650 FND_MSG_PUB.Add;
651 END IF;
652
653 Validate_Interest ( p_interest_use_code, p_interest_tbl(l_curr_row), l_return_status );
654
655 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
656 THEN
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659
660 END IF;
661
662 OPEN duplicate_cat_cur(p_customer_id,
663 p_interest_tbl(l_curr_row).product_category_id,
664 p_interest_tbl(l_curr_row).product_cat_set_id);
665 FETCH duplicate_cat_cur INTO l_count;
666 IF (duplicate_cat_cur%FOUND)
667 THEN
668 FND_MESSAGE.Set_Name('AS', 'AS_DUPLICATE_MAPPING');
669 FND_MSG_PUB.Add;
670 Close duplicate_cat_cur;
671 RAISE FND_API.G_EXC_ERROR;
672 END IF;
673 Close duplicate_cat_cur;
674
675 -- remarked by ACNG, 07/06/2000
676 convert_miss_interest_rec(p_interest_tbl(l_curr_row), l_interest_tbl(l_curr_row));
677
678 -- Debug Message
679 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
680 THEN
681 FND_MESSAGE.Set_Name('AS', 'Inserting Record');
682 FND_MSG_PUB.Add;
683 END IF;
684
685 -- Clear values for next insert
686 l_rowid := NULL;
687 l_interest_id := NULL;
688
689 -- Insert interest row
690 AS_INTERESTS_PKG.Insert_Row ( X_Rowid => l_rowid,
691 X_Interest_Id => l_interest_tbl(l_curr_row).interest_id,
692 X_Last_Update_Date =>sysdate,
693 X_Last_Updated_By =>FND_GLOBAL.User_Id,
694 X_Creation_Date => SYSDATE,
695 X_Created_By =>FND_GLOBAL.User_Id,
696 X_Last_Update_Login =>FND_GLOBAL.Conc_Login_Id,
697 X_Request_Id => FND_GLOBAL.Conc_Request_Id,
698 X_Program_Application_Id => FND_GLOBAL.Prog_Appl_Id,
699 X_Program_Id => FND_GLOBAL.Conc_Program_Id,
700 X_Program_Update_Date => SYSDATE,
701 X_Interest_Use_Code => p_interest_use_code,
702 X_Interest_Type_Id => l_interest_tbl(l_curr_row).Interest_Type_Id,
703 X_Contact_Id => p_contact_id,
704 X_Customer_Id => p_customer_id,
705 X_Address_Id => p_address_id,
706 X_Lead_Id => p_lead_id,
707 X_Primary_Interest_Code_Id => l_interest_tbl(l_curr_row).Primary_Interest_Code_Id,
708 X_Secondary_Interest_Code_Id => l_interest_tbl(l_curr_row).Secondary_Interest_Code_Id,
709 X_Status_Code => l_interest_tbl(l_curr_row).Status_Code,
710 X_Description => l_interest_tbl(l_curr_row).description,
711 X_Attribute_Category => l_interest_tbl(l_curr_row).Attribute_Category,
712 X_Attribute1 => l_interest_tbl(l_curr_row).Attribute1,
713 X_Attribute2 => l_interest_tbl(l_curr_row).Attribute2,
714 X_Attribute3 => l_interest_tbl(l_curr_row).Attribute3,
715 X_Attribute4 => l_interest_tbl(l_curr_row).Attribute4,
716 X_Attribute5 => l_interest_tbl(l_curr_row).Attribute5,
717 X_Attribute6 => l_interest_tbl(l_curr_row).Attribute6,
718 X_Attribute7 => l_interest_tbl(l_curr_row).Attribute7,
719 X_Attribute8 => l_interest_tbl(l_curr_row).Attribute8,
720 X_Attribute9 => l_interest_tbl(l_curr_row).Attribute9,
721 X_Attribute10 => l_interest_tbl(l_curr_row).Attribute10,
722 X_Attribute11 => l_interest_tbl(l_curr_row).Attribute11,
723 X_Attribute12 => l_interest_tbl(l_curr_row).Attribute12,
724 X_Attribute13 => l_interest_tbl(l_curr_row).Attribute13,
725 X_Attribute14 => l_interest_tbl(l_curr_row).Attribute14,
726 X_Attribute15 => l_interest_tbl(l_curr_row).Attribute15,
727 X_Product_Category_Id => l_interest_tbl(l_curr_row).Product_Category_Id,
728 X_Product_Cat_Set_Id => l_interest_tbl(l_curr_row).Product_Cat_Set_Id
729 );
730
731 p_interest_out_tbl(l_curr_row).interest_id := l_interest_tbl(l_curr_row).interest_id;
732 l_interests_inserted := l_interests_inserted + 1;
733
734 -- Handle exceptions within the loop, so that other rows will be processed if possible
735 --
736 EXCEPTION
737
738 WHEN FND_API.G_EXC_ERROR THEN
739
740 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
741 P_MODULE => l_module
742 ,P_API_NAME => L_API_NAME
743 ,P_PKG_NAME => G_PKG_NAME
744 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
745 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
746 ,X_MSG_COUNT => P_MSG_COUNT
747 ,X_MSG_DATA => P_MSG_DATA
748 ,X_RETURN_STATUS => l_RETURN_STATUS);
749
750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
751
752 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
753 P_MODULE => l_module
754 ,P_API_NAME => L_API_NAME
755 ,P_PKG_NAME => G_PKG_NAME
756 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
757 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
758 ,X_MSG_COUNT => P_MSG_COUNT
759 ,X_MSG_DATA => P_MSG_DATA
760 ,X_RETURN_STATUS => l_RETURN_STATUS);
761
762 WHEN OTHERS THEN
763
764 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
765 P_MODULE => l_module
766 ,P_API_NAME => L_API_NAME
767 ,P_PKG_NAME => G_PKG_NAME
768 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
769 ,P_SQLCODE => SQLCODE
770 ,P_SQLERRM => SQLERRM
771 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
772 ,X_MSG_COUNT => P_MSG_COUNT
773 ,X_MSG_DATA => P_MSG_DATA
774 ,X_RETURN_STATUS => l_RETURN_STATUS);
775
776 END;
777 ---------------- End of Processing Interest Record -----------------------
778
779 END LOOP;
780
781 -- Fix bug 2304022
782 IF (p_lead_id is not NULL AND p_lead_id <> FND_API.G_MISS_NUM) THEN
783 IF l_debug THEN
784 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
785 'Calling Opportunity Real Time API ');
786 END IF;
787 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
788 P_Api_Version_Number => 1.0,
789 P_Init_Msg_List => FND_API.G_FALSE,
790 P_Commit => FND_API.G_FALSE,
791 p_lead_id => p_LEAD_ID,
792 X_Return_Status => l_return_status,
793 X_Msg_Count => l_msg_count,
794 X_Msg_Data => l_msg_data
795 );
796
797 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
798 IF l_debug THEN
799 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
800 'Opportunity Real Time API fail');
801 END IF;
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804 END IF;
805
806
807 -- Calculate the return status
808 FOR l_curr_row IN 1..l_interest_count
809 LOOP
810 IF l_return_status_tbl(l_curr_row) = FND_API.G_RET_STS_ERROR
811 THEN
812 p_return_status := FND_API.G_RET_STS_ERROR;
813 l_return_status := FND_API.G_RET_STS_ERROR;
814
815 EXIT;
816 END IF;
817 IF l_return_status_tbl(l_curr_row) = FND_API.G_RET_STS_UNEXP_ERROR
818 THEN
819 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
822 EXIT;
823 END IF;
824 END LOOP;
825
826 --
827 -- End of API body.
828 --
829
830 -- Success Message
831 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
832 l_interests_inserted > 0
833 THEN
834 FND_MESSAGE.Set_Name('AS', 'API_ROWS_INSERTED');
835 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
836 FND_MESSAGE.Set_Token('NUMBER', to_char(l_interests_inserted), FALSE);
837 FND_MSG_PUB.Add;
838 END IF;
839
840
841 -- Success Message
842 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
843 l_return_status = FND_API.G_RET_STS_SUCCESS
844 THEN
845 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
846 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
847 FND_MSG_PUB.Add;
848 END IF;
849
850 -- Standard check of p_commit.
851 IF FND_API.To_Boolean ( p_commit )
852 THEN
853 COMMIT WORK;
854 END IF;
855
856 -- Debug Message
857 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
858 THEN
859 FND_MESSAGE.Set_Name('AS', 'Pvt Interest API: End');
860 FND_MSG_PUB.Add;
861 END IF;
862
863 -- Standard call to get message count and if count is 1, get message info.
864 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
865 p_data => p_msg_data
866 );
867
868 --
869 -- Normal API Exception handling, if exception occurs outside of interest processing loop
870 --
871 EXCEPTION
872
873 WHEN FND_API.G_EXC_ERROR THEN
874
875 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
876 P_MODULE => l_module
877 ,P_API_NAME => L_API_NAME
878 ,P_PKG_NAME => G_PKG_NAME
879 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
880 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
881 ,X_MSG_COUNT => P_MSG_COUNT
882 ,X_MSG_DATA => P_MSG_DATA
883 ,X_RETURN_STATUS => l_RETURN_STATUS);
884
885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
886
887 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
888 P_MODULE => l_module
889 ,P_API_NAME => L_API_NAME
890 ,P_PKG_NAME => G_PKG_NAME
891 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
892 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
893 ,X_MSG_COUNT => P_MSG_COUNT
894 ,X_MSG_DATA => P_MSG_DATA
895 ,X_RETURN_STATUS => l_RETURN_STATUS);
896
897 WHEN OTHERS THEN
898
899 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
900 P_MODULE => l_module
901 ,P_API_NAME => L_API_NAME
902 ,P_PKG_NAME => G_PKG_NAME
903 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
904 ,P_SQLCODE => SQLCODE
905 ,P_SQLERRM => SQLERRM
906 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
907 ,X_MSG_COUNT => P_MSG_COUNT
908 ,X_MSG_DATA => P_MSG_DATA
909 ,X_RETURN_STATUS => l_RETURN_STATUS);
910
911 END Create_Interest;
912
913 -- Start of Comments
914 --
915 -- API name : Update Interest
916 -- Type : Private
917 -- Function : Update Account, Contact, or Lead Classification Interest
918 -- Pre-reqs : Account, contact, or lead exists
919 -- Parameters
920 -- IN :
921 -- p_api_version_number IN NUMBER Required
922 -- p_init_msg_list IN VARCHAR2 Optional
923 -- Default = FND_API.G_FALSE
924 -- p_commit IN VARCHAR2 Optional
925 -- Default = FND_API.G_FALSE
926 -- p_validation_level IN NUMBER Optional
927 -- Default = FND_API.G_VALID_LEVEL_FULL
928 -- p_interest_rec IN INTEREST_REC_TYPE Optional
929 -- p_interest_use_code IN VARCHAR2 Required
930 -- (LEAD_CLASSIFICATION, COMPANY_CLASSIFICATION,
931 -- CONTACT_INTEREST)
932 --
933 -- OUT :
934 -- x_return_status OUT VARCHAR2(1)
935 -- x_msg_count OUT NUMBER
936 -- x_msg_data OUT VARCHAR2(2000)
937 -- x_interest_id OUT NUMBER
938 --
939 --
940 -- Version : Current version 2.0
941 -- Initial Version
942 -- Initial version 2.0
943 --
944 -- Notes:
945 -- Validation proceeds as follows:
946 -- For lead classification: lead_id, customer_id,
947 -- address_id must exist
948 -- For contact interest: contact_id, customer_id,
949 -- address_id must exists
950 -- For account interest: customer_id, address_id must exists
951 -- For each interest, the interest type must be denoted properly
952 -- (i.e. for updating lead classifications, the interest
953 -- type must be denoted as a lead classification interest)
954 --
955 --
956 -- End of Comments
957 PROCEDURE Update_Interest
958 ( p_api_version_number IN NUMBER,
959 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
960 p_commit IN VARCHAR2 := FND_API.G_FALSE,
961 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
962 p_identity_salesforce_id IN NUMBER,
963 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
964 p_interest_use_code IN VARCHAR2,
965 p_check_access_flag IN VARCHAR2,
966 p_admin_flag IN VARCHAR2,
967 p_admin_group_id IN NUMBER,
968 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
969 x_return_status OUT NOCOPY VARCHAR2,
970 x_msg_count OUT NOCOPY NUMBER,
971 x_msg_data OUT NOCOPY VARCHAR2,
972 x_interest_id OUT NOCOPY NUMBER
973 ) is
974
975 l_api_name CONSTANT VARCHAR2(30) := 'Update_Interest';
976 l_api_version_number CONSTANT NUMBER := 2.0;
977 l_return_status VARCHAR2(1);
978 l_msg_count NUMBER;
979 l_msg_data VARCHAR2(2000);
980 l_rowid ROWID;
981 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
982 l_last_update_date DATE;
983 l_update_access_flag VARCHAR2(1);
984 l_count NUMBER := 0;
985
986 x_lead_id NUMBER;
987
988 cursor get_interest_info_csr is
989 SELECT rowid, last_update_date
990 FROM as_interests
991 WHERE interest_id = p_interest_rec.interest_id
992 FOR UPDATE of interest_Id NOWAIT;
993
994 CURSOR duplicate_cat_cur(p_customer_id IN NUMBER,
995 p_interest_id IN NUMBER,
996 p_product_category_id IN NUMBER,
997 p_product_cat_set_id IN NUMBER) IS
998 select 1
999 from AS_INTERESTS_ALL
1000 where customer_id = p_customer_id
1001 and interest_use_code = 'CONTACT_INTEREST'
1002 and product_category_id = p_product_category_id
1003 and product_cat_set_id = p_product_cat_set_id
1004 and interest_id <> p_interest_id;
1005
1006 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1007 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Update_Interest';
1008 BEGIN
1009
1010 -- Standard Start of API savepoint
1011 SAVEPOINT UPDATE_INTEREST_PVT;
1012
1013 -- Standard call to check for call compatibility.
1014 IF NOT FND_API.Compatible_API_Call( l_api_version_number,
1015 p_api_version_number,
1016 l_api_name,
1017 G_PKG_NAME)
1018 THEN
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020 END IF;
1021
1022
1023 -- Initialize message list if p_init_msg_list is set to TRUE.
1024 IF FND_API.to_Boolean( p_init_msg_list )
1025 THEN
1026 FND_MSG_PUB.initialize;
1027 END IF;
1028
1029 -- Debug Message
1030 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1031 THEN
1032 FND_MESSAGE.Set_Name('AS', 'Pvt Interest API: Start');
1033 FND_MSG_PUB.Add;
1034 END IF;
1035
1036 -- Initialize API return status to success
1037 x_return_status := FND_API.G_RET_STS_SUCCESS;
1038 l_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 --
1041 -- API body
1042 --
1043
1044 -- ******************************************************************
1045 -- Validate Environment
1046 -- ******************************************************************
1047
1048 IF(p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
1049
1050 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1051 p_api_version_number => 2.0
1052 ,p_salesforce_id => p_identity_salesforce_id
1053 ,p_admin_group_id => p_admin_group_id
1054 ,x_return_status => l_return_status
1055 ,x_msg_count => x_msg_count
1056 ,x_msg_data => x_msg_data
1057 ,x_sales_member_rec => l_identity_sales_member_rec);
1058
1059 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1060 IF l_debug THEN
1061 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Get_CurrentUser fail');
1062 END IF;
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065 END IF;
1066
1067 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
1068 THEN
1069
1070 -- Insure that all required parameters exist
1071 --
1072 IF (p_interest_rec.customer_id is NULL or p_interest_rec.customer_id = FND_API.G_MISS_NUM) THEN
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074
1075 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1076 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1077 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ID', FALSE);
1078 FND_MSG_PUB.ADD;
1079 END IF;
1080 RAISE FND_API.G_EXC_ERROR;
1081 END IF;
1082
1083 -- check to see if the address_id and customer_id passed are valid.
1084 if p_interest_rec.address_id is not NULL and p_interest_rec.address_id <> FND_API.G_MISS_NUM
1085 then
1086 AS_TCA_PVT.VALIDATE_PARTY_SITE_ID(
1087 p_init_msg_list => p_init_msg_list
1088 ,p_party_id => p_interest_rec.customer_id
1089 ,p_party_site_id => p_interest_rec.address_id
1090 ,x_return_status => l_return_status
1091 ,x_msg_count => l_msg_count
1092 ,x_msg_data => l_msg_data);
1093
1094 if l_return_status = FND_API.G_RET_STS_ERROR then
1095 raise FND_API.G_EXC_ERROR;
1096 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1097 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1098 end if;
1099 end if;
1100
1101 -- if the contact_id is passed check to see if it is valid.
1102 if p_interest_rec.contact_id is not null and p_interest_rec.contact_id <> FND_API.G_MISS_NUM
1103 then
1104 AS_TCA_PVT.VALIDATE_CONTACT_ID(
1105 p_init_msg_list => p_init_msg_list
1106 ,p_party_id => p_interest_rec.customer_id
1107 ,p_contact_id => p_interest_rec.contact_id
1108 ,x_return_status => l_return_status
1109 ,x_msg_count => l_msg_count
1110 ,x_msg_data => l_msg_data);
1111
1112 if l_return_status = FND_API.G_RET_STS_ERROR then
1113 raise FND_API.G_EXC_ERROR;
1114 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1115 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1116 end if;
1117 end if;
1118
1119 -- If the interest use code is not consistent with the ids that are passed in
1120 -- then return an error
1121 IF INVALID_USE (p_interest_use_code, p_interest_rec.customer_id, p_interest_rec.address_id,
1122 p_interest_rec.contact_id, p_interest_rec.lead_id)
1123 THEN
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125
1126 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1127 THEN
1128 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1129 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_USE_CODE', FALSE);
1130 FND_MESSAGE.Set_Token('VALUE', p_interest_use_code, FALSE);
1131 FND_MSG_PUB.ADD;
1132 END IF;
1133 RAISE FND_API.G_EXC_ERROR;
1134 END IF;
1135
1136 END IF;
1137
1138 -- check access here
1139
1140 IF(p_check_access_flag = 'Y') THEN
1141 IF (p_interest_rec.lead_id is NULL or p_interest_rec.lead_id = FND_API.G_MISS_NUM) THEN
1142 AS_ACCESS_PUB.has_updateCustomerAccess
1143 ( p_api_version_number => 2.0
1144 ,p_init_msg_list => p_init_msg_list
1145 ,p_validation_level => p_validation_level
1146 ,p_access_profile_rec => p_access_profile_rec
1147 ,p_admin_flag => p_admin_flag
1148 ,p_admin_group_id => p_admin_group_id
1149 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1150 ,p_customer_id => p_interest_rec.customer_id
1151 ,p_check_access_flag => 'Y'
1152 ,p_identity_salesforce_id => p_identity_salesforce_id
1153 ,p_partner_cont_party_id => NULL
1154 ,x_return_status => l_return_status
1155 ,x_msg_count => l_msg_count
1156 ,x_msg_data => l_msg_data
1157 ,x_update_access_flag => l_update_access_flag
1158 );
1159
1160 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1161 IF l_debug THEN
1162 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateCustomerAccess fail');
1163 END IF;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166
1167 IF (l_update_access_flag <> 'Y') THEN
1168 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1169 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1170 FND_MSG_PUB.ADD;
1171 END IF;
1172 RAISE FND_API.G_EXC_ERROR;
1173 END IF;
1174 ELSE
1175 AS_ACCESS_PUB.has_updateOpportunityAccess
1176 ( p_api_version_number => 2.0
1177 ,p_init_msg_list => p_init_msg_list
1178 ,p_validation_level => p_validation_level
1179 ,p_access_profile_rec => p_access_profile_rec
1180 ,p_admin_flag => p_admin_flag
1181 ,p_admin_group_id => p_admin_group_id
1182 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1183 ,p_opportunity_id => p_interest_rec.lead_id
1184 ,p_check_access_flag => 'Y'
1185 ,p_identity_salesforce_id => p_identity_salesforce_id
1186 ,p_partner_cont_party_id => Null
1187 ,x_return_status => l_return_status
1188 ,x_msg_count => l_msg_count
1189 ,x_msg_data => l_msg_data
1190 ,x_update_access_flag => l_update_access_flag
1191 );
1192
1193 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1194 IF l_debug THEN
1195 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
1196 END IF;
1197 RAISE FND_API.G_EXC_ERROR;
1198 END IF;
1199
1200 IF (l_update_access_flag <> 'Y') THEN
1201 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1202 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1203 FND_MSG_PUB.ADD;
1204 END IF;
1205 RAISE FND_API.G_EXC_ERROR;
1206 END IF;
1207 END IF;
1208 END IF;
1209
1210 -- If the validation level is full, then validate the interest record
1211 --
1212 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
1213 THEN
1214
1215 -- Debug Message
1216 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1217 THEN
1218 FND_MESSAGE.Set_Name('AS', 'Validating Record');
1219 FND_MSG_PUB.Add;
1220 END IF;
1221
1222 Validate_Interest ( p_interest_use_code, p_interest_rec, l_return_status );
1223
1224 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1225 THEN
1226 RAISE FND_API.G_EXC_ERROR;
1227 END IF;
1228
1229 END IF;
1230
1231 OPEN duplicate_cat_cur(p_interest_rec.customer_id,
1232 p_interest_rec.interest_id,
1233 p_interest_rec.product_category_id,
1234 p_interest_rec.product_cat_set_id);
1235 FETCH duplicate_cat_cur INTO l_count;
1236 IF (duplicate_cat_cur%FOUND)
1237 THEN
1238 FND_MESSAGE.Set_Name('AS', 'AS_DUPLICATE_MAPPING');
1239 FND_MSG_PUB.Add;
1240 Close duplicate_cat_cur;
1241 RAISE FND_API.G_EXC_ERROR;
1242 END IF;
1243 Close duplicate_cat_cur;
1244
1245 -- Debug Message
1246 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1247 THEN
1248 FND_MESSAGE.Set_Name('AS', 'Updating Record');
1249 FND_MSG_PUB.Add;
1250 END IF;
1251
1252 -- lock rows before update
1253
1254 open get_interest_info_csr;
1255 fetch get_interest_info_csr into l_rowid, l_last_update_date;
1256 close get_interest_info_csr;
1257
1258 if (p_interest_rec.last_update_date is NULL
1259 or p_interest_rec.last_update_date = FND_API.G_MISS_DATE)
1260 then
1261 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1262 THEN
1263 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1264 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1265 FND_MSG_PUB.ADD;
1266 END IF;
1267 RAISE FND_API.G_EXC_ERROR;
1268 end if;
1269
1270 if (l_last_update_date <> p_interest_rec.last_update_date)
1271 then
1272 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1273 THEN
1274 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
1275 FND_MESSAGE.Set_Token('INFO', 'AS_INTERESTS', FALSE);
1276 FND_MSG_PUB.ADD;
1277 END IF;
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 else
1280
1281 AS_INTERESTS_PKG.Update_Row ( X_Rowid => l_rowid,
1282 X_Interest_Id => p_interest_rec.interest_id,
1283 X_Last_Update_Date => SYSDATE,
1284 X_Last_Updated_By => FND_GLOBAL.USER_ID,
1285 X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
1286 X_Request_Id => FND_GLOBAL.Conc_Request_Id,
1287 X_Program_Application_Id => FND_GLOBAL.Prog_Appl_Id,
1288 X_Program_Id => FND_GLOBAL.Conc_Program_Id,
1289 X_Program_Update_Date => SYSDATE,
1290 X_Interest_Use_Code => p_interest_use_code,
1291 X_Interest_Type_Id => p_interest_rec.Interest_Type_Id,
1292 X_Contact_Id => p_interest_rec.contact_id,
1293 X_Customer_Id => p_interest_rec.customer_id,
1294 X_Address_Id => p_interest_rec.address_id,
1295 X_Lead_Id => p_interest_rec.lead_id,
1296 X_Primary_Interest_Code_Id => p_interest_rec.Primary_Interest_Code_Id,
1297 X_Secondary_Interest_Code_Id => p_interest_rec.Secondary_Interest_Code_Id,
1298 X_Status_Code => p_interest_rec.Status_Code,
1299 X_Description => p_interest_rec.description,
1300 X_Attribute_Category => p_interest_rec.Attribute_Category,
1301 X_Attribute1 => p_interest_rec.Attribute1,
1302 X_Attribute2 => p_interest_rec.Attribute2,
1303 X_Attribute3 => p_interest_rec.Attribute3,
1304 X_Attribute4 => p_interest_rec.Attribute4,
1305 X_Attribute5 => p_interest_rec.Attribute5,
1306 X_Attribute6 => p_interest_rec.Attribute6,
1307 X_Attribute7 => p_interest_rec.Attribute7,
1308 X_Attribute8 => p_interest_rec.Attribute8,
1309 X_Attribute9 => p_interest_rec.Attribute9,
1310 X_Attribute10 => p_interest_rec.Attribute10,
1311 X_Attribute11 => p_interest_rec.Attribute11,
1312 X_Attribute12 => p_interest_rec.Attribute12,
1313 X_Attribute13 => p_interest_rec.Attribute13,
1314 X_Attribute14 => p_interest_rec.Attribute14,
1315 X_Attribute15 => p_interest_rec.Attribute15,
1316 X_Product_Category_Id => p_interest_rec.Product_Category_Id,
1317 X_Product_Cat_Set_Id => p_interest_rec.Product_Cat_Set_Id
1318 );
1319 end if;
1320 x_interest_id := p_interest_rec.interest_id;
1321
1322 -- Fix bug 2304022
1323 IF (p_interest_rec.lead_id is not NULL AND
1324 p_interest_rec.lead_id <> FND_API.G_MISS_NUM) THEN
1325 IF l_debug THEN
1326 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1327 'Calling Opportunity Real Time API');
1328 END IF;
1329 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
1330 P_Api_Version_Number => 1.0,
1331 P_Init_Msg_List => FND_API.G_FALSE,
1332 P_Commit => FND_API.G_FALSE,
1333 p_lead_id => p_interest_rec.lead_id,
1334 X_Return_Status => l_return_status,
1335 X_Msg_Count => l_msg_count,
1336 X_Msg_Data => l_msg_data
1337 );
1338
1339 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1340 IF l_debug THEN
1341 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1342 'Opportunity Real Time API fail');
1343 END IF;
1344 RAISE FND_API.G_EXC_ERROR;
1345
1346 END IF;
1347 END IF;
1348
1349
1350 -- Success Message
1351 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
1352 l_return_status = FND_API.G_RET_STS_SUCCESS
1353 THEN
1354 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
1355 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
1356 FND_MSG_PUB.Add;
1357 END IF;
1358
1359 -- Standard check of p_commit.
1360 IF FND_API.To_Boolean ( p_commit )
1361 THEN
1362 COMMIT WORK;
1363 END IF;
1364
1365 -- Debug Message
1366 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1367 THEN
1368 FND_MESSAGE.Set_Name('AS', 'Pvt Interest API: End');
1369 FND_MSG_PUB.Add;
1370 END IF;
1371
1372 -- Standard call to get message count and if count is 1, get message info.
1373 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1374 p_data => x_msg_data
1375 );
1376
1377 EXCEPTION
1378
1379 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1380 ROLLBACK TO UPDATE_INTEREST_PVT;
1381 x_return_status := FND_API.G_RET_STS_ERROR ;
1382
1383 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1384 THEN
1385 FND_MESSAGE.Set_Name('AS', 'API_CANNOT_RESERVE_RECORD');
1386 FND_MESSAGE.Set_Token('INFO', 'UPDATE_INTEREST', FALSE);
1387 FND_MSG_PUB.Add;
1388 END IF;
1389
1390 WHEN FND_API.G_EXC_ERROR THEN
1391 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1392 P_MODULE => l_module
1393 ,P_API_NAME => L_API_NAME
1394 ,P_PKG_NAME => G_PKG_NAME
1395 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1396 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1397 ,X_MSG_COUNT => X_MSG_COUNT
1398 ,X_MSG_DATA => X_MSG_DATA
1399 ,X_RETURN_STATUS => X_RETURN_STATUS);
1400
1401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1402 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1403 P_MODULE => l_module
1404 ,P_API_NAME => L_API_NAME
1405 ,P_PKG_NAME => G_PKG_NAME
1406 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1407 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1408 ,X_MSG_COUNT => X_MSG_COUNT
1409 ,X_MSG_DATA => X_MSG_DATA
1410 ,X_RETURN_STATUS => X_RETURN_STATUS);
1411
1412 WHEN OTHERS THEN
1413 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1414 P_MODULE => l_module
1415 ,P_API_NAME => L_API_NAME
1416 ,P_PKG_NAME => G_PKG_NAME
1417 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1418 ,P_SQLCODE => SQLCODE
1419 ,P_SQLERRM => SQLERRM
1420 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1421 ,X_MSG_COUNT => X_MSG_COUNT
1422 ,X_MSG_DATA => X_MSG_DATA
1423 ,X_RETURN_STATUS => X_RETURN_STATUS);
1424
1425 END Update_Interest;
1426
1427 PROCEDURE Delete_Interest
1428 ( p_api_version_number IN NUMBER,
1429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1430 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1431 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1432 p_identity_salesforce_id IN NUMBER,
1433 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
1434 p_interest_use_code IN VARCHAR2,
1435 p_check_access_flag in varchar2,
1436 p_admin_flag in varchar2,
1437 p_admin_group_id in number,
1438 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
1439 x_return_status OUT NOCOPY VARCHAR2,
1440 x_msg_count OUT NOCOPY NUMBER,
1441 x_msg_data OUT NOCOPY VARCHAR2
1442 ) is
1443 cursor get_interest_info_csr(p_interest_id NUMBER) is
1444 select 1
1445 from as_interests_all
1446 where interest_id = p_interest_id;
1447
1448 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Interest';
1449 l_api_version_number CONSTANT NUMBER := 2.0;
1450 l_return_status VARCHAR2(1);
1451 l_member_access VARCHAR2(1);
1452 l_member_role VARCHAR2(1);
1453 l_val NUMBER;
1454 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1455 l_update_access_flag varchar2(1);
1456 x_lead_id NUMBER;
1457 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1458 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Delete_Interest';
1459 begin
1460 -- Standard Start of API savepoint
1461 SAVEPOINT DELETE_INTEREST_PVT;
1462
1463 -- Standard call to check for call compatibility.
1464 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1465 p_api_version_number,
1466 l_api_name,
1467 G_PKG_NAME)
1468 THEN
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END IF;
1471
1472
1473 -- Initialize message list if p_init_msg_list is set to TRUE.
1474 IF FND_API.to_Boolean( p_init_msg_list )
1475 THEN
1476 FND_MSG_PUB.initialize;
1477 END IF;
1478
1479 -- Initialize API return status to success
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481 l_return_status := FND_API.G_RET_STS_SUCCESS;
1482 --
1483 -- API body
1484 --
1485
1486 -- ******************************************************************
1487 -- Validate Environment
1488 -- ******************************************************************
1489 IF FND_GLOBAL.User_Id IS NULL
1490 THEN
1491 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1492 THEN
1493 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
1494 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1495 FND_MSG_PUB.ADD;
1496 END IF;
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499
1500 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
1501 then
1502
1503 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1504 p_api_version_number => 2.0
1505 ,p_salesforce_id => p_identity_salesforce_id
1506 , p_admin_group_id => p_admin_group_id
1507 ,x_return_status => l_return_status
1508 ,x_msg_count => x_msg_count
1509 ,x_msg_data => x_msg_data
1510 ,x_sales_member_rec => l_identity_sales_member_rec);
1511
1512 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1513 RAISE FND_API.G_EXC_ERROR;
1514 END IF;
1515 end if;
1516
1517
1518 -- ******************************************************************
1519
1520 if (p_interest_rec.interest_id is NULL)
1521 then
1522 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1523 THEN
1524 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1525 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_ID', FALSE);
1526 FND_MSG_PUB.ADD;
1527 END IF;
1528 end if;
1529
1530 open get_interest_info_csr(p_interest_rec.interest_id);
1531 fetch get_interest_info_csr into l_val;
1532
1533 if (get_interest_info_csr%NOTFOUND)
1534 then
1535 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1536 THEN
1537 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1538 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_ID', FALSE);
1539 fnd_message.set_token('VALUE', p_interest_rec.interest_id, FALSE);
1540 FND_MSG_PUB.ADD;
1541 END IF;
1542 close get_interest_info_csr;
1543 raise FND_API.G_EXC_ERROR;
1544 End if;
1545 if p_check_access_flag = 'Y'
1546 then
1547 IF p_interest_rec.lead_id is NULL or p_interest_rec.lead_id = FND_API.G_MISS_NUM
1548 THEN
1549 AS_ACCESS_PUB.has_updateCustomerAccess
1550 ( p_api_version_number => 2.0
1551 ,p_init_msg_list => p_init_msg_list
1552 ,p_validation_level => p_validation_level
1553 ,p_access_profile_rec => p_access_profile_rec
1554 ,p_admin_flag => p_admin_flag
1555 ,p_admin_group_id => p_admin_group_id
1556 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1557 ,p_customer_id => p_interest_rec.customer_id
1558 ,p_check_access_flag => 'Y'
1559 ,p_identity_salesforce_id => p_identity_salesforce_id
1560 ,p_partner_cont_party_id => NULL
1561 ,x_return_status => l_return_status
1562 ,x_msg_count => x_msg_count
1563 ,x_msg_data => x_msg_data
1564 ,x_update_access_flag => l_update_access_flag
1565 );
1566
1567 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1569 END IF;
1570
1571 IF (l_update_access_flag <> 'Y') THEN
1572 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1573 FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
1574 FND_MSG_PUB.ADD;
1575 END IF;
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578 ELSE
1579 AS_ACCESS_PUB.has_updateOpportunityAccess
1580 ( p_api_version_number => 2.0
1581 ,p_init_msg_list => p_init_msg_list
1582 ,p_validation_level => p_validation_level
1583 ,p_access_profile_rec => p_access_profile_rec
1584 ,p_admin_flag => p_admin_flag
1585 ,p_admin_group_id => p_admin_group_id
1586 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1587 ,p_opportunity_id => p_interest_rec.lead_id
1588 ,p_check_access_flag => 'Y'
1589 ,p_identity_salesforce_id => p_identity_salesforce_id
1590 ,p_partner_cont_party_id => Null
1591 ,x_return_status => l_return_status
1592 ,x_msg_count => x_msg_count
1593 ,x_msg_data => x_msg_data
1594 ,x_update_access_flag => l_update_access_flag
1595 );
1596
1597 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1598 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1599 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
1600 END IF;
1601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1602 END IF;
1603
1604 IF (l_update_access_flag <> 'Y') THEN
1605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1606 FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
1607 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
1608 FND_MSG_PUB.ADD;
1609 END IF;
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612 END IF;
1613 end if; -- p_check_access_flag = 'Y'
1614
1615 delete from as_interests_all
1616 where interest_id = p_interest_rec.interest_id;
1617
1618 -- Fix bug 2304022
1619 IF (p_interest_rec.lead_id is not NULL AND
1620 p_interest_rec.lead_id <> FND_API.G_MISS_NUM) THEN
1621 IF l_debug THEN
1622 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1623 'Calling Opportunity Real Time API');
1624 END IF;
1625 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
1626 P_Api_Version_Number => 1.0,
1627 P_Init_Msg_List => FND_API.G_FALSE,
1628 P_Commit => FND_API.G_FALSE,
1629 p_lead_id => p_interest_rec.lead_id,
1630 X_Return_Status => l_return_status,
1631 X_Msg_Count => x_msg_count,
1632 X_Msg_Data => x_msg_data
1633 );
1634
1635 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1636 IF l_debug THEN
1637 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1638 'Opportunity Real Time API fail');
1639 END IF;
1640
1641 RAISE FND_API.G_EXC_ERROR;
1642 END IF;
1643 END IF;
1644
1645 x_return_status := l_return_status;
1646
1647 --
1648 -- End of API body.
1649 --
1650
1651 -- Standard check of p_commit.
1652 IF FND_API.To_Boolean ( p_commit )
1653 THEN
1654 COMMIT WORK;
1655 END IF;
1656
1657 -- Standard call to get message count and if count is 1, get message info.
1658 FND_MSG_PUB.Count_And_Get
1659 ( p_count => x_msg_count,
1660 p_data => x_msg_data
1661 );
1662
1663 EXCEPTION
1664 WHEN FND_API.G_EXC_ERROR THEN
1665 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1666 P_MODULE => l_module
1667 ,P_API_NAME => L_API_NAME
1668 ,P_PKG_NAME => G_PKG_NAME
1669 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1670 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1671 ,X_MSG_COUNT => X_MSG_COUNT
1672 ,X_MSG_DATA => X_MSG_DATA
1673 ,X_RETURN_STATUS => X_RETURN_STATUS);
1674
1675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1676 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1677 P_MODULE => l_module
1678 ,P_API_NAME => L_API_NAME
1679 ,P_PKG_NAME => G_PKG_NAME
1680 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1681 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1682 ,X_MSG_COUNT => X_MSG_COUNT
1683 ,X_MSG_DATA => X_MSG_DATA
1684 ,X_RETURN_STATUS => X_RETURN_STATUS);
1685
1686 WHEN OTHERS THEN
1687 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1688 P_MODULE => l_module
1689 ,P_API_NAME => L_API_NAME
1690 ,P_PKG_NAME => G_PKG_NAME
1691 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1692 ,P_SQLCODE => SQLCODE
1693 ,P_SQLERRM => SQLERRM
1694 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1695 ,X_MSG_COUNT => X_MSG_COUNT
1696 ,X_MSG_DATA => X_MSG_DATA
1697 ,X_RETURN_STATUS => X_RETURN_STATUS);
1698
1699
1700 end Delete_Interest;
1701
1702 PROCEDURE Validate_Product_Category ( p_interest_id IN NUMBER,
1703 p_product_category_id IN NUMBER,
1704 p_product_cat_set_id IN NUMBER,
1705 p_interest_status_code IN VARCHAR2,
1706 p_return_status OUT NOCOPY VARCHAR2
1707 )
1708 IS
1709
1710 CURSOR C_GET_OLD_PROD_CAT_INFO(l_interest_id NUMBER) IS
1711 SELECT PRODUCT_CATEGORY_ID, PRODUCT_CAT_SET_ID
1712 FROM AS_INTERESTS_ALL
1713 WHERE INTEREST_ID = l_interest_id;
1714
1715 l_return_status VARCHAR2(1);
1716 l_interest_fields_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1717 l_old_product_category_id NUMBER;
1718 l_old_product_cat_set_id NUMBER;
1719 l_validation_level VARCHAR2(1) := 'L';
1720 BEGIN
1721 l_return_status := FND_API.G_RET_STS_SUCCESS;
1722
1723 -- Interest Validation
1724 --
1725 IF ((p_product_category_id is NULL)
1726 or (p_product_category_id = FND_API.G_MISS_NUM))
1727 THEN
1728 l_return_status := FND_API.G_RET_STS_ERROR;
1729
1730 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1731 THEN
1732 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1733 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CATEGORY_ID', FALSE);
1734 FND_MSG_PUB.ADD;
1735 END IF;
1736 ELSIF ((p_product_cat_set_id is NULL)
1737 or (p_product_cat_set_id = FND_API.G_MISS_NUM))
1738 THEN
1739 l_return_status := FND_API.G_RET_STS_ERROR;
1740
1741 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1742 THEN
1743 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1744 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CAT_SET_ID', FALSE);
1745 FND_MSG_PUB.ADD;
1746 END IF;
1747 ELSE
1748 -- Insure that all ids are valid
1749 --
1750 OPEN C_GET_OLD_PROD_CAT_INFO ( p_interest_id );
1751 Fetch C_GET_OLD_PROD_CAT_INFO INTO l_old_product_category_id, l_old_product_cat_set_id;
1752
1753 IF ((l_old_product_category_id is NOT NULL) and
1754 (l_old_product_cat_set_id is NOT NULL) and
1755 (l_old_product_category_id = p_product_category_id) and
1756 (l_old_product_cat_set_id = p_product_cat_set_id))
1757 THEN
1758 l_validation_level := 'L';
1759 ELSE
1760 l_validation_level := 'H';
1761 END IF;
1762
1763 AS_OPP_LINE_PVT.Validate_Prod_Cat_Fields ( p_product_category_id => p_product_category_id,
1764 p_product_cat_set_id => p_product_cat_set_id,
1765 p_validation_level => l_validation_level,
1766 x_return_status => l_interest_fields_status
1767 );
1768
1769 IF l_interest_fields_status <> FND_API.G_RET_STS_SUCCESS
1770 THEN
1771 l_return_status := FND_API.G_RET_STS_ERROR;
1772 END IF;
1773 END IF;
1774
1775 -- Now validate interest status
1776 --
1777 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
1778 THEN
1779 Validate_Int_Status_For_PC(p_product_category_id => p_product_category_id,
1780 p_product_cat_set_id => p_product_cat_set_id,
1781 p_interest_status_code => p_interest_status_code,
1782 p_return_status => l_return_status);
1783 END IF;
1784
1785 p_return_status := l_return_status;
1786
1787 END Validate_Product_Category;
1788
1789
1790 PROCEDURE Validate_Interest_Type ( p_interest_type_id IN NUMBER,
1791 p_primary_interest_code_id IN NUMBER,
1792 p_secondary_interest_code_id IN NUMBER,
1793 p_interest_status_code IN VARCHAR2,
1794 p_return_status OUT NOCOPY VARCHAR2
1795 )
1796 IS
1797 l_return_status VARCHAR2(1);
1798 l_interest_fields_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1799 BEGIN
1800 l_return_status := FND_API.G_RET_STS_SUCCESS;
1801
1802 -- Interest Validation
1803 --
1804 IF ((p_interest_type_id is NULL)
1805 or (p_interest_type_id = FND_API.G_MISS_NUM))
1806 THEN
1807 l_return_status := FND_API.G_RET_STS_ERROR;
1808
1809 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1810 THEN
1811 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1812 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_TYPE_ID', FALSE);
1813 FND_MSG_PUB.ADD;
1814 END IF;
1815
1816 ELSE
1817 -- Insure that all ids are valid
1818 --
1819 Validate_Int_Type_Fields( p_interest_type_id => p_interest_type_id,
1820 p_primary_interest_code_id => p_primary_interest_code_id,
1821 p_secondary_interest_code_id => p_secondary_interest_code_id,
1822 p_return_status => l_interest_fields_status
1823 );
1824
1825 IF l_interest_fields_status <> FND_API.G_RET_STS_SUCCESS
1826 THEN
1827 l_return_status := FND_API.G_RET_STS_ERROR;
1828 END IF;
1829 END IF;
1830
1831 -- Now validate interest status
1832 --
1833 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
1834 THEN
1835 Validate_Int_Status(p_interest_type_id => p_interest_type_id,
1836 p_primary_interest_code_id => p_primary_interest_code_id,
1837 p_secondary_interest_code_id => p_secondary_interest_code_id,
1838 p_interest_status_code => p_interest_status_code,
1839 p_return_status => l_return_status);
1840 END IF;
1841
1842 p_return_status := l_return_status;
1843
1844 END Validate_Interest_Type;
1845
1846 -- Procedure validates interest type ids and returns SUCCESS if all ids are
1847 -- valid, ERROR otherwise
1848 -- Procedure assumes that at least the interest type exists
1849 --
1850 PROCEDURE Validate_Int_Type_Fields ( p_interest_type_id IN NUMBER,
1851 p_primary_interest_code_id IN NUMBER,
1852 p_secondary_interest_code_id IN NUMBER,
1853 p_return_status OUT NOCOPY VARCHAR2
1854 )
1855 Is
1856 CURSOR C_Int_Type_Exists (X_Int_Type_Id NUMBER) IS
1857 SELECT 'X'
1858 FROM as_interest_types_b
1859 WHERE Interest_Type_Id = X_Int_Type_Id;
1860
1861 CURSOR C_Prim_Int_Code_Exists (X_Int_Code_Id NUMBER,
1862 X_Int_Type_Id NUMBER) IS
1863 SELECT 'X'
1864 FROM As_Interest_Codes_B Pic
1865 WHERE Pic.Interest_Type_Id = X_Int_Type_Id
1866 and Pic.Interest_Code_Id = X_Int_Code_Id
1867 and Pic.Parent_Interest_Code_Id Is Null;
1868
1869 CURSOR C_Sec_Int_Code_Exists (X_Sec_Int_Code_Id NUMBER,
1870 X_Int_Code_Id NUMBER,
1871 X_Int_Type_Id NUMBER) IS
1872 SELECT 'X'
1873 FROM As_Interest_Codes_B Sic
1874 WHERE Sic.Interest_Type_Id = X_Int_Type_Id
1875 And Sic.Interest_Code_Id = X_Sec_Int_Code_Id
1876 And Sic.Parent_Interest_Code_Id = X_Int_Code_Id;
1877
1878 l_variable VARCHAR2(1);
1879 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1880 Begin
1881
1882 OPEN C_Int_Type_Exists (p_interest_type_id);
1883 FETCH C_Int_Type_Exists INTO l_variable;
1884
1885 IF (C_Int_Type_Exists%NOTFOUND)
1886 THEN
1887 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
1888 THEN
1889 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1890 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_TYPE', FALSE);
1891 FND_MESSAGE.Set_Token('VALUE', p_interest_type_id, FALSE);
1892 FND_MSG_PUB.Add;
1893 END IF;
1894
1895 l_return_status := FND_API.G_RET_STS_ERROR;
1896 END IF;
1897 CLOSE C_Int_Type_Exists;
1898
1899
1900 IF p_primary_interest_code_id is NOT NULL
1901 and p_primary_interest_code_id <> FND_API.G_MISS_NUM
1902 THEN
1903 OPEN C_Prim_Int_Code_Exists ( p_primary_interest_code_id,
1904 p_interest_type_id);
1905 FETCH C_Prim_Int_Code_Exists INTO l_variable;
1906
1907 IF (C_Prim_Int_Code_Exists%NOTFOUND)
1908 THEN
1909 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_Msg_Lvl_Error)
1910 THEN
1911 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1912 FND_MESSAGE.Set_Token('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
1913 FND_MESSAGE.Set_Token('VALUE', p_primary_interest_code_id, FALSE);
1914 FND_MSG_PUB.Add;
1915 END IF;
1916
1917 l_return_status := FND_API.G_RET_STS_ERROR;
1918 END IF;
1919 CLOSE C_Prim_Int_Code_Exists;
1920 END IF;
1921
1922
1923 IF p_secondary_interest_code_id is NOT NULL
1924 and p_secondary_interest_code_id <> FND_API.G_MISS_NUM
1925 THEN
1926 OPEN C_Sec_Int_Code_Exists (p_secondary_interest_code_id,
1927 p_primary_interest_code_id,
1928 p_interest_type_id);
1929 FETCH C_Sec_Int_Code_Exists INTO l_variable;
1930 IF (C_Sec_Int_Code_Exists%NOTFOUND)
1931 THEN
1932 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1933 THEN
1934 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1935 FND_MESSAGE.Set_Token('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
1936 FND_MESSAGE.Set_Token('VALUE', p_secondary_interest_code_id, FALSE);
1937 FND_MSG_PUB.ADD;
1938 END IF;
1939
1940 l_return_status := FND_API.G_RET_STS_ERROR;
1941 END IF;
1942 CLOSE C_Sec_Int_Code_Exists;
1943 END IF;
1944
1945 p_return_status := l_return_status;
1946
1947 END Validate_Int_Type_Fields;
1948
1949 -- Procedure validates interest status and returns SUCCESS if status is
1950 -- valid, ERROR otherwise
1951 -- Procedure assumes that at least the interest type exists
1952 --
1953 PROCEDURE Validate_Int_Status ( p_interest_type_id IN NUMBER,
1954 p_primary_interest_code_id IN NUMBER,
1955 p_secondary_interest_code_id IN NUMBER,
1956 p_interest_status_code IN VARCHAR2,
1957 p_return_status OUT NOCOPY VARCHAR2
1958 )
1959 Is
1960 CURSOR C_Int_Status_Exists (X_Int_Status_Code Varchar2,
1961 X_Int_Type_Id Number) IS
1962 SELECT 'X'
1963 FROM As_Interest_Statuses
1964 WHERE Interest_Type_Id = X_Int_Type_Id
1965 And Interest_Status_Code = X_Int_Status_Code;
1966
1967 l_variable VARCHAR2(1);
1968 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1969 Begin
1970
1971 IF p_interest_status_code is NOT NULL
1972 and p_interest_status_code <> FND_API.G_MISS_CHAR
1973 THEN
1974 OPEN C_Int_Status_Exists (p_interest_status_code,
1975 p_interest_type_id);
1976 FETCH C_Int_Status_Exists INTO l_variable;
1977 IF (C_Int_Status_Exists%NOTFOUND)
1978 THEN
1979 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1980 THEN
1981 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1982 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_STATUS', FALSE);
1983 FND_MESSAGE.Set_Token('VALUE', p_interest_status_code, FALSE);
1984 FND_MSG_PUB.ADD;
1985 END IF;
1986
1987 l_return_status := FND_API.G_RET_STS_ERROR;
1988 END IF;
1989 CLOSE C_Int_Status_Exists;
1990 END IF;
1991
1992 p_return_status := l_return_status;
1993
1994 END Validate_Int_Status;
1995
1996 -- Procedure validates interest status for product catalog and returns SUCCESS if status is
1997 -- valid, ERROR otherwise
1998 -- Procedure assumes that at least the product category exists
1999 --
2000 PROCEDURE Validate_Int_Status_For_PC ( p_product_category_id IN NUMBER,
2001 p_product_cat_set_id IN NUMBER,
2002 p_interest_status_code IN VARCHAR2,
2003 p_return_status OUT NOCOPY VARCHAR2
2004 )
2005 Is
2006 CURSOR C_Int_Status_Exists (X_Int_Status_Code Varchar2,
2007 X_Product_Category_Id Number,
2008 X_Product_Cat_Set_Id Number) IS
2009 SELECT 'X'
2010 FROM As_Interest_Statuses
2011 WHERE Product_Category_Id = X_Product_Category_Id
2012 And Product_Cat_Set_Id = X_Product_Cat_Set_Id
2013 And Interest_Status_Code = X_Int_Status_Code;
2014
2015 l_variable VARCHAR2(1);
2016 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2017 Begin
2018
2019 IF p_interest_status_code is NOT NULL
2020 and p_interest_status_code <> FND_API.G_MISS_CHAR
2021 THEN
2022 OPEN C_Int_Status_Exists (p_interest_status_code,
2023 p_product_category_id, p_product_cat_set_id);
2024 FETCH C_Int_Status_Exists INTO l_variable;
2025 IF (C_Int_Status_Exists%NOTFOUND)
2026 THEN
2027 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2028 THEN
2029 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
2030 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_STATUS', FALSE);
2031 FND_MESSAGE.Set_Token('VALUE', p_interest_status_code, FALSE);
2032 FND_MSG_PUB.ADD;
2033 END IF;
2034
2035 l_return_status := FND_API.G_RET_STS_ERROR;
2036 END IF;
2037 CLOSE C_Int_Status_Exists;
2038 END IF;
2039
2040 p_return_status := l_return_status;
2041
2042 END Validate_Int_Status_For_PC;
2043
2044 END AS_INTEREST_PVT;