Home > Technical > Processes > Accounts

Accounts

The Accounts system provides the basis for creditors, debtors, loyalty points, gift accounts, and so on.

There are three main tables that are used for the accounts functionality - ACHDR, ACDTL and ACALLOC.

ACHDR

Contains one record for each account. The lintAccountCode is used to uniquely identify the account.

ACDTL

Contains one record for each transaction made on an account. An ACDTL record is linked to the ACHDR via the lintAccountCode. Each ACDTL record can be identified by the ctrTransNumber field.

ACALLOC

Contains links between account transactions. For example, we have a payment transaction which is to be allocated against five sales. We would have five ACALLOC records linking the payment to the sales.

Other tables such as ACLEDGER, ACTERMS, ACTRANSCODE contains codes that support the working of the account systems.

Account Ledgers

All accounts in the system use the same basic underlying structures and logic. However, it makes sense to group similar accounts together. This is done by using ledger codes. All ACHDR records have a ledger code stored in the strLedgerCode field.

Ledger Code

Description  

Usage

When Are These Types Of Accounts Created?

SUPPCR

Supplier Credit Accounts

Records invoices received from suppliers.

Created automatically when a supplier is created.

SUPPDR

Supplier Debit Accounts

Records rebates that are being claimed from suppliers.

Created automatically when a supplier is created.

MEMCR

Member Credit Accounts

Used by customers. Customers can charge sales to their account and pay later.

Created on demand.

MEMDR

Member Debit Accounts

Used by customers. Customers can prepay an amount and then use that available amount for sales.

Created on demand.

MEMPTS

Member Points Accounts

Used to record loyalty points earned by customers.

Created automatically when a member is created.

GIFTAC

Gift Accounts

You can sell a gift card to the user with a specific balance. This creates an account. The card can then be used to pay for sales. Similar to MEMDR but is for anonymous customers.

Created on demand.

When an account program is run, one of the parameters is the ledger code, for example DataEntryItems.exe ACCOUNT MEMCR

This means only accounts of that type will be available within the function. This allows you to separate customer and supplier accounts functions and for security to be assigned accordingly.

Identifying An Account

The primary field for an account is the lintAccountCode. This is a number generated by the system.

You can also identify an account through the use of three fields on ACHDR:

strLedgerCode

The type of account (see Account Ledgers above).

strAccountCode

This is the code of the original entity for which the account was created. For example, if this is a supplier account, this field will contain the supplier code (SUPPLIER.strSupplierCode). If it was a member account, it would contain the customer's number (CUSTOMER.lintCustomerNum).

strTradeCode

The location to which this account can apply. See Account Locations below for more information.

Account Locations

When a store has an account with a supplier, is that an account specific to that store? Or is it a common account used by stores? If a customer has an account, is it specific to that one store or can the account be accessed at any store? The answer to these questions will depend on each business. To cater for this we have Account Locations. In the Location screen is a tab for Accounts. In here you specify the Account Location for each ledger type for the current location. The Account Location specifies how the account is to be shared. For example, if you are editing the SHOP1 location and you enter the Account Location as SHOP1 for the SUPPCR ledger, this tells the system that the account is specific to SHOP1 and can't be used elsewhere. If you had set the Account Location to ALL, this would mean that the account could be used by any other store whose account location was also ALL.

Location Code

Ledger

Account Location

Comment

SHOP1

SUPPCR

SHOP1

Accounts created for SHOP1 can be used only by SHOP1

SHOP2

SUPPCR

SHOP2

Accounts created for SHOP2 can be used only by SHOP2

SHOP1

MEMCR

ALL

Accounts created for SHOP1 will be for ALL and can be used by any other locations with Account Location = ALL.

SHOP2

MEMCR

ALL

Accounts created for SHOP2 will be for ALL and can be used by any other locations with Account Location = ALL.  

So how does this work technically?

In ACHDR there are three fields: strLedgerCode, strAccountCode and strTradeCode. The strTradeCode contains this Account Location. So in the SUPPCR examples above, SHOP1 will check to see if an ACHDR exists for SHOP1. If it doesn't it will create one. When SHOP2 creates its account, it will check to see if an ACHDR exists for SHOP2. If it doesn't it will create one. So we now have two ACHDR records for the same ledger code (SUPPCR) and supplier code. The location code is what separates the two accounts.

When we need to use an account for SHOP1, we look up the Account location and then use that to look up the appropriate account.

Sale Location (SHOP1)--> Account Location (SHOP1) --> Account (SHOP1)

Sale Location (SHOP2)--> Account Location (SHOP2) --> Account (SHOP2)

This would give us accounts as follows:

Account Location: SHOP1

SHOP1

SALE

