The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure to insert records into the criteria tables *
****************************************************************/
Procedure INSERT_CRITERIA
(
p_archive_name VARCHAR2,
p_entity_type VARCHAR2,
p_source_system_code VARCHAR2,
p_entity NUMBER,
p_archive_start_date VARCHAR2,
p_archive_end_date VARCHAR2,
p_user_id NUMBER,
p_conc_request_id NUMBER,
p_result_status VARCHAR2
)
IS
BEGIN
insert into QP_ARCH_CRITERIA_HEADERS
(request_id,
request_name,
request_type,
source_system,
creation_date,
created_by,
request_status,
purge_flag)
values
(p_conc_request_id,
NULL,
'PURGE',
p_source_system_code,
sysdate,
p_user_id,
p_result_status,
'N');
insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values
(p_conc_request_id,
'ENTITY_TYPE',
p_entity_type);
insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values(p_conc_request_id,
'ENTITY',
p_entity);
insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values
(p_conc_request_id,
'ARCHIVE_START_DATE',
fnd_date.canonical_to_date(p_archive_start_date));
insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values(p_conc_request_id,
'ARCHIVE_END_DATE',
fnd_date.canonical_to_date(p_archive_end_date));
END INSERT_CRITERIA;
SELECT request_id into l_request_id
FROM QP_ARCH_CRITERIA_HEADERS
WHERE nvl(request_name,'')=p_archive_name
and request_type = 'ARCHIVE'
and purge_flag = 'N';
DELETE QP_ARCH_LIST_HEADERS_TL WHERE ARCH_PURG_REQUEST_ID = l_request_id;
DELETE QP_ARCH_LIST_HEADERS_B WHERE ARCH_PURG_REQUEST_ID = l_request_id;
DELETE QP_ARCH_LIST_LINES WHERE ARCH_PURG_REQUEST_ID = l_request_id;
DELETE QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID = l_request_id;
DELETE QP_ARCH_RLTD_MODIFIERS WHERE ARCH_PURG_REQUEST_ID = l_request_id;
DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID = l_request_id;
UPDATE QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' where request_id =l_request_id;
select count(*) into l_count
from QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
And (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity));
DELETE QP_ARCH_LIST_HEADERS_TL WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
DELETE QP_ARCH_LIST_HEADERS_B WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
DELETE QP_ARCH_LIST_LINES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
DELETE QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
DELETE QP_ARCH_RLTD_MODIFIERS WHERE ARCH_PURG_REQUEST_ID in
(select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID in
(select c.request_id from
QP_ARCH_CRITERIA_LINES a ,
QP_ARCH_CRITERIA_LINES b ,
QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
update QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' WHERE request_id in (select c.request_id from
QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
where c.request_id = a.request_id and
a.REQUEST_ID = b.REQUEST_ID
and c.purge_flag = 'N'
and c.REQUEST_TYPE = 'ARCHIVE'
and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
AND rownum <= 500;
SELECT count(*) into l_count FROM QP_ARCH_CRITERIA_HEADERS
WHERE trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date)) and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code;
DELETE QP_ARCH_LIST_HEADERS_TL
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
DELETE QP_ARCH_LIST_HEADERS_B
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
DELETE QP_ARCH_LIST_LINES
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
DELETE QP_ARCH_PRICING_ATTRIBUTES
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
DELETE QP_ARCH_RLTD_MODIFIERS
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
DELETE QP_ARCH_QUALIFIERS
WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
update QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y'
WHERE request_id in (select request_id from QP_ARCH_CRITERIA_HEADERS
where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
AND rownum <= 500;
INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
p_archive_start_date,p_archive_end_date,
l_user_id,l_conc_request_id,'S');
INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
p_archive_start_date,p_archive_end_date,
l_user_id,l_conc_request_id,'W');
fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Deleted');
errbuf := 'No Data Found - 0 Records Deleted';
INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
p_archive_start_date,p_archive_end_date,
l_user_id,l_conc_request_id,'F');