/****** 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(''+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(''+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#','********','########'