Dont Get Stopped at the Border: Getting Ready for ICS2 in SAP B1

The email from FedEx crossed my desk on February 1st, and the contents have me scrambling. My company will be blocked from shipping to Europe over a few EU regulatory changes. Your company might be too.?

If you don't know about this, you might be in for a surprise in the next few months. The problem is the EU's Import Control System 2 (ICS2) FedEx describes?here?the requirements for which start March 1st, 2023. UPS describes it?here?though it mentions the implementation date pushed back to July 1st, 2023. If you don't follow these rules and ship to the EU, Northern Ireland, Norway, or Switzerland, your package will be stopped at the border or prevented from loading on the plane or boat, never reaching its destination.?

There's a lot to unpack in the ICS2 documents. Though it comes down to three things:?

1. The Commercial invoice has a Harmonized code for each item in the shipment
2. The Commercial Invoice has an accurate goods description and explains the goods beyond a brand name.
3. All Communications must transmit the Economic Operator Registration and identification number(EORI) for the receiver of the goods.

These must be on the printed commercial invoice and electronically transmitted to your carrier.

Now, I'm assuming if you have SAP Business One and a shipping add-On, your shipping add-on support team knows about this. If you have a robust solution, they fixed this, or it was integral to the solution for a long time.?

I, unfortunately, don't. Our former SAP channel partner, who, to protect the guilty, I'll call The Knights Who Say?Next*, has a shipping solution, and because we didn't have any solution at the time of our migration, adopted this add-on. We assumed they would support it better if they were the developer and our support partner.?

We were wrong.

When I sent in a ticket inquiring about upgrades to comply with ICS2 and the FedEx documentation, they responded that support sent an email to the?innovations team. Translation: They didn't anticipate this change, have no update, nor have?any solution to deal with it.?

Two weeks later, I got this:

The FedEx change is something outside the add-on in itself.?

Translation: They didn't read the FedEx documentation nor researched the scope of this change as an EU regulatory change for?all?shipping. In their minds, it must be a customization.?

This Channel partner might be great for users with no IT department and who want everything taken care of by their support partner. If you want a cookie-cutter version of SAP Business One, I'd recommend them in a second. If you're tech-savvy, want to do things on your own like backup, add simple customizations, or like to ask technical questions, they are slow to respond, bureaucratic, and a pain in the glutes to deal with.?

A few years ago, I got fed up with them. We switched to another channel partner, which I pay much more for our support, but I get answers in hours rather than weeks or months. However, migration costs made it difficult to budget for another shipping solution. I'm stuck with the Knights Who Say?Next's shipping solution.?

While waiting for a response, I did my planning and research. I will give you a rough sketch with useful queries instead of details because everyone's system differs. You can pass on this information to your shipping system provider, and they can do the details.?

Making UDFs for the Data

First, you'll need three new UDFs. The EORI, Harmonized Code, and Accurate goods description are unavailable in SAP B1. You'll need to add them as User Defined Fields (UDF).?

There is an EORI field in the OCRD table. However, you can't access it through the interface, as it does not show on the possible field to display in Business Partner master data. OCRD is a lousy place to put this. A given Business Partner may have multiple shipping locations in different countries. EORIs have a country prefix. A distributor customer would have different EORI values for their warehouses in Dublin, Paris, and Brussels if we shipped for this customer directly to those warehouses.?

Thus I add the UDF to the Business Partners - Addresses (CRD1) table, where I'll define it on ship-to locations. For CRD1, UDFs appear in the address window without needing the UDF panel. As operators enter the shipping address, they have the field on the bottom of the ship to entry screen to enter the EORI. When adding, Use an Alphanumeric code, it's shorter than this, but I used a 20-character Alphanumeric code.?

The Harmonized code and Accurate Goods Description are UDF in the OITM table. I made the Harmonized Code 20 characters and the Accurate Goods description 100 characters.?

Commercial Invoice Changes

A more robust shipping system would generate the Commercial invoice for you with all that information in ORDR and OCRD and linked tables. Our clunker of a shipping module doesn't do commercial invoices, though they are easy to create. They are modified packing lists with the harmonized Code listed in the line items and the goods description concatenated to the Item Description. If you've taken my course?SAP Business One Reporting and customization, I have no doubt you can make your own

For a head start, if you don't have a commercial invoice, you can use this query in Crystal Reports, which includes my UDFs for ICS2.

SELECT 
   T0.DocEntry as "DocEntry",T0.LineNum, 
   T0.Dscription, 
   T0.Weight1,  
   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",
   (SELECT t4.U_HSCode FROM OITM T4 Where t4.ItemCode = t0.Itemcode) AS "Harmonzed Code",
   (SELECT t4.U_CommDescription FROM OITM T4 Where t4.ItemCode = t0.Itemcode) AS "Goods Description"
 
 FROM    DLN1 T0 INNER JOIN  ODLN T1 ON T0.DocEntry= T1.DocEntry        

To learn more about importing queries into crystal reports, see my video:SAP SQL to Crystal Reports

Then you format the commercial invoice like any marketing document, with a header, and rows of product data, something like this:

There's more here, but I leave that to you to figure out. This will get you started.?

A View for Shipping Solutions

We are at the one step that if you are not a professional, please contact your support partner. Due to the nature of the SAP Support agreement, Shipping add-ons?never?directly touch SAP tables. Nothing but SAP code touches those tables directly. Instead, any add-on uses a query that pretends to be a table called a?View. Making views requires a lot of technical skills. Pass this article on to your support partner or tech team if you are not authorized to do this step. You can lose your SAP license for messing this up, so be?Very careful

Before making a view, in SAP Business One, you build a query that generates the information:?

Depending on your shipping solution and settings, you may generate this from Sales Orders(ORDR) or Delivery Documents(ODLV). We use sales orders. I'll code this accordingly, but use the magic of Marketing documents and switch to the table you need -- the fields are the same.?

 SELECT 
   T0.Docnum,
   T0.DocEntry,
   T0.CardCode,
   T2.LineNum,
   (
     SELECT t3.U_HScode 
     FROM OITM t3 
     WHERE t2.Itemcode = t3.ItemCode
    ) AS "HarmonizedCode",
   (
      SELECT t4.U_CommDescription 
      FROM OITM t4 
      WHERE t2.Itemcode = t4.ItemCode
    ) AS "AccurateDescription",
   (
      SELECT t1.U_EORI 
      FROM CRD1 t1 
      WHERE  t0.CardCode = t1.CardCode AND 
             t0.ShipTocode =  t1.Address AND 
             t1.Adrestype = 'S'
     ) AS "EORI"
FROM 
   ORDR T0 
   INNER JOIN RDR1 t2 ON t0.Docentry = T2.DocEntry 
WHERE t0.Docstatus = 'O'        

More likely than not, this will be a blank table due to those newly created UDFs. We'll populate the data shortly.?

You'll copy this table into the view you or your Support partner create in SQL Server Studio, which I called?EU_ICS2. Again, I'm not going to show you how to do this. You should not be anywhere near SQL Server Studio or a SQL Command Line unless you 100% know what you are doing.?

Once made, you will have a new table,?EU_ICS2?you can write queries on?

 SELECT * FROM EU_ICS2 T0 WHERE t0.DocNum = [%0]        

Populating Data

Your next step is populating the data. For data population, I use Excel spreadsheets which I then use to import the data for DTW. I make those spreadsheets using a SQL query for relevant data.?

For ICS2, using queries saves you a lot of time and focuses on what is essential. Honolulu or Buenos Aires isn't necessary here, but Berlin is. Removing everything but the EU will make the job a lot easier. You can also use sorting to identify the most significant customers and items you need data for.?

I came out with many queries that list products and Business partners, which I can then use with DTW. With a looming deadline, I made the data entry a little easier. I prioritized those items sold in the EU and the Business partners who bought them. I used the RDR12 table with sales order addresses, limiting the sales to the last 12 months.?

