4 Procedure Lines(
1 PACKAGE BODY GMI_MO_PURGE AS
2 /* $Header: GMIPURGB.pls 115.0 2004/02/23 21:27:25 lswamy noship $ */
3
8 , p_date_from IN VARCHAR2 :=NULL
5 errbuf OUT NOCOPY VARCHAR2
6 , retcode OUT NOCOPY VARCHAR2
7 , p_organization_id IN NUMBER :=NULL
9 , p_date_to IN VARCHAR2 :=NULL
10 , p_lines_percommit IN NUMBER :=NULL
11 , p_purge_option IN NUMBER) IS
12
13 loopnum number := 0;
14 l_line_id NUMBER;
15 l_header_id NUMBER;
16 v_sql_stmt VARCHAR2(300);
17 v_object_name VARCHAR2(200);
18 l_date_from DATE:= trunc(fnd_date.canonical_to_date(p_date_from));
19 l_date_to DATE:= trunc(fnd_date.canonical_to_date(p_date_to));
20 table_already_exists EXCEPTION;
21 PRAGMA EXCEPTION_INIT (table_already_exists,-955);
22
23
24
25 Cursor Closed_order_lines IS
26 select line_id, flow_status_code, open_flag, cancelled_flag
27 from oe_order_lines_all lines
28 where lines.flow_status_code in('CLOSED','CANCELLED')
29 and ship_from_org_id=p_organization_id
30 and trunc(lines.creation_date) between l_date_from and l_date_to;
31
32 Cursor Default_transactions (p_line_id VARCHAR2) is
33 SELECT trans_id
34 FROM ic_tran_pnd itp
35 WHERE doc_type = 'OMSO'
36 AND trans_qty = 0
37 AND lot_id = 0
38 AND delete_mark = 0
39 AND completed_ind = 0
40 AND line_detail_id is NULL
41 AND line_id = p_line_id;
42
43 Cursor For_Cancelled_Closed_MO_lines (p_line_id VARCHAR2) IS
44 SELECT icl.line_id,
45 icl.header_id
46 FROM ic_txn_request_lines icl
47 where icl.line_status = 5
48 and icl.txn_source_line_id = p_line_id;
49
50 Cursor See_if_inv_interface_is_done (p_line_id VARCHAR2) IS
51 select count(*)
52 from wsh_delivery_details wdd
53 where wdd.source_line_id=p_line_id
54 and wdd.source_code='OE'
55 and wdd.inv_interfaced_flag = 'Y';
56
57 is_inv_interface_done NUMBER;
58
59 continue exception;
60
61 BEGIN
62
63 IF NOT INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id)THEN
64 RETURN;
65 END IF;
66
67 --
68 -- If purge transactions are selected
69 --
70 IF p_purge_option in (0,2) THEN
71 GMI_Reservation_Util.PrintLn('Purging default transactions');
72 FOR Order_rec IN Closed_order_lines
73 LOOP
74 FOR c1rec in Default_transactions (Order_rec.line_id)
75 LOOP
76 --
77 -- Logically delete the transactions.
78 --
79 update ic_Tran_pnd
80 set delete_mark=1
81 where trans_id= c1rec.trans_id;
82
83
84 loopnum := loopnum + 1;
85
86 IF (mod(loopnum, p_lines_percommit) = 0) then
87 COMMIT;
88 END IF;
89 END LOOP; -- Default_transactions
90 COMMIT;
91 END LOOP; -- Closed_order_lines
92 GMI_Reservation_Util.PrintLn(' '||loopnum||' Default transactions Successfully purged');
93 END IF;
94
95 IF p_purge_option in (1,2) THEN
96 --
97 -- Create the table. If table exists handle the exception.
98 --
99 BEGIN
100 v_sql_stmt := 'CREATE TABLE IC_TXN_REQUEST_HEADERS_BAK AS SELECT * FROM IC_TXN_REQUEST_HEADERS WHERE 1=2';
101 EXECUTE IMMEDIATE v_sql_stmt;
102 EXCEPTION
103 WHEN table_already_exists THEN
104 NULL;
105 END;
106
107 --
108 -- Create the table. If table exists handle the exception.
109 --
110 BEGIN
111 v_sql_stmt := 'CREATE TABLE IC_TXN_REQUEST_LINES_BAK AS SELECT * FROM IC_TXN_REQUEST_LINES WHERE 1=2';
112 EXECUTE IMMEDIATE v_sql_stmt;
113 EXCEPTION
114 WHEN table_already_exists THEN
115 NULL;
116 END;
117
118 GMI_Reservation_Util.PrintLn('Backup MO Tables successfully Created');
119 END IF;
120
121 --
122 -- If purge Move order is selected
123 --
124 IF p_purge_option in (1,2) THEN
125 loopnum := 0; -- Intialize loopnum
126 GMI_Reservation_Util.PrintLn('Purging Move Order Header/Lines');
127 FOR Order_rec IN Closed_order_lines
128 LOOP
129 BEGIN
130 --
131 -- Check if the order is CLOSED and deliveries are INV interfaced.
132 --
133 IF (Order_rec.flow_status_code = 'CLOSED') THEN
134 OPEN See_if_inv_interface_is_done (Order_rec.line_id);
135 FETCH See_if_inv_interface_is_done into is_inv_interface_done;
136 CLOSE See_if_inv_interface_is_done;
137
138 IF (is_inv_interface_done = 0) THEN
139 -- this line is not yet interfaced. Skip this record.
140 RAISE continue;
141 END IF;
142 END IF;
143
144 --
145 -- Loop thru all the MO lines and archive them in _BAK table.
146 --
147 OPEN For_Cancelled_Closed_MO_lines (Order_rec.line_id);
148 LOOP
149 FETCH For_Cancelled_Closed_MO_lines into l_line_id, l_header_id;
150 EXIT WHEN For_Cancelled_Closed_MO_lines%NOTFOUND;
151
152 BEGIN
153 --
154 -- Archive first.
155 --
156 v_sql_stmt :=
157 'INSERT INTO IC_TXN_REQUEST_LINES_BAK ' ||
158 '(SELECT * FROM IC_TXN_REQUEST_LINES ' ||
159 ' WHERE line_id = :1) ';
160
161 EXECUTE IMMEDIATE v_sql_stmt using l_line_id;
162
163 v_sql_stmt :=
164 'INSERT INTO IC_TXN_REQUEST_HEADERS_BAK ' ||
165 ' (SELECT * FROM IC_TXN_REQUEST_HEADERS ' ||
166 ' WHERE header_id = :1) ';
167
168 EXECUTE IMMEDIATE v_sql_stmt using l_header_id;
169
170 --
171 -- Physically delete.
172 --
173 DELETE FROM ic_txn_request_lines
174 WHERE line_id=l_line_id;
175
176 DELETE FROM ic_txn_request_headers ich
177 WHERE header_id=l_header_id
178 AND NOT EXISTS (select 1 from ic_txn_request_lines icl
179 where icl.header_id=ich.header_id);
180 EXCEPTION
181 WHEN others THEN
182 raise continue;
183 END;
184
185 END LOOP; -- For_Cancelled_Closed_MO_lines
186
187 IF (For_Cancelled_Closed_MO_lines%ISOPEN) THEN
188 CLOSE For_Cancelled_Closed_MO_lines;
189 END IF;
190
191 loopnum := loopnum + 1;
192
193 IF (mod(loopnum,p_lines_percommit) = 0) THEN
194 COMMIT;
195 END IF;
196
197
198 EXCEPTION
199 WHEN continue THEN
200 NULL;
201 END;
202 END LOOP;
203 COMMIT;
204 GMI_Reservation_Util.PrintLn('About '||loopnum||' Move Order Header/Lines purged successfully');
205 END IF;
206 EXCEPTION
207 when others then
208 GMI_Reservation_Util.PrintLn('Exception has occurred'||SQLERRM|| ' : ' || SQLCODE );
209 ROLLBACK;
210
211 END Lines;
212
213 END GMI_MO_PURGE;
[Home] [Help]