CREATE VIEW SD_HOSPITAL.VW_OUTP_PATIENT_CARDINFO_HTBL
 (
 CARD_NO, CARD_NO1, PID, CASE_NO, PATIENT_NAME, INSURANCE_NO, CERT_NO, CERT_TYPE, 
SEX_CODE, SEX, STATUS, STATUS_DESC, DATA_OF_BIRTH, TIME_OF_BIRTH, MARRIAGE_STATUS_CODE, 
MARRIAGE_STATUS, OCCUPATION_CODE, OCCUPATION, FEE_TYPE_CODE, FEE_TYPE, 
BUSINESS_ADDRESS, HOME_ADDRESS, MOBILE_PHONE, CONTACT, BLOOD_TYPE, CONTACT_ADDRESS, 
CONTACT_PHONE, NATIONALITY_CODE, NATIONALITY, COUNTRY_CODE, COUNTRY, CONTRACT_UNIT, 
OPERATOR_ID, OPERATOR, OPERATOR_TIME, BALANCE, INSURANCE_TYPE_CODE, INSURANCE_TYPE, 
MEDICINE_PATIENT_TYPE, PREFIX_CARD_NO, MEDICINE_CENTER_TYPE, BRZYE0, ACCURATE_HELP_POOR, 
EMPI

 )
 AS 
select
--decode(b.fbbh00,'3',a.ickh00,A.kmh000) CARD_NO,  --卡号
a.ickh00 card_no,
a.kmh000 card_no1,
A.BRID00 PID,     --病人ID
trim(B.BRBLH0) BRBLH0,  --病人病历号
B.BRXM00 PATIENT_NAME,  --病人姓名
B.YBKH00 INSURANCE_NO, --医保卡号
B.BRZJBH CERT_NO,     --证件号码
B.BRZJLX CERT_TYPE,   --证件类型
decode(B.BRXB00,'女','2','男','1') SEX_CODE,  --性别代码
B.BRXB00 SEX,    --性别
A.ZT0000 STATUS, --卡状态代码
decode(A.ZT0000,'0','空白卡','1','有效卡','暂停使用卡','3','作废卡','4','无效卡') STATUS_DESC, --卡状态
B.BRCSRQ DATA_OF_BIRTH,  --出生日期
'000000' TIME_OF_BIRTH,  --出生时间
B.BRHYZK MARRIAGE_STATUS_CODE, --婚姻状况代码
B.BRHYZK MARRIAGE_STATUS, --婚姻状况
B.BRZY00 OCCUPATION_CODE,--职业代码
B.BRZY00 OCCUPATION,     --职业
B.FBBH00 FEE_TYPE_CODE,  --费别代码
(select max(FBMC00) from BM_BRFBB0 where FBBH00=B.FBBH00) FEE_TYPE, --费别
B.TYDWMC BUSINESS_ADDRESS, --单位地址
B.BRJTDZ HOME_ADDRESS,     --家庭地址
B.BRDH00 MOBILE_PHONE,     --病人电话
B.BRLXXM CONTACT,          --联系人姓名
B.BRXX00 BLOOD_TYPE,       --血型
B.BRLXDZ CONTACT_ADDRESS,  --联系人地址
B.BRLXDH CONTACT_PHONE,    --联系电话
B.BRMZ00 NATIONALITY_CODE, --民族代码
B.BRMZ00 NATIONALITY,   --民族
null COUNTRY_CODE,      --国籍代码
null COUNTRY,           --国籍
B.TYDWMC CONTRACT_UNIT, --合同单位
decode((select nvl(max(trim(value0)),'0') from XT_XTCS00 where NAME00='HTEMRHGBHPBFS'),'0',(select XKH000 from BM_YGBM00 where YGBH00=B.CZY000),CZY000) OPERATOR_ID,--建档人ID
(select ZWXM00 from BM_YGBM00 where YGBH00=B.CZY000) OPERATOR,  --建档 人
to_char(to_date(CZRQ00||CZSJ00,'yyyymmddhh24:mi:ss'),'yyyymmddhh24miss') OPERATOR_TIME, --建档日期
B.KYJE00 BALANCE,  --卡余额
B.YBLB00 INSURANCE_TYPE_CODE,  --医保类型代码
(select max(YBMC00) from IC_YBBRLB where YBLB00=B.YBLB00 and FBBH00=B.FBBH00) INSURANCE_TYPE,  --医嘱类型
B.YBBRLB MEDICINE_PATIENT_TYPE,    --医保病人类别
(select nvl(max(KHQZBH),'') from IC_KLDM00 where KLDH00=A.KLDH00) PREFIX_CARD_NO, --卡号前缀
YBZXLB MEDICINE_CENTER_TYPE,
B.ZYE000 BRZYE0, --余额
b.sfjzfp Accurate_Help_Poor ,-- 精准扶贫标志,
b.ZSYID0 empi
from IC_YHXX00 A,VW_YS_BRXXB0 B where A.BRID00=B.BRID00 and A.ZT0000='1'
/