WITH EU_BPSales AS (
SELECT 
   T0.DocEntry as DocEntry,
   T0.BaseCard as CardCode,
   T0.LineTotal as LineTotal,
   T0.ItemCode as ItemCode,
   T0.Dscription as ItemName,
   T2.code as Country   
FROM 
   RDR1 T0
   INNER JOIN RDR12 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OCRY T2 ON T1.CountryS = T2.[Code]
WHERE 
  (t2.isEC = 'Y'  OR t2.code  IN('NO','CH','XI' )) AND 
 (Select t3.DocDate From ORDR t3 Where t0.Docentry = t3.docentry) >= DATEADD(month,-12,GETDATE())         

To filter for affected countries, I used the?OCRY.isEC?column to identify EU countries. This logical expression has an?OR OCRY.code IN('NO','CH','XI' )to find non-member nations and regions following the ICS2. The OCRY table is in?Administration>Setup>Business Partners>Countries/Regions?You may want to make two changes: If you haven't yet, check if you set your system for Brexit and check off United kingdom for the EU. Secondly, add a record for Northern Ireland, which for international shipping purposes is a separate region identified as?XI

This creates a table I can implement a WITH clause to then join it to other tables.?

Affected Business Partners

I'll build a list of Affected Business Partners by joining the table above, which I called?EU_BPSales, With the CRD1 table where I store the?U_EORI?UDF.?

WITH EU_BPSales AS (
SELECT 
   T0.DocEntry as DocEntry,
   T0.BaseCard as CardCode,
   T0.LineTotal as LineTotal,
   T0.ItemCode as ItemCode,
   T0.Dscription as ItemName,
   T2.code as Country   
FROM 
   RDR1 T0
   INNER JOIN RDR12 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OCRY T2 ON T1.CountryS = T2.[Code]
WHERE 
  (t2.isEC = 'Y'  OR t2.code  IN('NO','CH','XI' )) AND 
 (Select T3.DocDate From ORDR T3 Where T0.DocEntry = T3.DocEntry) >= DATEADD(month,-12,GETDATE()) 
)
SELECT 
   T0.cardcode,
   SUM(T0.LineTotal) as TotalSales,
   T1.Address,
   T1.U_EORI
FROM
  EU_BPSales T0
   INNER JOIN CRD1 T1 ON T1.Cardcode = T0.cardcode
WHERE 
   T0.country = T1.country
GROUP BY 
   T1.Address,T0.Cardcode,T1.U_EORI
ORDER BY 
   SUM(t0.LineTotal) DESC        

I grouped this by the?CRD1.Address?which is a shipping address, then Summed the products sent to that address and sorted the table by those totals. The top line is the BP who orders the most product, thus your biggest priority. Export this to Excel and fill it out with the EORI information for these business partners.?

Affected Products

For Harmonized Codes and Accurate Goods description, You do a similar thing to OITM as you did to CRD1 for the EORI. This time however, you'll emphasize items.?

  WITH EU_BPSales AS (
SELECT 
   T0.BaseCard as CardCode,
   T0.LineTotal as LineTotal,
   T0.ItemCode as ItemCode,
   T0.Dscription as ItemName,
   T2.code as Country
   
FROM 
   RDR1 T0
   INNER JOIN RDR12 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OCRY T2 ON T1.CountryS = T2.[Code]
WHERE 
  (T2.isEC = 'Y'  OR T2.Code  IN('NO','CH','XI' )) AND 
 (SELECT t3.DocDate FROM ORDR t3 WHERE t0.Docentry = t3.docentry) >= DATEADD(month,-12,GETDATE()) 
)

SELECT 
   T0.ItemCode,T1.ItemName,
   SUM(t0.LineTotal) as TotalSales,
   T1.U_HSCode,T1.U_CommDescription
FROM  
   EU_BPSales T0
   INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
WHERE 
   T1.ValidFor = 'Y' 
GROUP BY 
   t0.ItemCode,t1.ItemName,T1.U_HSCode,T1.U_CommDescription
ORDER BY
  Sum(t0.LineTotal) DESC        

I filtered here for active items since I won't be shipping inactive items.?

Again I'll make an excel spreadsheet and complete it with the relevant information. On this one, many lines will be repetitive, so cutting and pasting or filling are all options to speed this up.?

Once you've collected the data, update your system. Do that by DTW if you have a lot of information or by a manual entry for a few entries. In my case, I have only 40 of my thousands of Customers with shipping locations in the EU, with only ten spending any significant amount, so I'll manually enter those.?

Mapping to a Shipping System

Your last step is to map your data to a shipping system. Unfortunately, there are a lot of variations here depending on the shipper or the method the shipper is using. Web portals will be different from desktop applications. Both may be different from an API. I can't give you directions from this point. Your best course of action is to call your shippers like FedEx and UPS and get their help. Have the name of the views and the fields in it ready so they can easily access its data and match their fields to the fields for the View you or your support partner created. Here's a handy table of the Data you'll find in the View.?

Being ICS2 Ccompliant

And with all that in place, you should be ICS2 compliant. For full disclosure, I have yet to get hold of FedEx or UPS to map the fields, so if this will work is not guaranteed. However, this will give you a big boost toward implementation.?

This article was a special edition Of BizOneness, off of my usual biweekly posting, as this issue is fast approaching, and I wanted it out as soon as possible. Delays from the Knights who Say?Next?didn't help, nor did an ISO Audit in the middle of getting ready for this. But The core of what to do is here, and if it helps someone else move forward toward ICS2 implementation, I've done my job here at BizOneness.?

*Yes this is a takeoff of?Monty Python and the Holy Grail's?Knights Who Say?Ni. Quite a few years ago, I presented to the ASUG Business One convention about implementing fixed costs in SAP B1 and themed it to this comedy classic because what happened on our journey to the Holy Grail of fixed costs was so absurd. My dear former support partner claimed from version 8.8 to version 10.0 that there would be fixed costs in the?next?patch level or version. Fixed costs still aren't there, but they say?next?way too often. I don't think it is a coincidence they also have a fear of IT departments, but I thought of that later.