cancel
Showing results for 
Search instead for 
Did you mean: 

Query Help

Former Member
0 Kudos

Experts,

select T0.DocEntry, T0.DocNum, T1.ItemCode

,T1.Dscription, T1.Quantity,

T2.ItemCode, T2.Quantity

from OINV T0 inner join INV1 T1 on T0.DocEntry = T1.Docentry where

T0.DocNum = '12105744' inner join ODLN T2 on T1.BaseEntry = T2.

what condition to give to complete this query with "Return " too

Means details of one item in terms of AR invoice, Delivery and return

Plz help

Accepted Solutions (1)

Accepted Solutions (1)

former_member204969
Active Contributor
0 Kudos

To correctly connect the source record, you should specify the BaseType too. For the invoice-delivery connection you could use this:

Select T0.DocEntry, T0.DocNum,
 T1.ItemCode,T1.Dscription, T1.Quantity,
 T2.ItemCode, T2.Quantity
From OINV T0 inner join INV1 T1 on T0.DocEntry = T1.Docentry
 inner join DLN1 T2 on T2.BaseEntry = T1.DocEntry and T2.BaseLine=T1.LineNum 
                        and T1.BaseType=15

Former Member
0 Kudos

How to add return too, means for one item

invoice->delivery->return

former_member204969
Active Contributor
0 Kudos

--To follow the delivered goods you can start from this query:

Select         DD.DocNum'Delivery',D.DocDate,D.Quantity,
                  RR.DocNum'Return',R.DocDate,R.Quantity,
                  II.DocNum'Invoice',I.DocDate,I.Quantity,
		  CC.DocNum'Cred.N',C.DocDate,C.quantity
From  DLN1 D inner join ODLN DD on D.DocEntry=DD.DocEntry
   left outer join Inv1 I on (I.Basetype=15 and I.BaseEntry=D.DocEntry and I.BaseLine=D.LineNum)
   left outer join OINV II on I.DocEntry=II.DocEntry
   left outer join Rdn1 R on (R.Basetype=15 and R.BaseEntry=D.DocEntry and R.BaseLine=D.LineNum)
   left outer join ORDN RR on R.DocEntry=RR.DocEntry  
   left outer join RIN1 C on (C.Basetype=15 and C.BaseEntry=D.DocEntry and C.BaseLine=D.LineNum)
                          or (C.Basetype=13 and C.BaseEntry=I.DocEntry and C.BaseLine=I.LineNum)
   left outer join ORIN CC on CC.DocEntry=C.DocEntry

(This shows the credit notes too.)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Rupa......

Try this.......

Select T0.DocEntry, T0.DocNum, T1.ItemCode
,T1.Dscription, T1.Quantity,
T2.ItemCode, T2.Quantity
from OINV T0 inner join INV1 T1 on T0.DocEntry = T1.Docentry 
LEFT join DLN1 T2 on T1.BaseEntry = T2.DocEntry LEFT JOIN 
ODLN T3 On T3.DocEntry=T2.DocEntry LEFT JOIN RDN1 T4 On T4.BaseEntry=T2.DocEntry
LEFT Join RIN1 T5 On T5.BaseEntry=T1.DocEntry
Where T0.DocNum=12105744

Regards,

Rahul

Former Member
0 Kudos

Hi Rupa,

Try This

SELECT T1.DOcnum 'SO Number',T1.DocDate 'So Date', t1.CardCode 'Customer Code',t1.cardname 'Customer Name', t0.itemcode,T0.Dscription 'Item Description',T0.QUANTITY 'SO Qty',

t3.docnum 'Delivery No',T3.DocDate 'Del Date', t2.quantity 'Del Qty',

t5.docnum 'Invoice No',t5.DocDate 'Inv Date',t4.quantity 'Inv Qty',

t8.docnum 'Credit Memo No',t8.DocDate 'Credit Memo Date',t7.quantity 'Credit Memo Qty'

FROM DBO.rdr1 T0

INNER JOIN OITW T6 ON T6.ItemCode = T0.ItemCode AND T6.WhsCOde = T0.WhsCode

INNER JOIN ORdr T1 ON T1.DOCENTRY = T0.DOCENTRY

LEFT JOIN dln1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T2.BaseLine = T0.LineNum

LEFT JOIN OdlN T3 ON T2.DOCENTRY = T3.DOCENTRY

left join inv1 t4 on t4.baseentry = t2.docentry and t4.baseline = t2.linenum

left join oinv t5 on t4.docentry = t5.docentry

left join rin1 t7 on t7.baseentry = t4.docentry and t7.baseline =t4.linenum

left join orin t8 on t7.docentry = t8.docentry

WHERE T1.DocDate between '[%0]' and '[%1]'

Thanks,

Srujal Patel