10

SHOP1

SALE

20

Balance

 

30

Account Location: SHOP2

SHOP2

SALE

  5

SHOP2

SALE

15

Balance

 

20

In the MEMCR examples, we have shared accounts. SHOP1 creates their account. It will check to see if an ACHDR exists for the ALL location. If it doesn't it will create it. Now when SHOP2 goes to create its account, it will check to see if a ACHDR record exists for ALL. Since it does, it will not create the account. It will simply use the existing account. So we only end up with one ACHDR record serving both locations.

So when we need to use an account, the links will take us through to the same account for both locations.

Sale Location (SHOP1)--> Account Location (ALL) --> Account (ALL)

Sale Location (SHOP2)--> Account Location (ALL) --> Account (ALL)

This would give us accounts as follows:

Account Location: ALL

SHOP1

SALE

10

SHOP1

SALE

20

SHOP2

SALE

  5

SHOP2

SALE

15

Balance

 

50

If you don't add the account locations to the location screen until after you have started creating suppliers, or you add a new supplier or a new location later on,  the system will go back and create all the required accounts.

Account Balances

All accounts have an account balance stored in ACHDR.curBalance. As a transaction is added to the ACDTL an insert trigger will update the ACHDR.curBalance. This is the only way the balance is updated. The advantage of using a trigger is evident when installing sites that use replication. You can replicate the ACDTL from the store sites to head office. As the ACDTL records are insert at head office, the ACDTL trigger will be fired and the ACHDR will be updated to provide an overall balance for all transactions.

As of 5.14 there was a new balance added called Balance To Date (stored in ACHDR.curToDateValue). Although it is updated for all accounts, it is primarily used for Point accounts. The idea is that the balance will be reset to zero as of a certain date. As sales and adjustments are made, this balance will be adjusted. Sales will always adjust the balance. Payments never adjust the balance. Adjustments may or may not adjust the balance. When you make an adjustment, you select an adjustment type (stored in ACTRANSCODE). This type contains a flag which controls whether or not the Balance To Date will be adjusted. The idea of this balance is that it shows you the sales the customer has made, which is used to calculate whether or not the customer's current type should be upgraded/downgraded/retained, for example a SILVER member has earned enough points to be upgraded to GOLD.

Example: Customer made 9000 points for sales but redeemed 8000 points. The normal balance would be 1000 points but you would not want to base the customer's status on this. The Balance To Date would be 9000. You calculate the customer's type on this and then it gets reset to zero. The balance will go up as new sales are made and the cycle repeats.

Expiry/Due Dates

On the ACDTL record is a field called dtmDue. The exact use of this field (and the way it is set) depends on the type of ledger. If it was for supplier or member credit/debit accounts, the field would be when the transaction would be due. If it was for member points it would be the date the points would expire.

Due Date For All Non-Points Accounts

These sorts of accounts generally work on trading terms, for example invoices are 30 days net. The system looks at the trading term on each account (ACHDR.strTermCode). It looks up the corresponding record in ACTERMS. There is information in there about how many days before the transaction is due and whether the date should be rounded to the end of the month.

Expiry Date For Points Accounts

The system uses the rules defines in ACEXPIRYRULES to calculate the expiry date. This allows much greater flexibility as to when the points expire. for example Points on Food can expire later than points on Drinks.

Allocated Amounts/ Due Amounts

On each ACDTL record there are two important fields. curAmount and curAllocated. curAmount is the value of the transaction. So if there was a sale of $100, the curAmount would be $100. The curAllocated field is how much of this transaction has been paid (or allocated). This will initially be zero. But as one transaction is allocated to another, the curAllocated field will change. (This is also where the ACALLOC table comes in).

Lets say we have transactions as follows:

Transaction

Trans#

Date

Amount

SALE

1

1/1/2008

$100

SALE

2

2/1/2008

$50

PAYMENT

3

15/1/2008

$-60

The payment should be allocated against the sales so we can see how it was used. Lets say the payment was to make Sale 2 fully paid and the remaining $10 was for Sale 1. We'd have a table like this:

Transaction

Trans#

Date

Amount

Allocated

SALE

1

1/1/2008

100

$10

SALE

2

2/1/2008

50

$50

PAYMENT

3

15/1/2008

-60

$-60

The sum of the Allocated column should always be zero because the amount allocated to the payment would be the negative value of the amount allocated to the sales.

Now a sale may have multiple payments allocated to it. The value of curAllocated on the sale transaction would be the sum of those allocations. But you would not be able to see where those allocations came from. Similarly, a payment may be allocated to multiple sales, but you would only see the total allocated from the payment, not the sales to which it was allocated. This is where the ACALLOC table comes into it. It records the links of the allocations. So in the example above we would have two ACALLOC records.

