PMSI.md

@@ -53,13 +53,23 @@ PMSI data are recorded and stored in DXCARE PMSI.
 
 ## Data description
 
+### DXCARE PMSI
+
+
+
+# Data integration
+## ETL
+
+### Diagnostic selection query
+
+
 ``` sql
 SELECT DISTINCT
   PENSOINS.C_DIAG_M.CODECIM CONCEPT_CD,
   UAM_MVT.CODE PROVIDER_ID,
-  TO_DATE(MOUVUM.DATE_SOR,'YYYYMMDD') START_DATE,
+  TO_DATE(MOUVUM.DATE_ENT || MOUVUM.HEURE_ENT,'YYYYMMDDHH24MI') START_DATE,
   PENSOINS.MOUVUM.NIRUM INSTANCE_NUM,
-  TO_DATE(MOUVUM.DATE_SOR,'YYYYMMDD') END_DATE,
+  TO_DATE(MOUVUM.DATE_SOR || MOUVUM.HEURE_SOR,'YYYYMMDDHH24MI') END_DATE,
   'DXCARE-PMSI' SOURCESYSTEM_CD,
   IAM_REFERENTIEL.I2B2_MAPPING.PATIENT_NUM_MASTER PATIENT_NUM,
   IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_NUM,
@@ -84,7 +94,7 @@ FROM
 
 WHERE
   PENSOINS.SEJOUR.NDA = IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_IDE
-  AND	PENSOINS.SEJOUR.NISEJOUR = PENSOINS.MOUVUM.NISEJOUR
+  AND PENSOINS.SEJOUR.NISEJOUR = PENSOINS.MOUVUM.NISEJOUR
   AND PENSOINS.MOUVUM.NIRUM = PENSOINS.DIAG_CIM10.NIPARENT
   AND PENSOINS.DIAG_CIM10.NIDIAGREF = PENSOINS.C_DIAG_M.NI
   AND PENSOINS.MOUVUM.NIUF(+) = UAM_MVT.NIUF
@@ -100,11 +110,52 @@ WHERE
     OR PENSOINS.DIAG_CIM10.statut IS NULL
   )
   AND IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_NUM = VISIT_DIMENSION.ENCOUNTER_NUM
-  AND EXTRACT(YEAR FROM COALESCE(VISIT_DIMENSION.END_DATE, SYSDATE)) = " + context.anneeExtraction
+  AND EXTRACT(YEAR FROM COALESCE(VISIT_DIMENSION.END_DATE, SYSDATE)) = 2020
 ```
 
-# Data integration
-## ETL
+
+### Procedure selection query
+
+```sql
+SELECT DISTINCT
+  PENSOINS.CCAMCODAGE.CCAM CONCEPT_CD,
+  UAM_MVT.CODE PROVIDER_ID,
+  TO_DATE(SUBSTR(PENSOINS.CCAMINTERV.DATEINTERV,1,8),'YYYYMMDD') START_DATE,
+  PENSOINS.MOUVUM.NIRUM INSTANCE_NUM ,
+  TO_DATE(SUBSTR(PENSOINS.CCAMINTERV.DATEINTERV,1,8),'YYYYMMDD') END_DATE,
+  'DXCARE-PMSI' SOURCESYSTEM_CD,
+  IAM_REFERENTIEL.I2B2_MAPPING.PATIENT_NUM_MASTER PATIENT_NUM,
+  IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_NUM,
+  SYSDATE UPDATE_DATE,
+  SYSDATE DOWNLOAD_DATE,
+  SYSDATE IMPORT_DATE
+
+FROM
+  PENSOINS.CCAMINTERV,
+  PENSOINS.CCAMCODAGE ,
+  PENSOINS.CCAMACTIVITE,
+  PENSOINS.MOUVUM,
+  PENSOINS.SEJOUR,
+  PENSOINS.EJ_UF UAM_MVT,
+  IAM_REFERENTIEL.I2B2_MAPPING,
+  VISIT_DIMENSION
+
+WHERE
+  PENSOINS.SEJOUR.NDA = IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_IDE
+  AND PENSOINS.SEJOUR.NISEJOUR = PENSOINS.MOUVUM.NISEJOUR
+  AND PENSOINS.SEJOUR.NISEJOUR = PENSOINS.CCAMINTERV.NISEJOUR
+  AND PENSOINS.CCAMCODAGE.NIINTERV = PENSOINS.CCAMINTERV.NIINTERV
+  AND PENSOINS.CCAMCODAGE.NICODAGE = PENSOINS.CCAMACTIVITE.NICODAGE
+  AND PENSOINS.MOUVUM.NIUF (+) = UAM_MVT.NIUF
+  AND PENSOINS.SEJOUR.ETAT_DOS != 'D'  --Dossier non détruit
+  AND PENSOINS.CCAMINTERV.RETRAIT = 'F'  --Intervention non supprimée
+  AND PENSOINS.CCAMCODAGE.STATUT != '3'  --Codage non annulée
+  AND PENSOINS.CCAMACTIVITE.STATUT != '3'  --Activité initiée ou validée
+  AND PENSOINS.MOUVUM.DATE_ENT || PENSOINS.MOUVUM.HEURE_ENT <= PENSOINS.CCAMINTERV.DATEINTERV
+  AND IAM_REFERENTIEL.I2B2_MAPPING.ENCOUNTER_NUM = VISIT_DIMENSION.ENCOUNTER_NUM
+  AND EXTRACT(YEAR FROM COALESCE(VISIT_DIMENSION.END_DATE, SYSDATE)) = 2020
+  ```
+
 
 
 ### Observation
@@ -134,7 +185,7 @@ All CONCEPT_CD have a similar structure :
 |---------------|--------------------------------|------------|--------------|-------------|
 |               |                                |DATA_ELEMENT|*VALUE_DOMAIN*|*PERMISSIBLE*|
 
-## Data types
+
 
 ## Relevant external Resources