PMSI.md

@@ -49,15 +49,78 @@ Patient with a breast cancer and an ongoing diabetes.
 
 
 ## Software
+PMSI data are recorded and stored in DXCARE PMSI.
 
 ## Data description
 
+``` sql
+SELECT DISTINCT
+  PENSOINS.C_DIAG_M.CODECIM CONCEPT_CD,
+  UAM_MVT.CODE PROVIDER_ID,
+  TO_DATE(MOUVUM.DATE_SOR,'YYYYMMDD') START_DATE,
+  PENSOINS.MOUVUM.NIRUM INSTANCE_NUM,
+  TO_DATE(MOUVUM.DATE_SOR,'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,
+  CASE DIAG_CIM10.TYPE_DIAG
+    WHEN 0 THEN 'DP'
+    WHEN 1 THEN 'DR'
+    WHEN 2 THEN 'DAS'
+    WHEN 3 THEN 'DAD'
+  END TYPERUM
+
+FROM
+  PENSOINS.SEJOUR,
+  PENSOINS.MOUVUM,
+  PENSOINS.DIAG_CIM10,
+  PENSOINS.C_DIAG_M,
+  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.MOUVUM.NIRUM = PENSOINS.DIAG_CIM10.NIPARENT
+  AND PENSOINS.DIAG_CIM10.NIDIAGREF = PENSOINS.C_DIAG_M.NI
+  AND PENSOINS.MOUVUM.NIUF(+) = UAM_MVT.NIUF
+  AND PENSOINS.SEJOUR.ETAT_DOS !='D'
+  AND PENSOINS.MOUVUM.STATUTRUM = '4'
+  AND PENSOINS.DIAG_CIM10.RETRAIT = 'F'
+  AND (
+    PENSOINS.DIAG_CIM10.TYPE_PARENT = '1'
+    OR PENSOINS.DIAG_CIM10.TYPE_PARENT IS NULL
+  )
+  AND (
+    PENSOINS.DIAG_CIM10.STATUT != '7'
+    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
+```
+
 # Data integration
 ## ETL
 
 
-## Observation
+### Observation
+| SOURCE ELEMENT        | CONCEPT_CD    | MODIFIER_CD       | START DATE      |   END DATE               |
+|-----------------      |-------------  |-------------      |-----------      | ---------                |
+| ICD10 PRIMARY CODE    | Actual code   |  DP               | Mouvement start |  Mouvenment end          |
+| ICD10 SECONDARY CODE  | Actual code   |  DR               | Mouvement start |  Mouvenment end          |
+| ICD10 RELATED CODE    | Actual code   |  DAS              | Mouvement start |  Mouvenment end          |
+| CCAM PROCEDURE CODE   | Actual code   |  @                | Mouvement start |  Mouvenment end          |
+
+### Concept codes structure
 
+### Data types
+PMSI data contains only coded concepts :
+ * ICD10 codes representing diagnosis for the venue (primary, secondary and related diagnosis are coded
+ * Procedures are natively coded using a french procedure terminology (aka CCAM) a part of these codes are mapped to SNOMED-CT codes
 |SOURCE ELEMENT | CONCEPT_CD | MODIFIER_CD | START DATE | END DATE|
 |---------------|------------|-------------|------------|---------|