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