SELECT tmp1.pid, tmp1.indd, tmp1.instcd, tmp1.cretno, tmp1.visitno, DECODE (tmp1.visitno, 0, ptbs.hngnm, ptbs.hngnm || 'ⓡ') AS patnm , ptbs.sex AS sex , com.fn_zz_getage ('', '', TO_CHAR (SYSDATE, 'YYYYMMDD'), 'b', ptbs.brthdd) AS age , CASE WHEN erce.nurscccnts IS NULL OR erce.nurscccnts = '' THEN '' ELSE erce.nurscccnts || '; ' END || CASE WHEN errt.termengnm IS NULL OR erce.nurscccnts = '' THEN '' ELSE errt.termengnm || '; ' END || (SELECT NVL (term.termengnm, '') FROM emr.mmohdiag diag, emr.mrtmterm term WHERE tmp1.pid = diag.pid AND tmp1.cretno = diag.cretno AND tmp1.instcd = diag.instcd AND tmp1.indd = diag.orddd AND diag.diagkindcdflag = 'M' AND diag.diaghistcd = 'O' AND diag.genrflagcd = 'E' AND diag.diagkindcd = 'C' AND diag.diagtypecd = 'D' AND diag.instcd = term.instcd(+) AND diag.diagcd = term.termcd(+) AND term.termflag(+) = '0' AND term.termfromdd(+) <= TO_CHAR (SYSDATE, 'YYYYMMDD') AND term.termtodd(+) >= TO_CHAR (SYSDATE, 'YYYYMMDD') AND ROWNUM = 1) AS diagnm , DECODE (inpt.fsexamflag, NULL, (SELECT fsexamflag FROM pam.pmohotpt WHERE pid = tmp1.pid AND instcd = tmp1.instcd AND orddd = tmp1.indd AND cretno = tmp1.cretno), inpt.fsexamflag ) AS fsexamflag, RTRIM (errt.fstexamorddd || errt.fstexamordtm) AS fstexamorddt , DECODE (code.cdnm, '-', '', NULL, '', code.cdnm) AS recovwardcd, (SELECT MAX (opcr.patinroomdt) FROM emr.mmohoprs oprs, emr.mnphopcr opcr WHERE oprs.instcd = opcr.instcd AND oprs.oprsrvno = opcr.oprsrvno AND oprs.ophistcd = 'O' AND oprs.pid = tmp1.pid AND oprs.instcd = tmp1.instcd AND oprs.opcnfmdd BETWEEN tmp1.chosdd AND inpt.dschdd) AS opdd , tmp1.outrmcd, CASE WHEN inpt.inpath = '1' AND tmp1.outrmcd LIKE '3%' THEN '외래경유 입원' WHEN inpt.inpath = '1' THEN '외래경유 내원' WHEN tmp1.outrmcd = '14' THEN 'DAMA' WHEN tmp1.outrmcd = '13' THEN 'Hopeless Discharge' WHEN tmp1.outrmcd = '19' THEN '외래처치대행' WHEN tmp1.outrmcd LIKE '1%' THEN '귀가' WHEN tmp1.outrmcd LIKE '2%' THEN '전원' WHEN tmp1.outrmcd IN ('33', '34') THEN '수술' WHEN tmp1.outrmcd LIKE '3%' THEN '입원' WHEN tmp1.outrmcd = '41' THEN 'DOA' WHEN tmp1.outrmcd LIKE '4%' THEN '사망' WHEN tmp1.outrmcd LIKE '8%' THEN '탈원' WHEN tmp1.outrmcd LIKE '9%' THEN '기타' WHEN tmp1.outrmcd IS NULL OR tmp1.outrmcd = '' THEN '응급실재원' END AS outrmcdnm, tmp1.outrmdd || tmp1.outrmtm AS outrmdt , tmp1.outdecidt AS decidt , tmp1.chosdd || tmp1.chostm AS indt , tmp1.maindept AS mainorddept, (SELECT usernm FROM com.zsumusrb WHERE userid = tmp1.maindoctcd AND userfromdd <= TO_CHAR (SYSDATE, 'YYYYMMDD') AND usertodd >= TO_CHAR (SYSDATE, 'YYYYMMDD')) AS maindocnm, tmp1.maincalldt, tmp1.mainarrivdt, ROUND ( ( TO_DATE (CASE WHEN tmp1.maincalldt NOT LIKE '-%' AND tmp1.maincalldt IS NOT NULL THEN tmp1.maincalldt END, 'yyyymmddhh24miss') - TO_DATE (tmp1.chosdd || tmp1.chostm, 'yyyymmddhh24miss') ) * 24 * 60 ) AS callmodin , ROUND ( ( TO_DATE (CASE WHEN tmp1.mainarrivdt NOT LIKE '-%' AND tmp1.mainarrivdt IS NOT NULL THEN tmp1.mainarrivdt END, 'yyyymmddhh24miss') - TO_DATE (CASE WHEN tmp1.maincalldt NOT LIKE '-%' AND tmp1.maincalldt IS NOT NULL AND tmp1.maincalldt != '' THEN tmp1.maincalldt END, 'yyyymmddhh24miss' ) ) * 24 * 60 ) AS visitmodcall , (CASE WHEN (tmp1.outdecidt IS NULL OR tmp1.outdecidt = '-') AND (tmp1.chosdd IS NULL OR tmp1.chosdd = '-') THEN NULL ELSE ROUND ((TO_DATE (tmp1.outdecidt, 'yyyymmddhh24miss') - TO_DATE (tmp1.chosdd || tmp1.chostm, 'yyyymmddhh24miss')) * 24 * 60) END ) AS dschdclrmodin , (CASE WHEN (tmp1.outrmdd IS NULL OR tmp1.outrmdd = '-') AND (tmp1.chosdd IS NULL OR tmp1.chosdd = '-') THEN NULL ELSE ROUND ( (TO_DATE (tmp1.outrmdd || tmp1.outrmtm, 'yyyymmddhh24miss') - TO_DATE (tmp1.chosdd || tmp1.chostm, 'yyyymmddhh24miss')) * 24 * 60 ) END ) AS outmodin , (CASE WHEN (tmp1.outrmdd IS NULL OR tmp1.outrmdd = '-') OR (tmp1.outdecidt IS NULL OR tmp1.outdecidt = '-') THEN NULL ELSE ROUND ((TO_DATE (tmp1.outrmdd || tmp1.outrmtm, 'yyyymmddhh24miss') - TO_DATE (tmp1.outdecidt, 'yyyymmddhh24miss')) * 24 * 60) END ) AS outmoddschdclr , tmp1.dept1 AS difnm1, tmp1.calldt1 AS difcall1, tmp1.arrivdt1 AS difvisit1, CASE WHEN (tmp1.arrivdt1 LIKE '-%' OR tmp1.arrivdt1 IS NULL) OR (tmp1.calldt1 LIKE '-%' OR tmp1.calldt1 IS NULL) THEN NULL ELSE ROUND ((TO_DATE (tmp1.arrivdt1, 'yyyymmddhh24miss') - TO_DATE (tmp1.calldt1, 'yyyymmddhh24miss')) * 24 * 60) END AS difgab1, tmp1.dept2 AS difnm2, tmp1.calldt2 AS difcall2, tmp1.arrivdt2 AS difvisit2, CASE WHEN (tmp1.arrivdt2 LIKE '-%' OR tmp1.arrivdt2 IS NULL) OR (tmp1.calldt2 LIKE '-%' OR tmp1.calldt2 IS NULL) THEN NULL ELSE ROUND ((TO_DATE (tmp1.arrivdt2, 'yyyymmddhh24miss') - TO_DATE (tmp1.calldt2, 'yyyymmddhh24miss')) * 24 * 60) END AS difgab2, tmp1.dept3 AS difnm3, tmp1.calldt3 AS difcall3, tmp1.arrivdt3 AS difvisit3, CASE WHEN (tmp1.arrivdt3 LIKE '-%' OR tmp1.arrivdt3 IS NULL) OR (tmp1.calldt3 LIKE '-%' OR tmp1.calldt3 IS NULL) THEN NULL ELSE ROUND ((TO_DATE (tmp1.arrivdt3, 'yyyymmddhh24miss') - TO_DATE (tmp1.calldt3, 'yyyymmddhh24miss')) * 24 * 60) END AS difgab3, tmp1.dept4 AS difnm4, tmp1.arrivdt4 AS difcall4, tmp1.calldt4 AS difvisit4, CASE WHEN (tmp1.arrivdt4 LIKE '-%' OR tmp1.arrivdt4 IS NULL) OR (tmp1.calldt4 LIKE '-%' OR tmp1.calldt4 IS NULL) THEN NULL ELSE ROUND ((TO_DATE (tmp1.arrivdt4, 'yyyymmddhh24miss') - TO_DATE (tmp1.calldt4, 'yyyymmddhh24miss')) * 24 * 60) END AS difgab4, tmp1.dept5 AS difnm5, tmp1.calldt5 AS difcall5, tmp1.arrivdt5 AS difvisit5, CASE WHEN (tmp1.calldt5 LIKE '-%' OR tmp1.arrivdt5 IS NULL) OR (tmp1.arrivdt5 LIKE '-%' OR tmp1.calldt5 IS NULL) THEN NULL ELSE ROUND ((TO_DATE (tmp1.arrivdt5, 'yyyymmddhh24miss') - TO_DATE (tmp1.calldt5, 'yyyymmddhh24miss')) * 24 * 60) END AS difgab5, erce.bedposcnts, DECODE (errt.serdiagflag, NULL, '-', errt.serdiagflag) serdiagflag , CASE WHEN com.fn_zz_getage ('', '', TO_CHAR (SYSDATE, 'YYYYMMDD'), 'b', ptbs.brthdd) != '-' AND com.fn_zz_getage ('', '', TO_CHAR (SYSDATE, 'YYYYMMDD'), 'b', ptbs.brthdd) >= 15 AND ( (TO_NUMBER (errt.highbp) <= 90 AND errt.highbp NOT IN ('-1', '999')) OR (TO_NUMBER (errt.mntpulsecnt) >= 120 AND errt.mntpulsecnt NOT IN ('-1', '999')) ) THEN (SELECT COUNT (COUNT (pcri.instcd || pcri.pid || pcri.orddd || TO_CHAR (pcri.cretno) || pcor.recdt)) cnt FROM emr.mnrhpcri pcri, emr.mnrhpcor pcor, emr.mnrdlbil lbil WHERE pcri.carerecseqno = pcor.carerecseqno AND pcri.instcd = pcor.instcd AND pcri.itemcd = lbil.itemcd AND pcri.instcd = lbil.instcd AND lbil.lnkviewcd = 'SMMNE00200' AND lbil.cncldd = '99991231' AND pcri.pid = tmp1.pid AND pcri.orddd = tmp1.indd AND pcri.cretno = tmp1.cretno AND pcri.instcd = tmp1.instcd AND pcor.cncldt = '99991231235959' AND (pcor.recrslt IS NOT NULL OR TRIM (pcor.recrslt) != '') AND pcor.wardcd IN (SELECT cdid FROM emr.mnwmcode WHERE cdgrupid = '002' AND instcd = '012') AND pcor.recdt BETWEEN DECODE (tmp1.chosdd, NULL, (inpt.indd || inpt.intm), (tmp1.chosdd || tmp1.chostm)) || '00' AND DECODE (tmp1.outrmdd, NULL, (inpt.dschdd || inpt.dschtm), (tmp1.outrmdd || tmp1.outrmtm)) || '00' GROUP BY pcri.instcd, pcri.pid, pcri.orddd, pcri.cretno, pcor.recdt HAVING COUNT (pcri.instcd || pcri.pid || pcri.orddd || TO_CHAR (pcri.cretno) || pcor.recdt) >= (SELECT COUNT (1) FROM emr.mnrdlbil WHERE lnkviewcd = 'SMMNE00200')) ELSE NULL END AS unstablevs FROM (SELECT pid, indd, instcd, cretno, visitno, chosdd, chostm, outrmdd, outrmtm, outrmcd, outdecidt, MAX (DECODE (mainyn, 'M', deptengabbr, NULL)) maindept, MAX (DECODE (mainyn, 'M', calldt, NULL)) maincalldt, MAX (DECODE (mainyn, 'M', arrivdt, NULL)) mainarrivdt, MAX (DECODE (mainyn, 'M', erorddoctcd, NULL)) maindoctcd, MAX (CASE WHEN mainyn = 'S' AND mrnum = 1 THEN deptengabbr ELSE NULL END) dept1, MAX (CASE WHEN mainyn = 'S' AND mrnum = 1 THEN calldt ELSE NULL END) calldt1, MAX (CASE WHEN mainyn = 'S' AND mrnum = 1 THEN arrivdt ELSE NULL END) arrivdt1, MAX (CASE WHEN mainyn = 'S' AND mrnum = 2 THEN deptengabbr ELSE NULL END) dept2, MAX (CASE WHEN mainyn = 'S' AND mrnum = 2 THEN calldt ELSE NULL END) calldt2, MAX (CASE WHEN mainyn = 'S' AND mrnum = 2 THEN arrivdt ELSE NULL END) arrivdt2, MAX (CASE WHEN mainyn = 'S' AND mrnum = 3 THEN deptengabbr ELSE NULL END) dept3, MAX (CASE WHEN mainyn = 'S' AND mrnum = 3 THEN calldt ELSE NULL END) calldt3, MAX (CASE WHEN mainyn = 'S' AND mrnum = 3 THEN arrivdt ELSE NULL END) arrivdt3, MAX (CASE WHEN mainyn = 'S' AND mrnum = 4 THEN deptengabbr ELSE NULL END) dept4, MAX (CASE WHEN mainyn = 'S' AND mrnum = 4 THEN calldt ELSE NULL END) calldt4, MAX (CASE WHEN mainyn = 'S' AND mrnum = 4 THEN arrivdt ELSE NULL END) arrivdt4, MAX (CASE WHEN mainyn = 'S' AND mrnum = 5 THEN deptengabbr ELSE NULL END) dept5, MAX (CASE WHEN mainyn = 'S' AND mrnum = 5 THEN calldt ELSE NULL END) calldt5, MAX (CASE WHEN mainyn = 'S' AND mrnum = 5 THEN arrivdt ELSE NULL END) arrivdt5 FROM (SELECT ervd.pid, ervd.indd, ervd.instcd, ervd.cretno, ervd.visitno, ervd.chosdd, ervd.chostm, ervd.outrmdd, ervd.outrmtm, ervd.outrmcd, ervd.outdecidt, ercd.calldt, ercd.arrivdt, ercd.mainyn, ercd.erorddeptcd, ercd.erorddoctcd, dept.deptengabbr, ROW_NUMBER () OVER (PARTITION BY ervd.indd, ervd.pid, ervd.cretno, ervd.visitno, ercd.mainyn ORDER BY ervd.indd, ervd.pid, ervd.cretno, ervd.visitno, ercd.mainyn, ercd.calldt) mrnum, ROW_NUMBER () OVER (PARTITION BY ervd.indd, ervd.pid, ervd.cretno, ervd.visitno ORDER BY ervd.indd, ervd.pid, ervd.cretno, ervd.visitno, ercd.mainyn, ercd.calldt) rnum FROM emr.mnehervd ervd, emr.mnehercd ercd, com.zsdddept dept WHERE ervd.instcd = '012' AND ervd.chosdd BETWEEN '20090301' AND '20091231' AND ervd.incncldt = '-' AND ervd.pid = ercd.pid(+) AND ervd.indd = ercd.indd(+) AND ervd.cretno = ercd.cretno(+) AND ervd.visitno = ercd.visitno(+) AND ervd.instcd = ercd.instcd(+) AND ercd.histcd(+) = 'O' AND ercd.erorddeptcd = dept.deptcd(+) AND dept.instcd(+) = '012' AND dept.valifromdd(+) <= TO_CHAR (SYSDATE, 'YYYYMMDD') AND dept.valitodd(+) >= TO_CHAR (SYSDATE, 'YYYYMMDD')) GROUP BY pid, indd, instcd, cretno, visitno, chosdd, chostm, outrmdd, outrmtm, outrmcd, outdecidt) tmp1, pam.pmihinpt inpt, emr.mnwmcode code, pam.pmcmptbs ptbs, emr.mneherce erce, emr.mmoherrt errt WHERE tmp1.pid = inpt.pid AND tmp1.indd = inpt.indd AND tmp1.cretno = inpt.cretno AND tmp1.instcd = inpt.instcd AND inpt.mskind = 'M' AND inpt.histstat = 'Y' AND inpt.recovwardcd = code.cdid(+) AND code.cdgrupid(+) = '104' AND code.instcd(+) = '012' AND tmp1.pid = ptbs.pid AND tmp1.instcd = ptbs.instcd AND inpt.pid = erce.pid(+) AND inpt.indd = erce.indd(+) AND inpt.cretno = erce.cretno(+) AND inpt.instcd = erce.instcd(+) AND erce.incncldt(+) = '-' AND tmp1.pid = errt.pid(+) AND tmp1.indd = errt.orddd(+) AND tmp1.instcd = errt.instcd(+) AND tmp1.cretno = errt.cretno(+) AND errt.histcd(+) = 'O' ORDER BY chosdd, chostm