Formatted Search to Generate Consecutive Number of Batch

[Former Member]

Hello !

I'm trying to create a query to use it in a Formatted Search to generate a consecutive number of Batch.

In the Good Receipt when you define the Batches - Setup in the section Created Batches i want to generate a consecutive number with a formatted search.

This is my query:

/* Start Query */

declare @temp as varchar

declare @pad as varchar

declare @batchnum as varchar

set @batchnum=(select top 1 batchnum from oibt order by itemcode desc)

set @temp=(SELECT

CASE

WHEN (@batchnum LIKE cast($[$3.0.0] as varchar)) THEN (@batchnum + 1)

WHEN (@batchnum NOT LIKE cast($[$3.0.0] as varchar)) THEN ($[$3.0.0] + 1)

END

)

set @pad=(SELECT len(@temp))

SELECT CASE @pad

when '1' then cast('00000' + @temp as varchar)

when '2' then cast('0000' + @temp as varchar)

when '3' then cast('000' + @temp as varchar)

when '4' then cast('00' + @temp as varchar)

when '5' then cast('0' + @temp as varchar)

else

@temp

end

/* End Query */

In this query i try to compare the value of field BatchNum in the Table OIBT, this is for know the last value in the tables if exists, then because in the Batch: Setup windows in SAP you can Add many numbers of Batch that you can, this depends of the items quantity.

So this is my real problem because i need to know the last value inserted in the table and if the user is adding more than one Batch i need to know the number that user is writing.

In this query i trying to use the fiel batchnum and the variable $[$3.0.0] but it doesn't work, i'm not sure if the variable $[$3.0.0] is taking the value that the user typing in the window Batch:Setup

I hope you can help please.

I want to know if exist a way to create this consecutive number in a formatted search or this is a develop that i have to do.

Thanks in advance

Best Regards.

[former_member583013 · Accepted solution]

Xavier,

Are you trying to creating a Consecutive Batch number by ItemCode or just a running number irrespective of the Item.

For Example:

Current highest batch number is 001205 and it is for Item A

Let us say you are doing a Goods receipt for Item B now. Do you want 001206?

Please clarify

Suda

[Former Member · Accepted solution]

Ok

I trying to create a Consecutive number for the Batch by the Quantity of the ItemCode

I mean for Item A that has 3 in Quantity Field i want to generate a batch number 001205 if in this Batch user put all the Quantity there (3).

But if the user decide that in batch number 001205 put only 1 Quantity in the other row of batch i want to generate the batch number 001206 and put the rest of Quantity (2)

Like this is what i trying to create the Formatted Search!

i hope i clarify the explanation

Thanks in advance Suda

Best Regards!

[Former Member]

good job

[former_member583013]

Xavier,

In the Batch-setup screen > in the top window (Rows from Documents) make sure the following columns are visible.

Total Needed

Total Created

Open Quantity

Total Batches

I am using the values from these columns in my SQL for calculation and condition checking

Go to form settings and for <b>Rows from documents</b> enable the above referred fields if they are not already visible.

Check the formatted search after this and let me know.

Suda

[former_member583013]

Xavier,

Please let me know if this worked...

Suda

[Former Member]

Hi Suda!

Sorry, but it didn't work.

I check the values of the columns that you told me.

All were enable, but only Total Needed and Open Quantity has a value, the other columns not has a value.

The performance of the Formatted Search is the same, it now generate a value, just generate in the User Defined Table an empty field.

Thanks in advance.

Best Regards

[former_member583013]

Hi,

For using Field reference in formatted search, go through the document on Formatted Searches on this page

<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/businessone-elearning">https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/businessone-elearning</a>

In the meanwhile, I will analyse what might be going wrong when you try the code and will come back later today

Suda

[former_member583013]

Xavier,

This made it a complex SQL but I am giving it to you so that you can decide on it.

You will have to create a table in SQL Server

CREATE TABLE [dbo].[TBATCH](

[BatchNum] [nvarchar](32))

Then replace my previous code with this one.

IF ($[$35.0] = 1 AND $[$35.39] = $[$35.44])

BEGIN

SELECT MAX(CAST(T0.BatchNum AS INT))+1 AS 'Batch Number' FROM [dbo].[OIBT] T0 WHERE T0.ItemCode = N'B'

DELETE FROM TBATCH

