Skip to Content
0
Oct 12, 2020 at 04:58 PM

Need to connect Ar Cr meno connected/Linked with Ar Down Payment

40 Views

hi expert

I need a help to link Ar Cr memo which is linked with Ar Down Payment in the same way how Ar Down Payment is linked with Ar invoice

Pls help me to link the document

Am attaching the existing query

Regards

Vinod

SELECT Distinct 

T3.[DocNum] 'AR DP Number',T3.[CardName]'Customer Name',T3.[DocDate] 'AR DP Date',SUM(T4.[PriceBefDi])'Total Invoice VALUE',T3.[VatSum],T3.[DocTotal] 'AR DP Doc Total',

T3.[DpmPrcnt] 'DP %', case when T3.[CANCELED] = 'N' THEN 'Not Cancal' Else 'Cancel' End as 'Cancel/Not Cancel',

T0.[DocNum] 'AR Inv Number',T0.[CardName]'Customer Name', T0.[DocDate]'AR Inv Date',T0.[VatSum],T0.[DpmAmnt] 'AR Inv Doc Total', 

Case When T0.[CANCELED] ='N' THEN 'Not Cancel' When T0.[CANCELED]= 'C' THEN 'Closed' Else 'Cancel' End as 'Cancel/Not Cancel'

FROM OINV T0  

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

INNER JOIN INV9 T2 ON T0.DocEntry = T2.DocEntry 

Right JOIN ODPI T3 ON T2.BaseAbs = T3.DocEntry

Right JOIN DPI1 T4 ON T3.DocEntry = T4.DocEntry

-- Where T3.DocNum = /*4000138  4002385 13000001 */5000071

 where T3.[DocDate] between '2019-01-01 00:00:00.000' and '2019-12-31 00:00:00.000'

 Group by T3.[DocNum],T3.[CardName],T3.[DocDate],T3.[VatSum],T3.[DocTotal] ,T3.[DpmPrcnt] ,

T0.[DocNum] ,T0.[CardName], T0.[DocDate],T0.[VatSum],T0.[DpmAmnt],T3.[DocStatus],T0.[DocStatus],T3.[CANCELED],T0.[CANCELED]