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

				/**********************************************************************************
				 *                             SPECIALITES MEDECINS                              *
				 **********************************************************************************/
				
				
				
				/**********************************************************************************
				 *                                  MEDECINS                                      *
				 **********************************************************************************/
				
				INSERT INTO pmsi.t_medecins (code, nom, prenom, specialite_id, no_adeli, specialite_interne_id)
				SELECT
					NUM_MED::text as code
					,MEDECINS.NOM as nom
					,MEDECINS.PRENOM as prenom
					,COALESCE(t_specialites_medecin.oid, 0) as specialite_id
                    ,COALESCE(MEDECINS.RPPS,'') AS RPPS
                    ,COALESCE(t_specialites_internes_medecin.oid, 0) as specialite_interne_id
				FROM
					prod_hemat2a.MEDECINS
					LEFT JOIN pmsi.t_specialites_medecin ON t_specialites_medecin.code = to_char(MEDECINS.SPECIALITE,'FM00')
                    LEFT JOIN pmsi.t_specialites_internes_medecin ON (t_specialites_internes_medecin.code = to_char(MEDECINS.SPECIALITE,'FM00') || '..')
                    LEFT JOIN pmsi.t_medecins ON NUM_MED::text = t_medecins.code
                WHERE t_medecins.code IS NULL;
                
                SELECT pmsi.cti_reorganize_medecins_c();
				
				/**********************************************************************************
				 *                               UNITES MEDICALES                                 *
				 **********************************************************************************/
				
				
                
				/**********************************************************************************
				 *                             UNITES FONCTIONNELLES                           *
				 **********************************************************************************/
				
				INSERT INTO pmsi.t_unites_fonctionnelles(oid, code, texte) 
				SELECT 0, '', 'Non renseignée' WHERE 0 NOT IN (SELECt oid FROM pmsi.t_unites_fonctionnelles);
				
				
				
          
			]]></sqlcmd>
		</NODE>
	
		<NODE label="Identités">
			<sqlcmd><![CDATA[
				
				-- N° de rss = n° de facture
                UPDATE [SCHEMA].p_rss SET
					no_sejour_administratif = NUM_PAT::text,
					no_patient = NUM_PAT
				FROM prod_hemat2a.ARCH_FACT
				WHERE no_rss = NUM_FACT AND
					(
					no_sejour_administratif IS DISTINCT FROM num_pat::text OR
					no_patient IS DISTINCT FROM NUM_PAT
					)
				;
				
				UPDATE [SCHEMA].p_rss SET
					no_sejour_administratif = NUM_PAT::text,
					no_patient = NUM_PAT
				FROM prod_hemat2a.FACTURES
				WHERE no_rss = NUM_FACT AND
					(
					no_sejour_administratif IS DISTINCT FROM num_pat::text OR
					no_patient IS DISTINCT FROM NUM_PAT
					)
				;

				UPDATE [SCHEMA].p_identites SET
					nom = ASSURES.NOM
					,prenom = ASSURES.PRENOM
					,nom_naissance = ASSURES.NOM_JF
				FROM [SCHEMA].p_rss
                    JOIN prod_hemat2a.ASSURES ON no_sejour_administratif = NUM_PAT::text
				WHERE
					p_identites.rss_id = p_rss.oid AND
					p_rss.import_id > 0
				;
                
                UPDATE [SCHEMA].p_rss SET code_postal = SUBSTR(ASSURES.CP, 1 , 5)
				FROM prod_hemat2a.ASSURES
				WHERE no_sejour_administratif = NUM_PAT::text
				;

			]]></sqlcmd>
		</NODE>
				
		<NODE label="Médecins RSS">
			<sqlcmd><![CDATA[
			
				-- Identification des lignes honoraires
				DROP TABLE IF EXISTS w_LIGNES_FACTURES;
				CREATE TEMP TABLE w_LIGNES_FACTURES AS
				SELECT 
					NUM_FACT::text,
					PRESTATION,
					NUM_ACTE,
					SEJOUR_DU,
					SEJOUR_AU,
					QUANTITE,
					COEF,
					PRIX_UNIT,
					MAJORATION,
					MT_FACT,
					BASE_REMB,
					DEPASS,
					PART_CAISSE,
					PART_MUT,
					PART_ASS,
					EXECUTANT,
					DENOMBREMENT,
					RMO,
					NUM_PAT,
					NUM_EXE,
					CPT_COMPTA,
					DMT,
					CODE_TVA,
					NUM_LIG,
					HONO_SEJ,
					CT_EPO,
					XMT_EPO,
					CODIF_AFF,
					NUM_SOC,
					TIERPERS,
					FELEC,
					TYPE_PRODUIT,
					DATE_PRESCRIPTION_PRODUIT,
					PRIX_UNIT_NATIONAL,
					COEF_NATIONAL,
					COEF_REGIONAL,
					ACTE_PMSI,
					CODE_PRODUIT,
					TYPE_ETI,
					CODE_ASSOCIATION,
					NUM_PRODUIT,
					ID_CLEFPRIMAIRE,
					FINESS_ETAB_DESTINATAIRE,
					''::text AS medecin_executant_code_original
				FROM prod_hemat2a.LIGNES_FACTURES
				WHERE HONO_SEJ = 'true'
				; 


				INSERT INTO w_LIGNES_FACTURES
				SELECT 
					NUM_FACT::text,
					PRESTATION,
					NUM_ACTE,
					SEJOUR_DU,
					SEJOUR_AU,
					QUANTITE,
					COEF,
					PRIX_UNIT,
					MAJORATION,
					MT_FACT,
					BASE_REMB,
					DEPASS,
					PART_CAISS,
					PART_MUT,
					PART_ASS,
					EXECUTANT,
					DENOMBREMENT,
					RMO,
					NUM_PAT,
					NUM_EXE,
					CPT_COMPTA,
					DMT,
					CODE_TVA,
					NUM_LIGG,
					HONO_SEJ,
					CPTEPO,
					XMT_EPO,
					CODIF_AFF,
					NUM_SOC,
					TIERPERS,
					FELEC,
					TYPE_PRODUIT,
					DATE_PRESCRIPTION_PRODUIT,
					PRIX_UNIT_NATIONAL,
					COEF_NATIONAL,
					COEF_REGIONAL,
					ACTE_PMSI,
					CODE_PRODUIT,
					TYPE_ETI,
					CODE_ASSOCIATION,
					NUM_PRODUIT,
					ID_CLEFPRIMAIRE,
					FINESS_ETAB_DESTINATAIRE,
					''::text AS medecin_executant_code_original
				FROM prod_hemat2a.ARCH_LIG_FACT
				WHERE HONO_SEJ = 'true'
				;  
				
				ANALYSE w_LIGNES_FACTURES
				;
				
				UPDATE w_LIGNES_FACTURES
				SET medecin_executant_code_original = MEDECINS.NUM_MED::text
				FROM prod_hemat2a.MEDECINS
				WHERE HONO_SEJ = 'true' AND
					NUM_EXE = MEDECINS.FINESS AND
					EXECUTANT = MEDECINS.NOM
				;

				UPDATE w_LIGNES_FACTURES
				SET medecin_executant_code_original = MEDECINS.NUM_MED::text
				FROM prod_hemat2a.MEDECINS
				WHERE HONO_SEJ = 'true' AND
					EXECUTANT = MEDECINS.NOM AND
					medecin_executant_code_original = ''
				;
				
				ANALYSE w_LIGNES_FACTURES
				;

                UPDATE [SCHEMA].p_rss
				SET medecin_rss_id = subview.medecin_rss_id
				FROM
				(
					SELECT p_rss.oid, (MAX(ARRAY[SEJOUR_AU::text, t_medecins.oid::text]))[2]::bigint AS medecin_rss_id
					FROM w_LIGNES_FACTURES
					JOIN [SCHEMA].p_rss ON p_rss.no_rss = NUM_FACT
					JOIN prod_hemat2a.MEDECINS ON medecin_executant_code_original = NUM_MED::text
					JOIN pmsi.t_medecins ON medecin_executant_code_original = t_medecins.code
					WHERE HONO_SEJ = 'true' AND
						specialite <> 30
					GROUP BY 1
				) subview
				WHERE p_rss.oid = subview.oid
				;
                                     
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Médecins actes">
			<sqlcmd><![CDATA[
				
				DROP TABLE IF EXISTS w_rss_actes_medecin;
				CREATE TEMP TABLE w_rss_actes_medecin AS
				SELECT p_rss_actes.rss_id, p_rss_actes.CTID AS acte_CTID, MAX(t_medecins.oid) AS medecin_id
				FROM [SCHEMA].p_rss_actes
				JOIN pmsi.t_actes ON acte_id = t_actes.oid
				JOIN w_LIGNES_FACTURES ON
					p_rss_actes.no_rss = w_LIGNES_FACTURES.NUM_FACT AND
					t_actes.code = PRESTATION AND
					date_acte BETWEEN date(SEJOUR_DU) AND date(SEJOUR_AU)
				JOIN pmsi.t_medecins ON w_LIGNES_FACTURES.medecin_executant_code_original = t_medecins.code
				GROUP BY 1,2
				;

				UPDATE [SCHEMA].p_rss_actes
				SET medecin_id = w_rss_actes_medecin.medecin_id
				FROM w_rss_actes_medecin
				WHERE p_rss_actes.rss_id = w_rss_actes_medecin.rss_id AND
					p_rss_actes.CTID = w_rss_actes_medecin.acte_CTID
				;
		
			]]></sqlcmd>
		</NODE>
		
		<NODE label="Etat des dossiers">
			<sqlcmd><![CDATA[
				
				SELECT pmsi.cti_initialize_rss_etat();
			
			]]></sqlcmd>
		</NODE>	
        
        
		
	</NODE>
	
	<NODE name="PMSIETAT" label="RECUPERATION ETAT DES DOSSIERS">
			
		<NODE label="Initialisation">
			<sqlcmd><![CDATA[
			
				
			]]></sqlcmd>
		</NODE>

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