create or replace  view VW_BM_ZLZDJG 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' 
;


--新
SELECT a.ZLXMID as hisCode, a.ZLXMJC as hisName, a.price as hisprice, d.order_code, d.order_content, d.price 
   FROM VW_BM_ZLZDJG a ,  EMR_MEDICAL_DICT b, EMERM.EMER_CHARGE_PACKAGE  d 
   WHERE A.ZLXMID=to_number(B.ORDER_CODE) and B.USE_FLAG in ('1','2') AND B.IS_VALID_APPLY='Y'
   and not exists(select 1 from EMR_MEDICAL_EXECUTE_DEPT where ORDER_CODE=B.ORDER_CODE) 
   and a.ZLXMID=d.ORDER_CODE  AND a.SFJE00!=d.price;
--旧   
SELECT distinct a.orderCode as hisCode, a.orderName as hisName, a.price as hisprice, d.order_code, d.order_content, d.price 
   FROM VW_OUTP_MEDICAL_ORDER_ZF_HTBL a ,  EMERM.EMER_CHARGE_PACKAGE  d WHERE a.ordercode=d.ORDER_CODE  AND a.PRICE!=d.price