金蝶K3 WISE BOM多级展开_BOM成本表

/****** Object:  StoredProcedure [dbo].[pro_bobang_BOMCost]    Script Date: 07/29/2015 16:09:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
drop proc pro_bobang_BOMCost
GO
create PROC [dbo].[pro_bobang_BOMCost]
@FBomNumber1 varchar(50),  --bom单号
@FBomNumber2 varchar(50)   --bom单号
as
begin
SET ANSI_WARNINGS OFF
set nocount on


--1.--业务员查找某个BOM单,包含所有BOM(已使用,未使用,已审核,未审核)
  with cte as (
  select convert(varchar(100),'') as cen,Finterid,FBOMNumber as fppbomnumber,convert(varchar(50),'') as fpbomnumber,Fbomnumber AS FCbomnumber,fitemid,fitemid as fpitemid,0 as fpinterid,convert(decimal(18,4),1) as FBomQty,convert(varchar(500),RIGHT('000000'+CONVERT(varchar(10),Finterid),6)) as code  from ICBOM 
  where (1=1)
  and FBOMNumber >= @FBomNumber1
  and FBOMNumber <= case when @FBomNumber2='' then (select MAX(FBOMNumber) from ICBOM) else @FBomNumber2 end
  union all
  select convert(varchar(100),cen+'------'),a.finterid,c.fppbomnumber,convert(varchar(50),c.FCbomnumber) as fpbomnumber,a.fbomnumber as fcbomnumber,a.fitemid,c.fpitemid,a.fpinterid,convert(decimal(18,4),a.FAuxQty) as FBomQty,convert(varchar(500),c.code+RIGHT('000000'+convert(varchar(10),a.finterid),6)) as code
  from ( 
  select t1.finterid as fpinterid,t2.FInterID,t1.FItemID,t2.FBOMNumber,t1.FAuxQty 
  from  ICBOMChild t1 inner join ICBOM t2 on t1.FItemID=t2.FItemID
  where FParentID=1038
  union all 
  select t1.finterid as fpinterid,0,t1.fitemid,'',t1.FAuxQty from ICBOMChild t1 where not exists (select * from ICBOM where FItemID=t1.FItemID)) a 
  inner join cte c on a.fpinterid=c.FInterID
  )
  select  cen,finterid,fppbomnumber,fpbomnumber,fcbomnumber,fitemid,fpitemid,fpinterid,FBomQty,code into #tmp1 from cte order by code
  OPTION (MAXRECURSION 0)

--2.--取采购价格管理物料最新日期的价格(已审核,可使用)
SELECT distinct u1.FSupID,u1.FItemID,case when u1.fcyid=1 then u1.FPrice*t3.FExchangeRate/(1+v1.FValueAddRate/100) else u1.FPrice*t3.FExchangeRate end as FPrice,
t3.FName AS FCyName,u1.FQuoteTime,u1.FDisableDate
into #tmp2 FROM t_SupplyEntry  u1 
     INNER JOIN t_ICItem t1 ON u1.FItemID=t1.FItemID
     INNER JOIN t_Supply u2 ON u1.FSupID=u2.FSupID AND u1.FItemID=u2.FItemID AND u1.FPType=u2.FPType
     INNER JOIN t_Currency t3 ON t3.FCurrencyID=u1.FCyID
     INNER JOIN t_Currency u3 ON u3.FCurrencyID=u2.FCurrencyID
     INNER JOIN t_Supplier v1 ON u1.FSupID=v1.FItemID
     inner join 
     (
     select MAX(fquotetime) as fquotetime,FItemID from t_SupplyEntry where FCheckerID>0 group by FItemID
     ) bb on bb.FItemID=u1.FItemID and bb.fquotetime=u1.FQuoteTime
 WHERE t1.FErpClsID not in (6,8) and u1.FCheckerID>0 and u1.FUsed=1

--3.--列出BOM明细成本,取采购价格的最新价格
select t1.cen+t1.code as FTree,t1.cen,t1.code,t1.fppbomnumber,t1.fpbomnumber,t1.fcbomnumber,t1.FItemID,t1.FBomQty,
t2.FSupID,t2.FPrice as FAuxPrice,t1.FBomQty*t2.FPrice as FAmount,case when FCbomnumber<>'' then t1.FBomQty*t2.FPrice end as FJGAmount 
into #tmp3 from #tmp1 t1
left join #tmp2 t2 on t1.FItemID=t2.FItemID
left join t_ICItem t3 on t1.FItemID=t3.FItemID
order by t1.code,t3.FNumber


--4.--循环计算中间件成本
---这部分部署会出错,先注释,部署成功,再解除注释更新存储过程
declare @cb decimal(18,4),@fbom varchar(50),@fppbom varchar(50)
declare auth_cur cursor for
select fppbomnumber,fcbomnumber from #tmp3 where FAuxPrice is null and fcbomnumber<>'' and cen<>''  order by cen desc
open auth_cur
fetch next from auth_cur into @fppbom,@fbom
while(@@fetch_status=0)
begin
  set @cb=0
  select @cb=isnull(SUM(FAmount),0) from #tmp3 where fpbomnumber=@fbom and fppbomnumber=@fppbom
  UPDATE #tmp3 set FAmount=isnull(FAmount,0)+@cb,FAuxPrice=isnull(FAuxPrice,0)+@cb/FBomQty where FCbomnumber=@fbom and fppbomnumber=@fppbom
  fetch next from auth_cur into @fppbom,@fbom
end
close auth_cur
deallocate auth_cur
----这部分部署会出错,先注释,部署成功,再解除注释更新存储过程


--5.--循环计算成品件成本
---这部分部署会出错,先注释,部署成功,再解除注释更新存储过程
declare @cb1 decimal(18,4),@fbom1 varchar(50),@fppbom1 varchar(50)
declare auth_cur cursor for
select fppbomnumber,fcbomnumber from #tmp3 where fcbomnumber<>'' and cen=''  order by cen desc
open auth_cur
fetch next from auth_cur into @fppbom1,@fbom1
while(@@fetch_status=0)
begin
  set @cb1=0
  select @cb1=isnull(SUM(FAmount),0) from #tmp3 where fpbomnumber=@fbom1 and fppbomnumber=@fppbom1
  UPDATE #tmp3 set FAmount=isnull(FAmount,0)+@cb1,FAuxPrice=isnull(FAmount,0)+@cb1/FBomQty where FCbomnumber=@fbom1 and fppbomnumber=@fppbom1
  fetch next from auth_cur into @fppbom1,@fbom1
end
close auth_cur
deallocate auth_cur
----这部分部署会出错,先注释,部署成功,再解除注释更新存储过程

select t1.FTree,case when t1.fpbomnumber='' then t1.FCbomnumber end as fpbomnumber,t2.FNumber,t2.FName,t2.FModel,t3.FName as FUnitName,t1.FBomQty,t1.FAuxPrice,t1.FAmount,t1.FJGAmount,t4.FName as FSupName from #tmp3 t1
inner join t_ICItem t2 on t2.FItemID=t1.FItemID
inner join t_MeasureUnit t3 on t2.FUnitID=t3.FItemID
left join t_Supplier t4 on t1.FSupID=t4.FItemID
order by t1.code


drop table #tmp1
drop table #tmp2
drop table #tmp3
set nocount off

end 

--exec pro_bobang_BOMCost 'BOM000032','BOM000032'
--exec pro_bobang_BOMCost '*FBomNumber*','#FBomNumber#'

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Ryan Miao

mysql连接查询

以前查询都是随便查到结果就行了,因为发现每次查询的数量都很少,当然现在也是。不过效率一直是程序员执着的追求,我就多了解下差距。 首先是多张表联合,一张模板种类c...

3717
来自专栏杨建荣的学习笔记

关于Flashback的小测试(r10笔记第15天)

对于Oracle的Flashback来说,在11g里面有了一个很细微的变化,可以说是一个很不错的福利,那就是开启闪回不需要重启数据库至mount状态下,...

3414
来自专栏腾讯云TStack专栏

五分钟聊T-SQL:数据压缩

传说中数据压缩能压缩到原始数据的1/10,但是... ... 但是至少目前为止我还没遇到过这样的情形,通常情况下能压缩到原始数据的1/5-2/5的样子。

3602
来自专栏杨建荣的学习笔记

一个慢查询报警的简单处理 (r8笔记第12天)

今天在做节后的一个基本检查的时候,发现一个不太起眼的报警,报警内容为大体为: MySQL 每秒慢查询次数超过 <1>个on xxxx 查看zabbix的监控数...

3688
来自专栏Java学习123

powerdesigner 15 如何导出sql schema

3357
来自专栏Java学习123

powerdesigner 15 如何导出sql schema

3005
来自专栏沃趣科技

ASM 翻译系列第三十三弹:REQUIRED_MIRROR_FREE_MB的含义

原作者:Bane Radulovic 译者: 陈亚军 审核: 魏兴华 DBGeeK社区联合出品 原文链接:http://asmsupportguy....

37610
来自专栏乐沙弥的世界

Oracle 基于用户管理恢复的处理

Oracle支持多种方式来管理数据文件的备份与恢复来保证数据库的可靠与完整。除了使用RMAN工具以及第三方备份与恢复工具之外,基于

622
来自专栏乐沙弥的世界

Oracle AWR管理与维护

  AWR是Automatic Workload Repository的简称,中文叫着自动工作量资料档案库。对于AWR的管理,主要是针对快照和基线的管理而言。比...

1120
来自专栏微服务生态

小程聊微服务-数据抽取那点事(一)

我们在《微服务是在双刃剑 http://www.jianshu.com/p/82ec12651d2d 》中提到了当我们将应用服务化以后,很多在单块系统中能够开展...

1492

扫码关注云+社区

领取腾讯云代金券