on 06-07-2011 10:45 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
--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.)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.