GM-X ERP for Blockchain - The ACCOUNTS RECEIVABLE subsystem

By Nelson Nones

12th December 2018
Amended 21st March 2024

Introduction

1. Organisational Structure

AR_PARTY_RELATIONSHIP table

2. Customer Payment and Settlement Processing

SETTLEMENT_HEADER table

SETTLEMENT_ITEM table

SETTLEMENT_ITEM_APPLIED table

SETTLEMENT_STATUS_TYPE table

SETTLEMENT_STATUS_HIST table

3. Constructing Journals for the General Ledger

Journal Entries Constructed from Invoices, Credit Notes and Debit Notes

Segment 1 - Invoice/CN/DN components

Segment 2 - Delivery costs

Segment 3 - Sales Taxes

Segment 4 - Sales Commission

Segment 5 - Pension expenses

Journal Entries Constructed from Settlements

Overview

Calculating Foreign Exchange Gain or Loss at Settlement Time

Traversing the SUBSYSTEM_JE_TEMPLATE table (receipts)

Determine the account_id from an account_usage value (receipts)

Debits and Credits for First Side and Contra Side Entries (receipts)

INVOICE_ADJUSTMENT records (tax) (settlements)

INVOICE_ADJUSTMENT records (non-tax) (settlements)

INVOICE_ITEM records

INVOICE_ITEM_ADJUSTMENT records

INVOICE_ITEM_FEATURE records

PAYMENT_HEADER records (commissions and fees)

PAYMENT_HEADER records (unapplied cash)

PAYMENT_HEADER records (sales commission)

4. Control Data

Amendment History

Introduction

Accounts receivable is an account in the General Ledger that shows money owed to a business entity by customers who have purchased goods or services from the entity on credit.

The accounts receivable process starts when a business entity sells a good or service and notes the expected payment terms, discounts and credit guidelines on a commercial invoice to the customer. When a payment arrives from the customer, it needs to be recorded and the business entity may also need to issue a payment receipt or tax invoice document. Payments may be made in many different forms including cash, cheques, electronic funds transfers and credit or debit card transactions. Those payments may be made on or delivered directly to the business entity's premises or may be handled by third parties such as agents or banks acting on the entity's behalf.

Business entities need to have meticulous rules for issuing credit and collecting debts in a timely manner, and they need to keep good data on customers. To maintain accurate accounts receivable, payments also need to be applied to the correct customer and precise invoice. Additional business processes include tracking accounts receivable to determine if they are overdue, dunning customers to speed payments and responding to customer queries.

A business entity's collections department would use an ageing report to track and list unpaid customer invoices and the length of time each invoice has remained unpaid, usually in increments of 30, 60 and 90 days, or more. An ageing report is a way to determine if a business entity is collecting money from customers and reducing accounts receivable efficiently.

Business entities can also leverage their accounts receivable by using unpaid bills as an asset to obtain credit through what is known as "asset-based lending". In seeking to win credit backed by the money it is owed, a business entity's accounts receivable turnover ratio becomes an important factor. The ratio counts the number of times the entity collects its average accounts receivable over a year and is a way to determine its skill at converting receivables into cash. An asset-based lender might look at the turnover ratio to determine the likelihood of being repaid or set an interest rate for lending money to the business entity based on its accounts receivable.

Accounts receivable are also important because they can be sold by a company to a "factor," such as a bank or other financier. Factoring, or the sale of accounts receivable, raises money for a business entity and boosts the seller's cash flow, but it also involves paying a fee to the factor in return for faster turnover. The factor pays the business entity for its accounts receivable and then collects the money due from customers.

Organisational Structure

The PARTY, ORGANISATION and PERSON tables contain entries for every organisation or person which is required to be known to the GM-X application.

At least one of these parties is an organisation which is the "client" and it is the one for which the application is being run. This organisation is known as the "functional unit" and can be identified by adding it to the PARTY and ORGANISATION tables using a single party_id. This data provides the following:

• The identity of the internal party in various transactions, such as sales orders and invoices.
• The home currency for all such transactions, which is known as the "functional currency".

It is also possible for a GM-X installation to handle the business for a group of organisations which do business under separate names, possibly using different functional currencies. Those organisations can be added as functional units to the PARTY and ORGANISATION tables. Each of them is identified in those tables by its own party_id.

The GM-X application can maintain separate general ledgers for these organisations. In this case each of those organisations must be added as a business entity to the FUNCTIONAL_UNIT table using its party_id, in addition to the PARTY and ORGANISATION tables.

When managing accounts receivable, each of those functional units has relationships with external parties which are defined on the AR_PARTY_RELATIONSHIP table for various role types including:

