Specifications General Ledger: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
 
(10 intermediate revisions by the same user not shown)
Line 65: Line 65:
** is Postgresql fast enough to cope with all transactions in one table?
** is Postgresql fast enough to cope with all transactions in one table?
** this would make reporting much easier
** this would make reporting much easier
== Analysis Attributes ==
=== Description ===
Analysis Attributes are "tags" with values that you can assign to a part of a transaction.
When an account is linked to an Analysis Attribute, all transactions to that account are forced to have an Analysis Attribute Value assigned to the transaction.
Examples:
* for assigning tax codes to transactions to the income account for book sales. some transactions have lower tax values, eg. Books, and CDs have the normal VAT tax.
* for assigning car registration numbers to accounts for fuel or car repairs. This way you avoid needing a separate cost centre for each car. Although that might be a good idea too...
=== Database ===
* [http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_analysis_type&group=account a_analysis_type] defines the types of Analysis Attributes, eg. Tax, Car Registration Number
* [http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_freeform_analysis&group=account a_freeform_analysis] defines the values for those types, eg. VAT5%, VAT17.5%, SI-CA-007, etc.
* [http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_analysis_attribute&group=account a_analysis_attribute] links an analysis type to an account, ie all transactions to that account are required to have an analysis attribute value associated with the transaction
* [http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_trans_anal_attrib&group=account a_trans_anal_attrib] tags a transaction with the given analysis attribute value
* [http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_ap_anal_attrib&group=account a_ap_anal_attrib] allows to define an analysis value already in the AP system with the AP document, so that you don't need to enter the analysis value later when posting to GL.
=== User Stories ===
* U1: I want to be able to create a new type of analysis attribute, and want to create predefined values for such a attribute.
* U2: I want to connect analysis attributes with accounts.
* U3: When I enter a GL transaction to an account that is associated with an analysis attribute, I want to be prompted for a value from a list of available values.
* U4: when working in AP, I want to define the analysis value for an invoice when creating the invoice.
=== Implementation ===
==== Setup screen (implementing U1) ====
* upper part: list of analysis attributes. buttons for add and delete.
* detail part:
** name of analysis attribute.
** can activate/deactivate analysis attribute.
** List of analysis values for the current attribute. Add and remove values.
** detail part for values: edit name and activate/deactivate value
* Can we do this as a normal [[How-To:_Creating_a_Maintain_Table_screen|Maintain Table screen]]?
** this screen is different: it will have 2 tables to maintain, not just one table as in the usual table maintenance screens
** will we use cached tables for the analysis types and analysis values? Or our own typed dataset?
** Proposal:
*** use typed dataset GLSetupTDS which contains the tables a_analysis_type and a_freeform_analysis
*** function on the server with parameter ALedgerNumber which loads AAnalysisType and AFreeformAnalysis by ledger number
*** we can reuse the function SaveGLSetupTDS from TGLSetupWebConnector which will save those tables as well, so no need for a new server function.
*** for the yaml files: the easiest is to do it similar to GLBatch screen: ABatch is now AAnalysisType, and AJournal is now AFreeformAnalysis. We should try to do it without tabcontrol, but on one screen. It might work to have the AAnalysisType as the main grid, and AFreeformAnalysis in the user control with its own grid.
*** create a yaml file that has the grid for analysisType
*** create a yaml file for a usercontrol that has a grid for all AFreeformAnalysis (depending on the selected analysisType) and allows to add new attribute values etc
*** the first yaml file refers to ucoFreemformAnalysis in the detail area of analysisType.
==== Extension to Account Hierarchy Setup (implementing U2) ====
** in detail of account, add or remove analysis attributes from account
** TODO: how to implement this in the UI
==== Extension to GL Batch screen, transaction tab (implementing U3)====
** depending on the selection of the account, either a popup window or separate control must be enabled that allows to select an analysis value if that account is associated with an analysis attribute
====Extension to AP Document Edit screen, implementing U4====
** there is already an analysis button for the ap document detail. That button needs to be implemented. The analysis attribute should be displayed in the document detail grid

Latest revision as of 13:28, 15 September 2010

multiple currencies

  • There is a base currency for each ledger, the balances of the ledger are stored in the base currency
  • There is an international currency
    • it is only used for reporting (previously it was stored with each balance, but that caused too much problems/overhead)
  • There can be foreign currency accounts which have balances in their own currency
    • they can be revaluated with a new daily exchange rate
  • exchange rate is stored in the database
    • corporate exchange rate (eg. issued once per month, an average exchange rate over the last 30 days)
      • the corporate exchange rate is used for calculating the reports in international currency
      • it is also used by ICH for clearing the debits/credits of the different offices between each other
    • daily exchange rate: this can be updated each day, or downloaded from Yahoo Finance etc.
      • the daily exchange rate is used for transactions with other currencies (different from base currency)

transactions

double entry bookkeeping

Transactions in GnuCash

  • see [1]
    • "For every transaction, total debits must equal total credits."
  • see GnuCash Manual: Multiple Split Transactions; also see a discussion here: [2];
    • there are simple transactions (one split), which just post to 2 accounts, with same (opposite) amount, same narrative etc
    • there are multiple split transactions, that have several transactions, and combined they balance
    • see the GUI of GnuCash for this:

You can switch between View: Basic Ledger, this shows only the summary: GnuCashTransactionViewBasicLedger.png

The other View is: Transaction Journal, this shows all movements of money involved: GnuCashTransactionViewTransactionJournal.png

