CREATE OR REPLACE VIEW VW_BQ_YPYZZXDNEW_CF AS select Z.YZID00, Z.ZYID00, Z.YEXM00, Z.YPLBBH, Z.DMBZ00, Z.CLBZ00, Z.KZYS00, Z.LRRQ00, Z.LRSJ00, Z.PXSJ00, Z.LRRXM0, Z.HDRXM0, Z.QYRQ00, Z.QYSJ00, Z.TZRQ00, Z.TZSJ00, Z.TQRQ00, Z.TQSJ00, W.ZXRQ00, Z.TZYS00, Z.TZRXM0, Z.ZQS000, Z.ZQDW00, Z.ZQCD00, Z.ZQCYTS, Z.JYCS00, Z.BQH000, Z.KSH000, Z.YFBMBH, Z.PCID00, Z.PCMC00, Z.YFID00, Z.YPYFMC, Z.YYZF00, Z.YZZT00, Z.YZMXSM, Z.BZID00, Z.BZ0000, Z.BRDZ00, Z.BZMC00, Z.HDR000, M.YZMXID ,--ID号，序列号(分组号) Z.SRCS00 AS SRCS01, DECODE(Z.SRCS00,-1,'','[首'||Z.SRCS00||']') AS SRCS00, DECODE(YZCLLB,'正常','','自备药','[备]','门诊带药','[门]','') AS YZCLLB02, D.YPNM00 , --药品内码 M.SFDZGG , --'0'是,'1'否 是否多种规格 M.YPMC00 || decode(Z.sfps00 ,'0','','1','[代:' || Z.pspc00 || ']') AS YPMC00 , --药品名称 M.YPGG00 , --药品规格 M.QZFSID , --取整方式 M.PSID00 , --皮试ID 对应MZ_PS0000表 M.PSMC00 , --皮试名称(冗余) M.MCSL00 , --每次数量(剂量单位的每次剂量) M.JLDW00 , --剂量单位(J)(冗余) decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00)) MCSLDW, --每次用量(带单位) decode(Z.CLBZ00,'1',M.LYL000,'0',round(decode(D.QZFS00,0,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*ceil(M.MCSL00*M.J2J000/D.Z2J000)),--每次取整 1,ceil(((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000)),--每天取整 2,ceil(((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000)),--每方取整 4,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000), --每方取整后顺延 3,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000))+0.0049,2)--不取整 ) LYL000,--领药量 --M.LYL000 , --领药量(发药单位,临时医嘱用) M.ZYFYDW , --住院发药单位(Z)(住院药房维护)(冗余) D.Z2J000 , --住院发药/基本单位转换率(默认药房) M.XMYFMC , --草药细目用法名称 M.TQFS00 , --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 --'3' M.YZCLLB , --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' M.TQRQ00 TQJZRQ, --提取截至日期(加1) M.TQSJ00 TQJZSJ, --提取截至时间 M.ZFBL00 , --自付比率 M.YBLB00 , --医保类别:0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目 --4甲类药品 5乙类药品 6民族药品 7非医保药品 M.TQYPNM , --提取时实际使用的药品内码(提取使用) --(多种规格下，同种药品在提取时可以换规格) trim(M.YPMC00)||decode(M.YPGG00,'','','['||trim(M.YPGG00)||']') ||decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00))||decode(Z.sfps00 ,'0','','1','[代:' || Z.pspc00 || ']') YZNR00 , --显示字段1(药品) trim(M.YPMC00)||decode(M.YPGG00,'','','['||trim(M.YPGG00)||']') ||decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00)) YZNR04 , --显示字段4(药品) M.YZNR01 , --显示字段2(用法) M.PSTGBZ , --皮试通过标志 '+'皮试正常'-'皮试不正常 NULL未做皮试 'E'已经执行皮试 Z.SJQSRQ QSRQ00 , -- Z.SJQSSJ QSSJ00 , -- DECODE(D.DXSYBZ,'0',1,'1',CEIL(MCSL00/D.Z2J000))DXSYSL, B.RYCWH0 CH0000, B.ZYH000, B.XM0000, (select P.BZ0000 from BM_PC0000 P where Z.PCID00=P.PCID00) PCSJ00, TRUNC(SYSDATE) YYRQ00,--用药日期 W.ZXDDY0, W.JYKDY0, W.XSKDY0, ---- 医嘱范围 0今日所有 1明日所有 2今日新开 3昨日所有 decode(W.ZXRQ00,to_char(sysdate,'YYYYMMDD'),0,to_char(sysdate+1,'yyyymmdd'),1,to_char(sysdate-1,'yyyymmdd'),3) YZFW00, Y.YFLBID, substr(W.ZXRQ00,1,4)||'.'||substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ01, W.ZXSJ00, decode(Z.CLBZ00,'0','长','短') CLBZZW, decode(Z.sfps00 ,'0','','1','[代:' || Z.pspc00 || ']') AS PSPC00, B.XB0000, sf_bq_csrqtonl(B.CSRQ00) AS NL0000, substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ02, W.YPBSM0, W.TQBZ00, --20191120判断药品当日提取后才能打印 W.ID0000, decode(D.GWYPFL,'0',decode(nvl(D.GWDJ00,'-'),'-','','(高危)'),'(高危)') as GWBZ00, W.DRPCXH --当日频次序号 from BQ_YPYZ00 Z,BQ_YPYZMX M,BM_YD0000 D,ZY_BRXXB0 B,BM_YPYF00 Y,VW_BQ_YZCFMX_CF W where Z.YZID00=M.YZID00 and M.YPNM00=D.YPNM00 and Z.YFID00=Y.YFID00 and Z.ZYID00=B.ZYID00 and Z.BQH000=B.DQBQ00 --AND Y.YFLBID in (3,4,5) and YZCLLB IN ('正常','嘱托','基数药','自备药','门诊带药') and Z.YZID00=W.YZID00 --AND W.ZXRQ00>=to_char(sysdate-1,'YYYYMMDD') and ( (Z.CLBZ00='0' and Z.YZZT00 in ('1','2')) or (Z.CLBZ00='1' and Z.YZZT00 in ('1','3')) ) union all select Z.YZID00, Z.ZYID00, Z.YEXM00, Z.YPLBBH, Z.DMBZ00, Z.CLBZ00, Z.KZYS00, Z.LRRQ00, Z.LRSJ00, Z.PXSJ00, Z.LRRXM0, Z.HDRXM0, Z.QYRQ00, Z.QYSJ00, Z.TZRQ00, Z.TZSJ00, Z.TQRQ00, Z.TQSJ00, W.ZXRQ00, Z.TZYS00, Z.TZRXM0, Z.ZQS000, Z.ZQDW00, Z.ZQCD00, Z.ZQCYTS, Z.JYCS00, Z.BQH000, Z.KSH000, Z.YFBMBH, Z.PCID00, Z.PCMC00, Z.YFID00, Z.YPYFMC, Z.YYZF00, Z.YZZT00, Z.YZMXSM, Z.BZID00, Z.BZ0000, Z.BRDZ00, Z.BZMC00, Z.HDR000, M.YZMXID ,--ID号，序列号(分组号) Z.SRCS00 AS SRCS01, DECODE(Z.SRCS00,-1,'','[首'||Z.SRCS00||']') AS SRCS00, DECODE(YZCLLB,'正常','','自备药','[备]','门诊带药','[门]','') AS YZCLLB02, D.YPNM00 , --药品内码 M.SFDZGG , --'0'是,'1'否 是否多种规格 M.YPMC00 || decode(Z.sfps00 ,'0','','1','[代:' || Z.pspc00 || ']') AS YPMC00 , --药品名称 M.YPGG00 , --药品规格 M.QZFSID , --取整方式 M.PSID00 , --皮试ID 对应MZ_PS0000表 M.PSMC00 , --皮试名称(冗余) M.MCSL00 , --每次数量(剂量单位的每次剂量) M.JLDW00 , --剂量单位(J)(冗余) decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00)) MCSLDW, --每次用量(带单位) decode(Z.CLBZ00,'1',M.LYL000,'0',round(decode(D.QZFS00,0,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*ceil(M.MCSL00*M.J2J000/D.Z2J000)),--每次取整 1,ceil(((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000)),--每天取整 2,ceil(((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000)),--每方取整 4,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000), --每方取整后顺延 3,((select zqcs00 from bm_pc0000 where pcid00=z.pcid00)*M.MCSL00*M.J2J000/D.Z2J000))+0.0049,2)--不取整 ) LYL000,--领药量 --M.LYL000 , --领药量(发药单位,临时医嘱用) M.ZYFYDW , --住院发药单位(Z)(住院药房维护)(冗余) D.Z2J000 , --住院发药/基本单位转换率(默认药房) M.XMYFMC , --草药细目用法名称 M.TQFS00 , --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 --'3' M.YZCLLB , --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' M.TQRQ00 TQJZRQ, --提取截至日期(加1) M.TQSJ00 TQJZSJ, --提取截至时间 M.ZFBL00 , --自付比率 M.YBLB00 , --医保类别:0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目 --4甲类药品 5乙类药品 6民族药品 7非医保药品 M.TQYPNM , --提取时实际使用的药品内码(提取使用) --(多种规格下，同种药品在提取时可以换规格) trim(M.YPMC00)||decode(M.YPGG00,'','','['||trim(M.YPGG00)||']') ||decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00))||decode(Z.SFPS00 ,'0','','1','[代:' || Z.pspc00 || ']') YZNR00 , --显示字段1(药品) trim(M.YPMC00)||decode(M.YPGG00,'','','['||trim(M.YPGG00)||']') ||decode(M.MCSL00,0,'',decode(substr(to_char(M.MCSL00),1,1),'.','0','')||to_char(M.MCSL00)||trim(M.JLDW00)) YZNR04 , --显示字段4(药品) M.YZNR01 , --显示字段2(用法) M.PSTGBZ , --皮试通过标志 '+'皮试正常'-'皮试不正常 NULL未做皮试 'E'已经执行皮试 Z.SJQSRQ QSRQ00 , -- Z.SJQSSJ QSSJ00 , -- decode(D.DXSYBZ,'0',1,'1',ceil(MCSL00/D.Z2J000))DXSYSL, B.RYCWH0 CH0000, B.ZYH000, B.XM0000, (select P.BZ0000 from BM_PC0000 P where Z.PCID00=P.PCID00)PCSJ00, TRUNC(SYSDATE) YYRQ00, --用药日期 W.ZXDDY0, W.JYKDY0, W.XSKDY0, ---- 医嘱范围 0今日所有 1明日所有 2今日新开 3昨日所有 2 YZFW00 , Y.YFLBID, substr(W.ZXRQ00,1,4)||'.'||substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ01, W.ZXSJ00, decode(Z.CLBZ00,'0','长','短') CLBZZW, decode(Z.SFPS00 ,'0','','1','[代:' || Z.pspc00 || ']') AS PSPC00, B.XB0000, sf_bq_csrqtonl(B.CSRQ00) AS NL0000, substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ02, W.YPBSM0, W.TQBZ00, --20191120判断药品当日提取后才能打印 W.ID0000, decode(D.GWYPFL,'0',decode(nvl(D.GWDJ00,'-'),'-','','(高危)'),'(高危)') as GWBZ00, W.DRPCXH --当日频次序号 from BQ_YPYZ00 Z,BQ_YPYZMX M,BM_YD0000 D,ZY_BRXXB0 B,BM_YPYF00 Y,VW_BQ_YZCFMX_CF W where Z.YZID00=M.YZID00 and M.YPNM00=D.YPNM00 and Z.YFID00=Y.YFID00 and Z.ZYID00=B.ZYID00 and Z.BQH000=B.DQBQ00 --AND Y.YFLBID in (3,4,5) and YZCLLB in ('正常','嘱托','基数药','自备药','门诊带药') and Z.YZID00=W.YZID00(+) /* AND ( (Z.CLBZ00='0' and Z.YZZT00 in ('1','2')and to_char(sysdate,'YYYYMMDD')between Z.qyrq00 and nvl(Z.TZRQ00,to_char(SYSDATE,'YYYYMMDD'))) or(Z.CLBZ00='1' and Z.YZZT00 in ('1','3')and to_char(sysdate,'YYYYMMDD') =Z.QYRQ00)) and exists (SELECT 1 FROM BQ_YPYZCF F WHERE Z.YZID00=F.YZID00 and mod(TRUNC(SYSDATE)-to_date(F.QSRQ00,'YYYYMMDD'),F.HSZQCD/24)=0 ) and W.ZXRQ00 = to_char(sysdate,'YYYYMMDD');*/ and z.QYRQ00 = to_char(sysdate,'YYYYMMDD') and W.ZXRQ00=(select min(ZXRQ00) from BQ_YZCFMX c where c.YZID00=Z.YZID00)--新开取拆分后时间最近的记录 and ( (Z.CLBZ00='0' and Z.YZZT00 in ('1','2')) or (Z.CLBZ00='1' and Z.YZZT00 in ('1','3')) ) ;