web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Report- SQL View
Microsoft Dynamics GP (Archived)

Report- SQL View

(0) ShareShare
ReportReport
Posted on by Optiforms 335

I am needing help with a sql view that will show the following:

Customers with Invoice Number exceeding 60 days of invoice date.
    (Only display unpaid invoices) (Solved- Thank you)

Customers currently on credit limit hold.
    (Solved - Thank you)


A view that shows the order and its invoice.
Still Open

It can be three different views

*This post is locked for comments

I have the same question (0)
  • K Day Profile Picture
    K Day 7,365 on at

    Well, it might be better if you looked at the tables yourself and determined exactly what you need.  My SQL is pretty bad but I can point you in the right direction.

    First one, the table is the RM20101 - Open Transactions.  Start with Select * from RM20101 and you might end up with something like this

     Select

    CUSTNMBR as [Customer Number],

    DOCNUMBR as [Document Number]

    From RM20101

    Where DOCDATE <= (GETDATE()-60) and VOIDSTTS = 0

    For the Credit Limit Hold, I am  just thinking about the main customer card.  You can put a Customer on Hold or set their credit limit to 0.  Run this script and see if you can figure out what you want

    Select

    CUSTNMBR as [Customer Number],

    CUSTNAME as [Customer Name],

    HOLD,

    CRLMTTYP as [Credit Limit Type]

    from RM00101

    I don't have time right now to get the Order and It's invoice.  Maybe someone else can help.

  • K Day Profile Picture
    K Day 7,365 on at

    You can add  and CURTRXAM > 0 to the where clause to get unpaid.  You seem to be comparing it to some actual data.  You can probably refine it to what you need better by looking at all the columns available when you run

    SELECT * FROM RM20101

  • Optiforms Profile Picture
    Optiforms 335 on at

    I have an owner (company) that refuses to learn the simple little smartlists in GP and wants to use Excel to view live data for which of his customers are over credit limit, currently on hold and who should be, and who hasn't paid within 60 days of invoice.  I am comparing the data I am pulling for the SQL View to GP's Smartlists. the CURTRXAM worked great I just need to figure out GP tells me 131 and my list only 44.  It's down to fine tuning on that.

     Thank you

  • Richard Schultz Profile Picture
    Richard Schultz 1,085 on at

     Optiforms,

    The RM20101 table stores all RM documents; invoices, Debit memos, finance charges, payments, write-offs, and credit memos.  That may be why you are getting more records than you expected - you're not just getting invoices.

    To get just invoices, add this to the WHERE clause of your SQL query:

    RMDTYPAL = 1

    Hope that helps.

  • Optiforms Profile Picture
    Optiforms 335 on at

    Thank you! That worked extremely well with the datediff.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans