[Home] [Help]
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')
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'
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
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
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
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
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
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
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