EEE SummarySales table definition

From EnablerWiki

Created: 21 July 2009. Last Reviewed: 21 July 2009

Below is the structure of the SummarySales table and an explanation of where each field comes from.

Structure of SummarySales

Fld	Field Name/Definition	Populated From
1	BranchCode TBranchCode,	SaleLine.BranchCode
2	ProductCode TProductCode,	SaleLine.ProductCode
3	CountryCode TRegionCode NULL,	Branch.CountryCode
4	State TState NULL,	Branch.State
5	RegionCode TRegionCode NULL,	Branch.RegionCode
6	SubregionCode TRegionCode NULL,	Branch.SubregionCode
7	StoreName TDescription NULL,	Branch.Description
8	Anal1 TAnalysisCode NULL,	Product.Analysis1Code
9	Anal2 TAnalysisCode NULL,	Product.Analysis2Code
10	Anal3 TAnalysisCode NULL,	Product.Analysis3Code
11	Anal4 TAnalysisCode NULL,	Product.Analysis4Code
12	Anal5 TAnalysisCode NULL,	Product.Analysis5Code
13	Anal6 TAnalysisCode NULL,	Product.Analysis6Code
14	Anal7 TAnalysisCode NULL,	Product.Analysis7Code
15	Anal8 TAnalysisCode NULL,	Product.Analysis8Code
16	Anal9 TAnalysisCode NULL,	Product.Analysis9Code
17	Anal10 TAnalysisCode NULL,	Product.Analysis10Code
18	Brand TAnalysisCode NULL,	Product.BrandCode
19	StyleCode TAnalysisCode NULL,	Product.StyleCode
20	ColourCode TAnalysisCode NULL,	Product.ColourCode
21	SizeCode TAnalysisCode NULL,	Product.SizeCode
22	StyleDescription TDescription NULL,	Style.Description
23	ColourDescription TDescription NULL,	Colour.Description
24	SizeDescription TDescription NULL,	Size.Description
25	SupplierCode TSupplierCode NULL,	Product.Supplier1
26	SupplierName TName NULL,	Supplier.Name
27	UnitsDay TQuantity NULL default 0,	Note 1
28	UnitsWTD TQuantity NULL default 0,	Note 2
29	UnitsLW TQuantity NULL default 0,	Note 3
30	UnitsW2 TQuantity NULL default 0,	Note 3
31	UnitsW3 TQuantity NULL default 0,	Note 3
32	UnitsW4 TQuantity NULL default 0,	Note 3
33	Units4WkAve TQuantity NULL default 0,	Note 4 (Computed Column)
34	UnitCost Money NULL default 0,	Cost.BuyPrice1 – I.e. current list price for branch
35	UnitWAC Money NULL default 0,	Cost.WAC – I.e. current WAC for the branch
36	UnitSell Money NULL default 0,	Price.RetailPrice – I.e. current full selling price for branch
37	TotalSalesDay Money NULL default 0,	Note 5
38	TotQtyDay	Note 6
39	TotSellDay	Note 6
40	TotQtyTot	Note 7
41	TotSellTot	Note 7
42	AveSellDay Money NULL default 0,	Note 6 (Computed Column)
43	AveSellTot Money NULL default 0,	Note 7 (Computed Column)
44	GPPercToday numeric(9,2) NULL default 0,	Note 8 (Computed Column)
45	GPPercWTD numeric(9,2) NULL default 0,	Note 8 (Computed Column)
46	GPPercLW numeric(9,2) NULL default 0,	Note 8 (Computed Column)
47	GPPercTotal numeric(9,2) NULL default 0,	Note 8 (Computed Column)
48	SOH TQuantity NULL default 0,	Note 9
49	SOHLayby TQuantity NULL default 0,	Note 10
50	LastSoldDate datetime NULL,	Note 11
51	LastRecvDate datetime NULL,	Note 12
52	SOHLW TQuantity NULL default 0,	Note 13
53	NetSalesDay Money NULL default 0,	Note 14
54	NetSalesWTD Money NULL default 0,	Note 14
55	NetSalesLW Money NULL default 0,	Note 14
56	NetSalesW2 Money NULL default 0,	Note 14
57	NetSalesW3 Money NULL default 0,	Note 14
58	NetSalesW4 Money NULL default 0,	Note 14
59	GPDay Money NULL default 0,	Note 15
60	GPWTD Money NULL default 0,	Note 15
61	GPLW Money NULL default 0,	Note 15
62	GPW2 Money NULL default 0,	Note 15
63	GPW3 Money NULL default 0,	Note 15
64	GPW4 Money NULL default 0	Note 15
65	TransferInQtyDay TQuantity null default 0	Note 16
66	TransferOutQtyDay TQuantity null default 0	Note 16
67	CommittedPOQty TQuantity null default 0	Note 17
68	UnCommittedPOQty TQuantity null default 0	Note 18
69	InBoundStockQty TQuantity null default 0	Note 19
70	SupplierStockCode TProduct NULL	Product.Supplier1Code
71	UnitDutyFreePrice money NULL default 0	Price.DutyFreePrice
72	UnitsEver TQuantity NULL default 0	Note 3
73	NetSalesEver Money NULL default 0	Note 14
74	GPEver Money NULL default 0	Note 15
75	GPPercEver	Note 8 (Computed Column)
76	WeeksCover	Note 20 (Computed Column)
77	FirstSoldDate datetime NULL,	Note 21
78	FirstRecvDate datetime NULL	Note 22
79	EarliestPODueDate datetime NULL	Note 23
80	LatestPODueDate datetime NULL	Note 23
81	WeeksSoldFor int NULL	Note 24 (Computed Column)
82	SuperProduct bit NOT NULL DEFAULT 0	Note 25
83	SubProduct bit NOT NULL DEFAULT 0	Note 25
84	ProductDescription TDescription NULL	Product.Description

