--修改物料的属性
物料表名：t_icitembase和t_icitemcore，
物料属性字段：ferpclsid
--查询符合条件的物料的信息
select a.fitemid,a.fnumber,a.fname,b.funitid,b.funitgroupid,b.fsecunitid,b.forderunitid,b.fsaleunitid,b.fstoreunitid,b.fproductunitid from t_item a,t_icitembase b where a.fnumber like '1.02.08001%' and a.fitemid=b.fitemid
--将符合条件的物料的计量单位组和辅助单位调整为PCS-KG和KG
--update b set b.funitid=1084,b.funitgroupid=1083,b.fsecunitid=1085,b.forderunitid=1084,b.fsaleunitid=1084,b.fstoreunitid=1084,b.fproductunitid=1084 from t_item a,t_icitembase b where a.fnumber like '1.02.08001%' and a.fitemid=b.fitemid
--查找并将对应的物料编码禁用
select fnumber,fdeleted,* from t_icitem where fnumber in('1.01.21005.10025-001','1.01.21005.10025-002','1.01.21005.10025-003','1.01.21005.10025-004','1.01.21005.10025-005','1.01.21005.35050-001','1.01.21005.35050-011','1.01.21005.35050-012','1.01.21005.35050-013')
update t_icitem set fdeleted='1' where fnumber in('1.01.21005.10025-001','1.01.21005.10025-002','1.01.21005.10025-003','1.01.21005.10025-004','1.01.21005.10025-005','1.01.21005.35050-001','1.01.21005.35050-011','1.01.21005.35050-012','1.01.21005.35050-013')
--将外购物料对应的存货客户修改为1403-1057 1406-1060
select * from t_account where fnumber like '1403%' or fnumber like '5051.01%' or fnumber like '5402.01%' or fnumber like '1406%'
select facctid,ferpclsid,fnumber,fname,fmodel from t_icitem where ferpclsid=1 and facctid=1060
update t_icitem set facctid=1057 where ferpclsid=1 and facctid=1060
--查找并将对应的盘盈亏单进行调整
select a.fbillno,c.fnumber,b.fqty,* from icstockbill a,icstockbillentry b,t_icitem c
where a.fbillno like 'KADJ000072' and a.finterid=b.finterid and b.fitemid=c.fitemid
select * from icstockbillentry a, t_icitem b
where a.fitemid=b.fitemid and b.fnumber='6.06.99004.00000-210' and finterid=18172 and a.fqtymust=127
update a set a.fqty=0,a.fauxqty=0,a.fqtyactual=127,a.fauxqtyactual=127 from icstockbillentry a, t_icitem b
where a.fitemid=b.fitemid and b.fnumber='6.06.99004.00000-210' and finterid=18172 and a.fqtymust=127
--查找并将对应物料的最低最高库存修改
select flowlimit,fhighlimit,* from t_icitem where fnumber like '8%' and fhighlimit=1000
update t_icitem set flowlimit=0,fhighlimit=0 where fnumber=''
--将8开头的产品对应的客户BOM和BOM内对应的项目配置属性修改为“可选”
update iccustbomchild set fmarshaltype=386 where finterid in(select finterid from icbom where fitemid in(select fitemid from t_icitem where fnumber like '8.08.06%' and ferpclsid=7)) AND fmarshaltype<>386
--k3 由于找不到这台计算机的安全许可证,连接被中断
2012-11-01 16:17:06| 分类： 默认分类 |字号 订阅
开始-运行---输入“regedit”---确定后进入注册表编辑器-----找到“MSLicensing”-直接删
除掉，
这个值的查找路径是: HKEY_LOCAL_MACHINE---SOFTWARE----Microsoft-----MSLicensing删除掉后，客户端在重新登陆即可
将 MSLICENSING 项整个删除即可,删除了会连子项一起删除,属于正常现象
--查找未审核的出入库单据
select fbillno,fstatus,fdate,fcheckdate,fscbillno,fseltrantype from icstockbill where fstatus=0
--查找并将对应的物料的盘盈单做后台修改
select * from icstockbill a,icstockbillentry b where a.fbillno='YADJ000033' and a.finterid=b.finterid and b.fitemid=15586 --in(select fitemid from t_icitem where fnumber in('1.05.08001.10000-001'))
--select fitemid,fnumber from t_icitem where fnumber in('1.05.08001.10000-001','1.01.25005.25118-001')
update b set b.fqty=10,b.fauxqty=10,b.famount=650,b.fqtyactual=226,b.fauxqtyactual=226 from icstockbill a,icstockbillentry b where a.fbillno='YADJ000033' and a.finterid=b.finterid and b.fitemid=15586
--将对应的物料的单位修改为20KG\桶
update b set b.funitid=293,b.funitgroupid=292,b.forderunitid=294,b.fsaleunitid=293,b.fstoreunitid=293,b.fproductunitid=293
from t_item a,t_icitembase b
where a.fnumber in('1.05.02001.50000-002') and a.fitemid=b.fitemid
--查找并将对应BOM内的计划百分比为50调整为100
SELECT fpercent,* FROM Icbomchild where finterid in(select finterid from icbom where fitemid in(select fitemid from t_icitem where fnumber like '6%' or fnumber like '8%' )) and fpercent=50
--update Iccustbomchild set fpercent=100 where finterid in(select finterid from icbom where fitemid in(select fitemid from t_icitem where fnumber like '6%' or fnumber like '8%' )) and fpercent=50
--将物料基本单位均修改为桶
update b set b.funitid=287,b.funitgroupid=286,b.forderunitid=287,b.fsaleunitid=287,b.fstoreunitid=287,b.fproductunitid=287
from t_item a,t_icitembase b
where a.fnumber in('1.10.02003.00000-012','1.10.02003.00071-001','1.10.02003.00611-001','1.10.02003.00911-001') and a.fitemid=b.fitemid
--查找并将对应的委外订单表体的单价进行修改
select a.finterid,a.fbillno,* from icsubcontract a,icsubcontractentry b where a.finterid=b.finterid and a.fbillno='WW028' and b.fitemid=6305
update b set b.fauxprice=6.9,b.fauxpricediscount=6.9,fauxtaxprice=6.9,b.fallamount=3180.9,b.famount=3180.9,b.fstdallamount=3180.9 from icsubcontract a,icsubcontractentry b where a.finterid=b.finterid and a.fbillno='WW028' and b.fitemid=6305
--查找并将没有出货的销售订单的交货日期和建议交货日期进行修改
select fhavemrp,fdate,fadviceconsigndate,fauxstockqty,fstockqty,fcommitqty,* from seorderentry where fhavemrp=0 and fstockqty=0
update seorderentry set fdate='2013-10-23 00:00:00.000',fadviceconsigndate='2013-10-25 00:00:00.000' where fhavemrp=0 and fstockqty=0
--查找并将对应的客户BOM禁用
select fforbid,* from icbom where fbomnumber in('custbom000168','custbom000169','custbom000572')
update
icbom set fforbid=1 where fbomnumber in('custbom000168','custbom000169','custbom000572')
--将物料的物料属性修改为外购
select * from t_icitembase where fitemid>=25952 and fitemid<=25958
--update t_icitembase set ferpclsid=1 where fitemid>=25982 and fitemid<=25998
--最低最高库存各品牌预警
select distinct b.fnumber,b.fname,b.fmodel,a.fqty,a.fbatchno,c.low,c.hight,a.fqty-c.low as 库存与最低库存差异, a.fqty-c.hight as 库存与最高库存差异
from icinventory a,t_icitem b,a11222 c
where a.fitemid=b.fitemid and b.fnumber like '8.08.01%' and a.fqty>=0 and a.fbatchno=c.pinpai
and b.fnumber=c.fnumber and (a.fbatchno like '%劳%' or a.fbatchno like '%士%' or a.fbatchno like '%顿%' or a.fbatchno like '%金%' or a.fbatchno like '%玛%' or a.fbatchno like '%仕%' or a.fbatchno like '%上%' or a.fbatchno like '%海%' or a.fbatchno like '%通%' or a.fbatchno like '%用%' )
--查找并将对应的BOM删除
select * from icbom where fbomnumber in('BOM000642')
delete from icbom where fbomnumber in('BOM000642')
select * from icbomchild where finterid=1230
delete from icbomchild where finterid=1230
--将指定的BOM或客户BOM修改为未审核未使用
update icbom set fstatus=0,fusestatus=1073 where fbomnumber like 'custbom000239'
--将物料属性修改为自制
update b set b.ferpclsid=2 from t_icitem a,t_icitembase b
where a.fitemid=b.fitemid
and a.fnumber in('1.01.24003.10025-0018','1.01.24003.10025-003')
--将物料属性修改为委外加工
update b set b.ferpclsid=3 from t_icitem a,t_icitembase b
where a.fitemid=b.fitemid
and a.fnumber in('6.06.01001.00081-110')
--删除BOM表的禁用的物料
select a.fitemid,b.fnumber,b.fname,b.fdeleted from icbomchild a ,t_icitem b where a.fitemid=b.fitemid and b.fdeleted<>0 --and a.fitemid=26122
--DELETE FROM ICBOMChild
WHERE FItemID IN (SELECT FItemID FROM t_ICItem WHERE FDeleted<>0)
--批量更改BOM内默认仓库
select a.fstockid,b.fitemid,b.fbomnumber,* from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.03%' ) and a.fitemid in(select fitemid from t_icitem where fnumber like '1.03.01%' or fnumber like '1.03.09%')
select a.fstockid,b.fitemid,b.fbomnumber,* from icbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.01%') and a.fitemid in(select fitemid from t_icitem where fnumber like '1.03.99%')
update a set a.fstockid=777 from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.01%') and a.fitemid in(select fitemid from t_icitem where fnumber like '1.03.99%')
update a set a.fstockid=759 from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.0200%') and fstockid=776
select a.f
stockid,b.fitemid,b.fbomnumber,* from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.01%') and a.fstockid=766
update a set a.fstockid=759 from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.04%' or fnumber like '8.08.05%') and a.fstockid=776
--查找并将对应的客户BOM的默认仓库进行批量修改
select * from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fbomnumber in('custbom000020','custbom000021','custbom000218','custbom000219','custbom001489',
'custbom000846','custbom001183','custbom001586','custbom001587','custbom000224','custbom000225','custbom001870','custbom001871','custbom001102',
'custbom002171','custbom000714','custbom001106','custbom000185','custbom000843','custbom001091','custbom001095','custbom001493','custbom001616',
'custbom000717','custbom001794','custbom002078','custbom002079','custbom000195','custbom001108','custbom001110','custbom001112','custbom001545',
'custbom001872','custbom001874','custbom001546','custbom001554','custbom001555','custbom001556','custbom001557','custbom000202','custbom000203',
'custbom001993','custbom000787','custbom001558','custbom001559','custbom001116','custbom001120','custbom001122','custbom000526','custbom001570',
'custbom001541','custbom002028','custbom001880','custbom001397','custbom001398','custbom000262','custbom000263','custbom001399','custbom001401',
'custbom002166','custbom000069','custbom001269','custbom001270','custbom000250','custbom000251','custbom001395','custbom001396','custbom000864',
'custbom002140','custbom002142','custbom000884','custbom001339','custbom001603','custbom000716','custbom001130','custbom001882','custbom001884',
'custbom000965','custbom001205','custbom001812','custbom001813','custbom001808','custbom001809','custbom001810','custbom001811','custbom001787',
'custbom001208','custbom001294','custbom001424','custbom001438','custbom001797','custbom000972','custbom001594','custbom002057','custbom000915',
'custbom001861','custbom001531','custbom001506','custbom001873','custbom001875','custbom000044','custbom000045','custbom000046','custbom000047',
'custbom000904','custbom001022','custbom001866','custbom001868','custbom000049','custbom000834','custbom000920','custbom002162','custbom000212',
'custbom001581','custbom001582','custbom000416','custbom000577','custbom000478','custbom000576','custbom001535','custbom001220','custbom001554',
'custbom001228','custbom001179','custbom001181','custbom001065','custbom001123','custbom001127','custbom001142','custbom001333','custbom001555',
'custbom000680','custbom001596','custbom000969','custbom001817','custbom001819','custbom001820','custbom001047','custbom001776','custbom001556',
'custbom001778','custbom000967','custbom001777','custbom000782','custbom001134','custbom000716','custbom001130','custbom000864','custbom001557',
'custbom000884'
,'custbom001339','custbom000251','custbom001395','custbom001396','custbom001269','custbom001270','custbom000262','custbom000197',
'custbom000263','custbom001399','custbom001603','custbom000250','custbom001401','custbom001397','custbom001398','custbom001570','custbom000198',
'custbom001880','custbom001116','custbom001120','custbom001122','custbom000202','custbom000203','custbom001558','custbom001559','custbom001550',
'custbom000195','custbom001108','custbom001110','custbom001112','custbom001545','custbom001546','custbom000185','custbom000843','custbom001551',
'custbom001091','custbom001095','custbom001493','custbom001616','custbom002078','custbom002079','custbom001102','custbom001106','custbom001587',
'custbom000224','custbom000225','custbom001870','custbom001871','custbom000218','custbom000219','custbom001489','custbom001586','custbom000020')
and a.fstockid not in(774,775,776,777,783) order by a.fstockid
--update a set a.fstockid=776 from iccustbomchild a,icbom b
--where a.finterid=b.finterid and b.fbomnumber in('custbom000150','custbom001157','custbom000138','custbom000139','custbom000931','custbom000934','custbom001168','custbom001169','custbom001171','custbom001172','custbom000390','custbom000692','custbom000728','custbom002067','custbom001381','custbom001382','custbom001638','bom000129') and a.fstockid not in(774,775,776,777,783) and a.fstockid=0
update a set a.fstockid=777 from iccustbomchild a,icbom b
where a.finterid=b.finterid and b.fbomnumber in('custbom000020','custbom000021','custbom000218','custbom000219','custbom001489',
'custbom000846','custbom001183','custbom001586','custbom001587','custbom000224','custbom000225','custbom001870','custbom001871','custbom001102',
'custbom002171','custbom000714','custbom001106','custbom000185','custbom000843','custbom001091','custbom001095','custbom001493','custbom001616',
'custbom000717','custbom001794','custbom002078','custbom002079','custbom000195','custbom001108','custbom001110','custbom001112','custbom001545',
'custbom001872','custbom001874','custbom001546','custbom001554','custbom001555','custbom001556','custbom001557','custbom000202','custbom000203',
'custbom001993','custbom000787','custbom001558','custbom001559','custbom001116','custbom001120','custbom001122','custbom000526','custbom001570',
'custbom001541','custbom002028','custbom001880','custbom001397','custbom001398','custbom000262','custbom000263','custbom001399','custbom001401',
'custbom002166','custbom000069','custbom001269','custbom001270','custbom000250','custbom000251','custbom001395','custbom001396','custbom000864',
'custbom002140','custbom002142','custbom000884','custbom001339','custbom001603','custbom000716','custbom001130','custbom001882','custbom001884',
'custbom000965','custbom001205','custbom001812','custbom001813','custbom001808','custbom001809','custbom001810','custbom001811','custbom001787',
'custbom001208','custbom001294','custbom001424','custbom001438','custbom001797','custbom000972','cu
stbom001594','custbom002057','custbom000915',
'custbom001861','custbom001531','custbom001506','custbom001873','custbom001875','custbom000044','custbom000045','custbom000046','custbom000047',
'custbom000904','custbom001022','custbom001866','custbom001868','custbom000049','custbom000834','custbom000920','custbom002162','custbom000212',
'custbom001581','custbom001582','custbom000416','custbom000577','custbom000478','custbom000576','custbom001535','custbom001220','custbom001554',
'custbom001228','custbom001179','custbom001181','custbom001065','custbom001123','custbom001127','custbom001142','custbom001333','custbom001555',
'custbom000680','custbom001596','custbom000969','custbom001817','custbom001819','custbom001820','custbom001047','custbom001776','custbom001556',
'custbom001778','custbom000967','custbom001777','custbom000782','custbom001134','custbom000716','custbom001130','custbom000864','custbom001557',
'custbom000884','custbom001339','custbom000251','custbom001395','custbom001396','custbom001269','custbom001270','custbom000262','custbom000197',
'custbom000263','custbom001399','custbom001603','custbom000250','custbom001401','custbom001397','custbom001398','custbom001570','custbom000198',
'custbom001880','custbom001116','custbom001120','custbom001122','custbom000202','custbom000203','custbom001558','custbom001559','custbom001550',
'custbom000195','custbom001108','custbom001110','custbom001112','custbom001545','custbom001546','custbom000185','custbom000843','custbom001551',
'custbom001091','custbom001095','custbom001493','custbom001616','custbom002078','custbom002079','custbom001102','custbom001106','custbom001587',
'custbom000224','custbom000225','custbom001870','custbom001871','custbom000218','custbom000219','custbom001489','custbom001586','custbom000020')
and a.fstockid not in(774,775,776,777,783) and a.fstockid=766
--查找并将对应的销售订单的表体的交期修改
select b.fbillno,c.fnumber,* from seorderentry a ,seorder b,t_icitem c where a.finterid=b.finterid and a.fitemid=c.fitemid and b.fbillno='seord001028'
update a set a.fdate='2013-12-10 00:00:00.000',a.fadviceconsigndate='2013-12-10 00:00:00.000' from seorderentry a ,seorder b,t_icitem c where a.finterid=b.finterid and a.fitemid=c.fitemid and b.fbillno='seord001028'
--将物料的单位修改为套
update b set b.funitid=255,b.funitgroupid=254,b.forderunitid=255,b.fsaleunitid=255,b.fstoreunitid=255,b.fproductunitid=255
from t_item a,t_icitembase b
where a.fnumber in('1.01.21005.XP000-002','1.01.21005.XP000-006','1.01.21005.XP100-001','1.01.21005.XP100-002','1.01.21005.XP140-001','1.01.21005.XP140-002','1.01.21005.XP180-001','1.01.21005.XP180-002
','1.01.21005.XT100-001','1.01.21005.XT350-001','1.01.21005.XT350-002') and a.fitemid=b.fitemid
--将物料单位修改为瓶
update b set b.funitid=267,b.funitgroupid=266,b.forderunitid=267,b.fsaleunitid=267,b.fstoreunitid=267,b.fproductunitid=267
from t_item a,t_icitembase b
where a.f
number in('1.10.06000.00000-090','1.10.06000.00000-091','1.10.06000.00006-001','1.10.06000.00023-001','1.10.06000.00102-001','1.10.06000.00141-001','1.10.06000.00142-001') and a.fitemid=b.fitemid
--将物料的单位修改本
update t_icitembase set funitid=21048,funitgroupid=21047,forderunitid=21048,fsaleunitid=21048,fstoreunitid=21048,fproductunitid=21048 where fitemid=20951
select * from t_icitem where fnumber in('1.10.06000.00000-110')
--将物料的单位改成把
update t_icitembase set funitid=269,funitgroupid=268,forderunitid=269,fsaleunitid=269,fstoreunitid=269,fproductunitid=269 where fitemid=20912
select * from t_icitem where fnumber in('1.05.02001.00008-026')
--批量更改BOM内默认仓库
select a.fstockid,b.fitemid,b.fbomnumber,* from iccustbomchild a,icbom b where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.01%') and fstockid=759
update a set a.fstockid=759 from iccustbomchild a,icbom b where a.finterid=b.finterid and b.fitemid in(select fitemid from t_icitem where fnumber like '8.08.0200%') and fstockid=776
--将物料的单位改成张
update t_icitembase set funitid=251,funitgroupid=250,forderunitid=251,fsaleunitid=251,fstoreunitid=251,fproductunitid=251 where fitemid=20913
select * from t_icitem where fnumber in('1.10.06000.00000-0024')
--统计系统内有多少物料有多个处于使用状态的BOM
select fnumber,fname,fmodel from t_icitem where fitemid in(select fitemid from icbom where fusestatus=1072
and fbomnumber not like '%custbom%' group by fitemid having count(fitemid)>1) order by fnumber
--批量修改BOM\客户bom内的默认仓库为物料基本资料的默认仓库
update icbomchild set icbomchild.FStockID=t_icitem.FDefaultLoc
from icbomchild inner join t_icitem ON icbomchild.fitemid=t_icitem.fitemid
update iccustbomchild set iccustbomchild.FStockID=t_icitem.FDefaultLoc
from iccustbomchild inner join t_icitem ON iccustbomchild.fitemid=t_icitem.fitemid
--将BOM中物料的工位批量修改为“安装”
update icbomchild set fmachinepos='安装' from icbom a,icbomchild b,t_icitem c where a.finterid=b.finterid and a.fitemid=c.fitemid and c.fnumber like '6.06.99004.%' and a.fbomnumber='BOM000751'
--将客户BOM中物料的工位批量修改为“安装”
update iccustbomchild set fmachinepos='安装' from icbom a,iccustbomchild b,t_icitem c where a.finterid=b.finterid and a.fitemid=c.fitemid and c.fnumber like '6.06.99004.%' and a.fbomnumber='custbom000671'
--将任务单中被引用的客户BOM删除，从而禁用对应的客户BOM
select * from icbom where fbomnumber='custbom000865'
select fbominterid,fitemid,* from icmo WHERE Fbominterid=4182
update icmo set Fbominterid=4787 WHERE Fbominterid=4182
select * from t_icitem where fnumber='6.06.99004.0250L-002'
--查找并将收料通知单内对应的物料的关联入库数量清零
select b.fbillno,a.fauxcon
commitqty,a.fconcommitqty,* from poinstockentry a ,poinstock b where a.finterid=b.finterid and b.fbillno='DD000410'
select * from poinstock
update a set a.fauxconcommitqty=0,a.fconcommitqty=0 from poinstockentry a ,poinstock b where a.finterid=b.finterid and b.fbillno='DD000410'
--查询并修改采购订单表体的单价和金额
select a.finterid,a.fbillno,* from poorder a,poorderentry b where a.finterid=b.finterid and a.fbillno='POORD000522' and b.fitemid=8099
update b set b.fprice=0.054455446,b.fauxprice=0.054455446,b.fauxpricediscount=0.054455446,b.fpricediscount=0.054455446,fauxtaxprice=0.054455446,ftaxprice=0.054455446,b.fallamount=653.46,b.famount=653.46 from poorder a,poorderentry b where a.finterid=b.finterid and a.fbillno='POORD000522' and b.fitemid=8099
--将BOM中物料的工位批量修改为“手插”
update b set b.fmachinepos='手插' from icbom a,icbomchild b,t_icitem c where a.finterid=b.finterid and b.fitemid=c.fitemid and c.fnumber like '1.01.28001.%'
--将客户BOM中物料的工位批量修改为“手插”
update iccustbomchild set fmachinepos='手插' from icbom a,iccustbomchild b,t_icitem c where a.finterid=b.finterid and b.fitemid=c.fitemid and c.fnumber like '1.01.28001.%'
--删除BOM禁用的物料
DELETE FROM ICBOMChild
WHERE FItemID IN (SELECT FItemID FROM t_ICItem WHERE FDeleted<>0)
--将物料的单位都修改为KG
update b set b.funitid=289,b.funitgroupid=288,b.fsecunitid=289,b.forderunitid=289,b.fsaleunitid=289,b.fstoreunitid=289,b.fproductunitid=289 from t_item a,t_icitembase b where a.fnumber='1.04.03001.000QS-001' and a.fitemid=b.fitemid
--将物料单位修改为PCS
update b set b.funitid=249,b.funitgroupid=248,b.forderunitid=249,b.fsaleunitid=249,b.fstoreunitid=249,b.fproductunitid=249
from t_item a,t_icitembase b
where a.fnumber='1.01.25004.20250-180' and a.fitemid=b.fitemid
--将物料单位修改为条
update b set b.funitid=277,b.funitgroupid=276,b.forderunitid=277,b.fsaleunitid=277,b.fstoreunitid=277,b.fproductunitid=277
from t_item a,t_icitembase b
where a.fnumber='1.01.25004.07510-001' and a.fitemid=b.fitemid
--将物料单位修改为台
update b set b.funitid=279,b.funitgroupid=278,b.forderunitid=279,b.fsaleunitid=279,b.fstoreunitid=279,b.fproductunitid=279
from t_item a,t_icitembase b
where a.fnumber='1.10.06000.00800-600' and a.fitemid=b.fitemid
--将对应的物料的单位修改为PCS-KG，有辅助单位KG
update b set b.funitid=308,b.funitgroupid=307,b.fsecunitid=309,b.forderunitid=308,b.fsaleunitid=308,b.fstoreunitid=308,b.fproductunitid=308
from t_item a,t_icitembase b
where a.fnumber='1.02.16001.00030-001' and a.fitemid=b.fitemid
--将物料单位修改为米
update b set b.funitid=281,b.funitgroupid=280,b.forderunitid=281,b.fsaleunitid=281,b.fstoreunitid=281,b.fproductunitid=281
from t_item a,t_icitembase b
where a.fnumber='1.04.03004.00007-001' and a.fitemid=b.fitemid
--将物料单位组修改
为15KG/桶，单位相应修改
update b set b.funitid=329,b.funitgroupid=328,b.forderunitid=330,b.fsaleunitid=329,b.fstoreunitid=329,b.fproductunitid=329
from t_item a,t_icitembase b
where a.fnumber='1.05.02001.00007-003' and a.fitemid=b.fitemid
--查找并将对应的物料的BOM内物料进行后台批量替换，用量保持不变
select fitemid,fnumber,fname,fmodel,* from t_icitem where fnumber in('1.01.02001.0150K-002','1.01.02001.0150K-001')
select fitemid,* from icbomchild where finterid in(1554,6305) and fitemid=3608
update icbomchild set fitemid=3068 where fitemid=3069
--将成品的默认生产类型修改为按工序跟踪
UPDATE t_icitem set FDefaultWorkTypeID=57 where FDefaultWorkTypeID=0 and fnumber like '8.08.%'
--select * from t_item where fnumber like '1.02.01026.14211-001'
--
-- 修改默认仓位 默认仓位字段 FDEFAULTLOC(为0 就是清空默认仓库)
--select * from t_icitembase where fitemid=9169
--将默认仓库不是空的修改为空
update t_icitem set fdefaultloc=0 where fdefaultloc<>0
--select * from t_icitemcore
--成品启用批次管理
update t_ICItem set fbatchmanager=1 where fnumber like '8.08.%' and fbatchmanager=0
--查找并将对应物料的启用批次管理改为不启用批次
select fbatchmanager,fnumber,ferpclsid,ftrack,* from t_icitem where fnumber not like '8%' and ftrack<>76
--update t_icitem set fbatchmanager=0 where fnumber not like '8%' and fbatchmanager=1
--将符合条件的物料的计件方法修改为加权平均
--update t_icitem set ftrack=76 where fnumber like '6%' and ftrack<>76
--查询物料的是否启用批次管理和固定提前期
select fitemid,fnumber,fname,fmodel,ffixleadtime,fbatchmanager from t_ICItem
--将物料属性修改为自制 2
select * from t_icitem where fname like '%空压机主机%'
select * from t_icitembase where ffullname like '%空压机主机%' and ferpclsid<>2
update t_icitembase set ferpclsid=2 where ffullname like '%空压机主机%' and ferpclsid<>2
--查询盘点单对应的字段
select fstockid 仓库内码,FQTY 账存数量,fqtyact 实存数量,fsecqty 辅助单位账面数量,fsecqtyact 辅助计量单位盘点数量,fseccheckqty 辅助单位实存数量,fsecminus 辅助单位差异,fadjustact 盘点差异,fauxcheckqty 盘点数量,fauxqty 账存数量,fauxqtyact 实存数量,fcheckqty 实存数量二,FITEMID 物料内码,fminus 盘亏数量,fminuspercent 盘亏率 from icinvbackup
--更新工艺路线到物料默认工艺路线
update t_ICItemplan set FDefaultRoutingID=b.FInterid
from t_ICItemplan a inner join t_Routing b
ON a.fitemid=b.fitemid and b.fdefault=1058
--更新有工艺路线的物料到相应的BOM中去
update ICBOM set FRoutingID=(select FDefaultRoutingID from t_icitem where FItemID=ICBOM.FItemID)--金软
update a set a.froutingid=b.fdefaultroutingid from icbom a,t_icitem b where a.fitemid=b.fitemid and b.fitemid<>0--本人
--
将工艺路线的表体自动派工设置为“是”
update t_routingoper set fautotd=1058
-- BOS-->视图--》系统字段描述工具
--select * from t_MeasureUnit
--物料表
select * from t_icitem
--物料表
select * from t_ICItemMaterial
--select * from vwUnitGroup
---出现2007错误时的解决方案（先查询内码，再删除记录）
select * from t_user where fname =
'李凤'
delete from t_userprofile where fuserid = '16400'
select * from t_userprofile where fuserid = '16400'
--批量删除或关闭对应的计划订单
select fmrpclosed,fitemid,* from icmrpresult where fitemid=3665 and frunid=15
delete from icmrpresult where fitemid=3665 and frunid=15
update icmrpresult set fmrpclosed='1' where fitemid=3665 and fmrpclosed<>1
select * from t_icitem where fnumber='1.01.11002.K7815-001'
--将物料单位修改为KG，无辅助单位和换算
update b set b.funitid=289,b.funitgroupid=288,b.fsecunitid='',b.forderunitid=289,b.fsaleunitid=289,b.fstoreunitid=289,b.fproductunitid=289
from t_item a,t_icitembase b
where a.fnumber='1.10.06000.00000-002' and a.fitemid=b.fitemid
--将物料的单位组和单位均修改为“双”
update t_icitem set funitid=24051,funitgroupid=24050,forderunitid=24051,fsaleunitid=24051,fstoreunitid=24051,fproductunitid=24051
where fnumber='1.10.05001.00001-002'
--批量修改物料的单位组和单位为米
select * from t_icitem where fnumber='1.04.03004.00080-001' or fnumber='1234567'
--update t_icitem set funitid=281,funitgroupid=280,forderunitid=281,fsaleunitid=281,fstoreunitid=281,fproductunitid=281 where fnumber='1.04.03004.00080-001'