get_translate_values

get_translate_values lists the valid values of a field that uses translate values, such as a status code, with their long and short names. Translate values are effective-dated, so the tool returns the values in effect on a given date, which is today unless you ask for another.

Use it for

“What are the valid values of EMPL_STATUS?”
“What does status P mean on a training request?”
“Which ZZ_TRAIN_STATUS values were in use on January 1, 2025?”

Input

Argument	Type	Required	Description
`field`	string	Yes	The field name, such as `EMPL_STATUS`. Up to 18 characters.
`as_of`	string	No	The date to report values for, as `YYYY-MM-DD`. Default: today.
`include_inactive`	boolean	No	Also return values that are inactive on that date. Default: `false`.

{
  "type": "object",
  "properties": {
    "field": {
      "type": "string",
      "pattern": "^[A-Za-z][A-Za-z0-9_]{0,17}$",
      "description": "Field name, for example EMPL_STATUS"
    },
    "as_of": {
      "type": "string",
      "format": "date",
      "description": "Report values in effect on this date"
    },
    "include_inactive": { "type": "boolean", "default": false }
  },
  "required": ["field"],
  "additionalProperties": false
}

Output

Field	Description
`field`, `asOf`	The field, and the date the values are in effect on
`values`	The values in value order, each with `value`, `longName`, `shortName`, and `effectiveDate`
`values[].inactive`	Present and `true` for an inactive value, when `include_inactive` is `true`

{
  "field": "ZZ_TRAIN_STATUS",
  "asOf": "2026-09-24",
  "values": [
    {
      "value": "A",
      "longName": "Approved",
      "shortName": "Approved",
      "effectiveDate": "2025-01-01"
    },
    {
      "value": "C",
      "longName": "Completed",
      "shortName": "Completed",
      "effectiveDate": "2025-01-01"
    },
    {
      "value": "D",
      "longName": "Denied",
      "shortName": "Denied",
      "effectiveDate": "2025-01-01"
    },
    {
      "value": "P",
      "longName": "Pending Approval",
      "shortName": "Pending",
      "effectiveDate": "2025-06-01"
    }
  ]
}

How it works

Translate values live in PSXLATITEM, keyed by FIELDNAME, FIELDVALUE, and EFFDT. The tool uses the standard effective-dated pattern: for each value, it takes the row with the latest effective date on or before as_of.

SELECT X.FIELDVALUE, X.XLATLONGNAME, X.XLATSHORTNAME,
       X.EFFDT, X.EFF_STATUS
  FROM PSXLATITEM X
 WHERE X.FIELDNAME = :field
   AND X.EFFDT = (SELECT MAX(X1.EFFDT)
                    FROM PSXLATITEM X1
                   WHERE X1.FIELDNAME = X.FIELDNAME
                     AND X1.FIELDVALUE = X.FIELDVALUE
                     AND X1.EFFDT <= :as_of)
 ORDER BY X.FIELDVALUE

Rows whose EFF_STATUS is I are inactive on that date. They’re left out unless include_inactive is true.

Limits and errors

A field without translate values returns a tool execution error. The error suggests describe_record, because the field may be validated against a prompt table instead.
A value added after as_of doesn’t appear, because it wasn’t in effect yet.
Names come back in the base language. Translations into other languages aren’t included.

describe_record shows which fields are validated against translate values, with the translate flag.
run_select can count how often each value is used, if your administrator allows it.