[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
SELECT 1
FROM as_interest_types_vl
WHERE TRIM(NLS_UPPER(interest_type)) = p_interest_type; -- passing in trimmed value while opening.
SELECT as_interest_types_s.NEXTVAL
FROM DUAL;
SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
WHERE P.CATEGORY_ID = p_prod_cat_id
AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
IF (p_interest_type_rec.last_update_date = FND_API.G_MISS_DATE)
OR TRIM(p_interest_type_rec.last_update_date) IS NULL THEN
l_last_update_date := sysdate;
l_last_update_date := p_interest_type_rec.last_update_date;
IF (p_interest_type_rec.last_updated_by = FND_API.G_MISS_NUM)
OR TRIM(p_interest_type_rec.last_updated_by ) IS NULL THEN
l_last_updated_by := FND_GLOBAL.user_id;
l_last_updated_by := p_interest_type_rec.last_updated_by;
IF (p_interest_type_rec.last_update_login = FND_API.G_MISS_NUM)
OR TRIM(p_interest_type_rec.last_update_login ) IS NULL THEN
l_last_update_login := FND_GLOBAL.login_id;
l_last_update_login := p_interest_type_rec.last_update_login;
-- If g_miss is passed then replace with null before insert
-- otherwise use the value passed for the insert.
-- This is done only for optional fields, for required fields
-- the DB will throw error.
IF p_interest_type_rec.description = FND_API.G_MISS_CHAR
OR TRIM(p_interest_type_rec.description ) IS NULL
THEN
l_description := NULL;
-- Insert a New Interest Type into table
as_interest_types_pkg.insert_row(
l_row_id,
x_interest_type_id,
l_master_enabled_flag,
l_enabled_flag,
l_company_classification_flag,
l_contact_interest_flag,
l_lead_classification_flag,
l_expected_purchase_flag,
l_current_environment_flag,
l_org_id,
l_interest_type,
l_description,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_prod_cat_set_id,
l_prod_cat_id
);
PROCEDURE update_interest_type(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_interest_type_rec IN as_interest_types_pub.interest_type_rec_type
) IS
-- Devlare Local Variables and Cursors
l_api_version NUMBER := p_api_version_number;
l_api_name CONSTANT VARCHAR2(30) := 'update_interest_type';
x_last_update_date DATE;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_current_last_update_date DATE;
l_last_update_login NUMBER;
SELECT master_enabled_flag,
org_id,
description,
enabled_flag,
company_classification_flag,
contact_interest_flag,
lead_classification_flag,
expected_purchase_flag,
current_environment_flag,
interest_type,
last_update_date,
last_updated_by,
last_update_login,
product_cat_set_id,
product_category_id
FROM as_interest_types_vl
WHERE interest_type_id = p_interest_type_id;
SELECT 1
FROM as_interest_types_vl
WHERE interest_type_id <> p_interest_type_id
AND TRIM(NLS_UPPER(interest_type)) = p_interest_type;
CURSOR lock_row_for_update( p_interest_type_id in NUMBER) IS
SELECT last_update_date
FROM as_interest_types_vl
WHERE interest_type_id = p_interest_type_id;
SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
WHERE P.CATEGORY_ID = p_prod_cat_id
AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
l_module CONSTANT VARCHAR2(255) := 'as.plsql.intypub.update_interest_type';
SAVEPOINT update_interest_type_PUB;
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_prod_cat_set_id,
l_prod_cat_id;
IF p_interest_type_rec.last_update_date = FND_API.G_MISS_DATE
OR TRIM(p_interest_type_rec.last_update_date) IS NULL
THEN
x_last_update_date := sysdate ;
x_last_update_date := p_interest_type_rec.last_update_date ;
IF p_interest_type_rec.last_updated_by = FND_API.G_MISS_NUM
OR TRIM(p_interest_type_rec.last_updated_by ) IS NULL
THEN
x_last_updated_by := fnd_global.user_id ;
x_last_updated_by := p_interest_type_rec.last_updated_by ;
IF p_interest_type_rec.last_update_login = FND_API.G_MISS_NUM
OR TRIM(p_interest_type_rec.last_update_login ) IS NULL
THEN
x_last_update_login := fnd_global.login_id ;
x_last_update_login := p_interest_type_rec.last_update_login ;
-- Lock the row for update. Check to see if the fetched value is same still.
-- If they are same then update the record else give a message that the row has been
-- updated by others.
OPEN lock_row_for_update(p_interest_type_rec.interest_type_id);
FETCH lock_row_for_update INTO l_current_last_update_date;
IF lock_row_for_update%NOTFOUND
THEN
CLOSE lock_row_for_update;
IF l_last_update_date <> l_current_last_update_date
THEN
fnd_message.set_name('AS', 'API_RECORD_CHANGED');
-- update Interest Type ID in the table
as_interest_types_pkg.update_row(
p_interest_type_rec.interest_type_id,
x_master_enabled_flag,
x_enabled_flag,
x_company_classification_flag,
x_contact_interest_flag,
x_lead_classification_flag,
x_expected_purchase_flag,
x_current_environment_flag,
x_org_id,
x_interest_type,
x_description,
SYSDATE,
x_last_updated_by,
x_last_update_login,
x_prod_cat_set_id,
x_prod_cat_id);
CLOSE lock_row_for_update ;
END update_interest_type;