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;