﻿create or replace view VW_YF_ZYBYDYMX (
YPQLPC,  --药品请领批次 
YPMC00,  --药品名称 
YPGG00,  --规格 
CSL000,  --剂量 
PCMC00,  --频次 
YPZSL0,  --数量
JYZSL0,  --借药总数量(一张汇总单里面同一个药品只有一行有记录)
SFZSL0,  --实发总数量
JCZSL0,  --寄存总数量
GHZSL0,  --归还总数量
QLDW00,  --请领单位 
YPYF00,  --用法 
LSDJ00,  --零售价 
YPJE00,  --金额 
TS0000,  --天数 
YYRQ00,  --用药日期 
YSXM00,  --医生 
CKDH00,  --出库单号
BRID00,  --病人ID 
ZYID00,  --ZYID00
ZYH000,  --ZYH000
XM0000,  --BRXM00
JLDW00,  --剂量单位 
YZID00,  --医嘱ID
YZMXID,  --医嘱明细ID
XMYFMC,  --细目用法
ZCXSL0,  --总冲销数量
CXCSL0,  --冲销次数量
FYKSRQ,  --服药开始日期
FYJZRQ,  --服药截止日期
SFZDBY,  --摆药途径 '0'手工'1'自动摆药'2'静配'3'干预为手工
BQBMBH,  --病区部门编号
CH0000,  --床号
XB0000,  --性别
BQMC00,  --病区名称
KSMC00,  --科室名称
NL0000,  --年龄
ZDMC00,  --诊断名称
FYRQSJ,  --摆药日期时间
BYTJMC,  --摆药条件
SHRXM0,  --审方人姓名
CFYZID,  --处方医嘱ID(门诊药房打印处方编号)
MXTYJE,  --明细退药金额
ZHL000,  --转换率
YPNM00,  --药品内码
YPZLXX,  --药品总量信息
YZNR01,  --显示字段2(用法)
FYRXM0,  --发药人姓名
YPDLBH,
YFLBID,
ZYDJS0,
MJBZ00,  --1毒麻,2精二
chzyh0,
PC0000,
YPZL01,
YFMC00,  --药房名称
SQYSXM,  --双签医生签名
YYZF00,  --嘱咐
CLZ000,  --长短嘱
SFBD00,  --是否补打
QWM000,  --区位号
SCCJZW,  --生产产家
YYMC00,  --医院名称
FZZD00,  --分组字段
DYRQ00,  --打印日期
DYSJ00,  --打印时间
FYRXM1,  --发药人姓名（不包含胸卡号）
CSL100,  --次剂量（中间计量单位）
PYSM00,
GZDJ00,
ZQCS00,  --周期次数
YYTS00,  --用作出院带药计算用药天数
PDXH00,  --盘点序号
CSL200,  --次剂量显示中间计量单位2
LBBH00,
KCDWSL,  --库存单位数量
CSL001,  --次数量转为中间计量单位形式
YSXM01,
ZQDW00,  --周期单位   
CSL002,  --草药和农本方住院处方中剂量的小数点保留1位(省老年)  
CSLNSB,
CXSLNS,
XMYF01,   --细目用法(加括号) 
--DMJBH0, 
BRZJBH,  --病人证件编号
SCPH00,  --生产批号
SJDJ00,  --神精等级
MZDJ00,  --麻醉等级
TJRXM0,   --调剂人姓名
DPDJ00
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- gzj         2018.08.03  create for YF9-20180803-003
-- gzj         2018.08.06  允许打印冲销单据 for YF9-20180712-001
-- gzj         2018.01.03  整合省人民的视图内容 for YF9-20180919-001
-- jiangzy     2019.07.09  视图vw_yf_zybydymx增加PC0000和YPZL01字段 for YF9-20190708-002
-- gzj         2019.07.11  修改TS0000字段取值 YF9-20190710-001
-- gzj         2019.08.07  增加字段YFMC00 for YF9-20190731-002
-- liwm        2019.10.18  省人民增加双签医生签名sqysxm以及沙县增加嘱咐yyzf00、长短嘱clz000; for YF9-20191017-003
-- linshu      2019.11.21  增加了是否补打字段 for YF9-20191120-002 
-- linshu      2019.12.09  增加了区位号:QWM000;生产产家：SCCJZW; by YF9-20191121-001
-- chb         2020.01.06  增加了医院名称:YYMC00;分组字段：FZZD00; for YF9-20191226-003
-- luowf       2020.04.13  工程xzh归档增加DYRQ00,DYSJ00,FYRXM1  for     YF9-20200409-001 
-- luowf       2020.04.16  增加字段次剂量CSL100 采用中间计量单位显示数量 for    YF9-20200413-002 
-- luowf       2020.04.20  增加字段拼音首码PYSM00    for    YF9-20200413-004  
-- linshu      2020.08.19  增加字段GZDJ00 by YF9-20200816-001 
-- linshu      2020.09.21  增加字段zqcs00(周期天数)、yyts00(用药天数),现场归档需求 by YF9-20200917-001
-- qks         2021.04.19  增加字段PDXH00(盘点序号)、CSL200(次剂量显示中间计量单位2); for YF9-20210420-001
-- chenxz      2021.06.11  增加字段LBBH00 for YF9-20210526-003
-- luowf       2021.11.12  增加字段库存单位的总数量KCDWSL,请领单位的次数量CSL001   YF9-20211115-002
-- chenxz      2022.02.23  增加YSXM01作为双签医师姓名显示 for YF9-20220210-001
-- linshu      2022.02.24  增加字段:ZQDW00(周期单位); for YF9-20220215-001 
-- linshu      2022.07.21  增加字段:CSL002(草药和农本方住院处方中剂量的小数点保留1位) for YF9-20220622-001
-- linshu      2022.08.03  增加字段:CSLNSB、CXSLNS；for YF9-20220721-001
-- linminghan  2023.03.07  增加字段:XMYF01(细目用法(加括号)),现场归档需求; for YF9-20230301-001
-- linshu      2024.03.12  增加字段:BRZJBH(病人证件编号); for YF9-20240304-001   
-- linshu      2024.03.07  增加字段:SCPH00(生产批号)、SJDJ00(神精等级)、MZDJ00(麻醉等级); for YF9-20240204-001 
-- linshu      2024.04.29  修正(对于有寄存的，打印出来都显示0，有问题) 现场归档; for YF9-20240424-002
-- linshu      2024.05.14  别名SCDJ00修正为SJDJ00(现场归档); for YF9-20240514-001 
-- linminghan  2024.08.05  增加字段:TJRXM0(调剂人姓名),现场归档需求; for YF9-20240709-003
-- ---------   ----------  ------------------------------------------- 
select a.YPQLPC,
       i.YPMC00,
       i.YPGG00,
       decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)||a.JLDW00 CSL000,
       a.PCMC00,
       a.YPZSL0 YPZSL0,
       nvl(a.JYZSL0,0) JYZSL0,  --借药总数量(一张汇总单里面同一个药品只有一行有记录)
       a.SFZSL0 SFZSL0,
       a.JCZSL0 JCZSL0,
       a.GHZSL0 GHZSL0,
       a.QLDW00,
       a.YPYFMC||' '||a.YFSMMC YPYF00,
       a.LSDJ00,
       round(a.YPZSL0*a.LSDJ00,2) YPJE00,
       (select nvl(max(ZQS000),1) from BQ_YPYZ00 where YZID00=a.YZID00) TS0000,
       substr(a.YYRQ00,1,4)||'-'||substr(a.YYRQ00,5,2)||'-'||substr(a.YYRQ00,7,2),
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0) YSXM00,
       a.CKDH00,
       b.BRID00,
       b.ZYID00,  --ZYID00
       b.ZYH000,  --ZYH000
       b.XM0000,  --BRXM00
       a.JLDW00,
       a.YZID00,
       a.YZMXID,
       a.XMYFMC,
       round(a.ZCXSL0*a.ZYTS00,2) as ZCXSL0,
       decode(a.ZCXSL0,0,null,'(退'||to_char(round(a.ZCXSL0/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXCSL0,
       to_char(to_date(A.YYRQ00,'YYYYMMDD'),'Mondd')||'日' FYKSRQ, --服药开始日期
       to_char(to_date(A.YYRQ00,'YYYYMMDD') + nvl(A.ZYTS00,1) - 1,'Mondd')||'日' FYJZRQ, --服药截止日期
       a.BYTJ00 SFZDBY,
       a.BQBMBH,
       a.CH0000,
       b.XB0000,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.BQBMBH) BQMC00,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.DQKS00) KSMC00,
       substr(trim(SF_XT_CSRQTONL(b.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000,
       substr(trim(sf_yf_getzybrzdxx(b.ZYID00,'1')),1,100) ZDMC00,
       substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ,
       (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJMC,
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.SHR000) SHRXM0,
       a.CFYZID,
       round(a.ZCXSL0*a.LSDJ00,2) MXTYJE,
       a.ZHL000,
       a.YPNM00,
       substr(SF_YF_YPSLZH(a.YPZSL0*a.ZHL000,a.YPNM00,3),1,50) YPZLXX,
       nvl((select YZNR01 from bq_ypyzmx where yzmxid=a.yzmxid),a.YPYFMC) as YZNR01, --显示字段2(用法)
       (select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM0,
       a.YPDLBH,
       a.YFLBID,
       a.zydjs0||'剂,代煎费'||(select hjje00 from zy_fymx00 where djh000=a.FJFDJH and XMBH00='25730') zydjs0,
       (select decode(DPDJ00||MZDJ00||decode(SJDJ00,'0','0','1','1','2'),'000','0','002','2','1') from BM_YD0000 where YPNM00=a.YPNM00) as MJBZ00,  --1毒麻,2精二
       a.CH0000||b.ZYH000  CHZYH0,
       (select nvl(max(sm0000),'') from bm_pc0000 where pcid00=a.pcid00 and rownum=1) pc0000,
       a.YPZSL0 YPZL01,
       (select bmmc00 from bm_bmbm00 where bmbh00=a.yfbmbh) yfmc00,
       (select g.zwxm00 from bq_ypyz00 f,bm_ygbm00 g where f.sqysbh=g.ygbh00 and a.yzid00=f.yzid00 and tsys00 in(6,7)and rownum=1) sqysxm, --双签医生签名
       (select yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐
       decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000,  --长短嘱
       decode(nvl(h.DYCS00,0),0,'','补打') SFBD00,
       (select QWM000 from YF_YPKCXX where YPNM00=i.YPNM00 and qwm000 is not null and yfbmbh=a.yfbmbh and rownum =1 ) QWM000,  --区位号
       (select SCCJZW from BM_YPSCCJ where SCCJBH = i.SCCJBH ) SCCJZW, --生产厂家
       (select yyQC00 from xt_yyxx00 where yyid00= (select YYID00 from bm_bmbm00 where bmbh00=a.yfbmbh and rownum=1) AND ROWNUM=1) YYMC00,  --医院名称
       A.CH0000||B.ZYID00  FZZD00,  --分组字段
       A.DYRQ00,
       A.DYSJ00,
       (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM1, --发药人姓名不带胸卡号
       substr(SF_YF_GETYPKCZLXX(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000),12,i.K2J000,i.ZJJL2J,i.KCDW00,i.ZJJLDW,i.JLDW00)||i.ZJJLDW,1,20) CSL100,   --次剂量显示中间计量单位
       i.PYSM00,
       (select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00,
       (select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) zqcs00,   --周期次数
       round(((a.ypzsl0*a.zhl000)/(a.csl000*(select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1))),0) yyts00, --用药天数
       (select pdxh00 from YF_YPKCXX x where x.yfbmbh=a.yfbmbh and x.ypnm00=a.ypnm00 and rownum=1) pdxh00, --盘点序号 - 药房维护的
       to_char((case when trim(i.ZYFYDW) in ('片','粒') then round(a.CSL000/i.Z2J000,2) else a.CSL000 end),'fm9990.00')||(case when trim(i.ZYFYDW) in ('片','粒') then trim(i.ZYFYDW) else trim(i.JLDW00) end) CSL200, --次剂量显示中间计量单位2
       i.LBBH00,
       case when instr(rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$','')),'.')>0 then
        YPZSL0||QLDW00
       else
         rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$',''))|| trim(I.KCDW00)
       end KCDWSL,
       case when a.JLDW00=I.ZJJLDW then
           ''
       else
          '('||rtrim(regexp_replace(to_char(A.CSL000/I.ZJJL2J,'FM9999990.99'),'\.$',''))|| trim(I.ZJJLDW)||')'
       end CSL001,
       decode((select count(1) from bq_ypyz00 f where a.yzid00=f.yzid00 and tsys00 >5),0,'',(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0 )) YSXM01,
       (select  zqdw00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) ZQDW00,
       decode (i.lbbh00,'2',to_char(round(a.CSL000,1)),decode (sign (A.CSL000-1),-1,to_char (a.CSL000,'FM99990.99999' ),A.CSL000))||a.JLDW00 CSL002,
       round(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)*nvl(i.nsbl00,1),1)||a.JLDW00 CSLNSB,
       decode(a.ZCXSL0*nvl(i.nsbl00,1),0,null,'(退'||to_char(round(a.ZCXSL0*nvl(i.nsbl00,1)/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXSLNS,
       decode(a.xmyfmc,'','', '('||a.XMYFMC||')') XMYF01,
       --i.dmjbh0  dmjbh0,
       nvl(c.brzjbh,'空') brzjbh,
       ( select M.SCPH00 from YF_YPCKMX M where M.CKDH00 = h.CKDH00 and M.YPNM00 = A.YPNM00 and rownum = 1 ) SCPH00,
       i.SJDJ00,
       i.MZDJ00, 
       (select ZWXM00 from BM_YGBM00 where h.TJR000 = YGBH00) TJRXM0,
       i.dpdj00
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i,bm_brxxb0 c
where a.ZYID00=b.ZYID00 and a.CKDH00=h.CKDH00
  and a.YPNM00=i.YPNM00
  and c.brid00 = b.brid00
  and not exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00)
  union all
select
       a.YPQLPC,
       (select ypmc00 from BM_YD0000 where ypnm00=d.MXYPNM) ypmc00,
       (select YPGG00 from BM_YD0000 where ypnm00=d.MXYPNM) YPGG00,
       trim(decode(substr(abs(d.YPSL00*d.ZHXS00),1,1),'.','0','')||to_char(round(d.YPSL00*d.ZHXS00,2)))||
         (select JLDW00 from bm_yd0000 where ypnm00=d.MXYPNM) CSL000,
       a.PCMC00,
       a.YPZSL0 YPZSL0,
       nvl(a.JYZSL0,0) JYZSL0,  --借药总数量(一张汇总单里面同一个药品只有一行有记录)
       a.SFZSL0 SFZSL0,
       a.JCZSL0 JCZSL0,
       a.GHZSL0 GHZSL0,
       a.QLDW00,
       a.YPYFMC||' '||a.YFSMMC YPYF00,
       a.LSDJ00,
       round(a.YPZSL0*a.LSDJ00,2) YPJE00,
       (select nvl(max(ZQS000),1) from BQ_YPYZ00 where YZID00=a.YZID00) TS0000,
       substr(a.YYRQ00,1,4)||'-'||substr(a.YYRQ00,5,2)||'-'||substr(a.YYRQ00,7,2),
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0) YSXM00,
       a.CKDH00,
       b.BRID00,
       b.ZYID00,  --ZYID00
       b.ZYH000,  --ZYH000
       b.XM0000,  --BRXM00
       a.JLDW00,
       a.YZID00,
       a.YZMXID,
       a.XMYFMC,
       round(a.ZCXSL0*a.ZYTS00,2) as ZCXSL0,
       decode(a.ZCXSL0,0,null,'(退'||to_char(round(a.ZCXSL0/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXCSL0,
       to_char(to_date(A.YYRQ00,'YYYYMMDD'),'Mondd')||'日' FYKSRQ, --服药开始日期
       to_char(to_date(A.YYRQ00,'YYYYMMDD') + nvl(A.ZYTS00,1) - 1,'Mondd')||'日' FYJZRQ, --服药截止日期
       a.BYTJ00 SFZDBY,
       a.BQBMBH,
       a.CH0000,
       b.XB0000,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.BQBMBH) BQMC00,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.DQKS00) KSMC00,
       substr(trim(SF_XT_CSRQTONL(b.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000,
       substr(trim(sf_yf_getzybrzdxx(b.ZYID00,'1')),1,100) ZDMC00,
       substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ,
       (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJMC,
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.SHR000) SHRXM0,
       a.CFYZID,
       round(a.ZCXSL0*a.LSDJ00,2) MXTYJE,
       a.ZHL000,
       d.MXYPNM,
       substr(SF_YF_YPSLZH(a.YPZSL0*a.ZHL000,a.YPNM00,3),1,50) YPZLXX,
       nvl((select YZNR01 from bq_ypyzmx where yzmxid=a.yzmxid),a.YPYFMC) as YZNR01, --显示字段2(用法)
       (select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM0,
       a.YPDLBH,
       a.YFLBID,
       a.ZYDJS0||'剂,代煎费'||(select HJJE00 from ZY_FYMX00 where DJH000=a.FJFDJH and XMBH00='25730') ZYDJS0,
       (select decode(DPDJ00||MZDJ00||decode(SJDJ00,'0','0','1','1','2'),'000','0','002','2','1') from BM_YD0000 where YPNM00=a.YPNM00) as MJBZ00,  --1毒麻,2精二
       a.CH0000||b.ZYH000  CHZYH0,
       (select nvl(max(sm0000),'') from bm_pc0000 where pcid00=a.pcid00 and rownum=1) pc0000,
       a.YPZSL0 YPZL01,
       (select bmmc00 from bm_bmbm00 where bmbh00=a.yfbmbh) yfmc00,
       (select g.zwxm00 from bq_ypyz00 f,bm_ygbm00 g where f.sqysbh=g.ygbh00 and a.yzid00=f.yzid00 and tsys00 in(6,7)and rownum=1) sqysxm, --双签医生签名
       (select yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐
       decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000,  --长短嘱
       decode(nvl(h.DYCS00,0),0,'','补打') SFBD00,
       (select QWM000 from YF_YPKCXX where YPNM00=i.YPNM00 and qwm000 is not null and yfbmbh=a.yfbmbh and rownum =1 ) QWM000,  --区位号
       (select SCCJZW from BM_YPSCCJ where SCCJBH = i.SCCJBH ) SCCJZW, --生产厂家
       (select yyQC00 from xt_yyxx00 where yyid00= (select YYID00 from bm_bmbm00 where bmbh00=a.yfbmbh and rownum=1) AND ROWNUM=1) YYMC00,  --医院名称
       A.CH0000||B.ZYID00  FZZD00,  --分组字段
       A.DYRQ00,
       A.DYSJ00,
       (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM1, --发药人姓名不带胸卡号
       trim(substr(abs(SF_YF_GETYPKCZLXX(d.YPSL00,12,i.K2J000,i.ZJJL2J,i.KCDW00,i.ZJJLDW,i.JLDW00))||i.ZJJLDW,1,20)) CSL100,    --次剂量使用
       i.PYSM00,
       (select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00,
       (select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) zqcs00,  --周期次数
       round(((a.ypzsl0*a.zhl000)/(a.csl000*(select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1))),0) yyts00, --用药天数
       (select pdxh00 from YF_YPKCXX x where x.yfbmbh=a.yfbmbh and x.ypnm00=a.ypnm00 and rownum=1) pdxh00, --盘点序号 - 药房维护的
       to_char((case when trim(i.ZYFYDW) in ('片','粒') then round(a.CSL000/i.Z2J000,2) else a.CSL000 end),'fm9990.00')||(case when trim(i.ZYFYDW) in ('片','粒') then trim(i.ZYFYDW) else trim(i.JLDW00) end) CSL200, --次剂量显示中间计量单位2
       i.LBBH00,
       case when instr(rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$','')),'.')>0 then
        YPZSL0||QLDW00
       else
         rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$',''))|| trim(I.KCDW00)
       end KCDWSL,     --库存单位数量
       case when (select JLDW00 from BM_YD0000 where YPNM00=d.MXYPNM)=I.ZJJLDW then
           ''
       else
          '('||rtrim(regexp_replace(to_char(d.YPSL00*d.ZHXS00/I.ZJJL2J,'FM9999990.99'),'\.$',''))|| trim(I.ZJJLDW)||')'
       end CSL001,
       decode((select count(1) from bq_ypyz00 f where a.yzid00=f.yzid00 and tsys00 >5),0,'',(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0 ))YSXM01,
       (select  zqdw00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) ZQDW00,
       decode (i.lbbh00,'2',to_char(round(a.CSL000,1)),trim(decode(substr(abs(d.YPSL00*d.ZHXS00),1,1),'.','0','')||to_char(round(d.YPSL00*d.ZHXS00,2))))||(select JLDW00 from bm_yd0000 where ypnm00=d.MXYPNM) CSL002,
       round(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)*nvl(i.nsbl00,1),1)||a.JLDW00 CSLNSB,
       decode(a.ZCXSL0*nvl(i.nsbl00,1),0,null,'(退'||to_char(round(a.ZCXSL0*nvl(i.nsbl00,1)/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXSLNS,
       decode(a.xmyfmc,'','', '('||a.XMYFMC||')') XMYF01,
       --i.dmjbh0  dmjbh0,
       nvl(c.brzjbh,'空') brzjbh,
       ( select M.SCPH00 from YF_YPCKMX M where M.CKDH00 = h.CKDH00 and M.YPNM00 = A.YPNM00 and rownum = 1 ) SCPH00,
       i.SJDJ00,
       i.MZDJ00,           
       (select ZWXM00 from BM_YGBM00 where h.TJR000 = YGBH00) TJRXM0,
       i.dpdj00
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i,BM_XDCFWH d,bm_brxxb0 c
where a.ZYID00=b.ZYID00 and a.CKDH00=h.CKDH00
  and a.YPNM00=i.YPNM00 and a.YPNM00=d.XDYPNM and d.XDYPNM=i.ypnm00 and b.brid00 = c.brid00
  and exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00)                                                                                             
/