首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >金蝶K3 WISE BOM多级展开_销售成本表

金蝶K3 WISE BOM多级展开_销售成本表

作者头像
landv
发布2018-08-02 16:42:10
6690
发布2018-08-02 16:42:10
举报
文章被收录于专栏:landvlandv
/****** Object:  StoredProcedure [dbo].[pro_bobang_SaleCost]    Script Date: 07/29/2015 16:13:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
drop proc pro_bobang_SaleCost
go
create PROC [dbo].[pro_bobang_SaleCost]
@FBillNo1 varchar(50),  --销售订单号
@FBillNo2 varchar(50),  --销售订单号
@FCustID1 varchar(50),  --客户名称
@FCustID2 varchar(50),  --客户名称
@FNumber1 varchar(50),  --产品代码
@FNumber2 varchar(50),  --产品代码
@FDate1 varchar(10),    --日期
@FDate2 varchar(10)     --日期
as
--#tmp1  物料最新核销委外入库单
--#tmp2  成品成本
--#tmp3  已核销委外出库明细
--#tmp4  明细成本 
--#tmp5  BOM展开清单明细
--#tmp6  采购价格管理物料最新价格列表
begin
set nocount on
--取出所有已核销委外入库单,同一个物料只取最新日期/最大批号的委外入库单
select a1.FBillNo,a2.FItemID,a1.FDate,a2.FBatchNo into #tmp1 
from ICStockBill a1 
inner join ICStockBillEntry a2 on a1.FInterID=a2.FInterID
where exists (select a3.FDInterID from ICClientVer a3 where a3.FDInterID=a1.FInterID)
and a1.FTranType=5 
and exists 
(select fdate from 
(select MAX(fdate) as FDate,max(fbatchno) as FBatchNo,FItemID from 
(select b1.FBillNo,b2.FItemID,b1.FDate,b2.FBatchNo from ICStockBill b1 
inner join ICStockBillEntry b2 on b1.FInterID=b2.FInterID
where exists (select b3.FDInterID from ICClientVer b3 where b3.FDInterID=b1.FInterID)
) w group by FItemID
) k where k.FDate=a1.FDate and k.FItemID=a2.FItemID and k.FBatchNo=a2.FBatchNo  
) 

--1--成品成本
--1.1--销售订单有出库成本
select t1.FBillNo,t1.FInterID,t2.FEntryID,t1.FCustID,t6.FBillNo as FOutBillNo,Convert(varchar(50),'') as FWWBillNo_New,
t6.FItemID,t4.FErpClsID,t6.FBatchNo,t6.FAuxQty,t6.FAuxPrice,t6.FAuxQty*t6.FAuxPrice as FAmount,1 as CBType
into #tmp2 from SEOrder t1                                                                 
inner join SEOrderEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t4 on t2.FItemID=t4.FItemID
inner join t_Organization t5 on t5.FItemID=t1.FCustID
left join  
(select t061.FOrderInterID,t061.FOrderEntryID,t062.FBillNo,t061.FEntryID,t061.FItemID,t061.FBatchNo,t061.FAuxPrice,t061.FAuxQty 
from ICStockBillEntry t061 inner join ICStockBill t062 on t061.FInterID=t062.FInterID where t062.FTranType=21 and t061.FAuxPrice<>0
) t6 on t2.FInterID=t6.FOrderInterID and t2.FEntryID=t6.FOrderEntryID
where (1=1) and t1.FStatus>0 and t1.FCancellation=0
and t1.FBillNo >= @FBillNo1 
and t1.FBillNo <= case when @FBillNo2='' then (select MAX(FBillNo) from SEOrder) else @FBillNo2 end 
and t4.FNumber >= @FNumber1
and t4.FNumber <= case when @FNumber2='' then (select MAX(FNumber) from t_ICItem) else @FNumber2 end 
and t5.FNumber >= @FCustID1
and t5.FNumber <= case when @FCustID2='' then (select MAX(FNumber) from t_Organization) else @FCustID2 end
and t1.FDate >= @FDate1
and t1.FDate <= case when @FDate2='' then '2100-1-1' else @FDate2 end
and t6.FBillNo is not null

--1.2--销售订单没有出库成本,取最新的已核销委外入库单成本,明细成本取对应核销的委外出库成本
--select * into #tmp1 from vw_bobang_WWRuKu_YiHeXiao_New
insert into #tmp2
select t1.FBillNo,t1.FInterID,t2.FEntryID,t1.FCustID,t6.FBillNo as FOutBillNo,t7.fbillno as FWWBillNo_New,
t8.FItemID,t4.FErpClsID,t8.FBatchNo,t2.FAuxQty,t8.FAuxPrice,t2.FAuxQty*t8.FAuxPrice as FAmount,2 as CBType
from SEOrder t1                                                                 
inner join SEOrderEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t4 on t2.FItemID=t4.FItemID
inner join t_Organization t5 on t5.FItemID=t1.FCustID
left join  
(select t061.FOrderInterID,t061.FOrderEntryID,t062.FBillNo from ICStockBillEntry t061 
inner join ICStockBill t062 on t061.FInterID=t062.FInterID where t062.FTranType=21 and t061.FAuxPrice<>0
) t6 on t2.FInterID=t6.FOrderInterID and t2.FEntryID=t6.FOrderEntryID
inner join #tmp1 t7 on t7.fitemid =t2.FItemID
inner join 
(
select t081.FItemID,t081.FBatchNo,t082.FBillNo,t081.FAuxPrice from ICStockBillEntry t081
inner join ICStockBill t082 on t081.FInterID=t082.FInterID where t082.FTranType=5  
) t8 on t8.FBillNo=t7.FBillNo and t8.FItemID=t7.FItemID and t8.FBatchNo=t7.FBatchNo
where (1=1)  and t1.FStatus>0 and t1.FCancellation=0
and t1.FBillNo >= @FBillNo1 
and t1.FBillNo <= case when @FBillNo2='' then (select MAX(FBillNo) from SEOrder) else @FBillNo2 end 
and t4.FNumber >= @FNumber1
and t4.FNumber <= case when @FNumber2='' then (select MAX(FNumber) from t_ICItem) else @FNumber2 end 
and t5.FNumber >= @FCustID1
and t5.FNumber <= case when @FCustID2='' then (select MAX(FNumber) from t_Organization) else @FCustID2 end
and t1.FDate >= @FDate1
and t1.FDate <= case when @FDate2='' then '2100-1-1' else @FDate2 end
and t6.FBillNo is null

--1.3--销售订单没有出库成本,没有核销毁委外出库成本,取BOM成本(最新采购价)
insert into #tmp2
select t1.FBillNo,t1.FInterID,t2.FEntryID,t1.FCustID,t6.FBillNo as FOutBillNo,t7.fbillno as FWWBillNo_New,
t2.FItemID,t4.FErpClsID,t7.FBatchNo,t2.FAuxQty,0 as FAuxPrice,0 as FAmount,3 as CBType
from SEOrder t1                                                                 
inner join SEOrderEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t4 on t2.FItemID=t4.FItemID
inner join t_Organization t5 on t5.FItemID=t1.FCustID
left join  
(select t061.FOrderInterID,t061.FOrderEntryID,t062.FBillNo from ICStockBillEntry t061 
inner join ICStockBill t062 on t061.FInterID=t062.FInterID where t062.FTranType=21 and t061.FAuxPrice<>0
) t6 on t2.FInterID=t6.FOrderInterID and t2.FEntryID=t6.FOrderEntryID
left join #tmp1 t7 on t7.fitemid =t2.FItemID
where (1=1)  and t1.FStatus>0 and t1.FCancellation=0
and t1.FBillNo >= @FBillNo1 
and t1.FBillNo <= case when @FBillNo2='' then (select MAX(FBillNo) from SEOrder) else @FBillNo2 end 
and t4.FNumber >= @FNumber1
and t4.FNumber <= case when @FNumber2='' then (select MAX(FNumber) from t_ICItem) else @FNumber2 end 
and t5.FNumber >= @FCustID1
and t5.FNumber <= case when @FCustID2='' then (select MAX(FNumber) from t_Organization) else @FCustID2 end
and t1.FDate >= @FDate1
and t1.FDate <= case when @FDate2='' then '2100-1-1' else @FDate2 end
and t6.FBillNo is null
and t7.FBillNo is null
--select * from #tmp2  order by FBillNo 

--已核销委外出库明细
select t022.FInterID,t022.FEntryID,t022.FItemID,t022.FBatchNo,t023.FDInterID,t023.FDEntryID,t023.FSInterID,t023.FSEntryID,t023.FQty,t023.FAmount into #tmp3 from ICStockBill t021 
inner join ICStockBillEntry t022 on t021.FInterID=t022.FInterID
inner join ICClientVer t023 on t022.FInterID=t023.FDInterID and t022.FEntryID=t023.FDEntryID
where t021.FTranType=5

--2--明细成本
--2.1--出库明细成本,产品批号有委外入库,取对应核销的委外出库成本(初始化批号没有明细成本)
select t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FItemID,t1.FBatchNo,t2.FDInterID,t2.FDEntryID,t3.FItemID as FSubItemID,
sum(t2.FQty)/t4.FQty as FAuxQty,sum(t2.FAmount)/sum(t2.FQty) as FAuxPrice,t1.FAuxQty as FSaleQty,
0 as FAmount,t1.CBType
into #tmp4 from #tmp2 t1 
left join #tmp3 t2 on t1.FItemID=t2.FItemID and t1.FBatchNo=t2.FBatchNo
left join ICStockBillEntry t3 on t3.FInterID=t2.FSInterID and t3.FEntryID=t2.FSEntryID
left join ICStockBillEntry t4 on t4.FInterID=t2.FDInterID and t4.FEntryID=t2.FDEntryID
where t1.CBType=1 and t1.FErpClsID=3  --有出库成本,物料委外加工属性
and t2.FSInterID is not null  --有委外入库和委外出库
group by t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FItemID,t1.FBatchNo,t2.FDInterID,t2.FDEntryID,t3.FItemID,t4.FQty,t1.FAuxQty,t1.CBType

--2.2--入库明细成本
insert into #tmp4
select t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FItemID,t1.FBatchNo,t2.FDInterID,t2.FDEntryID,t3.FItemID as FSubItemID,
sum(t2.FQty)/t4.FQty as FAuxQty,sum(t2.FAmount)/sum(t2.FQty) as FAuxPrice,t1.FAuxQty as FSaleQty,
0 as FAmount,t1.CBType
from #tmp2 t1
inner join #tmp3 t2 on t1.FItemID=t2.FItemID and t1.FBatchNo=t2.FBatchNo
left join ICStockBillEntry t3 on t3.FInterID=t2.FSInterID and t3.FEntryID=t2.FSEntryID
left join ICStockBillEntry t4 on t4.FInterID=t2.FDInterID and t4.FEntryID=t2.FDEntryID
where t1.CBType=2 and t1.FErpClsID=3  --有入库成本,物料委外加工属性
group by t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FItemID,t1.FBatchNo,t2.FDInterID,t2.FDEntryID,t3.FItemID,t4.FQty,t1.FAuxQty,t1.CBType

--2.3--BOM明细成本
--2.3.1--递归所有有BOM成本的料号的明细材料
  ;with cte as (
  select c0.Finterid,convert(varchar(50),'') as fpbomnumber,c0.Fbomnumber AS FCbomnumber,
  c0.FBOMNumber as FPPBOMNumber,c0.fitemid,c0.FItemID as FPItemID,0 as fpinterid,
  convert(varchar(500),RIGHT('000000'+CONVERT(varchar(10),c0.Finterid),6)) as code,c0.FAUXQTY  
  from ICBOM c0
  inner join --有BOM成本的所有物料,仅筛选有BOM单的料号
  (
        select c011.FItemID,c012.FBOMNumber
        from #tmp2 c011
        inner join   
        (
        select c0011.FBOMNumber,c0012.FItemID  from ICBOM c0011
        inner join ICBOM c0012 on c0011.FInterID=c0012.FInterID
        ) c012 on c011.FItemID=c012.FItemID
        where c011.CBType=3 and c011.FErpClsID=3  --有BOM成本,物料委外加工属性
        group by c011.FItemID,c012.FBOMNumber  
   )c1 on c0.FBOMNumber=c1.FBOMNumber where c0.FUseStatus=1072 and c0.FStatus>0
  union all
  select a.finterid,convert(varchar(50),c.FCbomnumber) as fpbomnumber,a.fbomnumber as fcbomnumber,
  c.FPPBOMNumber,a.fitemid,c.FPItemID,a.fpinterid,convert(varchar(500),
  c.code+RIGHT('000000'+convert(varchar(10),a.finterid),6)) as code,a.FAuxQty
  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.FStatus>0 and t2.FUseStatus=1072
  --没有下一级
  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 finterid,fpbomnumber,fcbomnumber,FPPBOMNumber,fitemid,FAUXQTY,FPItemID,fpinterid,code 
  into #tmp5 from cte where FInterID=0 order by code  --finterid=0  取最终明细,不显示半成品
  OPTION (MAXRECURSION 0)
  ;
--2.3.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 #tmp6 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
  
--2.3.3--列出BOM明细成本,取采购价格的最新价格
insert into #tmp4
select t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FItemID,t1.FBatchNo,0 as FDInterID,0 as FDEntryID,t3.FItemID as FSubItemID,t3.FAUXQTY,
t4.FPrice as FAuxPrice,t1.FAuxQty as FSaleQty,isnull(t4.FPrice,0)*isnull(t1.FAuxQty,0)*isnull(t3.FAuxQty,0) as FAmount,t1.CBType
from #tmp2 t1
inner join   --过滤有BOM的数据
(
select t021.FBOMNumber,t022.FItemID  from ICBOM t021
inner join ICBOM t022 on t021.FInterID=t022.FInterID
) t2 on t1.FItemID=t2.FItemID
inner join #tmp5 t3 on t2.FItemID=t3.FPItemID and t2.FBOMNumber=t3.FPPBOMNumber and t3.fpinterid<>0
left join #tmp6 t4 on t3.FItemID=t4.FItemID
where t1.CBType=3 and t1.FErpClsID=3 --有BOM成本,物料委外加工属性

--3--更新BOM成品成本
update t1 set t1.FAmount=t2.FAmount,t1.FAuxPrice=t2.FAmount/t1.FAuxQty from #tmp2 t1
inner join
(
select FBillNo,FEntryID,FItemID,CBType,sum(FAmount) as FAmount from #tmp4 where cbtype=3
group by FBillNo,FEntryID,FItemID,CBType
) t2 on t1.FBillNo=t2.FBillNo and t1.FEntryID=t2.FEntryID and t1.FItemID=t2.FItemID
where t1.CBType=3

--4--合并报表数据
select k1.FBillNo,k1.FCustName,k1.FNumber,k1.FName,k1.FModel,k1.FUnitName,k1.FClsName,k1.FBatchNo,k1.FSubNumber,k1.FSubName,k1.FSubModel,k1.FSubUnitName,k1.FAuxQty,k1.FPrice,k1.FAuxPrice,k1.FSaleQty,k1.FAmount,
case k1.CBType when 1 then '出库成本' when 2 then '入库成本' when 3 then 'BOM成本' end as FCBType
from
(
--总成本
select t1.FBillNo,t1.FInterID,t1.FEntryID,t1.FCustID,t12.FName as FCustName,t1.FItemID,t13.FNumber,t13.FName,t13.FModel,t14.FName as FUnitName,t1.FErpClsID,t15.FName as FClsName,t1.FBatchNo,t1.FBatchNo as FBatchNo1,null as FSubItemID,
null as FSubNumber,null as FSubName,null as FSubModel,null as FSubUnitName,1 as FAuxQty,t1.FAuxPrice as FPrice,t1.FAuxPrice,t1.FAuxQty as FSaleQty, t1.FAmount,t1.CBType
from #tmp2 t1
inner join t_Organization t12 on t1.FCustID=t12.FItemID
inner join t_ICItem t13 on t1.FItemID=t13.FItemID
inner join t_MeasureUnit t14 on t13.FUnitID=t14.FItemID
left join (select FInterID,FName from t_SubMessage where FTypeID=210) t15 on t1.FErpClsID=t15.FInterID
--明细成本
union all
select null,t2.FInterID,t2.FEntryID,null,null,t2.FItemID,null,null,null,null,null,null,null,t2.FBatchNo,t2.FSubItemID,t22.FNumber,t22.FName,t22.FModel,t23.FName as FSubUnitName,t2.FAuxQty,t2.FAuxPrice as FPrice,t2.FAuxQty*t2.FAuxPrice,t2.FSaleQty*t2.FAuxQty,t2.FAuxPrice*t2.FSaleQty*t2.FAuxQty,null 
from #tmp4 t2 
inner join t_ICItem t22 on t22.FItemID=t2.FSubItemID
inner join t_MeasureUnit t23 on t22.FUnitID=t23.FItemID
--小计(销售订单成品大于1个,才算小计)
union all
select '小计',t4.FInterID,9999,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,SUM(t4.FAuxQty),SUM(t4.FAmount),null
from #tmp2 t4 group by t4.FBillNo,t4.FInterID having count(*) > 1
--总计
union all
select '总计',MAX(t4.FInterID)+10,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,SUM(t4.FAuxQty),SUM(t4.FAmount),null
from #tmp2 t4
)k1
--where k1.FInterID=31278
--where k1.CBType=1 and k1.FPrice=0
--where k1.CBType=3
order by k1.FInterID,k1.FEntryID,k1.FItemID,k1.FBatchNo1,k1.FCustID desc
 
drop table #tmp6  
drop table #tmp5
drop table #tmp4
drop table #tmp3
drop table #tmp2
drop table #tmp1
--and t1.FBillNo='15010107649997930580'
--order by t1.FBillNo
set nocount off
end 

-- exec pro_bobang_SaleCost 'SEORD000062','SEORD000062','','','','','',''

--exec pro_bobang_SaleCost '*FBillNo*','#FBillNo#','*CustNo*','#CustNo#','*ItemNo*','#ItemNo#','********','########'
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2018-07-06 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
腾讯云 BI
腾讯云 BI(Business Intelligence,BI)提供从数据源接入、数据建模到数据可视化分析全流程的BI能力,帮助经营者快速获取决策数据依据。系统采用敏捷自助式设计,使用者仅需通过简单拖拽即可完成原本复杂的报表开发过程,并支持报表的分享、推送等企业协作场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档