HCM/Campus Solutions Address Validations

If anyone has worked on a CI or a web service (e.g.: CWS - Constituent Web Service, AAWS - Admission Applications Web Service) that involves loading data related to addresses into HCM/Campus Solutions applications then you must have run into the address validation problem. The problem with the validation on the addresses component/page is that the fields displayed on the address subpage (specific to a country) would vary depending on the country.

This complicates the process of dynamically identifying the list fields that need to be populated depending on the current country that is being used for data entry. Let us take the example of the Admission Applications Web Service to put things into perspective. Typically, there would a web front-end for the admission applications and that would in turn invoke the AAWS web service to create and save applications into Campus Solutions. Now, the problem for the web front-end application is to dynamically figure out the list of fields that need to be populated on the AAWS request message based on the current country. Once we determine the list of fields that need to be populated, we would additionally run into the problem where the validation on the fields (required/translate/prompt edits) would differ per country.

Here are a list of logical steps and SQLs that would help with building some of that dynamic address validation logic. I was able to write some SQLs that look into PeopeTools metadata and pull out the information we need.

E.g.:

Address Formatting:

To identify the list of fields to display and populate (CIs/Web Service calls) data use PS_EO_ADDR_FMT_TBL.

E.g.:

select * from PS_EO_ADDR_FMT_TBL where COUNTRY = 'ESP';

The address format data could be found online as well.

Address Validations:

Required Fields:

Only the following fields are required:
-    COUNTRY
-    ADDRESS1* or ADDRESS2* or ADDDRESS3* or ADDRESS4*: At least one of these fields is required depending on how many exist in SQL¹.

* The SQL¹ provided must return the fields listed.

E.g.:

For ESP (Spain), SQL¹ only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL¹ returns all fields ADDRESS1, ADDRESS2, ADDRESS3 and ADDRESS4. Therefore, at least one of ADDRESS1 or ADDRESS2 or ADDRESS3 or ADDRESS4 fields is required for Brazil.

Fields With Translate Edits:

The SQL¹ provided must return ‘Y’ for TRANSLATE_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

ADDR_FIELD1 for Spain (ESP) is based on a translate edit.

Note: For List of Translates Values for a particular field use SQL².

Fields With Prompt Table Edits:

The SQL¹ provided must return ‘Y’ for PROMPT_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

STATE field for BRA (Brazil) has a prompt table of STATE_TBL. The key fields on the prompt table are COUNTRY, STATE.
CITY field for BRA (Brazil) has a prompt table of CODE_CITY_BRA. The key fields on the prompt table are STATE, CITY.

Note: For List of Values for a particular field use the prompt table (PROMPT_TABLE) with the associated prompt table keys (PROMPT_TABLE_KEYS).

Other Scenario:

If a field exists in PS_EO_ADDR_FMT_TBL and does not fall into any of the above scenarios then there is NO field validation.

SQL¹ - Address Format Sub Page Information Based On PeopleTools MetaData:

SELECT Substr(A.pnlname, 9, 3) AS COUNTRY,
       A.fieldname,
       CASE
         WHEN Bitand(B.useedit, 512) > 0 THEN 'Y'
         ELSE 'N'
       END AS TRANSLATE_EDIT,
       CASE
         WHEN Bitand(B.useedit, 16384) > 0 THEN 'Y'
         ELSE 'N'
       END AS PROMPT_EDIT,
       edittable AS PROMPT_TABLE,
       (SELECT Listagg(Y.fieldname, ', ')
                 within GROUP (ORDER BY y.fieldnum) "PROMPT_TABLE_KEYS"
        FROM   psrecfield Y
        WHERE  Y.recname = B.edittable
               AND Bitand(Y.useedit, 1) > 0
        GROUP  BY Y.recname)   AS PROMPT_TABLE_KEYS
FROM   pspnlfield A,
       psrecfield B
WHERE  A.pnlname IN (SELECT X.pnlname
                     FROM   ps_eo_addr_subpage X)
       AND A.recname <> ' '
       AND A.fieldname <> ' '
       AND MOD(A.fielduse, 2) = 0
       AND A.recname = B.recname
       AND A.fieldname = B.fieldname
--and substr(A.PNLNAME, 9,3) = 'BRA'
ORDER  BY country,
          A.fieldnum; 

SQL² – List Translate Values For A Field:

SELECT A.fieldname,
       A.fieldvalue,
       A.xlatlongname  AS DESCR,
       A.xlatshortname AS DESCR_SHORT
FROM   psxlatitem A
WHERE  A.fieldname = 'ADDR_FIELD1'
       AND A.effdt = (SELECT Max(A_ED.effdt)
                      FROM   psxlatitem A_ED
                      WHERE  A.fieldname = A_ED.fieldname
                             AND A.fieldvalue = A_ED.fieldvalue
                             AND A_ED.effdt <= SYSDATE)
       AND A.eff_status = 'A';