-- 20230303 검사일자 제외, HOSCOD 추가, NOT EXISTS (SELECT 1 FROM IMRESVMT 삭제 CREATE OR REPLACE VIEW IMRESINF ( HOSCOD, PATNUM, PATNME, SEXTYP, BITDAT, TELNUM, AGETYP, ORDDAT, ORDTIM, EXMDAT, EXMSBK, EXMTYP, EXMKND, EXMKNM, EXMSEQ, STATUS, PKGCOD, PKGNME , CUSTCD, CUSTNM, EMAILN, NIGHTS, HWLIFE, HWCOGN, HWMENT, HWELDE, HWRADI, JUGCDT, JUGCDR, HWBONE, BONERT, BONEJG, JUGDAT, DOCTOR, LICENO ) AS SELECT Z.HOSCOD --병원구분 , Z.PATNUM --수진번호 , Z.PATNME --수진자명 , Z.SEXTYP --성별 , Z.BITDAT --생년월일 , Z.TELNUM --전화번호 , Z.AGETYP --나이 , Z.ORDDAT --검진일자 , Z.ORDTIM --예약시간 , Z.EXMDAT --예약일자 Key값 , Z.EXMSBK --상/하반기 Key값 , Z.EXMTYP --검진형태 Key값 , Z.EXMKND --검진종류 Key값 , Z.DTLNME --검진종류명 , Z.EXMSEQ --검진차수 Key값 , Z.STATUS --상태 , Z.PKGCOD --상세 검진종류 , Z.PKGNME --묶음명 , Z.CUSTCD --사업장코드 , Z.CUSTNM --사업장명 , Z.EMAILN --이메일 , Z.NIGHTS --야간작업 유무 , Z.HWLIFE --생활습관 유무 , Z.HWCOGN --인지기능장애 유무 , Z.HWMENT --정신건강검사 유무 , Z.HWELDE --노인신체기능 유무 , Z.HWRADI --방사선작업종사자 유무 , Z.JUGCDT --폐결핵 판정 , Z.JUGCDR --'R'판정 유무 , Z.HWBONE --골밀도 유무 , Z.BONERT --골밀도 측정부위 , Z.BONEJG --골밀도 판정 , Z.JUGDAT --판정일자 , Z.DOCTOR --판정의사 , Z.LICENO --면허번호 FROM ( SELECT A.HOSCOD , B.PATNUM , B.PATNME , B.SEXTYP , B.BITDAT , NVL(B.TELNO2,B.TELNO1) TELNUM ,(SUBSTR(A.ORDDAT,1,4) - SUBSTR(B.BITDAT,1,4)) AGETYP , A.ORDDAT , A.ORDTIM , A.EXMDAT , A.EXMSBK , A.EXMTYP , A.EXMKND , D.DTLNME , A.EXMSEQ , A.STATUS , A.PKGCOD , A.PKGNME , A.CUSTCD , C.CUSTNM , B.EMAIL EMAILN ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGTYP = '60') NIGHTS ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD = 'HWLIFE') HWLIFE ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD = 'HWCOGN') HWCOGN ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD = 'HWMENT') HWMENT ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD = 'HWELDE') HWELDE ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD IN ('09','P0126')) HWRADI ,(SELECT MAX('O') FROM IMJUDGMT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.ORDDAT = A.ORDDAT AND E.JUGCOD IN ('TBC301','TBC302')) JUGCDT ,(SELECT MAX('O') FROM IMJUDGMT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.ORDDAT = A.ORDDAT AND E.JUGTYP = 'R' ) JUGCDR ,(SELECT MAX('O') FROM IMRESVPT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.PKGCOD = 'HWBONE') HWBONE ,(SELECT E.RESLT1 FROM IMEXAMMT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.ORDDAT = A.ORDDAT AND E.EXMCOD = 'QXR108') BONERT ,(SELECT E.JUGNME FROM IMJUDGMT E WHERE E.HOSCOD = A.HOSCOD AND E.PATNUM = A.PATNUM AND E.EXMKND = A.EXMKND AND E.EXMDAT = A.EXMDAT AND E.EXMSBK = A.EXMSBK AND E.EXMTYP = A.EXMTYP AND E.EXMSEQ = A.EXMSEQ AND E.ORDDAT = A.ORDDAT AND E.JUGCOD IN ('OSPLT0','OSPLT1','OSPLT2')) BONEJG , A.JUGDAT , A.JUGDO2 JUGDOT ,(SELECT Z.DTLNME FROM IMOCODMT Z WHERE Z.HOSCOD = A.HOSCOD AND Z.GRPCOD = 'DOT' AND Z.DTLCOD = A.JUGDO2) DOCTOR ,(SELECT Z.DTLTXT FROM IMOCODMT Z WHERE Z.HOSCOD = A.HOSCOD AND Z.GRPCOD = 'DOT' AND Z.DTLCOD = A.JUGDO2) LICENO FROM IMRESVMT A , IMPATBAT B , IMCUSTMT C , IMOCODMT D WHERE A.STATUS <> 'C' --AND A.HOSCOD = '02' --AND A.ORDDAT = '20210527' AND A.EXMKND <> '04' AND B.PATNUM = A.PATNUM AND C.HOSCOD = A.HOSCOD AND C.CUSTCD = A.CUSTCD AND C.CUSTYP = '1' AND D.HOSCOD = A.HOSCOD AND D.GRPCOD = 'KND' AND D.DTLCOD = A.EXMKND UNION ALL SELECT A.HOSCOD , B.PATNUM , B.PATNME , B.SEXTYP , B.BITDAT , NVL(B.TELNO2,B.TELNO1) TELNUM ,(SUBSTR(A.ORDDAT,1,4) - SUBSTR(B.BITDAT,1,4)) AGETYP , A.ORDDAT , A.ORDTIM , A.EXMDAT , A.EXMSBK , A.EXMTYP , A.EXMKND , D.DTLNME , A.EXMSEQ , A.STATUS , A.PKGCOD , A.PKGNME , A.CUSTCD , C.CUSTNM , B.EMAIL EMAILN , '' NIGHTS , '' HWLIFE , '' HWCOGN , '' HWMENT , '' HWELDE , '' HWRADI , '' JUGCDT , '' JUGCDR , '' HWBONE , '' BONERT , '' BONEJG , '' JUGDAT , '' JUGDOT , '' DOCTOR , '' LICENO FROM IMRESVMT A , IMPATBAT B , IMCUSTMT C , IMOCODMT D WHERE A.STATUS <> 'C' --AND A.HOSCOD = '02' --AND A.ORDDAT = '20210527' AND A.EXMKND = '04' AND B.PATNUM = A.PATNUM AND C.HOSCOD = A.HOSCOD AND C.CUSTCD = A.CUSTCD AND C.CUSTYP = '1' AND D.HOSCOD = A.HOSCOD AND D.GRPCOD = 'KND' AND D.DTLCOD = A.EXMKND ) Z