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 SL (Archived) / Dynamics SL SQL Query ...
Microsoft Dynamics SL (Archived)

Dynamics SL SQL Query for exporting data from Modules

(1) ShareShare
ReportReport
Posted on by Community Member

Hi Team,

I am a developer for a company where Dynamics SL is used(I don't have any proir experience in SL).  We have a cloud based BI application for executive dashboards and other collaborative reporting purposes. I am trying to export some key data from SL to build some kind of dashboard outside SL. I have full access to SQL server database, but the number of tables and views are overwhelming. I really don't know how to start looking for any kind of data. Here are some reports I am planning to build. 

Total Ap  - this month &  prev month

Total AR - this month &  prev month

current Aging 

Invoices - Overdue, Paid, Open

Vendor performance / Vendor Spend analysis.

Open balance by vendor, Vendor expenses YTD

Purchase Order - POs by vendor, Open POs by Period

Inventory transactions

I really need a starting point. Could anybody share some SQL queries I can use to start digging? I tried searching many forums. I can see queries for GP, AX.. but i am not getting anything useful for SL.

Any help is really really appreciated.

Thanks,

*This post is locked for comments

I have the same question (0)
  • BarbaraF Profile Picture
    BarbaraF 147 on at

    I know how you feel.  I inherited Dynamics SL too.  We only use the AR module.  Tables most important for AR module are Ardoc, Artran, and Aradjust.  In Ardoc the CuryOrigDocAmt field gives you the original amt, the perpost field gives you the month it was posted, the doctype field gives you the type like IN - invoice, cm - credit memo etc.., and docbal field gives you the outstanding balance.

    You can link the ar tables by refnbr and batnbr.  I think the tables for Ap are the same.

    I hope this helps you a little.

  • JohnTwohig Profile Picture
    JohnTwohig 152 on at

    In the Dynamics SL client click Help, Help Topics and scroll down to schema and it will list all the fields in all of the tables with somewhat of a description as to what they mean.

  • Paco Beltran Profile Picture
    Paco Beltran 10 on at

    Here some information:

    For AP

    APDoc  Documents Header  (All Documents Vouchers, Adjustments, Checks)

    APTran  Documents Detail

    APAdjust Relations between documents

    For AR

    ARDoc  Documents Header  (All Documents Invoices, Adjustments, Payments)

    ARTran  Documents Detail

    ARAdjust Relations between documents

    For Inventory

    INTran

    For Purchase

    PurchOrd   Purchase Orders

    PurOrdDet  Purchase Orders Detail

    POReceipt  Receipt Headers

    POTran       Receipt Detail

    If you see once you see the names is not complicated.  Check also the views that start with QQ this were built the be used by the final user.

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 SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans