<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
	<NODE name="PMSICOMPLEMENTS" label="RECUPERATION DES DONNEES COMPLEMENTAIRES PMSI VITALIA">
		<NODE label="Paramètres">
			<sqlcmd><![CDATA[

				-- Recensement des médecins responsables séjour.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_sejour
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_sejour.idetab
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_sejour.idperson
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;

				-- Recensement des médecins UM.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_sejour
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_sejour.idetab
						JOIN prod_chataigneraie_mysql.stdev_person_sejour on stdev_person_sejour.id_sejour = stdev_sejour.id_sejour
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_person_sejour.idperson
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;

				-- Recensement des médecins acte.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_nbloccam
						join prod_chataigneraie_mysql.stdev_sejour on stdev_sejour.id_sejour = stdev_nbloccam.senum
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_nbloccam.idetab
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_nbloccam.bapnuax
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;
				
				-- Unités médicales.
				DROP TABLE IF EXISTS w_um
				;
				CREATE TEMP TABLE w_um AS 
					SELECT 
						to_char(valum,'FM0000') as code,
						MIN(lum) AS texte
					FROM prod_chataigneraie_mysql.stdev_unimed
					WHERE finess = '[FINESS]' 
					GROUP BY 1
					order by 1
				;
				 
				UPDATE pmsi.t_unites_medicales
				SET texte = w_um.texte
				FROM w_um
				WHERE 1=1
					AND t_unites_medicales.code = t_unites_medicales.texte
					AND w_um.code = t_unites_medicales.code
					AND w_um.texte != ''
				;
				
				INSERT INTO pmsi.t_unites_medicales (code, texte)
					SELECT
						w_um.code,
						w_um.texte
					FROM w_um
					WHERE w_um.code NOT IN (SELECT code FROM pmsi.t_unites_medicales)
					GROUP BY 1,2
				; 
			]]></sqlcmd>
		</NODE>
		<NODE label="Identités">
			<sqlcmd><![CDATA[
				
				
				-- Nom et prénom
				DROP TABLE IF EXISTS w_sejours;
				CREATE TEMP TABLE w_sejours AS
				SELECT 
					SENUM,
					CASE WHEN BENU <> 0 THEN SENUM * 1000000 + BENU ELSE SENUM END::text AS no_sejour_administratif,
					DUDTNA,
					DUNOMH,
					DUPNMH,
					DUNJFH,
					DATENT,
					DATSOR,
					STDEV_SEJBEBE.DUUNIQ,
					STDEV_SEJOUR.IDPERSON
				FROM  prod_chataigneraie_mysql.STDEV_SEJBEBE
				JOIN prod_chataigneraie_as400.NXXXFIC_DPATIENT ON NXXXFIC_DPATIENT.DUUNIQ = STDEV_SEJBEBE.DUUNIQ
				JOIN prod_chataigneraie_mysql.STDEV_ETAB ON STDEV_ETAB.IDETAB = STDEV_SEJBEBE.IDETAB AND STDEV_ETAB.FINESS = '[FINESS]'
				JOIN prod_chataigneraie_mysql.STDEV_SEJOUR ON SENUM = STDEV_SEJOUR.ID_SEJOUR AND STDEV_SEJBEBE.IDETAB = STDEV_SEJOUR.IDETAB
				;

		
				UPDATE pmsi.p_identites SET
					nom = DUNOMH,
					prenom = DUPNMH,
					nom_naissance = DUNJFH
				FROM pmsi.p_rss
				JOIN w_sejours ON p_rss.no_sejour_administratif =  w_sejours.no_sejour_administratif
				WHERE p_rss.oid = p_identites.rss_id AND
					p_rss.date_naissance = DUDTNA AND
					(
						p_rss.date_sortie = date(DATENT) OR
						p_rss.date_entree = date(DATSOR)
					) AND 
					(
						nom NOT ILIKE DUNOMH OR
						prenom NOT ILIKE DUPNMH OR
						nom_naissance NOT ILIKE DUNJFH
					)
				;
				
				UPDATE pmsi.p_rss SET
					no_patient  = w_sejours.DUUNIQ
				FROM w_sejours
				WHERE p_rss.no_sejour_administratif = w_sejours.no_sejour_administratif AND
					p_rss.date_naissance = DUDTNA AND
					(
						p_rss.date_sortie = date(DATENT) OR
						p_rss.date_entree = date(DATSOR)
					) AND 
					(
						no_patient <> w_sejours.DUUNIQ
					)
				;

			]]></sqlcmd>
		</NODE>
		<NODE label="Médecins RSS">
			<sqlcmd><![CDATA[

				DROP TABLE IF EXISTS w_pernu_medecin;
				CREATE TEMP TABLE w_pernu_medecin AS
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 NXXXFIC_DPERSON.PERNOM = STDEV_PERSON.PERNOM AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;



				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 substr(NXXXFIC_DPERSON.PERNOM,1,4) = substr(STDEV_PERSON.PERNOM,1,4) AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;

				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 substr(NXXXFIC_DPERSON.PERNOM,1,4) = substr(STDEV_PERSON.PERNOM,1,4) 
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;


				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				left JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.PERNOM = STDEV_PERSON.PERNOM AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				left JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;
		

				UPDATE pmsi.p_rss SET
					medecin_rss_id = w_pernu_medecin.medecin_id
				FROM w_sejours
				JOIN w_pernu_medecin ON w_pernu_medecin.PERNU = w_sejours.DPERNU
				WHERE p_rss.no_sejour_administratif = w_sejours.no_sejour_administratif AND
					p_rss.date_naissance = DUDTNA AND
					(
						p_rss.date_sortie = date(DATENT) OR
						p_rss.date_entree = date(DATSOR)
					) AND 
					(
						medecin_rss_id <> w_pernu_medecin.medecin_id
					)
				;

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

				
				
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Unités médicales Externes">
			<sqlcmd><![CDATA[
				
				
		]]></sqlcmd>
		</NODE>
	</NODE>
	<NODE name="PMSIENCOURS" label="RECUPERATION DES DOSSIERS PMSI EN-COURS VITALIA">
		<NODE label="Médecins">
			<sqlcmd><![CDATA[

				-- Recensement des médecins responsables séjour.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_sejour
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_sejour.idetab
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_sejour.idperson
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;

				-- Recensement des médecins UM.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_sejour
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_sejour.idetab
						JOIN prod_chataigneraie_mysql.stdev_person_sejour on stdev_person_sejour.id_sejour = stdev_sejour.id_sejour
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_person_sejour.idperson
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;

				-- Recensement des médecins acte.
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
					SELECT
						stdev_person.idperson,
						stdev_person.pernom,
						stdev_person.perpnm,
						COALESCE(t_specialites_medecin.oid, 0),
						stdev_person.pernse,
						0 -- COALESCE(t_specialites_internes_medecin.oid, 0)
					from prod_chataigneraie_mysql.stdev_nbloccam
						join prod_chataigneraie_mysql.stdev_sejour on stdev_sejour.id_sejour = stdev_nbloccam.senum
						join prod_chataigneraie_mysql.stdev_etab on stdev_etab.idetab = stdev_nbloccam.idetab
						join prod_chataigneraie_mysql.stdev_person on stdev_person.idperson = stdev_nbloccam.bapnuax
						LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = stdev_person.sc1nu
						--LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = SUBSTR(MDZTAR , 1, 2) || '..' -- Pas de spécialité interne identifiée.
						LEFT JOIN pmsi.t_medecins ON t_medecins.code = stdev_person.idperson
					WHERE 1=1
						AND to_char(date_entree, 'YYYY') >= [ANNEE] 
						and stdev_etab.finess = '[FINESS]' 
						AND t_medecins.code IS NULL
					GROUP BY 1,2,3,4,5,6
				;

			]]></sqlcmd>
		</NODE>
		<NODE label="Autres paramètres">
			<sqlcmd><![CDATA[
			
				-- Unités médicales.
				DROP TABLE IF EXISTS w_um
				;
				CREATE TEMP TABLE w_um AS 
					SELECT 
						to_char(valum,'FM0000') as code,
						MIN(lum) AS texte
					FROM prod_chataigneraie_mysql.stdev_unimed
					WHERE finess = '[FINESS]' 
					GROUP BY 1
					order by 1
				;
				 
				UPDATE pmsi.t_unites_medicales
				SET texte = w_um.texte
				FROM w_um
				WHERE 1=1
					AND t_unites_medicales.code = t_unites_medicales.texte
					AND w_um.code = t_unites_medicales.code
					AND w_um.texte != ''
				;
				
				INSERT INTO pmsi.t_unites_medicales (code, texte)
					SELECT
						w_um.code,
						w_um.texte
					FROM w_um
					WHERE w_um.code NOT IN (SELECT code FROM pmsi.t_unites_medicales)
					GROUP BY 1,2
				; 

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

				DROP SEQUENCE IF EXISTS w_sejours_sequence;
				CREATE TEMP SEQUENCE w_sejours_sequence;

				DROP TABLE IF EXISTS w_sejours;
				CREATE TEMP TABLE w_sejours AS
				SELECT 
					nextval('w_sejours_sequence'::regclass) AS sejour_id,
					STDEV_ETAB.IDETAB,
					STDEV_ETAB.FINESS,
					SENUM,
					BENU,
					CASE WHEN BENU <> 0 THEN SENUM * 1000000 + BENU ELSE SENUM END::text AS no_sejour_administratif,
					NUMRSS,
					DUSEXH,
					DUDTNA,
					DUNOMH,
					DUPNMH,
					DUNJFH,
					NOCDPH,
					DATENT,
					DATSOR,
					STDEV_SEJBEBE.DUUNIQ,
					NXXXFAC_NDOADM.DPERNU,
					STDEV_SEJBEBE.POINA
				FROM prod_chataigneraie_mysql.STDEV_SEJBEBE
				JOIN prod_chataigneraie_as400.NXXXFIC_DPATIENT ON NXXXFIC_DPATIENT.DUUNIQ = STDEV_SEJBEBE.DUUNIQ
				JOIN prod_chataigneraie_mysql.STDEV_ETAB ON STDEV_ETAB.IDETAB = STDEV_SEJBEBE.IDETAB AND STDEV_ETAB.FINESS = '[FINESS]'
				JOIN prod_chataigneraie_as400.NXXXFAC_NDOADM ON SENUM = NONUMA
				WHERE DATSOR BETWEEN '20140101' AND now() AND STID <> 3 AND NOFACT <> 'N' AND NOTAR <> 77181 AND HORSPMSI <> 1
				;


				UPDATE w_sejours 
				SET NUMRSS = 0-SENUM
				WHERE NUMRSS = 0
				;

				CREATE INDEX w_sejours_i1
				ON w_sejours 
				USING btree
				(SENUM);

				CREATE INDEX w_sejours_i2
				ON w_sejours 
				USING btree
				(sejour_id);
				


				DROP TABLE IF EXISTS w_STDEV_RUM;
				CREATE TEMP TABLE w_STDEV_RUM AS
				SELECT
					sejour_id,
					w_sejours.SENUM, 
					w_sejours.BENU, 
					w_sejours.IDETAB,
					substr(LIGNRSS,87,4) AS unite_medicale_code,
					rank() OVER (PARTITION BY w_sejours.SENUM, w_sejours.BENU ORDER BY MVTLIEU_DEB.DATENT) AS no_rum,
					MVTLIEU_DEB.DATENT, 
					to_char(MVTLIEU_DEB.MDEID,'FM9') AS MDEID,
					CASE WHEN MVTLIEU_DEB.PREID <> 0 THEN to_char(MVTLIEU_DEB.PREID,'FM0') ELSE '' END AS PREID,
					CASE WHEN date(MVTLIEU_FIN.DATSOR) >= w_sejours.DATENT THEN MVTLIEU_FIN.DATSOR ELSE w_sejours.DATSOR END, 
					to_char(MVTLIEU_FIN.MDSID,'FM9') AS MDSID,
					CASE WHEN MVTLIEU_DEB.DEEID <> 0 THEN to_char(MVTLIEU_DEB.DEEID,'FM0') ELSE '' END AS DEEID,
					COALESCE(t_ghm.oid,0) AS ghm_id,
					COALESCE(t_ghs.oid,0) AS ghs_id,
					COALESCE(CCAM4_GHM.code,'') AS ghm_code,
					NBCHIMIOS,
					IDMVTDEB,
					IDMVTFIN,
					0::bigint AS diagnostic_principal_id,
					0::bigint AS diagnostic_relie_id,
					0::bigint AS unite_medicale_id
				FROM w_sejours
				JOIN prod_chataigneraie_mysql.STDEV_RUM ON w_sejours.SENUM = stdev_rum.SENUMD AND 
								w_sejours.IDETAB = stdev_rum.IDETAB AND
								w_sejours.DUUNIQ = stdev_rum.DUUNIQ
				JOIN prod_chataigneraie_mysql.STDEV_MVTLIEU MVTLIEU_DEB ON STDEV_RUM.IDETAB = MVTLIEU_DEB.IDETAB AND IDMVTDEB = MVTLIEU_DEB.IDMVTL
				JOIN prod_chataigneraie_mysql.STDEV_MVTLIEU MVTLIEU_FIN ON STDEV_RUM.IDETAB = MVTLIEU_FIN.IDETAB AND IDMVTFIN = MVTLIEU_FIN.IDMVTL
				LEFT JOIN prod_chataigneraie_mysql.CCAM4_GHM ON CCAM4_GHM.ID = STDEV_RUM.IDGHM
				LEFT JOIN prod_chataigneraie_mysql.CCAM4_GHS ON CCAM4_GHS.ID = STDEV_RUM.IDGHS
				LEFT JOIN pmsi.t_ghm ON CCAM4_GHM.code = t_ghm.code
				LEFT JOIN pmsi.t_ghs ON CCAM4_GHS.ghs = t_ghs.code 
				WHERE date(STDEV_RUM.DTFIN) = '00010101'
				
				;

				DELETE FROM w_STDEV_RUM
				USING
					(
					SELECT
						SENUM,
						BENU,
						date(DATENT),
						date(DATSOR),
						ghm_id,
						MAX(CTID) AS del_ctid
					FROM w_STDEV_RUM
					GROUP BY 1,2,3,4,5
					HAVING count(*) > 1
					) subview
				WHERE w_STDEV_RUM.CTID = del_ctid;



				CREATE INDEX w_STDEV_RUM_i1
				ON w_STDEV_RUM
				USING btree
				(SENUM);

				CREATE INDEX w_STDEV_RUM_i2
				ON w_STDEV_RUM
				USING btree
				(IDMVTDEB);

				CREATE INDEX w_STDEV_RUM_i3
				ON w_STDEV_RUM
				USING btree
				(IDMVTDEB);

				CREATE INDEX w_STDEV_RUM_i4
				ON w_STDEV_RUM
				USING btree
				(sejour_id);


				UPDATE w_STDEV_RUM
				SET diagnostic_principal_id = t_diagnostics.oid
				FROM prod_chataigneraie_mysql.STDEV_DIAGP
				JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DPDIAGP
				JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				WHERE STDEV_DIAGP.IDMVTL = w_STDEV_RUM.IDMVTDEB
				;


				UPDATE w_STDEV_RUM
				SET diagnostic_principal_id = t_diagnostics.oid
				FROM prod_chataigneraie_mysql.STDEV_DIAGP
				JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DPDIAGP
				JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				WHERE STDEV_DIAGP.IDMVTL = w_STDEV_RUM.IDMVTFIN AND
					diagnostic_principal_id = 0
				;

				UPDATE w_STDEV_RUM
				SET diagnostic_principal_id = t_diagnostics.oid
				FROM prod_chataigneraie_mysql.STDEV_DIAGP
				JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DPDIAGP
				JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				WHERE STDEV_DIAGP.IDMVTL = w_STDEV_RUM.IDMVTFIN AND
					diagnostic_principal_id = 0
				;


				UPDATE w_STDEV_RUM
				SET diagnostic_relie_id = t_diagnostics.oid
				FROM prod_chataigneraie_mysql.STDEV_DIAGR
				JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DRDIAGR
				JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				WHERE STDEV_DIAGR.IDMVTL = w_STDEV_RUM.IDMVTDEB
				;

				UPDATE w_STDEV_RUM
				SET diagnostic_relie_id = t_diagnostics.oid
				FROM prod_chataigneraie_mysql.STDEV_DIAGR
				JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DRDIAGR
				JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				WHERE STDEV_DIAGR.IDMVTL = w_STDEV_RUM.IDMVTFIN AND
					diagnostic_relie_id = 0
				;

				UPDATE w_STDEV_RUM
				SET unite_medicale_id = t_unites_medicales.oid
				FROM pmsi.t_unites_medicales 
				WHERE unite_medicale_code = t_unites_medicales.code
				;




				DROP TABLE IF EXISTS w_STDEV_RSS;
				CREATE TEMP TABLE w_STDEV_RSS AS
				SELECT
					sejour_id,
					IDETAB,
					SENUM, 
					BENU, 
					MIN(DATENT) AS DATENT, 
					(MIN(Array[DATENT::text,MDEID::text]))[2] AS MDEID,
					(MIN(Array[DATENT::text,PREID]))[2] AS PREID,
					MAX(DATSOR) AS DATSOR, 
					(MAX(Array[DATSOR::text,MDSID::text]))[2] AS MDSID,
					(MAX(Array[DATSOR::text,DEEID::text]))[2] AS DEEID,
					MIN(ghm_id) AS ghm_id,
					MIN(ghs_id) AS ghs_id,
					MIN(ghm_code) AS ghm_code,
					SUM(NBCHIMIOS) AS NBCHIMIOS
				FROM w_STDEV_RUM
				GROUP BY 1,2,3,4
				;

				CREATE INDEX w_STDEV_RSS_i1
				ON w_STDEV_RSS
				USING btree
				(SENUM);

				CREATE INDEX w_STDEV_RSS_i2
				ON w_STDEV_RSS
				USING btree
				(sejour_id);
				
				



				DROP TABLE IF EXISTS w_pernu_medecin;
				CREATE TEMP TABLE w_pernu_medecin AS
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 NXXXFIC_DPERSON.PERNOM = STDEV_PERSON.PERNOM AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;



				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 substr(NXXXFIC_DPERSON.PERNOM,1,4) = substr(STDEV_PERSON.PERNOM,1,4) AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;

				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.SC1NU = STDEV_PERSON.SC1NU AND
					 substr(NXXXFIC_DPERSON.PERNOM,1,4) = substr(STDEV_PERSON.PERNOM,1,4) 
				JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;


				INSERT INTO w_pernu_medecin
				SELECT
					STDEV_PERSON.PERNU, MAX(t_medecins.oid) AS medecin_id
				FROM w_sejours
				LEFT JOIN w_pernu_medecin on w_sejours.DPERNU = w_pernu_medecin.PERNU
				JOIN prod_chataigneraie_AS400.NXXXFIC_DPERSON ON NXXXFIC_DPERSON.PERNU = DPERNU
				left JOIN prod_chataigneraie_mysql.STDEV_PERSON ON 
					NXXXFIC_DPERSON.PERNU = STDEV_PERSON.PERNU AND
					 NXXXFIC_DPERSON.PERNOM = STDEV_PERSON.PERNOM AND
					 NXXXFIC_DPERSON.PERPNM = STDEV_PERSON.PERPNM
				left JOIN pmsi.t_medecins ON t_medecins.code = IDPERSON
				WHERE w_pernu_medecin.medecin_id IS NULL AND STDEV_PERSON.PERNU IS NOT NULL
				GROUP BY 1;




				DROP TABLE IF EXISTS w_rss
				;
									
				CREATE TEMP TABLE w_rss AS 
					SELECT
						w_sejours.sejour_id,
						w_sejours.finess
						,nextval('pmsi.s_rss'::regclass) AS rss_id
						,w_sejours.NUMRSS AS no_rss
						,w_sejours.DUUNIQ AS no_patient
						,w_sejours.POINA AS poids_bebe
						,w_sejours.no_sejour_administratif
						,w_sejours.DUUNIQ
						,w_sejours.DUNOMH AS nom
						,w_sejours.DUPNMH AS prenom
						,w_sejours.DUNJFH AS nom_naissance
						,CASE 
							WHEN CASE WHEN date(w_sejours.DATSOR) < date(w_sejours.DATENT) THEN date(now()) ELSE date(w_sejours.DATSOR) END >= date(w_sejours.DATENT) 
							THEN trunc(
								(w_sejours.DATENT::date - (CASE WHEN date(w_sejours.DUDTNA) > '1880-01-01' THEN date(w_sejours.DUDTNA) ELSE '1979-12-17' END)::date) / 365.25
								,0)
							ELSE 0
						 END AS age
						,date(w_sejours.DATENT) AS date_entree
						,CASE WHEN date(w_sejours.DATSOR) < date(w_sejours.DATENT) THEN current_date ELSE date(w_sejours.DATSOR) END AS date_sortie
						,CASE WHEN date(w_sejours.DATSOR) > date(w_sejours.DATENT) THEN date(w_sejours.DATSOR) - date(w_sejours.DATENT) ELSE 0 END AS duree_sejour
						,CASE WHEN substr(COALESCE(w_STDEV_RSS.ghm_code,''), 1, 2) = '28' THEN w_STDEV_RSS.NBCHIMIOS ELSE 1 END AS nb_rsa
						,COALESCE(w_STDEV_RSS.MDEID,'8') AS mode_entree
						,COALESCE(w_STDEV_RSS.PREID,'') AS provenance
						,COALESCE(w_STDEV_RSS.MDSID,'8') AS mode_sortie
						,COALESCE(w_STDEV_RSS.DEEID,'') AS destination
						,date(w_sejours.DUDTNA) AS date_naissance
						,CASE WHEN DUSEXH = 'F' THEN  '2' ELSE '1' END AS sexe
						,w_sejours.NOCDPH AS code_postal
						,substr(COALESCE(w_STDEV_RSS.ghm_code,'') , 3 , 1) AS groupe_ghm
						,1 AS nb_rum
						,current_date AS date_import
						,COALESCE(w_STDEV_RSS.ghm_id,0) AS ghm_id
						,COALESCE(w_pernu_medecin.medecin_id, 0) AS medecin_rss_id
						,COALESCE(w_STDEV_RSS.ghs_id,0) AS ghs_id
						,to_char(w_sejours.DATSOR, 'YYYYMM')::numeric AS mois_sortie
						,COALESCE(w_STDEV_RSS.ghm_id,0) AS ghm_production_id
						,CASE WHEN substr(COALESCE(w_STDEV_RSS.ghm_code,''), 1, 2) = '28' THEN w_STDEV_RSS.NBCHIMIOS ELSE 0 END AS nb_seances
						,COALESCE(w_STDEV_RSS.ghm_code,'') AS ghm_code
					FROM w_sejours
						LEFT JOIN w_STDEV_RSS ON w_STDEV_RSS.sejour_id = w_sejours.sejour_id
						LEFT JOIN w_pernu_medecin ON w_pernu_medecin.PERNU = w_sejours.DPERNU
						LEFT JOIN pmsi.p_rss ON p_rss.no_sejour_administratif = w_sejours.no_sejour_administratif AND p_rss.etat = ''
						LEFT JOIN pmsi.p_rss p_rss2 ON p_rss2.no_rss = NUMRSS AND NUMRSS <> 0 AND p_rss2.etat = ''
					WHERE 1=1
						AND p_rss.no_rss IS NULL
						AND p_rss2.no_rss IS NULL
				;



				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)
				SELECT
					finess,
					rss_id,
					no_rss,
					no_patient,
					'' AS version_groupage,
					date_naissance, 
					sexe, 
					date_entree, 
					date_sortie,
					mode_entree, 
					provenance, 
					mode_sortie, 
					destination, 
					code_postal,
					poids_bebe, 
					0 AS igs2, 
					'' AS cma, 
					groupe_ghm,
					duree_sejour, 
					nb_rsa, 
					age,
					'' AS supprime, 
					date_import, 
					nb_rum, 
					'' AS secteur, 
					no_sejour_administratif, 
					nb_seances, 
					'' AS ghm_fg9, 
					ghm_id, 
					medecin_rss_id, 
					ghs_id, 
					mois_sortie, 
					0 AS diagnostic_principal_id, 
					0 AS diagnostic_relie_id, 
					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 1=1
							AND length(ghm_code) = 6
							AND substr(ghm_code, 1, 2) != '90'
							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, 
					'' AS code_retour_groupage, 
					0 AS comite_medical_id, 
					'0' AS rehosp_meme_ghm, 
					0 AS from_oid
				FROM w_rss
				;

			]]></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.no_rss AS no_rss, 
					nom AS nom, 
					prenom AS prenom, 
					nom_naissance AS nom_naissance
				FROM w_rss
				;

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

				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, 
					w_rss.rss_id,
					w_rss.no_rss,
					w_STDEV_RUM.no_rum,
					date(w_STDEV_RUM.DATENT) AS date_entree,
					date(w_STDEV_RUM.DATSOR) AS date_sortie,
					w_STDEV_RUM.MDEID,
					w_STDEV_RUM.PREID,
					w_STDEV_RUM.MDSID,
					w_STDEV_RUM.DEEID,
					date(w_STDEV_RUM.DATSOR) - date(w_STDEV_RUM.DATENT) AS duree_sejour,
					w_STDEV_RUM.NBCHIMIOS,
					w_STDEV_RUM.unite_medicale_code,
					w_STDEV_RUM.diagnostic_principal_id,
					w_STDEV_RUM.diagnostic_relie_id,
					w_STDEV_RUM.unite_medicale_id,
					''::text AS type_autorisation,
					0::numeric AS igs2,
					''::text AS type_autorisation_lit_dedie,
					''::text AS supplement_code
				FROM w_sejours
				JOIN w_rss ON  w_rss.sejour_id = w_sejours.sejour_id
				JOIN w_STDEV_RUM ON w_STDEV_RUM.sejour_id = w_sejours.sejour_id
				
				;

				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
					p_rss.finess  AS finess, 
					p_rss.oid AS rss_id, 
					p_rss.no_rss AS no_rss, 
					1 AS no_rum, 
					p_rss.date_entree, 
					p_rss.date_sortie, 
					p_rss.mode_entree, 
					p_rss.provenance, 
					p_rss.mode_sortie, 
					p_rss.destination, 
					p_rss.duree_sejour, 
					p_rss.nb_seances, 
					''::text AS unite_medicale, 
					p_rss.diagnostic_principal_id, 
					p_rss.diagnostic_relie_id, 
					0::bigint AS unite_medicale_id, 
					'' AS type_autorisation, 
					0 AS igs2, 
					'' AS type_autorisation_lit_dedie, 
					'' AS supplement_code
				FROM pmsi.p_rss
					LEFT JOIN pmsi.p_rss_rum ON p_rss_rum.rss_id = p_rss.oid
				WHERE p_rss.en_cours = '1' AND
					p_rss_rum.rss_id IS NULL;


				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
					FROM pmsi.p_rss_rum, pmsi.p_rss 
					WHERE 1=1
						AND p_rss.oid = p_rss_rum.rss_id
						AND en_cours = '1'
					GROUP BY 1
				;

				UPDATE pmsi.p_rss 
				SET 
					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,
					diagnostic_principal_id = p_rss_rum.diagnostic_principal_id,
					diagnostic_relie_id = p_rss_rum.diagnostic_relie_id,
					nb_seances = w_rss_rum.nb_seances
				FROM w_rss_rum, pmsi.p_rss_rum
				WHERE 1=1
					AND 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
				;

			]]></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, 
						p_rss_rum.rss_id, 
						w_rss.no_rss, 
						no_rum, 
						diagnostic_principal_id AS diagnostic_id, 
						'DP' AS type_diagnostic, 
						'DP' AS type_diagnostic_rss, 
						'' AS imprecis, 
						unite_medicale, 
						unite_medicale_id
				FROM pmsi.p_rss_rum 
					JOIN w_rss ON (p_rss_rum.rss_id = w_rss.rss_id)
				WHERE diagnostic_principal_id <> 0;
	
	
				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, 
						p_rss_rum.rss_id, 
						w_rss.no_rss, 
						no_rum, 
						diagnostic_relie_id AS diagnostic_id, 
						'DR' AS type_diagnostic, 
						'DR' AS type_diagnostic_rss, 
						'' AS imprecis, 
						unite_medicale, 
						unite_medicale_id
				FROM pmsi.p_rss_rum 
					JOIN w_rss ON (p_rss_rum.rss_id = w_rss.rss_id)
				WHERE diagnostic_relie_id <> 0;

				-- Insertion des diags. associés.
				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.no_rss,
					w_STDEV_RUM.no_rum,
					t_diagnostics.oid,
					'DA' AS type_diagnostic,
					'DA' AS type_diagnostic_rss,
					'' AS imprecis,
					w_STDEV_RUM.unite_medicale_code,
					w_STDEV_RUM.unite_medicale_id
				FROM w_STDEV_RUM
					JOIN w_rss ON w_STDEV_RUM.sejour_id = w_rss.sejour_id
					JOIN prod_chataigneraie_mysql.STDEV_DIAGA ON IDMVTDEB =  STDEV_DIAGA.IDMVTL
					JOIN prod_chataigneraie_mysql.STDEV_CIM10 ON IDDIAG = DADIAGA
					JOIN pmsi.t_diagnostics ON STDEV_CIM10.code = t_diagnostics.code
				;

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

				INSERT INTO pmsi.p_rss_actes(
					finess, 
					no_rss,
					rss_id,
					no_rum,
					unite_medicale,
					unite_medicale_id,
					date_acte,
					medecin_id,
					acte_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 AS finess,
					w_rss.no_rss AS no_rss,
					w_rss.rss_id AS rss_id,
					1 AS no_rum,
					'' AS unite_medicale,
					0 AS unite_medicale_id,
					date(BADINA) AS date_acte,
					COALESCE(t_medecins.oid, 0) AS medecin_id,
					COALESCE(t_actes.oid, 0) AS acte_id,
					1::numeric AS nombre,
					'0'::text AS phase_ccam,
					IDACTIVITE::text AS activite_ccam,
					''::text AS extension_ccam,
					''::text AS modificateur_ccam_1,
					''::text AS modificateur_ccam_2,
					''::text AS modificateur_ccam_3,
					''::text AS modificateur_ccam_4,
					''::text AS remboursement_excp_ccam,
					''::text AS association_nonprevue_ccam
				FROM w_sejours
				JOIN w_rss ON w_rss.sejour_id = w_sejours.sejour_id
				JOIN prod_chataigneraie_mysql.STDEV_NBLOCCAM ON 
					w_sejours.SENUM = STDEV_NBLOCCAM.SENUM AND 
					w_sejours.BENU = STDEV_NBLOCCAM.BENU AND 
					w_sejours.IDETAB = STDEV_NBLOCCAM.IDETAB 
				JOIN prod_chataigneraie_mysql.CCAM4_ACTES ON CHROACTE = CCAM4_ACTES.ID
				JOIN prod_chataigneraie_mysql.CCAM4_ACTATIVS ON CHROACTIV = CCAM4_ACTATIVS.ID
				LEFT JOIN pmsi.t_medecins ON t_medecins.code = BAPNUAX
				LEFT JOIN pmsi.t_actes ON CCAM4_ACTES.CODE = t_actes.code
				;

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

					
				-- Pas de facture
				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[

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

										
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Finalisation">
			<sqlcmd><![CDATA[

				
				DELETE FROM pmsi.p_rss_rum
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rss_actes
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rss_diagnostics
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rss_lpp
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);
				
				DELETE FROM pmsi.p_rss_ucd
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rss_etat
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rsf_total
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);

				DELETE FROM pmsi.p_rsf_detail
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);
				
				DELETE FROM pmsi.p_identites
				WHERE rss_id NOT IN (SELECT oid FROM pmsi.p_rss);


										
			]]></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_sejours;
				CREATE TEMP TABLE w_sejours AS
				SELECT 
					SENUM,
					CASE WHEN BENU <> 0 THEN SENUM * 1000000 + BENU ELSE SENUM END::text AS no_sejour_administratif,
					DUDTNA,
					DUNOMH,
					DUPNMH,
					DUNJFH,
					DATENT,
					DATSOR,
					STDEV_SEJBEBE.DUUNIQ,
					STDEV_SEJOUR.IDPERSON,
					NODFA1,
					NODFA2,
					dateprval,
					datedrval,
					CCAMOK,
					GHMOK
				FROM  prod_chataigneraie_mysql.STDEV_SEJBEBE
				JOIN prod_chataigneraie_as400.NXXXFIC_DPATIENT ON NXXXFIC_DPATIENT.DUUNIQ = STDEV_SEJBEBE.DUUNIQ
				JOIN prod_chataigneraie_mysql.STDEV_ETAB ON STDEV_ETAB.IDETAB = STDEV_SEJBEBE.IDETAB AND STDEV_ETAB.FINESS = '[FINESS]'
				JOIN prod_chataigneraie_mysql.STDEV_SEJOUR ON SENUM = STDEV_SEJOUR.ID_SEJOUR AND STDEV_SEJBEBE.IDETAB = STDEV_SEJOUR.IDETAB
				JOIN prod_chataigneraie_as400.NXXXFAC_NDOADM ON SENUM = NONUMA
				;




				UPDATE pmsi.p_rss_etat SET
					date_facture = GREATEST(NODFA1,NODFA2),
					est_facture = CASE WHEN date(NODFA1) >= date_sortie OR date(NODFA2) >= date_sortie THEN '1' ELSE '0' END
				FROM pmsi.p_rss
				JOIN w_sejours ON p_rss.no_sejour_administratif =  w_sejours.no_sejour_administratif
				WHERE p_rss_etat.rss_id = p_rss.oid AND
					(
					date_facture <> GREATEST(NODFA1,NODFA2) OR
					est_facture <> CASE WHEN date(NODFA1) >= date_sortie OR date(NODFA2) >= date_sortie 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 = GREATEST(DATEPRVAL,DATEDRVAL),
					est_groupe = '1',
					est_valide = '1'
				FROM pmsi.p_rss
				JOIN w_sejours ON p_rss.no_sejour_administratif =  w_sejours.no_sejour_administratif
				WHERE p_rss_etat.rss_id = p_rss.oid AND
					en_cours = '0' AND
					(
					date_groupage <> GREATEST(DATEPRVAL,DATEDRVAL) OR
					est_groupe <> '1' OR
					est_valide <> '1'
					)
				;



				UPDATE pmsi.p_rss_etat SET
					date_groupage = GREATEST(DATEPRVAL,DATEDRVAL),
					est_groupe = CASE WHEN substr(t_ghm.code,1,2) BETWEEN '01' AND '28' THEN '1' ELSE '0' END,
					est_valide = CASE WHEN substr(t_ghm.code,1,2) BETWEEN '01' AND '28' AND GHMOK = '1' THEN '1' ELSE '0' END
				FROM pmsi.p_rss
				JOIN w_sejours ON p_rss.no_sejour_administratif =  w_sejours.no_sejour_administratif
				JOIN pmsi.t_ghm ON p_rss.ghm_id = t_ghm.oid
				WHERE p_rss_etat.rss_id = p_rss.oid AND
					en_cours <> '0' AND
					(
					date_groupage <> GREATEST(DATEPRVAL,DATEDRVAL) OR
					est_groupe <> CASE WHEN substr(t_ghm.code,1,2) BETWEEN '01' AND '28' THEN '1' ELSE '0' END OR
					est_valide <> CASE WHEN substr(t_ghm.code,1,2) BETWEEN '01' AND '28' AND GHMOK = '1' THEN '1' ELSE '0' END
					)
				;
				
				UPDATE pmsi.p_rss_etat SET
					date_facture = '20991231'
				WHERE date_facture = '00010101'
				;
				
				UPDATE pmsi.p_rss_etat SET
					date_groupage = '20991231'
				WHERE date_groupage = '00010101'
				;


				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', 'APE', 'AP2', 'FPI', 'FFM', 'XTR') 
						);
						
				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', 'APE', 'AP2', 'FPI', 'FFM', 'XTR') 
						);
						
					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', 'APE', 'AP2', 'FPI', 'FFM', 'XTR') 
							);
						
						
					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>
