CREATE OR REPLACE PROCEDURE SP_ZY_FYJZCY  
(
   ad_rq0000     in varchar
)
as
   ls_SQRQ00       BQ_TJSJ00.TJRQ00%TYPE;   --上期日期
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- daihq     2015.01.06    中药房（部门编号298）数据分为成药和草药两部分 for JCBB-20141211-001   
BEGIN
    --对账公式:本期记账收入=本期发药收入+本期差额 ;上期累计未接收药品+本期差额=本期累计未接收药品  
    delete from BQ_TJSJ00 where ZBXZ00=17 and TJRQ00=ad_rq0000;
    select nvl(max(TJRQ00),'20080411') into ls_SQRQ00 from BQ_TJSJ00 where ZBXZ00=17;
    --记账收入
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select yfbmbh,ad_rq0000,17,1,'本期记账收入',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,' ' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' and qlzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'   --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and jzrq00>='20080412' 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,17,2,'本期发药收入',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,' ' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' and qlzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,'(成药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' and qlzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,2))cfzje0,'(草药)' YPLBMC
            from yf_yzypsq where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and fyrq00>='20080412' 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,17,3,'本期结算还未接收(出院带药)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz='5' and cfzt00<>'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,17,4,'上期结算本期接收(出院带药)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00>='20080412' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00>='20080412' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00>='20080412' and jzrq00<=ls_SQRQ00 and cfsrbz='5' and cfzt00<>'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,17,5,'本期结算还未接收(科室药品)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2'  --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where jzrq00>ls_SQRQ00 and jzrq00<=ad_rq0000 and fyrq00>ad_rq0000 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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,17,6,'上期结算本期接收(科室药品)',sum(cfzje0),YPLBMC
      from (select yfbmbh,sum(cfzje0)cfzje0,' ' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh<>298
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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(cfzje0)cfzje0,'(成药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH<>'2' --成药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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(cfzje0)cfzje0,'(草药)' YPLBMC
            from yf_zycf00 where fyrq00>ls_SQRQ00 and fyrq00<=ad_rq0000 and jzrq00<=ls_SQRQ00 and cfsrbz<>'5' and cfzt00<>'3'
            and yfbmbh=298 and YPDLBH='2'  --草药
            group by yfbmbh
            union all
            select yfbmbh,sum(round(ypzsl0*lsdj00,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,17,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=17 group by bmbh00,ZBSJ01);
              
    --各药房各自累计结算未接收
    insert into BQ_TJSJ00 (BMBH00,TJRQ00,ZBXZ00,ZBBH00,ZBMC00,ZBSJ00,ZBSJ01)
      select bmbh00,ad_rq0000,17,8,'本期累计未接收药品',ZBSJ00,ZBSJ01
        from (select bmbh00,sum(ZBSJ00)ZBSJ00,ZBSJ01
              from BQ_TJSJ00 where ZBXZ00=17 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,17,ZBBH00,ZBMC00,sum(ZBSJ00)
        from BQ_TJSJ00 where ZBXZ00=17 and TJRQ00=ad_rq0000 group by ad_rq0000,ZBBH00,ZBMC00;
    commit;
        --处理购进价报表
    sp_zy_fyjzcy_gjj(ad_rq0000);
    commit;
END;
/