AR Credit Memo

[Former Member]

select T0.DocEntry,

T0.DocNum,

T0.DocDate,

T0.Series,

(select SUM (T4.Linetotal) from INV1 T4 where T4.DocEntry = T0.DocEntry) 'Total before Tax',

(Select MAX(T6.state) from CRD1 T6 inner join OCRD T7 on T6.CardCode = T7.CardCode and T7.CardCode = T0.CardCode)'State',

(select max(T2.TaxId11) from CRD7 T2 where T2.CardCode = T0.CardCode) 'TIN No',

(Select T1.SeriesName from NNM1 T1 where T1.Series = T0.Series and T1.ObjectCode ='13')

'SeriesName',

T0.DocNum,

(select T4.DocNum from ORIN T4 left join RIN1 T5

on T4.DocEntry = T5.DocEntry inner join INV1 T2 on

T5.BaseEntry = T2.DocEntry and T2.DocEntry = T0.DocEntry group by T4.DocNum ) 'AR Credit Memo',

(select SUM(T5.Quantity) from ORIN T4 left join RIN1 T5

on T4.DocEntry = T5.DocEntry inner join INV1 T2 on

T5.BaseEntry = T2.DocEntry and T2.DocEntry = T0.DocEntry group by T4.DocNum ) 'AR Credit Memo QTY',

(select SUM(T4.Quantity) from INV1 T4 where T4.DocEntry = T0.DocEntry) 'Total QTY',

T0.CardCode

from OINV T0

in this query AR crddit memo no is coming ok, but Quantity not coming correctly. Please help

[Former Member · Accepted solution]

Hi Shruti........

Try this........

select T0.DocEntry,
T0.DocNum,
T0.DocDate,
T0.Series,
(select SUM (T4.Linetotal) from INV1 T4 where T4.DocEntry = T0.DocEntry) 'Total before Tax',

(Select MAX(T6.state) from CRD1 T6 inner join OCRD T7 on T6.CardCode = T7.CardCode and T7.CardCode = T0.CardCode)'State',

(select max(T2.TaxId11) from CRD7 T2 where T2.CardCode = T0.CardCode) 'TIN No',
(Select T1.SeriesName from NNM1 T1 where T1.Series = T0.Series and T1.ObjectCode ='13')
'SeriesName',

T0.DocNum,

(select T4.DocNum from ORIN T4 left join RIN1 T5
on T4.DocEntry = T5.DocEntry inner join INV1 T2 on
T5.BaseEntry = T2.DocEntry and T2.DocEntry = T0.DocEntry group by T4.DocNum ) 'AR Credit Memo',
(IsNull((Select Sum(T2.Quantity) From RIN1 T2 Where T1.DocEntry=T2.BaseEntry),0)) 'ARCreditQty',

(select SUM(T4.Quantity) from INV1 T4 where T4.DocEntry = T0.DocEntry) 'Total QTY',
T0.CardCode

from OINV T0 Inner Join INV1 T1 On T0.DocEntry=T1.DocEntry
Group By T0.DocEntry,
T0.DocNum,
T0.DocDate,
T0.Series, T0.DocEntry, T0.CardCode, T1.DocEntry

Hope this will help you.......

Regards,

Rahul