Skip to Content
avatar image
Former Member

FS Query the days of invoice delay

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 21, 2015 at 06:22 AM

    Hi,

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

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • avatar image
    Former Member
    Oct 21, 2015 at 04:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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