[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF l_Trigger_Mode = 'ON-UPDATE' THEN
l_old_prob_bucket := get_prob_bucket(l_old_win_probability);
ELSIF l_Trigger_Mode = 'ON-INSERT' THEN
l_apply_frcst_def := TRUE;
select customer_id, address_id
from as_leads
where lead_id = p_lead_id;
SELECT source_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = p_resource_id
AND category = 'EMPLOYEE';
SELECT source_id, address_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = p_resource_id
AND (category = 'PARTNER'
OR category = 'PARTY');
SELECT FREEZE_FLAG
FROM AS_LEADS
WHERE LEAD_ID = c_LEAD_ID;
Change the select clause so that it returns team_leader_flag,last_update_date,access_id
instead of 'X'.
*/
cursor get_dup_sales_team(c_customer_id NUMBER,c_address_id NUMBER,c_lead_id NUMBER,c_salesforce_id NUMBER, c_sales_group_id NUMBER ) is
select team_leader_flag,last_update_date,access_id
from as_accesses
where customer_id = c_customer_id
--and nvl(address_id, -99) = nvl(c_address_id, -99)
and nvl(lead_id, -99) = nvl(c_lead_id, -99)
and salesforce_id = c_salesforce_id
and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
SELECT to_char(max(c.group_id))
FROM jtf_rs_resource_extns a, fnd_user b, jtf_rs_group_members c, JTF_RS_ROLE_RELATIONS d , JTF_RS_ROLES_B e
WHERE a.user_id = b.user_id
AND a.resource_id = c.resource_id
AND e.ROLE_TYPE_CODE in ('SALES','TELESALES','FIELDSALES','PRM')
AND c.GROUP_MEMBER_ID = d.ROLE_RESOURCE_ID
AND d.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' AND d.ROLE_ID = e.ROLE_ID
AND a.resource_id = c_SALESFORCE_ID;
l_update_access_flag VARCHAR2(1);
AS_ACCESS_PUB.has_updateOpportunityAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,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_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
,p_check_access_flag => p_check_access_flag
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => p_partner_cont_party_id
,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 );
'has_updateOpportunityAccess fail');
IF (l_update_access_flag <> 'Y') THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
Select lead.win_probability, status.win_loss_indicator,
status.forecast_rollup_flag
Into l_win_probability, l_win_loss_indicator,
l_forecast_rollup_flag
From as_leads_all lead, as_statuses_vl status
Where lead_id = l_sales_credit_rec.LEAD_ID
And lead.status = status.status_code(+);
l_sales_credit_rec.CREDIT_AMOUNT, 'ON-INSERT',
l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
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 => l_sales_credit_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_sales_credit_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_sales_credit_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_sales_credit_rec.PROGRAM_UPDATE_DATE,
p_LEAD_ID => l_sales_credit_rec.LEAD_ID,
p_LEAD_LINE_ID => l_sales_credit_rec.LEAD_LINE_ID,
p_SALESFORCE_ID => l_sales_credit_rec.SALESFORCE_ID,
p_PERSON_ID => l_sales_credit_rec.PERSON_ID,
p_SALESGROUP_ID => l_sales_credit_rec.SALESGROUP_ID,
p_PARTNER_CUSTOMER_ID => l_sales_credit_rec.PARTNER_CUSTOMER_ID,
p_PARTNER_ADDRESS_ID => l_sales_credit_rec.PARTNER_ADDRESS_ID,
p_REVENUE_AMOUNT => l_sales_credit_rec.REVENUE_AMOUNT,
p_REVENUE_PERCENT => l_sales_credit_rec.REVENUE_PERCENT,
p_QUOTA_CREDIT_AMOUNT => l_sales_credit_rec.QUOTA_CREDIT_AMOUNT,
p_QUOTA_CREDIT_PERCENT => l_sales_credit_rec.QUOTA_CREDIT_PERCENT,
p_ATTRIBUTE_CATEGORY => l_sales_credit_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_sales_credit_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_sales_credit_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_sales_credit_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_sales_credit_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_sales_credit_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_sales_credit_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_sales_credit_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_sales_credit_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_sales_credit_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_sales_credit_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_sales_credit_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_sales_credit_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_sales_credit_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_sales_credit_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_sales_credit_rec.ATTRIBUTE15,
p_MANAGER_REVIEW_FLAG => l_sales_credit_rec.MANAGER_REVIEW_FLAG,
p_MANAGER_REVIEW_DATE => l_sales_credit_rec.MANAGER_REVIEW_DATE,
p_ORIGINAL_SALES_CREDIT_ID => l_sales_credit_rec.ORIGINAL_SALES_CREDIT_ID,
-- p_CREDIT_TYPE => l_sales_credit_rec.CREDIT_TYPE,
p_CREDIT_PERCENT => l_sales_credit_rec.CREDIT_PERCENT,
p_CREDIT_AMOUNT => l_sales_credit_rec.CREDIT_AMOUNT,
-- p_SECURITY_GROUP_ID => l_sales_credit_rec.SECURITY_GROUP_ID,
p_CREDIT_TYPE_ID => l_sales_credit_rec.CREDIT_TYPE_ID,
p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
P_DEFAULTED_FROM_OWNER_FLAG =>l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG -- -- Added for ASNB
);
If the sales creditor exists in the sales team then update the
full access flag ie team_leader_flag to 'Y' if not already set.
*/
ELSE -- get_dup_sales_team found
IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
nvl(l_upd_team_flag,'N') <> 'Y'
THEN
l_Sales_Team_Rec.last_update_date := l_upd_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 => 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: l_access_id = ' || l_access_id);
'update_SalesTeam fail');
PROCEDURE Update_sales_credits(
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_FALSE,
P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
P_Admin_Group_Id IN NUMBER,
P_Identity_Salesforce_Id IN NUMBER := NULL,
P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type,
X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Person_Id(p_resource_id NUMBER) IS
SELECT source_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = p_resource_id
AND category = 'EMPLOYEE';
SELECT source_id, address_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = p_resource_id
AND category = 'PARTNER';
Select rowid,
SALES_CREDIT_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_ID,
LEAD_LINE_ID,
SALESFORCE_ID,
PERSON_ID,
SALESGROUP_ID,
PARTNER_CUSTOMER_ID,
PARTNER_ADDRESS_ID,
REVENUE_AMOUNT,
REVENUE_PERCENT,
QUOTA_CREDIT_AMOUNT,
QUOTA_CREDIT_PERCENT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MANAGER_REVIEW_FLAG,
MANAGER_REVIEW_DATE,
ORIGINAL_SALES_CREDIT_ID,
-- CREDIT_TYPE,
CREDIT_PERCENT,
CREDIT_AMOUNT,
-- SECURITY_GROUP_ID,
CREDIT_TYPE_ID
From AS_SALES_CREDITS
WHERE SALES_CREDIT_ID = c_SALES_CREDIT_ID
-- Hint: Developer need to provide Where clause
For Update NOWAIT;
SELECT FREEZE_FLAG
FROM AS_LEADS
WHERE LEAD_ID = c_LEAD_ID;
select customer_id, address_id
from as_leads
where lead_id = p_lead_id;
Change the select clause so that it returns team_leader_flag,last_update_date,access_id
instead of 'X'.
*/
cursor get_dup_sales_team(c_customer_id NUMBER,c_address_id NUMBER,c_lead_id NUMBER,c_salesforce_id NUMBER, c_sales_group_id NUMBER ) is
select team_leader_flag,last_update_date,access_id
from as_accesses
where customer_id = c_customer_id
--and nvl(address_id, -99) = nvl(c_address_id, -99)
and nvl(lead_id, -99) = nvl(c_lead_id, -99)
and salesforce_id = c_salesforce_id
and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
select 'X'
from as_accesses
where customer_id = c_customer_id
--and nvl(address_id, -99) = nvl(c_address_id, -99)
and nvl(lead_id, -99) = nvl(c_lead_id, -99)
and salesforce_id = c_salesforce_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_credits';
l_update_access_flag VARCHAR2(1);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Update_sales_credits';
SAVEPOINT UPDATE_SALES_CREDITS_PVT;
AS_CALLOUT_PKG.Update_sales_credit_BU(
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_identity_salesforce_id => p_identity_salesforce_id,
P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AS_ACCESS_PUB.has_updateOpportunityAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,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_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
,p_check_access_flag => p_check_access_flag
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => p_partner_cont_party_id
,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 );
'has_updateOpportunityAccess fail');
IF (l_update_access_flag <> 'Y') THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
'Private API11- Open Cursor to Select');
l_ref_SALES_CREDIT_rec.LAST_UPDATE_DATE,
l_ref_SALES_CREDIT_rec.LAST_UPDATED_BY,
l_ref_SALES_CREDIT_rec.CREATION_DATE,
l_ref_SALES_CREDIT_rec.CREATED_BY,
l_ref_SALES_CREDIT_rec.LAST_UPDATE_LOGIN,
l_ref_SALES_CREDIT_rec.REQUEST_ID,
l_ref_SALES_CREDIT_rec.PROGRAM_APPLICATION_ID,
l_ref_SALES_CREDIT_rec.PROGRAM_ID,
l_ref_SALES_CREDIT_rec.PROGRAM_UPDATE_DATE,
l_ref_SALES_CREDIT_rec.LEAD_ID,
l_ref_SALES_CREDIT_rec.LEAD_LINE_ID,
l_ref_SALES_CREDIT_rec.SALESFORCE_ID,
l_ref_SALES_CREDIT_rec.PERSON_ID,
l_ref_SALES_CREDIT_rec.SALESGROUP_ID,
l_ref_SALES_CREDIT_rec.PARTNER_CUSTOMER_ID,
l_ref_SALES_CREDIT_rec.PARTNER_ADDRESS_ID,
l_ref_SALES_CREDIT_rec.REVENUE_AMOUNT,
l_ref_SALES_CREDIT_rec.REVENUE_PERCENT,
l_ref_SALES_CREDIT_rec.QUOTA_CREDIT_AMOUNT,
l_ref_SALES_CREDIT_rec.QUOTA_CREDIT_PERCENT,
l_ref_SALES_CREDIT_rec.ATTRIBUTE_CATEGORY,
l_ref_SALES_CREDIT_rec.ATTRIBUTE1,
l_ref_SALES_CREDIT_rec.ATTRIBUTE2,
l_ref_SALES_CREDIT_rec.ATTRIBUTE3,
l_ref_SALES_CREDIT_rec.ATTRIBUTE4,
l_ref_SALES_CREDIT_rec.ATTRIBUTE5,
l_ref_SALES_CREDIT_rec.ATTRIBUTE6,
l_ref_SALES_CREDIT_rec.ATTRIBUTE7,
l_ref_SALES_CREDIT_rec.ATTRIBUTE8,
l_ref_SALES_CREDIT_rec.ATTRIBUTE9,
l_ref_SALES_CREDIT_rec.ATTRIBUTE10,
l_ref_SALES_CREDIT_rec.ATTRIBUTE11,
l_ref_SALES_CREDIT_rec.ATTRIBUTE12,
l_ref_SALES_CREDIT_rec.ATTRIBUTE13,
l_ref_SALES_CREDIT_rec.ATTRIBUTE14,
l_ref_SALES_CREDIT_rec.ATTRIBUTE15,
l_ref_SALES_CREDIT_rec.MANAGER_REVIEW_FLAG,
l_ref_SALES_CREDIT_rec.MANAGER_REVIEW_DATE,
l_ref_SALES_CREDIT_rec.ORIGINAL_SALES_CREDIT_ID,
-- l_ref_SALES_CREDIT_rec.CREDIT_TYPE,
l_ref_SALES_CREDIT_rec.CREDIT_PERCENT,
l_ref_SALES_CREDIT_rec.CREDIT_AMOUNT,
-- l_ref_SALES_CREDIT_rec.SECURITY_GROUP_ID,
l_ref_SALES_CREDIT_rec.CREDIT_TYPE_ID;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
If (l_tar_SALES_CREDIT_rec.last_update_date is NULL or
l_tar_SALES_CREDIT_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_CREDIT_rec.last_update_date <> l_ref_SALES_CREDIT_rec.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');
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
P_SALES_CREDIT_Rec => l_SALES_CREDIT_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Private API: Calling update table handler');
Select lead.win_probability, status.win_loss_indicator,
status.forecast_rollup_flag
Into l_win_probability, l_win_loss_indicator,
l_forecast_rollup_flag
From as_leads_all lead, as_statuses_vl status
Where lead_id = l_sales_credit_rec.LEAD_ID
And lead.status = status.status_code(+);
l_sales_credit_rec.CREDIT_AMOUNT, 'ON-UPDATE',
l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
AS_SALES_CREDITS_PKG.Update_Row(
p_SALES_CREDIT_ID => l_sales_credit_rec.SALES_CREDIT_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 => l_sales_credit_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_sales_credit_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_sales_credit_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_sales_credit_rec.PROGRAM_UPDATE_DATE,
p_LEAD_ID => l_sales_credit_rec.LEAD_ID,
p_LEAD_LINE_ID => l_sales_credit_rec.LEAD_LINE_ID,
p_SALESFORCE_ID => l_sales_credit_rec.SALESFORCE_ID,
p_PERSON_ID => l_sales_credit_rec.PERSON_ID,
p_SALESGROUP_ID => l_sales_credit_rec.SALESGROUP_ID,
p_PARTNER_CUSTOMER_ID => l_sales_credit_rec.PARTNER_CUSTOMER_ID,
p_PARTNER_ADDRESS_ID => l_sales_credit_rec.PARTNER_ADDRESS_ID,
p_REVENUE_AMOUNT => l_sales_credit_rec.REVENUE_AMOUNT,
p_REVENUE_PERCENT => l_sales_credit_rec.REVENUE_PERCENT,
p_QUOTA_CREDIT_AMOUNT => l_sales_credit_rec.QUOTA_CREDIT_AMOUNT,
p_QUOTA_CREDIT_PERCENT => l_sales_credit_rec.QUOTA_CREDIT_PERCENT,
p_ATTRIBUTE_CATEGORY => l_sales_credit_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_sales_credit_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_sales_credit_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_sales_credit_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_sales_credit_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_sales_credit_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_sales_credit_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_sales_credit_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_sales_credit_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_sales_credit_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_sales_credit_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_sales_credit_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_sales_credit_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_sales_credit_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_sales_credit_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_sales_credit_rec.ATTRIBUTE15,
p_MANAGER_REVIEW_FLAG => l_sales_credit_rec.MANAGER_REVIEW_FLAG,
p_MANAGER_REVIEW_DATE => l_sales_credit_rec.MANAGER_REVIEW_DATE,
p_ORIGINAL_SALES_CREDIT_ID => l_sales_credit_rec.ORIGINAL_SALES_CREDIT_ID,
-- p_CREDIT_TYPE => l_sales_credit_rec.CREDIT_TYPE,
p_CREDIT_PERCENT => l_sales_credit_rec.CREDIT_PERCENT,
p_CREDIT_AMOUNT => l_sales_credit_rec.CREDIT_AMOUNT,
-- p_SECURITY_GROUP_ID => l_sales_credit_rec.SECURITY_GROUP_ID,
p_CREDIT_TYPE_ID => l_sales_credit_rec.CREDIT_TYPE_ID,
p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
P_DEFAULTED_FROM_OWNER_FLAG =>l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
);
'Private API17: Updated sales credit: ' ||l_sales_credit_rec.SALES_CREDIT_ID );
If the sales creditor exists in the sales team then update the
full access flag ie team_leader_flag to 'Y' if not already set.
*/
ELSE -- get_dup_sales_team found
IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
nvl(l_upd_team_flag,'N') <> 'Y'
THEN
l_Sales_Team_Rec.last_update_date := l_upd_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 => 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: l_access_id = ' || l_access_id);
'update_SalesTeam fail');
AS_CALLOUT_PKG.Update_sales_credit_AU(
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_identity_salesforce_id => p_identity_salesforce_id,
P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
-- Hint: Add detail tables as parameter lists if it's master-detail relationship.
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
End Update_sales_credits;
CURSOR C_DELETED_SALES_CREDITS( p_lead_line_id NUMBER,
p_forecast_credit_type_id NUMBER )IS
SELECT *
FROM AS_SALES_CREDITS
WHERE lead_line_id = p_lead_line_id
AND credit_type_id = p_forecast_credit_type_id;
SELECT FREEZE_FLAG
FROM AS_LEADS
WHERE LEAD_ID = c_LEAD_ID;
l_update_access_flag VARCHAR2(1);
delete_flag BOOLEAN;
AS_ACCESS_PUB.has_updateOpportunityAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,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_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
,p_check_access_flag => p_check_access_flag
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => p_partner_cont_party_id
,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 );
'has_updateOpportunityAccess fail');
IF (l_update_access_flag <> 'Y') THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
FOR scr in C_DELETED_SALES_CREDITS(l_lead_line_id,l_forecast_credit_type_id) LOOP
delete_flag := True;
delete_flag := False;
IF(delete_flag = true)
THEN
d_sales_credit_tbl(DL).sales_credit_id := scr.sales_credit_id;
AS_OPP_sales_credit_PVT.Delete_sales_credits(
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_NONE,
P_Check_Access_Flag => FND_API.G_FALSE,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_Profile_Tbl => P_Profile_tbl,
P_Partner_Cont_Party_Id => p_partner_cont_party_id,
P_Identity_Salesforce_Id => p_identity_salesforce_id,
P_Sales_Credit_Tbl => d_sales_credit_tbl,
X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
'Private API 26: Delete_Sales_credits fail');
AS_OPP_sales_credit_PVT.Update_sales_credits(
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_NONE,
P_Check_Access_Flag => FND_API.G_FALSE,
P_Admin_Flag => P_Admin_Flag ,
P_Admin_Group_Id => P_Admin_Group_Id,
P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
P_Partner_Cont_Party_Id => p_partner_cont_party_id,
P_Profile_Tbl => P_Profile_tbl,
P_Sales_Credit_Tbl => u_sales_credit_tbl,
X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
'Private API 28: Update_Sales_credits fail');
PROCEDURE Delete_sales_credits(
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_FALSE,
P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
P_Admin_Group_Id IN NUMBER,
P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
P_identity_salesforce_id IN NUMBER := NULL,
p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_type,
X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- solin, for bug 1554330
CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
SELECT FREEZE_FLAG
FROM AS_LEADS
WHERE LEAD_ID = c_LEAD_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_credits';
l_update_access_flag VARCHAR2(1);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Delete_sales_credits';
SAVEPOINT DELETE_SALES_CREDITS_PVT;
AS_CALLOUT_PKG.Delete_sales_credit_BD(
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_identity_salesforce_id => p_identity_salesforce_id,
P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
-- Hint: Add detail tables as parameter lists if it's master-detail relationship.
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AS_ACCESS_PUB.has_updateOpportunityAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,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_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
,p_check_access_flag => p_check_access_flag
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => p_partner_cont_party_id
,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 );
'has_updateOpportunityAccess fail');
IF (l_update_access_flag <> 'Y') THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
'Private API 31: Calling delete table handler');
AS_SALES_CREDITS_PKG.Delete_Row(
p_SALES_CREDIT_ID => p_SALES_CREDIT_tbl(I).SALES_CREDIT_ID);
AS_CALLOUT_PKG.Delete_sales_credit_AD(
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_identity_salesforce_id => p_identity_salesforce_id,
P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
-- Hint: Add detail tables as parameter lists if it's master-detail relationship.
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
End Delete_sales_credits;
SELECT 'X'
FROM as_sales_credits
WHERE sales_credit_id = c_Sales_Credit_Id;
ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
THEN
-- validate NOT NULL column
IF (p_SALES_CREDIT_ID is NULL) or (p_SALES_CREDIT_ID = FND_API.G_MISS_NUM)
THEN
IF l_debug THEN
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
'Private API 34: Violate NOT NULL constraint(SALES_CREDIT_ID)');
SELECT 'X'
FROM as_leads
WHERE lead_id = c_Lead_Id;
SELECT 'X'
FROM as_lead_lines
WHERE lead_line_id = c_Lead_Line_Id;
SELECT 'X'
FROM as_salesforce_v
WHERE salesforce_id = c_Salesforce_Id;
SELECT 'X'
FROM as_salesforce_v
WHERE EMPLOYEE_PERSON_ID = c_PERSON_ID;
SELECT 'X'
FROM as_sales_groups_v
WHERE SALES_GROUP_ID = c_SALES_GROUP_ID;
SELECT 'X'
FROM JTF_RS_GROUPS_B a, JTF_RS_GROUP_USAGES b
WHERE a.group_id = b.group_id
AND b.usage in ('SALES','PRM')
AND sysdate between nvl(a.start_date_active,sysdate) and
nvl(a.end_date_active,sysdate)
AND a.group_id = c_SALES_GROUP_ID;
SELECT 'X'
FROM as_salesforce_v
WHERE PARTNER_CUSTOMER_ID = c_PARTNER_CUSTOMER_ID;
SELECT 'X'
FROM as_salesforce_v
WHERE PARTNER_ADDRESS_ID = c_PARTNER_ADDRESS_ID;
SELECT 'X'
FROM oe_sales_credit_types
WHERE sales_credit_type_id = c_Credit_Type_Id;
SELECT 'X'
FROM jtf_rs_resource_extns res,
jtf_rs_role_relations rrel,
jtf_rs_roles_b role
WHERE sysdate between res.start_date_active and nvl(res.end_date_active,sysdate)
AND sysdate between rrel.start_date_active and nvl(rrel.end_date_active,sysdate)
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')
AND role.admin_flag = 'N'
AND res.resource_id = X_Salesforce_Id
AND res.source_id = X_Person_Id
AND res.category ='EMPLOYEE';
SELECT 'X'
FROM as_salesforce_v
WHERE ((type = 'PARTNER' and partner_customer_id = X_Partner_Customer_Id)
or (type = 'PARTY' and partner_contact_id = X_Partner_Customer_Id))
AND salesforce_id = X_Salesforce_Id;
SELECT 'X'
FROM as_sales_groups_v
WHERE sales_group_id = X_Sales_Group_Id;
SELECT 'X'
FROM as_sales_credits
WHERE lead_id =P_SALES_CREDIT_Rec.lead_id
AND lead_line_id = P_SALES_CREDIT_Rec.lead_line_id
AND salesforce_id = P_SALES_CREDIT_Rec.salesforce_id
AND person_id =P_SALES_CREDIT_Rec.person_id
AND salesgroup_id =P_SALES_CREDIT_Rec.salesgroup_id
AND credit_type_id = P_SALES_CREDIT_Rec.credit_type_id
AND (P_SALES_CREDIT_Rec.sales_credit_id is null or P_SALES_CREDIT_Rec.sales_credit_id = fnd_api.g_miss_num
OR sales_credit_id <> P_SALES_CREDIT_Rec.sales_credit_id);