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

/****** 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--销售订单没有出库成本&#