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