The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE NUMBER :=1;
PROCEDURE Update_sales_lead(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
:= AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_Rec IN SALES_LEAD_Rec_Type DEFAULT G_MISS_SALES_LEAD_REC,
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) := 'Update_sales_lead';
SAVEPOINT UPDATE_SALES_LEAD_PUB;
'PUB: Calling PVT.Update_sales_lead');
AS_SALES_LEADS_PVT.Update_sales_lead(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE, -- p_commit,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_Rec => P_SALES_LEAD_Rec,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Update_sales_lead;
PROCEDURE Update_sales_lead_lines(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_LINE_Tbl IN SALES_LEAD_LINE_Tbl_Type,
X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY SALES_LEAD_LINE_OUT_Tbl_Type,
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) := 'Update_sales_lead_lines';
SAVEPOINT UPDATE_SALES_LEAD_LINES_PUB;
'PUB: Calling PVT.Update_lines');
AS_SALES_LEAD_LINES_PVT.Update_sales_lead_lines(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE, -- p_commit,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_LINE_Tbl => P_SALES_LEAD_LINE_Tbl,
X_SALES_LEAD_LINE_OUT_Tbl => X_Sales_Lead_Line_Out_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Update_sales_lead_lines;
PROCEDURE Delete_sales_lead_lines(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_LINE_Tbl IN SALES_LEAD_LINE_Tbl_Type,
X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY SALES_LEAD_LINE_OUT_Tbl_Type,
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) := 'Delete_sales_lead_lines';
SAVEPOINT DELETE_SALES_LEAD_LINES_PUB;
'PUB: Calling PVT.Delete_lines');
AS_SALES_LEAD_LINES_PVT.Delete_sales_lead_lines(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE, -- p_commit,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_LINE_Tbl => P_SALES_LEAD_LINE_Tbl,
X_SALES_LEAD_LINE_OUT_Tbl => X_Sales_Lead_Line_Out_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Delete_sales_lead_lines;
PROCEDURE Update_sales_lead_contacts(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_CONTACT_Tbl IN SALES_LEAD_CONTACT_Tbl_Type,
X_SALES_LEAD_CNT_OUT_Tbl OUT NOCOPY SALES_LEAD_CNT_OUT_Tbl_Type,
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) := 'Update_sales_lead_contacts';
SAVEPOINT UPDATE_SALES_LEAD_CONTACTS_PUB;
'PUB: Calling PVT.Update_contacts');
AS_SALES_LEAD_CONTACTS_PVT.Update_sales_lead_contacts(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE, -- p_commit,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_CONTACT_Tbl => P_SALES_LEAD_CONTACT_Tbl,
x_SALES_LEAD_CNT_OUT_Tbl => X_SALES_LEAD_CNT_OUT_Tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Update_sales_lead_contacts;
PROCEDURE Delete_sales_lead_contacts(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_CONTACT_Tbl IN SALES_LEAD_CONTACT_Tbl_Type,
X_SALES_LEAD_CNT_OUT_Tbl OUT NOCOPY SALES_LEAD_CNT_OUT_Tbl_Type,
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) := 'Delete_sales_lead_contacts';
SAVEPOINT DELETE_SALES_LEAD_CONTACTS_PUB;
'PUB: Calling PVT.Delete_contacts');
AS_SALES_LEAD_CONTACTS_PVT.Delete_sales_lead_contacts(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE, -- p_commit,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_CONTACT_Tbl => P_SALES_LEAD_CONTACT_Tbl,
x_SALES_LEAD_CNT_OUT_Tbl => X_SALES_LEAD_CNT_OUT_Tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Delete_sales_lead_contacts;
SELECT sales_lead_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_number
,status_code
,customer_id
,address_id
,source_promotion_id
,initiating_contact_id
,orig_system_reference
,contact_role_code
,channel_code
,budget_amount
,currency_code
,decision_timeframe_code
,close_reason
,lead_rank_code
,parent_project
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,assign_to_person_id
,assign_to_salesforce_id
,budget_status_code
,assign_date
,accept_flag
,vehicle_response_code
,total_score
,scorecard_id
,keep_flag
,urgent_flag
,import_flag
,reject_reason_code
,lead_rank_id
,deleted_flag
,assign_sales_group_id
,offer_id
-- ,security_group_id
,incumbent_partner_party_id
,incumbent_partner_resource_id
,PRM_EXEC_SPONSOR_FLAG
,PRM_PRJ_LEAD_IN_PLACE_FLAG
,PRM_SALES_LEAD_TYPE
,PRM_IND_CLASSIFICATION_CODE
,QUALIFIED_FLAG
,ORIG_SYSTEM_CODE
,PRM_ASSIGNMENT_TYPE
,AUTO_ASSIGNMENT_TYPE
,PRIMARY_CONTACT_PARTY_ID
,PRIMARY_CNT_PERSON_PARTY_ID
,PRIMARY_CONTACT_PHONE_ID
,REFERRED_BY
,REFERRAL_TYPE
,REFERRAL_STATUS
,REF_DECLINE_REASON
,REF_COMM_LTR_STATUS
,REF_ORDER_NUMBER
,REF_ORDER_AMT
,REF_COMM_AMT
-- bug No.2341515, 2368075
,LEAD_DATE
,SOURCE_SYSTEM
,COUNTRY
-- 11.5.9
, TOTAL_AMOUNT
,EXPIRATION_DATE
,LEAD_RANK_IND
,LEAD_ENGINE_RUN_DATE
,CURRENT_REROUTES
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
,l_Sales_Lead_Rec.last_update_date
,l_Sales_Lead_Rec.last_updated_by
,l_Sales_Lead_Rec.creation_date
,l_Sales_Lead_Rec.created_by
,l_Sales_Lead_Rec.last_update_login
,l_Sales_Lead_Rec.request_id
,l_Sales_Lead_Rec.program_application_id
,l_Sales_Lead_Rec.program_id
,l_Sales_Lead_Rec.program_update_date
,l_Sales_Lead_Rec.lead_number
,l_Sales_Lead_Rec.status_code
,l_Sales_Lead_Rec.customer_id
,l_Sales_Lead_Rec.address_id
,l_Sales_Lead_Rec.source_promotion_id
,l_Sales_Lead_Rec.initiating_contact_id
,l_Sales_Lead_Rec.orig_system_reference
,l_Sales_Lead_Rec.contact_role_code
,l_Sales_Lead_Rec.channel_code
,l_Sales_Lead_Rec.budget_amount
,l_Sales_Lead_Rec.currency_code
,l_Sales_Lead_Rec.decision_timeframe_code
,l_Sales_Lead_Rec.close_reason
,l_Sales_Lead_Rec.lead_rank_code
,l_Sales_Lead_Rec.parent_project
,l_Sales_Lead_Rec.description
,l_Sales_Lead_Rec.attribute_category
,l_Sales_Lead_Rec.attribute1
,l_Sales_Lead_Rec.attribute2
,l_Sales_Lead_Rec.attribute3
,l_Sales_Lead_Rec.attribute4
,l_Sales_Lead_Rec.attribute5
,l_Sales_Lead_Rec.attribute6
,l_Sales_Lead_Rec.attribute7
,l_Sales_Lead_Rec.attribute8
,l_Sales_Lead_Rec.attribute9
,l_Sales_Lead_Rec.attribute10
,l_Sales_Lead_Rec.attribute11
,l_Sales_Lead_Rec.attribute12
,l_Sales_Lead_Rec.attribute13
,l_Sales_Lead_Rec.attribute14
,l_Sales_Lead_Rec.attribute15
,l_Sales_Lead_Rec.assign_to_person_id
,l_Sales_Lead_Rec.assign_to_salesforce_id
,l_Sales_Lead_Rec.budget_status_code
,l_Sales_Lead_Rec.assign_date
,l_Sales_Lead_Rec.accept_flag
,l_Sales_Lead_Rec.vehicle_response_code
,l_Sales_Lead_Rec.total_score
,l_Sales_Lead_Rec.scorecard_id
,l_Sales_Lead_Rec.keep_flag
,l_Sales_Lead_Rec.urgent_flag
,l_Sales_Lead_Rec.import_flag
,l_Sales_Lead_Rec.reject_reason_code
,l_Sales_Lead_Rec.lead_rank_id
,l_Sales_Lead_Rec.deleted_flag
,l_Sales_Lead_Rec.assign_sales_group_id
,l_Sales_Lead_Rec.offer_id
-- ,l_Sales_Lead_Rec.security_group_id
,l_Sales_Lead_Rec.incumbent_partner_party_id
,l_Sales_Lead_Rec.incumbent_partner_resource_id
,l_Sales_Lead_Rec.PRM_EXEC_SPONSOR_FLAG
,l_Sales_Lead_Rec.PRM_PRJ_LEAD_IN_PLACE_FLAG
,l_Sales_Lead_Rec.PRM_SALES_LEAD_TYPE
,l_Sales_Lead_Rec.PRM_IND_CLASSIFICATION_CODE
,l_Sales_Lead_Rec.QUALIFIED_FLAG
,l_Sales_Lead_Rec.ORIG_SYSTEM_CODE
,l_Sales_Lead_Rec.PRM_ASSIGNMENT_TYPE
,l_Sales_Lead_Rec.AUTO_ASSIGNMENT_TYPE
,l_Sales_Lead_Rec.PRIMARY_CONTACT_PARTY_ID
,l_Sales_Lead_Rec.PRIMARY_CNT_PERSON_PARTY_ID
,l_Sales_Lead_Rec.PRIMARY_CONTACT_PHONE_ID
,l_Sales_Lead_Rec.REFERRED_BY
,l_Sales_Lead_Rec.REFERRAL_TYPE
,l_Sales_Lead_Rec.REFERRAL_STATUS
,l_Sales_Lead_Rec.REF_DECLINE_REASON
,l_Sales_Lead_Rec.REF_COMM_LTR_STATUS
,l_Sales_Lead_Rec.REF_ORDER_NUMBER
,l_Sales_Lead_Rec.REF_ORDER_AMT
,l_Sales_Lead_Rec.REF_COMM_AMT
-- bug No.2341515, 2368075
,l_Sales_Lead_Rec.LEAD_DATE
,l_Sales_Lead_Rec.SOURCE_SYSTEM
,l_Sales_Lead_Rec.COUNTRY
-- 11.5.9
, l_Sales_Lead_Rec.TOTAL_AMOUNT
,l_Sales_Lead_Rec.EXPIRATION_DATE
,l_Sales_Lead_Rec.LEAD_RANK_IND
,l_Sales_Lead_Rec.LEAD_ENGINE_RUN_DATE
,l_Sales_Lead_Rec.CURRENT_REROUTES
;
AS_SALES_LEAD_REFERRAL.Update_sales_referral_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_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_Rec => l_sales_lead_rec,
p_overriding_usernames => AS_SALES_LEAD_REFERRAL.G_MISS_OVER_USERNAMES_TBL,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
SELECT attribute_id
,attr_value
,party_id
,score
FROM pv_enty_attr_values PVAV
WHERE entity_id = X_Sales_Lead_Id
AND entity = 'SALES_LEAD'
AND enabled_flag = 'Y'
AND attribute_id in (Select ATTRIBUTE_ID
FROM PV_ENTITY_ATTRS pva
where entity='LEAD'
and pva.ATTRIBUTE_ID = pvav.attribute_id
and enabled_flag = 'Y'
);
SELECT uom_code
, quantity
, amount
, attr_code_id
, partner_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM PV_LEAD_PSS_LINES
WHERE object_id = X_Sales_Lead_Id
AND object_name = 'SALES_LEAD';
SELECT sales_lead_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_number
,status_code
,customer_id
,address_id
,source_promotion_id
,initiating_contact_id
,orig_system_reference
,contact_role_code
,channel_code
,budget_amount
,currency_code
,decision_timeframe_code
,close_reason
,lead_rank_code
,parent_project
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,assign_to_person_id
,assign_to_salesforce_id
,budget_status_code
,assign_date
,accept_flag
,vehicle_response_code
,total_score
,scorecard_id
,keep_flag
,urgent_flag
,import_flag
,reject_reason_code
,lead_rank_id
,deleted_flag
,assign_sales_group_id
,offer_id
-- ,security_group_id
,incumbent_partner_party_id
,incumbent_partner_resource_id
,PRM_EXEC_SPONSOR_FLAG
,PRM_PRJ_LEAD_IN_PLACE_FLAG
,PRM_SALES_LEAD_TYPE
,PRM_IND_CLASSIFICATION_CODE
,QUALIFIED_FLAG
,ORIG_SYSTEM_CODE
,PRM_ASSIGNMENT_TYPE
,AUTO_ASSIGNMENT_TYPE
,PRIMARY_CONTACT_PARTY_ID
,PRIMARY_CNT_PERSON_PARTY_ID
,PRIMARY_CONTACT_PHONE_ID
,REFERRED_BY
,REFERRAL_TYPE
,REFERRAL_STATUS
,REF_DECLINE_REASON
,REF_COMM_LTR_STATUS
,REF_ORDER_NUMBER
,REF_ORDER_AMT
,REF_COMM_AMT
-- bug No.2341515, 2368075
,LEAD_DATE
,SOURCE_SYSTEM
,COUNTRY
-- 11.5.9
, TOTAL_AMOUNT
,EXPIRATION_DATE
,LEAD_RANK_IND
,LEAD_ENGINE_RUN_DATE
,CURRENT_REROUTES
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
,l_Sales_Lead_Rec.last_update_date
,l_Sales_Lead_Rec.last_updated_by
,l_Sales_Lead_Rec.creation_date
,l_Sales_Lead_Rec.created_by
,l_Sales_Lead_Rec.last_update_login
,l_Sales_Lead_Rec.request_id
,l_Sales_Lead_Rec.program_application_id
,l_Sales_Lead_Rec.program_id
,l_Sales_Lead_Rec.program_update_date
,l_Sales_Lead_Rec.lead_number
,l_Sales_Lead_Rec.status_code
,l_Sales_Lead_Rec.customer_id
,l_Sales_Lead_Rec.address_id
,l_Sales_Lead_Rec.source_promotion_id
,l_Sales_Lead_Rec.initiating_contact_id
,l_Sales_Lead_Rec.orig_system_reference
,l_Sales_Lead_Rec.contact_role_code
,l_Sales_Lead_Rec.channel_code
,l_Sales_Lead_Rec.budget_amount
,l_Sales_Lead_Rec.currency_code
,l_Sales_Lead_Rec.decision_timeframe_code
,l_Sales_Lead_Rec.close_reason
,l_Sales_Lead_Rec.lead_rank_code
,l_Sales_Lead_Rec.parent_project
,l_Sales_Lead_Rec.description
,l_Sales_Lead_Rec.attribute_category
,l_Sales_Lead_Rec.attribute1
,l_Sales_Lead_Rec.attribute2
,l_Sales_Lead_Rec.attribute3
,l_Sales_Lead_Rec.attribute4
,l_Sales_Lead_Rec.attribute5
,l_Sales_Lead_Rec.attribute6
,l_Sales_Lead_Rec.attribute7
,l_Sales_Lead_Rec.attribute8
,l_Sales_Lead_Rec.attribute9
,l_Sales_Lead_Rec.attribute10
,l_Sales_Lead_Rec.attribute11
,l_Sales_Lead_Rec.attribute12
,l_Sales_Lead_Rec.attribute13
,l_Sales_Lead_Rec.attribute14
,l_Sales_Lead_Rec.attribute15
,l_Sales_Lead_Rec.assign_to_person_id
,l_Sales_Lead_Rec.assign_to_salesforce_id
,l_Sales_Lead_Rec.budget_status_code
,l_Sales_Lead_Rec.assign_date
,l_Sales_Lead_Rec.accept_flag
,l_Sales_Lead_Rec.vehicle_response_code
,l_Sales_Lead_Rec.total_score
,l_Sales_Lead_Rec.scorecard_id
,l_Sales_Lead_Rec.keep_flag
,l_Sales_Lead_Rec.urgent_flag
,l_Sales_Lead_Rec.import_flag
,l_Sales_Lead_Rec.reject_reason_code
,l_Sales_Lead_Rec.lead_rank_id
,l_Sales_Lead_Rec.deleted_flag
,l_Sales_Lead_Rec.assign_sales_group_id
,l_Sales_Lead_Rec.offer_id
-- ,l_Sales_Lead_Rec.security_group_id
,l_Sales_Lead_Rec.incumbent_partner_party_id
,l_Sales_Lead_Rec.incumbent_partner_resource_id
,l_Sales_Lead_Rec.PRM_EXEC_SPONSOR_FLAG
,l_Sales_Lead_Rec.PRM_PRJ_LEAD_IN_PLACE_FLAG
,l_Sales_Lead_Rec.PRM_SALES_LEAD_TYPE
,l_Sales_Lead_Rec.PRM_IND_CLASSIFICATION_CODE
,l_Sales_Lead_Rec.QUALIFIED_FLAG
,l_Sales_Lead_Rec.ORIG_SYSTEM_CODE
,l_Sales_Lead_Rec.PRM_ASSIGNMENT_TYPE
,l_Sales_Lead_Rec.AUTO_ASSIGNMENT_TYPE
,l_Sales_Lead_Rec.PRIMARY_CONTACT_PARTY_ID
,l_Sales_Lead_Rec.PRIMARY_CNT_PERSON_PARTY_ID
,l_Sales_Lead_Rec.PRIMARY_CONTACT_PHONE_ID
,l_Sales_Lead_Rec.REFERRED_BY
,l_Sales_Lead_Rec.REFERRAL_TYPE
,l_Sales_Lead_Rec.REFERRAL_STATUS
,l_Sales_Lead_Rec.REF_DECLINE_REASON
,l_Sales_Lead_Rec.REF_COMM_LTR_STATUS
,l_Sales_Lead_Rec.REF_ORDER_NUMBER
,l_Sales_Lead_Rec.REF_ORDER_AMT
,l_Sales_Lead_Rec.REF_COMM_AMT
-- bug No.2341515, 2368075
,l_Sales_Lead_Rec.LEAD_DATE
,l_Sales_Lead_Rec.SOURCE_SYSTEM
,l_Sales_Lead_Rec.COUNTRY
-- 11.5.9
,l_Sales_Lead_Rec.TOTAL_AMOUNT
,l_Sales_Lead_Rec.EXPIRATION_DATE
,l_Sales_Lead_Rec.LEAD_RANK_IND
,l_Sales_Lead_Rec.LEAD_ENGINE_RUN_DATE
,l_Sales_Lead_Rec.CURRENT_REROUTES
;
AS_SALES_LEAD_REFERRAL.Update_sales_referral_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_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_Rec => l_sales_lead_rec,
p_overriding_usernames => AS_SALES_LEAD_REFERRAL.G_MISS_OVER_USERNAMES_TBL,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
P_auto_select_flag => NULL,
P_effort_duration => NULL,
P_effort_uom => NULL,
P_start_date => NULL,
p_end_date => NULL,
P_territory_flag => 'Y',
P_calendar_flag => 'Y',
P_Sales_Lead_Id => P_Sales_Lead_Id,
X_Return_Status => X_Msg_Count,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Assign_Id_Tbl => X_Assign_Id_Tbl
);
IF p_profile_tbl(l_curr).PROFILE_NAME = 'AS_MGR_UPDATE' THEN
x_access_profile_rec.mgr_update_profile_value := l_profile_value;
IF p_profile_tbl(l_curr).PROFILE_NAME = 'AS_ADMIN_UPDATE' THEN
x_access_profile_rec.admin_update_profile_value := l_profile_value;
IF x_access_profile_rec.mgr_update_profile_value IS NULL OR
x_access_profile_rec.mgr_update_profile_value = FND_API.G_MISS_CHAR
THEN
x_access_profile_rec.mgr_update_profile_value
:= FND_PROFILE.Value('AS_MGR_UPDATE');
IF x_access_profile_rec.admin_update_profile_value IS NULL OR
x_access_profile_rec.admin_update_profile_value = FND_API.G_MISS_CHAR
THEN
x_access_profile_rec.admin_update_profile_value
:= FND_PROFILE.Value('AS_ADMIN_UPDATE');
l_last_update_date DATE;
SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
WHERE ACC.FREEZE_FLAG = 'N'
AND ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.ACCESS_ID = TERRACC.ACCESS_ID;
SELECT asl.ASSIGN_TO_SALESFORCE_ID
FROM AS_SALES_LEADS asl
WHERE asl.sales_lead_id = c_sales_lead_id;
SELECT js.source_id
FROM JTF_RS_RESOURCE_EXTNS js
WHERE js.resource_id = c_resource_id;
SELECT asl.customer_id, asl.address_id, asl.status_code,
asl.reject_reason_code, asl.assign_to_salesforce_id,
asl.qualified_flag, asl.lead_rank_id
FROM AS_SALES_LEADS asl
WHERE asl.sales_lead_id = c_sales_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 = 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) --'SALES','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 'Y'
FROM as_accesses_all acc
WHERE acc.sales_lead_id = c_sales_lead_id
AND acc.owner_flag = 'Y';
SELECT acc.access_id
FROM as_accesses_all acc
WHERE acc.salesforce_id = c_resource_id
AND acc.sales_lead_id = c_sales_lead_id;
'Delete acc_id:' || l_ta_access_id_tbl(l_i)
|| ' terr_id:' || l_ta_terr_id_tbl(l_i));
DELETE FROM AS_ACCESSES_ALL
WHERE ACCESS_ID = l_ta_access_id_tbl(l_i);
DELETE FROM AS_TERRITORY_ACCESSES
WHERE ACCESS_ID = l_ta_access_id_tbl(l_i)
AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
DELETE FROM as_accesses_all acc
WHERE acc.sales_lead_id = p_sales_lead_id
AND acc.freeze_flag = 'N'
--AND acc.salesforce_id <> p_identity_salesforce_id
AND NOT EXISTS (
SELECT 1
FROM as_territory_accesses terracc
WHERE terracc.access_id = acc.access_id);
UPDATE as_accesses_all
SET address_id = l_address_id
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_accesses_all
SET owner_flag = 'Y',
sales_group_id = l_group_id
WHERE access_id = l_access_id;
'Before calling Update Saleslead');
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_person_id,
p_assign_to_salesforce_id => l_salesforce_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
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_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_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 => l_person_id,
p_ASSIGN_TO_SALESFORCE_ID => l_salesforce_id,
p_ASSIGN_SALES_GROUP_ID => l_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
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 => NULL, --l_reject_reason_code,
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_SECURITY_GROUP_ID => FND_API.G_MISS_NUM,
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,
-- 11.5.9
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 => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
-- new columns for appsperf CRMAP denorm project bug 2928041
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
);
PROCEDURE Lead_Process_After_Update (
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
p_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Salesgroup_id IN NUMBER := FND_API.G_MISS_NUM,
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';
SAVEPOINT LEAD_PROCESS_AFTER_UPDATE_PUB;
'PUB: Call PVT.Lead_Process_After_Update');
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 => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
p_Admin_Flag => p_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Salesgroup_Id => P_Salesgroup_Id,
P_Sales_Lead_Id => P_Sales_Lead_Id,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
End Lead_Process_After_Update;