返回黑办首页
当前位置: 黑办主页 > IT外包 > 正文内容

金蝶K3 V10.4生产企业以成品为主体查询生产其所需物料库存信息

时间:2010-03-31 15:31来源:未知 作者:admin 点击:
生产企业以成品为主体查询生产其所需物料库存信息(类似BOM) 文档整理时间:2010年3月31日 数据库:金蝶K3 V10.4 工业账套 实现:VB6.0 提出问题: 企业产品是按照成品、半成品、中间体、材料进行分类存储的。 经过一段时间运行,需要以成品为主体,查询显
  

生产企业以成品为主体查询生产其所需物料库存信息(类似BOM)

文档整理时间:2010年3月31日

数据库:金蝶K3 V10.4 工业账套

实现:VB6.0

 

提出问题:
企业产品是按照成品、半成品、中间体、材料进行分类存储的。
经过一段时间运行,需要以成品为主体,查询显示制造主体成品的半成品、中间体和材料数据。并且计算出以当前库存量,还可以生产多少成品。
经过分析,k3 V10.4软件本身无法实现这种产品交叉功能的查询,需要经过二次开发

 

需求分析:
基于公司的要求,对系统进行了分析
由于公司启用了BOM功能,所以所需功能的查询数据对照,完全可以从BOM里面获得。
所需功能完成的步骤:
第一步,从BOM单数据表中,导出数据到临时查询数据库
第二步,提取BOM主体(成品)数据到临时查询库
第三步,进行数据整合
第四步,导出报表或打印报表

 

完成过程:
一、涉及的表

1.SELECT FMeasureUnitID, FName FROM t_MeasureUnit
  计量单位表(辅表) 
  字段说明:FMeasureUnitID单位内码,FName单位名称
2.SELECT FInterID, FName, FNumber, FBootID FROM ICBOMGroup
  BOM组别表(主表)
  字段说明:FBootID BOM根组内码
3.SELECT FInterID, FBOMNumber, FParentID, FItemID, FQty, FUnitID, FAUXQTY FROM ICBOM
  BOM表(主表)
  字段说明:FParentID关联BOM组别表ICBOMGROUP.FInterID
            FUnitID 单位代码关联计量单位表t_MeasureUnit.FMeasureUnitID
            FItemID 物料内码 关联
4.BOM视图数据
SELECT dbo.vICBOM.FParentID, dbo.vICBOM.FBOMNumber, dbo.vICBOM.FName,
              dbo.vICBOM.FInterID, dbo.vICBOM.FNumber, dbo.vICBOM.Fmodel,
              dbo.vICBOM.FStatus, dbo.vICBOM.FUseStatus, dbo.vICBOM.FUnitName,
              dbo.vICBOM.FQty, dbo.vICBOM.FNote, dbo.vICBOM.FEntryID,
              dbo.vICBOM.FChildNumber, dbo.vICBOM.FChildName,
              dbo.vICBOM.FChildModel, dbo.vICBOM.FChildUnitName,
              dbo.vICBOM.FChildQty, dbo.vICBOM.FStockID, dbo.vICBOM.FchildNote,
              dbo.t_ICItem.FNumber AS t_ICItemFNumber,
              dbo.t_ICItem.FName AS t_ICItemFName,
              dbo.ICInventory.FQty AS ICInventoryFQty
        FROM dbo.vICBOM INNER JOIN
              dbo.t_ICItem ON
              dbo.vICBOM.FChildNumber = dbo.t_ICItem.FNumber INNER JOIN
              dbo.ICInventory ON dbo.t_ICItem.FItemID = dbo.ICInventory.FItemID
vICBOM为BOM视图
字段说明:
FParentID:BOM分组名称
FBOMNumber:BOM单号
FName:BOM单名称 商品名称
FInterID:BOM内码
FNumber:BOM单商品代码
Fmodel:父项规格
FStatus:BOM状态 审核 未审核
FUseStatus:BOM使用状态   使用  未使用
FUnitName:父项单位
FQty:父项商品单位数量
FNote:父项备注
FEntryID:子项顺序号
FChildNumber:子项长代码
FChildName:子项名称
FChildModel:子项规格
FChildUnitName:子项单位
FChildQty:子项数量
FStockID:仓库
FchildNote:子项备注
5.Select * from t_ICItem  商品表

