adding a country to existing uCommerce catalog

After creating two different catalogs for two different countries (US and Italy) in our uCommerce solution, I needed to add another country. This time however the Italy store and catalog are changed (manually) into an EMEA store. Therefore the new country is added to this EMEA store, in my case, using the same payment method and currency.

Basically you can use the stored procedure for creating a new store and remove the things you do not need. You will need to change the parameters somewhat to link the new country to the payment method (and perhaps other relations).

Here’s the stored procedure that let’s you do that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruben Verschueren
-- Create date: 2016/07/28
-- Description: creates a new uCommerce country store
-- and adds it to an existing store
-- =============================================
CREATE PROCEDURE AddCountryToStore
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@paymentMethodId int,
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogId int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @shippingMethodId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @ProductCatalogPriceGroupRelationId int

SELECT @definitionTypeId = definitionTypeId
FROM uCommerce_DefinitionType
WHERE name = 'PaymentMethod'

SELECT @emailProfileId = emailprofileid
FROM uCommerce_EmailProfile
WHERE name ='Default'

SELECT @orderNumberId = ordernumberid
FROM uCommerce_OrderNumberSerie
WHERE OrderNumberName = 'Default'

-- create currency
SELECT @currencyId = currencyid
FROM uCommerce_Currency
WHERE isocode = @currencyIsoCode AND deleted = 0

IF (@currencyId is not null)
BEGIN
PRINT 'currency already exists'
END
ELSE
BEGIN
INSERT uCommerce_Currency(isocode,exchangerate,deleted,guid)
VALUES (@currencyIsoCode, @currenctExchangeRate, 0, NEWID())
SELECT @currencyId = SCOPE_IDENTITY()
END

-- create pricegroup
SELECT @pricegroupId = pricegroupid
FROM uCommerce_PriceGroup
WHERE name = @countryName AND currencyId = @currencyId

IF (@pricegroupId is not null)
BEGIN
PRINT 'pricegroup already exists'
UPDATE uCommerce_PriceGroup
SET deleted = 0 WHERE name = @countryName AND currencyId = @currencyId
END
ELSE
BEGIN
INSERT uCommerce_PriceGroup(name,currencyid,vatrate,description,
createdon,createdby,modifiedon,modifiedby,deleted,guid)
VALUES (@countryName ,@currencyId,@vatRate, @priceGroupDescription,getdate(),
@createdBy,getdate(),@createdBy,0,NEWID())
SELECT @pricegroupId = SCOPE_IDENTITY()
END

-- create country
SELECT @countryId = countryid
FROM uCommerce_Country
WHERE name = @countryName AND Culture = @cultureCode
IF (@countryId is not null)
BEGIN
PRINT 'country already exists'
UPDATE uCommerce_Country
SET deleted = 0
WHERE name = @countryName
AND Culture = @cultureCode
END
ELSE
BEGIN
INSERT uCommerce_Country(name,culture,deleted)
VALUES (@countryName, @cultureCode, 0)
SELECT @countryId = SCOPE_IDENTITY()
END


-- needed?
SELECT @shippingMethodId = ShippingMethodId
FROM uCommerce_ShippingMethod
WHERE name = @shippingMethodName AND paymentmethodId = @paymentMethodId
IF (@shippingMethodId is not null)
BEGIN
PRINT 'shipping method already exists'
END
ELSE
BEGIN
INSERT uCommerce_ShippingMethod(name,PaymentMethodId,ServiceName,deleted)
VALUES (@shippingMethodName, @paymentMethodId, 'SinglePriceService', 0)
SELECT @shippingMethodId = SCOPE_IDENTITY()
END

--map shipping method to country
IF((SELECT count(*)
FROM uCommerce_ShippingMethodCountry
WHERE CountryId = @countryId and shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodCountry(CountryId,shippingMethodId)
VALUES(@CountryId,@shippingMethodId)
END

-- create ProductCatalog pricegroup relation
SELECT @ProductCatalogPriceGroupRelationId = ProductCatalogPriceGroupRelationId
FROM uCommerce_ProductCatalogPriceGroupRelation
WHERE ProductCatalogId = @productCatalogId and PriceGroupId = @pricegroupId
IF (@ProductCatalogPriceGroupRelationId is not null)
BEGIN
PRINT 'ProductCatalog is already linked to pricegroup'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogPriceGroupRelation
(productcatalogid,PriceGroupId,SortOrder)
VALUES (@productCatalogId,@priceGroupId,0)
SELECT @ProductCatalogPriceGroupRelationId = SCOPE_IDENTITY()
END
END

Now that we created the stored procedure, we can execute it as many times as we want adding more countries to our store. Obviously you need to change the values of the parameters especially the PaymentMethodId and productCatalogId. In the uCommerce backend you will now see the new country, pricegroup etc. If you open up the catalog, you will now see the new country as an option in the allowed priceGroups. Just enable the checkbox and you’re good to go… after you add all the prices on the products that is.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC AddCountryToStore 
'Ruben' --createdBy
,'EUR' --CurrencyIsoCode
,100 --currentExchangeRate
,'Netherlands' --countryName
,0.21 --vatRate
,'Pricing for NL eCommerce' --priceGroupDescription
,'nl-NL' --cultureCode
,8 --PaymentMethodId
,0.0000 --feePerentage
,'MyShippingMethod' --shippingmethodName
,5 --productCatalogId