[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (px_SALES_LEAD_rec.DELETED_FLAG IS NULL) OR
(px_SALES_LEAD_rec.DELETED_FLAG = FND_API.G_MISS_CHAR
AND p_mode = AS_UTILITY_PVT.G_CREATE)
THEN
px_SALES_LEAD_rec.DELETED_FLAG := 'N';
ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE) THEN
IF p_LEAD_NUMBER IS NULL THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SELECT 'X'
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
THEN
-- validate NOT NULL column
IF (p_sales_lead_id is NULL) or (p_sales_lead_id = FND_API.G_MISS_NUM)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_LEAD_ID');
select 'X'
from as_sales_methodology_b asmb
--, as_sales_methodology_tl asmt
where trunc(nvl(asmb.start_date_active, sysdate)) <= trunc(sysdate)
and trunc(nvl(asmb.end_date_active, sysdate)) >= trunc(sysdate)
--and asmb.sales_methodology_id = asmt.sales_methodology_id
--and asmt.language = userenv('LANG')
and asmb.sales_methodology_id = X_Sales_Methodology_ID;
SELECT 'X'
FROM AS_SALES_METH_STAGE_MAP asms
, AS_SALES_STAGES_ALL_B assa
WHERE asms.sales_stage_id = assa.sales_stage_id
AND TRUNC(NVL(assa.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(assa.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND sales_methodology_id = X_Sales_Methodology_ID
AND assa.applicability IN ('LEAD', 'BOTH')
-- ckapoor 05/11/04 bug 3621389 -
-- adding new additional condition as per AUYU
AND assa.enabled_flag = 'Y'
-- end ckapoor
AND asms.sales_stage_id = X_Sales_Stage_ID
;
SELECT 'X'
FROM HZ_PARTIES
WHERE PARTY_TYPE IN ('PERSON', 'ORGANIZATION')
AND PARTY_ID = c_Customer_Id;
SELECT status
FROM hz_parties
WHERE PARTY_TYPE IN ('PERSON', 'ORGANIZATION')
AND PARTY_ID = c_Customer_Id;
(p_CUSTOMER_ID is NULL AND p_validation_mode=AS_UTILITY_PVT.G_UPDATE)
THEN
IF (AS_DEBUG_LOW_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'Private API: Violate NOT NULL constraint(CUSTOMER_ID)');
-- do the customer is inactive check only at time of create. allow updates.
IF (p_validation_mode = AS_UTILITY_PVT.G_CREATE) THEN
OPEN C_Customer_is_active (p_Customer_Id);
p_validation_mode = AS_UTILITY_PVT.G_UPDATE
THEN
NULL;
SELECT 'X'
FROM as_statuses_b
WHERE lead_flag = 'Y' and enabled_flag = 'Y'
and status_code = X_Lookup_Code;
SELECT 'X'
FROM as_sales_lead_opportunity
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT status_code
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
(p_status_code IS NULL AND p_validation_mode=AS_UTILITY_PVT.G_UPDATE)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_ID',
p_token1 => 'COLUMN',
p_token1_value => 'STATUS_CODE' );
if p_status_code <> FND_API.G_MISS_CHAR and l_status_code <> p_status_code and p_validation_mode = AS_UTILITY_PVT.G_update and l_newStateTransition = 'Y' and l_linkStatus = l_status_code
then
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'AS_INVALID_STATUS_TRANSITION'
);
elsif (p_validation_mode=AS_UTILITY_PVT.G_UPDATE) then
if (p_status_code = l_linkStatus) then
OPEN C_opp_exists (p_sales_lead_id);
p_msg_name => 'AS_NO_LEAD_UPDATE_NO_OPP'
);
SELECT 'X'
FROM ams_p_source_codes_v
WHERE source_code_id = X_promotion_id
and source_type in ('CAMP','CSCH','EONE', 'EVEH','EVEO')
and status in ('ACTIVE','ONHOLD', 'COMPLETED');
AND p_validation_mode=AS_UTILITY_PVT.G_UPDATE)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_ID',
p_token1 => 'COLUMN',
p_token1_value => 'SOURCE NAME');
and (p_validation_mode <> AS_UTILITY_PVT.G_UPDATE) -- added by bmuthukr for fixing bug 3817333. val not reqd during updates.
--IF ((p_source_promotion_id is NOT NULL AND
-- p_source_promotion_id <> FND_API.G_MISS_NUM))
THEN
OPEN C_Promotion_Exists ( p_source_promotion_id);
SELECT 'X'
FROM aso_i_sales_channels_v
WHERE sales_channel_code = X_Lookup_Code
-- ffang 012501, add more criteria
and nvl(start_date_active, sysdate) <= sysdate
and nvl(end_date_active, sysdate) >= sysdate
and enabled_flag = 'Y';
SELECT 'X'
FROM FND_LOOKUP_VALUES
WHERE lookup_code = X_Currency_Code
and LOOKUP_TYPE = 'REPORTING_CURRENCY'
and VIEW_APPLICATION_ID = 279
-- ffang 012501
and nvl(start_date_active, sysdate) <= sysdate
and nvl(end_date_active, sysdate) >= sysdate
and enabled_flag = 'Y';
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and enabled_flag = 'Y';
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and enabled_flag = 'Y';
SELECT 'X'
FROM as_sales_lead_ranks_b
WHERE rank_id = X_Rank_id
and enabled_flag = 'Y';
SELECT 'X'
FROM per_all_people_f per,
jtf_rs_resource_extns res
WHERE per.person_id = X_person_id
AND TRUNC(SYSDATE) BETWEEN per.effective_start_date
AND per.effective_end_date
AND res.category = 'EMPLOYEE'
AND res.source_id = per.person_id;
SELECT 'X'
FROM per_all_people_f per,
jtf_rs_resource_extns res,
jtf_rs_role_relations rrel,
jtf_rs_roles_b role
WHERE TRUNC(SYSDATE) BETWEEN per.effective_start_date
AND per.effective_end_date
AND res.resource_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_INDIVIDUAL'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
--changing for bug 2877597 ckapoor adding nvl since the role table contains
-- Y/N and null
AND nvl(role.admin_flag, 'N') = 'N'
AND res.source_id = per.person_id
AND res.resource_id = X_Assign_Id
-- ffang 012501
AND res.category = 'EMPLOYEE';
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and enabled_flag = 'Y';
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and enabled_flag = 'Y';
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and enabled_flag = 'Y';
SELECT opp_open_status_flag
FROM as_statuses_b
WHERE lead_flag = 'Y' and enabled_flag = 'Y'
and status_code = X_Lookup_Code;
ELSIF p_validation_mode = AS_UTILITY_PVT.G_UPDATE
THEN
IF (P_REF_TYPE_CODE IS NULL and P_REF_BY_ID IS NOT NULL) or
(P_REF_TYPE_CODE IS NOT NULL and P_REF_BY_ID IS NULL) or
(P_REF_TYPE_CODE = FND_API.G_MISS_CHAR and P_REF_BY_ID IS NOT NULL and P_OLD_REF_TYPE_CODE IS NULL) or
(P_REF_BY_ID = FND_API.G_MISS_NUM and P_REF_TYPE_CODE IS NOT NULL and P_OLD_REF_BY_ID IS NULL) or
(P_REF_BY_ID IS NULL and P_REF_TYPE_CODE = FND_API.G_MISS_CHAR and P_OLD_REF_TYPE_CODE IS NOT NULL) or
(P_REF_BY_ID = FND_API.G_MISS_NUM and P_REF_TYPE_CODE IS NULL and P_OLD_REF_BY_ID IS NOT NULL)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'AS_REF_TYPE_REF_BY');
SELECT 'X'
FROM ams_source_codes
WHERE source_code_id = c_offer_id
and ARC_SOURCE_CODE_FOR = 'OFFR';
SELECT 'X'
FROM as_sf_ptr_v
WHERE partner_customer_id = c_inc_parn_party_id;
SELECT 'X'
FROM as_sf_ptr_v
WHERE SALESFORCE_ID = c_INC_PARTNER_RESOURCE_ID;
SELECT 'X'
FROM as_lookups
WHERE lookup_type = 'PRM_LEAD_TYPE'
and lookup_code = c_Lookup_Code;
SELECT 'X'
FROM as_lookups
WHERE lookup_type = 'PRM_IND_CLASSIFICATION_TYPE'
and lookup_code = c_Lookup_Code;
SELECT 'X'
FROM as_lookups
WHERE lookup_type = c_lookup_type
and lookup_code = c_Lookup_Code;
SELECT 'X'
FROM as_lookups
WHERE lookup_type = c_lookup_type
and lookup_code = c_Lookup_Code;
SELECT budget_amount
FROM as_sales_leads
where sales_lead_id = X_Sales_Lead_ID;
SELECT sum (budget_amount)
FROM as_sales_lead_lines
where sales_lead_id = X_Sales_Lead_ID;
SELECT sum (budget_amount)
FROM as_sales_lead_lines
where sales_lead_id = X_Sales_Lead_ID;
l_deleted_flag VARCHAR2(1);
select LOC.COUNTRY
FROM HZ_PARTY_SITES SITE,HZ_LOCATIONS LOC
WHERE SITE.PARTY_ID = X_CUSTOMER_ID
AND SITE.PARTY_SITE_ID = X_ADDRESS_ID
AND SITE.STATUS IN ('A','I')
AND SITE.LOCATION_ID = LOC.LOCATION_ID;
SELECT timeframe_days
FROM aml_sales_lead_timeframes
where decision_timeframe_code = X_DECISION_TIMEFRAME_CODE
and enabled_flag='Y';
SELECT opp_open_status_flag
FROM as_statuses_b
WHERE lead_flag = 'Y' and enabled_flag = 'Y'
and status_code = X_Lookup_Code;
SELECT min_score
FROM as_sales_lead_ranks_b
WHERE rank_id = X_Rank_ID;
SELECT hzp.party_name
FROM hz_parties hzp, as_sales_leads sl
WHERE hzp.party_id = sl.primary_cnt_person_party_id
and sl.sales_lead_id = X_sales_lead_id;
SELECT party_name
FROM hz_parties
WHERE party_id = X_party_id;
select party_site_id
from hz_party_sites
where party_id = p_customer_id
and IDENTIFYING_ADDRESS_FLAG = 'Y';
SELECT sales_stage_id
FROM (
SELECT asms.sales_stage_id
FROM AS_SALES_METH_STAGE_MAP asms
, AS_SALES_STAGES_ALL_B assa
WHERE asms.sales_stage_id = assa.sales_stage_id
AND TRUNC(NVL(assa.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(assa.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND sales_methodology_id = X_Sales_Meth_ID
AND assa.applicability IN ('LEAD', 'BOTH')
-- ckapoor 05/11/04 bug 3621389 -
-- adding new additional condition as per AUYU
AND assa.enabled_flag = 'Y'
-- end ckapoor
ORDER BY asms.stage_sequence
)
WHERE ROWNUM = 1 ;
(l_SALES_LEAD_rec.DELETED_FLAG IS NULL) or
(l_SALES_LEAD_rec.DELETED_FLAG = FND_API.G_MISS_CHAR) or
(l_SALES_LEAD_rec.currency_code IS NULL) or
(l_SALES_LEAD_rec.currency_code = FND_API.G_MISS_CHAR)
THEN
IF (AS_DEBUG_LOW_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'Calling Set_default_values');
'Calling Sales_Lead_Insert_Row');
AS_SALES_LEADS_PKG.Sales_Lead_Insert_Row(
px_SALES_LEAD_ID => x_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_LEAD_NUMBER => x_SALES_LEAD_ID,
--p_STATUS_CODE => l_qualified,
p_STATUS_CODE => l_SALES_LEAD_rec.STATUS_CODE,
p_CUSTOMER_ID => l_SALES_LEAD_rec.CUSTOMER_ID,
p_ADDRESS_ID => l_SALES_LEAD_rec.ADDRESS_ID,
p_SOURCE_PROMOTION_ID => l_SALES_LEAD_rec.SOURCE_PROMOTION_ID,
p_INITIATING_CONTACT_ID => l_SALES_LEAD_rec.INITIATING_CONTACT_ID,
p_ORIG_SYSTEM_REFERENCE => l_SALES_LEAD_rec.ORIG_SYSTEM_REFERENCE,
p_CONTACT_ROLE_CODE => l_SALES_LEAD_rec.CONTACT_ROLE_CODE,
p_CHANNEL_CODE => l_SALES_LEAD_rec.CHANNEL_CODE,
p_BUDGET_AMOUNT => l_SALES_LEAD_rec.BUDGET_AMOUNT,
p_CURRENCY_CODE => l_SALES_LEAD_rec.CURRENCY_CODE,
p_DECISION_TIMEFRAME_CODE => l_SALES_LEAD_rec.DECISION_TIMEFRAME_CODE,
p_CLOSE_REASON => l_SALES_LEAD_rec.CLOSE_REASON,
p_LEAD_RANK_ID => l_SALES_LEAD_rec.LEAD_RANK_ID,
p_LEAD_RANK_CODE => l_SALES_LEAD_rec.LEAD_RANK_CODE,
p_PARENT_PROJECT => l_SALES_LEAD_rec.PARENT_PROJECT,
p_DESCRIPTION => l_SALES_LEAD_rec.DESCRIPTION,
p_ATTRIBUTE_CATEGORY => l_SALES_LEAD_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_SALES_LEAD_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_SALES_LEAD_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_SALES_LEAD_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_SALES_LEAD_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_SALES_LEAD_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_SALES_LEAD_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_SALES_LEAD_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_SALES_LEAD_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_SALES_LEAD_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_SALES_LEAD_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_SALES_LEAD_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_SALES_LEAD_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_SALES_LEAD_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_SALES_LEAD_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_SALES_LEAD_rec.ATTRIBUTE15,
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_ASSIGN_SALES_GROUP_ID => l_SALES_LEAD_rec.ASSIGN_SALES_GROUP_ID,
--p_ASSIGN_DATE => l_SALES_LEAD_rec.ASSIGN_DATE,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => l_SALES_LEAD_rec.BUDGET_STATUS_CODE,
p_ACCEPT_FLAG => l_SALES_LEAD_rec.ACCEPT_FLAG,
p_VEHICLE_RESPONSE_CODE => l_SALES_LEAD_rec.VEHICLE_RESPONSE_CODE,
p_TOTAL_SCORE => l_SALES_LEAD_rec.TOTAL_SCORE,
p_SCORECARD_ID => l_SALES_LEAD_rec.SCORECARD_ID,
p_KEEP_FLAG => l_SALES_LEAD_rec.KEEP_FLAG,
p_URGENT_FLAG => l_SALES_LEAD_rec.URGENT_FLAG,
p_IMPORT_FLAG => l_SALES_LEAD_rec.IMPORT_FLAG,
p_REJECT_REASON_CODE => l_SALES_LEAD_rec.REJECT_REASON_CODE,
p_DELETED_FLAG => l_SALES_LEAD_rec.DELETED_FLAG,
p_OFFER_ID => l_SALES_LEAD_rec.OFFER_ID,
--p_QUALIFIED_FLAG => l_SALES_LEAD_rec.QUALIFIED_FLAG,
p_QUALIFIED_FLAG => l_qualified,
p_ORIG_SYSTEM_CODE => l_SALES_LEAD_rec.ORIG_SYSTEM_CODE,
-- p_SECURITY_GROUP_ID => l_SALES_LEAD_rec.SECURITY_GROUP_ID,
p_INC_PARTNER_PARTY_ID => l_SALES_LEAD_rec.INCUMBENT_PARTNER_PARTY_ID,
p_INC_PARTNER_RESOURCE_ID =>
l_SALES_LEAD_rec.INCUMBENT_PARTNER_RESOURCE_ID,
p_PRM_EXEC_SPONSOR_FLAG => l_SALES_LEAD_rec.PRM_EXEC_SPONSOR_FLAG,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG =>
l_SALES_LEAD_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG,
p_PRM_SALES_LEAD_TYPE => l_SALES_LEAD_rec.PRM_SALES_LEAD_TYPE,
p_PRM_IND_CLASSIFICATION_CODE =>
l_SALES_LEAD_rec.PRM_IND_CLASSIFICATION_CODE,
p_PRM_ASSIGNMENT_TYPE => l_SALES_LEAD_rec.PRM_ASSIGNMENT_TYPE,
p_AUTO_ASSIGNMENT_TYPE => l_SALEs_LEAD_rec.AUTO_ASSIGNMENT_TYPE,
p_PRIMARY_CONTACT_PARTY_ID => l_SALES_LEAD_rec.PRIMARY_CONTACT_PARTY_ID,
p_PRIMARY_CNT_PERSON_PARTY_ID => l_SALES_LEAD_rec.PRIMARY_CNT_PERSON_PARTY_ID,
p_PRIMARY_CONTACT_PHONE_ID => l_SALES_LEAD_rec.PRIMARY_CONTACT_PHONE_ID,
-- new columns for CAPRI lead referral
p_REFERRED_BY => l_SALES_LEAD_rec.REFERRED_BY,
p_REFERRAL_TYPE => l_SALES_LEAD_rec.REFERRAL_TYPE,
p_REFERRAL_STATUS => l_referral_status_profile,
p_REF_DECLINE_REASON => l_SALES_LEAD_rec.REF_DECLINE_REASON,
p_REF_COMM_LTR_STATUS => l_SALES_LEAD_rec.REF_COMM_LTR_STATUS,
p_REF_ORDER_NUMBER => l_SALES_LEAD_rec.REF_ORDER_NUMBER,
p_REF_ORDER_AMT => l_SALES_LEAD_rec.REF_ORDER_AMT,
p_REF_COMM_AMT => l_SALES_LEAD_rec.REF_COMM_AMT,
p_LEAD_DATE => l_SALES_LEAD_rec.LEAD_DATE,
p_SOURCE_SYSTEM => l_SALES_LEAD_rec.SOURCE_SYSTEM,
-- Bug 3385646 - MKTU3R10:COUNTRY IS NOT POPULATED WHEN THE LEAD IS CREATED THROUGH HTML
p_COUNTRY => l_country_code, --l_SALES_LEAD_rec.COUNTRY,
p_TOTAL_AMOUNT => l_SALES_LEAD_rec.TOTAL_AMOUNT,
p_EXPIRATION_DATE => l_exp_date, --l_SALES_LEAD_rec.EXPIRATION_DATE,
p_LEAD_RANK_IND => l_SALES_LEAD_rec.LEAD_RANK_IND,
p_LEAD_ENGINE_RUN_DATE => l_SALES_LEAD_rec.LEAD_ENGINE_RUN_DATE,
p_CURRENT_REROUTES => l_SALES_LEAD_rec.CURRENT_REROUTES
-- new columns for appsperf CRMAP denorm project bug 2928041
,p_STATUS_OPEN_FLAG => l_status_open_flag
--FND_API.G_MISS_CHAR
,p_LEAD_RANK_SCORE => l_lead_rank_score
--FND_API.G_MISS_NUM
-- ckapoor 11.5.10 New columns
, p_MARKETING_SCORE => l_SALES_LEAD_rec.MARKETING_SCORE
, p_INTERACTION_SCORE => l_SALES_LEAD_rec.INTERACTION_SCORE
, p_SOURCE_PRIMARY_REFERENCE => l_SALES_LEAD_rec.SOURCE_PRIMARY_REFERENCE
, p_SOURCE_SECONDARY_REFERENCE => l_SALES_LEAD_rec.SOURCE_SECONDARY_REFERENCE
, p_SALES_METHODOLOGY_ID => l_SALES_LEAD_rec.SALES_METHODOLOGY_ID
, p_SALES_STAGE_ID => l_default_sales_stage
--l_SALES_LEAD_rec.SALES_STAGE_ID
);
'Calling AS_SALES_LEADS_LOG_PKG.Insert_Row');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => l_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,
-- using standard parameters for program who columns
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_qualified,
p_category => fnd_api.g_miss_char,
p_manual_rank_flag => l_manual_rank_flag
);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => l_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,
-- using standard parameters for program who columns
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_referral_status_profile, -- for referral log, we use referral status
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_qualified,
p_category => 'REFERRAL'
);
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;
-- has_updateLeadAccess
AS_SALES_LEAD_LINES_PVT.Create_sales_lead_lines(
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 => 'N',
-- 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,
P_SALES_LEAD_ID => x_SALES_LEAD_ID,
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
);
-- has_updateLeadAccess
AS_SALES_LEAD_CONTACTS_PVT.Create_sales_lead_contacts(
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 => 'N',
-- 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,
P_SALES_LEAD_ID => l_SALES_LEAD_ID,
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 );
update as_sales_leads set description = l_default_lead_name where sales_lead_id =
l_SALES_LEAD_ID;
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 AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type,
-- P_Calling_From_WF_Flag IN VARCHAR2 := 'N',
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_Access (X_Sales_Lead_Id NUMBER , c_salesforce_id NUMBER, c_sales_group_id NUMBER) IS
Select
access_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,freeze_flag
,reassign_flag
,team_leader_flag
,customer_id
,address_id
,salesforce_id
,person_id
,partner_customer_id
,partner_address_id
,created_person_id
,lead_id
,freeze_date
,reassign_reason
,downloadable_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,salesforce_role_code
,salesforce_relationship_code
,sales_group_id
-- ,reassign_requested_person_id
-- ,reassign_request_date
-- ,internal_update_access
,sales_lead_id
From as_accesses_all
Where sales_lead_id = X_Sales_Lead_Id
and salesforce_id = c_salesforce_id
and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
Select last_update_date,
customer_id,
assign_to_salesforce_id,
assign_sales_group_id,
assign_to_person_id,
status_code,
reject_reason_code,
qualified_flag,
lead_rank_id,
referral_type,
referred_by,
referral_status,
accept_flag,
decision_timeframe_code,
creation_date
From AS_SALES_LEADS
Where SALES_LEAD_ID = p_Sales_Lead_ID
For Update NOWAIT;
SELECT opp_open_status_flag
FROM as_statuses_b
WHERE lead_flag = 'Y' and enabled_flag = 'Y'
and status_code = X_Lookup_Code;
SELECT min_score
FROM as_sales_lead_ranks_b
WHERE rank_id = X_Rank_ID;
SELECT phone_id
FROM AS_SALES_LEAD_CONTACTS
WHERE sales_lead_id = c_sales_lead_id;
SELECT contact_role_code
FROM AS_SALES_LEAD_CONTACTS
WHERE sales_lead_id = c_sales_lead_id ;
SELECT status_code
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
SELECT source_id
FROM jtf_rs_resource_extns
WHERE category = 'EMPLOYEE'
and resource_id = x_resource_id;
select 'Y'
from as_accesses_all a
where a.sales_lead_id = c_sales_lead_id
and a.owner_flag = 'Y'
and a.salesforce_id = c_identity_salesforce_id;
select LOC.COUNTRY
FROM HZ_PARTY_SITES SITE,HZ_LOCATIONS LOC
WHERE SITE.PARTY_ID = X_CUSTOMER_ID
AND SITE.PARTY_SITE_ID = X_ADDRESS_ID
AND SITE.STATUS IN ('A','I')
AND SITE.LOCATION_ID = LOC.LOCATION_ID;
select 'Y'
from as_accesses_all a
where a.sales_lead_id = c_sales_lead_id
and a.salesforce_id = c_salesforce_id
and nvl(a.sales_group_id, -99) = nvl(c_sales_group_id,-99);
SELECT timeframe_days
FROM aml_sales_lead_timeframes
where decision_timeframe_code = X_DECISION_TIMEFRAME_CODE and enabled_flag = 'Y';
SELECT lead_rank_id
FROM as_sales_leads_log
WHERE sales_lead_id = c_sales_lead_id
AND manual_rank_flag = 'N'
ORDER BY log_id DESC;
SELECT min_score
FROM as_sales_lead_ranks_b
WHERE rank_id = c_lead_rank_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;
l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_lead';
l_last_update_date DATE;
l_update_access_flag VARCHAR2(1);
SAVEPOINT UPDATE_SALES_LEAD_PVT;
Fetch C_Get_sales_leads into l_last_update_date,
l_customer_id,
l_assign_to_salesforce_id,
l_assign_sales_group_id,
l_assign_to_person_id,
l_old_status_code,
l_reject_reason_code,
l_qualified_flag,
l_lead_rank_id,
l_referral_type,
l_referred_by,
l_current_ref_status,
l_accept_flag,
l_decision_timeframe_code,
l_creation_date;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
IF (l_tar_SALES_LEAD_rec.last_update_date is NULL or
l_tar_SALES_LEAD_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
IF (l_tar_SALES_LEAD_rec.last_update_date <> l_last_update_date)
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
(l_tar_SALES_LEAD_rec.DELETED_FLAG IS NULL) or
(l_tar_SALES_LEAD_rec.currency_code IS NULL)or
(l_tar_SALES_LEAD_rec.lead_rank_ind is NULL)
THEN
IF (AS_DEBUG_LOW_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'Calling Set_default_values');
p_mode => AS_UTILITY_PVT.G_UPDATE,
px_SALES_LEAD_rec => l_tar_SALES_LEAD_Rec);
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
P_SALES_LEAD_Rec => l_tar_SALES_LEAD_Rec,
p_referral_type => l_referral_type,
p_referred_by => l_referred_by,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id =>
l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => p_sales_lead_rec.sales_lead_id
-- ffang 012501, p_check_access_flag should always be 'Y'
,p_check_access_flag => p_check_access_flag -- 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_UPDATE_PRIVILEGE');
,x_update_access_flag => l_check_owner);
'API_NO_LEAD_UPDATE_PRIVILEGE');
'Calling Sales_Lead_Update_Row');
'Just before AS_SALES_LEADS_PKG.Sales_Lead_Update_Row');
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => l_tar_SALES_LEAD_rec.SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => l_tar_SALES_LEAD_rec.CREATION_DATE,
p_CREATED_BY => l_tar_SALES_LEAD_rec.CREATED_BY,
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 => l_tar_SALES_LEAD_rec.LEAD_NUMBER,
--p_STATUS_CODE => l_qualified,
p_STATUS_CODE => l_tar_SALES_LEAD_rec.STATUS_CODE,
p_CUSTOMER_ID => l_tar_SALES_LEAD_rec.CUSTOMER_ID,
p_ADDRESS_ID => l_tar_SALES_LEAD_rec.ADDRESS_ID,
p_SOURCE_PROMOTION_ID => l_tar_SALES_LEAD_rec.SOURCE_PROMOTION_ID,
p_INITIATING_CONTACT_ID => l_tar_SALES_LEAD_rec.INITIATING_CONTACT_ID,
p_ORIG_SYSTEM_REFERENCE => l_tar_SALES_LEAD_rec.ORIG_SYSTEM_REFERENCE,
p_CONTACT_ROLE_CODE => l_tar_SALES_LEAD_rec.CONTACT_ROLE_CODE,
p_CHANNEL_CODE => l_tar_SALES_LEAD_rec.CHANNEL_CODE,
p_BUDGET_AMOUNT => l_tar_SALES_LEAD_rec.BUDGET_AMOUNT,
p_CURRENCY_CODE => l_tar_SALES_LEAD_rec.CURRENCY_CODE,
p_DECISION_TIMEFRAME_CODE =>
l_tar_SALES_LEAD_rec.DECISION_TIMEFRAME_CODE,
p_CLOSE_REASON => l_tar_SALES_LEAD_rec.CLOSE_REASON,
p_LEAD_RANK_ID => l_tar_SALES_LEAD_rec.LEAD_RANK_ID,
p_LEAD_RANK_CODE => l_tar_SALES_LEAD_rec.LEAD_RANK_CODE,
p_PARENT_PROJECT => l_tar_SALES_LEAD_rec.PARENT_PROJECT,
p_DESCRIPTION => l_tar_SALES_LEAD_rec.DESCRIPTION,
p_ATTRIBUTE_CATEGORY => l_tar_SALES_LEAD_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_tar_SALES_LEAD_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_tar_SALES_LEAD_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_tar_SALES_LEAD_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_tar_SALES_LEAD_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_tar_SALES_LEAD_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_tar_SALES_LEAD_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_tar_SALES_LEAD_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_tar_SALES_LEAD_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_tar_SALES_LEAD_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_tar_SALES_LEAD_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_tar_SALES_LEAD_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_tar_SALES_LEAD_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_tar_SALES_LEAD_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_tar_SALES_LEAD_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_tar_SALES_LEAD_rec.ATTRIBUTE15,
p_ASSIGN_TO_PERSON_ID => l_tar_SALES_LEAD_rec.ASSIGN_TO_PERSON_ID,
p_ASSIGN_TO_SALESFORCE_ID =>
l_tar_SALES_LEAD_rec.ASSIGN_TO_SALESFORCE_ID,
p_ASSIGN_SALES_GROUP_ID => l_tar_SALES_LEAD_rec.ASSIGN_SALES_GROUP_ID,
--p_ASSIGN_DATE => l_tar_SALES_LEAD_rec.ASSIGN_DATE,
p_ASSIGN_DATE => l_tar_SALES_LEAD_rec.ASSIGN_DATE,--SYSDATE
p_BUDGET_STATUS_CODE => l_tar_SALES_LEAD_rec.BUDGET_STATUS_CODE,
p_ACCEPT_FLAG => NVL(l_tar_SALES_LEAD_rec.ACCEPT_FLAG, 'N'),
p_VEHICLE_RESPONSE_CODE => l_tar_SALES_LEAD_rec.VEHICLE_RESPONSE_CODE,
p_TOTAL_SCORE => l_tar_SALES_LEAD_rec.TOTAL_SCORE,
p_SCORECARD_ID => l_tar_SALES_LEAD_rec.SCORECARD_ID,
p_KEEP_FLAG => NVL(l_tar_SALES_LEAD_rec.KEEP_FLAG, 'N'),
p_URGENT_FLAG => NVL(l_tar_SALES_LEAD_rec.URGENT_FLAG, 'N'),
p_IMPORT_FLAG => NVL(l_tar_SALES_LEAD_rec.IMPORT_FLAG, 'N'),
p_REJECT_REASON_CODE => l_tar_SALES_LEAD_rec.REJECT_REASON_CODE,
p_DELETED_FLAG => NVL(l_tar_SALES_LEAD_rec.DELETED_FLAG, 'N'),
p_OFFER_ID => p_SALES_LEAD_rec.OFFER_ID,
--p_QUALIFIED_FLAG => p_SALES_LEAD_rec.QUALIFIED_FLAG,
p_QUALIFIED_FLAG => l_qualified,
p_ORIG_SYSTEM_CODE => p_SALES_LEAD_rec.ORIG_SYSTEM_CODE,
-- p_SECURITY_GROUP_ID => p_SALES_LEAD_rec.SECURITY_GROUP_ID,
p_INC_PARTNER_PARTY_ID => p_SALES_LEAD_rec.INCUMBENT_PARTNER_PARTY_ID,
p_INC_PARTNER_RESOURCE_ID =>
p_SALES_LEAD_rec.INCUMBENT_PARTNER_RESOURCE_ID,
p_PRM_EXEC_SPONSOR_FLAG => p_SALES_LEAD_rec.PRM_EXEC_SPONSOR_FLAG,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG =>
p_SALES_LEAD_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG,
p_PRM_SALES_LEAD_TYPE => p_SALES_LEAD_rec.PRM_SALES_LEAD_TYPE,
p_PRM_IND_CLASSIFICATION_CODE =>
p_SALES_LEAD_rec.PRM_IND_CLASSIFICATION_CODE,
p_PRM_ASSIGNMENT_TYPE => p_SALES_LEAD_rec.PRM_ASSIGNMENT_TYPE,
p_AUTO_ASSIGNMENT_TYPE => p_SALES_LEAD_rec.AUTO_ASSIGNMENT_TYPE,
p_PRIMARY_CONTACT_PARTY_ID => p_SALES_LEAD_rec.PRIMARY_CONTACT_PARTY_ID,
p_PRIMARY_CNT_PERSON_PARTY_ID => p_SALES_LEAD_rec.PRIMARY_CNT_PERSON_PARTY_ID,
p_PRIMARY_CONTACT_PHONE_ID => p_SALES_LEAD_rec.PRIMARY_CONTACT_PHONE_ID,
-- new columns for CAPRI lead referral
p_REFERRED_BY => p_SALES_LEAD_rec.REFERRED_BY,
p_REFERRAL_TYPE => p_SALES_LEAD_rec.REFERRAL_TYPE,
p_REFERRAL_STATUS => l_referral_status_profile,
p_REF_DECLINE_REASON => p_SALES_LEAD_rec.REF_DECLINE_REASON,
p_REF_COMM_LTR_STATUS => p_SALES_LEAD_rec.REF_COMM_LTR_STATUS,
p_REF_ORDER_NUMBER => p_SALES_LEAD_rec.REF_ORDER_NUMBER,
p_REF_ORDER_AMT => p_SALES_LEAD_rec.REF_ORDER_AMT,
p_REF_COMM_AMT => p_SALES_LEAD_rec.REF_COMM_AMT,
p_LEAD_DATE => p_SALES_LEAD_rec.LEAD_DATE,
p_SOURCE_SYSTEM => p_SALES_LEAD_rec.SOURCE_SYSTEM,
p_COUNTRY => l_country_code,
p_TOTAL_AMOUNT => p_SALES_LEAD_rec.TOTAL_AMOUNT,
p_EXPIRATION_DATE => l_exp_date, -- p_SALES_LEAD_rec.EXPIRATION_DATE,
p_LEAD_RANK_IND => l_lead_rank_ind,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE, --p_SALES_LEAD_rec.LEAD_ENGINE_RUN_DATE,
p_CURRENT_REROUTES => p_SALES_LEAD_rec.CURRENT_REROUTES
-- new columns for appsperf CRMAP denorm project bug 2928041
,p_STATUS_OPEN_FLAG => l_new_status_flag
--FND_API.G_MISS_CHAR
,p_LEAD_RANK_SCORE => l_new_lead_rank_score
--FND_API.G_MISS_NUM
-- ckapoor 11.5.10 New columns
, p_MARKETING_SCORE => l_tar_SALES_LEAD_rec.MARKETING_SCORE
, p_INTERACTION_SCORE => l_tar_SALES_LEAD_rec.INTERACTION_SCORE
, p_SOURCE_PRIMARY_REFERENCE => l_tar_SALES_LEAD_rec.SOURCE_PRIMARY_REFERENCE
, p_SOURCE_SECONDARY_REFERENCE => l_tar_SALES_LEAD_rec.SOURCE_SECONDARY_REFERENCE
, p_SALES_METHODOLOGY_ID => l_tar_SALES_LEAD_rec.SALES_METHODOLOGY_ID
, p_SALES_STAGE_ID => l_tar_SALES_LEAD_rec.SALES_STAGE_ID
);
-- update_sales_lead, it will cause infinite recursive calls
-- Kick off Lead Assignment Workflow
CALL_WF_TO_ASSIGN (
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Sales_Lead_Id => l_tar_SALES_LEAD_rec.SALES_LEAD_ID,
P_assigned_resource_id => l_tar_SALES_LEAD_rec.ASSIGN_TO_SALESFORCE_ID,
X_Return_Status => l_Return_Status,
X_Msg_Count => l_Msg_Count,
X_Msg_Data => l_Msg_Data
);
'Just after AS_SALES_LEADS_PKG.Sales_Lead_Update_Row');
'Just after AS_SALES_LEADS_PKG.Sales_Lead_Update_Row');
'Calling AS_SALES_LEADS_LOG_PKG.Insert_Row');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => l_tar_sales_lead_rec.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,
-- using standard parameters for program who columns
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_tar_sales_lead_rec.status_code,
l_log_status_code,
p_assign_to_person_id =>
--l_tar_sales_lead_rec.assign_to_person_id,
l_log_assign_to_person_id,
p_assign_to_salesforce_id =>
--l_tar_sales_lead_rec.assign_to_salesforce_id,
l_log_assign_to_sf_id,
p_reject_reason_code =>
--l_tar_sales_lead_rec.reject_reason_code,
l_log_reject_reason_code,
p_assign_sales_group_id =>
--l_tar_sales_lead_rec.assign_sales_group_id,
l_log_assign_sg_id,
p_lead_rank_id =>
--l_tar_sales_lead_rec.lead_rank_id,
l_log_lead_rank_id,
p_qualified_flag => l_log_qualified,
p_category => fnd_api.g_miss_char,
p_manual_rank_flag => l_manual_rank_flag
);
'Not Calling AS_SALES_LEADS_LOG_PKG.Insert_Row');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => l_tar_sales_lead_rec.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,
-- using standard parameters for program who columns
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_referral_status_profile, -- for referral log, we use referral status
p_assign_to_person_id =>
--l_tar_sales_lead_rec.assign_to_person_id,
l_log_assign_to_person_id,
p_assign_to_salesforce_id=>
--l_tar_sales_lead_rec.assign_to_salesforce_id,
l_log_assign_to_sf_id,
p_reject_reason_code =>
--l_tar_sales_lead_rec.reject_reason_code,
l_log_reject_reason_code,
p_assign_sales_group_id =>
--l_tar_sales_lead_rec.assign_sales_group_id,
l_log_assign_sg_id,
p_lead_rank_id =>
--l_tar_sales_lead_rec.lead_rank_id,
l_log_lead_rank_id,
p_qualified_flag => l_log_qualified,
p_category => 'REFERRAL'
);
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;
-- Change since update_sales_lead is not called from workflow any longer.
--IF (P_Calling_From_WF_flag = 'Y')
--THEN
-- l_Sales_Team_Rec.created_by_TAP_flag := 'Y';
,l_Sales_Team_Rec.last_update_date
,l_Sales_Team_Rec.last_updated_by
,l_Sales_Team_Rec.creation_date
,l_Sales_Team_Rec.created_by
,l_Sales_Team_Rec.last_update_login
,l_Sales_Team_Rec.freeze_flag
,l_Sales_Team_Rec.reassign_flag
,l_Sales_Team_Rec.team_leader_flag
,l_Sales_Team_Rec.customer_id
,l_Sales_Team_Rec.address_id
,l_Sales_Team_Rec.salesforce_id
,l_Sales_Team_Rec.person_id
,l_Sales_Team_Rec.partner_customer_id
,l_Sales_Team_Rec.partner_address_id
,l_Sales_Team_Rec.created_person_id
,l_Sales_Team_Rec.lead_id
,l_Sales_Team_Rec.freeze_date
,l_Sales_Team_Rec.reassign_reason
,l_Sales_Team_Rec.downloadable_flag
,l_Sales_Team_Rec.attribute_category
,l_Sales_Team_Rec.attribute1
,l_Sales_Team_Rec.attribute2
,l_Sales_Team_Rec.attribute3
,l_Sales_Team_Rec.attribute4
,l_Sales_Team_Rec.attribute5
,l_Sales_Team_Rec.attribute6
,l_Sales_Team_Rec.attribute7
,l_Sales_Team_Rec.attribute8
,l_Sales_Team_Rec.attribute9
,l_Sales_Team_Rec.attribute10
,l_Sales_Team_Rec.attribute11
,l_Sales_Team_Rec.attribute12
,l_Sales_Team_Rec.attribute13
,l_Sales_Team_Rec.attribute14
,l_Sales_Team_Rec.attribute15
,l_Sales_Team_Rec.salesforce_role_code
,l_Sales_Team_Rec.salesforce_relationship_code
,l_Sales_Team_Rec.sales_group_id
-- ,l_Sales_Team_Rec.reassign_requested_person_id
-- ,l_Sales_Team_Rec.reassign_request_date
-- ,l_Sales_Team_Rec.internal_update_access
,l_Sales_Team_Rec.sales_lead_id;
,l_login_salesteam_rec.last_update_date
,l_login_salesteam_rec.last_updated_by
,l_login_salesteam_rec.creation_date
,l_login_salesteam_rec.created_by
,l_login_salesteam_rec.last_update_login
,l_login_salesteam_rec.freeze_flag
,l_login_salesteam_rec.reassign_flag
,l_login_salesteam_rec.team_leader_flag
,l_login_salesteam_rec.customer_id
,l_login_salesteam_rec.address_id
,l_login_salesteam_rec.salesforce_id
,l_login_salesteam_rec.person_id
,l_login_salesteam_rec.partner_customer_id
,l_login_salesteam_rec.partner_address_id
,l_login_salesteam_rec.created_person_id
,l_login_salesteam_rec.lead_id
,l_login_salesteam_rec.freeze_date
,l_login_salesteam_rec.reassign_reason
,l_login_salesteam_rec.downloadable_flag
,l_login_salesteam_rec.attribute_category
,l_login_salesteam_rec.attribute1
,l_login_salesteam_rec.attribute2
,l_login_salesteam_rec.attribute3
,l_login_salesteam_rec.attribute4
,l_login_salesteam_rec.attribute5
,l_login_salesteam_rec.attribute6
,l_login_salesteam_rec.attribute7
,l_login_salesteam_rec.attribute8
,l_login_salesteam_rec.attribute9
,l_login_salesteam_rec.attribute10
,l_login_salesteam_rec.attribute11
,l_login_salesteam_rec.attribute12
,l_login_salesteam_rec.attribute13
,l_login_salesteam_rec.attribute14
,l_login_salesteam_rec.attribute15
,l_login_salesteam_rec.salesforce_role_code
,l_login_salesteam_rec.salesforce_relationship_code
,l_login_salesteam_rec.sales_group_id
-- ,l_Sales_Team_Rec.reassign_requested_person_id
-- ,l_Sales_Team_Rec.reassign_request_date
-- ,l_Sales_Team_Rec.internal_update_access
,l_login_salesteam_rec.sales_lead_id;
--l_login_salesteam_rec.last_update_date := SYSDATE;
l_login_salesteam_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_login_salesteam_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
'CK:Calling Update_SalesTeam');
AS_ACCESS_PUB.Update_SalesTeam (
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 -- 'Y'
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_identity_salesforce_id => p_identity_salesforce_id
,p_sales_team_rec => l_login_salesteam_rec
,X_Return_Status => x_Return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,x_access_id => l_login_Access_Id
);
l_login_salesteam_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;
'Calling Update_SalesTeam');
AS_ACCESS_PUB.Update_SalesTeam (
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 -- 'Y'
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_identity_salesforce_id => p_identity_salesforce_id
,p_sales_team_rec => l_Sales_Team_Rec
,X_Return_Status => x_Return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,x_access_id => l_Access_Id
);
/* AS_ACCESS_PUB.Delete_SalesTeam(
p_api_version_number => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_access_profile_rec => l_access_profile_rec,
p_check_access_flag => 'Y',
p_admin_flag => p_admin_flag,
p_admin_group_id => p_admin_group_id,
p_identity_salesforce_id => p_identity_salesforce_id,
p_sales_team_rec => l_sales_team_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
); */
'Delete_SalesTeam:x_access_id > ' || l_access_id);
l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
/*AS_ACCESS_PUB.Update_SalesTeam (
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 -- 'Y'
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_identity_salesforce_id => p_identity_salesforce_id
,p_sales_team_rec => l_Sales_Team_Rec
,X_Return_Status => x_Return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,x_access_id => l_Access_Id
);
'Update_SalesTeam:x_access_id > ' || l_access_id);
'Calling Update_SalesTeam');
AS_ACCESS_PUB.Update_SalesTeam (
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 -- 'Y'
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_identity_salesforce_id => p_identity_salesforce_id
,p_sales_team_rec => l_Sales_Team_Rec
,X_Return_Status => x_Return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,x_access_id => l_Access_Id
);
l_Sales_Team_Rec.last_update_date := SYSDATE;
update as_accesses_all
set open_flag = null , last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
where sales_lead_id = l_tar_SALES_LEAD_rec.SALES_LEAD_ID;
update as_accesses_all
set open_flag = 'Y', last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
where sales_lead_id = l_tar_SALES_LEAD_rec.SALES_LEAD_ID;
-- Update lead_rank_score as well.
-- fixing problem found during testing that if u updated lead e.g from Cold Lead to
-- null rank, then lead_rank_score in as_accesses_all was not getting updated.
-- if new rank is null, we want to fix the score since old might have been non null.
-- if new rank is g_miss , means anyways the rank is not changing so score is not changing.
-- since we are re-using code from denorming for as_sales_leads, if l_new_lead_rank_score
-- is g_miss then dont update as_accesses_all, basically simulate table handler
if ( l_new_lead_rank_score <> FND_API.G_MISS_NUM ) THEN
-- update the as_accesses_all.lead_rank_score if the rank has been changed i.e not g_miss
update as_accesses_all
set lead_rank_score = l_new_lead_rank_score , last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
where sales_lead_id = l_tar_SALES_LEAD_rec.SALES_LEAD_ID;
END Update_sales_lead;