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