[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hzl.country, asl.lead_rank_id, asl.creation_date
FROM as_sales_leads asl,
hz_party_sites hzp,
hz_locations hzl
WHERE hzl.location_id = hzp.location_id
AND hzp.party_site_id = asl.address_id
AND asl.sales_lead_id = c_sales_lead_id;
SELECT rule.process_rule_id, rule.monitor_condition_id, rule.time_lag_num
FROM (
-- ------------------------------------------------------------
-- Country
-- ------------------------------------------------------------
SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
AML_monitor_conditions d
WHERE b.selection_type_code = 'MONITOR_SCOPE'
AND b.attribute_id = pv_check_match_pub.g_a_Country_
AND a.process_type = 'LEAD_MONITOR'
AND a.process_rule_id = b.process_rule_id
AND b.selection_criteria_id = c.selection_criteria_id(+)
AND (b.operator = 'EQUALS' AND c.attribute_value = c_country)
AND a.process_rule_id = d.process_rule_id
AND a.status_code = 'ACTIVE'
AND d.time_lag_from_stage = c_from_stage_changed
-- ------------------------------------------------------------
-- Lead Rating
-- ------------------------------------------------------------
-- INTERSECT
UNION ALL
SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
AML_monitor_conditions d
WHERE b.selection_type_code = 'MONITOR_SCOPE'
AND b.attribute_id =pv_check_match_pub.g_a_Lead_Rating
AND a.process_type = 'LEAD_MONITOR'
AND a.process_rule_id = b.process_rule_id
AND b.selection_criteria_id = c.selection_criteria_id(+)
AND (b.operator = 'EQUALS' AND c.attribute_value = c_lead_rank )
AND a.process_rule_id = d.process_rule_id
AND a.status_code = 'ACTIVE'
AND d.time_lag_from_stage = c_from_stage_changed
) rule
GROUP BY rule.process_rule_id, rule.monitor_condition_id,rule.time_lag_num
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 = 'MONITOR_SCOPE' AND
a.status_code = 'ACTIVE' AND
a.process_type = 'LEAD_MONITOR' AND
SYSDATE >= a.start_date AND SYSDATE <= a.end_date
GROUP BY a.process_rule_id)
ORDER BY COUNT(*) DESC,
rule.time_lag_num ASC;
SELECT item_key
FROM wf_items
WHERE item_type= c_item_type
AND item_key like c_item_key_like
AND end_date is null
ORDER BY item_key desc;
select item_key
from wf_items
where item_type= c_itemtype
AND item_key like c_itemkey_like
ORDER BY to_number (substr(item_key,(instr(item_key,'_')+1) ) ) desc;
SELECT text_value from wf_item_attribute_values
WHERE item_type = c_item_type
AND item_key like c_item_key
AND name = 'TIMELAG_FROM_STAGE'
-- swkhanna 9/8/03
-- AND text_value = 'CREATION_DATE'
ORDER BY item_key desc;
SELECT number_value from wf_item_attribute_values
WHERE item_type = c_item_type
AND item_key = c_item_key
AND name = c_attr_name
ORDER BY item_key desc;
elsif p_lead_action = 'UPDATE' then
l_new_lead := 'N';
/* SELECT TO_CHAR(AS_WORKFLOW_KEYS_S.nextval) INTO itemkey */
/* FROM dual; */
select p_sales_lead_id||'_'||'1' into itemkey from dual;
select p_sales_lead_id || '_' || (substr(l_existing_itemkey,(instr(l_existing_itemkey,'_')+1) ) + 1 ) into itemkey
from dual ;
select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
into l_monitor_launch_date from dual;
l_last_update_date DATE;
SELECT DISTINCT d.monitor_type_code,d.object_version_number,
d.time_lag_from_stage, d.time_lag_to_stage,
d.time_lag_num, d.time_lag_uom_code,
d.expiration_relative, d.Reminder_defined, d.total_reminders,
d.reminder_frequency,d.timeout_defined, d.timeout_duration,
d.timeout_uom_code, d.notify_owner, d.notify_owner_manager
FROM AML_monitor_conditions d
WHERE d.monitor_condition_id = c_monitor_condition_id;
SELECT creation_date, last_update_date, lead_number, status_code,
assign_date, accept_flag, lead_rank_id, expiration_date,
assign_to_salesforce_id, assign_sales_group_id, current_reroutes, description,
customer_id
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
SELECT usr.user_name
FROM as_sales_leads lead, fnd_user usr
WHERE lead.sales_lead_id = c_lead_id
and lead.assign_to_person_id = usr.employee_id;
select source_first_name || ' '||source_last_name
from jtf_rs_resource_extns
where resource_id = c_assign_to_salesforce_id;
/* SELECT usr.user_name */
/* FROM pv_process_rules_b rule, fnd_user usr, jtf_rs_resource_extns res */
/* WHERE rule.process_rule_id = c_process_rule_id */
/* AND rule.owner_resource_id = res.resource_id */
/* and res.user_id = usr.user_id; */
select usr.user_name, res.resource_id
from jtf_rs_rep_managers mgr, fnd_user usr, jtf_rs_resource_extns res
where mgr.manager_person_id = res.source_id
and res.user_id = usr.user_id
and mgr.resource_id= c_resource_id
and mgr.group_id = c_group_id
and mgr.start_date_active <= SYSDATE
and (mgr.end_date_active IS NULL OR mgr.end_date_active >= SYSDATE)
and mgr.reports_to_flag = 'Y';
select meaning
from as_lookups
where lookup_type = c_lookup_type
and lookup_code = c_lookup_code;
select meaning
from as_statuses_vl
where status_code = c_status_code;
select party_name
from hz_parties
where party_id = c_customer_id;
select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
into l_monitor_launch_date from dual;
fetch c_get_lead_details into l_creation_date, l_last_update_date, l_lead_number, l_status_code, l_assign_date,
l_accept_flag, l_lead_rank_id , l_expiration_date,
l_resource_id, l_group_id , l_current_reroutes, l_description,l_customer_id;
aname => 'LEAD_UPDATED_DATE',
avalue => l_last_update_date);
SELECT aml_MONITOR_LOG_S.nextval into l_monitor_log_id FROM sys.dual;
INSERT INTO aml_MONITOR_LOG(
MONITOR_LOG_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,MONITOR_CONDITION_ID
,RECIPIENT_ROLE
,MONITOR_ACTION
,RECIPIENT_RESOURCE_ID
,SALES_LEAD_ID
) VALUES (
l_monitor_log_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,1
,null
,null
,null
,null
,l_monitor_condition_id
,'OWNER'
,l_monitor_action
,l_lead_owner_resource_id
,l_sales_lead_id
);
update as_sales_leads
set current_reroutes = l_current_reroutes
where sales_lead_id = l_sales_lead_id;
select source_id, source_name, source_email, user_name
from jtf_rs_resource_extns
where resource_id = c_esc_mgr_resource_id;
l_curr_last_update_date date;
l_orig_last_update_date date;
select 'Y'
from as_statuses_b
where lead_flag = 'Y'
and status_code = c_status_code;
select status_code
from as_sales_leads
where sales_lead_id = c_sales_lead_id;
select 'Y'
from as_lookups
where lookup_type = 'TIME_LAG_TO_STAGE'
and lookup_code = c_lookup_code;
select accept_flag
from as_sales_leads
where sales_lead_id = c_sales_lead_id;
SELECT nvl(opp_open_status_flag, 'Y')
INTO l_opp_open_status_flag
FROM as_statuses_b
WHERE status_code = l_lead_current_status;
elsif l_timelag_to_stage = 'LAST_UPDATE_DATE' then
-- chk to see if lead has been updated since the workflow started
l_orig_last_update_date := wf_engine.GetItemAttrDate(
itemtype => itemtype,
itemkey => itemkey,
aname => 'LEAD_UPDATED_DATE');
select last_update_date
into l_curr_last_update_date
from as_sales_leads
where sales_lead_id = l_sales_lead_id;
if l_orig_last_update_date < l_curr_last_update_date then
l_condition_true := 'N';
SELECT nvl(opp_open_status_flag, 'Y')
INTO l_opp_open_status_flag
FROM as_statuses_b
WHERE status_code = l_lead_current_status;
elsif l_timelag_to_stage = 'LAST_UPDATE_DATE' then
-- chk to see if lead has been updated since the workflow started
l_orig_last_update_date := wf_engine.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'LEAD_UPDATED_DATE');
select last_update_date
into l_curr_last_update_date
from as_sales_leads
where sales_lead_id = l_sales_lead_id;
if l_orig_last_update_date = l_curr_last_update_date then
l_condition_true := 'Y';
end if; -- last_update_date
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category = 'EMPLOYEE'
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 = '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 res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT hzl.country, asl.lead_rank_id
FROM as_sales_leads asl,
hz_party_sites hzp,
hz_locations hzl
WHERE hzl.location_id = hzp.location_id
AND hzp.party_site_id = asl.address_id
AND asl.sales_lead_id = c_sales_lead_id;
SELECT rule.process_rule_id, rule.monitor_condition_id, rule.time_lag_num
FROM (
-- ------------------------------------------------------------
-- Country
-- ------------------------------------------------------------
SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
AML_monitor_conditions d
WHERE b.selection_type_code = 'MONITOR_SCOPE'
AND b.attribute_id = pv_check_match_pub.g_a_Country_
AND a.process_type = 'LEAD_MONITOR'
AND a.process_rule_id = b.process_rule_id
AND b.selection_criteria_id = c.selection_criteria_id(+)
AND (b.operator = 'EQUALS' AND c.attribute_value = c_country)
AND a.process_rule_id = d.process_rule_id
AND a.status_code = 'ACTIVE'
AND d.time_lag_from_stage = c_from_stage_changed
-- ------------------------------------------------------------
-- Lead Rating
-- ------------------------------------------------------------
INTERSECT
SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
AML_monitor_conditions d
WHERE b.selection_type_code = 'MONITOR_SCOPE'
AND b.attribute_id =pv_check_match_pub.g_a_Lead_Rating
AND a.process_type = 'LEAD_MONITOR'
AND a.process_rule_id = b.process_rule_id
AND b.selection_criteria_id = c.selection_criteria_id(+)
AND (b.operator = 'EQUALS' AND c.attribute_value = c_lead_rank )
AND a.process_rule_id = d.process_rule_id
AND a.status_code = 'ACTIVE'
AND d.time_lag_from_stage = c_from_stage_changed
) rule
GROUP BY rule.process_rule_id, rule.monitor_condition_id,rule.time_lag_num
HAVING (rule.process_rule_id) IN (
SELECT a.process_rule_id
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 = 'MONITOR_SCOPE' AND
a.status_code = 'ACTIVE' AND
a.process_type = 'LEAD_MONITOR' AND
SYSDATE >= a.start_date AND SYSDATE <= a.end_date
GROUP BY a.process_rule_id)
ORDER BY rule.time_lag_num ASC;
select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
into l_monitor_launch_date from dual;
select timezone_id
into l_GMT_timezone_id
from hz_timezones_vl
where name = 'GMT';
/* update pv_lead_workflows set matched_due_date = l_matched_due_date,
object_version_number = object_version_number + 1
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey;*/
SELECT assign_date, assign_to_salesforce_id, assign_sales_group_id, assign_to_person_id
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
SELECT usr.user_name
FROM as_sales_leads lead, fnd_user usr
WHERE lead.sales_lead_id = c_lead_id
and lead.assign_to_person_id = usr.employee_id;
select source_first_name || ' '||source_last_name
from jtf_rs_resource_extns
where resource_id = c_assign_to_salesforce_id;
select usr.user_name, res.resource_id
from jtf_rs_rep_managers mgr, fnd_user usr, jtf_rs_resource_extns res
where mgr.manager_person_id = res.source_id
and res.user_id = usr.user_id
and mgr.resource_id= c_resource_id
and mgr.group_id = c_group_id
and mgr.start_date_active <= SYSDATE
and (mgr.end_date_active IS NULL OR mgr.end_date_active >= SYSDATE)
and mgr.reports_to_flag = 'Y';
select source_id, source_name, source_email, user_name
from jtf_rs_resource_extns
where resource_id = c_resource_id;
select instr(l_notify_role_list, l_manager_username) into l_number from dual;
select instr(l_notify_role_list, l_mgr_name) into l_number from dual;