Cash Register

 

 Overview

This feature set is designed to address specific retail sales requirements for the Highlander Project. While many sales requirements could be handled through the existing Sales Order cycle in xTupleERP, that module generally involves many more steps and transactions than are necessary to support a point of sale environment. The Cash Register will enable greatly simplified transactions sets, plus it will also support specific Highlander requirements to handle opening and closing of a cash register that would be extremely awkward in the Sales Order paradigm.

The cash register functionality is designed to be general purpose and support any point of sale environment and is not specifically designed at this time to handle Highlander cross system integration requirements as those are intended to be handled by a Service Bus. It should, however, be more conducive to other Point of Sale requirements needed for the Highlander project such as the processing of Check payments, Gift Certificates, Vendor Commissions and This iteration is not intended to provide all functionality required for retail sales management in the Highlander Project, but rather enough to be able accomplish basic cash register management and sales processing.

Functional Requirements

The Cash Register interface will:

Windows should be designed to be loaded via the Screen Design Interface in xTupleERP 3.0 with all screen logic provided by scripts. New database functionality specific to this feature set should be created in a schema called "retail." API views should be created that bridge functionality of the public and retail schemas. Screens developed for this functionality should use the API schemas.

New Terms and Definitions

Related Existing Functionality

Cash Register is similar to functionality in the Sales module, but is targeted toward retail applications where the sale is closed and funds are tendered at the point of sale. Existing Customer, CRM Contact, Price Schedule, Tax, credit card processing, inventory and sales item characteristic logic will apply to the cash register.

Conflicting Features

There is a requirement that multiple sales reps may be associated with a sale for splitting commissions. Recording this in the sales history (cohist) table is problematic because cohist currently allows only one sales rep per record. The commissions portion of the cohist table would have to be separated into another table to complete that functionality.

User-Level Functionality

The basic process flow for cash register involves opening the register, processing sales and closing the register, with the option of making adjustments to the till between sales. The wire diagram below is not meant to cover detailed use cases, but simply to convey the basic concept of the daily cash register work cycle.

cashRegisterFlow.png

Window Changes

As a general rule windows for retail sales windows should be sized 600 x 400 pixels so they may fit in a variety of computing environments including small LCD screens. All mock up UI files pictured in this specification are attached to this page.

Retail Site Maintenance

The retail sales sub system must support records for cash register sessions associated with sales at a specific retail site. The sites, therefore, need to be mapped to G/L accounts to track the cash balances when registers are opened and closed. Retail Site Maintenance is used to maintain these mappings.

Retail Site List

retailsites1.png

The retail list window should appear in Sales > Master Information on the bottom of the sub menu under a new separator.

The retail site list is a main window that will display a list of (warehouse) sites that support cash register sales. Users may print the list or select a site to Edit or View. Users may click New to add a new site or Delete to remove an existing one. Only one entry per site is allowed.

Retail Site

retailsite1.png

Adding, editing or viewing a Retail Site in the Retail Site List will open the dialog window pictured above. The site is a required selection that identifies a site (a.k.a "warehouse") which supports cash register sales. Quote Expiration Days determines how long a quote is valid to be converted to a sale at that site; the minimum value is 1 day. The Asset account is a General Ledger Asset account mapping that identifies the account to be used to hold the value of cash in the register. The Adjustment account is the General Ledger Expense account mapping that is used when there is a discrepancy recorded between the amount of cash the system calculates is in a register, and the amount actually counted at the opening or closing of the register. Both G/L Account mappings are required. Bank Accounts are a listing of allowed bank accounts from which funds may be transferred to or from the register at opening and closing. At least one Bank Account is required.

Open Register

In order to process a retail sale a cash register must first be opened which involves associating a terminal as a register and setting or confirming the register's initial balance. Therefore, a register must be opened on the machine that will be conducting sales transactions. The local MAC address of the machine will be identified using the QNetworkInterface class and will be recorded when the register is opened. Details on how to obtain the local machine's MAC address may be foundhere.

To open a register, the user will navigate to Sales > Retail Sales > Open Register. The Retail Sales menu option will be the topmost menu item under Sales. This menu option should be disabled if the terminal is already open as a register.

