PROCEDURE SP_SF_XMFYCX 
(
     as_KSRQ00    in   char,
     as_JSRQ00    in   char,
     as_KDKS00    in   char,
     as_JSFS00    in   char default '-1', --  -1: 全部  0：未结算 1：已结算
     as_FYTJ00    in   char default '-1', --  -1: 全部  0：费用  1：材料
     as_ZXYS00    in   char default '-1', --全部
     ad_ID0000    out  number,
     as_YHMSG0    out  varchar,
     as_SYSMSG    out  varchar
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2011.09.01  create; 
-- zhangyc     2012.04.13  by MZSF-20120413-001 增加入参执行医生
-- zhangyc     2014.05.22  增加门诊统计标志条件and nvl(c.ZBLB00,'0')='0' by  MZSF-20140422-002
as     
BEGIN
    Select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
 
 
   ---GHRQ00:项目名称 BEIZHU：核算项目名称  BEIZH1:项目规格 BEIZH2：项目单位 GHKS100：项目单价 ，GHKS101：项目数量  GHKS102：项目金额
   insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00)  
    select
      ad_ID0000,
      Trim(A.XMMC00)XMMC00,  --项目名称
      D.HSXMID,
      D.HSXMMC,--核算项目名,
      trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)) as  XMGG00,    --项目规格
      --trim(C.GG0000) as  XMGG00,
      --trim(c.CWNR00)as  XMGG00,
      trim(C.DW0000) DW0000 ,      --单位
      round(sum(A.HJJE00)/decode(sum(A.XMSL00),0,1,sum(A.XMSL00)),2) XMDJ00 ,      --单价
      sum(A.XMSL00)XMSL00 ,  --数量
      sum(A.HJJE00)HJJE00 ,
      0       
from  SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,VW_BM_YJHSXM D
where A.DJH000 = B.DJH000
  and A.XMBH00 = C.SFXMID
  and  C.HSXMID = d.HSXMID
  and (A.XMBH00 < 9999999990  or  JMBZ00 = '3')  --增加药费的事后减免
  and  B.czrq00>=as_KSRQ00
  and  B.czrq00<=as_JSRQ00
  and  (a.zxks00 =as_KDKS00 or as_KDKS00='-1')
  and  (a.zxys00 =as_ZXYS00 or as_ZXYS00='-1')
  and  ((AS_JSFS00='0' and b.jzdh00=0) or (AS_JSFS00='1' and b.jzdh00>0)  or (AS_JSFS00='-1' and 1=1))
  group by ad_ID0000,A.XMMC00,D.HSXMID,D.HSXMMC,
  trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)),C.DW0000;
  --C.GG0000, C.DW0000;
 --c.CWNR00, C.DW0000;

 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00)    
 select
      ad_ID0000,
      Trim(C.YPMC00),
      F.HSXMID,    
      F.HSXMMC ,
      --C.YPGG00  ,
      E.CWNR00,   
      C.CFDW00  ,    
      C.LSDJ00  ,  
      (DECODE(C.SFZBY0,'Y',0,C.YPZSL0)),  
      (round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100)HJJE00,
      0
from  SF_BRFY00 A,yf_mzcf00 B,yf_mzcfmx C,BM_YPLB00 D,BM_YYSFXM E,VW_BM_YJHSXM F 
where  A.DJH000 = B.DJH000
  and  B.cflsh0 = C.cflsh0
  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  e.HSXMID = f.HSXMID
  and  a.czrq00>=as_KSRQ00
  and  a.czrq00<=as_JSRQ00
  --and  (b.SSKS00 =as_KDKS00 or as_KDKS00='-1')
  and  b.djh000 in  (select djh000 from sf_fymx00 where ( zxks00 =as_KDKS00 or as_KDKS00='-1') and  (zxys00 =as_ZXYS00 or as_ZXYS00='-1')
                     and  czrq00>=as_KSRQ00 and czrq00<=as_JSRQ00 and XMBH00 > 9999999990
                     )
  and  ((AS_JSFS00='0' and a.jzdh00=0) or (AS_JSFS00='1' and a.jzdh00>0) or (AS_JSFS00='-1' and 1=1) );

  
  ---材料的账簿类别就包含材料收入和其他收入
  insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)  
 select ID0000,GHRQ00,c.bh0000,c.xmmc00,BEIZH1,BEIZH2,GHKS100,sum(GHKS101),sum(GHKS102),1,0
 from BM_GHKSTJ a,BM_CWXMDY b ,BM_CWXMBM c
   where a.GHKS99=b.HSXMID and b.bh0000=c.BH0000 and  id0000=ad_ID0000 and DYID00=0 and nvl(c.ZBLB00,'0')='0'
    AND ((as_FYTJ00='-1' AND 1=1) OR (as_FYTJ00='0' AND  c.bh0000 NOT IN ('09','10')) OR (as_FYTJ00='1' AND  c.bh0000 IN  ('09','10')))
 group by ID0000,GHRQ00,c.bh0000,c.xmmc00,BEIZH1,BEIZH2,GHKS100;
 
 insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00,GHKS88)values(ad_ID0000,'  ','     ',1,0.1)  ;
 insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00,GHKS88)values(ad_ID0000,'项目统计：  ','         ',1,0.2)  ; 
  
 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)  
 select ID0000,'    ',GHKS99,BEIZHU,null,null,null,null,sum(GHKS102),1,2
 from BM_GHKSTJ where id0000=ad_ID0000 and GHKS88=0 and DYID00=1
 group by ID0000,GHKS99,BEIZHU; 
 
insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)  
 select ID0000,'   ',99999999,'合计',null,null,null,null,sum(GHKS102),1,2
 from BM_GHKSTJ where id0000=ad_ID0000 and GHKS88=0 and DYID00=1
 group by ID0000; 
 
 
 --删除临时数据
 delete BM_GHKSTJ where id0000=ad_ID0000 and DYID00=0;


--commit;      
exception
      when others then
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_XMFYCX('||as_KSRQ00||','||as_JSRQ00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
        rollback;
end;