These various export interfaces extract primary information such as civility, legal form, countries
and so on, translated in different languages, to be imported into CAPENCY or DQE SOFTWARE
environment. This will enable the information to be displayed based on o user 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 following request by clicking on :
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 form translated by cultural profiles
Add following request by clicking on :
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 fields label translated by cultural profiles
Add following request by clicking on :
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 following request by clicking on :
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 following request by clicking on :
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 following request by clicking on :
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 following request by clicking on :
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 following request by clicking on :
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. Tables libre contact (Contact)
C_LIBRECONTACTx: YYLIBRECONx (CHOIXEXT with YX_TYPE = "LBx") → similar to the export of third-party free table values (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)
)
) TABLETMP
10. Coded function (Contact)
C_FONCTIONCODEE : TTFONCTION (CHOIXCOD with CC_TYPE = "FON") → similar to the export of civility values (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")
)) R
11. Sercice code (Contact)
C_SERVICECODE: YYSERVICE (CHOIXCOD with CC_TYPE = "SRV") → similar to the export of civility values (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")
)) R
12. Relationship (Contact)
C_LIPARENT: TTLIENPARENT (CHOIXCOD with CC_TYPE = "LIP") → similar to the export of civility values (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")
)) R
Related to