webERP General Ledger

Overview

The general ledger is the accounting hub that is central to the "sub" ledgers for creditors (Accounts Payable), debtors (Accounts Receivable) and stock (inventory). All entries in the sub ledgers are also represented by entries in the general ledger. It is the integration set-up that determines how entries in the sub-ledgers are reflected in the general ledger. Most activity in the general ledger will be automatically created from the activity in the sub-ledgers with receivables, payables and stock administration.

However, there are also facilities to:
 

  • Enter general ledger receipts against a pre-defined bank accounts.
  • Enter general ledger payments against pre-defined bank accounts.
  • Enter general ledger journals between any general ledger accounts - except bank accounts. These can also be made to reverse automatically in the following period. Further journals can be posted to any period future or previously - the period is determined by reference to the date entered.
  • Inquire on general ledger account activity and from any entry in this inquiry drill down to the journals created to produce the entry.
  • Inquire on the general ledger trial balance for any period end in history or currently.

Account Groups

The account group is the parent object of a general ledger account for someone who understands OO programming. Child accounts created inherit the properties of the account group - ie the account will be a profit and loss account if it belongs to an account group that is a balance sheet account, the child accounts will display in the trial balance (TB) together in the sequence determined by the account groups sequence in the trial balance (TB).

Using a numbering system inhibits the ability to manipulate the format of the trial balance ie you have to be able to change the account code to change where an account appears ie

10100 motor expense Copenhagen

10110 motor expenses The Hague

10120 motor expense Amsterdam

would be great but then if we wish to restructure so that Copenhagen expenses are all shown together and The Hague is now all shown together etc we will have to change the numbering. In web-erp all that is required is to change the account group. In the first situation we could have an account group for motor expenses and all these account numbers would be set up as belonging to the account group. We can decide whereabouts the account group should appear in the trial balance by changing the sequence in trial balance field. All accounts in the account group will show together. If we decided to change the trial balance to show The Hague expenses together as a separate group of costs, we could create an account group for the The Hague selling costs - or whatever, and change the motor expenses the Hague account no 10110 to be a member of that account group.

Account groups require the sequence in the trial balance to be specified and also whether the accounts in that group will be profit and loss accounts or balance sheet accounts.

A balance sheet account is one where only the balance at the end of the period concerned is of interest. A profit and loss is one where we are interested in the movement over the period. eg. Motor expenses we are not concerned with the balance at the end of the month so much as how much was spent over the period of the profit and loss. However, for a bank account we wish to know what we have now as a balance not the movements in the account. As noted accounts created as a member of an account group will inherit the properties of the account group ie if the account group is a balance sheet group then the accounts will be interpreted as balance sheet accounts.

Bank Accounts

Certain general ledger accounts can be defined as bank accounts - as many bank accounts as needed can be defined. At the time of defining a general ledger account as bank account the currency of the bank account must also be specified. General ledger accounts defined as bank accounts can be reconciled to bank statements using the matching facilities - all receipts and payments show in the currency of the bank account for easy matching off statments. Entries made to bank accounts using receipts or payments, also create a total receipt or payment, which is retained for the purposes of matching off the bank statements. Using the bank payments page, general ledger payments can be analysed to any number of other general ledger accounts, but only one entry to the bank account is made. This page also allows payments to supplier accounts to be created. Similarly, using the receipt entry page, a series of receipts from customers which may all have been banked together can be deposited as one amount to a bank account. There is only one amount appearing on the statement as the total of all these receipts, this bank account transaction is created and available for matching deposits off the bank statements.

Bank accounts are defined from the setup tab from the link to Bank Accounts Maintenance. There is facility to enter the name of the account, the currency of the account, the bank account number and the address of the bank if required, as well as selecting the general ledger account to which it refers. There are links to edit existing bank account records and to delete them. However, once defined as referring to a particular general ledger code it is not possible to change the general ledger code of the bank account. This is because there would be entries made to this account. Similarly, if bank transactions have been created using this bank account it is not possible to delete it. The bank account transactions must be purged first (but currently no facility exists to purge bank transactions). It is not possible to change the currency of a bank account once there are transactions against it.

