The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* for now calling delete procedure in the Generation package because this
is what is needed here. Later that Procedure can be migrated here. */
AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Before delete in Cancel List Gen'|| l_status_code,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
delete_list_entries(p_list_header_id, x_msg_count, x_msg_data, x_return_status);
AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'After delete in Cancel List Gen'|| l_status_code,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'HIGH');
AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', p_list_header_id,'HIGH');
Update_List_Header (p_list_header_id, x_return_status);
AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'List header info updated '|| l_status_code,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing UpdateListHeader. Unable to update List header.', 'LIST', p_list_header_id,'HIGH');
Procedure Delete_List_entries(p_list_header_id in NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status out nocopy VARCHAR2)
is
-- l_gen_type VARCHAR2(200);
l_delete_action varchar2(80);
AMS_LISTGENERATION_PKG.write_to_act_log('Executing delete_list_entries in listcancelgen.', 'LIST', p_list_header_id,'LOW');
select generation_type into l_gen_type
from ams_list_headers_all
where list_header_id = p_list_header_id;
DELETE FROM ams_list_entries
WHERE list_header_id = p_list_header_id;
AMS_LISTGENERATION_PKG.write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', p_list_header_id,'LOW');
If g_remote_gen = 'Y' -- based on remote DS so delete remotely
then
AMS_LISTGENERATION_PKG.write_to_act_log('Calling remote procedure with process type as DELETE_LIST_ENTRIES to delete entries in remote instance', 'LIST', p_list_header_id,'LOW');
'DELETE_LIST_ENTRIES';
AMS_LISTGENERATION_PKG.write_to_act_log('Entries deleted succesfully in remote instance','LIST', p_list_header_id,'LOW');
DELETE FROM ams_list_src_type_usages
WHERE list_header_id = p_list_header_id;
AMS_LISTGENERATION_PKG.write_to_act_log('Procedure delete_list_entries executed successfully.', 'LIST', p_list_header_id,'LOW');
FND_MESSAGE.Set_Token('TEXT', 'Delete List Entries ' || l_delete_action || ' '|| SQLERRM||' '||SQLCODE);
End Delete_list_entries;
Procedure Update_list_header(p_list_header_id in Number,
-- x_msg_count IN NUMBER,
-- x_msg_data IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
AS
Begin
x_return_status := FND_API.G_RET_STS_SUCCESS;
Currently, I don't see the remote list header table getting updated.
Endif
*/
update ams_list_headers_all
set --WORKFLOW_ITEM_KEY = NULL,
status_code = 'DRAFT',
ctrl_status_code = 'DRAFT',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
NO_OF_ROWS_DUPLICATES = null,
NO_OF_ROWS_MIN_REQUESTED = null,
NO_OF_ROWS_MAX_REQUESTED = null,
NO_OF_ROWS_IN_LIST = null,
NO_OF_ROWS_IN_CTRL_GROUP = null,
NO_OF_ROWS_ACTIVE = null,
NO_OF_ROWS_INACTIVE = null,
NO_OF_ROWS_MANUALLY_ENTERED = null,
NO_OF_ROWS_DO_NOT_CALL = null,
NO_OF_ROWS_DO_NOT_MAIL = null,
NO_OF_ROWS_RANDOM = null
where list_header_id = p_list_header_id;
AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing procedure Update_list_header '||sqlcode||' '||sqlerrm, 'LIST', p_list_header_id,'HIGH');
End Update_list_header;
select workflow_item_key
from ams_list_headers_all
where list_header_id = p_list_header_id;
select sum(decode(enabled_flag,'N',0,1)),
sum(decode(enabled_flag,'Y',0,1)),
sum(1),
sum(decode(part_of_control_group_flag,'Y',1,0))
from ams_list_entries
where list_header_id = p_list_header_id ;
select list_type, ctrl_gen_mode
from ams_list_headers_all
where list_header_id = p_list_header_id;
update ams_list_headers_all
set ctrl_conc_job_id = l_request_id,
last_update_date = sysdate
where list_header_id = p_list_header_id;
update ams_list_headers_all
set ctrl_status_code = 'FAILED',
status_code = status_code_old,
status_code_old = null,
last_update_date = sysdate,
user_status_id = 303
where list_header_id = p_list_header_id;
select ctrl_conc_job_id,status_code_old,status_code
from ams_list_headers_all
where list_header_id = p_list_header_id;
select user_status_id
from ams_user_statuses_vl
where system_status_code = p_status_code
and system_status_type = 'AMS_LIST_STATUS';
update ams_list_headers_all
set ctrl_status_code = 'DRAFT',
status_code = nvl(status_code_old,status_code),
last_update_date = sysdate,
user_status_id = l_old_status_id
where list_header_id = p_list_header_id;
select nvl(stypes.remote_flag,'N') ,database_link
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = p_list_header_id;
p_split_preview_count_tbl(i).sp_query := 'SELECT count(1) '||substr(p_split_preview_count_tbl(i).sp_query,instr(upper(p_split_preview_count_tbl(i).sp_query), ' FROM '));