Posted On: June 27, 2019

Finding Differences Between UDCs (SQL Commands)

When comparing an individual or small number of UDC values between environments it will be easiest to use the Work With User Defined Codes application (P0004A).

However, in order to ensure that your UDCs are in sync between environments, it may be best to use SQL. The following tables make up UDCs:

  • F0004 – User Defined Code Types
  • F00041 – User Defined Code Types Language Status
  • F0004D – User Defined Codes – Alternate Language Descriptions
  • F0005 – User Defined Code Values
  • F00051 – User Defined Code Language Status
  • F0005D – User Defined Code – Alternate Language Descriptions

Below are a few queries that can be used to determine that status of your UDCs.

Note: Your system, environments, database platform or configuration may require changes to the below SQL scripts in order to run effectively. Please consult your DBA before running any of them.

Determine if the record counts are equal between environments. Run this on each table listed above:

SELECT 'DV920' AS Env, COUNT(*) AS Count FROM TESTCTL.F0004
UNION
SELECT 'PY920' AS Env, COUNT(*) AS Count FROM CRPCTL.F0004
UNION
SELECT 'PD920' AS Env, COUNT(*) AS Count FROM PRODCTL.F0004

Find the UDC Types that are in PY but NOT in PD:

SELECT DTSY,DTRT FROM CRPCTL.F0004
EXCEPT
SELECT DTSY,DTRT FROM PRODCTL.F0004

Find UDC Type descriptions that are NOT equal between PD & PY:

SELECT a.DTSY,a.DTRT,a.DTDL01, b.DTDL01
FROM CRPCTL.F0004 a
INNER JOIN PRODCTL.F0004 b
   ON (a.DTSY = b.DTSY AND a.DTRT = b.DTRT)
WHERE a.DTDL01 <> b.DTDL01

Find UDC values that are in PY but NOT in PD:

SELECT DRSY,DRRT,DRKY FROM CRPCTL.F0005
EXCEPT
SELECT DRSY,DRRT,DRKY FROM PRODCTL.F0005

Find UDC value descriptions that are NOT equal between PD & PY:

SELECT a.DRSY,a.DRRT,a.DRKY,a.DRDL01, b.DRDL01
FROM PRODCTL.F0005 a
INNER JOIN CRPCTL.F0005 b
   ON (a.DRSY = b.DRSY
      AND a.DRRT = b.DRRT
      AND a.DRKY = b.DRKY)
WHERE a.DRDL01 <> b.DRDL01