• Customer: a party who is the debtor, having been supplied goods and services from the functional unit. Customers may be organisations or persons. Often, particularly when the customer is a larger organisation, the customer role type is split into various subtypes such as 'Ship To Customer' representing the party who received the goods and services, and 'Bill To Customer' representing the party who is the debtor responsible for paying the debt. The relationship between those parties can also be defined on the AR_PARTY_RELATIONSHIP and PARTY_RELATIONSHIP tables.
• Bank: an organisation to which customers transfer funds electronically to settle specific debts owed to the functional unit, or which extends credit or guarantees to customers who use credit cards (or other instruments such as letters of credit) for the purpose of settling specific debts owed to the functional unit. Many banks also offer "lockbox" services which provide post office boxes, or other centrally-located unified bank mailing addresses, to which customers can mail cheques for immediate deposit into the functional unit's bank account. The bank will then provide daily reports allowing the functional unit to apply payments to the correct customers and invoices or may take responsibility for applying those payments and reporting those actions to the functional unit.
• Agent: an organisation or person who collects money owed by a functional unit's customers and may also issue payment receipt or tax invoice documents to customers on behalf of the functional unit. The agent, in turn, remits the collected funds to the functional unit's bank (or allows the functional unit to withdraw funds from the agent's account and remit them to the bank) after deducting the agreed agency fee, and reports its actions to the functional unit. The agent role type could be split into many different subtypes depending on the functional unit's industry and the business circumstances. Some of the possible role subtypes include 'Attorney', 'Collection Agent', 'Payment Service Provider' (such as PayPal), 'Travel Agent' and 'Vending Route Driver' but this is not an exhaustive list.
• Tax authority: depending on the relevant tax laws, the supply of goods and services, issuance of customer invoices and/or receipt of customer payments may be taxable events which must be reported to tax authorities. These events frequently require the functional unit to apply surcharges onto invoices in order to recover the taxes due from customers, when a sales, use or value-added tax (VAT) is imposed. In these cases, the functional unit may have to issue a tax invoice to the customer when the tax is imposed, evidencing the value of the goods and services supplied as well as the applicable rates and taxes thereon; and the taxes are treated as amounts due and payable immediately to the tax authority. In addition, some jurisdictions require customers to withhold corporate or personal income tax when making payments, and remit the amounts withheld to a tax authority instead of the functional unit. In these cases, the customer must issue a withholding certificate document to the functional unit evidencing the amount withheld, which is treated as an income tax prepayment to the tax authority on the functional unit's accounts.
• Asset-based lender: an organisation or person which extends credit to the functional unit backed by the functional unit's accounts receivable.
• Factor: an organisation or person which purchases the functional unit's accounts receivable in exchange for an immediate cash payment to the functional unit. As the new owner of those accounts receivable, the factor then takes full responsibility for debt collection.
• Salesperson: an external organisation or person, such as a manufacturer's representative, which receives a commission for sales made to the customer but only when invoiced amounts are collected. If two or more salespersons split the commission, then the percentage split is determined by the rel_weighting_factor entry on each salesperson's PARTY_RELATIONSHIP record.

When the GM-X installation handles business for a group of related functional units, it is possible for any of those functional units to be customers, agents, asset-based lenders or factors of the others, and these intercompany relationships may also be defined on the AR_PARTY_RELATIONSHIP and PARTY_RELATIONSHIP tables. In these situations, accounts receivable owed by related parties are usually carried separately on each functional unit's accounts in order to segregate the cumulative effects of business transactions attributable to internal parties from those of external parties.

This is an important consideration when drawing up consolidated financial statements, because intercompany business transactions must offset one another during the consolidation process. For example, an amount owed to one functional unit (the creditor) by a related functional unit (the debtor) would be treated as an asset by the creditor and an offsetting liability by the debtor which eliminates the amount from the group's consolidated balance sheet. Similarly, revenues derived by one functional unit from supplying goods and services to a related functional unit would be treated as revenues by the supplier and as offsetting expenses by the customer which eliminate the revenues from the group's consolidated income statement. The accounts reserved exclusively for intercompany transactions are known as "elimination accounts" and may be identified as such in the GM-X general ledger subsystem.

The parties in internal as well as external trading relationships may utilise the same or different functional currencies. When the functional currencies are different, both parties must agree to use a single currency for invoicing and payment purposes whenever goods and services are sold. When the agreed currency is also the functional currency of one party, the opposite party in the relationship bears the entire foreign exchange risk; but when the agreed currency is neither party's functional currency, both parties bear the risk. Unless those risks are hedged through forward future contract instruments, each risk-bearing party must periodically revalue its accounts receivable which are not denominated in its functional currency to account for the effects of exchange rate fluctuations, and the revaluation gains or losses must be accumulated in a book foreign exchange gains or losses account. When the customer makes a payment, cumulative revaluation gains or losses attributable to the payment must be cleared from the book gains or losses account and written off to crystallised foreign exchange gains or losses, and any further differences in functional currency amounts received which are attributable to exchange rate fluctuations since the last revaluation (if any) must also be written off to crystallised foreign exchange gains or losses in order to fully clear accounts receivable.

In addition to the trading relationships described above, personnel who carry out accounts receivable processes for or on behalf of each functional unit, either as employees or as contractors to whom the work is outsourced, have relationships with the functional units which may be defined on the PARTY_RELATIONSHIP table for various role types and subtypes such as salesperson, data enterer, payment processor, approver, collector, credit analyst, controller and chief financial officer.

Figure 1 - Organisational Structure for Accounts Receivable: Business Entities and Customers

AR_PARTY_RELATIONSHIP table

This extends the existing PARTY_RELATIONSHIP table of the PARTY subsystem.

Tasks are available to users of the FINANCE-AR subsystem to create, enquire, update and delete AR_PARTY_RELATIONSHIP records for customers. These will perform the same functions as the existing tasks available to users of the PARTY subsystem to create, enquire, update and delete PARTY_RELATIONSHIP records, and in addition will allow the extra fields to be maintained. Together with the existing PARTY subsystem tasks for creating, enquiring, updating and deleting PERSON and ORGANISATION records, these tasks are functionally equivalent to the customer master data management functions of traditional ERP systems. This serves the purpose of allowing master data maintenance tasks performed on customer relationships to be completed in a single step, rather than two.

Although the order in which two parties are specified in a PARTY_RELATIONSHIP does not matter, in this instance the functional unit (supplier) will always be party_id_1 and the customer will always be party_id_2. Values to be used in role_type_id_1, role_subtype_id_1, role_type_id_2 and role_subtype_id_2 will be defined within the application.

Customer Payment and Settlement Processing

Settlements of open customer invoices and customer debit notes using customer payments and customer credit notes are recorded on the SETTLEMENT_HEADER, SETTLEMENT_ITEM and SETTLEMENT_ITEM_APPLIED tables. The SETTLEMENT_ITEM table holds an entry for each invoice/credit note/debit note component which is to be settled with this payment. The SETTLEMENT_ITEM_APPLIED table holds an entry whenever a settlement amount is reduced or replaced by an amount which is applied from a credit note. If the amount being applied matches the amount being settled then there is no payment.

Should the need arise to void a settlement (for e.g. in the event a customer payment is not honoured upon presentation to the bank) then a separate SETTLEMENT_HEADER and one or more SETTLEMENT_ITEM records must be recorded which reverse the effects of the original settlement.

Each settlement has at least two states, pending and posted. After a settlement is posted, its amounts and accounts must not be changed. Instead, if corrections are needed, two new settlements should be entered and posted, one to reverse the settlement (same as voiding a settlement) and the other consisting of the corrected customer invoices, customer debit notes, customer payments, customer credit notes and amounts.

Figure 2a - Accounts Receivable Settlements

SETTLEMENT_HEADER table

SETTLEMENT_ITEM table

This holds information about each of the customer payments, customer credit notes and/or customer debit notes used to settle a customer invoice or customer debit note.

SETTLEMENT_ITEM_APPLIED table

This identifies when all or part of a settlement amount comes from either a debit note or credit note. Each record must point to one and only one of the 4 invoice components which must also be the same type of component on the parent SETTLEMENT_ITEM record. Each applied_value_tx will reduce the amount which will be received from the customer.

Note that the values for applied_value_tx and applied_value_fn will be accumulated into the corresponding columns on the parent SETTLEMENT_ITEM table.

Figure 2b - Accounts Receivable Settlement Status

SETTLEMENT_STATUS_TYPE table

This holds user-definable settlement status values. Values used by the software are defined in a system file and cannot be altered.

Here are the values used by the system which cannot be altered:

SETTLEMENT_STATUS_HIST table

This holds the status history for each settlement.

Constructing Journals for the General Ledger

The SUBSYSTEM_JE_TEMPLATE_HEADER and SUBSYSTEM_JE_TEMPLATE_ITEM tables are used together with general ledger accounts defined on the FUNCTIONAL_UNIT table to construct journal entries from the GM-X INVOICE and FINANCE-AR subsystems. The FUNC_UNIT_PRICE_COMPONENT, FUNC_UNIT_PROD_CAT and FUNC_UNIT_FEATURE_CAT tables can be used to provide alternative account_ids for certain entries.

Figure 3 - Journals originating from the INVOICE and FINANCE-AR subsystem

Together these tables implement account determination logic for invoices, credit notes, debit notes and settlements.

The specifications below refer to a "First Column" and a "Contra Column":

• For customer invoices, customer debit notes and accounts receivable settlements, the First Column is the debit column of the journal entry and the Contra (opposite) Column is the credit column. The Contra Column for invoices and debit notes contains items and adjustments (for e.g. prices and surcharges) which increase the total value of the invoice or debit note. For adjustments which decrease the total value of the invoice or debit note (for e.g. discounts), the First Column is the credit column of the journal entry and the Contra (opposite) Column is the debit column.
• For customer credit notes and when voiding an accounts receivable settlement, the First Column is the credit column of the journal entry and the Contra (opposite) Column is the debit column. The Contra Column for credit notes contains items and adjustments (for e.g. prices and surcharges) which increase the total absolute value of the credit note. For adjustments which decrease the total absolute value of the credit note (for e.g. discounts), the First Column is the debit column of the journal entry and the Contra (opposite) Column is the credit column.

Journal Entries Constructed from Invoices, Credit Notes and Debit Notes

Journal entries constructed from customer invoices, customer credit notes and customer debit notes can have as many as 5 segments. Segment 1 is required, and the other segments are optional depending upon the circumstances described below.

To extract the account for a particular usage, the logic for all segments searches first for an account_id on a SUBSYSTEM_JE_TEMPLATE_ITEM entry for that usage. These are the junior items of the SUBSYSTEM_JE_TEMPLATE_HEADER entries linked to the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to on the INVOICE_HEADER. The SUBSYSTEM_JE_TEMPLATE_HEADER entry utilised is based on:

• je_template_id_invoice for customer invoices;
• je_template_id_credit_note for customer credit notes; and
• je_template_id_debit_note for customer debit notes.

If an account_id exists, it will be extracted, and no further logic is required.

Otherwise, in the event that the applicable SUBSYSTEM_JE_TEMPLATE_HEADER entry is blank, or no account_id is found in a SUBSYSTEM_JE_TEMPLATE_ITEM entry for that usage, the account logic may next search for accounts on the FUNC_UNIT_FEATURE_CAT, FUNC_UNIT_PRICE_COMPONENT and/or FUNC_UNIT_PROD_CAT tables for the party_id_bill_ from, and extract the first such account found. The subsections below provide detailed specifications for those searches. If an account is extracted in this manner, no further logic is required.

Otherwise, the determination logic will attempt to extract the account for that usage from the FUNCTIONAL_UNIT table for the party_id_bill_from.

If the determination logic cannot extract an account by any of the above means, then an error message will be thrown.

Segment 1 - Invoice/CN/DN components

A separate Segment 1 is constructed for each component of a customer invoice, customer credit note or customer debit note. A component is a row in the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT or INVOICE_ITEM_FEATURE table having the same invoice_type and invoice_id values as the INVOICE_HEADER.

Each INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entry is linked to an INVOICE_ITEM which may have a product_id that links to an entry on the PRODUCT table. The product_subtype field on the PRODUCT table had two possible values: 'G' (Good) or 'S' (Service). In this situation, therefore, the determination logic will search by default for:

• The 'sales_supply' usage when the product_subtype is 'G' (Good); or
• The 'sales_service' usage when the product_subtype is 'S' (Service).

However, when the price_type of the applicable PRICE_COMPONENT (if any) for the product_id is 'H' (Hire Charge) instead of 'B' (Base Price) then the determination logic will ignore the product_subtype and will search for the 'sales_use' usage instead.

Alternatively, the product_id field on the INVOICE_ITEM may be blank. In this situation Segment 1 is not constructed for the INVOICE_ITEM; refer instead to Segment 2, below.

Entries on the INVOICE_ADJUSTMENT table which do not represent tax surcharges are not linked to any particular PRODUCT. In this situation the determination logic will search for:

• The 'sales_supply' usage when the order_adjustment_type_id links to an ORDER_ADJUSTMENT_TYPE entry having a discount_or_surcharge value of 'D' (Discount);
• Otherwise, the 'sales_service' usage when the order_adjustment_type_id links to an ORDER_ADJUSTMENT_TYPE entry having a discount_or_surcharge value of 'S' (Surcharge).

Based on the foregoing, the detailed account determination logic for each Segment 1 is set out in the tables below.

Segment 2 - Delivery costs

For recovery of delivery costs, an INVOICE_ADJUSTMENT linked to an ORDER_ADJUSTMENT_TYPE having an order_adjustment_type_id that begins with 'DELIVERY' is required. In this situation a Segment 2 must be constructed from each such INVOICE_ADJUSTMENT.

Also, the product_id field on the INVOICE_ITEM must be blank when the INVOICE_ITEM represents the recovery of nontax expenses from customers. In this situation a Segment 2 must be constructed from each such INVOICE_ITEM.

Segment 3 - Sales taxes

For recovery of sales taxes, or other taxes such as duties or use taxes, an INVOICE_ADJUSTMENT linked to an ORDER_ADJUSTMENT_TYPE having an order_adjustment_type_id that begins with 'TAX' is required. In this situation a Segment 3 must be constructed from each such INVOICE_ADJUSTMENT.

Segment 4 - Sales Commission

This segment accounts for any sales commissions earned by salespersons or sales organisations upon issuance of the invoice.

Determination of whether or not any sales commissions were earned is done at two levels. First, certain PRICE_COMPONENT entries linked to the INVOICE_ADJUSTMENT and/or INVOICE_ITEM_ADJUSTMENT components of an invoice may have the price_type value 'C' (Commission). If any such PRICE_COMPONENT entries exist then a Segment 4 must be constructed for each such INVOICE_ADJUSTMENT and INVOICE_ITEM_ADJUSTMENT, but only if the is_commission_accrued indicator on the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to is YES.

Alternatively, if the is_commission_accrued indicator on the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to is YES, but no Segment 4 journal entries were constructed for INVOICE_ADJUSTMENT or INVOICE_ITEM_ADJUSTMENT components as described in the preceding paragraph, then a Segment 4 must be constructed for the INVOICE_HEADER.

Segment 5 - Pension expenses

This segment accounts for any employer-paid pension expenses incurred and accrued when salespersons earn commissions as described for Segment 4, above.

If the pension_percent field on the FUNCTIONAL_UNIT entry for the party_id_bill_from is not blank and greater than zero then a Segment 5 must be constructed for the INVOICE_HEADER.

Journal Entries Constructed from Settlements

Overview

This procedure applies when a payment has been completed and is posted to the General Ledger. Each amount on each invoice component has to be linked with a General Ledger Account before it can be added to an entry on the JOURNAL_ENTRY_ITEM table.

Each settlement will have a SETTLEMENT_HEADER entry and may have one or more related SETTLEMENT_ITEM entries. Additionally, each of those SETTLEMENT_ITEM entries (if any) may have one or more related SETTLEMENT_ITEM_APPLIED entries.

Each of those SETTLEMENT_ITEM and SETTLEMENT_ITEM_APPLIED entries (if any) will refer to a component of a document which is a customer invoice, customer credit note or customer debit note. Each of those components is represented as an entry on one of the following database tables:

• INVOICE_ADJUSTMENT (tax)
• INVOICE_ADJUSTMENT (non-tax)
• INVOICE_ITEM
• INVOICE_ITEM_ADJUSTMENT
• INVOICE_ITEM_FEATURE

Note that the INVOICE_ADJUSTMENT table has different rules for tax and non-tax entries.

Note also that INVOICE_ADJUSTMENT entries linked to a PRICE_COMPONENT having a price_type of 'C' (Commission) do *NOT* have entries on the SETTLEMENT_ITEM and SETTLEMENT_ITEM_APPLIED tables, so will have to be processed separately.

When an entry on any of these four database tables is settled, either by receipt of money or by applying another entry from one of these four database tables, this action will require two or more entries to be written to the JOURNAL_ENTRY_ITEM table. Every input will require an output for the account_id which has been associated with the usage 'trade_receivables' as a contra side entry, plus one or more additional usages which are identified as required because of relevant data which exists in other database tables.

There are six situations which may cause additional entries to be written to the JOURNAL_ENTRY_ITEM table:

1. A related INVOICE_ADJUSTMENT entry links to a PRICE_COMPONENT having a price_type of 'E' (Early Settlement Discount). In this situation the remitter qualifies for the Early Settlement Discount if payment is remitted on or before the discount due date specified by an entry on the INVOICE_TERMS table; or
2. The total_settlement_value_fn value on the SETTLEMENT_HEADER entry is greater than the sum of all settlement_value_fn values on the related SETTLEMENT_ITEM entries, less the sum of all applied_value_fn values on their related SETTLEMENT_ITEM_APPLIED entries. In this situation the difference is known as "unapplied cash" which creates a liability for the payee; or
3. The is_tax_due_on_accrual field in the AR_PARTY_RELATIONSHIP table is NO. In this situation the output sales tax is only payable to the tax authority when an invoice, credit note, or debit note is paid or settled, at which time the output sales tax accrual associated with the invoice, credit note, or debit note must be reversed; or
4. A related INVOICE_ADJUSTMENT entry links to a PRICE_COMPONENT having a price_type of 'W' (Withholding Tax). In this situation the remitter must withhold part of the payment from the payee as either a provisional or final income tax assessment and remit the withheld amount to a tax authority instead; or
5. The SETTLEMENT_HEADER entry contains a commission_fee_fn value; or
6. The is_commission_accrued field in the AR_PARTY_RELATIONSHIP table is YES; and a commission_percent exists on that AR_PARTY_RELATIONSHIP entry, or a related INVOICE_ADJUSTMENT entry links to a PRICE_COMPONENT having a price_type of 'C' (Commission). In this situation a sales commission has become due and payable to a sales organisation, or one or more salespersons, because money has been received from a customer for a commissionable sale.

In situations involving receipt of money every input will require an output for the account_id which has been associated with the usage 'cash_in_bank' as a first side entry, plus one or more additional usages which can be provided by the logic described in Traversing the SUBSYSTEM_JE_TEMPLATE table (receipts).

In all of the above cases, because only one additional usage is ever identified for a particular monetary value then the value used in the contra side entry can be reversed and used in a single first side entry.

When dealing with customer invoices where the transaction currency is different from the functional currency the exchange rate used when the invoice was created may be different from that which is calculated when the invoice is settled. This results in a different functional currency amount, and this difference must be recorded in the ledger as either a gain or a loss. This revaluation may occur in two different places:

• Each invoice component with an outstanding (unpaid) balance can have the balance in functional currency (the balance column) revalued using the current EXCHANGE_RATE with the difference stored in the reval_value column of that component. Refer to Invoice Revaluations for details.
• The recognition rate of exchange (exchange_rate_actual) on the SETTLEMENT_HEADER may be used to update the values on the SETTLEMENT_ITEM and SETTLEMENT_ITEM_APPLIED entries with the difference being stored in the realised_gain_loss column on those entries. Refer to Calculating Foreign Exchange Gain or Loss at Settlement Time, next, for details.

When payors are legally required to deduct taxes such as withholding taxes or taxes on gross business receipts at the source (i.e. 'tax deducted at source') then this condition is indicated when the is_withholding_tax_applicable field in the AR_PARTY_RELATIONSHIP table for the party_id_bill_to and party_id_bill_from is YES. In this case an INVOICE_ADJUSTMENT linked to a PRICE_COMPONENT having a price_type of 'W' (Withholding Tax) is required, and a Segment 5 must be constructed from each such INVOICE_ADJUSTMENT.

In some situations these are considered final tax assessments, so the payee must recognise them as an expense. This condition is indicated when the is_withholding_tax_final field in the AR_PARTY_RELATIONSHIP table for the party_id_bill_to and party_id_bill_from is YES.

Otherwise those taxes are considered an advance payment of personal or corporate income tax due to the tax authority, so the payee can recognise them as an asset which can be used to clear accrued income taxes. This condition is indicated when the is_withholding_tax_final field in the AR_PARTY_RELATIONSHIP table for the party_id_bill_to and party_id_bill_from is NO.

When payments are received from debtors, the bank or collector may deduct service fees or, for foreign currency payments, currency exchange commissions before remitting the net proceeds to the beneficiary account. The value of any such fees is held in the commission_fee_fn field of the SETTLEMENT_HEADER table and it is always denominated in the functional currency of the party_id_bill_from. In this situation a Segment 6 is required to account for the commission and fee expense.

Lastly, Segment 7 transfers accrued sales commission liabilities to sales commissions payable, when commissionable customer invoices, credit notes and debit notes are settled.

Calculating Foreign Exchange Gain or Loss at Settlement Time

This procedure takes place whenever a different value for exchange_rate_actual has been calculated due to a value being entered for total_settlement_value_fn, on the SETTLEMENT_HEADER table. It involves cycling through the associated SETTLEMENT_ITEM and SETTLEMENT_ITEM_APPLIED entries each of which is linked to one of the four invoice components - INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE. Each of these entries must be processed EXCEPT the following entries which must ALWAYS be skipped and for which settlement gains or losses are *NEVER* calculated:

• INVOICE_ADJUSTMENT and INVOICE_ITEM_ADJUSTMENT entries which are linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission); OR
• INVOICE_ADJUSTMENT entries which are linked to a PRICE_COMPONENT entry having a price_type of 'W' (Withholding tax); OR
• INVOICE_ADJUSTMENT entries which are linked to a PRICE_COMPONENT entry having a price_type of 'E' (Early Settlement Discount); OR
• INVOICE_ADJUSTMENT entries which have a non-blank product_id_free value.

