——-更新及时库存辅助数量语法—————————
update a set a.FSecQty=’0′
from ICInventory a
left join t_ICItem i on a.fitemid=i.fitemid
left join t_stock b on a.FStockID=b.fitemid
where i.fnumber like ‘%6.37.IMY4651%’ and a.FQty=’426′
——更新库存批号———-
update a set a.FBatchNo=’KT-111217-01′
–select * from ICInventory a
from ICInventory a
left join t_ICItem i on a.fitemid=i.fitemid
left join t_stock b on a.FStockID=b.fitemid
where i.fnumber= ‘0.21-P60-259-142-70′ and a.FQty=’6’
————-更新物料已发生业务的物料计量单位—-
update t1 set t1.FUnitGroupID=’135′ –先更新计量单位组
update t1 set t1.FUnitID=’136′ —后更新计量单位
from t_ICItemCore t0
LEFT JOIN t_ICItemBase t1 ON t0.FItemID=t1.FItemID
where t0.FNumber=’8.34-18-0,125-0166′
–select * from t_UnitGroup
–select * from t_MeasureUnit
———计算单位已使用无法设置为默认单位/执行此SQL语法既可——-
查询：select * from t_MeasureUnit where FStandard=’0′
更新：update a set a.FStandard=’1′
from t_MeasureUnit a
where a.FStandard=’0′
——更新物料基础资料换算单位语法—–
select * from t_ICItemCustom
update t0 set t0.F_108=’pcs’
from t_ICItemCustom t0
LEFT JOIN t_ICItemCore t8 ON t8.FItemID=t0.FItemID
where t8.FNumber=’6.34.2030-D’
—-查询物料是否有用到批号管理———
–select * from t_ICItemMaterial where FBatchManager=’1′
–sELECT * FROM t_ICItemCore t0
–LEFT JOIN t_ICItemMaterial t2 ON t0.FItemID=t2.FItemID
–WHERE t0.fnumber=’5.27-CN-48-650-1D’ AND t2.FBatchManager=’1′
—-更新物料不使用批号管理—–
UPDATE t2 SET T2.FBatchManager=’0′
FROM t_ICItemCore t0
LEFT JOIN t_ICItemMaterial t2 ON t0.FItemID=t2.FItemID
where T2.FBatchManager=’1′
–WHERE t0.fnumber=’5.27-CN-48-650-1D’
—–查询使用批号管理物料库存—-
–select * from ICInventory a
–left join t_ICItem i on a.fitemid=i.fitemid
–left join t_stock b on a.FStockID=b.fitemid
–where a.FStockPlaceid=”a.FStockid=”a.FItemID=” a.FBatchNo=” and i.fnumber like ‘%5.27-CN-48-650-1D’ and a.FQty=’426′
———–商品辅助属性变更语法——————-
select FAuxClassID from t_ICItemBase
select * from t_ItemClass —辅助属性表
select FAuxClassID from t_ICItemBase
update t1 set t1.FAuxClassID=’3129′
from t_ICItemBase t1
inner join t_ICItemCore t0 on t1.FItemID=t0.FItemid
where t0.FNumber=’7.61-LXM3-PW71-01′
update t1 set t1.FAuxClassID=’3129′
from t_ICItemBase t1
inner join t_ICItemCore t0 on t1.FItemID=t0.FItemid
where t0.FNumber=’7.61-LXML-PR02-01′
—————————更新物料计价方法——-
select Ftrack from t_ICItemMaterial
update t2 set t2.Ftrack=’76’
from t_ICItemMaterial t2
LEFT JOIN t_ICItemCore t0 ON t2.FItemID=t0.FItemID
where t0.FNumber=’2.57-29-13-01-P80′
———-更新采购订单单价————————–
update i set i.FPrice =’0.130′
from POOrder y
inner join POOrderEntry i on y.finterid = i.finterid
inner join t_icitem t on i.FItemID = t.fitemid
where y.FCancellation=’0′ and i.Fmrpclosed=’0′
and y.FBillNo=’Y-POORD000907′
————更新物料固定批量———
update t2 set t2.FBatFixEconomy=’1′
from t_ICItemPlan t2
LEFT JOIN t_ICItemCore t0 ON t2.FItemID=t0.FItemID
where t0.FNumber=’2.57-29-13-01-P80′
——–委外加工币别更新语法————–
select * from ICMO where FBillNo=’DBWW999′ a
inner join t_Supplier b on a.FSupplyID=b.FItemID
inner join t_Currency c on b.FCyID=c.FcurrencyID
where ftrantype=’571′ and FType=’1067′ and a.FBillNo=’DBWW999′
——币别－－－
update a set a.FHeadSelfY1062=c.FName
from ICMO a
inner join t_Supplier b on a.FSupplyID=b.FItemID
inner join t_Currency c on b.FCyID=c.FcurrencyID
where ftrantype=’571′ and FType=’1067′ and a.FCommitDate>=’2011-01-01′
—–月结方式－－－－－
update a set a.FHeadSelfY1071=c.FName
from ICMO a
inner join t_Supplier b on a.FSupplyID=b.FItemID
inner join t_Settle c on b.FSetID=c.FItemid
where ftrantype=’571′ and FType=’1067′ and a.FCommitDate>=’2011-01-01′
select * from ICMO 委外加工表
select * from t_Supplier 供应商表
select * from t_Currency 币别表
select * from t_Settle 月结方式
———————-更新物料精度——————
——————-连接计量单位表———
–select FUnitID from t_ICItemBase a
–inner join t_MeasureUnit b on a.FUnitID=b.FItemID
–查询计量单位表——–
–select * from t_MeasureUnit
–select FQtyDecimal from t_ICItemBase where FUnitID=’136′
update a set a.FQtyDecimal=’0′
from t_ICItemBase a
where a.FUnitID=’136′
——————-更新物料默认仓库－－－－－－－－－－－－
select * from t_stock
select * from t_ICItemBase
select * from t_ICItemCore t0
LEFT JOIN t_ICItemBase t1 ON t0.FItemID=t1.FItemID
where t0.FNumber like ‘%9.12%’
update a set FDefaultLoc=’588′
from t_ICItemBase a
left join t_ICItemCore b on a.FItemID=b.FItemID
where b.FNumber like ‘%9.12%’
—–BOM表默认仓库更新－－－
select * from ICBOMChild a
inner join t_ICItem b on a.FItemID = b.FItemID
where b.FNumber like ‘%7.%’
update a set a.FStockID=’335′
from ICBOMChild a
inner join t_ICItem b on a.FItemID = b.FItemID
where b.FNumber like ‘%9.2%’
———委外加工完工入库数量更新－－－－－
select * from ICInventory WHERE FQty=’0′
delete from ICInventory WHERE FQty=’0′ and FSecQty=’0′
select * from ICInventory a
left join t_ICItem i on a.fitemid=i.fitemid
left join t_stock b on a.FStockID=b.fitemid
WHERE FQty=’0′ and FSecQty=’0′
————-更新委外加工入生产单完工数量－－－－
select * from ICMO WHERE FBillNo=’DSWW1000′
update a set a.FCommitQty=’268740′
from icmo a
where a.FBillNo=’DSWW1000′
update a set a.FAuxCommitQty =’268740′
from icmo a
where a.FBillNo=’DSWW1000′
——–更新物料属性－－－自制改成委外加工－－
update t1 set t1.FErpClsID=’3′
from t_ICItemBase t1
inner join t_ICItemCore t0 on t1.FItemID=t0.FItemid
where t0.FNumber=’E.42A15-W001′
–1代表外购 2代表自制 3代表委外加工
select FAuxClassID from t_ICItemBase
———-物料单价精度－－－－－－－－
update a set FPriceDecimal=’4′
from t_ICItemMaterial a
inner join t_ICItemCore t0 ON a.FItemID=t0.FItemID
where t0.FNumber like ‘%7.44%’
————使用批号 及 更新物料库存批号—
UPDATE t2 SET T2.FBatchManager=’1′
FROM t_ICItemCore t0
LEFT JOIN t_ICItemMaterial t2 ON t0.FItemID=t2.FItemID
WHERE t0.fnumber like ‘%0.51%’
update a set a.FBatchNo=’KT-111013-01′
from ICInventory a
left join t_ICItem i on a.fitemid=i.fitemid
left join t_stock b on a.FStockID=b.fitemid
where i.fnumber like ‘%0.51%’
————更新出入库单价或金额为的‘0’单据
select * from icstockbill i
inner join icstockbillentry y on i.finterid = y.finterid
inner join t_supplier r on i.fsupplyid = r.fitemid
inner join t_icitem t on y.fitemid = t.fitemid
inner join t_measureunit tt on y.funitid = tt.fitemid
inner join t_stock k on y.fdcstockid = k.fitemid
where FCancellation<>1 and y.FAmount=’0′
–and y.FPrice=’0′
y.FPrice 单价
y.FAmount 金额
update y set y.FAmount=y.FPrice * y.FQty
from icstockbill i
inner join icstockbillentry y on i.finterid = y.finterid
inner join t_supplier r on i.fsupplyid = r.fitemid
inner join t_icitem t on y.fitemid = t.fitemid
inner join t_measureunit tt on y.funitid = tt.fitemid
inner join t_stock k on y.fdcstockid = k.fitemid
where FCancellation<>1 and y.FAmount=’0′
——
select y.FPrice from icstockbill i
inner join icstockbillentry y on i.finterid = y.finterid
where y.FPrice=’0′
–and y.FPrice=’0′
FPlanAmount
FPlanPrice
y.FPrice 单价
y.FAmount 金额
FAuxPrice
update y set y.FAmount=
update y set y.FAmount=y.FPrice * y.FQty
from icstockbill i
inner join icstockbillentry y on i.finterid = y.finterid
where y.FAmount=’0′
————–更新物料辅助单位———-
select * from t_ICItem where fNumber=’KP.0058EU13WTRE’
select * from t_ICItem where fNumber=’KC.0089EU13WTWR48′
select * from t_MeasureUnit
select FSecUnitid from t_ICItemCore t0
LEFT JOIN t_ICItemBase t1 ON t0.FItemID=t1.FItemID
where t0.fNumber=’KC.0089EU13WTWR48′
update t1 set t1.FSecUnitid=’132′
from t_ICItemCore t0
LEFT JOIN t_ICItemBase t1 ON t0.FItemID=t1.FItemID
where t0.fNumber=’KC.0089EU13WTWR48′
132 灯饰账套 卡板换算单位代码
133 功能灯账套 卡板换算单位代码
FSecUnitid 卡板换算单位代码
—–删除已清理固定资产—需同时清以下表同样的ID
select * from t_FAcard b where b.FAssetNumber=’02021346′
select * from t_FAClear
delete e from t_FAOrgFor e where e.FAlterID=’1693′
delete c from t_FAClear c where c.FAssetID=’1693′
delete a from t_FACardITem a where a.FAlterID=’1693′
delete a from t_FADept a where a.FAlterID=’1693′
delete a from t_FAExpense a where a.FAlterID=’1693′
delete a from t_faaLter a where a.FAlterID=’1693′
delete b from t_FAcard b where b.FAssetNumber=’02021346′ and FAlterID=’1693′
delete a from t_faaLter a where a.FAssetID=’1693′
select * from t_FAcard where FAlterID=’4760′
—–委外加工任务单上下限数量——–
select * from ICMO
where ftrantype=’571′ and FBillNo=’ww6033′
update a set a.FAuxInHighLimitQty=30000 from ICMO a
where ftrantype=’571′ and a.FBillNo=’ww6033′
update a set a.FAuxInLowlimitQty=30000 from ICMO a
where ftrantype=’571′ and a.FBillNo=’ww6033′
——–将物料计划单价更新至标准成本————–
计划单价：FPlanPrice 标准成本单价：FStandardCost
select FPlanPrice FROM t_ICItemCore t0
LEFT JOIN t_ICItemStandard t5 ON t0.FItemID=t5.FItemID
left join t_ICItemMaterial t2 on t0.fitemid=t2.fitemid
where t0.FNumber=’1.42-0881-162-D28′
update t5 set t5.FStandardCost=t2.FPlanPrice
FROM t_ICItemCore t0
LEFT JOIN t_ICItemStandard t5 ON t0.FItemID=t5.FItemID
left join t_ICItemMaterial t2 on t0.fitemid=t2.fitemid
where t0.FNumber=’1.42-0881-162-D28′
——-清理委加工出入、采购订单、委外加工订单 －－
select * from ICMO a
inner join t_Supplier b on a.FSupplyID=b.FItemID
where a.ftrantype=’571′ and b.fname like ‘%华特伦%’
select * from POOrder y
inner join POOrderEntry i on y.finterid = i.finterid
inner join t_icitem t on i.FItemID = t.fitemid
inner join t_supplier b on y.fsupplyid=b.fitemid
where b.fname like ‘%华特伦%’
select * from icstockbill i
inner join icstockbillentry y on i.finterid = y.finterid
inner join t_supplier r on i.fsupplyid = r.fitemid
where i.ftrantype in(5,28) and r.fname like ‘%华特伦%’
select * from t_Supplier where