Value Set to be created based on Geography Structure

Summary:

Hi Team,

We have a requirement to create Country , State and City as EFF on Assignment page which will fetch the values from Geographies.

We were able to create the Value Set for Country and State but for City we are encountering some issues as for some set of countries we are not getting the City Names and for some countries City LOVs have duplicate values.

Sharing the query here which we are using and let me know if I miss something

WHERE Clause:

hzg.GEOGRAPHY_ELEMENT2=:{SEGMENT.State}
AND hzg.geography_type IN ('LOCALIDAD','COMUNA','MUNICIPIO','POSTAL_CODE','MUNICIPALITY','DEPARTMENT','CITY','GEMEINDE','BEZIRK','GEMEENTE','PROVINCIE','SETTLEMENT','OBSHHINA','PROVINCIA','KOMMUNE','GRAD','DIMOS','OBEC','OKRES','CANTON','KUNTA','COMMUNE','DIMOTIK','NOMI','JARAS','TELEPULES','POST TOWN','SUBDISTRICT','COMUNE','TAJMA','LIWA','DIVISION','PILSETADISTRICT','LOCALITY','TOWN','DISTRITO','GMINA','POWIAT','CONCELHO','BARRIO','ZONE','COUNTY','OBCINA','UPRAVNA ENOTA','DISTRICT_MUNICIPALITY','LOCAL_MUNICIPALITY','PROVINCE','CHANGWAT','AMPHOE','DELEGATION','KASABA','ILCE','AREA','TOWNSHIP','THANK','PHO','QUAN',