The following lines contain the word 'select', 'insert', 'update' or 'delete':
select employee_person_id
from as_salesforce_v
where salesforce_id = p_salesforce_id;
IF px_access_profile_rec.mgr_update_profile_value IS NULL OR
px_access_profile_rec.mgr_update_profile_value = FND_API.G_MISS_CHAR
THEN
px_access_profile_rec.mgr_update_profile_value
:= FND_PROFILE.Value('AS_MGR_UPDATE');
IF px_access_profile_rec.admin_update_profile_value IS NULL OR
px_access_profile_rec.admin_update_profile_value = FND_API.G_MISS_CHAR
THEN
px_access_profile_rec.admin_update_profile_value
:= FND_PROFILE.Value('AS_ADMIN_UPDATE');
'AS_MGR_UPDATE: ' ||
px_access_profile_rec.mgr_update_profile_value);
'AS_ADMIN_UPDATE: ' ||
px_access_profile_rec.mgr_update_profile_value);
,x_update_access_flag OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'has_deleteLeadAccess';
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and a.owner_flag = 'Y'
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and (rm.parent_resource_id = rm.resource_id
and a.owner_flag ='Y'
or (rm.parent_resource_id <> rm.resource_id)));
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and a.owner_flag = 'Y'
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'x'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and ((rm.salesforce_id = p_identity_salesforce_id
and a.owner_flag ='Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and a.owner_flag = 'Y'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and lead.sales_lead_id = p_sales_lead_id;
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and lead.sales_lead_id = p_sales_lead_id;
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
if l_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.mgr_update_profile_value ='I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
elsif l_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_csr;
x_update_access_flag := 'Y';
'x_update_access_flag: ' || x_update_access_flag);
,x_update_access_flag OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'has_oppOwnerAccess';
select 'X'
from as_accesses_all a
where a.lead_id = p_lead_id
and a.owner_flag = 'Y'
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.lead_id = p_lead_id
and EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and (rm.parent_resource_id = rm.resource_id
and a.owner_flag ='Y'
or (rm.parent_resource_id <> rm.resource_id)));
select 'x'
from as_leads_all lead, as_accesses_all a, as_rpt_managers_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and lead.lead_id = p_lead_id;
select 'X'
from as_accesses_all a
where a.lead_id = p_lead_id
and a.owner_flag = 'Y'
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'x'
from as_accesses_all a
where a.lead_id = p_lead_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and ((rm.salesforce_id = p_identity_salesforce_id
and a.owner_flag ='Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a
where a.lead_id = p_lead_id
and a.owner_flag = 'Y'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_leads_all lead, as_accesses_all a, as_rpt_admins_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and lead.lead_id = p_lead_id;
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
if l_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.mgr_update_profile_value ='I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
elsif l_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_csr;
x_update_access_flag := 'Y';
'x_update_access_flag: ' || x_update_access_flag);
select 'x'
from as_accesses_all
where salesforce_id = p_salesforce_id
and salesforce_role_code = 'AM'
and customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null;
select 'x'
from as_accesses_all
where access_id = p_access_id
and owner_flag = 'Y';
select 'x'
from as_accesses_all
where lead_id = p_lead_id
and access_id <> p_access_id
and owner_flag = 'Y';
update as_accesses_all
set object_version_number = nvl(object_version_number,0) + 1, owner_flag = 'N'
where lead_id = p_lead_id
and access_id <> p_access_id;
select 'x'
from as_accesses_all
where sales_lead_id = p_sales_lead_id
and access_id <> p_access_id
and owner_flag = 'Y';
update as_accesses_all
set object_version_number = nvl(object_version_number,0) + 1, owner_flag = 'N'
where sales_lead_id = p_sales_lead_id
and access_id <> p_access_id;
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 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_Assign_Id
AND res.category in ('EMPLOYEE','PARTY');
SELECT 'X'
FROM jtf_rs_groups_b grp
WHERE sysdate between GRP.start_date_active and nvl(GRP.end_date_active,sysdate)
AND grp.group_id = p_SALESGROUP_ID ;
SELECT 'X'
FROM jtf_rs_group_members GRPMEM
WHERE resource_id = p_SALESREP_ID
AND group_id = p_SALESGROUP_ID
AND delete_flag = 'N'
AND EXISTS
(SELECT 'X'
FROM jtf_rs_role_relations REL
WHERE role_resource_type = 'RS_GROUP_MEMBER'
AND delete_flag = 'N'
AND sysdate between REL.start_date_active and nvl(REL.end_date_active,sysdate)
AND REL.role_resource_id = GRPMEM.group_member_id
AND role_id IN (SELECT role_id FROM jtf_rs_roles_b WHERE role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')));
SELECT 'X'
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT 'X'
FROM as_leads_all
WHERE lead_id = X_Lead_Id;
SELECT 1
FROM HZ_PARTIES
WHERE party_id = X_Party_Id;
SELECT 1
FROM AS_PARTY_ADDRESSES_V
WHERE party_id = X_Party_Id
AND party_site_id = X_Party_Site_Id;
SELECT 1
FROM AS_PARTY_ADDRESSES_V
WHERE party_id = (SELECT PARTNER_PARTY_ID FROM PV_PARTNER_PROFILES WHERE PARTNER_ID = X_Party_Id)
AND party_site_id = X_Party_Site_Id;
select 1
from as_salesforce_v
where salesforce_id = p_sales_team_rec.salesforce_id
and type = 'PARTNER';
select access_id
from as_accesses
where customer_id = p_sales_team_rec.customer_id
and nvl(lead_id, -99) = nvl(p_sales_team_rec.lead_id, -99)
and nvl(sales_lead_id, -99) = nvl(p_sales_team_rec.sales_lead_id, -99)
and salesforce_id = p_sales_team_rec.salesforce_id
and nvl(sales_group_id, -99) = nvl(p_sales_team_rec.sales_group_id, -99);
l_internal_update_access number;
l_update_access_flag varchar2(1);
select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
from as_leads_all ld, as_statuses_b st
where ld.lead_id = pc_lead_id
and ld.status = st.status_code(+);
select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
from as_sales_leads ld, as_statuses_b st
where ld.sales_lead_id = pc_sales_lead_id
and ld.status_code = st.status_code(+);
select rank.min_score
from as_sales_lead_ranks_b rank, as_sales_leads sl
where sl.sales_lead_id = pc_sales_lead_id
and sl.lead_rank_id = rank.rank_id(+);
select sl.creation_date
from as_sales_leads sl
where sl.sales_lead_id = pc_sales_lead_id;
select access_id
from as_accesses
where customer_id = p_sales_team_rec.customer_id
and nvl(address_id, -99) = nvl(p_sales_team_rec.address_id, -99)
and nvl(lead_id, -99) = nvl(p_sales_team_rec.lead_id, -99)
and nvl(sales_lead_id, -99) = nvl(p_sales_team_rec.sales_lead_id, -99)
and salesforce_id = p_sales_team_rec.salesforce_id
and nvl(sales_group_id, -99) = nvl(p_sales_team_rec.sales_group_id, -99)
and nvl(salesforce_role_code, 'X') = nvl(p_sales_team_rec.salesforce_role_code, 'X');
select source_id
from jtf_rs_resource_extns
where resource_id = pc_resource_id
and category = 'PARTY';
AS_ACCESS_PUB.has_updateCustomerAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_customer_id => p_sales_team_rec.customer_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => l_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 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
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 => p_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_team_rec.lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'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');
AS_ACCESS_PUB.has_updateLeadAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_team_rec.sales_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'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');
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess 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_OWNER_PRIVILEGE');
,x_update_access_flag => l_update_access_flag );
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess 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_OPP_OWNER_PRIVILEGE');
l_internal_update_access := 1;
else l_internal_update_access := 0;
-- else l_internal_update_access := 1;
as_accesses_pkg.insert_row(
X_Rowid => l_rowid,
X_Access_Id => l_access_id,
X_Last_Update_Date => SYSDATE,
X_Last_Updated_By => FND_GLOBAL.User_Id,
X_Creation_Date => SYSDATE,
X_Created_By => FND_GLOBAL.User_Id,
X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
X_Access_Type => 'X',
X_Freeze_Flag => l_sales_team_rec.freeze_flag,
X_Reassign_Flag => l_sales_team_rec.reassign_flag,
X_Team_Leader_Flag => l_sales_team_rec.team_leader_flag,
X_Person_Id => l_sales_team_rec.person_id,
X_Customer_Id => l_sales_team_rec.customer_id,
X_Address_Id => l_sales_team_rec.address_id,
X_Salesforce_id => l_sales_team_rec.salesforce_id,
X_Created_Person_Id => l_sales_team_rec.created_person_id,
X_Partner_Customer_id => l_sales_team_rec.partner_customer_id,
X_Partner_Address_id => l_sales_team_rec.partner_address_id,
X_Lead_Id => l_sales_team_rec.lead_id,
X_Freeze_Date => l_sales_team_rec.freeze_date,
X_Reassign_Reason => l_sales_team_rec.reassign_reason,
X_Reassign_request_date => l_sales_team_rec.reassign_request_date,
X_Reassign_requested_person_id => l_sales_team_rec.reassign_requested_person_id,
X_Attribute_Category => l_sales_team_rec.attribute_category,
X_Attribute1 => l_sales_team_rec.attribute1,
X_Attribute2 => l_sales_team_rec.attribute2,
X_Attribute3 => l_sales_team_rec.attribute3,
X_Attribute4 => l_sales_team_rec.attribute4,
X_Attribute5 => l_sales_team_rec.attribute5,
X_Attribute6 => l_sales_team_rec.attribute6,
X_Attribute7 => l_sales_team_rec.attribute7,
X_Attribute8 => l_sales_team_rec.attribute8,
X_Attribute9 => l_sales_team_rec.attribute9,
X_Attribute10 => l_sales_team_rec.attribute10,
X_Attribute11 => l_sales_team_rec.attribute11,
X_Attribute12 => l_sales_team_rec.attribute12,
X_Attribute13 => l_sales_team_rec.attribute13,
X_Attribute14 => l_sales_team_rec.attribute14,
X_Attribute15 => l_sales_team_rec.attribute15,
X_Sales_group_id => l_sales_team_rec.sales_group_id,
X_Sales_lead_id => l_sales_team_rec.sales_lead_id,
X_Internal_update_access => l_internal_update_access,
X_Partner_Cont_Party_Id => l_sales_team_rec.partner_cont_party_id,
X_owner_flag => l_sales_team_rec.owner_flag,
X_created_by_tap_flag =>l_sales_team_rec.created_by_tap_flag,
X_prm_keep_flag => l_sales_team_rec.prm_keep_flag,
X_Salesforce_Role_Code => l_sales_team_rec.salesforce_role_code,
X_Salesforce_Relationship_Code => l_sales_team_rec.salesforce_relationship_code,
X_open_flag => l_open_flag,
X_lead_rank_score => l_lead_rank_score,
X_object_creation_date => l_object_creation_date,
X_contributor_flag =>l_sales_team_rec.contributor_flag); -- Added for ASNB
update as_leads_all set object_version_number = nvl(object_version_number,0) + 1, owner_salesforce_id = l_sales_team_rec.salesforce_id,
owner_sales_group_id = l_sales_team_rec.sales_group_id
where lead_id = l_sales_team_rec.lead_id;
PROCEDURE Delete_SalesTeam
( 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_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
p_check_access_flag IN VARCHAR2,
p_admin_flag IN VARCHAR2,
p_admin_group_id IN NUMBER,
p_identity_salesforce_id IN NUMBER,
p_sales_team_rec IN SALES_TEAM_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
cursor get_access_info_csr(p_access_id NUMBER) is
select 1
from as_accesses
where access_id = p_access_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_SalesTeam';
l_update_access_flag varchar2(1);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Delete_SalesTeam';
SAVEPOINT DELETE_SALESTEAM_PVT;
AS_ACCESS_PUB.has_updateCustomerAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_customer_id => p_sales_team_rec.customer_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => l_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 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
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 => p_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_team_rec.lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'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_DELETE_PRIVILEGE');
has_updateLeadAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_team_rec.sales_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'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');
then -- only owner can delete owner row
has_oppOwnerAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_lead_id => p_sales_team_rec.lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess 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_OPP_OWNER_PRIVILEGE');
then -- only owner can delete owner row
has_leadOwnerAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_team_rec.sales_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess 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_OWNER_PRIVILEGE');
delete from as_accesses
where access_id = p_sales_team_rec.access_id;
end Delete_SalesTeam;
PROCEDURE Update_SalesTeam
( 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_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
p_check_access_flag IN VARCHAR2,
p_admin_flag IN VARCHAR2,
p_admin_group_id IN NUMBER,
p_identity_salesforce_id IN NUMBER,
p_sales_team_rec IN SALES_TEAM_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_access_id OUT NOCOPY NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_SalesTeam';
l_last_update_date DATE;
l_internal_update_access number;
l_update_access_flag varchar2(1);
select rowid, last_update_date
from as_accesses
where access_id = p_sales_team_rec.access_id
for update of access_id nowait;
select owner_flag
from as_accesses_all
where access_id = p_access_id;
select salesforce_role_code
from as_accesses_all
where access_id = p_access_id;
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Update_SalesTeam';
SAVEPOINT UPDATE_SALESTEAM_PVT;
AS_ACCESS_PUB.has_updateCustomerAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_customer_id => p_sales_team_rec.customer_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => l_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 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
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 => p_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_team_rec.lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'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');
AS_ACCESS_PUB.has_updateLeadAccess
( p_api_version_number => 2.0
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_access_profile_rec => p_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_team_rec.sales_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess 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');
,x_update_access_flag => l_update_access_flag );
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess 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_OPP_OWNER_PRIVILEGE');
fetch get_salesTeam_info_csr into l_rowid, l_last_update_date;
if (l_sales_team_rec.last_update_date is NULL
or l_sales_team_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_last_update_date <> l_sales_team_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');
l_internal_update_access := 1;
else l_internal_update_access := 0;
--else l_internal_update_access := 1;
,x_update_access_flag => l_update_access_flag
);
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess 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_OWNER_PRIVILEGE');
-- or update 'AM' to be not 'AM'
then
if not is_account_manager(p_identity_salesforce_id, l_sales_team_rec.customer_id)
and (nvl(fnd_profile.value('AS_CUST_ACCESS'),'F') <>'F')
-- if login person is not account manager,he can't make other
-- people account manager or not account manager
then
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS','API_NO_ACC_MGR_PRIVILEGE');
as_accesses_pkg.update_row(
X_Rowid => l_rowid,
X_Access_Id => l_sales_team_rec.access_id,
X_Last_Update_Date => SYSDATE,
X_Last_Updated_By => FND_GLOBAL.User_Id,
X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
X_Access_Type => 'X',
X_Freeze_Flag => l_sales_team_rec.freeze_flag,
X_Reassign_Flag => l_sales_team_rec.reassign_flag,
X_Team_Leader_Flag => l_sales_team_rec.team_leader_flag,
X_Person_Id => l_sales_team_rec.person_id,
X_Customer_Id => l_sales_team_rec.customer_id,
X_Address_Id => l_sales_team_rec.address_id,
X_Salesforce_id => l_sales_team_rec.salesforce_id,
X_Created_Person_Id => l_sales_team_rec.created_person_id,
X_Partner_Customer_id => l_sales_team_rec.partner_customer_id,
X_Partner_Address_id => l_sales_team_rec.partner_address_id,
X_Lead_Id => l_sales_team_rec.lead_id,
X_Freeze_Date => l_sales_team_rec.freeze_date,
X_Reassign_Reason => l_sales_team_rec.reassign_reason,
X_Reassign_request_date => l_sales_team_rec.reassign_request_date,
X_Reassign_requested_person_id => l_sales_team_rec.reassign_requested_person_id,
X_Attribute_Category => l_sales_team_rec.attribute_category,
X_Attribute1 => l_sales_team_rec.attribute1,
X_Attribute2 => l_sales_team_rec.attribute2,
X_Attribute3 => l_sales_team_rec.attribute3,
X_Attribute4 => l_sales_team_rec.attribute4,
X_Attribute5 => l_sales_team_rec.attribute5,
X_Attribute6 => l_sales_team_rec.attribute6,
X_Attribute7 => l_sales_team_rec.attribute7,
X_Attribute8 => l_sales_team_rec.attribute8,
X_Attribute9 => l_sales_team_rec.attribute9,
X_Attribute10 => l_sales_team_rec.attribute10,
X_Attribute11 => l_sales_team_rec.attribute11,
X_Attribute12 => l_sales_team_rec.attribute12,
X_Attribute13 => l_sales_team_rec.attribute13,
X_Attribute14 => l_sales_team_rec.attribute14,
X_Attribute15 => l_sales_team_rec.attribute15,
X_Sales_group_id => l_sales_team_rec.sales_group_id,
X_Sales_lead_id => l_sales_team_rec.sales_lead_id,
X_Internal_update_access => l_internal_update_access,
X_Partner_Cont_Party_Id =>l_sales_team_rec.partner_cont_party_id,
X_owner_flag => l_sales_team_rec.owner_flag,
X_created_by_tap_flag =>l_sales_team_rec.created_by_tap_flag,
X_prm_keep_flag => l_sales_team_rec.prm_keep_flag,
X_Salesforce_Role_Code => l_sales_team_rec.salesforce_role_code,
X_Salesforce_Relationship_Code => l_sales_team_rec.salesforce_relationship_code,
X_contributor_flag =>l_sales_team_rec.contributor_flag); -- Added for ASNB
update as_leads_all set object_version_number = nvl(object_version_number,0) + 1, owner_salesforce_id = l_sales_team_rec.salesforce_id,
--owner_sales_group_id = l_sales_team_rec.sales_group_id
owner_sales_group_id = (select sales_group_id from as_accesses_all where access_id = l_sales_team_rec.access_id)
where lead_id = l_sales_team_rec.lead_id;
ROLLBACK TO UPDATE_SALESTEAM_PVT;
FND_MESSAGE.Set_Token('INFO', 'UPDATE_SALESTEAM', FALSE);
end Update_SalesTeam;
select 'x'
from hz_parties
where party_id = p_customer_id
and party_type = 'PERSON';
select 'X'
from as_accesses_all a, hz_parties p,hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and a.salesforce_id = p_identity_salesforce_id
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
and rownum = 1;
select 'X'
from as_accesses_all a, as_rpt_managers_v rm,
hz_parties p,hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and a.salesforce_id = rm.resource_id
and rel.subject_id = p_customer_id
and rm.parent_resource_id = p_resource_id
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
and rownum = 1;
select 'X'
from hz_parties p, hz_relationships rel
where rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
and exists (select 1 from as_accesses_all a ,as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and a.customer_id = rel.object_id
and rm.parent_sales_group_id = p_admin_group_id)
and rownum = 1;
procedure has_updatePersonAccess
( p_access_profile_rec IN as_access_pub.access_profile_rec_type
,p_admin_flag IN VARCHAR2
,p_admin_group_id IN NUMBER
,p_person_id IN NUMBER
,p_customer_id IN NUMBER
,p_identity_salesforce_id IN NUMBER
,x_update_access_flag OUT NOCOPY VARCHAR2
)is
l_tmp varchar2(1);
select 'X'
from as_accesses_all a, hz_parties p,hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and a.salesforce_id = p_identity_salesforce_id
and a.lead_id is null
and a.sales_lead_id is null
and a.team_leader_flag = 'Y'
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
and rownum = 1;
select 'X'
from as_accesses_all a,
hz_parties p, hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and a.lead_id is null
and a.sales_lead_id is null
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and (EXISTS (select 'X'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and ((rm.parent_resource_id = rm.resource_id
and a.team_leader_flag = 'Y')
or (rm.parent_resource_id <> rm.resource_id))));
select 'X'
from as_accesses_all a, hz_parties p, hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and a.lead_id is null
and a.sales_lead_id is null
and a.team_leader_flag = 'Y'
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'X'
from as_accesses_all a, hz_parties p, hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION','PERSON')
and rel.subject_id = p_customer_id
and a.lead_id is null
and a.sales_lead_id is null
and rel.object_table_name = 'HZ_PARTIES'
and rel.subject_table_name = 'HZ_PARTIES'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and ((rm.salesforce_id = p_identity_salesforce_id
and a.team_leader_flag = 'Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a, hz_parties p, hz_relationships rel
where a.customer_id = rel.object_id
and rel.object_id = p.party_id
and p.party_type in ('ORGANIZATION', 'PERSON')
and rel.subject_id = p_customer_id
and a.lead_id is null
and a.sales_lead_id is null
and a.team_leader_flag = 'Y'
and rel.object_table_name = 'HZ_PARTIES'
and rel.subject_table_name = 'HZ_PARTIES'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updatePersonAccess';
AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updatePersonAccess start');
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
then if p_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
elsif p_access_profile_rec.mgr_update_profile_value = 'I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
else if p_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
elsif p_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_csr;
x_update_access_flag := 'Y';
'has_updatePersonAccess end');
end has_updatePersonAccess;
select 'X'
from as_accesses_all
where customer_id = p_customer_id
and salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where customer_id = p_customer_id
and (EXISTS (select 'X'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'X'
from as_accesses_all a
where customer_id = p_customer_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
procedure has_updateCustomerAccess
( p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_access_profile_rec IN as_access_pub.access_profile_rec_type
,p_admin_flag IN VARCHAR2
,p_admin_group_id IN NUMBER
,p_person_id IN NUMBER
,p_customer_id IN NUMBER
,p_check_access_flag IN VARCHAR2
,p_identity_salesforce_id IN NUMBER
,p_partner_cont_party_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_update_access_flag OUT NOCOPY VARCHAR2
)is
l_api_name CONSTANT VARCHAR2(30) := 'has_updateCustomerAccess';
select 'X'
from as_accesses_all
where customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null
and team_leader_flag = 'Y'
and salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null
and (EXISTS (select 'X'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and ((rm.parent_resource_id = rm.resource_id
and a.team_leader_flag = 'Y')
or (rm.parent_resource_id <> rm.resource_id))));
select 'X'
from as_accesses_all a
where a.customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null
and a.team_leader_flag = 'Y'
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'X'
from as_accesses_all a
where customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and ((rm.salesforce_id = p_identity_salesforce_id
and a.team_leader_flag = 'Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a
where a.customer_id = p_customer_id
and lead_id is null
and sales_lead_id is null
and a.team_leader_flag = 'Y'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateCustomerAccess';
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
if l_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
elsif l_access_profile_rec.mgr_update_profile_value = 'I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
if l_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
elsif l_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_Csr;
x_update_access_flag := 'Y';
if x_update_access_flag = 'N' and is_party_person(p_customer_id)
then
has_updatePersonAccess(
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 => p_person_id
,p_customer_id => p_customer_id
,p_identity_salesforce_id => p_identity_salesforce_id
,x_update_access_flag => x_update_access_flag
);
'x_update_access_flag: ' || x_update_access_flag);
end has_updateCustomerAccess;
procedure has_updateLeadAccess
( p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_access_profile_rec IN as_access_pub.access_profile_rec_type
,p_admin_flag IN VARCHAR2
,p_admin_group_id IN NUMBER
,p_person_id IN NUMBER
,p_sales_lead_id IN NUMBER
,p_check_access_flag IN VARCHAR2
,p_identity_salesforce_id IN NUMBER
,p_partner_cont_party_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_update_access_flag OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'has_updateLeadAccess';
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and (rm.parent_resource_id = rm.resource_id
and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
or (rm.parent_resource_id <> rm.resource_id)));
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id));
select 'x'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and (rm.salesforce_id = p_identity_salesforce_id
and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and lead.sales_lead_id = p_sales_lead_id;
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and lead.sales_lead_id = p_sales_lead_id;
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateLeadAccess';
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
if l_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.mgr_update_profile_value = 'I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
elsif l_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_csr;
x_update_access_flag := 'Y';
'x_update_access_flag: ' || x_update_access_flag);
end has_updateLeadAccess;
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id);
select 'x'
from as_accesses_all a
where a.sales_lead_id = p_sales_lead_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and lead.sales_lead_id = p_sales_lead_id;
select 'x'
from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
where lead.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and lead.sales_lead_id = p_sales_lead_id;
procedure has_updateOpportunityAccess
( p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_access_profile_rec IN as_access_pub.access_profile_rec_type
,p_admin_flag IN VARCHAR2
,p_admin_group_id IN NUMBER
,p_person_id IN NUMBER
,p_opportunity_id IN NUMBER
,p_check_access_flag IN VARCHAR2
,p_identity_salesforce_id IN NUMBER
,p_partner_cont_party_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_update_access_flag OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'has_updateOppurtunityAccess';
l_update_access_flag varchar2(1);
select 'X'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and a.team_leader_flag = 'Y'
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
and ((rm.parent_resource_id = rm.resource_id
and a.team_leader_flag = 'Y')
or (rm.parent_resource_id <> rm.resource_id))));
select 'X'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and a.team_leader_flag = 'Y'
and (EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
));
select 'x'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id
and ((rm.salesforce_id = p_identity_salesforce_id
and a.team_leader_flag = 'Y')
or (rm.salesforce_id <> p_identity_salesforce_id)));
select 'x'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and a.team_leader_flag = 'Y'
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
where opp.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and opp.lead_id = p_opportunity_id
and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
select 'x'
from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
where opp.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and opp.lead_id = p_opportunity_id;
select org_id
from as_leads_all
where lead_id = p_opportunity_id;
select 'Y'
from hr_operating_units hr
where hr.organization_id = p_org_id
and mo_global.check_access(hr.organization_id) = 'Y';
l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateOpportunityAccess';
x_update_access_flag := 'N';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
l_update_access_flag := 'N';
l_update_access_flag := 'Y'; -- Access allowed if org_id NULL
l_update_access_flag := NULL; -- Need to check for the org id
if l_update_access_flag IS NULL then
open c_org_access(l_org_id);
fetch c_org_access into l_update_access_flag;
l_update_access_flag := 'N';
if l_update_access_flag = 'N' and resource_access_csr%FOUND then
l_update_access_flag := 'Y';
x_update_access_flag := l_update_access_flag;
x_update_access_flag := 'Y';
if l_access_profile_rec.mgr_update_profile_value = 'U'
then
open manager_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.mgr_update_profile_value = 'I'
then
open mgr_i_access_csr(p_identity_salesforce_id);
x_update_access_flag := 'Y';
end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
elsif l_access_profile_rec.admin_update_profile_value = 'U'
then
open admin_access_csr;
x_update_access_flag := 'Y';
x_update_access_flag := 'Y';
elsif l_access_profile_rec.admin_update_profile_value = 'I'
then
open admin_i_access_csr;
x_update_access_flag := 'Y';
'x_update_access_flag: ' || x_update_access_flag);
end has_updateOpportunityAccess;
select 'X'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and a.salesforce_id = p_identity_salesforce_id;
select 'X'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and EXISTS (select 'x'
from as_rpt_managers_v rm
where a.salesforce_id = rm.resource_id
and rm.parent_resource_id = p_resource_id
and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
);
select 'x'
from as_accesses_all a
where a.lead_id = p_opportunity_id
and EXISTS (select 'x'
from as_rpt_admins_v rm
where a.salesforce_id = rm.salesforce_id
and rm.parent_sales_group_id = p_admin_group_id);
select 'x'
from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
where opp.customer_id = a.customer_id
and a.salesforce_id = rm.resource_id
and a.salesforce_role_code = 'AM'
and rm.parent_resource_id = p_resource_id
and opp.lead_id = p_opportunity_id
and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
select 'x'
from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
where opp.customer_id = a.customer_id
and a.salesforce_id = rm.salesforce_id
and a.salesforce_role_code = 'AM'
and rm.parent_sales_group_id = p_admin_group_id
and opp.lead_id = p_opportunity_id;
select org_id
from as_leads_all
where lead_id = p_opportunity_id;
select 'Y'
from hr_operating_units hr
where hr.organization_id = p_org_id
and mo_global.check_access(hr.organization_id) = 'Y';