Transactions in Petra 2.x

  • the definition of transaction in Petra 2.x is different from anyone else's definition: In Petra 2.x, a transaction only involves one account, therefore you need at least 2 transactions to get to "total debits equal total credits".
  • in Petra 2.x: a lot of transactions are in one journal, only some of them really belong together ("total debits equal total credits").
  • a journal was only used for different grouping transactions by currency
  • it was hard to find all parts of a real transaction, because there were so many transactions in the journal unrelated to each other

Transactions in OpenPetra

  • option 1: do the same as GnuCash:
    • keep the same database structure as in Petra 2.x, with a_batch/a_journal/a_transactions
    • different GUI: journals are not displayed anymore as such, but they become the transactions to the user;
      • you can expand a journal line and see/edit the simple or split transactions involved
      • when a journal row is selected, show the details for the journal in the edit section
      • if a transaction row is seleted, show either simple transaction edit (2 accounts), or one of the split transactions
      • buttons: add journal, add transaction, add split transaction
    • simple transaction involves 2 accounts
      • only one reference/narrative/amount
    • split transaction: allows to define each account/reference/narrative
  • option 2: keep the Petra 2.x users happy
    • addition in the database: a_transaction.a_transaction_group_i: refers to one transaction in the group; all transactions with the same a_transaction_group_i make up a real bookkeeping transaction
      • it might actually work to use the a_reference_c field; but we should then disallow manual editing of that field
    • GUI: mark several transactions in the grid, and click button "Group", which will assign the first transaction_number_i to all transaction_group_i in the group.
  • improved reports will show the opposite transaction(s), on Account Detail report etc.
  • we should change the names for Transaction in the common language files to reflect the usual meaning of Transaction to avoid misunderstandings with trained bookkeepers
  • import transactions from text file; group transactions as soon as the totals of credits balances the totals of debits.

Posting

reduce number of tables

  • to test: should there be 3 different tables, for transactions from the current periods, the closed periods of the current year, and periods of previous years?
    • is Postgresql fast enough to cope with all transactions in one table?
    • this would make reporting much easier

Analysis Attributes

Description

Analysis Attributes are "tags" with values that you can assign to a part of a transaction.

When an account is linked to an Analysis Attribute, all transactions to that account are forced to have an Analysis Attribute Value assigned to the transaction.

Examples:

  • for assigning tax codes to transactions to the income account for book sales. some transactions have lower tax values, eg. Books, and CDs have the normal VAT tax.
  • for assigning car registration numbers to accounts for fuel or car repairs. This way you avoid needing a separate cost centre for each car. Although that might be a good idea too...

Database

  • a_analysis_type defines the types of Analysis Attributes, eg. Tax, Car Registration Number
  • a_freeform_analysis defines the values for those types, eg. VAT5%, VAT17.5%, SI-CA-007, etc.
  • a_analysis_attribute links an analysis type to an account, ie all transactions to that account are required to have an analysis attribute value associated with the transaction
  • a_trans_anal_attrib tags a transaction with the given analysis attribute value
  • a_ap_anal_attrib allows to define an analysis value already in the AP system with the AP document, so that you don't need to enter the analysis value later when posting to GL.

User Stories

  • U1: I want to be able to create a new type of analysis attribute, and want to create predefined values for such a attribute.
  • U2: I want to connect analysis attributes with accounts.
  • U3: When I enter a GL transaction to an account that is associated with an analysis attribute, I want to be prompted for a value from a list of available values.
  • U4: when working in AP, I want to define the analysis value for an invoice when creating the invoice.

Implementation

Setup screen (implementing U1)

  • upper part: list of analysis attributes. buttons for add and delete.
  • detail part:
    • name of analysis attribute.
    • can activate/deactivate analysis attribute.
    • List of analysis values for the current attribute. Add and remove values.
    • detail part for values: edit name and activate/deactivate value
  • Can we do this as a normal Maintain Table screen?
    • this screen is different: it will have 2 tables to maintain, not just one table as in the usual table maintenance screens
    • will we use cached tables for the analysis types and analysis values? Or our own typed dataset?
    • Proposal:
      • use typed dataset GLSetupTDS which contains the tables a_analysis_type and a_freeform_analysis
      • function on the server with parameter ALedgerNumber which loads AAnalysisType and AFreeformAnalysis by ledger number
      • we can reuse the function SaveGLSetupTDS from TGLSetupWebConnector which will save those tables as well, so no need for a new server function.
      • for the yaml files: the easiest is to do it similar to GLBatch screen: ABatch is now AAnalysisType, and AJournal is now AFreeformAnalysis. We should try to do it without tabcontrol, but on one screen. It might work to have the AAnalysisType as the main grid, and AFreeformAnalysis in the user control with its own grid.
      • create a yaml file that has the grid for analysisType
      • create a yaml file for a usercontrol that has a grid for all AFreeformAnalysis (depending on the selected analysisType) and allows to add new attribute values etc
      • the first yaml file refers to ucoFreemformAnalysis in the detail area of analysisType.

Extension to Account Hierarchy Setup (implementing U2)

    • in detail of account, add or remove analysis attributes from account
    • TODO: how to implement this in the UI

Extension to GL Batch screen, transaction tab (implementing U3)

    • depending on the selection of the account, either a popup window or separate control must be enabled that allows to select an analysis value if that account is associated with an analysis attribute


Extension to AP Document Edit screen, implementing U4

    • there is already an analysis button for the ap document detail. That button needs to be implemented. The analysis attribute should be displayed in the document detail grid