--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt    摆药接口之门诊处方    VW_SF_BYJK00_MZCF00
create or replace view VW_SF_BYJK00_MZCF00 as
-- MODIFICATION HISTORY
-- Person      Date           Comments
-- linzetao    2021.05.31     created  for  MZSF9-20210531-001
-- linzetao    2021.07.29     调整执行频率取BM_PC0000.SM0000  MZSF9-20210729-003
select 
   a.CFLSH0 as PresNO,      --处方流水号
   b.YPNM00 as MedOnlyCode, --药品的唯一编码
   b.YPZSL0 as MedAMT,      --出药数量
   b.CFDW00 as MedPack,     --发药单位
   b.CFDW00 as MedUnitPack, --包装单位
   1 as Medunit,            --药品规格
   substr(a.JZRQ00,1,4)||'-'||substr(a.JZRQ00,5,2)||'-'||substr(a.JZRQ00,7,2)||' '||a.JZSJ00 as MedOutTime,   --收费时间
   '' as PresType,          --处方类型
   '' as WindowNo,          --窗口号
   a.BRID00 as PatientID,   --病人id
   a.BRXM00 as PatientName, --病人姓名
   a.XB0000 as PatientSex,  --病人性别
   substr(SF_XT_CSRQTONL_BZ(a.CSRQ00),1,instrb(SF_XT_CSRQTONL_BZ(a.CSRQ00),'Y')-1) as PatientAge,   --病人年龄
   substr(a.CSRQ00,1,4)||'-'||substr(a.CSRQ00,5,2)||'-'||substr(a.CSRQ00,7,2) as PatientBirth,      --病人生日
   nvl((select max(ZDMC00) from YS_BRZDXX h where h.GHID00=a.GHID00 and ZZDBZ0='3'),
       (select max(ZDMC00) from YS_BRZDXX h where h.GHID00=a.GHID00 and h.ZZDBZ0='0')) as Diagnosis,   --诊断
   a.GHKS00 as WardNo,      --科别代码
   (select BMMC00 from BM_BMBM00 i where i.BMBH00=a.GHKS00) as WardName,     --科别名称
   trim(to_char(b.CSL000,'fm9999999990.00'))||b.JLDW00 as MedPerDos,     --每次用量
   b.YPYFMC as MedUsage,    --给药方式（口服、外服)
   --b.PCMC00 as MedPerDay,   --执行频率（三次/日)
   (select SM0000 from BM_PC0000 where PCMC00=b.PCMC00 and rownum=1) as MedPerDay,    --执行频率（三次/日)
   a.YSXM00 as DoctorName,  --医生姓名
   '' as FPNO,              --发票号
   b.HJJE00 as MedUnitPrice,--药品单价,药品单价*药品数量
   a.CFZJE0 as MedTotalPrice,     --处方总价格
   '' as MedType,           --药品类型
   1 as MedConvercof,       --转换系数
   (select ICKH00 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1') as remark,    --备注, 收费时使用的卡号
   '0' as YFCode,           --药房标记
   b.YPMC00 as MedName,     --药品名称
   (select SCCJZW from BM_YPSCCJ 
         where rownum=1 and SCCJBH=(select SCCJBH from BM_YD0000 where YPNM00=b.YPNM00)) as MedFactory,   --药品厂家
   (select decode(BRZJLX,'身份证',BRZJBH,'') from BM_BRXXB0 where BRID00=a.BRID00) as Remark2,    --身份证号
   '' as Remark3,           --医源通卡号
   (select YBKH00 from BM_BRXXB0 where BRID00=a.BRID00) as Remark4    --医保卡号
from YF_MZCF00 a,YF_MZCFMX b 
where a.CFLSH0=b.CFLSH0
/