1 PACKAGE BODY POR_PURGE_SYS_SAVED_REQ AS
2 /* $Header: PORSSPGB.pls 120.1.12000000.2 2007/10/15 09:20:14 ssachdev ship $ */
3
4 PROCEDURE purge_req(x_updated_days IN NUMBER default 1) is
5
6 cursor c_req (p_date number) is
7 select distinct prh.requisition_header_id
8 from po_requisition_headers_all prh, po_requisition_lines_all prl
9 where prh.authorization_status ='SYSTEM_SAVED'
10 and prh.requisition_header_id = prl.requisition_header_id (+)
11 and prh.last_update_date < (sysdate-p_date)
12 and prl.line_location_id is null
13 order by prh.requisition_header_id;
14
15 /*
16 select requisition_header_id
17 from po_requisition_headers_all
18 where authorization_status ='SYSTEM_SAVED'
19 and last_update_date < (sysdate-p_date)
20 order by requisition_header_id;
21 */
22 age number := 1;
23 syssaved_req_header_id number := -9999;
24 x_progress varchar2(3) := null;
25
26
27 BEGIN
28
29 x_progress := '000';
30 age := 1;
31
32 if(x_updated_days>1) then
33 age := x_updated_days;
34 end if;
35
36 open c_req(age);
37
38 loop
39 fetch c_req into syssaved_req_header_id;
40
41 exit when c_req%NOTFOUND;
42
43 -- dbms_output.put_line('deleting ' || to_char(syssaved_req_header_id));
44
45 -- bluk: call the API to delete headers, lines, and other information for the req
46
47 --por_util_pkg.delete_requisition(syssaved_req_header_id);
48 --commented the call to function delete_requsition().
49 --Instead put a call to function purge_requisition(),
50 --that has been added to por_util_pkg as a part of fix for bug#6368269
51 por_util_pkg.purge_requisition(syssaved_req_header_id);
52
53 /*
54 delete po_approval_list_lines
55 where APPROVAL_LIST_HEADER_ID in
56 ( select approval_list_header_id
57 from po_approval_list_headers
58 where document_id = syssaved_req_header_id
59 and document_type = 'REQUISITION');
60
61 delete po_approval_list_headers
62 where document_id = syssaved_req_header_id
63 and document_type = 'REQUISITION';
64
65 delete PO_REQ_DISTRIBUTIONS_ALL
66 where REQUISITION_LINE_ID in
67 ( select requisition_line_id
68 from PO_REQUISITION_LINES_ALL
69 where REQUISITION_HEADER_ID = syssaved_req_header_id);
70
71 delete PO_REQUISITION_LINES_ALL
72 where REQUISITION_HEADER_ID = syssaved_req_header_id;
73
74 delete PO_REQUISITION_HEADERS_ALL
75 where REQUISITION_HEADER_ID = syssaved_req_header_id;
76 */
77 commit;
78
79 end loop;
80 close c_req;
81
82 exception
83
84 when others then
85 rollback;
86 po_message_s.sql_error('POR_PURGE_SYS_SAVED_REQ', x_progress, sqlcode);
87 raise;
88 end;
89
90 end POR_PURGE_SYS_SAVED_REQ;
[Home] [Help]