create or replace view vw_py_cfpytxm as
select
       b.BRXM00,--姓名
       b.XB0000,--性别
       substr(SF_BQ_CSRQTONL(b.csrq00),1,10) as BRNL00,
       a.ZB0000,--组别
       a.CFID00,--处方id
       a.YPNM00,--药品内码
       b.CFLSH0,--处方号
       substr(a.ypmc00,1,15) as YPMC00,
       --a.YPMC00,--药品品名
       a.YPGG00,--药品规格
       --a.PCMC00,
       (select sm0000 from bm_pc0000 where pcmc00=a.pcmc00) PCMC00,
       a.YPZSL0,
       a.CFDW00,
       a.CSL000,--次数量
       a.JLDW00,-- 次数量单位
       a.YYSJ00,--用药时间
       a.YYSJDW,--用药时间单位
       a.YPYFMC,--药品用法
       b.YSGZH0,--医生工号
       b.YSXM00,--医生姓名
       b.GHH000,--挂号号
       b.GHID00, --挂号id
       b.SRRQ00,--输入日期
       b.SRSJ00,--输入时间
       b.BRID00,--病人id
       b.CSRQ00, --出生日期
       b.GHKS00,--挂号科室
       CFID00 PXH000,
       (select YYJC00 from XT_YYXX00 where rownum=1) YYMC00,
       (select round(a.CSL000*F2J000/Z2J000,3) from BM_YD0000 where YPNM00=a.YPNM00) CZSL00,
       (select PSMC00 FROM BM_PS0000 where psid00=a.pslbid ) PSMC00,
       (select zyfydw from BM_YD0000 where ypnm00=a.ypnm00 ) zyfydw
 from YF_MZCF00 b,YF_MZCFMX a where a.CFLSH0=b.CFLSH0 and b.CFZT00 in ('0','1','2','4') 
 AND A.SFZBY0='N'
 --AND a.CXBZ00='Z' and b.SRRQ00=to_char(sysdate,'YYYYMMDD');

 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%;