GP Get Next Document Number
Great Plains document numbers are typically in the format 'ORD000001'. The next document number is typically stored in a setup table. The following is a sample script to create a stored procedure that will do the following:
- 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.
This particular sample reads the setup table for Manufacturing Orders, [mops0100].
Example on how to execute the stored procedure:
declare @NextMfgOrder varchar(31) exec dbo.p_GetNextMfgOrder @NextMfgOrder output print @NextMfgOrder
The stored procedure:
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 do not want other to be changing data here at the same time.
BEGIN tran p_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 are 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 p_taGetNextMfgOrder
NoOpEnd:
end
GO