Skip to Content
author's profile photo
Former Member

Formatted Search to Generate Consecutive Number of Batch

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • Best Answer
    author's profile photo
    Former Member
    Oct 12, 2007 at 12:12 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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!

  • author's profile photo
    Former Member
    Oct 12, 2007 at 05:10 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo
    Former Member
    Oct 14, 2007 at 10:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo
    Former Member
    Oct 16, 2007 at 08:11 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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!

  • author's profile photo
    Former Member
    Oct 17, 2007 at 09:13 PM

    Hi,

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

    https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/businessone-elearningwww.sdn.sap.com/irj/sdn/businessone-elearning">https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/businessone-elearning>

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

    Suda

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Oct 18, 2007 at 10:41 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo
    Former Member
    Jan 21, 2008 at 09:25 PM

    good job

    Add comment
    10|10000 characters needed characters exceeded