1 PACKAGE BODY INVNIRIS AS
2 /* $Header: INVNIRIB.pls 120.37.12020000.2 2013/01/23 09:23:37 lmai ship $ */
3 ------------------------ validate_item_revs -----------------------------------
4
5 FUNCTION mtl_validate_nir_item
6 (
7 org_id number,
8 all_org NUMBER,
9 prog_appid NUMBER,
10 prog_id NUMBER,
11 request_id NUMBER,
12 user_id NUMBER,
13 login_id NUMBER,
14 xset_id IN NUMBER,
15 err_text in out NOCOPY varchar2
16 )
17 RETURN INTEGER
18 IS
19 LOGGING_ERR Exception;
20 status NUMBER;
21 l_sysdate DATE ;
22 l_nir_rec_exists BOOLEAN;
23 l_flag_error NUMBER;
24 l_item_approval_status mtl_system_items_b.approval_status%TYPE; --Bug 4418037
25 l_dumm_status NUMBER;
26 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
27 l_err_text VARCHAR2(1000);
28
29 /* R12C : Changing the New Item Req Reqd = 'Y' sub-query for hierarchy enabled Catalogs */
30 CURSOR c_get_processed_records(cp_process_flag NUMBER) IS
31 SELECT msii.INVENTORY_ITEM_ID,
32 msii.ITEM_CATALOG_GROUP_ID,
33 msii.ORGANIZATION_ID,
34 msii.TRANSACTION_ID,
35 msii.ITEM_NUMBER,
36 msii.rowid,
37 msii.ENG_ITEM_FLAG,
38 -- micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
39 mp.ORGANIZATION_CODE,
40 mp.MASTER_ORGANIZATION_ID,
41 msii.TRANSACTION_TYPE
42 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
43 --MTL_ITEM_CATALOG_GROUPS_B micb,
44 MTL_PARAMETERS mp
45 WHERE ( (msii.organization_id + 0 = org_id) OR (all_Org = 1) )
46 AND msii.process_flag = cp_process_flag
47 AND msii.set_process_id = xset_id
48 --AND msii.ITEM_CATALOG_GROUP_ID = micb.ITEM_CATALOG_GROUP_ID
49 --AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
50 AND mp.ORGANIZATION_ID = msii.ORGANIZATION_ID
51 FOR UPDATE OF msii.INVENTORY_ITEM_ID;
52
53 /* R12C : Introducing cursor for hierarchy enabled Catalogs */
54 CURSOR c_nir_reqd (cp_item_catalog_group_id IN NUMBER)
55 IS
56 SELECT ICC.NEW_ITEM_REQUEST_REQD
57 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
58 WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
59 AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
60 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
61 START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_item_catalog_group_id
62 ORDER BY LEVEL ASC;
63
64 --4676583 : Honouring batch option - Add All Imported Items to Change Order
65 CURSOR c_get_batch_policy IS
66 SELECT NVL(add_all_to_change_flag,'N')
67 FROM ego_import_option_sets
68 WHERE batch_id = xset_id;
69
70 l_import_co_option VARCHAR2(1) := 'N';
71 l_nir_reqd VARCHAR2(1) := 'N';
72
73 BEGIN
74 status := 0;
75 l_sysdate := sysdate;
76 l_nir_rec_exists := false;
77
78 IF l_inv_debug_level IN(101, 102) THEN
79 INVPUTLI.info('INVNIRIS: first sta..set_id'||to_char(xset_id)||'org'||to_char(org_id)||'all'||to_char(all_org));
80 END IF;
81
82 FOR cur in c_get_processed_records(cp_process_flag=>2) LOOP
83 /* R12C : Retrieving NIR reqd for hierarchy enabled Catalogs */
84 l_nir_reqd := 'N';
85
86 OPEN c_nir_reqd(cp_item_catalog_group_id => cur.item_catalog_group_id);
87 FETCH c_nir_reqd INTO l_nir_reqd;
88 CLOSE c_nir_reqd;
89
90 IF l_nir_reqd = 'Y' THEN
91 l_flag_error := 0;
92 --Start : Child Items for a master item which is unapproved cannot be created.
93 IF cur.TRANSACTION_TYPE = 'UPDATE' THEN
94
95 BEGIN
96 SELECT approval_status
97 INTO l_item_approval_status
101 EXCEPTION
98 FROM mtl_system_items_b
99 WHERE inventory_item_id = cur.INVENTORY_ITEM_ID
100 AND organization_id = cur.ORGANIZATION_ID;
102 WHEN OTHERS THEN
103 l_item_approval_status := 'N';
104 END;
105
106 ELSIF cur.TRANSACTION_TYPE = 'CREATE' THEN
107
108 IF cur.organization_id <> cur.master_organization_id THEN
109
110 BEGIN
111 SELECT msi.approval_status
112 INTO l_item_approval_status
113 FROM mtl_system_items_b msi
114 WHERE msi.inventory_item_id = cur.inventory_item_id
115 AND msi.organization_id = (SELECT mp.master_organization_id
116 FROM mtl_parameters mp
117 WHERE mp.organization_id = cur.organization_id);
118 EXCEPTION
119 WHEN OTHERS THEN
120 l_item_approval_status := 'N';
121 END;
122
123 IF l_item_approval_status <> 'A' THEN
124 UPDATE mtl_system_items_interface
125 SET process_flag = 3
126 WHERE CURRENT OF c_get_processed_records;
127
128 l_dumm_status := INVPUOPI.mtl_log_interface_err (
129 -1,
130 user_id,
131 login_id,
132 prog_appid,
133 prog_id,
134 request_id,
135 cur.transaction_id,
136 'Unapproved Item cannot be assigned to a child org',
137 'APPROVAL_STATUS',
138 'MTL_SYSTEM_ITEMS_INTERFACE',
139 'INV_NIR_NO_APPROVE',
140 l_err_text);
141 IF l_dumm_status < 0 then
142 raise LOGGING_ERR;
143 END IF;
144 l_flag_error := 1;
145 END IF;
146 END IF;
147 END IF;
148
149 IF ( l_flag_error = 0 ) THEN
150 IF (((cur.TRANSACTION_TYPE = 'CREATE') AND (cur.organization_id = cur.master_organization_id))
151 OR((cur.TRANSACTION_TYPE = 'UPDATE') AND (NVL(l_item_approval_status, 'A') <> 'A')))
152 THEN
153 l_nir_rec_exists := true;
154
155 UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
156 SET I.SET_PROCESS_ID = I.SET_PROCESS_ID + 3000000000000,
157 I.ENG_ITEM_FLAG = 'Y', --Bug 5519768
158 I.SUMMARY_FLAG = 'Y',
159 I.ENABLED_FLAG = 'Y',
160 I.BUYER_ID = NULL,
161 I.ACCOUNTING_RULE_ID = NULL,
162 I.INVOICING_RULE_ID = NULL,
163 I.COLLATERAL_FLAG = NULL,
164 I.STOCK_ENABLED_FLAG = 'N',
165 I.MTL_TRANSACTIONS_ENABLED_FLAG = 'N',
166 I.INTERNAL_ORDER_ENABLED_FLAG = 'N',
167 I.INVOICE_ENABLED_FLAG = 'N',
168 -- Bug 5738958: BOM Allowed Flag can be updated even when Item Status is Pending
169 -- I.BOM_ENABLED_FLAG = 'N',
170 I.BUILD_IN_WIP_FLAG = 'N',
171 I.CUSTOMER_ORDER_ENABLED_FLAG = 'N',
172 I.PURCHASING_ENABLED_FLAG = 'N',
173 I.INVENTORY_ITEM_FLAG = 'N',
174 I.WIP_SUPPLY_TYPE = 1,
175 I.AUTO_CREATED_CONFIG_FLAG = 'N',
176 I.CYCLE_COUNT_ENABLED_FLAG = 'N',
177 I.INTERNAL_ORDER_FLAG = 'N',
178 I.INVENTORY_ITEM_STATUS_CODE = 'Pending',
179 I.INVENTORY_PLANNING_CODE = 6,
180 I.MRP_PLANNING_CODE = 6,
181 I.INVENTORY_ASSET_FLAG = 'N',
182 I.INVOICEABLE_ITEM_FLAG = 'N',
183 I.EXPENSE_BILLABLE_FLAG = NULL,
184 I.BOM_ITEM_TYPE = 4,
185 I.COSTING_ENABLED_FLAG = 'N',
186 I.CUSTOMER_ORDER_FLAG = 'N',
187 -- I.ALLOWED_UNITS_LOOKUP_CODE = 3, --BUG 7255713
188 I.ATP_COMPONENTS_FLAG = 'N',
189 I.ATP_FLAG = 'N',
190 I.TIME_BILLABLE_FLAG = NULL,
191 I.SERVICEABLE_PRODUCT_FLAG = 'N',
192 I.SHELF_LIFE_CODE = 1,
193 I.SHIPPABLE_ITEM_FLAG = 'N',
194 I.SO_TRANSACTIONS_FLAG = 'N',
195 I.SERVICEABLE_COMPONENT_FLAG = 'N',
196 I.REPLENISH_TO_ORDER_FLAG = 'N',
197 I.RESERVABLE_TYPE = 1,
198 I.RESTRICT_LOCATORS_CODE = 2,
199 I.RESTRICT_SUBINVENTORIES_CODE = 2,
200 I.REVISION_QTY_CONTROL_CODE = 1,
201 I.SERIAL_NUMBER_CONTROL_CODE = 1,
202 I.PREVENTIVE_MAINTENANCE_FLAG = 'N',
203 I.SERV_BILLING_ENABLED_FLAG = 'N',
204 I.PRORATE_SERVICE_FLAG = 'N',
205 I.PURCHASING_ITEM_FLAG = 'N',
206 I.OUTSIDE_OPERATION_FLAG = 'N',
207 I.PICK_COMPONENTS_FLAG = 'N',
208 I.PLANNING_MAKE_BUY_CODE = 2,
209 I.PLANNING_TIME_FENCE_CODE = 4,
213 I.LOT_CONTROL_CODE = 1,
210 I.PLANNING_TIME_FENCE_DAYS = 1,
211 I.MUST_USE_APPROVED_VENDOR_FLAG = 'N',
212 I.LOCATION_CONTROL_CODE = 1,
214 I.MRP_SAFETY_STOCK_CODE = 1,
215 I.SHIP_MODEL_COMPLETE_FLAG = 'N',
216 I.MARKET_PRICE = NULL,
217 I.LIST_PRICE_PER_UNIT = NULL,
218 I.PRICE_TOLERANCE_PERCENT = NULL,
219 I.SHELF_LIFE_DAYS = 0,
220 I.REPETITIVE_PLANNING_FLAG = 'N',
221 I.ACCEPTABLE_RATE_DECREASE = 0,
222 I.ACCEPTABLE_RATE_INCREASE = 0,
223 I.POSTPROCESSING_LEAD_TIME = 0,
224 I.RETURN_INSPECTION_REQUIREMENT = 2,
225 I.CONTAINER_ITEM_FLAG = 'N',
226 I.VEHICLE_ITEM_FLAG = 'N',
227 I.SERVICE_DURATION = NULL,
228 I.RETURNABLE_FLAG = 'N',
229 I.LEAD_TIME_LOT_SIZE = 1,
230 I.CHECK_SHORTAGES_FLAG = 'N',
231 I.EFFECTIVITY_CONTROL = 1,
232 I.EQUIPMENT_TYPE = 2,
233 I.COMMS_NL_TRACKABLE_FLAG = NULL,
234 I.WEB_STATUS = 'UNPUBLISHED',
235 I.BULK_PICKED_FLAG = 'N',
236 I.LOT_STATUS_ENABLED = 'N',
237 I.DEFAULT_LOT_STATUS_ID = NULL,
238 I.SERIAL_STATUS_ENABLED = 'N',
239 I.DEFAULT_SERIAL_STATUS_ID = NULL,
240 I.DUAL_UOM_CONTROL = 1,
241 I.LOT_SPLIT_ENABLED = 'N',
242 I.LOT_MERGE_ENABLED = 'N',
243 I.LOT_TRANSLATE_ENABLED = 'N',
244 I.DEFAULT_SO_SOURCE_TYPE = 'INTERNAL',
245 I.CREATE_SUPPLY_FLAG = 'Y',
246 -- I.TRACKING_QUANTITY_IND = 'P', --BUG 7255713
247 -- I.ONT_PRICING_QTY_SOURCE = 'P', --BUG 7255713
248 -- I.DUAL_UOM_DEVIATION_HIGH = 0, --BUG 7255713
249 -- I.DUAL_UOM_DEVIATION_LOW = 0, --BUG 7255713
250 I.VMI_MINIMUM_UNITS = NULL,
251 I.VMI_MINIMUM_DAYS = NULL,
252 I.VMI_MAXIMUM_UNITS = NULL,
253 I.VMI_MAXIMUM_DAYS = NULL,
254 I.VMI_FIXED_ORDER_QUANTITY = NULL,
255 I.SO_AUTHORIZATION_FLAG = NULL,
256 I.CONSIGNED_FLAG = 2,
257 I.ASN_AUTOEXPIRE_FLAG = 2,
258 I.VMI_FORECAST_TYPE = 1,
259 I.FORECAST_HORIZON = NULL,
260 I.EXCLUDE_FROM_BUDGET_FLAG = 2,
261 I.DAYS_TGT_INV_SUPPLY = NULL,
262 I.DAYS_TGT_INV_WINDOW = NULL,
263 I.DAYS_MAX_INV_SUPPLY = NULL,
264 I.DAYS_MAX_INV_WINDOW = NULL,
265 I.DRP_PLANNED_FLAG = 2,
266 I.CRITICAL_COMPONENT_FLAG = 2,
267 I.CONTINOUS_TRANSFER = 3,
268 I.CONVERGENCE = 3,
269 I.DIVERGENCE = 3,
270 I.ACCEPTABLE_EARLY_DAYS = NULL,
271 I.ALLOW_EXPRESS_DELIVERY_FLAG = NULL,
272 I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG = NULL,
273 I.ALLOW_UNORDERED_RECEIPTS_FLAG = NULL,
274 I.ASSET_CATEGORY_ID = NULL,
275 I.ASSET_CREATION_CODE = NULL,
276 I.ATO_FORECAST_CONTROL = NULL,
277 I.ATP_RULE_ID = NULL,
278 I.AUTO_LOT_ALPHA_PREFIX = NULL,
279 I.AUTO_REDUCE_MPS = NULL,
280 I.AUTO_SERIAL_ALPHA_PREFIX = NULL,
281 I.BACK_ORDERABLE_FLAG = NULL,
282 I.BASE_ITEM_ID = NULL,
283 I.BASE_WARRANTY_SERVICE_ID = NULL,
284 I.CARRYING_COST = NULL,
285 I.CATALOG_STATUS_FLAG = NULL,
286 I.COMMS_ACTIVATION_REQD_FLAG = NULL,
287 I.CONFIG_MATCH = NULL,
288 I.CONFIG_MODEL_TYPE = NULL,
289 I.CONFIG_ORGS = NULL,
290 I.CONTAINER_TYPE_CODE = NULL,
291 I.CONTRACT_ITEM_TYPE_CODE = NULL,
292 I.COUPON_EXEMPT_FLAG = NULL,
293 I.COVERAGE_SCHEDULE_ID = NULL,
294 I.CUM_MANUFACTURING_LEAD_TIME = NULL,
295 I.CUMULATIVE_TOTAL_LEAD_TIME = NULL,
296 I.DAYS_EARLY_RECEIPT_ALLOWED = NULL,
297 I.DAYS_LATE_RECEIPT_ALLOWED = NULL,
298 I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG = NULL,
299 I.DEFAULT_SHIPPING_ORG = NULL,
300 I.DEFECT_TRACKING_ON_FLAG = NULL,
301 I.DEMAND_TIME_FENCE_CODE = NULL,
302 I.DEMAND_TIME_FENCE_DAYS = NULL,
303 I.DIMENSION_UOM_CODE = NULL,
304 I.DOWNLOADABLE_FLAG = NULL,
305 I.EAM_ACT_NOTIFICATION_FLAG = NULL,
309 I.EAM_ACTIVITY_TYPE_CODE = NULL,
306 I.EAM_ACT_SHUTDOWN_STATUS = NULL,
307 I.EAM_ACTIVITY_CAUSE_CODE = NULL,
308 I.EAM_ACTIVITY_SOURCE_CODE = NULL,
310 I.EAM_ITEM_TYPE = NULL,
311 I.ELECTRONIC_FLAG = NULL,
312 I.END_ASSEMBLY_PEGGING_FLAG = NULL,
313 I.END_DATE_ACTIVE = NULL,
314 I.ENFORCE_SHIP_TO_LOCATION_CODE = NULL,
315 I.ENGINEERING_DATE = NULL,
316 I.ENGINEERING_ECN_CODE = NULL,
317 I.ENGINEERING_ITEM_ID = NULL,
318 I.EVENT_FLAG = NULL,
319 I.FINANCING_ALLOWED_FLAG = NULL,
320 I.FIXED_DAYS_SUPPLY = NULL,
321 I.FIXED_LEAD_TIME = NULL,
322 I.FIXED_LOT_MULTIPLIER = NULL,
323 I.FIXED_ORDER_QUANTITY = NULL,
324 I.FULL_LEAD_TIME = NULL,
325 I.HAZARD_CLASS_ID = NULL,
326 I.IB_ITEM_INSTANCE_CLASS = NULL,
327 I.INDIVISIBLE_FLAG = NULL,
328 I.INSPECTION_REQUIRED_FLAG = NULL,
329 I.INTERNAL_VOLUME = NULL,
330 I.INVENTORY_CARRY_PENALTY = NULL,
331 I.INVOICE_CLOSE_TOLERANCE = NULL,
332 I.LOT_SUBSTITUTION_ENABLED = NULL,
333 I.MATERIAL_BILLABLE_FLAG = NULL,
334 I.MAX_MINMAX_QUANTITY = NULL,
335 I.MAX_WARRANTY_AMOUNT = NULL,
336 I.MAXIMUM_LOAD_WEIGHT = NULL,
337 I.MAXIMUM_ORDER_QUANTITY = NULL,
338 I.MIN_MINMAX_QUANTITY = NULL,
339 I.MINIMUM_FILL_PERCENT = NULL,
340 I.MINIMUM_LICENSE_QUANTITY = NULL,
341 I.MINIMUM_ORDER_QUANTITY = NULL,
342 I.MODEL_CONFIG_CLAUSE_NAME = NULL,
343 I.MRP_CALCULATE_ATP_FLAG = NULL,
344 I.MRP_SAFETY_STOCK_PERCENT = NULL,
345 I.NEGATIVE_MEASUREMENT_ERROR = NULL,
346 I.OPERATION_SLACK_PENALTY = NULL,
347 I.ORDER_COST = NULL,
348 I.ORDERABLE_ON_WEB_FLAG = NULL,
349 I.OUTSIDE_OPERATION_UOM_TYPE = NULL,
350 I.OVER_RETURN_TOLERANCE = NULL,
351 I.OVER_SHIPMENT_TOLERANCE = NULL,
352 I.OVERCOMPLETION_TOLERANCE_TYPE = NULL,
353 I.OVERCOMPLETION_TOLERANCE_VALUE = NULL,
354 I.OVERRUN_PERCENTAGE = NULL,
355 I.PAYMENT_TERMS_ID = NULL,
356 I.PICKING_RULE_ID = NULL,
357 I.PLANNED_INV_POINT_FLAG = NULL,
358 I.PLANNER_CODE = NULL,
359 I.PLANNING_EXCEPTION_SET = NULL,
360 I.POSITIVE_MEASUREMENT_ERROR = NULL,
361 I.PREPROCESSING_LEAD_TIME = NULL,
362 I.PRIMARY_SPECIALIST_ID = NULL,
363 I.PRODUCT_FAMILY_ITEM_ID = NULL,
364 I.PURCHASING_TAX_CODE = NULL,
365 I.QTY_RCV_EXCEPTION_CODE = NULL,
366 I.QTY_RCV_TOLERANCE = NULL,
367 I.RECEIPT_DAYS_EXCEPTION_CODE = NULL,
368 I.RECEIVE_CLOSE_TOLERANCE = NULL,
369 I.RECEIVING_ROUTING_ID = NULL,
370 I.RECOVERED_PART_DISP_CODE = NULL,
371 I.RELEASE_TIME_FENCE_CODE = NULL,
372 I.RELEASE_TIME_FENCE_DAYS = NULL,
373 I.RESPONSE_TIME_PERIOD_CODE = NULL,
374 I.RESPONSE_TIME_VALUE = NULL,
375 I.ROUNDING_CONTROL_TYPE = NULL,
376 I.ROUNDING_FACTOR = NULL,
377 I.SAFETY_STOCK_BUCKET_DAYS = NULL,
378 -- I.SECONDARY_DEFAULT_IND = NULL, BUG 7255713
379 I.SECONDARY_SPECIALIST_ID = NULL,
380 -- I.SECONDARY_UOM_CODE = NULL, BUG 7255713
381 I.SERV_IMPORTANCE_LEVEL = NULL,
382 I.SERV_REQ_ENABLED_CODE = NULL,
383 I.SERVICE_DURATION_PERIOD_CODE = NULL,
384 I.SERVICE_ITEM_FLAG = NULL,
385 I.SERVICE_STARTING_DELAY = NULL,
386 I.SERVICEABLE_ITEM_CLASS_ID = NULL,
387 I.SHRINKAGE_RATE = NULL,
388 I.SOURCE_ORGANIZATION_ID = NULL,
389 I.SOURCE_SUBINVENTORY = NULL,
390 I.SOURCE_TYPE = NULL,
391 I.START_AUTO_LOT_NUMBER = NULL,
392 I.START_AUTO_SERIAL_NUMBER = NULL,
393 I.START_DATE_ACTIVE = NULL,
394 I.STD_LOT_SIZE = NULL,
395 I.SUBSCRIPTION_DEPEND_FLAG = NULL,
396 I.SUBSTITUTION_WINDOW_CODE = NULL,
397 I.SUBSTITUTION_WINDOW_DAYS = NULL,
398 I.TAX_CODE = NULL,
399 I.UN_NUMBER_ID = NULL,
400 I.UNDER_RETURN_TOLERANCE = NULL,
404 I.UNIT_OF_ISSUE = NULL,
401 I.UNDER_SHIPMENT_TOLERANCE = NULL,
402 I.UNIT_HEIGHT = NULL,
403 I.UNIT_LENGTH = NULL,
405 I.UNIT_VOLUME = NULL,
406 -- Unit Weight can be updated for Pending Items -R12 C
407 -- I.UNIT_WEIGHT = NULL,
408 I.UNIT_WIDTH = NULL,
409 I.USAGE_ITEM_FLAG = NULL,
410 I.VARIABLE_LEAD_TIME = NULL,
411 I.VENDOR_WARRANTY_FLAG = NULL,
412 I.VOL_DISCOUNT_EXEMPT_FLAG = NULL,
413 I.VOLUME_UOM_CODE = NULL,
414 I.WARRANTY_VENDOR_ID = NULL,
415 -- Weight UOM can be updated for Pending Items -R12 C
416 -- I.WEIGHT_UOM_CODE = NULL,
417 I.WH_UPDATE_DATE = NULL,
418 I.WIP_SUPPLY_LOCATOR_ID = NULL,
419 I.WIP_SUPPLY_SUBINVENTORY = NULL,
420 I.GLOBAL_ATTRIBUTE_CATEGORY = NULL,
421 I.GLOBAL_ATTRIBUTE1 = NULL,
422 I.GLOBAL_ATTRIBUTE2 = NULL,
423 I.GLOBAL_ATTRIBUTE3 = NULL,
424 I.GLOBAL_ATTRIBUTE4 = NULL,
425 I.GLOBAL_ATTRIBUTE5 = NULL,
426 I.GLOBAL_ATTRIBUTE6 = NULL,
427 I.GLOBAL_ATTRIBUTE7 = NULL,
428 I.GLOBAL_ATTRIBUTE8 = NULL,
429 I.GLOBAL_ATTRIBUTE9 = NULL,
430 I.GLOBAL_ATTRIBUTE10 = NULL,
431 I.ATTRIBUTE_CATEGORY = NULL,
432 I.ATTRIBUTE1 = NULL,
433 I.ATTRIBUTE2 = NULL,
434 I.ATTRIBUTE3 = NULL,
435 I.ATTRIBUTE4 = NULL,
436 I.ATTRIBUTE5 = NULL,
437 I.ATTRIBUTE6 = NULL,
438 I.ATTRIBUTE7 = NULL,
439 I.ATTRIBUTE8 = NULL,
440 I.ATTRIBUTE9 = NULL,
441 I.ATTRIBUTE10 = NULL,
442 I.ATTRIBUTE11 = NULL,
443 I.ATTRIBUTE12 = NULL,
444 I.ATTRIBUTE13 = NULL,
445 I.ATTRIBUTE14 = NULL,
446 I.ATTRIBUTE15 = NULL
447 WHERE I.ROWID = CUR.ROWID;
448
449 UPDATE MTL_ITEM_REVISIONS_INTERFACE
450 SET SET_PROCESS_ID = SET_PROCESS_ID + 3000000000000
451 WHERE INVENTORY_ITEM_ID = CUR.INVENTORY_ITEM_ID
452 AND ORGANIZATION_ID = CUR.ORGANIZATION_ID;
453
454 END IF;
455 END IF;
456 END IF; -- If New Item Req is YES
457 END LOOP;
458
459 --Start : Check for data security and user privileges
460 IF l_inv_debug_level IN(101, 102) THEN
461 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: before INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
462 END IF;
463
464 IF l_nir_rec_exists THEN
465 status := INV_EGO_REVISION_VALIDATE.validate_item_user_privileges(
466 P_Org_Id => org_id
467 ,P_All_Org => all_org
468 ,P_Prog_AppId => prog_appid
469 ,P_Prog_Id => prog_id
470 ,P_Request_Id => request_id
471 ,P_User_Id => user_id
472 ,P_Login_Id => login_id
473 ,P_Set_Id => xset_id + 3000000000000
474 ,X_Err_Text => err_text);
475
476 IF l_inv_debug_level IN(101, 102) THEN
477 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
478 END IF;
479
480 --End : Check for data security and user privileges
481
482 IF (status = 0) then
483 IF l_inv_debug_level IN(101, 102) THEN
484 INVPUTLI.info('INVNIRIS: before INVPVHDR.validate_item_header'||to_char(xset_id)||'org'||to_char(org_id)||'all'||to_char(all_org));
485 END IF;
486
487 status := INVPVHDR.validate_item_header(
488 org_id,
489 all_org,
490 prog_appid,
491 prog_id,
492 request_id,
493 user_id,
494 login_id,
495 err_text,
496 xset_id + 3000000000000);
497 END IF;
498
499 IF (status = 0) THEN
500
501 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
502 --SET PROCESS_FLAG = 4 BUG 7255713
503 SET PROCESS_FLAG = 44 -- added for bug 7255713-ccsingh
504 WHERE PROCESS_FLAG = 41
505 AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
506
507 IF l_inv_debug_level IN(101, 102) THEN
508 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: before INV_EGO_REVISION_VALIDATE.validate_items_lifecycle');
509 END IF;
510 /* start addition of code for bug 7255713-ccsingh */
511 status := INVPVDR5.validate_item_header5(
512 org_id,
513 all_org,
514 prog_appid,
515 prog_id,
516 request_id,
517 user_id,
521
518 login_id,
519 err_text,
520 xset_id + 3000000000000);
522
523 END IF ;
524
525 IF (status = 0) THEN
526
527 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
528 SET PROCESS_FLAG = 4
529 WHERE PROCESS_FLAG = 45 --Changed the value from 41 to 45 as we need to validate primary attributes
530 AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
531
532 IF l_inv_debug_level IN(101, 102) THEN
533 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: before INV_EGO_REVISION_VALIDATE.validate_items_lifecycle');
534 END IF;
535 /* end addition of code for bug 7255713-ccsingh */
536 status := INV_EGO_REVISION_VALIDATE.validate_items_lifecycle(
537 P_Org_Id => org_id
538 ,P_All_Org => all_org
539 ,P_Prog_AppId => prog_appid
540 ,P_Prog_Id => prog_id
541 ,P_Request_Id => request_id
542 ,P_User_Id => user_id
543 ,P_Login_Id => login_id
544 ,P_Set_Id => xset_id + 3000000000000
545 ,X_Err_Text => err_text);
546
547 IF l_inv_debug_level IN(101, 102) THEN
548 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: INV_EGO_REVISION_VALIDATE.validate_items_lifecycle');
549 END IF;
550
551 END IF;
552
553 -- validate item revisions
554 IF (status = 0) THEN
555 IF l_inv_debug_level IN(101, 102) THEN
556 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: before validate_item_revs');
557 END IF;
558 status := INVPVALI.validate_item_revs (
559 org_id,
560 all_org,
561 prog_appid,
562 prog_id,
563 request_id,
564 user_id,
565 login_id,
566 err_text,
567 xset_id + 3000000000000 );
568
569 IF l_inv_debug_level IN(101, 102) THEN
570 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: after validate_item_revs');
571 END IF;
572 END IF;
573 END IF;
574
575 IF l_inv_debug_level IN(101, 102) THEN
576 INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: done with status :'||status);
577 END IF;
578
579 UPDATE mtl_system_items_interface
580 SET SET_PROCESS_ID = xset_id
581 WHERE ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
582
583 UPDATE mtl_item_revisions_interface
584 SET SET_PROCESS_ID = xset_id
585 WHERE ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
586
587
588 OPEN c_get_batch_policy;
589 FETCH c_get_batch_policy INTO l_import_co_option;
590 CLOSE c_get_batch_policy;
591
592 IF NVL(l_import_co_option,'N') = 'Y' THEN
593
594 UPDATE mtl_item_revisions_interface i
595 SET i.process_flag = 5
596 WHERE i.process_flag = 2
597 AND i.set_process_id = xset_id
598 AND ((i.organization_id = org_id) or (all_org = 1))
599 AND i.transaction_type = 'CREATE'
600 AND i.revision <> (select m.starting_revision
601 from mtl_parameters m
602 where m.organization_id = i.organization_id);
603 END IF;
604
605 RETURN (status);
606
607 EXCEPTION
608
609 WHEN OTHERS THEN
610 err_text := substr('INVNIRIS.mtl_pr_validate_item ' || SQLERRM, 1,240);
611 IF l_inv_debug_level IN(101, 102) THEN
612 INVPUTLI.info(err_text);
613 END IF;
614 RETURN(SQLCODE);
615
616 END mtl_validate_nir_item;
617
618 FUNCTION change_policy_check(
619 org_id number,
620 all_org NUMBER DEFAULT 2,
621 prog_appid NUMBER DEFAULT -1,
622 prog_id NUMBER DEFAULT -1,
623 request_id NUMBER DEFAULT -1,
624 user_id NUMBER DEFAULT -1,
625 login_id NUMBER DEFAULT -1,
626 xset_id NUMBER DEFAULT -999,
627 err_text IN OUT NOCOPY VARCHAR2) RETURN INTEGER IS
628
629 CURSOR c_populate_values IS
630 SELECT msi.rowid
631 ,msb.lifecycle_id
632 ,msb.current_phase_id
633 ,msb.inventory_item_status_code
634 ,msb.item_catalog_group_id
635 ,msb.eng_item_flag -- 5306178
636 ,msb.style_item_flag
637 ,msb.style_item_id
638 ,msb.gdsn_outbound_enabled_flag
639 FROM mtl_system_items_interface msi,
640 mtl_system_items_b msb
641 WHERE msi.process_flag = 1
642 AND msi.set_process_id = xset_id
643 AND ((msi.organization_id = org_id) or (all_org = 1))
644 AND msi.transaction_type = 'UPDATE'
645 AND msi.organization_id = msb.organization_id
646 AND msi.inventory_item_id = msb.inventory_item_id;
647 -- 5306178 and NVL(msb.approval_status,'A') = 'A';
648
649 CURSOR c_check_attributes_policy IS
650 SELECT msi.rowid
651 ,msi.*
652 FROM mtl_system_items_interface msi
653 WHERE msi.process_flag = 1
654 AND msi.set_process_id = xset_id
655 AND ((msi.organization_id = org_id) or (all_org = 1))
656 AND msi.transaction_type = 'UPDATE';
657
658 --4676583 : Honouring batch option - Add All Imported Items to Change Order
659 --5216971 : Added structure_type_id
663 WHERE batch_id = xset_id;
660 CURSOR c_get_batch_policy IS
661 SELECT NVL(add_all_to_change_flag,'N'), structure_type_id
662 FROM ego_import_option_sets
664
665
666 cursor c_hold_status_codes is
667 SELECT msi.inventory_item_status_code, msi.eng_item_flag, msi.rowid
668 FROM mtl_system_items_interface msi
669 WHERE msi.process_flag = 1
670 AND msi.set_process_id = xset_id
671 AND ((msi.organization_id = org_id) or (all_org = 1))
672 AND msi.transaction_type = 'UPDATE';
673
674 l_process_control VARCHAR2(50) := INV_EGO_REVISION_VALIDATE.Get_Process_Control;
675 l_ret_code NUMBER := 1;
676 l_msb_rec mtl_system_items%ROWTYPE;
677 l_attr_grps VARCHAR2(200);
678 l_eng_object VARCHAR2(10);
679 l_policy_value VARCHAR2(100);
680 l_error_logged NUMBER := 0;
681 l_Err_Text VARCHAR2(500);
682 LOGGING_ERR EXCEPTION;
683 l_return_status VARCHAR2(100);
684 l_import_co_option VARCHAR2(1) := 'N';
685 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
686 --Contains 'Attribute_Group_ID:Attribute_Group_Name'
687 TYPE Item_Attributes_Type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
688 Attribute_Grp_Table Item_Attributes_Type;
689 l_status NUMBER;
690
691 l_structure_type_id NUMBER; --5216971
692 l_values_provided BOOLEAN := FALSE;
693 l_desc_status_change BOOLEAN := FALSE;
694 l_rowid ROWID;
695 l_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
696 l_eng_item_flag mtl_system_items_b.eng_item_flag%TYPE;
697
698 --Bug: 5532737
699 l_attr_grp_name EGO_ATTR_GROUPS_V.ATTR_GROUP_DISP_NAME%TYPE;
700 l_ch_policy_found BOOLEAN := FALSE;
701 l_delim_pos NUMBER := 1;
702 l_attr_group_id VARCHAR2(10);
703 l_msg_text VARCHAR2(2000);
704 --End Bug: 5532737
705
706 FUNCTION get_attribute_group_id(p_attr_grp_name VARCHAR2) RETURN NUMBER IS
707 l_attr_grp_id NUMBER;
708 BEGIN
709
710 FOR I IN 1..Attribute_Grp_Table.COUNT LOOP
711 IF SUBSTR(Attribute_Grp_Table(I),INSTR(Attribute_Grp_Table(I),':')+1) = p_attr_grp_name THEN
712 l_attr_grp_id := TO_NUMBER(SUBSTR(Attribute_Grp_Table(I),1,INSTR(Attribute_Grp_Table(I),':')-1));
713 END IF;
714 END LOOP;
715 RETURN l_attr_grp_id ;
716 END get_attribute_group_id;
717
718
719 BEGIN
720
721 -- Bug 10404086 : Added below hint
722 UPDATE /*+ first_rows index(MTL_SYSTEM_ITEMS_INTERFACE,MTL_SYSTEM_ITEMS_INTERFACE_N3) */
723 MTL_SYSTEM_ITEMS_INTERFACE
724 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
725 WHERE transaction_id IS NULL
726 AND set_process_id = xset_id;
727
728 --Assign Item Id and apply template.
729 l_ret_code := INVUPD1B.chk_exist_copy_template_attr(
730 org_id,
731 all_org,
732 prog_appid,
733 prog_id,
734 request_id,
735 user_id,
736 login_id,
737 err_text,
738 xset_id);
739
740 l_eng_object := INV_ITEM_UTIL.Object_Exists(p_object_type=>'PACKAGE BODY',p_object_name=>'ENG_CHANGE_POLICY_PKG');
741
742 --When called from ECO validate and impliment should not mark them to 5.
743 --6157001 : Removed the ENG_CALL:Y check
744 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) AND (l_eng_object ='Y') THEN
745
746 --Bug 5383744
747 --Cache the current value of status code in this cursor.
748 OPEN c_hold_status_codes;
749
750 --Default certain basic attributes which are required for policy check.
751 FOR cur IN c_populate_values LOOP
752 UPDATE mtl_system_items_interface
753 SET lifecycle_id = DECODE(lifecycle_id,NULL,cur.lifecycle_id,-999999,NULL,lifecycle_id)
754 ,current_phase_id = DECODE(current_phase_id,NULL,cur.current_phase_id,-999999,NULL,current_phase_id)
755 ,item_catalog_group_id = DECODE(item_catalog_group_id,NULL,cur.item_catalog_group_id,-999999, NULL,item_catalog_group_id)
756 ,inventory_item_status_code = NVL(inventory_item_status_code,cur.inventory_item_status_code)
757 ,eng_item_flag = NVL(eng_item_flag,cur.eng_item_flag) -- 5306178
758 --Adding style item defaulting, since style/sku validations need to be performed before ICC change
759 --which happens in policy check
760 ,style_item_flag = DECODE(style_item_flag,NULL,cur.style_item_flag,'!',NULL,chr(0),NULL,style_item_flag)
761 ,style_item_id = DECODE(style_item_id,NULL, cur.style_item_id,-999999,NULL,9.99E125,NULL,style_item_id)
762 ,gdsn_outbound_enabled_flag = DECODE(gdsn_outbound_enabled_flag,NULL,cur.gdsn_outbound_enabled_flag,'!',NULL,chr(0),NULL,gdsn_outbound_enabled_flag)
763 WHERE rowid = cur.rowid;
764 END LOOP;
765 END IF;
766
767 -- Bug 9884067 : Start The below code is moved here so that the validation is done after above defaulting.
768 --Start bug: 5238510
769 -- : Check for data security and user privileges
770 IF l_inv_debug_level IN(101, 102) THEN
771 INVPUTLI.info('INVNIRIS.change_poilcy_check: before INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
772 END IF;
773
774 l_status := INV_EGO_REVISION_VALIDATE.validate_item_user_privileges(
775 P_Org_Id => org_id
776 ,P_All_Org => all_org
777 ,P_Prog_AppId => prog_appid
778 ,P_Prog_Id => prog_id
779 ,P_Request_Id => request_id
783 ,X_Err_Text => err_text
780 ,P_User_Id => user_id
781 ,P_Login_Id => login_id
782 ,P_Set_Id => xset_id
784 ,P_Process_flag => 1);
785
786 IF l_inv_debug_level IN(101, 102) THEN
787 INVPUTLI.info('INVNIRIS.change_poilcy_check: done INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
788 END IF;
789 --End bug: 5238510
790
791 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) AND (l_eng_object ='Y') THEN
792 -- Bug 9884067 : End
793 --Call Item Lifecycle-Phase-Status validation.
794 l_ret_code := INV_EGO_REVISION_VALIDATE.validate_items_lifecycle(
795 P_Org_Id => org_id
796 ,P_All_Org => all_org
797 ,P_Prog_AppId => prog_appid
798 ,P_Prog_Id => prog_id
799 ,P_Request_Id => request_id
800 ,P_User_Id => user_id
801 ,P_Login_Id => login_id
802 ,P_Set_id => xset_id
803 ,P_Process_Flag => 1
804 ,X_Err_Text => err_text);
805
806 --Bug 5383744 Update the status code values back to what user has populated.
807 LOOP
808 FETCH c_hold_status_codes
809 INTO l_status_code, l_eng_item_flag, l_rowid ;
810 EXIT WHEN c_hold_status_codes%NOTFOUND;
811 UPDATE mtl_system_items_interface
812 SET inventory_item_status_code = l_status_code
813 ,eng_item_flag = l_eng_item_flag
814 WHERE rowid = l_rowid;
815 END LOOP;
816 CLOSE c_hold_status_codes;
817
818 --Get attribute group+id
819 SELECT ATTR_GROUP_ID||':'||UPPER(ATTR_GROUP_NAME)
820 BULK COLLECT INTO Attribute_Grp_Table
821 FROM EGO_ATTR_GROUPS_V
822 WHERE ATTR_GROUP_TYPE ='EGO_MASTER_ITEMS';
823
824 -- Bug 4870703 : Change policy check should take precedence over batch options
825 -- Bug 4676583 : Honouring batch option - Add All Imported Items to Change Order
826
827 OPEN c_get_batch_policy;
828 FETCH c_get_batch_policy INTO l_import_co_option, l_structure_type_id;
829 CLOSE c_get_batch_policy;
830
831 FOR cur IN c_check_attributes_policy LOOP
832 IF l_inv_debug_level IN(101, 102) THEN
833 INVPUTLI.info('INVNIRIS.change_policy_check: Checking Attr groups');
834 END IF;
835
836 l_attr_grps := NULL;
837 l_policy_value := NULL;
838 l_Err_Text := NULL;
839 l_error_logged := 0;
840
841 SELECT * INTO l_msb_rec
842 FROM mtl_system_items
843 WHERE inventory_item_id = cur.inventory_item_id
844 AND organization_id = cur.organization_id;
845
846 --5367962 Check for attribute changes if:
847 --Item Import : Item has a lifecycle attached to it.
848 --Structure Import: If Add to CO is YES.
849
850 IF NVL(l_msb_rec.APPROVAL_STATUS,'A') ='A'
851 AND (cur.current_phase_id is NOT NULL OR
852 (NVL(l_import_co_option,'N') = 'Y' AND l_structure_type_id IS NOT NULL) )
853 THEN
854
855 --Inventory Attribute Group
856 IF (NVL(cur.INVENTORY_ITEM_FLAG, NVL(l_msb_rec.INVENTORY_ITEM_FLAG,'!')) <> NVL(l_msb_rec.INVENTORY_ITEM_FLAG,'!'))
857 OR (NVL(cur.STOCK_ENABLED_FLAG, NVL(l_msb_rec.STOCK_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.STOCK_ENABLED_FLAG,'!'))
858 OR (NVL(cur.MTL_TRANSACTIONS_ENABLED_FLAG, NVL(l_msb_rec.MTL_TRANSACTIONS_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.MTL_TRANSACTIONS_ENABLED_FLAG,'!'))
859 OR (NVL(cur.REVISION_QTY_CONTROL_CODE, NVL(l_msb_rec.REVISION_QTY_CONTROL_CODE,-999999)) <> NVL(l_msb_rec.REVISION_QTY_CONTROL_CODE,-999999))
860 OR (NVL(cur.RESERVABLE_TYPE, NVL(l_msb_rec.RESERVABLE_TYPE,-999999)) <> NVL(l_msb_rec.RESERVABLE_TYPE,-999999))
861 OR (NVL(cur.CHECK_SHORTAGES_FLAG, NVL(l_msb_rec.CHECK_SHORTAGES_FLAG,'!')) <> NVL(l_msb_rec.CHECK_SHORTAGES_FLAG,'!'))
862 OR (NVL(cur.LOT_CONTROL_CODE, NVL(l_msb_rec.LOT_CONTROL_CODE,-999999)) <> NVL(l_msb_rec.LOT_CONTROL_CODE,-999999))
863 OR (NVL(cur.AUTO_LOT_ALPHA_PREFIX, NVL(l_msb_rec.AUTO_LOT_ALPHA_PREFIX,'!')) <> NVL(l_msb_rec.AUTO_LOT_ALPHA_PREFIX,'!'))
864 OR (NVL(cur.START_AUTO_LOT_NUMBER, NVL(l_msb_rec.START_AUTO_LOT_NUMBER,'!')) <> NVL(l_msb_rec.START_AUTO_LOT_NUMBER,'!'))
865 OR (NVL(cur.MATURITY_DAYS, NVL(l_msb_rec.MATURITY_DAYS,-999999)) <> NVL(l_msb_rec.MATURITY_DAYS,-999999))
866 OR (NVL(cur.HOLD_DAYS, NVL(l_msb_rec.HOLD_DAYS,-999999)) <> NVL(l_msb_rec.HOLD_DAYS,-999999))
867 OR (NVL(cur.SHELF_LIFE_CODE, NVL(l_msb_rec.SHELF_LIFE_CODE,-999999)) <> NVL(l_msb_rec.SHELF_LIFE_CODE,-999999))
868 OR (NVL(cur.SHELF_LIFE_DAYS, NVL(l_msb_rec.SHELF_LIFE_DAYS,-999999)) <> NVL(l_msb_rec.SHELF_LIFE_DAYS,-999999))
869 OR (NVL(cur.RETEST_INTERVAL, NVL(l_msb_rec.RETEST_INTERVAL,-999999)) <> NVL(l_msb_rec.RETEST_INTERVAL,-999999))
870 OR (NVL(cur.EXPIRATION_ACTION_INTERVAL, NVL(l_msb_rec.EXPIRATION_ACTION_INTERVAL,-999999)) <> NVL(l_msb_rec.EXPIRATION_ACTION_INTERVAL,-999999))
871 OR (NVL(cur.EXPIRATION_ACTION_CODE, NVL(l_msb_rec.EXPIRATION_ACTION_CODE,'!')) <> NVL(l_msb_rec.EXPIRATION_ACTION_CODE,'!'))
872 OR (NVL(cur.CYCLE_COUNT_ENABLED_FLAG, NVL(l_msb_rec.CYCLE_COUNT_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.CYCLE_COUNT_ENABLED_FLAG,'!'))
873 OR (NVL(cur.NEGATIVE_MEASUREMENT_ERROR, NVL(l_msb_rec.NEGATIVE_MEASUREMENT_ERROR,-999999)) <> NVL(l_msb_rec.NEGATIVE_MEASUREMENT_ERROR,-999999))
877 OR (NVL(cur.START_AUTO_SERIAL_NUMBER, NVL(l_msb_rec.START_AUTO_SERIAL_NUMBER,'!')) <> NVL(l_msb_rec.START_AUTO_SERIAL_NUMBER,'!'))
874 OR (NVL(cur.POSITIVE_MEASUREMENT_ERROR, NVL(l_msb_rec.POSITIVE_MEASUREMENT_ERROR,-999999)) <> NVL(l_msb_rec.POSITIVE_MEASUREMENT_ERROR,-999999))
875 OR (NVL(cur.SERIAL_NUMBER_CONTROL_CODE, NVL(l_msb_rec.SERIAL_NUMBER_CONTROL_CODE,-999999)) <> NVL(l_msb_rec.SERIAL_NUMBER_CONTROL_CODE,-999999))
876 OR (NVL(cur.AUTO_SERIAL_ALPHA_PREFIX, NVL(l_msb_rec.AUTO_SERIAL_ALPHA_PREFIX,'!')) <> NVL(l_msb_rec.AUTO_SERIAL_ALPHA_PREFIX,'!'))
878 OR (NVL(cur.LOCATION_CONTROL_CODE, NVL(l_msb_rec.LOCATION_CONTROL_CODE,-999999)) <> NVL(l_msb_rec.LOCATION_CONTROL_CODE,-999999))
879 OR (NVL(cur.RESTRICT_SUBINVENTORIES_CODE, NVL(l_msb_rec.RESTRICT_SUBINVENTORIES_CODE,-999999)) <> NVL(l_msb_rec.RESTRICT_SUBINVENTORIES_CODE,-999999))
880 OR (NVL(cur.RESTRICT_LOCATORS_CODE, NVL(l_msb_rec.RESTRICT_LOCATORS_CODE,-999999)) <> NVL(l_msb_rec.RESTRICT_LOCATORS_CODE,-999999))
881 OR (NVL(cur.LOT_STATUS_ENABLED, NVL(l_msb_rec.LOT_STATUS_ENABLED,'!')) <> NVL(l_msb_rec.LOT_STATUS_ENABLED,'!'))
882 OR (NVL(cur.DEFAULT_LOT_STATUS_ID, NVL(l_msb_rec.DEFAULT_LOT_STATUS_ID,-999999)) <> NVL(l_msb_rec.DEFAULT_LOT_STATUS_ID,-999999))
883 OR (NVL(cur.SERIAL_STATUS_ENABLED, NVL(l_msb_rec.SERIAL_STATUS_ENABLED,'!')) <> NVL(l_msb_rec.SERIAL_STATUS_ENABLED,'!'))
884 OR (NVL(cur.DEFAULT_SERIAL_STATUS_ID, NVL(l_msb_rec.DEFAULT_SERIAL_STATUS_ID,-999999)) <> NVL(l_msb_rec.DEFAULT_SERIAL_STATUS_ID,-999999))
885 OR (NVL(cur.SERIAL_STATUS_ENABLED, NVL(l_msb_rec.SERIAL_STATUS_ENABLED,'!')) <> NVL(l_msb_rec.SERIAL_STATUS_ENABLED,'!'))
886 OR (NVL(cur.DEFAULT_SERIAL_STATUS_ID, NVL(l_msb_rec.DEFAULT_SERIAL_STATUS_ID,-999999)) <> NVL(l_msb_rec.DEFAULT_SERIAL_STATUS_ID,-999999))
887 OR (NVL(cur.GRADE_CONTROL_FLAG, NVL(l_msb_rec.GRADE_CONTROL_FLAG,'!')) <> NVL(l_msb_rec.GRADE_CONTROL_FLAG,'!'))
888 OR (NVL(cur.DEFAULT_GRADE, NVL(l_msb_rec.DEFAULT_GRADE,'!')) <> NVL(l_msb_rec.DEFAULT_GRADE,'!'))
889 OR (NVL(cur.LOT_SPLIT_ENABLED, NVL(l_msb_rec.LOT_SPLIT_ENABLED,'!')) <> NVL(l_msb_rec.LOT_SPLIT_ENABLED,'!'))
890 OR (NVL(cur.LOT_MERGE_ENABLED, NVL(l_msb_rec.LOT_MERGE_ENABLED,'!')) <> NVL(l_msb_rec.LOT_MERGE_ENABLED,'!'))
891 OR (NVL(cur.LOT_TRANSLATE_ENABLED, NVL(l_msb_rec.LOT_TRANSLATE_ENABLED,'!')) <> NVL(l_msb_rec.LOT_TRANSLATE_ENABLED,'!'))
892 OR (NVL(cur.LOT_SUBSTITUTION_ENABLED, NVL(l_msb_rec.LOT_SUBSTITUTION_ENABLED,'!')) <> NVL(l_msb_rec.LOT_SUBSTITUTION_ENABLED,'!'))
893 OR (NVL(cur.BULK_PICKED_FLAG, NVL(l_msb_rec.BULK_PICKED_FLAG,'!')) <> NVL(l_msb_rec.BULK_PICKED_FLAG,'!'))
894 OR (NVL(cur.LOT_DIVISIBLE_FLAG, NVL(l_msb_rec.LOT_DIVISIBLE_FLAG,'!')) <> NVL(l_msb_rec.LOT_DIVISIBLE_FLAG,'!'))
895 OR (NVL(cur.CHILD_LOT_PREFIX, NVL(l_msb_rec.CHILD_LOT_PREFIX,-999999)) <> NVL(l_msb_rec.CHILD_LOT_PREFIX,-999999))
896 OR (NVL(cur.CHILD_LOT_STARTING_NUMBER, NVL(l_msb_rec.CHILD_LOT_STARTING_NUMBER,-999999)) <> NVL(l_msb_rec.CHILD_LOT_STARTING_NUMBER,-999999))
897 OR (NVL(cur.CHILD_LOT_VALIDATION_FLAG, NVL(l_msb_rec.CHILD_LOT_VALIDATION_FLAG,'!')) <> NVL(l_msb_rec.CHILD_LOT_VALIDATION_FLAG,'!'))
898 OR (NVL(cur.PARENT_CHILD_GENERATION_FLAG, NVL(l_msb_rec.PARENT_CHILD_GENERATION_FLAG,'!')) <> NVL(l_msb_rec.PARENT_CHILD_GENERATION_FLAG,'!'))
899 OR (NVL(cur.COPY_LOT_ATTRIBUTE_FLAG, NVL(l_msb_rec.COPY_LOT_ATTRIBUTE_FLAG,'!')) <> NVL(l_msb_rec.COPY_LOT_ATTRIBUTE_FLAG,'!'))
900 OR (NVL(cur.CHILD_LOT_FLAG, NVL(l_msb_rec.CHILD_LOT_FLAG,'!')) <> NVL(l_msb_rec.CHILD_LOT_FLAG,'!'))
901 THEN
902 IF l_attr_grps IS NULL THEN
903 l_attr_grps := TO_CHAR(get_attribute_group_id('INVENTORY'));
904 ELSE
905 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('INVENTORY'));
906 END IF;
907 END IF;
908
909 --BOM Attribute Group
910 IF (NVL(cur.BOM_ENABLED_FLAG, NVL(l_msb_rec.BOM_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.BOM_ENABLED_FLAG,'!'))
911 OR (NVL(cur.BOM_ITEM_TYPE, NVL(l_msb_rec.BOM_ITEM_TYPE,-999999)) <> NVL(l_msb_rec.BOM_ITEM_TYPE,-999999))
912 OR (NVL(cur.BASE_ITEM_ID, NVL(l_msb_rec.BASE_ITEM_ID,-999999)) <> NVL(l_msb_rec.BASE_ITEM_ID,-999999))
913 OR (NVL(cur.AUTO_CREATED_CONFIG_FLAG, NVL(l_msb_rec.AUTO_CREATED_CONFIG_FLAG,'!')) <> NVL(l_msb_rec.AUTO_CREATED_CONFIG_FLAG,'!'))
914 OR (NVL(cur.ENG_ITEM_FLAG, NVL(l_msb_rec.ENG_ITEM_FLAG,'!')) <> NVL(l_msb_rec.ENG_ITEM_FLAG,'!'))
915 OR (NVL(cur.EFFECTIVITY_CONTROL, NVL(l_msb_rec.EFFECTIVITY_CONTROL,-999999)) <> NVL(l_msb_rec.EFFECTIVITY_CONTROL,-999999))
916 OR (NVL(cur.CONFIG_MODEL_TYPE, NVL(l_msb_rec.CONFIG_MODEL_TYPE,'!')) <> NVL(l_msb_rec.CONFIG_MODEL_TYPE,'!'))
917 OR (NVL(cur.CONFIG_ORGS, NVL(l_msb_rec.CONFIG_ORGS,-999999)) <> NVL(l_msb_rec.CONFIG_ORGS,-999999))
918 OR (NVL(cur.CONFIG_MATCH, NVL(l_msb_rec.CONFIG_MATCH,'!')) <> NVL(l_msb_rec.CONFIG_MATCH,'!'))
919 THEN
920 IF l_attr_grps IS NULL THEN
921 l_attr_grps := TO_CHAR(get_attribute_group_id('BILLOFMATERIALS'));
922 ELSE
923 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('BILLOFMATERIALS'));
924 END IF;
925 END IF;
926
927 --Asset Management Group
931 OR (NVL(cur.EAM_ACTIVITY_SOURCE_CODE, NVL(l_msb_rec.EAM_ACTIVITY_SOURCE_CODE,'!')) <> NVL(l_msb_rec.EAM_ACTIVITY_SOURCE_CODE,'!'))
928 IF (NVL(cur.EAM_ITEM_TYPE, NVL(l_msb_rec.EAM_ITEM_TYPE,-999999)) <> NVL(l_msb_rec.EAM_ITEM_TYPE,-999999))
929 OR (NVL(cur.EAM_ACTIVITY_TYPE_CODE, NVL(l_msb_rec.EAM_ACTIVITY_TYPE_CODE,'!')) <> NVL(l_msb_rec.EAM_ACTIVITY_TYPE_CODE,'!'))
930 OR (NVL(cur.EAM_ACTIVITY_CAUSE_CODE, NVL(l_msb_rec.EAM_ACTIVITY_CAUSE_CODE,'!')) <> NVL(l_msb_rec.EAM_ACTIVITY_CAUSE_CODE,'!'))
932 OR (NVL(cur.EAM_ACT_SHUTDOWN_STATUS, NVL(l_msb_rec.EAM_ACT_SHUTDOWN_STATUS,'!')) <> NVL(l_msb_rec.EAM_ACT_SHUTDOWN_STATUS,'!'))
933 OR (NVL(cur.EAM_ACT_NOTIFICATION_FLAG, NVL(l_msb_rec.EAM_ACT_NOTIFICATION_FLAG,'!')) <> NVL(l_msb_rec.EAM_ACT_NOTIFICATION_FLAG,'!'))
934 THEN
935 IF l_attr_grps IS NULL THEN
936 l_attr_grps := TO_CHAR(get_attribute_group_id('ASSETMANAGEMENT'));
937 ELSE
938 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('ASSETMANAGEMENT'));
939 END IF;
940 END IF;
941
942 --Costing Attribute Group
943 IF (NVL(cur.COSTING_ENABLED_FLAG, NVL(l_msb_rec.COSTING_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.COSTING_ENABLED_FLAG,'!'))
944 OR (NVL(cur.INVENTORY_ASSET_FLAG, NVL(l_msb_rec.INVENTORY_ASSET_FLAG,'!')) <> NVL(l_msb_rec.INVENTORY_ASSET_FLAG,'!'))
945 OR (NVL(cur.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,NVL(l_msb_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,'!')) <> NVL(l_msb_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,'!'))
946 OR (NVL(cur.COST_OF_SALES_ACCOUNT, NVL(l_msb_rec.COST_OF_SALES_ACCOUNT,-999999)) <> NVL(l_msb_rec.COST_OF_SALES_ACCOUNT,-999999))
947 OR (NVL(cur.STD_LOT_SIZE, NVL(l_msb_rec.STD_LOT_SIZE,-999999)) <> NVL(l_msb_rec.STD_LOT_SIZE,-999999))
948 THEN
949 IF l_attr_grps IS NULL THEN
950 l_attr_grps := TO_CHAR(get_attribute_group_id('COSTING'));
951 ELSE
952 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('COSTING'));
953 END IF;
954 END IF;
955
956 --Purchasing Attribute Group
957 IF (NVL(cur.PURCHASING_ITEM_FLAG, NVL(l_msb_rec.PURCHASING_ITEM_FLAG,'!')) <> NVL(l_msb_rec.PURCHASING_ITEM_FLAG,'!'))
958 OR (NVL(cur.PURCHASING_ENABLED_FLAG, NVL(l_msb_rec.PURCHASING_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.PURCHASING_ENABLED_FLAG,'!'))
959 OR (NVL(cur.MUST_USE_APPROVED_VENDOR_FLAG, NVL(l_msb_rec.MUST_USE_APPROVED_VENDOR_FLAG,'!')) <> NVL(l_msb_rec.MUST_USE_APPROVED_VENDOR_FLAG,'!'))
960 OR (NVL(cur.ALLOW_ITEM_DESC_UPDATE_FLAG, NVL(l_msb_rec.ALLOW_ITEM_DESC_UPDATE_FLAG,'!')) <> NVL(l_msb_rec.ALLOW_ITEM_DESC_UPDATE_FLAG,'!'))
961 OR (NVL(cur.RFQ_REQUIRED_FLAG, NVL(l_msb_rec.RFQ_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.RFQ_REQUIRED_FLAG,'!'))
962 OR (NVL(cur.OUTSIDE_OPERATION_FLAG, NVL(l_msb_rec.OUTSIDE_OPERATION_FLAG,'!')) <> NVL(l_msb_rec.OUTSIDE_OPERATION_FLAG,'!'))
963 OR (NVL(cur.OUTSIDE_OPERATION_UOM_TYPE, NVL(l_msb_rec.OUTSIDE_OPERATION_UOM_TYPE,'!')) <> NVL(l_msb_rec.OUTSIDE_OPERATION_UOM_TYPE,'!'))
964 OR (NVL(cur.TAXABLE_FLAG, NVL(l_msb_rec.TAXABLE_FLAG,'!')) <> NVL(l_msb_rec.TAXABLE_FLAG,'!'))
965 OR (NVL(cur.TAX_CODE, NVL(l_msb_rec.TAX_CODE,'!')) <> NVL(l_msb_rec.TAX_CODE,'!'))
966 OR (NVL(cur.RECEIPT_REQUIRED_FLAG, NVL(l_msb_rec.RECEIPT_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.RECEIPT_REQUIRED_FLAG,'!'))
967 OR (NVL(cur.INSPECTION_REQUIRED_FLAG, NVL(l_msb_rec.INSPECTION_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.INSPECTION_REQUIRED_FLAG,'!'))
968 OR (NVL(cur.BUYER_ID, NVL(l_msb_rec.BUYER_ID,-999999)) <> NVL(l_msb_rec.BUYER_ID,-999999))
969 OR (NVL(cur.UNIT_OF_ISSUE, NVL(l_msb_rec.UNIT_OF_ISSUE,'!')) <> NVL(l_msb_rec.UNIT_OF_ISSUE,'!'))
970 OR (NVL(cur.RECEIVE_CLOSE_TOLERANCE, NVL(l_msb_rec.RECEIVE_CLOSE_TOLERANCE,-999999)) <> NVL(l_msb_rec.RECEIVE_CLOSE_TOLERANCE,-999999))
971 OR (NVL(cur.INVOICE_CLOSE_TOLERANCE, NVL(l_msb_rec.INVOICE_CLOSE_TOLERANCE,-999999)) <> NVL(l_msb_rec.INVOICE_CLOSE_TOLERANCE,-999999))
972 OR (NVL(cur.UN_NUMBER_ID, NVL(l_msb_rec.UN_NUMBER_ID,-999999)) <> NVL(l_msb_rec.UN_NUMBER_ID,-999999))
973 OR (NVL(cur.HAZARD_CLASS_ID, NVL(l_msb_rec.HAZARD_CLASS_ID,-999999)) <> NVL(l_msb_rec.HAZARD_CLASS_ID,-999999))
974 OR (NVL(cur.LIST_PRICE_PER_UNIT, NVL(l_msb_rec.LIST_PRICE_PER_UNIT,-999999)) <> NVL(l_msb_rec.LIST_PRICE_PER_UNIT,-999999))
975 OR (NVL(cur.MARKET_PRICE, NVL(l_msb_rec.MARKET_PRICE,-999999)) <> NVL(l_msb_rec.MARKET_PRICE,-999999))
976 OR (NVL(cur.PRICE_TOLERANCE_PERCENT, NVL(l_msb_rec.PRICE_TOLERANCE_PERCENT,-999999)) <> NVL(l_msb_rec.PRICE_TOLERANCE_PERCENT,-999999))
977 OR (NVL(cur.ROUNDING_FACTOR, NVL(l_msb_rec.ROUNDING_FACTOR,-999999)) <> NVL(l_msb_rec.ROUNDING_FACTOR,-999999))
978 OR (NVL(cur.ENCUMBRANCE_ACCOUNT, NVL(l_msb_rec.ENCUMBRANCE_ACCOUNT,-999999)) <> NVL(l_msb_rec.ENCUMBRANCE_ACCOUNT,-999999))
979 OR (NVL(cur.EXPENSE_ACCOUNT, NVL(l_msb_rec.EXPENSE_ACCOUNT,-999999)) <> NVL(l_msb_rec.EXPENSE_ACCOUNT,-999999))
980 OR (NVL(cur.ASSET_CATEGORY_ID, NVL(l_msb_rec.ASSET_CATEGORY_ID,-999999)) <> NVL(l_msb_rec.ASSET_CATEGORY_ID,-999999))
984 l_attr_grps := TO_CHAR(get_attribute_group_id('PURCHASING'));
981 OR (NVL(cur.OUTSOURCED_ASSEMBLY, NVL(l_msb_rec.OUTSOURCED_ASSEMBLY,-999999)) <> NVL(l_msb_rec.OUTSOURCED_ASSEMBLY,-999999))
982 THEN
983 IF l_attr_grps IS NULL THEN
985 ELSE
986 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('PURCHASING'));
987 END IF;
988 END IF;
989
990 --Receiving Attribute Group
991 IF (NVL(cur.RECEIPT_DAYS_EXCEPTION_CODE, NVL(l_msb_rec.RECEIPT_DAYS_EXCEPTION_CODE,'!')) <> NVL(l_msb_rec.RECEIPT_DAYS_EXCEPTION_CODE,'!'))
992 OR (NVL(cur.DAYS_EARLY_RECEIPT_ALLOWED, NVL(l_msb_rec.DAYS_EARLY_RECEIPT_ALLOWED,-999999)) <> NVL(l_msb_rec.DAYS_EARLY_RECEIPT_ALLOWED,-999999))
993 OR (NVL(cur.DAYS_LATE_RECEIPT_ALLOWED, NVL(l_msb_rec.DAYS_LATE_RECEIPT_ALLOWED,-999999)) <> NVL(l_msb_rec.DAYS_LATE_RECEIPT_ALLOWED,-999999))
994 OR (NVL(cur.QTY_RCV_EXCEPTION_CODE, NVL(l_msb_rec.QTY_RCV_EXCEPTION_CODE,'!')) <> NVL(l_msb_rec.QTY_RCV_EXCEPTION_CODE,'!'))
995 OR (NVL(cur.QTY_RCV_TOLERANCE, NVL(l_msb_rec.QTY_RCV_TOLERANCE,-999999)) <> NVL(l_msb_rec.QTY_RCV_TOLERANCE,-999999))
996 OR (NVL(cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,NVL(l_msb_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,'!')) <> NVL(l_msb_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,'!'))
997 OR (NVL(cur.ALLOW_UNORDERED_RECEIPTS_FLAG, NVL(l_msb_rec.ALLOW_UNORDERED_RECEIPTS_FLAG,'!')) <> NVL(l_msb_rec.ALLOW_UNORDERED_RECEIPTS_FLAG,'!'))
998 OR (NVL(cur.ALLOW_EXPRESS_DELIVERY_FLAG, NVL(l_msb_rec.ALLOW_EXPRESS_DELIVERY_FLAG,'!')) <> NVL(l_msb_rec.ALLOW_EXPRESS_DELIVERY_FLAG,'!'))
999 OR (NVL(cur.RECEIVING_ROUTING_ID, NVL(l_msb_rec.RECEIVING_ROUTING_ID,-999999)) <> NVL(l_msb_rec.RECEIVING_ROUTING_ID,-999999))
1000 OR (NVL(cur.ENFORCE_SHIP_TO_LOCATION_CODE, NVL(l_msb_rec.ENFORCE_SHIP_TO_LOCATION_CODE,'!')) <> NVL(l_msb_rec.ENFORCE_SHIP_TO_LOCATION_CODE,'!'))
1001 THEN
1002 IF l_attr_grps IS NULL THEN
1003 l_attr_grps := TO_CHAR(get_attribute_group_id('RECEIVING'));
1004 ELSE
1005 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('RECEIVING'));
1006 END IF;
1007 END IF;
1008
1009 --Process Manufacturing Attribute Group
1010 IF (NVL(cur.RECIPE_ENABLED_FLAG, NVL(l_msb_rec.RECIPE_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.RECIPE_ENABLED_FLAG,'!'))
1011 OR (NVL(cur.PROCESS_QUALITY_ENABLED_FLAG, NVL(l_msb_rec.PROCESS_QUALITY_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.PROCESS_QUALITY_ENABLED_FLAG,'!'))
1012 OR (NVL(cur.PROCESS_EXECUTION_ENABLED_FLAG,NVL(l_msb_rec.PROCESS_EXECUTION_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.PROCESS_EXECUTION_ENABLED_FLAG,'!'))
1013 OR (NVL(cur.PROCESS_SUPPLY_SUBINVENTORY, NVL(l_msb_rec.PROCESS_SUPPLY_SUBINVENTORY,'!')) <> NVL(l_msb_rec.PROCESS_SUPPLY_SUBINVENTORY,'!'))
1014 OR (NVL(cur.PROCESS_SUPPLY_LOCATOR_ID, NVL(l_msb_rec.PROCESS_SUPPLY_LOCATOR_ID,-999999)) <> NVL(l_msb_rec.PROCESS_SUPPLY_LOCATOR_ID,-999999))
1015 OR (NVL(cur.PROCESS_YIELD_SUBINVENTORY, NVL(l_msb_rec.PROCESS_YIELD_SUBINVENTORY,'!')) <> NVL(l_msb_rec.PROCESS_YIELD_SUBINVENTORY,'!'))
1016 OR (NVL(cur.PROCESS_YIELD_LOCATOR_ID, NVL(l_msb_rec.PROCESS_YIELD_LOCATOR_ID,-999999)) <> NVL(l_msb_rec.PROCESS_YIELD_LOCATOR_ID,-999999))
1017 OR (NVL(cur.PROCESS_COSTING_ENABLED_FLAG, NVL(l_msb_rec.PROCESS_COSTING_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.PROCESS_COSTING_ENABLED_FLAG,'!'))
1018 OR (NVL(cur.HAZARDOUS_MATERIAL_FLAG, NVL(l_msb_rec.HAZARDOUS_MATERIAL_FLAG,'!')) <> NVL(l_msb_rec.HAZARDOUS_MATERIAL_FLAG,'!'))
1019 OR (NVL(cur.CAS_NUMBER, NVL(l_msb_rec.CAS_NUMBER,'!')) <> NVL(l_msb_rec.CAS_NUMBER,'!'))
1020 THEN
1021 IF l_attr_grps IS NULL THEN
1022 l_attr_grps := TO_CHAR(get_attribute_group_id('PROCESSMANUFACTURING'));
1023 ELSE
1024 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('PROCESSMANUFACTURING'));
1025 END IF;
1026 END IF;
1027
1028 --Physical Attributes
1029 IF (NVL(cur.WEIGHT_UOM_CODE, NVL(l_msb_rec.WEIGHT_UOM_CODE,'!')) <> NVL(l_msb_rec.WEIGHT_UOM_CODE,'!'))
1030 OR (NVL(cur.UNIT_WEIGHT, NVL(l_msb_rec.UNIT_WEIGHT,-999999)) <> NVL(l_msb_rec.UNIT_WEIGHT,-999999))
1031 OR (NVL(cur.VOLUME_UOM_CODE, NVL(l_msb_rec.VOLUME_UOM_CODE,'!')) <> NVL(l_msb_rec.VOLUME_UOM_CODE,'!'))
1032 OR (NVL(cur.UNIT_VOLUME, NVL(l_msb_rec.UNIT_VOLUME,-999999)) <> NVL(l_msb_rec.UNIT_VOLUME,-999999))
1033 OR (NVL(cur.CONTAINER_ITEM_FLAG, NVL(l_msb_rec.CONTAINER_ITEM_FLAG,'!')) <> NVL(l_msb_rec.CONTAINER_ITEM_FLAG,'!'))
1034 OR (NVL(cur.VEHICLE_ITEM_FLAG, NVL(l_msb_rec.VEHICLE_ITEM_FLAG,'!')) <> NVL(l_msb_rec.VEHICLE_ITEM_FLAG,'!'))
1035 OR (NVL(cur.CONTAINER_TYPE_CODE, NVL(l_msb_rec.CONTAINER_TYPE_CODE,'!')) <> NVL(l_msb_rec.CONTAINER_TYPE_CODE,'!'))
1036 OR (NVL(cur.INTERNAL_VOLUME, NVL(l_msb_rec.INTERNAL_VOLUME,-999999)) <> NVL(l_msb_rec.INTERNAL_VOLUME,-999999))
1037 OR (NVL(cur.MAXIMUM_LOAD_WEIGHT, NVL(l_msb_rec.MAXIMUM_LOAD_WEIGHT,-999999)) <> NVL(l_msb_rec.MAXIMUM_LOAD_WEIGHT,-999999))
1038 OR (NVL(cur.MINIMUM_FILL_PERCENT, NVL(l_msb_rec.MINIMUM_FILL_PERCENT,-999999)) <> NVL(l_msb_rec.MINIMUM_FILL_PERCENT,-999999))
1039 OR (NVL(cur.DIMENSION_UOM_CODE, NVL(l_msb_rec.DIMENSION_UOM_CODE,'!')) <> NVL(l_msb_rec.DIMENSION_UOM_CODE,'!'))
1043 OR (NVL(cur.COLLATERAL_FLAG, NVL(l_msb_rec.COLLATERAL_FLAG,'!')) <> NVL(l_msb_rec.COLLATERAL_FLAG,'!'))
1040 OR (NVL(cur.UNIT_LENGTH, NVL(l_msb_rec.UNIT_LENGTH,-999999)) <> NVL(l_msb_rec.UNIT_LENGTH,-999999))
1041 OR (NVL(cur.UNIT_WIDTH, NVL(l_msb_rec.UNIT_WIDTH,-999999)) <> NVL(l_msb_rec.UNIT_WIDTH,-999999))
1042 OR (NVL(cur.UNIT_HEIGHT, NVL(l_msb_rec.UNIT_HEIGHT,-999999)) <> NVL(l_msb_rec.UNIT_HEIGHT,-999999))
1044 OR (NVL(cur.EVENT_FLAG, NVL(l_msb_rec.EVENT_FLAG,'!')) <> NVL(l_msb_rec.EVENT_FLAG,'!'))
1045 OR (NVL(cur.EQUIPMENT_TYPE, NVL(l_msb_rec.EQUIPMENT_TYPE,-999999)) <> NVL(l_msb_rec.EQUIPMENT_TYPE,-999999))
1046 OR (NVL(cur.ELECTRONIC_FLAG, NVL(l_msb_rec.ELECTRONIC_FLAG,'!')) <> NVL(l_msb_rec.ELECTRONIC_FLAG,'!'))
1047 OR (NVL(cur.DOWNLOADABLE_FLAG, NVL(l_msb_rec.DOWNLOADABLE_FLAG,'!')) <> NVL(l_msb_rec.DOWNLOADABLE_FLAG,'!'))
1048 OR (NVL(cur.INDIVISIBLE_FLAG, NVL(l_msb_rec.INDIVISIBLE_FLAG,'!')) <> NVL(l_msb_rec.INDIVISIBLE_FLAG,'!'))
1049 THEN
1050 IF l_attr_grps IS NULL THEN
1051 l_attr_grps := TO_CHAR(get_attribute_group_id('PHYSICALATTRIBUTES'));
1052 ELSE
1053 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('PHYSICALATTRIBUTES'));
1054 END IF;
1055 END IF;
1056
1057 --General Planning
1058 IF (NVL(cur.INVENTORY_PLANNING_CODE, NVL(l_msb_rec.INVENTORY_PLANNING_CODE,-999999)) <> NVL(l_msb_rec.INVENTORY_PLANNING_CODE,-999999))
1059 OR (NVL(cur.PLANNER_CODE, NVL(l_msb_rec.PLANNER_CODE,'!')) <> NVL(l_msb_rec.PLANNER_CODE,'!'))
1060 OR (NVL(cur.PLANNING_MAKE_BUY_CODE, NVL(l_msb_rec.PLANNING_MAKE_BUY_CODE,-999999)) <> NVL(l_msb_rec.PLANNING_MAKE_BUY_CODE,-999999))
1061 OR (NVL(cur.MIN_MINMAX_QUANTITY, NVL(l_msb_rec.MIN_MINMAX_QUANTITY,-999999)) <> NVL(l_msb_rec.MIN_MINMAX_QUANTITY,-999999))
1062 OR (NVL(cur.MAX_MINMAX_QUANTITY, NVL(l_msb_rec.MAX_MINMAX_QUANTITY,-999999)) <> NVL(l_msb_rec.MAX_MINMAX_QUANTITY,-999999))
1063 OR (NVL(cur.MINIMUM_ORDER_QUANTITY, NVL(l_msb_rec.MINIMUM_ORDER_QUANTITY,-999999)) <> NVL(l_msb_rec.MINIMUM_ORDER_QUANTITY,-999999))
1064 OR (NVL(cur.MAXIMUM_ORDER_QUANTITY, NVL(l_msb_rec.MAXIMUM_ORDER_QUANTITY,-999999)) <> NVL(l_msb_rec.MAXIMUM_ORDER_QUANTITY,-999999))
1065 OR (NVL(cur.ORDER_COST, NVL(l_msb_rec.ORDER_COST,-999999)) <> NVL(l_msb_rec.ORDER_COST,-999999))
1066 OR (NVL(cur.CARRYING_COST, NVL(l_msb_rec.CARRYING_COST,-999999)) <> NVL(l_msb_rec.CARRYING_COST,-999999))
1067 OR (NVL(cur.VMI_MINIMUM_UNITS, NVL(l_msb_rec.VMI_MINIMUM_UNITS,-999999)) <> NVL(l_msb_rec.VMI_MINIMUM_UNITS,-999999))
1068 OR (NVL(cur.VMI_MINIMUM_DAYS, NVL(l_msb_rec.VMI_MINIMUM_DAYS,-999999)) <> NVL(l_msb_rec.VMI_MINIMUM_DAYS,-999999))
1069 OR (NVL(cur.VMI_MAXIMUM_UNITS, NVL(l_msb_rec.VMI_MAXIMUM_UNITS,-999999)) <> NVL(l_msb_rec.VMI_MAXIMUM_UNITS,-999999))
1070 OR (NVL(cur.VMI_MAXIMUM_DAYS, NVL(l_msb_rec.VMI_MAXIMUM_DAYS,-999999)) <> NVL(l_msb_rec.VMI_MAXIMUM_DAYS,-999999))
1071 OR (NVL(cur.VMI_FIXED_ORDER_QUANTITY, NVL(l_msb_rec.VMI_FIXED_ORDER_QUANTITY,-999999)) <> NVL(l_msb_rec.VMI_FIXED_ORDER_QUANTITY,-999999))
1072 OR (NVL(cur.SO_AUTHORIZATION_FLAG, NVL(l_msb_rec.SO_AUTHORIZATION_FLAG,-999999)) <> NVL(l_msb_rec.SO_AUTHORIZATION_FLAG,-999999))
1073 OR (NVL(cur.CONSIGNED_FLAG, NVL(l_msb_rec.CONSIGNED_FLAG,-999999)) <> NVL(l_msb_rec.CONSIGNED_FLAG,-999999))
1074 OR (NVL(cur.ASN_AUTOEXPIRE_FLAG, NVL(l_msb_rec.ASN_AUTOEXPIRE_FLAG,-999999)) <> NVL(l_msb_rec.ASN_AUTOEXPIRE_FLAG,-999999))
1075 OR (NVL(cur.VMI_FORECAST_TYPE, NVL(l_msb_rec.VMI_FORECAST_TYPE,-999999)) <> NVL(l_msb_rec.VMI_FORECAST_TYPE,-999999))
1076 OR (NVL(cur.FORECAST_HORIZON, NVL(l_msb_rec.FORECAST_HORIZON,-999999)) <> NVL(l_msb_rec.FORECAST_HORIZON,-999999))
1077 OR (NVL(cur.SOURCE_TYPE, NVL(l_msb_rec.SOURCE_TYPE,-999999)) <> NVL(l_msb_rec.SOURCE_TYPE,-999999))
1078 OR (NVL(cur.SOURCE_ORGANIZATION_ID, NVL(l_msb_rec.SOURCE_ORGANIZATION_ID,-999999)) <> NVL(l_msb_rec.SOURCE_ORGANIZATION_ID,-999999))
1079 OR (NVL(cur.SOURCE_SUBINVENTORY, NVL(l_msb_rec.SOURCE_SUBINVENTORY,'!')) <> NVL(l_msb_rec.SOURCE_SUBINVENTORY,'!'))
1080 OR (NVL(cur.MRP_SAFETY_STOCK_CODE, NVL(l_msb_rec.MRP_SAFETY_STOCK_CODE,-999999)) <> NVL(l_msb_rec.MRP_SAFETY_STOCK_CODE,-999999))
1081 OR (NVL(cur.SAFETY_STOCK_BUCKET_DAYS, NVL(l_msb_rec.SAFETY_STOCK_BUCKET_DAYS,-999999)) <> NVL(l_msb_rec.SAFETY_STOCK_BUCKET_DAYS,-999999))
1082 OR (NVL(cur.MRP_SAFETY_STOCK_PERCENT, NVL(l_msb_rec.MRP_SAFETY_STOCK_PERCENT,-999999)) <> NVL(l_msb_rec.MRP_SAFETY_STOCK_PERCENT,-999999))
1083 OR (NVL(cur.FIXED_ORDER_QUANTITY, NVL(l_msb_rec.FIXED_ORDER_QUANTITY,-999999)) <> NVL(l_msb_rec.FIXED_ORDER_QUANTITY,-999999))
1084 OR (NVL(cur.FIXED_LOT_MULTIPLIER, NVL(l_msb_rec.FIXED_LOT_MULTIPLIER,-999999)) <> NVL(l_msb_rec.FIXED_LOT_MULTIPLIER,-999999))
1085 OR (NVL(cur.SUBCONTRACTING_COMPONENT, NVL(l_msb_rec.SUBCONTRACTING_COMPONENT,-999999)) <> NVL(l_msb_rec.SUBCONTRACTING_COMPONENT,-999999))
1086 THEN
1087 IF l_attr_grps IS NULL THEN
1088 l_attr_grps := TO_CHAR(get_attribute_group_id('GENERALPLANNING'));
1089 ELSE
1093
1090 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('GENERALPLANNING'));
1091 END IF;
1092 END IF;
1094 --MPS/MRP Planning
1095 IF (NVL(cur.MRP_PLANNING_CODE, NVL(l_msb_rec.MRP_PLANNING_CODE,-999999)) <> NVL(l_msb_rec.MRP_PLANNING_CODE,-999999))
1096 OR (NVL(cur.ATO_FORECAST_CONTROL, NVL(l_msb_rec.ATO_FORECAST_CONTROL,-999999)) <> NVL(l_msb_rec.ATO_FORECAST_CONTROL,-999999))
1097 OR (NVL(cur.PLANNING_EXCEPTION_SET, NVL(l_msb_rec.PLANNING_EXCEPTION_SET,'!')) <> NVL(l_msb_rec.PLANNING_EXCEPTION_SET,'!'))
1098 OR (NVL(cur.END_ASSEMBLY_PEGGING_FLAG, NVL(l_msb_rec.END_ASSEMBLY_PEGGING_FLAG,'!')) <> NVL(l_msb_rec.END_ASSEMBLY_PEGGING_FLAG,'!'))
1099 OR (NVL(cur.PLANNED_INV_POINT_FLAG, NVL(l_msb_rec.PLANNED_INV_POINT_FLAG,'!')) <> NVL(l_msb_rec.PLANNED_INV_POINT_FLAG,'!'))
1100 OR (NVL(cur.CREATE_SUPPLY_FLAG, NVL(l_msb_rec.CREATE_SUPPLY_FLAG,'!')) <> NVL(l_msb_rec.CREATE_SUPPLY_FLAG,'!'))
1101 OR (NVL(cur.EXCLUDE_FROM_BUDGET_FLAG, NVL(l_msb_rec.EXCLUDE_FROM_BUDGET_FLAG,-999999)) <> NVL(l_msb_rec.EXCLUDE_FROM_BUDGET_FLAG,-999999))
1102 OR (NVL(cur.ROUNDING_CONTROL_TYPE, NVL(l_msb_rec.ROUNDING_CONTROL_TYPE,-999999)) <> NVL(l_msb_rec.ROUNDING_CONTROL_TYPE,-999999))
1103 OR (NVL(cur.SHRINKAGE_RATE, NVL(l_msb_rec.SHRINKAGE_RATE,-999999)) <> NVL(l_msb_rec.SHRINKAGE_RATE,-999999))
1104 OR (NVL(cur.ACCEPTABLE_EARLY_DAYS, NVL(l_msb_rec.ACCEPTABLE_EARLY_DAYS,-999999)) <> NVL(l_msb_rec.ACCEPTABLE_EARLY_DAYS,-999999))
1105 OR (NVL(cur.REPETITIVE_PLANNING_FLAG, NVL(l_msb_rec.REPETITIVE_PLANNING_FLAG,'!')) <> NVL(l_msb_rec.REPETITIVE_PLANNING_FLAG,'!'))
1106 OR (NVL(cur.OVERRUN_PERCENTAGE, NVL(l_msb_rec.OVERRUN_PERCENTAGE,-999999)) <> NVL(l_msb_rec.OVERRUN_PERCENTAGE,-999999))
1107 OR (NVL(cur.ACCEPTABLE_RATE_DECREASE, NVL(l_msb_rec.ACCEPTABLE_RATE_DECREASE,-999999)) <> NVL(l_msb_rec.ACCEPTABLE_RATE_DECREASE,-999999))
1108 OR (NVL(cur.ACCEPTABLE_RATE_INCREASE, NVL(l_msb_rec.ACCEPTABLE_RATE_INCREASE,-999999)) <> NVL(l_msb_rec.ACCEPTABLE_RATE_INCREASE,-999999))
1109 OR (NVL(cur.MRP_CALCULATE_ATP_FLAG, NVL(l_msb_rec.MRP_CALCULATE_ATP_FLAG,'!')) <> NVL(l_msb_rec.MRP_CALCULATE_ATP_FLAG,'!'))
1110 OR (NVL(cur.AUTO_REDUCE_MPS, NVL(l_msb_rec.AUTO_REDUCE_MPS,-999999)) <> NVL(l_msb_rec.AUTO_REDUCE_MPS,-999999))
1111 OR (NVL(cur.PLANNING_TIME_FENCE_CODE, NVL(l_msb_rec.PLANNING_TIME_FENCE_CODE,-999999)) <> NVL(l_msb_rec.PLANNING_TIME_FENCE_CODE,-999999))
1112 OR (NVL(cur.PLANNING_TIME_FENCE_DAYS, NVL(l_msb_rec.PLANNING_TIME_FENCE_DAYS,-999999)) <> NVL(l_msb_rec.PLANNING_TIME_FENCE_DAYS,-999999))
1113 OR (NVL(cur.DEMAND_TIME_FENCE_CODE, NVL(l_msb_rec.DEMAND_TIME_FENCE_CODE,-999999)) <> NVL(l_msb_rec.DEMAND_TIME_FENCE_CODE,-999999))
1114 OR (NVL(cur.DEMAND_TIME_FENCE_DAYS, NVL(l_msb_rec.DEMAND_TIME_FENCE_DAYS,-999999)) <> NVL(l_msb_rec.DEMAND_TIME_FENCE_DAYS,-999999))
1115 OR (NVL(cur.RELEASE_TIME_FENCE_CODE, NVL(l_msb_rec.RELEASE_TIME_FENCE_CODE,-999999)) <> NVL(l_msb_rec.RELEASE_TIME_FENCE_CODE,-999999))
1116 OR (NVL(cur.RELEASE_TIME_FENCE_DAYS, NVL(l_msb_rec.RELEASE_TIME_FENCE_DAYS,-999999)) <> NVL(l_msb_rec.RELEASE_TIME_FENCE_DAYS,-999999))
1117 OR (NVL(cur.SUBSTITUTION_WINDOW_CODE, NVL(l_msb_rec.SUBSTITUTION_WINDOW_CODE,-999999)) <> NVL(l_msb_rec.SUBSTITUTION_WINDOW_CODE,-999999))
1118 OR (NVL(cur.SUBSTITUTION_WINDOW_DAYS, NVL(l_msb_rec.SUBSTITUTION_WINDOW_DAYS,-999999)) <> NVL(l_msb_rec.SUBSTITUTION_WINDOW_DAYS,-999999))
1119 OR (NVL(cur.DAYS_TGT_INV_SUPPLY, NVL(l_msb_rec.DAYS_TGT_INV_SUPPLY,-999999)) <> NVL(l_msb_rec.DAYS_TGT_INV_SUPPLY,-999999))
1120 OR (NVL(cur.DAYS_TGT_INV_WINDOW, NVL(l_msb_rec.DAYS_TGT_INV_WINDOW,-999999)) <> NVL(l_msb_rec.DAYS_TGT_INV_WINDOW,-999999))
1121 OR (NVL(cur.DAYS_MAX_INV_SUPPLY, NVL(l_msb_rec.DAYS_MAX_INV_SUPPLY,-999999)) <> NVL(l_msb_rec.DAYS_MAX_INV_SUPPLY,-999999))
1122 OR (NVL(cur.DAYS_MAX_INV_WINDOW, NVL(l_msb_rec.DAYS_MAX_INV_WINDOW,-999999)) <> NVL(l_msb_rec.DAYS_MAX_INV_WINDOW,-999999))
1123 OR (NVL(cur.CRITICAL_COMPONENT_FLAG, NVL(l_msb_rec.CRITICAL_COMPONENT_FLAG,-999999)) <> NVL(l_msb_rec.CRITICAL_COMPONENT_FLAG,-999999))
1124 OR (NVL(cur.CONVERGENCE, NVL(l_msb_rec.CONVERGENCE,-999999)) <> NVL(l_msb_rec.CONVERGENCE,-999999))
1125 OR (NVL(cur.CONTINOUS_TRANSFER, NVL(l_msb_rec.CONTINOUS_TRANSFER,-999999)) <> NVL(l_msb_rec.CONTINOUS_TRANSFER,-999999))
1126 OR (NVL(cur.DIVERGENCE, NVL(l_msb_rec.DIVERGENCE,-999999)) <> NVL(l_msb_rec.DIVERGENCE,-999999))
1127 OR (NVL(cur.DRP_PLANNED_FLAG, NVL(l_msb_rec.DRP_PLANNED_FLAG,-999999)) <> NVL(l_msb_rec.DRP_PLANNED_FLAG,-999999))
1128 OR (NVL(cur.REPAIR_LEADTIME, NVL(l_msb_rec.REPAIR_LEADTIME,-999999)) <> NVL(l_msb_rec.REPAIR_LEADTIME,-999999))
1129 OR (NVL(cur.REPAIR_YIELD, NVL(l_msb_rec.REPAIR_YIELD,-999999)) <> NVL(l_msb_rec.REPAIR_YIELD,-999999))
1130 OR (NVL(cur.PREPOSITION_POINT, NVL(l_msb_rec.PREPOSITION_POINT,'!')) <> NVL(l_msb_rec.PREPOSITION_POINT,'!'))
1131 OR (NVL(cur.REPAIR_PROGRAM, NVL(l_msb_rec.REPAIR_PROGRAM,-999999)) <> NVL(l_msb_rec.REPAIR_PROGRAM,-999999))
1132 THEN
1133 IF l_attr_grps IS NULL THEN
1134 l_attr_grps := TO_CHAR(get_attribute_group_id('MPSMRPPLANNING'));
1135 ELSE
1136 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('MPSMRPPLANNING'));
1137 END IF;
1138 END IF;
1139
1140 --Lead Times
1144 OR (NVL(cur.FIXED_LEAD_TIME, NVL(l_msb_rec.FIXED_LEAD_TIME,-999999)) <> NVL(l_msb_rec.FIXED_LEAD_TIME,-999999))
1141 IF (NVL(cur.PREPROCESSING_LEAD_TIME, NVL(l_msb_rec.PREPROCESSING_LEAD_TIME,-999999)) <> NVL(l_msb_rec.PREPROCESSING_LEAD_TIME,-999999))
1142 OR (NVL(cur.FULL_LEAD_TIME, NVL(l_msb_rec.FULL_LEAD_TIME,-999999)) <> NVL(l_msb_rec.FULL_LEAD_TIME,-999999))
1143 OR (NVL(cur.POSTPROCESSING_LEAD_TIME, NVL(l_msb_rec.POSTPROCESSING_LEAD_TIME,-999999)) <> NVL(l_msb_rec.POSTPROCESSING_LEAD_TIME,-999999))
1145 OR (NVL(cur.VARIABLE_LEAD_TIME, NVL(l_msb_rec.VARIABLE_LEAD_TIME,-999999)) <> NVL(l_msb_rec.VARIABLE_LEAD_TIME,-999999))
1146 OR (NVL(cur.CUM_MANUFACTURING_LEAD_TIME, NVL(l_msb_rec.CUM_MANUFACTURING_LEAD_TIME,-999999)) <> NVL(l_msb_rec.CUM_MANUFACTURING_LEAD_TIME,-999999))
1147 OR (NVL(cur.CUMULATIVE_TOTAL_LEAD_TIME, NVL(l_msb_rec.CUMULATIVE_TOTAL_LEAD_TIME,-999999)) <> NVL(l_msb_rec.CUMULATIVE_TOTAL_LEAD_TIME,-999999))
1148 OR (NVL(cur.LEAD_TIME_LOT_SIZE, NVL(l_msb_rec.LEAD_TIME_LOT_SIZE,-999999)) <> NVL(l_msb_rec.LEAD_TIME_LOT_SIZE,-999999))
1149 THEN
1150 IF l_attr_grps IS NULL THEN
1151 l_attr_grps := TO_CHAR(get_attribute_group_id('LEADTIMES'));
1152 ELSE
1153 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('LEADTIMES'));
1154 END IF;
1155 END IF;
1156
1157 --Work In Progress
1158 IF (NVL(cur.BUILD_IN_WIP_FLAG, NVL(l_msb_rec.BUILD_IN_WIP_FLAG,'!')) <> NVL(l_msb_rec.BUILD_IN_WIP_FLAG,'!'))
1159 OR (NVL(cur.WIP_SUPPLY_TYPE, NVL(l_msb_rec.WIP_SUPPLY_TYPE,-999999)) <> NVL(l_msb_rec.WIP_SUPPLY_TYPE,-999999))
1160 OR (NVL(cur.WIP_SUPPLY_SUBINVENTORY, NVL(l_msb_rec.WIP_SUPPLY_SUBINVENTORY,'!')) <> NVL(l_msb_rec.WIP_SUPPLY_SUBINVENTORY,'!'))
1161 OR (NVL(cur.WIP_SUPPLY_LOCATOR_ID, NVL(l_msb_rec.WIP_SUPPLY_LOCATOR_ID,-999999)) <> NVL(l_msb_rec.WIP_SUPPLY_LOCATOR_ID,-999999))
1162 OR (NVL(cur.OVERCOMPLETION_TOLERANCE_TYPE, NVL(l_msb_rec.OVERCOMPLETION_TOLERANCE_TYPE,-999999))<> NVL(l_msb_rec.OVERCOMPLETION_TOLERANCE_TYPE,-999999))
1163 OR (NVL(cur.OVERCOMPLETION_TOLERANCE_VALUE,NVL(l_msb_rec.OVERCOMPLETION_TOLERANCE_VALUE,-999999))<>NVL(l_msb_rec.OVERCOMPLETION_TOLERANCE_VALUE,-999999))
1164 OR (NVL(cur.INVENTORY_CARRY_PENALTY, NVL(l_msb_rec.INVENTORY_CARRY_PENALTY,-999999)) <> NVL(l_msb_rec.INVENTORY_CARRY_PENALTY,-999999))
1165 OR (NVL(cur.OPERATION_SLACK_PENALTY, NVL(l_msb_rec.OPERATION_SLACK_PENALTY,-999999)) <> NVL(l_msb_rec.OPERATION_SLACK_PENALTY,-999999))
1166 THEN
1167 IF l_attr_grps IS NULL THEN
1168 l_attr_grps := TO_CHAR(get_attribute_group_id('WORKINPROGRESS'));
1169 ELSE
1170 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('WORKINPROGRESS'));
1171 END IF;
1172 END IF;
1173
1174 --Order Management
1175 IF (NVL(cur.CUSTOMER_ORDER_FLAG, NVL(l_msb_rec.CUSTOMER_ORDER_FLAG,'!')) <> NVL(l_msb_rec.CUSTOMER_ORDER_FLAG,'!'))
1176 OR (NVL(cur.CUSTOMER_ORDER_ENABLED_FLAG, NVL(l_msb_rec.CUSTOMER_ORDER_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.CUSTOMER_ORDER_ENABLED_FLAG,'!'))
1177 OR (NVL(cur.INTERNAL_ORDER_FLAG, NVL(l_msb_rec.INTERNAL_ORDER_FLAG,'!')) <> NVL(l_msb_rec.INTERNAL_ORDER_FLAG,'!'))
1178 OR (NVL(cur.INTERNAL_ORDER_ENABLED_FLAG, NVL(l_msb_rec.INTERNAL_ORDER_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.INTERNAL_ORDER_ENABLED_FLAG,'!'))
1179 OR (NVL(cur.SHIPPABLE_ITEM_FLAG, NVL(l_msb_rec.SHIPPABLE_ITEM_FLAG,'!')) <> NVL(l_msb_rec.SHIPPABLE_ITEM_FLAG,'!'))
1180 OR (NVL(cur.SO_TRANSACTIONS_FLAG, NVL(l_msb_rec.SO_TRANSACTIONS_FLAG,'!')) <> NVL(l_msb_rec.SO_TRANSACTIONS_FLAG,'!'))
1181 OR (NVL(cur.DEFAULT_SHIPPING_ORG, NVL(l_msb_rec.DEFAULT_SHIPPING_ORG,-999999)) <> NVL(l_msb_rec.DEFAULT_SHIPPING_ORG,-999999))
1182 OR (NVL(cur.DEFAULT_SO_SOURCE_TYPE, NVL(l_msb_rec.DEFAULT_SO_SOURCE_TYPE,'!')) <> NVL(l_msb_rec.DEFAULT_SO_SOURCE_TYPE,'!'))
1183 OR (NVL(cur.PICK_COMPONENTS_FLAG, NVL(l_msb_rec.PICK_COMPONENTS_FLAG,'!')) <> NVL(l_msb_rec.PICK_COMPONENTS_FLAG,'!'))
1184 OR (NVL(cur.REPLENISH_TO_ORDER_FLAG, NVL(l_msb_rec.REPLENISH_TO_ORDER_FLAG,'!')) <> NVL(l_msb_rec.REPLENISH_TO_ORDER_FLAG,'!'))
1185 OR (NVL(cur.ATP_FLAG, NVL(l_msb_rec.ATP_FLAG,'!')) <> NVL(l_msb_rec.ATP_FLAG,'!'))
1186 OR (NVL(cur.ATP_COMPONENTS_FLAG, NVL(l_msb_rec.ATP_COMPONENTS_FLAG,'!')) <> NVL(l_msb_rec.ATP_COMPONENTS_FLAG,'!'))
1187 OR (NVL(cur.ATP_RULE_ID, NVL(l_msb_rec.ATP_RULE_ID,-999999)) <> NVL(l_msb_rec.ATP_RULE_ID,-999999))
1188 OR (NVL(cur.SHIP_MODEL_COMPLETE_FLAG, NVL(l_msb_rec.SHIP_MODEL_COMPLETE_FLAG,'!')) <> NVL(l_msb_rec.SHIP_MODEL_COMPLETE_FLAG,'!'))
1189 OR (NVL(cur.RETURNABLE_FLAG, NVL(l_msb_rec.RETURNABLE_FLAG,'!')) <> NVL(l_msb_rec.RETURNABLE_FLAG,'!'))
1190 OR (NVL(cur.RETURN_INSPECTION_REQUIREMENT, NVL(l_msb_rec.RETURN_INSPECTION_REQUIREMENT,-999999))<> NVL(l_msb_rec.RETURN_INSPECTION_REQUIREMENT,-999999))
1191 OR (NVL(cur.FINANCING_ALLOWED_FLAG, NVL(l_msb_rec.FINANCING_ALLOWED_FLAG,'!')) <> NVL(l_msb_rec.FINANCING_ALLOWED_FLAG,'!'))
1192 OR (NVL(cur.OVER_SHIPMENT_TOLERANCE, NVL(l_msb_rec.OVER_SHIPMENT_TOLERANCE,-999999)) <> NVL(l_msb_rec.OVER_SHIPMENT_TOLERANCE,-999999))
1193 OR (NVL(cur.OVER_SHIPMENT_TOLERANCE, NVL(l_msb_rec.OVER_SHIPMENT_TOLERANCE,-999999)) <> NVL(l_msb_rec.OVER_SHIPMENT_TOLERANCE,-999999))
1194 OR (NVL(cur.UNDER_SHIPMENT_TOLERANCE, NVL(l_msb_rec.UNDER_SHIPMENT_TOLERANCE,-999999)) <> NVL(l_msb_rec.UNDER_SHIPMENT_TOLERANCE,-999999))
1198 OR (NVL(cur.CHARGE_PERIODICITY_CODE, NVL(l_msb_rec.CHARGE_PERIODICITY_CODE,'!')) <> NVL(l_msb_rec.CHARGE_PERIODICITY_CODE,'!'))
1195 OR (NVL(cur.OVER_RETURN_TOLERANCE, NVL(l_msb_rec.OVER_RETURN_TOLERANCE,-999999)) <> NVL(l_msb_rec.OVER_RETURN_TOLERANCE,-999999))
1196 OR (NVL(cur.UNDER_RETURN_TOLERANCE, NVL(l_msb_rec.UNDER_RETURN_TOLERANCE,-999999)) <> NVL(l_msb_rec.UNDER_RETURN_TOLERANCE,-999999))
1197 OR (NVL(cur.PICKING_RULE_ID, NVL(l_msb_rec.PICKING_RULE_ID,-999999)) <> NVL(l_msb_rec.PICKING_RULE_ID,-999999))
1199 THEN
1200 IF l_attr_grps IS NULL THEN
1201 l_attr_grps := TO_CHAR(get_attribute_group_id('ORDERMANAGEMENT'));
1202 ELSE
1203 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('ORDERMANAGEMENT'));
1204 END IF;
1205 END IF;
1206
1207 --Service Attributes
1208 IF (NVL(cur.CONTRACT_ITEM_TYPE_CODE, NVL(l_msb_rec.CONTRACT_ITEM_TYPE_CODE,'!')) <> NVL(l_msb_rec.CONTRACT_ITEM_TYPE_CODE,'!'))
1209 OR (NVL(cur.COVERAGE_SCHEDULE_ID, NVL(l_msb_rec.COVERAGE_SCHEDULE_ID,-999999)) <> NVL(l_msb_rec.COVERAGE_SCHEDULE_ID,-999999))
1210 OR (NVL(cur.SERVICE_DURATION_PERIOD_CODE, NVL(l_msb_rec.SERVICE_DURATION_PERIOD_CODE,'!')) <> NVL(l_msb_rec.SERVICE_DURATION_PERIOD_CODE,'!'))
1211 OR (NVL(cur.MATERIAL_BILLABLE_FLAG, NVL(l_msb_rec.MATERIAL_BILLABLE_FLAG,'!')) <> NVL(l_msb_rec.MATERIAL_BILLABLE_FLAG,'!'))
1212 OR (NVL(cur.SERV_REQ_ENABLED_CODE, NVL(l_msb_rec.SERV_REQ_ENABLED_CODE,'!')) <> NVL(l_msb_rec.SERV_REQ_ENABLED_CODE,'!'))
1213 OR (NVL(cur.COMMS_ACTIVATION_REQD_FLAG, NVL(l_msb_rec.COMMS_ACTIVATION_REQD_FLAG,'!')) <> NVL(l_msb_rec.COMMS_ACTIVATION_REQD_FLAG,'!'))
1214 OR (NVL(cur.SERVICEABLE_PRODUCT_FLAG, NVL(l_msb_rec.SERVICEABLE_PRODUCT_FLAG,'!')) <> NVL(l_msb_rec.SERVICEABLE_PRODUCT_FLAG,'!'))
1215 OR (NVL(cur.SERV_BILLING_ENABLED_FLAG, NVL(l_msb_rec.SERV_BILLING_ENABLED_FLAG,'!')) <> NVL(l_msb_rec.SERV_BILLING_ENABLED_FLAG,'!'))
1216 OR (NVL(cur.DEFECT_TRACKING_ON_FLAG, NVL(l_msb_rec.DEFECT_TRACKING_ON_FLAG,'!')) <> NVL(l_msb_rec.DEFECT_TRACKING_ON_FLAG,'!'))
1217 OR (NVL(cur.RECOVERED_PART_DISP_CODE, NVL(l_msb_rec.RECOVERED_PART_DISP_CODE,'!')) <> NVL(l_msb_rec.RECOVERED_PART_DISP_CODE,'!'))
1218 OR (NVL(cur.COMMS_NL_TRACKABLE_FLAG, NVL(l_msb_rec.COMMS_NL_TRACKABLE_FLAG,'!')) <> NVL(l_msb_rec.COMMS_NL_TRACKABLE_FLAG,'!'))
1219 OR (NVL(cur.SERVICE_STARTING_DELAY, NVL(l_msb_rec.SERVICE_STARTING_DELAY,-999999)) <> NVL(l_msb_rec.SERVICE_STARTING_DELAY,-999999))
1220 OR (NVL(cur.ASSET_CREATION_CODE, NVL(l_msb_rec.ASSET_CREATION_CODE,'!')) <> NVL(l_msb_rec.ASSET_CREATION_CODE,'!'))
1221 THEN
1222 IF l_attr_grps IS NULL THEN
1223 l_attr_grps := TO_CHAR(get_attribute_group_id('SERVICE'));
1224 ELSE
1225 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('SERVICE'));
1226 END IF;
1227 END IF;
1228
1229 --Web Option
1230 IF (NVL(cur.WEB_STATUS, NVL(l_msb_rec.WEB_STATUS,'!')) <> NVL(l_msb_rec.WEB_STATUS,'!'))
1231 OR (NVL(cur.ORDERABLE_ON_WEB_FLAG, NVL(l_msb_rec.ORDERABLE_ON_WEB_FLAG,'!')) <> NVL(l_msb_rec.ORDERABLE_ON_WEB_FLAG,'!'))
1232 OR (NVL(cur.BACK_ORDERABLE_FLAG, NVL(l_msb_rec.BACK_ORDERABLE_FLAG,'!')) <> NVL(l_msb_rec.BACK_ORDERABLE_FLAG,'!'))
1233 OR (NVL(cur.MINIMUM_LICENSE_QUANTITY, NVL(l_msb_rec.MINIMUM_LICENSE_QUANTITY,-999999)) <> NVL(l_msb_rec.MINIMUM_LICENSE_QUANTITY,-999999))
1234 THEN
1235 IF l_attr_grps IS NULL THEN
1236 l_attr_grps := TO_CHAR(get_attribute_group_id('WEBOPTION'));
1237 ELSE
1238 l_attr_grps := l_attr_grps ||','||TO_CHAR(get_attribute_group_id('WEBOPTION'));
1239 END IF;
1240 END IF;
1241
1242 IF l_attr_grps IS NOT NULL THEN
1243 IF l_inv_debug_level IN(101, 102) THEN
1244 INVPUTLI.info('INVNIRIS.change_policy_check: Attr Grps Changed:' || l_attr_grps);
1245 END IF;
1246
1247 IF cur.current_phase_id is NOT NULL THEN
1248 --Bug 5367962 Find if any of the AG as ChanhePolicy of 'NOT ALLOWED'
1249 EXECUTE IMMEDIATE
1250 'BEGIN '||
1251 ' ENG_CHANGE_POLICY_PKG.GET_OPATTR_CHANGEPOLICY( '||
1252 ' P_API_VERSION => 1.0 '||
1253 ' ,X_RETURN_STATUS => :l_return_status '||
1254 ' ,P_CATALOG_CATEGORY_ID => :cur.item_catalog_group_id '||
1255 ' ,P_ITEM_LIFECYCLE_ID => :cur.lifecycle_id '||
1256 ' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '||
1257 ' ,P_ATTRIBUTE_GRP_IDS => :l_attr_grps '||
1258 ' ,X_POLICY_VALUE => :l_policy_value); '||
1259 ' EXCEPTION '||
1260 ' WHEN OTHERS THEN '||
1261 ' NULL; '||
1262 ' END; '
1263 USING OUT l_return_status,
1264 IN cur.item_catalog_group_id,
1265 IN cur.lifecycle_id,
1266 IN cur.current_phase_id,
1267 IN l_attr_grps,
1268 OUT l_policy_value;
1269
1273 IF l_policy_value = 'NOT_ALLOWED' THEN
1270 --If the above call returns NULL or ALLOWED CO is not required except the case
1271 --when add to CO is true
1272 --If NOT_ALLOWED is returned find which AG has it
1274 WHILE ((l_delim_pos > 0) AND (l_ch_policy_found = FALSE)) LOOP
1275 l_delim_pos := instr(l_attr_grps,',');
1276 IF(l_delim_pos = 0 AND l_attr_grps is not null) then
1277 l_attr_group_id := l_attr_grps;
1278 ELSE
1279 l_attr_group_id := substr(l_attr_grps,1,l_delim_pos-1);
1280 l_attr_grps := substr(l_attr_grps,l_delim_pos+1);
1281 END IF;
1282 EXECUTE IMMEDIATE
1283 'BEGIN '||
1284 ' ENG_CHANGE_POLICY_PKG.GET_OPATTR_CHANGEPOLICY( '||
1285 ' P_API_VERSION => 1.0 '||
1286 ' ,X_RETURN_STATUS => :l_return_status '||
1287 ' ,P_CATALOG_CATEGORY_ID => :cur.item_catalog_group_id '||
1288 ' ,P_ITEM_LIFECYCLE_ID => :cur.lifecycle_id '||
1289 ' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '||
1290 ' ,P_ATTRIBUTE_GRP_IDS => :l_attr_group_id '||
1291 ' ,X_POLICY_VALUE => :l_policy_value); '||
1292 ' EXCEPTION '||
1293 ' WHEN OTHERS THEN '||
1294 ' NULL; '||
1295 ' END; '
1296 USING OUT l_return_status,
1297 IN cur.item_catalog_group_id,
1298 IN cur.lifecycle_id,
1299 IN cur.current_phase_id,
1300 IN l_attr_group_id,
1301 OUT l_policy_value;
1302
1303 IF l_policy_value = 'NOT_ALLOWED' THEN
1304 l_ch_policy_found := TRUE;
1305 END IF;
1306 END LOOP;
1307 END IF; --l_policy_value = NOT ALLOWED
1308 ELSE --current_phase_id is NULL
1309 l_policy_value := 'CHANGE_ORDER_REQUIRED';
1310 END IF; --cur.current_phase_id is not NULL
1311
1312 IF l_policy_value = 'NOT_ALLOWED' THEN
1313 UPDATE mtl_system_items_interface
1314 SET process_flag = 3
1315 WHERE rowid = cur.rowid;
1316
1317 SELECT ATTR_GROUP_DISP_NAME INTO l_attr_grp_name
1318 FROM EGO_ATTR_GROUPS_V
1319 WHERE ATTR_GROUP_ID = l_attr_group_id;
1320
1321 FND_MESSAGE.SET_NAME('INV','INV_IOI_CHANGE_NOT_ALLOWED');
1322 FND_MESSAGE.SET_TOKEN('ATTR_GROUP_NAME',l_attr_grp_name);
1323 l_msg_text := FND_MESSAGE.GET;
1324 l_error_logged := INVPUOPI.mtl_log_interface_err(
1325 cur.organization_id,
1326 User_Id,
1327 Login_Id,
1328 Prog_AppId,
1329 Prog_Id,
1330 Request_id,
1331 cur.transaction_id,
1332 l_msg_text,
1333 'APPROVAL_STATUS',
1334 'MTL_SYTEM_ITEMS_INTERFACE',
1335 'INV_IOI_ERR_CHANGE_NOT_ALLOWED',--added by bug 11894684, replace INV_IOI_ERR with specific message name so we can identify error easily
1336 l_Err_Text);
1337 IF l_error_logged < 0 THEN
1338 Raise LOGGING_ERR;
1339 END IF;
1340 ELSIF l_policy_value = 'CHANGE_ORDER_REQUIRED' OR
1341 (NVL(l_import_co_option,'N') = 'Y' AND l_structure_type_id IS NOT NULL)
1342 THEN
1343 --4723028 If called from CO, dont move to 5.
1344 IF INSTR(l_process_control,'ENG_CALL:Y') = 0 THEN
1345 IF l_inv_debug_level IN(101, 102) THEN
1346 INVPUTLI.info('INVNIRIS.change_policy_check: Moving to 5 for CO');
1347 END IF;
1348
1349 --4696529: Insert row into interface table to propogate changes to production.
1350 IF l_msb_rec.inventory_item_status_code <> NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1351 OR l_msb_rec.description <> NVL(cur.description,l_msb_rec.description)
1352 THEN
1353 INSERT INTO mtl_system_items_interface(
1354 organization_id
1355 ,inventory_item_id
1356 ,process_flag
1357 ,set_process_id
1358 ,transaction_type
1359 ,transaction_id
1360 ,inventory_item_status_code
1361 ,allowed_units_lookup_code
1362 ,item_type
1363 ,description)
1364 VALUES(
1365 cur.organization_id
1366 ,cur.inventory_item_id
1367 ,1
1368 ,cur.set_process_id
1369 ,'UPDATE'
1370 ,mtl_system_items_interface_s.nextval
1371 ,cur.inventory_item_status_code --Bug 5383744
1372 ,cur.allowed_units_lookup_code
1373 ,cur.item_type
1374 ,NVL(cur.description,l_msb_rec.description));
1375 END IF;
1376
1377 UPDATE mtl_system_items_interface
1378 SET process_flag = 5,
1379 --bug 5383744
1380 inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1381 WHERE rowid = cur.rowid;
1382
1383 END IF; --ENG call
1384 END IF; --l_policy_value
1385 END IF; --l_attr_grps IS NOT NULL
1386
1387 END IF; -- NVL(l_msb_rec.approval_status,'A') ='A'
1388 END LOOP;
1389
1390 /*-- Bug 5216971 : Added the and condition on structure_type_id. Rows should not be
1391 marked to 5 if they are coming from structure import and have no changes on item
1392 specified. For rows which have changes to attributes will be set to 5
1393 in the above for loop. */
1394 -- 6157001 : Added the ENG_CALL check
1395 IF NVL(l_import_co_option,'N') = 'Y'
1396 AND l_structure_type_id IS NULL
1397 AND INSTR(l_process_control,'ENG_CALL:Y') = 0
1398 THEN
1399
1400 --Start: 5383744: Donot move 5 for no attrib values
1401 FOR cur IN c_check_attributes_policy LOOP
1402
1403 SELECT * INTO l_msb_rec
1404 FROM mtl_system_items
1405 WHERE inventory_item_id = cur.inventory_item_id
1406 AND organization_id = cur.organization_id;
1407
1408 --Inventory Attribute group
1409 IF cur.AUTO_LOT_ALPHA_PREFIX IS NOT NULL
1410 OR cur.AUTO_SERIAL_ALPHA_PREFIX IS NOT NULL
1411 OR cur.CYCLE_COUNT_ENABLED_FLAG IS NOT NULL
1412 OR cur.INVENTORY_ITEM_FLAG IS NOT NULL
1413 OR cur.LOCATION_CONTROL_CODE IS NOT NULL
1414 OR cur.MTL_TRANSACTIONS_ENABLED_FLAG IS NOT NULL
1415 OR cur.NEGATIVE_MEASUREMENT_ERROR IS NOT NULL
1416 OR cur.POSITIVE_MEASUREMENT_ERROR IS NOT NULL
1417 OR cur.RESERVABLE_TYPE IS NOT NULL
1418 OR cur.RESTRICT_LOCATORS_CODE IS NOT NULL
1419 OR cur.RESTRICT_SUBINVENTORIES_CODE IS NOT NULL
1420 OR cur.REVISION_QTY_CONTROL_CODE IS NOT NULL
1421 OR cur.SERIAL_NUMBER_CONTROL_CODE IS NOT NULL
1422 OR cur.SHELF_LIFE_CODE IS NOT NULL
1423 OR cur.SHELF_LIFE_DAYS IS NOT NULL
1424 OR cur.START_AUTO_LOT_NUMBER IS NOT NULL
1425 OR cur.START_AUTO_SERIAL_NUMBER IS NOT NULL
1426 OR cur.STOCK_ENABLED_FLAG IS NOT NULL
1427 OR cur.LOT_CONTROL_CODE IS NOT NULL
1428 OR cur.CHECK_SHORTAGES_FLAG IS NOT NULL
1429 OR cur.LOT_STATUS_ENABLED IS NOT NULL
1430 OR cur.DEFAULT_LOT_STATUS_ID IS NOT NULL
1431 OR cur.SERIAL_STATUS_ENABLED IS NOT NULL
1432 OR cur.DEFAULT_SERIAL_STATUS_ID IS NOT NULL
1433 OR cur.LOT_SPLIT_ENABLED IS NOT NULL
1434 OR cur.LOT_MERGE_ENABLED IS NOT NULL
1435 OR cur.BULK_PICKED_FLAG IS NOT NULL
1436 OR cur.LOT_TRANSLATE_ENABLED IS NOT NULL
1437 OR cur.LOT_SUBSTITUTION_ENABLED IS NOT NULL
1438 OR cur.LOT_DIVISIBLE_FLAG IS NOT NULL
1439 OR cur.GRADE_CONTROL_FLAG IS NOT NULL
1440 OR cur.DEFAULT_GRADE IS NOT NULL
1441 OR cur.CHILD_LOT_FLAG IS NOT NULL
1442 OR cur.PARENT_CHILD_GENERATION_FLAG IS NOT NULL
1443 OR cur.CHILD_LOT_PREFIX IS NOT NULL
1444 OR cur.CHILD_LOT_STARTING_NUMBER IS NOT NULL
1445 OR cur.CHILD_LOT_VALIDATION_FLAG IS NOT NULL
1446 OR cur.RETEST_INTERVAL IS NOT NULL
1447 OR cur.EXPIRATION_ACTION_INTERVAL IS NOT NULL
1448 OR cur.EXPIRATION_ACTION_CODE IS NOT NULL
1449 OR cur.MATURITY_DAYS IS NOT NULL
1450 OR cur.HOLD_DAYS IS NOT NULL
1451 OR cur.COPY_LOT_ATTRIBUTE_FLAG IS NOT NULL
1452
1453
1454 --BOM Attribute Group
1455 OR cur.BASE_ITEM_ID IS NOT NULL
1456 OR cur.BOM_ENABLED_FLAG IS NOT NULL
1457 OR cur.BOM_ITEM_TYPE IS NOT NULL
1458 OR cur.ENG_ITEM_FLAG IS NOT NULL
1459 OR cur.EFFECTIVITY_CONTROL IS NOT NULL
1460 OR cur.CONFIG_MODEL_TYPE IS NOT NULL
1461 OR cur.AUTO_CREATED_CONFIG_FLAG IS NOT NULL
1462 OR cur.CONFIG_ORGS IS NOT NULL
1463 OR cur.CONFIG_MATCH IS NOT NULL
1464
1465 --Asset Management Group
1466 OR cur.EAM_ITEM_TYPE IS NOT NULL
1467 OR cur.EAM_ACTIVITY_TYPE_CODE IS NOT NULL
1468 OR cur.EAM_ACTIVITY_CAUSE_CODE IS NOT NULL
1469 OR cur.EAM_ACT_SHUTDOWN_STATUS IS NOT NULL
1470 OR cur.EAM_ACT_NOTIFICATION_FLAG IS NOT NULL
1471 OR cur.EAM_ACTIVITY_SOURCE_CODE IS NOT NULL
1472
1473 --Costing Attribute Group
1474 OR cur.COSTING_ENABLED_FLAG IS NOT NULL
1475 OR cur.COST_OF_SALES_ACCOUNT IS NOT NULL
1476 OR cur.DEFAULT_INCLUDE_IN_ROLLUP_FLAG IS NOT NULL
1477 OR cur.STD_LOT_SIZE IS NOT NULL
1478 OR cur.INVENTORY_ASSET_FLAG IS NOT NULL
1479
1480 --Purchasing Attribute Group
1481 OR cur.ALLOW_ITEM_DESC_UPDATE_FLAG IS NOT NULL
1482 OR cur.ASSET_CATEGORY_ID IS NOT NULL
1483 OR cur.BUYER_ID IS NOT NULL
1484 OR cur.ENCUMBRANCE_ACCOUNT IS NOT NULL
1485 OR cur.EXPENSE_ACCOUNT IS NOT NULL
1486 OR cur.HAZARD_CLASS_ID IS NOT NULL
1487 OR cur.LIST_PRICE_PER_UNIT IS NOT NULL
1488 OR cur.MARKET_PRICE IS NOT NULL
1489 OR cur.MUST_USE_APPROVED_VENDOR_FLAG IS NOT NULL
1490 OR cur.OUTSIDE_OPERATION_UOM_TYPE IS NOT NULL
1491 OR cur.PRICE_TOLERANCE_PERCENT IS NOT NULL
1492 OR cur.PURCHASING_ITEM_FLAG IS NOT NULL
1493 OR cur.RFQ_REQUIRED_FLAG IS NOT NULL
1494 OR cur.ROUNDING_FACTOR IS NOT NULL
1495 OR cur.TAXABLE_FLAG IS NOT NULL
1496 OR cur.UNIT_OF_ISSUE IS NOT NULL
1497 OR cur.UN_NUMBER_ID IS NOT NULL
1498 OR cur.INSPECTION_REQUIRED_FLAG IS NOT NULL
1499 OR cur.INVOICE_CLOSE_TOLERANCE IS NOT NULL
1500 OR cur.RECEIPT_REQUIRED_FLAG IS NOT NULL
1501 OR cur.RECEIVE_CLOSE_TOLERANCE IS NOT NULL
1502 OR cur.OUTSIDE_OPERATION_FLAG IS NOT NULL
1503 OR cur.PURCHASING_TAX_CODE IS NOT NULL
1504 OR cur.OUTSOURCED_ASSEMBLY IS NOT NULL
1505 OR cur.PURCHASING_ENABLED_FLAG IS NOT NULL
1506
1507 --Receiving Attribute Group
1508 OR cur.ALLOW_EXPRESS_DELIVERY_FLAG IS NOT NULL
1509 OR cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG IS NOT NULL
1510 OR cur.ALLOW_UNORDERED_RECEIPTS_FLAG IS NOT NULL
1511 OR cur.DAYS_EARLY_RECEIPT_ALLOWED IS NOT NULL
1512 OR cur.ENFORCE_SHIP_TO_LOCATION_CODE IS NOT NULL
1513 OR cur.QTY_RCV_TOLERANCE IS NOT NULL
1514 OR cur.RECEIPT_DAYS_EXCEPTION_CODE IS NOT NULL
1515 OR cur.RECEIVING_ROUTING_ID IS NOT NULL
1516 OR cur.DAYS_LATE_RECEIPT_ALLOWED IS NOT NULL
1517 OR cur.QTY_RCV_EXCEPTION_CODE IS NOT NULL
1518
1519 --Process Manufacturing Attribute Group
1520 OR cur.RECIPE_ENABLED_FLAG IS NOT NULL
1521 OR cur.CAS_NUMBER IS NOT NULL
1522 OR cur.HAZARDOUS_MATERIAL_FLAG IS NOT NULL
1523 OR cur.PROCESS_YIELD_LOCATOR_ID IS NOT NULL
1524 OR cur.PROCESS_QUALITY_ENABLED_FLAG IS NOT NULL
1525 OR cur.PROCESS_SUPPLY_LOCATOR_ID IS NOT NULL
1526 OR cur.PROCESS_EXECUTION_ENABLED_FLAG IS NOT NULL
1527 OR cur.PROCESS_COSTING_ENABLED_FLAG IS NOT NULL
1528 OR cur.PROCESS_SUPPLY_SUBINVENTORY IS NOT NULL
1529 OR cur.PROCESS_YIELD_SUBINVENTORY IS NOT NULL
1530
1531 --Physical Attributes
1532 OR cur.UNIT_WEIGHT IS NOT NULL
1533 OR cur.VOLUME_UOM_CODE IS NOT NULL
1534 OR cur.WEIGHT_UOM_CODE IS NOT NULL
1535 OR cur.COLLATERAL_FLAG IS NOT NULL
1536 OR cur.VEHICLE_ITEM_FLAG IS NOT NULL
1537 OR cur.MAXIMUM_LOAD_WEIGHT IS NOT NULL
1538 OR cur.MINIMUM_FILL_PERCENT IS NOT NULL
1539 OR cur.UNIT_VOLUME IS NOT NULL
1540 OR cur.CONTAINER_ITEM_FLAG IS NOT NULL
1541 OR cur.INTERNAL_VOLUME IS NOT NULL
1542 OR cur.CONTAINER_TYPE_CODE IS NOT NULL
1543 OR cur.EQUIPMENT_TYPE IS NOT NULL
1544 OR cur.EVENT_FLAG IS NOT NULL
1545 OR cur.ELECTRONIC_FLAG IS NOT NULL
1546 OR cur.DOWNLOADABLE_FLAG IS NOT NULL
1547 OR cur.INDIVISIBLE_FLAG IS NOT NULL
1548 OR cur.DIMENSION_UOM_CODE IS NOT NULL
1549 OR cur.UNIT_LENGTH IS NOT NULL
1550 OR cur.UNIT_WIDTH IS NOT NULL
1551 OR cur.UNIT_HEIGHT IS NOT NULL
1552
1553 --General Planning
1554 OR cur.FIXED_DAYS_SUPPLY IS NOT NULL
1555 OR cur.FIXED_LOT_MULTIPLIER IS NOT NULL
1556 OR cur.FIXED_ORDER_QUANTITY IS NOT NULL
1557 OR cur.INVENTORY_PLANNING_CODE IS NOT NULL
1558 OR cur.MAXIMUM_ORDER_QUANTITY IS NOT NULL
1559 OR cur.MAX_MINMAX_QUANTITY IS NOT NULL
1560 OR cur.MINIMUM_ORDER_QUANTITY IS NOT NULL
1561 OR cur.MIN_MINMAX_QUANTITY IS NOT NULL
1562 OR cur.MRP_SAFETY_STOCK_PERCENT IS NOT NULL
1563 OR cur.ORDER_COST IS NOT NULL
1564 OR cur.PLANNER_CODE IS NOT NULL
1565 OR cur.SAFETY_STOCK_BUCKET_DAYS IS NOT NULL
1566 OR cur.SOURCE_SUBINVENTORY IS NOT NULL
1567 OR cur.SOURCE_TYPE IS NOT NULL
1568 OR cur.CARRYING_COST IS NOT NULL
1569 OR cur.MRP_SAFETY_STOCK_CODE IS NOT NULL
1570 OR cur.SOURCE_ORGANIZATION_ID IS NOT NULL
1571 OR cur.PLANNING_MAKE_BUY_CODE IS NOT NULL
1572 OR cur.VMI_MINIMUM_UNITS IS NOT NULL
1573 OR cur.VMI_MINIMUM_DAYS IS NOT NULL
1574 OR cur.VMI_MAXIMUM_UNITS IS NOT NULL
1575 OR cur.VMI_MAXIMUM_DAYS IS NOT NULL
1576 OR cur.VMI_FIXED_ORDER_QUANTITY IS NOT NULL
1577 OR cur.SO_AUTHORIZATION_FLAG IS NOT NULL
1578 OR cur.CONSIGNED_FLAG IS NOT NULL
1579 OR cur.VMI_FORECAST_TYPE IS NOT NULL
1580 OR cur.FORECAST_HORIZON IS NOT NULL
1581 OR cur.ASN_AUTOEXPIRE_FLAG IS NOT NULL
1582 OR cur.SUBCONTRACTING_COMPONENT IS NOT NULL
1583
1584 --MPS/MRP Planning
1585 OR cur.ACCEPTABLE_EARLY_DAYS IS NOT NULL
1586 OR cur.ACCEPTABLE_RATE_DECREASE IS NOT NULL
1587 OR cur.ACCEPTABLE_RATE_INCREASE IS NOT NULL
1588 OR cur.AUTO_REDUCE_MPS IS NOT NULL
1589 OR cur.DEMAND_TIME_FENCE_CODE IS NOT NULL
1590 OR cur.DEMAND_TIME_FENCE_DAYS IS NOT NULL
1591 OR cur.MRP_CALCULATE_ATP_FLAG IS NOT NULL
1592 OR cur.MRP_PLANNING_CODE IS NOT NULL
1593 OR cur.OVERRUN_PERCENTAGE IS NOT NULL
1594 OR cur.PLANNING_EXCEPTION_SET IS NOT NULL
1595 OR cur.PLANNING_TIME_FENCE_CODE IS NOT NULL
1596 OR cur.PLANNING_TIME_FENCE_DAYS IS NOT NULL
1597 OR cur.REPETITIVE_PLANNING_FLAG IS NOT NULL
1598 OR cur.ROUNDING_CONTROL_TYPE IS NOT NULL
1599 OR cur.SHRINKAGE_RATE IS NOT NULL
1600 OR cur.ATO_FORECAST_CONTROL IS NOT NULL
1601 OR cur.END_ASSEMBLY_PEGGING_FLAG IS NOT NULL
1602 OR cur.RELEASE_TIME_FENCE_CODE IS NOT NULL
1603 OR cur.RELEASE_TIME_FENCE_DAYS IS NOT NULL
1604 OR cur.PLANNED_INV_POINT_FLAG IS NOT NULL
1605 OR cur.CREATE_SUPPLY_FLAG IS NOT NULL
1606 OR cur.SUBSTITUTION_WINDOW_CODE IS NOT NULL
1607 OR cur.SUBSTITUTION_WINDOW_DAYS IS NOT NULL
1608 OR cur.CRITICAL_COMPONENT_FLAG IS NOT NULL
1609 OR cur.EXCLUDE_FROM_BUDGET_FLAG IS NOT NULL
1610 OR cur.DRP_PLANNED_FLAG IS NOT NULL
1611 OR cur.DAYS_MAX_INV_SUPPLY IS NOT NULL
1612 OR cur.DAYS_MAX_INV_WINDOW IS NOT NULL
1613 OR cur.DAYS_TGT_INV_SUPPLY IS NOT NULL
1614 OR cur.DAYS_TGT_INV_WINDOW IS NOT NULL
1615 OR cur.CONTINOUS_TRANSFER IS NOT NULL
1616 OR cur.CONVERGENCE IS NOT NULL
1617 OR cur.DIVERGENCE IS NOT NULL
1618 OR cur.REPAIR_PROGRAM IS NOT NULL
1619 OR cur.REPAIR_LEADTIME IS NOT NULL
1620 OR cur.REPAIR_YIELD IS NOT NULL
1621 OR cur.PREPOSITION_POINT IS NOT NULL
1622
1623 --Lead Times
1624 OR cur.CUM_MANUFACTURING_LEAD_TIME IS NOT NULL
1625 OR cur.FIXED_LEAD_TIME IS NOT NULL
1626 OR cur.VARIABLE_LEAD_TIME IS NOT NULL
1627 OR cur.FULL_LEAD_TIME IS NOT NULL
1628 OR cur.POSTPROCESSING_LEAD_TIME IS NOT NULL
1629 OR cur.PREPROCESSING_LEAD_TIME IS NOT NULL
1630 OR cur.LEAD_TIME_LOT_SIZE IS NOT NULL
1631 OR cur.CUMULATIVE_TOTAL_LEAD_TIME IS NOT NULL
1632
1633 --Work In Progress
1634 OR cur.WIP_SUPPLY_TYPE IS NOT NULL
1635 OR cur.OVERCOMPLETION_TOLERANCE_TYPE IS NOT NULL
1636 OR cur.INVENTORY_CARRY_PENALTY IS NOT NULL
1637 OR cur.OPERATION_SLACK_PENALTY IS NOT NULL
1638 OR cur.OVERCOMPLETION_TOLERANCE_VALUE IS NOT NULL
1639 OR cur.WIP_SUPPLY_SUBINVENTORY IS NOT NULL
1640 OR cur.WIP_SUPPLY_LOCATOR_ID IS NOT NULL
1641 OR cur.BUILD_IN_WIP_FLAG IS NOT NULL
1642
1643 --Order Management
1644 OR cur.ATP_COMPONENTS_FLAG IS NOT NULL
1645 OR cur.ATP_FLAG IS NOT NULL
1646 OR cur.ATP_RULE_ID IS NOT NULL
1647 OR cur.CUSTOMER_ORDER_ENABLED_FLAG IS NOT NULL
1648 OR cur.CUSTOMER_ORDER_FLAG IS NOT NULL
1649 OR cur.DEFAULT_SHIPPING_ORG IS NOT NULL
1650 OR cur.INTERNAL_ORDER_ENABLED_FLAG IS NOT NULL
1651 OR cur.INTERNAL_ORDER_FLAG IS NOT NULL
1652 OR cur.PICKING_RULE_ID IS NOT NULL
1653 OR cur.PICK_COMPONENTS_FLAG IS NOT NULL
1654 OR cur.RETURNABLE_FLAG IS NOT NULL
1655 OR cur.RETURN_INSPECTION_REQUIREMENT IS NOT NULL
1656 OR cur.SHIPPABLE_ITEM_FLAG IS NOT NULL
1657 OR cur.SHIP_MODEL_COMPLETE_FLAG IS NOT NULL
1658 OR cur.SO_TRANSACTIONS_FLAG IS NOT NULL
1659 OR cur.REPLENISH_TO_ORDER_FLAG IS NOT NULL
1660 OR cur.OVER_SHIPMENT_TOLERANCE IS NOT NULL
1661 OR cur.UNDER_SHIPMENT_TOLERANCE IS NOT NULL
1662 OR cur.OVER_RETURN_TOLERANCE IS NOT NULL
1663 OR cur.UNDER_RETURN_TOLERANCE IS NOT NULL
1664 OR cur.VOL_DISCOUNT_EXEMPT_FLAG IS NOT NULL
1665 OR cur.COUPON_EXEMPT_FLAG IS NOT NULL
1666 OR cur.FINANCING_ALLOWED_FLAG IS NOT NULL
1667 OR cur.DEFAULT_SO_SOURCE_TYPE IS NOT NULL
1668 OR cur.CHARGE_PERIODICITY_CODE IS NOT NULL
1669
1670 --Service Attributes
1671 OR cur.COVERAGE_SCHEDULE_ID IS NOT NULL
1672 OR cur.MATERIAL_BILLABLE_FLAG IS NOT NULL
1673 OR cur.MAX_WARRANTY_AMOUNT IS NOT NULL
1674 OR cur.PREVENTIVE_MAINTENANCE_FLAG IS NOT NULL
1675 OR cur.PRORATE_SERVICE_FLAG IS NOT NULL
1676 OR cur.RESPONSE_TIME_PERIOD_CODE IS NOT NULL
1677 OR cur.RESPONSE_TIME_VALUE IS NOT NULL
1678 OR cur.SERVICE_DURATION IS NOT NULL
1679 OR cur.SERVICE_DURATION_PERIOD_CODE IS NOT NULL
1680 OR cur.SERVICE_ITEM_FLAG IS NOT NULL
1681 OR cur.WARRANTY_VENDOR_ID IS NOT NULL
1682 OR cur.SERVICEABLE_PRODUCT_FLAG IS NOT NULL
1683 OR cur.VENDOR_WARRANTY_FLAG IS NOT NULL
1684 OR cur.SERVICE_STARTING_DELAY IS NOT NULL
1685 OR cur.USAGE_ITEM_FLAG IS NOT NULL
1686 OR cur.RECOVERED_PART_DISP_CODE IS NOT NULL
1687 OR cur.DEFECT_TRACKING_ON_FLAG IS NOT NULL
1688 OR cur.COMMS_NL_TRACKABLE_FLAG IS NOT NULL
1689 OR cur.ASSET_CREATION_CODE IS NOT NULL
1690 OR cur.COMMS_ACTIVATION_REQD_FLAG IS NOT NULL
1691 OR cur.CONTRACT_ITEM_TYPE_CODE IS NOT NULL
1692 OR cur.SERV_REQ_ENABLED_CODE IS NOT NULL
1693 OR cur.SERV_BILLING_ENABLED_FLAG IS NOT NULL
1694 OR cur.IB_ITEM_INSTANCE_CLASS IS NOT NULL
1695
1696 --Web Option
1697 OR cur.ORDERABLE_ON_WEB_FLAG IS NOT NULL
1698 OR cur.BACK_ORDERABLE_FLAG IS NOT NULL
1699 OR cur.WEB_STATUS IS NOT NULL
1700 OR cur.MINIMUM_LICENSE_QUANTITY IS NOT NULL
1701 THEN
1702 IF l_inv_debug_level IN(101, 102) THEN
1703 INVPUTLI.info('INVNIRIS.change_policy_check: Some Attr is non null');
1704 END IF;
1705
1706 l_values_provided := TRUE;
1707 ELSE
1708 l_values_provided := FALSE;
1709 END IF;
1710
1711 IF l_msb_rec.inventory_item_status_code <> NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1712 OR l_msb_rec.description <> NVL(cur.description,l_msb_rec.description)
1713 THEN
1714 l_desc_status_change := TRUE;
1715 ELSE
1716 l_desc_status_change := FALSE;
1717 END IF;
1718
1719 IF l_values_provided THEN
1720 UPDATE mtl_system_items_interface msi
1721 SET process_flag = 5,
1722 --Bug 5383744
1723 inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1724 WHERE rowid = cur.rowid;
1725
1726 IF l_desc_status_change THEN
1727 INSERT INTO mtl_system_items_interface(
1728 organization_id
1729 ,inventory_item_id
1730 ,process_flag
1731 ,set_process_id
1732 ,transaction_type
1733 ,transaction_id
1734 ,inventory_item_status_code
1735 ,allowed_units_lookup_code
1736 ,item_type
1737 ,description)
1738 VALUES(
1739 cur.organization_id
1740 ,cur.inventory_item_id
1741 ,1
1742 ,cur.set_process_id
1743 ,'UPDATE'
1744 ,mtl_system_items_interface_s.nextval
1745 ,cur.inventory_item_status_code --Bug 5383744
1746 ,cur.allowed_units_lookup_code
1747 ,cur.item_type
1748 ,NVL(cur.description,l_msb_rec.description));
1749 END IF;
1750
1751 ELSIF NOT l_desc_status_change THEN
1752
1753 UPDATE mtl_system_items_interface msi
1754 SET process_flag = 7
1755 WHERE rowid = cur.rowid;
1756
1757 END IF; -- IF l_values_provided THEN
1758 --Start: 5383744: Donot move 5 for no attrib values
1759
1760 END LOOP;
1761
1762 END IF; -- NVL(l_import_co_option,'N') = 'Y' AND l_structure_type_id IS NULL
1763
1764 END IF; -- INTSR('ITEM_APPROVAL:A')
1765
1766 RETURN(l_ret_code);
1767
1768 EXCEPTION
1769 WHEN LOGGING_ERR then
1770 IF l_inv_debug_level IN(101, 102) THEN
1771 INVPUTLI.info(l_Err_Text);
1772 END IF;
1773 RETURN(l_error_logged);
1774 WHEN OTHERS THEN
1775 err_text := substr('INVNIRIS.change_policy_check ' || SQLERRM, 1,240);
1776 IF l_inv_debug_level IN(101, 102) THEN
1777 INVPUTLI.info(err_text);
1778 END IF;
1779 RETURN(SQLCODE);
1780 END change_policy_check;
1781
1782 END INVNIRIS;