[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.4 2011/09/29 10:54:32 snsarava ship $ */
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 -- Added for bug 7654339
2580 l_freeze_flag VARCHAR2(1);
2581 l_created_by_tap_flag VARCHAR2(1);
2582
2583 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
2584 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
2585 SL.ASSIGN_TO_SALESFORCE_ID, SL.STATUS_CODE
2586 FROM AS_SALES_LEADS SL
2587 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
2588
2589 CURSOR C_Get_Person(C_Resource_Id NUMBER) IS
2590 SELECT source_id
2591 FROM jtf_rs_resource_extns
2592 WHERE resource_id = c_resource_id;
2593
2594 CURSOR C_Get_Open_Status_Flag(C_Status_Code VARCHAR2) IS
2595 SELECT opp_open_status_flag
2596 FROM as_statuses_b
2597 WHERE status_code = c_status_code
2598 AND lead_flag = 'Y';
2599
2600 -- Below two cursors added for bug 7654339
2601 CURSOR C_Check_Freeze(c_sales_lead_id NUMBER) IS
2602 SELECT acc.freeze_flag
2603 FROM as_accesses_all acc
2604 WHERE acc.sales_lead_id = c_sales_lead_id
2605 AND acc.owner_flag = 'Y';
2606
2607 CURSOR C_Check_TAP(c_sales_lead_id NUMBER) IS
2608 SELECT 'Y'
2609 FROM as_accesses_all acc
2610 WHERE acc.sales_lead_id = c_sales_lead_id
2611 AND acc.created_by_tap_flag = 'Y';
2612
2613 BEGIN
2614 -- Standard Start of API savepoint
2615 SAVEPOINT LEAD_PROCESS_AFTER_CREATE_PVT;
2616
2617 -- Standard call to check for call compatibility.
2618 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2619 p_api_version_number,
2620 l_api_name,
2621 G_PKG_NAME)
2622 THEN
2623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2624 END IF;
2625
2626 -- Initialize message list IF p_init_msg_list is set to TRUE.
2627 IF FND_API.to_Boolean( p_init_msg_list )
2628 THEN
2629 FND_MSG_PUB.initialize;
2630 END IF;
2631
2632 -- Debug Message
2633 IF (AS_DEBUG_LOW_ON) THEN
2634 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2635 'PVT:' || l_api_name || ' Start');
2636 END IF;
2637
2638 -- Initialize API return status to SUCCESS
2639 x_return_status := FND_API.G_RET_STS_SUCCESS;
2640
2641 --
2642 -- Api body
2643 --
2644 -- ******************************************************************
2645 -- Validate Environment
2646 -- ******************************************************************
2647
2648 IF FND_GLOBAL.User_Id IS NULL
2649 THEN
2650 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2651 THEN
2652 AS_UTILITY_PVT.Set_Message(
2653 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2654 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
2655 p_token1 => 'PROFILE',
2656 p_token1_value => 'USER_ID');
2657 END IF;
2658 RAISE FND_API.G_EXC_ERROR;
2659 END IF;
2660
2661 IF (p_validation_level = fnd_api.g_valid_level_full)
2662 THEN
2663 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2664 p_api_version_number => 2.0
2665 ,p_init_msg_list => p_init_msg_list
2666 ,p_salesforce_id => P_Identity_Salesforce_Id
2667 ,p_admin_group_id => p_admin_group_id
2668 ,x_return_status => x_return_status
2669 ,x_msg_count => x_msg_count
2670 ,x_msg_data => x_msg_data
2671 ,x_sales_member_rec => l_identity_sales_member_rec);
2672
2673 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2674 RAISE FND_API.G_EXC_ERROR;
2675 END IF;
2676 END IF;
2677
2678 Run_Lead_Engines(
2679 P_Api_Version_Number => 2.0,
2680 P_Init_Msg_List => FND_API.G_FALSE,
2681 P_Commit => FND_API.G_FALSE,
2682 P_Validation_Level => P_Validation_Level,
2683 P_Admin_Group_Id => P_Admin_Group_Id,
2684 P_identity_salesforce_id => P_identity_salesforce_id,
2685 P_Salesgroup_id => P_Salesgroup_id,
2686 P_Sales_Lead_id => P_Sales_Lead_id,
2687 -- ckapoor Phase 2 filtering project 11.5.10
2688 -- P_Is_Create_Mode => 'Y',
2689
2690 X_Lead_Engines_Out_Rec => l_lead_engines_out_rec,
2691 X_Return_Status => x_return_status,
2692 X_Msg_Count => x_msg_count,
2693 X_Msg_Data => x_msg_data);
2694
2695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2696 RAISE FND_API.G_EXC_ERROR;
2697 END IF;
2698
2699 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
2700 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
2701 l_assign_to_salesforce_id, l_status_code;
2702 CLOSE C_Get_Lead_Info;
2703
2704 IF L_Lead_Engines_Out_Rec.sales_team_flag = 'Y'
2705 THEN
2706 -- not incubation lead
2707 As_Sales_Lead_Assign_PVT.Build_Lead_Sales_Team(
2708 P_Api_Version_Number => 2.0,
2709 P_Init_Msg_List => FND_API.G_FALSE,
2710 P_Commit => FND_API.G_FALSE,
2711 P_Validation_Level => P_Validation_Level,
2712 P_Admin_Group_Id => P_Admin_Group_Id,
2713 P_identity_salesforce_id => P_identity_salesforce_id,
2714 P_Salesgroup_id => P_Salesgroup_id,
2715 P_Sales_Lead_id => P_Sales_Lead_id,
2716 X_Request_id => l_request_id,
2717 X_Return_Status => x_return_status,
2718 X_Msg_Count => x_msg_count,
2719 X_Msg_Data => x_msg_data);
2720
2721 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724
2725 -- below code added for bug 7654339
2726 l_freeze_flag := 'N';
2727 l_created_by_tap_flag := 'N';
2728 OPEN C_Check_Freeze(p_sales_lead_id);
2729 FETCH C_Check_Freeze INTO l_freeze_flag;
2730 CLOSE C_Check_Freeze;
2731 OPEN C_Check_TAP(p_sales_lead_id);
2732 FETCH C_Check_TAP INTO l_created_by_tap_flag;
2733 CLOSE C_Check_TAP;
2734
2735 -- Condition modified for bug 7654339
2736 -- Creator to be deleted from Sales Team in Real Time TAP when there are winning resources from TAP
2737 --Code modified for bug12766399
2738 IF NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'N') = 'Y' AND
2739 l_created_by_tap_flag = 'Y' THEN
2740
2741 DELETE from as_accesses_all acc
2742 WHERE acc.sales_lead_id = p_sales_lead_id
2743 AND nvl(acc.freeze_flag,'N') = 'N'
2744 AND acc.created_by_tap_flag = 'N';
2745 END IF;
2746
2747 -- condition modified for bug 7654339
2748 IF NVL(l_freeze_flag,'N') = 'N' AND
2749 l_created_by_tap_flag = 'Y' AND
2750 NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'N') = 'Y' --Code modified for bug12766399
2751 THEN
2752 As_Sales_Lead_Assign_PVT.Find_Lead_Owner(
2753 p_sales_lead_id, p_salesgroup_id, l_request_id,P_identity_salesforce_id, --code added for bug12766399
2754 l_return_status, x_msg_count, x_msg_data);
2755
2756 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2757 RAISE fnd_api.g_exc_error;
2758 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2759 RAISE fnd_api.g_exc_unexpected_error;
2760 END IF;
2761 --code added/modified for bug12766399
2762 ELSIF NVL(l_freeze_flag,'N') = 'N' THEN
2763 As_Sales_Lead_Assign_PVT.Set_Default_Lead_Owner(
2764 p_sales_lead_id, p_salesgroup_id, l_request_id,P_identity_salesforce_id, --code added for bug12766399
2765 l_return_status, x_msg_count, x_msg_data);
2766
2767 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2768 RAISE fnd_api.g_exc_error;
2769 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2770 RAISE fnd_api.g_exc_unexpected_error;
2771 END IF;
2772
2773 ELSE
2774 As_Sales_Lead_Assign_PVT.Process_Access_Record(
2775 p_sales_lead_id, l_request_id);
2776 END IF;
2777
2778 ELSE
2779 -- incubation lead
2780 IF l_assign_to_salesforce_id IS NULL
2781 THEN
2782 AS_SALES_LEADS_PUB.ROUTE_LEAD_TO_MARKETING(
2783 P_Api_Version_Number => 2.0,
2784 P_Init_Msg_List => FND_API.G_FALSE,
2785 P_Commit => FND_API.G_FALSE,
2786 P_Validation_Level => P_Validation_Level,
2787 P_Admin_Group_Id => P_Admin_Group_Id,
2788 P_identity_salesforce_id => P_identity_salesforce_id,
2789 P_Sales_Lead_id => P_Sales_Lead_id,
2790 X_Return_Status => x_return_status,
2791 X_Msg_Count => x_msg_count,
2792 X_Msg_Data => x_msg_data);
2793
2794 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2795 RAISE FND_API.G_EXC_ERROR;
2796 END IF;
2797 END IF;
2798
2799 OPEN C_Get_Person(P_identity_salesforce_id);
2800 FETCH C_Get_Person INTO l_person_id;
2801 CLOSE C_Get_Person;
2802
2803 -- Add creator to sales team
2804 l_Sales_Team_Rec.last_update_date := SYSDATE;
2805 l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
2806 l_Sales_Team_Rec.creation_date := SYSDATE;
2807 l_Sales_Team_Rec.created_by := FND_GLOBAL.USER_ID;
2808 l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
2809 l_Sales_Team_Rec.customer_id := l_Customer_Id;
2810 l_Sales_Team_Rec.address_id := l_Address_Id;
2811 l_Sales_Team_Rec.salesforce_id := P_identity_salesforce_id;
2812 l_Sales_Team_Rec.person_id := l_person_id;
2813 l_Sales_Team_Rec.sales_group_id := P_salesgroup_id;
2814 l_Sales_Team_Rec.sales_lead_id := p_sales_lead_id;
2815 l_Sales_Team_Rec.team_leader_flag := 'Y';
2816 l_Sales_Team_Rec.owner_flag := 'N';
2817 l_Sales_Team_Rec.freeze_flag := 'Y';
2818 l_Sales_Team_Rec.reassign_flag := 'N';
2819 l_Sales_Team_Rec.created_by_TAP_flag := 'N';
2820
2821 As_Access_PVT.Create_SalesTeam( -- for creator
2822 P_Api_Version_Number => 2.0,
2823 P_Init_Msg_List => FND_API.G_FALSE,
2824 P_Commit => FND_API.G_FALSE,
2825 P_Validation_Level => P_Validation_Level,
2826 P_Access_Profile_Rec => NULL,
2827 P_Check_Access_Flag => 'N',
2828 P_Admin_Flag => P_Admin_Flag,
2829 P_Admin_Group_Id => P_Admin_Group_Id,
2830 P_identity_salesforce_id => P_identity_salesforce_id,
2831 p_sales_team_rec => l_sales_team_rec,
2832 X_Return_Status => x_return_status,
2833 X_Msg_Count => x_msg_count,
2834 X_Msg_Data => x_msg_data,
2835 X_Access_Id => l_access_id);
2836
2837 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2838 RAISE FND_API.G_EXC_ERROR;
2839 END IF;
2840 END IF;
2841
2842 IF (AS_DEBUG_LOW_ON) THEN
2843 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2844 'indirect?' || L_Lead_Engines_Out_Rec.indirect_channel_flag);
2845 END IF;
2846 IF L_Lead_Engines_Out_Rec.indirect_channel_flag = 'Y' AND
2847 L_Lead_Engines_Out_Rec.qualified_flag = 'Y' AND
2848 FND_PROFILE.Value('AS_AUTO_CONVERT_LEAD_OPP') = 'Y'
2849 THEN
2850 As_Sales_Lead_Opp_PVT.Create_Opportunity_For_Lead(
2851 P_Api_Version_Number => 2.0,
2852 P_Init_Msg_List => FND_API.G_FALSE,
2853 P_Commit => FND_API.G_FALSE,
2854 P_Validation_Level => P_Validation_Level,
2855 P_Check_Access_Flag => P_Check_Access_Flag,
2856 P_Admin_Flag => P_Admin_Flag,
2857 P_Admin_Group_Id => P_Admin_Group_Id,
2858 P_identity_salesforce_id => P_identity_salesforce_id,
2859 P_identity_Salesgroup_id => P_Salesgroup_id,
2860 P_Sales_Lead_Profile_Tbl => L_Sales_Lead_Profile_Tbl,
2861 P_Sales_Lead_id => P_Sales_Lead_id,
2862 P_OPP_STATUS => NULL,
2863 X_Return_Status => x_return_status,
2864 X_Msg_Count => x_msg_count,
2865 X_Msg_Data => x_msg_data,
2866 X_Opportunity_Id => l_opportunity_id);
2867
2868 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2869 RAISE FND_API.G_EXC_ERROR;
2870 END IF;
2871
2872 PV_BG_PARTNER_MATCHING_PUB.Start_Partner_Matching(
2873 P_Api_Version_Number => 2.0,
2874 P_Init_Msg_List => FND_API.G_FALSE,
2875 P_Commit => FND_API.G_FALSE,
2876 P_Validation_Level => P_Validation_Level,
2877 P_Admin_Group_Id => P_Admin_Group_Id,
2878 P_identity_salesforce_id => P_identity_salesforce_id,
2879 P_Salesgroup_id => P_Salesgroup_id,
2880 P_Lead_id => L_Opportunity_Id,
2881 X_Return_Status => x_return_status,
2882 X_Msg_Count => x_msg_count,
2883 X_Msg_Data => x_msg_data);
2884
2885 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2886 RAISE FND_API.G_EXC_ERROR;
2887 END IF;
2888 END IF;
2889
2890 -- IF l_referral_type IS NOT NULL
2891 -- THEN
2892 -- AS_SALES_LEAD_REFERRAL.Notify_Party(
2893 -- P_Api_Version => 2.0,
2894 -- P_Init_Msg_List => FND_API.G_FALSE,
2895 -- P_Commit => FND_API.G_FALSE,
2896 -- P_Validation_Level => P_Validation_Level,
2897 -- P_Lead_Id => p_sales_lead_id,
2898 -- P_Lead_Status => NULL,
2899 -- P_salesforce_id => P_identity_salesforce_id,
2900 -- p_overriding_usernames => l_overriding_usernames,
2901 -- X_Return_Status => x_return_status,
2902 -- X_Msg_Count => x_msg_count,
2903 -- X_Msg_Data => x_msg_data);
2904
2905 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2906 -- RAISE FND_API.G_EXC_ERROR;
2907 -- END IF;
2908 -- END IF;
2909
2910 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
2911 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
2912 l_assign_to_salesforce_id, l_status_code;
2913 CLOSE C_Get_Lead_Info;
2914
2915 OPEN C_Get_Open_Status_Flag(l_status_code);
2916 FETCH C_Get_Open_Status_Flag INTO l_open_status_flag;
2917 CLOSE C_Get_Open_Status_Flag;
2918
2919 IF (AS_DEBUG_LOW_ON) THEN
2920 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2921 'status_code=' || l_status_code);
2922 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2923 'open status flag=' || l_open_status_flag);
2924 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2925 'assign_to_sf_id=' || l_assign_to_salesforce_id);
2926 END IF;
2927
2928 IF l_open_status_flag = 'Y' AND l_assign_to_salesforce_id IS NOT NULL
2929 AND l_lead_engines_out_rec.qualified_flag = 'Y'
2930 THEN
2931 aml_monitor_wf.launch_monitor(
2932 P_Api_Version_Number => 2.0,
2933 P_Init_Msg_List => FND_API.G_FALSE,
2934 p_commit => FND_API.G_FALSE,
2935 P_Sales_Lead_Id => p_sales_lead_id,
2936 P_Changed_From_stage => 'CREATION_DATE',
2937 P_Lead_Action => 'CREATE',
2938 -- 9/9/03 SWKHANNA -- Added for Lead Upgrade/Downgrade Functionality
2939 P_Attribute_Changed => NULL,
2940 X_Return_Status => x_return_status,
2941 X_Msg_Count => x_msg_count,
2942 X_Msg_Data => x_msg_data);
2943
2944 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2945 RAISE FND_API.G_EXC_ERROR;
2946 END IF;
2947 END IF;
2948
2949 -- ER 3052066
2950 -- Leave a record for TAP New mode
2951 -- Used to sync TRANS and NM_TRANS table
2952 BEGIN
2953 INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
2954 customer_id, address_id, sales_lead_id, last_update_date,
2955 last_updated_by, creation_date, created_by,
2956 last_update_login, change_type, delete_flag, insert_flag,
2957 processed_flag)
2958 VALUES
2959 (l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
2960 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
2961 FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
2962 'Y');
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 UPDATE AS_CHANGED_ACCOUNTS_ALL
2966 SET processed_flag = 'Y'
2967 WHERE sales_lead_id = p_sales_lead_id;
2968 END;
2969 -- IF NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'Y') = 'Y'
2970 -- THEN
2971 -- 1. If AS_ENABLE_LEAD_ONLINE_TAP is 'Y', update record.
2972 -- 2. If AS_ENABLE_LEAD_ONLINE_TAP is 'N', still keep it in
2973 -- as_changed_accounts_all, so TAP New Mode will pick up this
2974 -- record.
2975 -- DELETE FROM as_changed_accounts_all
2976 -- WHERE sales_lead_id = p_sales_lead_id;
2977 -- IF (AS_DEBUG_LOW_ON) THEN
2978 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2979 -- 'Delete as_changed_accounts_all record');
2980 -- END IF;
2981 -- UPDATE as_changed_accounts_all
2982 -- SET processed_flag = 'Y'
2983 -- WHERE sales_lead_id = p_sales_lead_id;
2984 -- END IF;
2985
2986 IF l_return_status = 'W'
2987 THEN
2988 x_return_status := 'W';
2989 END IF;
2990
2991 --
2992 -- END of API body
2993 --
2994
2995 -- Standard check for p_commit
2996 IF FND_API.to_Boolean( p_commit )
2997 THEN
2998 COMMIT WORK;
2999 END IF;
3000
3001 -- Debug Message
3002 IF (AS_DEBUG_LOW_ON) THEN
3003 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3004 'PVT: ' || l_api_name || ' End');
3005 END IF;
3006
3007 -- Standard call to get message count and IF count is 1, get message info.
3008 IF x_return_status = 'W'
3009 THEN
3010 FND_MSG_PUB.Count_And_Get
3011 (
3012 p_encoded => FND_API.G_FALSE,
3013 p_count => x_msg_count,
3014 p_data => x_msg_data );
3015 ELSE
3016 FND_MSG_PUB.Count_And_Get
3017 ( p_count => x_msg_count,
3018 p_data => x_msg_data );
3019 END IF;
3020
3021 EXCEPTION
3022
3023 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
3024 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
3025
3026 WHEN FND_API.G_EXC_ERROR THEN
3027 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3028 P_API_NAME => L_API_NAME
3029 ,P_PKG_NAME => G_PKG_NAME
3030 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3031 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3032 ,X_MSG_COUNT => X_MSG_COUNT
3033 ,X_MSG_DATA => X_MSG_DATA
3034 ,X_RETURN_STATUS => X_RETURN_STATUS);
3035
3036 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3037 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3038 P_API_NAME => L_API_NAME
3039 ,P_PKG_NAME => G_PKG_NAME
3040 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3041 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3042 ,X_MSG_COUNT => X_MSG_COUNT
3043 ,X_MSG_DATA => X_MSG_DATA
3044 ,X_RETURN_STATUS => X_RETURN_STATUS);
3045
3046 WHEN OTHERS THEN
3047 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3048 P_API_NAME => L_API_NAME
3049 ,P_PKG_NAME => G_PKG_NAME
3050 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3051 ,P_SQLCODE => SQLCODE
3052 ,P_SQLERRM => SQLERRM
3053 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3054 ,X_MSG_COUNT => X_MSG_COUNT
3055 ,X_MSG_DATA => X_MSG_DATA
3056 ,X_RETURN_STATUS => X_RETURN_STATUS);
3057 END Lead_Process_After_Create;
3058
3059
3060 PROCEDURE Lead_Process_After_Update(
3061 P_Api_Version_Number IN NUMBER,
3062 P_Init_Msg_List IN VARCHAR2,
3063 p_Commit IN VARCHAR2,
3064 p_Validation_Level IN NUMBER,
3065 P_Check_Access_Flag IN VARCHAR2,
3066 p_Admin_Flag IN VARCHAR2,
3067 P_Admin_Group_Id IN NUMBER,
3068 P_identity_salesforce_id IN NUMBER,
3069 P_Salesgroup_id IN NUMBER,
3070 P_Sales_Lead_Id IN NUMBER,
3071 X_Return_Status OUT NOCOPY VARCHAR2,
3072 X_Msg_Count OUT NOCOPY NUMBER,
3073 X_Msg_Data OUT NOCOPY VARCHAR2
3074 )
3075 IS
3076 l_api_name CONSTANT VARCHAR2(30)
3077 := 'Lead_Process_After_Update';
3078 l_api_version_number CONSTANT NUMBER := 2.0;
3079 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
3080 l_auto_run_lead_engines VARCHAR2(1);
3081 l_lead_engines_out_rec AS_SALES_LEADS_PUB.Lead_Engines_Out_Rec_Type;
3082 L_Sales_Lead_Profile_Tbl AS_UTILITY_PUB.Profile_Tbl_Type;
3083 l_opportunity_id NUMBER;
3084 l_customer_id NUMBER;
3085 l_address_id NUMBER;
3086 l_referral_type VARCHAR2(30);
3087 l_assign_to_salesforce_id NUMBER;
3088 l_status_code VARCHAR2(30);
3089 l_reject_reason_code VARCHAR2(30);
3090 l_access_id NUMBER;
3091 l_person_id NUMBER;
3092 l_request_id NUMBER;
3093 l_sales_team_rec AS_ACCESS_PVT.sales_team_rec_type;
3094 -- l_overriding_usernames AS_SALES_LEAD_REFERRAL.t_overriding_usernames;
3095 l_owner_exists_flag VARCHAR2(1) := 'N';
3096 l_owner_changed_flag VARCHAR2(1) := 'N';
3097 l_open_status_flag VARCHAR2(1);
3098 l_creation_date_tbl JTF_DATE_TABLE;
3099 l_resource_id_tbl JTF_NUMBER_TABLE;
3100 l_creation_date DATE;
3101 l_resource_id NUMBER;
3102 l_i NUMBER;
3103 l_return_status VARCHAR2(1);
3104
3105 -- swkhanna Jun17,03
3106 l_rank_changed_flag VARCHAR2(1) := 'N';
3107 l_lead_rank_id NUMBER;
3108 l_rank_id NUMBER;
3109 l_rank_id_tbl JTF_NUMBER_TABLE;
3110 -- 9/9/03 SWKHANNA
3111 l_attribute_changed VARCHAR2(60);
3112
3113 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
3114 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
3115 SL.ASSIGN_TO_SALESFORCE_ID, SL.QUALIFIED_FLAG,
3116 SL.LEAD_RANK_ID, SL.CHANNEL_CODE, SL.STATUS_CODE,
3117 SL.REJECT_REASON_CODE
3118 FROM AS_SALES_LEADS SL
3119 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
3120
3121 -- Retrieve channel type
3122 CURSOR c_get_indirect_channel_flag(c_channel_code VARCHAR2) IS
3123 SELECT NVL(channel.indirect_channel_flag, 'N')
3124 FROM pv_channel_types channel
3125 WHERE channel.channel_lookup_code = c_channel_code;
3126
3127 -- Check whether owner exists or not
3128 CURSOR c_check_owner_exists(c_sales_lead_id NUMBER) IS
3129 SELECT 'Y'
3130 FROM as_accesses_all acc
3131 WHERE acc.sales_lead_id = c_sales_lead_id
3132 AND acc.owner_flag = 'Y';
3133
3134 CURSOR C_Get_Person(C_Resource_Id NUMBER) IS
3135 SELECT source_id
3136 FROM jtf_rs_resource_extns
3137 WHERE resource_id = c_resource_id;
3138
3139 CURSOR C_Get_Open_Status_Flag(c_sales_lead_id NUMBER) IS
3140 SELECT lead.status_code, sta.opp_open_status_flag
3141 FROM as_statuses_b sta, as_sales_leads lead
3142 WHERE lead.sales_lead_id = c_sales_lead_id
3143 AND lead.status_code = sta.status_code
3144 AND sta.lead_flag = 'Y';
3145
3146
3147 -- swkhanna Jun17,03
3148 -- also added lead_rank_id in select clause
3149 CURSOR C_Get_Log(C_Sales_Lead_Id NUMBER) IS
3150 SELECT creation_date, assign_to_salesforce_id, lead_rank_id
3151 FROM as_sales_leads_log
3152 WHERE sales_lead_id = c_sales_lead_id
3153 ORDER BY log_id DESC;
3154
3155 BEGIN
3156 -- Standard Start of API savepoint
3157 SAVEPOINT LEAD_PROCESS_AFTER_UPDATE_PVT;
3158
3159 -- Standard call to check for call compatibility.
3160 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3161 p_api_version_number,
3162 l_api_name,
3163 G_PKG_NAME)
3164 THEN
3165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3166 END IF;
3167
3168 -- Initialize message list IF p_init_msg_list is set to TRUE.
3169 IF FND_API.to_Boolean( p_init_msg_list )
3170 THEN
3171 FND_MSG_PUB.initialize;
3172 END IF;
3173
3174 -- Debug Message
3175 IF (AS_DEBUG_LOW_ON) THEN
3176 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3177 'PVT:' || l_api_name || ' Start');
3178 END IF;
3179
3180 -- Initialize API return status to SUCCESS
3181 x_return_status := FND_API.G_RET_STS_SUCCESS;
3182
3183 --
3184 -- Api body
3185 --
3186 -- ******************************************************************
3187 -- Validate Environment
3188 -- ******************************************************************
3189
3190 IF FND_GLOBAL.User_Id IS NULL
3191 THEN
3192 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3193 THEN
3194 AS_UTILITY_PVT.Set_Message(
3195 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3196 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
3197 p_token1 => 'PROFILE',
3198 p_token1_value => 'USER_ID');
3199 END IF;
3200 RAISE FND_API.G_EXC_ERROR;
3201 END IF;
3202
3203 IF (p_validation_level = fnd_api.g_valid_level_full)
3204 THEN
3205 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
3206 p_api_version_number => 2.0
3207 ,p_init_msg_list => p_init_msg_list
3208 ,p_salesforce_id => P_Identity_Salesforce_Id
3209 ,p_admin_group_id => p_admin_group_id
3210 ,x_return_status => x_return_status
3211 ,x_msg_count => x_msg_count
3212 ,x_msg_data => x_msg_data
3213 ,x_sales_member_rec => l_identity_sales_member_rec);
3214
3215 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3216 RAISE FND_API.G_EXC_ERROR;
3217 END IF;
3218 END IF;
3219
3220 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
3221 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
3222 l_assign_to_salesforce_id,
3223 l_lead_engines_out_rec.qualified_flag,
3224 l_lead_engines_out_rec.lead_rank_id,
3225 l_lead_engines_out_rec.channel_code,
3226 l_status_code, l_reject_reason_code;
3227 CLOSE C_Get_Lead_Info;
3228
3229 -- Profile OS: Auto Run Lead Engines While Update
3230 l_auto_run_lead_engines := FND_PROFILE.Value('AS_AUTO_RUN_LEAD_ENGINES');
3231 IF (AS_DEBUG_LOW_ON) THEN
3232 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3233 'auto run engine=' || l_auto_run_lead_engines);
3234 END IF;
3235 IF NVL(l_auto_run_lead_engines, 'N') = 'Y'
3236 THEN
3237 Run_Lead_Engines(
3238 P_Api_Version_Number => 2.0,
3239 P_Init_Msg_List => FND_API.G_FALSE,
3240 P_Commit => FND_API.G_FALSE,
3241 P_Validation_Level => P_Validation_Level,
3242 P_Admin_Group_Id => P_Admin_Group_Id,
3243 P_identity_salesforce_id => P_identity_salesforce_id,
3244 P_Salesgroup_id => P_Salesgroup_id,
3245 P_Sales_Lead_id => P_Sales_Lead_id,
3246 -- ckapoor Phase 2 filtering project 11.5.10
3247 -- P_Is_Create_Mode => 'N',
3248
3249 X_Lead_Engines_Out_Rec => l_lead_engines_out_rec,
3250 X_Return_Status => x_return_status,
3251 X_Msg_Count => x_msg_count,
3252 X_Msg_Data => x_msg_data);
3253
3254 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3255 RAISE FND_API.G_EXC_ERROR;
3256 END IF;
3257
3258 ELSE
3259 OPEN c_get_indirect_channel_flag(l_lead_engines_out_rec.channel_code);
3260 FETCH c_get_indirect_channel_flag INTO
3261 l_lead_engines_out_rec.indirect_channel_flag;
3262 CLOSE c_get_indirect_channel_flag;
3263
3264 IF l_lead_engines_out_rec.channel_code =
3265 FND_PROFILE.Value('AS_LEAD_INCUBATION_CHANNEL')
3266 THEN
3267 l_lead_engines_out_rec.sales_team_flag := 'N';
3268 ELSE
3269 l_lead_engines_out_rec.sales_team_flag := 'Y';
3270 END IF;
3271 END IF;
3272
3273 OPEN c_check_owner_exists(p_sales_lead_id);
3274 FETCH c_check_owner_exists INTO l_owner_exists_flag;
3275 CLOSE c_check_owner_exists;
3276
3277 IF L_Lead_Engines_Out_Rec.sales_team_flag = 'Y'
3278 THEN
3279 -- not incubation lead
3280 As_Sales_Lead_Assign_PVT.Rebuild_Lead_Sales_Team(
3281 P_Api_Version_Number => 2.0,
3282 P_Init_Msg_List => FND_API.G_FALSE,
3283 P_Commit => FND_API.G_FALSE,
3284 P_Validation_Level => P_Validation_Level,
3285 P_Admin_Group_Id => P_Admin_Group_Id,
3286 P_identity_salesforce_id => P_identity_salesforce_id,
3287 P_Salesgroup_id => P_Salesgroup_id,
3288 P_Sales_Lead_id => P_Sales_Lead_id,
3289 X_Request_id => l_request_id,
3290 X_Return_Status => x_return_status,
3291 X_Msg_Count => x_msg_count,
3292 X_Msg_Data => x_msg_data);
3293
3294 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3295 RAISE FND_API.G_EXC_ERROR;
3296 END IF;
3297
3298 -- Check owner again here because Rebuild_Lead_Sales_Team may
3299 -- remove owner in as_accesses_all
3300 l_owner_exists_flag := 'N';
3301 OPEN c_check_owner_exists(p_sales_lead_id);
3302 FETCH c_check_owner_exists INTO l_owner_exists_flag;
3303 CLOSE c_check_owner_exists;
3304
3305 IF (AS_DEBUG_LOW_ON) THEN
3306 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3307 'assign to=' || l_assign_to_salesforce_id);
3308 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3309 'reject reason=' || l_reject_reason_code);
3310 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3311 'owner exist?' || l_owner_exists_flag);
3312 END IF;
3313 IF l_assign_to_salesforce_id IS NULL OR
3314 l_reject_reason_code IS NOT NULL OR
3315 l_owner_exists_flag = 'N'
3316 THEN
3317 As_Sales_Lead_Assign_PVT.Find_Lead_Owner(
3318 p_sales_lead_id, p_salesgroup_id, l_request_id,P_identity_salesforce_id, --code added for bug12766399
3319 l_return_status, x_msg_count, x_msg_data);
3320
3321 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3322 RAISE fnd_api.g_exc_error;
3323 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3324 RAISE fnd_api.g_exc_unexpected_error;
3325 END IF;
3326 l_owner_changed_flag := 'Y';
3327 ELSE
3328 As_Sales_Lead_Assign_PVT.Process_Access_Record(
3329 p_sales_lead_id, l_request_id);
3330 END IF;
3331 ELSE
3332 -- lead with incubation channel
3333 AS_SALES_LEADS_PUB.ROUTE_LEAD_TO_MARKETING(
3334 P_Api_Version_Number => 2.0,
3335 P_Init_Msg_List => FND_API.G_FALSE,
3336 P_Commit => FND_API.G_FALSE,
3337 P_Validation_Level => P_Validation_Level,
3338 P_Admin_Group_Id => P_Admin_Group_Id,
3339 P_identity_salesforce_id => P_identity_salesforce_id,
3340 P_Sales_Lead_id => P_Sales_Lead_id,
3341 X_Return_Status => x_return_status,
3342 X_Msg_Count => x_msg_count,
3343 X_Msg_Data => x_msg_data);
3344
3345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3346 RAISE FND_API.G_EXC_ERROR;
3347 END IF;
3348 END IF;
3349
3350 IF L_Lead_Engines_Out_Rec.indirect_channel_flag = 'Y' AND
3351 FND_PROFILE.Value('AS_AUTO_CONVERT_LEAD_OPP') = 'Y' AND
3352 l_status_code <> 'CONVERTED_TO_OPPORTUNITY'
3353 THEN
3354 As_Sales_Lead_Opp_PVT.Create_Opportunity_For_Lead(
3355 P_Api_Version_Number => 2.0,
3356 P_Init_Msg_List => FND_API.G_FALSE,
3357 P_Commit => FND_API.G_FALSE,
3358 P_Validation_Level => P_Validation_Level,
3359 P_Check_Access_Flag => P_Check_Access_Flag,
3360 P_Admin_Flag => P_Admin_Flag,
3361 P_Admin_Group_Id => P_Admin_Group_Id,
3362 P_identity_salesforce_id => P_identity_salesforce_id,
3363 P_identity_Salesgroup_id => P_Salesgroup_id,
3364 P_Sales_Lead_Profile_Tbl => L_Sales_Lead_Profile_Tbl,
3365 P_Sales_Lead_id => P_Sales_Lead_id,
3366 P_OPP_STATUS => NULL,
3367 X_Return_Status => x_return_status,
3368 X_Msg_Count => x_msg_count,
3369 X_Msg_Data => x_msg_data,
3370 X_Opportunity_Id => l_opportunity_id);
3371
3372 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3373 RAISE FND_API.G_EXC_ERROR;
3374 END IF;
3375
3376 PV_BG_PARTNER_MATCHING_PUB.Start_Partner_Matching(
3377 P_Api_Version_Number => 2.0,
3378 P_Init_Msg_List => FND_API.G_FALSE,
3379 P_Commit => FND_API.G_FALSE,
3380 P_Validation_Level => P_Validation_Level,
3381 P_Admin_Group_Id => P_Admin_Group_Id,
3382 P_identity_salesforce_id => P_identity_salesforce_id,
3383 P_Salesgroup_id => P_Salesgroup_id,
3384 P_Lead_id => L_Opportunity_Id,
3385 X_Return_Status => x_return_status,
3386 X_Msg_Count => x_msg_count,
3387 X_Msg_Data => x_msg_data);
3388
3389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3390 RAISE FND_API.G_EXC_ERROR;
3391 END IF;
3392 END IF;
3393
3394 -- IF l_referral_type IS NOT NULL
3395 -- THEN
3396 -- AS_SALES_LEAD_REFERRAL.Notify_Party(
3397 -- P_Api_Version => 2.0,
3398 -- P_Init_Msg_List => FND_API.G_FALSE,
3399 -- P_Commit => FND_API.G_FALSE,
3400 -- P_Validation_Level => P_Validation_Level,
3401 -- P_Lead_Id => p_sales_lead_id,
3402 -- P_Lead_Status => NULL,
3403 -- P_salesforce_id => P_identity_salesforce_id,
3404 -- p_overriding_usernames => l_overriding_usernames,
3405 -- X_Return_Status => x_return_status,
3406 -- X_Msg_Count => x_msg_count,
3407 -- X_Msg_Data => x_msg_data);
3408
3409 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3410 -- RAISE FND_API.G_EXC_ERROR;
3411 -- END IF;
3412 -- END IF;
3413
3414 OPEN C_Get_Open_Status_Flag(p_sales_lead_id);
3415 FETCH C_Get_Open_Status_Flag INTO l_status_code, l_open_status_flag;
3416 CLOSE C_Get_Open_Status_Flag;
3417
3418 IF (AS_DEBUG_LOW_ON) THEN
3419 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3420 'status=' || l_status_code);
3421 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3422 'open?' || l_open_status_flag);
3423 END IF;
3424
3425 OPEN C_Get_Lead_Info(P_Sales_Lead_Id);
3426 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id, l_referral_type,
3427 l_assign_to_salesforce_id,
3428 l_lead_engines_out_rec.qualified_flag,
3429 -- l_lead_engines_out_rec.lead_rank_id,
3430 -- swkhanna Jun17,03
3431 l_lead_rank_id,
3432 l_lead_engines_out_rec.channel_code,
3433 l_status_code, l_reject_reason_code;
3434 CLOSE C_Get_Lead_Info;
3435
3436 IF l_owner_changed_flag = 'N'
3437 THEN
3438 -- Get the time when previous owner still owns the lead.
3439 OPEN C_Get_Log(P_Sales_Lead_Id);
3440 FETCH C_Get_Log BULK COLLECT INTO
3441 l_creation_date_tbl, l_resource_id_tbl, l_rank_id_tbl ;
3442 CLOSE C_Get_Log;
3443
3444 IF (AS_DEBUG_LOW_ON) AND l_creation_date_tbl.count>=1 THEN
3445 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3446 'count=' || l_creation_date_tbl.count);
3447 END IF;
3448 IF l_creation_date_tbl.count >= 1
3449 THEN
3450 l_i := 1;
3451
3452 WHILE l_i <= l_creation_date_tbl.count
3453 LOOP
3454 l_resource_id := l_resource_id_tbl(l_i);
3455 l_creation_date := l_creation_date_tbl(l_i);
3456
3457 IF l_resource_id = l_assign_to_salesforce_id AND
3458 SYSDATE > l_creation_date+0.0001
3459 THEN
3460 -- There's no record inserted for this transaction.
3461 IF (AS_DEBUG_LOW_ON) THEN
3462 AS_UTILITY_PVT.Debug_Message(
3463 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3464 'same owner, no change');
3465 END IF;
3466 l_owner_changed_flag := 'N';
3467 EXIT;
3468 END IF;
3469 IF l_resource_id <> l_assign_to_salesforce_id
3470 THEN
3471 -- User manually sets the owner.
3472 IF (AS_DEBUG_LOW_ON) THEN
3473 AS_UTILITY_PVT.Debug_Message(
3474 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3475 'different owner');
3476 END IF;
3477 EXIT;
3478 END IF;
3479
3480 l_i := l_i + 1;
3481 END LOOP;
3482
3483 IF l_resource_id <> l_assign_to_salesforce_id
3484 THEN
3485 l_owner_changed_flag := 'Y';
3486 END IF;
3487 END IF;
3488 END IF;
3489
3490 IF (AS_DEBUG_LOW_ON) THEN
3491 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3492 'open status flag=' || l_open_status_flag);
3493 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3494 'assign_to_sf_id=' || l_assign_to_salesforce_id);
3495 END IF;
3496
3497 -- swkhanna Jun17,03
3498
3499 IF l_rank_changed_flag = 'N'
3500 THEN
3501 --dbms_output.put_line('SWKHANNA l_rank_changed_flag:'||l_rank_changed_flag);
3502 -- Get the previous rank
3503 OPEN C_Get_Log(P_Sales_Lead_Id);
3504 FETCH C_Get_Log BULK COLLECT INTO
3505 l_creation_date_tbl, l_resource_id_tbl, l_rank_id_tbl;
3506 CLOSE C_Get_Log;
3507
3508 IF (AS_DEBUG_LOW_ON) AND l_creation_date_tbl.count>=1 THEN
3509 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3510 'count=' || l_creation_date_tbl.count);
3511 END IF;
3512 IF l_creation_date_tbl.count >= 1
3513 THEN
3514 l_i := 1;
3515
3516 WHILE l_i <= l_creation_date_tbl.count
3517 LOOP
3518 l_resource_id := l_resource_id_tbl(l_i);
3519 l_rank_id := l_rank_id_tbl(l_i);
3520 l_creation_date := l_creation_date_tbl(l_i);
3521
3522 IF l_rank_id = l_lead_rank_id AND
3523 SYSDATE > l_creation_date+0.0001
3524 THEN
3525 -- There's no record inserted for this transaction.
3526 IF (AS_DEBUG_LOW_ON) THEN
3527 AS_UTILITY_PVT.Debug_Message(
3528 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3529 'same rank, no change');
3530 --dbms_output.put_line('SWKHANNA same rank');
3531 END IF;
3532 l_rank_changed_flag := 'N';
3533 EXIT;
3534 END IF;
3535 IF l_rank_id <> l_lead_rank_id
3536 THEN
3537 -- User manually sets the owner.
3538 IF (AS_DEBUG_LOW_ON) THEN
3539 AS_UTILITY_PVT.Debug_Message(
3540 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3541 'different rank');
3542 --dbms_output.put_line('SWKHANNA different rank');
3543 END IF;
3544 EXIT;
3545 END IF;
3546
3547 l_i := l_i + 1;
3548 END LOOP;
3549
3550 IF l_rank_id <> l_lead_rank_id
3551 THEN
3552 l_rank_changed_flag := 'Y';
3553 END IF;
3554 END IF;
3555 END IF;
3556 -- swkhanna
3557 -- 9/9/03 SWKHANNA Lead Upgrade/Downgrade Functionality
3558 if l_owner_changed_flag = 'Y' then
3559 l_attribute_changed := 'OWNER';
3560 end if;
3561
3562 if l_rank_changed_flag = 'Y' then
3563 l_attribute_changed := 'RANK';
3564 end if;
3565
3566 IF l_open_status_flag = 'Y' AND l_assign_to_salesforce_id IS NOT NULL AND
3567 --l_owner_changed_flag = 'Y' AND
3568 (l_owner_changed_flag = 'Y' OR l_rank_changed_flag = 'Y') AND
3569 l_lead_engines_out_rec.qualified_flag = 'Y'
3570 THEN
3571 aml_monitor_wf.launch_monitor(
3572 P_Api_Version_Number => 2.0,
3573 P_Init_Msg_List => FND_API.G_FALSE,
3574 p_commit => FND_API.G_FALSE,
3575 P_Sales_Lead_Id => p_sales_lead_id,
3576 P_Changed_From_stage => 'ASSIGNED_DATE',
3577 P_Lead_Action => 'UPDATE',
3578 -- 9/9/03 SWKHANNA -- added for Lead Upgrade/Downgrade
3579 P_Attribute_Changed => l_attribute_changed,
3580 X_Return_Status => x_return_status,
3581 X_Msg_Count => x_msg_count,
3582 X_Msg_Data => x_msg_data);
3583
3584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3585 RAISE FND_API.G_EXC_ERROR;
3586 END IF;
3587 END IF;
3588
3589 -- ER 3052066
3590 -- Leave a record for TAP New mode
3591 -- Used to sync TRANS and NM_TRANS table
3592 BEGIN
3593 INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
3594 customer_id, address_id, sales_lead_id, last_update_date,
3595 last_updated_by, creation_date, created_by,
3596 last_update_login, change_type, delete_flag, insert_flag,
3597 processed_flag)
3598 VALUES
3599 (l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
3600 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
3601 FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
3602 'Y');
3603 EXCEPTION
3604 WHEN OTHERS THEN
3605 UPDATE AS_CHANGED_ACCOUNTS_ALL
3606 SET processed_flag = 'Y'
3607 WHERE sales_lead_id = p_sales_lead_id;
3608 END;
3609 -- IF NVL(fnd_profile.value('AS_ENABLE_LEAD_ONLINE_TAP'), 'Y') = 'Y'
3610 -- THEN
3611 -- 1. If AS_ENABLE_LEAD_ONLINE_TAP is 'Y', delete record.
3612 -- 2. If AS_ENABLE_LEAD_ONLINE_TAP is 'N', still keep it in
3613 -- as_changed_accounts_all, so TAP New Mode will pick up this
3614 -- record.
3615 -- DELETE FROM as_changed_accounts_all
3616 -- WHERE sales_lead_id = p_sales_lead_id;
3617 -- IF (AS_DEBUG_LOW_ON) THEN
3618 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3619 -- 'Delete as_changed_accounts_all record');
3620 -- END IF;
3621 -- END IF;
3622
3623 IF l_return_status = 'W'
3624 THEN
3625 x_return_status := 'W';
3626 END IF;
3627
3628 --
3629 -- END of API body
3630 --
3631
3632 -- Standard check for p_commit
3633 IF FND_API.to_Boolean( p_commit )
3634 THEN
3635 COMMIT WORK;
3636 END IF;
3637
3638 -- Debug Message
3639 IF (AS_DEBUG_LOW_ON) THEN
3640 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3641 'PVT: ' || l_api_name || ' End');
3642 END IF;
3643
3644 -- Standard call to get message count and IF count is 1, get message info.
3645 IF x_return_status = 'W'
3646 THEN
3647 FND_MSG_PUB.Count_And_Get
3648 (
3649 p_encoded => FND_API.G_FALSE,
3650 p_count => x_msg_count,
3651 p_data => x_msg_data );
3652 ELSE
3653 FND_MSG_PUB.Count_And_Get
3654 ( p_count => x_msg_count,
3655 p_data => x_msg_data );
3656 END IF;
3657
3658 EXCEPTION
3659
3660 -- WHEN AS_SALES_LEADS_PUB.Filter_Exception THEN
3661 -- RAISE AS_SALES_LEADS_PUB.Filter_Exception;
3662
3663 WHEN FND_API.G_EXC_ERROR THEN
3664 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3665 P_API_NAME => L_API_NAME
3666 ,P_PKG_NAME => G_PKG_NAME
3667 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3668 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3669 ,X_MSG_COUNT => X_MSG_COUNT
3670 ,X_MSG_DATA => X_MSG_DATA
3671 ,X_RETURN_STATUS => X_RETURN_STATUS);
3672
3673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3674 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3675 P_API_NAME => L_API_NAME
3676 ,P_PKG_NAME => G_PKG_NAME
3677 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3678 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3679 ,X_MSG_COUNT => X_MSG_COUNT
3680 ,X_MSG_DATA => X_MSG_DATA
3681 ,X_RETURN_STATUS => X_RETURN_STATUS);
3682
3683 WHEN OTHERS THEN
3684 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3685 P_API_NAME => L_API_NAME
3686 ,P_PKG_NAME => G_PKG_NAME
3687 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3688 ,P_SQLCODE => SQLCODE
3689 ,P_SQLERRM => SQLERRM
3690 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3691 ,X_MSG_COUNT => X_MSG_COUNT
3692 ,X_MSG_DATA => X_MSG_DATA
3693 ,X_RETURN_STATUS => X_RETURN_STATUS);
3694 END Lead_Process_After_Update;
3695
3696
3697
3698
3699
3700
3701 END AS_SALES_LEAD_ENGINE_PVT;