The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from AK_QUERY_OBJECT_LINES
where query_code = param_query_code
order by seq_num;
FND_LOAD_UTIL.OWNER_NAME(l_queryobj_lines_rec.last_updated_by) || '"';
l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
to_char(l_queryobj_lines_rec.last_update_date,
AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = ' ||
nvl(to_char(l_queryobj_lines_rec.last_update_login),'""');
l_databuffer_tbl.delete;
select *
from AK_QUERY_OBJECTS
where query_code = p_query_code;
FND_LOAD_UTIL.OWNER_NAME(l_queryobj_rec.last_updated_by) || '"';
l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
to_char(l_queryobj_rec.last_update_date,
AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = ' ||
nvl(to_char(l_queryobj_rec.last_update_login),'""');
l_databuffer_tbl.delete;
select query_code
from AK_QUERY_OBJECTS
where APPLICATION_ID = appl_id_parm;
FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
select *
from ak_query_object_lines
where query_code = param_query_code
and seq_num = param_seq_num;
select 1
from ak_query_objects
where query_code = param_query_code
and application_id = param_application_id;
select 1
from ak_query_object_lines
where query_code = param_query_code
and seq_num = param_seq_num;
p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
p_last_update_date IN DATE := FND_API.G_MISS_DATE,
p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
p_pass IN NUMBER := 2
) IS
l_api_version_number CONSTANT number := 1.0;
l_last_update_date date;
l_last_update_login number;
l_last_updated_by number;
if (p_last_updated_by <> FND_API.G_MISS_NUM) then
l_last_updated_by := p_last_updated_by;
if (p_last_update_date <> FND_API.G_MISS_DATE) then
l_last_update_date := p_last_update_date;
if (p_last_update_login <> FND_API.G_MISS_NUM) then
l_last_update_login := p_last_update_login;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_db_last_updated_by => null,
p_last_update_date => l_last_update_date,
p_db_last_update_date => null,
p_last_update_login => l_last_update_login,
p_create_or_update => 'CREATE') then
null;
insert into AK_QUERY_OBJECTS (
QUERY_CODE,
APPLICATION_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
p_query_code,
p_application_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login);
p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
p_last_update_date IN DATE := FND_API.G_MISS_DATE,
p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
p_pass IN NUMBER := 2
) IS
l_api_name constant varchar2(30) := 'CREATE_QUERY_OBJECT_LINE';
l_last_update_date date;
l_last_update_login number;
l_last_updated_by number;
if (p_last_updated_by <> FND_API.G_MISS_NUM) then
l_last_updated_by := p_last_updated_by;
if (p_last_update_date <> FND_API.G_MISS_DATE) then
l_last_update_date := p_last_update_date;
if (p_last_update_login <> FND_API.G_MISS_NUM) then
l_last_update_login := p_last_update_login;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_db_last_updated_by => null,
p_last_update_date => l_last_update_date,
p_db_last_update_date => null,
p_last_update_login => l_last_update_login,
p_create_or_update => 'CREATE') then
null;
insert into AK_QUERY_OBJECT_LINES (
QUERY_CODE,
SEQ_NUM,
QUERY_LINE_TYPE,
QUERY_LINE,
LINKED_PARAMETER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
p_query_code,
p_seq_num,
p_query_line_type,
p_query_line,
p_linked_parameter,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login);
PROCEDURE UPDATE_QUERY_OBJECT(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_api_version_number IN NUMBER,
p_init_msg_tbl IN BOOLEAN := FALSE,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_query_code IN VARCHAR2,
p_application_id IN NUMBER,
p_created_by IN NUMBER := FND_API.G_MISS_NUM,
p_creation_date IN DATE := FND_API.G_MISS_DATE,
p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
p_last_update_date IN DATE := FND_API.G_MISS_DATE,
p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
p_pass IN NUMBER := 2
) IS
cursor l_get_query_csr is
select *
from AK_QUERY_OBJECTS
where QUERY_CODE = p_query_code
for update of APPLICATION_ID;
l_api_name CONSTANT varchar2(30) := 'Update_Query_Object';
l_last_update_date date;
l_last_update_login number;
l_last_updated_by number;
savepoint start_update_queryobj;
if (p_last_updated_by <> FND_API.G_MISS_NUM) then
l_last_updated_by := p_last_updated_by;
if (p_last_update_date <> FND_API.G_MISS_DATE) then
l_last_update_date := p_last_update_date;
if (p_last_update_login <> FND_API.G_MISS_NUM) then
l_last_update_login := p_last_update_login;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_db_last_updated_by => l_queryobj_rec.last_updated_by,
p_last_update_date => l_last_update_date,
p_db_last_update_date => l_queryobj_rec.last_update_date,
p_last_update_login => l_last_update_login,
p_create_or_update => 'UPDATE') then
null;
update AK_QUERY_OBJECTS set
application_id = l_queryobj_rec.application_id,
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
where query_code = p_query_code;
FND_MESSAGE.SET_NAME('AK','AK_QUERYOBJ_UPDATE_FAILED');
FND_MESSAGE.SET_NAME('AK','AK_QUERYOBJ_UPDATED');
rollback to start_update_queryobj;
FND_MESSAGE.SET_NAME('AK','AK_QUERYOBJ_NOT_UPDATED');
rollback to start_update_queryobj;
rollback to start_update_queryobj;
END UPDATE_QUERY_OBJECT;
PROCEDURE UPDATE_QUERY_OBJECT_LINE(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_api_version_number IN NUMBER,
p_init_msg_tbl IN BOOLEAN := FALSE,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_query_code IN VARCHAR2,
p_seq_num IN NUMBER,
p_query_line_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_query_line IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_linked_parameter IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_created_by IN NUMBER := FND_API.G_MISS_NUM,
p_creation_date IN DATE := FND_API.G_MISS_DATE,
p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
p_last_update_date IN DATE := FND_API.G_MISS_DATE,
p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
p_pass IN NUMBER := 2
) IS
cursor l_get_query_line_csr is
select *
from AK_QUERY_OBJECT_LINES
where QUERY_CODE = p_query_code
and seq_num = p_seq_num
for update of QUERY_LINE;
l_api_name CONSTANT varchar2(30) := 'Update_Query_Object_Line';
l_last_update_date date;
l_last_update_login number;
l_last_updated_by number;
savepoint start_update_query_line;
if (p_last_updated_by <> FND_API.G_MISS_NUM) then
l_last_updated_by := p_last_updated_by;
if (p_last_update_date <> FND_API.G_MISS_DATE) then
l_last_update_date := p_last_update_date;
if (p_last_update_login <> FND_API.G_MISS_NUM) then
l_last_update_login := p_last_update_login;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_db_last_updated_by => l_line_rec.last_updated_by,
p_last_update_date => l_last_update_date,
p_db_last_update_date => l_line_rec.last_update_date,
p_last_update_login => l_last_update_login,
p_create_or_update => 'UPDATE') then
null;
update AK_QUERY_OBJECT_LINES set
query_line_type = l_line_rec.query_line_type,
query_line = l_line_rec.query_line,
linked_parameter = l_line_rec.linked_parameter,
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
where query_code = p_query_code
and seq_num = p_seq_num;
FND_MESSAGE.SET_NAME('AK','AK_LINE_UPDATE_FAILED');
FND_MESSAGE.SET_NAME('AK','AK_LINE_UPDATED');
rollback to start_update_query_line;
FND_MESSAGE.SET_NAME('AK','AK_LINE_NOT_UPDATED');
rollback to start_update_query_line;
rollback to start_update_query_line;
END UPDATE_QUERY_OBJECT_LINE;
l_update1 DATE;
l_update2 DATE;
(l_token = 'LAST_UPDATED_BY') or
(l_token = 'OWNER') or
(l_token = 'LAST_UPDATE_DATE') or
(l_token = 'LAST_UPDATE_LOGIN') then
l_column := l_token;
elsif (l_column = 'LAST_UPDATED_BY') then
l_query_rec.last_updated_by := to_number(l_token);
l_query_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
elsif (l_column = 'LAST_UPDATE_DATE') then
l_query_rec.last_update_date := to_date(l_token,
AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
elsif (l_column = 'LAST_UPDATE_LOGIN') then
l_query_rec.last_update_login := to_number(l_token);
(l_token = 'LAST_UPDATED_BY') or
(l_token = 'OWNER') or
(l_token = 'LAST_UPDATE_DATE') or
(l_token = 'LAST_UPDATE_LOGIN') then
l_column := l_token;
elsif (l_column = 'LAST_UPDATED_BY') then
l_line_rec.last_updated_by := to_number(l_token);
l_line_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
elsif (l_column = 'LAST_UPDATE_DATE') then
l_line_rec.last_update_date := to_date(l_token,
AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
elsif (l_column = 'LAST_UPDATE_LOGIN') then
l_line_rec.last_update_login := to_number(l_token);
if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
AK_QUERYOBJ_PVT.UPDATE_QUERY_OBJECT (
p_validation_level => p_validation_level,
p_api_version_number => 1.0,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_query_code => l_query_tbl(l_index).query_code,
p_application_id => l_query_tbl(l_index).application_id,
p_created_by => l_query_tbl(l_index).created_by,
p_creation_date => l_query_tbl(l_index).creation_date,
p_last_updated_by => l_query_tbl(l_index).last_updated_by,
p_last_update_date => l_query_tbl(l_index).last_update_date,
p_last_update_login => l_query_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);
elsif (AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE) then
-- do not update customized data
select aqo.last_updated_by, aqo.last_update_date
into l_user_id1, l_update1
from ak_query_objects aqo
where aqo.query_code = l_query_tbl(l_index).query_code
and aqo.application_id = l_query_tbl(l_index).application_id;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_query_tbl(l_index).created_by,
p_creation_date => l_query_tbl(l_index).creation_date,
p_last_updated_by => l_query_tbl(l_index).last_updated_by,
p_db_last_updated_by => l_user_id1,
p_last_update_date => l_query_tbl(l_index).last_update_date,
p_db_last_update_date => l_update1,
p_last_update_login => l_query_tbl(l_index).last_update_login,
p_create_or_update => 'UPDATE') then
AK_QUERYOBJ_PVT.UPDATE_QUERY_OBJECT (
p_validation_level => p_validation_level,
p_api_version_number => 1.0,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_query_code => l_query_tbl(l_index).query_code,
p_application_id => l_query_tbl(l_index).application_id,
p_created_by => l_query_tbl(l_index).created_by,
p_creation_date => l_query_tbl(l_index).creation_date,
p_last_updated_by => l_query_tbl(l_index).last_updated_by,
p_last_update_date => l_query_tbl(l_index).last_update_date,
p_last_update_login => l_query_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);
end if; -- /* if G_UPDATE_MODE G_NC_UPDATE_MODE*/
p_last_updated_by => l_query_tbl(l_index).last_updated_by,
p_last_update_date => l_query_tbl(l_index).last_update_date,
p_last_update_login => l_query_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);
if (AK_UPLOAD_GRP.G_UPDATE_MODE) then
AK_QUERYOBJ_PVT.UPDATE_QUERY_OBJECT_LINE (
p_validation_level => p_validation_level,
p_api_version_number => 1.0,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_query_code => l_line_tbl(l_index).query_code,
p_seq_num =>l_line_tbl(l_index).seq_num,
p_query_line_type => l_line_tbl(l_index).query_line_type,
p_query_line => l_line_tbl(l_index).query_line,
p_linked_parameter => l_line_tbl(l_index).linked_parameter,
p_created_by => l_line_tbl(l_index).created_by,
p_creation_date => l_line_tbl(l_index).creation_date,
p_last_updated_by => l_line_tbl(l_index).last_updated_by,
p_last_update_date => l_line_tbl(l_index).last_update_date,
p_last_update_login => l_line_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);
-- update non-customized data only
elsif ( AK_UPLOAD_GRP.G_NO_CUSTOM_UPDATE ) then
select aqol.last_updated_by, aqol.last_update_date
into l_user_id1, l_update1
from ak_query_object_lines aqol
where aqol.query_code = l_line_tbl(l_index).query_code
and aqol.seq_num = l_line_tbl(l_index).seq_num;
if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
p_loader_timestamp => p_loader_timestamp,
p_created_by => l_line_tbl(l_index).created_by,
p_creation_date => l_line_tbl(l_index).creation_date,
p_last_updated_by => l_line_tbl(l_index).last_updated_by,
p_db_last_updated_by => l_user_id1,
p_last_update_date => l_line_tbl(l_index).last_update_date,
p_db_last_update_date => l_update1,
p_last_update_login => l_line_tbl(l_index).last_update_login,
p_create_or_update => 'UPDATE') then
AK_QUERYOBJ_PVT.UPDATE_QUERY_OBJECT_LINE (
p_validation_level => p_validation_level,
p_api_version_number => 1.0,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_query_code => l_line_tbl(l_index).query_code,
p_seq_num =>l_line_tbl(l_index).seq_num,
p_query_line_type => l_line_tbl(l_index).query_line_type,
p_query_line => l_line_tbl(l_index).query_line,
p_linked_parameter => l_line_tbl(l_index).linked_parameter,
p_created_by => l_line_tbl(l_index).created_by,
p_creation_date => l_line_tbl(l_index).creation_date,
p_last_updated_by => l_line_tbl(l_index).last_updated_by,
p_last_update_date => l_line_tbl(l_index).last_update_date,
p_last_update_login => l_line_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);
end if; -- /* if G_UPDATE_MODE G_NO_CUSTOM_UPDATE */
p_last_updated_by => l_line_tbl(l_index).last_updated_by,
p_last_update_date => l_line_tbl(l_index).last_update_date,
p_last_update_login => l_line_tbl(l_index).last_update_login,
p_loader_timestamp => p_loader_timestamp,
p_pass => p_pass
);