<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
	<NODE name="PMSICOMPLEMENTS" label="RECUPERATION DES DONNEES COMPLEMENTAIRES PMSI ORBIS SIM">
		<NODE label="Parametres">
			<sqlcmd><![CDATA[
			
				--------------------------------------------
				-- Médecins
				--------------------------------------------
				DROP TABLE IF EXISTS w_med;
				CREATE TEMP TABLE w_med AS
				-- Actes CCAM
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATACTESCCAM
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATACTESCCAM.ACTEXECCCAM
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATACTESCCAM.ACTEXECCCAM
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				UNION
				-- Historique Actes CCAM
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATACTESCCAMDELETED
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATACTESCCAMDELETED.ACTEXECCCAM
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATACTESCCAMDELETED.ACTEXECCCAM
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				UNION
				-- Médecins responsables de passages
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATPASSAGE
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATPASSAGE.PASRESP
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATPASSAGE.PASRESP
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				ORDER BY 1;
				
				
				-- Mise à jour des noms des médecins
				UPDATE pmsi.t_medecins SET
					nom = w_med.med_lib
				FROM
					w_med
				WHERE 1=1
					AND t_medecins.nom != w_med.med_lib 
					AND t_medecins.code = w_med.med_code;
				
				
				-- Ajout des nouveaux médecins
				INSERT INTO pmsi.t_medecins (code, nom)
				SELECT
					w_med.med_code
					,w_med.med_lib
				FROM
					w_med
					LEFT JOIN pmsi.t_medecins ON t_medecins.code = w_med.med_code
				WHERE
					t_medecins.code IS NULL;
					
				SELECT base.cti_initialize_medecins();
				SELECT pmsi.cti_reorganize_medecins_c();
				
				--------------------------------------------
				-- Unités médicales
				--------------------------------------------
				DROP TABLE IF EXISTS w_um;
				CREATE TEMP TABLE w_um AS 
				-- Actes CCAM
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTCODEUFCCAM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- Historique Actes CCAM
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATACTESCCAMDELETED ON DATACTESCCAMDELETED.ACTCODEUFCCAM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- Rums
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASUM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- UM déjà expédiées ATIH
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = NOMUM.CODE
					GROUP BY 1,2,3
				)
				ORDER BY 1;
				
				-- Mise à jour des libellés si code = texte
				UPDATE pmsi.t_unites_medicales SET
					texte = w_um.LIB
				FROM
					w_um
				WHERE 1=1
					AND w_um.CODE = t_unites_medicales.code
					AND t_unites_medicales.code = t_unites_medicales.texte;
				
				-- Ajout des nouvelles unités médicales
				INSERT INTO pmsi.t_unites_medicales (code, texte, type_autorisation)
				SELECT
					w_um.CODE
					,w_um.LIB
					,COALESCE(t_types_autorisations.code::text, ''::text)
				FROM
					w_um
					LEFT JOIN pmsi.t_types_autorisations on t_types_autorisations.code = w_um.DEFTYPEAUTOUM
					LEFT JOIN pmsi.t_unites_medicales um on um.code = w_um.CODE
				WHERE
					um.code IS NULL;
				
				-- Lien vers l'unité d'expédition
				UPDATE pmsi.t_unites_medicales SET
					um_expedition_id = oid
				WHERE
					um_expedition_id = 0 OR um_expedition_id IS NULL;
				
				--------------------------------------------
				-- Unités fonctionnelles
				--------------------------------------------
				DROP TABLE IF EXISTS w_uf;
				CREATE TEMP TABLE w_uf AS
				SELECT
					UPPER(DATACTESCCAM.ACTCODEUFCCAM) as code,
					COALESCE(NOMUM.LIB, UPPER(DATACTESCCAM.ACTCODEUFCCAM)) as lib
				FROM
					prod_sim.DATACTESCCAM
					LEFT JOIN prod_sim.NOMUM on NOMUM.CODE = UPPER(DATACTESCCAM.ACTCODEUFCCAM)
				GROUP BY 1,2
				ORDER BY 1;
				
				UPDATE pmsi.t_unites_fonctionnelles SET
					texte = w_uf.lib
				FROM 
					w_uf
				WHERE 1=1
					AND t_unites_fonctionnelles.code = w_uf.code
					AND t_unites_fonctionnelles.code = t_unites_fonctionnelles.texte
					AND t_unites_fonctionnelles.texte != w_uf.lib;
				
				INSERT INTO pmsi.t_unites_fonctionnelles(oid, code, texte)
				SELECT 0, chr(127) || '**', 'Non renseigné'
				WHERE 0 NOT IN (SELECT oid FROM pmsi.t_unites_fonctionnelles);
				
				INSERT INTO pmsi.t_unites_fonctionnelles (code, texte)
				SELECT
					w_uf.code
					,w_uf.lib
				FROM
					w_uf
					LEFT JOIN pmsi.t_unites_fonctionnelles uf ON uf.code = w_uf.code
				WHERE 1=1
					AND uf.code IS NULL
					AND w_uf.code IS DISTINCT FROM ''::text
				ORDER BY w_uf.code;
				
				--------------------------------------------
				-- UCD
				--------------------------------------------
				INSERT INTO pmsi.t_ucd (code, texte, texte_court)
				SELECT
					NOMUCD.CODE
					,NOMUCD.LIB
					,NOMUCD.LIB
				FROM
					prod_sim.DATUCD
					JOIN prod_sim.NOMUCD ON NOMUCD.CODE = DATUCD.UCDCODE
					LEFT JOIN pmsi.t_ucd ucd ON ucd.code = NOMUCD.CODE
				WHERE
					ucd.code is null
				GROUP BY 1,2,3;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Unite medicale sejours">
			<sqlcmd><![CDATA[
				
				DROP TABLE IF EXISTS w_DATPASSAGE;
				CREATE TEMP TABLE w_DATPASSAGE AS
				SELECT
					p_rss.oid AS rss_id,
					p_rss.no_sejour_administratif,
					p_rss_rum.date_entree,
					p_rss_rum.date_sortie,
					p_rss_rum.no_rum,
					MAX(t_unites_medicales_rum.code) AS unite_medicale_rum_code,
					MAX(DATPASSAGE.PASUM) AS PASUM,
					count(*)
				FROM [SCHEMA].p_rss_rum
				JOIN pmsi.t_unites_medicales t_unites_medicales_rum ON p_rss_rum.unite_medicale_id = t_unites_medicales_rum.oid
				JOIN [SCHEMA].p_rss ON p_rss_rum.rss_id = p_rss.oid
				JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
				JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID AND
					date(p_rss_rum.date_entree) = date(DATPASSAGE.PASENTREELE) AND
					date(p_rss_rum.date_sortie) = date(DATPASSAGE.PASSORTIELE) AND
					p_rss_rum.no_rum = DATPASSAGE.PASINDEX
				GROUP BY 1,2,3,4,5
				HAVING COUNT(*) = 1
				;
				
				UPDATE [SCHEMA].p_rss_rum
				SET unite_medicale_id = t_unites_medicales.oid
				FROM w_DATPASSAGE 
				JOIN pmsi.t_unites_medicales ON w_DATPASSAGE.PASUM = t_unites_medicales.code
				WHERE w_DATPASSAGE.rss_id = p_rss_rum.rss_id AND
					w_DATPASSAGE.date_entree = p_rss_rum.date_entree AND
					w_DATPASSAGE.date_sortie = p_rss_rum.date_sortie AND
					w_DATPASSAGE.no_rum = p_rss_rum.no_rum AND
					w_DATPASSAGE.unite_medicale_rum_code <> w_DATPASSAGE.PASUM 
				;
				
				UPDATE [SCHEMA].p_rss 
				SET unite_medicale_principale_id = p_rss_rum.unite_medicale_id 
				FROM [SCHEMA].p_rss_rum
				WHERE p_rss.oid = p_rss_rum.rss_id AND 
					p_rss.no_rum_principal = p_rss_rum.no_rum AND 
					p_rss.nb_rum = 1 AND
					p_rss.unite_medicale_principale_id <> p_rss_rum.unite_medicale_id
				;
				
				UPDATE [SCHEMA].p_rss_actes
				SET unite_medicale = p_rss_rum.unite_medicale,
					unite_medicale_id = p_rss_rum.unite_medicale_id
				FROM [SCHEMA].p_rss_rum
				WHERE p_rss_actes.rss_id = p_rss_rum.rss_id AND
					p_rss_actes.no_rum = p_rss_rum.no_rum AND
					NOT (
					p_rss_actes.unite_medicale = p_rss_rum.unite_medicale AND
					p_rss_actes.unite_medicale_id = p_rss_rum.unite_medicale_id
					)
				;
			]]></sqlcmd>
		</NODE>
		<NODE label="Unite fonctionnelle actes">
			<sqlcmd><![CDATA[
				
				UPDATE [SCHEMA].p_rss_actes SET
					unite_fonctionnelle_id = t_unites_fonctionnelles.oid
				FROM
					[SCHEMA].p_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTIDPASSCCAM = DATPASSAGE.PASID
					JOIN pmsi.t_unites_fonctionnelles on t_unites_fonctionnelles.code = DATACTESCCAM.ACTCODEUFCCAM
					JOIN pmsi.t_actes ON t_actes.code = DATACTESCCAM.ACTCODECCAM
				WHERE 1=1
					AND p_rss_actes.rss_id = p_rss.oid
					AND p_rss_actes.acte_id = t_actes.oid
					AND p_rss_actes.unite_fonctionnelle_id = 0
					AND p_rss_actes.date_acte = DATACTESCCAM.ACTDATECCAM
					AND p_rss_actes.activite_ccam = DATACTESCCAM.ACTACTIVITECCAM
					AND p_rss_actes.no_rum = DATPASSAGE.PASINDEX;
			]]></sqlcmd>
		</NODE>
		<NODE label="Interventions">
			<sqlcmd><![CDATA[
				
				WITH w_interv AS (
					SELECT
						p_rss.oid as rss_id
						,p_rss_actes.no_rum
						,t_actes.oid as acte_id
						,p_rss_actes.date_acte
						,p_rss_actes.activite_ccam
						,p_rss_actes.association_nonprevue_ccam
						,array_agg(ACTNUMINTERVCCAM) as interventions
					FROM
						[SCHEMA].p_rss
						JOIN [SCHEMA].p_rss_actes on p_rss_actes.rss_id = p_rss.oid
						JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
						JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
						JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTIDPASSCCAM = DATPASSAGE.PASID
						JOIN pmsi.t_actes ON t_actes.code = DATACTESCCAM.ACTCODECCAM
					WHERE 1=1
						AND p_rss.import_id = [IMPORT_ID]
						AND p_rss_actes.rss_id = p_rss.oid
						AND p_rss_actes.acte_id = t_actes.oid
						AND p_rss_actes.date_acte = DATACTESCCAM.ACTDATECCAM
						AND p_rss_actes.activite_ccam = DATACTESCCAM.ACTACTIVITECCAM
						AND p_rss_actes.association_nonprevue_ccam = DATACTESCCAM.ACTASSONONPREVUE
						AND p_rss_actes.no_rum = DATPASSAGE.PASINDEX
					GROUP BY 1,2,3,4,5,6
				)
				UPDATE [SCHEMA].p_rss_actes SET
					interventions = w_interv.interventions
				FROM w_interv
				WHERE 1=1
					AND p_rss_actes.rss_id = w_interv.rss_id
					AND p_rss_actes.no_rum = w_interv.no_rum
					AND p_rss_actes.acte_id = w_interv.acte_id
					AND p_rss_actes.date_acte = w_interv.date_acte
					AND p_rss_actes.association_nonprevue_ccam = w_interv.association_nonprevue_ccam
					AND p_rss_actes.activite_ccam = w_interv.activite_ccam;
			]]></sqlcmd>
		</NODE>
		<NODE label="Identites">
			<sqlcmd><![CDATA[
				
				UPDATE [SCHEMA].p_identites SET
					nom = IPPNOM
					,prenom = IPPPRENOM
					,nom_naissance = IPPNOMEPOUSE
				FROM
					[SCHEMA].p_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
					JOIN prod_sim.DATPATIENT ON DATPATIENT.IPPID = DATENTREE.ENTIPP
				WHERE 1=1
					AND p_rss.oid = p_identites.rss_id
					AND
					( 1!=1
						OR p_identites.nom <> DATPATIENT.IPPNOM
						OR p_identites.prenom <> DATPATIENT.IPPPRENOM
						OR p_identites.nom_naissance <> DATPATIENT.IPPNOMEPOUSE
					);
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Medecins actes">
			<sqlcmd><![CDATA[ 
				
				UPDATE [SCHEMA].p_rss_actes SET
					medecin_id = t_medecins.oid
				FROM
					[SCHEMA].p_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTIDPASSCCAM = DATPASSAGE.PASID
					JOIN pmsi.t_unites_medicales on t_unites_medicales.code = DATPASSAGE.PASUM
					JOIN pmsi.t_medecins ON t_medecins.code = DATACTESCCAM.ACTEXECCCAM,
					pmsi.t_actes
				WHERE 1=1
					AND p_rss_actes.medecin_id = 0
					AND p_rss_actes.acte_id = t_actes.oid
					AND p_rss_actes.rss_id = p_rss.oid
					AND t_actes.code = DATACTESCCAM.ACTCODECCAM
					AND p_rss_actes.date_acte = DATACTESCCAM.ACTDATECCAM
					AND p_rss_actes.activite_ccam = DATACTESCCAM.ACTACTIVITECCAM
					AND p_rss_actes.unite_medicale_id = t_unites_medicales.um_expedition_id;
				
				UPDATE [SCHEMA].p_rss_actes SET
					medecin_id = t_medecins.oid
				FROM
					[SCHEMA].p_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = p_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATACTESCCAMDELETED ON DATACTESCCAMDELETED.ACTIDPASSCCAM = DATPASSAGE.PASID
					JOIN pmsi.t_unites_medicales on t_unites_medicales.code = DATPASSAGE.PASUM
					JOIN pmsi.t_medecins ON t_medecins.code = DATACTESCCAMDELETED.ACTEXECCCAM,
					pmsi.t_actes
				WHERE 1=1
					AND p_rss_actes.medecin_id = 0
					AND p_rss_actes.acte_id = t_actes.oid
					AND p_rss_actes.rss_id = p_rss.oid
					AND t_actes.code = DATACTESCCAMDELETED.ACTCODECCAM
					AND p_rss_actes.date_acte = DATACTESCCAMDELETED.ACTDATECCAM
					AND p_rss_actes.activite_ccam = DATACTESCCAMDELETED.ACTACTIVITECCAM
					AND p_rss_actes.unite_medicale_id = t_unites_medicales.um_expedition_id;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Medecins RSS">
			<sqlcmd><![CDATA[ 
				
				-- Médecin de l'acte avec le plus fort ICR par RSS et par RUM
				DROP TABLE IF EXISTS w_max_icr;
				CREATE TEMP TABLE w_max_icr AS
				SELECT
					rss_id,
					no_rum,
					(COALESCE(
						MAX(
						CASE WHEN activite_ccam <> '4' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ADI' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ADA' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ATM'
						THEN Array[t_actes_c.icr_1,medecin_id] ELSE NULL END),
						MAX(
						CASE WHEN activite_ccam <> '4' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ADI' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ADA' 
						THEN Array[t_actes_c.icr_1,medecin_id] ELSE NULL END),
						MAX(
						CASE WHEN activite_ccam <> '4' AND 
							t_actes_c.ccam_regroupement_code_1 <> 'ADA' 
						THEN Array[t_actes_c.icr_1,medecin_id] ELSE NULL END),
						Array[0,0]
					)) AS icr_medecin
				FROM 
					[SCHEMA].p_rss_actes p_rss_actes
					JOIN pmsi.t_actes_c ON p_rss_actes.acte_id = t_actes_c.oid
				GROUP BY 1,2
				ORDER BY 1,2;
				
				-- Médecin RSS de l'exécutant du plus fort ICR du premier RUM
				-- Si l'option OPTPMSI_MED_RSS vaut 1
				UPDATE [SCHEMA].p_rss SET
					medecin_rss_id = subview.medecin_id
				FROM 
					(
						SELECT 
							rss_id, 
							icr_medecin[2]::bigint as medecin_id
						FROM
							w_max_icr
						where no_rum = 1
					) subview, pmsi.t_divers
				WHERE 1=1
					AND subview.rss_id = p_rss.oid
					AND p_rss.medecin_rss_id IS DISTINCT FROM subview.medecin_id
					AND t_divers.code = 'OPTPMSI_MED_RSS' 
					AND t_divers.valeur = '1';
				
				-- Médecin RSS de l'exécutant du plus fort ICR, peu importe le RUM
				-- Si l'option OPTPMSI_MED_RSS vaut 0 ou s'il reste des médecins RSS non renseignés
				UPDATE [SCHEMA].p_rss SET
					medecin_rss_id = subview.medecin_id
				FROM 
					(
						SELECT 
							rss_id, 
							(MAX(icr_medecin))[2]::bigint as medecin_id
						FROM
							w_max_icr
						GROUP BY 1
					) subview, pmsi.t_divers
				WHERE 1=1
					AND subview.rss_id = p_rss.oid
					AND p_rss.medecin_rss_id IS DISTINCT FROM subview.medecin_id
					AND t_divers.code = 'OPTPMSI_MED_RSS' 
					AND (t_divers.valeur = '0' OR medecin_rss_id = 0);
				
				
				-- S'il reste des séjours sans médecins, on prend le responsable du premier RUM
				-- Si l'option OPTPMSI_MED_RSS vaut 1
				UPDATE [SCHEMA].p_rss SET
					medecin_rss_id = t_medecins.oid
				FROM
					prod_sim.DATENTREE
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN pmsi.t_medecins ON t_medecins.code = DATPASSAGE.PASRESP,
					pmsi.t_divers
				where 1=1
					AND DATENTREE.ENTNUM = p_rss.no_sejour_administratif
					AND DATPASSAGE.PASINDEX = 1
					AND medecin_rss_id = 0
					AND t_divers.code = 'OPTPMSI_MED_RSS' 
					AND t_divers.valeur = '1';
			]]></sqlcmd>
		</NODE>
	</NODE>
	<NODE name="PMSIENCOURS" label="RECUPERATION DES DOSSIERS PMSI EN-COURS ORBIS SIM">
		<NODE label="Paramètres">
			<sqlcmd><![CDATA[
				
				--------------------------------------------
				-- Médecins
				--------------------------------------------
				DROP TABLE IF EXISTS w_med;
				CREATE TEMP TABLE w_med AS
				-- Actes CCAM
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATACTESCCAM
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATACTESCCAM.ACTEXECCCAM
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATACTESCCAM.ACTEXECCCAM
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				UNION
				-- Historique Actes CCAM
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATACTESCCAMDELETED
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATACTESCCAMDELETED.ACTEXECCCAM
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATACTESCCAMDELETED.ACTEXECCCAM
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				UNION
				-- Médecins responsables de passages
				(
					SELECT
						COALESCE(NOMEXECUTANT.CODE, NOMPRAT.CODE) as med_code
						,COALESCE(NOMEXECUTANT.LIB, NOMPRAT.LIB) as med_lib
					FROM
						prod_sim.DATPASSAGE
						LEFT JOIN prod_sim.NOMEXECUTANT ON NOMEXECUTANT.CODE = DATPASSAGE.PASRESP
						LEFT JOIN prod_sim.NOMPRAT ON NOMPRAT.CODE = DATPASSAGE.PASRESP
					WHERE 1!=1
						OR NOMEXECUTANT.CODE IS DISTINCT FROM NULL
						OR NOMPRAT.CODE IS DISTINCT FROM NULL
					GROUP BY 1, 2
				)
				ORDER BY 1;
				
				
				-- Mise à jour des noms des médecins
				UPDATE pmsi.t_medecins SET
					nom = w_med.med_lib
				FROM
					w_med
				WHERE 1=1
					AND t_medecins.nom != w_med.med_lib 
					AND t_medecins.code = w_med.med_code;
				
				
				-- Ajout des nouveaux médecins
				INSERT INTO pmsi.t_medecins (code, nom)
				SELECT
					w_med.med_code
					,w_med.med_lib
				FROM
					w_med
					LEFT JOIN pmsi.t_medecins ON t_medecins.code = w_med.med_code
				WHERE
					t_medecins.code IS NULL;
					
				SELECT base.cti_initialize_medecins();
				SELECT pmsi.cti_reorganize_medecins_c();
				
				--------------------------------------------
				-- Unités médicales
				--------------------------------------------
				DROP TABLE IF EXISTS w_um;
				CREATE TEMP TABLE w_um AS 
				-- Actes CCAM
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTCODEUFCCAM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- Historique Actes CCAM
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATACTESCCAMDELETED ON DATACTESCCAMDELETED.ACTCODEUFCCAM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- Rums
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASUM = NOMUM.CODE
					GROUP BY 1,2,3
				)
				UNION
				-- UM déjà expédiées ATIH
				(
					SELECT
						NOMUM.CODE,
						NOMUM.LIB,
						NOMUM.DEFTYPEAUTOUM
					FROM
						prod_sim.NOMUM
						JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = NOMUM.CODE
					GROUP BY 1,2,3
				)
				ORDER BY 1;
				
				-- Mise à jour des libellés si code = texte
				UPDATE pmsi.t_unites_medicales SET
					texte = w_um.LIB
				FROM
					w_um
				WHERE 1=1
					AND w_um.CODE = t_unites_medicales.code
					AND t_unites_medicales.code = t_unites_medicales.texte;
				
				-- Ajout des nouvelles unités médicales
				INSERT INTO pmsi.t_unites_medicales (code, texte, type_autorisation)
				SELECT
					w_um.CODE
					,w_um.LIB
					,COALESCE(t_types_autorisations.code::text, ''::text)
				FROM
					w_um
					LEFT JOIN pmsi.t_types_autorisations on t_types_autorisations.code = w_um.DEFTYPEAUTOUM
					LEFT JOIN pmsi.t_unites_medicales um on um.code = w_um.CODE
				WHERE
					um.code IS NULL;
				
				-- Lien vers l'unité d'expédition
				UPDATE pmsi.t_unites_medicales SET
					um_expedition_id = oid
				WHERE
					um_expedition_id = 0 OR um_expedition_id IS NULL;
				
				--------------------------------------------
				-- Unités fonctionnelles
				--------------------------------------------
				DROP TABLE IF EXISTS w_uf;
				CREATE TEMP TABLE w_uf AS
				SELECT
					UPPER(DATACTESCCAM.ACTCODEUFCCAM) as code,
					COALESCE(NOMUM.LIB, UPPER(DATACTESCCAM.ACTCODEUFCCAM)) as lib
				FROM
					prod_sim.DATACTESCCAM
					LEFT JOIN prod_sim.NOMUM on NOMUM.CODE = UPPER(DATACTESCCAM.ACTCODEUFCCAM)
				GROUP BY 1,2
				ORDER BY 1;
				
				UPDATE pmsi.t_unites_fonctionnelles SET
					texte = w_uf.lib
				FROM 
					w_uf
				WHERE 1=1
					AND t_unites_fonctionnelles.code = w_uf.code
					AND t_unites_fonctionnelles.code = t_unites_fonctionnelles.texte
					AND t_unites_fonctionnelles.texte != w_uf.lib;
				
				INSERT INTO pmsi.t_unites_fonctionnelles(oid, code, texte)
				SELECT 0, chr(127) || '**', 'Non renseigné'
				WHERE 0 NOT IN (SELECT oid FROM pmsi.t_unites_fonctionnelles);
				
				INSERT INTO pmsi.t_unites_fonctionnelles (code, texte)
				SELECT
					w_uf.code
					,w_uf.lib
				FROM
					w_uf
					LEFT JOIN pmsi.t_unites_fonctionnelles uf ON uf.code = w_uf.code
				WHERE 1=1
					AND uf.code IS NULL
					AND w_uf.code IS DISTINCT FROM ''::text
				ORDER BY w_uf.code;
				
				--------------------------------------------
				-- UCD
				--------------------------------------------
				INSERT INTO pmsi.t_ucd (code, texte, texte_court)
				SELECT
					NOMUCD.CODE
					,NOMUCD.LIB
					,NOMUCD.LIB
				FROM
					prod_sim.DATUCD
					JOIN prod_sim.NOMUCD ON NOMUCD.CODE = DATUCD.UCDCODE
					LEFT JOIN pmsi.t_ucd ucd ON ucd.code = NOMUCD.CODE
				WHERE
					ucd.code is null
				GROUP BY 1,2,3;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="RSS">
			<sqlcmd><![CDATA[
				
				-- Séjours existant dans SIM mais pas dans CTI
				DROP TABLE IF EXISTS w_sej;
				CREATE TEMP TABLE w_sej AS
				SELECT
					ENTNUM
				FROM
					prod_sim.DATENTREE
					LEFT JOIN pmsi.p_rss ON p_rss.no_sejour_administratif = DATENTREE.ENTNUM
				WHERE 1=1
					AND p_rss.no_sejour_administratif IS NULL
					AND DATENTREE.ENTSORTIELE >= '[ANNEE]0101'
					AND DATENTREE.ENTENTREELE <= now();
					
				CREATE INDEX i_rss_11 ON w_sej
				USING btree
				(ENTNUM);
					
				DROP TABLE IF EXISTS w_rss;
				CREATE TEMP TABLE w_rss AS
				SELECT
					base.cti_to_number(DATENTREE.ENTIPP) AS no_patient
					,'[FINESS]'::text as finess
					,DATRESUMEMCO.RSSNUMANONYME as no_rss
					,''::text as version_groupage
					,date(DATPATIENT.IPPNAISSANCE) as date_naissance
					,DATRESUMEMCO.RSSSEXE AS sexe
					,date(DATENTREE.ENTENTREELE) AS date_entree
					,date(DATENTREE.ENTSORTIELE) AS date_sortie
					,DATRESUMEMCO.RSSME as mode_entree
					,DATRESUMEMCO.RSSPROV as provenance
					,DATRESUMEMCO.RSSMS as mode_sortie
					,DATRESUMEMCO.RSSDEST as destination
					,COALESCE(t_codes_postaux_c.code, ''::text) as code_postal
					,DATRESUMEMCO.RSSPOIDS as poids_bebe
					,DATRESUMEMCO.RSSIGS as igs2
					,''::text as cma
					,COALESCE(substr(DATRESUMEMCO.RSSGHM, 3, 1), ''::text) as groupe_ghm
					,DATRESUMEMCO.RSSDUREE as duree_sejour
					,GREATEST(DATRESUMEMCO.RSSSEANCES,1) as nb_rsa
					,DATRESUMEMCO.RSSAGEAN as age
					,''::text as supprime
					,now() AS date_import
					,DATRESUMEMCO.RSSNBRERUM as nb_rum
					,''::text as secteur
					,DATENTREE.ENTNUM as no_sejour_administratif
					,DATRESUMEMCO.RSSSEANCES as nb_seances
					,''::text as ghm_fg9
					,COALESCE(t_ghm.oid, 0) as ghm_id
					,0::bigint as medecin_rss_id
					,COALESCE(t_ghs.oid, 0) as ghs_id
					,date_part('year', date(DATENTREE.ENTSORTIELE)) * 100 + date_part('month', date(DATENTREE.ENTSORTIELE)) AS mois_sortie
					,COALESCE(dp.oid, 0) as diagnostic_principal_id
					,COALESCE(dr.oid, 0) as diagnostic_relie_id
					,COALESCE(t_ghm.oid, 0) as ghm_production_id
					,DATRESUMEMCO.RSSNOCLASSANT as no_rum_principal
					,COALESCE(t_unites_medicales.oid, 0) as unite_medicale_principale_id
					,nextval('pmsi.s_rss'::regclass) as rss_id
					,-1::bigint as import_id
					,''::text as etat
					,0::bigint as acte_principal_id
					,COALESCE(t_codes_postaux_c.oid, 0) as code_postal_id
					,0::bigint as patient_id
					,0::bigint as prestation_principale_id
					,''::text as severite_fg11_simulation_code
					,'1'::text as en_cours
					,CASE WHEN DATRESUMEMCO.RSSCMD <> '90' THEN 'V'::text ELSE 'S'::text END as en_cours_etat
					,0::bigint as base_remboursement
					,0::bigint as sejour_facture
					,0::bigint as honoraires_factures
					,0::bigint as t2a_facture
					,0::bigint as dmi_facture
					,0::bigint as phc_facture
					,0::bigint as ca_ghs_theorique
					,0::bigint as nb_ghs
					,0::bigint as ca_ghs
					,0::bigint as nb_exb
					,0::bigint as ca_exb
					,0::bigint as nb_exh
					,0::bigint as ca_exh
					,0::bigint as nb_si
					,0::bigint as ca_si
					,0::bigint as nb_sur
					,0::bigint as ca_sur
					,0::bigint as nb_rea
					,0::bigint as ca_rea
					,0::bigint as nb_neonat
					,0::bigint as ca_neonat
					,''::text as traitement_epmsi
					,''::text as code_retour_groupage
					,0::bigint as comite_medical_id
					,''::text as rehosp_meme_ghm
					,0::bigint as from_oid
					,0::bigint as forfait_facture
					,DATRESUMEMCO.RSSAGEGEST as age_gestationnel
					,'20991231'::date as date_dernieres_regles
					,''::text as confirmation_codage
					,''::text as type_machine_rdth
					,''::text as type_dosimetrie
					,''::text as numero_innovation
					,0::bigint as rss_mere_id
					,0::smallint as nombre_ivg_anterieures
					,0::smallint as annee_ivg_precedente
					,0::bigint as nombre_naissances_vivantes_anterieures
					,0::bigint as nb_ghsmin
				FROM
					prod_sim.DATENTREE
					JOIN w_sej ON DATENTREE.ENTNUM = w_sej.ENTNUM
					JOIN prod_sim.DATRESUMEMCO ON DATRESUMEMCO.RSSENTID = DATENTREE.ENTID
					JOIN prod_sim.DATPATIENT ON DATPATIENT.IPPID = DATENTREE.ENTIPP
					LEFT JOIN pmsi.t_ghm ON t_ghm.code = DATRESUMEMCO.RSSGHM
					LEFT JOIN pmsi.t_codes_postaux_c ON t_codes_postaux_c.code = DATRESUMEMCO.RSSCP
					LEFT JOIN pmsi.t_ghs ON t_ghs.code_text = DATRESUMEMCO.RSSGHS
					LEFT JOIN pmsi.t_diagnostics dp on dp.code = DATRESUMEMCO.RSSDP
					LEFT JOIN pmsi.t_diagnostics dr on dr.code = DATRESUMEMCO.RSSDR
					LEFT JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = DATRESUMEMCO.RSSUMCLASSANT;
					
				INSERT INTO pmsi.p_rss (
					no_patient
					,finess
					,no_rss
					,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
					,oid
					,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
					,forfait_facture
					,age_gestationnel
					,date_dernieres_regles
					,confirmation_codage
					,type_machine_rdth
					,type_dosimetrie
					,numero_innovation
					,rss_mere_id
					,nombre_ivg_anterieures
					,annee_ivg_precedente
					,nombre_naissances_vivantes_anterieures
					,nb_ghsmin)
				SELECT
					no_patient
					,finess
					,no_rss
					,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
					,rss_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
					,forfait_facture
					,age_gestationnel
					,date_dernieres_regles
					,confirmation_codage
					,type_machine_rdth
					,type_dosimetrie
					,numero_innovation
					,rss_mere_id
					,nombre_ivg_anterieures
					,annee_ivg_precedente
					,nombre_naissances_vivantes_anterieures
					,nb_ghsmin	
				FROM w_rss;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Identités">
			<sqlcmd><![CDATA[ 
				
				INSERT INTO pmsi.p_identites(
						no_rss
						,nom
						,finess
						,rss_id
						,prenom
						,nom_naissance)
				SELECT
						w_rss.no_rss,
						DATPATIENT.IPPNOM AS nom,
						w_rss.finess,
						w_rss.rss_id,
						DATPATIENT.IPPPRENOM AS prenom,
						DATPATIENT.IPPNOMEPOUSE AS nom_naissance
				FROM w_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = w_rss.no_sejour_administratif
					JOIN prod_sim.DATPATIENT ON DATPATIENT.IPPID = DATENTREE.ENTIPP
				;
			]]></sqlcmd>
		</NODE>
		<NODE label="Rum">
			<sqlcmd><![CDATA[ 
				
				INSERT INTO pmsi.p_rss_rum(
					finess
					,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
					,rss_id
					,igs2
					,type_autorisation_lit_dedie
					,supplement_code)
				SELECT
					w_rss.finess as finess
					,w_rss.no_rss as no_rss
					,DATPASSAGE.PASINDEX as no_rum
					,DATPASSAGE.PASENTREELE as date_entree
					,DATPASSAGE.PASSORTIELE as date_sortie
					,DATPASSAGE.PASME as mode_entree
					,DATPASSAGE.PASPROV as provenance
					,DATPASSAGE.PASMS as mode_sortie
					,DATPASSAGE.PASDEST as destination
					,DATPASSAGE.PASDUREE as duree_sejour
					,DATINFOPASCS.PCSSEANCES as nb_seances
					,DATPASSAGE.PASUM as unite_medicale
					,COALESCE(dp.oid, 0) as diagnostic_principal_id
					,COALESCE(dr.oid, 0) as diagnostic_relie_id
					,t_unites_medicales.oid as unite_medicale_id
					,DATPASSAGE.PASAUTORISATIONUM as type_autorisation
					,w_rss.rss_id as rss_id
					,DATINFOPASCS.PCSIGS2 as igs2
					,''::text as type_autorisation_lit_dedie
					,''::text as supplement_code
				FROM
					w_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = w_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					LEFT JOIN prod_sim.DATDIAGS sim_dp ON sim_dp.DIAGIDPASS = DATPASSAGE.PASID 
						AND sim_dp.DIAGTYPE = 'DP'
					LEFT JOIN pmsi.t_diagnostics dp on dp.code = sim_dp.DIAGCODE
					LEFT JOIN prod_sim.DATDIAGS sim_dr ON sim_dr.DIAGIDPASS = DATPASSAGE.PASID 
						AND sim_dr.DIAGTYPE = 'DR'
					LEFT JOIN pmsi.t_diagnostics dr on dr.code = sim_dr.DIAGCODE
					JOIN prod_sim.DATINFOPASCS ON DATINFOPASCS.PCSID = DATPASSAGE.PASID
					JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = DATPASSAGE.PASUM;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Diagnostics">
			<sqlcmd><![CDATA[ 
				
				INSERT INTO pmsi.p_rss_diagnostics (
					finess
					,no_rss
					,no_rum
					,diagnostic_id
					,type_diagnostic
					,type_diagnostic_rss
					,imprecis
					,unite_medicale
					,rss_id
					,unite_medicale_id
				)
				SELECT
					w_rss.finess as finess
					,w_rss.no_rss as no_rss
					,DATPASSAGE.PASINDEX as no_rum
					,COALESCE(t_diagnostics.oid, 0) as diagnostic_id
					,DATDIAGS.DIAGTYPE as type_diagnostic
					,CASE
						WHEN DATRESUMEMCO.RSSNOCLASSANT <> DATPASSAGE.PASINDEX AND DATDIAGS.DIAGTYPE in ('DP', 'DR') THEn 'DA'
						ELSE DATDIAGS.DIAGTYPE
					END as type_diagnostic_rss
					,''::text as imprecis
					,DATPASSAGE.PASUM as unite_medicale
					,w_rss.rss_id as rss_id
					,COALESCE(t_unites_medicales.oid, 0) as unite_medicale_id
				FROM
					w_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = w_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATDIAGS ON DATDIAGS.DIAGIDPASS = DATPASSAGE.PASID 
					JOIN prod_sim.DATRESUMEMCO ON DATRESUMEMCO.RSSENTID = DATENTREE.ENTID
					LEFT JOIN pmsi.t_diagnostics on t_diagnostics.code = DATDIAGS.DIAGCODE
					LEFT JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = DATPASSAGE.PASUM;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="Actes">
			<sqlcmd><![CDATA[ 
				
				INSERT INTO pmsi.p_rss_actes (
					finess
					,no_rss
					,no_rum
					,unite_medicale
					,date_acte
					,medecin_id
					,acte_id
					,phase_ccam
					,activite_ccam
					,extension_ccam
					,modificateur_ccam_1
					,modificateur_ccam_2
					,modificateur_ccam_3
					,modificateur_ccam_4
					,remboursement_excp_ccam
					,association_nonprevue_ccam
					,rss_id
					,unite_medicale_id
					,condition_gm_ok
					,unite_fonctionnelle_id
					,nombre
					,interventions
				)
				SELECT
					w_rss.finess as finess
					,w_rss.no_rss as no_rss
					,DATPASSAGE.PASINDEX as no_rum
					,DATPASSAGE.PASUM as unite_medicale
					,DATACTESCCAM.ACTDATECCAM as date_acte
					,COALESCE(t_medecins.oid, 0) as medecin_id
					,COALESCE(t_actes.oid, 0) AS acte_id
					,DATACTESCCAM.ACTPHASECCAM as phase_ccam
					,DATACTESCCAM.ACTACTIVITECCAM as activite_ccam
					,DATACTESCCAM.ACTEXTDOCUM as extension_ccam
					,DATACTESCCAM.ACTMODIFICATEUR1 as modificateur_ccam_1
					,DATACTESCCAM.ACTMODIFICATEUR2 as modificateur_ccam_2
					,DATACTESCCAM.ACTMODIFICATEUR3 as modificateur_ccam_3
					,DATACTESCCAM.ACTMODIFICATEUR4 as modificateur_ccam_4
					,DATACTESCCAM.ACTREMBEXCEP as remboursement_excp_ccam
					,DATACTESCCAM.ACTASSONONPREVUE as association_nonprevue_ccam
					,w_rss.rss_id as rss_id
					,COALESCE(t_unites_medicales.oid, 0) as unite_medicale_id
					,''::character(1) as condition_gm_ok
					,COALESCE(t_unites_fonctionnelles.oid, 0) as unite_fonctionnelle_id
					,count(*) as nombre
					,array_agg(ACTNUMINTERVCCAM) as interventions
				FROM
					w_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = w_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATRESUMEMCO ON DATRESUMEMCO.RSSENTID = DATENTREE.ENTID
					JOIN prod_sim.DATACTESCCAM ON DATACTESCCAM.ACTIDPASSCCAM = DATPASSAGE.PASID
					LEFT JOIN pmsi.t_medecins ON t_medecins.code = DATACTESCCAM.ACTEXECCCAM
					LEFT JOIN pmsi.t_unites_medicales ON t_unites_medicales.code = DATPASSAGE.PASUM
					LEFT JOIN pmsi.t_actes ON t_actes.code = DATACTESCCAM.ACTCODECCAM
					LEFT JOIN pmsi.t_unites_fonctionnelles ON t_unites_fonctionnelles.code = DATACTESCCAM.ACTCODEUFCCAM
				GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20;
				
			]]></sqlcmd>
		</NODE>
		<NODE label="UCD">
			<sqlcmd><![CDATA[
			
				INSERT INTO pmsi.p_rss_ucd(
					finess
					,no_rss
					,rang
					,date_ucd
					,ucd_id
					,nombre
					,coefficient_fractionnement
					,prix_unitaire_achat
					,prix_unitaire_ecart_indemnisable
					,montant_ecart_indemnisable
					,montant_facture
					,rss_id
					,non_pris_en_compte
					,prix_unitaire_tr
					,montant_tr
					,montant_t2a_accorde
				)
				SELECT
					w_rss.finess as finess
					,w_rss.no_rss as no_rss
					,''::text as rang
					,DATUCD.UCDDATEADMIN as date_ucd
					,COALESCE(t_ucd.oid, 0) as ucd_id
					,DATUCD.UCDNBADMIN as nombre
					,0 as coefficient_fractionnement
					,DATUCD.UCDPRXACH as prix_unitaire_achat
					,0 as prix_unitaire_ecart_indemnisable
					,0 as montant_ecart_indemnisable
					,0 As montant_facture
					,w_rss.rss_id as rss_id
					,'0'::text as non_pris_en_compte
					,0::bigint as prix_unitaire_tr
					,0::numeric as montant_tr
					,0::numeric as montant_t2a_accorde
				FROM
					w_rss
					JOIN prod_sim.DATUCD ON DATUCD.UCDENTNUM = w_rss.no_sejour_administratif
					LEFT JOIN pmsi.t_ucd ON t_ucd.code = DATUCD.UCDCODE
				;
			]]></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
					,taux_remboursement
					,matricule_assure
					,cle_matricule_assure
					,forfait_facture
					,pat_benef_cmu
					,valorise_fides
					,no_facture_sejour_mere
					,regime_id
					,exoneration_tm_id
					,nature_pec_id
					,code_pec_id
					,type_contrat_oc_id
					,motif_non_facturation_id
					,code_pec_fj_id
					,facturation_18euros
					,facturation_18euros_id
					,code_gestion
					,no_organisme_complementaire
					,no_at_date_adc
					,nb_ghsmin
				)
				SELECT
					w_rss.finess as finess
					,w_rss.no_rss as no_rss
					,DATVIDHOSP.VHFACTURABLE as code_pec
					,DATVIDHOSP.VHRANGBENEF as rang_beneficiaire
					,DATVIDHOSP.VHCODEGR as regime
					,DATVIDHOSP.VHASSURANCE as nature_pec
					,DATVIDHOSP.VHCODEJUSTEXOTM as justificatif_exo
					,DATVIDHOSP.VHNUMSEJOUR as no_facture
					,'1'::character(1) as code_regularisation
					,0::NUMERIC as base_remboursement
					,0::NUMERIC as sejour_facture
					,0::NUMERIC as sejour_remboursable
					,0::NUMERIC as honoraires_factures
					,0::NUMERIC as honoraires_remboursable
					,0::NUMERIC as t2a_facture
					,0::NUMERIC as dmi_facture
					,0::NUMERIC as phc_facture
					,0::NUMERIC as participation_assure_avant_oc
					,0::NUMERIC as sejour_remboursable_2
					,0::NUMERIC as honoraires_remboursable_2
					,0::NUMERIC as montant_dmi_ghs2006
					,DATVALMCO.VALNBJSOINSINT as nb_si
					,DATVALMCO.VALSOINSINT as ca_si
					,DATVALMCO.VALNBJSURV as nb_sur
					,DATVALMCO.VALSURV as ca_sur
					,DATVALMCO.VALNBJREA as nb_rea
					,DATVALMCO.VALREA as ca_rea
					,DATVALMCO.VALNBJNN1 + DATVALMCO.VALNBJNN2 + DATVALMCO.VALNBJNN3 as nb_neonat
					,DATVALMCO.VALNN1 + DATVALMCO.VALNN2 + DATVALMCO.VALNN3 as ca_neonat
					,DATVALMCO.VALNBJXSUP as nb_exh
					,DATVALMCO.VALEHX as ca_exh
					,DATVALMCO.VALNBJXINF as nb_exb
					,DATVALMCO.VALJXINF as ca_exb
					,0::NUMERIC as nb_ghs
					,0::NUMERIC as ca_ghs
					,w_rss.rss_id as rss_id
					,0::NUMERIC as ca_ghs_theorique
					,DATVIDHOSP.VHTXREMB as taux_remboursement
					,DATVIDHOSP.VHNUMSECU as matricule_assure
					,DATVIDHOSP.VHCLEFNUMIMMAT as cle_matricule_assure
					,0::NUMERIC as forfait_facture
					,base.cti_to_number(DATVIDHOSP.VHBENEFCMU) as pat_benef_cmu
					,0::integer as valorise_fides
					,DATVIDHOSP.VHNUMSEJMERE as no_facture_sejour_mere
					,COALESCE(t_grands_regimes.oid, 0) as regime_id
					,COALESCE(t_exoneration_tm.oid, 0) as exoneration_tm_id
					,COALESCE(t_nature_assurance.oid, 0) as nature_pec_id
					,COALESCE(t_pec.oid, 0) as code_pec_id
					,0::bigint as type_contrat_oc_id
					,COALESCE(t_motif_non_facturation.oid, 0) as motif_non_facturation_id
					,COALESCE(t_pec_fj.oid, 0) as code_pec_fj_id
					,DATVIDHOSP.VHFACT18 as facturation_18euros
					,COALESCE(t_facturation_18euros.oid, 0) as facturation_18euros_id
					,DATVIDHOSP.VHCODEGESTION as code_gestion
					,DATVIDHOSP.VHORGCOMPL as no_organisme_complementaire
					,DATVIDHOSP.VHNUMDATEACCIDENT as no_at_date_adc
					,0::numeric as nb_ghsmin
				FROM
					w_rss
					JOIN prod_sim.DATENTREE ON DATENTREE.ENTNUM = w_rss.no_sejour_administratif
					JOIN prod_sim.DATPASSAGE ON DATPASSAGE.PASIDENT = DATENTREE.ENTID
					JOIN prod_sim.DATRESUMEMCO ON DATRESUMEMCO.RSSENTID = DATENTREE.ENTID
					JOIN prod_sim.DATVALMCO ON DATVALMCO.VALMCOIDPASS = DATPASSAGE.PASIDENT 
						AND DATPASSAGE.PASINDEX = DATRESUMEMCO.RSSNOCLASSANT
						AND DATVALMCO.VALMCOTYPE = 'RSS'
					LEFT JOIN prod_sim.DATVIDHOSP ON DATVIDHOSP.VHNUMSEJOUR = w_rss.no_sejour_administratif
					LEFT JOIN base.t_grands_regimes ON t_grands_regimes.code = DATVIDHOSP.VHCODEGR
					LEFT JOIN base.t_facturation_18euros ON t_facturation_18euros.code = DATVIDHOSP.VHFACT18
					LEFT JOIN base.t_pec ON t_pec.code = DATVIDHOSP.VHFACTURABLE
					LEFT JOIN base.t_nature_assurance ON t_nature_assurance.code = DATVIDHOSP.VHASSURANCE
					LEFT JOIN base.t_exoneration_tm ON t_exoneration_tm.code = DATVIDHOSP.VHCODEJUSTEXOTM
					LEFT JOIN base.t_pec_fj ON t_pec_fj.code = DATVIDHOSP.VHCODEPECFJ
					LEFT JOIN base.t_type_contrat_oc ON t_type_contrat_oc.code = DATVIDHOSP.VHTYPECONTRAT
					LEFT JOIN base.t_motif_non_facturation ON t_motif_non_facturation.code = DATVIDHOSP.VHMOTIFNFAM;		
			]]></sqlcmd>
		</NODE>
	</NODE>
	<NODE name="PMSIETAT" label="RECUPERATION ETAT DES DOSSIERS">
			
		<NODE label="Initialisation">
			<sqlcmd><![CDATA[
				SELECT pmsi.cti_initialize_rss_etat();
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Etat des dossiers">
			<sqlcmd><![CDATA[
				
			]]></sqlcmd>
		</NODE>
		
	</NODE>
	
</ROOT>
