prompt 167  广州公医病人费用信息 SP_SF_GYBRFYBB_RZ0000
CREATE OR REPLACE PROCEDURE SP_SF_GYBRFYBB_RZ0000
(ad_BRID00    in  number,
 ad_XM0000    in  CHAR,
 ad_JZDH00    in  number,
 ad_CZY000    in  number,
 as_CZYXM0    in  CHAR,
 as_SXH000    in  char,
 as_JKDH00    in  NUMBER,
 as_YHMSG0    OUT   VARCHAR,      --存储过程提示的错误信息
 as_SYSMSG    OUT   VARCHAR       --系统提示的错误信息
)
as
ls_CZRQ00   CHAR(8);
lv_ZYF000 SF_BRFY00.HJJE00%TYPE;
lv_XYF000 SF_BRFY00.HJJE00%TYPE;
lv_ZLF000 SF_BRFY00.HJJE00%TYPE;
lv_JCF000 SF_BRFY00.HJJE00%TYPE;
lv_CWF000 SF_BRFY00.HJJE00%TYPE;
lv_HLF000 SF_BRFY00.HJJE00%TYPE;
lv_SSF000 SF_BRFY00.HJJE00%TYPE;
lv_SXF000 SF_BRFY00.HJJE00%TYPE;
lv_GJF000 SF_BRFY00.HJJE00%TYPE;
lv_HYF000 SF_BRFY00.HJJE00%TYPE;
lv_ZCF000 SF_BRFY00.HJJE00%TYPE;
lv_GRZFJE SF_BRFY00.HJJE00%TYPE;
lv_JZJE00 SF_BRFY00.HJJE00%TYPE;
lv_ZFBL00 SF_FYMX00.ZFBL00%TYPE;
lv_GYZFJE SF_BRFY00.HJJE00%TYPE;
lv_GYJZJE SF_BRFY00.HJJE00%TYPE;
lv_HJJE00 SF_BRFY00.HJJE00%TYPE;
ls_zdmc00 YS_BRZDXX.zdmc00%type;
lv_BRGFZH bm_brxxb0.BRGFZH%type;
lv_XKH000 BM_YGBM00.XKH000%TYPE;
lv_sxh000 SF_GYBB00.sxh000%TYPE;
begin
	ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
	begin
		select BRGFZH into lv_BRGFZH from bm_brxxb0 where brid00=ad_BRID00;
			EXCEPTION
           WHEN OTHERS THEN
           lv_BRGFZH :='';
  end;
  begin
	select e.zdmc00 into ls_zdmc00 from YS_BRZDXX e,sf_brxxb0 f where e.ghid00=f.ghid00 and f.brid00=ad_BRID00 and e.ZZDBZ0='3' and rownum=1; 
	EXCEPTION
           WHEN OTHERS THEN
           ls_zdmc00 :='';
  end;

     select sum(decode(xmbh00,2,xmje00,3,xmje00,0)) ZYF000,sum(decode(xmbh00,1,xmje00,0)) XYF000,sum(decode(xmbh00,12,xmje00,0)) ZLF000, 
            sum(decode(xmbh00,8,decode(sign(xmje00-300),-1,xmje00,0),0)) JCF000,sum(decode(xmbh00,5,xmje00,0))  CWF000, 
            sum(decode(xmbh00,7,xmje00,0)) HLF000,sum(decode(xmbh00,13,xmje00,0)) SSF000,sum(decode(xmbh00,17,xmje00,0)) SXF000,sum(decode(xmbh00,8,decode(sign(xmje00-300),1,xmje00,0),0)) GJF000, 
            sum(decode(xmbh00,11,xmje00,0)) HYF000,sum(decode(xmbh00,6,xmje00,0)) ZCF000,
            sum(ROUND(xmje00*zfbl00,2)) GRZFJE,sum(round(xmje00*(1-zfbl00),2)) JZJE00,round(sum(ROUND(xmje00*zfbl00,2))/sum(hjje00),2) ZFBL00, 
            round(sum(round(xmje00*(1-zfbl00),2))*round(sum(hjje00)/sum(ROUND(xmje00*zfbl00,2)),2),2)/100 GYZFJE, 
            sum(round(xmje00*(1-zfbl00),2))-(round(sum(round(xmje00*(1-zfbl00),2))*round(sum(hjje00)/sum(ROUND(xmje00*zfbl00,2)),2),2)/100) GYJZJE,SUM(XMJE00) HJJE00
            into lv_ZYF000,lv_XYF000,lv_ZLF000,lv_JCF000,lv_CWF000,lv_HLF000,lv_SSF000,lv_SXF000,lv_GJF000,lv_HYF000,lv_ZCF000,lv_GRZFJE,lv_JZJE00,lv_ZFBL00,lv_GYZFJE,lv_GYJZJE,lv_HJJE00
     from sf_jzmx00 a,sf_jzb000 b,sf_pjsyqk d,bm_brxxb0 c,(select  f.brid00,f.ghid00,e.zdmc00 from YS_BRZDXX e,sf_brxxb0 f where e.ghid00=f.ghid00 and e.ZZDBZ0='3' and rownum=1) e  
   where a.jzdh00=b.jzdh00 and b.brid00=c.brid00 and b.pjh000=d.pjh000 and b.brid00=e.brid00(+) and b.jzdh00=ad_JZDH00 and c.fbbh00=7;

    if as_JKDH00<>0 then
       update SF_GYBB00 set ZYF000=ZYF000+nvl(lv_ZYF000,0),XYF000=XYF000+nvl(lv_XYF000,0),ZLF000=ZLF000+nvl(lv_ZLF000,0),JCF000=JCF000+nvl(lv_JCF000,0),
                            CWF000=CWF000+nvl(lv_CWF000,0),HLF000=HLF000+nvl(lv_HLF000,0),SSF000=SSF000+nvl(lv_SSF000,0),SXF000=SXF000+nvl(lv_SXF000,0),
                            GJF000=GJF000+nvl(lv_GJF000,0),HYF000=HYF000+nvl(lv_HYF000,0),ZCF000=ZCF000+nvl(lv_ZCF000,0),GRZFJE=GRZFJE+nvl(lv_GRZFJE,0),
                            JZHJ00=JZHJ00+nvl(lv_JZJE00,0),ZFBL00=ZFBL00+nvl(lv_ZFBL00,0),GFZFJE=GFZFJE+nvl(lv_GYZFJE,0),                            
                            GFJZJE=GFJZJE+nvl(lv_GYJZJE,0),HJJE00=HJJE00+nvl(lv_HJJE00,0)
                     where  SXH000=as_SXH000 AND BRID00=ad_BRID00 AND CZY000=ad_CZY000;
    else
       -- SELECT XKH000 into lv_XKH000 FROM BM_YGBM00 WHERE YGBH00=ad_CZY000;
       -- lv_sxh000 :=as_SXH000||to_char(sysdate,'YYYYMMDD')||lv_XKH000;  
       insert into SF_GYBB00(YYBH00,SXH000,RQ0000,XM0000,YLZH00,JZDM00,JKDH00,ZD0000,ZYF000,XYF000,ZLF000,JCF000,CWF000,HLF000,SSF000,SXF000,GJF000,HYF000,ZCF000,GRZFJE,JZhj00,ZFBL00,GFZFJE,GFJZJE,HJJE00,brid00,CZY000) 
      -- select --YYBH00,as_SXH000,RQ0000,XM0000,GYZH00,JZDM00,jkdid0,jzdh00,ZYF000,XYF000,ZLF000,JCF000,CWF000,HLF000,SSF000,SXF000,GJF000,HYF000,ZCF000,zdmc00,JZJE00,ZFBL00,GRZFJE,GYZFJE,GYJZJE,HJJE00 from (
       select '90002' YYBH00,as_SXH000 SXH000,ls_CZRQ00 RQ0000,ad_XM0000 XM0000,lv_BRGFZH,'0' JZDM00,0,ls_zdmc00,
       sum(decode(xmbh00,2,xmje00,3,xmje00,0))  ZYF000,sum(decode(xmbh00,1,xmje00,0)) XYF000,sum(decode(xmbh00,12,xmje00,0)) ZLF000, 
       sum(decode(xmbh00,8,decode(sign(xmje00-300),-1,xmje00,0),0)) JCF000,sum(decode(xmbh00,5,xmje00,0))  CWF000, 
      sum(decode(xmbh00,7,xmje00,0)) HLF000,sum(decode(xmbh00,13,xmje00,0)) SSF000,sum(decode(xmbh00,17,xmje00,0)) SXF000,sum(decode(xmbh00,8,decode(sign(xmje00-300),1,xmje00,0),0)) GJF000, 
      sum(decode(xmbh00,11,xmje00,0)) HYF000,sum(decode(xmbh00,6,xmje00,0)) ZCF000, 
      sum(ROUND(xmje00*zfbl00,2)) GRZFJE,sum(round(xmje00*(1-zfbl00),2)) JZJE00,round(sum(ROUND(xmje00*zfbl00,2))/sum(hjje00),2) ZFBL00, 
      round(sum(round(xmje00*(1-zfbl00),2))*round(sum(hjje00)/sum(ROUND(xmje00*zfbl00,2)),2),2)/100 GYZFJE, 
      sum(round(xmje00*(1-zfbl00),2))-(round(sum(round(xmje00*(1-zfbl00),2))*round(sum(hjje00)/sum(ROUND(xmje00*zfbl00,2)),2),2)/100) GYJZJE,SUM(XMJE00) HJJE00,ad_BRID00,ad_CZY000 
     from sf_jzmx00 a,sf_jzb000 b,sf_pjsyqk d,bm_brxxb0 c
     where a.jzdh00=b.jzdh00 and b.brid00=c.brid00 and b.pjh000=d.pjh000  and b.jzdh00=ad_JZDH00
     and c.fbbh00=7;
    -- group by C.BRGFZH,e.zdmc00,ad_BRID00
  end if;
  
EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_GYBRFYBB_RZ0000('||TO_CHAR(ad_BRID00)||','||ad_XM0000||','||TO_CHAR(ad_JZDH00)||','||TO_CHAR(ad_CZY000)||',as_YHMSG0,as_SYSMSG',1,150);
		ROLLBACK;  
end;
/
show error;