The calculation is slightly different for each payment component. Note that the SETTLEMENT_ITEM_APPLIED entries must be processed before their parent SETTLEMENT_ITEM entries as the latter will require the results produced by the former.

Note also that it is possible for a settlement to deal with two different currencies:

• Functional currency - the currency of the functional unit.
• Transaction currency - the currency of the vendor invoice, credit note or debit note.

The SETTLEMENT_HEADER record stores exactly one exchange rate. It is exchange_rate_actual which is used to convert total_settlement_value_tx into functional currency.

If the functional and transaction currencies are different, then the value for exchange_rate_actual must be calculated as total_settlement_value_fn / total_settlement_value_tx.

1. When exchange_rate_actual is not blank/zero:
   1. For each SETTLEMENT_ITEM_APPLIED:
      1. Multiply applied_value_tx by exchange_rate_actual to provide a new value for applied_value_fn. Round the result to the functional currency's least significant digit.
      2. Subtract balance_fn from applied_value_fn and store the result in realised_gain_loss. Round the result to the functional currency's least significant digit.
      3. Update the parent SETTLEMENT_ITEM entry to store the sum of all applied_value_fn values from its children in its own applied_value_fn column
   2. For each SETTLEMENT_ITEM:
      1. Multiply settlement_value_tx by exchange_rate_actual and store the result in settlement_value_fn after rounding it to the functional currency's least significant digit.
      2. Divide settlement_value_tx by balance_tx and store the result in temporary variable settlement_ratio. This is the proportion of the balance_tx that is being settled.
      3. Multiply balance_fn by settlement_ratio to identify the original settlement amount (before any revaluations were performed).
      4. Subtract the original settlement amount (see 1.b.iii) from settlement_value_fn and store the result in realised_gain_loss after rounding it to the functional currency's least significant digit.
