[Home] [Help]
42: p_result_status,
43: 'N');
44:
45: IF p_archive_name is NULL and p_entity_type is not null and p_entity is not null THEN
46: insert into QP_ARCH_CRITERIA_LINES
47: (request_id,
48: parameter_name,
49: parameter_value)
50: values
51: (p_conc_request_id,
52: 'ENTITY_TYPE',
53: p_entity_type);
54:
55: insert into QP_ARCH_CRITERIA_LINES
56: (request_id,
57: parameter_name,
58: parameter_value)
59: values(p_conc_request_id,
62:
63: END IF;
64:
65: IF (p_archive_start_date is not null and p_archive_end_date is not null) THEN
66: insert into QP_ARCH_CRITERIA_LINES
67: (request_id,
68: parameter_name,
69: parameter_value)
70: values
71: (p_conc_request_id,
72: 'ARCHIVE_START_DATE',
73: fnd_date.canonical_to_date(p_archive_start_date));
74:
75: insert into QP_ARCH_CRITERIA_LINES
76: (request_id,
77: parameter_name,
78: parameter_value)
79: values(p_conc_request_id,
183: IF (p_entity_type is not null and p_entity is not null) THEN
184: --Get the count of records matching the purge criteria
185:
186: select count(*) into l_count
187: from QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
188: where c.request_id = a.request_id and
189: a.REQUEST_ID = b.REQUEST_ID
190: and c.purge_flag = 'N'
191: and c.REQUEST_TYPE = 'ARCHIVE'
200: --Delete from QP_ARCH_LIST_HEADERS_TL
201:
202: LOOP
203: DELETE QP_ARCH_LIST_HEADERS_TL WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
204: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
205: where c.request_id = a.request_id and
206: a.REQUEST_ID = b.REQUEST_ID
207: and c.purge_flag = 'N'
208: and c.REQUEST_TYPE = 'ARCHIVE'
219: --Delete from QP_ARCH_LIST_HEADERS_B
220:
221: LOOP
222: DELETE QP_ARCH_LIST_HEADERS_B WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
223: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
224: where c.request_id = a.request_id and
225: a.REQUEST_ID = b.REQUEST_ID
226: and c.purge_flag = 'N'
227: and c.REQUEST_TYPE = 'ARCHIVE'
238: --Delete from QP_ARCH_LIST_LINES
239:
240: LOOP
241: DELETE QP_ARCH_LIST_LINES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
242: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
243: where c.request_id = a.request_id and
244: a.REQUEST_ID = b.REQUEST_ID
245: and c.purge_flag = 'N'
246: and c.REQUEST_TYPE = 'ARCHIVE'
257: --Delete from QP_ARCH_PRICING_ATTRIBUTES
258:
259: LOOP
260: DELETE QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
261: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
262: where c.request_id = a.request_id and
263: a.REQUEST_ID = b.REQUEST_ID
264: and c.purge_flag = 'N'
265: and c.REQUEST_TYPE = 'ARCHIVE'
277:
278: LOOP
279: DELETE QP_ARCH_RLTD_MODIFIERS WHERE ARCH_PURG_REQUEST_ID in
280: (select c.request_id from
281: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
282: where c.request_id = a.request_id and
283: a.REQUEST_ID = b.REQUEST_ID
284: and c.purge_flag = 'N'
285: and c.REQUEST_TYPE = 'ARCHIVE'
298: LOOP
299:
300: DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID in
301: (select c.request_id from
302: QP_ARCH_CRITERIA_LINES a ,
303: QP_ARCH_CRITERIA_LINES b ,
304: QP_ARCH_CRITERIA_HEADERS c
305: where c.request_id = a.request_id and
306: a.REQUEST_ID = b.REQUEST_ID
299:
300: DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID in
301: (select c.request_id from
302: QP_ARCH_CRITERIA_LINES a ,
303: QP_ARCH_CRITERIA_LINES b ,
304: QP_ARCH_CRITERIA_HEADERS c
305: where c.request_id = a.request_id and
306: a.REQUEST_ID = b.REQUEST_ID
307: and c.purge_flag = 'N'
320: --Update the purge_flag in the QP_ARCH_CRITERIA_HEADERS table to Y
321:
322: LOOP
323: update QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' WHERE request_id in (select c.request_id from
324: QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
325: where c.request_id = a.request_id and
326: a.REQUEST_ID = b.REQUEST_ID
327: and c.purge_flag = 'N'
328: and c.REQUEST_TYPE = 'ARCHIVE'
467: commit;
468:
469: fnd_file.put_line(FND_FILE.LOG,'Purge completed successfully');
470:
471: --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
472:
473: INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
474: p_archive_start_date,p_archive_end_date,
475: l_user_id,l_conc_request_id,'S');
479:
480: EXCEPTION
481: WHEN NO_DATA_FOUND THEN
482:
483: --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
484:
485: INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
486: p_archive_start_date,p_archive_end_date,
487: l_user_id,l_conc_request_id,'W');
491: retcode := 1;
492:
493: WHEN OTHERS THEN
494:
495: --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
496:
497: INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
498: p_archive_start_date,p_archive_end_date,
499: l_user_id,l_conc_request_id,'F');