The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM HZ_PARTIES CUST
WHERE CUST.PARTY_TYPE in ('PERSON', 'ORGANIZATION', 'PARTY_RELATIONSHIP')
AND CUST.STATUS IN ('A','I')
AND party_id = X_Party_Id;
l_interests_inserted NUMBER := 0; -- Number of successful inserts
l_update_access_flag VARCHAR2(1);
select 1
from AS_INTERESTS_ALL
where customer_id = p_customer_id
and interest_use_code = 'CONTACT_INTEREST'
and product_category_id = p_product_category_id
and product_cat_set_id = p_product_cat_set_id;
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_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 => l_msg_count
,x_msg_data => l_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_updateCustomerAccess 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_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_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => Null
,x_return_status => p_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_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');
FND_MESSAGE.Set_Name('AS', 'Inserting Record');
AS_INTERESTS_PKG.Insert_Row ( X_Rowid => l_rowid,
X_Interest_Id => l_interest_tbl(l_curr_row).interest_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_Request_Id => FND_GLOBAL.Conc_Request_Id,
X_Program_Application_Id => FND_GLOBAL.Prog_Appl_Id,
X_Program_Id => FND_GLOBAL.Conc_Program_Id,
X_Program_Update_Date => SYSDATE,
X_Interest_Use_Code => p_interest_use_code,
X_Interest_Type_Id => l_interest_tbl(l_curr_row).Interest_Type_Id,
X_Contact_Id => p_contact_id,
X_Customer_Id => p_customer_id,
X_Address_Id => p_address_id,
X_Lead_Id => p_lead_id,
X_Primary_Interest_Code_Id => l_interest_tbl(l_curr_row).Primary_Interest_Code_Id,
X_Secondary_Interest_Code_Id => l_interest_tbl(l_curr_row).Secondary_Interest_Code_Id,
X_Status_Code => l_interest_tbl(l_curr_row).Status_Code,
X_Description => l_interest_tbl(l_curr_row).description,
X_Attribute_Category => l_interest_tbl(l_curr_row).Attribute_Category,
X_Attribute1 => l_interest_tbl(l_curr_row).Attribute1,
X_Attribute2 => l_interest_tbl(l_curr_row).Attribute2,
X_Attribute3 => l_interest_tbl(l_curr_row).Attribute3,
X_Attribute4 => l_interest_tbl(l_curr_row).Attribute4,
X_Attribute5 => l_interest_tbl(l_curr_row).Attribute5,
X_Attribute6 => l_interest_tbl(l_curr_row).Attribute6,
X_Attribute7 => l_interest_tbl(l_curr_row).Attribute7,
X_Attribute8 => l_interest_tbl(l_curr_row).Attribute8,
X_Attribute9 => l_interest_tbl(l_curr_row).Attribute9,
X_Attribute10 => l_interest_tbl(l_curr_row).Attribute10,
X_Attribute11 => l_interest_tbl(l_curr_row).Attribute11,
X_Attribute12 => l_interest_tbl(l_curr_row).Attribute12,
X_Attribute13 => l_interest_tbl(l_curr_row).Attribute13,
X_Attribute14 => l_interest_tbl(l_curr_row).Attribute14,
X_Attribute15 => l_interest_tbl(l_curr_row).Attribute15,
X_Product_Category_Id => l_interest_tbl(l_curr_row).Product_Category_Id,
X_Product_Cat_Set_Id => l_interest_tbl(l_curr_row).Product_Cat_Set_Id
);
l_interests_inserted := l_interests_inserted + 1;
l_interests_inserted > 0
THEN
FND_MESSAGE.Set_Name('AS', 'API_ROWS_INSERTED');
FND_MESSAGE.Set_Token('NUMBER', to_char(l_interests_inserted), FALSE);
PROCEDURE Update_Interest
( 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_identity_salesforce_id IN NUMBER,
p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
p_interest_use_code IN VARCHAR2,
p_check_access_flag IN VARCHAR2,
p_admin_flag IN VARCHAR2,
p_admin_group_id IN NUMBER,
p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_interest_id OUT NOCOPY NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_Interest';
l_last_update_date DATE;
l_update_access_flag VARCHAR2(1);
SELECT rowid, last_update_date
FROM as_interests
WHERE interest_id = p_interest_rec.interest_id
FOR UPDATE of interest_Id NOWAIT;
select 1
from AS_INTERESTS_ALL
where customer_id = p_customer_id
and interest_use_code = 'CONTACT_INTEREST'
and product_category_id = p_product_category_id
and product_cat_set_id = p_product_cat_set_id
and interest_id <> p_interest_id;
l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Update_Interest';
SAVEPOINT UPDATE_INTEREST_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_interest_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 => l_msg_count
,x_msg_data => l_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_updateCustomerAccess 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_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_interest_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 => l_msg_count
,x_msg_data => l_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');
fetch get_interest_info_csr into l_rowid, l_last_update_date;
if (p_interest_rec.last_update_date is NULL
or p_interest_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 <> p_interest_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');
AS_INTERESTS_PKG.Update_Row ( X_Rowid => l_rowid,
X_Interest_Id => p_interest_rec.interest_id,
X_Last_Update_Date => SYSDATE,
X_Last_Updated_By => FND_GLOBAL.USER_ID,
X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
X_Request_Id => FND_GLOBAL.Conc_Request_Id,
X_Program_Application_Id => FND_GLOBAL.Prog_Appl_Id,
X_Program_Id => FND_GLOBAL.Conc_Program_Id,
X_Program_Update_Date => SYSDATE,
X_Interest_Use_Code => p_interest_use_code,
X_Interest_Type_Id => p_interest_rec.Interest_Type_Id,
X_Contact_Id => p_interest_rec.contact_id,
X_Customer_Id => p_interest_rec.customer_id,
X_Address_Id => p_interest_rec.address_id,
X_Lead_Id => p_interest_rec.lead_id,
X_Primary_Interest_Code_Id => p_interest_rec.Primary_Interest_Code_Id,
X_Secondary_Interest_Code_Id => p_interest_rec.Secondary_Interest_Code_Id,
X_Status_Code => p_interest_rec.Status_Code,
X_Description => p_interest_rec.description,
X_Attribute_Category => p_interest_rec.Attribute_Category,
X_Attribute1 => p_interest_rec.Attribute1,
X_Attribute2 => p_interest_rec.Attribute2,
X_Attribute3 => p_interest_rec.Attribute3,
X_Attribute4 => p_interest_rec.Attribute4,
X_Attribute5 => p_interest_rec.Attribute5,
X_Attribute6 => p_interest_rec.Attribute6,
X_Attribute7 => p_interest_rec.Attribute7,
X_Attribute8 => p_interest_rec.Attribute8,
X_Attribute9 => p_interest_rec.Attribute9,
X_Attribute10 => p_interest_rec.Attribute10,
X_Attribute11 => p_interest_rec.Attribute11,
X_Attribute12 => p_interest_rec.Attribute12,
X_Attribute13 => p_interest_rec.Attribute13,
X_Attribute14 => p_interest_rec.Attribute14,
X_Attribute15 => p_interest_rec.Attribute15,
X_Product_Category_Id => p_interest_rec.Product_Category_Id,
X_Product_Cat_Set_Id => p_interest_rec.Product_Cat_Set_Id
);
ROLLBACK TO UPDATE_INTEREST_PVT;
FND_MESSAGE.Set_Token('INFO', 'UPDATE_INTEREST', FALSE);
END Update_Interest;
PROCEDURE Delete_Interest
( 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_identity_salesforce_id IN NUMBER,
p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
p_interest_use_code IN VARCHAR2,
p_check_access_flag in varchar2,
p_admin_flag in varchar2,
p_admin_group_id in number,
p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
cursor get_interest_info_csr(p_interest_id NUMBER) is
select 1
from as_interests_all
where interest_id = p_interest_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Interest';
l_update_access_flag varchar2(1);
l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Delete_Interest';
SAVEPOINT DELETE_INTEREST_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_interest_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_interest_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');
delete from as_interests_all
where interest_id = p_interest_rec.interest_id;
end Delete_Interest;
SELECT PRODUCT_CATEGORY_ID, PRODUCT_CAT_SET_ID
FROM AS_INTERESTS_ALL
WHERE INTEREST_ID = l_interest_id;
SELECT 'X'
FROM as_interest_types_b
WHERE Interest_Type_Id = X_Int_Type_Id;
SELECT 'X'
FROM As_Interest_Codes_B Pic
WHERE Pic.Interest_Type_Id = X_Int_Type_Id
and Pic.Interest_Code_Id = X_Int_Code_Id
and Pic.Parent_Interest_Code_Id Is Null;
SELECT 'X'
FROM As_Interest_Codes_B Sic
WHERE Sic.Interest_Type_Id = X_Int_Type_Id
And Sic.Interest_Code_Id = X_Sec_Int_Code_Id
And Sic.Parent_Interest_Code_Id = X_Int_Code_Id;
SELECT 'X'
FROM As_Interest_Statuses
WHERE Interest_Type_Id = X_Int_Type_Id
And Interest_Status_Code = X_Int_Status_Code;
SELECT 'X'
FROM As_Interest_Statuses
WHERE Product_Category_Id = X_Product_Category_Id
And Product_Cat_Set_Id = X_Product_Cat_Set_Id
And Interest_Status_Code = X_Int_Status_Code;