Proposal for tagging GL transactions


Purpose

The purpose of this functionality is to allow reporting of GL transactions within each GL code. For instance the company may want to keep travel expenses in one general ledger code, but be able to see how much has been spent on travel for a particular project. It could also be desirable to report on income and expenditure by branch, while not having the Chart of Accounts grow out of control.

<Phil>I think it is important to "tag" that this functionality is in addition to the heirarchical account group structure that we already have.
Heirarchical account groups allows us to set up charts within charts easily - for branch reporting or project reporting - the snag is you end up with a massive chart of accounts.... which can get ugly

eg. We want to report on projects using the GL ... and each project is really (say) product development and the projects follow the same structure:

Product Development is set up as an account group
Project 1 is set up as an account group but with a parent account group of product development.
Project 1 has GL accounts for samples, travel, salaries, 3rd party testing etc... all of which refer to Project 1 account group

When you report the TB or profit and loss in detail you see

Product Development

Project 1
Samples 100.00
Travel 120.00
Salaries 5,200.00
3rd party testing 50.00

Total Project 1 5,470.00
Project 2
Samples
Travel
Salaries
3rd party testing
Total Project 2
Project 3
.
.
.

Total Product Development
So you can use this heirarchical structure to define how you see the accounts. You could equally modify this at any time in the future to have all salaries accounts under a Staff Costs account group ... without having to recode the entire chart.
This tagging functionality is not attempting to duplicate what we can do already. As I understand it I think the functionality proposed here is about tagging GL transactions with a project or job reference for the purposes of reporting on income/costs that might span a multitude of GL accounts - without creating additional chart headings. So taking the example above - we are able to report on the costs associated with Project 1 directly with all the GL transactions tagged as project 1 related without having to create the whole chart structure above. We can have chart that has one account for
Product Development:
Samples
Travel
Salaries
3rd Prty testing

that contain the costs for all product development - if you want a report for project 1 this then uses the tags to show all the gltrans posted to project 1....

</Phil>

Database changes required

A field would be required within the gltrans table to hold a reference to the tag. It may be sensible to use the jobref field which is already there, and not currently used, as the intended functionality for tagging is very similar to the contract reference functionality originally envisaged for this field.

A new table would be needed to hold the information regarding each tag. This would look similar to this:


CREATE TABLE `tags` (
`tagref` varchar(20) NOT NULL,
`tagdescription` varchar(50) NOT NULL,
`department` varchar(50) NOT NULL,
PRIMARY KEY (`tagref`)
) ENGINE=InnoDB;

Script changes

A new script will be required for creating/editing/deleting tags.

All scripts that create gl transactions will need altering. The following incomplete list will act as a starting base:

Bank GL payments.
Bank GL receipts.
GL Journals.
Inventory adjustments.
Sales invoices/credit notes
Purchase ledger GL invoices and debit notes


More can be added as the project moves forward.

Other thoughts

It will be necessary to have a tag of "none", which should always appear as the default item in the list so that anyone not using the tagging system will not have to perform any action.

Valid XHTML :: Valid CSS: :: Powered by WikkaWiki