[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE;
l_current_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
SELECT 1
FROM as_interest_codes_vl
WHERE interest_code_id = p_parent_interest_code_id
AND interest_code_id IS NOT NULL;
SELECT 1
FROM as_interest_types_vl
WHERE interest_type_id = p_interest_type_id
AND interest_type_id IS NOT NULL;
SELECT 1
FROM as_interest_codes_vl
WHERE TRIM(NLS_UPPER(code)) = p_code -- passing in trimmed value while opening.
AND interest_type_id = p_interest_type_id
AND parent_interest_code_id IS NULL ;
SELECT 1
FROM as_interest_codes_vl
WHERE TRIM(NLS_UPPER(code)) = p_code -- passing in trimmed value while opening.
AND interest_type_id = p_interest_type_id
AND parent_interest_code_id = p_parent_interest_code_id ;
SELECT as_interest_codes_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;
FND_MSG_PUB.DELETE_MSG;
IF p_interest_code_rec.last_update_date = FND_API.G_MISS_DATE
OR TRIM(p_interest_code_rec.last_update_date) IS NULL
THEN
l_last_update_date := SYSDATE;
l_last_update_date := p_interest_code_rec.last_update_date;
IF p_interest_code_rec.last_updated_by = FND_API.G_MISS_NUM
OR TRIM(p_interest_code_rec.last_updated_by) IS NULL
THEN
l_last_updated_by := FND_GLOBAL.user_id;
l_last_updated_by := p_interest_code_rec.last_updated_by;
IF p_interest_code_rec.last_update_login = FND_API.G_MISS_NUM
OR TRIM(p_interest_code_rec.last_update_login) IS NULL
THEN
l_last_update_login := FND_GLOBAL.login_id;
l_last_update_login := p_interest_code_rec.last_update_login;
-- replace with NULL before insert. Else use the value passed for insert.
-- Only optional fields needs to be checked for Insert and replace with
-- NULL for required fields the DB will throw error.
IF p_interest_code_rec.parent_interest_code_id = FND_API.G_MISS_NUM
THEN
l_parent_interest_code_id := NULL;
-- Get the sequence number before inserting.
OPEN as_int_code_nextval_cur;
-- Insert a New Interest Code into table
as_interest_codes_pkg.insert_row(
x_row_id,
x_interest_code_id,
p_interest_code_rec.interest_type_id,
p_interest_code_rec.master_enabled_flag,
l_parent_interest_code_id,
l_category_id,
l_category_set_id,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_pf_item_id,
l_pf_organization_id,
l_price,
l_currency_code,
TRIM(l_code),
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_code(
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_code_rec IN interest_code_rec_type
) IS
-- Declare Local Variable and Cursors
l_api_version NUMBER := p_api_version_number;
l_api_name CONSTANT VARCHAR2(30) := 'update_interest_code';
x_last_update_date DATE;
x_last_update_login NUMBER;
x_last_updated_by NUMBER;
l_last_update_date DATE;
l_current_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
CURSOR fetch_db_value_cur(p_interest_code_id IN NUMBER) IS SELECT
master_enabled_flag,
parent_interest_code_id,
category_id,
category_set_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pf_item_id,
pf_organization_id,
price,
currency_code,
code,
description,
last_update_date,
last_updated_by,
last_update_login,
product_cat_set_id,
product_category_id
FROM AS_INTEREST_CODES_vl
WHERE interest_code_id = p_interest_code_id;
SELECT 1
FROM AS_INTEREST_TYPES_B
WHERE INTEREST_TYPE_ID = p_interest_type_id
AND INTEREST_TYPE_ID IS NOT NULL;
SELECT 1
FROM AS_INTEREST_CODES_B
WHERE INTEREST_CODE_ID = p_parent_interest_code_id
AND INTEREST_CODE_ID IS NOT NULL;
SELECT 1
FROM as_interest_codes_vl
WHERE interest_code_id <> p_interest_code_id
AND TRIM(NLS_UPPER(code)) = p_code ---- -- passing in trimmed value while opening.
AND interest_type_id = p_interest_type_id
AND parent_interest_code_id IS NULL ;
SELECT 1
FROM as_interest_codes_vl
WHERE interest_code_id <> p_interest_code_id
AND TRIM(NLS_UPPER(code)) = p_code ----- -- passing in trimmed value while opening.
AND interest_type_id = p_interest_type_id
AND parent_interest_code_id = p_parent_interest_code_id ;
CURSOR lock_row_for_update(p_interest_code_id in number) IS
SELECT last_update_date
FROM as_interest_codes_vl
WHERE interest_code_id = p_interest_code_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.intc.update_interest_code';
SAVEPOINT update_interest_code_PUB;
FND_MSG_PUB.DELETE_MSG;
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_prod_cat_set_id,
l_prod_cat_id;
IF p_interest_code_rec.last_update_date = FND_API.G_MISS_DATE
OR TRIM(p_interest_code_rec.last_update_date) IS NULL
THEN
x_last_update_date := SYSDATE;
x_last_update_date := p_interest_code_rec.last_update_date;
IF p_interest_code_rec.last_updated_by = FND_API.G_MISS_NUM
OR TRIM(p_interest_code_rec.last_updated_by) IS NULL
THEN
x_last_updated_by := FND_GLOBAL.user_id;
x_last_updated_by := p_interest_code_rec.last_updated_by;
IF p_interest_code_rec.last_update_login = FND_API.G_MISS_NUM
OR TRIM(p_interest_code_rec.last_update_login) IS NULL
THEN
x_last_update_login := FND_GLOBAL.login_id;
x_last_update_login := p_interest_code_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(TRIM(NLS_UPPER(p_interest_code_rec.interest_code_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');
as_interest_codes_pkg.update_row(
p_interest_code_rec.interest_code_id,
p_interest_code_rec.interest_type_id,
x_master_enabled_flag,
x_parent_interest_code_id,
x_category_id,
x_category_set_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_pf_item_id,
x_pf_organization_id,
x_price,
x_currency_code,
x_code,
x_description,
x_last_update_date,
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_code;