Base Document Number Need to fetch in Crystal report Header ....

[Former Member]

Hi Experts,

    I am creating one crystal report for A/R Invoice.

          Client asked me to fetch the Sales Order Number & delivery Number in A/R Invoice Crystal report Header. Please Provide the query to get the base document  Number.

Thanks

Sudhir.J

[Former Member · Accepted solution]

Hi Sudhir,

Try:

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,

Gordon

[kothandaraman_nagarajan]

Hi,

If you got answer, please close this thread by marking helpful/correct answer.

Thanks & Regards,

Nagarajan

[Former Member]

how to mark ??

[kothandaraman_nagarajan]

Hi,

Choose accordingly in each reply:

Thanks & Regards,

Nagarajan

[Former Member]

Dear Nagarajan K,

Thank You

[Former Member]

Thanks Gorgon Du,

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T1.docnum as SO#, t3.docnum as DO#

FROM RDR1 T0  INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry left join DLN1 T2 on  T2.[BaseEntry]  = T0.docentry and  T2.[BaseLine]  =  T0.[LineNum] and T2.[BaseType] = 17 left JOIN ODLN T3 ON T2.DocEntry = T3.DocEntry left join INV1 T4 on (T4.Basetype=15 and T4.BaseEntry=T2.DocEntry and T4.BaseLine=T2.LineNum) or (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum) left JOIN OINV T5 ON T4.DocEntry = T5.DocEntry

Thanks & Regards,

Nagarajan