How to get Field Value from its Description?
How to get Translate Field Value from its Description?
A brief overview 1st.What are XLAT Tables in PeopleSoft?
XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).
Some Functions:
LongTranslateValue: This property returns a string that contains the Long translate (XLAT) value of the field if the field is based on a translate table.ShortTranslateValue: This property returns a string that contains the Short translate (XLAT) value of the field if the field is based on a translate table.
How to get Translate Field Value from its Description?
What are the important Fields in the PSXLATITEM table?
| PeopleSoft Field Name | Description |
|---|---|
| FIELDNAME | Field Name |
| FIELDVALUE | Field Value |
| EFFDT | Effective DateDefault: %date |
| EFF_STATUS | Effective Status |
| XLATLONGNAME | Translate Long Name |
| XLATSHORTNAME | Translate Value Short Name |
SQL For getting Translate Field From Description?
SELECT FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = :1 AND(FIELDVALUE= :2 OR XLATLONGNAME = :2 OR XLATSHORTNAME= :2) AND
EFFDT=(SELECT MAX(B.EFFDT) FROM PSXLATITEM B
WHERE B.FIELDNAME = FIELDNAME AND B.FIELDVALUE = FIELDVALUE AND B.EFFDT <= SYSDATE)
AND EFF_STATUS = 'A';
Method to get the Translate Field from Description:
| method GetValueFromXlatDescr /+ &FieldSrc as Field, +/ /+ &FieldTrgt as Field, +/ /+ &effDt as Date, +/ /+ &refFieldName as String +/ | Variables: Source Field Target Field Reference Date Xlat Field Name |
| Local Record &rec = CreateRecord(Record.PSXLATITEM); Local string &SQL, &value, &sError; If All(&FieldSrc.Value) Then | If Field has some value then |
| &SQL = "SELECT A.FIELDVALUE FROM PSXLATITEM A WHERE A.FIELDNAME = :1 AND %EffdtCheck(PSXLATITEM B, A, %Datein(:2)) AND A.EFF_STATUS = 'A' AND (upper(A.XLATLONGNAME) = upper(:3) or A.FIELDVALUE = :3)"; | SQL |
| SQLExec(&SQL, &refFieldName, &effDt, &FieldSrc.Value, &FieldTrgt.Value); %This.ErrorIfNoFieldValue(&FieldSrc, &FieldTrgt); End-If; end-method; | Execute the SQL & Check for Errors |
Explanation:
I have written method in App Pkg style. Since this is the new style of coding and is object oriented, we should adopt this as much as possible.We could have passed many variables, but to adopt a Object Oriented approach (pass by reference) I am using the Fields.
The Field object can carry all these and we need not worry about the limitation of returning more than one value.
After verification that the field has some value, we formulate the SQL. In the WHERE clause I have used the FIELDVALUE check along with the intended XLATLONGNAME just in case we get the actual FIELDVALUE and want to validate it. Also I have used upper to not limit the case of recieved strings.
Post execution of the SQL, we are passing it to Check and Error so that we dont retain the null values, and use them for loading in the final targets.
