PROMPT 门诊科室收入日报表（罗源集成报表使用）SP_SF_MZKSSR_RBB000_LY 
create or replace procedure SP_SF_MZKSSR_RBB000_LY 
(
        as_KSRQ00    IN    CHAR,       --开始日期 
        as_JSRQ00    IN    CHAR,       --结束日期  
        ad_ID0000    OUT   NUMBER      --统计报表对应的ID 
)
as
       ls_BMBH00    SF_FYMX00.KDBQ00%TYPE;    --部门编号
       ls_XMBH00    SF_FYMX00.XMBH00%TYPE;
       ls_HJJE00    SF_FYMX00.HJJE00%TYPE;
       ls_ZFJE00    SF_FYMX00.ZFJE00%TYPE;
       ls_JZJE00    SF_FYMX00.JZJE00%TYPE;
       ls_GFJE00    SF_FYMX00.GFJE00%TYPE;
       ls_BH0000    BM_FPXM00.BH0000%TYPE;
       SF_ZXTJKD    CHAR(1);
       ls_GHRC00    NUMBER(10);  
       ls_Count0    NUMBER(10);     
       CURSOR CUR_SF_MZKSSR_KDFYMX IS
          SELECT H.BH0000,A.KDKS00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                                          from SF_FYMX00 A,BM_BMBM00 B,SF_BRFY00 C,BM_BRXXB0 E,BM_YYSFXM G,BM_FPXM00 H,ic_ybbrlb d
                                         where A.KDKS00 = B.BMBH00
                                           and A.DJH000 = C.DJH000
                                           and C.BRID00 = E.BRID00
                                           and A.XMBH00 = G.SFXMID
                                           and G.MZFPID = H.FPXMID
                                       and d.fbbh00=e.fbbh00
                                       and d.yblb00=e.yblb00
                                           and C.CZRQ00 >=  as_KSRQ00
                                           and C.CZRQ00 <=  as_JSRQ00 
                                     group  by  H.BH0000,A.KDKS00; 
-- MODIFICATION HISTORY      
-- Person      Date          Comments
--  liuj     2012.09.12   create for MZSF-20120827-002
BEGIN  
    select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
    OPEN CUR_SF_MZKSSR_KDFYMX;
    LOOP 
       FETCH CUR_SF_MZKSSR_KDFYMX INTO ls_BH0000,ls_BMBH00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
       EXIT WHEN CUR_SF_MZKSSR_KDFYMX%NOTFOUND;  
       select count(*) into ls_Count0 from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=ls_BMBH00;
       if ls_Count0>0 then 
         SP_EXECUTE_SQL('Update BM_YYSFTJ Set SFXM'||ls_BH0000||'=NVL(SFXM'||ls_BH0000||',0)+'||to_char(ls_HJJE00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_HJJE00)||' where BMBH00='||to_char(ls_BMBH00)||' and ID0000='||to_char(ad_ID0000));
       else
          SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM'||(ls_BH0000)||',ID0000,XMHJ00)  values('||to_char(ls_BMBH00)||','||to_char(ls_HJJE00)||','||to_char(ad_ID0000)||','||to_char(ls_HJJE00)||')');
       end if; 
                            
    END LOOP;
    CLOSE CUR_SF_MZKSSR_KDFYMX;
    --增加挂号人次 SFXM99  
    update BM_YYSFTJ A  SET SFXM99=        
           ( SELECT COUNT(*) from SF_BRXXB0 B,BM_BRXXB0 C,IC_YBBRLB D 
                                       where B.BRID00 = C.BRID00
                                         and C.FBBH00 = D.FBBH00
                                         and C.YBLB00 = D.YBLB00                                                                                   
                                         and B.GHKS00 = A.BMBH00  
                                         and B.GHRQ00 >=  as_KSRQ00
                                         and B.GHRQ00 <=  as_JSRQ00                                         
                                         and THBZ00 <> '0')                                       
    where  ID0000 = ad_ID0000;                                 
    --添加一条总计行    
    Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06,SFXM07,SFXM08,SFXM09,SFXM10,
                          SFXM11,SFXM12,SFXM13,SFXM14,SFXM15,SFXM16,SFXM17,SFXM18,SFXM19,SFXM20,
                          SFXM21,SFXM22,SFXM23,SFXM24,SFXM25,SFXM26,SFXM27,SFXM28,SFXM29,SFXM30,SFXM98,SFXM99,XMHJ00)
      select ad_ID0000,999999999,sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05),sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10),
                          sum(SFXM11),sum(SFXM12),sum(SFXM13),sum(SFXM14),sum(SFXM15),sum(SFXM16),sum(SFXM17),sum(SFXM18),sum(SFXM19),sum(SFXM20),
                          sum(SFXM21),sum(SFXM22),sum(SFXM23),sum(SFXM24),sum(SFXM25),sum(SFXM26),sum(SFXM27),sum(SFXM28),sum(SFXM29),sum(SFXM30),
                          SUM(SFXM98),SUM(SFXM99),sum(XMHJ00) from BM_YYSFTJ  where  ID0000 = ad_ID0000;
    commit;
EXCEPTION
      WHEN OTHERS THEN
               -- as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                --as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZKSSR_RBB000('||as_KSRQ00||','||as_JSRQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                ROLLBACK;
END;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%