[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT import_list_header_id, object_version_number,
creation_date, expiry_date, usage
FROM ams_imp_list_headers_all
WHERE rented_list_flag = 'R'
and status_code <> 'PURGED';
SELECT DISTINCT b.list_header_id
FROM ams_list_select_actions a, ams_list_headers_all b
WHERE a.incl_object_id = c_imp_header_id
AND a.arc_incl_object_from = 'IMPH'
AND a.action_used_by_id = b.list_header_id
AND (b.list_type <> 'TARGET' OR b.status_code <> 'LOCKED');
SELECT DISTINCT b.list_header_id
FROM ams_list_select_actions a, ams_list_headers_all b
WHERE a.incl_object_id = c_imp_header_id
AND a.arc_incl_object_from = 'IMPH'
AND a.action_used_by_id = b.list_header_id;
SELECT a.list_header_id, a.list_entry_id
FROM ams_list_entries a, ams_imp_source_lines b
WHERE a.list_header_id = c_list_header_id
AND a.imp_source_line_id = b.import_source_line_id
AND b.import_list_header_id = c_imp_header_id
ORDER BY a.list_header_id;
SELECT a.list_header_id, a.list_entry_id
FROM ams_list_entries a, ams_imp_source_lines b
WHERE a.list_header_id = c_list_header_id
AND a.imp_source_line_id = b.import_source_line_id
AND b.import_list_header_id = c_imp_header_id
AND b.current_usage >= c_usage
ORDER BY a.list_header_id;
SELECT current_usage
FROM ams_imp_source_lines
WHERE import_list_header_id = p_imp_header_id;
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_code = p_lookup_code
AND system_status_type = 'AMS_IMPORT_STATUS';
AMS_Utility_PVT.debug_message(l_api_name||': delete from source lines');
DELETE FROM ams_imp_source_lines
WHERE import_list_header_id = l_import_list_header_id;
AMS_Utility_PVT.debug_message(l_api_name||': delete from list entries');
AMS_Utility_PVT.debug_message(l_api_name||': bulk update on entries');
DELETE FROM ams_list_entries
WHERE list_entry_id = l_list_entry_tbl(i);
AMS_Utility_PVT.debug_message(l_api_name||': bulk update on list headers');
UPDATE ams_list_headers_all
SET (no_of_rows_in_list
, no_of_rows_active
, last_update_date )=(select count(1),
sum(decode(enabled_flag,'Y',1,0)),
sysdate
from ams_list_entries
where list_header_id = l_list_header_tbl(i) )
WHERE list_header_id = l_list_header_tbl(i);
DELETE FROM ams_imp_source_lines
WHERE import_list_header_id = l_import_list_header_id;
AMS_Utility_PVT.debug_message(l_api_name||': force purge expiry_date passed count of ams_imp_source_lines_deleted:'||sql%rowcount);
DELETE FROM ams_party_sources
WHERE import_list_header_id = l_import_list_header_id
AND used_flag = 'N';
AMS_Utility_PVT.debug_message(l_api_name||': delete from list entries:' || l_force_purge_flag);
DELETE FROM ams_list_entries
WHERE list_entry_id = l_list_entry_tbl(i);
AMS_Utility_PVT.debug_message(l_api_name||': bulk update on list headers');
UPDATE ams_list_headers_all
SET (no_of_rows_in_list
, no_of_rows_active
, last_update_date )=(select count(1),
sum(decode(enabled_flag,'Y',1,0)),
sysdate
from ams_list_entries
where list_header_id = l_list_header_tbl(i) )
WHERE list_header_id = l_list_header_tbl(i);
DELETE FROM ams_party_sources
WHERE used_flag = 'N'
AND import_source_line_id IN (
SELECT import_source_line_id
FROM ams_imp_source_lines
WHERE import_list_header_id = l_import_list_header_id
AND current_usage >= l_usage);
AMS_Utility_PVT.debug_message(l_api_name||': count of ams_party_sources deleted:'||sql%rowcount);
DELETE FROM ams_imp_source_lines
WHERE import_list_header_id = l_import_list_header_id
AND current_usage >= l_usage;
AMS_Utility_PVT.debug_message(l_api_name||': count of ams_imp_source_lines deleted:'||sql%rowcount);
AMS_Utility_PVT.Debug_Message(l_api_name||': Delete log and update ams_imp_list_headers_all ');
UPDATE ams_imp_list_headers_all a
SET status_code = l_lookup_code,
user_status_id = l_status_id,
status_date = SYSDATE,
object_version_number = l_object_version + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE a.import_list_header_id = l_import_list_header_id
AND a.object_version_number = l_object_version
AND NOT EXISTS (
SELECT import_source_line_id
FROM ams_imp_source_lines b
WHERE b.import_list_header_id = a.import_list_header_id)
RETURNING status_code INTO l_upd_status_code;
DELETE FROM ams_act_logs
WHERE arc_act_log_used_by = l_arc_log_used_by
AND act_log_used_by_id = l_import_list_header_id;
AMS_Utility_PVT.debug_message(l_api_name||': count of ams_act_logs deleted:'||sql%rowcount);
l_list_entry_tbl.delete;
l_list_header_tbl.delete;
l_list_size_tbl.delete;
AMS_Utility_PVT.Debug_Message(l_api_name||': Delete cancelld imp list headers ');
DELETE FROM ams_imp_list_headers_all
WHERE status_code = 'CANCELLED';
SELECT list_header_id, status_code, object_version_number, purge_flag,
sent_out_date
FROM ams_list_headers_all
WHERE list_type = p_list_type
AND upper(status_code) <> p_list_status;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'AMS_LIST_STATUS'
AND system_status_code = c_status_code
AND enabled_flag = 'Y'
AND default_flag = 'Y';
SELECT lookup_code INTO l_lookup_type
FROM ams_lookups
WHERE lookup_type = 'AMS_LIST_TYPE'
AND lookup_code = 'TARGET';
SELECT lookup_code INTO l_lookup_status
FROM ams_lookups
WHERE lookup_type = 'AMS_LIST_STATUS'
AND lookup_code = 'ARCHIVED';
DELETE FROM ams_list_entries
WHERE list_header_id = l_list_header_id;
-- Update ams_list_headers_all
UPDATE ams_list_headers_all
SET (no_of_rows_in_list
, no_of_rows_active
, NO_OF_ROWS_DUPLICATES
, NO_OF_ROWS_INACTIVE
, last_update_date )=(select count(1),
sum(decode(enabled_flag,'Y',1,0)),
sum(decode(marked_as_duplicate_flag,'Y',1,0)),
sum(decode(enabled_flag,'Y',0,1)),
sysdate
from ams_list_entries
where list_header_id = l_list_header_id)
WHERE list_header_id = l_list_header_id;*/
UPDATE ams_list_headers_all
SET no_of_rows_in_list = 0,
no_of_rows_active = 0,
no_of_rows_inactive = 0,
no_of_rows_in_ctrl_group = 0,
no_of_rows_random = 0,
no_of_rows_duplicates = 0,
no_of_rows_manually_entered = 0,
no_of_rows_suppressed = 0,
no_of_rows_fatigued = 0,
tca_failed_records = 0,
no_of_rows_initially_selected= 0,
object_version_number = l_object_version + 1,
status_code = l_lookup_status,
user_status_id = l_user_status_id,
status_date = SYSDATE,
archived_by = FND_GLOBAL.user_id,
archived_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE list_header_id = l_list_header_id;
SELECT lines.import_source_line_id, lines.object_version_number,
lines.current_usage
FROM ams_imp_source_lines lines, ams_list_entries entries
WHERE entries.list_header_id = p_list_header_id
AND ENTRIES.enabled_flag = 'Y'
AND lines.import_source_line_id = entries.imp_source_line_id;
UPDATE ams_imp_source_lines SET
current_usage = l_current_usage_tbl(i)
, object_version_number = l_object_version_tbl(i)
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
WHERE import_source_line_id = l_source_line_id_tbl(i);
PROCEDURE delete_list_info(p_id_tbl IN ams_list_purge_pvt.l_id_tbl%type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) is
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE from ams_act_logs
WHERE act_log_used_by_id = p_id_tbl(i);
DELETE from ams_list_select_actions
WHERE action_used_by_id = p_id_tbl(i);
DELETE from ams_list_rule_usages
WHERE list_header_id = p_id_tbl(i);
DELETE from ams_query_condition_value value
WHERE EXISTS (SELECT 1
FROM ams_query_temp_inst_cond_assoc assoc,
ams_query_template_instance inst
WHERE assoc.template_instance_id = inst.template_instance_id
AND value.assoc_id = assoc.assoc_id
AND inst.instance_used_by_id = p_id_tbl(i));
DELETE from AMS_QUERY_TEMP_INST_COND_ASSOC assoc
WHERE EXISTS (SELECT 1
FROM ams_query_template_instance inst
WHERE assoc.template_instance_id = inst.template_instance_id
AND inst.instance_used_by_id = p_id_tbl(i));
DELETE from ams_query_template_instance
WHERE instance_used_by_id = p_id_tbl(i);
UPDATE ams_query_template_all qt
SET in_use_flag = 'N'
WHERE NOT EXISTS (SELECT 1
FROM ams_query_template_instance inst
WHERE qt.template_id = inst.template_id
AND inst.instance_used_by_id is not null);
UPDATE ams_query_condition cond
SET condition_in_use_flag = 'N'
WHERE NOT EXISTS (SELECT 1
FROM ams_query_template_instance inst
WHERE cond.template_id = inst.template_id
AND inst.instance_used_by_id is not null);
DELETE from ams_list_headers_all
WHERE list_header_id = p_id_tbl(i);
END delete_list_info;
PROCEDURE delete_list_manager (x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2
, p_list_header_id IN NUMBER
, p_batch_size IN NUMBER DEFAULT 1000
, p_num_workers IN NUMBER DEFAULT 3) IS
CURSOR c_get_list_header_status IS
SELECT status_code
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
SELECT list_header_id
FROM ams_list_select_actions
WHERE arc_incl_object_from='LIST'
AND incl_object_id = p_list_header_id;
SELECT list_header_id
FROM ams_act_lists
WHERE list_act_type IN ('TARGET','LIST')
AND list_header_id = p_list_header_id;
SELECT list_header_id, list_name
FROM ams_list_headers_vl
WHERE request_id = l_request_id;
fnd_file.put_line(fnd_file.log,'Execution of Delete List entries master concurrent program started.');
if l_list_header_status = 'DELETED' then
UPDATE ams_list_headers_all
SET request_id = l_conc_request_id,user_status_id = 314
WHERE list_header_id = p_list_header_id;
fnd_file.put_line(fnd_file.log,'List header status is already DELETED');
fnd_file.put_line(fnd_file.log,'List header status is not DELETED. Checking if its in use');
fnd_file.put_line(fnd_file.log,'This list is in use. Could not be deleted.');
fnd_file.put_line(fnd_file.log,'This list is not in use. Could be deleted.');
UPDATE ams_list_headers_all
SET request_id = l_conc_request_id, status_code = 'DELETED', user_status_id = 314
WHERE list_header_id = p_list_header_id;
fnd_file.put_line(fnd_file.log,'No list header id passed. So all the entries for lists in DELETED status will be deleted');
update ams_list_headers_all
set request_id = l_conc_request_id
where status_code = 'DELETED';
fnd_file.put_line(fnd_file.log,'Entries deleted successfully. Need to delete from other tables.');
fnd_file.put_line(fnd_file.log,'Entries deleted successfully. Need to delete from other tables. Total count of header rec is '||l_header_id_tbl.count);
delete_list_info(p_id_tbl => l_header_id_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_file.put_line(fnd_file.log,'List '||l_list_name_tbl(i)||' is deleted');
fnd_file.put_line(fnd_file.log,'Delete Entries concurrent program executed successfully.');
fnd_file.put_line(fnd_file.log,'Error while executing Delete Entries concurrent program '||sqlerrm);
END delete_list_manager;
PROCEDURE delete_list_worker( x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2
, x_batch_size IN NUMBER
, x_worker_id IN NUMBER
, x_num_workers IN NUMBER
, x_argument4 IN VARCHAR2) IS
l_worker_id NUMBER;
l_update_name VARCHAR2(30);
l_update_name := x_argument4;
ad_parallel_updates_pkg.initialize_rowid_range
(
ad_parallel_updates_pkg.ROWID_RANGE
, l_table_owner
, l_table_name
, l_update_name
, x_worker_id
, x_num_workers
, x_batch_size
, 0
);
ad_parallel_updates_pkg.get_rowid_range
(
l_start_rowid
, l_end_rowid
, l_any_rows_to_process
, x_batch_size
, TRUE
);
DELETE /*+ rowid(entries) */
AMS_LIST_ENTRIES entries
WHERE list_header_id IN (select list_header_id from ams_list_headers_all
where request_id = x_argument4)
AND ROWID BETWEEN l_start_rowid AND l_end_rowid;
ad_parallel_updates_pkg.processed_rowid_range
(
l_rows_processed
, l_end_rowid
);
ad_parallel_updates_pkg.get_rowid_range
(
l_start_rowid
, l_end_rowid
, l_any_rows_to_process
, x_batch_size
, FALSE
);
END delete_list_worker;
PROCEDURE delete_entries_soft (p_list_header_id_tbl IN AMS_LIST_PURGE_PVT.l_list_header_id_tbl%type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) is
type num_tbl is table of number index by binary_integer;
UPDATE ams_list_headers_all
SET status_code = 'DELETED', user_status_id = 314
WHERE list_header_id = l_header_id_tbl(i);
END delete_entries_soft;
PROCEDURE delete_entries_online(p_list_header_id_tbl IN AMS_LIST_PURGE_PVT.l_list_header_id_tbl%type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) is
l_header_id_tbl ams_list_purge_pvt.l_id_tbl%type;
DELETE from ams_list_entries
WHERE list_header_id = l_header_id_tbl(i);
delete_list_info(p_id_tbl => l_header_id_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_entries_online;
SELECT list_header_id, list_name
FROM ams_list_headers_vl
WHERE request_id = l_request_id;
UPDATE ams_list_headers_all head
SET request_id = l_conc_request_id,last_update_date = sysdate
WHERE status_code in ('AVAILABLE','LOCKED')
AND trunc(creation_date) between trunc(l_start_date) AND trunc(l_end_date)
AND list_type = p_list_type
AND not exists (SELECT 1
FROM AMS_LIST_SRC_TYPES type
WHERE head.list_source_type = type.source_type_code
AND nvl(type.remote_flag,'N') = 'Y') ;
UPDATE ams_list_headers_all head
SET request_id = l_conc_request_id,last_update_date = sysdate
WHERE status_code in ('AVAILABLE','LOCKED')
AND trunc(creation_date) between trunc(l_start_date) AND trunc(l_end_date)
AND not exists (SELECT 1
FROM AMS_LIST_SRC_TYPES type
WHERE head.list_source_type = type.source_type_code
AND nvl(type.remote_flag,'N') = 'Y') ;
DELETE from ams_act_logs
WHERE act_log_used_by_id = l_header_id_tbl(i);
UPDATE ams_list_headers_all
SET status_code = 'PURGED',
user_status_id = 313,
no_of_rows_in_list = 0,
no_of_rows_active = 0,
no_of_rows_inactive = 0,
no_of_rows_in_ctrl_group = 0,
no_of_rows_random = 0,
no_of_rows_duplicates = 0,
no_of_rows_manually_entered = 0,
no_of_rows_suppressed = 0,
NO_OF_ROWS_FATIGUED = 0,
TCA_FAILED_RECORDS = 0,
no_of_rows_initially_selected= 0,
object_version_number = object_version_number + 1,
status_date = SYSDATE,
archived_by = FND_GLOBAL.user_id,
archived_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE list_header_id = l_header_id_tbl(i);
fnd_file.put_line(fnd_file.log,'Entries for list/target group '||l_list_name_tbl(i)||' is deleted');
l_update_name VARCHAR2(30);
l_update_name := x_argument4;
ad_parallel_updates_pkg.initialize_rowid_range
(
ad_parallel_updates_pkg.ROWID_RANGE
, l_table_owner
, l_table_name
, l_update_name
, x_worker_id
, x_num_workers
, x_batch_size
, 0
);
ad_parallel_updates_pkg.get_rowid_range
(
l_start_rowid
, l_end_rowid
, l_any_rows_to_process
, x_batch_size
, TRUE
);
DELETE /*+ rowid(entries) */
AMS_LIST_ENTRIES entries
WHERE list_header_id IN (select list_header_id from ams_list_headers_all
where request_id = x_argument4)
AND ROWID BETWEEN l_start_rowid AND l_end_rowid;
ad_parallel_updates_pkg.processed_rowid_range
(
l_rows_processed
, l_end_rowid
);
ad_parallel_updates_pkg.get_rowid_range
(
l_start_rowid
, l_end_rowid
, l_any_rows_to_process
, x_batch_size
, FALSE
);