cancel
Showing results for 
Search instead for 
Did you mean: 

batch number in delivery note report

former_member193355
Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member186095
Active Contributor
0 Kudos

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,

Answers (5)

Answers (5)

former_member193355
Contributor
0 Kudos

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,

former_member186095
Active Contributor
0 Kudos

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-

former_member583013
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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]'

zal_parchem2
Active Contributor
0 Kudos

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

former_member583013
Active Contributor
0 Kudos

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

former_member193355
Contributor
0 Kudos

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