2. Otherwise, when exchange_rate_actual is blank/zero (to reverse the effects of any previous revaluation):
   1. For each SETTLEMENT_ITEM_APPLIED:
      1. Set applied_value_fn to the value of balance_fn.
      2. Set realised_gain_loss to zero.
      3. Update the parent SETTLEMENT_ITEM entry to store the sum of all applied_value_fn values from its children in its own applied_value_fn column.
   2. For each SETTLEMENT_ITEM:
      1. Divide settlement_value_tx by balance_tx and store the result in temporary variable settlement_ratio. This is the proportion of the balance_tx that is being settled.
      2. Multiply balance_fn by settlement_ratio and store the result in settlement_value_fn after rounding it to the functional currency's least significant digit.
      3. Set realised_gain_loss to zero.

Traversing the SUBSYSTEM_JE_TEMPLATE table (receipts)

The identity of the records to be read is held in je_template_id_payment on the AR_PARTY_RELATIONSHIP table for the party_id_bill_to and relationship_seq_no values of the SETTLEMENT_HEADER.

Note that even though a value for je_template_id may be provided, there may or may not be any eligible records on the SUBSYSTEM_JE_TEMPLATE_ITEM table.

Each valid record read from the SUBSYSTEM_JE_TEMPLATE_ITEM table will result in a record being written to the JOURNAL_ENTRY_ITEM table for each document component.

