cancel
Showing results for 
Search instead for 
Did you mean: 

Increment BP by 1 issue

Former Member
0 Kudos
77

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'

  BEGIN

  Set @Code = (Select 'V' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))
from OCRD where CardType = 'S')

  END  ELSE

Set @Code = (Select 'C' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))
from OCRD where CardType = 'C' or CardType = 'L')

  Select @Code

Steve

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Steve,

My suggestion is keep the current 5 digits number and allow the gap. Why should you change to 6 digits?

Thanks, Gordon

Former Member
0 Kudos

Gordon

Other than these three record which are 5 digit, we have over 5400 BP's that are 6 digit. When in the search mode those 3 records follow the 6 digit codes. We would like to maintain our original numbering schemse.

Hope this explains why we are trying to fix this issue.

I am thinking that instead of using substring function there may be another function that would help us resolve this.

Thanks

Steve

Former Member
0 Kudos

What are those 3 codes?

Former Member
0 Kudos

The three codes are:

1. C10838

2. C10839

3. C10840

We want the next code to be assigned at this moment to be

C105410

Thanks

Steve

Former Member
0 Kudos

Try:

Declare @Code as char(10), @CardType as char(1)

   Set @CardType = $[$-40.0.0]

  If @CardType = 'S' or @CardType = 'Vendor'

    BEGIN

    Set @Code = (Select 'V' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))

from OCRD where CardType = 'S')

    END  ELSE

  Set @Code = (Select 'C' + cast(substring(max(CardCode),2,6) + 1 as nvarchar(10))

from OCRD where (CardType = 'C' or CardType = 'L') AND CardCode NOT IN ('C10838','C10839','C10840'))

    Select @Code

Former Member
0 Kudos

Gordon

I am getting an error and I think it is related to th second ) after the codes

AND CardCode NOT IN ('C10838','C10839','C10840'))

I have tried several variation and it is not working

Former Member
0 Kudos

Gordon

I retried it by copying the entire formulae and it now works.

Thanks for your help,

Steve

Answers (1)

Answers (1)

former_member218051
Active Contributor
0 Kudos

Hi Steven,

what i understood is this,

You corrected three erroneous BPs with the proper BP code that means the earlier 6 digit one.

And now you want to your code to generate the next BP Code same as earlier i.e. 6 digit.

In your query why are you saying  cast(substring(max(CardCode),2,6)

instead it should be  cast(substring(max(CardCode),2,7). Correct me if I'm wrong.

Or else Now you have inactivated those BPs. So system is keeping a flag under a field Frozenfor .

so try getting the last number for all those BPs who are active and check the result.

thanking you

Malhaar

Former Member
0 Kudos

Malhaar

I tried using your suggestion  cast(substring(max(CardCode),2,7),but it gives me the same result.

The reason is that it find the lower number first.

Thanks for your input though

Steve