1 PACKAGE BODY ego_import_batch_purge_pkg as
2 /* $Header: EGOIPURB.pls 120.2.12020000.2 2012/07/13 01:29:40 mshirkol ship $ */
3
4 -- ****************************************************************** --
5 -- API name : Ego_import_batch_purge_pkg --
6 -- Type : Private --
7 -- Pre-reqs : None. --
8 -- Parameters : --
9 -- IN : --
10 -- p_batch_id NUMBER Required --
11 -- p_purge_criteria varchar2 --
12 --
13 -- OUT : retcode VARCHAR2(1) --
14 -- error_buf VARCHAR2(30) --
15 -- Version : --
16 -- Current version 1.0 --
17 -- Initial version 1.0 --
18 -- --
19 -- Notes : --
20
21 -- ****************************************************************** --
22
23 CURSOR l_item_list_c(batch_id NUMBER) IS
24
25 SELECT Ego_item_list_inf (set_process_id, organization_id,
26 ORGANIZATION_CODE, REQUEST_ID,
27 INVENTORY_ITEM_ID, item_number,
28 BUNDLE_ID,
29 status ,
30 isinbill,
31 isincomp ,
32 isinsubcomp
33 )
34 FROM
35
36 (SELECT
37 set_process_id,
38 organization_id,
39 ORGANIZATION_CODE,
40 REQUEST_ID,
41 INVENTORY_ITEM_ID,
42 item_number,
43 BUNDLE_ID,
44 COALESCE -- search for failures
45 (
46 ( -- Looking for failure rows in MTL_SYSTEM_ITEMS_INTERFACE
47 SELECT 'PARTIAL'
48 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
49 WHERE
50 ( MSII.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
51 OR ( MSII.ITEM_NUMBER = I.ITEM_NUMBER
52 AND (
53 MSII.INVENTORY_ITEM_ID IS NULL
54 OR I.INVENTORY_ITEM_ID IS NULL
55 )
56 )
57 )
58 AND MSII.SET_PROCESS_ID = I.SET_PROCESS_ID
59 AND ( MSII.ORGANIZATION_ID = I.ORGANIZATION_ID
60 OR ( MSII.ORGANIZATION_CODE = I.ORGANIZATION_CODE
61 AND (MSII.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
62 )
63 )
64 AND ( (MSII.PROCESS_FLAG in (3,6) AND I.PROCESS_FLAG = 7) OR
65 (MSII.PROCESS_FLAG = 7 AND I.PROCESS_FLAG <> 7)
66 )
67 AND MSII.REQUEST_ID = I.REQUEST_ID
68 AND ROWNUM = 1
69 )
70 ,
71 ( -- Looking for failure rows in MTL_ITEM_REVISIONS_INTERFACE
72 SELECT 'PARTIAL'
73 FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
74 WHERE
75 ( MIRI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
76 OR ( MIRI.ITEM_NUMBER = I.ITEM_NUMBER
77 AND (
78 MIRI.INVENTORY_ITEM_ID IS NULL
79 OR I.INVENTORY_ITEM_ID IS NULL
80 )
81 )
82 )
83 AND MIRI.SET_PROCESS_ID = I.SET_PROCESS_ID
84 AND ( MIRI.ORGANIZATION_ID = I.ORGANIZATION_ID
85 OR ( MIRI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
86 AND (MIRI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
87 )
88 )
89 AND ( (MIRI.PROCESS_FLAG in (3,6) AND I.PROCESS_FLAG = 7) OR
90 (MIRI.PROCESS_FLAG = 7 AND I.PROCESS_FLAG <> 7)
91 )
92 AND MIRI.REQUEST_ID = I.REQUEST_ID
93 AND ROWNUM = 1
94 )
95 ,
96 ( -- Looking for failure rows in EGO_ITEM_PEOPLE_INTF
97 SELECT 'PARTIAL'
98 FROM EGO_ITEM_PEOPLE_INTF EIPI
99 WHERE
100 ( EIPI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
101 OR ( EIPI.ITEM_NUMBER = I.ITEM_NUMBER
102 AND (
103 EIPI.INVENTORY_ITEM_ID IS NULL
104 OR I.INVENTORY_ITEM_ID IS NULL
105 )
106 )
107 )
108 AND EIPI.DATA_SET_ID = I.SET_PROCESS_ID
109 AND ( EIPI.ORGANIZATION_ID = I.ORGANIZATION_ID
110 OR ( EIPI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
111 AND (EIPI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
112 )
113 )
114 AND ( (EIPI.PROCESS_STATUS in (3,6) AND I.PROCESS_FLAG = 7) OR
115 (EIPI.PROCESS_STATUS = 4 AND I.PROCESS_FLAG <> 7)
116 )
117 AND EIPI.REQUEST_ID = I.REQUEST_ID
118 AND ROWNUM = 1
119 )
120 ,
121 ( -- Looking for failure rows in MTL_ITEM_CATEGORIES_INTERFACE
122 SELECT 'PARTIAL'
123 FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
124 WHERE
125 ( MICI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
126 OR ( MICI.ITEM_NUMBER = I.ITEM_NUMBER
127 AND (
128 MICI.INVENTORY_ITEM_ID IS NULL
129 OR I.INVENTORY_ITEM_ID IS NULL
130 )
131 )
132 )
133 AND MICI.SET_PROCESS_ID = I.SET_PROCESS_ID
134 AND ( MICI.ORGANIZATION_ID = I.ORGANIZATION_ID
135 OR ( MICI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
136 AND (MICI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
137 )
138 )
139 AND ( (MICI.PROCESS_FLAG in (3,6) AND I.PROCESS_FLAG = 7) OR
140 (MICI.PROCESS_FLAG = 7 AND I.PROCESS_FLAG <> 7)
141 )
142 AND MICI.REQUEST_ID = I.REQUEST_ID
143 AND Nvl(MICI.BUNDLE_ID,0) = Nvl(I.BUNDLE_ID,0)
144 AND ROWNUM = 1
145 )
146 ,
147 ( -- Looking for failure rows in EGO_AML_INTF
148 SELECT 'PARTIAL'
149 FROM EGO_AML_INTF EAI
150 WHERE
151 ( EAI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
152 OR ( EAI.ITEM_NUMBER = I.ITEM_NUMBER
153 AND (
154 EAI.INVENTORY_ITEM_ID IS NULL
155 OR I.INVENTORY_ITEM_ID IS NULL
156 )
157 )
158 )
159 AND EAI.DATA_SET_ID = I.SET_PROCESS_ID
160 AND ( EAI.ORGANIZATION_ID = I.ORGANIZATION_ID
161 OR ( EAI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
162 AND (EAI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
163 )
164 )
165 AND ( (EAI.PROCESS_FLAG in (3,6) AND I.PROCESS_FLAG = 7) OR
166 (EAI.PROCESS_FLAG = 7 AND I.PROCESS_FLAG <> 7)
167 )
168 AND EAI.REQUEST_ID = I.REQUEST_ID
169 AND ROWNUM = 1
170 )
171 ,
172 ( -- Looking for failure rows in EGO_ITM_USR_ATTR_INTRFC
173 SELECT 'PARTIAL'
174 FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
175 WHERE
176 ( EIUAI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
177 OR ( EIUAI.ITEM_NUMBER = I.ITEM_NUMBER
178 AND (
179 EIUAI.INVENTORY_ITEM_ID IS NULL
180 OR I.INVENTORY_ITEM_ID IS NULL
181 )
182 )
183 )
184 AND EIUAI.DATA_SET_ID = I.SET_PROCESS_ID
185 AND ( EIUAI.ORGANIZATION_ID = I.ORGANIZATION_ID
186 OR ( EIUAI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
187 AND (EIUAI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
188 )
189 )
190 AND ( (EIUAI.PROCESS_STATUS in (3,6) AND I.PROCESS_FLAG = 7) OR
191 (EIUAI.PROCESS_STATUS = 4 AND I.PROCESS_FLAG <> 7)
192 )
193 AND EIUAI.REQUEST_ID = I.REQUEST_ID
194 AND Nvl(EIUAI.BUNDLE_ID,0) = Nvl(I.BUNDLE_ID,0)
195 AND ROWNUM = 1
196 )
197 ,
198 /* ( -- Looking for failure rows in EGO_ITEM_ASSOCIATIONS_INTF
199 SELECT 'PARTIAL'
200 FROM EGO_ITEM_ASSOCIATIONS_INTF EIAI
201 WHERE
202 ( EIAI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
203 OR ( EIAI.ITEM_NUMBER = I.ITEM_NUMBER
204 AND (
205 EIAI.INVENTORY_ITEM_ID IS NULL
206 OR I.INVENTORY_ITEM_ID IS NULL
207 )
208 )
209 )
210 AND EIAI.BATCH_ID = I.SET_PROCESS_ID
211 AND ( EIAI.ORGANIZATION_ID = I.ORGANIZATION_ID
212 OR ( EIAI.ORGANIZATION_CODE = I.ORGANIZATION_CODE
213 AND (EIAI.ORGANIZATION_CODE IS NULL OR I.ORGANIZATION_CODE IS NULL )
214 )
215 )
216 AND ( (EIAI.PROCESS_FLAG in (3,6) AND I.PROCESS_FLAG = 7) OR
217 (EIAI.PROCESS_FLAG = 7 AND I.PROCESS_FLAG <> 7)
218 )
219 AND EIAI.REQUEST_ID = I.REQUEST_ID
220 AND Nvl(EIAI.BUNDLE_ID,0) = Nvl(I.BUNDLE_ID,0)
221 AND ROWNUM = 1
222 )
223 , */
224 DECODE(I.PROCESS_FLAG,7,'SUCCESS','ERROR')
225 ) AS status,
226 0 isinbill,
227 0 isincomp,
228 0 isinsubcomp
229
230 FROM ( SELECT DISTINCT set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, BUNDLE_ID ,PROCESS_FLAG
231 FROM MTL_SYSTEM_ITEMS_INTERFACE
232 WHERE PROCESS_FLAG IN (3,6,7)
233
234 UNION
235
236 SELECT DISTINCT set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, null BUNDLE_ID ,PROCESS_FLAG
237 FROM MTL_ITEM_REVISIONS_INTERFACE
238 WHERE PROCESS_FLAG IN (3,6,7)
239
240 UNION
241
242 SELECT DISTINCT DATA_SET_ID set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, null BUNDLE_ID , Decode ( PROCESS_STATUS,4,7,PROCESS_STATUS) PROCESS_FLAG
243 FROM EGO_ITEM_PEOPLE_INTF
244 WHERE PROCESS_STATUS IN (3,6,4)
245
246 UNION
247
248 SELECT DISTINCT set_process_id , organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, BUNDLE_ID, PROCESS_FLAG
249 FROM MTL_ITEM_CATEGORIES_INTERFACE
250 WHERE PROCESS_FLAG IN (3,6,7)
251
252 UNION
253
254 SELECT DISTINCT DATA_SET_ID set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, null BUNDLE_ID , PROCESS_FLAG
255 FROM EGO_AML_INTF
256 WHERE PROCESS_FLAG IN (3,6,7)
257
258 /* UNION
259
260 SELECT DISTINCT DATA_SET_ID set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, BUNDLE_ID , Decode ( PROCESS_STATUS,4,7,PROCESS_STATUS) PROCESS_FLAG
261 FROM EGO_ITM_USR_ATTR_INTRFC
262 WHERE PROCESS_STATUS IN (3,6,4)*/
263
264 UNION
265
266 SELECT DISTINCT BATCH_ID set_process_id, organization_id, ORGANIZATION_CODE, REQUEST_ID, INVENTORY_ITEM_ID, item_number, BUNDLE_ID, PROCESS_FLAG
267 FROM EGO_ITEM_ASSOCIATIONS_INTF
268 WHERE PROCESS_FLAG IN (3,6,7)
269 ) I
270 WHERE set_process_id = batch_id );
271
272
273
274 TYPE cur_table IS TABLE OF Ego_item_list_inf;
275
276
277 PROCEDURE clear_items(p_purge_criteria IN VARCHAR2,
278 l_item_table IN cur_table) IS
279
280 l_item_entity_count NUMBER := 0;
281 l_item_rev_entity_count NUMBER := 0;
282 l_item_ppl_entity_count NUMBER := 0;
283 l_item_cat_entity_count NUMBER := 0;
284 l_item_asso_entity_count NUMBER := 0;
285
286 BEGIN
287
288 fnd_file.put_line(fnd_file.Log,'Start deleting Item Entities.');
289
290
291 FORALL item IN 1.. l_item_table.Count
292
293 DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
294 WHERE set_process_id = TREAT( l_item_table(item) AS Ego_item_list_inf).set_process_id
295 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
296 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
297 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
298 )
299 )
300 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
301 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
302 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
303 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
304 )
305 )
306 AND Nvl(BUNDLE_ID,0) = Nvl(TREAT( l_item_table(item) AS Ego_item_list_inf).BUNDLE_ID,0)
307 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
308 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
309
310
311 FOR i IN 1.. l_item_table.Count
312 LOOP
313 l_item_entity_count := l_item_entity_count + SQL%BULK_ROWCOUNT(i);
314
315 END LOOP;
316
317 fnd_file.put_line(fnd_file.Log,'No.of Item Entities deleted are '||l_item_entity_count);
318
319 FORALL item IN l_item_table.first .. l_item_table.last
320
321 DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
322 WHERE SET_PROCESS_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
323 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
324 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
325 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
326 )
327 )
328 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
329 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
330 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
331 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
332 )
333 )
334 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
335 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
336
337
338
339 FOR i IN l_item_table.first .. l_item_table.last
340 LOOP
341 l_item_rev_entity_count := l_item_rev_entity_count + SQL%BULK_ROWCOUNT(i);
342 END LOOP;
343 fnd_file.put_line(fnd_file.Log,'No.of Item Revision Entities deleted are '||l_item_rev_entity_count);
344
345
346
347
348
349 FORALL item IN l_item_table.first .. l_item_table.last
350
351 DELETE FROM EGO_ITEM_PEOPLE_INTF
352 WHERE DATA_SET_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
353 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
354 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
355 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
356 )
357 )
358 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
359 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
360 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
361 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
362 )
363 )
364 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
365 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
366
367
368 FOR i IN l_item_table.first .. l_item_table.last
369 LOOP
370 l_item_ppl_entity_count := l_item_ppl_entity_count + SQL%BULK_ROWCOUNT(i);
371 END LOOP;
372 fnd_file.put_line(fnd_file.Log,'No.of Item People deleted are '||l_item_ppl_entity_count);
373
374
375 FORALL item IN l_item_table.first .. l_item_table.last
376
377 DELETE FROM MTL_ITEM_CATEGORIES_INTERFACE
378 WHERE SET_PROCESS_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
379 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
380 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
381 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
382 )
383 )
384 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
385 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
386 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
387 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
388 )
389 )
390 AND Nvl(BUNDLE_ID,0) = Nvl(TREAT( l_item_table(item) AS Ego_item_list_inf).BUNDLE_ID,0)
391 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
392 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
393
394
395
396 FOR i IN l_item_table.first .. l_item_table.last
397 LOOP
398 l_item_cat_entity_count := l_item_cat_entity_count + SQL%BULK_ROWCOUNT(i);
399 END LOOP;
400 fnd_file.put_line(fnd_file.Log,'No.of Item Categories deleted are '||l_item_cat_entity_count);
401
402
403
404 FORALL item IN l_item_table.first .. l_item_table.last
405
406 DELETE FROM EGO_AML_INTF
407 WHERE DATA_SET_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
408 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
409 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
410 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
411 )
412 )
413 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
414 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
415 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
416 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
417 )
418 )
419 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
420 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
421
422
423
424 FORALL item IN l_item_table.first .. l_item_table.last
425
426 DELETE FROM EGO_ITM_USR_ATTR_INTRFC
427 WHERE DATA_SET_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
428 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
429 OR ( organization_code = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code
430 AND ( organization_code IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).organization_code IS NULL )
431 )
432 )
433 AND REQUEST_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
434 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
435 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
436 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
437 )
438 )
439 AND Nvl(BUNDLE_ID,0) = Nvl(TREAT( l_item_table(item) AS Ego_item_list_inf).BUNDLE_ID,0)
440 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
441 AND TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria;
442
443
444 FORALL item IN l_item_table.first .. l_item_table.last
445
446 DELETE FROM EGO_ITEM_ASSOCIATIONS_INTF EIAI
447 WHERE TREAT( l_item_table(item) AS Ego_item_list_inf).status=p_purge_criteria
448 AND BATCH_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).SET_PROCESS_ID
449 AND ( organization_id = TREAT( l_item_table(item) AS Ego_item_list_inf).organization_id
450 OR ( ORGANIZATION_CODE = TREAT( l_item_table(item) AS Ego_item_list_inf).ORGANIZATION_CODE
451 AND (ORGANIZATION_CODE IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).ORGANIZATION_CODE IS NULL)
452 )
453 )
454 AND nvl(REQUEST_ID,0) = TREAT( l_item_table(item) AS Ego_item_list_inf).REQUEST_ID
455 AND ( INVENTORY_ITEM_ID = TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID
456 OR ( ITEM_NUMBER = TREAT( l_item_table(item) AS Ego_item_list_inf).ITEM_NUMBER
457 AND ( INVENTORY_ITEM_ID IS NULL OR TREAT( l_item_table(item) AS Ego_item_list_inf).INVENTORY_ITEM_ID IS NULL )
458 )
459 )
460 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinbill = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isincomp = 0 AND TREAT( l_item_table(item) AS Ego_item_list_inf).isinsubcomp = 0
461 AND Nvl(BUNDLE_ID,0) = Nvl(TREAT( l_item_table(item) AS Ego_item_list_inf).BUNDLE_ID,0);
462
463
464
465 FOR i IN l_item_table.first .. l_item_table.last
466 LOOP
467 l_item_asso_entity_count := l_item_asso_entity_count + SQL%BULK_ROWCOUNT(i);
468 END LOOP;
469 fnd_file.put_line(fnd_file.Log,'No.of Item Associations deleted are '||l_item_asso_entity_count);
470
471
472
473 END;
474
475
476 PROCEDURE batch_purge(err_buff OUT NOCOPY VARCHAR2,
477 ret_code OUT NOCOPY VARCHAR2,
478 p_batch_id IN NUMBER,
479 p_purge_criteria IN varchar2) IS
480
481 l_batch_type VARCHAR2(50);
482 stm_num NUMBER := 0;
483
484 BEGIN
485 stm_num :=1;
486
487
488
489 SELECT BATCH_TYPE INTO l_batch_type FROM EGO_IMPORT_BATCHES_B WHERE BATCH_ID=p_batch_id;
490
491 fnd_file.put_line(fnd_file.Log,'Purge Program run for Batch ID:'||p_batch_id || '.');
492 fnd_file.put_line(fnd_file.Log,'Batch type is '||l_batch_type||'.');
493 fnd_file.put_line(fnd_file.Log,'Purge Criteria is '|| p_purge_criteria||'.');
494 fnd_file.put_line(fnd_file.Log,' ');
495
496
497 IF p_purge_criteria='ALL' THEN
498
499 stm_num :=2;
500
501 Purge_All(p_batch_id,ret_code,err_buff);
502
503 ELSIF l_batch_type='EGO_ITEM' AND p_purge_criteria IS NOT NULL THEN
504
505 stm_num :=3;
506
507 Item_Purge(p_batch_id,p_purge_criteria,ret_code,err_buff);
508
509 ELSIF l_batch_type='BOM_STRUCTURE' AND p_purge_criteria IS NOT NULL THEN
510
511 stm_num :=4;
512
513 Structure_Purge(p_batch_id,p_purge_criteria,ret_code,err_buff);
514
515 END IF;
516
517 EXCEPTION
518
519 WHEN OTHERS THEN
520 err_buff := 'batch_purge: stm_num = '||stm_num||'. Error msg: '||SUBSTR(SQLERRM, 1, 200);
521 ret_code := 2;--FND_API.G_RET_STS_ERROR;
522 fnd_file.put_line(fnd_file.Log,err_buff);
523
524 END batch_purge;
525
526
527 --Item Purge Procedure
528
529 PROCEDURE Item_Purge(p_batch_id IN NUMBER,p_purge_criteria IN VARCHAR2,ret_code OUT NOCOPY VARCHAR2, err_buff OUT NOCOPY VARCHAR2) IS
530
531 l_item_table cur_table;
532
533 item_num NUMBER:=0;
534
535 stm_num NUMBER := 0;
536
537 BEGIN
538
539 stm_num := 1;
540
541 OPEN l_item_list_c(p_batch_id);
542 FETCH l_item_list_c BULK COLLECT INTO l_item_table;
543 item_num:=l_item_list_c%ROWCOUNT;
544 CLOSE l_item_list_c;
545
546 stm_num := 2;
547 IF item_num>0 THEN
548
549 clear_items(p_purge_criteria,l_item_table);
550
551 COMMIT;
552 ELSE
553 fnd_file.put_line(fnd_file.Log,'No.of Item Entities deleted are 0');
554 fnd_file.put_line(fnd_file.Log,'No.of Item Revision Entities deleted are 0');
555 fnd_file.put_line(fnd_file.Log,'No.of Item People deleted are 0');
556 fnd_file.put_line(fnd_file.Log,'No.of Item Categories deleted are 0');
557 fnd_file.put_line(fnd_file.Log,'No.of Item Associations deleted are 0');
558 END IF;
559
560 EXCEPTION
561
562 WHEN OTHERS THEN
563 err_buff := 'Item_Purge: stm_num = '||stm_num||'. Error msg: '||SUBSTR(SQLERRM, 1, 200);
564 ret_code := 2;--FND_API.G_RET_STS_ERROR;
565 fnd_file.put_line(fnd_file.Log,err_buff);
566
567
568
569 END Item_Purge;
570
571
572 --structure purge procedure
573
574 PROCEDURE Structure_Purge(p_batch_id IN NUMBER, p_purge_criteria IN VARCHAR2,ret_code OUT NOCOPY VARCHAR2,err_buff OUT NOCOPY VARCHAR2) IS
575
576 l_batch_id NUMBER:= p_batch_id;
577 l_purge_criteria VARCHAR2(20):=p_purge_criteria;
578
579
580 l_item_table cur_table;
581 item_num NUMBER;
582
583 l_bom_entity_count NUMBER :=0;
584 l_comp_entity_count NUMBER :=0;
585 l_sub_comp_entity_count NUMBER :=0;
586 l_ref_comp_entity_count NUMBER :=0;
587 l_op_comp_entity_count NUMBER :=0;
588
589 l_bom_op_route_entity_count NUMBER :=0;
590 l_bom_op_seq_entity_count NUMBER := 0;
591 l_bom_op_res_entity_count NUMBER := 0;
592 l_bom_sub_op_res_entity_count NUMBER := 0;
593
594
595 --cursor for list of structures in given batch
596
597 CURSOR l_struct_list_c(batch_id_struct NUMBER,purge_criteria VARCHAR2)
598 IS
599 SELECT Ego_Structure_list_inf(
600 BATCH_ID,
601 ORGANIZATION_ID,
602 REQUEST_ID,
603 ASSEMBLY_ITEM_ID,
604 BILL_SEQUENCE_ID,
605 BUNDLE_ID,
606 ALTERNATE_BOM_DESIGNATOR ,
607 process_flag )
608 FROM
609 (
610 SELECT BATCH_ID,ORGANIZATION_ID,REQUEST_ID,ASSEMBLY_ITEM_ID,BILL_SEQUENCE_ID,BUNDLE_ID,ALTERNATE_BOM_DESIGNATOR,process_flag
611 FROM BOM_BILL_OF_MTLS_INTERFACE I
612 WHERE I.BATCH_ID =batch_id_struct
613 AND I.PROCESS_FLAG IN ( Decode(purge_criteria,'SUCCESS',7,'ERROR',3,-123456), Decode(purge_criteria,'ERROR',6,-123456) ));
614
615
616 TYPE structure_table IS TABLE OF Ego_Structure_list_inf;
617 l_structure_table structure_table:=null;
618 str_num NUMBER;
619
620 stm_num NUMBER := 0;
621
622 --cursor for list of routings in given batch
623
624 CURSOR l_routing_list_c(batch_id_route NUMBER,purge_criteria VARCHAR2)IS
625
626 SELECT Ego_route_list_inf(
627 BATCH_ID,
628 ORGANIZATION_ID,
629 REQUEST_ID,
630 ASSEMBLY_ITEM_ID,
631 ROUTING_SEQUENCE_ID,
632 process_flag,
633 ALTERNATE_ROUTING_DESIGNATOR)
634 FROM
635 ( SELECT BATCH_ID,ORGANIZATION_ID,REQUEST_ID,ASSEMBLY_ITEM_ID,ROUTING_SEQUENCE_ID,process_flag,ALTERNATE_ROUTING_DESIGNATOR
636 FROM bom_op_routings_interface I
637 WHERE I.BATCH_ID =batch_id_route
638 AND I.PROCESS_FLAG IN ( Decode(purge_criteria,'SUCCESS',7,'ERROR',3,-123456) , Decode(purge_criteria,'SUCCESS',7,'ERROR',6,-123456) )
639 );
640
641
642 TYPE routing_table IS TABLE OF Ego_route_list_inf;
643 l_routing_table routing_table:=null;
644 routing_num NUMBER := 0;
645
646
647 BEGIN
648
649 --deletion of bom records in Sructure batch
650 stm_num := 1;
651
652 OPEN l_struct_list_c(l_batch_id,l_purge_criteria);
653 FETCH l_struct_list_c BULK COLLECT INTO l_structure_table;
654 str_num:=l_struct_list_c%ROWCOUNT;
655 CLOSE l_struct_list_c;
656
657
658
659 --Start deleting records from bom tables
660
661 IF str_num >0 THEN
662
663 fnd_file.put_line(fnd_file.Log,'Start deleting Structure Entities');
664
665 --delete records from bom_sub_comps_interface
666 stm_num := 2;
667
668 FORALL comp IN l_structure_table.first .. l_structure_table.last
669
670 DELETE FROM bom_sub_comps_interface bsci
671 WHERE bsci.batch_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id
672 AND bsci.organization_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
673 AND Nvl(bsci.request_id,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
674 AND ( bsci.bill_sequence_id = bill_sequence_id
675 OR ( bsci.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id AND
676 bsci.ASSEMBLY_ITEM_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).assembly_item_id AND
677 Nvl(bsci.ALTERNATE_BOM_DESIGNATOR,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).ALTERNATE_BOM_DESIGNATOR,0)
678 AND (bsci.bill_sequence_id IS NULL OR bill_sequence_id IS NULL )
679 )
680 );
681
682
683 FOR i IN l_structure_table.first .. l_structure_table.last LOOP
684 l_sub_comp_entity_count := l_sub_comp_entity_count + SQL%BULK_ROWCOUNT(i);
685 END LOOP;
686
687
688 --delete records from bom_ref_desgs_interface
689 stm_num := 3;
690
691 FORALL comp IN l_structure_table.first .. l_structure_table.last
692
693 DELETE FROM bom_ref_desgs_interface brdi
694 WHERE brdi.batch_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id
695 AND brdi.organization_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
696 AND Nvl(brdi.request_id,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
697 AND ( brdi.bill_sequence_id = bill_sequence_id
698 OR ( brdi.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id AND
699 brdi.ASSEMBLY_ITEM_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).assembly_item_id AND
700 Nvl(brdi.ALTERNATE_BOM_DESIGNATOR,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).ALTERNATE_BOM_DESIGNATOR,0)
701 AND (brdi.bill_sequence_id IS NULL OR bill_sequence_id IS NULL )
702 )
703 );
704
705
706 FOR i IN l_structure_table.first .. l_structure_table.last LOOP
707 l_ref_comp_entity_count := l_ref_comp_entity_count + SQL%BULK_ROWCOUNT(i);
708 END LOOP;
709
710
711
712 --delete records from bom_cmp_usr_attr_interface
713 stm_num := 4;
714
715 FORALL comp IN l_structure_table.first .. l_structure_table.last
716
717 DELETE FROM bom_cmp_usr_attr_interface bcuai
718 WHERE ( bcuai.batch_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id OR bcuai.data_set_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id)
719 AND bcuai.organization_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
720 AND Nvl(bcuai.request_id,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
721 AND bcuai.bill_sequence_id = bill_sequence_id;
722
723
724
725 --delete records from bom_component_ops_interface
726 stm_num := 5;
727
728 FORALL comp IN l_structure_table.first .. l_structure_table.last
729
730 DELETE FROM bom_component_ops_interface bcoi
731 WHERE bcoi.batch_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id
732 AND bcoi.organization_id = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
733 AND Nvl(bcoi.request_id,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
734 AND ( bcoi.bill_sequence_id = bill_sequence_id
735 OR ( bcoi.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id AND
736 bcoi.ASSEMBLY_ITEM_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).assembly_item_id AND
737 Nvl(bcoi.ALTERNATE_BOM_DESIGNATOR,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).ALTERNATE_BOM_DESIGNATOR,0)
738 AND (bcoi.bill_sequence_id IS NULL OR bill_sequence_id IS NULL )
739 )
740 );
741
742
743 FOR i IN l_structure_table.first .. l_structure_table.last LOOP
744 l_op_comp_entity_count := l_op_comp_entity_count + SQL%BULK_ROWCOUNT(i);
745 END LOOP;
746
747
748 --delete records from bom_inventory_comps_interface
749 stm_num := 6;
750
751 FORALL comp IN l_structure_table.first .. l_structure_table.last
752
753 DELETE FROM bom_inventory_comps_interface bici
754 WHERE bici.BATCH_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id
755 AND bici.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
756 AND Nvl(bici.REQUEST_ID,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
757 AND Nvl(bici.BUNDLE_ID,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).bundle_id,0)
758 AND ( bici.bill_sequence_id = bill_sequence_id
759 OR ( bici.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id AND
760 bici.ASSEMBLY_ITEM_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).assembly_item_id AND
761 Nvl(bici.ALTERNATE_BOM_DESIGNATOR,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).ALTERNATE_BOM_DESIGNATOR,0) AND
762 (bici.bill_sequence_id IS NULL OR bill_sequence_id IS NULL )
763 )
764 );
765
766
767 FOR i IN l_structure_table.first .. l_structure_table.last LOOP
768 l_comp_entity_count := l_comp_entity_count + SQL%BULK_ROWCOUNT(i);
769 END LOOP;
770
771
772 --delete record from bom_bill_of_mtls_interface
773 stm_num := 7;
774
775 FORALL comp IN l_structure_table.first .. l_structure_table.last
776
777 DELETE FROM bom_bill_of_mtls_interface bbmi
778 WHERE bbmi.BATCH_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).batch_id
779 AND bbmi.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id
780 AND Nvl(bbmi.REQUEST_ID,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).request_id,0)
781 AND Nvl(bbmi.BUNDLE_ID,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).bundle_id,0)
782 AND ( bbmi.BILL_SEQUENCE_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).bill_sequence_id
783 OR ( bbmi.ORGANIZATION_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).organization_id AND
784 bbmi.ASSEMBLY_ITEM_ID = TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).assembly_item_id AND
785 nvl(bbmi.ALTERNATE_BOM_DESIGNATOR,0) = Nvl(TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).ALTERNATE_BOM_DESIGNATOR,0) AND
786 (bbmi.BILL_SEQUENCE_ID IS NULL OR TREAT(l_structure_table(comp) AS Ego_Structure_list_inf).bill_sequence_id IS NULL )
787 )
788 );
789
790
791 FOR i IN l_structure_table.first .. l_structure_table.last LOOP
792 l_bom_entity_count := l_bom_entity_count + SQL%BULK_ROWCOUNT(i);
793 END LOOP;
794
795 COMMIT;
796
797 END IF;
798
799
800 fnd_file.put_line(fnd_file.Log,'No.of Structures deleted are '||l_bom_entity_count);
801 fnd_file.put_line(fnd_file.Log,'No.of Components deleted are '||l_comp_entity_count);
802 fnd_file.put_line(fnd_file.Log,'No.of Sub-components deleted are '||l_sub_comp_entity_count);
803 fnd_file.put_line(fnd_file.Log,'No.of Ref-designators deleted are '||l_ref_comp_entity_count);
804 fnd_file.put_line(fnd_file.Log,'No.of Component operations deleted are '||l_op_comp_entity_count);
805
806
807
808 --end deleting records from bom tables
809
810
811 --Start deleting data from routing tables
812
813
814
815 OPEN l_routing_list_c(l_batch_id,l_purge_criteria);
816 FETCH l_routing_list_c BULK COLLECT INTO l_routing_table;
817 routing_num:=l_routing_list_c%ROWCOUNT;
818 CLOSE l_routing_list_c;
819
820
821
822 IF routing_num >0 THEN
823
824 --delete records from bom_op_resources_interface
825 stm_num := 8;
826
827 fnd_file.put_line(fnd_file.Log,'Start deleting Routing Entities');
828
829
830 FORALL op IN l_routing_table.first .. l_routing_table.last
831
832 DELETE FROM bom_op_resources_interface bori
833 WHERE bori.batch_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).batch_id
834 AND bori.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
835 AND bori.request_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).request_id
836 AND bori.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
837 AND ( bori.ROUTING_SEQUENCE_ID = TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID
838 OR ( bori.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
839 AND bori.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
840 AND Nvl(bori.ALTERNATE_ROUTING_DESIGNATOR,0) = Nvl(TREAT(l_routing_table(op) AS Ego_route_list_inf).alternate_routing_designator,0)
841 AND (bori.ROUTING_SEQUENCE_ID IS NULL OR TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID IS NULL)
842 )
843 );
844
845 FOR i IN l_routing_table.first .. l_routing_table.last LOOP
846 l_bom_op_res_entity_count := l_bom_op_res_entity_count + SQL%BULK_ROWCOUNT(i);
847 END LOOP;
848
849
850 --delete records from bom_sub_op_resources_interface
851 stm_num := 9;
852
853 FORALL op IN l_routing_table.first .. l_routing_table.last
854
855 DELETE FROM bom_sub_op_resources_interface bsr
856 WHERE bsr.batch_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).batch_id
857 AND bsr.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
858 AND bsr.request_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).request_id
859 AND bsr.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
860 AND ( bsr.ROUTING_SEQUENCE_ID = TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID
861 OR ( bsr.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
862 AND bsr.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
863 AND Nvl(bsr.ALTERNATE_ROUTING_DESIGNATOR,0) = Nvl(TREAT(l_routing_table(op) AS Ego_route_list_inf).alternate_routing_designator,0)
864 AND (bsr.ROUTING_SEQUENCE_ID IS NULL OR TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID IS NULL)
865 )
866 );
867
868 FOR i IN l_routing_table.first .. l_routing_table.last LOOP
869 l_bom_sub_op_res_entity_count := l_bom_sub_op_res_entity_count + SQL%BULK_ROWCOUNT(i);
870 END LOOP;
871
872
873 --delete records from bom_op_networks_interface
874 stm_num := 10;
875
876 /* DELETE FROM bom_op_networks_interface boni
877 WHERE boni.batch_id = l_op_table(op).batch_id
878 AND boni.organization_id = l_op_table(op).organization_id
879 AND boni.request_id = l_op_table(op).request_id
880 AND boni.assembly_item_id = l_op_table(op).assembly_item_id;
881 */
882
883
884 --delete records from bom_op_sequences_interface
885 stm_num := 11;
886
887 FORALL op IN l_routing_table.first .. l_routing_table.last
888
889 DELETE FROM bom_op_sequences_interface bseq
890 WHERE bseq.batch_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).batch_id
891 AND bseq.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
892 AND bseq.request_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).request_id
893 AND bseq.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
894 AND ( bseq.ROUTING_SEQUENCE_ID = TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID
895 OR ( bseq.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
896 AND bseq.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
897 AND Nvl(bseq.ALTERNATE_ROUTING_DESIGNATOR,0) = Nvl(TREAT(l_routing_table(op) AS Ego_route_list_inf).alternate_routing_designator,0)
898 AND (bseq.ROUTING_SEQUENCE_ID IS NULL OR TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID IS NULL)
899 )
900 );
901
902 FOR i IN l_routing_table.first .. l_routing_table.last LOOP
903 l_bom_op_seq_entity_count := l_bom_op_seq_entity_count + SQL%BULK_ROWCOUNT(i);
904 END LOOP;
905
906
907 --delete records from bom_op_routings_interface
908 stm_num := 12;
909
910 FORALL op IN l_routing_table.first .. l_routing_table.last
911
912 DELETE FROM bom_op_routings_interface brou
913 WHERE brou.batch_id =TREAT(l_routing_table(op) AS Ego_route_list_inf).batch_id
914 AND brou.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id
915 AND brou.request_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).request_id
916 AND brou.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id
917 AND ( brou.ROUTING_SEQUENCE_ID = TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID
918 OR ( brou.organization_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).organization_id AND
919 brou.assembly_item_id = TREAT(l_routing_table(op) AS Ego_route_list_inf).assembly_item_id AND
920 Nvl(brou.ALTERNATE_ROUTING_DESIGNATOR,0) = Nvl(TREAT(l_routing_table(op) AS Ego_route_list_inf).alternate_routing_designator,0) AND
921 (brou.ROUTING_SEQUENCE_ID IS NULL OR TREAT(l_routing_table(op) AS Ego_route_list_inf).ROUTING_SEQUENCE_ID IS NULL )
922 )
923 );
924
925 FOR i IN l_routing_table.first .. l_routing_table.last LOOP
926 l_bom_op_route_entity_count := l_bom_op_route_entity_count + SQL%BULK_ROWCOUNT(i);
927 END LOOP;
928
929
930 COMMIT;
931
932 END IF;
933
934
935 fnd_file.put_line(fnd_file.Log,'No.of Routings deleted are '||l_bom_op_route_entity_count);
936 fnd_file.put_line(fnd_file.Log,'No.of Operation Resources deleted are '||l_bom_op_res_entity_count);
937 fnd_file.put_line(fnd_file.Log,'No.of Sub-operation Resources deleted are '||l_bom_sub_op_res_entity_count);
938 fnd_file.put_line(fnd_file.Log,'No.of Operation sequences deleted are '||l_bom_op_seq_entity_count);
939
940 --end delete routing tables
941
942
943
944
945 --deletion of items in Sructure batch
946
947 stm_num := 13;
948
949 OPEN l_item_list_c(l_batch_id);
950 FETCH l_item_list_c BULK COLLECT INTO l_item_table;
951 item_num:=l_item_list_c%ROWCOUNT;
952 CLOSE l_item_list_c;
953
954 IF item_num >0 THEN
955
956 FOR item IN l_item_table.first .. l_item_table.last LOOP
957
958 SELECT Count(1) INTO l_item_table(item).isinbill FROM bom_bill_of_mtls_interface bbmi
959 WHERE bbmi.BATCH_ID = l_item_table(item).set_process_id
960 AND ( bbmi.ORGANIZATION_ID = l_item_table(item).organization_id
961 OR ( bbmi.ORGANIZATION_CODE = l_item_table(item).organization_code
962 AND ( bbmi.ORGANIZATION_CODE IS NULL OR l_item_table(item).organization_code IS NULL)
963 )
964 )
965 AND bbmi.ITEM_NUMBER = l_item_table(item).item_number;
966
967
968
969 SELECT Count(1) INTO l_item_table(item).isincomp FROM bom_inventory_comps_interface bici
970 WHERE bici.BATCH_ID = l_item_table(item).set_process_id
971 AND ( bici.ORGANIZATION_ID = l_item_table(item).organization_id
972 OR ( bici.ORGANIZATION_CODE = l_item_table(item).organization_code
973 AND ( bici.ORGANIZATION_CODE IS NULL OR l_item_table(item).organization_code IS NULL)
974 )
975 )
976 AND bici.COMPONENT_ITEM_NUMBER = l_item_table(item).item_number;
977
978
979
980 SELECT Count(1) INTO l_item_table(item).isinsubcomp FROM bom_sub_comps_interface bsci
981 WHERE bsci.BATCH_ID = l_item_table(item).set_process_id
982 AND ( bsci.ORGANIZATION_ID = l_item_table(item).organization_id
983 OR ( bsci.ORGANIZATION_CODE = l_item_table(item).organization_code
984 AND ( bsci.ORGANIZATION_CODE IS NULL OR l_item_table(item).organization_code IS NULL)
985 )
986 )
987 AND bsci.SUBSTITUTE_COMP_NUMBER = l_item_table(item).item_number;
988
989 END LOOP;
990
991 clear_items(l_purge_criteria, l_item_table);
992
993 COMMIT;
994
995 ELSE
996
997 fnd_file.put_line(fnd_file.Log,'No.of Item Entities deleted are 0');
998 fnd_file.put_line(fnd_file.Log,'No.of Item Revision Entities deleted are 0');
999 fnd_file.put_line(fnd_file.Log,'No.of Item People deleted are 0');
1000 fnd_file.put_line(fnd_file.Log,'No.of Item Categories deleted are 0');
1001 fnd_file.put_line(fnd_file.Log,'No.of Item Associations deleted are 0');
1002
1003 END IF;
1004
1005
1006 --end deleting item records in structure batch
1007
1008 EXCEPTION
1009
1010 WHEN OTHERS THEN
1011
1012 err_buff := 'Structure_Purge: stm_num = '||stm_num||'. Error msg: '||SUBSTR(SQLERRM, 1, 200);
1013 ret_code := 2;--FND_API.G_RET_STS_ERROR;
1014 fnd_file.put_line(fnd_file.Log,err_buff);
1015
1016 END Structure_Purge;
1017
1018
1019 --purge all procedure
1020
1021 PROCEDURE Purge_All(p_batch_id IN NUMBER,ret_code OUT NOCOPY VARCHAR2,err_buff OUT NOCOPY VARCHAR2 ) IS
1022
1023
1024 l_batch_type VARCHAR2(50);
1025 stm_num NUMBER := 0;
1026
1027 BEGIN
1028
1029 stm_num :=1;
1030
1031 SELECT BATCH_TYPE INTO l_batch_type FROM EGO_IMPORT_BATCHES_B WHERE BATCH_ID=p_batch_id;
1032
1033 stm_num :=2;
1034
1035 DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE WHERE set_process_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1036
1037 fnd_file.put_line(fnd_file.Log,'No.of Item Entities deleted are '||SQL%ROWCOUNT);
1038
1039 stm_num :=3;
1040
1041 DELETE FROM MTL_ITEM_REVISIONS_INTERFACE WHERE SET_PROCESS_ID = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1042
1043 fnd_file.put_line(fnd_file.Log,'No.of Item Revision Entities deleted are '||SQL%ROWCOUNT);
1044
1045
1046 stm_num :=4;
1047
1048 DELETE FROM EGO_ITEM_PEOPLE_INTF WHERE DATA_SET_ID = p_batch_id AND PROCESS_STATUS IN (3,4,6,7);
1049
1050 fnd_file.put_line(fnd_file.Log,'No.of Item People Entities deleted are '||SQL%ROWCOUNT);
1051
1052
1053 stm_num :=5;
1054
1055 DELETE FROM MTL_ITEM_CATEGORIES_INTERFACE WHERE SET_PROCESS_ID = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1056
1057 fnd_file.put_line(fnd_file.Log,'No.of Item Categories Entities deleted are '||SQL%ROWCOUNT);
1058
1059
1060 stm_num :=6;
1061
1062
1063 DELETE FROM EGO_ITEM_ASSOCIATIONS_INTF WHERE BATCH_ID = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1064
1065 fnd_file.put_line(fnd_file.Log,'No.of Item Association Entities deleted are '||SQL%ROWCOUNT);
1066
1067
1068 stm_num :=7;
1069
1070
1071 DELETE FROM EGO_AML_INTF WHERE DATA_SET_ID = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1072
1073
1074
1075 DELETE FROM EGO_ITM_USR_ATTR_INTRFC WHERE DATA_SET_ID = p_batch_id AND PROCESS_STATUS IN (3,4,6,7);
1076
1077
1078
1079
1080
1081
1082 IF l_batch_type='BOM_STRUCTURE' THEN
1083
1084 stm_num :=8;
1085
1086 DELETE bom_bill_of_mtls_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1087
1088 fnd_file.put_line(fnd_file.Log,'No.of Structures deleted are '||SQL%ROWCOUNT);
1089
1090
1091 stm_num :=9;
1092
1093 DELETE bom_inventory_comps_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1094
1095 fnd_file.put_line(fnd_file.Log,'No.of Components deleted are '||SQL%ROWCOUNT);
1096
1097
1098 stm_num :=10;
1099
1100 DELETE bom_ref_desgs_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1101
1102 fnd_file.put_line(fnd_file.Log,'No.of ref-designators deleted are '||SQL%ROWCOUNT);
1103
1104
1105 stm_num :=11;
1106
1107 DELETE bom_sub_comps_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1108
1109 fnd_file.put_line(fnd_file.Log,'No.of Sub-components deleted are '||SQL%ROWCOUNT);
1110
1111
1112 stm_num :=12;
1113
1114 DELETE bom_cmp_usr_attr_interface WHERE (batch_id = p_batch_id or data_set_id = p_batch_id) AND PROCESS_STATUS IN (3,6,7);
1115
1116
1117 stm_num :=13;
1118
1119 DELETE bom_component_ops_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1120
1121 fnd_file.put_line(fnd_file.Log,'No.of Component Operations deleted are '||SQL%ROWCOUNT);
1122
1123
1124 stm_num :=14;
1125
1126 DELETE bom_op_routings_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1127
1128 fnd_file.put_line(fnd_file.Log,'No.of Routings deleted are '||SQL%ROWCOUNT);
1129
1130
1131 stm_num :=15;
1132
1133 DELETE bom_op_resources_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1134
1135 fnd_file.put_line(fnd_file.Log,'No.of Operation Resources deleted are '||SQL%ROWCOUNT);
1136
1137
1138 stm_num :=16;
1139
1140 DELETE bom_sub_op_resources_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1141
1142 fnd_file.put_line(fnd_file.Log,'No.of Sub-operation Resources deleted are '||SQL%ROWCOUNT);
1143
1144
1145 stm_num :=17;
1146
1147 DELETE bom_op_sequences_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1148
1149 fnd_file.put_line(fnd_file.Log,'No.of Operation sequences deleted are '||SQL%ROWCOUNT);
1150
1151
1152 stm_num :=18;
1153
1154 DELETE bom_op_networks_interface WHERE batch_id = p_batch_id AND PROCESS_FLAG IN (3,6,7);
1155
1156 END IF;
1157
1158 COMMIT;
1159
1160 EXCEPTION
1161
1162 WHEN OTHERS THEN
1163 err_buff := 'Purge_All: stm_num = '||stm_num||'. Error msg: '||SUBSTR(SQLERRM, 1, 200);
1164 ret_code := 2; --FND_API.G_RET_STS_ERROR;
1165 fnd_file.put_line(fnd_file.Log,err_buff);
1166
1167 END Purge_All;
1168
1169
1170 END Ego_import_batch_purge_pkg;
[Home] [Help]