[Home] [Help]
PACKAGE BODY: APPS.PO_DOCS_INTERFACE_PURGE
Source
4 d_pkg_name CONSTANT varchar2(50) :=
1 PACKAGE BODY PO_DOCS_INTERFACE_PURGE AS
2 /* $Header: POXPOIPB.pls 120.4.12000000.2 2007/09/28 10:12:05 ppadilam ship $ */
3
5 PO_LOG.get_package_base('PO_DOCS_INTERFACE_PURGE');
6
7 PROCEDURE exclude_undeletable_records
8 ( x_intf_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
9 p_process_code_tbl IN PO_TBL_VARCHAR30,
10 p_po_header_id IN NUMBER
11 );
12
13 /*================================================================
14
15 PROCEDURE NAME: process_po_interface_tables()
16
17 ==================================================================*/
18 PROCEDURE process_po_interface_tables(
19 X_document_type IN VARCHAR2,
20 X_document_subtype IN VARCHAR2,
21 X_accepted_flag IN VARCHAR2,
22 X_rejected_flag IN VARCHAR2,
23 X_start_date IN DATE,
24 X_end_date IN DATE,
25 X_selected_batch_id IN NUMBER,
26 p_org_id IN NUMBER DEFAULT NULL, -- <R12 MOAC>
27 p_po_header_id IN NUMBER DEFAULT NULL -- <PDOI Rewrite>
28 )
29 IS
30
31 d_api_name CONSTANT VARCHAR2(30) := 'process_po_interface_tables';
32 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
33 d_position NUMBER;
34
35 l_org_id NUMBER;
36
37 l_intf_header_id_tbl PO_TBL_NUMBER;
38 l_process_code_tbl PO_TBL_VARCHAR30;
39 BEGIN
40
41 -- <PDOI Rewrite R12>
42 -- The whole procedure is refactored to remove repetitive code
43 -- Also, this procedure will now delete data from the following interface
44 -- tables:
45 -- PO_HEADERS_INTERFACE
46 -- PO_LINES_INTERFACE
47 -- PO_LINE_LOCATIONS_INTERFACE
48
49 d_position := 0;
50
51 IF (PO_LOG.d_proc) THEN
52 PO_LOG.proc_begin (d_module);
53 END IF;
54
55 l_org_id := p_org_id ; -- <R12 MOAC>
56
57 -- bug5471513
58 -- We should get the interface_header_id list first, and then perform
59 -- deletion after filtering process is done
60
61 SELECT interface_header_id,
62 process_code
63 BULK COLLECT
64 INTO l_intf_header_id_tbl,
65 l_process_code_tbl
66 FROM po_headers_interface
67 WHERE (batch_id = X_selected_batch_id
68 OR X_selected_batch_id IS NULL )
69
70 AND ((process_code = PO_PDOI_CONSTANTS.g_process_code_ACCEPTED and X_accepted_flag = 'Y')
71 OR (process_code = PO_PDOI_CONSTANTS.g_process_code_REJECTED and X_rejected_flag = 'Y')
72 OR (process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS AND p_po_header_id IS NOT NULL))
73
74 AND (org_id = l_org_id
75 OR l_org_id is NULL)
76
77 AND (document_type_code = UPPER(X_document_type)
78 OR X_document_type is NULL)
79
80 AND (document_subtype = UPPER(X_document_subtype)
81 OR X_document_subtype is NULL)
82
83 AND (Trunc(creation_date) >= X_start_date
84 OR X_start_date is NULL)
85
86 AND (trunc(creation_date) <= X_end_date
87 OR X_end_date is NULL)
88
89 AND (p_po_header_id IS NULL
90 OR p_po_header_id = po_header_id);
91
92 d_position := 10;
93
94 -- refine the list of records to be deleted
95 exclude_undeletable_records
96 ( x_intf_header_id_tbl => l_intf_header_id_tbl,
97 p_process_code_tbl => l_process_code_tbl,
98 p_po_header_id => p_po_header_id
99 );
100
101 d_position := 15;
102
103 -- bug5471513
104 -- delete header interface records after filtering
105 FORALL i IN 1..l_intf_header_id_tbl.COUNT
106 DELETE FROM po_headers_interface
107 WHERE interface_header_id = l_intf_header_id_tbl(i);
108
109 IF (PO_LOG.d_stmt) THEN
110 PO_LOG.stmt(d_module, d_position, '# hdr intf rec deleted' || SQL%ROWCOUNT);
111 END IF;
112
113 d_position := 18;
114
115
116 FORALL i IN 1..l_intf_header_id_tbl.COUNT
117 DELETE FROM po_lines_interface
118 WHERE interface_header_id = l_intf_header_id_tbl(i);
119
120 IF (PO_LOG.d_stmt) THEN
121 PO_LOG.stmt(d_module, d_position, '# line intf rec deleted' || SQL%ROWCOUNT);
122 END IF;
123
124 d_position := 20;
125
126 FORALL i IN 1..l_intf_header_id_tbl.COUNT
127 DELETE FROM po_line_locations_interface
128 WHERE interface_header_id = l_intf_header_id_tbl(i);
129
130 IF (PO_LOG.d_stmt) THEN
131 PO_LOG.stmt(d_module, d_position, '# line loc intf rec deleted' || SQL%ROWCOUNT);
132 END IF;
133
134 d_position := 30;
135
136 FORALL i IN 1..l_intf_header_id_tbl.COUNT
137 DELETE FROM po_distributions_interface
138 WHERE interface_header_id = l_intf_header_id_tbl(i);
139
140 IF (PO_LOG.d_stmt) THEN
141 PO_LOG.stmt(d_module, d_position, '# distr intf rec deleted' || SQL%ROWCOUNT);
142 END IF;
143
144 d_position := 40;
145
146 FORALL i IN 1..l_intf_header_id_tbl.COUNT
147 DELETE FROM po_price_diff_interface
148 WHERE interface_header_id = l_intf_header_id_tbl(i);
149
150 IF (PO_LOG.d_stmt) THEN
151 PO_LOG.stmt(d_module, d_position, '# price diff intf rec deleted' || SQL%ROWCOUNT);
152 END IF;
153
154 d_position := 50;
155
156 FORALL i IN 1..l_intf_header_id_tbl.COUNT
157 DELETE FROM po_attr_values_interface
158 WHERE interface_header_id = l_intf_header_id_tbl(i);
159
160 IF (PO_LOG.d_stmt) THEN
161 PO_LOG.stmt(d_module, d_position, '# attr values intf rec deleted' || SQL%ROWCOUNT);
162 END IF;
163
164 d_position := 60;
165
166 FORALL i IN 1..l_intf_header_id_tbl.COUNT
167 DELETE FROM po_attr_values_tlp_interface
168 WHERE interface_header_id = l_intf_header_id_tbl(i);
169
170 IF (PO_LOG.d_stmt) THEN
171 PO_LOG.stmt(d_module, d_position, '# attr values tlp intf rec deleted' || SQL%ROWCOUNT);
172 END IF;
173
174 d_position := 70;
175
176 IF (PO_LOG.d_proc) THEN
177 PO_LOG.proc_end (d_module);
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 PO_MESSAGE_S.add_exc_msg
183 ( p_pkg_name => d_pkg_name,
184 p_procedure_name => d_api_name || '.' || d_position
185 );
186 RAISE;
187 END process_po_interface_tables;
188
189 PROCEDURE exclude_undeletable_records
190 ( x_intf_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
191 p_process_code_tbl IN PO_TBL_VARCHAR30,
192 p_po_header_id IN NUMBER
193 ) IS
194
195 d_api_name CONSTANT VARCHAR2(30) := 'exclude_undeletable_records';
196 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
197 d_position NUMBER;
198
199 l_new_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
200
201 l_draft_id PO_DRAFTS.draft_id%TYPE;
202 l_request_id PO_DRAFTS.request_id%TYPE;
203 l_old_request_complete VARCHAR2(1);
204 l_need_collapsing BOOLEAN := FALSE;
205
206 l_cur_index NUMBER;
207 l_counter NUMBER;
208 BEGIN
209
210 d_position := 0;
211
212 IF (PO_LOG.d_proc) THEN
213 PO_LOG.proc_begin (d_module);
214 END IF;
215
216 IF (p_po_header_id IS NULL) THEN
217 RETURN;
218 END IF;
219
220 d_position := 10;
221
222 FOR i IN 1..p_process_code_tbl.COUNT LOOP
223 d_position := 20;
224
225 IF (p_process_code_tbl(i) = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS) THEN
226 -- if user wants to purge the intf record that is still in process, make
227 -- sure that the drafts are removed and locks are released, if the
228 -- record is no longer being touched.
229
230 PO_DRAFTS_PVT.find_draft
231 ( p_po_header_id => p_po_header_id,
232 x_draft_id => l_draft_id
233 );
234
235 d_position := 30;
236
237 IF (l_draft_id IS NOT NULL) THEN
238 PO_DRAFTS_PVT.get_request_id
239 ( p_draft_id => l_draft_id,
240 x_request_id => l_request_id
241 );
242
243 IF (l_request_id IS NOT NULL) THEN
244 d_position := 40;
245
246 l_old_request_complete := PO_PDOI_UTL.is_old_request_complete
247 ( p_old_request_id => l_request_id
248 );
249
250 IF (PO_LOG.d_stmt) THEN
251 PO_LOG.stmt(d_module, d_position, 'l_old_request_complete',
252 l_old_request_complete);
253 END IF;
254
255 IF (l_old_request_complete = FND_API.G_TRUE) THEN
256 d_position := 50;
257
258 PO_DRAFTS_PVT.unlock_document
259 ( p_po_header_id => p_po_header_id
260 );
261 ELSE
262 d_position := 60;
263 -- cannot touch the draft yet since it is still being processed.
264 -- the interface records should not be deleted either.
265 x_intf_header_id_tbl.DELETE(i);
266 l_need_collapsing := TRUE;
267 END IF; -- old_request_complete = TRUE
268 END IF; -- request_id IS NOT NULL
269 END IF; -- draft_id IS NOT NULL
270 END IF; -- process_code = IN_PROCESS
271
272 END LOOP;
273
274 IF (l_need_collapsing) THEN
275 d_position := 70;
276
277 IF (PO_LOG.d_stmt) THEN
278 PO_LOG.stmt(d_module, d_position, 'new array size',
279 x_intf_header_id_tbl.COUNT);
280 END IF;
281
282 l_new_intf_header_id_tbl.EXTEND(x_intf_header_id_tbl.COUNT);
283
284 l_cur_index := x_intf_header_id_tbl.FIRST;
285
286 -- Copy all non-deleted data to temporary storage
287 WHILE l_cur_index <= x_intf_header_id_tbl.LAST LOOP
288 d_position := 80;
289
290 l_new_intf_header_id_tbl(l_counter) := x_intf_header_id_tbl(l_cur_index);
291 l_counter := l_counter + 1;
292 l_cur_index := x_intf_header_id_tbl.NEXT(l_cur_index);
293 END LOOP;
294
295 d_position := 90;
296 -- get back the array without holes
297 x_intf_header_id_tbl := l_new_intf_header_id_tbl;
298 END IF;
299
300 d_position := 100;
301
302 IF (PO_LOG.d_proc) THEN
303 PO_LOG.proc_end (d_module);
304 END IF;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 PO_MESSAGE_S.add_exc_msg
309 ( p_pkg_name => d_pkg_name,
310 p_procedure_name => d_api_name || '.' || d_position
311 );
312 RAISE;
313 END exclude_undeletable_records;
314
315 END PO_DOCS_INTERFACE_PURGE;