Once all receipts and payments are matched to bank statements, the bank reconciliation statement can be printed which should show how the current general ledger balance reconciles to the bank statement for this account. The reconciliation also has an option available for bank accounts set up in other than the functional currency of the business (local currency), to post differences in exchange. The balance of the account is maintained in local currency in the general ledger and for the purposes of the bank reconciliation this is converted to the bank account currency at the exchange rate in the currencies table (see Setup -> Currency Maintenance) - this rate can be changed manually to the rate of the day and the foreign currency balance on the account will change - to correct this balance an exchange difference needs to be recorded. Having worked through the matching of receipts and payments to the bank statements - the bank statment balance can be entered to compare against the system balance - a correcting entry is then made to the GL to post the difference on exchange. The posting to the general ledger is back dated to the end of the preceeding month - since it is assumed that the reconciliation may be a few days or a week behind the current date.

Bank Account Payments

From the general ledger tab, the first link under transactions is Bank Account Payments.

The following data is required:

  • The bank account from which the payment has been (or is to be) made. A select box allows this to be selected from the list of defined bank accounts.
  • The date on which it was paid. This is important since the accounting period in which the payment is entered is determined from the date. The system will default to today's date - this must be changed where bank payments are being entered retrospectively.
  • The currency which is being paid. Payment to suppliers may be made in foreign currency being purchased in the currency of the bank account at the exchange rate entered - see below.
  • The exchange rate - this is the exchange rate between the currency being paid and the currency of the bank account. If the currency being paid is the same as the currency of the bank account then this rate should be 1. If another currency is being purchased with the payment then the rate at which it is being purchased should be entered.
  • The functional exchange rate - this the exchange rate between the currency of the bank account and the functional currency of the business as defined in the company preferences (ie the reporting currency of the business). Where the bank account is in the same currency as the functional (reporting) currency of the business then this value should be 1. The functional currency entry will only be required when the bank account currency is different to the the functional currency and will default to 1 automatically if they are the same.
  • Narrative - applicable to the whole payment. Narrative applicable to individual general ledger entries can be entered separately.

Payments can take two forms - either it is a general ledger payment or it is a payment to a supplier. General ledger payments require an analysis of how the payment should be posted to the general ledger. General ledger accounts can be specified either as the account code directly (if the account code is known) or by selecting from the select box. Any narrative applicable to the general ledger amount can be entered too - and the amount to be posted to the selected/entered account. The total payment is taken as being the sum of all the entries made. If the total of all entries made is negative then this is entered as a negative payment - these are accepted to allow for correction of data entry errors. Payments are always entered in the curreny of the payment - the conversions are handled by the system for general ledger postings etc.

General Ledger Integration Setup

Bank Accounts are automatically integrated with the general ledger and cannot exist separately without the GL being used. Every transaction is recorded in two places (double entry) eg. A bank account payment reflects in the bank account and also in the expense account that is was paid for - eg. stationery, fuel, entertaining, advertising or whatever. One entry goes as a debit on the left and the other as a credit on the right - when you look at the trial balance the debits should tie up with the credits ie the trial balance - a list of the general ledger balances should have balancing debit total and credit totals.

With respect to the sales (AR) and purchase (AP) ledgers, general ledger postings can be turned off in the company preferences screen by setting each of the flags to No.

Integrated general ledger postings do provide a good way of building up the business's accounts from activity in these sub ledgers.

You can choose between two levels of integration:

1. Integrate GL at the debtors and sales level only

This creates general ledger journals for each sale as follows:

DR the debtors control account - defined in the company preferences screen
CR the sales account - defined with reference to the customer sales area, stock category of the item being sold and the sales type (price list) of the customer. This provides great flexibility as to how sales should be posted
CR the tax to the taxgl account defined in the tax authorities (ie the general ledger code of the tax authority of the customer branch). It is also possible to have just one general ledger account for all sales by defaulting ANY sales area, ANY stock category and ANY sales type with a single general ledger code - see later section on sales general ledger codes.
the reverse takes place for a credit note.
When cash is received:
CR the debtors control - defined in company preferences DR the bank account - defined in the bank account setup.
There are also general ledger entries for discounts and differences on exchange which have been ignored for the purposes of this introduction.
This level of integration ensures that the list of balances of all customer accounts (in local currency) always ties up with the general ledger debtors control account.