openregister1.png

The site combo box will allow a user to select the site at which the register exists. Only sites mapped as retail sites should be listed. The Printer selected will direct where receipts are printed. To reduce selection error, the site should default to the user's preferred site (warehouse) setting and printer selected for opening should be saved on the local machine and be retrieved as the default when a register is re-opened; both are required fields. The previous balance will display the balance of cash in the register the last time it was closed, if applicable. When checked the group labeled "Transfer from bank" will allow a user to specify an amount of cash to be deposited in the register from a selected bank account at time of opening. The user will select from a list of bank accounts associated with the register. If there is a discrepancy between the previous balance and the amount found in the register at the time of opening, this can be recorded by checking the "Adjustment" check box and recording the adjustment amount. The opening balance is a read-only calculated field that sums the previous balance, bank transfer and adjustment. Notes can be optionally entered, but are required if Adjustment is checked.

When the user clicks "OK" this will open the register to process sales on the local machine. The user should not be allowed to click "OK" if the opening balance is negative. If "Print Receipt" is checked, a transaction report will print when the user clicks "OK."

Close Register

Closing a register posts the sales for the register to the General Ledger and sales history table, and allows the user an opportunity to reconcile the cash balance in the register including the recording of bank transfers and adjustments.

A user can close a register by navigating to Sales > Retail Sales > Close Register.

closeregister1.png

The user can select from a drop down list of any open terminal registers. By default this window will be presented with the current register selected if it is open. It is necessary to allow the closing of one register terminal from another terminal in case hardware failure of the opening terminal makes closing from the original machine impossible. However, if the user selects a register that is not the current machine, they should be presented with a warning telling them they will be closing a register on another machine. They should not be allowed to do this if the other machine is in the midst of a sale.

The Opened field displays the date and time the selected register was opened. Opening balance displays the balance of cash in the register when it was opened. Current Balance displays the balance of cash and checks that are calculated to be in the register. "Total Sales" are the total sales that were made on the register since it was opened. Cash, Credit Card and Check sales are the total cash sales of each of those respective payment types made on the register since it was opened.

"Transfer to Bank" must be checked and a user user must specify a Bank Account and enter an amount to transfer to the bank, even if it is zero. Deposit Checks will be checked and disabled so a user may not uncheck this option (not pictured). If Adjustment is checked a user can enter a discrepancy amount between what was expected to be in the register and what was actually found. The closing balance displays the sum of the current balance, transfers and adjustments. Notes can be added, and are required if an adjustment has been made. Clicking "OK" closes the register, but a user may not close the register if the closing balance is less than zero.

Note the user has a sticky "Print Receipt" check box option that allows them to print the transaction when they click "OK." Printed Receipts should include all information on the screen, plus a list and subtotal of deposited checks if applicable, and the total deposited amount of cash and checks.

Adjust Register

Adjust Register should appear in the menu beneath Open Register.

adjustregister1.png

Adjusting the register allows the user to record the addition or removal of cash from the till, and/or checks deposited to the bank mid-day. An adjustment works similarly to closing the register, except an adjustment only posts the change in cash and has no affect on revenue accumulation or posting to the G/L. Also note the user may enter a negative cash amount if they are replenishing the till. However, the new balance must not be negative. Deposit checks is unchecked and disabled as an option if the register is not open.

Cash Register Windows

A new menu item will be created as Sales > Retail Sales > New. This menu path will only be enabled when a register has been opened on the local machine. When the Cash Register window is open a lock should be put on the register, similar to the locks that can be put on sales orders, to prevent the register from being closed when it is in the middle of a sale. Since it is possible to close a register from another machine, the Cash Register window should check to make sure it is actually open when it is instantiated.

The Point of Sale window is a dialog window that will have basic header and footer information as follows:

  • Header
    • Clear button: Begins a new sale. If an existing sale was started and still open the window will ask if the user wants to save for completion later.

    • Number: An auto generated sales order number. Use the existing xTuple Sales Order number for this.

    • Type: Options are hard coded "Sale" or "Quote." Payment button is disabled when type is quote. Quotes expire after the number of days specified in retail site configuration. If the type has expired, the Quote opens in view only mode with a status "Expired."

    • Status: An informational field that reads "Open" or "Closed" for sales, and Open or Expired for Quotes. Sale is closed when payment has been tendered.

    • Scan Button: A "Checkable" button used to put the retail sale window in a mode to accept UPC or item number bar code scans. All text edit widgets will be disabled in scan mode.

    • Save button: Saves the sale or quote to be processed at a later time. If a quote the user should be offered the option to print the quote when saving.

  • Footer
    • Subtotal: The total sale value of all the items listed on the order.

    • Tax: The total taxable amount for the sale.

    • Total: The subtotal plus tax.

    • Payment button: Opens a payment tendering window which will close the sale. Disabled for quotes and closed sales.

  • Tabs - Customer, Items and Detail described as follows:

Customer Tab

cashregister1.png

The customer tab is where customer information can be searched and selected. The customer is optional. The "New" button should allow a user with appropriate privileges to open the Customer window and create a new customer record. When a customer is selected, the "New" button should change to "Edit" which would launch the customer record for editing. Contact information changed in the Point of Sale window will update the contact information stored on the contact's record, as well as the sale itself.

Items Tab - Item

cashregister2.png

The items tab will display a list of items on the order with a quantity not equal to zero. Columns include the line number, item number, UPC code, description, quantity, unit price and extended price. Any line selected will cause cause editable detail of the selected line to populate beneath the list. The editable detail will include the item, quantity, and unit price, with the extended price displayed as a read-only calculation. The window must be out of scan mode to be able to manually edit line data. Edits are committed when a new line is added, another line is selected, the sale is saved, or payment is clicked.

Serial-tracked items should be prompted for distribution at the time the quantity is committed. A line can be canceled by simply changing the quantity to zero.

Item tab - Characteristics

cashregister3.png

Characteristics for the selected line will display on a separate tab. They should operate similarly to sales order item, including their impact on configurable pricing. One difference, however, is that the characteristic description should be visible on the characteristic list.

Sale Detail

cashregister4.png

The detail tab will display read-only information concerning the date of the order, the retail site, the terminal the sale is made on, and the tax authority information copied in from the site (warehouse) record.

By default, the sales rep associated with the currently logged in user/employee will be added to a list of sales reps. associated with the sale. The one-to-many structure will allow for split commissioning scenarios required in AdvancedSalesCommissions. However, for this development pass the Add and Delete buttons should be hidden (not pictured), due to current limitations of commissioning described elsewhere in this document.

Users can add unlimited notes on the detail tab.

Cash Register Payment Window

retailsalepayment1.png

When the sales rep. clicks the payment button, they will be prompted with a new dialog window showing the same sub totals as before, plus a selection for payment type of cash, credit card or check. If cash is selected, the sales clerk simply keys in the amount paid, and the change due is totaled. When the change has been disbursed, the clerk clicks "OK" and the sale is closed.

If credit card is selected, the credit card information group is enabled while the "OK" and amount paid controls are disabled. The Amount paid is populated with the amount due. The "OK" button is enabled when all required credit card information has been entered. When the user clicks "OK", the credit card will be processed and the sale closed. If the credit card is rejected, an appropriate error will be displayed and the user will be returned to the payment window.

cashregisterpayment2.png

If check is selected, the amount paid control is disabled and populated with the due amount. A stacked widget flips from credit card information to a check number that must be entered before the user can click "OK". When the user clicks "OK" the sale is closed.

If the "Print Receipt" check box is checked, a receipt will print when the clerk clicks "OK". The user should not be prompted for printer selection, the receipt should simply print to the printer selected when the register was opened.

When a sale is closed, control should be returned back to the retail sales window which is automatically cleared for the next sale.

Pending Sales

Since it is possible to save a quote or sale for later processing, a list window needs to be made available to show quotes and sales that are outstanding. This window will be available under Sales > Retail Sales > Pending Sales.

pendingsales1.png

This window will allow users to view pending sales from any site (warehouse) or all sites depending on their privileges. A "show expired" sticky check box will be available to determine whether expired quotes are visible. From here users can create a new sale (if the terminal is an open register), view and delete pending quotes and sales. Users should not be able, however, to edit a sale started on another terminal or delete a sale currently opened for editing on another register. Users should also be able to print the selected quote or sale.

