PROMPT 22.VW_BQ_CFYPSC
--创建处方药品输出视图VW_BQ_CFYPSC
--DROP VIEW VW_BQ_CFYPSC;
CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_CFYPSC
 (
 YFBMBH, YPNM00, YPMC00, YPGG00, LSJ000, JLDW00, MZFYDW, ZYFYDW, Z2J000, 
K2J000, M2J000, CYJL00, SFPSYY, SFDZGG, QZFS00, DMBZ00, MZDJ00, DPDJ00, 
SJDJ00, CYL000, PCID00, YFID00, PCMC00, YFMC00, ZQCS00, ZQCD00, ZQDW00 
 )
 AS 
select
--date          user   history
--2008.03.28    yangh  增加周期长度,周期单位
                b.YFBMBH,--药房部门编号
                b.YPNM00,--药品内码，机器内码
		a.YPMC00,--药品名称
		a.YPGG00,--药品规格
		round(b.LSDJ00/K2J000,4) LSJ000,--零售价
		a.JLDW00,--剂量单位(J)
		a.MZFYDW,--门诊发药单位
		a.ZYFYDW,--住院发药单位(Z)（住院药房维护）
		a.Z2J000,--Z/J转换率  --单位相同，此值须为1
		a.K2J000,--K/J转换率
		a.M2J000,--M/J转换率
		a.CYJL00,--常用剂量
		a.SFPSYY,--是否皮试用药'N'-否，'Y'-是
		a.SFDZGG,--是否多种规格'0'是，'1'否
		a.QZFS00, --取整方式
		decode(a.MZDJ00||a.DPDJ00||decode(a.SJDJ00,'1','1','0'),'000','0','1') DMBZ00, --毒麻标识
        a.MZDJ00,--麻醉等级
		a.DPDJ00,--毒麻等级
		a.SJDJ00,--神经等级		
        a.cyl000,
        a.pcid00,
        a.yfid00,
        (select c.pcmc00 from bm_pc0000 c where c.pcid00=a.pcid00) pcmc00,
        (select d.ypyfmc from bm_ypyf00 d where d.yfid00=a.yfid00) yfmc00,
        (select e.zqcs00 from bm_pc0000 e where e.pcid00=a.pcid00) zqcs00,
        (select f.zqcd00 from bm_pc0000 f where f.pcid00=a.pcid00) zqcd00,
        (select DECODE(SIGN(ZQCD00-1),1,'*'||TO_CHAR(ZQCD00)||ZQDW00,ZQDW00) 
        from bm_pc0000 g where g.pcid00=a.pcid00) zqdw00
from BM_YD0000 a,YF_YPKCXX b
where a.YPNM00=b.YPNM00 and b.PDXH00<>'0' AND NVL(b.ZTBZ00,'0')='0'
/