Skip to Content
0
Former Member
Nov 19, 2010 at 02:47 PM

data reporting question

30 Views

Greetings.

I work for a company in the field of audit recovery, where we take different a/p systems, have data sent to us (ideally in true native format) and we then produce a table that is basically one record per vendorcheck numberinvoice number (or more accurately vend number, chkno, chkdate, chkamt, inv number, invdate, inv_amt).

For JDE, Lawson, Peoplesoft, Grt Plains we've gotten this down to a science. For SAP, we have experimented with different methods, and currently have a good method which entails splitting the bsak and also using the payr. We also pullin maybe the reverse doc information from bsak. We used to get the bseg but stopped requesting it b/c it was deemed more detailed than what we need---but it's conceivable that it is crucial and that we should be using some aggregated values from it.

Does anyone know of the optimal way to achieve the end result? Note that I'm familiar with the ap tables, I have documentation on the 5 character field names, the 'prettified names' and I've done quite a bit of querying on the ap tables. I'm also aware that some tables would need aggregating because they are at a more detailed level than what I seek.

I'm also familiar with a system like Grt Plains, where you have a table that has one record per invoice+'payment' made on the invoice, where payment can be by 'check' or by 'credit memo'. Grt Plains does not have lots of client created doc_types, and thus is more cut and dry when it comes to separating 'invoices' and 'things that pay invoices'.

If anyone has ideas on: tables to use, tables that may need splitting, fields to use for joining, that could be useful. If not, we're not in the dark, we're just trying to optimize our methods.

thnx,