1 PACKAGE BODY AS_SALES_LEAD_RANKS_PVT AS
2 /* #$Header: asxvrnkb.pls 115.24 2004/01/23 23:48:36 chchandr ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_RANKS_PVT
5 -- Purpose : to add ranks into AS_SALES_LEAD_RANKS_B and _TL
6 -- History : 07/24/2000 raverma created
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AS_SALES_LEAD_RANKS_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvrnkb.pls';
12 /*
13 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
14 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
15 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
16 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
17 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
18 G_VERSION_NUM NUMBER := 1.0;
19 */
20
21 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
22 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
24 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
25
26 PROCEDURE Validate_Score_Range (
27 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
28 P_Validation_mode IN VARCHAR2,
29 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type,
30 p_is_old_engine IN VARCHAR2,
31 X_Return_Status OUT NOCOPY VARCHAR2,
32 X_Msg_Count OUT NOCOPY NUMBER,
33 X_Msg_Data OUT NOCOPY VARCHAR2
34 )
35 IS
36 CURSOR C_Get_Ranks (c_min NUMBER, c_max NUMBER) IS
37 SELECT rank_id
38 FROM AS_SALES_LEAD_RANKS_B
39 WHERE enabled_flag = 'Y'
40 and ((min_score <= c_min and max_score >= c_min )
41 or (min_score <= c_max and max_score >= c_max));
42
43 CURSOR c_get_count (c_min NUMBER, c_max NUMBER) IS
44 SELECT count(*)
45 FROM AS_SALES_LEAD_RANKS_B
46 WHERE enabled_flag = 'Y'
47 and ((min_score <= c_min and max_score >= c_min )
48 or (min_score <= c_max and max_score >= c_max));
49
50 l_rank_id NUMBER;
51 l_count NUMBER;
52
53 BEGIN
54 -- Initialize message list IF p_init_msg_list is set to TRUE.
55 IF FND_API.to_Boolean( p_init_msg_list )
56 THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59
60 -- Initialize API return status to SUCCESS
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62 IF upper(nvl(p_sales_lead_rank_rec.enabled_flag,'N')) = 'Y'
63 THEN
64 -- Validate if the score range overlap with other ranks' range
65 OPEN C_Get_Ranks (p_sales_lead_rank_rec.min_score,
66 p_sales_lead_rank_rec.max_score);
67 FETCH C_Get_Ranks INTO l_rank_id;
68
69 IF C_Get_Ranks%FOUND
70 THEN
71 -- 120400 ffang For bug 1520911, if the record to be updated is not
72 -- the same record, then fail.
73 OPEN c_get_count (p_sales_lead_rank_rec.min_score,
74 p_sales_lead_rank_rec.max_score);
75 FETCH c_get_count into l_count;
76 CLOSE c_get_count;
77
78 -- ffang 040501, for bug 1713105, check if rank_id is null
79 -- IF (l_count = 1 and p_sales_lead_rank_rec.rank_id <> l_rank_id)
80 IF (l_count = 1 and nvl(p_sales_lead_rank_rec.rank_id,0) <> l_rank_id)
81 or (l_count > 1)
82 THEN
83 IF (AS_DEBUG_LOW_ON) THEN
84
85 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
86 'Score range overlay: '||
87 p_sales_lead_rank_rec.min_score || '-' ||
88 p_sales_lead_rank_rec.max_score);
89 END IF;
90
91 IF p_is_old_engine = 'Y'
92 THEN
93 AS_UTILITY_PVT.Set_Message(
94 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
95 p_msg_name => 'ASF_RANKSCORE_OVERLAP',
96 p_token1 => 'MEANING',
97 p_token1_value => p_sales_lead_rank_rec.meaning);
98 ELSE
99 AS_UTILITY_PVT.Set_Message(
100 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
101 p_msg_name => 'AS_PREC_RANKSCORE_OVERLAP',
102 p_token1 => 'MEANING',
103 p_token1_value => p_sales_lead_rank_rec.meaning);
104 END IF;
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 END IF;
107 END IF;
108 CLOSE C_Get_Ranks;
109 END IF;
110
111 -- Standard call to get message count and IF count is 1, get message info.
112 FND_MSG_PUB.Count_And_Get
113 ( p_count => x_msg_count,
114 p_data => x_msg_data );
115
116 END Validate_Score_Range;
117
118
119 PROCEDURE Validate_Rank_Meaning (
120 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
121 P_Validation_mode IN VARCHAR2,
122 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type,
123 X_Return_Status OUT NOCOPY VARCHAR2,
124 X_Msg_Count OUT NOCOPY NUMBER,
125 X_Msg_Data OUT NOCOPY VARCHAR2
126 )
127 IS
128 CURSOR C_Get_Meaning (c_meaning VARCHAR2) IS
129 SELECT b.rank_id
130 FROM AS_SALES_LEAD_RANKS_B b, AS_SALES_LEAD_RANKS_TL tl
131 WHERE b.rank_id = tl.rank_id
132 and b.enabled_flag = 'Y'
133 and tl.meaning = c_meaning;
134
135 l_rank_id NUMBER;
136
137 BEGIN
138 -- Initialize message list IF p_init_msg_list is set to TRUE.
139 IF FND_API.to_Boolean( p_init_msg_list )
140 THEN
141 FND_MSG_PUB.initialize;
142 END IF;
143
144 -- Initialize API return status to SUCCESS
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146 IF upper(nvl(p_sales_lead_rank_rec.enabled_flag,'N')) = 'Y'
147 THEN
148 -- Validate if the meaning is duplicate
149 OPEN C_Get_Meaning (p_sales_lead_rank_rec.meaning);
150 FETCH C_Get_Meaning INTO l_rank_id;
151 IF C_Get_Meaning%FOUND
152 THEN
153 -- ffang 040501, for bug 1713105, check if rank_id is null
154 -- IF p_sales_lead_rank_rec.rank_id <> l_rank_id
155 IF nvl(p_sales_lead_rank_rec.rank_id,0) <> l_rank_id
156 THEN
157 IF (AS_DEBUG_LOW_ON) THEN
158
159 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
160 'Duplicate rank : '|| p_sales_lead_rank_rec.meaning);
161 END IF;
162
163 AS_UTILITY_PVT.Set_Message(
164 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
165 p_msg_name => 'ASF_DUPLICATE_RANK',
166 p_token1 => 'MEANING',
167 p_token1_value => p_sales_lead_rank_rec.meaning);
168
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 END IF;
171 END IF;
172 CLOSE C_Get_Meaning;
173 END IF;
174 -- Standard call to get message count and IF count is 1, get message info.
175 FND_MSG_PUB.Count_And_Get
176 ( p_count => x_msg_count,
177 p_data => x_msg_data );
178
179 END Validate_Rank_Meaning;
180
181
182 PROCEDURE Validate_PRECEDENCE (
183 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
184 P_Validation_mode IN VARCHAR2,
185 P_PRECEDENCE IN NUMBER,
186 p_sales_lead_rank_id IN NUMBER,
187 X_Return_Status OUT NOCOPY VARCHAR2,
188 X_Msg_Count OUT NOCOPY NUMBER,
189 X_Msg_Data OUT NOCOPY VARCHAR2
190 )
191 IS
192 CURSOR C_Get_Rating (c_precedence NUMBER) IS
193 SELECT rank_id
194 FROM AS_SALES_LEAD_RANKS_B
195 WHERE enabled_flag = 'Y'
196 AND min_score = c_precedence;
197
198 l_sales_lead_rank_id NUMBER;
199
200 BEGIN
201
202 -- Initialize message list if p_init_msg_list is set to TRUE.
203 IF FND_API.to_Boolean( p_init_msg_list )
204 THEN
205 FND_MSG_PUB.initialize;
206 END IF;
207
208
209 -- Initialize API return status to SUCCESS
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211
212 -- validate NOT NULL column
213 IF(p_PRECEDENCE is NULL OR p_PRECEDENCE = FND_API.G_MISS_NUM)
214 THEN
215 AS_UTILITY_PVT.Set_Message(
216 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
217 p_msg_name => 'API_MISSING_ID',
218 p_token1 => 'PRECEDENCE',
219 p_token1_value => p_PRECEDENCE);
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 END IF;
222
223
224 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
225 THEN
226 -- Hint: Validate data
227 -- IF p_PRECEDENCE is not NULL and p_PRECEDENCE <> G_MISS_CHAR
228 -- verify if data is valid
229 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
230
231 OPEN C_Get_Rating(p_PRECEDENCE);
232 FETCH C_Get_Rating INTO l_sales_lead_rank_id;
233
234 IF C_Get_Rating%FOUND
235 THEN
236 AS_UTILITY_PVT.Set_Message(
237 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
238 p_msg_name => 'AS_DUPE_PRECEDENCE');
239
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 END IF;
242 CLOSE C_Get_Rating;
243
244 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
245 THEN
246 -- Hint: Validate data
247 -- IF p_PRECEDENCE <> G_MISS_CHAR
248 -- verify if data is valid
249 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
250
251 OPEN C_Get_Rating(p_PRECEDENCE);
252 FETCH C_Get_Rating INTO l_sales_lead_rank_id;
253
254 IF C_Get_Rating%FOUND
255 THEN
256 IF (l_sales_lead_rank_id <> p_sales_lead_rank_id) THEN
257 AS_UTILITY_PVT.Set_Message(
258 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
259 p_msg_name => 'AS_DUPE_PRECEDENCE');
260
261 x_return_status := FND_API.G_RET_STS_ERROR;
262
263 END IF;
264
265 END IF;
266 CLOSE C_Get_Rating;
267
268 END IF;
269
270 -- Standard call to get message count and if count is 1, get message info.
271 FND_MSG_PUB.Count_And_Get
272 ( p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275
276 END Validate_PRECEDENCE;
277
278
279 PROCEDURE Validate_USED_RANK (
280 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
281 P_Validation_mode IN VARCHAR2,
282 P_Sales_Lead_Rank_Id IN NUMBER,
283 X_Return_Status OUT NOCOPY VARCHAR2,
284 X_Msg_Count OUT NOCOPY NUMBER,
285 X_Msg_Data OUT NOCOPY VARCHAR2
286 )
287 IS
288 CURSOR C_Get_Lead_Using_Rank (c_sales_lead_rank_id NUMBER) IS
289 SELECT 1
290 FROM AS_SALES_LEADS
291 WHERE lead_rank_id = c_sales_lead_rank_id;
292 l_dummy NUMBER;
293 l_default_profile NUMBER;
294 l_sql_text VARCHAR2(500);
295
296 TYPE c_attr_type IS REF CURSOR;
297 lc_rule_cursor c_attr_type;
298
299 BEGIN
300
301 -- Initialize message list if p_init_msg_list is set to TRUE.
302 IF FND_API.to_Boolean( p_init_msg_list )
303 THEN
304 FND_MSG_PUB.initialize;
305 END IF;
306
307
308 -- Initialize API return status to SUCCESS
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310
311
312 l_default_profile := to_number( nvl(fnd_profile.value('AS_DEFAULT_LEAD_ENGINE_RANK'), '-1'));
313
314 IF (p_validation_mode = AS_UTILITY_PVT.G_UPDATE) THEN
315
316 -- first check if profile is using this rating
317 if (p_sales_lead_rank_id = l_default_profile) then
318 AS_UTILITY_PVT.Set_Message(
319 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
320 p_msg_name => 'AS_USED_RANK');
321
322 --x_return_status := FND_API.G_RET_STS_ERROR;
323 raise FND_API.G_EXC_ERROR;
324
325
326 end if;
327
328 -- now check if any lead is using this rating.
329
330 OPEN C_Get_Lead_Using_Rank(p_sales_lead_rank_id);
331 FETCH C_Get_Lead_Using_Rank INTO l_dummy;
332
333
334 IF C_Get_Lead_Using_Rank%FOUND
335 THEN
336 AS_UTILITY_PVT.Set_Message(
337 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
338 p_msg_name => 'AS_USED_RANK');
339 CLOSE C_Get_Lead_Using_Rank;
340 raise FND_API.G_EXC_ERROR;
341 END IF;
342 CLOSE C_Get_Lead_Using_Rank;
343
344
345
346 -- now check if any rule is referring to this rating.
347
348 l_sql_text := 'select 1 from pv_process_rules_b where process_type = ''LEAD_RATING'' and action_value = to_char(:p_sales_lead_rank_id)';
349
350 OPEN lc_rule_cursor FOR l_sql_text USING p_sales_lead_rank_id;
351 FETCH lc_rule_cursor INTO l_dummy;
352 CLOSE lc_rule_cursor;
353
354 if (l_dummy = 1)
355 then
356 AS_UTILITY_PVT.Set_Message(
357 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
358 p_msg_name => 'AS_USED_RANK');
359
360 raise FND_API.G_EXC_ERROR;
361 end if;
362
363
364 END IF;
365
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 );
372
373 END Validate_USED_RANK;
374
375
376
377
378 PROCEDURE Create_Rank (
379 p_api_version IN NUMBER := 2.0,
380 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
381 p_commit IN VARCHAR2 := FND_API.G_FALSE,
382 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type,
387 x_sales_lead_rank_id OUT NOCOPY NUMBER)
388 IS
389 l_api_name CONSTANT VARCHAR2(30) := 'Create_Rank';
390 l_api_version CONSTANT NUMBER := 2.0;
391 l_return_status VARCHAR2(1);
392 l_msg_count NUMBER;
393 l_msg_data VARCHAR2(32767);
394 l_commit VARCHAR2(1);
395 l_sales_lead_rank_rec AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type;
396 l_rank_id NUMBER;
397 l_dummy CHAR(1);
398 l_new_min_score NUMBER;
399 l_new_max_score NUMBER;
400 l_is_old_engine VARCHAR2(1) := 'Y';
401
402 CURSOR c1 IS
403 SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
404 WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;
405
406 CURSOR c2 IS
407 SELECT MAX(MIN_SCORE) FROM AS_SALES_LEAD_RANKS_B;
408
409
410 BEGIN
411 -- Standard start of API savepoint
412 SAVEPOINT Create_Rank_PVT;
413
414 -- Standard call to check for call compatibility
415 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
416 l_api_name, G_PKG_NAME)
417 THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420
421 -- Initialize message list if p_init_msg_list is set to TRUE
422 IF FND_API.To_Boolean(p_init_msg_list) THEN
423 FND_MSG_PUB.initialize;
424 END IF;
425
426 -- Initialize API return status to success
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- API body
430 OPEN c1;
431 FETCH c1 INTO l_dummy;
432 IF c1%FOUND THEN
433 CLOSE c1;
434 --dbms_output.put_line('duplicate found ');
435 FND_MESSAGE.SET_NAME('AS', 'AS_DUPE_RANK');
436 -- Add message to API message list
437 FND_MSG_PUB.Add;
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440 CLOSE c1;
441
442 /* OPEN c2;
443 FETCH c2 into l_new_min_score;
444 IF c2%NOTFOUND THEN
445 l_new_min_score := 0;
446 ELSE
447 l_new_min_score := l_new_min_score + 1;
448 END IF;
449 CLOSE c2;
450
451 l_new_max_score := l_new_min_score; */
452
453 l_sales_lead_rank_rec := p_sales_lead_rank_rec;
454
455 /*l_sales_lead_rank_rec.min_score := l_new_min_score;
456 l_sales_lead_rank_rec.max_score := l_new_max_score;
457 */
458
459 /*
460 Code to ensure that both new and old rank and rating engines work together
461 */
462
463 IF l_sales_lead_rank_rec.max_score is NULL
464 THEN
465 l_sales_lead_rank_rec.max_score := l_sales_lead_rank_rec.min_score;
466 l_is_old_engine := 'N';
467 END IF;
468
469
470 /* The Validate precedence routine is called only if the caller is the new rating setup screen*/
471
472 IF l_is_old_engine = 'N'
473 THEN
474 Validate_PRECEDENCE(
475 p_init_msg_list => FND_API.G_FALSE,
476 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
477 p_PRECEDENCE => P_SALES_LEAD_RANK_Rec.MIN_SCORE,
478 p_SALES_LEAD_RANK_ID => -1,
479 x_return_status => x_return_status,
480 x_msg_count => x_msg_count,
481 x_msg_data => x_msg_data);
482 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
483 raise FND_API.G_EXC_ERROR;
484 END IF;
485 END IF; /* end if l_is_old_engine = 'N' */
486
487
488 /*
489 -- Check score range
490 Validate_Score_Range (
491 P_Init_Msg_List => FND_API.G_FALSE,
492 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
493 p_sales_lead_rank_rec => l_sales_lead_rank_rec,
494 p_is_old_engine => l_is_old_engine,
495 X_Return_Status => x_return_status,
496 X_Msg_Count => x_msg_count,
497 X_Msg_Data => x_msg_data
498 );
499
500 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 */
505
506
507 -- Check duplicate rank
508 Validate_Rank_Meaning (
509 P_Init_Msg_List => FND_API.G_FALSE,
510 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
511 p_sales_lead_rank_rec => l_sales_lead_rank_rec,
512 X_Return_Status => x_return_status,
513 X_Msg_Count => x_msg_count,
514 X_Msg_Data => x_msg_data
515 );
516
517 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520
521 -- get the nextval from the sequence as rank_id
522 select as_sales_lead_ranks_s.nextval into l_rank_id from dual;
523 -- this is being commented out since this occurs before now
524 --l_sales_lead_rank_rec := p_sales_lead_rank_rec;
525 l_sales_lead_rank_rec.rank_id := l_rank_id;
526
527 -- X_CREATION_DATE in DATE,
528 -- X_CREATED_BY in NUMBER,
529
530 --dbms_output.put_line('before insert Row');
531 AS_SALES_LEAD_RANKS_PKG.Insert_Row(
532 x_RANK_ID => l_sales_lead_rank_rec.RANK_ID,
533 x_MIN_SCORE => l_sales_lead_rank_rec.MIN_SCORE,
534 -- use the same min_score as the max_score as well. Ignore the max_score passed in
535
536 x_MAX_SCORE => l_sales_lead_rank_rec.MAX_SCORE,
537 x_enabled_flag => l_sales_lead_rank_rec.enabled_flag,
538 x_meaning => l_sales_lead_rank_rec.meaning,
539 x_description => l_sales_lead_rank_rec.description,
540 x_creation_date => sysdate,
541 x_created_by => FND_GLOBAL.user_id,
542 X_LAST_UPDATE_DATE => sysdate,
543 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
544 X_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
545 );
546 x_sales_lead_rank_id := l_sales_lead_rank_rec.rank_id;
547 --dbms_output.put_line('private API returns ' || x_return_status);
548
549 -- End of API body
550
551 -- Standard check of p_commit
552 IF FND_API.To_Boolean(p_commit) THEN
553 COMMIT WORK;
554 END IF;
555
556 -- Standard call to get message count and if count is 1, get message info
557 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
558
559 EXCEPTION
560
561 WHEN FND_API.G_EXC_ERROR THEN
562 ROLLBACK TO Create_Rank_PVT;
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 FND_MSG_PUB.Count_And_Get(
565 p_count => x_msg_count,
566 p_data => x_msg_data);
567
568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
569 ROLLBACK TO Create_Rank_PVT;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571 FND_MSG_PUB.Count_And_Get(
572 p_count => x_msg_count,
573 p_data => x_msg_data);
574
575 WHEN OTHERS THEN
576 ROLLBACK TO Create_Rank_PVT;
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
579 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
580 END IF;
581 FND_MSG_PUB.Count_And_Get(
582 p_count => x_msg_count,
583 p_data => x_msg_data);
584 END Create_Rank;
585
586
587 PROCEDURE Update_Rank (
588 p_api_version IN NUMBER := 2.0,
589 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
590 p_commit IN VARCHAR2 := FND_API.G_FALSE,
591 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_msg_count OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2,
595 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type)
596 IS
597 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rank';
598 l_api_version CONSTANT NUMBER := 2.0;
599 l_return_status VARCHAR2(1);
600 l_msg_count NUMBER;
601 l_msg_data VARCHAR2(32767);
602 l_sales_lead_rank_rec AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type;
603 l_dummy CHAR(1);
604 l_is_old_engine VARCHAR2(1) := 'Y';
605
606 CURSOR c1 IS
607 SELECT enabled_flag, min_score FROM AS_SALES_LEAD_RANKS_B
608 WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;
609 l_enabled_flag VARCHAR(1);
610 l_min_score NUMBER;
611 l_request_id NUMBER;
612
613 -- 120400 ffang, update API should check if the record has been update by
614 -- someone else or not.
615 CURSOR c_get_last_update IS
616 SELECT last_update_date FROM AS_SALES_LEAD_RANKS_B
617 WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;
618 l_last_update_date DATE;
619
620 BEGIN
621 -- Standard start of API savepoint
622 SAVEPOINT Update_Rank_PVT;
623
624 -- Standard call to check for call compatibility
625 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
626 l_api_name, G_PKG_NAME)
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 -- Initialize message list if p_init_msg_list is set to TRUE
632 IF FND_API.To_Boolean(p_init_msg_list) THEN
633 FND_MSG_PUB.initialize;
634 END IF;
635
636 l_sales_lead_rank_rec := p_sales_lead_rank_rec;
637
638 -- Initialize API return status to success
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640
641 -- API body
642 OPEN c1;
643 FETCH c1 INTO l_enabled_flag, l_min_score;
644 IF (c1%NOTFOUND) THEN
645 CLOSE c1;
646 RAISE NO_DATA_FOUND;
647 END IF;
648 CLOSE c1;
649
650 -- 120400 ffang, Check Whether record has been changed by someone else
651 IF (p_sales_lead_rank_rec.last_update_date is NULL or
652 p_sales_lead_rank_rec.last_update_date = FND_API.G_MISS_Date )
653 THEN
654 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
655 THEN
656 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
657 FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
658 FND_MSG_PUB.ADD;
659 END IF;
660 raise FND_API.G_EXC_ERROR;
661 END IF;
662
663 OPEN c_get_last_update;
664 FETCH c_get_last_update into l_last_update_date;
665 IF (c_get_last_update%NOTFOUND) THEN
666 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
667 THEN
668 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
669 FND_MESSAGE.Set_Token ('INFO', 'SALES_LEAD_RANK', FALSE);
670 FND_MSG_PUB.Add;
671 END IF;
672 CLOSE c_get_last_update;
673
674 raise FND_API.G_EXC_ERROR;
675 END IF;
676 CLOSE c_get_last_update;
677
678 IF (p_sales_lead_rank_rec.last_update_date <> l_last_update_date)
679 THEN
680 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
681 THEN
682 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
683 FND_MESSAGE.Set_Token('INFO', 'SALES_LEAD', FALSE);
684 FND_MSG_PUB.ADD;
685 END IF;
686 raise FND_API.G_EXC_ERROR;
687 END IF;
688
689 -- Check score range
690 -- 120200 FFANG for bug 1520911
691 -- Only validate score range when either min score or max score passed in
692 -- then do the validataion
693
694
695
696
697 IF p_sales_lead_rank_rec.min_score is not null
698 THEN
699
700 IF l_sales_lead_rank_rec.max_score is NULL
701 THEN
702 l_sales_lead_rank_rec.max_score := l_sales_lead_rank_rec.min_score;
703 l_is_old_engine := 'N';
704
705 END IF;
706
707 /* The Validate precedence routine is called only if the caller is the new rating setup screen*/
708
709 IF l_is_old_engine = 'N'
710 THEN
711 Validate_PRECEDENCE(
712 p_init_msg_list => FND_API.G_FALSE,
713 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
714 p_PRECEDENCE => P_SALES_LEAD_RANK_Rec.MIN_SCORE,
715 p_sales_lead_rank_id => P_SALES_LEAD_RANK_Rec.RANK_ID,
716 x_return_status => x_return_status,
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data);
719 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
720 raise FND_API.G_EXC_ERROR;
721 END IF;
722 END IF;
723
724 /*
725 -- Check score range
726 Validate_Score_Range (
727 P_Init_Msg_List => FND_API.G_FALSE,
728 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
729 p_sales_lead_rank_rec => l_sales_lead_rank_rec,
730 p_is_old_engine => l_is_old_engine,
731 X_Return_Status => x_return_status,
732 X_Msg_Count => x_msg_count,
733 X_Msg_Data => x_msg_data
734 );
735
736 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 */
740 END IF;
741
742 -- Check duplicate rank only when meaning is passed in
743 IF p_sales_lead_rank_rec.meaning is not null THEN
744 Validate_Rank_Meaning (
745 P_Init_Msg_List => FND_API.G_FALSE,
746 P_Validation_mode => AS_UTILITY_PVT.G_UPDATE,
747 p_sales_lead_rank_rec => p_sales_lead_rank_rec,
748 X_Return_Status => x_return_status,
749 X_Msg_Count => x_msg_count,
750 X_Msg_Data => x_msg_data
751 );
752
753 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756 END IF;
757
758
759 -- Cannot disable a rank if used by profile or by a lead
760 IF (l_enabled_flag = 'Y' and p_sales_lead_rank_rec.enabled_flag <> 'Y') THEN
761 Validate_USED_RANK(
762 P_Init_Msg_List => FND_API.G_FALSE,
763 P_Validation_mode => AS_UTILITY_PVT.G_UPDATE,
764 p_sales_lead_rank_id => p_sales_lead_rank_rec.rank_id,
765 X_Return_Status => x_return_status,
766 X_Msg_Count => x_msg_count,
767 X_Msg_Data => x_msg_data
768 );
769
770 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
771 RAISE FND_API.G_EXC_ERROR;
772 END IF;
773 END IF;
774
775
776
777 --dbms_output.put_line('before update Row');
778 AS_SALES_LEAD_RANKS_PKG.Update_Row(
779 x_RANK_ID => l_sales_lead_rank_rec.RANK_ID,
780 x_MIN_SCORE => l_sales_lead_rank_rec.MIN_SCORE,
781 x_MAX_SCORE => l_sales_lead_rank_rec.MAX_SCORE,
782 x_enabled_flag => l_sales_lead_rank_rec.enabled_flag,
783 x_meaning => l_sales_lead_rank_rec.meaning,
784 x_description => l_sales_lead_rank_rec.description,
785 X_LAST_UPDATE_DATE => sysdate,
786 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
787 X_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
788 );
789
790 IF l_min_score <> p_sales_lead_rank_rec.min_score
791 THEN
792 -- Run concurrent program for the rank.
793 -- Update as_sales_leads.lead_rank_score and
794 -- as_accesses_all.lead_rank_score
795 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AS',
796 'ASXSLRS',
797 'Update LEAD_RANK_SCORE',
798 '',
799 FALSE,
800 p_sales_lead_rank_rec.rank_id,
801 p_sales_lead_rank_rec.min_score);
802
803 END IF;
804 -- End of API body
805
806 -- Standard check of p_commit
807 IF FND_API.To_Boolean(p_commit) THEN
808 COMMIT WORK;
809 END IF;
810
811 -- Standard call to get message count and if count is 1, get message info
812 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
813
814 EXCEPTION
815 WHEN FND_API.G_EXC_ERROR THEN
816 ROLLBACK TO Update_Rank_PVT;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MSG_PUB.Count_And_Get(
819 p_count => x_msg_count,
820 p_data => x_msg_data);
821
822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
823 ROLLBACK TO Update_Rank_PVT;
824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825 FND_MSG_PUB.Count_And_Get(
826 p_count => x_msg_count,
827 p_data => x_msg_data);
828
829 WHEN OTHERS THEN
830 ROLLBACK TO Update_Rank_PVT;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
833 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
834 END IF;
835 FND_MSG_PUB.Count_And_Get(
836 p_count => x_msg_count,
837 p_data => x_msg_data);
838 END Update_Rank;
839
840
841 Procedure Delete_Rank (
842 p_api_version IN NUMBER := 2.0,
843 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
844 p_commit IN VARCHAR2 := FND_API.G_FALSE,
845 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 p_sales_lead_rank_id IN NUMBER)
850 IS
851 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rank';
852 l_api_version CONSTANT NUMBER := 2.0;
853 l_return_status VARCHAR2(1);
854 l_msg_count NUMBER;
855 l_msg_data VARCHAR2(32767);
856 l_sales_lead_rank_id NUMBER;
857 l_dummy CHAR(1);
858 l_default_profile NUMBER;
859 l_sql_text VARCHAR2(500);
860 l_dummy2 NUMBER;
861
862 TYPE c_attr_type IS REF CURSOR;
863 lc_rule_cursor c_attr_type;
864
865
866 CURSOR C_Get_Lead_Using_Rank (c_sales_lead_rank_id NUMBER) IS
867 SELECT 1
868 FROM AS_SALES_LEADS
869 WHERE lead_rank_id = c_sales_lead_rank_id;
870
871
872 CURSOR c1 IS
873 SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
874 WHERE rank_id = p_sales_lead_rank_id;
875
876 BEGIN
877 -- Standard start of API savepoint
878 SAVEPOINT Delete_Rank_PVT;
879
880 -- Standard call to check for call compatibility
881 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
882 l_api_name, G_PKG_NAME)
883 THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887 -- Initialize message list if p_init_msg_list is set to TRUE
888 IF FND_API.To_Boolean(p_init_msg_list) THEN
889 FND_MSG_PUB.initialize;
890 END IF;
891
892 l_sales_lead_rank_id := p_sales_lead_rank_id;
893
894 FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
895
896
897
898 -- Initialize API return status to success
899 x_return_status := FND_API.G_RET_STS_SUCCESS;
900
901
902 -- API body
903 OPEN c1;
904 FETCH c1 INTO l_dummy;
905 --dbms_output.put_line('dummy ' || l_dummy);
906 IF (c1%NOTFOUND) THEN
907 CLOSE c1;
908 --dbms_output.put_line('no data found');
909 RAISE NO_DATA_FOUND;
910 END IF;
911 CLOSE c1;
912
913
914 --Validate if seeded rank. If seeded, it should be locked. User delete
915 -- not allowed. A good check for seeded rank is that rank_id < 10000
916
917 if (l_sales_lead_rank_id < 10000) then
918 AS_UTILITY_PVT.Set_Message(
919 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
920 p_msg_name => 'AS_SEEDED_RANK');
921
922 raise FND_API.G_EXC_ERROR;
923 end if;
924
925 --Do not allow delete on any grade which is referred by any Lead
926 --otherwise it will throw BIM report OUT NOCOPY of sink), disable the rank if
927 -- not to be used.
928
929 OPEN C_Get_Lead_Using_Rank(l_sales_lead_rank_id);
930 FETCH C_Get_Lead_Using_Rank INTO l_dummy;
931
932
933 IF C_Get_Lead_Using_Rank%FOUND
934 THEN
935 AS_UTILITY_PVT.Set_Message(
936 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
937 p_msg_name => 'AS_USED_RANK');
938
939 CLOSE C_Get_Lead_Using_Rank;
940 raise FND_API.G_EXC_ERROR;
941 END IF;
942 CLOSE C_Get_Lead_Using_Rank;
943
944 -- Do not allow delete on any grade which is set in the profile value
945
946 l_default_profile := to_number( nvl(fnd_profile.value('AS_DEFAULT_LEAD_ENGINE_RANK'), '-1'));
947
948
949 if (l_sales_lead_rank_id = l_default_profile) then
950 AS_UTILITY_PVT.Set_Message(
951 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
952 p_msg_name => 'AS_USED_RANK');
953
954 raise FND_API.G_EXC_ERROR;
955
956 end if;
957
958
959 -- now check if any rule is referring to this rating.
960
961 l_sql_text := 'select 1 from pv_process_rules_b where process_type = ''LEAD_RATING'' and action_value = to_char(:l_sales_lead_rank_id)';
962
963 OPEN lc_rule_cursor FOR l_sql_text USING l_sales_lead_rank_id;
964 FETCH lc_rule_cursor INTO l_dummy2;
965 CLOSE lc_rule_cursor;
966
967 if (l_dummy2= 1)
968 then
969 AS_UTILITY_PVT.Set_Message(
970 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
971 p_msg_name => 'AS_USED_RANK');
972
973 raise FND_API.G_EXC_ERROR;
974 end if;
975
976
977
978 AS_SALES_LEAD_RANKS_PKG.Delete_Row(X_RANK_ID => l_sales_lead_rank_id);
979 -- End of API body
980
981
982
983
984 -- Standard check of p_commit
985 IF FND_API.To_Boolean(p_commit) THEN
986 COMMIT WORK;
987 END IF;
988
989 -- Standard call to get message count and if count is 1, get message info
990 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
991
992 EXCEPTION
993 WHEN FND_API.G_EXC_ERROR THEN
994 ROLLBACK TO Delete_Rank_PVT;
995 x_return_status := FND_API.G_RET_STS_ERROR;
996 FND_MSG_PUB.Count_And_Get(
997 p_count => x_msg_count,
998 p_data => x_msg_data);
999
1000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1001 ROLLBACK TO Delete_Rank_PVT;
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 FND_MSG_PUB.Count_And_Get(
1004 p_count => x_msg_count,
1005 p_data => x_msg_data);
1006
1007 WHEN OTHERS THEN
1008 ROLLBACK TO Delete_Rank_PVT;
1009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1010 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1011 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1012 END IF;
1013 FND_MSG_PUB.Count_And_Get(
1014 p_count => x_msg_count,
1015 p_data => x_msg_data);
1016 END DELETE_RANK;
1017
1018
1019 PROCEDURE UPDATE_LEAD_RANK_SCORE(
1020 ERRBUF OUT NOCOPY VARCHAR2,
1021 RETCODE OUT NOCOPY VARCHAR2,
1022 X_LEAD_RANK_ID IN NUMBER,
1023 X_LEAD_RANK_SCORE IN NUMBER)
1024 IS
1025 BEGIN
1026 --Update the as_sales_leads.lead_rank_score
1027 Write_log (1, 'Updating the as_sales_leads.lead_rank_score');
1028 UPDATE as_sales_leads sl
1029 SET sl.lead_rank_score = x_lead_rank_score
1030 , sl.last_update_date = sysdate
1031 , sl.last_updated_by = fnd_global.user_id
1032 , sl.last_update_login = fnd_global.conc_login_id
1033 WHERE sl.lead_rank_id = x_lead_rank_id;
1034
1035 --Update the as_accesses_all.lead_rank_score
1036 Write_log (1, 'Updating the as_accesses_all.lead_rank_score');
1037 UPDATE AS_ACCESSES_ALL acc
1038 SET acc.lead_rank_score = x_lead_rank_score
1039 , acc.last_update_date = sysdate
1040 , acc.last_updated_by = fnd_global.user_id
1041 , acc.last_update_login = fnd_global.conc_login_id
1042 WHERE acc.sales_lead_id IN
1043 (select sl.sales_lead_id
1044 from as_sales_leads sl
1045 where sl.lead_rank_id = x_lead_rank_id);
1046
1047 EXCEPTION
1048 WHEN FND_API.G_EXC_ERROR THEN
1049 ERRBUF := ERRBUF || sqlerrm;
1050 RETCODE := FND_API.G_RET_STS_ERROR;
1051 ROLLBACK;
1052 Write_log (1, 'Error in as_sales_lead_ranks_pvt.update_lead_rank_score');
1053 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
1054 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1056 ERRBUF := ERRBUF||sqlerrm;
1057 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1058 ROLLBACK;
1059 Write_Log(1, 'Unexpected error in as_sales_lead_ranks_pvt.update_lead_rank_score');
1060 WHEN others THEN
1061 ERRBUF := SQLERRM;
1062 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1063 ROLLBACK;
1064 Write_Log(1, 'Exception: others in as_sales_lead_ranks_pvt.update_lead_rank_score');
1065 Write_Log(1, 'SQLCODE ' || to_char(SQLCODE) ||
1066 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1067 end UPDATE_LEAD_RANK_SCORE;
1068
1069
1070 PROCEDURE Write_Log(p_which NUMBER, p_msg VARCHAR2) IS
1071 BEGIN
1072 FND_FILE.put(p_which, p_msg);
1073 FND_FILE.new_line(p_which, 1);
1074 END Write_Log;
1075
1076 END AS_SALES_LEAD_RANKS_PVT;
[Home] [Help]