Report - how to find an Items paid date

[UncleTupelo]

Good Morning,

I am using Sage 50 on 2010 (don't think what version I use makes much odds as the data model seems almost identical between versions) and need to export the all the item data (open or closed recently, for sales and purchase ledger).

It would appear the AUDIT_HEADER table has all the information I need and the query is simple. Apart from one thing. How can I determine the date at which the item was closed?

I know if an item is closed as the balance (AUDIT_HEADER.OUTSTANDING) is 0 (or the PAID_FLAG could probably be used).

But where is the Paid Date (or close date) stored? I can't see it anywhere.

Any help appreciated.

Cheers

Mat

(ps the confirmation code when registering has to be the hardest to read I have ever seen!)

[leyburnsolutions]

AUDIT_HEADER isn't necessarily a good indicator of whether an item has been paid or not. In reality, the items that get paid are actually in AUDIT_SPLIT (every record in AUDIT_HEADER will have at least one record in AUDIT_SPLIT but possibly more). When an item is paid, the relevant AUDIT_SPLIT record gets a record of the payment in the AUDIT_USAGE table and that it where you get the paid date from although, again, a single AUDIT_SPLIT record can be linked to more than one AUDIT_USAGE record.

Bruce posted a good diagram of how this works in this thread: viewtopic.php?f=1&t=1792&p=5451#p5451

John

[UncleTupelo]

Ah yes. Thanks a lot John (and Bruce) that makes perfect sense.

I ran some queries and I can now see how the allocation\reconciliation model works.

However, I tried joining all 3 tables (AUDIT_HEADER, AUDIT_SPLIT, AUDIT_USAGE) to get all the info I want.

Code: Select all

SELECT 
stuff
FROM AUDIT_SPLIT ASP
JOIN AUDIT_HEADER AH ON AH.TRAN_NUMBER = ASP.TRAN_NUMBER
JOIN AUDIT_USAGE AUU ON AUU.SPLIT_NUMBER = ASP.SPLIT_NUMBER   
WHERE ..blah

Unfortunately joining these 3 tables in, any order, seems to result in an odd error:

Code: Select all

 Syntax error: Invalid outer join specification 

So working on your statement that

every record in AUDIT_HEADER will have at least one record in AUDIT_SPLIT but possibly more

I thought why don't I ignore the AUDIT_HEADER table altogether and just use the AUDIT_SPLIT table as that also appears to have all the fields I want. Apart from one. There is no DUE_DATE on the AUDIT_SPLIT table. Argghhhh!

I'm now looking longingly at the RECORD_MODIFY_DATE field on the AUDIT_HEADER table and contemplating using that as a rough and ready guide to when an item was closed. Does that sound like a very cowboy option? Seems ok from a few extracts I have completed.

Is it possible to modify an item that is already closed?

[brucedenney]

I am not familiar with what you are doing, looks like code of some sort but I would guess making the joins inner joins might work eg

SELECT
stuff
FROM AUDIT_SPLIT ASP
INNER JOIN AUDIT_HEADER AH ON AH.TRAN_NUMBER = ASP.TRAN_NUMBER
INNER JOIN AUDIT_USAGE AUU ON AUU.SPLIT_NUMBER = ASP.SPLIT_NUMBER
WHERE ..blah

[UncleTupelo]

Just SQL. JOIN, INNER JOIN, LEFT JOIN, LEFT OUTER JOIN. Nothing works.

The Sage ODBC driver is pretty awful (as is the general db schema - I guess no one at Sage HQ is big on normalisation) and very restrictive - which is why Sage are not keen to support it, even through the developer program.

Typical legacy code and database schema system I suppose.

Anyway easy to complain. Thanks for your help gents. I have taken enough of your time already.

Cheers

Mat

[leyburnsolutions]

You are right, AUDIT_SPLIT does not have a DUE_DATE as it is calculated based on the date of the transaction and the value of SALES_LEDGER.PAYMENT_DUE_DAYS.

RECORD_MODIFY_DATE may not do the job for you for a couple of reasons:

1. It changes if the record is updated for any reason, not just because the item has been paid.
2. As a header can have multiple splits each of which can have multiple allocations so a transaction could have one or may payment dates, only the last of which would match the modify date on the header (subject to the caveat in item 1 above).

The driver is, as you say, pretty awful as it's only purpose in life is to provide Sage with a mechanism to extract data for the Report Designer, it was and still is, never designed to be used by other applications/developers which is why Sage do not support it at all...

Arguably, as a flat file database with multiple files, Sage shouldn't be using ODBC anyway as it was never designed to work with such databases in the first place.

The only time I'd ever use the ODBC driver over the Developer SDO is if I need to run a query on a non indexed field as the ODBC driver returns data much faster than the SDO can.

John

[brucedenney]

Well, my real bitch with ODBC is the fact that sage chose the comma as it's internal delimiter, which is crass, because the comma in SQL means something, why they didn't and haven't changed it since I have no idea, it would remove all the ambiguity in a statement and make sense. It would also make it work with open office data pilot, that demands non ambiguous SQL statements.

I just tried to do the link in MSquery and got this SQL

SELECT
Stuff
FROM AUDIT_HEADER AUDIT_HEADER, AUDIT_SPLIT AUDIT_SPLIT, AUDIT_USAGE AUDIT_USAGE
WHERE AUDIT_SPLIT.HEADER_NUMBER = AUDIT_HEADER.HEADER_NUMBER AND AUDIT_SPLIT.SPLIT_NUMBER = AUDIT_USAGE.SPLIT_NUMBER

Which works...

[UncleTupelo]

Yay! The join (all table) at the beginning of the SQL statement does work. Odd that joining sequentially (in my previous example) doesn't.

Anyway, that is what I need.

Thanks a lot - excellent forum service!

[brucedenney]

Would you care to post the final code so that anyone looking at this later can clearly see what did work?

[UncleTupelo]

Well, in the end it didn't work completely via SQL as the Sage ODBC driver doesn't seem to support DISTINCT and the GROUP BY seems flaky.

So I basically run this query (there is a lot of 'stuff' from various tables):

Code: Select all

SELECT
AUDIT_HEADER.TRAN_NUMBER,
AUDIT_USAGE.DATE
and a lot more stuff
FROM AUDIT_HEADER AUDIT_HEADER, AUDIT_SPLIT AUDIT_SPLIT, AUDIT_USAGE AUDIT_USAGE
WHERE AUDIT_SPLIT.HEADER_NUMBER = AUDIT_HEADER.HEADER_NUMBER AND AUDIT_SPLIT.SPLIT_NUMBER = AUDIT_USAGE.SPLIT_NUMBER
AND AUDIT_HEADER.TYPE IN('SI', 'SR', 'SP', 'SA', 'SC', 'SD', 'PI', 'PP', 'PD', 'PA', 'PC') 
AND AUDIT_HEADER.DELETED_FLAG=0

But this obviously returns back duplicate rows, so in pseudo code I then do the following:

Code: Select all

For each AUDIT_HEADER.TRAN_NUMBER create a Map, with the TRAN_NUMBER as a Key and a collection of associated AUDIT_SPLIT, AUDIT_USAGE rows

Iterate Map and for each TRAN_NUMBER if Item is closed (FOREIGN_GROSS_AMOUNT - FOREIGN_AMOUNT_PAID == 0) then get last (most recent) AUDIT_USAGE.DATE from collection  of applied items.

Not ideal, but will suffice.