<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
    <NODE name="GETENV" label="TRAITEMENT ENVIRONNEMENT [FINESS] [FINESS_TEXTE]">
        <NODE name="PARAM" label="Paramètres">
            <sqlcmd><![CDATA[
            
            -- GHM --
            SELECT base.cti_copy_table
                (
                'dbname=[DBNAME] port=[DBPORT] host=[DBHOST] user=[DBUSERNAME] password=[DBPASSWORD]',
                'SELECT * FROM pmsi.t_ghm WHERE oid <> 0', 'temp e_ghm'
                )
            ;

            DROP TABLE IF EXISTS c_ghm
            ;

            CREATE TEMP TABLE c_ghm AS
                SELECT
                    e_ghm.oid AS oid_source,
                    coalesce(t_ghm.oid,0) AS oid
                FROM e_ghm
                    LEFT JOIN pmsi.t_ghm ON e_ghm.code = t_ghm.code
            ;

            ALTER TABLE c_ghm ADD CONSTRAINT c_ghm_pkey PRIMARY KEY(oid_source)
            ;

            -- GHS --
            SELECT base.cti_copy_table
                (
                'dbname=[DBNAME] port=[DBPORT] host=[DBHOST] user=[DBUSERNAME] password=[DBPASSWORD]',
                'SELECT oid, code, texte, borne_basse, borne_haute, dms_nationale FROM pmsi.t_ghs WHERE oid <> 0', 'temp e_ghs'
                )
            ;

            DROP TABLE IF EXISTS c_ghs
            ;

            CREATE TEMP TABLE c_ghs AS
                SELECT
                    e_ghs.oid AS oid_source,
                    coalesce(t_ghs.oid,0) AS oid
                FROM e_ghs
                    LEFT JOIN pmsi.t_ghs ON e_ghs.code = t_ghs.code
            ;

            ALTER TABLE c_ghs 
            ADD CONSTRAINT c_ghs_pkey 
            PRIMARY KEY(oid_source)
            ;

            ]]></sqlcmd>
        </NODE>
        <NODE name="PRODUCTION" label="Production">
            <sqlcmd><![CDATA[

            DELETE FROM pmsi.p_simulation_fg2223 
            WHERE finess = '[FINESS]'
            ;

            SELECT base.cti_copy_table
                (
                'dbname=[DBNAME] port=[DBPORT] host=[DBHOST] user=[DBUSERNAME] password=[DBPASSWORD]', 
                'SELECT p_simulation_fg2223.*
                 FROM pmsi.p_simulation_fg2223
                    JOIN pmsi.p_rss ON 1=1
                        AND p_simulation_fg2223.rss_id = p_rss.oid
                        AND etat <> ''S''',
                'temp e_simulation_fg2223'
                )
            ;

            SELECT base.cti_copy_table
                (
                'dbname=[DBNAME] port=[DBPORT] host=[DBHOST] user=[DBUSERNAME] password=[DBPASSWORD]',
                'SELECT *
                 FROM pmsi.p_rss
                 WHERE 1=1
                    AND mois_sortie >= 202103
                    AND etat <> ''S''',
                'temp e_rss'
                )
            ;

            DROP TABLE IF EXISTS c_rss
            ;

            CREATE TEMP TABLE c_rss AS
                SELECT
                    e_rss.oid AS oid_source,
                    MAX(COALESCE(p_rss.oid,0)) AS oid
                FROM e_rss 
                    JOIN pmsi.p_rss ON 1=1
                        AND p_rss.finess = '[FINESS]'
                        AND e_rss.no_rss = p_rss.no_rss
                        AND e_rss.date_sortie = p_rss.date_sortie 
                GROUP BY 1
            ;

            ALTER TABLE c_rss
            ADD CONSTRAINT c_rss_pkey
            PRIMARY KEY(oid_source)
            ;




                INSERT INTO pmsi.p_simulation_fg2223
                    (
                        finess, 
                        rss_id, 
                        ghm_fg0_id, 
                        ghs_fg0_id, 
                        ghm_fg1_id,       
                        ghs_fg1_id, 
                        annee_sortie, 
                        coefficient_mco_fg0, 
                        coefficient_mco_fg1, 
                        commentaire, 
                        erreur, 
                        est_envoye, 
                        est_regroupe, 
                        ghm_fg1_code, 
                        ghs_fg1_borne_basse, 
                        ghs_fg1_borne_haute, 
                        ghs_fg1_code, 
                        ghs_fg1_forfait_exb, 
                        ghs_fg1_tarif_exb, 
                        ghs_fg1_tarif_exh, 
                        ghs_fg1_tarif_ghs, 
                        ghs_minore_code, 
                        ghs_minore_text, 
                        montant_9604_fg0, 
                        montant_9604_fg1, 
                        montant_9605_fg0, 
                        montant_9605_fg1, 
                        montant_ant_fg0, 
                        montant_ant_fg1, 
                        montant_emi_fg0, 
                        montant_emi_fg1, 
                        montant_emi_fg1_deremboursees, 
                        montant_eti_fg0, 
                        montant_eti_fg1, 
                        montant_eti_fg1_deremboursees, 
                        montant_exb_fg0, 
                        montant_exb_fg1, 
                        montant_exh_fg0, 
                        montant_exh_fg1, 
                        montant_ghs_theorique_fg0, 
                        montant_ghs_theorique_fg1, 
                        montant_lpp_fg0, 
                        montant_lpp_fg1, 
                        montant_lpp_fg1_deremboursees, 
                        montant_nn1_fg0, 
                        montant_nn1_fg1, 
                        montant_nn2_fg0, 
                        montant_nn2_fg1, 
                        montant_nn3_fg0, 
                        montant_nn3_fg1, 
                        montant_rap_fg0, 
                        montant_rap_fg1, 
                        montant_rea_fg0, 
                        montant_rea_fg1, 
                        montant_rep_fg0, 
                        montant_rep_fg1, 
                        montant_src_eligibles_fg1, 
                        montant_src_fg0, 
                        montant_src_fg1, 
                        montant_stf_fg0, 
                        montant_stf_fg1, 
                        montant_tde_fg0,
                        montant_tde_fg1,
                        montant_tse_fg0,
                        montant_tse_fg1,
                        montant_ucd_fg0, 
                        montant_ucd_fg1,
                        montant_ucd_fg1_deremboursees, 
                        nb_9604_fg0, 
                        nb_9604_fg1, 
                        nb_9605_fg0, 
                        nb_9605_fg1, 
                        nb_ant_fg0, 
                        nb_ant_fg1, 
                        nb_exb_fg1, 
                        nb_exh_fg0, 
                        nb_exh_fg1, 
                        nb_nn1_fg0, 
                        nb_nn1_fg1, 
                        nb_nn2_fg0, 
                        nb_nn2_fg1, 
                        nb_nn3_fg0, 
                        nb_nn3_fg1, 
                        nb_rap_fg0, 
                        nb_rap_fg1, 
                        nb_rea_fg0, 
                        nb_rea_fg1, 
                        nb_rep_fg0, 
                        nb_rep_fg1, 
                        nb_sej_exb_fg0, 
                        nb_sej_exb_fg1, 
                        nb_sej_src_eligibles_fg1, 
                        nb_src_eligibles_fg1, 
                        nb_src_fg0, 
                        nb_src_fg1, 
                        nb_stf_fg0, 
                        nb_stf_fg1, 
                        nb_ucd_fg0, 
                        no_rss, 
                        regle_src_eligible_fg1, 
                        tarif_9604_fg1, 
                        tarif_9605_fg1, 
                        tarif_ant_fg1, 
                        tarif_nn1_fg1, 
                        tarif_nn2_fg1, 
                        tarif_nn3_fg1, 
                        tarif_rap_fg1, 
                        tarif_rea_fg1, 
                        tarif_src_fg1, 
                        tarif_stf_fg1, 
                        tarif_tde_fg0,
                        tarif_tde_fg1,
                        tarif_tse_fg0,
                        tarif_tse_fg1,
                        translation, 
                        nb_sdc_fg1, 
                        tarif_sdc_fg1, 
                        nb_sdc_fg0, 
                        tarif_sdc_fg0, 
                        montant_sdc_fg1, 
                        montant_sdc_fg0,
                        is_raac
                    )
                    SELECT
                        '[FINESS]' AS finess,
                        COALESCE(c_rss.oid,0) AS rss_id,
                        COALESCE(c_ghm_0.oid,0) AS ghm_fg0_id,
                        COALESCE(c_ghs_0.oid,0) AS ghs_fg0_id,
                        COALESCE(c_ghm_1.oid,0) AS ghm_fg1_id,
                        COALESCE(c_ghs_1.oid,0) AS ghs_fg1_id,
                        annee_sortie, 
                        coefficient_mco_fg0, 
                        coefficient_mco_fg1, 
                        commentaire, 
                        erreur, 
                        est_envoye, 
                        est_regroupe, 
                        ghm_fg1_code, 
                        ghs_fg1_borne_basse, 
                        ghs_fg1_borne_haute, 
                        ghs_fg1_code, 
                        ghs_fg1_forfait_exb, 
                        ghs_fg1_tarif_exb, 
                        ghs_fg1_tarif_exh, 
                        ghs_fg1_tarif_ghs, 
                        ghs_minore_code, 
                        ghs_minore_text, 
                        montant_9604_fg0, 
                        montant_9604_fg1, 
                        montant_9605_fg0, 
                        montant_9605_fg1, 
                        montant_ant_fg0, 
                        montant_ant_fg1, 
                        montant_emi_fg0, 
                        montant_emi_fg1, 
                        montant_emi_fg1_deremboursees, 
                        montant_eti_fg0, 
                        montant_eti_fg1, 
                        montant_eti_fg1_deremboursees, 
                        montant_exb_fg0, 
                        montant_exb_fg1, 
                        montant_exh_fg0, 
                        montant_exh_fg1, 
                        montant_ghs_theorique_fg0, 
                        montant_ghs_theorique_fg1, 
                        montant_lpp_fg0, 
                        montant_lpp_fg1, 
                        montant_lpp_fg1_deremboursees, 
                        montant_nn1_fg0, 
                        montant_nn1_fg1, 
                        montant_nn2_fg0, 
                        montant_nn2_fg1, 
                        montant_nn3_fg0, 
                        montant_nn3_fg1, 
                        montant_rap_fg0, 
                        montant_rap_fg1, 
                        montant_rea_fg0, 
                        montant_rea_fg1, 
                        montant_rep_fg0, 
                        montant_rep_fg1, 
                        montant_src_eligibles_fg1, 
                        montant_src_fg0, 
                        montant_src_fg1, 
                        montant_stf_fg0, 
                        montant_stf_fg1, 
                        montant_tde_fg0,
                        montant_tde_fg1,
                        montant_tse_fg0,
                        montant_tse_fg1,
                        montant_ucd_fg0, 
                        montant_ucd_fg1,
                        montant_ucd_fg1_deremboursees, 
                        nb_9604_fg0, 
                        nb_9604_fg1, 
                        nb_9605_fg0, 
                        nb_9605_fg1, 
                        nb_ant_fg0, 
                        nb_ant_fg1, 
                        nb_exb_fg1, 
                        nb_exh_fg0, 
                        nb_exh_fg1, 
                        nb_nn1_fg0, 
                        nb_nn1_fg1, 
                        nb_nn2_fg0, 
                        nb_nn2_fg1, 
                        nb_nn3_fg0, 
                        nb_nn3_fg1, 
                        nb_rap_fg0, 
                        nb_rap_fg1, 
                        nb_rea_fg0, 
                        nb_rea_fg1, 
                        nb_rep_fg0, 
                        nb_rep_fg1, 
                        nb_sej_exb_fg0, 
                        nb_sej_exb_fg1, 
                        nb_sej_src_eligibles_fg1, 
                        nb_src_eligibles_fg1, 
                        nb_src_fg0, 
                        nb_src_fg1, 
                        nb_stf_fg0, 
                        nb_stf_fg1, 
                        nb_ucd_fg0, 
                        no_rss, 
                        regle_src_eligible_fg1, 
                        tarif_9604_fg1, 
                        tarif_9605_fg1, 
                        tarif_ant_fg1, 
                        tarif_nn1_fg1, 
                        tarif_nn2_fg1, 
                        tarif_nn3_fg1, 
                        tarif_rap_fg1, 
                        tarif_rea_fg1, 
                        tarif_src_fg1, 
                        tarif_stf_fg1, 
                        tarif_tde_fg0,
                        tarif_tde_fg1,
                        tarif_tse_fg0,
                        tarif_tse_fg1,
                        translation, 
                        nb_sdc_fg1, 
                        tarif_sdc_fg1, 
                        nb_sdc_fg0, 
                        tarif_sdc_fg0, 
                        montant_sdc_fg1, 
                        montant_sdc_fg0,
                        is_raac
                    FROM e_simulation_fg2223
                        JOIN c_rss ON rss_id = c_rss.oid_source
                        LEFT JOIN c_ghm c_ghm_0 ON ghm_fg0_id = c_ghm_0.oid_source
                        LEFT JOIN c_ghs c_ghs_0 ON ghs_fg0_id = c_ghs_0.oid_source
                        LEFT JOIN c_ghm c_ghm_1 ON ghm_fg1_id = c_ghm_1.oid_source
                        LEFT JOIN c_ghs c_ghs_1 ON ghs_fg1_id = c_ghs_1.oid_source
                ;   

                                        
            ]]></sqlcmd>
        </NODE>
    </NODE>
</ROOT>
