修改后的收发存汇总表.sql

仓储系统

--外购--------对应物料类别为：  101----------606形式的物料编码
select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,
    (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初数量,
    (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初总额,
    (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,
    (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库总额,
    (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,
    (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库总额,
    a.stoc_amount as 库存数量,a.mate_totalprice as 库存总额
from mate_basic a 
left outer join 
(select mate_code,sum(in_amount) as in_amount,sum(in_zj) as in_zj from mate_CgRk group by mate_code) b on a.mate_code=b.mate_code
left outer join 
(select mate_code,sum(TL_amount) as TL_amount,sum(TL_zj) as TL_zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code
left outer join 
(select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Ly group by mate_code) d on a.mate_code=d.mate_code
left outer join 
(select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Bf group by mate_code) e on a.mate_code=e.mate_code
where (a.mate_class between '101' and '606') 
order by a.mate_code
union all
--自制--------对应物料类别为：  CCC
select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,
    (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初结存数量,
    (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初结存金额,
    (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,
    (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库金额,
    (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,
    (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库金额,
    a.stoc_amount as 期末结存数量,a.mate_totalprice as 期末结存金额
from mate_basic a                                                     
left outer join
(select mate_code,sum(in_amount) as in_amount,sum(in_zj) as in_zj from mate_ZzRk group by mate_code) b on a.mate_code=b.mate_code
left outer join
(select mate_code,sum(TL_amount) as TL_amount,sum(TL_zj) as TL_zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code
left outer join
(select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from mate_Ly group by mate_code) d on a.mate_code=d.mate_code
left outer join
(select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from mate_Bf group by mate_code) e on a.mate_code=e.mate_code
where a.mate_class like 'CCC'
order by a.mate_code
union all
--成品--------对应物料类别为：  PPP
select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,
                (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初结存数量,
                (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初结存金额,
                (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,
                (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库总额,
                (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,
                (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库金额,
                a.stoc_amount as 期末结存数量,a.mate_totalprice as 期末结存金额
from mate_basic a                                                     
left outer join 
(select mate_code,sum(In_Amount) as In_Amount,sum(In_Zj) as In_Zj from Product_Rk group by mate_code) b on a.mate_code=b.mate_code
left outer join 
(select mate_code,sum(TL_Amount) as TL_Amount,sum(TL_Zj) as TL_Zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code
left outer join 
(select mate_code,sum(Out_Amount) as Out_Amount,sum(Out_Zj) as Out_Zj from mate_Ck group by mate_code) d on a.mate_code=d.mate_code
left outer join 
(select mate_code,sum(Out_Amount) as Out_Amount,sum(Out_Zj) as Out_Zj from mate_Bf group by mate_code) e on a.mate_code=e.mate_code
where a.mate_class like 'PPP'
order by a.mate_code