Ces interfaces d'export extraient des informations de référence telles que la civilité, les formes juridiques, les pays et d'autres données de référence traduites dans différentes langues. Les données exportées peuvent ensuite être importées dans l'environnement CAPENCY ou DQE SOFTWARE, afin que les informations puissent être affichées selon le profil culturel de l'utilisateur.
Option de menu en Mode Place : Data Exchange > Data export > User-defined export > Request templates
1. Titres - Liste des civilités traduites par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
2. Forme juridique - Liste des formes juridiques traduites par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
3. Libellés - Liste des libellés de champs traduits par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
4. Langues - Liste des langues traduites par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
5. Nationalités - Liste des nationalités traduites par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
6. Pays - Liste des pays traduits par profil culturel
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
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
Allez ensuite dans Paramètres.
Format de fichier
7. Régions - Liste des régions par pays
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
SELECT "REG" AS TYPINF,
RG_PAYS AS COUNTRY,
RG_REGION AS "REGION CODE" ,
RG_LIBELLE AS DESCRIPTION
FROM REGION
Allez ensuite dans Paramètres.
Format de fichier
8. Langues de traduction - Liste des profils culturels
Ajoutez la requête suivante en cliquant sur le bouton ci-dessous :
SELECT CO_TYPE AS TYPINF,
CO_CODE AS CODE,
CO_LIBELLE AS DESCRIPTION
FROM COMMUN WHERE CO_TYPE="TRA"
Allez ensuite dans Paramètres.
Format de fichier
9. Tables libres de contact (Contact)
C_LIBRECONTACTx : YYLIBRECONx (CHOIXEXT
avec YX_TYPE = "LBx") est similaire à l'export des valeurs des tables libres tiers
de la section 6, où LT% doit être remplacé par
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. Fonction codifiée (Contact)
C_FONCTIONCODEE : TTFONCTION (CHOIXCOD
avec CC_TYPE = "FON") est similaire à l'export des valeurs de civilité
de la section 1, où CIV doit être remplacé par 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. Code service (Contact)
C_SERVICECODE : YYSERVICE (CHOIXCOD avec
CC_TYPE = "SRV") est similaire à l'export des valeurs de civilité de la
section 1, où CIV doit être remplacé par 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. Lien de parenté (Contact)
C_LIPARENT : TTLIENPARENT (CHOIXCOD avec
CC_TYPE = "LIP") est similaire à l'export des valeurs de civilité de la
section 1, où CIV doit être remplacé par 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")
)) RAssocié à