View source for EEE SummarySales table definition

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'''


{| class="prettytable"
| <center>'''Fld'''</center>
| '''Field Name/Definition'''
| '''Populated From'''

|-
| <center>1</center>
| BranchCode TBranchCode,
| SaleLine.BranchCode

|-
| <center>2</center>
| ProductCode TProductCode, 
| SaleLine.ProductCode

|-
| <center>3</center>
| CountryCode TRegionCode NULL,
| Branch.CountryCode

|-
| <center>4</center>
| State TState NULL,
| Branch.State

|-
| <center>5</center>
| RegionCode TRegionCode NULL,
| Branch.RegionCode

|-
| <center>6</center>
| SubregionCode TRegionCode NULL,
| Branch.SubregionCode

|-
| <center>7</center>
| StoreName TDescription NULL,
| Branch.Description

|-
| <center>8</center>
| Anal1 TAnalysisCode NULL,
| Product.Analysis1Code

|-
| <center>9</center>
| Anal2 TAnalysisCode NULL,
| Product.Analysis2Code

|-
| <center>10</center>
| Anal3 TAnalysisCode NULL,
| Product.Analysis3Code

|-
| <center>11</center>
| Anal4 TAnalysisCode NULL,
| Product.Analysis4Code

|-
| <center>12</center>
| Anal5 TAnalysisCode NULL,
| Product.Analysis5Code

|-
| <center>13</center>
| Anal6 TAnalysisCode NULL,
| Product.Analysis6Code

|-
| <center>14</center>
| Anal7 TAnalysisCode NULL,
| Product.Analysis7Code

|-
| <center>15</center>
| Anal8 TAnalysisCode NULL,
| Product.Analysis8Code

|-
| <center>16</center>
| Anal9 TAnalysisCode NULL,
| Product.Analysis9Code

|-
| <center>17</center>
| Anal10 TAnalysisCode NULL,
| Product.Analysis10Code

|-
| <center>18</center>
| Brand TAnalysisCode NULL,
| Product.BrandCode

|-
| <center>19</center>
| StyleCode TAnalysisCode NULL,
| Product.StyleCode

|-
| <center>20</center>
| ColourCode TAnalysisCode NULL,
| Product.ColourCode

|-
| <center>21</center>
| SizeCode TAnalysisCode NULL,
| Product.SizeCode

|-
| <center>22</center>
| StyleDescription TDescription NULL,
| Style.Description

|-
| <center>23</center>
| ColourDescription TDescription NULL,
| Colour.Description

|-
| <center>24</center>
| SizeDescription TDescription NULL,
| Size.Description

|-
| <center>25</center>
| SupplierCode TSupplierCode NULL,
| Product.Supplier1

|-
| <center>26</center>
| SupplierName TName NULL,
| Supplier.Name

|-
| <center>27</center>
| UnitsDay TQuantity NULL default 0,
| Note 1

|-
| <center>28</center>
| UnitsWTD TQuantity NULL default 0,
| Note 2

|-
| <center>29</center>
| UnitsLW TQuantity NULL default 0,
| Note 3

|-
| <center>30</center>
| UnitsW2 TQuantity NULL default 0,
| Note 3

|-
| <center>31</center>
| UnitsW3 TQuantity NULL default 0,
| Note 3

|-
| <center>32</center>
| UnitsW4 TQuantity NULL default 0,
| Note 3

|-
| <center>33</center>
| ''Units4WkAve TQuantity NULL default 0,''
| Note 4 ''(Computed Column)''

|-
| <center>34</center>
| UnitCost Money NULL default 0,
| Cost.BuyPrice1 – I.e. current list price for branch

|-
| <center>35</center>
| UnitWAC Money NULL default 0,
| Cost.WAC – I.e. current WAC for the branch

|-
| <center>36</center>
| UnitSell Money NULL default 0,
| Price.RetailPrice – I.e. current full selling price for branch

|-
| <center>37</center>
| TotalSalesDay Money NULL default 0,
| Note 5

|-
| <center>38</center>
| TotQtyDay
| Note 6

|-
| <center>39</center>
| TotSellDay
| Note 6

|-
| <center>40</center>
| TotQtyTot
| Note 7

|-
| <center>41</center>
| TotSellTot
| Note 7

|-
| <center>42</center>
| ''AveSellDay Money NULL default 0,''
| Note 6 ''(Computed Column)''

|-
| <center>43</center>
| ''AveSellTot Money NULL default 0,''
| Note 7 ''(Computed Column)''

|-
| <center>44</center>
| ''GPPercToday numeric(9,2) NULL default 0,''
| Note 8 ''(Computed Column)''

|-
| <center>45</center>
| ''GPPercWTD numeric(9,2) NULL default 0,''
| Note 8 ''(Computed Column)''

|-
| <center>46</center>
| ''GPPercLW numeric(9,2) NULL default 0,''
| Note 8 ''(Computed Column)''

|-
| <center>47</center>
| ''GPPercTotal numeric(9,2) NULL default 0,''
| Note 8 ''(Computed Column)''

|-
| <center>48</center>
| SOH TQuantity NULL default 0,
| Note 9

|-
| <center>49</center>
| SOHLayby TQuantity NULL default 0,
| Note 10

|-
| <center>50</center>
| LastSoldDate datetime NULL,
| Note 11

|-
| <center>51</center>
| LastRecvDate datetime NULL,
| Note 12

|-
| <center>52</center>
| SOHLW TQuantity NULL default 0,
| Note 13

|-
| <center>53</center>
| NetSalesDay Money NULL default 0,
| Note 14

|-
| <center>54</center>
| NetSalesWTD Money NULL default 0,
| Note 14

|-
| <center>55</center>
| NetSalesLW Money NULL default 0,
| Note 14

|-
| <center>56</center>
| NetSalesW2 Money NULL default 0,
| Note 14

|-
| <center>57</center>
| NetSalesW3 Money NULL default 0,
| Note 14

|-
| <center>58</center>
| NetSalesW4 Money NULL default 0,
| Note 14

|-
| <center>59</center>
| GPDay Money NULL default 0,
| Note 15

|-
| <center>60</center>
| GPWTD Money NULL default 0,
| Note 15

|-
| <center>61</center>
| GPLW Money NULL default 0,
| Note 15

|-
| <center>62</center>
| GPW2 Money NULL default 0,
| Note 15

|-
| <center>63</center>
| GPW3 Money NULL default 0,
| Note 15

|-
| <center>64</center>
| GPW4 Money NULL default 0
| Note 15

|-
| <center>65</center>
| TransferInQtyDay TQuantity null default 0
| Note 16

|-
| <center>66</center>
| TransferOutQtyDay TQuantity null default 0
| Note 16

|-
| <center>67</center>
| CommittedPOQty TQuantity null default 0
| Note 17

|-
| <center>68</center>
| UnCommittedPOQty TQuantity null default 0
| Note 18

|-
| <center>69</center>
| InBoundStockQty TQuantity null default 0
| Note 19

|-
| <center>70</center>
| SupplierStockCode TProduct NULL
| Product.Supplier1Code

|-
| <center>71</center>
| UnitDutyFreePrice money NULL default 0
| Price.DutyFreePrice

|-
| <center>72</center>
| UnitsEver TQuantity NULL default 0
| Note 3

|-
| <center>73</center>
| NetSalesEver Money NULL default 0
| Note 14

|-
| <center>74</center>
| GPEver Money NULL default 0
| Note 15

|-
| <center>75</center>
| ''GPPercEver''
| Note 8 ''(Computed Column)''

|-
| <center>76</center>
| ''WeeksCover''
| Note 20 (''Computed Column)''

|-
| <center>77</center>
| FirstSoldDate datetime NULL,
| Note 21

|-
| <center>78</center>
| FirstRecvDate datetime NULL
| Note 22

|-
| <center>79</center>
| EarliestPODueDate datetime NULL
| Note 23

|-
| <center>80</center>
| LatestPODueDate datetime NULL
| Note 23

|-
| <center>81</center>
| ''WeeksSoldFor int NULL''
| Note 24 ''(Computed Column)''

|-
| <center>82</center>
| SuperProduct bit NOT NULL DEFAULT 0
| Note 25

|-
| <center>83</center>
| SubProduct bit NOT NULL DEFAULT 0
| Note 25

|-
| <center>84</center>
| 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<sup><nowiki>*</nowiki></sup> divided by Net sales for the day. Gross Profit and Net Sales defined later in notes 14 & 15.


<sup><nowiki>*</nowiki></sup> 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<sup><nowiki>*</nowiki></sup> 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.


<sup><nowiki>*</nowiki></sup> Period is the day, WTD, LW etc according to the specific field. 


'''Note 15'''

GP for the period<sup><nowiki>*</nowiki></sup> 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.


<sup><nowiki>*</nowiki></sup> 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)