Report Changes

Reports will be created to print:

  • Retail Sites
  • Receipt for register open, adjustment or close transactions
  • Quote/Sale receipt form from the Pending Sales Window or Cash Register

In addition there needs to be the capability to display and print a report of cash register balances. As pictured:

dspregisterbalances1.png

Note the filter selection criteria by site (warehouse), and that the report only shows balances for open registers and closed registers with a balance greater than zero. There is an option, however, to list all registers, including closed registers with zero balance.

The ability will be provided to review cash register management history. A history report will be available as pictured below:

dspregisterhistory1.png

Users should be able to right click and either view the transaction detail history in the original window, or print the detail receipt.

Batch Manager Changes

None Anticipated

Usability Considerations

One primary consideration when developing this set of modules is to make conducting a sale involve as little interaction as possible from the user. At a minimum with the Cash Register module in scan mode, the sales clerk should simply scan all the items being purchased, hit the Enter key to launch the payment screen, enter the cash amount tendered, and hit Enter again.

Problems and Alternatives

Credit Card Handling

Credit card activation may pose a problem as the current paradigm relies on storing credit card data persistently with customer data. While Highlander expects to have customer records, there could frequently be sales situations where the consumer is simply purchasing an accessory and no customer record will be provided, which would present problems for credit card processing. To mitigate this problem on the first pass of development we should require a customer record to process a credit card. It is likely, however, in future passes that we will need to rework credit card processing to operate without a customer record and persistent storage of the credit card number.

Inventory Transactions

As indicated in the internal design section below, inventory transactions are not posted until the sale is closed. However, serial number distribution, if applicable, must be handled at the time the item is scanned or entered at the sale. The usual xTuple paradigm of only prompting for serial detail at the time of posting is unacceptable in this environment. Therefore, the inventory distribution class will have to be modified since it is currently designed to only associate lot/serial transaction detail with a pre-existing transaction. The schema specification provided below assumes the serial detail should be recorded subordinate to the register item record. One way to achieve this would be to add a static function to the inventory distribution class that, rather than creating transactional detail, simply returns a list of selected location, lot/serial and qty detail records that can be recorded with the sale, then used for processing when the sale is closed. Generally, however, the developer can solve this problem using his or her own discretion bearing in mind, however, that whatever logic is used to handle this situation should be extensible to other kinds of transactions in the future that may have similar requirements.

There is a specific requirement to record sales revenue in the G/L as aggregate postings when a register is closed to prevent the proliferation of millions of detailed revenue records against the General Ledger. The same problem will still exist with inventory related transactions as a G/L transaction will be created for every sales line inventory transaction if existing xTuple business logic is used. This could be mitigated by creating a new global option to post inventory transactions to the G/L in batch. In this scenario, all inventory transactions would be posted to a sub ledger that could be reviewed and posted to the G/L in aggregate. However, this functionality is not essential to making a working example of the cash register, requires changes to the core inventory management code base and can be easily broken into a separate feature specification. It will therefore be handled separately.

Commissions

Architecturally, the Cash Register will pave the way for split commission scenarios, however, the current structure of commissions in xTuple does not allow for this, and the work required to achieve that functionality is outside the scope of this specification. For now, Cash Register will only allow users to accept one default sales rep on a sale, with the possibility left open to add in commission splitting functionality later with the AdvancedSalesCommissions feature.

Other Features beyond scope

This document describes a "best guess" at requirements for Highlander retail sales management. It is subject to change as more information is gathered. That said, it is certain that some functionality is being held back for later iterations or separate specifications including:

  • The linking of documents to the sale
  • The presentation of accessory (cross selling) lists for the item selected
  • Sales Lookup
  • Return Handling
  • Gift Certificate Handling

Some work done on the highlander project specifies touch screen processing. Since the xTuple interface is intended to be a secondary interface, it is not necessary at this time to design the UI specifically as a touch screen application. Also, there will undoubtedly be requirements at some point to link this Cash Register application to specific hardware for credit card swiping, physical cash till opening signals and possibly other interactive devices. However, since we do not currently have requirements or specifications to interface with these devices, it is not necessary to develop support mechanisms for them at this time.

