return: text
lang: plpgsql
parameters:
  p0:
    type: text
    name: i_table_code
src: |
  DECLARE	
  	result TEXT;
  BEGIN
    
    -- Si public, réplication des classes de prestations PMSI
    IF (i_table_code = 'PRESTA_C' OR i_table_code = '*ALL') THEN
    IF EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'pmsi' AND tablename = 't_classes') THEN
    IF EXISTS (SELECT * FROM activite.p_sejours WHERE est_budget_global = '1' LIMIT 1) THEN
      UPDATE activite.t_classes
      SET code = 'CTI_RAFA_P'
      WHERE code LIKE 'CTI\_RAFA\_P%' AND
        code <> 'CTI_RAFA_P'
        ;

      DROP TABLE IF EXISTS w_classes_pmsi
      ;
      CREATE TEMP TABLE w_classes_pmsi AS
      SELECT 
        t_listes_tables.oid AS table_id,
        0::bigint AS classe_id,
        t_classes.code AS classe_code,
        t_classes.texte AS classe_texte,
        0::bigint AS section_id,
        t_classes_sections.code AS section_code,
        t_classes_sections.texte AS section_texte,
        t_prestations.code::text AS prestation_code,
        t_prestations_activite.oid AS prestation_id
      FROM pmsi.t_classes
      JOIN pmsi.t_classes_sections ON classe_id = t_classes.oid
      JOIN pmsi.t_classes_sections_elements ON section_id = t_classes_sections.oid
      JOIN pmsi.t_prestations ON to_id = t_prestations.oid
      JOIN activite.t_prestations t_prestations_activite ON t_prestations_activite.code = t_prestations.code
      JOIN activite.t_listes_tables ON t_listes_tables.code = 'PRESTA_C'
      WHERE t_classes.code IN ('CTI_RAFA_P','CTI_ARRETEVERS')
      ;

      INSERT INTO activite.t_classes(
        code, 
        texte, 
        table_id, 
        is_cti, 
        sequence, 
        contenu_by_code, 
        contenu_non_replicable_ailleurs
        )
        SELECT w_classes_pmsi.classe_code,
        w_classes_pmsi.classe_texte,
        w_classes_pmsi.table_id,
        '1'::text AS is_cti,
        (SELECT MAX(t_classes.sequence) FROM activite.t_classes WHERE t_classes.table_id = w_classes_pmsi.table_id AND t_classes.sequence < 30) + row_number() OVER (ORDER BY w_classes_pmsi.classe_code) ,
        '0'::text AS contenu_by_code, 
        '0'::text AS contenu_non_replicable_ailleurs
      FROM w_classes_pmsi
      LEFT JOIN activite.t_classes ON
        w_classes_pmsi.classe_code = t_classes.code AND
        w_classes_pmsi.table_id = t_classes.table_id
      WHERE t_classes.oid IS NULL
      GROUP BY 1,2,3
      ;

      UPDATE w_classes_pmsi
      SET classe_id = t_classes.oid
      FROM activite.t_classes
      WHERE t_classes.code = classe_code AND
        t_classes.table_id = w_classes_pmsi.table_id 
      ;

      INSERT INTO w_classes_pmsi
      SELECT 
        t_classes.table_id,
        t_classes.oid AS classe_id,
        t_classes.code AS classe_code,
        t_classes.texte AS classe_texte,
        0::bigint AS section_id,
        '99'::text AS section_code,
        'Autres prestations'::text AS section_texte,
        t_prestations.code AS prestation_code,
        t_prestations.oid AS prestation_id
      FROM activite.t_prestations
      JOIN activite.t_classes ON t_classes.oid IN (SELECT classe_id FROM w_classes_pmsi)
      LEFT JOIn w_classes_pmsi ON
        w_classes_pmsi.classe_id = t_classes.oid AND
        w_classes_pmsi.prestation_id = t_prestations.oid
      WHERE w_classes_pmsi.prestation_id IS NULL
      ;

      INSERT INTO activite.t_classes_sections(
        code, 
        texte, 
        classe_id, 
        condition, 
        include_code, 
        exclude_code
        )
      SELECT 
        w_classes_pmsi.section_code,
        w_classes_pmsi.section_texte,
        w_classes_pmsi.classe_id,
        ''::text AS condition, 
        ''::text AS include_code,
        ''::text AS exclude_code
      FROM w_classes_pmsi
      LEFT JOIN activite.t_classes_sections ON 
        t_classes_sections.classe_id = w_classes_pmsi.classe_id AND
        t_classes_sections.code = w_classes_pmsi.section_code
      WHERE w_classes_pmsi.classe_id <> 0 AND
        t_classes_sections IS NULL
      GROUP BY 1,2,3
      ORDER BY 1
      ;

      UPDATE w_classes_pmsi
      SET section_id = t_classes_sections.oid
      FROM activite.t_classes_sections
      WHERE t_classes_sections.classe_id = w_classes_pmsi.classe_id AND
        t_classes_sections.code = w_classes_pmsi.section_code
      ;

      DELETE
      FROM activite.t_classes_sections
      USING activite.t_classes 
      WHERE classe_id = t_classes.oid AND
        t_classes.oid IN (SELECT classe_id FROM w_classes_pmsi) AND
        t_classes.oid::text||'-'||t_classes_sections.oid::text NOT IN (SELECT classe_id::text||'-'||section_id::text FROm w_classes_pmsi)
      ;

      UPDATE activite.t_classes_sections
      SET texte = section_texte
      FROM w_classes_pmsi
      WHERE t_classes_sections.oid = section_id AND
        t_classes_sections.texte IS DISTINCT FROM section_texte
      ;

      DELETE FROM activite.t_classes_sections_elements
      USING
      (
      SELECT t_classes_sections_elements.section_id, to_id
      FROM activite.t_classes_sections_elements
      JOIN activite.t_classes_sections ON section_id = t_classes_sections.oid
      JOIN w_classes_pmsi ON 
        t_classes_sections.classe_id = w_classes_pmsi.classe_id AND
        t_classes_sections_elements.to_id = w_classes_pmsi.prestation_id AND
        t_classes_sections.oid <> w_classes_pmsi.section_id
      GROUP BY 1,2
      ) subview
      WHERE t_classes_sections_elements.section_id = subview.section_id AND
        t_classes_sections_elements.to_id = subview.to_id
      ;

      INSERT INTO activite.t_classes_sections_elements (
        section_id,
        to_id
        )
      SELECT section_id, prestation_id AS to_id
      FROM w_classes_pmsi
      LEFT JOIN 
        (
        SELECT classe_id, to_id
        FROM activite.t_classes_sections_elements
        JOIN activite.t_classes_sections ON section_id = t_classes_sections.oid
        GROUP BY 1,2
        ) subview ON w_classes_pmsi.classe_id = subview.classe_id AND
          w_classes_pmsi.prestation_id = subview.to_id
      WHERE w_classes_pmsi.classe_id <> 0
        AND w_classes_pmsi.section_id <> 0 AND
        subview.to_id IS NULL
      GROUP BY 1,2	
      ;

      DELETE
      FROM activite.t_classes_sections
      USING activite.t_classes 
      WHERE classe_id = t_classes.oid AND
        t_classes.oid IN (SELECT classe_id FROM w_classes_pmsi) AND
        t_classes.oid::text||'-'||t_classes_sections.oid::text NOT IN (SELECT classe_id::text||'-'||section_id::text FROm w_classes_pmsi)
      ;

    END IF;
    END IF;
    END IF;

    -- Si Toutes les tables, mettre à jour oid par codes 
  		IF (i_table_code = '*ALL') THEN
  			PERFORM base.cti_initialize_classes_by_code('activite');
  			PERFORM base.cti_initialize_listes_by_code('activite');
  		END IF;

    IF (i_table_code = 'SERVICE_F' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'SERVICE_F','service_facturation_section');
    END IF;
    IF (i_table_code = 'SERVICE_F' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_services_facturation', 'SERVICE_F','');
    END IF;
    IF (i_table_code = 'ETAGE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'ETAGE','etage_section');
    END IF;
    IF (i_table_code = 'ETAGE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_etages', 'ETAGE','');
    END IF;
    IF (i_table_code = 'LIT' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lits', 'LIT','');
    END IF;
    IF (i_table_code = 'LIT' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'LIT','lit_section');
    END IF;
    IF (i_table_code = 'UM' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_unites_medicales', 'UM','');
    END IF;
    IF (i_table_code = 'UM' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'UM','unite_medicale_section');
    END IF;
    IF (i_table_code = 'UF' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'UF','unite_fonctionnelle_section');
    END IF;
    IF (i_table_code = 'UF' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_unites_fonctionnelles', 'UF','');
    END IF;
    IF (i_table_code = 'MEDECIN' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_medecins_administratifs_c', 'MEDECIN','');
    END IF;
    IF (i_table_code = 'PRESTA_C' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_prestations', 'PRESTA_C','');
    END IF;
    IF (i_table_code = 'RUBRIQUE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_rubriques_facturation', 'RUBRIQUE','');
    END IF;
    IF (i_table_code = 'TIERSP' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_tiers_payant', 'TIERSP','');
    END IF;
    IF (i_table_code = 'FINESS' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('base', 't_finess', 'FINESS','');
      UPDATE activite.t_finess_c SET 
        section_id = t_finess.section_id,
        section_code = t_finess.section_code,
        section_texte = t_finess.section_texte
      FROM base.t_finess
      WHERE t_finess.oid = t_finess_c.oid AND
        (
          t_finess_c.section_id IS DISTINCT FROM t_finess.section_id OR
          t_finess_c.section_code IS DISTINCT FROM t_finess.section_code OR
          t_finess_c.section_texte IS DISTINCT FROM t_finess.section_texte
        )
      ;
    END IF;
    IF (i_table_code = 'ACTE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_actes_c', 'ACTE','');
    END IF;
    IF (i_table_code = 'GHM' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_ghm_c', 'GHM','ghm_section');
    END IF;		
    IF (i_table_code = 'GME' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_gme_c', 'GME','gme_section');
    END IF;
    IF (i_table_code = 'GHMGME' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_ghmgme_c', 'GHMGME','ghmgme_section');
    END IF;
    IF (i_table_code = 'COMPTE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_compte', 'COMPTE', '');
    END IF;
    IF (i_table_code = 'DURSEJ' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_durees_sejour_c', 'DURSEJ','');
    END IF;
    IF (i_table_code = 'AGE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_ages_c', 'AGE','');
    END IF;
    IF (i_table_code = 'CODEPOST' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_codes_postaux_c', 'CODEPOST','');
    END IF;
    IF (i_table_code = 'REJETNOEMI' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_rejets_noemie', 'REJETNOEMI','');
    END IF;
    IF (i_table_code = 'BUDGETCLE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_budget_cle', 'BUDGETCLE','');
    END IF;
    IF (i_table_code = 'FORMEACTIVITE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_forme_activite', 'FORMEACTIVITE','');
    END IF;		
    IF (i_table_code = 'ETATSEJOUR' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_etat_sejour', 'ETATSEJOUR','');
    END IF;	
    IF (i_table_code = 'ACTIVITE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_activites', 'ACTIVITE','');
    END IF;
    IF (i_table_code = 'ACTIVITE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('activite', 't_lieux_c', 'ACTIVITE','activite_section');
    END IF;
    IF (i_table_code = 'POLE' OR i_table_code = '*ALL') THEN
      PERFORM base.cti_update_classes('base', 't_pole', 'POLE','');
      UPDATE activite.t_pole SET 
        section_id = t_pole.section_id,
        section_code = t_pole.section_code,
        section_texte = t_pole.section_texte
      FROM base.t_pole as t_pole_base
      WHERE t_pole.oid = t_pole_base.oid AND
        (
          t_pole.section_id IS DISTINCT FROM t_pole_base.section_id OR
          t_pole.section_code IS DISTINCT FROM t_pole_base.section_code OR
          t_pole.section_texte IS DISTINCT FROM t_pole_base.section_texte
        )
      ;
    END IF;
    
    RETURN 'OK';
  END;