Skip to Content
author's profile photo
Former Member

Automatic number generation

Hi All,

How can I generate automatic number's in Business patner code? I tried to execute one query for this , but error came while executing this query.Error message is: 'Error (8180) encountered'. Please help me out from this

thanks in advance bbn

Query:

-- This Query generates a Business Partner Code automatically

-- depending on the Card Type i.e. C100001, S1000001, L1000001

-- Prerequisites:

-- 1. The first customer, supplier and lead number has to be entered manually

-- 2. The number must start directly behind the cardtype, no leading zeros (NOT C000100)

-- Edda, March 27, 2003

SELECT $[OCRD.CardType] +

CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(CardCode, 2, 6)))+1)

FROM OCRD

where OCRD.CardType = $[OCRD.CARDTYPE]

FOR BROWSE

SELECT

CASE OCRD.CardType

WHEN 'C' Then MAX(OCRD.CardCode)+1

When 'S' Then MAX(OCRD.CardCode)+1

Else ''

END

FROM OCRD WHERE OCRD.CardCode <> '20001' and OCRD.CardType = $[OCRD.CARDTYPE] Group by OCRD.CardType

FOR BROWSE

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    author's profile photo
    Former Member
    Sep 30, 2005 at 11:10 AM

    DECLARE @CardType varchar(1)

    SET @CardType = $[$40.0.0]

    SELECT

    CASE @CardType

    WHEN 'C' Then 'C' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(T0.CardCode, 2, 6)))+1)

    When 'S' Then 'S' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(T0.CardCode, 2, 6)))+1)

    When 'L' Then 'L' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(CardCode, 2, 6)))+1)

    Else ''

    END

    FROM OCRD T0 WHERE T0.CardCode <> '20001' and T0.CardType = @CardType Group by T0.CardType

    FOR BROWSE

    <i>

    Message was edited by: Adele le Roux

    Was busy posting and testing when Alexey was. Was trying to base query on your query</i>

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 30, 2005 at 10:53 AM

    Hi!

    Hope it'll be not so hard to complete my query in accordance with your requirements:[code]DECLARE @NumLen Int

    SET @NumLen = 7

    IF (SELECT Count(*) FROM OCRD) = 0

    BEGIN

    IF (RTrim($[OCRD.CardType]) = 'S')

    SELECT 'V'SUBSTRING('0000000000',1,@NumLen-2)'1'

    ELSE

    SELECT RTrim($[OCRD.CardType])SUBSTRING('0000000000',1,@NumLen-2)'1'

    END

    ELSE

    IF (RTrim($[OCRD.CardType]) = 'S')

    SELECT

    'V'

    +

    SUBSTRING('0000000000',1,@NumLen-1-LEN(RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)))

    +

    RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)

    FROM

    OCRD

    ELSE

    SELECT

    RTrim($[OCRD.CardType])

    +

    SUBSTRING('0000000000',1,@NumLen-1-LEN(RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)))

    +

    RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)

    FROM

    OCRD[/code]HTH

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Oct 03, 2005 at 12:06 PM

    Hi,

    Thanks for ur reply. The query is working fine.

    with regards bbn

    Add comment
    10|10000 characters needed characters exceeded