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