here is a script that we received from Microsoft to correct this. I do not warranty this script nor assume any liability; but, it certainly worked for us. you can test it on Fabrikam first.
--USE [TWO]
go
/****** Object: UserDefinedFunction [dbo].[FA_GetNextFADocNumber] Script Date: 2/27/2014 10:07:28 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
ALTER FUNCTION [dbo].[Fa_getnextfadocnumber] (@IN_Source_Doc CHAR(11))
returns CHAR(16)
AS
BEGIN
DECLARE @prefix CHAR(5),
@last_doc_number CHAR(16),
@fa_doc_number CHAR(16)
DECLARE @last_value INTEGER,
@new_value BIGINT
IF @IN_Source_Doc IN ( 'FAADD' )
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc = 'FAADD'
SELECT @prefix = 'FAADD'
END
IF @IN_Source_Doc IN ( 'FACHG', 'FACHG-R', 'FAMCH', 'FAMCH-R' )
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc IN ( 'FACHG', 'FACHG-R', 'FAMCH', 'FAMCH-R' )
SELECT @prefix = 'FACHG'
END
IF @IN_Source_Doc IN ( 'FADEP', 'FADEP-O', 'FADEP-R' )
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc IN ( 'FADEP', 'FADEP-O', 'FADEP-R' )
SELECT @prefix = 'FADEP'
END
IF @IN_Source_Doc IN ( 'FARET', 'FAMRT', 'FARET-P', 'FARET-PU', 'FARET-U'
)
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc IN ( 'FARET', 'FAMRT', 'FARET-P', 'FARET-PU',
'FARET-U' )
SELECT @prefix = 'FARET'
END
IF @IN_Source_Doc IN ( 'FAXFR', 'FAMXF', 'FAMXF-C', 'FAXFR-C', 'FAXFR-P' )
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc IN ( 'FAXFR', 'FAMXF', 'FAMXF-C', 'FAXFR-C',
'FAXFR-P'
)
SELECT @prefix = 'FAXFR'
END
IF @IN_Source_Doc IN ( 'FASUM' )
BEGIN
SELECT @last_doc_number = Max(fa_doc_number)
FROM fa00902
WITH (INDEX(FA00902_MSFT)) /* amelroe added index hint 02/27/2014 */
WHERE sourcdoc = 'FASUM'
SELECT @prefix = 'FASUM'
END
SELECT @last_doc_number = Isnull(@last_doc_number, '')
IF @last_doc_number <> ''
BEGIN
SELECT @last_value = (SELECT Cast(Substring(@last_doc_number, 6, 11)
AS
INTEGER))
SELECT @new_value = @last_value + 100000000001
SELECT @fa_doc_number = Rtrim(@prefix)
+ Ltrim(Substring(Str(@new_value, 12), 2, 11
))
END
ELSE
BEGIN
SELECT @fa_doc_number = CASE
WHEN @IN_Source_Doc = ( 'FAADD' ) THEN
'FAADD00000000001'
WHEN @IN_Source_Doc IN
( 'FACHG', 'FACHG-R',
'FAMCH',
'FAMCH-R' ) THEN
'FACHG00000000001'
WHEN @IN_Source_Doc IN
( 'FADEP', 'FADEP-O',
'FADEP-R'
) THEN
'FADEP00000000001'
WHEN @IN_Source_Doc IN (
'FARET', 'FAMRT', 'FARET-P',
'FARET-PU', 'FARET-U' )
THEN 'FARET00000000001'
WHEN @IN_Source_Doc IN (
'FAXFR', 'FAMXF', 'FAMXF-C',
'FAXFR-C', 'FAXFR-P' )
THEN 'FAXFR00000000001'
WHEN @IN_Source_Doc = ( 'FASUM' ) THEN
'FASUM00000000001'
ELSE ''
END
END
RETURN( @fa_doc_number )
END
go
