outgoing payment query

[Former Member]

Hello everybody,

Is there any way to calculate the duedate of a check I want to create in outgoing payment object according to some a/p invoices that had marked in the outgoing payment.

I want to calculate duadate of three a/p invoices. every invoice has a diferent total value and different duedate.

I tried to create a FMS with a UDF , but I don't know how to get the marked records from VPM2 before the outgoing payment is saved.

Thank you all,

Joseph

[Former Member · Accepted solution]

Hi Joseph,

you can try this query and see if you get the desired result. Let me know.

select T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T1.CheckNum,T1.DueDate 'Check due date', T1.CheckSum, T1.BankCode + ' ' + T1.Branch + ' ' + T1.AcctNum Account

from ovpm T0 inner join vpm1 T1 on T1.DocNum = T0.DocEntry

inner join jdt1 T2 on T2.TransId = T0.TransId

inner join dsc1 T3 on T3.BankCode = T1.BankCode and T3.Branch = T1.Branch and T3.Account = T1.AcctNum

where T0.Canceled = 'N'

and T2.Ref3Line = T1.CheckNum

and T2.ExtrMatch = 0

and T2.Account = T3.GLAccount

order by T0.DocNum, T1.CheckNum

however, this query will get you a list of all the outgoing payments done!!

Best Regards,

Joseph Antony

[Former Member · Accepted solution]

Hi Joseph

I try to calculate the duedate of check before I add the outgoing payment.

The aim is paying according to payments terms, So I need to calculate the duedate before I save the outgoing payments.

I tried to get the list of documents that were marked by using query (SELECT $[$20.0.0]) but it return the last record appear in the current outgoing payment neither its was marked or not .

I would like to have a query the return the marked records (only the docnum and object type) afterward I'll write the rest query.

Thank you

[Former Member · Accepted solution]

You could try this one:

SELECT T0.DocNum FROM DBO.OPCH T0 WHERE T0.NumAtCard = -$[$20.2.0\]

However, keep in mind that the line level data would not be accessible except the first line.

Thanks,

Gordon