CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YND_FEELIST_IP
 (
 HOSPITAL_CD, PKID, IP_ID, SICK_ID, SICK_NAME, ZYCS000, FEE_ITEM_ID, FEE_ITEM_NAME,
QTY, PRICE, AMOUNT, FEE_DATE, ORDER_DEPT_CD, ORDER_DEPT_NAME, ORDER_STAFF_CD, 
ORDER_STAFF_NAME, EXEC_DEPT_CD, EXEC_DEPT_NAME, EXEC_STAFF_CD, EXEC_STAFF_NAME, 
MATERIALS_CD, MATERIALS_NAME, FIRSTCODE, SECONDCODE, DEVID, REMARK, FINA_ITEM_CD, 
FINA_ITEM_NAME, ACCOUNT_ITEM_CD, ACCOUNT_ITEM_NAME, CONSULTATION, FINA_DATE, 
PATIENT_DEPT_CODE, PATIENT_DEPT, NURSE_CD, NURSE_NAME, DOCTOR_CD, DOCTOR_NAME, 
DOCTOR_DEPT, WYZD, INPATIENTDEPTID, INPATIENTDEPTNAME, SECTIONNO, SECTIONMC, 
ORDER_SECTIONNO, ORDER_SECTIONMC, EXEC_SECTIONNO, EXEC_SECTIONMC, SSYS000, 
SSYSMC0, IYS0000, IYSMC00, IIYS000, IIYSMC0, IIIYS00, IIIYSMC, PATIENT_AGE, 
BACKFIELD1, BACKFIELD2, XKNO, XKMC, RJSJ000, DOCTOR_DEPT_CD, WS

 )
 AS 
