--参考表--
With RM(MPartNo,MPartName,UM,MSupplier,Stock,MType)
as
(
select
p_partno MPartNo,p_partname MPartName,p_unit UM,p_supplier MSupplier,
Stock,p_type MType
from p_partno_rm
), --计算期初库存----
RMBeforeQty_tmp(MPartNo, BefQty)
as
(
--电线部分统计
select MPartNo,
Sum(SumInQty)-Sum(SumOutQty)BefQty
From(
Select partno MPartNo,
Sum(isnull(inQty,0)) * 1000 SumInQty,
0 SumOutQty From Storage_Cable
Where Indate <'$BDate'
group by partno
--如果电线发料信息在storage_fixbin中出现,调整下面代码
Union all
Select partno MPartNo,
0 SumInQty,
Sum(isnull(outQty,0)) * 1000 SumOutQty From Storage_Cable
Where outdate <'$BDate'
group by partno
) as t_cable_befQty
group by MPartNo
Union all
--其他材料-----------------------
select MPartNo,
Sum(SumInQty)-Sum(SumOutQty)BefQty
From(
select partno MPartNo,
sum(isnull(inqty,0)) SumInQty,
0 SumOutQty
From Storage_RM
Where TicketType <> '归还'
And Indate <'$BDate'
Group by partno
Union all
--这里需要考虑排除电线
select partno MPartNo,
0 SumInQty,
sum(isnull(outqty,0)) SumOutQty
From Storage_fixBin
Where ptype = '发料' And TicketStatus <> '借用'
And outdate <'$BDate'
Group by partno
) as t_rm_befQty
group by MPartNo
), --再次汇总求期初库存
RMBefQty(MPartNo,BefQty)
as
(
Select MPartNo, Sum(BefQty) BefQty From RMBeforeQty_tmp
group by MPartNo
), --电线入库统计
CableIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(
Select partno MPartNo,
Sum(isnull(inQty,0)) * 1000 ProductionIn,
0 BuyIn, 0 InventoryIn, 0 OtherIn, 0 InventoryProfitIn, 0 ReturnIn
From Storage_Cable
Where Indate >='$BDate' and indate <='$EDate 23:59:59'
group by partno
),--电线出库统计
CableOut (MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
Select partno MPartNo,
Sum(isnull(outQty,0)) * 1000 PlanOut,
0 SellOut,0 OutPlanOut,0 InventoryLossesOut,0 BadMReturnOut,0 BorrowOut,0 OtherOut
From Storage_Cable
Where outdate >='$BDate' and outdate <='$EDate 23:59:59'
group by partno
),
--材料入库
RMIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(
--其他材料---
Select MPartNo,
Sum(ProductionIn) ProductionIn,
Sum(BuyIn) BuyIn,
Sum(InventoryIn)InventoryIn,
Sum(OtherIn)OtherIn,
Sum(InventoryProfitIn) InventoryProfitIn,
Sum(ReturnIn)ReturnIn
From(
Select partno MPartNo,
Case When tickettype='生产缴库' Then isnull(inQty,0)
Else 0 End as ProductionIn,
Case When tickettype='采购入库' Then isnull(inQty,0)
Else 0 End as BuyIn,
Case When tickettype='盘存入库' Then isnull(inQty,0)
Else 0 End as InventoryIn,
Case When tickettype='其它入库' Then isnull(inQty,0)
Else 0 End as OtherIn,
Case When tickettype='盘盈入库' Then isnull(inQty,0)
Else 0 End as InventoryProfitIn,
Case When tickettype='归还' Then isnull(inQty,0)
Else 0 End as ReturnIn
From Storage_RM
Where
Indate >='$BDate' and indate <='$EDate 23:59:59'
)as t
group by MPartNO
), --材料出库
RMOut(MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
Select MPartNo,
Sum(PlanOut) PlanOut,
Sum(SellOut) SellOut,
Sum(OutPlanOut) OutPlanOut,
Sum(InventoryLossesOut) InventoryLossesOut,
Sum(BadMReturnOut) BadMReturnOut,
Sum(BorrowOut) BorrowOut,
Sum(OtherOut) OtherOut
From(
Select partno MPartNo,
Case When ticketstatus='生产性领料' Then isnull(OutQty,0)
Else 0 End as PlanOut,
Case When ticketstatus='销售出库' Then isnull(OutQty,0)
Else 0 End as SellOut,
Case When ticketstatus='计划外出库' Then isnull(OutQty,0)
Else 0 End as OutPlanOut,
Case When ticketstatus='盘亏出库' Then isnull(OutQty,0)
Else 0 End as InventoryLossesOut,
Case When ticketstatus='废料退货' Then -isnull(InQty,0)
Else 0 End as BadMReturnOut,
Case When ticketstatus='借用' Then isnull(OutQty,0)
Else 0 End as BorrowOut,
Case When ticketstatus='其它出库' Then isnull(OutQty,0)
Else 0 End as OtherOut
From Storage_Fixbin
Where ptype = '发料'
And Outdate >='$BDate' and Outdate <='$EDate 23:59:59'
) as t
group by MPartNo
),--入库集合
SetIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(
Select * From CableIn
Union
Select * From RMIn
),--出库集合
SetOut(MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
Select * From CableOut
Union
Select * From RMOut
)
Select *,
ProductionIn + BuyIn+ InventoryIn +OtherIn +InventoryProfitIn As SumIn,
PlanOut + SellOut + OutPlanOut + InventoryLossesOut +BadMReturnOut +OtherOut As SumOut,
BefQty+ProductionIn + BuyIn+ InventoryIn +OtherIn +InventoryProfitIn - (PlanOut + SellOut + OutPlanOut + InventoryLossesOut +BadMReturnOut +OtherOut)As Inventory
From
(
select RM.*,BefQty,
isnull(ProductionIn,0) ProductionIn,
isnull(BuyIn,0) BuyIn,
isnull(InventoryIn,0) InventoryIn,
isnull(OtherIn,0) OtherIn,
isnull(InventoryProfitIn,0) InventoryProfitIn,
isnull(ReturnIn,0) ReturnIn,
isnull(PlanOut,0) PlanOut,
isnull(SellOut,0) SellOut,
isnull(OutPlanOut,0) OutPlanOut,
isnull(InventoryLossesOut,0) InventoryLossesOut,
isnull(BadMReturnOut,0) BadMReturnOut,
isnull(BorrowOut,0) BorrowOut,
isnull(OtherOut,0) OtherOut
from
RM left join RMBefQty On RM.MPartNo=RMBefQty.MPartNo
left join SetIn On RM.MPartNo=SetIn.MPartNo
left join SetOut On RM.MPartNo=SetOut.MPartNo
) as t
Where 1=1
$Condition