1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| SELECT
SIGMA||';'||
NIP||';'||
ATC1||';'||
ATC2||';'||
ATC3||';'||
ATC4||';'||
ATC5||';'||
UCD7||';'||
MEDICAMENT||';'||
DEBUT||';'||
FIN||';'||
DUREE||';'||
QUANTITE_PERIODE||';'||
QUANTITE_24H
FROM
(
SELECT
PATIENT.HOSP_NO as SIGMA,
PATIENT.PAT_NIP as NIP,
MEDICAMENT.CLA_ATCNIV1 as ATC1,
MEDICAMENT.CLA_ATCNIV2 as ATC2,
MEDICAMENT.CLA_ATCNIV3 as ATC3,
MEDICAMENT.CLA_ATCNIV4 as ATC4,
MEDICAMENT.CLA_ATCNIV5 as ATC5,
DECODE(LENGTH(MEDICAMENT.SP_CODECIPUDC),'13',INLSUBSTR(MEDICAMENT.SP_CODECIPUDC,6,7),MEDICAMENT.SP_CODECIPUDC) as UCD7,
MEDICAMENT.SP_LIB as MEDICAMENT,
TO_CHAR(IOLIG_SYNTHESE.IOLIGS_JOURDEB, 'yyyy-mm-dd HH24:MI:SS') as DEBUT,
TO_CHAR((IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE), 'yyyy-mm-dd HH24:MI:SS') as FIN,
TRUNC(IOLIG_SYNTHESE.IOLIGS_DUREE, 2) as DUREE,
DECODE(IOLIG_SYNTHESE.CD_CODE,
'UI',(IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1))*0.000001,
'MG',(IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1))*0.001,
'G',IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1),
'MU',IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1),
'MUI',IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1),
'CAM',DECODE(SP_CD_UNITS_CNT2,
'UI',(IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD2)/(SP_CONTUCD*1000000),
'MG',(IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD2)/(SP_CONTUCD*1000),
IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD2/SP_CONTUCD),
DECODE(SP_CD_UNITS_CONT,
'UI',IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD*0.000001,
'MG',IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD*0.001,
IOLIG_SYNTHESE.IOLIGS_QTE*(trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB + IOLIG_SYNTHESE.IOLIGS_DUREE-0.00001)-trunc(IOLIG_SYNTHESE.IOLIGS_JOURDEB)+1)*SP_CONTUCD)
) as QUANTITE_PERIODE,
DECODE(IOLIG_SYNTHESE.CD_CODE,
'UI',IOLIG_SYNTHESE.IOLIGS_QTE*0.000001,
'MG',IOLIG_SYNTHESE.IOLIGS_QTE*0.001,
'G',IOLIG_SYNTHESE.IOLIGS_QTE,
'MU',IOLIG_SYNTHESE.IOLIGS_QTE,
'MUI',IOLIG_SYNTHESE.IOLIGS_QTE,
'CAM',DECODE(SP_CD_UNITS_CNT2,
'UI',(IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD2)/(SP_CONTUCD*1000000),
'MG',(IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD2)/(SP_CONTUCD*1000),
IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD2/SP_CONTUCD),
DECODE(SP_CD_UNITS_CONT,
'UI',IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD*0.000001,
'MG',IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD*0.001,
IOLIG_SYNTHESE.IOLIGS_QTE*SP_CONTUCD)
) as QUANTITE_24H
FROM
PATIENT,
MEDICAMENT,
IOLIG_SYNTHESE,
(
SELECT ISPSVCO.*
FROM
ISPSVCO,
ISERVICE
WHERE
ISPSVCO.SVC_CODE = ISERVICE.SVC_CODE
AND ISERVICE.SVC_CODE = ISERVICE.SVC_SVC_CODE
)
ISPSVCO_PH_PRINC,
ORDO_LIGNE INI,
ORDONNANCE,
IPATIENT,
SPECIALITES
WHERE
(PATIENT.ID_CB=IPATIENT.ID_CB AND PATIENT.PAT_CODE=IPATIENT.PAT_CODE)
AND (IPATIENT.ID_CB=ORDONNANCE.ID_CB and IPATIENT.PAT_CODE=ORDONNANCE.PAT_CODE)
AND (ORDONNANCE.ORD_CODE=INI.ORD_CODE)
AND (INI.OLIG_CODE=IOLIG_SYNTHESE.OLIG_CODE)
AND (INI.SP_CODE=MEDICAMENT.SP_CODE)
AND (ISPSVCO_PH_PRINC.SP_CODE=MEDICAMENT.SP_CODE)
AND IOLIG_SYNTHESE.IOLIGS_JOURDEB IS NOT NULL
AND INI.OLIG_CODE_RACINE IN
(
SELECT
BIS.OLIG_CODE
FROM
ORDO_LIGNE BIS
WHERE
INI.OLIG_CODE_RACINE=BIS.OLIG_CODE
AND BIS.olig_datec >= '01-01-2009'
AND BIS.olig_datec < '01-02-2009'
)
AND (MEDICAMENT.CLA_ATCNIV2 = 'J01' OR MEDICAMENT.CLA_ATCNIV4 = 'P01AB' OR MEDICAMENT.CLA_ATCNIV5 = 'J04AB02')
AND MEDICAMENT.SP_LIB != 'GENTAMICINE 100 MG GELULE'
AND MEDICAMENT.SP_CODE=SPECIALITES.SP_CODE
ORDER BY
PATIENT.HOSP_NO,
PATIENT.PAT_NIP,
MEDICAMENT.SP_LIB,
IOLIG_SYNTHESE.IOLIGS_JOURDEB;
) |
Partager