Source

Destination

Value

3

1

10

3

2

50

The sum(Value) where Source = 3 is the allocation of the payment. The sum(Value) where destination = 2 is the total allocations to that sale.

Payments always need to be allocated. Sales are only ever allocated against. They can't be allocated to other transactions. For an adjustment, you select the adjustment type (from ACTRANSCODES). There is a flag on this table to determine whether or not the adjustment should be allocated or not. For an adjustment which was similar to a sale, for example MISSED SALE, you would not have the allocation flag on. You will eventually allocate a payment to it. But for a discount, you probably would have the flag checked on so the adjustment can be allocated to existing sales.

So why do we need to allocate the payment? Its important when calculating balances and overdue amounts.

Look at the following tables. In the first table, we can see a sale followed by a payment. The payment has not been allocated to the sale. In the second table, the payment has been allocated. Notice in both cases the sum of Amount - Allocated is the same as the sum of the Amount. Both fields can be used to give the overall balance.

But lets say we want to calculate the amount due and the overdue periods. In the first table it looks like there is a sale of $100 due on 1/1/2008. However, there was a payment made so it looks like we owe them $20.So even though we can determine the overall balance, we can't determine the overdue amounts. When the payment has been allocated (as in the second table), we can see that the overdue amounts are easy to determine. We can see that as of 1/1/2008 we have a sale that is still $80 overdue.

Table 1. Unallocated Payment

Transaction

Date

Amount

Allocated

Amount - Allocated

SALE

1/1/2008

100

0

100

PAYMENT

15/1/2008

-20

0

-20

Balance

 

80

0

80

Table 2. Allocated Payment

Transaction

Date

Amount

Allocated

Amount - Allocated

SALE

1/1/2008

100

20

80

PAYMENT

15/1/2008

-20

-20

0

Balance

 

80

0

80

So we can see the important of allocating transactions. But how are the allocations done? They can be done manually in the Accounts function. When a payment or appropriate adjustment is done, the user can allocate the amount to other transactions. The user can manually allocate the value to specific transactions. The user can also press a button to allocate to oldest. Basically the system will go through the ACDTL records, oldest to newest. It will examine the Amount - Allocated. If there is a non-zero balance, the transactions will be allocated to.

Lets say we have to allocate a payment of $30. We would have this:

SALE

10

10

SALE

15

15

SALE

12

5

 

 

30

Let's say we had some adjustments in there which had not been allocated and were of a negative value. The system will attempt to allocate to these negative transactions in order to clear them out.

SALE

10

10

ADJ

-3

-3

SALE

15

15

SALE

12

8

 

 

30

Notice the total allocated amount is still 30.

There is also an Unallocated Payments Report. When this is run it looks for all payments that have not been allocated and reports them. The user can manually adjust the transactions (as described above) or they can run the report with the adjust option checked on. In this case, it will find the account the payment is on (the same way as if the user had opened it via the Accounts screen), find the payment and then allocate using the same allocate to oldest method as described above. This report is useful if you take payments at the POS, where th PO user won't be able to perform allocations.

You should always ensure that all allocations are up to date before running account statements. When statements are run it shows two lots of transactions:

1. The full amount of all transactions made since the statement start date. ACDTL.curAmount where dtmTrans > statement date. These are the transactions performed since the last statement was we want to see full amounts of payments, sales, and so on.

2. The outstanding balance of all transactions made before the statement start date. ACDTL.curAmount - ACDTL.curAllocated where dtmTrans < statement date. These are the transactions performed on the last statement or prior. For use on the statements, we can either sum the transactions to get an opening balance or we can show each transaction and the outstanding amount. It depends on whether you want a balance bought forward or open item type of statement.

Automatic Account Adjustments

There is an process called Account Adjustments (MERCHANTEOD.EXE ACCOUNTADJ) that will allow bulk account transactions to be raised on various accounts. Basically this will create transactions to the nominated value. You can have three types of transactions:

Transactions Of A Specific Value

You want to charge a $50 membership fee to all accounts. Simply creates an account transaction record of $50 for each of the specified accounts.

Clear Accounts

You want to create a single transaction that will reduce the account balance to zero. This will get the ACHDR.curBalance and create a transaction of that value for all selected accounts which have a non-zero balance.

Remove Expired Transactions

This one is used primarily for point accounts. When points are recorded, an expiry date is calculated, for example I get points on the 1/1/2008 and they will expire in two years - 1/1/2010. This type will go through and find the sum(ACDTL.curAmount - curAllocated) where dtmDue < required expiry date. It then creates a transaction of that value and allocates it to those expired transactions.

 

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)