SQL Tables / Invoice Payment / Journal Entries for Current & Past FYs

SUGGESTED

Working on a report for the AP team to better schedule payments for our vendors.

The Sage documentation is quite good – but there are a few gaps I’m struggling with.

Vendor Payments:

 I see that there are two sets of tables for Vendor invoices.

    1. tVenTr / tVenTrDt – Vendor Invoice Transaction Details
    2. tiTLU / tiTLULi – Invoice Lookup/Details

I have built a pretty slick week-based Supplier Aged Report using the tables from tables in A above – but even after reviewing the tables in A and the tables in B – It’s not clear to determine if a particular invoice has been paid or not – but now that I dig into B – it has a bit more info that is useful.  I’m reading online – and folks are saying there is no ‘way’ – except to programmatically sum the invoices BEFORE him to determine if he’s paid or not.  Is there a better/easier way?

Why are there two sets of tables that have, what appears to be, similar data? They do not appear to have a direct relationship.

Journal Entries:

Not 100% clear in the documentation, but I believe the Current_FiscalYear, and Last_FiscalYear are treated similarly, whereas all other older Fiscal Years become "historic" by migrating data to TableXX names.

Does that mean that Current_FiscalYear and Last_FiscalYear share tAccount and tActDpt tables - while maintaining separate JournalEntry/JournalEntryAcct Allocation tables? If not - where are the Last_FiscalYear tAccount and tActDpt equivalents?

Am I understanding this correctly?

    1. Current Fiscal = tJourEnt / tJentAct / tAccount / tActDpt
    2. Previous Year =  tjEntLY / tJEntLYA / tAccount / tActDpt
    3. Historical =  tjEHxx / tjEAHxx / tActHinf / tActHDpt
      1. * I see that historical tables link up based on the indexes found in tActHDat. I believe I understand that correctly. After rolling a year, all JE’s/Department/Accounts are migrated to HISTORIC years

Appreciate your help!

Thank you.

Mike

  • 0
    folks are saying there is no ‘way’ – except to programmatically sum the invoices BEFORE him to determine if he’s paid or not.  Is there a better/easier way?

    No.  Every invoice with a non-zero total has at least one tventr and one tventrDT record.   Every payment is an additional tventrDT record.  The original tVentr and tVentrDT records are not 'touched' when the invoice is paid.   (and there are also prepayment records, etc.)

    Why are there two sets of tables that have, what appears to be, similar data?

    The A/R data is mostly normalized, although my guess is that some denormalization was probably done for performance and to make coding easier.

    - i.e. there is no need to have another copy of the invoice reference text field in the invoice detail record, but the payment reference has to go somewhere, and having that reference in the A/R detail table means that an A/R report doesn't have to look up each payment reference in the payment header table.

    You are right, much of it is redundant.  The titrec table is primarily for inventory / g/l related data, while the titlu table is pretty much a copy of the Purchase Order, frozen into an invoice.  It's used for text search and on-screen display.   There also has to be a way to record non-inventory items' descriptions and item code, detect whether someone is altering inventory items to commit a fraud, etc.

    But, also keep in mind that Sage 50 is modular, so paid invoices can be 'cleared' from the A/R module and from the inventory module.  Some redundancy is no doubt needed in order to make that possible.

    They do not appear to have a direct relationship.

    It's a one-to-many relationship between the master and detail record.  When invoices are always paid in full each time, there are only two detail records, however the data structure would have to allow more complicated situations - i.e. short / over / multiple payments, discounts, adjustments and corrections to paid invoices, the application refreshing when someone else posts an invoice to a vendor that you have open in the payment window, but not when it's another vendor, etc.

    Normalization into multiple tables also makes checking integrity as easy as comparing the sum of all tCustrDT sub-ledger detail records against the G/L balance, and the DBMS can do it in a second.  (I guess, I don't work for Sage, but this is how / why I would do it)

    I believe the Current_FiscalYear, and Last_FiscalYear are treated similarly, whereas all other older Fiscal Years become "historic" by migrating data to TableXX names.

    Does that mean that Current_FiscalYear and Last_FiscalYear share tAccount and tActDpt tables - while maintaining separate JournalEntry/JournalEntryAcct Allocation tables? If not - where are the Last_FiscalYear tAccount and tActDpt equivalents?

    Am I understanding this correctly?

    Yes.  For the first years of its life (Bedford, Accpac, Simply Accounting) the software was limited to only two years of financial data.  When the '99 year' capability was added, they didn't totally rewrite the software to allow transactions into the additional years, probably for a mix of technical and security / integrity reasons. 

    The only reason I've ever had to look at the historical tables, was to fix an integrity error. 

  • 0

    Hi Mike,

    I am wondering how you connect to the Sage 50. Do you use MySQL to open the sage 50 database file directly? 

    I am trying to customize AP reports as well. I am wondering if I am able to display the item description on my GL report. As we deal with general contractor, we want to know what kind of expenses they have been charging us (which we will type he sub-contractors' name on the item description box), so we can work with our budget more efficiently. Currently, the standard GL report run by Sage 50 can only show the vendor name, not the item descriptions.

    Appreciate your help.

    Thank you,

    Alvin

  • 0 in reply to [email protected]
    SUGGESTED

    Hi Alvin:

    One way to do this is to use projects designated as the sub-contractors or create a service code identified as the sub-contractor, The report(s) for the first option will show in the Projects menu section and the second option will show under the Payables reports under Vendor purchases. Try these in the sample company to see which works best.

    Hope this helps!

  • 0 in reply to Agate

    Thank you. I will try the option you suggested.