CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_YZLL00
 (
 ZYID00, RYCWH0, YEXM00, LB0000, CLBZ00, DMBZ00, YZLB00, YZZT00, YZID00, 
YZMXID, QYSJ00, QYYSHS, YFNR00, YZXX00, LYLXX0, TYSJ00, TYYSHS, BZ0000, 
QYRQ00, TZRQ00, PXSJ00, ZXRQ00, TQSJ00, LRRQ00, LRSJ00, YZLY00, BQH000, 
CYDJRQ, BB0000, ZXKSMC, ZXYSMC, KZYSMC, TZSHR0, TSYS00, CJKS00, SRCS00, 
YFID00, BBID00, CZPC00, KSH000, YZHDSJ, YZYZM0, YCXBZ0, SPZT00, SPZTMC, 
XMZTMC, JKZTMC

 )
 AS 
SELECT     A.ZYID00,                              --住院ID
               E.RYCWH0,                              --床号
               A.YEXM00,                              --婴儿姓名
               '0',                                   --药品
               A.CLBZ00,                              --处理标志长期'0'短期'1'
               A.DMBZ00,                              --毒麻标志'0'正常'1'毒麻
               to_number(A.YPLBBH),                   --药品类别
               decode(A.YZZT00,'2',decode(sign(to_number(A.ZXRQ00)-to_number(to_char(sysdate,'YYYYMMDD'))),0,'9',1,'9','2'),A.YZZT00),      --医嘱状态
               A.YZID00,                              --医嘱ID
               B.YZMXID,                              --医嘱明细ID
               substr(A.QYRQ00,3,6)||' '||A.QYSJ00,                      --起用日期+起用时间
               trim(C.ZWXM00)||'/'||trim(A.LRRXM0)||'/'||trim(A.HDRXM0),             --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               decode(A.YPLBBH,'2',A.CFMC00||' '||to_char(A.ZQCYTS)||'贴','') --草药贴数
                   ||decode(A.YPLBBH,'2',decode(A.JYCS00,0,'','代煎'||to_char(A.JYCS00)||'贴' )||SF_BQ_CALZYYYRQ(A.YYRQ00,A.ZQCYTS,A.CLBZ00),'') --草药煎药次数
                   ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次
                   ||decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','[首'||to_char(SRCS00)||']'))
                   ||trim(A.YPYFMC)||decode(a.yplbbh,'2',trim(A.PCMC00),'') --用法20200318
                   ||decode(A.CLBZ00,'0','',decode(A.ZQS000,1,'',to_char(A.ZQS000)||trim(A.ZQDW00))) YFNR00,--短期周期数
               --药品信息
               SF_BQ_YZTSBZ(B.YZMXID,0)  --电子病例补入标识
               ||(select decode(nvl(h.ypnm00,0),'0','', '(危)') from XT_XTCS00 g,BM_YD0000 h where g.NAME00='BQ_GWYPYZQZ' and g.VALUE0='1' and h.YPNM00=b.YPNM00 and h.GWYPFL in('1','2','3') )
               ||trim(B.YPMC00)--药品名称
                   ||decode(b.YPGG00,'','','['||trim(B.YPGG00)||']')--药品规格
                   ||decode(B.MCSL00,0,'',decode(substr(to_char(B.MCSL00),1,1),'.','0','')||to_char(B.MCSL00)||trim(B.JLDW00)) --每次数量
                   ||trim(B.PSMC00)                      --皮试名称
                   ||trim(B.XMYFMC) --草药细目名称
                   ||decode(b.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') --处理
                   ||trim(B.BZ0000)
                   ||decode(YZZT00,'3',decode(nvl(YCXBZ0,'0'),'1','(预撤销)',''),'') 
                   ||DECODE(ZFFSBZ,'Y',' (自费)',null) YZXX00, --备注
               decode(A.CLBZ00,'0',decode(sign((select count(*) from XT_XTCS00 where name00='BQ_CQYZZJSSFTXYPSL' and value0<>'0')),1,
                   decode(A.YPLBBH,'2','',decode(B.LYL000,0,'[无]',
                   decode(substr(to_char(B.LYL000),1,1),'.','0','')||to_char(B.LYL000)||trim(B.ZYFYDW))),''),
                   decode(A.YPLBBH,'2','',decode(B.LYL000,0,'[无]',
                   decode(substr(to_char(B.LYL000),1,1),'.','0','')||to_char(B.LYL000)||trim(B.ZYFYDW)))) LYLXX0,--短期领药量
--               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间
               decode(a.YZZT00,'3',substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),substr(nvl(A.YTZRQ0,A.TZRQ00),3,6)||' '||substr(nvl(A.YTZSJ0,A.TZSJ00),1,5)),             --停用日期+停用时间
               decode(D.ZWXM00,null,'',trim(D.ZWXM00)||decode(a.TZRXM0,null,'','/'||trim(A.TZRXM0))), --停止医生+停止人姓名
               trim(A.YYZF00)||decode(b.SFMFBZ,'0','','1','[GCP]') BZ0000,                        --备注
               A.QYRQ00,                              --起用日期
               A.TZRQ00,                              --停止日期
               A.PXSJ00,                              --排序时间
               A.ZXRQ00,                              --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5), --提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00,--录入时间
               substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.YZLY00, --医嘱来源 '0'护士'1'医生'2'手术
               a.BQH000, --病区
               e.cydjrq,
               '' BB0000,
               --'', ---原先药品的执行科室为空 现修改为药房部门名称 caijy 2024.09.11
               (select g.bmmc00 from yf_yzypsq f,bm_bmbm00 g where f.yzid00=a.yzid00 and g.bmbh00=f.yfbmbh and f.zyid00=e.zyid00 and rownum=1) as ZXKSMC,
               --'', ---原先药品的执行人为空 现修改成提交医生姓名 caijy 2024.09.11
               (select g.zwxm00 from yf_yzypsq f,bm_ygbm00 g where f.yzid00=a.yzid00 and f.fyr000=g.ygbh00 and f.zyid00=e.zyid00 and rownum=1) as ZXYSMC,
               trim(C.ZWXM00),
               (select F.ZWXM00 from BM_YGBM00 F where F.ygbh00=A.TZSHR0 and rownum=1) TZSHR0,
               a.TSYS00,
               '' CJKS00,
               a.SRCS00,
               a.YFID00,
               '' BBID00,
               a.czpc00,
               a.KSH000,
               substr(A.HDRQ00,3,6)||' '||A.HDSJ00,
               a.YZYZM0,
               nvl(a.YCXBZ0,'0'),
               b.SPZT00,
               decode(nvl(b.SPZT00,'0'),'0','','1','已提交未审批','2','审批通过','3','审批不通过,可双签','4','双签名通过','5','审核不通过') SPZTMC,
               ' '  XMZTMC,  --项目状态
               ' '  JKZTMC   --接口状态
      from BQ_YPYZ00 a,
           BQ_YPYZMX b,
           BM_YGBM00 c,
           BM_YGBM00 d,
           zy_brxxb0 e
      where b.YZID00 = a.YZID00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.KZYS00
      and d.YGBH00(+) = a.TZYS00
      and a.zyid00=e.zyid00
