[Home] [Help]
PACKAGE BODY: APPS.AS_SCORECARD_PVT
Source
1 PACKAGE BODY AS_SCORECARD_PVT AS
2 /* $Header: asxvscdb.pls 115.19 2003/03/28 20:34:53 solin ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SCORECARD_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvscdb.pls';
6
7
8 -- API_NAME Get_Score
9
10 -- this will be the main call of the scoreCard scoring engine
11 -- logic:
12 -- 1. validate for single Active Valid ScoreCard
13 -- 2. get sales_lead info
14 -- 3. get active scoreCard rules
15 -- 4. score the lead
16 -- 5. get rank based from score
17 -- 6. update the sales lead with the new score
18 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
22
23 Procedure Get_Score (
24 p_api_version IN NUMBER := 2.0,
25 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
26 p_commit IN VARCHAR2 := FND_API.G_FALSE,
27 p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
28 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
29 p_sales_lead_id IN NUMBER,
30 p_scorecard_id IN NUMBER,
31 -- swkhanna Bug 2260459
32 p_marketing_score IN NUMBER,
33 p_identity_salesforce_id IN NUMBER, -- This is to be used by iStore, pass in user_id
34 p_admin_flag IN Varchar2,
35 p_admin_group_id IN NUMBER,
36 x_rank_id OUT NOCOPY NUMBER,
37 X_SCORE OUT NOCOPY NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2 )
41 IS
42
43 CURSOR c_get_group_id (c_resource_id NUMBER) IS
44 SELECT grp.group_id, jts.resource_id
45 FROM JTF_RS_GROUP_MEMBERS mem,
46 JTF_RS_ROLE_RELATIONS rrel,
47 JTF_RS_ROLES_B role,
48 JTF_RS_GROUP_USAGES u,
49 JTF_RS_GROUPS_B grp,
50 JTF_RS_RESOURCE_EXTNS jts
51 WHERE mem.group_member_id = rrel.role_resource_id
52 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
53 AND rrel.role_id = role.role_id
54 AND role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
55 AND mem.delete_flag <> 'Y'
56 AND rrel.delete_flag <> 'Y'
57 AND SYSDATE BETWEEN rrel.start_date_active AND
58 NVL(rrel.end_date_active,SYSDATE)
59 AND mem.resource_id = jts.resource_id
60 AND jts.user_id = c_resource_id -- changed to support iStore.
61 AND mem.group_id = u.group_id
62 AND u.usage = 'SALES'
63 AND mem.group_id = grp.group_id
64 AND SYSDATE BETWEEN grp.start_date_active AND
65 NVL(grp.end_date_active,SYSDATE)
66 AND ROWNUM < 2;
67
68 l_api_name CONSTANT VARCHAR2(30) := 'Get_Score';
69 l_api_version_number CONSTANT NUMBER := 2.0;
70 l_salesgroup_id NUMBER;
71 l_resource_id NUMBER;
72
73 BEGIN
74 -- Standard Start of API savepoint
75 SAVEPOINT GET_SCORE_PVT;
76
77 -- Standard call to check for call compatibility.
78 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
79 p_api_version,
80 l_api_name,
81 G_PKG_NAME)
82 THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean( p_init_msg_list )
88 THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91
92 -- Debug Message
93 IF (AS_DEBUG_LOW_ON) THEN
94 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
95 'PVT:' || l_api_name || ' start');
96 END IF;
97
98 -- Initialize API return status to SUCCESS
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 --
102 -- API BODY
103 --
104 OPEN c_get_group_id(p_identity_salesforce_id);
105 FETCH c_get_group_id INTO l_salesgroup_id, l_resource_id;
106 CLOSE c_get_group_id;
107
108 -- Call Lead_Process_After_Create, so iStore doesn't need to call it.
109 -- iStore uses OSO 11.5.6 baseline.
110 -- 1. If customer applies iStore 11.5.9, but OSO is still 11.5.6 to 11.5.8,
111 -- real get_score will be called.
112 -- 2. If customer applied iStore 11.5.9, and Leads is still 11.5.9,
113 -- The following code will be called.
114 AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Create(
115 P_Api_Version_Number => 2.0,
116 P_Init_Msg_List => FND_API.G_FALSE,
117 p_Commit => FND_API.G_FALSE,
118 p_Validation_Level => p_validation_level,
119 P_Check_Access_Flag => p_check_access_flag,
120 p_Admin_Flag => p_admin_flag,
121 P_Admin_Group_Id => p_admin_group_id,
122 P_identity_salesforce_id => l_resource_id,
123 P_Salesgroup_id => l_salesgroup_id,
124 P_Sales_Lead_Id => p_sales_lead_id,
125 X_Return_Status => x_return_status,
126 X_Msg_Count => x_msg_count,
127 X_Msg_Data => x_msg_data);
128
129 IF x_return_status = FND_API.G_RET_STS_SUCCESS OR
130 x_return_status = 'W'
131 THEN
132 -- For iStore, if return status is 'A', it's 11.5.9, don't call
133 -- build_lead_sales_team.
134 x_return_status := 'A';
135 END IF;
136
137 --
138 -- End of API body
139 --
140
141 -- Standard check for p_commit
142 IF FND_API.to_Boolean( p_commit )
143 THEN
144 COMMIT WORK;
145 END IF;
146
147 -- Debug Message
148 IF (AS_DEBUG_LOW_ON) THEN
149 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
150 'PVT: ' || l_api_name || ' end');
151 END IF;
152
153 FND_MSG_PUB.Count_And_Get
154 ( p_count => x_msg_count,
155 p_data => x_msg_data );
156
157 EXCEPTION
158 WHEN FND_API.G_EXC_ERROR THEN
159 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
160 P_API_NAME => L_API_NAME
161 ,P_PKG_NAME => G_PKG_NAME
162 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
163 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
164 ,X_MSG_COUNT => X_MSG_COUNT
165 ,X_MSG_DATA => X_MSG_DATA
166 ,X_RETURN_STATUS => X_RETURN_STATUS);
167
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
170 P_API_NAME => L_API_NAME
171 ,P_PKG_NAME => G_PKG_NAME
172 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
173 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
174 ,X_MSG_COUNT => X_MSG_COUNT
175 ,X_MSG_DATA => X_MSG_DATA
176 ,X_RETURN_STATUS => X_RETURN_STATUS);
177
178 WHEN OTHERS THEN
179 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
180 P_API_NAME => L_API_NAME
181 ,P_PKG_NAME => G_PKG_NAME
182 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
183 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
184 ,X_MSG_COUNT => X_MSG_COUNT
185 ,X_MSG_DATA => X_MSG_DATA
186 ,X_RETURN_STATUS => X_RETURN_STATUS);
187 END Get_Score;
188
189 -- Comment out the original code because Get_Score is not used in 11.5.9.
190 /*
191 Procedure Get_Score (
192 p_api_version IN NUMBER := 2.0,
193 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
194 p_commit IN VARCHAR2 := FND_API.G_FALSE,
195 p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
196 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
197 p_sales_lead_id IN NUMBER,
198 p_scorecard_id IN NUMBER,
199 -- swkhanna Bug 2260459
200 p_marketing_score IN NUMBER,
201 p_identity_salesforce_id IN NUMBER,
202 p_admin_flag IN Varchar2,
203 p_admin_group_id IN NUMBER,
204 x_rank_id OUT NOCOPY NUMBER,
205 X_SCORE OUT NOCOPY NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2 )
209 IS
210
211 -- ***** ffang 052201, these cursor is no longer needed
212 -- for validation
213 -- CURSOR C_Validate_ScoreCards IS
214 -- SELECT Count(ScoreCarD_ID)
215 -- FROM AS_SALES_LEAD_SCORECARDS
216 -- WHERE start_date_active <= trunc(SYSDATE)
217 -- AND nvl(end_date_active,SYSDATE) >= trunc(SYSDATE);
218 -- WHERE ENABLED_FLAG = 'Y';
219
220 -- for validation
221 -- CURSOR C_Enabled_ScoreCard IS
222 -- SELECT ScoreCarD_ID
223 -- FROM AS_SALES_LEAD_SCORECARDS
224 -- WHERE start_date_active <= trunc(SYSDATE)
225 -- AND nvl(end_date_active,SYSDATE) >= trunc(SYSDATE);
226 -- WHERE ENABLED_FLAG = 'Y';
227 -- *****
228
229 -- kmahajan - 05/01/01 - for validation against profile
230 CURSOR C_default_ScoreCard (x_scorecard_id NUMBER) IS
231 SELECT 'X' -- scorecard_id
232 FROM AS_SALES_LEAD_SCORECARDS
233 WHERE start_date_active <= trunc(SYSDATE)
234 AND nvl(end_date_active,SYSDATE) >= trunc(SYSDATE)
235 AND scorecard_id = x_scorecard_id;
236
237
238 -- for rules information
239 Cursor C_ScoreCard_Rules (IN_SCORECARD_ID NUMBER) IS
240 select card_rule_id CARD_RULE_ID,
241 score SCORE,
242 high_value_number HIGH_VALUE_NUMBER,
243 low_value_number LOW_VALUE_NUMBER,
244 high_value_char HIGH_VALUE_CHAR,
245 low_value_char LOW_VALUE_CHAR,
246 qual_value_id QUAL_VALUE_ID,
247 seed_qual_id SEED_QUAL_ID,
248 source_table_name SOURCE_TABLE_NAME,
249 source_column_name SOURCE_COLUMN_NAME,
250 substr(data_type,1,8) DATA_TYPE,
251 range_flag RANGE_FLAG,
252 meaning MEANING
253 from AS_SCORECARD_RULES_V
254 where scorecard_id = IN_SCORECARD_ID;
255
256 -- get the lead info to compare to the rules
257 -- this one has contacts
258 Cursor C_Sales_lead_info_contact (IN_SALES_LEAD_ID NUMBER) IS
259 select a.LAST_UPDATE_DATE,
260 party.party_name CUSTOMER_NAME, --a.CUSTOMER_NAME,
261 c.CONTACT_ROLE_CODE,
262 a.CHANNEL_CODE,
263 a.BUDGET_AMOUNT,
264 a.CURRENCY_CODE,
265 a.DECISION_TIMEFRAME_CODE,
266 a.BUDGET_STATUS_CODE,
267 AMSCT.CAMPAIGN_NAME SOURCE_PROMOTION_NAME,
268 -- a.SOURCE_PROMOTION_NAME,
269 a.LEAD_RANK_ID
270 from as_sales_leads a, -- as_sales_leads_v a,
271 as_sales_lead_contacts c,
272 AMS_CAMPAIGNS_ALL_B AMSCB,AMS_CAMPAIGNS_ALL_TL AMSCT,
273 AMS_SOURCE_CODES AMSS,
274 hz_parties party
275 where a.sales_lead_id = IN_SALES_LEAD_ID
276 and a.sales_lead_id = c.sales_lead_id
277 and c.primary_contact_flag = 'Y'
278 and AMSS.SOURCE_CODE = AMSCB.SOURCE_CODE(+)
279 AND AMSCB.CAMPAIGN_ID = AMSCT.CAMPAIGN_ID(+)
280 and AMSCT.LANGUAGE(+) = USERENV('LANG')
281 and AMSS.SOURCE_CODE_ID(+) = a.source_promotion_id
282 and party.party_id=a.customer_id;
283
284 -- *** ffang 091901, use base tables instead of as_sales_leads_v
285 -- select a.LAST_UPDATE_DATE,
286 -- a.CUSTOMER_NAME,
287 -- c.CONTACT_ROLE_CODE,
288 -- a.CHANNEL_CODE,
289 -- a.BUDGET_AMOUNT,
290 -- a.CURRENCY_CODE,
291 -- a.DECISION_TIMEFRAME_CODE,
292 -- a.BUDGET_STATUS_CODE,
293 -- a.SOURCE_PROMOTION_NAME,
294 -- a.LEAD_RANK_ID
295 -- from as_sales_leads_v a,
296 -- as_sales_lead_contacts c
297 -- where a.sales_lead_id = IN_SALES_LEAD_ID AND
298 -- a.sales_lead_id = c.sales_lead_id AND
299 -- c.primary_contact_flag = 'Y';
300 --*** end of ffang 091901
301
302 -- this cursor is if the lead has no contacts
303 Cursor C_Sales_lead_info (IN_SALES_LEAD_ID NUMBER) IS
304 select a.LAST_UPDATE_DATE,
305 party.party_name CUSTOMER_NAME, -- a.CUSTOMER_NAME,
306 a.CHANNEL_CODE,
307 a.BUDGET_AMOUNT,
308 a.CURRENCY_CODE,
309 a.DECISION_TIMEFRAME_CODE,
310 a.BUDGET_STATUS_CODE,
311 AMSCT.CAMPAIGN_NAME SOURCE_PROMOTION_NAME,
312 -- a.SOURCE_PROMOTION_NAME,
313 a.LEAD_RANK_ID
314 from as_sales_leads a, -- as_sales_leads_v a
315 hz_parties party,
316 AMS_CAMPAIGNS_ALL_B AMSCB,AMS_CAMPAIGNS_ALL_TL AMSCT,
317 AMS_SOURCE_CODES AMSS
318 where a.sales_lead_id = IN_SALES_LEAD_ID
319 and party.party_id = a.customer_id
320 and AMSS.SOURCE_CODE = AMSCB.SOURCE_CODE(+)
321 AND AMSCB.CAMPAIGN_ID = AMSCT.CAMPAIGN_ID(+)
322 and AMSCT.LANGUAGE(+) = USERENV('LANG')
323 and AMSS.SOURCE_CODE_ID(+) = a.source_promotion_id;
324
325 -- *** ffang 091901, use base tables instead of as_sales_leads_v
326 -- select a.LAST_UPDATE_DATE,
327 -- a.CUSTOMER_NAME,
328 -- a.CHANNEL_CODE,
329 -- a.BUDGET_AMOUNT,
330 -- a.CURRENCY_CODE,
331 -- a.DECISION_TIMEFRAME_CODE,
332 -- a.BUDGET_STATUS_CODE,
333 -- a.SOURCE_PROMOTION_NAME,
334 -- a.LEAD_RANK_ID
338
335 -- from as_sales_leads_v a
336 -- where a.sales_lead_id = IN_SALES_LEAD_ID;
337 --*** end of ffang 091901
339 -- cursor to find if lead has contacts
340 Cursor c_lead_contacts(IN_SALES_LEAD_ID NUMBER) IS
341 select count(1)
342 from as_sales_Lead_contacts
343 where sales_lead_id = IN_SALES_LEAD_ID AND
344 primary_contact_flag = 'Y';
345
346 -- cursor to see if rank is valid
347 Cursor C_rank (IN_SCORE NUMBER) IS
348 Select Rank_id
349 from AS_SALES_LEAD_RANKS_B
350 where min_score <= IN_SCORE AND
351 max_score >= IN_SCORE
352 -- kmahajan 03/26/01 line below added for re-opened Bug 1687132
353 AND enabled_flag = 'Y';
354
355 Cursor C_Lead_Info (IN_SALES_LEAD_ID NUMBER) IS
356 Select Lead_Rank_ID,
357 ASSIGN_TO_SALESFORCE_ID,
358 ASSIGN_TO_PERSON_ID,
359 ASSIGN_SALES_GROUP_ID,
360 last_update_date
361 from AS_SALES_LEADS
362 Where sales_lead_id = IN_SALES_LEAD_ID;
363
364
365 --
366 l_api_name CONSTANT VARCHAR2(30) := 'Get_Score';
367 l_api_version_number CONSTANT NUMBER := 2.0;
368 -- l_card_rule_id NUMBER := p_card_rule_id;
369 l_return_status VARCHAR2(1);
370 l_msg_count NUMBER;
371 l_msg_data VARCHAR2(32767);
372 l_rowid VARCHAR2(50);
373 l_num_scoreCards_enabled NUMBER; -- for validation
374 l_scoreCard_id NUMBER := p_scorecard_id; -- active scorecard
375 l_sales_lead_id NUMBER := p_sales_lead_id;
376 l_sales_lead_rec AS_SALES_LEADS_PUB.sales_lead_rec_type
377 := AS_API_RECORDS_PKG.get_p_sales_lead_rec;
378 l_valid_level_full NUMBER := 90;
379 l_total_Score NUMBER := 0; -- the Final Score for the lead
380 l_rank_id NUMBER; -- will be output
381 l_rank_Code VARCHAR2(25); -- will be output
382 l_sales_lead_info C_Sales_lead_info%rowtype;
383 l_sales_lead_info_contact C_Sales_lead_info_contact%rowtype;
384 type t_rules_table is Table of
385 C_ScoreCard_Rules%rowtype index by binary_integer;
386 v_rules t_rules_table;
387
388 --type t_lead_info C_Lead_Info%rowtype;
389 v_lead_info C_Lead_Info%rowtype;
390
391 i NUMBER := 0;
392 l_last_update_date DATE;
393 l_rule VARCHAR2(35); --for hardCoded Source_Column_Name
394 l_contacts_count NUMBER;
395 l_CUSTOMER_NAME VARCHAR2(360); -- for party_name
396 l_CONTACT_ROLE_CODE VARCHAR2(30); -- contact role
397 l_CHANNEL_CODE VARCHAR2(80); -- sales channel (lookup)
398 l_BUDGET_AMOUNT NUMBER; -- $ amount
399 l_CURRENCY_CODE VARCHAR2(15); -- currency
400 l_DECISION_TIMEFRAME_CODE VARCHAR2(80); -- decision time frame
401 l_BUDGET_STATUS_CODE VARCHAR2(30); -- budget status
402 l_SOURCE_PROMOTION_NAME VARCHAR2(240); -- promotion name
403 l_PREVIOUS_RANK_ID NUMBER;
404
405 l_default_scorecard_id NUMBER;
406 l_val VARCHAR2(1);
407 l_call_user_hook BOOLEAN;
408 -- swkhanna 2260459
409 l_marketing_score NUMBER := p_marketing_score;
410
411 BEGIN
412 -- Standard Start of API savepoint
413 SAVEPOINT GET_SCORE_PVT;
414
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
417 p_api_version,
418 l_api_name,
419 G_PKG_NAME)
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list )
426 THEN
427 FND_MSG_PUB.initialize;
428 END IF;
429
430 -- Debug Message
431 IF (AS_DEBUG_LOW_ON) THEN
432
433 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
434 'PVT:' || l_api_name || 'start');
435 END IF;
436
437 -- Initialize API return status to SUCCESS
438 l_return_status := FND_API.G_RET_STS_SUCCESS;
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 --
442 -- API BODY
443 --
444
445 -- ffang 052201, scorecard id should be past in, if not, error out
446 IF (p_scorecard_id IS NOT NULL and p_scorecard_id<>FND_API.G_MISS_NUM)
447 THEN
448 l_scorecard_id := p_scorecard_id;
449
450 -- validate scorecard_id
451 OPEN c_default_scorecard(l_scorecard_id);
452 FETCH c_default_scorecard INTO l_val; -- l_scorecard_id;
453
454 IF (AS_DEBUG_LOW_ON) THEN
455
456
457
458 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
459 'l_val: '||l_val);
460
461 END IF;
462 IF c_default_scorecard%NOTFOUND THEN
463 -- ffang 082801, use a more meaning message
467 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
464 -- note: It is assumed that if no valid scorecard id passed in, then
465 -- the profile for default scorecard is not set.
466 AS_UTILITY_PVT.Set_Message(
468 p_msg_name => 'AS_NO_DEFAULT_SCORECARD'); --'AS_INVALID_SCD_ID',
469 --p_token1 => 'SCD_ID',
470 --p_token1_value => l_scorecard_id);
471
472 l_return_status := 'W';
473 x_return_status := 'W';
474 END IF;
475 Close c_default_scorecard;
476 ELSE
477 -- ffang 082801, use a more meaning message
478 -- note: It is assumed that if no valid scorecard id passed in, then
479 -- the profile for default scorecard is not set.
480 AS_UTILITY_PVT.Set_Message(
481 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
482 p_msg_name => 'AS_NO_DEFAULT_SCORECARD'); --'API_MISSING_ID',
483 --p_token1 => 'COLUMN',
484 --p_token1_value => 'SCORECARD_ID');
485
486 l_return_status := 'W'; -- FND_API.G_RET_STS_ERROR;
487 x_return_status := 'W'; -- FND_API.G_RET_STS_ERROR;
488
489 -- l_default_scorecard_id:=fnd_profile.value('AS_DEFAULT_SCORECARD');
490 END IF;
491
492 IF (AS_DEBUG_LOW_ON) THEN
493
494
495
496 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
497 'scorecard_id : ' || l_scorecard_id);
498
499 END IF;
500 -- end ffang 052201
501
502 -- ***** ffang 052201, these codes are no longer needed
503 -- dbms_output.put_line('Validating scd');
504 -- check the system to see how many scoreCards are enabled.
505 -- if the number is != 1 then error out
506 -- kmahajan - 05/01/01 - changed to check profile if number > 1
507 -- Open C_Validate_ScoreCards;
508 -- Fetch C_Validate_ScoreCards into l_num_scoreCards_enabled;
509
510 -- If ( C_Validate_ScoreCards%NOTFOUND) OR (l_num_ScoreCards_enabled < 1)
511 -- Then
512 --dbms_output.put_line('No active or too many active scorecards enabled');
513 -- Close C_Validate_ScoreCards;
514 -- FND_MESSAGE.Set_Name('AS', 'AS_NO_SCD_ENABLED');
515 -- FND_MSG_PUB.Add;
516 --raise FND_API.G_EXC_ERROR;
517 -- kmahajan 5/5/1
518 -- want to return a Warning without any further processing
519 l_return_status := 'W';
520 -- kmahajan 05/01/01 - elsif added to check for the profile
521 -- elsif (l_num_scorecards_enabled > 1) then
522 -- l_default_scorecard_id := fnd_profile.value('AS_DEFAULT_SCORECARD');
523 -- if l_default_scorecard_id is not null then
524 -- open c_default_scorecard(l_default_scorecard_id);
525 -- fetch c_default_scorecard into l_scorecard_id;
526 -- if c_default_scorecard%NOTFOUND then
527 -- l_default_scorecard_id := null;
528 -- end if;
529 -- close c_default_scorecard;
530 -- end if;
531 -- if l_default_scorecard_id is null then
532 -- Close C_Validate_ScoreCards;
533 -- FND_MESSAGE.Set_Name('AS', 'MULTIPLE_SCD_ENABLED');
534 -- FND_MSG_PUB.Add;
535 --raise FND_API.G_EXC_ERROR;
536 -- kmahajan 5/5/1
537 -- want to return a Warning without any further processing
538 -- l_return_status := 'W';
539 -- end if;
540 -- else
541 -- get the enabled scorecard
542 -- Open C_Enabled_ScoreCard;
543 -- Fetch C_Enabled_ScoreCard into l_scoreCard_id;
544 -- Close c_enabled_scorecard;
545 --dbms_output.PUT_line('active scorecard is ' || l_scoreCard_id);
546 -- end if;
547 -- Close C_Validate_ScoreCards;
548 -- *****
549
550 -- if-then-else below added to return a warning if no valid scorecard
551 -- is found
552 if l_return_status <> 'W' then -- kmahajan 5/5/1
553
554 -- USER HOOK standard : customer pre-processing section - mandatory
555 l_call_user_hook := JTF_USR_HKS.Ok_to_execute('AS_SCORECARD_PVT',
556 'GET_SCORE','B','C');
557
558 IF l_call_user_hook
559 THEN
560 IF (AS_DEBUG_LOW_ON) THEN
561
562 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
563 'Call user_hook is true');
564 END IF;
565 AS_SCORECARD_CUHK.Get_Score_Pre(
566 p_api_version_number => 2.0,
567 p_init_msg_list => FND_API.G_FALSE,
568 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
569 p_commit => FND_API.G_FALSE,
570 p_sales_lead_id => p_sales_lead_id,
571 p_scorecard_id => p_scorecard_id,
572 x_score => l_total_score,
573 x_return_status => l_return_status,
574 x_msg_count => l_msg_count,
575 x_msg_data => l_msg_data);
576
577 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
578 x_return_status := l_return_status;
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581 END IF;
582
586 -- Compute total score, Oracle logic
583 IF (l_call_user_hook AND l_total_score IS NULL) OR
584 NOT l_call_user_hook
585 THEN
587
588 IF (AS_DEBUG_LOW_ON) THEN
589
590
591
592 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
593 'Getting lead info');
594
595 END IF;
596 -- get the information for this lead needed to calculate the score
597
598 -- find out if we have contacts for this lead
599 -- if so, contact_role code is a valid parameter
600 Open c_lead_contacts (l_sales_lead_id);
601 fetch c_lead_contacts into l_contacts_count;
602 close c_lead_contacts;
603
604 IF (AS_DEBUG_LOW_ON) THEN
605
606
607
608 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
609 'Contact Count = ' || l_contacts_count);
610
611 END IF;
612 if l_contacts_count = 1 then
613 Open C_Sales_lead_info_contact (l_sales_lead_id);
614 fetch c_sales_lead_info_contact into
615 l_sales_lead_info_contact.LAST_UPDATE_DATE,
616 l_sales_lead_info_contact.CUSTOMER_NAME,
617 l_sales_lead_info_contact.CONTACT_ROLE_CODE,
618 l_sales_lead_info_contact.CHANNEL_CODE,
619 l_sales_lead_info_contact.BUDGET_AMOUNT,
620 l_sales_lead_info_contact.CURRENCY_CODE,
621 l_sales_lead_info_contact.DECISION_TIMEFRAME_CODE,
622 l_sales_lead_info_contact.BUDGET_STATUS_CODE,
623 l_sales_lead_info_contact.SOURCE_PROMOTION_NAME,
624 l_sales_lead_info_contact.LEAD_RANK_ID;
625
626 l_LAST_UPDATE_DATE := l_sales_lead_info_contact.LAST_UPDATE_DATE;
627 l_CUSTOMER_NAME := l_sales_lead_info_contact.CUSTOMER_NAME;
628 l_CONTACT_ROLE_CODE:= l_sales_lead_info_contact.CONTACT_ROLE_CODE;
629 l_CHANNEL_CODE := l_sales_lead_info_contact.CHANNEL_CODE;
630 l_BUDGET_AMOUNT := l_sales_lead_info_contact.BUDGET_AMOUNT;
631 l_CURRENCY_CODE := l_sales_lead_info_contact.CURRENCY_CODE;
632 l_DECISION_TIMEFRAME_CODE :=
633 l_sales_lead_info_contact.DECISION_TIMEFRAME_CODE;
634 l_BUDGET_STATUS_CODE :=l_sales_lead_info_contact.BUDGET_STATUS_CODE;
635 l_SOURCE_PROMOTION_NAME :=
636 l_sales_lead_info_contact.SOURCE_PROMOTION_NAME;
637 l_PREVIOUS_RANK_ID := l_sales_lead_info_contact.LEAD_RANK_ID;
638 else
639 Open C_Sales_lead_info (l_sales_lead_id);
640 fetch c_sales_lead_info into
641 l_sales_lead_info.LAST_UPDATE_DATE,
642 l_sales_lead_info.CUSTOMER_NAME,
643 l_sales_lead_info.CHANNEL_CODE,
644 l_sales_lead_info.BUDGET_AMOUNT,
645 l_sales_lead_info.CURRENCY_CODE,
646 l_sales_lead_info.DECISION_TIMEFRAME_CODE,
647 l_sales_lead_info.BUDGET_STATUS_CODE,
648 l_sales_lead_info.SOURCE_PROMOTION_NAME,
649 l_sales_lead_info.LEAD_RANK_ID;
650
651 l_LAST_UPDATE_DATE := l_sales_lead_info.LAST_UPDATE_DATE;
652 l_CUSTOMER_NAME := l_sales_lead_info.CUSTOMER_NAME;
653 l_CHANNEL_CODE := l_sales_lead_info.CHANNEL_CODE;
654 l_BUDGET_AMOUNT := l_sales_lead_info.BUDGET_AMOUNT;
655 l_CURRENCY_CODE := l_sales_lead_info.CURRENCY_CODE;
656 l_DECISION_TIMEFRAME_CODE :=
657 l_sales_lead_info.DECISION_TIMEFRAME_CODE;
658 l_BUDGET_STATUS_CODE := l_sales_lead_info.BUDGET_STATUS_CODE;
659 l_SOURCE_PROMOTION_NAME := l_sales_lead_info.SOURCE_PROMOTION_NAME;
660 l_PREVIOUS_RANK_ID := l_sales_lead_info.LEAD_RANK_ID;
661
662 end if;
663 if l_contacts_count = 1 then
664 Close C_Sales_lead_info_contact;
665 else
666 Close C_Sales_lead_info;
667 end if;
668
669 IF (AS_DEBUG_LOW_ON) THEN
670
671
672
673 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
674 'LAST_UPDATE_DATE ' || l_LAST_UPDATE_DATE);
675
676 END IF;
677 IF (AS_DEBUG_LOW_ON) THEN
678
679 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
680 'CONTACT_ROLE_CODE = ' || l_CONTACT_ROLE_CODE);
681 END IF;
682 IF (AS_DEBUG_LOW_ON) THEN
683
684 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
685 'CUSTOMER_NAME = ' || l_CUSTOMER_NAME);
686 END IF;
687 IF (AS_DEBUG_LOW_ON) THEN
688
689 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
690 'CHANNEL_CODE = ' || l_CHANNEL_CODE);
691 END IF;
692 IF (AS_DEBUG_LOW_ON) THEN
693
697 IF (AS_DEBUG_LOW_ON) THEN
694 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
695 'BUDGET_AMOUNT = ' || l_BUDGET_AMOUNT);
696 END IF;
698
699 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
700 'CURRENCY_CODE = ' || l_CURRENCY_CODE);
701 END IF;
702 IF (AS_DEBUG_LOW_ON) THEN
703
704 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
705 'DECISION_TIMEFRAME_CODE = ' || l_DECISION_TIMEFRAME_CODE);
706 END IF;
707 IF (AS_DEBUG_LOW_ON) THEN
708
709 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
710 'BUDGET_STATUS_CODE = ' || l_BUDGET_STATUS_CODE);
711 END IF;
712 IF (AS_DEBUG_LOW_ON) THEN
713
714 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
715 'SOURCE_PROMOTION_NAME = ' || l_SOURCE_PROMOTION_NAME);
716 END IF;
717 IF (AS_DEBUG_LOW_ON) THEN
718
719 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
720 'Previous Rank ID => ' || l_PREVIOUS_RANK_ID);
721 END IF;
722 IF (AS_DEBUG_LOW_ON) THEN
723
724 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
725 'Getting rules');
726 END IF;
727 -- get all the rules with their score(s) and values
728 open C_ScoreCard_Rules(l_scoreCard_id);
729 loop
730 i := i + 1;
731 fetch c_scoreCard_rules into
732 v_rules(i).CARD_RULE_ID,
733 v_rules(i).SCORE,
734 v_rules(i).HIGH_VALUE_NUMBER,
735 v_rules(i).LOW_VALUE_NUMBER,
736 v_rules(i).HIGH_VALUE_CHAR,
737 v_rules(i).LOW_VALUE_CHAR,
738 v_rules(i).QUAL_VALUE_ID,
739 v_rules(i).SEED_QUAL_ID,
740 v_rules(i).SOURCE_TABLE_NAME,
741 v_rules(i).SOURCE_COLUMN_NAME,
742 v_rules(i).DATA_TYPE,
743 v_rules(i).RANGE_FLAG,
744 v_rules(i).MEANING;
745 exit when c_scoreCard_rules%notfound;
746
747 IF (AS_DEBUG_LOW_ON) THEN
748
749
750
751 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
752 'v_rules(i).card_rule_id: '||v_rules(i).card_rule_id);
753
754 END IF;
755
756 --MEANING SOURCE_COLUMN_NAME
757 ------------------------ ---------------------------
758 --Status STATUS_CODE
759 --Organization CUSTOMER_ID
760 --Campaign Code SOURCE_PROMOTION_ID
761 --Contact Role CONTACT_ROLE
762 --Sales Channel CHANNEL_CODE
763 --Budget Amount BUDGET_AMOUNT
764 --Timeframe DECISION_TIMEFRAME_CODE
765 --Budget Status BUDGET_STATUS_CODE
766 --Response Code VEHICLE_RESPONSE_CODE
767 --Urgent URGENT_FLAG
768 --Offer Code OFFER_ID
769 --Purchase Type INTEREST_TYPE_ID
770 --Purchase Primary PRIMARY_INTEREST_CODE_ID
771 --Purchase Secondery SECONDARY_INTEREST_CODE_ID
772
773 -- this is a workaround to the problem of uniquely identifying
774 -- table_name
775 -- use source_column_name to identify the rule
776 l_rule := v_rules(i).source_column_name;
777
778 IF (AS_DEBUG_LOW_ON) THEN
779
780
781
782 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
783 'score pass ' || i || ' rule = ' || l_rule);
784
785 END IF;
786 IF (AS_DEBUG_LOW_ON) THEN
787
788 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
789 'high rule char value = ' || v_rules(i).high_value_char);
790 END IF;
791 IF (AS_DEBUG_LOW_ON) THEN
792
793 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
794 'low rule char value = ' || v_rules(i).low_value_char);
795 END IF;
796
797 -- customer_name
798 if l_rule = 'CUSTOMER_ID' then
799 if v_rules(i).high_value_char like l_CUSTOMER_NAME OR
800 v_rules(i).low_value_char like l_CUSTOMER_NAME then
801 l_total_Score := l_total_Score + v_rules(i).SCORE;
802 end if;
803
804 -- contact_role
805 elsif l_rule = 'CONTACT_ROLE' then
806 -- need to pull the contact_role_code in the AS_SALES_LEADS_V
807 if v_rules(i).high_value_char like l_CONTACT_ROLE_CODE OR
808 v_rules(i).low_value_char like l_CONTACT_ROLE_CODE then
812 -- decision Time_Frame
809 l_total_Score := l_total_Score + v_rules(i).SCORE;
810 end if;
811
813 elsif l_rule = 'DECISION_TIMEFRAME_CODE' then
814 if v_rules(i).high_value_char like l_DECISION_TIMEFRAME_CODE OR
815 v_rules(i).low_value_char like l_DECISION_TIMEFRAME_CODE then
816 l_total_Score := l_total_Score + v_rules(i).SCORE;
817 end if;
818
819 -- source_promotion
820 elsif l_rule = 'SOURCE_PROMOTION_ID' then
821 if v_rules(i).high_value_char = l_SOURCE_PROMOTION_NAME OR
822 v_rules(i).low_value_char = l_SOURCE_PROMOTION_NAME then
823 l_total_Score := l_total_Score + v_rules(i).SCORE;
824 end if;
825
826 -- channel
827 elsif l_rule = 'CHANNEL_CODE' then
828 if v_rules(i).high_value_char like l_CHANNEL_CODE OR
829 v_rules(i).low_value_char like l_CHANNEL_CODE then
830 l_total_Score := l_total_Score + v_rules(i).SCORE;
831 end if;
832
833 -- budget status
834 elsif l_rule = 'BUDGET_STATUS_CODE' then
835 if v_rules(i).high_value_char like l_BUDGET_STATUS_CODE OR
836 v_rules(i).low_value_char like l_BUDGET_STATUS_CODE then
837 l_total_Score := l_total_Score + v_rules(i).SCORE;
838 end if;
839
840 -- budget amount
841 -- we must convert this to functional currency!!!!
842 -- l_CURRENCY_CODE
843 elsif l_rule = 'BUDGET_AMOUNT' then
844 -- kmahajan 04/26/01 - changed OR to AND
845 if v_rules(i).high_value_number >= l_BUDGET_AMOUNT AND
846 v_rules(i).low_value_number <= l_BUDGET_AMOUNT then
847 l_total_Score := l_total_Score + v_rules(i).SCORE;
848 end if;
849
850 -- we have an invalid rule
851 else
852 FND_MESSAGE.Set_Name('AS', 'AS_INVALID_RULE');
853 FND_MSG_PUB.Add;
854 IF (AS_DEBUG_LOW_ON) THEN
855
856 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
857 'invalid rule');
858 END IF;
859 raise FND_API.G_EXC_ERROR;
860 end if;
861
862 --dbms_output.put_line('Running Score is = ' || l_total_score);
863 end Loop;
864
865 -- close any cursors needed
866 Close C_ScoreCard_Rules;
867 END IF; -- Compute total score
868 IF (AS_DEBUG_LOW_ON) THEN
869
870 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
871 'Total Score = ' || l_total_score);
872 END IF;
873 x_score := l_total_score;
874 l_total_score := to_number(l_total_score) + to_number(nvl(l_marketing_score,0));
875
876 IF (AS_DEBUG_LOW_ON) THEN
877
878
879
880 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Total Score = ' || l_sales_lead_rec.total_score);
881
882 END IF;
883 x_score := l_total_score;
884
885
886 -- find if score generated is in a valid rank range
887 if l_total_score is not null then
888 open C_rank(l_total_score);
889 fetch c_rank into l_rank_id;
890 if l_rank_id is null then
891 FND_MESSAGE.Set_Name('AS', 'AS_INVALID_RANK');
892 FND_MSG_PUB.Add;
893 else
894 x_rank_id := l_rank_id;
895 end if;
896 Close C_Rank;
897 end if;
898 IF (AS_DEBUG_LOW_ON) THEN
899
900 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
901 'Previous Rank_id = ' || l_previous_rank_id);
902 END IF;
903 IF (AS_DEBUG_LOW_ON) THEN
904
905 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
906 'Rank_id = ' || l_rank_id);
907 END IF;
908
909 -- get the sales lead information to pass into update API
910 open C_Lead_Info(l_sales_lead_id);
911 fetch C_Lead_Info into
912 v_lead_info.Lead_Rank_ID,
913 v_lead_info.ASSIGN_TO_SALESFORCE_ID,
914 v_lead_info.ASSIGN_TO_PERSON_ID,
915 v_lead_info.ASSIGN_SALES_GROUP_ID,
916 l_last_update_date;
917 close C_Lead_Info;
918
919 -- bypass update if we've simply got the same rank again
920 if (l_rank_id <> v_lead_info.Lead_Rank_ID) or
921 (v_lead_info.Lead_Rank_ID is null) then
922 -- this is for updating the sales lead
923 -- assign the rank_code based on the total score.
924 -- if the total score is not within a valid rank mix/max range,
925 -- pass a null for rank_id
926
927 l_sales_lead_rec.sales_lead_id := l_sales_lead_id;
928 l_sales_lead_rec.total_score := l_total_score;
929 l_sales_lead_rec.scorecard_id := l_scoreCard_id;
930 l_sales_lead_rec.lead_rank_id := l_rank_id;
931 l_sales_lead_rec.lead_rank_code := null;
932 l_sales_lead_rec.ASSIGN_TO_SALESFORCE_ID :=
933 v_lead_info.ASSIGN_TO_SALESFORCE_ID;
934 l_sales_lead_rec.ASSIGN_TO_PERSON_ID :=
935 v_lead_info.ASSIGN_TO_PERSON_ID;
936 l_sales_lead_rec.ASSIGN_SALES_GROUP_ID :=
937 v_lead_info.ASSIGN_SALES_GROUP_ID;
938 l_sales_lead_rec.last_update_date := l_LAST_UPDATE_DATE;
939
940 -- begin raverma 01312001
941 -- add params for security check
942 -- p_identity_salesforce_id IN NUMBER,
943 -- p_admin_flag IN Varchar2(1),
944 -- p_admin_group_id IN NUMBER,
945 -- always check update access = 'Y'
946
947 -- ffang 050901, pass p_check_access_flag as it was past in
948 AS_SALES_LEADS_PUB.update_sales_lead(
949 p_api_version_number => 2.0
950 ,p_init_msg_list => fnd_api.g_false
951 ,p_commit => fnd_api.g_false
952 ,p_validation_level => l_valid_level_full
953 ,p_check_access_flag => p_check_access_flag -- 'Y'
954 ,p_admin_flag => p_admin_flag
955 ,p_admin_group_id => p_admin_group_id
956 ,p_identity_salesforce_id => p_identity_salesforce_id
957 ,p_sales_lead_rec => l_sales_lead_rec
958 ,x_return_status => l_return_status
959 ,x_msg_count => l_msg_count
960 ,x_msg_data => l_msg_data
961 );
962
963 IF (AS_DEBUG_LOW_ON) THEN
964
965
966
967 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
968 'update_lead status = ' || l_return_status);
969
970 END IF;
971 end if;
972
973 IF l_return_status <> 'S' THEN
974 IF (AS_DEBUG_LOW_ON) THEN
975
976 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
977 'Error=====> ' || l_msg_data);
978 END IF;
979 --AST_API.display_error(l_msg_count);
980 x_return_status := l_return_status;
981 raise FND_API.G_EXC_ERROR;
982 END IF;
983
984 end if; -- kmahajan 5/5/1 - if-then-else for l_return_status <> 'W'
985
986 --
987 -- End of API body
988 --
989
990 -- Standard check for p_commit
991 IF FND_API.to_Boolean( p_commit )
992 THEN
993 COMMIT WORK;
994 END IF;
995
996 -- Debug Message
997 IF (AS_DEBUG_LOW_ON) THEN
998
999 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1000 'PVT: ' || l_api_name || ' end');
1001 END IF;
1002 FND_MSG_PUB.Count_And_Get
1003 ( p_count => x_msg_count,
1004 p_data => x_msg_data );
1005
1006 EXCEPTION
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1009 P_API_NAME => L_API_NAME
1010 ,P_PKG_NAME => G_PKG_NAME
1011 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1012 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1013 ,X_MSG_COUNT => X_MSG_COUNT
1014 ,X_MSG_DATA => X_MSG_DATA
1015 ,X_RETURN_STATUS => X_RETURN_STATUS);
1016
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1019 P_API_NAME => L_API_NAME
1020 ,P_PKG_NAME => G_PKG_NAME
1021 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1022 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1023 ,X_MSG_COUNT => X_MSG_COUNT
1024 ,X_MSG_DATA => X_MSG_DATA
1025 ,X_RETURN_STATUS => X_RETURN_STATUS);
1026
1027 WHEN OTHERS THEN
1028 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1029 P_API_NAME => L_API_NAME
1030 ,P_PKG_NAME => G_PKG_NAME
1031 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1032 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1033 ,X_MSG_COUNT => X_MSG_COUNT
1034 ,X_MSG_DATA => X_MSG_DATA
1035 ,X_RETURN_STATUS => X_RETURN_STATUS);
1036 END Get_Score;
1037 */
1038
1039 END AS_SCORECARD_PVT;