Generating POS Bill Data & POS Bill Item Data Reconcile Automation for EasyRewardz

There are two reasons for which POS bill data from GINESYS may have to be shared with Easyrewardz -

• Internet failure – Very often due to network problem, during the registration process, the customer’s information data from POS bill (point accrual/redemption) fails to share with the Easyrewardz website, in spite of the data being present in the GINESYS local database. This data mismatch creates a lot of problem later on.

• POS Loyalty Data - Need to capture loyalty data from in POS Bill for data analytics purpose.

Therefore, POS bill data from GINESYS needs to be shared with Easyrewardz. This article describes how to generate POS Bill Data and reconcile automation for Easyrewardz.

Important

Note: This can be executed in MS-OO-CM, MS-CO-CM and MS-CO-OM-TS sites only. POS bill data is to be considered store wise and only the settled data (till the last settled date) has to be converted into a .CSV file and executed.

Prerequisites

Easyrewardz must be integrated in the system.
User should be able to run patches and job.

Please watch the following Powerpoint Presentation and follow the steps given it it to ensure a smooth and proper functioning of ER in your retail stores

Step-by-step guide

The process is divided into the following steps:

Step1: Create working directory

Create a new Folder in C:\ or D:\ Drive e.g. C:\ER_SALE_FILE
To make the above mentioned directory as your working directory for generating the required .csv file, run the following script in your database.
Script to create working directory

DROP DIRECTORY ER_WORKING_DIR;
CREATE OR REPLACE DIRECTORY
ER_WORKING_DIR AS
'C:\ER_SALE_FILE';

Step2: Capture last POS Bill sync event information

We have introduced a new procedure in Ginesys HO Version 11.131.5, which will capture Site wise Last POS Bill sync event information.
Users of versions below 11.131.5 can run this procedure manually.

Script to capture last POS Bill sync event

CREATE TABLE ER_LASTPOSBILLSYNCINFO
(
ADMSITE_CODE NUMBER (12) NOT NULL ,
LAST_STLMDATE DATE
);

ALTER TABLE ER_LASTPOSBILLSYNCINFO
ADD (CONSTRAINT ER_LASTPOSBILLSYNCINFO_R01 FOREIGN KEY (ADMSITE_CODE)
REFERENCES ADMSITE (CODE));

Step 3: Procedures to generate POS Bill Data .csv and POS Bill Item Data.csv file

We have introduced two new procedures in Ginesys HO Version 11.131.5, which will generate .csv file on last POS settlement date wise POS Bill and POS Bill Item data for EasyRewardz.
Users of versions below 11.131.5 can run this procedure manually.

Name format of the .csv file generated from the ER_Sales Data procedure is given below -

File Name for POS Bill Data: 'ER_GINPOS_Sale_DDMMRRRR_HHMMSS.csv';

Name format of the .csv file generated from the ER_Sales Item Data procedure is given below -

File Name for POS Bill Item Data: 'ER_GINPOS_Sale_Item_DDMMRRRR_HHMMSS.csv';

Step 4 : Schedule Jobs for generating Incremental POS Sale Data and POS Sale Item Data

We need to run two schedule job procedures on HO Database to automate the.csv file generating process.(These Jobs will find the site wise records of settled POS Bills and POS Billi Items from the last settled date to the day before the report generation date to generate the respective .csv files).

Important

Note:

In case, there are multiple unsettled dates within respective date range, the report will fetch records till the last available settled date, only the settled POS bill records from the POS stores will be used to create the CSV file.
This would also handle the scenario, where POS bills of multiple days were not synced to HO due to absence of internet.
In case, there are no settled records of POS bill within the respective period, no POS bill would be recorded in the CSV file. An empty CSV file should be created.
Once the site wise settled POS bill records are published in the CSV file, the site wise last POS settlement date get updated in the table - ER_LASTPOSBILLSYNCINFO.

To go back to the main page click on the link: Managing Loyalty Benefit & Card in Ginesys Retail