cancel
Showing results for 
Search instead for 
Did you mean: 

Creditors and Debtors Aging Report With reference to A UDF (Date)

former_member609283
Participant
0 Kudos

Hello Everyone,

     I have came across a situation that I want Aging report to be run with reference of UDF which is a date field call "Acknowledge Date" not from Due Date.

The UDF is created in AP Invoice and AR Invoice form, Now when the document is added this field would be empty or today date can be entered, but after some time when Customer or Vendor acknowledge that they have got the invoice then this field would be updated to that particular date and then on the basis of that date Aging report will run.


PLease share the solution, how can I solve this problem as Aging report can be extracted from OJDT and JDT1 and my UDFs are in AP and AR Invoice Form so when I update the acknowledge date it will update the Date in the UDF also created in Journal Entry Form and then Aging can be run with reference to that date.



Thanks in advance

Accepted Solutions (0)

Answers (4)

Answers (4)

rajesh_khater
Active Participant
0 Kudos

Instead of using a UDF in Journal Entry and the FMS approach, cant you directly change the Due Date in the Journal Entry?

Former Member
0 Kudos

Hi,

Not sure if this would work as when you add the Invoice, the JE would be posted.

Instead of updating the udf, update the document date field and then run the system ageing report by document date.

Thanks,

Joseph

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

1. Assign below FMS at journal entry UDF

SELECT T0.[U_rgJobno] FROM OPCH T0 WHERE T0.[DocNum]  = $[OJDT.baseref]

2. Set auto refresh and select document date for auto refreshing.

Please note that, the UDF field journal entry will be updated only when adding document. After any changes in AP UDF, user need to update journal entry UDF by clinking user defined value icon.

Thanks & Regards,

Nagarajan

javier_facessantos
Contributor
0 Kudos

Hello Idrees

Check this tread:

You will need to use your "Acknowledge Date" instead of the DueDate in the query proposed by Kennedy.

Regards

former_member609283
Participant
0 Kudos

Hi Javier,

     At the time of document entry, the Acknowledge date is not defined but afterwards it will update so do I have to update Journal Entry as well to get the required result???? If so then How can I pass UDF from AP and AR Invoice to JE??

Regards,

Idrees

javier_facessantos
Contributor
0 Kudos

Hello Idrees

I am not sure I understood correctly, but I assume that you don't have the UDF created on OJDT or JDT1 table.

In that case you can create another UDF in OJDT table and bring the value from OINV using a formatted search.Then use that new UDF in the query.

Other option is to bring OINV table to the query using following JOIN:

OJDT T0  INNER JOIN OINV T1 ON T0.TransId = T1.TransId

But I am not sure how could this affect query performance. But you can just try.

Regards

former_member609283
Participant
0 Kudos

Hey Javier,

     I have a query below that works fine but is not cattering Partial Payments, like if I do Incoming payment partially of an invoice, it simply took off that invoice from the aging as it has to be there untill it is closed.

SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',

CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document Type',

     T2.[U_AcknowDueDate], (T0.Debit- T0.Credit) 'Balance'

,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())<=-1),0) 'Future'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>=0 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=30),0) 'Current'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>30 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=60),0) '31-60 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())>60 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=90),0) '61-90 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())>90 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=120),0) '91-120 Days'

,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>=121),0) '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

INNER JOIN OJDT T2 ON T2.Transid=T0.TransID

WHERE (T0.MthDate IS NULL OR T0.MthDate >getdate()) AND T0.RefDate <= getdate() AND T1.CardType = 'C'

ORDER BY T1.CardCode, T2.[U_AcknowDueDate], T0.Ref1

How can I do that??

Regards,

Idrees