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