All the available records must be read and processed one at a time. Note that any record with an account_usage which does not appear in the following table for a relevant segment is to be ignored.

To extract the account for a particular usage, the logic for all segments searches first for an account_id on a SUBSYSTEM_JE_TEMPLATE_ITEM entry for that usage. These are the junior items of the SUBSYSTEM_JE_TEMPLATE_HEADER entry linked by the je_template_id_payment field to the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to on the INVOICE_HEADER.

If an account_id exists, it will be extracted, and no further logic is required.

Otherwise, in the event that the applicable SUBSYSTEM_JE_TEMPLATE_HEADER entry is blank, or no account_id is found in a SUBSYSTEM_JE_TEMPLATE_ITEM entry for that usage, the account logic may next search for accounts on the FUNC_UNIT_PRICE_COMPONENT table for the party_id_bill_from, and extract the first such account found. The subsections below provide detailed specifications for those searches. If an account is extracted in this manner, no further logic is required.

Otherwise, the determination logic will attempt to extract the account for that usage from the FUNCTIONAL_UNIT table for the party_id_bill_from.

If the determination logic cannot extract an account by any of the above means, then an error message will be thrown.

Examination of these tables will stop when it finds a record containing a non-blank account_id for the selected account_usage.

Determine the account_id from an account_usage value (receipts)

