--prompt 诊疗字典价格视图 VW_BM_ZLZDJG
create or replace view VW_BM_ZLZDJG 
-- MODIFICATION HISTORY                                                       
-- Person    Date          Comments                                           
-- linbin    2022.09.09    created 补归档 by XMGL9-20220909-001
as
select
      a.ZLXMID,--项目ID
      a.ZLXMJC,--项目简称
      sum(nvl(C.TCJE00,b.SFJE00)*c.SFCS00) SFJE00 --单价
 from BM_ZLZD00 a, XT_ZLSFGX c,VW_BM_YYSFXM b
where a.ZLXMID = c.ZLXMID and c.SFXMID =b.SFXMID 
  and a.QYRQ00 is not null and a.QYRQ00<=to_char(sysdate,'yyyymmdd')
  and ((a.TZRQ00 is null) or (a.TZRQ00>to_char(sysdate,'yyyymmdd')))
  and a.ZLXMID>=100  and a.SFYXSQ='Y' and a.LBBZ00='1' 
group by a.ZLXMID,a.ZLXMJC
union all select
      ZLXMID,--项目ID
      ZLXMJC,--项目简称
      0
 from BM_ZLZD00 a
where not exists (select ZLXMID from XT_ZLSFGX b where b.ZLXMID = a.ZLXMID)
  and a.QYRQ00 is not null and a.QYRQ00<=to_char(sysdate,'yyyymmdd')
  and ((a.TZRQ00 is null) or (a.TZRQ00>to_char(sysdate,'yyyymmdd')))
  and a.ZLXMID>=100 and a.SFYXSQ='Y' and a.LBBZ00='1'