Method: The method of importing the data into the BOM list is to import the data into the interface table and allow it to automatically run. When you upload a file, pay attention to the use of ASCII characters. 1, a transfer table establishing themselves drop table cux_bill_temp; create table cux_bill_temp (bill_sequence_id number, assembly_item_id number, organization_id number, assembly_item varchar2 (50), --BOM component_sequence_id number, component_quantity number, - the number of components item_num number, - project sequence operation_seq_num number, - a step sequence component_item_id number, component_item varchar2 (50), - assembly PLANNING_FACTOR number, - Program% d100 component_yield_factor number, - yield d1 wip_supply_type number, - supply type supply_type varchar2 (50), supply_subinventory VARCHAR2 (50), - Supply Subssence Optional Number, - Optional optional_disp varcha2 (10), - Optional MUTUALLY_EXCLUSIVE_OPTIONS NUALLE_EXCLUSIVE_O_DISP VARCHAR2 (10), - Mutual Combined Attribute1 VARCHAR2 (50), - Rank Row_num Number); 2. Delete the data delete cux_bill_temp in the transit table, put the data to be imported in the file that is * .csv, and must correspond to the transfer The field of the table, the file name in this example is Bill.csv. The additional script file is Bill.ctl, its contents are as follows: Options (Skip = 1) // Skip the first line, general first behavior Its field Description Load DataInfile Bill.csv //bill.csv is a data file Append
INTO TABLE CUX_BILL_TEMPFIELDS TERMINATED BY "," Optionally Enclosed By '"' (list of fields corresponding to transit table) Log in to the Oracle database server, use commands: (SQLLOAD username / password @ database name) Load file Bill.csv Data into the transit table. 4. View the number of records in the transit table (comparison after entering the data) Select count (*) from cux_bill_temp; 5, remove the space character of the key field in Table Bill.csv when imported, so as not to affect the import update cux_bill_temp set aSSEMBLY_ITEM = replace (aSSEMBLY_ITEM, '', ''), COMPONENT_ITEM = replace (COMPONENT_ITEM, '', '');. 6, to see if there are duplicate options (only if repeated Item) select assembly_item , component_item, min (row_num), count (*) from cux_bill_temp group by assembly_item, component_item having count (*)> 1; if there are duplicate Item, will have to delete (or re-merge) delete cux_bill_tempwhere row_num in (select min ( Row_num) from cux_bill_temp group by assembly_item, Component_Item Having Count (*)> 1); The following steps are selected (if there is any repetition, do not do 7-10) 7, then re-establish a temporary table (for repetition Data, only one data is taken, and one of Row_NUM is the smallest one) Drop Table Cux_bill_a;
create table cux_bill_aasselect assembly_item, component_item, component_quantity, PLANNING_FACTOR, component_yield_factor, supply_type, supply_subinventory, OPTIONAL_disp, MUTUALLY_EXCLUSIVE_O_disp, attribute1, min (row_num) row_numfrom cux_bill_tempgroup by assembly_item, component_item, component_quantity, PLANNING_FACTOR, component_yield_factor, supply_type, supply_subinventory, OPTIONAL_disp, MUTUALLY_EXCLUSIVE_O_disp, attribute1; 8, delete cux_bill_temp table delete cux_bill_temp; 9, and then re cux_bill_a table data imported to cux_bill_temp table, completed the duplicate data culling feature insert into cux_bill_temp (assembly_item, component_item, component_quantity, PLANNING_FACTOR, component_yield_factor, supply_type, supply_subinventory, OPTIONAL_disp, MUTUALLY_EXCLUSIVE_O_disp , attribute1, row_num) select assembly_item, component_item, component_quantity, PLANNING_FACTOR, component_yield_factor, supply_type, supply_subinventory, OPTIONAL_disp, MUTUALLY_EXCLUSIVE_O_disp, attribute1, row_numfrom cux_bill_a; 10, delete tables cux_bill_a drop table cux_bill_a; 11, then check A table, check for duplicate data select assembly_item, component_item, min (row_num), count (*) from cux_bill_tempgroup by assembly_item, component_itemhaving count (*)> 1; 12, viewed in mtl_system_items table, both in the inventory table, there no exist Item.select distinct itemfrom (select distinct assembly_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = b.assembly_item and organization_id = 2) unionselect distinct component_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = B.Component_Item and Organization_ID = 2)) ORDER BY ITEM; 13, if there is an item Item in MTL_System_Items, delete it (or import these items Item into the system) Delete:
delete cux_bill_temp b where not exists (select null from mtl_system_items where segment1 = b.component_item and organization_id = 2); delete cux_bill_temp a where not exists (select null from mtl_system_items where segment1 = a.assembly_item and organization_id = 2); 14, for no Item processed article, the temporary table cux_item_temp it into another (in future reference table and introduced mtl_system_items) delete cux_item_temp; insert into cux_item_temp (segment1, description) select distinct item, itemfrom (select distinct assembly_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = b.assembly_item and organization_id = 2) unionselect distinct component_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = b.component_item and organization_id = 2)); BOM will find no data to ITEM into another table to prepare for the next ITEM after introducing the guide BOMcreate table cux_bom_temp1select distinct itemfrom (select distinct assembly_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = b.asse mbly_item and organization_id = 2) unionselect distinct component_item itemfrom cux_bill_temp bwhere not exists (select null from mtl_system_items where segment1 = b.component_item and organization_id = 2)) ------------------- -------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15, from the coded ID table mtl_system_items added staging table cux_bill_temp article table (item from the main tissue) update cux_bill_temp b set assembly_item_id = (select inventory_item_id from mtl_system_items where segment1 = b.assembly_item and organization_id = 2), component_item_id = (select inventory_item_id from mtl_system_items where segment1 = b.component_item and organization_id = 2); 16, check whether there exists a coding article ID (ID neither article is introduced into the temporary table cux_bill_temp) select row_num from cux_bill_temp where assembly_item_id is null or component_item_id is NULL; 17, processing Update Cux_bill_temp set optional = 1 where Upper (optional_disp) Like 'Y%'; Update Cux_bill_temp set Optional = 2 Where Optional Is NULL;
Update cux_bill_temp set mutually_exclusive_options = 1 where Upper (mutually_exclusive_o_disp) Like 'Y%';
update cux_bill_temp set MUTUALLY_EXCLUSIVE_OPTIONS = 2 where MUTUALLY_EXCLUSIVE_O_DISP is null; 18, data processing in view cux_bill_temp whether leakage select count (*) from cux_bill_temp where OPTIONAL is null or MUTUALLY_EXCLUSIVE_OPTIONS is null or assembly_item_id is null or component_item_id is null; 19, update WIP_SUPPLY_TYPE within; update cux_bill_temp set WIP_SUPPLY_TYPE = 6 where component_item like 'B%'; 20, delete the table package (cux_bill_temp in), which corresponds to table bom_bill_of_materials (both in the table already exists some options package, not need to import header, the packet content can simply import) delete cux_bill_temp twhere exists (select null from bom_bill_of_materials where assembly_item_id = t.assembly_item_id and organization_id = 2); 21, a packet has been written using the write data (write only interface table bom_bill_of_mtls_interface) exec cux_bom_temp.insert_bill_15 (1); select count (*) from cux_bill_temp tempwhere exits (select null from bom_inventory_components b where temp.bill_sequence_id = b.bill_sequence_id and temp.component_item_id = b.component_item_id); delete cux_bill_temp tempwhere exists (select null from bom _INVENTORY_COMPONENTS B WHERE B.BILL_SEQUENCE_ID = Temp.bill_sequence_id and b.component_item_id = temp.component_item_id);
EXEC CUX_BOM_TEMP.INSERT_BILL_10 (1); 22, the case written in the interface table in the interface table select count (*) from bom_bill_of_mtls_interface; 23, then update exec Cux_bom_temp.insert_bill_15 (1);
Select count (*) from cux_bill_temp where bill_sequence_id is null; exec Cux_bom_temp.insert_bill_20 (1); go to the request
Select count (*) from bom_inventory_comps_interface; (After the introduction is successful) Sort the components EXEC CUX_BOM_TEMP.UPDATE_BILL_ITEM_NUM4;