2. Integrate GL at the stock level as well

For every sale:

CR stock value at the standard cost of each item sold - the stock GL account being defined in the stock categories record.
DR Cost of Goods Sales (COGS - or COS) with the same cost. - the COGS GL accounts are defined with similar flexibility as descibed for the Sales GL accounts under the setup menu under AR/AP options

the reverse happens for credits.

This enables the stock value to be continuously updated in the general ledger and always be equal to the stock valuation at standard cost.

This level of integration also has ramifications for stock adjustments, stock deliveries and stock cost changes.

For stock adjustments the quantity adjusted is extended by the standard cost and it is written on (CR) or off (DR) to the stock adjustment GL account as specified in the stock category record for that item.

For receipts of stock - the stock coming in is extended by the standard cost and the entry is to:

DR stock at standard cost x number received - the stock account being defined in the stock category record for the item being received.
CR GRN suspense at standard cost x number received - this account is specified again in the company preferences screen.

The two levels of general ledger integration are:

  • Sales journals that post a credit to a sales general ledger account, a debit to a discount account, a credit to a tax account, a credit to a freight recovery account and a debit to a debtor account. This level of integration also reverses the posting described here for sales credit notes. This level also triggers the general ledger journals for banking of cash against debtor accounts. Debiting a bank account and crediting the debtors account.
  • Stock journals that post a debit to a cost of sales account and a credit to a stock account - and the reverse entries for sales credit notes.

The level of general ledger integration is determined by reference to the flags in the company preferences page.

Sales Journals

The general ledger accounts that are used in this level of integration are determined from several inputs.
 

  • Sales Area of the customer being invoiced/credited
  • Sales Type (or price list) of the customer being invoiced/credited.
  • Stock Category of the item being invoiced/credited.

A table of sales general ledger accounts is maintained and can be modified from the setup tab. When an invoice is created from the ConfirmDispatch_Invoice.php script the system uses a function defined in GetSalesTransGLCode.inc to look up the general ledger codes that are appropriate. By default this function uses the following logic:
 

  • If there is a record in the SalesGLPostings table that has a matching Area, SalesType and Stock Category then the function returns the sales account and the discount account applicable.
  • If there is a match for the Area and SalesType using the default Stock Category (ANY) then the codes applicable to this record are returned.
  • Then if there is a matching Sales type, stock category with default (AN) area this is used.
  • Then if there is a matching stock category record using the default area (AN) and the default salestype (AN) this is used - finally
  • If there is no record is found after trying the above combinations then the GL Code for the default area, sales type and default stock category is used - this is GL code 1. If GL Code 1 is not defined, then it will be created.

Since the logic of how the general ledger account is determined is defined in this function it is relatively simple to change this to what is most appropriate for the business.

The freight recovery and the debtors control account used are those defined in the company preferences page.

The tax account used is the account defined in the tax authorities definition used for the customer being invoiced.

Stock Journals

The general ledger accounts that are used for posting sales transactions are determined using the sales area, the sales type of the customer being invoiced/credited and the stock category of the item being invoiced/credited. A table of general ledger accounts is maintained and can be modified from the set up tab from the link "COGS GL Interface Postings". The same logic as above is applied and the function is defined in the same GetSalesTransGLCode.inc script to look up the appropriate GL codes. Again, since the logic of how the general ledger account is determined is defined in the function GetCOGSGLAccount, it is relatively simple to change this to suit the business.

The account to credit stock with for the cost of goods sold is determined by reference to the stock item being sold. The stock category of the item is retrieved and the general ledger codes applicable to the stock category are used.

The profit and loss accounts used for stock adjustments are also determined by reference to the stock category record.

The profit and loss account used for posting the variance between standard cost of a purchased item and its actual cost as invoiced is also determined from the stock category record.

EDI

EDI stands for electronic data interchange - the electronic transmission of transaction information between trading partners. There are numerous standards for the encoding of such transactions the most widely used being UN/EDIFACT and its derivative EANCOM implementation. In fact many industry groups use the standard formats in slightly different ways and some businesses within the industry use the industry standard slightly differently again. So ultimately, the standards are really only a framework for what the actual messages look like. In implementing EDI in webERP, some flexibility in the format of messages to be sent and received is available. EDI messages are created in flat files in the directory specified in config.php for EDI outgoing messages and a log of the EDI messages sent is retained also. The messages can be sent either as an email attachment to the customer supplied email address or via ftp to a customer supplied ftp server address - using the ftp username and password provided by the customer.

