——-更新及时库存辅助数量语法—————————

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