[Home] [Help]
PACKAGE BODY: APPS.INVPPRCI
Source
4 function inproit_process_item (
1 package body invpprci as
2 /* $Header: INVPRCIB.pls 120.1 2005/06/21 04:36:36 appldev ship $ */
3
5 ato_flag in NUMBER,
6 prg_appid in NUMBER,
7 prg_id in NUMBER,
8 req_id in NUMBER,
9 user_id in NUMBER,
10 login_id in NUMBER,
11 error_message out NOCOPY VARCHAR2,
12 message_name out NOCOPY VARCHAR2,
13 table_name out NOCOPY VARCHAR2)
14 return integer
15 is
16 validation_org NUMBER;
17 l_cost_group_id NUMBER;
18
19 /** Need to define a cursor to get project_id for each config item **/
20
21 cursor allconfig is
22 select sla.line_id, si.organization_id,sla.project_id
23 from so_headers_all SOH,
24 mtl_system_items_interface SI,
25 mtl_sales_orders mso,
26 so_order_types_all sot,
27 so_lines_all sla
28 where si.demand_source_header_id = mso.sales_order_id
29 and mso.segment1 = soh.order_number
30 and soh.order_type_id = sot.order_type_id
31 and sot.name = mso.segment2
32 and sla.line_id = si.demand_source_line
33 and si.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')));
34 BEGIN
35
36 /* Yet another addition thanks to "Order from Chaos" */
37 /* we need to have this configuration item created */
38 /* in the validation organization as well so if */
39 /* their val org is different from master org, it */
40 /* will still be visible */
41
42 /* Time for a kludge here. We will pass in the profile value */
43 /* in the ato_flag parameter, so that we can use C api in */
44 /* bmlcci.ppc to get the profile option setting */
45 /* ato_flag is not being used anywhere else */
46
47 validation_org := ato_flag;
48
49 /* Copy the configuration item in the item interface table
50 into the inventory system master table */
51
52 table_name := 'MTL_SYSTEM_ITEMS';
53
54 insert into MTL_SYSTEM_ITEMS_B
55 (INVENTORY_ITEM_ID,
56 ORGANIZATION_ID,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_LOGIN,
62 SUMMARY_FLAG,
63 ENABLED_FLAG,
64 START_DATE_ACTIVE,
65 END_DATE_ACTIVE,
66 DESCRIPTION,
67 BUYER_ID,
68 ACCOUNTING_RULE_ID,
69 INVOICING_RULE_ID,
70 SEGMENT1,
71 SEGMENT2,
72 SEGMENT3,
73 SEGMENT4,
74 SEGMENT5,
75 SEGMENT6,
76 SEGMENT7,
77 SEGMENT8,
78 SEGMENT9,
79 SEGMENT10,
80 SEGMENT11,
81 SEGMENT12,
82 SEGMENT13,
83 SEGMENT14,
84 SEGMENT15,
85 SEGMENT16,
86 SEGMENT17,
87 SEGMENT18,
88 SEGMENT19,
89 SEGMENT20,
90 ATTRIBUTE_CATEGORY,
91 ATTRIBUTE1,
92 ATTRIBUTE2,
93 ATTRIBUTE3,
94 ATTRIBUTE4,
95 ATTRIBUTE5,
96 ATTRIBUTE6,
97 ATTRIBUTE7,
98 ATTRIBUTE8,
99 ATTRIBUTE9,
100 ATTRIBUTE10,
101 ATTRIBUTE11,
102 ATTRIBUTE12,
103 ATTRIBUTE13,
104 ATTRIBUTE14,
105 ATTRIBUTE15,
106 PURCHASING_ITEM_FLAG,
107 SHIPPABLE_ITEM_FLAG,
108 CUSTOMER_ORDER_FLAG,
109 INTERNAL_ORDER_FLAG,
110 SERVICE_ITEM_FLAG,
111 INVENTORY_ITEM_FLAG,
112 ENG_ITEM_FLAG,
113 INVENTORY_ASSET_FLAG,
114 PURCHASING_ENABLED_FLAG,
115 CUSTOMER_ORDER_ENABLED_FLAG,
119 STOCK_ENABLED_FLAG,
116 INTERNAL_ORDER_ENABLED_FLAG,
117 SO_TRANSACTIONS_FLAG,
118 MTL_TRANSACTIONS_ENABLED_FLAG,
120 BOM_ENABLED_FLAG,
121 BUILD_IN_WIP_FLAG,
122 REVISION_QTY_CONTROL_CODE,
123 ITEM_CATALOG_GROUP_ID,
124 CATALOG_STATUS_FLAG,
125 RETURNABLE_FLAG,
126 DEFAULT_SHIPPING_ORG,
127 COLLATERAL_FLAG,
128 TAXABLE_FLAG,
129 ALLOW_ITEM_DESC_UPDATE_FLAG,
130 INSPECTION_REQUIRED_FLAG,
131 RECEIPT_REQUIRED_FLAG,
132 MARKET_PRICE,
133 HAZARD_CLASS_ID,
134 RFQ_REQUIRED_FLAG,
135 QTY_RCV_TOLERANCE,
136 LIST_PRICE_PER_UNIT,
137 UN_NUMBER_ID,
138 PRICE_TOLERANCE_PERCENT,
139 ASSET_CATEGORY_ID,
140 ROUNDING_FACTOR,
141 UNIT_OF_ISSUE,
142 ENFORCE_SHIP_TO_LOCATION_CODE,
143 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
144 ALLOW_UNORDERED_RECEIPTS_FLAG,
145 ALLOW_EXPRESS_DELIVERY_FLAG,
146 DAYS_EARLY_RECEIPT_ALLOWED,
147 DAYS_LATE_RECEIPT_ALLOWED,
148 RECEIPT_DAYS_EXCEPTION_CODE,
149 RECEIVING_ROUTING_ID,
150 INVOICE_CLOSE_TOLERANCE,
151 RECEIVE_CLOSE_TOLERANCE,
152 AUTO_LOT_ALPHA_PREFIX,
153 START_AUTO_LOT_NUMBER,
154 LOT_CONTROL_CODE,
155 SHELF_LIFE_CODE,
156 SHELF_LIFE_DAYS,
157 SERIAL_NUMBER_CONTROL_CODE,
158 START_AUTO_SERIAL_NUMBER,
159 AUTO_SERIAL_ALPHA_PREFIX,
160 SOURCE_TYPE,
161 SOURCE_ORGANIZATION_ID,
162 SOURCE_SUBINVENTORY,
163 EXPENSE_ACCOUNT,
164 ENCUMBRANCE_ACCOUNT,
165 RESTRICT_SUBINVENTORIES_CODE,
166 UNIT_WEIGHT,
167 WEIGHT_UOM_CODE,
168 VOLUME_UOM_CODE,
169 UNIT_VOLUME,
170 RESTRICT_LOCATORS_CODE,
171 LOCATION_CONTROL_CODE,
172 SHRINKAGE_RATE,
173 ACCEPTABLE_EARLY_DAYS,
174 PLANNING_TIME_FENCE_CODE,
175 DEMAND_TIME_FENCE_CODE,
176 LEAD_TIME_LOT_SIZE,
177 STD_LOT_SIZE,
178 CUM_MANUFACTURING_LEAD_TIME,
179 OVERRUN_PERCENTAGE,
180 ACCEPTABLE_RATE_INCREASE,
181 ACCEPTABLE_RATE_DECREASE,
182 CUMULATIVE_TOTAL_LEAD_TIME,
183 PLANNING_TIME_FENCE_DAYS,
184 DEMAND_TIME_FENCE_DAYS,
185 END_ASSEMBLY_PEGGING_FLAG,
186 PLANNING_EXCEPTION_SET,
187 BOM_ITEM_TYPE,
188 PICK_COMPONENTS_FLAG,
189 REPLENISH_TO_ORDER_FLAG,
190 BASE_ITEM_ID,
191 ATP_COMPONENTS_FLAG,
192 ATP_FLAG,
193 FIXED_LEAD_TIME,
194 VARIABLE_LEAD_TIME,
195 WIP_SUPPLY_LOCATOR_ID,
196 WIP_SUPPLY_TYPE,
197 WIP_SUPPLY_SUBINVENTORY,
198 PRIMARY_UOM_CODE,
199 PRIMARY_UNIT_OF_MEASURE,
200 ALLOWED_UNITS_LOOKUP_CODE,
201 COST_OF_SALES_ACCOUNT,
202 SALES_ACCOUNT,
203 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
204 INVENTORY_ITEM_STATUS_CODE,
205 INVENTORY_PLANNING_CODE,
206 PLANNER_CODE,
207 PLANNING_MAKE_BUY_CODE,
208 FIXED_LOT_MULTIPLIER,
209 ROUNDING_CONTROL_TYPE,
210 CARRYING_COST,
211 POSTPROCESSING_LEAD_TIME,
212 PREPROCESSING_LEAD_TIME,
213 FULL_LEAD_TIME,
214 ORDER_COST,
215 MRP_SAFETY_STOCK_PERCENT,
216 MRP_SAFETY_STOCK_CODE,
217 MIN_MINMAX_QUANTITY,
218 MAX_MINMAX_QUANTITY,
219 MINIMUM_ORDER_QUANTITY,
220 FIXED_ORDER_QUANTITY,
221 FIXED_DAYS_SUPPLY,
222 MAXIMUM_ORDER_QUANTITY,
223 ATP_RULE_ID,
224 PICKING_RULE_ID,
225 RESERVABLE_TYPE,
226 POSITIVE_MEASUREMENT_ERROR,
227 NEGATIVE_MEASUREMENT_ERROR,
228 ENGINEERING_ECN_CODE,
229 ENGINEERING_ITEM_ID,
230 ENGINEERING_DATE,
231 SERVICE_STARTING_DELAY,
232 VENDOR_WARRANTY_FLAG,
233 SERVICEABLE_COMPONENT_FLAG,
234 SERVICEABLE_PRODUCT_FLAG,
235 BASE_WARRANTY_SERVICE_ID,
236 PAYMENT_TERMS_ID,
237 PREVENTIVE_MAINTENANCE_FLAG,
238 PRIMARY_SPECIALIST_ID,
239 SECONDARY_SPECIALIST_ID,
240 SERVICEABLE_ITEM_CLASS_ID,
241 TIME_BILLABLE_FLAG,
242 MATERIAL_BILLABLE_FLAG,
243 EXPENSE_BILLABLE_FLAG,
244 PRORATE_SERVICE_FLAG,
245 COVERAGE_SCHEDULE_ID,
246 SERVICE_DURATION_PERIOD_CODE,
247 SERVICE_DURATION,
248 MAX_WARRANTY_AMOUNT,
249 RESPONSE_TIME_PERIOD_CODE,
250 RESPONSE_TIME_VALUE,
251 NEW_REVISION_CODE,
252 TAX_CODE,
253 MUST_USE_APPROVED_VENDOR_FLAG,
254 SAFETY_STOCK_BUCKET_DAYS,
255 AUTO_REDUCE_MPS,
256 COSTING_ENABLED_FLAG,
257 INVOICEABLE_ITEM_FLAG,
258 INVOICE_ENABLED_FLAG,
259 OUTSIDE_OPERATION_FLAG,
260 OUTSIDE_OPERATION_UOM_TYPE,
261 AUTO_CREATED_CONFIG_FLAG,
262 CYCLE_COUNT_ENABLED_FLAG,
263 ITEM_TYPE,
264 MODEL_CONFIG_CLAUSE_NAME,
265 SHIP_MODEL_COMPLETE_FLAG,
266 MRP_PLANNING_CODE,
267 REPETITIVE_PLANNING_FLAG,
268 RETURN_INSPECTION_REQUIREMENT,
269 EFFECTIVITY_CONTROL,
270 REQUEST_ID,
271 PROGRAM_APPLICATION_ID,
272 PROGRAM_ID,
273 PROGRAM_UPDATE_DATE
274 )
275 select
276 I.INVENTORY_ITEM_ID,
277 MP1.ORGANIZATION_ID,
278 NVL(I.LAST_UPDATE_DATE,SYSDATE),
279 user_id, /* last_updated_by */
280 NVL(I.CREATION_DATE,SYSDATE),
284 NVL(I.ENABLED_FLAG,M.ENABLED_FLAG),
281 user_id, /* created_by */
282 login_id, /* last_update_login */
283 NVL(I.SUMMARY_FLAG,M.SUMMARY_FLAG),
285 NVL(I.START_DATE_ACTIVE,M.START_DATE_ACTIVE),
286 NVL(I.END_DATE_ACTIVE,M.END_DATE_ACTIVE),
287 NVL(I.DESCRIPTION,M.DESCRIPTION),
288 NVL(I.BUYER_ID,M.BUYER_ID),
289 NVL(I.ACCOUNTING_RULE_ID,M.ACCOUNTING_RULE_ID),
290 NVL(I.INVOICING_RULE_ID,M.INVOICING_RULE_ID),
291 I.SEGMENT1,
292 I.SEGMENT2,
293 I.SEGMENT3,
294 I.SEGMENT4,
295 I.SEGMENT5,
296 I.SEGMENT6,
297 I.SEGMENT7,
298 I.SEGMENT8,
299 I.SEGMENT9,
300 I.SEGMENT10,
301 I.SEGMENT11,
302 I.SEGMENT12,
303 I.SEGMENT13,
304 I.SEGMENT14,
305 I.SEGMENT15,
306 I.SEGMENT16,
307 I.SEGMENT17,
308 I.SEGMENT18,
309 I.SEGMENT19,
310 I.SEGMENT20,
311 NVL(I.ATTRIBUTE_CATEGORY,M.ATTRIBUTE_CATEGORY),
312 NVL(I.ATTRIBUTE1,M.ATTRIBUTE1),
313 NVL(I.ATTRIBUTE2,M.ATTRIBUTE2),
314 NVL(I.ATTRIBUTE3,M.ATTRIBUTE3),
315 NVL(I.ATTRIBUTE4,M.ATTRIBUTE4),
316 NVL(I.ATTRIBUTE5,M.ATTRIBUTE5),
317 NVL(I.ATTRIBUTE6,M.ATTRIBUTE6),
318 NVL(I.ATTRIBUTE7,M.ATTRIBUTE7),
319 NVL(I.ATTRIBUTE8,M.ATTRIBUTE8),
320 NVL(I.ATTRIBUTE9,M.ATTRIBUTE9),
321 NVL(I.ATTRIBUTE10,M.ATTRIBUTE10),
322 NVL(I.ATTRIBUTE11,M.ATTRIBUTE11),
323 NVL(I.ATTRIBUTE12,M.ATTRIBUTE12),
324 NVL(I.ATTRIBUTE13,M.ATTRIBUTE13),
325 NVL(I.ATTRIBUTE14,M.ATTRIBUTE14),
326 NVL(I.ATTRIBUTE15,M.ATTRIBUTE15),
327 NVL(I.PURCHASING_ITEM_FLAG,M.PURCHASING_ITEM_FLAG),
328 NVL(I.SHIPPABLE_ITEM_FLAG,M.SHIPPABLE_ITEM_FLAG),
329 NVL(I.CUSTOMER_ORDER_FLAG,M.CUSTOMER_ORDER_FLAG),
330 NVL(I.INTERNAL_ORDER_FLAG,M.INTERNAL_ORDER_FLAG),
331 NVL(I.SERVICE_ITEM_FLAG,M.SERVICE_ITEM_FLAG),
332 NVL(I.INVENTORY_ITEM_FLAG,M.INVENTORY_ITEM_FLAG),
333 NVL(I.ENG_ITEM_FLAG,M.ENG_ITEM_FLAG),
334 NVL(I.INVENTORY_ASSET_FLAG,M.INVENTORY_ASSET_FLAG),
335 NVL(I.PURCHASING_ENABLED_FLAG,M.PURCHASING_ENABLED_FLAG),
336 NVL(I.CUSTOMER_ORDER_ENABLED_FLAG,M.CUSTOMER_ORDER_ENABLED_FLAG),
337 NVL(I.INTERNAL_ORDER_ENABLED_FLAG,M.INTERNAL_ORDER_ENABLED_FLAG),
338 NVL(I.SO_TRANSACTIONS_FLAG,M.SO_TRANSACTIONS_FLAG),
339 NVL(I.MTL_TRANSACTIONS_ENABLED_FLAG,M.MTL_TRANSACTIONS_ENABLED_FLAG),
340 NVL(I.STOCK_ENABLED_FLAG,M.STOCK_ENABLED_FLAG),
341 NVL(I.BOM_ENABLED_FLAG,M.BOM_ENABLED_FLAG),
342 NVL(I.BUILD_IN_WIP_FLAG,M.BUILD_IN_WIP_FLAG),
343 NVL(I.REVISION_QTY_CONTROL_CODE,M.REVISION_QTY_CONTROL_CODE),
344 NVL(I.ITEM_CATALOG_GROUP_ID,M.ITEM_CATALOG_GROUP_ID),
345 NVL(I.CATALOG_STATUS_FLAG,M.CATALOG_STATUS_FLAG),
346 NVL(I.RETURNABLE_FLAG,M.RETURNABLE_FLAG),
347 NVL(I.DEFAULT_SHIPPING_ORG,M.DEFAULT_SHIPPING_ORG),
348 NVL(I.COLLATERAL_FLAG,M.COLLATERAL_FLAG),
349 NVL(I.TAXABLE_FLAG,M.TAXABLE_FLAG),
350 NVL(I.ALLOW_ITEM_DESC_UPDATE_FLAG,M.ALLOW_ITEM_DESC_UPDATE_FLAG),
351 NVL(I.INSPECTION_REQUIRED_FLAG,M.INSPECTION_REQUIRED_FLAG),
352 NVL(I.RECEIPT_REQUIRED_FLAG,M.RECEIPT_REQUIRED_FLAG),
353 NVL(I.MARKET_PRICE,M.MARKET_PRICE),
354 NVL(I.HAZARD_CLASS_ID,M.HAZARD_CLASS_ID),
355 NVL(I.RFQ_REQUIRED_FLAG,M.RFQ_REQUIRED_FLAG),
356 NVL(I.QTY_RCV_TOLERANCE,M.QTY_RCV_TOLERANCE),
357 NVL(I.LIST_PRICE_PER_UNIT,M.LIST_PRICE_PER_UNIT),
358 NVL(I.UN_NUMBER_ID,M.UN_NUMBER_ID),
359 NVL(I.PRICE_TOLERANCE_PERCENT,M.PRICE_TOLERANCE_PERCENT),
360 NVL(I.ASSET_CATEGORY_ID,M.ASSET_CATEGORY_ID),
361 NVL(I.ROUNDING_FACTOR,M.ROUNDING_FACTOR),
362 NVL(I.UNIT_OF_ISSUE,M.UNIT_OF_ISSUE),
363 NVL(I.ENFORCE_SHIP_TO_LOCATION_CODE,M.ENFORCE_SHIP_TO_LOCATION_CODE),
364 NVL(I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,M.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
365 NVL(I.ALLOW_UNORDERED_RECEIPTS_FLAG,M.ALLOW_UNORDERED_RECEIPTS_FLAG),
366 NVL(I.ALLOW_EXPRESS_DELIVERY_FLAG,M.ALLOW_EXPRESS_DELIVERY_FLAG),
367 NVL(I.DAYS_EARLY_RECEIPT_ALLOWED,M.DAYS_EARLY_RECEIPT_ALLOWED),
368 NVL(I.DAYS_LATE_RECEIPT_ALLOWED,M.DAYS_LATE_RECEIPT_ALLOWED),
369 NVL(I.RECEIPT_DAYS_EXCEPTION_CODE,M.RECEIPT_DAYS_EXCEPTION_CODE),
370 NVL(I.RECEIVING_ROUTING_ID,M.RECEIVING_ROUTING_ID),
371 NVL(I.INVOICE_CLOSE_TOLERANCE,M.INVOICE_CLOSE_TOLERANCE),
372 NVL(I.RECEIVE_CLOSE_TOLERANCE,M.RECEIVE_CLOSE_TOLERANCE),
373 NVL(I.AUTO_LOT_ALPHA_PREFIX,M.AUTO_LOT_ALPHA_PREFIX),
374 NVL(I.START_AUTO_LOT_NUMBER,M.START_AUTO_LOT_NUMBER),
375 NVL(I.LOT_CONTROL_CODE,M.LOT_CONTROL_CODE),
376 NVL(I.SHELF_LIFE_CODE,M.SHELF_LIFE_CODE),
377 NVL(I.SHELF_LIFE_DAYS,M.SHELF_LIFE_DAYS),
378 NVL(I.SERIAL_NUMBER_CONTROL_CODE,M.SERIAL_NUMBER_CONTROL_CODE),
379 NVL(I.START_AUTO_SERIAL_NUMBER,M.START_AUTO_SERIAL_NUMBER),
380 NVL(I.AUTO_SERIAL_ALPHA_PREFIX,M.AUTO_SERIAL_ALPHA_PREFIX),
381 NVL(I.SOURCE_TYPE,M.SOURCE_TYPE),
382 NVL(I.SOURCE_ORGANIZATION_ID,M.SOURCE_ORGANIZATION_ID),
383 NVL(I.SOURCE_SUBINVENTORY,M.SOURCE_SUBINVENTORY),
384 NVL(I.EXPENSE_ACCOUNT,M.EXPENSE_ACCOUNT),
385 NVL(I.ENCUMBRANCE_ACCOUNT,M.ENCUMBRANCE_ACCOUNT),
386 NVL(I.RESTRICT_SUBINVENTORIES_CODE,M.RESTRICT_SUBINVENTORIES_CODE),
387 NVL(I.UNIT_WEIGHT,M.UNIT_WEIGHT),
388 NVL(I.WEIGHT_UOM_CODE,M.WEIGHT_UOM_CODE),
389 NVL(I.VOLUME_UOM_CODE,M.VOLUME_UOM_CODE),
390 NVL(I.UNIT_VOLUME,M.UNIT_VOLUME),
391 NVL(I.RESTRICT_LOCATORS_CODE,M.RESTRICT_LOCATORS_CODE),
392 NVL(I.LOCATION_CONTROL_CODE,M.LOCATION_CONTROL_CODE),
393 NVL(I.SHRINKAGE_RATE,M.SHRINKAGE_RATE),
394 NVL(I.ACCEPTABLE_EARLY_DAYS,M.ACCEPTABLE_EARLY_DAYS),
398 NVL(I.STD_LOT_SIZE,M.STD_LOT_SIZE),
395 NVL(I.PLANNING_TIME_FENCE_CODE,M.PLANNING_TIME_FENCE_CODE),
396 NVL(I.DEMAND_TIME_FENCE_CODE,M.DEMAND_TIME_FENCE_CODE),
397 NVL(I.LEAD_TIME_LOT_SIZE,M.LEAD_TIME_LOT_SIZE),
399 NVL(I.CUM_MANUFACTURING_LEAD_TIME,M.CUM_MANUFACTURING_LEAD_TIME),
400 NVL(I.OVERRUN_PERCENTAGE,M.OVERRUN_PERCENTAGE),
401 NVL(I.ACCEPTABLE_RATE_INCREASE,M.ACCEPTABLE_RATE_INCREASE),
402 NVL(I.ACCEPTABLE_RATE_DECREASE,M.ACCEPTABLE_RATE_DECREASE),
403 NVL(I.CUMULATIVE_TOTAL_LEAD_TIME,M.CUMULATIVE_TOTAL_LEAD_TIME),
404 NVL(I.PLANNING_TIME_FENCE_DAYS,M.PLANNING_TIME_FENCE_DAYS),
405 NVL(I.DEMAND_TIME_FENCE_DAYS,M.DEMAND_TIME_FENCE_DAYS),
406 NVL(I.END_ASSEMBLY_PEGGING_FLAG,M.END_ASSEMBLY_PEGGING_FLAG),
407 NVL(I.PLANNING_EXCEPTION_SET,M.PLANNING_EXCEPTION_SET),
408 NVL(I.BOM_ITEM_TYPE,M.BOM_ITEM_TYPE),
409 NVL(I.PICK_COMPONENTS_FLAG,M.PICK_COMPONENTS_FLAG),
410 NVL(I.REPLENISH_TO_ORDER_FLAG,M.REPLENISH_TO_ORDER_FLAG),
411 NVL(I.BASE_ITEM_ID,M.BASE_ITEM_ID),
412 NVL(I.ATP_COMPONENTS_FLAG,M.ATP_COMPONENTS_FLAG),
413 NVL(I.ATP_FLAG,M.ATP_FLAG),
414 NVL(I.FIXED_LEAD_TIME,M.FIXED_LEAD_TIME),
415 NVL(I.VARIABLE_LEAD_TIME,M.VARIABLE_LEAD_TIME),
416 NVL(I.WIP_SUPPLY_LOCATOR_ID,M.WIP_SUPPLY_LOCATOR_ID),
417 NVL(I.WIP_SUPPLY_TYPE,M.WIP_SUPPLY_TYPE),
418 NVL(I.WIP_SUPPLY_SUBINVENTORY,M.WIP_SUPPLY_SUBINVENTORY),
419 NVL(I.PRIMARY_UOM_CODE,M.PRIMARY_UOM_CODE),
420 NVL(I.PRIMARY_UNIT_OF_MEASURE,M.PRIMARY_UNIT_OF_MEASURE),
421 NVL(I.ALLOWED_UNITS_LOOKUP_CODE,M.ALLOWED_UNITS_LOOKUP_CODE),
422 NVL(I.COST_OF_SALES_ACCOUNT,M.COST_OF_SALES_ACCOUNT),
423 NVL(I.SALES_ACCOUNT,M.SALES_ACCOUNT),
424 NVL(I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,M.DEFAULT_INCLUDE_IN_ROLLUP_FLAG),
425 NVL(I.INVENTORY_ITEM_STATUS_CODE,M.INVENTORY_ITEM_STATUS_CODE),
426 NVL(I.INVENTORY_PLANNING_CODE,M.INVENTORY_PLANNING_CODE),
427 NVL(I.PLANNER_CODE,M.PLANNER_CODE),
428 NVL(I.PLANNING_MAKE_BUY_CODE,M.PLANNING_MAKE_BUY_CODE),
429 NVL(I.FIXED_LOT_MULTIPLIER,M.FIXED_LOT_MULTIPLIER),
430 NVL(I.ROUNDING_CONTROL_TYPE,M.ROUNDING_CONTROL_TYPE),
431 NVL(I.CARRYING_COST,M.CARRYING_COST),
432 NVL(I.POSTPROCESSING_LEAD_TIME,M.POSTPROCESSING_LEAD_TIME),
433 NVL(I.PREPROCESSING_LEAD_TIME,M.PREPROCESSING_LEAD_TIME),
434 NVL(I.FULL_LEAD_TIME,M.FULL_LEAD_TIME),
435 NVL(I.ORDER_COST,M.ORDER_COST),
436 NVL(I.MRP_SAFETY_STOCK_PERCENT,M.MRP_SAFETY_STOCK_PERCENT),
437 NVL(I.MRP_SAFETY_STOCK_CODE,M.MRP_SAFETY_STOCK_CODE),
438 NVL(I.MIN_MINMAX_QUANTITY,M.MIN_MINMAX_QUANTITY),
439 NVL(I.MAX_MINMAX_QUANTITY,M.MAX_MINMAX_QUANTITY),
440 NVL(I.MINIMUM_ORDER_QUANTITY,M.MINIMUM_ORDER_QUANTITY),
441 NVL(I.FIXED_ORDER_QUANTITY,M.FIXED_ORDER_QUANTITY),
442 NVL(I.FIXED_DAYS_SUPPLY,M.FIXED_DAYS_SUPPLY),
443 NVL(I.MAXIMUM_ORDER_QUANTITY,M.MAXIMUM_ORDER_QUANTITY),
444 NVL(I.ATP_RULE_ID,M.ATP_RULE_ID),
445 NVL(I.PICKING_RULE_ID,M.PICKING_RULE_ID),
446 NVL(I.RESERVABLE_TYPE,M.RESERVABLE_TYPE),
447 NVL(I.POSITIVE_MEASUREMENT_ERROR,M.POSITIVE_MEASUREMENT_ERROR),
448 NVL(I.NEGATIVE_MEASUREMENT_ERROR,M.NEGATIVE_MEASUREMENT_ERROR),
449 NVL(I.ENGINEERING_ECN_CODE,M.ENGINEERING_ECN_CODE),
450 NVL(I.ENGINEERING_ITEM_ID,M.ENGINEERING_ITEM_ID),
451 NVL(I.ENGINEERING_DATE,M.ENGINEERING_DATE),
452 NVL(I.SERVICE_STARTING_DELAY,M.SERVICE_STARTING_DELAY),
453 NVL(I.VENDOR_WARRANTY_FLAG,M.VENDOR_WARRANTY_FLAG),
454 NVL(I.SERVICEABLE_COMPONENT_FLAG,M.SERVICEABLE_COMPONENT_FLAG),
455 NVL(I.SERVICEABLE_PRODUCT_FLAG,M.SERVICEABLE_PRODUCT_FLAG),
456 NVL(I.BASE_WARRANTY_SERVICE_ID,M.BASE_WARRANTY_SERVICE_ID),
457 NVL(I.PAYMENT_TERMS_ID,M.PAYMENT_TERMS_ID),
458 NVL(I.PREVENTIVE_MAINTENANCE_FLAG,M.PREVENTIVE_MAINTENANCE_FLAG),
459 NVL(I.PRIMARY_SPECIALIST_ID,M.PRIMARY_SPECIALIST_ID),
460 NVL(I.SECONDARY_SPECIALIST_ID,M.SECONDARY_SPECIALIST_ID),
461 NVL(I.SERVICEABLE_ITEM_CLASS_ID,M.SERVICEABLE_ITEM_CLASS_ID),
462 NVL(I.TIME_BILLABLE_FLAG,M.TIME_BILLABLE_FLAG),
463 NVL(I.MATERIAL_BILLABLE_FLAG,M.MATERIAL_BILLABLE_FLAG),
464 NVL(I.EXPENSE_BILLABLE_FLAG,M.EXPENSE_BILLABLE_FLAG),
465 NVL(I.PRORATE_SERVICE_FLAG,M.PRORATE_SERVICE_FLAG),
466 NVL(I.COVERAGE_SCHEDULE_ID,M.COVERAGE_SCHEDULE_ID),
467 NVL(I.SERVICE_DURATION_PERIOD_CODE,M.SERVICE_DURATION_PERIOD_CODE),
468 NVL(I.SERVICE_DURATION,M.SERVICE_DURATION),
469 NVL(I.MAX_WARRANTY_AMOUNT,M.MAX_WARRANTY_AMOUNT),
470 NVL(I.RESPONSE_TIME_PERIOD_CODE,M.RESPONSE_TIME_PERIOD_CODE),
471 NVL(I.RESPONSE_TIME_VALUE,M.RESPONSE_TIME_VALUE),
472 NVL(I.NEW_REVISION_CODE,M.NEW_REVISION_CODE),
473 NVL(I.TAX_CODE,M.TAX_CODE),
474 NVL(I.MUST_USE_APPROVED_VENDOR_FLAG,M.MUST_USE_APPROVED_VENDOR_FLAG),
475 NVL(I.SAFETY_STOCK_BUCKET_DAYS,M.SAFETY_STOCK_BUCKET_DAYS),
476 NVL(I.AUTO_REDUCE_MPS,M.AUTO_REDUCE_MPS),
477 NVL(I.COSTING_ENABLED_FLAG,M.COSTING_ENABLED_FLAG),
478 NVL(I.INVOICEABLE_ITEM_FLAG,M.INVOICEABLE_ITEM_FLAG),
479 NVL(I.INVOICE_ENABLED_FLAG, M.INVOICE_ENABLED_FLAG),
480 NVL(I.OUTSIDE_OPERATION_FLAG,M.OUTSIDE_OPERATION_FLAG),
481 NVL(I.OUTSIDE_OPERATION_UOM_TYPE,M.OUTSIDE_OPERATION_UOM_TYPE),
482 NVL(I.AUTO_CREATED_CONFIG_FLAG, 'Y'),
483 NVL(I.CYCLE_COUNT_ENABLED_FLAG,M.CYCLE_COUNT_ENABLED_FLAG),
484 I.ITEM_TYPE,
485 NVL(I.MODEL_CONFIG_CLAUSE_NAME,M.MODEL_CONFIG_CLAUSE_NAME),
486 NVL(I.SHIP_MODEL_COMPLETE_FLAG,M.SHIP_MODEL_COMPLETE_FLAG),
487 NVL(I.MRP_PLANNING_CODE,M.MRP_PLANNING_CODE),
488 I.REPETITIVE_PLANNING_FLAG,
492 prg_appid,
489 NVL(I.RETURN_INSPECTION_REQUIREMENT,M.RETURN_INSPECTION_REQUIREMENT),
490 nvl( NVL(I.EFFECTIVITY_CONTROL,M.EFFECTIVITY_CONTROL), 1),
491 req_id,
493 prg_id,
494 SYSDATE
495 from
496 MTL_PARAMETERS MP1,
497 MTL_PARAMETERS MP2,
498 MTL_SYSTEM_ITEMS_B M, /* Model */
499 MTL_SYSTEM_ITEMS_INTERFACE I
500 where M.ORGANIZATION_ID = MP1.ORGANIZATION_ID
501 and I.COPY_ITEM_ID = M.INVENTORY_ITEM_ID
502 and I.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
503 and MP2.organization_id = I.organization_id
504 and ((I.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
505 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
506 (MP1.ORGANIZATION_ID = validation_org ));
507
508 --
509 -- R11.5 MLS
510 --
511 insert into MTL_SYSTEM_ITEMS_TL (
512 INVENTORY_ITEM_ID,
513 ORGANIZATION_ID,
514 LANGUAGE,
515 SOURCE_LANG,
516 DESCRIPTION,
517 LAST_UPDATE_DATE,
518 LAST_UPDATED_BY,
519 CREATION_DATE,
520 CREATED_BY,
521 LAST_UPDATE_LOGIN
522 ) select
523 I.INVENTORY_ITEM_ID,
524 MP1.ORGANIZATION_ID,
525 L.LANGUAGE_CODE,
526 userenv('LANG'),
527 NVL(I.DESCRIPTION,M.DESCRIPTION),
528 NVL(I.LAST_UPDATE_DATE,SYSDATE),
529 user_id, /* last_updated_by */
530 NVL(I.CREATION_DATE,SYSDATE),
531 user_id, /* created_by */
532 login_id /* last_update_login */
533 from
534 MTL_PARAMETERS MP1,
535 MTL_PARAMETERS MP2,
536 MTL_SYSTEM_ITEMS_B M, /* Model */
537 MTL_SYSTEM_ITEMS_INTERFACE I
538 , FND_LANGUAGES L
539 where M.ORGANIZATION_ID = MP1.ORGANIZATION_ID
540 and I.COPY_ITEM_ID = M.INVENTORY_ITEM_ID
541 and I.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
542 and MP2.organization_id = I.organization_id
543 and ((I.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
544 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
545 (MP1.ORGANIZATION_ID = validation_org ))
546 and L.INSTALLED_FLAG in ('I', 'B')
547 and not exists
548 ( select NULL
549 from MTL_SYSTEM_ITEMS_TL T
550 where T.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
551 and T.ORGANIZATION_ID = MP1.ORGANIZATION_ID
552 and T.LANGUAGE = L.LANGUAGE_CODE );
553
554 /* Copy the item revisions into the item revisions table */
555
556 table_name := 'MTL_ITEM_REVISIONS';
557 insert into MTL_ITEM_REVISIONS
558 (INVENTORY_ITEM_ID,
559 ORGANIZATION_ID,
560 REVISION,
561 LAST_UPDATE_DATE,
562 LAST_UPDATED_BY,
563 CREATION_DATE,
564 CREATED_BY,
565 LAST_UPDATE_LOGIN,
566 IMPLEMENTATION_DATE,
567 EFFECTIVITY_DATE
568 )
569 select
570 R.INVENTORY_ITEM_ID,
571 MP1.ORGANIZATION_ID,
572 R.REVISION,
573 NVL(R.LAST_UPDATE_DATE,SYSDATE),
574 user_id, /* LAST_UPDATED_BY */
575 NVL(R.CREATION_DATE,SYSDATE),
576 user_id, /* created_by */
577 login_id, /* last_update_login */
578 SYSDATE,
579 SYSDATE
580 from
581 MTL_PARAMETERS MP1,
582 MTL_PARAMETERS MP2,
583 MTL_SYSTEM_ITEMS_INTERFACE R
584 where R.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
585 and MP2.organization_id = R.organization_id
586 and ((R.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
587 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
588 (MP1.ORGANIZATION_ID = validation_org ));
589
590 /* Create rows for config items in the MTL_PENDING_ITEM_STATUS */
591
592 table_name := 'MTL_PENDING_ITEM_STATUS';
593 insert into MTL_PENDING_ITEM_STATUS (
594 INVENTORY_ITEM_ID,
595 ORGANIZATION_ID,
596 STATUS_CODE,
597 EFFECTIVE_DATE,
598 PENDING_FLAG,
599 LAST_UPDATE_DATE,
600 LAST_UPDATED_BY,
601 CREATION_DATE,
602 CREATED_BY,
603 LAST_UPDATE_LOGIN,
604 PROGRAM_APPLICATION_ID,
605 PROGRAM_ID,
606 PROGRAM_UPDATE_DATE,
607 REQUEST_ID)
608 select
609 R.INVENTORY_ITEM_ID,
610 MP1.ORGANIZATION_ID,
611 SI.INVENTORY_ITEM_STATUS_CODE,
612 SYSDATE,
613 'N',
614 NVL(R.LAST_UPDATE_DATE,SYSDATE),
615 user_id,
616 NVL(R.CREATION_DATE,SYSDATE),
617 user_id,
618 login_id,
619 prg_appid,
620 prg_id,
621 SYSDATE,
622 req_id
623 from MTL_SYSTEM_ITEMS_B SI,
624 MTL_PARAMETERS MP1,
625 MTL_PARAMETERS MP2,
626 MTL_SYSTEM_ITEMS_INTERFACE R
627 where R.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
628 and R.inventory_item_id = SI.inventory_item_id
629 and R.organization_id = SI.organization_id
630 and MP2.organization_id = R.organization_id
631 and ((R.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
632 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
633 (MP1.ORGANIZATION_ID = validation_org ));
634
635
639 /* is different from the other table loads. These tables will load */
636 /* **************************************************************** */
637 /* */
638 /* The logic for the cst_item_costs and cst_item_costs_detail */
640 /* whatever data is found in their interface tables exactly as they */
641 /* are. The logic is this way because there is no unique key on */
642 /* on the cst_item_costs_detail table for us to match to a model's */
643 /* row. */
644 /* Above logic has been changed to copy the data from the model */
645 /* in the respective org rather than keeping the same data in all */
646 /* three Orgs. It is important because these Orgs may have different*/
647 /* Primary costing methods since we support avg costing now. */
648 /* */
649 /* **************************************************************** */
650
651 /* Copy the item cost attributes into the regular product table */
652 table_name := 'CST_ITEM_COSTS';
653 insert into CST_ITEM_COSTS
654 (
655 INVENTORY_ITEM_ID,
656 ORGANIZATION_ID,
657 COST_TYPE_ID,
658 LAST_UPDATE_DATE,
659 LAST_UPDATED_BY,
660 CREATION_DATE,
661 CREATED_BY,
662 LAST_UPDATE_LOGIN,
663 INVENTORY_ASSET_FLAG,
664 LOT_SIZE,
665 BASED_ON_ROLLUP_FLAG,
666 SHRINKAGE_RATE,
667 DEFAULTED_FLAG,
668 COST_UPDATE_ID,
669 PL_MATERIAL,
670 PL_MATERIAL_OVERHEAD,
671 PL_RESOURCE,
672 PL_OUTSIDE_PROCESSING,
673 PL_OVERHEAD,
674 TL_MATERIAL,
675 TL_MATERIAL_OVERHEAD,
676 TL_RESOURCE,
677 TL_OUTSIDE_PROCESSING,
678 TL_OVERHEAD,
679 MATERIAL_COST,
680 MATERIAL_OVERHEAD_COST,
681 RESOURCE_COST,
682 OUTSIDE_PROCESSING_COST ,
683 OVERHEAD_COST,
684 PL_ITEM_COST,
685 TL_ITEM_COST,
686 ITEM_COST,
687 UNBURDENED_COST ,
688 BURDEN_COST,
689 ATTRIBUTE_CATEGORY,
690 ATTRIBUTE1,
691 ATTRIBUTE2,
692 ATTRIBUTE3,
693 ATTRIBUTE4,
694 ATTRIBUTE5,
695 ATTRIBUTE6,
696 ATTRIBUTE7,
697 ATTRIBUTE8,
698 ATTRIBUTE9,
699 ATTRIBUTE10,
700 ATTRIBUTE11,
701 ATTRIBUTE12,
702 ATTRIBUTE13,
703 ATTRIBUTE14,
704 ATTRIBUTE15,
705 REQUEST_ID,
706 PROGRAM_APPLICATION_ID,
707 PROGRAM_ID,
708 PROGRAM_UPDATE_DATE
709 )
710 select
711 SI.INVENTORY_ITEM_ID,
712 MP1.ORGANIZATION_ID,
713 C.COST_TYPE_ID,
714 NVL(C.LAST_UPDATE_DATE,SYSDATE),
715 user_id, /* LAST_UPDATED_BY */
716 NVL(C.CREATION_DATE,SYSDATE),
717 user_id, /* created_by */
718 login_id, /* last_update_login */
719 C.INVENTORY_ASSET_FLAG,
720 C.LOT_SIZE,
721 C.BASED_ON_ROLLUP_FLAG,
722 C.SHRINKAGE_RATE,
723 C.DEFAULTED_FLAG,
724 NVL(C.COST_UPDATE_ID,CST_LISTS_S.NEXTVAL),
725 C.PL_MATERIAL,
726 C.PL_MATERIAL_OVERHEAD,
727 C.PL_RESOURCE,
728 C.PL_OUTSIDE_PROCESSING,
729 C.PL_OVERHEAD,
730 C.TL_MATERIAL,
731 C.TL_MATERIAL_OVERHEAD,
732 C.TL_RESOURCE,
733 C.TL_OUTSIDE_PROCESSING,
734 C.TL_OVERHEAD,
735 C.MATERIAL_COST,
736 C.MATERIAL_OVERHEAD_COST,
737 C.RESOURCE_COST,
738 C.OUTSIDE_PROCESSING_COST ,
739 C.OVERHEAD_COST,
740 C.PL_ITEM_COST,
741 C.TL_ITEM_COST,
742 C.ITEM_COST,
743 C.UNBURDENED_COST ,
744 C.BURDEN_COST,
745 C.ATTRIBUTE_CATEGORY,
746 C.ATTRIBUTE1,
747 C.ATTRIBUTE2,
748 C.ATTRIBUTE3,
749 C.ATTRIBUTE4,
750 C.ATTRIBUTE5,
751 C.ATTRIBUTE6,
752 C.ATTRIBUTE7,
753 C.ATTRIBUTE8,
754 C.ATTRIBUTE9,
755 C.ATTRIBUTE10,
756 C.ATTRIBUTE11,
757 C.ATTRIBUTE12,
758 C.ATTRIBUTE13,
759 C.ATTRIBUTE14,
760 C.ATTRIBUTE15,
761 req_id, /* request_id */
762 prg_appid, /* program_application_id */
763 prg_id, /* program_id */
764 SYSDATE
765 from
766 CST_ITEM_COSTS C,
767 MTL_PARAMETERS MP1,
768 MTL_PARAMETERS MP2,
769 MTL_SYSTEM_ITEMS_INTERFACE SI
770 where C.ORGANIZATION_ID = MP1.ORGANIZATION_ID
771 and C.INVENTORY_ITEM_ID = SI.Copy_item_id
772 and C.COST_TYPE_ID IN ( MP1.PRIMARY_COST_METHOD, MP1.AVG_RATES_COST_TYPE_ID)
773 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
774 and MP2.organization_id = SI.organization_id
778
775 and ((SI.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
776 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
777 (MP1.ORGANIZATION_ID = validation_org ));
779
780 /* Copy the item cost details into the regular product table */
781
782 table_name := 'CST_ITEM_COST_DETAILS';
783 insert into CST_ITEM_COST_DETAILS
784 (INVENTORY_ITEM_ID,
785 ORGANIZATION_ID,
786 COST_TYPE_ID,
787 LAST_UPDATE_DATE,
788 LAST_UPDATED_BY,
789 CREATION_DATE,
790 CREATED_BY,
791 LAST_UPDATE_LOGIN,
792 OPERATION_SEQUENCE_ID,
793 OPERATION_SEQ_NUM,
794 DEPARTMENT_ID,
795 LEVEL_TYPE,
796 ACTIVITY_ID,
797 RESOURCE_SEQ_NUM,
798 RESOURCE_ID,
799 RESOURCE_RATE,
800 ITEM_UNITS,
801 ACTIVITY_UNITS,
802 USAGE_RATE_OR_AMOUNT,
803 BASIS_TYPE,
804 BASIS_RESOURCE_ID,
805 BASIS_FACTOR,
806 NET_YIELD_OR_SHRINKAGE_FACTOR,
807 ITEM_COST,
808 COST_ELEMENT_ID,
809 ROLLUP_SOURCE_TYPE,
810 ACTIVITY_CONTEXT,
811 REQUEST_ID,
812 PROGRAM_APPLICATION_ID,
813 PROGRAM_ID,
814 PROGRAM_UPDATE_DATE,
815 ATTRIBUTE_CATEGORY,
816 ATTRIBUTE1,
817 ATTRIBUTE2,
818 ATTRIBUTE3,
819 ATTRIBUTE4,
820 ATTRIBUTE5,
821 ATTRIBUTE6,
822 ATTRIBUTE7,
823 ATTRIBUTE8,
824 ATTRIBUTE9,
825 ATTRIBUTE10,
826 ATTRIBUTE11,
827 ATTRIBUTE12,
828 ATTRIBUTE13,
829 ATTRIBUTE14,
830 ATTRIBUTE15
831 )
832 select SI.INVENTORY_ITEM_ID,
833 MP1.ORGANIZATION_ID,
834 C.COST_TYPE_ID,
835 NVL(C.LAST_UPDATE_DATE,SYSDATE),
836 user_id, /* LAST_UPDATED_BY */
837 NVL(C.CREATION_DATE,SYSDATE),
838 user_id, /* created_by */
839 login_id, /* last_update_login */
840 C.OPERATION_SEQUENCE_ID,
841 C.OPERATION_SEQ_NUM,
842 C.DEPARTMENT_ID,
843 C.LEVEL_TYPE,
844 C.ACTIVITY_ID,
845 C.RESOURCE_SEQ_NUM,
846 C.RESOURCE_ID,
847 C.RESOURCE_RATE,
848 C.ITEM_UNITS,
849 C.ACTIVITY_UNITS,
850 C.USAGE_RATE_OR_AMOUNT,
851 C.BASIS_TYPE,
852 C.BASIS_RESOURCE_ID,
853 C.BASIS_FACTOR,
854 C.NET_YIELD_OR_SHRINKAGE_FACTOR,
855 C.ITEM_COST,
856 C.COST_ELEMENT_ID,
857 C.ROLLUP_SOURCE_TYPE,
858 C.ACTIVITY_CONTEXT,
859 req_id, /* request_id */
860 prg_appid, /* program_application_id */
861 prg_id, /* program_id */
862 SYSDATE, /* program_update_date */
863 C.ATTRIBUTE_CATEGORY,
864 C.ATTRIBUTE1,
865 C.ATTRIBUTE2,
866 C.ATTRIBUTE3,
867 C.ATTRIBUTE4,
868 C.ATTRIBUTE5,
869 C.ATTRIBUTE6,
870 C.ATTRIBUTE7,
871 C.ATTRIBUTE8,
872 C.ATTRIBUTE9,
873 C.ATTRIBUTE10,
874 C.ATTRIBUTE11,
875 C.ATTRIBUTE12,
876 C.ATTRIBUTE13,
877 C.ATTRIBUTE14,
878 C.ATTRIBUTE15
879 from
880 CST_ITEM_COST_DETAILS C,
881 MTL_PARAMETERS MP1,
882 MTL_PARAMETERS MP2,
883 MTL_SYSTEM_ITEMS_INTERFACE SI
884 where C.ORGANIZATION_ID = MP1.ORGANIZATION_ID
885 and C.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
886 and C.COST_TYPE_ID IN ( MP1.PRIMARY_COST_METHOD, MP1.AVG_RATES_COST_TYPE_ID)
887 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
888 and MP2.organization_id = SI.organization_id
889 and ((SI.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
890 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
891 (MP1.ORGANIZATION_ID = validation_org ));
892
893
894 /* If the config item is being created in an average costing organisation
895 insert a blank row in cst_quantity_layers to earn MOH when the
896 item is transacted. If the item is being created in the shipping org,
897 and the corrosponding Sales order has project refrence, use projects
898 cost_group_id.
899 */
900
901 l_cost_group_id := 1;
902
903 for nxtconfig in allconfig
904 loop
905 if nxtconfig.project_id is NULL then
906 l_cost_group_id := 1;
907 else
908 select nvl(costing_group_id,1)
909 into l_cost_group_id
910 from pjm_project_parameters ppp
911 where ppp.project_id = nxtconfig.project_id
912 and ppp.organization_id = nxtconfig.organization_id;
913 end if;
914
915 insert into cst_quantity_layers (
916 layer_id,
917 organization_id,
918 inventory_item_id,
919 cost_group_id,
920 layer_quantity,
921 last_update_date,
922 last_updated_by,
923 creation_date,
924 created_by,
925 request_id,
926 program_id,
927 program_application_id,
928 PL_MATERIAL,
929 PL_MATERIAL_OVERHEAD,
930 PL_RESOURCE,
931 PL_OUTSIDE_PROCESSING,
932 PL_OVERHEAD,
933 TL_MATERIAL,
937 TL_OVERHEAD,
934 TL_MATERIAL_OVERHEAD,
935 TL_RESOURCE,
936 TL_OUTSIDE_PROCESSING,
938 MATERIAL_COST,
939 MATERIAL_OVERHEAD_COST ,
940 RESOURCE_COST,
941 OUTSIDE_PROCESSING_COST,
942 OVERHEAD_COST,
943 PL_ITEM_COST,
944 TL_ITEM_COST,
945 ITEM_COST,
946 UNBURDENED_COST,
947 BURDEN_COST,
948 CREATE_TRANSACTION_ID
949 )
950 Select
951 cst_quantity_layers_s.nextval,
952 MP1.organization_id,
953 SI.inventory_item_id,
954 DECODE(MP1.ORGANIZATION_ID, SI.ORGANIZATION_ID,l_cost_group_id,1), /* cost_group_id */
955 0,
956 SYSDATE,
957 user_id,
958 SYSDATE,
959 user_id,
960 req_id,
961 prg_id,
962 prg_appid,
963 0,
964 0,
965 0,
966 0,
967 0,
968 0,
969 0,
970 0,
971 0,
972 0,
973 0,
974 0,
975 0,
976 0,
977 0,
978 0,
979 0,
980 0,
981 0,
982 0,
983 -1 /* txn_id */
984 from
985 MTL_SYSTEM_ITEMS_INTERFACE SI,
986 MTL_PARAMETERS MP1,
987 MTL_PARAMETERS MP2,
988 CST_ITEM_COSTS C
989 where
990 C.ORGANIZATION_ID = MP1.ORGANIZATION_ID
991 and C.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
992 and C.COST_TYPE_ID = MP1.primary_cost_method /*Average FIFO/LIFO Costing */
993 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
994 and MP2.organization_id = SI.organization_id
995 and ((SI.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
996 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
997 (MP1.ORGANIZATION_ID = validation_org ))
998 and MP1.Primary_cost_method IN (2,5,6); /* Create only in Average costing organization*/
999 end loop;
1000
1001 /* Copy the item descriptive element values into the regular
1002 product table. */
1003
1004 table_name := 'MTL_DESCR_ELEMENT_VALUES';
1005 insert into MTL_DESCR_ELEMENT_VALUES
1006 (INVENTORY_ITEM_ID,
1007 ELEMENT_NAME,
1008 LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY,
1010 LAST_UPDATE_LOGIN,
1011 CREATION_DATE,
1012 CREATED_BY,
1013 ELEMENT_VALUE,
1014 DEFAULT_ELEMENT_FLAG,
1015 PROGRAM_APPLICATION_ID,
1016 PROGRAM_ID,
1017 PROGRAM_UPDATE_DATE,
1018 REQUEST_ID,
1019 ELEMENT_SEQUENCE
1020 )
1021 select V.INVENTORY_ITEM_ID,
1022 NVL(V.ELEMENT_NAME,M.ELEMENT_NAME),
1023 NVL(V.LAST_UPDATE_DATE,SYSDATE),
1024 user_id, /* last_updated_by */
1025 login_id, /* last_update_login */
1026 NVL(V.CREATION_DATE,SYSDATE),
1027 user_id, /* created_by */
1028 NVL(V.ELEMENT_VALUE,M.ELEMENT_VALUE),
1029 NVL(V.DEFAULT_ELEMENT_FLAG,M.DEFAULT_ELEMENT_FLAG),
1030 prg_appid, /* PROGRAM_APPLICATION_ID */
1031 prg_id, /* PROGRAM_ID */
1032 SYSDATE, /* PROGRAM_UPDATE_DATE */
1033 req_id, /* REQUEST_ID */
1034 NVL(V.ELEMENT_SEQUENCE,M.ELEMENT_SEQUENCE)
1035 from MTL_DESCR_ELEMENT_VALUES M, /* Model's desc elem values */
1036 MTL_DESC_ELEM_VAL_INTERFACE V,
1037 MTL_SYSTEM_ITEMS_INTERFACE SI
1038 where V.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
1039 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
1040 and M.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
1041 and M.ELEMENT_NAME = V.ELEMENT_NAME; /* This where clause was */
1042 /* originally an outer join */
1043
1044
1045 /* Copy item categories into MTL_ITEM_CATEGORIES table */
1046 table_name := 'MTL_ITEM_CATEGORIES';
1047 insert into MTL_ITEM_CATEGORIES
1048 ( inventory_item_id,
1049 category_set_id,
1050 category_id,
1051 last_update_date,
1052 last_updated_by,
1053 creation_date,
1054 created_by,
1055 last_update_login,
1056 request_id,
1057 program_application_id,
1058 program_id,
1059 program_update_date,
1060 organization_id
1061 )
1062 select
1063 ici.inventory_item_id,
1064 ici.category_set_id,
1065 ici.category_id,
1066 NVL(ici.last_update_date,sysdate),
1067 user_id, /* last_updated_by */
1068 NVL(ici.creation_date, sysdate),
1069 user_id, /* created_by */
1070 login_id, /* last_update_login */
1071 req_id, /* request_id */
1072 prg_appid, /* program_application_id */
1073 prg_id, /* program_id */
1074 SYSDATE, /* program_update_date */
1075 MP1.organization_id
1076 from MTL_PARAMETERS MP1,
1077 MTL_PARAMETERS MP2,
1078 mtl_item_categories_interface ici,
1082 and ici.category_set_id is not NULL
1079 mtl_system_items_interface si
1080 where si.inventory_item_id = ici.inventory_item_id
1081 and si.organization_id = ici.organization_id
1083 and ici.category_id is not NULL
1084 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1085 and MP2.organization_id = si.organization_id
1086 and ((si.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
1087 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
1088 (MP1.ORGANIZATION_ID = validation_org ));
1089
1090 insert into MTL_ITEM_CATEGORIES
1091 ( inventory_item_id,
1092 category_set_id,
1093 category_id,
1094 last_update_date,
1095 last_updated_by,
1096 creation_date,
1097 created_by,
1098 last_update_login,
1099 request_id,
1100 program_application_id,
1101 program_id,
1102 program_update_date,
1103 organization_id
1104 )
1105 select
1106 si.inventory_item_id,
1107 ic.category_set_id,
1108 ic.category_id,
1109 sysdate, /* last_update_date */
1110 user_id, /* last_updated_by */
1111 sysdate, /*creation_date */
1112 user_id, /* created_by */
1113 login_id, /* last_update_login */
1114 req_id, /* request_id */
1115 prg_appid, /* program_application_id */
1116 prg_id, /* program_id */
1117 SYSDATE, /* program_update_date */
1118 MP1.organization_id
1119 from
1120 mtl_item_categories ic,
1121 MTL_PARAMETERS MP1,
1122 MTL_PARAMETERS MP2,
1123 mtl_system_items_interface si
1124 where si.copy_item_id = ic.inventory_item_id
1125 and si.organization_id = ic.organization_id
1126 and MP2.organization_id = si.organization_id
1127 and not exists ( select NULL
1128 from mtl_item_categories_interface ici
1129 where ici.inventory_item_id = si.inventory_item_id
1130 and ici.organization_id = si.organization_id
1131 and ici.category_set_id is not NULL
1132 and ici.category_id is not NULL)
1133 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1134 and ((si.ORGANIZATION_ID = MP1.ORGANIZATION_ID) OR
1135 (MP1.ORGANIZATION_ID = MP2.master_organization_id) OR
1136 (MP1.ORGANIZATION_ID = validation_org ));
1137
1138
1139
1140 /* Copy related items into MTL_RELATED_ITEMS table */
1141 table_name := 'MTL_RELATED_ITEMS';
1142 insert into MTL_RELATED_ITEMS
1143 (
1144 inventory_item_id,
1145 related_item_id,
1146 relationship_type_id,
1147 reciprocal_flag,
1148 last_update_date,
1149 last_updated_by,
1150 creation_date, created_by,
1151 last_update_login,
1152 request_id,
1153 program_application_id,
1154 program_id,
1155 program_update_date,
1156 organization_id
1157 )
1158 select
1159 rii.inventory_item_id,
1160 rii.related_item_id,
1161 rii.relationship_type_id,
1162 rii.reciprocal_flag,
1163 NVL(rii.last_update_date,sysdate),
1164 user_id, /* last_updated_by */
1165 NVL(rii.creation_date, sysdate),
1166 user_id, /* created_by */
1167 login_id, /* last_update_login */
1168 req_id, /* request_id */
1169 prg_appid, /* program_application_id */
1170 prg_id, /* program_id */
1171 SYSDATE, /* program_update_date */
1172 rii.organization_id
1173 from mtl_related_items_interface rii,
1174 mtl_system_items_interface si
1175 where rii.inventory_item_id = si.inventory_item_id
1176 and rii.organization_id = si.organization_id
1177 and rii.related_item_id is not NULL
1178 and rii.relationship_type_id is not NULL
1179 and rii.reciprocal_flag is not NULL
1180 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1181
1182
1183 insert into MTL_RELATED_ITEMS
1184 (
1185 inventory_item_id,
1186 related_item_id,
1187 relationship_type_id,
1188 reciprocal_flag,
1189 last_update_date,
1190 last_updated_by,
1191 creation_date,
1192 created_by,
1193 last_update_login,
1194 request_id,
1195 program_application_id,
1196 program_id,
1197 program_update_date,
1198 organization_id
1199 )
1200 select
1201 si.inventory_item_id,
1202 ri.related_item_id,
1203 ri.relationship_type_id,
1207 sysdate, /*creation_date */
1204 ri.reciprocal_flag,
1205 sysdate, /* last_update_date */
1206 user_id, /* last_updated_by */
1208 user_id, /* created_by */
1209 login_id, /* last_update_login */
1210 req_id, /* request_id */
1211 prg_appid, /* program_application_id */
1212 prg_id, /* program_id */
1213 SYSDATE, /* program_update_date */
1214 ri.organization_id
1215 from mtl_related_items ri,
1216 mtl_system_items_interface si
1217 where ri.inventory_item_id = si.copy_item_id
1218 and ri.organization_id = si.organization_id
1219 and not exists ( select NULL
1220 from mtl_related_items_interface rii
1221 where rii.inventory_item_id = si.inventory_item_id
1222 and rii.organization_id = si.organization_id
1223 and rii.related_item_id is not NULL
1224 and rii.relationship_type_id is not NULL
1225 and rii.reciprocal_flag is not NULL)
1226 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1227
1228 /* Copy substitute inventories into MTL_ITEM_SUB_INVENTORIES */
1229 table_name := 'MTL_ITEM_SUB_INVENTORIES';
1230 insert into mtl_item_sub_inventories
1231 (
1232 INVENTORY_ITEM_ID,
1233 ORGANIZATION_ID,
1234 SECONDARY_INVENTORY,
1235 LAST_UPDATE_DATE,
1236 LAST_UPDATED_BY,
1237 CREATION_DATE,
1238 CREATED_BY,
1239 LAST_UPDATE_LOGIN,
1240 PRIMARY_SUBINVENTORY_FLAG ,
1241 PICKING_ORDER,
1242 MIN_MINMAX_QUANTITY,
1243 MAX_MINMAX_QUANTITY,
1244 INVENTORY_PLANNING_CODE,
1245 FIXED_LOT_MULTIPLE,
1246 MINIMUM_ORDER_QUANTITY,
1247 MAXIMUM_ORDER_QUANTITY,
1248 SOURCE_TYPE,
1249 SOURCE_ORGANIZATION_ID,
1250 SOURCE_SUBINVENTORY,
1251 ATTRIBUTE_CATEGORY,
1252 ATTRIBUTE1,
1253 ATTRIBUTE2,
1254 ATTRIBUTE3,
1255 ATTRIBUTE4,
1256 ATTRIBUTE5,
1257 ATTRIBUTE6,
1258 ATTRIBUTE7,
1259 ATTRIBUTE8,
1260 ATTRIBUTE9,
1261 ATTRIBUTE10,
1262 ATTRIBUTE11,
1263 ATTRIBUTE12,
1264 ATTRIBUTE13,
1265 ATTRIBUTE14,
1266 ATTRIBUTE15,
1267 REQUEST_ID,
1268 PROGRAM_APPLICATION_ID ,
1269 PROGRAM_ID,
1270 PROGRAM_UPDATE_DATE,
1271 ENCUMBRANCE_ACCOUNT
1272 )
1273 select
1274 isii.INVENTORY_ITEM_ID,
1275 isii.ORGANIZATION_ID,
1276 isii.SECONDARY_INVENTORY,
1277 NVL(isii.LAST_UPDATE_DATE, SYSDATE),
1278 user_id, /* LAST_UPDATED_BY */
1279 NVL(isii.CREATION_DATE, SYSDATE),
1280 user_id, /* CREATED_BY */
1281 login_id, /* LAST_UPDATE_LOGIN */
1282 isii.PRIMARY_SUBINVENTORY_FLAG,
1283 isii.PICKING_ORDER,
1284 isii.MIN_MINMAX_QUANTITY,
1285 isii.MAX_MINMAX_QUANTITY,
1286 isii.INVENTORY_PLANNING_CODE,
1287 isii.FIXED_LOT_MULTIPLE,
1288 isii.MINIMUM_ORDER_QUANTITY,
1289 isii.MAXIMUM_ORDER_QUANTITY,
1290 isii.SOURCE_TYPE,
1291 isii.SOURCE_ORGANIZATION_ID,
1292 isii.SOURCE_SUBINVENTORY,
1293 isii.ATTRIBUTE_CATEGORY,
1294 isii.ATTRIBUTE1,
1295 isii.ATTRIBUTE2,
1296 isii.ATTRIBUTE3,
1297 isii.ATTRIBUTE4,
1298 isii.ATTRIBUTE5,
1299 isii.ATTRIBUTE6,
1300 isii.ATTRIBUTE7,
1301 isii.ATTRIBUTE8,
1302 isii.ATTRIBUTE9,
1303 isii.ATTRIBUTE10,
1304 isii.ATTRIBUTE11,
1305 isii.ATTRIBUTE12,
1306 isii.ATTRIBUTE13,
1307 isii.ATTRIBUTE14,
1308 isii.ATTRIBUTE15,
1309 req_id, /* request_id */
1310 prg_appid, /* program_application_id */
1311 prg_id, /* program_id */
1312 SYSDATE, /* program_update_date */
1313 isii.ENCUMBRANCE_ACCOUNT
1314 from
1315 mtl_item_sub_invs_interface isii,
1316 mtl_system_items_interface si
1317 where si.organization_id = isii.organization_id
1318 and si.inventory_item_id = isii.inventory_item_id
1319 and isii.secondary_inventory is not NULL
1320 and isii.inventory_planning_code is not NULL
1321 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1322
1323 insert into mtl_item_sub_inventories
1327 SECONDARY_INVENTORY,
1324 (
1325 INVENTORY_ITEM_ID,
1326 ORGANIZATION_ID,
1328 LAST_UPDATE_DATE,
1329 LAST_UPDATED_BY,
1330 CREATION_DATE,
1331 CREATED_BY,
1332 LAST_UPDATE_LOGIN,
1333 PRIMARY_SUBINVENTORY_FLAG ,
1334 PICKING_ORDER,
1335 MIN_MINMAX_QUANTITY,
1336 MAX_MINMAX_QUANTITY,
1337 INVENTORY_PLANNING_CODE,
1338 FIXED_LOT_MULTIPLE,
1339 MINIMUM_ORDER_QUANTITY,
1340 MAXIMUM_ORDER_QUANTITY,
1341 SOURCE_TYPE,
1342 SOURCE_ORGANIZATION_ID,
1343 SOURCE_SUBINVENTORY,
1344 ATTRIBUTE_CATEGORY,
1345 ATTRIBUTE1,
1346 ATTRIBUTE2,
1347 ATTRIBUTE3,
1348 ATTRIBUTE4,
1349 ATTRIBUTE5,
1350 ATTRIBUTE6,
1351 ATTRIBUTE7,
1352 ATTRIBUTE8,
1353 ATTRIBUTE9,
1354 ATTRIBUTE10,
1355 ATTRIBUTE11,
1356 ATTRIBUTE12,
1357 ATTRIBUTE13,
1358 ATTRIBUTE14,
1359 ATTRIBUTE15,
1360 REQUEST_ID,
1361 PROGRAM_APPLICATION_ID ,
1362 PROGRAM_ID,
1363 PROGRAM_UPDATE_DATE,
1364 ENCUMBRANCE_ACCOUNT
1365 )
1366 select
1367 si.INVENTORY_ITEM_ID,
1368 isi.ORGANIZATION_ID,
1369 isi.SECONDARY_INVENTORY,
1370 sysdate, /* last_update_date */
1371 user_id, /* last_updated_by */
1372 sysdate, /*creation_date */
1373 user_id, /* created_by */
1374 login_id, /* last_update_login */
1375 isi.PRIMARY_SUBINVENTORY_FLAG ,
1376 isi.PICKING_ORDER,
1377 isi.MIN_MINMAX_QUANTITY,
1378 isi.MAX_MINMAX_QUANTITY,
1379 isi.INVENTORY_PLANNING_CODE,
1380 isi.FIXED_LOT_MULTIPLE,
1381 isi.MINIMUM_ORDER_QUANTITY,
1382 isi.MAXIMUM_ORDER_QUANTITY,
1383 isi.SOURCE_TYPE,
1384 isi.SOURCE_ORGANIZATION_ID,
1385 isi.SOURCE_SUBINVENTORY,
1386 isi.ATTRIBUTE_CATEGORY,
1387 isi.ATTRIBUTE1,
1388 isi.ATTRIBUTE2,
1389 isi.ATTRIBUTE3,
1390 isi.ATTRIBUTE4,
1391 isi.ATTRIBUTE5,
1392 isi.ATTRIBUTE6,
1393 isi.ATTRIBUTE7,
1394 isi.ATTRIBUTE8,
1395 isi.ATTRIBUTE9,
1396 isi.ATTRIBUTE10,
1397 isi.ATTRIBUTE11,
1398 isi.ATTRIBUTE12,
1399 isi.ATTRIBUTE13,
1400 isi.ATTRIBUTE14,
1401 isi.ATTRIBUTE15,
1402 req_id, /* request_id */
1403 prg_appid, /* program_application_id */
1404 prg_id, /* program_id */
1405 SYSDATE, /* program_update_date */
1406 isi.ENCUMBRANCE_ACCOUNT
1407 from
1408 mtl_item_sub_inventories isi,
1409 mtl_system_items_interface si
1410 where si.organization_id = isi.organization_id
1411 and si.copy_item_id = isi.inventory_item_id
1412 and not exists ( select NULL
1413 from mtl_item_sub_invs_interface isii
1414 where isii.inventory_item_id = si.inventory_item_id
1415 and isii.organization_id = si.organization_id
1416 and isii.secondary_inventory is not NULL
1417 and isii.inventory_planning_code is not NULL)
1418 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1419
1420 /* Copy secondary locators into MTL_SECONDARY_LOCATORS table */
1421 table_name := 'MTL_SECONDARY_LOCATORS';
1422 insert into mtl_secondary_locators
1423 (
1424 inventory_item_id,
1425 organization_id,
1426 secondary_locator,
1427 primary_locator_flag,
1428 picking_order,
1429 subinventory_code,
1430 last_update_date,
1431 last_updated_by,
1432 creation_date,
1433 created_by,
1434 last_update_login,
1435 request_id,
1436 program_application_id,
1437 program_id,
1438 program_update_date
1439 )
1440 select
1441 sli.inventory_item_id,
1442 sli.organization_id,
1443 sli.secondary_locator,
1444 sli.primary_locator_flag,
1445 sli.picking_order,
1446 sli.subinventory_code,
1447 NVL(sli.last_update_date,sysdate),
1448 user_id, /* last_updated_by */
1452 req_id, /* request_id */
1449 NVL(sli.creation_date,sysdate),
1450 user_id, /* created_by */
1451 login_id, /* last_update_login */
1453 prg_appid, /* program_application_id */
1454 prg_id, /* program_id */
1455 SYSDATE /* program_update_date */
1456 from
1457 mtl_secondary_locs_interface sli,
1458 mtl_system_items_interface si
1459 where si.organization_id = sli.organization_id
1460 and si.inventory_item_id = sli.inventory_item_id
1461 and sli.secondary_locator is not NULL
1462 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1463
1464
1465 insert into mtl_secondary_locators
1466 (
1467 inventory_item_id,
1468 organization_id,
1469 secondary_locator,
1470 primary_locator_flag,
1471 picking_order,
1472 subinventory_code,
1473 last_update_date,
1474 last_updated_by,
1475 creation_date,
1476 created_by,
1477 last_update_login,
1478 request_id,
1479 program_application_id,
1480 program_id,
1481 program_update_date
1482 )
1483 select
1484 si.inventory_item_id,
1485 sl.organization_id,
1486 sl.secondary_locator,
1487 sl.primary_locator_flag,
1488 sl.picking_order,
1489 sl.subinventory_code,
1490 sysdate, /* last_update_date */
1491 user_id, /* last_updated_by */
1492 sysdate, /*creation_date */
1493 user_id, /* created_by */
1494 login_id, /* last_update_login */
1495 req_id, /* request_id */
1496 prg_appid, /* program_application_id */
1497 prg_id, /* program_id */
1498 SYSDATE /* program_update_date */
1499 from
1500 mtl_secondary_locators sl,
1501 mtl_system_items_interface si
1502 where si.organization_id = sl.organization_id
1503 and si.copy_item_id = sl.inventory_item_id
1504 and not exists ( select NULL
1505 from mtl_secondary_locs_interface sli
1506 where sli.inventory_item_id = si.inventory_item_id
1507 and sli.organization_id = si.organization_id
1508 and sli.secondary_locator is not NULL)
1509 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1510
1511 /* Copy cross references into MTL_CROSS_REFERENCES table */
1512 table_name := 'MTL_CROSS_REFERENCES table';
1513 insert into mtl_cross_references
1514 (
1515 inventory_item_id,
1516 organization_id,
1517 cross_reference_type,
1518 cross_reference,
1519 description,
1520 org_independent_flag,
1521 last_update_date,
1522 last_updated_by,
1523 creation_date,
1524 created_by,
1525 last_update_login,
1526 request_id,
1527 program_application_id,
1528 program_id,
1529 program_update_date
1530 )
1531 select
1532 cri.inventory_item_id,
1533 cri.organization_id,
1534 cri.cross_reference_type,
1535 cri.cross_reference,
1536 cri.description,
1537 cri.org_independent_flag,
1538 NVL(cri.last_update_date,sysdate),
1539 user_id, /* last_updated_by */
1540 NVL(cri.creation_date,sysdate),
1541 user_id, /* created_by */
1542 login_id, /* last_update_login */
1543 req_id, /* request_id */
1544 prg_appid, /* program_application_id */
1545 prg_id, /* program_id */
1546 SYSDATE /* program_update_date */
1547 from
1548 mtl_cross_references_interface cri,
1549 mtl_system_items_interface si
1550 where (si.organization_id = cri.organization_id or
1551 cri.organization_id is NULL)
1552 and si.inventory_item_id = cri.inventory_item_id
1553 and cri.cross_reference_type is not NULL
1554 and cri.cross_reference is not NULL
1555 and org_independent_flag is not NULL
1556 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1557
1558
1559 insert into mtl_cross_references
1560 (
1561 inventory_item_id,
1562 organization_id,
1563 cross_reference_type,
1564 cross_reference,
1565 description,
1566 org_independent_flag,
1567 last_update_date,
1568 last_updated_by,
1569 creation_date,
1570 created_by,
1571 last_update_login,
1572 request_id,
1573 program_application_id,
1574 program_id,
1575 program_update_date
1576 )
1580 cr.cross_reference_type,
1577 select
1578 si.inventory_item_id,
1579 cr.organization_id,
1581 cr.cross_reference,
1582 cr.description,
1583 cr.org_independent_flag,
1584 sysdate, /* last_update_date */
1585 user_id, /* last_updated_by */
1586 sysdate, /*creation_date */
1587 user_id, /* created_by */
1588 login_id, /* last_update_login */
1589 req_id, /* request_id */
1590 prg_appid, /* program_application_id */
1591 prg_id, /* program_id */
1592 SYSDATE /* program_update_date */
1593 from
1594 mtl_cross_references cr,
1598 and si.copy_item_id = cr.inventory_item_id
1595 mtl_system_items_interface si
1596 where (si.organization_id = cr.organization_id or
1597 cr.organization_id is NULL)
1599 and not exists (select NULL
1600 from mtl_cross_references_interface cri
1601 where cri.inventory_item_id = si.inventory_item_id
1602 and cri.organization_id = si.organization_id
1603 and cri.cross_reference_type is not NULL
1604 and cri.cross_reference is not NULL
1605 and org_independent_flag is not NULL )
1606 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1607
1608 table_name := 'MTL_DESC_ELEM_VAL_INTERFACE';
1609 delete from MTL_DESC_ELEM_VAL_INTERFACE di
1610 where di.rowid in ( select di2.rowid
1611 from MTL_DESC_ELEM_VAL_INTERFACE di2,
1612 MTL_SYSTEM_ITEMS_INTERFACE si
1613 where si.inventory_item_id = di2.inventory_item_id
1614 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1615
1616 table_name := 'MTL_ITEM_CATEGORIES_INTERFACE';
1617 delete from MTL_ITEM_CATEGORIES_INTERFACE ci
1618 where ci.rowid in ( select ci2.rowid
1619 from MTL_ITEM_CATEGORIES_INTERFACE ci2,
1620 MTL_SYSTEM_ITEMS_INTERFACE si
1621 where si.inventory_item_id = ci2.inventory_item_id
1622 and si.organization_id = ci2.organization_id
1623 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1624
1625 table_name := 'MTL_RELATED_ITEMS_INTERFACE';
1626 delete from MTL_RELATED_ITEMS_INTERFACE ri
1627 where ri.rowid in ( select ri2.rowid
1628 from MTL_RELATED_ITEMS_INTERFACE ri2,
1629 MTL_SYSTEM_ITEMS_INTERFACE si
1630 where si.inventory_item_id = ri2.inventory_item_id
1631 and si.organization_id = ri2.organization_id
1632 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1633
1634 table_name := 'MTL_ITEM_SUB_INVS_INTERFACE';
1635 delete from MTL_ITEM_SUB_INVS_INTERFACE ii
1636 where ii.rowid in ( select ii2.rowid
1637 from MTL_ITEM_SUB_INVS_INTERFACE ii2,
1638 MTL_SYSTEM_ITEMS_INTERFACE si
1639 where si.inventory_item_id = ii2.inventory_item_id
1640 and si.organization_id = ii2.organization_id
1641 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1642
1643 table_name := 'MTL_SECONDARY_LOCS_INTERFACE';
1644 delete from MTL_SECONDARY_LOCS_INTERFACE li
1645 where li.rowid in ( select li2.rowid
1646 from MTL_SECONDARY_LOCS_INTERFACE li2,
1647 MTL_SYSTEM_ITEMS_INTERFACE si
1648 where si.inventory_item_id = li2.inventory_item_id
1649 and si.organization_id = li2.organization_id
1650 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1651
1652 table_name := 'MTL_CROSS_REFERENCES_INTERFACE';
1653 delete from MTL_CROSS_REFERENCES_INTERFACE ri
1654 where rowid in ( select ri2.rowid
1655 from MTL_CROSS_REFERENCES_INTERFACE ri2,
1656 MTL_SYSTEM_ITEMS_INTERFACE si
1657 where si.inventory_item_id = ri2.inventory_item_id
1658 and (si.organization_id = ri2.organization_id or
1659 ri2.organization_id is NULL)
1660 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1661
1662 table_name := 'CST_ITEM_COSTS_INTERFACE';
1663 delete from CST_ITEM_COSTS_INTERFACE ci
1664 where ci.rowid in ( select ci2.rowid
1665 from CST_ITEM_COSTS_INTERFACE ci2,
1666 MTL_SYSTEM_ITEMS_INTERFACE si
1667 where si.inventory_item_id = ci2.inventory_item_id
1668 and si.organization_id = ci2.organization_id
1669 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1670
1671 table_name := 'CST_ITEM_CST_DTLS_INTERFACE';
1675 MTL_SYSTEM_ITEMS_INTERFACE si
1672 delete from CST_ITEM_CST_DTLS_INTERFACE ci
1673 where ci.rowid in ( select ci2.rowid
1674 from CST_ITEM_CST_DTLS_INTERFACE ci2,
1676 where si.inventory_item_id = ci2.inventory_item_id
1677 and si.organization_id = ci2.organization_id
1678 and si.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1679 return(1);
1680
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND then
1683 return (1);
1684 WHEN others THEN
1685 error_message := 'INVPPRCI:' || substrb(sqlerrm,1,150);
1686 message_name := 'BOM_ATO_PROCESS_ERROR';
1687 return(0);
1688
1689 END inproit_process_item;
1690
1691
1692 end invpprci;