This procedure is used to convert an account_usage into an account_id. This value may be provided on any one of a number of records which must be searched in the following sequence:

1. SUBSYSTEM_JE_TEMPLATE_ITEM - refer to Traversing the SUBSYSTEM_JE_TEMPLATE table (receipts).
2. FUNC_UNIT_PRICE_COMPONENT (if a value for price_component_id exists)
3. FUNCTIONAL_UNIT table - look for an entry in the column which is named account_id_{account_usage}.

Note that some of these tables can only be read if the input record from the INVOICE subsystem contains the primary key for that table.

As soon as the first non-blank value for account_id is found then the account determination process will be terminated. If at the end there is no value for account_id then an error message will be thrown.

Note that the PRICE_COMPONENT table can only be used for percentage rates (such as tax rates) and sale prices, it cannot be used to provide purchase prices as they are always entered manually.

Debits and Credits for First Side and Contra Side Entries (receipts)

The "contra side" (or "opposite side") refers to the side (debit side, or credit side) of a JOURNAL_ENTRY_ITEM entry which identifies the trade receivables asset account affected by the document component. The credit_functional or debit_functional value comes from:

• applied_value_fn for any of the four invoice components found in the SETTLEMENT_ITEM_APPLIED table; or
• realised_gain_loss for any of the four invoice components found in the SETTLEMENT_ITEM_APPLIED table; or
• settlement_value_fn for any of the four invoice components found in the SETTLEMENT_ITEM table; or
• realised_gain_loss for any of the four invoice components found in the SETTLEMENT_ITEM table.

Monetary amounts for the four invoice components, the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a customer invoice or customer debit note are positive numbers for prices and surcharges, or negative numbers for discounts. This means:

• If they represent prices or surcharges, the contra side entry for these components will always debit the trade receivables asset account.
• If they represent discounts, the contra side entry for these components will always credit the trade receivables asset account.

Monetary amounts on the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a customer credit note are negative numbers for prices and surcharges, or positive numbers for discounts. This means:

• If they represent prices or surcharges, the contra side entry for these components will always credit the trade receivables asset account.
• If they represent discounts, the contra side entry for these components will always debit the trade receivables asset account.

Relative to the contra side entry for a document component, the "first side" refers to the side of its mandatory and opposite entry. Each first side entry must affect a different account than its corresponding contra side entry. Therefore:

• If the corresponding first side entry is a credit then the contra side entry will always debit that different account.
• If the corresponding first side entry is a debit then the contra side entry will always credit that different account.

INVOICE_ADJUSTMENT records (tax) (settlements)

This is for sales taxes, other taxes such as duties or use taxes. Only applies when the settlement_value_fn (for SETTLEMENT_ITEM entries) or applied_value_fn (for SETTLEMENT_ITEM_APPLIED entries) are non-zero.

1. A separate Segment 1 must always be constructed from each INVOICE_ADJUSTMENT entry linked to an ORDER_ADJUSTMENT_TYPE having an order_adjustment_type_id that begins with 'TAX' (for sales taxes and other taxes such as duties or use taxes);
2. A separate Segment 4 must be constructed from each INVOICE_ADJUSTMENT entry linked to an ORDER_ADJUSTMENT_TYPE having an order_adjustment_type_id that begins with 'TAX' (for sales taxes and other taxes such as duties or use taxes), but ONLY if the value of the is_tax_due_on_accrual field in the AR_PARTY_RELATIONSHIP table is NO;

Note that for INVOICE_ADJUSTMENT records (tax) the procedure begins with Segment 5 construction (if required) because each Segment 5 eliminates the posting of values to the 'trade_receivables' and 'cash_in_bank' accounts for Segment 1 constructed from the same INVOICE_ADJUSTMENT record (tax). For this purpose it will be necessary to initialise the temporary variable is_segment_5_posted to FALSE for each INVOICE_ADJUSTMENT record (tax).

Note that in the following paragraphs the settlement_value_fn column is taken from the SETTLEMENT_ITEM table and the applied_value_fn column is taken from the SETTLEMENT_ITEM_APPLIED table when the INVOICE_ADJUSTMENT was found on that table.

To construct each Segment 1:

1. If the value of the realised_gain_loss field on the SETTLEMENT_ITEM table or the value of the realised_gain_loss field on the SETTLEMENT_ITEM_APPLIED table is unequal to zero then:
   1. Set usage to 'crystallised_gain_loss', and then determine the account for this usage and output as a contra-side entry with the realised_gain_loss value.
   2. Set usage to 'cash_in_bank', and then determine the account for this usage and output as a first-side entry with the realised_gain_loss value.
2. If the value of the reval_value field on the invoice component table is unequal to zero then:
   1. If the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry, otherwise divide applied_value_tx by the balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the INVOICE_ADJUSTMENT balance_tx that was cleared by this settlement.
   2. Multiply the reval_value field on the INVOICE_ADJUSTMENT entry by the proportion determined at Step 2a, above, and round the result to the nearest decimal point specified by scale on the CURRENCY_CODE entry linked to the currency_code_fn value on the related INVOICE_HEADER entry. This step determines the "reversal amount" which is the amount of accumulated revaluation gain or loss for the INVOICE_ADJUSTMENT that must be reversed because of the settlement.
   3. Subtract the reversal amount determined at Step 2b, above, from the reval_value field on the INVOICE_ADJUSTMENT entry, and re-save the INVOICE_ADJUSTMENT entry.
   4. Subtract the reversal amount determined at Step 2b, above, from the adjusted_reval_value field on the INVOICE_HEADER entry related to the INVOICE_ADJUSTMENT entry, and re-save the INVOICE_HEADER entry.
   5. Set usage to 'book_gain_loss', and then determine the account for this usage and output as a first-side entry with the reversal amount from step 2c.
   6. Set usage to 'trade_receivables', and then determine the account for this usage and output as a contra-side entry with the reversal amount.
