[Home] [Help]
PACKAGE BODY: APPS.INVPUTLI
Source
1 PACKAGE BODY INVPUTLI AS
2 /* $Header: INVPUL1B.pls 120.25.12010000.2 2008/11/25 02:26:05 akbharga ship $ */
3 indent NUMBER := 1;
4 g_Debug_Level NUMBER := 0;
5
6 PROCEDURE send (P_message VARCHAR2);
7
8 PROCEDURE info (p_message VARCHAR2)
9 IS
10 BEGIN
11 IF ( g_Debug_Level = 101 ) THEN
12 DBMS_Output.put_line('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] **> ' || p_message);
13 -- FND_FILE.put_line (FND_FILE.log, '['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] **> ' || p_message);
14 ELSIF ( g_Debug_Level = 102 ) THEN
15 FND_FILE.put_line (FND_FILE.log, '['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] **> ' || p_message);
16 END IF;
17 END info;
18
19 PROCEDURE send (P_message VARCHAR2)
20 IS
21 id NUMBER;
22 BEGIN
23 IF indent < 1 or indent > 60 THEN
24 dbms_pipe.pack_message(
25 ' Indent: ' || to_char(indent) || ' is invalid ' ||
26 p_message );
27 ELSE
28 IF p_message LIKE '%Stop Server' THEN
29 dbms_pipe.pack_message('Abort');
30 ELSE
31 dbms_pipe.pack_message(lpad(' ', Indent) || P_message);
32 END IF;
33 END IF;
34 id := dbms_pipe.send_message('INVDEBUG', 1);
35 IF id <> 0 THEN
36 null; -- could not arcs in with a call to dbms_output.put
37 -- dbms_output.Put('Could not send through pipe status: ' || to_char(id));
38 END IF;
39
40 END send;
41
42
43 FUNCTION get_dynamic_sql_str(
44 dyn_sql_num IN NUMBER,
45 sql_text OUT NOCOPY VARCHAR2,
46 err_text OUT NOCOPY VARCHAR2
47 )
48 RETURN INTEGER
49 IS
50 TYPE NumType IS TABLE OF NUMBER INDEX BY binary_integer;
51 TYPE CharType IS TABLE OF VARCHAR2(1) INDEX BY binary_integer;
52 seg_tab NumType;
53 req_tab CharType;
54 segnum_temp VARCHAR2(2);
55 stmt VARCHAR2(2000);
56 max_segment NUMBER;
57 return_status NUMBER;
58 pos NUMBER;
59 ind NUMBER;
60 totalsegs NUMBER;
61 segnum NUMBER;
62 l_required_flag VARCHAR2(1);
63
64 l_application_id NUMBER(10) := 401;
65 l_id_flex_code VARCHAR2(4) := 'MSTK';
66 l_enabled_flag VARCHAR2(1) := 'Y';
67 l_id_flex_num NUMBER(15) := 101 ;
68 BEGIN
69 err_text := NULL;
70 stmt := NULL;
71
72 FOR n IN 1..20 LOOP
73 seg_tab(n) := NULL;
74 END LOOP;
75
76 SELECT MAX(fs.segment_num), COUNT(*)
77 INTO max_segment, totalsegs
78 FROM fnd_id_flex_segments fs
79 WHERE fs.application_id = l_application_id
80 AND fs.id_flex_code = l_id_flex_code
81 AND fs.enabled_flag = l_enabled_flag
82 AND fs.id_flex_num = l_id_flex_num;
83
84 return_status := 1;
85 pos := 1;
86 ind := 1;
87
88 FOR n IN 1..max_segment LOOP
89 BEGIN
90
91 SELECT TO_NUMBER(SUBSTR(fs.application_column_name, 8)), required_flag
92 INTO segnum, l_required_flag
93 FROM fnd_id_flex_segments fs
94 WHERE fs.segment_num = n
95 AND fs.id_flex_code = l_id_flex_code
96 AND fs.id_flex_num = l_id_flex_num
97 AND fs.enabled_flag = l_enabled_flag
98 AND fs.application_id = l_application_id;
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 segnum := NULL;
103 WHEN OTHERS THEN
104 raise_application_error(-20001, SQLERRM);
105
106 END;
107
108 IF segnum IS NOT NULL THEN
109 seg_tab(ind) := segnum;
110 req_tab(ind) := l_required_flag;
111 ind := ind + 1;
112 END IF;
113 END LOOP;
114
115 IF dyn_sql_num = 1 THEN
116 BEGIN
117 stmt := '';
118 FOR n IN 1..totalsegs LOOP
119 segnum_temp := to_char(SEG_TAB(n));
120 IF n <> 1 THEN
121 stmt := stmt ||' and ';
122 END IF;
123 IF req_tab(n)= 'Y' THEN
124 stmt := stmt || ' msi.segment'||segnum_temp|| ' = msii.segment' ||segnum_temp ;
125 ELSE
126 stmt := stmt || ' nvl(msi.segment'||segnum_temp||',''none'') = nvl(msii.segment'||
127 segnum_temp||' ,''none'') ' ;
128 END IF;
129 END LOOP;
130 END;
131 ELSIF dyn_sql_num = 2 THEN
132 BEGIN
133 stmt := '';
134 FOR n IN 1..totalsegs LOOP
135 segnum_temp := TO_CHAR(seg_tab(n));
136 IF n <> 1 THEN
137 stmt := stmt ||' and ';
138 END IF;
139 IF req_tab(n) = 'Y' THEN
140 stmt := stmt || ' msi.segment'||segnum_temp||' = nvl(:cr_segment'||segnum_temp||'_bind'||' ,''.'') ' ;
141 ELSE
142 stmt := stmt || ' nvl(msi.segment'||segnum_temp||',''.'') = nvl(:cr_segment'||segnum_temp||
143 '_bind'||' ,''.'') ' ;
144 END IF;
145 END LOOP;
146 END;
147 ELSIF dyn_sql_num = 3 THEN
148 BEGIN
149 stmt := '';
150 FOR n IN 1..totalsegs LOOP
151 segnum_temp := to_char(SEG_TAB(n));
152 IF n <> 1 THEN
153 stmt := stmt ||' and ';
154 END IF;
155 IF req_tab(n) = 'Y' THEN
156 stmt := stmt || ' msii.segment'||segnum_temp||' = nvl(:cr_segment'||segnum_temp||'_bind'||' ,''.'') ' ;
157 ELSE
158 stmt := stmt || ' nvl(msii.segment'||segnum_temp||',''.'') = nvl(:cr_segment'||segnum_temp||
159 '_bind'||' ,''.'') ' ;
160 END IF ;
161 END LOOP;
162 END;
163 END IF;
164
165 sql_text := STMT;
166 return(0);
167
168 EXCEPTION
169
170 WHEN OTHERS THEN
171 err_text := substr('INVPUTLI.get_dynamic_sql_str:' || SQLERRM , 1,240);
172 return(SQLCODE);
173
174 END get_dynamic_sql_str;
175
176 FUNCTION predefault_child_master
177 (
178 item_id NUMBER,
179 org_id NUMBER,
180 master_org_id NUMBER,
181 err_text out NOCOPY VARCHAR2,
182 xset_id in NUMBER DEFAULT -999 ,
183 p_rowid ROWID
184 )
185 RETURN INTEGER
186 IS
187 l_created_by NUMBER;
188 BEGIN
189 l_created_by := 1;
190
191 UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
192 SET
193 ( I.LAST_UPDATED_BY,
194 I.CREATED_BY,
195 I.SUMMARY_FLAG,
196 I.ENABLED_FLAG,
197 I.START_DATE_ACTIVE,
198 I.END_DATE_ACTIVE,
199 I.DESCRIPTION,
200 I.LONG_DESCRIPTION,
201 I.BUYER_ID,
202 I.ACCOUNTING_RULE_ID,
203 I.INVOICING_RULE_ID,
204 I.SEGMENT1,
205 I.SEGMENT2,
206 I.SEGMENT3,
207 I.SEGMENT4,
208 I.SEGMENT5,
209 I.SEGMENT6,
210 I.SEGMENT7,
211 I.SEGMENT8,
212 I.SEGMENT9,
213 I.SEGMENT10,
214 I.SEGMENT11,
215 I.SEGMENT12,
216 I.SEGMENT13,
217 I.SEGMENT14,
218 I.SEGMENT15,
219 I.SEGMENT16,
220 I.SEGMENT17,
221 I.SEGMENT18,
222 I.SEGMENT19,
223 I.SEGMENT20,
224 I.ATTRIBUTE_CATEGORY,
225 I.ATTRIBUTE1,
226 I.ATTRIBUTE2,
227 I.ATTRIBUTE3,
228 I.ATTRIBUTE4,
229 I.ATTRIBUTE5,
230 I.ATTRIBUTE6,
231 I.ATTRIBUTE7,
232 I.ATTRIBUTE8,
233 I.ATTRIBUTE9,
234 I.ATTRIBUTE10,
235 I.ATTRIBUTE11,
236 I.ATTRIBUTE12,
237 I.ATTRIBUTE13,
238 I.ATTRIBUTE14,
239 I.ATTRIBUTE15,
240 /* Start Bug 3713912 */
241 I.ATTRIBUTE16,
242 I.ATTRIBUTE17,
243 I.ATTRIBUTE18,
244 I.ATTRIBUTE19,
245 I.ATTRIBUTE20,
246 I.ATTRIBUTE21,
247 I.ATTRIBUTE22,
248 I.ATTRIBUTE23,
249 I.ATTRIBUTE24,
250 I.ATTRIBUTE25,
251 I.ATTRIBUTE26,
252 I.ATTRIBUTE27,
253 I.ATTRIBUTE28,
254 I.ATTRIBUTE29,
255 I.ATTRIBUTE30,
256 /* End Bug 3713912 */
257 I.GLOBAL_ATTRIBUTE_CATEGORY,
258 I.GLOBAL_ATTRIBUTE1,
259 I.GLOBAL_ATTRIBUTE2,
260 I.GLOBAL_ATTRIBUTE3,
261 I.GLOBAL_ATTRIBUTE4,
262 I.GLOBAL_ATTRIBUTE5,
263 I.GLOBAL_ATTRIBUTE6,
264 I.GLOBAL_ATTRIBUTE7,
265 I.GLOBAL_ATTRIBUTE8,
266 I.GLOBAL_ATTRIBUTE9,
267 I.GLOBAL_ATTRIBUTE10,
268 I.GLOBAL_ATTRIBUTE11,
269 I.GLOBAL_ATTRIBUTE12,
270 I.GLOBAL_ATTRIBUTE13,
271 I.GLOBAL_ATTRIBUTE14,
272 I.GLOBAL_ATTRIBUTE15,
273 I.GLOBAL_ATTRIBUTE16,
274 I.GLOBAL_ATTRIBUTE17,
275 I.GLOBAL_ATTRIBUTE18,
276 I.GLOBAL_ATTRIBUTE19,
277 I.GLOBAL_ATTRIBUTE20,
278 I.PURCHASING_ITEM_FLAG,
279 I.SHIPPABLE_ITEM_FLAG,
280 I.CUSTOMER_ORDER_FLAG,
281 I.INTERNAL_ORDER_FLAG,
282 I.INVENTORY_ITEM_FLAG,
283 I.ENG_ITEM_FLAG,
284 I.INVENTORY_ASSET_FLAG,
285 -- * For the bug 3888752 from its base bug --ANMURALI
286 -- I.PURCHASING_ENABLED_FLAG,
287 -- I.CUSTOMER_ORDER_ENABLED_FLAG,
288 -- I.INTERNAL_ORDER_ENABLED_FLAG,
289 I.SO_TRANSACTIONS_FLAG,
290 -- I.MTL_TRANSACTIONS_ENABLED_FLAG,
291 -- I.STOCK_ENABLED_FLAG,
292 -- I.BOM_ENABLED_FLAG,
293 -- I.BUILD_IN_WIP_FLAG,
294 I.REVISION_QTY_CONTROL_CODE,
295 I.ITEM_CATALOG_GROUP_ID,
296 I.CATALOG_STATUS_FLAG,
297 I.RETURNABLE_FLAG,
298 I.DEFAULT_SHIPPING_ORG,
299 I.COLLATERAL_FLAG,
300 I.TAXABLE_FLAG,
301 I.PURCHASING_TAX_CODE,
302 I.QTY_RCV_EXCEPTION_CODE,
303 I.ALLOW_ITEM_DESC_UPDATE_FLAG,
304 I.INSPECTION_REQUIRED_FLAG,
305 I.RECEIPT_REQUIRED_FLAG,
306 I.MARKET_PRICE,
307 I.HAZARD_CLASS_ID,
308 I.RFQ_REQUIRED_FLAG,
309 I.QTY_RCV_TOLERANCE,
310 I.LIST_PRICE_PER_UNIT,
311 I.UN_NUMBER_ID,
312 I.PRICE_TOLERANCE_PERCENT,
313 I.ASSET_CATEGORY_ID,
314 I.ROUNDING_FACTOR,
315 I.UNIT_OF_ISSUE,
316 I.ENFORCE_SHIP_TO_LOCATION_CODE,
317 I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
318 I.ALLOW_UNORDERED_RECEIPTS_FLAG,
319 I.ALLOW_EXPRESS_DELIVERY_FLAG,
320 I.DAYS_EARLY_RECEIPT_ALLOWED,
321 I.DAYS_LATE_RECEIPT_ALLOWED,
322 I.RECEIPT_DAYS_EXCEPTION_CODE,
323 I.RECEIVING_ROUTING_ID,
324 I.INVOICE_CLOSE_TOLERANCE,
325 I.RECEIVE_CLOSE_TOLERANCE,
326 I.AUTO_LOT_ALPHA_PREFIX,
327 I.START_AUTO_LOT_NUMBER,
328 I.LOT_CONTROL_CODE,
329 I.SHELF_LIFE_CODE,
330 I.SHELF_LIFE_DAYS,
331 I.SERIAL_NUMBER_CONTROL_CODE,
332 I.START_AUTO_SERIAL_NUMBER,
333 I.AUTO_SERIAL_ALPHA_PREFIX,
334 I.SOURCE_TYPE,
335 --* Commented for Bug #3764605 : Below attributes are org controlled.
336 --I.SOURCE_ORGANIZATION_ID,
337 --I.SOURCE_SUBINVENTORY,
338 --* End of Bug #3764605
339 I.RESTRICT_SUBINVENTORIES_CODE,
340 I.UNIT_WEIGHT,
341 I.WEIGHT_UOM_CODE,
342 I.VOLUME_UOM_CODE,
343 I.UNIT_VOLUME,
344 I.RESTRICT_LOCATORS_CODE,
345 I.LOCATION_CONTROL_CODE,
346 I.SHRINKAGE_RATE,
347 I.ACCEPTABLE_EARLY_DAYS,
348 I.PLANNING_TIME_FENCE_CODE,
349 I.DEMAND_TIME_FENCE_CODE,
350 I.LEAD_TIME_LOT_SIZE,
351 I.STD_LOT_SIZE,
352 I.CUM_MANUFACTURING_LEAD_TIME,
353 I.OVERRUN_PERCENTAGE,
354 I.MRP_CALCULATE_ATP_FLAG,
355 I.ACCEPTABLE_RATE_INCREASE,
356 I.ACCEPTABLE_RATE_DECREASE,
357 I.CUMULATIVE_TOTAL_LEAD_TIME,
358 I.PLANNING_TIME_FENCE_DAYS,
359 I.DEMAND_TIME_FENCE_DAYS,
360 I.END_ASSEMBLY_PEGGING_FLAG,
361 I.REPETITIVE_PLANNING_FLAG,
362 I.BOM_ITEM_TYPE,
363 I.PICK_COMPONENTS_FLAG,
364 I.REPLENISH_TO_ORDER_FLAG,
365 I.BASE_ITEM_ID,
366 I.ATP_COMPONENTS_FLAG,
367 I.ATP_FLAG,
368 I.FIXED_LEAD_TIME,
369 I.VARIABLE_LEAD_TIME,
370 I.WIP_SUPPLY_LOCATOR_ID,
371 I.WIP_SUPPLY_TYPE,
372 I.WIP_SUPPLY_SUBINVENTORY,
373 I.PRIMARY_UOM_CODE,
374 I.PRIMARY_UNIT_OF_MEASURE,
375 I.ALLOWED_UNITS_LOOKUP_CODE,
376 I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
377 I.INVENTORY_ITEM_STATUS_CODE,
378 I.INVENTORY_PLANNING_CODE,
379 I.PLANNER_CODE,
380 I.PLANNING_MAKE_BUY_CODE,
381 I.FIXED_LOT_MULTIPLIER,
382 I.ROUNDING_CONTROL_TYPE,
383 I.CARRYING_COST,
384 I.POSTPROCESSING_LEAD_TIME,
385 I.PREPROCESSING_LEAD_TIME,
389 I.MRP_SAFETY_STOCK_CODE,
386 I.FULL_LEAD_TIME,
387 I.ORDER_COST,
388 I.MRP_SAFETY_STOCK_PERCENT,
390 I.MIN_MINMAX_QUANTITY,
391 I.MAX_MINMAX_QUANTITY,
392 I.MINIMUM_ORDER_QUANTITY,
393 I.FIXED_ORDER_QUANTITY,
394 I.FIXED_DAYS_SUPPLY,
395 I.MAXIMUM_ORDER_QUANTITY,
396 I.ATP_RULE_ID,
397 I.PICKING_RULE_ID,
398 I.RESERVABLE_TYPE,
399 I.POSITIVE_MEASUREMENT_ERROR,
400 I.NEGATIVE_MEASUREMENT_ERROR,
401 I.ENGINEERING_ECN_CODE,
402 I.ENGINEERING_ITEM_ID,
403 I.ENGINEERING_DATE,
404 I.SERVICE_STARTING_DELAY,
405 I.SERVICEABLE_COMPONENT_FLAG,
406 I.SERVICEABLE_PRODUCT_FLAG,
407 I.BASE_WARRANTY_SERVICE_ID,
408 I.PAYMENT_TERMS_ID,
409 I.PREVENTIVE_MAINTENANCE_FLAG,
410 I.PRIMARY_SPECIALIST_ID,
411 I.SECONDARY_SPECIALIST_ID,
412 I.SERVICEABLE_ITEM_CLASS_ID,
413 I.TIME_BILLABLE_FLAG,
414 I.MATERIAL_BILLABLE_FLAG,
415 I.EXPENSE_BILLABLE_FLAG,
416 I.PRORATE_SERVICE_FLAG,
417 I.COVERAGE_SCHEDULE_ID,
418 I.SERVICE_DURATION_PERIOD_CODE,
419 I.SERVICE_DURATION,
420 I.WARRANTY_VENDOR_ID,
421 I.MAX_WARRANTY_AMOUNT,
422 I.RESPONSE_TIME_PERIOD_CODE,
423 I.RESPONSE_TIME_VALUE,
424 I.NEW_REVISION_CODE,
425 I.INVOICEABLE_ITEM_FLAG,
426 I.TAX_CODE,
427 -- I.INVOICE_ENABLED_FLAG,
428 I.MUST_USE_APPROVED_VENDOR_FLAG,
429 I.REQUEST_ID,
430 I.PROGRAM_APPLICATION_ID,
431 I.PROGRAM_ID,
432 I.PROGRAM_UPDATE_DATE,
433 I.OUTSIDE_OPERATION_FLAG,
434 I.OUTSIDE_OPERATION_UOM_TYPE,
435 I.SAFETY_STOCK_BUCKET_DAYS,
436 I.AUTO_REDUCE_MPS,
437 I.COSTING_ENABLED_FLAG,
438 I.CYCLE_COUNT_ENABLED_FLAG,
439 I.AUTO_CREATED_CONFIG_FLAG,
440 I.ITEM_TYPE,
441 I.MODEL_CONFIG_CLAUSE_NAME,
442 I.SHIP_MODEL_COMPLETE_FLAG,
443 I.MRP_PLANNING_CODE,
444 I.ATO_FORECAST_CONTROL,
445 I.RETURN_INSPECTION_REQUIREMENT,
446 I.RELEASE_TIME_FENCE_CODE,
447 I.RELEASE_TIME_FENCE_DAYS,
448 I.CONTAINER_ITEM_FLAG,
449 I.CONTAINER_TYPE_CODE,
450 I.INTERNAL_VOLUME,
451 I.MAXIMUM_LOAD_WEIGHT,
452 I.MINIMUM_FILL_PERCENT,
453 I.VEHICLE_ITEM_FLAG,
454 I.CHECK_SHORTAGES_FLAG, /*CK 18NOV98 Added new attribute*/
455 I.EFFECTIVITY_CONTROL,
456 I.OVERCOMPLETION_TOLERANCE_TYPE,
457 I.OVERCOMPLETION_TOLERANCE_VALUE,
458 I.OVER_SHIPMENT_TOLERANCE,
459 I.UNDER_SHIPMENT_TOLERANCE,
460 I.OVER_RETURN_TOLERANCE,
461 I.UNDER_RETURN_TOLERANCE,
462 I.EQUIPMENT_TYPE,
463 I.RECOVERED_PART_DISP_CODE,
464 I.DEFECT_TRACKING_ON_FLAG,
465 I.EVENT_FLAG,
466 I.ELECTRONIC_FLAG,
467 I.DOWNLOADABLE_FLAG
468 , I.VOL_DISCOUNT_EXEMPT_FLAG
469 , I.COUPON_EXEMPT_FLAG
470 , I.COMMS_NL_TRACKABLE_FLAG
471 , I.ASSET_CREATION_CODE
472 , I.COMMS_ACTIVATION_REQD_FLAG
473 , I.ORDERABLE_ON_WEB_FLAG
474 , I.BACK_ORDERABLE_FLAG
475 , I.WEB_STATUS
476 , I.INDIVISIBLE_FLAG
477 , I.DIMENSION_UOM_CODE
478 , I.UNIT_LENGTH
479 , I.UNIT_WIDTH
480 , I.UNIT_HEIGHT
481 , I.BULK_PICKED_FLAG
482 , I.LOT_STATUS_ENABLED
483 , I.DEFAULT_LOT_STATUS_ID
484 , I.SERIAL_STATUS_ENABLED
485 , I.DEFAULT_SERIAL_STATUS_ID
486 , I.INVENTORY_CARRY_PENALTY
487 , I.OPERATION_SLACK_PENALTY
488 , I.FINANCING_ALLOWED_FLAG
489 , I.EAM_ITEM_TYPE
490 , I.EAM_ACTIVITY_TYPE_CODE
491 , I.EAM_ACTIVITY_CAUSE_CODE
492 , I.EAM_ACT_NOTIFICATION_FLAG
493 , I.EAM_ACT_SHUTDOWN_STATUS
494 , I.DUAL_UOM_CONTROL
495 , I.SECONDARY_UOM_CODE
496 , I.DUAL_UOM_DEVIATION_HIGH
497 , I.DUAL_UOM_DEVIATION_LOW
498 --, I.SERVICE_ITEM_FLAG
499 --, I.VENDOR_WARRANTY_FLAG
500 --, I.USAGE_ITEM_FLAG
501 , I.CONTRACT_ITEM_TYPE_CODE
502 , I.SUBSCRIPTION_DEPEND_FLAG
503 --
504 , I.SERV_REQ_ENABLED_CODE
505 , I.SERV_BILLING_ENABLED_FLAG
506 , I.SERV_IMPORTANCE_LEVEL
507 , I.PLANNED_INV_POINT_FLAG
508 , I.DEFAULT_SO_SOURCE_TYPE
509 , I.CREATE_SUPPLY_FLAG
510 , I.SUBSTITUTION_WINDOW_CODE
511 , I.SUBSTITUTION_WINDOW_DAYS
512 --Moved the dependant Lot attributes together
513 --(For ease of fix for bug# 2760857)
514 , I.LOT_SPLIT_ENABLED
515 , I.LOT_MERGE_ENABLED
516 , I.LOT_TRANSLATE_ENABLED
517 --Added as part of 11.5.9
518 , I.LOT_SUBSTITUTION_ENABLED
519 , I.MINIMUM_LICENSE_QUANTITY
520 , I.EAM_ACTIVITY_SOURCE_CODE
521 , I.IB_ITEM_INSTANCE_CLASS
522 , I.CONFIG_MODEL_TYPE
526 --Added as part of 11.5.10
523 --2740503: Defaulting lifecycle-phase from master org.
524 , I.LIFECYCLE_ID
525 , I.CURRENT_PHASE_ID
527 , I.TRACKING_QUANTITY_IND
528 , I.ONT_PRICING_QTY_SOURCE
529 , I.SECONDARY_DEFAULT_IND
530 , I.CONFIG_ORGS
531 , I.CONFIG_MATCH
532 , I.VMI_MINIMUM_UNITS
533 , I.VMI_MINIMUM_DAYS
534 , I.VMI_MAXIMUM_UNITS
535 , I.VMI_MAXIMUM_DAYS
536 , I.VMI_FIXED_ORDER_QUANTITY
537 , I.SO_AUTHORIZATION_FLAG
538 , I.CONSIGNED_FLAG
539 , I.ASN_AUTOEXPIRE_FLAG
540 , I.VMI_FORECAST_TYPE
541 , I.FORECAST_HORIZON
542 , I.EXCLUDE_FROM_BUDGET_FLAG
543 , I.DAYS_TGT_INV_SUPPLY
544 , I.DAYS_TGT_INV_WINDOW
545 , I.DAYS_MAX_INV_SUPPLY
546 , I.DAYS_MAX_INV_WINDOW
547 , I.DRP_PLANNED_FLAG
548 , I.CRITICAL_COMPONENT_FLAG
549 , I.CONTINOUS_TRANSFER
550 , I.CONVERGENCE
551 , I.DIVERGENCE
552 /* Start Bug 3713912 */
553 , I.LOT_DIVISIBLE_FLAG
554 , I.GRADE_CONTROL_FLAG
555 , I.DEFAULT_GRADE
556 , I.CHILD_LOT_FLAG
557 , I.PARENT_CHILD_GENERATION_FLAG
558 , I.CHILD_LOT_PREFIX
559 , I.CHILD_LOT_STARTING_NUMBER
560 , I.CHILD_LOT_VALIDATION_FLAG
561 , I.COPY_LOT_ATTRIBUTE_FLAG
562 --5349389 , I.RECIPE_ENABLED_FLAG
563 , I.PROCESS_QUALITY_ENABLED_FLAG
564 --5349389 , I.PROCESS_EXECUTION_ENABLED_FLAG
565 , I.PROCESS_COSTING_ENABLED_FLAG
566 , I.PROCESS_SUPPLY_SUBINVENTORY
567 , I.PROCESS_SUPPLY_LOCATOR_ID
568 , I.PROCESS_YIELD_SUBINVENTORY
569 , I.PROCESS_YIELD_LOCATOR_ID
570 , I.HAZARDOUS_MATERIAL_FLAG
571 , I.CAS_NUMBER
572 , I.RETEST_INTERVAL
573 , I.EXPIRATION_ACTION_INTERVAL
574 , I.EXPIRATION_ACTION_CODE
575 , I.MATURITY_DAYS
576 , I.HOLD_DAYS
577 /* End Bug 3713912 */
578 --R12 Enhancement
579 , I.REPAIR_LEADTIME
580 , I.REPAIR_YIELD
581 , I.PREPOSITION_POINT
582 , I.REPAIR_PROGRAM
583 , I.CHARGE_PERIODICITY_CODE
584 , I.SUBCONTRACTING_COMPONENT
585 , I.OUTSOURCED_ASSEMBLY
586 /* FPC Attributes */
587 , I.GDSN_OUTBOUND_ENABLED_FLAG
588 , I.TRADE_ITEM_DESCRIPTOR
589 , I.STYLE_ITEM_FLAG
590 , I.STYLE_ITEM_ID
591 ) = (
592 SELECT
593 master_org_id, /* LAST_UPDATED_BY */
594 decode(i.created_by,-99,i.created_by,l_created_by), /*NP Comment here is where created_by is set
595 to 1 to reflect item exists in msi */
596 NVL(I.SUMMARY_FLAG,M.SUMMARY_FLAG),
597 NVL(I.ENABLED_FLAG,M.ENABLED_FLAG),
598 NVL(I.START_DATE_ACTIVE,M.START_DATE_ACTIVE),
599 NVL(I.END_DATE_ACTIVE,M.END_DATE_ACTIVE),
600 NVL(I.DESCRIPTION, M.DESCRIPTION),
601 NVL(I.LONG_DESCRIPTION, M.LONG_DESCRIPTION),
602 NVL(I.BUYER_ID,M.BUYER_ID),
603 NVL(I.ACCOUNTING_RULE_ID,M.ACCOUNTING_RULE_ID),
604 NVL(I.INVOICING_RULE_ID,M.INVOICING_RULE_ID),
605 NVL(I.SEGMENT1,M.SEGMENT1),
606 NVL(I.SEGMENT2,M.SEGMENT2),
607 NVL(I.SEGMENT3,M.SEGMENT3),
608 NVL(I.SEGMENT4,M.SEGMENT4),
609 NVL(I.SEGMENT5,M.SEGMENT5),
610 NVL(I.SEGMENT6,M.SEGMENT6),
611 NVL(I.SEGMENT7,M.SEGMENT7),
612 NVL(I.SEGMENT8,M.SEGMENT8),
613 NVL(I.SEGMENT9,M.SEGMENT9),
614 NVL(I.SEGMENT10,M.SEGMENT10),
615 NVL(I.SEGMENT11,M.SEGMENT11),
616 NVL(I.SEGMENT12,M.SEGMENT12),
617 NVL(I.SEGMENT13,M.SEGMENT13),
618 NVL(I.SEGMENT14,M.SEGMENT14),
619 NVL(I.SEGMENT15,M.SEGMENT15),
620 NVL(I.SEGMENT16,M.SEGMENT16),
621 NVL(I.SEGMENT17,M.SEGMENT17),
622 NVL(I.SEGMENT18,M.SEGMENT18),
623 NVL(I.SEGMENT19,M.SEGMENT19),
624 NVL(I.SEGMENT20,M.SEGMENT20),
625 NVL(I.ATTRIBUTE_CATEGORY,M.ATTRIBUTE_CATEGORY),
626 NVL(I.ATTRIBUTE1,M.ATTRIBUTE1),
627 NVL(I.ATTRIBUTE2,M.ATTRIBUTE2),
628 NVL(I.ATTRIBUTE3,M.ATTRIBUTE3),
629 NVL(I.ATTRIBUTE4,M.ATTRIBUTE4),
630 NVL(I.ATTRIBUTE5,M.ATTRIBUTE5),
631 NVL(I.ATTRIBUTE6,M.ATTRIBUTE6),
632 NVL(I.ATTRIBUTE7,M.ATTRIBUTE7),
633 NVL(I.ATTRIBUTE8,M.ATTRIBUTE8),
634 NVL(I.ATTRIBUTE9,M.ATTRIBUTE9),
635 NVL(I.ATTRIBUTE10,M.ATTRIBUTE10),
636 NVL(I.ATTRIBUTE11,M.ATTRIBUTE11),
637 NVL(I.ATTRIBUTE12,M.ATTRIBUTE12),
638 NVL(I.ATTRIBUTE13,M.ATTRIBUTE13),
639 NVL(I.ATTRIBUTE14,M.ATTRIBUTE14),
640 NVL(I.ATTRIBUTE15,M.ATTRIBUTE15),
641 /* End Bug 3713912 */
642 NVL(I.ATTRIBUTE16, M.ATTRIBUTE16) ,
643 NVL(I.ATTRIBUTE17, M.ATTRIBUTE17) ,
644 NVL(I.ATTRIBUTE18, M.ATTRIBUTE18) ,
645 NVL(I.ATTRIBUTE19, M.ATTRIBUTE19) ,
646 NVL(I.ATTRIBUTE20, M.ATTRIBUTE20) ,
647 NVL(I.ATTRIBUTE21, M.ATTRIBUTE21) ,
648 NVL(I.ATTRIBUTE22, M.ATTRIBUTE22) ,
649 NVL(I.ATTRIBUTE23, M.ATTRIBUTE23) ,
650 NVL(I.ATTRIBUTE24, M.ATTRIBUTE24) ,
651 NVL(I.ATTRIBUTE25, M.ATTRIBUTE25) ,
652 NVL(I.ATTRIBUTE26, M.ATTRIBUTE26) ,
653 NVL(I.ATTRIBUTE27, M.ATTRIBUTE27) ,
654 NVL(I.ATTRIBUTE28, M.ATTRIBUTE28) ,
655 NVL(I.ATTRIBUTE29, M.ATTRIBUTE29) ,
656 NVL(I.ATTRIBUTE30, M.ATTRIBUTE30) ,
657 /* End Bug 3713912 */
658 NVL(I.GLOBAL_ATTRIBUTE_CATEGORY,M.GLOBAL_ATTRIBUTE_CATEGORY),
659 NVL(I.GLOBAL_ATTRIBUTE1,M.GLOBAL_ATTRIBUTE1),
663 NVL(I.GLOBAL_ATTRIBUTE5,M.GLOBAL_ATTRIBUTE5),
660 NVL(I.GLOBAL_ATTRIBUTE2,M.GLOBAL_ATTRIBUTE2),
661 NVL(I.GLOBAL_ATTRIBUTE3,M.GLOBAL_ATTRIBUTE3),
662 NVL(I.GLOBAL_ATTRIBUTE4,M.GLOBAL_ATTRIBUTE4),
664 NVL(I.GLOBAL_ATTRIBUTE6,M.GLOBAL_ATTRIBUTE6),
665 NVL(I.GLOBAL_ATTRIBUTE7,M.GLOBAL_ATTRIBUTE7),
666 NVL(I.GLOBAL_ATTRIBUTE8,M.GLOBAL_ATTRIBUTE8),
667 NVL(I.GLOBAL_ATTRIBUTE9,M.GLOBAL_ATTRIBUTE9),
668 NVL(I.GLOBAL_ATTRIBUTE10,M.GLOBAL_ATTRIBUTE10),
669 NVL(I.GLOBAL_ATTRIBUTE11,M.GLOBAL_ATTRIBUTE11),
670 NVL(I.GLOBAL_ATTRIBUTE12,M.GLOBAL_ATTRIBUTE12),
671 NVL(I.GLOBAL_ATTRIBUTE13,M.GLOBAL_ATTRIBUTE13),
672 NVL(I.GLOBAL_ATTRIBUTE14,M.GLOBAL_ATTRIBUTE14),
673 NVL(I.GLOBAL_ATTRIBUTE15,M.GLOBAL_ATTRIBUTE15),
674 NVL(I.GLOBAL_ATTRIBUTE16,M.GLOBAL_ATTRIBUTE16),
675 NVL(I.GLOBAL_ATTRIBUTE17,M.GLOBAL_ATTRIBUTE17),
676 NVL(I.GLOBAL_ATTRIBUTE18,M.GLOBAL_ATTRIBUTE18),
677 NVL(I.GLOBAL_ATTRIBUTE19,M.GLOBAL_ATTRIBUTE19),
678 NVL(I.GLOBAL_ATTRIBUTE20,M.GLOBAL_ATTRIBUTE20),
679 NVL(I.PURCHASING_ITEM_FLAG,M.PURCHASING_ITEM_FLAG),
680 DECODE(I.INTERNAL_ORDER_FLAG, 'Y','Y', DECODE(M.INTERNAL_ORDER_FLAG, 'Y','Y',
681 nvl(I.SHIPPABLE_ITEM_FLAG,M.SHIPPABLE_ITEM_FLAG))),
682 NVL(I.CUSTOMER_ORDER_FLAG,M.CUSTOMER_ORDER_FLAG),
683 NVL(I.INTERNAL_ORDER_FLAG,M.INTERNAL_ORDER_FLAG),
684 NVL(I.INVENTORY_ITEM_FLAG,M.INVENTORY_ITEM_FLAG),
685 NVL(I.ENG_ITEM_FLAG,M.ENG_ITEM_FLAG),
686 NVL(I.INVENTORY_ASSET_FLAG,M.INVENTORY_ASSET_FLAG),
687 -- * For the bug 3888752 from its base bug --ANMURALI
688 -- NVL(I.PURCHASING_ENABLED_FLAG,M.PURCHASING_ENABLED_FLAG),
689 -- NVL(I.CUSTOMER_ORDER_ENABLED_FLAG,M.CUSTOMER_ORDER_ENABLED_FLAG),
690 -- NVL(I.INTERNAL_ORDER_ENABLED_FLAG,M.INTERNAL_ORDER_ENABLED_FLAG),
691 NVL(I.SO_TRANSACTIONS_FLAG,M.SO_TRANSACTIONS_FLAG),
692 -- NVL(I.MTL_TRANSACTIONS_ENABLED_FLAG,M.MTL_TRANSACTIONS_ENABLED_FLAG),
693 -- NVL(I.STOCK_ENABLED_FLAG,M.STOCK_ENABLED_FLAG),
694 -- NVL(I.BOM_ENABLED_FLAG,M.BOM_ENABLED_FLAG),
695 -- NVL(I.BUILD_IN_WIP_FLAG,M.BUILD_IN_WIP_FLAG),
696 NVL(I.REVISION_QTY_CONTROL_CODE,M.REVISION_QTY_CONTROL_CODE),
697 NVL(I.ITEM_CATALOG_GROUP_ID,M.ITEM_CATALOG_GROUP_ID),
698 NVL(I.CATALOG_STATUS_FLAG,M.CATALOG_STATUS_FLAG),
699 NVL(I.RETURNABLE_FLAG,M.RETURNABLE_FLAG),
700 NVL(I.DEFAULT_SHIPPING_ORG,M.DEFAULT_SHIPPING_ORG),
701 NVL(I.COLLATERAL_FLAG,M.COLLATERAL_FLAG),
702 NVL(I.TAXABLE_FLAG,M.TAXABLE_FLAG),
703 -- NVL(I.PURCHASING_TAX_CODE,M.PURCHASING_TAX_CODE),
704 NVL(I.PURCHASING_TAX_CODE,DECODE(NVL(I.TAXABLE_FLAG,M.TAXABLE_FLAG),NULL,NULL,'N',NULL,M.PURCHASING_TAX_CODE)),
705 NVL(I.QTY_RCV_EXCEPTION_CODE,M.QTY_RCV_EXCEPTION_CODE),
706 NVL(I.ALLOW_ITEM_DESC_UPDATE_FLAG,M.ALLOW_ITEM_DESC_UPDATE_FLAG),
707 NVL(I.INSPECTION_REQUIRED_FLAG,M.INSPECTION_REQUIRED_FLAG),
708 NVL(I.RECEIPT_REQUIRED_FLAG,M.RECEIPT_REQUIRED_FLAG),
709 NVL(I.MARKET_PRICE,M.MARKET_PRICE),
710 NVL(I.HAZARD_CLASS_ID,M.HAZARD_CLASS_ID),
711 NVL(I.RFQ_REQUIRED_FLAG,M.RFQ_REQUIRED_FLAG),
712 NVL(I.QTY_RCV_TOLERANCE,M.QTY_RCV_TOLERANCE),
713 NVL(I.LIST_PRICE_PER_UNIT,M.LIST_PRICE_PER_UNIT),
714 NVL(I.UN_NUMBER_ID,M.UN_NUMBER_ID),
715 NVL(I.PRICE_TOLERANCE_PERCENT,M.PRICE_TOLERANCE_PERCENT),
716 NVL(I.ASSET_CATEGORY_ID,M.ASSET_CATEGORY_ID),
717 NVL(I.ROUNDING_FACTOR,M.ROUNDING_FACTOR),
718 NVL(I.UNIT_OF_ISSUE,M.UNIT_OF_ISSUE),
719 NVL(I.ENFORCE_SHIP_TO_LOCATION_CODE,M.ENFORCE_SHIP_TO_LOCATION_CODE),
720 NVL(I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,M.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
721 NVL(I.ALLOW_UNORDERED_RECEIPTS_FLAG,M.ALLOW_UNORDERED_RECEIPTS_FLAG),
722 NVL(I.ALLOW_EXPRESS_DELIVERY_FLAG,M.ALLOW_EXPRESS_DELIVERY_FLAG),
723 NVL(I.DAYS_EARLY_RECEIPT_ALLOWED,M.DAYS_EARLY_RECEIPT_ALLOWED),
724 NVL(I.DAYS_LATE_RECEIPT_ALLOWED,M.DAYS_LATE_RECEIPT_ALLOWED),
725 NVL(I.RECEIPT_DAYS_EXCEPTION_CODE,M.RECEIPT_DAYS_EXCEPTION_CODE),
726 NVL(I.RECEIVING_ROUTING_ID,M.RECEIVING_ROUTING_ID),
727 NVL(I.INVOICE_CLOSE_TOLERANCE,M.INVOICE_CLOSE_TOLERANCE),
728 NVL(I.RECEIVE_CLOSE_TOLERANCE,M.RECEIVE_CLOSE_TOLERANCE),
729 NVL(I.AUTO_LOT_ALPHA_PREFIX,M.AUTO_LOT_ALPHA_PREFIX),
730 NVL(I.START_AUTO_LOT_NUMBER,M.START_AUTO_LOT_NUMBER),
731 NVL(I.LOT_CONTROL_CODE,M.LOT_CONTROL_CODE),
732 NVL(I.SHELF_LIFE_CODE,M.SHELF_LIFE_CODE),
733 NVL(I.SHELF_LIFE_DAYS,M.SHELF_LIFE_DAYS),
734 NVL(I.SERIAL_NUMBER_CONTROL_CODE,M.SERIAL_NUMBER_CONTROL_CODE),
735 NVL(I.START_AUTO_SERIAL_NUMBER,M.START_AUTO_SERIAL_NUMBER),
736 NVL(I.AUTO_SERIAL_ALPHA_PREFIX,M.AUTO_SERIAL_ALPHA_PREFIX),
737 NVL(I.SOURCE_TYPE,M.SOURCE_TYPE),
738 --* Commented for Bug #3764605 : Below attributes are org controlled.
739 --NVL(I.SOURCE_ORGANIZATION_ID,M.SOURCE_ORGANIZATION_ID),
740 --NVL(I.SOURCE_SUBINVENTORY,M.SOURCE_SUBINVENTORY),
741 --* End of Bug #3764605
742 NVL(I.RESTRICT_SUBINVENTORIES_CODE,M.RESTRICT_SUBINVENTORIES_CODE),
743 NVL(I.UNIT_WEIGHT,M.UNIT_WEIGHT),
744 NVL(I.WEIGHT_UOM_CODE,M.WEIGHT_UOM_CODE),
745 NVL(I.VOLUME_UOM_CODE,M.VOLUME_UOM_CODE),
746 NVL(I.UNIT_VOLUME,M.UNIT_VOLUME),
747 NVL(I.RESTRICT_LOCATORS_CODE,M.RESTRICT_LOCATORS_CODE),
748 NVL(I.LOCATION_CONTROL_CODE,M.LOCATION_CONTROL_CODE),
749 NVL(I.SHRINKAGE_RATE,M.SHRINKAGE_RATE),
750 NVL(I.ACCEPTABLE_EARLY_DAYS,M.ACCEPTABLE_EARLY_DAYS),
751 NVL(I.PLANNING_TIME_FENCE_CODE, M.PLANNING_TIME_FENCE_CODE),
752 NVL(I.DEMAND_TIME_FENCE_CODE, M.DEMAND_TIME_FENCE_CODE),
753 NVL(I.LEAD_TIME_LOT_SIZE,M.LEAD_TIME_LOT_SIZE),
754 NVL(I.STD_LOT_SIZE,M.STD_LOT_SIZE),
755 NVL(I.CUM_MANUFACTURING_LEAD_TIME,M.CUM_MANUFACTURING_LEAD_TIME),
756 NVL(I.OVERRUN_PERCENTAGE,M.OVERRUN_PERCENTAGE),
760 NVL(I.CUMULATIVE_TOTAL_LEAD_TIME,M.CUMULATIVE_TOTAL_LEAD_TIME),
757 NVL(I.MRP_CALCULATE_ATP_FLAG,M.MRP_CALCULATE_ATP_FLAG),
758 NVL(I.ACCEPTABLE_RATE_INCREASE,M.ACCEPTABLE_RATE_INCREASE),
759 NVL(I.ACCEPTABLE_RATE_DECREASE,M.ACCEPTABLE_RATE_DECREASE),
761 /*AN991207 #1081419: If child org time_fence_code is non-user defined,
762 do not copy master org time_fence_days to the child org */
763 NVL(I.PLANNING_TIME_FENCE_DAYS, decode(NVL(I.PLANNING_TIME_FENCE_CODE, M.PLANNING_TIME_FENCE_CODE),
764 4, M.PLANNING_TIME_FENCE_DAYS, NULL)),
765 NVL(I.DEMAND_TIME_FENCE_DAYS, decode(NVL(I.DEMAND_TIME_FENCE_CODE, M.DEMAND_TIME_FENCE_CODE),
766 4, M.DEMAND_TIME_FENCE_DAYS, NULL)),
767 NVL(I.END_ASSEMBLY_PEGGING_FLAG,M.END_ASSEMBLY_PEGGING_FLAG),
768 NVL(I.REPETITIVE_PLANNING_FLAG,M.REPETITIVE_PLANNING_FLAG),
769 NVL(I.BOM_ITEM_TYPE,M.BOM_ITEM_TYPE),
770 NVL(I.PICK_COMPONENTS_FLAG,M.PICK_COMPONENTS_FLAG),
771 NVL(I.REPLENISH_TO_ORDER_FLAG,M.REPLENISH_TO_ORDER_FLAG),
772 NVL(I.BASE_ITEM_ID,M.BASE_ITEM_ID),
773 NVL(I.ATP_COMPONENTS_FLAG,M.ATP_COMPONENTS_FLAG),
774 NVL(I.ATP_FLAG,M.ATP_FLAG),
775 NVL(I.FIXED_LEAD_TIME,M.FIXED_LEAD_TIME),
776 NVL(I.VARIABLE_LEAD_TIME,M.VARIABLE_LEAD_TIME),
777 I.WIP_SUPPLY_LOCATOR_ID, /*NP 26AUG94 this should not default from master*/
778 NVL(I.WIP_SUPPLY_TYPE,M.WIP_SUPPLY_TYPE),
779 I.WIP_SUPPLY_SUBINVENTORY, /*NP 26AUG94 this should not default from master*/
780 -- Primary Unit of Measure is now maintained via the PRIMARY_UOM_CODE column.
781 -- Bug: 4071090 NVL(I.PRIMARY_UOM_CODE,M.PRIMARY_UOM_CODE),
782 NVL(I.PRIMARY_UOM_CODE, M.PRIMARY_UOM_CODE),
783 --No defaulting from Master, as PRIMARY_UOM_CODE takes care of it.
784 NVL(I.PRIMARY_UNIT_OF_MEASURE,M.PRIMARY_UNIT_OF_MEASURE), /* MSIB only stores untranslated values for PUOM 5438341*/
785 NVL(I.ALLOWED_UNITS_LOOKUP_CODE,M.ALLOWED_UNITS_LOOKUP_CODE),
786 NVL(I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,M.DEFAULT_INCLUDE_IN_ROLLUP_FLAG),
787 NVL(I.INVENTORY_ITEM_STATUS_CODE,M.INVENTORY_ITEM_STATUS_CODE),
788 NVL(I.INVENTORY_PLANNING_CODE,M.INVENTORY_PLANNING_CODE),
789 I.PLANNER_CODE, /*NP 26AUG94 no defaulting from master*/
790 NVL(I.PLANNING_MAKE_BUY_CODE,M.PLANNING_MAKE_BUY_CODE),
791 NVL(I.FIXED_LOT_MULTIPLIER,M.FIXED_LOT_MULTIPLIER),
792 NVL(I.ROUNDING_CONTROL_TYPE,M.ROUNDING_CONTROL_TYPE),
793 NVL(I.CARRYING_COST,M.CARRYING_COST),
794 NVL(I.POSTPROCESSING_LEAD_TIME,M.POSTPROCESSING_LEAD_TIME),
795 NVL(I.PREPROCESSING_LEAD_TIME,M.PREPROCESSING_LEAD_TIME),
796 NVL(I.FULL_LEAD_TIME,M.FULL_LEAD_TIME),
797 NVL(I.ORDER_COST,M.ORDER_COST),
798 NVL(I.MRP_SAFETY_STOCK_PERCENT,M.MRP_SAFETY_STOCK_PERCENT),
799 NVL(I.MRP_SAFETY_STOCK_CODE,M.MRP_SAFETY_STOCK_CODE),
800 NVL(I.MIN_MINMAX_QUANTITY,M.MIN_MINMAX_QUANTITY),
801 NVL(I.MAX_MINMAX_QUANTITY,M.MAX_MINMAX_QUANTITY),
802 NVL(I.MINIMUM_ORDER_QUANTITY,M.MINIMUM_ORDER_QUANTITY),
803 NVL(I.FIXED_ORDER_QUANTITY,M.FIXED_ORDER_QUANTITY),
804 NVL(I.FIXED_DAYS_SUPPLY,M.FIXED_DAYS_SUPPLY),
805 NVL(I.MAXIMUM_ORDER_QUANTITY,M.MAXIMUM_ORDER_QUANTITY),
806 NVL(I.ATP_RULE_ID,M.ATP_RULE_ID),
807 NVL(I.PICKING_RULE_ID,M.PICKING_RULE_ID),
808 NVL(I.RESERVABLE_TYPE,M.RESERVABLE_TYPE),
809 NVL(I.POSITIVE_MEASUREMENT_ERROR,M.POSITIVE_MEASUREMENT_ERROR),
810 NVL(I.NEGATIVE_MEASUREMENT_ERROR,M.NEGATIVE_MEASUREMENT_ERROR),
811 NVL(I.ENGINEERING_ECN_CODE,M.ENGINEERING_ECN_CODE),
812 NVL(I.ENGINEERING_ITEM_ID,M.ENGINEERING_ITEM_ID),
813 NVL(I.ENGINEERING_DATE,M.ENGINEERING_DATE),
814 NVL(I.SERVICE_STARTING_DELAY,M.SERVICE_STARTING_DELAY),
815 NVL(I.SERVICEABLE_COMPONENT_FLAG,M.SERVICEABLE_COMPONENT_FLAG),
816 NVL(I.SERVICEABLE_PRODUCT_FLAG,M.SERVICEABLE_PRODUCT_FLAG),
817 NVL(I.BASE_WARRANTY_SERVICE_ID,M.BASE_WARRANTY_SERVICE_ID),
818 NVL(I.PAYMENT_TERMS_ID,M.PAYMENT_TERMS_ID),
819 NVL(I.PREVENTIVE_MAINTENANCE_FLAG,M.PREVENTIVE_MAINTENANCE_FLAG),
820 NVL(I.PRIMARY_SPECIALIST_ID,M.PRIMARY_SPECIALIST_ID),
821 NVL(I.SECONDARY_SPECIALIST_ID,M.SECONDARY_SPECIALIST_ID),
822 NVL(I.SERVICEABLE_ITEM_CLASS_ID,M.SERVICEABLE_ITEM_CLASS_ID),
823 NVL(I.TIME_BILLABLE_FLAG,M.TIME_BILLABLE_FLAG),
824 -- NVL(I.MATERIAL_BILLABLE_FLAG,M.MATERIAL_BILLABLE_FLAG),
825 NVL(I.MATERIAL_BILLABLE_FLAG, DECODE(
826 NVL(I.SERV_BILLING_ENABLED_FLAG,M.SERV_BILLING_ENABLED_FLAG)
827 ,NULL,NULL,
828 'N',NULL,
829 M.MATERIAL_BILLABLE_FLAG)), -- Bug 5361618
830 NVL(I.EXPENSE_BILLABLE_FLAG,M.EXPENSE_BILLABLE_FLAG),
831 NVL(I.PRORATE_SERVICE_FLAG,M.PRORATE_SERVICE_FLAG),
832 NVL(I.COVERAGE_SCHEDULE_ID,M.COVERAGE_SCHEDULE_ID),
833 NVL(I.SERVICE_DURATION_PERIOD_CODE,M.SERVICE_DURATION_PERIOD_CODE),
834 NVL(I.SERVICE_DURATION,M.SERVICE_DURATION),
835 NVL(I.WARRANTY_VENDOR_ID,M.WARRANTY_VENDOR_ID),
836 NVL(I.MAX_WARRANTY_AMOUNT,M.MAX_WARRANTY_AMOUNT),
837 NVL(I.RESPONSE_TIME_PERIOD_CODE,M.RESPONSE_TIME_PERIOD_CODE),
838 NVL(I.RESPONSE_TIME_VALUE,M.RESPONSE_TIME_VALUE),
839 NVL(I.NEW_REVISION_CODE,M.NEW_REVISION_CODE),
840 NVL(I.INVOICEABLE_ITEM_FLAG,M.INVOICEABLE_ITEM_FLAG),
841 NVL(I.TAX_CODE,M.TAX_CODE),
842 --NVL(I.INVOICE_ENABLED_FLAG,M.INVOICE_ENABLED_FLAG),
843 NVL(I.MUST_USE_APPROVED_VENDOR_FLAG,M.MUST_USE_APPROVED_VENDOR_FLAG),
844 NVL(I.REQUEST_ID,M.REQUEST_ID),
845 NVL(I.PROGRAM_APPLICATION_ID,M.PROGRAM_APPLICATION_ID),
846 NVL(I.PROGRAM_ID,M.PROGRAM_ID),
847 NVL(I.PROGRAM_UPDATE_DATE,M.PROGRAM_UPDATE_DATE),
848 NVL(I.OUTSIDE_OPERATION_FLAG,M.OUTSIDE_OPERATION_FLAG),
849 NVL(I.OUTSIDE_OPERATION_UOM_TYPE,M.OUTSIDE_OPERATION_UOM_TYPE),
850 NVL(I.SAFETY_STOCK_BUCKET_DAYS,M.SAFETY_STOCK_BUCKET_DAYS),
854 NVL(I.AUTO_CREATED_CONFIG_FLAG,M.AUTO_CREATED_CONFIG_FLAG),
851 NVL(I.AUTO_REDUCE_MPS,M.AUTO_REDUCE_MPS),
852 NVL(I.COSTING_ENABLED_FLAG,M.COSTING_ENABLED_FLAG),
853 NVL(I.CYCLE_COUNT_ENABLED_FLAG,M.CYCLE_COUNT_ENABLED_FLAG),
855 NVL(I.ITEM_TYPE,M.ITEM_TYPE),
856 NVL(I.MODEL_CONFIG_CLAUSE_NAME,M.MODEL_CONFIG_CLAUSE_NAME),
857 NVL(I.SHIP_MODEL_COMPLETE_FLAG,M.SHIP_MODEL_COMPLETE_FLAG),
858 NVL(I.MRP_PLANNING_CODE,M.MRP_PLANNING_CODE),
859 NVL(I.ATO_FORECAST_CONTROL,M.ATO_FORECAST_CONTROL),
860 NVL(I.RETURN_INSPECTION_REQUIREMENT,M.RETURN_INSPECTION_REQUIREMENT),
861 NVL(I.RELEASE_TIME_FENCE_CODE, M.RELEASE_TIME_FENCE_CODE),
862 /*AN991207 #1081419: If child org time_fence_code is non-user defined,
863 do not copy master org time_fence_days to the child org */
864 NVL(I.RELEASE_TIME_FENCE_DAYS, decode(NVL(I.RELEASE_TIME_FENCE_CODE, M.RELEASE_TIME_FENCE_CODE),
865 4, M.RELEASE_TIME_FENCE_DAYS, NULL)),
866 NVL(I.CONTAINER_ITEM_FLAG, M.CONTAINER_ITEM_FLAG),
867 NVL(I.CONTAINER_TYPE_CODE, M.CONTAINER_TYPE_CODE),
868 NVL(I.INTERNAL_VOLUME, M.INTERNAL_VOLUME),
869 NVL(I.MAXIMUM_LOAD_WEIGHT, M.MAXIMUM_LOAD_WEIGHT),
870 NVL(I.MINIMUM_FILL_PERCENT, M.MINIMUM_FILL_PERCENT),
871 NVL(I.VEHICLE_ITEM_FLAG, M.VEHICLE_ITEM_FLAG),
872 NVL(I.CHECK_SHORTAGES_FLAG, M.CHECK_SHORTAGES_FLAG)
873 , NVL( I.EFFECTIVITY_CONTROL, M.EFFECTIVITY_CONTROL )
874 , NVL( I.OVERCOMPLETION_TOLERANCE_TYPE, M.OVERCOMPLETION_TOLERANCE_TYPE )
875 , NVL( I.OVERCOMPLETION_TOLERANCE_VALUE, M.OVERCOMPLETION_TOLERANCE_VALUE )
876 , NVL( I.OVER_SHIPMENT_TOLERANCE, M.OVER_SHIPMENT_TOLERANCE )
877 , NVL( I.UNDER_SHIPMENT_TOLERANCE, M.UNDER_SHIPMENT_TOLERANCE )
878 , NVL( I.OVER_RETURN_TOLERANCE, M.OVER_RETURN_TOLERANCE )
879 , NVL( I.UNDER_RETURN_TOLERANCE, M.UNDER_RETURN_TOLERANCE )
880 , NVL( I.EQUIPMENT_TYPE, M.EQUIPMENT_TYPE )
881 , NVL( I.RECOVERED_PART_DISP_CODE, M.RECOVERED_PART_DISP_CODE )
882 , NVL( I.DEFECT_TRACKING_ON_FLAG, M.DEFECT_TRACKING_ON_FLAG )
883 , NVL( I.EVENT_FLAG, M.EVENT_FLAG )
884 , NVL( I.ELECTRONIC_FLAG, M.ELECTRONIC_FLAG )
885 , NVL( I.DOWNLOADABLE_FLAG, M.DOWNLOADABLE_FLAG )
886 , NVL( I.VOL_DISCOUNT_EXEMPT_FLAG, M.VOL_DISCOUNT_EXEMPT_FLAG )
887 , NVL( I.COUPON_EXEMPT_FLAG, M.COUPON_EXEMPT_FLAG )
888 , NVL( I.COMMS_NL_TRACKABLE_FLAG, M.COMMS_NL_TRACKABLE_FLAG )
889 , NVL( I.ASSET_CREATION_CODE, M.ASSET_CREATION_CODE )
890 , NVL( I.COMMS_ACTIVATION_REQD_FLAG, M.COMMS_ACTIVATION_REQD_FLAG )
891 , NVL( I.ORDERABLE_ON_WEB_FLAG, M.ORDERABLE_ON_WEB_FLAG )
892 , NVL( I.BACK_ORDERABLE_FLAG, M.BACK_ORDERABLE_FLAG )
893 , NVL( I.WEB_STATUS, M.WEB_STATUS )
894 , NVL( I.INDIVISIBLE_FLAG, M.INDIVISIBLE_FLAG )
895 , NVL( I.DIMENSION_UOM_CODE, M.DIMENSION_UOM_CODE )
896 , NVL( I.UNIT_LENGTH, M.UNIT_LENGTH )
897 , NVL( I.UNIT_WIDTH, M.UNIT_WIDTH )
898 , NVL( I.UNIT_HEIGHT, M.UNIT_HEIGHT )
899 , NVL( I.BULK_PICKED_FLAG, M.BULK_PICKED_FLAG )
900 , NVL( I.LOT_STATUS_ENABLED, M.LOT_STATUS_ENABLED )
901 , NVL( I.DEFAULT_LOT_STATUS_ID, M.DEFAULT_LOT_STATUS_ID )
902 , NVL( I.SERIAL_STATUS_ENABLED, M.SERIAL_STATUS_ENABLED )
903 /*Bug: 5739429 Default_serial_status_id should be defaulted based on serial_status_enabled flag.
904 If serial_status_enabled flag is 'N' then default_serial_status_id should be null.
905 , NVL( I.DEFAULT_SERIAL_STATUS_ID, M.DEFAULT_SERIAL_STATUS_ID )
906 */
907 , NVL( I.DEFAULT_SERIAL_STATUS_ID,DECODE(NVL(I.SERIAL_STATUS_ENABLED,M.SERIAL_STATUS_ENABLED),NULL,NULL,'N',NULL,M.DEFAULT_SERIAL_STATUS_ID))
908 , NVL( I.INVENTORY_CARRY_PENALTY, M.INVENTORY_CARRY_PENALTY )
909 , NVL( I.OPERATION_SLACK_PENALTY, M.OPERATION_SLACK_PENALTY )
910 , NVL( I.FINANCING_ALLOWED_FLAG, M.FINANCING_ALLOWED_FLAG )
911 , NVL( I.EAM_ITEM_TYPE , M.EAM_ITEM_TYPE )
912 , NVL( I.EAM_ACTIVITY_TYPE_CODE , M.EAM_ACTIVITY_TYPE_CODE )
913 , NVL( I.EAM_ACTIVITY_CAUSE_CODE , M.EAM_ACTIVITY_CAUSE_CODE )
914 , NVL( I.EAM_ACT_NOTIFICATION_FLAG , M.EAM_ACT_NOTIFICATION_FLAG )
915 , NVL( I.EAM_ACT_SHUTDOWN_STATUS , M.EAM_ACT_SHUTDOWN_STATUS )
916 , NVL( I.DUAL_UOM_CONTROL , M.DUAL_UOM_CONTROL )
917 , NVL( I.SECONDARY_UOM_CODE , M.SECONDARY_UOM_CODE )
918 , NVL( I.DUAL_UOM_DEVIATION_HIGH , M.DUAL_UOM_DEVIATION_HIGH )
919 , NVL( I.DUAL_UOM_DEVIATION_LOW , M.DUAL_UOM_DEVIATION_LOW )
920 --, NVL( I.SERVICE_ITEM_FLAG , M.SERVICE_ITEM_FLAG )
921 --, NVL( I.VENDOR_WARRANTY_FLAG , M.VENDOR_WARRANTY_FLAG )
922 --, NVL( I.USAGE_ITEM_FLAG , M.USAGE_ITEM_FLAG )
923 , NVL( I.CONTRACT_ITEM_TYPE_CODE , M.CONTRACT_ITEM_TYPE_CODE )
924 , NVL( I.SUBSCRIPTION_DEPEND_FLAG , M.SUBSCRIPTION_DEPEND_FLAG )
925 --
926 , NVL( I.SERV_REQ_ENABLED_CODE , M.SERV_REQ_ENABLED_CODE )
927 , NVL( I.SERV_BILLING_ENABLED_FLAG , M.SERV_BILLING_ENABLED_FLAG )
928 , NVL( I.SERV_IMPORTANCE_LEVEL , M.SERV_IMPORTANCE_LEVEL )
929 , NVL( I.PLANNED_INV_POINT_FLAG , M.PLANNED_INV_POINT_FLAG )
930 , NVL( I.DEFAULT_SO_SOURCE_TYPE , M.DEFAULT_SO_SOURCE_TYPE )
931 , NVL( I.CREATE_SUPPLY_FLAG , M.CREATE_SUPPLY_FLAG )
932 , NVL( I.SUBSTITUTION_WINDOW_CODE , M.SUBSTITUTION_WINDOW_CODE )
933 -- If child org window_code is non-user defined,
934 -- do not copy master org window_days to the child org.
935 , NVL( I.SUBSTITUTION_WINDOW_DAYS,
936 DECODE( NVL(I.SUBSTITUTION_WINDOW_CODE, M.SUBSTITUTION_WINDOW_CODE),
937 4, M.SUBSTITUTION_WINDOW_DAYS,
938 NULL ) )
939 --Moved the dependant Lot attributes together
940 --(For ease of fix for bug# 2760857)
941 , NVL( I.LOT_SPLIT_ENABLED , M.LOT_SPLIT_ENABLED )
945 , NVL( I.LOT_SUBSTITUTION_ENABLED , M.LOT_SUBSTITUTION_ENABLED )
942 , NVL( I.LOT_MERGE_ENABLED , M.LOT_MERGE_ENABLED )
943 , NVL( I.LOT_TRANSLATE_ENABLED , M.LOT_TRANSLATE_ENABLED )
944 --Added as part of 11.5.9 ENH
946 , NVL( I.MINIMUM_LICENSE_QUANTITY , M.MINIMUM_LICENSE_QUANTITY )
947 , NVL( I.EAM_ACTIVITY_SOURCE_CODE , M.EAM_ACTIVITY_SOURCE_CODE )
948 , NVL( I.IB_ITEM_INSTANCE_CLASS , M.IB_ITEM_INSTANCE_CLASS )
949 , NVL( I.CONFIG_MODEL_TYPE , M.CONFIG_MODEL_TYPE )
950 --2740503: Defaulting lifecycle-phase from master org.
951 , NVL( I.LIFECYCLE_ID , M.LIFECYCLE_ID )
952 , NVL( I.CURRENT_PHASE_ID , M.CURRENT_PHASE_ID )
953 --Added as part of 11.5.10 ENH
954 , NVL( I.TRACKING_QUANTITY_IND , M.TRACKING_QUANTITY_IND )
955 , NVL( I.ONT_PRICING_QTY_SOURCE , M.ONT_PRICING_QTY_SOURCE )
956 , NVL( I.SECONDARY_DEFAULT_IND , M.SECONDARY_DEFAULT_IND )
957 , NVL( I.CONFIG_ORGS , M.CONFIG_ORGS )
958 , NVL( I.CONFIG_MATCH , M.CONFIG_MATCH )
959 , NVL(I.VMI_MINIMUM_UNITS , M.VMI_MINIMUM_UNITS )
960 , NVL(I.VMI_MINIMUM_DAYS , M.VMI_MINIMUM_DAYS )
961 , NVL(I.VMI_MAXIMUM_UNITS , M.VMI_MAXIMUM_UNITS )
962 , NVL(I.VMI_MAXIMUM_DAYS , M.VMI_MAXIMUM_DAYS )
963 , NVL(I.VMI_FIXED_ORDER_QUANTITY , M.VMI_FIXED_ORDER_QUANTITY )
964 , NVL(I.SO_AUTHORIZATION_FLAG , M.SO_AUTHORIZATION_FLAG )
965 , NVL(I.CONSIGNED_FLAG , M.CONSIGNED_FLAG )
966 , NVL(I.ASN_AUTOEXPIRE_FLAG , M.ASN_AUTOEXPIRE_FLAG )
967 , NVL(I.VMI_FORECAST_TYPE , M.VMI_FORECAST_TYPE )
968 , NVL(I.FORECAST_HORIZON , M.FORECAST_HORIZON )
969 , NVL(I.EXCLUDE_FROM_BUDGET_FLAG , M.EXCLUDE_FROM_BUDGET_FLAG )
970 , NVL(I.DAYS_TGT_INV_SUPPLY , M.DAYS_TGT_INV_SUPPLY )
971 , NVL(I.DAYS_TGT_INV_WINDOW , M.DAYS_TGT_INV_WINDOW )
972 , NVL(I.DAYS_MAX_INV_SUPPLY , M.DAYS_MAX_INV_SUPPLY )
973 , NVL(I.DAYS_MAX_INV_WINDOW , M.DAYS_MAX_INV_WINDOW )
974 , NVL(I.DRP_PLANNED_FLAG , M.DRP_PLANNED_FLAG )
975 , NVL(I.CRITICAL_COMPONENT_FLAG , M.CRITICAL_COMPONENT_FLAG )
976 , NVL(I.CONTINOUS_TRANSFER , M.CONTINOUS_TRANSFER )
977 , NVL(I.CONVERGENCE , M.CONVERGENCE )
978 , NVL(I.DIVERGENCE , M.DIVERGENCE )
979 /* Start Bug 3713912 */
980 , NVL(I.LOT_DIVISIBLE_FLAG , M.LOT_DIVISIBLE_FLAG )
981 , NVL(I.GRADE_CONTROL_FLAG , M.GRADE_CONTROL_FLAG )
982 , NVL(I.DEFAULT_GRADE , M.DEFAULT_GRADE )
983 , NVL(I.CHILD_LOT_FLAG , M.CHILD_LOT_FLAG )
984 , NVL(I.PARENT_CHILD_GENERATION_FLAG , M.PARENT_CHILD_GENERATION_FLAG )
985 , NVL(I.CHILD_LOT_PREFIX , M.CHILD_LOT_PREFIX )
986 , NVL(I.CHILD_LOT_STARTING_NUMBER , M.CHILD_LOT_STARTING_NUMBER )
987 , NVL(I.CHILD_LOT_VALIDATION_FLAG , M.CHILD_LOT_VALIDATION_FLAG )
988 , NVL(I.COPY_LOT_ATTRIBUTE_FLAG , M.COPY_LOT_ATTRIBUTE_FLAG )
989 --5349389, NVL(I.RECIPE_ENABLED_FLAG , M.RECIPE_ENABLED_FLAG )
990 , NVL(I.PROCESS_QUALITY_ENABLED_FLAG , M.PROCESS_QUALITY_ENABLED_FLAG )
991 --5349389, NVL(I.PROCESS_EXECUTION_ENABLED_FLAG , M.PROCESS_EXECUTION_ENABLED_FLAG )
992 , NVL(I.PROCESS_COSTING_ENABLED_FLAG , M.PROCESS_COSTING_ENABLED_FLAG )
993 , I.PROCESS_SUPPLY_SUBINVENTORY /* this should not default from master */
994 , I.PROCESS_SUPPLY_LOCATOR_ID /* this should not default from master */
995 , I.PROCESS_YIELD_SUBINVENTORY /* this should not default from master */
996 , I.PROCESS_YIELD_LOCATOR_ID /* this should not default from master */
997 , NVL(I.HAZARDOUS_MATERIAL_FLAG , M.HAZARDOUS_MATERIAL_FLAG )
998 , NVL(I.CAS_NUMBER , M.CAS_NUMBER )
999 , NVL(I.RETEST_INTERVAL , M.RETEST_INTERVAL )
1000 , NVL(I.EXPIRATION_ACTION_INTERVAL , M.EXPIRATION_ACTION_INTERVAL )
1001 , NVL(I.EXPIRATION_ACTION_CODE , M.EXPIRATION_ACTION_CODE )
1002 , NVL(I.MATURITY_DAYS , M.MATURITY_DAYS )
1003 , NVL(I.HOLD_DAYS , M.HOLD_DAYS )
1004 /* End Bug 3713912 */
1005 --R12 Enhancement
1006 , NVL(I.REPAIR_LEADTIME ,M.REPAIR_LEADTIME )
1007 , NVL(I.REPAIR_YIELD ,M.REPAIR_YIELD )
1008 , NVL(I.PREPOSITION_POINT ,M.PREPOSITION_POINT )
1009 , NVL(I.REPAIR_PROGRAM ,M.REPAIR_PROGRAM )
1010 , NVL(I.CHARGE_PERIODICITY_CODE ,M.CHARGE_PERIODICITY_CODE )
1011 , NVL(I.SUBCONTRACTING_COMPONENT ,M.SUBCONTRACTING_COMPONENT )
1012 , NVL(I.OUTSOURCED_ASSEMBLY ,M.OUTSOURCED_ASSEMBLY )
1013 /* R12 FPC Attributes */
1014 , NVL(I.GDSN_OUTBOUND_ENABLED_FLAG ,M.GDSN_OUTBOUND_ENABLED_FLAG )
1015 , NVL(I.TRADE_ITEM_DESCRIPTOR ,M.TRADE_ITEM_DESCRIPTOR )
1016 , NVL(I.STYLE_ITEM_FLAG ,M.STYLE_ITEM_FLAG )
1017 , NVL(I.STYLE_ITEM_ID ,M.STYLE_ITEM_ID )
1018 FROM
1019 mtl_system_items_interface M
1020 WHERE
1021 M.inventory_item_id = item_id AND
1022 M.organization_id = master_org_id AND
1023 M.set_process_id = xset_id AND
1024 M.process_flag in (1,60000+1)) /*masters are moved to 60000+ to before calling child create*/
1025 WHERE I.rowid = p_rowid;
1026
1027 return(0);
1028
1029 EXCEPTION
1030
1031 WHEN OTHERS THEN
1032 err_text := substr('INVPUTLI.assign_master_defaults ' || SQLERRM ,1,240 );
1033 return(SQLCODE);
1034
1035 END predefault_child_master;
1036
1037
1038
1042 (
1039 ------------------------------------------------------------------------
1040
1041 FUNCTION assign_master_defaults
1043 tran_id NUMBER,
1044 item_id NUMBER,
1045 org_id NUMBER,
1046 master_org_id NUMBER,
1047 status_default VARCHAR2,
1048 uom_default VARCHAR2,
1049 allow_item_desc_flag VARCHAR2,
1050 req_required_flag VARCHAR2,
1051 err_text out NOCOPY VARCHAR2,
1052 xset_id in NUMBER DEFAULT -999 ,
1053 p_rowid ROWID
1054 )
1055 RETURN INTEGER
1056 IS
1057 rtn_status NUMBER := 1;
1058 msicount NUMBER := 0;
1059 msiicount NUMBER := 0;
1060 user_id NUMBER := -1;
1061 login_id NUMBER := -1;
1062 prog_appid NUMBER := -1;
1063 prog_id NUMBER := -1;
1064 request_id NUMBER := -1;
1065 error_msg VARCHAR2(70);
1066 dumm_status NUMBER;
1067 l_created_by NUMBER;
1068 LOGGING_ERR EXCEPTION;
1069 l_Primary_Unit_of_Measure VARCHAR2(25);
1070 l_Primary_UOM_Code VARCHAR2(3);
1071 l_base_primary_UOM mtl_units_of_measure_tl.unit_of_measure%TYPE;
1072 BEGIN
1073
1074 INVPUTLI.info('INVPUTLI: inside assign_master_defaults');
1075
1076 -- Assign defaults if the interface table column value is NULL
1077
1078 l_created_by := 1 ;
1079
1080 SELECT primary_unit_of_measure, primary_uom_code
1081 INTO l_Primary_Unit_of_Measure, l_Primary_UOM_Code
1082 FROM MTL_SYSTEM_ITEMS_INTERFACE
1083 WHERE rowid = p_rowid;
1084
1085 IF (l_Primary_UOM_Code IS NULL AND l_Primary_Unit_of_Measure IS NOT NULL) THEN
1086 /*Bug: 4932464 Perf Issue replaced mtl_all_primary_uoms_vv with mtl_units_of_measure_tl
1087 SELECT uom_code INTO l_Primary_UOM_Code
1088 FROM mtl_all_primary_uoms_vv
1089 WHERE inventory_item_id = 0
1090 AND unit_of_measure_tl = l_Primary_Unit_of_Measure;*/
1091 BEGIN
1092 SELECT uom_code
1093 INTO l_Primary_UOM_Code
1094 FROM mtl_units_of_measure_tl
1095 WHERE language = USERENV('LANG')
1096 AND unit_of_measure = l_Primary_Unit_of_Measure;
1097 -- AND unit_of_measure_tl = l_Primary_Unit_of_Measure; -Bug5260388
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 -- Bug:5438341 PUOM in interface table is a translated value
1101 SELECT uom_code, unit_of_measure
1102 INTO l_Primary_UOM_Code, l_base_primary_UOM
1103 FROM mtl_units_of_measure_tl
1104 WHERE language = USERENV('LANG')
1105 AND unit_of_measure_tl = l_Primary_Unit_of_Measure;
1106
1107 END;
1108
1109 END IF;
1110
1111 UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
1112 SET
1113 ( I.LAST_UPDATED_BY,
1114 I.CREATED_BY,
1115 I.SUMMARY_FLAG,
1116 I.ENABLED_FLAG,
1117 I.START_DATE_ACTIVE,
1118 I.END_DATE_ACTIVE,
1119 I.DESCRIPTION,
1120 I.LONG_DESCRIPTION,
1121 I.BUYER_ID,
1122 I.ACCOUNTING_RULE_ID,
1123 I.INVOICING_RULE_ID,
1124 I.SEGMENT1,
1125 I.SEGMENT2,
1126 I.SEGMENT3,
1127 I.SEGMENT4,
1128 I.SEGMENT5,
1129 I.SEGMENT6,
1130 I.SEGMENT7,
1131 I.SEGMENT8,
1132 I.SEGMENT9,
1133 I.SEGMENT10,
1134 I.SEGMENT11,
1135 I.SEGMENT12,
1136 I.SEGMENT13,
1137 I.SEGMENT14,
1138 I.SEGMENT15,
1139 I.SEGMENT16,
1140 I.SEGMENT17,
1141 I.SEGMENT18,
1142 I.SEGMENT19,
1143 I.SEGMENT20,
1144 I.ATTRIBUTE_CATEGORY,
1145 I.ATTRIBUTE1,
1146 I.ATTRIBUTE2,
1147 I.ATTRIBUTE3,
1148 I.ATTRIBUTE4,
1149 I.ATTRIBUTE5,
1150 I.ATTRIBUTE6,
1151 I.ATTRIBUTE7,
1152 I.ATTRIBUTE8,
1153 I.ATTRIBUTE9,
1154 I.ATTRIBUTE10,
1155 I.ATTRIBUTE11,
1156 I.ATTRIBUTE12,
1157 I.ATTRIBUTE13,
1158 I.ATTRIBUTE14,
1159 I.ATTRIBUTE15,
1160 /* Start Bug 3713912 */
1161 I.ATTRIBUTE16,
1162 I.ATTRIBUTE17,
1163 I.ATTRIBUTE18,
1164 I.ATTRIBUTE19,
1165 I.ATTRIBUTE20,
1166 I.ATTRIBUTE21,
1167 I.ATTRIBUTE22,
1168 I.ATTRIBUTE23,
1169 I.ATTRIBUTE24,
1170 I.ATTRIBUTE25,
1171 I.ATTRIBUTE26,
1172 I.ATTRIBUTE27,
1173 I.ATTRIBUTE28,
1174 I.ATTRIBUTE29,
1175 I.ATTRIBUTE30,
1176 /* End Bug 3713912 */
1177 I.GLOBAL_ATTRIBUTE_CATEGORY,
1178 I.GLOBAL_ATTRIBUTE1,
1179 I.GLOBAL_ATTRIBUTE2,
1180 I.GLOBAL_ATTRIBUTE3,
1181 I.GLOBAL_ATTRIBUTE4,
1182 I.GLOBAL_ATTRIBUTE5,
1183 I.GLOBAL_ATTRIBUTE6,
1184 I.GLOBAL_ATTRIBUTE7,
1185 I.GLOBAL_ATTRIBUTE8,
1186 I.GLOBAL_ATTRIBUTE9,
1187 I.GLOBAL_ATTRIBUTE10,
1188 I.GLOBAL_ATTRIBUTE11,
1189 I.GLOBAL_ATTRIBUTE12,
1190 I.GLOBAL_ATTRIBUTE13,
1191 I.GLOBAL_ATTRIBUTE14,
1195 I.GLOBAL_ATTRIBUTE18,
1192 I.GLOBAL_ATTRIBUTE15,
1193 I.GLOBAL_ATTRIBUTE16,
1194 I.GLOBAL_ATTRIBUTE17,
1196 I.GLOBAL_ATTRIBUTE19,
1197 I.GLOBAL_ATTRIBUTE20,
1198 I.PURCHASING_ITEM_FLAG,
1199 I.SHIPPABLE_ITEM_FLAG,
1200 I.CUSTOMER_ORDER_FLAG,
1201 I.INTERNAL_ORDER_FLAG,
1202 I.INVENTORY_ITEM_FLAG,
1203 I.ENG_ITEM_FLAG,
1204 I.INVENTORY_ASSET_FLAG,
1205 -- * For the bug 3888752 from its base bug --ANMURALI
1206 -- I.PURCHASING_ENABLED_FLAG,
1207 -- I.CUSTOMER_ORDER_ENABLED_FLAG,
1208 -- I.INTERNAL_ORDER_ENABLED_FLAG,
1209 I.SO_TRANSACTIONS_FLAG,
1210 -- I.MTL_TRANSACTIONS_ENABLED_FLAG,
1211 -- I.STOCK_ENABLED_FLAG,
1212 -- I.BOM_ENABLED_FLAG,
1213 -- I.BUILD_IN_WIP_FLAG,
1214 I.REVISION_QTY_CONTROL_CODE,
1215 I.ITEM_CATALOG_GROUP_ID,
1216 I.CATALOG_STATUS_FLAG,
1217 I.RETURNABLE_FLAG,
1218 I.DEFAULT_SHIPPING_ORG,
1219 I.COLLATERAL_FLAG,
1220 I.TAXABLE_FLAG,
1221 I.PURCHASING_TAX_CODE,
1222 I.QTY_RCV_EXCEPTION_CODE,
1223 I.ALLOW_ITEM_DESC_UPDATE_FLAG,
1224 I.INSPECTION_REQUIRED_FLAG,
1225 I.RECEIPT_REQUIRED_FLAG,
1226 I.MARKET_PRICE,
1227 I.HAZARD_CLASS_ID,
1228 I.RFQ_REQUIRED_FLAG,
1229 I.QTY_RCV_TOLERANCE,
1230 I.LIST_PRICE_PER_UNIT,
1231 I.UN_NUMBER_ID,
1232 I.PRICE_TOLERANCE_PERCENT,
1233 I.ASSET_CATEGORY_ID,
1234 I.ROUNDING_FACTOR,
1235 I.UNIT_OF_ISSUE,
1236 I.ENFORCE_SHIP_TO_LOCATION_CODE,
1237 I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
1238 I.ALLOW_UNORDERED_RECEIPTS_FLAG,
1239 I.ALLOW_EXPRESS_DELIVERY_FLAG,
1240 I.DAYS_EARLY_RECEIPT_ALLOWED,
1241 I.DAYS_LATE_RECEIPT_ALLOWED,
1242 I.RECEIPT_DAYS_EXCEPTION_CODE,
1243 I.RECEIVING_ROUTING_ID,
1244 I.INVOICE_CLOSE_TOLERANCE,
1245 I.RECEIVE_CLOSE_TOLERANCE,
1246 I.AUTO_LOT_ALPHA_PREFIX,
1247 I.START_AUTO_LOT_NUMBER,
1248 I.LOT_CONTROL_CODE,
1249 I.SHELF_LIFE_CODE,
1250 I.SHELF_LIFE_DAYS,
1251 I.SERIAL_NUMBER_CONTROL_CODE,
1252 I.START_AUTO_SERIAL_NUMBER,
1253 I.AUTO_SERIAL_ALPHA_PREFIX,
1254 I.SOURCE_TYPE,
1255 --* Commented for Bug #3764605 : Below attributes are org controlled.
1256 --I.SOURCE_ORGANIZATION_ID,
1257 --I.SOURCE_SUBINVENTORY,
1258 --* End of Bug #3764605
1259 I.RESTRICT_SUBINVENTORIES_CODE,
1260 I.UNIT_WEIGHT,
1261 I.WEIGHT_UOM_CODE,
1262 I.VOLUME_UOM_CODE,
1263 I.UNIT_VOLUME,
1264 I.RESTRICT_LOCATORS_CODE,
1265 I.LOCATION_CONTROL_CODE,
1266 I.SHRINKAGE_RATE,
1267 I.ACCEPTABLE_EARLY_DAYS,
1268 I.PLANNING_TIME_FENCE_CODE,
1269 I.DEMAND_TIME_FENCE_CODE,
1270 I.LEAD_TIME_LOT_SIZE,
1271 I.STD_LOT_SIZE,
1272 I.CUM_MANUFACTURING_LEAD_TIME,
1273 I.OVERRUN_PERCENTAGE,
1274 I.MRP_CALCULATE_ATP_FLAG,
1275 I.ACCEPTABLE_RATE_INCREASE,
1276 I.ACCEPTABLE_RATE_DECREASE,
1277 I.CUMULATIVE_TOTAL_LEAD_TIME,
1278 I.PLANNING_TIME_FENCE_DAYS,
1279 I.DEMAND_TIME_FENCE_DAYS,
1280 I.END_ASSEMBLY_PEGGING_FLAG,
1281 I.REPETITIVE_PLANNING_FLAG,
1282 I.BOM_ITEM_TYPE,
1283 I.PICK_COMPONENTS_FLAG,
1284 I.REPLENISH_TO_ORDER_FLAG,
1285 I.BASE_ITEM_ID,
1286 I.ATP_COMPONENTS_FLAG,
1287 I.ATP_FLAG,
1288 I.FIXED_LEAD_TIME,
1289 I.VARIABLE_LEAD_TIME,
1290 I.WIP_SUPPLY_LOCATOR_ID,
1291 I.WIP_SUPPLY_TYPE,
1292 I.WIP_SUPPLY_SUBINVENTORY,
1293 I.PRIMARY_UOM_CODE,
1294 I.PRIMARY_UNIT_OF_MEASURE,
1295 I.ALLOWED_UNITS_LOOKUP_CODE,
1296 I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
1297 I.INVENTORY_ITEM_STATUS_CODE,
1298 I.INVENTORY_PLANNING_CODE,
1299 I.PLANNER_CODE,
1300 I.PLANNING_MAKE_BUY_CODE,
1301 I.FIXED_LOT_MULTIPLIER,
1302 I.ROUNDING_CONTROL_TYPE,
1303 I.CARRYING_COST,
1304 I.POSTPROCESSING_LEAD_TIME,
1305 I.PREPROCESSING_LEAD_TIME,
1306 I.FULL_LEAD_TIME,
1307 I.ORDER_COST,
1308 I.MRP_SAFETY_STOCK_PERCENT,
1309 I.MRP_SAFETY_STOCK_CODE,
1310 I.MIN_MINMAX_QUANTITY,
1311 I.MAX_MINMAX_QUANTITY,
1312 I.MINIMUM_ORDER_QUANTITY,
1313 I.FIXED_ORDER_QUANTITY,
1314 I.FIXED_DAYS_SUPPLY,
1315 I.MAXIMUM_ORDER_QUANTITY,
1316 I.ATP_RULE_ID,
1317 I.PICKING_RULE_ID,
1318 I.RESERVABLE_TYPE,
1319 I.POSITIVE_MEASUREMENT_ERROR,
1320 I.NEGATIVE_MEASUREMENT_ERROR,
1321 I.ENGINEERING_ECN_CODE,
1322 I.ENGINEERING_ITEM_ID,
1323 I.ENGINEERING_DATE,
1324 I.SERVICE_STARTING_DELAY,
1325 I.SERVICEABLE_COMPONENT_FLAG,
1326 I.SERVICEABLE_PRODUCT_FLAG,
1327 I.BASE_WARRANTY_SERVICE_ID,
1328 I.PAYMENT_TERMS_ID,
1329 I.PREVENTIVE_MAINTENANCE_FLAG,
1330 I.PRIMARY_SPECIALIST_ID,
1331 I.SECONDARY_SPECIALIST_ID,
1332 I.SERVICEABLE_ITEM_CLASS_ID,
1333 I.TIME_BILLABLE_FLAG,
1334 I.MATERIAL_BILLABLE_FLAG,
1335 I.EXPENSE_BILLABLE_FLAG,
1336 I.PRORATE_SERVICE_FLAG,
1337 I.COVERAGE_SCHEDULE_ID,
1338 I.SERVICE_DURATION_PERIOD_CODE,
1339 I.SERVICE_DURATION,
1340 I.WARRANTY_VENDOR_ID,
1341 I.MAX_WARRANTY_AMOUNT,
1342 I.RESPONSE_TIME_PERIOD_CODE,
1343 I.RESPONSE_TIME_VALUE,
1344 I.NEW_REVISION_CODE,
1345 I.INVOICEABLE_ITEM_FLAG,
1346 I.TAX_CODE,
1347 -- I.INVOICE_ENABLED_FLAG,
1348 I.MUST_USE_APPROVED_VENDOR_FLAG,
1349 I.REQUEST_ID,
1350 I.PROGRAM_APPLICATION_ID,
1351 I.PROGRAM_ID,
1352 I.PROGRAM_UPDATE_DATE,
1353 I.OUTSIDE_OPERATION_FLAG,
1354 I.OUTSIDE_OPERATION_UOM_TYPE,
1355 I.SAFETY_STOCK_BUCKET_DAYS,
1356 I.AUTO_REDUCE_MPS,
1357 I.COSTING_ENABLED_FLAG,
1358 I.CYCLE_COUNT_ENABLED_FLAG,
1359 I.AUTO_CREATED_CONFIG_FLAG,
1360 I.ITEM_TYPE,
1361 I.MODEL_CONFIG_CLAUSE_NAME,
1362 I.SHIP_MODEL_COMPLETE_FLAG,
1363 I.MRP_PLANNING_CODE,
1364 I.ATO_FORECAST_CONTROL,
1365 I.RETURN_INSPECTION_REQUIREMENT,
1366 I.RELEASE_TIME_FENCE_CODE,
1367 I.RELEASE_TIME_FENCE_DAYS,
1368 I.CONTAINER_ITEM_FLAG,
1369 I.CONTAINER_TYPE_CODE,
1370 I.INTERNAL_VOLUME,
1371 I.MAXIMUM_LOAD_WEIGHT,
1372 I.MINIMUM_FILL_PERCENT,
1373 I.VEHICLE_ITEM_FLAG,
1374 I.CHECK_SHORTAGES_FLAG /*CK 18NOV98 Added new attribute*/
1375 , I.EFFECTIVITY_CONTROL
1376 , I.OVERCOMPLETION_TOLERANCE_TYPE
1377 , I.OVERCOMPLETION_TOLERANCE_VALUE
1378 , I.OVER_SHIPMENT_TOLERANCE
1379 , I.UNDER_SHIPMENT_TOLERANCE
1380 , I.OVER_RETURN_TOLERANCE
1381 , I.UNDER_RETURN_TOLERANCE
1382 , I.EQUIPMENT_TYPE
1383 , I.RECOVERED_PART_DISP_CODE
1384 , I.DEFECT_TRACKING_ON_FLAG
1385 , I.EVENT_FLAG
1386 , I.ELECTRONIC_FLAG
1387 , I.DOWNLOADABLE_FLAG
1388 , I.VOL_DISCOUNT_EXEMPT_FLAG
1389 , I.COUPON_EXEMPT_FLAG
1390 , I.COMMS_NL_TRACKABLE_FLAG
1391 , I.ASSET_CREATION_CODE
1392 , I.COMMS_ACTIVATION_REQD_FLAG
1393 , I.ORDERABLE_ON_WEB_FLAG
1394 , I.BACK_ORDERABLE_FLAG
1395 , I.WEB_STATUS
1396 , I.INDIVISIBLE_FLAG
1397 , I.DIMENSION_UOM_CODE
1398 , I.UNIT_LENGTH
1399 , I.UNIT_WIDTH
1400 , I.UNIT_HEIGHT
1401 , I.BULK_PICKED_FLAG
1402 , I.LOT_STATUS_ENABLED
1403 , I.DEFAULT_LOT_STATUS_ID
1404 , I.SERIAL_STATUS_ENABLED
1405 , I.DEFAULT_SERIAL_STATUS_ID
1406 , I.INVENTORY_CARRY_PENALTY
1407 , I.OPERATION_SLACK_PENALTY
1408 , I.FINANCING_ALLOWED_FLAG
1409 , I.EAM_ITEM_TYPE
1410 , I.EAM_ACTIVITY_TYPE_CODE
1411 , I.EAM_ACTIVITY_CAUSE_CODE
1412 , I.EAM_ACT_NOTIFICATION_FLAG
1413 , I.EAM_ACT_SHUTDOWN_STATUS
1414 , I.DUAL_UOM_CONTROL
1415 , I.SECONDARY_UOM_CODE
1416 , I.DUAL_UOM_DEVIATION_HIGH
1417 , I.DUAL_UOM_DEVIATION_LOW
1418 --, I.SERVICE_ITEM_FLAG
1419 --, I.VENDOR_WARRANTY_FLAG
1420 --, I.USAGE_ITEM_FLAG
1421 , I.CONTRACT_ITEM_TYPE_CODE
1422 , I.SUBSCRIPTION_DEPEND_FLAG
1423 --
1424 , I.SERV_REQ_ENABLED_CODE
1425 , I.SERV_BILLING_ENABLED_FLAG
1426 , I.SERV_IMPORTANCE_LEVEL
1427 , I.PLANNED_INV_POINT_FLAG
1428 , I.DEFAULT_SO_SOURCE_TYPE
1429 , I.CREATE_SUPPLY_FLAG
1430 , I.SUBSTITUTION_WINDOW_CODE
1431 , I.SUBSTITUTION_WINDOW_DAYS
1432 --Moved the dependant Lot attributes together
1433 --(For ease of fix for bug# 2760857)
1434 , I.LOT_SPLIT_ENABLED
1435 , I.LOT_MERGE_ENABLED
1436 , I.LOT_TRANSLATE_ENABLED
1437 --Added as part of 11.5.9
1438 , I.LOT_SUBSTITUTION_ENABLED
1439 , I.MINIMUM_LICENSE_QUANTITY
1440 , I.EAM_ACTIVITY_SOURCE_CODE
1441 , I.IB_ITEM_INSTANCE_CLASS
1442 , I.CONFIG_MODEL_TYPE
1443 --2740503: Defaulting lifecycle-phase from master org.
1444 , I.LIFECYCLE_ID
1445 , I.CURRENT_PHASE_ID
1446 --Added as part of 11.5.10
1447 , I.TRACKING_QUANTITY_IND
1448 , I.ONT_PRICING_QTY_SOURCE
1449 , I.SECONDARY_DEFAULT_IND
1450 , I.CONFIG_ORGS
1451 , I.CONFIG_MATCH
1452 , I.VMI_MINIMUM_UNITS
1453 , I.VMI_MINIMUM_DAYS
1454 , I.VMI_MAXIMUM_UNITS
1455 , I.VMI_MAXIMUM_DAYS
1456 , I.VMI_FIXED_ORDER_QUANTITY
1457 , I.SO_AUTHORIZATION_FLAG
1458 , I.CONSIGNED_FLAG
1459 , I.ASN_AUTOEXPIRE_FLAG
1460 , I.VMI_FORECAST_TYPE
1461 , I.FORECAST_HORIZON
1462 , I.EXCLUDE_FROM_BUDGET_FLAG
1463 , I.DAYS_TGT_INV_SUPPLY
1464 , I.DAYS_TGT_INV_WINDOW
1465 , I.DAYS_MAX_INV_SUPPLY
1466 , I.DAYS_MAX_INV_WINDOW
1467 , I.DRP_PLANNED_FLAG
1468 , I.CRITICAL_COMPONENT_FLAG
1469 , I.CONTINOUS_TRANSFER
1470 , I.CONVERGENCE
1471 , I.DIVERGENCE
1472 /* Start Bug 3713912 */
1473 , I.LOT_DIVISIBLE_FLAG
1474 , I.GRADE_CONTROL_FLAG
1475 , I.DEFAULT_GRADE
1476 , I.CHILD_LOT_FLAG
1477 , I.PARENT_CHILD_GENERATION_FLAG
1478 , I.CHILD_LOT_PREFIX
1479 , I.CHILD_LOT_STARTING_NUMBER
1480 , I.CHILD_LOT_VALIDATION_FLAG
1481 , I.COPY_LOT_ATTRIBUTE_FLAG
1482 --5349389 , I.RECIPE_ENABLED_FLAG
1483 , I.PROCESS_QUALITY_ENABLED_FLAG
1484 --5349389 , I.PROCESS_EXECUTION_ENABLED_FLAG
1485 , I.PROCESS_COSTING_ENABLED_FLAG
1486 , I.PROCESS_SUPPLY_SUBINVENTORY
1487 , I.PROCESS_SUPPLY_LOCATOR_ID
1488 , I.PROCESS_YIELD_SUBINVENTORY
1489 , I.PROCESS_YIELD_LOCATOR_ID
1490 , I.HAZARDOUS_MATERIAL_FLAG
1491 , I.CAS_NUMBER
1492 , I.RETEST_INTERVAL
1493 , I.EXPIRATION_ACTION_INTERVAL
1494 , I.EXPIRATION_ACTION_CODE
1495 , I.MATURITY_DAYS
1496 , I.HOLD_DAYS
1497 /* End Bug 3713912 */
1498 --R12 Enhancement
1499 , I.REPAIR_LEADTIME
1500 , I.REPAIR_YIELD
1501 , I.PREPOSITION_POINT
1502 , I.REPAIR_PROGRAM
1503 , I.CHARGE_PERIODICITY_CODE
1504 , I.SUBCONTRACTING_COMPONENT
1505 , I.OUTSOURCED_ASSEMBLY
1506 /* FPC Attributes */
1507 , I.GDSN_OUTBOUND_ENABLED_FLAG
1508 , I.TRADE_ITEM_DESCRIPTOR
1509 , I.STYLE_ITEM_FLAG
1510 , I.STYLE_ITEM_ID
1511 ) = (
1512 SELECT
1513 master_org_id, /* LAST_UPDATED_BY */
1514 decode(i.created_by,-99,i.created_by,l_created_by), /*NP Comment here is where created_by is set
1515 to 1 to reflect item exists in msi */
1516 NVL(I.SUMMARY_FLAG,M.SUMMARY_FLAG),
1517 NVL(I.ENABLED_FLAG,M.ENABLED_FLAG),
1518 NVL(I.START_DATE_ACTIVE,M.START_DATE_ACTIVE),
1519 NVL(I.END_DATE_ACTIVE,M.END_DATE_ACTIVE),
1520 NVL(I.DESCRIPTION, MT.DESCRIPTION),
1521 NVL(I.LONG_DESCRIPTION, MT.LONG_DESCRIPTION),
1522 NVL(I.BUYER_ID,M.BUYER_ID),
1523 NVL(I.ACCOUNTING_RULE_ID,M.ACCOUNTING_RULE_ID),
1524 NVL(I.INVOICING_RULE_ID,M.INVOICING_RULE_ID),
1525 NVL(I.SEGMENT1,M.SEGMENT1),
1526 NVL(I.SEGMENT2,M.SEGMENT2),
1527 NVL(I.SEGMENT3,M.SEGMENT3),
1528 NVL(I.SEGMENT4,M.SEGMENT4),
1529 NVL(I.SEGMENT5,M.SEGMENT5),
1530 NVL(I.SEGMENT6,M.SEGMENT6),
1531 NVL(I.SEGMENT7,M.SEGMENT7),
1532 NVL(I.SEGMENT8,M.SEGMENT8),
1533 NVL(I.SEGMENT9,M.SEGMENT9),
1534 NVL(I.SEGMENT10,M.SEGMENT10),
1535 NVL(I.SEGMENT11,M.SEGMENT11),
1536 NVL(I.SEGMENT12,M.SEGMENT12),
1537 NVL(I.SEGMENT13,M.SEGMENT13),
1538 NVL(I.SEGMENT14,M.SEGMENT14),
1539 NVL(I.SEGMENT15,M.SEGMENT15),
1540 NVL(I.SEGMENT16,M.SEGMENT16),
1541 NVL(I.SEGMENT17,M.SEGMENT17),
1542 NVL(I.SEGMENT18,M.SEGMENT18),
1543 NVL(I.SEGMENT19,M.SEGMENT19),
1544 NVL(I.SEGMENT20,M.SEGMENT20),
1545 NVL(I.ATTRIBUTE_CATEGORY,M.ATTRIBUTE_CATEGORY),
1546 NVL(I.ATTRIBUTE1,M.ATTRIBUTE1),
1547 NVL(I.ATTRIBUTE2,M.ATTRIBUTE2),
1548 NVL(I.ATTRIBUTE3,M.ATTRIBUTE3),
1549 NVL(I.ATTRIBUTE4,M.ATTRIBUTE4),
1550 NVL(I.ATTRIBUTE5,M.ATTRIBUTE5),
1551 NVL(I.ATTRIBUTE6,M.ATTRIBUTE6),
1552 NVL(I.ATTRIBUTE7,M.ATTRIBUTE7),
1553 NVL(I.ATTRIBUTE8,M.ATTRIBUTE8),
1554 NVL(I.ATTRIBUTE9,M.ATTRIBUTE9),
1555 NVL(I.ATTRIBUTE10,M.ATTRIBUTE10),
1556 NVL(I.ATTRIBUTE11,M.ATTRIBUTE11),
1557 NVL(I.ATTRIBUTE12,M.ATTRIBUTE12),
1558 NVL(I.ATTRIBUTE13,M.ATTRIBUTE13),
1559 NVL(I.ATTRIBUTE14,M.ATTRIBUTE14),
1560 NVL(I.ATTRIBUTE15,M.ATTRIBUTE15),
1561 /* End Bug 3713912 */
1562 NVL(I.ATTRIBUTE16, M.ATTRIBUTE16) ,
1563 NVL(I.ATTRIBUTE17, M.ATTRIBUTE17) ,
1564 NVL(I.ATTRIBUTE18, M.ATTRIBUTE18) ,
1565 NVL(I.ATTRIBUTE19, M.ATTRIBUTE19) ,
1569 NVL(I.ATTRIBUTE23, M.ATTRIBUTE23) ,
1566 NVL(I.ATTRIBUTE20, M.ATTRIBUTE20) ,
1567 NVL(I.ATTRIBUTE21, M.ATTRIBUTE21) ,
1568 NVL(I.ATTRIBUTE22, M.ATTRIBUTE22) ,
1570 NVL(I.ATTRIBUTE24, M.ATTRIBUTE24) ,
1571 NVL(I.ATTRIBUTE25, M.ATTRIBUTE25) ,
1572 NVL(I.ATTRIBUTE26, M.ATTRIBUTE26) ,
1573 NVL(I.ATTRIBUTE27, M.ATTRIBUTE27) ,
1574 NVL(I.ATTRIBUTE28, M.ATTRIBUTE28) ,
1575 NVL(I.ATTRIBUTE29, M.ATTRIBUTE29) ,
1576 NVL(I.ATTRIBUTE30, M.ATTRIBUTE30) ,
1577 /* End Bug 3713912 */
1578 NVL(I.GLOBAL_ATTRIBUTE_CATEGORY,M.GLOBAL_ATTRIBUTE_CATEGORY),
1579 NVL(I.GLOBAL_ATTRIBUTE1,M.GLOBAL_ATTRIBUTE1),
1580 NVL(I.GLOBAL_ATTRIBUTE2,M.GLOBAL_ATTRIBUTE2),
1581 NVL(I.GLOBAL_ATTRIBUTE3,M.GLOBAL_ATTRIBUTE3),
1582 NVL(I.GLOBAL_ATTRIBUTE4,M.GLOBAL_ATTRIBUTE4),
1583 NVL(I.GLOBAL_ATTRIBUTE5,M.GLOBAL_ATTRIBUTE5),
1584 NVL(I.GLOBAL_ATTRIBUTE6,M.GLOBAL_ATTRIBUTE6),
1585 NVL(I.GLOBAL_ATTRIBUTE7,M.GLOBAL_ATTRIBUTE7),
1586 NVL(I.GLOBAL_ATTRIBUTE8,M.GLOBAL_ATTRIBUTE8),
1587 NVL(I.GLOBAL_ATTRIBUTE9,M.GLOBAL_ATTRIBUTE9),
1588 NVL(I.GLOBAL_ATTRIBUTE10,M.GLOBAL_ATTRIBUTE10),
1589 NVL(I.GLOBAL_ATTRIBUTE11,M.GLOBAL_ATTRIBUTE11),
1590 NVL(I.GLOBAL_ATTRIBUTE12,M.GLOBAL_ATTRIBUTE12),
1591 NVL(I.GLOBAL_ATTRIBUTE13,M.GLOBAL_ATTRIBUTE13),
1592 NVL(I.GLOBAL_ATTRIBUTE14,M.GLOBAL_ATTRIBUTE14),
1593 NVL(I.GLOBAL_ATTRIBUTE15,M.GLOBAL_ATTRIBUTE15),
1594 NVL(I.GLOBAL_ATTRIBUTE16,M.GLOBAL_ATTRIBUTE16),
1595 NVL(I.GLOBAL_ATTRIBUTE17,M.GLOBAL_ATTRIBUTE17),
1596 NVL(I.GLOBAL_ATTRIBUTE18,M.GLOBAL_ATTRIBUTE18),
1597 NVL(I.GLOBAL_ATTRIBUTE19,M.GLOBAL_ATTRIBUTE19),
1598 NVL(I.GLOBAL_ATTRIBUTE20,M.GLOBAL_ATTRIBUTE20),
1599 NVL(I.PURCHASING_ITEM_FLAG,M.PURCHASING_ITEM_FLAG),
1600 DECODE(I.INTERNAL_ORDER_FLAG, 'Y','Y', DECODE(M.INTERNAL_ORDER_FLAG, 'Y','Y',
1601 nvl(I.SHIPPABLE_ITEM_FLAG,M.SHIPPABLE_ITEM_FLAG))),
1602 NVL(I.CUSTOMER_ORDER_FLAG,M.CUSTOMER_ORDER_FLAG),
1603 NVL(I.INTERNAL_ORDER_FLAG,M.INTERNAL_ORDER_FLAG),
1604 NVL(I.INVENTORY_ITEM_FLAG,M.INVENTORY_ITEM_FLAG),
1605 NVL(I.ENG_ITEM_FLAG,M.ENG_ITEM_FLAG),
1606 NVL(I.INVENTORY_ASSET_FLAG,M.INVENTORY_ASSET_FLAG),
1607 -- * For the bug 3888752 from its base bug --ANMURALI
1608 -- NVL(I.PURCHASING_ENABLED_FLAG,M.PURCHASING_ENABLED_FLAG),
1609 -- NVL(I.CUSTOMER_ORDER_ENABLED_FLAG,M.CUSTOMER_ORDER_ENABLED_FLAG),
1610 -- NVL(I.INTERNAL_ORDER_ENABLED_FLAG,M.INTERNAL_ORDER_ENABLED_FLAG),
1611 NVL(I.SO_TRANSACTIONS_FLAG,M.SO_TRANSACTIONS_FLAG),
1612 -- NVL(I.MTL_TRANSACTIONS_ENABLED_FLAG,M.MTL_TRANSACTIONS_ENABLED_FLAG),
1613 -- NVL(I.STOCK_ENABLED_FLAG,M.STOCK_ENABLED_FLAG),
1614 -- NVL(I.BOM_ENABLED_FLAG,M.BOM_ENABLED_FLAG),
1615 -- NVL(I.BUILD_IN_WIP_FLAG,M.BUILD_IN_WIP_FLAG),
1616 NVL(I.REVISION_QTY_CONTROL_CODE,M.REVISION_QTY_CONTROL_CODE),
1617 NVL(I.ITEM_CATALOG_GROUP_ID,M.ITEM_CATALOG_GROUP_ID),
1618 NVL(I.CATALOG_STATUS_FLAG,M.CATALOG_STATUS_FLAG),
1619 NVL(I.RETURNABLE_FLAG,M.RETURNABLE_FLAG),
1620 NVL(I.DEFAULT_SHIPPING_ORG,M.DEFAULT_SHIPPING_ORG),
1621 NVL(I.COLLATERAL_FLAG,M.COLLATERAL_FLAG),
1622 NVL(I.TAXABLE_FLAG,M.TAXABLE_FLAG),
1623 -- NVL(I.PURCHASING_TAX_CODE,M.PURCHASING_TAX_CODE),
1624 NVL(I.PURCHASING_TAX_CODE,DECODE(NVL(I.TAXABLE_FLAG,M.TAXABLE_FLAG),NULL,NULL,'N',NULL,M.PURCHASING_TAX_CODE)),
1625 NVL(I.QTY_RCV_EXCEPTION_CODE,M.QTY_RCV_EXCEPTION_CODE),
1626 NVL(I.ALLOW_ITEM_DESC_UPDATE_FLAG,M.ALLOW_ITEM_DESC_UPDATE_FLAG),
1627 NVL(I.INSPECTION_REQUIRED_FLAG,M.INSPECTION_REQUIRED_FLAG),
1628 NVL(I.RECEIPT_REQUIRED_FLAG,M.RECEIPT_REQUIRED_FLAG),
1629 NVL(I.MARKET_PRICE,M.MARKET_PRICE),
1630 NVL(I.HAZARD_CLASS_ID,M.HAZARD_CLASS_ID),
1631 NVL(I.RFQ_REQUIRED_FLAG,M.RFQ_REQUIRED_FLAG),
1632 NVL(I.QTY_RCV_TOLERANCE,M.QTY_RCV_TOLERANCE),
1633 NVL(I.LIST_PRICE_PER_UNIT,M.LIST_PRICE_PER_UNIT),
1634 NVL(I.UN_NUMBER_ID,M.UN_NUMBER_ID),
1635 NVL(I.PRICE_TOLERANCE_PERCENT,M.PRICE_TOLERANCE_PERCENT),
1636 NVL(I.ASSET_CATEGORY_ID,M.ASSET_CATEGORY_ID),
1637 NVL(I.ROUNDING_FACTOR,M.ROUNDING_FACTOR),
1638 NVL(I.UNIT_OF_ISSUE,M.UNIT_OF_ISSUE),
1639 NVL(I.ENFORCE_SHIP_TO_LOCATION_CODE,M.ENFORCE_SHIP_TO_LOCATION_CODE),
1640 NVL(I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,M.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
1641 NVL(I.ALLOW_UNORDERED_RECEIPTS_FLAG,M.ALLOW_UNORDERED_RECEIPTS_FLAG),
1642 NVL(I.ALLOW_EXPRESS_DELIVERY_FLAG,M.ALLOW_EXPRESS_DELIVERY_FLAG),
1643 NVL(I.DAYS_EARLY_RECEIPT_ALLOWED,M.DAYS_EARLY_RECEIPT_ALLOWED),
1644 NVL(I.DAYS_LATE_RECEIPT_ALLOWED,M.DAYS_LATE_RECEIPT_ALLOWED),
1645 NVL(I.RECEIPT_DAYS_EXCEPTION_CODE,M.RECEIPT_DAYS_EXCEPTION_CODE),
1646 NVL(I.RECEIVING_ROUTING_ID,M.RECEIVING_ROUTING_ID),
1647 NVL(I.INVOICE_CLOSE_TOLERANCE,M.INVOICE_CLOSE_TOLERANCE),
1648 NVL(I.RECEIVE_CLOSE_TOLERANCE,M.RECEIVE_CLOSE_TOLERANCE),
1649 NVL(I.AUTO_LOT_ALPHA_PREFIX,M.AUTO_LOT_ALPHA_PREFIX),
1650 NVL(I.START_AUTO_LOT_NUMBER,M.START_AUTO_LOT_NUMBER),
1651 NVL(I.LOT_CONTROL_CODE,M.LOT_CONTROL_CODE),
1652 NVL(I.SHELF_LIFE_CODE,M.SHELF_LIFE_CODE),
1653 NVL(I.SHELF_LIFE_DAYS,M.SHELF_LIFE_DAYS),
1654 NVL(I.SERIAL_NUMBER_CONTROL_CODE,M.SERIAL_NUMBER_CONTROL_CODE),
1655 NVL(I.START_AUTO_SERIAL_NUMBER,M.START_AUTO_SERIAL_NUMBER),
1656 NVL(I.AUTO_SERIAL_ALPHA_PREFIX,M.AUTO_SERIAL_ALPHA_PREFIX),
1657 NVL(I.SOURCE_TYPE,M.SOURCE_TYPE),
1658 --* Commented for Bug #3764605 : Below attributes are org controlled.
1659 --NVL(I.SOURCE_ORGANIZATION_ID,M.SOURCE_ORGANIZATION_ID),
1660 --NVL(I.SOURCE_SUBINVENTORY,M.SOURCE_SUBINVENTORY),
1661 --* End of Bug #3764605
1662 NVL(I.RESTRICT_SUBINVENTORIES_CODE,M.RESTRICT_SUBINVENTORIES_CODE),
1663 NVL(I.UNIT_WEIGHT,M.UNIT_WEIGHT),
1667 NVL(I.RESTRICT_LOCATORS_CODE,M.RESTRICT_LOCATORS_CODE),
1664 NVL(I.WEIGHT_UOM_CODE,M.WEIGHT_UOM_CODE),
1665 NVL(I.VOLUME_UOM_CODE,M.VOLUME_UOM_CODE),
1666 NVL(I.UNIT_VOLUME,M.UNIT_VOLUME),
1668 NVL(I.LOCATION_CONTROL_CODE,M.LOCATION_CONTROL_CODE),
1669 NVL(I.SHRINKAGE_RATE,M.SHRINKAGE_RATE),
1670 NVL(I.ACCEPTABLE_EARLY_DAYS,M.ACCEPTABLE_EARLY_DAYS),
1671 NVL(I.PLANNING_TIME_FENCE_CODE, M.PLANNING_TIME_FENCE_CODE),
1672 NVL(I.DEMAND_TIME_FENCE_CODE, M.DEMAND_TIME_FENCE_CODE),
1673 NVL(I.LEAD_TIME_LOT_SIZE,M.LEAD_TIME_LOT_SIZE),
1674 NVL(I.STD_LOT_SIZE,M.STD_LOT_SIZE),
1675 NVL(I.CUM_MANUFACTURING_LEAD_TIME,M.CUM_MANUFACTURING_LEAD_TIME),
1676 NVL(I.OVERRUN_PERCENTAGE,M.OVERRUN_PERCENTAGE),
1677 NVL(I.MRP_CALCULATE_ATP_FLAG,M.MRP_CALCULATE_ATP_FLAG),
1678 NVL(I.ACCEPTABLE_RATE_INCREASE,M.ACCEPTABLE_RATE_INCREASE),
1679 NVL(I.ACCEPTABLE_RATE_DECREASE,M.ACCEPTABLE_RATE_DECREASE),
1680 NVL(I.CUMULATIVE_TOTAL_LEAD_TIME,M.CUMULATIVE_TOTAL_LEAD_TIME),
1681 /*AN991207 #1081419: If child org time_fence_code is non-user defined,
1682 do not copy master org time_fence_days to the child org */
1683 NVL(I.PLANNING_TIME_FENCE_DAYS, decode(NVL(I.PLANNING_TIME_FENCE_CODE, M.PLANNING_TIME_FENCE_CODE),
1684 4, M.PLANNING_TIME_FENCE_DAYS, NULL)),
1685 NVL(I.DEMAND_TIME_FENCE_DAYS, decode(NVL(I.DEMAND_TIME_FENCE_CODE, M.DEMAND_TIME_FENCE_CODE),
1686 4, M.DEMAND_TIME_FENCE_DAYS, NULL)),
1687 NVL(I.END_ASSEMBLY_PEGGING_FLAG,M.END_ASSEMBLY_PEGGING_FLAG),
1688 NVL(I.REPETITIVE_PLANNING_FLAG,M.REPETITIVE_PLANNING_FLAG),
1689 NVL(I.BOM_ITEM_TYPE,M.BOM_ITEM_TYPE),
1690 NVL(I.PICK_COMPONENTS_FLAG,M.PICK_COMPONENTS_FLAG),
1691 NVL(I.REPLENISH_TO_ORDER_FLAG,M.REPLENISH_TO_ORDER_FLAG),
1692 NVL(I.BASE_ITEM_ID,M.BASE_ITEM_ID),
1693 NVL(I.ATP_COMPONENTS_FLAG,M.ATP_COMPONENTS_FLAG),
1694 NVL(I.ATP_FLAG,M.ATP_FLAG),
1695 NVL(I.FIXED_LEAD_TIME,M.FIXED_LEAD_TIME),
1696 NVL(I.VARIABLE_LEAD_TIME,M.VARIABLE_LEAD_TIME),
1697 I.WIP_SUPPLY_LOCATOR_ID, /*NP 26AUG94 this should not default from master*/
1698 NVL(I.WIP_SUPPLY_TYPE,M.WIP_SUPPLY_TYPE),
1699 I.WIP_SUPPLY_SUBINVENTORY, /*NP 26AUG94 this should not default from master*/
1700 -- Primary Unit of Measure is now maintained via the PRIMARY_UOM_CODE column.
1701 -- Bug: 4071090 NVL(I.PRIMARY_UOM_CODE,M.PRIMARY_UOM_CODE),
1702 DECODE(I.PRIMARY_UOM_CODE, NULL,decode(I.PRIMARY_UNIT_OF_MEASURE,NULL,M.PRIMARY_UOM_CODE,l_Primary_UOM_Code),I.PRIMARY_UOM_CODE),
1703 --No defaulting from Master, as PRIMARY_UOM_CODE takes care of it.
1704 NVL(l_base_primary_UOM,I.PRIMARY_UNIT_OF_MEASURE), /* MSIB only stores untranslated values for PUOM 5438341*/
1705 NVL(I.ALLOWED_UNITS_LOOKUP_CODE,M.ALLOWED_UNITS_LOOKUP_CODE),
1706 NVL(I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,M.DEFAULT_INCLUDE_IN_ROLLUP_FLAG),
1707 NVL(I.INVENTORY_ITEM_STATUS_CODE,M.INVENTORY_ITEM_STATUS_CODE),
1708 NVL(I.INVENTORY_PLANNING_CODE,M.INVENTORY_PLANNING_CODE),
1709 I.PLANNER_CODE, /*NP 26AUG94 no defaulting from master*/
1710 NVL(I.PLANNING_MAKE_BUY_CODE,M.PLANNING_MAKE_BUY_CODE),
1711 NVL(I.FIXED_LOT_MULTIPLIER,M.FIXED_LOT_MULTIPLIER),
1712 NVL(I.ROUNDING_CONTROL_TYPE,M.ROUNDING_CONTROL_TYPE),
1713 NVL(I.CARRYING_COST,M.CARRYING_COST),
1714 NVL(I.POSTPROCESSING_LEAD_TIME,M.POSTPROCESSING_LEAD_TIME),
1715 NVL(I.PREPROCESSING_LEAD_TIME,M.PREPROCESSING_LEAD_TIME),
1716 NVL(I.FULL_LEAD_TIME,M.FULL_LEAD_TIME),
1717 NVL(I.ORDER_COST,M.ORDER_COST),
1718 NVL(I.MRP_SAFETY_STOCK_PERCENT,M.MRP_SAFETY_STOCK_PERCENT),
1719 NVL(I.MRP_SAFETY_STOCK_CODE,M.MRP_SAFETY_STOCK_CODE),
1720 NVL(I.MIN_MINMAX_QUANTITY,M.MIN_MINMAX_QUANTITY),
1721 NVL(I.MAX_MINMAX_QUANTITY,M.MAX_MINMAX_QUANTITY),
1722 NVL(I.MINIMUM_ORDER_QUANTITY,M.MINIMUM_ORDER_QUANTITY),
1723 NVL(I.FIXED_ORDER_QUANTITY,M.FIXED_ORDER_QUANTITY),
1724 NVL(I.FIXED_DAYS_SUPPLY,M.FIXED_DAYS_SUPPLY),
1725 NVL(I.MAXIMUM_ORDER_QUANTITY,M.MAXIMUM_ORDER_QUANTITY),
1726 NVL(I.ATP_RULE_ID,M.ATP_RULE_ID),
1727 NVL(I.PICKING_RULE_ID,M.PICKING_RULE_ID),
1728 NVL(I.RESERVABLE_TYPE,M.RESERVABLE_TYPE),
1729 NVL(I.POSITIVE_MEASUREMENT_ERROR,M.POSITIVE_MEASUREMENT_ERROR),
1730 NVL(I.NEGATIVE_MEASUREMENT_ERROR,M.NEGATIVE_MEASUREMENT_ERROR),
1731 NVL(I.ENGINEERING_ECN_CODE,M.ENGINEERING_ECN_CODE),
1732 NVL(I.ENGINEERING_ITEM_ID,M.ENGINEERING_ITEM_ID),
1733 NVL(I.ENGINEERING_DATE,M.ENGINEERING_DATE),
1734 NVL(I.SERVICE_STARTING_DELAY,M.SERVICE_STARTING_DELAY),
1735 NVL(I.SERVICEABLE_COMPONENT_FLAG,M.SERVICEABLE_COMPONENT_FLAG),
1736 NVL(I.SERVICEABLE_PRODUCT_FLAG,M.SERVICEABLE_PRODUCT_FLAG),
1737 NVL(I.BASE_WARRANTY_SERVICE_ID,M.BASE_WARRANTY_SERVICE_ID),
1738 NVL(I.PAYMENT_TERMS_ID,M.PAYMENT_TERMS_ID),
1739 NVL(I.PREVENTIVE_MAINTENANCE_FLAG,M.PREVENTIVE_MAINTENANCE_FLAG),
1740 NVL(I.PRIMARY_SPECIALIST_ID,M.PRIMARY_SPECIALIST_ID),
1741 NVL(I.SECONDARY_SPECIALIST_ID,M.SECONDARY_SPECIALIST_ID),
1742 NVL(I.SERVICEABLE_ITEM_CLASS_ID,M.SERVICEABLE_ITEM_CLASS_ID),
1743 NVL(I.TIME_BILLABLE_FLAG,M.TIME_BILLABLE_FLAG),
1744 -- NVL(I.MATERIAL_BILLABLE_FLAG,M.MATERIAL_BILLABLE_FLAG),
1745 NVL(I.MATERIAL_BILLABLE_FLAG, DECODE(
1746 NVL(I.SERV_BILLING_ENABLED_FLAG,M.SERV_BILLING_ENABLED_FLAG)
1747 ,NULL,NULL,
1748 'N',NULL,
1749 M.MATERIAL_BILLABLE_FLAG)), -- Bug 5361618
1750 NVL(I.EXPENSE_BILLABLE_FLAG,M.EXPENSE_BILLABLE_FLAG),
1751 NVL(I.PRORATE_SERVICE_FLAG,M.PRORATE_SERVICE_FLAG),
1752 NVL(I.COVERAGE_SCHEDULE_ID,M.COVERAGE_SCHEDULE_ID),
1753 NVL(I.SERVICE_DURATION_PERIOD_CODE,M.SERVICE_DURATION_PERIOD_CODE),
1754 NVL(I.SERVICE_DURATION,M.SERVICE_DURATION),
1755 NVL(I.WARRANTY_VENDOR_ID,M.WARRANTY_VENDOR_ID),
1759 NVL(I.NEW_REVISION_CODE,M.NEW_REVISION_CODE),
1756 NVL(I.MAX_WARRANTY_AMOUNT,M.MAX_WARRANTY_AMOUNT),
1757 NVL(I.RESPONSE_TIME_PERIOD_CODE,M.RESPONSE_TIME_PERIOD_CODE),
1758 NVL(I.RESPONSE_TIME_VALUE,M.RESPONSE_TIME_VALUE),
1760 NVL(I.INVOICEABLE_ITEM_FLAG,M.INVOICEABLE_ITEM_FLAG),
1761 NVL(I.TAX_CODE,M.TAX_CODE),
1762 --NVL(I.INVOICE_ENABLED_FLAG,M.INVOICE_ENABLED_FLAG),
1763 NVL(I.MUST_USE_APPROVED_VENDOR_FLAG,M.MUST_USE_APPROVED_VENDOR_FLAG),
1764 NVL(I.REQUEST_ID,M.REQUEST_ID),
1765 NVL(I.PROGRAM_APPLICATION_ID,M.PROGRAM_APPLICATION_ID),
1766 NVL(I.PROGRAM_ID,M.PROGRAM_ID),
1767 NVL(I.PROGRAM_UPDATE_DATE,M.PROGRAM_UPDATE_DATE),
1768 NVL(I.OUTSIDE_OPERATION_FLAG,M.OUTSIDE_OPERATION_FLAG),
1769 NVL(I.OUTSIDE_OPERATION_UOM_TYPE,M.OUTSIDE_OPERATION_UOM_TYPE),
1770 NVL(I.SAFETY_STOCK_BUCKET_DAYS,M.SAFETY_STOCK_BUCKET_DAYS),
1771 NVL(I.AUTO_REDUCE_MPS,M.AUTO_REDUCE_MPS),
1772 NVL(I.COSTING_ENABLED_FLAG,M.COSTING_ENABLED_FLAG),
1773 NVL(I.CYCLE_COUNT_ENABLED_FLAG,M.CYCLE_COUNT_ENABLED_FLAG),
1774 NVL(I.AUTO_CREATED_CONFIG_FLAG,M.AUTO_CREATED_CONFIG_FLAG),
1775 NVL(I.ITEM_TYPE,M.ITEM_TYPE),
1776 NVL(I.MODEL_CONFIG_CLAUSE_NAME,M.MODEL_CONFIG_CLAUSE_NAME),
1777 NVL(I.SHIP_MODEL_COMPLETE_FLAG,M.SHIP_MODEL_COMPLETE_FLAG),
1778 NVL(I.MRP_PLANNING_CODE,M.MRP_PLANNING_CODE),
1779 NVL(I.ATO_FORECAST_CONTROL,M.ATO_FORECAST_CONTROL),
1780 NVL(I.RETURN_INSPECTION_REQUIREMENT,M.RETURN_INSPECTION_REQUIREMENT),
1781 NVL(I.RELEASE_TIME_FENCE_CODE, M.RELEASE_TIME_FENCE_CODE),
1782 /*AN991207 #1081419: If child org time_fence_code is non-user defined,
1783 do not copy master org time_fence_days to the child org */
1784 NVL(I.RELEASE_TIME_FENCE_DAYS, decode(NVL(I.RELEASE_TIME_FENCE_CODE, M.RELEASE_TIME_FENCE_CODE),
1785 4, M.RELEASE_TIME_FENCE_DAYS, NULL)),
1786 NVL(I.CONTAINER_ITEM_FLAG, M.CONTAINER_ITEM_FLAG),
1787 NVL(I.CONTAINER_TYPE_CODE, M.CONTAINER_TYPE_CODE),
1788 NVL(I.INTERNAL_VOLUME, M.INTERNAL_VOLUME),
1789 NVL(I.MAXIMUM_LOAD_WEIGHT, M.MAXIMUM_LOAD_WEIGHT),
1790 NVL(I.MINIMUM_FILL_PERCENT, M.MINIMUM_FILL_PERCENT),
1791 NVL(I.VEHICLE_ITEM_FLAG, M.VEHICLE_ITEM_FLAG),
1792 NVL(I.CHECK_SHORTAGES_FLAG, M.CHECK_SHORTAGES_FLAG)
1793 , NVL( I.EFFECTIVITY_CONTROL, M.EFFECTIVITY_CONTROL )
1794 , NVL( I.OVERCOMPLETION_TOLERANCE_TYPE, M.OVERCOMPLETION_TOLERANCE_TYPE )
1795 , NVL( I.OVERCOMPLETION_TOLERANCE_VALUE, M.OVERCOMPLETION_TOLERANCE_VALUE )
1796 , NVL( I.OVER_SHIPMENT_TOLERANCE, M.OVER_SHIPMENT_TOLERANCE )
1797 , NVL( I.UNDER_SHIPMENT_TOLERANCE, M.UNDER_SHIPMENT_TOLERANCE )
1798 , NVL( I.OVER_RETURN_TOLERANCE, M.OVER_RETURN_TOLERANCE )
1799 , NVL( I.UNDER_RETURN_TOLERANCE, M.UNDER_RETURN_TOLERANCE )
1800 , NVL( I.EQUIPMENT_TYPE, M.EQUIPMENT_TYPE )
1801 , NVL( I.RECOVERED_PART_DISP_CODE, M.RECOVERED_PART_DISP_CODE )
1802 , NVL( I.DEFECT_TRACKING_ON_FLAG, M.DEFECT_TRACKING_ON_FLAG )
1803 , NVL( I.EVENT_FLAG, M.EVENT_FLAG )
1804 , NVL( I.ELECTRONIC_FLAG, M.ELECTRONIC_FLAG )
1805 , NVL( I.DOWNLOADABLE_FLAG, M.DOWNLOADABLE_FLAG )
1806 , NVL( I.VOL_DISCOUNT_EXEMPT_FLAG, M.VOL_DISCOUNT_EXEMPT_FLAG )
1807 , NVL( I.COUPON_EXEMPT_FLAG, M.COUPON_EXEMPT_FLAG )
1808 , NVL( I.COMMS_NL_TRACKABLE_FLAG, M.COMMS_NL_TRACKABLE_FLAG )
1809 , NVL( I.ASSET_CREATION_CODE, M.ASSET_CREATION_CODE )
1810 , NVL( I.COMMS_ACTIVATION_REQD_FLAG, M.COMMS_ACTIVATION_REQD_FLAG )
1811 , NVL( I.ORDERABLE_ON_WEB_FLAG, M.ORDERABLE_ON_WEB_FLAG )
1812 , NVL( I.BACK_ORDERABLE_FLAG, M.BACK_ORDERABLE_FLAG )
1813 , NVL( I.WEB_STATUS, M.WEB_STATUS )
1814 , NVL( I.INDIVISIBLE_FLAG, M.INDIVISIBLE_FLAG )
1815 , NVL( I.DIMENSION_UOM_CODE, M.DIMENSION_UOM_CODE )
1816 , NVL( I.UNIT_LENGTH, M.UNIT_LENGTH )
1817 , NVL( I.UNIT_WIDTH, M.UNIT_WIDTH )
1818 , NVL( I.UNIT_HEIGHT, M.UNIT_HEIGHT )
1819 , NVL( I.BULK_PICKED_FLAG, M.BULK_PICKED_FLAG )
1820 , NVL( I.LOT_STATUS_ENABLED, M.LOT_STATUS_ENABLED )
1821 , NVL( I.DEFAULT_LOT_STATUS_ID, M.DEFAULT_LOT_STATUS_ID )
1822 , NVL( I.SERIAL_STATUS_ENABLED, M.SERIAL_STATUS_ENABLED )
1823 /*Bug: 5739429 Default_serial_status_id should be defaulted based on serial_status_enabled flag.
1824 If serial_status_enabled flag is 'N' then default_serial_status_id should be null.
1825 , NVL( I.DEFAULT_SERIAL_STATUS_ID, M.DEFAULT_SERIAL_STATUS_ID )
1826 */
1827 , NVL( I.DEFAULT_SERIAL_STATUS_ID,DECODE(NVL(I.SERIAL_STATUS_ENABLED,M.SERIAL_STATUS_ENABLED),NULL,NULL,'N',NULL,M.DEFAULT_SERIAL_STATUS_ID))
1828 , NVL( I.INVENTORY_CARRY_PENALTY, M.INVENTORY_CARRY_PENALTY )
1829 , NVL( I.OPERATION_SLACK_PENALTY, M.OPERATION_SLACK_PENALTY )
1830 , NVL( I.FINANCING_ALLOWED_FLAG, M.FINANCING_ALLOWED_FLAG )
1831 , NVL( I.EAM_ITEM_TYPE , M.EAM_ITEM_TYPE )
1832 , NVL( I.EAM_ACTIVITY_TYPE_CODE , M.EAM_ACTIVITY_TYPE_CODE )
1833 , NVL( I.EAM_ACTIVITY_CAUSE_CODE , M.EAM_ACTIVITY_CAUSE_CODE )
1834 , NVL( I.EAM_ACT_NOTIFICATION_FLAG , M.EAM_ACT_NOTIFICATION_FLAG )
1835 , NVL( I.EAM_ACT_SHUTDOWN_STATUS , M.EAM_ACT_SHUTDOWN_STATUS )
1836 , NVL( I.DUAL_UOM_CONTROL , M.DUAL_UOM_CONTROL )
1837 , NVL( I.SECONDARY_UOM_CODE , M.SECONDARY_UOM_CODE )
1838 , NVL( I.DUAL_UOM_DEVIATION_HIGH , M.DUAL_UOM_DEVIATION_HIGH )
1839 , NVL( I.DUAL_UOM_DEVIATION_LOW , M.DUAL_UOM_DEVIATION_LOW )
1840 --, NVL( I.SERVICE_ITEM_FLAG , M.SERVICE_ITEM_FLAG )
1841 --, NVL( I.VENDOR_WARRANTY_FLAG , M.VENDOR_WARRANTY_FLAG )
1842 --, NVL( I.USAGE_ITEM_FLAG , M.USAGE_ITEM_FLAG )
1843 , NVL( I.CONTRACT_ITEM_TYPE_CODE , M.CONTRACT_ITEM_TYPE_CODE )
1844 , NVL( I.SUBSCRIPTION_DEPEND_FLAG , M.SUBSCRIPTION_DEPEND_FLAG )
1845 --
1846 , NVL( I.SERV_REQ_ENABLED_CODE , M.SERV_REQ_ENABLED_CODE )
1847 , NVL( I.SERV_BILLING_ENABLED_FLAG , M.SERV_BILLING_ENABLED_FLAG )
1851 , NVL( I.CREATE_SUPPLY_FLAG , M.CREATE_SUPPLY_FLAG )
1848 , NVL( I.SERV_IMPORTANCE_LEVEL , M.SERV_IMPORTANCE_LEVEL )
1849 , NVL( I.PLANNED_INV_POINT_FLAG , M.PLANNED_INV_POINT_FLAG )
1850 , NVL( I.DEFAULT_SO_SOURCE_TYPE , M.DEFAULT_SO_SOURCE_TYPE )
1852 , NVL( I.SUBSTITUTION_WINDOW_CODE , M.SUBSTITUTION_WINDOW_CODE )
1853 -- If child org window_code is non-user defined,
1854 -- do not copy master org window_days to the child org.
1855 , NVL( I.SUBSTITUTION_WINDOW_DAYS,
1856 DECODE( NVL(I.SUBSTITUTION_WINDOW_CODE, M.SUBSTITUTION_WINDOW_CODE),
1857 4, M.SUBSTITUTION_WINDOW_DAYS,
1858 NULL ) )
1859 --Moved the dependant Lot attributes together
1860 --(For ease of fix for bug# 2760857)
1861 , NVL( I.LOT_SPLIT_ENABLED , M.LOT_SPLIT_ENABLED )
1862 , NVL( I.LOT_MERGE_ENABLED , M.LOT_MERGE_ENABLED )
1863 , NVL( I.LOT_TRANSLATE_ENABLED , M.LOT_TRANSLATE_ENABLED )
1864 --Added as part of 11.5.9 ENH
1865 , NVL( I.LOT_SUBSTITUTION_ENABLED , M.LOT_SUBSTITUTION_ENABLED )
1866 , NVL( I.MINIMUM_LICENSE_QUANTITY , M.MINIMUM_LICENSE_QUANTITY )
1867 , NVL( I.EAM_ACTIVITY_SOURCE_CODE , M.EAM_ACTIVITY_SOURCE_CODE )
1868 , NVL( I.IB_ITEM_INSTANCE_CLASS , M.IB_ITEM_INSTANCE_CLASS )
1869 , NVL( I.CONFIG_MODEL_TYPE , M.CONFIG_MODEL_TYPE )
1870 --2740503: Defaulting lifecycle-phase from master org.
1871 , NVL( I.LIFECYCLE_ID , M.LIFECYCLE_ID )
1872 , NVL( I.CURRENT_PHASE_ID , M.CURRENT_PHASE_ID )
1873 --Added as part of 11.5.10 ENH
1874 , NVL( I.TRACKING_QUANTITY_IND , M.TRACKING_QUANTITY_IND )
1875 , NVL( I.ONT_PRICING_QTY_SOURCE , M.ONT_PRICING_QTY_SOURCE )
1876 , NVL( I.SECONDARY_DEFAULT_IND , M.SECONDARY_DEFAULT_IND )
1877 , NVL( I.CONFIG_ORGS , M.CONFIG_ORGS )
1878 , NVL( I.CONFIG_MATCH , M.CONFIG_MATCH )
1879 , NVL(I.VMI_MINIMUM_UNITS , M.VMI_MINIMUM_UNITS )
1880 , NVL(I.VMI_MINIMUM_DAYS , M.VMI_MINIMUM_DAYS )
1881 , NVL(I.VMI_MAXIMUM_UNITS , M.VMI_MAXIMUM_UNITS )
1882 , NVL(I.VMI_MAXIMUM_DAYS , M.VMI_MAXIMUM_DAYS )
1883 , NVL(I.VMI_FIXED_ORDER_QUANTITY , M.VMI_FIXED_ORDER_QUANTITY )
1884 , NVL(I.SO_AUTHORIZATION_FLAG , M.SO_AUTHORIZATION_FLAG )
1885 , NVL(I.CONSIGNED_FLAG , M.CONSIGNED_FLAG )
1886 , NVL(I.ASN_AUTOEXPIRE_FLAG , M.ASN_AUTOEXPIRE_FLAG )
1887 , NVL(I.VMI_FORECAST_TYPE , M.VMI_FORECAST_TYPE )
1888 , NVL(I.FORECAST_HORIZON , M.FORECAST_HORIZON )
1889 , NVL(I.EXCLUDE_FROM_BUDGET_FLAG , M.EXCLUDE_FROM_BUDGET_FLAG )
1890 , NVL(I.DAYS_TGT_INV_SUPPLY , M.DAYS_TGT_INV_SUPPLY )
1891 , NVL(I.DAYS_TGT_INV_WINDOW , M.DAYS_TGT_INV_WINDOW )
1892 , NVL(I.DAYS_MAX_INV_SUPPLY , M.DAYS_MAX_INV_SUPPLY )
1893 , NVL(I.DAYS_MAX_INV_WINDOW , M.DAYS_MAX_INV_WINDOW )
1894 , NVL(I.DRP_PLANNED_FLAG , M.DRP_PLANNED_FLAG )
1895 , NVL(I.CRITICAL_COMPONENT_FLAG , M.CRITICAL_COMPONENT_FLAG )
1896 , NVL(I.CONTINOUS_TRANSFER , M.CONTINOUS_TRANSFER )
1897 , NVL(I.CONVERGENCE , M.CONVERGENCE )
1898 , NVL(I.DIVERGENCE , M.DIVERGENCE )
1899 /* Start Bug 3713912 */
1900 , NVL(I.LOT_DIVISIBLE_FLAG , M.LOT_DIVISIBLE_FLAG )
1901 , NVL(I.GRADE_CONTROL_FLAG , M.GRADE_CONTROL_FLAG )
1902 , NVL(I.DEFAULT_GRADE , M.DEFAULT_GRADE )
1903 , NVL(I.CHILD_LOT_FLAG , M.CHILD_LOT_FLAG )
1904 , NVL(I.PARENT_CHILD_GENERATION_FLAG , M.PARENT_CHILD_GENERATION_FLAG )
1905 , NVL(I.CHILD_LOT_PREFIX , M.CHILD_LOT_PREFIX )
1906 , NVL(I.CHILD_LOT_STARTING_NUMBER , M.CHILD_LOT_STARTING_NUMBER )
1907 , NVL(I.CHILD_LOT_VALIDATION_FLAG , M.CHILD_LOT_VALIDATION_FLAG )
1908 , NVL(I.COPY_LOT_ATTRIBUTE_FLAG , M.COPY_LOT_ATTRIBUTE_FLAG )
1909 --5349389, NVL(I.RECIPE_ENABLED_FLAG , M.RECIPE_ENABLED_FLAG )
1910 , NVL(I.PROCESS_QUALITY_ENABLED_FLAG , M.PROCESS_QUALITY_ENABLED_FLAG )
1911 --5349389, NVL(I.PROCESS_EXECUTION_ENABLED_FLAG , M.PROCESS_EXECUTION_ENABLED_FLAG )
1912 , NVL(I.PROCESS_COSTING_ENABLED_FLAG , M.PROCESS_COSTING_ENABLED_FLAG )
1913 , I.PROCESS_SUPPLY_SUBINVENTORY /* this should not default from master */
1914 , I.PROCESS_SUPPLY_LOCATOR_ID /* this should not default from master */
1915 , I.PROCESS_YIELD_SUBINVENTORY /* this should not default from master */
1916 , I.PROCESS_YIELD_LOCATOR_ID /* this should not default from master */
1917 , NVL(I.HAZARDOUS_MATERIAL_FLAG , M.HAZARDOUS_MATERIAL_FLAG )
1918 , NVL(I.CAS_NUMBER , M.CAS_NUMBER )
1919 , NVL(I.RETEST_INTERVAL , M.RETEST_INTERVAL )
1920 , NVL(I.EXPIRATION_ACTION_INTERVAL , M.EXPIRATION_ACTION_INTERVAL )
1921 , NVL(I.EXPIRATION_ACTION_CODE , M.EXPIRATION_ACTION_CODE )
1922 , NVL(I.MATURITY_DAYS , M.MATURITY_DAYS )
1923 , NVL(I.HOLD_DAYS , M.HOLD_DAYS )
1924 /* End Bug 3713912 */
1925 --R12 Enhancement
1926 , NVL(I.REPAIR_LEADTIME ,M.REPAIR_LEADTIME )
1927 , NVL(I.REPAIR_YIELD ,M.REPAIR_YIELD )
1928 , NVL(I.PREPOSITION_POINT ,M.PREPOSITION_POINT )
1929 , NVL(I.REPAIR_PROGRAM ,M.REPAIR_PROGRAM )
1930 , NVL(I.CHARGE_PERIODICITY_CODE ,M.CHARGE_PERIODICITY_CODE )
1931 , NVL(I.SUBCONTRACTING_COMPONENT ,M.SUBCONTRACTING_COMPONENT )
1932 , NVL(I.OUTSOURCED_ASSEMBLY ,M.OUTSOURCED_ASSEMBLY )
1933 /* R12 FPC Attributes */
1934 , NVL(I.GDSN_OUTBOUND_ENABLED_FLAG ,M.GDSN_OUTBOUND_ENABLED_FLAG )
1938 FROM
1935 , NVL(I.TRADE_ITEM_DESCRIPTOR ,M.TRADE_ITEM_DESCRIPTOR )
1936 , NVL(I.STYLE_ITEM_FLAG ,M.STYLE_ITEM_FLAG )
1937 , NVL(I.STYLE_ITEM_ID ,M.STYLE_ITEM_ID )
1939 mtl_system_items_tl MT
1940 , mtl_system_items_b M
1941 WHERE
1942 M.inventory_item_id = item_id
1943 AND M.organization_id = master_org_id
1944 AND MT.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
1945 AND MT.ORGANIZATION_ID = M.ORGANIZATION_ID
1946 AND MT.LANGUAGE = userenv('LANG')
1947 )
1948 WHERE I.rowid = p_rowid;
1949
1950 return(0);
1951
1952 EXCEPTION
1953
1954 WHEN LOGGING_ERR THEN
1955 return(dumm_status);
1956
1957 WHEN OTHERS THEN
1958 err_text := substr('INVPUTLI.assign_master_defaults ' || SQLERRM ,1,240 );
1959 return(SQLCODE);
1960
1961 END assign_master_defaults;
1962
1963 ----------------------------------------------------------------------
1964
1965 -- The following function assigns master item record defaults.
1966
1967 FUNCTION assign_item_defaults
1968 (
1969 item_id NUMBER,
1970 org_id NUMBER,
1971 status_default VARCHAR2,
1972 uom_default VARCHAR2,
1973 allow_item_desc_flag VARCHAR2,
1974 req_required_flag VARCHAR2,
1975 tax_flag VARCHAR2,
1976 err_text OUT NOCOPY VARCHAR2,
1977 xset_id IN NUMBER DEFAULT -999,
1978 p_rowid ROWID,
1979 v_receiving_flag VARCHAR2
1980 )
1981 RETURN INTEGER
1982 IS
1983
1984 CURSOR c_ego_intf_rows IS
1985 SELECT tl.column_value
1986 FROM ego_interface_tl tl, mtl_system_items_interface msii
1987 WHERE tl.unique_id = msii.transaction_id
1988 AND tl.set_process_id = msii.set_process_id
1989 AND UPPER(tl.table_name) = 'MTL_SYSTEM_ITEMS_INTERFACE'
1990 AND UPPER(tl.column_name) = 'DESCRIPTION'
1991 AND tl.language = USERENV('LANG')
1992 AND msii.rowid = p_rowid;
1993
1994 CURSOR c_item_desc_func (cp_catalog_group_id NUMBER)
1995 IS
1996 SELECT ITEM_DESC_GEN_METHOD
1997 FROM
1998 (
1999 SELECT ICC.ITEM_DESC_GEN_METHOD
2000 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
2001 WHERE ICC.ITEM_DESC_GEN_METHOD IS NOT NULL
2002 AND ICC.ITEM_DESC_GEN_METHOD <> 'I'
2003 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
2004 START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_catalog_group_id
2005 ORDER BY LEVEL ASC
2006 )
2007 WHERE ROWNUM = 1;
2008
2009 /* Cursor to retrieve default Trade Item Descriptor - 6126870 */
2010 CURSOR c_trade_item_default
2011 IS
2012 SELECT default_value FROM FND_DESCR_FLEX_COLUMN_USAGES
2013 WHERE application_id = 431
2014 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_MASTER_ITEMS'
2015 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Main'
2016 AND END_USER_COLUMN_NAME = 'Trade_Item_Descriptor';
2017
2018 rtn_status NUMBER := 1;
2019 temp_desc VARCHAR2(240);
2020
2021 l_Primary_Unit_of_Measure VARCHAR2(25);
2022 l_Primary_UOM_Code VARCHAR2(3);
2023
2024 NULL_DESC_ERR EXCEPTION;
2025 op_unit NUMBER;
2026 l_created_by NUMBER;
2027 l_rownum NUMBER;
2028 l_sysdate DATE := SYSDATE;
2029
2030 c_Equipment_Type_default CONSTANT NUMBER := 2;
2031 c_Web_Status_default CONSTANT VARCHAR2(30) := 'UNPUBLISHED';
2032 c_default_Lot_Status_id CONSTANT NUMBER := 1;
2033 c_default_Serial_Status_id CONSTANT NUMBER := 1;
2034
2035 l_style_item_flag VARCHAR2(1);
2036 l_style_item_id NUMBER;
2037 l_ret_status NUMBER;
2038 l_org_id NUMBER;
2039 l_revision VARCHAR2(1);
2040 l_desc_gen_method VARCHAR2(1) := 'N';
2041 l_item_catalog_group_id NUMBER;
2042
2043 l_process_ctrl VARCHAR2(100) := INV_EGO_REVISION_VALIDATE.GET_PROCESS_CONTROL;
2044 l_trade_item_default VARCHAR2(240);
2045 BEGIN
2046
2047 SELECT description,
2048 organization_id,
2049 style_item_flag,
2050 style_item_id,
2051 primary_unit_of_measure,
2052 primary_uom_code,
2053 item_catalog_group_id
2054 INTO temp_desc,
2055 l_org_id,
2056 l_style_item_flag,
2057 l_style_item_id,
2058 l_Primary_Unit_of_Measure,
2059 l_Primary_UOM_Code,
2060 l_item_catalog_group_id
2061 FROM mtl_system_items_interface I
2062 WHERE I.rowid = p_rowid;
2063
2064 SELECT starting_revision
2065 INTO l_revision
2066 FROM mtl_parameters
2067 WHERE organization_id = l_org_id;
2068
2069 IF (temp_desc IS NULL) THEN
2070 IF (l_item_catalog_group_id IS NOT NULL) THEN
2071 OPEN c_item_desc_func (cp_catalog_group_id => l_item_catalog_group_id);
2072 FETCH c_item_desc_func INTO l_desc_gen_method;
2073 CLOSE c_item_desc_func;
2074 END IF;
2075
2076 IF l_desc_gen_method <> 'F' THEN
2077 OPEN c_ego_intf_rows;
2081 raise NULL_DESC_ERR;
2078 FETCH c_ego_intf_rows INTO temp_desc;
2079
2080 IF c_ego_intf_rows%NOTFOUND OR temp_desc IS NULL THEN
2082 END IF;
2083
2084 CLOSE c_ego_intf_rows;
2085 END IF;
2086 END IF;
2087
2088 IF ( l_Primary_UOM_Code IS NULL ) THEN
2089 IF ( l_Primary_Unit_of_Measure IS NULL ) THEN
2090 IF (l_style_item_flag = 'N' ) THEN
2091 SELECT primary_unit_of_measure
2092 INTO l_Primary_Unit_Of_Measure
2093 FROM mtl_system_items_b
2094 WHERE inventory_item_id = l_style_item_id
2095 AND organization_id = l_org_id;
2096 ELSE
2097 l_Primary_Unit_of_Measure := uom_default;
2098 END IF;
2099 END IF;
2100 /*Bug: 4932464 Perf Issue replaced mtl_all_primary_uoms_vv with mtl_units_of_measure_tl
2101 SELECT uom_code
2102 INTO l_Primary_UOM_Code
2103 FROM mtl_all_primary_uoms_vv
2104 WHERE inventory_item_id = 0
2105 AND unit_of_measure = l_Primary_Unit_of_Measure;--Bug:3775854*/
2106
2107 SELECT uom_code
2108 INTO l_Primary_UOM_Code
2109 FROM mtl_units_of_measure_tl
2110 WHERE language = USERENV('LANG')
2111 AND unit_of_measure = l_Primary_Unit_of_Measure;
2112 -- AND unit_of_measure_tl = l_Primary_Unit_of_Measure; -Bug5260388
2113 --Bug 5192495 use unit_of_measure
2114 ELSE
2115 IF ( l_Primary_Unit_of_Measure IS NULL ) THEN
2116 SELECT unit_of_measure --Bug:3775854 --Bug 5192495 Use of unit_of_measure
2117 INTO l_Primary_Unit_of_Measure
2118 FROM mtl_units_of_measure
2119 WHERE uom_code = l_Primary_UOM_Code;
2120 END IF;
2121 END IF;
2122
2123 --Retrieve default value for Trade Item Descriptor Bug 6126870
2124 OPEN c_trade_item_default;
2125 FETCH c_trade_item_default INTO l_trade_item_default;
2126 CLOSE c_trade_item_default;
2127
2128 l_created_by := -1;
2129
2130 IF (l_style_item_flag = 'N' ) THEN
2131 UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
2132 SET (
2133 I.DESCRIPTION,
2134 I.LAST_UPDATED_BY,
2135 I.CREATED_BY,
2136 I.SUMMARY_FLAG,
2137 I.ENABLED_FLAG,
2138 I.ITEM_CATALOG_GROUP_ID,
2139 I.INVENTORY_ITEM_FLAG,
2140 I.WIP_SUPPLY_TYPE,
2141 I.AUTO_CREATED_CONFIG_FLAG,
2142 I.CYCLE_COUNT_ENABLED_FLAG,
2143 I.INTERNAL_ORDER_FLAG,
2144 I.INVENTORY_ITEM_STATUS_CODE,
2145 I.INVENTORY_PLANNING_CODE,
2146 I.MRP_PLANNING_CODE,
2147 I.INVENTORY_ASSET_FLAG,
2148 I.INVOICEABLE_ITEM_FLAG,
2149 I.ENG_ITEM_FLAG,
2150 I.BOM_ITEM_TYPE,
2151 I.COSTING_ENABLED_FLAG,
2152 I.CUSTOMER_ORDER_FLAG,
2153 I.ALLOWED_UNITS_LOOKUP_CODE,
2154 I.ALLOW_ITEM_DESC_UPDATE_FLAG,
2155 I.ATP_COMPONENTS_FLAG,
2156 I.ATP_FLAG,
2157 I.SERVICEABLE_PRODUCT_FLAG,
2158 I.SHELF_LIFE_CODE,
2159 I.SHIPPABLE_ITEM_FLAG,
2160 I.SO_TRANSACTIONS_FLAG,
2161 I.SERVICEABLE_COMPONENT_FLAG,
2162 I.REPLENISH_TO_ORDER_FLAG,
2163 I.RESERVABLE_TYPE,
2164 I.RESTRICT_LOCATORS_CODE,
2165 I.RESTRICT_SUBINVENTORIES_CODE,
2166 I.REVISION_QTY_CONTROL_CODE,
2167 I.RFQ_REQUIRED_FLAG,
2168 I.SERIAL_NUMBER_CONTROL_CODE,
2169 I.PREVENTIVE_MAINTENANCE_FLAG,
2170 I.SERV_BILLING_ENABLED_FLAG,
2171 I.PRIMARY_UOM_CODE,
2172 I.PRIMARY_UNIT_OF_MEASURE,
2173 I.PRORATE_SERVICE_FLAG,
2174 I.PURCHASING_ITEM_FLAG,
2175 I.RECEIPT_REQUIRED_FLAG,
2176 I.OUTSIDE_OPERATION_FLAG,
2177 I.PICK_COMPONENTS_FLAG,
2178 I.PLANNING_MAKE_BUY_CODE,
2179 I.PLANNING_TIME_FENCE_CODE,
2180 I.PLANNING_TIME_FENCE_DAYS,
2181 I.MUST_USE_APPROVED_VENDOR_FLAG,
2182 I.LOCATION_CONTROL_CODE,
2183 I.LOT_CONTROL_CODE,
2184 I.MRP_SAFETY_STOCK_CODE,
2185 I.revision,
2186 I.LAST_UPDATE_DATE,
2187 I.CREATION_DATE,
2188 I.SHIP_MODEL_COMPLETE_FLAG,
2189 I.TAXABLE_FLAG,
2190 I.MARKET_PRICE,
2191 I.LIST_PRICE_PER_UNIT,
2192 I.PRICE_TOLERANCE_PERCENT,
2193 I.SHELF_LIFE_DAYS,
2194 I.REPETITIVE_PLANNING_FLAG,
2195 I.ACCEPTABLE_RATE_DECREASE,
2196 I.ACCEPTABLE_RATE_INCREASE,
2197 I.POSTPROCESSING_LEAD_TIME,
2198 I.RETURN_INSPECTION_REQUIREMENT,
2199 I.CONTAINER_ITEM_FLAG,
2200 I.VEHICLE_ITEM_FLAG,
2201 I.RETURNABLE_FLAG,
2202 I.LEAD_TIME_LOT_SIZE,
2203 I.CHECK_SHORTAGES_FLAG,
2204 I.EFFECTIVITY_CONTROL,
2205 I.EQUIPMENT_TYPE,
2206 I.COMMS_NL_TRACKABLE_FLAG,
2207 I.WEB_STATUS,
2208 I.BULK_PICKED_FLAG,
2209 I.LOT_STATUS_ENABLED,
2210 I.DEFAULT_LOT_STATUS_ID,
2211 I.SERIAL_STATUS_ENABLED,
2212 I.DEFAULT_SERIAL_STATUS_ID,
2213 I.DUAL_UOM_CONTROL,
2214 I.LOT_SPLIT_ENABLED,
2215 I.LOT_MERGE_ENABLED,
2216 I.LOT_TRANSLATE_ENABLED,
2217 I.DEFAULT_SO_SOURCE_TYPE,
2221 I.DUAL_UOM_DEVIATION_HIGH,
2218 I.CREATE_SUPPLY_FLAG,
2219 I.TRACKING_QUANTITY_IND,
2220 I.ONT_PRICING_QTY_SOURCE,
2222 I.DUAL_UOM_DEVIATION_LOW,
2223 I.VMI_MINIMUM_UNITS,
2224 I.VMI_MINIMUM_DAYS,
2225 I.VMI_MAXIMUM_UNITS,
2226 I.VMI_MAXIMUM_DAYS,
2227 I.VMI_FIXED_ORDER_QUANTITY,
2228 I.SO_AUTHORIZATION_FLAG,
2229 I.CONSIGNED_FLAG,
2230 I.ASN_AUTOEXPIRE_FLAG,
2231 I.VMI_FORECAST_TYPE,
2232 I.FORECAST_HORIZON,
2233 I.EXCLUDE_FROM_BUDGET_FLAG,
2234 I.DAYS_TGT_INV_SUPPLY,
2235 I.DAYS_TGT_INV_WINDOW,
2236 I.DAYS_MAX_INV_SUPPLY,
2237 I.DAYS_MAX_INV_WINDOW,
2238 I.DRP_PLANNED_FLAG,
2239 I.CRITICAL_COMPONENT_FLAG,
2240 I.CONTINOUS_TRANSFER,
2241 I.CONVERGENCE,
2242 I.DIVERGENCE,
2243 I.LOT_DIVISIBLE_FLAG,
2244 I.GRADE_CONTROL_FLAG,
2245 I.DEFAULT_GRADE,
2246 I.CHILD_LOT_FLAG,
2247 I.PARENT_CHILD_GENERATION_FLAG,
2248 I.CHILD_LOT_PREFIX,
2249 I.CHILD_LOT_STARTING_NUMBER,
2250 I.CHILD_LOT_VALIDATION_FLAG,
2251 I.COPY_LOT_ATTRIBUTE_FLAG,
2252 I.PROCESS_QUALITY_ENABLED_FLAG,
2253 I.PROCESS_COSTING_ENABLED_FLAG,
2254 I.PROCESS_SUPPLY_SUBINVENTORY,
2255 I.PROCESS_SUPPLY_LOCATOR_ID,
2256 I.PROCESS_YIELD_SUBINVENTORY,
2257 I.PROCESS_YIELD_LOCATOR_ID,
2258 I.HAZARDOUS_MATERIAL_FLAG,
2259 I.CAS_NUMBER,
2260 I.RETEST_INTERVAL,
2261 I.EXPIRATION_ACTION_INTERVAL,
2262 I.EXPIRATION_ACTION_CODE,
2263 I.MATURITY_DAYS,
2264 I.HOLD_DAYS,
2265 /* End Bug 3713912 */
2266 --R12 Enhancement
2267 I.PREPOSITION_POINT,
2268 I.REPAIR_PROGRAM,
2269 I.OUTSOURCED_ASSEMBLY,
2270 /* Bug 5452757 */
2271 I.COLLATERAL_FLAG,
2272 I.EVENT_FLAG,
2273 I.ELECTRONIC_FLAG,
2274 I.DOWNLOADABLE_FLAG,
2275 I.INDIVISIBLE_FLAG,
2276 /* End of Bug 5452757 */
2277 I.GDSN_OUTBOUND_ENABLED_FLAG,
2278 I.TRADE_ITEM_DESCRIPTOR
2279 )
2280 = ( SELECT
2281 NVL(I.DESCRIPTION, temp_desc),
2282 l_created_by,
2283 l_created_by,
2284 NVL(I.SUMMARY_FLAG, 'N'), --Bug: 4918974
2285 NVL(I.ENABLED_FLAG, 'Y'),
2286 NVL(I.ITEM_CATALOG_GROUP_ID, M.ITEM_CATALOG_GROUP_ID),
2287 NVL (I.INVENTORY_ITEM_FLAG, M.INVENTORY_ITEM_FLAG),
2288 NVL (I.WIP_SUPPLY_TYPE, M.WIP_SUPPLY_TYPE),
2289 NVL(I.AUTO_CREATED_CONFIG_FLAG, M.AUTO_CREATED_CONFIG_FLAG),
2290 NVL(I.CYCLE_COUNT_ENABLED_FLAG,M.CYCLE_COUNT_ENABLED_FLAG),
2291 NVL(I.INTERNAL_ORDER_FLAG,M.INTERNAL_ORDER_FLAG),
2292 NVL(I.INVENTORY_ITEM_STATUS_CODE, M.INVENTORY_ITEM_STATUS_CODE),
2293 NVL(I.INVENTORY_PLANNING_CODE, M.INVENTORY_PLANNING_CODE),
2294 NVL(I.MRP_PLANNING_CODE, M.MRP_PLANNING_CODE),
2295 NVL(I.INVENTORY_ASSET_FLAG, M.INVENTORY_ASSET_FLAG),
2296 NVL(I.INVOICEABLE_ITEM_FLAG, M.INVOICEABLE_ITEM_FLAG),
2297 NVL(I.ENG_ITEM_FLAG, M.ENG_ITEM_FLAG),
2298 NVL(I.BOM_ITEM_TYPE, M.BOM_ITEM_TYPE),
2299 NVL(I.COSTING_ENABLED_FLAG, M.COSTING_ENABLED_FLAG),
2300 NVL(I.CUSTOMER_ORDER_FLAG, M.CUSTOMER_ORDER_FLAG),
2301 NVL(I.ALLOWED_UNITS_LOOKUP_CODE, M.ALLOWED_UNITS_LOOKUP_CODE),
2302 NVL(I.ALLOW_ITEM_DESC_UPDATE_FLAG, M.ALLOW_ITEM_DESC_UPDATE_FLAG),
2303 NVL(I.ATP_COMPONENTS_FLAG, M.ATP_COMPONENTS_FLAG),
2304 NVL(I.ATP_FLAG, M.ATP_FLAG),
2305 NVL(I.SERVICEABLE_PRODUCT_FLAG, M.SERVICEABLE_PRODUCT_FLAG),
2306 NVL(I.SHELF_LIFE_CODE,M.SHELF_LIFE_CODE),
2307 NVL(I.SHIPPABLE_ITEM_FLAG, M.SHIPPABLE_ITEM_FLAG),
2308 NVL(I.SO_TRANSACTIONS_FLAG, M.SO_TRANSACTIONS_FLAG),
2309 NVL(I.SERVICEABLE_COMPONENT_FLAG, M.SERVICEABLE_COMPONENT_FLAG),
2310 NVL(I.REPLENISH_TO_ORDER_FLAG, M.REPLENISH_TO_ORDER_FLAG),
2311 NVL(I.RESERVABLE_TYPE, M.RESERVABLE_TYPE),
2312 NVL(I.RESTRICT_LOCATORS_CODE, M.RESTRICT_LOCATORS_CODE),
2313 NVL(I.RESTRICT_SUBINVENTORIES_CODE, M.RESTRICT_SUBINVENTORIES_CODE),
2314 NVL(I.REVISION_QTY_CONTROL_CODE, M.REVISION_QTY_CONTROL_CODE),
2315 NVL(I.RFQ_REQUIRED_FLAG, M.RFQ_REQUIRED_FLAG),
2316 NVL(I.SERIAL_NUMBER_CONTROL_CODE, M.SERIAL_NUMBER_CONTROL_CODE),
2317 NVL(I.PREVENTIVE_MAINTENANCE_FLAG, M.PREVENTIVE_MAINTENANCE_FLAG),
2318 NVL(I.SERV_BILLING_ENABLED_FLAG, M.SERV_BILLING_ENABLED_FLAG),
2319 l_primary_uom_code,
2320 l_primary_unit_of_measure,
2321 NVL(I.PRORATE_SERVICE_FLAG, M.PRORATE_SERVICE_FLAG),
2322 NVL(I.PURCHASING_ITEM_FLAG, M.PURCHASING_ITEM_FLAG),
2323 NVL(I.RECEIPT_REQUIRED_FLAG, M.RECEIPT_REQUIRED_FLAG),
2324 NVL(I.OUTSIDE_OPERATION_FLAG, M.OUTSIDE_OPERATION_FLAG),
2325 NVL(I.PICK_COMPONENTS_FLAG, M.PICK_COMPONENTS_FLAG),
2326 NVL(I.PLANNING_MAKE_BUY_CODE, M.PLANNING_MAKE_BUY_CODE),
2327 NVL(I.PLANNING_TIME_FENCE_CODE, M.PLANNING_TIME_FENCE_CODE),
2328 NVL(I.PLANNING_TIME_FENCE_DAYS, M.PLANNING_TIME_FENCE_DAYS),
2329 NVL(I.MUST_USE_APPROVED_VENDOR_FLAG, M.MUST_USE_APPROVED_VENDOR_FLAG),
2330 NVL(I.LOCATION_CONTROL_CODE, M.LOCATION_CONTROL_CODE),
2331 NVL(I.LOT_CONTROL_CODE, M.LOT_CONTROL_CODE),
2332 NVL(I.MRP_SAFETY_STOCK_CODE, M.MRP_SAFETY_STOCK_CODE),
2333 NVL(I.REVISION,l_revision),
2334 NVL(I.LAST_UPDATE_DATE, l_sysdate),
2335 NVL(I.CREATION_DATE, l_sysdate),
2339 NVL(I.LIST_PRICE_PER_UNIT, M.LIST_PRICE_PER_UNIT),
2336 NVL(I.SHIP_MODEL_COMPLETE_FLAG, M.SHIP_MODEL_COMPLETE_FLAG),
2337 NVL(I.TAXABLE_FLAG, M.TAXABLE_FLAG),
2338 NVL(I.MARKET_PRICE, M.MARKET_PRICE),
2340 NVL(I.PRICE_TOLERANCE_PERCENT, M.PRICE_TOLERANCE_PERCENT),
2341 NVL(I.SHELF_LIFE_DAYS, M.SHELF_LIFE_DAYS),
2342 NVL(I.REPETITIVE_PLANNING_FLAG, M.REPETITIVE_PLANNING_FLAG),
2343 NVL(I.ACCEPTABLE_RATE_DECREASE, M.ACCEPTABLE_RATE_DECREASE),
2344 NVL(I.ACCEPTABLE_RATE_INCREASE, M.ACCEPTABLE_RATE_INCREASE),
2345 NVL(I.POSTPROCESSING_LEAD_TIME, M.POSTPROCESSING_LEAD_TIME),
2346 NVL(I.RETURN_INSPECTION_REQUIREMENT, M.RETURN_INSPECTION_REQUIREMENT),
2347 NVL(I.CONTAINER_ITEM_FLAG, M.CONTAINER_ITEM_FLAG),
2348 NVL(I.VEHICLE_ITEM_FLAG, M.VEHICLE_ITEM_FLAG),
2349 NVL(I.RETURNABLE_FLAG, M.RETURNABLE_FLAG),
2350 NVL(I.LEAD_TIME_LOT_SIZE, M.LEAD_TIME_LOT_SIZE),
2351 NVL(I.CHECK_SHORTAGES_FLAG, M.CHECK_SHORTAGES_FLAG),
2352 NVL(I.EFFECTIVITY_CONTROL, M.EFFECTIVITY_CONTROL),
2353 NVL(I.EQUIPMENT_TYPE, M.EQUIPMENT_TYPE),
2354 NVL(I.COMMS_NL_TRACKABLE_FLAG, M.COMMS_NL_TRACKABLE_FLAG),
2355 NVL(I.WEB_STATUS, M.WEB_STATUS),
2356 NVL(I.BULK_PICKED_FLAG, M.BULK_PICKED_FLAG),
2357 NVL(I.LOT_STATUS_ENABLED, M.LOT_STATUS_ENABLED),
2358 NVL(I.DEFAULT_LOT_STATUS_ID, M.DEFAULT_LOT_STATUS_ID),
2359 NVL(I.SERIAL_STATUS_ENABLED, M.SERIAL_STATUS_ENABLED),
2360 NVL(I.DEFAULT_SERIAL_STATUS_ID, M.DEFAULT_SERIAL_STATUS_ID),
2361 NVL(I.DUAL_UOM_CONTROL, M.DUAL_UOM_CONTROL),
2362 NVL(I.LOT_SPLIT_ENABLED, M.LOT_SPLIT_ENABLED),
2363 NVL(I.LOT_MERGE_ENABLED, M.LOT_MERGE_ENABLED),
2364 NVL(I.LOT_TRANSLATE_ENABLED, M.LOT_TRANSLATE_ENABLED),
2365 NVL(I.DEFAULT_SO_SOURCE_TYPE, M.DEFAULT_SO_SOURCE_TYPE),
2366 NVL(I.CREATE_SUPPLY_FLAG, M.CREATE_SUPPLY_FLAG),
2367 NVL(I.TRACKING_QUANTITY_IND, M.TRACKING_QUANTITY_IND),
2368 NVL(I.ONT_PRICING_QTY_SOURCE, M.ONT_PRICING_QTY_SOURCE),
2369 NVL(I.DUAL_UOM_DEVIATION_HIGH, M.DUAL_UOM_DEVIATION_HIGH),
2370 NVL(I.DUAL_UOM_DEVIATION_LOW, M.DUAL_UOM_DEVIATION_LOW),
2371 NVL(I.VMI_MINIMUM_UNITS, M.VMI_MINIMUM_UNITS),
2372 NVL(I.VMI_MINIMUM_DAYS, M.VMI_MINIMUM_DAYS),
2373 NVL(I.VMI_MAXIMUM_UNITS, M.VMI_MAXIMUM_UNITS),
2374 NVL(I.VMI_MAXIMUM_DAYS, M.VMI_MAXIMUM_DAYS),
2375 NVL(I.VMI_FIXED_ORDER_QUANTITY, M.VMI_FIXED_ORDER_QUANTITY),
2376 NVL(I.SO_AUTHORIZATION_FLAG, M.SO_AUTHORIZATION_FLAG),
2377 NVL(I.CONSIGNED_FLAG, M.CONSIGNED_FLAG),
2378 NVL(I.ASN_AUTOEXPIRE_FLAG, M.ASN_AUTOEXPIRE_FLAG),
2379 NVL(I.VMI_FORECAST_TYPE, M.VMI_FORECAST_TYPE),
2380 NVL(I.FORECAST_HORIZON, M.FORECAST_HORIZON),
2381 NVL(I.EXCLUDE_FROM_BUDGET_FLAG, M.EXCLUDE_FROM_BUDGET_FLAG),
2382 NVL(I.DAYS_TGT_INV_SUPPLY, M.DAYS_TGT_INV_SUPPLY),
2383 NVL(I.DAYS_TGT_INV_WINDOW, M.DAYS_TGT_INV_WINDOW),
2384 NVL(I.DAYS_MAX_INV_SUPPLY, M.DAYS_MAX_INV_SUPPLY),
2385 NVL(I.DAYS_MAX_INV_WINDOW, M.DAYS_MAX_INV_WINDOW),
2386 NVL(I.DRP_PLANNED_FLAG, M.DRP_PLANNED_FLAG),
2387 NVL(I.CRITICAL_COMPONENT_FLAG, M.CRITICAL_COMPONENT_FLAG),
2388 NVL(I.CONTINOUS_TRANSFER, M.CONTINOUS_TRANSFER),
2389 NVL(I.CONVERGENCE, M.CONVERGENCE),
2390 NVL(I.DIVERGENCE, M.DIVERGENCE),
2391 NVL(I.LOT_DIVISIBLE_FLAG, M.LOT_DIVISIBLE_FLAG),
2392 NVL(I.GRADE_CONTROL_FLAG, M.GRADE_CONTROL_FLAG),
2393 NVL(I.DEFAULT_GRADE, M.DEFAULT_GRADE),
2394 NVL(I.CHILD_LOT_FLAG, M.CHILD_LOT_FLAG),
2395 NVL(I.PARENT_CHILD_GENERATION_FLAG, M.PARENT_CHILD_GENERATION_FLAG),
2396 NVL(I.CHILD_LOT_PREFIX, M.CHILD_LOT_PREFIX),
2397 NVL(I.CHILD_LOT_STARTING_NUMBER, M.CHILD_LOT_STARTING_NUMBER),
2398 NVL(I.CHILD_LOT_VALIDATION_FLAG, M.CHILD_LOT_VALIDATION_FLAG),
2399 NVL(I.COPY_LOT_ATTRIBUTE_FLAG, M.COPY_LOT_ATTRIBUTE_FLAG),
2400 NVL(I.PROCESS_QUALITY_ENABLED_FLAG, M.PROCESS_QUALITY_ENABLED_FLAG),
2401 NVL(I.PROCESS_COSTING_ENABLED_FLAG, M.PROCESS_COSTING_ENABLED_FLAG),
2402 NVL(I.PROCESS_SUPPLY_SUBINVENTORY, M.PROCESS_SUPPLY_SUBINVENTORY),
2403 NVL(I.PROCESS_SUPPLY_LOCATOR_ID, M.PROCESS_SUPPLY_LOCATOR_ID),
2404 NVL(I.PROCESS_YIELD_SUBINVENTORY, M.PROCESS_YIELD_SUBINVENTORY),
2405 NVL(I.PROCESS_YIELD_LOCATOR_ID, M.PROCESS_YIELD_LOCATOR_ID),
2406 NVL(I.HAZARDOUS_MATERIAL_FLAG, M.HAZARDOUS_MATERIAL_FLAG),
2407 NVL(I.CAS_NUMBER, M.CAS_NUMBER),
2408 NVL(I.RETEST_INTERVAL, M.RETEST_INTERVAL),
2409 NVL(I.EXPIRATION_ACTION_INTERVAL, M.EXPIRATION_ACTION_INTERVAL),
2410 NVL(I.EXPIRATION_ACTION_CODE, M.EXPIRATION_ACTION_CODE),
2411 NVL(I.MATURITY_DAYS, M.MATURITY_DAYS),
2412 NVL(I.HOLD_DAYS, M.HOLD_DAYS),
2413 NVL(I.PREPOSITION_POINT, M.PREPOSITION_POINT),
2414 NVL(I.REPAIR_PROGRAM, M.REPAIR_PROGRAM),
2415 NVL(I.OUTSOURCED_ASSEMBLY, M.OUTSOURCED_ASSEMBLY),
2416 NVL(I.COLLATERAL_FLAG, M.COLLATERAL_FLAG),
2417 NVL(I.EVENT_FLAG, M.EVENT_FLAG),
2418 NVL(I.ELECTRONIC_FLAG, M.ELECTRONIC_FLAG),
2419 NVL(I.DOWNLOADABLE_FLAG, M.DOWNLOADABLE_FLAG),
2420 NVL(I.INDIVISIBLE_FLAG, M.INDIVISIBLE_FLAG),
2421 NVL(I.GDSN_OUTBOUND_ENABLED_FLAG,M.GDSN_OUTBOUND_ENABLED_FLAG),
2422 NVL(I.TRADE_ITEM_DESCRIPTOR, M.TRADE_ITEM_DESCRIPTOR)
2423 FROM mtl_system_items M
2424 WHERE M.inventory_item_id = l_style_item_id
2425 AND M.organization_id = l_org_id
2426 )
2427 WHERE I.ROWID = p_rowid;
2428 l_ret_status := 0;
2429 ELSE
2430 UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
2431 SET
2432 I.LAST_UPDATED_BY = org_id,
2433 I.CREATED_BY = l_created_by,
2434 I.SUMMARY_FLAG = NVL(I.SUMMARY_FLAG, 'N'), --Bug: 4918974
2435 I.ENABLED_FLAG = NVL(I.ENABLED_FLAG, 'Y'),
2436
2440 -- This will be done in INVPULI4.assign_status_attributes.
2437 --
2438 -- Do not assign here defaults for
2439 -- STOCK_ENABLED_FLAG and MTL_TRANSACTIONS_ENABLED_FLAG.
2441 -- I.STOCK_ENABLED_FLAG = DECODE(I.STOCK_ENABLED_FLAG,NULL, 'N', I.STOCK_ENABLED_FLAG)
2442 -- I.MTL_TRANSACTIONS_ENABLED_FLAG = DECODE(I.MTL_TRANSACTIONS_ENABLED_FLAG,NULL, 'N', I.MTL_TRANSACTIONS_ENABLED_FLAG)
2443
2444 -- 3410315: Status controlled attributes defaulting in INVPUL4B.
2445 -- I.INTERNAL_ORDER_ENABLED_FLAG = DECODE(I.INTERNAL_ORDER_ENABLED_FLAG, NULL, 'N',I.INTERNAL_ORDER_ENABLED_FLAG),
2446 -- I.INVOICE_ENABLED_FLAG = DECODE(I.INVOICE_ENABLED_FLAG, NULL, 'N', I.INVOICE_ENABLED_FLAG),
2447 -- I.BOM_ENABLED_FLAG = DECODE(I.BOM_ENABLED_FLAG, NULL, 'N', I.BOM_ENABLED_FLAG),
2448 -- I.BUILD_IN_WIP_FLAG = DECODE(I.BUILD_IN_WIP_FLAG,NULL,'N',I.BUILD_IN_WIP_FLAG),
2449 -- I.CUSTOMER_ORDER_ENABLED_FLAG = DECODE(I.CUSTOMER_ORDER_ENABLED_FLAG, NULL, 'N',I.CUSTOMER_ORDER_ENABLED_FLAG),
2450 -- I.PURCHASING_ENABLED_FLAG = DECODE(I.PURCHASING_ENABLED_FLAG,NULL,'N',I.PURCHASING_ENABLED_FLAG),
2451
2452 I.INVENTORY_ITEM_FLAG = NVL(I.INVENTORY_ITEM_FLAG, 'N'),
2453 I.WIP_SUPPLY_TYPE = NVL(I.WIP_SUPPLY_TYPE, 1),
2454 I.AUTO_CREATED_CONFIG_FLAG = NVL(I.AUTO_CREATED_CONFIG_FLAG, 'N'),
2455 I.CYCLE_COUNT_ENABLED_FLAG = DECODE(I.CYCLE_COUNT_ENABLED_FLAG,NULL,'N',I.CYCLE_COUNT_ENABLED_FLAG),
2456 I.INTERNAL_ORDER_FLAG = DECODE(I.INTERNAL_ORDER_FLAG, NULL,'N', I.INTERNAL_ORDER_FLAG),
2457 I.INVENTORY_ITEM_STATUS_CODE = DECODE(I.INVENTORY_ITEM_STATUS_CODE, NULL, status_default, I.INVENTORY_ITEM_STATUS_CODE),
2458 I.INVENTORY_PLANNING_CODE = DECODE(I.INVENTORY_PLANNING_CODE, NULL, 6, I.INVENTORY_PLANNING_CODE),
2459 I.MRP_PLANNING_CODE = DECODE(I.MRP_PLANNING_CODE, NULL, 6, I.MRP_PLANNING_CODE),
2460 I.INVENTORY_ASSET_FLAG = DECODE(I.INVENTORY_ASSET_FLAG, NULL, 'N', I.INVENTORY_ASSET_FLAG),
2461 I.INVOICEABLE_ITEM_FLAG = DECODE(I.INVOICEABLE_ITEM_FLAG, NULL, 'N', I.INVOICEABLE_ITEM_FLAG),
2462 --5665680 : When imported from XL, item should be eng item only.
2463 I.ENG_ITEM_FLAG = DECODE(I.ENG_ITEM_FLAG, NULL, DECODE(l_process_ctrl,'EGO_ITEM_BULKLOAD','Y','N'),I.ENG_ITEM_FLAG),
2464 -- Remove the defaulting of the expense_billable_flag to 'N' for bug 2886456- anmurali
2465 -- I.EXPENSE_BILLABLE_FLAG = DECODE(I.EXPENSE_BILLABLE_FLAG,NULL,'N',I.EXPENSE_BILLABLE_FLAG),
2466 I.BOM_ITEM_TYPE = DECODE(I.BOM_ITEM_TYPE, NULL, 4, I.BOM_ITEM_TYPE),
2467 I.COSTING_ENABLED_FLAG = DECODE(I.COSTING_ENABLED_FLAG,NULL,'N',I.COSTING_ENABLED_FLAG),
2468 I.CUSTOMER_ORDER_FLAG = DECODE(I.CUSTOMER_ORDER_FLAG,NULL,'N',I.CUSTOMER_ORDER_FLAG),
2469 I.ALLOWED_UNITS_LOOKUP_CODE = DECODE(I.ALLOWED_UNITS_LOOKUP_CODE,NULL,3,I.ALLOWED_UNITS_LOOKUP_CODE),
2470 I.ALLOW_ITEM_DESC_UPDATE_FLAG = DECODE(I.ALLOW_ITEM_DESC_UPDATE_FLAG,NULL,allow_item_desc_flag, ALLOW_ITEM_DESC_UPDATE_FLAG),
2471 I.ATP_COMPONENTS_FLAG = DECODE(I.ATP_COMPONENTS_FLAG,NULL,'N',I.ATP_COMPONENTS_FLAG),
2472 I.ATP_FLAG = DECODE(I.ATP_FLAG,NULL,'N',I.ATP_FLAG),
2473 -- Remove the defaulting of the time_billable_flag to 'N' for bug 2886456- anmurali
2474 -- I.TIME_BILLABLE_FLAG = DECODE(I.TIME_BILLABLE_FLAG,NULL,'N',I.TIME_BILLABLE_FLAG),
2475 I.SERVICEABLE_PRODUCT_FLAG = DECODE(I.SERVICEABLE_PRODUCT_FLAG,NULL,'N',I.SERVICEABLE_PRODUCT_FLAG),
2476 I.SHELF_LIFE_CODE = DECODE(I.SHELF_LIFE_CODE,NULL,1,I.SHELF_LIFE_CODE),
2477 I.SHIPPABLE_ITEM_FLAG = DECODE(I.INTERNAL_ORDER_FLAG,'Y','Y', NVL(I.SHIPPABLE_ITEM_FLAG,'N')),
2478 I.SO_TRANSACTIONS_FLAG = DECODE(I.SO_TRANSACTIONS_FLAG,NULL,'N',I.SO_TRANSACTIONS_FLAG),
2479 I.SERVICEABLE_COMPONENT_FLAG = DECODE(I.SERVICEABLE_COMPONENT_FLAG,NULL,'N',I.SERVICEABLE_COMPONENT_FLAG),
2480 I.REPLENISH_TO_ORDER_FLAG = DECODE(I.REPLENISH_TO_ORDER_FLAG,NULL,'N',I.REPLENISH_TO_ORDER_FLAG),
2481 I.RESERVABLE_TYPE = DECODE(I.RESERVABLE_TYPE, NULL, 1, I.RESERVABLE_TYPE),
2482 I.RESTRICT_LOCATORS_CODE = DECODE(I.RESTRICT_LOCATORS_CODE,NULL,2,I.RESTRICT_LOCATORS_CODE),
2483 I.RESTRICT_SUBINVENTORIES_CODE = DECODE(I.RESTRICT_SUBINVENTORIES_CODE, NULL, 2,I.RESTRICT_SUBINVENTORIES_CODE),
2484 I.REVISION_QTY_CONTROL_CODE = DECODE(I.REVISION_QTY_CONTROL_CODE,NULL,1,I.REVISION_QTY_CONTROL_CODE),
2485 I.RFQ_REQUIRED_FLAG = DECODE(I.RFQ_REQUIRED_FLAG,NULL,req_required_flag,I.RFQ_REQUIRED_FLAG),
2486 I.SERIAL_NUMBER_CONTROL_CODE = DECODE(I.SERIAL_NUMBER_CONTROL_CODE,NULL,1,I.SERIAL_NUMBER_CONTROL_CODE),
2487 I.PREVENTIVE_MAINTENANCE_FLAG = DECODE(I.PREVENTIVE_MAINTENANCE_FLAG,NULL,'N', I.PREVENTIVE_MAINTENANCE_FLAG),
2488 -- Anmurali - As in forms even while creating an item through IOI the SERV_BILLING_ENABLED_FLAG must default to 'N'
2489 I.SERV_BILLING_ENABLED_FLAG = DECODE(I.SERV_BILLING_ENABLED_FLAG,NULL,'N',I.SERV_BILLING_ENABLED_FLAG),
2490 -- Primary Unit of Measure is now maintained via the PRIMARY_UOM_CODE column.
2491 I.PRIMARY_UOM_CODE = l_Primary_UOM_Code,
2492 I.PRIMARY_UNIT_OF_MEASURE = l_Primary_Unit_of_Measure,
2493 I.PRORATE_SERVICE_FLAG = DECODE(I.PRORATE_SERVICE_FLAG,NULL,'N',I.PRORATE_SERVICE_FLAG),
2494 I.PURCHASING_ITEM_FLAG = DECODE(I.PURCHASING_ITEM_FLAG,NULL,'N',I.PURCHASING_ITEM_FLAG),
2495 I.RECEIPT_REQUIRED_FLAG = DECODE(I.RECEIPT_REQUIRED_FLAG,NULL,v_receiving_flag,I.RECEIPT_REQUIRED_FLAG),
2496 I.OUTSIDE_OPERATION_FLAG = DECODE(I.OUTSIDE_OPERATION_FLAG,NULL,'N',I.OUTSIDE_OPERATION_FLAG),
2497 I.PICK_COMPONENTS_FLAG = DECODE(I.PICK_COMPONENTS_FLAG,NULL,'N',I.PICK_COMPONENTS_FLAG),
2501 I.PLANNING_TIME_FENCE_DAYS = DECODE(I.PLANNING_TIME_FENCE_CODE,
2498 I.PLANNING_MAKE_BUY_CODE = DECODE(I.PLANNING_MAKE_BUY_CODE,NULL,2,I.PLANNING_MAKE_BUY_CODE),
2499 I.PLANNING_TIME_FENCE_CODE = DECODE(I.PLANNING_TIME_FENCE_CODE,NULL,4,I.PLANNING_TIME_FENCE_CODE),
2500 -- I.PLANNING_TIME_FENCE_DAYS = DECODE(I.PLANNING_TIME_FENCE_DAYS,NULL,1,I.PLANNING_TIME_FENCE_DAYS),
2502 NULL,DECODE(I.PLANNING_TIME_FENCE_DAYS, NULL,1,I.PLANNING_TIME_FENCE_DAYS),--Bug:3839935
2503 4,DECODE(I.PLANNING_TIME_FENCE_DAYS, NULL, 1, I.PLANNING_TIME_FENCE_DAYS),
2504 I.PLANNING_TIME_FENCE_DAYS ),
2505 I.MUST_USE_APPROVED_VENDOR_FLAG = DECODE(I.MUST_USE_APPROVED_VENDOR_FLAG,NULL,'N',
2506 I.MUST_USE_APPROVED_VENDOR_FLAG),
2507 I.LOCATION_CONTROL_CODE = DECODE(I.LOCATION_CONTROL_CODE, NULL, 1, I.LOCATION_CONTROL_CODE),
2508 I.LOT_CONTROL_CODE = DECODE(I.LOT_CONTROL_CODE, NULL, 1, I.LOT_CONTROL_CODE),
2509 I.MRP_SAFETY_STOCK_CODE = DECODE(I.MRP_SAFETY_STOCK_CODE,NULL,1,I.MRP_SAFETY_STOCK_CODE),
2510 I.revision = ( SELECT DECODE(I.revision, NULL,M.STARTING_REVISION, I.revision)
2511 FROM MTL_PARAMETERS M
2512 WHERE M.organization_id = org_id ),
2513 I.LAST_UPDATE_DATE = NVL(I.LAST_UPDATE_DATE,l_sysdate),
2514 I.CREATION_DATE = NVL(I.CREATION_DATE,l_sysdate),
2515 I.SHIP_MODEL_COMPLETE_FLAG = DECODE(I.SHIP_MODEL_COMPLETE_FLAG, NULL, 'N', I.SHIP_MODEL_COMPLETE_FLAG ),
2516 I.TAXABLE_FLAG = DECODE(I.TAXABLE_FLAG, NULL, tax_flag, I.TAXABLE_FLAG),
2517 I.MARKET_PRICE = DECODE(I.MARKET_PRICE, NULL, NULL, I.MARKET_PRICE),
2518 I.LIST_PRICE_PER_UNIT = DECODE(I.LIST_PRICE_PER_UNIT, NULL, NULL, I.LIST_PRICE_PER_UNIT),
2519 -- I.PRICE_TOLERANCE_PERCENT = DECODE(I.PRICE_TOLERANCE_PERCENT, NULL, 0, I.PRICE_TOLERANCE_PERCENT),
2520 I.PRICE_TOLERANCE_PERCENT = I.PRICE_TOLERANCE_PERCENT,
2521 I.SHELF_LIFE_DAYS = DECODE(I.SHELF_LIFE_DAYS, NULL, 0 , I.SHELF_LIFE_DAYS),
2522 I.REPETITIVE_PLANNING_FLAG = DECODE(I.REPETITIVE_PLANNING_FLAG, NULL, 'N', I.REPETITIVE_PLANNING_FLAG),
2523 I.ACCEPTABLE_RATE_DECREASE = DECODE(I.ACCEPTABLE_RATE_DECREASE, NULL, 0, I.ACCEPTABLE_RATE_DECREASE),
2524 I.ACCEPTABLE_RATE_INCREASE = DECODE(I.ACCEPTABLE_RATE_INCREASE, NULL, 0, I.ACCEPTABLE_RATE_INCREASE),
2525 I.POSTPROCESSING_LEAD_TIME = DECODE(I.POSTPROCESSING_LEAD_TIME, NULL, 0, I.POSTPROCESSING_LEAD_TIME),
2526 I.RETURN_INSPECTION_REQUIREMENT = DECODE(I.RETURN_INSPECTION_REQUIREMENT, NULL, 2,
2527 I.RETURN_INSPECTION_REQUIREMENT),
2528 I.CONTAINER_ITEM_FLAG = DECODE(I.CONTAINER_ITEM_FLAG, NULL, 'N', I.CONTAINER_ITEM_FLAG),
2529 I.VEHICLE_ITEM_FLAG = DECODE(I.VEHICLE_ITEM_FLAG, NULL, 'N', I.VEHICLE_ITEM_FLAG),
2530 -- I.SERVICE_DURATION = DECODE(I.SERVICE_DURATION, NULL, 0, I.SERVICE_DURATION), commented for bug 3048968
2531 --Bug: 2696647 For Contract Items like SERVICE,WARRANTY and USAGE returnable_flag = 'N'
2532 I.RETURNABLE_FLAG = DECODE(I.RETURNABLE_FLAG,NULL,DECODE(I.INVENTORY_ITEM_FLAG,'Y','Y','N'),I.RETURNABLE_FLAG), --Added for bug#929335
2533 -- I.LEAD_TIME_LOT_SIZE = nvl(I.LEAD_TIME_LOT_SIZE,I.STD_LOT_SIZE), -- Added for bug# 900262
2534 I.LEAD_TIME_LOT_SIZE = DECODE(I.LEAD_TIME_LOT_SIZE,null,DECODE(I.STD_LOT_SIZE,NULL,1,I.STD_LOT_SIZE),I.LEAD_TIME_LOT_SIZE), -- Added for bug# 900262
2535 I.CHECK_SHORTAGES_FLAG = DECODE(I.CHECK_SHORTAGES_FLAG, NULL, 'N', I.CHECK_SHORTAGES_FLAG),
2536 I.EFFECTIVITY_CONTROL = DECODE(I.EFFECTIVITY_CONTROL, NULL, 1, I.EFFECTIVITY_CONTROL),
2537 I.EQUIPMENT_TYPE = NVL( I.EQUIPMENT_TYPE, c_Equipment_Type_default ),
2538 I.COMMS_NL_TRACKABLE_FLAG = DECODE(I.COMMS_NL_TRACKABLE_FLAG,NULL,DECODE(I.CONTRACT_ITEM_TYPE_CODE,NULL,DECODE(I.SERVICEABLE_PRODUCT_FLAG,'Y','Y')),I.COMMS_NL_TRACKABLE_FLAG),
2539 I.WEB_STATUS = NVL( I.WEB_STATUS, c_Web_Status_default ),
2540 I.BULK_PICKED_FLAG = NVL( I.BULK_PICKED_FLAG, 'N' ),
2541 I.LOT_STATUS_ENABLED = NVL( I.LOT_STATUS_ENABLED, 'N' ),
2542 I.DEFAULT_LOT_STATUS_ID = NVL( I.DEFAULT_LOT_STATUS_ID, DECODE( NVL( I.LOT_CONTROL_CODE, 1 ),1, NULL,
2543 DECODE( I.LOT_STATUS_ENABLED, 'Y', c_default_Lot_Status_id, NULL ))),
2544 I.SERIAL_STATUS_ENABLED = NVL( I.SERIAL_STATUS_ENABLED, 'N' ),
2545 I.DEFAULT_SERIAL_STATUS_ID = NVL( I.DEFAULT_SERIAL_STATUS_ID,
2546 DECODE( NVL( I.SERIAL_NUMBER_CONTROL_CODE, 1 ), 1, NULL,
2547 DECODE( I.SERIAL_STATUS_ENABLED, 'Y', c_default_Serial_Status_id, NULL ))),
2548 I.DUAL_UOM_CONTROL = DECODE(NVL(TRACKING_QUANTITY_IND,'P'),'P',1,
2549 DECODE(NVL(SECONDARY_DEFAULT_IND,'X'),'F',2,'D',3, 4)),--11.5.10
2550
2551 --2/17/2003
2552 --As a Fix for bug# 2760857, the following LOT attributes were made to behave
2553 --similar to LOT_SUBSTITUTION_ENABLED attribute.
2554 --The Defaulting to 'N' below was commented out. But as per latest decision,
2555 --Old Checkbox related attributes will default to N, and new ones to NULL.
2556 --Since LOT_SUBSTITUTION_ENABLED is new attr, it will default to NULL.
2557 --
2558 I.LOT_SPLIT_ENABLED = NVL( I.LOT_SPLIT_ENABLED, 'N' ),
2559 I.LOT_MERGE_ENABLED = NVL( I.LOT_MERGE_ENABLED, 'N' ),
2560 I.LOT_TRANSLATE_ENABLED = NVL( I.LOT_TRANSLATE_ENABLED, 'N' ),
2561 I.DEFAULT_SO_SOURCE_TYPE = NVL( I.DEFAULT_SO_SOURCE_TYPE, 'INTERNAL' ),
2562 I.CREATE_SUPPLY_FLAG = NVL( I.CREATE_SUPPLY_FLAG, 'Y' ),
2563
2564 -- Added for 11.5.10 ENH
2565 I.TRACKING_QUANTITY_IND = NVL(I.TRACKING_QUANTITY_IND, 'P'),
2566 I.ONT_PRICING_QTY_SOURCE = NVL(I.ONT_PRICING_QTY_SOURCE,'P'),
2567 I.DUAL_UOM_DEVIATION_HIGH = NVL(I.DUAL_UOM_DEVIATION_HIGH, 0),
2568 I.DUAL_UOM_DEVIATION_LOW = NVL(I.DUAL_UOM_DEVIATION_LOW, 0),
2569 I.VMI_MINIMUM_UNITS = I.VMI_MINIMUM_UNITS,
2570 I.VMI_MINIMUM_DAYS = I.VMI_MINIMUM_DAYS,
2571 I.VMI_MAXIMUM_UNITS = VMI_MAXIMUM_UNITS,
2572 I.VMI_MAXIMUM_DAYS = VMI_MAXIMUM_DAYS,
2573 I.VMI_FIXED_ORDER_QUANTITY = I.VMI_FIXED_ORDER_QUANTITY,
2574 I.SO_AUTHORIZATION_FLAG = I.SO_AUTHORIZATION_FLAG,
2575 I.CONSIGNED_FLAG = NVL(I.CONSIGNED_FLAG, 2),
2576 I.ASN_AUTOEXPIRE_FLAG = NVL(I.ASN_AUTOEXPIRE_FLAG, 2),
2577 I.VMI_FORECAST_TYPE = NVL(I.VMI_FORECAST_TYPE, 1),
2578 I.FORECAST_HORIZON = I.FORECAST_HORIZON,
2579 I.EXCLUDE_FROM_BUDGET_FLAG = NVL(I.EXCLUDE_FROM_BUDGET_FLAG, 2),
2580 I.DAYS_TGT_INV_SUPPLY = I.DAYS_TGT_INV_SUPPLY,
2581 I.DAYS_TGT_INV_WINDOW = I.DAYS_TGT_INV_WINDOW,
2582 I.DAYS_MAX_INV_SUPPLY = I.DAYS_MAX_INV_SUPPLY,
2583 I.DAYS_MAX_INV_WINDOW = I.DAYS_MAX_INV_WINDOW,
2584 I.DRP_PLANNED_FLAG = NVL(I.DRP_PLANNED_FLAG, 2),
2585 I.CRITICAL_COMPONENT_FLAG = NVL(I.CRITICAL_COMPONENT_FLAG, 2),
2586 I.CONTINOUS_TRANSFER = NVL(I.CONTINOUS_TRANSFER, 3),
2587 I.CONVERGENCE = NVL(I.CONVERGENCE, 3),
2588 I.DIVERGENCE = NVL(I.DIVERGENCE, 3) ,
2589 /* Start Bug 3713912 */
2590 I.LOT_DIVISIBLE_FLAG = NVL(I.LOT_DIVISIBLE_FLAG , 'N' ) ,
2591 I.GRADE_CONTROL_FLAG = NVL(I.GRADE_CONTROL_FLAG , 'N' ) ,
2592 I.DEFAULT_GRADE = DEFAULT_GRADE ,
2593 I.CHILD_LOT_FLAG = NVL(I.CHILD_LOT_FLAG , 'N' ) ,
2594 I.PARENT_CHILD_GENERATION_FLAG = I.PARENT_CHILD_GENERATION_FLAG ,
2595 I.CHILD_LOT_PREFIX = I.CHILD_LOT_PREFIX ,
2596 I.CHILD_LOT_STARTING_NUMBER = I.CHILD_LOT_STARTING_NUMBER ,
2597 I.CHILD_LOT_VALIDATION_FLAG = NVL(I.CHILD_LOT_VALIDATION_FLAG , 'N' ) ,
2598 I.COPY_LOT_ATTRIBUTE_FLAG = NVL(I.COPY_LOT_ATTRIBUTE_FLAG , 'N' ) ,
2599 --5349389 I.RECIPE_ENABLED_FLAG = NVL(I.RECIPE_ENABLED_FLAG , 'N' ) ,
2600 I.PROCESS_QUALITY_ENABLED_FLAG = NVL(I.PROCESS_QUALITY_ENABLED_FLAG , 'N' ) ,
2601 --5349389 I.PROCESS_EXECUTION_ENABLED_FLAG = NVL(I.PROCESS_EXECUTION_ENABLED_FLAG, 'N' ) ,
2602 I.PROCESS_COSTING_ENABLED_FLAG = NVL(I.PROCESS_COSTING_ENABLED_FLAG , 'N' ) ,
2603 I.PROCESS_SUPPLY_SUBINVENTORY = I.PROCESS_SUPPLY_SUBINVENTORY ,
2604 I.PROCESS_SUPPLY_LOCATOR_ID = I.PROCESS_SUPPLY_LOCATOR_ID ,
2605 I.PROCESS_YIELD_SUBINVENTORY = I.PROCESS_YIELD_SUBINVENTORY ,
2606 I.PROCESS_YIELD_LOCATOR_ID = I.PROCESS_YIELD_LOCATOR_ID ,
2607 I.HAZARDOUS_MATERIAL_FLAG = NVL(I.HAZARDOUS_MATERIAL_FLAG , 'N' ) ,
2608 I.CAS_NUMBER = I.CAS_NUMBER ,
2609 I.RETEST_INTERVAL = I.RETEST_INTERVAL ,
2610 I.EXPIRATION_ACTION_INTERVAL = I.EXPIRATION_ACTION_INTERVAL ,
2611 I.EXPIRATION_ACTION_CODE = I.EXPIRATION_ACTION_CODE ,
2612 I.MATURITY_DAYS = I.MATURITY_DAYS ,
2613 I.HOLD_DAYS = I.HOLD_DAYS ,
2614 /* End Bug 3713912 */
2615 --R12 Enhancement
2616 I.PREPOSITION_POINT = NVL(I.PREPOSITION_POINT , 'N' ),
2617 I.REPAIR_PROGRAM = NVL(I.REPAIR_PROGRAM , 3 ),
2618 I.OUTSOURCED_ASSEMBLY = NVL(I.OUTSOURCED_ASSEMBLY , 2 ),
2619 /* Bug 5452757 */
2620 I.COLLATERAL_FLAG = NVL(I.COLLATERAL_FLAG,'N'),
2621 I.EVENT_FLAG = NVL(I.EVENT_FLAG,'N'),
2622 I.ELECTRONIC_FLAG = NVL(I.ELECTRONIC_FLAG,'N'),
2623 I.DOWNLOADABLE_FLAG = NVL(I.DOWNLOADABLE_FLAG, 'N'),
2624 I.INDIVISIBLE_FLAG = NVL(I.INDIVISIBLE_FLAG,'N'),
2625 /* End of Bug 5452757 */
2626 /* R12 FPC */
2627 I.GDSN_OUTBOUND_ENABLED_FLAG = NVL(I.GDSN_OUTBOUND_ENABLED_FLAG, 'N'),
2628 --Default Trade Item Descriptor for GDSN Syndicated Items Bug 6126870
2629 I.TRADE_ITEM_DESCRIPTOR = NVL(I.TRADE_ITEM_DESCRIPTOR, DECODE(I.GDSN_OUTBOUND_ENABLED_FLAG, 'Y', l_trade_item_default, NULL))
2630 WHERE I.rowid = p_rowid;
2631 l_ret_status := 0;
2632 END IF;
2633 RETURN (l_ret_status);
2634 EXCEPTION
2635 WHEN NULL_DESC_ERR THEN
2636 err_text := 'INVPUTLI.assign_item_defaults: Description cannot be null in MSII';
2637 return(1403);
2638 -- ToDo: make sure the following NO_DATA_FOUND happens only in a relevant sql statement
2639 WHEN NO_DATA_FOUND THEN
2640 err_text := 'INVPUTLI.assign_item_defaults: Invalid PRIMARY_UNIT_OF_MEASURE or PRIMARY_UOM_CODE';
2641 return(SQLCODE);
2642 WHEN OTHERS THEN
2643 err_text := substr('INVPUTLI.assign_item_defaults ' || SQLERRM , 1, 240);
2644 return(SQLCODE);
2645 END assign_item_defaults;
2646
2647 /*Bug: 4667452 */
2648 FUNCTION get_debug_level
2649 RETURN INTEGER
2650 IS
2651 BEGIN
2652 return(g_Debug_Level);
2653 END get_debug_level;
2654
2655 /*------------------------------*/
2656 /* Package initialization block */
2657 /*------------------------------*/
2658
2659 BEGIN
2660
2661 g_Debug_Level := FND_PROFILE.Value('INV_DEBUG_LEVEL');
2662
2663 END INVPUTLI;