二、实现步骤
第一步:本sql语句提取了BOM对应的数据信息,包括库存和现有库存能生产产品的数量

SELECT dbo.vICBOM.FParentID AS 组名, dbo.vICBOM.FBOMNumber AS BOM单号,
      dbo.vICBOM.FName AS 商品名称, dbo.vICBOM.FInterID AS BOM内码,
      dbo.vICBOM.FNumber AS 商品编码, dbo.vICBOM.Fmodel AS 商品规格,
      dbo.vICBOM.FStatus AS 审核状态, dbo.vICBOM.FUseStatus AS 使用状态,
      dbo.vICBOM.FUnitName AS 商品单位, dbo.vICBOM.FQty AS 商品数量,
      dbo.vICBOM.FNote AS 商品备注, dbo.vICBOM.FEntryID AS 子项顺序号,
      dbo.vICBOM.FChildNumber AS 子项代码, dbo.vICBOM.FChildName AS 子项名称,
      dbo.vICBOM.FChildModel AS 子项规格, dbo.vICBOM.FChildUnitName AS 子项单位,
      dbo.vICBOM.FChildQty AS 子项BOM需求数量, dbo.vICBOM.FStockID AS 仓库,
      dbo.vICBOM.FchildNote AS 子项备注, ISNULL(b.ICInventoryFqty, 0) AS 子项库存,
      ISNULL(ROUND(b.ICInventoryFqty / dbo.vICBOM.FChildQty * dbo.vICBOM.FQty, 2), 0)
      AS 还可生产件数
FROM dbo.vICBOM INNER JOIN
      dbo.t_ICItem ON
      dbo.vICBOM.FChildNumber = dbo.t_ICItem.FNumber LEFT OUTER JOIN
          (SELECT dbo.ICInventory.fitemid, SUM(dbo.ICInventory.Fqty)
               AS ICInventoryFqty
         FROM dbo.ICInventory
         GROUP BY fitemid) b ON dbo.t_ICItem.FItemID = b.fitemid

注释:(SELECT dbo.ICInventory.fitemid, SUM(dbo.ICInventory.Fqty) AS ICInventoryFqty FROM dbo.ICInventory GROUP BY fitemid) b 
这样写的目的是因为有些商品在多个仓库中都有,必须把数量合计才行

第二步:新建临时数据库,将数据导入新库,进行二次加工,提供给用户
数据库名称:A_temp
数据表都是临时导入

将bom单子项引入到临时数据库中处理
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vICBOM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table a_temp.[dbo].[vICBOM]


SELECT ais20100130173207.dbo.vICBOM.FParentID AS 组名, ais20100130173207.dbo.vICBOM.FBOMNumber AS BOM单号,
      ais20100130173207.dbo.vICBOM.FName AS 商品名称, ais20100130173207.dbo.vICBOM.FInterID AS BOM内码,
      ais20100130173207.dbo.vICBOM.FNumber AS 商品编码, ais20100130173207.dbo.vICBOM.Fmodel AS 商品规格,
      ais20100130173207.dbo.vICBOM.FStatus AS 审核状态, ais20100130173207.dbo.vICBOM.FUseStatus AS 使用状态,
      ais20100130173207.dbo.vICBOM.FUnitName AS 商品单位, ais20100130173207.dbo.vICBOM.FQty AS 商品数量,
      ais20100130173207.dbo.vICBOM.FNote AS 商品备注, ais20100130173207.dbo.vICBOM.FEntryID AS 子项顺序号,
      ais20100130173207.dbo.vICBOM.FChildNumber AS 子项代码, ais20100130173207.dbo.vICBOM.FChildName AS 子项名称,
      ais20100130173207.dbo.vICBOM.FChildModel AS 子项规格, ais20100130173207.dbo.vICBOM.FChildUnitName AS 子项单位,
      ais20100130173207.dbo.vICBOM.FChildQty AS 子项BOM需求数量, ais20100130173207.dbo.vICBOM.FStockID AS 仓库,
      ais20100130173207.dbo.vICBOM.FchildNote AS 子项备注, ISNULL(b.ICInventoryFqty, 0) AS 子项库存,
      ISNULL(ROUND(b.ICInventoryFqty / ais20100130173207.dbo.vICBOM.FChildQty * ais20100130173207.dbo.vICBOM.FQty, 1), 0)
      AS 可生产数量
into vICBOM FROM ais20100130173207.dbo.vICBOM INNER JOIN
      ais20100130173207.dbo.t_ICItem ON
      ais20100130173207.dbo.vICBOM.FChildNumber = ais20100130173207.dbo.t_ICItem.FNumber LEFT OUTER JOIN
          (SELECT ais20100130173207.dbo.ICInventory.fitemid, SUM(ais20100130173207.dbo.ICInventory.Fqty)
               AS ICInventoryFqty
         FROM ais20100130173207.dbo.ICInventory
         GROUP BY fitemid) b ON ais2010013= b.fitemid
注释:ais20100130173207 为数据源 数据库

第三步:按bom单主体产品,提取当前库存数量,用来显示在第一行(dbo前面需要缀上源数据库名称)
SELECT
dbo.ICBOM.FBOMNumber as BOM单号,
dbo.ICBOM.FInterID as BOM内码,
dbo.t_ICItem.FName as 商品名称,
dbo.t_ICItem.FNumber 商品编码,
dbo.ICBOM.FQty AS 商品数量,
isnull(dbo.t_ICItem.Fmodel,'') as 商品规格,
dbo.t_MeasureUnit.FName AS 商品单位,
dbo.t_ICItem.FNumber 子项代码,
dbo.t_ICItem.FName as 子项名称,
isnull(dbo.t_ICItem.Fmodel,'') as 子项规格,
dbo.t_MeasureUnit.FName AS 子项单位,
ISNULL(b.ICInventoryFqty, 0) AS 子项库存,
0 as 子项顺序号,
'商品主体' as 子项备注,
0 as 可生产数量,
0 as 子项BOM需求数量,
'审核' as 审核状态,
'使用' as 使用状态,
'' as 商品备注,
'' as 仓库
FROM dbo.ICBOM INNER JOIN dbo.t_ICItem ON dbo.ICBOM.FItemID = dbo.t_ICItem.FItemID inner join dbo.t_MeasureUnit on dbo.ICBOM.FUnitID=dbo.t_MeasureUnit.FMeasureUnitID
LEFT OUTER JOIN (SELECT dbo.ICInventory.fitemid, SUM(dbo.ICInventory.Fqty) AS ICInventoryFqty From dbo.ICInventory
GROUP BY fitemid) b ON dbo.t_ICItem.FItemID = b.fitemid

第四步:将第二步,第三步数据整合到一起

第五步:完成数据整合,打印输出报表

