[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_GRP
Source
1 PACKAGE BODY INV_ITEM_GRP AS
2 /* $Header: INVGITMB.pls 120.13.12020000.3 2012/07/26 07:07:47 gnookala ship $ */
3
4 -- ------------------------------------------------------------
5 -- -------------- Global variables and constants --------------
6 -- ------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_GRP';
9
10 g_Null_CHAR VARCHAR2(1) := NULL;
11 g_Null_NUM NUMBER := NULL;
12 g_Null_DATE DATE := NULL;
13
14 -- Values used in IOI to indicate the meaning of NULL.
15 --
16 g_Upd_Null_CHAR VARCHAR2(1);
17 g_Upd_Null_NUM NUMBER;
18 g_Upd_Null_DATE DATE;
19 -- Bug 8442016
20 G_PS_IN_PROCESS CONSTANT NUMBER := 2;
21 G_PS_GENERIC_ERROR CONSTANT NUMBER := 3;
22 -- Bug 8442016
23 -- ------------------------------------------------------
24 -- ------------------- Global cursors -------------------
25 -- ------------------------------------------------------
26
27 -- Item cursor for Item_GRP API
28 --
29 CURSOR Item_csr
30 (
31 p_Item_ID IN NUMBER
32 , p_Org_ID IN NUMBER
33 )
34 RETURN Item_rec_type
35 IS
36 SELECT
37 MSI.ORGANIZATION_ID
38 , MP.ORGANIZATION_CODE
39 , MSI.INVENTORY_ITEM_ID
40 , MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
41 , MSI.SEGMENT1
42 , MSI.SEGMENT2
43 , MSI.SEGMENT3
44 , MSI.SEGMENT4
45 , MSI.SEGMENT5
46 , MSI.SEGMENT6
47 , MSI.SEGMENT7
48 , MSI.SEGMENT8
49 , MSI.SEGMENT9
50 , MSI.SEGMENT10
51 , MSI.SEGMENT11
52 , MSI.SEGMENT12
53 , MSI.SEGMENT13
54 , MSI.SEGMENT14
55 , MSI.SEGMENT15
56 , MSI.SEGMENT16
57 , MSI.SEGMENT17
58 , MSI.SEGMENT18
59 , MSI.SEGMENT19
60 , MSI.SEGMENT20
61 , MSI.SUMMARY_FLAG
62 , MSI.ENABLED_FLAG
63 , MSI.START_DATE_ACTIVE
64 , MSI.END_DATE_ACTIVE
65 , MSI.DESCRIPTION
66 , MSI.LONG_DESCRIPTION
67 , MSI.PRIMARY_UOM_CODE
68 , MSI.PRIMARY_UNIT_OF_MEASURE
69 , MSI.ITEM_TYPE
70 , MSI.INVENTORY_ITEM_STATUS_CODE
71 , MSI.ALLOWED_UNITS_LOOKUP_CODE
72 , MSI.ITEM_CATALOG_GROUP_ID
73 , MSI.CATALOG_STATUS_FLAG
74 , MSI.INVENTORY_ITEM_FLAG
75 , MSI.STOCK_ENABLED_FLAG
76 , MSI.MTL_TRANSACTIONS_ENABLED_FLAG
77 , MSI.CHECK_SHORTAGES_FLAG
78 , MSI.REVISION_QTY_CONTROL_CODE
79 , MSI.RESERVABLE_TYPE
80 , MSI.SHELF_LIFE_CODE
81 , MSI.SHELF_LIFE_DAYS
82 , MSI.CYCLE_COUNT_ENABLED_FLAG
83 , MSI.NEGATIVE_MEASUREMENT_ERROR
84 , MSI.POSITIVE_MEASUREMENT_ERROR
85 , MSI.LOT_CONTROL_CODE
86 , MSI.AUTO_LOT_ALPHA_PREFIX
87 , MSI.START_AUTO_LOT_NUMBER
88 , MSI.SERIAL_NUMBER_CONTROL_CODE
89 , MSI.AUTO_SERIAL_ALPHA_PREFIX
90 , MSI.START_AUTO_SERIAL_NUMBER
91 , MSI.LOCATION_CONTROL_CODE
92 , MSI.RESTRICT_SUBINVENTORIES_CODE
93 , MSI.RESTRICT_LOCATORS_CODE
94 , MSI.BOM_ENABLED_FLAG
95 , MSI.BOM_ITEM_TYPE
96 , MSI.BASE_ITEM_ID
97 , MSI.EFFECTIVITY_CONTROL
98 , MSI.ENG_ITEM_FLAG
99 , MSI.ENGINEERING_ECN_CODE
100 , MSI.ENGINEERING_ITEM_ID
101 , MSI.ENGINEERING_DATE
102 , MSI.PRODUCT_FAMILY_ITEM_ID
103 , MSI.AUTO_CREATED_CONFIG_FLAG
104 , MSI.MODEL_CONFIG_CLAUSE_NAME
105 , MSI.COSTING_ENABLED_FLAG
106 , MSI.INVENTORY_ASSET_FLAG
107 , MSI.DEFAULT_INCLUDE_IN_ROLLUP_FLAG
108 , MSI.COST_OF_SALES_ACCOUNT
109 , MSI.STD_LOT_SIZE
110 , MSI.PURCHASING_ITEM_FLAG
111 , MSI.PURCHASING_ENABLED_FLAG
112 , MSI.MUST_USE_APPROVED_VENDOR_FLAG
113 , MSI.ALLOW_ITEM_DESC_UPDATE_FLAG
114 , MSI.RFQ_REQUIRED_FLAG
115 , MSI.OUTSIDE_OPERATION_FLAG
116 , MSI.OUTSIDE_OPERATION_UOM_TYPE
117 , MSI.TAXABLE_FLAG
118 , MSI.PURCHASING_TAX_CODE
119 , MSI.RECEIPT_REQUIRED_FLAG
120 , MSI.INSPECTION_REQUIRED_FLAG
121 , MSI.BUYER_ID
122 , MSI.UNIT_OF_ISSUE
123 , MSI.RECEIVE_CLOSE_TOLERANCE
124 , MSI.INVOICE_CLOSE_TOLERANCE
125 , MSI.UN_NUMBER_ID
126 , MSI.HAZARD_CLASS_ID
127 , MSI.LIST_PRICE_PER_UNIT
128 , MSI.MARKET_PRICE
129 , MSI.PRICE_TOLERANCE_PERCENT
130 , MSI.ROUNDING_FACTOR
131 , MSI.ENCUMBRANCE_ACCOUNT
132 , MSI.EXPENSE_ACCOUNT
133 , MSI.ASSET_CATEGORY_ID
134 , MSI.RECEIPT_DAYS_EXCEPTION_CODE
135 , MSI.DAYS_EARLY_RECEIPT_ALLOWED
136 , MSI.DAYS_LATE_RECEIPT_ALLOWED
137 , MSI.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
138 , MSI.ALLOW_UNORDERED_RECEIPTS_FLAG
139 , MSI.ALLOW_EXPRESS_DELIVERY_FLAG
140 , MSI.QTY_RCV_EXCEPTION_CODE
141 , MSI.QTY_RCV_TOLERANCE
142 , MSI.RECEIVING_ROUTING_ID
143 , MSI.ENFORCE_SHIP_TO_LOCATION_CODE
144 , MSI.WEIGHT_UOM_CODE
145 , MSI.UNIT_WEIGHT
146 , MSI.VOLUME_UOM_CODE
147 , MSI.UNIT_VOLUME
148 , MSI.CONTAINER_ITEM_FLAG
149 , MSI.VEHICLE_ITEM_FLAG
150 , MSI.CONTAINER_TYPE_CODE
151 , MSI.INTERNAL_VOLUME
152 , MSI.MAXIMUM_LOAD_WEIGHT
153 , MSI.MINIMUM_FILL_PERCENT
154 , MSI.INVENTORY_PLANNING_CODE
155 , MSI.PLANNER_CODE
156 , MSI.PLANNING_MAKE_BUY_CODE
157 , MSI.MIN_MINMAX_QUANTITY
158 , MSI.MAX_MINMAX_QUANTITY
159 , MSI.MINIMUM_ORDER_QUANTITY
160 , MSI.MAXIMUM_ORDER_QUANTITY
161 , MSI.ORDER_COST
162 , MSI.CARRYING_COST
163 , MSI.SOURCE_TYPE
164 , MSI.SOURCE_ORGANIZATION_ID
165 , MSI.SOURCE_SUBINVENTORY
166 , MSI.MRP_SAFETY_STOCK_CODE
167 , MSI.SAFETY_STOCK_BUCKET_DAYS
168 , MSI.MRP_SAFETY_STOCK_PERCENT
169 , MSI.FIXED_ORDER_QUANTITY
170 , MSI.FIXED_DAYS_SUPPLY
171 , MSI.FIXED_LOT_MULTIPLIER
172 , MSI.MRP_PLANNING_CODE
173 , MSI.ATO_FORECAST_CONTROL
174 , MSI.PLANNING_EXCEPTION_SET
175 , MSI.END_ASSEMBLY_PEGGING_FLAG
176 , MSI.SHRINKAGE_RATE
177 , MSI.ROUNDING_CONTROL_TYPE
178 , MSI.ACCEPTABLE_EARLY_DAYS
179 , MSI.REPETITIVE_PLANNING_FLAG
180 , MSI.OVERRUN_PERCENTAGE
181 , MSI.ACCEPTABLE_RATE_INCREASE
182 , MSI.ACCEPTABLE_RATE_DECREASE
183 , MSI.MRP_CALCULATE_ATP_FLAG
184 , MSI.AUTO_REDUCE_MPS
185 , MSI.PLANNING_TIME_FENCE_CODE
186 , MSI.PLANNING_TIME_FENCE_DAYS
187 , MSI.DEMAND_TIME_FENCE_CODE
188 , MSI.DEMAND_TIME_FENCE_DAYS
189 , MSI.RELEASE_TIME_FENCE_CODE
190 , MSI.RELEASE_TIME_FENCE_DAYS
191 , MSI.PREPROCESSING_LEAD_TIME
192 , MSI.FULL_LEAD_TIME
193 , MSI.POSTPROCESSING_LEAD_TIME
194 , MSI.FIXED_LEAD_TIME
195 , MSI.VARIABLE_LEAD_TIME
196 , MSI.CUM_MANUFACTURING_LEAD_TIME
197 , MSI.CUMULATIVE_TOTAL_LEAD_TIME
198 , MSI.LEAD_TIME_LOT_SIZE
199 , MSI.BUILD_IN_WIP_FLAG
200 , MSI.WIP_SUPPLY_TYPE
201 , MSI.WIP_SUPPLY_SUBINVENTORY
202 , MSI.WIP_SUPPLY_LOCATOR_ID
203 , MSI.OVERCOMPLETION_TOLERANCE_TYPE
204 , MSI.OVERCOMPLETION_TOLERANCE_VALUE
205 , MSI.CUSTOMER_ORDER_FLAG
206 , MSI.CUSTOMER_ORDER_ENABLED_FLAG
207 , MSI.SHIPPABLE_ITEM_FLAG
208 , MSI.INTERNAL_ORDER_FLAG
209 , MSI.INTERNAL_ORDER_ENABLED_FLAG
210 , MSI.SO_TRANSACTIONS_FLAG
211 , MSI.PICK_COMPONENTS_FLAG
212 , MSI.ATP_FLAG
213 , MSI.REPLENISH_TO_ORDER_FLAG
214 , MSI.ATP_RULE_ID
215 , MSI.ATP_COMPONENTS_FLAG
216 , MSI.SHIP_MODEL_COMPLETE_FLAG
217 , MSI.PICKING_RULE_ID
218 , MSI.COLLATERAL_FLAG
219 , MSI.DEFAULT_SHIPPING_ORG
220 , MSI.RETURNABLE_FLAG
221 , MSI.RETURN_INSPECTION_REQUIREMENT
222 , MSI.OVER_SHIPMENT_TOLERANCE
223 , MSI.UNDER_SHIPMENT_TOLERANCE
224 , MSI.OVER_RETURN_TOLERANCE
225 , MSI.UNDER_RETURN_TOLERANCE
226 , MSI.INVOICEABLE_ITEM_FLAG
227 , MSI.INVOICE_ENABLED_FLAG
228 , MSI.ACCOUNTING_RULE_ID
229 , MSI.INVOICING_RULE_ID
230 , MSI.TAX_CODE
231 , MSI.SALES_ACCOUNT
232 , MSI.PAYMENT_TERMS_ID
233 , MSI.COVERAGE_SCHEDULE_ID
234 , MSI.SERVICE_DURATION
235 , MSI.SERVICE_DURATION_PERIOD_CODE
236 , MSI.SERVICEABLE_PRODUCT_FLAG
237 , MSI.SERVICE_STARTING_DELAY
238 , MSI.MATERIAL_BILLABLE_FLAG
239 , MSI.SERVICEABLE_COMPONENT_FLAG
240 , MSI.PREVENTIVE_MAINTENANCE_FLAG
241 , MSI.PRORATE_SERVICE_FLAG
242 , MSI.WH_UPDATE_DATE
243 , MSI.EQUIPMENT_TYPE
244 , MSI.RECOVERED_PART_DISP_CODE
245 , MSI.DEFECT_TRACKING_ON_FLAG
246 , MSI.EVENT_FLAG
247 , MSI.ELECTRONIC_FLAG
248 , MSI.DOWNLOADABLE_FLAG
249 , MSI.VOL_DISCOUNT_EXEMPT_FLAG
250 , MSI.COUPON_EXEMPT_FLAG
251 , MSI.COMMS_NL_TRACKABLE_FLAG
252 , MSI.ASSET_CREATION_CODE
253 , MSI.COMMS_ACTIVATION_REQD_FLAG
254 , MSI.WEB_STATUS
255 , MSI.ORDERABLE_ON_WEB_FLAG
256 , MSI.BACK_ORDERABLE_FLAG
257 , MSI.INDIVISIBLE_FLAG
258 , MSI.DIMENSION_UOM_CODE
259 , MSI.UNIT_LENGTH
260 , MSI.UNIT_WIDTH
261 , MSI.UNIT_HEIGHT
262 , MSI.BULK_PICKED_FLAG
263 , MSI.LOT_STATUS_ENABLED
264 , MSI.DEFAULT_LOT_STATUS_ID
265 , MSI.SERIAL_STATUS_ENABLED
266 , MSI.DEFAULT_SERIAL_STATUS_ID
267 , MSI.LOT_SPLIT_ENABLED
268 , MSI.LOT_MERGE_ENABLED
269 , MSI.INVENTORY_CARRY_PENALTY
270 , MSI.OPERATION_SLACK_PENALTY
271 , MSI.FINANCING_ALLOWED_FLAG
272 , MSI.EAM_ITEM_TYPE
273 , MSI.EAM_ACTIVITY_TYPE_CODE
274 , MSI.EAM_ACTIVITY_CAUSE_CODE
275 , MSI.EAM_ACT_NOTIFICATION_FLAG
276 , MSI.EAM_ACT_SHUTDOWN_STATUS
277 , MSI.DUAL_UOM_CONTROL
278 , MSI.SECONDARY_UOM_CODE
279 , MSI.DUAL_UOM_DEVIATION_HIGH
280 , MSI.DUAL_UOM_DEVIATION_LOW
281 --
282 , MSI.SERVICE_ITEM_FLAG
283 , MSI.VENDOR_WARRANTY_FLAG
284 , MSI.USAGE_ITEM_FLAG
285 --
286 , MSI.CONTRACT_ITEM_TYPE_CODE
287 , MSI.SUBSCRIPTION_DEPEND_FLAG
288 --
289 , MSI.SERV_REQ_ENABLED_CODE
290 , MSI.SERV_BILLING_ENABLED_FLAG
291 , MSI.SERV_IMPORTANCE_LEVEL
292 , MSI.PLANNED_INV_POINT_FLAG
293 , MSI.LOT_TRANSLATE_ENABLED
294 , MSI.DEFAULT_SO_SOURCE_TYPE
295 , MSI.CREATE_SUPPLY_FLAG
296 , MSI.SUBSTITUTION_WINDOW_CODE
297 , MSI.SUBSTITUTION_WINDOW_DAYS
298 --Added as part of 11.5.9
299 , MSI.LOT_SUBSTITUTION_ENABLED
300 , MSI.MINIMUM_LICENSE_QUANTITY
301 , MSI.EAM_ACTIVITY_SOURCE_CODE
302 , MSI.IB_ITEM_INSTANCE_CLASS
303 , MSI.CONFIG_MODEL_TYPE
304 --Added as part of 11.5.10
305 , MSI.TRACKING_QUANTITY_IND
306 , MSI.ONT_PRICING_QTY_SOURCE
307 , MSI.SECONDARY_DEFAULT_IND
308 , MSI.CONFIG_ORGS
309 , MSI.CONFIG_MATCH
310 --
311 , MSI.ATTRIBUTE_CATEGORY
312 , MSI.ATTRIBUTE1
313 , MSI.ATTRIBUTE2
314 , MSI.ATTRIBUTE3
315 , MSI.ATTRIBUTE4
316 , MSI.ATTRIBUTE5
317 , MSI.ATTRIBUTE6
318 , MSI.ATTRIBUTE7
319 , MSI.ATTRIBUTE8
320 , MSI.ATTRIBUTE9
321 , MSI.ATTRIBUTE10
322 , MSI.ATTRIBUTE11
323 , MSI.ATTRIBUTE12
324 , MSI.ATTRIBUTE13
325 , MSI.ATTRIBUTE14
326 , MSI.ATTRIBUTE15
327 /* Start Bug 3713912 */
328 , MSI.ATTRIBUTE16
329 , MSI.ATTRIBUTE17
330 , MSI.ATTRIBUTE18
331 , MSI.ATTRIBUTE19
332 , MSI.ATTRIBUTE20
333 , MSI.ATTRIBUTE21
334 , MSI.ATTRIBUTE22
335 , MSI.ATTRIBUTE23
336 , MSI.ATTRIBUTE24
337 , MSI.ATTRIBUTE25
338 , MSI.ATTRIBUTE26
339 , MSI.ATTRIBUTE27
340 , MSI.ATTRIBUTE28
341 , MSI.ATTRIBUTE29
342 , MSI.ATTRIBUTE30
343 /* End Bug 3713912 */
344 , MSI.GLOBAL_ATTRIBUTE_CATEGORY
345 , MSI.GLOBAL_ATTRIBUTE1
346 , MSI.GLOBAL_ATTRIBUTE2
347 , MSI.GLOBAL_ATTRIBUTE3
348 , MSI.GLOBAL_ATTRIBUTE4
349 , MSI.GLOBAL_ATTRIBUTE5
350 , MSI.GLOBAL_ATTRIBUTE6
351 , MSI.GLOBAL_ATTRIBUTE7
352 , MSI.GLOBAL_ATTRIBUTE8
353 , MSI.GLOBAL_ATTRIBUTE9
354 , MSI.GLOBAL_ATTRIBUTE10
355 , MSI.GLOBAL_ATTRIBUTE11
356 , MSI.GLOBAL_ATTRIBUTE12
357 , MSI.GLOBAL_ATTRIBUTE13
358 , MSI.GLOBAL_ATTRIBUTE14
359 , MSI.GLOBAL_ATTRIBUTE15
360 , MSI.GLOBAL_ATTRIBUTE16
361 , MSI.GLOBAL_ATTRIBUTE17
362 , MSI.GLOBAL_ATTRIBUTE18
363 , MSI.GLOBAL_ATTRIBUTE19
364 , MSI.GLOBAL_ATTRIBUTE20
365 --
366 , MSI.Lifecycle_Id
367 , MSI.Current_Phase_Id
368 --
369 , MSI.CREATION_DATE
370 , MSI.CREATED_BY
371 , MSI.LAST_UPDATE_DATE
372 , MSI.LAST_UPDATED_BY
373 , MSI.LAST_UPDATE_LOGIN
374 , MSI.REQUEST_ID
375 , MSI.PROGRAM_APPLICATION_ID
376 , MSI.PROGRAM_ID
377 , MSI.PROGRAM_UPDATE_DATE
378 , MSI.VMI_MINIMUM_UNITS
379 , MSI.VMI_MINIMUM_DAYS
380 , MSI.VMI_MAXIMUM_UNITS
381 , MSI.VMI_MAXIMUM_DAYS
382 , MSI.VMI_FIXED_ORDER_QUANTITY
383 , MSI.SO_AUTHORIZATION_FLAG
384 , MSI.CONSIGNED_FLAG
385 , MSI.ASN_AUTOEXPIRE_FLAG
386 , MSI.VMI_FORECAST_TYPE
387 , MSI.FORECAST_HORIZON
388 , MSI.EXCLUDE_FROM_BUDGET_FLAG
389 , MSI.DAYS_TGT_INV_SUPPLY
390 , MSI.DAYS_TGT_INV_WINDOW
391 , MSI.DAYS_MAX_INV_SUPPLY
392 , MSI.DAYS_MAX_INV_WINDOW
393 , MSI.DRP_PLANNED_FLAG
394 , MSI.CRITICAL_COMPONENT_FLAG
395 , MSI.CONTINOUS_TRANSFER
396 , MSI.CONVERGENCE
397 , MSI.DIVERGENCE
398 /* Start Bug 3713912 */
399 , MSI.LOT_DIVISIBLE_FLAG
400 , MSI.GRADE_CONTROL_FLAG
401 , MSI.DEFAULT_GRADE
402 , MSI.CHILD_LOT_FLAG
403 , MSI.PARENT_CHILD_GENERATION_FLAG
404 , MSI.CHILD_LOT_PREFIX
405 , MSI.CHILD_LOT_STARTING_NUMBER
406 , MSI.CHILD_LOT_VALIDATION_FLAG
407 , MSI.COPY_LOT_ATTRIBUTE_FLAG
408 , MSI.RECIPE_ENABLED_FLAG
409 , MSI.PROCESS_QUALITY_ENABLED_FLAG
410 , MSI.PROCESS_EXECUTION_ENABLED_FLAG
411 , MSI.PROCESS_COSTING_ENABLED_FLAG
412 , MSI.PROCESS_SUPPLY_SUBINVENTORY
413 , MSI.PROCESS_SUPPLY_LOCATOR_ID
414 , MSI.PROCESS_YIELD_SUBINVENTORY
415 , MSI.PROCESS_YIELD_LOCATOR_ID
416 , MSI.HAZARDOUS_MATERIAL_FLAG
417 , MSI.CAS_NUMBER
418 , MSI.RETEST_INTERVAL
419 , MSI.EXPIRATION_ACTION_INTERVAL
420 , MSI.EXPIRATION_ACTION_CODE
421 , MSI.MATURITY_DAYS
422 , MSI.HOLD_DAYS
423 , 1 -- Process Item Record.
424 /* End Bug 3713912 */
425 /* R12 Enhancement */
426 , MSI.CHARGE_PERIODICITY_CODE
427 , MSI.REPAIR_LEADTIME
428 , MSI.REPAIR_YIELD
429 , MSI.PREPOSITION_POINT
430 , MSI.REPAIR_PROGRAM
431 , MSI.SUBCONTRACTING_COMPONENT
432 , MSI.OUTSOURCED_ASSEMBLY
433 /* R12 C Attributes */
434 , MSI.GDSN_OUTBOUND_ENABLED_FLAG
435 , MSI.TRADE_ITEM_DESCRIPTOR
436 , MSI.STYLE_ITEM_FLAG
437 , MSI.STYLE_ITEM_ID
438 FROM
439 MTL_SYSTEM_ITEMS_VL MSI
440 , MTL_PARAMETERS MP
441 WHERE
442 MSI.INVENTORY_ITEM_ID = p_Item_ID
443 AND MSI.ORGANIZATION_ID = p_Org_ID
444 AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID;
445
446 -- ------------------------------------------------------
447 -- ----------------- Local Procedure Specs --------------
448 -- ------------------------------------------------------
449
450 PROCEDURE Insert_MSII_Row
451 (
452 p_commit IN VARCHAR2
453 , p_transaction_type IN VARCHAR2
454 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
455 , p_revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
456 , p_Template_Id IN NUMBER
457 , p_Template_Name IN VARCHAR2
458 , x_set_process_id OUT NOCOPY NUMBER
459 , x_return_status OUT NOCOPY VARCHAR2
460 , x_return_err OUT NOCOPY VARCHAR2
461 );
462
463 PROCEDURE Insert_Revision_Record
464 (
465 p_item_rowid IN ROWID
466 ,p_Revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
467 ,p_set_process_id IN NUMBER
468 ,x_return_status OUT NOCOPY VARCHAR2
469 ,x_return_err OUT NOCOPY VARCHAR2
470 );
471
472 PROCEDURE IOI_Process
473 (
474 p_transaction_type IN VARCHAR2
475 , p_commit IN VARCHAR2
476 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
477 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
478 , p_revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
479 , p_Template_Id IN NUMBER
480 , p_Template_Name IN VARCHAR2
481 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
482 , x_return_status OUT NOCOPY VARCHAR2
483 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
484 -- Bug 9092888 - changes
485 , p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
486 , p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
487 -- Bug 9092888 - changes
488 );
489
490 PROCEDURE Get_IOI_Errors
491 (
492 p_transaction_id IN NUMBER
493 , p_inventory_item_id IN NUMBER
494 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
495 , x_return_status OUT NOCOPY VARCHAR2
496 , x_return_err OUT NOCOPY VARCHAR2
497 );
498
499 -- -------------------------------------------------------
500 -- --------------------- Procedures ----------------------
501 -- -------------------------------------------------------
502
503 -- Bug 8442016
504
505 -- Private API to call tempalte application for UDAs
506 PROCEDURE Apply_Templates_For_UDAs
507 ( p_batch_id NUMBER,
508 p_template_id NUMBER,
509 p_user_id NUMBER,
510 p_login_id NUMBER,
511 p_prog_appid NUMBER,
512 p_prog_id NUMBER,
513 p_request_id NUMBER,
514 x_return_status OUT NOCOPY VARCHAR2,
515 ERRBUF OUT NOCOPY VARCHAR2,
516 RETCODE OUT NOCOPY VARCHAR2
517 )
518 IS
519 l_return_status VARCHAR2(2);
520 l_entity_sql VARCHAR2(32000);
521 l_gdsn_entity_sql VARCHAR2(32000);
522 l_item_dl_id NUMBER;
523 l_item_rev_dl_id NUMBER;
524 l_item_org_dl_id NUMBER;
525 l_item_gtin_dl_id NUMBER;
526 l_item_gtin_multi_dl_id NUMBER;
527 l_msg_data VARCHAR2(4000);
528
529 CURSOR c_data_levels IS
530 SELECT ATTR_GROUP_TYPE, DATA_LEVEL_ID, DATA_LEVEL_NAME
531 FROM EGO_DATA_LEVEL_B
532 WHERE ATTR_GROUP_TYPE IN ('EGO_ITEMMGMT_GROUP', 'EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
533 AND APPLICATION_ID = 431
534 AND DATA_LEVEL_NAME IN ( 'ITEM_LEVEL', 'ITEM_REVISION_LEVEL', 'ITEM_ORG' );
535
536 BEGIN
537 RETCODE := '0';
538
539 FOR i IN c_data_levels LOOP
540 IF i.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND i.DATA_LEVEL_NAME = 'ITEM_LEVEL' THEN
541 l_item_dl_id := i.DATA_LEVEL_ID;
542 ELSIF i.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND i.DATA_LEVEL_NAME = 'ITEM_ORG' THEN
543 l_item_org_dl_id := i.DATA_LEVEL_ID;
544 ELSIF i.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND i.DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL' THEN
545 l_item_rev_dl_id := i.DATA_LEVEL_ID;
546 ELSIF i.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS' AND i.DATA_LEVEL_NAME = 'ITEM_LEVEL' THEN
547 l_item_gtin_dl_id := i.DATA_LEVEL_ID;
548 ELSIF i.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS' AND i.DATA_LEVEL_NAME = 'ITEM_LEVEL' THEN
549 l_item_gtin_multi_dl_id := i.DATA_LEVEL_ID;
550 END IF;
551 END LOOP;
552
553 l_entity_sql := q'#
554 SELECT
555 MIRI.TEMPLATE_ID,
556 MSIB.INVENTORY_ITEM_ID ,
557 MSIB.ORGANIZATION_ID,
558 MSIB.ITEM_CATALOG_GROUP_ID,
559 NULL AS PK1_VALUE,
560 NULL AS PK2_VALUE,
561 NULL AS PK3_VALUE,
562 NULL AS PK4_VALUE,
563 NULL AS PK5_VALUE,
564 MIRI.REVISION_ID, #' ||
565 l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
566 FROM
567 MTL_ITEM_REVISIONS_INTERFACE MIRI,
568 MTL_SYSTEM_ITEMS_B MSIB
569 WHERE MSIB.ITEM_CATALOG_GROUP_ID IS NOT NULL
570 AND MIRI.TEMPLATE_ID IS NOT NULL
571 AND MIRI.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
572 AND MIRI.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
573 AND MIRI.SET_PROCESS_ID = #' || p_batch_id || q'#
574 AND MIRI.PROCESS_FLAG = 7
575 AND NOT EXISTS
576 (SELECT NULL
577 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
578 WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
579 AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
580 AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
581 AND MSII.PROCESS_FLAG = MIRI.PROCESS_FLAG)
582 UNION ALL
583 SELECT /*+ LEADING(MSII) USE_NL_WITH_INDEX(MIRI, MTL_ITEM_REVS_INTERFACE_N2 ) */
584 MSII.TEMPLATE_ID,
585 MSII.INVENTORY_ITEM_ID ,
586 MSII.ORGANIZATION_ID,
587 MSII.ITEM_CATALOG_GROUP_ID,
588 NULL AS PK1_VALUE,
589 NULL AS PK2_VALUE,
590 NULL AS PK3_VALUE,
591 NULL AS PK4_VALUE,
592 NULL AS PK5_VALUE,
593 (CASE WHEN MIRI.REVISION_Id IS NULL
594 THEN (SELECT Max(REVISION_ID)
595 FROM MTL_ITEM_REVISIONS_B MIRB
596 WHERE MIRB.EFFECTIVITY_DATE <= SYSDATE
597 AND MIRB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
598 AND MIRB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
599 )
600 ELSE MIRI.REVISION_ID
601 END) REVISION_ID, #' ||
602 l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
603 FROM
604 MTL_SYSTEM_ITEMS_INTERFACE MSII,
605 MTL_ITEM_REVISIONS_INTERFACE MIRI
606 WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
607 AND MSII.TEMPLATE_ID IS NOT NULL
608 AND MIRI.INVENTORY_ITEM_ID(+) = MSII.INVENTORY_ITEM_ID
609 AND MIRI.ORGANIZATION_ID(+) = MSII.ORGANIZATION_ID
610 AND MIRI.SET_PROCESS_ID(+) = MSII.SET_PROCESS_ID
611 AND MIRI.PROCESS_FLAG(+) = 7
612 AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
613 AND MSII.PROCESS_FLAG = 7
614 UNION ALL
615 SELECT
616 MSII.TEMPLATE_ID,
617 MSII.INVENTORY_ITEM_ID ,
618 MSII.ORGANIZATION_ID,
619 MSII.ITEM_CATALOG_GROUP_ID,
620 NULL AS PK1_VALUE,
621 NULL AS PK2_VALUE,
622 NULL AS PK3_VALUE,
623 NULL AS PK4_VALUE,
624 NULL AS PK5_VALUE,
625 NULL AS REVISION_ID, #' ||
626 l_item_dl_id || q'# AS DATA_LEVEL_ID
627 FROM
628 MTL_SYSTEM_ITEMS_INTERFACE MSII
629 WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
630 AND MSII.TEMPLATE_ID IS NOT NULL
631 AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
632 AND MSII.PROCESS_FLAG = 7
633 UNION ALL
634 SELECT
635 MSII.TEMPLATE_ID,
636 MSII.INVENTORY_ITEM_ID ,
637 MSII.ORGANIZATION_ID,
638 MSII.ITEM_CATALOG_GROUP_ID,
639 NULL AS PK1_VALUE,
640 NULL AS PK2_VALUE,
641 NULL AS PK3_VALUE,
642 NULL AS PK4_VALUE,
643 NULL AS PK5_VALUE,
644 NULL AS REVISION_ID, #' ||
645 l_item_org_dl_id || q'# AS DATA_LEVEL_ID
646 FROM
647 MTL_SYSTEM_ITEMS_INTERFACE MSII
648 WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
649 AND MSII.TEMPLATE_ID IS NOT NULL
650 AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
651 AND MSII.PROCESS_FLAG = 7 #';
652
653
654 l_gdsn_entity_sql := q'#
655 SELECT
656 MSII.TEMPLATE_ID,
657 MSII.INVENTORY_ITEM_ID ,
658 MSII.ORGANIZATION_ID,
659 MSII.ITEM_CATALOG_GROUP_ID,
660 NULL AS PK1_VALUE,
661 NULL AS PK2_VALUE,
662 NULL AS PK3_VALUE,
663 NULL AS PK4_VALUE,
664 NULL AS PK5_VALUE,
665 NULL AS REVISION_ID, #' ||
666 l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
667 FROM
668 MTL_SYSTEM_ITEMS_INTERFACE MSII
669 WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
670 AND MSII.TEMPLATE_ID IS NOT NULL
671 AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
672 AND MSII.PROCESS_FLAG = 7
673 AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
674 UNION ALL
675 SELECT
676 MSII.TEMPLATE_ID,
677 MSII.INVENTORY_ITEM_ID ,
678 MSII.ORGANIZATION_ID,
679 MSII.ITEM_CATALOG_GROUP_ID,
680 NULL AS PK1_VALUE,
681 NULL AS PK2_VALUE,
682 NULL AS PK3_VALUE,
683 NULL AS PK4_VALUE,
684 NULL AS PK5_VALUE,
685 NULL AS REVISION_ID, #' ||
686 l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
687 FROM
688 MTL_SYSTEM_ITEMS_INTERFACE MSII
689 WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
690 AND MSII.TEMPLATE_ID IS NOT NULL
691 AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
692 AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
693 AND MSII.PROCESS_FLAG = 7 #';
694
695
696 EGO_IMPORT_UTIL_PVT.Call_UDA_Apply_Template
697 ( p_batch_id => p_batch_id,
698 p_entity_sql => l_entity_sql,
699 p_gdsn_entity_sql => l_gdsn_entity_sql,
700 p_user_id => p_user_id,
701 p_login_id => p_login_id,
702 p_prog_appid => p_prog_appid,
703 p_prog_id => p_prog_id,
704 p_request_id => p_request_id,
705 x_return_status => l_return_status,
706 x_err_msg => l_msg_data
707 );
708 IF (l_return_status = '2') THEN
709 RETCODE := '2';
710 ERRBUF := l_msg_data;
711 ELSIF (l_return_status = '1') THEN
712 RETCODE := '1';
713 ERRBUF := l_msg_data;
714 ELSE
715 RETCODE := '0';
716 ERRBUF := NULL;
717 END IF;
718
719 EXCEPTION
720 WHEN OTHERS THEN
721
722 ----------------------------------------
723 -- Mark all rows in process as errors --
724 ----------------------------------------
725 UPDATE EGO_ITM_USR_ATTR_INTRFC
726 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
727 WHERE DATA_SET_ID = p_batch_id
728 AND PROCESS_STATUS = G_PS_IN_PROCESS;
729
730 RETCODE := '2';
731 ERRBUF := 'When Others Exception - Apply_Templates_For_UDAs';
732
733 END Apply_Templates_For_UDAs;
734
735 -- Bug 8442016
736
737 -- -------------------- Create_Item --------------------
738 --Start:3259338: Overloaded procedures Update_Item,Create_item
739 PROCEDURE Create_Item
740 (
741 p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
742 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
743 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
744 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
745 , x_return_status OUT NOCOPY VARCHAR2
746 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
747 , p_Template_Id IN NUMBER DEFAULT NULL
748 , p_Template_Name IN VARCHAR2 DEFAULT NULL
749 )
750 IS
751 BEGIN
752 INV_ITEM_GRP.Create_Item(p_commit => p_commit
753 ,p_validation_level => p_validation_level
754 ,p_Item_rec => p_Item_rec
755 ,x_Item_rec => x_Item_rec
756 ,x_return_status => x_return_status
757 ,x_Error_tbl => x_Error_tbl
758 ,p_Template_Id => p_Template_Id
759 ,p_Template_Name => p_Template_Name
760 ,p_Revision_rec => g_Miss_Revision_rec);
761 END Create_Item;
762 --End:3259338: Overloaded procedures Update_Item,Create_item
763
764 PROCEDURE Create_Item
765 (
766 p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
767 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
768 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
769 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
770 , x_return_status OUT NOCOPY VARCHAR2
771 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
772 , p_Template_Id IN NUMBER DEFAULT NULL
773 , p_Template_Name IN VARCHAR2 DEFAULT NULL
774 , p_Revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
775 -- Bug 9092888 - changes
776 , p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
777 , p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
778 -- Bug 9092888 - changes
779 )
780 IS
781 c_transaction_type CONSTANT VARCHAR2(10) := 'CREATE';
782 l_return_status VARCHAR2(1);
783 l_idx BINARY_INTEGER;
784 BEGIN
785
786 SAVEPOINT Create_Item_GRP;
787 x_return_status := fnd_api.g_RET_STS_SUCCESS;
788
789 IOI_Process
790 (
791 p_transaction_type => c_transaction_type
792 , p_commit => p_commit
793 , p_validation_level => p_validation_level
794 , p_Item_rec => p_Item_rec
795 , p_revision_rec => p_Revision_rec
796 , p_Template_Id => p_Template_Id
797 , p_Template_Name => p_Template_Name
798 , x_Item_rec => x_Item_rec
799 , x_return_status => l_return_status
800 , x_Error_tbl => x_Error_tbl
801 -- Bug 9092888 - Changes
802 , p_attributes_row_table => p_attributes_row_table -- IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
803 , p_attributes_data_table => p_attributes_data_table -- IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
804 -- Bug 9092888 - Changes
805 );
806
807 x_return_status := l_return_status;
808
809 EXCEPTION
810
811 WHEN others THEN
812 ROLLBACK TO Create_Item_GRP;
813 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
814 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
815 x_Error_tbl(l_idx).UNIQUE_ID := 999;
816 x_Error_tbl(l_idx).TABLE_NAME := '';
817 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('INV_ITEM_GRP.Create_Item: Unexpexted error: ' || SQLERRM,1,239);
818 -- INV_message_s.sql_error('Create_Item', x_progress, SQLCODE);
819 -- INV_ITEM_debug.put_line('Create_Item: ');
820 -- RAISE;
821
822 END Create_Item;
823
824 -- -------------------- Update_Item -------------------
825 --Start:3259338: Overloaded procedures Update_Item,Create_item
826 PROCEDURE Update_Item
827 (
828 p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
829 , p_lock_rows IN VARCHAR2 DEFAULT fnd_api.g_TRUE
830 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
831 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
832 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
833 , x_return_status OUT NOCOPY VARCHAR2
834 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
835 , p_Template_Id IN NUMBER DEFAULT NULL
836 , p_Template_Name IN VARCHAR2 DEFAULT NULL
837 )
838 IS
839 BEGIN
840
841 INV_ITEM_GRP.Update_Item(
842 p_commit => p_commit
843 ,p_lock_rows => p_lock_rows
844 ,p_validation_level => p_validation_level
845 ,p_Item_rec => p_Item_rec
846 ,x_Item_rec => x_Item_rec
847 ,x_return_status => x_return_status
848 ,x_Error_tbl => x_Error_tbl
849 ,p_Template_Id => p_Template_Id
850 ,p_Template_Name => p_Template_Name
851 ,p_Revision_rec => g_Miss_Revision_rec);
852
853 END Update_Item;
854 --End:3259338: Overloaded procedures Update_Item,Create_item
855
856 PROCEDURE Update_Item
857 (
858 p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
859 , p_lock_rows IN VARCHAR2 DEFAULT fnd_api.g_TRUE
860 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
861 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
862 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
863 , x_return_status OUT NOCOPY VARCHAR2
864 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
865 , p_Template_Id IN NUMBER DEFAULT NULL
866 , p_Template_Name IN VARCHAR2 DEFAULT NULL
867 , p_Revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
868 )
869 IS
870 c_transaction_type CONSTANT VARCHAR2(10) := 'UPDATE';
871 l_return_status VARCHAR2(1);
872 l_idx BINARY_INTEGER;
873 BEGIN
874
875 SAVEPOINT Update_Item_GRP;
876 x_return_status := fnd_api.g_RET_STS_SUCCESS;
877
878 IOI_Process
879 (
880 p_transaction_type => c_transaction_type
881 , p_commit => p_commit
882 , p_validation_level => p_validation_level
883 , p_Item_rec => p_Item_rec
884 , p_revision_rec => p_Revision_rec
885 , p_Template_Id => p_Template_Id
886 , p_Template_Name => p_Template_Name
887 , x_Item_rec => x_Item_rec
888 , x_return_status => l_return_status
889 , x_Error_tbl => x_Error_tbl
890 );
891
892 x_return_status := l_return_status;
893
894 EXCEPTION
895
896 WHEN others THEN
897 ROLLBACK TO Update_Item_GRP;
898 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
899 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
900 x_Error_tbl(l_idx).UNIQUE_ID := 999;
901 x_Error_tbl(l_idx).TABLE_NAME := '';
902 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('INV_ITEM_GRP.Update_Item: Unexpexted error: ' || SQLERRM,1,239);
903
904 END Update_Item;
905
906 -- --------------------- Lock_Item ---------------------
907
908 PROCEDURE Lock_Item
909 (
910 p_Item_ID IN NUMBER
911 , p_Org_ID IN NUMBER
912 , x_return_status OUT NOCOPY VARCHAR2
913 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
914 )
915 IS
916 BEGIN
917
918 INV_ITEM_PVT.Lock_Org_Items
919 (
920 p_Item_ID => p_Item_ID
921 , p_Org_ID => p_Org_ID
922 , p_lock_Master => fnd_api.g_TRUE
923 , p_lock_Orgs => fnd_api.g_TRUE
924 , x_return_status => x_return_status
925 );
926
927 END Lock_Item;
928
929 -- Bug 9092888 - changes
930 FUNCTION SKU_ITEM_PREPROCESS
931 (p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
932 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
933 ,p_set_process_id IN NUMBER
934 ,p_Item_rec IN INV_ITEM_GRP.Item_rec_type
935 ,x_return_err OUT NOCOPY VARCHAR2
936 )
937 RETURN VARCHAR2 IS
938 l_ix NUMBER;
939 l_attr_name VARCHAR2(100);
940 l_attr_group_id NUMBER;
941 l_attr_group_name VARCHAR2(100);
942 l_attr_group_type VARCHAR2(100);
943 l_row_identifier NUMBER;
944 l_token_table ERROR_HANDLER.Token_Tbl_Type;
945 l_err_msg_name VARCHAR2(30);
946
947 BEGIN
948 IF p_attributes_row_table IS NOT NULL AND
949 p_attributes_data_table IS NOT NULL AND
950 p_attributes_data_table.Count > 0 AND
951 p_attributes_row_table.Count > 0
952 THEN
953 FOR j IN 1 .. p_attributes_data_table.COUNT LOOP
954 l_ix := -1;
955
956 FOR k IN 1 .. p_attributes_row_table.COUNT LOOP
957 IF p_attributes_row_table(k).ROW_IDENTIFIER =
958 p_attributes_data_table(j).ROW_IDENTIFIER
959 THEN
960 l_ix := k;
961 exit;
962 END IF;
963 END LOOP;
964
965 IF l_ix = -1 THEN
966 x_return_err := 'SKU_ITEM_PREPROCESS - Unexpected Error occured';
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END IF;
969
970 l_attr_name := p_attributes_data_table(j).ATTR_NAME;
971 l_attr_group_id := p_attributes_row_table(l_ix).ATTR_GROUP_ID;
972 l_attr_group_name := p_attributes_row_table(l_ix).ATTR_GROUP_NAME;
973 l_attr_group_type := p_attributes_row_table(l_ix).ATTR_GROUP_TYPE;
974 l_row_identifier := p_attributes_row_table(l_ix).ROW_IDENTIFIER;
975
976 IF (l_attr_group_name IS NULL) THEN
977 IF (l_attr_group_id IS NOT NULL) THEN
978
979 BEGIN
980 SELECT attr_group_name INTO l_attr_group_name
981 FROM ego_attr_groups_v
982 WHERE APPLICATION_ID = 431
983 AND attr_group_id = l_attr_group_id
984 AND ATTR_GROUP_TYPE = l_attr_group_type
985 AND VARIANT = 'Y';
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988 l_err_msg_name := 'EGO_ATTR_GROUP_ID_PK_NOT_FOUND';
989
990 l_token_table(1).TOKEN_NAME := 'APP_ID';
991 l_token_table(1).TOKEN_VALUE := 431;
992
993 l_token_table(2).TOKEN_NAME := 'AG_TYPE"';
994 l_token_table(2).TOKEN_VALUE := l_attr_group_type;
995
996 l_token_table(3).TOKEN_NAME := 'AG_ID';
997 l_token_table(3).TOKEN_VALUE := l_attr_group_id;
998
999 ERROR_HANDLER.Add_Error_Message(
1000 p_message_name => l_err_msg_name
1001 ,p_application_id => 'EGO'
1002 ,p_token_tbl => l_token_table
1003 ,p_message_type => FND_API.G_RET_STS_ERROR
1004 );
1005 RETURN FND_API.g_RET_STS_ERROR;
1006 END;
1007 ELSE
1008 -- error attribute group details are not provided.
1009 l_err_msg_name := 'EGO_VAR_ATTR_GROUP_DETAILS_ERR';
1010
1011 ERROR_HANDLER.Add_Error_Message(
1012 p_message_name => l_err_msg_name
1013 ,p_application_id => 'EGO'
1014 ,p_token_tbl => l_token_table
1015 ,p_message_type => FND_API.G_RET_STS_ERROR
1016 );
1017
1018 RETURN FND_API.g_RET_STS_ERROR;
1019 END IF;
1020 END IF;
1021
1022 INSERT INTO EGO_ITM_USR_ATTR_INTRFC
1023 (
1024 DATA_SET_ID ,
1025 ITEM_NUMBER ,
1026 ROW_IDENTIFIER ,
1027 ATTR_GROUP_INT_NAME ,
1028 ATTR_INT_NAME ,
1029 PROCESS_STATUS ,
1030 ATTR_GROUP_TYPE,
1031 ITEM_CATALOG_GROUP_ID,
1032 DATA_LEVEL_ID ,
1033 ATTR_VALUE_STR,
1034 ATTR_VALUE_NUM,
1035 ATTR_VALUE_DATE,
1036 ATTR_DISP_VALUE
1037 )
1038 VALUES
1039 (
1040 p_set_process_id,
1041 p_Item_rec.ITEM_NUMBER,
1042 l_row_identifier,
1043 l_attr_group_name,
1044 l_attr_name,
1045 1,
1046 l_attr_group_type,
1047 p_Item_rec.ITEM_CATALOG_GROUP_ID,
1048 43101 , --since variants are always item level, hard coding this
1049 p_attributes_data_table(j).ATTR_VALUE_STR,
1050 p_attributes_data_table(j).ATTR_VALUE_NUM,
1051 p_attributes_data_table(j).ATTR_VALUE_DATE,
1052 p_attributes_data_table(j).ATTR_DISP_VALUE
1053 );
1054 END LOOP; -- p_attributes_data_table
1055 ELSE
1056 l_err_msg_name := 'EGO_ALL_VAR_ATTR_VAL_ERR';
1057
1058 ERROR_HANDLER.Add_Error_Message(
1059 p_message_name => l_err_msg_name
1060 ,p_application_id => 'EGO'
1061 ,p_token_tbl => l_token_table
1062 ,p_message_type => FND_API.G_RET_STS_ERROR
1063 );
1064 RETURN FND_API.g_RET_STS_ERROR;
1065 END IF; -- attr row and data NOT NULL
1066
1067 RETURN fnd_api.g_RET_STS_SUCCESS;
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 x_return_err := 'SKU_ITEM_PREPROCESS - Exception Othres'||SQLERRM ;
1071 RETURN fnd_api.g_RET_STS_UNEXP_ERROR;
1072
1073 END SKU_ITEM_PREPROCESS;
1074 -- Bug 9092888 - changes
1075
1076
1077 -- -------------------- IOI_Process --------------------
1078
1079 PROCEDURE IOI_Process
1080 (
1081 p_transaction_type IN VARCHAR2
1082 , p_commit IN VARCHAR2
1083 , p_validation_level IN NUMBER DEFAULT fnd_api.g_VALID_LEVEL_FULL
1084 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
1085 , p_revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
1086 , p_Template_Id IN NUMBER
1087 , p_Template_Name IN VARCHAR2
1088 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
1089 , x_return_status OUT NOCOPY VARCHAR2
1090 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
1091 -- Bug 9092888 - changes
1092 , p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
1093 , p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
1094 -- Bug 9092888 - changes
1095 )
1096 IS
1097 -- Do not commit an inserted row in MSII
1098 --
1099 c_MSII_commit_flag CONSTANT VARCHAR2(1) := fnd_api.g_FALSE;
1100
1101 -- Control the IOI run mode (CREATE, UPDATE) through the IOI parameter
1102 --
1103 l_IOI_run_mode NUMBER;
1104
1105 -- No init value; control the API commit through the IOI parameter
1106 --
1107 l_IOI_commit_flag NUMBER;
1108
1109 l_set_process_id NUMBER;
1110 l_process_flag NUMBER;
1111 l_rev_process_flag NUMBER;
1112 l_rev_transaction_id NUMBER;
1113 l_transaction_id NUMBER;
1114 l_return_status VARCHAR2(1);
1115
1116 -- Return error from Insert_MSII_Row and Get_IOI_Errors procedures
1117 --
1118 l_return_err VARCHAR2(2000);
1119
1120 l_inventory_item_id NUMBER;
1121 l_organization_id NUMBER;
1122
1123 l_org_id NUMBER;
1124 l_all_org NUMBER;
1125
1126 -- Created/updated item id
1127 --
1128 l_Item_ID_out NUMBER;
1129 l_Org_ID_out NUMBER;
1130
1131 l_Language_Code VARCHAR2(4);
1132
1133 -- IOI return code
1134 --
1135 l_return_code NUMBER;
1136 l_err_text VARCHAR2(2000);
1137
1138 l_idx BINARY_INTEGER;
1139 l_only_validate NUMBER;
1140 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
1141
1142 -- Bug 9092888 - changes
1143 l_err_bug VARCHAR2(1000);
1144 l_ret_code VARCHAR2(1000);
1145 -- Bug 9092888 - changes
1146 -- Bug 8442016
1147 l_msg_data VARCHAR2(1000);
1148 l_errorcode VARCHAR2(1);
1149 -- Bug 8442016
1150 BEGIN
1151
1152 -- Control the IOI run mode (CREATE, UPDATE) through the IOI parameter
1153 --
1154 IF ( p_transaction_type = 'CREATE' ) then
1155 l_IOI_run_mode := 1;
1156 ELSIF ( p_transaction_type = 'UPDATE' ) then
1157 l_IOI_run_mode := 2;
1158
1159 /* Need to get Item ID from MTL_SYSTEM_ITEMS_INTERFACE since ITEM_NUMBER
1160 could be passed with item record to Update_Item
1161 --
1162 -- Use passed record item id to retrieve updated item
1163 --
1164 l_Item_ID_out := p_Item_rec.INVENTORY_ITEM_ID;
1165 l_Org_ID_out := p_Item_rec.ORGANIZATION_ID;
1166 */
1167
1168 ELSE
1169 l_IOI_run_mode := -1;
1170 END IF;
1171
1172 -- Control the API commit through the IOI parameter
1173 --
1174 IF ( fnd_api.to_Boolean (p_commit) ) THEN
1175 l_IOI_commit_flag := 1;
1176 ELSE
1177 l_IOI_commit_flag := 2;
1178 END IF;
1179
1180 x_return_status := fnd_api.g_RET_STS_SUCCESS;
1181
1182 ----------------------------------------------------------------------------
1183 -- Insert a row into MSI INTERFACE table converting missing values to nulls
1184 ----------------------------------------------------------------------------
1185
1186 Insert_MSII_Row
1187 (
1188 p_commit => c_MSII_commit_flag
1189 , p_transaction_type => p_transaction_type
1190 , p_Item_rec => p_Item_rec
1191 , p_revision_rec => p_revision_rec
1192 , p_Template_Id => p_Template_Id
1193 , p_Template_Name => p_Template_Name
1194 , x_set_process_id => l_set_process_id
1195 , x_return_status => l_return_status
1196 , x_return_err => l_return_err
1197 );
1198
1199 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1200 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1201 x_Error_tbl(1).UNIQUE_ID := 1;
1202 x_Error_tbl(1).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1203 x_Error_tbl(1).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1204 RETURN;
1205 END IF;
1206
1207 -- Bug 9092888 - changes
1208 IF(p_transaction_type = 'CREATE' AND INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API = 'API' AND
1209 p_Item_rec.STYLE_ITEM_FLAG = 'N' AND p_Item_rec.STYLE_ITEM_ID IS NOT NULL)
1210 THEN
1211 l_return_status := SKU_ITEM_PREPROCESS
1212 (
1213 p_attributes_row_table => p_attributes_row_table -- IN EGO_USER_ATTR_ROW_TABLE DEFAULT NULL
1214 ,p_attributes_data_table => p_attributes_data_table -- IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
1215 ,p_set_process_id => l_set_process_id
1216 ,p_Item_rec => p_Item_rec
1217 , x_return_err => l_return_err
1218 );
1219
1220 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1221 x_return_status := l_return_status;
1222 x_Error_tbl(1).UNIQUE_ID := 1;
1223 x_Error_tbl(1).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1224 x_Error_tbl(1).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1225 RETURN;
1226 END IF;
1227 END IF;
1228 -- Bug 9092888 - changes
1229
1230 -----------------------------------------------------
1231 -- Run Item OI process.
1232 -- Then call oi delete; need to get
1233 -- process_flag
1234 -- , transaction_id
1235 -- , inventory_item_id
1236 -- , organization_id
1237 -- before deleting processed records.
1238 --
1239 -- ToDo: Get the above from the Interface Process routine.
1240 -----------------------------------------------------
1241
1242 /*
1243 select organization_id
1244 from MTL_PARAMETERS
1245 where organization_code = p_Item_rec.ORGANIZATION_code;
1246 */
1247
1248 IF ( p_Item_rec.ORGANIZATION_ID = g_MISS_NUM
1249 OR p_Item_rec.ORGANIZATION_ID IS NULL )
1250 THEN
1251 l_org_id := NULL;
1252 l_all_org := 1;
1253 ELSE
1254 -- Bug 12635842 : Start
1255 -- If we are creating a SKU from API, then we need to process for all orgs though the user provides the specifi organization.
1256 -- Becases the style item could be assicgned to multiple orgs, so while creating SKU we will create the SKU in all those orgs.
1257 IF(INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API = 'API' AND p_Item_rec.STYLE_ITEM_FLAG = 'N'
1258 AND p_Item_rec.STYLE_ITEM_ID IS NOT NULL AND p_transaction_type = 'CREATE')
1259 THEN
1260 l_org_id := NULL;
1261 l_all_org := 1;
1262 ELSE
1263 -- Bug 12635842 : End
1264 l_org_id := p_Item_rec.ORGANIZATION_ID;
1265 l_all_org := 2; -- process only org_id
1266 END IF; -- Bug 12635842
1267 END IF;
1268
1269 IF p_Item_rec.PROCESS_ITEM_RECORD NOT IN (1,2) THEN
1270 l_only_validate := 1;
1271 ELSE
1272 l_only_validate := p_Item_rec.PROCESS_ITEM_RECORD;
1273 END IF;
1274
1275 IF l_inv_debug_level IN(101, 102) THEN
1276 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: calling INVPOPIF.inopinp_open_interface_process');
1277 END IF;
1278 l_return_code :=
1279 INVPOPIF.inopinp_open_interface_process
1280 (
1281 org_id => l_org_id
1282 , all_org => l_all_org
1283 , val_item_flag => 1 -- validate item
1284 , pro_item_flag => l_only_validate -- process validated items
1285 , del_rec_flag => 2 -- do not delete processed records
1286 , prog_appid => fnd_global.prog_appl_id
1287 , prog_id => fnd_global.conc_program_id
1288 , request_id => fnd_global.conc_request_id
1289 , user_id => fnd_global.user_id
1290 , login_id => fnd_global.login_id
1291 , err_text => l_err_text
1292 , xset_id => l_set_process_id -- only run for the current item record
1293 , commit_flag => l_IOI_commit_flag
1294 , run_mode => l_IOI_run_mode
1295 );
1296
1297 IF l_inv_debug_level IN(101, 102) THEN
1298 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: done INVPOPIF.inopinp_open_interface_process: l_return_code = ' || l_return_code);
1299 END IF;
1300
1301 --------------------------------------------------------------------------
1302 -- Get IOI transaction_id and process_flag for the current set_process_id
1303 --------------------------------------------------------------------------
1304
1305 -- Bug 12635842 : Start
1306 -- Adding the below IF statement as when creating a SKU, the sku can be created in child orgs also if the style is addigned
1307 -- to any child orgs. So we ned to check for master org creation .
1308 -- IF creating a SKU using API, check if the SKU creation for master org successeded or not
1309 IF(INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API = 'API' AND p_Item_rec.STYLE_ITEM_FLAG = 'N'
1310 AND p_Item_rec.STYLE_ITEM_ID IS NOT NULL AND p_transaction_type = 'CREATE')
1311 THEN
1312 SELECT
1313 process_flag
1314 , transaction_id
1315 , inventory_item_id
1316 , MSII.organization_id
1317 INTO
1318 l_process_flag
1319 , l_transaction_id
1320 , l_inventory_item_id
1321 , l_organization_id
1322 FROM
1323 MTL_SYSTEM_ITEMS_INTERFACE MSII,
1324 MTL_PARAMETERS MP
1325 WHERE set_process_id = l_set_process_id
1326 AND MP.MASTER_ORGANIZATION_ID = MP.ORGANIZATION_ID
1327 AND MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID;
1328
1329 -- IF item got created in master org and failed in one child org, then return code may not be 0.
1330 -- In this case we need to process child enties for other child orgs in which item got created, So making the l_return_code to 0.
1331 IF(l_process_flag = 7 and l_return_code <> 0) THEN
1332 l_return_code := 0;
1333 END IF;
1334
1335 ELSE
1336 -- Bug 12635842 : End
1337 SELECT
1338 process_flag
1339 , transaction_id
1340 , inventory_item_id
1341 , organization_id
1342 INTO
1343 l_process_flag
1344 , l_transaction_id
1345 , l_inventory_item_id
1346 , l_organization_id
1347 FROM
1348 MTL_SYSTEM_ITEMS_INTERFACE
1349 WHERE
1350 set_process_id = l_set_process_id;
1351 END IF; -- Bug 12635842
1352
1353 IF l_inv_debug_level IN(101, 102) THEN
1354 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: l_process_flag = ' || l_process_flag || ' l_return_code = ' || l_return_code || ' l_err_text = ' || l_err_text);
1355 END IF;
1356 -- Bug 13935085 START. Get the Item Reivsion record status also
1357 BEGIN
1358 SELECT
1359 process_flag , transaction_id
1360 INTO
1361 l_rev_process_flag , l_rev_transaction_id
1362 FROM
1363 MTL_ITEM_REVISIONS_INTERFACE
1364 WHERE
1365 set_process_id = l_set_process_id AND inventory_item_id = l_inventory_item_id AND organization_id = l_organization_id;
1366
1367 EXCEPTION WHEN NO_DATA_FOUND THEN
1368 -- NO revision changes
1369 l_rev_process_flag := 7 ;
1370
1371 END;
1372 -- Bug 13935085 END
1373
1374 IF l_inv_debug_level IN(101, 102) THEN
1375 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: l_rev_process_flag = ' || l_rev_process_flag || ' l_return_code = ' || l_return_code || ' l_err_text = ' || l_err_text);
1376 END IF;
1377 -----------------------------------------------------------
1378 -- Populate the API Error_tbl with the IOI errors, if any.
1379 -----------------------------------------------------------
1380
1381 IF ( (l_process_flag = 7) AND (l_return_code = 0) and (l_only_validate =1))
1382 THEN
1383 -- Successfull competion of the IOI run.
1384 -- Get the whole record for the newly created/updated item.
1385 --
1386 l_Item_ID_out := l_inventory_item_id;
1387 l_Org_ID_out := l_organization_id;
1388
1389 -- Bug 8442016
1390 IF ( INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API = 'API' ) THEN
1391
1392 IF ( p_Template_Id IS NOT NULL AND (p_transaction_type = 'CREATE' OR p_transaction_type = 'UPDATE') ) -- For Template UDAs processing
1393 THEN
1394 Apply_Templates_For_UDAs
1395 ( p_batch_id => l_set_process_id
1396 ,p_template_id => p_Template_Id
1397 ,p_user_id => fnd_global.user_id
1398 ,p_login_id => fnd_global.login_id
1399 ,p_prog_appid => fnd_global.prog_appl_id
1400 ,p_prog_id => fnd_global.conc_program_id
1401 ,p_request_id => fnd_global.conc_request_id
1402 ,x_return_status => l_return_status
1403 ,ERRBUF => l_msg_data
1404 ,RETCODE => l_errorcode
1405 );
1406
1407 /* Bug 13414358 : Validating the UDAs after applying templates for UDAs, so that transaction type for UDAs
1408 inserted by template code will be set to correct value CREATE/UPDATE from SYNC. */
1409 /*Bug 14151154 passing p_commit flag in call to procedure EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data*/
1410 EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data(
1411 ERRBUF => l_err_bug
1412 ,RETCODE => l_ret_code
1413 ,p_data_set_id => l_set_process_id
1414 ,p_validate_only => FND_API.G_TRUE
1415 ,p_is_id_validations_reqd => FND_API.G_FALSE
1416 ,p_commit => p_commit
1417 );
1418 END IF;
1419
1420 -- Bug 12635842 : Start
1421 -- If we are creating a SKU, then we need to default child entities (like supplier, supplier site and supplier site org etc..) along with their UDAS.
1422 IF(p_Item_rec.STYLE_ITEM_FLAG = 'N' AND p_Item_rec.STYLE_ITEM_ID IS NOT NULL AND p_transaction_type = 'CREATE') THEN
1423
1424 EGO_ITEM_OPEN_INTERFACE_PVT.process_item_entities(ERRBUF => l_err_bug
1425 ,RETCODE => l_ret_code
1426 ,p_del_rec_flag => 1
1427 ,p_xset_id => l_set_process_id
1428 ,p_request_id => fnd_global.conc_request_id
1429 ,p_call_uda_process => FALSE );
1430 END IF;
1431 -- Bug 12635842 : End
1432
1433 -- Bug 9959169 : Start
1434 -- Below api is called to populate the default udas into ego interface table.
1435 EGO_IMPORT_UTIL_PVT.Do_AGLevel_UDA_Defaulting( p_batch_id => l_set_process_id,
1436 x_return_status => l_return_status,
1437 x_err_msg => l_return_err ,
1438 p_msii_miri_process_flag => 7
1439 );
1440 /*
1441 -- commenting the below if clause so that the Process_Item_User_Attrs_Data api is called always,
1442 -- So that the processing of default udas is done always.
1443 -- if there are no records to be processed the api will return in the startin only,
1444 -- so it won't have any performance impact in calling this always.
1445
1446 IF ( (p_transaction_type = 'CREATE' AND p_Item_rec.STYLE_ITEM_FLAG = 'N' AND p_Item_rec.STYLE_ITEM_ID IS NOT NULL) OR -- For SKU UDAS processing
1447 (p_Template_Id IS NOT NULL AND (p_transaction_type = 'CREATE' OR p_transaction_type = 'UPDATE') AND l_errorcode <> '2' ) -- For Template UDAs processing
1448 )
1449 THEN
1450 */ -- Bug 9959169
1451 /*Bug 14151154 passing p_commit flag in call to procedure EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data*/
1452 EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data(
1453 ERRBUF => l_err_bug
1454 ,RETCODE => l_ret_code
1455 ,p_data_set_id => l_set_process_id
1456 ,p_commit => p_commit
1457 );
1458 -- END IF; -- Bug 9959169 : End
1459 END IF; -- end of if INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API = 'API'
1460 -- Bug 8442016
1461
1462 -- IF ( p_transaction_type <> 'UPDATE' ) then
1463 -- END IF;
1464
1465 IF l_inv_debug_level IN(101, 102) THEN
1466 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: calling INV_ITEM_GRP.Get_Item');
1467 END IF;
1468
1469 INV_ITEM_GRP.Get_Item
1470 (
1471 p_Item_ID => l_Item_ID_out
1472 , p_Org_ID => l_Org_ID_out
1473 , x_Item_rec => x_Item_rec
1474 , x_return_status => l_return_status
1475 , x_return_err => l_return_err
1476 );
1477
1478 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1479 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1480 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1481 x_Error_tbl(l_idx).UNIQUE_ID := 999;
1482 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1483 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1484 END IF;
1485
1486 -- Do not insert message, if IOI is success.
1487 --
1488 /*
1489 l_idx := 1;
1490 x_Error_tbl(l_idx).UNIQUE_ID := 0;
1491 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1492 x_Error_tbl(l_idx).MESSAGE_TEXT :=
1493 'Create_Item success. IOI tnx_id=' || to_char(l_transaction_id) ||
1494 ' inventory_item_id=' || to_char(l_inventory_item_id);
1495 */
1496 ELSIF ( (l_process_flag = 4) AND (l_return_code = 0) and (l_only_validate =2)) THEN
1497 x_return_status := fnd_api.g_RET_STS_SUCCESS;
1498 ELSE
1499 --
1500 -- An error happened during the IOI run.
1501 --
1502 x_return_status := fnd_api.g_RET_STS_ERROR;
1503
1504 -----------------------------------------------------------------
1505 -- Populate the Item API Error_tbl with the IOI errors, if any.
1506 -----------------------------------------------------------------
1507
1508 l_idx := NVL( x_Error_tbl.COUNT, 0);
1509
1510 IF l_inv_debug_level IN(101, 102) THEN
1511 INVPUTLI.info('INV_ITEM_GRP.IOI_Process: calling INV_ITEM_GRP.Get_IOI_Errors');
1512 END IF;
1513
1514 INV_ITEM_GRP.Get_IOI_Errors
1515 ( p_transaction_id => l_transaction_id
1516 , p_inventory_item_id => l_inventory_item_id
1517 , x_Error_tbl => x_Error_tbl
1518 , x_return_status => l_return_status
1519 , x_return_err => l_return_err
1520 );
1521
1522 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1523 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1524 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1525 x_Error_tbl(l_idx).UNIQUE_ID := 999;
1526 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_INTERFACE_ERRORS';
1527 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1528 ELSE
1529 --
1530 -- If Interface Process return_code <> 0 and there are no additional
1531 -- messages in x_Error_tbl, then insert an Unexpexted error message.
1532 --
1533 IF ( l_return_code <> 0
1534 AND l_idx = NVL( x_Error_tbl.COUNT, 0) ) THEN
1535 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1536 x_Error_tbl(l_idx).UNIQUE_ID := 1;
1537 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1538 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('Error during IOI run.'
1539 || ' Process_flag=' || to_char(l_process_flag)
1540 || ' Return_code=' || to_char(l_return_code)
1541 || ' Unexpexted error: ' || l_err_text,1,239);
1542 END IF;
1543 END IF;
1544
1545 END IF; -- (l_process_flag = 7) AND (l_return_code = 0)
1546 /*
1547 Bug 13935085 Item Revision Phase change through EGO_ITEM_PUB.Process_Item does not show
1548 error message ends with sucess. in case of Change orders, invalid phase id,
1549 invalid revision id.
1550 Check the process flag in MTL_ITEM_REVISIONS_INTERFACE to see if any of
1551 revision validations are failed and populate the error table START
1552 */
1553 IF(l_rev_process_flag = 3) THEN
1554
1555 INV_ITEM_GRP.Get_IOI_Errors
1556 ( p_transaction_id => l_rev_transaction_id
1557 , p_inventory_item_id => l_inventory_item_id
1558 , x_Error_tbl => x_Error_tbl
1559 , x_return_status => l_return_status
1560 , x_return_err => l_return_err
1561 );
1562
1563 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1564 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1565 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1566 x_Error_tbl(l_idx).UNIQUE_ID := 999;
1567 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_INTERFACE_ERRORS';
1568 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1569 ELSE
1570
1571 --
1572 -- If Interface Process return_code <> 0 and there are no additional
1573 -- messages in x_Error_tbl, then insert an Unexpexted error message.
1574 --
1575 IF ( l_return_code <> 0
1576 AND l_idx = NVL( x_Error_tbl.COUNT, 0) ) THEN
1577 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1578 x_Error_tbl(l_idx).UNIQUE_ID := 1;
1579 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1580 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('Error during IOI run.'
1581 || ' Process_flag=' || to_char(l_process_flag)
1582 || ' Return_code=' || to_char(l_return_code)
1583 || ' Unexpexted error: ' || l_err_text,1,239);
1584 END IF;
1585 END IF;
1586 END IF;
1587
1588 -- Bug 13935085 END
1589 ----------------------------------------------------------
1590 -- Delete IOI process set rows from the interface tables
1591 ----------------------------------------------------------
1592 l_return_code :=
1593 INVPOPIF.indelitm_delete_item_oi
1594 ( err_text => l_err_text
1595 , com_flag => l_IOI_commit_flag
1596 , xset_id => l_set_process_id
1597 );
1598
1599 IF ( l_return_code <> 0 ) THEN
1600 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1601 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1602 x_Error_tbl(l_idx).UNIQUE_ID := 999;
1603 x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1604 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_err_text,1,239);
1605 END IF;
1606 ----------------------------------------------------------
1607 -- Control the API commit through the IOI
1608 -- inopinp_open_interface_process parameter
1609 ----------------------------------------------------------
1610 /*
1611 IF ( fnd_api.to_Boolean (p_commit) ) THEN
1612 COMMIT WORK;
1613 END IF;
1614 */
1615
1616 EXCEPTION
1617
1618 WHEN others THEN
1619 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1620 l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1621 x_Error_tbl(l_idx).UNIQUE_ID := 999;
1622 x_Error_tbl(l_idx).TABLE_NAME := '';
1623 x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('INV_ITEM_GRP.IOI_Process: Unexpexted error: ' || SQLERRM,1,239);
1624
1625 END IOI_Process;
1626
1627 -- -------------------- Get_IOI_Errors -------------------
1628
1629 PROCEDURE Get_IOI_Errors
1630 (
1631 p_transaction_id IN NUMBER
1632 , p_inventory_item_id IN NUMBER
1633 , x_Error_tbl IN OUT NOCOPY INV_ITEM_GRP.Error_tbl_type
1634 , x_return_status OUT NOCOPY VARCHAR2
1635 , x_return_err OUT NOCOPY VARCHAR2
1636 )
1637 IS
1638 l_Error_rec INV_ITEM_GRP.Error_rec_type;
1639 l_init_errno NUMBER;
1640 l_idx BINARY_INTEGER;
1641
1642 /*
1643 CURSOR c_mtl_item_cat_interface
1644 IS
1645 SELECT transaction_id
1646 FROM MTL_ITEM_CATEGORIES_INTERFACE
1647 WHERE organization_id = X_organization_id
1648 AND inventory_item_id = X_inventory_item_id;
1649
1650 CURSOR c_mtl_item_rev_interface
1651 IS
1652 SELECT transaction_id
1653 FROM mtl_item_revisions_interface
1654 WHERE organization_id = X_organization_id
1655 AND inventory_item_id = X_inventory_item_id
1656 AND revision = X_revision;
1657 */
1658
1659 CURSOR Interface_Errors_csr ( p_tnx_id IN NUMBER )
1660 RETURN Error_rec_type
1661 IS
1662 SELECT
1663 TRANSACTION_ID
1664 , UNIQUE_ID
1665 , MESSAGE_NAME MESSAGE_NAME
1666 , ERROR_MESSAGE MESSAGE_TEXT
1667 -- , error_message || column_name
1668 , TABLE_NAME
1669 , substr(COLUMN_NAME,1,30) COLUMN_NAME
1670 , ORGANIZATION_ID
1671 FROM MTL_INTERFACE_ERRORS
1672 WHERE TRANSACTION_ID = p_tnx_id
1673 ORDER BY TRANSACTION_ID, UNIQUE_ID;
1674
1675 BEGIN
1676
1677 /*
1678 OPEN c_mtl_item_cat_interface;
1679 FETCH c_mtl_item_cat_interface INTO X_transaction_id_2;
1680 IF c_mtl_item_cat_interface%NOTFOUND THEN
1681 X_transaction_id_2 := -999; -- assign some number to tnx
1682 END IF;
1683 CLOSE c_mtl_item_cat_interface;
1684
1685 OPEN c_mtl_item_rev_interface;
1686 FETCH c_mtl_item_rev_interface INTO X_transaction_id_3;
1687 IF c_mtl_item_rev_interface%NOTFOUND THEN
1688 X_transaction_id_3 := -99;
1689 END IF;
1690 CLOSE c_mtl_item_rev_interface;
1691 */
1692
1693 /*
1694 OPEN Interface_Errors_csr( p_tnx_id => p_transaction_id );
1695 l_idx := l_init_errno;
1696 LOOP -- Loop through error records
1697 FETCH Interface_Errors_csr INTO l_Error_rec;
1698 EXIT WHEN ( Interface_Errors_csr%NOTFOUND );
1699 l_idx := l_idx + 1;
1700 x_Error_tbl(l_idx) := l_Error_rec;
1701 END LOOP;
1702 */
1703
1704 l_init_errno := NVL( x_Error_tbl.COUNT, 0);
1705 l_idx := l_init_errno;
1706 FOR l_Error_rec IN Interface_Errors_csr( p_tnx_id => p_transaction_id )
1707 LOOP
1708 l_idx := l_idx + 1;
1709 x_Error_tbl(l_idx) := l_Error_rec;
1710 END LOOP;
1711
1712 IF ( l_idx = l_init_errno ) THEN
1713 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1714 x_return_err := 'IOI error(s) occurred, but no records found in MTL_INTERFACE_ERRORS table';
1715 END IF;
1716
1717 EXCEPTION
1718
1719 WHEN others THEN
1720 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1721 x_return_err := 'INV_ITEM_GRP.Get_IOI_Errors: Unexpexted error: ' || SQLERRM;
1722
1723 END Get_IOI_Errors;
1724
1725 -- ------------------- Insert_MSII_Row -------------------
1726
1727 PROCEDURE Insert_MSII_Row
1728 (
1729 p_commit IN VARCHAR2
1730 , p_transaction_type IN VARCHAR2
1731 , p_Item_rec IN INV_ITEM_GRP.Item_rec_type
1732 , p_revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
1733 , p_Template_Id IN NUMBER
1734 , p_Template_Name IN VARCHAR2
1735 , x_set_process_id OUT NOCOPY NUMBER
1736 , x_return_status OUT NOCOPY VARCHAR2
1737 , x_return_err OUT NOCOPY VARCHAR2
1738 )
1739 IS
1740 -- Initial row status (Awaiting Validation)
1741 l_process_flag NUMBER := 1;
1742
1743 -- Unique process set id for one record
1744 l_set_process_id NUMBER;
1745
1746 -- x_def_master_org_id /* organization id */
1747
1748 l_Contract_Item_Type_Code VARCHAR2(30);
1749 l_item_rowid ROWID;
1750 l_Item_rec INV_ITEM_GRP.Item_Rec_Type := p_Item_rec;
1751
1752 BEGIN
1753
1754 SAVEPOINT Insert_MSII_Row;
1755 x_return_status := fnd_api.g_RET_STS_SUCCESS;
1756
1757 IF ( p_transaction_type = 'UPDATE' ) THEN
1758 -- Values used in IOI to indicate update to NULL.
1759 g_Upd_Null_CHAR := '!';
1760 g_Upd_Null_NUM := -999999;
1761 g_Upd_Null_DATE := NULL;
1762 ELSE
1763 g_Upd_Null_CHAR := NULL;
1764 g_Upd_Null_NUM := NULL;
1765 g_Upd_Null_DATE := NULL;
1766 END IF;
1767
1768 ----------------------------------------------------------------------------
1769 -- Derive Contract Item Type attribute value based on Service Item, Warranty
1770 -- and Usage flag attributes (11.5.7).
1771 ----------------------------------------------------------------------------
1772
1773 IF ( p_Item_rec.CONTRACT_ITEM_TYPE_CODE = g_MISS_CHAR ) THEN
1774 IF ( p_Item_rec.VENDOR_WARRANTY_FLAG = 'Y' ) THEN
1775 l_Contract_Item_Type_Code := 'WARRANTY';
1776 ELSIF ( p_Item_rec.SERVICE_ITEM_FLAG = 'Y' ) THEN
1777 l_Contract_Item_Type_Code := 'SERVICE';
1778 ELSIF ( p_Item_rec.USAGE_ITEM_FLAG = 'Y' ) THEN
1779 l_Contract_Item_Type_Code := 'USAGE';
1780 ELSIF ( p_Item_rec.SERVICE_ITEM_FLAG = 'N'
1781 OR p_Item_rec.VENDOR_WARRANTY_FLAG = 'N' ) THEN
1782 l_Contract_Item_Type_Code := g_Null_CHAR;
1783 ELSE
1784 l_Contract_Item_Type_Code := p_Item_rec.CONTRACT_ITEM_TYPE_CODE;
1785 END IF;
1786 ELSE
1787 l_Contract_Item_Type_Code := p_Item_rec.CONTRACT_ITEM_TYPE_CODE;
1788 END IF;
1789
1790 -- Get unique process set id for one record processing.
1791
1792 SELECT mtl_system_items_intf_sets_s.NEXTVAL
1793 INTO l_set_process_id
1794 FROM dual;
1795
1796 x_set_process_id := l_set_process_id;
1797
1798 --5565453 : Appsperf issues reducing the shared memory.
1799 IF(p_Item_rec.ORGANIZATION_ID IS NULL ) THEN
1800 l_Item_rec.ORGANIZATION_ID := g_Upd_Null_NUM ;
1801 ELSIF p_Item_rec.ORGANIZATION_ID = g_MISS_NUM THEN
1802 l_Item_rec.ORGANIZATION_ID := null;
1803 END IF;
1804
1805 IF(p_Item_rec.ORGANIZATION_CODE IS NULL) THEN
1806 l_Item_rec.ORGANIZATION_CODE := g_Upd_Null_CHAR;
1807 ELSIF p_Item_rec.ORGANIZATION_CODE = g_MISS_CHAR THEN
1808 l_Item_rec.ORGANIZATION_CODE := null;
1809 END IF;
1810
1811 IF(p_Item_rec.INVENTORY_ITEM_ID IS NULL ) THEN
1812 l_Item_rec.INVENTORY_ITEM_ID := g_Upd_Null_NUM ;
1813 ELSIF p_Item_rec.INVENTORY_ITEM_ID = g_MISS_NUM THEN
1814 l_Item_rec.INVENTORY_ITEM_ID := null;
1815 END IF;
1816
1817 IF(p_Item_rec.ITEM_NUMBER IS NULL) THEN
1818 l_Item_rec.ITEM_NUMBER := g_Upd_Null_CHAR;
1819 ELSIF p_Item_rec.ITEM_NUMBER = g_MISS_CHAR THEN
1820 l_Item_rec.ITEM_NUMBER := null;
1821 END IF;
1822
1823 IF(p_Item_rec.SEGMENT1 IS NULL) THEN
1824 l_Item_rec.SEGMENT1 := g_Upd_Null_CHAR;
1825 ELSIF p_Item_rec.SEGMENT1 = g_MISS_CHAR THEN
1826 l_Item_rec.SEGMENT1 := null;
1827 END IF;
1828
1829 IF(p_Item_rec.SEGMENT2 IS NULL) THEN
1830 l_Item_rec.SEGMENT2 := g_Upd_Null_CHAR;
1831 ELSIF p_Item_rec.SEGMENT2 = g_MISS_CHAR THEN
1832 l_Item_rec.SEGMENT2 := null;
1833 END IF;
1834
1835 IF(p_Item_rec.SEGMENT3 IS NULL) THEN
1836 l_Item_rec.SEGMENT3 := g_Upd_Null_CHAR;
1837 ELSIF p_Item_rec.SEGMENT3 = g_MISS_CHAR THEN
1838 l_Item_rec.SEGMENT3 := null;
1839 END IF;
1840
1841 IF(p_Item_rec.SEGMENT4 IS NULL) THEN
1842 l_Item_rec.SEGMENT4 := g_Upd_Null_CHAR;
1843 ELSIF p_Item_rec.SEGMENT4 = g_MISS_CHAR THEN
1844 l_Item_rec.SEGMENT4 := null;
1845 END IF;
1846
1847 IF(p_Item_rec.SEGMENT5 IS NULL) THEN
1848 l_Item_rec.SEGMENT5 := g_Upd_Null_CHAR;
1849 ELSIF p_Item_rec.SEGMENT5 = g_MISS_CHAR THEN
1850 l_Item_rec.SEGMENT5 := null;
1851 END IF;
1852
1853 IF(p_Item_rec.SEGMENT6 IS NULL) THEN
1854 l_Item_rec.SEGMENT6 := g_Upd_Null_CHAR;
1855 ELSIF p_Item_rec.SEGMENT6 = g_MISS_CHAR THEN
1856 l_Item_rec.SEGMENT6 := null;
1857 END IF;
1858
1859 IF(p_Item_rec.SEGMENT7 IS NULL) THEN
1860 l_Item_rec.SEGMENT7 := g_Upd_Null_CHAR;
1861 ELSIF p_Item_rec.SEGMENT7 = g_MISS_CHAR THEN
1862 l_Item_rec.SEGMENT7 := null;
1863 END IF;
1864
1865 IF(p_Item_rec.SEGMENT8 IS NULL) THEN
1866 l_Item_rec.SEGMENT8 := g_Upd_Null_CHAR;
1867 ELSIF p_Item_rec.SEGMENT8 = g_MISS_CHAR THEN
1868 l_Item_rec.SEGMENT8 := null;
1869 END IF;
1870
1871 IF(p_Item_rec.SEGMENT9 IS NULL) THEN
1872 l_Item_rec.SEGMENT9 := g_Upd_Null_CHAR;
1873 ELSIF p_Item_rec.SEGMENT9 = g_MISS_CHAR THEN
1874 l_Item_rec.SEGMENT9 := null;
1875 END IF;
1876
1877 IF(p_Item_rec.SEGMENT10 IS NULL) THEN
1878 l_Item_rec.SEGMENT10 := g_Upd_Null_CHAR;
1879 ELSIF p_Item_rec.SEGMENT10 = g_MISS_CHAR THEN
1880 l_Item_rec.SEGMENT10 := null;
1881 END IF;
1882
1883 IF(p_Item_rec.SEGMENT11 IS NULL) THEN
1884 l_Item_rec.SEGMENT11 := g_Upd_Null_CHAR;
1885 ELSIF p_Item_rec.SEGMENT11 = g_MISS_CHAR THEN
1886 l_Item_rec.SEGMENT11 := null;
1887 END IF;
1888
1889 IF(p_Item_rec.SEGMENT12 IS NULL) THEN
1890 l_Item_rec.SEGMENT12 := g_Upd_Null_CHAR;
1891 ELSIF p_Item_rec.SEGMENT12 = g_MISS_CHAR THEN
1892 l_Item_rec.SEGMENT12 := null;
1893 END IF;
1894
1895 IF(p_Item_rec.SEGMENT13 IS NULL) THEN
1896 l_Item_rec.SEGMENT13 := g_Upd_Null_CHAR;
1897 ELSIF p_Item_rec.SEGMENT13 = g_MISS_CHAR THEN
1898 l_Item_rec.SEGMENT13 := null;
1899 END IF;
1900
1901 IF(p_Item_rec.SEGMENT14 IS NULL) THEN
1902 l_Item_rec.SEGMENT14 := g_Upd_Null_CHAR;
1903 ELSIF p_Item_rec.SEGMENT14 = g_MISS_CHAR THEN
1904 l_Item_rec.SEGMENT14 := null;
1905 END IF;
1906
1907 IF(p_Item_rec.SEGMENT15 IS NULL) THEN
1908 l_Item_rec.SEGMENT15 := g_Upd_Null_CHAR;
1909 ELSIF p_Item_rec.SEGMENT15 = g_MISS_CHAR THEN
1910 l_Item_rec.SEGMENT15 := null;
1911 END IF;
1912
1913 IF(p_Item_rec.SEGMENT16 IS NULL) THEN
1914 l_Item_rec.SEGMENT16 := g_Upd_Null_CHAR;
1915 ELSIF p_Item_rec.SEGMENT16 = g_MISS_CHAR THEN
1916 l_Item_rec.SEGMENT16 := null;
1917 END IF;
1918
1919 IF(p_Item_rec.SEGMENT17 IS NULL) THEN
1920 l_Item_rec.SEGMENT17 := g_Upd_Null_CHAR;
1921 ELSIF p_Item_rec.SEGMENT17 = g_MISS_CHAR THEN
1922 l_Item_rec.SEGMENT17 := null;
1923 END IF;
1924
1925 IF(p_Item_rec.SEGMENT18 IS NULL) THEN
1926 l_Item_rec.SEGMENT18 := g_Upd_Null_CHAR;
1927 ELSIF p_Item_rec.SEGMENT18 = g_MISS_CHAR THEN
1928 l_Item_rec.SEGMENT18 := null;
1929 END IF;
1930
1931 IF(p_Item_rec.SEGMENT19 IS NULL) THEN
1932 l_Item_rec.SEGMENT19 := g_Upd_Null_CHAR;
1933 ELSIF p_Item_rec.SEGMENT19 = g_MISS_CHAR THEN
1934 l_Item_rec.SEGMENT19 := null;
1935 END IF;
1936
1937 IF(p_Item_rec.SEGMENT20 IS NULL) THEN
1938 l_Item_rec.SEGMENT20 := g_Upd_Null_CHAR;
1939 ELSIF p_Item_rec.SEGMENT20 = g_MISS_CHAR THEN
1940 l_Item_rec.SEGMENT20 := null;
1941 END IF;
1942
1943 IF(p_Item_rec.SUMMARY_FLAG IS NULL) THEN
1944 l_Item_rec.SUMMARY_FLAG := g_Upd_Null_CHAR;
1945 ELSIF p_Item_rec.SUMMARY_FLAG = g_MISS_CHAR THEN
1946 l_Item_rec.SUMMARY_FLAG := null;
1947 END IF;
1948
1949 IF(p_Item_rec.ENABLED_FLAG IS NULL) THEN
1950 l_Item_rec.ENABLED_FLAG := g_Upd_Null_CHAR;
1951 ELSIF p_Item_rec.ENABLED_FLAG = g_MISS_CHAR THEN
1952 l_Item_rec.ENABLED_FLAG := null;
1953 END IF;
1954
1955 IF(p_Item_rec.START_DATE_ACTIVE = g_Null_DATE) THEN
1956 l_Item_rec.START_DATE_ACTIVE := g_Upd_Null_DATE;
1957 ELSIF p_Item_rec.START_DATE_ACTIVE = g_MISS_DATE THEN
1958 l_Item_rec.START_DATE_ACTIVE := null;
1959 END IF;
1960
1961 IF(p_Item_rec.END_DATE_ACTIVE = g_Null_DATE) THEN
1962 l_Item_rec.END_DATE_ACTIVE := g_Upd_Null_DATE;
1963 ELSIF p_Item_rec.END_DATE_ACTIVE = g_MISS_DATE THEN
1964 l_Item_rec.END_DATE_ACTIVE := null;
1965 END IF;
1966
1967 IF(p_Item_rec.DESCRIPTION IS NULL) THEN
1968 l_Item_rec.DESCRIPTION := g_Upd_Null_CHAR;
1969 ELSIF p_Item_rec.DESCRIPTION = g_MISS_CHAR THEN
1970 l_Item_rec.DESCRIPTION := null;
1971 END IF;
1972
1973 IF(p_Item_rec.LONG_DESCRIPTION IS NULL) THEN
1974 l_Item_rec.LONG_DESCRIPTION := g_Upd_Null_CHAR;
1975 ELSIF p_Item_rec.LONG_DESCRIPTION = g_MISS_CHAR THEN
1976 l_Item_rec.LONG_DESCRIPTION := null;
1977 END IF;
1978
1979 IF(p_Item_rec.PRIMARY_UOM_CODE IS NULL) THEN
1980 l_Item_rec.PRIMARY_UOM_CODE := g_Upd_Null_CHAR;
1981 ELSIF p_Item_rec.PRIMARY_UOM_CODE = g_MISS_CHAR THEN
1982 l_Item_rec.PRIMARY_UOM_CODE := null;
1983 END IF;
1984
1985 IF(p_Item_rec.PRIMARY_UNIT_OF_MEASURE IS NULL) THEN
1986 l_Item_rec.PRIMARY_UNIT_OF_MEASURE := g_Upd_Null_CHAR;
1987 ELSIF p_Item_rec.PRIMARY_UNIT_OF_MEASURE = g_MISS_CHAR THEN
1988 l_Item_rec.PRIMARY_UNIT_OF_MEASURE := null;
1989 END IF;
1990
1991 IF(p_Item_rec.ITEM_TYPE IS NULL) THEN
1992 l_Item_rec.ITEM_TYPE := g_Upd_Null_CHAR;
1993 ELSIF p_Item_rec.ITEM_TYPE = g_MISS_CHAR THEN
1994 l_Item_rec.ITEM_TYPE := null;
1995 END IF;
1996
1997 IF(p_Item_rec.INVENTORY_ITEM_STATUS_CODE IS NULL) THEN
1998 l_Item_rec.INVENTORY_ITEM_STATUS_CODE := g_Upd_Null_CHAR;
1999 ELSIF p_Item_rec.INVENTORY_ITEM_STATUS_CODE = g_MISS_CHAR THEN
2000 l_Item_rec.INVENTORY_ITEM_STATUS_CODE := null;
2001 END IF;
2002
2003 IF(p_Item_rec.ALLOWED_UNITS_LOOKUP_CODE IS NULL ) THEN
2004 l_Item_rec.ALLOWED_UNITS_LOOKUP_CODE := g_Upd_Null_NUM ;
2005 ELSIF p_Item_rec.ALLOWED_UNITS_LOOKUP_CODE = g_MISS_NUM THEN
2006 l_Item_rec.ALLOWED_UNITS_LOOKUP_CODE := null;
2007 END IF;
2008
2009 IF(p_Item_rec.ITEM_CATALOG_GROUP_ID IS NULL ) THEN
2010 l_Item_rec.ITEM_CATALOG_GROUP_ID := g_Upd_Null_NUM ;
2011 ELSIF p_Item_rec.ITEM_CATALOG_GROUP_ID = g_MISS_NUM THEN
2012 l_Item_rec.ITEM_CATALOG_GROUP_ID := null;
2013 END IF;
2014
2015 IF(p_Item_rec.CATALOG_STATUS_FLAG IS NULL) THEN
2016 l_Item_rec.CATALOG_STATUS_FLAG := g_Upd_Null_CHAR;
2017 ELSIF p_Item_rec.CATALOG_STATUS_FLAG = g_MISS_CHAR THEN
2018 l_Item_rec.CATALOG_STATUS_FLAG := null;
2019 END IF;
2020
2021 IF(p_Item_rec.INVENTORY_ITEM_FLAG IS NULL) THEN
2022 l_Item_rec.INVENTORY_ITEM_FLAG := g_Upd_Null_CHAR;
2023 ELSIF p_Item_rec.INVENTORY_ITEM_FLAG = g_MISS_CHAR THEN
2024 l_Item_rec.INVENTORY_ITEM_FLAG := null;
2025 END IF;
2026
2027 IF(p_Item_rec.STOCK_ENABLED_FLAG IS NULL) THEN
2028 l_Item_rec.STOCK_ENABLED_FLAG := g_Upd_Null_CHAR;
2029 ELSIF p_Item_rec.STOCK_ENABLED_FLAG = g_MISS_CHAR THEN
2030 l_Item_rec.STOCK_ENABLED_FLAG := null;
2031 END IF;
2032
2033 IF(p_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG IS NULL) THEN
2034 l_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG := g_Upd_Null_CHAR;
2035 ELSIF p_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG = g_MISS_CHAR THEN
2036 l_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG := null;
2037 END IF;
2038
2039 IF(p_Item_rec.CHECK_SHORTAGES_FLAG IS NULL) THEN
2040 l_Item_rec.CHECK_SHORTAGES_FLAG := g_Upd_Null_CHAR;
2041 ELSIF p_Item_rec.CHECK_SHORTAGES_FLAG = g_MISS_CHAR THEN
2042 l_Item_rec.CHECK_SHORTAGES_FLAG := null;
2043 END IF;
2044
2045 IF(p_Item_rec.REVISION_QTY_CONTROL_CODE IS NULL ) THEN
2046 l_Item_rec.REVISION_QTY_CONTROL_CODE := g_Upd_Null_NUM ;
2047 ELSIF p_Item_rec.REVISION_QTY_CONTROL_CODE = g_MISS_NUM THEN
2048 l_Item_rec.REVISION_QTY_CONTROL_CODE := null;
2049 END IF;
2050
2051 IF(p_Item_rec.RESERVABLE_TYPE IS NULL ) THEN
2052 l_Item_rec.RESERVABLE_TYPE := g_Upd_Null_NUM ;
2053 ELSIF p_Item_rec.RESERVABLE_TYPE = g_MISS_NUM THEN
2054 l_Item_rec.RESERVABLE_TYPE := null;
2055 END IF;
2056
2057 IF(p_Item_rec.SHELF_LIFE_CODE IS NULL ) THEN
2058 l_Item_rec.SHELF_LIFE_CODE := g_Upd_Null_NUM ;
2059 ELSIF p_Item_rec.SHELF_LIFE_CODE = g_MISS_NUM THEN
2060 l_Item_rec.SHELF_LIFE_CODE := null;
2061 END IF;
2062
2063 IF(p_Item_rec.SHELF_LIFE_DAYS IS NULL ) THEN
2064 l_Item_rec.SHELF_LIFE_DAYS := g_Upd_Null_NUM ;
2065 ELSIF p_Item_rec.SHELF_LIFE_DAYS = g_MISS_NUM THEN
2066 l_Item_rec.SHELF_LIFE_DAYS := null;
2067 END IF;
2068
2069 IF(p_Item_rec.CYCLE_COUNT_ENABLED_FLAG IS NULL) THEN
2070 l_Item_rec.CYCLE_COUNT_ENABLED_FLAG := g_Upd_Null_CHAR;
2071 ELSIF p_Item_rec.CYCLE_COUNT_ENABLED_FLAG = g_MISS_CHAR THEN
2072 l_Item_rec.CYCLE_COUNT_ENABLED_FLAG := null;
2073 END IF;
2074
2075 IF(p_Item_rec.NEGATIVE_MEASUREMENT_ERROR IS NULL ) THEN
2076 l_Item_rec.NEGATIVE_MEASUREMENT_ERROR := g_Upd_Null_NUM ;
2077 ELSIF p_Item_rec.NEGATIVE_MEASUREMENT_ERROR = g_MISS_NUM THEN
2078 l_Item_rec.NEGATIVE_MEASUREMENT_ERROR := null;
2079 END IF;
2080
2081 IF(p_Item_rec.POSITIVE_MEASUREMENT_ERROR IS NULL ) THEN
2082 l_Item_rec.POSITIVE_MEASUREMENT_ERROR := g_Upd_Null_NUM ;
2083 ELSIF p_Item_rec.POSITIVE_MEASUREMENT_ERROR = g_MISS_NUM THEN
2084 l_Item_rec.POSITIVE_MEASUREMENT_ERROR := null;
2085 END IF;
2086
2087 IF(p_Item_rec.LOT_CONTROL_CODE IS NULL ) THEN
2088 l_Item_rec.LOT_CONTROL_CODE := g_Upd_Null_NUM ;
2089 ELSIF p_Item_rec.LOT_CONTROL_CODE = g_MISS_NUM THEN
2090 l_Item_rec.LOT_CONTROL_CODE := null;
2091 END IF;
2092
2093 IF(p_Item_rec.AUTO_LOT_ALPHA_PREFIX IS NULL) THEN
2094 l_Item_rec.AUTO_LOT_ALPHA_PREFIX := g_Upd_Null_CHAR;
2095 ELSIF p_Item_rec.AUTO_LOT_ALPHA_PREFIX = g_MISS_CHAR THEN
2096 l_Item_rec.AUTO_LOT_ALPHA_PREFIX := null;
2097 END IF;
2098
2099 IF(p_Item_rec.START_AUTO_LOT_NUMBER IS NULL) THEN
2100 l_Item_rec.START_AUTO_LOT_NUMBER := g_Upd_Null_CHAR;
2101 ELSIF p_Item_rec.START_AUTO_LOT_NUMBER = g_MISS_CHAR THEN
2102 l_Item_rec.START_AUTO_LOT_NUMBER := null;
2103 END IF;
2104
2105 IF(p_Item_rec.SERIAL_NUMBER_CONTROL_CODE IS NULL ) THEN
2106 l_Item_rec.SERIAL_NUMBER_CONTROL_CODE := g_Upd_Null_NUM ;
2107 ELSIF p_Item_rec.SERIAL_NUMBER_CONTROL_CODE = g_MISS_NUM THEN
2108 l_Item_rec.SERIAL_NUMBER_CONTROL_CODE := null;
2109 END IF;
2110
2111 IF(p_Item_rec.AUTO_SERIAL_ALPHA_PREFIX IS NULL) THEN
2112 l_Item_rec.AUTO_SERIAL_ALPHA_PREFIX := g_Upd_Null_CHAR;
2113 ELSIF p_Item_rec.AUTO_SERIAL_ALPHA_PREFIX = g_MISS_CHAR THEN
2114 l_Item_rec.AUTO_SERIAL_ALPHA_PREFIX := null;
2115 END IF;
2116
2117 IF(p_Item_rec.START_AUTO_SERIAL_NUMBER IS NULL) THEN
2118 l_Item_rec.START_AUTO_SERIAL_NUMBER := g_Upd_Null_CHAR;
2119 ELSIF p_Item_rec.START_AUTO_SERIAL_NUMBER = g_MISS_CHAR THEN
2120 l_Item_rec.START_AUTO_SERIAL_NUMBER := null;
2121 END IF;
2122
2123 IF(p_Item_rec.LOCATION_CONTROL_CODE IS NULL ) THEN
2124 l_Item_rec.LOCATION_CONTROL_CODE := g_Upd_Null_NUM ;
2125 ELSIF p_Item_rec.LOCATION_CONTROL_CODE = g_MISS_NUM THEN
2126 l_Item_rec.LOCATION_CONTROL_CODE := null;
2127 END IF;
2128
2129 IF(p_Item_rec.RESTRICT_SUBINVENTORIES_CODE IS NULL ) THEN
2130 l_Item_rec.RESTRICT_SUBINVENTORIES_CODE := g_Upd_Null_NUM ;
2131 ELSIF p_Item_rec.RESTRICT_SUBINVENTORIES_CODE = g_MISS_NUM THEN
2132 l_Item_rec.RESTRICT_SUBINVENTORIES_CODE := null;
2133 END IF;
2134
2135 IF(p_Item_rec.RESTRICT_LOCATORS_CODE IS NULL ) THEN
2136 l_Item_rec.RESTRICT_LOCATORS_CODE := g_Upd_Null_NUM ;
2137 ELSIF p_Item_rec.RESTRICT_LOCATORS_CODE = g_MISS_NUM THEN
2138 l_Item_rec.RESTRICT_LOCATORS_CODE := null;
2139 END IF;
2140
2141 IF(p_Item_rec.BOM_ENABLED_FLAG IS NULL) THEN
2142 l_Item_rec.BOM_ENABLED_FLAG := g_Upd_Null_CHAR;
2143 ELSIF p_Item_rec.BOM_ENABLED_FLAG = g_MISS_CHAR THEN
2144 l_Item_rec.BOM_ENABLED_FLAG := null;
2145 END IF;
2146
2147 IF(p_Item_rec.BOM_ITEM_TYPE IS NULL ) THEN
2148 l_Item_rec.BOM_ITEM_TYPE := g_Upd_Null_NUM ;
2149 ELSIF p_Item_rec.BOM_ITEM_TYPE = g_MISS_NUM THEN
2150 l_Item_rec.BOM_ITEM_TYPE := null;
2151 END IF;
2152
2153 IF(p_Item_rec.BASE_ITEM_ID IS NULL ) THEN
2154 l_Item_rec.BASE_ITEM_ID := g_Upd_Null_NUM ;
2155 ELSIF p_Item_rec.BASE_ITEM_ID = g_MISS_NUM THEN
2156 l_Item_rec.BASE_ITEM_ID := null;
2157 END IF;
2158
2159 IF(p_Item_rec.EFFECTIVITY_CONTROL IS NULL ) THEN
2160 l_Item_rec.EFFECTIVITY_CONTROL := g_Upd_Null_NUM ;
2161 ELSIF p_Item_rec.EFFECTIVITY_CONTROL = g_MISS_NUM THEN
2162 l_Item_rec.EFFECTIVITY_CONTROL := null;
2163 END IF;
2164
2165 IF(p_Item_rec.ENG_ITEM_FLAG IS NULL) THEN
2166 l_Item_rec.ENG_ITEM_FLAG := g_Upd_Null_CHAR;
2167 ELSIF p_Item_rec.ENG_ITEM_FLAG = g_MISS_CHAR THEN
2168 l_Item_rec.ENG_ITEM_FLAG := null;
2169 END IF;
2170
2171 IF(p_Item_rec.ENGINEERING_ECN_CODE IS NULL) THEN
2172 l_Item_rec.ENGINEERING_ECN_CODE := g_Upd_Null_CHAR;
2173 ELSIF p_Item_rec.ENGINEERING_ECN_CODE = g_MISS_CHAR THEN
2174 l_Item_rec.ENGINEERING_ECN_CODE := null;
2175 END IF;
2176
2177 IF(p_Item_rec.ENGINEERING_ITEM_ID IS NULL ) THEN
2178 l_Item_rec.ENGINEERING_ITEM_ID := g_Upd_Null_NUM ;
2179 ELSIF p_Item_rec.ENGINEERING_ITEM_ID = g_MISS_NUM THEN
2180 l_Item_rec.ENGINEERING_ITEM_ID := null;
2181 END IF;
2182
2183 IF(p_Item_rec.ENGINEERING_DATE = g_Null_DATE) THEN
2184 l_Item_rec.ENGINEERING_DATE := g_Upd_Null_DATE;
2185 ELSIF p_Item_rec.ENGINEERING_DATE = g_MISS_DATE THEN
2186 l_Item_rec.ENGINEERING_DATE := null;
2187 END IF;
2188
2189 IF(p_Item_rec.PRODUCT_FAMILY_ITEM_ID IS NULL ) THEN
2190 l_Item_rec.PRODUCT_FAMILY_ITEM_ID := g_Upd_Null_NUM ;
2191 ELSIF p_Item_rec.PRODUCT_FAMILY_ITEM_ID = g_MISS_NUM THEN
2192 l_Item_rec.PRODUCT_FAMILY_ITEM_ID := null;
2193 END IF;
2194
2195 IF(p_Item_rec.AUTO_CREATED_CONFIG_FLAG IS NULL) THEN
2196 l_Item_rec.AUTO_CREATED_CONFIG_FLAG := g_Upd_Null_CHAR;
2197 ELSIF p_Item_rec.AUTO_CREATED_CONFIG_FLAG = g_MISS_CHAR THEN
2198 l_Item_rec.AUTO_CREATED_CONFIG_FLAG := null;
2199 END IF;
2200
2201 IF(p_Item_rec.MODEL_CONFIG_CLAUSE_NAME IS NULL) THEN
2202 l_Item_rec.MODEL_CONFIG_CLAUSE_NAME := g_Upd_Null_CHAR;
2203 ELSIF p_Item_rec.MODEL_CONFIG_CLAUSE_NAME = g_MISS_CHAR THEN
2204 l_Item_rec.MODEL_CONFIG_CLAUSE_NAME := null;
2205 END IF;
2206
2207 IF(p_Item_rec.COSTING_ENABLED_FLAG IS NULL) THEN
2208 l_Item_rec.COSTING_ENABLED_FLAG := g_Upd_Null_CHAR;
2209 ELSIF p_Item_rec.COSTING_ENABLED_FLAG = g_MISS_CHAR THEN
2210 l_Item_rec.COSTING_ENABLED_FLAG := null;
2211 END IF;
2212
2213 IF(p_Item_rec.INVENTORY_ASSET_FLAG IS NULL) THEN
2214 l_Item_rec.INVENTORY_ASSET_FLAG := g_Upd_Null_CHAR;
2215 ELSIF p_Item_rec.INVENTORY_ASSET_FLAG = g_MISS_CHAR THEN
2216 l_Item_rec.INVENTORY_ASSET_FLAG := null;
2217 END IF;
2218
2219 IF(p_Item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG IS NULL) THEN
2220 l_Item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG := g_Upd_Null_CHAR;
2221 ELSIF p_Item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG = g_MISS_CHAR THEN
2222 l_Item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG := null;
2223 END IF;
2224
2225 IF(p_Item_rec.COST_OF_SALES_ACCOUNT IS NULL ) THEN
2226 l_Item_rec.COST_OF_SALES_ACCOUNT := g_Upd_Null_NUM ;
2227 ELSIF p_Item_rec.COST_OF_SALES_ACCOUNT = g_MISS_NUM THEN
2228 l_Item_rec.COST_OF_SALES_ACCOUNT := null;
2229 END IF;
2230
2231 IF(p_Item_rec.STD_LOT_SIZE IS NULL ) THEN
2232 l_Item_rec.STD_LOT_SIZE := g_Upd_Null_NUM ;
2233 ELSIF p_Item_rec.STD_LOT_SIZE = g_MISS_NUM THEN
2234 l_Item_rec.STD_LOT_SIZE := null;
2235 END IF;
2236
2237 IF(p_Item_rec.PURCHASING_ITEM_FLAG IS NULL) THEN
2238 l_Item_rec.PURCHASING_ITEM_FLAG := g_Upd_Null_CHAR;
2239 ELSIF p_Item_rec.PURCHASING_ITEM_FLAG = g_MISS_CHAR THEN
2240 l_Item_rec.PURCHASING_ITEM_FLAG := null;
2241 END IF;
2242
2243 IF(p_Item_rec.PURCHASING_ENABLED_FLAG IS NULL) THEN
2244 l_Item_rec.PURCHASING_ENABLED_FLAG := g_Upd_Null_CHAR;
2245 ELSIF p_Item_rec.PURCHASING_ENABLED_FLAG = g_MISS_CHAR THEN
2246 l_Item_rec.PURCHASING_ENABLED_FLAG := null;
2247 END IF;
2248
2249 IF(p_Item_rec.MUST_USE_APPROVED_VENDOR_FLAG IS NULL) THEN
2250 l_Item_rec.MUST_USE_APPROVED_VENDOR_FLAG := g_Upd_Null_CHAR;
2251 ELSIF p_Item_rec.MUST_USE_APPROVED_VENDOR_FLAG = g_MISS_CHAR THEN
2252 l_Item_rec.MUST_USE_APPROVED_VENDOR_FLAG := null;
2253 END IF;
2254
2255 IF(p_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG IS NULL) THEN
2256 l_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG := g_Upd_Null_CHAR;
2257 ELSIF p_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG = g_MISS_CHAR THEN
2258 l_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG := null;
2259 END IF;
2260
2261 IF(p_Item_rec.RFQ_REQUIRED_FLAG IS NULL) THEN
2262 l_Item_rec.RFQ_REQUIRED_FLAG := g_Upd_Null_CHAR;
2263 ELSIF p_Item_rec.RFQ_REQUIRED_FLAG = g_MISS_CHAR THEN
2264 l_Item_rec.RFQ_REQUIRED_FLAG := null;
2265 END IF;
2266
2267 IF(p_Item_rec.OUTSIDE_OPERATION_FLAG IS NULL) THEN
2268 l_Item_rec.OUTSIDE_OPERATION_FLAG := g_Upd_Null_CHAR;
2269 ELSIF p_Item_rec.OUTSIDE_OPERATION_FLAG = g_MISS_CHAR THEN
2270 l_Item_rec.OUTSIDE_OPERATION_FLAG := null;
2271 END IF;
2272
2273 IF(p_Item_rec.OUTSIDE_OPERATION_UOM_TYPE IS NULL) THEN
2274 l_Item_rec.OUTSIDE_OPERATION_UOM_TYPE := g_Upd_Null_CHAR;
2275 ELSIF p_Item_rec.OUTSIDE_OPERATION_UOM_TYPE = g_MISS_CHAR THEN
2276 l_Item_rec.OUTSIDE_OPERATION_UOM_TYPE := null;
2277 END IF;
2278
2279 IF(p_Item_rec.TAXABLE_FLAG IS NULL) THEN
2280 l_Item_rec.TAXABLE_FLAG := g_Upd_Null_CHAR;
2281 ELSIF p_Item_rec.TAXABLE_FLAG = g_MISS_CHAR THEN
2282 l_Item_rec.TAXABLE_FLAG := null;
2283 END IF;
2284
2285 IF(p_Item_rec.PURCHASING_TAX_CODE IS NULL) THEN
2286 l_Item_rec.PURCHASING_TAX_CODE := g_Upd_Null_CHAR;
2287 ELSIF p_Item_rec.PURCHASING_TAX_CODE = g_MISS_CHAR THEN
2288 l_Item_rec.PURCHASING_TAX_CODE := null;
2289 END IF;
2290
2291 IF(p_Item_rec.RECEIPT_REQUIRED_FLAG IS NULL) THEN
2292 l_Item_rec.RECEIPT_REQUIRED_FLAG := g_Upd_Null_CHAR;
2293 ELSIF p_Item_rec.RECEIPT_REQUIRED_FLAG = g_MISS_CHAR THEN
2294 l_Item_rec.RECEIPT_REQUIRED_FLAG := null;
2295 END IF;
2296
2297 IF(p_Item_rec.INSPECTION_REQUIRED_FLAG IS NULL) THEN
2298 l_Item_rec.INSPECTION_REQUIRED_FLAG := g_Upd_Null_CHAR;
2299 ELSIF p_Item_rec.INSPECTION_REQUIRED_FLAG = g_MISS_CHAR THEN
2300 l_Item_rec.INSPECTION_REQUIRED_FLAG := null;
2301 END IF;
2302
2303 IF(p_Item_rec.BUYER_ID IS NULL ) THEN
2304 l_Item_rec.BUYER_ID := g_Upd_Null_NUM ;
2305 ELSIF p_Item_rec.BUYER_ID = g_MISS_NUM THEN
2306 l_Item_rec.BUYER_ID := null;
2307 END IF;
2308
2309 IF(p_Item_rec.UNIT_OF_ISSUE IS NULL) THEN
2310 l_Item_rec.UNIT_OF_ISSUE := g_Upd_Null_CHAR;
2311 ELSIF p_Item_rec.UNIT_OF_ISSUE = g_MISS_CHAR THEN
2312 l_Item_rec.UNIT_OF_ISSUE := null;
2313 END IF;
2314
2315 IF(p_Item_rec.RECEIVE_CLOSE_TOLERANCE IS NULL ) THEN
2316 l_Item_rec.RECEIVE_CLOSE_TOLERANCE := g_Upd_Null_NUM ;
2317 ELSIF p_Item_rec.RECEIVE_CLOSE_TOLERANCE = g_MISS_NUM THEN
2318 l_Item_rec.RECEIVE_CLOSE_TOLERANCE := null;
2319 END IF;
2320
2321 IF(p_Item_rec.INVOICE_CLOSE_TOLERANCE IS NULL ) THEN
2322 l_Item_rec.INVOICE_CLOSE_TOLERANCE := g_Upd_Null_NUM ;
2323 ELSIF p_Item_rec.INVOICE_CLOSE_TOLERANCE = g_MISS_NUM THEN
2324 l_Item_rec.INVOICE_CLOSE_TOLERANCE := null;
2325 END IF;
2326
2327 IF(p_Item_rec.UN_NUMBER_ID IS NULL ) THEN
2328 l_Item_rec.UN_NUMBER_ID := g_Upd_Null_NUM ;
2329 ELSIF p_Item_rec.UN_NUMBER_ID = g_MISS_NUM THEN
2330 l_Item_rec.UN_NUMBER_ID := null;
2331 END IF;
2332
2333 IF(p_Item_rec.HAZARD_CLASS_ID IS NULL ) THEN
2334 l_Item_rec.HAZARD_CLASS_ID := g_Upd_Null_NUM ;
2335 ELSIF p_Item_rec.HAZARD_CLASS_ID = g_MISS_NUM THEN
2336 l_Item_rec.HAZARD_CLASS_ID := null;
2337 END IF;
2338
2339 IF(p_Item_rec.LIST_PRICE_PER_UNIT IS NULL ) THEN
2340 l_Item_rec.LIST_PRICE_PER_UNIT := g_Upd_Null_NUM ;
2341 ELSIF p_Item_rec.LIST_PRICE_PER_UNIT = g_MISS_NUM THEN
2342 l_Item_rec.LIST_PRICE_PER_UNIT := null;
2343 END IF;
2344
2345 IF(p_Item_rec.MARKET_PRICE IS NULL ) THEN
2346 l_Item_rec.MARKET_PRICE := g_Upd_Null_NUM ;
2347 ELSIF p_Item_rec.MARKET_PRICE = g_MISS_NUM THEN
2348 l_Item_rec.MARKET_PRICE := null;
2349 END IF;
2350
2351 IF(p_Item_rec.PRICE_TOLERANCE_PERCENT IS NULL ) THEN
2352 l_Item_rec.PRICE_TOLERANCE_PERCENT := g_Upd_Null_NUM ;
2353 ELSIF p_Item_rec.PRICE_TOLERANCE_PERCENT = g_MISS_NUM THEN
2354 l_Item_rec.PRICE_TOLERANCE_PERCENT := null;
2355 END IF;
2356
2357 IF(p_Item_rec.ROUNDING_FACTOR IS NULL ) THEN
2358 l_Item_rec.ROUNDING_FACTOR := g_Upd_Null_NUM ;
2359 ELSIF p_Item_rec.ROUNDING_FACTOR = g_MISS_NUM THEN
2360 l_Item_rec.ROUNDING_FACTOR := null;
2361 END IF;
2362
2363 IF(p_Item_rec.ENCUMBRANCE_ACCOUNT IS NULL ) THEN
2364 l_Item_rec.ENCUMBRANCE_ACCOUNT := g_Upd_Null_NUM ;
2365 ELSIF p_Item_rec.ENCUMBRANCE_ACCOUNT = g_MISS_NUM THEN
2366 l_Item_rec.ENCUMBRANCE_ACCOUNT := null;
2367 END IF;
2368
2369 IF(p_Item_rec.EXPENSE_ACCOUNT IS NULL ) THEN
2370 l_Item_rec.EXPENSE_ACCOUNT := g_Upd_Null_NUM ;
2371 ELSIF p_Item_rec.EXPENSE_ACCOUNT = g_MISS_NUM THEN
2372 l_Item_rec.EXPENSE_ACCOUNT := null;
2373 END IF;
2374
2375 IF(p_Item_rec.ASSET_CATEGORY_ID IS NULL ) THEN
2376 l_Item_rec.ASSET_CATEGORY_ID := g_Upd_Null_NUM ;
2377 ELSIF p_Item_rec.ASSET_CATEGORY_ID = g_MISS_NUM THEN
2378 l_Item_rec.ASSET_CATEGORY_ID := null;
2379 END IF;
2380
2381 IF(p_Item_rec.RECEIPT_DAYS_EXCEPTION_CODE IS NULL) THEN
2382 l_Item_rec.RECEIPT_DAYS_EXCEPTION_CODE := g_Upd_Null_CHAR;
2383 ELSIF p_Item_rec.RECEIPT_DAYS_EXCEPTION_CODE = g_MISS_CHAR THEN
2384 l_Item_rec.RECEIPT_DAYS_EXCEPTION_CODE := null;
2385 END IF;
2386
2387 IF(p_Item_rec.DAYS_EARLY_RECEIPT_ALLOWED IS NULL ) THEN
2388 l_Item_rec.DAYS_EARLY_RECEIPT_ALLOWED := g_Upd_Null_NUM ;
2389 ELSIF p_Item_rec.DAYS_EARLY_RECEIPT_ALLOWED = g_MISS_NUM THEN
2390 l_Item_rec.DAYS_EARLY_RECEIPT_ALLOWED := null;
2391 END IF;
2392
2393 IF(p_Item_rec.DAYS_LATE_RECEIPT_ALLOWED IS NULL ) THEN
2394 l_Item_rec.DAYS_LATE_RECEIPT_ALLOWED := g_Upd_Null_NUM ;
2395 ELSIF p_Item_rec.DAYS_LATE_RECEIPT_ALLOWED = g_MISS_NUM THEN
2396 l_Item_rec.DAYS_LATE_RECEIPT_ALLOWED := null;
2397 END IF;
2398
2399 IF(p_Item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG IS NULL) THEN
2400 l_Item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG := g_Upd_Null_CHAR;
2401 ELSIF p_Item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG = g_MISS_CHAR THEN
2402 l_Item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG := null;
2403 END IF;
2404
2405 IF(p_Item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG IS NULL) THEN
2406 l_Item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG := g_Upd_Null_CHAR;
2407 ELSIF p_Item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG = g_MISS_CHAR THEN
2408 l_Item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG := null;
2409 END IF;
2410
2411 IF(p_Item_rec.ALLOW_EXPRESS_DELIVERY_FLAG IS NULL) THEN
2412 l_Item_rec.ALLOW_EXPRESS_DELIVERY_FLAG := g_Upd_Null_CHAR;
2413 ELSIF p_Item_rec.ALLOW_EXPRESS_DELIVERY_FLAG = g_MISS_CHAR THEN
2414 l_Item_rec.ALLOW_EXPRESS_DELIVERY_FLAG := null;
2415 END IF;
2416
2417 IF(p_Item_rec.QTY_RCV_EXCEPTION_CODE IS NULL) THEN
2418 l_Item_rec.QTY_RCV_EXCEPTION_CODE := g_Upd_Null_CHAR;
2419 ELSIF p_Item_rec.QTY_RCV_EXCEPTION_CODE = g_MISS_CHAR THEN
2420 l_Item_rec.QTY_RCV_EXCEPTION_CODE := null;
2421 END IF;
2422
2423 IF(p_Item_rec.QTY_RCV_TOLERANCE IS NULL ) THEN
2424 l_Item_rec.QTY_RCV_TOLERANCE := g_Upd_Null_NUM ;
2425 ELSIF p_Item_rec.QTY_RCV_TOLERANCE = g_MISS_NUM THEN
2426 l_Item_rec.QTY_RCV_TOLERANCE := null;
2427 END IF;
2428
2429 IF(p_Item_rec.RECEIVING_ROUTING_ID IS NULL ) THEN
2430 l_Item_rec.RECEIVING_ROUTING_ID := g_Upd_Null_NUM ;
2431 ELSIF p_Item_rec.RECEIVING_ROUTING_ID = g_MISS_NUM THEN
2432 l_Item_rec.RECEIVING_ROUTING_ID := null;
2433 END IF;
2434
2435 IF(p_Item_rec.ENFORCE_SHIP_TO_LOCATION_CODE IS NULL) THEN
2436 l_Item_rec.ENFORCE_SHIP_TO_LOCATION_CODE := g_Upd_Null_CHAR;
2437 ELSIF p_Item_rec.ENFORCE_SHIP_TO_LOCATION_CODE = g_MISS_CHAR THEN
2438 l_Item_rec.ENFORCE_SHIP_TO_LOCATION_CODE := null;
2439 END IF;
2440
2441 IF(p_Item_rec.WEIGHT_UOM_CODE IS NULL) THEN
2442 l_Item_rec.WEIGHT_UOM_CODE := g_Upd_Null_CHAR;
2443 ELSIF p_Item_rec.WEIGHT_UOM_CODE = g_MISS_CHAR THEN
2444 l_Item_rec.WEIGHT_UOM_CODE := null;
2445 END IF;
2446
2447 IF(p_Item_rec.UNIT_WEIGHT IS NULL ) THEN
2448 l_Item_rec.UNIT_WEIGHT := g_Upd_Null_NUM ;
2449 ELSIF p_Item_rec.UNIT_WEIGHT = g_MISS_NUM THEN
2450 l_Item_rec.UNIT_WEIGHT := null;
2451 END IF;
2452
2453 IF(p_Item_rec.VOLUME_UOM_CODE IS NULL) THEN
2454 l_Item_rec.VOLUME_UOM_CODE := g_Upd_Null_CHAR;
2455 ELSIF p_Item_rec.VOLUME_UOM_CODE = g_MISS_CHAR THEN
2456 l_Item_rec.VOLUME_UOM_CODE := null;
2457 END IF;
2458
2459 IF(p_Item_rec.UNIT_VOLUME IS NULL ) THEN
2460 l_Item_rec.UNIT_VOLUME := g_Upd_Null_NUM ;
2461 ELSIF p_Item_rec.UNIT_VOLUME = g_MISS_NUM THEN
2462 l_Item_rec.UNIT_VOLUME := null;
2463 END IF;
2464
2465 IF(p_Item_rec.CONTAINER_ITEM_FLAG IS NULL) THEN
2466 l_Item_rec.CONTAINER_ITEM_FLAG := g_Upd_Null_CHAR;
2467 ELSIF p_Item_rec.CONTAINER_ITEM_FLAG = g_MISS_CHAR THEN
2468 l_Item_rec.CONTAINER_ITEM_FLAG := null;
2469 END IF;
2470
2471 IF(p_Item_rec.VEHICLE_ITEM_FLAG IS NULL) THEN
2472 l_Item_rec.VEHICLE_ITEM_FLAG := g_Upd_Null_CHAR;
2473 ELSIF p_Item_rec.VEHICLE_ITEM_FLAG = g_MISS_CHAR THEN
2474 l_Item_rec.VEHICLE_ITEM_FLAG := null;
2475 END IF;
2476
2477 IF(p_Item_rec.CONTAINER_TYPE_CODE IS NULL) THEN
2478 l_Item_rec.CONTAINER_TYPE_CODE := g_Upd_Null_CHAR;
2479 ELSIF p_Item_rec.CONTAINER_TYPE_CODE = g_MISS_CHAR THEN
2480 l_Item_rec.CONTAINER_TYPE_CODE := null;
2481 END IF;
2482
2483 IF(p_Item_rec.INTERNAL_VOLUME IS NULL ) THEN
2484 l_Item_rec.INTERNAL_VOLUME := g_Upd_Null_NUM ;
2485 ELSIF p_Item_rec.INTERNAL_VOLUME = g_MISS_NUM THEN
2486 l_Item_rec.INTERNAL_VOLUME := null;
2487 END IF;
2488
2489 IF(p_Item_rec.MAXIMUM_LOAD_WEIGHT IS NULL ) THEN
2490 l_Item_rec.MAXIMUM_LOAD_WEIGHT := g_Upd_Null_NUM ;
2491 ELSIF p_Item_rec.MAXIMUM_LOAD_WEIGHT = g_MISS_NUM THEN
2492 l_Item_rec.MAXIMUM_LOAD_WEIGHT := null;
2493 END IF;
2494
2495 IF(p_Item_rec.MINIMUM_FILL_PERCENT IS NULL ) THEN
2496 l_Item_rec.MINIMUM_FILL_PERCENT := g_Upd_Null_NUM ;
2497 ELSIF p_Item_rec.MINIMUM_FILL_PERCENT = g_MISS_NUM THEN
2498 l_Item_rec.MINIMUM_FILL_PERCENT := null;
2499 END IF;
2500
2501 IF(p_Item_rec.INVENTORY_PLANNING_CODE IS NULL ) THEN
2502 l_Item_rec.INVENTORY_PLANNING_CODE := g_Upd_Null_NUM ;
2503 ELSIF p_Item_rec.INVENTORY_PLANNING_CODE = g_MISS_NUM THEN
2504 l_Item_rec.INVENTORY_PLANNING_CODE := null;
2505 END IF;
2506
2507 IF(p_Item_rec.PLANNER_CODE IS NULL) THEN
2508 l_Item_rec.PLANNER_CODE := g_Upd_Null_CHAR;
2509 ELSIF p_Item_rec.PLANNER_CODE = g_MISS_CHAR THEN
2510 l_Item_rec.PLANNER_CODE := null;
2511 END IF;
2512
2513 IF(p_Item_rec.PLANNING_MAKE_BUY_CODE IS NULL ) THEN
2514 l_Item_rec.PLANNING_MAKE_BUY_CODE := g_Upd_Null_NUM ;
2515 ELSIF p_Item_rec.PLANNING_MAKE_BUY_CODE = g_MISS_NUM THEN
2516 l_Item_rec.PLANNING_MAKE_BUY_CODE := null;
2517 END IF;
2518
2519 IF(p_Item_rec.MIN_MINMAX_QUANTITY IS NULL ) THEN
2520 l_Item_rec.MIN_MINMAX_QUANTITY := g_Upd_Null_NUM ;
2521 ELSIF p_Item_rec.MIN_MINMAX_QUANTITY = g_MISS_NUM THEN
2522 l_Item_rec.MIN_MINMAX_QUANTITY := null;
2523 END IF;
2524
2525 IF(p_Item_rec.MAX_MINMAX_QUANTITY IS NULL ) THEN
2526 l_Item_rec.MAX_MINMAX_QUANTITY := g_Upd_Null_NUM ;
2527 ELSIF p_Item_rec.MAX_MINMAX_QUANTITY = g_MISS_NUM THEN
2528 l_Item_rec.MAX_MINMAX_QUANTITY := null;
2529 END IF;
2530
2531 IF(p_Item_rec.MINIMUM_ORDER_QUANTITY IS NULL ) THEN
2532 l_Item_rec.MINIMUM_ORDER_QUANTITY := g_Upd_Null_NUM ;
2533 ELSIF p_Item_rec.MINIMUM_ORDER_QUANTITY = g_MISS_NUM THEN
2534 l_Item_rec.MINIMUM_ORDER_QUANTITY := null;
2535 END IF;
2536
2537 IF(p_Item_rec.MAXIMUM_ORDER_QUANTITY IS NULL ) THEN
2538 l_Item_rec.MAXIMUM_ORDER_QUANTITY := g_Upd_Null_NUM ;
2539 ELSIF p_Item_rec.MAXIMUM_ORDER_QUANTITY = g_MISS_NUM THEN
2540 l_Item_rec.MAXIMUM_ORDER_QUANTITY := null;
2541 END IF;
2542
2543 IF(p_Item_rec.ORDER_COST IS NULL ) THEN
2544 l_Item_rec.ORDER_COST := g_Upd_Null_NUM ;
2545 ELSIF p_Item_rec.ORDER_COST = g_MISS_NUM THEN
2546 l_Item_rec.ORDER_COST := null;
2547 END IF;
2548
2549 IF(p_Item_rec.CARRYING_COST IS NULL ) THEN
2550 l_Item_rec.CARRYING_COST := g_Upd_Null_NUM ;
2551 ELSIF p_Item_rec.CARRYING_COST = g_MISS_NUM THEN
2552 l_Item_rec.CARRYING_COST := null;
2553 END IF;
2554
2555 IF(p_Item_rec.SOURCE_TYPE IS NULL ) THEN
2556 l_Item_rec.SOURCE_TYPE := g_Upd_Null_NUM ;
2557 ELSIF p_Item_rec.SOURCE_TYPE = g_MISS_NUM THEN
2558 l_Item_rec.SOURCE_TYPE := null;
2559 END IF;
2560
2561 IF(p_Item_rec.SOURCE_ORGANIZATION_ID IS NULL ) THEN
2562 l_Item_rec.SOURCE_ORGANIZATION_ID := g_Upd_Null_NUM ;
2563 ELSIF p_Item_rec.SOURCE_ORGANIZATION_ID = g_MISS_NUM THEN
2564 l_Item_rec.SOURCE_ORGANIZATION_ID := null;
2565 END IF;
2566
2567 IF(p_Item_rec.SOURCE_SUBINVENTORY IS NULL) THEN
2568 l_Item_rec.SOURCE_SUBINVENTORY := g_Upd_Null_CHAR;
2569 ELSIF p_Item_rec.SOURCE_SUBINVENTORY = g_MISS_CHAR THEN
2570 l_Item_rec.SOURCE_SUBINVENTORY := null;
2571 END IF;
2572
2573 IF(p_Item_rec.MRP_SAFETY_STOCK_CODE IS NULL ) THEN
2574 l_Item_rec.MRP_SAFETY_STOCK_CODE := g_Upd_Null_NUM ;
2575 ELSIF p_Item_rec.MRP_SAFETY_STOCK_CODE = g_MISS_NUM THEN
2576 l_Item_rec.MRP_SAFETY_STOCK_CODE := null;
2577 END IF;
2578
2579 IF(p_Item_rec.SAFETY_STOCK_BUCKET_DAYS IS NULL ) THEN
2580 l_Item_rec.SAFETY_STOCK_BUCKET_DAYS := g_Upd_Null_NUM ;
2581 ELSIF p_Item_rec.SAFETY_STOCK_BUCKET_DAYS = g_MISS_NUM THEN
2582 l_Item_rec.SAFETY_STOCK_BUCKET_DAYS := null;
2583 END IF;
2584
2585 IF(p_Item_rec.MRP_SAFETY_STOCK_PERCENT IS NULL ) THEN
2586 l_Item_rec.MRP_SAFETY_STOCK_PERCENT := g_Upd_Null_NUM ;
2587 ELSIF p_Item_rec.MRP_SAFETY_STOCK_PERCENT = g_MISS_NUM THEN
2588 l_Item_rec.MRP_SAFETY_STOCK_PERCENT := null;
2589 END IF;
2590
2591 IF(p_Item_rec.FIXED_ORDER_QUANTITY IS NULL ) THEN
2592 l_Item_rec.FIXED_ORDER_QUANTITY := g_Upd_Null_NUM ;
2593 ELSIF p_Item_rec.FIXED_ORDER_QUANTITY = g_MISS_NUM THEN
2594 l_Item_rec.FIXED_ORDER_QUANTITY := null;
2595 END IF;
2596
2597 IF(p_Item_rec.FIXED_DAYS_SUPPLY IS NULL ) THEN
2598 l_Item_rec.FIXED_DAYS_SUPPLY := g_Upd_Null_NUM ;
2599 ELSIF p_Item_rec.FIXED_DAYS_SUPPLY = g_MISS_NUM THEN
2600 l_Item_rec.FIXED_DAYS_SUPPLY := null;
2601 END IF;
2602
2603 IF(p_Item_rec.FIXED_LOT_MULTIPLIER IS NULL ) THEN
2604 l_Item_rec.FIXED_LOT_MULTIPLIER := g_Upd_Null_NUM ;
2605 ELSIF p_Item_rec.FIXED_LOT_MULTIPLIER = g_MISS_NUM THEN
2606 l_Item_rec.FIXED_LOT_MULTIPLIER := null;
2607 END IF;
2608
2609 IF(p_Item_rec.MRP_PLANNING_CODE IS NULL ) THEN
2610 l_Item_rec.MRP_PLANNING_CODE := g_Upd_Null_NUM ;
2611 ELSIF p_Item_rec.MRP_PLANNING_CODE = g_MISS_NUM THEN
2612 l_Item_rec.MRP_PLANNING_CODE := null;
2613 END IF;
2614
2615 IF(p_Item_rec.ATO_FORECAST_CONTROL IS NULL ) THEN
2616 l_Item_rec.ATO_FORECAST_CONTROL := g_Upd_Null_NUM ;
2617 ELSIF p_Item_rec.ATO_FORECAST_CONTROL = g_MISS_NUM THEN
2618 l_Item_rec.ATO_FORECAST_CONTROL := null;
2619 END IF;
2620
2621 IF(p_Item_rec.PLANNING_EXCEPTION_SET IS NULL) THEN
2622 l_Item_rec.PLANNING_EXCEPTION_SET := g_Upd_Null_CHAR;
2623 ELSIF p_Item_rec.PLANNING_EXCEPTION_SET = g_MISS_CHAR THEN
2624 l_Item_rec.PLANNING_EXCEPTION_SET := null;
2625 END IF;
2626
2627 IF(p_Item_rec.END_ASSEMBLY_PEGGING_FLAG IS NULL) THEN
2628 l_Item_rec.END_ASSEMBLY_PEGGING_FLAG := g_Upd_Null_CHAR;
2629 ELSIF p_Item_rec.END_ASSEMBLY_PEGGING_FLAG = g_MISS_CHAR THEN
2630 l_Item_rec.END_ASSEMBLY_PEGGING_FLAG := null;
2631 END IF;
2632
2633 IF(p_Item_rec.SHRINKAGE_RATE IS NULL ) THEN
2634 l_Item_rec.SHRINKAGE_RATE := g_Upd_Null_NUM ;
2635 ELSIF p_Item_rec.SHRINKAGE_RATE = g_MISS_NUM THEN
2636 l_Item_rec.SHRINKAGE_RATE := null;
2637 END IF;
2638
2639 IF(p_Item_rec.ROUNDING_CONTROL_TYPE IS NULL ) THEN
2640 l_Item_rec.ROUNDING_CONTROL_TYPE := g_Upd_Null_NUM ;
2641 ELSIF p_Item_rec.ROUNDING_CONTROL_TYPE = g_MISS_NUM THEN
2642 l_Item_rec.ROUNDING_CONTROL_TYPE := null;
2643 END IF;
2644
2645 IF(p_Item_rec.ACCEPTABLE_EARLY_DAYS IS NULL ) THEN
2646 l_Item_rec.ACCEPTABLE_EARLY_DAYS := g_Upd_Null_NUM ;
2647 ELSIF p_Item_rec.ACCEPTABLE_EARLY_DAYS = g_MISS_NUM THEN
2648 l_Item_rec.ACCEPTABLE_EARLY_DAYS := null;
2649 END IF;
2650
2651 IF(p_Item_rec.REPETITIVE_PLANNING_FLAG IS NULL) THEN
2652 l_Item_rec.REPETITIVE_PLANNING_FLAG := g_Upd_Null_CHAR;
2653 ELSIF p_Item_rec.REPETITIVE_PLANNING_FLAG = g_MISS_CHAR THEN
2654 l_Item_rec.REPETITIVE_PLANNING_FLAG := null;
2655 END IF;
2656
2657 IF(p_Item_rec.OVERRUN_PERCENTAGE IS NULL ) THEN
2658 l_Item_rec.OVERRUN_PERCENTAGE := g_Upd_Null_NUM ;
2659 ELSIF p_Item_rec.OVERRUN_PERCENTAGE = g_MISS_NUM THEN
2660 l_Item_rec.OVERRUN_PERCENTAGE := null;
2661 END IF;
2662
2663 IF(p_Item_rec.ACCEPTABLE_RATE_INCREASE IS NULL ) THEN
2664 l_Item_rec.ACCEPTABLE_RATE_INCREASE := g_Upd_Null_NUM ;
2665 ELSIF p_Item_rec.ACCEPTABLE_RATE_INCREASE = g_MISS_NUM THEN
2666 l_Item_rec.ACCEPTABLE_RATE_INCREASE := null;
2667 END IF;
2668
2669 IF(p_Item_rec.ACCEPTABLE_RATE_DECREASE IS NULL ) THEN
2670 l_Item_rec.ACCEPTABLE_RATE_DECREASE := g_Upd_Null_NUM ;
2671 ELSIF p_Item_rec.ACCEPTABLE_RATE_DECREASE = g_MISS_NUM THEN
2672 l_Item_rec.ACCEPTABLE_RATE_DECREASE := null;
2673 END IF;
2674
2675 IF(p_Item_rec.MRP_CALCULATE_ATP_FLAG IS NULL) THEN
2676 l_Item_rec.MRP_CALCULATE_ATP_FLAG := g_Upd_Null_CHAR;
2677 ELSIF p_Item_rec.MRP_CALCULATE_ATP_FLAG = g_MISS_CHAR THEN
2678 l_Item_rec.MRP_CALCULATE_ATP_FLAG := null;
2679 END IF;
2680
2681 IF(p_Item_rec.AUTO_REDUCE_MPS IS NULL ) THEN
2682 l_Item_rec.AUTO_REDUCE_MPS := g_Upd_Null_NUM ;
2683 ELSIF p_Item_rec.AUTO_REDUCE_MPS = g_MISS_NUM THEN
2684 l_Item_rec.AUTO_REDUCE_MPS := null;
2685 END IF;
2686
2687 IF(p_Item_rec.PLANNING_TIME_FENCE_CODE IS NULL ) THEN
2688 l_Item_rec.PLANNING_TIME_FENCE_CODE := g_Upd_Null_NUM ;
2689 ELSIF p_Item_rec.PLANNING_TIME_FENCE_CODE = g_MISS_NUM THEN
2690 l_Item_rec.PLANNING_TIME_FENCE_CODE := null;
2691 END IF;
2692
2693 IF(p_Item_rec.PLANNING_TIME_FENCE_DAYS IS NULL ) THEN
2694 l_Item_rec.PLANNING_TIME_FENCE_DAYS := g_Upd_Null_NUM ;
2695 ELSIF p_Item_rec.PLANNING_TIME_FENCE_DAYS = g_MISS_NUM THEN
2696 l_Item_rec.PLANNING_TIME_FENCE_DAYS := null;
2697 END IF;
2698
2699 IF(p_Item_rec.DEMAND_TIME_FENCE_CODE IS NULL ) THEN
2700 l_Item_rec.DEMAND_TIME_FENCE_CODE := g_Upd_Null_NUM ;
2701 ELSIF p_Item_rec.DEMAND_TIME_FENCE_CODE = g_MISS_NUM THEN
2702 l_Item_rec.DEMAND_TIME_FENCE_CODE := null;
2703 END IF;
2704
2705 IF(p_Item_rec.DEMAND_TIME_FENCE_DAYS IS NULL ) THEN
2706 l_Item_rec.DEMAND_TIME_FENCE_DAYS := g_Upd_Null_NUM ;
2707 ELSIF p_Item_rec.DEMAND_TIME_FENCE_DAYS = g_MISS_NUM THEN
2708 l_Item_rec.DEMAND_TIME_FENCE_DAYS := null;
2709 END IF;
2710
2711 IF(p_Item_rec.RELEASE_TIME_FENCE_CODE IS NULL ) THEN
2712 l_Item_rec.RELEASE_TIME_FENCE_CODE := g_Upd_Null_NUM ;
2713 ELSIF p_Item_rec.RELEASE_TIME_FENCE_CODE = g_MISS_NUM THEN
2714 l_Item_rec.RELEASE_TIME_FENCE_CODE := null;
2715 END IF;
2716
2717 IF(p_Item_rec.RELEASE_TIME_FENCE_DAYS IS NULL ) THEN
2718 l_Item_rec.RELEASE_TIME_FENCE_DAYS := g_Upd_Null_NUM ;
2719 ELSIF p_Item_rec.RELEASE_TIME_FENCE_DAYS = g_MISS_NUM THEN
2720 l_Item_rec.RELEASE_TIME_FENCE_DAYS := null;
2721 END IF;
2722
2723 IF(p_Item_rec.PREPROCESSING_LEAD_TIME IS NULL ) THEN
2724 l_Item_rec.PREPROCESSING_LEAD_TIME := g_Upd_Null_NUM ;
2725 ELSIF p_Item_rec.PREPROCESSING_LEAD_TIME = g_MISS_NUM THEN
2726 l_Item_rec.PREPROCESSING_LEAD_TIME := null;
2727 END IF;
2728
2729 IF(p_Item_rec.FULL_LEAD_TIME IS NULL ) THEN
2730 l_Item_rec.FULL_LEAD_TIME := g_Upd_Null_NUM ;
2731 ELSIF p_Item_rec.FULL_LEAD_TIME = g_MISS_NUM THEN
2732 l_Item_rec.FULL_LEAD_TIME := null;
2733 END IF;
2734
2735 IF(p_Item_rec.POSTPROCESSING_LEAD_TIME IS NULL ) THEN
2736 l_Item_rec.POSTPROCESSING_LEAD_TIME := g_Upd_Null_NUM ;
2737 ELSIF p_Item_rec.POSTPROCESSING_LEAD_TIME = g_MISS_NUM THEN
2738 l_Item_rec.POSTPROCESSING_LEAD_TIME := null;
2739 END IF;
2740
2741 IF(p_Item_rec.FIXED_LEAD_TIME IS NULL ) THEN
2742 l_Item_rec.FIXED_LEAD_TIME := g_Upd_Null_NUM ;
2743 ELSIF p_Item_rec.FIXED_LEAD_TIME = g_MISS_NUM THEN
2744 l_Item_rec.FIXED_LEAD_TIME := null;
2745 END IF;
2746
2747 IF(p_Item_rec.VARIABLE_LEAD_TIME IS NULL ) THEN
2748 l_Item_rec.VARIABLE_LEAD_TIME := g_Upd_Null_NUM ;
2749 ELSIF p_Item_rec.VARIABLE_LEAD_TIME = g_MISS_NUM THEN
2750 l_Item_rec.VARIABLE_LEAD_TIME := null;
2751 END IF;
2752
2753 IF(p_Item_rec.CUM_MANUFACTURING_LEAD_TIME IS NULL ) THEN
2754 l_Item_rec.CUM_MANUFACTURING_LEAD_TIME := g_Upd_Null_NUM ;
2755 ELSIF p_Item_rec.CUM_MANUFACTURING_LEAD_TIME = g_MISS_NUM THEN
2756 l_Item_rec.CUM_MANUFACTURING_LEAD_TIME := null;
2757 END IF;
2758
2759 IF(p_Item_rec.CUMULATIVE_TOTAL_LEAD_TIME IS NULL ) THEN
2760 l_Item_rec.CUMULATIVE_TOTAL_LEAD_TIME := g_Upd_Null_NUM ;
2761 ELSIF p_Item_rec.CUMULATIVE_TOTAL_LEAD_TIME = g_MISS_NUM THEN
2762 l_Item_rec.CUMULATIVE_TOTAL_LEAD_TIME := null;
2763 END IF;
2764
2765 IF(p_Item_rec.LEAD_TIME_LOT_SIZE IS NULL ) THEN
2766 l_Item_rec.LEAD_TIME_LOT_SIZE := g_Upd_Null_NUM ;
2767 ELSIF p_Item_rec.LEAD_TIME_LOT_SIZE = g_MISS_NUM THEN
2768 l_Item_rec.LEAD_TIME_LOT_SIZE := null;
2769 END IF;
2770
2771 IF(p_Item_rec.BUILD_IN_WIP_FLAG IS NULL) THEN
2772 l_Item_rec.BUILD_IN_WIP_FLAG := g_Upd_Null_CHAR;
2773 ELSIF p_Item_rec.BUILD_IN_WIP_FLAG = g_MISS_CHAR THEN
2774 l_Item_rec.BUILD_IN_WIP_FLAG := null;
2775 END IF;
2776
2777 IF(p_Item_rec.WIP_SUPPLY_TYPE IS NULL ) THEN
2778 l_Item_rec.WIP_SUPPLY_TYPE := g_Upd_Null_NUM ;
2779 ELSIF p_Item_rec.WIP_SUPPLY_TYPE = g_MISS_NUM THEN
2780 l_Item_rec.WIP_SUPPLY_TYPE := null;
2781 END IF;
2782
2783 IF(p_Item_rec.WIP_SUPPLY_SUBINVENTORY IS NULL) THEN
2784 l_Item_rec.WIP_SUPPLY_SUBINVENTORY := g_Upd_Null_CHAR;
2785 ELSIF p_Item_rec.WIP_SUPPLY_SUBINVENTORY = g_MISS_CHAR THEN
2786 l_Item_rec.WIP_SUPPLY_SUBINVENTORY := null;
2787 END IF;
2788
2789 IF(p_Item_rec.WIP_SUPPLY_LOCATOR_ID IS NULL ) THEN
2790 l_Item_rec.WIP_SUPPLY_LOCATOR_ID := g_Upd_Null_NUM ;
2791 ELSIF p_Item_rec.WIP_SUPPLY_LOCATOR_ID = g_MISS_NUM THEN
2792 l_Item_rec.WIP_SUPPLY_LOCATOR_ID := null;
2793 END IF;
2794
2795 IF(p_Item_rec.OVERCOMPLETION_TOLERANCE_TYPE IS NULL ) THEN
2796 l_Item_rec.OVERCOMPLETION_TOLERANCE_TYPE := g_Upd_Null_NUM ;
2797 ELSIF p_Item_rec.OVERCOMPLETION_TOLERANCE_TYPE = g_MISS_NUM THEN
2798 l_Item_rec.OVERCOMPLETION_TOLERANCE_TYPE := null;
2799 END IF;
2800
2801 IF(p_Item_rec.OVERCOMPLETION_TOLERANCE_VALUE IS NULL ) THEN
2802 l_Item_rec.OVERCOMPLETION_TOLERANCE_VALUE := g_Upd_Null_NUM ;
2803 ELSIF p_Item_rec.OVERCOMPLETION_TOLERANCE_VALUE = g_MISS_NUM THEN
2804 l_Item_rec.OVERCOMPLETION_TOLERANCE_VALUE := null;
2805 END IF;
2806
2807 IF(p_Item_rec.CUSTOMER_ORDER_FLAG IS NULL) THEN
2808 l_Item_rec.CUSTOMER_ORDER_FLAG := g_Upd_Null_CHAR;
2809 ELSIF p_Item_rec.CUSTOMER_ORDER_FLAG = g_MISS_CHAR THEN
2810 l_Item_rec.CUSTOMER_ORDER_FLAG := null;
2811 END IF;
2812
2813 IF(p_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG IS NULL) THEN
2814 l_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG := g_Upd_Null_CHAR;
2815 ELSIF p_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG = g_MISS_CHAR THEN
2816 l_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG := null;
2817 END IF;
2818
2819 IF(p_Item_rec.SHIPPABLE_ITEM_FLAG IS NULL) THEN
2820 l_Item_rec.SHIPPABLE_ITEM_FLAG := g_Upd_Null_CHAR;
2821 ELSIF p_Item_rec.SHIPPABLE_ITEM_FLAG = g_MISS_CHAR THEN
2822 l_Item_rec.SHIPPABLE_ITEM_FLAG := null;
2823 END IF;
2824
2825 IF(p_Item_rec.INTERNAL_ORDER_FLAG IS NULL) THEN
2826 l_Item_rec.INTERNAL_ORDER_FLAG := g_Upd_Null_CHAR;
2827 ELSIF p_Item_rec.INTERNAL_ORDER_FLAG = g_MISS_CHAR THEN
2828 l_Item_rec.INTERNAL_ORDER_FLAG := null;
2829 END IF;
2830
2831 IF(p_Item_rec.INTERNAL_ORDER_ENABLED_FLAG IS NULL) THEN
2832 l_Item_rec.INTERNAL_ORDER_ENABLED_FLAG := g_Upd_Null_CHAR;
2833 ELSIF p_Item_rec.INTERNAL_ORDER_ENABLED_FLAG = g_MISS_CHAR THEN
2834 l_Item_rec.INTERNAL_ORDER_ENABLED_FLAG := null;
2835 END IF;
2836
2837 IF(p_Item_rec.SO_TRANSACTIONS_FLAG IS NULL) THEN
2838 l_Item_rec.SO_TRANSACTIONS_FLAG := g_Upd_Null_CHAR;
2839 ELSIF p_Item_rec.SO_TRANSACTIONS_FLAG = g_MISS_CHAR THEN
2840 l_Item_rec.SO_TRANSACTIONS_FLAG := null;
2841 END IF;
2842
2843 IF(p_Item_rec.PICK_COMPONENTS_FLAG IS NULL) THEN
2844 l_Item_rec.PICK_COMPONENTS_FLAG := g_Upd_Null_CHAR;
2845 ELSIF p_Item_rec.PICK_COMPONENTS_FLAG = g_MISS_CHAR THEN
2846 l_Item_rec.PICK_COMPONENTS_FLAG := null;
2847 END IF;
2848
2849 IF(p_Item_rec.ATP_FLAG IS NULL) THEN
2850 l_Item_rec.ATP_FLAG := g_Upd_Null_CHAR;
2851 ELSIF p_Item_rec.ATP_FLAG = g_MISS_CHAR THEN
2852 l_Item_rec.ATP_FLAG := null;
2853 END IF;
2854
2855 IF(p_Item_rec.REPLENISH_TO_ORDER_FLAG IS NULL) THEN
2856 l_Item_rec.REPLENISH_TO_ORDER_FLAG := g_Upd_Null_CHAR;
2857 ELSIF p_Item_rec.REPLENISH_TO_ORDER_FLAG = g_MISS_CHAR THEN
2858 l_Item_rec.REPLENISH_TO_ORDER_FLAG := null;
2859 END IF;
2860
2861 IF(p_Item_rec.ATP_RULE_ID IS NULL ) THEN
2862 l_Item_rec.ATP_RULE_ID := g_Upd_Null_NUM ;
2863 ELSIF p_Item_rec.ATP_RULE_ID = g_MISS_NUM THEN
2864 l_Item_rec.ATP_RULE_ID := null;
2865 END IF;
2866
2867 IF(p_Item_rec.ATP_COMPONENTS_FLAG IS NULL) THEN
2868 l_Item_rec.ATP_COMPONENTS_FLAG := g_Upd_Null_CHAR;
2869 ELSIF p_Item_rec.ATP_COMPONENTS_FLAG = g_MISS_CHAR THEN
2870 l_Item_rec.ATP_COMPONENTS_FLAG := null;
2871 END IF;
2872
2873 IF(p_Item_rec.SHIP_MODEL_COMPLETE_FLAG IS NULL) THEN
2874 l_Item_rec.SHIP_MODEL_COMPLETE_FLAG := g_Upd_Null_CHAR;
2875 ELSIF p_Item_rec.SHIP_MODEL_COMPLETE_FLAG = g_MISS_CHAR THEN
2876 l_Item_rec.SHIP_MODEL_COMPLETE_FLAG := null;
2877 END IF;
2878
2879 IF(p_Item_rec.PICKING_RULE_ID IS NULL ) THEN
2880 l_Item_rec.PICKING_RULE_ID := g_Upd_Null_NUM ;
2881 ELSIF p_Item_rec.PICKING_RULE_ID = g_MISS_NUM THEN
2882 l_Item_rec.PICKING_RULE_ID := null;
2883 END IF;
2884
2885 IF(p_Item_rec.COLLATERAL_FLAG IS NULL) THEN
2886 l_Item_rec.COLLATERAL_FLAG := g_Upd_Null_CHAR;
2887 ELSIF p_Item_rec.COLLATERAL_FLAG = g_MISS_CHAR THEN
2888 l_Item_rec.COLLATERAL_FLAG := null;
2889 END IF;
2890
2891 IF(p_Item_rec.DEFAULT_SHIPPING_ORG IS NULL ) THEN
2892 l_Item_rec.DEFAULT_SHIPPING_ORG := g_Upd_Null_NUM ;
2893 ELSIF p_Item_rec.DEFAULT_SHIPPING_ORG = g_MISS_NUM THEN
2894 l_Item_rec.DEFAULT_SHIPPING_ORG := null;
2895 END IF;
2896
2897 IF(p_Item_rec.RETURNABLE_FLAG IS NULL) THEN
2898 l_Item_rec.RETURNABLE_FLAG := g_Upd_Null_CHAR;
2899 ELSIF p_Item_rec.RETURNABLE_FLAG = g_MISS_CHAR THEN
2900 l_Item_rec.RETURNABLE_FLAG := null;
2901 END IF;
2902
2903 IF(p_Item_rec.RETURN_INSPECTION_REQUIREMENT IS NULL ) THEN
2904 l_Item_rec.RETURN_INSPECTION_REQUIREMENT := g_Upd_Null_NUM ;
2905 ELSIF p_Item_rec.RETURN_INSPECTION_REQUIREMENT = g_MISS_NUM THEN
2906 l_Item_rec.RETURN_INSPECTION_REQUIREMENT := null;
2907 END IF;
2908
2909 IF(p_Item_rec.OVER_SHIPMENT_TOLERANCE IS NULL ) THEN
2910 l_Item_rec.OVER_SHIPMENT_TOLERANCE := g_Upd_Null_NUM ;
2911 ELSIF p_Item_rec.OVER_SHIPMENT_TOLERANCE = g_MISS_NUM THEN
2912 l_Item_rec.OVER_SHIPMENT_TOLERANCE := null;
2913 END IF;
2914
2915 IF(p_Item_rec.UNDER_SHIPMENT_TOLERANCE IS NULL ) THEN
2916 l_Item_rec.UNDER_SHIPMENT_TOLERANCE := g_Upd_Null_NUM ;
2917 ELSIF p_Item_rec.UNDER_SHIPMENT_TOLERANCE = g_MISS_NUM THEN
2918 l_Item_rec.UNDER_SHIPMENT_TOLERANCE := null;
2919 END IF;
2920
2921 IF(p_Item_rec.OVER_RETURN_TOLERANCE IS NULL ) THEN
2922 l_Item_rec.OVER_RETURN_TOLERANCE := g_Upd_Null_NUM ;
2923 ELSIF p_Item_rec.OVER_RETURN_TOLERANCE = g_MISS_NUM THEN
2924 l_Item_rec.OVER_RETURN_TOLERANCE := null;
2925 END IF;
2926
2927 IF(p_Item_rec.UNDER_RETURN_TOLERANCE IS NULL ) THEN
2928 l_Item_rec.UNDER_RETURN_TOLERANCE := g_Upd_Null_NUM ;
2929 ELSIF p_Item_rec.UNDER_RETURN_TOLERANCE = g_MISS_NUM THEN
2930 l_Item_rec.UNDER_RETURN_TOLERANCE := null;
2931 END IF;
2932
2933 IF(p_Item_rec.INVOICEABLE_ITEM_FLAG IS NULL) THEN
2934 l_Item_rec.INVOICEABLE_ITEM_FLAG := g_Upd_Null_CHAR;
2935 ELSIF p_Item_rec.INVOICEABLE_ITEM_FLAG = g_MISS_CHAR THEN
2936 l_Item_rec.INVOICEABLE_ITEM_FLAG := null;
2937 END IF;
2938
2939 IF(p_Item_rec.INVOICE_ENABLED_FLAG IS NULL) THEN
2940 l_Item_rec.INVOICE_ENABLED_FLAG := g_Upd_Null_CHAR;
2941 ELSIF p_Item_rec.INVOICE_ENABLED_FLAG = g_MISS_CHAR THEN
2942 l_Item_rec.INVOICE_ENABLED_FLAG := null;
2943 END IF;
2944
2945 IF(p_Item_rec.ACCOUNTING_RULE_ID IS NULL ) THEN
2946 l_Item_rec.ACCOUNTING_RULE_ID := g_Upd_Null_NUM ;
2947 ELSIF p_Item_rec.ACCOUNTING_RULE_ID = g_MISS_NUM THEN
2948 l_Item_rec.ACCOUNTING_RULE_ID := null;
2949 END IF;
2950
2951 IF(p_Item_rec.INVOICING_RULE_ID IS NULL ) THEN
2952 l_Item_rec.INVOICING_RULE_ID := g_Upd_Null_NUM ;
2953 ELSIF p_Item_rec.INVOICING_RULE_ID = g_MISS_NUM THEN
2954 l_Item_rec.INVOICING_RULE_ID := null;
2955 END IF;
2956
2957 IF(p_Item_rec.TAX_CODE IS NULL) THEN
2958 l_Item_rec.TAX_CODE := g_Upd_Null_CHAR;
2959 ELSIF p_Item_rec.TAX_CODE = g_MISS_CHAR THEN
2960 l_Item_rec.TAX_CODE := null;
2961 END IF;
2962
2963 IF(p_Item_rec.SALES_ACCOUNT IS NULL ) THEN
2964 l_Item_rec.SALES_ACCOUNT := g_Upd_Null_NUM ;
2965 ELSIF p_Item_rec.SALES_ACCOUNT = g_MISS_NUM THEN
2966 l_Item_rec.SALES_ACCOUNT := null;
2967 END IF;
2968
2969 IF(p_Item_rec.PAYMENT_TERMS_ID IS NULL ) THEN
2970 l_Item_rec.PAYMENT_TERMS_ID := g_Upd_Null_NUM ;
2971 ELSIF p_Item_rec.PAYMENT_TERMS_ID = g_MISS_NUM THEN
2972 l_Item_rec.PAYMENT_TERMS_ID := null;
2973 END IF;
2974
2975 IF(p_Item_rec.COVERAGE_SCHEDULE_ID IS NULL ) THEN
2976 l_Item_rec.COVERAGE_SCHEDULE_ID := g_Upd_Null_NUM ;
2977 ELSIF p_Item_rec.COVERAGE_SCHEDULE_ID = g_MISS_NUM THEN
2978 l_Item_rec.COVERAGE_SCHEDULE_ID := null;
2979 END IF;
2980
2981 IF(p_Item_rec.SERVICE_DURATION IS NULL ) THEN
2982 l_Item_rec.SERVICE_DURATION := g_Upd_Null_NUM ;
2983 ELSIF p_Item_rec.SERVICE_DURATION = g_MISS_NUM THEN
2984 l_Item_rec.SERVICE_DURATION := null;
2985 END IF;
2986
2987 IF(p_Item_rec.SERVICE_DURATION_PERIOD_CODE IS NULL) THEN
2988 l_Item_rec.SERVICE_DURATION_PERIOD_CODE := g_Upd_Null_CHAR;
2989 ELSIF p_Item_rec.SERVICE_DURATION_PERIOD_CODE = g_MISS_CHAR THEN
2990 l_Item_rec.SERVICE_DURATION_PERIOD_CODE := null;
2991 END IF;
2992
2993 IF(p_Item_rec.SERVICEABLE_PRODUCT_FLAG IS NULL) THEN
2994 l_Item_rec.SERVICEABLE_PRODUCT_FLAG := g_Upd_Null_CHAR;
2995 ELSIF p_Item_rec.SERVICEABLE_PRODUCT_FLAG = g_MISS_CHAR THEN
2996 l_Item_rec.SERVICEABLE_PRODUCT_FLAG := null;
2997 END IF;
2998
2999 IF(p_Item_rec.SERVICE_STARTING_DELAY IS NULL ) THEN
3000 l_Item_rec.SERVICE_STARTING_DELAY := g_Upd_Null_NUM ;
3001 ELSIF p_Item_rec.SERVICE_STARTING_DELAY = g_MISS_NUM THEN
3002 l_Item_rec.SERVICE_STARTING_DELAY := null;
3003 END IF;
3004
3005 IF(p_Item_rec.MATERIAL_BILLABLE_FLAG IS NULL) THEN
3006 l_Item_rec.MATERIAL_BILLABLE_FLAG := g_Upd_Null_CHAR;
3007 ELSIF p_Item_rec.MATERIAL_BILLABLE_FLAG = g_MISS_CHAR THEN
3008 l_Item_rec.MATERIAL_BILLABLE_FLAG := null;
3009 END IF;
3010
3011 IF(p_Item_rec.SERVICEABLE_COMPONENT_FLAG IS NULL) THEN
3012 l_Item_rec.SERVICEABLE_COMPONENT_FLAG := g_Upd_Null_CHAR;
3013 ELSIF p_Item_rec.SERVICEABLE_COMPONENT_FLAG = g_MISS_CHAR THEN
3014 l_Item_rec.SERVICEABLE_COMPONENT_FLAG := null;
3015 END IF;
3016
3017 IF(p_Item_rec.PREVENTIVE_MAINTENANCE_FLAG IS NULL) THEN
3018 l_Item_rec.PREVENTIVE_MAINTENANCE_FLAG := g_Upd_Null_CHAR;
3019 ELSIF p_Item_rec.PREVENTIVE_MAINTENANCE_FLAG = g_MISS_CHAR THEN
3020 l_Item_rec.PREVENTIVE_MAINTENANCE_FLAG := null;
3021 END IF;
3022
3023 IF(p_Item_rec.PRORATE_SERVICE_FLAG IS NULL) THEN
3024 l_Item_rec.PRORATE_SERVICE_FLAG := g_Upd_Null_CHAR;
3025 ELSIF p_Item_rec.PRORATE_SERVICE_FLAG = g_MISS_CHAR THEN
3026 l_Item_rec.PRORATE_SERVICE_FLAG := null;
3027 END IF;
3028
3029 IF(p_Item_rec.WH_UPDATE_DATE = g_Null_DATE) THEN
3030 l_Item_rec.WH_UPDATE_DATE := g_Upd_Null_DATE;
3031 ELSIF p_Item_rec.WH_UPDATE_DATE = g_MISS_DATE THEN
3032 l_Item_rec.WH_UPDATE_DATE := null;
3033 END IF;
3034
3035 IF(p_Item_rec.EQUIPMENT_TYPE IS NULL ) THEN
3036 l_Item_rec.EQUIPMENT_TYPE := g_Upd_Null_NUM ;
3037 ELSIF p_Item_rec.EQUIPMENT_TYPE = g_MISS_NUM THEN
3038 l_Item_rec.EQUIPMENT_TYPE := null;
3039 END IF;
3040
3041 IF(p_Item_rec.RECOVERED_PART_DISP_CODE IS NULL) THEN
3042 l_Item_rec.RECOVERED_PART_DISP_CODE := g_Upd_Null_CHAR;
3043 ELSIF p_Item_rec.RECOVERED_PART_DISP_CODE = g_MISS_CHAR THEN
3044 l_Item_rec.RECOVERED_PART_DISP_CODE := null;
3045 END IF;
3046
3047 IF(p_Item_rec.DEFECT_TRACKING_ON_FLAG IS NULL) THEN
3048 l_Item_rec.DEFECT_TRACKING_ON_FLAG := g_Upd_Null_CHAR;
3049 ELSIF p_Item_rec.DEFECT_TRACKING_ON_FLAG = g_MISS_CHAR THEN
3050 l_Item_rec.DEFECT_TRACKING_ON_FLAG := null;
3051 END IF;
3052
3053 IF(p_Item_rec.EVENT_FLAG IS NULL) THEN
3054 l_Item_rec.EVENT_FLAG := g_Upd_Null_CHAR;
3055 ELSIF p_Item_rec.EVENT_FLAG = g_MISS_CHAR THEN
3056 l_Item_rec.EVENT_FLAG := null;
3057 END IF;
3058
3059 IF(p_Item_rec.ELECTRONIC_FLAG IS NULL) THEN
3060 l_Item_rec.ELECTRONIC_FLAG := g_Upd_Null_CHAR;
3061 ELSIF p_Item_rec.ELECTRONIC_FLAG = g_MISS_CHAR THEN
3062 l_Item_rec.ELECTRONIC_FLAG := null;
3063 END IF;
3064
3065 IF(p_Item_rec.DOWNLOADABLE_FLAG IS NULL) THEN
3066 l_Item_rec.DOWNLOADABLE_FLAG := g_Upd_Null_CHAR;
3067 ELSIF p_Item_rec.DOWNLOADABLE_FLAG = g_MISS_CHAR THEN
3068 l_Item_rec.DOWNLOADABLE_FLAG := null;
3069 END IF;
3070
3071 IF(p_Item_rec.VOL_DISCOUNT_EXEMPT_FLAG IS NULL) THEN
3072 l_Item_rec.VOL_DISCOUNT_EXEMPT_FLAG := g_Upd_Null_CHAR;
3073 ELSIF p_Item_rec.VOL_DISCOUNT_EXEMPT_FLAG = g_MISS_CHAR THEN
3074 l_Item_rec.VOL_DISCOUNT_EXEMPT_FLAG := null;
3075 END IF;
3076
3077 IF(p_Item_rec.COUPON_EXEMPT_FLAG IS NULL) THEN
3078 l_Item_rec.COUPON_EXEMPT_FLAG := g_Upd_Null_CHAR;
3079 ELSIF p_Item_rec.COUPON_EXEMPT_FLAG = g_MISS_CHAR THEN
3080 l_Item_rec.COUPON_EXEMPT_FLAG := null;
3081 END IF;
3082
3083 IF(p_Item_rec.COMMS_NL_TRACKABLE_FLAG IS NULL) THEN
3084 l_Item_rec.COMMS_NL_TRACKABLE_FLAG := g_Upd_Null_CHAR;
3085 ELSIF p_Item_rec.COMMS_NL_TRACKABLE_FLAG = g_MISS_CHAR THEN
3086 l_Item_rec.COMMS_NL_TRACKABLE_FLAG := null;
3087 END IF;
3088
3089 IF(p_Item_rec.ASSET_CREATION_CODE IS NULL) THEN
3090 l_Item_rec.ASSET_CREATION_CODE := g_Upd_Null_CHAR;
3091 ELSIF p_Item_rec.ASSET_CREATION_CODE = g_MISS_CHAR THEN
3092 l_Item_rec.ASSET_CREATION_CODE := null;
3093 END IF;
3094
3095 IF(p_Item_rec.COMMS_ACTIVATION_REQD_FLAG IS NULL) THEN
3096 l_Item_rec.COMMS_ACTIVATION_REQD_FLAG := g_Upd_Null_CHAR;
3097 ELSIF p_Item_rec.COMMS_ACTIVATION_REQD_FLAG = g_MISS_CHAR THEN
3098 l_Item_rec.COMMS_ACTIVATION_REQD_FLAG := null;
3099 END IF;
3100
3101 IF(p_Item_rec.WEB_STATUS IS NULL) THEN
3102 l_Item_rec.WEB_STATUS := g_Upd_Null_CHAR;
3103 ELSIF p_Item_rec.WEB_STATUS = g_MISS_CHAR THEN
3104 l_Item_rec.WEB_STATUS := null;
3105 END IF;
3106
3107 IF(p_Item_rec.ORDERABLE_ON_WEB_FLAG IS NULL) THEN
3108 l_Item_rec.ORDERABLE_ON_WEB_FLAG := g_Upd_Null_CHAR;
3109 ELSIF p_Item_rec.ORDERABLE_ON_WEB_FLAG = g_MISS_CHAR THEN
3110 l_Item_rec.ORDERABLE_ON_WEB_FLAG := null;
3111 END IF;
3112
3113 IF(p_Item_rec.BACK_ORDERABLE_FLAG IS NULL) THEN
3114 l_Item_rec.BACK_ORDERABLE_FLAG := g_Upd_Null_CHAR;
3115 ELSIF p_Item_rec.BACK_ORDERABLE_FLAG = g_MISS_CHAR THEN
3116 l_Item_rec.BACK_ORDERABLE_FLAG := null;
3117 END IF;
3118
3119 IF(p_Item_rec.INDIVISIBLE_FLAG IS NULL) THEN
3120 l_Item_rec.INDIVISIBLE_FLAG := g_Upd_Null_CHAR;
3121 ELSIF p_Item_rec.INDIVISIBLE_FLAG = g_MISS_CHAR THEN
3122 l_Item_rec.INDIVISIBLE_FLAG := null;
3123 END IF;
3124
3125 IF(p_Item_rec.DIMENSION_UOM_CODE IS NULL) THEN
3126 l_Item_rec.DIMENSION_UOM_CODE := g_Upd_Null_CHAR;
3127 ELSIF p_Item_rec.DIMENSION_UOM_CODE = g_MISS_CHAR THEN
3128 l_Item_rec.DIMENSION_UOM_CODE := null;
3129 END IF;
3130
3131 IF(p_Item_rec.UNIT_LENGTH IS NULL ) THEN
3132 l_Item_rec.UNIT_LENGTH := g_Upd_Null_NUM ;
3133 ELSIF p_Item_rec.UNIT_LENGTH = g_MISS_NUM THEN
3134 l_Item_rec.UNIT_LENGTH := null;
3135 END IF;
3136
3137 IF(p_Item_rec.UNIT_WIDTH IS NULL ) THEN
3138 l_Item_rec.UNIT_WIDTH := g_Upd_Null_NUM ;
3139 ELSIF p_Item_rec.UNIT_WIDTH = g_MISS_NUM THEN
3140 l_Item_rec.UNIT_WIDTH := null;
3141 END IF;
3142
3143 IF(p_Item_rec.UNIT_HEIGHT IS NULL ) THEN
3144 l_Item_rec.UNIT_HEIGHT := g_Upd_Null_NUM ;
3145 ELSIF p_Item_rec.UNIT_HEIGHT = g_MISS_NUM THEN
3146 l_Item_rec.UNIT_HEIGHT := null;
3147 END IF;
3148
3149 IF(p_Item_rec.BULK_PICKED_FLAG IS NULL) THEN
3150 l_Item_rec.BULK_PICKED_FLAG := g_Upd_Null_CHAR;
3151 ELSIF p_Item_rec.BULK_PICKED_FLAG = g_MISS_CHAR THEN
3152 l_Item_rec.BULK_PICKED_FLAG := null;
3153 END IF;
3154
3155 IF(p_Item_rec.LOT_STATUS_ENABLED IS NULL) THEN
3156 l_Item_rec.LOT_STATUS_ENABLED := g_Upd_Null_CHAR;
3157 ELSIF p_Item_rec.LOT_STATUS_ENABLED = g_MISS_CHAR THEN
3158 l_Item_rec.LOT_STATUS_ENABLED := null;
3159 END IF;
3160
3161 IF(p_Item_rec.DEFAULT_LOT_STATUS_ID IS NULL ) THEN
3162 l_Item_rec.DEFAULT_LOT_STATUS_ID := g_Upd_Null_NUM ;
3163 ELSIF p_Item_rec.DEFAULT_LOT_STATUS_ID = g_MISS_NUM THEN
3164 l_Item_rec.DEFAULT_LOT_STATUS_ID := null;
3165 END IF;
3166
3167 IF(p_Item_rec.SERIAL_STATUS_ENABLED IS NULL) THEN
3168 l_Item_rec.SERIAL_STATUS_ENABLED := g_Upd_Null_CHAR;
3169 ELSIF p_Item_rec.SERIAL_STATUS_ENABLED = g_MISS_CHAR THEN
3170 l_Item_rec.SERIAL_STATUS_ENABLED := null;
3171 END IF;
3172
3173 IF(p_Item_rec.DEFAULT_SERIAL_STATUS_ID IS NULL ) THEN
3174 l_Item_rec.DEFAULT_SERIAL_STATUS_ID := g_Upd_Null_NUM ;
3175 ELSIF p_Item_rec.DEFAULT_SERIAL_STATUS_ID = g_MISS_NUM THEN
3176 l_Item_rec.DEFAULT_SERIAL_STATUS_ID := null;
3177 END IF;
3178
3179 IF(p_Item_rec.LOT_SPLIT_ENABLED IS NULL) THEN
3180 l_Item_rec.LOT_SPLIT_ENABLED := g_Upd_Null_CHAR;
3181 ELSIF p_Item_rec.LOT_SPLIT_ENABLED = g_MISS_CHAR THEN
3182 l_Item_rec.LOT_SPLIT_ENABLED := null;
3183 END IF;
3184
3185 IF(p_Item_rec.LOT_MERGE_ENABLED IS NULL) THEN
3186 l_Item_rec.LOT_MERGE_ENABLED := g_Upd_Null_CHAR;
3187 ELSIF p_Item_rec.LOT_MERGE_ENABLED = g_MISS_CHAR THEN
3188 l_Item_rec.LOT_MERGE_ENABLED := null;
3189 END IF;
3190
3191 IF(p_Item_rec.INVENTORY_CARRY_PENALTY IS NULL ) THEN
3192 l_Item_rec.INVENTORY_CARRY_PENALTY := g_Upd_Null_NUM ;
3193 ELSIF p_Item_rec.INVENTORY_CARRY_PENALTY = g_MISS_NUM THEN
3194 l_Item_rec.INVENTORY_CARRY_PENALTY := null;
3195 END IF;
3196
3197 IF(p_Item_rec.OPERATION_SLACK_PENALTY IS NULL ) THEN
3198 l_Item_rec.OPERATION_SLACK_PENALTY := g_Upd_Null_NUM ;
3199 ELSIF p_Item_rec.OPERATION_SLACK_PENALTY = g_MISS_NUM THEN
3200 l_Item_rec.OPERATION_SLACK_PENALTY := null;
3201 END IF;
3202
3203 IF(p_Item_rec.FINANCING_ALLOWED_FLAG IS NULL) THEN
3204 l_Item_rec.FINANCING_ALLOWED_FLAG := g_Upd_Null_CHAR;
3205 ELSIF p_Item_rec.FINANCING_ALLOWED_FLAG = g_MISS_CHAR THEN
3206 l_Item_rec.FINANCING_ALLOWED_FLAG := null;
3207 END IF;
3208
3209 IF(p_Item_rec.EAM_ITEM_TYPE IS NULL ) THEN
3210 l_Item_rec.EAM_ITEM_TYPE := g_Upd_Null_NUM ;
3211 ELSIF p_Item_rec.EAM_ITEM_TYPE = g_MISS_NUM THEN
3212 l_Item_rec.EAM_ITEM_TYPE := null;
3213 END IF;
3214
3215 IF(p_Item_rec.EAM_ACTIVITY_TYPE_CODE IS NULL) THEN
3216 l_Item_rec.EAM_ACTIVITY_TYPE_CODE := g_Upd_Null_CHAR;
3217 ELSIF p_Item_rec.EAM_ACTIVITY_TYPE_CODE = g_MISS_CHAR THEN
3218 l_Item_rec.EAM_ACTIVITY_TYPE_CODE := null;
3219 END IF;
3220
3221 IF(p_Item_rec.EAM_ACTIVITY_CAUSE_CODE IS NULL) THEN
3222 l_Item_rec.EAM_ACTIVITY_CAUSE_CODE := g_Upd_Null_CHAR;
3223 ELSIF p_Item_rec.EAM_ACTIVITY_CAUSE_CODE = g_MISS_CHAR THEN
3224 l_Item_rec.EAM_ACTIVITY_CAUSE_CODE := null;
3225 END IF;
3226
3227 IF(p_Item_rec.EAM_ACT_NOTIFICATION_FLAG IS NULL) THEN
3228 l_Item_rec.EAM_ACT_NOTIFICATION_FLAG := g_Upd_Null_CHAR;
3229 ELSIF p_Item_rec.EAM_ACT_NOTIFICATION_FLAG = g_MISS_CHAR THEN
3230 l_Item_rec.EAM_ACT_NOTIFICATION_FLAG := null;
3231 END IF;
3232
3233 IF(p_Item_rec.EAM_ACT_SHUTDOWN_STATUS IS NULL) THEN
3234 l_Item_rec.EAM_ACT_SHUTDOWN_STATUS := g_Upd_Null_CHAR;
3235 ELSIF p_Item_rec.EAM_ACT_SHUTDOWN_STATUS = g_MISS_CHAR THEN
3236 l_Item_rec.EAM_ACT_SHUTDOWN_STATUS := null;
3237 END IF;
3238
3239 IF(p_Item_rec.DUAL_UOM_CONTROL IS NULL ) THEN
3240 l_Item_rec.DUAL_UOM_CONTROL := g_Upd_Null_NUM ;
3241 ELSIF p_Item_rec.DUAL_UOM_CONTROL = g_MISS_NUM THEN
3242 l_Item_rec.DUAL_UOM_CONTROL := null;
3243 END IF;
3244
3245 IF(p_Item_rec.SECONDARY_UOM_CODE IS NULL) THEN
3246 l_Item_rec.SECONDARY_UOM_CODE := g_Upd_Null_CHAR;
3247 ELSIF p_Item_rec.SECONDARY_UOM_CODE = g_MISS_CHAR THEN
3248 l_Item_rec.SECONDARY_UOM_CODE := null;
3249 END IF;
3250
3251 IF(p_Item_rec.DUAL_UOM_DEVIATION_HIGH IS NULL ) THEN
3252 l_Item_rec.DUAL_UOM_DEVIATION_HIGH := g_Upd_Null_NUM ;
3253 ELSIF p_Item_rec.DUAL_UOM_DEVIATION_HIGH = g_MISS_NUM THEN
3254 l_Item_rec.DUAL_UOM_DEVIATION_HIGH := null;
3255 END IF;
3256
3257 IF(p_Item_rec.DUAL_UOM_DEVIATION_LOW IS NULL ) THEN
3258 l_Item_rec.DUAL_UOM_DEVIATION_LOW := g_Upd_Null_NUM ;
3259 ELSIF p_Item_rec.DUAL_UOM_DEVIATION_LOW = g_MISS_NUM THEN
3260 l_Item_rec.DUAL_UOM_DEVIATION_LOW := null;
3261 END IF;
3262
3263 IF(p_Item_rec.SUBSCRIPTION_DEPEND_FLAG IS NULL) THEN
3264 l_Item_rec.SUBSCRIPTION_DEPEND_FLAG := g_Upd_Null_CHAR;
3265 ELSIF p_Item_rec.SUBSCRIPTION_DEPEND_FLAG = g_MISS_CHAR THEN
3266 l_Item_rec.SUBSCRIPTION_DEPEND_FLAG := null;
3267 END IF;
3268
3269 IF(p_Item_rec.SERV_REQ_ENABLED_CODE IS NULL) THEN
3270 l_Item_rec.SERV_REQ_ENABLED_CODE := g_Upd_Null_CHAR;
3271 ELSIF p_Item_rec.SERV_REQ_ENABLED_CODE = g_MISS_CHAR THEN
3272 l_Item_rec.SERV_REQ_ENABLED_CODE := null;
3273 END IF;
3274
3275 IF(p_Item_rec.SERV_BILLING_ENABLED_FLAG IS NULL) THEN
3276 l_Item_rec.SERV_BILLING_ENABLED_FLAG := g_Upd_Null_CHAR;
3277 ELSIF p_Item_rec.SERV_BILLING_ENABLED_FLAG = g_MISS_CHAR THEN
3278 l_Item_rec.SERV_BILLING_ENABLED_FLAG := null;
3279 END IF;
3280
3281 IF(p_Item_rec.SERV_IMPORTANCE_LEVEL IS NULL ) THEN
3282 l_Item_rec.SERV_IMPORTANCE_LEVEL := g_Upd_Null_NUM ;
3283 ELSIF p_Item_rec.SERV_IMPORTANCE_LEVEL = g_MISS_NUM THEN
3284 l_Item_rec.SERV_IMPORTANCE_LEVEL := null;
3285 END IF;
3286
3287 IF(p_Item_rec.PLANNED_INV_POINT_FLAG IS NULL) THEN
3288 l_Item_rec.PLANNED_INV_POINT_FLAG := g_Upd_Null_CHAR;
3289 ELSIF p_Item_rec.PLANNED_INV_POINT_FLAG = g_MISS_CHAR THEN
3290 l_Item_rec.PLANNED_INV_POINT_FLAG := null;
3291 END IF;
3292
3293 IF(p_Item_rec.LOT_TRANSLATE_ENABLED IS NULL) THEN
3294 l_Item_rec.LOT_TRANSLATE_ENABLED := g_Upd_Null_CHAR;
3295 ELSIF p_Item_rec.LOT_TRANSLATE_ENABLED = g_MISS_CHAR THEN
3296 l_Item_rec.LOT_TRANSLATE_ENABLED := null;
3297 END IF;
3298
3299 IF(p_Item_rec.DEFAULT_SO_SOURCE_TYPE IS NULL) THEN
3300 l_Item_rec.DEFAULT_SO_SOURCE_TYPE := g_Upd_Null_CHAR;
3301 ELSIF p_Item_rec.DEFAULT_SO_SOURCE_TYPE = g_MISS_CHAR THEN
3302 l_Item_rec.DEFAULT_SO_SOURCE_TYPE := null;
3303 END IF;
3304
3305 IF(p_Item_rec.CREATE_SUPPLY_FLAG IS NULL) THEN
3306 l_Item_rec.CREATE_SUPPLY_FLAG := g_Upd_Null_CHAR;
3307 ELSIF p_Item_rec.CREATE_SUPPLY_FLAG = g_MISS_CHAR THEN
3308 l_Item_rec.CREATE_SUPPLY_FLAG := null;
3309 END IF;
3310
3311 IF(p_Item_rec.SUBSTITUTION_WINDOW_CODE IS NULL ) THEN
3312 l_Item_rec.SUBSTITUTION_WINDOW_CODE := g_Upd_Null_NUM ;
3313 ELSIF p_Item_rec.SUBSTITUTION_WINDOW_CODE = g_MISS_NUM THEN
3314 l_Item_rec.SUBSTITUTION_WINDOW_CODE := null;
3315 END IF;
3316
3317 IF(p_Item_rec.SUBSTITUTION_WINDOW_DAYS IS NULL ) THEN
3318 l_Item_rec.SUBSTITUTION_WINDOW_DAYS := g_Upd_Null_NUM ;
3319 ELSIF p_Item_rec.SUBSTITUTION_WINDOW_DAYS = g_MISS_NUM THEN
3320 l_Item_rec.SUBSTITUTION_WINDOW_DAYS := null;
3321 END IF;
3322
3323 IF(p_Item_rec.LOT_SUBSTITUTION_ENABLED IS NULL) THEN
3324 l_Item_rec.LOT_SUBSTITUTION_ENABLED := g_Upd_Null_CHAR;
3325 ELSIF p_Item_rec.LOT_SUBSTITUTION_ENABLED = g_MISS_CHAR THEN
3326 l_Item_rec.LOT_SUBSTITUTION_ENABLED := null;
3327 END IF;
3328
3329 IF(p_Item_rec.MINIMUM_LICENSE_QUANTITY = g_MISS_NUM ) THEN
3330 l_Item_rec.MINIMUM_LICENSE_QUANTITY := g_Upd_Null_NUM ;
3331 ELSIF p_Item_rec.MINIMUM_LICENSE_QUANTITY = g_MISS_NUM THEN
3332 l_Item_rec.MINIMUM_LICENSE_QUANTITY := null;
3333 END IF;
3334
3335 IF(p_Item_rec.EAM_ACTIVITY_SOURCE_CODE IS NULL) THEN
3336 l_Item_rec.EAM_ACTIVITY_SOURCE_CODE := g_Upd_Null_CHAR;
3337 ELSIF p_Item_rec.EAM_ACTIVITY_SOURCE_CODE = g_MISS_CHAR THEN
3338 l_Item_rec.EAM_ACTIVITY_SOURCE_CODE := null;
3339 END IF;
3340
3341 IF(p_Item_rec.IB_ITEM_INSTANCE_CLASS IS NULL) THEN
3342 l_Item_rec.IB_ITEM_INSTANCE_CLASS := g_Upd_Null_CHAR;
3343 ELSIF p_Item_rec.IB_ITEM_INSTANCE_CLASS = g_MISS_CHAR THEN
3344 l_Item_rec.IB_ITEM_INSTANCE_CLASS := null;
3345 END IF;
3346
3347 IF(p_Item_rec.CONFIG_MODEL_TYPE IS NULL) THEN
3348 l_Item_rec.CONFIG_MODEL_TYPE := g_Upd_Null_CHAR;
3349 ELSIF p_Item_rec.CONFIG_MODEL_TYPE = g_MISS_CHAR THEN
3350 l_Item_rec.CONFIG_MODEL_TYPE := null;
3351 END IF;
3352
3353 IF(p_Item_rec.TRACKING_QUANTITY_IND IS NULL) THEN
3354 l_Item_rec.TRACKING_QUANTITY_IND := g_Upd_Null_CHAR;
3355 ELSIF p_Item_rec.TRACKING_QUANTITY_IND = g_MISS_CHAR THEN
3356 l_Item_rec.TRACKING_QUANTITY_IND := null;
3357 END IF;
3358
3359 IF(p_Item_rec.ONT_PRICING_QTY_SOURCE IS NULL) THEN
3360 l_Item_rec.ONT_PRICING_QTY_SOURCE := g_Upd_Null_CHAR;
3361 ELSIF p_Item_rec.ONT_PRICING_QTY_SOURCE = g_MISS_CHAR THEN
3362 l_Item_rec.ONT_PRICING_QTY_SOURCE := null;
3363 END IF;
3364
3365 IF(p_Item_rec.SECONDARY_DEFAULT_IND IS NULL) THEN
3366 l_Item_rec.SECONDARY_DEFAULT_IND := g_Upd_Null_CHAR;
3367 ELSIF p_Item_rec.SECONDARY_DEFAULT_IND = g_MISS_CHAR THEN
3368 l_Item_rec.SECONDARY_DEFAULT_IND := null;
3369 END IF;
3370
3371 IF(p_Item_rec.CONFIG_ORGS IS NULL) THEN
3372 l_Item_rec.CONFIG_ORGS := g_Upd_Null_CHAR;
3373 ELSIF p_Item_rec.CONFIG_ORGS = g_MISS_CHAR THEN
3374 l_Item_rec.CONFIG_ORGS := null;
3375 END IF;
3376
3377 IF(p_Item_rec.CONFIG_MATCH IS NULL) THEN
3378 l_Item_rec.CONFIG_MATCH := g_Upd_Null_CHAR;
3379 ELSIF p_Item_rec.CONFIG_MATCH = g_MISS_CHAR THEN
3380 l_Item_rec.CONFIG_MATCH := null;
3381 END IF;
3382
3383 IF(p_Item_rec.ATTRIBUTE_CATEGORY IS NULL) THEN
3384 l_Item_rec.ATTRIBUTE_CATEGORY := g_Upd_Null_CHAR;
3385 ELSIF p_Item_rec.ATTRIBUTE_CATEGORY = g_MISS_CHAR THEN
3386 l_Item_rec.ATTRIBUTE_CATEGORY := null;
3387 END IF;
3388
3389 IF(p_Item_rec.ATTRIBUTE1 IS NULL) THEN
3390 l_Item_rec.ATTRIBUTE1 := g_Upd_Null_CHAR;
3391 ELSIF p_Item_rec.ATTRIBUTE1 = g_MISS_CHAR THEN
3392 l_Item_rec.ATTRIBUTE1 := null;
3393 END IF;
3394
3395 IF(p_Item_rec.ATTRIBUTE2 IS NULL) THEN
3396 l_Item_rec.ATTRIBUTE2 := g_Upd_Null_CHAR;
3397 ELSIF p_Item_rec.ATTRIBUTE2 = g_MISS_CHAR THEN
3398 l_Item_rec.ATTRIBUTE2 := null;
3399 END IF;
3400
3401 IF(p_Item_rec.ATTRIBUTE3 IS NULL) THEN
3402 l_Item_rec.ATTRIBUTE3 := g_Upd_Null_CHAR;
3403 ELSIF p_Item_rec.ATTRIBUTE3 = g_MISS_CHAR THEN
3404 l_Item_rec.ATTRIBUTE3 := null;
3405 END IF;
3406
3407 IF(p_Item_rec.ATTRIBUTE4 IS NULL) THEN
3408 l_Item_rec.ATTRIBUTE4 := g_Upd_Null_CHAR;
3409 ELSIF p_Item_rec.ATTRIBUTE4 = g_MISS_CHAR THEN
3410 l_Item_rec.ATTRIBUTE4 := null;
3411 END IF;
3412
3413 IF(p_Item_rec.ATTRIBUTE5 IS NULL) THEN
3414 l_Item_rec.ATTRIBUTE5 := g_Upd_Null_CHAR;
3415 ELSIF p_Item_rec.ATTRIBUTE5 = g_MISS_CHAR THEN
3416 l_Item_rec.ATTRIBUTE5 := null;
3417 END IF;
3418
3419 IF(p_Item_rec.ATTRIBUTE6 IS NULL) THEN
3420 l_Item_rec.ATTRIBUTE6 := g_Upd_Null_CHAR;
3421 ELSIF p_Item_rec.ATTRIBUTE6 = g_MISS_CHAR THEN
3422 l_Item_rec.ATTRIBUTE6 := null;
3423 END IF;
3424
3425 IF(p_Item_rec.ATTRIBUTE7 IS NULL) THEN
3426 l_Item_rec.ATTRIBUTE7 := g_Upd_Null_CHAR;
3427 ELSIF p_Item_rec.ATTRIBUTE7 = g_MISS_CHAR THEN
3428 l_Item_rec.ATTRIBUTE7 := null;
3429 END IF;
3430
3431 IF(p_Item_rec.ATTRIBUTE8 IS NULL) THEN
3432 l_Item_rec.ATTRIBUTE8 := g_Upd_Null_CHAR;
3433 ELSIF p_Item_rec.ATTRIBUTE8 = g_MISS_CHAR THEN
3434 l_Item_rec.ATTRIBUTE8 := null;
3435 END IF;
3436
3437 IF(p_Item_rec.ATTRIBUTE9 IS NULL) THEN
3438 l_Item_rec.ATTRIBUTE9 := g_Upd_Null_CHAR;
3439 ELSIF p_Item_rec.ATTRIBUTE9 = g_MISS_CHAR THEN
3440 l_Item_rec.ATTRIBUTE9 := null;
3441 END IF;
3442
3443 IF(p_Item_rec.ATTRIBUTE10 IS NULL) THEN
3444 l_Item_rec.ATTRIBUTE10 := g_Upd_Null_CHAR;
3445 ELSIF p_Item_rec.ATTRIBUTE10 = g_MISS_CHAR THEN
3446 l_Item_rec.ATTRIBUTE10 := null;
3447 END IF;
3448
3449 IF(p_Item_rec.ATTRIBUTE11 IS NULL) THEN
3450 l_Item_rec.ATTRIBUTE11 := g_Upd_Null_CHAR;
3451 ELSIF p_Item_rec.ATTRIBUTE11 = g_MISS_CHAR THEN
3452 l_Item_rec.ATTRIBUTE11 := null;
3453 END IF;
3454
3455 IF(p_Item_rec.ATTRIBUTE12 IS NULL) THEN
3456 l_Item_rec.ATTRIBUTE12 := g_Upd_Null_CHAR;
3457 ELSIF p_Item_rec.ATTRIBUTE12 = g_MISS_CHAR THEN
3458 l_Item_rec.ATTRIBUTE12 := null;
3459 END IF;
3460
3461 IF(p_Item_rec.ATTRIBUTE13 IS NULL) THEN
3462 l_Item_rec.ATTRIBUTE13 := g_Upd_Null_CHAR;
3463 ELSIF p_Item_rec.ATTRIBUTE13 = g_MISS_CHAR THEN
3464 l_Item_rec.ATTRIBUTE13 := null;
3465 END IF;
3466
3467 IF(p_Item_rec.ATTRIBUTE14 IS NULL) THEN
3468 l_Item_rec.ATTRIBUTE14 := g_Upd_Null_CHAR;
3469 ELSIF p_Item_rec.ATTRIBUTE14 = g_MISS_CHAR THEN
3470 l_Item_rec.ATTRIBUTE14 := null;
3471 END IF;
3472
3473 IF(p_Item_rec.ATTRIBUTE15 IS NULL) THEN
3474 l_Item_rec.ATTRIBUTE15 := g_Upd_Null_CHAR;
3475 ELSIF p_Item_rec.ATTRIBUTE15 = g_MISS_CHAR THEN
3476 l_Item_rec.ATTRIBUTE15 := null;
3477 END IF;
3478
3479 IF(p_Item_rec.ATTRIBUTE16 IS NULL) THEN
3480 l_Item_rec.ATTRIBUTE16 := g_Upd_Null_CHAR;
3481 ELSIF p_Item_rec.ATTRIBUTE16 = g_MISS_CHAR THEN
3482 l_Item_rec.ATTRIBUTE16 := null;
3483 END IF;
3484
3485 IF(p_Item_rec.ATTRIBUTE17 IS NULL) THEN
3486 l_Item_rec.ATTRIBUTE17 := g_Upd_Null_CHAR;
3487 ELSIF p_Item_rec.ATTRIBUTE17 = g_MISS_CHAR THEN
3488 l_Item_rec.ATTRIBUTE17 := null;
3489 END IF;
3490
3491 IF(p_Item_rec.ATTRIBUTE18 IS NULL) THEN
3492 l_Item_rec.ATTRIBUTE18 := g_Upd_Null_CHAR;
3493 ELSIF p_Item_rec.ATTRIBUTE18 = g_MISS_CHAR THEN
3494 l_Item_rec.ATTRIBUTE18 := null;
3495 END IF;
3496
3497 IF(p_Item_rec.ATTRIBUTE19 IS NULL) THEN
3498 l_Item_rec.ATTRIBUTE19 := g_Upd_Null_CHAR;
3499 ELSIF p_Item_rec.ATTRIBUTE19 = g_MISS_CHAR THEN
3500 l_Item_rec.ATTRIBUTE19 := null;
3501 END IF;
3502
3503 IF(p_Item_rec.ATTRIBUTE20 IS NULL) THEN
3504 l_Item_rec.ATTRIBUTE20 := g_Upd_Null_CHAR;
3505 ELSIF p_Item_rec.ATTRIBUTE20 = g_MISS_CHAR THEN
3506 l_Item_rec.ATTRIBUTE20 := null;
3507 END IF;
3508
3509 IF(p_Item_rec.ATTRIBUTE21 IS NULL) THEN
3510 l_Item_rec.ATTRIBUTE21 := g_Upd_Null_CHAR;
3511 ELSIF p_Item_rec.ATTRIBUTE21 = g_MISS_CHAR THEN
3512 l_Item_rec.ATTRIBUTE21 := null;
3513 END IF;
3514
3515 IF(p_Item_rec.ATTRIBUTE22 IS NULL) THEN
3516 l_Item_rec.ATTRIBUTE22 := g_Upd_Null_CHAR;
3517 ELSIF p_Item_rec.ATTRIBUTE22 = g_MISS_CHAR THEN
3518 l_Item_rec.ATTRIBUTE22 := null;
3519 END IF;
3520
3521 IF(p_Item_rec.ATTRIBUTE23 IS NULL) THEN
3522 l_Item_rec.ATTRIBUTE23 := g_Upd_Null_CHAR;
3523 ELSIF p_Item_rec.ATTRIBUTE23 = g_MISS_CHAR THEN
3524 l_Item_rec.ATTRIBUTE23 := null;
3525 END IF;
3526
3527 IF(p_Item_rec.ATTRIBUTE24 IS NULL) THEN
3528 l_Item_rec.ATTRIBUTE24 := g_Upd_Null_CHAR;
3529 ELSIF p_Item_rec.ATTRIBUTE24 = g_MISS_CHAR THEN
3530 l_Item_rec.ATTRIBUTE24 := null;
3531 END IF;
3532
3533 IF(p_Item_rec.ATTRIBUTE25 IS NULL) THEN
3534 l_Item_rec.ATTRIBUTE25 := g_Upd_Null_CHAR;
3535 ELSIF p_Item_rec.ATTRIBUTE25 = g_MISS_CHAR THEN
3536 l_Item_rec.ATTRIBUTE25 := null;
3537 END IF;
3538
3539 IF(p_Item_rec.ATTRIBUTE26 IS NULL) THEN
3540 l_Item_rec.ATTRIBUTE26 := g_Upd_Null_CHAR;
3541 ELSIF p_Item_rec.ATTRIBUTE26 = g_MISS_CHAR THEN
3542 l_Item_rec.ATTRIBUTE26 := null;
3543 END IF;
3544
3545 IF(p_Item_rec.ATTRIBUTE27 IS NULL) THEN
3546 l_Item_rec.ATTRIBUTE27 := g_Upd_Null_CHAR;
3547 ELSIF p_Item_rec.ATTRIBUTE27 = g_MISS_CHAR THEN
3548 l_Item_rec.ATTRIBUTE27 := null;
3549 END IF;
3550
3551 IF(p_Item_rec.ATTRIBUTE28 IS NULL) THEN
3552 l_Item_rec.ATTRIBUTE28 := g_Upd_Null_CHAR;
3553 ELSIF p_Item_rec.ATTRIBUTE28 = g_MISS_CHAR THEN
3554 l_Item_rec.ATTRIBUTE28 := null;
3555 END IF;
3556
3557 IF(p_Item_rec.ATTRIBUTE29 IS NULL) THEN
3558 l_Item_rec.ATTRIBUTE29 := g_Upd_Null_CHAR;
3559 ELSIF p_Item_rec.ATTRIBUTE29 = g_MISS_CHAR THEN
3560 l_Item_rec.ATTRIBUTE29 := null;
3561 END IF;
3562
3563 IF(p_Item_rec.ATTRIBUTE30 IS NULL) THEN
3564 l_Item_rec.ATTRIBUTE30 := g_Upd_Null_CHAR;
3565 ELSIF p_Item_rec.ATTRIBUTE30 = g_MISS_CHAR THEN
3566 l_Item_rec.ATTRIBUTE30 := null;
3567 END IF;
3568
3569 IF(p_Item_rec.GLOBAL_ATTRIBUTE_CATEGORY IS NULL) THEN
3570 l_Item_rec.GLOBAL_ATTRIBUTE_CATEGORY := g_Upd_Null_CHAR;
3571 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE_CATEGORY = g_MISS_CHAR THEN
3572 l_Item_rec.GLOBAL_ATTRIBUTE_CATEGORY := null;
3573 END IF;
3574
3575 IF(p_Item_rec.GLOBAL_ATTRIBUTE1 IS NULL) THEN
3576 l_Item_rec.GLOBAL_ATTRIBUTE1 := g_Upd_Null_CHAR;
3577 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE1 = g_MISS_CHAR THEN
3578 l_Item_rec.GLOBAL_ATTRIBUTE1 := null;
3579 END IF;
3580
3581 IF(p_Item_rec.GLOBAL_ATTRIBUTE2 IS NULL) THEN
3582 l_Item_rec.GLOBAL_ATTRIBUTE2 := g_Upd_Null_CHAR;
3583 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE2 = g_MISS_CHAR THEN
3584 l_Item_rec.GLOBAL_ATTRIBUTE2 := null;
3585 END IF;
3586
3587 IF(p_Item_rec.GLOBAL_ATTRIBUTE3 IS NULL) THEN
3588 l_Item_rec.GLOBAL_ATTRIBUTE3 := g_Upd_Null_CHAR;
3589 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE3 = g_MISS_CHAR THEN
3590 l_Item_rec.GLOBAL_ATTRIBUTE3 := null;
3591 END IF;
3592
3593 IF(p_Item_rec.GLOBAL_ATTRIBUTE4 IS NULL) THEN
3594 l_Item_rec.GLOBAL_ATTRIBUTE4 := g_Upd_Null_CHAR;
3595 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE4 = g_MISS_CHAR THEN
3596 l_Item_rec.GLOBAL_ATTRIBUTE4 := null;
3597 END IF;
3598
3599 IF(p_Item_rec.GLOBAL_ATTRIBUTE5 IS NULL) THEN
3600 l_Item_rec.GLOBAL_ATTRIBUTE5 := g_Upd_Null_CHAR;
3601 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE5 = g_MISS_CHAR THEN
3602 l_Item_rec.GLOBAL_ATTRIBUTE5 := null;
3603 END IF;
3604
3605 IF(p_Item_rec.GLOBAL_ATTRIBUTE6 IS NULL) THEN
3606 l_Item_rec.GLOBAL_ATTRIBUTE6 := g_Upd_Null_CHAR;
3607 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE6 = g_MISS_CHAR THEN
3608 l_Item_rec.GLOBAL_ATTRIBUTE6 := null;
3609 END IF;
3610
3611 IF(p_Item_rec.GLOBAL_ATTRIBUTE7 IS NULL) THEN
3612 l_Item_rec.GLOBAL_ATTRIBUTE7 := g_Upd_Null_CHAR;
3613 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE7 = g_MISS_CHAR THEN
3614 l_Item_rec.GLOBAL_ATTRIBUTE7 := null;
3615 END IF;
3616
3617 IF(p_Item_rec.GLOBAL_ATTRIBUTE8 IS NULL) THEN
3618 l_Item_rec.GLOBAL_ATTRIBUTE8 := g_Upd_Null_CHAR;
3619 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE8 = g_MISS_CHAR THEN
3620 l_Item_rec.GLOBAL_ATTRIBUTE8 := null;
3621 END IF;
3622
3623 IF(p_Item_rec.GLOBAL_ATTRIBUTE9 IS NULL) THEN
3624 l_Item_rec.GLOBAL_ATTRIBUTE9 := g_Upd_Null_CHAR;
3625 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE9 = g_MISS_CHAR THEN
3626 l_Item_rec.GLOBAL_ATTRIBUTE9 := null;
3627 END IF;
3628
3629 IF(p_Item_rec.GLOBAL_ATTRIBUTE10 IS NULL) THEN
3630 l_Item_rec.GLOBAL_ATTRIBUTE10 := g_Upd_Null_CHAR;
3631 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE10 = g_MISS_CHAR THEN
3632 l_Item_rec.GLOBAL_ATTRIBUTE10 := null;
3633 END IF;
3634
3635 IF(p_Item_rec.GLOBAL_ATTRIBUTE11 IS NULL) THEN
3636 l_Item_rec.GLOBAL_ATTRIBUTE11 := g_Upd_Null_CHAR;
3637 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE11 = g_MISS_CHAR THEN
3638 l_Item_rec.GLOBAL_ATTRIBUTE11 := null;
3639 END IF;
3640
3641 IF(p_Item_rec.GLOBAL_ATTRIBUTE12 IS NULL) THEN
3642 l_Item_rec.GLOBAL_ATTRIBUTE12 := g_Upd_Null_CHAR;
3643 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE12 = g_MISS_CHAR THEN
3644 l_Item_rec.GLOBAL_ATTRIBUTE12 := null;
3645 END IF;
3646
3647 IF(p_Item_rec.GLOBAL_ATTRIBUTE13 IS NULL) THEN
3648 l_Item_rec.GLOBAL_ATTRIBUTE13 := g_Upd_Null_CHAR;
3649 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE13 = g_MISS_CHAR THEN
3650 l_Item_rec.GLOBAL_ATTRIBUTE13 := null;
3651 END IF;
3652
3653 IF(p_Item_rec.GLOBAL_ATTRIBUTE14 IS NULL) THEN
3654 l_Item_rec.GLOBAL_ATTRIBUTE14 := g_Upd_Null_CHAR;
3655 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE14 = g_MISS_CHAR THEN
3656 l_Item_rec.GLOBAL_ATTRIBUTE14 := null;
3657 END IF;
3658
3659 IF(p_Item_rec.GLOBAL_ATTRIBUTE15 IS NULL) THEN
3660 l_Item_rec.GLOBAL_ATTRIBUTE15 := g_Upd_Null_CHAR;
3661 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE15 = g_MISS_CHAR THEN
3662 l_Item_rec.GLOBAL_ATTRIBUTE15 := null;
3663 END IF;
3664
3665 IF(p_Item_rec.GLOBAL_ATTRIBUTE16 IS NULL) THEN
3666 l_Item_rec.GLOBAL_ATTRIBUTE16 := g_Upd_Null_CHAR;
3667 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE16 = g_MISS_CHAR THEN
3668 l_Item_rec.GLOBAL_ATTRIBUTE16 := null;
3669 END IF;
3670
3671 IF(p_Item_rec.GLOBAL_ATTRIBUTE17 IS NULL) THEN
3672 l_Item_rec.GLOBAL_ATTRIBUTE17 := g_Upd_Null_CHAR;
3673 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE17 = g_MISS_CHAR THEN
3674 l_Item_rec.GLOBAL_ATTRIBUTE17 := null;
3675 END IF;
3676
3677 IF(p_Item_rec.GLOBAL_ATTRIBUTE18 IS NULL) THEN
3678 l_Item_rec.GLOBAL_ATTRIBUTE18 := g_Upd_Null_CHAR;
3679 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE18 = g_MISS_CHAR THEN
3680 l_Item_rec.GLOBAL_ATTRIBUTE18 := null;
3681 END IF;
3682
3683 IF(p_Item_rec.GLOBAL_ATTRIBUTE19 IS NULL) THEN
3684 l_Item_rec.GLOBAL_ATTRIBUTE19 := g_Upd_Null_CHAR;
3685 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE19 = g_MISS_CHAR THEN
3686 l_Item_rec.GLOBAL_ATTRIBUTE19 := null;
3687 END IF;
3688
3689 IF(p_Item_rec.GLOBAL_ATTRIBUTE20 IS NULL) THEN
3690 l_Item_rec.GLOBAL_ATTRIBUTE20 := g_Upd_Null_CHAR;
3691 ELSIF p_Item_rec.GLOBAL_ATTRIBUTE20 = g_MISS_CHAR THEN
3692 l_Item_rec.GLOBAL_ATTRIBUTE20 := null;
3693 END IF;
3694
3695 IF(p_Item_rec.VMI_MINIMUM_UNITS IS NULL ) THEN
3696 l_Item_rec.VMI_MINIMUM_UNITS := g_Upd_Null_NUM ;
3697 ELSIF p_Item_rec.VMI_MINIMUM_UNITS = g_MISS_NUM THEN
3698 l_Item_rec.VMI_MINIMUM_UNITS := null;
3699 END IF;
3700
3701 IF(p_Item_rec.VMI_MINIMUM_DAYS IS NULL ) THEN
3702 l_Item_rec.VMI_MINIMUM_DAYS := g_Upd_Null_NUM ;
3703 ELSIF p_Item_rec.VMI_MINIMUM_DAYS = g_MISS_NUM THEN
3704 l_Item_rec.VMI_MINIMUM_DAYS := null;
3705 END IF;
3706
3707 IF(p_Item_rec.VMI_MAXIMUM_UNITS IS NULL ) THEN
3708 l_Item_rec.VMI_MAXIMUM_UNITS := g_Upd_Null_NUM ;
3709 ELSIF p_Item_rec.VMI_MAXIMUM_UNITS = g_MISS_NUM THEN
3710 l_Item_rec.VMI_MAXIMUM_UNITS := null;
3711 END IF;
3712
3713 IF(p_Item_rec.VMI_MAXIMUM_DAYS IS NULL ) THEN
3714 l_Item_rec.VMI_MAXIMUM_DAYS := g_Upd_Null_NUM ;
3715 ELSIF p_Item_rec.VMI_MAXIMUM_DAYS = g_MISS_NUM THEN
3716 l_Item_rec.VMI_MAXIMUM_DAYS := null;
3717 END IF;
3718
3719 IF(p_Item_rec.VMI_FIXED_ORDER_QUANTITY IS NULL ) THEN
3720 l_Item_rec.VMI_FIXED_ORDER_QUANTITY := g_Upd_Null_NUM ;
3721 ELSIF p_Item_rec.VMI_FIXED_ORDER_QUANTITY = g_MISS_NUM THEN
3722 l_Item_rec.VMI_FIXED_ORDER_QUANTITY := null;
3723 END IF;
3724
3725 IF(p_Item_rec.SO_AUTHORIZATION_FLAG IS NULL ) THEN
3726 l_Item_rec.SO_AUTHORIZATION_FLAG := g_Upd_Null_NUM ;
3727 ELSIF p_Item_rec.SO_AUTHORIZATION_FLAG = g_MISS_NUM THEN
3728 l_Item_rec.SO_AUTHORIZATION_FLAG := null;
3729 END IF;
3730
3731 IF(p_Item_rec.CONSIGNED_FLAG IS NULL ) THEN
3732 l_Item_rec.CONSIGNED_FLAG := g_Upd_Null_NUM ;
3733 ELSIF p_Item_rec.CONSIGNED_FLAG = g_MISS_NUM THEN
3734 l_Item_rec.CONSIGNED_FLAG := null;
3735 END IF;
3736
3737 IF(p_Item_rec.ASN_AUTOEXPIRE_FLAG IS NULL ) THEN
3738 l_Item_rec.ASN_AUTOEXPIRE_FLAG := g_Upd_Null_NUM ;
3739 ELSIF p_Item_rec.ASN_AUTOEXPIRE_FLAG = g_MISS_NUM THEN
3740 l_Item_rec.ASN_AUTOEXPIRE_FLAG := null;
3741 END IF;
3742
3743 IF(p_Item_rec.VMI_FORECAST_TYPE IS NULL ) THEN
3744 l_Item_rec.VMI_FORECAST_TYPE := g_Upd_Null_NUM ;
3745 ELSIF p_Item_rec.VMI_FORECAST_TYPE = g_MISS_NUM THEN
3746 l_Item_rec.VMI_FORECAST_TYPE := null;
3747 END IF;
3748
3749 IF(p_Item_rec.FORECAST_HORIZON IS NULL ) THEN
3750 l_Item_rec.FORECAST_HORIZON := g_Upd_Null_NUM ;
3751 ELSIF p_Item_rec.FORECAST_HORIZON = g_MISS_NUM THEN
3752 l_Item_rec.FORECAST_HORIZON := null;
3753 END IF;
3754
3755 IF(p_Item_rec.EXCLUDE_FROM_BUDGET_FLAG IS NULL ) THEN
3756 l_Item_rec.EXCLUDE_FROM_BUDGET_FLAG := g_Upd_Null_NUM ;
3757 ELSIF p_Item_rec.EXCLUDE_FROM_BUDGET_FLAG = g_MISS_NUM THEN
3758 l_Item_rec.EXCLUDE_FROM_BUDGET_FLAG := null;
3759 END IF;
3760
3761 IF(p_Item_rec.DAYS_TGT_INV_SUPPLY IS NULL ) THEN
3762 l_Item_rec.DAYS_TGT_INV_SUPPLY := g_Upd_Null_NUM ;
3763 ELSIF p_Item_rec.DAYS_TGT_INV_SUPPLY = g_MISS_NUM THEN
3764 l_Item_rec.DAYS_TGT_INV_SUPPLY := null;
3765 END IF;
3766
3767 IF(p_Item_rec.DAYS_TGT_INV_WINDOW IS NULL ) THEN
3768 l_Item_rec.DAYS_TGT_INV_WINDOW := g_Upd_Null_NUM ;
3769 ELSIF p_Item_rec.DAYS_TGT_INV_WINDOW = g_MISS_NUM THEN
3770 l_Item_rec.DAYS_TGT_INV_WINDOW := null;
3771 END IF;
3772
3773 IF(p_Item_rec.DAYS_MAX_INV_SUPPLY IS NULL ) THEN
3774 l_Item_rec.DAYS_MAX_INV_SUPPLY := g_Upd_Null_NUM ;
3775 ELSIF p_Item_rec.DAYS_MAX_INV_SUPPLY = g_MISS_NUM THEN
3776 l_Item_rec.DAYS_MAX_INV_SUPPLY := null;
3777 END IF;
3778
3779 IF(p_Item_rec.DAYS_MAX_INV_WINDOW IS NULL ) THEN
3780 l_Item_rec.DAYS_MAX_INV_WINDOW := g_Upd_Null_NUM ;
3781 ELSIF p_Item_rec.DAYS_MAX_INV_WINDOW = g_MISS_NUM THEN
3782 l_Item_rec.DAYS_MAX_INV_WINDOW := null;
3783 END IF;
3784
3785 IF(p_Item_rec.DRP_PLANNED_FLAG IS NULL ) THEN
3786 l_Item_rec.DRP_PLANNED_FLAG := g_Upd_Null_NUM ;
3787 ELSIF p_Item_rec.DRP_PLANNED_FLAG = g_MISS_NUM THEN
3788 l_Item_rec.DRP_PLANNED_FLAG := null;
3789 END IF;
3790
3791 IF(p_Item_rec.CRITICAL_COMPONENT_FLAG IS NULL ) THEN
3792 l_Item_rec.CRITICAL_COMPONENT_FLAG := g_Upd_Null_NUM ;
3793 ELSIF p_Item_rec.CRITICAL_COMPONENT_FLAG = g_MISS_NUM THEN
3794 l_Item_rec.CRITICAL_COMPONENT_FLAG := null;
3795 END IF;
3796
3797 IF(p_Item_rec.CONTINOUS_TRANSFER IS NULL ) THEN
3798 l_Item_rec.CONTINOUS_TRANSFER := g_Upd_Null_NUM ;
3799 ELSIF p_Item_rec.CONTINOUS_TRANSFER = g_MISS_NUM THEN
3800 l_Item_rec.CONTINOUS_TRANSFER := null;
3801 END IF;
3802
3803 IF(p_Item_rec.CONVERGENCE IS NULL ) THEN
3804 l_Item_rec.CONVERGENCE := g_Upd_Null_NUM ;
3805 ELSIF p_Item_rec.CONVERGENCE = g_MISS_NUM THEN
3806 l_Item_rec.CONVERGENCE := null;
3807 END IF;
3808
3809 IF(p_Item_rec.DIVERGENCE IS NULL ) THEN
3810 l_Item_rec.DIVERGENCE := g_Upd_Null_NUM ;
3811 ELSIF p_Item_rec.DIVERGENCE = g_MISS_NUM THEN
3812 l_Item_rec.DIVERGENCE := null;
3813 END IF;
3814
3815 IF(p_Item_rec.LOT_DIVISIBLE_FLAG IS NULL) THEN
3816 l_Item_rec.LOT_DIVISIBLE_FLAG := g_Upd_Null_CHAR;
3817 ELSIF p_Item_rec.LOT_DIVISIBLE_FLAG = g_MISS_CHAR THEN
3818 l_Item_rec.LOT_DIVISIBLE_FLAG := null;
3819 END IF;
3820
3821 IF(p_Item_rec.GRADE_CONTROL_FLAG IS NULL) THEN
3822 l_Item_rec.GRADE_CONTROL_FLAG := g_Upd_Null_CHAR;
3823 ELSIF p_Item_rec.GRADE_CONTROL_FLAG = g_MISS_CHAR THEN
3824 l_Item_rec.GRADE_CONTROL_FLAG := null;
3825 END IF;
3826
3827 IF(p_Item_rec.DEFAULT_GRADE IS NULL) THEN
3828 l_Item_rec.DEFAULT_GRADE := g_Upd_Null_CHAR;
3829 ELSIF p_Item_rec.DEFAULT_GRADE = g_MISS_CHAR THEN
3830 l_Item_rec.DEFAULT_GRADE := null;
3831 END IF;
3832
3833 IF(p_Item_rec.CHILD_LOT_FLAG IS NULL) THEN
3834 l_Item_rec.CHILD_LOT_FLAG := g_Upd_Null_CHAR;
3835 ELSIF p_Item_rec.CHILD_LOT_FLAG = g_MISS_CHAR THEN
3836 l_Item_rec.CHILD_LOT_FLAG := null;
3837 END IF;
3838
3839 IF(p_Item_rec.PARENT_CHILD_GENERATION_FLAG IS NULL) THEN
3840 l_Item_rec.PARENT_CHILD_GENERATION_FLAG := g_Upd_Null_CHAR;
3841 ELSIF p_Item_rec.PARENT_CHILD_GENERATION_FLAG = g_MISS_CHAR THEN
3842 l_Item_rec.PARENT_CHILD_GENERATION_FLAG := null;
3843 END IF;
3844
3845 IF(p_Item_rec.CHILD_LOT_PREFIX IS NULL) THEN
3846 l_Item_rec.CHILD_LOT_PREFIX := g_Upd_Null_CHAR;
3847 ELSIF p_Item_rec.CHILD_LOT_PREFIX = g_MISS_CHAR THEN
3848 l_Item_rec.CHILD_LOT_PREFIX := null;
3849 END IF;
3850
3851 IF(p_Item_rec.CHILD_LOT_STARTING_NUMBER IS NULL ) THEN
3852 l_Item_rec.CHILD_LOT_STARTING_NUMBER := g_Upd_Null_NUM ;
3853 ELSIF p_Item_rec.CHILD_LOT_STARTING_NUMBER = g_MISS_NUM THEN
3854 l_Item_rec.CHILD_LOT_STARTING_NUMBER := null;
3855 END IF;
3856
3857 IF(p_Item_rec.CHILD_LOT_VALIDATION_FLAG IS NULL) THEN
3858 l_Item_rec.CHILD_LOT_VALIDATION_FLAG := g_Upd_Null_CHAR;
3859 ELSIF p_Item_rec.CHILD_LOT_VALIDATION_FLAG = g_MISS_CHAR THEN
3860 l_Item_rec.CHILD_LOT_VALIDATION_FLAG := null;
3861 END IF;
3862
3863 IF(p_Item_rec.COPY_LOT_ATTRIBUTE_FLAG IS NULL) THEN
3864 l_Item_rec.COPY_LOT_ATTRIBUTE_FLAG := g_Upd_Null_CHAR;
3865 ELSIF p_Item_rec.COPY_LOT_ATTRIBUTE_FLAG = g_MISS_CHAR THEN
3866 l_Item_rec.COPY_LOT_ATTRIBUTE_FLAG := null;
3867 END IF;
3868
3869 IF(p_Item_rec.RECIPE_ENABLED_FLAG IS NULL) THEN
3870 l_Item_rec.RECIPE_ENABLED_FLAG := g_Upd_Null_CHAR;
3871 ELSIF p_Item_rec.RECIPE_ENABLED_FLAG = g_MISS_CHAR THEN
3872 l_Item_rec.RECIPE_ENABLED_FLAG := null;
3873 END IF;
3874
3875 IF(p_Item_rec.PROCESS_QUALITY_ENABLED_FLAG IS NULL) THEN
3876 l_Item_rec.PROCESS_QUALITY_ENABLED_FLAG := g_Upd_Null_CHAR;
3877 ELSIF p_Item_rec.PROCESS_QUALITY_ENABLED_FLAG = g_MISS_CHAR THEN
3878 l_Item_rec.PROCESS_QUALITY_ENABLED_FLAG := null;
3879 END IF;
3880
3881 IF(p_Item_rec.PROCESS_EXECUTION_ENABLED_FLAG IS NULL) THEN
3882 l_Item_rec.PROCESS_EXECUTION_ENABLED_FLAG := g_Upd_Null_CHAR;
3883 ELSIF p_Item_rec.PROCESS_EXECUTION_ENABLED_FLAG = g_MISS_CHAR THEN
3884 l_Item_rec.PROCESS_EXECUTION_ENABLED_FLAG := null;
3885 END IF;
3886
3887 IF(p_Item_rec.PROCESS_COSTING_ENABLED_FLAG IS NULL) THEN
3888 l_Item_rec.PROCESS_COSTING_ENABLED_FLAG := g_Upd_Null_CHAR;
3889 ELSIF p_Item_rec.PROCESS_COSTING_ENABLED_FLAG = g_MISS_CHAR THEN
3890 l_Item_rec.PROCESS_COSTING_ENABLED_FLAG := null;
3891 END IF;
3892
3893 IF(p_Item_rec.PROCESS_SUPPLY_SUBINVENTORY IS NULL) THEN
3894 l_Item_rec.PROCESS_SUPPLY_SUBINVENTORY := g_Upd_Null_CHAR;
3895 ELSIF p_Item_rec.PROCESS_SUPPLY_SUBINVENTORY = g_MISS_CHAR THEN
3896 l_Item_rec.PROCESS_SUPPLY_SUBINVENTORY := null;
3897 END IF;
3898
3899 IF(p_Item_rec.PROCESS_SUPPLY_LOCATOR_ID IS NULL ) THEN
3900 l_Item_rec.PROCESS_SUPPLY_LOCATOR_ID := g_Upd_Null_NUM ;
3901 ELSIF p_Item_rec.PROCESS_SUPPLY_LOCATOR_ID = g_MISS_NUM THEN
3902 l_Item_rec.PROCESS_SUPPLY_LOCATOR_ID := null;
3903 END IF;
3904
3905 IF(p_Item_rec.PROCESS_YIELD_SUBINVENTORY IS NULL) THEN
3906 l_Item_rec.PROCESS_YIELD_SUBINVENTORY := g_Upd_Null_CHAR;
3907 ELSIF p_Item_rec.PROCESS_YIELD_SUBINVENTORY = g_MISS_CHAR THEN
3908 l_Item_rec.PROCESS_YIELD_SUBINVENTORY := null;
3909 END IF;
3910
3911 IF(p_Item_rec.PROCESS_YIELD_LOCATOR_ID IS NULL ) THEN
3912 l_Item_rec.PROCESS_YIELD_LOCATOR_ID := g_Upd_Null_NUM ;
3913 ELSIF p_Item_rec.PROCESS_YIELD_LOCATOR_ID = g_MISS_NUM THEN
3914 l_Item_rec.PROCESS_YIELD_LOCATOR_ID := null;
3915 END IF;
3916
3917 IF(p_Item_rec.HAZARDOUS_MATERIAL_FLAG IS NULL) THEN
3918 l_Item_rec.HAZARDOUS_MATERIAL_FLAG := g_Upd_Null_CHAR;
3919 ELSIF p_Item_rec.HAZARDOUS_MATERIAL_FLAG = g_MISS_CHAR THEN
3920 l_Item_rec.HAZARDOUS_MATERIAL_FLAG := null;
3921 END IF;
3922
3923 IF(p_Item_rec.CAS_NUMBER IS NULL) THEN
3924 l_Item_rec.CAS_NUMBER := g_Upd_Null_CHAR;
3925 ELSIF p_Item_rec.CAS_NUMBER = g_MISS_CHAR THEN
3926 l_Item_rec.CAS_NUMBER := null;
3927 END IF;
3928
3929 IF(p_Item_rec.RETEST_INTERVAL IS NULL ) THEN
3930 l_Item_rec.RETEST_INTERVAL := g_Upd_Null_NUM ;
3931 ELSIF p_Item_rec.RETEST_INTERVAL = g_MISS_NUM THEN
3932 l_Item_rec.RETEST_INTERVAL := null;
3933 END IF;
3934
3935 IF(p_Item_rec.EXPIRATION_ACTION_INTERVAL IS NULL ) THEN
3936 l_Item_rec.EXPIRATION_ACTION_INTERVAL := g_Upd_Null_NUM ;
3937 ELSIF p_Item_rec.EXPIRATION_ACTION_INTERVAL = g_MISS_NUM THEN
3938 l_Item_rec.EXPIRATION_ACTION_INTERVAL := null;
3939 END IF;
3940
3941 IF(p_Item_rec.EXPIRATION_ACTION_CODE IS NULL) THEN
3942 l_Item_rec.EXPIRATION_ACTION_CODE := g_Upd_Null_CHAR;
3943 ELSIF p_Item_rec.EXPIRATION_ACTION_CODE = g_MISS_CHAR THEN
3944 l_Item_rec.EXPIRATION_ACTION_CODE := null;
3945 END IF;
3946
3947 IF(p_Item_rec.MATURITY_DAYS IS NULL ) THEN
3948 l_Item_rec.MATURITY_DAYS := g_Upd_Null_NUM ;
3949 ELSIF p_Item_rec.MATURITY_DAYS = g_MISS_NUM THEN
3950 l_Item_rec.MATURITY_DAYS := null;
3951 END IF;
3952
3953 IF(p_Item_rec.HOLD_DAYS IS NULL ) THEN
3954 l_Item_rec.HOLD_DAYS := g_Upd_Null_NUM ;
3955 ELSIF p_Item_rec.HOLD_DAYS = g_MISS_NUM THEN
3956 l_Item_rec.HOLD_DAYS := null;
3957 END IF;
3958
3959 IF(p_Item_rec.Lifecycle_Id IS NULL ) THEN
3960 l_Item_rec.Lifecycle_Id := g_Upd_Null_NUM ;
3961 ELSIF p_Item_rec.Lifecycle_Id = g_MISS_NUM THEN
3962 l_Item_rec.Lifecycle_Id := null;
3963 END IF;
3964
3965 IF(p_Item_rec.Current_Phase_Id IS NULL ) THEN
3966 l_Item_rec.Current_Phase_Id := g_Upd_Null_NUM ;
3967 ELSIF p_Item_rec.Current_Phase_Id = g_MISS_NUM THEN
3968 l_Item_rec.Current_Phase_Id := null;
3969 END IF;
3970
3971 IF(p_Item_rec.CHARGE_PERIODICITY_CODE IS NULL) THEN
3972 l_Item_rec.CHARGE_PERIODICITY_CODE := g_Upd_Null_CHAR;
3973 ELSIF p_Item_rec.CHARGE_PERIODICITY_CODE = g_MISS_CHAR THEN
3974 l_Item_rec.CHARGE_PERIODICITY_CODE := null;
3975 END IF;
3976
3977 IF(p_Item_rec.REPAIR_LEADTIME IS NULL ) THEN
3978 l_Item_rec.REPAIR_LEADTIME := g_Upd_Null_NUM ;
3979 ELSIF p_Item_rec.REPAIR_LEADTIME = g_MISS_NUM THEN
3980 l_Item_rec.REPAIR_LEADTIME := null;
3981 END IF;
3982
3983 IF(p_Item_rec.REPAIR_YIELD IS NULL ) THEN
3984 l_Item_rec.REPAIR_YIELD := g_Upd_Null_NUM ;
3985 ELSIF p_Item_rec.REPAIR_YIELD = g_MISS_NUM THEN
3986 l_Item_rec.REPAIR_YIELD := null;
3987 END IF;
3988
3989 IF(p_Item_rec.PREPOSITION_POINT IS NULL) THEN
3990 l_Item_rec.PREPOSITION_POINT := g_Upd_Null_CHAR;
3991 ELSIF p_Item_rec.PREPOSITION_POINT = g_MISS_CHAR THEN
3992 l_Item_rec.PREPOSITION_POINT := null;
3993 END IF;
3994
3995 IF(p_Item_rec.REPAIR_PROGRAM IS NULL ) THEN
3996 l_Item_rec.REPAIR_PROGRAM := g_Upd_Null_NUM ;
3997 ELSIF p_Item_rec.REPAIR_PROGRAM = g_MISS_NUM THEN
3998 l_Item_rec.REPAIR_PROGRAM := null;
3999 END IF;
4000
4001 IF(p_Item_rec.SUBCONTRACTING_COMPONENT IS NULL ) THEN
4002 l_Item_rec.SUBCONTRACTING_COMPONENT := g_Upd_Null_NUM ;
4003 ELSIF p_Item_rec.SUBCONTRACTING_COMPONENT = g_MISS_NUM THEN
4004 l_Item_rec.SUBCONTRACTING_COMPONENT := null;
4005 END IF;
4006
4007 IF(p_Item_rec.OUTSOURCED_ASSEMBLY IS NULL ) THEN
4008 l_Item_rec.OUTSOURCED_ASSEMBLY := g_Upd_Null_NUM ;
4009 ELSIF p_Item_rec.OUTSOURCED_ASSEMBLY = g_MISS_NUM THEN
4010 l_Item_rec.OUTSOURCED_ASSEMBLY := null;
4011 END IF;
4012
4013 --R12 C Attributes
4014 IF(p_Item_rec.GDSN_OUTBOUND_ENABLED_FLAG IS NULL) THEN
4015 l_Item_rec.GDSN_OUTBOUND_ENABLED_FLAG := g_Upd_Null_CHAR;
4016 ELSIF p_Item_rec.GDSN_OUTBOUND_ENABLED_FLAG = g_MISS_CHAR THEN
4017 l_Item_rec.GDSN_OUTBOUND_ENABLED_FLAG := null;
4018 END IF;
4019
4020 IF(p_Item_rec.TRADE_ITEM_DESCRIPTOR IS NULL) THEN
4021 l_Item_rec.TRADE_ITEM_DESCRIPTOR := g_Upd_Null_CHAR;
4022 ELSIF p_Item_rec.TRADE_ITEM_DESCRIPTOR = g_MISS_CHAR THEN
4023 l_Item_rec.TRADE_ITEM_DESCRIPTOR := null;
4024 END IF;
4025
4026 IF(p_Item_rec.STYLE_ITEM_FLAG IS NULL) THEN
4027 l_Item_rec.STYLE_ITEM_FLAG := g_Upd_Null_CHAR;
4028 ELSIF p_Item_rec.STYLE_ITEM_FLAG = g_MISS_CHAR THEN
4029 l_Item_rec.STYLE_ITEM_FLAG := null;
4030 END IF;
4031
4032 IF(p_Item_rec.STYLE_ITEM_ID IS NULL ) THEN
4033 l_Item_rec.STYLE_ITEM_ID := g_Upd_Null_NUM ;
4034 ELSIF p_Item_rec.STYLE_ITEM_ID = g_MISS_NUM THEN
4035 l_Item_rec.STYLE_ITEM_ID := null;
4036 END IF;
4037
4038 -- Insert item row into MSII for further processing by IOI.
4039
4040 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(
4041 process_flag
4042 ,set_process_id
4043 ,transaction_type
4044 ,ORGANIZATION_ID
4045 ,ORGANIZATION_CODE
4046 ,INVENTORY_ITEM_ID
4047 ,ITEM_NUMBER
4048 ,SEGMENT1
4049 ,SEGMENT2
4050 ,SEGMENT3
4051 ,SEGMENT4
4052 ,SEGMENT5
4053 ,SEGMENT6
4054 ,SEGMENT7
4055 ,SEGMENT8
4056 ,SEGMENT9
4057 ,SEGMENT10
4058 ,SEGMENT11
4059 ,SEGMENT12
4060 ,SEGMENT13
4061 ,SEGMENT14
4062 ,SEGMENT15
4063 ,SEGMENT16
4064 ,SEGMENT17
4065 ,SEGMENT18
4066 ,SEGMENT19
4067 ,SEGMENT20
4068 ,SUMMARY_FLAG
4069 ,ENABLED_FLAG
4070 ,START_DATE_ACTIVE
4071 ,END_DATE_ACTIVE
4072 --
4073 ,TEMPLATE_ID
4074 ,TEMPLATE_NAME
4075 --
4076 ,DESCRIPTION
4077 ,LONG_DESCRIPTION
4078 ,PRIMARY_UOM_CODE
4079 ,PRIMARY_UNIT_OF_MEASURE
4080 ,ITEM_TYPE
4081 ,INVENTORY_ITEM_STATUS_CODE
4082 ,ALLOWED_UNITS_LOOKUP_CODE
4083 ,ITEM_CATALOG_GROUP_ID
4084 ,CATALOG_STATUS_FLAG
4085 ,INVENTORY_ITEM_FLAG
4086 ,STOCK_ENABLED_FLAG
4087 ,MTL_TRANSACTIONS_ENABLED_FLAG
4088 ,CHECK_SHORTAGES_FLAG
4089 ,REVISION_QTY_CONTROL_CODE
4090 ,RESERVABLE_TYPE
4091 ,SHELF_LIFE_CODE
4092 ,SHELF_LIFE_DAYS
4093 ,CYCLE_COUNT_ENABLED_FLAG
4094 ,NEGATIVE_MEASUREMENT_ERROR
4095 ,POSITIVE_MEASUREMENT_ERROR
4096 ,LOT_CONTROL_CODE
4097 ,AUTO_LOT_ALPHA_PREFIX
4098 ,START_AUTO_LOT_NUMBER
4099 ,SERIAL_NUMBER_CONTROL_CODE
4100 ,AUTO_SERIAL_ALPHA_PREFIX
4101 ,START_AUTO_SERIAL_NUMBER
4102 ,LOCATION_CONTROL_CODE
4103 ,RESTRICT_SUBINVENTORIES_CODE
4104 ,RESTRICT_LOCATORS_CODE
4105 ,BOM_ENABLED_FLAG
4106 ,BOM_ITEM_TYPE
4107 ,BASE_ITEM_ID
4108 ,EFFECTIVITY_CONTROL
4109 ,ENG_ITEM_FLAG
4110 ,ENGINEERING_ECN_CODE
4111 ,ENGINEERING_ITEM_ID
4112 ,ENGINEERING_DATE
4113 ,PRODUCT_FAMILY_ITEM_ID
4114 ,AUTO_CREATED_CONFIG_FLAG
4115 ,MODEL_CONFIG_CLAUSE_NAME
4116 ,COSTING_ENABLED_FLAG
4117 ,INVENTORY_ASSET_FLAG
4118 ,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
4119 ,COST_OF_SALES_ACCOUNT
4120 ,STD_LOT_SIZE
4121 ,PURCHASING_ITEM_FLAG
4122 ,PURCHASING_ENABLED_FLAG
4123 ,MUST_USE_APPROVED_VENDOR_FLAG
4124 ,ALLOW_ITEM_DESC_UPDATE_FLAG
4125 ,RFQ_REQUIRED_FLAG
4126 ,OUTSIDE_OPERATION_FLAG
4127 ,OUTSIDE_OPERATION_UOM_TYPE
4128 ,TAXABLE_FLAG
4129 ,PURCHASING_TAX_CODE
4130 ,RECEIPT_REQUIRED_FLAG
4131 ,INSPECTION_REQUIRED_FLAG
4132 ,BUYER_ID
4133 ,UNIT_OF_ISSUE
4134 ,RECEIVE_CLOSE_TOLERANCE
4135 ,INVOICE_CLOSE_TOLERANCE
4136 ,UN_NUMBER_ID
4137 ,HAZARD_CLASS_ID
4138 ,LIST_PRICE_PER_UNIT
4139 ,MARKET_PRICE
4140 ,PRICE_TOLERANCE_PERCENT
4141 ,ROUNDING_FACTOR
4142 ,ENCUMBRANCE_ACCOUNT
4143 ,EXPENSE_ACCOUNT
4144 ,ASSET_CATEGORY_ID
4145 ,RECEIPT_DAYS_EXCEPTION_CODE
4146 ,DAYS_EARLY_RECEIPT_ALLOWED
4147 ,DAYS_LATE_RECEIPT_ALLOWED
4148 ,ALLOW_SUBSTITUTE_RECEIPTS_FLAG
4149 ,ALLOW_UNORDERED_RECEIPTS_FLAG
4150 ,ALLOW_EXPRESS_DELIVERY_FLAG
4151 ,QTY_RCV_EXCEPTION_CODE
4152 ,QTY_RCV_TOLERANCE
4153 ,RECEIVING_ROUTING_ID
4154 ,ENFORCE_SHIP_TO_LOCATION_CODE
4155 ,WEIGHT_UOM_CODE
4156 ,UNIT_WEIGHT
4157 ,VOLUME_UOM_CODE
4158 ,UNIT_VOLUME
4159 ,CONTAINER_ITEM_FLAG
4160 ,VEHICLE_ITEM_FLAG
4161 ,CONTAINER_TYPE_CODE
4162 ,INTERNAL_VOLUME
4163 ,MAXIMUM_LOAD_WEIGHT
4164 ,MINIMUM_FILL_PERCENT
4165 ,INVENTORY_PLANNING_CODE
4166 ,PLANNER_CODE
4167 ,PLANNING_MAKE_BUY_CODE
4168 ,MIN_MINMAX_QUANTITY
4169 ,MAX_MINMAX_QUANTITY
4170 ,MINIMUM_ORDER_QUANTITY
4171 ,MAXIMUM_ORDER_QUANTITY
4172 ,ORDER_COST
4173 ,CARRYING_COST
4174 ,SOURCE_TYPE
4175 ,SOURCE_ORGANIZATION_ID
4176 ,SOURCE_SUBINVENTORY
4177 ,MRP_SAFETY_STOCK_CODE
4178 ,SAFETY_STOCK_BUCKET_DAYS
4179 ,MRP_SAFETY_STOCK_PERCENT
4180 ,FIXED_ORDER_QUANTITY
4181 ,FIXED_DAYS_SUPPLY
4182 ,FIXED_LOT_MULTIPLIER
4183 ,MRP_PLANNING_CODE
4184 ,ATO_FORECAST_CONTROL
4185 ,PLANNING_EXCEPTION_SET
4186 ,END_ASSEMBLY_PEGGING_FLAG
4187 ,SHRINKAGE_RATE
4188 ,ROUNDING_CONTROL_TYPE
4189 ,ACCEPTABLE_EARLY_DAYS
4190 ,REPETITIVE_PLANNING_FLAG
4191 ,OVERRUN_PERCENTAGE
4192 ,ACCEPTABLE_RATE_INCREASE
4193 ,ACCEPTABLE_RATE_DECREASE
4194 ,MRP_CALCULATE_ATP_FLAG
4195 ,AUTO_REDUCE_MPS
4196 ,PLANNING_TIME_FENCE_CODE
4197 ,PLANNING_TIME_FENCE_DAYS
4198 ,DEMAND_TIME_FENCE_CODE
4199 ,DEMAND_TIME_FENCE_DAYS
4200 ,RELEASE_TIME_FENCE_CODE
4201 ,RELEASE_TIME_FENCE_DAYS
4202 ,PREPROCESSING_LEAD_TIME
4203 ,FULL_LEAD_TIME
4204 ,POSTPROCESSING_LEAD_TIME
4205 ,FIXED_LEAD_TIME
4206 ,VARIABLE_LEAD_TIME
4207 ,CUM_MANUFACTURING_LEAD_TIME
4208 ,CUMULATIVE_TOTAL_LEAD_TIME
4209 ,LEAD_TIME_LOT_SIZE
4210 ,BUILD_IN_WIP_FLAG
4211 ,WIP_SUPPLY_TYPE
4212 ,WIP_SUPPLY_SUBINVENTORY
4213 ,WIP_SUPPLY_LOCATOR_ID
4214 ,OVERCOMPLETION_TOLERANCE_TYPE
4215 ,OVERCOMPLETION_TOLERANCE_VALUE
4216 ,CUSTOMER_ORDER_FLAG
4217 ,CUSTOMER_ORDER_ENABLED_FLAG
4218 ,SHIPPABLE_ITEM_FLAG
4219 ,INTERNAL_ORDER_FLAG
4220 ,INTERNAL_ORDER_ENABLED_FLAG
4221 ,SO_TRANSACTIONS_FLAG
4222 ,PICK_COMPONENTS_FLAG
4223 ,ATP_FLAG
4224 ,REPLENISH_TO_ORDER_FLAG
4225 ,ATP_RULE_ID
4226 ,ATP_COMPONENTS_FLAG
4227 ,SHIP_MODEL_COMPLETE_FLAG
4228 ,PICKING_RULE_ID
4229 ,COLLATERAL_FLAG
4230 ,DEFAULT_SHIPPING_ORG
4231 ,RETURNABLE_FLAG
4232 ,RETURN_INSPECTION_REQUIREMENT
4233 ,OVER_SHIPMENT_TOLERANCE
4234 ,UNDER_SHIPMENT_TOLERANCE
4235 ,OVER_RETURN_TOLERANCE
4236 ,UNDER_RETURN_TOLERANCE
4237 ,INVOICEABLE_ITEM_FLAG
4238 ,INVOICE_ENABLED_FLAG
4239 ,ACCOUNTING_RULE_ID
4240 ,INVOICING_RULE_ID
4241 ,TAX_CODE
4242 ,SALES_ACCOUNT
4243 ,PAYMENT_TERMS_ID
4244 ,COVERAGE_SCHEDULE_ID
4245 ,SERVICE_DURATION
4246 ,SERVICE_DURATION_PERIOD_CODE
4247 ,SERVICEABLE_PRODUCT_FLAG
4248 ,SERVICE_STARTING_DELAY
4249 ,MATERIAL_BILLABLE_FLAG
4250 ,SERVICEABLE_COMPONENT_FLAG
4251 ,PREVENTIVE_MAINTENANCE_FLAG
4252 ,PRORATE_SERVICE_FLAG
4253 ,WH_UPDATE_DATE
4254 ,EQUIPMENT_TYPE
4255 ,RECOVERED_PART_DISP_CODE
4256 ,DEFECT_TRACKING_ON_FLAG
4257 ,EVENT_FLAG
4258 ,ELECTRONIC_FLAG
4259 ,DOWNLOADABLE_FLAG
4260 ,VOL_DISCOUNT_EXEMPT_FLAG
4261 ,COUPON_EXEMPT_FLAG
4262 ,COMMS_NL_TRACKABLE_FLAG
4263 ,ASSET_CREATION_CODE
4264 ,COMMS_ACTIVATION_REQD_FLAG
4265 ,WEB_STATUS
4266 ,ORDERABLE_ON_WEB_FLAG
4267 ,BACK_ORDERABLE_FLAG
4268 , INDIVISIBLE_FLAG
4269 ,DIMENSION_UOM_CODE
4270 ,UNIT_LENGTH
4271 ,UNIT_WIDTH
4272 ,UNIT_HEIGHT
4273 ,BULK_PICKED_FLAG
4274 ,LOT_STATUS_ENABLED
4275 ,DEFAULT_LOT_STATUS_ID
4276 ,SERIAL_STATUS_ENABLED
4277 ,DEFAULT_SERIAL_STATUS_ID
4278 ,LOT_SPLIT_ENABLED
4279 ,LOT_MERGE_ENABLED
4280 ,INVENTORY_CARRY_PENALTY
4281 ,OPERATION_SLACK_PENALTY
4282 ,FINANCING_ALLOWED_FLAG
4283 ,EAM_ITEM_TYPE
4284 ,EAM_ACTIVITY_TYPE_CODE
4285 ,EAM_ACTIVITY_CAUSE_CODE
4286 ,EAM_ACT_NOTIFICATION_FLAG
4287 ,EAM_ACT_SHUTDOWN_STATUS
4288 ,DUAL_UOM_CONTROL
4289 ,SECONDARY_UOM_CODE
4290 ,DUAL_UOM_DEVIATION_HIGH
4291 ,DUAL_UOM_DEVIATION_LOW
4292 --
4293 ,CONTRACT_ITEM_TYPE_CODE
4294 ,SUBSCRIPTION_DEPEND_FLAG
4295 --
4296 ,SERV_REQ_ENABLED_CODE
4297 ,SERV_BILLING_ENABLED_FLAG
4298 ,SERV_IMPORTANCE_LEVEL
4299 ,PLANNED_INV_POINT_FLAG
4300 ,LOT_TRANSLATE_ENABLED
4301 ,DEFAULT_SO_SOURCE_TYPE
4302 ,CREATE_SUPPLY_FLAG
4303 ,SUBSTITUTION_WINDOW_CODE
4304 ,SUBSTITUTION_WINDOW_DAYS
4305 -- Added as part of 11.5.9
4306 ,LOT_SUBSTITUTION_ENABLED
4307 ,MINIMUM_LICENSE_QUANTITY
4308 ,EAM_ACTIVITY_SOURCE_CODE
4309 ,IB_ITEM_INSTANCE_CLASS
4310 ,CONFIG_MODEL_TYPE
4311 -- Added as part of 11.5.10
4312 ,TRACKING_QUANTITY_IND
4313 ,ONT_PRICING_QTY_SOURCE
4314 ,SECONDARY_DEFAULT_IND
4315 ,CONFIG_ORGS
4316 ,CONFIG_MATCH
4317 --
4318 ,ATTRIBUTE_CATEGORY
4319 ,ATTRIBUTE1
4320 ,ATTRIBUTE2
4321 ,ATTRIBUTE3
4322 ,ATTRIBUTE4
4323 ,ATTRIBUTE5
4324 ,ATTRIBUTE6
4325 ,ATTRIBUTE7
4326 ,ATTRIBUTE8
4327 ,ATTRIBUTE9
4328 ,ATTRIBUTE10
4329 ,ATTRIBUTE11
4330 ,ATTRIBUTE12
4331 ,ATTRIBUTE13
4332 ,ATTRIBUTE14
4333 ,ATTRIBUTE15
4334 /* Start Bug 3713912 */
4335 ,ATTRIBUTE16
4336 ,ATTRIBUTE17
4337 ,ATTRIBUTE18
4338 ,ATTRIBUTE19
4339 ,ATTRIBUTE20
4340 ,ATTRIBUTE21
4341 ,ATTRIBUTE22
4342 ,ATTRIBUTE23
4343 ,ATTRIBUTE24
4344 ,ATTRIBUTE25
4345 ,ATTRIBUTE26
4346 ,ATTRIBUTE27
4347 ,ATTRIBUTE28
4348 ,ATTRIBUTE29
4349 ,ATTRIBUTE30
4350 /* End Bug 3713912 */
4351 ,GLOBAL_ATTRIBUTE_CATEGORY
4352 ,GLOBAL_ATTRIBUTE1
4353 ,GLOBAL_ATTRIBUTE2
4354 ,GLOBAL_ATTRIBUTE3
4355 ,GLOBAL_ATTRIBUTE4
4356 ,GLOBAL_ATTRIBUTE5
4357 ,GLOBAL_ATTRIBUTE6
4358 ,GLOBAL_ATTRIBUTE7
4359 ,GLOBAL_ATTRIBUTE8
4360 ,GLOBAL_ATTRIBUTE9
4361 ,GLOBAL_ATTRIBUTE10
4362 ,GLOBAL_ATTRIBUTE11
4363 ,GLOBAL_ATTRIBUTE12
4364 ,GLOBAL_ATTRIBUTE13
4365 ,GLOBAL_ATTRIBUTE14
4366 ,GLOBAL_ATTRIBUTE15
4367 ,GLOBAL_ATTRIBUTE16
4368 ,GLOBAL_ATTRIBUTE17
4369 ,GLOBAL_ATTRIBUTE18
4370 ,GLOBAL_ATTRIBUTE19
4371 ,GLOBAL_ATTRIBUTE20
4372 ,CREATION_DATE
4373 ,CREATED_BY
4374 ,LAST_UPDATE_DATE
4375 ,LAST_UPDATED_BY
4376 ,LAST_UPDATE_LOGIN
4377 ,REQUEST_ID
4378 ,PROGRAM_APPLICATION_ID
4379 ,PROGRAM_ID
4380 ,PROGRAM_UPDATE_DATE
4381 ,VMI_MINIMUM_UNITS
4382 ,VMI_MINIMUM_DAYS
4383 ,VMI_MAXIMUM_UNITS
4384 ,VMI_MAXIMUM_DAYS
4385 ,VMI_FIXED_ORDER_QUANTITY
4386 ,SO_AUTHORIZATION_FLAG
4387 ,CONSIGNED_FLAG
4388 ,ASN_AUTOEXPIRE_FLAG
4389 ,VMI_FORECAST_TYPE
4390 ,FORECAST_HORIZON
4391 ,EXCLUDE_FROM_BUDGET_FLAG
4392 ,DAYS_TGT_INV_SUPPLY
4393 ,DAYS_TGT_INV_WINDOW
4394 ,DAYS_MAX_INV_SUPPLY
4395 ,DAYS_MAX_INV_WINDOW
4396 ,DRP_PLANNED_FLAG
4397 ,CRITICAL_COMPONENT_FLAG
4398 ,CONTINOUS_TRANSFER
4399 ,CONVERGENCE
4400 ,DIVERGENCE
4401 /* Start Bug 3713912 */
4402 ,LOT_DIVISIBLE_FLAG
4403 ,GRADE_CONTROL_FLAG
4404 ,DEFAULT_GRADE
4405 ,CHILD_LOT_FLAG
4406 ,PARENT_CHILD_GENERATION_FLAG
4407 ,CHILD_LOT_PREFIX
4408 ,CHILD_LOT_STARTING_NUMBER
4409 ,CHILD_LOT_VALIDATION_FLAG
4410 ,COPY_LOT_ATTRIBUTE_FLAG
4411 ,RECIPE_ENABLED_FLAG
4412 ,PROCESS_QUALITY_ENABLED_FLAG
4413 ,PROCESS_EXECUTION_ENABLED_FLAG
4414 ,PROCESS_COSTING_ENABLED_FLAG
4415 ,PROCESS_SUPPLY_SUBINVENTORY
4416 ,PROCESS_SUPPLY_LOCATOR_ID
4417 ,PROCESS_YIELD_SUBINVENTORY
4418 ,PROCESS_YIELD_LOCATOR_ID
4419 ,HAZARDOUS_MATERIAL_FLAG
4420 ,CAS_NUMBER
4421 ,RETEST_INTERVAL
4422 ,EXPIRATION_ACTION_INTERVAL
4423 ,EXPIRATION_ACTION_CODE
4424 ,MATURITY_DAYS
4425 ,HOLD_DAYS
4426 /* End Bug 3713912 */
4427 ,LIFECYCLE_ID -- Bug 3933277
4428 ,CURRENT_PHASE_ID -- Bug 3933277
4429 /* R12 Enhancement */
4430 ,CHARGE_PERIODICITY_CODE
4431 ,REPAIR_LEADTIME
4432 ,REPAIR_YIELD
4433 ,PREPOSITION_POINT
4434 ,REPAIR_PROGRAM
4435 ,SUBCONTRACTING_COMPONENT
4436 ,OUTSOURCED_ASSEMBLY
4437 --R12 C Attributes
4438 ,GDSN_OUTBOUND_ENABLED_FLAG
4439 ,TRADE_ITEM_DESCRIPTOR
4440 ,STYLE_ITEM_FLAG
4441 ,STYLE_ITEM_ID)
4442 VALUES(
4443 l_process_flag
4444 ,l_set_process_id /* unique process set id for one record in IOI */
4445 ,p_transaction_type /* transaction type (CREATE, UPDATE) */
4446 ,l_Item_rec.ORGANIZATION_ID
4447 ,l_Item_rec.ORGANIZATION_CODE
4448 ,l_Item_rec.INVENTORY_ITEM_ID
4449 ,l_Item_rec.ITEM_NUMBER
4450 ,l_Item_rec.SEGMENT1
4451 ,l_Item_rec.SEGMENT2
4452 ,l_Item_rec.SEGMENT3
4453 ,l_Item_rec.SEGMENT4
4454 ,l_Item_rec.SEGMENT5
4455 ,l_Item_rec.SEGMENT6
4456 ,l_Item_rec.SEGMENT7
4457 ,l_Item_rec.SEGMENT8
4458 ,l_Item_rec.SEGMENT9
4459 ,l_Item_rec.SEGMENT10
4460 ,l_Item_rec.SEGMENT11
4461 ,l_Item_rec.SEGMENT12
4462 ,l_Item_rec.SEGMENT13
4463 ,l_Item_rec.SEGMENT14
4464 ,l_Item_rec.SEGMENT15
4465 ,l_Item_rec.SEGMENT16
4466 ,l_Item_rec.SEGMENT17
4467 ,l_Item_rec.SEGMENT18
4468 ,l_Item_rec.SEGMENT19
4469 ,l_Item_rec.SEGMENT20
4470 ,l_Item_rec.SUMMARY_FLAG
4471 ,l_Item_rec.ENABLED_FLAG
4472 ,l_Item_rec.START_DATE_ACTIVE
4473 ,l_Item_rec.END_DATE_ACTIVE
4474 --
4475 ,p_Template_Id
4476 ,p_Template_Name
4477 --
4478 ,l_Item_rec.DESCRIPTION
4479 ,l_Item_rec.LONG_DESCRIPTION
4480 ,l_Item_rec.PRIMARY_UOM_CODE
4481 ,l_Item_rec.PRIMARY_UNIT_OF_MEASURE
4482 ,l_Item_rec.ITEM_TYPE
4483 ,l_Item_rec.INVENTORY_ITEM_STATUS_CODE
4484 ,l_Item_rec.ALLOWED_UNITS_LOOKUP_CODE
4485 ,l_Item_rec.ITEM_CATALOG_GROUP_ID
4486 ,l_Item_rec.CATALOG_STATUS_FLAG
4487 ,l_Item_rec.INVENTORY_ITEM_FLAG
4488 ,l_Item_rec.STOCK_ENABLED_FLAG
4489 ,l_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG
4490 ,l_Item_rec.CHECK_SHORTAGES_FLAG
4491 ,l_Item_rec.REVISION_QTY_CONTROL_CODE
4492 ,l_Item_rec.RESERVABLE_TYPE
4493 ,l_Item_rec.SHELF_LIFE_CODE
4494 ,l_Item_rec.SHELF_LIFE_DAYS
4495 ,l_Item_rec.CYCLE_COUNT_ENABLED_FLAG
4496 ,l_Item_rec.NEGATIVE_MEASUREMENT_ERROR
4497 ,l_Item_rec.POSITIVE_MEASUREMENT_ERROR
4498 ,l_Item_rec.LOT_CONTROL_CODE
4499 ,l_Item_rec.AUTO_LOT_ALPHA_PREFIX
4500 ,l_Item_rec.START_AUTO_LOT_NUMBER
4501 ,l_Item_rec.SERIAL_NUMBER_CONTROL_CODE
4502 ,l_Item_rec.AUTO_SERIAL_ALPHA_PREFIX
4503 ,l_Item_rec.START_AUTO_SERIAL_NUMBER
4504 ,l_Item_rec.LOCATION_CONTROL_CODE
4505 ,l_Item_rec.RESTRICT_SUBINVENTORIES_CODE
4506 ,l_Item_rec.RESTRICT_LOCATORS_CODE
4507 ,l_Item_rec.BOM_ENABLED_FLAG
4508 ,l_Item_rec.BOM_ITEM_TYPE
4509 ,l_Item_rec.BASE_ITEM_ID
4510 ,l_Item_rec.EFFECTIVITY_CONTROL
4511 ,l_Item_rec.ENG_ITEM_FLAG
4512 ,l_Item_rec.ENGINEERING_ECN_CODE
4513 ,l_Item_rec.ENGINEERING_ITEM_ID
4514 ,l_Item_rec.ENGINEERING_DATE
4515 ,l_Item_rec.PRODUCT_FAMILY_ITEM_ID
4516 ,l_Item_rec.AUTO_CREATED_CONFIG_FLAG
4517 ,l_Item_rec.MODEL_CONFIG_CLAUSE_NAME
4518 ,l_Item_rec.COSTING_ENABLED_FLAG
4519 ,l_Item_rec.INVENTORY_ASSET_FLAG
4520 ,l_Item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG
4521 ,l_Item_rec.COST_OF_SALES_ACCOUNT
4522 ,l_Item_rec.STD_LOT_SIZE
4523 ,l_Item_rec.PURCHASING_ITEM_FLAG
4524 ,l_Item_rec.PURCHASING_ENABLED_FLAG
4525 ,l_Item_rec.MUST_USE_APPROVED_VENDOR_FLAG
4526 ,l_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG
4527 ,l_Item_rec.RFQ_REQUIRED_FLAG
4528 ,l_Item_rec.OUTSIDE_OPERATION_FLAG
4529 ,l_Item_rec.OUTSIDE_OPERATION_UOM_TYPE
4530 ,l_Item_rec.TAXABLE_FLAG
4531 ,l_Item_rec.PURCHASING_TAX_CODE
4532 ,l_Item_rec.RECEIPT_REQUIRED_FLAG
4533 ,l_Item_rec.INSPECTION_REQUIRED_FLAG
4534 ,l_Item_rec.BUYER_ID
4535 ,l_Item_rec.UNIT_OF_ISSUE
4536 ,l_Item_rec.RECEIVE_CLOSE_TOLERANCE
4537 ,l_Item_rec.INVOICE_CLOSE_TOLERANCE
4538 ,l_Item_rec.UN_NUMBER_ID
4539 ,l_Item_rec.HAZARD_CLASS_ID
4540 ,l_Item_rec.LIST_PRICE_PER_UNIT
4541 ,l_Item_rec.MARKET_PRICE
4542 ,l_Item_rec.PRICE_TOLERANCE_PERCENT
4543 ,l_Item_rec.ROUNDING_FACTOR
4544 ,l_Item_rec.ENCUMBRANCE_ACCOUNT
4545 ,l_Item_rec.EXPENSE_ACCOUNT
4546 ,l_Item_rec.ASSET_CATEGORY_ID
4547 ,l_Item_rec.RECEIPT_DAYS_EXCEPTION_CODE
4548 ,l_Item_rec.DAYS_EARLY_RECEIPT_ALLOWED
4549 ,l_Item_rec.DAYS_LATE_RECEIPT_ALLOWED
4550 ,l_Item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
4551 ,l_Item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG
4552 ,l_Item_rec.ALLOW_EXPRESS_DELIVERY_FLAG
4553 ,l_Item_rec.QTY_RCV_EXCEPTION_CODE
4554 ,l_Item_rec.QTY_RCV_TOLERANCE
4555 ,l_Item_rec.RECEIVING_ROUTING_ID
4556 ,l_Item_rec.ENFORCE_SHIP_TO_LOCATION_CODE
4557 ,l_Item_rec.WEIGHT_UOM_CODE
4558 ,l_Item_rec.UNIT_WEIGHT
4559 ,l_Item_rec.VOLUME_UOM_CODE
4560 ,l_Item_rec.UNIT_VOLUME
4561 ,l_Item_rec.CONTAINER_ITEM_FLAG
4562 ,l_Item_rec.VEHICLE_ITEM_FLAG
4563 ,l_Item_rec.CONTAINER_TYPE_CODE
4564 ,l_Item_rec.INTERNAL_VOLUME
4565 ,l_Item_rec.MAXIMUM_LOAD_WEIGHT
4566 ,l_Item_rec.MINIMUM_FILL_PERCENT
4567 ,l_Item_rec.INVENTORY_PLANNING_CODE
4568 ,l_Item_rec.PLANNER_CODE
4569 ,l_Item_rec.PLANNING_MAKE_BUY_CODE
4570 ,l_Item_rec.MIN_MINMAX_QUANTITY
4571 ,l_Item_rec.MAX_MINMAX_QUANTITY
4572 ,l_Item_rec.MINIMUM_ORDER_QUANTITY
4573 ,l_Item_rec.MAXIMUM_ORDER_QUANTITY
4574 ,l_Item_rec.ORDER_COST
4575 ,l_Item_rec.CARRYING_COST
4576 ,l_Item_rec.SOURCE_TYPE
4577 ,l_Item_rec.SOURCE_ORGANIZATION_ID
4578 ,l_Item_rec.SOURCE_SUBINVENTORY
4579 ,l_Item_rec.MRP_SAFETY_STOCK_CODE
4580 ,l_Item_rec.SAFETY_STOCK_BUCKET_DAYS
4581 ,l_Item_rec.MRP_SAFETY_STOCK_PERCENT
4582 ,l_Item_rec.FIXED_ORDER_QUANTITY
4583 ,l_Item_rec.FIXED_DAYS_SUPPLY
4584 ,l_Item_rec.FIXED_LOT_MULTIPLIER
4585 ,l_Item_rec.MRP_PLANNING_CODE
4586 ,l_Item_rec.ATO_FORECAST_CONTROL
4587 ,l_Item_rec.PLANNING_EXCEPTION_SET
4588 ,l_Item_rec.END_ASSEMBLY_PEGGING_FLAG
4589 ,l_Item_rec.SHRINKAGE_RATE
4590 ,l_Item_rec.ROUNDING_CONTROL_TYPE
4591 ,l_Item_rec.ACCEPTABLE_EARLY_DAYS
4592 ,l_Item_rec.REPETITIVE_PLANNING_FLAG
4593 ,l_Item_rec.OVERRUN_PERCENTAGE
4594 ,l_Item_rec.ACCEPTABLE_RATE_INCREASE
4595 ,l_Item_rec.ACCEPTABLE_RATE_DECREASE
4596 ,l_Item_rec.MRP_CALCULATE_ATP_FLAG
4597 ,l_Item_rec.AUTO_REDUCE_MPS
4598 ,l_Item_rec.PLANNING_TIME_FENCE_CODE
4599 ,l_Item_rec.PLANNING_TIME_FENCE_DAYS
4600 ,l_Item_rec.DEMAND_TIME_FENCE_CODE
4601 ,l_Item_rec.DEMAND_TIME_FENCE_DAYS
4602 ,l_Item_rec.RELEASE_TIME_FENCE_CODE
4603 ,l_Item_rec.RELEASE_TIME_FENCE_DAYS
4604 ,l_Item_rec.PREPROCESSING_LEAD_TIME
4605 ,l_Item_rec.FULL_LEAD_TIME
4606 ,l_Item_rec.POSTPROCESSING_LEAD_TIME
4607 ,l_Item_rec.FIXED_LEAD_TIME
4608 ,l_Item_rec.VARIABLE_LEAD_TIME
4609 ,l_Item_rec.CUM_MANUFACTURING_LEAD_TIME
4610 ,l_Item_rec.CUMULATIVE_TOTAL_LEAD_TIME
4611 ,l_Item_rec.LEAD_TIME_LOT_SIZE
4612 ,l_Item_rec.BUILD_IN_WIP_FLAG
4613 ,l_Item_rec.WIP_SUPPLY_TYPE
4614 ,l_Item_rec.WIP_SUPPLY_SUBINVENTORY
4615 ,l_Item_rec.WIP_SUPPLY_LOCATOR_ID
4616 ,l_Item_rec.OVERCOMPLETION_TOLERANCE_TYPE
4617 ,l_Item_rec.OVERCOMPLETION_TOLERANCE_VALUE
4618 ,l_Item_rec.CUSTOMER_ORDER_FLAG
4619 ,l_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG
4620 ,l_Item_rec.SHIPPABLE_ITEM_FLAG
4621 ,l_Item_rec.INTERNAL_ORDER_FLAG
4622 ,l_Item_rec.INTERNAL_ORDER_ENABLED_FLAG
4623 ,l_Item_rec.SO_TRANSACTIONS_FLAG
4624 ,l_Item_rec.PICK_COMPONENTS_FLAG
4625 ,l_Item_rec.ATP_FLAG
4626 ,l_Item_rec.REPLENISH_TO_ORDER_FLAG
4627 ,l_Item_rec.ATP_RULE_ID
4628 ,l_Item_rec.ATP_COMPONENTS_FLAG
4629 ,l_Item_rec.SHIP_MODEL_COMPLETE_FLAG
4630 ,l_Item_rec.PICKING_RULE_ID
4631 ,l_Item_rec.COLLATERAL_FLAG
4632 ,l_Item_rec.DEFAULT_SHIPPING_ORG
4633 ,l_Item_rec.RETURNABLE_FLAG
4634 ,l_Item_rec.RETURN_INSPECTION_REQUIREMENT
4635 ,l_Item_rec.OVER_SHIPMENT_TOLERANCE
4636 ,l_Item_rec.UNDER_SHIPMENT_TOLERANCE
4637 ,l_Item_rec.OVER_RETURN_TOLERANCE
4638 ,l_Item_rec.UNDER_RETURN_TOLERANCE
4639 ,l_Item_rec.INVOICEABLE_ITEM_FLAG
4640 ,l_Item_rec.INVOICE_ENABLED_FLAG
4641 ,l_Item_rec.ACCOUNTING_RULE_ID
4642 ,l_Item_rec.INVOICING_RULE_ID
4643 ,l_Item_rec.TAX_CODE
4644 ,l_Item_rec.SALES_ACCOUNT
4645 ,l_Item_rec.PAYMENT_TERMS_ID
4646 ,l_Item_rec.COVERAGE_SCHEDULE_ID
4647 ,l_Item_rec.SERVICE_DURATION
4648 ,l_Item_rec.SERVICE_DURATION_PERIOD_CODE
4649 ,l_Item_rec.SERVICEABLE_PRODUCT_FLAG
4650 ,l_Item_rec.SERVICE_STARTING_DELAY
4651 ,l_Item_rec.MATERIAL_BILLABLE_FLAG
4652 ,l_Item_rec.SERVICEABLE_COMPONENT_FLAG
4653 ,l_Item_rec.PREVENTIVE_MAINTENANCE_FLAG
4654 ,l_Item_rec.PRORATE_SERVICE_FLAG
4655 ,l_Item_rec.WH_UPDATE_DATE
4656 ,l_Item_rec.EQUIPMENT_TYPE
4657 ,l_Item_rec.RECOVERED_PART_DISP_CODE
4658 ,l_Item_rec.DEFECT_TRACKING_ON_FLAG
4659 ,l_Item_rec.EVENT_FLAG
4660 ,l_Item_rec.ELECTRONIC_FLAG
4661 ,l_Item_rec.DOWNLOADABLE_FLAG
4662 ,l_Item_rec.VOL_DISCOUNT_EXEMPT_FLAG
4663 ,l_Item_rec.COUPON_EXEMPT_FLAG
4664 ,l_Item_rec.COMMS_NL_TRACKABLE_FLAG
4665 ,l_Item_rec.ASSET_CREATION_CODE
4666 ,l_Item_rec.COMMS_ACTIVATION_REQD_FLAG
4667 ,l_Item_rec.WEB_STATUS
4668 ,l_Item_rec.ORDERABLE_ON_WEB_FLAG
4669 ,l_Item_rec.BACK_ORDERABLE_FLAG
4670 ,l_Item_rec.INDIVISIBLE_FLAG
4671 ,l_Item_rec.DIMENSION_UOM_CODE
4672 ,l_Item_rec.UNIT_LENGTH
4673 ,l_Item_rec.UNIT_WIDTH
4674 ,l_Item_rec.UNIT_HEIGHT
4675 ,l_Item_rec.BULK_PICKED_FLAG
4676 ,l_Item_rec.LOT_STATUS_ENABLED
4677 ,l_Item_rec.DEFAULT_LOT_STATUS_ID
4678 ,l_Item_rec.SERIAL_STATUS_ENABLED
4679 ,l_Item_rec.DEFAULT_SERIAL_STATUS_ID
4680 ,l_Item_rec.LOT_SPLIT_ENABLED
4681 ,l_Item_rec.LOT_MERGE_ENABLED
4682 ,l_Item_rec.INVENTORY_CARRY_PENALTY
4683 ,l_Item_rec.OPERATION_SLACK_PENALTY
4684 ,l_Item_rec.FINANCING_ALLOWED_FLAG
4685 ,l_Item_rec.EAM_ITEM_TYPE
4686 ,l_Item_rec.EAM_ACTIVITY_TYPE_CODE
4687 ,l_Item_rec.EAM_ACTIVITY_CAUSE_CODE
4688 ,l_Item_rec.EAM_ACT_NOTIFICATION_FLAG
4689 ,l_Item_rec.EAM_ACT_SHUTDOWN_STATUS
4690 ,l_Item_rec.DUAL_UOM_CONTROL
4691 ,l_Item_rec.SECONDARY_UOM_CODE
4692 ,l_Item_rec.DUAL_UOM_DEVIATION_HIGH
4693 ,l_Item_rec.DUAL_UOM_DEVIATION_LOW
4694 --
4695 ,DECODE(l_Contract_Item_Type_Code, g_Null_CHAR, g_Upd_Null_CHAR, g_MISS_CHAR, NULL, l_Contract_Item_Type_Code)
4696 ,l_Item_rec.SUBSCRIPTION_DEPEND_FLAG
4697 --
4698 ,l_Item_rec.SERV_REQ_ENABLED_CODE
4699 ,l_Item_rec.SERV_BILLING_ENABLED_FLAG
4700 ,l_Item_rec.SERV_IMPORTANCE_LEVEL
4701 ,l_Item_rec.PLANNED_INV_POINT_FLAG
4702 ,l_Item_rec.LOT_TRANSLATE_ENABLED
4703 ,l_Item_rec.DEFAULT_SO_SOURCE_TYPE
4704 ,l_Item_rec.CREATE_SUPPLY_FLAG
4705 ,l_Item_rec.SUBSTITUTION_WINDOW_CODE
4706 ,l_Item_rec.SUBSTITUTION_WINDOW_DAYS
4707 -- Added as part of 11.5.9
4708 ,l_Item_rec.LOT_SUBSTITUTION_ENABLED
4709 ,l_Item_rec.MINIMUM_LICENSE_QUANTITY
4710 ,l_Item_rec.EAM_ACTIVITY_SOURCE_CODE
4711 ,l_Item_rec.IB_ITEM_INSTANCE_CLASS
4712 ,l_Item_rec.CONFIG_MODEL_TYPE
4713 -- Added as part of 11.5.10
4714 ,l_Item_rec.TRACKING_QUANTITY_IND
4715 ,l_Item_rec.ONT_PRICING_QTY_SOURCE
4716 ,l_Item_rec.SECONDARY_DEFAULT_IND
4717 ,l_Item_rec.CONFIG_ORGS
4718 ,l_Item_rec.CONFIG_MATCH
4719 --
4720 ,l_Item_rec.ATTRIBUTE_CATEGORY
4721 ,l_Item_rec.ATTRIBUTE1
4722 ,l_Item_rec.ATTRIBUTE2
4723 ,l_Item_rec.ATTRIBUTE3
4724 ,l_Item_rec.ATTRIBUTE4
4725 ,l_Item_rec.ATTRIBUTE5
4726 ,l_Item_rec.ATTRIBUTE6
4727 ,l_Item_rec.ATTRIBUTE7
4728 ,l_Item_rec.ATTRIBUTE8
4729 ,l_Item_rec.ATTRIBUTE9
4730 ,l_Item_rec.ATTRIBUTE10
4731 ,l_Item_rec.ATTRIBUTE11
4732 ,l_Item_rec.ATTRIBUTE12
4733 ,l_Item_rec.ATTRIBUTE13
4734 ,l_Item_rec.ATTRIBUTE14
4735 ,l_Item_rec.ATTRIBUTE15
4736 /* Start Bug 3713912 */
4737 ,l_Item_rec.ATTRIBUTE16
4738 ,l_Item_rec.ATTRIBUTE17
4739 ,l_Item_rec.ATTRIBUTE18
4740 ,l_Item_rec.ATTRIBUTE19
4741 ,l_Item_rec.ATTRIBUTE20
4742 ,l_Item_rec.ATTRIBUTE21
4743 ,l_Item_rec.ATTRIBUTE22
4744 ,l_Item_rec.ATTRIBUTE23
4745 ,l_Item_rec.ATTRIBUTE24
4746 ,l_Item_rec.ATTRIBUTE25
4747 ,l_Item_rec.ATTRIBUTE26
4748 ,l_Item_rec.ATTRIBUTE27
4749 ,l_Item_rec.ATTRIBUTE28
4750 ,l_Item_rec.ATTRIBUTE29
4751 ,l_Item_rec.ATTRIBUTE30
4752 /* End Bug 3713912 */
4753 ,l_Item_rec.GLOBAL_ATTRIBUTE_CATEGORY
4754 ,l_Item_rec.GLOBAL_ATTRIBUTE1
4755 ,l_Item_rec.GLOBAL_ATTRIBUTE2
4756 ,l_Item_rec.GLOBAL_ATTRIBUTE3
4757 ,l_Item_rec.GLOBAL_ATTRIBUTE4
4758 ,l_Item_rec.GLOBAL_ATTRIBUTE5
4759 ,l_Item_rec.GLOBAL_ATTRIBUTE6
4760 ,l_Item_rec.GLOBAL_ATTRIBUTE7
4761 ,l_Item_rec.GLOBAL_ATTRIBUTE8
4762 ,l_Item_rec.GLOBAL_ATTRIBUTE9
4763 ,l_Item_rec.GLOBAL_ATTRIBUTE10
4764 ,l_Item_rec.GLOBAL_ATTRIBUTE11
4765 ,l_Item_rec.GLOBAL_ATTRIBUTE12
4766 ,l_Item_rec.GLOBAL_ATTRIBUTE13
4767 ,l_Item_rec.GLOBAL_ATTRIBUTE14
4768 ,l_Item_rec.GLOBAL_ATTRIBUTE15
4769 ,l_Item_rec.GLOBAL_ATTRIBUTE16
4770 ,l_Item_rec.GLOBAL_ATTRIBUTE17
4771 ,l_Item_rec.GLOBAL_ATTRIBUTE18
4772 ,l_Item_rec.GLOBAL_ATTRIBUTE19
4773 ,l_Item_rec.GLOBAL_ATTRIBUTE20
4774 ,SYSDATE
4775 ,FND_GLOBAL.user_id
4776 ,SYSDATE
4777 ,FND_GLOBAL.user_id
4778 ,FND_GLOBAL.login_id
4779 ,FND_GLOBAL.conc_request_id
4780 ,FND_GLOBAL.prog_appl_id
4781 ,FND_GLOBAL.conc_program_id
4782 ,SYSDATE
4783 ,l_Item_rec.VMI_MINIMUM_UNITS
4784 ,l_Item_rec.VMI_MINIMUM_DAYS
4785 ,l_Item_rec.VMI_MAXIMUM_UNITS
4786 ,l_Item_rec.VMI_MAXIMUM_DAYS
4787 ,l_Item_rec.VMI_FIXED_ORDER_QUANTITY
4788 ,l_Item_rec.SO_AUTHORIZATION_FLAG
4789 ,l_Item_rec.CONSIGNED_FLAG
4790 ,l_Item_rec.ASN_AUTOEXPIRE_FLAG
4791 ,l_Item_rec.VMI_FORECAST_TYPE
4792 ,l_Item_rec.FORECAST_HORIZON
4793 ,l_Item_rec.EXCLUDE_FROM_BUDGET_FLAG
4794 ,l_Item_rec.DAYS_TGT_INV_SUPPLY
4795 ,l_Item_rec.DAYS_TGT_INV_WINDOW
4796 ,l_Item_rec.DAYS_MAX_INV_SUPPLY
4797 ,l_Item_rec.DAYS_MAX_INV_WINDOW
4798 ,l_Item_rec.DRP_PLANNED_FLAG
4799 ,l_Item_rec.CRITICAL_COMPONENT_FLAG
4800 ,l_Item_rec.CONTINOUS_TRANSFER
4801 ,l_Item_rec.CONVERGENCE
4802 ,l_Item_rec.DIVERGENCE
4803 /* Start Bug 3713912 */
4804 ,l_Item_rec.LOT_DIVISIBLE_FLAG
4805 ,l_Item_rec.GRADE_CONTROL_FLAG
4806 ,l_Item_rec.DEFAULT_GRADE
4807 ,l_Item_rec.CHILD_LOT_FLAG
4808 ,l_Item_rec.PARENT_CHILD_GENERATION_FLAG
4809 ,l_Item_rec.CHILD_LOT_PREFIX
4810 ,l_Item_rec.CHILD_LOT_STARTING_NUMBER
4811 ,l_Item_rec.CHILD_LOT_VALIDATION_FLAG
4812 ,l_Item_rec.COPY_LOT_ATTRIBUTE_FLAG
4813 ,l_Item_rec.RECIPE_ENABLED_FLAG
4814 ,l_Item_rec.PROCESS_QUALITY_ENABLED_FLAG
4815 ,l_Item_rec.PROCESS_EXECUTION_ENABLED_FLAG
4816 ,l_Item_rec.PROCESS_COSTING_ENABLED_FLAG
4817 ,l_Item_rec.PROCESS_SUPPLY_SUBINVENTORY
4818 ,l_Item_rec.PROCESS_SUPPLY_LOCATOR_ID
4819 ,l_Item_rec.PROCESS_YIELD_SUBINVENTORY
4820 ,l_Item_rec.PROCESS_YIELD_LOCATOR_ID
4821 ,l_Item_rec.HAZARDOUS_MATERIAL_FLAG
4822 ,l_Item_rec.CAS_NUMBER
4823 ,l_Item_rec.RETEST_INTERVAL
4824 ,l_Item_rec.EXPIRATION_ACTION_INTERVAL
4825 ,l_Item_rec.EXPIRATION_ACTION_CODE
4826 ,l_Item_rec.MATURITY_DAYS
4827 ,l_Item_rec.HOLD_DAYS
4828 /* End Bug 3713912 */
4829 /* Bug: 3933277 */
4830 ,l_Item_rec.Lifecycle_Id
4831 ,l_Item_rec.Current_Phase_Id
4832 /* Bug: 3933277 */
4833 /* R12 Enhancement */
4834 ,l_Item_rec.CHARGE_PERIODICITY_CODE
4835 ,l_Item_rec.REPAIR_LEADTIME
4836 ,l_Item_rec.REPAIR_YIELD
4837 ,l_Item_rec.PREPOSITION_POINT
4838 ,l_Item_rec.REPAIR_PROGRAM
4839 ,l_Item_rec.SUBCONTRACTING_COMPONENT
4840 ,l_Item_rec.OUTSOURCED_ASSEMBLY
4841 --R12 C Attributes
4842 ,l_Item_rec.GDSN_OUTBOUND_ENABLED_FLAG
4843 ,l_Item_rec.TRADE_ITEM_DESCRIPTOR
4844 ,l_Item_rec.STYLE_ITEM_FLAG
4845 ,l_Item_rec.STYLE_ITEM_ID
4846 ) RETURNING ROWID INTO l_item_rowid;
4847
4848 Insert_Revision_Record(
4849 p_item_rowid => l_item_rowid
4850 ,p_Revision_rec => p_Revision_rec
4851 ,p_set_process_id => l_set_process_id
4852 ,x_return_status => x_return_status
4853 ,x_return_err => x_return_err);
4854
4855
4856 IF ( fnd_api.to_Boolean (p_commit) ) THEN
4857 COMMIT WORK;
4858 END IF;
4859
4860 EXCEPTION
4861
4862 WHEN others THEN
4863 ROLLBACK TO Insert_MSII_Row;
4864 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
4865 x_return_err := 'INV_ITEM_GRP.Insert_MSII_Row: Unexpexted error: ' || SQLERRM;
4866
4867 END Insert_MSII_Row;
4868
4869 PROCEDURE Insert_Revision_Record(
4870 p_item_rowid IN ROWID
4871 ,p_Revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
4872 ,p_set_process_id IN NUMBER
4873 ,x_return_status OUT NOCOPY VARCHAR2
4874 ,x_return_err OUT NOCOPY VARCHAR2)
4875 IS
4876
4877 p_Upd_Null_CHAR VARCHAR2(1);
4878 p_Upd_Null_NUM NUMBER;
4879 p_Upd_Null_DATE DATE;
4880 l_default_revision mtl_item_revisions_interface.revision%TYPE;
4881 l_temp_integer INTEGER;
4882 l_item_number mtl_item_revisions_interface.item_number%TYPE := NULL;
4883 l_item_id mtl_item_revisions_interface.inventory_item_id%TYPE := NULL;
4884 l_error_text VARCHAR2(1000);
4885
4886 BEGIN
4887
4888 x_return_status := fnd_api.g_RET_STS_SUCCESS;
4889
4890 IF UPPER(p_Revision_rec.TRANSACTION_TYPE) IN ('CREATE','UPDATE','SYNC') THEN
4891
4892 SELECT starting_revision
4893 INTO l_default_revision
4894 FROM mtl_parameters
4895 WHERE organization_id = p_Revision_rec.ORGANIZATION_ID;
4896
4897 l_item_number := p_Revision_rec.ITEM_NUMBER;
4898
4899 IF (l_item_number IS NULL OR l_item_number = G_MISS_CHAR) THEN
4900 l_temp_integer:=INVPUOPI.mtl_pr_parse_item_segments
4901 (p_row_id => p_item_rowid
4902 ,item_number => l_item_number
4903 ,item_id => l_item_id
4904 ,err_text => l_error_text);
4905 END IF;
4906
4907 IF ( UPPER(p_Revision_rec.TRANSACTION_TYPE) = 'UPDATE' ) THEN
4908 p_Upd_Null_CHAR := '!';
4909 p_Upd_Null_NUM := -999999;
4910 p_Upd_Null_DATE := NULL;
4911 ELSE
4912 p_Upd_Null_CHAR := NULL;
4913 p_Upd_Null_NUM := NULL;
4914 p_Upd_Null_DATE := NULL;
4915 END IF;
4916
4917 INSERT INTO MTL_ITEM_REVISIONS_INTERFACE (
4918 INVENTORY_ITEM_ID
4919 ,ORGANIZATION_ID
4920 ,REVISION
4921 ,EFFECTIVITY_DATE
4922 ,ATTRIBUTE_CATEGORY
4923 ,ATTRIBUTE1
4924 ,ATTRIBUTE2
4925 ,ATTRIBUTE3
4926 ,ATTRIBUTE4
4927 ,ATTRIBUTE5
4928 ,ATTRIBUTE6
4929 ,ATTRIBUTE7
4930 ,ATTRIBUTE8
4931 ,ATTRIBUTE9
4932 ,ATTRIBUTE10
4933 ,ATTRIBUTE11
4934 ,ATTRIBUTE12
4935 ,ATTRIBUTE13
4936 ,ATTRIBUTE14
4937 ,ATTRIBUTE15
4938 ,DESCRIPTION
4939 ,ITEM_NUMBER
4940 ,PROCESS_FLAG
4941 ,TRANSACTION_TYPE
4942 ,SET_PROCESS_ID
4943 ,REVISION_ID
4944 ,REVISION_LABEL
4945 ,LIFECYCLE_ID
4946 ,CURRENT_PHASE_ID
4947 ,TEMPLATE_ID
4948 ,TEMPLATE_NAME
4949 ,CREATION_DATE
4950 ,CREATED_BY
4951 ,LAST_UPDATE_DATE
4952 ,LAST_UPDATED_BY
4953 ,LAST_UPDATE_LOGIN
4954 ,REQUEST_ID
4955 ,PROGRAM_APPLICATION_ID
4956 ,PROGRAM_ID
4957 ,PROGRAM_UPDATE_DATE)
4958 VALUES
4959 (NVL(DECODE(p_Revision_rec.INVENTORY_ITEM_ID , g_Null_NUM , p_Upd_Null_NUM , g_MISS_NUM , NULL, p_Revision_rec.INVENTORY_ITEM_ID),l_item_id)
4960 ,DECODE(p_Revision_rec.ORGANIZATION_ID , g_Null_NUM , p_Upd_Null_NUM , g_MISS_NUM , NULL, p_Revision_rec.ORGANIZATION_ID)
4961 ,NVL(DECODE(p_Revision_rec.revision_code , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.revision_code),l_default_revision)
4962 ,NVL(DECODE(p_Revision_rec.EFFECTIVITY_DATE , g_Null_DATE, p_Upd_Null_DATE, g_MISS_DATE, NULL, p_Revision_rec.EFFECTIVITY_DATE),SYSDATE)
4963 ,DECODE(p_Revision_rec.ATTRIBUTE_CATEGORY, g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE_CATEGORY)
4964 ,DECODE(p_Revision_rec.ATTRIBUTE1 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE1)
4965 ,DECODE(p_Revision_rec.ATTRIBUTE2 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE2)
4966 ,DECODE(p_Revision_rec.ATTRIBUTE3 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE3)
4967 ,DECODE(p_Revision_rec.ATTRIBUTE4 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE4)
4968 ,DECODE(p_Revision_rec.ATTRIBUTE5 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE5)
4969 ,DECODE(p_Revision_rec.ATTRIBUTE6 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE6)
4970 ,DECODE(p_Revision_rec.ATTRIBUTE7 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE7)
4971 ,DECODE(p_Revision_rec.ATTRIBUTE8 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE8)
4972 ,DECODE(p_Revision_rec.ATTRIBUTE9 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE9)
4973 ,DECODE(p_Revision_rec.ATTRIBUTE10 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE10)
4974 ,DECODE(p_Revision_rec.ATTRIBUTE11 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE11)
4975 ,DECODE(p_Revision_rec.ATTRIBUTE12 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE12)
4976 ,DECODE(p_Revision_rec.ATTRIBUTE13 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE13)
4977 ,DECODE(p_Revision_rec.ATTRIBUTE14 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE14)
4978 ,DECODE(p_Revision_rec.ATTRIBUTE15 , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ATTRIBUTE15)
4979 ,DECODE(p_Revision_rec.DESCRIPTION , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.DESCRIPTION)
4980 ,NVL(DECODE(p_Revision_rec.ITEM_NUMBER , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.ITEM_NUMBER),l_item_number)
4981 ,1
4982 ,p_Revision_rec.TRANSACTION_TYPE
4983 ,P_SET_PROCESS_ID
4984 ,DECODE(p_Revision_rec.REVISION_ID , g_Null_NUM , p_Upd_Null_NUM , g_MISS_NUM , NULL, p_Revision_rec.REVISION_ID)
4985 ,DECODE(p_Revision_rec.REVISION_LABEL , g_Null_CHAR, p_Upd_Null_CHAR, g_MISS_CHAR, NULL, p_Revision_rec.REVISION_LABEL)
4986 ,DECODE(p_Revision_rec.LIFECYCLE_ID , g_Null_NUM , p_Upd_Null_NUM , g_MISS_NUM , NULL, p_Revision_rec.LIFECYCLE_ID)
4987 ,DECODE(p_Revision_rec.CURRENT_PHASE_ID , g_Null_NUM , p_Upd_Null_NUM , g_MISS_NUM , NULL, p_Revision_rec.CURRENT_PHASE_ID)
4988 --5208102: Supporting template for UDA's at revisions
4989 ,DECODE(p_Revision_rec.TEMPLATE_ID ,g_Null_NUM ,p_Upd_Null_NUM ,g_MISS_NUM ,NULL ,p_Revision_rec.TEMPLATE_ID)
4990 ,DECODE(p_Revision_rec.TEMPLATE_NAME ,g_Null_CHAR ,p_Upd_Null_CHAR ,g_MISS_CHAR ,NULL ,p_Revision_rec.TEMPLATE_NAME)
4991 ,SYSDATE
4992 ,FND_GLOBAL.user_id
4993 ,SYSDATE
4994 ,FND_GLOBAL.user_id
4995 ,FND_GLOBAL.login_id
4996 ,FND_GLOBAL.conc_request_id
4997 ,FND_GLOBAL.prog_appl_id
4998 ,FND_GLOBAL.conc_program_id
4999 ,SYSDATE);
5000 END IF;
5001
5002 EXCEPTION
5003 WHEN OTHERS THEN
5004 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5005 x_return_err := 'INV_ITEM_GRP.Insert_Revision_Record: Unexpexted error: ' || SQLERRM;
5006 END Insert_Revision_Record;
5007
5008 -- -------------------- Get_Item ---------------------
5009
5010 PROCEDURE Get_Item
5011 (
5012 p_Item_Number IN VARCHAR2 := fnd_api.g_MISS_CHAR
5013 , p_Item_ID IN NUMBER := fnd_api.g_MISS_NUM
5014 , p_Org_ID IN NUMBER
5015 , p_Language_Code IN VARCHAR2 := fnd_api.g_MISS_CHAR
5016 , x_Item_rec OUT NOCOPY INV_ITEM_GRP.Item_rec_type
5017 , x_return_status OUT NOCOPY VARCHAR2
5018 , x_return_err OUT NOCOPY VARCHAR2
5019 )
5020 IS
5021 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item';
5022 l_return_status VARCHAR2(1);
5023 BEGIN
5024
5025 -- Initialize API return status to success
5026 --
5027 x_return_status := fnd_api.g_RET_STS_SUCCESS;
5028
5029 IF ( p_Item_ID = fnd_api.g_MISS_NUM ) OR ( p_Item_ID IS NULL ) OR
5030 ( p_Org_ID = fnd_api.g_MISS_NUM ) OR ( p_Org_ID IS NULL )
5031 THEN
5032 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5033 x_return_err := 'INV_ITEM_GRP.Get_Item: INV_MISS_ORG_ITEM_ID';
5034 RETURN;
5035 /*
5036 fnd_message.SET_NAME( 'INV', 'INV_MISS_ORG_ITEM_ID' );
5037 fnd_msg_pub.Add;
5038 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
5039 */
5040 END IF;
5041
5042 --------------------------------------------
5043 -- Open item query and fetch a first row.
5044 --------------------------------------------
5045
5046 OPEN Item_csr ( p_Item_ID => p_Item_ID
5047 , p_Org_ID => p_Org_ID
5048 );
5049
5050 FETCH Item_csr INTO x_Item_rec;
5051
5052 IF ( Item_csr%NOTFOUND ) THEN
5053 CLOSE Item_csr;
5054 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5055 x_return_err := 'INV_ITEM_GRP.Get_Item: Item not found.';
5056 RETURN;
5057 /*
5058 fnd_message.SET_NAME( 'INV', 'INV_ORG_ITEM_NOTFOUND' );
5059 fnd_msg_pub.Add;
5060 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
5061 */
5062 END IF;
5063
5064 CLOSE Item_csr;
5065
5066 EXCEPTION
5067
5068 WHEN others THEN
5069 IF ( Item_csr%ISOPEN ) THEN
5070 CLOSE Item_csr;
5071 END IF;
5072 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5073 x_return_err := 'INV_ITEM_GRP.Get_Item: Unexpected error: ' || SQLERRM;
5074
5075 END Get_Item;
5076
5077 PROCEDURE Interface_Handler
5078 (
5079 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
5080 ,p_transaction_type IN VARCHAR2
5081 ,p_Item_rec IN INV_ITEM_GRP.Item_Rec_Type
5082 ,P_revision_rec IN INV_ITEM_GRP.Item_Revision_Rec_Type
5083 ,p_Template_Id IN NUMBER
5084 ,p_Template_Name IN VARCHAR2
5085 ,x_batch_id OUT NOCOPY NUMBER
5086 ,x_return_status OUT NOCOPY VARCHAR2
5087 ,x_return_err OUT NOCOPY VARCHAR2
5088 )
5089 IS
5090 l_return_status VARCHAR2(1);
5091 l_return_err VARCHAR2(1000);
5092 l_error_exists NUMBER := 0;
5093 BEGIN
5094
5095 --Adding basic validations before creating item row in Interface
5096
5097 IF p_transaction_type NOT IN ('CREATE', 'UPDATE', 'SYNC') THEN
5098 l_return_err := 'INV_INVALID_TTYPE';
5099 l_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5100 l_error_exists := 1;
5101 END IF;
5102
5103 IF (P_item_rec.organization_id = g_MISS_NUM AND p_item_rec.organization_code = g_MISS_CHAR )
5104 THEN
5105 l_return_err := 'INV_ORG_CODE_MAND';
5106 l_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5107 l_error_exists := 1;
5108 END IF;
5109
5110 IF(p_transaction_type = 'CREATE' AND P_item_rec.item_number = g_MISS_CHAR AND
5111 P_item_rec.segment1 = g_MISS_CHAR AND P_item_rec.segment2 = g_MISS_CHAR AND
5112 P_item_rec.segment3 = g_MISS_CHAR AND P_item_rec.segment4 = g_MISS_CHAR AND
5113 P_item_rec.segment5 = g_MISS_CHAR AND P_item_rec.segment6 = g_MISS_CHAR AND
5114 P_item_rec.segment7 = g_MISS_CHAR AND P_item_rec.segment8 = g_MISS_CHAR AND
5115 P_item_rec.segment9 = g_MISS_CHAR AND P_item_rec.segment10 = g_MISS_CHAR AND
5116 P_item_rec.segment11 = g_MISS_CHAR AND P_item_rec.segment12 = g_MISS_CHAR AND
5117 P_item_rec.segment13 = g_MISS_CHAR AND P_item_rec.segment14 = g_MISS_CHAR AND
5118 P_item_rec.segment15 = g_MISS_CHAR AND P_item_rec.segment16 = g_MISS_CHAR AND
5119 P_item_rec.segment17 = g_MISS_CHAR AND P_item_rec.segment18 = g_MISS_CHAR AND
5120 P_item_rec.segment19 = g_MISS_CHAR AND P_item_rec.segment20 = g_MISS_CHAR)
5121 THEN
5122 l_return_err := 'INV_SEG_ITM_NUMB_VAL';
5123 l_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5124 l_error_exists := 1;
5125
5126 ELSIF(p_transaction_type = 'UPDATE' AND P_Item_rec.inventory_item_id = g_MISS_NUM AND
5127 P_item_rec.item_number = g_MISS_CHAR AND
5128 P_item_rec.segment1 = g_MISS_CHAR AND P_item_rec.segment2 = g_MISS_CHAR AND
5129 P_item_rec.segment3 = g_MISS_CHAR AND P_item_rec.segment4 = g_MISS_CHAR AND
5130 P_item_rec.segment5 = g_MISS_CHAR AND P_item_rec.segment6 = g_MISS_CHAR AND
5131 P_item_rec.segment7 = g_MISS_CHAR AND P_item_rec.segment8 = g_MISS_CHAR AND
5132 P_item_rec.segment9 = g_MISS_CHAR AND P_item_rec.segment10 = g_MISS_CHAR AND
5133 P_item_rec.segment11 = g_MISS_CHAR AND P_item_rec.segment12 = g_MISS_CHAR AND
5134 P_item_rec.segment13 = g_MISS_CHAR AND P_item_rec.segment14 = g_MISS_CHAR AND
5135 P_item_rec.segment15 = g_MISS_CHAR AND P_item_rec.segment16 = g_MISS_CHAR AND
5136 P_item_rec.segment17 = g_MISS_CHAR AND P_item_rec.segment18 = g_MISS_CHAR AND
5137 P_item_rec.segment19 = g_MISS_CHAR AND P_item_rec.segment20 = g_MISS_CHAR )
5138 THEN
5139 l_return_err := 'INV_SEG_ITM_NUMB_VAL';
5140 l_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5141 l_error_exists := 1;
5142 END IF;
5143
5144 IF l_error_exists = 0 THEN
5145 Insert_MSII_Row
5146 (
5147 p_commit => p_commit
5148 , p_transaction_type => p_transaction_type
5149 , p_Item_rec => p_Item_rec
5150 , p_revision_rec => p_revision_rec
5151 , p_Template_Id => p_Template_Id
5152 , p_Template_Name => p_Template_Name
5153 , x_set_process_id => x_batch_id
5154 , x_return_status => l_return_status
5155 , x_return_err => l_return_err
5156 );
5157
5158 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
5159 l_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
5160 l_return_err := SUBSTR(l_return_err,1,239);
5161 END IF;
5162 END IF;
5163
5164 x_return_status := l_return_status;
5165 x_return_err := l_return_err;
5166
5167 IF FND_API.To_Boolean(p_commit) THEN
5168 COMMIT WORK;
5169 END IF;
5170
5171 END Interface_Handler;
5172
5173
5174 END INV_ITEM_GRP;