SQL Query for automatic creation of Item Code

[Former Member]

Hi - A contact of mine is asking this question about SQL Query which is out of my knowledge. Please can anyone help?

Hi,thanks for the help,i've one more problem.i have made a query for automatically creation of item code in item name field but it didn't work.can u help me that it is right or wrong.query that i've made is below.

SELECT       substring ((MAX(T0.ItemCode)), 0,4)cast ((Substring ((MAX(T0.ItemCode)),4,4)1) AS varchar(20))

FROM    OITM T0

WHERET0.ItmsGrpCod = $[OITM.ItmsGrpCod]

whenever i execute this query i get an error like this:-  1). [SQL Native Client][SQL Server]Incorrect syntax near the keyword 'SELECT'. 2). [SQL Native Client][SQL Server]Incorrect syntax near 'OITM.ItmsGrpCod'. 3). [SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.so how could i genrate item code automatically and what is the solution of above error,plz tell me asap.i'll wait for ur reply

[K_Pauquet · Accepted solution]

Hi Tim,

in version 882 we have automatic sequential numbering of master data. The functionality works like the document numbering. You can also convert existing data from a lower version. Please have a look at  SAP Note 642497 - Business Partner and Item Manual Master Data Series Conversion.

All the best,

Kerstin

[zal_parchem2 · Accepted solution]

Kerstin - that note talks about POD confimation...can you please INSERT the link to the Data Series Conversion???

Regards - Zal

Oh - Found it, needed a 1:  https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...

Message was edited by: Zal Parchem

[former_member589482 · Accepted solution]

Does this include automatic numbering of new BPs too?

[K_Pauquet · Accepted solution]

Hi Martin,

indeed it does. For existing BPs you use the converter & once you have the numbering defined & create new BPs, they are automatically numbered.

All the best,

Kerstin

[former_member589482 · Accepted solution]

Dear Kerstin

I have since had this suggestion so I think I'll wait? I also think its goo information for others to know.

"while the referred suggestion on this portal page allows you to add a formatted search to work around the situation, this is now standard functionality offered in the latest version of Business One"

"As this is the case, it may be advisable to hold off until you move onto 8.82 in order to take advantage of this and other enhanced features within the standard product".

[Former Member]

Thanks to everyone for helping with Manish's questions. This thread show how helpful and how useful SCN really is!

I use SCN in part of my "Sales Pitch" and I shall link this post to my presentation to show Prospects what an integrated, knowledgeable community is waiting for them should they choose SAP B1.

[Former Member]

Hi Tim,

This is a working example of a formatted search and you can manipulate this according to your item naming convention. In my example the item code naming convention is as follows: '270/SQ005'

--Auto generates item code

select  --substring(max(ItemCode),1,CHARINDEX ( '/',MAX(itemcode))+2),

substring(max(ItemCode),1,6)

+case len(cast(substring(max(ItemCode),7,6) as numeric(18,0)))

when 2 then '0'
when 1 then '00'
else '' end

+ cast(substring(max(ItemCode),7,6)    
+ 1 as nvarchar(10))

from OITM                           
where  OITM.ItemCode<>'9999'
----------------------------------------------------------------------------------------------------------------------
--This shows constant characters in your Item code (if relevant)

select MAX(itemcode),CHARINDEX ( '/',MAX(itemcode))  from OITM

Regards,

David

[former_member184146]

Hi David,

i tried your query too.but it is not working too.and showing the error:-

1). [Microsoft][SQL Native Client][SQL Server]The multi-part identifier "OITM.ItemCode" could not be bound. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

regards

manish

[Former Member]

Hi,

My query and Zal's query are basically doing the same thing.

Zal's will be easier for you to manipulate in this case

Mine you still need to manipulate it to work in B1. Pls follow Zal's instructions- you will not see any results if you run it in the back end. You need to create a formatted search in B1 use the script Zal sent you. If you struggle pls sent an example your item code/s then i will prepare a script for you.

Thanks,

David

[zal_parchem2]

Hello Tim - back again...OK this seems to be heading into the wild-blue yonder here...so...

1.  What you are looking for is a Formatted Search (FMS) on the Item Master Data Record.

2.  The FMS should be used when in the mode of "Add" on the Item Master Data Record.

3.  Manish had something about Item Group Code in his original posting.

4.  The length of the Item Number to be created is critical.

5.  I can only give you an example and Manish or you will need to adjust it accordingly.

6.  I am assuming Item Groups are "101", "102", etc and each starts with a different letter.

7.  39 refers to the field on the Item Master Data Record (use your View of Sysem Information).

8.  I am assuming the numeric length is 6.

9.  These are all assumptions without enough info at hand...

SELECT

CASE $[$39.0.number]

WHEN 101 THEN 'I'
WHEN 102 THEN 'J'
WHEN 103 THEN 'K'

END

+Isnull(Substring(Str(1000000+Max(Right(T0.ItemCode,6))+1,7),2,6),'000001')

FROM
OITM T0

WHERE

T0.ItemCode LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'

      AND T0.ItmsGrpCod = $[$39.0.number]

[former_member184146]

Hi Zal,

i tried the query given by you but unfortunately it didn't work.

this time its showing the error :-

1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '$39.0.number'. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

is there any problem in my sql database?

regards

manish

[zal_parchem2]

No, nothing wrong with you SQL Database

Big Question - did you create a Formatted Search???