编程实现核心代码(用VB6完成):
'进行数据提取并二次处理
Private Sub dcButton2_Click()
On Error GoTo ErrH234
  Dim sSQL As String '存储SQL语句
  Dim sDatabase As String '数据来源库
  Dim Rs As New ADODB.Recordset
 
  If MsgBox("数据整理过程需要一段时间,请耐心等待,是否继续?", vbYesNo + 32) = vbNo Then Exit Sub

  '将bom单子项数据规则 引入到临时数据库中
  'a_temp 为临时数据库专为二次查询用
  'vICBOM 为临时表  存储bom单数据
  sDatabase = "ais20100130173207" '生产库

  bbGrid.Visible = False '为了加快写表速度
  '临时表存在 删除
  Conn.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vICBOM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " _
               & " drop table a_temp.[dbo].[vICBOM]"
 
  '重新提取数据
  sSQL = "SELECT dbo.vICBOM.FParentID AS 组名, dbo.vICBOM.FBOMNumber AS BOM单号,dbo.vICBOM.FName AS 商品名称, dbo.vICBOM.FInterID AS BOM内码,"
  sSQL = sSQL & " dbo.vICBOM.FNumber AS 商品编码, dbo.vICBOM.Fmodel AS 商品规格,dbo.vICBOM.FStatus AS 审核状态, dbo.vICBOM.FUseStatus AS 使用状态,"
  sSQL = sSQL & " dbo.vICBOM.FUnitName AS 商品单位, dbo.vICBOM.FQty AS 商品数量,dbo.vICBOM.FNote AS 商品备注, dbo.vICBOM.FEntryID AS 子项顺序号,"
  sSQL = sSQL & " dbo.vICBOM.FChildNumber AS 子项代码, dbo.vICBOM.FChildName AS 子项名称,dbo.vICBOM.FChildModel AS 子项规格, dbo.vICBOM.FChildUnitName AS 子项单位,"
  sSQL = sSQL & " dbo.vICBOM.FChildQty AS 子项BOM需求数量, dbo.vICBOM.FStockID AS 仓库,dbo.vICBOM.FchildNote AS 子项备注, ISNULL(b.ICInventoryFqty, 0) AS 子项库存,"
  sSQL = sSQL & " ISNULL(ROUND(b.ICInventoryFqty / dbo.vICBOM.FChildQty * dbo.vICBOM.FQty, 1), 0) AS 可生产数量"
  sSQL = sSQL & " into vICBOM FROM dbo.vICBOM INNER JOIN dbo.t_ICItem ON"
  sSQL = sSQL & " dbo.vICBOM.FChildNumber = dbo.t_ICItem.FNumber LEFT OUTER JOIN"
  sSQL = sSQL & " (SELECT dbo.ICInventory.fitemid, SUM(dbo.ICInventory.Fqty) AS ICInventoryFqty From dbo.ICInventory GROUP BY fitemid) b ON dbo.t_ICItem.FItemID = b.fitemid"
  '加入来源数据库前缀
  sSQL = Replace(sSQL, "dbo.", sDatabase & ".dbo.")
  '提取bom数据
  Conn.Execute sSQL

  '将bom主体信息添加到表 vICBOM
  sSQL = "SELECT dbo.ICBOM.FBOMNumber as BOM单号,dbo.ICBOM.FInterID as BOM内码,dbo.t_ICItem.FName as 商品名称,dbo.t_ICItem.FNumber 商品编码,"
  sSQL = sSQL & " dbo.ICBOM.FQty AS 商品数量,isnull(dbo.t_ICItem.Fmodel,'') as 商品规格,dbo.t_MeasureUnit.FName AS 商品单位,dbo.t_ICItem.FNumber 子项代码,"
  sSQL = sSQL & " dbo.t_ICItem.FName as 子项名称,isnull(dbo.t_ICItem.Fmodel,'') as 子项规格,dbo.t_MeasureUnit.FName AS 子项单位,ISNULL(b.ICInventoryFqty, 0) AS 子项库存,"
  sSQL = sSQL & " 0 as 子项顺序号,'商品主体' as 子项备注,0 as 可生产数量,0 as 子项BOM需求数量,'审核' as 审核状态,'使用' as 使用状态,'' as 商品备注,"
  sSQL = sSQL & " '' as 仓库 "
  sSQL = sSQL & " FROM dbo.ICBOM INNER JOIN dbo.t_ICItem ON dbo.ICBOM.FItemID = dbo.t_ICItem.FItemID inner join dbo.t_MeasureUnit on dbo.ICBOM.FUnitID=dbo.t_MeasureUnit.FMeasureUnitID"
  sSQL = sSQL & " LEFT OUTER JOIN (SELECT dbo.ICInventory.fitemid, SUM(dbo.ICInventory.Fqty) AS ICInventoryFqty From dbo.ICInventory"
  sSQL = sSQL & " GROUP BY fitemid) b ON dbo.t_ICItem.FItemID = b.fitemid"
 
  sSQL = Replace(sSQL, "dbo.", sDatabase & ".dbo.")
  '
  Conn.Execute "insert into vICBOM(BOM单号,BOM内码,商品名称,商品编码,商品数量,商品规格,商品单位,子项代码,子项名称,子项规格,子项单位,子项库存,子项顺序号,子项备注, " _
             & " 可生产数量,子项BOM需求数量,审核状态,使用状态,商品备注,仓库) " & sSQL
  Rs.Open "select * from vICBOM order by BOM单号,子项顺序号", Conn, adOpenStatic, adLockReadOnly
  FlexGridInit bbGrid, Rs
  bbGrid.Visible = True
  View "数据提取完成!"
  wRiZhi "按成品提取库存数据"
  Exit Sub
ErrH234:
If Err.Number <> "" Then
  View Err.Description & vbNewLine & "运行错误"
End If
End Sub

顶一下
(0)
0%
踩一下
(0)
0%

------分隔线----------------------------
最新评论 查看所有评论
发表评论 查看所有评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
发布者资料
admin 查看详细资料 发送留言 加为好友 用户等级:高级会员 注册时间:2009-04-15 10:04 最后登录:2016-11-21 15:11
推荐内容