Skip to Content
avatar image
Former Member

Query for batch item and item type text

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 21, 2008 at 11:38 AM

    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,

    Add comment
    10|10000 characters needed characters exceeded

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