Please see below the complete list of tests that can be performed to ensure the highest level of data quality in the vendor master tables in SAP:
1.Check for duplicate vendor master records in the LFA1 table:
SELECT LIFNR, COUNT(*) AS CNT FROM LFA1 GROUP BY LIFNR HAVING COUNT(*) > 1;This query selects all vendor master records in the LFA1 table and groups them by vendor number (LIFNR). The HAVING clause filters the results to only show vendor numbers that have more than one record in the table, indicating potential duplicates. The COUNT(*) function returns the number of records for each vendor number.
If you run this query and get results, it indicates that there are duplicates in the LFA1 table. You will need to review the records and determine which ones are correct and which ones should be deleted. Before deleting any records, you should check if there are any transactions linked to them to avoid data loss. You may also want to investigate the root cause of the duplicate records to prevent them from occurring in the future.
2.Check for vendor master records with invalid or missing data in the LFA1 table:
SELECT *
FROM LFA1
WHERE NAME1 IS NULL OR NAME1 = ''
OR SORTL IS NULL OR SORTL = ''
OR LAND1 IS NULL OR LAND1 = ''
OR (STCD1 IS NULL AND STCD2 IS NULL)
OR (STCD1 IS NOT NULL AND STCD1 <> '' AND STCD1 <> 'BRAND' AND STCD1 <> 'FISCAL')
OR (STCD2 IS NOT NULL AND STCD2 <> '' AND STCD2 <> 'BRAND' AND STCD2 <> 'FISCAL')
OR (STCD1 IS NOT NULL AND STCD1 <> '' AND (LAND1 <> 'BR' AND LAND1 <> 'FR' AND LAND1 <> 'DE' AND LAND1 <> 'IT'))
OR (STCD2 IS NOT NULL AND STCD2 <> '' AND (LAND1 <> 'BR' AND LAND1 <> 'FR' AND LAND1 <> 'DE' AND LAND1 <> 'IT'));This query checks for vendor master records with invalid or missing data in various fields of the LFA1 table. Specifically, it checks for null or empty values in the NAME1, SORTL, and LAND1 fields, and for missing or invalid tax numbers in the STCD1 and STCD2 fields.
The query also checks for STCD1 and STCD2 values that are not valid tax numbers. In this example, 'BRAND' and 'FISCAL' are assumed to be valid tax numbers, but you may need to modify the query to include other valid tax numbers depending on your requirements.
Finally, the query checks that the country code (LAND1) is valid for the tax number. In this example, only vendors with tax numbers in Brazil, France, Germany, and Italy are considered valid. You may need to modify this clause to include other valid countries depending on your requirements.
If you run this query and get results, it indicates that there are vendor master records with invalid or missing data in the LFA1 table. You will need to review the records and correct any errors or missing data.
