USE UFDATA_001_2011
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
/******获得当前现存量、在单量、预约量**********/
CREATE procedure Usp_GetInvUseableQty
@v_PartId int,
@v_Onhand UDT_QTY output,
@v_Onorder UDT_QTY output,
@v_Allocate UDT_QTY output,
@v_SoType tinyint = 0,
@v_SoDId nvarchar(30) = ''
as
declare @l_invcode nvarchar(60),
@l_free1 nvarchar(20),
@l_free2 nvarchar(20),
@l_free3 nvarchar(20),
@l_free4 nvarchar(20),
@l_free5 nvarchar(20),
@l_free6 nvarchar(20),
@l_free7 nvarchar(20),
@l_free8 nvarchar(20),
@l_free9 nvarchar(20),
@l_free10 nvarchar(20),
@l_inqty UDT_Qty,
@l_onhand UDT_Qty,
@l_inspqtyflag bit,
@l_r int,
@l_error int,
@l_police bit,
@l_trancnt int
-- select @l_trancnt = @@trancount
-- if @l_trancnt = 0
-- begin tran tran_GetInvUseableQty
select @l_invcode = Invcode, @l_free1 = Free1, @l_free2 = Free2, @l_free3 = Free3, @l_free4 = Free4, @l_free5 = Free5,
@l_free6 = Free6, @l_free7 = Free7, @l_free8 = Free8, @l_free9 = Free9, @l_free10 = Free10
from bas_part where PartId = @v_PartId
select @l_police = case when csrpolicy = 'PE' then 0 else 1 end
from inventory where cInvcode = @l_invcode
if @l_police = 0 select @v_SoType = 0, @v_SoDId = ''
if exists(select 1 from #mps_invuseableqty where PartId = @v_PartId and (SoType = @v_SoType and SoDId = @v_SoDId))
begin
select @v_Onhand = sum(isnull(Onhand,0)), @v_Onorder = sum(isnull(Onorder,0)), @v_Allocate = sum(isnull(Allocate,0))
from #mps_invuseableqty where PartId = @v_PartId and (SoType = @v_SoType and SoDId = @v_SoDId)
-- if @l_trancnt = 0 commit tran
return 0
end
select @l_inspqtyflag = InspOnhFlag from mom_parameter
select @v_Onhand = 0, @v_Onorder = 0, @v_Allocate = 0
/****在手量******/
select @l_inqty = sum(c.iQuantity)
from v_arrivalcurrentstock c
where c.cInvCode = @l_invcode and
(@l_free1 = '' or c.cfree1 = @l_free1 ) and
(@l_free2 = '' or c.cfree2 = @l_free2 ) and
(@l_free3 = '' or c.cfree3 = @l_free3 ) and
(@l_free4 = '' or c.cfree4 = @l_free4 ) and
(@l_free5 = '' or c.cfree5 = @l_free5 ) and
(@l_free6 = '' or c.cfree6 = @l_free6 ) and
(@l_free7 = '' or c.cfree7 = @l_free7 ) and
(@l_free8 = '' or c.cfree8 = @l_free8 ) and
(@l_free9 = '' or c.cfree9 = @l_free9 ) and
(@l_free10 = '' or c.cfree10 = @l_free10 ) and
(@v_SoType = 0 and @l_police = 0 or isnull(SoType,0) = @v_SoType and isnull(SoDId,'') = @v_SoDId)
select @l_onhand = sum(case w.bMRP when 1 then coalesce(a.iQuantity,0) - coalesce(a.fOutQuantity,0)+coalesce(a.fInQuantity,0)+coalesce(a.fTransInQuantity,0)-coalesce(a.fTransOutQuantity,0)+(case @l_inspqtyflag when 1 then isnull(a.fStopQuantity,0) else 0 end) else 0 end)
from v_CurrentPartStock a left outer join warehouse w on a.cWhCode = w.cWhCode
where a.PartId = @v_PartId and (@v_SoType = 0 and @l_police = 0 or isnull(a.SoType,0) = @v_SoType and isnull(a.SoDId,'') = @v_SoDId)
/***现存量*****/
select @l_onhand = (coalesce(@l_onhand,0) + coalesce(@l_inqty,0))
-- if @l_onhand > 0
insert into #mps_invuseableqty
(SoType, SoDId, InvCode, Free1, Free2, Free3, Free4, Free5, Free6, Free7, Free8, Free9, Free10, Onhand, PartId)
select @v_SoType, @v_SoDId, @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @l_onhand, @v_PartId
/****在单量、预约量******/
exec SA_Usp_GetInvUseableQty_CO @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @v_SoType, @v_SoDId, @l_police, @l_r
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
exec Usp_GetInvUseableQty_PO @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @v_SoType, @v_SoDId, @l_police
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
exec Usp_GetInvUseableQty_SO @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @v_SoType, @v_SoDId, @l_police
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
exec Usp_GetInvUseableQty_EO @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @v_SoType, @v_SoDId, @l_police
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
exec Usp_GetInvUseableQty_IPO @l_invcode, @l_free1, @l_free2, @l_free3, @l_free4, @l_free5, @l_free6, @l_free7 , @l_free8 , @l_free9 , @l_free10, @v_SoType, @v_SoDId, @l_police
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
/*****处理存货、自由项********/
update #mps_invuseableqty
set PartId = @v_PartId
where Invcode = @l_invcode and
(isnull(Free1,'') = @l_free1 ) and
(isnull(Free2,'') = @l_free2 ) and
(isnull(Free3,'') = @l_free3 ) and
(isnull(Free4,'') = @l_free4 ) and
(isnull(Free5,'') = @l_free5 ) and
(isnull(Free6,'') = @l_free6 ) and
(isnull(Free7,'') = @l_free7 ) and
(isnull(Free8,'') = @l_free8 ) and
(isnull(Free9,'') = @l_free9 ) and
(isnull(Free10,'') = @l_free10 )
exec Usp_GetInvUseableQty_MO @v_PartId, @v_SoType, @v_SoDId, @l_police
if @@error <> 0
begin
-- if @l_trancnt = 0
-- rollback tran tran_GetInvUseableQty
return 1019999
end
select @v_Onhand = sum(Onhand), @v_Onorder = sum(Onorder), @v_Allocate = sum(Allocate)
from #mps_invuseableqty where PartId = @v_PartId and (SoType = @v_SoType and SoDId = @v_SoDId)
select @v_Onhand = coalesce(@v_Onhand,0), @v_Onorder = coalesce(@v_Onorder,0), @v_Allocate = coalesce(@v_Allocate,0)
-- if @l_trancnt = 0
-- commit tran
return 0
GO
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
