View that shows number of invoices paid per month and if they were paid late

A user on the GPUG forum just asked "I'm trying to create a SmartList that shows number of invoices paid per month and if they were paid late"

Please note that there is a difference between the day the payment is received, and the date of the apply. In this view, I'm using the date that the payment was received.

That sounded like a good blog, so here it is...

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

-- Drop stored procedure if it already exists
IF object_id(N'ddv_RMPaidInvoices', 'V') IS NOT NULL
    DROP VIEW ddv_RMPaidInvoices
GO
     
CREATE VIEW ddv_RMPaidInvoices
     
--  select * from ddv_RMPaidInvoices
 
AS
   
--view showing invoices, and the date they were paid off
select rt.docnumbr,
        rt.glpostdt,
        duedate,
        rt.dinvpdof,
        apy.apfrdcdt,
        apy.sumapfrmaplyamt,
        rt.ortrxamt,
        rt.curtrxam,
        case when apy.apfrdcdt > rt.duedate then 'LATE' else '' end as Late,
        rt.source
    from (
        select docnumbr, rmdtypal, glpostdt, duedate, dinvpdof, ortrxamt, curtrxam, 'Open' as source from rm20101
        union all
        select docnumbr, rmdtypal, glpostdt, duedate, dinvpdof, ortrxamt, curtrxam, 'Hist' as source from rm30101
        ) rt
        join (
                select aptodcnm, aptodcty, max(apfrdcdt) as apfrdcdt, sum(apfrmaplyamt) as sumapfrmaplyamt from rm20201 apy group by aptodcnm, aptodcty
                union all
                select aptodcnm, aptodcty, max(apfrdcdt) as apfrdcdt, sum(apfrmaplyamt) as sumapfrmaplyamt from rm30201 apy group by aptodcnm, aptodcty
            ) apy on apy.aptodcnm = rt.docnumbr
    where rt.rmdtypal = 1 --invoices
        and rt.curtrxam = 0
 
 
  
GO
  
grant SELECT on ddv_RMPaidInvoices to public

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences