-- Start of DDL script for VW_BQ_YJYZZXDNEW -- Generated 18-三月-14 3:17:39 pm -- from fw2-SD_HOSPITAL:1 -- View VW_BQ_YJYZZXDNEW CREATE OR REPLACE VIEW vw_bq_yjyzzxdnew ( yzid00, zyid00, yexm00, yplbbh, dmbz00, clbz00, kzys00, lrrq00, lrsj00, pxsj00, lrrxm0, hdrxm0, qyrq00, qysj00, tzrq00, tzsj00, tqrq00, tqsj00, zxrq00, tzys00, tzrxm0, zqs000, zqdw00, zqcd00, zqcyts, jycs00, bqh000, ksh000, yfbmbh, pcid00, pcmc00, yfid00, ypyfmc, yyzf00, yzzt00, yzmxsm, bzid00, bz0000, brdz00, bzmc00, hdr000, yzmxid, ypnm00, sfdzgg, ypmc00, ypgg00, qzfsid, psid00, psmc00, mcsl00, jldw00, lyl000, zyfydw, z2j000, xmyfmc, tqfs00, yzcllb, tqjzrq, tqjzsj, zfbl00, yblb00, tqypnm, yznr00, yznr01, pstgbz, qsrq00, qssj00, dxsysl, ch0000, zyh000, xm0000, pcsj00, yyrq00, yzfl00, zxddy0, jykdy0, xskdy0, yzfw00, zxrq01, clbzzw, bb0000 ) AS SELECT Z.YZMXID, Z.ZYID00, Z.YEXM00, TO_CHAR(Z.YZLB00) YPDLBH, '0', 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, NULL , Z.TZYS00, Z.TZRXM0, Z.TS0000, Z.TSDW00, (select P.ZQCD00 from BM_PC0000 P where Z.PCID00=P.PCID00), 0 , 0 , Z.BQH000, Z.KSH000, Z.ZXKS00, Z.PCID00, Z.PCMC00, NULL , ' ' , ' ' , Z.YZZT00, 0 , 0 , Z.BZ0000, ' ' , ' ' , 0 , Z.YZMXID ,--ID号，序列号(分组号) Z.ZLXMID, --药品内码 NULL , --'0'是,'1'否 是否多种规格 Z.ZLXMJC, --药品名称 NULL , --药品规格 0 , --取整方式 NULL , --皮试ID 对应MZ_PS0000表 NULL , --皮试名称(冗余) Z.MCSL00, --每次数量(剂量单位的每次剂量) Z.DW0000, --剂量单位(J)(冗余) 0 , --领药量(发药单位,临时医嘱用) NULL , --住院发药单位(Z)(住院药房维护)(冗余) 0 , --住院发药/基本单位转换率(默认药房) NULL , --草药细目用法名称 '0' , --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 --'3' Z.YZCLLB, --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' Z.TQRQ00 TQJZRQ, --提取截至日期(加1) Z.TQSJ00 TQJZSJ, --提取截至时间 Z.ZFBL00, --自付比率 Z.YBLB00, --医保类别:0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目 --4甲类药品 5乙类药品 6民族药品 7非医保药品 0 , --提取时实际使用的药品内码(提取使用) --医嘱内容 trim(Z.ZLXMJC)||' ' ||decode(Z.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理 ||trim(Z.PCMC00)||' ' --频次 ||decode(Z.MCSL00,1,'',decode(substr(Z.MCSL00,1,1),'.','0','')||trim(Z.MCSL00)||trim(Z.DW0000))||' ' --每次数量 ||decode(Z.CLBZ00,0,decode(Z.TS0000,0,'',to_char(Z.TS0000)||trim(Z.TSDW00)),decode(Z.TS0000,1,'',to_char(Z.TS0000)||trim(Z.TSDW00))), --周期数 Z.BZ0000, NULL, Z.SJQSRQ QSRQ00 , -- Z.SJQSSJ QSSJ00 , -- 0, B.RYCWH0 CH0000, B.ZYH000, B.XM0000, (select P.BZ0000 from BM_PC0000 P where Z.PCID00=P.PCID00) PCSJ00, TRUNC(SYSDATE),--用药日期 '1' YZFL00,--项目医嘱 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, substr(W.ZXRQ00,1,4)||'.'||substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ01, decode(Z.CLBZ00,'0','长','短') CLBZZW, Z.BB0000 FROM BQ_YJYZ00 Z,BM_ZLZD00 D,ZY_BRXXB0 B,BM_BQKS00 K,VW_BQ_YZCFMX_distinct W WHERE Z.ZLXMID=D.ZLXMID AND Z.ZYID00=B.ZYID00 AND Z.BQH000=B.DQBQ00 AND YZCLLB IN ('正常','嘱托','基数药','自备','门诊处理') AND D.BQDYBZ='Y' AND Z.KSH000=K.KSH000 AND Z.BQH000=K.BQH000 AND Z.YZMXID=W.YZID00 AND W.ZXRQ00>=to_char(sysdate-1,'YYYYMMDD') AND( (Z.CLBZ00='0' AND Z.YZZT00 IN ('1','2')AND TO_CHAR(SYSDATE,'YYYYMMDD')BETWEEN Z.SJQSRQ AND NVL(Z.TZRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))AND EXISTS (SELECT 1 FROM BQ_YJYZCF F WHERE Z.YZMXID=F.YZMXID AND MOD(TRUNC(SYSDATE)-TO_DATE(F.QSRQ00,'YYYYMMDD'),F.HSZQCD/24)>=0 )) OR(Z.CLBZ00='1' AND Z.YZZT00 IN ('1','3')AND TO_CHAR(SYSDATE,'YYYYMMDD') =Z.QYRQ00)) UNION ALL SELECT Z.YZMXID, Z.ZYID00, Z.YEXM00, TO_CHAR(Z.YZLB00) YPDLBH, '0', 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, NULL , Z.TZYS00, Z.TZRXM0, Z.TS0000, Z.TSDW00, (select P.ZQCD00 from BM_PC0000 P where Z.PCID00=P.PCID00), 0 , 0 , Z.BQH000, Z.KSH000, Z.ZXKS00, Z.PCID00, Z.PCMC00, NULL , ' ' , ' ' , Z.YZZT00, 0 , 0 , Z.BZ0000, ' ' , ' ' , 0 , Z.YZMXID ,--ID号，序列号(分组号) Z.ZLXMID, --药品内码 NULL , --'0'是,'1'否 是否多种规格 Z.ZLXMJC, --药品名称 NULL , --药品规格 0 , --取整方式 NULL , --皮试ID 对应MZ_PS0000表 NULL , --皮试名称(冗余) Z.MCSL00, --每次数量(剂量单位的每次剂量) Z.DW0000, --剂量单位(J)(冗余) 0 , --领药量(发药单位,临时医嘱用) NULL , --住院发药单位(Z)(住院药房维护)(冗余) 0 , --住院发药/基本单位转换率(默认药房) NULL , --草药细目用法名称 '0' , --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 --'3' Z.YZCLLB, --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' Z.TQRQ00 TQJZRQ, --提取截至日期(加1) Z.TQSJ00 TQJZSJ, --提取截至时间 Z.ZFBL00, --自付比率 Z.YBLB00, --医保类别:0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目 --4甲类药品 5乙类药品 6民族药品 7非医保药品 0 , --提取时实际使用的药品内码(提取使用) --医嘱内容 trim(Z.ZLXMJC)||' ' ||decode(Z.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理 ||trim(Z.PCMC00)||' ' --频次 ||decode(Z.MCSL00,1,'',decode(substr(Z.MCSL00,1,1),'.','0','')||trim(Z.MCSL00)||trim(Z.DW0000))||' ' --每次数量 ||decode(Z.CLBZ00,0,decode(Z.TS0000,0,'',to_char(Z.TS0000)||trim(Z.TSDW00)),decode(Z.TS0000,1,'',to_char(Z.TS0000)||trim(Z.TSDW00))), --周期数 Z.BZ0000, NULL, Z.SJQSRQ QSRQ00 , -- Z.SJQSSJ QSSJ00 , -- 0, B.RYCWH0 CH0000, B.ZYH000, B.XM0000, (select P.BZ0000 from BM_PC0000 P where Z.PCID00=P.PCID00) PCSJ00, TRUNC(SYSDATE),--用药日期 '1' YZFL00,--项目医嘱 W.ZXDDY0, W.JYKDY0, W.XSKDY0, ---- 医嘱范围 0今日所有 1明日所有 2今日新开 3昨日所有 2 YZFW00, substr(W.ZXRQ00,1,4)||'.'||substr(W.ZXRQ00,5,2)||'.'||substr(W.ZXRQ00,7,2) ZXRQ01, decode(Z.CLBZ00,'0','长','短') CLBZZW, Z.BB0000 FROM BQ_YJYZ00 Z,BM_ZLZD00 D,ZY_BRXXB0 B,BM_BQKS00 K,VW_BQ_YZCFMX_distinct W WHERE Z.ZLXMID=D.ZLXMID AND Z.ZYID00=B.ZYID00 AND Z.BQH000=B.DQBQ00 AND YZCLLB IN ('正常','嘱托','基数药','自备','门诊处理') AND D.BQDYBZ='Y' AND Z.KSH000=K.KSH000 AND Z.BQH000=K.BQH000 AND Z.YZMXID=W.YZID00 AND( (Z.CLBZ00='0' AND Z.YZZT00 IN ('1','2')AND TO_CHAR(SYSDATE,'YYYYMMDD')BETWEEN Z.SJQSRQ AND NVL(Z.TZRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))AND EXISTS (SELECT 1 FROM BQ_YJYZCF F WHERE Z.YZMXID=F.YZMXID AND MOD(TRUNC(SYSDATE)-TO_DATE(F.QSRQ00,'YYYYMMDD'),F.HSZQCD/24)=0 )) OR(Z.CLBZ00='1' AND Z.YZZT00 IN ('1','3')AND TO_CHAR(SYSDATE,'YYYYMMDD') =Z.QYRQ00)) and W.ZXRQ00 = TO_CHAR(SYSDATE,'YYYYMMDD') / -- End of DDL script for VW_BQ_YJYZZXDNEW