Enable "smart mouse" setting on workstations

The Smart Mouse setting that was previously available on the Workstation Preferences window in

CounterPoint SQL is no longer available. If you were using this setting to automatically move the pointer to the default button of each dialog in CounterPoint, you can instead enable the equivalent setting on the Mouse Properties property sheet of the Windows Control Panel.

To enable this setting, select Start > Settings > Control Panel, double-click Mouse, select the Pointer Options tab, and select the Snap To check box.

Repeat this step for each workstation on which you wish to enable this setting.

Updating customizations

If you are updating a CounterPoint SQL system that has been customized, you must ensure that the custom modifications that were implemented in your old version are appropriate and function correctly in V8.3.9. This section of the CounterPoint SQL Update Guide highlights some of the changes that may have an impact on your customizations and describes specific areas of the software you should consider before updating. These topics are not intended to be an exhaustive "to do" list, but should serve as a suitable starting point to enable you to make a smooth transition to V8.3.9.

You should have records of the custom modifications that have been made to your system. Review the details of those records before reading the topics in this section.

You should also generate and print the Database Customizations Report (System > Utilities > Database Customizations Report) to be sure you have a record of all your customizations. This report lists database schema changes, triggers, stored procedures, and other information that you should document. It is easy to forget exactly how your system has been modified and this report is a useful tool for tracking those changes.

Review the Database Comparison topics for detailed information about the differences between the default V8.3.8 and V8.3.9 database schema. For a comparison between the V8.3.7 and V8.3.9 database schema, refer to the CompareDBResultsFrom837.html file on the CounterPoint SQL DVD.

In addition, you may wish to print screen shots of certain customizations, such as custom maintenance or view forms, to verify that the new system matches your old version.

Database schema changes BOGO/twofer pricing

Custom and customized reports OPOS Point of Sale forms Update Pre-defined Data Delta-tracked tables Custom stored procedures

Prompt for custom payment fields Validated returns

Ticket Entry customizations (V8.3.7 only) Ticket posting customizations (V8.3.7 only)

Crystal receipts and Point of Sale forms (V8.3.7 only) Fixing custom Crystal reports and receipts (V8.3.7 only) Custom Point of Sale maintenance forms (V8.3.7 only) Custom indexes for Point of Sale tables (V8.3.7 only) Data Dictionary considerations (V8.3.7 only)

Update Guide

Database schema changes

Typically, database schema changes you have made will be retained during the update process, because those changes are part of the database. However, you must take the appropriate steps to ensure that your

customizations are incorporated into the UpdateFrom8.3.x.sql scripts before you begin the update. For example, if you added a trigger that depends on a particular database column and that column is removed by the update script, your trigger will not work.

Refer to the Database Comparison topics for detailed information about the differences between the V8.3.8 and V8.3.9 database schema.

Refer to the CompareDBResultsFrom837.html file in the root directory of the CounterPoint SQL DVD for detailed information about the differences between the V8.3.7 and V8.3.9 database schema.

Point of Sale data model changes (V8.3.7 only)

In V8.3.8, extensive changes were made to the Point of Sale data model. CounterPoint only supports custom fields in Point of Sale transaction tables for which there is a corresponding EXT table (i.e., header, line, payment, cell, and serial number tables).

Custom columns are not supported in Point of Sale transaction tables for which there is not a corresponding EXT table (i.e., tax, package tracking, note, stored value card, gift certificate, and audit log tables).

If you are updating from V8.3.7 and you have added custom columns to V8.3.7 Point of Sale (PS) tables for which custom columns are still supported, the UpdateFrom8.3.7.sql script and the history conversion task will move your custom columns and their contents to the appropriate EXT table. After you update your database, you should add all future custom columns to one of these EXT tables (e.g., PS_DOC_HDR_EXT).

If you have added custom columns to tables for which they are no longer supported, those columns will be dropped and the data stored in them will be lost when you update to V8.3.9. You must take the necessary steps to preserve the data in your custom columns.

In addition, any custom constraints you may have added to any Point of Sale tables (including constraints on custom columns) will be lost when you update to V8.3.8. Similarly, if you have added SQL-calculated columns to any Point of Sale tables, they will also be lost during the update. You may be able to manually restore your constraints and SQL-calculated columns to the appropriate V8.3.8 tables after you update your database.

