GP Get Next Document Number

• read the next document number
• increment the numeric part of the document number
• save the new value back to the table, and return the next document number in the output parameter.

declare @NextMfgOrder varchar(31)	
exec dbo.p_GetNextMfgOrder @NextMfgOrder output
print @NextMfgOrder

CREATE PROCEDURE dbo.p_GetNextMfgOrder 
(
   @o_MANUFACTUREORDER_I       varchar(31) output
)
as
begin

   -- get the current company ID
   declare @CmpnayID int
   SELECT @CmpnayID = a.CMPANYID
   from DYNAMICS..SY01500 a
   where a.INTERID = DB_NAME()

   --  begin a transaction, we don't want other to be changing data here at the same time.
   BEGIN tran S1_taGetNextMfgOrder
   SAVE TRAN SavePt

   -- declare a variable to hold the current value in the table.
   declare @OrigMfgOrder varchar(31)
   -- get the current value
   SELECT top 1 @OrigMfgOrder = ltrim(rtrim(a.MANUFACTUREORDER_I))
   from mops0100 a
   where a.CMPANYID = @CmpnayID

   -- do some special processing if the value retrieved is null
   if (@OrigMfgOrder is null)
   begin
      set @OrigMfgOrder = 'MO0000000000001'
   end

   --set @OrigMfgOrder = 'MO0002'
   --set @OrigMfgOrder = 'MO9999'

   -- set the return output parameter
   set @o_MANUFACTUREORDER_I = @OrigMfgOrder

   -- increment the current Mfg Order so we can store it back to the field in the mps0100 table.
   begin
      -- declare a variable to hold the incremented value. Set its value to the Original Value, for now.
      declare @IncrMfrOrder varchar(31) = @OrigMfgOrder

      -- declare some work variables that we'll need.
      declare
         @lOrderSize int = len(rtrim(ltrim(@IncrMfrOrder)))
         , @lConstantSize int = 0
         , @lNumberSize int
         , @sTestNumber varchar(31)
         , @sConstantPortion varchar(31)
         , @sNumberPortion varchar(31)
         , @lNumber bigint

      -- get the substring to test, the first time, it'll be the whole thing.
      set @sTestNumber = SUBSTRING(@OrigMfgOrder, @lConstantSize + 1, 31)
      
      WHILE ( (ISNUMERIC(@sTestNumber) = 0) and (@lConstantSize < len(@OrigMfgOrder)) )
      begin
         -- if we come in here, the string we're testing is NOT a number
         -- increment our constant size.
         set @lConstantSize = @lConstantSize + 1
         -- get the substring to test.
         set @sTestNumber = SUBSTRING(@OrigMfgOrder, @lConstantSize + 1, 31)
      end      

      -- get the constant prefix and the number portions.
      SELECT 
         @sConstantPortion = substring(@OrigMfgOrder, 1, @lConstantSize)
         , @sNumberPortion = substring(@OrigMfgOrder, @lConstantSize + 1, 31)
         , @lNumberSize = len(@OrigMfgOrder) - @lConstantSize

      -- convert the number portion to a bigint
      set @lNumber = convert(bigint, @sNumberPortion)

      --print 'Constant Size: ' + convert(varchar, @lConstantSize)
      --print 'Number Size: ' + convert(varchar, @lNumberSize)
      --print 'Constant: ' + @sConstantPortion
      --print 'Number: ' + @sNumberPortion


      declare @bBuildNextNumber bit = 1

      WHILE (@bBuildNextNumber = 1)
      begin
         -- incremnent the number
         set @lNumber = @lNumber + 1
         -- build the next order number
         --set @IncrMfrOrder = @sConstantPortion + right(replicate('0', 31) + convert(varchar, @lNumber), @lNumberSize)
         set @IncrMfrOrder = convert(varchar, @lNumber)
         if (len(@IncrMfrOrder) < @lNumberSize)
         begin
            set @IncrMfrOrder = replicate('0', @lNumberSize - len(@IncrMfrOrder)) + @IncrMfrOrder
         end
         set @IncrMfrOrder = @sConstantPortion + @IncrMfrOrder


         -- check to see if the new increment number exist in the DB
         if (exists(select 1 from WO010032 a with (nolock) where a.MANUFACTUREORDER_I = @IncrMfrOrder))
         begin
            -- the next increment number that we've calculated already exists
            -- need to build another one.
            continue;
         end

         -- check to see if the current number to be returned exists in the DB
         if (exists(select 1 from WO010032 a with (nolock) where a.MANUFACTUREORDER_I = @o_MANUFACTUREORDER_I))
         begin
            -- the next increment number that we have set to be returned exists.
            -- need to build another one.
            set @o_MANUFACTUREORDER_I = @IncrMfrOrder
            continue;
         end

         -- if we get here, reset the flag so we drop out of the loop
         set @bBuildNextNumber = 0
      end


      --print 'Incr Mfg Order: ' + @IncrMfrOrder


      NoOpIncrement:
   end

   -- save the values back to the table.
   begin
      MERGE into mops0100 as d -- Target
      using (
         select ModifiedDate = getdate()
            , MANUFACTUREORDER_I       = @IncrMfrOrder
            , CMPANYID                 = a.CMPANYID
            , Delete_Configured_BOM    = isnull(b.Delete_Configured_BOM, 0)
            , Archive_Configured_BOM   = isnull(b.Archive_Configured_BOM, 0)
            , Remove_Canceled_MO       = isnull(b.Remove_Canceled_MO, 0)
            , Remove_Canceled_MO_Days  = isnull(b.Remove_Canceled_MO_Days, 0)
            , Remove_CF_MO             = isnull(b.Remove_CF_MO, 0)
            , Remove_CF_MO_Days        = isnull(b.Remove_CF_MO_Days, 0)
            , Remove_LotSerial_MO      = isnull(b.Remove_LotSerial_MO, 0)
            , Remove_LotSerial_MO_Days = isnull(b.Remove_LotSerial_MO_Days, 0)
            , MOCloseOption            = isnull(b.MOCloseOption, 0)
            , OSRCLABEL                = isnull(b.OSRCLABEL, '')
            , PROCESSSECURITY          = isnull(b.PROCESSSECURITY, '')
            , PROCESSSECURITY2         = isnull(b.PROCESSSECURITY2, '')
            , PICKNUMBER               = isnull(b.PICKNUMBER, '001')
            , MOPRCTNM                 = isnull(b.MOPRCTNM, '001')
            , MOCLOSEOPTIONS_1         = isnull(b.MOCLOSEOPTIONS_1, 0)
            , MOCLOSEOPTIONS_2         = isnull(b.MOCLOSEOPTIONS_2, 0)
            , MOCLOSEOPTIONS_3         = isnull(b.MOCLOSEOPTIONS_3, 0)
            , MOCLOSEOPTIONS_4         = isnull(b.MOCLOSEOPTIONS_4, 0)
            , MOCLOSEOPTIONS_5         = isnull(b.MOCLOSEOPTIONS_5, 0)
            , MOCLOSEOPTIONS_6         = isnull(b.MOCLOSEOPTIONS_6, 0)
            , MOCLOSEOPTIONS_7         = isnull(b.MOCLOSEOPTIONS_7, 0)
            , MOCLOSEOPTIONS_8         = isnull(b.MOCLOSEOPTIONS_8, 0)
            , MOCLOSEOPTIONS_9         = isnull(b.MOCLOSEOPTIONS_9, 0)
            , MOCLOSEOPTIONS_10        = isnull(b.MOCLOSEOPTIONS_10, 0)
            , MOCLOSEOPTIONS_11        = isnull(b.MOCLOSEOPTIONS_11, 0)
            , MOCLOSEOPTIONS_12        = isnull(b.MOCLOSEOPTIONS_12, 0)
            , MOCLOSEOPTIONS_13        = isnull(b.MOCLOSEOPTIONS_13, 0)
            , MOCLOSEOPTIONS_14        = isnull(b.MOCLOSEOPTIONS_14, 0)
            , ALLOCATERELEASED         = isnull(b.ALLOCATERELEASED, 0)
            , COMPLETEMOATCOMPCLOSE    = isnull(b.COMPLETEMOATCOMPCLOSE, 0)
            , CLOSEMOATCOMPCLOSE       = isnull(b.CLOSEMOATCOMPCLOSE, 0)
            , REVAL_IN_PROCESS_I       = isnull(b.REVAL_IN_PROCESS_I, 0)
            , RECONCILE_IN_PROCESS_I   = isnull(b.RECONCILE_IN_PROCESS_I, 0)
            , PRINT_PICKLIST_NOTES     = isnull(b.PRINT_PICKLIST_NOTES, 0)
            , ALLOCATE_REV_ISS_I       = isnull(b.ALLOCATE_REV_ISS_I, 0)
            , FLRSTCKACCNTINDEX        = isnull(b.FLRSTCKACCNTINDEX, 0)
            , CBXALLOWNEGATIVEWIP      = isnull(b.CBXALLOWNEGATIVEWIP, 0)
            , MANUALSELECT             = isnull(b.MANUALSELECT, 0)
            , DEF_PICKLIST_SORTBY      = isnull(b.DEF_PICKLIST_SORTBY, 0)
            , MFGUSEALLCOLLECTED       = isnull(b.MFGUSEALLCOLLECTED, 0)
            , MFGUSEALLSETUP           = isnull(b.MFGUSEALLSETUP, 0)
            , MFGUSEALLWIP             = isnull(b.MFGUSEALLWIP, 0)
            , LOTSLCTNMTHD             = isnull(b.LOTSLCTNMTHD, 0)
            , REQLNKNG                 = isnull(b.REQLNKNG, 0)
            , USE_DEFAULT_BIN          = isnull(b.USE_DEFAULT_BIN, 0)
         from DYNAMICS..SY01500 a
         left outer join mops0100 b on a.CMPANYID = b.CMPANYID
         where a.CMPANYID = @CmpnayID
      ) as s on  -- Source
         d.CMPANYID = s.CMPANYID      -- set the criteria to find matching rows.
      when matched then -- On match update
      update set 
         MANUFACTUREORDER_I         = s.MANUFACTUREORDER_I
      when not matched then -- Add missing
      insert ( MANUFACTUREORDER_I,   CMPANYID,   Delete_Configured_BOM,   Archive_Configured_BOM,   Remove_Canceled_MO,   Remove_Canceled_MO_Days,   Remove_CF_MO,   Remove_CF_MO_Days,   Remove_LotSerial_MO,   Remove_LotSerial_MO_Days,   MOCloseOption,   OSRCLABEL,   PROCESSSECURITY,   PROCESSSECURITY2,   PICKNUMBER,   MOPRCTNM,   MOCLOSEOPTIONS_1,   MOCLOSEOPTIONS_2,   MOCLOSEOPTIONS_3,   MOCLOSEOPTIONS_4,   MOCLOSEOPTIONS_5,   MOCLOSEOPTIONS_6,   MOCLOSEOPTIONS_7,   MOCLOSEOPTIONS_8,   MOCLOSEOPTIONS_9,   MOCLOSEOPTIONS_10,   MOCLOSEOPTIONS_11,   MOCLOSEOPTIONS_12,   MOCLOSEOPTIONS_13,   MOCLOSEOPTIONS_14,   ALLOCATERELEASED,   COMPLETEMOATCOMPCLOSE,   CLOSEMOATCOMPCLOSE,   REVAL_IN_PROCESS_I,   RECONCILE_IN_PROCESS_I,   PRINT_PICKLIST_NOTES,   ALLOCATE_REV_ISS_I,   FLRSTCKACCNTINDEX,   CBXALLOWNEGATIVEWIP,   MANUALSELECT,   DEF_PICKLIST_SORTBY,   MFGUSEALLCOLLECTED,   MFGUSEALLSETUP,   MFGUSEALLWIP,   LOTSLCTNMTHD,   REQLNKNG,   USE_DEFAULT_BIN )
      values ( s.MANUFACTUREORDER_I, s.CMPANYID, s.Delete_Configured_BOM, s.Archive_Configured_BOM, s.Remove_Canceled_MO, s.Remove_Canceled_MO_Days, s.Remove_CF_MO, s.Remove_CF_MO_Days, s.Remove_LotSerial_MO, s.Remove_LotSerial_MO_Days, s.MOCloseOption, s.OSRCLABEL, s.PROCESSSECURITY, s.PROCESSSECURITY2, s.PICKNUMBER, s.MOPRCTNM, s.MOCLOSEOPTIONS_1, s.MOCLOSEOPTIONS_2, s.MOCLOSEOPTIONS_3, s.MOCLOSEOPTIONS_4, s.MOCLOSEOPTIONS_5, s.MOCLOSEOPTIONS_6, s.MOCLOSEOPTIONS_7, s.MOCLOSEOPTIONS_8, s.MOCLOSEOPTIONS_9, s.MOCLOSEOPTIONS_10, s.MOCLOSEOPTIONS_11, s.MOCLOSEOPTIONS_12, s.MOCLOSEOPTIONS_13, s.MOCLOSEOPTIONS_14, s.ALLOCATERELEASED, s.COMPLETEMOATCOMPCLOSE, s.CLOSEMOATCOMPCLOSE, s.REVAL_IN_PROCESS_I, s.RECONCILE_IN_PROCESS_I, s.PRINT_PICKLIST_NOTES, s.ALLOCATE_REV_ISS_I, s.FLRSTCKACCNTINDEX, s.CBXALLOWNEGATIVEWIP, s.MANUALSELECT, s.DEF_PICKLIST_SORTBY, s.MFGUSEALLCOLLECTED, s.MFGUSEALLSETUP, s.MFGUSEALLWIP, s.LOTSLCTNMTHD, s.REQLNKNG, s.USE_DEFAULT_BIN )
      ; -- all MERGE statements must end with a semicolon

      NoOpUpate:
   end

   NoOpEndTrans:
   COMMIT tran S1_taGetNextMfgOrder

   NoOpEnd:
end

GO