Skip to Content

batch number in delivery note report

Dear All,

This issue made me confusing, a user require a delivery report where it is contains item either has batch no. and doesn't have batch number in one page report. In the current situation (SBO 2005 SP 01 PL 18), both are separated. So, there are 2 reports will be generated by system one is for batch and other is non batch item. Could it be customized to be one report only but contains batch and no batch items ? please give advice. TIA

Rgd,

Steve

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    Posted on Dec 03, 2007 at 01:28 PM

    Hello,

    After I retested the query made by me and others, there is a certain script in the query there that need to be considered to replace with this new one :

    ...

    inner JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

    ...

    to be :

    ...

    inner JOIN OSLP T4 ON T2.SlpCode = T4.SlpCode

    ...

    so, the new query will be:

    SELECT DISTINCT T0.DocNum, t2.itemcode, T2.[BaseRef], T4.[SlpName], T2.[Dscription], CASE WHEN t3.batchnum is null THEN t2.quantity ELSE 0 END [item Non Batch Qty], T3.[Quantity], T3.[BatchNum]

    FROM ODLN T0 INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry

    LEFT OUTER JOIN IBT1 T3 ON T0.DocNum = T3.BaseNum AND T3.ItemCode = T2.ItemCode

    inner JOIN OCRD T1 ON T0.CardCode = T1.CardCode

    inner JOIN OSLP T4 ON T2.SlpCode = T4.SlpCode

    WHERE T0.[DocNum] ='[%0]'

    The previous query will only display sales person from delivery document but if your delivery is copied from more than one sales orders and every sales orders have different sales persons although it is one customer only, then you need the new one I provided.

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 02, 2007 at 11:46 AM

    Steve - you might want to change some JOINS and the order they are joined in your FROM section to read:

    FROM ODLN T0 INNER JOIN DLN1 T2

    ON T0.DocEntry = T2.DocEntry

    LEFT OUTER JOIN IBT1 T3

    ON T0.DocNum = T3.BaseNum

    LEFT OUTER JOIN OCRD T1

    ON T0.CardCode = T1.CardCode

    LEFT OUTER JOIN OSLP

    ON T0.SlpCode = T4.SlpCode

    I cannot get to SAP B1 to test it out, but that change should probably eliminate the batch numbers appearing where they should not be. And your idea about using the Print Layout Designer sounds great - please let us know how you progress in your effort!

    Good Luck - Zal

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2007 at 04:02 PM

    Steve,

    Very valid requirement. There has been various posts on this Forum on the same topic. It is not quite possible to have both of them on one Report layout.

    SAP has recogonized this requiremend and in the SBO 2007 version the System prints this as One Report.

    If you have Upgrading plans to 2007, this requirement will be automatically addressed.

    Best wishes

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

    • Tks for your quick answer. do not your think that query print layout can do that ?

      I created this query :

      SELECT T2.[BaseRef], T4.[SlpName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T3.[BatchNum] FROM [dbo].[ODLN] T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry inner JOIN IBT1 T3 ON T0.docnum = T3.basenum INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode WHERE T0.[DocNum] =[%0]

      and then made print layout designer, where I deleted t3.batchnum and put the field into repetitive area footer1. but it still results in more than one pages, meanwhile I just need to have columns : SO no. Sales Item description batch no. qty, but the weakness are the non batch items are also filled with batch no. in the column.

      Rgd,

      Steve

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 04:47 AM

    Steve,

    I would really love to give you an answer which works but there is not really one. I myself have given up trying as many of my Customers have asked this for many years and finally the 2007 product has the solution.

    You can create the Query Print layout but it has to be printed seperately, which would really make it two steps.

    I have slightly modified the Query, I have added the DISTINCT clause and joined the DLN1 ItemCode and IBT1 ItemCode.

    SELECT DISTINCT T0.DocNum, T2.[BaseRef], T4.[SlpName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T3.[BatchNum]

    FROM ODLN T0 INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry

    LEFT OUTER JOIN IBT1 T3 ON T0.DocNum = T3.BaseNum AND T3.ItemCode = T2.ItemCode

    LEFT OUTER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

    LEFT OUTER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

    WHERE T0.[DocNum] ='[%0]'

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      For quantity I would say it needs to display the quantity from IBT1 instead of DLN1 for the case when there are different batch numbers for the same item.

      Then the query will be:

      SELECT DISTINCT T0.DocNum, T2.[BaseRef], T4.[SlpName], T2.[ItemCode], T2.[Dscription], <b>T3.[Quantity]</b>, T3.[BatchNum]

      FROM ODLN T0 INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry

      LEFT OUTER JOIN IBT1 T3 ON T0.DocNum = T3.BaseNum AND T3.ItemCode = T2.ItemCode

      LEFT OUTER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

      LEFT OUTER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

      WHERE T0.[DocNum] ='[%0]'

  • Posted on Dec 03, 2007 at 11:57 AM

    Hello,

    In addition to Natalija, Suda and other's answer you can use this query:

    SELECT DISTINCT T0.DocNum, T2.[BaseRef], T4.[SlpName], T2.[ItemCode], T2.[Dscription], CASE WHEN t3.batchnum is null THEN T2.Quantity ELSE 0 END[Item Non-Batch Qty], T3.[Quantity], T3.[BatchNum]

    FROM ODLN T0 INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry LEFT OUTER JOIN IBT1 T3 ON T0.DocNum = T3.BaseNum AND T3.ItemCode = T2.ItemCode inner JOIN OCRD T1 ON T0.CardCode = T1.CardCode inner JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode WHERE T0.[DocNum] ='[%0]'

    You must use query print layout designer to make it become a real print out.

    Rgds,

    -hakan ma sukkan-

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 04, 2007 at 01:28 AM

    Hi All,

    tks a lot for the answer, I will test it and ask the user if the query is enough and good for them

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.