SQL更新-金蝶kis专业版的-计量单位
SQL更新金蝶kis专业版-计量单位
-------------------------------------------------------------
001-更新计量单位的基本计量单位标志
查看高清图片方法：点击图片---右键---在新标签页中打开图片（或用360浏览器全屏看图）
-------------------------------------------------------------
002-更新物料的计量单位
查看高清图片方法：点击图片---右键---在新标签页中打开图片（或用360浏览器全屏看图）
-------------------------------------------------------------
003-更新会计科目的计量单位
查看高清图片方法：点击图片---右键---在新标签页中打开图片（或用360浏览器全屏看图）
-------------------------------------------------------------
004-更新凭证列表中的计量单位
查看高清图片方法：点击图片---右键---在新标签页中打开图片（或用360浏览器全屏看图）
-------------------------------------------------------------
SQL语句:
------------------------
--************************************************--************************************************ --************************************************
--************************************************--************************************************ --************************************************
--说明:适合使用了[单个]---[计量单位]的账套,如果使用的计量单位比较多,更新的时候容易出现错误
--该操作前提条件:
--1.执行前必须做金蝶账套的备份
--2.操作人员必须熟悉金蝶软件
--3.操作人员必须熟悉sql数据库
--4.必须在所有凭证处于未记账的状态下执行
--5.sql语句的执行条件,请自行根据实际情况进行调整
--说明:执行后,请自行检测是否对金蝶软件操作有不良影响,我方不对这些sql语句的正确性负责
--************************************************--************************************************ --************************************************
--基础档案-07.计量单位
select * from t_MeasureUnit
-------------
select t_MeasureUnit.FMeasureUnitID As 单位内码 , t_MeasureUnit.FUnitGroupID As 单位组别 , t_MeasureUnit.FNumber As 单位代码 , t_MeasureUnit.FAuxClass As 未知1 , t_MeasureUnit.FName As 单位名称 , t_MeasureUnit.FConversation As 未知3 , t_MeasureUnit.FCoefficient As 系数 , t_MeasureUnit.FPrecision As 未知5 , t_MeasureUnit.FBrNo As 公司代码 , t_MeasureUnit.FItemID As 单位内码二 , t_MeasureUnit.FParentID As 上级内码 , t_MeasureUnit.FDeleted As 是否禁用 , t_MeasureUnit.FShortNumber As 单位简码 , t_MeasureUnit.FOperDate As 操作日期 , t_MeasureUnit.FScale As 修正值 , t_MeasureUnit.FStandard As 基本单位 , t_MeasureUnit.FControl As 未知2 , t_MeasureUnit.FModifyTime As 未知4 , t_MeasureUnit.FSystemType As 系统标示 , t_MeasureUnit.UUID As 未知6
from t_MeasureUnit
--where FName = '吨'
--基础档案-06.计量单位组
select * from t_UnitGroup
-------------
select t_UnitGroup.FUnitGroupID As 单位组内码 , t_UnitGroup.FName As 单位组名称 , t_UnitGroup.FDefaultUnitID As 默认单位内码
from t_UnitGroup
-------------
--基础档案-06.计量单位组连接计量单位
select T1.FUnitGroupID As 单位组_组内码 , T1.FName As 单位组_组名称 , T1.FDefaultUnitID As 单位组_基本单位内码
, T2.FUnitGroupID As 单位_组内码 , T2.FMeasureUnitID as 单位_单位内码 , T2.FNumber as 单位_单位代码 , T2.FName as 单位_单位名称 , T2.FStandard as 单位_是否基本单位1是0否
from t_UnitGroup as T1
left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
--************************************************
--更新计量单位的名称
update t_MeasureUnit
set FNumber = 'TON'
, FName = '吨'
where FMeasureUnitID = 202
------------------------------------------------------------
--根据计量单位代码,更新计量单位的名称
--比如---套
update t_MeasureUnit
set FName = '套'
where FNumber = 'TAO'
--比如---台
update t_MeasureUnit
set FName = '台'
where FNumber = 'TAI'
--比如---块
update t_MeasureUnit
set FName = '块'
where FNumber = 'KUAI'
--比如---只
update t_MeasureUnit
set FName = '只'
where FNumber = 'ZHI'
--比如---件
update t_MeasureUnit
set FName = '件'
where FNumber = 'pcs'
--比如---栋
update t_MeasureUnit
set FName = '栋'
where FNumber = 'DONG'
--比如---辆
update t_MeasureUnit
set FName = '辆'
where FNumber = 'LIANG'
--比如---批
update t_MeasureUnit
set FName = '批'
where FNumber = 'PI'
--比如---一批
update t_MeasureUnit
set FName = '一批'
where FNumber = 'YIPI'
--------------------------
--比如---米
update t_MeasureUnit
set FName = '米'
where FNumber = 'M'
--比如---分米
update t_MeasureUnit
set FName = '分米'
where FNumber = 'DM'
--比如---厘米
update t_MeasureUnit
set FName = '厘米'
where FNumber = 'CM'
--比如---毫米
update t_MeasureUnit
set FName = '毫米'
where FNumber = 'MM'
--------------------------
--比如---平方米
update t_MeasureUnit
set FName = '平方米'
where FNumber = 'SQM'
--比如---平方分米
update t_MeasureUnit
set FName = '平方分米'
where FNumber = 'SQDM'
--比如---平方厘米
update t_MeasureUnit
set FName = '平方厘米'
where FNumber = 'SQCM'
--------------------------
--比如---立方米
update t_MeasureUnit
set FName = '立方米'
where FNumber = 'CUM'
--比如---立方分米
update t_MeasureUnit
set FName = '立方分米'
where FNumber = 'CUDM'
--比如---立方厘米
update t_MeasureUnit
set FName = '立方厘米'
where FNumber = 'CUCM'
----------------------
--比如---吨
update t_MeasureUnit
set FName = '吨'
where FNumber = 'TON'
--比如---千克
update t_MeasureUnit
set FName = '公斤(千克)'
where FNumber = 'KG'
--比如---克
update t_MeasureUnit
set FName = '克'
where FNumber = 'G'
--************************************************
--1>.更新[计量单位]表----[是否默认]字段
----------------------------------------------
--第1步,更新[mdf计量组]中----[是否默认]的字段为[0](即取消所有的默认标志)
update t_MeasureUnit
set FStandard = 0
where t_MeasureUnit.FUnitGroupID in (
select DISTINCT T1.FUnitGroupID As 单位组_组内码
from t_UnitGroup as T1 left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
where T1.FName = 'mdf计量组' --[变量1]
)
--第2步,更新[mdf计量组]中----[是否默认]的字段为[1](重新设置,默认标志),比如将[mdf计量组]中的[吨]为该组的默认单位
update t_MeasureUnit
set FStandard = 1
where t_MeasureUnit.FMeasureUnitID in (
select DISTINCT T2.FMeasureUnitID as 单位_单位内码
from t_UnitGroup as T1 left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
where T1.FName = 'mdf计量组' --[变量1]
and T2.FName = '吨' --[变量2]
)
--************************************************
--2>.更新[计量单位组]表----[默认单位内码]字段 ,
--将mdf计量组中,默认单位pcs更新为吨
update t_UnitGroup
set FDefaultUnitID = (
select DISTINCT T2.FMeasureUnitID as 单位_单位内码
from t_UnitGroup as T1 left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
where T1.FName = 'mdf计量组' --[变量1]
and T2.FName = '吨' --[变量2]
)
--************************************************ --************************************************ --************************************************
--基础档案-04.产品表
select * from t_ICItem
-------------
select t_ICItem.FItemID As 产品内码 , t_ICItem.FName As 产品名称 , t_ICItem.FHelpCode As 助记码 , t_ICItem.FDeleted As 是否禁用 , t_ICItem.FShortNumber As 产品简码 , t_ICItem.FNumber As 产品代码 , t_ICItem.FModifyTime As 未知3 , t_ICItem.FParentID As 产品类代码 , t_ICItem.FBrNo As 公司代码 , t_ICItem.FTopID As 最高级 , t_ICItem.FRP As 收付标志 , t_ICItem.FOmortize As 未知4 , t_ICItem.FOmortizeScale As 未知5 , t_ICItem.FForSale As 是否销售1 , t_ICItem.FStaCost As 未知9 , t_ICItem.FOrderPrice As 订货单价 , t_ICItem.FOrderMethod As 订货方法 , t_ICItem.FPriceFixingType As 未知7 , t_ICItem.FSalePriceFixingType As 未知8 , t_ICItem.FPerWastage As 未知6 , t_ICItem.FARAcctID As 应收科目 , t_ICItem.FPlanPriceMethod As 计划方法 , t_ICItem.FPlanClass As 计划类型 , t_ICItem.FModel As 规格型号 , t_ICItem.FAlias As 别名 , t_ICItem.FApproveNo As 批准文号 , t_ICItem.FAuxClassID As 辅助属性类别 , t_ICItem.FDefaultLoc As 缺省库房 , t_ICItem.FEquipmentNum As 设备编码 , t_ICItem.FErpClsID As 产品属性 , t_ICItem.FFullName As 全名 , t_ICItem.FHighLimit As 最高存量 , t_ICItem.FIsEquipment As 是否设备 , t_ICItem.FIsSparePart As 是否备件 , t_ICItem.FLowLimit As 最低存量 , t_ICItem.FOrderUnitID As 采购计量单位 , t_ICItem.FPreDeadLine As 未知91 , t_ICItem.FProductUnitID As 生产计量单位 , t_ICItem.FQtyDecimal As 数量精度 , t_ICItem.FSaleUnitID As 销售计量单位 , t_ICItem.FSecCoefficient As 辅助计量单位换算率 , t_ICItem.FSecUnitDecimal As 未知92 , t_ICItem.FSecInv As 安全库存 , t_ICItem.FSecUnitID As 辅助计量单位 , t_ICItem.FSerialClassID As 未知93 , t_ICItem.FSource As 来源 , t_ICItem.FSPID As 缺省仓位 , t_ICItem.FStoreUnitID As 库存计量单位 , t_ICItem.FTypeID As 产品分类 , t_ICItem.FUnitGroupID As 单位组内码 , t_ICItem.FUnitID As 单位内码 , t_ICItem.FUseState As 使用状态 , t_ICItem.FABCCls As ABC类别 , t_ICItem.FAcctID As 存货科目 , t_ICItem.FAdminAcctID As 代管物资科目 , t_ICItem.FAPAcctID As 应付科目 , t_ICItem.FBatchManager As 是否采用业务批次管理 , t_ICItem.FBatchQty As 订货批量 , t_ICItem.FBeforeExpire As 失效提前期_天 , t_ICItem.FBookPlan As 是否需要进行订补货计划的运算 , t_ICItem.FCheckCycle As 盘点周期 , t_ICItem.FCheckCycUnit As 盘点周期单位 , t_ICItem.FClass As 产品类别 , t_ICItem.FCostAcctID As 成本科目 , t_ICItem.FCostDiffRate As 成本差异科目 , t_ICItem.FCostProject As 成本项目 , t_ICItem.FDaysPer As 每周月第_天 , t_ICItem.FDepartment As 部门 , t_ICItem.FGoodSpec As 税目代码内码 , t_ICItem.FISKFPeriod As 是否进行保质期管理 , t_ICItem.FIsSale As 是否销售2 , t_ICItem.FIsSnManage As 未知2 , t_ICItem.FIsSpecialTax As 是否农林计税 , t_ICItem.FKFPeriod As 保质期_天 , t_ICItem.FLastCheckDate As 上次盘点日期 , t_ICItem.FNote As 备注 , t_ICItem.FOIHighLimit As 外购超收比例_ , t_ICItem.FOILowLimit As 外购欠收比例_ , t_ICItem.FOrderRector As 采购负责人内码 , t_ICItem.FPlanPrice As 计划单价 , t_ICItem.FPOHghPrcMnyType As 采购最高价币别 , t_ICItem.FPOHighPrice As 采购最高价 , t_ICItem.FPriceDecimal As 单价精度 , t_ICItem.FProfitRate As 毛利率 , t_ICItem.FSaleAcctID As 销售科目 , t_ICItem.FSalePrice As 销售单价 , t_ICItem.FSaleTaxAcctID As 税金科目 , t_ICItem.FSOHighLimit As 销售超交比例_ , t_ICItem.FSOLowLimit As 销售欠交比例_ , t_ICItem.FSOLowPrc As 销售最低价 , t_ICItem.FSOLowPrcMnyType As 销售最低价币别 , t_ICItem.FStockPrice As 未知98 , t_ICItem.FStockTime As 是否需要库龄管理 , t_ICItem.FTaxRate As 税率_ , t_ICItem.FTrack As 计价方法 , t_ICItem.FWWHghPrc As 委外加工最高价 , t_ICItem.FWWHghPrcMnyType As 委外加工最高价币别 , t_ICItem.FCBBmStandardID As 分配标准内码 , t_ICItem.FBatChangeEconomy As 变动提前期批量 , t_ICItem.FBatchAppendQty As 批量增量 , t_ICItem.FBatFixEconomy As 固定经济批量 , t_ICItem.FCUUnitID As 常用计量单位 , t_ICItem.FDailyConsume As 日消耗量 , t_ICItem.FDefaultRoutingID As 默认工艺路线 , t_ICItem.FDefaultWorkTypeID As 默认生产类型 , t_ICItem.FFixLeadTime As 固定提前期 , t_ICItem.FInHighLimit As 完工超收比例_ , t_ICItem.FInLowLimit As 完工欠收比例_ , t_ICItem.FLeadTime As 提前期 , t_ICItem.FLowestBomCode As 低层码 , t_ICItem.FMRPCon As MRP计算是否合并需求 , t_ICItem.FOrderInterVal As 订货间隔期_天 , t_ICItem.FOrderPoint As 再订货点 , t_ICItem.FOrderTrategy As 订货策略 , t_ICItem.FPlanner As 计划员 , t_ICItem.FPlanPoint As 计划时界_天 , t_ICItem.FPlanTrategy As 计划策略 , t_ICItem.FProductPrincipal As 生产负责人 , t_ICItem.FPutInteger As 投料自动取整 , t_ICItem.FQtyMax As 最大订货量 , t_ICItem.FQtyMin As 最小订货量 , t_ICItem.FRequirePoint As 需求时界_天 , t_ICItem.FTotalTQQ As 累计提前期 , t_ICItem.FMRPOrder As MRP计算是否产生采购申请 , t_ICItem.FChartNumber As 图号 , t_ICItem.FCubicMeasure As 长度单位 , t_ICItem.FGrossWeight As 毛重 , t_ICItem.FHeight As 高度 , t_ICItem.FIsKeyItem As 是否关键件 , t_ICItem.FLength As 长度 , t_ICItem.FMaund As 重量单位 , t_ICItem.FNetWeight As 净重 , t_ICItem.FSize As 体积 , t_ICItem.FVersion As 版本号 , t_ICItem.FWidth As 宽度 , t_ICItem.FChgFeeRate As 变动制造费用分配率 , t_ICItem.FOutMachFee As 单位委外加工费 , t_ICItem.FPieceRate As 单位计件工资 , t_ICItem.FStandardCost As 单位标准成本 , t_ICItem.FStandardManHour As 单位标准工时_小时 , t_ICItem.FStdFixFeeRate As 单位标准固定制造费用金额 , t_ICItem.FStdPayRate As 标准工资率 , t_ICItem.FIdentifier As 检验员 , t_ICItem.FInspectionLevel As 检验方式 , t_ICItem.FInspectionProject As 检验方案 , t_ICItem.FIsListControl As 未知1 , t_ICItem.FOtherChkMde As 其他检验方式 , t_ICItem.FProChkMde As 产品检验方式 , t_ICItem.FSOChkMde As 发货检验方式 , t_ICItem.FStkChkAlrm As 库存周期检验预警提前期_天 , t_ICItem.FStkChkMde As 库存检验方式 , t_ICItem.FStkChkPrd As 库存检验周期_天 , t_ICItem.FWthDrwChkMde As 退货检验方式 , t_ICItem.FWWChkMde As 委外加工检验方式
from t_ICItem
--************************************************
----------------------------------------------
--1>.更新[物料表]的[计量单位组]参数,
--比如,将[指定物料]的----[默认计量单位组],改为:[mdf计量组]
update t_ICItem
set FUnitGroupID = (
select FUnitGroupID As 单位组_组内码
from t_UnitGroup
where FName = 'mdf计量组'
)
where FNumber = '[物料代码]'
and FName = '[物料名称]'
--------------------------------
--2>.更新[物料表]的[默认计量单位]参数,
--比如,将[指定物料]的----[默认计量单位],改为:[mdf计量组]中的[默认单位]
update t_ICItem
set FUnitID = (
select FDefaultUnitID As 单位组_基本单位内码
from t_UnitGroup
where FName = 'mdf计量组'
)
where FNumber = '[物料代码]'
and FName = '[物料名称]'
--************************************************ --************************************************ --************************************************
--基础档案-会计科目
select * from t_Account --会计科目档案
--------------------------
select T1.FNumber as T1科目代码 , T1.FName as T1科目名称 , T1.FUnitGroupID as T1计量单位组内码 , T1.FMeasureUnitID as T1计量单位内码
, T2.FUnitGroupID as T2计量单位组内码 , T2.FName as T2计量单位组名称
, T3.FMeasureUnitID as T3计量单位内码 , T3.FNumber as T3计量单位代码 , T3.FName as T3计量单位名称
from t_Account as T1
left join t_UnitGroup as T2 on T1.FUnitGroupID = T2.FUnitGroupID
left join t_MeasureUnit as T3 on T1.FMeasureUnitID = T3.FMeasureUnitID
where T1.FUnitGroupID <> 0 or T1.FMeasureUnitID <> 0
--************************************************
----------------------------------------------
--1>.更新[会计科目]的[计量单位组]参数,
--比如,改为:[mdf计量组]
update t_Account
set FUnitGroupID = (
select FUnitGroupID As 单位组_组内码
from t_UnitGroup
where FName = 'mdf计量组' --[变量1]
)
where FUnitGroupID = '181' --变量2:该参数必填[原计量组----内码]
and FNumber = '[科目代码]'
and FName = '[科目名称]'
---更新会计科目的指定[缺省单位]
update t_Account
set FMeasureUnitID = (
select DISTINCT T2.FMeasureUnitID as 单位_单位内码
from t_UnitGroup as T1 left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
where T1.FName = 'mdf计量组' --[变量1]
and T2.FName = '吨' --[变量2]
)
where FMeasureUnitID ='182' --变量3:该参数必填[原计量单位----内码]
and FNumber = '[科目代码]'
and FName = '[科目名称]'
--************************************************ --************************************************ --************************************************
----------------
--凭证分录表
select * from t_VoucherEntry
--------------------------
--凭证
select t_Voucher.FYear As 会计年度 , t_Voucher.FPeriod As 会计期间 , t_Voucher.FDate As 凭证日期 , t_VoucherGroup.FName As 凭证字名称 , t_Voucher.FNumber As 凭证号 , t_VoucherEntry.FEntryID As 分录号 , t_VoucherEntry.FExplanation As 摘要 , t_Account.FNumber As 科目代码 , t_Account.FFullName As 科目全名 , t_VoucherEntry.FDC As 借贷方向 , t_VoucherEntry.FAmount As 本位币金额 , t_Currency.FNumber As 币别代码 , t_Currency.FName As 币别名称 , t_VoucherEntry.FExchangeRate As 汇率_凭证分录表 , t_user.FName As 用户名称 , t_Voucher.FChecked As 是否审核 , t_Voucher.FPosted As 是否过账 , t_Voucher.FReference As 参考信息 , t_Voucher.FDebitTotal As 借方金额合计 , t_Voucher.FCreditTotal As 贷方金额合计 , t_Voucher.FPreparerID As 制单人 , t_Voucher.FSerialNum As 凭证序号 , t_VoucherEntry.FDetailID As 核算项目使用状况内码_凭证分录表 , t_VoucherEntry.FAmountFor As 原币金额 , t_VoucherEntry.FAccountID2 As 对方科目 , t_Account.FName As 科目名称
,t_VoucherEntry.FQuantity as 数量 , t_VoucherEntry.FUnitPrice as 单价 , t_VoucherEntry.FMeasureUnitID as 凭证分录_计量单位内码 , t_MeasureUnit.FMeasureUnitID as 计量单位_计量单位内码 , t_MeasureUnit.FNumber As 单位代码 , t_MeasureUnit.FName As 单位名称
from t_Voucher
left outer join t_VoucherEntry on t_Voucher.FVoucherID = t_VoucherEntry.FVoucherID
left outer join t_VoucherGroup on t_Voucher.FGroupID = t_VoucherGroup.FGroupID
left outer join t_user on t_Voucher.FPreparerID = t_user.FUserID
left outer join t_Account on t_VoucherEntry.FAccountID = t_Account.FAccountID
left outer join t_Currency on t_VoucherEntry.FCurrencyID = t_Currency.FCurrencyID
left outer join t_MeasureUnit on t_VoucherEntry.FMeasureUnitID = t_MeasureUnit.FMeasureUnitID
where t_VoucherEntry.FMeasureUnitID <> 0
--************************************************
--------------------------------
---更新凭证分录中的计量单位
update t_VoucherEntry
set t_VoucherEntry.FMeasureUnitID = (
select DISTINCT T2.FMeasureUnitID as 单位_单位内码
from t_UnitGroup as T1 left join t_MeasureUnit as T2 on T1.FUnitGroupID = T2.FUnitGroupID
where T1.FName = 'mdf计量组' --[变量1]---[计量单位]------档案的名称
and T2.FName = '吨' --[变量2]---[计量单位组]----档案的名称
)
where t_VoucherEntry.FMeasureUnitID ='182' --变量3:凭证分录的计量单位原内码
-------------------------------------------------------------
-------------------------------------------------------------