3. Always:
   1. If the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry, otherwise divide applied_value_tx by the balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the INVOICE_ADJUSTMENT balance_tx that was cleared by this settlement.
   2. Determine the 'trade_receivables' account and output this as a contra side entry using balance_fn from either the SETTLEMENT_ITEM or SETTLEMENT_ITEM_APPLIED entry, depending on the table in which the INVOICE_ADJUSTMENT was found, multiplied by the proportion determined at Step 3a, above.
   3. Determine the 'cash_in_bank' account and output this as a first side entry having the same value as the contra side entry output at Step 3b, above.

To construct each Segment 4:

1. Determine the 'sales_tax_payable' account and output this as a contra side entry with values from the applied_value_fn column or settlement_value_fn column.
2. Determine the 'sales_tax_liability' account and output this as a first side entry with values from the applied_value_fn column or settlement_value_fn column.

INVOICE_ADJUSTMENT records (non-tax) (settlements)

This includes all INVOICE_ADJUSTMENT entries except those linked to an ORDER_ADJUSTMENT_TYPE entry having an order_adjustment_type_id that begins with 'TAX', and those linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission). It also excludes any entry where product_id_free is non-blank as that product will have its own entry in the INVOICE_ITEM table, and where the settlement_value_fn (for SETTLEMENT_ITEM entries) or applied_value_fn (for SETTLEMENT_ITEM_APPLIED entries) are zero.

For each INVOICE_ADJUSTMENT record meeting the criteria described in the preceding paragraph:

• If price_type = 'W' (Withholding Tax) then a Segment 5 must be constructed
• ELSE
  • If price_type = 'E' (Early Settlement Discount) then a Segment 2 must be constructed (see Note 15d) followed by a Segment 1
  • ELSE a Segment 1 must be constructed on its own.

Note that for INVOICE_ADJUSTMENT records (non-tax) the construction of a Segment 2 (if required) is performed before a Segment 1 because each Segment 2 eliminates the posting of values to the 'trade_receivables' and 'cash_in_bank' accounts for Segment 1 constructed from the same INVOICE_ADJUSTMENT record (non-tax). For this purpose it will be necessary to initialise the temporary variable is_segment_2_posted to FALSE for each INVOICE_ADJUSTMENT record (non-tax).

To construct each Segment 2:

1. Change the value of the is_segment_2_posted temporary variable to TRUE for the INVOICE_ADJUSTMENT.
2. Determine the 'trade_receivables' account and output this as a contra side entry with the value from either the applied_value_fn or settlement_value_fn column depending on whether the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM_APPLIED or SETTLEMENT_ITEM table.
3. Determine the 'debtor_fin_dsc_offered' account and output this as a first side entry with the value from either the applied_value_fn or settlement_value_fn column depending on whether the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM_APPLIED or SETTLEMENT_ITEM table..

To construct each Segment 1:

1. If the value of the realised_gain_loss field on the SETTLEMENT_ITEM or SETTLEMENT_ITEM_APPLIED table which is linked to the INVOICE_ADJUSTMENT is unequal to zero then:
   1. Set usage to 'crystallised_gain_loss', and then determine the account for this usage and output as a contra-side entry with the realised_gain_loss value.
   2. Set usage to 'cash_in_bank', and then determine the account for this usage and output as a first-side entry with the realised_gain_loss value.
2. If the value of the reval_value field on the invoice component table is unequal to zero then:
   1. If the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry; otherwise divide applied_value_tx by balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the INVOICE_ADJUSTMENT balance_tx that was cleared by this settlement.
   2. Multiply the reval_value field on the INVOICE_ADJUSTMENT entry by the proportion determined at Step 2a, above, and round the result to the nearest decimal point specified by scale on the CURRENCY_CODE entry linked to the currency_code_fn value on the related INVOICE_HEADER entry. This step determines the "reversal amount" which is the amount of accumulated revaluation gain or loss for the INVOICE_ADJUSTMENT that must be reversed because of the settlement.
   3. Subtract the reversal amount determined at Step 2b, above, from the reval_value field on the INVOICE_ADJUSTMENT entry, and re-save the INVOICE_ADJUSTMENT entry.
   4. Subtract the reversal amount determined at Step 2b, above, from the adjusted_reval_value field on the INVOICE_HEADER entry related to the INVOICE_ADJUSTMENT entry, and re-save the INVOICE_HEADER entry.
   5. Set usage to 'book_gain_loss', and then determine the account for this usage and output as a first-side entry with the reversal amount.
   6. Set usage to 'trade_receivables', and then determine the account for this usage and output as a contra-side entry with the reversal amount.
3. If the value of the is_segment_2_posted temporary variable is FALSE for the INVOICE_ADJUSTMENT then:
   1. If the INVOICE_ADJUSTMENT was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry, otherwise divide applied_value_tx by balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the INVOICE_ADJUSTMENT balance_tx that was cleared by this settlement.
   2. Determine the 'trade_receivables' account and output this as a contra side entry using balance_fn from either the SETTLEMENT_ITEM or SETTLEMENT_ITEM_APPLIED entry, depending on the table in which the INVOICE_ADJUSTMENT was found, multiplied by the proportion determined at Step 3a, above.
   3. Determine the 'cash_in_bank' account and output this as a first side entry having the same value as the contra side entry output at Step 3b, above.

To construct each Segment 5:

1. Determine the 'cash_in_bank' account and output this as a contra side entry with the value from the balance column.
2. Determine the 'tax_wh_prepaid' or 'tax_wh_final' account (refer Note 15c, above) and output this as a first side entry with the value from the balance column.

INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE records (settlements)

This section includes all INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE components which are attached to an entry on the SETTLEMENT_ITEM_APPLIED or SETTLEMENT_ITEM table, except those INVOICE_ITEM_ADJUSTMENT entries linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission).

To construct each Segment 1:

1. If the value of the realised_gain_loss field on either the SETTLEMENT_ITEM or the SETTLEMENT_ITEM_APPLIED table (depending on where the invoice component was linked) is unequal to zero then:
   1. Set usage to 'crystallised_gain_loss', and then determine the account for this usage and output as a contra-side entry with the realised_gain_loss value.
   2. Set usage to 'cash_in_bank', and then determine the account for this usage and output as a first-side entry with the realised_gain_loss value.
