[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT interaction_id
FROM jtf_ih_interactions
WHERE creation_date > (SYSDATE - c_interaction_lookback)
ORDER BY interaction_id;
SELECT DISTINCT v.interaction_id, NVL(v.source_code, c_default_source_code),
amsc.source_code_id,
amsc.source_code_for_id, amsc.arc_source_code_for,
v.party_name, v.party_id, v.customer_id, v.address_id,
v.contact_party_id
FROM ams_source_codes amsc,
(
-- Interactions from OMO and customer is person or organization without
-- contact
SELECT interact.interaction_id, interact.source_code,
interact.source_code_id, party.party_name, party.party_type,
TO_NUMBER(NULL) party_id, interact.resource_id,
party.party_id customer_id, site.party_site_id address_id,
TO_NUMBER(NULL) contact_party_id
FROM jtf_ih_interactions interact, hz_parties party, hz_party_sites site
WHERE interact.party_id = party.party_id
AND interact.handler_id = 530 -- for Oracle Marketing
AND party.party_type IN ('PERSON', 'ORGANIZATION')
AND party.party_id = site.party_id(+)
AND site.identifying_address_flag(+) = 'Y'
UNION ALL
-- Interactions from OMO and customer is organization with contact
SELECT interact.interaction_id, interact.source_code,
interact.source_code_id, party.party_name, party.party_type,
interact.party_id, interact.resource_id,
rel.object_id customer_id, site.party_site_id address_id,
rel.subject_id contact_party_id
FROM jtf_ih_interactions interact, hz_parties party,
hz_relationships rel, hz_party_sites site
WHERE interact.party_id = party.party_id
AND interact.handler_id = 530 -- for Oracle Marketing
AND party.party_type = 'PARTY_RELATIONSHIP'
AND party.party_id = rel.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.status = 'A'
AND rel.object_id = site.party_id(+)
AND site.identifying_address_flag(+) = 'Y'
UNION ALL
-- Interactions not from OMO and customer is person or organization
-- without contact
SELECT interact.interaction_id, interact.source_code,
interact.source_code_id, party.party_name, party.party_type,
TO_NUMBER(NULL) party_id, interact.resource_id,
party.party_id customer_id, site.party_site_id address_id,
TO_NUMBER(NULL) contact_party_id
FROM jtf_ih_interactions interact, hz_parties party,
jtf_ih_activities acv, pv_process_rules_b rule,
pv_enty_select_criteria cra, pv_selected_attr_values val,
aml_business_event_types_b bet, hz_party_sites site
WHERE interact.party_id = party.party_id
AND interact.handler_id <> 530 -- for Oracle Marketing
AND party.party_type IN ('PERSON', 'ORGANIZATION')
AND party.party_id = site.party_id(+)
AND site.identifying_address_flag(+) = 'Y'
AND interact.interaction_id = acv.interaction_id
AND TO_CHAR(bet.business_event_type_id) = val.attribute_value
AND bet.action_id = acv.action_id
AND bet.action_item_id = acv.action_item_id
AND val.selection_criteria_id = cra.selection_criteria_id
AND cra.selection_type_code = 'CRITERION'
AND cra.process_rule_id = rule.process_rule_id
AND rule.process_type = 'LEAD_INTERACTION'
AND rule.status_code = 'ACTIVE'
AND SYSDATE BETWEEN rule.start_date AND rule.end_date
UNION ALL
-- Interactions not from OMO and customer is organization with contact
SELECT interact.interaction_id, interact.source_code,
interact.source_code_id, party.party_name, party.party_type,
interact.party_id, interact.resource_id,
rel.object_id customer_id, site.party_site_id address_id,
rel.subject_id contact_party_id
FROM jtf_ih_interactions interact, hz_parties party,
hz_relationships rel, hz_party_sites site, jtf_ih_activities acv,
pv_process_rules_b rule, pv_enty_select_criteria cra,
pv_selected_attr_values val, aml_business_event_types_b bet
WHERE interact.party_id = party.party_id
AND interact.handler_id <> 530 -- for Oracle Marketing
AND party.party_type = 'PARTY_RELATIONSHIP'
AND rel.object_id = site.party_id(+)
AND site.identifying_address_flag(+) = 'Y'
AND party.party_id = rel.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.status = 'A'
AND interact.interaction_id = acv.interaction_id
AND TO_CHAR(bet.business_event_type_id) = val.attribute_value
AND bet.action_id = acv.action_id
AND bet.action_item_id = acv.action_item_id
AND val.selection_criteria_id = cra.selection_criteria_id
AND cra.selection_type_code = 'CRITERION'
AND cra.process_rule_id = rule.process_rule_id
AND rule.process_type = 'LEAD_INTERACTION'
AND rule.status_code = 'ACTIVE'
AND SYSDATE BETWEEN rule.start_date AND rule.end_date) v
WHERE v.interaction_id > c_last_interaction_id
AND NVL(v.source_code, c_default_source_code) = amsc.source_code(+)
ORDER BY v.interaction_id;
SELECT as_sl_imp_batch_s.nextval
FROM dual;
SELECT rule.process_rule_id, rule.rank
FROM (
-- ----------------------------------------------------------------
-- Campaign
-------------------------------------------------------------------
SELECT DISTINCT a.process_rule_id, a.rank
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c
WHERE b.selection_type_code = 'INPUT_FILTER' AND
b.attribute_id = pv_check_match_pub.g_a_Campaign_ AND
a.process_type = 'LEAD_INTERACTION' AND
a.process_rule_id = b.process_rule_id AND
c_source_code_id IS NOT NULL AND
b.selection_criteria_id = c.selection_criteria_id(+) AND
((b.operator = 'EQUALS' AND c.attribute_value = c_source_code_id) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_source_code_id) OR
(b.operator = 'IS_NOT_NULL' AND c_source_code_id IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_source_code_id IS NULL))
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
jtf_ih_activities d
WHERE b.selection_type_code = 'INPUT_FILTER' AND
b.attribute_id = pv_check_match_pub.g_a_Campaign_ AND
a.process_type = 'LEAD_INTERACTION' AND
a.process_rule_id = b.process_rule_id AND
c_source_code_id IS NULL AND
b.selection_criteria_id = c.selection_criteria_id(+) AND
d.interaction_id = c_interaction_id AND
((b.operator = 'EQUALS' AND c.attribute_value = d.source_code_id) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> d.source_code_id) OR
(b.operator = 'IS_NOT_NULL' AND d.source_code_id IS NOT NULL) OR
(b.operator = 'IS_NULL' AND d.source_code_id IS NULL))
-- ----------------------------------------------------------------
-- All
-------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank
FROM pv_process_rules_b a,
pv_enty_select_criteria b
WHERE b.selection_type_code = 'INPUT_FILTER' AND
b.attribute_id = pv_check_match_pub.g_a_all AND
a.process_type = 'LEAD_INTERACTION' AND
a.process_rule_id = b.process_rule_id
-- -------------------------------------------------------------------
-- Country
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
hz_party_sites d,
hz_locations e
WHERE b.selection_type_code = 'INPUT_FILTER' AND
b.attribute_id = pv_check_match_pub.g_a_Country_ AND
a.process_type = 'LEAD_INTERACTION' AND
a.process_rule_id = b.process_rule_id AND
d.party_site_id = c_address_id AND
e.location_id = d.location_id AND
b.selection_criteria_id = c.selection_criteria_id(+) AND
((b.operator = 'EQUALS' AND c.attribute_value = e.country) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> e.country) OR
(b.operator = 'IS_NOT_NULL' AND e.country IS NOT NULL) OR
(b.operator = 'IS_NULL' AND e.country IS NULL))
) rule
GROUP BY rule.process_rule_id, rule.rank
HAVING (rule.process_rule_id, COUNT(*)) IN (
SELECT a.process_rule_id, COUNT(*)
FROM pv_process_rules_b a,
pv_enty_select_criteria b
WHERE a.process_rule_id = b.process_rule_id AND
b.selection_type_code = 'INPUT_FILTER' AND
a.status_code = 'ACTIVE' AND
a.process_type = 'LEAD_INTERACTION' AND
SYSDATE BETWEEN a.start_date AND a.end_date
GROUP BY a.process_rule_id)
ORDER BY rule.rank DESC;
SELECT SUM(TO_NUMBER(val.score))
FROM jtf_ih_activities activity, pv_enty_select_criteria attr,
pv_selected_attr_values val, aml_business_event_types_b bet
WHERE activity.interaction_id = c_interaction_id
AND attr.process_rule_id = c_process_rule_id
AND attr.attribute_id = pv_check_match_pub.g_a_business_event_type
AND attr.selection_type_code = 'CRITERION'
AND attr.selection_criteria_id = val.selection_criteria_id
AND val.attribute_value = bet.business_event_type_id
AND bet.action_id = activity.action_id
AND bet.action_item_id = activity.action_item_id;
SELECT sl.sales_lead_id--, NVL(sl.interaction_score, 0)
FROM as_sales_leads sl
WHERE sl.creation_date > (SYSDATE - c_lead_interaction_lookback)
AND sl.customer_id = c_customer_id
AND sl.primary_cnt_person_party_id = c_cnt_person_party_id
AND sl.status_open_flag = 'Y'
ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
SELECT sl.sales_lead_id--, NVL(sl.interaction_score, 0)
FROM as_sales_leads sl
WHERE sl.creation_date > (SYSDATE - c_lead_interaction_lookback)
AND sl.customer_id = c_customer_id
AND sl.status_open_flag = 'Y'
ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
SELECT distinct aap.category_id, aap.category_set_id, aap.inventory_item_id,
aap.organization_id,
DECODE(aap.inventory_item_id, NULL, NULL, msi.primary_uom_code),
aap.quantity
FROM ams_source_codes amsc,
ams_act_products aap,
mtl_system_items_b msi,
(
SELECT jii.source_code
FROM jtf_ih_interactions jii
WHERE interaction_id = c_interaction_id
UNION ALL
SELECT jia.source_code
FROM jtf_ih_activities jia
WHERE interaction_id = c_interaction_id
) v
WHERE v.source_code = amsc.source_code
AND amsc.source_code_for_id = aap.act_product_used_by_id
AND amsc.arc_source_code_for = aap.arc_act_product_used_by
AND aap.enabled_flag = 'Y'
AND aap.category_id IS NOT NULL
AND aap.inventory_item_id = msi.inventory_item_id(+)
AND aap.organization_id = msi.organization_id(+);
SELECT rule.process_rule_id
FROM pv_process_rules_b rule, pv_process_rules_b prule,
pv_enty_select_criteria ruleattr
WHERE rule.process_type IN ('LEAD_QUALIFICATION', 'LEAD_RATING',
'CHANNEL_SELECTION')
AND rule.process_rule_id = ruleattr.process_rule_id
AND ruleattr.attribute_id = pv_check_match_pub.g_a_interaction_score
AND rule.parent_rule_id = prule.process_rule_id
AND prule.status_code = 'ACTIVE'
AND prule.start_date <= SYSDATE
AND prule.end_date >= SYSDATE;
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category IN ('EMPLOYEE', 'PARTY')
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'LES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> c_y --'Y'
AND rrel.delete_flag <> c_y --'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = c_sales --'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
FROM as_import_interface imp
WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
AND imp.party_id = c_customer_id
AND imp.contact_party_id = c_contact_party_id
AND imp.sales_lead_id IS NULL
AND imp.source_system = 'INTERACTION' -- new in 11.5.10
AND imp.load_status = 'NEW'
ORDER BY imp.interaction_score DESC, imp.creation_date DESC;
SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
FROM as_import_interface imp
WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
AND imp.party_id = c_customer_id
AND imp.sales_lead_id IS NULL
AND imp.source_system = 'INTERACTION' -- new in 11.5.10
AND imp.load_status = 'NEW'
ORDER BY imp.interaction_score DESC, imp.creation_date DESC;
SELECT il.score
FROM aml_interaction_leads il
WHERE il.import_interface_id = c_import_interface_id
ORDER BY il.score desc;
SELECT 'Y'
FROM as_import_interface
WHERE batch_id = c_batch_id
AND source_system = 'INTERACTION';
AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Insert_Row(
px_SALES_LEAD_LINE_ID => l_sales_lead_line_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_SALES_LEAD_ID => l_sales_lead_id,
p_STATUS_CODE => NULL, -- ???
p_CATEGORY_ID => l_category_id,
p_CATEGORY_SET_ID => l_category_set_id,
p_INVENTORY_ITEM_ID => l_inventory_item_id,
p_ORGANIZATION_ID => l_organization_id,
p_UOM_CODE => l_uom_code,
p_QUANTITY => l_quantity,
p_BUDGET_AMOUNT => NULL,
p_SOURCE_PROMOTION_ID => l_source_code_id_tbl(i),
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_OFFER_ID => l_offer_id);
UPDATE as_sales_leads
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
qualified_flag = 'N',
lead_rank_id = NULL,
channel_code = NULL,
interaction_score = NVL(interaction_score, 0)
+ l_interaction_score
WHERE sales_lead_id = l_sales_lead_id;
AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Update(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
p_Commit => FND_API.G_FALSE,
p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
P_Check_Access_Flag => 'N',
p_Admin_Flag => 'N',
P_Admin_Group_Id => NULL,
P_identity_salesforce_id => l_identity_salesforce_id,
P_Salesgroup_id => l_salesgroup_id,
P_Sales_Lead_Id => l_sales_lead_id,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
AML_DEBUG('Lead_Process_After_Update has error for '
|| 'sales_lead_id ' || l_sales_lead_id || '!');
UPDATE as_sales_leads
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
interaction_score = NVL(interaction_score, 0)
+ l_interaction_score
WHERE sales_lead_id = l_sales_lead_id;
AML_INTERACTION_LEADS_PKG.INSERT_ROW(
px_INTERACTION_LEAD_ID => l_INTERACTION_LEAD_ID,
p_INTERACTION_ID => l_interaction_id_tbl(i),
p_IMPORT_INTERFACE_ID => NULL,
p_SALES_LEAD_ID => l_sales_lead_id,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_OBJECT_VERSION_NUMBER => 1,
p_SCORE => l_interaction_score);
UPDATE as_import_interface
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
interaction_score = NVL(interaction_score, 0)
+ l_interaction_score,
promotion_id = l_source_code_id_tbl(i),
batch_id = l_batch_id
WHERE import_interface_id = l_import_interface_id;
UPDATE as_import_interface
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
interaction_score = NVL(interaction_score, 0)
+ l_interaction_score,
batch_id = l_batch_id
WHERE import_interface_id = l_import_interface_id;
INSERT INTO AS_IMPORT_INTERFACE(
IMPORT_INTERFACE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, LOAD_TYPE, LOAD_DATE, LOAD_STATUS,
PROMOTION_ID, CUSTOMER_ID,
PARTY_ID, ADDRESS_ID,
PARTY_SITE_ID, SOURCE_SYSTEM, BATCH_ID,
REL_PARTY_ID, CONTACT_PARTY_ID,
INTERACTION_SCORE, SOURCE_PRIMARY_REFERENCE
) VALUES (
AS_IMPORT_INTERFACE_S.nextval, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
FND_GLOBAL.CONC_LOGIN_ID, FND_GLOBAL.Conc_Request_Id,
FND_GLOBAL.Prog_Appl_Id, FND_GLOBAL.Conc_Program_Id,
SYSDATE, 'LEAD_LOAD', SYSDATE, 'NEW',
l_source_code_id_tbl(i), l_customer_id_tbl(i),
l_customer_id_tbl(i), l_address_id_tbl(i),
l_address_id_tbl(i), 'INTERACTION', l_batch_id,
l_party_id_tbl(i), l_contact_party_id_tbl(i),
l_interaction_score, l_interaction_id_tbl(i))
RETURNING IMPORT_INTERFACE_ID INTO l_import_interface_id;
INSERT INTO AS_IMP_LINES_INTERFACE(
IMP_LINES_INTERFACE_ID,
IMPORT_INTERFACE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE,
SOURCE_PROMOTION_ID, CATEGORY_ID,
INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE,
QUANTITY
) VALUES (
AS_IMP_LINES_INTERFACE_S.nextval,
l_import_interface_id, SYSDATE,
fnd_global.user_id, SYSDATE,
fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
FND_GLOBAL.Conc_Program_Id, SYSDATE,
l_source_code_id_tbl(i), l_category_id,
l_inventory_item_id, l_organization_id, l_uom_code,
l_quantity)
RETURNING IMP_LINES_INTERFACE_ID INTO
l_imp_lines_interface_id;
AML_INTERACTION_LEADS_PKG.INSERT_ROW(
px_INTERACTION_LEAD_ID => l_INTERACTION_LEAD_ID,
p_INTERACTION_ID => l_interaction_id_tbl(i),
p_IMPORT_INTERFACE_ID => l_import_interface_id,
p_SALES_LEAD_ID => NULL,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_OBJECT_VERSION_NUMBER => 1,
p_SCORE => l_interaction_score);
UPDATE as_import_interface
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
batch_id = NULL
WHERE batch_id = l_batch_id
AND source_system = 'INTERACTION'
AND interaction_score < l_interaction_score_threshold;
SELECT sl.sales_lead_id, sl.status_code, sl.status_open_flag
INTO l_sales_lead_id, l_status_code, l_status_open_flag
FROM as_sales_leads sl, as_sales_lead_opportunity slo, as_leads_all opp, as_statuses_b status
WHERE sl.creation_date > l_lead_interaction_lookback
AND sl.customer_id = l_customer_id
AND sl.status_code = 'CONVERTED_TO_OPPORTUNITY'
AND sl.sales_lead_id = slo.sales_lead_id
AND slo.opportunity_id = opp.lead_id
AND opp.status_code = status.status_code
AND status.opp_open_status_flag = 'Y'
ORDER BY sl.lead_rank_score DESC, sl.creation_date DESC;
UPDATE as_sales_leads
SET lead_rank_id = NULL, channel_code = NULL, interaction_score = l_lead_interaction_score
WHERE sales_lead_id = l_sales_lead_id;
Call Lead_Process_After_Update;
UPDATE jtf_ih_interactions
SET sales_lead_id = l_sales_lead_id
WHERE interaction_id = l_interaction_id;