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

				-- Récupération des spécialites internes
				DROP TABLE IF EXISTS w_specialite
				;
				CREATE TEMP TABLE w_specialite AS
					SELECT
						CLI_CODE_SPECIALITE as w_CLI_CODE_SPECIALITE,
						CLI_DONN_DESCRIPTION as w_CLI_DONN_DESCRIPTION,
						CASE WHEN length(CLI_CODE_SPECIALITE) = 2 THEN CLI_CODE_SPECIALITE || '..' ELSE CLI_CODE_SPECIALITE END as spe_cti
					FROM prod_clinicom.FB_MVTS_SPECIALITE
					WHERE CLI_FLAG_SPECIALITEMEDICALE > 0
						OR CLI_CODE_SPECIALITE = ANY (ARRAY(SELECT CLI_CODE_SPECIALITE FROM prod_clinicom.FB_MVTS_MEDECETB GROUP BY 1)::text[])
					ORDER BY CLI_CODE_SPECIALITE
				;
				
				INSERT INTO pmsi.t_specialites_internes_medecin(code, texte)
					SELECT 
						spe_cti,
						w_CLI_DONN_DESCRIPTION
					FROM w_specialite
						LEFT JOIN pmsi.t_specialites_internes_medecin ON t_specialites_internes_medecin.code = spe_cti
					WHERE t_specialites_internes_medecin.code IS NULL
				;
				
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
				SELECT 
					CLI_CODE_MEDECIN, 
					MAX(CLI_DONN_NOM),
					MAX(COALESCE(CLI_DONN_PRENOM,'')) AS CLI_DONN_PRENOM, 
					MAX(COALESCE(t_specialites_medecin.oid,0)),
					MAX(CASE WHEN COALESCE(CLI_CODE_ADELI,0) IS DISTINCT FROM 0 THEN to_char(CLI_CODE_ADELI,'FM000000000') ELSE '' END) AS CLI_CODE_ADELI, 
					MAX(COALESCE(t_specialites_internes_medecin.oid,0))
				FROM prod_clinicom.FB_MVTS_MEDECETB
					LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = FB_MVTS_MEDECETB.CLI_CODE_SPECIALITE
					LEFT JOIN w_specialite ON w_specialite.w_CLI_CODE_SPECIALITE = FB_MVTS_MEDECETB.CLI_CODE_SPECIALITE
					LEFT JOIN pmsi.t_specialites_internes_medecin ON  w_specialite.spe_cti = t_specialites_internes_medecin.code
					LEFT JOIN pmsi.t_medecins ON CLI_CODE_MEDECIN = t_medecins.code
				WHERE t_medecins.code IS NULL
				GROUP BY 1
				ORDER BY 1;
				
				UPDATE pmsi.t_medecins SET
					specialite_interne_id = COALESCE(t_specialites_internes_medecin.oid, 0)
				FROM
					prod_clinicom.FB_MVTS_MEDECETB
					LEFT JOIN w_specialite ON w_specialite.w_CLI_CODE_SPECIALITE = FB_MVTS_MEDECETB.CLI_CODE_SPECIALITE
					LEFT JOIN pmsi.t_specialites_internes_medecin ON  w_specialite.spe_cti = t_specialites_internes_medecin.code
				WHERE 1=1
					AND FB_MVTS_MEDECETB.CLI_CODE_MEDECIN = t_medecins.code
					AND t_medecins.specialite_interne_id != COALESCE(t_specialites_internes_medecin.oid, 0);
				
				DROP TABLE IF EXISTS w_um;
				CREATE TEMP TABLE w_um AS
				SELECT 
					CASE 
						WHEN char_length(CLI_CODE_UM) <= 4 THEN CLI_CODE_UM
						WHEN char_length(CLI_CODE_UM) = 5 THEN substr(CLI_CODE_UM, 2, 4)
					ELSE ''::text END as um_code
					,CLI_DONN_DESCRIPTION as um_texte
				FROM prod_clinicom.FS_MVTS_UM
				WHERE 1=1
					AND cli_code_suppression = 0
					AND char_length(CLI_CODE_UM) <= 5
				ORDER BY 1;
				
				INSERT INTO pmsi.t_unites_medicales(code, texte, type_autorisation)
				SELECT 
					um_code::text
					,um_texte::text
					,''::text
				FROM w_um
				WHERE um_code != ALL(ARRAY(SELECT code FROM pmsi.t_unites_medicales)::text[])
				ORDER BY 1;
	
											
										
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Identités et médecin">
			<sqlcmd><![CDATA[
				
				-- Traitement PMSI
				
				DROP TABLE IF EXISTS w_FD_PMSI_RUM;
				CREATE TEMP TABLE w_FD_PMSI_RUM AS
				SELECT FD_PMSI_RSS.CLI_Donn_RSSReference,
					(MAX(Array[CLI_Date_SortieUM::text,FD_PMSI_RUM.CLI_Code_MedecinResponsable]))[2] AS CLI_Code_MedecinResponsable
				FROM prod_clinicom.FD_PMSI_RSS
				JOIN prod_clinicom.FD_PMSI_RUM ON 
					FD_PMSI_RSS.CLI_Donn_NoRSS = FD_PMSI_RUM.CLI_Donn_NoRSS AND 
					FD_PMSI_RSS.CLI_Donn_NoAbsEpisode = FD_PMSI_RUM.CLI_Donn_NoAbsEpisode
				WHERE FD_PMSI_RUM.CLI_Code_MedecinResponsable <> '' AND
					FD_PMSI_RUM.CLI_Code_MedecinResponsable <> 'NR'
				GROUP BY 1;

				ALTER TABLE w_FD_PMSI_RUM ADD CONSTRAINT w_FD_PMSI_RUM_pkey PRIMARY KEY(CLI_Donn_RSSReference);

				DROP TABLE IF EXISTS w_MVTS_EPISODES_medecin_acte;
				CREATE TEMP TABLE w_MVTS_EPISODES_medecin_acte AS
				SELECT FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE, 
					(MAX(Array[FD_ACTES_ENTETE.CLI_DATE_SAISIE::text, FD_ACTES_ENTETE.CLI_CODE_MEDECIN1]))[2] AS CLI_CODE_MEDECIN
				FROM prod_clinicom.FD_ACTES_ENTETE
					JOIN prod_clinicom.FD_MVTS_EPISODES ON FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE = FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE AND 
						(CLI_CODE_MEDECINRESPONSABLE = '' OR CLI_CODE_MEDECINRESPONSABLE = 'NR')
				WHERE CLI_CODE_MEDECIN1 <> '' AND CLI_CODE_MEDECIN1 <> 'NR'
				GROUP BY 1;

				ALTER TABLE w_MVTS_EPISODES_medecin_acte ADD CONSTRAINT w_MVTS_EPISODES_medecin_acte_pkey PRIMARY KEY(CLI_DONN_NOABSEPISODE);


				DROP TABLE IF EXISTS w_MVTS_EPISODES_medecin;
				CREATE TEMP TABLE w_MVTS_EPISODES_medecin AS
				SELECT FD_MVTS_MOUVEMENTS.CLI_DONN_NOABSEPISODE, 
					(MAX(Array[CLI_DATE_MOUVEMENT::text, CLI_CODE_MEDECIN]))[2] AS CLI_CODE_MEDECIN
				FROM prod_clinicom.FD_MVTS_MOUVEMENTS
					JOIN prod_clinicom.FD_MVTS_EPISODES ON FD_MVTS_MOUVEMENTS.CLI_DONN_NOABSEPISODE = FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE AND 
						(CLI_CODE_MEDECINRESPONSABLE = '' OR CLI_CODE_MEDECINRESPONSABLE = 'NR')
				WHERE CLI_CODE_MEDECIN <> '' AND CLI_CODE_MEDECIN <> 'NR'
				GROUP BY 1;

				ALTER TABLE w_MVTS_EPISODES_medecin ADD CONSTRAINT w_MVTS_EPISODES_medecin_pkey PRIMARY KEY(CLI_DONN_NOABSEPISODE);
			

				DROP TABLE IF EXISTS w_FD_PMSI_RSS;
				CREATE TEMP TABLE w_FD_PMSI_RSS AS
				SELECT FD_PMSI_RSS.CLI_Donn_RSSReference,
					CLI_DONN_NOEXTEPISODE,
					date(CLI_Date_Entree) AS CLI_Date_Entree,
					date(CLI_Date_Sortie) AS CLI_Date_Sortie,
					CLI_Donn_NoIPPExterne,
					CLI_DONN_NOM, 
					CLI_DONN_NOMNAISSANCE,
					CLI_DONN_PRENOM, 
					COALESCE(
						w_FD_PMSI_RUM.CLI_Code_MedecinResponsable,
						CASE WHEN FD_MVTS_EPISODES.CLI_Code_MedecinResponsable <> '' AND FD_MVTS_EPISODES.CLI_Code_MedecinResponsable <> 'NR' THEN FD_MVTS_EPISODES.CLI_Code_MedecinResponsable ELSE NULL END,
						w_MVTS_EPISODES_medecin.CLI_CODE_MEDECIN,
						w_MVTS_EPISODES_medecin_acte.CLI_CODE_MEDECIN
						) AS CLI_Code_MedecinResponsable
				FROM prod_clinicom.FD_PMSI_RSS
				JOIN prod_clinicom.FD_MVTS_EPISODES ON FD_PMSI_RSS.CLI_Donn_NoAbsEpisode = FD_MVTS_EPISODES.CLI_Donn_NoAbsEpisode
				JOIN prod_clinicom.FD_MVTS_PATIENTS ON FD_MVTS_PATIENTS.CLI_Code_NoIPP = FD_MVTS_EPISODES.CLI_Code_NoIPP
				LEFT JOIN w_MVTS_EPISODES_medecin ON FD_PMSI_RSS.CLI_Donn_NoAbsEpisode = w_MVTS_EPISODES_medecin.CLI_Donn_NoAbsEpisode
				LEFT JOIN w_MVTS_EPISODES_medecin_acte ON FD_PMSI_RSS.CLI_Donn_NoAbsEpisode = w_MVTS_EPISODES_medecin_acte.CLI_Donn_NoAbsEpisode
				LEFT JOIN w_FD_PMSI_RUM ON w_FD_PMSI_RUM.CLI_Donn_RSSReference = FD_PMSI_RSS.CLI_Donn_RSSReference;


				CREATE INDEX w_FD_PMSI_RSS_i1
					ON w_FD_PMSI_RSS
					USING btree
					(CLI_Donn_RSSReference);

				CREATE INDEX w_FD_PMSI_RSS_i2
					ON w_FD_PMSI_RSS
					USING btree
					(CLI_Code_MedecinResponsable);
					
					
				UPDATE [SCHEMA].p_rss
				SET no_sejour_administratif = CLI_DONN_NOEXTEPISODE
				FROM w_FD_PMSI_RSS
				WHERE CLI_Donn_RSSReference = no_rss AND
					date_trunc('year',CLI_Date_Sortie) = date_trunc('year',date_sortie) AND
					trim(no_sejour_administratif) IS DISTINCT FROM CLI_DONN_NOEXTEPISODE AND
					en_cours LIKE '0' AND
					ghm_id <> 0
				;

				UPDATE [SCHEMA].p_identites
				SET nom = CLI_DONN_NOM,
					nom_naissance = CLI_DONN_NOMNAISSANCE,
					prenom = CLI_DONN_PRENOM
				FROM [SCHEMA].p_rss
				JOIN  w_FD_PMSI_RSS ON CLI_Donn_RSSReference = no_rss AND 
					(CLI_Date_Sortie = date_sortie OR CLI_Date_Entree = date_entree OR CLI_DONN_NOEXTEPISODE = substr(no_sejour_administratif,1,8) AND date_trunc('month',CLI_Date_Sortie) = date_trunc('month',date_sortie))
				WHERE p_identites.rss_id = p_rss.oid AND
					en_cours LIKE '0' AND
					(
						nom IS DISTINCT FROM CLI_DONN_NOM OR
						nom_naissance IS DISTINCT FROM CLI_DONN_NOMNAISSANCE OR
						prenom IS DISTINCT FROM CLI_DONN_PRENOM
					);


				UPDATE [SCHEMA].p_rss
				SET medecin_rss_id = COALESCE(t_medecins.oid,0),
					no_patient = to_number(CLI_Donn_NoIPPExterne,'000000000000')
				FROM w_FD_PMSI_RSS
				LEFT JOIN pmsi.t_medecins ON CLI_Code_MedecinResponsable = t_medecins.code
				WHERE CLI_Donn_RSSReference = no_rss AND 
					(CLI_Date_Sortie = date_sortie OR CLI_Date_Entree = date_entree OR CLI_DONN_NOEXTEPISODE = substr(no_sejour_administratif,1,8) AND date_trunc('month',CLI_Date_Sortie) = date_trunc('month',date_sortie)) AND
					en_cours LIKE '0' AND
					(
					medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
					no_patient IS DISTINCT FROM to_number(CLI_Donn_NoIPPExterne,'000000000000')
					)
				;
							
				-- Traitement Externes
				
				DROP TABLE IF EXISTS w_FD_MVTS_EPISODES;
				CREATE TEMP TABLE w_FD_MVTS_EPISODES AS
				SELECT 
					FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE,
					CLI_DONN_NOEXTEPISODE,
					date(CLI_Date_Entree) AS CLI_Date_Entree,
					date(CLI_Date_Sortie) AS CLI_Date_Sortie,
					CLI_Donn_NoIPPExterne,
					CLI_DONN_NOM, 
					CLI_DONN_NOMNAISSANCE,
					CLI_DONN_PRENOM, 
					COALESCE(
						CASE WHEN FD_MVTS_EPISODES.CLI_Code_MedecinResponsable <> '' THEN FD_MVTS_EPISODES.CLI_Code_MedecinResponsable ELSE NULL END,
						w_MVTS_EPISODES_medecin.CLI_CODE_MEDECIN,
						w_MVTS_EPISODES_medecin_acte.CLI_CODE_MEDECIN
						) AS CLI_Code_MedecinResponsable,
					FD_MVTS_SORTIES.CLI_CODE_UF,
					CASE WHEN CLI_CODE_UM <> '' THEN CLI_CODE_UM ELSE FD_MVTS_SORTIES.CLI_CODE_UF END AS CLI_CODE_UM
				FROM prod_clinicom.FD_MVTS_EPISODES 
				JOIN prod_clinicom.FD_MVTS_PATIENTS ON FD_MVTS_PATIENTS.CLI_Code_NoIPP = FD_MVTS_EPISODES.CLI_Code_NoIPP
				JOIN prod_clinicom.FD_MVTS_SORTIES ON FD_MVTS_SORTIES.CLI_DONN_NOABSEPISODE = FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE
				JOIN [SCHEMA].p_rss ON no_sejour_administratif = CLI_DONN_NOEXTEPISODE AND ghm_id = 0
				LEFT JOIN w_MVTS_EPISODES_medecin ON FD_MVTS_EPISODES.CLI_Donn_NoAbsEpisode = w_MVTS_EPISODES_medecin.CLI_Donn_NoAbsEpisode
				LEFT JOIN w_MVTS_EPISODES_medecin_acte ON FD_MVTS_EPISODES.CLI_Donn_NoAbsEpisode = w_MVTS_EPISODES_medecin_acte.CLI_Donn_NoAbsEpisode
				LEFT JOIN prod_clinicom.FS_MVTS_UF ON FD_MVTS_SORTIES.CLI_CODE_UF = FS_MVTS_UF.CLI_CODE_UF;




				CREATE INDEX w_FD_MVTS_EPISODES_i1
					ON w_FD_MVTS_EPISODES
					USING btree
					(CLI_DONN_NOEXTEPISODE);
					
				CREATE INDEX w_FD_MVTS_EPISODES_i2
					ON w_FD_MVTS_EPISODES
					USING btree
					(CLI_DONN_NOABSEPISODE);

				CREATE INDEX w_FD_MVTS_EPISODES_i3
					ON w_FD_MVTS_EPISODES
					USING btree
					(CLI_Code_MedecinResponsable);

				CREATE INDEX w_FD_MVTS_EPISODES_i4
					ON w_FD_MVTS_EPISODES
					USING btree
					(CLI_CODE_UM);
					
				UPDATE w_FD_MVTS_EPISODES SET CLI_Code_MedecinResponsable = CLI_CODE_MEDECIN1
				FROM prod_clinicom.FD_ACTES_ENTETE
				WHERE CLI_CODE_MEDECINRESPONSABLE = '' AND
					w_FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE = FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE;


				UPDATE [SCHEMA].p_identites
				SET nom = CLI_DONN_NOM,
					nom_naissance = CLI_DONN_NOMNAISSANCE,
					prenom = CLI_DONN_PRENOM
				FROM [SCHEMA].p_rss
				JOIN  w_FD_MVTS_EPISODES ON CLI_DONN_NOEXTEPISODE = no_sejour_administratif AND CLI_Date_Sortie = date_sortie 
				WHERE ghm_id = 0 AND 
					en_cours LIKE '0' AND
					p_identites.rss_id = p_rss.oid AND
					(
						nom IS DISTINCT FROM CLI_DONN_NOM OR
						nom_naissance IS DISTINCT FROM CLI_DONN_NOMNAISSANCE OR
						prenom IS DISTINCT FROM CLI_DONN_PRENOM
					);


				UPDATE [SCHEMA].p_rss
				SET medecin_rss_id = COALESCE(t_medecins.oid,0),
					no_patient = to_number(CLI_Donn_NoIPPExterne,'000000000000')
				FROM w_FD_MVTS_EPISODES
				LEFT JOIN pmsi.t_medecins ON CLI_Code_MedecinResponsable = t_medecins.code
				WHERE ghm_id = 0 AND 
					CLI_DONN_NOEXTEPISODE = no_sejour_administratif AND 
					en_cours LIKE '0' AND
					CLI_Date_Sortie = date_sortie AND
					(
					medecin_rss_id IS DISTINCT FROM COALESCE(t_medecins.oid,0) OR
					no_patient IS DISTINCT FROM to_number(CLI_Donn_NoIPPExterne,'000000000000')
					)
				;



				INSERT INTO pmsi.t_unites_medicales(code, texte, type_autorisation)
				SELECT 
					w_FD_MVTS_EPISODES.CLI_CODE_UM, 
					CLI_DONN_DESCRIPTION,
					''
				FROM w_FD_MVTS_EPISODES
					JOIN prod_clinicom.FS_MVTS_UF ON w_FD_MVTS_EPISODES.CLI_CODE_UF = FS_MVTS_UF.CLI_CODE_UF 
				WHERE w_FD_MVTS_EPISODES.CLI_CODE_UF = w_FD_MVTS_EPISODES.CLI_CODE_UM AND
					w_FD_MVTS_EPISODES.CLI_CODE_UM NOT IN (SELECT code FROM pmsi.t_unites_medicales)
				GROUP BY 1,2
				ORDER BY 1;



				UPDATE [SCHEMA].p_rss_rum
				SET unite_medicale_id = t_unites_medicales.oid
				FROM [SCHEMA].p_rss
				JOIN w_FD_MVTS_EPISODES ON CLI_DONN_NOEXTEPISODE = no_sejour_administratif AND CLI_Date_Sortie = date_sortie 
				JOIN pmsi.t_unites_medicales ON CLI_CODE_UM = t_unites_medicales.code
				WHERE ghm_id = 0 AND 
					en_cours LIKE '0' AND
					p_rss_rum.rss_id = p_rss.oid AND
					(
						unite_medicale_id IS DISTINCT FROM t_unites_medicales.oid
					);


				UPDATE [SCHEMA].p_rss_actes
				SET unite_medicale_id = t_unites_medicales.oid
				FROM [SCHEMA].p_rss
				JOIN w_FD_MVTS_EPISODES ON CLI_DONN_NOEXTEPISODE = no_sejour_administratif AND CLI_Date_Sortie = date_sortie 
				JOIN pmsi.t_unites_medicales ON CLI_CODE_UM = t_unites_medicales.code
				WHERE ghm_id = 0 AND 
					en_cours LIKE '0' AND
					p_rss_actes.rss_id = p_rss.oid AND
					(
						unite_medicale_id IS DISTINCT FROM t_unites_medicales.oid
					);
				
										
			]]></sqlcmd>
		</NODE>
				
		
		<NODE label="Médecins actes">
			<sqlcmd><![CDATA[

				-- PMSI
				
				DROP TABLE IF EXISTS w_FD_ACTES_CCAM;
				CREATE TEMP TABLE w_FD_ACTES_CCAM AS
				SELECT 
					p_rss.oid AS rss_id,
					t_actes.oid AS acte_id,
					date(CLI_Date_saisie) AS CLI_Date_saisie,
					CLI_Code_Phase,
					CLI_Code_Activite,
					t_medecins.oid AS medecin_id
				FROM prod_clinicom.FD_PMSI_RSS
				JOIN [SCHEMA].p_rss ON CLI_Donn_RSSReference = no_rss
				JOIN prod_clinicom.FD_ACTES_ENTETE ON FD_PMSI_RSS.CLI_Donn_NoAbsEpisode = FD_ACTES_ENTETE.CLI_Donn_NoAbsEpisode
				JOIN prod_clinicom.FD_ACTES_CCAM ON FD_ACTES_ENTETE.CLI_Donn_NoAbsEpisode = FD_ACTES_CCAM.CLI_Donn_NoAbsEpisode AND FD_ACTES_CCAM.CLI_Donn_Lien = FD_ACTES_ENTETE.CLI_Donn_Lien
				JOIN pmsi.t_medecins ON CLI_Code_MedecinRealisateur = t_medecins.code
				JOIN pmsi.t_actes ON substring(CLI_Code_CCAM, 1, 7) = t_actes.code;


				CREATE INDEX w_FD_ACTES_CCAM_i1
							  ON w_FD_ACTES_CCAM
							  USING btree
							  (rss_id);
							  
				UPDATE [SCHEMA].p_rss_actes
				SET medecin_id = w_FD_ACTES_CCAM.medecin_id
				FROM w_FD_ACTES_CCAM 
				WHERE w_FD_ACTES_CCAM.rss_id = p_rss_actes.rss_id AND
					w_FD_ACTES_CCAM.CLI_Date_saisie = p_rss_actes.date_acte AND
					w_FD_ACTES_CCAM.acte_id = p_rss_actes.acte_id AND
					w_FD_ACTES_CCAM.CLI_Code_Activite = p_rss_actes.activite_ccam AND
					w_FD_ACTES_CCAM.medecin_id IS DISTINCT FROM p_rss_actes.medecin_id;

				-- Externes		
				
				DROP TABLE IF EXISTS w_FD_ACTES_CCAM;
				CREATE TEMP TABLE w_FD_ACTES_CCAM AS
				SELECT 
					p_rss.oid AS rss_id,
					t_actes.oid AS acte_id,
					date(CLI_Date_saisie) AS CLI_Date_saisie,
					CLI_Code_Phase,
					CLI_Code_Activite,
					t_medecins.oid AS medecin_id
				FROM prod_clinicom.FD_MVTS_EPISODES 
				JOIN [SCHEMA].p_rss ON no_sejour_administratif = CLI_DONN_NOEXTEPISODE AND ghm_id = 0
				JOIN prod_clinicom.FD_ACTES_ENTETE ON FD_MVTS_EPISODES.CLI_Donn_NoAbsEpisode = FD_ACTES_ENTETE.CLI_Donn_NoAbsEpisode
				JOIN prod_clinicom.FD_ACTES_CCAM ON FD_ACTES_ENTETE.CLI_Donn_NoAbsEpisode = FD_ACTES_CCAM.CLI_Donn_NoAbsEpisode AND FD_ACTES_CCAM.CLI_Donn_Lien = FD_ACTES_ENTETE.CLI_Donn_Lien
				JOIN pmsi.t_medecins ON CLI_Code_MedecinRealisateur = t_medecins.code
				JOIN pmsi.t_actes ON substring(CLI_Code_CCAM, 1, 7) = t_actes.code;


				CREATE INDEX w_FD_ACTES_CCAM_i1
							  ON w_FD_ACTES_CCAM
							  USING btree
							  (rss_id);
							  
				UPDATE [SCHEMA].p_rss_actes
				SET medecin_id = w_FD_ACTES_CCAM.medecin_id
				FROM w_FD_ACTES_CCAM 
				WHERE w_FD_ACTES_CCAM.rss_id = p_rss_actes.rss_id AND
					w_FD_ACTES_CCAM.CLI_Date_saisie = p_rss_actes.date_acte AND
					w_FD_ACTES_CCAM.acte_id = p_rss_actes.acte_id AND
					w_FD_ACTES_CCAM.CLI_Code_Activite = p_rss_actes.activite_ccam AND
					w_FD_ACTES_CCAM.medecin_id IS DISTINCT FROM p_rss_actes.medecin_id;

										
			]]></sqlcmd>
		</NODE>
	
	</NODE>
	
	<NODE name="PMSIENCOURS" label="RECUPERATION DES DOSSIERS PMSI EN-COURS CLINICOM">
		
		<NODE label="Médecins">
			<sqlcmd><![CDATA[
				
				INSERT INTO pmsi.t_medecins(code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
				SELECT 
					CLI_CODE_MEDECIN, 
					MAX(CLI_DONN_NOM),
					MAX(COALESCE(CLI_DONN_PRENOM,'')) AS CLI_DONN_PRENOM, 
					MAX(COALESCE(t_specialites_medecin.oid,0)),
					MAX(CASE WHEN COALESCE(CLI_CODE_ADELI,0) IS DISTINCT FROM 0 THEN to_char(CLI_CODE_ADELI,'FM000000000') ELSE '' END) AS CLI_CODE_ADELI, 
					MAX(COALESCE(t_specialites_internes_medecin.oid,0))
				FROM prod_clinicom.FB_MVTS_MEDECETB
					LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = CLI_CODE_SPECIALITE
					LEFT JOIN pmsi.t_specialites_internes_medecin ON (t_specialites_internes_medecin.code = CLI_CODE_SPECIALITE || '..')
					LEFT JOIN pmsi.t_medecins ON CLI_CODE_MEDECIN = t_medecins.code
				WHERE t_medecins.code IS NULL
				GROUP BY 1
				ORDER BY 1;



				INSERT INTO pmsi.t_unites_medicales(code, texte, type_autorisation)
				SELECT 
					substr(CLI_CODE_UM,1,4), 
					CLI_DONN_DESCRIPTION,
					''
				FROM prod_clinicom.FS_MVTS_UM
				WHERE substr(CLI_CODE_UM,1,4) NOT IN (SELECT code FROM pmsi.t_unites_medicales)
				ORDER BY 1;
										
			]]></sqlcmd>
		</NODE>
		
		<NODE label="RSS">
			<sqlcmd><![CDATA[
			
				
					

				DROP TABLE IF EXISTS w_finess;
				CREATE TEMP TABLE w_finess AS
				SELECT (max(array[date_sortie::text,finess]))[2] AS finess
				FROM pmsi.p_rss;
				
				WITH base_finess AS (
					SELECT max(code) as w_code from base.t_finess WHERE secondaire != '1'
				)
				UPDATE w_finess SET
					finess = w_code
				FROM base_finess
				WHERE w_finess.finess IS NULL;
				
				DROP TABLE IF EXISTS w_FD_PMSI_RUM;
				CREATE TEMP TABLE w_FD_PMSI_RUM AS
				SELECT
					FD_PMSI_RUM.CLI_DONN_NOABSEPISODE,
					FD_PMSI_RUM.CLI_DONN_NORSS,
					FD_PMSI_RUM.CLI_DONN_NORUM,
					FD_PMSI_RUM.CLI_DONN_LIEN,
					CLI_DONN_NBSEANCES,
					CLI_DATE_ENTREEUM,
					CLI_DATE_SORTIEUM,
					substr(CLI_CODE_UM,1,4) AS CLI_CODE_UM,
					CLI_CODE_MODEENTREEPMSI,
					CASE WHEN CLI_CODE_PROVENANCEPMSI <> '0' THEN CLI_CODE_PROVENANCEPMSI ELSE '' END AS CLI_CODE_PROVENANCEPMSI,
					CLI_CODE_MODESORTIEPMSI,
					CASE WHEN CLI_CODE_DESTINATIONPMSI <> '0' THEN CLI_CODE_DESTINATIONPMSI ELSE '' END AS CLI_CODE_DESTINATIONPMSI,
					CLI_CODE_MEDECINRESPONSABLE,
					CLI_DONN_IGS2,
					MAX(CASE WHEN CLI_CODE_TYPEDIAGNOSTIC = '1' THEN CLI_CODE_DIAG ELSE '' END) AS CLI_CODE_DIAG_DP,
					MAX(CASE WHEN CLI_CODE_TYPEDIAGNOSTIC = '2' THEN CLI_CODE_DIAG ELSE '' END) AS CLI_CODE_DIAG_DR
				FROM
					prod_clinicom.FD_PMSI_RUM
					LEFT JOIN prod_clinicom.FD_PMSI_DIAG ON FD_PMSI_RUM.CLI_DONN_NOABSEPISODE = FD_PMSI_DIAG.CLI_DONN_NOABSEPISODE AND
										FD_PMSI_RUM.CLI_DONN_LIEN = FD_PMSI_DIAG.CLI_DONN_LIEN
				WHERE CLI_DATE_SORTIEUM >= '[ANNEE]0101'
				GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;

				CREATE INDEX w_FD_PMSI_RUM_i1 ON w_FD_PMSI_RUM USING btree (CLI_DONN_NOABSEPISODE);



				DROP TABLE IF EXISTS w_FD_PMSI_RSS;
				CREATE TEMP TABLE w_FD_PMSI_RSS AS
				SELECT 
					'HOS'::text AS rss_type,
					w_finess.finess,
					0::bigint AS rss_id,
					FD_PMSI_RSS.CLI_DONN_NOABSEPISODE,
					FD_PMSI_RSS.CLI_DONN_NORSS,
					MAX(CLI_DONN_NOIPPEXTERNE) AS CLI_DONN_NOIPPEXTERNE,
					MAX(CLI_DONN_NOM) AS CLI_DONN_NOM, 
					MAX(CLI_DONN_NOMNAISSANCE) AS CLI_DONN_NOMNAISSANCE,
					MAX(CLI_DONN_PRENOM) AS CLI_DONN_PRENOM, 
					MAX(CLI_DATE_NAISSANCE) AS CLI_DATE_NAISSANCE,
					MAX(CLI_CODE_POSTAL) AS CLI_CODE_POSTAL,
					MAX(CASE WHEN FB_MVTS_SEXE.CLI_DONN_DESCRIPTIONCOURTE ILIKE 'F%' THEN '2' ELSE '1' END) AS CLI_CODE_SEXE,
					MAX(CASE WHEN FD_PMSI_RSS.CLI_DONN_RSSREFERENCE <> 0 THEN FD_PMSI_RSS.CLI_DONN_RSSREFERENCE ELSE 99000000000 + to_number(FD_PMSI_RSS.CLI_DONN_NOABSEPISODE,'0000000') * 100 + FD_PMSI_RSS.CLI_DONN_NORSS  END) AS CLI_DONN_RSSREFERENCE,
					MAX(CLI_DONN_NOEXTEPISODE) AS CLI_DONN_NOEXTEPISODE,
					MAX(FD_PMSI_RSS.CLI_DATE_ENTREE) AS CLI_DATE_ENTREE,
					MAX(FD_PMSI_RSS.CLI_DATE_SORTIE) AS CLI_DATE_SORTIE,
					MAX(FD_PMSI_RSS.CLI_CODE_GHM) AS CLI_CODE_GHM,
					MAX(FD_PMSI_RSS.CLI_CODE_GHS) AS CLI_CODE_GHS,
					MAX(CLI_FLAG_CODAGERSS) AS CLI_FLAG_CODAGERSS,
					count(*) AS nb_rum,
					SUM(CLI_DONN_NBSEANCES) AS RSS_CLI_DONN_NBSEANCES,
					(min(array[CLI_DATE_ENTREEUM::text,CLI_CODE_MODEENTREEPMSI::text]))[2] AS RSS_CLI_CODE_MODEENTREEPMSI,
					(min(array[CLI_DATE_ENTREEUM::text,CLI_CODE_PROVENANCEPMSI::text]))[2] AS RSS_CLI_CODE_PROVENANCEPMSI,
					(max(array[CLI_DATE_SORTIEUM::text,CLI_CODE_MODESORTIEPMSI::text]))[2] AS RSS_CLI_CODE_MODESORTIEPMSI,
					(max(array[CLI_DATE_SORTIEUM::text,CLI_CODE_DESTINATIONPMSI::text]))[2] AS RSS_CLI_CODE_DESTINATIONPMSI,
					(max(array[CLI_DATE_SORTIEUM::text,w_FD_PMSI_RUM.CLI_CODE_MEDECINRESPONSABLE::text]))[2] AS RSS_CLI_CODE_MEDECINRESPONSABLE,
					''::text AS CLI_CODE_UM
				FROM w_finess,
					prod_clinicom.FD_PMSI_RSS
					JOIN w_FD_PMSI_RUM ON FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE AND
									FD_PMSI_RSS.CLI_DONN_NORSS = w_FD_PMSI_RUM.CLI_DONN_NORSS
					JOIN prod_clinicom.FD_MVTS_EPISODES ON FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE
					JOIN prod_clinicom.FD_MVTS_PATIENTS ON FD_MVTS_EPISODES.CLI_CODE_NOIPP = FD_MVTS_PATIENTS.CLI_CODE_NOIPP
					JOIN prod_clinicom.FB_MVTS_SEXE ON FD_MVTS_PATIENTS.CLI_CODE_SEXE = FB_MVTS_SEXE.CLI_CODE_SEXE
					LEFT JOIN pmsi.p_rss ON CLI_DONN_RSSREFERENCE = no_rss AND date_trunc('month',CLI_DATE_SORTIE) = date_trunc('month',date_sortie)
				WHERE CLI_DATE_SORTIE >= '[ANNEE]0101' AND p_rss.oid IS NULL
				GROUP BY 2,4,5;


				INSERT INTO w_FD_PMSI_RSS
				SELECT 
					'EXT'::text AS rss_type,
					w_finess.finess,
					0::bigint AS rss_id,
					FD_ACTES_NGAP.CLI_DONN_NOABSEPISODE,
					0 AS CLI_DONN_NORSS,
					MAX(CLI_DONN_NOIPPEXTERNE) AS CLI_DONN_NOIPPEXTERNE,
					MAX(CLI_DONN_NOM) AS CLI_DONN_NOM, 
					MAX(CLI_DONN_NOMNAISSANCE) AS CLI_DONN_NOMNAISSANCE,
					MAX(CLI_DONN_PRENOM) AS CLI_DONN_PRENOM, 
					MAX(CLI_DATE_NAISSANCE) AS CLI_DATE_NAISSANCE,
					MAX(CLI_CODE_POSTAL) AS CLI_CODE_POSTAL,
					MAX(FD_MVTS_PATIENTS.CLI_CODE_SEXE) AS CLI_CODE_SEXE,
					MAX(99000000000 + to_number(FD_ACTES_NGAP.CLI_DONN_NOABSEPISODE,'0000000') * 100) AS CLI_DONN_RSSREFERENCE,
					MAX(CLI_DONN_NOEXTEPISODE) AS CLI_DONN_NOEXTEPISODE,
					MAX(FD_MVTS_SORTIES.CLI_DATE_ENTREE) AS CLI_DATE_ENTREE,
					MAX(FD_MVTS_SORTIES.CLI_DATE_SORTIE) AS CLI_DATE_SORTIE,
					''::text AS CLI_CODE_GHM,
					0::numeric AS CLI_CODE_GHS,
					0::numeric AS CLI_FLAG_CODAGERSS,
					1::numeric AS nb_rum,
					0::numeric AS RSS_CLI_DONN_NBSEANCES,
					'8' AS RSS_CLI_CODE_MODEENTREEPMSI,
					'' AS RSS_CLI_CODE_PROVENANCEPMSI,
					'8'  AS RSS_CLI_CODE_MODESORTIEPMSI,
					'' AS RSS_CLI_CODE_DESTINATIONPMSI,
					MAX(CASE WHEN CLI_CODE_MEDECINRESPONSABLE <> '' THEN CLI_CODE_MEDECINRESPONSABLE ELSE FD_MVTS_SORTIES.CLI_CODE_MEDECIN END) AS RSS_CLI_CODE_MEDECINRESPONSABLE,
					MAX(CASE WHEN CLI_CODE_UM <> '' THEN CLI_CODE_UM ELSE FD_MVTS_SORTIES.CLI_CODE_UF END) AS CLI_CODE_UM
				FROM w_finess,
					prod_clinicom.FD_ACTES_NGAP
					JOIN prod_clinicom.FD_MVTS_EPISODES ON FD_ACTES_NGAP.CLI_DONN_NOABSEPISODE = FD_MVTS_EPISODES.CLI_DONN_NOABSEPISODE
					JOIN prod_clinicom.FD_MVTS_SORTIES ON FD_ACTES_NGAP.CLI_DONN_NOABSEPISODE = FD_MVTS_SORTIES.CLI_DONN_NOABSEPISODE
					JOIN prod_clinicom.FD_MVTS_PATIENTS ON FD_MVTS_EPISODES.CLI_CODE_NOIPP = FD_MVTS_PATIENTS.CLI_CODE_NOIPP
					JOIN prod_clinicom.FS_MVTS_UF ON FD_MVTS_SORTIES.CLI_CODE_UF = FS_MVTS_UF.CLI_CODE_UF
					LEFT JOIN pmsi.p_rss ON CLI_DONN_NOEXTEPISODE = no_sejour_administratif AND 
						p_rss.ghm_id = 0 AND date(CLI_DATE_SORTIE) = date(date_sortie) AND
						en_cours LIKE '0'
				WHERE FD_MVTS_SORTIES.CLI_DATE_SORTIE >= '[ANNEE]0101' AND date(FD_MVTS_SORTIES.CLI_DATE_SORTIE) = date(FD_MVTS_SORTIES.CLI_DATE_ENTREE) AND p_rss.oid IS NULL 
				GROUP BY 2,4,5;


				UPDATE w_FD_PMSI_RSS SET rss_id = nextval('pmsi.s_rss'::regclass);


				CREATE INDEX w_FD_PMSI_RSS_i1 ON w_FD_PMSI_RSS USING btree (CLI_DONN_NOABSEPISODE);


				INSERT INTO pmsi.t_unites_medicales(code, texte, type_autorisation)
				SELECT 
					w_FD_PMSI_RSS.CLI_CODE_UM, 
					CLI_DONN_DESCRIPTION,
					''
				FROM w_FD_PMSI_RSS
					JOIN prod_clinicom.FS_MVTS_UF ON w_FD_PMSI_RSS.CLI_CODE_UM = FS_MVTS_UF.CLI_CODE_UF 
				WHERE rss_type = 'EXT' AND
					w_FD_PMSI_RSS.CLI_CODE_UM NOT IN (SELECT code FROM pmsi.t_unites_medicales)
				GROUP BY 1,2
				ORDER BY 1;




				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 AS oid, 
					CLI_DONN_RSSREFERENCE AS no_rss, 
					to_number(CLI_DONN_NOIPPEXTERNE, '000000000000000')::bigint AS no_patient, 
					'' AS version_groupage, 
					date(CLI_DATE_NAISSANCE) AS date_naissance, 
					CLI_CODE_SEXE AS sexe, 
					date(CLI_DATE_ENTREE) AS date_entree, 
					date(CLI_DATE_SORTIE) AS date_sortie, 
					COALESCE(RSS_CLI_CODE_MODEENTREEPMSI,'8') AS mode_entree, 
					COALESCE(RSS_CLI_CODE_PROVENANCEPMSI,'') AS provenance, 
					COALESCE(RSS_CLI_CODE_MODESORTIEPMSI,'8') AS mode_sortie, 
					COALESCE(RSS_CLI_CODE_DESTINATIONPMSI,'') AS destination, 
					SUBSTR(CLI_CODE_POSTAL,1,5) AS code_postal, 
					0 AS poids_bebe, 
					0 AS igs2, 
					'' AS cma, 
					substr(CLI_CODE_GHM , 3 , 1) AS groupe_ghm, 
					CASE WHEN CLI_DATE_SORTIE > CLI_DATE_ENTREE THEN date(CLI_DATE_SORTIE) - date(CLI_DATE_ENTREE) ELSE 0 END AS duree_sejour, 
					CASE WHEN substr(CLI_CODE_GHM,1,2) = '28' THEN 1 ELSE 1 END AS nb_rsa, 
					CASE WHEN CLI_DATE_ENTREE > CLI_DATE_NAISSANCE THEN trunc((date(CLI_DATE_ENTREE) - date(CLI_DATE_NAISSANCE)) / 365.25,0) ELSE 0 END AS age, 
					'' AS supprime, 
					now() AS date_import, 
					nb_rum, 
					'' AS secteur, 
					CLI_DONN_NOEXTEPISODE AS no_sejour_administratif, 
					CASE WHEN substr(CLI_CODE_GHM,1,2) = '28' THEN RSS_CLI_DONN_NBSEANCES 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', CLI_DATE_SORTIE) * 100 + date_part('month', CLI_DATE_SORTIE) AS mois_sortie, 
					0::bigint AS diagnostic_principal_id, 
					0::bigint AS diagnostic_relie_id, 
					COALESCE(t_ghm.oid, 0) AS ghm_production_id, 
					CASE WHEN nb_rum > 1 THEN 0::bigint ELSE 1 END AS no_rum_principal, 
					0::bigint 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(CLI_CODE_GHM) = 6 AND substr(CLI_CODE_GHM,1,2) <> '90' AND '1' <> '1' THEN 'V' 
						WHEN length(CLI_CODE_GHM) = 6 AND substr(CLI_CODE_GHM,1,2) <> '90' THEN 'G' 
						WHEN rss_type = 'EXT' 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_FD_PMSI_RSS
					LEFT JOIN pmsi.t_ghm ON (CLI_CODE_GHM = t_ghm.code)
					LEFT JOIN pmsi.t_ghs ON (CLI_CODE_GHS = t_ghs.code)
					LEFT JOIN pmsi.t_medecins ON RSS_CLI_CODE_MEDECINRESPONSABLE = t_medecins.code;
										
										
			]]></sqlcmd>
		</NODE>
				
		<NODE label="Identités">
			<sqlcmd><![CDATA[

				INSERT INTO pmsi.p_identites(
					finess, 
					rss_id,
					no_rss, 
					nom, 
					prenom, 
					nom_naissance)
				SELECT 
					finess, 
					rss_id, 
					CLI_DONN_RSSREFERENCE AS no_rss, 
					CLI_DONN_NOM AS nom, 
					CLI_DONN_PRENOM AS prenom, 
					CASE WHEN CLI_DONN_NOMNAISSANCE <> CLI_DONN_NOM THEN CLI_DONN_NOMNAISSANCE ELSE '' END AS nom_naissance
				FROM w_FD_PMSI_RSS;

				UPDATE pmsi.p_rss
				SET no_patient = to_number(CLI_Donn_NoIPPExterne,'000000000000')
				FROM w_FD_PMSI_RSS
				WHERE CLI_Donn_RSSReference = no_rss AND 
					(CLI_Date_Sortie = date_sortie OR CLI_Date_Entree = date_entree OR CLI_DONN_NOEXTEPISODE = substr(no_sejour_administratif,1,8) AND date_trunc('month',CLI_Date_Sortie) = date_trunc('month',date_sortie)) AND
					en_cours LIKE '0' AND
					(
					no_patient IS DISTINCT FROM to_number(CLI_Donn_NoIPPExterne,'000000000000')
					)
				;

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

					
				INSERT INTO w_FD_PMSI_RUM
				SELECT
					w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE,
					w_FD_PMSI_RSS.CLI_DONN_NORSS,
					1::numeric AS CLI_DONN_NORUM,
					0::numeric AS CLI_DONN_LIEN,
					0::numeric AS CLI_DONN_NBSEANCES,
					CLI_DATE_ENTREE,
					CLI_DATE_SORTIE,
					CLI_CODE_UM,
					RSS_CLI_CODE_MODEENTREEPMSI,
					RSS_CLI_CODE_PROVENANCEPMSI,
					RSS_CLI_CODE_MODESORTIEPMSI,
					RSS_CLI_CODE_DESTINATIONPMSI,
					RSS_CLI_CODE_MEDECINRESPONSABLE,
					0::numeric AS CLI_DONN_IGS2,
					''::text AS CLI_CODE_DIAG_DP,
					''::text AS CLI_CODE_DIAG_DR
				FROM w_FD_PMSI_RSS
				WHERE rss_type = 'EXT';


				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
					finess, 
					rss_id, 
					CLI_DONN_RSSREFERENCE AS no_rss, 
					CLI_DONN_NORUM AS no_rum, 
					date(CLI_DATE_ENTREEUM) AS date_entree, 
					date(CLI_DATE_SORTIEUM) AS date_sortie, 
					CLI_CODE_MODEENTREEPMSI AS mode_entree, 
					CLI_CODE_PROVENANCEPMSI AS provenance, 
					CLI_CODE_MODESORTIEPMSI AS mode_sortie, 
					CLI_CODE_DESTINATIONPMSI AS destination, 
					CASE WHEN CLI_DATE_SORTIEUM > CLI_DATE_ENTREEUM THEN date(CLI_DATE_SORTIEUM) - date(CLI_DATE_ENTREEUM) ELSE 0 END AS duree_sejour, 
					CASE WHEN substr(CLI_CODE_GHM,1,2) = '28' THEN CLI_DONN_NBSEANCES ELSE 0 END AS nb_seances, 
					''::text AS unite_medicale, 
					COALESCE(t_diagnostics_dp.oid, 0) AS diagnostic_principal_id, 
					COALESCE(t_diagnostics_dr.oid, 0) AS diagnostic_relie_id, 
					COALESCE(t_unites_medicales.oid,0) AS unite_medicale_id, 
					'' AS type_autorisation, 
					CLI_DONN_IGS2 AS igs2, 
					'' AS type_autorisation_lit_dedie, 
					'' AS supplement_code
				FROM w_FD_PMSI_RSS
					JOIN w_FD_PMSI_RUM ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = w_FD_PMSI_RUM.CLI_DONN_NORSS
					LEFT JOIN pmsi.t_unites_medicales ON (w_FD_PMSI_RUM.CLI_CODE_UM = t_unites_medicales.code)
					LEFT JOIN pmsi.t_diagnostics t_diagnostics_dp ON CLI_CODE_DIAG_DP = t_diagnostics_dp.code
					LEFT JOIN pmsi.t_diagnostics t_diagnostics_dr ON CLI_CODE_DIAG_DR = t_diagnostics_dr.code;
										
										
			]]></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_FD_PMSI_RSS.finess, 
					w_FD_PMSI_RSS.rss_id, 
					CLI_DONN_RSSREFERENCE AS no_rss, 
					CLI_DONN_NORUM AS no_rum, 
					COALESCE(t_diagnostics.oid,0) AS diagnostic_id, 
					CASE 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '1' THEN 'DP' 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '2' THEN 'DR' 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '3' THEN 'DD' 
						ELSE 'DA' END
						AS type_diagnostic, 
					CASE 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '1' THEN 'DP' 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '2' THEN 'DR' 
						WHEN CLI_CODE_TYPEDIAGNOSTIC = '3' THEN 'DD' 
						ELSE 'DA' END
						AS type_diagnostic_rss, 
					'' AS imprecis, 
					unite_medicale, 
					unite_medicale_id
				FROM w_FD_PMSI_RSS
					JOIN w_FD_PMSI_RUM ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = w_FD_PMSI_RUM.CLI_DONN_NORSS
					JOIN prod_clinicom.FD_PMSI_DIAG ON w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE = FD_PMSI_DIAG.CLI_DONN_NOABSEPISODE AND
										w_FD_PMSI_RUM.CLI_DONN_LIEN = FD_PMSI_DIAG.CLI_DONN_LIEN
					JOIN pmsi.t_diagnostics ON (CLI_CODE_DIAG = t_diagnostics.code)
					JOIN pmsi.p_rss_rum ON (w_FD_PMSI_RSS.rss_id = p_rss_rum.rss_id AND CLI_DONN_NORUM = p_rss_rum.no_rum)
				WHERE CLI_CODE_TYPEDIAGNOSTIC IN ('0', '1', '2', '3') AND
					rss_type = 'HOS';
										
										
			]]></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, 
					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_FD_PMSI_RSS.finess, 
					w_FD_PMSI_RSS.rss_id, 
					CLI_DONN_RSSREFERENCE AS no_rss, 
					CLI_DONN_NORUM AS no_rum, 
					p_rss_rum.unite_medicale AS unite_medicale, 
					p_rss_rum.unite_medicale_id AS unite_medicale_id, 
					date(CLI_DATE_SAISIE) AS date_acte, 
					COALESCE(t_medecins.oid,0) AS medecin_id,
					COALESCE(t_actes.oid,0) AS acte_id, 
					1::numeric AS nombre, 
					CLI_CODE_PHASE AS phase_ccam, 
					CLI_CODE_ACTIVITE AS activite_ccam, 
					CLI_DONN_EXTDOC AS extension_ccam, 
					SUBSTR(CLI_DONN_MODIFICATEUR,1,1) AS modificateur_ccam_1, 
					SUBSTR(CLI_DONN_MODIFICATEUR,2,1) AS modificateur_ccam_2, 
					SUBSTR(CLI_DONN_MODIFICATEUR,3,1) AS modificateur_ccam_3, 
					SUBSTR(CLI_DONN_MODIFICATEUR,4,1) AS modificateur_ccam_4, 
					CLI_FLAG_REMBEXC AS remboursement_excp_ccam, 
					CLI_CODE_ASSNP AS association_nonprevue_ccam
				FROM w_FD_PMSI_RSS
					JOIN w_FD_PMSI_RUM ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = w_FD_PMSI_RUM.CLI_DONN_NORSS
					JOIN prod_clinicom.FD_ACTES_ENTETE ON w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE = FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE AND
										FD_ACTES_ENTETE.CLI_DATE_SAISIE BETWEEN CLI_DATE_ENTREEUM AND CLI_DATE_SORTIEUM
					JOIN prod_clinicom.FD_ACTES_CCAM ON FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE = FD_ACTES_CCAM.CLI_DONN_NOABSEPISODE AND
										FD_ACTES_ENTETE.CLI_DONN_LIEN = FD_ACTES_CCAM.CLI_DONN_LIEN
					JOIN pmsi.t_actes ON (substring(CLI_CODE_CCAM, 1, 7) = t_actes.code)
					JOIN pmsi.t_medecins ON (CLI_CODE_MEDECINREALISATEUR = t_medecins.code)
					JOIN pmsi.p_rss_rum ON (w_FD_PMSI_RSS.rss_id = p_rss_rum.rss_id AND CLI_DONN_NORUM = p_rss_rum.no_rum);

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


				INSERT INTO pmsi.p_rsf_total(
						finess, 
						rss_id, 
						no_rss, 
						code_pec, 
						rang_beneficiaire, 
						regime, 
						nature_pec, 
						justificatif_exo, 
						no_facture, 
						code_regularisation, 
						matricule_assure, 
						cle_matricule_assure, 
						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, 
						ca_ghs_theorique, 
						taux_remboursement)
				SELECT 
						w_FD_PMSI_RSS.finess, 
						w_FD_PMSI_RSS.rss_id, 
						CLI_DONN_RSSREFERENCE AS no_rss,  
						'1' AS code_pec, 
						'' AS rang_beneficiaire, 
						'' AS regime, 
						'' AS nature_pec, 
						'' AS justificatif_exo, 
						CLI_DONN_NOEXTEPISODE AS no_facture, 
						'1' AS code_regularisation, 
						'' AS matricule_assure, 
						'' AS cle_matricule_assure, 
						SUM(CLI_DONN_PRIXTOTAL) AS base_remboursement, 
						SUM(CLI_DONN_PRIXTOTAL) AS sejour_facture, 
						SUM(CLI_DONN_PRIXTOTAL) AS sejour_remboursable, 
						0 AS honoraires_factures, 
						0 AS honoraires_remboursable, 
						0 AS t2a_facture, 
						0 AS dmi_facture, 
						0 AS phc_facture, 
						0 AS participation_assure_avant_oc, 
						0 AS sejour_remboursable_2, 
						0 AS honoraires_remboursable_2, 
						0 AS montant_dmi_ghs2006, 
						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, 
						0 AS nb_exh, 
						0 AS ca_exh, 
						0 AS nb_exb, 
						0 AS ca_exb, 
						0 AS nb_ghs, 
						0 AS ca_ghs, 
						0 AS ca_ghs_theorique, 
						0 AS taux_remboursement
				FROM w_FD_PMSI_RSS
					JOIN prod_clinicom.FD_PMSI_RSS_VALORISATION ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = FD_PMSI_RSS_VALORISATION.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = FD_PMSI_RSS_VALORISATION.CLI_DONN_NORSS
					JOIN pmsi.t_prestations ON CLI_CODE_TYPE = t_prestations.code
				WHERE rss_type = 'HOS'
				GROUP BY w_FD_PMSI_RSS.finess, 
					w_FD_PMSI_RSS.rss_id, 
					CLI_DONN_RSSREFERENCE,
					CLI_DONN_NOEXTEPISODE;


				DROP TABLE IF EXISTS w_FD_ACTES_NGAP;
				CREATE TEMP TABLE w_FD_ACTES_NGAP AS
				SELECT 
						w_FD_PMSI_RSS.finess,
						w_FD_PMSI_RSS.rss_id,
						w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE,
						w_FD_PMSI_RSS.CLI_DONN_NORSS,
						CLI_DONN_RSSREFERENCE,
						CLI_DONN_NOEXTEPISODE,
						CLI_DATE_SAISIE,
						CLI_DONN_NBACTESSTATISTIQUES, 
						CLI_DONN_COEFF, 
						CASE WHEN FD_ACTES_NGAP.CLI_DONN_TARIF <> 0 THEN FD_ACTES_NGAP.CLI_DONN_TARIF ELSE FB_FAC_HISTOLETTRECLE.CLI_DONN_TARIF END AS CLI_DONN_TARIF, 
						CASE WHEN FB_FAC_LETTRECLE.CLI_CODE_IMPRESSION <> '' THEN FB_FAC_LETTRECLE.CLI_CODE_IMPRESSION ELSE FD_ACTES_NGAP.CLI_DONN_LETTRECLE END AS CLI_CODE_IMPRESSION,
						CASE WHEN CASE WHEN FB_FAC_LETTRECLE.CLI_CODE_IMPRESSION <> '' THEN FB_FAC_LETTRECLE.CLI_CODE_IMPRESSION ELSE FD_ACTES_NGAP.CLI_DONN_LETTRECLE END
								IN ('ATU', 'FTN', 'FTR', 'PHH', 'PHS', 'PII', 'PME', 'SE1', 'SE2', 'SE3', 'SE4', 'SE5', 'SE6', 'APE', 'AP2', 'FPI', 'FFM', 'REA', 'SRC', 'STF', 'PJ', 'TM') 
							THEN 'B' ELSE 'C' END AS CLI_DONN_LETTRECLE_TYPE
				FROM w_FD_PMSI_RSS
					JOIN w_FD_PMSI_RUM ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = w_FD_PMSI_RUM.CLI_DONN_NORSS
					JOIN prod_clinicom.FD_ACTES_ENTETE ON w_FD_PMSI_RUM.CLI_DONN_NOABSEPISODE = FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE AND
										FD_ACTES_ENTETE.CLI_DATE_SAISIE BETWEEN CLI_DATE_ENTREEUM AND CLI_DATE_SORTIEUM
					JOIN prod_clinicom.FD_ACTES_NGAP ON FD_ACTES_ENTETE.CLI_DONN_NOABSEPISODE = FD_ACTES_NGAP.CLI_DONN_NOABSEPISODE AND
										FD_ACTES_ENTETE.CLI_DONN_LIEN = FD_ACTES_NGAP.CLI_DONN_LIEN
					JOIN prod_clinicom.FB_FAC_LETTRECLE ON FD_ACTES_NGAP.CLI_DONN_LETTRECLE = FB_FAC_LETTRECLE.CLI_CODE_LETTRECLE 
					JOIN prod_clinicom.FB_FAC_HISTOLETTRECLE ON FD_ACTES_NGAP.CLI_DONN_LETTRECLE = FB_FAC_HISTOLETTRECLE.CLI_CODE_LETTRECLE AND
						CLI_DATE_SAISIE BETWEEN CLI_DATE_EFFET AND COALESCE(CLI_DATE_FINEFFET,'2099-12-31 00:00:00') - interval '1 second'
				WHERE rss_type = 'EXT';


				INSERT INTO pmsi.p_rsf_total(
						finess, 
						rss_id, 
						no_rss, 
						code_pec, 
						rang_beneficiaire, 
						regime, 
						nature_pec, 
						justificatif_exo, 
						no_facture, 
						code_regularisation, 
						matricule_assure, 
						cle_matricule_assure, 
						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, 
						ca_ghs_theorique, 
						taux_remboursement)
				SELECT 
						finess, 
						rss_id, 
						CLI_DONN_RSSREFERENCE AS no_rss,  
						'1' AS code_pec, 
						'' AS rang_beneficiaire, 
						'' AS regime, 
						'' AS nature_pec, 
						'' AS justificatif_exo, 
						CLI_DONN_NOEXTEPISODE AS no_facture, 
						'1' AS code_regularisation, 
						'' AS matricule_assure, 
						'' AS cle_matricule_assure, 
						SUM(CASE WHEN CLI_DONN_LETTRECLE_TYPE = 'B' THEN round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) ELSE 0 END) AS base_remboursement,
						SUM(CASE WHEN CLI_DONN_LETTRECLE_TYPE = 'B' THEN round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) ELSE 0 END) AS sejour_facture,
						SUM(CASE WHEN CLI_DONN_LETTRECLE_TYPE = 'B' THEN round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) ELSE 0 END) AS sejour_remboursable,
						SUM(CASE WHEN CLI_DONN_LETTRECLE_TYPE = 'C' THEN round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) ELSE 0 END) AS honoraires_factures,
						SUM(CASE WHEN CLI_DONN_LETTRECLE_TYPE = 'C' THEN round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) ELSE 0 END) AS honoraires_remboursable, 
						0 AS t2a_facture, 
						0 AS dmi_facture, 
						0 AS phc_facture, 
						0 AS participation_assure_avant_oc, 
						0 AS sejour_remboursable_2, 
						0 AS honoraires_remboursable_2, 
						0 AS montant_dmi_ghs2006, 
						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, 
						0 AS nb_exh, 
						0 AS ca_exh, 
						0 AS nb_exb, 
						0 AS ca_exb, 
						0 AS nb_ghs, 
						0 AS ca_ghs, 
						0 AS ca_ghs_theorique, 
						0 AS taux_remboursement
				FROM w_FD_ACTES_NGAP 
					JOIN pmsi.t_prestations ON CLI_CODE_IMPRESSION = t_prestations.code
				GROUP BY finess, 
					rss_id, 
					CLI_DONN_RSSREFERENCE,
					CLI_DONN_NOEXTEPISODE;



				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_FD_PMSI_RSS.finess, 
						w_FD_PMSI_RSS.rss_id, 
						CLI_DONN_RSSREFERENCE AS no_rss, 
						'' AS nature, 
						'' AS mt, 
						'' AS dmt, 
						date(CLI_DATE_ENTREE) AS date_debut, 
						date(CLI_DATE_SORTIE) AS date_fin, 
						CLI_DONN_QUANTITE AS nombre, 
						1::numeric AS coefficient, 
						CLI_DONN_PRIXUNITAIRE AS prix_unitaire, 
						CLI_DONN_PRIXTOTAL AS base_remboursement, 
						100 AS taux_remboursement, 
						CLI_DONN_PRIXTOTAL AS sejour_facture, 
						CLI_DONN_PRIXTOTAL AS sejour_remboursable, 
						0 AS compteur, 
						'' AS ligne_t2a, 
						'' AS pec_fj, 
						1 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, 
						0 AS no_rum, 
						0 AS unite_medicale_id, 
						0 AS base_remboursement_rum
				FROM w_FD_PMSI_RSS
					JOIN prod_clinicom.FD_PMSI_RSS_VALORISATION ON w_FD_PMSI_RSS.CLI_DONN_NOABSEPISODE = FD_PMSI_RSS_VALORISATION.CLI_DONN_NOABSEPISODE AND
									w_FD_PMSI_RSS.CLI_DONN_NORSS = FD_PMSI_RSS_VALORISATION.CLI_DONN_NORSS
					JOIN pmsi.t_prestations ON CLI_CODE_TYPE = t_prestations.code
					LEFT JOIN pmsi.t_ghs ON CLI_CODE_GHS = t_ghs.code
				WHERE rss_type = 'HOS';




				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 
						finess, 
						rss_id, 
						CLI_DONN_RSSREFERENCE AS no_rss, 
						'' AS nature, 
						'' AS mt, 
						'' AS dmt, 
						date(CLI_DATE_SAISIE) AS date_debut, 
						date(CLI_DATE_SAISIE) AS date_fin, 
						CLI_DONN_NBACTESSTATISTIQUES AS nombre, 
						CLI_DONN_COEFF AS coefficient, 
						CLI_DONN_TARIF AS prix_unitaire, 
						round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) AS base_remboursement, 
						100::numeric AS taux_remboursement, 
						round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) AS sejour_facture, 
						round(CLI_DONN_NBACTESSTATISTIQUES * CLI_DONN_COEFF * CLI_DONN_TARIF,2) AS sejour_remboursable, 
						0 AS compteur, 
						'' AS ligne_t2a, 
						'' AS pec_fj, 
						1 AS coefficient_mco, 
						0 AS sejour_remboursable_2, 
						0::bigint AS ghs_id,
						0 AS sejour_rembourse_noemie, 
						'' AS nature_noemie, 
						COALESCE(t_prestations.oid, 0) AS prestation_id, 
						CLI_DONN_LETTRECLE_TYPE AS type, 
						0 AS coefficient_geo, 
						'1' AS est_ligne_rss, 
						'0' AS est_ligne_rum, 
						0 AS no_rum, 
						0 AS unite_medicale_id, 
						0 AS base_remboursement_rum
				FROM w_FD_ACTES_NGAP 
					JOIN pmsi.t_prestations ON CLI_CODE_IMPRESSION = t_prestations.code
				WHERE CLI_DONN_TARIF <> 0;
										
			]]></sqlcmd>
		</NODE>
		
		<NODE label="LPP">
			<sqlcmd><![CDATA[

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

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


</ROOT>













