create or replace view vw_zy_mzzrzy_yj as
select 
  a.BRID00,    --病人ID
  a.YJDJH0,    --医技单据号
  a.ZLXMID,    --诊疗项目ID
  a.ZLXMJC,    --诊疗项目名称
  a.ZJE000,    --总金额
  a.DW0000,    --单位
  a.KDKSBH,    --开单科室编号
  a.YYID00,    --医院ID
  a.KDKSMC,    --开单科室名称
  a.KDYSXM,    --开单医生姓名
  a.KDRQ00,    --开单日期
  a.KDRQSJ,    --开单日期时间
  a.JGBGRQ,    --结果报告日期
  a.BGRQSJ,    --结果报告日期时间
  a.SFDJH0,    --收费单据号
  a.HBZYXM,    --合并住院项目
  a.SFHBZY,    --是否合并住院项目
  a.CZRQ00,    --操作日期
  a.CZRQSJ,    --操作日期时间
  a.JSZTMC,    --结算状态名称
  a.ZRZYZT,    --转入住院状态
  a.ZRZTMC,    --转入住院名称
  a.FZYJD0,    --父,子医技单
  a.XMZTMC     --项目状态名称
from (
  select 
    a.BRID00,      --病人ID
    a.YJDJH0,      --医技单据号
    a.ZLXMID,      --诊疗项目ID
    a.ZLXMJC,      --诊疗项目名称
    a.ZJE000,      --总金额
    a.DW0000,      --单位
    a.KDKSBH,      --开单科室编号
    (select YYID00 from BM_BMBM00 where BMBH00=a.KDKSBH) as YYID00,      --医院ID
    (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKSBH) as KDKSMC,      --开单科室名称
    a.KDYSXM,      --开单医生姓名
    a.KDRQ00,      --开单日期
    a.KDRQ00||' '||a.KDSJ00 as KDRQSJ,       --开单日期时间
    a.JGBGRQ,                                --结果报告日期
    a.JGBGRQ||' '||'23:59:59' as BGRQSJ,     --结果报告日期时间
    a.SFDJH0,      --收费单据号
    a.HBZYXM,      --合并住院项目
    decode(a.HBZYXM,'1','是','否') as SFHBZY,   --是否合并住院项目
    (select CZRQ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQ00,
    --(select CZRQ00||' '||CZSJ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQSJ,
    a.KDRQ00||' '||a.KDSJ00 as CZRQSJ,--20241024 官科确认修改为开单时间
    decode((select nvl(max(JZDH00),0) from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and CXBZ00='Z'),0,'未结算','已结算') as JSZTMC,
    nvl(c.ZRZYZT,'0') ZRZYZT,       --转入住院状态
    case c.ZRZYZT when '1' then '已转入' when '2' then '不转入' else '未转入' end as ZRZTMC,      --转入住院名称
    --decode(nvl((select max(ZYJDJH) from YJ_YJDJGL where ZYJDJH=a.YJDJH0 and rownum=1),0),0,'父','子') as FZYJD0,    --父,子医技单
    '父' as FZYJD0,    
    (select decode(count(*),0,'N','Y') from SF_FYMX00 
       where BRID00=a.BRID00 
         and YJDJH0=a.YJDJH0 
         and CXBZ00='Z' 
         and SF_ZY_GETYBBRMZFYZRZYPCXM(XMBH00)=0) as SFWPC0,  --是否有未排除收费项目
    /*(select decode(count(*),0,'N','Y') from SF_FYMX00 
      where BRID00=a.BRID00 
        and CXBZ00='Z' 
        and YJDJH0 in (select FYJDJH from YJ_YJDJGL where ZYJDJH=a.YJDJH0) 
        and SF_ZY_GETYBBRMZFYZRZYPCXM(XMBH00)=0) as FSFWPC,  --父医技单是否有未排除收费项目*/
    'N' as FSFWPC,
    decode(a.XMZT00,'0','申请','1','计价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') XMZTMC
  from YJ_YW0000 a,ZY_MZZRZY c
    where a.MZZYBZ='0'
      --and a.HBZYXM='1'
      and a.YJDJH0=c.YJDJH0(+)
      and a.FJDJBZ not in ('7','8','a')
      and (a.CXDJH0 is null or a.CXDJH0=0)
      and exists(select 1 from SF_FYMX00 where BRID00=a.BRID00 and CXBZ00='Z' and YJDJH0=to_char(nvl(a.YJDJH0,0)) and MZFPID not in (4,6))
      and not exists(select 1 from YJ_YJDJGL where ZYJDJH=a.YJDJH0)
) a
  where (FZYJD0='父' and SFWPC0='Y')
     or (FZYJD0='子' and FSFWPC='Y')
union
select 
  a.BRID00,      --病人ID
  a.YJDJH0,      --医技单据号
  a.ZLXMID,      --诊疗项目ID
  a.ZLXMJC,      --诊疗项目名称
  a.ZJE000,      --总金额
  a.DW0000,      --单位
  a.KDKSBH,      --开单科室编号
  (select YYID00 from BM_BMBM00 where BMBH00=a.KDKSBH) as YYID00,      --医院ID
  (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKSBH) as KDKSMC,      --开单科室名称
  a.KDYSXM,      --开单医生姓名
  a.KDRQ00,      --开单日期
  a.KDRQ00||' '||a.KDSJ00 as KDRQSJ,       --开单日期时间
  a.JGBGRQ,      --结果报告日期
  a.JGBGRQ||' '||'23:59:59' as BGRQSJ,     --结果报告日期时间
  a.SFDJH0,      --收费单据号
  a.HBZYXM,      --合并住院项目
  decode(a.HBZYXM,'1','是','否') as SFHBZY,   --是否合并住院项目
  (select CZRQ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQ00,
  --(select CZRQ00||' '||CZSJ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQSJ,
  a.KDRQ00||' '||a.KDSJ00 as CZRQSJ,--20241024 官科确认修改为开单时间
  decode((select nvl(max(JZDH00),0) from SF_FYMX00 where BRID00=a.BRID00 and YJDJH0=to_char(nvl(a.YJDJH0,0)) and CXBZ00='Z'),0,'未结算','已结算') as JSZTMC,
  nvl(c.ZRZYZT,'0') ZRZYZT,       --转入住院状态
  case c.ZRZYZT when '1' then '已转入' when '2' then '不转入' else '未转入' end as ZRZTMC,      --转入住院名称
  decode(nvl((select max(ZYJDJH) from YJ_YJDJGL where ZYJDJH=a.YJDJH0 and rownum=1),0),0,'父','子') as FZYJD0,    --父,子医技单
  decode(a.XMZT00,'0','申请','1','计价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') XMZTMC
from YJ_YW0000 a,ZY_MZZRZY c
  where a.YJDJH0=c.YJDJH0
    and not exists(select 1 from YJ_YJDJGL where ZYJDJH=a.YJDJH0);