UNION ALL
    select     A.ZYID00,  --住院ID
               E.RYCWH0,  --床号
               A.YEXM00,  --婴儿姓名
               '1',       --非药品医嘱
               A.CLBZ00,  --处理标志长期'0'短期'1'
               '0',       --
               A.YZLB00,--医嘱类别
               decode(a.YZZT00,'2',decode(sign(to_number(a.TQRQ00)-to_number(to_char(sysdate,'YYYYMMDD'))),0,'9',1,'9','2'),a.YZZT00),      --医嘱状态
               A.YZMXID,--医嘱明细ID
               A.YZMXID,--医嘱明细ID
               substr(a.QYRQ00,3,6)||' '||a.QYSJ00,          --起用日期+起用时间
               trim(b.ZWXM00)||'/'||trim(a.LRRXM0)||'/'||trim(a.HDRXM0), --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               '',
               --治疗项目信息
               --trim(a.ZLXMJC)||' '  --治疗项目名称
               trim(SF_ZS_GETFYYZMC(a.yzmxid))||' ' --治疗项目名称
               ||decode(a.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理
               ||trim(a.PCMC00)||' ' --频次               
               ||case when trim(a.DW0000)= '小时' or a.MCSL00!='1'   then decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||'*'||trim(a.DW0000)||' ' end||' ' --每次数量
               ||decode(a.CLBZ00,0,decode(a.TS0000,0,'',to_char(a.TS0000)||trim(a.TSDW00)),decode(a.TS0000,1,'',to_char(a.TS0000)||trim(a.TSDW00))) --周期数
               ||'['||d.BMMC00||'/'
               ||decode((select Trim(value0) from xt_xtcs00 where name00='BQ_YZLLSFAZXQKXSZXYS'),'Y',
                       decode(a.CLBZ00,'0',(select ZXRXM0 from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and ZXRQ00=to_char(sysdate,'YYYYMMDD') and
                                            SL0000>CXSL00 and ZXRXM0 is not null and rownum=1),
                             (select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))),
               (select ZWXM00 from BM_YGBM00 where YGBH00=NVL(a.ZXYS00,a.KZYS00)))||']'
               ||(select decode(nvl(a.SQID00,0),0,'[未申请]','[已申请'||a.SQID00||']') from BM_ZLZD00 e,XT_XTCS00 f where ZLXMID=a.ZLXMID and nvl(e.ZYSQID,0)>0 and a.ZLXMID>0 and f.NAME00='ZS_YSXMSQD' and VALUE0='Y')
               ||decode(YZZT00,'3',decode(nvl(YCXBZ0,'0'),'1','(预撤销)',''),'') 
               ||decode(nvl(a.ZFFSBZ,'N'),'Y','(自费)','')||trim(a.bz0000) YZXX00,
               '',