You cannot run the SQL directly in the Query Generator due to "$[$39...] - again it must be run as a FMS on the Item Master Data Window...

Find the SAP User Guide of "How to Define and Use Formatted Search".

Regards - Zal

[former_member184146]

No Sir, i have not created any Formatted search!

can u give me any link where i can find FMS  Guide.

regards

manish

[zal_parchem2]

Hello Manish - here you go...find your version and onwards you go...

https://websmp208.sap-ag.de/smb/sbo/documentation

Regards - Zal

[former_member184146]

thanks for the linkbut i've not user id and password.

regards

manish

[zal_parchem2]

Manish - are you a Customer or do you work for an SAP Partner???

Do you have an S Number???

If not, then send an E Mail to my address on my profile and I will send you a copy of that PDF from SAP - OK???

Regards - Zal

[former_member184146]

I works  for SAP Partner and i m on my first project and i've to learn so many things in SAP,whatever i know in SAP B1 is due to of my self learning,

and thank  a lot both of you Tim and Zal for helping me.i am so much happy.

my E MAIL Address is manish30khatri@gmail.com

thank u!

best regards

manish

[zal_parchem2]

more than welcome Manish...

1.  Sent the PDF (why SAP does not allow PDF to be placed here is beyond my understanding since THEY put almost everything in PDF format)...

2.  If you are working for an SAP Partner, then get them to give you an "S Number" so you can get into these areas - you are only going to be at a huge disadvantage if you do not get one...

3.  Have fun in learning everything - it might be difficult, but it is definitely rewarding...

Best of luck moving forward - Zal

[former_member184146]

Thank you sir!!

[former_member184146]

Thank you sir!!

[former_member184146]

hurrreyy........it really worked.

this time after setting the FMS item code are generating automatically.

finally i got the success.thank u so mch SAP Team.specially Zal & Tim.

i cant explain how much i m happy..........

thanks again guys for the help.

best regards

manish

[former_member184146]

Hi zal,

this time i tried this query for business partner master data

CASE $[$40.0.number]

WHEN 100 THEN 'C'

WHEN 102 THEN 'V'

WHEN 103 THEN 'L'

END

+Isnull(Substring(Str(1000000+Max(Right(T0.CardCode,6))+1,7),2,6),'000001')

FROM

OCRD T0

WHERE

T0.CardCode LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'

      AND T0.CardType = $[$40.0.number]

but i got the error below:-

1). [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting the varchar value 'C' to data type int.  '' (CRD1)

can u tell why this error is coming. and is the query written above right or wrong.

thank u!

best regards

manish

[zal_parchem2]

Hello Manish...OK with being new, there are some rules you need to know about in the SCN...and this will also help increase your skills a bit in using the SCN...

One - SAP requires that you post a different thread for each question/topic and that you do not mix new requests into old ones...

Two - the moderators (when they get a chance to look) will send you notices and tell you to open a new thread and might get a bit upset with you...

Three - please have Tim close this one thread as "Answered".  It is only right for you to let others know your question is answered so others can come in here and will say "hey, bingo - this is something I can also use"...

Four - SAP and those answering questions also like for you to do a search before you ask a question.  There is a TON of information out there for you to locate.  And this actually helps everyone in that you might get more replies coming your way if folks know you have searched before-hand which shows a bit of initiative...and, most importantly, you can say why/how what you found does not actually meet your needs...gives them better ideas on alternatives they can give to you...

Below is a thread which might help with the seaching of SAP SCN...with the Google search try looking for "Autogenerate Business Partner Code" and you will find a great starting place with posts from Gordon Du, Jimmy Michael, and Istvan Koros (just those I happened to find while looking through the numerous postings found)...PS - they also will point out why your FMS is not working...(hint "CardCode" and "CardType")...

http://scn.sap.com/community/getting-started/blog/2012/03/29/custom-google-search-for-the-new-scn

Make sure you "add to favorite" on your browser for the above...

Try that out and (if you do not find your answer or the resolutions are not exactly what you need)  open up a new thread, and then we can take it from there...OK???

Regards - Zal

PS - get your S Number yet??? 

[former_member184146]

ok sir and i didn't get my S number yet

[former_member589482]

Could you send it me too? I do have an S number but cant acces the link either. my email address is martin@betagroupltd.com

[former_member589482]

Now this google search functionality I like - a lot. Thanks for the heads up.

[zal_parchem2]

Hello Tim - Manish has posted this on the German forum and so he is not getting very many replies...

http://scn.sap.com/thread/3194843

There are two suggestions - one, he should probably order this book since he has had so many questions on SQL over the past few months (his answer is on page 205)...

http://www.amazon.com/Mastering-SQL-Queries-SAP-Business/dp/1849682364/ref=sr_1_1?ie=UTF8&qid=134026...

Manish would be able to pick up quite a few queries he has been asking for...

Two, what is the current structure of his item codes - does he have an alpha character in the front of the number.  In order for him to get some help, he really needs to define his requirements a bit better with more information.

Regards - Zal

[Former Member]

Thanks Zal - I sent him the link for this and advised he post his questions here. Good advice on the book, I think I will buy it as well.

Tim

[zal_parchem2]

http://scn.sap.com/thread/3194892

oops - wrong thread provided above - here is where Manish started in the German forum...

[former_member184146]

Hi Zal,

i've made first item code starting with 'I' i.e I001