We have been using the following query since our go live in 2009 to increment the BP. Our BP number for customers and leads started at C100000 and we are up to C105407. Unfortunately someone keyed over the assigned BP number for a sales opportunity and created a new starting point. We were able to fix the the Sales Opportunities manually to be in our normal sequence. Before we realized it someone put in a sales order and an invoice for three new customers and we can no longer modify the BP number because SAP will not allow when there are financial transactions. Those three customers have paid and we have inactivated them after duplicating them with the proper numbering sequence.
The question is how can I change the query to start at a 6 digit number instead of a 5 digit number.
Thanks for any help
Increment BP by 1
Declare @Code as char(10), @CardType as char(1)
Set @CardType = $[$-40.0.0]
If @CardType = 'S' or @CardType = 'Vendor'
Set @Code = (Select 'V' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))
from OCRD where CardType = 'S')
Set @Code = (Select 'C' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))
from OCRD where CardType = 'C' or CardType = 'L')