--               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间
               decode(a.YZZT00,'3',substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),substr(nvl(A.YTZRQ0,A.TZRQ00),3,6)||' '||substr(nvl(A.YTZSJ0,A.TZSJ00),1,5)),             --停用日期+停用时间
               decode(C.ZWXM00,null,'',trim(C.ZWXM00)||decode(a.TZRXM0,null,'','/'||trim(A.TZRXM0))), --停止医生+停止人姓名
               trim(A.BZ0000)||decode(a.SFMFBZ,'0','','1','[GCP]') BZ0000,                             --备注
               A.QYRQ00,                   --起用日期
               A.TZRQ00,                   --停止日期
               A.PXSJ00,                   --排序时间
               A.TQRQ00,                   --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5),--提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00,--录入时间
               --substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.YZLY00, --医嘱来源 '0'护士'1'医生'2'手术
               a.BQH000, --病区
               e.cydjrq,
               a.BB0000,
               d.BMMC00 ZXKSMC,
--               decode((select Trim(value0) from xt_xtcs00 where name00='BQ_YZLLSFAZXQKXSZXYS'),'Y',
--               (select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid)),
--               (select ZWXM00 from BM_YGBM00 where YGBH00=NVL(a.ZXYS00,a.KZYS00))) ZXYSMC,


        /*
               decode((select Trim(value0) from xt_xtcs00 where name00='BQ_YZLLSFAZXQKXSZXYS'),'Y',
                       decode(a.CLBZ00,'0',(select ZXRXM0 from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and ZXRQ00=to_char(sysdate,'YYYYMMDD')
                       and SL0000>CXSL00 and ZXRXM0 is not null and rownum=1),
                             (select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))),
               (select ZWXM00 from BM_YGBM00 where YGBH00=NVL(a.ZXYS00,a.KZYS00))) ZXYSMC,

        */

                ---修改执行医生的判断方式 2025.07.31 caijiayuan 原先的判断方式出现医技医嘱的执行人全都是住院医生的问题 修改后根据医技执行人判断执行医生
                (case
                when (select trim(value0) from xt_xtcs00 where name00='BQ_YZLLSFAZXQKXSZXYS')='Y' and a.clbz00='0'
                then (select zxrxm0 from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid and zxrq00=to_char(sysdate,'yyyymmdd') and sl0000>cxsl00 and zxrxm0 is not null and rownum=1)
                when (select trim(value0) from xt_xtcs00 where name00='BQ_YZLLSFAZXQKXSZXYS')='N' and a.clbz00='0'
                then a.lrrxm0
                when a.clbz00='1'
                then (select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))
                else a.lrrxm0
                end ) zxysmc,


               trim(B.ZWXM00),
               (select F.ZWXM00 from BM_YGBM00 F where F.ygbh00=A.TZSHR0 and rownum=1) TZSHR0,
               -1 TSYS00,
               to_char(a.CJKS00) CJKS00,
               -1 as SRCS00,
               '0' YFID00,
               a.BBID00,
               a.czpc00,
               a.KSH000,
               substr(A.HDRQ00,3,6)||' '||A.HDSJ00,
               ' ' YZYZM0,
               nvl(a.YCXBZ0,'0') YCXBZ0,
               '0'  SPZT00,
               ' ' SPZTMC,
               substr(SF_BQ_GETXMZTMC(a.ZYID00,a.YZMXID,a.CLBZ00),1,20) XMZTMC,
               substr(SF_BQ_GETJKZTMC(a.ZYID00,a.YZMXID,a.CLBZ00),1,20) JKZTMC              
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           zy_brxxb0 e
      where b.YGBH00(+) = a.KZYS00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and a.zyid00=e.zyid00
/