User-Defined Values for Item Codes

[Former Member]

Hello dears...

I've created a new formatted query which it automatically controls Item codes from Item classification for taxes.

For instance, when I register a new item as a classification 'Service' and not check Phantom Item, the Item Code should change to 'S000001'. The same when I use items as a classification 'Material', changing to 'M000001'.

But, if I check phantom item box, the item code has change to 'F000001', even exchanging item classification to service and material. It's works well when I change the field Classification Item, but if I change phantom item field, the code should change.

I don't know if my code is wrong. Someone can help me about it?

My code follows below:

DECLARE @sequencial varchar(1)

DECLARE @controle varchar(1)

set @sequencial = $[OITM.ItemClass]

set @controle = $[OITM.Phantom]

--Direcionamento

BEGIN

IF @sequencial = '1' GOTO Servico

IF @sequencial = '2' GOTO Item

END

Servico:

IF @controle <> 'Y'

BEGIN

Select Top 1 'S'+right(('000000' + cast((right(cast(replace(itemcode,'S','') as int),6))++1 as varchar(6))), 6)

from [OITM]

where Substring(itemcode,1,1)='S'

order by itemcode desc

END

--Ficticio:

IF @controle = 'Y'

BEGIN

Select Top 1 'F' +right(('000000' + cast((right(cast(replace(itemcode,'F','') as int),6))++1 as varchar(6))), 6)

from [OITM]

where Substring(itemcode,1,1)='F'

END

Item:

IF @controle <> 'Y'

BEGIN

Select Top 1 'M' +right(('000000' + cast((right(cast(replace(itemcode,'M','') as int),6))++1 as varchar(6))), 6)

from [OITM]

where Substring(itemcode,1,1)='M'

order by itemcode desc

END

--Ficticio:

IF @controle = 'Y'

BEGIN

Select Top 1 'F'+right(('000000' + cast((right(cast(replace(itemcode,'F','') as int),6))++1 as varchar(6))), 6)

from [OITM]

where Substring(itemcode,1,1)='F'

END

Waiting for news...

Regards...

[Former Member]

This problem is solved.