[Home] [Help]
PACKAGE BODY: APPS.AML_INTERACTION_ENGINE
Source
1 PACKAGE BODY AML_INTERACTION_ENGINE as
2 /* $Header: amlitenb.pls 115.13 2004/05/07 23:37:12 solin ship $ */
3
4 --
5 -- HISTORY
6 -- 06/17/2003 SOLIN Created.
7 -- 11/05/2003 SOLIN Bug 3240753
8 -- Pass party_id, party_site_id to as_import_interface
9 -- 02/23/2004 SOLIN Bug 3454115
10 -- Add "AND aap.level_type_code = 'FAMILY'" in cursor
11 -- C_Get_Category
12 -- 04/27/2004 SOLIN Bug 3584079, 3583298
13 -- Join mtl_system_items_b to get uom_code.
14 -- Change rule exit condition.
15 --
16 -- FLOW
17 --
18 -- NOTES
19 -- The main package for the concurrent program "Run Interaction Engine to
20 -- Match or Create Leads"
21 --
22 /*-------------------------------------------------------------------------*
23 |
24 | PRIVATE CONSTANTS
25 |
26 *-------------------------------------------------------------------------*/
27
28 /*-------------------------------------------------------------------------*
29 |
30 | PRIVATE DATATYPES
31 |
32 *-------------------------------------------------------------------------*/
33
34 /*-------------------------------------------------------------------------*
35 |
36 | PRIVATE VARIABLES
37 |
38 *-------------------------------------------------------------------------*/
39 g_debug_flag VARCHAR2(1);
40
41
42 /*-------------------------------------------------------------------------*
43 |
44 | PRIVATE ROUTINES SPECIFICATION
45 |
46 *-------------------------------------------------------------------------*/
47
48 /*-------------------------------------------------------------------------*
49 |
50 | PUBLIC ROUTINES
51 |
52 *-------------------------------------------------------------------------*/
53 PROCEDURE AML_DEBUG(msg IN VARCHAR2);
54
55
56 /*-------------------------------------------------------------------------*
57 | PUBLIC ROUTINE
58 | Run_Interaction_Engine
59 |
60 | PURPOSE
61 | The main program to run interaction engine.
62 | Concurrent program finds interesting interactions and match them with
63 | leads. If there's any lead matched, bump up score and rerun rating engine.
64 | Otherwise, create a new lead.
65 |
66 | NOTES
67 |
68 | HISTORY
69 | 06/17/2003 SOLIN Created
70 *-------------------------------------------------------------------------*/
71
72 PROCEDURE Run_Interaction_Engine(
73 ERRBUF OUT NOCOPY VARCHAR2,
74 RETCODE OUT NOCOPY VARCHAR2,
75 p_debug_mode IN VARCHAR2,
76 p_trace_mode IN VARCHAR2)
77 IS
78 l_status BOOLEAN;
79 l_batch_id NUMBER;
80 l_last_interaction_id NUMBER;
81 l_lead_interaction_lookback NUMBER;
82 l_default_source_code VARCHAR2(30);
83 l_interaction_lookback NUMBER;
84 l_interaction_id_tbl JTF_NUMBER_TABLE;
85 l_source_code_tbl JTF_VARCHAR2_TABLE_100;
86 l_source_code_id_tbl JTF_NUMBER_TABLE;
87 l_source_code_for_id_tbl JTF_NUMBER_TABLE;
88 l_arc_source_code_for_tbl JTF_VARCHAR2_TABLE_100;
89 l_description_tbl JTF_VARCHAR2_TABLE_400;
90 l_party_id_tbl JTF_NUMBER_TABLE;
91 l_customer_id_tbl JTF_NUMBER_TABLE;
92 l_address_id_tbl JTF_NUMBER_TABLE;
93 l_contact_party_id_tbl JTF_NUMBER_TABLE;
94 l_process_rule_id_tbl JTF_NUMBER_TABLE;
95 l_rank_tbl JTF_NUMBER_TABLE;
96 l_save_profile BOOLEAN;
97 l_default_interaction_score NUMBER;
98 l_temp_interaction_score NUMBER;
99 l_get_score_flag VARCHAR2(1);
100 l_interaction_score NUMBER;
101 l_prev_rank NUMBER;
102 l_sales_lead_id NUMBER;
103 l_status_code VARCHAR2(30);
104 l_category_id NUMBER;
105 l_category_set_id NUMBER;
106 l_inventory_item_id NUMBER;
107 l_organization_id NUMBER;
108 l_uom_code VARCHAR2(3);
109 l_quantity NUMBER;
110 l_offer_id NUMBER;
111 l_sales_lead_line_id NUMBER;
112 l_lead_interaction_score NUMBER;
113 l_highest_score NUMBER;
114 l_check_rerun NUMBER;
115 l_identity_salesforce_id NUMBER;
116 l_salesgroup_id NUMBER;
117 l_interaction_lead_id NUMBER;
118 l_return_status VARCHAR2(1);
119 l_msg_count NUMBER;
120 l_msg_data VARCHAR2(4000);
121 l_import_interface_id NUMBER;
122 l_imp_lines_interface_id NUMBER;
123 l_response_interaction_score NUMBER;
124 l_interaction_score_threshold NUMBER;
125 l_run_import_flag VARCHAR2(1);
126 l_request_id NUMBER;
127 l_count NUMBER;
128 l_message VARCHAR2(2000);
129
130
131 CURSOR c_get_last_interaction_id(c_interaction_lookback NUMBER) IS
132 SELECT interaction_id
133 FROM jtf_ih_interactions
134 WHERE creation_date > (SYSDATE - c_interaction_lookback)
135 ORDER BY interaction_id;
136
137 CURSOR c_get_interactions(c_last_interaction_id NUMBER,
138 c_default_source_code VARCHAR2) IS
139 SELECT DISTINCT v.interaction_id, NVL(v.source_code, c_default_source_code),
140 amsc.source_code_id,
141 amsc.source_code_for_id, amsc.arc_source_code_for,
142 v.party_name, v.party_id, v.customer_id, v.address_id,
143 v.contact_party_id
144 FROM ams_source_codes amsc,
145 (
146 -- Interactions from OMO and customer is person or organization without
147 -- contact
148 SELECT interact.interaction_id, interact.source_code,
149 interact.source_code_id, party.party_name, party.party_type,
150 TO_NUMBER(NULL) party_id, interact.resource_id,
151 party.party_id customer_id, site.party_site_id address_id,
152 TO_NUMBER(NULL) contact_party_id
153 FROM jtf_ih_interactions interact, hz_parties party, hz_party_sites site
154 WHERE interact.party_id = party.party_id
155 AND interact.handler_id = 530 -- for Oracle Marketing
156 AND party.party_type IN ('PERSON', 'ORGANIZATION')
157 AND party.party_id = site.party_id(+)
158 AND site.identifying_address_flag(+) = 'Y'
159 UNION ALL
160 -- Interactions from OMO and customer is organization with contact
161 SELECT interact.interaction_id, interact.source_code,
162 interact.source_code_id, party.party_name, party.party_type,
163 interact.party_id, interact.resource_id,
164 rel.object_id customer_id, site.party_site_id address_id,
165 rel.subject_id contact_party_id
166 FROM jtf_ih_interactions interact, hz_parties party,
167 hz_relationships rel, hz_party_sites site
168 WHERE interact.party_id = party.party_id
169 AND interact.handler_id = 530 -- for Oracle Marketing
170 AND party.party_type = 'PARTY_RELATIONSHIP'
171 AND party.party_id = rel.party_id
172 AND rel.object_type = 'ORGANIZATION'
173 AND rel.status = 'A'
174 AND rel.object_id = site.party_id(+)
175 AND site.identifying_address_flag(+) = 'Y'
176 UNION ALL
177 -- Interactions not from OMO and customer is person or organization
178 -- without contact
179 SELECT interact.interaction_id, interact.source_code,
180 interact.source_code_id, party.party_name, party.party_type,
181 TO_NUMBER(NULL) party_id, interact.resource_id,
182 party.party_id customer_id, site.party_site_id address_id,
183 TO_NUMBER(NULL) contact_party_id
184 FROM jtf_ih_interactions interact, hz_parties party,
185 jtf_ih_activities acv, pv_process_rules_b rule,
186 pv_enty_select_criteria cra, pv_selected_attr_values val,
187 aml_business_event_types_b bet, hz_party_sites site
188 WHERE interact.party_id = party.party_id
189 AND interact.handler_id <> 530 -- for Oracle Marketing
190 AND party.party_type IN ('PERSON', 'ORGANIZATION')
191 AND party.party_id = site.party_id(+)
192 AND site.identifying_address_flag(+) = 'Y'
193 AND interact.interaction_id = acv.interaction_id
194 AND TO_CHAR(bet.business_event_type_id) = val.attribute_value
195 AND bet.action_id = acv.action_id
196 AND bet.action_item_id = acv.action_item_id
197 AND val.selection_criteria_id = cra.selection_criteria_id
198 AND cra.selection_type_code = 'CRITERION'
199 AND cra.process_rule_id = rule.process_rule_id
200 AND rule.process_type = 'LEAD_INTERACTION'
201 AND rule.status_code = 'ACTIVE'
202 AND SYSDATE BETWEEN rule.start_date AND rule.end_date
203 UNION ALL
204 -- Interactions not from OMO and customer is organization with contact
205 SELECT interact.interaction_id, interact.source_code,
206 interact.source_code_id, party.party_name, party.party_type,
207 interact.party_id, interact.resource_id,
208 rel.object_id customer_id, site.party_site_id address_id,
209 rel.subject_id contact_party_id
210 FROM jtf_ih_interactions interact, hz_parties party,
211 hz_relationships rel, hz_party_sites site, jtf_ih_activities acv,
212 pv_process_rules_b rule, pv_enty_select_criteria cra,
213 pv_selected_attr_values val, aml_business_event_types_b bet
214 WHERE interact.party_id = party.party_id
215 AND interact.handler_id <> 530 -- for Oracle Marketing
216 AND party.party_type = 'PARTY_RELATIONSHIP'
217 AND rel.object_id = site.party_id(+)
218 AND site.identifying_address_flag(+) = 'Y'
219 AND party.party_id = rel.party_id
220 AND rel.object_type = 'ORGANIZATION'
221 AND rel.status = 'A'
222 AND interact.interaction_id = acv.interaction_id
223 AND TO_CHAR(bet.business_event_type_id) = val.attribute_value
224 AND bet.action_id = acv.action_id
225 AND bet.action_item_id = acv.action_item_id
226 AND val.selection_criteria_id = cra.selection_criteria_id
227 AND cra.selection_type_code = 'CRITERION'
228 AND cra.process_rule_id = rule.process_rule_id
229 AND rule.process_type = 'LEAD_INTERACTION'
230 AND rule.status_code = 'ACTIVE'
231 AND SYSDATE BETWEEN rule.start_date AND rule.end_date) v
232 WHERE v.interaction_id > c_last_interaction_id
233 AND NVL(v.source_code, c_default_source_code) = amsc.source_code(+)
234 ORDER BY v.interaction_id;
235
236 CURSOR C_Get_Batch_ID IS
237 SELECT as_sl_imp_batch_s.nextval
238 FROM dual;
239
240 CURSOR C_Get_Matching_Rules(c_interaction_id NUMBER, c_source_code_id NUMBER,
241 c_address_id NUMBER) IS
242 SELECT rule.process_rule_id, rule.rank
243 FROM (
244 -- ----------------------------------------------------------------
245 -- Campaign
246 -------------------------------------------------------------------
247 SELECT DISTINCT a.process_rule_id, a.rank
248 FROM pv_process_rules_b a,
249 pv_enty_select_criteria b,
250 pv_selected_attr_values c
251 WHERE b.selection_type_code = 'INPUT_FILTER' AND
252 b.attribute_id = pv_check_match_pub.g_a_Campaign_ AND
253 a.process_type = 'LEAD_INTERACTION' AND
254 a.process_rule_id = b.process_rule_id AND
255 c_source_code_id IS NOT NULL AND
256 b.selection_criteria_id = c.selection_criteria_id(+) AND
257 ((b.operator = 'EQUALS' AND c.attribute_value = c_source_code_id) OR
258 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_source_code_id) OR
259 (b.operator = 'IS_NOT_NULL' AND c_source_code_id IS NOT NULL) OR
260 (b.operator = 'IS_NULL' AND c_source_code_id IS NULL))
261 UNION ALL
262 SELECT DISTINCT a.process_rule_id, a.rank
263 FROM pv_process_rules_b a,
264 pv_enty_select_criteria b,
265 pv_selected_attr_values c,
266 jtf_ih_activities d
267 WHERE b.selection_type_code = 'INPUT_FILTER' AND
268 b.attribute_id = pv_check_match_pub.g_a_Campaign_ AND
269 a.process_type = 'LEAD_INTERACTION' AND
270 a.process_rule_id = b.process_rule_id AND
271 c_source_code_id IS NULL AND
272 b.selection_criteria_id = c.selection_criteria_id(+) AND
273 d.interaction_id = c_interaction_id AND
274 ((b.operator = 'EQUALS' AND c.attribute_value = d.source_code_id) OR
275 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> d.source_code_id) OR
276 (b.operator = 'IS_NOT_NULL' AND d.source_code_id IS NOT NULL) OR
277 (b.operator = 'IS_NULL' AND d.source_code_id IS NULL))
278
279 -- ----------------------------------------------------------------
280 -- All
281 -------------------------------------------------------------------
282 UNION ALL
283 SELECT DISTINCT a.process_rule_id, a.rank
284 FROM pv_process_rules_b a,
285 pv_enty_select_criteria b
286 WHERE b.selection_type_code = 'INPUT_FILTER' AND
287 b.attribute_id = pv_check_match_pub.g_a_all AND
288 a.process_type = 'LEAD_INTERACTION' AND
289 a.process_rule_id = b.process_rule_id
290 -- -------------------------------------------------------------------
291 -- Country
292 -- -------------------------------------------------------------------
293 UNION ALL
294 SELECT DISTINCT a.process_rule_id, a.rank
295 FROM pv_process_rules_b a,
296 pv_enty_select_criteria b,
297 pv_selected_attr_values c,
298 hz_party_sites d,
299 hz_locations e
300 WHERE b.selection_type_code = 'INPUT_FILTER' AND
301 b.attribute_id = pv_check_match_pub.g_a_Country_ AND
302 a.process_type = 'LEAD_INTERACTION' AND
303 a.process_rule_id = b.process_rule_id AND
304 d.party_site_id = c_address_id AND
305 e.location_id = d.location_id AND
306 b.selection_criteria_id = c.selection_criteria_id(+) AND
307 ((b.operator = 'EQUALS' AND c.attribute_value = e.country) OR
308 (b.operator = 'NOT_EQUALS' AND c.attribute_value <> e.country) OR
309 (b.operator = 'IS_NOT_NULL' AND e.country IS NOT NULL) OR
310 (b.operator = 'IS_NULL' AND e.country IS NULL))
311 ) rule
312 GROUP BY rule.process_rule_id, rule.rank
313 HAVING (rule.process_rule_id, COUNT(*)) IN (
314 SELECT a.process_rule_id, COUNT(*)
315 FROM pv_process_rules_b a,
316 pv_enty_select_criteria b
317 WHERE a.process_rule_id = b.process_rule_id AND
318 b.selection_type_code = 'INPUT_FILTER' AND
319 a.status_code = 'ACTIVE' AND
320 a.process_type = 'LEAD_INTERACTION' AND
321 SYSDATE BETWEEN a.start_date AND a.end_date
322 GROUP BY a.process_rule_id)
323 ORDER BY rule.rank DESC;
324
325 CURSOR C_Calculate_Score(c_interaction_id NUMBER, c_process_rule_id NUMBER) IS
326 SELECT SUM(TO_NUMBER(val.score))
327 FROM jtf_ih_activities activity, pv_enty_select_criteria attr,
328 pv_selected_attr_values val, aml_business_event_types_b bet
329 WHERE activity.interaction_id = c_interaction_id
330 AND attr.process_rule_id = c_process_rule_id
331 AND attr.attribute_id = pv_check_match_pub.g_a_business_event_type
332 AND attr.selection_type_code = 'CRITERION'
333 AND attr.selection_criteria_id = val.selection_criteria_id
334 AND val.attribute_value = bet.business_event_type_id
335 AND bet.action_id = activity.action_id
336 AND bet.action_item_id = activity.action_item_id;
337
338 CURSOR C_Match_Lead1(c_lead_interaction_lookback NUMBER, c_customer_id NUMBER,
339 c_cnt_person_party_id NUMBER) IS
340 SELECT sl.sales_lead_id--, NVL(sl.interaction_score, 0)
341 FROM as_sales_leads sl
342 WHERE sl.creation_date > (SYSDATE - c_lead_interaction_lookback)
343 AND sl.customer_id = c_customer_id
344 AND sl.primary_cnt_person_party_id = c_cnt_person_party_id
345 AND sl.status_open_flag = 'Y'
346 ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
347
348 CURSOR C_Match_Lead2(c_lead_interaction_lookback NUMBER, c_customer_id NUMBER) IS
349 SELECT sl.sales_lead_id--, NVL(sl.interaction_score, 0)
350 FROM as_sales_leads sl
351 WHERE sl.creation_date > (SYSDATE - c_lead_interaction_lookback)
352 AND sl.customer_id = c_customer_id
353 AND sl.status_open_flag = 'Y'
354 ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
355
356 -- Bug 3583298, join mtl_system_items_b to get uom_code
357 -- Bug 3583510, remove AND aap.level_type_code = 'FAMILY' because both 'PRODUCT'
358 -- and 'FAMILY' should be used.
359 CURSOR C_Get_Category(c_interaction_id NUMBER, c_default_source_code VARCHAR2) IS
360 SELECT distinct aap.category_id, aap.category_set_id, aap.inventory_item_id,
361 aap.organization_id,
362 DECODE(aap.inventory_item_id, NULL, NULL, msi.primary_uom_code),
363 aap.quantity
364 FROM ams_source_codes amsc,
365 ams_act_products aap,
366 mtl_system_items_b msi,
367 (
368 SELECT jii.source_code
369 FROM jtf_ih_interactions jii
370 WHERE interaction_id = c_interaction_id
371 UNION ALL
372 SELECT jia.source_code
373 FROM jtf_ih_activities jia
374 WHERE interaction_id = c_interaction_id
375 ) v
376 WHERE v.source_code = amsc.source_code
377 AND amsc.source_code_for_id = aap.act_product_used_by_id
378 AND amsc.arc_source_code_for = aap.arc_act_product_used_by
379 AND aap.enabled_flag = 'Y'
380 AND aap.category_id IS NOT NULL
381 AND aap.inventory_item_id = msi.inventory_item_id(+)
382 AND aap.organization_id = msi.organization_id(+);
383
384 CURSOR C_Check_Rerun IS
385 SELECT rule.process_rule_id
386 FROM pv_process_rules_b rule, pv_process_rules_b prule,
387 pv_enty_select_criteria ruleattr
388 WHERE rule.process_type IN ('LEAD_QUALIFICATION', 'LEAD_RATING',
389 'CHANNEL_SELECTION')
390 AND rule.process_rule_id = ruleattr.process_rule_id
391 AND ruleattr.attribute_id = pv_check_match_pub.g_a_interaction_score
392 AND rule.parent_rule_id = prule.process_rule_id
393 AND prule.status_code = 'ACTIVE'
394 AND prule.start_date <= SYSDATE
395 AND prule.end_date >= SYSDATE;
396
397 CURSOR C_get_current_resource IS
398 SELECT res.resource_id
399 FROM jtf_rs_resource_extns res
400 WHERE res.category IN ('EMPLOYEE', 'PARTY')
401 AND res.user_id = fnd_global.user_id;
402
403 CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
404 c_sales VARCHAR2, c_telesales VARCHAR2,
405 c_fieldsales VARCHAR2, c_prm VARCHAR2, c_y VARCHAR2) IS
406 SELECT grp.group_id
407 FROM JTF_RS_GROUP_MEMBERS mem,
408 JTF_RS_ROLE_RELATIONS rrel,
409 JTF_RS_ROLES_B role,
410 JTF_RS_GROUP_USAGES u,
411 JTF_RS_GROUPS_B grp
412 WHERE mem.group_member_id = rrel.role_resource_id
413 AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
414 AND rrel.role_id = role.role_id
415 AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'LES','TELESALES','FIELDSALES','PRM')
416 AND mem.delete_flag <> c_y --'Y'
417 AND rrel.delete_flag <> c_y --'Y'
418 AND SYSDATE BETWEEN rrel.start_date_active AND
419 NVL(rrel.end_date_active,SYSDATE)
420 AND mem.resource_id = c_resource_id
421 AND mem.group_id = u.group_id
422 AND u.usage = c_sales --'SALES'
423 AND mem.group_id = grp.group_id
424 AND SYSDATE BETWEEN grp.start_date_active AND
425 NVL(grp.end_date_active,SYSDATE)
426 AND ROWNUM < 2;
427
428 CURSOR C_Match_Reponse1(c_lead_interaction_lookback NUMBER,
429 c_customer_id NUMBER,
430 c_contact_party_id NUMBER) IS
431 SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
432 FROM as_import_interface imp
433 WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
434 AND imp.party_id = c_customer_id
435 AND imp.contact_party_id = c_contact_party_id
436 AND imp.sales_lead_id IS NULL
437 AND imp.source_system = 'INTERACTION' -- new in 11.5.10
438 AND imp.load_status = 'NEW'
439 ORDER BY imp.interaction_score DESC, imp.creation_date DESC;
440
441 CURSOR C_Match_Reponse2(c_lead_interaction_lookback NUMBER,
442 c_customer_id NUMBER) IS
443 SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
444 FROM as_import_interface imp
445 WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
446 AND imp.party_id = c_customer_id
447 AND imp.sales_lead_id IS NULL
448 AND imp.source_system = 'INTERACTION' -- new in 11.5.10
449 AND imp.load_status = 'NEW'
450 ORDER BY imp.interaction_score DESC, imp.creation_date DESC;
451
452 CURSOR C_Find_Highest_Score(c_import_interface_id NUMBER) IS
453 SELECT il.score
454 FROM aml_interaction_leads il
455 WHERE il.import_interface_id = c_import_interface_id
456 ORDER BY il.score desc;
457
458 CURSOR C_Get_Run_Import_Flag(c_batch_id NUMBER) IS
459 SELECT 'Y'
460 FROM as_import_interface
461 WHERE batch_id = c_batch_id
462 AND source_system = 'INTERACTION';
463 BEGIN
464 g_debug_flag := p_debug_mode;
465 AML_DEBUG('Run_Interaction_Engine starts ***');
466
467 IF p_trace_mode = 'Y' THEN
468 dbms_session.set_sql_trace(TRUE);
469 ELSE
470 dbms_session.set_sql_trace(FALSE);
471 END IF;
472
473 -- Find all the interactions after the last interaction engine run.
474 l_last_interaction_id :=
475 NVL(TO_NUMBER(FND_PROFILE.Value('AS_LAST_INTERACTION_ID')), 0);
476 l_lead_interaction_lookback :=
477 NVL(TO_NUMBER(FND_PROFILE.Value('AS_LEAD_INTERACTION_LOOKBACK')), 0);
478 l_default_source_code :=
479 FND_PROFILE.Value('AS_DEFAULT_SOURCE_FOR_INTERENG');
480 AML_DEBUG('l_last_interaction_id=' || l_last_interaction_id);
481 AML_DEBUG('l_lead_interaction_lookback=' || l_lead_interaction_lookback);
482 AML_DEBUG('l_default_source_code=' || l_default_source_code);
483
484 -- If this is the first time for interaction engine, get the first
485 -- interaction created in N days.
486 IF l_last_interaction_id = 0
487 THEN
488 l_interaction_lookback :=
489 NVL(TO_NUMBER(FND_PROFILE.Value('AS_INTERACTION_LOOKBACK')), 0);
490 AML_DEBUG('l_interaction_lookback=' || l_interaction_lookback);
491 -- Pick the first interaction created in N days.
492 OPEN c_get_last_interaction_id(l_interaction_lookback);
493 FETCH c_get_last_interaction_id INTO l_last_interaction_id;
494 CLOSE c_get_last_interaction_id;
495 END IF;
496
497 AML_DEBUG('l_last_interaction_id=' || l_last_interaction_id);
498
499 -- Find the interactions that should be processed.
500 OPEN c_get_interactions(l_last_interaction_id, l_default_source_code);
501 FETCH c_get_interactions BULK COLLECT INTO l_interaction_id_tbl,
502 l_source_code_tbl, l_source_code_id_tbl, l_source_code_for_id_tbl,
503 l_arc_source_code_for_tbl, l_description_tbl,
504 l_party_id_tbl, l_customer_id_tbl, l_address_id_tbl,
505 l_contact_party_id_tbl;
506 CLOSE c_get_interactions;
507
508 AML_DEBUG('l_interaction_id_tbl.count=' || l_interaction_id_tbl.count);
509 -- Set profile AS_LAST_INTERACTION_ID to be the maximum id in
510 -- l_interaction_id_tbl;
511 IF l_interaction_id_tbl.count > 0
512 THEN
513 OPEN C_Get_Batch_ID;
514 FETCH C_Get_Batch_ID INTO l_batch_id;
515 CLOSE C_Get_Batch_ID;
516 AML_DEBUG('batch_id=' || l_batch_id);
517
518 l_save_profile := fnd_profile.save('AS_LAST_INTERACTION_ID',
519 TO_CHAR(l_interaction_id_tbl(l_interaction_id_tbl.count)), 'SITE');
520
521 l_default_interaction_score :=
522 NVL(TO_NUMBER(FND_PROFILE.Value('AS_DEFAULT_INTERACTION_SCORE')),0);
523 AML_DEBUG('default score=' || l_default_interaction_score);
524
525 -- If the attribute Interaction Score is not used in
526 -- qualification, rating, channel selection rules, lead
527 -- doesn't need to be reprocessed.
528 OPEN C_Check_Rerun;
529 FETCH C_Check_Rerun INTO l_check_rerun;
530 CLOSE C_Check_Rerun;
531 AML_DEBUG('interaction score attr used?' || l_check_rerun);
532
533 -- For each interaction, find the matching interaction rules
534 FOR i IN l_interaction_id_tbl.FIRST..l_interaction_id_tbl.LAST
535 LOOP
536 AML_DEBUG(i || '==========----------==========----------');
537 AML_DEBUG('interaction_id=' || l_interaction_id_tbl(i));
538 AML_DEBUG('source_code=' || l_source_code_tbl(i));
539 AML_DEBUG('source_code_id=' || l_source_code_id_tbl(i));
540 AML_DEBUG('source_code_for_id=' || l_source_code_for_id_tbl(i));
541 AML_DEBUG('arc_source_code_for=' || l_arc_source_code_for_tbl(i));
542 AML_DEBUG('description=' || l_description_tbl(i));
543 AML_DEBUG('party_id=' || l_party_id_tbl(i));
544 AML_DEBUG('customer_id=' || l_customer_id_tbl(i));
545 AML_DEBUG('address_id=' || l_address_id_tbl(i));
546 AML_DEBUG('contact_party_id=' || l_contact_party_id_tbl(i));
547 OPEN C_Get_Matching_Rules(l_interaction_id_tbl(i),
548 l_source_code_id_tbl(i), l_address_id_tbl(i));
549 FETCH C_Get_Matching_Rules BULK COLLECT INTO
550 l_process_rule_id_tbl, l_rank_tbl;
551 CLOSE C_Get_Matching_Rules;
552
553 AML_DEBUG('l_process_rule_id_tbl.count='
554 || l_process_rule_id_tbl.count);
555 l_interaction_score := l_default_interaction_score;
556 -- For each rule, calculate interaction score
557 IF l_process_rule_id_tbl.count > 0
558 THEN
559 -- Get the score of the highest precedence rule. If different
560 -- rules have the same precedence, get the highest score
561 l_prev_rank := l_rank_tbl(1);
562 l_temp_interaction_score := NULL;
563 l_get_score_flag := 'N';
564 FOR j IN l_process_rule_id_tbl.FIRST..l_process_rule_id_tbl.LAST
565 LOOP
566 AML_DEBUG('process_rule_id=' || l_process_rule_id_tbl(j));
567
568 -- Bug 3584079, add check for l_get_score_flag
569 IF l_prev_rank <> l_rank_tbl(j) AND l_get_score_flag = 'Y'
570 THEN
571 -- different precedence
572 EXIT;
573 END IF;
574
575 OPEN C_Calculate_Score(l_interaction_id_tbl(i),
576 l_process_rule_id_tbl(j));
577 FETCH C_Calculate_Score INTO l_temp_interaction_score;
578 CLOSE C_Calculate_Score;
579
580 AML_DEBUG('l_temp_score=' || l_temp_interaction_score);
581 IF l_temp_interaction_score IS NOT NULL
582 THEN
583 l_get_score_flag := 'Y';
584 END IF;
585 IF l_interaction_score < l_temp_interaction_score
586 THEN
587 l_interaction_score := l_temp_interaction_score;
588 END IF;
589 l_prev_rank := l_rank_tbl(j);
590 END LOOP;
591 END IF;
592
593 AML_DEBUG('l_interaction_score=' || l_interaction_score);
594 l_sales_lead_id := NULL;
595 -- l_lead_interaction_score := NULL;
596 IF FND_PROFILE.Value('AS_INTR_MATCH_B2B_LEAD_CONTACT') = 'Y' AND
597 l_contact_party_id_tbl(i) IS NOT NULL
598 THEN
599 -- match organization party_id and contact
600 AML_DEBUG('Match lead party and contact');
601 OPEN C_Match_Lead1(l_lead_interaction_lookback,
602 l_customer_id_tbl(i), l_contact_party_id_tbl(i));
603 FETCH C_Match_Lead1 INTO
604 l_sales_lead_id/*, l_lead_interaction_score*/;
605 CLOSE C_Match_Lead1;
606 ELSE
607 -- match party_id
608 AML_DEBUG('Match lead party only');
609 OPEN C_Match_Lead2(l_lead_interaction_lookback,
610 l_customer_id_tbl(i));
611 FETCH C_Match_Lead2 INTO
612 l_sales_lead_id/*, l_lead_interaction_score*/;
613 CLOSE C_Match_Lead2;
614 END IF;
615 AML_DEBUG('sales_lead_id=' || l_sales_lead_id);
616 -- AML_DEBUG('lead_interaction_score=' || l_lead_interaction_score);
617
618 IF l_sales_lead_id IS NOT NULL
619 THEN
620 -- Create_Sales_Lead_Lines for category_id/category_set_id from
621 -- l_source_code_id(both interaction and activities);
622 OPEN C_Get_Category(l_interaction_id_tbl(i),
623 l_default_source_code);
624 LOOP
625 FETCH C_Get_Category INTO l_category_id, l_category_set_id,
626 l_inventory_item_id, l_organization_id, l_uom_code,
627 l_quantity;
628 EXIT WHEN C_Get_Category%NOTFOUND;
629 AML_DEBUG('found category_id=' || l_category_id);
630
631 IF l_arc_source_code_for_tbl(i) = 'OFFR'
632 THEN
633 l_offer_id := l_source_code_id_tbl(i);
634 ELSE
635 l_offer_id := NULL;
636 END IF;
637
638 l_sales_lead_line_id := NULL;
639 AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Insert_Row(
640 px_SALES_LEAD_LINE_ID => l_sales_lead_line_id,
641 p_LAST_UPDATE_DATE => SYSDATE,
642 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
643 p_CREATION_DATE => SYSDATE,
644 p_CREATED_BY => FND_GLOBAL.USER_ID,
645 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
649 p_PROGRAM_UPDATE_DATE => SYSDATE,
650 p_SALES_LEAD_ID => l_sales_lead_id,
651 p_STATUS_CODE => NULL, -- ???
652 p_CATEGORY_ID => l_category_id,
653 p_CATEGORY_SET_ID => l_category_set_id,
654 p_INVENTORY_ITEM_ID => l_inventory_item_id,
655 p_ORGANIZATION_ID => l_organization_id,
656 p_UOM_CODE => l_uom_code,
657 p_QUANTITY => l_quantity,
658 p_BUDGET_AMOUNT => NULL,
659 p_SOURCE_PROMOTION_ID => l_source_code_id_tbl(i),
660 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
661 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
662 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
663 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
664 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
665 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
666 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
667 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
668 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
669 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
670 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
671 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
672 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
673 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
674 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
675 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
676 p_OFFER_ID => l_offer_id);
677 AML_DEBUG('created sales_lead_line_id='
678 || l_sales_lead_line_id);
679 END LOOP;
680 CLOSE C_Get_Category;
681
682 -- If the attribute Interaction Score is not used in
683 -- qualification, rating, channel selection rules, lead
684 -- doesn't need to be reprocessed.
685 IF l_check_rerun IS NOT NULL
686 THEN
687 UPDATE as_sales_leads
688 SET last_update_date = SYSDATE,
689 last_updated_by = fnd_global.user_id,
690 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
691 qualified_flag = 'N',
692 lead_rank_id = NULL,
693 channel_code = NULL,
694 interaction_score = NVL(interaction_score, 0)
695 + l_interaction_score
696 WHERE sales_lead_id = l_sales_lead_id;
697
698 OPEN C_get_current_resource;
699 FETCH C_get_current_resource INTO l_identity_salesforce_id;
700 IF (C_get_current_resource%NOTFOUND)
701 THEN
702 AML_DEBUG('No current resource found! Get default');
703 l_identity_salesforce_id :=
704 fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
705 END IF;
706 CLOSE C_get_current_resource;
707
708 AML_DEBUG('l_i_sf_id=' || l_identity_salesforce_id);
709 OPEN c_get_group_id (l_identity_salesforce_id,
710 'RS_GROUP_MEMBER', 'SALES',
711 'TELESALES', 'FIELDSALES', 'PRM', 'Y');
712 FETCH c_get_group_id INTO l_salesgroup_id;
713 CLOSE c_get_group_id;
714 AML_DEBUG('l_sg_id=' || l_salesgroup_id);
715
716 -- Initialize message list for each interaction
717 FND_MSG_PUB.initialize;
718
719 AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Update(
720 P_Api_Version_Number => 2.0,
721 P_Init_Msg_List => FND_API.G_FALSE,
722 p_Commit => FND_API.G_FALSE,
723 p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
724 P_Check_Access_Flag => 'N',
725 p_Admin_Flag => 'N',
726 P_Admin_Group_Id => NULL,
727 P_identity_salesforce_id => l_identity_salesforce_id,
728 P_Salesgroup_id => l_salesgroup_id,
729 P_Sales_Lead_Id => l_sales_lead_id,
730 X_Return_Status => l_return_status,
731 X_Msg_Count => l_msg_count,
732 X_Msg_Data => l_msg_data);
733
734 IF l_return_status = FND_API.G_RET_STS_ERROR OR
735 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
736 THEN
737 AML_DEBUG('Lead_Process_After_Update has error for '
738 || 'sales_lead_id ' || l_sales_lead_id || '!');
739 -- RAISE FND_API.G_EXC_ERROR;
740 END IF;
741
742 l_count := FND_MSG_PUB.Count_Msg;
743 FOR l_index IN 1..l_count LOOP
744 l_message := FND_MSG_PUB.Get(
745 p_msg_index => l_index,
746 p_encoded => FND_API.G_FALSE);
747 AML_DEBUG(l_message);
748 END LOOP;
749 ELSE
750 -- Lead doesn't need to be reprocessed, update
751 -- interaction_score
752 UPDATE as_sales_leads
753 SET last_update_date = SYSDATE,
754 last_updated_by = fnd_global.user_id,
755 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
756 interaction_score = NVL(interaction_score, 0)
757 + l_interaction_score
758 WHERE sales_lead_id = l_sales_lead_id;
759
760 END IF; -- lead get reprocessed
761
762 -- Maintain relevance of interaction and lead
763 l_INTERACTION_LEAD_ID := NULL;
764 AML_INTERACTION_LEADS_PKG.INSERT_ROW(
765 px_INTERACTION_LEAD_ID => l_INTERACTION_LEAD_ID,
766 p_INTERACTION_ID => l_interaction_id_tbl(i),
767 p_IMPORT_INTERFACE_ID => NULL,
768 p_SALES_LEAD_ID => l_sales_lead_id,
769 p_CREATION_DATE => SYSDATE,
770 p_CREATED_BY => fnd_global.user_id,
771 p_LAST_UPDATE_DATE => SYSDATE,
772 p_LAST_UPDATED_BY => fnd_global.user_id,
773 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
777 p_PROGRAM_UPDATE_DATE => SYSDATE,
778 p_OBJECT_VERSION_NUMBER => 1,
779 p_SCORE => l_interaction_score);
780 ELSE
781 -- This interaction can't match any lead.
782 -- Find matched record in as_import_interface, if no open lead
783 -- matched
784 l_import_interface_id := NULL;
785
786 IF FND_PROFILE.Value('AS_INTR_MATCH_B2B_LEAD_CONTACT') = 'Y' AND
787 l_contact_party_id_tbl(i) IS NOT NULL
788 THEN
789 -- match organization party_id and contact
790 AML_DEBUG('Match response party and contact');
791 OPEN C_Match_Reponse1(l_lead_interaction_lookback,
792 l_customer_id_tbl(i), l_contact_party_id_tbl(i));
793 FETCH C_Match_Reponse1 INTO
794 l_import_interface_id/*, l_response_interaction_score*/;
795 CLOSE C_Match_Reponse1;
796 ELSE
797 -- match party_id
798 AML_DEBUG('Match response party only');
799 OPEN C_Match_Reponse2(l_lead_interaction_lookback,
800 l_customer_id_tbl(i));
801 FETCH C_Match_Reponse2 INTO
802 l_import_interface_id/*, l_response_interaction_score*/;
803 CLOSE C_Match_Reponse2;
804 END IF;
805 AML_DEBUG('import_interface_id=' || l_import_interface_id);
806
807 IF l_import_interface_id IS NOT NULL
808 THEN
809 OPEN C_Find_Highest_Score(l_import_interface_id);
810 FETCH C_Find_Highest_Score INTO l_highest_score;
811 CLOSE C_Find_Highest_Score;
812
813 IF l_interaction_score > l_highest_score
814 THEN
815 -- All interactions for this response have score lower
816 -- than that of this interaction.
817 UPDATE as_import_interface
818 SET last_update_date = SYSDATE,
819 last_updated_by = fnd_global.user_id,
820 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
821 interaction_score = NVL(interaction_score, 0)
822 + l_interaction_score,
823 promotion_id = l_source_code_id_tbl(i),
824 batch_id = l_batch_id
825 WHERE import_interface_id = l_import_interface_id;
826 ELSE
827 UPDATE as_import_interface
828 SET last_update_date = SYSDATE,
829 last_updated_by = fnd_global.user_id,
830 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
831 interaction_score = NVL(interaction_score, 0)
832 + l_interaction_score,
833 batch_id = l_batch_id
834 WHERE import_interface_id = l_import_interface_id;
835 END IF;
836 ELSE
837 INSERT INTO AS_IMPORT_INTERFACE(
838 IMPORT_INTERFACE_ID, LAST_UPDATE_DATE,
839 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
840 LAST_UPDATE_LOGIN, REQUEST_ID,
841 PROGRAM_APPLICATION_ID, PROGRAM_ID,
842 PROGRAM_UPDATE_DATE, LOAD_TYPE, LOAD_DATE, LOAD_STATUS,
843 PROMOTION_ID, CUSTOMER_ID,
844 PARTY_ID, ADDRESS_ID,
845 PARTY_SITE_ID, SOURCE_SYSTEM, BATCH_ID,
846 REL_PARTY_ID, CONTACT_PARTY_ID,
847 INTERACTION_SCORE, SOURCE_PRIMARY_REFERENCE
848 ) VALUES (
849 AS_IMPORT_INTERFACE_S.nextval, SYSDATE,
850 fnd_global.user_id, SYSDATE, fnd_global.user_id,
851 FND_GLOBAL.CONC_LOGIN_ID, FND_GLOBAL.Conc_Request_Id,
852 FND_GLOBAL.Prog_Appl_Id, FND_GLOBAL.Conc_Program_Id,
853 SYSDATE, 'LEAD_LOAD', SYSDATE, 'NEW',
854 l_source_code_id_tbl(i), l_customer_id_tbl(i),
855 l_customer_id_tbl(i), l_address_id_tbl(i),
856 l_address_id_tbl(i), 'INTERACTION', l_batch_id,
857 l_party_id_tbl(i), l_contact_party_id_tbl(i),
858 l_interaction_score, l_interaction_id_tbl(i))
859 RETURNING IMPORT_INTERFACE_ID INTO l_import_interface_id;
860 AML_DEBUG('Create new response ' || l_import_interface_id);
861 END IF; -- l_import_interface_id IS NULL or not
862
863 OPEN C_Get_Category(l_interaction_id_tbl(i),
864 l_default_source_code);
865 LOOP
866 FETCH C_Get_Category INTO l_category_id,
867 l_category_set_id, l_inventory_item_id,
868 l_organization_id, l_uom_code, l_quantity;
869 EXIT WHEN C_Get_Category%NOTFOUND;
870 AML_DEBUG('found category_id=' || l_category_id);
871
872 INSERT INTO AS_IMP_LINES_INTERFACE(
873 IMP_LINES_INTERFACE_ID,
874 IMPORT_INTERFACE_ID, LAST_UPDATE_DATE,
875 LAST_UPDATED_BY, CREATION_DATE,
876 CREATED_BY, LAST_UPDATE_LOGIN,
877 REQUEST_ID, PROGRAM_APPLICATION_ID,
878 PROGRAM_ID, PROGRAM_UPDATE_DATE,
879 SOURCE_PROMOTION_ID, CATEGORY_ID,
880 INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE,
881 QUANTITY
882 ) VALUES (
883 AS_IMP_LINES_INTERFACE_S.nextval,
884 l_import_interface_id, SYSDATE,
885 fnd_global.user_id, SYSDATE,
886 fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
887 FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
888 FND_GLOBAL.Conc_Program_Id, SYSDATE,
889 l_source_code_id_tbl(i), l_category_id,
890 l_inventory_item_id, l_organization_id, l_uom_code,
891 l_quantity)
892 RETURNING IMP_LINES_INTERFACE_ID INTO
893 l_imp_lines_interface_id;
894 AML_DEBUG('created imp_lines_interface_id='
895 || l_imp_lines_interface_id);
896 END LOOP;
897 CLOSE C_Get_Category;
898
899 -- Maintain relevance of interaction and response
900 l_INTERACTION_LEAD_ID := NULL;
901 AML_INTERACTION_LEADS_PKG.INSERT_ROW(
902 px_INTERACTION_LEAD_ID => l_INTERACTION_LEAD_ID,
903 p_INTERACTION_ID => l_interaction_id_tbl(i),
904 p_IMPORT_INTERFACE_ID => l_import_interface_id,
905 p_SALES_LEAD_ID => NULL,
906 p_CREATION_DATE => SYSDATE,
907 p_CREATED_BY => fnd_global.user_id,
908 p_LAST_UPDATE_DATE => SYSDATE,
909 p_LAST_UPDATED_BY => fnd_global.user_id,
910 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
914 p_PROGRAM_UPDATE_DATE => SYSDATE,
915 p_OBJECT_VERSION_NUMBER => 1,
916 p_SCORE => l_interaction_score);
917 END IF; -- sales_lead_id is NULL or not
918 END LOOP; -- for each interaction
919
920 -- If interaction_score is less than threshold, lead import doesn't
921 -- need to process this record. Therefore, set batch_id to NULL,
922 -- so lead import program won't pick up this record, and interaction
923 -- engine can match this interaction again next time.
924 l_interaction_score_threshold :=
925 NVL(TO_NUMBER(FND_PROFILE.Value('AS_INTERACTION_SCORE_THRESHOLD')), 0);
926 AML_DEBUG('intr score threshold: ' || l_interaction_score_threshold);
927 UPDATE as_import_interface
928 SET last_update_date = SYSDATE,
929 last_updated_by = fnd_global.user_id,
930 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
931 batch_id = NULL
932 WHERE batch_id = l_batch_id
933 AND source_system = 'INTERACTION'
934 AND interaction_score < l_interaction_score_threshold;
935
936 -- Only if there is one or more records in as_import_interface,
937 -- then we launch lead import program.
938 l_run_import_flag := 'N';
939 OPEN C_Get_Run_Import_Flag(l_batch_id);
940 FETCH C_Get_Run_Import_Flag INTO l_run_import_flag;
941 CLOSE C_Get_Run_Import_Flag;
942 AML_DEBUG('l_run_import_flag: ' || l_run_import_flag);
943
944 IF l_run_import_flag = 'Y'
945 THEN
946 -- Call Lead Import program to import lead.
947 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AS',
948 'ASXSLIMP',
949 'Import Sales Leads',
950 '',
951 FALSE,
952 'INTERACTION',
953 p_debug_mode,
954 l_batch_id, -- batch id
955 'N',
956 CHR(0));
957
958 IF l_request_id = 0
959 THEN
960 l_msg_data := FND_MESSAGE.GET;
961 AML_DEBUG(l_msg_data);
962 END IF;
963 END IF;
964
965 AML_DEBUG('submitted request ' || l_request_id);
966 END IF; -- If more than on interaction is found
967
968 /*
969 -- The following logic is to find open opportunity for closed lead.
970 -- It's low priority for 11.5.10.
971 -- Find open opportunity, if no open lead matched
972 IF l_sales_lead_id IS NULL THEN
973 SELECT sl.sales_lead_id, sl.status_code, sl.status_open_flag
974 INTO l_sales_lead_id, l_status_code, l_status_open_flag
975 FROM as_sales_leads sl, as_sales_lead_opportunity slo, as_leads_all opp, as_statuses_b status
976 WHERE sl.creation_date > l_lead_interaction_lookback
977 AND sl.customer_id = l_customer_id
978 AND sl.status_code = 'CONVERTED_TO_OPPORTUNITY'
979 AND sl.sales_lead_id = slo.sales_lead_id
980 AND slo.opportunity_id = opp.lead_id
981 AND opp.status_code = status.status_code
982 AND status.opp_open_status_flag = 'Y'
983 ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
984 END IF;
985 IF l_status_code = 'NEW' THEN
986 Create_Sales_Lead_Lines for interest_type_id from l_source_code_id(both interaction and activities);
987 L_lead_interaction_score := l_lead_interaction_score + l_interaction_score;
988 UPDATE as_sales_leads
989 SET lead_rank_id = NULL, channel_code = NULL, interaction_score = l_lead_interaction_score
990 WHERE sales_lead_id = l_sales_lead_id;
991 Call Lead_Process_After_Update;
992 END IF;
993 UPDATE jtf_ih_interactions
994 SET sales_lead_id = l_sales_lead_id
995 WHERE interaction_id = l_interaction_id;
996 */
997
998 EXCEPTION
999 WHEN FND_API.G_EXC_ERROR THEN
1000 AML_DEBUG('Expected error');
1001
1002 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1003 AML_DEBUG('Unexpected error');
1004
1005 WHEN others THEN
1006 AML_DEBUG('Exception: others in Run_Interaction_Engine');
1007 AML_DEBUG('SQLCODE ' || to_char(SQLCODE) ||
1008 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1009
1010 errbuf := SQLERRM;
1011 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1012 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
1013
1014 END Run_Interaction_Engine;
1015
1016
1017 /*-------------------------------------------------------------------------*
1018 | PRIVATE ROUTINE
1019 | AML_Debug
1020 |
1021 | PURPOSE
1022 | Write debug message
1023 |
1024 | NOTES
1025 |
1026 |
1027 | HISTORY
1028 | 06/17/2003 SOLIN Created
1029 *-------------------------------------------------------------------------*/
1030
1031
1032 PROCEDURE AML_DEBUG(msg IN VARCHAR2)
1033 IS
1034 l_length NUMBER;
1035 l_start NUMBER;
1036 l_substring VARCHAR2(255);
1037
1038 l_base VARCHAR2(12);
1039 BEGIN
1040 l_start := 1;
1041 IF g_debug_flag = 'Y'
1042 THEN
1043 -- chop the message to 255 long
1044 l_length := length(msg);
1045 WHILE l_length > 255 LOOP
1046 l_substring := substr(msg, l_start, 255);
1047 FND_FILE.PUT_LINE(FND_FILE.LOG, l_substring);
1048 -- dbms_output.put_line(l_substring);
1049
1050 l_start := l_start + 255;
1051 l_length := l_length - 255;
1052 END LOOP;
1053
1054 l_substring := substr(msg, l_start);
1055 FND_FILE.PUT_LINE(FND_FILE.LOG,l_substring);
1056 -- dbms_output.put_line(l_substring);
1057 END IF;
1058 EXCEPTION
1059 WHEN others THEN
1060 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in AML_DEBUG');
1061 FND_FILE.PUT_LINE(FND_FILE.LOG,
1062 'SQLCODE ' || to_char(SQLCODE) ||
1063 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1064 END AML_Debug;
1065
1066
1067 END AML_INTERACTION_ENGINE;