Field Notes

Note 1

Sum of SaleLine.Quantity for all records for target date. I.e. units sold net of units returned.

Note 2

Sum of SaleLine.Quantity for all records for week to date (00:00:00 Sunday morning up until and including target date). I.e. units sold net of units returned.

Note 3

Sum of SaleLine.Quantity for all records for relevant week. I.e. units sold net of units returned.

Note 4

Calculated: (UnitsLW+UnitsW2+UnitsW3+UnitsW4) / 4

Note 5

Not Populated – Unused. Refer Field 49. Field 37 to be dropped in final version?

Note 6

Total units sold for the day, total sales value for the day. Use to calculate computed column AveSellDay – average selling price for the day which is sum of sales value for day divided by units sold for the day. Sales value is net of discounts and inclusive of GST and expressed in the local currency of the store. Only considers records with quantity > 0 (i.e. not returns).

Note 7

Total units sold for the entire period (i.e. 4 weeks plus current WTD), total sales value for entire period. Use to calculate computed column AveSellTot – average selling price for the period which is sum of sales value for period divided by units sold for the period. Sales value is net of discounts and inclusive of GST and expressed in the local currency of the store. Only considers records with quantity > 0 (i.e. not returns).

Note 8

Gross Profit for the period^* divided by Net sales for the day. Gross Profit and Net Sales defined later in notes 14 & 15.

^* Period is the day, WTD, LW etc according to the specific field.

Note 9

Total units on hand – includes stock available for sale AND stock reserved for laybys. Sum of Inventory.Quantity for ProductCode/BranchCode.

Note 10

Stock units in layby stock bin – i.e. depending on configuration this should equate to number of units currently on layby. Populated from Inventory.Quantity for ProductCode/BranchCode and StockLocationCode=’LAYBY’ rather than from layby tables. ‘LAYBY’ can be customised to suit configuration via the @lbbin variable defined at the beginning of the stored procedure.

Note 11

Date/time of the most recent sale assuming this sale occurred within the 4.x week period analysed in the summary table. Otherwise NULL. Max(SaleLine.SaleDate) for ProductCode/BranchCode.

Note 12

Date/time of most recent stock receipt of the product at the branch, regardless of how long ago this occurred. Max(StockMovement.Date) where Type = 2 (Goods Receipt) or Type = 4 (Stock Transfer) and where goods movement was inwards / positive.

