The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Backupdate_Header(
p_sales_lead_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR C_line_total IS
SELECT sum(budget_amount) line_total
FROM as_sales_lead_lines
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_sales_leads
SET total_amount = nvl(l_line_total, 0),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
-- creation_Date = SYSDATE, -- solin, for bug 1579950
-- created_by = FND_GLOBAL.USER_ID, -- solin, for bug 1579950
last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE sales_lead_id = p_sales_lead_id;
END Backupdate_Header;
SELECT 'X'
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
THEN
-- validate NOT NULL column
IF (p_sales_lead_id is NULL) or (p_sales_lead_id = FND_API.G_MISS_NUM)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_LEAD_ID');
SELECT distinct 'X'
FROM as_interest_types_b
WHERE Interest_Type_Id = X_Int_Type_Id
-- ffang 012501
and ENABLED_FLAG = 'Y'
and EXPECTED_PURCHASE_FLAG = 'Y';
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
-- ffang 012501
and ENABLED_FLAG = 'Y';
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
-- ffang 012501
and ENABLED_FLAG = 'Y';
SELECT 'X'
FROM mtl_system_items
WHERE inventory_item_id = X_Inventory_Item_Id
and organization_id = X_Organization_Id;
SELECT 'X'
FROM mtl_units_of_measure
WHERE uom_code = X_Uom_Code;
SELECT distinct 'X'
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_id = X_cat_Id
AND (disable_date IS NULL OR disable_date > SYSDATE)
and PURCHASE_interest = 'Y';
SELECT distinct 'X'
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_set_id = X_cat_set_Id
AND (disable_date IS NULL OR disable_date > SYSDATE)
and PURCHASE_interest = 'Y';
AND p_validation_mode=AS_UTILITY_PVT.G_UPDATE)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_ID',
p_token1 => 'COLUMN',
p_token1_value => 'INTEREST_TYPE_ID/INVENTORY_ITEM_ID');
SELECT budget_amount
FROM as_sales_leads
where sales_lead_id = X_Sales_Lead_ID;
SELECT sum (budget_amount)
FROM as_sales_lead_lines
where sales_lead_id = X_Sales_Lead_ID;
l_update_access_flag VARCHAR2(1);
SELECT source_promotion_id, offer_id
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
SELECT category_set_id
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_id = X_cat_Id
AND (disable_date IS NULL OR disable_date > SYSDATE)
and PURCHASE_interest = 'Y';
SELECT P.CATEGORY_ID CAT_ID, P.CATEGORY_SET_ID CAT_SET_ID
FROM MTL_SYSTEM_ITEMS_B_KFV B, MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DEN_HRCHY_PARENTS_V P
WHERE (MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = X_Org_Id
and B.INVENTORY_ITEM_ID = X_prod_Id
AND P.CATEGORY_ID = MIC.CATEGORY_ID
AND P.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND P.LANGUAGE = userenv('LANG')
AND P.PURCHASE_INTEREST = 'Y'
AND (P.DISABLE_DATE is null OR P.DISABLE_DATE > SYSDATE)) ;
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id =>
l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_sales_lead_line_rec.sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
'Calling Line_Insert_Row');
AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Insert_Row(
px_SALES_LEAD_LINE_ID => l_sales_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 => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_SALES_LEAD_ID => l_SALES_LEAD_LINE_rec.SALES_LEAD_ID,
p_STATUS_CODE => l_SALES_LEAD_LINE_rec.STATUS_CODE,
/*p_INTEREST_TYPE_ID => l_SALES_LEAD_LINE_rec.INTEREST_TYPE_ID,
p_PRIMARY_INTEREST_CODE_ID
=> l_SALES_LEAD_LINE_rec.PRIMARY_INTEREST_CODE_ID,
p_SECONDARY_INTEREST_CODE_ID
=> l_SALES_LEAD_LINE_rec.SECONDARY_INTEREST_CODE_ID,
*/
p_CATEGORY_ID
=> l_SALES_LEAD_LINE_rec.CATEGORY_ID,
p_CATEGORY_SET_ID
=> l_category_set_id,
p_INVENTORY_ITEM_ID => l_SALES_LEAD_LINE_rec.INVENTORY_ITEM_ID,
p_ORGANIZATION_ID => l_SALES_LEAD_LINE_rec.ORGANIZATION_ID,
p_UOM_CODE => l_SALES_LEAD_LINE_rec.UOM_CODE,
p_QUANTITY => l_SALES_LEAD_LINE_rec.QUANTITY,
p_BUDGET_AMOUNT => l_SALES_LEAD_LINE_rec.BUDGET_AMOUNT,
p_SOURCE_PROMOTION_ID => l_SALES_LEAD_LINE_rec.SOURCE_PROMOTION_ID,
p_ATTRIBUTE_CATEGORY => l_SALES_LEAD_LINE_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_SALES_LEAD_LINE_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_SALES_LEAD_LINE_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_SALES_LEAD_LINE_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_SALES_LEAD_LINE_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_SALES_LEAD_LINE_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_SALES_LEAD_LINE_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_SALES_LEAD_LINE_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_SALES_LEAD_LINE_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_SALES_LEAD_LINE_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_SALES_LEAD_LINE_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_SALES_LEAD_LINE_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_SALES_LEAD_LINE_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_SALES_LEAD_LINE_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_SALES_LEAD_LINE_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_SALES_LEAD_LINE_rec.ATTRIBUTE15,
p_OFFER_ID => l_SALES_LEAD_LINE_rec.OFFER_ID
-- p_SECURITY_GROUP_ID => l_SALES_LEAD_LINE_rec.SECURITY_GROUP_ID
);
UPDATE as_sales_leads
SET budget_amount = nvl(budget_amount, 0) + l_lines_amount
WHERE sales_lead_id = p_SALES_LEAD_ID;
Backupdate_Header(
p_sales_lead_id => p_sales_lead_id,
x_return_status => x_return_status);
'Private API: Create_lead_line: Backupdate_header fail' );
PROCEDURE Update_sales_lead_lines(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
:= AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type,
X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_sales_lead_line(c_SALES_LEAD_LINE_ID Number) IS
Select LAST_UPDATE_DATE,
BUDGET_AMOUNT
From AS_SALES_LEAD_LINES
WHERE sales_lead_line_id = c_sales_lead_line_id
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_lead_lines';
l_last_update_date DATE;
l_update_amounts NUMBER := 0;
l_update_access_flag VARCHAR2(1);
SAVEPOINT UPDATE_SALES_LEAD_LINES_PVT;
l_update_amounts := 0;
Fetch C_Get_sales_lead_line into l_last_update_date, l_budget_amount;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
IF (l_tar_SALES_LEAD_LINE_rec.last_update_date is NULL or
l_tar_SALES_LEAD_LINE_rec.last_update_date = FND_API.G_MISS_Date)
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
IF (l_tar_SALES_LEAD_LINE_rec.last_update_date <> l_last_update_date)
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
P_SALES_LEAD_LINE_Rec => l_tar_SALES_LEAD_LINE_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_tar_sales_lead_line_rec.sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
'Calling line_Update_Row');
AS_SALES_LEAD_LINES_PKG.Sales_Lead_line_Update_Row(
p_SALES_LEAD_LINE_ID => l_tar_SALES_LEAD_LINE_rec.SALES_LEAD_LINE_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => l_tar_SALES_LEAD_LINE_rec.CREATION_DATE,
p_CREATED_BY => l_tar_SALES_LEAD_LINE_rec.CREATED_BY,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_SALES_LEAD_ID => l_tar_SALES_LEAD_LINE_rec.SALES_LEAD_ID,
p_STATUS_CODE => l_tar_SALES_LEAD_LINE_rec.STATUS_CODE,
/*p_INTEREST_TYPE_ID => l_tar_SALES_LEAD_LINE_rec.INTEREST_TYPE_ID,
p_PRIMARY_INTEREST_CODE_ID =>
l_tar_SALES_LEAD_LINE_rec.PRIMARY_INTEREST_CODE_ID,
p_SECONDARY_INTEREST_CODE_ID =>
l_tar_SALES_LEAD_LINE_rec.SECONDARY_INTEREST_CODE_ID,
*/
p_CATEGORY_ID =>
l_tar_SALES_LEAD_LINE_rec.CATEGORY_ID,
p_CATEGORY_SET_ID =>
l_tar_SALES_LEAD_LINE_rec.CATEGORY_SET_ID,
p_INVENTORY_ITEM_ID => l_tar_SALES_LEAD_LINE_rec.INVENTORY_ITEM_ID,
p_ORGANIZATION_ID => l_tar_SALES_LEAD_LINE_rec.ORGANIZATION_ID,
p_UOM_CODE => l_tar_SALES_LEAD_LINE_rec.UOM_CODE,
p_QUANTITY => l_tar_SALES_LEAD_LINE_rec.QUANTITY,
p_BUDGET_AMOUNT => l_tar_SALES_LEAD_LINE_rec.BUDGET_AMOUNT,
p_SOURCE_PROMOTION_ID =>
l_tar_SALES_LEAD_LINE_rec.SOURCE_PROMOTION_ID,
p_ATTRIBUTE_CATEGORY => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE15,
p_OFFER_ID => l_tar_SALES_LEAD_LINE_rec.OFFER_ID
-- p_SECURITY_GROUP_ID => l_tar_SALES_LEAD_LINE_rec.SECURITY_GROUP_ID
);
l_update_amounts := l_update_amounts
+ NVL(l_tar_SALES_LEAD_LINE_rec.BUDGET_AMOUNT, 0)
- NVL(l_budget_amount, 0);
UPDATE as_sales_leads
SET budget_amount = budget_amount + l_update_amounts
WHERE sales_lead_id = l_sales_lead_id;
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
p_SALES_LEAD_ID => l_SALES_LEAD_ID,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Backupdate_Header(
p_sales_lead_id => l_sales_lead_id,
x_return_status => x_return_status);
END Update_sales_lead_lines;
PROCEDURE Delete_sales_lead_lines(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
:= AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type,
X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_sales_lead_line(c_SALES_LEAD_LINE_ID Number) IS
Select LAST_UPDATE_DATE, BUDGET_AMOUNT
From AS_SALES_LEAD_LINES
WHERE sales_lead_line_id = c_sales_lead_line_id
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_lead_lines';
l_last_update_date DATE;
l_delete_amounts NUMBER;
l_update_access_flag VARCHAR2(1);
SAVEPOINT DELETE_SALES_LEAD_LINES_PVT;
l_delete_amounts := 0;
Fetch C_Get_sales_lead_line into l_last_update_date, l_budget_amount;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_sales_lead_line_rec.sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
'Calling_Line_Delete_Row');
AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Delete_Row(
p_SALES_LEAD_LINE_ID => l_SALES_LEAD_LINE_rec.SALES_LEAD_LINE_ID);
UPDATE as_sales_leads
SET budget_amount = budget_amount - l_delete_amounts
WHERE sales_lead_id = l_sales_lead_line_rec.sales_lead_id;
END Delete_sales_lead_lines;