FS Query the days of invoice delay

[wojciech_domanski]

Hi Experts,

I need help to create formatted search query to get the number of days of delay most delayed invoice refreshed on BP Code

Thank You

Wojtek

[Former Member · Accepted solution]

Hi,

Please clarify: how do you define the number of days of delay?

Thanks,

Gordon

[wojciech_domanski · Accepted solution]

In the customer balance table is column called "delay" (its hidden by default). I meant in my question to get this information by FS query.

Wojtek

[wojciech_domanski · Accepted solution]

Probably my question is not clear enough, so I attached picture to clarify it. I need just get the number 46. I want to have two information about Customer on sales order current balance and number of overdue days of the oldest invoice. Get Balance is easy it comes directly from OCRD Tables but I can't get the second information.

[wojciech_domanski · Accepted solution]

Could you please comment it, is it to sophisticated or still unclear. I'm confused.

[MukeshSingh · Accepted solution]

Hi Wojciech,

Your question is not clear, can you explain more i think you requirement is

When you want to make Sales order that time you need which oldest invoice still not paid?

select DATEDIFF(Day,min(t11.DocDueDate),SYSDATETIME()) as 'Days' from OINV t11 where t11.CardCode=$[$OINV.4.0.0] and T11.[DocStatus]='O'))

Please try above FMS

Regards

Mukesh Singh

[Former Member]

I have made a query for this. Possible you can use some details for your FMS...

SELECT T2.CardCode AS 'GP-Code', T2.Cardname AS 'GP-Name', T2.DocNum AS 'INV-No.', T2.DocDate AS 'INV-Date', 
T0.DocDate AS 'Date Payment', T3.[PymntGroup], T4.[NumOfDays] AS 'Discount-Days', T3.[ExtraDays] as 'Net-days',
 T4.[Discount], T2.[DocTotal], T1.[SumApplied] AS 'Payment-Amount',  T2.[DocTotal]- T1.[SumApplied] as 'Difference', 
T2.DocTotal * T4.Discount / 100 as 'Discount', T0.DocNum AS 'No.Payment',
 DATEDIFF(DAY, T2.DocDate,T0.DocDate) as 'Distance', DATEDIFF(DAY, T2.DocDate,T0.DocDate)-T3.ExtraDays as 'Delay'
FROM ORCT T0  
INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
INNER JOIN OINV T2 on T1.DocEntry = T2.DocEntry
INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum
Left Outer Join CDC1 T4 on T4.CdcCode = T3.DiscCode
Where T0.DocDate >= '[%1]' and T0.DocDate <= '[%2]' and T1.Invtype = '13'
ORDER BY T0.DocDate desc

Hope that helps

Markus

[wojciech_domanski]

Hi Markus,

Thank you for your help. I needed exactly this query:

SELECT DATEDIFF(Day,min(t11.DocDueDate),SYSDATETIME()) from OINV t11 where t11.[CardCode] = $[$4.0.0] and T11.[DocStatus]='O'

Wojtek