[Home] [Help]
PACKAGE BODY: APPS.INVPPROC
Source
1 PACKAGE BODY INVPPROC AS
2 /* $Header: INVPPROB.pls 120.26.12010000.3 2008/11/25 02:24:19 akbharga ship $ */
3 FUNCTION inproit_process_item
4 (
5 prg_appid in NUMBER,
6 prg_id in NUMBER,
7 req_id in NUMBER,
8 user_id in NUMBER,
9 login_id in NUMBER,
10 error_message out NOCOPY VARCHAR2,
11 message_name out NOCOPY VARCHAR2,
12 table_name out NOCOPY VARCHAR2,
13 xset_id IN NUMBER DEFAULT -999
14 )
15 RETURN INTEGER
16 IS
17
18 CURSOR UOM_Process IS
19 SELECT
20 msii.inventory_item_id INV_ITEM_ID
21 , msii.primary_uom_code PUOMCODE
22 , muom.unit_of_measure PUOM
23 , muom.base_uom_flag
24 , muom.uom_class UOMCL
25 FROM
26 mtl_units_of_measure_vl muom
27 , mtl_system_items_interface msii
28 WHERE
29 msii.process_flag = 4
30 AND msii.allowed_units_lookup_code = 1
31 AND msii.set_process_id = xset_id
32 AND muom.uom_code = msii.primary_uom_code
33 AND NOT EXISTS
34 ( select 'x'
35 from mtl_uom_conversions
36 where inventory_item_id = msii.inventory_item_id
37 and uom_code = msii.primary_uom_code
38 );
39
40 return_code number := 0;
41 return_err VARCHAR2(240);
42 dumm_status number := 0;
43 COST_ERR exception;
44 conversion_rate_temp number;
45 status number;
46 error_msg varchar2(70);
47 LOGGING_ERR exception;
48 l_sysdate date := sysdate ;
49 l_process_flag_4 number := 4 ;
50 l_process_flag_7 number := 7 ;
51
52 CURSOR Cat_Assign IS
53 select
54 mp.MASTER_ORGANIZATION_ID MORG,
55 msi.organization_id ORGID,
56 msi.inventory_item_id ITEMID,
57 msi.inventory_item_flag INVFLAG,
58 msi.purchasing_item_flag PURFLAG,
59 msi.internal_order_flag INTFLAG,
60 msi.mrp_planning_code MRPCODE,
61 msi.serviceable_product_flag SERVFLAG,
62 msi.costing_enabled_flag COSTFLAG,
63 msi.eng_item_flag ENGFLAG,
64 msi.customer_order_flag CUSTFLAG,
65 msi.eam_item_type EAMTYPE,
66 msi.contract_item_type_code CONTCODE,
67 msi.gdsn_outbound_enabled_flag GDSNFLAG
68 from mtl_parameters mp,
69 mtl_system_items_interface msi
70 where mp.MASTER_ORGANIZATION_ID <> msi.organization_id
71 and mp.organization_id = msi.organization_id
72 AND msi.transaction_type = 'CREATE'
73 AND msi.process_flag = l_process_flag_4
74 AND msi.set_process_id = xset_id
75 AND msi.INVENTORY_ITEM_STATUS_CODE <> 'Pending';
76
77 CURSOR Flex_Exists IS
78 select msi.item_number,msi.organization_id,msi.organization_code,
79 msi.transaction_id,msi.process_flag
80 from mtl_system_items_b_kfv msk,
81 mtl_system_items_interface msi
82 where msi.item_number = msk.concatenated_segments
83 and msk.organization_id = msi.organization_id
84 AND msi.transaction_type = 'CREATE'
85 AND msi.process_flag = l_process_flag_4
86 AND msi.set_process_id = xset_id
87 FOR UPDATE OF process_flag;
88
89 CURSOR get_organization_code (cp_org_id VARCHAR2) IS
90 SELECT name
91 FROM hr_organization_units
92 WHERE organization_id = cp_org_id;
93
94 CURSOR c_ego_intf_rows IS
95 SELECT msii.transaction_id,
96 tl.language,
97 tl.column_value,
98 msii.inventory_item_id,
99 msii.organization_id
100 FROM mtl_system_items_interface msii,
101 ego_interface_tl tl
102 WHERE msii.process_flag = l_process_flag_4
103 AND msii.set_process_id = xset_id
104 AND msii.transaction_type = 'CREATE'
105 AND tl.unique_id = msii.transaction_id
106 AND tl.set_process_id = msii.set_process_id
107 AND UPPER(tl.table_name) = 'MTL_SYSTEM_ITEMS_INTERFACE'
108 AND UPPER(tl.column_name) = 'DESCRIPTION'
109 AND tl.language IN (SELECT l.language_code FROM fnd_languages l
110 WHERE l.installed_flag IN ('I', 'B'));
111
112 CURSOR is_gdsn_batch(cp_xset_id NUMBER) IS
113 SELECT 1 FROM ego_import_option_sets
114 WHERE batch_id = cp_xset_id
115 AND enabled_for_data_pool = 'Y';
116
117 CURSOR duplicate_recs IS
118 SELECT inventory_item_id, organization_id, item_number
119 FROM mtl_system_items_interface
120 WHERE transaction_type = 'CREATE'
121 AND process_flag = l_process_flag_4
122 AND set_process_id = xset_id;
123
124 TYPE transaction_type IS TABLE OF mtl_system_items_interface.transaction_id%TYPE
125 INDEX BY BINARY_INTEGER;
126
127 transaction_table transaction_type;
128 l_transaction_type VARCHAR2(10) := 'CREATE';
129
130 l_pending_flag varchar2(1);
131 -- fix for 3409139
132 -- l_last_updated_by number;
133 -- l_created_by number;
134 l_default_conversion_flag VARCHAR2(1);
135
136 -- l_Primary_Unit_of_Measure VARCHAR2(25);
137
138 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
139 l_item_id NUMBER;
140 l_item_count NUMBER;
141 l_err_text VARCHAR2(1000);
142 org_name varchar2(240);
143 l_is_gdsn_batch NUMBER;
144 ext_flag NUMBER;
145
146 BEGIN
147
148 IF l_inv_debug_level IN(101, 102) THEN
149 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into MSI_B with xset_id = '|| xset_id);
150 END IF;
151 --Bug 4767919 Anmurali
152 IF (INSTR(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'PLM_UI:Y') <> 0) THEN
153
154 For ff in Flex_Exists Loop
155
156 Open get_organization_code(ff.organization_id);
157 Fetch get_organization_code Into org_name;
158 Close get_organization_code;
159 org_name := NVL(org_name, ff.organization_code);
160
161 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_ITEM_MSI');
162 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', ff.item_number);
163 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
164 error_msg := FND_MESSAGE.GET;
165
166 dumm_status := INVPUOPI.mtl_log_interface_err(
167 ff.organization_id,
168 user_id,
169 login_id,
170 prg_appid,
171 prg_id,
172 req_id,
173 ff.TRANSACTION_ID,
174 error_msg,
175 'INVENTORY_ITEM_ID',
176 'MTL_SYSTEM_ITEMS_INTERFACE',
177 'INV_IOI_ERR',
178 l_err_text);
179 IF dumm_status < 0 THEN
180 raise LOGGING_ERR;
181 END IF;
182
183 UPDATE mtl_system_items_interface
184 SET process_flag = 3
185 WHERE CURRENT OF Flex_Exists;
186
187 END LOOP;
188 END IF;
189
190 --Performing duplicate check validation for GDSN batches
191 OPEN is_gdsn_batch(xset_id);
192 FETCH is_gdsn_batch INTO l_is_gdsn_batch;
193 CLOSE is_gdsn_batch;
194
195 IF l_is_gdsn_batch = 1 THEN
196 FOR cr IN duplicate_recs LOOP
197 SELECT count(*) INTO ext_flag
198 FROM MTL_SYSTEM_ITEMS_INTERFACE
199 WHERE inventory_item_id = cr.inventory_item_id
200 AND organization_id = cr.organization_id
201 AND process_flag = 4
202 AND set_process_id = xset_id
203 AND transaction_type = 'CREATE';
204
205 IF ext_flag > 1 THEN
206 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
207 SET process_flag = 3
208 WHERE inventory_item_id = cr.inventory_item_id
209 AND organization_id = cr.organization_id
210 AND process_flag = l_process_flag_4
211 AND set_process_id = xset_id
212 AND transaction_type = 'CREATE'
213 RETURNING transaction_id BULK COLLECT INTO transaction_table;
214
215 OPEN get_organization_code(cr.organization_id);
216 FETCH get_organization_code Into org_name;
217 CLOSE get_organization_code;
218
219 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_REC_MSII');
220 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', cr.item_number);
221 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
222 error_msg := FND_MESSAGE.GET;
223
224 IF transaction_table.COUNT > 0 THEN
225 FOR j IN transaction_table.FIRST .. transaction_table.LAST LOOP
226 dumm_status := INVPUOPI.mtl_log_interface_err(
227 cr.organization_id,
228 user_id,
229 login_id,
230 prg_appid,
231 prg_id,
232 req_id,
233 transaction_table(j),
234 error_msg,
235 'INVENTORY_ITEM_ID',
236 'MTL_SYSTEM_ITEMS_INTERFACE',
237 'INV_IOI_ERR' ,
238 l_err_text);
239 END LOOP;
240 END IF;
241 END IF;
242 END LOOP;
243 END IF;
244
245 INSERT INTO MTL_SYSTEM_ITEMS_B
246 (
247 INVENTORY_ITEM_ID,
248 ORGANIZATION_ID,
249 LAST_UPDATE_DATE,
250 LAST_UPDATED_BY,
251 CREATION_DATE,
252 CREATED_BY,
253 LAST_UPDATE_LOGIN,
254 SUMMARY_FLAG,
255 ENABLED_FLAG,
256 -- START_DATE_ACTIVE, Commented for Bug: 4457440
257 -- END_DATE_ACTIVE, Commented for Bug: 4457440
258 DESCRIPTION,
259 BUYER_ID,
260 ACCOUNTING_RULE_ID,
261 INVOICING_RULE_ID,
262 SEGMENT1,
263 SEGMENT2,
264 SEGMENT3,
265 SEGMENT4,
266 SEGMENT5,
267 SEGMENT6,
268 SEGMENT7,
269 SEGMENT8,
270 SEGMENT9,
271 SEGMENT10,
272 SEGMENT11,
273 SEGMENT12,
274 SEGMENT13,
275 SEGMENT14,
276 SEGMENT15,
277 SEGMENT16,
278 SEGMENT17,
279 SEGMENT18,
280 SEGMENT19,
281 SEGMENT20,
282 ATTRIBUTE_CATEGORY,
283 ATTRIBUTE1,
284 ATTRIBUTE2,
285 ATTRIBUTE3,
286 ATTRIBUTE4,
287 ATTRIBUTE5,
288 ATTRIBUTE6,
289 ATTRIBUTE7,
290 ATTRIBUTE8,
291 ATTRIBUTE9,
292 ATTRIBUTE10,
293 ATTRIBUTE11,
294 ATTRIBUTE12,
295 ATTRIBUTE13,
296 ATTRIBUTE14,
297 ATTRIBUTE15,
298 /* Start Bug 3713912 */
299 ATTRIBUTE16,
300 ATTRIBUTE17,
301 ATTRIBUTE18,
302 ATTRIBUTE19,
303 ATTRIBUTE20,
304 ATTRIBUTE21,
305 ATTRIBUTE22,
306 ATTRIBUTE23,
307 ATTRIBUTE24,
308 ATTRIBUTE25,
309 ATTRIBUTE26,
310 ATTRIBUTE27,
311 ATTRIBUTE28,
312 ATTRIBUTE29,
313 ATTRIBUTE30,
314 /* End Bug 3713912 */
315 GLOBAL_ATTRIBUTE_CATEGORY,
316 GLOBAL_ATTRIBUTE1,
317 GLOBAL_ATTRIBUTE2,
318 GLOBAL_ATTRIBUTE3,
319 GLOBAL_ATTRIBUTE4,
320 GLOBAL_ATTRIBUTE5,
321 GLOBAL_ATTRIBUTE6,
322 GLOBAL_ATTRIBUTE7,
323 GLOBAL_ATTRIBUTE8,
324 GLOBAL_ATTRIBUTE9,
325 GLOBAL_ATTRIBUTE10,
326 GLOBAL_ATTRIBUTE11,
327 GLOBAL_ATTRIBUTE12,
328 GLOBAL_ATTRIBUTE13,
329 GLOBAL_ATTRIBUTE14,
330 GLOBAL_ATTRIBUTE15,
331 GLOBAL_ATTRIBUTE16,
332 GLOBAL_ATTRIBUTE17,
333 GLOBAL_ATTRIBUTE18,
334 GLOBAL_ATTRIBUTE19,
335 GLOBAL_ATTRIBUTE20,
336 PURCHASING_ITEM_FLAG,
337 SHIPPABLE_ITEM_FLAG,
338 CUSTOMER_ORDER_FLAG,
339 INTERNAL_ORDER_FLAG,
340 INVENTORY_ITEM_FLAG,
341 ENG_ITEM_FLAG,
342 INVENTORY_ASSET_FLAG,
343 PURCHASING_ENABLED_FLAG,
344 CUSTOMER_ORDER_ENABLED_FLAG,
345 INTERNAL_ORDER_ENABLED_FLAG,
346 SO_TRANSACTIONS_FLAG,
347 MTL_TRANSACTIONS_ENABLED_FLAG,
348 STOCK_ENABLED_FLAG,
349 BOM_ENABLED_FLAG,
350 BUILD_IN_WIP_FLAG,
351 REVISION_QTY_CONTROL_CODE,
352 ITEM_CATALOG_GROUP_ID,
353 CATALOG_STATUS_FLAG,
354 RETURNABLE_FLAG,
355 DEFAULT_SHIPPING_ORG,
356 COLLATERAL_FLAG,
357 TAXABLE_FLAG,
358 PURCHASING_TAX_CODE,
359 ALLOW_ITEM_DESC_UPDATE_FLAG,
360 INSPECTION_REQUIRED_FLAG,
361 RECEIPT_REQUIRED_FLAG,
362 MARKET_PRICE,
363 HAZARD_CLASS_ID,
364 RFQ_REQUIRED_FLAG,
365 QTY_RCV_TOLERANCE,
366 LIST_PRICE_PER_UNIT,
367 UN_NUMBER_ID,
368 PRICE_TOLERANCE_PERCENT,
369 ASSET_CATEGORY_ID,
370 ROUNDING_FACTOR,
371 UNIT_OF_ISSUE,
372 ENFORCE_SHIP_TO_LOCATION_CODE,
373 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
374 ALLOW_UNORDERED_RECEIPTS_FLAG,
375 ALLOW_EXPRESS_DELIVERY_FLAG,
376 DAYS_EARLY_RECEIPT_ALLOWED,
377 DAYS_LATE_RECEIPT_ALLOWED,
378 RECEIPT_DAYS_EXCEPTION_CODE,
379 RECEIVING_ROUTING_ID,
380 INVOICE_CLOSE_TOLERANCE,
381 RECEIVE_CLOSE_TOLERANCE,
382 AUTO_LOT_ALPHA_PREFIX,
383 START_AUTO_LOT_NUMBER,
384 LOT_CONTROL_CODE,
385 SHELF_LIFE_CODE,
386 SHELF_LIFE_DAYS,
387 SERIAL_NUMBER_CONTROL_CODE,
388 START_AUTO_SERIAL_NUMBER,
389 AUTO_SERIAL_ALPHA_PREFIX,
390 SOURCE_TYPE,
391 SOURCE_ORGANIZATION_ID,
392 SOURCE_SUBINVENTORY,
393 EXPENSE_ACCOUNT,
394 ENCUMBRANCE_ACCOUNT,
395 RESTRICT_SUBINVENTORIES_CODE,
396 UNIT_WEIGHT,
397 WEIGHT_UOM_CODE,
398 VOLUME_UOM_CODE,
399 UNIT_VOLUME,
400 RESTRICT_LOCATORS_CODE,
401 LOCATION_CONTROL_CODE,
402 SHRINKAGE_RATE,
403 ACCEPTABLE_EARLY_DAYS,
404 PLANNING_TIME_FENCE_CODE,
405 DEMAND_TIME_FENCE_CODE,
406 LEAD_TIME_LOT_SIZE,
407 STD_LOT_SIZE,
408 CUM_MANUFACTURING_LEAD_TIME,
409 OVERRUN_PERCENTAGE,
410 ACCEPTABLE_RATE_INCREASE,
411 ACCEPTABLE_RATE_DECREASE,
412 CUMULATIVE_TOTAL_LEAD_TIME,
413 PLANNING_TIME_FENCE_DAYS,
414 DEMAND_TIME_FENCE_DAYS,
415 END_ASSEMBLY_PEGGING_FLAG,
416 PLANNING_EXCEPTION_SET,
417 BOM_ITEM_TYPE,
418 PICK_COMPONENTS_FLAG,
419 REPLENISH_TO_ORDER_FLAG,
420 BASE_ITEM_ID,
421 ATP_COMPONENTS_FLAG,
422 ATP_FLAG,
423 FIXED_LEAD_TIME,
424 VARIABLE_LEAD_TIME,
425 WIP_SUPPLY_LOCATOR_ID,
426 WIP_SUPPLY_TYPE,
427 WIP_SUPPLY_SUBINVENTORY,
428 PRIMARY_UOM_CODE,
429 PRIMARY_UNIT_OF_MEASURE,
430 ALLOWED_UNITS_LOOKUP_CODE,
431 COST_OF_SALES_ACCOUNT,
432 SALES_ACCOUNT,
433 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
434 INVENTORY_ITEM_STATUS_CODE,
435 INVENTORY_PLANNING_CODE,
436 PLANNER_CODE,
437 PLANNING_MAKE_BUY_CODE,
438 FIXED_LOT_MULTIPLIER,
439 ROUNDING_CONTROL_TYPE,
440 CARRYING_COST,
441 POSTPROCESSING_LEAD_TIME,
442 PREPROCESSING_LEAD_TIME,
443 FULL_LEAD_TIME,
444 ORDER_COST,
445 MRP_SAFETY_STOCK_PERCENT,
446 MRP_SAFETY_STOCK_CODE,
447 MIN_MINMAX_QUANTITY,
448 MAX_MINMAX_QUANTITY,
449 MINIMUM_ORDER_QUANTITY,
450 FIXED_ORDER_QUANTITY,
451 FIXED_DAYS_SUPPLY,
452 MAXIMUM_ORDER_QUANTITY,
453 ATP_RULE_ID,
454 PICKING_RULE_ID,
455 RESERVABLE_TYPE,
456 POSITIVE_MEASUREMENT_ERROR,
457 NEGATIVE_MEASUREMENT_ERROR,
458 ENGINEERING_ECN_CODE,
459 ENGINEERING_ITEM_ID,
460 ENGINEERING_DATE,
461 SERVICE_STARTING_DELAY,
462 SERVICEABLE_COMPONENT_FLAG,
463 SERVICEABLE_PRODUCT_FLAG,
464 BASE_WARRANTY_SERVICE_ID,
465 PAYMENT_TERMS_ID,
466 PREVENTIVE_MAINTENANCE_FLAG,
467 PRIMARY_SPECIALIST_ID,
468 SECONDARY_SPECIALIST_ID,
469 SERVICEABLE_ITEM_CLASS_ID,
470 TIME_BILLABLE_FLAG,
471 MATERIAL_BILLABLE_FLAG,
472 EXPENSE_BILLABLE_FLAG,
473 PRORATE_SERVICE_FLAG,
474 COVERAGE_SCHEDULE_ID,
475 SERVICE_DURATION_PERIOD_CODE,
476 SERVICE_DURATION,
477 MAX_WARRANTY_AMOUNT,
478 RESPONSE_TIME_PERIOD_CODE,
479 RESPONSE_TIME_VALUE,
480 NEW_REVISION_CODE,
481 TAX_CODE,
482 MUST_USE_APPROVED_VENDOR_FLAG,
483 SAFETY_STOCK_BUCKET_DAYS,
484 AUTO_REDUCE_MPS,
485 COSTING_ENABLED_FLAG,
486 INVOICEABLE_ITEM_FLAG,
487 INVOICE_ENABLED_FLAG,
488 OUTSIDE_OPERATION_FLAG,
489 OUTSIDE_OPERATION_UOM_TYPE,
490 AUTO_CREATED_CONFIG_FLAG,
491 CYCLE_COUNT_ENABLED_FLAG,
492 MODEL_CONFIG_CLAUSE_NAME,
493 SHIP_MODEL_COMPLETE_FLAG,
494 MRP_PLANNING_CODE,
495 RETURN_INSPECTION_REQUIREMENT,
496 REQUEST_ID,
497 PROGRAM_APPLICATION_ID,
498 PROGRAM_ID,
499 PROGRAM_UPDATE_DATE,
500 REPETITIVE_PLANNING_FLAG, /*NP 13SEP94 Added this column*/
501 QTY_RCV_EXCEPTION_CODE, /*NP 12OCT94 Added this column*/
502 MRP_CALCULATE_ATP_FLAG, /*NP 12OCT94 Added this column*/
503 ITEM_TYPE, /*NP 12OCT94 Added this column*/
504 WARRANTY_VENDOR_ID, /*NP 12OCT94 Added this column*/
505 ATO_FORECAST_CONTROL, /*NP 10OCT94 Added this column*/
506 RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Added these 8 columns*/
507 RELEASE_TIME_FENCE_DAYS,
508 CONTAINER_ITEM_FLAG,
509 CONTAINER_TYPE_CODE,
510 INTERNAL_VOLUME,
511 MAXIMUM_LOAD_WEIGHT,
512 MINIMUM_FILL_PERCENT,
513 VEHICLE_ITEM_FLAG,
514 CHECK_SHORTAGES_FLAG, /*CK 21MAY98 Added column*/
515 EFFECTIVITY_CONTROL
516 , OVERCOMPLETION_TOLERANCE_TYPE
517 , OVERCOMPLETION_TOLERANCE_VALUE
518 , OVER_SHIPMENT_TOLERANCE
519 , UNDER_SHIPMENT_TOLERANCE
520 , OVER_RETURN_TOLERANCE
521 , UNDER_RETURN_TOLERANCE
522 , EQUIPMENT_TYPE
523 , RECOVERED_PART_DISP_CODE
524 , DEFECT_TRACKING_ON_FLAG
525 , EVENT_FLAG
526 , ELECTRONIC_FLAG
527 , DOWNLOADABLE_FLAG
528 , VOL_DISCOUNT_EXEMPT_FLAG
529 , COUPON_EXEMPT_FLAG
530 , COMMS_NL_TRACKABLE_FLAG
531 , ASSET_CREATION_CODE
532 , COMMS_ACTIVATION_REQD_FLAG
533 , ORDERABLE_ON_WEB_FLAG
534 , BACK_ORDERABLE_FLAG
535 , WEB_STATUS
536 , INDIVISIBLE_FLAG
537 , DIMENSION_UOM_CODE
538 , UNIT_LENGTH
539 , UNIT_WIDTH
540 , UNIT_HEIGHT
541 , BULK_PICKED_FLAG
542 , LOT_STATUS_ENABLED
543 , DEFAULT_LOT_STATUS_ID
544 , SERIAL_STATUS_ENABLED
545 , DEFAULT_SERIAL_STATUS_ID
546 , LOT_SPLIT_ENABLED
547 , LOT_MERGE_ENABLED
548 , INVENTORY_CARRY_PENALTY
549 , OPERATION_SLACK_PENALTY
550 , FINANCING_ALLOWED_FLAG
551 , EAM_ITEM_TYPE
552 , EAM_ACTIVITY_TYPE_CODE
553 , EAM_ACTIVITY_CAUSE_CODE
554 , EAM_ACT_NOTIFICATION_FLAG
555 , EAM_ACT_SHUTDOWN_STATUS
556 , DUAL_UOM_CONTROL
557 , SECONDARY_UOM_CODE
558 , DUAL_UOM_DEVIATION_HIGH
559 , DUAL_UOM_DEVIATION_LOW
560 --
561 , SERVICE_ITEM_FLAG
562 , VENDOR_WARRANTY_FLAG
563 , USAGE_ITEM_FLAG
564 --
565 , CONTRACT_ITEM_TYPE_CODE
566 , SUBSCRIPTION_DEPEND_FLAG
567 --
568 , SERV_REQ_ENABLED_CODE
569 , SERV_BILLING_ENABLED_FLAG
570 , SERV_IMPORTANCE_LEVEL
571 , PLANNED_INV_POINT_FLAG
572 , LOT_TRANSLATE_ENABLED
573 , DEFAULT_SO_SOURCE_TYPE
574 , CREATE_SUPPLY_FLAG
575 , SUBSTITUTION_WINDOW_CODE
576 , SUBSTITUTION_WINDOW_DAYS
577 --Added as part of 11.5.9
578 , LOT_SUBSTITUTION_ENABLED
579 , MINIMUM_LICENSE_QUANTITY
580 , EAM_ACTIVITY_SOURCE_CODE
581 , IB_ITEM_INSTANCE_CLASS
582 , CONFIG_MODEL_TYPE
583 --2740503: Lifecycle-phase introduced.
584 , LIFECYCLE_ID
585 , CURRENT_PHASE_ID
586 --Added as part of 11.5.10
587 , TRACKING_QUANTITY_IND
588 , ONT_PRICING_QTY_SOURCE
589 , SECONDARY_DEFAULT_IND
590 , CONFIG_ORGS
591 , CONFIG_MATCH
592 ,VMI_MINIMUM_UNITS
593 ,VMI_MINIMUM_DAYS
594 ,VMI_MAXIMUM_UNITS
595 ,VMI_MAXIMUM_DAYS
596 ,VMI_FIXED_ORDER_QUANTITY
597 ,SO_AUTHORIZATION_FLAG
598 ,CONSIGNED_FLAG
599 ,ASN_AUTOEXPIRE_FLAG
600 ,VMI_FORECAST_TYPE
601 ,FORECAST_HORIZON
602 ,EXCLUDE_FROM_BUDGET_FLAG
603 ,DAYS_TGT_INV_SUPPLY
604 ,DAYS_TGT_INV_WINDOW
605 ,DAYS_MAX_INV_SUPPLY
606 ,DAYS_MAX_INV_WINDOW
607 ,DRP_PLANNED_FLAG
608 ,CRITICAL_COMPONENT_FLAG
609 ,CONTINOUS_TRANSFER
610 ,CONVERGENCE
611 ,DIVERGENCE
612 /* Start Bug 3713912 */
613 ,LOT_DIVISIBLE_FLAG
614 ,GRADE_CONTROL_FLAG
615 ,DEFAULT_GRADE
616 ,CHILD_LOT_FLAG
617 ,PARENT_CHILD_GENERATION_FLAG
618 ,CHILD_LOT_PREFIX
619 ,CHILD_LOT_STARTING_NUMBER
620 ,CHILD_LOT_VALIDATION_FLAG
621 ,COPY_LOT_ATTRIBUTE_FLAG
622 ,RECIPE_ENABLED_FLAG
623 ,PROCESS_QUALITY_ENABLED_FLAG
624 ,PROCESS_EXECUTION_ENABLED_FLAG
625 ,PROCESS_COSTING_ENABLED_FLAG
626 ,PROCESS_SUPPLY_SUBINVENTORY
627 ,PROCESS_SUPPLY_LOCATOR_ID
628 ,PROCESS_YIELD_SUBINVENTORY
629 ,PROCESS_YIELD_LOCATOR_ID
630 ,HAZARDOUS_MATERIAL_FLAG
631 ,CAS_NUMBER
632 ,RETEST_INTERVAL
633 ,EXPIRATION_ACTION_INTERVAL
634 ,EXPIRATION_ACTION_CODE
635 ,MATURITY_DAYS
636 ,HOLD_DAYS
637 /* End Bug 3713912 */
638 /* Bug 4224512 Updating the object version number - Anmurali */
639 ,OBJECT_VERSION_NUMBER
640 ,CHARGE_PERIODICITY_CODE
641 ,OUTSOURCED_ASSEMBLY
642 ,SUBCONTRACTING_COMPONENT
643 ,REPAIR_PROGRAM
644 ,REPAIR_LEADTIME
645 ,PREPOSITION_POINT
646 ,REPAIR_YIELD
647 /* New attrs for R12 FPC */
648 ,GDSN_OUTBOUND_ENABLED_FLAG
649 ,TRADE_ITEM_DESCRIPTOR
650 ,STYLE_ITEM_FLAG
651 ,STYLE_ITEM_ID
652 )
653 SELECT
654 I.INVENTORY_ITEM_ID
655 , I.ORGANIZATION_ID
656 , NVL(I.LAST_UPDATE_DATE, l_sysdate),
657 user_id, /* last_updated_by */
658 NVL(I.CREATION_DATE, l_sysdate),
659 user_id, /* created_by */
660 login_id, /* last_update_login */
661 I.SUMMARY_FLAG,
662 I.ENABLED_FLAG,
663 -- I.START_DATE_ACTIVE, Commented for Bug: 4457440
664 -- I.END_DATE_ACTIVE, Commented for Bug: 4457440
665 I.DESCRIPTION,
666 I.BUYER_ID,
667 I.ACCOUNTING_RULE_ID,
668 I.INVOICING_RULE_ID,
669 I.SEGMENT1,
670 I.SEGMENT2,
671 I.SEGMENT3,
672 I.SEGMENT4,
673 I.SEGMENT5,
674 I.SEGMENT6,
675 I.SEGMENT7,
676 I.SEGMENT8,
677 I.SEGMENT9,
678 I.SEGMENT10,
679 I.SEGMENT11,
680 I.SEGMENT12,
681 I.SEGMENT13,
682 I.SEGMENT14,
683 I.SEGMENT15,
684 I.SEGMENT16,
685 I.SEGMENT17,
686 I.SEGMENT18,
687 I.SEGMENT19,
688 I.SEGMENT20,
689 I.ATTRIBUTE_CATEGORY,
690 I.ATTRIBUTE1,
691 I.ATTRIBUTE2,
692 I.ATTRIBUTE3,
693 I.ATTRIBUTE4,
694 I.ATTRIBUTE5,
695 I.ATTRIBUTE6,
696 I.ATTRIBUTE7,
697 I.ATTRIBUTE8,
698 I.ATTRIBUTE9,
699 I.ATTRIBUTE10,
700 I.ATTRIBUTE11,
701 I.ATTRIBUTE12,
702 I.ATTRIBUTE13,
703 I.ATTRIBUTE14,
704 I.ATTRIBUTE15,
705 /* Start Bug 3713912 */
706 I.ATTRIBUTE16,
707 I.ATTRIBUTE17,
708 I.ATTRIBUTE18,
709 I.ATTRIBUTE19,
710 I.ATTRIBUTE20,
711 I.ATTRIBUTE21,
712 I.ATTRIBUTE22,
713 I.ATTRIBUTE23,
714 I.ATTRIBUTE24,
715 I.ATTRIBUTE25,
716 I.ATTRIBUTE26,
717 I.ATTRIBUTE27,
718 I.ATTRIBUTE28,
719 I.ATTRIBUTE29,
720 I.ATTRIBUTE30,
721 /* End Bug 3713912 */
722 I.GLOBAL_ATTRIBUTE_CATEGORY,
723 I.GLOBAL_ATTRIBUTE1,
724 I.GLOBAL_ATTRIBUTE2,
725 I.GLOBAL_ATTRIBUTE3,
726 I.GLOBAL_ATTRIBUTE4,
727 I.GLOBAL_ATTRIBUTE5,
728 I.GLOBAL_ATTRIBUTE6,
729 I.GLOBAL_ATTRIBUTE7,
730 I.GLOBAL_ATTRIBUTE8,
731 I.GLOBAL_ATTRIBUTE9,
732 I.GLOBAL_ATTRIBUTE10,
733 I.GLOBAL_ATTRIBUTE11,
734 I.GLOBAL_ATTRIBUTE12,
735 I.GLOBAL_ATTRIBUTE13,
736 I.GLOBAL_ATTRIBUTE14,
737 I.GLOBAL_ATTRIBUTE15,
738 I.GLOBAL_ATTRIBUTE16,
739 I.GLOBAL_ATTRIBUTE17,
740 I.GLOBAL_ATTRIBUTE18,
741 I.GLOBAL_ATTRIBUTE19,
742 I.GLOBAL_ATTRIBUTE20,
743 I.PURCHASING_ITEM_FLAG,
744 I.SHIPPABLE_ITEM_FLAG,
745 I.CUSTOMER_ORDER_FLAG,
746 I.INTERNAL_ORDER_FLAG,
747 I.INVENTORY_ITEM_FLAG,
748 I.ENG_ITEM_FLAG,
749 I.INVENTORY_ASSET_FLAG,
750 I.PURCHASING_ENABLED_FLAG,
751 I.CUSTOMER_ORDER_ENABLED_FLAG,
752 I.INTERNAL_ORDER_ENABLED_FLAG,
753 I.SO_TRANSACTIONS_FLAG,
754 I.MTL_TRANSACTIONS_ENABLED_FLAG,
755 I.STOCK_ENABLED_FLAG,
756 I.BOM_ENABLED_FLAG,
757 I.BUILD_IN_WIP_FLAG,
758 I.REVISION_QTY_CONTROL_CODE,
759 I.ITEM_CATALOG_GROUP_ID,
760 I.CATALOG_STATUS_FLAG,
761 I.RETURNABLE_FLAG,
762 I.DEFAULT_SHIPPING_ORG,
763 I.COLLATERAL_FLAG,
764 I.TAXABLE_FLAG,
765 I.PURCHASING_TAX_CODE,
766 I.ALLOW_ITEM_DESC_UPDATE_FLAG,
767 I.INSPECTION_REQUIRED_FLAG,
768 I.RECEIPT_REQUIRED_FLAG,
769 I.MARKET_PRICE,
770 I.HAZARD_CLASS_ID,
771 I.RFQ_REQUIRED_FLAG,
772 I.QTY_RCV_TOLERANCE,
773 I.LIST_PRICE_PER_UNIT,
774 I.UN_NUMBER_ID,
775 I.PRICE_TOLERANCE_PERCENT,
776 I.ASSET_CATEGORY_ID,
777 I.ROUNDING_FACTOR,
778 I.UNIT_OF_ISSUE,
779 I.ENFORCE_SHIP_TO_LOCATION_CODE,
780 I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
781 I.ALLOW_UNORDERED_RECEIPTS_FLAG,
782 I.ALLOW_EXPRESS_DELIVERY_FLAG,
783 I.DAYS_EARLY_RECEIPT_ALLOWED,
784 I.DAYS_LATE_RECEIPT_ALLOWED,
785 I.RECEIPT_DAYS_EXCEPTION_CODE,
786 I.RECEIVING_ROUTING_ID,
787 I.INVOICE_CLOSE_TOLERANCE,
788 I.RECEIVE_CLOSE_TOLERANCE,
789 I.AUTO_LOT_ALPHA_PREFIX,
790 I.START_AUTO_LOT_NUMBER,
791 I.LOT_CONTROL_CODE,
792 I.SHELF_LIFE_CODE,
793 I.SHELF_LIFE_DAYS,
794 I.SERIAL_NUMBER_CONTROL_CODE,
795 I.START_AUTO_SERIAL_NUMBER,
796 I.AUTO_SERIAL_ALPHA_PREFIX,
797 I.SOURCE_TYPE,
798 I.SOURCE_ORGANIZATION_ID,
799 I.SOURCE_SUBINVENTORY,
800 I.EXPENSE_ACCOUNT,
801 I.ENCUMBRANCE_ACCOUNT,
802 I.RESTRICT_SUBINVENTORIES_CODE,
803 I.UNIT_WEIGHT,
804 I.WEIGHT_UOM_CODE,
805 I.VOLUME_UOM_CODE,
806 I.UNIT_VOLUME,
807 I.RESTRICT_LOCATORS_CODE,
808 I.LOCATION_CONTROL_CODE,
809 I.SHRINKAGE_RATE,
810 I.ACCEPTABLE_EARLY_DAYS,
811 I.PLANNING_TIME_FENCE_CODE,
812 I.DEMAND_TIME_FENCE_CODE,
813 --Bug: 2473633
814 --- I.LEAD_TIME_LOT_SIZE,
815 decode(I.LEAD_TIME_LOT_SIZE, NULL,decode(I.STD_LOT_SIZE,NULL,1,I.STD_LOT_SIZE), I.LEAD_TIME_LOT_SIZE),
816 --Bug: 2473633 ended.
817 I.STD_LOT_SIZE,
818 I.CUM_MANUFACTURING_LEAD_TIME,
819 I.OVERRUN_PERCENTAGE,
820 I.ACCEPTABLE_RATE_INCREASE,
821 I.ACCEPTABLE_RATE_DECREASE,
822 I.CUMULATIVE_TOTAL_LEAD_TIME,
823 I.PLANNING_TIME_FENCE_DAYS,
824 I.DEMAND_TIME_FENCE_DAYS,
825 I.END_ASSEMBLY_PEGGING_FLAG,
826 I.PLANNING_EXCEPTION_SET,
827 I.BOM_ITEM_TYPE,
828 I.PICK_COMPONENTS_FLAG,
829 I.REPLENISH_TO_ORDER_FLAG,
830 I.BASE_ITEM_ID,
831 I.ATP_COMPONENTS_FLAG,
832 I.ATP_FLAG,
833 I.FIXED_LEAD_TIME,
834 I.VARIABLE_LEAD_TIME,
835 I.WIP_SUPPLY_LOCATOR_ID,
836 I.WIP_SUPPLY_TYPE,
837 I.WIP_SUPPLY_SUBINVENTORY,
838 I.PRIMARY_UOM_CODE,
839 I.PRIMARY_UNIT_OF_MEASURE,
840 I.ALLOWED_UNITS_LOOKUP_CODE,
841 I.COST_OF_SALES_ACCOUNT,
842 I.SALES_ACCOUNT,
843 I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
844 I.INVENTORY_ITEM_STATUS_CODE,
845 I.INVENTORY_PLANNING_CODE,
846 I.PLANNER_CODE,
847 I.PLANNING_MAKE_BUY_CODE,
848 I.FIXED_LOT_MULTIPLIER,
849 I.ROUNDING_CONTROL_TYPE,
850 I.CARRYING_COST,
851 I.POSTPROCESSING_LEAD_TIME,
852 I.PREPROCESSING_LEAD_TIME,
853 I.FULL_LEAD_TIME,
854 I.ORDER_COST,
855 I.MRP_SAFETY_STOCK_PERCENT,
856 I.MRP_SAFETY_STOCK_CODE,
857 I.MIN_MINMAX_QUANTITY,
858 I.MAX_MINMAX_QUANTITY,
859 I.MINIMUM_ORDER_QUANTITY,
860 I.FIXED_ORDER_QUANTITY,
861 I.FIXED_DAYS_SUPPLY,
862 I.MAXIMUM_ORDER_QUANTITY,
863 I.ATP_RULE_ID,
864 I.PICKING_RULE_ID,
865 I.RESERVABLE_TYPE,
866 I.POSITIVE_MEASUREMENT_ERROR,
867 I.NEGATIVE_MEASUREMENT_ERROR,
868 I.ENGINEERING_ECN_CODE,
869 I.ENGINEERING_ITEM_ID,
870 I.ENGINEERING_DATE,
871 I.SERVICE_STARTING_DELAY,
872 I.SERVICEABLE_COMPONENT_FLAG,
873 I.SERVICEABLE_PRODUCT_FLAG,
874 I.BASE_WARRANTY_SERVICE_ID,
875 I.PAYMENT_TERMS_ID,
876 I.PREVENTIVE_MAINTENANCE_FLAG,
877 I.PRIMARY_SPECIALIST_ID,
878 I.SECONDARY_SPECIALIST_ID,
879 I.SERVICEABLE_ITEM_CLASS_ID,
880 I.TIME_BILLABLE_FLAG,
881 I.MATERIAL_BILLABLE_FLAG,
882 I.EXPENSE_BILLABLE_FLAG,
883 I.PRORATE_SERVICE_FLAG,
884 I.COVERAGE_SCHEDULE_ID,
885 I.SERVICE_DURATION_PERIOD_CODE,
886 I.SERVICE_DURATION,
887 I.MAX_WARRANTY_AMOUNT,
888 I.RESPONSE_TIME_PERIOD_CODE,
889 I.RESPONSE_TIME_VALUE,
890 I.NEW_REVISION_CODE,
891 I.TAX_CODE,
892 I.MUST_USE_APPROVED_VENDOR_FLAG,
893 I.SAFETY_STOCK_BUCKET_DAYS,
894 I.AUTO_REDUCE_MPS,
895 I.COSTING_ENABLED_FLAG,
896 I.INVOICEABLE_ITEM_FLAG,
897 I.INVOICE_ENABLED_FLAG,
898 I.OUTSIDE_OPERATION_FLAG,
899 I.OUTSIDE_OPERATION_UOM_TYPE,
900 I.AUTO_CREATED_CONFIG_FLAG,
901 I.CYCLE_COUNT_ENABLED_FLAG,
902 I.MODEL_CONFIG_CLAUSE_NAME,
903 I.SHIP_MODEL_COMPLETE_FLAG,
904 I.MRP_PLANNING_CODE,
905 I.RETURN_INSPECTION_REQUIREMENT,
906 req_id,
907 prg_appid,
908 prg_id,
909 l_sysdate,
910 I.REPETITIVE_PLANNING_FLAG,
911 I.QTY_RCV_EXCEPTION_CODE, /*NP 12OCT94 Added this column*/
912 I.MRP_CALCULATE_ATP_FLAG, /*NP 12OCT94 Added this column*/
913 I.ITEM_TYPE, /*NP 12OCT94 Added this column*/
914 I.WARRANTY_VENDOR_ID, /*NP 12OCT94 Added this column*/
915 I.ATO_FORECAST_CONTROL,
916 I.RELEASE_TIME_FENCE_CODE,
917 I.RELEASE_TIME_FENCE_DAYS,
918 I.CONTAINER_ITEM_FLAG,
919 I.CONTAINER_TYPE_CODE,
920 I.INTERNAL_VOLUME,
921 I.MAXIMUM_LOAD_WEIGHT,
922 I.MINIMUM_FILL_PERCENT,
923 I.VEHICLE_ITEM_FLAG,
924 I.CHECK_SHORTAGES_FLAG, /*CK 21MAY98 Added column*/
925 I.EFFECTIVITY_CONTROL
926 , I.OVERCOMPLETION_TOLERANCE_TYPE
927 , I.OVERCOMPLETION_TOLERANCE_VALUE
928 , I.OVER_SHIPMENT_TOLERANCE
929 , I.UNDER_SHIPMENT_TOLERANCE
930 , I.OVER_RETURN_TOLERANCE
931 , I.UNDER_RETURN_TOLERANCE
932 , I.EQUIPMENT_TYPE
933 , I.RECOVERED_PART_DISP_CODE
934 , I.DEFECT_TRACKING_ON_FLAG
935 , I.EVENT_FLAG
936 , I.ELECTRONIC_FLAG
937 , I.DOWNLOADABLE_FLAG
938 , I.VOL_DISCOUNT_EXEMPT_FLAG
939 , I.COUPON_EXEMPT_FLAG
940 , I.COMMS_NL_TRACKABLE_FLAG
941 , I.ASSET_CREATION_CODE
942 , I.COMMS_ACTIVATION_REQD_FLAG
943 , I.ORDERABLE_ON_WEB_FLAG
944 , I.BACK_ORDERABLE_FLAG
945 , I.WEB_STATUS
946 , I.INDIVISIBLE_FLAG
947 , I.DIMENSION_UOM_CODE
948 , I.UNIT_LENGTH
949 , I.UNIT_WIDTH
950 , I.UNIT_HEIGHT
951 , I.BULK_PICKED_FLAG
952 , I.LOT_STATUS_ENABLED
953 , I.DEFAULT_LOT_STATUS_ID
954 , I.SERIAL_STATUS_ENABLED
955 , I.DEFAULT_SERIAL_STATUS_ID
956 , I.LOT_SPLIT_ENABLED
957 , I.LOT_MERGE_ENABLED
958 , I.INVENTORY_CARRY_PENALTY
959 , I.OPERATION_SLACK_PENALTY
960 , I.FINANCING_ALLOWED_FLAG
961 , I.EAM_ITEM_TYPE
962 , I.EAM_ACTIVITY_TYPE_CODE
963 , I.EAM_ACTIVITY_CAUSE_CODE
964 , I.EAM_ACT_NOTIFICATION_FLAG
965 , I.EAM_ACT_SHUTDOWN_STATUS
966 , I.DUAL_UOM_CONTROL
967 , I.SECONDARY_UOM_CODE
968 , I.DUAL_UOM_DEVIATION_HIGH
969 , I.DUAL_UOM_DEVIATION_LOW
970 --
971 -- Service Item, Warranty, Usage flag attributes are dependent on
972 -- and derived from Contract Item Type; supported for view only.
973 , DECODE( I.CONTRACT_ITEM_TYPE_CODE,
974 'SERVICE' , 'Y',
975 'WARRANTY' , 'Y', 'N' )
976 , DECODE( I.CONTRACT_ITEM_TYPE_CODE, 'WARRANTY', 'Y', 'N' )
977 , DECODE( I.CONTRACT_ITEM_TYPE_CODE, 'USAGE', 'Y', NULL )
978 --
979 , I.CONTRACT_ITEM_TYPE_CODE
980 , I.SUBSCRIPTION_DEPEND_FLAG
981 --
982 , I.SERV_REQ_ENABLED_CODE
983 , I.SERV_BILLING_ENABLED_FLAG
984 , I.SERV_IMPORTANCE_LEVEL
985 , I.PLANNED_INV_POINT_FLAG
986 , I.LOT_TRANSLATE_ENABLED
987 , I.DEFAULT_SO_SOURCE_TYPE
988 , I.CREATE_SUPPLY_FLAG
989 , I.SUBSTITUTION_WINDOW_CODE
990 , I.SUBSTITUTION_WINDOW_DAYS
991 --Added as part of 11.5.9
992 , I.LOT_SUBSTITUTION_ENABLED
993 , I.MINIMUM_LICENSE_QUANTITY
994 , I.EAM_ACTIVITY_SOURCE_CODE
995 , I.IB_ITEM_INSTANCE_CLASS
996 , I.CONFIG_MODEL_TYPE
997 --2740503: Lifecycle-phase introduced.
998 , I.LIFECYCLE_ID
999 , I.CURRENT_PHASE_ID
1000 --Added as part of 11.5.10
1001 , I.TRACKING_QUANTITY_IND
1002 , I.ONT_PRICING_QTY_SOURCE
1003 , I.SECONDARY_DEFAULT_IND
1004 , I.CONFIG_ORGS
1005 , I.CONFIG_MATCH
1006 , I.VMI_MINIMUM_UNITS
1007 , I.VMI_MINIMUM_DAYS
1008 , I.VMI_MAXIMUM_UNITS
1009 , I.VMI_MAXIMUM_DAYS
1010 , I.VMI_FIXED_ORDER_QUANTITY
1011 , I.SO_AUTHORIZATION_FLAG
1012 , I.CONSIGNED_FLAG
1013 , I.ASN_AUTOEXPIRE_FLAG
1014 , I.VMI_FORECAST_TYPE
1015 , I.FORECAST_HORIZON
1016 , I.EXCLUDE_FROM_BUDGET_FLAG
1017 , I.DAYS_TGT_INV_SUPPLY
1018 , I.DAYS_TGT_INV_WINDOW
1019 , I.DAYS_MAX_INV_SUPPLY
1020 , I.DAYS_MAX_INV_WINDOW
1021 , I.DRP_PLANNED_FLAG
1022 , I.CRITICAL_COMPONENT_FLAG
1023 , I.CONTINOUS_TRANSFER
1024 , I.CONVERGENCE
1025 , I.DIVERGENCE
1026 /* Start Bug 3713912 */
1027 , I.LOT_DIVISIBLE_FLAG
1028 , I.GRADE_CONTROL_FLAG
1029 , I.DEFAULT_GRADE
1030 , I.CHILD_LOT_FLAG
1031 , I.PARENT_CHILD_GENERATION_FLAG
1032 , I.CHILD_LOT_PREFIX
1033 , I.CHILD_LOT_STARTING_NUMBER
1034 , I.CHILD_LOT_VALIDATION_FLAG
1035 , I.COPY_LOT_ATTRIBUTE_FLAG
1036 , I.RECIPE_ENABLED_FLAG
1037 , I.PROCESS_QUALITY_ENABLED_FLAG
1038 , I.PROCESS_EXECUTION_ENABLED_FLAG
1039 , I.PROCESS_COSTING_ENABLED_FLAG
1040 , I.PROCESS_SUPPLY_SUBINVENTORY
1041 , I.PROCESS_SUPPLY_LOCATOR_ID
1042 , I.PROCESS_YIELD_SUBINVENTORY
1043 , I.PROCESS_YIELD_LOCATOR_ID
1044 , I.HAZARDOUS_MATERIAL_FLAG
1045 , I.CAS_NUMBER
1046 , I.RETEST_INTERVAL
1047 , I.EXPIRATION_ACTION_INTERVAL
1048 , I.EXPIRATION_ACTION_CODE
1049 , I.MATURITY_DAYS
1050 , I.HOLD_DAYS
1051 /* End Bug 3713912 */
1052 /* Bug 4224512 Updating the object version number - Anmurali */
1053 , 1
1054 , I.CHARGE_PERIODICITY_CODE
1055 , I.OUTSOURCED_ASSEMBLY
1056 , I.SUBCONTRACTING_COMPONENT
1057 , I.REPAIR_PROGRAM
1058 , I.REPAIR_LEADTIME
1059 , I.PREPOSITION_POINT
1060 , I.REPAIR_YIELD
1061 , I.GDSN_OUTBOUND_ENABLED_FLAG
1062 , I.TRADE_ITEM_DESCRIPTOR
1063 , I.STYLE_ITEM_FLAG
1064 , I.STYLE_ITEM_ID
1065 FROM
1066 MTL_SYSTEM_ITEMS_INTERFACE I
1067 WHERE
1068 I.process_flag = l_process_flag_4
1069 AND I.set_process_id = xset_id
1070 AND I.transaction_type = l_transaction_type;
1071
1072 IF l_inv_debug_level IN(101, 102) THEN
1073 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into MSI_TL with xset_id = '||xset_id);
1074 END IF;
1075
1076 -- R11.5 MLS
1077
1078 INSERT INTO MTL_SYSTEM_ITEMS_TL
1079 (
1080 INVENTORY_ITEM_ID,
1081 ORGANIZATION_ID,
1082 LANGUAGE,
1083 SOURCE_LANG,
1084 DESCRIPTION,
1085 LONG_DESCRIPTION,
1086 LAST_UPDATE_DATE,
1087 LAST_UPDATED_BY,
1088 CREATION_DATE,
1089 CREATED_BY,
1090 LAST_UPDATE_LOGIN
1091 )
1092 SELECT
1093 I.INVENTORY_ITEM_ID,
1094 I.ORGANIZATION_ID,
1095 L.LANGUAGE_CODE,
1096 userenv('LANG'),
1097 I.DESCRIPTION,
1098 I.LONG_DESCRIPTION,
1099 NVL(I.LAST_UPDATE_DATE, l_sysdate),
1100 user_id, /* last_updated_by */
1101 NVL(I.CREATION_DATE, l_sysdate),
1102 user_id, /* created_by */
1103 login_id /* last_update_login */
1104 from MTL_SYSTEM_ITEMS_INTERFACE I
1105 , FND_LANGUAGES L
1106 , mtl_parameters mp
1107 where I.process_flag = l_process_flag_4
1108 and I.set_process_id = xset_id
1109 and I.transaction_type = l_transaction_type
1110 and L.INSTALLED_FLAG in ('I', 'B')
1111 /*Bug 5398775 Restrict child org creates*/
1112 and I.Organization_Id = mp.Master_Organization_Id
1113 /*Bug 6983581 Performance Changes */
1114 -- and mp.master_organization_id = mp.organization_id;
1115 and I.Organization_Id = mp.organization_id;
1116
1117 /*Bug: 4667452 Commneting out the following condition
1118 and not exists
1119 ( select NULL
1120 from MTL_SYSTEM_ITEMS_TL T
1121 where T.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1122 and T.ORGANIZATION_ID = I.ORGANIZATION_ID
1123 and T.LANGUAGE = L.LANGUAGE_CODE
1124 );
1125 End comment Bug: 4667452*/
1126
1127 /*Bug 5398775 Create for child org with translations*/
1128 INSERT INTO MTL_SYSTEM_ITEMS_TL
1129 (
1130 INVENTORY_ITEM_ID,
1131 ORGANIZATION_ID,
1132 LANGUAGE,
1133 SOURCE_LANG,
1134 DESCRIPTION,
1135 LONG_DESCRIPTION,
1136 LAST_UPDATE_DATE,
1137 LAST_UPDATED_BY,
1138 CREATION_DATE,
1139 CREATED_BY,
1140 LAST_UPDATE_LOGIN
1141 )
1142 SELECT
1143 I.INVENTORY_ITEM_ID,
1144 I.ORGANIZATION_ID,
1145 L.LANGUAGE_CODE,
1146 Decode(L.LANGUAGE_CODE, userenv('LANG'), userenv('LANG'), msitl.source_lang) Source_Lang,
1147 Decode(L.LANGUAGE_CODE, userenv('LANG'), I.DESCRIPTION, msitl.description) Description,
1148 Decode(L.LANGUAGE_CODE, userenv('LANG'), I.LONG_DESCRIPTION, msitl.long_description) Long_Description,
1149 NVL(I.LAST_UPDATE_DATE, l_sysdate),
1150 user_id, --* last_updated_by
1151 NVL(I.CREATION_DATE, l_sysdate),
1152 user_id, --* created_by
1153 login_id --* last_update_login
1154 from MTL_SYSTEM_ITEMS_INTERFACE I
1155 , MTL_SYSTEM_ITEMS_TL msitl
1156 , FND_LANGUAGES L
1157 where I.process_flag = l_process_flag_4
1158 and I.set_process_id = xset_id
1159 and I.transaction_type = l_transaction_type
1160 and L.INSTALLED_FLAG in ('I', 'B')
1161 and msitl.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1162 and msitl.ORGANIZATION_ID = (SELECT Master_Organization_Id
1163 FROM Mtl_Parameters
1164 WHERE Organization_Id = I.Organization_id)
1165 and msitl.Language = l.language_code
1166 and I.ORGANIZATION_ID IN (SELECT Organization_Id
1167 FROM Mtl_Parameters
1168 WHERE Master_Organization_Id <> Organization_Id);
1169
1170 FOR cr IN c_ego_intf_rows LOOP
1171 UPDATE MTL_SYSTEM_ITEMS_TL
1172 SET DESCRIPTION = NVL(cr.column_value, DESCRIPTION),
1173 LAST_UPDATE_DATE = l_sysdate,
1174 LAST_UPDATED_BY = user_id,
1175 LAST_UPDATE_LOGIN = login_id
1176 WHERE inventory_item_id = cr.inventory_item_id
1177 AND organization_id = cr.organization_id
1178 AND language = cr.language;
1179 END LOOP;
1180
1181
1182 IF l_inv_debug_level IN(101, 102) THEN
1183 INVPUTLI.info('INVPPROC.inproit_process_item: deleting from MDEV with xset_id = '||xset_id);
1184 END IF;
1185
1186 -- Bug #1068191 (1031733)
1187 --
1188 delete from mtl_descr_element_values
1189 where inventory_item_id in
1190 ( select inventory_item_id
1191 from mtl_parameters MP,
1192 MTL_SYSTEM_ITEMS_INTERFACE I
1193 where I.process_flag = l_process_flag_4
1194 and I.set_process_id = xset_id
1195 and I.transaction_type = l_transaction_type
1196 and I.item_catalog_group_id is not null
1197 and I.organization_id = MP.organization_id
1198 and I.organization_id = MP.master_organization_id
1199 );
1200
1201 IF l_inv_debug_level IN(101, 102) THEN
1202 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into MDEV with xset_id = '|| xset_id);
1203 END IF;
1204
1205 table_name := 'MTL_DESCR_ELEMENT_VALUES';
1206
1207 insert into MTL_DESCR_ELEMENT_VALUES
1208 (inventory_item_id,
1209 element_name,
1210 default_element_flag,
1211 last_update_date,
1212 last_updated_by,
1213 creation_date,
1214 created_by,
1215 last_update_login,
1216 request_id,
1217 program_application_id,
1218 program_id,
1219 program_update_date,
1220 element_sequence)
1221 select
1222 I.INVENTORY_ITEM_ID,
1223 MDE.ELEMENT_NAME,
1224 MDE.default_element_flag,
1225 NVL(I.LAST_UPDATE_DATE,l_sysdate),
1226 user_id, /* last_updated_by */
1227 NVL(I.CREATION_DATE,l_sysdate),
1228 user_id, /* created_by */
1229 login_id, /* last_update_login */
1230 req_id,
1231 prg_appid,
1232 prg_id,
1233 l_sysdate,
1234 MDE.ELEMENT_SEQUENCE
1235 from mtl_descriptive_elements MDE,
1236 mtl_parameters MP,
1237 mtl_system_items_interface I
1238 where I.process_flag = l_process_flag_4
1239 and I.set_process_id = xset_id
1240 and I.transaction_type = l_transaction_type
1241 and I.organization_id = MP.master_organization_id
1242 and I.organization_id = MP.organization_id
1243 and MDE.item_catalog_group_id = nvl(I.item_catalog_group_id,-999) ;
1244
1245 /*
1246 ** COSTING WILL BE HANDLED BY NEW COSTING FUNCTION
1247 ** This code was obsoleted. Now removed in version 50.12 of this file
1248 */
1249
1250 /*
1251 ** Copy the item revisions into the item revisions table
1252 ** This does not cover the master record created for orphans
1253 ** NP 08SEP94 Comment There is no Orphan case allowed anymore
1254 ** with the Two_pass design
1255 */
1256
1257 IF l_inv_debug_level IN(101, 102) THEN
1258 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into MIR from MIRI');
1259 END IF;
1260
1261 table_name := 'MTL_ITEM_REVISIONS';
1262
1263 INSERT into MTL_ITEM_REVISIONS_B
1264 ( INVENTORY_ITEM_ID,
1265 ORGANIZATION_ID,
1266 REVISION,
1267 LAST_UPDATE_DATE,
1268 LAST_UPDATED_BY,
1269 CREATION_DATE,
1270 CREATED_BY,
1271 LAST_UPDATE_LOGIN,
1272 CHANGE_NOTICE,
1273 ECN_INITIATION_DATE,
1274 IMPLEMENTATION_DATE,
1275 IMPLEMENTED_SERIAL_NUMBER,
1276 EFFECTIVITY_DATE,
1277 ATTRIBUTE_CATEGORY,
1278 ATTRIBUTE1,
1279 ATTRIBUTE2,
1280 ATTRIBUTE3,
1281 ATTRIBUTE4,
1282 ATTRIBUTE5,
1283 ATTRIBUTE6,
1284 ATTRIBUTE7,
1285 ATTRIBUTE8,
1286 ATTRIBUTE9,
1287 ATTRIBUTE10,
1288 ATTRIBUTE11,
1289 ATTRIBUTE12,
1290 ATTRIBUTE13,
1291 ATTRIBUTE14,
1292 ATTRIBUTE15,
1293 REQUEST_ID,
1294 PROGRAM_APPLICATION_ID,
1295 PROGRAM_ID,
1296 PROGRAM_UPDATE_DATE,
1297 REVISED_ITEM_SEQUENCE_ID,
1298 DESCRIPTION,
1299 OBJECT_VERSION_NUMBER,
1300 LIFECYCLE_ID,
1301 CURRENT_PHASE_ID,
1302 REVISION_ID,
1303 REVISION_LABEL,
1304 REVISION_REASON)
1305 SELECT
1306 r.INVENTORY_ITEM_ID,
1307 r.ORGANIZATION_ID,
1308 r.REVISION,
1309 r.LAST_UPDATE_DATE,
1310 r.LAST_UPDATED_BY,
1311 r.CREATION_DATE,
1312 r.CREATED_BY,
1313 r.LAST_UPDATE_LOGIN,
1314 r.CHANGE_NOTICE,
1315 r.ECN_INITIATION_DATE,
1316 r.IMPLEMENTATION_DATE,
1317 r.IMPLEMENTED_SERIAL_NUMBER,
1318 r.EFFECTIVITY_DATE,
1319 r.ATTRIBUTE_CATEGORY,
1320 r.ATTRIBUTE1,
1321 r.ATTRIBUTE2,
1322 r.ATTRIBUTE3,
1323 r.ATTRIBUTE4,
1324 r.ATTRIBUTE5,
1325 r.ATTRIBUTE6,
1326 r.ATTRIBUTE7,
1327 r.ATTRIBUTE8,
1328 r.ATTRIBUTE9,
1329 r.ATTRIBUTE10,
1330 r.ATTRIBUTE11,
1331 r.ATTRIBUTE12,
1332 r.ATTRIBUTE13,
1333 r.ATTRIBUTE14,
1334 r.ATTRIBUTE15,
1335 req_id,
1336 prg_appid,
1337 prg_id,
1338 l_sysdate,
1339 r.REVISED_ITEM_SEQUENCE_ID,
1340 r.DESCRIPTION,
1341 1 OBJECT_VERSION_NUMBER,
1342 r.LIFECYCLE_ID,
1343 r.CURRENT_PHASE_ID,
1344 r.REVISION_ID,
1345 r.REVISION_LABEL,
1346 r.REVISION_REASON
1347 FROM MTL_ITEM_REVISIONS_INTERFACE R
1348 WHERE r.process_flag = l_process_flag_4
1349 AND r.set_process_id = xset_id
1350 AND r.transaction_type = l_transaction_type;
1351
1352
1353 INSERT INTO MTL_ITEM_REVISIONS_TL (
1354 INVENTORY_ITEM_ID,
1355 ORGANIZATION_ID,
1356 REVISION_ID,
1357 DESCRIPTION,
1358 CREATION_DATE,
1359 CREATED_BY,
1360 LAST_UPDATE_DATE,
1361 LAST_UPDATED_BY,
1362 LAST_UPDATE_LOGIN,
1363 LANGUAGE,
1364 SOURCE_LANG)
1365 SELECT r.INVENTORY_ITEM_ID,
1366 r.ORGANIZATION_ID,
1367 r.REVISION_ID,
1368 r.DESCRIPTION,
1369 r.CREATION_DATE,
1370 r.CREATED_BY,
1371 r.LAST_UPDATE_DATE,
1372 r.LAST_UPDATED_BY,
1373 r.LAST_UPDATE_LOGIN,
1374 L.LANGUAGE_CODE,
1375 USERENV('LANG')
1376 FROM MTL_ITEM_REVISIONS_INTERFACE r,
1377 FND_LANGUAGES L
1378 WHERE r.process_flag = l_process_flag_4
1379 AND r.set_process_id = xset_id
1380 AND r.transaction_type = l_transaction_type
1381 and L.INSTALLED_FLAG in ('I', 'B');
1382 /* Bug: 4667452 Removing the following condition
1383 AND NOT EXISTS (SELECT NULL
1384 FROM MTL_ITEM_REVISIONS_TL T
1385 WHERE T.INVENTORY_ITEM_ID = r.INVENTORY_ITEM_ID
1386 AND T.ORGANIZATION_ID = r.ORGANIZATION_ID
1387 AND T.REVISION_ID = r.REVISION_ID
1388 AND T.LANGUAGE = L.LANGUAGE_CODE);
1389 End Bug: 4667452 */
1390
1391
1392 /*NP 22AUG94 Commenting out the code for the following functionality
1393 ** If a master record was inserted for an orphan child record,
1394 ** we need to create a default
1395 ** record in mtl_item_revisions_interface in the
1396 ** master org (which is stored in last_updated_by
1397 ** The TWO-PASS approach makes it irrelevant
1398 ** Note the use of the last_updated_by who column
1399 ** This is initialized in INVPUTLI and holds the master_org_id
1400 **NP 26SEP95 Deleted the commented out code in version 50.12
1401 ** To see it refer to a prior version
1402 **INVPUTLI.info('INVPPROC: Skipped the orphan ins code in MTL_ITEM_REVISIONS');
1403 */
1404
1405 -- create child orgs category assignment
1406
1407 For Cat_Assign_Rec in Cat_Assign
1408 Loop
1409 INSERT INTO mtl_item_categories
1410 ( inventory_item_id,
1411 category_set_id,
1412 category_id,
1413 last_update_date,
1414 last_updated_by,
1415 creation_date,
1416 created_by,
1417 last_update_login,
1418 program_application_id,
1419 program_id,
1420 program_update_date,
1421 request_id,
1422 organization_id
1423 )
1424 SELECT
1425 Cat_Assign_Rec.ITEMID,
1426 s.category_set_id,
1427 s.category_id,
1428 l_sysdate,
1429 user_id, -- last_updated_by
1430 l_sysdate,
1431 user_id, -- created_by
1432 login_id, -- last_update_login
1433 prg_appid, -- program_application_id
1434 prg_id, -- program_id
1435 l_sysdate, -- program_update_date
1436 req_id, -- request_id
1437 Cat_Assign_Rec.ORGID
1438 FROM mtl_item_categories s,
1439 mtl_category_sets_b d
1440 WHERE s.inventory_item_id = Cat_Assign_Rec.ITEMID
1441 AND s.category_set_id = d.category_set_id
1442 AND s.organization_id = Cat_Assign_Rec.MORG
1443 AND (d.control_level = 1
1444 OR EXISTS
1445 ( SELECT 'x'
1446 FROM mtl_default_category_sets d
1447 WHERE
1448 d.category_set_id = s.category_set_id
1449 AND
1450 (d.functional_area_id = DECODE( Cat_Assign_Rec.INVFLAG, 'Y', 1, 0 )
1451 OR d.functional_area_id = DECODE( Cat_Assign_Rec.PURFLAG, 'Y', 2, 0 )
1452 OR d.functional_area_id = DECODE( Cat_Assign_Rec.INTFLAG, 'Y', 2, 0 )
1453 OR d.functional_area_id = DECODE( Cat_Assign_Rec.MRPCODE, 6, 0, 3 )
1454 OR d.functional_area_id = DECODE( Cat_Assign_Rec.SERVFLAG, 'Y', 4, 0 )
1455 OR d.functional_area_id = DECODE( Cat_Assign_Rec.COSTFLAG, 'Y', 5, 0 )
1456 OR d.functional_area_id = DECODE( Cat_Assign_Rec.ENGFLAG, 'Y', 6, 0 )
1457 OR d.functional_area_id = DECODE( Cat_Assign_Rec.CUSTFLAG, 'Y', 7, 0 )
1458 -- Add default Category assignment for GDSN Syndicated Items
1459 OR d.functional_area_id = DECODE( Cat_Assign_Rec.GDSNFLAG, 'Y',12,0)
1460 OR d.functional_area_id = DECODE( NVL(Cat_Assign_Rec.EAMTYPE, 0), 0, 0, 9 )
1461 OR d.functional_area_id =
1462 DECODE( Cat_Assign_Rec.CONTCODE,
1463 'SERVICE' , 10,
1464 'WARRANTY' , 10,
1465 'SUBSCRIPTION' , 10,
1466 'USAGE' , 10, 0 )
1467 OR d.functional_area_id =
1468 DECODE( Cat_Assign_Rec.CONTCODE,
1469 'SERVICE' , 4,
1470 'WARRANTY' , 4, 0 )
1471 OR d.functional_area_id = DECODE( Cat_Assign_Rec.CUSTFLAG, 'Y', 11, 0 )
1472 OR d.functional_area_id = DECODE( Cat_Assign_Rec.INTFLAG, 'Y', 11, 0 )) ))
1473 AND NOT EXISTS
1474 (SELECT 'already_exists'
1475 FROM mtl_item_categories mic
1476 WHERE mic.inventory_item_id = Cat_Assign_Rec.ITEMID
1477 AND mic.organization_id = Cat_Assign_Rec.ORGID
1478 AND mic.category_set_id = s.category_set_id);
1479
1480 End Loop;
1481
1482 --------------------------------------------------------
1483 -- Insert item assignments to default categories of the
1484 -- mandatory category sets for all functional areas.
1485 --------------------------------------------------------
1486 IF l_inv_debug_level IN(101, 102) THEN
1487 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into MIC from MCS/MSII');
1488 END IF;
1489
1490 /*
1491 SQL Modified to fix 4869915
1492 Cartesian Join eliminated.
1493 */
1494 /*
1495 Bug: 5050604 Added distinct
1496 */
1497
1498 INSERT INTO mtl_item_categories
1499 (
1500 inventory_item_id
1501 ,organization_id
1502 ,category_set_id
1503 ,category_id
1504 ,last_update_date
1505 ,last_updated_by
1506 ,creation_date
1507 ,created_by
1508 ,last_update_login
1509 ,program_application_id
1510 ,program_id
1511 ,program_update_date
1512 ,request_id
1513 )
1514 SELECT DISTINCT
1515 m.inventory_item_id
1516 ,m.organization_id
1517 ,d.category_set_id
1518 ,s.default_category_id
1519 ,l_sysdate
1520 ,user_id -- last_updated_by
1521 ,l_sysdate
1522 ,user_id -- created_by
1523 ,login_id -- last_update_login
1524 ,prg_appid -- program_application_id
1525 ,prg_id -- program_id
1526 ,l_sysdate -- program_update_date
1527 ,req_id -- request_id
1528 FROM mtl_system_items_interface m
1529 ,mtl_default_category_sets d
1530 ,mtl_category_sets_b s
1531 WHERE
1532 m.transaction_type = 'CREATE'
1533 AND m.process_flag = l_process_flag_4
1534 AND m.set_process_id = xset_id
1535 AND m.INVENTORY_ITEM_STATUS_CODE <> 'Pending'
1536 AND d.category_set_id = s.category_set_id
1537 AND s.default_category_id IS NOT NULL
1538 AND
1539 ( -- which all functional areas apply
1540 d.functional_area_id = DECODE( m.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
1541 OR d.functional_area_id = DECODE( m.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
1542 OR d.functional_area_id = DECODE( m.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
1543 OR d.functional_area_id = DECODE( m.MRP_PLANNING_CODE, 6, 0, 3 )
1544 OR d.functional_area_id = DECODE( m.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
1545 OR d.functional_area_id = DECODE( m.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
1546 OR d.functional_area_id = DECODE( m.ENG_ITEM_FLAG, 'Y', 6, 0 )
1547 OR d.functional_area_id = DECODE( m.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
1548 -- Add default Category assignment for GDSN Syndicated Items
1549 OR d.functional_area_id = DECODE( m.GDSN_OUTBOUND_ENABLED_FLAG, 'Y',12,0)
1550 OR d.functional_area_id = DECODE( NVL(m.EAM_ITEM_TYPE, 0), 0, 0, 9 )
1551 OR d.functional_area_id =
1552 DECODE( m.CONTRACT_ITEM_TYPE_CODE,
1553 'SERVICE' , 10,
1554 'WARRANTY' , 10,
1555 'SUBSCRIPTION' , 10,
1556 'USAGE' , 10, 0 )
1557 OR d.functional_area_id =
1558 DECODE( m.CONTRACT_ITEM_TYPE_CODE,
1559 'SERVICE' , 4,
1560 'WARRANTY' , 4, 0 )
1561 OR d.functional_area_id = DECODE( m.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )
1562 OR d.functional_area_id = DECODE( m.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1563 )
1564 AND NOT EXISTS
1565 (SELECT 'x'
1566 FROM mtl_item_categories mic
1567 WHERE mic.inventory_item_id = m.inventory_item_id
1568 AND mic.organization_id = m.organization_id
1569 AND mic.category_set_id = d.category_set_id );
1570
1571 --Bug: 5344163 Added this query for upward propogation
1572 -- of functional area Product Reporting
1573 INSERT INTO mtl_item_categories
1574 (
1575 inventory_item_id
1576 ,organization_id
1577 ,category_set_id
1578 ,category_id
1579 ,last_update_date
1580 ,last_updated_by
1581 ,creation_date
1582 ,created_by
1583 ,last_update_login
1584 ,program_application_id
1585 ,program_id
1586 ,program_update_date
1587 ,request_id
1588 )
1589 SELECT --DISTINCT
1590 msi.inventory_item_id
1591 ,mp.organization_id
1592 ,mdcs.category_set_id
1593 ,mcs.default_category_id
1594 ,l_sysdate
1595 ,user_id -- last_updated_by
1596 ,l_sysdate
1597 ,user_id -- created_by
1598 ,login_id -- last_update_login
1599 ,prg_appid -- program_application_id
1600 ,prg_id -- program_id
1601 ,l_sysdate -- program_update_date
1602 ,req_id -- request_id
1603 FROM mtl_system_items_interface msi
1604 ,mtl_default_category_sets mdcs
1605 ,mtl_category_sets_b mcs
1606 ,mtl_parameters mp
1607 WHERE
1608 msi.transaction_type = 'CREATE'
1609 AND msi.process_flag = l_process_flag_4
1610 AND msi.set_process_id = xset_id
1611 AND msi.INVENTORY_ITEM_STATUS_CODE <> 'Pending'
1612 AND mdcs.category_set_id = mcs.category_set_id
1613 AND mp.master_organization_id = (select master_organization_id
1614 FROM mtl_parameters m
1615 where m.organization_id = msi.organization_id)
1616 AND mcs.default_category_id IS NOT NULL
1617 AND
1618 ( -- which all functional areas apply
1619 mdcs.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )
1620 OR mdcs.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1621 )
1622 AND NOT EXISTS
1623 (SELECT 'x'
1624 FROM mtl_item_categories mic
1625 WHERE mic.inventory_item_id = msi.inventory_item_id
1626 AND mic.organization_id = mp.organization_id
1627 AND mic.category_set_id = mdcs.category_set_id )
1628 AND EXISTS
1629 ( SELECT 'x'
1630 FROM mtl_system_items_b i
1631 WHERE
1632 i.inventory_item_id = msi.inventory_item_id
1633 AND i.organization_id = mp.organization_id
1634 );
1635
1636 ---------------------------------------------
1637 -- Insert into MTL_PENDING_ITEM_STATUS table
1638 ---------------------------------------------
1639 IF l_inv_debug_level IN(101, 102) THEN
1640 INVPUTLI.info('INVPPROC.inproit_process_item: inserting into mtl_pending_item_status');
1641 END IF;
1642
1643 l_pending_flag := 'N';
1644 -- l_last_updated_by := 0;
1645 -- l_created_by := 0;
1646
1647 INSERT INTO mtl_pending_item_status
1648 (
1649 INVENTORY_ITEM_ID,
1650 ORGANIZATION_ID,
1651 STATUS_CODE,
1652 EFFECTIVE_DATE,
1653 PENDING_FLAG,
1654 LAST_UPDATE_DATE,
1655 LAST_UPDATED_BY,
1656 CREATION_DATE,
1657 CREATED_BY,
1658 IMPLEMENTED_DATE,
1659 --2740503: Lifecycle-phase introduced.
1660 LIFECYCLE_ID,
1661 PHASE_ID
1662 )
1663 select
1664 I.INVENTORY_ITEM_ID,
1665 I.ORGANIZATION_ID,
1666 I.INVENTORY_ITEM_STATUS_CODE,
1667 l_sysdate,
1668 l_pending_flag,
1669 l_sysdate,
1670 user_id,
1671 l_sysdate,
1672 user_id,
1673 l_sysdate,
1674 --2740503: Lifecycle-phase introduced.
1675 I.LIFECYCLE_ID,
1676 I.CURRENT_PHASE_ID
1677 from MTL_SYSTEM_ITEMS_INTERFACE I
1678 where I.process_flag = l_process_flag_4
1679 and I.set_process_id = xset_id
1680 and I.transaction_type = l_transaction_type;
1681
1682 /*
1683 ** For child records that are NOT orphans,
1684 ** we must insert child category records in mtl_item_categories
1685 ** for each category that its master is in.
1686 ** Default categories are assigned earlier..but for child records we also
1687 ** need to assign the categories that
1688 ** the parent has..and also make sure that we do not assign duplicate values
1689 ** because the parent may also have
1690 ** the default value that was assigned to child earlier.
1691 **
1692 **NP 26AUG94 commenting out for now ..REOPEN later when testing categories..
1693 ** also..optimize it Also check if msii.created_by in (1,2) is
1694 ** valid anymore with TWO_PASS design
1695 ** This stmt is a BIG resource hog..have to fix it
1696 **NP 06MAY96 Note that IOI does not support categories
1697 ** assignment in 10.4, 10.5, 10.6 anyway.
1698 ** Only default category assignment for mandatory catg set is done by IOI in INVPPROB.pls.
1699 **
1700 ** insert into MTL_ITEM_CATEGORIES
1701 ** ( inventory_item_id,
1702 ** category_set_id,
1703 ** category_id,
1704 ** last_update_date,
1705 ** last_updated_by,
1706 ** creation_date,
1707 ** created_by,
1708 ** last_update_login,
1709 ** request_id,
1710 ** program_application_id,
1711 ** program_id,
1712 ** program_update_date,
1713 ** organization_id
1714 ** )
1715 ** select
1716 ** msii.inventory_item_id,
1717 ** mic.category_set_id,
1718 ** mic.category_id,
1719 ** sysdate,
1720 ** user_id,
1721 ** sysdate,
1722 ** user_id,
1723 ** login_id,
1724 ** req_id,
1725 ** prg_appid,
1726 ** prg_id,
1727 ** sysdate,
1728 ** msii.organization_id
1729 ** from mtl_system_items_interface msii,
1730 ** mtl_item_categories mic,
1731 ** MTL_PARAMETERS MP
1732 ** where msii.process_flag = 4
1733 ** and msii.transaction_type = 'CREATE'
1734 ** and msii.created_by in (1,2)
1735 ** and mic.organization_id = msii.last_updated_by
1736 ** and mic.inventory_item_id = msii.inventory_item_id
1737 ** and msii.inventory_item_id not in
1738 ** (select mic2.inventory_item_id
1739 ** from mtl_item_categories mic2
1740 ** where mic2.category_set_id = mic.category_set_id
1741 ** and mic2.category_id = category_id
1742 ** and mic2.organization_id = msii.organization_id
1743 ** and mic2.inventory_item_id = msii.inventory_item_id);
1744 */
1745
1746
1747 /*
1748 ** Insert a record into mtl_uom_conversions for items with
1749 ** specific UOM (allowed_units_lookup_code = 1)
1750 */
1751
1752 FOR UOM_process_rec IN UOM_Process LOOP
1753
1754 IF ( UOM_process_rec.base_uom_flag = 'Y' ) THEN
1755 conversion_rate_temp := 1;
1756 ELSE
1757 select conversion_rate
1758 into conversion_rate_temp
1759 from mtl_uom_conversions
1760 where inventory_item_id = 0
1761 and uom_code = UOM_process_rec.PUOMCODE;
1762 END IF;
1763
1764 l_default_conversion_flag := 'N';
1765
1766 INSERT INTO mtl_uom_conversions
1767 (
1768 unit_of_measure,
1769 uom_code,
1770 uom_class,
1771 inventory_item_id,
1772 conversion_rate,
1773 default_conversion_flag,
1774 last_update_date,
1775 last_updated_by,
1776 creation_date,
1777 created_by
1778 )
1779 VALUES
1780 (
1781 UOM_process_rec.PUOM,
1782 UOM_process_rec.PUOMCODE,
1783 UOM_process_rec.UOMCL,
1784 UOM_process_rec.INV_ITEM_ID,
1785 conversion_rate_temp,
1786 l_default_conversion_flag,
1787 l_sysdate,
1788 user_id,
1789 l_sysdate,
1790 user_id
1791 );
1792
1793 END LOOP;
1794
1795 /** Need to put in error handling within the loop
1796 ** cannot just put in status := INVPUOPI.mtl_log_interface_err(
1797 cr.organization_id,
1798 user_id,
1799 login_id,
1800 prog_appid,
1801 prog_id,
1802 request_id,
1803 cr.TRANSACTION_ID,
1804 error_msg,
1805 'CAT_ID',
1806 'MTL_ITEM_CATEGORIES_INTERFACE',
1807 'BOM_OP_VALIDATION_ERR',
1808 err_text);
1809
1810 To be explored as an option**/
1811
1812
1813 /* 26SEP95 Explored the created_by strategy that
1814 ** was used to enhance performance
1815 ** 26SEP95 Removed the following from the statement above
1816 ** This is obsolete since the TWO_PASS design assumes that the master is in
1817 ** Also added the where not exists clause
1818 ** And of course, removed mtl_parameters mp from the FROM clause
1819 **
1820 ** and ((msii.ORGANIZATION_ID = MP.ORGANIZATION_ID) OR
1821 ** ((MP.ORGANIZATION_ID =
1822 ** (select MASTER_ORGANIZATION_ID
1823 ** from MTL_PARAMETERS MP
1824 ** where msii.ORGANIZATION_ID = MP.ORGANIZATION_ID)) and
1825 ** msii.created_by = 0))
1826 **
1827 ** Finally moved it all into a cursor UOM_process so that individual rows
1828 ** can get the values of conversion_type as needed
1829 ** So can't really batch process this insert as in other cases.
1830 ** This is a major fix to the code.
1831 */
1832
1833 IF l_inv_debug_level IN(101, 102) THEN
1834 INVPUTLI.info('INVPPROC: Finished inserting in mtl_uom_conversions');
1835 END IF;
1836
1837
1838
1839 /*
1840 ** call costing package to do costing
1841 ** NP 06MAY96 Added new parameter xset_id to call to CSTPIICP
1842 */
1843
1844 IF l_inv_debug_level IN(101, 102) THEN
1845 INVPUTLI.info('INVPPROC: Calling Costing procedure CSTPIICP');
1846 END IF;
1847
1848 INVPCOII.CSTPIICP(user_id,
1849 login_id,
1850 req_id,
1851 prg_id,
1852 prg_appid,
1853 return_code,
1854 return_err,
1855 xset_id);
1856
1857 if (return_code <> 0) then
1858 raise COST_ERR;
1859 end if;
1860
1861 --Bug: 3033702 Added for EGO grants on item
1862 --Moved support of user attribs code to INVEGRVB
1863 --INVPUTLI.info('INVPPROC: Calling Ego procedure Insert_Grants_And_UserAttr');
1864 --INV_EGO_REVISION_VALIDATE.Insert_Grants_And_UserAttr(xset_id);
1865 --Bug: 3033702 Ended
1866 INV_EGO_REVISION_VALIDATE.Create_New_Item_Request(xset_id);
1867 --Bug:3777954 added call to new processing for NIR required items (for EGO)
1868
1869 --
1870 -- The last step: set process_flags to 7
1871 --
1872
1873 table_name := 'MTL_SYSTEM_ITEMS_INTERFACE';
1874
1875 update MTL_SYSTEM_ITEMS_INTERFACE
1876 set process_flag = l_process_flag_7,
1877 request_id = nvl(request_id,req_id),
1878 program_application_id = nvl(program_application_id,prg_appid),
1879 PROGRAM_ID = nvl(PROGRAM_ID,prg_id),
1880 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1881 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1882 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1883 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1884 CREATED_BY = nvl(CREATED_BY,user_id),
1885 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1886 where process_flag = l_process_flag_4
1887 and set_process_id = xset_id;
1888
1889 table_name := 'MTL_ITEM_REVISIONS_INTERFACE';
1890
1891 update MTL_ITEM_REVISIONS_INTERFACE
1892 set process_flag = l_process_flag_7,
1893 request_id = nvl(request_id,req_id),
1894 program_application_id = nvl(program_application_id,prg_appid),
1895 PROGRAM_ID = nvl(PROGRAM_ID,prg_id),
1896 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1897 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1898 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1899 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1900 CREATED_BY = nvl(CREATED_BY,user_id),
1901 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1902 where process_flag = l_process_flag_4
1903 and set_process_id = xset_id;
1904
1905 --Bug 5435229 Call appy_default_uda_values
1906 INV_EGO_REVISION_VALIDATE.apply_default_uda_values(xset_id);
1907
1908 RETURN (0);
1909
1910 EXCEPTION
1911
1912 WHEN no_data_found THEN
1913 RETURN (0);
1914
1915 WHEN cost_err THEN
1916 error_message := SUBSTR('INVPPROC.inproit_process_item: ' || return_err, 1,240);
1917 message_name := 'COST_ERROR';
1918 RETURN (1);
1919
1920 WHEN LOGGING_ERR THEN
1921 error_message := SUBSTR('INVPPROC.inproit_process_item : Logging Error',1,240);
1922 message_name := 'LOGGING_ERROR';
1923 RETURN (1);
1924
1925 WHEN others THEN
1926 IF l_inv_debug_level IN(101, 102) THEN
1927 INVPUTLI.info('INVPPROC: Exception during INSERT '||FND_MESSAGE.GET);
1928 END IF;
1929 error_message := SUBSTR('INVPPROC.inproit_process_item ' || sqlerrm, 1,240);
1930 message_name := 'OTHER_INVPPROC_ERROR';
1931 RETURN (1);
1932
1933 END inproit_process_item;
1934
1935
1936 END INVPPROC;