Skip to Content
0
Former Member
Jan 15, 2011 at 10:37 AM

BP Master Data FMS (Query)

247 Views

Hi all,

I have created the query "Auto Code Generator" Group wise. But it was working only for Customer and for Lead.

The Query was as follows.

declare @type as varchar(10)

declare @temp as char(15)

declare @group as int

set @group = (Select max(groupcode) from OCRD where GROUPCODE = $[OCRD.GROUPCODE])

set @type = (select max(cardtype) from OCRD where cardTYPe = $[OCRD.CARDTYPE])

if @group = '103'

BEGIN

SEt @temp=(select max(right(cardcode,3)) + 1 from ocrd where (cardtype='C') and groupcode= '103')

set @temp='C'isnull(replicate(0,4-len(@temp)),'')@temp

select cast(@temp as char(15))

--SELECT @TEMP, @GROUP, @TYPE

End

Else

if @group = '103'

BEGIN

SEt @temp=(select max(right(cardcode,3)) + 1 from ocrd where (cardtype='L') and groupcode= '103')

set @temp='L'isnull(replicate(0,3-len(@temp)),'')@temp

select cast(@temp as char(15))

--SELECT @TEMP, @GROUP, @TYPE

End

Remarks: While selecting Lead also only Customer number Generating.

Kindly do whats wrong in this query.

Thanks in advance

Anish