MRP核心存储过程。

sh_lwx

Usp_GetInvUseableQty，刚破解，在研究下，可以直接拿过来 用。。。

sztlbb

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   

loosen2009

我曾试着自己将MRP的计算过程展现出来，后来由于工作的关系没有继续，如果谁有时间，可以研究一下，功德无量

dekie

我曾试着自己将MRP的计算过程展现出来，后来由于工作的关系没有继续，如果谁有时间，可以研究一下，功德无量

dekie

能否将MRP的计算过程展现出来，让用户明明白白

kaikai7902

自己不能在用友中做二开，可以研究下原理

xshan2004

怎么什么也看不到啊

sue.hou

谢谢分享，正在学习中。。。。。

zhx_9808

谢谢分享

俊希

{:soso_e102:}楼主辛苦了，楼主加油。

dxb0505

求分享。。。。。。。。。。

yaoxiaoxiang

看了有啥用，还是不一样的，又不能二开

sh_lwx

在这个基础上，已做了二次开发。

mangnalia

谢谢楼主分享

王旭葵

没看到

vickdracula

在哪里？

luojun702

啥 都 没 有，看 到 呀

稳稳的幸福

啥 都 没 有，看 到 呀