汇总委外入库单匹配的领料单

1. –先抓取委外入库单
2. –后抓取委外订单的相关信息
3. –最后抓紧委外出库的领料单
4. –最后合并相关的信息
5. –报表显示

USE [AIS20101130170856]
GO
/****** Object: StoredProcedure [dbo].[proc_wwdd] Script Date: 04/04/2019 15:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_wwdd]
--委外订单统计相应的领料单
@stdate datetime,
@enddate datetime
as
set nocount on
begin

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#ww_dingdan_fx'))
begin
drop table #ww_dingdan_fx
end
--抓数据
select distinct t.FOrderBillNo ,fbillno,t.MFITEMID,t.MFOrderInterID,t.MFOrderEntryID
into #ww_dingdan_fx
from 
(select distinct FOrderBillNo,FOrderEntryID MFOrderEntryID,
FOrderInterID MFOrderInterID ,fitemid MFITEMID
from ICStockBill a inner join ICStockBillEntry b on a.finterid=b.finterid
where FTranType=5 and a.fdate>=@stdate and a.fdate<=@enddate) t
inner join (
select distinct FOrderEntryID zFOrderEntryID,FOrderInterID zFOrderInterID ,fbillno
from ICStockBill a inner join ICStockBillEntry b on a.finterid=b.finterid
where FTranType=28 and fdate>DATEADD(dd,-360,getdate())) s  
on t.MFOrderInterID=zFOrderInterID and t.MFOrderEntryID=s.zFOrderEntryID
order by t.FOrderBillNo,s.fbillno

--获取领料单单号
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#ww_dd_fx_jg'))
begin
drop table #ww_dd_fx_jg
end
select t.FOrderBillNo 委外订单号,t.MFOrderInterID,t.MFOrderEntryID,
left(t.ddlist,LEN(t.ddlist)-1) 对应的领料单  
into #ww_dd_fx_jg
from (
select b.FOrderBillNo,b.MFOrderInterID,b.MFOrderEntryID,(select FBillNo+'/' from #ww_dingdan_fx 
a where a.MFOrderInterID=b.MFOrderInterID and a.MFOrderEntryID=b.MFOrderEntryID for XML PATH('')) as ddlist
 from #ww_dingdan_fx b group by b.FOrderBillNo, b.MFOrderInterID,b.MFOrderEntryID) t 
 
 
-- select * from #ww_dd_fx_jg
 
--委外订单在单量

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#ww_zd_qty'))
begin
drop table #ww_zd_qty
end 
select b.FInterID,b.FEntryID,b.FAuxQty fqty,
ISNULL(b.FAuxQty, 0) - ISNULL(b.FAuxCommitQty, 0) zdtqy ,a.FSupplyID,b.FUnitID
into #ww_zd_qty
 from dbo.ICSubContract AS a INNER JOIN
            dbo.ICSubContractEntry AS b 
            ON a.FInterID = b.FInterID
 WHERE (a.FCancellation = 0) and (a.FInvStyle = 14190)
 
-- select * from #ww_zd_qty
 
 IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#ww_ruku_qty'))
begin
drop table #ww_ruku_qty
end 
 
select a.FDate,a.FBillNo, FOrderBillNo,FOrderEntryID MFOrderEntryID,
FOrderInterID MFOrderInterID ,fitemid MFITEMID,b.FQty
into #ww_ruku_qty
from ICStockBill a inner join ICStockBillEntry b on a.finterid=b.finterid
where FTranType=5 and a.fdate>=@stdate and a.fdate<=@enddate
order by FOrderInterID,FOrderEntryID

select a.FDate,d.FName gys_name,a.FBillNo,c.FNumber,c.FName wl_name,
c.FModel,e.fname dw,a.FQty ruk_qty,f.qty_by_month ,g.对应的领料单,a.FOrderBillNo,b.fqty ww_qty,b.zdtqy
from 
#ww_ruku_qty a
left join #ww_zd_qty b on a.MFOrderInterID=b.FInterID and a.MFOrderEntryID=b.FEntryID
left join dbo.t_ICItem c on a.MFITEMID=c.FItemID
left join t_Item d on b.FSupplyID=d.FItemID
left join t_Item e on b.FUnitID=e.FItemID
left join 
(select s.MFOrderInterID,s.MFOrderEntryID,sum(s.FQty) qty_by_month from #ww_ruku_qty s group by
s.MFOrderInterID,s.MFOrderEntryID) f
on a.MFOrderInterID=f.MFOrderInterID and a.MFOrderEntryID=f.MFOrderEntryID
left join #ww_dd_fx_jg g on a.MFOrderInterID=g.MFOrderInterID and a.MFOrderEntryID=g.MFOrderEntryID
 
 
 
end
--exec proc_wwdd @stdate='2019-03-01',@enddate='2019-03-31'