linking tables

[Former Member]

Dear All,

How can i link ORDR with OINV directly? how many invoices was derived from an SO.

Appreciate your SQL experts advice.

Thanks.

Harith

[Former Member]

Dear SQL experts,

Thank you for the all the comments & contributions. I think I can manage it from here...thanks a lot guys!!!

rgds,

Harith

[former_member325312]

Hi Harith

here is ur quey for SO ...>Delivery....>Invoice

i think this will solve ur prob

SELECT T0.[DocNum]'SO NO', T0.[DocDate], T0.[CardCode], T0.[CardName],

T1.[ItemCode], T1.[Dscription], T1.[Quantity], T0.[DocTotal],

T2.[DocNum]'Delivey No', T2.[DocDate], T2.[CardCode], T2.[CardName],

T3.[ItemCode], T3.[Dscription], T3.[Quantity], T2.[DocTotal],

T4.[DocNum]'Invoice No', T4.[DocDate], T4.[CardCode], T4.[CardName],

T5.[ItemCode], T5.[Dscription], T5.[Quantity], T4.[DocTotal]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry,

ODLN T2 INNER JOIN DLN1 T3 ON T2.DocEntry = T3.DocEntry,

OINV T4 INNER JOIN INV1 T5 ON T4.DocEntry = T5.DocEntry

WHERE T5.[BaseRef] = T2.[DocNum] and T3.[BaseRef] = T0.[DocNum] and

T5.[ItemCode] = T3.[ItemCode] and T5.[ItemCode] = T1.[ItemCode]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],

T1.[Dscription], T1.[Quantity], T0.[DocTotal], T2.[DocNum], T2.[DocDate],

T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Quantity], T2.[DocTotal],

T4.[DocNum], T4.[DocDate], T4.[CardCode], T4.[CardName],

T5.[ItemCode], T5.[Dscription], T5.[Quantity], T4.[DocTotal]

regards

Jenny

[Former Member]

Hi Harith,

Try this:

SELECT Distinct T0.DocNum 'Invoice#',T1.DocNum 'Sales Order#'

FROM dbo.OINV T0

INNER JOIN dbo.RDR1 T2 ON T2.TrgetEntry = T0.DocEntry AND T2.TargetType = '13'

INNER JOIN dbo.ORDR T1 ON T1.DocEntry = T2.DocEntry

Thanks,

Gordon

[Former Member]

Hi

If the Sales Invoice is derived by following the Sales Quotation->Sales Order->Delivery Order->AR Invoice

order then you can check for the Base Document Number or Base Reference Number of the corresponding

Invoice in the OINV by which you can identify its Sales Order.

[former_member325312]

Hi

please find the below query for SO to Invoice

this query works only when SO is directly copied to i invoice

i think this will solve ur problem if so pls do close the thread

SELECT T0.[DocNum]'SO No', T0.[DocDate], T0.[CardCode], T0.[CardName],

T1.[ItemCode], T1.[Dscription], T1.[Quantity]'SO Qty', T1.[LineTotal], T0.[DocTotal]'SO Total',

T2.[DocNum]'Invoice No', T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode],

T3.[Dscription], T3.[Quantity]'Invoce Qty', T3.[LineTotal], T2.[DocTotal]'Invocie Total'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry,

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

WHERE T3.[BaseRef] = T0.[DocNum] and T3.[ItemCode] = T1.[ItemCode]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],

T1.[Dscription], T1.[Quantity], T1.[LineTotal], T0.[DocTotal], T2.[DocNum],

T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription],

T3.[Quantity], T3.[LineTotal], T2.[DocTotal]

Regards

Jenny

[Former Member]

hi Jenny,

Thanks for the feedback. Unfortunately, my SO are mostly copied to 'Delivery' 1st then only invoice. Any idea from here?

Thanks.

Harith

[former_member1269712]

Hi Harith,

Try this...

SELECT T0.DocNum'SO No', T0.DocDate'SO Date', T0.CardCode, T0.CardName,
T1.ItemCode, T1.Dscription, T1.Quantity'SO Qty', T1.LineTotal, T0.DocTotal'SO Total',
T4.DocNum'Invoice No', T4.DocDate'Invoice Date',T3.Quantity'Invoce Qty',T3.LineTotal, T4.DocTotal'Invocie Total'
FROM ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN DLN1 T2 ON T1.DocEntry = T2.BaseEntry and T1.Linenum = T2.Baseline
INNER JOIN INV1 T3 ON T2.DocEntry = T3.BaseEntry and T2.Linenum = T3.Baseline and T2.ObjType=T3.basetype
INNER JOIN OINV T4 ON T3.DocEntry = T4.DocEntry
union all
SELECT T0.DocNum'SO No', T0.DocDate'SO Date', T0.CardCode, T0.CardName,
T1.ItemCode, T1.Dscription, T1.Quantity'SO Qty', T1.LineTotal, T0.DocTotal'SO Total',
T4.DocNum'Invoice No', T4.DocDate'Invoice Date',T3.Quantity'Invoce Qty',T3.LineTotal, T4.DocTotal'Invocie Total'
FROM ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN INV1 T3 ON T1.DocEntry = T3.BaseEntry and T3.Linenum = T3.Baseline and T1.ObjType=T3.basetype
INNER JOIN OINV T4 ON T3.DocEntry = T4.DocEntry

Thanks

Sachin