Note 13

Total stock on hand as at end of last week including stock in layby bin. Calculated by adjusting current SOH (field 44) by net stock movements this week to date.

Note 14

Net sales for the period^* is defined as the sale amount net of discounts, exclusive of GST and expressed in the local currency of the store making the sale. These fields are used in the GP percentage calculations.

^* Period is the day, WTD, LW etc according to the specific field.

Note 15

GP for the period^* is the dollar amount of gross profit as calculated at POS and uploaded to H/O. Therefore depends on stores being correctly configured to use WAC for GP calculations and depends on store databases being correctly populated with WAC. GP is expressed in the local currency of the store making the sale.

^* Period is the day, WTD, LW etc according to the specific field.

Note 16

Quantity transferred in/out for target date. This is the sum of StockLed.Quantity for transaction types 4 (transfers) and 8 (transfers). Transfer in/out is determined by the sign of the quantity (negative quantity is a transfer out). Note that transfer outs may result from Head Office EEE transfers out, Head Office EEE Replenishments, or Head Office EEE Manual Stock Distributions.

Note 17

Quantity on committed purchase orders. This purchase orders may be Store-generated purchase orders or central purchase orders (ie generated by Head Office on behalf of stores).

For purchase orders which are to be delivered to the warehouse (without store distribution) they are counted in the totals for the warehouse branch. For purchase orders which are to be delivered to the warehouse (with store distribution) and purchase orders which are to be delieved direct to stores, the quantities are tabulated according to the final destination branch, ie not the warehouse.

Note 18

Quantity on uncommitted, centralised purchase orders. Typically, these are purchase orders that are still in the process of being created/edited before being committed and sent to suppliers. For purchase orders which are to be delivered to the warehouse (without store distribution) they are counted in the totals for the warehouse branch. For purchase orders which are to be delivered to the warehouse (with store distribution) and purchase orders which are to be delieved direct to stores, the quantities are tabulated according to the final destination branch, ie not the warehouse.

Note 19

Quantity of stock which has been transferred out from another store or Head Office and which is in transit to the store tabulated. Note that this will not include stock inbound directly from suppliers, resulting from central purchase orders. It will include: stock from Head Office EEE replenishments, Head Office EEE manual stock distributions, and Head Office EEE transfers.

Note 20

Weeks cover = SOH / (UnitsLW+UnitsW2+UnitsW3+UnitsW4) * 4, Rounded to the nearest week. Set to 9999 if no units sold in last four weeks, or if formula produces a result > 9999. Set to zero if there is zero (or negative) stock.

Note 21

Date/time of the first ever sale. Otherwise NULL. MIN(SaleLine.SaleDate) for ProductCode/BranchCode.

Note 22

Date/time of first ever stock receipt of the product at the branch, regardless of how long ago this occurred. MIN(StockMovement.Date) where Type = 2 (Goods Receipt) or Type = 4 (Stock Transfer) and where goods movement was inwards / positive.

Note 23

Date/time of the nearest future delivery due date on products within EEE central purchase orders (or furthest into the future). Note that a purchase order of delivery type to warehouse with distribution will be tabulated according to the branches in the distribution rather than the warehouse. Only components of purchase orders which are outstanding are considered.

Note 24

Number of weeks since first sale of the product at the branch, rounded to the nearest week.

Note 25

A SuperProduct is a product which is sold as a kit, or as a multi-pack. A SubProduct is one which can be sold as a component of a kit or is an item within a multi-pack. This is determined as follows: Products which are in KitDefinition.KitProductCode are SuperProducts, and those which are in KitDefinition.ComponentProductCode are SubProducts.

Magenta / EEE Internal SQL Data Types

TRegionCodevarchar(20)

TStatechar(5)

TAnalysisCodevarchar(20)

TDescriptionvarchar(60)

TNamevarchar(60)

TProductCodevarchar(25)

TBranchcodevarchar(20)

TQuantitynumeric(12,6)

TSupplierCodevarchar(20)