Internal Design

Basic Algorithms

Opening a Register

Create a history record in reghist
        Set the terminal id
        Record the Site Id
        Copy in the asset and account ids from the Retail Site record
        Copy in the tax authority id from the warehouse
        Copy in the tax currency id from the tax authority
        Set open boolean status as true and open time as current time
Create a detail record in regdetail
        Relate to the parent history record
        Set the starting balance based on the previous ending balance or zero if none
        Set type as opening
        Set cash sales to 0
        Set transfer amount to specified amount
        Set adjustment amount to specified amount
        Set ending balance to calculated amount
        Set time and username

Adjusting a Register

If register is not open
        create a reghist record as described in opening a register except:
                status will be closed
Create a detail record
        Relate to the parent history record
        Set the starting balance based on the previous ending balance or zero if none
        Set type as adjustment
        Set cash sales to calculated cash sales since last detail entry
        Set transfer amount to specified amount
        Set adjustment amount to specified amount
        Set ending balance to calculated amount
        Set time and username
Associate all unposted cash sales headers to the new detail record
If deposit checks was checked and register is open
        Associate all unposted check sales headers to the new detail record
If register is not open
        Create a g/l series
        If transfer amount
          Create a g/l series entry to debit bank account specified for transfer amount
        If adjustment amount
          Create a g/l series entry to debit adjustment account specified for transfer amount
        Create a g/l series entry to credit asset account specified on parent reghist record for sum of transfer and adjustment amount
        Post g/l series

Closing a Register

Create a detail record
        Relate to the parent history record
        Set the starting balance based on the previous ending balance or zero if none
        Set type as close
        Set cash sales to calculated cash sales since last detail entry
        Set transfer amount to specified amount
        Set adjustment amount to specified amount
        Set ending balance to calculated amount
        Set time and username
Associate all unposted cash sales headers to the new detail record
Associate all unposted check sales headers to the new detail record
Associate all credit card sales headers associated with this reghist record to the new regdetail record
Start a g/l series
For each regdetail record associated with this parent reghist
        For sum of cash sales associated with regdetail
          Create g/l series record for cash sales that debits asset account specified on reghist record
        If transfer
          Create g/l series record for cash transfer amount that credits asset account specified on reghist record
        If adjustment
          Create g/l series record that credits adjustment amount for account specified on parent reghist record
        Create g/l series record for sum of checks, transfer and adjustments that debits bank account on regdetail
For sales items associated with current reghist record
        If credit card sales exist
          Create g/l series record debiting credit card account for sum of credit card sales
        Sum revenue by account using sales account mapping logic
        For each revenue account
          Create g/l series record crediting account
        Sum tax by account using tax authority mapping logic
        For each tax account
          Create g/l series record crediting account
Post g/l series
Flag reghist as open=false
Set close time on reg hist

Editing Items

Note that saving line items does not create an inventory transaction

Calculate price based on pricing schedule logic
Calculate tax based on tax logic

Tendering Payment

If credit card
  Process credit card
  If fail
    Error message
    Return
For each sale item
 Create inventory transactions
 If location/lot/serial detail exists
   process inventory detail
Update header record
  Record tendering information
  Change header status to closed

Custom Widget Changes

No custom widgets or custom widget changes are anticipated for this specification.

Schema Changes

New tables should be created in a schema called "retail" which should have its own SVN repository. Suggested table definitions are listed as actual self describing SQL statements below as "ready to use" code. The developer may alter these definitions at his or her discretion as needed to meet business logic requirements. The complete SQL file set, including field level comments, are attached here:

cashRegisterSQL.zip

Retail Site table

The retail site table is captures data for the Retail Site window:

CREATE TABLE retail.rtlsite
(
   rtlsite_id serial PRIMARY KEY,
-- Retail site primary key
   rtlsite_warehous_id integer NOT NULL REFERENCES whsinfo (warehous_id),
--Retail site warehouse id
   rtlsite_quotedays integer NOT NULL DEFAULT 1,
--Retail site number of days until a quote expires
   rtlsite_asset_accnt_id integer NOT NULL REFERENCES accnt (accnt_id),
--Retail site asset account id
   rtlsite_adjust_accnt_id integer NOT NULL REFERENCES accnt (accnt_id)
--Retail site adjustment account id
);

