Linking ORDN to ORIN

[Former Member]

Dear Experts,

Could you please help to modify below code to add RIN1.[Price]?

Also please help to add to the query Return deliveries where credit memo is missing (RDN1.[TrgetEntry] IS NULL)

SELECT

T0.[DocNum], T0.[DocDate], T1.[ItemCode],T1.[Quantity],  T3.[DocNum]

FROM

ORDN T0  INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry left join RIN1 T2 on  T1.[TrgetEntry]  =  T2.docentry INNER JOIN ORIN T3 ON T2.DocEntry = T3.DocEntry

Thanks & Regards,

Yaroslav

[Former Member · Accepted solution]

Hi,

You can try:

SELECT

T0.[DocNum], T0.[DocDate], T1.[ItemCode],T1.[Quantity],  T3.[DocNum],T2.Price

FROM

ORDN T0

INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry

left join RIN1 T2 on T1.[TrgetEntry]  =  T2.docentry and T2.BaseLine=T1.LineNum

left JOIN ORIN T3 ON T2.DocEntry = T3.DocEntry

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Thank you so much. This is exactly what I need.

[former_member184146]

Hi ,

     Try this

SELECT

DISTINCT  'Goods Return ' as  [DocType] ,A.[DocNum], A.[DocDate], A1.[ItemCode],A1.[Quantity],A1.Price

FROM

ORDN A  INNER JOIN RDN1 A1 ON A.DocEntry = A1.DocEntry

LEFT JOIN DLN1 B1 ON B1.TrgetEntry=A1.BaseEntry

LEFT JOIN INV1 C1 ON C1.BASEENTRY=A1.TrgetEntry

LEFT JOIN RIN1 D1 ON D1.BaseEntry=C1.TrgetEntry

LEFT JOIN ORIN  D ON D.DOCENTRY=D1.DOCENTRY

union all

SELECT

DISTINCT  'Credit Memo' as  [DocType] ,D.[DocNum], D.[DocDate], D1.[ItemCode],D1.[Quantity],D1.Price

FROM

RIN1 D1

INNER JOIN ORIN  D ON D.DOCENTRY=D1.DOCENTRY

Regards,

Manish