help with field or table in payments

[Former Member]

there is a field or table that show if a customer invoice was payed in 2,3 o more payments.??

[former_member212181]

Hi Carlos,

You can pick data from OITR and ITR1

SELECT T2.DocNum, T2.DocDate,  T1.ReconSum,ReconDate [Reco Date],T0.InitObjTyp[reco Type], T0.InitObjAbs [Reco Document Internal Num]

FROM OITR T0

INNER JOIN ITR1 T1 ON T0.[ReconNum] = T1.[ReconNum] and  T1.[SrcObjTyp] =13

Inner Join OINV T2 on T1.[SrcObjAbs] = T2.DocEntry

Please try above query.

Thanks

Unnikrishnan

[Former Member]

You query run but is not the info that i need.......i only want to know number of paymets.

[former_member212181]

Please try below query

Select T0.DocDate, T0.DocNum, T0.DocTotal, T0.DocStatus, Sum(T1.SumApplied)[Incoming payment Sum],Count(T1.DocNum)[No of receipt]

from OINV T0

  Left Outer Join RCT2 T1 on T1.InvType= 13 and T0.DocEntry = T1.DocEntry

  Inner Join ORCT T2 on T1.DocNum = T2.DocEntry and T2.Canceled = 'N'

Where T0.CANCELED = 'N'

  and T0.DocDate>=[%0] and T0.DocDate<=[%1]

Group By T0.DocDate, T0.DocNum, T0.DocTotal, T0.DocStatus

Regards

Unnikrishnan

[pierrecanali]

Hi Carlos,

INV6 is the table of the installments

ORCT and RCT2 are the incoming payments

because an incoming payment is not the only way to close the Invoice you should also consider JDT1 (journal entries) and OITR and ITR1 (reconciliations) to get what you want.

Regards

Pierre

[Former Member]

I want to know or make a query that give me number of payments....but you are rigth, let me check those tables for a new query.