金蝶K3 WISE BOM多级展开_物料齐套表

/****** Object:  StoredProcedure [dbo].[pro_bobang_ICItemQiTao]    Script Date: 07/29/2015 16:12:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROC [dbo].[pro_bobang_ICItemQiTao]
@FBomNumber1 varchar(50),  --bom单号
@FBomNumber2 varchar(50)   --bom单号
as
begin
set nocount on

  --1.--业务员查找某个BOM单,包含所有BOM(已使用,未使用,已审核,未审核)
  with cte as (
  select convert(varchar(100),'') as cen,Finterid,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,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  t2.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,fpbomnumber,fcbomnumber,fitemid,fpitemid,fpinterid,FBomQty,code into #tmp1 from cte order by code
  OPTION (MAXRECURSION 0)
  
  --2.--获取成品库存(因为明细材料的需求量,还要减去现有成品库存隐形消耗的材料,才能体现真实需求量)
  declare @kc decimal(18,6)
  declare @fitemid int 
  select @kc=isnull(SUM(t1.FQty),0),@fitemid=t1.FItemID from ICInventory t1 
  inner join (select FItemID from ICBOM where FBOMNumber=@FBomNumber1) t2 on t1.FItemID=t2.FItemID
  group by t1.FItemID;
  
  --3.--所有正在使用的BOM单(已使用,已审核),为了检索明细物料在哪些bom(成品)中使用,进而计算需求数量
  with cte as (
  select convert(varchar(100),'') as cen,Finterid,convert(varchar(50),FBOMNumber) 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 FParentID=1038
  union all
  select convert(varchar(100),cen+'------'),a.finterid,convert(varchar(50),c.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  fpbomnumber,fitemid,fpitemid,sum(FBomQty) as FBomQty into #tmp2 
  from cte group by fpbomnumber,FItemID,fpitemid 
  OPTION (MAXRECURSION 0)
  
  --4.--取采购价格管理物料最新日期的价格(已审核,可使用)
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 #tmp3 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

select w.cen+w2.FNumber as FTree,w2.FName,w2.FModel,w3.FName as FUnitName,w.FBomQty,
--需求量=产品预测量+销售订单量-已发货量-即时库存量-该BOM成品耗费材料库存(仅明细材料)
--w.FKCQty,w.FPRQty,w.FPOQty,w.FPPQty,w.FXSQty1,w.FXSQty2,w.FPPQty+w.FXSQty1-w.FXSQty2-w.FKCQty-(case when w.FItemID=@fitemid then 0 else @kc*w.FBomQty end) as XQSL,(case when w.FItemID=@fitemid then 0 else @kc*w.FBomQty end)
--需求量改为生产需求
--需求量=产品预测量+销售订单量-已发货量-该BOM成品库存及耗费材料库存
w.FKCQty,w.FPRQty,w.FPOQty,w.FStockQty,w.FPPQty,w.FXSQty1,w.FXSQty2,isnull(@kc*w.FBomQty,0) as cbkc,isnull(w.FPPQty,0)+isnull(w.FXSQty1,0)-isnull(w.FXSQty2,0)-isnull(@kc*w.FBomQty,0) as XQSL,w5.FName as FSupName
from
(
select t1.code,t1.cen,t1.FItemID,t1.FBomQty,isnull(t4.FKCQty,0) as FKCQty,isnull(t5.FPRQty1,0)-isnull(t6.FPRQty2,0) as FPRQty,t11.FStockQty,
isnull(t7.FPOQty1,0)-isnull(t7.FPOQty2,0) as FPOQty,isnull(sum(t8.FPPQty1*t3.FBomQty),0)-isnull(sum(t9.FPPQty2*t3.FBomQty),0) as FPPQty,
isnull(sum(t10.FXSQty1*t3.FBomQty),0) as FXSQty1,isnull(sum(t10.FXSQty2*t3.FBomQty),0) as FXSQty2,t2.FErpClsID
from #tmp1 t1
left join t_ICItem t2 on t1.FItemID=t2.FItemID
left join #tmp2 t3 on t1.FItemID=t3.FItemID
left join (select SUM(FQty) as FKCQty,FItemID from ICInventory group by FItemID) t4 on t1.FItemID=t4.FItemID
--采购申请单的数量
left join 
(
select SUM(t2.FQty) as FPRQty1,t2.FItemID from PORequest t1 
inner join  PORequestEntry t2 on t1.FInterID=t2.FInterID
where t1.FStatus>0 and t1.FCancellation=0 
group by t2.FItemID
) t5 on t1.FItemID=t5.FItemID
--PR下推采购订单或委外订单
left join
(
--采购申请单下推采购订单
select SUM(t2.FQty) as FPRQty2,t2.FItemID from POOrder t1
inner join POOrderEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t3 on t2.FItemID=t3.FItemID
where t1.FCancellation=0 and t2.FSourceTranType=70 and t3.FErpClsID<>3
group by t2.FItemID
--采购申请单下推委外订单
union all
select SUM(t2.FQty) as FPRQty2,t2.FItemID from ICSubContract t1
inner join ICSubContractEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t3 on t2.FItemID=t3.FItemID
where t1.FCancellation=0 and t2.FClassTypeID_SRC=-70 and t3.FErpClsID=3
group by t2.FItemID
) t6 on t1.FItemID=t6.FItemID
--PO
left join
(
--采购订单的数量、入库数量(外购件下推采购订单)
select SUM(t2.FQty) as FPOQty1,SUM(t2.FStockQty) as FPOQty2,t2.FItemID from POOrder t1
inner join POOrderEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t3 on t2.FItemID=t3.FItemID
where t1.FCancellation=0 and t3.FErpClsID<>3
group by t2.FItemID
--委外订单的数量、入库数量(委外加工件下推委外订单)
union all
select SUM(t2.FQty) as FPOQty1,SUM(t2.FStockQty) as FPOQty2,t2.FItemID from ICSubContract t1
inner join ICSubContractEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t3 on t2.FItemID=t3.FItemID
where t1.FCancellation=0 and t2.FClassTypeID_SRC=-70 and t3.FErpClsID=3
group by t2.FItemID
) t7 on t1.FItemID=t7.FItemID
--产品预测单的数量
left join
(
select sum(t2.FQty) as FPPQty1,t2.FItemID from PPOrder t1
inner join PPOrderEntry t2 on t1.FInterID=t2.FInterID
where t1.FStatus=1 and t1.FCancellation=0 and t2.FNeedDateEnd>GETDATE()                                                                   
group by t2.FItemID
) t8 on t3.FPItemID=t8.FItemID
--产品预测单下推销售订单的数量
left join
(
select SUM(t2.FQty) as FPPQty2,t2.FItemID from SEOrder t1
inner join SEOrderEntry t2 on t1.FInterID=t2.FInterID
where t1.FCancellation=0 and t2.FSourceTranType=87 
group by t2.FItemID
) t9 on t3.FPItemID=t9.FItemID
--销售订单的数量,销售订单下推销售出库的数量
left join 
(
select SUM(t2.FQty) as FXSQty1,SUM(t2.FStockQty) as FXSQty2,t2.FItemID from SEOrder t1
inner join SEOrderEntry t2 on t1.FInterID=t2.FInterID
where t1.FCancellation=0  
group by t2.FItemID
) t10 on t3.FPItemID=t10.FItemID
--采购入库数量,委外加工入库数量
left join 
(
select t1.FItemID,SUM(FQty) as FStockQty from ICStockBillEntry t1 inner join ICStockBill t2 on t1.FInterID=t2.FInterID
where (t2.FTranType=1 or t2.FTranType=5) and t2.FCancellation=0
group by t1.FItemID
) t11 on t1.FItemID=t11.FItemID
group by t1.code,t1.cen,t1.FItemID,t1.FBomQty,t4.FKCQty,t5.FPRQty1,t6.FPRQty2,t7.FPOQty1,t7.FPOQty2,t11.FStockQty,t2.FErpClsID
) w
inner join t_ICItem w2 on w.FItemID=w2.FItemID
inner join t_MeasureUnit w3 on w2.FUnitID=w3.FItemID
left join #tmp3 w4 on w4.FItemID=w.FItemID
left join t_Supplier w5 on w4.FSupID=w5.FItemID
order by w.code,w2.FNumber

drop table #tmp1
drop table #tmp2
set nocount off
end 
--select * from ICBOM
--select * from ICBOMChild
--exec pro_bobang_ICItemQiTao 'BOM000080','BOM000080'
--exec pro_bobang_ICItemQiTao '*FBomNumber*','#FBomNumber#'

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏码神联盟

碎片化 | 第四阶段-49-hibernate之HQL查询操作-视频

本套视频从Java基础到架构模式以及AI算法,整体视频以“碎片化”学习的模式,提供给大家 ,并配备实际项目为案例,让大家在坐车、吃饭、午休、蹲坑的时候,都可以学...

3506
来自专栏PingCAP的专栏

TiDB 助力客如云餐饮 SaaS 服务

客如云成立于 2012 年,是全球领先、 国内最大的 SaaS 系统公司。 目前面向餐饮、 零售等服务业商家, 提供软硬一体的新一代智能化前台、收银等 SaaS...

2304
来自专栏用户画像

实验3.1 简单的单表查询

熟练掌握用SELECT语句实现简单的单表查询。掌握SELECT子句、FROM子句、WHERE子句及ORDER BY 子句的用法。

1382
来自专栏PPV课数据科学社区

【学习】图解SQL连接语句

SQL 联合语句好像是基于集合的,用韦恩图来解释咋一看是很自然而然的。假设我们有下面两张表。表A在左边,表B在右边。我们给它们各四条记录。 id name ...

3689
来自专栏数据小魔方

MySQL入门学习笔记——七周数据分析师实战作业

本篇推送主要涉及SQL语言中较为复杂的子查询与函数嵌套。 虽然这个MySQL系列取名为MySQL基础入门,但是个人不打算做单个函数的用法总结,或者说简单罗列,...

4957
来自专栏java架构学习交流

面试技巧,如何通过索引说数据库优化能力,内容来自Java web轻量级开发面试教程

       如果我们需要招个Java方面的高级程序员,一方面看年限(本科3年),具体到数据库方面的技能要求,包括如下三个方面:        第一,是否会...

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

使用sql语句分析双色球(85天)

这个题目看似有点无厘头,老写技术博客,也来干点“正事",用sql语句分析一下近十年来的双色球情况,不过我肯定算不出来开奖结果,纯属个人娱乐, 个人觉得概率让一切...

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

MySQL运维的一款利器sys schema

MySQL运维中有很多的工具,就好比你进了一个杂货铺,各种运维中的小技巧几乎都有一些小工具, 走一圈发现真是琳琅满目,可见MySQL充分享受了开源社区的红利。 ...

4618
来自专栏不想当开发的产品不是好测试

数据库索引问题

群上有一位同学咨询一个问题,两个查询语句,就一个limit 11, 一个limit 12,处理的效率相差巨大,如下图:

1172
来自专栏Grace development

基于TP的每日签到代码

3293

扫码关注云+社区

领取腾讯云代金券