<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>

    <NODE name="PMSICOMPLEMENTS" label="RECUPERATION DES DONNEES COMPLEMENTAIRES PMSI">

        <NODE label="Paramètres">
            <sqlcmd><![CDATA[

                INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
                SELECT
                    COD_PRA,
                    NOM_PRA,
                    PRE_PRA,
                    COALESCE(t_specialites_medecin.oid,0),
                    NID_PRA,
                    COALESCE(t_specialites_internes_medecin.oid,0)
                FROM prod_sigems.PRATICIEN
                    LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = CSP_PRA
                    LEFT JOIN pmsi.t_specialites_internes_medecin ON (t_specialites_internes_medecin.code = CSP_PRA || '..')
                    LEFT JOIN pmsi.t_medecins ON COD_PRA = t_medecins.code
                WHERE t_medecins.code IS NULL;

                UPDATE pmsi.t_unites_medicales
                SET texte = lib
                FROM prod_sigems.unite_medicale
                WHERE t_unites_medicales.code = unite_medicale.code AND
                    texte <> lib;

                INSERT INTO pmsi.t_unites_medicales(code, texte)
                 SELECT
                    UNITE_MEDICALE.CODE,
                    MAX(UPPER(UNITE_MEDICALE.LIB))
                FROM
                    prod_sigems.UNITE_MEDICALE
                    LEFT JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = UNITE_MEDICALE.CODE
                WHERE t_unites_medicales.code IS NULL
                GROUP BY UNITE_MEDICALE.CODE
				ORDER BY UNITE_MEDICALE.CODE;
				
                
				UPDATE pmsi.t_diagnostics SET
					texte = CIM10.LIBELL
				FROM
					prod_sigems.CIM10
				WHERE 1=1
					AND t_diagnostics.code = t_diagnostics.texte
					AND t_diagnostics.code = CIM10.DIAG
					AND t_diagnostics.texte != CIM10.LIBELL;

				-- maj des établissements insérés juste avec le finess
				UPDATE base.t_etablissements
				SET
					texte = NOM,
					texte_court = NOM
				FROM 
					prod_sigems.ETAB_CONV
				WHERE 1=1 AND
					ETAB_CONV.NUM_FINESS =  t_etablissements.code AND 
					t_etablissements.code = t_etablissements.texte
				;
            ]]></sqlcmd>
        </NODE>

        <NODE label="Identités et médecin">
            <sqlcmd><![CDATA[

                -- D'après RSS
                DROP TABLE IF EXISTS w_RSS;
                CREATE TEMP TABLE w_RSS AS
                SELECT ANNEE,
                    CODE,
                    ANNEE || to_char(CODE,'FM900000') AS ANNEE_CODE,
                    ENFANT,
                    MAX(NUMERO_RSS) AS NUMERO_RSS,
                    date(MAX(ENTREE)) AS ENTREE,
                    date(MAX(SORTIE)) AS SORTIE,
                    MAX(COALESCE(NAISSANCES.NOM,CASE WHEN NMA_PAT <> '' THEN NMA_PAT ELSE NOM_PAT END)) AS NOM_PAT,
                    MAX(COALESCE(NAISSANCES.NOM,NOM_PAT)) AS NNA_PAT,
                    MAX(COALESCE(NAISSANCES.PRENOM,PRE_PAT)) AS PRE_PAT,
                    MAX(CCH_DOS) AS CCH_DOS,
                    MAX(CPH_DOS) AS CPH_DOS
                FROM prod_sigems.ETAT_PMSI
				    JOIN prod_sigems.DOSSIER ON trim(ANN_DOS) || trim(COD_DOS) = trim(ETAT_PMSI.ANNEE) || trim(ETAT_PMSI.CODE)
                    JOIN prod_sigems.PATIENT ON DOSSIER.CPH_DOS = PATIENT.COD_PAT
                    LEFT JOIN prod_sigems.NAISSANCES ON ANNEE = NAISSANCES.ANN AND CODE = NAISSANCES.COD AND ENFANT = NAISSANCES.NUM_RUM - 50
                WHERE NUMERO_RSS <> 0
                GROUP BY ANNEE, CODE, ENFANT;

                CREATE INDEX w_RSS_i1
                  ON w_RSS
                  USING btree
                  (NUMERO_RSS);

                CREATE INDEX w_RSS_i2
                  ON w_RSS
                  USING btree
                  (CCH_DOS);

                CREATE INDEX w_RSS_i3
                  ON w_RSS
                  USING btree
                  (ANNEE_CODE);


                UPDATE [SCHEMA].p_identites
                SET nom = NOM_PAT,
                    nom_naissance = NNA_PAT,
                    prenom = PRE_PAT
                FROM [SCHEMA].p_rss
                JOIN w_RSS ON NUMERO_RSS = no_rss
                WHERE p_identites.rss_id = p_rss.oid AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    (
                        nom IS DISTINCT FROM NOM_PAT OR
                        nom_naissance IS DISTINCT FROM NNA_PAT OR
                        prenom IS DISTINCT FROM PRE_PAT
                    );


                UPDATE [SCHEMA].p_rss
                SET medecin_rss_id = COALESCE(t_medecins.oid,0),
                    no_patient = CPH_DOS
                FROM w_RSS
                LEFT JOIN pmsi.t_medecins ON CCH_DOS = t_medecins.code
                WHERE NUMERO_RSS = no_rss AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    (
                    medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
                    no_patient IS DISTINCT FROM CPH_DOS
                    );



                -- D'après numéro de séjour

                -- au préalable, on reformate les numéros de séjour sur 7 ou 8 caractères. Il se peut que les numéros soient sur 16,17 ou 18 caractères,
                -- ce qui ne colle pas avec l'activité. S'ils sont sur 16, on dégage les 8 derniers caractères qui sont une date, 
                -- si c'est sur 17, il faut ignorer le premier caractère et sortir les 9 derniers caractères
                -- si c'est sur 18, il faut sortir la date et deux caractères supplémentaires (je ne sais pas ce qu'ils font là)
                -- la table w_new permet de passer sur 7 caractères les dossiers qui ont un 0 en troisième caractère pour coller avec l'activité.
                WITH w_brut AS (
                SELECT
                    oid as rss_id,
                    CASE
                        WHEN char_length(no_sejour_administratif) = 16 THEN substring(no_sejour_administratif from 1 for char_length(no_sejour_administratif) - 8)
                        WHEN char_length(no_sejour_administratif) = 17 THEN substring(no_sejour_administratif from 2 for char_length(no_sejour_administratif) - 9)
                        WHEN char_length(no_sejour_administratif) = 18 THEN substring(no_sejour_administratif from 1 for char_length(no_sejour_administratif) - 10)
                        ELSE no_sejour_administratif END as no_sejour_brut
                FROM
                    [SCHEMA].p_rss
                WHERE
                    char_length(no_sejour_administratif) > 7
                ),
                w_new AS (
                SELECT
                    rss_id,
                    no_sejour_brut
                    ,CASE WHEN substring(no_sejour_brut from 3 for 1) = '0' THEN substr(no_sejour_brut, 1, 2) || substr(no_sejour_brut, 4, 5) ELSE no_sejour_brut END as no_sejour_new
                FROM w_brut
                ORDER BY w_brut.no_sejour_brut DESC
                )
                UPDATE [SCHEMA].p_rss SET no_sejour_administratif = w_new.no_sejour_new 
                FROM w_new
                WHERE 1=1
                    AND p_rss.oid = w_new.rss_id
                    AND p_rss.no_sejour_administratif != w_new.no_sejour_new
                    AND (SELECT count(*) FROM pg_tables where tablename LIKE 'hl7%' AND schemaname = 'prod_sigems') = 0;
                

                DROP TABLE IF EXISTS w_DOSSIER;
                CREATE TEMP TABLE w_DOSSIER AS
                SELECT ANN_DOS AS ANNEE,
                    COD_DOS AS CODE,
                    ANN_DOS || to_char(COD_DOS,'FM900000') AS ANNEE_CODE,
                    date(MAX(DEN_DOS)) AS ENTREE,
                    date(MAX(DSO_DOS)) AS SORTIE,
                    MAX(COALESCE(CASE WHEN NMA_PAT <> '' THEN NMA_PAT ELSE NOM_PAT END,'')) AS NOM_PAT,
                    MAX(COALESCE(NOM_PAT,'')) AS NNA_PAT,
                    MAX(COALESCE(PRE_PAT,'')) AS PRE_PAT,
                    MAX(CCH_DOS) AS CCH_DOS,
                    MAX(CPH_DOS) AS CPH_DOS,
                    MAX(substr(CPO_PAT,1,5)) AS CPO_PAT
                FROM prod_sigems.DOSSIER
                    LEFT JOIN prod_sigems.PATIENT ON DOSSIER.CPH_DOS = PATIENT.COD_PAT
                GROUP BY ANN_DOS, COD_DOS;


                CREATE INDEX w_DOSSIER_i1
                  ON w_DOSSIER
                  USING btree
                  (CCH_DOS);

                CREATE INDEX w_DOSSIER_i2
                  ON w_DOSSIER
                  USING btree
                  (ANNEE_CODE);

                -- Externes
                UPDATE [SCHEMA].p_identites
                SET nom = NOM_PAT,
                    nom_naissance = NNA_PAT,
                    prenom = PRE_PAT
                FROM [SCHEMA].p_rss
                JOIN w_DOSSIER ON ANNEE_CODE = no_sejour_administratif
                WHERE NOM_PAT <> '' AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    ghm_id = 0 AND
                    p_identites.rss_id = p_rss.oid AND
                    (
                        nom IS DISTINCT FROM NOM_PAT OR
                        nom_naissance IS DISTINCT FROM NNA_PAT OR
                        prenom IS DISTINCT FROM PRE_PAT
                    );
					
				UPDATE [SCHEMA].p_rss
                SET code_postal = CPO_PAT,
					code_postal_id = COALESCE(t_codes_postaux_c.oid,0)
                FROM w_DOSSIER
				LEFT JOIN pmsi.t_codes_postaux_c ON CPO_PAT = t_codes_postaux_c.code
                WHERE ANNEE_CODE = no_sejour_administratif AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    ghm_id = 0 AND
                    (
                    code_postal IS DISTINCT FROM CPO_PAT
                 );
					
                UPDATE [SCHEMA].p_rss
                SET medecin_rss_id = COALESCE(t_medecins.oid,0),
                    no_patient = CPH_DOS
                FROM w_DOSSIER
                JOIN pmsi.t_medecins ON CCH_DOS = t_medecins.code
                WHERE ANNEE_CODE = no_sejour_administratif AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    ghm_id = 0 AND
                    (
                    medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
                    no_patient IS DISTINCT FROM CPH_DOS
                    );

                -- Hospitalisés (si pas trouvés par RSS)
                UPDATE [SCHEMA].p_identites
                SET nom = NOM_PAT,
                    nom_naissance = NNA_PAT,
                    prenom = PRE_PAT
                FROM [SCHEMA].p_rss
                JOIN w_DOSSIER ON ANNEE_CODE = no_sejour_administratif
                WHERE NOM_PAT <> '' AND
                    p_identites.nom LIKE 'RSS%' AND
                    p_identites.rss_id = p_rss.oid AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    (
                        nom IS DISTINCT FROM NOM_PAT OR
                        nom_naissance IS DISTINCT FROM NNA_PAT OR
                        prenom IS DISTINCT FROM PRE_PAT
                    );

                UPDATE [SCHEMA].p_rss
                SET medecin_rss_id = COALESCE(t_medecins.oid,0),
                    no_patient = CPH_DOS
                FROM w_DOSSIER
                JOIN pmsi.t_medecins ON CCH_DOS = t_medecins.code
                WHERE ANNEE_CODE = no_sejour_administratif AND
                    (SORTIE = date_sortie OR ENTREE = date_entree) AND
                    ghm_id <> 0 AND
                    medecin_rss_id = 0 AND
                    (
                    medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
                    no_patient IS DISTINCT FROM CPH_DOS
                    );
                
                -- Pour les séances qui peuvent avoir plusieurs RSS par dossier, le matching des dates E/S ne marche pas
                UPDATE [SCHEMA].p_identites
                SET nom = NOM_PAT,
                    nom_naissance = NNA_PAT,
                    prenom = PRE_PAT
                FROM [SCHEMA].p_rss
                    JOIN pmsi.t_ghm ON t_ghm.oid = p_rss.ghm_id
                    JOIN w_DOSSIER ON ANNEE_CODE = no_sejour_administratif
                WHERE NOM_PAT <> '' AND
                    p_identites.nom LIKE 'RSS%' AND
                    p_identites.rss_id = p_rss.oid AND
                    t_ghm.code LIKE '28%' AND
                    (
                        nom IS DISTINCT FROM NOM_PAT OR
                        nom_naissance IS DISTINCT FROM NNA_PAT OR
                        prenom IS DISTINCT FROM PRE_PAT
                    );
                
                UPDATE [SCHEMA].p_rss
                SET medecin_rss_id = COALESCE(t_medecins.oid,0),
                    no_patient = CPH_DOS
                FROM w_DOSSIER
                    JOIN pmsi.t_medecins ON CCH_DOS = t_medecins.code,
                    pmsi.t_ghm
                WHERE ANNEE_CODE = no_sejour_administratif AND
                    t_ghm.oid = p_rss.ghm_id AND 
                    t_ghm.code LIKE '28%' AND
                    medecin_rss_id = 0 AND
                    (
                    medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
                    no_patient IS DISTINCT FROM CPH_DOS
                    );
            ]]></sqlcmd>
        </NODE>


        <NODE label="Médecins actes">
            <sqlcmd><![CDATA[

            
                REINDEX TABLE prod_sigems.CCAM_RUM;
                
                ANALYZE prod_sigems.CCAM_RUM
                ;
                
                UPDATE [SCHEMA].p_rss_actes
                SET medecin_id = t_medecins.oid
                FROM prod_sigems.CCAM_RUM
                    JOIN w_rss ON CCAM_RUM.JOINTURE = w_rss.ANNEE_CODE AND CASE WHEN CCAM_RUM.NUM_RUM <= 50 THEN 0 ELSE CCAM_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                    JOIN [SCHEMA].p_rss ON w_rss.NUMERO_RSS = no_rss AND SORTIE = date_sortie
                    LEFT JOIN prod_sigems.CCAM_RUM_DESC ON CCAM_RUM_DESC.ID_CCAM = CCAM_RUM.ID_CCAM
                    JOIN pmsi.t_actes ON COALESCE(CCAM_RUM_DESC.ACTE_CCAM || '-' || CCAM_RUM_DESC.EXT, substring(CCAM_RUM.CODE, 1, 7)) = t_actes.code
                    JOIN pmsi.t_medecins ON CCAM_RUM.PRAT = t_medecins.code
                WHERE p_rss_actes.rss_id = p_rss.oid AND p_rss_actes.date_acte = date(CCAM_RUM.DAT) AND
                    (p_rss_actes.acte_id = t_actes.oid OR p_rss_actes.acte_ccam_descriptif_id = t_actes.oid) AND p_rss_actes.activite_ccam = CCAM_RUM.ACTIVITE AND
                    p_rss_actes.medecin_id IS DISTINCT FROM t_medecins.oid;
					
				UPDATE [SCHEMA].p_rss_actes
                SET medecin_id = t_medecins.oid
                FROM prod_sigems.CCAM_RUM
                    JOIN [SCHEMA].p_rss ON JOINTURE = no_sejour_administratif
                    LEFT JOIN prod_sigems.CCAM_RUM_DESC ON CCAM_RUM_DESC.ID_CCAM = CCAM_RUM.ID_CCAM
                    JOIN pmsi.t_actes ON COALESCE(CCAM_RUM_DESC.ACTE_CCAM || '-' || CCAM_RUM_DESC.EXT, substring(CCAM_RUM.CODE, 1, 7)) = t_actes.code
                    JOIN pmsi.t_medecins ON CCAM_RUM.PRAT = t_medecins.code
                WHERE ghm_id = 0 AND  
					p_rss_actes.rss_id = p_rss.oid AND p_rss_actes.date_acte = date(CCAM_RUM.DAT) AND
                    (p_rss_actes.acte_id = t_actes.oid OR p_rss_actes.acte_ccam_descriptif_id = t_actes.oid) AND p_rss_actes.activite_ccam = CCAM_RUM.ACTIVITE AND
                    p_rss_actes.medecin_id IS DISTINCT FROM t_medecins.oid;

                UPDATE [SCHEMA].p_rss_actes
                SET medecin_id = t_medecins.oid
                FROM prod_sigems.CCAM_RUM
                    JOIN w_rss ON CCAM_RUM.JOINTURE = w_rss.ANNEE_CODE AND CASE WHEN CCAM_RUM.NUM_RUM <= 50 THEN 0 ELSE CCAM_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                    JOIN [SCHEMA].p_rss ON w_rss.NUMERO_RSS = no_rss AND SORTIE = date_sortie
                    LEFT JOIN prod_sigems.CCAM_RUM_DESC ON CCAM_RUM_DESC.ID_CCAM = CCAM_RUM.ID_CCAM
                    JOIN pmsi.t_actes ON COALESCE(CCAM_RUM_DESC.ACTE_CCAM || '-' || CCAM_RUM_DESC.EXT, substring(CCAM_RUM.CODE, 1, 7)) = t_actes.code
                    JOIN pmsi.t_medecins ON CCAM_RUM.PRAT = t_medecins.code
                WHERE p_rss_actes.rss_id = p_rss.oid AND p_rss_actes.date_acte = date(CCAM_RUM.DAT) AND
                    (p_rss_actes.acte_id = t_actes.oid OR p_rss_actes.acte_ccam_descriptif_id = t_actes.oid) AND p_rss_actes.activite_ccam = CCAM_RUM.ACTIVITE AND
                    p_rss_actes.medecin_id IS DISTINCT FROM t_medecins.oid
                    AND CCAM_RUM.MODIFICATEUR = modificateur_ccam_1
                    AND CCAM_RUM.MODIF2 = modificateur_ccam_2
                    AND CCAM_RUM.MODIF3 = modificateur_ccam_3
                    AND CCAM_RUM.MODIF4 = modificateur_ccam_4
                    AND CCAM_RUM.assoc = association_nonprevue_ccam 
                ;
                    
                UPDATE [SCHEMA].p_rss_actes
                SET medecin_id = t_medecins.oid
                FROM prod_sigems.CCAM_RUM
                    JOIN [SCHEMA].p_rss ON JOINTURE = no_sejour_administratif
                    LEFT JOIN prod_sigems.CCAM_RUM_DESC ON CCAM_RUM_DESC.ID_CCAM = CCAM_RUM.ID_CCAM
                    JOIN pmsi.t_actes ON COALESCE(CCAM_RUM_DESC.ACTE_CCAM || '-' || CCAM_RUM_DESC.EXT, substring(CCAM_RUM.CODE, 1, 7)) = t_actes.code
                    JOIN pmsi.t_medecins ON CCAM_RUM.PRAT = t_medecins.code
                WHERE ghm_id = 0 AND  
                    p_rss_actes.rss_id = p_rss.oid AND p_rss_actes.date_acte = date(CCAM_RUM.DAT) AND
                    (p_rss_actes.acte_id = t_actes.oid OR p_rss_actes.acte_ccam_descriptif_id = t_actes.oid) AND p_rss_actes.activite_ccam = CCAM_RUM.ACTIVITE AND
                    p_rss_actes.medecin_id IS DISTINCT FROM t_medecins.oid
                    AND CCAM_RUM.MODIFICATEUR = modificateur_ccam_1
                    AND CCAM_RUM.MODIF2 = modificateur_ccam_2
                    AND CCAM_RUM.MODIF3 = modificateur_ccam_3
                    AND CCAM_RUM.MODIF4 = modificateur_ccam_4
                    AND CCAM_RUM.assoc = association_nonprevue_ccam 
                ;

                UPDATE [SCHEMA].p_rss_actes
                SET medecin_id = p_rss.medecin_rss_id
                FROM [SCHEMA].p_rss
                WHERE p_rss_actes.rss_id = p_rss.oid AND ghm_id = 0 AND medecin_id = 0 AND medecin_rss_id <> 0;


            ]]></sqlcmd>
        </NODE>

        <NODE label="Particularité UM">
            <sqlcmd><![CDATA[

                -- Forcage type externe
                UPDATE [SCHEMA].p_rss
                SET ghm_id = 0, ghm_production_id = 0, ghs_id = 0
                FROM pmsi.t_unites_medicales
                WHERE unite_medicale_principale_id = t_unites_medicales.oid AND
                    en_cours = '1' AND (ghm_id <> 0 OR ghs_id <> 0) AND t_unites_medicales.type_sejour = '3';


        ]]></sqlcmd>
        </NODE>

        <NODE label="Externes">
            <sqlcmd><![CDATA[

                DROP TABLE IF EXISTS w_rss_ser;
                CREATE TEMP TABLE w_rss_ser AS
                SELECT
                    p_rss.oid, SER_DOS
                FROM [SCHEMA].p_rss
                JOIN pmsi.t_unites_medicales ON unite_medicale_principale_id = t_unites_medicales.oid
                JOIN prod_sigems.dossier ON no_sejour_administratif = ann_dos || to_char(cod_dos,'FM900000')
                WHERE unite_medicale_principale_id = 0 OR
                    (t_unites_medicales.type_sejour = '3' AND SER_DOS <> t_unites_medicales.code );


                INSERT INTO pmsi.t_unites_medicales (code, texte)
                SELECT SER_DOS, NOM_SER
                FROM w_rss_ser
                JOIN prod_sigems.SERVICE ON SER_DOS = COD_SER
                WHERE SER_DOS NOT IN (SELECT code FROM pmsi.t_unites_medicales)
                GROUP BY 1,2;

                UPDATE [SCHEMA].p_rss_rum
                SET unite_medicale_id = t_unites_medicales.oid
                FROM w_rss_ser
                JOIN pmsi.t_unites_medicales ON SER_DOS = t_unites_medicales.code
                WHERE p_rss_rum.rss_id = w_rss_ser.oid AND
                    unite_medicale_id = 0;

                UPDATE [SCHEMA].p_rss
                SET unite_medicale_principale_id = t_unites_medicales.oid
                FROM w_rss_ser
                JOIN pmsi.t_unites_medicales ON SER_DOS = t_unites_medicales.code
                WHERE p_rss.oid = w_rss_ser.oid AND
                    unite_medicale_principale_id = 0;

        ]]></sqlcmd>
        </NODE>
        <NODE name="TRANSLATE" label="CONVERSION DOSSIERS AXIGATE/SIGEMS">
			<condition><![CDATA[
				SELECT count(*) = 1 FROM pg_tables where tablename LIKE 'hl7%' AND schemaname = 'prod_sigems' 
			]]></condition>
            <NODE label="Modification numero sejour">
                <sqlcmd><![CDATA[

                    UPDATE [SCHEMA].p_rss SET
	    				no_rss = base.cti_to_number(no_sejour_administratif),
	    				no_sejour_administratif = ANN_SIGEMS || to_char(COD_SIGEMS::integer,'FM00000')
	    			FROM prod_sigems.hl7_corresp_dos
	    			WHERE no_sejour_administratif = DOS_PRESTATAIRE AND
	    				TYPE_CORRESP = 'DOSSIER' AND
	    				en_cours = '0'
	    				;
                ]]></sqlcmd>
            </NODE>

	    	<NODE label="Report sur autres tables">
                <sqlcmd><![CDATA[

                    UPDATE [SCHEMA].p_rss_rum p_rss_x
	    			SET no_rss = p_rss.no_rss
	    			FROM [SCHEMA].p_rss
	    			WHERE p_rss.oid = rss_id AND
	    				p_rss_x.no_rss <> p_rss.no_rss
	    				;
    
	    			UPDATE [SCHEMA].p_identites p_rss_x
	    			SET no_rss = p_rss.no_rss
	    			FROM [SCHEMA].p_rss
	    			WHERE p_rss.oid = rss_id AND
	    				p_rss_x.no_rss <> p_rss.no_rss
	    				;	
    
                ]]></sqlcmd>
            </NODE>

        </NODE>
    </NODE>

    <NODE name="PMSIENCOURS" label="RECUPERATION DES DOSSIERS PMSI EN-COURS SIGEMS">

        <NODE label="Corrections lignes SIGEMS">
            <sqlcmd><![CDATA[

                -- Nombre de GHS <> 1 ou -1
                UPDATE prod_sigems.lig_clini
                SET QTE_LCL = QTE_LCL / abs(QTE_LCL)
                WHERE pro_lcl = 'GHS' AND
                    round(QTE_LCL * COG_LCL * COO_LCL * PUN_LCL / 100) <> round(TAR_LCL) AND
                    round(QTE_LCL / abs(QTE_LCL) * COG_LCL * COO_LCL * PUN_LCL / 100) = round(TAR_LCL) AND
                    QTE_LCL <> 0
            ]]></sqlcmd>
        </NODE>

        <NODE label="Paramètres">
            <sqlcmd><![CDATA[

                INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
                SELECT
                    COD_PRA,
                    NOM_PRA,
                    PRE_PRA,
                    COALESCE(t_specialites_medecin.oid,0),
                    NID_PRA,
                    COALESCE(t_specialites_internes_medecin.oid,0)
                FROM prod_sigems.PRATICIEN
                    LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = CSP_PRA
                    LEFT JOIN pmsi.t_specialites_internes_medecin ON (t_specialites_internes_medecin.code = CSP_PRA || '..')
                    LEFT JOIN pmsi.t_medecins ON COD_PRA = t_medecins.code
                WHERE t_medecins.code IS NULL;


                UPDATE pmsi.t_unites_medicales
                SET texte = lib
                FROM prod_sigems.unite_medicale
                WHERE t_unites_medicales.code = unite_medicale.code AND
                    texte <> lib;

                INSERT INTO pmsi.t_unites_medicales(code, texte)
                SELECT
                    UNITE_MEDICALE.CODE,
                    UPPER(UNITE_MEDICALE.LIB)
                FROM
                    prod_sigems.UNITE_MEDICALE
                    LEFT JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = UNITE_MEDICALE.CODE
                WHERE t_unites_medicales.code IS NULL
                GROUP BY UNITE_MEDICALE.CODE, UPPER(UNITE_MEDICALE.LIB)
				ORDER BY UNITE_MEDICALE.CODE;
				
                
				UPDATE pmsi.t_diagnostics SET
					texte = CIM10.LIBELL
				FROM
					prod_sigems.CIM10
				WHERE 1=1
					AND t_diagnostics.code = t_diagnostics.texte
					AND t_diagnostics.code = CIM10.DIAG
					AND t_diagnostics.texte != CIM10.LIBELL;
					
				DROP TABLE IF EXISTS w_periode;
				CREATE TEMP TABLE w_periode AS
				SELECT 
					GREATEST(MIN(date_sortie),'[ANNEE]0101'::date) AS fildeleau_debut,
					date(now()) AS fildeleau_fin
				FROM pmsi.p_rss
				WHERE en_cours = '0' 
				;
				ANALYSE w_periode
				;

            ]]></sqlcmd>
        </NODE>

        <NODE label="RSS">
            <sqlcmd><![CDATA[

                DROP TABLE IF EXISTS w_finess;
                CREATE TEMP TABLE w_finess AS
                SELECT MAX(code) AS code FROM base.t_finess WHERE secondaire <> 1;

                DROP TABLE IF EXISTS w_dossier;
                CREATE TEMP TABLE w_DOSSIER AS
                SELECT
                    ANN_DOS || to_char(COD_DOS,'FM900000') AS no_sejour,
                    ANN_DOS AS no_sejour_ANNEE,
                    COD_DOS AS no_sejour_CODE,
                    SER_DOS,
                    DEN_DOS,
                    DSO_DOS,
                    MDT_DOS
                FROM w_periode, prod_sigems.dossier
                JOIN prod_sigems.dossier_x ON ANN_DOS = ANN_DOX AND COD_DOS = COD_DOX
                WHERE ANN_DOS NOT LIKE 'PA%' AND
                    (DSO_DOS >= fildeleau_debut OR DEN_DOS >=fildeleau_debut OR DSO_DOS IS NULL) AND
                    (DEN_DOS <= fildeleau_fin AND DSO_DOS IS NULL OR DSO_DOS <= fildeleau_fin);


                CREATE INDEX w_dossier_i1
                  ON w_dossier
                  USING btree
                  (no_sejour);

                CREATE INDEX w_dossier_i2
                  ON w_dossier
                  USING btree
                  (no_sejour_CODE);

                -- Dossiers administratifs non codés en PMSI
                DROP TABLE IF EXISTS w_DOS2_ABAND;
                CREATE TEMP TABLE w_DOS2_ABAND AS
                SELECT
                    ANN_DOS2 || to_char(COD_DOS2,'FM900000') AS no_sejour
                FROM prod_sigems.DOS2
                WHERE NDK_DOS2 = 'ABAND'
                GROUP BY 1;

                DROP TABLE IF EXISTS w_UN_RUM_non_codes;
                CREATE TEMP TABLE w_UN_RUM_non_codes AS
                SELECT
                    no_sejour AS NUM_RSS,
                    1::numeric AS NUM_RUM,
                    SER_DOS AS NUM_UM,
                    DEN_DOS AS ENTREE,
                    '8'::text AS MODE_ENTREE,
                    '':: text AS PROVENANCE,
                    DSO_DOS AS SORTIE,
                    '8'::text AS MODE_SORTIE,
                    ''::text AS DESTINATION,
                    ''::text AS GHM,
                    ''::text AS IGS_REEL
                FROM w_dossier
                    LEFT JOIN pmsi.t_divers t_select_ser ON t_select_ser.code = 'FILDELEAU_SER'
                WHERE MDT_DOS NOT IN ('07') AND
                    (t_select_ser.valeur IS NULL OR t_select_ser.valeur = '' OR replace(SER_DOS, ',', '@') = ANY (string_to_array(replace(t_select_ser.valeur,' ',''),','))) AND
                    no_sejour NOT IN (SELECT num_rss FROM prod_sigems.UN_RUM) AND
                    no_sejour NOT IN (SELECT no_sejour_administratif FROM pmsi.p_rss WHERE en_cours = '0' AND etat = '') AND
                    no_sejour NOT IN (SELECT no_sejour FROM w_DOS2_ABAND)
                ORDER BY 1;

                -- Etat du RUM
                DROP TABLE IF EXISTS w_UN_RUM_ghm;
                CREATE TEMP TABLE w_UN_RUM_ghm AS
                SELECT
                    NUM_RSS::text AS NUM_RSS,
                    substr(NUM_RSS,1,2) AS NUM_RSS_ANNEE,
                    base.cti_to_number(substr(NUM_RSS,3)) AS NUM_RSS_CODE,
                    CASE WHEN NUM_RUM <= 50 THEN 0 ELSE NUM_RUM - 50 END AS ENFANT,
                    '1'::text AS in_un_rum,
                    MAX(CASE WHEN CLE_GHM NOT LIKE '90%' THEN COALESCE(CLE_GHM,'') ELSE '' END) AS CLE_GHM,
                    COUNT(DISTINCT num_um) AS NB_UM,
                    MAX(CASE WHEN t_select_um.valeur IS NULL OR t_select_um.valeur = '' OR replace(NUM_UM, ',', '@') = ANY (string_to_array(replace(t_select_um.valeur,' ',''),',')) THEN '1' ELSE '0' END) AS RSS_OK_um,
                    MAX(CASE WHEN t_select_ser.valeur IS NULL OR t_select_ser.valeur = '' OR replace(SER_DOS, ',', '@') = ANY (string_to_array(replace(t_select_ser.valeur,' ',''),',')) THEN '1' ELSE '0' END) AS RSS_OK_ser
                FROM prod_sigems.UN_RUM
                    JOIN pmsi.t_unites_medicales ON UN_RUM.NUM_UM = t_unites_medicales.code
                    JOIN w_dossier ON NUM_RSS = no_sejour
                    LEFT JOIN prod_sigems.PMSI_GHM ON UN_RUM.GHM = PMSI_GHM.CODE_GHM
                    LEFT JOIN pmsi.t_divers t_select_um ON t_select_um.code = 'FILDELEAU_UM'
                    LEFT JOIN pmsi.t_divers t_select_ser ON t_select_ser.code = 'FILDELEAU_SER'
                WHERE base.cti_to_number(substr(NUM_RSS,3)) <> 0
                GROUP BY 1,2,3,4
                HAVING MAX(t_unites_medicales.type_sejour) IS DISTINCT FROM '9';

                INSERT INTO w_UN_RUM_ghm
                SELECT
                    NUM_RSS AS NUM_RSS,
                    substr(NUM_RSS,1,2) AS NUM_RSS_ANNEE,
                    base.cti_to_number(substr(NUM_RSS,3)) AS NUM_RSS_CODE,
                    CASE WHEN NUM_RUM <= 50 THEN 0 ELSE NUM_RUM - 50 END AS ENFANT,
                    '0'::text AS in_un_rum,
                    MAX(CASE WHEN CLE_GHM NOT LIKE '90%' THEN COALESCE(CLE_GHM,'') ELSE '' END) AS CLE_GHM,
                    COUNT(DISTINCT num_um) AS NB_UM,
                    MAX(CASE WHEN t_select_um.valeur IS NULL OR t_select_um.valeur = '' OR replace(NUM_UM, ',', '@') = ANY (string_to_array(replace(t_select_um.valeur,' ',''),',')) THEN '1' ELSE '0' END) AS RSS_OK_um,
                    MAX(CASE WHEN t_select_ser.valeur IS NULL OR t_select_ser.valeur = '' OR replace(SER_DOS, ',', '@') = ANY (string_to_array(replace(t_select_ser.valeur,' ',''),',')) THEN '1' ELSE '0' END) AS RSS_OK_ser
                FROM w_UN_RUM_non_codes UN_RUM
                    JOIN pmsi.t_unites_medicales ON UN_RUM.NUM_UM = t_unites_medicales.code
                    JOIN w_dossier ON NUM_RSS = no_sejour
                    LEFT JOIN prod_sigems.PMSI_GHM ON UN_RUM.GHM = PMSI_GHM.CODE_GHM
                    LEFT JOIN pmsi.t_divers t_select_um ON t_select_um.code = 'FILDELEAU_UM'
                    LEFT JOIN pmsi.t_divers t_select_ser ON t_select_ser.code = 'FILDELEAU_SER'
                WHERE base.cti_to_number(substr(NUM_RSS,3)) <> 0
                GROUP BY 1,2,3,4
                HAVING MAX(t_unites_medicales.type_sejour) IS DISTINCT FROM '9';

                CREATE INDEX w_UN_RUM_ghm_i1
                  ON w_UN_RUM_ghm
                  USING btree
                  (NUM_RSS);

                 CREATE INDEX w_UN_RUM_ghm_i2
                  ON w_UN_RUM_ghm
                  USING btree
                  (NUM_RSS_CODE);

                -- Eliminer code GHM pour des multi rum incomplets en PMSI
                UPDATE w_UN_RUM_ghm
                SET CLE_GHM = ''
                FROM (
                    SELECT ANN_SEJ, COD_SEJ
                    FROM prod_sigems.sejour
                    JOIN w_UN_RUM_ghm ON ANN_SEJ = NUM_RSS_ANNEE AND COD_SEJ = NUM_RSS_CODE
                    WHERE CLE_GHM IS DISTINCT FROM '' AND CLE_GHM NOT LIKE '90%'
                    GROUP BY 1,2
                    HAVING COUNT(DISTINCT SER_SEJ) > 1 AND MAX(NB_UM) IS DISTINCT FROM COUNT(DISTINCT SER_SEJ)
						AND MAX(das_sej)::date >= (now()::date - interval '1 day')
                    ORDER BY 1,2
                ) subview
                WHERE ANN_SEJ = NUM_RSS_ANNEE AND COD_SEJ = NUM_RSS_CODE;

				DROP TABLE IF EXISTS w_ETAT_PMSI;
                CREATE TEMP TABLE w_ETAT_PMSI AS
                SELECT 
                    NUM_RSS, 
                    ANNEE, CODE, 
                    ETAT_PMSI.ENFANT,
                    MAX(CLE_GHM) AS CLE_GHM, 
                    MAX(NUMERO_RSS) AS NUMERO_RSS, 
                    MAX(SORTIE) AS SORTIE, 
                    MAX(ETAT) AS ETAT,
                    MAX(trim(ETAT_PMSI.ANNEE) || trim(ETAT_PMSI.CODE)) AS ETAT_PMSI_JOINTURE_DOSSIER
              FROM prod_sigems.ETAT_PMSI
                JOIN w_UN_RUM_ghm ON
				trim(ETAT_PMSI.ANNEE) || trim(ETAT_PMSI.CODE) = w_UN_RUM_ghm.NUM_RSS_ANNEE || w_UN_RUM_ghm.NUM_RSS_CODE
                        AND w_UN_RUM_GHM.ENFANT = ETAT_PMSI.ENFANT AND
                        w_UN_RUM_GHM.RSS_OK_um = '1' AND
                        w_UN_RUM_GHM.RSS_OK_ser = '1'
                GROUP BY 1,2,3,4;


				INSERT INTO w_ETAT_PMSI
				SELECT 
							NUM_RSS, 
							ANNEE, CODE, 
							ETAT_PMSI.ENFANT,
							MAX(CLE_GHM) AS CLE_GHM, 
							MAX(NUMERO_RSS) AS NUMERO_RSS, 
							MAX(SORTIE) AS SORTIE, 
							MAX(ETAT) AS ETAT,
							MAX(trim(ETAT_PMSI.jointure)) AS ETAT_PMSI_JOINTURE_DOSSIER
				FROM prod_sigems.ETAT_PMSI
				JOIN w_UN_RUM_ghm ON
								trim(ETAT_PMSI.jointure) != '' AND
					trim(ETAT_PMSI.jointure) = w_UN_RUM_ghm.NUM_RSS_ANNEE || lpad(w_UN_RUM_ghm.NUM_RSS_CODE,6,'0')
								AND w_UN_RUM_GHM.ENFANT = ETAT_PMSI.ENFANT AND
								w_UN_RUM_GHM.RSS_OK_um = '1' AND
								w_UN_RUM_GHM.RSS_OK_ser = '1'
								 --group by 1,2,3,4 limit 10
				LEFT JOIN (
					SELECT NUM_RSS as NUM_RSS_ok, 
							ANNEE AS ANNEE_ok, 
							CODE AS CODE_ok, 
							ENFANT AS ENFANT_ok FROM w_ETAT_PMSI) sub
							ON NUM_RSS_ok = NUM_RSS AND
							ANNEE_ok = ANNEE AND
							CODE_ok = CODE AND
							ENFANT_ok = ETAT_PMSI.ENFANT
						WHERE annee_ok IS NULL
						GROUP BY 1,2,3,4;

                CREATE INDEX w_ETAT_PMSI_i1
                  ON w_ETAT_PMSI
                  USING btree
                  (CODE);

                CREATE INDEX w_ETAT_PMSI_i2
                  ON w_ETAT_PMSI
                  USING btree
                  (NUM_RSS);

                CREATE INDEX w_ETAT_PMSI_i3
                  ON w_ETAT_PMSI
                  USING btree
                  (ETAT_PMSI_JOINTURE_DOSSIER);


                INSERT INTO w_ETAT_PMSI
                    SELECT 
                        NUM_RSS, 
                        NUM_RSS_ANNEE, 
                        to_number(NUM_RSS_CODE,'FM00000'),
                        0, MAX(CLE_GHM) AS CLE_GHM, 
                        MAX('-' || NUM_RSS)::numeric AS NUMERO_RSS, 
                        MAX(DSO_DOS::date) AS SORTIE, 
                        0 AS ETAT,
                        MAX( trim(w_UN_RUM_ghm.NUM_RSS_ANNEE) || trim(w_UN_RUM_ghm.NUM_RSS_CODE) ) AS ETAT_PMSI_JOINTURE_DOSSIER
                    FROM w_UN_RUM_ghm
                    JOIN w_dossier ON NUM_RSS = no_sejour
                    WHERE true 
                        AND in_un_rum = '0' AND
                        w_UN_RUM_GHM.RSS_OK_um = '1' AND
                        w_UN_RUM_GHM.RSS_OK_ser = '1' AND
                        NUM_RSS NOT IN(SELECT NUM_RSS FROM w_ETAT_PMSI)
                    GROUP BY 1,2,3,4
                ;

				-- Mise à jour du champ SORTIE si absent et renseigné dans w_DOSSIER (SC-8251)
				WITH temp_w_dossier AS (
					SELECT
						no_sejour
						,min(DSO_DOS) as DSO_DOS
					FROM
						w_DOSSIER
					GROUP BY no_sejour
				)
				UPDATE w_ETAT_PMSI SET
					SORTIE = temp_w_dossier.DSO_DOS
				FROM
					temp_w_dossier
				WHERE 1=1
					AND temp_w_dossier.no_sejour = w_ETAT_PMSI.etat_pmsi_jointure_dossier
					AND temp_w_dossier.DSO_DOS IS DISTINCT FROM NULL
					AND w_ETAT_PMSI.SORTIE IS NULL;
					

                DROP TABLE IF EXISTS w_CONTROLE_DIM;
                CREATE TEMP TABLE w_CONTROLE_DIM AS
                SELECT CONTROLE_DIM.ANNEE, CONTROLE_DIM.CODE, ENFANT, etat
                FROM prod_sigems.CONTROLE_DIM
                JOIN (SELECT annee,code, MAX(nl) as nl FROM prod_sigems.CONTROLE_DIM group by annee, code) as sub ON sub.annee = CONTROLE_DIM.annee and sub.code = CONTROLE_DIM.code
                WHERE CONTROLE_DIM.nl = sub.nl;
                -- GROUP BY 1,2,3 remplacé par un JOIN pour prendre le dernier état enregistré;

                CREATE INDEX w_CONTROLE_DIM_i1
                  ON w_CONTROLE_DIM
                  USING btree
                  (CODE);

                DROP TABLE IF EXISTS w_rss;
                CREATE TEMP TABLE w_rss AS
                SELECT
                    (SELECT code FROM w_finess) AS finess,
                    w_ETAT_PMSI.ANNEE AS ANNEE,
                    w_ETAT_PMSI.CODE AS CODE,
                    w_ETAT_PMSI.NUM_RSS AS ANNEE_CODE,
                    NUMERO_RSS,
                    w_ETAT_PMSI.ENFANT,
                    0::bigint AS rss_id,
					CPH_DOS,
                    MAX(COALESCE(NAISSANCES.SEXE,SEX_PAT)) AS SEX_PAT,
                    MAX(date(COALESCE(NAISSANCES.DAT,DNA_PAT))) AS DNA_PAT,
                    MAX(COALESCE(NAISSANCES.NOM,CASE WHEN NMA_PAT <> '' THEN NMA_PAT ELSE NOM_PAT END)) AS NOM_PAT,
                    MAX(COALESCE(NAISSANCES.NOM,NOM_PAT)) AS NNA_PAT,
                    MAX(COALESCE(NAISSANCES.PRENOM,PRE_PAT)) AS PRE_PAT,
                    MAX(substr(CPO_PAT,1,5)) AS CPO_PAT,
                    MAX(DOSSIER.DEN_DOS) AS DEN_DOS,
                    MAX(DOSSIER.DSO_DOS) AS DSO_DOS,
                    MAX(DOSSIER.CCH_DOS) AS CCH_DOS,
                    MAX(CASE WHEN DOSSIER.MSO_DOS <> '' THEN DOSSIER.MSO_DOS ELSE '80' END) AS MSO_DOS,
                    MAX(w_ETAT_PMSI.CLE_GHM) AS CLE_GHM ,
                    COALESCE(MAX(GHS_VALORISE.GHS),'') AS GHS,
                    MAX(COALESCE(w_CONTROLE_DIM.ETAT,0)) AS CONTROLE_DIM_ETAT,
                    MAX(w_ETAT_PMSI.ETAT) AS ETAT_PMSI_ETAT,
					MAX(ERR) AS ERR,
                    COALESCE(MAX(DOSSIER_SUPPL.PEC_RAAC),'') AS RAAC,
                    COALESCE(MAX(DOSSIER_SUPPL.PASSAGE_URGE),'') AS PASSAGE_URGE,
                    COALESCE(MAX(URGENCE),'0') AS NP,
                    COALESCE(NULLIF(MAX(CONTEXTE_SURV),''),'0')::smallint AS CONTEXTE_SURV,
                    COALESCE(NULLIF(MAX(ADMINIST_PROD_RH),''),'0')::smallint AS ADMINIST_PROD_RH,
                    COALESCE(NULLIF(MAX(RESCRIT_TARIFAIRE),''),'0')::smallint AS RESCRIT_TARIFAIRE,
                    COALESCE(MAX(CATEG_NB_INTERV), '') AS CATEG_NB_INTERV,
                    p_rss.oid AS p_rss_id
                FROM w_periode, w_ETAT_PMSI
                    LEFT JOIN prod_sigems.GHS_VALORISE ON w_ETAT_PMSI.CODE = GHS_VALORISE.CODE AND w_ETAT_PMSI.ANNEE = GHS_VALORISE.ANNEE AND w_ETAT_PMSI.ENFANT = GHS_VALORISE.ENFANT
                    LEFT JOIN w_CONTROLE_DIM ON w_ETAT_PMSI.CODE = w_CONTROLE_DIM.CODE AND w_ETAT_PMSI.ANNEE = w_CONTROLE_DIM.ANNEE AND w_ETAT_PMSI.ENFANT = w_CONTROLE_DIM.ENFANT
                    JOIN prod_sigems.DOSSIER ON trim(DOSSIER.ANN_DOS) || trim(DOSSIER.COD_DOS) = w_ETAT_PMSI.ETAT_PMSI_JOINTURE_DOSSIER
					LEFT JOIN prod_sigems.PMSI_DOSSIER ON  trim(PMSI_DOSSIER.ANNEE) || trim(PMSI_DOSSIER.CODE) = w_ETAT_PMSI.ETAT_PMSI_JOINTURE_DOSSIER
                    LEFT JOIN prod_sigems.DOSSIER_SUPPL ON trim(DOSSIER.ANN_DOS) || trim(DOSSIER.COD_DOS) = trim(DOSSIER_SUPPL.ANN_DOS) || trim(DOSSIER_SUPPL.COD_DOS)
                    JOIN prod_sigems.PATIENT ON DOSSIER.CPH_DOS = PATIENT.COD_PAT
                    LEFT JOIN prod_sigems.NAISSANCES ON w_ETAT_PMSI.ANNEE = NAISSANCES.ANN AND w_ETAT_PMSI.CODE = NAISSANCES.COD AND w_ETAT_PMSI.ENFANT = NAISSANCES.NUM_RUM - 50
                    LEFT JOIN pmsi.p_rss ON NUMERO_RSS = p_rss.no_rss AND p_rss.etat = '' AND date_part('month', SORTIE) = date_part('month', p_rss.date_sortie) AND date_part('year', SORTIE) = date_part('year', p_rss.date_sortie)
					LEFT JOIN prod_sigems.ERREURS_RSS ON ERREURS_RSS.ANNEE = w_ETAT_PMSI.ANNEE AND ERREURS_RSS.CODE = w_ETAT_PMSI.CODE 
					LEFT JOIN prod_sigems.DOSSIER_Y ON DOSSIER_Y.ANNEE || to_char(DOSSIER_Y.CODE,'FM900000') = p_rss.no_sejour_administratif
                WHERE 1=1
                    AND SORTIE BETWEEN fildeleau_debut and fildeleau_fin
                    AND DSO_DOS BETWEEN fildeleau_debut and fildeleau_fin
                    AND NUMERO_RSS <> 0
                    AND w_ETAT_PMSI.NUM_RSS NOT IN (
                        SELECT ANN_LCL || to_char(COD_LCL,'FM900000')
                        FROM prod_sigems.LIG_CLINI
                        WHERE PRO_LCL IN ('SE1', 'SE2', 'SE3', 'SE4', 'SE5', 'SE6', 'SE7') )
                    AND w_ETAT_PMSI.NUM_RSS NOT IN (SELECT no_sejour_administratif FROM pmsi.p_rss WHERE en_cours = '0' AND etat = '')
                GROUP BY 1,2,3,4,5,6,7,8, p_rss.oid
                ORDER BY 1,2;


                UPDATE w_rss SET
                    rss_id = nextval('pmsi.s_rss'::regclass),
                    CLE_GHM = CASE WHEN CLE_GHM <> '' THEN CLE_GHM ELSE '99Z99Z' END;


                INSERT INTO pmsi.p_rss (

                        finess,
                        oid,
                        no_rss,
                        no_patient,
                        version_groupage,
                        date_naissance,
                        sexe,
                        date_entree,
                        date_sortie,
                        mode_entree,
                        provenance,
                        mode_sortie,
                        destination,
                        code_postal,
                        poids_bebe,
                        igs2,
                        cma,
                        groupe_ghm,
                        duree_sejour,
                        nb_rsa,
                        age,
                        supprime,
                        date_import,
                        nb_rum,
                        secteur,
                        no_sejour_administratif,
                        nb_seances,
                        ghm_fg9,
                        ghm_id,
                        medecin_rss_id,
                        ghs_id,
                        mois_sortie,
                        diagnostic_principal_id,
                        diagnostic_relie_id,
                        ghm_production_id,
                        no_rum_principal,
                        unite_medicale_principale_id,
                        import_id,
                        etat,
                        acte_principal_id,
                        code_postal_id,
                        patient_id,
                        prestation_principale_id,
                        severite_fg11_simulation_code,
                        en_cours,
                        en_cours_etat,
                        base_remboursement,
                        sejour_facture,
                        honoraires_factures,
                        t2a_facture,
                        dmi_facture,
                        phc_facture,
                        ca_ghs_theorique,
                        nb_ghs,
                        ca_ghs,
                        nb_exb,
                        ca_exb,
                        nb_exh,
                        ca_exh,
                        nb_si,
                        ca_si,
                        nb_sur,
                        ca_sur,
                        nb_rea,
                        ca_rea,
                        nb_neonat,
                        ca_neonat,
                        traitement_epmsi,
                        code_retour_groupage,
                        comite_medical_id,
                        rehosp_meme_ghm,
                        from_oid,
                        raac_id,
                        np_id,
                        passage_urgences_code,
                        contexte_pat_id,
                        adm_prod_reshospi_id,
                        rescrit_tarif_id,
                        nb_intervention_id
                        )
                SELECT
                        w_rss.finess AS finess,
                        w_rss.rss_id AS oid,
                        w_rss.NUMERO_RSS AS no_rss,
                        CPH_DOS AS no_patient,
                        '' AS version_groupage,
                        date(CASE WHEN substr(CLE_GHM,1,2) <> '15' THEN DNA_PAT ELSE DEN_DOS END) AS date_naissance,
                        CASE WHEN SEX_PAT = 'F' THEN  '2' ELSE '1' END AS sexe,
                        date(DEN_DOS) AS date_entree,
                        date(DSO_DOS) AS date_sortie,
                        '8'::text AS mode_entree,
                        ''::text AS provenance,
                        SUBSTR(MSO_DOS,1,1) AS mode_sortie,
                        CASE WHEN SUBSTR(MSO_DOS,2,1) <> '0' THEN SUBSTR(MSO_DOS,2,1) ELSE '' END AS destination,
                        CPO_PAT AS code_postal,
                        0::numeric AS poids_bebe,
                        0 AS igs2,
                        '' AS cma,
                        substr(CLE_GHM , 3 , 1) AS groupe_ghm,
                        CASE WHEN date(DSO_DOS) > date(DEN_DOS) THEN date(DSO_DOS) - date(DEN_DOS) ELSE 0 END AS duree_sejour,
                        CASE WHEN substr(CLE_GHM,1,2) = '28' THEN 1 ELSE 1 END AS nb_rsa,
                        LEAST(CASE WHEN date(DEN_DOS) > date(DNA_PAT) THEN trunc((date(DEN_DOS) - date(DNA_PAT)) / 365.25,0) ELSE 0 END, 999::numeric) AS age,
                        '' AS supprime,
                        now() AS date_import,
                        1 AS nb_rum,
                        '' AS secteur,
                        ANNEE_CODE AS no_sejour_administratif,
                        CASE WHEN substr(CLE_GHM,1,2) = '28' THEN 1 ELSE 0 END AS nb_seances,
                        '' AS ghm_fg9,
                        COALESCE(t_ghm.oid, 0) AS ghm_id,
                        COALESCE(t_medecins.oid, 0) AS medecin_rss_id,
                        COALESCE(t_ghs.oid, 0) AS ghs_id,
                        date_part('year', DSO_DOS) * 100 + date_part('month', DSO_DOS) AS mois_sortie,
                        0 AS diagnostic_principal_id,
                        0 AS diagnostic_relie_id,
                        COALESCE(t_ghm.oid, 0) AS ghm_production_id,
                        1 AS no_rum_principal,
                        0 AS unite_medicale_principale_id,
                        -1 AS import_id,
                        '' AS etat,
                        0 AS acte_principal_id,
                        0 AS code_postal_id,
                        0 AS patient_id,
                        0 AS prestation_principale_id,
                        '' AS severite_fg11_simulation_code,
                        '1' AS en_cours,
                        CASE WHEN length(CLE_GHM) = 6 AND CLE_GHM <> '' AND CLE_GHM NOT LIKE '90%' AND CLE_GHM NOT LIKE '99%' AND CONTROLE_DIM_ETAT >= 1 THEN 'V' WHEN length(CLE_GHM) = 6 AND CLE_GHM NOT LIKE '90%' AND CLE_GHM NOT LIKE '99%' AND CLE_GHM <> '' THEN 'G' ELSE 'S' END AS en_cours_etat,
                        0 AS base_remboursement,
                        0 AS sejour_facture,
                        0 AS honoraires_factures,
                        0 AS t2a_facture,
                        0 AS dmi_facture,
                        0 AS phc_facture,
                        0 AS ca_ghs_theorique,
                        0 AS nb_ghs,
                        0 AS ca_ghs,
                        0 AS nb_exb,
                        0 AS ca_exb,
                        0 AS nb_exh,
                        0 AS ca_exh,
                        0 AS nb_si,
                        0 AS ca_si,
                        0 AS nb_sur,
                        0 AS ca_sur,
                        0 AS nb_rea,
                        0 AS ca_rea,
                        0 AS nb_neonat,
                        0 AS ca_neonat,
                        '' AS traitement_epmsi,
                        ERR AS code_retour_groupage,
                        0 AS comite_medical_id,
                        '0' AS rehosp_meme_ghm,
                        0 AS from_oid,
                        COALESCE(t_raac.oid, 0) AS raac_id,
                        COALESCE(t_np.oid,0) AS np_id,
                        PASSAGE_URGE AS passage_urgences_code,
                        CONTEXTE_SURV AS contexte_pat_id,
                        ADMINIST_PROD_RH AS adm_prod_reshospi_id,
                        RESCRIT_TARIFAIRE AS rescrit_tarif_id,
                        COALESCE(t_nb_interventions.oid, 0) AS nb_intervention_id
                FROM w_rss
                    LEFT JOIN pmsi.t_ghm ON CLE_GHM = t_ghm.code
                    LEFT JOIN pmsi.t_ghs ON GHS = to_char(t_ghs.code,'FM0000')
                    LEFT JOIN pmsi.t_medecins ON CCH_DOS = t_medecins.code
                    LEFT JOIN pmsi.t_raac on w_rss.raac = t_raac.code
                    LEFT JOIN pmsi.t_np on w_rss.np = t_np.code
                    LEFT JOIN pmsi.t_nb_interventions ON w_rss.CATEG_NB_INTERV = t_nb_interventions.code 
                WHERE w_rss.p_rss_id is NULL
                ;



				DELETE FROM pmsi.p_rss_leg
				WHERE rss_id IN (SELECT oid FROM pmsi.p_rss WHERE import_id = -1)
                ;

				-- transferts d'établissements
				INSERT INTO base.t_etablissements (code, texte, texte_court)
				SELECT
					NUM_FINESS,
					NOM,
					NOM
				FROM 
					prod_sigems.ETAB_CONV
				WHERE 1=1 AND
					CODE IN (SELECT DISTINCT ETAB FROM prod_sigems.DOSSIER_Y) AND
					NUM_FINESS NOT IN (SELECT code FROM base.t_etablissements)
				;
				
				INSERT INTO pmsi.p_rss_leg (finess, no_rsa, rss_id, code_retour_id, nombre)
				SELECT
					finess
					, 0 AS no_rsa
					, p_rss.oid AS rss_id
					, t_codes_retour_groupeur.oid
					, 1
				FROM prod_sigems.ERREURS_RSS
					JOIN pmsi.t_codes_retour_groupeur ON t_codes_retour_groupeur.type = TYP AND t_codes_retour_groupeur.code =  CASE WHEN TYP = 'P' THEN ERREURS_RSS.ERR ELSE to_char(CASE WHEN ERREURS_RSS.ERR IS NULL OR ERREURS_RSS.ERR = '' THEN '0' ELSE ERREURS_RSS.ERR::int END,'FM000') END
					JOIN pmsi.p_rss ON ANNEE || to_char(ERREURS_RSS.CODE::int,'FM00000') = no_sejour_administratif
				WHERE import_id = -1;

				INSERT INTO base.t_etablissements (code, texte, texte_court)
				SELECT
					NUM_FINESS,
					NOM,
					NOM
				FROM 
					prod_sigems.ETAB_CONV
				WHERE 1=1 AND
					CODE IN (SELECT DISTINCT CH2 FROM prod_sigems.DOSSIER_Y) AND
					NUM_FINESS NOT IN (SELECT code FROM base.t_etablissements)
				;
				
            ]]></sqlcmd>
        </NODE>

       <NODE label="Identités">
            <sqlcmd><![CDATA[

                    INSERT INTO pmsi.p_identites(
                            finess,
                            rss_id,
                            no_rss,
                            nom,
                            prenom,
                            nom_naissance)
                    SELECT
                            w_rss.finess AS finess,
                            w_rss.rss_id AS oid,
                            w_rss.NUMERO_RSS AS no_rss,
                            NOM_PAT AS nom,
                            PRE_PAT AS prenom,
                            NNA_PAT AS nom_naissance
                    FROM w_rss;

                    UPDATE pmsi.p_rss
                    SET no_patient = CPH_DOS
                    FROM w_RSS
                    WHERE NUMERO_RSS = no_rss AND
                        (DSO_DOS = date_sortie OR DEN_DOS = date_entree) AND
                        (
                        no_patient IS DISTINCT FROM CPH_DOS
                        );
    
                    UPDATE pmsi.p_rss
                    SET no_patient = CPH_DOS
                    FROM w_RSS
                    WHERE ANNEE_CODE = no_sejour_administratif AND
                        (DSO_DOS = date_sortie OR DEN_DOS = date_entree) AND
                        ghm_id = 0 AND
                        (
                        no_patient IS DISTINCT FROM CPH_DOS
                        );
    
                    UPDATE pmsi.p_rss
                    SET no_patient = CPH_DOS
                    FROM w_RSS
                    WHERE ANNEE_CODE = no_sejour_administratif AND
                        (DSO_DOS = date_sortie OR DEN_DOS = date_entree) AND
                        ghm_id <> 0 AND
                        (
                        no_patient IS DISTINCT FROM CPH_DOS
                        );
            ]]></sqlcmd>
        </NODE>

        <NODE label="Rum">
            <sqlcmd><![CDATA[


                    DROP TABLE IF EXISTS w_DIAG_RUM;
                    CREATE TEMP TABLE w_DIAG_RUM AS
                    SELECT DIAG_RUM.NUM_RSS,
                        DIAG_RUM.NUM_RUM,
                        MAX(CASE WHEN QTE = 0 THEN DIAG ELSE '' END) AS DIAG_P,
                        MAX(CASE WHEN QTE = 2 THEN DIAG ELSE '' END) AS DIAG_R
                    FROM prod_sigems.DIAG_RUM
                        JOIN w_rss ON NUM_RSS = w_rss.ANNEE_CODE
                    GROUP BY 1,2
                    ORDER BY 1,2;

                    CREATE INDEX w_DIAG_RUM_i1
                                      ON w_DIAG_RUM
                                      USING btree
                                      (NUM_RSS);

                    INSERT INTO pmsi.t_unites_medicales (code, texte)
                    SELECT
                            NUM_UM,
                            NUM_UM
                    FROM prod_sigems.UN_RUM
                        JOIN w_rss ON UN_RUM.NUM_RSS = w_rss.ANNEE_CODE AND
                            CASE WHEN UN_RUM.NUM_RUM <= 50 THEN 0 ELSE UN_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                    WHERE NUM_UM NOT IN (SELECT code FROM pmsi.t_unites_medicales WHERE code IS NOT NULL)
                    GROUP BY 1,2;

                    INSERT INTO pmsi.p_rss_rum(
                            finess,
                            rss_id,
                            no_rss,
                            no_rum,
                            date_entree,
                            date_sortie,
                            mode_entree,
                            provenance,
                            mode_sortie,
                            destination,
                            duree_sejour,
                            nb_seances,
                            unite_medicale,
                            diagnostic_principal_id,
                            diagnostic_relie_id,
                            unite_medicale_id,
                            type_autorisation,
                            igs2,
                            type_autorisation_lit_dedie,
                            supplement_code)
                    SELECT
                            w_rss.finess  AS finess,
                            w_rss.rss_id AS rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            CASE WHEN UN_RUM.NUM_RUM <= 50 THEN UN_RUM.NUM_RUM ELSE UN_RUM.NUM_RUM - 50 END AS no_rum,
                            GREATEST(date(UN_RUM.ENTREE),date(DEN_DOS)) AS date_entree,
                            LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) AS date_sortie,

                            MODE_ENTREE AS mode_entree,
                            PROVENANCE AS provenance,
                            MODE_SORTIE AS mode_sortie,
                            DESTINATION AS destination,
                            CASE WHEN LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) > GREATEST(date(UN_RUM.ENTREE),date(DEN_DOS)) THEN LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) - GREATEST(date(UN_RUM.ENTREE),date(DEN_DOS)) ELSE 0 END AS duree_sejour,
                            CASE WHEN CLE_GHM LIKE '28%' THEN 1 ELSE 0 END::numeric AS nb_seances,
                            NUM_UM AS unite_medicale,
                            COALESCE(t_diagnostics.oid,0) AS diagnostic_principal_id,
                            COALESCE(t_diagnostics_relies.oid,0) AS diagnostic_relie_id,
                            COALESCE(t_unites_medicales.oid,0) AS unite_medicale_id,
                            '' AS type_autorisation,
                             CASE WHEN trim(IGS_REEL) = '' OR NOT IGS_REEL ~ '^[0-9,\.]*$' THEN 0 ELSE round(base.cti_to_number(IGS_REEL::text)) END,
                            '' AS type_autorisation_lit_dedie,
                            '' AS supplement_code
                    FROM prod_sigems.UN_RUM
                        JOIN w_rss ON UN_RUM.NUM_RSS = w_rss.ANNEE_CODE AND CASE WHEN UN_RUM.NUM_RUM <= 50 THEN 0 ELSE UN_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                        LEFT JOIN w_DIAG_RUM ON w_DIAG_RUM.NUM_RSS = UN_RUM.NUM_RSS AND w_DIAG_RUM.NUM_RUM = UN_RUM.NUM_RUM
                        LEFT JOIN pmsi.t_unites_medicales ON (NUM_UM = t_unites_medicales.code)
                        LEFT JOIN pmsi.t_diagnostics ON (w_DIAG_RUM.DIAG_P = t_diagnostics.code)
                        LEFT JOIN pmsi.t_diagnostics t_diagnostics_relies ON (w_DIAG_RUM.DIAG_R = t_diagnostics_relies.code)
                    GROUP BY 
                            w_rss.finess  ,
                            w_rss.rss_id ,
                            w_rss.NUMERO_RSS  ,
                            MODE_ENTREE ,
                            PROVENANCE ,
                            MODE_SORTIE ,
                            DESTINATION ,
                            NUM_UM,
                            COALESCE(t_diagnostics.oid,0),
                            COALESCE(t_diagnostics_relies.oid,0),
                            COALESCE(t_unites_medicales.oid,0),
                            UN_RUM.NUM_RUM ,
                            UN_RUM.SORTIE,
                            DSO_DOS,
                            CLE_GHM ,
                            IGS_REEL,
                            UN_RUM.ENTREE,
                            UN_RUM.SORTIE,
                            DEN_DOS
                    ;

                    -- RUM fictifs pour non codés PMSI
                    INSERT INTO pmsi.p_rss_rum(
                            finess,
                            rss_id,
                            no_rss,
                            no_rum,
                            date_entree,
                            date_sortie,
                            mode_entree,
                            provenance,
                            mode_sortie,
                            destination,
                            duree_sejour,
                            nb_seances,
                            unite_medicale,
                            diagnostic_principal_id,
                            diagnostic_relie_id,
                            unite_medicale_id,
                            type_autorisation,
                            igs2,
                            type_autorisation_lit_dedie,
                            supplement_code)
                    SELECT
                            w_rss.finess  AS finess,
                            w_rss.rss_id AS rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            CASE WHEN UN_RUM.NUM_RUM <= 50 THEN UN_RUM.NUM_RUM ELSE UN_RUM.NUM_RUM - 50 END AS no_rum,
                            GREATEST(date(UN_RUM.ENTREE), date(DEN_DOS)) AS date_entree,
                            LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) AS date_sortie,
                            MODE_ENTREE AS mode_entree,
                            PROVENANCE AS provenance,
                            MODE_SORTIE AS mode_sortie,
                            DESTINATION AS destination,
                            CASE WHEN LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) > GREATEST(date(UN_RUM.ENTREE),date(DEN_DOS)) THEN LEAST(date(UN_RUM.SORTIE),date(DSO_DOS)) - GREATEST(date(UN_RUM.ENTREE),date(DEN_DOS)) ELSE 0 END AS duree_sejour,
                            CASE WHEN CLE_GHM LIKE '28%' THEN 1 ELSE 0 END::numeric AS nb_seances,
                            NUM_UM AS unite_medicale,
                            0 AS diagnostic_principal_id,
                            0 AS diagnostic_relie_id,
                            COALESCE(t_unites_medicales.oid,0) AS unite_medicale_id,
                            '' AS type_autorisation,
                            CASE WHEN trim(IGS_REEL) = '' THEN 0 ELSE round(base.cti_to_number(IGS_REEL::text)) END,
                            '' AS type_autorisation_lit_dedie,
                            '' AS supplement_code
                    FROM w_UN_RUM_non_codes UN_RUM
                        JOIN w_rss ON UN_RUM.NUM_RSS = w_rss.ANNEE_CODE AND CASE WHEN UN_RUM.NUM_RUM <= 50 THEN 0 ELSE UN_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                        LEFT JOIN pmsi.t_unites_medicales ON (NUM_UM = t_unites_medicales.code);
                        
                    -- Quelques rum peuvent être créés en double
					DELETE FROM pmsi.p_rss_rum
					USING
					(
					SELECT p_rss_rum.rss_id, p_rss_rum.no_rum, min(p_rss_rum.ctid) AS keep_ctid
					FROM pmsi.p_rss_rum
					JOIN pmsi.p_rss ON rss_id = p_rss.oid
					WHERE en_cours = '1'
					GROUP BY 1,2
					HAVING count(*) > 1
					) subview
					WHERE p_rss_rum.rss_id = subview.rss_id AND
						p_rss_rum.no_rum = subview.no_rum AND
						p_rss_rum.Ctid <> keep_ctid
					;


                    DROP TABLE IF EXISTS w_rss_rum;
                    CREATE TEMP TABLE w_rss_rum AS
                    SELECT p_rss_rum.rss_id,
                        count(*) AS nb_rum,
                        SUM(p_rss_rum.nb_seances) AS nb_seances,
                        MIN(no_rum) AS no_premier_rum,
                        MAX(p_rss.passage_urgences_code) AS passage_urge
                    FROM pmsi.p_rss_rum, pmsi.p_rss
                    WHERE p_rss.oid = p_rss_rum.rss_id AND en_cours = '1'
                    GROUP BY 1;

                    -- On ajoute la variable passage urgence seulement sur le premier RUM
                    UPDATE pmsi.p_rss_rum
                    SET passage_urgences_code = w_rss_rum.passage_urge
                    FROM w_rss_rum
                    WHERE w_rss_rum.rss_id =  p_rss_rum.rss_id
                        AND no_rum = 1
                        AND w_rss_rum.passage_urge <> ''
                        AND w_rss_rum.passage_urge IS DISTINCT FROM passage_urgences_code
                    ;

                    UPDATE pmsi.p_rss
                    SET
                        mode_entree = p_rss_rum.mode_entree,
                        provenance = p_rss_rum.provenance,
                        nb_rum = w_rss_rum.nb_rum,
                        no_rum_principal = p_rss_rum.no_rum,
                        unite_medicale_principale_id = p_rss_rum.unite_medicale_id,
                        nb_seances = w_rss_rum.nb_seances
                    FROM w_rss_rum, pmsi.p_rss_rum
                    WHERE p_rss.oid = w_rss_rum.rss_id AND en_cours = '1' AND
                        p_rss_rum.rss_id = w_rss_rum.rss_id AND p_rss_rum.no_rum = w_rss_rum.no_premier_rum;

                /**********************************************************************************
				 *            	 ETABLISSEMENT PROVENANCE ET DESTINATION (TRANSFERT)              *
				 **********************************************************************************/

				-- Insertion des établissement de provenance et destination dans p_rss_transfert
				-- Traitement en commun réalisé dans import_rss_rsf_encours.php (generation_rum_post_traitements())
				-- Grâce à la table w_TRANSFERTS
				
				DROP TABLE IF EXISTS temp.w_TRANSFERTS;
				CREATE TABLE temp.w_TRANSFERTS AS 
				SELECT 
					p_rss.oid AS rss_id,
					import_id,
		            COALESCE(MIN(CASE WHEN mode_entree IN (7, 0) THEN provenance.oid ELSE null END),0) AS prov_id,
					COALESCE(MIN(CASE WHEN mode_sortie IN (7, 0) THEN destination.oid ELSE null END),0) AS dest_id
				FROM
					pmsi.p_rss
					JOIN prod_sigems.DOSSIER_Y ON ANNEE || to_char(DOSSIER_Y.CODE,'FM900000') = no_sejour_administratif
					LEFT JOIN prod_sigems.ETAB_CONV PROV ON CH2 = PROV.CODE
					LEFT JOIN prod_sigems.ETAB_CONV DEST ON ETAB = DEST.CODE
					LEFT JOIN base.t_etablissements provenance ON PROV.NUM_FINESS = provenance.code
					LEFT JOIN base.t_etablissements destination ON DEST.NUM_FINESS = destination.code
				WHERE 1=1 AND (
					ETAB != '' OR
					CH2 != '') AND
					etat != 'S'
                GROUP BY 1,2
                ;


            ]]></sqlcmd>
        </NODE>

        <NODE label="Diagnostics">
            <sqlcmd><![CDATA[


                    INSERT INTO pmsi.p_rss_diagnostics(
                            finess,
                            rss_id,
                            no_rss,
                            no_rum,
                            diagnostic_id,
                            type_diagnostic,
                            type_diagnostic_rss,
                            imprecis,
                            unite_medicale,
                            unite_medicale_id)
                    SELECT
                            w_rss.finess,
                            w_rss.rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            p_rss_rum.no_rum,
                            COALESCE(t_diagnostics.oid,0) AS diagnostic_id,
                            CASE WHEN qte = 0 THEN 'DP'
                                WHEN qte = 2 THEN 'DR'
                                WHEN qte = 3 THEN 'DD'
                                ELSE 'DA' END
                            AS type_diagnostic,
                            CASE WHEN qte = 0 THEN 'DP'
                                WHEN qte = 2 THEN 'DR'
                                WHEN qte = 3 THEN 'DD'
                                ELSE 'DA' END
                            AS type_diagnostic_rss,
                            '' AS imprecis,
                            p_rss_rum.unite_medicale,
                            p_rss_rum.unite_medicale_id
                    FROM prod_sigems.DIAG_RUM
                        JOIN w_rss ON DIAG_RUM.NUM_RSS = w_rss.ANNEE_CODE AND CASE WHEN DIAG_RUM.NUM_RUM <= 50 THEN 0 ELSE DIAG_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                        JOIN pmsi.p_rss_rum ON w_rss.rss_id = p_rss_rum.rss_id AND CASE WHEN DIAG_RUM.NUM_RUM <= 50 THEN DIAG_RUM.NUM_RUM ELSE DIAG_RUM.NUM_RUM - 50 END = p_rss_rum.no_rum
                        JOIN pmsi.t_diagnostics ON (DIAG = t_diagnostics.code);


            ]]></sqlcmd>
        </NODE>

        <NODE label="Actes">
            <sqlcmd><![CDATA[


                    INSERT INTO pmsi.p_rss_actes(
                        finess,
                        rss_id,
                        no_rss,
                        no_rum,
                        unite_medicale,
                        unite_medicale_id,
                        date_acte,
                        medecin_id,
                        acte_id,
						acte_ccam_descriptif_id,
                        nombre,
                        phase_ccam,
                        activite_ccam,
                        extension_ccam,
                        modificateur_ccam_1,
                        modificateur_ccam_2,
                        modificateur_ccam_3,
                        modificateur_ccam_4,
                        remboursement_excp_ccam,
                        association_nonprevue_ccam
                        )
                    SELECT
                            w_rss.finess,
                            w_rss.rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            p_rss_rum.no_rum,
                            p_rss_rum.unite_medicale,
                            p_rss_rum.unite_medicale_id,
                            date(CCAM_RUM.DAT) AS date_acte,
                            COALESCE(t_medecins.oid,0) AS medecin_id,
                            COALESCE(t_actes.oid,0) AS acte_id,
							COALESCE(t_actes_desc.oid,t_actes.oid) AS acte_ccam_descriptif_id,
                            1 AS nombre,
                            CCAM_RUM.PHASE AS phase_ccam,
                            CCAM_RUM.ACTIVITE AS activite_ccam,
                            CCAM_RUM.EXT_DOC AS extension_ccam,
                            SUBSTR(TRIM(CCAM_RUM.MODIFICATEUR)||TRIM(CCAM_RUM.MODIF2)||TRIM(CCAM_RUM.MODIF3)||TRIM(CCAM_RUM.MODIF4)||TRIM(CCAM_RUM.MODIF5),1,1) AS modificateur_ccam_1,
                            SUBSTR(TRIM(CCAM_RUM.MODIFICATEUR)||TRIM(CCAM_RUM.MODIF2)||TRIM(CCAM_RUM.MODIF3)||TRIM(CCAM_RUM.MODIF4)||TRIM(CCAM_RUM.MODIF5),2,1) AS modificateur_ccam_2,
                            SUBSTR(TRIM(CCAM_RUM.MODIFICATEUR)||TRIM(CCAM_RUM.MODIF2)||TRIM(CCAM_RUM.MODIF3)||TRIM(CCAM_RUM.MODIF4)||TRIM(CCAM_RUM.MODIF5),3,1) AS modificateur_ccam_3,
                            SUBSTR(TRIM(CCAM_RUM.MODIFICATEUR)||TRIM(CCAM_RUM.MODIF2)||TRIM(CCAM_RUM.MODIF3)||TRIM(CCAM_RUM.MODIF4)||TRIM(CCAM_RUM.MODIF5),4,1) AS modificateur_ccam_4,
                            CCAM_RUM.REMBOURSEMENT AS remboursement_excp_ccam,
                            CCAM_RUM.ASSOC AS association_nonprevue_ccam
                    FROM prod_sigems.CCAM_RUM
                        JOIN w_rss ON CCAM_RUM.JOINTURE = w_rss.ANNEE_CODE AND CASE WHEN CCAM_RUM.NUM_RUM <= 50 THEN 0 ELSE CCAM_RUM.NUM_RUM - 50 END = w_rss.ENFANT
                        JOIN pmsi.p_rss_rum ON w_rss.rss_id = p_rss_rum.rss_id AND CCAM_RUM.NUM_RUM = p_rss_rum.no_rum AND CASE WHEN CCAM_RUM.NUM_RUM <= 50 THEN CCAM_RUM.NUM_RUM ELSE CCAM_RUM.NUM_RUM - 50 END = p_rss_rum.no_rum
                        LEFT JOIN prod_sigems.CCAM_RUM_DESC ON CCAM_RUM_DESC.ID_CCAM = CCAM_RUM.ID_CCAM
                        JOIN pmsi.t_actes ON substring(CCAM_RUM.CODE, 1, 7) = t_actes.code
						LEFT JOIN pmsi.t_actes t_actes_desc ON CCAM_RUM_DESC.ACTE_CCAM || '-' || CCAM_RUM_DESC.EXT = t_actes_desc.code
                        LEFT JOIN pmsi.t_medecins ON CCAM_RUM.PRAT = t_medecins.code;

            ]]></sqlcmd>
        </NODE>

        <NODE label="Factures">
            <sqlcmd><![CDATA[


                    INSERT INTO pmsi.p_rsf_total(
                        finess, no_rss, code_pec, rang_beneficiaire, regime, nature_pec,
                        justificatif_exo, no_facture, code_regularisation, base_remboursement,
                        sejour_facture, sejour_remboursable, honoraires_factures, honoraires_remboursable,
                        t2a_facture, dmi_facture, phc_facture, participation_assure_avant_oc,
                        sejour_remboursable_2, honoraires_remboursable_2, montant_dmi_ghs2006,
                        nb_si, ca_si, nb_sur, ca_sur, nb_rea, ca_rea, nb_neonat, ca_neonat,
                        nb_exh, ca_exh, nb_exb, ca_exb, nb_ghs, ca_ghs, rss_id, ca_ghs_theorique)
                    SELECT
                        p_rss.finess, p_rss.no_rss, '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0,
                        0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, p_rss.oid, 0
                    FROM pmsi.p_rss LEFT JOIN pmsi.p_rsf_total ON (p_rsf_total.rss_id = p_rss.oid)
                    WHERE en_cours = '1' AND p_rsf_total.rss_id IS NULL;


            ]]></sqlcmd>
        </NODE>

        <NODE label="Prestations">
            <sqlcmd><![CDATA[


                INSERT INTO pmsi.p_rsf_detail(
                        finess,
                        rss_id,
                        no_rss,
                        nature,
                        mt,
                        dmt,
                        date_debut,
                        date_fin,
                        nombre,
                        coefficient,
                        prix_unitaire,
                        base_remboursement,
                        taux_remboursement,
                        sejour_facture,
                        sejour_remboursable,
                        compteur,
                        ligne_t2a,
                        pec_fj,
                        coefficient_mco,
                        sejour_remboursable_2,
                        ghs_id,
                        sejour_rembourse_noemie,
                        nature_noemie,
                        prestation_id,
                        type,
                        coefficient_geo,
                        est_ligne_rss,
                        est_ligne_rum,
                        no_rum,
                        unite_medicale_id,
                        base_remboursement_rum)
                SELECT
                        w_rss.finess  AS finess,
                        w_rss.rss_id AS rss_id,
                        w_rss.NUMERO_RSS AS no_rss,
                        '' AS nature,
                        '' AS mt,
                        '' AS dmt,
                        date(DDE_LCL) AS date_debut,
                        date(COALESCE(DFI_LCL,DDE_LCL)) AS date_fin,
                        QTE_LCL AS nombre,
                        COG_LCL AS coefficient,
                        PUN_LCL AS prix_unitaire,
                        TAR_LCL AS base_remboursement,
                        TX1_LCL AS taux_remboursement,
                        TAR_LCL AS sejour_facture,
                        TAR_LCL AS sejour_remboursable,
                        0 AS compteur,
                        '' AS ligne_t2a,
                        '' AS pec_fj,
                        COO_LCL / 100 AS coefficient_mco,
                        0 AS sejour_remboursable_2,
                        COALESCE(t_ghs.oid, 0) AS ghs_id,
                        0 AS sejour_rembourse_noemie,
                        '' AS nature_noemie,
                        COALESCE(t_prestations.oid, 0) AS prestation_id,
                        'B' AS type,
                        0 AS coefficient_geo,
                        '1' AS est_ligne_rss,
                        '0' AS est_ligne_rum,
                        1 AS no_rum,
                        0 AS unite_medicale_id,
                        0 AS base_remboursement_rum
                FROM prod_sigems.LIG_CLINI
                    JOIN prod_sigems.DOS2 ON ANN_LCL = ANN_DOS2 AND COD_LCL = COD_DOS2 AND IND_LCL = IND_DOS2 AND ETA_DOS2 >= 3 AND DFA_DOS2 IS NOT NULL
                    JOIN w_rss ON ANN_LCL = w_rss.ANNEE AND COD_LCL = w_rss.CODE
                    JOIN prod_sigems.PROD_CLINI ON PRO_LCL = COD_PRC
                    JOIN pmsi.t_prestations ON ACT_PRC = t_prestations.code
                    LEFT JOIN pmsi.t_ghs ON w_rss.GHS = to_char(t_ghs.code,'FM0000')
                WHERE PRO_LCL <> 'PJ' AND
                    ACT_PRC <> 'PJ' AND
                    TX1_LCL <> 0 AND
                    (
                        w_rss.ENFANT = 0 AND PRO_LCL NOT IN ('GHS1', 'GHS2', 'GHS3', 'GHS4', 'GHS5')
                            OR
                        w_rss.ENFANT = 1 AND PRO_LCL = 'GHS1'
                            OR
                        w_rss.ENFANT = 2 AND PRO_LCL = 'GHS2'
                            OR
                        w_rss.ENFANT = 3 AND PRO_LCL = 'GHS3'
                            OR
                        w_rss.ENFANT = 4 AND PRO_LCL = 'GHS4'
                            OR
                        w_rss.ENFANT = 5 AND PRO_LCL = 'GHS5'
                    )

                    ;
				
				-- mettre à jour le nombre de seances dans p_rss
			
				DROP TABLE IF EXISTS w_rss_nb_seances;
				CREATE TEMP TABLE w_rss_nb_seances AS
				SELECT  
					no_rss,
					count(nombre) as nb_seances
				FROM pmsi.p_rsf_detail 
							JOIN pmsi.t_ghs ON t_ghs.oid = p_rsf_detail.ghs_id
							JOIN pmsi.t_prestations ON t_prestations.oid = p_rsf_detail.prestation_id
				WHERE 1=1
					AND (t_ghs.code = 9605 OR t_prestations.code = 'D11')
				GROUP BY 1
				;
							
				UPDATE pmsi.p_rss
					SET
						nb_seances = w_rss_nb_seances.nb_seances,
                        nb_rsa = CASE WHEN t_ghm.code LIKE '28%' THEN w_rss_nb_seances.nb_seances ELSE nb_rsa END
					FROM w_rss_nb_seances, pmsi.t_ghm
					WHERE 1=1
                        AND p_rss.ghm_id = t_ghm.oid
						AND p_rss.no_rss = w_rss_nb_seances.no_rss 
						AND en_cours = '1'
                        AND (p_rss.nb_seances != w_rss_nb_seances.nb_seances
                            OR
                            p_rss.nb_rsa != CASE WHEN t_ghm.code LIKE '28%' THEN w_rss_nb_seances.nb_seances ELSE nb_rsa END
                            )
				;

            ]]></sqlcmd>
        </NODE>

        <NODE label="LPP">
            <sqlcmd><![CDATA[


                    INSERT INTO pmsi.p_rss_lpp(
                            finess,
                            rss_id,
                            no_rss,
                            rang,
                            date_lpp,
                            lpp_id,
                            nombre,
                            prix_unitaire,
                            montant_facture,
                            non_pris_en_compte)
                    SELECT
                            w_rss.finess  AS finess,
                            w_rss.rss_id AS rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            '' AS rang,
                            date(DDE_LCL) AS date_lpp,
                            COALESCE(t_lpp.oid, 0) AS lpp_id,
                            QTE_LPP AS nombre,
                            PU_LPP AS prix_unitaire,
                            MNT_LPP AS montant_facture,
                            '0' AS non_pris_en_compte
                    FROM prod_sigems.LCL_BIS
                        JOIN prod_sigems.LIG_CLINI ON NUM_LCL = NUM
                        JOIN w_rss ON ANN_LCL = w_rss.ANNEE AND COD_LCL = w_rss.CODE AND w_rss.ENFANT = 0
                        JOIN pmsi.t_lpp ON (COD_LPP = t_lpp.code);

            ]]></sqlcmd>
        </NODE>

        <NODE label="UCD">
            <sqlcmd><![CDATA[

                    INSERT INTO pmsi.p_rss_ucd(
                            finess,
                            rss_id,
                            no_rss,
                            rang,
                            date_ucd,
                            ucd_id,
                            nombre,
                            coefficient_fractionnement,
                            prix_unitaire_achat,
                            montant_facture,
                            non_pris_en_compte)
                    SELECT
                            w_rss.finess  AS finess,
                            w_rss.rss_id AS rss_id,
                            w_rss.NUMERO_RSS AS no_rss,
                            '' AS rang,
                            date(DDE_LCL) AS date_ucd,
                            COALESCE(t_ucd.oid, 0) AS ucd_id,
                            QTE AS nombre,
                            1::numeric AS coefficient_fractionnement,
                            ACHAT_COUT AS prix_unitaire,
                            QTE * ACHAT_COUT AS montant_facture,
                            '0' AS non_pris_en_compte
                    FROM prod_sigems.UCD_LCL_HON
                        JOIN prod_sigems.LIG_CLINI ON NUM_LIGNE = NUM_LCL
                        JOIN w_rss ON ANN_LCL = w_rss.ANNEE AND COD_LCL = w_rss.CODE AND w_rss.ENFANT = 0
                        JOIN pmsi.t_ucd ON (CODE_UCD = t_ucd.code)
					WHERE UCD_LCL_HON.TYP = 2;


            ]]></sqlcmd>
        </NODE>

        <NODE label="Particularité UM">
            <sqlcmd><![CDATA[

                -- Forcage type externe
                UPDATE pmsi.p_rss
                SET ghm_id = 0, ghm_production_id = 0, ghs_id = 0
                FROM pmsi.t_unites_medicales
                WHERE unite_medicale_principale_id = t_unites_medicales.oid AND
                    en_cours = '1' AND (ghm_id <> 0 OR ghs_id <> 0) AND t_unites_medicales.type_sejour = '3';


        ]]></sqlcmd>
        </NODE>


        <NODE label="Externes">
            <sqlcmd><![CDATA[

                DROP TABLE IF EXISTS w_rss_ser;
                CREATE TEMP TABLE w_rss_ser AS
                SELECT
                    p_rss.oid, SER_DOS
                FROM pmsi.p_rss
                JOIN pmsi.t_unites_medicales ON unite_medicale_principale_id = t_unites_medicales.oid
                JOIN prod_sigems.dossier ON no_sejour_administratif = ann_dos || to_char(cod_dos,'FM900000')
                WHERE unite_medicale_principale_id = 0 OR
                    (t_unites_medicales.type_sejour = '3' AND SER_DOS <> t_unites_medicales.code );


                INSERT INTO pmsi.t_unites_medicales (code, texte)
                SELECT SER_DOS, NOM_SER
                FROM w_rss_ser
                JOIN prod_sigems.SERVICE ON SER_DOS = COD_SER
                WHERE SER_DOS NOT IN (SELECT code FROM pmsi.t_unites_medicales)
                GROUP BY 1,2;

                UPDATE pmsi.t_unites_medicales
                SET texte = NOM_SER
                FROM prod_sigems.SERVICE
                LEFT JOIN prod_sigems.UNITE_MEDICALE ON COD_SER = UNITE_MEDICALE.CODE
                WHERE t_unites_medicales.code = COD_SER AND
                    UNITE_MEDICALE.CODE IS NULL AND
                    texte <> NOM_SER;

                UPDATE pmsi.p_rss_rum
                SET unite_medicale_id = t_unites_medicales.oid
                FROM w_rss_ser
                JOIN pmsi.t_unites_medicales ON SER_DOS = t_unites_medicales.code
                WHERE p_rss_rum.rss_id = w_rss_ser.oid AND
                    unite_medicale_id = 0;

                UPDATE pmsi.p_rss
                SET unite_medicale_principale_id = t_unites_medicales.oid
                FROM w_rss_ser
                JOIN pmsi.t_unites_medicales ON SER_DOS = t_unites_medicales.code
                WHERE p_rss.oid = w_rss_ser.oid AND
                    unite_medicale_principale_id = 0;

            ]]></sqlcmd>
        </NODE>

        <NODE label="Etat des dossiers">
            <sqlcmd><![CDATA[

                SELECT pmsi.cti_initialize_rss_etat();

        ]]></sqlcmd>
        </NODE>

    </NODE>


    <NODE name="PMSIETAT" label="RECUPERATION ETAT DES DOSSIERS">

        <NODE label="Initialisation">
            <sqlcmd><![CDATA[

                -- Séjours non groupés
                INSERT INTO pmsi.t_ghm(
                    code,
                    texte,
                    mco,
                    aso,
                    severite,
                    cmd_id,
                    ghm5_id,
                    ligne_oap_id,
                    cas_id)
                SELECT
                    '99Z99Z' AS code,
                    'Séjours sortis non groupés' AS texte,
                    '?' AS mco,
                    '?' AS aso,
                    'Z' AS severite,
                    COALESCE((SELECT oid FROM pmsi.t_cmd WHERE code = '99'),0) AS cmd_id,
                    COALESCE((SELECT oid FROM pmsi.t_ghm5 WHERE code = '99Z99'),0) AS ghm5_id,
                    0 AS ligne_oap_id,
                    COALESCE((SELECT oid FROM base.t_cas WHERE code = '?'),0) AS cas_id
                WHERE '99Z99Z' NOT IN (SELECT code FROM pmsi.t_ghm)
                UNION
                SELECT
                    '99Z98Z' AS code,
                    'GHM indisponible' AS texte,
                    '?' AS mco,
                    '?' AS aso,
                    'Z' AS severite,
                    COALESCE((SELECT oid FROM pmsi.t_cmd WHERE code = '99'),0) AS cmd_id,
                    COALESCE((SELECT oid FROM pmsi.t_ghm5 WHERE code = '99Z98'),0) AS ghm5_id,
                    0 AS ligne_oap_id,
                    COALESCE((SELECT oid FROM base.t_cas WHERE code = '?'),0) AS cas_id
                WHERE '99Z98Z' NOT IN (SELECT code FROM pmsi.t_ghm);



                SELECT pmsi.cti_initialize_rss_etat();

            ]]></sqlcmd>
        </NODE>

        <NODE label="Etat des dossiers">
            <sqlcmd><![CDATA[

				DROP TABLE IF EXISTS w_ETAT_PMSI;
				CREATE TEMP TABLE w_ETAT_PMSI AS
				SELECT ANNEE,
					CODE,
					ENFANT,
					MAX(NUMERO_RSS) AS NUMERO_RSS,
					date(MAX(SORTIE)) AS DATE_SORTIE,
					MAX(ETAT) AS ETAT,
					'20991231'::date AS DATE_GROUPAGE,
					'20991231'::date AS DATE_VALIDATION
				FROM prod_sigems.ETAT_PMSI
				GROUP BY ANNEE, CODE, ENFANT
				;

				CREATE INDEX w_ETAT_PMSI_i1
				  ON w_ETAT_PMSI
				  USING btree
				  (CODE);

				DROP TABLE IF EXISTS w_CONTROLE_DIM;
				CREATE TEMP TABLE w_CONTROLE_DIM AS
				SELECT
					ANNEE,
					CODE,
					(MAX(Array[to_char(CONTROLE_DIM.NL,'FM0000'), CASE WHEN CONTROLE_DIM.ETAT >= 0 THEN CONTROLE_DIM.DATE_ACTION::text ELSE '00010101' END]))[2]::date AS DATE_GROUPAGE,
					(MAX(Array[to_char(CONTROLE_DIM.NL,'FM0000'), CASE WHEN CONTROLE_DIM.ETAT >= 1 THEN CONTROLE_DIM.DATE_ACTION::text ELSE '00010101' END]))[2]::date AS DATE_VALIDATION
				FROM prod_sigems.CONTROLE_DIM
				WHERE ETAT <> 2
				GROUP BY 1,2
				ORDER BY 1,2
				;

				CREATE INDEX w_CONTROLE_DIM_i1
				  ON w_CONTROLE_DIM
				  USING btree
				  (CODE)
				;

				  
                UPDATE w_CONTROLE_DIM
				SET DATE_GROUPAGE = DATE_VALIDATION
				WHERE DATE_VALIDATION > DATE_GROUPAGE AND DATE_VALIDATION <> '00010101'
				;



				UPDATE w_ETAT_PMSI SET
					DATE_GROUPAGE = CASE WHEN w_CONTROLE_DIM.DATE_GROUPAGE <> '00010101' THEN w_CONTROLE_DIM.DATE_GROUPAGE ELSE '20991231' END,
					DATE_VALIDATION = CASE WHEN w_CONTROLE_DIM.DATE_VALIDATION <> '00010101' THEN w_CONTROLE_DIM.DATE_VALIDATION ELSE '20991231' END
				FROM w_CONTROLE_DIM
				WHERE w_ETAT_PMSI.ANNEE = w_CONTROLE_DIM.ANNEE AND
					w_ETAT_PMSI.CODE = w_CONTROLE_DIM.CODE AND
					(
						w_ETAT_PMSI.DATE_GROUPAGE IS DISTINCT FROM CASE WHEN w_CONTROLE_DIM.DATE_GROUPAGE <> '00010101' THEN w_CONTROLE_DIM.DATE_GROUPAGE ELSE '20991231' END OR
						w_ETAT_PMSI.DATE_VALIDATION IS DISTINCT FROM CASE WHEN w_CONTROLE_DIM.DATE_VALIDATION <> '00010101' THEN w_CONTROLE_DIM.DATE_VALIDATION ELSE '20991231' END
					)
				;


				CREATE INDEX w_ETAT_PMSI_i2
				  ON w_ETAT_PMSI
				  USING btree
				  (NUMERO_RSS)
				;



				UPDATE pmsi.p_rss_etat
				SET date_groupage = CASE WHEN w_ETAT_PMSI.DATE_GROUPAGE >= w_ETAT_PMSI.date_sortie THEN w_ETAT_PMSI.DATE_GROUPAGE ELSE '20991231' END
				FROM pmsi.p_rss
				JOIN w_ETAT_PMSI ON NUMERO_RSS = no_rss AND
					date_trunc('month',p_rss.date_sortie) = date_trunc('month',w_ETAT_PMSI.date_sortie)
				WHERE p_rss_etat.rss_id = p_rss.oid AND
					p_rss_etat.date_groupage IS DISTINCT FROM CASE WHEN w_ETAT_PMSI.DATE_GROUPAGE >= w_ETAT_PMSI.date_sortie THEN w_ETAT_PMSI.DATE_GROUPAGE ELSE '20991231' END;




				DROP TABLE IF EXISTS w_DOS2;
				CREATE TEMP TABLE w_DOS2 AS
				SELECT
					ANN_DOS2 || to_char(COD_DOS2,'FM900000') AS no_sejour,
					date(MAX(DFA_DOS2)) AS DFA_DOS2
				FROM prod_sigems.DOS2
				WHERE ETA_DOS2 >= 3 AND DFA_DOS2 IS NOT NULL
				GROUP BY 1;



				CREATE INDEX w_DOS2_i1
				  ON w_DOS2
				  USING btree
				  (no_sejour);



				UPDATE pmsi.p_rss_etat
				SET date_facture = CASE WHEN DFA_DOS2 >= date_sortie THEN DFA_DOS2 ELSE '20991231' END,
					est_facture = CASE WHEN DFA_DOS2 >= date_sortie AND DFA_DOS2 <> '20991231' THEN '1' ELSE '0' END
				FROM pmsi.p_rss
				JOIN w_DOS2 ON no_sejour = no_sejour_administratif
				WHERE p_rss_etat.rss_id = p_rss.oid AND
					(
					date_facture IS DISTINCT FROM CASE WHEN DFA_DOS2 >= date_sortie THEN DFA_DOS2 ELSE '20991231' END
					OR
					est_facture IS DISTINCT FROM CASE WHEN DFA_DOS2 >= date_sortie AND DFA_DOS2 <> '20991231' THEN '1' ELSE '0' END
					);






				UPDATE pmsi.p_rss_etat
				SET est_groupe = '1', est_valide = '1'
				WHERE est_facture = '1' AND
					(est_groupe = '0' OR est_valide = '0') ;


				UPDATE pmsi.p_rss_etat
				SET date_groupage = date_facture
				WHERE date_facture < date_groupage AND
					date_facture <> '20991231' AND
					date_groupage <> '20991231';


				UPDATE pmsi.p_rss_etat
				SET date_groupage = '20991231'
				WHERE date_groupage <> '20991231' AND
					est_groupe IN ('0','9');


				UPDATE pmsi.p_rss
				SET ghm_id = t_ghm_99Z98Z.oid,
					ghm_production_id = t_ghm_99Z98Z.oid
				FROM pmsi.p_rss_etat, pmsi.t_ghm, pmsi.t_ghm t_ghm_99Z98Z
				WHERE p_rss.oid = p_rss_etat.rss_id AND
					p_rss.ghm_id = t_ghm.oid AND
					t_ghm_99Z98Z.code = '99Z98Z' AND
					est_facture = '1' AND
					t_ghm.code = '99Z99Z' AND
					p_rss.oid NOT IN (
						SELECT rss_id
						FROM pmsi.p_rsf_detail
						JOIN pmsi.t_prestations ON prestation_id = t_prestations.oid
						WHERE t_prestations.code IN ('SE1', 'SE2', 'SE3', 'SE4', 'SE5', 'SE6', 'SE7', 'APE', 'AP2', 'FPI', 'FFM', 'ATU')
						);

				UPDATE pmsi.p_rss
				SET ghm_id = 0,
					ghm_production_id = 0
				FROM pmsi.p_rss_etat, pmsi.t_ghm, pmsi.t_ghm t_ghm_99Z98Z
				WHERE p_rss.oid = p_rss_etat.rss_id AND
					p_rss.en_cours = '1' AND
					p_rss.ghm_id = t_ghm.oid AND
					t_ghm_99Z98Z.code = '99Z98Z' AND
					est_facture = '1' AND
					t_ghm.code = '99Z99Z' AND
					p_rss.oid NOT IN (
						SELECT rss_id
						FROM pmsi.p_rsf_detail
						JOIN pmsi.t_prestations ON prestation_id = t_prestations.oid
						WHERE t_prestations.code IN ('SE1', 'SE2', 'SE3', 'SE4', 'SE5', 'SE6', 'SE7', 'APE', 'AP2', 'FPI', 'FFM', 'ATU')
						);

				UPDATE pmsi.p_rss
				SET ghm_id = t_ghm_99Z99Z.oid,
					ghm_production_id = t_ghm_99Z99Z.oid
				FROM pmsi.p_rss_etat, pmsi.t_ghm, pmsi.t_ghm t_ghm_99Z99Z
				WHERE p_rss.oid = p_rss_etat.rss_id AND
					p_rss.ghm_id = t_ghm.oid AND
					t_ghm_99Z99Z.code = '99Z99Z' AND
					en_cours = '1' AND
					t_ghm.code LIKE '90%';

				UPDATE pmsi.p_rss
				SET ghm_id = 0,
					ghm_production_id = 0
				FROM pmsi.p_rss_etat, pmsi.t_ghm
				WHERE p_rss.oid = p_rss_etat.rss_id AND
					p_rss.en_cours = '1' AND
					p_rss.ghm_id = t_ghm.oid AND
					est_facture = '1' AND
					(t_ghm.code = '99Z99Z' OR ghm_id = 0) AND
					p_rss.oid IN (
						SELECT rss_id
						FROM pmsi.p_rsf_detail
						JOIN pmsi.t_prestations ON prestation_id = t_prestations.oid
						WHERE t_prestations.code IN ('SE1', 'SE2', 'SE3', 'SE4', 'SE5', 'SE6', 'SE7', 'APE', 'AP2', 'FPI', 'FFM', 'ATU')
						);


				UPDATE pmsi.p_rss
				SET en_cours_etat = 'F'
				FROM pmsi.p_rss_etat
				WHERE p_rss.oid = p_rss_etat.rss_id AND
					en_cours = '1' AND
					en_cours_etat IS DISTINCT FROM 'F' AND
					est_facture = 1 ;


            ]]></sqlcmd>
        </NODE>

    </NODE>


</ROOT>