DBA Data[Home] [Help]

APPS.QP_PURGE_ENTITY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

* 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');
Line: 46

insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values
(p_conc_request_id,
'ENTITY_TYPE',
p_entity_type);
Line: 55

insert into QP_ARCH_CRITERIA_LINES
(request_id,
parameter_name,
parameter_value)
values(p_conc_request_id,
'ENTITY',
p_entity);
Line: 66

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));
Line: 75

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));
Line: 85

END INSERT_CRITERIA;
Line: 119

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';
Line: 132

          DELETE QP_ARCH_LIST_HEADERS_TL  WHERE ARCH_PURG_REQUEST_ID  = l_request_id;
Line: 139

          DELETE QP_ARCH_LIST_HEADERS_B  WHERE ARCH_PURG_REQUEST_ID  = l_request_id;
Line: 146

          DELETE QP_ARCH_LIST_LINES  WHERE ARCH_PURG_REQUEST_ID  = l_request_id;
Line: 153

          DELETE QP_ARCH_PRICING_ATTRIBUTES  WHERE ARCH_PURG_REQUEST_ID  = l_request_id;
Line: 160

          DELETE QP_ARCH_RLTD_MODIFIERS  WHERE ARCH_PURG_REQUEST_ID = l_request_id;
Line: 167

          DELETE QP_ARCH_QUALIFIERS  WHERE ARCH_PURG_REQUEST_ID  = l_request_id;
Line: 174

           UPDATE QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' where request_id =l_request_id;
Line: 186

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));
Line: 203

          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;
Line: 222

          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;
Line: 241

          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;
Line: 260

 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;
Line: 279

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;
Line: 300

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;
Line: 323

          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;
Line: 349

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;
Line: 359

          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;
Line: 374

          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;
Line: 389

          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;
Line: 404

          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;
Line: 419

          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;
Line: 434

          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;
Line: 449

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;
Line: 473

  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');
Line: 485

  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');
Line: 489

            fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Deleted');
Line: 490

            errbuf := 'No Data Found - 0 Records Deleted';
Line: 497

  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');