Query for batch item and item type text

[Former Member]

Hi All,

I have searched query relates to batch item and find this following one i.e.:

SELECT DISTINCT T2.[BaseRef][No. SO], T4.[SlpName][Sales], T2.[Dscription][Description], CASE WHEN t3.batchnum is null THEN t2.quantity ELSE 0 END [Non Batch Qty],  t2.unitmsr[  ], T3.[Quantity][Batch Quantity], t2.unitmsr[  ], T3.[BatchNum][Batch Code]
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]'

I have used it to customize a PLD report but when I generate a delivery order report that have item type text, for example bonus in the delivery row document, the report did not have it. pls give advice if the query will not include the text and how to add the text that available in the DLN10 table in the query ?

Rajh

[former_member186095 · Accepted solution]

Hi,

try to use this query:

SELECT T2.[BaseRef][No. SO], T4.[SlpName][Sales], T2.[Dscription][Description], t5.linetext, CASE WHEN t3.batchnum is null THEN t2.quantity ELSE 0 END [Non Batch Qty],  t2.unitmsr[  ], T3.[Quantity][Batch Quantity], t2.unitmsr[  ], T3.[BatchNum][Batch Code]
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 dln10 t5 on t0.docentry = t5.docentry and t2.linenum = t5.LineSeq
inner JOIN OCRD T1 ON T0.CardCode = T1.CardCode
inner JOIN OSLP T4 ON T2.SlpCode = T4.SlpCode
WHERE T0.[DocNum] ='[%0]'

I have added the table dln10 there.

Rgds,

[Former Member · Accepted solution]

Tks for your answer. I have checked that your query will not give the info column behind the item code column. I thought the item code that is sold as bonus is in the row after line text box. So, I expect that when I see the text (bonus) row, then the item code row is also the item that is sold as bonus.

What would be the work around to solve this issue ? I need the query like that. So, the item code (bonus) column, linetext column (bonus), etc.

Rajh

[former_member186095 · Accepted solution]

Try this new one:

SELECT T2.[BaseRef][No. SO], T4.[SlpName][Sales], T2.[Dscription][Description], t2.freetxt[Info], CASE WHEN t3.batchnum is null THEN t2.quantity ELSE 0 END [Non Batch Qty],  t2.unitmsr[  ], T3.[Quantity][Batch Quantity], t2.unitmsr[  ], T3.[BatchNum][Batch Code]
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]'

there is a new field replacing the old field (linetext from DLN10 table). The new one is freetxt field. It is field from DLN1 table. So, there is no more DLN10 table. you must use the new field. you can fill it manually or automatically using query FMS.

Rgds,