BizOneness: Harmonized Codes and String Formatting

I've been explaining formatting in the last few regular columns, only to be interrupted by last week's special edition. Using one detail of that special edition, I'd like to discuss what happens when you have to customize formatting using string manipulation. Let's look at how to format the Harmonized code on a commercial invoice through string manipulation.?

Commercial invoices?are not invoices in a A/R or A/P invoice sense. They are a special delivery document for shipping internationally to determine import duties and taxes. It's likely you either have an add-on or will have to make your own commercial invoices out of a delivery document. One extra piece of Information you need for each item shipped is its harmonized code.?

The Harmonized code is a globally accepted standard code that classifieds your product. Although countries might make modifications, using this code triggers how the country the shipment is delivered to will handle it. Let's use Huli Pizza Company as an example. With all the East Asian visitors to their Waikiki and Hawaii Kai Stores, Huli Pizza plans to ship pizzas from Hawaii to East Asia. As an experiment, they'll ship three different par-baked frozen pizzas: Huli Chicken, The Big Island Calzone, and The Meat Lover's Deep Dish. As an American company, HPC would look to the?Schedule B of the U.S. census?or use?their search tool?to get their harmonized codes. Chapter 19 of the harmonized codes contains wheat-based products. Pizza as a commodity is based on its crust, which is a bread. Subchapter 05 is for?Pastry and bread products. It falls in the section forOther, since it isn't directly a bread, thus section 90. For Shipping documents, you'll only need those numbers, with a period separating the section from the subchapters. Huli chicken and The Big Island both have Harmonized codes of 1905.90. Countries, including the U.S., might add another four-digit number that is not standard to classify for statistical and marketing purposes. According to schedule B, 1905.90 is the code for corn chips and pizzas. 1905.90.9060 is the code for pizzas in the United States.?

Be aware there may be exceptions. In chapter 19, an exception occurs when the item contains more than 20% meat by weight. In that case, the pizza goes under chapter 16. The Big Island has 18% meat, and Huli Chicken has 8% meat: they are in Chapter 19. However, the meat lover's deep dish has 30% meat due to layers of sausage and pepperoni in meat sauce. The Meat Lover's gets a different harmonized code of 1602.49.7000

You'll need a User Defined Field (UDF) for harmonized codes. Even if your country does not have a ten-digit designation, I suggest using them. You'll want a length of at least 12 Alphanumeric characters for this field - ten for the digits and 2 for the decimal points.

Instead of manual entry for all your exported goods, importing the values from an excel spreadsheet through Data Transfer Workbench(DTW) makes this much less tedious. You'll enter into a spreadsheet of Item codes with the corresponding Harmonized code. For more on this process,?you can read my series on DTW. Even if you never use all ten, the ten digits make DTW much easier to deal with. To be more precise, it makes Excel a lot easier to deal with.?

If I entered?1905.90?into Excel, it would round it to?1905.9, which is not the correct code, and will import wrong. Some localizations in Excel may change the separator to the wrong character. We make this a string by entering?another?decimal point to prevent this. If we have the four-digit suffix, we can enter that and make queries for market analysis. If not, you can use?.xxxx?or?.x?to end the six-digit code.?

When making the UDF for harmonized code, you have two options: Add it to the Item master data(OITM) or the delivery Document rows(DLN1).?

You'd use the Delivery document method if you add the Harmonized code to the delivery document rows while shipping. However, there is no DLN1 in UDF management. Instead, you make this UDF for all marketing document rows. Unless you set up a UDO lookup table as I did at the end of?SAP Business One: Reporting and customization, this will mean a manual entry of the harmonized code on each delivery document that needs a Commercial invoice.?

Alternatively, you can add the Harmonized code to the Item master data and either join or sub-query the code from the Item master. In this setup, you'd have to assign the correct harmonized code to each finished good in your table. This is where DTW comes to play. You make a spreadsheet with columns for ItemCode and your UDF. As I explained in a?previous BizOneness, I tend to write a simple query for active items for sale, then export that to Excel

SELECT 
    t0.ItemCode,
    t0.ItemName,
    t0.U_HSCode 
FROM OITM T0
WHERE  -- Active Items for sale
   t0.validFor = 'Y' AND
   t0.sellItem = 'Y' AND
   t0.InvntItem = 'Y'         

I'll change the names for DTW to ones DTW wants, and then fill out the table,?convert it to a Tab delimited text file, and?run it through DTW.?

Once you have data in the system, you can write a query to include all necessary data. How you do this depends on the strategy you take with Crystal reports. There are three ways of doing this:?

1. As I did in?SAP Business One: reports and customization, by adding tables to crystal reports
2. Use a subtable like in?this video?for a production report.
3. Use a query as I did in this?BizOneness article?or?video

I'll give you my current version of #3, which I used for my new commercial invoices:?

SELECT 
   T0.DocEntry as "DocEntry",T0.LineNum, 
   T0.Dscription, 
   T0.Weight1,
   
 -- Harmonized Code DLN1
   LEFT(T0.U_HSCode,7) AS "DLN1_HSCode",
   
   T0.Quantity, T0.Price, 
   T0.LineTotal, T1.NumAtCard, 
   T1.DocDate, T1.Address,
    T1.CardName, T1.Address2, 
    T1.U_CntctTel,
    CASE WHEN T0.CountryOrg IS NULL or T0.CountryOrg = ''  THEN 'US' ELSE T0.CountryOrg  END as "Country of Origin",
   (SELECT t2.PymntGroup FROM OCTG T2 WHERE t1.groupNum = t2.groupnum) as "Payment Terrms",
   (SELECT t3.Name FROM OCPR T3 WHERE T1.CntctCode = T3.CNtctcode) as "Contact Person",
   
-- Harmonized Code OITM
   (SELECT LEFT(t4.U_HSCode,7) FROM OITM T4 Where t4.ItemCode = t0.Itemcode) AS "OITM_HSCode", 
   
   (SELECT t4.U_CommDescription FROM OITM T4 Where t4.ItemCode = t0.Itemcode) AS "Goods Description",
   (SELECT t5.U_EORI FROM CRD1 t5 WHERE t5.CardCode = t1.cardcode AND t1.ShipTocode =  t5.Address AND 
             t5.Adrestype = 'S') AS "EORI"

 FROM    DLN1 T0 INNER JOIN  ODLN T1 ON T0.DocEntry= T1.DocEntry
 Where [%0] = T0.DocEntry        

You'll notice there are two harmonized codes that look like this.

-- Harmonized Code DLN1
LEFT(T0.U_HSCode,7) AS "DLN1_HSCode",
-- Harmonized Code OITM  
(SELECT LEFT(t4.U_HSCode,7) FROM OITM T4 Where t4.ItemCode = t0.Itemcode) AS "OITM_HSCode", 
        

I ended up using both. I'll use the OITM one if the DLN1 is null or empty.?

I stuck both in a LEFT function. LEFT is a string function. There are three crucial string functions and one string operator you should know, both for SQL and Crystal reports: LEFT, RIGHT, SUBSTRING/MID, and +/CONCAT. While not named the same, they work the same in both and in many computer languages(1). Here's a table of them.?

When formatting doesn't work any other way, I convert the data into raw strings (VARCHAR for SQL) and then manipulate them with these.?

The harmonized code is an example of a complex formatting case. Unless we validate our UDFs, we also have some user problems with code entry. Here's a simple query to test possible instances. A harmonized code might get entered as:

 SELECT
 '1905909060',
 '1905.90.9060',
 '1905.90.x'         

It can be worse than this, but I'll discuss data validation in a future Newsletter. By using LEFT(...,7), six digits, and a decimal point, I convert this to something manageable.?

 SELECT
 LEFT('1905909060',7), --> '1905909'
 LEFT('1905.90.9060',7), -->'1905.90'
 LEFT('1905.90.x',7) --> '1905.90'        

I'm already done for two cases. The third case has no decimal points. I can check the fifth position character for the existence of a decimal point with MID.?

SELECT
 SUBSTRING(LEFT('1905909060',7),5,1), --> '9'
 SUBSTRING(LEFT('1905.90.9060',7),5,1), -->'.'
 SUBSTRING(LEFT('1905.90.x',7),5,1) -->'.'        

If there is a decimal point, we are good to go. Here's a small query to test that:

 DECLARE @HSCode as VARCHAR(12)  = '1905.90.9060'
SELECT
CASE WHEN SUBSTRING(@HSCode, 5,1) = '.' 
    THEN  LEFT(@HSCode,7)
END AS HSCode        

Changing the value for?HSCode?gets us either a well-formed HSCode or a blank. For the blank case, we can use string functions to make a well-formed version in the ELSE of the CASE:?

DECLARE @HSCode as VARCHAR(12)  = '190590.9060'
SELECT
CASE WHEN SUBSTRING(@HSCode, 5,1) = '.' THEN
   LEFT(@HSCode,7)
ELSE
   LEFT(@HSCode,4) + '.' + SUBSTRING(@HSCode,5,2) 
END AS HSCode        

I could add this code to my query for the commercial invoice. I could also do all this string manipulation in Crystal Reports once the query is there. I'd make a formula to do the same thing.?

IF Mid({@Harmonized code },5,1) = "." THEN
    LEFT({@Harmonized code },7)
ELSE
    LEFT({@Harmonized code },4) + '.' + Mid({@Harmonized code },5 ,2)        

I'm also building an override function. If the DLN1 has a blank harmonized code on the delivery document, I'll use the OITM version; otherwise, I'll use DLN1. Blank, however, is tricky because there are two types of blank. There is a string of no length, or the value could be null. Testing for null is easy with the?isNull?function. Testing for an empty string, something DTW might do to a blank value on your spreadsheet, requires looking at the length of the string with LEN. I'd write another function that would then feed into my formatted formula like this:?

IF 
   (isNull({Command.DLN1_HSCode}) or 
   Len({Command.DLN1_HSCode})<6) 
THEN 
   {Command.OITM_HSCode}. //Blank In Delivery Doc
ELSE 
   {Command.DLN1_HSCode} //Exists in Delivery Doc        

I used the?LEN?function here to get the length of the string. While I could look for blank strings only, strings less than six characters need to be correctly formed, and I'll reject the delivery document version under those circumstances.?

I'd then place that formula on the form and save it. This gets you the properly formatted and sized harmonized code.?

That is a lot to take in. I've given you context concerning the last newsletter about Commercial invoices and harmonized codes. I've gone deeper into how to format a harmonized code in SQL and Crystal Reports as an example of using string functions for custom formatting. Even if you never make a commercial invoice, you can use string manipulations to create better reports in plenty of situations.?

(1) Sadly, one that doesn't is my beloved Swift. To be flexible with Unicode diacritical characters, Swift has more difficulty using?right,?left, and?mid. See the video in?iOS Development tips weekly?and a?Solution?for more on this.