IDempiere 監測 HIS 醫療系統

SELECT * FROM FTH008K WHERE IDNO='E121081171'--身分別
SELECT * FROM FTH020K WHERE POSI='039'--身分別

SELECT l.REGNO,l.NAME,l.IDNO,l.BIRTH,l.TELP,CASE WHEN l.SEX='1' THEN '男' ELSE '女' END CASE,l.ADDRESS,a.CASENO,
s.NAMES,s1.NAMES,a.CASENO,A.OBDATE,A.OBTIME
FROM FOB002K a --醫令表頭
INNER JOIN FTH008K l ON a.REGNO=l.REGNO --基本
INNER JOIN FIB003K b ON b.CASENO=a.CASENO --就診身分
INNER JOIN FTH020K s ON b.POSI=s.POSI      --就診身分
INNER JOIN FTH020K s1 ON l.POSI=s1.POSI      --基本身分
WHERE l.REGNO='00132562'
order by A.OBDATE,A.OBTIME

SELECT l.REGNO,l.NAME,l.IDNO,l.BIRTH,l.TELP,CASE WHEN l.SEX='1' THEN '男' ELSE '女' END CASE,l.ADDRESS,
c.CITY_NAME,c.POST_NO,y.NAME,s.Name
FROM  FTH008K l

INNER JOIN FTH020K s ON s.POSI=l.POSI             --身分
INNER JOIN FTH012K c ON c.CITY=l.CITY_ID          --鄉鎮市
INNER JOIN ZNTY01  z ON l.REGNO=z.REGNO           --00132562
INNER JOIN FTH011K y ON z.INSNO=y.INSNO           --F0001
WHERE l.REGNO='00132562'


SELECT * FROM FOB002K WHERE REGNO='00132562'--醫令表
SELECT * FROM FOB003K --醫令表身

SELECT * FROM  FTH011K --公司團體
SELECT * FROM  FTH008K WHERE REGNO='00132562' -- 勞工資料



SELECT l.REGNO,l.NAME,l.IDNO,l.BIRTH,l.TELP,CASE WHEN l.SEX='1' THEN '男' ELSE '女' END CASE,l.ADDRESS,
c.CITY_NAME,c.POST_NO,y.NAME,s.NAMES
FROM  FTH008K l

INNER JOIN FTH020K s ON s.POSI=l.POSI             --身分
INNER JOIN FTH012K c ON c.CITY=l.CITY_ID          --鄉鎮市
INNER JOIN ZNTY01  z ON l.REGNO=z.REGNO           --00132562
INNER JOIN FTH011K y ON z.INSNO=y.INSNO           --F0001
WHERE l.REGNO='00132562'


SELECT CITY_NAME,POST_NO FROM FTH012K WHERE CITY=


CREATE TABLE IPD.ZNTY01  --公司團體勞工資料
(
  INSNO      VARCHAR2(10 BYTE),
  REGNO      VARCHAR2(20 BYTE)
)

INSERT INTO ZNTY01 VALUES('F0001','00132562')

INNER JOIN ZNTY01  z ON l.REGNO=z.REGNO         --00132562
INNER JOIN FTH011K y ON z.ISNO=t.ISNO           --F0001


SELECT * FROM ALL_TABLES t WHERE t.OWNER='IPD' AND
EXISTS (SELECT 1 FROM ALL_TAB_COLS c WHERE c.OWNER='IPD' AND c.COLUMN_NAME='CASENO' AND c.TABLE_NAME=t.TABLE_NAME)
AND
EXISTS (SELECT 1 FROM ALL_TAB_COLS c WHERE c.OWNER='IPD' AND c.COLUMN_NAME='POSI' AND c.TABLE_NAME=t.TABLE_NAME
  AND c.DATA_LENGTH=4)
 
選出 醫療身分 改變的 診療註記
 
SELECT  COUNT(1)  FROM FEX001K_SYN    333
SELECT  COUNT(1)  FROM FIB003K        7998
SELECT  COUNT(1)  FROM FIB003K_1020712
SELECT  COUNT(1)  FROM FIB003K_1030127
SELECT  COUNT(1)  FROM FIB018K    0
SELECT  COUNT(1)  FROM FPR005K    691325

SELECT * FROM FIB003K