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 SQL1.
* The SQL1 provided must return the fields listed.
E.g.:
For ESP (Spain), SQL1 only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL1 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 SQL1 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 SQL2.
Fields With Prompt Table Edits:
The SQL1 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.
SQL1 - 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;
SQL2 – List Translate Values For A Field:
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 SQL1.
* The SQL1 provided must return the fields listed.
E.g.:
For ESP (Spain), SQL1 only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL1 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 SQL1 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 SQL2.
Fields With Prompt Table Edits:
The SQL1 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.
SQL1 - 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;
SQL2 – 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';
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';
