--主组织存在但库存组织不存在
declare
cursor c1 is
select distinct MSI.* from item_temp a,apps.mtl_system_items msi
where a.total_item=msi.segment1
and organization_id=2
and NOT exists (select 1 from mtl_system_items where organization_id=419 and segment1=a.total_item)
;
v1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
begin
insert into mtl_system_items_interface (
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
SUMMARY_FLAG ,
ENABLED_FLAG ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
DESCRIPTION ,
BUYER_ID ,
ACCOUNTING_RULE_ID ,
INVOICING_RULE_ID ,
SEGMENT1 ,
PURCHASING_ITEM_FLAG ,
SHIPPABLE_ITEM_FLAG ,
CUSTOMER_ORDER_FLAG ,
INTERNAL_ORDER_FLAG ,
SERVICE_ITEM_FLAG ,
INVENTORY_ITEM_FLAG ,
ENG_ITEM_FLAG ,
INVENTORY_ASSET_FLAG ,
PURCHASING_ENABLED_FLAG ,
CUSTOMER_ORDER_ENABLED_FLAG ,
INTERNAL_ORDER_ENABLED_FLAG ,
SO_TRANSACTIONS_FLAG ,
MTL_TRANSACTIONS_ENABLED_FLAG ,
STOCK_ENABLED_FLAG ,
BOM_ENABLED_FLAG ,
BUILD_IN_WIP_FLAG ,
REVISION_QTY_CONTROL_CODE ,
ITEM_CATALOG_GROUP_ID ,
CATALOG_STATUS_FLAG ,
RETURNABLE_FLAG ,
DEFAULT_SHIPPING_ORG ,
COLLATERAL_FLAG ,
TAXABLE_FLAG ,
QTY_RCV_EXCEPTION_CODE ,
ALLOW_ITEM_DESC_UPDATE_FLAG ,
INSPECTION_REQUIRED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
MARKET_PRICE ,
HAZARD_CLASS_ID ,
RFQ_REQUIRED_FLAG ,
QTY_RCV_TOLERANCE ,
LIST_PRICE_PER_UNIT ,
UN_NUMBER_ID ,
PRICE_TOLERANCE_PERCENT ,
ASSET_CATEGORY_ID ,
ROUNDING_FACTOR ,
UNIT_OF_ISSUE ,
ENFORCE_SHIP_TO_LOCATION_CODE ,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
ALLOW_UNORDERED_RECEIPTS_FLAG ,
ALLOW_EXPRESS_DELIVERY_FLAG ,
DAYS_EARLY_RECEIPT_ALLOWED ,
DAYS_LATE_RECEIPT_ALLOWED ,
RECEIPT_DAYS_EXCEPTION_CODE ,
RECEIVING_ROUTING_ID ,
INVOICE_CLOSE_TOLERANCE ,
RECEIVE_CLOSE_TOLERANCE ,
AUTO_LOT_ALPHA_PREFIX ,
START_AUTO_LOT_NUMBER ,
LOT_CONTROL_CODE ,
SHELF_LIFE_CODE ,
SHELF_LIFE_DAYS ,
SERIAL_NUMBER_CONTROL_CODE ,
START_AUTO_SERIAL_NUMBER ,
AUTO_SERIAL_ALPHA_PREFIX ,
SOURCE_TYPE ,
SOURCE_ORGANIZATION_ID ,
SOURCE_SUBINVENTORY ,
EXPENSE_ACCOUNT ,
ENCUMBRANCE_ACCOUNT ,
RESTRICT_SUBINVENTORIES_CODE ,
UNIT_WEIGHT ,
WEIGHT_UOM_CODE ,
VOLUME_UOM_CODE ,
UNIT_VOLUME ,
RESTRICT_LOCATORS_CODE ,
LOCATION_CONTROL_CODE ,
SHRINKAGE_RATE ,
ACCEPTABLE_EARLY_DAYS ,
PLANNING_TIME_FENCE_CODE ,
DEMAND_TIME_FENCE_CODE ,
LEAD_TIME_LOT_SIZE ,
STD_LOT_SIZE ,
CUM_MANUFACTURING_LEAD_TIME ,
OVERRUN_PERCENTAGE ,
MRP_CALCULATE_ATP_FLAG ,
ACCEPTABLE_RATE_INCREASE ,
ACCEPTABLE_RATE_DECREASE ,
CUMULATIVE_TOTAL_LEAD_TIME ,
PLANNING_TIME_FENCE_DAYS ,
DEMAND_TIME_FENCE_DAYS ,
END_ASSEMBLY_PEGGING_FLAG ,
REPETITIVE_PLANNING_FLAG ,
PLANNING_EXCEPTION_SET ,
BOM_ITEM_TYPE ,
PICK_COMPONENTS_FLAG ,
REPLENISH_TO_ORDER_FLAG ,
BASE_ITEM_ID ,
ATP_COMPONENTS_FLAG ,
ATP_FLAG ,
FIXED_LEAD_TIME ,
VARIABLE_LEAD_TIME ,
WIP_SUPPLY_LOCATOR_ID ,
WIP_SUPPLY_TYPE ,
WIP_SUPPLY_SUBINVENTORY ,
PRIMARY_UOM_CODE ,
PRIMARY_UNIT_OF_MEASURE ,
ALLOWED_UNITS_LOOKUP_CODE ,
COST_OF_SALES_ACCOUNT ,
SALES_ACCOUNT ,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG ,
INVENTORY_ITEM_STATUS_CODE ,
INVENTORY_PLANNING_CODE ,
PLANNER_CODE ,
PLANNING_MAKE_BUY_CODE ,
FIXED_LOT_MULTIPLIER ,
ROUNDING_CONTROL_TYPE ,
CARRYING_COST ,
POSTPROCESSING_LEAD_TIME ,
PREPROCESSING_LEAD_TIME ,
FULL_LEAD_TIME ,
ORDER_COST ,
MRP_SAFETY_STOCK_PERCENT ,
MRP_SAFETY_STOCK_CODE ,
MIN_MINMAX_QUANTITY ,
MAX_MINMAX_QUANTITY ,
MINIMUM_ORDER_QUANTITY ,
FIXED_ORDER_QUANTITY ,
FIXED_DAYS_SUPPLY ,
MAXIMUM_ORDER_QUANTITY ,
ATP_RULE_ID ,
PICKING_RULE_ID ,
RESERVABLE_TYPE ,
POSITIVE_MEASUREMENT_ERROR ,
NEGATIVE_MEASUREMENT_ERROR ,
ENGINEERING_ECN_CODE ,
ENGINEERING_ITEM_ID ,
ENGINEERING_DATE ,
SERVICE_STARTING_DELAY ,
VENDOR_WARRANTY_FLAG ,
SERVICEABLE_COMPONENT_FLAG ,
SERVICEABLE_PRODUCT_FLAG ,
BASE_WARRANTY_SERVICE_ID ,
PAYMENT_TERMS_ID ,
PREVENTIVE_MAINTENANCE_FLAG ,
PRIMARY_SPECIALIST_ID ,
SECONDARY_SPECIALIST_ID ,
SERVICEABLE_ITEM_CLASS_ID ,
TIME_BILLABLE_FLAG ,
MATERIAL_BILLABLE_FLAG ,
EXPENSE_BILLABLE_FLAG ,
PRORATE_SERVICE_FLAG ,
COVERAGE_SCHEDULE_ID ,
SERVICE_DURATION_PERIOD_CODE ,
SERVICE_DURATION ,
WARRANTY_VENDOR_ID ,
MAX_WARRANTY_AMOUNT ,
RESPONSE_TIME_PERIOD_CODE ,
RESPONSE_TIME_VALUE ,
NEW_REVISION_CODE ,
INVOICEABLE_ITEM_FLAG ,
TAX_CODE ,
INVOICE_ENABLED_FLAG ,
MUST_USE_APPROVED_VENDOR_FLAG ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
OUTSIDE_OPERATION_FLAG ,
OUTSIDE_OPERATION_UOM_TYPE ,
SAFETY_STOCK_BUCKET_DAYS ,
AUTO_REDUCE_MPS ,
COSTING_ENABLED_FLAG ,
CYCLE_COUNT_ENABLED_FLAG ,
-- DEMAND_SOURCE_LINE ,
-- COPY_ITEM_ID ,
-- SET_ID ,
REVISION ,
AUTO_CREATED_CONFIG_FLAG ,
ITEM_TYPE ,
MODEL_CONFIG_CLAUSE_NAME ,
SHIP_MODEL_COMPLETE_FLAG ,
MRP_PLANNING_CODE ,
RETURN_INSPECTION_REQUIREMENT ,
-- DEMAND_SOURCE_TYPE ,
-- DEMAND_SOURCE_HEADER_ID ,
-- TRANSACTION_ID ,
PROCESS_FLAG ,
-- ORGANIZATION_CODE ,
ITEM_NUMBER ,
-- COPY_ITEM_NUMBER ,
-- TEMPLATE_ID ,
-- TEMPLATE_NAME ,
-- COPY_ORGANIZATION_ID ,
-- COPY_ORGANIZATION_CODE ,
ATO_FORECAST_CONTROL ,
TRANSACTION_TYPE ,
MATERIAL_COST ,
-- MATERIAL_SUB_ELEM ,
-- MATERIAL_OH_RATE ,
-- MATERIAL_OH_SUB_ELEM ,
-- MATERIAL_SUB_ELEM_ID ,
-- MATERIAL_OH_SUB_ELEM_ID ,
-- AUTO_REL_TIME_FENCE_CODE ,
-- AUTO_REL_TIME_FENCE_DAYS ,
CONTAINER_ITEM_FLAG ,
VEHICLE_ITEM_FLAG ,
MAXIMUM_LOAD_WEIGHT ,
MINIMUM_FILL_PERCENT ,
CONTAINER_TYPE_CODE ,
INTERNAL_VOLUME ,
SET_PROCESS_ID ,
PURCHASING_TAX_CODE )
values (
NULL,--INVENTORY_ITEM_ID ,
419,--ORGANIZATION_ID ,
sysdate,--LAST_UPDATE_DATE ,
1231,--LAST_UPDATED_BY ,
sysdate,--CREATION_DATE ,
1231,--CREATED_BY ,
v1.LAST_UPDATE_LOGIN ,
v1.SUMMARY_FLAG ,
v1.ENABLED_FLAG ,
v1.START_DATE_ACTIVE ,
v1.END_DATE_ACTIVE ,
v1.DESCRIPTION ,
v1.BUYER_ID ,
v1.ACCOUNTING_RULE_ID ,
v1.INVOICING_RULE_ID ,
v1.SEGMENT1 ,
v1.PURCHASING_ITEM_FLAG ,
v1.SHIPPABLE_ITEM_FLAG ,
v1.CUSTOMER_ORDER_FLAG ,
v1.INTERNAL_ORDER_FLAG ,
v1.SERVICE_ITEM_FLAG ,
v1.INVENTORY_ITEM_FLAG ,
v1.ENG_ITEM_FLAG ,
v1.INVENTORY_ASSET_FLAG ,
v1.PURCHASING_ENABLED_FLAG ,
v1.CUSTOMER_ORDER_ENABLED_FLAG ,
v1.INTERNAL_ORDER_ENABLED_FLAG ,
v1.SO_TRANSACTIONS_FLAG ,
v1.MTL_TRANSACTIONS_ENABLED_FLAG ,
v1.STOCK_ENABLED_FLAG ,
v1.BOM_ENABLED_FLAG ,
v1.BUILD_IN_WIP_FLAG ,
v1.REVISION_QTY_CONTROL_CODE ,
v1.ITEM_CATALOG_GROUP_ID ,
v1.CATALOG_STATUS_FLAG ,
v1.RETURNABLE_FLAG ,
v1.DEFAULT_SHIPPING_ORG ,
v1.COLLATERAL_FLAG ,
v1.TAXABLE_FLAG ,
v1.QTY_RCV_EXCEPTION_CODE ,
v1.ALLOW_ITEM_DESC_UPDATE_FLAG ,
v1.INSPECTION_REQUIRED_FLAG ,
v1.RECEIPT_REQUIRED_FLAG ,
v1.MARKET_PRICE ,
v1.HAZARD_CLASS_ID ,
v1.RFQ_REQUIRED_FLAG ,
v1.QTY_RCV_TOLERANCE ,
v1.LIST_PRICE_PER_UNIT ,
v1.UN_NUMBER_ID ,
v1.PRICE_TOLERANCE_PERCENT ,
v1.ASSET_CATEGORY_ID ,
v1.ROUNDING_FACTOR ,
v1.UNIT_OF_ISSUE ,
v1.ENFORCE_SHIP_TO_LOCATION_CODE ,
v1.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
v1.ALLOW_UNORDERED_RECEIPTS_FLAG ,
v1.ALLOW_EXPRESS_DELIVERY_FLAG ,
v1.DAYS_EARLY_RECEIPT_ALLOWED ,
v1.DAYS_LATE_RECEIPT_ALLOWED ,
v1.RECEIPT_DAYS_EXCEPTION_CODE ,
v1.RECEIVING_ROUTING_ID ,
v1.INVOICE_CLOSE_TOLERANCE ,
v1.RECEIVE_CLOSE_TOLERANCE ,
v1.AUTO_LOT_ALPHA_PREFIX ,
v1.START_AUTO_LOT_NUMBER ,
v1.LOT_CONTROL_CODE ,
v1.SHELF_LIFE_CODE ,
v1.SHELF_LIFE_DAYS ,
v1.SERIAL_NUMBER_CONTROL_CODE ,
v1.START_AUTO_SERIAL_NUMBER ,
v1.AUTO_SERIAL_ALPHA_PREFIX ,
v1.SOURCE_TYPE ,
v1.SOURCE_ORGANIZATION_ID ,
v1.SOURCE_SUBINVENTORY ,
v1.EXPENSE_ACCOUNT ,
v1.ENCUMBRANCE_ACCOUNT ,
v1.RESTRICT_SUBINVENTORIES_CODE ,
v1.UNIT_WEIGHT ,
v1.WEIGHT_UOM_CODE ,
v1.VOLUME_UOM_CODE ,
v1.UNIT_VOLUME ,
v1.RESTRICT_LOCATORS_CODE ,
v1.LOCATION_CONTROL_CODE ,
v1.SHRINKAGE_RATE ,
v1.ACCEPTABLE_EARLY_DAYS ,
v1.PLANNING_TIME_FENCE_CODE ,
v1.DEMAND_TIME_FENCE_CODE ,
v1.LEAD_TIME_LOT_SIZE ,
v1.STD_LOT_SIZE ,
v1.CUM_MANUFACTURING_LEAD_TIME ,
v1.OVERRUN_PERCENTAGE ,
v1.MRP_CALCULATE_ATP_FLAG ,
v1.ACCEPTABLE_RATE_INCREASE ,
v1.ACCEPTABLE_RATE_DECREASE ,
v1.CUMULATIVE_TOTAL_LEAD_TIME ,
v1.PLANNING_TIME_FENCE_DAYS ,
v1.DEMAND_TIME_FENCE_DAYS ,
v1.END_ASSEMBLY_PEGGING_FLAG ,
v1.REPETITIVE_PLANNING_FLAG ,
v1.PLANNING_EXCEPTION_SET ,
v1.BOM_ITEM_TYPE ,
v1.PICK_COMPONENTS_FLAG ,
v1.REPLENISH_TO_ORDER_FLAG ,
v1.BASE_ITEM_ID ,
v1.ATP_COMPONENTS_FLAG ,
v1.ATP_FLAG ,
v1.FIXED_LEAD_TIME ,
v1.VARIABLE_LEAD_TIME ,
NULL,--V1.WIP_SUPPLY_LOCATOR_ID ,
NULL,--v1.WIP_SUPPLY_TYPE ,
NULL,--v1.WIP_SUPPLY_SUBINVENTORY ,
v1.PRIMARY_UOM_CODE ,
v1.PRIMARY_UNIT_OF_MEASURE ,
v1.ALLOWED_UNITS_LOOKUP_CODE ,
v1.COST_OF_SALES_ACCOUNT ,
v1.SALES_ACCOUNT ,
v1.DEFAULT_INCLUDE_IN_ROLLUP_FLAG ,
v1.INVENTORY_ITEM_STATUS_CODE ,
v1.INVENTORY_PLANNING_CODE ,
'NBPMC' ,--v1.PLANNER_CODE ,
v1.PLANNING_MAKE_BUY_CODE ,
v1.FIXED_LOT_MULTIPLIER ,
v1.ROUNDING_CONTROL_TYPE ,
v1.CARRYING_COST ,
v1.POSTPROCESSING_LEAD_TIME ,
v1.PREPROCESSING_LEAD_TIME ,
v1.FULL_LEAD_TIME ,
v1.ORDER_COST ,
v1.MRP_SAFETY_STOCK_PERCENT ,
v1.MRP_SAFETY_STOCK_CODE ,
v1.MIN_MINMAX_QUANTITY ,
v1.MAX_MINMAX_QUANTITY ,
v1.MINIMUM_ORDER_QUANTITY ,
v1.FIXED_ORDER_QUANTITY ,
v1.FIXED_DAYS_SUPPLY ,
v1.MAXIMUM_ORDER_QUANTITY ,
v1.ATP_RULE_ID ,
v1.PICKING_RULE_ID ,
v1.RESERVABLE_TYPE ,
v1.POSITIVE_MEASUREMENT_ERROR ,
v1.NEGATIVE_MEASUREMENT_ERROR ,
v1.ENGINEERING_ECN_CODE ,
v1.ENGINEERING_ITEM_ID ,
v1.ENGINEERING_DATE ,
v1.SERVICE_STARTING_DELAY ,
v1.VENDOR_WARRANTY_FLAG ,
v1.SERVICEABLE_COMPONENT_FLAG ,
v1.SERVICEABLE_PRODUCT_FLAG ,
v1.BASE_WARRANTY_SERVICE_ID ,
v1.PAYMENT_TERMS_ID ,
v1.PREVENTIVE_MAINTENANCE_FLAG ,
v1.PRIMARY_SPECIALIST_ID ,
v1.SECONDARY_SPECIALIST_ID ,
v1.SERVICEABLE_ITEM_CLASS_ID ,
v1.TIME_BILLABLE_FLAG ,
v1.MATERIAL_BILLABLE_FLAG ,
v1.EXPENSE_BILLABLE_FLAG ,
v1.PRORATE_SERVICE_FLAG ,
v1.COVERAGE_SCHEDULE_ID ,
v1.SERVICE_DURATION_PERIOD_CODE ,
v1.SERVICE_DURATION ,
v1.WARRANTY_VENDOR_ID ,
v1.MAX_WARRANTY_AMOUNT ,
v1.RESPONSE_TIME_PERIOD_CODE ,
v1.RESPONSE_TIME_VALUE ,
v1.NEW_REVISION_CODE ,
v1.INVOICEABLE_ITEM_FLAG ,
v1.TAX_CODE ,
v1.INVOICE_ENABLED_FLAG ,
v1.MUST_USE_APPROVED_VENDOR_FLAG ,
v1.REQUEST_ID ,
v1.PROGRAM_APPLICATION_ID ,
v1.PROGRAM_ID ,
v1.PROGRAM_UPDATE_DATE ,
v1.OUTSIDE_OPERATION_FLAG ,
v1.OUTSIDE_OPERATION_UOM_TYPE ,
v1.SAFETY_STOCK_BUCKET_DAYS ,
v1.AUTO_REDUCE_MPS ,
v1.COSTING_ENABLED_FLAG ,
v1.CYCLE_COUNT_ENABLED_FLAG ,
-- v1.DEMAND_SOURCE_LINE ,
-- v1.COPY_ITEM_ID ,
-- v1.SET_ID ,
1,--v1.REVISION_QTY_CONTROL_CODE ,
v1.AUTO_CREATED_CONFIG_FLAG ,
v1.ITEM_TYPE ,
v1.MODEL_CONFIG_CLAUSE_NAME ,
v1.SHIP_MODEL_COMPLETE_FLAG ,
v1.MRP_PLANNING_CODE ,
v1.RETURN_INSPECTION_REQUIREMENT ,
-- v1.DEMAND_SOURCE_TYPE ,
-- v1.DEMAND_SOURCE_HEADER_ID ,
-- v1.TRANSACTION_ID ,
1,--PROCESS_FLAG ,
-- v1.ORGANIZATION_CODE ,
null,--ITEM_NUMBER ,
-- COPY_ITEM_NUMBER ,
-- v1.TEMPLATE_ID ,
-- v1.TEMPLATE_NAME ,
-- COPY_ORGANIZATION_ID ,
-- COPY_ORGANIZATION_CODE ,
v1.ATO_FORECAST_CONTROL ,
'CREATE',--TRANSACTION_TYPE ,
0,-- v1.MATERIAL_COST ,
-- v1.MATERIAL_SUB_ELEM ,
-- v1.MATERIAL_OH_RATE ,
-- v1.MATERIAL_OH_SUB_ELEM ,
-- v1.MATERIAL_SUB_ELEM_ID ,
-- v1.MATERIAL_OH_SUB_ELEM_ID ,
-- v1.AUTO_REL_TIME_FENCE_CODE ,
-- v1.AUTO_REL_TIME_FENCE_DAYS ,
v1.CONTAINER_ITEM_FLAG ,
v1.VEHICLE_ITEM_FLAG ,
v1.MAXIMUM_LOAD_WEIGHT ,
v1.MINIMUM_FILL_PERCENT ,
v1.CONTAINER_TYPE_CODE ,
v1.INTERNAL_VOLUME ,
0,--SET_PROCESS_ID ,
v1.PURCHASING_TAX_CODE );
EXCEPTION
WHEN OTHERS THEN
NULL;
END ;
END LOOP;
COMMIT;
CLOSE C1;
END;
---导入物料到主组织中
declare
cursor c1 is
select a.* from item_temp a
where not exists (select 1 from mtl_system_items where organization_id =2 and segment1=a.total_item)
;
v1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
begin
insert into
mtl_system_items_interface (organization_id, process_flag , transaction_type, segment1, description)
values(2, 1, 'CREATE', v1.total_item, v1.description) ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END ;
END LOOP;
COMMIT;
CLOSE C1;
END;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-507971/，如需转载，请注明出处，否则将追究法律责任。
