[Home] [Help]
PACKAGE BODY: APPS.INVPOPIF
Source
1 PACKAGE BODY INVPOPIF AS
2 /* $Header: INVPOPIB.pls 120.45 2008/01/25 14:52:24 ajmittal ship $ */
3 ---------------------- Package variables and constants -----------------------
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INVPOPIF';
6
7 G_SUCCESS CONSTANT NUMBER := 0;
8 G_WARNING CONSTANT NUMBER := 1;
9 G_ERROR CONSTANT NUMBER := 2;
10
11 ------------------------------------------------------------------------------
12
13 ------------------------ inopinp_open_interface_process -----------------------
14 PROCEDURE UPDATE_SYNC_RECORDS(p_set_id IN NUMBER);
15 PROCEDURE UPDATE_ITEM_CATALOG_ID(
16 p_set_id IN NUMBER
17 ,p_prog_appid IN NUMBER
18 ,p_prog_id IN NUMBER
19 ,p_request_id IN NUMBER
20 ,p_user_id IN NUMBER
21 ,p_login_id IN NUMBER
22 ,x_err_text IN OUT NOCOPY VARCHAR2);
23
24 PROCEDURE inopinp_open_interface_process(
25 ERRBUF OUT NOCOPY VARCHAR2,
26 RETCODE OUT NOCOPY NUMBER,
27 p_org_id IN NUMBER,
28 p_all_org IN NUMBER := 1,
29 p_val_item_flag IN NUMBER := 1,
30 p_pro_item_flag IN NUMBER := 1,
31 p_del_rec_flag IN NUMBER := 1,
32 p_xset_id IN NUMBER DEFAULT -999,
33 p_run_mode IN NUMBER DEFAULT 1,
34 source_org_id IN NUMBER DEFAULT -999 /*Added for bug 6372595. Adds the functionality for looping over the master default assignment
35 when the import program is called from the copy organization program*/)
36 IS
37 ret_status NUMBER;
38 err_text VARCHAR2(2000);
39
40 l_pro_flag_3 NUMBER;
41
42 BEGIN
43
44 RETCODE := G_SUCCESS;
45
46 FND_FILE.put_line (FND_FILE.log, 'Import Items');
47 FND_FILE.put_line (FND_FILE.log, '--------------------------------------------------------------------------------');
48 FND_FILE.put_line (FND_FILE.log, 'Argument 1 (ORG_ID) = '||p_org_id);
49 FND_FILE.put_line (FND_FILE.log, 'Argument 2 (ALL_ORG) = '||p_all_org);
50 FND_FILE.put_line (FND_FILE.log, 'Argument 3 (VAL_ITEM_FLAG) = '||p_val_item_flag);
51 FND_FILE.put_line (FND_FILE.log, 'Argument 4 (PRO_ITEM_FLAG) = '||p_pro_item_flag);
52 FND_FILE.put_line (FND_FILE.log, 'Argument 5 (DEL_REC_FLAG) = '||p_del_rec_flag);
53 FND_FILE.put_line (FND_FILE.log, 'Argument 6 (PROCESS_SET) = '||p_xset_id);
54 FND_FILE.put_line (FND_FILE.log, 'Argument 7 (MODE) = '||p_run_mode);
55 FND_FILE.put_line (FND_FILE.log, '--------------------------------------------------------------------------------');
56 FND_FILE.put_line (FND_FILE.log, ' ');
57
58 IF p_xset_id IS NULL THEN
59
60 IF p_run_mode = 1 THEN
61
62 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
63 SET SET_PROCESS_ID = -999
64 WHERE PROCESS_FLAG = 1
65 AND TRANSACTION_TYPE in ('CREATE','Create','create');
66
67 UPDATE MTL_ITEM_REVISIONS_INTERFACE
68 SET SET_PROCESS_ID = -999
69 WHERE PROCESS_FLAG = 1
70 AND TRANSACTION_TYPE in ('CREATE','Create','create');
71
72 ELSIF p_run_mode = 2 THEN
73
74 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
75 SET SET_PROCESS_ID = -999
76 WHERE PROCESS_FLAG = 1
77 AND TRANSACTION_TYPE in ('UPDATE','Update','update');
78
79 UPDATE MTL_ITEM_REVISIONS_INTERFACE
80 SET SET_PROCESS_ID = -999
81 WHERE PROCESS_FLAG = 1
82 AND TRANSACTION_TYPE in ('UPDATE','Update','update');
83
84 ELSIF p_run_mode = 3 THEN
85
86 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
87 SET SET_PROCESS_ID = -999
88 WHERE PROCESS_FLAG = 1
89 AND TRANSACTION_TYPE in ('SYNC','Sync','sync');
90
91 UPDATE MTL_ITEM_REVISIONS_INTERFACE
92 SET SET_PROCESS_ID = -999
93 WHERE PROCESS_FLAG = 1
94 AND TRANSACTION_TYPE in ('SYNC','Sync','sync');
95
96 END IF;
97
98 END IF;
99
100 BEGIN
101 ret_status := INVPOPIF.inopinp_open_interface_process(
102 org_id => p_org_id,
103 all_org => p_all_org,
104 val_item_flag => p_val_item_flag,
105 pro_item_flag => p_pro_item_flag,
106 del_rec_flag => p_del_rec_flag,
107 prog_appid => FND_GLOBAL.prog_appl_id,
108 prog_id => FND_GLOBAL.conc_program_id,
109 request_id => FND_GLOBAL.conc_request_id,
110 user_id => FND_GLOBAL.user_id,
111 login_id => FND_GLOBAL.login_id,
112 err_text => err_text,
113 xset_id => NVL(p_xset_id,-999),
114 run_mode => p_run_mode,
115 source_org_id => source_org_id);
116
117 SELECT count(*) INTO l_pro_flag_3
118 FROM mtl_system_items_interface
119 WHERE process_flag = 3
120 AND request_id = FND_GLOBAL.conc_request_id
121 AND rownum = 1;
122
123 IF l_pro_flag_3 > 0 THEN
124 FND_FILE.put_line (FND_FILE.log, 'Validation errors occured during Import Item');
125 FND_FILE.put_line (FND_FILE.log, 'Refer to table MTL_INTERFACE_ERRORS to access validation errors');
126 ERRBUF := 'Validation errors occured during Import Item';
127 RETCODE := G_WARNING;
128 END IF;
129
130 IF ret_status <> 0 THEN
131 FND_FILE.put_line (FND_FILE.log, 'Exceptions occured during Import Item');
132 FND_FILE.put_line (FND_FILE.log, err_text);
133 ERRBUF := err_text;
134 RETCODE := G_ERROR;
135 END IF;
136
137 EXCEPTION
138 WHEN others THEN
139 FND_FILE.put_line (FND_FILE.log, 'Exceptions occured during Import Item');
140 FND_FILE.put_line (FND_FILE.log, SQLERRM);
141 ERRBUF := SQLERRM;
142 RETCODE := G_ERROR;
143 END;
144
145 END inopinp_open_interface_process;
146
147
148 FUNCTION inopinp_open_interface_process (
149 org_id NUMBER,
150 all_org NUMBER := 1,
151 val_item_flag NUMBER := 1,
152 pro_item_flag NUMBER := 1,
153 del_rec_flag NUMBER := 1,
154 prog_appid NUMBER := -1,
155 prog_id NUMBER := -1,
156 request_id NUMBER := -1,
157 user_id NUMBER := -1,
158 login_id NUMBER := -1,
159 err_text IN OUT NOCOPY VARCHAR2,
160 xset_id IN NUMBER DEFAULT -999,
161 default_flag IN NUMBER DEFAULT 1,
162 commit_flag IN NUMBER DEFAULT 1,
163 run_mode IN NUMBER DEFAULT 1,
164 source_org_id IN NUMBER DEFAULT -999)
165 RETURN INTEGER IS
166
167
168 ret_code NUMBER := 0;
169 ret_code_create NUMBER := 0;
170 ret_code_update NUMBER := 0;
171 p_flag NUMBER := 0;
172 ret_code_grp NUMBER := 0;
173 dumm_status NUMBER;
174 LOGGING_ERR EXCEPTION;
175 req_id NUMBER := request_id;
176 mtl_count NUMBER := 0;
177 mtli_count NUMBER := 0;
178 err_msg VARCHAR2(300);
179 l_return_status VARCHAR2(1);
180 l_msg_data VARCHAR2(2000);
181 l_msg_count NUMBER;
182
183 CURSOR lock_rows IS
184 select rowid
185 from mtl_system_items_interface
186 where set_process_id = xset_id
187 for update;
188
189 CURSOR lock_revs IS
190 select rowid
191 from mtl_item_revisions_interface
192 where set_process_id = xset_id
193 for update;
194
195 CURSOR update_org_id IS
196 select rowid, transaction_id
197 from mtl_system_items_interface
198 where organization_id is NULL
199 and set_process_id = xset_id
200 and process_flag = 1;
201
202 CURSOR update_org_id_revs IS
203 select rowid, transaction_id
204 from mtl_item_revisions_interface
205 where organization_id is NULL
206 and set_process_id = xset_id
207 and process_flag = 1;
208
209 CURSOR c_master_items(cp_transaction_type VARCHAR2) IS
210 SELECT COUNT(*)
211 FROM mtl_system_items_interface msii
212 ,mtl_parameters mp1
213 WHERE set_process_id = xset_id
214 AND transaction_type = cp_transaction_type
215 AND process_flag in (1,2,4)
216 AND mp1.master_organization_id = msii.organization_id
217 AND ROWNUM = 1;
218
219 CURSOR c_master_revs(cp_transaction_type VARCHAR2) IS
220 SELECT count(*)
221 FROM mtl_item_revisions_interface msii
222 ,mtl_parameters mp1
223 WHERE set_process_id = xset_id
224 AND transaction_type = cp_transaction_type
225 AND process_flag in (1,2,4)
226 AND mp1.master_organization_id = msii.organization_id
227 AND ROWNUM = 1;
228
229 --: Bug 6158936
230 --: Child counts
231 CURSOR c_child_items(cp_transaction_type VARCHAR2) IS
232 SELECT count(*)
233 FROM mtl_system_items_interface
234 WHERE set_process_id = xset_id
235 AND transaction_type = cp_transaction_type
236 AND process_flag in (1,2,4)
237 AND organization_id
238 NOT IN (SELECT master_organization_id
239 FROM mtl_parameters)
240 AND ROWNUM = 1;
241
242 CURSOR c_child_revs(cp_transaction_type VARCHAR2) IS
243 SELECT count(*)
244 FROM mtl_item_revisions_interface
245 WHERE set_process_id = xset_id
246 AND transaction_type = cp_transaction_type
247 AND process_flag in (1,2,4)
248 AND organization_id
249 NOT IN (SELECT master_organization_id
250 FROM mtl_parameters)
251 AND ROWNUM = 1;
252
253 CURSOR c_interface_items(cp_transaction_type VARCHAR2) IS
254 SELECT count(*)
255 FROM mtl_system_items_interface
256 WHERE set_process_id = xset_id
257 AND transaction_type = cp_transaction_type
258 AND process_flag in (1,4);
259
260 CURSOR c_interface_revs(cp_transaction_type VARCHAR2) IS
261 SELECT count(*)
262 FROM mtl_item_revisions_interface
263 WHERE set_process_id = xset_id
264 AND transaction_type = cp_transaction_type
265 AND process_flag in (1,4);
266
267 l_processed_flag BOOLEAN := FALSE;
268
269 --2698140 : Gather stats before running the IOI
270 l_schema VARCHAR2(30);
271 l_status VARCHAR2(1);
272 l_industry VARCHAR2(1);
273 l_records NUMBER(10);
274 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
275 l_source_system_id EGO_IMPORT_BATCHES_B.source_system_id%TYPE;
276 l_import_xref_only EGO_IMPORT_OPTION_SETS.import_xref_only%TYPE;
277 l_items_bulk_rec_cnt NUMBER;
278
279 BEGIN
280 /*Added for bug 6372595*/
281 IF source_org_id <> -999
282 THEN
283 INVPOPIF.g_source_org := FALSE ;
284 END IF ;
285
286 IF l_inv_debug_level IN(101, 102) THEN
287 INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
288 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
289 END IF;
290 /*
291 ** Make sure transaction type is in upper case
292 */
293 --Start 2698140 : Gather stats before running the IOI
294 --When called through GRP pac, or through PLM prog_id will be -1.
295 --IF fnd_global.conc_program_id <> -1 THEN Bug:3547401
296 IF NVL(prog_id,-1) <> -1 THEN
297
298 IF l_inv_debug_level IN(101, 102) THEN
299 INVPUTLI.info('INVPOPIF: Gathering interface table stats');
300 END IF;
301
302 --3515652: Collect stats only if no. records > 50
303 SELECT count(*) INTO l_records
304 FROM mtl_system_items_interface
305 WHERE set_process_id = xset_id
306 AND process_flag = 1;
307
308 IF (l_records > 50)
309 AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema)
310 THEN
311 IF l_schema IS NOT NULL THEN
312 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
313 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_ITEM_REVISIONS_INTERFACE');
314 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_DESC_ELEM_VAL_INTERFACE');
315 END IF;
316 END IF;
317
318 IF l_inv_debug_level IN(101, 102) THEN
319 INVPUTLI.info('INVPOPIF: Gathering interface table stats done');
320 END IF;
321
322 END IF;
323 --End 2698140 : Gather stats before running the IOI
324
325 -- Populate request_id to have a correct value in case
326 -- validation fails while Creating or Updating an Item.
327
328 -- Bug 3975408 :Changed the where clause to (1,4) of the following update.
329
330 UPDATE mtl_system_items_interface
331 SET transaction_type = UPPER(transaction_type)
332 ,request_id = req_id
333 ,transaction_id = NVL(transaction_id, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL)
334 WHERE set_process_id = xset_id
335 AND process_flag IN (1,4);
336
337 UPDATE mtl_item_revisions_interface
338 SET transaction_type = UPPER(transaction_type)
339 ,request_id = req_id
340 WHERE set_process_id = xset_id
341 AND process_flag IN (1,4);
342
343 --SYNC: IOI to support SYNC operation.
344 UPDATE mtl_system_items_interface msii
345 SET process_flag = -888
346 WHERE ( transaction_type NOT IN ('CREATE', 'UPDATE','SYNC')
347 OR transaction_type IS NULL OR set_process_id >= 900000000000)
348 AND set_process_id = xset_id;
349
350 -- Rev UPDATE is not supported
351 -- Start: 2808277 Supporting Item Revision Update
352 -- SYNC: IOI to support SYNC operation.
353 UPDATE mtl_item_revisions_interface
354 SET process_flag = -888
355 WHERE ( transaction_type NOT IN ('CREATE', 'UPDATE','SYNC')
356 OR transaction_type IS NULL OR set_process_id >= 900000000000)
357 AND set_process_id = xset_id;
358
359 -- End: 2808277 Supporting Item Revision Update
360
361 -- Assign missing organization_id from organization_code
362
363 update MTL_SYSTEM_ITEMS_INTERFACE MSII
364 set MSII.organization_id =
365 ( select MP.organization_id
366 from MTL_PARAMETERS MP
367 where MP.organization_code = MSII.organization_code
368 )
369 where MSII.organization_id is NULL
370 and MSII.set_process_id = xset_id
371 and MSII.process_flag = 1;
372
373 update MTL_ITEM_REVISIONS_INTERFACE MIRI
374 set miri.template_id =
375 ( select template_id
376 FROM mtl_item_templates_vl
377 WHERE template_name = miri.template_name
378 )
379 where miri.template_id IS NULL
380 and miri.template_name IS NOT NULL
381 and miri.set_process_id = xset_id
382 and miri.process_flag = 1;
383
384
385 update MTL_ITEM_REVISIONS_INTERFACE MIRI
386 set MIRI.organization_id =
387 ( select MP.organization_id
388 from MTL_PARAMETERS MP
389 where MP.organization_code = MIRI.organization_code
390 )
391 where MIRI.organization_id is NULL
392 and MIRI.set_process_id = xset_id
393 and MIRI.process_flag = 1;
394
395 --Bug: 3614120 Making sure that revision code is in upper case.
396 update MTL_ITEM_REVISIONS_INTERFACE MIRI
397 set MIRI.REVISION = UPPER(MIRI.REVISION)
398 WHERE MIRI.set_process_id = xset_id
399 AND MIRI.process_flag=1;
400
401 -- When organization id is missing, update process_flag, and log an error
402 FOR cr IN update_org_id LOOP
403 dumm_status := INVPUOPI.mtl_log_interface_err(
404 -1,
405 user_id,
406 login_id,
407 prog_appid,
408 prog_id,
409 request_id,
410 cr.transaction_id,
411 'INVPOPIF: Invalid Organization ID',
412 'ORGANIZATION_ID',
413 'MTL_SYSTEM_ITEMS_INTERFACE',
414 'INV_IOI_ORG_NO_EXIST',
415 err_text);
416 if dumm_status < 0 then
417 raise LOGGING_ERR;
418 end if;
419
420 update mtl_system_items_interface
421 set process_flag = 3
422 where rowid = cr.rowid ;
423
424 END LOOP;
425
426 FOR cr IN update_org_id_revs LOOP
427 dumm_status := INVPUOPI.mtl_log_interface_err (
428 -1,
429 user_id,
430 login_id,
431 prog_appid,
432 prog_id,
433 request_id,
434 cr.transaction_id,
435 'INVPOPIF: Invalid Organization ID',
436 'ORGANIZATION_ID',
437 'MTL_ITEM_REVISIONS_INTERFACE',
438 'INV_IOI_ORG_NO_EXIST',
439 err_text);
440 if dumm_status < 0 then
441 raise LOGGING_ERR;
442 end if;
443
444 UPDATE mtl_item_revisions_interface
445 SET process_flag = 3
446 WHERE rowid = cr.rowid;
447
448 END LOOP;
449
450 /* Bug 5738958
451 ** Update Item Status to pending for ITEM CREATE rows in a
452 ** ICC with NIR enabled. This will prevent Active status
453 ** to be defaulted and subsequently applied.
454
455 R12C : Changing the New Item Req Reqd = 'Y' sub-query for hierarchy enabled Catalogs */
456 --6521101 - Pending status updation for master recs only
457 UPDATE mtl_system_items_interface msii
458 SET msii.INVENTORY_ITEM_STATUS_CODE = 'Pending'
459 WHERE (msii.organization_id = org_id OR all_Org = 1)
460 AND msii.INVENTORY_ITEM_STATUS_CODE IS NULL
461 AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
462 AND msii.process_flag = 1
463 AND msii.set_process_id = xset_id
464 AND msii.TRANSACTION_TYPE = 'CREATE'
465 AND EXISTS (SELECT NULL
466 FROM MTL_PARAMETERS PARAM
467 WHERE PARAM.ORGANIZATION_ID = MSII.ORGANIZATION_ID
468 AND PARAM.MASTER_ORGANIZATION_ID = PARAM.ORGANIZATION_ID)
469 AND 'Y' =
470 ( SELECT ICC.NEW_ITEM_REQUEST_REQD
471 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
472 WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
473 AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
474 AND ROWNUM = 1
475 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
476 START WITH ICC.ITEM_CATALOG_GROUP_ID = msii.ITEM_CATALOG_GROUP_ID ); --R12C
477
478 --SYNC: IOI to support SYNC operation.
479 IF run_mode = 3 THEN
480 --3018673: Start of bug fix.
481 UPDATE mtl_system_items_interface msii
482 SET process_flag = process_flag + 20000
483 WHERE transaction_type IN ('CREATE','UPDATE')
484 AND process_flag < 20000
485 AND set_process_id = xset_id;
486
487 UPDATE mtl_item_revisions_interface
488 SET process_flag = process_flag + 20000
489 WHERE transaction_type IN ('CREATE','UPDATE')
490 AND process_flag < 20000
491 AND set_process_id = xset_id;
492 --3018673: End of bug fix.
493 END IF;
494
495 --4682579
496 IF run_mode IN (3,2,0) THEN
497 UPDATE_SYNC_RECORDS(p_set_id => xset_id);
498 END IF;
499
500 IF (run_mode IN (1,3,0)) THEN --{ /* transaction_type IN 'CREATE' 'SYNC' */
501
502 l_processed_flag := TRUE;
503
504 UPDATE mtl_system_items_interface msii
505 SET process_flag = process_flag + 30000
506 WHERE transaction_type IN ('UPDATE','SYNC') --3018673
507 AND process_flag < 30000
508 AND set_process_id = xset_id;
509
510 UPDATE mtl_item_revisions_interface
511 SET process_flag = process_flag + 30000
512 WHERE transaction_type IN ('UPDATE','SYNC') --3018673
513 AND process_flag < 30000
514 AND set_process_id = xset_id;
515
516 IF (all_org = 1) THEN --{
517 OPEN c_master_items(cp_transaction_type=>'CREATE');
518 FETCH c_master_items INTO mtl_count;
519 CLOSE c_master_items;
520
521 OPEN c_master_revs(cp_transaction_type=>'CREATE');
522 FETCH c_master_revs INTO mtli_count;
523 CLOSE c_master_revs;
524
525
526 /* Added the below If condition so that if no records are present in the
527 interface table for creating master org Items then we can skip calling of
528 inopinp_OI_process_create for the master org */
529
530 IF (mtl_count <> 0 or mtli_count <> 0) THEN
531
532 UPDATE mtl_system_items_interface msii
533 SET process_flag = process_flag + 60000
534 WHERE transaction_type = 'CREATE'
535 AND process_flag < 60000
536 AND set_process_id = xset_id
537 AND not exists (select mp1.organization_id
538 from mtl_parameters mp1
539 where msii.organization_id = mp1.master_organization_id);
540
541 UPDATE mtl_item_revisions_interface miri
542 SET process_flag = process_flag + 60000
543 WHERE transaction_type = 'CREATE'
544 AND process_flag < 60000
545 AND set_process_id = xset_id
546 AND not exists (select mp1.organization_id
547 from mtl_parameters mp1
548 where miri.organization_id = mp1.master_organization_id);
549
550 --Creating Master Items
551 IF l_inv_debug_level IN(101, 102) THEN
552 INVPUTLI.info('INVPOPIF all_org=1: Calling create process for masters');
553 END IF;
554 ret_code_create := INVPOPIF.inopinp_OI_process_create (
555 NULL
556 ,1
557 ,val_item_flag
558 ,pro_item_flag
559 ,del_rec_flag
560 ,prog_appid
561 ,prog_id
562 ,request_id
563 ,user_id
564 ,login_id
565 ,err_text
566 ,xset_id
567 ,commit_flag
568 ,default_flag);
569
570 UPDATE mtl_system_items_interface msii
571 SET process_flag = process_flag - 60000
572 WHERE transaction_type = 'CREATE'
573 AND process_flag >= 60000
574 AND set_process_id = xset_id;
575
576 UPDATE mtl_item_revisions_interface
577 SET process_flag = process_flag - 60000
578 WHERE transaction_type = 'CREATE'
579 AND process_flag >= 60000
580 AND set_process_id = xset_id;
581 END IF;
582
583 --Master item records are processed above, now time for childs
584 --All master records will be having process flag as 3, 7. Not valid
585 --with predefaulting phase introduction. Master items will be in process flag 1
586 --We need to check only for REMAINING records with process flag in 1,4
587
588 OPEN c_child_items(cp_transaction_type => 'CREATE');
589 FETCH c_child_items INTO mtl_count;
590 CLOSE c_child_items;
591
592 OPEN c_child_revs(cp_transaction_type => 'CREATE');
593 FETCH c_child_revs INTO mtli_count;
594 CLOSE c_child_revs;
595
596 /* Added the below If condition so that if no records are present in the
597 interface table for creating child org Items then we can skip calling of
598 inopinp_OI_process_create for the child org */
599 IF (mtl_count <> 0 or mtli_count <> 0) THEN
600
601 /* R12C Bug 6158936 - All Master Items and revs will be isolated during child procesing */
602
603 UPDATE mtl_system_items_interface msii
604 SET process_flag = process_flag + 60000
605 WHERE transaction_type = 'CREATE'
606 AND process_flag < 60000
607 AND set_process_id = xset_id
608 AND EXISTS (select mp1.organization_id /*BUG 6158936*/
609 from mtl_parameters mp1
610 where msii.organization_id = mp1.master_organization_id);
611
612 UPDATE mtl_item_revisions_interface miri
613 SET process_flag = process_flag + 60000
614 WHERE transaction_type = 'CREATE'
615 AND process_flag < 60000
616 AND set_process_id = xset_id
617 AND EXISTS (select mp1.organization_id /*BUG 6158936*/
618 from mtl_parameters mp1
619 where miri.organization_id = mp1.master_organization_id);
620
621 IF l_inv_debug_level IN(101, 102) THEN
622 INVPUTLI.info('INVPOPIF all_org=1: Calling create process for childs');
623 END IF;
624 --Creating Child Items
625 ret_code_create := INVPOPIF.inopinp_OI_process_create (
626 NULL,
627 1,
628 val_item_flag,
629 pro_item_flag,
630 del_rec_flag,
631 prog_appid,
632 prog_id,
633 request_id,
634 user_id,
635 login_id,
636 err_text,
637 xset_id,
638 commit_flag,
639 default_flag);
640
641 /* R12C Bug 6158936 : Moving Master Items and revs back to batch */
642
643 UPDATE mtl_system_items_interface msii
644 SET process_flag = process_flag - 60000
645 WHERE transaction_type = 'CREATE'
646 AND process_flag >= 60000
647 AND set_process_id = xset_id;
648
649 UPDATE mtl_item_revisions_interface
650 SET process_flag = process_flag - 60000
651 WHERE transaction_type = 'CREATE'
652 AND process_flag >= 60000
653 AND set_process_id = xset_id;
654
655 END IF;
656
657 ELSE /* all_org <> 1 */
658 --Creating Items under a specific org.
659 OPEN c_interface_items(cp_transaction_type => 'CREATE');
660 FETCH c_interface_items INTO mtl_count;
661 CLOSE c_interface_items;
662
663 OPEN c_interface_revs(cp_transaction_type => 'CREATE');
664 FETCH c_interface_revs INTO mtli_count;
665 CLOSE c_interface_revs;
666
667 IF l_inv_debug_level IN(101, 102) THEN
668 INVPUTLI.info('INVPOPIF all_org<>1: Calling create process');
669 END IF;
670
671 IF (mtl_count <> 0 or mtli_count <> 0) THEN
672 ret_code_create := INVPOPIF.inopinp_OI_process_create (
673 org_id,
674 all_org,
675 val_item_flag,
676 pro_item_flag,
677 del_rec_flag,
678 prog_appid,
679 prog_id,
680 request_id,
681 user_id,
682 login_id,
683 err_text,
684 xset_id,
685 commit_flag,
686 default_flag);
687 END IF;
688 END IF; --}
689
690 UPDATE mtl_system_items_interface msii
691 SET process_flag = process_flag - 30000
692 WHERE transaction_type IN ('UPDATE','SYNC') --3018673
693 AND process_flag >= 30000
694 AND set_process_id = xset_id;
695
696 UPDATE mtl_item_revisions_interface
697 SET process_flag = process_flag - 30000
698 WHERE transaction_type IN ('UPDATE','SYNC') --3018673
699 AND process_flag >= 30000
700 AND set_process_id = xset_id;
701
702 END IF;
703
704 IF (run_mode IN (2,3,0)) THEN /* transaction_type IN 'UPDATE' 'SYNC' */
705
706 l_processed_flag := TRUE;
707
708 UPDATE mtl_system_items_interface msii
709 SET process_flag = process_flag + 30000
710 WHERE transaction_type IN ('CREATE','SYNC') --3018673
711 AND process_flag < 30000
712 AND set_process_id = xset_id;
713
714 UPDATE mtl_item_revisions_interface
715 SET process_flag = process_flag + 30000
716 WHERE transaction_type IN ('CREATE','SYNC') --3018673
717 AND process_flag < 30000
718 AND set_process_id = xset_id;
719
720 IF (all_org = 1) THEN --{
721
722 OPEN c_master_items(cp_transaction_type=>'UPDATE');
723 FETCH c_master_items INTO mtl_count;
724 CLOSE c_master_items;
725
726 OPEN c_master_revs(cp_transaction_type=>'UPDATE');
727 FETCH c_master_revs INTO mtli_count;
728 CLOSE c_master_revs;
729
730 IF (mtl_count <> 0 or mtli_count <> 0) THEN
731
732 UPDATE mtl_system_items_interface msii
733 SET process_flag = process_flag + 60000
734 WHERE transaction_type = 'UPDATE'
735 AND process_flag < 60000
736 AND set_process_id = xset_id
737 AND not exists (select mp1.organization_id
738 from mtl_parameters mp1
739 where msii.organization_id = mp1.master_organization_id);
740
741 UPDATE mtl_item_revisions_interface miri
742 SET process_flag = process_flag + 60000
743 WHERE transaction_type = 'UPDATE'
744 AND process_flag < 60000
745 AND set_process_id = xset_id
746 AND not exists (select mp1.organization_id
747 from mtl_parameters mp1
748 where miri.organization_id = mp1.master_organization_id);
749
750 --Update master Items.
751 ret_code_update := INVPOPIF.inopinp_OI_process_update (
752 NULL,
753 1,
754 val_item_flag,
755 pro_item_flag,
756 del_rec_flag,
757 prog_appid,
758 prog_id,
759 request_id,
760 user_id,
761 login_id,
762 err_text,
763 xset_id,
764 commit_flag,
765 default_flag);
766
767 UPDATE mtl_system_items_interface msii
768 SET process_flag = process_flag - 60000
769 WHERE transaction_type = 'UPDATE'
770 AND process_flag >= 60000
771 AND set_process_id = xset_id;
772
773 UPDATE mtl_item_revisions_interface
774 SET process_flag = process_flag - 60000
775 WHERE transaction_type = 'UPDATE'
776 AND process_flag >= 60000
777 AND set_process_id = xset_id;
778
779 END IF;
780
781 --Master item records are processed above, now time for childs
782 --All master records will have process flag as 3, 7.
783 --We need to check only for REMAINING records with process flag in 1,4
784
785 OPEN c_interface_items(cp_transaction_type => 'UPDATE');
786 FETCH c_interface_items INTO mtl_count;
787 CLOSE c_interface_items;
788
789 OPEN c_interface_revs(cp_transaction_type => 'UPDATE');
790 FETCH c_interface_revs INTO mtli_count;
791 CLOSE c_interface_revs;
792
793 IF (mtl_count <> 0 or mtli_count <> 0) THEN
794
795 /* R12C Bug 6158936 - All Master Items and revs will be isolated during child procesing */
796
797 UPDATE mtl_system_items_interface msii
798 SET process_flag = process_flag + 60000
799 WHERE transaction_type = 'UPDATE'
800 AND process_flag < 60000
801 AND set_process_id = xset_id
802 AND not exists (select mp1.organization_id
803 from mtl_parameters mp1
804 where msii.organization_id <> mp1.master_organization_id);
805
806 UPDATE mtl_item_revisions_interface miri
807 SET process_flag = process_flag + 60000
808 WHERE transaction_type = 'UPDATE'
809 AND process_flag < 60000
810 AND set_process_id = xset_id
811 AND not exists (select mp1.organization_id
812 from mtl_parameters mp1
813 where miri.organization_id <> mp1.master_organization_id);
814
815 --Updating the child records.
816 ret_code_update := INVPOPIF.inopinp_OI_process_update (
817 NULL,
818 1,
819 val_item_flag,
820 pro_item_flag,
821 del_rec_flag,
822 prog_appid,
823 prog_id,
824 request_id,
825 user_id,
826 login_id,
827 err_text,
828 xset_id,
829 commit_flag,
830 default_flag);
831
832 /* R12C Bug 6158936 : Moving Master Items and revs back to batch */
833
834 UPDATE mtl_system_items_interface msii
835 SET process_flag = process_flag - 60000
836 WHERE transaction_type = 'UPDATE'
837 AND process_flag >= 60000
838 AND set_process_id = xset_id;
839
840 UPDATE mtl_item_revisions_interface
841 SET process_flag = process_flag - 60000
842 WHERE transaction_type = 'UPDATE'
843 AND process_flag >= 60000
844 AND set_process_id = xset_id;
845
846 END IF;
847
848 ELSE -- all_org <> 1
849 --Update only org specific items
850 OPEN c_interface_items(cp_transaction_type => 'UPDATE');
851 FETCH c_interface_items INTO mtl_count;
852 CLOSE c_interface_items;
853
854 OPEN c_interface_revs(cp_transaction_type => 'UPDATE');
855 FETCH c_interface_revs INTO mtli_count;
856 CLOSE c_interface_revs;
857
858 IF (mtl_count <> 0 or mtli_count <> 0) THEN
859 ret_code_update := INVPOPIF.inopinp_OI_process_update (
860 org_id,
861 all_org,
862 val_item_flag,
863 pro_item_flag,
864 del_rec_flag,
865 prog_appid,
866 prog_id,
867 request_id,
868 user_id,
869 login_id,
870 err_text,
871 xset_id,
872 commit_flag,
873 default_flag);
874 END IF;
875
876 END IF; --}
877
878 UPDATE mtl_system_items_interface msii
879 SET process_flag = process_flag - 30000
880 WHERE transaction_type IN ('CREATE','SYNC') --3018673
881 AND process_flag >= 30000
882 AND set_process_id = xset_id;
883
884 UPDATE mtl_item_revisions_interface
885 SET process_flag = process_flag - 30000
886 WHERE transaction_type IN ('CREATE','SYNC') --3018673
887 AND process_flag >= 30000
888 AND set_process_id = xset_id;
889
890 END IF; --}
891
892 --3018673: Start of bug fix.
893 IF run_mode = 3 THEN
894
895 UPDATE mtl_system_items_interface msii
896 SET process_flag = process_flag - 20000
897 WHERE transaction_type IN ('CREATE','UPDATE')
898 AND process_flag >= 20000
899 AND set_process_id = xset_id;
900
901 UPDATE mtl_item_revisions_interface
902 SET process_flag = process_flag - 20000
903 WHERE transaction_type IN ('CREATE','UPDATE')
904 AND process_flag >= 20000
905 AND set_process_id = xset_id;
906
907 END IF;
908 --3018673: End of bug fix.
909
910 --Start : 5513065 Including xref import into same transaction context of item+rev
911 BEGIN
912 SELECT batch.source_system_id, NVL(opt.import_xref_only,'N')
913 INTO l_source_system_id, l_import_xref_only
914 FROM ego_import_batches_b batch
915 ,ego_import_option_sets opt
916 WHERE batch.batch_id = xset_id
917 AND batch.batch_id = opt.batch_id;
918 EXCEPTION
919 WHEN OTHERS THEN
920 l_source_system_id := EGO_IMPORT_PVT.get_pdh_source_system_id;
921 l_import_xref_only := 'N';
922 END;
923
924 IF NOT(l_source_system_id <> EGO_IMPORT_PVT.get_pdh_source_system_id AND l_import_xref_only = 'Y') THEN
925
926 --Calling Demerge_Batch_After_Import
927 IF l_inv_debug_level IN(101, 102) THEN
928 INVPUTLI.info('Calling EGO_IMPORT_PVT.Demerge_Batch_After_Import');
929 END IF;
930 EGO_IMPORT_PVT.Demerge_Batch_After_Import(
931 ERRBUF => err_text
932 ,RETCODE => ret_code
933 ,p_batch_id => xset_id);
934 IF l_inv_debug_level IN(101, 102) THEN
935 INVPUTLI.info('Returned EGO_IMPORT_PVT.Demerge_Batch_After_Import '||ret_code);
936 INVPUTLI.info(err_text);
937 END IF;
938
939 --Calling source system xref bulkloader
940 IF l_inv_debug_level IN(101, 102) THEN
941 INVPUTLI.info('Calling EGO_IMPORT_PVT.Process_SSXref_Intf_Rows');
942 END IF;
943
944 EGO_IMPORT_PVT.Process_SSXref_Intf_Rows(
945 ERRBUF => err_text
946 ,RETCODE => ret_code
947 ,p_data_set_id => xset_id);
948
949 IF l_inv_debug_level IN(101, 102) THEN
950 INVPUTLI.info('Returned EGO_IMPORT_PVT.Process_SSXref_Intf_Rows '||ret_code);
951 INVPUTLI.info(err_text);
952 END IF;
953
954 END IF;
955
956 IF (commit_flag = 1 ) THEN
957 commit;
958 END IF;
959
960 --End : 5513065 Including xref import into same transaction context of item+rev
961
962
963 IF NOT l_processed_flag THEN
964 ret_code := 1;
965 END IF;
966
967 IF l_inv_debug_level IN(101, 102) THEN
968 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Before Sync IM Index');
969 END IF;
970
971 --Start : Sync iM index changes
972 IF pro_item_flag = 1 --Bug 4667985
973 THEN
974 IF commit_flag = 1 THEN
975 INV_ITEM_PVT.SYNC_IM_INDEX;
976 END IF;
977 --Bug 4667985 Moving the code to Synch Up eni_oltp_item_star table here
978 -- from Create and Update calls
979 --
980 -- Sync processed rows with item star table
981 --
982 IF l_inv_debug_level IN(101, 102) THEN
983 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
984 END IF;
985
986 --Bug: 2718703 checking for ENI product before calling their package
987 --This check has been moved to INV_ENI_ITEMS_STAR_PKG
988 BEGIN
989 INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI(
990 p_api_version => 1.0
991 ,p_init_msg_list => FND_API.g_TRUE
992 ,p_set_process_id => xset_id
993 ,x_return_status => l_return_status
994 ,x_msg_count => l_msg_count
995 ,x_msg_data => l_msg_data);
996
997
998 IF NOT ( l_return_status = FND_API.g_RET_STS_SUCCESS ) THEN
999 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Synch Up of ENI tables failed');
1000 dumm_status := INVPUOPI.mtl_log_interface_err (
1001 ORG_ID => -1,
1002 USER_ID =>user_id,
1003 LOGIN_ID =>login_id,
1004 PROG_APPID =>prog_appid,
1005 PROG_ID =>prog_id,
1006 REQ_ID =>request_id,
1007 TRANS_ID =>-1,
1008 ERROR_TEXT =>l_msg_data,
1009 P_COLUMN_NAME =>NULL,
1010 TBL_NAME =>'ENI_OLTP_ITEM_STAR',
1011 MSG_NAME =>'INV_IOI_ERR',
1012 ERR_TEXT =>err_text);
1013
1014 if ( dumm_status < 0 ) then
1015 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Logging Error');
1016 end if;
1017 END IF; --l_return_status
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 l_msg_data := 'Unhandled Excpetion in INV_ENI_ITEMS_STAR_PKG: ';
1021 l_msg_data := l_msg_data || SQLERRM;
1022 dumm_status := INVPUOPI.mtl_log_interface_err (
1023 ORG_ID => -1,
1024 USER_ID =>user_id,
1025 LOGIN_ID =>login_id,
1026 PROG_APPID =>prog_appid,
1027 PROG_ID =>prog_id,
1028 REQ_ID =>request_id,
1029 TRANS_ID =>-1,
1030 ERROR_TEXT =>l_msg_data,
1031 P_COLUMN_NAME =>NULL,
1032 TBL_NAME =>'ENI_OLTP_ITEM_STAR',
1033 MSG_NAME =>'INV_IOI_ERR',
1034 ERR_TEXT =>err_text);
1035
1036 END;
1037
1038 IF l_inv_debug_level IN(101, 102) THEN
1039 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Start Raising Business Events');
1040 END IF;
1041
1042 --R12: Business Event Enhancement:
1043 --Raise events for EGO Bulk Load and Excel Import
1044 IF (request_id <> -1 ) THEN
1045
1046 --Populate Item Bulkload Recs for items and revisions
1047 BEGIN
1048 IF l_inv_debug_level IN(101, 102) THEN
1049 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Item');
1050 END IF;
1051
1052 INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1053 REQUEST_ID
1054 ,ENTITY_TYPE
1055 ,INVENTORY_ITEM_ID
1056 ,ORGANIZATION_ID
1057 ,TRANSACTION_TYPE
1058 ,CREATION_DATE
1059 ,CREATED_BY
1060 ,LAST_UPDATE_DATE
1061 ,LAST_UPDATED_BY
1062 ,LAST_UPDATE_LOGIN)
1063 (SELECT
1064 msi.REQUEST_ID
1065 ,'ITEM'
1066 ,msi.INVENTORY_ITEM_ID
1067 ,msi.ORGANIZATION_ID
1068 ,msi.TRANSACTION_TYPE
1069 ,NVL(msi.CREATION_DATE, SYSDATE)
1070 ,NVL(msi.CREATED_BY, -1)
1071 ,NVL(msi.LAST_UPDATE_DATE, SYSDATE)
1072 ,NVL(msi.LAST_UPDATED_BY, -1)
1073 ,msi.LAST_UPDATE_LOGIN
1074 FROM mtl_system_items_interface msi
1075 WHERE msi.request_id = request_id
1076 and msi.set_process_id = xset_id
1077 and msi.process_flag = 7
1078 /* Bug 6139403 Do not raise BE for fake rows*/
1079 and nvl(msi.confirm_status,'isnull')
1080 not in ('CFC', 'CFM', 'FMR', 'UFN', 'UFS', 'UFM', 'FK', 'FEX'));
1081
1082 l_items_bulk_rec_cnt := SQL%ROWCOUNT;
1083
1084 -- Raise for IOI and EGO Bulkload both
1085 IF ( SQL%ROWCOUNT > 0 ) THEN
1086 BEGIN
1087 INV_ITEM_EVENTS_PVT.Raise_Events(
1088 p_request_id => request_id
1089 ,p_xset_id => xset_id
1090 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_BULKLOAD_EVENT'
1091 ,p_dml_type => 'BULK');
1092
1093 IF l_inv_debug_level IN(101, 102) THEN
1094 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Item Bulkload Event');
1095 END IF;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising Item Change Event',1,240);
1100 IF l_inv_debug_level IN(101, 102) THEN
1101 INVPUTLI.info(err_msg);
1102 END IF;
1103 END;
1104 END IF;
1105
1106
1107 IF l_inv_debug_level IN(101, 102) THEN
1108 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Revision.');
1109 END IF;
1110
1111 INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1112 REQUEST_ID
1113 ,ENTITY_TYPE
1114 ,INVENTORY_ITEM_ID
1115 ,ORGANIZATION_ID
1116 ,REVISION_ID
1117 ,TRANSACTION_TYPE
1118 ,CREATION_DATE
1119 ,CREATED_BY
1120 ,LAST_UPDATE_DATE
1121 ,LAST_UPDATED_BY
1122 ,LAST_UPDATE_LOGIN)
1123 (SELECT
1124 mir.REQUEST_ID
1125 ,'ITEM_REVISION'
1126 ,mir.INVENTORY_ITEM_ID
1127 ,mir.ORGANIZATION_ID
1128 ,mir.REVISION_ID
1129 ,mir.TRANSACTION_TYPE
1130 ,NVL(mir.CREATION_DATE, SYSDATE)
1131 ,NVL(mir.CREATED_BY, -1)
1132 ,NVL(mir.LAST_UPDATE_DATE, SYSDATE)
1133 ,NVL(mir.LAST_UPDATED_BY, -1)
1134 ,mir.LAST_UPDATE_LOGIN
1135 FROM mtl_item_revisions_interface mir
1136 WHERE mir.request_id = request_id
1137 and mir.set_process_id = xset_id
1138 and mir.process_flag = 7);
1139
1140 --Raise for revision bulkload also
1141 IF ( SQL%ROWCOUNT > 0 ) THEN
1142 BEGIN
1143 INV_ITEM_EVENTS_PVT.Raise_Events(
1144 p_request_id => request_id
1145 ,p_xset_id => xset_id
1146 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1147 ,p_dml_type => 'BULK');
1148
1149 IF l_inv_debug_level IN(101, 102) THEN
1150 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Revision Bulkload Event');
1151 END IF;
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising REV Change Event',1,240);
1156 IF l_inv_debug_level IN(101, 102) THEN
1157 INVPUTLI.info(err_msg);
1158 END IF;
1159 END;
1160
1161 END IF;
1162
1163
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while inserting records in MTL_ITEM_BULKLOAD_RECS',1,240);
1167 IF l_inv_debug_level IN(101, 102) THEN
1168 INVPUTLI.info(err_msg);
1169 END IF;
1170 END;
1171
1172
1173 IF ( l_items_bulk_rec_cnt > 0 ) THEN
1174 BEGIN
1175 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
1176 p_request_id => request_id
1177 ,p_xset_id => xset_id
1178 ,p_entity_type => 'ITEM'
1179 ,p_dml_type => 'BULK');
1180 IF l_inv_debug_level IN(101, 102) THEN
1181 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Calling ICX Bulkload Event');
1182 END IF;
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||'while invoking ICX APIs',1,240);
1186 IF l_inv_debug_level IN(101, 102) THEN
1187 INVPUTLI.info(err_msg);
1188 END IF;
1189 END;
1190 END IF; --l_items_bulk_rec_cnt
1191
1192 END IF; --request_id <> -1
1193 --R12: Business Event Enhancement:
1194 --Raise events for EGO Bulk Load and Excel Import
1195 END IF; -- pro_item_flag
1196 --End : Sync iM index changes
1197
1198 --Delete processed records from IOI tables.
1199 --Bug: 5473976 Rows will not be deleted if control is coming from Import Workbench
1200 IF (del_rec_flag = 1 AND (NVL(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'!') <> 'EGO_ITEM_BULKLOAD')) THEN
1201 IF l_inv_debug_level IN(101, 102) THEN
1202 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
1203 END IF;
1204
1205 ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
1206 com_flag => commit_flag,
1207 xset_id => xset_id);
1208
1209 IF l_inv_debug_level IN(101, 102) THEN
1210 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPOPIF.indelitm_delete_item_oi: ret_code=' || ret_code);
1211 END IF;
1212
1213 ELSE
1214 /****Added for bug 5194369
1215 Delete processed rows (7) from the interface table
1216 if the record was created for CM support, and a similar row with
1217 process flag 5 exists in the interface table.
1218 ***/
1219 DELETE
1220 FROM mtl_system_items_interface msii
1221 WHERE process_flag = 7
1222 AND (inventory_item_id, organization_id, set_process_id) IN
1223 (SELECT inventory_item_id, organization_id, set_process_id
1224 FROM mtl_system_items_interface intf
1225 WHERE set_process_id = xset_id
1226 AND process_flag = 5);
1227 END IF; -- del_rec_flag = 1
1228
1229 IF (commit_flag = 1 ) THEN
1230 commit;
1231 END IF;
1232
1233 --
1234 -- Process Item Category Open Interface records
1235 --
1236
1237 IF l_inv_debug_level IN(101, 102) THEN
1238 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATEGORY_OI.process_Item_Category_records '||pro_item_flag);
1239 END IF;
1240
1241 SELECT COUNT(1) INTO mtl_count
1242 FROM mtl_item_categories_interface mici
1243 WHERE mici.SET_PROCESS_ID = xset_id
1244 AND mici.process_flag IN (1,2,4);
1245
1246 IF mtl_count > 0 THEN
1247 INV_ITEM_CATEGORY_OI.process_Item_Category_records (
1248 ERRBUF => err_text
1249 , RETCODE => ret_code
1250 , p_rec_set_id => xset_id
1251 , p_validate_rec_flag => val_item_flag
1252 , p_upload_rec_flag => pro_item_flag
1253 , p_delete_rec_flag => del_rec_flag
1254 , p_commit_flag => commit_flag
1255 , p_prog_appid => prog_appid
1256 , p_prog_id => prog_id
1257 , p_request_id => request_id
1258 , p_user_id => user_id
1259 , p_login_id => login_id);
1260 END IF;
1261
1262 IF l_inv_debug_level IN(101, 102) THEN
1263 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATEGORY_OI.process_Item_Category_records: ret_code=' || ret_code);
1264 END IF;
1265
1266 /* SET return code to that of last error, IF any */
1267
1268 IF (ret_code_create <> 0) THEN
1269 ret_code := ret_code_create;
1270 END IF;
1271
1272 IF (ret_code_update <> 0) THEN
1273 ret_code := ret_code_update;
1274 END IF;
1275
1276 --
1277 -- Process Item Catalog group element values open Interface records
1278 --
1279 IF l_inv_debug_level IN(101, 102) THEN
1280 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs');
1281 END IF;
1282
1283 SELECT COUNT(1) INTO mtl_count
1284 FROM mtl_desc_elem_val_interface mdei
1285 WHERE mdei.set_process_id = xset_id
1286 AND mdei.process_flag IN (1, 2, 4);
1287
1288 IF mtl_count > 0 THEN
1289 INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs (
1290 ERRBUF => err_text
1291 , RETCODE => ret_code_grp
1292 , p_rec_set_id => xset_id
1293 , p_upload_rec_flag => pro_item_flag
1294 , p_delete_rec_flag => del_rec_flag
1295 , p_commit_flag => commit_flag
1296 , p_prog_appid => prog_appid
1297 , p_prog_id => prog_id
1298 , p_request_id => request_id
1299 , p_user_id => user_id
1300 , p_login_id => login_id);
1301 ELSE
1302 ret_code_grp := 0;
1303 END IF;
1304
1305 IF l_inv_debug_level IN(101, 102) THEN
1306 INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs: ret_code=' || ret_code_grp);
1307 END IF;
1308
1309 IF (ret_code_grp <> 0) THEN
1310 ret_code := ret_code_grp;
1311 END IF;
1312
1313 RETURN (ret_code);
1314
1315 END inopinp_open_interface_process;
1316
1317
1318 --------------------------- inopinp_OI_process_update -------------------------
1319
1320 FUNCTION inopinp_OI_process_update
1321 (
1322 org_id NUMBER,
1323 all_org NUMBER := 1,
1324 val_item_flag NUMBER := 1,
1325 pro_item_flag NUMBER := 1,
1326 del_rec_flag NUMBER := 1,
1327 prog_appid NUMBER := -1,
1328 prog_id NUMBER := -1,
1329 request_id NUMBER := -1,
1330 user_id NUMBER := -1,
1331 login_id NUMBER := -1,
1332 err_text IN OUT NOCOPY VARCHAR2,
1333 xset_id IN NUMBER DEFAULT -999,
1334 commit_flag IN NUMBER DEFAULT 1,
1335 default_flag IN NUMBER DEFAULT 1)
1336 RETURN INTEGER IS
1337
1338 ret_code NUMBER:= 1;
1339 err_msg VARCHAR2(300);
1340 err_msg_name VARCHAR2(30);
1341 table_name VARCHAR2(30);
1342 dumm_status NUMBER;
1343 Logging_Err EXCEPTION;
1344 l_return_status VARCHAR2(1);
1345 l_msg_count NUMBER;
1346 l_msg_data VARCHAR2(2000);
1347 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
1348
1349 BEGIN
1350
1351 IF l_inv_debug_level IN(101, 102) THEN
1352 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
1353 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1354 END IF;
1355
1356 --Validate Catalog_Group_name
1357 UPDATE_ITEM_CATALOG_ID(
1358 p_set_id => xset_id
1359 ,p_prog_appid => prog_appid
1360 ,p_prog_id => prog_id
1361 ,p_request_id => request_id
1362 ,p_user_id => user_id
1363 ,p_login_id => login_id
1364 ,x_err_text => err_text);
1365
1366
1367 IF l_inv_debug_level IN(101, 102) THEN
1368 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check');
1369 END IF;
1370
1371 ret_code := INVNIRIS.change_policy_check (
1372 org_id => org_id,
1373 all_org => all_org,
1374 prog_appid => prog_appid,
1375 prog_id => prog_id,
1376 request_id => request_id,
1377 user_id => user_id,
1378 login_id => login_id,
1379 err_text => err_msg,
1380 xset_id => xset_id);
1381
1382 IF l_inv_debug_level IN(101, 102) THEN
1383 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check RETURN'||ret_code);
1384 INVPUTLI.info('INVNIRIS.change_policy_check->l'||err_msg);
1385 END IF;
1386
1387 IF (ret_code <> 0) THEN
1388 err_msg := 'INVNIRIS.change_policy_check: error in policy phase of UPDATE;' ||
1389 ' Please check mtl_interface_errors table ' || err_msg;
1390 goto ERROR_LABEL;
1391 END IF;
1392
1393 IF default_flag = 1 THEN
1394 IF l_inv_debug_level IN(101, 102) THEN
1395 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_assign_item_data_update');
1396 END IF;
1397
1398
1399
1400 ret_code := INVUPD1B.mtl_pr_assign_item_data_update (
1401 org_id => org_id,
1402 all_org => all_org,
1403 prog_appid => prog_appid,
1404 prog_id => prog_id,
1405 request_id => request_id,
1406 user_id => user_id,
1407 login_id => login_id,
1408 err_text => err_msg,
1409 xset_id => xset_id);
1410
1411 END IF;
1412
1413 IF (val_item_flag = 1) THEN
1414 IF (ret_code <> 0) THEN
1415 err_msg := 'INVPOPIF.inopinp_OI_process_update: error in ASSIGN phase of UPDATE;' ||
1416 ' Please check mtl_interface_errors table ' || err_msg;
1417 goto ERROR_LABEL;
1418 END IF;
1419
1420 --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1421 IF l_inv_debug_level IN(101, 102) THEN
1422 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1423 END IF;
1424
1425 ret_code := INVNIRIS.mtl_validate_nir_item (
1426 org_id => org_id,
1427 all_org => all_org,
1428 prog_appid => prog_appid,
1429 prog_id => prog_id,
1430 request_id => request_id,
1431 user_id => user_id,
1432 login_id => login_id,
1433 err_text => err_msg,
1434 xset_id => xset_id);
1435
1436
1437 IF l_inv_debug_level IN(101, 102) THEN
1438 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1439 END IF;
1440
1441 IF (ret_code <> 0) THEN
1442 err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
1443 ' Please check mtl_interface_errors table ' || err_msg;
1444 goto ERROR_LABEL;
1445 END IF;
1446
1447 --Bug:3777954 call ends
1448
1449 IF l_inv_debug_level IN(101, 102) THEN
1450 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_validate_item_update');
1451 END IF;
1452
1453 ret_code := INVUPD1B.mtl_pr_validate_item_update (
1454 org_id => org_id,
1455 all_org => all_org,
1456 prog_appid => prog_appid,
1457 prog_id => prog_id,
1458 request_id => request_id,
1459 user_id => user_id,
1460 login_id => login_id,
1461 err_text => err_msg,
1462 xset_id => xset_id);
1463
1464 IF (ret_code <> 0) THEN
1465 err_msg := 'INVPOPIF.inopinp_OI_process_update: error in VALIDATE phase of UPDATE;' ||
1466 ' Please check mtl_interface_errors table ' || err_msg;
1467 goto ERROR_LABEL;
1468 END IF;
1469
1470 END IF; /* validate_item_flag = 1 */
1471
1472 IF (pro_item_flag = 1) THEN
1473
1474 IF l_inv_debug_level IN(101, 102) THEN
1475 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD2B.inproit_process_item_update');
1476 END IF;
1477
1478 ret_code := INVUPD2B.inproit_process_item_update (
1479 prg_appid => prog_appid,
1480 prg_id => prog_id,
1481 req_id => request_id,
1482 user_id => user_id,
1483 login_id => login_id,
1484 error_message => err_msg,
1485 message_name => err_msg_name,
1486 table_name => table_name,
1487 xset_id => xset_id);
1488
1489 IF l_inv_debug_level IN(101, 102) THEN
1490 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
1491 END IF;
1492
1493 IF (ret_code <> 0) THEN
1494 err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
1495 ' Please check mtl_interface_errors table ' || err_msg;
1496 goto ERROR_LABEL;
1497 END IF;
1498
1499 --
1500 -- Sync processed rows with item star table
1501 --
1502
1503 IF l_inv_debug_level IN(101, 102) THEN
1504 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1505 END IF;
1506
1507 --Bug: 2718703 checking for ENI product before calling their package
1508 --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1509 /** Bug 4667985 Moved to main loop**/
1510
1511 END IF; /* pro_item_flag = 1 */
1512
1513 RETURN (0);
1514
1515 <<ERROR_LABEL>>
1516
1517 err_text := SUBSTR(err_msg, 1,240);
1518
1519 RETURN (ret_code);
1520
1521 EXCEPTION
1522
1523 WHEN OTHERS THEN
1524 err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
1525 IF l_inv_debug_level IN(101, 102) THEN
1526 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
1527 END IF;
1528 ROLLBACK;
1529 RETURN (ret_code);
1530 END inopinp_OI_process_update;
1531
1532
1533 --------------------------- inopinp_OI_process_create -------------------------
1534
1535 FUNCTION inopinp_OI_process_create
1536 (
1537 org_id NUMBER,
1538 all_org NUMBER := 1,
1539 val_item_flag NUMBER := 1,
1540 pro_item_flag NUMBER := 1,
1541 del_rec_flag NUMBER := 1,
1542 prog_appid NUMBER := -1,
1543 prog_id NUMBER := -1,
1544 request_id NUMBER := -1,
1545 user_id NUMBER := -1,
1546 login_id NUMBER := -1,
1547 err_text IN OUT NOCOPY VARCHAR2,
1548 xset_id IN NUMBER DEFAULT -999,
1549 commit_flag IN NUMBER DEFAULT 1,
1550 default_flag IN NUMBER DEFAULT 1
1551 )
1552 RETURN INTEGER IS
1553
1554 CURSOR Error_Items IS
1555 SELECT transaction_id, organization_id
1556 FROM mtl_system_items_interface
1557 WHERE process_flag = 4
1558 AND set_process_id = xset_id
1559 AND transaction_type = 'CREATE';
1560
1561 err_msg_name VARCHAR2(30);
1562 err_msg VARCHAR2(300);
1563 table_name VARCHAR2(30);
1564 ret_code NUMBER := 1;
1565 wrong_recs NUMBER := 0;
1566 create_recs NUMBER := 0;
1567 update_recs NUMBER := 0;
1568 p_flag NUMBER := 0;
1569 l_transaction_type VARCHAR2(10) := 'CREATE';
1570 dumm_status NUMBER;
1571 Logging_Err EXCEPTION;
1572 l_return_status VARCHAR2(1);
1573 l_msg_count NUMBER;
1574 l_msg_data VARCHAR2(2000);
1575 l_child_records VARCHAR2(1);
1576 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
1577 trans_id NUMBER;
1578
1579 BEGIN
1580 IF l_inv_debug_level IN(101, 102) THEN
1581 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create : begin org_id: ' || TO_CHAR(org_id));
1582 END IF;
1583
1584 IF l_inv_debug_level IN(101, 102) THEN
1585 INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1586 END IF;
1587
1588 --Validate Catalog_Group_name
1589 UPDATE_ITEM_CATALOG_ID(
1590 p_set_id => xset_id
1591 ,p_prog_appid => prog_appid
1592 ,p_prog_id => prog_id
1593 ,p_request_id => request_id
1594 ,p_user_id => user_id
1595 ,p_login_id => login_id
1596 ,x_err_text => err_text);
1597
1598 IF l_inv_debug_level IN(101, 102) THEN
1599 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPASGI.mtl_pr_assign_item_data');
1600 END IF;
1601
1602 ret_code := INVPASGI.mtl_pr_assign_item_data (
1603 org_id => org_id,
1604 all_org => all_org,
1605 prog_appid => prog_appid,
1606 prog_id => prog_id,
1607 request_id => request_id,
1608 user_id => user_id,
1609 login_id => login_id,
1610 err_text => err_msg,
1611 xset_id => xset_id,
1612 default_flag => default_flag);
1613
1614 IF l_inv_debug_level IN(101, 102) THEN
1615 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPASGI.mtl_pr_assign_item_data: ret_code=' || ret_code || ' err_msg=' || err_msg);
1616 END IF;
1617
1618 IF (ret_code <> 0) THEN
1619 err_msg := 'INVPOPIF.inopinp_OI_process_create: error in ASSIGN phase of CREATE;' ||
1620 ' Please check mtl_interface_errors table ' || err_msg;
1621 goto ERROR_LABEL;
1622 END IF;
1623
1624 IF (val_item_flag = 1) THEN
1625 --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1626
1627 IF l_inv_debug_level IN(101, 102) THEN
1628 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1629 END IF;
1630
1631 ret_code := INVNIRIS.mtl_validate_nir_item (
1632 org_id => org_id,
1633 all_org => all_org,
1634 prog_appid => prog_appid,
1635 prog_id => prog_id,
1636 request_id => request_id,
1637 user_id => user_id,
1638 login_id => login_id,
1639 err_text => err_msg,
1640 xset_id => xset_id);
1641
1642 IF l_inv_debug_level IN(101, 102) THEN
1643 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1644 END IF;
1645
1646 IF (ret_code <> 0) THEN
1647 err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of CREATE;' ||
1648 ' Please check mtl_interface_errors table ' || err_msg;
1649 goto ERROR_LABEL;
1650 END IF;
1651
1652 --Bug:3777954 call ends
1653
1654 IF l_inv_debug_level IN(101, 102) THEN
1655 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPVALI.mtl_pr_validate_item');
1656 END IF;
1657
1658 ret_code := INVPVALI.mtl_pr_validate_item (
1659 org_id => org_id,
1660 all_org => all_org,
1661 prog_appid => prog_appid,
1662 prog_id => prog_id,
1663 request_id => request_id,
1664 user_id => user_id,
1665 login_id => login_id,
1666 err_text => err_msg,
1667 xset_id => xset_id);
1668
1669 IF l_inv_debug_level IN(101, 102) THEN
1670 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPVALI.mtl_pr_validate_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1671 END IF;
1672
1673 IF (ret_code <> 0) THEN
1674 err_msg := 'INVPOPIF.inopinp_OI_process_create: error in VALIDATE phase of CREATE;'||
1675 ' Please check mtl_interface_errors table ' || err_msg;
1676 goto ERROR_LABEL;
1677 END IF;
1678
1679 END IF; -- val_item_flag = 1
1680
1681
1682 IF (pro_item_flag = 1) THEN
1683
1684 IF l_inv_debug_level IN(101, 102) THEN
1685 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPPROC.inproit_process_item');
1686 END IF;
1687
1688 ret_code := INVPPROC.inproit_process_item (
1689 prg_appid => prog_appid,
1690 prg_id => prog_id,
1691 req_id => request_id,
1692 user_id => user_id,
1693 login_id => login_id,
1694 error_message => err_msg,
1695 message_name => err_msg_name,
1696 table_name => table_name,
1697 xset_id => xset_id);
1698
1699 IF l_inv_debug_level IN(101, 102) THEN
1700 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPPROC.inproit_process_item: ret_code=' || ret_code);
1701 END IF;
1702
1703 IF (ret_code <> 0) THEN
1704
1705 --Bug 4767919 Anmurali
1706
1707 FOR ee in Error_Items LOOP
1708
1709 dumm_status := INVPUOPI.mtl_log_interface_err(
1710 ee.organization_id,
1711 user_id,
1712 login_id,
1713 prog_appid,
1714 prog_id,
1715 request_id,
1716 ee.transaction_id,
1717 err_msg,
1718 'INVENTORY_ITEM_ID',
1719 'MTL_SYSTEM_ITEMS_INTERFACE',
1720 'INV_IOI_ERR',
1721 err_msg);
1722 END LOOP;
1723
1724 UPDATE mtl_system_items_interface
1725 SET process_flag = 3
1726 WHERE process_flag = 4
1727 AND set_process_id = xset_id
1728 AND transaction_type = 'CREATE';
1729
1730 err_msg := 'INVPOPIF.inopinp_OI_process_create: error in PROCESS phase of CREATE;'||
1731 ' Please check mtl_interface_errors table ' || err_msg;
1732
1733 goto ERROR_LABEL;
1734 END IF;
1735
1736 --
1737 -- Sync processed rows with item star table
1738 --
1739 IF l_inv_debug_level IN(101, 102) THEN
1740 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1741 END IF;
1742
1743 --Bug: 2718703 checking for ENI product before calling their package
1744 --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1745 -- Call India localization API
1746 BEGIN
1747 SELECT 'Y' INTO l_child_records
1748 FROM DUAL
1749 WHERE EXISTS (SELECT NULL
1750 FROM mtl_system_items_interface msii,
1751 mtl_parameters mp
1752 WHERE transaction_type = 'CREATE'
1753 AND process_flag = 7
1754 AND set_process_id = xset_id
1755 AND msii.organization_id = mp.organization_id
1756 AND mp.organization_id <> mp.master_organization_id);
1757
1758 INV_ITEM_EVENTS_PVT.Invoke_JAI_API(
1759 p_action_type => 'IMPORT'
1760 ,p_organization_id => null
1761 ,p_inventory_item_id => null
1762 ,p_source_organization_id => null
1763 ,p_source_inventory_item_id => null
1764 ,p_set_process_id => xset_id
1765 ,p_called_from => 'INVPOPIB');
1766
1767 EXCEPTION
1768 WHEN NO_DATA_FOUND THEN
1769 NULL; --Child records not created, no need to call JAI api's
1770 WHEN OTHERS THEN
1771 err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while IL API call ',1,240);
1772 IF l_inv_debug_level IN(101, 102) THEN
1773 INVPUTLI.info(err_msg);
1774 END IF;
1775 END;
1776
1777 END IF; -- pro_item_flag = 1
1778
1779 RETURN (0);
1780
1781 <<ERROR_LABEL>>
1782 err_text := SUBSTRB(err_msg, 1,240);
1783
1784 RETURN (ret_code);
1785
1786 EXCEPTION
1787
1788 -- Parameter ret_code is defaulted to 1, which is passed
1789 -- back for oracle error in UPDATE st.
1790
1791 WHEN others THEN
1792 err_text := substr('INVPOPIF.inopinp_OI_process_create ' || SQLERRM , 1,240);
1793 IF l_inv_debug_level IN(101, 102) THEN
1794 INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: About to rollback.');
1795 END IF;
1796 ROLLBACK;
1797 RETURN (ret_code);
1798 END inopinp_OI_process_create;
1799
1800
1801 ---------------------------- indelitm_delete_item_oi --------------------------
1802
1803 FUNCTION indelitm_delete_item_oi
1804 (
1805 err_text OUT NOCOPY VARCHAR2,
1806 com_flag IN NUMBER DEFAULT 1,
1807 xset_id IN NUMBER DEFAULT -999
1808 )
1809 RETURN INTEGER
1810 IS
1811 stmt_num NUMBER;
1812 l_process_flag_7 NUMBER := 7;
1813 l_rownum NUMBER := 100000;
1814 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667453
1815 BEGIN
1816
1817 IF l_inv_debug_level IN(101, 102) THEN
1818 INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: begin');
1819 END IF;
1820
1821 stmt_num := 1;
1822
1823 LOOP
1824 DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
1825 WHERE process_flag = l_process_flag_7
1826 AND set_process_id in (xset_id, xset_id + 1000000000000)
1827 AND rownum < l_rownum;
1828
1829 EXIT WHEN SQL%NOTFOUND;
1830
1831 IF com_flag = 1 THEN
1832 commit;
1833 END IF;
1834 END LOOP;
1835
1836 stmt_num := 2;
1837
1838
1839 LOOP
1840 DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
1841 WHERE PROCESS_FLAG = l_process_flag_7
1842 AND set_process_id = xset_id
1843 AND rownum < l_rownum;
1844
1845 EXIT WHEN SQL%NOTFOUND;
1846
1847 IF com_flag = 1 THEN
1848 commit;
1849 END IF;
1850 END LOOP;
1851
1852 IF l_inv_debug_level IN(101, 102) THEN
1853 INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: end');
1854 END IF;
1855
1856
1857 RETURN (0);
1858
1859 EXCEPTION
1860
1861 WHEN OTHERS THEN
1862 err_text := SUBSTR('INVPOPIF.indelitm_delete_item_oi(' || stmt_num || ')' || SQLERRM, 1,240);
1863 RETURN (SQLCODE);
1864
1865 END indelitm_delete_item_oi;
1866
1867 --SYNC: IOI to support SYNC operation.
1868 PROCEDURE UPDATE_SYNC_RECORDS(p_set_id IN NUMBER) IS
1869
1870 CURSOR c_items_table IS
1871 SELECT rowid
1872 ,organization_id
1873 ,inventory_item_id
1874 ,segment1
1875 ,segment2
1876 ,segment3
1877 ,segment4
1878 ,segment5
1879 ,segment6
1880 ,segment7
1881 ,segment8
1882 ,segment9
1883 ,segment10
1884 ,segment11
1885 ,segment12
1886 ,segment13
1887 ,segment14
1888 ,segment15
1889 ,segment16
1890 ,segment17
1891 ,segment18
1892 ,segment19
1893 ,segment20
1894 ,item_number
1895 ,transaction_id
1896 ,transaction_type
1897 FROM mtl_system_items_interface
1898 WHERE set_process_id = p_set_id
1899 AND process_flag = 1
1900 AND (transaction_type = 'SYNC' OR
1901 (transaction_type = 'UPDATE' AND inventory_item_id IS NOT NULL AND
1902 (item_number IS NOT NULL OR
1903 SEGMENT1 IS NOT NULL OR SEGMENT2 IS NOT NULL OR SEGMENT3 IS NOT NULL OR SEGMENT4 IS NOT NULL OR
1904 SEGMENT5 IS NOT NULL OR SEGMENT6 IS NOT NULL OR SEGMENT7 IS NOT NULL OR SEGMENT8 IS NOT NULL OR
1905 SEGMENT9 IS NOT NULL OR SEGMENT10 IS NOT NULL OR SEGMENT11 IS NOT NULL OR SEGMENT12 IS NOT NULL OR
1906 SEGMENT13 IS NOT NULL OR SEGMENT14 IS NOT NULL OR SEGMENT15 IS NOT NULL OR SEGMENT16 IS NOT NULL OR
1907 SEGMENT17 IS NOT NULL OR SEGMENT18 IS NOT NULL OR SEGMENT19 IS NOT NULL OR SEGMENT20 IS NOT NULL
1908 )
1909 )
1910 )
1911 FOR UPDATE OF transaction_type;
1912
1913 CURSOR c_revision_table IS
1914 SELECT rowid
1915 ,organization_id
1916 ,inventory_item_id
1917 ,item_number
1918 ,revision_id
1919 ,revision
1920 ,transaction_id
1921 ,transaction_type
1922 FROM mtl_item_revisions_interface
1923 WHERE set_process_id = p_set_id
1924 AND process_flag = 1
1925 AND transaction_type = 'SYNC'
1926 FOR UPDATE OF transaction_type;
1927
1928 CURSOR c_item_exists(cp_item_id NUMBER,
1929 cp_org_id NUMBER) IS
1930 SELECT 1 ,concatenated_segments
1931 FROM mtl_system_items_b_kfv
1932 WHERE inventory_item_id = cp_item_id
1933 --Bug 4964023 - Adding the org id clause for org assign case
1934 AND organization_id = cp_org_id;
1935
1936 CURSOR c_fetch_by_item_number(cp_item_number MTL_SYSTEM_ITEMS_B_KFV.CONCATENATED_SEGMENTS%TYPE,
1937 cp_org_id NUMBER) IS
1938 SELECT inventory_item_id
1939 FROM mtl_system_items_b_kfv
1940 WHERE concatenated_segments = cp_item_number
1941 AND organization_id = cp_org_id;
1942
1943 /* Bug 6200383 Added one more AND condition on organization_id */
1944 CURSOR c_revision_exists(cp_item_id NUMBER,
1945 cp_rev_id NUMBER,
1946 cp_revision VARCHAR,
1947 cp_org_id NUMBER) IS
1948 SELECT 1
1949 FROM mtl_item_revisions
1950 WHERE inventory_item_id = cp_item_id
1951 AND (revision_id = cp_rev_id
1952 OR revision = cp_revision)
1953 AND organization_id = cp_org_id ;
1954
1955
1956
1957 l_item_exist NUMBER(10) := 0;
1958 l_err_text VARCHAR2(200);
1959 l_rev_exist NUMBER(10) := 0;
1960 l_status NUMBER(10):= 0;
1961 l_item_id mtl_system_items_b.inventory_item_id%TYPE;
1962 l_item_number MTL_SYSTEM_ITEMS_B_KFV.CONCATENATED_SEGMENTS%TYPE;
1963 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1964 dumm_status NUMBER;
1965 l_updateable_item_number varchar2(10);
1966 LOGGING_ERR EXCEPTION;
1967
1968 FUNCTION isMasterOrg ( cp_orgid NUMBER
1969 ) RETURN NUMBER
1970 IS
1971 l_masterOrg NUMBER;
1972 BEGIN
1973 SELECT 1
1974 INTO l_masterOrg
1975 FROM mtl_parameters
1976 WHERE master_organization_id = cp_orgid
1977 AND rownum = 1;
1978
1979 return 1;
1980
1981 EXCEPTION
1982 WHEN NO_DATA_FOUND THEN
1983 return 0;
1984 END isMasterOrg;
1985
1986 BEGIN
1987 fnd_profile.get('INV_UPDATEABLE_ITEM', l_updateable_item_number);
1988 FOR item_record IN c_items_table LOOP
1989 l_item_exist :=0;
1990 l_item_id := NULL;
1991
1992 IF item_record.inventory_item_id IS NULL THEN
1993 IF item_record.item_number IS NOT NULL THEN
1994 l_status := INVPUOPI.MTL_PR_PARSE_ITEM_NUMBER(
1995 ITEM_NUMBER =>item_record.item_number
1996 ,ITEM_ID =>item_record.inventory_item_id
1997 ,TRANS_ID =>item_record.transaction_id
1998 ,ORG_ID =>item_record.organization_id
1999 ,ERR_TEXT =>l_err_text
2000 ,P_ROWID =>item_record.rowid);
2001 ELSIF (item_record.segment1 || item_record.segment2 || item_record.segment3 || item_record.segment4 ||
2002 item_record.segment5 || item_record.segment6 || item_record.segment7 || item_record.segment8 ||
2003 item_record.segment9 || item_record.segment10 || item_record.segment11 || item_record.segment12 ||
2004 item_record.segment13 || item_record.segment14 || item_record.segment15 || item_record.segment16 ||
2005 item_record.segment17 || item_record.segment18 || item_record.segment19 || item_record.segment20 )
2006 IS NOT NULL THEN
2007 l_status := INVPUOPI.mtl_pr_parse_item_segments(
2008 P_ROW_ID => item_record.rowid,
2009 ITEM_NUMBER => item_record.item_number,
2010 ITEM_ID => item_record.inventory_item_id,
2011 ERR_TEXT => l_err_text);
2012 END IF; --ITEM NUMBER
2013 l_item_exist := INVUPD1B.EXISTS_IN_MSI(
2014 ROW_ID => item_record.rowid
2015 ,ORG_ID => item_record.organization_id
2016 ,INV_ITEM_ID => l_item_id
2017 ,TRANS_ID => item_record.transaction_id
2018 ,ERR_TEXT => l_err_text
2019 ,XSET_ID => p_set_id);
2020
2021 ELSE --INVENTORY_ITEM_ID IS NOT NULL
2022 l_item_id := item_record.inventory_item_id;
2023 OPEN c_item_exists(item_record.inventory_item_id,
2024 item_record.organization_id);
2025 FETCH c_item_exists INTO l_item_exist, l_item_number;
2026 CLOSE c_item_exists;
2027
2028 l_item_exist := NVL(l_item_exist,0);
2029
2030 IF ( item_record.transaction_type = 'UPDATE' AND l_item_exist <> 0 AND l_item_number <> item_record.item_number) THEN
2031 -- UPDATE row MSII item number is different than compared to
2032 -- MSIBKFV item number fetched using MSII inventory_item_id
2033 -- Both l_item_number and item_record.item_number cannot be NULL, hence NVL is not used
2034
2035 IF l_inv_debug_level IN(101, 102) THEN
2036 INVPUTLI.info('INVPOPIF.update sync records: Item Number update' || item_record.item_number || ' ' || l_item_number || ' ' || l_item_id);
2037 END IF;
2038
2039 IF ( l_updateable_item_number <> 'Y' ) THEN
2040 dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2041 fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2042 'INVPOPIF: Update to Item number not allowed',
2043 'ITEM NUMBER',
2044 'MTL_SYSTEM_ITEMS_INTERFACE',
2045 'INV_ITEM_NUMBER_NO_UDPATE',
2046 l_err_text);
2047 IF dumm_status < 0 THEN
2048 raise LOGGING_ERR;
2049 END IF;
2050
2051 update mtl_system_items_interface
2052 set process_flag = 3
2053 where rowid = item_record.rowid ;
2054
2055 END IF;
2056
2057 IF ( isMasterOrg(item_record.organization_id) = 0 ) THEN
2058 dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2059 fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2060 'INVPOPIF: Update to Item number not allowed in child organization',
2061 'ITEM NUMBER',
2062 'MTL_SYSTEM_ITEMS_INTERFACE',
2063 'INV_ITEM_NUMBER_ORG_NO_UDPATE',
2064 l_err_text);
2065 IF dumm_status < 0 THEN
2066 raise LOGGING_ERR;
2067 END IF;
2068
2069 update mtl_system_items_interface
2070 set process_flag = 3
2071 where rowid = item_record.rowid ;
2072
2073 END IF;
2074
2075 l_item_exist := 0;
2076 --This update might lead to duplicate ITEM NUMBER in MSIBKFV
2077 OPEN c_fetch_by_item_number(item_record.item_number, item_record.organization_id);
2078 FETCH c_fetch_by_item_number INTO l_item_exist;
2079 CLOSE c_fetch_by_item_number;
2080
2081 IF (l_item_exist <> 0 AND l_item_exist <> l_item_id) THEN
2082 IF l_inv_debug_level IN(101, 102) THEN
2083 INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
2084 END IF;
2085 dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2086 fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2087 'INVPOPIF: Update to Duplicate Item number',
2088 'ITEM NUMBER',
2089 'MTL_SYSTEM_ITEMS_INTERFACE',
2090 'INV_UPDATE_TO_EXIST_ITEM_NAME',
2091 l_err_text);
2092 IF dumm_status < 0 THEN
2093 raise LOGGING_ERR;
2094 END IF;
2095
2096 update mtl_system_items_interface
2097 set process_flag = 3
2098 where rowid = item_record.rowid ;
2099
2100 END IF;
2101 END IF;
2102 END IF; --ITEM ID
2103
2104 IF l_item_exist <> 0 THEN
2105 UPDATE mtl_system_items_interface
2106 SET transaction_type = 'UPDATE'
2107 WHERE rowid = item_record.rowid
2108 AND transaction_type = 'SYNC';
2109 ELSE
2110 UPDATE mtl_system_items_interface
2111 SET transaction_type = 'CREATE'
2112 WHERE rowid = item_record.rowid
2113 AND transaction_type = 'SYNC';
2114 END IF;
2115
2116 END LOOP;
2117
2118 FOR revision_record IN c_revision_table LOOP
2119 l_rev_exist := 0;
2120 l_item_id := NULL;
2121
2122 IF revision_record.inventory_item_id IS NOT NULL THEN
2123 l_item_id := revision_record.inventory_item_id;
2124 ELSIF revision_record.item_number is NOT NULL THEN
2125 l_status := INVPUOPI.mtl_pr_parse_flex_name (
2126 revision_record.organization_id
2127 ,'MSTK'
2128 ,revision_record.item_number
2129 ,l_item_id
2130 ,0
2131 ,l_err_text);
2132 END IF;
2133
2134 /* Bug 6200383 Added one more parameter cp_org_id to the cursor c_revision_exists */
2135 OPEN c_revision_exists(cp_item_id => l_item_id,
2136 cp_rev_id => revision_record.revision_id,
2137 cp_revision => revision_record.revision,
2138 cp_org_id => revision_record.organization_id);
2139 FETCH c_revision_exists INTO l_rev_exist;
2140 CLOSE c_revision_exists;
2141 l_rev_exist := NVL(l_rev_exist,0);
2142
2143 IF l_rev_exist = 1 THEN
2144 UPDATE mtl_item_revisions_interface
2145 SET transaction_type = 'UPDATE'
2146 WHERE rowid = revision_record.rowid;
2147 ELSE
2148 UPDATE mtl_item_revisions_interface
2149 SET transaction_type = 'CREATE'
2150 WHERE rowid = revision_record.rowid;
2151 END IF;
2152 END LOOP;
2153
2154 END UPDATE_SYNC_RECORDS;
2155 --End SYNC: IOI to support SYNC operation.
2156
2157 /*
2158 This Procedure populates ITEM_CATALOG_GROUP_ID column for IOI records
2159 where a valid ITEM_CATALOG_GROUP_NAME is provided and the ID field is NULL.
2160 If both the fields are NOT NULL no than action is taken.
2161 It also marks the records as errored if the ITEM_CATALOG_GROUP_NAME
2162 fails to validate against the Item Catalogs KFV.
2163 */
2164 PROCEDURE UPDATE_ITEM_CATALOG_ID(
2165 p_set_id IN NUMBER
2166 ,p_prog_appid IN NUMBER
2167 ,p_prog_id IN NUMBER
2168 ,p_request_id IN NUMBER
2169 ,p_user_id IN NUMBER
2170 ,p_login_id IN NUMBER
2171 ,x_err_text IN OUT NOCOPY VARCHAR2) IS
2172
2173 LOGGING_ERR EXCEPTION;
2174 CURSOR update_catg_name(p_catg_name IN VARCHAR2) IS
2175 SELECT ROWID, msii.TRANSACTION_ID
2176 FROM mtl_system_items_interface msii
2177 WHERE SET_PROCESS_ID = p_set_id
2178 AND msii.ITEM_CATALOG_GROUP_NAME = p_catg_name;
2179
2180 --Holds {Item Catalog Group Name: Item Catalog ID}
2181 TYPE Item_Catalog_Group_Type IS TABLE OF
2182 VARCHAR2(2000)
2183 INDEX BY BINARY_INTEGER;
2184 Item_Catalogs_Table Item_Catalog_Group_Type;
2185
2186 l_Item_Catalog_Group_ID
2187 mtl_item_catalog_groups_b.ITEM_CATALOG_GROUP_ID%TYPE;
2188 l_Item_Catalog_Group_Name
2189 mtl_system_items_interface.ITEM_CATALOG_GROUP_NAME%TYPE;
2190
2191 l_Item_Catalog VARCHAR2(2000);
2192 l_index INTEGER;
2193 l_dumm_status NUMBER;
2194
2195 BEGIN
2196 SELECT DISTINCT msii.ITEM_CATALOG_GROUP_NAME ||
2197 ':' || bkfv.ITEM_CATALOG_GROUP_ID
2198 BULK COLLECT INTO Item_Catalogs_Table
2199 FROM mtl_system_items_interface msii,
2200 mtl_item_catalog_groups_b_kfv bkfv
2201 WHERE msii.ITEM_CATALOG_GROUP_ID IS NULL
2202 AND msii.ITEM_CATALOG_GROUP_NAME IS NOT NULL
2203 AND msii.SET_PROCESS_ID = p_set_id
2204 AND msii.ITEM_CATALOG_GROUP_NAME = bkfv.CONCATENATED_SEGMENTS(+);
2205
2206 l_index := Item_Catalogs_Table.FIRST;
2207 WHILE l_index IS NOT NULL
2208 LOOP
2209 l_Item_Catalog := Item_Catalogs_Table(l_index);
2210 l_Item_Catalog_Group_Name := SUBSTR(l_Item_Catalog, 1,
2211 INSTR(l_Item_Catalog,':') - 1);
2212
2213 IF LENGTH(l_Item_Catalog) = INSTR(l_Item_Catalog,':') THEN
2214 --No ID is selected for this catalog name ...Mark these records as errored
2215 FOR cr IN update_catg_name(p_catg_name => l_Item_Catalog_Group_Name)
2216 LOOP
2217 l_dumm_status := INVPUOPI.mtl_log_interface_err(
2218 -1,
2219 p_user_id,
2220 p_login_id,
2221 p_prog_appid,
2222 p_prog_id,
2223 p_request_id,
2224 cr.transaction_id,
2225 'INVPOPIF: Invalid Item Catalog Group Name',
2226 'ITEM_CATALOG_GROUP_NAME',
2227 'MTL_SYSTEM_ITEMS_INTERFACE',
2228 'INV_IOI_CATG_NAME_INVALID',
2229 x_err_text);
2230 IF l_dumm_status < 0 then
2231 raise LOGGING_ERR;
2232 END IF;
2233
2234 UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2235 SET PROCESS_FLAG = 3
2236 WHERE ROWID = cr.ROWID ;
2237 END LOOP; --cr
2238
2239 ELSE --Get the ID and populate in the ITEM_CATALOG_GROUP_ID column
2240 l_Item_Catalog_Group_ID
2241 := SUBSTR(l_Item_Catalog, INSTR(l_Item_Catalog,':') +1);
2242
2243 UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2244 SET MSII.ITEM_CATALOG_GROUP_ID = l_Item_Catalog_Group_ID
2245 WHERE SET_PROCESS_ID = p_set_id
2246 AND MSII.ITEM_CATALOG_GROUP_NAME = l_Item_Catalog_Group_Name;
2247 END IF;
2248 l_index := Item_Catalogs_Table.NEXT(l_index);
2249 END LOOP;
2250
2251 END UPDATE_ITEM_CATALOG_ID;
2252
2253 END INVPOPIF;