1 UtamaPOS

COMMERCIAL IN CONFIDENCE

The 1Utama Point of Sale System Technical Details for POS/IT Vendor v1.4

Prepared by BUCC Updated on November 4, 2010

Copyright © 2006 - 2010 Bandar Utama City Centre Sdn Bhd. Page 1 of 6


Electronically upload Daily Sale Data to 1Utama’s Server Q1: What kind of PUSH file format is BUCC looking for? A1: Unfortunately we DO NOT accept any file format type. Please do not email any excel or txt file.  Extract required data fields and insert them into 1U’s DB.  BUCC technical team will open a Database table for each tenant. Tenant’s vendor (or IT team) should create an executable file (.exe) running on tenant’s local machine (or server). The .exe should execute according to the time allowance stated in the tenancy agreement.  BUCC is using SQL Server Standard Edition 2005. Tenants are free to use any DB. We DO NOT restrict any tenant from using any type of DB.  The Microsoft Windows’ “Scheduled Tasks” in Control Panel will allow you to do the scheduled task.  Each tenant will be assigned a connection String that consists of Server IP, Database name, Table name, Username & Password to access the database table of that particular tenant. For example, to connect to BUCC SQL server: Connecting String: data source=192.168.20.2; userid=myusername;password=mypwd;database=mydbname;persist security info=true; Q2: Will the push program interrupt my existing POS system? A2: No. It will not interrupt the existing POS  There’s no change to existing POS system. The only change could be on its’ sale table. Tenant’s vendor should add another column/field to its sale table called, e.g. “uploaded”. The “uploaded” column will have values of “0” or “1”. Zero means the record is yet to send to BUCC server, while “1” means the record is already sent. When the .exe is running, it will only read sale records with flag=0 and push them to BUCC’s DB table via the connecting string given as mentioned above. Subsequently, once record is uploaded, the .exe should update the flag from “0” to “1” on your table. This is to prevent any missing data.

Q3: What if my POS doesn’t have tax_percent, tax_amount, service_charge_percent, and service_charge_amount fields? A3: Leave those fields as NULL/EMPTY. Refer to Q5 below.



Q4: Do we send the daily Sales Total only in one single transaction? If we were to send every transaction, I assume that we only send completed transaction (for F&B, table is still OPEN until customers leave)? A4: No, BUCC needs sales total of each transaction, not End of Day (EOD) total. We do not need to know the details (item name, item ID, Cost price…etc) of the item sold. Yes, only send completed or voided transactions. For F & B, if that particular table is still occupied, that transaction can be sent later. Q5: Not very clear with the Datafield, what is the Data Dictionary? 1. * * * *

sale_id An unique ID of each sale transaction. (Primary Key) Data Type: nvarchar Length: 50 Allow Nulls: No

2. * * * *

sale_datetime Date and time of sale transaction Data Type: datetime Length: 8 Allow Nulls: No

3. * * *

sale_status It could be either 'closed' or 'void' Indicator of the sale transaction In our case: 'closed'= completed sale ONLY!; 'void'=refer to void sale transaction Data Type: nvarchar Length: 50 Allow Nulls: No

* * * 4. * * * *

sub_total Total amount of item purchased before GST or Service Charge or Grand Discount. Data Type: money Length: 8 Allow Nulls: Yes

5. * * * *

discount_percent Grand discount of sub_total calculated in percentage Data Type: float Length: 8 Allow Nulls: Yes

6. * * *

discount_amount Grand discount of sub_total calculated in $ Data Type: money Length: 8



*

Allow Nulls: Yes

7. * * * *

tax_percent 5% GST of sub_total calculated in percentage Data Type: float Length: 8 Allow Nulls: Yes

8. * * * *

tax_amount 5% GST of sub_total calculated in $ Data Type: money Length: 8 Allow Nulls: Yes

9. * * * *

service_charge_percent (this field only applicable to F&B tenants) 10% Service Charge of sub_total calculated in percentage Data Type: float Length: 8 Allow Nulls: Yes

10. service_charge_amount (this field only applicable to F&B tenants) * 10% Service Charge of sub_total calculated in $ * Data Type: money * Length: 8 * Allow Nulls: Yes 11. grand_total * grand_total= sub_total + tax_amount + service_charge_amount – discount amount * Data Type: money * Length: 8 * Allow Nulls: No

Q6: Is the PUSH function supposed to run in an interval? Daily? Hourly? A6: You may push the data in several batches in a day. OR, if you chose to push all data at the end of the day, please make sure it reaches the server before midnight, 12am. Q7: What is the SQL Script? A7: For Retail, If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[posmaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[posmaster] GO CREATE TABLE [dbo].[posmaster] ( [sale_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [sale_datetime] [datetime] NOT NULL , Copyright © 2006 - 2010 Bandar Utama City Centre Sdn Bhd. Page 4 of 6


[sale_status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [sub_total] [money] NULL , [discount_percent] [float] NULL , [discount_amount] [money] NULL , [tax_percent] [float] NULL , [tax_amount] [money] NULL , [grand_total] [money] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[posmaster] ADD CONSTRAINT [PK_posmaster] PRIMARY KEY ( [sale_id] ) ON [PRIMARY] GO

CLUSTERED

A7: For F&B, If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[posmaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[posmaster] GO CREATE TABLE [dbo].[posmaster] ( [sale_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [sale_datetime] [datetime] NOT NULL , [sale_status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [sub_total] [money] NULL , [discount_percent] [float] NULL , [discount_amount] [money] NULL , [tax_percent] [float] NULL , [tax_amount] [money] NULL , [service_charge_percent] [float] NULL , [service_charge_amount] [money] NULL , [grand_total] [money] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[posmaster] ADD CONSTRAINT [PK_posmaster] PRIMARY KEY ( [sale_id] ) ON [PRIMARY] GO

CLUSTERED



Q8: I am running on Linux, .exe does not work on Linux. What should I do to push the data? A8: EXE is just an example we suggested, you may write the PUSH function in any form. As long as it works on your platform and we are able to receive on our receiving end. Q9: Does the outlet’s DB need to stay connected to 1U’s DB 24/7? A9: No. The program you are going to write will extract the data from your DB and insert them into our DB. Once the operation is done, you may disconnect the VPN dial up. Q10: My push program said it has successfully uploaded the date, but how come 1U did not receive them? A10: First, you might want to check if you are connected to our network via the VPN login which we provided to you. If no, please inform 1U’s IT dept and have them check the username and password again. If yes, then you may do a simple test by creating a file, e.g. test.udl. Click the TEST.UDL file, on the first tab, make sure you select “Microsoft OLE DB Provider for SQL Server”. On the second tab, key in the IP add, Username, and Password provided to you. Click on the dropdown icon, if you are seeing the DB and table name, it means you are connected to the Server. Check your upload source code again. Q11: My cashier will need to select a date and press SEND every day? A11: It really depends on how the IT vendor writes the program. We recommend the push data program should be written and schedule to run automatically. Which means it is all running on the backend and the cashier does not have to do anything.


1 UtamaPOS

Recommend Documents