[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
SELECT count(1)
FROM ams_list_entries
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y';
g_msg_tbl.delete;
SELECT list_entry_id
FROM ams_list_entries
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y'
ORDER BY randomly_generated_number ;
select user_status_id
from ams_user_Statuses_vl
where system_status_code = p_status_code
and system_status_type = 'AMS_LIST_STATUS';*/
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)),
sum(decode(marked_as_random_flag,'Y',1,0)),
sum(decode(marked_as_duplicate_flag,'Y',1,0)),
sum(decode(manually_entered_flag,
'Y',decode(enabled_flag,'Y','1',0),
0)),
sum(decode(MARKED_AS_SUPPRESSED_FLAG,'Y',1,0)),
sum(decode(MARKED_AS_FATIGUED_FLAG,'Y',1,0)),
sum(decode(TCA_LOAD_STATUS,'ERROR',1,0))
from ams_list_entries
where list_header_id = p_list_header_id ;
update ams_list_entries -- need this when the CG is generated seperately..
set part_of_control_group_flag = 'N',
enabled_flag = 'Y'
where part_of_control_group_flag = 'Y'
and enabled_flag = 'N'
and list_header_id = p_list_header_id;
write_to_act_log('CG option not selected.','LIST',p_list_header_id,'LOW');
l_total_random_rows := nvl(g_list_header_info.ctrl_random_nth_row_selection,0);
write_to_act_log('Control group generation option is percentage. % of rows to be made part of control group is '||g_list_header_info.ctrl_random_pct_row_selection,'LIST',p_list_header_id,'LOW');
l_total_random_rows := floor((g_list_entry_count * nvl(g_list_header_info.ctrl_random_pct_row_selection,0)) / 100);
UPDATE ams_list_entries
SET randomly_generated_number = DBMS_RANDOM.random
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y';
UPDATE ams_list_entries
SET part_of_control_group_flag = 'Y',
enabled_flag = 'N'
WHERE list_header_id = p_list_header_id
AND list_entry_id = g_list_entries_id(i);
update ams_list_headers_all
set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
no_of_rows_active = nvl(l_no_of_rows_active,0),
no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
no_of_rows_in_ctrl_group = nvl(l_no_of_rows_in_ctrl_group,0),
no_of_rows_random = nvl(l_no_of_rows_random,0),
no_of_rows_duplicates = nvl(l_no_of_rows_duplicates,0),
no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0),
no_of_rows_suppressed = nvl(l_no_of_rows_suppressed,0),
no_of_rows_fatigued = nvl(l_no_of_rows_fatigued,0),
tca_failed_records = nvl(l_TCA_FAILED_RECORDS,0),
ctrl_status_code = decode(ctrl_gen_mode,'NONE','DRAFT','AVAILABLE'),
status_code = nvl(status_code_old,status_code),
last_update_date = sysdate,
user_status_id = l_status_id
where list_header_id = p_list_header_id;
update ams_list_headers_all
set status_code_old = null
where list_header_id = p_list_header_id;
if nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'DEFBYCNT' and nvl(g_list_header_info.ctrl_random_nth_row_selection,0) > 0 then
if nvl(l_no_of_rows_in_ctrl_group,0) < nvl(g_list_header_info.ctrl_random_nth_row_selection,0) then
write_to_act_log('Resetting the control group count to '||l_no_of_rows_in_ctrl_group||' since the given CG size is higher than actual size.', 'LIST', p_list_header_id,'HIGH');
update ams_list_headers_all
set ctrl_random_nth_row_selection = l_no_of_rows_in_ctrl_group
where list_header_id = p_list_header_id;
update ams_list_headers_all
set ctrl_status_code = 'FAILED',
status_code = nvl(status_code_old,status_code),
last_update_date = sysdate,
user_status_id = l_status_id
where list_header_id = p_list_header_id;
SELECT list_entry_id
FROM ams_list_entries
WHERE list_header_id = p_list_header_id
AND marked_as_random_flag = 'Y'
AND enabled_flag = 'N'
ORDER BY randomly_generated_number ;
write_to_act_log(g_list_header_info.main_random_pct_row_selection||' % of rows to be generated randomly ','LIST',p_list_header_id,'LOW');
if nvl(g_list_header_info.main_random_pct_row_selection,0) between 1 and 100 then
l_total_random_rows := FLOOR ((g_list_entry_count * g_list_header_info.main_random_pct_row_selection) / 100);
UPDATE ams_list_entries
SET randomly_generated_number = DBMS_RANDOM.random,
marked_as_random_flag = 'Y',
enabled_flag = 'N'
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y';
UPDATE ams_list_entries
SET marked_as_random_flag = 'Y',
enabled_flag = 'Y'
WHERE list_header_id = g_list_header_info.list_header_id
AND list_entry_id = g_list_entries_id(i);
SELECT e.list_entry_id
FROM ams_list_entries e
WHERE e.list_header_id = p_list_header_id
AND e.enabled_flag ='Y';
if (nvl(g_list_header_info.no_of_rows_max_requested,0) > 0 ) then -- already the row selection type is set to MAX.
if (g_list_entry_count <= g_list_header_info.no_of_rows_max_requested) then
write_to_act_log('No of max entries specified is greater than or equal to the no of available entries. No need to reduce the size.'
, 'LIST', p_list_header_id, 'HIGH');
UPDATE ams_list_entries
SET enabled_flag = 'N'
WHERE list_header_id = p_list_header_id
AND list_entry_id = g_list_entries_id(i);
g_msg_tbl.delete;
if nvl(g_list_header_info.row_selection_type,'x') = 'MAX' then
write_to_act_log('Max size option chosen for size reduction','LIST',p_list_header_id,'LOW');
elsif nvl(g_list_header_info.row_selection_type,'x') = 'RANDOM' then
write_to_act_log('Random % option chosen for size reduction','LIST',p_list_header_id,'LOW');
elsif nvl(g_list_header_info.row_selection_type,'x') = 'STANDARD' then
write_to_act_log('All records option chosen. Hence not restricting the size.','LIST',p_list_header_id,'LOW');