EDI Setup

To enable EDI transactions for a customer, first select the customer from the Select Customer link on any page, then click the link - Customer EDI Configuration. This page allows selection of the type of transactions that are to transmitted electronically currently only invoices/credit notes and orders are available. Each must be specifically enabled to activate them. Each customer must have their:
 

  • EDI reference that they are identified by
  • Transport mechanism and address to which the invoice/credit note messages are to be sent - either email as a file attachment or via ftp (file transfer protocol)

If the transport mechanism is to be ftp - this must be compiled into PHP with the flag -enable-ftp, most windows PHP installtions have this by default now. Additional fields for the ftp server username and password will also be required.

To activate EDI polling for invoices to send the script EDISendInvoices.php must be run as a scheduled job - using cron or some other scheduling system -see automating sales reports. It can also be run from the utilites menu Z_index.php with debugging output.

To activate EDI polling for orders to be entered as received the script ???? must be run as a scheduled job using cron or some other scheduling system.

Sending EDI Invoices

EDI messages are made up of segments which must appear in a certain order. Since customers will require EDI invoices in slightly different formats, the exact format can be defined in the table EDIMessageFormat. This table has a record for each customer invoice line and the sequence when it must appear in the message. The field line text in this table can include any of the predefined EDI invoice variables surrounded by "[" and "]" to denote them as a variable to be replaced with the appropriate value as follows:
 

EDI Invoice Detail Section
EDITransNo The unique EDI transaction number
InvOrCrd Whether the transaction is an invoice or a credit - the value of this variable is an EANCOM defined number, 388 for a tax invoice and 381 for a credit note
TransNo The transaction number of invoice or credit
OrigOrDup Whether the transaction is a duplicate or original sending the value of this variable is an EANCOM defined number 7 for a duplicate and 9 for an original
TranDate The transaction date in the format CCYYMMDD
OrderNo The original order number - only for invoices
CustBranchCode The customer's internal code for the branch
CompanyEDIReference The customer's EDI reference code
BranchName The branch name
BranchStreet  
BranchCity  
BranchState  
TaxAuthorityRef The businesses Tax Authority reference number
DatePaymentDue The due date for this transaction
TaxTotal The total amount of tax on the transaction
EDI Invoice Detail Section - for the lines on the transaction
LineNumber  
StockID The webERP item code
CustStockID The customer's internal code for the item
ItemDescription  
QtyInvoiced Quantity invoiced or credited
LineTotalExclTax The total for the line excluding tax
UnitPrice Unit price for the item
LineTaxAmount The tax applicable to the line
EDI Invoice Summary Section
NoLines The total number of lines on the invoice/credit
TotalAmountExclTax Total amount of the transaction excluding tax
TotalAmountInclTax Total amount of the transaction including tax
NoSegments The total number of segments in the transaction this is required as a control check in the summary

There is therefore great flexibility in how the messages are defined. The variables for the summary and heading sections can be used in any section. The detail section variables can only be used in the detail section.

Most customers will require that the branch to which the invoiced goods are delivered to, be identified using the customer's coding system. It is therefore important to ensure that the customer's branch code is actually entered against the webERP branch record. The variable CustBranchCode is retrieved from the branch record and if it is not entered then the EDI transaction will fail.

Some customers may also require the item code to be their item code, not the webERP item code. The variable CustStockID is derived from the cross reference table EDIItemMapping which would need to contain a cross reference record for each item that they may buy.

The script that creates the EDI invoices (EDISendInvoices.php) should be run automatically in the background as a scheduled task. It first gets a list of all customers who should receive EDI invoices (or credit notes) - as determined in the settings of their DebtorsMaster record. Then the script goes through each customer returned in turn to get any invoices or credits that have not already been sent. A flat file is created for all the customers invoices and credits and sent to the customer using the transport, address and other parameters defined in the customer edi setup page - recorded against their DebtorsMaster record. There is a link to enable the script to be run manually - the browser will also show the output of the EDI message