2. If the value of the reval_value field on the invoice component table is unequal to zero then: `
   1. If the invoice component was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry; otherwise divide applied_value_tx by balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the invoice component balance_tx that was cleared by this settlement.
   2. Multiply the reval_value field on the invoice component entry by the proportion determined at Step 2a, above, and round the result to the nearest decimal point specified by scale on the CURRENCY_CODE entry linked to the currency_code_fn value on the related INVOICE_HEADER entry. This step determines the "reversal amount" which is the amount of accumulated revaluation gain or loss for the invoice component that must be reversed because of the settlement.
   3. Subtract the reversal amount determined at Step 2b, above, from the reval_value field on the invoice component entry, and re-save that entry.
   4. Subtract the reversal amount determined at Step 2b, above, from the adjusted_reval_value field on the INVOICE_HEADER entry related to the invoice component, and re-save the INVOICE_HEADER entry.
   5. Set usage to 'book_gain_loss', and then determine the account for this usage and output as a first-side entry with the reversal amount.
   6. Set usage to 'trade_receivables', and then determine the account for this usage and output as a contra-side entry with the reversal amount.
3. If the invoice component was found on the SETTLEMENT_ITEM table then divide settlement_value_tx by balance_tx on the SETTLEMENT_ITEM entry; otherwise divide applied_value_tx by balance_tx on the SETTLEMENT_ITEM_APPLIED entry. This step determines the proportion of the invoice component balance_tx that was cleared by this settlement.
4. Determine the 'trade_receivables' account and output this as a contra side entry using balance_fn from either the SETTLEMENT_ITEM or SETTLEMENT_ITEM_APPLIED entry, depending on the table in which the invoice component was found, multiplied by the proportion determined at Step 3, above.
5. Determine the 'cash_in_bank' account and output this as a first side entry having the same value as the contra side entry output at Step 4, above.

PAYMENT_HEADER records (commissions and fees)

This includes all SETTLEMENT_HEADER entries having a non-zero commission_fee_fn value.

A Segment 6 must be constructed for any such entry:

1. Determine the 'commissions_fees' account and output this as a contra side entry with the commission_fee_fn value. If the commission_fee_fn value is greater than zero then the contra side entry must be a debit; otherwise it must be a credit.
2. Determine the 'cash_in_bank' account and output this as a first side entry with the commission_fee_fn value. If the commission_fee_fn value is greater than zero then the first side entry must be a credit; otherwise it must be a debit.

PAYMENT_HEADER records (unapplied cash)

This includes all SETTLEMENT_HEADER entries for which the total_settlement_value_fn value on the SETTLEMENT_HEADER entry is not equal to the sum of all settlement_value_fn values on the related SETTLEMENT_ITEM entries, less the sum of all applied_value_fn values on their related SETTLEMENT_ITEM_APPLIED entries.

A Segment 3 must be constructed for any such entry:

1. Calculate the difference between the total_settlement_value_fn value on the SETTLEMENT_HEADER entry, and the sum of all settlement_value_fn values on the related SETTLEMENT_ITEM entries less the sum of all applied_value_fn values on their related SETTLEMENT_ITEM_APPLIED entries. Store the difference in a temporary variable.
2. Determine the 'customer_deposit' account and output this as a contra side entry with values from the temporary variable. If the value of the temporary variable is greater than zero then the contra side entry must be a debit. The value of the temporary variable should never be less than zero but, if this should happen to be the case then the contra side entry must be a credit to keep the journal entry in balance.
3. Determine the 'cash_in_bank' account and output this as a first side entry with values from the temporary variable. If the value of the temporary variable is greater than zero then the first side entry must be a credit. The value of the temporary variable should never be less than zero but, if this should happen to be the case then the first side entry must be a debit to keep the journal entry in balance.

PAYMENT_HEADER records (sales commission)

Segment 7 transfers accrued sales commission liabilities to sales commissions payable, when commissionable customer invoices, credit notes and debit notes are settled (see Note 23, below). Determination of whether or not any sales commissions were earned is done at two levels:

• First, certain PRICE_COMPONENT entries linked to the INVOICE_ADJUSTMENT and/or INVOICE_ITEM_ADJUSTMENT components of an invoice may have the price_type value 'C' (Commission). If any such PRICE_COMPONENT entries exist then a Segment 7 must be constructed for each such INVOICE_ADJUSTMENT and INVOICE_ITEM_ADJUSTMENT, but only if the is_commission_accrued indicator on the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to is YES. The amount of commission earned is stored in the adjustment field of each such INVOICE_ADJUSTMENT and INVOICE_ITEM_ADJUSTMENT. Multiply adjustment by settlement_ratio (refer Calculating Foreign Exchange Gain or Loss at Settlement Time, above) to determine the sales_commission amount as a temporary variable.
• Alternatively, if the is_commission_accrued indicator on the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to is YES, but no Segment 7 journal entries were constructed for INVOICE_ADJUSTMENT or INVOICE_ITEM_ADJUSTMENT components as described in the preceding paragraph, then a Segment 7 must be constructed for the INVOICE_HEADER. In this case the amount of the temporary sales_commission variable is the total_settlement_value_fn value on the SETTLEMENT_HEADER entry, multiplied by the commission_percent value on the AR_PARTY_RELATIONSHIP entry for the party_id_bill_to. But if the commission_percent on that AR_PARTY_RELATIONSHIP entry is blank or zero, and the is_commission_accrued indicator on that AR_PARTY_RELATIONSHIP entry for the party_id_bill_to is YES, then the sales_commission amount is the total_settlement_value_fn value on the SETTLEMENT_HEADER entry, multiplied by the commission_percent value on the FUNCTIONAL_UNIT entry for the party_id_bill_from.

A Segment 7 must be constructed for any such entry:

1. Determine the 'commission_payable' account and output this as a contra side entry with the sales_commission value. If the sales_commission value is greater than zero then the contra side entry must be a debit; otherwise it must be a credit.
2. Determine the 'commission_liability' account and output this as a first side entry with the sales_commission value. If the sales_commission value is greater than zero then the first side entry must be a credit; otherwise it must be a debit.

Accounts Receivable Control Data

This information exists within a shared table in the MENU database and not a separate table within the FINANCE_AR database. It holds values which can be varied to suit that particular installation. The current values are as follows:

Date created: 12th December 2018

Amendment history:

Copyright © 1999-2024 by Geoprise Technologies Licensing, All Rights Reserved.