Business To Business (BTB)
Home/Retail Delivery System - Documentation
For Glen Dimplex Home Appliances (GDHA)
TRADE SECRET AND CONFIDENTIAL
All intellectual property rights including copyright subsisting in this work are owned or controlled by Computer Software Group Plc (CSG). This work and any information it contains is submitted for the purpose referenced within. It is to be treated as confidential and shall not be used for any other purpose. The work shall not be copied or disclosed to third parties in whole or in part without the prior written consent of CSG. All Trademarks are hereby acknowledged.
Author:Maurice O’Prey (Computer Software Group)
Email:
Telephone:+44(0)-113-293-8652
Version:1.2
Last Amended:3rd February 2006
Table of Contents
1Introduction
1.1Who’s Who | GDHA
1.2Who’s Who | CSG
2Overview
3GEAC System/21 Order Mappings
3.1Order Headers (XXCOP100 mappings to OEP40)
3.2Order Details (XXCOP200 mappings to OEP55)
4Customer/Product Database
5SQL Server DTS Package Mappings
5.1TP0010e - Customers
5.2TP0020e - Products
5.3TP0030e – Delivery Dates
5.4TP0050e – Delivery Addresses
5.5TP_ALL – All Tables
6TradePlace Auto Login
7Version Control
1Introduction
This document provides summary documentation for the Glen Dimplex BTB Home and Retail Delivery System. This document does NOT attempt to document the entire system; rather it provides basic technical details relating to some of the interfaces and features of the system.
All questions and queries relating to this document should be directed to .
1.1Who’s Who | GDHA
Name / Position / Email / Contact NumberJon Colley / IT Manager / / 0870 444 9919
John Long / EDI Analyst Programmer / / 0870 428 3472
1.2Who’s Who | CSG
Name / Position / Email / Contact NumberMaurice O’Prey / EBusiness Consultant / / 0113 293 8652
Rob Such / Project Manager / / 01270 613 800
2Overview
The GDHA BTB application allows online ordering of goods via the Internet for two categories of user,
Home Deliveries-A customer address must be entered
Retail Deliveries-A store address must be selected
The application is staged on a W2K server located at GDHA. AS/400 Customer and product data is extracted to an in-house written database. This data is then synchronised to the web site using DTS packages. The BTB ordering system contains NO real-time calls to the AS/400 and is driven entirely by a SQL Server database.
Orders are output on the server as XML documents and are periodically collected by the TALENT Engine. The orders are transferred to files XXCOP100/XXCOP200 and then transferred into System/21 via program GDB_GEAC_R. This program also processes the real-time orders placed via the TradePlace system (a separate project delivered by CSG).
The application is an ASP.NET system written in VB.NET and is based on the .NET framework V2.0
3GEAC System/21 Order Mappings
BTB orders (only) are processed by adapter program GDB_GEAC_Z (which is a modified version of GDH_GEAC_R)
Mapping details are as follows
TALENTGEAC
XXCOP100OPE40
XXCOP200OPE55
XXCOP300OEP45 (not used for BTB orders but mappings left in place)
3.1 Order Headers (XXCOP100 mappings to OEP40)
XXCOP100 / OEP40C1CUS / CUSN40 / Customer Number e.g. S40046
C1USR / Not mapped / Customer Number e.g. S40046
C1TIT / ? / Title e.g. Mr
C1FNM / ? / First Name e.g. Maurice
C1SNM / ? / Surname e.g. O’Prey
C1NAM / Not Used / Company Name
C1AD1 / Not Used / Address 1
C1AD2 / Not Used / Address 2
C1AD3 / Not Used / Address 3
C1AD4 / Not Used / Address 4
C1AD5 / Not Used / Address 5
C1PCD / Not Used / Post Code
C1CCD / Not Mapped / Country Code – defaults to GB
C1ORD / P#ORD / Order Number – e.g. A1055
C1ODT / DTCO50 / Order Entry Date – defaults to today’s date YYYY-MM-DD
C1SCD / Not Used / Shipping Code
C1SCS / Not Used / Shipping Cost
C1SHP / DSEQ## / Ship To Number – e.g. 004 (for retail customers only)
C1COR / Not Used / Customer Order Number
C1CDT / DTDR52 / Scheduled Shipment Date – defaults to today’s date YYYY-MM-DD
C1OTP / Not Mapped / Order Type = BTB
C1WHS / Not Mapped / Warehouse
C1TX1 / ? write to inp40 ? / Special Instructions 1 (First 30 characters of instructions)
C1TX2 / ? write to inp40 ? / Special Instructions 2 (second 30 characters of instructions)
C1PRM / Not Used / Promotion Code
C1PCS / Not Used / Pieces Count
C1ORM / Not Mapped / Order Method = BTB
C1POA / Not Used / Produce Acknowledgement = N
C1OAP / Not Used / Acknowledgement Produced = N
C1CUR / Not Mapped / Currency = GBP
C1PYT / Not Used / Payment Terms
C1STS / Set in GDB_GEAC_Z / Order Status = Blank
C1BOR / Set in GDB_GEAC_Z / JBA Order Number – Set by adapter program and updated to XXCOP100
C1TEL / Write to inp40 ? / Mobile Number – Holds home users mobile number
C1PON / CUSO50 / Customer PO Number – Retail Customers only
3.2 Order Details (XXCOP200 mappings to OEP55)
XXCOP200 / OEP55C2CUS / CUSN55 / Customer Number e.g. S40046
C2SHP / Not Mapped / Ship To Number
C2USR / Not Mapped / Customer Number e.g. S40046
C2CTL / Not Mapped / Application Code = GDB
C2GRP / Not Used / Product Group
C2CAT / Not Used / Product Category
C2SUB / Not Used / Product Subcategory
C2PRD / CATN52 / Product Code e.g. 016625
C2DSC / Not Mapped / Product Description
C2QTY / QTOR52 / Order Qty e.g. 1
C2PRC / Not Mapped / Unit Price e.g. 346.64
C2ORD / Not Mapped / Order Number e.g. A1055
C2LIN / POLE52 / Line Number – e.g. 1
C2PRF / Not Mapped / Processed Flag – Updated to Y by adapter program
C2SPC / Used to hold delivery date ? / Special Instructions
C2WGT / Not Used / Weight
C2LTR / Not Used / Litres
C2NAM / ? / Ship To Name e.g. Mr Maurice O’Prey
C2AD1 / ? / Ship To Address 1 e.g. 10 OakwellGardens
C2AD2 / ? / Ship To Address 2 . e.g Roundhay
C2AD3 / ? / Ship To Address 3 e.g. Leeds
C2AD4 / ? / Ship To Address 4 e.g. West Yorkshire
C2AD5 / ? / Ship To Address 5 e.g. Blank
C2PCD / ? / Ship To Postcode e.g. LS8 1RR
C2CCD / Not Used / Country Code
C2CUR / Not Mapped / Currency
C2UOM / Not Mapped / Unit Of Measure
C2TEL / ? / Telephone number e.g. 0113 293 8652
C2EMA / ? / Email Address e.g.
C2CPR / ? / Used to hold flags as follows
Position 1 =Y/N – Installation
Postion 2 = Y/N – Removal
C2TAX / Not Used / Tax Value
4Customer/Product Database
The application is driven by an extract database (created and maintained by GDHA) that contains the following tables.
TP0010-Usernames and passwords
TP0020-Products
TP0030-Delivery dates
TP0050-Delivery addresses (stores)
5SQL Server DTS Package Mappings
There are 5 DTS packages provided. These are:
Package Name / FunctionTP0010e / Customers – Synchronises TP0010
TP0020e / Products – Synchronises TP0020
TP0030e / Delivery Dates – Synchronises TP0030
TP0050e / Delivery Addresse – Synchronises TP0050
TP_ALL / Runs all DTS packages
Each package (apart from TP_ALL) contains similar SQL statements (specific to the file they use). The SQL statements and steps are listed below.
5.1TP0010e - Customers
Step – CLEAR
delete from TP0010e
Step – DELETE
delete from TP0010
where not exists
( select * from TP0010E as e
where CONO01 = e.CONO01e
and TCUS01 = e.TCUS01e
)
Step – INSERT
insert into TP0010
select *
from TP0010e as e
where exists
(
select * from TP0010e left outer join TP0010 on CONO01 = CONO01e and TCUS01 = TCUS01e
where CONO01 is null
and CONO01e = e.CONO01e and TCUS01e = e.TCUS01e
)
Step – UPDATE
update TP0010 set
CONO01 = e.CONO01e,
TCUS01 = e.TCUS01e,
TCPW01 = e.TCPW01e,
CUSN01 = e.CUSN01e,
DSEQ01 = e.DSEQ01e,
OVRT01 = e.OVRT01e,
DFTF01 = e.DFTF01e,
CUSI01 = e.CUSI01e,
CUSR01 = e.CUSR01e
from TP0010e as e
where CONO01 = CONO01e and
TCUS01 = TCUS01e and exists
(
Select *
from TP0010e
where CONO01 = CONO01e and TCUS01 = TCUS01e
)
5.2TP0020e - Products
Step – CLEAR
delete from TP0020e
Step – DELETE
delete from TP0020
where not exists
( select * from TP0020E as e
where CONO02 = e.CONO02e
and CUSN02 = e.CUSN02e
and CATN02 = e.CATN02e
)
Step – INSERT
insert into TP0020
select *
from TP0020e as e
where exists
(
select * from TP0020e left outer join TP0020 on CONO02 = CONO02e and CUSN02 = CUSN02e and CATN02 = CATN02e
where CONO02 is null
and CONO02e = e.CONO02e and CUSN02e = e.CUSN02e and CATN02e = e.CATN02e
)
Step – UPDATE
update TP0020 set
CONO02 = e.CONO02e,
CUSN02 = e.CUSN02e,
CATN02 = e.CATN02e,
PTYP02 = e.PTYP02e,
DESC02 = e.DESC02e,
BARC02 = e.BARC02e,
ALTI02 = e.ALTI02e,
SAMI02 = e.SAMI02e,
SAMS02 = e.SAMS02e,
HSAV02 = e.HSAV02e,
RSAV02 = e.RSAV02e,
EDSC02 = e.EDSC02e,
RPRC02 = e.RPRC02e,
IAVL02 = e.IAVL02e,
RAVL02 = e.RAVL02e
from TP0020e as e
where CONO02 = CONO02e and
CUSN02 = CUSN02e and CATN02 = CATN02e and exists
(
Select *
From TP0020e
where CONO02 = CONO02e and CUSN02 = CUSN02e and CATN02 = CATN02e
)
5.3TP0030e – Delivery Dates
Step – CLEAR
delete from TP0030e
Step – DELETE
delete from TP0030
where not exists
( select * from TP0030E as e
where CONO03 = e.CONO03e
and CUSN03 = e.CUSN03e
and DSEQ03 = e.DSEQ03e
and PTYP03 = e.PTYP03e
and PCOD03 = e.PCOD03e
)
Step – INSERT
insert into TP0030
select *
from TP0030e as e
where exists
(
select * from TP0030e left outer join TP0030 on CONO03 = CONO03e and CUSN03 = CUSN03e and DSEQ03 = DSEQ03e and PTYP03 = PTYP03e and PCOD03 = PCOD03e
where CONO03 is null
and CONO03e = e.CONO03e and CUSN03e = e.CUSN03e and DSEQ03e = e.DSEQ03e and PTYP03e = e.PTYP03e and PCOD03e = e.PCOD03e
)
Step – UPDATE
update TP0030 set
CONO03 = e.CONO03e,
CUSN03 = e.CUSN03e,
DSEQ03 = e.DSEQ03e,
PTYP03 = e.PTYP03e,
PCOD03 = e.PCOD03e,
AD0103 = e.AD0103e,
AD0203 = e.AD0203e,
AD0303 = e.AD0303e,
AD0403 = e.AD0403e,
AD0503 = e.AD0503e,
AD0603 = e.AD0603e,
AD0703 = e.AD0703e,
AD0803 = e.AD0803e,
AD0903 = e.AD0903e,
AD1003 = e.AD1003e,
AD1103 = e.AD1103e,
AD1203 = e.AD1203e,
AD1303 = e.AD1303e,
AD1403 = e.AD1403e,
AD1503 = e.AD1503e,
AD1603 = e.AD1603e,
AD1703 = e.AD1703e,
AD1803 = e.AD1803e,
AD1903 = e.AD1903e,
AD2003 = e.AD2003e,
ID0103 = e.ID0103e,
ID0203 = e.ID0203e,
ID0303 = e.ID0303e,
ID0403 = e.ID0403e,
ID0503 = e.ID0503e,
ID0603 = e.ID0603e,
ID0703 = e.ID0703e,
ID0803 = e.ID0803e,
ID0903 = e.ID0903e,
ID1003 = e.ID1003e,
ID1103 = e.ID1103e,
ID1203 = e.ID1203e,
ID1303 = e.ID1303e,
ID1403 = e.ID1403e,
ID1503 = e.ID1503e,
ID1603 = e.ID1603e,
ID1703 = e.ID1703e,
ID1803 = e.ID1803e,
ID1903 = e.ID1903e,
ID2003 = e.ID2003e
from TP0030e as e
where CONO03 = CONO03e and
CUSN03 = CUSN03e and DSEQ03 = DSEQ03e and PTYP03 = PTYP03e and PCOD03 = PCOD03e and exists
(
Select *
from TP0030e
where CONO03 = CONO03e and CUSN03 = CUSN03e and DSEQ03 = DSEQ03e and PTYP03 = PTYP03e and PCOD03 = PCOD03e
)
5.4TP0050e – Delivery Addresses
Step – CLEAR
delete from TP0050e
Step – DELETE
delete from TP0050
where not exists
( select * from TP0050E as e
where CONO50 = e.CONO50e
and CUSN50 = e.CUSN50e
and DSEQ50 = e.DSEQ50e
)
Step – INSERT
insert into TP0050
select *
from TP0050e as e
where exists
(
select * from TP0050e left outer join TP0050 on CONO50 = CONO50e and CUSN50 = CUSN50e and DSEQ50 = DSEQ50e
where CONO50 is null
and CONO50e = e.CONO50e and CUSN50e = e.CUSN50e and DSEQ50e = e.DSEQ50e
)
Step – UPDATE
update TP0050 set
CONO50 = e.CONO50e,
CUSN50 = e.CUSN50e,
DSEQ50 = e.DSEQ50e,
CNAM50 = e.CNAM50e,
CAD150 = e.CAD150e,
CAD250 = e.CAD250e,
CAD350 = e.CAD350e,
CAD450 = e.CAD450e,
CAD550 = e.CAD550e,
PCD150 = e.PCD150e,
PCD250 = e.PCD250e
from TP0050e as e
where CONO50 = CONO50e and
CUSN50 = CUSN50e and DSEQ50 = DSEQ50e and exists
(
Select *
from TP0050e
where CONO50 = CONO50e and CUSN50 = CUSN50e and DSEQ50 = DSEQ50e
)
5.5TP_ALL – All Tables
TP_ALL simply executes the above DTS packages as a group.
6TradePlace Auto Login
The application allows TradePlace users to bypass the login screen as follows;
1/.TP send a GET registration check to the BTB application containing the username and password in the query string;
2/.The BTB application validates the username and password and if OK returns the plain text;
Check=UserOk
ExtraData=None
If the user credentials are invalid the following text is returned
Check=UserUnknown
ExtraData=None
3/.TP send ticket request containing only the user name;
4/.The BTB application validates the username and OK generates and returns a ticket as the plain text;
Ticket=00064004skypt5d
Note – The ticket number comprises of the username (reversed) appended with a security code.
5/.TP then send a GET to enter the application including the user name and ticket number in the query string. These are validated and if OK the user is auto logged in to the BTB system.
7Version Control
This document is on the company electronic storage system.
Version / Date / By / Notes1.0 / 31st January 2006 / Maurice O’Prey / Version 1.0
1.1 / 2nd February 2006 / Maurice O’Prey / Mappings added
1.2 / 3rd February 2006 / Maurice O’Prey / SQL DTS mappings added
** End Of Document **
GDHA BTB Application / Version 1.2Computer Software Group / Page 1 of 16