Retail Site Bank Account Table

This table stores the listing of valid bank accounts

CREATE TABLE retail.rtlsitebnkacct
(
   rtlsitebnkacct_id serial PRIMARY KEY,
--Retail site bank account primary key
   rtlsitebnkacct_rtlsite_id integer NOT NULL REFERENCES retail.rtlsite (rtlsite_id),  --Retail site retail site id
   rtlsitebnkacct_bankaccnt_id integer NOT NULL REFERENCES bankaccnt (bankaccnt_id)
--Retail site bank account bank account id
);

Cash Register History Table

Stores information recording the opening and closing of cash register sessions

CREATE TABLE retail.reghist
(
  reghist_id serial PRIMARY KEY,
-- Cash register history primary key
  reghist_warehous_id integer NOT NULL REFERENCES whsinfo (warehous_id),
-- Cash register sale warehouse (retail site) id
  reghist_terminal text NOT NULL,
-- Cash register terminal number
  reghist_taxauth_id integer REFERENCES taxauth (taxauth_id),
-- Cash register tax authority id
  reghist_tax_curr_id integer REFERENCES curr_symbol (curr_id),
-- Cash register tax currency id
  reghist_asset_accnt_id integer REFERENCES accnt (accnt_id),
-- Cash register asset account id
  reghist_adjust_accnt_id integer REFERENCES accnt (accnt_id),
-- Cash register adjustment account id
  reghist_open boolean NOT NULL DEFAULT true,
-- Cash register open flag
  reghist_open_time timestamp with time zone NOT NULL DEFAULT now(),
-- Cash register open time
  reghist_close_time timestamp with time zone
-- Cash register close time
);

Cash Register Detail Table

This table stores transaction detail for register opening, adjustment and closing. It is subordinate to the Cash Register History table.

CREATE TABLE retail.regdetail
(
  regdetail_id serial NOT NULL PRIMARY KEY,
-- Cash register detail primary key
  regdetail_reghist_id integer NOT NULL REFERENCES retail.reghist (reghist_id),
-- Cash register detail cash register history id
  regdetail_type character(1) NOT NULL CHECK (regdetail_type IN ('O','A','C')),
-- Cash register detail type: Open, Adjustment, Close
  regdetail_startbal numeric NOT NULL DEFAULT 0,
-- Cash register detail starting balance
  regdetail_cashslsamt numeric NOT NULL DEFAULT 0,
-- Cash register detail cash sales amount processed between current transaction and previous detail transaction
  regdetail_bankaccnt_id integer REFERENCES bankaccnt (bankaccnt_id),
-- Cash register detail transfer bank account id
  regdetail_transferamt numeric NOT NULL DEFAULT 0,
-- Cash register detail transfer amount to register
  regdetail_adjustamt numeric NOT NULL DEFAULT 0,
-- Cash register detail adjustment amount to register
  regdetail_endbal numeric NOT NULL DEFAULT 0,
-- Cash register detail ending balance
  regdetail_depchks boolean NOT NULL DEFAULT false,
-- Cash register detail deposit checks flag
  regdetail_time timestamp with time zone DEFAULT now(),
-- Cash register detail time
  regdetail_username text NOT NULL
-- Cash register detail user name
);

Cash Register Sale Header Table

This table records header information for quotes and sales in the Cash Register window.

