DA No Misssing

[peddinti_pratap]

Hi All,

please suggested me how to get the Order number(DA No)

select t0.CardName,t0.NumAtCard,t0.[U_DEST] ,t4.SlpName as 'Sales Reference',T0.[U_NearBookPoint],t0.[Address], t0.[Address2],t5.[Cellolar] as 'Contact Ph.',t0.[U_PHONENO],t1.[Dscription], t1.[Quantity],t3.DocNum as 'DA no',t0.DocNum as 'Invoice No',t0.[DocDate] as 'Invoice Date', t0.[DocTotal] as 'Invoice Total', t0.[U_TPNAME], t0.[U_TNO],

t0.[U_DNAME], t0.[U_CNO], t0.[U_VTYPE], t0.[U_LTYPE], t0.[U_LRNO], t0.[U_LRDT] from OINV t0 inner join INV1 t1 on t0.DocEntry=t1.DocEntry left join dln1 t6 on t6.TrgetEntry=t1.BaseEntry left join RDR1 t2 on t2.TrgetEntry=t6.BaseEntry left join ORDR t3 on t3.DocEntry=t2.DocEntry left join oslp t4 on t4.SlpCode=t0.SlpCode LEFT join OCPR t5 on t5.CntctCode=t0.CntctCode where t1.TrgetEntry is null and t0.DocDate='2014/05/26'

thanks,

Pratap

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try to link tables like below:

SELECT Distinct T1.DocNum Invoice#, T3.docnum DO#, t5.docnum SO#

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry

left join DLN1 T2 on T0.[BaseEntry] = T2.docentry and
T0.[BaseLine] = T2.[LineNum] and T0.[BaseType] = 15 left JOIN ODLN T3
ON T2.DocEntry = T3.DocEntry left join RDR1 T4 on (T2.Basetype=17 and
T2.BaseEntry=T4.DocEntry) OR (T0.Basetype=17 AND
T0.BaseEntry=T4.DocEntry) left JOIN ORDR T5 ON T4.DocEntry =
T5.DocEntry

Thanks & Regards,

Nagarajan

[peddinti_pratap · Accepted solution]

Hi Nagarajan,

Thanks for your reply,

actually in my query every thing ok. but one invoice having 2 line items with 1&1(total 2qty) .but in my report quantity showing 1,how to corrct.

Query:

select t0.CardName,t0.NumAtCard,t0.[U_DEST] ,t4.SlpName as 'Sales Reference',T0.[U_NearBookPoint],t0.[Address], t0.[Address2],t5.[Cellolar] as 'Contact Ph.',t0.[U_PHONENO],t1.[Dscription], t1.[Quantity],t3.DocNum as 'DA no',t0.DocNum as 'Invoice No',t0.[DocDate] as 'Invoice Date', t0.[DocTotal] as 'Invoice Total', t0.[U_TPNAME], t0.[U_TNO],

t0.[U_DNAME], t0.[U_CNO], t0.[U_VTYPE], t0.[U_LTYPE], t0.[U_LRNO], t0.[U_LRDT] from OINV t0 inner join INV1 t1 on t0.DocEntry=t1.DocEntry left join RDR1 t2 on t2.TrgetEntry=t1.BaseEntry left join ORDR t3 on t3.DocEntry=t2.DocEntry left join oslp t4 on t4.SlpCode=t0.SlpCode LEFT join OCPR t5 on t5.CntctCode=t0.CntctCode     where t1.TrgetEntry is null and t0.DocDate=[%0] group by t0.CardName,t0.NumAtCard,t0.[U_DEST] ,t4.SlpName ,T0.[U_NearBookPoint],t0.[Address], t0.[Address2],t5.[Cellolar] ,t0.[U_PHONENO],t1.[Dscription], t1.[Quantity],t3.DocNum ,t0.DocNum ,t0.[DocDate] ,  t0.[U_TPNAME], t0.[U_TNO],

t0.[U_DNAME], t0.[U_CNO], t0.[U_VTYPE], t0.[U_LTYPE], t0.[U_LRNO], t0.[U_LRDT],t0.[DocTotal]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please confirm that "DA no" are correctly linked with AR invoice.

Thanks & Regards,

Nagarajan

[peddinti_pratap · Accepted solution]

hi ,

yes in my query DA no are coming correct .i checked that DA no.

thanks

[peddinti_pratap · Accepted solution]

hi all,

In my report one line item is missing how can i correct this issue .

plz check abve screen shots

thanks,

[Former Member · Accepted solution]

Hi Pratap,

If you don't use Distinct in your query then you can see the missing line as well.

Please check your query properly.

Regards,

Rahul

[Former Member · Accepted solution]

Hi ,

That Document have same Itemcode  and Quantity  so take Sum(quantity).

then it will display 2 quantity.

-Rajesh N

[peddinti_pratap · Accepted solution]

hi all,

thanks you all of you for giving valuable answers.

i closed  this issue with István Korös  query:

Select

II.CardName,II.NumAtCard,II.U_DEST,p.SlpName as 'Sales Reference',II.U_NearBookPoint,II.Address,II.Address2,q.[Cellolar] as 'Contact Ph.',II.U_PHONENO,I.Dscription,I.Quantity,OO.DocNum as 'DA no',II.DocNum as 'Invoice No',II.[DocDate] as 'Invoice Date', II.[DocTotal] as 'Invoice Total',II.[U_TPNAME], II.[U_TNO],

II.[U_DNAME], II.[U_CNO], II.[U_VTYPE], II.[U_LTYPE], II.[U_LRNO], II.[U_LRDT]

From OINV II inner join INV1 I On I.DocEntry=II.DocEntry

   left outer join DLN1 D on I.BaseEntry=D.DocEntry and I.BaseType=15 and I.BaseLine=D.LineNum

left outer join ODLN DD on DD.DocEntry=D.DocEntry

   left outer join RDR1 O on (I.BaseEntry=O.DocEntry and I.BaseType=17 and I.BaseLine=O.LineNum)

     or (D.BaseEntry=O.DocEntry and D.BaseType=17 and D.BaseLine=O.LineNum)

left outer join ORDR OO on OO.DocEntry=O.DocEntry left outer join OSLP p on p.SlpCode=II.SlpCode left outer join OCPR q on q.CntctCode=II.CntctCode

    where II.DocDate='2014/05/26' and I.TrgetEntry is null

Please check :

Thanks& Regards,

P.Pratap

[kothandaraman_nagarajan · Accepted solution]

Hi,

Did you check my first reply? same think I already replied. that's why i asked you whether you are getting correct result

Anyway you got answer from other thread.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Pratap

Your query seems fine, I get results on my test db. Try adding t6.BaseEntry to the Select line, and see if the invoice that has a null DA No shows the related DLN Doc Entry. Also try linking the LineNum and BaseLine.

Have you tried the query for a different posting date yet?

Kind regards

Peter Juby