Hello All,
I need help with tying a given activity transaction (table ACTRANS) to it's supporting details within table ARDISTRIB. As the company I'm working with currently has little activity and just started a new fiscal year, my matching process currently uses a SUM query in SQL to add up the amounts on any given date and then matches by transaction date and the total from the SUM query matching the "summarized transaction" amount from ACTRANS. I can see this breaking the moment there is more than one "distribution" for a given date, so I need to know how to link a specific distribution back to the summarized transaction in ACTRANS. I know that one CAN see that detail from within the Lawson interface, as I've seen it, but I can't seem to find the linkage within the table structure. Please be very specific, as I'm a newbie to Lawson, with ZERO experience using it's interface. Every exposure I have is entirely through SQL via the underlying data tables. I can often find what I'm looking for, but this one has stumped me so far. Any/all help will be greatly appreciated. Thanks!
Steve (aka sgmunson)
I'm not sure. Here's what I do know:
This is a multi-company setup, so for each company, there's a "discretionary" activity that allows that company to expend funds outside of the company model for normal expenditures. Each company represents an educational institution, so the spender is the principal. Fundraisers often create the "revenue" that funds a checking account belonging to the "master" company, and thus revenue is always recorded on a given schools company number, while expenses are always recorded against the master company number. Deposits into that checking account that belong to the discretionary category are documented in Excel and uploaded into Lawson through AR, with batch numbers, and there's some flag set that places a summarized transaction into the ACTRANS table, and the detail becomes a "distribution", and thus I find it in ARDISTRIB. Discretionary expenses that come through another Lawson module (e.g. Payroll) also appear as summarized transactions (expense accrual), but there are other deposits and expenses that appear in ACTRANS that aren't summaries, and I assume they're purchase orders or non-distributed deposits.
Thus it sounds more like your 2nd variety, but the only case I need to find detail for is when it's revenue (for which the criteria is that the account number is in a given range) AND there's a matching set of detail in ARDISTRIB.
So far, I just don't have enough information to find a way to tie a given set of ARDISTRIB records to a given summarized transaction in ACTRANS by anything other than comparing company, activity, transaction date, and the summary total being equal to the SUM of the ARDISTRIB records for the same company, activity, and transaction date. I know that this method will break if they ever have more than one summarized transaction for the same company on the same date. That's why I'm trying to figure out how to tie the distribution back to the summarized transaction.
Unfortunately, as a contractor, and being brand new to reporting on Lawson data, I just don't have more than a couple months background on the Lawson data structure. Fortunately, I'm pretty good with figuring many things out once I get the right concept in my head. As I don't use the Lawson interface to look at data (I have neither access right nor any training on it), I have to see everything exclusively from the back end, so to speak, by looking at table data.
Does that help?
Still have no solution, but I've found that AR195 appears in the REFERENCE field, suggesting that module AR195 is responsible for the summarized transaction. Other summarized transactions appear with similar data in the REFERENCE field, such as PR198 and AP175. Does this help?
John,
That was exactly what I needed. Now I have a link between the two based on those object ID fields and ensuring that flag field is 'Y'. Thanks so very much for your help.