CREATE TABLE retail.reghead
(
  reghead_id serial NOT NULL PRIMARY KEY,
-- Primary key for cash register header
  reghead_number text UNIQUE,
-- User interface sale number
  reghead_reghist_id integer NOT NULL REFERENCES retail.reghist (reghist_id),
-- Cash register history record id
  reghead_type character(1) NOT NULL CHECK (reghead_type IN ('S', 'Q')),
-- Cash register sale type
  reghead_cust_id integer REFERENCES custinfo (cust_id),
-- Cash register sale customer id
  reghead_time timestamp with time zone NOT NULL DEFAULT now(),
-- Cash register document date
  reghead_status character(1) DEFAULT 'O' CHECK (reghead_status IN ('O','C')),
-- Cash register sale status: open or closed
  reghead_notes text,
-- Cash register sale notes
  reghead_payment character(1) NOT NULL CHECK (reghead_payment IN ('S','K','C')),
-- Cash register sale payment: cash, check or credit card
  reghead_tendered numeric NOT NULL DEFAULT 0,
-- Cash register sale payment amount tendered
  reghead_checknumber text,
-- Cash register sale check number, if applicable
  reghead_regdetail_id integer REFERENCES retail.regdetail (regdetail_id)
-- Cash register sale register transaction detail id
);

Cash Register Sale Item Table

This table stores line item detail for a cash register sale.

CREATE TABLE retail.regitem
(
  regitem_id serial NOT NULL PRIMARY KEY,
-- Cash register sale item primary key
  regitem_reghead_id integer NOT NULL REFERENCES retail.reghead (reghead_id),
-- Cash register sale item cash register sale id
  regitem_linenumber integer NOT NULL,
-- Cash register sale item line number
  regitem_itemsite_id integer NOT NULL REFERENCES itemsite (itemsite_id),
-- Cash register sale item site id
  regitem_qty numeric NOT NULL,
-- Cash register item quantity
  regitem_unitprice numeric NOT NULL DEFAULT 0,
-- Cash register item unit price
  regitem_taxtype_id INTEGER REFERENCES taxtype (taxtype_id),
-- Cash register item tax type id
  regitem_tax_ratea numeric DEFAULT 0,
-- Cash register item tax rate A
  regitem_tax_rateb numeric DEFAULT 0,
-- Cash register item tax rate B
  regitem_tax_ratec numeric DEFAULT 0,
-- Cash register item tax rate C
  CONSTRAINT regitem_regitem_reghead_id_key UNIQUE (regitem_reghead_id, regitem_linenumber)
-- Do not allow duplicate line numbers
);

Cash Register Sale Item Distribution Table

This table stores distribution detail for location and lot/serial controlled items.

CREATE TABLE retail.regitemdist
(
  regitemdist_id serial NOT NULL PRIMARY KEY,
-- Cash register item distribution primary key
  regitemdist_regitem_id integer NOT NULL REFERENCES retail.regitem (regitem_id),
-- Cash register item distribution cash register item id
  regitemdist_location_id integer REFERENCES location (location_id),
-- Cash register item distribution location id
  regitemdist_ls_id integer REFERENCES ls (ls_id),
-- Cash register item distribution lot/serial id
  regitemdist_qty numeric NOT NULL
-- Cash register item distribution quantity
);

Cash Register Sale Characteristics Table

Cash Register will use the public.charass table to store characteristics as with all other characteristic functionality. Characteristic type could be 'CR.'

Cash Register Sale Sales Reps Table

This table stores the listing of sales reps associated with a Cash Register sale.

CREATE TABLE retail.regslsrep
(
  regslsrep_id serial NOT NULL PRIMARY KEY,
-- Cash register sales rep id
  regslsrep_reghead_id integer NOT NULL REFERENCES retail.reghead (reghead_id),
-- Cash register sales rep cash register sale header id
  regslsrep_salesrep_id integer NOT NULL REFERENCES salesrep (salesrep_id)
-- Cash register sales rep sales rep id
);

API Views

Views should be created in the API schema that mirror the methodology of other API views, with the one new difference being that these views will be the first designed by xTuple to straddle business logic stored in the general public schema and another vertically specialized schema, in this case "retail." Because this functionality is sponsored by the Highlander Project which will use the views as the primary interface, the views should be designed first and foremost, and the graphical interface should use them exclusively to ensure compatible business logic.

As usual, the views included should roughly match the graphical user interface and should therefore include:

  • retailsite
  • retailsitebankaccnt
  • cashregister
  • cashregistersalesrep
  • cashregisteritem
  • cashregisterchar
  • cashregisterdist (lot/serial distribution)
  • cashregisteropen
  • cashregisterclose
  • cashregisteradjust

