These export interfaces extract primary information such as civility, legal forms, countries, and other reference data translated into different languages. The exported data can then be imported into the CAPENCY or DQE SOFTWARE environment, so that information can be displayed according to the user's cultural profile.
Menu option in Mode Place: Data Exchange > Data export > User-defined export > Request templates
1. Titles - List of Civility Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(SELECT "CIV" AS TYPINF,
CO_CODE AS LANG,
CAST(TDT_CODE AS VARCHAR(3)) AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="CIV" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "CIV",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="CIV"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="CIV" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="CIV" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "CIV",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="CIV"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="CIV")
)) R
Then go to Settings.
File format
2. Legal Form - List of Legal Forms Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(SELECT "JUR" AS TYPINF,
CO_CODE AS LANG,
TDT_CODE AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="JUR" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "JUR",
CO_CODE,
CC_CODE,
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="JUR"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="JUR" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="JUR" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "JUR",
CO_CODE,
CC_CODE,
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="JUR"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="JUR")
)) R
Then go to Settings.
File format
3. Labels - List of Field Labels Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT "LBL" AS TYPINF, CO_CODE AS LANG, ZY2_FLDY2 AS CODE,
ISNULL(TDT_LIBELLE, CASE WHEN LEN(TDA_VAL) = 0 OR TDA_VAL IS NULL THEN ISNULL(CC_LIBELLE, ZY2_FLDESC) ELSE TDA_VAL END) AS DESCR
FROM COMMUN
LEFT JOIN ZRELCHAMPY2 ON ZY2_DOMY2 in ("T", "C", "YTC") AND ZY2_VISIBLE = "X"
LEFT JOIN CHOIXCOD ON CC_TYPE = "ZLI" AND CC_CODE = CASE
WHEN ZY2_FLDY2 like "YTC_BOOLLIBRE%" THEN "CB" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "YTC_TEXTELIBRE%" THEN "CC" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "YTC_DATELIBRE%" THEN "CD" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "YTC_VALLIBRE%" THEN "CM" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "YTC_TABLELIBRE%" THEN "CT" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "C_BOOLLIBRE%" THEN "BB" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "C_TEXTELIBRE%" THEN "BC" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "C_DATELIBRE%" THEN "BD" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "C_VALLIBRE%" THEN "BM" + RIGHT(ZY2_FLDY2, 1)
WHEN ZY2_FLDY2 like "C_LIBRECONTACT%" THEN "BT" + RIGHT(ZY2_FLDY2, 1)
END
LEFT JOIN TRADDATA ON TDA_CHAMP = "DH_LIBELLE" AND TDA_LANG = CO_CODE AND TDA_CLE = ZY2_FLDY2
LEFT JOIN TRADTT ON CC_TYPE IS NOT NULL AND TDT_PREFIXE = "CC" AND TDT_TYPE = "ZLI" AND TDT_CODE = CC_CODE AND TDT_LANG = CO_CODE
WHERE CO_TYPE = "TRA"
ORDER BY 2, 3
Then go to Settings.
File format
4. Languages - List of Languages Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT TYPINF, RTRIM(LANG) AS LANG, RTRIM(CODE) AS CODE, DESCRIPTION FROM (
SELECT "LGU" AS TYPINF,
CO_CODE AS LANG,
TDT_CODE AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="LGU" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "LGU",
CO_CODE,
CC_CODE,
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="LGU"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="LGU" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="LGU" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "LGU",
CO_CODE,
CC_CODE,
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="LGU"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="LGU")
)) R
Then go to Settings.
File format
5. Nationalities - List of Nationalities Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT TYPINF,RTRIM(LANG) AS LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM (
SELECT
"NAT" AS TYPINF,
CO_CODE AS LANG,
TDA_CLE AS CODE,
TDA_VAL AS DESCRIPTION
FROM COMMUN
JOIN TRADDATA
ON TDA_CHAMP = "PY_NATIONALITE" AND TDA_LANG = CO_CODE
WHERE CO_TYPE = "TRA"
UNION
(SELECT DISTINCT
"NAT",
CO_CODE,
PY_PAYS,
PY_NATIONALITE
FROM COMMUN
JOIN TRADDATA
ON TDA_CHAMP = "PY_NATIONALITE" AND TDA_LANG = CO_CODE
JOIN PAYS
ON CO_TYPE = "TRA" AND NOT EXISTS(SELECT DISTINCT TDA_CLE FROM TRADDATA WHERE TDA_CHAMP = "PY_NATIONALITE" AND TDA_LANG = CO_CODE AND TDA_CLE = PY_PAYS) AND LEN(PY_NATIONALITE)<>0 )
UNION
(SELECT
"NAT",
CO_CODE,
PY_PAYS,
PY_NATIONALITE
FROM COMMUN
JOIN PAYS
ON CO_TYPE = "TRA" AND NOT EXISTS (SELECT DISTINCT TDA_LANG FROM TRADDATA WHERE TDA_CHAMP = "PY_NATIONALITE" AND TDA_LANG = CO_CODE) AND LEN(PY_NATIONALITE)<>0)
) R
Then go to Settings.
File format
6. Countries - List of Countries Translated by Cultural Profiles
Add the following request by clicking the button below:
SELECT TYPINF,RTRIM(LANG) AS LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM (
SELECT "PYS" AS TYPINF,
CO_CODE AS LANG,
TDA_CLE AS CODE,
TDA_VAL AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADDATA ON TDA_CHAMP="PY_LIBELLE" AND TDA_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(SELECT DISTINCT "PYS",
CO_CODE,
PY_PAYS,
PY_LIBELLE
FROM COMMUN
INNER JOIN TRADDATA ON TDA_CHAMP = "PY_LIBELLE" AND TDA_LANG = CO_CODE
INNER JOIN PAYS
ON CO_TYPE="TRA" AND NOT EXISTS (SELECT DISTINCT TDA_CLE FROM TRADDATA WHERE TDA_CHAMP="PY_LIBELLE" AND TDA_LANG=CO_CODE AND TDA_CLE=PY_PAYS)
)
UNION
(
SELECT "PYS",
CO_CODE,
PY_PAYS,
PY_LIBELLE
FROM COMMUN
INNER JOIN PAYS ON CO_TYPE="TRA" AND NOT EXISTS (SELECT DISTINCT TDA_LANG FROM TRADDATA WHERE TDA_CHAMP="PY_LIBELLE" AND TDA_LANG=CO_CODE)
)) R
Then go to Settings.
File format
7. Regions - List of Regions by Country
Add the following request by clicking the button below:
SELECT "REG" AS TYPINF,
RG_PAYS AS COUNTRY,
RG_REGION AS "REGION CODE" ,
RG_LIBELLE AS DESCRIPTION
FROM REGION
Then go to Settings.
File format
8. Translation Languages - List of Cultural Profiles
Add the following request by clicking the button below:
SELECT CO_TYPE AS TYPINF,
CO_CODE AS CODE,
CO_LIBELLE AS DESCRIPTION
FROM COMMUN WHERE CO_TYPE="TRA"
Then go to Settings.
File format
9. Free Contact Tables (Contact)
C_LIBRECONTACTx: YYLIBRECONx (CHOIXEXT
with YX_TYPE = "LBx") is similar to the export of third-party free
table values in section 6, where LT% must be replaced by
LB%.
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(
SELECT TDT_TYPE AS TYPINF, CO_CODE AS LANG, CAST(TDT_CODE AS VARCHAR(3)) AS CODE, TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="YX" AND TDT_TYPE=TDT_TYPE AND TDT_LANG=CO_CODE
WHERE TDT_TYPE like "LB%" AND CO_TYPE="TRA"
UNION
(
SELECT DISTINCT YX_TYPE, CO_CODE, CAST(YX_CODE AS VARCHAR(3)), YX_LIBELLE
FROM COMMUN
INNER JOIN CHOIXEXT ON YX_TYPE like "LB%"
INNER JOIN TRADTT ON TDT_PREFIXE="YX" AND TDT_TYPE=YX_TYPE AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND YX_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="YX" AND TDT_TYPE=YX_TYPE AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT YX_TYPE, CO_CODE, CAST(YX_CODE AS VARCHAR(3)), YX_LIBELLE
FROM COMMUN
INNER JOIN CHOIXEXT ON YX_TYPE like "LB%"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="YX" AND TDT_TYPE=YX_TYPE)
)
) TABLETMP10. Coded Function (Contact)
C_FONCTIONCODEE: TTFONCTION (CHOIXCOD
with CC_TYPE = "FON") is similar to the export of civility values
in section 1, where CIV must be replaced by FON.
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(SELECT "FON" AS TYPINF,
CO_CODE AS LANG,
CAST(TDT_CODE AS VARCHAR(3)) AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="FON" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "FON",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="FON"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="FON" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="FON" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "FON",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="FON"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="FON")
)) R11. Service Code (Contact)
C_SERVICECODE: YYSERVICE (CHOIXCOD with
CC_TYPE = "SRV") is similar to the export of civility values in
section 1, where CIV must be replaced by SRV.
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(SELECT "SRV" AS TYPINF,
CO_CODE AS LANG,
CAST(TDT_CODE AS VARCHAR(3)) AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="SRV" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "SRV",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="SRV"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="SRV" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="SRV" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "SRV",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="SRV"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="SRV")
)) R12. Relationship (Contact)
C_LIPARENT: TTLIENPARENT (CHOIXCOD with
CC_TYPE = "LIP") is similar to the export of civility values in
section 1, where CIV must be replaced by LIP.
SELECT TYPINF,LANG,RTRIM(CODE) AS CODE,DESCRIPTION FROM
(SELECT "LIP" AS TYPINF,
CO_CODE AS LANG,
CAST(TDT_CODE AS VARCHAR(3)) AS CODE,
TDT_LIBELLE AS DESCRIPTION
FROM COMMUN
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="LIP" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA"
UNION
(
SELECT DISTINCT "LIP",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="LIP"
INNER JOIN TRADTT ON TDT_PREFIXE="CC" AND TDT_TYPE="LIP" AND TDT_LANG=CO_CODE
WHERE CO_TYPE="TRA" AND CC_CODE NOT IN (SELECT DISTINCT TDT_CODE FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="LIP" AND TDT_LANG=CO_CODE)
)
UNION
(
SELECT "LIP",
CO_CODE,
CAST(CC_CODE AS VARCHAR(3)),
CC_LIBELLE
FROM COMMUN
INNER JOIN CHOIXCOD ON CC_TYPE="LIP"
WHERE CO_TYPE="TRA" AND CO_CODE NOT IN (SELECT DISTINCT TDT_LANG FROM TRADTT WHERE TDT_PREFIXE="CC" AND TDT_TYPE="LIP")
)) RRelated to