[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_ENGINE_PVT
Source
1 PACKAGE BODY AS_SALES_LEAD_ENGINE_PVT as
2 /* $Header: asxvsleb.pls 120.1 2006/05/16 11:37:23 solin noship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_ENGINE_PVT
5 -- Purpose : Sales Leads Engines
6 -- NOTE :
7 -- History :
8 -- 02/04/2002 SOLIN Created.
9 -- AS provides package spec, PV provides package body
10 -- for this package.
11 -- 03/25/2002 SOLIN Add PV_AUTOMATED_PARTNER_MATCHING workflow.
12 -- 04/25/2002 SOLIN Add operator IS_NOT_NULL to input filter.
13 -- 05/01/2002 SOLIN Add operator IS_NOT_NULL to attribute
14 -- Product Interest.
15 -- 05/15/2002 SOLIN Add error message if opportunity can't find
16 -- matching rule, or no partner is found for
17 -- the matching rule.
18 -- 05/29/2002 SOLIN Add p_LEAD_DATE, p_SOURCE_SYSTEM, p_COUNTRY
19 -- when calling sales lead table handler.
20 -- Bug 2341515, 2368075
21 -- 06/05/2002 SOLIN Bug 2406434.
22 -- If rating/channel selection engine can't find
23 -- matched rule, use default value from profile.
24 -- Evaluate rules with different precedence.
25 -- 08/13/2002 SOLIN Default value in table pv_entity_rules_applied
26 -- will have process_status G_DEFAULT.
27 -- 08/16/2002 SOLIN Bug 2517227.
28 -- Set indirect_channel_flag for old engine
29 -- 08/20/2002 SOLIN Bug 2520329.
30 -- Don't throw exception if profile
31 -- AS_DEFAULT_LEAD_ENGINE_RANK,
32 -- AS_DEFAULT_LEAD_ENGINE_CHANNEL is NULL
33 -- 08/26/2002 SOLIN Bug 2531830.
34 -- Don't set result for failed qualification rule.
35 -- 09/26/2002 SOLIN Bug 2595996.
36 -- Set qualified_flag, lead_rank_id from sales lead
37 -- record if old qualification engine or ranking engine
38 -- don't need to be run.
39 -- 11/04/2002 SOLIN Add API Lead_Process_After_Create and
40 -- Lead_Process_After_Update
41 -- 11/18/2002 SOLIN Bug 2671964.
42 -- Creation date attribute doesn't work.
43 -- In cursor C_Get_Lead_Info, append '000000', instead
44 -- of '0000'
45 -- 12/17/2002 SOLIN Change for as_sales_leads.lead_rank_ind and
46 -- as_sales_leads_log.manual_rank_flag
47 -- 01/09/2003 SOLIN Bug 2740032
48 -- Obsolete profiles AS_RUN_NEW_LEAD_ENGINES,
49 -- AS_AUTO_QUALIFY, AS_RANK_LEAD_OPTION
50 -- 01/16/2003 SOLIN Remove Start_Partner_Matching.
51 -- It's moved to PV_BG_PARTNER_MATCHING_PUB.
52 -- Change filename from pvxvsleb.pls to asxvsleb.pls
53 -- 01/28/2003 SOLIN Bug 2770000
54 -- Find owner when user declines unqualified lead.
55 -- 02/07/2003 SOLIN Bug 2791689
56 -- Call route_lead_to_marketing before calling
57 -- Create_SalesTeam for lead creator.
58 -- 02/10/2003 SOLIN Bug 2795679
59 -- Call route_lead_to_marketing for incubation channel
60 -- lead.
61 -- 02/12/2003 SOLIN Bug 2791752
62 -- Find lead owner if there's no lead owner in
63 -- Lead_Process_After_Update.
64 -- 02/28/2003 SOLIN Bug 2825108
65 -- Lead creator will have freeze_flag 'Y'.
66 -- 03/07/2003 SOLIN Bug 2822580
67 -- Route_Lead_To_Marketing should remove access records
68 -- when lead is updated.
69 -- 03/14/2003 SOLIN Bug 2852597
70 -- Port 11.5.8 fix to 11.5.9.
71 -- 03/20/2003 SOLIN Bug 2825187
72 -- Add one more parameter p_lead_action in
73 -- aml_monitor_wf.launch_monitor
74 -- 05/01/2003 SOLIN Bug 2877904
75 -- Add open_flag, object_creation_date, and
76 -- lead_rank_score in as_accesses_all table
77 -- 06/17/2003 SWKHANNA changed lead_process_after_update to include logic for starting
78 -- monitor after grade change.
79 --
80 -- 09/09/2003 SWKHANNA Added extra parameter to be passed in to launch monitor
81 -- 12/08/2003 SOLIN Bug 3305007
82 -- NOT_CONTAINS operator doesn't work
83 -- Change lookup_code from "NOT CONTAINS" to
84 -- "NOT_CONTAINS" in Rate_Select_Lead
85 -- END of Comments
86
87
88 /*-------------------------------------------------------------------------*
89 |
90 | PRIVATE CONSTANTS
91 |
92 *-------------------------------------------------------------------------*/
93 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEAD_ENGINE_PVT';
94 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvsleb.pls';
95
96 -- lookup type PV_RULES_APPLIED_STATUS
97 G_FAIL_CRITERIA CONSTANT VARCHAR2(30) := 'FAIL_CRITERIA';
98 G_PASS_RULE CONSTANT VARCHAR2(30) := 'PASS_RULE';
99 G_DEFAULT CONSTANT VARCHAR2(30) := 'DEFAULT';
100
101 -- lookup type PV_PROCESS_TYPE
102 G_LEAD_QUALIFICATION CONSTANT VARCHAR2(30) := 'LEAD_QUALIFICATION';
103 G_LEAD_RATING CONSTANT VARCHAR2(30) := 'LEAD_RATING';
104 G_CHANNEL_SELECTION CONSTANT VARCHAR2(30) := 'CHANNEL_SELECTION';
105
106 /*-------------------------------------------------------------------------*
107 |
108 | PRIVATE DATATYPES
109 |
110 *-------------------------------------------------------------------------*/
111 TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
112 TYPE VARCHAR2_15_TABLE IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
113 TYPE VARCHAR2_500_TABLE IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
114
115 /*-------------------------------------------------------------------------*
116 |
117 | PRIVATE VARIABLES
118 |
119 *-------------------------------------------------------------------------*/
120
121
122 -- API Name: Run_Lead_Engines
123
124 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
125 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
126 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
127 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
128
129
130 PROCEDURE Debug(
131 p_msg_string IN VARCHAR2
132 )
133 IS
134
135 BEGIN
136 --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
137 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
138 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
139 FND_MSG_PUB.Add;
140 --END IF;
141 END Debug;
142
143
144 PROCEDURE Run_Lead_Engines(
145 P_Api_Version_Number IN NUMBER,
146 P_Init_Msg_List IN VARCHAR2,
147 p_Commit IN VARCHAR2,
148 p_Validation_Level IN NUMBER,
149 P_Admin_Group_Id IN NUMBER,
150 P_identity_salesforce_id IN NUMBER,
151 P_Salesgroup_id IN NUMBER,
152 P_Sales_Lead_Id IN NUMBER,
153 -- ckapoor Phase 2 filtering project 11.5.10
154 -- P_Is_Create_Mode IN VARCHAR2,
155
156 X_Lead_Engines_Out_Rec OUT NOCOPY AS_SALES_LEADS_PUB.Lead_Engines_Out_Rec_Type,
157 X_Return_Status OUT NOCOPY VARCHAR2,
158 X_Msg_Count OUT NOCOPY NUMBER,
159 X_Msg_Data OUT NOCOPY VARCHAR2
160 )
161 IS
162 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
163 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.ASSIGN_TO_SALESFORCE_ID,
164 SL.ASSIGN_TO_PERSON_ID, SL.ASSIGN_SALES_GROUP_ID,
165 SL.QUALIFIED_FLAG, SL.PARENT_PROJECT,
166 SL.CHANNEL_CODE, SL.DECISION_TIMEFRAME_CODE, SL.BUDGET_AMOUNT,
167 SL.BUDGET_STATUS_CODE, SL.SOURCE_PROMOTION_ID, SL.STATUS_CODE,
168 SL.REJECT_REASON_CODE, SL.LEAD_RANK_ID
169 FROM AS_SALES_LEADS SL
170 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
171
172 -- Retrieve channel type
173 CURSOR c_get_indirect_channel_flag(c_channel_code VARCHAR2) IS
174 SELECT NVL(channel.indirect_channel_flag, 'N')
175 FROM pv_channel_types channel
176 WHERE channel.channel_lookup_code = c_channel_code;
177
178 l_api_name CONSTANT VARCHAR2(30)
179 := 'Run_Lead_Engines';
180 l_api_version_number CONSTANT NUMBER := 2.0;
181 l_sales_lead_rec AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type;
182
183 l_return_status VARCHAR2(1);
184 l_count INTEGER DEFAULT 0;
185 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
186 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
187 l_sales_lead_log_id NUMBER;
188 l_action_value VARCHAR2(15);
189
190
191 BEGIN
192 -- Standard Start of API savepoint
193 SAVEPOINT RUN_LEAD_ENGINES_PVT;
194
195 -- Standard call to check for call compatibility.
196 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
197 p_api_version_number,
198 l_api_name,
199 G_PKG_NAME)
200 THEN
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 END IF;
203
204 -- Initialize message list IF p_init_msg_list is set to TRUE.
205 IF FND_API.to_Boolean( p_init_msg_list )
206 THEN
207 FND_MSG_PUB.initialize;
208 END IF;
209
210 -- Debug Message
211 IF (AS_DEBUG_LOW_ON) THEN
212 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
213 'PVT:' || l_api_name || ' Start');
214 END IF;
215
216 -- Initialize API return status to SUCCESS
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 --
220 -- Api body
221 --
222 -- ******************************************************************
223 -- Validate Environment
224 -- ******************************************************************
225
226 -- Initialize build sales team flag to 'N'
227 x_lead_engines_out_rec.sales_team_flag := 'N';
228
229 IF FND_GLOBAL.User_Id IS NULL
230 THEN
231 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
232 THEN
233 AS_UTILITY_PVT.Set_Message(
234 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
235 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
236 p_token1 => 'PROFILE',
237 p_token1_value => 'USER_ID');
238 END IF;
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241
242 IF (p_validation_level = fnd_api.g_valid_level_full)
243 THEN
244 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
245 p_api_version_number => 2.0
246 ,p_init_msg_list => p_init_msg_list
247 ,p_salesforce_id => P_Identity_Salesforce_Id
248 ,p_admin_group_id => p_admin_group_id
249 ,x_return_status => x_return_status
250 ,x_msg_count => x_msg_count
251 ,x_msg_data => x_msg_data
252 ,x_sales_member_rec => l_identity_sales_member_rec);
253
254 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257 END IF;
258
259 OPEN C_Get_Lead_Info(p_Sales_Lead_Id);
260 FETCH C_Get_Lead_Info INTO
261 l_sales_lead_rec.customer_id, l_sales_lead_rec.address_id,
262 l_sales_lead_rec.assign_to_salesforce_id,
263 l_sales_lead_rec.assign_to_person_id,
264 l_sales_lead_rec.assign_sales_group_id,
265 l_sales_lead_rec.qualified_flag, l_sales_lead_rec.parent_project,
266 l_sales_lead_rec.channel_code,
267 l_sales_lead_rec.decision_timeframe_code,
268 l_sales_lead_rec.budget_amount, l_sales_lead_rec.budget_status_code,
269 l_sales_lead_rec.source_promotion_id, l_sales_lead_rec.status_code,
270 l_sales_lead_rec.reject_reason_code, l_sales_lead_rec.lead_rank_id;
271 CLOSE C_Get_Lead_Info;
272
273 -- Bug 2740032
274 -- Before 11.5.9,
275 -- 1. If profile AS_RUN_NEW_LEAD_ENGINES='Y', run Qualify_Lead,
276 -- Rate_Select_Lead, otherwise, run
277 -- AS_SALES_LEADS_PVT.IS_LEAD_QUALIFIED, AS_SCORECARD_PUB.Get_Score
278 -- 2. Call qualification engine only when profile AS_AUTO_QUALIFY='Y'
279 -- 3. Call ranking engine or rating engine only when profile
280 -- AS_RANK_LEAD_OPTION='SYSTEM'
281 --
282 -- The above profiles are obsoleted in 11.5.9
283
284 IF (AS_DEBUG_LOW_ON) THEN
285 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
286 'Chinar qualified_flag=' || l_sales_lead_rec.qualified_flag);
287 END IF;
288
289 IF l_sales_lead_rec.qualified_flag = 'N'
290 THEN
291 -- new qualification engine
292 -- ckapoor Disqualification project - merged procedure for qual/rating/channel sel.
293
294 --Qualify_Lead(
295 -- P_Api_Version_Number => 2.0,
296 -- P_Init_Msg_List => FND_API.G_FALSE,
297 -- P_Commit => FND_API.G_FALSE,
298 -- P_Validation_Level => P_Validation_Level,
299 -- P_Admin_Group_Id => P_Admin_Group_Id,
300 -- P_identity_salesforce_id => P_identity_salesforce_id,
301 -- P_Sales_Lead_id => P_Sales_Lead_id,
302 --X_Qualified_Flag =>
303 -- l_sales_lead_rec.qualified_flag,
304 --X_Return_Status => x_return_status,
305 --X_Msg_Count => x_msg_count,
306 --X_Msg_Data => x_msg_data); */
307
308 Rate_Select_Lead(
309 P_Api_Version_Number => 2.0,
310 P_Init_Msg_List => FND_API.G_FALSE,
311 P_Commit => FND_API.G_FALSE,
312 P_Validation_Level => P_Validation_Level,
313 P_Admin_Group_Id => P_Admin_Group_Id,
314 P_identity_salesforce_id => P_identity_salesforce_id,
315 P_Sales_Lead_id => P_Sales_Lead_id,
316 P_Process_Type => G_LEAD_QUALIFICATION,
317 -- ckapoor Phase 2 filtering project 11.5.10
318 -- P_Is_Create_Mode => P_Is_Create_Mode,
319
320 X_Action_Value => l_sales_lead_rec.qualified_flag,
321 -- l_action_value,
322 X_Return_Status => x_return_status,
323 X_Msg_Count => x_msg_count,
324 X_Msg_Data => x_msg_data);
325
326
327
328 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331
332 IF (AS_DEBUG_LOW_ON) THEN
333 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
334 'x_qual_flag=' || l_sales_lead_rec.qualified_flag);
335 END IF;
336 x_lead_engines_out_rec.qualified_flag :=
337 l_sales_lead_rec.qualified_flag;
338
339 IF l_sales_lead_rec.qualified_flag = 'N'
340 THEN
341 IF l_sales_lead_rec.channel_code =
342 FND_PROFILE.Value('AS_LEAD_INCUBATION_CHANNEL')
343 THEN
344 x_lead_engines_out_rec.sales_team_flag := 'N';
345 ELSE
346 x_lead_engines_out_rec.sales_team_flag := 'Y';
347 END IF;
348
349
350 -- RETURN; -- ckapoor - need to channel unqualified leads
351
352 END IF; -- l_sales_lead_rec.qualified_flag = 'N'
353 ELSE
354 x_lead_engines_out_rec.qualified_flag :=
355 l_sales_lead_rec.qualified_flag;
356 END IF; -- run qualification engine
357
358 IF (AS_DEBUG_LOW_ON) THEN
359 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
360 'Chinar lead_rank_id=' || l_sales_lead_rec.lead_rank_id);
361 END IF;
362
363 -- ckapoor. Disqualification project - give a default rating from profile
364 -- for unqualified leads
365
366 IF (l_sales_lead_rec.qualified_flag = 'N') -- anyways we are sure this has passed thru qual engine if this was manual case
367 -- so this is definitely the value after passing thru qualification engine
368 THEN
369
370 IF (AS_DEBUG_LOW_ON) THEN
371 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
372 'Chinar Within qualified flag is N condition');
373 END IF;
374 /*
375 -- check manually no rank is passed.
376 IF l_sales_lead_rec.lead_rank_id IS NULL OR
377 l_sales_lead_rec.lead_rank_id = FND_API.G_MISS_NUM
378 THEN
379 IF (AS_DEBUG_LOW_ON) THEN
380 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
381 'Chinar giving default' || FND_PROFILE.Value('AS_DEF_RATING_UNQUAL_LEADS'));
382 END IF;
383 l_sales_lead_rec.lead_rank_id := FND_PROFILE.Value('AS_DEF_RATING_UNQUAL_LEADS');
384 x_lead_engines_out_rec.lead_rank_id :=
385 l_sales_lead_rec.lead_rank_id;
386 -- ckapoor Disqualification project call sales lead table handler to save this value of lead_rank_id
387
388 AS_SALES_LEADS_LOG_PKG.Insert_Row(
389 px_log_id => l_sales_lead_log_id ,
390 p_sales_lead_id => p_sales_lead_id,
391 p_created_by => fnd_global.user_id,
392 p_creation_date => SYSDATE,
393 p_last_updated_by => fnd_global.user_id,
394 p_last_update_date => SYSDATE,
395 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
396 p_request_id =>
397 FND_GLOBAL.Conc_Request_Id,
398 p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
399 p_program_id =>
400 FND_GLOBAL.Conc_Program_Id,
401 p_program_update_date => SYSDATE,
402 p_status_code => l_sales_lead_rec.status_code,
403 p_assign_to_person_id => l_sales_lead_rec.assign_to_person_id,
404 p_assign_to_salesforce_id => l_sales_lead_rec.assign_to_salesforce_id,
405 p_reject_reason_code => l_sales_lead_rec.reject_reason_code,
406 p_assign_sales_group_id => l_sales_lead_rec.assign_sales_group_id,
407 p_lead_rank_id => l_sales_lead_rec.lead_rank_id,
408 p_qualified_flag => l_sales_lead_rec.qualified_flag,
409 p_category => NULL,
410 p_manual_rank_flag => 'N');
411
412 UPDATE as_sales_leads
413 SET lead_rank_id = l_sales_lead_rec.lead_rank_id,
414 lead_rank_ind = 'N'
415 WHERE sales_lead_id = p_sales_lead_id;
416
417
418 END IF;
419
420 */
421 ELSIF -- i.e if lead is qualified already, u want to go thru rating engine
422 -- ckapoor - changed IF TO ELSIF
423
424 l_sales_lead_rec.lead_rank_id IS NULL OR
425 l_sales_lead_rec.lead_rank_id = FND_API.G_MISS_NUM
426 THEN
427 -- new rating engine
428 Rate_Select_Lead(
429 P_Api_Version_Number => 2.0,
430 P_Init_Msg_List => FND_API.G_FALSE,
431 P_Commit => FND_API.G_FALSE,
432 P_Validation_Level => P_Validation_Level,
433 P_Admin_Group_Id => P_Admin_Group_Id,
434 P_identity_salesforce_id => P_identity_salesforce_id,
435 P_Sales_Lead_id => P_Sales_Lead_id,
436 P_Process_Type => G_LEAD_RATING,
437 -- ckapoor Phase 2 filtering project 11.5.10
438 -- P_Is_Create_Mode => P_Is_Create_Mode,
439
440 X_Action_Value => l_action_value,
441 X_Return_Status => x_return_status,
442 X_Msg_Count => x_msg_count,
443 X_Msg_Data => x_msg_data);
444
445 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448 IF (AS_DEBUG_LOW_ON) THEN
449 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
450 'a_value=' || l_action_value);
451 END IF;
452
453 l_sales_lead_rec.lead_rank_id := TO_NUMBER(l_action_value);
454 x_lead_engines_out_rec.lead_rank_id :=
455 l_sales_lead_rec.lead_rank_id;
456 ELSE -- if qualified and manually lead_rank_id is passed in
457 x_lead_engines_out_rec.lead_rank_id :=
458 l_sales_lead_rec.lead_rank_id;
459 END IF; -- run rating engine -- this now applies to IF THEN ELSE condition ckapoor
460
461 IF (AS_DEBUG_LOW_ON) THEN
462 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
463 'channel_code=' || l_sales_lead_rec.channel_code);
464 END IF;
465
466 -- ckapoor : since the return has been removed earlier, the code will
467 -- fall through and we will run channel selection engine for all leads
468
469 IF(l_sales_lead_rec.channel_code IS NULL OR
470 l_sales_lead_rec.channel_code = FND_API.G_MISS_CHAR
471 -- ckapoor : making change for solin : We should make it same as rating.
472 -- Do not have special handling for channel in 11.5.10
473 --OR
474 --l_sales_lead_rec.channel_code = 'OTHER' OR
475 --l_sales_lead_rec.channel_code = 'Other'
476 -- end ckapoor making change for solin
477 )
478 THEN
479 -- new channel selection engine
480 Rate_Select_Lead(
481 P_Api_Version_Number => 2.0,
482 P_Init_Msg_List => FND_API.G_FALSE,
483 P_Commit => FND_API.G_FALSE,
484 P_Validation_Level => P_Validation_Level,
485 P_Admin_Group_Id => P_Admin_Group_Id,
486 P_identity_salesforce_id => P_identity_salesforce_id,
487 P_Sales_Lead_id => P_Sales_Lead_id,
488 P_Process_Type => G_CHANNEL_SELECTION,
489 -- ckapoor Phase 2 filtering project 11.5.10
490 -- P_Is_Create_Mode => P_Is_Create_Mode,
491
492 X_Action_Value => l_sales_lead_rec.channel_code,
493 X_Return_Status => x_return_status,
494 X_Msg_Count => x_msg_count,
495 X_Msg_Data => x_msg_data);
496
497 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
498 RAISE FND_API.G_EXC_ERROR;
499 END IF;
500 IF (AS_DEBUG_LOW_ON) THEN
501 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
502 'a_value=' || l_sales_lead_rec.channel_code);
503 END IF;
504
505 x_lead_engines_out_rec.channel_code :=
506 l_sales_lead_rec.channel_code;
507 ELSE
508 x_lead_engines_out_rec.channel_code :=
509 l_sales_lead_rec.channel_code;
510 END IF; -- run channel selection engine
511
512 OPEN c_get_indirect_channel_flag(l_sales_lead_rec.channel_code);
513 FETCH c_get_indirect_channel_flag INTO
514 x_lead_engines_out_rec.indirect_channel_flag;
515 CLOSE c_get_indirect_channel_flag;
516
517 IF x_lead_engines_out_rec.indirect_channel_flag IS NULL
518 THEN
519 IF (AS_DEBUG_LOW_ON) THEN
520 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
521 l_sales_lead_rec.channel_code
522 || ' not defined in Channel Types');
523 END IF;
524 x_lead_engines_out_rec.indirect_channel_flag := 'N';
525 END IF;
526
527 IF l_sales_lead_rec.channel_code =
528 FND_PROFILE.Value('AS_LEAD_INCUBATION_CHANNEL')
529 THEN
530 x_lead_engines_out_rec.sales_team_flag := 'N';
531 ELSE
532 x_lead_engines_out_rec.sales_team_flag := 'Y';
533 END IF;
534
535 --
536 -- END of API body
537 --
538
539 -- Standard check for p_commit
540 IF FND_API.to_Boolean( p_commit )
541 THEN
542 COMMIT WORK;
543 END IF;
544
545 -- Debug Message
546 IF (AS_DEBUG_LOW_ON) THEN
547 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
548 'PVT: ' || l_api_name || ' End');
549 END IF;
550
551 -- Standard call to get message count and IF count is 1, get message info.
552 FND_MSG_PUB.Count_And_Get
553 ( p_count => x_msg_count,
554 p_data => x_msg_data );
555
556 EXCEPTION
557 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
558 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
559
560 WHEN FND_API.G_EXC_ERROR THEN
561 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
562 P_API_NAME => L_API_NAME
563 ,P_PKG_NAME => G_PKG_NAME
564 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
565 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
566 ,X_MSG_COUNT => X_MSG_COUNT
567 ,X_MSG_DATA => X_MSG_DATA
568 ,X_RETURN_STATUS => X_RETURN_STATUS);
569
570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
572 P_API_NAME => L_API_NAME
573 ,P_PKG_NAME => G_PKG_NAME
574 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
575 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
576 ,X_MSG_COUNT => X_MSG_COUNT
577 ,X_MSG_DATA => X_MSG_DATA
578 ,X_RETURN_STATUS => X_RETURN_STATUS);
579
580 WHEN OTHERS THEN
581 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
582 P_API_NAME => L_API_NAME
583 ,P_PKG_NAME => G_PKG_NAME
584 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
585 ,P_SQLCODE => SQLCODE
586 ,P_SQLERRM => SQLERRM
587 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
588 ,X_MSG_COUNT => X_MSG_COUNT
589 ,X_MSG_DATA => X_MSG_DATA
590 ,X_RETURN_STATUS => X_RETURN_STATUS);
591 END Run_Lead_Engines;
592
593
594 -- ckapoor : Disqualification project
595 -- In 11.5.10 the following procedure will also support qualification in addition to
596 -- Rating and channel selection. This is being done since now qualification engine will be
597 -- very similar to rating/channel selection in that there can be multiple rules per rule set
598 -- and the outcome can be user set etc (qualified v/s unqualified). Hence we are merging the
599 -- qualify_lead with rate_select_lead. Rate_Select_Lead will support this additional P_process_type
600
601
602
603 PROCEDURE Rate_Select_Lead(
604 P_Api_Version_Number IN NUMBER,
605 P_Init_Msg_List IN VARCHAR2,
606 P_Commit IN VARCHAR2,
607 P_Validation_Level IN NUMBER,
608 P_Admin_Group_Id IN NUMBER,
609 P_identity_salesforce_id IN NUMBER,
610 P_Sales_Lead_id IN NUMBER,
611 P_Process_Type IN VARCHAR2,
612 -- ckapoor Phase 2 filtering project 11.5.10
613 -- P_Is_Create_Mode IN VARCHAR2,
614 X_Action_Value OUT NOCOPY VARCHAR2,
615 X_Return_Status OUT NOCOPY VARCHAR2,
616 X_Msg_Count OUT NOCOPY NUMBER,
617 X_Msg_Data OUT NOCOPY VARCHAR2
618 )
619 IS
620
621 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
622 SELECT SL.CUSTOMER_ID,
623 SL.ADDRESS_ID,
624 SL.SOURCE_PROMOTION_ID,
625 TO_CHAR(SL.CREATION_DATE, 'YYYYMMDD') || '000000',
626 SL.STATUS_CODE,
627 SL.ASSIGN_TO_PERSON_ID,
628 SL.ASSIGN_TO_SALESFORCE_ID,
629 SL.REJECT_REASON_CODE,
630 SL.ASSIGN_SALES_GROUP_ID,
631 SL.QUALIFIED_FLAG,
632 -- ckapoor changed
633 SL.LEAD_RANK_ID,
634 PARTY.CATEGORY_CODE,
635 CNT.EMAIL_ADDRESS
636 -- ckapoor Phase 2 Filtering 11.5.10. Find the mode of caller
637 -- , SL.IMPORT_FLAG
638
639 FROM AS_SALES_LEADS SL, HZ_PARTIES PARTY, HZ_CONTACT_POINTS CNT
640 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
641 AND SL.CUSTOMER_ID = PARTY.PARTY_ID
642 AND SL.PRIMARY_CNT_PERSON_PARTY_ID = CNT.OWNER_TABLE_ID(+)
643 AND CNT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
644 AND CNT.CONTACT_POINT_TYPE(+) = 'EMAIL'
645 AND CNT.STATUS(+) = 'A';
646
647 -- CURSOR C_Get_Area_Code(C_Customer_Id NUMBER, C_Address_Id NUMBER) IS
648
649 CURSOR C_Get_Area_Code(C_Sales_Lead_ID NUMBER) IS
650
651 select phone_area_code from HZ_CONTACT_POINTS phone,
652 as_sales_leads lead , hz_parties party
653 where
654 lead.sales_lead_id = C_Sales_Lead_ID
655 and ((lead.customer_id = party.party_id and party.party_type = 'PERSON'
656 and PHONE.OWNER_TABLE_NAME='HZ_PARTIES' and PHONE.OWNER_TABLE_ID=lead.customer_id
657 and PHONE.PRIMARY_FLAG ='Y' and PHONE.CONTACT_POINT_TYPE='PHONE' )
658 or (PHONE.OWNER_TABLE_NAME='HZ_PARTIES' and PHONE.OWNER_TABLE_ID=lead.primary_contact_party_id
659 and PHONE.PRIMARY_FLAG ='Y' and PHONE.CONTACT_POINT_TYPE='PHONE'
660 and lead.primary_contact_party_id = party.party_id
661 and party.party_type = 'PARTY_RELATIONSHIP'));
662
663
664 -- ckapoor 11.5.10 Change cursor sql to match sql_text for Area Code (changed in 11.5.10)
665 /*
666 SELECT phon.phone_area_code
667 FROM hz_contact_points phon
668 WHERE phon.owner_table_id = c_address_id
669 AND phon.owner_table_name = 'HZ_PARTY_SITES'
670 AND phon.contact_point_type = 'PHONE'
671 AND phon.status in ('A','I')
672 UNION ALL
673 SELECT phon.phone_area_code
674 FROM hz_contact_points phon
675 WHERE c_address_id IS NULL
676 AND phon.owner_table_id = c_customer_id
677 AND phon.owner_table_name = 'HZ_PARTIES'
678 AND phon.contact_point_type = 'PHONE'
679 AND phon.status in ('A','I'); */
680
681 CURSOR C_Get_Location(C_Address_Id NUMBER) IS
682 SELECT LOC.COUNTRY, LOC.STATE, LOC.PROVINCE, LOC.COUNTY,
683 LOC.CITY, LOC.POSTAL_CODE
684 FROM HZ_PARTY_SITES SITE, HZ_LOCATIONS LOC
685 WHERE SITE.PARTY_SITE_ID = c_address_id
686 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
687
688
689 -- ckapoor Campaign setup type project
690 Cursor C_Get_Campaign_Setup_Type(c_sales_lead_id NUMBER) IS
691 select sc.custom_setup_id from ams_p_source_codes_v sc, as_sales_leads sl
692 where
693 sl.sales_lead_id = c_sales_lead_id and sl.source_promotion_id = sc.source_code_id;
694
695
696 /* select v.custom_setup_id from as_sales_leads sl,
697 (
698 select cs.custom_setup_id custom_setup_id, sc.source_code_id
699 from ams_campaign_schedules_vl cs, ams_source_codes sc , ams_custom_setups_vl csv
700 where cs.source_code = sc.source_code and csv.object_type = 'CSCH' and csv.custom_setup_id = cs.custom_setup_id
701 and sc.arc_source_code_for = 'CSCH'
702 union
703 select eo.setup_type_id custom_setup_id, sc.source_code_id
704 from ams_event_offers_vl eo,ams_source_codes sc , ams_custom_setups_vl csv
705 where eo.source_code = sc.source_code and csv.object_type in ('EVEO', 'EONE') and csv.custom_setup_id = eo.setup_type_id
706 and sc.arc_source_code_for in ('EVEO','EONE')
707
708 union
709 select eh.setup_type_id custom_setup_id, sc.source_code_id
710 from ams_event_headers_vl eh,ams_source_codes sc , ams_custom_setups_vl csv
711 where eh.source_code = sc.source_code and csv.object_type in ('EVEH') and csv.custom_setup_id = eh.setup_type_id
712 and sc.arc_source_code_for = 'EVEH'
713
714
715 union
716 select ca.custom_setup_id custom_setup_id, sc.source_code_id
717 from ams_campaigns_vl ca,ams_source_codes sc, ams_custom_setups_vl csv
718 where ca.source_code = sc.source_code and csv.object_type in ('ECAM', 'COLL', 'DEAL', 'PARTNER', 'TRDP', 'EVCAM')
719 and csv.custom_setup_id = ca.custom_setup_id
720 and sc.arc_source_code_for = 'CAMP'
721
722 ) v
723 where
724 v.source_code_id = sl.source_promotion_id and sl.sales_lead_id = c_sales_lead_id
725 ; */
726
727
728
729 -- end ckapoor
730
731 CURSOR C_Get_Matching_Rules(c_sales_lead_id NUMBER,
732 c_process_type VARCHAR2,
733 c_country VARCHAR2,
734 c_source_promotion_id NUMBER,
735 -- ckapoor Campaign setup type
736 c_custom_setup_id NUMBER,
737 c_status_code VARCHAR2,
738 c_creation_date VARCHAR2,
739 c_email_address VARCHAR2,
740 c_area_code VARCHAR2,
741 c_state VARCHAR2,
742 c_province VARCHAR2,
743 c_county VARCHAR2,
744 c_city VARCHAR2,
745 c_postal_code VARCHAR2,
746 c_category_code VARCHAR2) IS
747 SELECT rule.process_rule_id, rule.rank, rule.currency_code
748 FROM (
749 -- -------------------------------------------------------------------
750 -- Country
751 -- -------------------------------------------------------------------
752 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
753 FROM pv_process_rules_b a,
754 pv_enty_select_criteria b,
755 pv_selected_attr_values c
756 WHERE b.selection_type_code = 'INPUT_FILTER' AND
757 b.attribute_id = pv_check_match_pub.g_a_Country_ AND
758 a.process_type = c_process_type AND
759 a.process_rule_id = b.process_rule_id AND
760 b.selection_criteria_id = c.selection_criteria_id(+) AND
761 ((b.operator = 'EQUALS' AND c.attribute_value = c_country) OR
762 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_country) OR
763 (b.operator = 'IS_NOT_NULL' AND c_country IS NOT NULL) OR
764 (b.operator = 'IS_NULL' AND c_country IS NULL))
765 -- -------------------------------------------------------------------
766 -- Campaign
767 -- -------------------------------------------------------------------
768 UNION ALL
769 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
770 FROM pv_process_rules_b a,
771 pv_enty_select_criteria b,
772 pv_selected_attr_values c
773 WHERE b.selection_type_code = 'INPUT_FILTER' AND
774 b.attribute_id = pv_check_match_pub.g_a_Campaign_ AND
775 a.process_type = c_process_type AND
776 a.process_rule_id = b.process_rule_id AND
777 b.selection_criteria_id = c.selection_criteria_id(+) AND
778 ((b.operator = 'EQUALS' AND c.attribute_value = TO_CHAR(c_source_promotion_id)) OR
779 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> TO_CHAR(c_source_promotion_id)) OR
780 (b.operator = 'IS_NOT_NULL' AND c_source_promotion_id IS NOT NULL) OR
781 (b.operator = 'IS_NULL' AND c_source_promotion_id IS NULL))
782 -- ckapoor Custom setup type project
783
784 -- -------------------------------------------------------------------
785 -- Campaign setup type
786 -- -------------------------------------------------------------------
787 UNION ALL
788 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
789 FROM pv_process_rules_b a,
790 pv_enty_select_criteria b,
791 pv_selected_attr_values c
792 WHERE b.selection_type_code = 'INPUT_FILTER' AND
793 b.attribute_id =
794 --575
795 pv_check_match_pub.g_a_Campaign_Setup_Type
796 AND
797 a.process_type = c_process_type AND
798 a.process_rule_id = b.process_rule_id AND
799 b.selection_criteria_id = c.selection_criteria_id(+) AND
800 ((b.operator = 'EQUALS' AND c.attribute_value = TO_CHAR(c_custom_setup_id)) OR
801 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> TO_CHAR(c_custom_setup_id)) OR
802 (b.operator = 'IS_NOT_NULL' AND c_custom_setup_id IS NOT NULL) OR
803 (b.operator = 'IS_NULL' AND c_custom_setup_id IS NULL))
804
805
806 -- -------------------------------------------------------------------
807 -- Lead Status
808 -- -------------------------------------------------------------------
809 UNION ALL
810 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
811 FROM pv_process_rules_b a,
812 pv_enty_select_criteria b,
813 pv_selected_attr_values c
814 WHERE b.selection_type_code = 'INPUT_FILTER' AND
815 b.attribute_id = pv_check_match_pub.g_a_Lead_Status AND
816 a.process_type = c_process_type AND
817 a.process_rule_id = b.process_rule_id AND
818 b.selection_criteria_id = c.selection_criteria_id(+) AND
819 ((b.operator = 'EQUALS' AND c.attribute_value = c_status_code) OR
820 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_status_code) OR
821 (b.operator = 'IS_NOT_NULL' AND c_status_code IS NOT NULL) OR
822 (b.operator = 'IS_NULL' AND c_status_code IS NULL))
823 -- -------------------------------------------------------------------
824 -- Product Interest
825 -- -------------------------------------------------------------------
826 UNION ALL
827 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
828 FROM pv_process_rules_b a,
829 pv_enty_select_criteria b,
830 pv_selected_attr_values c,
831 as_sales_lead_lines asll
832 WHERE a.process_rule_id = b.process_rule_id AND
833 b.selection_criteria_id = c.selection_criteria_id(+) AND
834 b.attribute_id = pv_check_match_pub.g_a_Product_Interest AND
835 a.process_type = c_process_type AND
836 b.selection_type_code = 'INPUT_FILTER' AND
837 asll.sales_lead_id = c_sales_lead_id AND
838 ((b.operator = 'IS_NOT_NULL' AND asll.CATEGORY_ID IS NOT NULL) OR
839 (b.operator = 'EQUALS' AND TO_NUMBER(C.attribute_value) IN
840 (select category_id from eni_prod_den_hrchy_parents_v
841 where category_id in (
842 select category_parent_id from eni_denorm_hrchy_parents
843 start with category_id = ASLL.CATEGORY_ID
844 connect by prior category_parent_id = category_id
845 union all
846 select ASLL.CATEGORY_ID from dual)
847 and disable_date is null and
848 purchase_interest = 'Y' )))
849
850
851 -- -------------------------------------------------------------------
852 -- Date Created
853 -- -------------------------------------------------------------------
854 UNION ALL
855 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
856 FROM pv_process_rules_b a,
857 pv_enty_select_criteria b,
858 pv_selected_attr_values c
859 WHERE a.process_rule_id = b.process_rule_id AND
860 b.selection_type_code = 'INPUT_FILTER' AND
861 b.selection_criteria_id = c.selection_criteria_id(+) AND
862 b.attribute_id = pv_check_match_pub.g_a_Creation_Date AND
863 a.process_type = c_process_type AND
864 ((b.operator = 'EQUALS' AND c_creation_date = c.attribute_value) OR
865 (b.operator = 'NOT_EQUALS' AND c_creation_date <> c.attribute_value) OR
866 (b.operator = 'LESS_THAN' AND c_creation_date < c.attribute_value) OR
867 (b.operator = 'LESS_THAN_OR_EQUALS' AND c_creation_date <= c.attribute_value) OR
868 (b.operator = 'GREATER_THAN' AND c_creation_date > c.attribute_value) OR
869 (b.operator = 'GREATER_THAN_OR_EQUALS' AND c_creation_date >= c.attribute_value) OR
870 (b.operator = 'IS_NOT_NULL' AND c_creation_date IS NOT NULL) OR
871 (b.operator = 'IS_NULL' AND c_creation_date IS NULL) OR
872 (b.operator = 'BETWEEN' AND
873 (c_creation_date BETWEEN c.attribute_value AND
874 c.attribute_to_value)))
875 -- -------------------------------------------------------------------
876 -- Area Code
877 -- -------------------------------------------------------------------
878 UNION ALL
879 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
880 FROM pv_process_rules_b a,
881 pv_enty_select_criteria b,
882 pv_selected_attr_values c
883 WHERE b.selection_type_code = 'INPUT_FILTER' AND
884 b.attribute_id = pv_check_match_pub.g_a_Area_Code AND
885 a.process_type = c_process_type AND
886 a.process_rule_id = b.process_rule_id AND
887 b.selection_criteria_id = c.selection_criteria_id(+) AND
888 ((b.operator = 'EQUALS' AND c.attribute_value = c_area_code) OR
889 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_area_code) OR
890 (b.operator = 'IS_NOT_NULL' AND c_area_code IS NOT NULL) OR
891 (b.operator = 'IS_NULL' AND c_area_code IS NULL) OR
892 (b.operator = 'CONTAINS' AND upper(c_area_code) like upper('%'||c.attribute_value||'%')) OR
893 (b.operator = 'NOT_CONTAINS' AND upper(c_area_code) not like upper('%'||c.attribute_value||'%')) OR
894 (b.operator = 'BEGINS_WITH' AND upper(c_area_code) like upper(c.attribute_value||'%')) OR
895 (b.operator = 'ENDS_WITH' AND upper(c_area_code) like upper('%'||c.attribute_value)) OR
896 (b.operator = 'BETWEEN' AND upper(c_area_code) between upper(c.attribute_value) and upper(c.attribute_to_value))
897 )
898 -- -------------------------------------------------------------------
899 -- State
900 -- -------------------------------------------------------------------
901 UNION ALL
902 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
903 FROM pv_process_rules_b a,
904 pv_enty_select_criteria b,
905 pv_selected_attr_values c
906 WHERE b.selection_type_code = 'INPUT_FILTER' AND
907 b.attribute_id = pv_check_match_pub.g_a_State_ AND
908 a.process_type = c_process_type AND
909 a.process_rule_id = b.process_rule_id AND
910 b.selection_criteria_id = c.selection_criteria_id(+) AND
911 ((b.operator = 'EQUALS' AND c.attribute_value = c_state) OR
912 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_state) OR
913 (b.operator = 'IS_NOT_NULL' AND c_state IS NOT NULL) OR
914 (b.operator = 'IS_NULL' AND c_state IS NULL))
915 -- -------------------------------------------------------------------
916 -- Province
917 -- -------------------------------------------------------------------
918 UNION ALL
919 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
920 FROM pv_process_rules_b a,
921 pv_enty_select_criteria b,
922 pv_selected_attr_values c
923 WHERE b.selection_type_code = 'INPUT_FILTER' AND
924 b.attribute_id = pv_check_match_pub.g_a_Province AND
925 a.process_type = c_process_type AND
926 a.process_rule_id = b.process_rule_id AND
927 b.selection_criteria_id = c.selection_criteria_id(+) AND
928 ((b.operator = 'EQUALS' AND c.attribute_value = c_province) OR
929 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_province) OR
930 (b.operator = 'IS_NOT_NULL' AND c_province IS NOT NULL) OR
931 (b.operator = 'IS_NULL' AND c_province IS NULL) OR
932 (b.operator = 'CONTAINS' AND upper(c_province) like upper('%'||c.attribute_value||'%')) OR
933 (b.operator = 'NOT_CONTAINS' AND upper(c_province) not like upper('%'||c.attribute_value||'%')) OR
934 (b.operator = 'BEGINS_WITH' AND upper(c_province) like upper(c.attribute_value||'%')) OR
935 (b.operator = 'ENDS_WITH' AND upper(c_province) like upper('%'||c.attribute_value)) OR
936 (b.operator = 'BETWEEN' AND upper(c_province) between upper(c.attribute_value) and upper(c.attribute_to_value))
937 )
938 -- -------------------------------------------------------------------
939 -- County
940 -- -------------------------------------------------------------------
941 UNION ALL
942 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
943 FROM pv_process_rules_b a,
944 pv_enty_select_criteria b,
945 pv_selected_attr_values c
946 WHERE b.selection_type_code = 'INPUT_FILTER' AND
947 b.attribute_id = pv_check_match_pub.g_a_County AND
948 a.process_type = c_process_type AND
949 a.process_rule_id = b.process_rule_id AND
950 b.selection_criteria_id = c.selection_criteria_id(+) AND
951 ((b.operator = 'EQUALS' AND c.attribute_value = c_county) OR
952 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_county) OR
953 (b.operator = 'IS_NOT_NULL' AND c_county IS NOT NULL) OR
954 (b.operator = 'IS_NULL' AND c_county IS NULL) OR
955 (b.operator = 'CONTAINS' AND upper(c_county) like upper('%'||c.attribute_value||'%')) OR
956 (b.operator = 'NOT_CONTAINS' AND upper(c_county) not like upper('%'||c.attribute_value||'%')) OR
957 (b.operator = 'BEGINS_WITH' AND upper(c_county) like upper(c.attribute_value||'%')) OR
958 (b.operator = 'ENDS_WITH' AND upper(c_county) like upper('%'||c.attribute_value)) OR
959 (b.operator = 'BETWEEN' AND upper(c_county) between upper(c.attribute_value) and upper(c.attribute_to_value))
960 )
961 -- -------------------------------------------------------------------
962 -- City
963 -- -------------------------------------------------------------------
964 UNION ALL
965 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
966 FROM pv_process_rules_b a,
967 pv_enty_select_criteria b,
968 pv_selected_attr_values c
969 WHERE b.selection_type_code = 'INPUT_FILTER' AND
970 b.attribute_id = pv_check_match_pub.g_a_City AND
971 a.process_type = c_process_type AND
972 a.process_rule_id = b.process_rule_id AND
973 b.selection_criteria_id = c.selection_criteria_id(+) AND
974 ((b.operator = 'EQUALS' AND c.attribute_value = c_city) OR
975 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_city) OR
976 (b.operator = 'IS_NOT_NULL' AND c_city IS NOT NULL) OR
977 (b.operator = 'IS_NULL' AND c_city IS NULL) OR
978 (b.operator = 'CONTAINS' AND upper(c_city) like upper('%'||c.attribute_value||'%')) OR
979 (b.operator = 'NOT_CONTAINS' AND upper(c_city) not like upper('%'||c.attribute_value||'%')) OR
980 (b.operator = 'BEGINS_WITH' AND upper(c_city) like upper(c.attribute_value||'%')) OR
981 (b.operator = 'ENDS_WITH' AND upper(c_city) like upper('%'||c.attribute_value)) OR
982 (b.operator = 'BETWEEN' AND upper(c_city) between upper(c.attribute_value) and upper(c.attribute_to_value))
983 )
984 -- -------------------------------------------------------------------
985 -- Postal Code
986 -- -------------------------------------------------------------------
987 UNION ALL
988 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
989 FROM pv_process_rules_b a,
990 pv_enty_select_criteria b,
991 pv_selected_attr_values c
992 WHERE b.selection_type_code = 'INPUT_FILTER' AND
993 b.attribute_id = pv_check_match_pub.g_a_Postal_Code AND
994 a.process_type = c_process_type AND
995 a.process_rule_id = b.process_rule_id AND
996 b.selection_criteria_id = c.selection_criteria_id(+) AND
997 ((b.operator = 'EQUALS' AND c.attribute_value = c_postal_code) OR
998 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_postal_code) OR
999 (b.operator = 'IS_NOT_NULL' AND c_postal_code IS NOT NULL) OR
1000 (b.operator = 'IS_NULL' AND c_postal_code IS NULL) OR
1001 (b.operator = 'CONTAINS' AND upper(c_postal_code) like upper('%'||c.attribute_value||'%')) OR
1002 (b.operator = 'NOT_CONTAINS' AND upper(c_postal_code) not like upper('%'||c.attribute_value||'%')) OR
1003 (b.operator = 'BEGINS_WITH' AND upper(c_postal_code) like upper(c.attribute_value||'%')) OR
1004 (b.operator = 'ENDS_WITH' AND upper(c_postal_code) like upper('%'||c.attribute_value)) OR
1005 (b.operator = 'BETWEEN' AND upper(c_postal_code) between upper(c.attribute_value) and upper(c.attribute_to_value))
1006 )
1007 -- -------------------------------------------------------------------
1008 -- Customer Category
1009 -- -------------------------------------------------------------------
1010 UNION ALL
1011 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
1012 FROM pv_process_rules_b a,
1013 pv_enty_select_criteria b,
1014 pv_selected_attr_values c
1015 WHERE b.selection_type_code = 'INPUT_FILTER' AND
1016 b.attribute_id = pv_check_match_pub.g_a_Customer_Category AND
1017 a.process_type = c_process_type AND
1018 a.process_rule_id = b.process_rule_id AND
1019 b.selection_criteria_id = c.selection_criteria_id(+) AND
1020 ((b.operator = 'EQUALS' AND c.attribute_value = c_category_code) OR
1021 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_category_code) OR
1022 (b.operator = 'IS_NOT_NULL' AND c_category_code IS NOT NULL) OR
1023 (b.operator = 'IS_NULL' AND c_category_code IS NULL))
1024 -- ----------------------------------------------------------------
1025 -- All
1026 -------------------------------------------------------------------
1027 UNION ALL
1028 SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
1029 FROM pv_process_rules_b a,
1030 pv_enty_select_criteria b,
1031 pv_selected_attr_values c
1032 WHERE b.selection_type_code = 'INPUT_FILTER' AND
1033 b.attribute_id = pv_check_match_pub.g_a_all AND
1034 a.process_type = c_process_type AND
1035 a.process_rule_id = b.process_rule_id AND
1036 b.selection_criteria_id = c.selection_criteria_id AND
1037 b.operator = 'EQUALS' AND c.attribute_value = 'Y'
1038 ) rule
1039 GROUP BY rule.process_rule_id, rule.rank, rule.currency_code
1040 HAVING (rule.process_rule_id, COUNT(*)) IN (
1041 SELECT a.process_rule_id, COUNT(*)
1042 FROM pv_process_rules_b a,
1043 pv_enty_select_criteria b
1044 WHERE a.process_rule_id = b.process_rule_id AND
1045 b.selection_type_code = 'INPUT_FILTER' AND
1046 a.status_code = 'ACTIVE' AND
1047 a.process_type = c_process_type AND
1048 SYSDATE BETWEEN a.start_date AND a.end_date
1049 GROUP BY a.process_rule_id)
1050 ORDER BY rule.rank DESC;
1051
1052 -- Retrieve rating criteria for the rule
1053 CURSOR c_get_rating_criterion_rule(c_process_rule_id NUMBER) IS
1054 SELECT rule.process_rule_id, rule.action, rule.action_value,
1055 rank.min_score
1056 FROM pv_process_rules_b rule, as_sales_lead_ranks_b rank
1057 WHERE rule.parent_rule_id = c_process_rule_id
1058 AND rank.rank_id = TO_NUMBER(rule.action_value)
1059 ORDER BY rule.rank;
1060
1061 -- Retrieve channel selection criteria for the rule
1062 CURSOR c_get_channel_criterion_rule(c_process_rule_id NUMBER) IS
1063 SELECT rule.process_rule_id, rule.action, rule.action_value,
1064 NVL(channel.rank, 0)
1065 FROM pv_process_rules_b rule, pv_channel_types channel
1066 WHERE rule.parent_rule_id = c_process_rule_id
1067 AND channel.channel_lookup_code(+) = rule.action_value
1068 ORDER BY rule.rank;
1069
1070 -- ckapoor 11.5.10. Disqualification project. Use API for qualification
1071
1072 CURSOR c_get_qual_cri_rule(c_process_rule_id NUMBER) IS
1073 SELECT rule.process_rule_id, rule.action, rule.action_value ,
1074 decode(rule.action_value, 'Y', 1, 'N', 0)
1075 FROM pv_process_rules_b rule
1076 WHERE rule.parent_rule_id = c_process_rule_id
1077 ORDER BY rule.rank; -- just make sure 'Y' comes before 'N'
1078 -- end ckapoor
1079
1080 -- pv_selected_attr_values is outer joined because of IS_NULL and
1081 -- IS_NOT_NULL operator.
1082 CURSOR C_Get_Criterion_Attributes(c_process_rule_id NUMBER) IS
1083 SELECT cra.selection_criteria_id, cra.attribute_id, cra.operator,
1084 val.attribute_value, val.attribute_to_value
1085 FROM pv_enty_select_criteria cra, pv_selected_attr_values val
1086 WHERE cra.process_rule_id = c_process_rule_id
1087 AND cra.selection_type_code = 'CRITERION'
1088 AND cra.selection_criteria_id = val.selection_criteria_id(+)
1089 ORDER BY cra.selection_criteria_id;
1090
1091 -- Get rank score
1092 CURSOR c_get_rank_score(c_rank_id NUMBER) IS
1093 SELECT NVL(min_score, 0)
1094 FROM as_sales_lead_ranks_b
1095 WHERE rank_id = c_rank_id;
1096
1097 -- ckapoor 11.5.10 Winning rule logging project : Cursor to get all the attributes
1098 -- for the winning rule
1099
1100 CURSOR c_get_enty_select_criteria(c_winning_rule_id NUMBER) IS
1101 SELECT c.selection_criteria_id, c.attribute_id, c.selection_type_code, c.operator,
1102 b.return_type
1103 FROM pv_enty_select_criteria c, pv_attributes_b b
1104 WHERE c.process_rule_id = c_winning_rule_id
1105 and b.attribute_id = c.attribute_id;
1106
1107 -- Cursor to get the attribute value information for all attributes selected
1108 -- via the above cursor i.e c_get_enty_select_criteria(..)
1109
1110 CURSOR c_get_selected_attr_values(c_sel_cri_id NUMBER) IS
1111 SELECT attr_value_id, attribute_value, attribute_to_value
1112 FROM pv_selected_attr_values
1113 WHERE selection_criteria_id = c_sel_cri_id;
1114
1115
1116 -- end ckapoor 11.5.10 Winning rule logging project
1117
1118
1119
1120 l_api_name CONSTANT VARCHAR2(30) := 'Rate_Select_Lead';
1121 l_api_version_number CONSTANT NUMBER := 2.0;
1122
1123 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1124 l_customer_id NUMBER;
1125 l_address_id NUMBER;
1126 l_source_promotion_id NUMBER;
1127 l_creation_date VARCHAR2(30);
1128 l_country VARCHAR2(60);
1129 l_state VARCHAR2(60);
1130 l_province VARCHAR2(60);
1131 l_county VARCHAR2(60);
1132 l_city VARCHAR2(60);
1133 l_postal_code VARCHAR2(60);
1134 l_category_code VARCHAR2(30);
1135 l_status_code VARCHAR2(30);
1136 l_assign_to_person_id NUMBER;
1137 l_assign_to_salesforce_id NUMBER;
1138 l_reject_reason_code VARCHAR2(30);
1139 l_assign_sales_group_id NUMBER;
1140 l_qualified_flag VARCHAR2(1);
1141 -- ckapoor changed
1142 l_old_lead_rank_id NUMBER;
1143 -- ckapoor
1144 l_email_address VARCHAR2(2000);
1145 l_area_code VARCHAR2(10);
1146 l_delimiter CONSTANT VARCHAR2(3) := '+++';
1147 l_process_rule_id_tbl NUMBER_TABLE;
1148 l_criterion_rule_id_tbl NUMBER_TABLE;
1149 l_rank_tbl NUMBER_TABLE;
1150 l_action_tbl VARCHAR2_500_TABLE;
1151 l_action_value_tbl VARCHAR2_15_TABLE;
1152 l_currency_code_tbl VARCHAR2_15_TABLE;
1153 l_min_score_tbl NUMBER_TABLE;
1154 l_match_rule_flag VARCHAR2(1);
1155 l_match_attribute_flag BOOLEAN;
1156 l_criterion_attribute_exist BOOLEAN;
1157 l_rule_index NUMBER;
1158 l_criterion_rule_index NUMBER;
1159 l_matched_rule_index_tbl NUMBER_TABLE;
1160 l_attr_index NUMBER;
1161 l_min_score NUMBER := -1000; -- no socre is less than -1000
1162 l_input_filter_tbl PV_CHECK_MATCH_PUB.t_input_filter;
1163 l_rank NUMBER;
1164 l_prev_attribute_id NUMBER;
1165 l_prev_selection_criteria_id NUMBER;
1166 l_selection_criteria_id NUMBER;
1167 l_attribute_id NUMBER;
1168 l_operator VARCHAR2(30);
1169 l_prev_operator VARCHAR2(30);
1170 l_attr_value VARCHAR2(500);
1171 l_attr_to_value VARCHAR2(500);
1172 l_rule_attr_value VARCHAR2(1500);
1173 l_rule_attr_to_value VARCHAR2(1500);
1174 l_entity_attr_value_tbl PV_CHECK_MATCH_PUB.t_entity_attr_value;
1175 l_entity_rule_applied_id NUMBER;
1176 l_final_index NUMBER;
1177 l_final_cron_rule_id NUMBER;
1178 l_sales_lead_log_id NUMBER;
1179 l_action VARCHAR2(500);
1180 l_action_value VARCHAR2(15);
1181 l_fail_rule_selection_flag VARCHAR2(1) := FND_API.G_FALSE;
1182
1183 l_cursor NUMBER;
1184 l_rows_inserted NUMBER;
1185
1186 l_default_lead_rank_id NUMBER;
1187 l_default_channel_code VARCHAR2(30);
1188 l_lead_rank_id NUMBER;
1189 l_lead_rank_score NUMBER;
1190 l_default_qualified_flag VARCHAR2(1);
1191
1192
1193 -- ckapoor 11.5.10 Winning rule project
1194 l_winning_rule_ent_rule_app_id NUMBER;
1195
1196 l_enty_select_criteria_val c_get_enty_select_criteria%ROWTYPE;
1197 l_selected_attr_values_val c_get_selected_attr_values%ROWTYPE;
1198
1199 l_concat_attribute_value VARCHAR2(4000);
1200 l_concat_attribute_to_value VARCHAR2(4000);
1201
1202 l_rule_applied_attrs_id NUMBER; -- primary key for AML_RULE_APPLIED_ATTRS TABLE
1203
1204
1205 -- end ckapoor 11.5.10 Winning rule project
1206
1207
1208 -- ckapoor Campaign setup type project 11.5.10
1209
1210 l_custom_setup_id NUMBER;
1211 -- end ckapoor
1212
1213 -- ckapoor Phase 2 Filtering 11.5.10
1214 -- following value is the value u compare in the get rule sets cursor
1215 -- l_is_create_import_mode_val VARCHAR2(1):='N';
1216 -- l_import_flag VARCHAR2(1);
1217 -- l_filter_unqual_leads VARCHAR2(1);
1218
1219
1220
1221
1222 BEGIN
1223 -- Standard Start of API savepoint
1224 SAVEPOINT RATE_SELECT_LEAD_PVT;
1225
1226 -- Standard call to check for call compatibility.
1227 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1228 p_api_version_number,
1229 l_api_name,
1230 G_PKG_NAME)
1231 THEN
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234
1235 -- Initialize message list IF p_init_msg_list is set to TRUE.
1236 IF FND_API.to_Boolean( p_init_msg_list )
1237 THEN
1238 FND_MSG_PUB.initialize;
1239 END IF;
1240
1241 -- Debug Message
1242 IF (AS_DEBUG_LOW_ON) THEN
1243 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1244 'PVT:' || l_api_name || ' Start');
1245 END IF;
1246
1247 -- Initialize API return status to SUCCESS
1248 x_return_status := FND_API.G_RET_STS_SUCCESS;
1249
1250 --
1251 -- Api body
1252 --
1253
1254 -- ******************************************************************
1255 -- Validate Environment
1256 -- ******************************************************************
1257
1258 IF FND_GLOBAL.User_Id IS NULL
1259 THEN
1260 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1261 THEN
1262 AS_UTILITY_PVT.Set_Message(
1263 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1264 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1265 p_token1 => 'PROFILE',
1266 p_token1_value => 'USER_ID');
1267 END IF;
1268 RAISE FND_API.G_EXC_ERROR;
1269 END IF;
1270
1271 IF (p_validation_level = fnd_api.g_valid_level_full)
1272 THEN
1273 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1274 p_api_version_number => 2.0
1275 ,p_init_msg_list => p_init_msg_list
1276 ,p_salesforce_id => P_Identity_Salesforce_Id
1277 ,p_admin_group_id => p_admin_group_id
1278 ,x_return_status => x_return_status
1279 ,x_msg_count => x_msg_count
1280 ,x_msg_data => x_msg_data
1281 ,x_sales_member_rec => l_identity_sales_member_rec);
1282
1283 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1284 RAISE FND_API.G_EXC_ERROR;
1285 END IF;
1286 END IF;
1287
1288 -- Update latest_flag before rating/channel selection engine starts.
1289 UPDATE pv_entity_rules_applied
1290 SET latest_flag = 'N'
1291 WHERE entity = 'SALES_LEAD'
1292 AND entity_id = p_sales_lead_id
1293 AND process_type = p_process_type;
1294
1295 -- Get sales lead info.
1296 OPEN C_Get_Lead_Info(p_Sales_Lead_Id);
1297 FETCH C_Get_Lead_Info INTO
1298 l_customer_id, l_address_id, l_source_promotion_id,
1299 l_creation_date, l_status_code,
1300 l_assign_to_person_id, l_assign_to_salesforce_id,
1301 l_reject_reason_code, l_assign_sales_group_id, l_qualified_flag,
1302 -- ckapoor : for disqualification project
1303 l_old_lead_rank_id,
1304 -- end ckapoor
1305 l_category_code, l_email_address;
1306 -- ckapoor Phase 2 filtering 11.5.10
1307 --, l_import_flag;
1308
1309 CLOSE C_Get_Lead_Info;
1310
1311 --OPEN C_Get_Area_Code(l_customer_id, l_address_id);
1312 OPEN C_Get_Area_Code(p_Sales_Lead_Id);
1313
1314 FETCH C_Get_Area_Code INTO l_area_code;
1315 CLOSE C_Get_Area_Code;
1316
1317 IF l_address_id IS NOT NULL
1318 THEN
1319 OPEN C_Get_Location(l_address_id);
1320 FETCH C_Get_Location INTO l_country, l_state, l_province, l_county,
1321 l_city, l_postal_code;
1322 CLOSE C_Get_Location;
1323 ELSE
1324 l_country := NULL;
1325 END IF;
1326
1327 -- ckapoor Campaign setup type project
1328 -- ?? WHAT IF MULTIPLE RECORDS ARE OBTAINED????????
1329
1330 -- Get custom setup id
1331
1332 OPEN C_Get_Campaign_Setup_Type(p_Sales_Lead_Id);
1333 FETCH C_Get_Campaign_Setup_Type INTO
1334 l_custom_setup_id;
1335 CLOSE C_Get_Campaign_Setup_Type;
1336
1337
1338
1339 IF (AS_DEBUG_LOW_ON) THEN
1340 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1341 'Process_Type=' || p_process_type);
1342 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1343 'Sales_Lead_Id=' || p_sales_lead_id);
1344 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1345 'Source_promotion_id=' || l_source_promotion_id);
1346 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1347 'Status_code=' || l_status_code);
1348 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1349 'Creation_date=' || l_creation_date);
1350 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1351 'Country=' || l_country);
1352 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1353 'Email=' || l_email_address);
1354 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1355 'Area_Code=' || l_area_code);
1356 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1357 'State=' || l_state);
1358 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1359 'Province=' || l_province);
1360 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1361 'County=' || l_county);
1362 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1363 'City=' || l_city);
1364 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1365 'Postal_Code=' || l_postal_code);
1366 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1367 'Category=' || l_category_code);
1368 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1369 'Campaign Setup type id =' || l_custom_setup_id);
1370 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1371 -- 'Import flag =' || l_import_flag);
1372
1373 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1374 -- 'create flag =' || P_Is_Create_Mode);
1375
1376 END IF;
1377
1378
1379
1380 -- ckapoor Phase 2 Filtering 11.5.10
1381 -- Determine if it is import mode or not
1382
1383 -- IF ((P_Is_Create_Mode = 'Y') AND ( l_import_flag = 'Y')) THEN
1384
1385 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1386 -- 'Chinar import case' );
1387
1388 -- l_is_create_import_mode_val := 'Y';
1389 -- END IF;
1390
1391 -- if l_is_create_import_mode_val is 'N' then it cud be any of the other 2 cases create (other) and update
1392
1393
1394
1395 OPEN C_Get_Matching_Rules(p_sales_lead_id, p_process_type,
1396 l_country, l_source_promotion_id,
1397 --ckapoor Campaign setup type
1398 l_custom_setup_id,
1399 l_status_code, l_creation_date,
1400 l_email_address, l_area_code, l_state,
1401 l_province, l_county, l_city, l_postal_code,
1402 l_category_code);
1403 FETCH C_Get_Matching_Rules BULK COLLECT INTO l_process_rule_id_tbl,
1404 l_rank_tbl, l_currency_code_tbl;
1405 -- l_action_tbl, l_action_value_tbl
1406 CLOSE C_Get_Matching_Rules;
1407
1408 IF (AS_DEBUG_LOW_ON) THEN
1409 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1410 'rule count=' || l_process_rule_id_tbl.count);
1411
1412 END IF;
1413 l_match_rule_flag := 'N';
1414 -- If there's any rule matching, check attribute one by one
1415 IF l_process_rule_id_tbl.count > 0
1416 THEN
1417 l_rule_index := l_process_rule_id_tbl.first;
1418 l_rank := l_rank_tbl(l_rule_index);
1419 WHILE l_rule_index <= l_process_rule_id_tbl.last
1420 LOOP
1421 IF (AS_DEBUG_LOW_ON) THEN
1422 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1423 'pro_rule_id=' || l_process_rule_id_tbl(l_rule_index));
1424 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1425 'rank=' || l_rank_tbl(l_rule_index));
1426 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1427 'l_match_rule_flag=' || l_match_rule_flag);
1428 END IF;
1429
1430 IF l_rank_tbl(l_rule_index) <> l_rank AND
1431 l_match_rule_flag = 'Y'
1432 THEN
1433 IF (AS_DEBUG_LOW_ON) THEN
1434 AS_UTILITY_PVT.Debug_Message(
1435 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1436 'not same precedence/rule found');
1437 END IF;
1438 EXIT;
1439 END IF;
1440
1441 pv_check_match_pub.Retrieve_Input_Filter (
1442 p_api_version_number => 1.0,
1443 p_init_msg_list => p_init_msg_list,
1444 p_commit => p_commit,
1445 p_validation_level => p_validation_level,
1446 p_process_rule_id =>
1447 l_process_rule_id_tbl(l_rule_index),
1448 p_delimiter => l_delimiter,
1449 x_input_filter => l_input_filter_tbl,
1450 x_return_status => x_return_status,
1451 x_msg_count => x_msg_count,
1452 x_msg_data => x_msg_data);
1453
1454 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1455 RAISE FND_API.G_EXC_ERROR;
1456 END IF;
1457
1458 IF (p_process_type = G_LEAD_RATING)
1459 THEN
1460 OPEN c_get_rating_criterion_rule(
1461 l_process_rule_id_tbl(l_rule_index));
1462 FETCH c_get_rating_criterion_rule BULK COLLECT INTO
1463 l_criterion_rule_id_tbl, l_action_tbl, l_action_value_tbl,
1464 l_min_score_tbl;
1465 CLOSE c_get_rating_criterion_rule;
1466 -- ckapoor Disqualification project in 11.5.10.
1467 -- Using this api for qualification
1468 -- as well.
1469
1470 ELSIF p_process_type = G_CHANNEL_SELECTION
1471 THEN
1472 -- Channel selection engine
1473 OPEN c_get_channel_criterion_rule(
1474 l_process_rule_id_tbl(l_rule_index));
1475 FETCH c_get_channel_criterion_rule BULK COLLECT INTO
1476 l_criterion_rule_id_tbl, l_action_tbl, l_action_value_tbl,
1477 l_min_score_tbl;
1478 CLOSE c_get_channel_criterion_rule;
1479
1480 -- NEEDS ????????????????????
1481 ELSIF p_process_type = G_LEAD_QUALIFICATION -- ckapoor Disqualification project
1482 THEN
1483 -- qualification engine
1484 -- this is the code for getting all rules within a rule set.
1485 OPEN c_get_qual_cri_rule(
1486 l_process_rule_id_tbl(l_rule_index));
1487 FETCH c_get_qual_cri_rule BULK COLLECT INTO
1488 l_criterion_rule_id_tbl, l_action_tbl, l_action_value_tbl,
1489 l_min_score_tbl;
1490
1491 CLOSE c_get_qual_cri_rule;
1492
1493 -- end ckapoor
1494
1495
1496 END IF;
1497
1498 IF (AS_DEBUG_LOW_ON) THEN
1499 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1500 'criterion rule count=' || l_criterion_rule_id_tbl.count);
1501
1502 END IF;
1503 IF l_criterion_rule_id_tbl.count > 0
1504 THEN
1505 l_criterion_rule_index := l_criterion_rule_id_tbl.first;
1506 WHILE l_criterion_rule_index <= l_criterion_rule_id_tbl.last
1507 LOOP
1508 IF (AS_DEBUG_LOW_ON) THEN
1509 AS_UTILITY_PVT.Debug_Message(
1510 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1511 'cri_rule_idx=' || l_criterion_rule_index);
1512 AS_UTILITY_PVT.Debug_Message(
1513 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1514 'cri_rule_id=' ||
1515 l_criterion_rule_id_tbl(l_criterion_rule_index));
1516 END IF;
1517
1518 l_prev_attribute_id := FND_API.G_MISS_NUM;
1519 l_prev_selection_criteria_id := FND_API.G_MISS_NUM;
1520 l_rule_attr_value := l_delimiter;
1521 l_rule_attr_to_value := l_delimiter;
1522 l_match_attribute_flag := TRUE;
1523 l_criterion_attribute_exist := FALSE;
1524 OPEN C_Get_Criterion_Attributes(
1525 l_criterion_rule_id_tbl(l_criterion_rule_index));
1526 LOOP
1527 FETCH C_Get_Criterion_Attributes INTO
1528 l_selection_criteria_id, l_attribute_id,
1529 l_operator, l_attr_value, l_attr_to_value;
1530 EXIT WHEN C_Get_Criterion_Attributes%NOTFOUND;
1531
1532 l_criterion_attribute_exist := TRUE;
1533 IF (AS_DEBUG_LOW_ON) THEN
1534 AS_UTILITY_PVT.Debug_Message(
1535 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1536 'sel_cra_id=' || l_selection_criteria_id);
1537 AS_UTILITY_PVT.Debug_Message(
1538 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1539 'attr_id=' || l_attribute_id);
1540 AS_UTILITY_PVT.Debug_Message(
1541 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1542 'op=' || l_operator);
1543 END IF;
1544 IF l_selection_criteria_id <>
1545 l_prev_selection_criteria_id
1546 THEN
1547 IF l_prev_attribute_id <> FND_API.G_MISS_NUM
1548 THEN
1549 IF (AS_DEBUG_LOW_ON) THEN
1550 AS_UTILITY_PVT.Debug_Message(
1551 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1552 'rul_val=' || l_rule_attr_value);
1553 AS_UTILITY_PVT.Debug_Message(
1554 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1555 'rul_to_val=' || l_rule_attr_to_value);
1556 AS_UTILITY_PVT.Debug_Message(
1557 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1558 'prev_attr_id=' || l_prev_attribute_id);
1559 AS_UTILITY_PVT.Debug_Message(
1560 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1561 'prev_op=' || l_prev_operator);
1562 END IF;
1563 l_match_attribute_flag :=
1564 pv_check_match_pub.Check_Match (
1565 p_attribute_id =>
1566 l_prev_attribute_id,
1567 p_entity => 'SALES_LEAD',
1568 p_entity_id => p_sales_lead_id,
1569 p_rule_attr_value =>
1570 l_rule_attr_value,
1571 p_rule_to_attr_value =>
1572 l_rule_attr_to_value,
1573 p_operator => l_prev_operator,
1574 p_input_filter =>
1575 l_input_filter_tbl,
1576 p_delimiter => l_delimiter,
1577 p_rule_currency_code =>
1578 l_currency_code_tbl(l_rule_index),
1579 x_entity_attr_value =>
1580 l_entity_attr_value_tbl);
1581
1582 IF l_match_attribute_flag = FALSE
1583 THEN
1584 IF (AS_DEBUG_LOW_ON) THEN
1585 AS_UTILITY_PVT.Debug_Message(
1586 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1587 'attr not match');
1588 END IF;
1589 EXIT; -- exit attribute loop
1590 ELSE
1591 IF (AS_DEBUG_LOW_ON) THEN
1592 AS_UTILITY_PVT.Debug_Message(
1593 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1594 'attr match');
1595 END IF;
1596 END IF;
1597 END IF; -- l_rule_attribute_value <> l_delimiter
1598 l_rule_attr_value := l_delimiter;
1599 l_rule_attr_to_value := l_delimiter;
1600 END IF; -- l_selection_criteria_id <>
1601 -- l_prev_selection_criteria_id
1602 l_rule_attr_value := l_rule_attr_value
1603 || l_attr_value || l_delimiter;
1604 l_rule_attr_to_value := l_rule_attr_to_value
1605 || l_attr_to_value || l_delimiter;
1606
1607 l_prev_selection_criteria_id :=
1608 l_selection_criteria_id;
1609 l_prev_attribute_id := l_attribute_id;
1610 l_prev_operator := l_operator;
1611 END LOOP; -- attribute/value
1612 CLOSE C_Get_Criterion_Attributes;
1613 IF l_match_attribute_flag AND l_criterion_attribute_exist
1614 THEN
1615 IF (AS_DEBUG_LOW_ON) THEN
1616 AS_UTILITY_PVT.Debug_Message(
1617 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1618 'l attr_id=' || l_prev_attribute_id);
1619 AS_UTILITY_PVT.Debug_Message(
1620 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1621 'rul_val=' || l_rule_attr_value);
1622 AS_UTILITY_PVT.Debug_Message(
1623 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1624 'rul_to_val=' || l_rule_attr_to_value);
1625 AS_UTILITY_PVT.Debug_Message(
1626 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1627 'prev_attr_id=' || l_prev_attribute_id);
1628 AS_UTILITY_PVT.Debug_Message(
1629 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1630 'prev_op=' || l_prev_operator);
1631 END IF;
1632 l_match_attribute_flag :=
1633 pv_check_match_pub.Check_Match (
1634 p_attribute_id => l_prev_attribute_id,
1635 p_entity => 'SALES_LEAD',
1636 p_entity_id => p_sales_lead_id,
1637 p_rule_attr_value => l_rule_attr_value,
1638 p_rule_to_attr_value => l_rule_attr_to_value,
1639 p_operator => l_prev_operator,
1640 p_input_filter => l_input_filter_tbl,
1641 p_delimiter => l_delimiter,
1642 p_rule_currency_code =>
1643 l_currency_code_tbl(l_rule_index),
1644 x_entity_attr_value =>
1645 l_entity_attr_value_tbl);
1646 END IF; -- l_match_attribute_flag = TRUE
1647
1648 IF l_match_attribute_flag AND l_criterion_attribute_exist
1649 THEN
1650 IF (AS_DEBUG_LOW_ON) THEN
1651 AS_UTILITY_PVT.Debug_Message(
1652 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1653 'cr_ru=' ||
1654 l_criterion_rule_id_tbl(l_criterion_rule_index));
1655 AS_UTILITY_PVT.Debug_Message(
1656 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1657 'l_min_score_tbl=' ||
1658 l_min_score_tbl(l_criterion_rule_index));
1659 AS_UTILITY_PVT.Debug_Message(
1660 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1661 'l_min_score=' || l_min_score);
1662 END IF;
1663 l_match_rule_flag := 'Y';
1664 l_matched_rule_index_tbl(l_rule_index) :=
1665 l_criterion_rule_index;
1666 l_entity_rule_applied_id := NULL;
1667 IF l_min_score_tbl(l_criterion_rule_index) >
1668 l_min_score
1669 THEN
1670 l_min_score :=
1671 l_min_score_tbl(l_criterion_rule_index);
1672 l_action := l_action_tbl(l_criterion_rule_index);
1673 l_action_value :=
1674 l_action_value_tbl(l_criterion_rule_index);
1675 l_final_index := l_rule_index;
1676 l_final_cron_rule_id :=
1677 l_criterion_rule_id_tbl(l_criterion_rule_index);
1678 IF (AS_DEBUG_LOW_ON) THEN
1679 AS_UTILITY_PVT.Debug_Message(
1680 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1681 'l_final_cron_rule_id=' ||
1682 l_final_cron_rule_id);
1683 END IF;
1684 END IF;
1685
1686 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
1687 px_ENTITY_RULE_APPLIED_ID =>
1688 l_entity_rule_applied_id
1689 ,p_LAST_UPDATE_DATE => SYSDATE
1690 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1691 ,p_CREATION_DATE => SYSDATE
1692 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1693 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
1694 ,p_OBJECT_VERSION_NUMBER => 1
1695 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
1696 ,p_PROGRAM_APPLICATION_ID =>
1697 FND_GLOBAL.Prog_Appl_Id
1698 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
1699 ,p_PROGRAM_UPDATE_DATE => SYSDATE
1700 ,p_ENTITY => 'SALES_LEAD'
1701 ,p_ENTITY_ID => p_sales_lead_id
1702 ,p_PROCESS_RULE_ID =>
1703 l_criterion_rule_id_tbl(l_criterion_rule_index)
1704 ,p_PARENT_PROCESS_RULE_ID =>
1705 l_process_rule_id_tbl(l_rule_index)
1706 ,p_LATEST_FLAG => 'Y'
1707 ,p_ACTION_VALUE =>
1708 l_action_value_tbl(l_criterion_rule_index)
1709 ,p_PROCESS_TYPE => p_process_type
1710 --,p_WINNING_RULE_FLAG => 'Y'
1711 ,p_WINNING_RULE_FLAG => 'N'
1712 ,p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR
1713 ,p_ATTRIBUTE1 => FND_API.G_MISS_CHAR
1714 ,p_ATTRIBUTE2 => FND_API.G_MISS_CHAR
1715 ,p_ATTRIBUTE3 => FND_API.G_MISS_CHAR
1716 ,p_ATTRIBUTE4 => FND_API.G_MISS_CHAR
1717 ,p_ATTRIBUTE5 => FND_API.G_MISS_CHAR
1718 ,p_ATTRIBUTE6 => FND_API.G_MISS_CHAR
1719 ,p_ATTRIBUTE7 => FND_API.G_MISS_CHAR
1720 ,p_ATTRIBUTE8 => FND_API.G_MISS_CHAR
1721 ,p_ATTRIBUTE9 => FND_API.G_MISS_CHAR
1722 ,p_ATTRIBUTE10 => FND_API.G_MISS_CHAR
1723 ,p_ATTRIBUTE11 => FND_API.G_MISS_CHAR
1724 ,p_ATTRIBUTE12 => FND_API.G_MISS_CHAR
1725 ,p_ATTRIBUTE13 => FND_API.G_MISS_CHAR
1726 ,p_ATTRIBUTE14 => FND_API.G_MISS_CHAR
1727 ,p_ATTRIBUTE15 => FND_API.G_MISS_CHAR
1728 ,p_PROCESS_STATUS => G_PASS_RULE
1729 ,p_ENTITY_DETAIL => l_status_code);
1730
1731 EXIT; -- exit criterion rule
1732 ELSE
1733 IF (AS_DEBUG_LOW_ON) THEN
1734 AS_UTILITY_PVT.Debug_Message(
1735 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1736 'rule not match');
1737 END IF;
1738 l_entity_rule_applied_id := NULL;
1739 l_matched_rule_index_tbl(l_rule_index) :=
1740 FND_API.G_MISS_NUM;
1741 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
1742 px_ENTITY_RULE_APPLIED_ID =>
1743 l_entity_rule_applied_id
1744 ,p_LAST_UPDATE_DATE => SYSDATE
1745 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1746 ,p_CREATION_DATE => SYSDATE
1747 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1748 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
1749 ,p_OBJECT_VERSION_NUMBER => 1
1750 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
1751 ,p_PROGRAM_APPLICATION_ID =>
1752 FND_GLOBAL.Prog_Appl_Id
1753 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
1754 ,p_PROGRAM_UPDATE_DATE => SYSDATE
1755 ,p_ENTITY => 'SALES_LEAD'
1756 ,p_ENTITY_ID => p_sales_lead_id
1757 ,p_PROCESS_RULE_ID =>
1758 l_criterion_rule_id_tbl(l_criterion_rule_index)
1759 ,p_PARENT_PROCESS_RULE_ID =>
1760 l_process_rule_id_tbl(l_rule_index)
1761 ,p_LATEST_FLAG => 'Y'
1762 ,p_ACTION_VALUE =>
1763 l_action_value_tbl(l_criterion_rule_index)
1764 ,p_PROCESS_TYPE => p_process_type
1765 ,p_WINNING_RULE_FLAG => NULL
1766 ,p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR
1767 ,p_ATTRIBUTE1 => FND_API.G_MISS_CHAR
1768 ,p_ATTRIBUTE2 => FND_API.G_MISS_CHAR
1769 ,p_ATTRIBUTE3 => FND_API.G_MISS_CHAR
1770 ,p_ATTRIBUTE4 => FND_API.G_MISS_CHAR
1771 ,p_ATTRIBUTE5 => FND_API.G_MISS_CHAR
1772 ,p_ATTRIBUTE6 => FND_API.G_MISS_CHAR
1773 ,p_ATTRIBUTE7 => FND_API.G_MISS_CHAR
1774 ,p_ATTRIBUTE8 => FND_API.G_MISS_CHAR
1775 ,p_ATTRIBUTE9 => FND_API.G_MISS_CHAR
1776 ,p_ATTRIBUTE10 => FND_API.G_MISS_CHAR
1777 ,p_ATTRIBUTE11 => FND_API.G_MISS_CHAR
1778 ,p_ATTRIBUTE12 => FND_API.G_MISS_CHAR
1779 ,p_ATTRIBUTE13 => FND_API.G_MISS_CHAR
1780 ,p_ATTRIBUTE14 => FND_API.G_MISS_CHAR
1781 ,p_ATTRIBUTE15 => FND_API.G_MISS_CHAR
1782 ,p_PROCESS_STATUS => G_FAIL_CRITERIA
1783 ,p_ENTITY_DETAIL => l_status_code);
1784 END IF;
1785 l_criterion_rule_index := l_criterion_rule_index + 1;
1786 END LOOP; -- criterion rule
1787 END IF; -- l_criterion_rule_id_tbl.count
1788
1789 IF NOT l_match_attribute_flag
1790 THEN
1791 IF (AS_DEBUG_LOW_ON) THEN
1792 AS_UTILITY_PVT.Debug_Message(
1793 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1794 'no criterion rule match');
1795 END IF;
1796 l_entity_rule_applied_id := NULL;
1797 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
1798 px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_id
1799 ,p_LAST_UPDATE_DATE => SYSDATE
1800 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1801 ,p_CREATION_DATE => SYSDATE
1802 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1803 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
1804 ,p_OBJECT_VERSION_NUMBER => 1
1805 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
1806 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
1807 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
1808 ,p_PROGRAM_UPDATE_DATE => SYSDATE
1809 ,p_ENTITY => 'SALES_LEAD'
1810 ,p_ENTITY_ID => p_sales_lead_id
1811 ,p_PROCESS_RULE_ID => NULL
1812 ,p_PARENT_PROCESS_RULE_ID =>
1813 l_process_rule_id_tbl(l_rule_index)
1814 ,p_LATEST_FLAG => 'Y'
1815 ,p_ACTION_VALUE => NULL
1816 ,p_PROCESS_TYPE => p_process_type
1817 ,p_WINNING_RULE_FLAG => NULL
1818 ,p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR
1819 ,p_ATTRIBUTE1 => FND_API.G_MISS_CHAR
1820 ,p_ATTRIBUTE2 => FND_API.G_MISS_CHAR
1821 ,p_ATTRIBUTE3 => FND_API.G_MISS_CHAR
1822 ,p_ATTRIBUTE4 => FND_API.G_MISS_CHAR
1823 ,p_ATTRIBUTE5 => FND_API.G_MISS_CHAR
1824 ,p_ATTRIBUTE6 => FND_API.G_MISS_CHAR
1825 ,p_ATTRIBUTE7 => FND_API.G_MISS_CHAR
1826 ,p_ATTRIBUTE8 => FND_API.G_MISS_CHAR
1827 ,p_ATTRIBUTE9 => FND_API.G_MISS_CHAR
1828 ,p_ATTRIBUTE10 => FND_API.G_MISS_CHAR
1829 ,p_ATTRIBUTE11 => FND_API.G_MISS_CHAR
1830 ,p_ATTRIBUTE12 => FND_API.G_MISS_CHAR
1831 ,p_ATTRIBUTE13 => FND_API.G_MISS_CHAR
1832 ,p_ATTRIBUTE14 => FND_API.G_MISS_CHAR
1833 ,p_ATTRIBUTE15 => FND_API.G_MISS_CHAR
1834 ,p_PROCESS_STATUS => G_FAIL_CRITERIA
1835 ,p_ENTITY_DETAIL => l_status_code);
1836 END IF;
1837 l_rank := l_rank_tbl(l_rule_index);
1838 l_rule_index := l_rule_index + 1;
1839 END LOOP; -- for each matched rule
1840 ELSE
1841 IF (AS_DEBUG_LOW_ON) THEN
1842 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1843 'no rule input filter match');
1844 END IF;
1845 END IF; -- l_process_rule_id_tbl.count > 0
1846
1847 IF (AS_DEBUG_LOW_ON) THEN
1848 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1849 'l_match_rule_flag:' || l_match_rule_flag);
1850 END IF;
1851 IF l_match_rule_flag = 'Y'
1852 THEN
1853 IF (AS_DEBUG_LOW_ON) THEN
1854 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1855 'l_final_index:' || l_final_index);
1856 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1857 'l_final_cron_rule_id:' || l_final_cron_rule_id);
1858 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1859 'final rule id:' || l_process_rule_id_tbl(l_final_index));
1860 END IF;
1861
1862
1863
1864
1865
1866 -- Update latest_flag before rating/channel selection engine starts.
1867 UPDATE pv_entity_rules_applied
1868 SET WINNING_RULE_FLAG = 'Y'
1869 WHERE entity = 'SALES_LEAD'
1870 AND entity_id = p_sales_lead_id
1871 AND process_type = p_process_type
1872 AND latest_flag = 'Y'
1873 AND process_rule_id = l_final_cron_rule_id
1874 AND parent_process_rule_id = l_process_rule_id_tbl(l_final_index)
1875
1876 returning entity_rule_applied_id into l_winning_rule_ent_rule_app_id ;
1877
1878
1879
1880
1881 -- ckapoor 11.5.10 filtering phase 2
1882
1883 -- if matched rule's action_value is N and lead_qual process type and create import mode
1884 -- and profile true then throw exception
1885 -- throw diff exception for unqualified case.
1886
1887 --if( l_rule_set_action_value_tbl(l_final_index) = 'F') then
1888
1889
1890 -- if (p_process_type = 'LEAD_QUALIFICATION') then
1891
1892 -- l_filter_unqual_leads := FND_PROFILE.value('AS_FILTER_UNQUALIFIED_LEADS');
1893
1894 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1895 -- 'filterprofile'||l_filter_unqual_leads);
1896
1897
1898 -- if ((l_filter_unqual_leads = 'Y') and (l_is_create_import_mode_val='Y') and (l_action_value = 'N')) -- then
1899 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1900 -- 'Match qual case');
1901
1902 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1903 -- 'Filtering error raised from API');
1904 -- raise AS_SALES_LEADS_PUB.Filter_Exception;
1905 -- end if;
1906 -- end if;
1907
1908
1909
1910
1911
1912 --ckapoor Code for logging winning rule value
1913 -- based on process_rule_id , write a cursor to get all attribs for the rule
1914
1915 -- TO ASK - what if there is no rows found..do i have to do NOTFOUND etc ??
1916
1917
1918 l_concat_attribute_value := NULL;
1919 l_concat_attribute_to_value := NULL;
1920
1921 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Just before the outerloop for winning rule value logging');
1922
1923 FOR l_enty_select_criteria_val in c_get_enty_select_criteria(l_final_cron_rule_id) LOOP
1924
1925 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Within the outerloop for winning rule value logging');
1926
1927 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'selection_criteria_id ::'||l_enty_select_criteria_val.selection_criteria_id);
1928
1929 l_concat_attribute_value := NULL;
1930 l_concat_attribute_to_value := NULL;
1931
1932 FOR l_selected_attr_values_val in c_get_selected_attr_values(l_enty_select_criteria_val.selection_criteria_id) LOOP
1933 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Within the innerloop for winning rule value logging');
1934 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'attr_value_id ::'||l_selected_attr_values_val.attr_value_id);
1935 --if (l_concat_attribute_value IS NULL) then
1936 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'first time');
1937 -- l_concat_attribute_value := l_selected_attr_values_val.attribute_value;
1938 --els
1939
1940 if (l_selected_attr_values_val.attribute_value IS NOT NULL) then
1941 l_concat_attribute_value := l_concat_attribute_value || l_delimiter || l_selected_attr_values_val.attribute_value;
1942
1943 if (l_enty_select_criteria_val.return_type = 'CURRENCY') then
1944 l_concat_attribute_value := l_concat_attribute_value || ':::' || l_currency_code_tbl(l_final_index);
1945 end if;
1946
1947 end if;
1948
1949 --next one is relevant only if it is a BETWEEN operator
1950 -- if (l_concat_attribute_value IS NULL) then
1951 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'again first time');
1952
1953 -- l_concat_attribute_to_value := l_selected_attr_values_val.attribute_to_value;
1954 -- els
1955
1956 if (l_selected_attr_values_val.attribute_to_value IS NOT NULL) then
1957 l_concat_attribute_to_value := l_concat_attribute_to_value || l_delimiter || l_selected_attr_values_val.attribute_to_value;
1958
1959 if (l_enty_select_criteria_val.return_type = 'CURRENCY') then
1960 l_concat_attribute_to_value := l_concat_attribute_to_value || ':::' || l_currency_code_tbl(l_final_index);
1961 end if;
1962 end if;
1963
1964 END LOOP; -- for l_selected_attr_values_val
1965
1966
1967
1968 if (l_concat_attribute_value IS NOT NULL) then
1969 l_concat_attribute_value := l_concat_attribute_value || l_delimiter;
1970 end if;
1971
1972 if (l_concat_attribute_to_value IS NOT NULL) then
1973 l_concat_attribute_to_value := l_concat_attribute_to_value || l_delimiter;
1974 end if;
1975
1976
1977
1978
1979 -- at the end of this loop, we have the right values constructed
1980
1981 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1982 'These values will be logged : ');
1983 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1984 'entity_rule_applied_id :: ' || l_winning_rule_ent_rule_app_id);
1985 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1986 'attribute_id ::' || l_enty_select_criteria_val.attribute_id);
1987 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1988 'operator :: ' || l_enty_select_criteria_val.operator);
1989 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1990 'a_v::' || l_concat_attribute_value);
1991
1992 Debug('a_v2::' || l_concat_attribute_value);
1993
1994
1995 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1996 'a_t_v::' || l_concat_attribute_to_value);
1997
1998 Debug('a_t_v2::' || l_concat_attribute_to_value);
1999
2000
2001 -- As we are looping through the attributes, we will refer to the
2002 -- l_entity_attr_value_tbl returned by pv_check_match_pub.check_match(,,)
2003 -- This contains the cached attribute value
2004
2005 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2006 'PV :: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
2007
2008 Debug('PV again:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
2009
2010
2011 -- One problem is that PV API returns the expanded concatenated value
2012 -- for FUE attribs (e.g # 510 i.e Prod Interest) We want the non expanded value
2013 -- so we have to call get_entity_attr_values(..) again.
2014
2015 if (l_enty_select_criteria_val.attribute_id = pv_check_match_pub.g_a_Product_Interest) then
2016
2017 l_entity_attr_value_tbl.delete(l_enty_select_criteria_val.attribute_id);
2018
2019 pv_check_match_pub.Get_Entity_Attr_Values (
2020 p_api_version_number => 1.0,
2021 p_attribute_id => l_enty_select_criteria_val.attribute_id,
2022 p_entity => 'SALES_LEAD',
2023 p_entity_id => p_sales_lead_id,
2024 p_delimiter => l_delimiter,
2025 p_expand_attr_flag => 'N',
2026 x_entity_attr_value => l_entity_attr_value_tbl,
2027 x_return_status => x_return_status,
2028 x_msg_count => x_msg_count,
2029 x_msg_data => x_msg_data
2030 );
2031
2032 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035
2036 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2037 'PV 510:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
2038
2039 Debug('PV again 510:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
2040
2041
2042 end if;
2043
2044
2045
2046 -- Now that the values are constructed, log these into aml_rule_applied_attrs
2047
2048
2049
2050 AML_RULE_APPLIED_ATTRS_PKG.Insert_Row(
2051 px_RULE_APPLIED_ATTR_ID => l_rule_applied_attrs_id
2052 ,p_LAST_UPDATE_DATE => SYSDATE
2053 ,p_LAST_UPDATED_BY => fnd_global.user_id
2054 ,p_CREATION_DATE => SYSDATE
2055 ,p_CREATED_BY => fnd_global.user_id
2056 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
2057 ,p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
2058 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
2059 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
2060 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
2061 ,p_PROGRAM_UPDATE_DATE => SYSDATE
2062 ,p_ENTITY_RULE_APPLIED_ID => l_winning_rule_ent_rule_app_id
2063 ,p_ATTRIBUTE_ID => l_enty_select_criteria_val.attribute_id
2064 ,p_OPERATOR => l_enty_select_criteria_val.operator
2065 ,p_ATTRIBUTE_VALUE => l_concat_attribute_value
2066 ,p_ATTRIBUTE_TO_VALUE => l_concat_attribute_to_value
2067 ,p_LEAD_VALUE => l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value
2068
2069 );
2070
2071
2072
2073
2074 END LOOP ; -- for l_enty_select_criteria_val
2075
2076
2077
2078
2079 -- for every attribute, goto pv_selected_attr_values and construct
2080 --concatenated string.
2081
2082 -- for every attribute , goto pv_entity)attrs using attribute_id, and entity combo
2083 --and get sql_text. Unique row
2084
2085 -- Use dynamic sql ?? to run the sql_text. This may return multiple records
2086
2087 -- if # > 1 then concatenate
2088
2089
2090 -- Confirm if the above comments are needed
2091
2092
2093
2094
2095 IF p_process_type = G_LEAD_RATING
2096 THEN
2097 AS_SALES_LEADS_LOG_PKG.Insert_Row(
2098 px_log_id => l_sales_lead_log_id ,
2099 p_sales_lead_id => p_sales_lead_id,
2100 p_created_by => fnd_global.user_id,
2101 p_creation_date => SYSDATE,
2102 p_last_updated_by => fnd_global.user_id,
2103 p_last_update_date => SYSDATE,
2104 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
2105 p_request_id =>
2106 FND_GLOBAL.Conc_Request_Id,
2107 p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
2108 p_program_id =>
2109 FND_GLOBAL.Conc_Program_Id,
2110 p_program_update_date => SYSDATE,
2111 p_status_code => l_status_code,
2112 p_assign_to_person_id => l_assign_to_person_id,
2113 p_assign_to_salesforce_id => l_assign_to_salesforce_id,
2114 p_reject_reason_code => l_reject_reason_code,
2115 p_assign_sales_group_id => l_assign_sales_group_id,
2116 p_lead_rank_id => TO_NUMBER(l_action_value),
2117 p_qualified_flag => l_qualified_flag,
2118 p_category => NULL,
2119 p_manual_rank_flag => 'N');
2120
2121 UPDATE as_sales_leads
2122 SET lead_rank_ind = 'N'
2123 WHERE sales_lead_id = p_sales_lead_id;
2124
2125 -- ckapoor : disqualification project
2126 ELSIF p_process_type = G_LEAD_QUALIFICATION -- qualification case
2127 THEN
2128
2129 AS_SALES_LEADS_LOG_PKG.Insert_Row(
2130 px_log_id => l_sales_lead_log_id ,
2131 p_sales_lead_id => p_sales_lead_id,
2132 p_created_by => fnd_global.user_id,
2133 p_creation_date => SYSDATE,
2134 p_last_updated_by => fnd_global.user_id,
2135 p_last_update_date => SYSDATE,
2136 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
2137 p_request_id =>
2138 FND_GLOBAL.Conc_Request_Id,
2139 p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
2140 p_program_id =>
2141 FND_GLOBAL.Conc_Program_Id,
2142 p_program_update_date => SYSDATE,
2143 p_status_code => l_status_code,
2144 p_assign_to_person_id => l_assign_to_person_id,
2145 p_assign_to_salesforce_id => l_assign_to_salesforce_id,
2146 p_reject_reason_code => l_reject_reason_code,
2147 p_assign_sales_group_id => l_assign_sales_group_id,
2148 p_lead_rank_id => l_old_lead_rank_id,
2149 p_qualified_flag => l_action_value,
2150 p_category => NULL,
2151 p_manual_rank_flag => NULL );
2152
2153 END IF;
2154
2155 -- Use l_action(l_rule_index) and l_action_value(l_rule_index)
2156 -- to update value
2157 IF (AS_DEBUG_LOW_ON) THEN
2158 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2159 'act:' || l_action);
2160 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2161 'act_val:' || l_action_value);
2162 END IF;
2163
2164 l_cursor := dbms_sql.open_cursor;
2165 dbms_sql.parse(l_cursor, l_action, dbms_sql.native);
2166
2167 IF p_process_type = G_LEAD_RATING
2168 THEN
2169 l_lead_rank_id := TO_NUMBER(l_action_value);
2170 dbms_sql.bind_variable(l_cursor, ':lead_rank_id', l_lead_rank_id);
2171 ELSIF p_process_type = G_CHANNEL_SELECTION
2172 THEN
2173 -- CHANNEL_SELECTION
2174 dbms_sql.bind_variable(l_cursor, ':channel_code', l_action_value);
2175 -- ckapoor : disqualification project
2176 ELSIF p_process_type = G_LEAD_QUALIFICATION --qualification case
2177 THEN
2178
2179 dbms_sql.bind_variable(l_cursor, ':qualified_flag',l_action_value);
2180 -- end ckapoor
2181
2182 END IF;
2183 dbms_sql.bind_variable(l_cursor, ':sales_lead_id',
2184 p_sales_lead_id);
2185 l_rows_inserted := dbms_sql.execute(l_cursor);
2186 dbms_sql.close_cursor(l_cursor);
2187 x_action_value := l_action_value;
2188 ELSE
2189 -- no rules matched
2190 IF p_process_type = G_LEAD_RATING
2191 THEN
2192 l_default_lead_rank_id :=
2193 TO_NUMBER(FND_PROFILE.Value('AS_DEFAULT_LEAD_ENGINE_RANK'));
2194 l_lead_rank_id := l_default_lead_rank_id;
2195 IF (AS_DEBUG_LOW_ON) THEN
2196 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2197 'Default rank:' || l_default_lead_rank_id);
2198 END IF;
2199
2200 IF l_default_lead_rank_id IS NULL
2201 THEN
2202 AS_UTILITY_PVT.Set_Message(
2203 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2204 p_msg_name => 'AS_NO_DEFAULT_RATING');
2205 END IF;
2206
2207 AS_SALES_LEADS_LOG_PKG.Insert_Row(
2208 px_log_id => l_sales_lead_log_id ,
2209 p_sales_lead_id => p_sales_lead_id,
2210 p_created_by => fnd_global.user_id,
2211 p_creation_date => SYSDATE,
2212 p_last_updated_by => fnd_global.user_id,
2213 p_last_update_date => SYSDATE,
2214 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
2215 p_request_id =>
2216 FND_GLOBAL.Conc_Request_Id,
2217 p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
2218 p_program_id =>
2219 FND_GLOBAL.Conc_Program_Id,
2220 p_program_update_date => SYSDATE,
2221 p_status_code => l_status_code,
2222 p_assign_to_person_id => l_assign_to_person_id,
2223 p_assign_to_salesforce_id => l_assign_to_salesforce_id,
2224 p_reject_reason_code => l_reject_reason_code,
2225 p_assign_sales_group_id => l_assign_sales_group_id,
2226 p_lead_rank_id => l_default_lead_rank_id,
2227 p_qualified_flag => l_qualified_flag,
2228 p_category => NULL,
2229 p_manual_rank_flag => 'N');
2230
2231 UPDATE as_sales_leads
2232 SET lead_rank_id = l_default_lead_rank_id,
2233 lead_rank_ind = 'N'
2234 WHERE sales_lead_id = p_sales_lead_id;
2235
2236 x_action_value := TO_CHAR(l_default_lead_rank_id);
2237 -- ckapoor changed.
2238 ELSIF p_process_type = G_CHANNEL_SELECTION
2239 THEN
2240 l_default_channel_code :=
2241 FND_PROFILE.Value('AS_DEFAULT_LEAD_ENGINE_CHANNEL');
2242 IF (AS_DEBUG_LOW_ON) THEN
2243 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2244 'Default channel:' || l_default_channel_code);
2245 END IF;
2246
2247 IF l_default_channel_code IS NULL
2248 THEN
2249 AS_UTILITY_PVT.Set_Message(
2250 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2251 p_msg_name => 'AS_NO_DEFAULT_CHANNEL');
2252 END IF;
2253
2254 UPDATE as_sales_leads
2255 SET channel_code = l_default_channel_code
2256 WHERE sales_lead_id = p_sales_lead_id;
2257
2258 x_action_value := l_default_channel_code;
2259
2260 -- ckapoor changed
2261 ELSIF p_process_type = G_LEAD_QUALIFICATION -- qualification case
2262 THEN
2263 l_default_qualified_flag := FND_PROFILE.Value('AS_DEFAULT_LEAD_ENGINE_QUALIFIED_FLAG');
2264 IF (AS_DEBUG_LOW_ON) THEN
2265 AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2266 'Default qualified flag:' || l_default_qualified_flag);
2267 END IF;
2268 IF l_default_qualified_flag IS NULL
2269 THEN
2270 AS_UTILITY_PVT.Set_Message(
2271 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2272 p_msg_name => 'AS_NO_DEFAULT_QUALIFIED_FLAG');
2273
2274 l_default_qualified_flag := 'N';
2275 END IF;
2276
2277 UPDATE as_sales_leads
2278 SET qualified_flag = l_default_qualified_flag
2279 WHERE sales_lead_id = p_sales_lead_id;
2280
2281 x_action_value := l_default_qualified_flag;
2282
2283 END IF;
2284
2285 l_entity_rule_applied_id := NULL;
2286 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
2287 px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_id
2288 ,p_LAST_UPDATE_DATE => SYSDATE
2289 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
2290 ,p_CREATION_DATE => SYSDATE
2291 ,p_CREATED_BY => FND_GLOBAL.USER_ID
2292 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
2293 ,p_OBJECT_VERSION_NUMBER => 1
2294 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
2295 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
2296 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
2297 ,p_PROGRAM_UPDATE_DATE => SYSDATE
2298 ,p_ENTITY => 'SALES_LEAD'
2299 ,p_ENTITY_ID => p_sales_lead_id
2300 ,p_PROCESS_RULE_ID => NULL
2301 ,p_PARENT_PROCESS_RULE_ID => NULL
2302 ,p_LATEST_FLAG => 'Y'
2303 ,p_ACTION_VALUE => x_action_value
2304 ,p_PROCESS_TYPE => p_process_type
2305 ,p_WINNING_RULE_FLAG => NULL
2306 ,p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR
2307 ,p_ATTRIBUTE1 => FND_API.G_MISS_CHAR
2308 ,p_ATTRIBUTE2 => FND_API.G_MISS_CHAR
2309 ,p_ATTRIBUTE3 => FND_API.G_MISS_CHAR
2310 ,p_ATTRIBUTE4 => FND_API.G_MISS_CHAR
2311 ,p_ATTRIBUTE5 => FND_API.G_MISS_CHAR
2312 ,p_ATTRIBUTE6 => FND_API.G_MISS_CHAR
2313 ,p_ATTRIBUTE7 => FND_API.G_MISS_CHAR
2314 ,p_ATTRIBUTE8 => FND_API.G_MISS_CHAR
2315 ,p_ATTRIBUTE9 => FND_API.G_MISS_CHAR
2316 ,p_ATTRIBUTE10 => FND_API.G_MISS_CHAR
2317 ,p_ATTRIBUTE11 => FND_API.G_MISS_CHAR
2318 ,p_ATTRIBUTE12 => FND_API.G_MISS_CHAR
2319 ,p_ATTRIBUTE13 => FND_API.G_MISS_CHAR
2320 ,p_ATTRIBUTE14 => FND_API.G_MISS_CHAR
2321 ,p_ATTRIBUTE15 => FND_API.G_MISS_CHAR
2322 ,p_PROCESS_STATUS => G_DEFAULT
2323 ,p_ENTITY_DETAIL => l_status_code);
2324
2325
2326
2327
2328 -- ckapoor 11.5.10 filtering phase 2
2329
2330 -- if no rules match, then check for default qual flag
2331
2332 -- if (p_process_type = 'LEAD_QUALIFICATION') then
2333
2334 -- l_filter_unqual_leads := FND_PROFILE.value('AS_FILTER_UNQUALIFIED_LEADS');
2335
2336 -- if ((l_filter_unqual_leads = 'Y') and (l_is_create_import_mode_val='Y') and
2337 -- (l_default_qualified_flag = 'N')) then
2338 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2339 -- 'Default qual case');
2340 -- AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2341 -- 'Filtering error raised from API');
2342 -- raise AS_SALES_LEADS_PUB.Filter_Exception;
2343 -- end if;
2344 -- end if;
2345
2346
2347
2348
2349 END IF;
2350
2351 -- Update LEAD_ENGINE_RUN_DATE
2352 -- Invoke table handler(Sales_Lead_Update_Row)
2353 AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
2354 p_SALES_LEAD_ID => p_SALES_LEAD_ID,
2355 p_LAST_UPDATE_DATE => SYSDATE,
2356 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2357 p_CREATION_DATE => FND_API.G_MISS_DATE,
2358 p_CREATED_BY => FND_API.G_MISS_NUM,
2359 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
2360 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
2361 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
2362 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
2363 p_PROGRAM_UPDATE_DATE => SYSDATE,
2364 p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
2365 p_STATUS_CODE => FND_API.G_MISS_CHAR,
2366 p_CUSTOMER_ID => FND_API.G_MISS_NUM,
2367 p_ADDRESS_ID => FND_API.G_MISS_NUM,
2368 p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
2369 p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
2370 p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
2371 p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
2372 p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
2373 p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
2374 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
2375 p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
2376 p_CLOSE_REASON => FND_API.G_MISS_CHAR,
2377 p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
2378 p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
2379 p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
2380 p_DESCRIPTION => FND_API.G_MISS_CHAR,
2381 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
2382 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2383 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2384 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2385 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2386 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2387 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2388 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2389 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2390 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2391 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2392 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2393 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2394 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2395 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2396 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2397 p_ASSIGN_TO_PERSON_ID => FND_API.G_MISS_NUM,
2398 p_ASSIGN_TO_SALESFORCE_ID => FND_API.G_MISS_NUM,
2399 p_ASSIGN_SALES_GROUP_ID => FND_API.G_MISS_NUM,
2400 p_ASSIGN_DATE => FND_API.G_MISS_DATE,
2401 p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
2402 p_ACCEPT_FLAG => FND_API.G_MISS_CHAR,
2403 p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
2404 p_TOTAL_SCORE => FND_API.G_MISS_NUM,
2405 p_SCORECARD_ID => FND_API.G_MISS_NUM,
2406 p_KEEP_FLAG => FND_API.G_MISS_CHAR,
2407 p_URGENT_FLAG => FND_API.G_MISS_CHAR,
2408 p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
2409 p_REJECT_REASON_CODE => FND_API.G_MISS_CHAR,
2410 p_DELETED_FLAG => FND_API.G_MISS_CHAR,
2411 p_OFFER_ID => FND_API.G_MISS_NUM,
2412 p_QUALIFIED_FLAG => FND_API.G_MISS_CHAR,
2413 p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
2414 p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
2415 p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
2416 p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
2417 p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
2418 p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
2419 p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
2420 p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
2421 p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
2422 p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
2423 p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
2424 p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
2425 p_REFERRED_BY => FND_API.G_MISS_NUM,
2426 p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
2427 p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
2428 p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
2429 p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
2430 p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
2431 p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
2432 p_REF_COMM_AMT => FND_API.G_MISS_NUM,
2433 -- bug No.2341515, 2368075
2434 p_LEAD_DATE => FND_API.G_MISS_DATE,
2435 p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
2436 p_COUNTRY => FND_API.G_MISS_CHAR,
2437 p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
2438 p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
2439 p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
2440 p_LEAD_ENGINE_RUN_DATE => SYSDATE,
2441 p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
2442 p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
2443 p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM
2444
2445 -- 11.5.10 new columns ckapoor
2446
2447
2448 , p_MARKETING_SCORE => FND_API.G_MISS_NUM
2449 , p_INTERACTION_SCORE => FND_API.G_MISS_NUM
2450 , p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR
2451 , p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR
2452 , p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM
2453 , p_SALES_STAGE_ID => FND_API.G_MISS_NUM
2454
2455
2456
2457 );
2458
2459 -- this code is only for CRMAP denorm project
2460 IF p_process_type = G_LEAD_RATING
2461 THEN
2462 IF l_lead_rank_id IS NULL
2463 THEN
2464 l_lead_rank_score := 0;
2465 ELSE
2466 OPEN c_get_rank_score(l_lead_rank_id);
2467 FETCH c_get_rank_score INTO l_lead_rank_score;
2468 CLOSE c_get_rank_score;
2469 END IF;
2470
2471 UPDATE as_sales_leads
2472 SET lead_rank_score = l_lead_rank_score
2473 WHERE sales_lead_id = p_sales_lead_id;
2474
2475 UPDATE as_accesses_all
2476 SET lead_rank_score = l_lead_rank_score
2477 WHERE sales_lead_id = p_sales_lead_id;
2478 END IF;
2479 -- end CRMAP denorm project
2480
2481 --
2482 -- END of API body
2483 --
2484
2485 -- Standard check for p_commit
2486 IF FND_API.to_Boolean( p_commit )
2487 THEN
2488 COMMIT WORK;
2489 END IF;
2490
2491
2492 -- Debug Message
2493 IF (AS_DEBUG_LOW_ON) THEN
2494 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2495 'PVT: ' || l_api_name || ' End');
2496 END IF;
2497
2498 -- Standard call to get message count and IF count is 1, get message info.
2499 FND_MSG_PUB.Count_And_Get
2500 ( p_count => x_msg_count,
2501 p_data => x_msg_data );
2502
2503 EXCEPTION
2504
2505 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
2506 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
2507 -- --RAISE_APPLICATION_ERROR(-20100, 'Filter Exception'));
2508
2509 WHEN FND_API.G_EXC_ERROR THEN
2510 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2511 P_API_NAME => L_API_NAME
2512 ,P_PKG_NAME => G_PKG_NAME
2513 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2514 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2515 ,X_MSG_COUNT => X_MSG_COUNT
2516 ,X_MSG_DATA => X_MSG_DATA
2517 ,X_RETURN_STATUS => X_RETURN_STATUS);
2518
2519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2520 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2521 P_API_NAME => L_API_NAME
2522 ,P_PKG_NAME => G_PKG_NAME
2523 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2524 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2525 ,X_MSG_COUNT => X_MSG_COUNT
2526 ,X_MSG_DATA => X_MSG_DATA
2527 ,X_RETURN_STATUS => X_RETURN_STATUS);
2528
2529 WHEN OTHERS THEN
2530 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2531 P_API_NAME => L_API_NAME
2532 ,P_PKG_NAME => G_PKG_NAME
2533 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2534 ,P_SQLCODE => SQLCODE
2535 ,P_SQLERRM => SQLERRM
2536 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2537 ,X_MSG_COUNT => X_MSG_COUNT
2538 ,X_MSG_DATA => X_MSG_DATA
2539 ,X_RETURN_STATUS => X_RETURN_STATUS);
2540
2541 END Rate_Select_Lead;
2542
2543
2544 PROCEDURE Lead_Process_After_Create(
2545 P_Api_Version_Number IN NUMBER,
2546 P_Init_Msg_List IN VARCHAR2,
2547 p_Commit IN VARCHAR2,
2548 p_Validation_Level IN NUMBER,
2549 P_Check_Access_Flag IN VARCHAR2,
2550 p_Admin_Flag IN VARCHAR2,
2551 P_Admin_Group_Id IN NUMBER,
2552 P_identity_salesforce_id IN NUMBER,
2553 P_Salesgroup_id IN NUMBER,
2554 P_Sales_Lead_Id IN NUMBER,
2555 X_Return_Status OUT NOCOPY VARCHAR2,
2556 X_Msg_Count OUT NOCOPY NUMBER,
2557 X_Msg_Data OUT NOCOPY VARCHAR2
2558 )
2559 IS
2560 l_api_name CONSTANT VARCHAR2(30)
2561 := 'Lead_Process_After_Create';
2562 l_api_version_number CONSTANT NUMBER := 2.0;
2563 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2564 l_lead_engines_out_rec AS_SALES_LEADS_PUB.Lead_Engines_Out_Rec_Type;
2565 L_Sales_Lead_Profile_Tbl AS_UTILITY_PUB.Profile_Tbl_Type;
2566 l_opportunity_id NUMBER;
2567 l_customer_id NUMBER;
2568 l_address_id NUMBER;
2569 l_referral_type VARCHAR2(30);
2570 l_assign_to_salesforce_id NUMBER;
2571 l_status_code VARCHAR2(30);
2572 l_access_id NUMBER;
2573 l_person_id NUMBER;
2574 l_request_id NUMBER;
2575 l_sales_team_rec AS_ACCESS_PVT.sales_team_rec_type;
2576 -- l_overriding_usernames AS_SALES_LEAD_REFERRAL.t_overriding_usernames;
2577 l_open_status_flag VARCHAR2(1);
2578 l_return_status VARCHAR2(1);
2579
2580 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
2581 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
2582 SL.ASSIGN_TO_SALESFORCE_ID, SL.STATUS_CODE
2583 FROM AS_SALES_LEADS SL
2584 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
2585
2586 CURSOR C_Get_Person(C_Resource_Id NUMBER) IS
2587 SELECT source_id
2588 FROM jtf_rs_resource_extns
2589 WHERE resource_id = c_resource_id;
2590
2591 CURSOR C_Get_Open_Status_Flag(C_Status_Code VARCHAR2) IS
2592 SELECT opp_open_status_flag
2593 FROM as_statuses_b
2594 WHERE status_code = c_status_code
2595 AND lead_flag = 'Y';
2596 BEGIN
2597 -- Standard Start of API savepoint
2598 SAVEPOINT LEAD_PROCESS_AFTER_CREATE_PVT;
2599
2600 -- Standard call to check for call compatibility.
2601 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2602 p_api_version_number,
2603 l_api_name,
2604 G_PKG_NAME)
2605 THEN
2606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2607 END IF;
2608
2609 -- Initialize message list IF p_init_msg_list is set to TRUE.
2610 IF FND_API.to_Boolean( p_init_msg_list )
2611 THEN
2612 FND_MSG_PUB.initialize;
2613 END IF;
2614
2615 -- Debug Message
2616 IF (AS_DEBUG_LOW_ON) THEN
2617 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2618 'PVT:' || l_api_name || ' Start');
2619 END IF;
2620
2621 -- Initialize API return status to SUCCESS
2622 x_return_status := FND_API.G_RET_STS_SUCCESS;
2623
2624 --
2625 -- Api body
2626 --
2627 -- ******************************************************************
2628 -- Validate Environment
2629 -- ******************************************************************
2630
2631 IF FND_GLOBAL.User_Id IS NULL
2632 THEN
2633 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2634 THEN
2635 AS_UTILITY_PVT.Set_Message(
2636 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2637 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
2638 p_token1 => 'PROFILE',
2639 p_token1_value => 'USER_ID');
2640 END IF;
2641 RAISE FND_API.G_EXC_ERROR;
2642 END IF;
2643
2644 IF (p_validation_level = fnd_api.g_valid_level_full)
2645 THEN
2646 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2647 p_api_version_number => 2.0
2648 ,p_init_msg_list => p_init_msg_list
2649 ,p_salesforce_id => P_Identity_Salesforce_Id
2650 ,p_admin_group_id => p_admin_group_id
2651 ,x_return_status => x_return_status
2652 ,x_msg_count => x_msg_count
2653 ,x_msg_data => x_msg_data
2654 ,x_sales_member_rec => l_identity_sales_member_rec);
2655
2656 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657 RAISE FND_API.G_EXC_ERROR;
2658 END IF;
2659 END IF;
2660
2661 Run_Lead_Engines(
2662 P_Api_Version_Number => 2.0,
2663 P_Init_Msg_List => FND_API.G_FALSE,
2664 P_Commit => FND_API.G_FALSE,
2665 P_Validation_Level => P_Validation_Level,
2666 P_Admin_Group_Id => P_Admin_Group_Id,
2667 P_identity_salesforce_id => P_identity_salesforce_id,
2668 P_Salesgroup_id => P_Salesgroup_id,
2669 P_Sales_Lead_id => P_Sales_Lead_id,
2670 -- ckapoor Phase 2 filtering project 11.5.10
2671 -- P_Is_Create_Mode => 'Y',
2672
2673 X_Lead_Engines_Out_Rec => l_lead_engines_out_rec,
2674 X_Return_Status => x_return_status,
2675 X_Msg_Count => x_msg_count,
2676 X_Msg_Data => x_msg_data);
2677
2678 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2679 RAISE FND_API.G_EXC_ERROR;
2680 END IF;
2681
2682 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
2683 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
2684 l_assign_to_salesforce_id, l_status_code;
2685 CLOSE C_Get_Lead_Info;
2686
2687 IF L_Lead_Engines_Out_Rec.sales_team_flag = 'Y'
2688 THEN
2689 -- not incubation lead
2690 As_Sales_Lead_Assign_PVT.Build_Lead_Sales_Team(
2691 P_Api_Version_Number => 2.0,
2692 P_Init_Msg_List => FND_API.G_FALSE,
2693 P_Commit => FND_API.G_FALSE,
2694 P_Validation_Level => P_Validation_Level,
2695 P_Admin_Group_Id => P_Admin_Group_Id,
2696 P_identity_salesforce_id => P_identity_salesforce_id,
2697 P_Salesgroup_id => P_Salesgroup_id,
2698 P_Sales_Lead_id => P_Sales_Lead_id,
2699 X_Request_id => l_request_id,
2700 X_Return_Status => x_return_status,
2701 X_Msg_Count => x_msg_count,
2702 X_Msg_Data => x_msg_data);
2703
2704 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2705 RAISE FND_API.G_EXC_ERROR;
2706 END IF;
2707
2708 IF l_assign_to_salesforce_id IS NULL
2709 THEN
2710 As_Sales_Lead_Assign_PVT.Find_Lead_Owner(
2711 p_sales_lead_id, p_salesgroup_id, l_request_id,
2712 l_return_status, x_msg_count, x_msg_data);
2713
2714 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2715 RAISE fnd_api.g_exc_error;
2716 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2717 RAISE fnd_api.g_exc_unexpected_error;
2718 END IF;
2719 ELSE
2720 As_Sales_Lead_Assign_PVT.Process_Access_Record(
2721 p_sales_lead_id, l_request_id);
2722 END IF;
2723
2724 ELSE
2725 -- incubation lead
2726 IF l_assign_to_salesforce_id IS NULL
2727 THEN
2728 AS_SALES_LEADS_PUB.ROUTE_LEAD_TO_MARKETING(
2729 P_Api_Version_Number => 2.0,
2730 P_Init_Msg_List => FND_API.G_FALSE,
2731 P_Commit => FND_API.G_FALSE,
2732 P_Validation_Level => P_Validation_Level,
2733 P_Admin_Group_Id => P_Admin_Group_Id,
2734 P_identity_salesforce_id => P_identity_salesforce_id,
2735 P_Sales_Lead_id => P_Sales_Lead_id,
2736 X_Return_Status => x_return_status,
2737 X_Msg_Count => x_msg_count,
2738 X_Msg_Data => x_msg_data);
2739
2740 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2741 RAISE FND_API.G_EXC_ERROR;
2742 END IF;
2743 END IF;
2744
2745 OPEN C_Get_Person(P_identity_salesforce_id);
2746 FETCH C_Get_Person INTO l_person_id;
2747 CLOSE C_Get_Person;
2748
2749 -- Add creator to sales team
2750 l_Sales_Team_Rec.last_update_date := SYSDATE;
2751 l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
2752 l_Sales_Team_Rec.creation_date := SYSDATE;
2753 l_Sales_Team_Rec.created_by := FND_GLOBAL.USER_ID;
2754 l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
2755 l_Sales_Team_Rec.customer_id := l_Customer_Id;
2756 l_Sales_Team_Rec.address_id := l_Address_Id;
2757 l_Sales_Team_Rec.salesforce_id := P_identity_salesforce_id;
2758 l_Sales_Team_Rec.person_id := l_person_id;
2759 l_Sales_Team_Rec.sales_group_id := P_salesgroup_id;
2760 l_Sales_Team_Rec.sales_lead_id := p_sales_lead_id;
2761 l_Sales_Team_Rec.team_leader_flag := 'Y';
2762 l_Sales_Team_Rec.owner_flag := 'N';
2763 l_Sales_Team_Rec.freeze_flag := 'Y';
2764 l_Sales_Team_Rec.reassign_flag := 'N';
2765 l_Sales_Team_Rec.created_by_TAP_flag := 'N';
2766
2767 As_Access_PVT.Create_SalesTeam( -- for creator
2768 P_Api_Version_Number => 2.0,
2769 P_Init_Msg_List => FND_API.G_FALSE,
2770 P_Commit => FND_API.G_FALSE,
2771 P_Validation_Level => P_Validation_Level,
2772 P_Access_Profile_Rec => NULL,
2773 P_Check_Access_Flag => 'N',
2774 P_Admin_Flag => P_Admin_Flag,
2775 P_Admin_Group_Id => P_Admin_Group_Id,
2776 P_identity_salesforce_id => P_identity_salesforce_id,
2777 p_sales_team_rec => l_sales_team_rec,
2778 X_Return_Status => x_return_status,
2779 X_Msg_Count => x_msg_count,
2780 X_Msg_Data => x_msg_data,
2781 X_Access_Id => l_access_id);
2782
2783 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2784 RAISE FND_API.G_EXC_ERROR;
2785 END IF;
2786 END IF;
2787
2788 IF (AS_DEBUG_LOW_ON) THEN
2789 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2790 'indirect?' || L_Lead_Engines_Out_Rec.indirect_channel_flag);
2791 END IF;
2792 IF L_Lead_Engines_Out_Rec.indirect_channel_flag = 'Y' AND
2793 L_Lead_Engines_Out_Rec.qualified_flag = 'Y' AND
2794 FND_PROFILE.Value('AS_AUTO_CONVERT_LEAD_OPP') = 'Y'
2795 THEN
2796 As_Sales_Lead_Opp_PVT.Create_Opportunity_For_Lead(
2797 P_Api_Version_Number => 2.0,
2798 P_Init_Msg_List => FND_API.G_FALSE,
2799 P_Commit => FND_API.G_FALSE,
2800 P_Validation_Level => P_Validation_Level,
2801 P_Check_Access_Flag => P_Check_Access_Flag,
2802 P_Admin_Flag => P_Admin_Flag,
2803 P_Admin_Group_Id => P_Admin_Group_Id,
2804 P_identity_salesforce_id => P_identity_salesforce_id,
2805 P_identity_Salesgroup_id => P_Salesgroup_id,
2806 P_Sales_Lead_Profile_Tbl => L_Sales_Lead_Profile_Tbl,
2807 P_Sales_Lead_id => P_Sales_Lead_id,
2808 P_OPP_STATUS => NULL,
2809 X_Return_Status => x_return_status,
2810 X_Msg_Count => x_msg_count,
2811 X_Msg_Data => x_msg_data,
2812 X_Opportunity_Id => l_opportunity_id);
2813
2814 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2815 RAISE FND_API.G_EXC_ERROR;
2816 END IF;
2817
2818 PV_BG_PARTNER_MATCHING_PUB.Start_Partner_Matching(
2819 P_Api_Version_Number => 2.0,
2820 P_Init_Msg_List => FND_API.G_FALSE,
2821 P_Commit => FND_API.G_FALSE,
2822 P_Validation_Level => P_Validation_Level,
2823 P_Admin_Group_Id => P_Admin_Group_Id,
2824 P_identity_salesforce_id => P_identity_salesforce_id,
2825 P_Salesgroup_id => P_Salesgroup_id,
2826 P_Lead_id => L_Opportunity_Id,
2827 X_Return_Status => x_return_status,
2828 X_Msg_Count => x_msg_count,
2829 X_Msg_Data => x_msg_data);
2830
2831 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2832 RAISE FND_API.G_EXC_ERROR;
2833 END IF;
2834 END IF;
2835
2836 -- IF l_referral_type IS NOT NULL
2837 -- THEN
2838 -- AS_SALES_LEAD_REFERRAL.Notify_Party(
2839 -- P_Api_Version => 2.0,
2840 -- P_Init_Msg_List => FND_API.G_FALSE,
2841 -- P_Commit => FND_API.G_FALSE,
2842 -- P_Validation_Level => P_Validation_Level,
2843 -- P_Lead_Id => p_sales_lead_id,
2844 -- P_Lead_Status => NULL,
2845 -- P_salesforce_id => P_identity_salesforce_id,
2846 -- p_overriding_usernames => l_overriding_usernames,
2847 -- X_Return_Status => x_return_status,
2848 -- X_Msg_Count => x_msg_count,
2849 -- X_Msg_Data => x_msg_data);
2850
2851 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2852 -- RAISE FND_API.G_EXC_ERROR;
2853 -- END IF;
2854 -- END IF;
2855
2856 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
2857 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
2858 l_assign_to_salesforce_id, l_status_code;
2859 CLOSE C_Get_Lead_Info;
2860
2861 OPEN C_Get_Open_Status_Flag(l_status_code);
2862 FETCH C_Get_Open_Status_Flag INTO l_open_status_flag;
2863 CLOSE C_Get_Open_Status_Flag;
2864
2865 IF (AS_DEBUG_LOW_ON) THEN
2866 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2867 'status_code=' || l_status_code);
2868 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2869 'open status flag=' || l_open_status_flag);
2870 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2871 'assign_to_sf_id=' || l_assign_to_salesforce_id);
2872 END IF;
2873
2874 IF l_open_status_flag = 'Y' AND l_assign_to_salesforce_id IS NOT NULL
2875 AND l_lead_engines_out_rec.qualified_flag = 'Y'
2876 THEN
2877 aml_monitor_wf.launch_monitor(
2878 P_Api_Version_Number => 2.0,
2879 P_Init_Msg_List => FND_API.G_FALSE,
2880 p_commit => FND_API.G_FALSE,
2881 P_Sales_Lead_Id => p_sales_lead_id,
2882 P_Changed_From_stage => 'CREATION_DATE',
2883 P_Lead_Action => 'CREATE',
2884 -- 9/9/03 SWKHANNA -- Added for Lead Upgrade/Downgrade Functionality
2885 P_Attribute_Changed => NULL,
2886 X_Return_Status => x_return_status,
2887 X_Msg_Count => x_msg_count,
2888 X_Msg_Data => x_msg_data);
2889
2890 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2891 RAISE FND_API.G_EXC_ERROR;
2892 END IF;
2893 END IF;
2894
2895 -- ER 3052066
2896 -- Leave a record for TAP New mode
2897 -- Used to sync TRANS and NM_TRANS table
2898 BEGIN
2899 INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
2900 customer_id, address_id, sales_lead_id, last_update_date,
2901 last_updated_by, creation_date, created_by,
2902 last_update_login, change_type, delete_flag, insert_flag,
2903 processed_flag)
2904 VALUES
2905 (l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
2906 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
2907 FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
2908 'Y');
2909 EXCEPTION
2910 WHEN OTHERS THEN
2911 UPDATE AS_CHANGED_ACCOUNTS_ALL
2912 SET processed_flag = 'Y'
2913 WHERE sales_lead_id = p_sales_lead_id;
2914 END;
2915 -- IF NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'Y') = 'Y'
2916 -- THEN
2917 -- 1. If AS_ENABLE_LEAD_ONLINE_TAP is 'Y', update record.
2918 -- 2. If AS_ENABLE_LEAD_ONLINE_TAP is 'N', still keep it in
2919 -- as_changed_accounts_all, so TAP New Mode will pick up this
2920 -- record.
2921 -- DELETE FROM as_changed_accounts_all
2922 -- WHERE sales_lead_id = p_sales_lead_id;
2923 -- IF (AS_DEBUG_LOW_ON) THEN
2924 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2925 -- 'Delete as_changed_accounts_all record');
2926 -- END IF;
2927 -- UPDATE as_changed_accounts_all
2928 -- SET processed_flag = 'Y'
2929 -- WHERE sales_lead_id = p_sales_lead_id;
2930 -- END IF;
2931
2932 IF l_return_status = 'W'
2933 THEN
2934 x_return_status := 'W';
2935 END IF;
2936
2937 --
2938 -- END of API body
2939 --
2940
2941 -- Standard check for p_commit
2942 IF FND_API.to_Boolean( p_commit )
2943 THEN
2944 COMMIT WORK;
2945 END IF;
2946
2947 -- Debug Message
2948 IF (AS_DEBUG_LOW_ON) THEN
2949 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2950 'PVT: ' || l_api_name || ' End');
2951 END IF;
2952
2953 -- Standard call to get message count and IF count is 1, get message info.
2954 IF x_return_status = 'W'
2955 THEN
2956 FND_MSG_PUB.Count_And_Get
2957 (
2958 p_encoded => FND_API.G_FALSE,
2959 p_count => x_msg_count,
2960 p_data => x_msg_data );
2961 ELSE
2962 FND_MSG_PUB.Count_And_Get
2963 ( p_count => x_msg_count,
2964 p_data => x_msg_data );
2965 END IF;
2966
2967 EXCEPTION
2968
2969 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
2970 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
2971
2972 WHEN FND_API.G_EXC_ERROR THEN
2973 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2974 P_API_NAME => L_API_NAME
2975 ,P_PKG_NAME => G_PKG_NAME
2976 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2977 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2978 ,X_MSG_COUNT => X_MSG_COUNT
2979 ,X_MSG_DATA => X_MSG_DATA
2980 ,X_RETURN_STATUS => X_RETURN_STATUS);
2981
2982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2983 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2984 P_API_NAME => L_API_NAME
2985 ,P_PKG_NAME => G_PKG_NAME
2986 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2987 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2988 ,X_MSG_COUNT => X_MSG_COUNT
2989 ,X_MSG_DATA => X_MSG_DATA
2990 ,X_RETURN_STATUS => X_RETURN_STATUS);
2991
2992 WHEN OTHERS THEN
2993 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2994 P_API_NAME => L_API_NAME
2995 ,P_PKG_NAME => G_PKG_NAME
2996 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2997 ,P_SQLCODE => SQLCODE
2998 ,P_SQLERRM => SQLERRM
2999 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3000 ,X_MSG_COUNT => X_MSG_COUNT
3001 ,X_MSG_DATA => X_MSG_DATA
3002 ,X_RETURN_STATUS => X_RETURN_STATUS);
3003 END Lead_Process_After_Create;
3004
3005
3006 PROCEDURE Lead_Process_After_Update(
3007 P_Api_Version_Number IN NUMBER,
3008 P_Init_Msg_List IN VARCHAR2,
3009 p_Commit IN VARCHAR2,
3010 p_Validation_Level IN NUMBER,
3011 P_Check_Access_Flag IN VARCHAR2,
3012 p_Admin_Flag IN VARCHAR2,
3013 P_Admin_Group_Id IN NUMBER,
3014 P_identity_salesforce_id IN NUMBER,
3015 P_Salesgroup_id IN NUMBER,
3016 P_Sales_Lead_Id IN NUMBER,
3017 X_Return_Status OUT NOCOPY VARCHAR2,
3018 X_Msg_Count OUT NOCOPY NUMBER,
3019 X_Msg_Data OUT NOCOPY VARCHAR2
3020 )
3021 IS
3022 l_api_name CONSTANT VARCHAR2(30)
3023 := 'Lead_Process_After_Update';
3024 l_api_version_number CONSTANT NUMBER := 2.0;
3025 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
3026 l_auto_run_lead_engines VARCHAR2(1);
3027 l_lead_engines_out_rec AS_SALES_LEADS_PUB.Lead_Engines_Out_Rec_Type;
3028 L_Sales_Lead_Profile_Tbl AS_UTILITY_PUB.Profile_Tbl_Type;
3029 l_opportunity_id NUMBER;
3030 l_customer_id NUMBER;
3031 l_address_id NUMBER;
3032 l_referral_type VARCHAR2(30);
3033 l_assign_to_salesforce_id NUMBER;
3034 l_status_code VARCHAR2(30);
3035 l_reject_reason_code VARCHAR2(30);
3036 l_access_id NUMBER;
3037 l_person_id NUMBER;
3038 l_request_id NUMBER;
3039 l_sales_team_rec AS_ACCESS_PVT.sales_team_rec_type;
3040 -- l_overriding_usernames AS_SALES_LEAD_REFERRAL.t_overriding_usernames;
3041 l_owner_exists_flag VARCHAR2(1) := 'N';
3042 l_owner_changed_flag VARCHAR2(1) := 'N';
3043 l_open_status_flag VARCHAR2(1);
3044 l_creation_date_tbl JTF_DATE_TABLE;
3045 l_resource_id_tbl JTF_NUMBER_TABLE;
3046 l_creation_date DATE;
3047 l_resource_id NUMBER;
3048 l_i NUMBER;
3049 l_return_status VARCHAR2(1);
3050
3051 -- swkhanna Jun17,03
3052 l_rank_changed_flag VARCHAR2(1) := 'N';
3053 l_lead_rank_id NUMBER;
3054 l_rank_id NUMBER;
3055 l_rank_id_tbl JTF_NUMBER_TABLE;
3056 -- 9/9/03 SWKHANNA
3057 l_attribute_changed VARCHAR2(60);
3058
3059 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
3060 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
3061 SL.ASSIGN_TO_SALESFORCE_ID, SL.QUALIFIED_FLAG,
3062 SL.LEAD_RANK_ID, SL.CHANNEL_CODE, SL.STATUS_CODE,
3063 SL.REJECT_REASON_CODE
3064 FROM AS_SALES_LEADS SL
3065 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
3066
3067 -- Retrieve channel type
3068 CURSOR c_get_indirect_channel_flag(c_channel_code VARCHAR2) IS
3069 SELECT NVL(channel.indirect_channel_flag, 'N')
3070 FROM pv_channel_types channel
3071 WHERE channel.channel_lookup_code = c_channel_code;
3072
3073 -- Check whether owner exists or not
3074 CURSOR c_check_owner_exists(c_sales_lead_id NUMBER) IS
3075 SELECT 'Y'
3076 FROM as_accesses_all acc
3077 WHERE acc.sales_lead_id = c_sales_lead_id
3078 AND acc.owner_flag = 'Y';
3079
3080 CURSOR C_Get_Person(C_Resource_Id NUMBER) IS
3081 SELECT source_id
3082 FROM jtf_rs_resource_extns
3083 WHERE resource_id = c_resource_id;
3084
3085 CURSOR C_Get_Open_Status_Flag(c_sales_lead_id NUMBER) IS
3086 SELECT lead.status_code, sta.opp_open_status_flag
3087 FROM as_statuses_b sta, as_sales_leads lead
3088 WHERE lead.sales_lead_id = c_sales_lead_id
3089 AND lead.status_code = sta.status_code
3090 AND sta.lead_flag = 'Y';
3091
3092
3093 -- swkhanna Jun17,03
3094 -- also added lead_rank_id in select clause
3095 CURSOR C_Get_Log(C_Sales_Lead_Id NUMBER) IS
3096 SELECT creation_date, assign_to_salesforce_id, lead_rank_id
3097 FROM as_sales_leads_log
3098 WHERE sales_lead_id = c_sales_lead_id
3099 ORDER BY log_id DESC;
3100
3101 BEGIN
3102 -- Standard Start of API savepoint
3103 SAVEPOINT LEAD_PROCESS_AFTER_UPDATE_PVT;
3104
3105 -- Standard call to check for call compatibility.
3106 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3107 p_api_version_number,
3108 l_api_name,
3109 G_PKG_NAME)
3110 THEN
3111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3112 END IF;
3113
3114 -- Initialize message list IF p_init_msg_list is set to TRUE.
3115 IF FND_API.to_Boolean( p_init_msg_list )
3116 THEN
3117 FND_MSG_PUB.initialize;
3118 END IF;
3119
3120 -- Debug Message
3121 IF (AS_DEBUG_LOW_ON) THEN
3122 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3123 'PVT:' || l_api_name || ' Start');
3124 END IF;
3125
3126 -- Initialize API return status to SUCCESS
3127 x_return_status := FND_API.G_RET_STS_SUCCESS;
3128
3129 --
3130 -- Api body
3131 --
3132 -- ******************************************************************
3133 -- Validate Environment
3134 -- ******************************************************************
3135
3136 IF FND_GLOBAL.User_Id IS NULL
3137 THEN
3138 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3139 THEN
3140 AS_UTILITY_PVT.Set_Message(
3141 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3142 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
3143 p_token1 => 'PROFILE',
3144 p_token1_value => 'USER_ID');
3145 END IF;
3146 RAISE FND_API.G_EXC_ERROR;
3147 END IF;
3148
3149 IF (p_validation_level = fnd_api.g_valid_level_full)
3150 THEN
3151 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
3152 p_api_version_number => 2.0
3153 ,p_init_msg_list => p_init_msg_list
3154 ,p_salesforce_id => P_Identity_Salesforce_Id
3155 ,p_admin_group_id => p_admin_group_id
3156 ,x_return_status => x_return_status
3157 ,x_msg_count => x_msg_count
3158 ,x_msg_data => x_msg_data
3159 ,x_sales_member_rec => l_identity_sales_member_rec);
3160
3161 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3162 RAISE FND_API.G_EXC_ERROR;
3163 END IF;
3164 END IF;
3165
3166 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
3167 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
3168 l_assign_to_salesforce_id,
3169 l_lead_engines_out_rec.qualified_flag,
3170 l_lead_engines_out_rec.lead_rank_id,
3171 l_lead_engines_out_rec.channel_code,
3172 l_status_code, l_reject_reason_code;
3173 CLOSE C_Get_Lead_Info;
3174
3175 -- Profile OS: Auto Run Lead Engines While Update
3176 l_auto_run_lead_engines := FND_PROFILE.Value('AS_AUTO_RUN_LEAD_ENGINES');
3177 IF (AS_DEBUG_LOW_ON) THEN
3178 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3179 'auto run engine=' || l_auto_run_lead_engines);
3180 END IF;
3181 IF NVL(l_auto_run_lead_engines, 'N') = 'Y'
3182 THEN
3183 Run_Lead_Engines(
3184 P_Api_Version_Number => 2.0,
3185 P_Init_Msg_List => FND_API.G_FALSE,
3186 P_Commit => FND_API.G_FALSE,
3187 P_Validation_Level => P_Validation_Level,
3188 P_Admin_Group_Id => P_Admin_Group_Id,
3189 P_identity_salesforce_id => P_identity_salesforce_id,
3190 P_Salesgroup_id => P_Salesgroup_id,
3191 P_Sales_Lead_id => P_Sales_Lead_id,
3192 -- ckapoor Phase 2 filtering project 11.5.10
3193 -- P_Is_Create_Mode => 'N',
3194
3195 X_Lead_Engines_Out_Rec => l_lead_engines_out_rec,
3196 X_Return_Status => x_return_status,
3197 X_Msg_Count => x_msg_count,
3198 X_Msg_Data => x_msg_data);
3199
3200 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3201 RAISE FND_API.G_EXC_ERROR;
3202 END IF;
3203
3204 ELSE
3205 OPEN c_get_indirect_channel_flag(l_lead_engines_out_rec.channel_code);
3206 FETCH c_get_indirect_channel_flag INTO
3207 l_lead_engines_out_rec.indirect_channel_flag;
3208 CLOSE c_get_indirect_channel_flag;
3209
3210 IF l_lead_engines_out_rec.channel_code =
3211 FND_PROFILE.Value('AS_LEAD_INCUBATION_CHANNEL')
3212 THEN
3213 l_lead_engines_out_rec.sales_team_flag := 'N';
3214 ELSE
3215 l_lead_engines_out_rec.sales_team_flag := 'Y';
3216 END IF;
3217 END IF;
3218
3219 OPEN c_check_owner_exists(p_sales_lead_id);
3220 FETCH c_check_owner_exists INTO l_owner_exists_flag;
3221 CLOSE c_check_owner_exists;
3222
3223 IF L_Lead_Engines_Out_Rec.sales_team_flag = 'Y'
3224 THEN
3225 -- not incubation lead
3226 As_Sales_Lead_Assign_PVT.Rebuild_Lead_Sales_Team(
3227 P_Api_Version_Number => 2.0,
3228 P_Init_Msg_List => FND_API.G_FALSE,
3229 P_Commit => FND_API.G_FALSE,
3230 P_Validation_Level => P_Validation_Level,
3231 P_Admin_Group_Id => P_Admin_Group_Id,
3232 P_identity_salesforce_id => P_identity_salesforce_id,
3233 P_Salesgroup_id => P_Salesgroup_id,
3234 P_Sales_Lead_id => P_Sales_Lead_id,
3235 X_Request_id => l_request_id,
3236 X_Return_Status => x_return_status,
3237 X_Msg_Count => x_msg_count,
3238 X_Msg_Data => x_msg_data);
3239
3240 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3241 RAISE FND_API.G_EXC_ERROR;
3242 END IF;
3243
3244 -- Check owner again here because Rebuild_Lead_Sales_Team may
3245 -- remove owner in as_accesses_all
3246 l_owner_exists_flag := 'N';
3247 OPEN c_check_owner_exists(p_sales_lead_id);
3248 FETCH c_check_owner_exists INTO l_owner_exists_flag;
3249 CLOSE c_check_owner_exists;
3250
3251 IF (AS_DEBUG_LOW_ON) THEN
3252 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3253 'assign to=' || l_assign_to_salesforce_id);
3254 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3255 'reject reason=' || l_reject_reason_code);
3256 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3257 'owner exist?' || l_owner_exists_flag);
3258 END IF;
3259 IF l_assign_to_salesforce_id IS NULL OR
3260 l_reject_reason_code IS NOT NULL OR
3261 l_owner_exists_flag = 'N'
3262 THEN
3263 As_Sales_Lead_Assign_PVT.Find_Lead_Owner(
3264 p_sales_lead_id, p_salesgroup_id, l_request_id,
3265 l_return_status, x_msg_count, x_msg_data);
3266
3267 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3268 RAISE fnd_api.g_exc_error;
3269 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3270 RAISE fnd_api.g_exc_unexpected_error;
3271 END IF;
3272 l_owner_changed_flag := 'Y';
3273 ELSE
3274 As_Sales_Lead_Assign_PVT.Process_Access_Record(
3275 p_sales_lead_id, l_request_id);
3276 END IF;
3277 ELSE
3278 -- lead with incubation channel
3279 AS_SALES_LEADS_PUB.ROUTE_LEAD_TO_MARKETING(
3280 P_Api_Version_Number => 2.0,
3281 P_Init_Msg_List => FND_API.G_FALSE,
3282 P_Commit => FND_API.G_FALSE,
3283 P_Validation_Level => P_Validation_Level,
3284 P_Admin_Group_Id => P_Admin_Group_Id,
3285 P_identity_salesforce_id => P_identity_salesforce_id,
3286 P_Sales_Lead_id => P_Sales_Lead_id,
3287 X_Return_Status => x_return_status,
3288 X_Msg_Count => x_msg_count,
3289 X_Msg_Data => x_msg_data);
3290
3291 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3292 RAISE FND_API.G_EXC_ERROR;
3293 END IF;
3294 END IF;
3295
3296 IF L_Lead_Engines_Out_Rec.indirect_channel_flag = 'Y' AND
3297 FND_PROFILE.Value('AS_AUTO_CONVERT_LEAD_OPP') = 'Y' AND
3298 l_status_code <> 'CONVERTED_TO_OPPORTUNITY'
3299 THEN
3300 As_Sales_Lead_Opp_PVT.Create_Opportunity_For_Lead(
3301 P_Api_Version_Number => 2.0,
3302 P_Init_Msg_List => FND_API.G_FALSE,
3303 P_Commit => FND_API.G_FALSE,
3304 P_Validation_Level => P_Validation_Level,
3305 P_Check_Access_Flag => P_Check_Access_Flag,
3306 P_Admin_Flag => P_Admin_Flag,
3307 P_Admin_Group_Id => P_Admin_Group_Id,
3308 P_identity_salesforce_id => P_identity_salesforce_id,
3309 P_identity_Salesgroup_id => P_Salesgroup_id,
3310 P_Sales_Lead_Profile_Tbl => L_Sales_Lead_Profile_Tbl,
3311 P_Sales_Lead_id => P_Sales_Lead_id,
3312 P_OPP_STATUS => NULL,
3313 X_Return_Status => x_return_status,
3314 X_Msg_Count => x_msg_count,
3315 X_Msg_Data => x_msg_data,
3316 X_Opportunity_Id => l_opportunity_id);
3317
3318 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3319 RAISE FND_API.G_EXC_ERROR;
3320 END IF;
3321
3322 PV_BG_PARTNER_MATCHING_PUB.Start_Partner_Matching(
3323 P_Api_Version_Number => 2.0,
3324 P_Init_Msg_List => FND_API.G_FALSE,
3325 P_Commit => FND_API.G_FALSE,
3326 P_Validation_Level => P_Validation_Level,
3327 P_Admin_Group_Id => P_Admin_Group_Id,
3328 P_identity_salesforce_id => P_identity_salesforce_id,
3329 P_Salesgroup_id => P_Salesgroup_id,
3330 P_Lead_id => L_Opportunity_Id,
3331 X_Return_Status => x_return_status,
3332 X_Msg_Count => x_msg_count,
3333 X_Msg_Data => x_msg_data);
3334
3335 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3336 RAISE FND_API.G_EXC_ERROR;
3337 END IF;
3338 END IF;
3339
3340 -- IF l_referral_type IS NOT NULL
3341 -- THEN
3342 -- AS_SALES_LEAD_REFERRAL.Notify_Party(
3343 -- P_Api_Version => 2.0,
3344 -- P_Init_Msg_List => FND_API.G_FALSE,
3345 -- P_Commit => FND_API.G_FALSE,
3346 -- P_Validation_Level => P_Validation_Level,
3347 -- P_Lead_Id => p_sales_lead_id,
3348 -- P_Lead_Status => NULL,
3349 -- P_salesforce_id => P_identity_salesforce_id,
3350 -- p_overriding_usernames => l_overriding_usernames,
3351 -- X_Return_Status => x_return_status,
3352 -- X_Msg_Count => x_msg_count,
3353 -- X_Msg_Data => x_msg_data);
3354
3355 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3356 -- RAISE FND_API.G_EXC_ERROR;
3357 -- END IF;
3358 -- END IF;
3359
3360 OPEN C_Get_Open_Status_Flag(p_sales_lead_id);
3361 FETCH C_Get_Open_Status_Flag INTO l_status_code, l_open_status_flag;
3362 CLOSE C_Get_Open_Status_Flag;
3363
3364 IF (AS_DEBUG_LOW_ON) THEN
3365 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3366 'status=' || l_status_code);
3367 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3368 'open?' || l_open_status_flag);
3369 END IF;
3370
3371 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
3372 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
3373 l_assign_to_salesforce_id,
3374 l_lead_engines_out_rec.qualified_flag,
3375 -- l_lead_engines_out_rec.lead_rank_id,
3376 -- swkhanna Jun17,03
3377 l_lead_rank_id,
3378 l_lead_engines_out_rec.channel_code,
3379 l_status_code, l_reject_reason_code;
3380 CLOSE C_Get_Lead_Info;
3381
3382 IF l_owner_changed_flag = 'N'
3383 THEN
3384 -- Get the time when previous owner still owns the lead.
3385 OPEN C_Get_Log(P_Sales_Lead_Id);
3386 FETCH C_Get_Log BULK COLLECT INTO
3387 l_creation_date_tbl, l_resource_id_tbl, l_rank_id_tbl ;
3388 CLOSE C_Get_Log;
3389
3390 IF (AS_DEBUG_LOW_ON) AND l_creation_date_tbl.count>=1 THEN
3391 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3392 'count=' || l_creation_date_tbl.count);
3393 END IF;
3394 IF l_creation_date_tbl.count >= 1
3395 THEN
3396 l_i := 1;
3397
3398 WHILE l_i <= l_creation_date_tbl.count
3399 LOOP
3400 l_resource_id := l_resource_id_tbl(l_i);
3401 l_creation_date := l_creation_date_tbl(l_i);
3402
3403 IF l_resource_id = l_assign_to_salesforce_id AND
3404 SYSDATE > l_creation_date+0.0001
3405 THEN
3406 -- There's no record inserted for this transaction.
3407 IF (AS_DEBUG_LOW_ON) THEN
3408 AS_UTILITY_PVT.Debug_Message(
3409 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3410 'same owner, no change');
3411 END IF;
3412 l_owner_changed_flag := 'N';
3413 EXIT;
3414 END IF;
3415 IF l_resource_id <> l_assign_to_salesforce_id
3416 THEN
3417 -- User manually sets the owner.
3418 IF (AS_DEBUG_LOW_ON) THEN
3419 AS_UTILITY_PVT.Debug_Message(
3420 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3421 'different owner');
3422 END IF;
3423 EXIT;
3424 END IF;
3425
3426 l_i := l_i + 1;
3427 END LOOP;
3428
3429 IF l_resource_id <> l_assign_to_salesforce_id
3430 THEN
3431 l_owner_changed_flag := 'Y';
3432 END IF;
3433 END IF;
3434 END IF;
3435
3436 IF (AS_DEBUG_LOW_ON) THEN
3437 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3438 'open status flag=' || l_open_status_flag);
3439 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3440 'assign_to_sf_id=' || l_assign_to_salesforce_id);
3441 END IF;
3442
3443 -- swkhanna Jun17,03
3444
3445 IF l_rank_changed_flag = 'N'
3446 THEN
3447 --dbms_output.put_line('SWKHANNA l_rank_changed_flag:'||l_rank_changed_flag);
3448 -- Get the previous rank
3449 OPEN C_Get_Log(P_Sales_Lead_Id);
3450 FETCH C_Get_Log BULK COLLECT INTO
3451 l_creation_date_tbl, l_resource_id_tbl, l_rank_id_tbl;
3452 CLOSE C_Get_Log;
3453
3454 IF (AS_DEBUG_LOW_ON) AND l_creation_date_tbl.count>=1 THEN
3455 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3456 'count=' || l_creation_date_tbl.count);
3457 END IF;
3458 IF l_creation_date_tbl.count >= 1
3459 THEN
3460 l_i := 1;
3461
3462 WHILE l_i <= l_creation_date_tbl.count
3463 LOOP
3464 l_resource_id := l_resource_id_tbl(l_i);
3465 l_rank_id := l_rank_id_tbl(l_i);
3466 l_creation_date := l_creation_date_tbl(l_i);
3467
3468 IF l_rank_id = l_lead_rank_id AND
3469 SYSDATE > l_creation_date+0.0001
3470 THEN
3471 -- There's no record inserted for this transaction.
3472 IF (AS_DEBUG_LOW_ON) THEN
3473 AS_UTILITY_PVT.Debug_Message(
3474 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3475 'same rank, no change');
3476 --dbms_output.put_line('SWKHANNA same rank');
3477 END IF;
3478 l_rank_changed_flag := 'N';
3479 EXIT;
3480 END IF;
3481 IF l_rank_id <> l_lead_rank_id
3482 THEN
3483 -- User manually sets the owner.
3484 IF (AS_DEBUG_LOW_ON) THEN
3485 AS_UTILITY_PVT.Debug_Message(
3486 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3487 'different rank');
3488 --dbms_output.put_line('SWKHANNA different rank');
3489 END IF;
3490 EXIT;
3491 END IF;
3492
3493 l_i := l_i + 1;
3494 END LOOP;
3495
3496 IF l_rank_id <> l_lead_rank_id
3497 THEN
3498 l_rank_changed_flag := 'Y';
3499 END IF;
3500 END IF;
3501 END IF;
3502 -- swkhanna
3503 -- 9/9/03 SWKHANNA Lead Upgrade/Downgrade Functionality
3504 if l_owner_changed_flag = 'Y' then
3505 l_attribute_changed := 'OWNER';
3506 end if;
3507
3508 if l_rank_changed_flag = 'Y' then
3509 l_attribute_changed := 'RANK';
3510 end if;
3511
3512 IF l_open_status_flag = 'Y' AND l_assign_to_salesforce_id IS NOT NULL AND
3513 --l_owner_changed_flag = 'Y' AND
3514 (l_owner_changed_flag = 'Y' OR l_rank_changed_flag = 'Y') AND
3515 l_lead_engines_out_rec.qualified_flag = 'Y'
3516 THEN
3517 aml_monitor_wf.launch_monitor(
3518 P_Api_Version_Number => 2.0,
3519 P_Init_Msg_List => FND_API.G_FALSE,
3520 p_commit => FND_API.G_FALSE,
3521 P_Sales_Lead_Id => p_sales_lead_id,
3522 P_Changed_From_stage => 'ASSIGNED_DATE',
3523 P_Lead_Action => 'UPDATE',
3524 -- 9/9/03 SWKHANNA -- added for Lead Upgrade/Downgrade
3525 P_Attribute_Changed => l_attribute_changed,
3526 X_Return_Status => x_return_status,
3527 X_Msg_Count => x_msg_count,
3528 X_Msg_Data => x_msg_data);
3529
3530 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3531 RAISE FND_API.G_EXC_ERROR;
3532 END IF;
3533 END IF;
3534
3535 -- ER 3052066
3536 -- Leave a record for TAP New mode
3537 -- Used to sync TRANS and NM_TRANS table
3538 BEGIN
3539 INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
3540 customer_id, address_id, sales_lead_id, last_update_date,
3541 last_updated_by, creation_date, created_by,
3542 last_update_login, change_type, delete_flag, insert_flag,
3543 processed_flag)
3544 VALUES
3545 (l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
3546 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
3547 FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
3548 'Y');
3549 EXCEPTION
3550 WHEN OTHERS THEN
3551 UPDATE AS_CHANGED_ACCOUNTS_ALL
3552 SET processed_flag = 'Y'
3553 WHERE sales_lead_id = p_sales_lead_id;
3554 END;
3555 -- IF NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'Y') = 'Y'
3556 -- THEN
3557 -- 1. If AS_ENABLE_LEAD_ONLINE_TAP is 'Y', delete record.
3558 -- 2. If AS_ENABLE_LEAD_ONLINE_TAP is 'N', still keep it in
3559 -- as_changed_accounts_all, so TAP New Mode will pick up this
3560 -- record.
3561 -- DELETE FROM as_changed_accounts_all
3562 -- WHERE sales_lead_id = p_sales_lead_id;
3563 -- IF (AS_DEBUG_LOW_ON) THEN
3564 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3565 -- 'Delete as_changed_accounts_all record');
3566 -- END IF;
3567 -- END IF;
3568
3569 IF l_return_status = 'W'
3570 THEN
3571 x_return_status := 'W';
3572 END IF;
3573
3574 --
3575 -- END of API body
3576 --
3577
3578 -- Standard check for p_commit
3579 IF FND_API.to_Boolean( p_commit )
3580 THEN
3581 COMMIT WORK;
3582 END IF;
3583
3584 -- Debug Message
3585 IF (AS_DEBUG_LOW_ON) THEN
3586 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3587 'PVT: ' || l_api_name || ' End');
3588 END IF;
3589
3590 -- Standard call to get message count and IF count is 1, get message info.
3591 IF x_return_status = 'W'
3592 THEN
3593 FND_MSG_PUB.Count_And_Get
3594 (
3595 p_encoded => FND_API.G_FALSE,
3596 p_count => x_msg_count,
3597 p_data => x_msg_data );
3598 ELSE
3599 FND_MSG_PUB.Count_And_Get
3600 ( p_count => x_msg_count,
3601 p_data => x_msg_data );
3602 END IF;
3603
3604 EXCEPTION
3605
3606 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
3607 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
3608
3609 WHEN FND_API.G_EXC_ERROR THEN
3610 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3611 P_API_NAME => L_API_NAME
3612 ,P_PKG_NAME => G_PKG_NAME
3613 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3614 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3615 ,X_MSG_COUNT => X_MSG_COUNT
3616 ,X_MSG_DATA => X_MSG_DATA
3617 ,X_RETURN_STATUS => X_RETURN_STATUS);
3618
3619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3620 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3621 P_API_NAME => L_API_NAME
3622 ,P_PKG_NAME => G_PKG_NAME
3623 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3624 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3625 ,X_MSG_COUNT => X_MSG_COUNT
3626 ,X_MSG_DATA => X_MSG_DATA
3627 ,X_RETURN_STATUS => X_RETURN_STATUS);
3628
3629 WHEN OTHERS THEN
3630 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3631 P_API_NAME => L_API_NAME
3632 ,P_PKG_NAME => G_PKG_NAME
3633 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3634 ,P_SQLCODE => SQLCODE
3635 ,P_SQLERRM => SQLERRM
3636 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3637 ,X_MSG_COUNT => X_MSG_COUNT
3638 ,X_MSG_DATA => X_MSG_DATA
3639 ,X_RETURN_STATUS => X_RETURN_STATUS);
3640 END Lead_Process_After_Update;
3641
3642
3643
3644
3645
3646
3647 END AS_SALES_LEAD_ENGINE_PVT;