Note the last 3 views listed are transactional, would rely on functions described in Algorithms above and Stored Procedure Changes below, and would not be able to be updated or deleted. Also, edit privileges listed below should be enforced by triggers, as should status logic. (i.e. expired quotes can not be converted to sales, closed sales can not be edited). Pricing and tax logic should default in the same manner on these views as it does on sales order views.

Privileges for Cash Register

 

Name

Description

MaintainRetailSite

Can Add/Edit/Delet Retail Site mappings

CreateCashRegisterSales

Create and Process Cash Register Quotes and Sales

MaintainCashRegisterSales

Can Open, Adjust and Close Cash Registers and view associated reports

Stored Procedure Changes

New stored procedures will need to be added to open close and adjust the register as described in the Basic Algorithms section. Triggers can be used to enforce privileges and status enforcement at the table level.

Performance Considerations

No performance affect on the broader application is anticipated. This will, however, be the first case where the screen builder and javascript paradigm is used to build a complete application extension. An eye should be kept on any performance limitations found by using this platform. For example, users should not have to wait any noticeable amount of time when bar code scanning an item for the UI to respond. If there is a time lag, we should examine closely whether the issue can be addressed by query optimization, script optimization, widget optimization or re-writing the window in C++, with C++ being the option of last resort.

This application is intended to be used at hundreds or thousands of workstations against a single database. Because of the potential for bottlenecks due to transactional processing loads, it will be best to process transactions in batch where possible, which is why posting of sales to the G/L is only specified to happen at Cash Register closing, and why it is also recommended that functionality to allow batch posting of inventory transactions to the G/L be developed in parallel. Developers and interested parties are encouraged to discuss any further opportunities or ideas on how to defer transactional or network load in the interest of keeping response time quick and transaction costs low.

QA Considerations

Since this feature is being designed explicitly for the Highlander Project, it is likely that much testing will be done by the Highlander testing team. Also, considering that the modules described are not part of the core application, if any testing is to be done using xTuple testing resources it will have to be coordinated in a new way since iterations of this application are not tied to a core release. We will need to address this issue more fully when we have more information.

Documentation Considerations

Again, because this application is a separate module from core xTuple ERP, documentation requires special consideration. It should not be included in the core documentation. At a minimum, the first pass at Cash Register help files should be to simply create a doc topic on www.xtuple.org.

There has been previous discussion of the notion of moving the DocBook based xTuple help files into the database. This would allow help file updates to be part of the database update process, and allow powerful linking capabilities to be leveraged by creating relationships between UI screens and help records. As a second phase, we may want to explore implementing this specifically for Cash Register as a prototype model. Once the methodology is established we could then gradually migrate core help files as well. The end result would be a help file system that extends automatically when application extensions are added.

The Highlander Project has extensive documentation staff. We should make every effort to use their existing resources to help with the documentation effort.

Release Considerations

Because Cash Register is an application extension, it is unnecessary to bind it to a specific core release. However, the process of developing this application will almost certainly uncover areas where the core application (specifically the script toolbox) will have to updated to allow certain Qt and xTuple functionality to be exposed. Developers should make an effort to identify these potential areas up front and work to include any necessary additions to the core in the 3.1 release of xTuple ERP. For certain, this specification identifies that the inventory distribution class is one core area that will need to be modified to accommodate certain requirements.

Using the screen builder paradigm poses significant challenges to installation and upgrade management. A specification for enhanced Package Manager capabilities will need to accompany this specification. Package Management will allow the xTuple Updater to load and upgrade specific application extensions that are developed and released separately from the core application. The package manager will allow a method to associate tables, views, functions, triggers and certain records to be associated with a functional extension such as Cash Register. These extensions could then be installed, upgraded and removed from xTupleERP as separate modules. While the development of this tool will be necessary before the release and deployment of Cash Register to the public, its existence does not preclude the development of Cash Register from beginning immediately. For interim development, functionality relating to Cash Register can be stored and tracked in a separate SVN repository, and work done on Package Management can be done in parallel.

AttachmentSize
cashRegisterSQL.zip4.57 KB
cashRegisterUI.zip17.13 KB