[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_CHANNEL_SELECTION CONSTANT VARCHAR2(30) := 'CHANNEL_SELECTION';
SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.ASSIGN_TO_SALESFORCE_ID,
SL.ASSIGN_TO_PERSON_ID, SL.ASSIGN_SALES_GROUP_ID,
SL.QUALIFIED_FLAG, SL.PARENT_PROJECT,
SL.CHANNEL_CODE, SL.DECISION_TIMEFRAME_CODE, SL.BUDGET_AMOUNT,
SL.BUDGET_STATUS_CODE, SL.SOURCE_PROMOTION_ID, SL.STATUS_CODE,
SL.REJECT_REASON_CODE, SL.LEAD_RANK_ID
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT NVL(channel.indirect_channel_flag, 'N')
FROM pv_channel_types channel
WHERE channel.channel_lookup_code = c_channel_code;
Rate_Select_Lead(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_id => P_Sales_Lead_id,
P_Process_Type => G_LEAD_QUALIFICATION,
-- ckapoor Phase 2 filtering project 11.5.10
-- P_Is_Create_Mode => P_Is_Create_Mode,
X_Action_Value => l_sales_lead_rec.qualified_flag,
-- l_action_value,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
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_status_code => l_sales_lead_rec.status_code,
p_assign_to_person_id => l_sales_lead_rec.assign_to_person_id,
p_assign_to_salesforce_id => l_sales_lead_rec.assign_to_salesforce_id,
p_reject_reason_code => l_sales_lead_rec.reject_reason_code,
p_assign_sales_group_id => l_sales_lead_rec.assign_sales_group_id,
p_lead_rank_id => l_sales_lead_rec.lead_rank_id,
p_qualified_flag => l_sales_lead_rec.qualified_flag,
p_category => NULL,
p_manual_rank_flag => 'N');
UPDATE as_sales_leads
SET lead_rank_id = l_sales_lead_rec.lead_rank_id,
lead_rank_ind = 'N'
WHERE sales_lead_id = p_sales_lead_id;
Rate_Select_Lead(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_id => P_Sales_Lead_id,
P_Process_Type => G_LEAD_RATING,
-- ckapoor Phase 2 filtering project 11.5.10
-- P_Is_Create_Mode => P_Is_Create_Mode,
X_Action_Value => l_action_value,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
Rate_Select_Lead(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_id => P_Sales_Lead_id,
P_Process_Type => G_CHANNEL_SELECTION,
-- ckapoor Phase 2 filtering project 11.5.10
-- P_Is_Create_Mode => P_Is_Create_Mode,
X_Action_Value => l_sales_lead_rec.channel_code,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
END IF; -- run channel selection engine
PROCEDURE Rate_Select_Lead(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
P_Validation_Level IN NUMBER,
P_Admin_Group_Id IN NUMBER,
P_identity_salesforce_id IN NUMBER,
P_Sales_Lead_id IN NUMBER,
P_Process_Type IN VARCHAR2,
-- ckapoor Phase 2 filtering project 11.5.10
-- P_Is_Create_Mode IN VARCHAR2,
X_Action_Value OUT NOCOPY VARCHAR2,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
SELECT SL.CUSTOMER_ID,
SL.ADDRESS_ID,
SL.SOURCE_PROMOTION_ID,
TO_CHAR(SL.CREATION_DATE, 'YYYYMMDD') || '000000',
SL.STATUS_CODE,
SL.ASSIGN_TO_PERSON_ID,
SL.ASSIGN_TO_SALESFORCE_ID,
SL.REJECT_REASON_CODE,
SL.ASSIGN_SALES_GROUP_ID,
SL.QUALIFIED_FLAG,
-- ckapoor changed
SL.LEAD_RANK_ID,
PARTY.CATEGORY_CODE,
CNT.EMAIL_ADDRESS
-- ckapoor Phase 2 Filtering 11.5.10. Find the mode of caller
-- , SL.IMPORT_FLAG
FROM AS_SALES_LEADS SL, HZ_PARTIES PARTY, HZ_CONTACT_POINTS CNT
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND SL.PRIMARY_CNT_PERSON_PARTY_ID = CNT.OWNER_TABLE_ID(+)
AND CNT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CNT.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND CNT.STATUS(+) = 'A';
select phone_area_code from HZ_CONTACT_POINTS phone,
as_sales_leads lead , hz_parties party
where
lead.sales_lead_id = C_Sales_Lead_ID
and ((lead.customer_id = party.party_id and party.party_type = 'PERSON'
and PHONE.OWNER_TABLE_NAME='HZ_PARTIES' and PHONE.OWNER_TABLE_ID=lead.customer_id
and PHONE.PRIMARY_FLAG ='Y' and PHONE.CONTACT_POINT_TYPE='PHONE' )
or (PHONE.OWNER_TABLE_NAME='HZ_PARTIES' and PHONE.OWNER_TABLE_ID=lead.primary_contact_party_id
and PHONE.PRIMARY_FLAG ='Y' and PHONE.CONTACT_POINT_TYPE='PHONE'
and lead.primary_contact_party_id = party.party_id
and party.party_type = 'PARTY_RELATIONSHIP'));
SELECT phon.phone_area_code
FROM hz_contact_points phon
WHERE phon.owner_table_id = c_address_id
AND phon.owner_table_name = 'HZ_PARTY_SITES'
AND phon.contact_point_type = 'PHONE'
AND phon.status in ('A','I')
UNION ALL
SELECT phon.phone_area_code
FROM hz_contact_points phon
WHERE c_address_id IS NULL
AND phon.owner_table_id = c_customer_id
AND phon.owner_table_name = 'HZ_PARTIES'
AND phon.contact_point_type = 'PHONE'
AND phon.status in ('A','I'); */
SELECT LOC.COUNTRY, LOC.STATE, LOC.PROVINCE, LOC.COUNTY,
LOC.CITY, LOC.POSTAL_CODE
FROM HZ_PARTY_SITES SITE, HZ_LOCATIONS LOC
WHERE SITE.PARTY_SITE_ID = c_address_id
AND SITE.LOCATION_ID = LOC.LOCATION_ID;
select sc.custom_setup_id from ams_p_source_codes_v sc, as_sales_leads sl
where
sl.sales_lead_id = c_sales_lead_id and sl.source_promotion_id = sc.source_code_id;
/* select v.custom_setup_id from as_sales_leads sl,
(
select cs.custom_setup_id custom_setup_id, sc.source_code_id
from ams_campaign_schedules_vl cs, ams_source_codes sc , ams_custom_setups_vl csv
where cs.source_code = sc.source_code and csv.object_type = 'CSCH' and csv.custom_setup_id = cs.custom_setup_id
and sc.arc_source_code_for = 'CSCH'
union
select eo.setup_type_id custom_setup_id, sc.source_code_id
from ams_event_offers_vl eo,ams_source_codes sc , ams_custom_setups_vl csv
where eo.source_code = sc.source_code and csv.object_type in ('EVEO', 'EONE') and csv.custom_setup_id = eo.setup_type_id
and sc.arc_source_code_for in ('EVEO','EONE')
union
select eh.setup_type_id custom_setup_id, sc.source_code_id
from ams_event_headers_vl eh,ams_source_codes sc , ams_custom_setups_vl csv
where eh.source_code = sc.source_code and csv.object_type in ('EVEH') and csv.custom_setup_id = eh.setup_type_id
and sc.arc_source_code_for = 'EVEH'
union
select ca.custom_setup_id custom_setup_id, sc.source_code_id
from ams_campaigns_vl ca,ams_source_codes sc, ams_custom_setups_vl csv
where ca.source_code = sc.source_code and csv.object_type in ('ECAM', 'COLL', 'DEAL', 'PARTNER', 'TRDP', 'EVCAM')
and csv.custom_setup_id = ca.custom_setup_id
and sc.arc_source_code_for = 'CAMP'
) v
where
v.source_code_id = sl.source_promotion_id and sl.sales_lead_id = c_sales_lead_id
; */
SELECT rule.process_rule_id, rule.rank, rule.currency_code
FROM (
-- -------------------------------------------------------------------
-- Country
-- -------------------------------------------------------------------
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Country_ AND
a.process_type = c_process_type 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) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_country) OR
(b.operator = 'IS_NOT_NULL' AND c_country IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_country IS NULL))
-- -------------------------------------------------------------------
-- Campaign
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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 = c_process_type 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 = TO_CHAR(c_source_promotion_id)) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> TO_CHAR(c_source_promotion_id)) OR
(b.operator = 'IS_NOT_NULL' AND c_source_promotion_id IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_source_promotion_id IS NULL))
-- ckapoor Custom setup type project
-- -------------------------------------------------------------------
-- Campaign setup type
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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 =
--575
pv_check_match_pub.g_a_Campaign_Setup_Type
AND
a.process_type = c_process_type 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 = TO_CHAR(c_custom_setup_id)) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> TO_CHAR(c_custom_setup_id)) OR
(b.operator = 'IS_NOT_NULL' AND c_custom_setup_id IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_custom_setup_id IS NULL))
-- -------------------------------------------------------------------
-- Lead Status
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Lead_Status AND
a.process_type = c_process_type 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_status_code) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_status_code) OR
(b.operator = 'IS_NOT_NULL' AND c_status_code IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_status_code IS NULL))
-- -------------------------------------------------------------------
-- Product Interest
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c,
as_sales_lead_lines asll
WHERE a.process_rule_id = b.process_rule_id AND
b.selection_criteria_id = c.selection_criteria_id(+) AND
b.attribute_id = pv_check_match_pub.g_a_Product_Interest AND
a.process_type = c_process_type AND
b.selection_type_code = 'INPUT_FILTER' AND
asll.sales_lead_id = c_sales_lead_id AND
((b.operator = 'IS_NOT_NULL' AND asll.CATEGORY_ID IS NOT NULL) OR
(b.operator = 'EQUALS' AND TO_NUMBER(C.attribute_value) IN
(select category_id from eni_prod_den_hrchy_parents_v
where category_id in (
select category_parent_id from eni_denorm_hrchy_parents
start with category_id = ASLL.CATEGORY_ID
connect by prior category_parent_id = category_id
union all
select ASLL.CATEGORY_ID from dual)
and disable_date is null and
purchase_interest = 'Y' )))
-- -------------------------------------------------------------------
-- Date Created
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
FROM pv_process_rules_b a,
pv_enty_select_criteria b,
pv_selected_attr_values c
WHERE a.process_rule_id = b.process_rule_id AND
b.selection_type_code = 'INPUT_FILTER' AND
b.selection_criteria_id = c.selection_criteria_id(+) AND
b.attribute_id = pv_check_match_pub.g_a_Creation_Date AND
a.process_type = c_process_type AND
((b.operator = 'EQUALS' AND c_creation_date = c.attribute_value) OR
(b.operator = 'NOT_EQUALS' AND c_creation_date <> c.attribute_value) OR
(b.operator = 'LESS_THAN' AND c_creation_date < c.attribute_value) OR
(b.operator = 'LESS_THAN_OR_EQUALS' AND c_creation_date <= c.attribute_value) OR
(b.operator = 'GREATER_THAN' AND c_creation_date > c.attribute_value) OR
(b.operator = 'GREATER_THAN_OR_EQUALS' AND c_creation_date >= c.attribute_value) OR
(b.operator = 'IS_NOT_NULL' AND c_creation_date IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_creation_date IS NULL) OR
(b.operator = 'BETWEEN' AND
(c_creation_date BETWEEN c.attribute_value AND
c.attribute_to_value)))
-- -------------------------------------------------------------------
-- Area Code
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Area_Code AND
a.process_type = c_process_type 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_area_code) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_area_code) OR
(b.operator = 'IS_NOT_NULL' AND c_area_code IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_area_code IS NULL) OR
(b.operator = 'CONTAINS' AND upper(c_area_code) like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'NOT_CONTAINS' AND upper(c_area_code) not like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'BEGINS_WITH' AND upper(c_area_code) like upper(c.attribute_value||'%')) OR
(b.operator = 'ENDS_WITH' AND upper(c_area_code) like upper('%'||c.attribute_value)) OR
(b.operator = 'BETWEEN' AND upper(c_area_code) between upper(c.attribute_value) and upper(c.attribute_to_value))
)
-- -------------------------------------------------------------------
-- State
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_State_ AND
a.process_type = c_process_type 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_state) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_state) OR
(b.operator = 'IS_NOT_NULL' AND c_state IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_state IS NULL))
-- -------------------------------------------------------------------
-- Province
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Province AND
a.process_type = c_process_type 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_province) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_province) OR
(b.operator = 'IS_NOT_NULL' AND c_province IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_province IS NULL) OR
(b.operator = 'CONTAINS' AND upper(c_province) like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'NOT_CONTAINS' AND upper(c_province) not like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'BEGINS_WITH' AND upper(c_province) like upper(c.attribute_value||'%')) OR
(b.operator = 'ENDS_WITH' AND upper(c_province) like upper('%'||c.attribute_value)) OR
(b.operator = 'BETWEEN' AND upper(c_province) between upper(c.attribute_value) and upper(c.attribute_to_value))
)
-- -------------------------------------------------------------------
-- County
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_County AND
a.process_type = c_process_type 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_county) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_county) OR
(b.operator = 'IS_NOT_NULL' AND c_county IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_county IS NULL) OR
(b.operator = 'CONTAINS' AND upper(c_county) like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'NOT_CONTAINS' AND upper(c_county) not like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'BEGINS_WITH' AND upper(c_county) like upper(c.attribute_value||'%')) OR
(b.operator = 'ENDS_WITH' AND upper(c_county) like upper('%'||c.attribute_value)) OR
(b.operator = 'BETWEEN' AND upper(c_county) between upper(c.attribute_value) and upper(c.attribute_to_value))
)
-- -------------------------------------------------------------------
-- City
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_City AND
a.process_type = c_process_type 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_city) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_city) OR
(b.operator = 'IS_NOT_NULL' AND c_city IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_city IS NULL) OR
(b.operator = 'CONTAINS' AND upper(c_city) like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'NOT_CONTAINS' AND upper(c_city) not like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'BEGINS_WITH' AND upper(c_city) like upper(c.attribute_value||'%')) OR
(b.operator = 'ENDS_WITH' AND upper(c_city) like upper('%'||c.attribute_value)) OR
(b.operator = 'BETWEEN' AND upper(c_city) between upper(c.attribute_value) and upper(c.attribute_to_value))
)
-- -------------------------------------------------------------------
-- Postal Code
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Postal_Code AND
a.process_type = c_process_type 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_postal_code) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_postal_code) OR
(b.operator = 'IS_NOT_NULL' AND c_postal_code IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_postal_code IS NULL) OR
(b.operator = 'CONTAINS' AND upper(c_postal_code) like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'NOT_CONTAINS' AND upper(c_postal_code) not like upper('%'||c.attribute_value||'%')) OR
(b.operator = 'BEGINS_WITH' AND upper(c_postal_code) like upper(c.attribute_value||'%')) OR
(b.operator = 'ENDS_WITH' AND upper(c_postal_code) like upper('%'||c.attribute_value)) OR
(b.operator = 'BETWEEN' AND upper(c_postal_code) between upper(c.attribute_value) and upper(c.attribute_to_value))
)
-- -------------------------------------------------------------------
-- Customer Category
-- -------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_Customer_Category AND
a.process_type = c_process_type 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_category_code) OR
(b.operator = 'NOT_EQUALS' AND c.attribute_value <> c_category_code) OR
(b.operator = 'IS_NOT_NULL' AND c_category_code IS NOT NULL) OR
(b.operator = 'IS_NULL' AND c_category_code IS NULL))
-- ----------------------------------------------------------------
-- All
-------------------------------------------------------------------
UNION ALL
SELECT DISTINCT a.process_rule_id, a.rank, a.currency_code
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_all AND
a.process_type = c_process_type 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 = 'Y'
) rule
GROUP BY rule.process_rule_id, rule.rank, rule.currency_code
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 = c_process_type AND
SYSDATE BETWEEN a.start_date AND a.end_date
GROUP BY a.process_rule_id)
ORDER BY rule.rank DESC;
SELECT rule.process_rule_id, rule.action, rule.action_value,
rank.min_score
FROM pv_process_rules_b rule, as_sales_lead_ranks_b rank
WHERE rule.parent_rule_id = c_process_rule_id
AND rank.rank_id = TO_NUMBER(rule.action_value)
ORDER BY rule.rank;
SELECT rule.process_rule_id, rule.action, rule.action_value,
NVL(channel.rank, 0)
FROM pv_process_rules_b rule, pv_channel_types channel
WHERE rule.parent_rule_id = c_process_rule_id
AND channel.channel_lookup_code(+) = rule.action_value
ORDER BY rule.rank;
SELECT rule.process_rule_id, rule.action, rule.action_value ,
decode(rule.action_value, 'Y', 1, 'N', 0)
FROM pv_process_rules_b rule
WHERE rule.parent_rule_id = c_process_rule_id
ORDER BY rule.rank; -- just make sure 'Y' comes before 'N'
SELECT cra.selection_criteria_id, cra.attribute_id, cra.operator,
val.attribute_value, val.attribute_to_value
FROM pv_enty_select_criteria cra, pv_selected_attr_values val
WHERE cra.process_rule_id = c_process_rule_id
AND cra.selection_type_code = 'CRITERION'
AND cra.selection_criteria_id = val.selection_criteria_id(+)
ORDER BY cra.selection_criteria_id;
SELECT NVL(min_score, 0)
FROM as_sales_lead_ranks_b
WHERE rank_id = c_rank_id;
CURSOR c_get_enty_select_criteria(c_winning_rule_id NUMBER) IS
SELECT c.selection_criteria_id, c.attribute_id, c.selection_type_code, c.operator,
b.return_type
FROM pv_enty_select_criteria c, pv_attributes_b b
WHERE c.process_rule_id = c_winning_rule_id
and b.attribute_id = c.attribute_id;
CURSOR c_get_selected_attr_values(c_sel_cri_id NUMBER) IS
SELECT attr_value_id, attribute_value, attribute_to_value
FROM pv_selected_attr_values
WHERE selection_criteria_id = c_sel_cri_id;
l_api_name CONSTANT VARCHAR2(30) := 'Rate_Select_Lead';
l_prev_selection_criteria_id NUMBER;
l_selection_criteria_id NUMBER;
l_fail_rule_selection_flag VARCHAR2(1) := FND_API.G_FALSE;
l_rows_inserted NUMBER;
l_enty_select_criteria_val c_get_enty_select_criteria%ROWTYPE;
l_selected_attr_values_val c_get_selected_attr_values%ROWTYPE;
SAVEPOINT RATE_SELECT_LEAD_PVT;
UPDATE pv_entity_rules_applied
SET latest_flag = 'N'
WHERE entity = 'SALES_LEAD'
AND entity_id = p_sales_lead_id
AND process_type = p_process_type;
ELSIF p_process_type = G_CHANNEL_SELECTION
THEN
-- Channel selection engine
OPEN c_get_channel_criterion_rule(
l_process_rule_id_tbl(l_rule_index));
l_prev_selection_criteria_id := FND_API.G_MISS_NUM;
l_selection_criteria_id, l_attribute_id,
l_operator, l_attr_value, l_attr_to_value;
'sel_cra_id=' || l_selection_criteria_id);
IF l_selection_criteria_id <>
l_prev_selection_criteria_id
THEN
IF l_prev_attribute_id <> FND_API.G_MISS_NUM
THEN
IF (AS_DEBUG_LOW_ON) THEN
AS_UTILITY_PVT.Debug_Message(
FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'rul_val=' || l_rule_attr_value);
END IF; -- l_selection_criteria_id <>
l_prev_selection_criteria_id :=
l_selection_criteria_id;
PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
px_ENTITY_RULE_APPLIED_ID =>
l_entity_rule_applied_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_OBJECT_VERSION_NUMBER => 1
,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_ENTITY => 'SALES_LEAD'
,p_ENTITY_ID => p_sales_lead_id
,p_PROCESS_RULE_ID =>
l_criterion_rule_id_tbl(l_criterion_rule_index)
,p_PARENT_PROCESS_RULE_ID =>
l_process_rule_id_tbl(l_rule_index)
,p_LATEST_FLAG => 'Y'
,p_ACTION_VALUE =>
l_action_value_tbl(l_criterion_rule_index)
,p_PROCESS_TYPE => p_process_type
--,p_WINNING_RULE_FLAG => 'Y'
,p_WINNING_RULE_FLAG => 'N'
,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_PROCESS_STATUS => G_PASS_RULE
,p_ENTITY_DETAIL => l_status_code);
PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
px_ENTITY_RULE_APPLIED_ID =>
l_entity_rule_applied_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_OBJECT_VERSION_NUMBER => 1
,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_ENTITY => 'SALES_LEAD'
,p_ENTITY_ID => p_sales_lead_id
,p_PROCESS_RULE_ID =>
l_criterion_rule_id_tbl(l_criterion_rule_index)
,p_PARENT_PROCESS_RULE_ID =>
l_process_rule_id_tbl(l_rule_index)
,p_LATEST_FLAG => 'Y'
,p_ACTION_VALUE =>
l_action_value_tbl(l_criterion_rule_index)
,p_PROCESS_TYPE => p_process_type
,p_WINNING_RULE_FLAG => NULL
,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_PROCESS_STATUS => G_FAIL_CRITERIA
,p_ENTITY_DETAIL => l_status_code);
PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_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_OBJECT_VERSION_NUMBER => 1
,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_ENTITY => 'SALES_LEAD'
,p_ENTITY_ID => p_sales_lead_id
,p_PROCESS_RULE_ID => NULL
,p_PARENT_PROCESS_RULE_ID =>
l_process_rule_id_tbl(l_rule_index)
,p_LATEST_FLAG => 'Y'
,p_ACTION_VALUE => NULL
,p_PROCESS_TYPE => p_process_type
,p_WINNING_RULE_FLAG => NULL
,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_PROCESS_STATUS => G_FAIL_CRITERIA
,p_ENTITY_DETAIL => l_status_code);
UPDATE pv_entity_rules_applied
SET WINNING_RULE_FLAG = 'Y'
WHERE entity = 'SALES_LEAD'
AND entity_id = p_sales_lead_id
AND process_type = p_process_type
AND latest_flag = 'Y'
AND process_rule_id = l_final_cron_rule_id
AND parent_process_rule_id = l_process_rule_id_tbl(l_final_index)
returning entity_rule_applied_id into l_winning_rule_ent_rule_app_id ;
FOR l_enty_select_criteria_val in c_get_enty_select_criteria(l_final_cron_rule_id) LOOP
AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Within the outerloop for winning rule value logging');
AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'selection_criteria_id ::'||l_enty_select_criteria_val.selection_criteria_id);
FOR l_selected_attr_values_val in c_get_selected_attr_values(l_enty_select_criteria_val.selection_criteria_id) LOOP
AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Within the innerloop for winning rule value logging');
AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'attr_value_id ::'||l_selected_attr_values_val.attr_value_id);
-- l_concat_attribute_value := l_selected_attr_values_val.attribute_value;
if (l_selected_attr_values_val.attribute_value IS NOT NULL) then
l_concat_attribute_value := l_concat_attribute_value || l_delimiter || l_selected_attr_values_val.attribute_value;
if (l_enty_select_criteria_val.return_type = 'CURRENCY') then
l_concat_attribute_value := l_concat_attribute_value || ':::' || l_currency_code_tbl(l_final_index);
-- l_concat_attribute_to_value := l_selected_attr_values_val.attribute_to_value;
if (l_selected_attr_values_val.attribute_to_value IS NOT NULL) then
l_concat_attribute_to_value := l_concat_attribute_to_value || l_delimiter || l_selected_attr_values_val.attribute_to_value;
if (l_enty_select_criteria_val.return_type = 'CURRENCY') then
l_concat_attribute_to_value := l_concat_attribute_to_value || ':::' || l_currency_code_tbl(l_final_index);
END LOOP; -- for l_selected_attr_values_val
'attribute_id ::' || l_enty_select_criteria_val.attribute_id);
'operator :: ' || l_enty_select_criteria_val.operator);
'PV :: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
Debug('PV again:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
if (l_enty_select_criteria_val.attribute_id = pv_check_match_pub.g_a_Product_Interest) then
l_entity_attr_value_tbl.delete(l_enty_select_criteria_val.attribute_id);
p_attribute_id => l_enty_select_criteria_val.attribute_id,
p_entity => 'SALES_LEAD',
p_entity_id => p_sales_lead_id,
p_delimiter => l_delimiter,
p_expand_attr_flag => 'N',
x_entity_attr_value => l_entity_attr_value_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'PV 510:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
Debug('PV again 510:: ' || l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value);
AML_RULE_APPLIED_ATTRS_PKG.Insert_Row(
px_RULE_APPLIED_ATTR_ID => l_rule_applied_attrs_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_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
,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_ENTITY_RULE_APPLIED_ID => l_winning_rule_ent_rule_app_id
,p_ATTRIBUTE_ID => l_enty_select_criteria_val.attribute_id
,p_OPERATOR => l_enty_select_criteria_val.operator
,p_ATTRIBUTE_VALUE => l_concat_attribute_value
,p_ATTRIBUTE_TO_VALUE => l_concat_attribute_to_value
,p_LEAD_VALUE => l_entity_attr_value_tbl(l_enty_select_criteria_val.attribute_id).attribute_value
);
END LOOP ; -- for l_enty_select_criteria_val
-- for every attribute, goto pv_selected_attr_values and construct
--concatenated string.
-- for every attribute , goto pv_entity)attrs using attribute_id, and entity combo
--and get sql_text. Unique row
-- Use dynamic sql ?? to run the sql_text. This may return multiple records
-- if # > 1 then concatenate
-- Confirm if the above comments are needed
IF p_process_type = G_LEAD_RATING
THEN
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
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_status_code => l_status_code,
p_assign_to_person_id => l_assign_to_person_id,
p_assign_to_salesforce_id => l_assign_to_salesforce_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_assign_sales_group_id,
p_lead_rank_id => TO_NUMBER(l_action_value),
p_qualified_flag => l_qualified_flag,
p_category => NULL,
p_manual_rank_flag => 'N');
UPDATE as_sales_leads
SET lead_rank_ind = 'N'
WHERE sales_lead_id = p_sales_lead_id;
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
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_status_code => l_status_code,
p_assign_to_person_id => l_assign_to_person_id,
p_assign_to_salesforce_id => l_assign_to_salesforce_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_assign_sales_group_id,
p_lead_rank_id => l_old_lead_rank_id,
p_qualified_flag => l_action_value,
p_category => NULL,
p_manual_rank_flag => NULL );
-- to update value
IF (AS_DEBUG_LOW_ON) THEN
AS_UTILITY_PVT.Debug_Message( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'act:' || l_action);
ELSIF p_process_type = G_CHANNEL_SELECTION
THEN
-- CHANNEL_SELECTION
dbms_sql.bind_variable(l_cursor, ':channel_code', l_action_value);
l_rows_inserted := dbms_sql.execute(l_cursor);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
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_status_code => l_status_code,
p_assign_to_person_id => l_assign_to_person_id,
p_assign_to_salesforce_id => l_assign_to_salesforce_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_assign_sales_group_id,
p_lead_rank_id => l_default_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL,
p_manual_rank_flag => 'N');
UPDATE as_sales_leads
SET lead_rank_id = l_default_lead_rank_id,
lead_rank_ind = 'N'
WHERE sales_lead_id = p_sales_lead_id;
ELSIF p_process_type = G_CHANNEL_SELECTION
THEN
l_default_channel_code :=
FND_PROFILE.Value('AS_DEFAULT_LEAD_ENGINE_CHANNEL');
UPDATE as_sales_leads
SET channel_code = l_default_channel_code
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_sales_leads
SET qualified_flag = l_default_qualified_flag
WHERE sales_lead_id = p_sales_lead_id;
PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_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_OBJECT_VERSION_NUMBER => 1
,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_ENTITY => 'SALES_LEAD'
,p_ENTITY_ID => p_sales_lead_id
,p_PROCESS_RULE_ID => NULL
,p_PARENT_PROCESS_RULE_ID => NULL
,p_LATEST_FLAG => 'Y'
,p_ACTION_VALUE => x_action_value
,p_PROCESS_TYPE => p_process_type
,p_WINNING_RULE_FLAG => NULL
,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_PROCESS_STATUS => G_DEFAULT
,p_ENTITY_DETAIL => l_status_code);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => p_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
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_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR,
p_CUSTOMER_ID => FND_API.G_MISS_NUM,
p_ADDRESS_ID => FND_API.G_MISS_NUM,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => FND_API.G_MISS_CHAR,
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_ASSIGN_TO_PERSON_ID => FND_API.G_MISS_NUM,
p_ASSIGN_TO_SALESFORCE_ID => FND_API.G_MISS_NUM,
p_ASSIGN_SALES_GROUP_ID => FND_API.G_MISS_NUM,
p_ASSIGN_DATE => FND_API.G_MISS_DATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => FND_API.G_MISS_CHAR,
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => FND_API.G_MISS_CHAR,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => FND_API.G_MISS_CHAR,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => SYSDATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM
-- 11.5.10 new columns ckapoor
, p_MARKETING_SCORE => FND_API.G_MISS_NUM
, p_INTERACTION_SCORE => FND_API.G_MISS_NUM
, p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR
, p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR
, p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM
, p_SALES_STAGE_ID => FND_API.G_MISS_NUM
);
UPDATE as_sales_leads
SET lead_rank_score = l_lead_rank_score
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_accesses_all
SET lead_rank_score = l_lead_rank_score
WHERE sales_lead_id = p_sales_lead_id;
END Rate_Select_Lead;
SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
SL.ASSIGN_TO_SALESFORCE_ID, SL.STATUS_CODE
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT source_id
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_id;
SELECT opp_open_status_flag
FROM as_statuses_b
WHERE status_code = c_status_code
AND lead_flag = 'Y';
SELECT acc.freeze_flag
FROM as_accesses_all acc
WHERE acc.sales_lead_id = c_sales_lead_id
AND acc.owner_flag = 'Y';
SELECT 'Y'
FROM as_accesses_all acc
WHERE acc.sales_lead_id = c_sales_lead_id
AND acc.created_by_tap_flag = 'Y';
DELETE from as_accesses_all acc
WHERE acc.sales_lead_id = p_sales_lead_id
AND nvl(acc.freeze_flag,'N') = 'N'
AND acc.created_by_tap_flag = 'N';
l_Sales_Team_Rec.last_update_date := SYSDATE;
l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
customer_id, address_id, sales_lead_id, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, change_type, delete_flag, insert_flag,
processed_flag)
VALUES
(l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
'Y');
UPDATE AS_CHANGED_ACCOUNTS_ALL
SET processed_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id;
PROCEDURE Lead_Process_After_Update(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
p_Commit IN VARCHAR2,
p_Validation_Level IN NUMBER,
P_Check_Access_Flag IN VARCHAR2,
p_Admin_Flag IN VARCHAR2,
P_Admin_Group_Id IN NUMBER,
P_identity_salesforce_id IN NUMBER,
P_Salesgroup_id IN NUMBER,
P_Sales_Lead_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Lead_Process_After_Update';
SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.REFERRAL_TYPE,
SL.ASSIGN_TO_SALESFORCE_ID, SL.QUALIFIED_FLAG,
SL.LEAD_RANK_ID, SL.CHANNEL_CODE, SL.STATUS_CODE,
SL.REJECT_REASON_CODE
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT NVL(channel.indirect_channel_flag, 'N')
FROM pv_channel_types channel
WHERE channel.channel_lookup_code = c_channel_code;
SELECT 'Y'
FROM as_accesses_all acc
WHERE acc.sales_lead_id = c_sales_lead_id
AND acc.owner_flag = 'Y';
SELECT source_id
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_id;
SELECT lead.status_code, sta.opp_open_status_flag
FROM as_statuses_b sta, as_sales_leads lead
WHERE lead.sales_lead_id = c_sales_lead_id
AND lead.status_code = sta.status_code
AND sta.lead_flag = 'Y';
SELECT creation_date, assign_to_salesforce_id, lead_rank_id
FROM as_sales_leads_log
WHERE sales_lead_id = c_sales_lead_id
ORDER BY log_id DESC;
SAVEPOINT LEAD_PROCESS_AFTER_UPDATE_PVT;
P_Lead_Action => 'UPDATE',
-- 9/9/03 SWKHANNA -- added for Lead Upgrade/Downgrade
P_Attribute_Changed => l_attribute_changed,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
customer_id, address_id, sales_lead_id, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, change_type, delete_flag, insert_flag,
processed_flag)
VALUES
(l_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE,
FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID, 'LEAD', 'N', 'Y',
'Y');
UPDATE AS_CHANGED_ACCOUNTS_ALL
SET processed_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id;
END Lead_Process_After_Update;