CREATE OR REPLACE PROCEDURE SP_ZY_FYJZCY_GJJ   
( 
   ad_rq0000     in  char
)
as
   ls_SQRQ00       BQ_TJSJ00.TJRQ00%TYPE;   --上期日期
--2015.01.06  daihq  中药房（部门编号298）数据分为成药和草药两部分 for JCBB-20141211-001
BEGIN
    --对账公式:本期记账收入=本期发药收入+本期差额 ;上期累计未接收药品+本期差额=本期累计未接收药品  
    delete from BQ_TJSJ00 where ZBXZ00=777 and TJRQ00=ad_rq0000;
    select nvl(max(TJRQ00),'20110930') into ls_SQRQ00 from BQ_TJSJ00 where ZBXZ00=777;
    --记账收入
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,1,'本期记账收入(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.gjdj00*b.ypzsl0,2))cfzje0,' ' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where  a.cflsh0=b.cflsh0 and a.jzrq00>ls_SQRQ00 and a.jzrq00<=ad_rq0000 
            and a.jzrq00>='20111001' and a.cfzt00<>'3' and a.yfbmbh<>298
            group by a.yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2)) cfzje0,' ' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20111001' and qlzt00<>'3' and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.gjdj00*b.ypzsl0,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where  a.cflsh0=b.cflsh0 and a.jzrq00>ls_SQRQ00 and a.jzrq00<=ad_rq0000 
            and a.jzrq00>='20111001' and a.cfzt00<>'3' and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by a.yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2)) cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20111001' and qlzt00<>'3' 
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.gjdj00*b.ypzsl0,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where  a.cflsh0=b.cflsh0 and a.jzrq00>ls_SQRQ00 and a.jzrq00<=ad_rq0000 
            and a.jzrq00>='20111001' and a.cfzt00<>'3' and a.yfbmbh=298 and a.YPDLBH='2'   --草药
            group by a.yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2)) cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20111001' and qlzt00<>'3' 
            and yfbmbh=298 and YPDLBH='2'   --草药
            group by yfbmbh) group by yfbmbh,YPLBMC;
    --发药收入
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,2,'本期发药收入(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,' ' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where a.cflsh0=b.cflsh0 and  fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and fyrq00>='20111001' and cfzt00<>'3' and a.yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,' ' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20111001' and qlzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where a.cflsh0=b.cflsh0 and  fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and fyrq00>='20111001' and cfzt00<>'3' 
            and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20111001' and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b where a.cflsh0=b.cflsh0 and  fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and fyrq00>='20111001' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20111001' and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh) group by yfbmbh,YPLBMC;


    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,3,'本期结算还未接收(出院带药)(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,' ' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 
            and jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 
            and jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 
            and jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH='2'  --草药
            group by yfbmbh) group by yfbmbh,YPLBMC ;

    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,4,'上期结算本期接收(出院带药)(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,' ' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00>='20111001' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00>='20111001' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00>='20111001' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH='2'  --草药
            group by yfbmbh) group by yfbmbh,YPLBMC;

    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,5,'本期结算还未接收(科室药品)(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,' ' YPLBMC
            from yf_zycf00  a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and  jzrq00>ls_SQRQ00 
            and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,' ' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and qlzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00  a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and  jzrq00>ls_SQRQ00 
            and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00 a,yf_zycfmx b  where a.cflsh0=b.cflsh0 and  jzrq00>ls_SQRQ00 
            and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh) group by yfbmbh,YPLBMC;
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,777,6,'上期结算本期接收(科室药品)(购进)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,' ' YPLBMC
            from yf_zycf00   a,yf_zycfmx b   where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,' ' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and qlzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_zycf00   a,yf_zycfmx b   where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(b.ypzsl0*b.gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_zycf00   a,yf_zycfmx b   where a.cflsh0=b.cflsh0 and fyrq00>ls_SQRQ00 
            and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and a.yfbmbh=298 and a.YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*gjdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh) group by yfbmbh,YPLBMC;
    --各药房差额本期差额
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select bmbh00,ad_rq0000,777,7,'本期差额(购进)',ZBSJ00,ZBSJ01
        from (select bmbh00,sum(decode(ZBBH00,3,zbsj00,5,zbsj00,4,-1*zbsj00,6,-1*zbsj00))ZBSJ00,ZBSJ01
              from BQ_TJSJ00 where TJRQ00=ad_rq0000 and ZBXZ00=777 group by bmbh00,ZBSJ01);
              
    --各药房各自累计结算未接收
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select bmbh00,ad_rq0000,777,8,'本期累计未接收药品(购进)',ZBSJ00,ZBSJ01
        from (select bmbh00,sum(ZBSJ00)ZBSJ00,ZBSJ01
              from BQ_TJSJ00 where ZBXZ00=777 and ((TJRQ00=ad_rq0000 and ZBBH00='7') 
              or (TJRQ00=ls_SQRQ00 and ZBBH00='8' and bmbh00<>0)) group by bmbh00,ZBSJ01);
    --全院汇总
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00)
      select 0,ad_rq0000,777,ZBBH00,ZBMC00,sum(ZBSJ00)
        from BQ_TJSJ00 where ZBXZ00=777 and TJRQ00=ad_rq0000 group by ad_rq0000,ZBBH00,ZBMC00;
    commit;    
END;
/