The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(PV_LEAD_WORKFLOWS_S.nextval) INTO l_item_key
FROM dual;
l_selected_rule_id NUMBER;
SELECT user_name
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_id;
SELECT description
FROM as_leads_all
WHERE lead_id = c_lead_id;
SELECT opp.description, rule.process_rule_name
FROM as_leads_all opp, pv_process_rules_vl rule
WHERE opp.lead_id = c_lead_id
AND rule.process_rule_id = c_process_rule_id;
SELECT customer_id, address_id
FROM as_leads_all
WHERE lead_id = c_lead_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 = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> '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 = '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 resource_id
FROM jtf_rs_resource_extns
WHERE user_name = c_user_name;
SELECT employee_person_id
FROM as_salesforce_v
WHERE salesforce_id = c_salesforce_id;
SELECT b.opp_open_status_flag, c.lead_id
FROM as_leads_all a,
as_statuses_b b,
pv_lead_workflows c
WHERE a.lead_id = l_lead_id AND
a.status = b.status_code AND
b.opp_flag = 'Y' AND
a.lead_id = c.lead_id (+)
)
LOOP
-- -----------------------------------------------------------------------
-- This is not an "open" opportunity. It cannot be routed.
-- -----------------------------------------------------------------------
IF (x.opp_open_status_flag <> 'Y') THEN
l_open_opportunity_flag := FALSE;
FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_CLOSED_OPP');
FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_ALREADY_EXISTS');
pv_opp_match_pub.opportunity_selection(
P_Api_Version => 1.0,
P_Init_Msg_List => FND_API.G_TRUE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_entity_id => l_lead_id,
p_entity => 'LEAD',
p_user_name => l_user_name,
p_resource_id => l_identity_salesforce_id,
x_selected_rule_id => l_selected_rule_id,
x_matched_partner_count => l_matched_partner_count,
x_failure_code => l_failure_code,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
'selected_rule_id=' || l_selected_rule_id);
IF l_selected_rule_id IS NULL
THEN
OPEN c_get_lead_name(l_lead_id);
OPEN c_get_lead_rule_name(l_lead_id, l_selected_rule_id);
l_lead_workflow_rec.last_updated_by := fnd_global.user_id;
l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Sales_Team_Rec.last_update_date := SYSDATE;
l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
SELECT 'X' flag
FROM pv_lead_workflows
WHERE lead_id = pc_lead_id
AND latest_routing_flag = 'Y'
AND routing_status IN ('ACTIVE','MATCHED','OFFERED');
SELECT TO_CHAR(PV_LEAD_WORKFLOWS_S.nextval) INTO l_item_key FROM dual;
SELECT user_name
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_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 = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> '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 = '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 employee_person_id
FROM as_salesforce_v
WHERE salesforce_id = c_salesforce_id;
SELECT distinct acp.partner_id, rownum
FROM ams_source_codes ac, ams_act_partners acp, pv_partner_profiles pp
WHERE ac.source_code_id = pc_source_promotion_id
AND ac.arc_source_code_for in ('CAMP', 'CSCH') AND ac.source_code_for_id = acp.act_partner_used_by_id
AND acp.arc_act_partner_used_by = ac.arc_source_code_for AND acp.partner_id = pp.partner_id;
select customer_id, address_id, source_promotion_id from as_leads_all where lead_id = pc_lead_id;
SELECT b.opp_open_status_flag, c.lead_id , c.routing_status
FROM as_leads_all a,
as_statuses_b b,
pv_lead_workflows c
WHERE a.lead_id = l_lead_id AND a.status = b.status_code AND
b.opp_flag = 'Y' AND a.lead_id = c.lead_id (+) and c.latest_routing_flag (+) = 'Y')
LOOP
-- -----------------------------------------------------------------------
-- This is not an "open" opportunity. It cannot be routed.
-- -----------------------------------------------------------------------
IF (x.opp_open_status_flag <> 'Y') THEN
l_open_opportunity_flag := FALSE;
FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_CLOSED_OPP');
FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_ALREADY_EXISTS');
SELECT resource_id INTO l_partner_resource_id
FROM jtf_rs_resource_extns
WHERE sysdate between start_date_active and nvl(end_date_active,sysdate)
and source_id = l_partner_id_tbl(i) and category='PARTNER' and rownum = 1;
l_lead_workflow_rec.last_updated_by := fnd_global.user_id;
l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Sales_Team_Rec.last_update_date := SYSDATE;
l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;