If a UDR is to update a ledger it must be configured in a particular way. The document needs to be defined so that the ‘Invoice’ template falls within a $SUBHEADER and $SUBTOTAL control blocks. Both control blocks should be set to break on the same item, typically an account code or order reference. A typical structure would be:
$HEADER
$SUBHEADER, STARTINV, BREAK (ACCOUNT)
Here you would have general account information – name, address, etc.
$RECORD
Here you would have individual invoice lines / details – qty, price, nominal, etc.
$SUBTOTAL, ENDINV, BREAK (ACCOUNT), PAGEAFTER
Here you would have final totals plus more account information
$END
To update the ledger you copy details from within the report structure into accumulator items, these are then cross-referenced for update to the ledger. Whenever a data item is printed on the report you have the option to save it to an accumulator.
Accumulators – creating accumulators is similar to creating extra database data items.Within the UDR set-up, select ‘Parameters’ and then ‘Accumulators’, you will then be presented with a blank database structure for you to add your own items.
Within the accumulators you need to configure selected items for update to the transaction file. For each invoice you need a minimum of
DATE
ACCOUNT
REF
DESCRIPTION
GOODS AMOUNT
TAX CODE
TAX AMOUNT
In addition you may want to take over Nominal, Analysis or Stock Codes or unit costs and quantities. You need to create a data item for each of these values within the accumulators.
If creating multiple analysis lines, i.e. each invoice will have more than one database record processed for it, then special consideration is required for the Goods Amount and Tax Amount accumulators. For multiple analysis lines you will a need an accumulator for the $RECORD and an accumulator for printing the document totals within the $SUBTOTAL.
A typical structure would be:
Name Type Description
LINE-GOODS K, L Holds the goods amount for current $RECORD
TAX-RATE K, L Holds the tax rate for the tax code on this $RECORD
LINE-VAT * Calculated item giving the VAT amount using a calculation of goods amount and tax-rate. Calculation would be ‘Line Goods x Tax Rate / 100’. To use a value of ‘100’ in an accumulator calculation you would reference a constant item, i.e. C1, and you would set this accumulator constant as 100
LINE-TOTAL * calculate item adding Line Goods and Line VAT
TOTAL-GOODS K, L Holds the total goods amount of the invoice
TOTAL-VAT K, L Holds the total VAT amount of the invoice
TOTAL K, L Holds the total value of the invoice
INV-DATE D Holds the date of the invoice
INV-REF X (6) Holds the reference number
INV-DESC X (30) Holds the document description
ACCOUNT X (10) Holds the customer/supplier account code
NOMINAL X (16) Holds the nominal analysis code
ANALYSIS N Holds the customer/supplier analysis code
STOCK X (16) Holds the stock code (if any)
UNIT-COST K, L Holds the unit cost (if any)
QUANTITY K, L, M Holds the issue quantity (if any)
Several of these items would be set to copy to the accumulators from the $SUBHEADER – these would include INV-DATE, INV-REF, INV-DESC and ACCOUNT. All others (except document totals) would come from accumulators copied from the $RECORD.To update the ‘Total’ items you need to print the accumulator line values and set to copy to the total accumulator items.
Tips on Copying to Accumulators
If you need to copy an item to the accumulators, but don’t want to print on the document, set to print after column 80/132 (whatever the maximum width of the document).
As each $RECORD is processed then items are copied to the accumulator and an analysis line is generated. When the next $RECORD is processed text items will be overwritten with the new copy but numeric items will be added (hence accumulators). To avoid this create a control block $RECORD, NOPRINT before the existing $RECORD and print the line value accumulated and in the print parameters set them to be zeroed.
If Premier, you can create a calculation on the input database to derive the tax rate from the tax code using special calculation item ‘!C11’. On other product levels you would need to create a tax lookup database containing the tax code.
We would recommend getting the document print configured (including copy to the accumulators) before proceeding to define the link to sales/purchase ledgers.
When saving document only (and not generating transactions) you still need accumulators for the account, date, reference, and description. If the document has a value then you need to update into Amount and Tax Amount.
Article ID: 1256
Created On: Fri, Jun 29, 2012 at 12:06 PM
Last Updated On: Tue, Jun 20, 2023 at 5:15 PM
Online URL: https://kb.datafile.co.uk/article/configuration-user-defined-report-set-up-1256.html