[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msii.INVENTORY_ITEM_ID,
msii.ITEM_CATALOG_GROUP_ID,
msii.ORGANIZATION_ID,
msii.TRANSACTION_ID,
msii.ITEM_NUMBER,
msii.rowid,
msii.ENG_ITEM_FLAG,
-- micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
mp.ORGANIZATION_CODE,
mp.MASTER_ORGANIZATION_ID,
msii.TRANSACTION_TYPE
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
--MTL_ITEM_CATALOG_GROUPS_B micb,
MTL_PARAMETERS mp
WHERE ( (msii.organization_id + 0 = org_id) OR (all_Org = 1) )
AND msii.process_flag = cp_process_flag
AND msii.set_process_id = xset_id
--AND msii.ITEM_CATALOG_GROUP_ID = micb.ITEM_CATALOG_GROUP_ID
--AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
AND mp.ORGANIZATION_ID = msii.ORGANIZATION_ID
FOR UPDATE OF msii.INVENTORY_ITEM_ID;
SELECT ICC.NEW_ITEM_REQUEST_REQD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_item_catalog_group_id
ORDER BY LEVEL ASC;
SELECT NVL(add_all_to_change_flag,'N')
FROM ego_import_option_sets
WHERE batch_id = xset_id;
IF cur.TRANSACTION_TYPE = 'UPDATE' THEN
BEGIN
SELECT approval_status
INTO l_item_approval_status
FROM mtl_system_items_b
WHERE inventory_item_id = cur.INVENTORY_ITEM_ID
AND organization_id = cur.ORGANIZATION_ID;
SELECT msi.approval_status
INTO l_item_approval_status
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = cur.inventory_item_id
AND msi.organization_id = (SELECT mp.master_organization_id
FROM mtl_parameters mp
WHERE mp.organization_id = cur.organization_id);
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE CURRENT OF c_get_processed_records;
OR((cur.TRANSACTION_TYPE = 'UPDATE') AND (NVL(l_item_approval_status, 'A') <> 'A')))
THEN
l_nir_rec_exists := true;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
SET I.SET_PROCESS_ID = I.SET_PROCESS_ID + 3000000000000,
I.ENG_ITEM_FLAG = 'Y', --Bug 5519768
I.SUMMARY_FLAG = 'Y',
I.ENABLED_FLAG = 'Y',
I.BUYER_ID = NULL,
I.ACCOUNTING_RULE_ID = NULL,
I.INVOICING_RULE_ID = NULL,
I.COLLATERAL_FLAG = NULL,
I.STOCK_ENABLED_FLAG = 'N',
I.MTL_TRANSACTIONS_ENABLED_FLAG = 'N',
I.INTERNAL_ORDER_ENABLED_FLAG = 'N',
I.INVOICE_ENABLED_FLAG = 'N',
-- Bug 5738958: BOM Allowed Flag can be updated even when Item Status is Pending
-- I.BOM_ENABLED_FLAG = 'N',
I.BUILD_IN_WIP_FLAG = 'N',
I.CUSTOMER_ORDER_ENABLED_FLAG = 'N',
I.PURCHASING_ENABLED_FLAG = 'N',
I.INVENTORY_ITEM_FLAG = 'N',
I.WIP_SUPPLY_TYPE = 1,
I.AUTO_CREATED_CONFIG_FLAG = 'N',
I.CYCLE_COUNT_ENABLED_FLAG = 'N',
I.INTERNAL_ORDER_FLAG = 'N',
I.INVENTORY_ITEM_STATUS_CODE = 'Pending',
I.INVENTORY_PLANNING_CODE = 6,
I.MRP_PLANNING_CODE = 6,
I.INVENTORY_ASSET_FLAG = 'N',
I.INVOICEABLE_ITEM_FLAG = 'N',
I.EXPENSE_BILLABLE_FLAG = NULL,
I.BOM_ITEM_TYPE = 4,
I.COSTING_ENABLED_FLAG = 'N',
I.CUSTOMER_ORDER_FLAG = 'N',
-- I.ALLOWED_UNITS_LOOKUP_CODE = 3, --BUG 7255713
I.ATP_COMPONENTS_FLAG = 'N',
I.ATP_FLAG = 'N',
I.TIME_BILLABLE_FLAG = NULL,
I.SERVICEABLE_PRODUCT_FLAG = 'N',
I.SHELF_LIFE_CODE = 1,
I.SHIPPABLE_ITEM_FLAG = 'N',
I.SO_TRANSACTIONS_FLAG = 'N',
I.SERVICEABLE_COMPONENT_FLAG = 'N',
I.REPLENISH_TO_ORDER_FLAG = 'N',
I.RESERVABLE_TYPE = 1,
I.RESTRICT_LOCATORS_CODE = 2,
I.RESTRICT_SUBINVENTORIES_CODE = 2,
I.REVISION_QTY_CONTROL_CODE = 1,
I.SERIAL_NUMBER_CONTROL_CODE = 1,
I.PREVENTIVE_MAINTENANCE_FLAG = 'N',
I.SERV_BILLING_ENABLED_FLAG = 'N',
I.PRORATE_SERVICE_FLAG = 'N',
I.PURCHASING_ITEM_FLAG = 'N',
I.OUTSIDE_OPERATION_FLAG = 'N',
I.PICK_COMPONENTS_FLAG = 'N',
I.PLANNING_MAKE_BUY_CODE = 2,
I.PLANNING_TIME_FENCE_CODE = 4,
I.PLANNING_TIME_FENCE_DAYS = 1,
I.MUST_USE_APPROVED_VENDOR_FLAG = 'N',
I.LOCATION_CONTROL_CODE = 1,
I.LOT_CONTROL_CODE = 1,
I.MRP_SAFETY_STOCK_CODE = 1,
I.SHIP_MODEL_COMPLETE_FLAG = 'N',
I.MARKET_PRICE = NULL,
I.LIST_PRICE_PER_UNIT = NULL,
I.PRICE_TOLERANCE_PERCENT = NULL,
I.SHELF_LIFE_DAYS = 0,
I.REPETITIVE_PLANNING_FLAG = 'N',
I.ACCEPTABLE_RATE_DECREASE = 0,
I.ACCEPTABLE_RATE_INCREASE = 0,
I.POSTPROCESSING_LEAD_TIME = 0,
I.RETURN_INSPECTION_REQUIREMENT = 2,
I.CONTAINER_ITEM_FLAG = 'N',
I.VEHICLE_ITEM_FLAG = 'N',
I.SERVICE_DURATION = NULL,
I.RETURNABLE_FLAG = 'N',
I.LEAD_TIME_LOT_SIZE = 1,
I.CHECK_SHORTAGES_FLAG = 'N',
I.EFFECTIVITY_CONTROL = 1,
I.EQUIPMENT_TYPE = 2,
I.COMMS_NL_TRACKABLE_FLAG = NULL,
I.WEB_STATUS = 'UNPUBLISHED',
I.BULK_PICKED_FLAG = 'N',
I.LOT_STATUS_ENABLED = 'N',
I.DEFAULT_LOT_STATUS_ID = NULL,
I.SERIAL_STATUS_ENABLED = 'N',
I.DEFAULT_SERIAL_STATUS_ID = NULL,
I.DUAL_UOM_CONTROL = 1,
I.LOT_SPLIT_ENABLED = 'N',
I.LOT_MERGE_ENABLED = 'N',
I.LOT_TRANSLATE_ENABLED = 'N',
I.DEFAULT_SO_SOURCE_TYPE = 'INTERNAL',
I.CREATE_SUPPLY_FLAG = 'Y',
-- I.TRACKING_QUANTITY_IND = 'P', --BUG 7255713
-- I.ONT_PRICING_QTY_SOURCE = 'P', --BUG 7255713
-- I.DUAL_UOM_DEVIATION_HIGH = 0, --BUG 7255713
-- I.DUAL_UOM_DEVIATION_LOW = 0, --BUG 7255713
I.VMI_MINIMUM_UNITS = NULL,
I.VMI_MINIMUM_DAYS = NULL,
I.VMI_MAXIMUM_UNITS = NULL,
I.VMI_MAXIMUM_DAYS = NULL,
I.VMI_FIXED_ORDER_QUANTITY = NULL,
I.SO_AUTHORIZATION_FLAG = NULL,
I.CONSIGNED_FLAG = 2,
I.ASN_AUTOEXPIRE_FLAG = 2,
I.VMI_FORECAST_TYPE = 1,
I.FORECAST_HORIZON = NULL,
I.EXCLUDE_FROM_BUDGET_FLAG = 2,
I.DAYS_TGT_INV_SUPPLY = NULL,
I.DAYS_TGT_INV_WINDOW = NULL,
I.DAYS_MAX_INV_SUPPLY = NULL,
I.DAYS_MAX_INV_WINDOW = NULL,
I.DRP_PLANNED_FLAG = 2,
I.CRITICAL_COMPONENT_FLAG = 2,
I.CONTINOUS_TRANSFER = 3,
I.CONVERGENCE = 3,
I.DIVERGENCE = 3,
I.ACCEPTABLE_EARLY_DAYS = NULL,
I.ALLOW_EXPRESS_DELIVERY_FLAG = NULL,
I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG = NULL,
I.ALLOW_UNORDERED_RECEIPTS_FLAG = NULL,
I.ASSET_CATEGORY_ID = NULL,
I.ASSET_CREATION_CODE = NULL,
I.ATO_FORECAST_CONTROL = NULL,
I.ATP_RULE_ID = NULL,
I.AUTO_LOT_ALPHA_PREFIX = NULL,
I.AUTO_REDUCE_MPS = NULL,
I.AUTO_SERIAL_ALPHA_PREFIX = NULL,
I.BACK_ORDERABLE_FLAG = NULL,
I.BASE_ITEM_ID = NULL,
I.BASE_WARRANTY_SERVICE_ID = NULL,
I.CARRYING_COST = NULL,
I.CATALOG_STATUS_FLAG = NULL,
I.COMMS_ACTIVATION_REQD_FLAG = NULL,
I.CONFIG_MATCH = NULL,
I.CONFIG_MODEL_TYPE = NULL,
I.CONFIG_ORGS = NULL,
I.CONTAINER_TYPE_CODE = NULL,
I.CONTRACT_ITEM_TYPE_CODE = NULL,
I.COUPON_EXEMPT_FLAG = NULL,
I.COVERAGE_SCHEDULE_ID = NULL,
I.CUM_MANUFACTURING_LEAD_TIME = NULL,
I.CUMULATIVE_TOTAL_LEAD_TIME = NULL,
I.DAYS_EARLY_RECEIPT_ALLOWED = NULL,
I.DAYS_LATE_RECEIPT_ALLOWED = NULL,
I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG = NULL,
I.DEFAULT_SHIPPING_ORG = NULL,
I.DEFECT_TRACKING_ON_FLAG = NULL,
I.DEMAND_TIME_FENCE_CODE = NULL,
I.DEMAND_TIME_FENCE_DAYS = NULL,
I.DIMENSION_UOM_CODE = NULL,
I.DOWNLOADABLE_FLAG = NULL,
I.EAM_ACT_NOTIFICATION_FLAG = NULL,
I.EAM_ACT_SHUTDOWN_STATUS = NULL,
I.EAM_ACTIVITY_CAUSE_CODE = NULL,
I.EAM_ACTIVITY_SOURCE_CODE = NULL,
I.EAM_ACTIVITY_TYPE_CODE = NULL,
I.EAM_ITEM_TYPE = NULL,
I.ELECTRONIC_FLAG = NULL,
I.END_ASSEMBLY_PEGGING_FLAG = NULL,
I.END_DATE_ACTIVE = NULL,
I.ENFORCE_SHIP_TO_LOCATION_CODE = NULL,
I.ENGINEERING_DATE = NULL,
I.ENGINEERING_ECN_CODE = NULL,
I.ENGINEERING_ITEM_ID = NULL,
I.EVENT_FLAG = NULL,
I.FINANCING_ALLOWED_FLAG = NULL,
I.FIXED_DAYS_SUPPLY = NULL,
I.FIXED_LEAD_TIME = NULL,
I.FIXED_LOT_MULTIPLIER = NULL,
I.FIXED_ORDER_QUANTITY = NULL,
I.FULL_LEAD_TIME = NULL,
I.HAZARD_CLASS_ID = NULL,
I.IB_ITEM_INSTANCE_CLASS = NULL,
I.INDIVISIBLE_FLAG = NULL,
I.INSPECTION_REQUIRED_FLAG = NULL,
I.INTERNAL_VOLUME = NULL,
I.INVENTORY_CARRY_PENALTY = NULL,
I.INVOICE_CLOSE_TOLERANCE = NULL,
I.LOT_SUBSTITUTION_ENABLED = NULL,
I.MATERIAL_BILLABLE_FLAG = NULL,
I.MAX_MINMAX_QUANTITY = NULL,
I.MAX_WARRANTY_AMOUNT = NULL,
I.MAXIMUM_LOAD_WEIGHT = NULL,
I.MAXIMUM_ORDER_QUANTITY = NULL,
I.MIN_MINMAX_QUANTITY = NULL,
I.MINIMUM_FILL_PERCENT = NULL,
I.MINIMUM_LICENSE_QUANTITY = NULL,
I.MINIMUM_ORDER_QUANTITY = NULL,
I.MODEL_CONFIG_CLAUSE_NAME = NULL,
I.MRP_CALCULATE_ATP_FLAG = NULL,
I.MRP_SAFETY_STOCK_PERCENT = NULL,
I.NEGATIVE_MEASUREMENT_ERROR = NULL,
I.OPERATION_SLACK_PENALTY = NULL,
I.ORDER_COST = NULL,
I.ORDERABLE_ON_WEB_FLAG = NULL,
I.OUTSIDE_OPERATION_UOM_TYPE = NULL,
I.OVER_RETURN_TOLERANCE = NULL,
I.OVER_SHIPMENT_TOLERANCE = NULL,
I.OVERCOMPLETION_TOLERANCE_TYPE = NULL,
I.OVERCOMPLETION_TOLERANCE_VALUE = NULL,
I.OVERRUN_PERCENTAGE = NULL,
I.PAYMENT_TERMS_ID = NULL,
I.PICKING_RULE_ID = NULL,
I.PLANNED_INV_POINT_FLAG = NULL,
I.PLANNER_CODE = NULL,
I.PLANNING_EXCEPTION_SET = NULL,
I.POSITIVE_MEASUREMENT_ERROR = NULL,
I.PREPROCESSING_LEAD_TIME = NULL,
I.PRIMARY_SPECIALIST_ID = NULL,
I.PRODUCT_FAMILY_ITEM_ID = NULL,
I.PURCHASING_TAX_CODE = NULL,
I.QTY_RCV_EXCEPTION_CODE = NULL,
I.QTY_RCV_TOLERANCE = NULL,
I.RECEIPT_DAYS_EXCEPTION_CODE = NULL,
I.RECEIVE_CLOSE_TOLERANCE = NULL,
I.RECEIVING_ROUTING_ID = NULL,
I.RECOVERED_PART_DISP_CODE = NULL,
I.RELEASE_TIME_FENCE_CODE = NULL,
I.RELEASE_TIME_FENCE_DAYS = NULL,
I.RESPONSE_TIME_PERIOD_CODE = NULL,
I.RESPONSE_TIME_VALUE = NULL,
I.ROUNDING_CONTROL_TYPE = NULL,
I.ROUNDING_FACTOR = NULL,
I.SAFETY_STOCK_BUCKET_DAYS = NULL,
-- I.SECONDARY_DEFAULT_IND = NULL, BUG 7255713
I.SECONDARY_SPECIALIST_ID = NULL,
-- I.SECONDARY_UOM_CODE = NULL, BUG 7255713
I.SERV_IMPORTANCE_LEVEL = NULL,
I.SERV_REQ_ENABLED_CODE = NULL,
I.SERVICE_DURATION_PERIOD_CODE = NULL,
I.SERVICE_ITEM_FLAG = NULL,
I.SERVICE_STARTING_DELAY = NULL,
I.SERVICEABLE_ITEM_CLASS_ID = NULL,
I.SHRINKAGE_RATE = NULL,
I.SOURCE_ORGANIZATION_ID = NULL,
I.SOURCE_SUBINVENTORY = NULL,
I.SOURCE_TYPE = NULL,
I.START_AUTO_LOT_NUMBER = NULL,
I.START_AUTO_SERIAL_NUMBER = NULL,
I.START_DATE_ACTIVE = NULL,
I.STD_LOT_SIZE = NULL,
I.SUBSCRIPTION_DEPEND_FLAG = NULL,
I.SUBSTITUTION_WINDOW_CODE = NULL,
I.SUBSTITUTION_WINDOW_DAYS = NULL,
I.TAX_CODE = NULL,
I.UN_NUMBER_ID = NULL,
I.UNDER_RETURN_TOLERANCE = NULL,
I.UNDER_SHIPMENT_TOLERANCE = NULL,
I.UNIT_HEIGHT = NULL,
I.UNIT_LENGTH = NULL,
I.UNIT_OF_ISSUE = NULL,
I.UNIT_VOLUME = NULL,
-- Unit Weight can be updated for Pending Items -R12 C
-- I.UNIT_WEIGHT = NULL,
I.UNIT_WIDTH = NULL,
I.USAGE_ITEM_FLAG = NULL,
I.VARIABLE_LEAD_TIME = NULL,
I.VENDOR_WARRANTY_FLAG = NULL,
I.VOL_DISCOUNT_EXEMPT_FLAG = NULL,
I.VOLUME_UOM_CODE = NULL,
I.WARRANTY_VENDOR_ID = NULL,
-- Weight UOM can be updated for Pending Items -R12 C
-- I.WEIGHT_UOM_CODE = NULL,
I.WH_UPDATE_DATE = NULL,
I.WIP_SUPPLY_LOCATOR_ID = NULL,
I.WIP_SUPPLY_SUBINVENTORY = NULL,
I.GLOBAL_ATTRIBUTE_CATEGORY = NULL,
I.GLOBAL_ATTRIBUTE1 = NULL,
I.GLOBAL_ATTRIBUTE2 = NULL,
I.GLOBAL_ATTRIBUTE3 = NULL,
I.GLOBAL_ATTRIBUTE4 = NULL,
I.GLOBAL_ATTRIBUTE5 = NULL,
I.GLOBAL_ATTRIBUTE6 = NULL,
I.GLOBAL_ATTRIBUTE7 = NULL,
I.GLOBAL_ATTRIBUTE8 = NULL,
I.GLOBAL_ATTRIBUTE9 = NULL,
I.GLOBAL_ATTRIBUTE10 = NULL,
I.ATTRIBUTE_CATEGORY = NULL,
I.ATTRIBUTE1 = NULL,
I.ATTRIBUTE2 = NULL,
I.ATTRIBUTE3 = NULL,
I.ATTRIBUTE4 = NULL,
I.ATTRIBUTE5 = NULL,
I.ATTRIBUTE6 = NULL,
I.ATTRIBUTE7 = NULL,
I.ATTRIBUTE8 = NULL,
I.ATTRIBUTE9 = NULL,
I.ATTRIBUTE10 = NULL,
I.ATTRIBUTE11 = NULL,
I.ATTRIBUTE12 = NULL,
I.ATTRIBUTE13 = NULL,
I.ATTRIBUTE14 = NULL,
I.ATTRIBUTE15 = NULL
WHERE I.ROWID = CUR.ROWID;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET SET_PROCESS_ID = SET_PROCESS_ID + 3000000000000
WHERE INVENTORY_ITEM_ID = CUR.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = CUR.ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
--SET PROCESS_FLAG = 4 BUG 7255713
SET PROCESS_FLAG = 44 -- added for bug 7255713-ccsingh
WHERE PROCESS_FLAG = 41
AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 4
WHERE PROCESS_FLAG = 45 --Changed the value from 41 to 45 as we need to validate primary attributes
AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
UPDATE mtl_system_items_interface
SET SET_PROCESS_ID = xset_id
WHERE ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
UPDATE mtl_item_revisions_interface
SET SET_PROCESS_ID = xset_id
WHERE ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
UPDATE mtl_item_revisions_interface i
SET i.process_flag = 5
WHERE i.process_flag = 2
AND i.set_process_id = xset_id
AND ((i.organization_id = org_id) or (all_org = 1))
AND i.transaction_type = 'CREATE'
AND i.revision <> (select m.starting_revision
from mtl_parameters m
where m.organization_id = i.organization_id);
SELECT msi.rowid
,msb.lifecycle_id
,msb.current_phase_id
,msb.inventory_item_status_code
,msb.item_catalog_group_id
,msb.eng_item_flag -- 5306178
,msb.style_item_flag
,msb.style_item_id
,msb.gdsn_outbound_enabled_flag
FROM mtl_system_items_interface msi,
mtl_system_items_b msb
WHERE msi.process_flag = 1
AND msi.set_process_id = xset_id
AND ((msi.organization_id = org_id) or (all_org = 1))
AND msi.transaction_type = 'UPDATE'
AND msi.organization_id = msb.organization_id
AND msi.inventory_item_id = msb.inventory_item_id;
SELECT msi.rowid
,msi.*
FROM mtl_system_items_interface msi
WHERE msi.process_flag = 1
AND msi.set_process_id = xset_id
AND ((msi.organization_id = org_id) or (all_org = 1))
AND msi.transaction_type = 'UPDATE';
SELECT NVL(add_all_to_change_flag,'N'), structure_type_id
FROM ego_import_option_sets
WHERE batch_id = xset_id;
SELECT msi.inventory_item_status_code, msi.eng_item_flag, msi.rowid
FROM mtl_system_items_interface msi
WHERE msi.process_flag = 1
AND msi.set_process_id = xset_id
AND ((msi.organization_id = org_id) or (all_org = 1))
AND msi.transaction_type = 'UPDATE';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
WHERE transaction_id IS NULL
AND set_process_id = xset_id;
UPDATE mtl_system_items_interface
SET lifecycle_id = DECODE(lifecycle_id,NULL,cur.lifecycle_id,-999999,NULL,lifecycle_id)
,current_phase_id = DECODE(current_phase_id,NULL,cur.current_phase_id,-999999,NULL,current_phase_id)
,item_catalog_group_id = DECODE(item_catalog_group_id,NULL,cur.item_catalog_group_id,-999999, NULL,item_catalog_group_id)
,inventory_item_status_code = NVL(inventory_item_status_code,cur.inventory_item_status_code)
,eng_item_flag = NVL(eng_item_flag,cur.eng_item_flag) -- 5306178
--Adding style item defaulting, since style/sku validations need to be performed before ICC change
--which happens in policy check
,style_item_flag = DECODE(style_item_flag,NULL,cur.style_item_flag,'!',NULL,chr(0),NULL,style_item_flag)
,style_item_id = DECODE(style_item_id,NULL, cur.style_item_id,-999999,NULL,9.99E125,NULL,style_item_id)
,gdsn_outbound_enabled_flag = DECODE(gdsn_outbound_enabled_flag,NULL,cur.gdsn_outbound_enabled_flag,'!',NULL,chr(0),NULL,gdsn_outbound_enabled_flag)
WHERE rowid = cur.rowid;
UPDATE mtl_system_items_interface
SET inventory_item_status_code = l_status_code
,eng_item_flag = l_eng_item_flag
WHERE rowid = l_rowid;
SELECT ATTR_GROUP_ID||':'||UPPER(ATTR_GROUP_NAME)
BULK COLLECT INTO Attribute_Grp_Table
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE ='EGO_MASTER_ITEMS';
SELECT * INTO l_msb_rec
FROM mtl_system_items
WHERE inventory_item_id = cur.inventory_item_id
AND organization_id = cur.organization_id;
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,'!'))
OR (NVL(cur.RFQ_REQUIRED_FLAG, NVL(l_msb_rec.RFQ_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.RFQ_REQUIRED_FLAG,'!'))
OR (NVL(cur.OUTSIDE_OPERATION_FLAG, NVL(l_msb_rec.OUTSIDE_OPERATION_FLAG,'!')) <> NVL(l_msb_rec.OUTSIDE_OPERATION_FLAG,'!'))
OR (NVL(cur.OUTSIDE_OPERATION_UOM_TYPE, NVL(l_msb_rec.OUTSIDE_OPERATION_UOM_TYPE,'!')) <> NVL(l_msb_rec.OUTSIDE_OPERATION_UOM_TYPE,'!'))
OR (NVL(cur.TAXABLE_FLAG, NVL(l_msb_rec.TAXABLE_FLAG,'!')) <> NVL(l_msb_rec.TAXABLE_FLAG,'!'))
OR (NVL(cur.TAX_CODE, NVL(l_msb_rec.TAX_CODE,'!')) <> NVL(l_msb_rec.TAX_CODE,'!'))
OR (NVL(cur.RECEIPT_REQUIRED_FLAG, NVL(l_msb_rec.RECEIPT_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.RECEIPT_REQUIRED_FLAG,'!'))
OR (NVL(cur.INSPECTION_REQUIRED_FLAG, NVL(l_msb_rec.INSPECTION_REQUIRED_FLAG,'!')) <> NVL(l_msb_rec.INSPECTION_REQUIRED_FLAG,'!'))
OR (NVL(cur.BUYER_ID, NVL(l_msb_rec.BUYER_ID,-999999)) <> NVL(l_msb_rec.BUYER_ID,-999999))
OR (NVL(cur.UNIT_OF_ISSUE, NVL(l_msb_rec.UNIT_OF_ISSUE,'!')) <> NVL(l_msb_rec.UNIT_OF_ISSUE,'!'))
OR (NVL(cur.RECEIVE_CLOSE_TOLERANCE, NVL(l_msb_rec.RECEIVE_CLOSE_TOLERANCE,-999999)) <> NVL(l_msb_rec.RECEIVE_CLOSE_TOLERANCE,-999999))
OR (NVL(cur.INVOICE_CLOSE_TOLERANCE, NVL(l_msb_rec.INVOICE_CLOSE_TOLERANCE,-999999)) <> NVL(l_msb_rec.INVOICE_CLOSE_TOLERANCE,-999999))
OR (NVL(cur.UN_NUMBER_ID, NVL(l_msb_rec.UN_NUMBER_ID,-999999)) <> NVL(l_msb_rec.UN_NUMBER_ID,-999999))
OR (NVL(cur.HAZARD_CLASS_ID, NVL(l_msb_rec.HAZARD_CLASS_ID,-999999)) <> NVL(l_msb_rec.HAZARD_CLASS_ID,-999999))
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))
OR (NVL(cur.MARKET_PRICE, NVL(l_msb_rec.MARKET_PRICE,-999999)) <> NVL(l_msb_rec.MARKET_PRICE,-999999))
OR (NVL(cur.PRICE_TOLERANCE_PERCENT, NVL(l_msb_rec.PRICE_TOLERANCE_PERCENT,-999999)) <> NVL(l_msb_rec.PRICE_TOLERANCE_PERCENT,-999999))
OR (NVL(cur.ROUNDING_FACTOR, NVL(l_msb_rec.ROUNDING_FACTOR,-999999)) <> NVL(l_msb_rec.ROUNDING_FACTOR,-999999))
OR (NVL(cur.ENCUMBRANCE_ACCOUNT, NVL(l_msb_rec.ENCUMBRANCE_ACCOUNT,-999999)) <> NVL(l_msb_rec.ENCUMBRANCE_ACCOUNT,-999999))
OR (NVL(cur.EXPENSE_ACCOUNT, NVL(l_msb_rec.EXPENSE_ACCOUNT,-999999)) <> NVL(l_msb_rec.EXPENSE_ACCOUNT,-999999))
OR (NVL(cur.ASSET_CATEGORY_ID, NVL(l_msb_rec.ASSET_CATEGORY_ID,-999999)) <> NVL(l_msb_rec.ASSET_CATEGORY_ID,-999999))
OR (NVL(cur.OUTSOURCED_ASSEMBLY, NVL(l_msb_rec.OUTSOURCED_ASSEMBLY,-999999)) <> NVL(l_msb_rec.OUTSOURCED_ASSEMBLY,-999999))
THEN
IF l_attr_grps IS NULL THEN
l_attr_grps := TO_CHAR(get_attribute_group_id('PURCHASING'));
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
SELECT ATTR_GROUP_DISP_NAME INTO l_attr_grp_name
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_ID = l_attr_group_id;
INSERT INTO mtl_system_items_interface(
organization_id
,inventory_item_id
,process_flag
,set_process_id
,transaction_type
,transaction_id
,inventory_item_status_code
,allowed_units_lookup_code
,item_type
,description)
VALUES(
cur.organization_id
,cur.inventory_item_id
,1
,cur.set_process_id
,'UPDATE'
,mtl_system_items_interface_s.nextval
,cur.inventory_item_status_code --Bug 5383744
,cur.allowed_units_lookup_code
,cur.item_type
,NVL(cur.description,l_msb_rec.description));
UPDATE mtl_system_items_interface
SET process_flag = 5,
--bug 5383744
inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
WHERE rowid = cur.rowid;
SELECT * INTO l_msb_rec
FROM mtl_system_items
WHERE inventory_item_id = cur.inventory_item_id
AND organization_id = cur.organization_id;
OR cur.ALLOW_ITEM_DESC_UPDATE_FLAG IS NOT NULL
OR cur.ASSET_CATEGORY_ID IS NOT NULL
OR cur.BUYER_ID IS NOT NULL
OR cur.ENCUMBRANCE_ACCOUNT IS NOT NULL
OR cur.EXPENSE_ACCOUNT IS NOT NULL
OR cur.HAZARD_CLASS_ID IS NOT NULL
OR cur.LIST_PRICE_PER_UNIT IS NOT NULL
OR cur.MARKET_PRICE IS NOT NULL
OR cur.MUST_USE_APPROVED_VENDOR_FLAG IS NOT NULL
OR cur.OUTSIDE_OPERATION_UOM_TYPE IS NOT NULL
OR cur.PRICE_TOLERANCE_PERCENT IS NOT NULL
OR cur.PURCHASING_ITEM_FLAG IS NOT NULL
OR cur.RFQ_REQUIRED_FLAG IS NOT NULL
OR cur.ROUNDING_FACTOR IS NOT NULL
OR cur.TAXABLE_FLAG IS NOT NULL
OR cur.UNIT_OF_ISSUE IS NOT NULL
OR cur.UN_NUMBER_ID IS NOT NULL
OR cur.INSPECTION_REQUIRED_FLAG IS NOT NULL
OR cur.INVOICE_CLOSE_TOLERANCE IS NOT NULL
OR cur.RECEIPT_REQUIRED_FLAG IS NOT NULL
OR cur.RECEIVE_CLOSE_TOLERANCE IS NOT NULL
OR cur.OUTSIDE_OPERATION_FLAG IS NOT NULL
OR cur.PURCHASING_TAX_CODE IS NOT NULL
OR cur.OUTSOURCED_ASSEMBLY IS NOT NULL
OR cur.PURCHASING_ENABLED_FLAG IS NOT NULL
--Receiving Attribute Group
OR cur.ALLOW_EXPRESS_DELIVERY_FLAG IS NOT NULL
OR cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG IS NOT NULL
OR cur.ALLOW_UNORDERED_RECEIPTS_FLAG IS NOT NULL
OR cur.DAYS_EARLY_RECEIPT_ALLOWED IS NOT NULL
OR cur.ENFORCE_SHIP_TO_LOCATION_CODE IS NOT NULL
OR cur.QTY_RCV_TOLERANCE IS NOT NULL
OR cur.RECEIPT_DAYS_EXCEPTION_CODE IS NOT NULL
OR cur.RECEIVING_ROUTING_ID IS NOT NULL
OR cur.DAYS_LATE_RECEIPT_ALLOWED IS NOT NULL
OR cur.QTY_RCV_EXCEPTION_CODE IS NOT NULL
--Process Manufacturing Attribute Group
OR cur.RECIPE_ENABLED_FLAG IS NOT NULL
OR cur.CAS_NUMBER IS NOT NULL
OR cur.HAZARDOUS_MATERIAL_FLAG IS NOT NULL
OR cur.PROCESS_YIELD_LOCATOR_ID IS NOT NULL
OR cur.PROCESS_QUALITY_ENABLED_FLAG IS NOT NULL
OR cur.PROCESS_SUPPLY_LOCATOR_ID IS NOT NULL
OR cur.PROCESS_EXECUTION_ENABLED_FLAG IS NOT NULL
OR cur.PROCESS_COSTING_ENABLED_FLAG IS NOT NULL
OR cur.PROCESS_SUPPLY_SUBINVENTORY IS NOT NULL
OR cur.PROCESS_YIELD_SUBINVENTORY IS NOT NULL
--Physical Attributes
OR cur.UNIT_WEIGHT IS NOT NULL
OR cur.VOLUME_UOM_CODE IS NOT NULL
OR cur.WEIGHT_UOM_CODE IS NOT NULL
OR cur.COLLATERAL_FLAG IS NOT NULL
OR cur.VEHICLE_ITEM_FLAG IS NOT NULL
OR cur.MAXIMUM_LOAD_WEIGHT IS NOT NULL
OR cur.MINIMUM_FILL_PERCENT IS NOT NULL
OR cur.UNIT_VOLUME IS NOT NULL
OR cur.CONTAINER_ITEM_FLAG IS NOT NULL
OR cur.INTERNAL_VOLUME IS NOT NULL
OR cur.CONTAINER_TYPE_CODE IS NOT NULL
OR cur.EQUIPMENT_TYPE IS NOT NULL
OR cur.EVENT_FLAG IS NOT NULL
OR cur.ELECTRONIC_FLAG IS NOT NULL
OR cur.DOWNLOADABLE_FLAG IS NOT NULL
OR cur.INDIVISIBLE_FLAG IS NOT NULL
OR cur.DIMENSION_UOM_CODE IS NOT NULL
OR cur.UNIT_LENGTH IS NOT NULL
OR cur.UNIT_WIDTH IS NOT NULL
OR cur.UNIT_HEIGHT IS NOT NULL
--General Planning
OR cur.FIXED_DAYS_SUPPLY IS NOT NULL
OR cur.FIXED_LOT_MULTIPLIER IS NOT NULL
OR cur.FIXED_ORDER_QUANTITY IS NOT NULL
OR cur.INVENTORY_PLANNING_CODE IS NOT NULL
OR cur.MAXIMUM_ORDER_QUANTITY IS NOT NULL
OR cur.MAX_MINMAX_QUANTITY IS NOT NULL
OR cur.MINIMUM_ORDER_QUANTITY IS NOT NULL
OR cur.MIN_MINMAX_QUANTITY IS NOT NULL
OR cur.MRP_SAFETY_STOCK_PERCENT IS NOT NULL
OR cur.ORDER_COST IS NOT NULL
OR cur.PLANNER_CODE IS NOT NULL
OR cur.SAFETY_STOCK_BUCKET_DAYS IS NOT NULL
OR cur.SOURCE_SUBINVENTORY IS NOT NULL
OR cur.SOURCE_TYPE IS NOT NULL
OR cur.CARRYING_COST IS NOT NULL
OR cur.MRP_SAFETY_STOCK_CODE IS NOT NULL
OR cur.SOURCE_ORGANIZATION_ID IS NOT NULL
OR cur.PLANNING_MAKE_BUY_CODE IS NOT NULL
OR cur.VMI_MINIMUM_UNITS IS NOT NULL
OR cur.VMI_MINIMUM_DAYS IS NOT NULL
OR cur.VMI_MAXIMUM_UNITS IS NOT NULL
OR cur.VMI_MAXIMUM_DAYS IS NOT NULL
OR cur.VMI_FIXED_ORDER_QUANTITY IS NOT NULL
OR cur.SO_AUTHORIZATION_FLAG IS NOT NULL
OR cur.CONSIGNED_FLAG IS NOT NULL
OR cur.VMI_FORECAST_TYPE IS NOT NULL
OR cur.FORECAST_HORIZON IS NOT NULL
OR cur.ASN_AUTOEXPIRE_FLAG IS NOT NULL
OR cur.SUBCONTRACTING_COMPONENT IS NOT NULL
--MPS/MRP Planning
OR cur.ACCEPTABLE_EARLY_DAYS IS NOT NULL
OR cur.ACCEPTABLE_RATE_DECREASE IS NOT NULL
OR cur.ACCEPTABLE_RATE_INCREASE IS NOT NULL
OR cur.AUTO_REDUCE_MPS IS NOT NULL
OR cur.DEMAND_TIME_FENCE_CODE IS NOT NULL
OR cur.DEMAND_TIME_FENCE_DAYS IS NOT NULL
OR cur.MRP_CALCULATE_ATP_FLAG IS NOT NULL
OR cur.MRP_PLANNING_CODE IS NOT NULL
OR cur.OVERRUN_PERCENTAGE IS NOT NULL
OR cur.PLANNING_EXCEPTION_SET IS NOT NULL
OR cur.PLANNING_TIME_FENCE_CODE IS NOT NULL
OR cur.PLANNING_TIME_FENCE_DAYS IS NOT NULL
OR cur.REPETITIVE_PLANNING_FLAG IS NOT NULL
OR cur.ROUNDING_CONTROL_TYPE IS NOT NULL
OR cur.SHRINKAGE_RATE IS NOT NULL
OR cur.ATO_FORECAST_CONTROL IS NOT NULL
OR cur.END_ASSEMBLY_PEGGING_FLAG IS NOT NULL
OR cur.RELEASE_TIME_FENCE_CODE IS NOT NULL
OR cur.RELEASE_TIME_FENCE_DAYS IS NOT NULL
OR cur.PLANNED_INV_POINT_FLAG IS NOT NULL
OR cur.CREATE_SUPPLY_FLAG IS NOT NULL
OR cur.SUBSTITUTION_WINDOW_CODE IS NOT NULL
OR cur.SUBSTITUTION_WINDOW_DAYS IS NOT NULL
OR cur.CRITICAL_COMPONENT_FLAG IS NOT NULL
OR cur.EXCLUDE_FROM_BUDGET_FLAG IS NOT NULL
OR cur.DRP_PLANNED_FLAG IS NOT NULL
OR cur.DAYS_MAX_INV_SUPPLY IS NOT NULL
OR cur.DAYS_MAX_INV_WINDOW IS NOT NULL
OR cur.DAYS_TGT_INV_SUPPLY IS NOT NULL
OR cur.DAYS_TGT_INV_WINDOW IS NOT NULL
OR cur.CONTINOUS_TRANSFER IS NOT NULL
OR cur.CONVERGENCE IS NOT NULL
OR cur.DIVERGENCE IS NOT NULL
OR cur.REPAIR_PROGRAM IS NOT NULL
OR cur.REPAIR_LEADTIME IS NOT NULL
OR cur.REPAIR_YIELD IS NOT NULL
OR cur.PREPOSITION_POINT IS NOT NULL
--Lead Times
OR cur.CUM_MANUFACTURING_LEAD_TIME IS NOT NULL
OR cur.FIXED_LEAD_TIME IS NOT NULL
OR cur.VARIABLE_LEAD_TIME IS NOT NULL
OR cur.FULL_LEAD_TIME IS NOT NULL
OR cur.POSTPROCESSING_LEAD_TIME IS NOT NULL
OR cur.PREPROCESSING_LEAD_TIME IS NOT NULL
OR cur.LEAD_TIME_LOT_SIZE IS NOT NULL
OR cur.CUMULATIVE_TOTAL_LEAD_TIME IS NOT NULL
--Work In Progress
OR cur.WIP_SUPPLY_TYPE IS NOT NULL
OR cur.OVERCOMPLETION_TOLERANCE_TYPE IS NOT NULL
OR cur.INVENTORY_CARRY_PENALTY IS NOT NULL
OR cur.OPERATION_SLACK_PENALTY IS NOT NULL
OR cur.OVERCOMPLETION_TOLERANCE_VALUE IS NOT NULL
OR cur.WIP_SUPPLY_SUBINVENTORY IS NOT NULL
OR cur.WIP_SUPPLY_LOCATOR_ID IS NOT NULL
OR cur.BUILD_IN_WIP_FLAG IS NOT NULL
--Order Management
OR cur.ATP_COMPONENTS_FLAG IS NOT NULL
OR cur.ATP_FLAG IS NOT NULL
OR cur.ATP_RULE_ID IS NOT NULL
OR cur.CUSTOMER_ORDER_ENABLED_FLAG IS NOT NULL
OR cur.CUSTOMER_ORDER_FLAG IS NOT NULL
OR cur.DEFAULT_SHIPPING_ORG IS NOT NULL
OR cur.INTERNAL_ORDER_ENABLED_FLAG IS NOT NULL
OR cur.INTERNAL_ORDER_FLAG IS NOT NULL
OR cur.PICKING_RULE_ID IS NOT NULL
OR cur.PICK_COMPONENTS_FLAG IS NOT NULL
OR cur.RETURNABLE_FLAG IS NOT NULL
OR cur.RETURN_INSPECTION_REQUIREMENT IS NOT NULL
OR cur.SHIPPABLE_ITEM_FLAG IS NOT NULL
OR cur.SHIP_MODEL_COMPLETE_FLAG IS NOT NULL
OR cur.SO_TRANSACTIONS_FLAG IS NOT NULL
OR cur.REPLENISH_TO_ORDER_FLAG IS NOT NULL
OR cur.OVER_SHIPMENT_TOLERANCE IS NOT NULL
OR cur.UNDER_SHIPMENT_TOLERANCE IS NOT NULL
OR cur.OVER_RETURN_TOLERANCE IS NOT NULL
OR cur.UNDER_RETURN_TOLERANCE IS NOT NULL
OR cur.VOL_DISCOUNT_EXEMPT_FLAG IS NOT NULL
OR cur.COUPON_EXEMPT_FLAG IS NOT NULL
OR cur.FINANCING_ALLOWED_FLAG IS NOT NULL
OR cur.DEFAULT_SO_SOURCE_TYPE IS NOT NULL
OR cur.CHARGE_PERIODICITY_CODE IS NOT NULL
--Service Attributes
OR cur.COVERAGE_SCHEDULE_ID IS NOT NULL
OR cur.MATERIAL_BILLABLE_FLAG IS NOT NULL
OR cur.MAX_WARRANTY_AMOUNT IS NOT NULL
OR cur.PREVENTIVE_MAINTENANCE_FLAG IS NOT NULL
OR cur.PRORATE_SERVICE_FLAG IS NOT NULL
OR cur.RESPONSE_TIME_PERIOD_CODE IS NOT NULL
OR cur.RESPONSE_TIME_VALUE IS NOT NULL
OR cur.SERVICE_DURATION IS NOT NULL
OR cur.SERVICE_DURATION_PERIOD_CODE IS NOT NULL
OR cur.SERVICE_ITEM_FLAG IS NOT NULL
OR cur.WARRANTY_VENDOR_ID IS NOT NULL
OR cur.SERVICEABLE_PRODUCT_FLAG IS NOT NULL
OR cur.VENDOR_WARRANTY_FLAG IS NOT NULL
OR cur.SERVICE_STARTING_DELAY IS NOT NULL
OR cur.USAGE_ITEM_FLAG IS NOT NULL
OR cur.RECOVERED_PART_DISP_CODE IS NOT NULL
OR cur.DEFECT_TRACKING_ON_FLAG IS NOT NULL
OR cur.COMMS_NL_TRACKABLE_FLAG IS NOT NULL
OR cur.ASSET_CREATION_CODE IS NOT NULL
OR cur.COMMS_ACTIVATION_REQD_FLAG IS NOT NULL
OR cur.CONTRACT_ITEM_TYPE_CODE IS NOT NULL
OR cur.SERV_REQ_ENABLED_CODE IS NOT NULL
OR cur.SERV_BILLING_ENABLED_FLAG IS NOT NULL
OR cur.IB_ITEM_INSTANCE_CLASS IS NOT NULL
--Web Option
OR cur.ORDERABLE_ON_WEB_FLAG IS NOT NULL
OR cur.BACK_ORDERABLE_FLAG IS NOT NULL
OR cur.WEB_STATUS IS NOT NULL
OR cur.MINIMUM_LICENSE_QUANTITY IS NOT NULL
THEN
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVNIRIS.change_policy_check: Some Attr is non null');
UPDATE mtl_system_items_interface msi
SET process_flag = 5,
--Bug 5383744
inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
WHERE rowid = cur.rowid;
INSERT INTO mtl_system_items_interface(
organization_id
,inventory_item_id
,process_flag
,set_process_id
,transaction_type
,transaction_id
,inventory_item_status_code
,allowed_units_lookup_code
,item_type
,description)
VALUES(
cur.organization_id
,cur.inventory_item_id
,1
,cur.set_process_id
,'UPDATE'
,mtl_system_items_interface_s.nextval
,cur.inventory_item_status_code --Bug 5383744
,cur.allowed_units_lookup_code
,cur.item_type
,NVL(cur.description,l_msb_rec.description));
UPDATE mtl_system_items_interface msi
SET process_flag = 7
WHERE rowid = cur.rowid;