- 打卡等级：无名新人
- 打卡总天数：3
- 打卡月天数：1
- 打卡总奖励：5
- 最近打卡：2024-11-01 16:19:28
|
发表于 2018-1-10 09:30:55
|
显示全部楼层
USE [UFDATA_999_2014]
GO
/****** Object: StoredProcedure [dbo].[Usp_GetInvUseableQty] Script Date: 2018-01-10 9:27:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/******获得当前现存量、在单量、预约量**********/
ALTER procedure [dbo].[Usp_GetInvUseableQty]
@v_FactoryCode nvarchar(50) = '',
@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) = ''
--with encryption
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_outqty 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 (FactoryCode = @v_FactoryCode or @v_FactoryCode = '') and 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 (FactoryCode = @v_FactoryCode or @v_FactoryCode = '') and 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 c.cfree1,c.cfree2,c.cfree3,c.cfree4,c.cfree5,c.cfree6,c.cfree7,c.cfree8,c.cfree9,c.cfree10,c.iQuantity into #t_onhand
from v_arrivalcurrentstock c
where c.cInvCode = @l_invcode and
(c.cFactoryCode = @v_FactoryCode or @v_FactoryCode = '') and
(@v_SoType = 0 and @l_police = 0 or isnull(SoType,0) = @v_SoType and isnull(SoDId,'') = @v_SoDId)
select @l_inqty = sum(c.iQuantity)
from #t_onhand c
where (@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 )
if (select IssApplyOnhFlag from mom_parameter) = 1
select @l_outqty = sum(isnull(c.iquantity,0) - isnull(c.foutquantity,0))
from MaterialAppVouchs c inner join MaterialAppVouch c1 on c.Id = c1.Id inner join warehouse w on c.cwhcode = w.cwhcode
where c.cInvCode = @l_invcode and isnull(c.impoids,0) = 0 and isnull(c.iomodid,0) = 0 and c1.chandler is not null and isnull(c.cbcloser,'')='' and
isnull(c.iquantity,0) - isnull(c.foutquantity,0)>0 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(iSoType,0) = @v_SoType and isnull(iSoDId,'') = @v_SoDId) and (w.cfactorycode = @v_FactoryCode or @v_FactoryCode = '') and w.bmrp = 1
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) and (w.cFactoryCode = @v_FactoryCode or w.cFactoryCode is null or @v_FactoryCode = '')
/***现存量*****/
select @l_onhand = (coalesce(@l_onhand,0) + coalesce(@l_inqty,0)) - coalesce(@l_outqty,0)
-- if @l_onhand > 0
insert into #mps_invuseableqty
(FactoryCode,SoType, SoDId, InvCode, Free1, Free2, Free3, Free4, Free5, Free6, Free7, Free8, Free9, Free10, Onhand, PartId)
select @v_FactoryCode,@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 @v_FactoryCode,@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 @v_FactoryCode,@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 @v_FactoryCode,@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 @v_FactoryCode,@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 @v_FactoryCode,@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_FactoryCode,@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 (FactoryCode = @v_FactoryCode or @v_FactoryCode = '' ) and 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
|
|
楼主: sh_lwx
|站长微信|Archiver|手机版|小黑屋|用友之家
( 蜀ICP备07505338号|
51072502110008 )
IP卡
狗仔卡
显身卡
