Add ODLN DLN1 table to script

[Former Member]

HI Forum,

How can i add Delivery notes to the following script?...

We have 2 locations one uses Deivery notes the main location doesn't yet... i need to print a report with all transactiones and i am missing invoices..

Kennedy helped me with following script... i just can't get to capture all data needed. Thanks.

SELECT Distinct T0.DocNum 'SO#', T0.DocDate 'SO Date',T2.DocDate 'Invoice Date'

FROM ORDR T0

INNER JOIN INV1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'

INNER JOIN OINV T2 ON T2.DocEntry=T1.DocEntry

Regards,

Manuel Roman

[kothandaraman_nagarajan]

Hi Manuel Roman,

Try this query:

SELECT T1.DocNum 'SOOrder',T1.[DocStatus]'SOStatus', T0.Quantity 'SOQty',T2.Quantity 'Delivered QTY',T0.OpenQty 'BalQty', T3.DocNum 'Delivery#',T3.[DocDueDate]'DelDate', T5.DocNum 'Bill#'
, T5.DocDate 'Bill date'
From RDR1 T0 inner join ORDR T1 on T0.DocEntry=T1.DocEntry

   left join DLN1 T2 on T2.Basetype=17 and T2.BaseEntry=T0.DocEntry and T2.BaseLine=T0.LineNum

   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 ORDER BY  T3.[DocDueDate]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Manuel,

Try:

SELECT Distinct T0.DocNum 'SO#', T0.DocDate 'SO Date',T1.DocEntry,T2.DocNum 'Invoice Number',T2.DocDate 'Invoice Date'

FROM ORDR T0

LEFT JOIN DLN1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'

LEFT JOIN INV1 T2 ON (T2.BaseEntry=T0.DocEntry AND T2.BaseType='17') OR (T2.BaseEntry=T1.DocEntry AND T2.BaseType='15')

INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry

Thanks,

Gordon

[KennedyT21]

Hi Manuel Roman...

Try This

SELECT Distinct t0.docnum as 'Sales Order',T3.docnum as 'Delivery Note',T5.DocNum as 'Invoice', T3.docdate as 'D/Note Date',T3.docstatus as 'Open/Closed',t0.numatcard as 'Customer Ref No.', T1.subcatnum as 'Customer No',T1.Itemcode as 'Item Code',T1.dscription as 'Description', T1.quantity as 'Quantity',T1.price as 'Price',T1.linetotal as 'Total Value'

FROM ORDR T0

INNER JOIN  rdr1 T1 on T0.docentry = T1.docentry

INNER JOIN  dln1 T2 on T2.baseentry = T0.docentry and t1.linenum = t2.baseline

INNER JOIN  odln T3 on T3.docentry = T2.docentry

LEFT JOIN INV1 T4 ON T4.BaseEntry=T3.DocEntry

LEFT JOIN OINV T5 ON T5.DocEntry=T4.DocEntry

WHERE t0.cardcode = '[%0]'

ORDER BY T0.docnum,T2.docentry

Regards

Kennedy

[Former Member]

Hi Kennedy,

Thanks for your prompt response.

What i actually need to do is print all orders and the invoices and Delivery Documents link to the orders.

order number     Invoice number     delivery Number

123                    123

124                    125

125                    180                            1

159                    200

250                    280                            2

and so on...

Right now i am only getting order with delivery notes.

I need to get al orders with and without delivery notes and all invoices.

Regards,

Manuel Roman

[Former Member]

The reason is that one location is working with delivery notes and the main location doesn't use delivery notes yet. They enter the sales order and invoiced from the sales order.

Regards,

Manuel

[KennedyT21]

Hi Manuel Roman...

Okay I get , Try this way

SELECT Distinct T0.DocNum 'SO.No', T0.DocDate 'SO Date',T1.DocEntry as DeliveryNo,T2.DocNum 'Invoice Number',T2.DocDate 'Invoice Date'

FROM ORDR T0 LEFT JOIN DLN1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'

LEFT JOIN INV1 T2 ON (T2.BaseEntry=T0.DocEntry AND T2.BaseType='17') OR (T2.BaseEntry=T1.DocEntry AND T2.BaseType='15')

INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry

Hope Helpful

Regards

Kennedy