The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = p_pool_rec.CURRENCY_CODE;
SELECT 'x'
FROM PER_EMPLOYEES_CURRENT_X
WHERE EMPLOYEE_ID= p_pool_rec.CONTACT_PERSON_ID;
SELECT 'x'
FROM OKE_PROGRAMS
WHERE PROGRAM_ID= p_pool_rec.PROGRAM_ID
AND SYSDATE BETWEEN START_DATE
AND NVL(END_DATE+1 , SYSDATE )
;
IF l_pool_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
l_pool_rec.LAST_UPDATED_BY := NULL;
IF l_pool_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
l_pool_rec.LAST_UPDATE_LOGIN := NULL;
IF l_pool_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
l_pool_rec.LAST_UPDATE_DATE := NULL;
SELECT
FUNDING_POOL_ID,
NAME,
DESCRIPTION,
CURRENCY_CODE,
CONTACT_PERSON_ID,
PROGRAM_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM OKE_FUNDING_POOLS a
WHERE (a.funding_pool_id = p_funding_pool_id);
l_pool_rec.LAST_UPDATE_DATE ,
l_pool_rec.LAST_UPDATED_BY ,
l_pool_rec.LAST_UPDATE_LOGIN ,
l_pool_rec.ATTRIBUTE_CATEGORY ,
l_pool_rec.ATTRIBUTE1 ,
l_pool_rec.ATTRIBUTE2 ,
l_pool_rec.ATTRIBUTE3 ,
l_pool_rec.ATTRIBUTE4 ,
l_pool_rec.ATTRIBUTE5 ,
l_pool_rec.ATTRIBUTE6 ,
l_pool_rec.ATTRIBUTE7 ,
l_pool_rec.ATTRIBUTE8 ,
l_pool_rec.ATTRIBUTE9 ,
l_pool_rec.ATTRIBUTE10 ,
l_pool_rec.ATTRIBUTE11 ,
l_pool_rec.ATTRIBUTE12 ,
l_pool_rec.ATTRIBUTE13 ,
l_pool_rec.ATTRIBUTE14 ,
l_pool_rec.ATTRIBUTE15 ;
OKE_API.set_message(G_APP_NAME,G_FORM_RECORD_DELETED);
-- row level insert
-- will create using nextVal from sequence oke_funding_pools_s
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_rec IN pool_rec_type,
x_pool_rec OUT NOCOPY pool_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
l_pool_rec.LAST_UPDATE_DATE := SYSDATE;
l_pool_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_pool_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
BEGIN -- insert
--oke_debug.debug('start call oke_pool_pvt.insert_row');
SELECT OKE_FUNDING_POOLS_S.nextval INTO l_seq FROM dual;
OKE_FUNDING_POOLS_PKG.Insert_Row
(l_row_id,
l_seq,
l_def_pool_rec.NAME,
l_def_pool_rec.DESCRIPTION,
l_def_pool_rec.CURRENCY_CODE,
l_def_pool_rec.CONTACT_PERSON_ID,
l_def_pool_rec.PROGRAM_ID,
l_def_pool_rec.LAST_UPDATE_DATE ,
l_def_pool_rec.LAST_UPDATED_BY ,
l_def_pool_rec.CREATION_DATE ,
l_def_pool_rec.CREATED_BY ,
l_def_pool_rec.LAST_UPDATE_LOGIN ,
l_def_pool_rec.ATTRIBUTE_CATEGORY ,
l_def_pool_rec.ATTRIBUTE1 ,
l_def_pool_rec.ATTRIBUTE2 ,
l_def_pool_rec.ATTRIBUTE3 ,
l_def_pool_rec.ATTRIBUTE4 ,
l_def_pool_rec.ATTRIBUTE5 ,
l_def_pool_rec.ATTRIBUTE6 ,
l_def_pool_rec.ATTRIBUTE7 ,
l_def_pool_rec.ATTRIBUTE8 ,
l_def_pool_rec.ATTRIBUTE9 ,
l_def_pool_rec.ATTRIBUTE10 ,
l_def_pool_rec.ATTRIBUTE11 ,
l_def_pool_rec.ATTRIBUTE12 ,
l_def_pool_rec.ATTRIBUTE13 ,
l_def_pool_rec.ATTRIBUTE14 ,
l_def_pool_rec.ATTRIBUTE15
);
END insert_row; -- row level
-- table level insert
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_tbl IN pool_tbl_type,
x_pool_tbl OUT NOCOPY pool_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
insert_row (
p_api_version => p_api_version,
p_init_msg_list => OKE_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pool_rec => p_pool_tbl(i),
x_pool_rec => x_pool_tbl(i));
END insert_row; -- table level
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_rec IN pool_rec_type,
x_pool_rec OUT NOCOPY pool_rec_type) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
select funding_pool_id
from oke_funding_pools
where funding_pool_id=p_pool_rec.funding_pool_id;
l_pool_rec.LAST_UPDATE_DATE := SYSDATE;
l_pool_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_pool_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IF x_pool_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
x_pool_rec.LAST_UPDATE_DATE := l_pool_rec.LAST_UPDATE_DATE;
IF x_pool_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
x_pool_rec.LAST_UPDATED_BY := l_pool_rec.LAST_UPDATED_BY ;
IF x_pool_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
x_pool_rec.LAST_UPDATE_LOGIN := l_pool_rec.LAST_UPDATE_LOGIN;
BEGIN -- update row
--oke_debug.debug('start call oke_pool_pvt.update_row');
OKE_FUNDING_POOLS_PKG.update_row(
l_def_pool_rec.FUNDING_POOL_ID,
l_def_pool_rec.NAME,
l_def_pool_rec.DESCRIPTION,
l_def_pool_rec.CONTACT_PERSON_ID,
l_def_pool_rec.PROGRAM_ID,
l_def_pool_rec.LAST_UPDATE_DATE,
l_def_pool_rec.LAST_UPDATED_BY,
l_def_pool_rec.LAST_UPDATE_LOGIN,
l_def_pool_rec.ATTRIBUTE_CATEGORY,
l_def_pool_rec.ATTRIBUTE1,
l_def_pool_rec.ATTRIBUTE2,
l_def_pool_rec.ATTRIBUTE3,
l_def_pool_rec.ATTRIBUTE4,
l_def_pool_rec.ATTRIBUTE5,
l_def_pool_rec.ATTRIBUTE6,
l_def_pool_rec.ATTRIBUTE7,
l_def_pool_rec.ATTRIBUTE8,
l_def_pool_rec.ATTRIBUTE9,
l_def_pool_rec.ATTRIBUTE10,
l_def_pool_rec.ATTRIBUTE11,
l_def_pool_rec.ATTRIBUTE12,
l_def_pool_rec.ATTRIBUTE13,
l_def_pool_rec.ATTRIBUTE14,
l_def_pool_rec.ATTRIBUTE15 );
END update_row; -- row level update
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_tbl IN pool_tbl_type,
x_pool_tbl OUT NOCOPY pool_tbl_type) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => OKE_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pool_rec => p_pool_tbl(i),
x_pool_rec => x_pool_tbl(i));
END update_row; -- table level update
-- deletes by the funding_pool_id
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_rec IN pool_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
select funding_pool_id
from oke_funding_pools
where funding_pool_id=p_pool_rec.funding_pool_id;
DELETE FROM OKE_FUNDING_POOLS
WHERE FUNDING_POOL_ID = p_pool_rec.FUNDING_POOL_ID;
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pool_tbl IN pool_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => OKE_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pool_rec => p_pool_tbl(i));
END delete_row; -- table level delete
SELECT funding_pool_id FROM oke_funding_pools a
WHERE
a.funding_pool_id = p.funding_pool_id
FOR UPDATE NOWAIT;
OKE_API.set_message(G_APP_NAME,G_FORM_RECORD_DELETED);