Forum OpenACS Q&A: Re: SQL-Modelling Costs and Invoices

3: Re: SQL-Modelling Costs and Invoices (response to 1)

Posted by Torben Brosten on 08/02/04 10:03 AM

Frank,

It looks much simpler than how the data is abstracted at OpenForBiz[1], which would make for a more manageable system --at least from this perspective. =)

Have you considered supply chain management/operations from the various perspectives?

What are the performance considerations?

Since accounting is all about summaries, it seems that a practical approach would be to use summary tables with calculation/data-entry time-marks to allow a scheduled process to periodically update reports etc for scalability. You can think of this as a slow, large spreadsheet calculation, where recalcs are done periodically or on demand.

Can anyone comment on what approach(es) would scale best?

Speaking of which, creating a "thin client" based on Lars' Skinny-Table spec[2] may be a fast way of developing here as it could be immediately applicable to various accounting, ecommerce and bookkeeping packages, and extended to meet specific specialties. In regard to developing the thin client, I've been considering how to use ad_form to implement a gui similar to sql-ledger's[3] invoicing, where there are update and posting features for various table rows represented on one page --but that's another thread.

1. https://ofbiz.dev.java.net/servlets/ProjectDocumentList?folderID=236&expandFolder=236&folderID=236

2. http://www.collaboraid.biz/developer/

3. http://sql-ledger.org

4: Re: SQL-Modelling Costs and Invoices (response to 3)

Posted by Frank Bergmann on 08/02/04 10:37 AM

Hi Torben,

thank for your link, I'll definitely have a look at it.

Concerning performance: I'm not too concerned with that, because Oracle/Postgres will keep the first 10 million entries or so in RAM... (Maybe I should move the "description" out to a different table...).

My idea is to go along the lines of the new SAP philosophy and to do most reporting with a data ("business") warehouse.

<blockquote> Have you considered supply chain management/operations
</blockquote>

I'm going to go for small and medium _project_oriented_ organizations, so there won't be no modules to deal with physical goods moving. Instead, our focus is on human resources, project collaboration and KM as the "supply chain of the knowledge economy"...

Also, we don't aim to do "accounting". We want to leave all double-entry stuff to external programs, because they are cheap and they handle the country specific legal and other conventions.

Instead, we want to do "controlling", that means calculating profit and loss on a per-project or per-customer base. The point is that there are already modules for timesheet, payroll, invoices and travel costs (from the old 3.4 intranet and other past work). All these modules gather some kind of cost, which "just" needs to be integrated in order to get all costs incured by a project.

And maybe the most elegant solution for that is a common superclass that is (relatively) easy to sum...

Bests,
Frank

7: Re: SQL-Modelling Costs and Invoices (response to 4)

Posted by Malte Sussdorff on 08/02/04 12:15 PM

This might be a little bit brutal, but why don't you just use the SQL Ledger Tables and port the functionality of it into an OpenACS Web based package. After all, it is reknown to be a very good accounting software *and* this would prevent inventing the wheel over and over again. Last but not least, if you go down that road, you might even be able to get some cross support from the developers.

Alternatively, just install SQL-Ledger on AOLserver along with your normal OpenACS installation and let your custom code write accounting information directly into SQL-Ledger by just porting from PERL to TCL the necessary ::create and ::get API.

Either way, I know it might be tempting to start with your own Accounting System, but give it some thought if it the best you can do with your development power, taking into account excellent software like SQL-Ledger.

8: Re: SQL-Modelling Costs and Invoices (response to 7)

Posted by Frank Bergmann on 08/02/04 12:56 PM

Hi Malte,

I think our posts crossed.

Let me reasure you that we're not going to go for a Yet Another Accounting System. Instead, P/O captures costs over the web and allows for the "controlling" stuff (redistributing costs to projects and customers) that is needed by project-oriented organizations (and not provided by SQL-ledger).

The idea is to export these items afterwards to SQL-ledger or similar packages (Quickbook, Conta-Plus, KHK-Kaufmann, ...) to facilitate the creation of balance sheets and income statements are required legally.

I'm already looking at the OfBiz and SQL-Ledger data models. It would be really cool to run OpenACS and SQL-Ledger on the same server...

Frank