Skip to Content

Query to Invoice batch & expiry

Dear All,

I want to frame a query to get the detail of AR Invoice along with Batch& Expiry, But this gives me all the available batches of the item, pls let me know how I can filter this query to get only the batches which is there in the invoice.

My query is as bellow

SELECT T2.[CardCode], T2.[CardName], T2.[Address], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription],

T5.[DistNumber] BATCHNO,T5.[ExpDate],

T1.[Quantity],

T1.[PriceBefDi], T1.[DiscPrcnt], T1.[Price], T1.[LineTotal],T3.[WhsName]

FROM [dbo].[OINV] T0

INNER JOIN [dbo].[INV1] T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN [dbo].[OCRD] T2 ON T0.[CardCode] = T2.[CardCode]

INNER JOIN [dbo].[OWHS] T3 ON T1.[WhsCode]= T3.[WhsCode]

INNER JOIN [dbo].[IBT1] T4 ON T0.[DocNum] =T4.[BaseNum]

INNER JOIN [dbo].[OBTN] T5 ON T4.[BatchNum]=T5.[DistNumber]

WHERE T0.[DocDate] >=[%0] and

T0.[DocDate] <=[%1] and

T1.[ItemCode] >=[%2] and

T1.[ItemCode] <=[%3]

rgds

Suman

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • May 19, 2015 at 08:51 AM

    Hi Suman,

    Please try below query

    SELECT T0.CardCode, T2.CardName, T2.Address,

    T0.DocNum,T4.BaseNum, T0.DocDate, T1.ItemCode, T1.Dscription,

    T5.DistNumber BATCHNO,T5.ExpDate,

    T1.Quantity,

    T1.PriceBefDi, T1.DiscPrcnt, T1.Price, T1.LineTotal,

    T3.WhsName

    FROM dbo.OINV T0

    INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode

    INNER JOIN dbo.OWHS T3 ON T1.WhsCode= T3.WhsCode

    Left Outer JOIN dbo.IBT1 T4 ON T4.BaseType=13 and T4.BaseEntry =T1.DocEntry and T4.BaseLinNum = T1.LineNum --T0.DocNum =T4.BaseNum

    Left Outer JOIN dbo.OBTN T5 ON T4.BatchNum=T5.DistNumber

    WHERE T0.DocDate >=[%0] and

    T0.DocDate <=[%1] and

    T1.ItemCode >=[%2] and

    T1.ItemCode <=[%3]

    Thanks

    Unnikrishnan

    Add comment
    10|10000 characters needed characters exceeded

    • Suman Roy Unnikrishnan Balan

      Dear Unnikrishnan,

      My query is still pending to get the axact result, however I have identified the reason, why it is not matching with SAP sale analysis report.

      When we do Ar CN wihout copying from AR invoice or we CN with negative qty they do not appear in this query.

      --------------------------------

      SELECT T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],

      T2.[Quantity], T1.[PriceBefDi] Tradeprice, T1.[DiscPrcnt] Disc, T1.[Price] Netprice, T1.[LineTotal] Value, T1.[WhsCode] , T3.DistNumber as Batch , T3.ExpDate

      FROM OINV T0

      INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

      left JOIN IBT1 T2 ON T2.ItemCode = T1.ItemCode and T0.DocNum = T2.BaseNum and T0.ObjType = T2.BaseType and T1.docEntry= T2.BaseEntry and T1.BaseLine = T2.BsDocLine

      left JOIN OBTN T3 ON T3.ItemCode = T2.ItemCode and T2.BatchNum = T3.DistNumber

      WHERE T0.[DocDate] >= [%0]

      and T0.[DocDate] <= [%1]

      and T1.ItemCode >='[%2]'

      and T1.ItemCode <='[%3]'

      union all

      SELECT T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],

      T2.[Quantity]*-1 Qty, T1.[PriceBefDi] Tradeprice, T1.[DiscPrcnt] Disc, T1.[Price] Netprice, T1.[LineTotal]*-1 Value, T1.[WhsCode] , T3.DistNumber as Batch , T3.ExpDate

      FROM ORIN T0

      INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

      left JOIN IBT1 T2 ON T2.ItemCode = T1.ItemCode and T0.DocNum = T2.BaseNum and T0.ObjType = T2.BaseType and T1.docEntry= T2.BaseEntry and T1.BaseLine = T2.BsDocLine

      left JOIN OBTN T3 ON T3.ItemCode = T2.ItemCode and T2.BatchNum = T3.DistNumber

      WHERE T0.[DocDate] >= [%0]

      and T0.[DocDate] <= [%1]

      and T1.ItemCode >='[%2]'

      and T1.ItemCode <='[%3]'

      and T1.NoInvtryMv = 'N'

      union all ---to include negative CN and CN not copied from AR Invoice

      SELECT T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],

      T2.[Quantity]*-1 Qty, T1.[PriceBefDi] Tradeprice, T1.[DiscPrcnt] Disc, T1.[Price] Netprice, T1.[LineTotal]*-1 Value, T1.[WhsCode] , T3.DistNumber as Batch , T3.ExpDate

      FROM ORIN T0

      INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

      left JOIN IBT1 T2 ON T2.ItemCode = T1.ItemCode and T0.DocNum = T2.BaseNum and T0.ObjType = T2.BaseType and T1.docEntry= T2.BaseEntry and T1.targettype = T2.BsDoctype

      left JOIN OBTN T3 ON T3.ItemCode = T2.ItemCode and T2.BatchNum = T3.DistNumber

      WHERE T0.[DocDate] >= [%0]

      and T0.[DocDate] <= [%1]

      and T1.ItemCode >='[%2]'

      and T1.ItemCode <='[%3]'

      and T1.NoInvtryMv = 'N'

      -----------------------------------

      Regards

      Suman

  • avatar image
    Former Member
    May 20, 2015 at 12:56 AM

    Hi Suman,

    The system report will include Credit Memo as negative total. Check it out if you have any CM.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 22, 2015 at 05:07 PM

    Hi Suman,

    Try this first:

    SELECT T2.[CardCode], T2.[CardName], T2.[Address], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription],

    T5.[DistNumber] BATCHNO,T5.[ExpDate],

    T1.[Quantity],

    T1.[PriceBefDi], T1.[DiscPrcnt], T1.[Price], T1.[LineTotal],T3.[WhsName]

    FROM [dbo].[OINV] T0

    INNER JOIN [dbo].[INV1] T1 ON T0.[DocEntry] = T1.[DocEntry]

    INNER JOIN [dbo].[OCRD] T2 ON T0.[CardCode] = T2.[CardCode]

    LEFT JOIN [dbo].[OWHS] T3 ON T1.[WhsCode]= T3.[WhsCode]

    LEFT JOIN [dbo].[IBT1] T4 ON T0.[DocNum] =T4.[BaseNum]

    LEFT JOIN [dbo].[OBTN] T5 ON T4.[BatchNum]=T5.[DistNumber]

    WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded