Accounts Receivable > Customer Query

Customer Query

You use this program to view data relating to your customers in the Accounts Receivable module.

You typically use this program to verify customer details and to view details of customer invoices and payments. This could be driven internally from your credit control department, or externally in response to a customer request.

This program can be run at any time.

There are no dependencies between this program and any other program.

AR Customer Query

Field Description
Options  
Preferences Configure options that will determine the level of detail you want to view within the query (see Preferences).
Options Define the month for which you want to view balances and the ageing date for balances (see Options).
Show Profits

View the Profit summary and Profit percentage values in the Customer History section for the selected customer.

[Note]

To enable this option, you must define a password against the option: A/R Viewing of customer profits in customer query (Password Definition).

Query  
Post Dated Checks View details of post dated checks for the customer (see AR Post Dated Checks).
Multiple Address Lines View multiple ship to addresses for the customer (see Browse on Multiple Ship to Addresses).
Subaccounts View sub accounts attached to the customer (see Browse on Master Sub-accounts).
Customer Contracts View the contract pricing details held against the customer (see AR Browse on Customer Contracts).
Buying Group Contracts

View the buying group information defined for the customer (see AR Contract Pricing for Buying Groups).

Buying groups are defined using the Browse on Customers program.

Invoices & Payments Use the Invoices for Customer program to view customer invoices and their corresponding payments.
Show Statements

View the customer's statement in a browser window. You can typically use your right mouse button within the browser window to view additional options available. For example, you can print the statement by selecting the Print option.

You can customize the design used to render this information using the Browser Queries and Themes program.

[Note]

Zero balance invoices are only displayed if you selected the Preference: Zero balance invoices (AR Statement Format - Print Options).

Contact Use the Contact Management Query program to view information on contacts for the customer.
Customer Enter the code of the customer whose details you want to query.
Find Use the Generic Find program to locate items according to extensive search criteria.
Play View any multimedia objects that have been assigned to the customer (see Multimedia).
Edit Maintain multimedia objects for the customer (see Multimedia).
Notes The first 10 lines of Customer Notes and Management Notes are displayed in this section. You can view and edit the notes by typically using your right mouse button to select the first line of text.
[Note]
  • You will only be able to maintain Customer notes and Management notes if you have been granted access to the activity: A/R Update/Add/Remove Customer/Management Notes (Operator Maintenance - Security tab).
  • An error message is displayed if you attempt to maintain notes when these are currently being maintained by another operator.
Management Notes Use the Text Editor program to view and maintain credit management notes for the customer.

These notes can be printed on the Credit Management report. You can also use the follow up date to indicate the date according to which credit narrations must be printed on the report.

Customer Notes Use the Text Editor program to view and maintain notes for the customer.
Additional Use the Notepad Editor program to capture additional free format notes against the customer.
Contacts Use the Contact Management Query program to view information on contacts for the customer.

Preferences

You use the Preferences option from the Options menu to define the level of detail you want to view within the query.

Field Description
Invoices & Payments  
Invoice ageing selection Refer to Invoice Ageing for information on how the aged balances for a customer are determined.
No ageing Select this if you do not want to display invoices in the Invoices listview according to their age.
Aged buckets Display invoices according to the ageing buckets defined for the customer. The ageing buckets are displayed as they are held on file (i.e. no re-ageing is performed).
[Note]

This will display values only for those customers with a balance print type of balance forward. Ageing buckets are not maintained for customers with a balance print type of open item.

Aged invoices Re-age invoices according to the current system date.
Include retained invoices Include invoices with a zero balance that you are retaining for a prescribed period of time.
Invoice display  
Include retained invoices Include invoices with a zero balance that you are retaining for a prescribed period of time.
[Note]

You can only select this option if you are retaining zero balance invoices for more than one month (Accounts Receivable Setup).

Zero balance current invoices Include zero balance invoices that became zero in the current month.
Ascending sequence Display the invoices in the Invoice table/file from the first invoice number in the table/file. This applies to invoices viewed within the listview pane of the Invoices tab.
Descending sequence Display the invoices in the Invoice table/file from the last invoice number in the table/file. This applies to invoices viewed within the listview pane of the Invoices tab.
View invoices and payments in  
Detail View invoice and payment details together with any transactions processed against the invoice.
Summary View invoice and payment details only.
Payments display  
Earliest payments first View payment details from the oldest payments held on file. This applies to payments viewed within the listview pane of the Payments tab.
Latest payments first View payment details from the most recent payments held on file. This applies to payments viewed within the listview pane of the Payments tab.
Invoice ageing method

This defaults to the ageing method defined against the Invoice ageing method setup option (Accounts Receivable Setup - General tab), but can be changed for the query. This does not affect the ageing method defined in Accounts Receivable Setup.

[Note]

Your invoice ageing method selection is only applicable for the current run of the program. It is not saved by using the Save Preferences function.

Refer to Invoice Ageing for information on how the aged balances for a customer are determined.
By statement Age invoices according to the last seven period end dates. An invoice is defined as current if the invoice date is after the last period end date.
By aged statement Age invoices according to the last seven period end dates. An invoice is defined as current if the invoice date is after the second last period end date.
By invoice date Age invoices according to the difference between the invoice date and the system run date.
By invoice due date Age invoices according to the due date of the invoice. This is calculated as the invoice date plus the days till due as defined within the Browse on AR Invoice Terms program. The invoice remains current for as long as the due date remains greater than the ageing date.
Sales Orders & Movements  
Movements display These options apply to the Movements listview only.
Earliest movements first View sales movements from the oldest movements held on file. This applies to movements viewed within the listview pane of the Movements tab.
Latest movements first View sales movements from the most recent movements held on file. This applies to movements viewed within the listview pane of the Movements tab.
Sales orders This applies to the Sales Orders listview only.
Show active orders only Exclude completed orders (i.e. orders in a status of 9) from being displayed in the listview pane of the Sales Orders tab.
General  
Foreign customer values in  
Local currency View the transaction values for foreign currency customers in the local currency.
Foreign currency View the transaction values for foreign currency customers in the foreign currency.
Convert to local value at This option is only enabled if you selected to view foreign customer values in the local currency.
Entry-time rate Convert the foreign currency transaction values into the local currency at the exchange rate at which the transaction was processed.
Current rate Convert the foreign currency transaction values into the local currency at the exchange rate currently defined for the foreign currency (see Browse on Currencies).
Save Preferences Store your selections. If you do not save your preferences then they are applied only for the current run of the program.

Options

This screen is displayed when you select Options from the Options menu.

Field Description
Balances as of Indicate whether you want to view balances as at the current month, previous month 1, or the previous month 2.
Current month View balances as of the current month.
Previous month 1 View balances as of the previous month.
Previous month 2 View balances as of the month before last.
Ageing date If you selected to view balances as of the Previous month 1 or Previous month 2 then you use this field to indicate the date according to which you want to age invoices.

Customer Details

The details about the customer are displayed in these fields.

Some of the details that can be viewed include:

Field Description
Customer information  
Credit status

The message "No Credit Allowed" is displayed in this field if the Credit status code = 9. In all other cases, this field is blank.

Customer credit statuses are reset when:

  • you run the Balance function of the AR Period End program and select the option: Reset customer credit status.

    If this is not done immediately after customer payments are processed, then it is possible that incorrect credit statuses could be displayed in the Customer Query program. For this reason, the credit status is only displayed if it is in a status of 9.

  • you use the AR Invoice Posting program to process invoices against customers and you selected the option: Reset credit status after posting.

Refer to Credit Status for information on how a customer's credit status is determined.

Credit status code

The following codes can be displayed in this field:

  • 0 - Current or zero balance
  • 1 - 30 day balance
  • 2 - 60 day balance
  • 3 - 90 day balance
  • 4 - 120 day balance
  • 5 - 150 day balance
  • 6 - 180 day balance
  • 9 - manual hold

    This indicates that the customer cannot receive further credit.

Master/subaccount This indicates whether the customer is a master or a sub account. This is blank if the customer is neither a master nor a sub account.
Over limit

This is calculated and displayed irrespective of whether credit checking is required for customers (Sales Order Setup).

The Over limit value is calculated as follows:

  • If you selected the setup option: Apply check to current orders only (Sales Order Setup), then:

    Over Limit = Current customer balance + Order value released - Credit limit

  • If you did NOT select the setup option: Apply check to current orders only (Sales Order Setup), then:

    Over Limit = Current customer balance + Order value - Credit limit

  • If you did NOT select the setup option: Include tax in customer invoiced value (Sales Order Setup), then:

    Over Limit = (Current customer balance - tax value included in balance) + Order value released - Credit limit

    if the option Apply check to current orders only (Sales Order Setup) is selected.

    OR

    Over Limit = (Current customer balance - tax value included in balance) + Order value - Credit limit

    if the option Apply check to current orders only (Sales Order Setup) is NOT selected.

[Note]

When USA tax is in force (Tax Options), tax is always excluded from the credit checking calculations (Sales Order Setup - Terms/Margins tab). USA tax is always exclusive, so the order value is always exclusive, whereas with other tax methods the order value may or may not include tax.

Credit checking This indicates the credit checking method defined against the customer (AR Customer Maintenance).
Order value

This indicates the value of orders and dispatch notes not yet invoiced.

This amount is amended by the following programs:

Option Description
Order Purge If a value of an orders changes during the balance/purge functions, the difference is added or subtracted.
Sales Order Import

The full value of the order is added.

In the case of a cancelled order, the value of the order is subtracted.

Browse on Master Sub-accounts When you attach or detach a sub account from a master account and the invoices are attached to the master account, the accumulated outstanding value of orders is moved between the master and sub account.
Purchase Order Receipts, Job Receipts and Shipment Receipt If the value of the order value has changed, the Order value is updated by the changed value.
Sales Order Entry

When an order is maintained the original value of the order is subtracted and the new value of the order is added.

The value of forward orders is added.

When an order is cancelled the value of the order is subtracted.

When an existing order is released, the total value of the order is subtracted.

Document Print

The total value of the order including tax, freight, etc., is subtracted.

Discount values are added back.

Point of Sale Entry The outstanding net value is added.
Quick Entry The value of forward orders is added.
Browse on Customers When creating new customers, the Order value field is set to zero.
Quotation Confirmation The value of the quote is added.
Blanket Sales Order Releases and Review The beginning order value is subtracted and the new order value added.
Dispatch Note Review If the value of the order value has changed, the Order value is updated by the changed value.
Part Billings The value if the order is added.
Order value released

This is the total value of all shipped order lines.

The Order value released is only displayed if Credit checking is required and the option: Apply check to current orders only is selected (Sales Order Setup - Terms/Margins tab).

If the option: Apply check when quantity moved to ship (Sales Order Setup - Terms/Margins tab) is selected, then the order does not have to be any specific status for it to be included in this total. If you are using dispatch notes, then the order value released includes the value of any dispatches that have been dispatched but not invoiced. With this option selected, ship values are added as soon as an item is moved to ship on an order or an item is dispatched.

If the option Apply check when quantity moved to ship (Sales Order Setup - Terms/Margins tab) is not selected, then ship values from orders are only added when orders are in a status of 8 and when dispatch notes are in a status of 7.

When an order is invoiced, the Order value released is reduced.

The Order value released is recalculated when you run the Balance function of the Order Purge program and you select the option: Validate inventory quantity allocations.

This value includes tax if credit checking is set to include tax (Sales Order Setup - Terms/Margins tab), except when USA tax is in force. USA tax is always excluded from the credit checking calculations (see Sales Order Setup - Terms/Margins tab).

No of orders

This indicates the number of active sales orders together with the number of completed orders held against the customer since the last sales order purge was run (see Order Purge).

Completed orders (i.e. orders in a status of 9) are excluded from the number of orders displayed only after you have run the Order Purge program.

The following programs increment the number of orders displayed at this field:

  • Order Entry (Sales Orders)
  • Point of Sales (Sales Orders)
  • Quick Entry (Sales Orders)
  • Consolidate Orders (Sales Orders)
  • Sales Order Import (Business-to-Business Trading)
  • Quotation Confirmation (Quotations)

Last payment This indicates the date on which the last payment was processed against the customer.
Last sale This indicates the date on which the last sale was processed against the customer.
Average no of days to pay

This field is recalculated each time you use the Payments and Adjustments program to process a payment for the customer or you run the Balance function of the AR Period End program.

The formula for Average number of days to pay is NumZeroDays / NumZeroInvoices. These fields are in the ArCustomerBal table.

For each invoice or debit note paid, two totals are updated:

  • A - the total number of invoices paid
  • B - the total number of days to pay.

When a payment is processed:

  • A is increased by 1 for each invoice/debit note paid
  • B is increased by the number of days taken to pay (the number of days between the invoice date and the payment date).

The average number of days to pay is calculated as:

  • Total number of days to pay / total number of invoices (i.e. B divided by A).

For the Average no of days to pay to be correctly calculated, you need to ensure that the option: Average number of days to pay calculation - Number of months to use (Accounts Receivable Setup - History tab) is defined as required. The number of months determines the cut-off point for including paid invoices (i.e. zero balance invoices) in the average number of days to pay calculation.

Ageing - invoice value

You can view the invoices that make up these balances in the Ageing column of the Invoices listview.

Balance

This indicates the customer's current balance. This is calculated by the Customer Query program at the time of performing the query. Therefore, no other programs need to be run to update the balance prior to performing the query.

Refer to Invoice Ageing for information on how the aged balances for a customer are determined.

Ageing - no of invoices

This indicates the number of invoices that make up the balance for the corresponding periods in the Ageing - invoice value fields.

The number of invoices for each period is determined by checking if the invoice balance is zero. If it is then the program determines the period in which the balance became zero. However, if the invoice became zero in the current period, then it is still included in the current period until you run the Month end function of the AR Period End program.

Master account details  
Highest balance attained

This indicates the highest balance ever owed by the customer. This balance is not set to zero at month end or year end and is purely for information purposes.

This field can be updated by the following programs:

The Highest balance attained field can also be updated manually using the AR Customer Maintenance program.

Contact Details

These are some of the contact details that can be viewed from this pane:

Field Description
Sold to address
Option Description
Bill-to extended tax code

This indicates the nine-character tax geo code to use for the sold to address (see Browse on Extended Tax Codes).

This is only indicated if you are using USA tax by advanced geocodes or the USA AVP sales tax system (Tax Options).

Bill rate

This indicates the tax rate to use as defined against the extended tax code (see Browse on Extended Tax Codes).

This is only indicated if you are using USA tax by advanced geocodes or the USA AVP sales tax system (Tax Options).

Ship to address
Option Description
Ship-to extended tax code

This indicates the nine-character tax geo code to use for the sold to address (see Browse on Extended Tax Codes).

This is only indicated if you are using USA tax by advanced geocodes or the USA AVP sales tax system (Tax Options).

Ship rate

This indicates the tax rate to use as defined against the extended tax code (see Browse on Extended Tax Codes).

This is only indicated if you are using USA tax by advanced geocodes or the USA AVP sales tax system (Tax Options).

Notes

Details that can be viewed include the following:

Customer History

Field Description
Prev 24 months sales

Previous 1 - Previous 24
[Note]

Miscellaneous entries from Sales Orders are excluded from the values displayed. However, invoices created using the AP Invoice Posting program are included, because they are not treated as miscellaneous entries even though they are non-merchandise entries.

MTD summary by value
[Note]

Miscellaneous sales values are included in the values displayed.

Profit summary

Profit summary and Profit percentage information can only be viewed if a password is defined against the option: A/R Viewing of customer profits in customer query (Password Definition).

When you select the Show Profits option, you are requested to enter the appropriate password before the profit information is displayed.

Profit percentage

Profit percentages are calculated from the A/R customer Master Balances file.

Option Description
Profit % this month Calculated as: Current month's Profit divided by Current month's Sales multiplied by 100)
Profit % this year Calculated as: Current year's Profit to date divided by Current year's Sales to date multiplied by 100)
Profit % previous year Calculated as: Previous year's Profit divided by Previous year's Sales multiplied by 100)

Extra Customer Details

Details that can be viewed include the following:

Custom Form

This pane displays custom form information defined against the customer (see Custom Form Entry).

Field Description
Custom Form Data Entry Select this to use the Custom Form Data Entry program to enter custom form data against the supplier.

Invoice Ageing Graph

This displays a graphical representation of the value of invoices for the customer aged according to the ageing buckets that are defined.

Refer to Invoice Ageing for information on how the aged balances for a customer are determined.

Sales History Graph

This displays a graphical representation of the value of sales processed for this customer over the past 24 months.

Additional Notes

Select this to view or add free format notes against the customer.

Invoices

This displays details of invoices, debit notes and credit notes raised for the customer.

Some of the details that can be viewed include:

Field Description
Type

This indicates the document type.

  • INV indicates an Invoice

  • C/N indicates a Credit Note not linked to an existing invoice (i.e. when using the AR Invoice Posting program to generate the credit note, the option: Post to existing invoice was NOT selected).

  • D/N indicates a Debit Note not linked to an existing invoice (i.e. when using the AR Invoice Posting program to generate the debit note, the option: Post to existing invoice was NOT selected).

Terms

For invoices created using the AR Finance Charge Calculation program, the default terms are the terms defined against the setup option: Finance charges - Terms (Accounts Receivable Setup - General tab).

Sales order

This indicates the sales order from which the invoice was created.

If there is no sales order number, then this indicates that the invoice was created using the AR Invoice Posting program.

POD

This indicates whether proof of delivery information exists for the invoice.

[Note]

If you use the smart link to access the Proof of Delivery program to update proof of delivery information, then although the information is updated against the invoice, the information is only displayed when you reload the query for the customer (i.e. it is not immediately displayed).

If however, you typically use your right mouse button to access the Proof of Delivery program to update proof of delivery information, then the updated information is immediately displayed in the listview.

POD reference

This indicates the reference defined against the proof of delivery (see Proof of Delivery).

Ageing

This column displays the age of the invoice, unless you selected the Preference: No ageing.

You can sort invoices by age by using your mouse pointer to select the column heading. This enables you to view the invoices which make up the ageing balances in the Ageing - invoice value section in the Customer Information pane.

Refer to Invoice Ageing for information on how the aged balances for a customer are determined.

You can highlight a line in this listview and typically use your right mouse button to select one of the following:

  • Order Details (see Detail Lines for Sales Order)

  • Proof of Delivery (see Proof of Delivery)

  • Exchange Rates. This indicates the foreign to local conversion rate for the transaction. This option is only enabled for foreign currency customers.

  • Revaluation Details (see AR Revaluation Details). This option is only enabled for foreign currency customers.

Invoice Payments pane

Field Description
Type

The type 'Rev' indicates that the AR Exchange Rate Revaluation program was used to revalue the transaction. This only applies to foreign currency customers.

'Cr' indicates a Credit note posted to the invoice (i.e. when using the AR Invoice Posting program to generate the credit note, the option: Post to existing invoice was selected).

'Dr' indicates a Debit note posted to the invoice (i.e. when using the AR Invoice Posting program to generate the debit note, the option: Post to existing invoice was selected).

Date

This indicates the date of payment.

Pmt jnl

This indicates the payment journal created for the payment transaction.

Reference

A reference of 'Reval' indicates that the AR Exchange Rate Revaluation program was used to revalue the transaction. This only applies to foreign currency customers.

Settlement discount note

This column displays the discount credit or debit note number if you selected to print discount credit and debit notes (Accounts Receivable Setup - Terms Discount tab).

Jnl notation

This indicates the Accounts Payable journal number for contra entries processed using the Customer - Supplier Contra program.

Movements

[Note]

Movements are only displayed if the option Retain detail movements (AR Customer Maintenance) is enabled against the customer.

Field Description
Stock description

By default, this displays the stock description defined against the sales order.

If the sales order has been purged, then for a stocked item, this displays the description defined against the stock code (Stock Code Maintenance).

If you processed a Billing for a stocked item using the Sales Order Entry program and you changed the description for the item, then the description you defined is displayed, unless the new description is spaces, in which case the description defined against the stock code (Stock Code Maintenance) is displayed.

Long stock description

This displays the stock description defined against the stock item (Stock Code Maintenance).

Unit price

This is the result of a calculation based on the line quantity and line sales amount, so both order and line discount have been taken into account.

If you want to see the contract price for a scheduled order, then you have to look at the sales order or the invoice.

Payments

Payment information is only displayed in this listview if you selected the setup option: Retain payments details (Accounts Receivable Setup - History tab).

This information is based on records in the ArPayHistory table and only reflects the actual payment amounts received from the customer. It does not reflect any discounts applied to the payment.

The payment details for the currently highlighted line are displayed in the Payment Details pane.

Payment detail pane

This pane displays the payment details for the line currently highlighted in the Payments listview.

This information is based on records in the ArInvoicePay table and reflects invoice payments and adjustments and therefore also includes any discounts applied.

Field Description
Entry number

This indicates the Journal entry number, which enables you to differentiate between payment detail entries when two payments are made against one invoice on one journal number.

Sales Orders

You use the Sales Orders tab to view all sales orders for the customer.

Some of the listview columns include:

Field Description
Doc type This is the document type processed using the Sales Order Entry program.

Cr note indicates a Credit note, while Dr note indicates a Debit note.

Ship date This indicates the ship date for the line.

None indicates that it is a scheduled order.

Scheduled Yes indicates that the order is a scheduled order. In this case, the Ship date is displayed as None.

Sales Order Details pane

This pane displays sales order details for the sales order currently highlighted in the Sales Orders listview.

Work in Progress

This displays details of work in progress outstanding for the customer (i.e. details of jobs linked to outstanding sales orders for the customer).

Work in Progress

This listview displays information on jobs linked to sales orders for the customer.

WIP Details pane

This pane displays job details for the job currently highlighted in the Work in Progress listview.

Some of the details that can be viewed include:

Field Description
Dates

If you are using the Factory Scheduling module and you selected the option: Update planned dates with scheduled dates (Factory Scheduling Setup), then planned dates against a job are updated with the scheduled dates from the Factory Scheduling system.

RMA

This displays details of return merchandise advices processed for the customer.

Quotations

This displays details of quotations processed for the customer.

Inserting Application Help

You would typically follow this procedure to display help for the current program in a customized pane that can be pinned to the program window.

Information includes step-by-step instructions for the various functions available within the program, including a brief overview of what the program does, what setup options are required and how to personalize the program.

  1. Open the program for which you want to insert application help into a customized pane.

    This functionality is only available for a program that has panes.

  2. Right-click any form field.

    You can also click the triangle menu icon that appears in the title area of a pane.

  3. Select Insert Application Help from the context-sensitive menu.

    The application help appears in a pane within your program. You can reposition the pane using the docking stickers or pin it to the program window.

Removing the Application Help pane

If you no longer want to display application help in a pane for your current program, you can simply remove it.

  1. Select the Close icon in the right-hand corner of the application help pane.

  2. Confirm that you want to delete the pane.