[Home] [Help]
PACKAGE BODY: APPS.INVPAGI2
Source
1 package body INVPAGI2 as
2 /* $Header: INVPAG2B.pls 120.45.12020000.6 2013/01/23 08:20:26 lmai ship $*/
3 /*Values used in IOI to indicate an attribute update to NULL. Added for bug
4 * 6417006*/
5 g_Upd_Null_CHAR VARCHAR2(1) := '!';
6 g_Upd_Null_NUM NUMBER := -999999;
7 g_Upd_Null_DATE DATE := NULL;
8
9 function assign_item_header_recs(
10 org_id number,
11 all_org NUMBER := 2,
12 prog_appid NUMBER := -1,
13 prog_id NUMBER := -1,
14 request_id NUMBER := -1,
15 user_id NUMBER := -1,
16 login_id NUMBER := -1,
17 err_text in out NOCOPY varchar2,
18 xset_id IN NUMBER DEFAULT -999,
19 default_flag IN NUMBER DEFAULT 1
20 ) return integer is
21
22 CURSOR header is
23 select inventory_item_id,
24 organization_id,
25 organization_code,
26 item_number,
27 item_catalog_group_id,
28 copy_item_id,
29 copy_item_number,
30 copy_organization_id,
31 copy_organization_code,
32 --serial_tagging enh -- bug 9913552
33 template_id,
34 transaction_id,
35 revision,
36 cost_of_sales_account,
37 encumbrance_account,
38 sales_account,
39 expense_account,
40 segment1,
41 segment2,
42 segment3,
43 segment4,
44 segment5,
45 segment6,
46 segment7,
47 segment8,
48 segment9,
49 segment10,
50 segment11,
51 segment12,
52 segment13,
53 segment14,
54 segment15,
55 segment16,
56 segment17,
57 segment18,
58 segment19,
59 segment20,
60 set_process_id ,
61 rowid,
62 --Adding R12 C attribute changes
63 style_item_flag,
64 style_item_id,
65 style_item_number,
66 source_system_id,
67 source_system_reference
68 from MTL_SYSTEM_ITEMS_INTERFACE
69 where process_flag = 1
70 and set_process_id = xset_id
71 and ((organization_id = org_id) or (all_org = 1));
72
73 --2861248 :Populate Item Id for default revision only
74
75 ---Start: Bug fix 3051653
76 CURSOR c_get_revisions(cp_item_number VARCHAR2,cp_revision VARCHAR2,
77 cp_organization_id NUMBER) IS
78 SELECT organization_id,item_number
79 FROM mtl_item_revisions_interface
80 WHERE inventory_item_id IS NULL
81 AND item_number = cp_item_number
82 AND organization_id = cp_organization_id
83 AND revision = cp_revision
84 AND set_process_id = xset_id
85 AND process_flag = 1;
86
87 CURSOR ee is
88 select rowid ,transaction_id,inventory_item_id
89 from mtl_system_items_interface child
90 where inventory_item_id is not NULL
91 and set_process_id = xset_id
92 and process_flag = 1
93 and not exists
94 (select inventory_item_id
95 from mtl_system_items msi
96 where msi.inventory_item_id = child.inventory_item_id);
97
98 /** Bug 5192495
99 Need to select unit_of_measure column instead of unit_of_measure_tl*/
100 --3818646 : PUOM from Profile is always in US.
101 --Below cursor gets PUOM in session langauge.
102 CURSOR c_get_uom (cp_unit_measure VARCHAR2) IS
103 SELECT unit_of_measure
104 FROM mtl_units_of_measure_vl
105 WHERE uom_code IN (SELECT uom_code
106 FROM mtl_units_of_measure_tl
107 WHERE unit_of_measure =cp_unit_measure);
108
109 CURSOR c_get_Style ( cp_style_item_number IN VARCHAR2
110 ,cp_organization_id IN NUMBER) IS
111 SELECT inventory_item_id
112 FROM mtl_system_items_b_kfv
113 WHERE concatenated_segments = cp_style_item_number
114 AND organization_id = cp_organization_id;
115
116 CURSOR c_item_num_func (cp_catalog_group_id NUMBER)
117 IS
118 SELECT ITEM_NUM_GEN_METHOD
119 FROM
120 (
121 SELECT ICC.ITEM_NUM_GEN_METHOD
122 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
123 WHERE ICC.ITEM_NUM_GEN_METHOD IS NOT NULL
124 AND ICC.ITEM_NUM_GEN_METHOD <> 'I'
125 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
126 START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_catalog_group_id
127 ORDER BY LEVEL ASC
128 )
129 WHERE ROWNUM = 1;
130
131 TYPE transaction_type IS TABLE OF mtl_system_items_interface.transaction_id%TYPE
132 INDEX BY BINARY_INTEGER;
133
134 TYPE style_number_type IS TABLE OF mtl_system_items_interface.style_item_number%TYPE
135 INDEX BY BINARY_INTEGER;
136
137 TYPE org_type IS TABLE OF mtl_system_items_interface.organization_id%TYPE
138 INDEX BY BINARY_INTEGER;
139
140 TYPE catalog_type IS TABLE OF mtl_system_items_interface.item_catalog_group_id%TYPE
141 INDEX BY BINARY_INTEGER;
142
143 transaction_table transaction_type;
144 style_item_num_table style_number_type;
145 org_table org_type;
146 catalog_table catalog_type;
147
148
149 status_def_tmp varchar2(30);
150 status_default varchar2(10);
151 uom_default varchar2(25);
152 uom_default_tmp varchar2(30);
153 allow_item_desc_flag varchar2(1);
154 tax_flag varchar2(1);
155 req_required_flag varchar2(1);
156 receiving_flag varchar2(1) := 'N';
157 l_sysdate date := sysdate;
158 l_transaction_type varchar2(10) := NULL ;
159 l_old_organization_id number := NULL;
160 l_item_num_gen VARCHAR2(1) := 'N';
161 org_flag number :=0;
162 org_code varchar2(3) := NULL;
163 l_process_flag_1 number := 1;
164 l_process_flag_3 number := 3;
165 l_copy_item_id number := NULL;
166 l_copy_org_id number := NULL;
167 l_org_id number;
168 master_org_id number;
169 msiicount number;
170 revs number;
171 default_rev varchar2(3);
172 rtn_status number := 1;
173 dumm_status number;
174 tran_id number := 0;
175 ASS_ITEM_ERR EXCEPTION;
176 LOGGING_ERR EXCEPTION;
177 -- ITEM_NUMBER_ERR EXCEPTION; -- Bug 13503782
178 ERR_TYPE varchar2(30);
179 d_cost_of_sales_account number;
180 d_encumbrance_account number;
181 d_sales_account number;
182 d_expense_account number;
183 exists_id number;
184 seg1 varchar2(40);
185 seg2 varchar2(40);
186 seg3 varchar2(40);
187 error_msg varchar2(300);
188 validation_check_status number := 0;
189 process_flag_temp number := -999;
190 op_unit number;
191
192 /* Variables for the ff cursor in Dynamic SQL */
193 DSQL_ff_transaction_id number;
194 DSQL_ff_statement varchar2(3000);
195 DSQL_ff_c integer; /*pointer to dynamic SQL cursor*/
196 DSQL_ff_rows_processed integer;
197 ff_statement_temp varchar2(2000);
198 ff_err_temp varchar2(1000);
199 dummy_ret_code number;
200
201 /* Variables for the second Dynamic SQL statement*/
202 DSQL_inventory_item_id number;
203 DSQL_statement varchar2(3000);
204 DSQL_Statement_Msii varchar2(3000);
205 DSQL_c integer; /*pointer to dynamic SQL cursor*/
206 DSQL_rows_processed integer;
207 statement_temp varchar2(2000);
208 err_temp varchar2(1000);
209 transaction_id_bind integer;
210 flex_id NUMBER;
211 l_effectivity_date DATE;
212 l_rowid ROWID;
213
214 l_curr_sysdate DATE; -- Bug 4539703 this will store sysdate- 1 sec
215 ---Start: Bug fix 3051653
216 l_item_number mtl_system_items_interface.item_number%TYPE;
217 l_item_id mtl_system_items_interface.inventory_item_id%TYPE;
218 l_Itemid_error BOOLEAN := FALSE;
219
220 l_org_name HR_ALL_ORGANIZATION_UNITS_VL.name%TYPE;
221 l_msg_text fnd_new_messages.message_text%TYPE;
222
223 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
224 l_ret_status VARCHAR2(1);
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2(100);
227 l_style_item_id NUMBER;
228 l_item_num_gen_method VARCHAR2(1);
229 l_item_desc_gen_method VARCHAR2(1);
230 l_seq_exists NUMBER := 0;
231
232 -- Fix for bug#9336604
233 l_schema VARCHAR2(30);
234 l_status VARCHAR2(1);
235 l_industry VARCHAR2(1);
236
237 -- serial_tagging Enh -- bug 9913552
238 x_ret_sts VARCHAR2(1);
239 --bug 10065810
240 l_temp_org_id number;
241
242 BEGIN
243
244 update mtl_system_items_interface
245 set transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
246 where transaction_id is NULL
247 and set_process_id = xset_id;
248
249 --Get profile options for default assignment
250 INVPROFL.inv_pr_get_profile('INV',
251 'INV_STATUS_DEFAULT',
252 user_id,
253 -1,
254 401,
255 status_def_tmp,
256 rtn_status,
257 err_text);
258 status_default := substr(status_def_tmp,1,10);
259
260 if rtn_status <> 0 and rtn_status <> -9999 then
261 tran_id := 0;
262 raise ASS_ITEM_ERR;
263 else
264 if rtn_status = -9999 then
265 dumm_status := INVPUOPI.mtl_log_interface_err(
266 0,
267 user_id,
268 login_id,
269 prog_appid,
270 prog_id,
271 request_id,
272 tran_id,
273 err_text,
274 'INV_STATUS_DEFAULT',
275 'MTL_SYSTEM_ITEMS_INTERFACE',
276 'INV_NO_DEFAULT_STATUS',
277 err_text);
278 if dumm_status < 0 then
279 raise LOGGING_ERR;
280 end if;
281 rtn_status := 0;
282 end if;
283 end if;
284
285 INVPROFL.inv_pr_get_profile('INV',
286 'INV_UOM_DEFAULT',
287 user_id,
288 fnd_global.RESP_APPL_ID, --application id
289 fnd_global.resp_id,--responsibility id
290 uom_default_tmp,
291 rtn_status,
292 err_text);
293 uom_default := substr(uom_default_tmp,1,25);
294
295 IF l_inv_debug_level IN(101, 102) THEN
296 INVPUTLI.info('INVPAGI2: uom default is '|| uom_default);
297 END IF;
298
299
300 if rtn_status <> 0 and
301 rtn_status <> -9999 then
302 tran_id := 0;
303 raise ASS_ITEM_ERR;
304 else
305 if rtn_status = -9999 then
306 dumm_status := INVPUOPI.mtl_log_interface_err(
307 0,
308 user_id,
309 login_id,
310 prog_appid,
311 prog_id,
312 request_id,
313 tran_id,
314 err_text,
315 'INV_UOM_DEFAULT',
316 'MTL_SYSTEM_ITEMS_INTERFACE',
317 'INV_NO_DEFAULT_UOM',
318 err_text);
319 if dumm_status < 0 then
320 raise LOGGING_ERR;
321 end if;
322 rtn_status := 0;
323 end if;
324 end if;
325
326 --Start 3818646 : PUOM from Profile is always in US.
327 OPEN c_get_uom(uom_default);
328 FETCH c_get_uom INTO uom_default;
329 CLOSE c_get_uom;
330 --End 3818646 : PUOM from Profile is always in US.
331
332 for cr in ee loop
333 --User can now populate inventory item id in the interface table.
334 l_Itemid_error := FALSE;
335 BEGIN
336 SELECT MTL_SYSTEM_ITEMS_S.CURRVAL
337 INTO l_item_id FROM DUAL;
338 IF cr.inventory_item_id > l_item_id THEN
339 l_Itemid_error := TRUE;
340 END IF;
341 EXCEPTION
342 WHEN OTHERS THEN
343 --Start Bug 8259665
344 SELECT MTL_SYSTEM_ITEMS_S.NEXTVAL
345 INTO l_item_id FROM DUAL;
346 IF cr.inventory_item_id > l_item_id THEN
347 l_Itemid_error := TRUE;
348 END IF;
349 --End Bug 8259665
350 END;
351
352 IF l_Itemid_error THEN
353 dumm_status := INVPUOPI.mtl_log_interface_err(
354 -1,
355 user_id,
356 login_id,
357 prog_appid,
358 prog_id,
359 request_id,
360 cr.transaction_id,
361 'INVPAGI2: Invalid Item ID',
362 'inventory_item_id',
363 'MTL_SYSTEM_ITEMS_INTERFACE',
364 'INV_INVALID_INV_ITEM_ID',
365 err_text);
366 if dumm_status < 0 then
367 raise LOGGING_ERR;
368 end if;
369
370 update mtl_system_items_interface
371 set process_flag = l_process_flag_3
372 where rowid = cr.rowid ;
373
374 END IF;
375 end loop;
376
377 -- Bug 5118572 Handle those items in ICC which have SEQ generated item numbers
378 -- R12C changing the implementation of Sequence generation
379 IF (INSTR(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'PLM_UI:Y') = 0) THEN
380
381 IF default_flag = 2 THEN --Sequence generated item nos only in pre-defaulting phase
382
383 IF l_inv_debug_level IN(101, 102) THEN
384 INVPUTLI.info('INVPAGI2: About to handle sequence generated item number case');
385 END IF;
386
387 SELECT DISTINCT item_catalog_group_id BULK COLLECT INTO catalog_table
388 FROM mtl_system_items_interface
389 WHERE process_flag = 1
390 AND set_process_id = xset_id
391 AND ((organization_id = org_id) or (all_org = 1))
392 AND organization_id IN (SELECT master_organization_id /*Bug 6158936*/
393 FROM mtl_parameters)
394 AND item_catalog_group_id IS NOT NULL ;
395
396 IF catalog_table.COUNT > 0 THEN
397 FOR I IN catalog_table.FIRST .. catalog_table.LAST LOOP
398 EGO_IMPORT_PVT.Get_Item_Num_Desc_Gen_Method
399 (p_item_catalog_group_id => catalog_table(i),
400 x_item_num_gen_method => l_item_num_gen_method,
401 x_item_desc_gen_method => l_item_desc_gen_method);
402 IF l_item_num_gen_method = 'S' THEN
403 UPDATE mtl_system_items_interface msii
404 SET msii.set_process_id = xset_id + 5000000000000
405 WHERE msii.process_flag = 1
406 AND msii.set_process_id = xset_id
407 AND nvl(msii.style_item_flag, 'N') <> 'Y' --Bug 6182208
408 AND ((msii.organization_id = org_id) or (all_org = 1))
409 AND msii.organization_id IN (SELECT master_organization_id /*Bug 6158936*/
410 FROM mtl_parameters)
411 AND (msii.item_catalog_group_id IS NOT NULL AND msii.item_catalog_group_id = catalog_table(i));
412
413 IF l_inv_debug_level IN(101, 102) THEN
414 INVPUTLI.info('INVPAGI2: Identified rows for SEQ generation are:' || SQL%ROWCOUNT);
415 END IF;
416
417 l_seq_exists := 1;
418 END IF;
419 END LOOP;
420
421 IF l_seq_exists = 1 THEN
422 INV_EGO_REVISION_VALIDATE.Populate_Seq_Gen_Item_Nums ( p_set_id => xset_id + 5000000000000
423 ,p_org_id => org_id
424 ,p_all_org => all_org
425 ,p_rec_status => 1
426 ,x_return_status => l_ret_status
427 ,x_msg_count => l_msg_count
428 ,x_msg_data => l_msg_data );
429 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
430 dumm_status := INVPUOPI.mtl_log_interface_err(
431 0,
432 user_id,
433 login_id,
434 prog_appid,
435 prog_id,
436 request_id,
437 tran_id,
438 err_text,
439 null,
440 'MTL_SYSTEM_ITEMS_INTERFACE',
441 'SEQUENCE GEN ITEM NUM ' || l_msg_data,
442 err_text);
443 UPDATE mtl_system_items_interface
444 SET process_flag = 3
445 ,set_process_id = xset_id
446 WHERE set_process_id = xset_id + 5000000000000;
447 ELSE
448 UPDATE mtl_system_items_interface
449 SET set_process_id = xset_id
450 WHERE set_process_id = xset_id + 5000000000000;
451 END IF;
452 END IF; --Sequence Generated ICC items exist
453 END IF; -- Items with NOT NULL ICC exist in batch
454 -- End of Bug 5118572
455 END IF; --Seq gen Item Nos only in pre defaulting phase
456
457 IF l_inv_debug_level IN(101, 102) THEN
458 INVPUTLI.info('INVPAGI2: About to handle SKU items');
459 END IF;
460
461 /* Mark all SKU items with no style item to error */
462 UPDATE mtl_system_items_interface msii
463 SET msii.process_flag = 3
464 WHERE msii.process_flag = 1
465 AND msii.transaction_type = 'CREATE'
466 AND msii.set_process_id = xset_id
467 AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
468 WHERE mp.organization_id = msii.organization_id )
469 AND ( msii.style_item_flag = 'N' AND msii.style_item_id IS NULL AND msii.style_item_number IS NULL)
470 RETURNING transaction_id BULK COLLECT INTO transaction_table;
471
472 IF transaction_table.COUNT > 0 THEN
473 FOR j IN transaction_table.FIRST .. transaction_table.LAST LOOP
474 dumm_status := INVPUOPI.mtl_log_interface_err(
475 org_id,
476 user_id,
477 login_id,
478 prog_appid,
479 prog_id,
480 request_id,
481 transaction_table(j),
482 err_text,
483 'STYLE_ITEM_ID',
484 'MTL_SYSTEM_ITEMS_INTERFACE',
485 'INV_INVALID_STYLE_FOR_SKU' ,
486 err_text);
487 END LOOP;
488 END IF;
489
490 /* Resolve Style Item Numbers into Ids for SKU Items */
491 SELECT msii.style_item_number,msii.organization_id,transaction_id
492 BULK COLLECT INTO style_item_num_table, org_table, transaction_table
493 FROM mtl_system_items_interface msii
494 WHERE msii.process_flag = 1
495 AND msii.transaction_type = 'CREATE'
496 AND msii.set_process_id = xset_id
497 AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
498 WHERE mp.organization_id = msii.organization_id )
499 AND ( msii.style_item_flag = 'N' AND msii.style_item_id IS NULL AND msii.style_item_number IS NOT NULL);
500
501 IF style_item_num_table.COUNT > 0 THEN
502 FOR I IN style_item_num_table.FIRST .. style_item_num_table.LAST LOOP
503 OPEN c_get_style ( cp_style_item_number => style_item_num_table(i)
504 ,cp_organization_id => org_table(i));
505 FETCH c_get_style INTO l_style_item_id;
506
507 IF c_get_style%NOTFOUND THEN
508 dumm_status := INVPUOPI.mtl_log_interface_err(
509 0,
510 user_id,
511 login_id,
512 prog_appid,
513 prog_id,
514 request_id,
515 transaction_table(i),
516 err_text,
517 'STYLE_ITEM_NUMBER',
518 'MTL_SYSTEM_ITEMS_INTERFACE',
519 'INV_INVALID_STYLE_FOR_SKU',
520 err_text);
521 IF dumm_status < 0 then
522 raise LOGGING_ERR;
523 END IF;
524
525 UPDATE mtl_system_items_interface msii
526 SET process_flag = 3
527 WHERE msii.process_flag = 1
528 AND msii.transaction_type = 'CREATE'
529 AND msii.set_process_id = xset_id
530 AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
531 WHERE mp.organization_id = msii.organization_id )
532 AND msii.style_item_number = style_item_num_table(i);
533 ELSE
534 UPDATE mtl_system_items_interface msii
535 SET msii.style_item_id = l_style_item_id
536 WHERE msii.process_flag = 1
537 AND msii.transaction_type = 'CREATE'
538 AND msii.set_process_id = xset_id
539 AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
540 WHERE mp.organization_id = msii.organization_id )
541 AND msii.style_item_number = style_item_num_table(i);
542 END IF;
543 CLOSE c_get_style;
544 END LOOP;
545 END IF;
546 END IF;
547
548 IF l_inv_debug_level IN(101, 102) THEN
549 INVPUTLI.info('INVPAGI2: About to enter DSQL block');
550 END IF;
551
552
553 BEGIN /* PL-SQL Block for doing the dynamic SQL part*/
554
555 ff_statement_temp := NULL;
556 ff_err_temp := NULL;
557 DSQL_ff_c := dbms_sql.open_cursor;
558 dummy_ret_code := INVPUTLI.get_dynamic_sql_str(1, ff_statement_temp, ff_err_temp);
559
560 /* Now append the sql statement to the generated dynamic sql where clause
561 ** NP 02MAY96 Added xset_id and a i
562 ** statement to BIND the set_id variable to DSQL_ff_c */
563
564 --3701962: Changed to exists clause.
565 DSQL_ff_statement := 'select msii.transaction_id
566 from mtl_system_items_interface msii
567 where msii.inventory_item_id is NULL
568 and msii.organization_id is not NULL
569 and msii.process_flag = 1
570 and msii.set_process_id = :set_id_bind
571 and exists (select null
572 from mtl_system_items_b msi
573 where msii.organization_id = msi.organization_id and ' || ff_statement_temp || ')';
574
575 dbms_sql.parse(DSQL_ff_c, DSQL_ff_statement, dbms_sql.native);
576 dbms_sql.define_column(DSQL_ff_c,1,DSQL_ff_transaction_id);
577 dbms_sql.bind_variable(DSQL_ff_c, 'set_id_bind', xset_id);
578
579 DSQL_ff_rows_processed := dbms_sql.execute(DSQL_ff_c);
580
581 IF l_inv_debug_level IN(101, 102) THEN
582 INVPUTLI.info('INVPAGI2: About to enter DSQL loop');
583 END IF;
584
585 loop
586 if dbms_sql.fetch_rows(DSQL_ff_c) > 0 then
587 dbms_sql.column_value(DSQL_ff_c,1,DSQL_ff_transaction_id);
588 dumm_status := INVPUOPI.mtl_log_interface_err(
589 -1,
590 user_id,
591 login_id,
592 prog_appid,
593 prog_id,
594 request_id,
595 DSQL_ff_transaction_id,
596 'INVPAGI2: Duplicate Org ID and segments in MSI',
597 'inventory_item_id',
598 'MTL_SYSTEM_ITEMS_INTERFACE',
599 'INV_DUPL_ORG_ITEM_SEG',
600 err_text);
601 if dumm_status < 0 then
602 raise LOGGING_ERR;
603 end if;
604
605 update mtl_system_items_interface
606 set process_flag = l_process_flag_3
607 where transaction_id = DSQL_ff_transaction_id
608 and set_process_id = nvl(xset_id, set_process_id);
609 else
610 -- no more rows, Close cursor and exit
611 dbms_sql.close_cursor(DSQL_ff_c);
612 exit;
613 end if;
614 end loop; -- loop over all rows
615
616 if dbms_sql.is_open(DSQL_ff_c) then
617 dbms_sql.close_cursor(DSQL_ff_c);
618 end if;
619 IF l_inv_debug_level IN(101, 102) THEN
620 INVPUTLI.info('INVPAGI2:out of loop ');
621 END IF;
622
623 EXCEPTION
624 when others then
625 if dbms_sql.is_open(DSQL_ff_c) then
626 dbms_sql.close_cursor(DSQL_ff_c);
627 end if;
628 err_text:= 'assign_item_header DSQL 1 '|| SQLERRM;
629 dumm_status := INVPUOPI.mtl_log_interface_err(
630 l_org_id,
631 user_id,
632 login_id,
633 prog_appid,
634 prog_id,
635 request_id,
636 tran_id,
637 err_text,
638 null,
639 'MTL_SYSTEM_ITEMS_INTERFACE',
640 'DYN_SQL_ERROR',
641 err_text);
642
643 return(SQLCODE);
644 END; /* PL-SQL Block for doing the dynamic SQL part*/
645
646 /* removed + 0 from where condition
647 and clause of organization_id to fix bug 7459820 with base bug 7003119 */
648 /*Bug 8808591 - Changing driving table according to Perf team suggestion*/
649 DSQL_statement := 'select msi.inventory_item_id
650 from mtl_parameters mp,
651 mtl_system_items msi,
652 mtl_system_items_interface msii
653
654 where msii.transaction_id = :transaction_id_bind
655 and msii.set_process_id = :set_id_bind2
656 and rownum = 1
657 and msi.organization_id = mp.organization_id
658 and ' || ff_statement_temp;
659
660 IF l_inv_debug_level IN(101, 102) THEN
661 INVPUTLI.info('INVPAGI2: About to enter header cursorloop');
662 END IF;
663
664
665 update MTL_ITEM_REVISIONS_INTERFACE i
666 set i.organization_id = (select o.organization_id
667 from MTL_PARAMETERS o
668 where o.organization_code = i.organization_code)
669 where i.organization_id is NULL
670 and set_process_id = xset_id
671 and i.process_flag = l_process_flag_1;
672
673
674 l_old_organization_id := -999 ;
675
676 -- IOI Perf improvements..apply mass template.
677 --Start : Performance enhancements
678 IF (INSTR(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'PLM_UI:Y') = 0) THEN
679 dumm_status := INVPULI2.copy_template_attributes(
680 org_id
681 ,all_org
682 ,prog_appid
683 ,prog_id
684 ,request_id
685 ,user_id
686 ,login_id
687 ,xset_id
688 ,err_text);
689 if dumm_status <> 0 then
690 raise LOGGING_ERR;
691 end if;
692 END IF;
693 --End : Performance enhancements
694
695 FOR cr in header loop
696 IF l_inv_debug_level IN(101, 102) THEN
697 INVPUTLI.info('INVPAGI2: Set_id for current_row is '||cr.set_process_id);
698 END IF;
699 rtn_status := 0;
700 org_flag := 0;
701 tran_id := cr.transaction_id;
702 l_org_id := cr.organization_id;
703 validation_check_status := 0;
704 dumm_status := NULL;
705
706 if cr.item_number is not NULL then
707 rtn_status := INVPUOPI.mtl_pr_parse_item_number(
708 cr.item_number,
709 cr.inventory_item_id,
710 cr.transaction_id,
711 cr.organization_id,
712 err_text,
713 cr.rowid);
714 if rtn_status < 0 then
715 -- raise ASS_ITEM_ERR;
716 --Fix for Bug 9706610
717
718 /* Bug 13503782 : Start
719 We should not raise exception, so that other records are processed.
720 Log the error and set this record to 3 in MSII and proceed to next record.
721 */
722 -- RAISE ITEM_NUMBER_ERR ;
723 dumm_status := INVPUOPI.mtl_log_interface_err(
724 l_org_id,
725 user_id,
726 login_id,
727 prog_appid,
728 prog_id,
729 request_id,
730 tran_id,
731 err_text,
732 null,
733 'MTL_SYSTEM_ITEMS_INTERFACE',
734 'INV_ITEM_INVALID',
735 err_text);
736
737 UPDATE mtl_system_items_interface
738 SET process_flag = 3
739 WHERE ROWID= cr.ROWID;
740
741 CONTINUE;
742 -- Bug 13503782 : End
743 end if;
744 else
745 IF cr.item_catalog_group_id IS NOT NULL THEN
746 OPEN c_item_num_func(cp_catalog_group_id => cr.item_catalog_group_id);
747 FETCH c_item_num_func INTO l_item_num_gen;
748 CLOSE c_item_num_func;
749 ELSE
750 l_item_num_gen := 'N';
751 END IF;
752
753 if (cr.inventory_item_id is NULL and
754 cr.item_number is NULL and
755 cr.segment1 is NULL and cr.segment2 is NULL and
756 cr.segment3 is NULL and cr.segment4 is NULL and
757 cr.segment5 is NULL and cr.segment6 is NULL and
758 cr.segment7 is NULL and cr.segment8 is NULL and
759 cr.segment9 is NULL and cr.segment10 is NULL and
760 cr.segment11 is NULL and cr.segment12 is NULL and
761 cr.segment13 is NULL and cr.segment14 is NULL and
762 cr.segment15 is NULL and cr.segment16 is NULL and
763 cr.segment17 is NULL and cr.segment18 is NULL and
764 cr.segment19 is NULL and cr.segment20 is NULL and
765 l_item_num_gen <> 'F') then
766 dumm_status := INVPUOPI.mtl_log_interface_err(
767 0,
768 user_id,
769 login_id,
770 prog_appid,
771 prog_id,
772 request_id,
773 tran_id,
774 err_text,
775 null,
776 'MTL_SYSTEM_ITEMS_INTERFACE',
777 'INV_SEG_ITM_NUMB_VAL',
778 err_text);
779 if dumm_status < 0 then
780 raise LOGGING_ERR;
781 end if;
782 validation_check_status := -1;
783 end if;
784 end if;
785
786 update mtl_system_items_interface
787 --Bug: 2821206 Replaced ltrim with trim for segment1..20
788 set segment1 = trim(segment1),
789 segment2 = trim(segment2),
790 segment3 = trim(segment3),
791 segment4 = trim(segment4),
792 segment5 = trim(segment5),
793 segment6 = trim(segment6),
794 segment7 = trim(segment7),
795 segment8 = trim(segment8),
796 segment9 = trim(segment9),
797 segment10 = trim(segment10),
798 segment11 = trim(segment11),
799 segment12 = trim(segment12),
800 segment13 = trim(segment13),
801 segment14 = trim(segment14),
802 segment15 = trim(segment15),
803 segment16 = trim(segment16),
804 segment17 = trim(segment17),
805 segment18 = trim(segment18),
806 segment19 = trim(segment19),
807 segment20 = trim(segment20) ,
808 description = trim(description),
809 long_description = trim(long_description),
810 attribute_category = trim(attribute_category),
811 attribute1 = trim(attribute1),
812 attribute2 = trim(attribute2),
813 attribute3 = trim(attribute3),
814 attribute4 = trim(attribute4),
815 attribute5 = trim(attribute5),
816 attribute6 = trim(attribute6),
817 attribute7 = trim(attribute7),
818 attribute8 = trim(attribute8),
819 attribute9 = trim(attribute9),
820 attribute10 = trim(attribute10),
821 attribute11 = trim(attribute11),
822 attribute12 = trim(attribute12),
823 attribute13 = trim(attribute13),
824 attribute14 = trim(attribute14),
825 attribute15 = trim(attribute15),
826 /* Start Bug 3713912 */
827 attribute16= trim(attribute16),
828 attribute17= trim(attribute17),
829 attribute18= trim(attribute18),
830 attribute19= trim(attribute19),
831 attribute20= trim(attribute20),
832 attribute21= trim(attribute21),
833 attribute22= trim(attribute22),
834 attribute23= trim(attribute23),
835 attribute24= trim(attribute24),
836 attribute25= trim(attribute25),
837 attribute26= trim(attribute26),
838 attribute27= trim(attribute27),
839 attribute28= trim(attribute28),
840 attribute29= trim(attribute29),
841 attribute30= trim(attribute30),
842 cas_number = trim(cas_number),
843 child_lot_prefix= rtrim(child_lot_prefix),
844 /* End Bug 3713912 */
845 auto_lot_alpha_prefix = trim(auto_lot_alpha_prefix), -- Rtrim changed to TRIM for bug-5896824
846 start_auto_lot_number = rtrim(start_auto_lot_number),
847 start_auto_serial_number =rtrim(start_auto_serial_number),
848 auto_serial_alpha_prefix =trim(auto_serial_alpha_prefix), -- Rtrim changed to TRIM for bug-5896824
849 engineering_ecn_code = rtrim(engineering_ecn_code),
850 model_config_clause_name = trim(model_config_clause_name),
851 global_attribute_category = trim(global_attribute_category),
852 global_attribute1 = trim(global_attribute1),
853 global_attribute2 = trim(global_attribute2),
854 global_attribute3 = trim(global_attribute3),
855 global_attribute4 = trim(global_attribute4),
856 global_attribute5 = trim(global_attribute5),
857 global_attribute6 = trim(global_attribute6),
858 global_attribute7 = trim(global_attribute7),
859 global_attribute8 = trim(global_attribute8),
860 global_attribute9 = trim(global_attribute9),
861 global_attribute10 = trim(global_attribute10),
862 global_attribute11 = trim(global_attribute11),
863 global_attribute12 = trim(global_attribute12),
864 global_attribute13 = trim(global_attribute13),
865 global_attribute14 = trim(global_attribute14),
866 global_attribute15 = trim(global_attribute15),
867 global_attribute16 = trim(global_attribute16),
868 global_attribute17 = trim(global_attribute17),
869 global_attribute18 = trim(global_attribute18),
870 global_attribute19 = trim(global_attribute19),
871 global_attribute20 = trim(global_attribute20)
872 where rowid = cr.rowid ;
873 begin
874 select organization_code
875 into org_code
876 from mtl_parameters
877 where organization_id = cr.organization_id;
878 exception
879 WHEN NO_DATA_FOUND then
880 org_flag := 1;
881 end;
882
883
884 /** Get some default values from PO_SYSTEM_PARAMETERS
885 ** NP 13-MAY-95 New changes for intrastat
886 ** This code moved into the cursor scope because it is now
887 ** dependent on cr.organization_id value.
888 ** Basic Assumption: Either each row of ood has a
889 ** non null value for op_unit
890 ** and each row of PSPA has a non_null value for org_id
891 ** OR
892 ** each row of ood has a null value for op_unit
893 ** and there is ONLY one row in PSPA and that row has a null
894 ** org_id
895 **
896 ** NP 26-JUL-95 Now defaulting values of
897 ** allow_item_desc_flag,req_required_flag
898 ** to Y and N instead of NULL.
899 **/
900 if org_flag <> 1 then
901
902 If l_old_organization_id <> cr.organization_id then
903 begin
904 --Perf Issue : Replaced org_organizations_definitions view.
905 select DECODE(ORG_INFORMATION_CONTEXT,
906 'Accounting Information',
907 TO_NUMBER(ORG_INFORMATION3),
908 TO_NUMBER(NULL)) operating_unit
909 into op_unit
910 from hr_organization_information
911 where organization_id = cr.organization_id
912 and (org_information_context|| '') ='Accounting Information';
913
914 begin
915 select PSPA.ALLOW_ITEM_DESC_UPDATE_FLAG,
916 PSPA.RFQ_REQUIRED_FLAG,
917 PSPA.receiving_flag, PSPA.TAXABLE_FLAG
918 into allow_item_desc_flag,
919 req_required_flag,
920 receiving_flag,
921 tax_flag
922 from PO_SYSTEM_PARAMETERS_ALL PSPA
923 where nvl(PSPA.org_id, -111) = nvl(op_unit, -111)
924 and rownum = 1;
925 exception
926 WHEN NO_DATA_FOUND then
927 allow_item_desc_flag := 'Y';
928 req_required_flag := 'N';
929 receiving_flag := 'N' ;
930 tax_flag := 'N';
931 end;
932 exception
933 WHEN NO_DATA_FOUND then
934 BEGIN
935 SELECT name INTO l_org_name
936 FROM hr_all_organization_units_vl
937 WHERE organization_id = cr.organization_id;
938 EXCEPTION
939 WHEN OTHERS THEN
940 l_org_name := cr.organization_id;
941 END;
942 FND_MESSAGE.SET_NAME ('INV', 'INV_NO_OP_UNIT_FOR_ORG');
943 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', l_org_name);
944 l_msg_text := FND_MESSAGE.GET;
945 err_text := 'No Operating Unit Found for the Organization';
946 dumm_status := INVPUOPI.mtl_log_interface_err(
947 cr.organization_id,
948 user_id,
949 login_id,
950 prog_appid,
951 prog_id,
952 request_id,
953 tran_id,
954 l_msg_text,
955 'ORGANIZATION_ID',
956 'ORG_ORGANIZATION_DEFINITIONS',
957 'INV_IOI_ERR_NO_OP_UNIT_4_ORG', --added by bug 11894684, replace INV_IOI_ERR with specific message name so we can identify error easily
958 err_text);
959 if dumm_status < 0 then
960 raise LOGGING_ERR;
961 end if;
962 WHEN OTHERS then
963 raise_application_error(-20001, SQLERRM);
964 end;
965 End If ;
966
967 /* Assign item_id based on segment values using the following two cases
968 ** 1) if record exists with identical segments, use it's item_id
969 ** 2) if NO record exists with identical segments, use sequence
970 */
971 if cr.inventory_item_id is null then
972 BEGIN
973 IF l_inv_debug_level IN(101, 102) THEN
974 INVPUTLI.info('INVPAGI2: About to process DSQL 2 ');
975 END IF;
976 DSQL_c := dbms_sql.open_cursor;
977 dbms_sql.parse(DSQL_c, DSQL_statement, dbms_sql.native);
978 dbms_sql.define_column(DSQL_c,1,DSQL_inventory_item_id);
979 dbms_sql.bind_variable(DSQL_c, 'transaction_id_bind', cr.transaction_id);
980 dbms_sql.bind_variable(DSQL_c, 'set_id_bind2', xset_id);
981 DSQL_rows_processed := dbms_sql.execute(DSQL_c);
982
983 --There is no loop over all rows; there is actually only ONE row here..
984 if dbms_sql.fetch_rows(DSQL_c) > 0 then
985 dbms_sql.column_value(DSQL_c,1,DSQL_inventory_item_id);
986 exists_id := DSQL_inventory_item_id;
987
988 update MTL_SYSTEM_ITEMS_INTERFACE
989 set inventory_item_id = exists_id
990 where rowid = cr.rowid ;
991
992 cr.inventory_item_id := exists_id;
993
994 -- bug10285405
995 exists_id:=NULL;
996
997 /* fix for bug 8510589 */
998 if dbms_sql.is_open(DSQL_c) then
999 dbms_sql.close_cursor(DSQL_c);
1000 end if;
1001
1002 else
1003 --Adding resolution of Item Id from Master in same batch (from Intf table)
1004 IF l_inv_debug_level IN(101, 102) THEN
1005 INVPUTLI.info('INVPAGI2: About to process DSQL 3 ');
1006 END IF;
1007 -- Fix for bug#9336604
1008 DSQL_Statement_Msii := 'SELECT /*+ index(msii MTL_SYSTEM_ITEMS_INTERFACE_N6) index(msi MTL_SYSTEM_ITEMS_INTERFACE_N4) */ msi.inventory_item_id
1009 FROM mtl_system_items_interface msi, mtl_system_items_interface msii
1010 WHERE msi.set_process_id = msii.set_process_id
1011 AND msi.organization_id =
1012 (SELECT mp.master_organization_id FROM mtl_parameters mp
1013 WHERE mp.organization_id = msii.organization_id)
1014 AND msii.set_process_id = :set_id_bind2
1015 AND msii.transaction_id = :transaction_id_bind
1016 AND rownum = 1
1017 AND ' ||ff_statement_temp ;
1018
1019 if dbms_sql.is_open(DSQL_c) then
1020 dbms_sql.close_cursor(DSQL_c);
1021 end if;
1022
1023 DSQL_c := dbms_sql.open_cursor;
1024 dbms_sql.parse(DSQL_c, DSQL_Statement_Msii, dbms_sql.native);
1025 dbms_sql.define_column(DSQL_c,1,DSQL_inventory_item_id);
1026 dbms_sql.bind_variable(DSQL_c, 'set_id_bind2', xset_id);
1027 dbms_sql.bind_variable(DSQL_c, 'transaction_id_bind', cr.transaction_id);
1028 DSQL_rows_processed := dbms_sql.execute(DSQL_c);
1029
1030 --There is no loop over all rows; there is actually only ONE row here..
1031 if dbms_sql.fetch_rows(DSQL_c) > 0 then
1032 dbms_sql.column_value(DSQL_c,1,DSQL_inventory_item_id);
1033 exists_id := DSQL_inventory_item_id;
1034 end if;
1035
1036 if exists_id IS NOT NULL then
1037
1038 update MTL_SYSTEM_ITEMS_INTERFACE
1039 set inventory_item_id = exists_id
1040 where rowid = cr.rowid ;
1041
1042 cr.inventory_item_id := exists_id;
1043 -- bug10285405
1044 exists_id:=NULL;
1045
1046 else
1047 -- No such row found. Close the cursor after
1048 -- Assigning missing inventory_item_id from sequence
1049
1050 IF l_inv_debug_level IN(101, 102) THEN
1051 INVPUTLI.info('INVPAGI2: No match in MSI; Creating Inventory Item Id from sequence');
1052 END IF;
1053
1054 /*update MTL_SYSTEM_ITEMS_INTERFACE
1055 set inventory_item_id = MTL_SYSTEM_ITEMS_S.nextval
1056 where rowid = cr.rowid
1057 returning inventory_item_id INTO cr.inventory_item_id;*/ /*Changed for bug 8808591*/
1058
1059 update MTL_SYSTEM_ITEMS_INTERFACE
1060 set inventory_item_id = MTL_SYSTEM_ITEMS_S.nextval
1061 where rowid = cr.rowid;
1062
1063 select MTL_SYSTEM_ITEMS_S.CURRVAL INTO cr.inventory_item_id FROM DUAL;
1064
1065 dbms_sql.close_cursor(DSQL_c);
1066 end if; -- Row not in MSI and MSII
1067
1068 if dbms_sql.is_open(DSQL_c) then
1069 dbms_sql.close_cursor(DSQL_c);
1070 end if;
1071 end if; --Row not in MSI
1072 EXCEPTION
1073 when others then
1074 if dbms_sql.is_open(DSQL_c) then
1075 dbms_sql.close_cursor(DSQL_c);
1076 end if;
1077 err_text:= 'assign_item_header DSQL 2 '|| SQLERRM;
1078 dumm_status := INVPUOPI.mtl_log_interface_err(
1079 l_org_id,
1080 user_id,
1081 login_id,
1082 prog_appid,
1083 prog_id,
1084 request_id,
1085 tran_id,
1086 err_text,
1087 null,
1088 'MTL_SYSTEM_ITEMS_INTERFACE',
1089 'DYN_SQL_ERROR',
1090 err_text);
1091
1092 return(SQLCODE);
1093 END; /* PLSQL Block for doing the second dynamic SQL*/
1094 end if; /* cr.inventory_item_id is null */
1095
1096 -- determine if item is in master org.
1097 IF l_inv_debug_level IN(101, 102) THEN
1098 INVPUTLI.info('INVPAGI2: Determining whether the item is in master org');
1099 END IF;
1100 if l_old_organization_id <> cr.organization_id then
1101 select mp.master_organization_id ,
1102 mp.starting_revision ,
1103 cost_of_sales_account,
1104 encumbrance_account,
1105 sales_account,
1106 expense_account
1107 into master_org_id ,
1108 default_rev ,
1109 d_cost_of_sales_account,
1110 d_encumbrance_account,
1111 d_sales_account,
1112 d_expense_account
1113 from mtl_parameters mp
1114 where mp.organization_id = cr.organization_id;
1115 End if ;
1116
1117 if (cr.revision is null) then
1118 cr.revision := default_rev;
1119 end if;
1120 IF l_inv_debug_level IN(101, 102) THEN
1121 INVPUTLI.info('cr.rev is ' || cr.revision);
1122 END IF;
1123 msiicount := 0;
1124
1125 select count(*) into msiicount
1126 from mtl_system_items msii
1127 where cr.inventory_item_id = msii.inventory_item_id
1128 and msii.organization_id = master_org_id;
1129 IF l_inv_debug_level IN(101, 102) THEN
1130 INVPUTLI.info('Processing itemid '|| cr.inventory_item_id );
1131 INVPUTLI.info('with Org id '|| cr.organization_id );
1132 INVPUTLI.info('with segment1 '|| cr.segment1 );
1133 END IF;
1134 --assign master_org attribute defaults if in child org AND parent exists
1135
1136 if ((master_org_id <> cr.organization_id) and (msiicount = 1)) then
1137 if rtn_status = 0 then
1138 IF INVPOPIF.g_source_org /*Added for bug 6372595*/
1139 THEN
1140 IF l_inv_debug_level IN(101, 102) THEN
1141 INVPUTLI.info('INVPAGI2: calling assign_master_defaults with set_id '||xset_id);
1142 END IF;
1143 rtn_status := INVPUTLI.assign_master_defaults(
1144 cr.transaction_id,
1145 cr.inventory_item_id,
1146 cr.organization_id,
1147 master_org_id,
1148 status_default,
1149 uom_default,
1150 allow_item_desc_flag,
1151 req_required_flag,
1152 err_text,
1153 xset_id ,
1154 cr.rowid);
1155 if rtn_status < 0 then
1156 raise ASS_ITEM_ERR;
1157 end if;
1158
1159 -- Sun Serial Tagging Enhancement -- bug 9913552
1160 -- For Supporting Excel Import Item Org Assignment Case
1161
1162 IF cr.inventory_item_id is not null and master_org_id is not null and cr.organization_id is not null then
1163
1164 IF ( INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => cr.inventory_item_id,
1165 p_organization_id => master_org_id)=2 ) THEN
1166
1167 /* both p_from_item_id and p_to_item_id will be same in this case */
1168 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
1169 p_from_item_id => cr.inventory_item_id,
1170 p_from_org_id => master_org_id,
1171 p_to_item_id => cr.inventory_item_id,
1172 p_to_org_id => cr.organization_id,
1173 x_return_status => x_ret_sts);
1174
1175 IF x_ret_sts <>FND_API.G_RET_STS_SUCCESS THEN
1176 RAISE ASS_ITEM_ERR;
1177 END IF ;
1178
1179 END IF;
1180 END IF;
1181
1182 END IF; /*Added for bug 6372595*/
1183 end if;
1184 else
1185 if ((master_org_id <> cr.organization_id) and (msiicount = 0)) then
1186 if rtn_status = 0 then
1187 IF l_inv_debug_level IN(101, 102) THEN
1188 INVPUTLI.info('INVPAGI2: Orphan found; about to call error ');
1189 INVPUTLI.info('INVPAGI2: Orphan found; checking in msii ');
1190 END IF;
1191
1192 select count(*) into msiicount
1193 from mtl_system_items_interface
1194 where inventory_item_id = cr.inventory_item_id
1195 and organization_id = master_org_id
1196 and set_process_id = xset_id
1197 and process_flag in (1,60000+1); /*masters are moved to 60000+ to before calling child create*/
1198
1199 if msiicount = 0 then
1200 error_msg := 'Orphan detected. This item has no parent in MSI';
1201 validation_check_status := -1;
1202 dumm_status := INVPUOPI.mtl_log_interface_err(
1203 cr.organization_id,
1204 user_id,
1205 login_id,
1206 prog_appid,
1207 prog_id,
1208 request_id,
1209 tran_id,
1210 error_msg,
1211 'ORGANIZATION_ID',
1212 'MTL_SYSTEM_ITEMS_INTERFACE',
1213 'INV_IOI_ORPHAN_CHILD',
1214 err_text);
1215 if dumm_status < 0 then
1216 raise LOGGING_ERR;
1217 end if;
1218 IF l_inv_debug_level IN(101, 102) THEN
1219 INVPUTLI.info('INVPAGI2: Orphan found; error called '|| rtn_status);
1220 END IF;
1221 else
1222 rtn_status := INVPUTLI.predefault_child_master(
1223 cr.inventory_item_id,
1224 cr.organization_id,
1225 master_org_id,
1226 err_text,
1227 xset_id ,
1228 cr.rowid);
1229 if rtn_status < 0 then
1230 raise ASS_ITEM_ERR;
1231 end if;
1232 end if;
1233 end if; /*rtn_status = 0*/
1234 else
1235 if rtn_status = 0 then
1236 IF l_inv_debug_level IN(101, 102) THEN
1237 INVPUTLI.info('INVPAGI2: In the new-item-in-master case ');
1238 INVPUTLI.info('INVPAGI2: Calling assign_item_defaults with set_id '|| xset_id);
1239 END IF;
1240
1241 rtn_status := INVPUTLI.assign_item_defaults(
1242 cr.inventory_item_id,
1243 cr.organization_id,
1244 status_default,
1245 uom_default,
1246 allow_item_desc_flag,
1247 req_required_flag,
1248 tax_flag,
1249 err_text,
1250 xset_id ,
1251 cr.rowid,
1252 receiving_flag );
1253 if rtn_status < 0 then
1254 raise ASS_ITEM_ERR;
1255 end if;
1256 end if; /*rtn_status = 0*/
1257 end if;
1258 end if;
1259
1260 update MTL_SYSTEM_ITEMS_INTERFACE
1261 set cost_of_sales_account = nvl(cost_of_sales_account,d_cost_of_sales_account),
1262 encumbrance_account = nvl(encumbrance_account,d_encumbrance_account),
1263 sales_account = nvl(sales_account,d_sales_account),
1264 expense_account = nvl(expense_account,d_expense_account)
1265 where rowid = cr.rowid ;
1266
1267 /*Bug 6417006 - Converting '!' to NULL and -999999 to NULL. This done to
1268 * ensure NULL attribute values while applying
1269 * Template with enabled attribute values as NULL. This is for the 'CREATE'
1270 * mode of the IOI*/
1271 UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
1272 SET
1273 -- Serial_Tagging Eng -- bug 9913552
1274 copy_item_id = Decode (copy_item_id, g_upd_null_NUM, NULL,
1275 copy_item_id),
1276 DESCRIPTION = DECODE( DESCRIPTION, g_Upd_Null_CHAR, NULL,
1277 trim(DESCRIPTION )),
1278 LONG_DESCRIPTION = DECODE( LONG_DESCRIPTION, g_Upd_Null_CHAR, NULL,
1279 trim(LONG_DESCRIPTION)),
1280 BUYER_ID = decode(BUYER_ID, g_Upd_Null_NUM, NULL,
1281 BUYER_ID),
1282 ACCOUNTING_RULE_ID = decode(ACCOUNTING_RULE_ID,g_Upd_Null_NUM,
1283 NULL, ACCOUNTING_RULE_ID),
1284 INVOICING_RULE_ID = decode(INVOICING_RULE_ID,g_Upd_Null_NUM,
1285 NULL, INVOICING_RULE_ID),
1286 ATTRIBUTE_CATEGORY = decode(ATTRIBUTE_CATEGORY,
1287 g_Upd_Null_CHAR, NULL, trim(ATTRIBUTE_CATEGORY)),
1288 ATTRIBUTE1 = decode(ATTRIBUTE1, g_Upd_Null_CHAR,
1289 NULL, trim(ATTRIBUTE1)),
1290 ATTRIBUTE2 = decode(ATTRIBUTE2, g_Upd_Null_CHAR,
1291 NULL, trim(ATTRIBUTE2)),
1292 ATTRIBUTE3 = decode(ATTRIBUTE3, g_Upd_Null_CHAR,
1293 NULL, trim(ATTRIBUTE3)),
1294 ATTRIBUTE4 = decode(ATTRIBUTE4, g_Upd_Null_CHAR,
1295 NULL, trim(ATTRIBUTE4)),
1296 ATTRIBUTE5 = decode(ATTRIBUTE5, g_Upd_Null_CHAR,
1297 NULL, trim(ATTRIBUTE5)),
1298 ATTRIBUTE6 = decode(ATTRIBUTE6, g_Upd_Null_CHAR,
1299 NULL, trim(ATTRIBUTE6)),
1300 ATTRIBUTE7 = decode(ATTRIBUTE7, g_Upd_Null_CHAR,
1301 NULL, trim(ATTRIBUTE7)),
1302 ATTRIBUTE8 = decode(ATTRIBUTE8, g_Upd_Null_CHAR,
1303 NULL, trim(ATTRIBUTE8)),
1304 ATTRIBUTE9 = decode(ATTRIBUTE9, g_Upd_Null_CHAR,
1305 NULL, trim(ATTRIBUTE9)),
1306 ATTRIBUTE10 = decode(ATTRIBUTE10, g_Upd_Null_CHAR,
1307 NULL, trim(ATTRIBUTE10)),
1308 ATTRIBUTE11 = decode(ATTRIBUTE11, g_Upd_Null_CHAR,
1309 NULL, trim(ATTRIBUTE11)),
1310 ATTRIBUTE12 = decode(ATTRIBUTE12, g_Upd_Null_CHAR,
1311 NULL, trim(ATTRIBUTE12)),
1312 ATTRIBUTE13 = decode(ATTRIBUTE13, g_Upd_Null_CHAR,
1313 NULL, trim(ATTRIBUTE13)),
1314 ATTRIBUTE14 = decode(ATTRIBUTE14, g_Upd_Null_CHAR,
1315 NULL, trim(ATTRIBUTE14)),
1316 ATTRIBUTE15 = decode(ATTRIBUTE15, g_Upd_Null_CHAR,
1317 NULL, trim(ATTRIBUTE15)),
1318 GLOBAL_ATTRIBUTE_CATEGORY =
1319 decode(GLOBAL_ATTRIBUTE_CATEGORY, g_Upd_Null_CHAR, NULL,
1320 trim(GLOBAL_ATTRIBUTE_CATEGORY)),
1321 GLOBAL_ATTRIBUTE1 = decode(GLOBAL_ATTRIBUTE1,
1322 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE1)),
1323 GLOBAL_ATTRIBUTE2 = decode(GLOBAL_ATTRIBUTE2,
1324 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE2)),
1325 GLOBAL_ATTRIBUTE3 = decode(GLOBAL_ATTRIBUTE3,
1326 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE3)),
1327 GLOBAL_ATTRIBUTE4 = decode(GLOBAL_ATTRIBUTE4,
1328 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE4)),
1329 GLOBAL_ATTRIBUTE5 = decode(GLOBAL_ATTRIBUTE5,
1330 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE5)),
1331 GLOBAL_ATTRIBUTE6 = decode(GLOBAL_ATTRIBUTE6,
1332 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE6)),
1333 GLOBAL_ATTRIBUTE7 = decode(GLOBAL_ATTRIBUTE7,
1334 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE7)),
1335 GLOBAL_ATTRIBUTE8 = decode(GLOBAL_ATTRIBUTE8,
1336 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE8)),
1337 GLOBAL_ATTRIBUTE9 = decode(GLOBAL_ATTRIBUTE9,
1338 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE9)),
1339 GLOBAL_ATTRIBUTE10 = decode(GLOBAL_ATTRIBUTE10,
1340 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE10)),
1341
1342 GLOBAL_ATTRIBUTE11 = decode(GLOBAL_ATTRIBUTE11,
1343 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE11)),
1344 GLOBAL_ATTRIBUTE12 = decode(GLOBAL_ATTRIBUTE12,
1345 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE12)),
1346 GLOBAL_ATTRIBUTE13 = decode(GLOBAL_ATTRIBUTE13,
1347 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE13)),
1348 GLOBAL_ATTRIBUTE14 = decode(GLOBAL_ATTRIBUTE14,
1349 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE14)),
1350 GLOBAL_ATTRIBUTE15 = decode(GLOBAL_ATTRIBUTE15,
1351 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE15)),
1352 GLOBAL_ATTRIBUTE16 = decode(GLOBAL_ATTRIBUTE16,
1353 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE16)),
1354 GLOBAL_ATTRIBUTE17 = decode(GLOBAL_ATTRIBUTE17,
1355 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE17)),
1356 GLOBAL_ATTRIBUTE18 = decode(GLOBAL_ATTRIBUTE18,
1357 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE18)),
1358 GLOBAL_ATTRIBUTE19 = decode(GLOBAL_ATTRIBUTE19,
1359 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE19)),
1360 GLOBAL_ATTRIBUTE20 = decode(GLOBAL_ATTRIBUTE20,
1361 g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE20)),
1362
1363 ITEM_CATALOG_GROUP_ID = decode(ITEM_CATALOG_GROUP_ID,
1364 g_Upd_Null_NUM, NULL, ITEM_CATALOG_GROUP_ID),
1365 CATALOG_STATUS_FLAG = DECODE(CATALOG_STATUS_FLAG,
1366 g_Upd_Null_CHAR, NULL, trim(CATALOG_STATUS_FLAG)),
1367 DEFAULT_SHIPPING_ORG = decode(DEFAULT_SHIPPING_ORG,
1368 g_Upd_Null_NUM, NULL, DEFAULT_SHIPPING_ORG),
1369 TAXABLE_FLAG = decode(TAXABLE_FLAG,
1370 g_Upd_Null_CHAR, NULL, trim(TAXABLE_FLAG)),
1371 PURCHASING_TAX_CODE =
1372 decode(PURCHASING_TAX_CODE,g_Upd_Null_CHAR,NULL,trim(PURCHASING_TAX_CODE)),
1373 QTY_RCV_EXCEPTION_CODE = decode(QTY_RCV_EXCEPTION_CODE,
1374 g_Upd_Null_CHAR, NULL, trim(QTY_RCV_EXCEPTION_CODE)),
1375 INSPECTION_REQUIRED_FLAG = decode(INSPECTION_REQUIRED_FLAG,
1376 g_Upd_Null_CHAR, NULL, trim(INSPECTION_REQUIRED_FLAG)),
1377 RECEIPT_REQUIRED_FLAG = decode(RECEIPT_REQUIRED_FLAG,
1378 g_Upd_Null_CHAR, NULL, trim(RECEIPT_REQUIRED_FLAG)),
1379 MARKET_PRICE = decode(MARKET_PRICE,
1380 g_Upd_Null_NUM, NULL, MARKET_PRICE),
1381 HAZARD_CLASS_ID = decode(HAZARD_CLASS_ID,
1382 g_Upd_Null_NUM, NULL, HAZARD_CLASS_ID),
1383 QTY_RCV_TOLERANCE = decode(QTY_RCV_TOLERANCE,
1384 g_Upd_Null_NUM, NULL, QTY_RCV_TOLERANCE),
1385 LIST_PRICE_PER_UNIT = decode(LIST_PRICE_PER_UNIT,
1386 g_Upd_Null_NUM, NULL, LIST_PRICE_PER_UNIT),
1387 UN_NUMBER_ID = decode(UN_NUMBER_ID,
1388 g_Upd_Null_NUM, NULL, UN_NUMBER_ID),
1389 PRICE_TOLERANCE_PERCENT = decode(PRICE_TOLERANCE_PERCENT,
1390 g_Upd_Null_NUM, NULL, PRICE_TOLERANCE_PERCENT),
1391 ASSET_CATEGORY_ID = decode(ASSET_CATEGORY_ID,
1392 g_Upd_Null_NUM, NULL, ASSET_CATEGORY_ID),
1393 ROUNDING_FACTOR = decode(ROUNDING_FACTOR,
1394 g_Upd_Null_NUM, NULL, ROUNDING_FACTOR),
1395 UNIT_OF_ISSUE = decode(UNIT_OF_ISSUE,
1396 g_Upd_Null_CHAR, NULL, trim(UNIT_OF_ISSUE)),
1397 ENFORCE_SHIP_TO_LOCATION_CODE =
1398 decode(ENFORCE_SHIP_TO_LOCATION_CODE, g_Upd_Null_CHAR, NULL,
1399 trim(ENFORCE_SHIP_TO_LOCATION_CODE)),
1400 ALLOW_SUBSTITUTE_RECEIPTS_FLAG =
1401 decode(ALLOW_SUBSTITUTE_RECEIPTS_FLAG, g_Upd_Null_CHAR, NULL,
1402 trim(ALLOW_SUBSTITUTE_RECEIPTS_FLAG)),
1403 ALLOW_UNORDERED_RECEIPTS_FLAG =
1404 decode(ALLOW_UNORDERED_RECEIPTS_FLAG, g_Upd_Null_CHAR, NULL,
1405 trim(ALLOW_UNORDERED_RECEIPTS_FLAG)),
1406 ALLOW_EXPRESS_DELIVERY_FLAG =
1407 decode(ALLOW_EXPRESS_DELIVERY_FLAG, g_Upd_Null_CHAR, NULL,
1408 trim(ALLOW_EXPRESS_DELIVERY_FLAG)),
1409 DAYS_EARLY_RECEIPT_ALLOWED =
1410 decode(DAYS_EARLY_RECEIPT_ALLOWED, g_Upd_Null_NUM, NULL,
1411 DAYS_EARLY_RECEIPT_ALLOWED),
1412 DAYS_LATE_RECEIPT_ALLOWED = decode(DAYS_LATE_RECEIPT_ALLOWED,
1413 g_Upd_Null_NUM, NULL, DAYS_LATE_RECEIPT_ALLOWED),
1414 RECEIPT_DAYS_EXCEPTION_CODE =
1415 decode(RECEIPT_DAYS_EXCEPTION_CODE, g_Upd_Null_CHAR, NULL,
1416 trim(RECEIPT_DAYS_EXCEPTION_CODE)),
1417 RECEIVING_ROUTING_ID = decode(RECEIVING_ROUTING_ID,
1418 g_Upd_Null_NUM, NULL, RECEIVING_ROUTING_ID),
1419 INVOICE_CLOSE_TOLERANCE = decode(INVOICE_CLOSE_TOLERANCE,
1420 g_Upd_Null_NUM, NULL, INVOICE_CLOSE_TOLERANCE),
1421 RECEIVE_CLOSE_TOLERANCE = decode(RECEIVE_CLOSE_TOLERANCE,
1422 g_Upd_Null_NUM, NULL, RECEIVE_CLOSE_TOLERANCE),
1423 AUTO_LOT_ALPHA_PREFIX = decode(AUTO_LOT_ALPHA_PREFIX,
1424 g_Upd_Null_CHAR, NULL, trim(AUTO_LOT_ALPHA_PREFIX)),
1425 START_AUTO_LOT_NUMBER = decode(START_AUTO_LOT_NUMBER,
1426 g_Upd_Null_CHAR, NULL, trim(START_AUTO_LOT_NUMBER)),
1427 SHELF_LIFE_DAYS = decode(SHELF_LIFE_DAYS,
1428 g_Upd_Null_NUM, NULL, SHELF_LIFE_DAYS),
1429 START_AUTO_SERIAL_NUMBER = decode(START_AUTO_SERIAL_NUMBER,
1430 g_Upd_Null_CHAR, NULL, trim(START_AUTO_SERIAL_NUMBER)),
1431 AUTO_SERIAL_ALPHA_PREFIX = decode(AUTO_SERIAL_ALPHA_PREFIX,
1432 g_Upd_Null_CHAR, NULL, trim(AUTO_SERIAL_ALPHA_PREFIX)),
1433 SOURCE_TYPE = decode(SOURCE_TYPE,
1434 g_Upd_Null_NUM, NULL, SOURCE_TYPE),
1435 SOURCE_ORGANIZATION_ID = decode(SOURCE_ORGANIZATION_ID,
1436 g_Upd_Null_NUM, NULL, SOURCE_ORGANIZATION_ID),
1437 SOURCE_SUBINVENTORY = decode(SOURCE_SUBINVENTORY,
1438 g_Upd_Null_CHAR, NULL, trim(SOURCE_SUBINVENTORY)),
1439 EXPENSE_ACCOUNT = decode(EXPENSE_ACCOUNT,
1440 g_Upd_Null_NUM, NULL, EXPENSE_ACCOUNT),
1441 ENCUMBRANCE_ACCOUNT = decode(ENCUMBRANCE_ACCOUNT,
1442 g_Upd_Null_NUM, NULL, ENCUMBRANCE_ACCOUNT),
1443 UNIT_WEIGHT = decode(UNIT_WEIGHT,
1444 g_Upd_Null_NUM, NULL, UNIT_WEIGHT),
1445 WEIGHT_UOM_CODE = decode(WEIGHT_UOM_CODE,
1446 g_Upd_Null_CHAR, NULL, trim(WEIGHT_UOM_CODE)),
1447 VOLUME_UOM_CODE = decode(VOLUME_UOM_CODE,
1448 g_Upd_Null_CHAR, NULL, trim(VOLUME_UOM_CODE)),
1449 UNIT_VOLUME = decode(UNIT_VOLUME,
1450 g_Upd_Null_NUM, NULL, UNIT_VOLUME),
1451 SHRINKAGE_RATE = decode(SHRINKAGE_RATE,
1452 g_Upd_Null_NUM, NULL, SHRINKAGE_RATE),
1453 ACCEPTABLE_EARLY_DAYS = decode(ACCEPTABLE_EARLY_DAYS,
1454 g_Upd_Null_NUM, NULL, ACCEPTABLE_EARLY_DAYS),
1455 DEMAND_TIME_FENCE_CODE = decode(DEMAND_TIME_FENCE_CODE,
1456 g_Upd_Null_NUM, NULL, DEMAND_TIME_FENCE_CODE),
1457 STD_LOT_SIZE = decode(STD_LOT_SIZE,
1458 g_Upd_Null_NUM, NULL, STD_LOT_SIZE),
1459 LEAD_TIME_LOT_SIZE = decode(LEAD_TIME_LOT_SIZE,
1460 g_Upd_Null_NUM, NULL, LEAD_TIME_LOT_SIZE),
1461 CUM_MANUFACTURING_LEAD_TIME =
1462 decode(CUM_MANUFACTURING_LEAD_TIME, g_Upd_Null_NUM, NULL,
1463 CUM_MANUFACTURING_LEAD_TIME),
1464 OVERRUN_PERCENTAGE = decode(OVERRUN_PERCENTAGE,
1465 g_Upd_Null_NUM, NULL, OVERRUN_PERCENTAGE),
1466 ACCEPTABLE_RATE_INCREASE = decode(ACCEPTABLE_RATE_INCREASE,
1467 g_Upd_Null_NUM, NULL, ACCEPTABLE_RATE_INCREASE),
1468 ACCEPTABLE_RATE_DECREASE = decode(ACCEPTABLE_RATE_DECREASE,
1469 g_Upd_Null_NUM, NULL, ACCEPTABLE_RATE_DECREASE),
1470 CUMULATIVE_TOTAL_LEAD_TIME =
1471 decode(CUMULATIVE_TOTAL_LEAD_TIME, g_Upd_Null_NUM, NULL,
1472 CUMULATIVE_TOTAL_LEAD_TIME),
1473 PLANNING_TIME_FENCE_DAYS = decode(PLANNING_TIME_FENCE_DAYS,
1474 g_Upd_Null_NUM, NULL, PLANNING_TIME_FENCE_DAYS),
1475 DEMAND_TIME_FENCE_DAYS = decode(DEMAND_TIME_FENCE_DAYS,
1476 g_Upd_Null_NUM, NULL, DEMAND_TIME_FENCE_DAYS),
1477 RELEASE_TIME_FENCE_CODE = decode(RELEASE_TIME_FENCE_CODE, g_Upd_Null_NUM,
1478 NULL, RELEASE_TIME_FENCE_CODE),
1479 RELEASE_TIME_FENCE_DAYS = decode(RELEASE_TIME_FENCE_DAYS,g_Upd_Null_NUM,
1480 NULL, RELEASE_TIME_FENCE_DAYS),
1481 END_ASSEMBLY_PEGGING_FLAG = decode(END_ASSEMBLY_PEGGING_FLAG,
1482 g_Upd_Null_CHAR, NULL, trim(END_ASSEMBLY_PEGGING_FLAG)),
1483 PLANNING_EXCEPTION_SET = decode(PLANNING_EXCEPTION_SET,
1484 g_Upd_Null_CHAR, NULL, trim(PLANNING_EXCEPTION_SET)),
1485 BASE_ITEM_ID = decode(BASE_ITEM_ID,
1486 g_Upd_Null_NUM, NULL, BASE_ITEM_ID),
1487 FIXED_LEAD_TIME = decode(FIXED_LEAD_TIME,
1488 g_Upd_Null_NUM, NULL, FIXED_LEAD_TIME),
1489 VARIABLE_LEAD_TIME = decode(VARIABLE_LEAD_TIME,
1490 g_Upd_Null_NUM, NULL, VARIABLE_LEAD_TIME),
1491 WIP_SUPPLY_LOCATOR_ID = decode(WIP_SUPPLY_LOCATOR_ID,
1492 g_Upd_Null_NUM, NULL, WIP_SUPPLY_LOCATOR_ID),
1493 WIP_SUPPLY_TYPE = decode(WIP_SUPPLY_TYPE,
1494 g_Upd_Null_NUM, 1, WIP_SUPPLY_TYPE), -- Syalaman - Fix for bug 5886000
1495 WIP_SUPPLY_SUBINVENTORY = decode(WIP_SUPPLY_SUBINVENTORY,
1496 g_Upd_Null_CHAR, NULL, trim(WIP_SUPPLY_SUBINVENTORY)),
1497 PLANNER_CODE = decode(PLANNER_CODE,
1498 g_Upd_Null_CHAR, NULL, trim(PLANNER_CODE)),
1499 FIXED_LOT_MULTIPLIER = decode(FIXED_LOT_MULTIPLIER,
1500 g_Upd_Null_NUM, NULL, FIXED_LOT_MULTIPLIER),
1501 CARRYING_COST = decode(CARRYING_COST,
1502 g_Upd_Null_NUM, NULL, CARRYING_COST),
1503 POSTPROCESSING_LEAD_TIME =
1504 decode(POSTPROCESSING_LEAD_TIME, g_Upd_Null_NUM,NULL,
1505 POSTPROCESSING_LEAD_TIME),
1506 PREPROCESSING_LEAD_TIME = decode(PREPROCESSING_LEAD_TIME,
1507 g_Upd_Null_NUM, NULL, PREPROCESSING_LEAD_TIME),
1508 FULL_LEAD_TIME = decode(FULL_LEAD_TIME,
1509 g_Upd_Null_NUM, NULL, FULL_LEAD_TIME),
1510 ORDER_COST = decode(ORDER_COST,
1511 g_Upd_Null_NUM, NULL, ORDER_COST),
1512 MRP_SAFETY_STOCK_PERCENT = decode(MRP_SAFETY_STOCK_PERCENT,
1513 g_Upd_Null_NUM, NULL, MRP_SAFETY_STOCK_PERCENT),
1514 MIN_MINMAX_QUANTITY = decode(MIN_MINMAX_QUANTITY,
1515 g_Upd_Null_NUM, NULL, MIN_MINMAX_QUANTITY),
1516 MAX_MINMAX_QUANTITY = decode(MAX_MINMAX_QUANTITY,
1517 g_Upd_Null_NUM, NULL, MAX_MINMAX_QUANTITY),
1518 MINIMUM_ORDER_QUANTITY = decode(MINIMUM_ORDER_QUANTITY,
1519 g_Upd_Null_NUM, NULL, MINIMUM_ORDER_QUANTITY),
1520 FIXED_ORDER_QUANTITY = decode(FIXED_ORDER_QUANTITY,
1521 g_Upd_Null_NUM, NULL, FIXED_ORDER_QUANTITY),
1522 FIXED_DAYS_SUPPLY = decode(FIXED_DAYS_SUPPLY,
1523 g_Upd_Null_NUM, NULL, FIXED_DAYS_SUPPLY),
1524 MAXIMUM_ORDER_QUANTITY = decode(MAXIMUM_ORDER_QUANTITY,
1525 g_Upd_Null_NUM, NULL, MAXIMUM_ORDER_QUANTITY),
1526 ATP_RULE_ID = decode(ATP_RULE_ID,
1527 g_Upd_Null_NUM, NULL, ATP_RULE_ID),
1528 PICKING_RULE_ID = decode(PICKING_RULE_ID,
1529 g_Upd_Null_NUM, NULL, PICKING_RULE_ID),
1530 POSITIVE_MEASUREMENT_ERROR =
1531 decode(POSITIVE_MEASUREMENT_ERROR, g_Upd_Null_NUM, NULL,
1532 POSITIVE_MEASUREMENT_ERROR),
1533 NEGATIVE_MEASUREMENT_ERROR =
1534 decode(NEGATIVE_MEASUREMENT_ERROR, g_Upd_Null_NUM, NULL,
1535 NEGATIVE_MEASUREMENT_ERROR),
1536 SERVICE_STARTING_DELAY = decode(SERVICE_STARTING_DELAY,
1537 g_Upd_Null_NUM, NULL, SERVICE_STARTING_DELAY),
1538 PAYMENT_TERMS_ID = decode(PAYMENT_TERMS_ID,
1539 g_Upd_Null_NUM, NULL, PAYMENT_TERMS_ID),
1540 MATERIAL_BILLABLE_FLAG =
1541 decode(MATERIAL_BILLABLE_FLAG,g_Upd_Null_CHAR,NULL,trim(MATERIAL_BILLABLE_FLAG)),
1542 COVERAGE_SCHEDULE_ID = decode(COVERAGE_SCHEDULE_ID,
1543 g_Upd_Null_NUM, NULL, COVERAGE_SCHEDULE_ID),
1544 SERVICE_DURATION_PERIOD_CODE =
1545 decode(SERVICE_DURATION_PERIOD_CODE, g_Upd_Null_CHAR, NULL,
1546 trim(SERVICE_DURATION_PERIOD_CODE)),
1547 SERVICE_DURATION = decode(SERVICE_DURATION,
1548 g_Upd_Null_NUM, NULL, SERVICE_DURATION),
1549 TAX_CODE = decode(TAX_CODE, g_Upd_Null_CHAR,
1550 NULL, trim(TAX_CODE)),
1551 OUTSIDE_OPERATION_UOM_TYPE =
1552 decode(OUTSIDE_OPERATION_UOM_TYPE,g_Upd_Null_CHAR,NULL,trim(OUTSIDE_OPERATION_UOM_TYPE)),
1553 SAFETY_STOCK_BUCKET_DAYS = decode(SAFETY_STOCK_BUCKET_DAYS,
1554 g_Upd_Null_NUM, NULL, SAFETY_STOCK_BUCKET_DAYS),
1555 AUTO_REDUCE_MPS = decode(AUTO_REDUCE_MPS,
1556 g_Upd_Null_NUM, NULL, trim(AUTO_REDUCE_MPS)),
1557 ITEM_TYPE = decode(ITEM_TYPE, g_Upd_Null_CHAR,
1558 NULL, trim(ITEM_TYPE)),
1559 ATO_FORECAST_CONTROL = decode(ATO_FORECAST_CONTROL,
1560 g_Upd_Null_NUM, NULL, ATO_FORECAST_CONTROL),
1561 MAXIMUM_LOAD_WEIGHT = decode(MAXIMUM_LOAD_WEIGHT,
1562 g_Upd_Null_NUM, NULL, MAXIMUM_LOAD_WEIGHT),
1563 MINIMUM_FILL_PERCENT =
1564 decode(MINIMUM_FILL_PERCENT,g_Upd_Null_NUM, NULL, MINIMUM_FILL_PERCENT),
1565 CONTAINER_TYPE_CODE = decode(CONTAINER_TYPE_CODE,
1566 g_Upd_Null_CHAR, NULL, trim(CONTAINER_TYPE_CODE)),
1567 INTERNAL_VOLUME = decode(INTERNAL_VOLUME,
1568 g_Upd_Null_NUM, NULL, INTERNAL_VOLUME),
1569 OVERCOMPLETION_TOLERANCE_TYPE = DECODE( OVERCOMPLETION_TOLERANCE_TYPE,
1570 g_Upd_Null_NUM, NULL, OVERCOMPLETION_TOLERANCE_TYPE ),
1571 OVERCOMPLETION_TOLERANCE_VALUE = DECODE( OVERCOMPLETION_TOLERANCE_VALUE,
1572 g_Upd_Null_NUM, NULL, OVERCOMPLETION_TOLERANCE_VALUE ),
1573 OVER_SHIPMENT_TOLERANCE = DECODE( OVER_SHIPMENT_TOLERANCE,
1574 g_Upd_Null_NUM, NULL, OVER_SHIPMENT_TOLERANCE ),
1575 UNDER_SHIPMENT_TOLERANCE = DECODE(
1576 UNDER_SHIPMENT_TOLERANCE,g_Upd_Null_NUM, NULL, UNDER_SHIPMENT_TOLERANCE ),
1577 OVER_RETURN_TOLERANCE = DECODE( OVER_RETURN_TOLERANCE,
1578 g_Upd_Null_NUM, NULL, OVER_RETURN_TOLERANCE ),
1579 UNDER_RETURN_TOLERANCE = DECODE( UNDER_RETURN_TOLERANCE,
1580 g_Upd_Null_NUM, NULL, UNDER_RETURN_TOLERANCE ),
1581 RECOVERED_PART_DISP_CODE = DECODE( RECOVERED_PART_DISP_CODE,
1582 g_Upd_Null_CHAR, NULL, trim(RECOVERED_PART_DISP_CODE) ),
1583 ASSET_CREATION_CODE = DECODE( ASSET_CREATION_CODE,
1584 g_Upd_Null_CHAR, NULL, trim(ASSET_CREATION_CODE) ),
1585 DIMENSION_UOM_CODE = DECODE( DIMENSION_UOM_CODE,
1586 g_Upd_Null_CHAR, NULL, trim(DIMENSION_UOM_CODE) ),
1587 UNIT_LENGTH = DECODE( UNIT_LENGTH, g_Upd_Null_NUM,
1588 NULL, UNIT_LENGTH ),
1589 UNIT_WIDTH = DECODE( UNIT_WIDTH, g_Upd_Null_NUM,
1590 NULL, UNIT_WIDTH ),
1591 UNIT_HEIGHT = DECODE( UNIT_HEIGHT, g_Upd_Null_NUM,
1592 NULL, UNIT_HEIGHT ),
1593 DEFAULT_LOT_STATUS_ID = DECODE( DEFAULT_LOT_STATUS_ID,
1594 g_Upd_Null_NUM, NULL, DEFAULT_LOT_STATUS_ID ),
1595 DEFAULT_SERIAL_STATUS_ID = DECODE( DEFAULT_SERIAL_STATUS_ID,
1596 g_Upd_Null_NUM, NULL, DEFAULT_SERIAL_STATUS_ID ),
1597 INVENTORY_CARRY_PENALTY = DECODE( INVENTORY_CARRY_PENALTY,
1598 g_Upd_Null_NUM, NULL, INVENTORY_CARRY_PENALTY ),
1599 OPERATION_SLACK_PENALTY = DECODE( OPERATION_SLACK_PENALTY,
1600 g_Upd_Null_NUM, NULL, OPERATION_SLACK_PENALTY ),
1601 EAM_ITEM_TYPE = DECODE( EAM_ITEM_TYPE, g_Upd_Null_NUM, NULL,
1602 EAM_ITEM_TYPE ),
1603 EAM_ACTIVITY_TYPE_CODE = DECODE( EAM_ACTIVITY_TYPE_CODE, g_Upd_Null_CHAR,
1604 NULL, trim(EAM_ACTIVITY_TYPE_CODE) ),
1605 EAM_ACTIVITY_CAUSE_CODE = DECODE( EAM_ACTIVITY_CAUSE_CODE,
1606 g_Upd_Null_CHAR, NULL, trim(EAM_ACTIVITY_CAUSE_CODE) ),
1607 EAM_ACT_NOTIFICATION_FLAG = DECODE( EAM_ACT_NOTIFICATION_FLAG,
1608 g_Upd_Null_CHAR, NULL, trim(EAM_ACT_NOTIFICATION_FLAG) ),
1609 EAM_ACT_SHUTDOWN_STATUS = DECODE( EAM_ACT_SHUTDOWN_STATUS,
1610 g_Upd_Null_CHAR, NULL, trim(EAM_ACT_SHUTDOWN_STATUS) ),
1611 SECONDARY_UOM_CODE = DECODE( SECONDARY_UOM_CODE, g_Upd_Null_CHAR,
1612 NULL, trim(SECONDARY_UOM_CODE) ),
1613 DUAL_UOM_DEVIATION_HIGH = DECODE( DUAL_UOM_DEVIATION_HIGH, g_Upd_Null_NUM,
1614 NULL, DUAL_UOM_DEVIATION_HIGH ),
1615 DUAL_UOM_DEVIATION_LOW = DECODE( DUAL_UOM_DEVIATION_LOW, g_Upd_Null_NUM,
1616 NULL, DUAL_UOM_DEVIATION_LOW ),
1617 CONTRACT_ITEM_TYPE_CODE = DECODE( CONTRACT_ITEM_TYPE_CODE,
1618 g_Upd_Null_CHAR, NULL, trim(CONTRACT_ITEM_TYPE_CODE) ),
1619 SUBSCRIPTION_DEPEND_FLAG = DECODE( SUBSCRIPTION_DEPEND_FLAG,
1620 g_Upd_Null_CHAR, NULL, trim(SUBSCRIPTION_DEPEND_FLAG) ),
1621 SERV_REQ_ENABLED_CODE = DECODE( SERV_REQ_ENABLED_CODE, g_Upd_Null_CHAR,
1622 NULL, trim(SERV_REQ_ENABLED_CODE) ),
1623 SERV_BILLING_ENABLED_FLAG = DECODE( SERV_BILLING_ENABLED_FLAG,
1624 g_Upd_Null_CHAR, NULL, trim(SERV_BILLING_ENABLED_FLAG) ),
1625 SERV_IMPORTANCE_LEVEL = DECODE( SERV_IMPORTANCE_LEVEL, g_Upd_Null_NUM,
1626 NULL, SERV_IMPORTANCE_LEVEL ),
1627 PLANNED_INV_POINT_FLAG = DECODE( PLANNED_INV_POINT_FLAG, g_Upd_Null_CHAR,
1628 NULL, trim(PLANNED_INV_POINT_FLAG) ),
1629 LOT_TRANSLATE_ENABLED = DECODE( LOT_TRANSLATE_ENABLED, g_Upd_Null_CHAR,
1630 NULL, trim(LOT_TRANSLATE_ENABLED) ),
1631 DEFAULT_SO_SOURCE_TYPE = DECODE( DEFAULT_SO_SOURCE_TYPE, g_Upd_Null_CHAR,
1632 NULL, trim(DEFAULT_SO_SOURCE_TYPE) ),
1633 CREATE_SUPPLY_FLAG = DECODE( CREATE_SUPPLY_FLAG, g_Upd_Null_CHAR,
1634 NULL, trim(CREATE_SUPPLY_FLAG) ),
1635 SUBSTITUTION_WINDOW_CODE = DECODE( SUBSTITUTION_WINDOW_CODE,
1636 g_Upd_Null_NUM, NULL, SUBSTITUTION_WINDOW_CODE ),
1637 SUBSTITUTION_WINDOW_DAYS = DECODE( SUBSTITUTION_WINDOW_DAYS,
1638 g_Upd_Null_NUM, NULL, SUBSTITUTION_WINDOW_DAYS ),
1639 LOT_SUBSTITUTION_ENABLED = DECODE( LOT_SUBSTITUTION_ENABLED,
1640 g_Upd_Null_CHAR, NULL, trim(LOT_SUBSTITUTION_ENABLED) ),
1641 MINIMUM_LICENSE_QUANTITY = DECODE( MINIMUM_LICENSE_QUANTITY,
1642 g_Upd_Null_NUM, NULL, MINIMUM_LICENSE_QUANTITY),
1643 EAM_ACTIVITY_SOURCE_CODE = DECODE( EAM_ACTIVITY_SOURCE_CODE,
1644 g_Upd_Null_CHAR, NULL, trim(EAM_ACTIVITY_SOURCE_CODE) ),
1645 IB_ITEM_INSTANCE_CLASS = DECODE( IB_ITEM_INSTANCE_CLASS, g_Upd_Null_CHAR,
1646 NULL, trim(IB_ITEM_INSTANCE_CLASS) ),
1647 CONFIG_MODEL_TYPE = DECODE( CONFIG_MODEL_TYPE, g_Upd_Null_CHAR,
1648 NULL, trim(CONFIG_MODEL_TYPE) ),
1649 TRACKING_QUANTITY_IND = DECODE( TRACKING_QUANTITY_IND, g_Upd_Null_CHAR,
1650 NULL, trim(TRACKING_QUANTITY_IND) ),
1651 ONT_PRICING_QTY_SOURCE = DECODE( ONT_PRICING_QTY_SOURCE, g_Upd_Null_CHAR,
1652 NULL, trim(ONT_PRICING_QTY_SOURCE) ),
1653 SECONDARY_DEFAULT_IND = DECODE( SECONDARY_DEFAULT_IND, g_Upd_Null_CHAR,
1654 NULL, trim(SECONDARY_DEFAULT_IND) ),
1655 CONFIG_ORGS = DECODE( CONFIG_ORGS, g_Upd_Null_CHAR, NULL,
1656 trim(CONFIG_ORGS) ),
1657 CONFIG_MATCH = DECODE( CONFIG_MATCH, g_Upd_Null_CHAR, NULL,
1658 trim(CONFIG_MATCH) ),
1659 LIFECYCLE_ID =
1660 decode(LIFECYCLE_ID,g_Upd_Null_NUM,NULL,LIFECYCLE_ID),
1661 CURRENT_PHASE_ID =
1662 decode(CURRENT_PHASE_ID,g_Upd_Null_NUM,NULL,CURRENT_PHASE_ID),
1663 VMI_MINIMUM_UNITS = DECODE( VMI_MINIMUM_UNITS,g_Upd_Null_NUM, NULL,
1664 VMI_MINIMUM_UNITS ) ,
1665 VMI_MINIMUM_DAYS = DECODE( VMI_MINIMUM_DAYS, g_Upd_Null_NUM, NULL,
1666 VMI_MINIMUM_DAYS ) ,
1667 VMI_MAXIMUM_UNITS = DECODE( VMI_MAXIMUM_UNITS,g_Upd_Null_NUM, NULL,
1668 VMI_MAXIMUM_UNITS ),
1669 VMI_MAXIMUM_DAYS = DECODE( VMI_MAXIMUM_DAYS, g_Upd_Null_NUM, NULL,
1670 VMI_MAXIMUM_DAYS ),
1671 VMI_FIXED_ORDER_QUANTITY = DECODE( VMI_FIXED_ORDER_QUANTITY,
1672 g_Upd_Null_NUM, NULL, VMI_FIXED_ORDER_QUANTITY ),
1673 SO_AUTHORIZATION_FLAG = DECODE(SO_AUTHORIZATION_FLAG, g_Upd_Null_NUM,
1674 NULL, SO_AUTHORIZATION_FLAG ),
1675 CONSIGNED_FLAG = DECODE(CONSIGNED_FLAG, g_Upd_Null_NUM,
1676 NULL,CONSIGNED_FLAG ),
1677 ASN_AUTOEXPIRE_FLAG = DECODE( ASN_AUTOEXPIRE_FLAG, g_Upd_Null_NUM,
1678 NULL, ASN_AUTOEXPIRE_FLAG ),
1679 VMI_FORECAST_TYPE = DECODE( VMI_FORECAST_TYPE, g_Upd_Null_NUM, NULL,
1680 VMI_FORECAST_TYPE ),
1681 FORECAST_HORIZON = DECODE( FORECAST_HORIZON, g_Upd_Null_NUM,
1682 NULL,FORECAST_HORIZON ),
1683 EXCLUDE_FROM_BUDGET_FLAG = DECODE( EXCLUDE_FROM_BUDGET_FLAG,
1684 g_Upd_Null_NUM, NULL, EXCLUDE_FROM_BUDGET_FLAG ),
1685 DAYS_TGT_INV_SUPPLY = DECODE( DAYS_TGT_INV_SUPPLY,
1686 g_Upd_Null_NUM,NULL, DAYS_TGT_INV_SUPPLY),
1687 DAYS_TGT_INV_WINDOW = DECODE( DAYS_TGT_INV_WINDOW, g_Upd_Null_NUM,
1688 NULL, DAYS_TGT_INV_WINDOW ),
1689 DAYS_MAX_INV_SUPPLY = DECODE( DAYS_MAX_INV_SUPPLY,g_Upd_Null_NUM,
1690 NULL, DAYS_MAX_INV_SUPPLY ),
1691 DAYS_MAX_INV_WINDOW = DECODE( DAYS_MAX_INV_WINDOW, g_Upd_Null_NUM,
1692 NULL, DAYS_MAX_INV_WINDOW ),
1693 DRP_PLANNED_FLAG = DECODE( DRP_PLANNED_FLAG, g_Upd_Null_NUM, NULL,
1694 DRP_PLANNED_FLAG ),
1695 CRITICAL_COMPONENT_FLAG = DECODE( CRITICAL_COMPONENT_FLAG, g_Upd_Null_NUM,
1696 NULL, CRITICAL_COMPONENT_FLAG ),
1697 CONTINOUS_TRANSFER = DECODE( CONTINOUS_TRANSFER, g_Upd_Null_NUM,
1698 NULL, CONTINOUS_TRANSFER ),
1699 CONVERGENCE = DECODE( CONVERGENCE, g_Upd_Null_NUM, NULL, CONVERGENCE
1700 ),
1701 DIVERGENCE = DECODE( DIVERGENCE, g_Upd_Null_NUM, NULL, DIVERGENCE )
1702 WHERE
1703 MSII.rowid = cr.rowid;
1704
1705 /*End of bug 6417006*/
1706
1707
1708 if rtn_status = 0 then
1709 rtn_status := INVPULI4.assign_status_attributes(
1710 cr.inventory_item_id,
1711 cr.organization_id,
1712 err_text,
1713 xset_id,
1714 cr.rowid);
1715 if rtn_status <> 0 then
1716 -- raise ASS_ITEM_ERR;
1717 -- 14170679, doesn't break import, flag it and continue
1718 update MTL_SYSTEM_ITEMS_INTERFACE
1719 set process_flag = l_process_flag_3
1720 where rowid = cr.rowid ;
1721
1722 dumm_status := INVPUOPI.mtl_log_interface_err(
1723 -1,
1724 user_id,
1725 login_id,
1726 prog_appid,
1727 prog_id,
1728 request_id,
1729 cr.transaction_id,
1730 'INVPAGI2: Invalid Item Status Code',
1731 'INVENTORY_ITEM_STATUS_CODE',
1732 'MTL_SYSTEM_ITEMS_INTERFACE',
1733 'INV_IOI_STATUS_CODE',
1734 err_text);
1735 if dumm_status < 0 then
1736 raise LOGGING_ERR;
1737 end if;
1738
1739 end if;
1740 end if;
1741
1742 -- Added for supporting copy item through Import Catalog Items.
1743 -- when we pass copy_item_id and copy_organization_id to MSII and run ICI or Import Item
1744 -- it should copy the assignments for target item also
1745
1746 IF cr.copy_item_id IS NOT NULL AND cr.inventory_item_id IS NOT NULL AND cr.organization_id IS NOT NULL THEN
1747
1748 g_copy_item_id:= cr.copy_item_id;
1749
1750 END IF;
1751
1752 --Serial_Tagging Item -- bug 9913552
1753 IF g_copy_item_id IS NOT NULL AND g_copy_item_id <>g_upd_null_num THEN
1754
1755 IF INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => g_copy_item_id,
1756 p_organization_id => cr.organization_id)=2 THEN
1757
1758 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
1759 p_from_item_id =>g_copy_item_id,
1760 p_from_org_id =>cr.organization_id,
1761 p_to_item_id => cr.inventory_item_id,
1762 p_to_org_id =>cr.organization_id,
1763 x_return_status => x_ret_sts);
1764
1765 g_copy_item_id := -999999;
1766
1767 IF x_ret_sts <>FND_API.G_RET_STS_SUCCESS THEN
1768
1769 dumm_status := INVPUOPI.mtl_log_interface_err(
1770 cr.organization_id,
1771 user_id,
1772 login_id,
1773 prog_appid,
1774 prog_id,
1775 request_id,
1776 tran_id,
1777 error_msg,
1778 'SERIAL_TAGGING_FLAG',
1779 'MTL_SYSTEM_ITEMS_INTERFACE',
1780 'INV_COPY_SER_FAIL_UNEXP',
1781 err_text);
1782 if dumm_status < 0 then
1783 raise LOGGING_ERR;
1784 end if;
1785 END IF;
1786
1787 END IF ;
1788 END IF ;
1789 -- this is for copy template case during insert mode
1790 -- added >1 condition as from PIM UI template_id is always coming as -1
1791 IF (cr.template_id IS NOT NULL) AND (cr.template_id > 0)
1792 AND (cr.template_id <> FND_API.G_MISS_NUM) ---- added AND bug 10388397
1793 THEN
1794 --bug 10065810
1795 begin
1796 SELECT CONTEXT_ORGANIZATION_ID into l_temp_org_id
1797 FROM mtl_item_templates
1798 WHERE template_id=cr.template_id;
1799 end;
1800
1801 IF (INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_template_id=>cr.template_id
1802 --bug 10065810
1803 ,p_organization_id=>l_temp_org_id
1804 )=2) THEN
1805
1806 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
1807 p_from_template_id => cr.template_id,
1808 -- bug 10065810
1809 p_from_org_id => l_temp_org_id,
1810 p_to_item_id => cr.inventory_item_id,
1811 p_to_org_id => cr.organization_id,
1812 x_return_status => x_ret_sts);
1813
1814 IF x_ret_sts <>FND_API.G_RET_STS_SUCCESS THEN
1815
1816 dumm_status := INVPUOPI.mtl_log_interface_err(
1817 cr.organization_id,
1818 user_id,
1819 login_id,
1820 prog_appid,
1821 prog_id,
1822 request_id,
1823 tran_id,
1824 error_msg,
1825 'SERIAL_TAGGING_FLAG',
1826 'MTL_SYSTEM_ITEMS_INTERFACE',
1827 'NV_COPY_SER_FAIL_UNEXP',
1828 err_text);
1829 if dumm_status < 0 then
1830 raise LOGGING_ERR;
1831 end if;
1832 END IF;
1833
1834 END IF ;
1835 END IF ;
1836
1837
1838
1839 revs := 0;
1840
1841 --Start 2861248 :Populate Item Id for default revision only
1842 ---Start: Bug fix 3051653. Bug Fix 8428488.
1843 if rtn_status = 0 then
1844 l_item_id := NULL;
1845 IF cr.item_number IS NULL THEN
1846 rtn_status := INVPUOPI.mtl_pr_parse_item_segments(p_row_id =>cr.rowid
1847 ,item_number =>l_item_number
1848 ,item_id =>l_item_id
1849 ,err_text => err_text);
1850 cr.item_number := l_item_number;
1851 if rtn_status < 0 then
1852 raise ASS_ITEM_ERR;
1853 end if;
1854
1855 --Bug: 5512333
1856 IF cr.item_number IS NOT NULL THEN
1857 rtn_status := INVPUOPI.mtl_pr_parse_item_number(cr.item_number
1858 ,cr.inventory_item_id
1859 ,cr.transaction_id
1860 ,cr.organization_id
1861 ,err_text
1862 ,cr.rowid);
1863 if rtn_status < 0 then
1864 -- raise ASS_ITEM_ERR;
1865 --Fix for Bug 9706610
1866
1867 /* Bug 13503782 : Start
1868 We should not raise exception, so that other records are processed.
1869 Log the error and set this record to 3 in MSII and proceed to next record.
1870 */
1871 -- RAISE ITEM_NUMBER_ERR ;
1872 dumm_status := INVPUOPI.mtl_log_interface_err(
1873 l_org_id,
1874 user_id,
1875 login_id,
1876 prog_appid,
1877 prog_id,
1878 request_id,
1879 tran_id,
1880 err_text,
1881 null,
1882 'MTL_SYSTEM_ITEMS_INTERFACE',
1883 'INV_ITEM_INVALID',
1884 err_text);
1885
1886 UPDATE mtl_system_items_interface
1887 SET process_flag = 3
1888 WHERE ROWID= cr.ROWID;
1889
1890 CONTINUE;
1891 -- Bug 13503782 : End
1892 end if; -- end of if(rtn_status < 0)
1893 END IF; -- end of if(cr.item_number IS NOT NULL)
1894 --End Bug: 5512333
1895 END IF; -- end of if (cr.item_number IS NULL)
1896 end if; -- End: Bug Fix 8428488.
1897
1898 -- bug 4539703
1899 -- Store sysdate minus 1 sec when inserting default revision data
1900 l_curr_sysdate := sysdate - 1/86400;
1901
1902 --default rev should be sysdate
1903 update /*+ index(MTL_ITEM_REVISIONS_INTERFACE, MTL_ITEM_REVS_INTERFACE_N2) */ -- Bug 9678667 , adding hint suggested by perf team
1904 mtl_item_revisions_interface
1905 set effectivity_date = sysdate
1906 where set_process_id = xset_id
1907 and process_flag = 1
1908 /* Bug 9678667 - Start */
1909 AND inventory_item_id = cr.inventory_item_id
1910 AND organization_id = cr.organization_id
1911 /* Bug 9678667 - End */
1912 and revision = cr.revision
1913 and (effectivity_date is null or effectivity_date > sysdate);
1914
1915
1916 --Passing item number and organization_id to cursor for bug 3051653
1917 FOR c_revision_record IN c_get_revisions(l_item_number,
1918 cr.revision,cr.organization_id) LOOP
1919 IF l_item_id IS NULL THEN
1920 dumm_status := INVPUOPI.mtl_pr_parse_flex_name (
1921 c_revision_record.organization_id,
1922 'MSTK', c_revision_record.item_number,
1923 flex_id, 0, err_temp);
1924 ELSE
1925 flex_id := l_item_id;
1926 dumm_status := 0;
1927 END IF;
1928
1929
1930 IF dumm_status = 0 THEN
1931 update mtl_item_revisions_interface
1932 set inventory_item_id = flex_id
1933 where item_number = c_revision_record.item_number
1934 and set_process_id = xset_id
1935 and organization_id = c_revision_record.organization_id
1936 and revision = cr.revision
1937 RETURNING effectivity_date,rowid INTO l_effectivity_date,l_rowid;
1938
1939 --2885843:Effectivity date to sysdate if passed date is > sysdate
1940 IF (l_effectivity_date IS NULL
1941 OR TRUNC(l_effectivity_date) > TRUNC(SYSDATE)) THEN
1942
1943 update mtl_item_revisions_interface
1944 set effectivity_date = sysdate
1945 where rowid = l_rowid;
1946
1947 END IF;
1948 END IF;
1949 END LOOP;
1950
1951 --End 2861248 :Populate Item Id for default revision only
1952
1953 SELECT /*+ use_concat */ count(*) INTO revs -- Bug 9678667 : Adding hint suggested by perf team
1954 FROM mtl_item_revisions_interface
1955 WHERE ((organization_id = cr.organization_id
1956 AND inventory_item_id = cr.inventory_item_id)
1957 OR (organization_id = cr.organization_id
1958 AND item_number = cr.item_number)
1959 OR (organization_id = cr.organization_id
1960 AND source_system_reference=cr.source_system_reference
1961 AND source_system_id=cr.source_system_id))--Bug 14001276
1962 AND revision = cr.revision
1963 AND process_flag = 1--Bug No: 3344480
1964 AND set_process_id = cr.set_process_id;
1965
1966 /*** insert a record into the revs interface table because one does not exist
1967 ** NP 06MAY96 Now inserting xset_id into set_process_id for MIRI
1968 ** NP 28MAY96 Choose the set_process_id of the relevant record for insertion to MIRI
1969 ** not the xset_id*/
1970
1971 l_transaction_type := 'CREATE' ;
1972 if (revs = 0) then
1973 if (cr.revision = default_rev) then -- Bug 4539703 for default revision create entry with sysdate - 1 sec
1974 --Bug 4626774 added request_id in both inserts
1975 --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
1976 insert into mtl_item_revisions_interface
1977 (organization_id, inventory_item_id, revision,
1978 process_flag, transaction_type, set_process_id, implementation_date,
1979 effectivity_date, creation_date, last_update_date
1980 ,request_id, source_system_id, source_system_reference)
1981 values(cr.organization_id, cr.inventory_item_id, cr.revision,
1982 l_process_flag_1, l_transaction_type, cr.set_process_id, l_curr_sysdate,
1983 l_curr_sysdate, l_curr_sysdate, l_curr_sysdate
1984 ,request_id, cr.source_system_id, cr.source_system_reference);
1985 else
1986 --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
1987 insert into mtl_item_revisions_interface
1988 (organization_id, inventory_item_id, revision,
1989 process_flag, transaction_type, set_process_id
1990 ,request_id, source_system_id, source_system_reference)
1991 values (cr.organization_id, cr.inventory_item_id, cr.revision,
1992 l_process_flag_1, l_transaction_type, cr.set_process_id
1993 ,request_id, cr.source_system_id, cr.source_system_reference);
1994 end if;
1995 end if;
1996
1997 /*** check to see if a record exists in the revs interface table for this
1998 ** item/org/rev combination for the DEFAULT STARTING REVISION*/
1999
2000 if (cr.revision <> default_rev) then
2001 revs := 0;
2002
2003 select count(revision) into revs
2004 from mtl_item_revisions_interface
2005 where set_process_id = xset_id
2006 and revision = default_rev
2007 AND process_flag = 1--Bug No: 3344480
2008 and ((organization_id = cr.organization_id
2009 and inventory_item_id = cr.inventory_item_id)
2010 or(organization_id = cr.organization_id
2011 and item_number = cr.item_number));
2012
2013 /*** insert a record into the revs interface table because one does not exist
2014 ** for the DEFAULT STARTING REVISION
2015 ** Included implementation_date, effectivity_date , creation_date and last_update_date in the below
2016 sql query to insert sysdate value for bug fix 3226359 */
2017
2018 l_transaction_type := 'CREATE' ;
2019 if (revs = 0) then
2020 -- bug 4539703
2021 --Bug 4626774 added request_id in the insert
2022 --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
2023 insert into mtl_item_revisions_interface -- create a default revision with sysdate - 1 sec
2024 (organization_id, inventory_item_id, revision,
2025 process_flag, transaction_type, set_process_id,implementation_date,
2026 effectivity_date,creation_date,last_update_date
2027 ,request_id, source_system_id, source_system_reference)
2028 values (cr.organization_id, cr.inventory_item_id, default_rev,
2029 l_process_flag_1, l_transaction_type, cr.set_process_id, l_curr_sysdate,
2030 l_curr_sysdate, l_curr_sysdate,l_curr_sysdate
2031 ,request_id, cr.source_system_id, cr.source_system_reference);
2032 end if;
2033 end if;
2034
2035 /*** we finished one record assignment, let's update it*/
2036 if (validation_check_status = 0) then
2037 if (rtn_status = 0) then
2038 if default_flag = 1 then
2039 process_flag_temp := 2;
2040 else
2041 process_flag_temp := 1;
2042 end if;
2043 else
2044 process_flag_temp := 3;
2045 end if;
2046 else /*validation check failed */
2047 process_flag_temp := 3;
2048 end if;
2049
2050 update MTL_SYSTEM_ITEMS_INTERFACE
2051 set process_flag = process_flag_temp,
2052 creation_date = nvl(creation_date, l_sysdate),
2053 revision = cr.revision
2054 where rowid = cr.rowid ;
2055
2056 if rtn_status <> 0 then
2057 error_msg := err_text;
2058 if rtn_status=1403 then
2059 rtn_status := INVPUOPI.mtl_log_interface_err(
2060 l_org_id,
2061 user_id,
2062 login_id,
2063 prog_appid,
2064 prog_id,
2065 request_id,
2066 tran_id,
2067 error_msg,
2068 'DESCRIPTION',
2069 'MTL_SYSTEM_ITEMS_INTERFACE',
2070 'INV_DESC_ITEM_ERROR',
2071 err_text);
2072 else
2073 rtn_status := INVPUOPI.mtl_log_interface_err(
2074 cr.organization_id,
2075 user_id,
2076 login_id,
2077 prog_appid,
2078 prog_id,
2079 request_id,
2080 cr.transaction_id,
2081 error_msg,
2082 null,
2083 'MTL_SYSTEM_ITEMS_INTERFACE',
2084 'BOM_PARSE_ITEM_ERROR',
2085 err_text);
2086 end if;
2087 if rtn_status < 0 then
2088 raise LOGGING_ERR;
2089 end if;
2090 end if;
2091 else --org_flag <> 0
2092 dumm_status := INVPUOPI.mtl_log_interface_err(
2093 l_org_id,
2094 user_id,
2095 login_id,
2096 prog_appid,
2097 prog_id,
2098 request_id,
2099 tran_id,
2100 err_text,
2101 'ORGANIZATION_ID',
2102 'MTL_SYSTEM_ITEMS_INTERFACE',
2103 'INVALID ORGANIZATION',
2104 err_text);
2105 if dumm_status < 0 then
2106 raise LOGGING_ERR;
2107 end if;
2108
2109 update mtl_system_items_interface
2110 set process_flag = l_process_flag_3
2111 where rowid = cr.rowid ;
2112 end if; --org_flag <> 0
2113
2114 l_old_organization_id := cr.organization_id ;
2115
2116 end loop;
2117
2118 -- Fix for bug#9336604
2119 IF (FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema)) THEN
2120
2121 IF (nvl(fnd_profile.value('EGO_ENABLE_GATHER_STATS'),'N') = 'Y') THEN
2122
2123 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2124 TABNAME => 'MTL_SYSTEM_ITEMS_INTERFACE',
2125 CASCADE => True);
2126 END IF;
2127
2128 END IF;
2129
2130 return(0);
2131
2132 exception
2133 when ASS_ITEM_ERR then
2134 error_msg := err_text;
2135 dumm_status := INVPUOPI.mtl_log_interface_err(
2136 l_org_id,
2137 user_id,
2138 login_id,
2139 prog_appid,
2140 prog_id,
2141 request_id,
2142 tran_id,
2143 error_msg,
2144 null,
2145 'MTL_SYSTEM_ITEMS_INTERFACE',
2146 'BOM_PARSE_ITEM_ERROR',
2147 err_text);
2148 return(rtn_status);
2149
2150 when LOGGING_ERR then
2151 err_text := 'logging error raised in INVPAGI2.assign_item_header_recs with message:' || err_text; --added by bug 11894684
2152 return(dumm_status);
2153 --Fix for Bug 9706610
2154 /* Bug 13503782 : Start
2155 -- Commenting the below exception, this is not required as we have logged the error already.
2156 when ITEM_NUMBER_ERR then
2157 dumm_status := INVPUOPI.mtl_log_interface_err(
2158 l_org_id,
2159 user_id,
2160 login_id,
2161 prog_appid,
2162 prog_id,
2163 request_id,
2164 tran_id,
2165 err_text,
2166 null,
2167 'MTL_SYSTEM_ITEMS_INTERFACE',
2168 'INV_ITEM_INVALID',
2169 err_text);
2170 return(rtn_status);
2171 */
2172 when OTHERS then
2173 error_msg := 'INVPAGI2.assign_item_header_recs:' || SQLERRM;
2174 dumm_status := INVPUOPI.mtl_log_interface_err(
2175 l_org_id ,
2176 user_id,
2177 login_id,
2178 prog_appid,
2179 prog_id,
2180 request_id,
2181 tran_id,
2182 error_msg,
2183 null,
2184 'MTL_SYSTEM_ITEMS_INTERFACE',
2185 'BOM_PARSE_ITEM_ERROR',
2186 err_text);
2187 if (rtn_status = 0) then
2188 rtn_status := -1;
2189 end if;
2190 return(rtn_status);
2191 end assign_item_header_recs;
2192
2193 end INVPAGI2;