Adding Cost Center to email template - xmpie-users/uStore-js GitHub Wiki

To add the cost center to the email template please follow the instruction bellow.

The cost center is saved in the Order in the column ClearingResult.

In the Message Template SQL the Order query (ID 19) looks like this:

exec('SELECT O.EncryptedOrderId, CAST (O.ClearingResult AS XML).value(''(/UserData//ClearingDisplayInfo/node())[1]'', ''nvarchar(max)'') as ClearingResult, 
CASE WHEN SCCC.Name IS NULL THEN CC.Name ELSE SCCC.Name END as PaymentMethod, 
Cy.Symbol as CurrencySymbol, 
Cn.CouponCode, 
CouponDiscountAmount = dbo.fn_GetFormattedPrice(O.CouponDiscountAmount, 
Cy.Symbol, 
CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator,CI.CurrencyDecimalDigits),
FormatPattern = dbo.fn_GetFormattedPricePattern(Cy.Symbol, CI.CurrencyPositivePattern, 
CI.CurrencyDecimalSeparator,CI.CurrencyDecimalDigits), 
CI.CurrencyDecimalSeparator as DecimalSeparator, 
Bill_City as BillingAddressCity, 
Bill_State as BillingAddressState, 
Bill_Zip as BillingAddressZip, 
Bill_Add1 BillingAddress1, 
Bill_Add2 as BillingAddress2 , 
P1.Name as BillingAddressRegion ,
C.Name as BillingAddressCountry 
FROM Orders O LEFT join ClearingConfig CC on CC.ClearingConfigId = O.PaymentMethodId LEFT outer join StoreClearingConfig SCC on SCC.ClearingConfigId = CC.ClearingConfigId and SCC.StoreId = '+@StoreID+' LEFT outer join StoreClearingConfig_Culture SCCC on SCCC.StoreClearingConfigId = SCC.StoreClearingConfigId and SCCC.CultureID = '+@CultureID+' LEFT outer join Coupon Cn ON Cn.CouponID = O.CouponID join CultureInfo CI ON CI.StoreID = O.StoreID AND BaseCultureId = '+@CultureId+'AND CI.StatusID = 1 join Currency Cy ON Cy.CurrencyId = CI.CurrencyId LEFT JOIN Province P1 ON O.[Bill_State] = P1.[ProvinceID] LEFT JOIN Country C ON O.Bill_Country = C.CountryID WHERE O.OrderId= '+@OrderId)

In order to receive the cost center you would need to add another field to fetch within this SQL query above.

Please add:

CAST(REPLACE(O.ClearingResult, ''utf-8'', ''utf-16'') AS XML ).value(''(/UserData//CostCenter/node())[1]'',
''nvarchar(max)'') AS ''CostCenter''

The result would like this:

EXEC( 
'SELECT O.EncryptedOrderId,CAST(REPLACE(O.ClearingResult, ''utf-8'', ''utf-16'') AS XML ).value(''(/UserData//CostCenter/node())[1]'',''nvarchar(max)'') AS ''CostCenter'', 
CAST (O.ClearingResult AS XML).value(''(/UserData//ClearingDisplayInfo/node())[1]'', ''nvarchar(max)'') as ClearingResult, CASE WHEN SCCC.Name IS NULL THEN CC.Name ELSE SCCC.Name END as PaymentMethod, 
Cy.Symbol as CurrencySymbol, 
Cn.CouponCode, 
CouponDiscountAmount = dbo.fn_GetFormattedPrice(O.CouponDiscountAmount, Cy.Symbol, CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator,CI.CurrencyDecimalDigits), 
FormatPattern = dbo.fn_GetFormattedPricePattern(Cy.Symbol, CI.CurrencyPositivePattern, CI.CurrencyDecimalSeparator,CI.CurrencyDecimalDigits), 
CI.CurrencyDecimalSeparator as DecimalSeparator, 
Bill_City as BillingAddressCity, 
Bill_State as BillingAddressState, 
Bill_Zip as BillingAddressZip, 
Bill_Add1 BillingAddress1, 
Bill_Add2 as BillingAddress2 , 
P1.Name as BillingAddressRegion,
C.Name as BillingAddressCountry 
FROM Orders O LEFT join ClearingConfig CC on CC.ClearingConfigId = O.PaymentMethodId LEFT outer join StoreClearingConfig SCC on SCC.ClearingConfigId = CC.ClearingConfigId and SCC.StoreId = '
+@StoreID+ 
' LEFT outer join StoreClearingConfig_Culture SCCC on SCCC.StoreClearingConfigId = SCC.StoreClearingConfigId and SCCC.CultureID = '
+@CultureID+ 
' LEFT outer join Coupon Cn ON Cn.CouponID = O.CouponID join CultureInfo CI ON CI.StoreID = O.StoreID AND BaseCultureId = '
+@CultureId+ 
'AND CI.StatusID = 1 join Currency Cy ON Cy.CurrencyId = CI.CurrencyId LEFT JOIN Province P1 ON O.[Bill_State] = P1.[ProvinceID] LEFT JOIN Country C ON O.Bill_Country = C.CountryID WHERE O.OrderId= ' 
+@OrderId)

After the SQL query is modified you can use the following field to fetch the cost center for your email template.

<xsl:value-of select="//Sqls/Order/Row/CostCenter">