select 
(select YYID00 from XT_YYXX00 where FLAG00=1 and rownum=1) HOSPITAL_CD,--  医院CD
'0-'||to_char(c.MXID00) PKID,-- 费用明细流水ID
a.ZYID00 IP_ID,--  住院流水号
a.BRID00 SICK_ID,--  病人ID/病案号
a.xm0000 SICK_NAME,--病人名称
a.BRRYCS ZYCS000,--  住院次数
(select SF.bh0000 from BM_YYSFXM SF where SF.SFXMID=c.XMBH00) FEE_ITEM_ID,--  收费项目编码
c.XMMC00 FEE_ITEM_NAME,--  收费项目名称
c.XMSL00 QTY,--  数量
c.XMDJ00 PRICE,--  单价
c.HJJE00 AMOUNT,-- 金额
to_date(c.ZXRQ00||c.zxsj00,'yyyy/mm/dd hh24:mi:ss') FEE_DATE,-- 收费时间
c.KDKS00 ORDER_DEPT_CD,--  开单科室编码
(select bmmc00 from bm_bmbm00 where bmbh00=c.KDKS00)ORDER_DEPT_NAME,--开单科室名称
c.KDYS00 ORDER_STAFF_CD,-- 开单员工工号
(select ZWXM00 from BM_YGBM00 where YGBH00=c.KDYS00) ORDER_STAFF_NAME,-- 开单员工名称
c.ZXKS00 EXEC_DEPT_CD,-- 执行科室编码
(select bmmc00 from bm_bmbm00 where bmbh00=c.ZXKS00)EXEC_DEPT_NAME,--执行科室名称
c.ZXYS00 EXEC_STAFF_CD,--  执行员工工号
(select ZWXM00 from BM_YGBM00 where YGBH00=c.ZXYS00) EXEC_STAFF_NAME,--  执行员工名称
null MATERIALS_CD,-- 物流耗材编码
null MATERIALS_NAME,-- 物流耗材名称
null FirstCode ,--高值耗材主码
null SecondCode ,-- 高值耗材次码
null DEVID,--  执行设备标识码
c.BEIZHU REMARK,-- 备注
(select SF.HSXMID from BM_YYSFXM SF where SF.SFXMID=c.XMBH00) FINA_ITEM_CD,-- 财务项目编号
(select HS.HSXMMC from BM_YYHSXM HS,BM_YYSFXM SF where HS.HSXMID=SF.HSXMID and SF.SFXMID=c.XMBH00) FINA_ITEM_NAME,-- 财务项目名称
(select SF.HSXMID from BM_YYSFXM SF where SF.SFXMID=c.XMBH00) ACCOUNT_ITEM_CD,-- 核算编号
(select HS.HSXMMC from BM_YYHSXM HS,BM_YYSFXM SF where HS.HSXMID=SF.HSXMID and SF.SFXMID=c.XMBH00) ACCOUNT_ITEM_NAME,-- 核算名称
null CONSULTATION,-- 是否会诊
to_date(b.CZRQ00||b.czsj00,'yyyy/mm/dd hh24:mi:ss') FINA_DATE,--  财务统计时间
--a.DQKS00 PATIENT_DEPT_CODE,-- 病人所在科室
c.kdbq00 PATIENT_DEPT_CODE,-- 病人所在科室
--(select bmmc00 from bm_bmbm00 where bmbh00=a.DQKS00)PATIENT_DEPT,--病人所在科室名称
(select bmmc00 from bm_bmbm00 where bmbh00=c.kdbq00)PATIENT_DEPT,--病人所在科室名称
a.ZRHS00 NURSE_CD,
(select ZWXM00 from BM_YGBM00 where YGBH00=a.ZRHS00) NURSE_NAME,-- 责任护士
a.ZRYS00 DOCTOR_CD,
(select ZWXM00 from BM_YGBM00 where YGBH00=a.ZRYS00) DOCTOR_NAME,--  责任医生
(select bmmc00 from bm_bmbm00 where bmbh00=a.DQKS00)DOCTOR_DEPT,-- 主管医生科室
null WYZD,--唯一字段更新数据时用于判断
a.dqbq00 INPATIENTDEPTID ,--病人所在病区代码
(select bmmc00 from bm_bmbm00 where bmbh00=a.dqbq00) INPATIENTDEPTNAME ,--  病人所在病区名称
a.YSZID0 SectionNo,--  医生归属医疗组号
(select YSZMC0 from ZS_YSZ000 where YSZID0=a.YSZID0 and YSZZT0<>'3'  and rownum=1) SectionMC,--  医生归属医疗组名称
(select YSZID0 from ZS_YSZMX0 where YSBH00=c.KDYS00 and YSZT00<>'3'  and rownum=1) ORDER_SectionNo,--  申请医生归属医疗组号
(select YSZMC0 from ZS_YSZ000 YSZ,ZS_YSZMX0 YSZMX where YSZMX.YSZID0=YSZ.YSZID0 and YSZMX.YSBH00=c.KDYS00 and YSZZT0<>'3'  and YSZT00<>'3'  and rownum=1) ORDER_SectionMC,--  申请医生归属医疗组名称
(select YSZID0 from ZS_YSZMX0 where YSBH00=c.ZXYS00 and YSZT00<>'3'  and rownum=1) EXEC_SectionNo,-- 执行医生归属医疗组号
(select YSZMC0 from ZS_YSZ000 YSZ,ZS_YSZMX0 YSZMX where YSZMX.YSZID0=YSZ.YSZID0 and YSZMX.YSBH00=c.ZXYS00 and YSZZT0<>'3'  and YSZT00<>'3'  and rownum=1) EXEC_SectionMC,-- 执行医生归属医疗组名称
(select SSYS00 from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) SSYS000,--  手术医生编号
(select SSYSMC from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) SSYSMC0,--  手术医生名称
(select IYS000 from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) IYS0000,--  I助医生编号
(select IYSMC0 from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) IYSMC00,--  I助医生名称
(select IIYS00 from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) IIYS000,--  II助医生编号
(select IIYSMC from BA_BRSSXX where LSH000=a.ZYID00 and rownum=1) IIYSMC0,--  II助医生名称
null IIIYS00,--III助医生编号
null IIIYSMC,--III助医生名称
SF_BQ_CSRQTONL(a.CSRQ00) PATIENT_AGE,   --患者年龄
null backField1,--其他1
null backField2,  --其他2
null XKNO,--    所属学科编码
null XKMC ,--   所属学科名称
null RJSJ000, -- 日间手术
(select bmbh00 from bm_ygbm00 where ygbh00=a.ZRYS00) DOCTOR_DEPT_CD,
--sign((select  count(1) from yj_yw0000 m where sfdjh0=B.djh000 and exists (select 1 from  jy_yw0000 where substr(ybh000,1,2) in ('DA','DN','AJ') and yjdjh0=m.yjdjh0))) ws
SF_JY_GETSFWSBYSFDJH(b.DJH000) ws
from ZY_BRXXB0 a,ZY_BRFY00 b,ZY_FYMX00 c
where a.ZYID00=b.ZYID00 and b.DJH000=c.DJH000
/