The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert BOOLEAN;
SELECT customer_id
FROM AS_LEADS_ALL
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_LEADS_ALL
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_ACCESSES_ALL A
WHERE lead_id = c_lead_id
AND salesforce_id in -- This condition was added for bug 5361442
(SELECT s.salesforce_id
FROM as_accesses_all s,
jtf_rs_resource_extns j
WHERE s.salesforce_id = j.resource_id
AND (j.end_date_active IS NULL
OR j.end_date_active > sysdate))
AND (c_cre_st_for_sc_flag = 'N' OR EXISTS
(SELECT 1
FROM AS_SALES_CREDITS C
WHERE C.lead_id = A.lead_id
AND C.salesforce_id = A.salesforce_id
AND C.salesgroup_id = A.sales_group_id))
ORDER BY nvl(owner_flag, 'N') desc;
SELECT access_id,last_update_date
FROM AS_ACCESSES_ALL
WHERE lead_id = c_lead_id
AND salesforce_id = c_salesforce_id
AND nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99)
AND nvl(owner_flag,'N')='Y';
SELECT access_id,last_update_date
FROM AS_ACCESSES_ALL
WHERE lead_id = c_lead_id
AND salesforce_id = c_salesforce_id
AND nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
SELECT *
FROM AS_LEAD_LINES_ALL
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_LEAD_CONTACTS
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_LEAD_COMPETITORS
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_SALES_CREDITS
WHERE lead_id = c_lead_id
AND lead_line_id = c_lead_line_id;
SELECT *
FROM AS_LEAD_COMP_PRODUCTS
WHERE lead_line_id = c_lead_line_id;
SELECT *
FROM AS_LEAD_DECISION_FACTORS
WHERE lead_line_id = c_lead_line_id;
l_header_rec.DELETE_ALLOWED_FLAG := hdr.DELETE_ALLOWED_FLAG;
l_header_rec.DELETED_FLAG := hdr.DELETED_FLAG;
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;
l_insert := true;
l_insert := false;
if (l_insert) then
IF l_debug THEN
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
'Calling Create_SalesTeam');
l_Sales_Team_Rec.last_update_date := l_date;
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_access_profile_rec => l_access_profile_rec
,p_check_access_flag => 'N' -- 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_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
);
'Calling Update_SalesTeam: access_id > ' || l_val);
l_Sales_Team_Rec.last_update_date := l_date;
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_access_profile_rec => l_access_profile_rec
,p_check_access_flag => 'N' -- 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_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: Done');
AS_LEAD_LINES_PKG.Insert_Row(
px_LEAD_LINE_ID => l_LEAD_LINE_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 => lr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => lr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => lr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => lr.PROGRAM_UPDATE_DATE,
p_LEAD_ID => x_LEAD_ID,
p_INTEREST_TYPE_ID => lr.INTEREST_TYPE_ID,
p_PRIMARY_INTEREST_CODE_ID => lr.PRIMARY_INTEREST_CODE_ID,
p_SECONDARY_INTEREST_CODE_ID => lr.SECONDARY_INTEREST_CODE_ID,
p_INTEREST_STATUS_CODE => lr.INTEREST_STATUS_CODE,
p_INVENTORY_ITEM_ID => lr.INVENTORY_ITEM_ID,
p_ORGANIZATION_ID => lr.ORGANIZATION_ID,
p_UOM_CODE => lr.UOM_CODE,
p_QUANTITY => lr.QUANTITY,
p_TOTAL_AMOUNT => lr.TOTAL_AMOUNT,
p_SALES_STAGE_ID => lr.SALES_STAGE_ID,
p_WIN_PROBABILITY => lr.WIN_PROBABILITY,
p_DECISION_DATE => lr.DECISION_DATE,
p_ORG_ID => lr.ORG_ID,
p_ATTRIBUTE_CATEGORY => lr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => lr.ATTRIBUTE1,
p_ATTRIBUTE2 => lr.ATTRIBUTE2,
p_ATTRIBUTE3 => lr.ATTRIBUTE3,
p_ATTRIBUTE4 => lr.ATTRIBUTE4,
p_ATTRIBUTE5 => lr.ATTRIBUTE5,
p_ATTRIBUTE6 => lr.ATTRIBUTE6,
p_ATTRIBUTE7 => lr.ATTRIBUTE7,
p_ATTRIBUTE8 => lr.ATTRIBUTE8,
p_ATTRIBUTE9 => lr.ATTRIBUTE9,
p_ATTRIBUTE10 => lr.ATTRIBUTE10,
p_ATTRIBUTE11 => lr.ATTRIBUTE11,
p_ATTRIBUTE12 => lr.ATTRIBUTE12,
p_ATTRIBUTE13 => lr.ATTRIBUTE13,
p_ATTRIBUTE14 => lr.ATTRIBUTE14,
p_ATTRIBUTE15 => lr.ATTRIBUTE15,
p_STATUS_CODE => lr.STATUS_CODE,
p_CHANNEL_CODE => lr.CHANNEL_CODE,
p_QUOTED_LINE_FLAG => lr.QUOTED_LINE_FLAG,
p_PRICE => lr.PRICE,
p_PRICE_VOLUME_MARGIN => lr.PRICE_VOLUME_MARGIN,
p_SHIP_DATE => lr.SHIP_DATE,
p_FORECAST_DATE => lr.FORECAST_DATE,
p_ROLLING_FORECAST_FLAG => lr.ROLLING_FORECAST_FLAG,
p_SOURCE_PROMOTION_ID => lr.SOURCE_PROMOTION_ID,
p_OFFER_ID => lr.OFFER_ID,
p_PRODUCT_CATEGORY_ID => lr.PRODUCT_CATEGORY_ID,
p_PRODUCT_CAT_SET_ID => lr.PRODUCT_CAT_SET_ID);
'Private API: as_lead_lines_pkg.insert_row fail');
'Private API: as_lead_lines_pkg.insert_row '|| l_lead_line_id);
AS_SALES_CREDITS_PKG.Insert_Row(
px_SALES_CREDIT_ID => l_SALES_CREDIT_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 => scr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => scr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => scr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => scr.PROGRAM_UPDATE_DATE,
p_LEAD_ID => x_LEAD_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_SALESFORCE_ID => scr.SALESFORCE_ID,
p_PERSON_ID => scr.PERSON_ID,
p_SALESGROUP_ID => scr.SALESGROUP_ID,
p_PARTNER_CUSTOMER_ID => scr.PARTNER_CUSTOMER_ID,
p_PARTNER_ADDRESS_ID => scr.PARTNER_ADDRESS_ID,
p_REVENUE_AMOUNT => scr.REVENUE_AMOUNT,
p_REVENUE_PERCENT => scr.REVENUE_PERCENT,
p_QUOTA_CREDIT_AMOUNT => scr.QUOTA_CREDIT_AMOUNT,
p_QUOTA_CREDIT_PERCENT => scr.QUOTA_CREDIT_PERCENT,
p_ATTRIBUTE_CATEGORY => scr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => scr.ATTRIBUTE1,
p_ATTRIBUTE2 => scr.ATTRIBUTE2,
p_ATTRIBUTE3 => scr.ATTRIBUTE3,
p_ATTRIBUTE4 => scr.ATTRIBUTE4,
p_ATTRIBUTE5 => scr.ATTRIBUTE5,
p_ATTRIBUTE6 => scr.ATTRIBUTE6,
p_ATTRIBUTE7 => scr.ATTRIBUTE7,
p_ATTRIBUTE8 => scr.ATTRIBUTE8,
p_ATTRIBUTE9 => scr.ATTRIBUTE9,
p_ATTRIBUTE10 => scr.ATTRIBUTE10,
p_ATTRIBUTE11 => scr.ATTRIBUTE11,
p_ATTRIBUTE12 => scr.ATTRIBUTE12,
p_ATTRIBUTE13 => scr.ATTRIBUTE13,
p_ATTRIBUTE14 => scr.ATTRIBUTE14,
p_ATTRIBUTE15 => scr.ATTRIBUTE15,
p_MANAGER_REVIEW_FLAG => scr.MANAGER_REVIEW_FLAG,
p_MANAGER_REVIEW_DATE => scr.MANAGER_REVIEW_DATE,
p_ORIGINAL_SALES_CREDIT_ID => scr.ORIGINAL_SALES_CREDIT_ID,
p_CREDIT_PERCENT => scr.CREDIT_PERCENT,
p_CREDIT_AMOUNT => scr.CREDIT_AMOUNT,
p_CREDIT_TYPE_ID => scr.CREDIT_TYPE_ID,
-- The following fields are not passed before ASNB
p_OPP_WORST_FORECAST_AMOUNT => scr.OPP_WORST_FORECAST_AMOUNT,
p_OPP_FORECAST_AMOUNT => scr.OPP_FORECAST_AMOUNT,
p_OPP_BEST_FORECAST_AMOUNT => scr.OPP_BEST_FORECAST_AMOUNT,
P_DEFAULTED_FROM_OWNER_FLAG =>scr.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
);
'Private API: as_sales_credits_pkg.insert_row fail');
'Private API: as_sales_credits_pkg.insert_row '|| l_sales_credit_id);
l_sales_credit_rec.last_update_date := SYSDATE;
l_sales_credit_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_sales_credit_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
SELECT sum(OPP_FORECAST_AMOUNT) INTO l_TOT_REVENUE_OPP_FORECAST_AMT
FROM AS_SALES_CREDITS
WHERE lead_id = l_Header_rec.lead_id AND
credit_type_id = l_forecast_credit_type_id;
UPDATE AS_LEADS_ALL
SET TOTAL_REVENUE_OPP_FORECAST_AMT = l_TOT_REVENUE_OPP_FORECAST_AMT
WHERE lead_id = l_Header_rec.lead_id;
AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row(
p_ATTRIBUTE15 => cpdr.ATTRIBUTE15,
p_ATTRIBUTE14 => cpdr.ATTRIBUTE14,
p_ATTRIBUTE13 => cpdr.ATTRIBUTE13,
p_ATTRIBUTE12 => cpdr.ATTRIBUTE12,
p_ATTRIBUTE11 => cpdr.ATTRIBUTE11,
p_ATTRIBUTE10 => cpdr.ATTRIBUTE10,
p_ATTRIBUTE9 => cpdr.ATTRIBUTE9,
p_ATTRIBUTE8 => cpdr.ATTRIBUTE8,
p_ATTRIBUTE7 => cpdr.ATTRIBUTE7,
p_ATTRIBUTE6 => cpdr.ATTRIBUTE6,
p_ATTRIBUTE4 => cpdr.ATTRIBUTE4,
p_ATTRIBUTE5 => cpdr.ATTRIBUTE5,
p_ATTRIBUTE2 => cpdr.ATTRIBUTE2,
p_ATTRIBUTE3 => cpdr.ATTRIBUTE3,
p_ATTRIBUTE1 => cpdr.ATTRIBUTE1,
p_ATTRIBUTE_CATEGORY => cpdr.ATTRIBUTE_CATEGORY,
p_PROGRAM_ID => cpdr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => cpdr.PROGRAM_UPDATE_DATE,
p_PROGRAM_APPLICATION_ID => cpdr.PROGRAM_APPLICATION_ID,
p_REQUEST_ID => cpdr.REQUEST_ID,
p_WIN_LOSS_STATUS => cpdr.WIN_LOSS_STATUS,
p_COMPETITOR_PRODUCT_ID => cpdr.COMPETITOR_PRODUCT_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_LEAD_ID => x_LEAD_ID,
px_LEAD_COMPETITOR_PROD_ID => l_LEAD_COMPETITOR_PROD_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE);
'Private API: as_lead_comp_products_pkg.insert_row fail');
'Private API: as_lead_comp_products_pkg.insert_row '|| l_lead_competitor_prod_id);
AS_LEAD_DECISION_FACTORS_PKG.Insert_Row(
p_ATTRIBUTE15 => dfcr.ATTRIBUTE15,
p_ATTRIBUTE14 => dfcr.ATTRIBUTE14,
p_ATTRIBUTE13 => dfcr.ATTRIBUTE13,
p_ATTRIBUTE12 => dfcr.ATTRIBUTE12,
p_ATTRIBUTE11 => dfcr.ATTRIBUTE11,
p_ATTRIBUTE10 => dfcr.ATTRIBUTE10,
p_ATTRIBUTE9 => dfcr.ATTRIBUTE9,
p_ATTRIBUTE8 => dfcr.ATTRIBUTE8,
p_ATTRIBUTE7 => dfcr.ATTRIBUTE7,
p_ATTRIBUTE6 => dfcr.ATTRIBUTE6,
p_ATTRIBUTE5 => dfcr.ATTRIBUTE5,
p_ATTRIBUTE4 => dfcr.ATTRIBUTE4,
p_ATTRIBUTE3 => dfcr.ATTRIBUTE3,
p_ATTRIBUTE2 => dfcr.ATTRIBUTE2,
p_ATTRIBUTE1 => dfcr.ATTRIBUTE1,
p_ATTRIBUTE_CATEGORY => dfcr.ATTRIBUTE_CATEGORY,
p_PROGRAM_UPDATE_DATE => dfcr.PROGRAM_UPDATE_DATE,
p_PROGRAM_ID => dfcr.PROGRAM_ID,
p_PROGRAM_APPLICATION_ID => dfcr.PROGRAM_APPLICATION_ID,
p_REQUEST_ID => dfcr.REQUEST_ID,
p_DECISION_RANK => dfcr.DECISION_RANK,
p_DECISION_PRIORITY_CODE => dfcr.DECISION_PRIORITY_CODE,
p_DECISION_FACTOR_CODE => dfcr.DECISION_FACTOR_CODE,
px_LEAD_DECISION_FACTOR_ID => l_LEAD_DECISION_FACTOR_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_CREATE_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_CREATION_DATE => SYSDATE);
'Private API: as_lead_decision_factors_pkg.insert_row fail');
'Private API: as_lead_decision_factors_pkg.insert_row '|| l_lead_decision_factor_id );
AS_LEAD_CONTACTS_PKG.Insert_Row(
px_LEAD_CONTACT_ID => l_LEAD_CONTACT_ID,
p_LEAD_ID => x_LEAD_ID,
p_CONTACT_ID => cnr.CONTACT_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 => cnr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => cnr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => cnr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => cnr.PROGRAM_UPDATE_DATE,
p_ENABLED_FLAG => cnr.ENABLED_FLAG,
p_CUSTOMER_ID => nvl(p_new_customer_id, cnr.CUSTOMER_ID),
p_ADDRESS_ID => p_new_address_id,
p_RANK => cnr.RANK,
p_PHONE_ID => cnr.PHONE_ID,
p_ATTRIBUTE_CATEGORY => cnr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => cnr.ATTRIBUTE1,
p_ATTRIBUTE2 => cnr.ATTRIBUTE2,
p_ATTRIBUTE3 => cnr.ATTRIBUTE3,
p_ATTRIBUTE4 => cnr.ATTRIBUTE4,
p_ATTRIBUTE5 => cnr.ATTRIBUTE5,
p_ATTRIBUTE6 => cnr.ATTRIBUTE6,
p_ATTRIBUTE7 => cnr.ATTRIBUTE7,
p_ATTRIBUTE8 => cnr.ATTRIBUTE8,
p_ATTRIBUTE9 => cnr.ATTRIBUTE9,
p_ATTRIBUTE10 => cnr.ATTRIBUTE10,
p_ATTRIBUTE11 => cnr.ATTRIBUTE11,
p_ATTRIBUTE12 => cnr.ATTRIBUTE12,
p_ATTRIBUTE13 => cnr.ATTRIBUTE13,
p_ATTRIBUTE14 => cnr.ATTRIBUTE14,
p_ATTRIBUTE15 => cnr.ATTRIBUTE15,
p_ORG_ID => cnr.ORG_ID,
p_PRIMARY_CONTACT_FLAG => cnr.PRIMARY_CONTACT_FLAG,
p_ROLE => cnr.ROLE,
p_CONTACT_PARTY_ID => cnr.CONTACT_PARTY_ID);
'Private API: as_lead_lines_pkg.insert_row fail');
'Private API: as_lead_contacts_pkg.insert_row '|| l_lead_contact_id);
AS_LEAD_COMPETITORS_PKG.Insert_Row(
px_LEAD_COMPETITOR_ID => l_LEAD_COMPETITOR_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 => cmpr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID =>cmpr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => cmpr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => cmpr.PROGRAM_UPDATE_DATE,
p_LEAD_ID => x_LEAD_ID,
p_COMPETITOR_CODE => cmpr.COMPETITOR_CODE,
p_COMPETITOR => cmpr.COMPETITOR,
p_PRODUCTS => cmpr.PRODUCTS,
p_COMMENTS => cmpr.COMMENTS,
p_ATTRIBUTE_CATEGORY => cmpr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => cmpr.ATTRIBUTE1,
p_ATTRIBUTE2 => cmpr.ATTRIBUTE2,
p_ATTRIBUTE3 => cmpr.ATTRIBUTE3,
p_ATTRIBUTE4 => cmpr.ATTRIBUTE4,
p_ATTRIBUTE5 => cmpr.ATTRIBUTE5,
p_ATTRIBUTE6 => cmpr.ATTRIBUTE6,
p_ATTRIBUTE7 => cmpr.ATTRIBUTE7,
p_ATTRIBUTE8 => cmpr.ATTRIBUTE8,
p_ATTRIBUTE9 => cmpr.ATTRIBUTE9,
p_ATTRIBUTE10 => cmpr.ATTRIBUTE10,
p_ATTRIBUTE11 => cmpr.ATTRIBUTE11,
p_ATTRIBUTE12 => cmpr.ATTRIBUTE12,
p_ATTRIBUTE13 => cmpr.ATTRIBUTE13,
p_ATTRIBUTE14 => cmpr.ATTRIBUTE14,
p_ATTRIBUTE15 => cmpr.ATTRIBUTE15,
p_WIN_LOSS_STATUS => cmpr.WIN_LOSS_STATUS,
p_COMPETITOR_RANK => cmpr.COMPETITOR_RANK,
p_RELATIONSHIP_PARTY_ID => cmpr.RELATIONSHIP_PARTY_ID,
p_COMPETITOR_ID => cmpr.COMPETITOR_ID);
'Private API: as_lead_competitors_pkg.insert_row fail');
'Private API: as_lead_competitors_pkg.insert_row '|| l_lead_competitor_id);