INSERT INTO TBATCH (BATCHNUM) (SELECT MAX(CAST(T0.BatchNum AS INT))+1 AS 'Batch Number' FROM [dbo].[OIBT] T0 WHERE T0.ItemCode = N'B')

END

ELSE IF ($[$35.0] = 1)

BEGIN

SELECT MAX(CAST(T0.BatchNum AS INT))1$[$35.43] AS 'Batch Number' FROM [dbo].[OIBT] T0 WHERE T0.ItemCode = N'B'

INSERT INTO TBATCH (BATCHNUM) (SELECT MAX(CAST(T0.BatchNum AS INT))1$[$35.43] AS 'Batch Number' FROM [dbo].[OIBT] T0 WHERE T0.ItemCode = N'B')

END

ELSE

BEGIN

SELECT MAX(CAST(BATCHNUM AS INT))+1 FROM TBATCH

INSERT INTO TBATCH (BATCHNUM) (SELECT MAX(CAST(BATCHNUM AS INT))+1 FROM TBATCH)

END

We can keep improving based on the requirement. But this should work

Suda

[Former Member]

Hi Suda!

I did that you said me, i created the table with the command directly in the SQL Management and replaced the previous code with you sent me.

But when i try to run the Formated Search with the new code, it did not display the consecutive number, in fact doesn't appear anything ...

When i check the new Table with a SELECT * FROM TBATCH and display empty fields.

I don't know if i did something wrong or something happen with the query.

Hope you can help me and again thanks a lot

One More thing, Where can i found information about how can i use variables in SAP, i mean i know that i can use the values of the fields in UI typing $[$X1.X2.X3] where X1 is the Item, X2 is Column and X3 is the row , but i know that i can use the number of Form and the number of Variable, but how can i use that ....

Exist a document that explain that kind of things ? if exists can you tell me please.

Thanks in advance !

Best Regards!

[former_member583013]

Xavier,

I am not sure what stage of the project you are in but I have a partial solution for you.

I have not tried with Padding '000' but my recommendation is to start with a

'1000000'. Padding '0000' adds a new dimension of complexity and enormous code.

Paste the following SQL and create a formatted seach and attach it to the Batch Number field in the Batches - Setup window. This solution is so far designed for receiving only 1 item per goods receipt. I am testing receiving multiple item in one goods receipt and still making it work.

IF $[$35.0] = 1 AND $[$35.39] = $[$35.44]

SELECT MAX(CAST(T0.BatchNum AS INT))+1 AS 'Batch Number' FROM [dbo].[OIBT] T0

ELSE

SELECT MAX(CAST(T0.BatchNum AS INT))1$[$35.43] AS 'Batch Number' FROM [dbo].[OIBT] T0

NOTE: THE ABOVE SQL WILL WORK IN YOUR SCENARIO

"I mean for Item A that has 3 in Quantity Field i want to generate a batch number 001205 if in this Batch user put all the Quantity there (3).

But if the user decide that in batch number 001205 put only 1 Quantity in the other row of batch i want to generate the batch number 001206 and put the rest of Quantity (2)"

Let me know what you think

Suda

[Former Member]

Hey Suda.

Thanks, i test the query and works great, but i have some trouble with this, because the problem is that.

This query only is so far designed for receiving only 1 item per goods receipt.

I see that is necessary that the Query work with many items per goods receipt.

I don't know if exists a way to create a counter inside the query to get the designed for receiving many items per goods receipt

something like

SET @n = 1

IF $[$35.0] = @n AND $[$35.39] = $[$35.44]

SELECT MAX(CAST(T0.BatchNum AS INT))+1 AS 'Batch Number' FROM [dbo].[OIBT] T0

ELSE

SELECT MAX(CAST(T0.BatchNum AS INT))1$[$35.43] AS 'Batch Number' FROM [dbo].[OIBT] T0

@n = @n + 1

Thanks in advanced Suda, you help me so much

Best Regards

[former_member583013]

Xavier,

I understand it now. But what if you have another item B with Quantity 5 will it get start with batch 001207 OR each and every Item has a different batch number series you want to maintain.

Please clarify

Suda

[Former Member]

Ok if exists an Item B with Quantity 5 it will get start with batch 001207,

i mean is only a batch number series, but consecutive for each quantity of Item

Thanks in advance !

Regards

Xavier