It is no longer always easy to ensure total transparency in international business relations. They are nevertheless subject to high compliance requirements. In recent blog posts, we have looked at the topic of how to analyze business relationships with countries which may potentially prove problematic. These first and foremost include payments to countries that might be suspected of money laundering and the issue of export restrictions. In today’s blog post, I would like to complete our discussion of this topic and show you how to perform an analysis in SAP to identify dangerous deliveries to “rogue states”.
In our series on “rogue states”, we have already covered the following topics:
This time we will look at what could potentially constitute dangerous deliveries to “rogue states” and how we can then identify them in SAP.
In order to get this topic rolling, two things must of course first be clearly defined:
- When is a country considered a “rogue state” or “unreliable country”?
- What is a “dangerous delivery”?
A working definition is given for both criteria below and we will then use these definitions to find the affected deliveries in SAP.
What are rogue states?
We have used this criterion many times before. It has already been explained in detail in this blog post. So we will just provide a short recap here.
Various organizations deal with the classification of countries in terms of the extent of corruption. This is a very extensive area, which is why the organizations involved focus on different priorities, such as money laundering, the financing of nuclear weapons or similar. For example, every year, the Non-Governmental Organization Transparency International publishes the Corruption Perception Index (CPI) – a list of the perceived level of corruption in different countries around the world. According to this index, it is the Scandinavian countries which come out on top, with four of them (Denmark, Norway, Sweden, Finland) in the top 6 countries with the least perceived corruption. Germany is ranked in 10th place, but it is really the other end of the list that we should be looking at as a basis for our analysis in SAP:
| Rank | Country | Score |
|---|---|---|
| 166 | Iraq | 17 |
| 166 | Venezuela | 17 |
| 168 | Guinea-Bissau | 16 |
| 169 | Afghanistan | 15 |
| 170 | Yemen | 14 |
| 170 | Libya | 14 |
| 170 | Sudan | 14 |
| 174 | North Korea | 12 |
| 175 | Southern Sudan | 11 |
| 176 | Somalia | 10 |
Now, of course, it all depends on your company, what industry you are in and the trade relations you conduct, but we would not usually want to be supporting criminal activities in such countries with funds from our company. So in this way we have already identified the top 10 biggest “rogue states”.
What are “dangerous deliveries”?
Of course, “dangerous deliveries” is a somewhat “loaded” term and one which is designed to catch our attention. But how can “dangerous deliveries” in fact be identified? Taking a look at material master data characteristics can be of help here. The material master data in SAP can be found in the “MARA” table. Information regarding whether materials are hazardous substances is also stored in the material master data, since they must of course be handled specially for logistics purposes. If we take a look at the attributes in the SAP table “MARA”, we can see that the following data fields appear to be relevant:
- “HAZMAT” data field (Relevant for Hazardous Substances)
- “STOFF” data field (Hazardous Material Number)
- “PROFL” data field (Profile for Dangerous Goods Indicator)
- “DG_PACK_STATUS” data field (Dangerous Goods Packaging Status)
You can use this information to check whether you have any of the relevant materials in your SAP system. Use the transaction “DBACOCKPIT – DIAGNOSIS – SQL-EDITOR” to execute the following SQL queries.
The following SQL Query searches for hazardous materials:
SELECT MARA.MANDT, MARA.MATNR, MAKTX FROM MARA, MAKT
WHERE (PROFL NOT LIKE ''
OR DG_PACK_STATUS NOT LIKE ''
OR STOFF NOT LIKE ''
OR HAZMAT NOT LIKE '')
AND MARA.MANDT = MAKT.MANDT
AND MARA.MATNR = MAKT.MATNR
AND MAKT.SPRAS='E'
Now let’s combine “rogue states” with “dangerous deliveries”
Now that we have a definition of “rogue states” and “dangerous deliveries”, we can use both definitions in SAP to analyze the actual deliveries we have made concerning “dangerous materials” in relation to the countries mentioned. In SAP, deliveries are listed in the tables LIKP (delivery note) and LIPS (delivery note items). We retrieve the country of destination (country to which the goods were delivered) from the customer master data (table KNA1).
SELECT LIKP.MANDT, LIKP.VBELN, LIPS.MATNR, KNA1.KUNNR, KNA1.LAND1
FROM (SELECT MANDT, MATNR FROM MARA WHERE PROFL NOT LIKE ''
OR DG_PACK_STATUS NOT LIKE ''
OR STOFF NOT LIKE ''
OR HAZMAT NOT LIKE '') DANGEROUS_MATERIAL
JOIN LIPS ON (DANGEROUS_MATERIAL.MANDT=LIPS.MANDT AND DANGEROUS_MATERIAL.MATNR=LIPS.MATNR)
JOIN LIKP ON (LIPS.MANDT=LIKP.MANDT AND LIPS.VBELN=LIKP.VBELN)
JOIN KNA1 ON (KNA1.MANDT=LIKP.MANDT AND KNA1.KUNNR=LIKP.KUNNR)
ORDER BY KNA1.LAND1, LIKP.MANDT, LIKP.VBELN
The SQL Query displayed lists all deliveries (VBELN=delivery number) of hazardous substances (MATNR) to customers (KUNNR) in a particular country (LAND1) sorted by country code.
The texts for the country abbreviations can be found in the SAP Table T005T.
If you want to exclude certain countries, e.g. Germany and the USA, from the analysis, then insert the following WHERE condition before the ORDER BY:
WHERE KNA1.LAND1 NOT IN ('DE','US')
When analyzing the set of results in detail, focus on countries that you consider to be problematic.
Sounds too complicated?
If this analysis is too technical or “programming-intensive” for your liking, then, not to worry, you can still make progress on this topic even if you don’t have any specialist knowledge of programming. For datasets with less than 50,000 entries in the “BKPF” table in SAP, you can even use zap Audit free-of-charge.
