CREATE OR REPLACE VIEW VW_BQ_YBZFBL AS
SELECT I.YBLB00,L.YBBRLB,I.YBZXLB,L.BRLBMC,I.YBMC00,L.ZYZFSX,
       L.BRLBJC,L.MZYBZF,L.YBZFBL,L.YBQFBZ,L.SFMLXZ,L.FBBH00,null ZYYJXX
FROM   IC_YBBRLB I,BM_YBBRLB L
WHERE  I.YBZXLB=L.YBZXLB AND I.FBBH00=L.FBBH00;

-------------------------------------------
create or replace view vw_bq_ypyzje as
select a.YZID00,--医嘱号
       C.ZYID00,
       c.CLBZ00,--处理标志
       c.YPLBBH LBBH00,--药品类别
       d.LBMC00,--类别名称
       a.YZMXID,--医嘱明细ID
       a.YPNM00,--药品内码
       b.YPMC00||decode(b.YPGG00,'','','['||b.YPGG00||']') YPMC00,--药品名称
       Round(f.LSDJ00/b.K2J000*b.Z2J000,2) YPDJ00,--药品单价
       to_char(a.MCSL00)||trim(a.JLDW00) MCSL00,--每次数量(剂量单位)
       c.PCMC00,--频次
       decode(c.CLBZ00,'0',e.ZQCS00,e.ZQCS00*c.ZQS000) ZQCS00,--周期次数
       to_char(a.LYL000)||trim(a.ZYFYDW) LYL000,--领药量(住院发药单位)
       Round(a.LYL000*(f.LSDJ00/b.K2J000*b.Z2J000),2) JE0000,--金额
       c.LRRQ00||' '||c.LRSJ00 LRSJ00,--录入时间
       c.TZRQ00||' '||c.TZSJ00 TZSJ00 --停止时间
from BQ_YPYZMX a,BM_YD0000 b,BQ_YPYZ00 c,BM_YPLB00 d,BM_PC0000 e,YF_YPKCXX f
where b.YPNM00 = a.YPNM00 and c.YZID00 = a.YZID00 and
      d.LBBH00 = c.YPLBBH and e.PCID00(+) = c.PCID00 and
      f.YPNM00=b.YPNM00 and f.YFBMBH=c.YFBMBH;

----------------------------------------------------
CREATE OR REPLACE VIEW VW_BQ_YJYZ00_WTZ AS
SELECT
  R.ZYID00,  Z.YEXM00,  Z.CLBZ00,  Z.KZYS00,
  Z.TS0000,  Z.ZXKS00,  Z.PCID00,  Z.PCMC00,
  Z.YZLB00,  L.LBMC00,  Z.YZZT00,  Z.BZ0000,
  Z.ZLXMJC,  Z.MCSL00,  Z.YZCLLB,  R.XM0000, KSH000, BQH000,
  Z.TQRQ00,  Z.TQSJ00,
  Y.ZWXM00"KZYSXM",Z.YZMXID,Z.ZLXMID,NVL(P.HSZQCD,24)"HSZQCD",
  TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS')"QYRQSJ",--医嘱的起用日期
  TO_DATE(Z.SJQSRQ||Z.SJQSSJ,'YYYYMMDDHH24:MI:SS')"QSRQSJ",--医嘱最早的起用时间
  TO_DATE(DECODE(Z.TQRQ00,NULL,NULL,Z.TQRQ00||Z.TQSJ00),'YYYYMMDDHH24:MI:SS' ) "TQRQSJ",--医嘱提取时间
  TO_DATE(DECODE(Z.TQRQ00,NULL,NULL,Z.TQRQ00||Z.TQSJ00),'YYYYMMDDHH24:MI:SS' ) "CLRQSJ",--上次提取截至时间
  DECODE(Z.TZRQ00,NULL,TO_DATE(NULL,'YYYYMMDD'),TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS') ) "YTZSJ1", --医嘱预停止时间
  DECODE(Z.TZRQ00,NULL,TO_DATE(NULL,'YYYYMMDD'),TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS') ) "TZRQSJ" --医嘱预停止时间
  FROM BQ_YJYZ00 Z,ZY_BRXXB0 R,BM_ZLXMLB L,BM_YGBM00 Y,BM_PC0000 P
  WHERE Z.YZZT00 IN('1','2')  AND Z.ZYID00=R.ZYID00 AND R.CYDJRQ='20991231' AND
        Z.YZLB00=L.LBBH00 AND Z.KZYS00=Y.YGBH00 AND Z.PCID00=P.PCID00(+);

----------------------------------------
CREATE OR REPLACE VIEW VW_XT_XMFWFX_ZXKS
(jsxmks, jsksmc, zlxmid, zlxmjc, sqxmks, ksxmid, yyid00)
AS
SELECT
    a.JSXMKS,   --NUMBER(5)       NOT NULL            , --接收项目科室
    b.BMMC00,                                           --接收科室名称
    a.ZLXMID,   --CHAR(8)         NOT NULL            , --诊疗项目ID
    a.ZLXMJC,--诊疗项目名称
    a.SQXMKS,    --NUMBER(5)       NOT NULL            , --申请项目科室/住院病区
    NULL,
    B.YYID00
    FROM VW_BQ_XMFWFX A,BM_BMBM00 B
    WHERE B.BMBH00 = A.JSXMKS;
--------------------------------------------
create or replace view vw_xt_kszxxm_zxks as
select
    a.ZXKSBH,--科室编码
    b.BMMC00 ZXKSMC,--科室名称
    a.ZLXMID,--诊疗项目ID
    a.ZLXMJC,--诊疗项目名称
    a.APBZ00,--项目是否需要安排的标志 'Y'是'N'否
    a.JGBGBZ,--项目是否需要报告的标志 'Y'是'N'否
    a.BZ0000,--备注
    NULL AS KSXMID,
    NULL AS YYFZ00,
    B.YYID00 AS YYID00
    from VW_BQ_KSZXXM a,BM_BMBM00 b
    where b.BMBH00 = a.ZXKSBH and b.FLAG00='1' and b.CZBZ00<>'2';