BOGO/twofer pricing

As detailed in the current Release Notes, CounterPoint now allows you to define "BOGO" (e.g., "buy one, get one free" or "buy one, get one for 50% off") and "twofer" (e.g., "2 for $1.00" or "3 for $5.00) price rules. If you customized a previous version of CounterPoint to simulate BOGO/twofer pricing, you may wish to disable or remove those modifications and use the BOGO/twofer pricing that is built in to V8.3.8 instead.

Implementing the BOGO/twofer pricing feature required major changes to the price and loyalty point calculation process. If you have made any customizations related to price or loyalty point calculations, in all likelihood, they will no longer work after you update to V8.3.9.

Specific, known issues related to these pricing changes are outlined below:

Pricing stored procedure changes

The USP_GET_PRICE stored procedure that was used in previous versions of CounterPoint SQL has been renamed to USP_PRICE_GET_PRICE. If you have customized the USP_GET_PRICE stored procedure in a previous version, it will be renamed automatically when you update your database.

Similarly, if you have implemented USER_BEFORE_GET_PRICE and USER_AFTER_GET_PRICE stored procedures in your database, they will be renamed USER_BEFORE_PRICE_GET_PRICE and

USER_AFTER_PRICE_GET_PRICE when you update your database.

Updating customizations

In addition, the parameters for the following pricing stored procedures have been changed to accommodate BOGO/twofer pricing. If you have customized any of these stored procedures, you must update them to match the new parameters.

• USP_PRICE_GET_PRICE

• USER_BEFORE_PRICE_GET_PRICE

• USER_AFTER_PRICE_GET_PRICE

• USP_GET_PRICE_AND_POINTS

• USER_BEFORE_GET_PRICE_AND_POINTS

• USER_AFTER_GET_PRICE_AND_POINTS

Refer to the Database Comparison topics for detailed information about stored procedure changes.

Custom price rules

In previous versions, if you created custom stored procedures for inclusion in price rules, the names of the parameters you included in those stored procedures did not matter, only that they appeared in the correct order. In this version, you must update your custom price rule stored procedures to use the following parameter names:

• @SessionId

• @LineSessionId

• @GrpCod

• @GrpTyp

• @RulSeqNo

• @ItemNumter

• @CustomerNumber

• @StkLocID

• @PrcLocID

• @AbsLineQtySold

• @UnitFlag

• @SaleDateTime

• @UnitPrice

• @PricingBreakDesc

Custom loyalty point earning rules

Update Guide

Custom triggers

If you have implemented custom triggers on the IM_PRC_WRK table and/or the IM_PRC_WRK_CELL table, you should verify that your triggers still work as expected.

Hold and Recall price modifications

If you have implemented custom stored procedures to modify prices on Point of Sale documents during a Hold and Recall action, you should verify that they still work as expected.

If your customizations update prices by recording a price override, they should continue to work. However, if your customizations changed prices without performing a price override, they will no longer function.

To modify prices during a Hold and Recall action, you must change data in the PS_DOC_LIN_PRICE table (e.g., UNIT_PRC). When a Point of Sale document is recalled, CounterPoint will recalculate line-level prices (e.g., CALC_PRC, PRC, EXT_PRC, and so forth) based on the new PS_DOC_LIN_PRICE values.

In addition, each line item may now have multiple prices. If your Hold and Recall customizations assume that only one price value exists per line, you should update them accordingly.

Price justification data

If you have customized any process that reads or writes price justification data (e.g., PRC_GRP_COD, PRC_GRP_DESCR, PRC_RUL_DESCR and so forth), you must update your customizations to accommodate the following changes:

• The PS_DOC_LIN_PRICE_COST table has been renamed to PS_DOC_LIN_PRICE

• Each Point of Sale document line may now have more than one record in the PS_DOC_LIN_PRICE table

• Price justification fields have been removed from many views (including VI_PS_DOC_LIN)

Custom and customized reports

As always, you should review your custom reports and any standard reports you have modified—whether they are based on stored procedures or tables—and update them to accommodate any relevant database schema changes.

If you are updating from V8.3.7, custom reports that rely on Point of Sale (PS) tables require particular

attention, as they will no longer work after you update to V8.3.9, due to the extensive Point of Sale data model changes that were made in V8.3.8. If you do not take the appropriate action to update your custom .rpt files, you will encounter errors when CounterPoint attempts to use them.

Follow the steps outlined in Fixing custom Crystal reports and receipts to update your custom reports to work in V8.3.9.

If you only use standard (i.e., non-customized) reports, you do not have to take any additional steps.

Updating customizations

OPOS Point of Sale forms

As always, if you have customized OPOS (.xml) Point of Sale forms, review those modifications and update them to accommodate any relevant database schema changes.

Refer to the Database Comparison topics for detailed information about the differences between the V8.3.8 and V8.3.9 database schema.

Refer to the CompareDBResultsFrom837.html file in the root directory of the CounterPoint SQL DVD for detailed information about the differences between the V8.3.7 and V8.3.9 database schema.

Point of Sale data model changes (V8.3.7 only)

If you are updating from V8.3.7, OPOS receipts and other Point of Sale forms should not be affected by the Point of Sale data model changes that were made in V8.3.8. However, if a custom OPOS form refers to a column that has been dropped, you will need to remove or replace that field. Again, you should carefully review your custom OPOS forms to determine whether you need to modify them.

In particular, the views from which OPOS receipts obtain their data have been renamed to replace TKT with DOC (e.g., VI_PS_TKT_HDR_RCPT was renamed VI_PS_DOC_HDR_RCPT). If you have added custom fields to the V8.3.7 views, you should add them to the renamed views.

Update Pre-defined Data

The Update Pre-defined Data utility (System > Utilities > Update pre-defined data) does not remove existing form groups or label jobs. If you have modified a form group or label job and you want to restore the original, unmodified version, you must delete the existing form group or label job before running the Update Pre-defined Data utility.

The FORM GROUPS report (Setup > Point of Sale > Reports > Form Groups) allows you to print all components of a form group. You can use this report as a reference to assist you in redefining your form groups, if necessary.

Similarly, the LABEL JOBS report (Setup > System > Reports > Label Jobs) allows you to print detailed information about label jobs.

Delta-tracked tables

Special handling is required for triggers on Multi-Site delta-tracked tables. The following tables are delta-tracked tables in V8.3.9:

• Customers - AR_CUST

• Inventory - IM_INV

Update Guide

Custom stored procedures

You can now define up to three custom stored procedures that CounterPoint SQL will execute whenever a user saves or deletes a record from any standard maintenance window, such as the Items window, the Customers window, the Purchase Requests Enter window, and so forth. This feature allows you to apply custom database functions to a record or perform other special actions each time the record is updated.

For each maintenance window, you can create the following custom stored procedures, where TableName is the name of the underlying database table:

• USER_TableName_BEGIN_TRANSACTION is executed immediately after a user saves or deletes a record and CounterPoint begins the corresponding SQL transaction.

• USER_TableName_BEFORE_COMMIT_TRANSACTION is executed immediately before the SQL transaction is committed to the database.

• USER_TableName_AFTER_COMMIT_TRANSACTION is executed immediately after the SQL transaction is committed to the database.

If you had been using triggers to hook into such operations, you may be able to simplify your customizations by using these stored procedure hooks instead.

Prompt for custom payment fields

You can include custom fields on the payment Validations dialog by adding a custom column to the

PS_DOC_PMT_EXT table, and then adding the column to the Standard form for the PS_TE_PMT table in the Data Dictionary.

If you previously wanted to prompt for custom fields on the Validations dialog and have implemented a workaround instead, you may disable or remove those modifications and use the built-in feature instead.

Validated returns

You can use the Validated returns function in Ticket Entry and Touchscreen Ticket Entry to process validated returns for items for which a ticket is on file.

If you customized a previous version of CounterPoint to allow validated returns, you may wish to disable or remove those modifications and use the built-in validated returns feature instead.

Updating customizations

Ticket Entry customizations (V8.3.7 only)

Due to the changes made to the Point of Sale data model in V8.3.8, any custom behavior you may have implemented in Ticket Entry in V8.3.7 will no longer function after you update to V8.3.9.

If you have created custom stored procedures, triggers, or other database functions to execute before or after a Point of Sale document is saved or printed, or during a hold-and-recall action, you must update those

customizations to accommodate the new Point of Sale table structure and other database changes.

Stored procedures

All standard Ticket Entry stored procedures have been renamed to include the TE designator. For example, the USP_COMMIT_PS_DOC_BAL stored procedure is now USP_PS_TE_COMMIT_DOC. As a result, calls to corresponding USER_BEFORE and USER_AFTER stored procedures were also renamed.

If you have defined any custom stored procedures that perform some special action immediately before or after any standard stored procedures in Ticket Entry, you must rename them to match V8.3.9 naming conventions.

Refer to the CompareDBResultsFrom837.html file on the CounterPoint SQL DVD for more information about new, dropped, and modified stored procedures.

In addition, the @StrId, @StaId, and @TktNo parameters have been dropped and replaced by the @DocId parameter in all Ticket Entry stored procedures that previously accepted them. You must modify your custom stored procedures accordingly.

Database triggers

If you have created custom database triggers in any Point of Sale (PS) tables, they will be dropped when you update to V8.3.9.

After you update your database, you should carefully evaluate the new Point of Sale table structure and decide whether you want to recreate your database triggers or replace them with custom stored procedures. Bear in mind that, due to the division of Point of Sale data that occurred in V8.3.8, you may have to create triggers in multiple tables to reproduce the behavior of a single trigger in previous versions.

Absent child records

If you previously used UPDATE statements to update ticket data, you may have to modify your customizations to UPDATE or INSERT data, as appropriate, to accommodate the possibility of "child" records that don't exist.

For example, you might use a hold-and-recall action to add ship-to addresses to tickets. Previously, you could have used an UPDATE statement to update the PS_TKT_HDR table. Since ship-to information is now stored in the PS_DOC_CONTACT table, if a ticket did not already have a ship-to address, you must use an INSERT statement to add one.

Update Guide

Ticket posting customizations (V8.3.7 only)

Due to the Point of Sale data model changes that were made in V8.3.8, any custom stored procedures or triggers you implemented in V8.3.7 to "hook" into the ticket posting process will no longer function after you update to V8.3.9.

If you have created custom stored procedures, triggers, or other database functions to execute before or after a Point of Sale document is posted, or during the posting process, you must update those customizations to accommodate the new Point of Sale table structure and other database changes.

For example, if you have defined any of the following custom stored procedures, you must modify them to accept the appropriate parameters (i.e., @DocId instead of @TktNo):

• USER_BEFORE_PS_DOC_POST_RUN

• USER_PS_DOC_POST_TRX

• USER_AFTER_PS_DOC_POST_RUN

In addition, if you have defined any USER_BEFORE or USER_AFTER stored procedures to hook into the various steps in the ticket posting process, you must update them to match the renamed USP_COPY stored procedures and modify them to accept the appropriate parameters.

Finally, custom database triggers in PS tables will be dropped when you update to V8.3.9. You should carefully evaluate the new Point of Sale table structure and decide whether you want to recreate your database triggers or replace them with custom stored procedures. Bear in mind that, due to the division of Point of Sale data that occurred in V8.3.8, you may have to create triggers in multiple tables to reproduce the behavior of a single trigger in previous versions.

Crystal receipts and Point of Sale forms (V8.3.7 only)

If you are updating from V8.3.7, custom Crystal Reports format (.rpt) receipts and other Point of Sale forms will no longer function after you update to V8.3.9 due to the changes to the Point of Sale data model that were made in V8.3.8, . If you do not take the appropriate action to update the corresponding .rpt files, you will encounter errors when CounterPoint attempts to use them.

Follow the steps outlined in Fixing custom Crystal reports and receipts to update your custom Crystal Reports format receipts to work in V8.3.9.

If you only use standard (i.e., non-customized) Crystal Reports format receipts, you do not have to take any additional steps.

Updating customizations

Fixing custom Crystal reports and receipts (V8.3.7 only)

If you are updating from V8.3.7, follow these steps to update your custom Crystal receipts and reports to work with V8.3.9:

1. From a workstation on which you installed the CounterPoint uilities, browse to the \Bin subdirectory of the CounterPoint workstation directory (e.g., C:\Program Files\CounterPoint\Bin).

2. Double-click the ReplaceTablesInCrystalReports.exe file to start the Crystal Replace Tables utility.