Query to Invoice batch & expiry

[former_member186803]

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

[Former Member]

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

[former_member186803]

Hi Gordon,

Thanks for your reply, this will return only invoices, not CNs. This query returns duplicate records.

Regards

Suman

[Former Member]

Try:

SELECT 'Invoice' as Type, T2.[CardCode], T2.[CardName], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode],

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]

UNION ALL

SELECT 'CR Memo' as Type, T2.[CardCode], T2.[CardName], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode],

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

-T1.[Quantity],

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

FROM [dbo].[ORIN]  T0

INNER JOIN [dbo].[RIN1]  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]

[former_member186803]

Dear Gordon,

Thanks for your reply but as I said this query returns duplicate records and not matching with SAP sales analysis report.

Regards

Suman

[Former Member]

Hi Suman,

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

Thanks,

Gordon

[former_member186803]

Hi Gordon,

Thanks for your reply, Yes, I know that and have checked, but still its not matching with my sales analysis report.

Rgds

[former_member212181]

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

[former_member186803]

Hi Unnikrishnan,

Thanks for your reply, now through your query I am not getting all the batches but the total qty sold is not matching with SAP sales analysis report, means somehow it is still not giving correct figure.

I am looking into this .

Regards

Suman

[former_member212181]

Hi Suman,

I am not getting all the batches: Reasons may be

You are creating AR invoices directly and create AR Invoices based on delivery.(Currently we are picking only from Direct AR Invoice scenario)

the total qty sold is not matching with SAP sales analysis report : Reasons may be

2) We are considering only AR Invoices now. if you need to to match quantity with sales analysis report, then you need to consider a)AR Credit Notes, b)Cancelled AR Invoices, c)Cancelled AR Credit Notes, d)AR Credit Notes without Qty posting, e)Single Line in AR  Invoice / AR Credit Memo but multiple batch selection.

Thanks

Unnirkishnan

[former_member212181]

Hi Suman,

Please try below query and let me know the result

SELECT 'AR Invoice'[Type], T0.CardCode, T2.CardName, T2.Address,

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

  Case T1.BaseType When '15' then T8.DistNumber else T5.DistNumber end [BATCHNO],

  Case T1.BaseType When '15' then T8.ExpDate else T5.ExpDate end [Expiry Date],

  Case When T0.CANCELED <> 'C' then T1.Quantity else -T1.Quantity end [Qty],

  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

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

  Left Outer Join DLN1 T6 on T1.BaseType=15 and T1.BaseEntry = T6.DocEntry and T1.BaseLine = T6.LineNum

  Left Outer JOIN dbo.IBT1 T7 ON T7.BaseType=15 and T7.BaseEntry =T6.DocEntry and T7.BaseLinNum = T6.LineNum

  Left Outer JOIN dbo.OBTN T8 ON T7.BatchNum=T8.DistNumber

WHERE T0.DocDate >=[%0] and

  T0.DocDate <=[%1] and

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

  T1.ItemCode <='[%3]'

Union All

SELECT 'AR Credit Memo'[Type], T0.CardCode, T2.CardName, T2.Address,

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

  Case T1.BaseType When '15' then T8.DistNumber else T5.DistNumber end [BATCHNO],

  Case T1.BaseType When '15' then T8.ExpDate else T5.ExpDate end [Expiry Date],

  Case When T1.NoInvtryMv='Y' then 0 When T0.CANCELED <> 'C' then -T1.Quantity else T1.Quantity end [Qty],

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

  T3.WhsName

FROM dbo.ORIN  T0

  INNER JOIN dbo.RIN1  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=14 and T4.BaseEntry =T1.DocEntry and T4.BaseLinNum = T1.LineNum

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

  Left Outer Join RDN1 T6 on T1.BaseType=16 and T1.BaseEntry = T6.DocEntry and T1.BaseLine = T6.LineNum

  Left Outer JOIN dbo.IBT1 T7 ON T7.BaseType=16 and T7.BaseEntry =T6.DocEntry and T7.BaseLinNum = T6.LineNum

  Left Outer JOIN dbo.OBTN T8 ON T7.BatchNum=T8.DistNumber

WHERE T0.DocDate >=[%0] and

  T0.DocDate <=[%1] and

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

  T1.ItemCode <='[%3]'

Order By T0.DocDate

Thanks

Unnikrishnan

[former_member186803]

Hi Unnikrishnan,

Thank for your assistance.

Still same, the total qty from the query is around 29000 for a particular period and from SAP it is around 25000, so there is a quite difference.

Regards

Suman

[former_member212181]

Hi Suman,

Please give me some clues where it gives wrong value, so that i can have a look.

Thanks

unnikrishnan

[former_member186803]

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