You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
92 lines
3.7 KiB
92 lines
3.7 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
BEGIN
|
|
UPDATE base.t_ucd SET texte_court = texte WHERE COALESCE(texte_court,'') = '';
|
|
|
|
INSERT INTO base.t_ucd(oid, code, texte, texte_court)
|
|
SELECT 0, chr(127) || '*******', chr(127) || 'Non renseigné', chr(127) || 'Non renseigné'
|
|
WHERE 0 NOT IN (SELECT oid FROM base.t_ucd);
|
|
INSERT INTO eco.t_ucd_c (oid, code, texte, texte_court)
|
|
SELECT oid, code, texte, texte_court
|
|
FROM base.t_ucd
|
|
WHERE oid NOT IN (SELECT oid FROM eco.t_ucd_c);
|
|
UPDATE eco.t_ucd_c SET
|
|
code = t_ucd.code,
|
|
code_ucd13 = sub.code_ucd13,
|
|
texte = t_ucd.texte,
|
|
texte_court = t_ucd.texte_court
|
|
FROM base.t_ucd
|
|
LEFT JOIN (
|
|
SELECT ucd_id, code_ucd13 FROM eco.t_articles WHERE ucd_id != 0 AND code_ucd13 != '' GROUP BY 1,2
|
|
) sub ON sub.ucd_id = oid
|
|
WHERE t_ucd.oid = t_ucd_c.oid AND
|
|
(
|
|
t_ucd_c.code IS DISTINCT FROM t_ucd.code OR
|
|
t_ucd_c.code_ucd13 IS DISTINCT FROM sub.code_ucd13 OR
|
|
t_ucd_c.texte IS DISTINCT FROM t_ucd.texte OR
|
|
t_ucd_c.texte_court IS DISTINCT FROM t_ucd.texte_court
|
|
);
|
|
|
|
-- Synchronisation des différentes colonnes concernant le code UCD13 dans les différentes tables
|
|
DROP TABLE IF EXISTS w_liste_codes_ucd
|
|
;
|
|
|
|
CREATE TEMP TABLE w_liste_codes_ucd AS
|
|
SELECT
|
|
t_ucd.oid AS ucd_id,
|
|
t_ucd.code AS t_ucd_code_ucd7,
|
|
t_ucd_c.code AS t_ucd_c_code_ucd7,
|
|
CASE WHEN t_ucd_c.code_ucd13 = '' THEN NULL ELSE t_ucd_c.code_ucd13 END AS t_ucd_c_code_ucd13,
|
|
t_ucd_c.code AS t_articles_code_ucd7,
|
|
CASE WHEN t_articles.code_ucd13 = '' THEN NULL ELSE t_articles.code_ucd13 END AS t_articles_code_ucd13,
|
|
t_ucd_c.code AS t_article_fournisseur_ucd7,
|
|
CASE WHEN t_article_fournisseur.code_ucd13 = '' THEN NULL ELSE t_article_fournisseur.code_ucd13 END AS t_article_fournisseur_code_ucd13
|
|
FROM base.t_ucd
|
|
LEFT JOIN eco.t_ucd_c ON t_ucd_c.oid = t_ucd.oid
|
|
LEFT JOIN eco.t_articles ON t_articles.ucd_id > 0 AND t_articles.ucd_id = t_ucd.oid AND t_articles.code_ucd13 <> ''
|
|
LEFT JOIN eco.t_article_fournisseur ON t_article_fournisseur.ucd_id > 0 AND t_article_fournisseur.ucd_id = t_ucd.oid AND t_article_fournisseur.code_ucd13 <> ''
|
|
;
|
|
|
|
UPDATE eco.t_ucd_c
|
|
SET code_ucd13 = COALESCE(t_articles_code_ucd13, t_article_fournisseur_code_ucd13)
|
|
FROM w_liste_codes_ucd
|
|
WHERE w_liste_codes_ucd.ucd_id = t_ucd_c.oid
|
|
;
|
|
|
|
UPDATE eco.t_articles
|
|
SET code_ucd13 = COALESCE(t_ucd_c_code_ucd13, t_article_fournisseur_code_ucd13)
|
|
FROM w_liste_codes_ucd
|
|
WHERE w_liste_codes_ucd.ucd_id = t_articles.ucd_id
|
|
;
|
|
|
|
UPDATE eco.t_article_fournisseur
|
|
SET code_ucd13 = COALESCE(t_ucd_c_code_ucd13, t_articles_code_ucd13)
|
|
FROM w_liste_codes_ucd
|
|
WHERE w_liste_codes_ucd.ucd_id = t_article_fournisseur.ucd_id
|
|
;
|
|
|
|
UPDATE base.t_lpp SET texte_court = texte WHERE COALESCE(texte_court,'') = '';
|
|
INSERT INTO base.t_lpp(oid, code, texte, texte_court)
|
|
SELECT 0, chr(127) || '*******', chr(127) || 'Non renseigné', chr(127) || 'Non renseigné'
|
|
WHERE 0 NOT IN (SELECT oid FROM base.t_lpp);
|
|
INSERT INTO eco.t_lpp_c (oid, code, texte, texte_court)
|
|
SELECT oid, code, texte, texte_court
|
|
FROM base.t_lpp
|
|
WHERE oid NOT IN (SELECT oid FROM eco.t_lpp_c);
|
|
UPDATE eco.t_lpp_c SET
|
|
code = t_lpp.code,
|
|
texte = t_lpp.texte,
|
|
texte_court = t_lpp.texte_court
|
|
FROM base.t_lpp
|
|
WHERE t_lpp.oid = t_lpp_c.oid AND
|
|
(
|
|
t_lpp_c.code IS DISTINCT FROM t_lpp.code OR
|
|
t_lpp_c.texte IS DISTINCT FROM t_lpp.texte OR
|
|
t_lpp_c.texte_court IS DISTINCT FROM t_lpp.texte_court
|
|
);
|
|
|
|
RETURN 'OK';
|
|
END;
|