Skip to Content
0
Aug 08, 2016 at 05:00 AM

How to link ivoice to incoming payment to payment on account

34 Views

Hi Experts,

Actually we are doing incoming payments on Invoice doc based but sometimes when down payments are done we do payment on account. Now i need a query how to relate invoice to payment of account.

SELECT T0.[DocNum] as [InvNo] , Convert(varchar(10),T0.[DocDate],103)as [InvDate] , T0.U_Connam, T0.[CardName]as[CustomerName],

(T0.[DocTotal]-T0.[Vatsum]-T0.[DiscSum])'Before Dicsum& Tax', T0.[DocTotal]as[InvTotal],T0.ReceiptNum as [IncomingPaymentNo],

T0.[PaidSum],T0.[DocTotal]-T0.[PaidSum]'Balance', DATEDIFF(Day, T0.[DocDate],T1.[DocDate])AS [Diff in Days]

FROM OINV T0 Left Outer Join ORCT T1 ON T0.ReceiptNum = T1.DocEntry

WHERE T0.[DocDate]>='2011/04/01' AND T0.[DocDate]<= '2016/07/31' AND T0.[U_CloseByAdmin]='NO' AND T0.[Series]='68'

AND t0.U_wrong is null

GROUP BY T0.[DocNum] , T1.[DocDate],T0.[DocDate],T0.[PaidToDate], T0.[DiscSum],T0.U_Connam,T0.[CardName],

T0.[Vatsum],T0.[DocTotal], T0.ReceiptNum ,T0.[PaidSum]

Having (T0.[DocTotal]-T0.[PaidSum]) > 0

Order by T0.[CardName]

My Output for the above Query

inv no inv Date CardName Inv.Doctotal IncPay No Incoming Payment total

11579 26/05/2016 GEMINI TEXTILE 519121.231600 529502.000000 NULL 0.000000

11584 01/06/2016 GEMINI TEXTILE 519119.719600 529502.000000 NULL 0.000000

Fro the above to invoice Incoming payment payment on account has done so now it is showing balance and we are not raising any AR down payment

Please anyone help me with this query

regards

Jeeny