Many of the database stored procedures use the variables in the PRO_SP_VARIABLES table to determine how to load the data. It is important to set the variables correctly so the data in the database loads properly. Administrator users can use the Data Editor – System Variable Settings table to manage these settings.
Click the links below for instructions on how to set specific variables:
Salesperson Number Used in Tlog
Configuring SKU and Customer Master Variables
Configuring General LP and SP Module Variables
Configuring Data Purge Process Variables
Configuring Sales Less than Threshold Variable, Setting and Managing Threshold Groups
Notes:
Remember to set the variable for both VAR_VALUE and VAR_VALUE2
Check and set all database variables in PRO_SP_VARIABLES table according to the installation questionnaire spreadsheet that should have been already completed.
To modify a system variable setting:
From the Tools menu, choose Data Editor.
In the Data Editor, double click on the row for System Variable Settings.
In the row of the variable that
you want to modify, click the Available
Actions 
icon, and choose Edit.
This displays an Edit Record page, where you can edit the variable values.
Refer to the information in the following sections for definitions of system variables and guidelines on entering values.
Store and Cashier/Employee/Salesperson Variables
STORE_UNIQUE - determines if store numbers are unique across the organization.
CASHIER_UNIQUE – determines if cashier numbers are unique across the organization.
EMPLOYEE_COPY - determines if cashier number is copied to employee number.
SALESPERSON_COPY - determines if cashier number is copied to salesperson number.
EMPNUM_USED - determines if employee number is used or always NULL.
SALESPERSONNUM_USED - determines if salesperson number is used or always NULL.
CASHIER_SIZE - determines the maximum size of CASHIERNUM, EMPNUM, and SALESPERSONNUM
SKU and Customer variables:
SKU_STAGE_OVERRIDE - determines if the SKU data from staging should override SKU temp.
CUST_STAGE_OVERRIDE - determines if the customer data from staging should override Customer temp.
General LP and SP Module Variables
PROCESSING_TYPE – determines the type of processing, either batch or real time.
FN_PRO_VOID_SCHEME – indicates which void scheme the customer uses.
LP_SP_OPTION – determines whether the customer purchased the Loss Prevention Module alone, or also purchased the Sales and Productivity Module.
CAPTURE_PV_DETAILS – Indicates if the tlog captures the Post void details.
POSTVOID_PROCESSING – Determines if the database or ETL handles the post void processing.
FB_NOSALE – Determines if the database or ETL handles the Followed by No Sale processing.
COMP_BACKFILL_DAYS – This is the number of days to go back to populate the spo_comp_poll table.
POLLED_DAYS - The number of days sp_spo_comp_poll procedure will look back for late polls.
SALES_THRESHOLD – Used for reporting sale transactions with a non-zero tender amount below a defined threshold value.
No Match Processing Variables
PROCESS_NM_RETURNEXCH – determines if we run the procedure that populates the return and exchange no match data.
PROCESS_NM_PVCANCEL - determines if we run the procedure that populates the post void and cancel no match data.
CAPTURE_ORIG_REGNUM – indicates if the tlog captures the original register number on returns.
PV_MINS – indicates the number of minutes to look forward for post void no match processing.
CANCEL_MINS – indicates the number of minutes to look forward for cancel no match processing.
These variables are indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog and Store Master.
CASHIER_STORE.STORE_UNIQUE
STORE_UNIQUE Settings for VAR_VALUE and VAR_VALUE2:
If Store Number is unique in Chain, set to Y.
If Store Number is not unique in Chain, set to N.
If Store Number is not unique in Chain and is unique within division, set to N.
These variables are indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog and Employee Master
CASHIER_STORE.CASHIER_UNIQUE
CASHIER_UNIQUE Settings for VAR_VALUE and VAR_VALUE2:
If Cashier Number is unique in Store but not unique in Chain, set to N.
If Cashier Number is unique in chain, set to Y.
These variables are indicated in the POS Questionnaire. Confirm them from the content of the customer’s Employee Master.
Employee number refers to the identification of the employee that is posted to the tlog on transactions where the employee is the customer, not the employee number from the customer’s HR system.
CASHIER_STORE.EMPLOYEE_COPY
EMPLOYEE_COPY Settings for VAR_VALUE and VAR_VALUE2:
If employee numbers on employee sales can be different from cashier numbers, set to N.
When a record is added to the employee master as a ‘NOF’ (Not on File) from the tlog and the cashier number should be copied to the employee number field in the NOF, set to ‘Y’.
These variables are indicated in the POS Questionnaire. Confirm them from the content
of the customer’s Employee Master.
CASHIER_STORE.SALESPERSON_COPY
SALESPERSON_COPY Settings for .VAR_VALUE and VAR_VALUE2
If salesperson numbers can be different from cashier numbers, set to N.
If salesperson numbers are not used at all, set to N.
If the cashier number should be copied to the salesperson number when a record is added to the employee master as a ‘NOF’ (Not on File) from the tlog, set to Y.
If the salesperson field in the tlog would be populated with the same number as the cashier number of the employee, then set to Y.
This variable is indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog and Employee Master.
This is true when an employee is the customer for a transaction and their employee number is captured and posted in the tlog
CASHIER_STORE.EMPNUM_USED
EMPNUM_USED Settings for VAR_VALUE and VAR_VALUE2:
If employee number field in the tlog is not null for employee sales and returns, set to ‘Y’.
If employee number field is null in the tlog for employee sales & returns, set to ‘N’.
This variable is indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog and Employee Master
CASHIER_STORE.SALESPERSONNUM_USED
SALESPERSONNUM_USED Settings for VAR_VALUE and VAR_VALUE2:
If salesperson number can be populated in the tlog for sales and returns, set to Y.
If salesperson number is not in the tlog for sales & returns, set to N.
This variable is indicated in the POS Questionnaire. Confirm them from the content of the customer’s Employee Master
CASHIER_STORE.CASHIER_SIZE
Determines the maximum size of the customers column which will be the source for CASHIERNUM, EMPNUM, SALESPERSONNUM – this value is used in the calculation of the CASHIERID, EMPLOYEEID and SALESPERSONID column values.
CASHIER_SIZE Settings for VAR_VALUE and VAR_VALUE2
The max size allowable is 20 for CASHIERNUM, EMPNUM, SALESPERSONNUM and will control the sizing of all three of the ID columns.
The default is 10. If the length of the cashier number in tlog and Employee Master are shorter than or equal to the default, use the default.
If the cashier number is longer than the default, change cashier_size to that length.
This variable determines if the SKU master update procedure will overwrite the values in the SKU master table with values from the staging table.
MASTERUPDATE.SKU_STAGE_OVERRIDE
SKU_STAGE_OVERRIDE Settings for VAR_VALUE and VAR_VALUE2:
If you want the sku master data to be overwritten by the pos_staging sku data then set this to Y.
If you don’t want the sku master data overwritten then set this to N.
This variable determines if the customer master update procedure will overwrite the values in the customer master table with values from the staging table.
MASTERUPDATE.CUST_STAGE_OVERRIDE
CUST_STAGE_OVERRIDE Settings for VAR_VALUE and VAR_VALUE2:
If you want the customer master data to be overwritten by the pos_staging customer data then set this to ‘Y.’
If you do not want the customer master data overwritten then set this to ‘N.’
This variable determines the type of processing; either batch, where the ETL is run once per day, or real time, where the ETL is run every 15 minutes during the day and one end of day procedure at the end of the day.
XBRI.PROCESSING_TYPE
PROCESSING_TYPE Settings for VAR_VALUE and VAR_VALUE2:
For batch processing set this to BATCH.
For real time processing set this to REAL.
This variable determines whether an organization has purchased the both LP Module alone, or both LP and SP modules.
XBRI.LP_SP_OPTION
LP_SP_OPTION Settings for VAR_VALUE and VAR_VALUE2:
If the organization has purchased the Loss Prevention module only, set to LP.
If the organization has purchased both modules, set to BOTH.
This variable is indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog.
If post void details are not captured on the post void transaction a database stored procedure will create the detail lines by looking at the original transaction.
PROACT.CAPTURE_PV_DETAILS
CAPTURE_PV_DETAILS Settings for VAR_VALUE and VAR_VALUE2:
If Post Void transactions have the detail lines from the voided transaction, set to Y.
If Post Void transactions do not have details, set to N.
This variable determines whether the database or ETL handles the post void processing.
This is for the transaction status and post void time difference columns at header level. If the database handles the processing it will mark the original transaction with transstat = ‘POSTVOID’ and calculate the time difference. In a real time processing environment this should be set to Y’
XBRI.POSTVOID_PROCESSING
POSTVOID_PROCESSING Settings for VAR_VALUE and VAR_VALUE2
If the database procedure handles the post void processing set to Y.
If the ETL handles the post void processing, set to N.
The Void Scheme variable should be updated based on the how the customer’s Tlog or web services pass the data for line item records that were voided. Because XBRi attempts to balance the quantity and amount field while still displaying the line void occurrences, two records are captured and generated for each line void. The value of the VOID_CODE field is set with “0” for not voided, “1” for voided line and “2” for voiding line.
The Void Scheme variable is available for the load process to determine based on whether the Tlog or web services send XBRi both records or only one record. If only one record, the offsetting (opposite value) record will be generated and loaded.
VOID_SCHEME.FN_PRO_VOID_SCHEME
FN_PRO_VOID_SCHEME Settings for VAR_VALUE and VAR_VALUE2:
Set value to 1 for where both the Voiding and Voided lines are included
Set value to 2 for where only one voiding line is included
This variable determines whether the database or ETL handles the Followed by No Sale processing. This is for the followed by no sale flag column at header level. If the database handles the processing it will mark the transaction prior to a NOSALE with fbnosale_flag = Y. In a real time processing environment this should be set to Y.
XBRI.FB_NOSALE
FB_NOSALE Settings for VAR_VALUE and VAR_VALUE2
If the database procedure handles the followed by no sale processing set to Y.
If the ETL handles the followed by no sale processing, set to N.
Used in the SP Module only
This is the number of days to go back to populate the spo_comp_poll table.
SPO.COMP_BACKFILL_DAYS
COMP_BACKFILL_DAYS Settings for VAR_VALUE and VAR_VALUE2
The default is 730 days
Used in the SP Module only
This is the number of days sp_spo_comp_poll procedure will look back for late polls.
SPO.POLLED_DAYS
POLLED_DAYS Settings for VAR_VALUE and VAR_VALUE2
The default is 14 days.
The transaction total tender value for reporting ‘sales less than threshold’. See “Configuring Sales Less than Threshold Variable, Setting and Managing Threshold Groups” for more information.
XBRSTATS.SALES_THRESHOLD
SALES_THRESHOLD Settings for VAR_VALUE and VAR_VALUE2
Core default is 5
The No Match process is performed using the SP_PRO_NOMATCH_RETURNEXCH procedure, which is run from within the SP_PRO_LOAD_HIST procedure. This procedure looks for original purchase transactions related to refunds and exchanges. Based on the results of these lookups, Match Codes are assigned.
NOMATCH.PROCESS_NM_RETURNEXCH
PROCESS_NM_RETURNEXCH Settings for VAR_VALUE and VAR_VALUE2:
If original transaction STORE, TRANSNUM, REGNUM & DATE for returns are in tlog, then set to Y, else N.
Note: If a customer has more than one POS and one POS captures original transaction information for returns and the other POS does not, this must be discussed with project manager. If Return No Match is enabled, the system will report a lot of false positives for the POS that does not capture original transaction information.
The No Match process is performed using the SP_PRO_NOMATCH_PVCANCEL procedure, which is run from within the SP_PRO_LOAD_HIST procedure. These procedures look for subsequent re-ring transactions related to post voids and cancels. Based on the results of these lookups, Match Codes are assigned.
NOMATCH.PROCESS_NM_PVCANCEL
PROCESS_NM_PVCANCEL Settings for VAR_VALUE and VAR_VALUE2:
If the tlog has Post Voids and/or Cancels, set to Y, else N.
This variable is indicated in the POS Questionnaire. Confirm them from the content of the customer’s tlog.
NOMATCH.CAPTURE_ORIG_REGNUM
CAPTURE_ORIG_REGNUM Settings for VAR_VALUE and VAR_VALUE2:
If the tlog captures the original register number for returns, set to Y, else N.
Note: if this were a real time environment there would be two places to modify the view.
The number of minutes to look forward to see if a SKU in a post-voided transaction was re-rung.
NOMATCH.PV_MINS
PV_MINS Settings for VAR_VALUE and VAR_VALUE2:
Core default is 15.
The number of minutes to look forward to see if a SKU in a cancelled transaction was re-rung.
NOMATCH.CANCEL_MINS
CANCEL_MINS Settings for VAR_VALUE and VAR_VALUE2:
Core default is 15.
To enhance data minimization for personal data, a purge process is configurable for the deletion of inactive Customer, Employee and Store personal data. The application will delete data considered to be personal data in the database, such as customer and ship to names, addresses, email addresses, etc. The purge routine is added to SP_ETL_XFINISH_BATCH and real time end of day SP_ETL_XFINISH_EOD procedures. Settings in pro_sp_variables enable this to be turned on when the active flag is set to Y and reaches the number of days defined. Administrator users can use the Data Editor – System Variable Settings table to manage these settings. By default, the active flag is set to Y, and the default number of days setting is 370 days for each:
CUSTOMER_INACTIVE_DAYS - based on number of days since the transaction date that is associated with a customer number. Customer First / Last Name, Shipping Address, Email Address, Shipping First / Last Name, Phone Number, Zip code, State, Country will be deleted from transaction history.
EMPLOYEE_TERMINATED_DAYS - based on number of days since the termination date set in the employee master file. Employee first name, last name, federal ID, employee image, and salesperson image data will be deleted from the employee master file.
STORE_CLOSED_DAYS - based on number of days since the closed date in the store master file. Manager name and email address will be deleted from the store master file.
Sales Less Than Threshold is an edit criterion for detecting and reporting sale transactions with a non-zero tender amount below a defined threshold value. Administrator users can use the Data Editor – System Variable Settings table to manage these settings.
The system default value for Sales Less Than Threshold is stored in PRO_SP_VARIABLES table with the following settings:
SYSTEM = ‘XBRSTATS’,
VAR_NAME = ‘SALES_THRESHOLD’
Threshold value in VAR_VALUE (initially set to 5).
In XBRi, sales threshold value can be set by country (threshold basis) and multiple countries can be mapped to a single threshold value (threshold group). Threshold groups provide a single point for changing the threshold values of all the countries in the group. Countries with the same currency could logically be in the same threshold group. There is no reporting by threshold group. The threshold basis of country can be changed to another field in the Store Master, such as state or district. Multiple bases for threshold groups cannot be chosen; for example,: calculating Sales Less Than Threshold for both Region and District is not allowed. Only one (1) threshold group basis should be implemented.
_____________________________
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Legal Notices