CREATE OR REPLACE PROCEDURE SP_XK_XYJXCH(
  PKSRQ00 in varchar2,           --开始日期
  PJSRQ00 in varchar2,           --结束日期
  PYYID00 in varchar2 default '' --医院院区
)
as
-- Person      Date        Comments
-- wusd        2019.04.03  create for SXGL9-20190401-003
-- wusd        2019.05.23  修改使用院区使用基础数据来源 for SXGL9-20190517-001
begin
  delete XK_XYJXCH;
  if nvl(PYYID00,' ')<>' ' then
    delete XK_XYJXCH_TMP;
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00,ZFJJE0)
      select XYPZMC,XX0000,RHXX00,XDBH00,RKDW00,DJ0000,RKSL00,JE0000,
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from VW_XK_XYRKMX X
      where RKRQ00<PKSRQ00
        and (PYYID00='-1'or exists(select 1 from BM_BMBM00 where BMBH00=X.XKBMBH and YYID00=PYYID00))
      union
      select XYPZMC,XX0000,RHXX00,XDBH00,CKDW00,DJ0000,-CKSL00,-JE0000*CKSL00/abs(CKSL00),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC),
             -CKSL00/abs(CKSL00)
              *(select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
                where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from VW_XK_XYCKMX X
      where CKRQ00<PKSRQ00
        and (PYYID00='-1'or exists(select 1 from BM_BMBM00 where BMBH00=X.XKBMBH and YYID00=PYYID00));
  else
    delete XK_XYJXCH_TMP;
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00,ZFJJE0)
      select XYPZMC,XX0000,RHXX00,XDBH00,RKDW00,DJ0000,RKSL00,JE0000,
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from XK_XYRK00 X
      where RKRQ00<PKSRQ00
      union
      select XYPZMC,XX0000,RHXX00,XDBH00,CKDW00,DJ0000,-CKSL00,-JE0000*CKSL00/abs(CKSL00),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC),
             -CKSL00/abs(CKSL00)
              *(select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
                where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from XK_XYCK00 X
      where CKRQ00<PKSRQ00;

  end if;
  --期初库存结存
  insert into XK_XYJXCH(XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,QCSL00,QCJE00,QCFJJE,QCZJE0)
    select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,JE0000 + ZFJJE0
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                sum(FJJE00*SL0000/abs(SL0000)) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00);
    /*select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,JE0000 + ZFJJE0
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                sum(ZFJJE0*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                            from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,count(*) CSL000
                                 from(select distinct XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,FJJE00
                                      from XK_XYJXCH_TMP)
                                 group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                           where XYPZMC=X.XYPZMC and XX0000=X.XX0000 and RHXX00=X.RHXX00
                             and DW0000=X.DW0000 and DJ0000=X.DJ0000 and FJJE00=X.FJJE00)) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00);  */
  --统计进库信息
  delete XK_XYJXCH_TMP;
  if nvl(PYYID00,' ')<>' ' then
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00)
      select XYPZMC,XX0000,RHXX00,XDBH00,RKDW00,DJ0000,RKSL00,JE0000,
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC) FJJE00
      from VW_XK_XYRKMX X
      where RKRQ00>=PKSRQ00 and RKRQ00<=PJSRQ00
        and (PYYID00='-1'or exists(select 1 from BM_BMBM00 where BMBH00=X.XKBMBH and YYID00=PYYID00));
  else
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00)
      select XYPZMC,XX0000,RHXX00,XDBH00,RKDW00,DJ0000,RKSL00,JE0000,
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC) FJJE00
      from XK_XYRK00 X
      where RKRQ00>=PKSRQ00 and RKRQ00<=PJSRQ00;
  end if;
  --更新入库信息
  update XK_XYJXCH B
  set (RKSL00,RKJE00,RKFJJE,RKZJE0)=(select nvl(B.RKSL00,0)+nvl(SL0000,0),nvl(B.RKJE00,0)+nvl(JE0000,0),
                                            nvl(B.RKFJJE,0)+nvl(ZFJJE0,0),nvl(B.RKZJE0,0)+nvl(JE0000,0)+nvl(ZFJJE0,0)
                                     from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                                                 sum(FJJE00) ZFJJE0
                                          from XK_XYJXCH_TMP X
                                          group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                     where XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                                       and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00)
  where exists(select 1 from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000
                             from XK_XYJXCH_TMP
                             group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
               where (SL0000<>0 or JE0000<>0)
                 and XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                 and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00);
  /*update XK_XYJXCH B
  set (RKSL00,RKJE00,RKFJJE,RKZJE0)=(select nvl(B.RKSL00,0)+nvl(SL0000,0),nvl(B.RKJE00,0)+nvl(JE0000,0),
                                            nvl(B.RKFJJE,0)+nvl(ZFJJE0,0),nvl(B.RKZJE0,0)+nvl(JE0000,0)+nvl(ZFJJE0,0)
                                     from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                                                 sum(FJJE00*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                                                             from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,count(*) CSL000
                                                                  from(select distinct XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,FJJE00
                                                                       from XK_XYJXCH_TMP)
                                                                  group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                                             where XYPZMC=X.XYPZMC and XX0000=X.XX0000 and RHXX00=X.RHXX00
                                                               and DW0000=X.DW0000 and DJ0000=X.DJ0000 and FJJE00=X.FJJE00)) ZFJJE0
                                          from XK_XYJXCH_TMP X
                                          group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                     where XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                                       and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00)
  where exists(select 1 from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000
                             from XK_XYJXCH_TMP
                             group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
               where (SL0000<>0 or JE0000<>0)
                 and XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                 and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00);*/
  --插入进库信息
  insert into XK_XYJXCH(XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,RKSL00,RKJE00,RKFJJE,RKZJE0)
    select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,ZFJJE0+JE0000
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                sum(FJJE00) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00) T
    where not exists(select 1 from XK_XYJXCH
                     where XYPZMC=T.XYPZMC and XX0000=T.XX0000 and RHXX00=T.RHXX00
                       and DW0000=T.DW0000 and DJ0000=T.DJ0000 and FJJE00=T.FJJE00);
    /*select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,ZFJJE0+JE0000
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000,
                sum(FJJE00*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                            from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,count(*) CSL000
                                 from(select distinct XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,FJJE00
                                      from XK_XYJXCH_TMP)
                                 group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                            where XYPZMC=X.XYPZMC and XX0000=X.XX0000 and RHXX00=X.RHXX00
                              and DW0000=X.DW0000 and DJ0000=X.DJ0000 and FJJE00=X.FJJE00)) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00) T
    where not exists(select 1 from XK_XYJXCH
                     where XYPZMC=T.XYPZMC and XX0000=T.XX0000 and RHXX00=T.RHXX00
                       and DW0000=T.DW0000 and DJ0000=T.DJ0000 and FJJE00=T.FJJE00);*/
  --统计出库信息
  delete XK_XYJXCH_TMP;
  if nvl(PYYID00,' ')<>' ' then
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00,ZFJJE0)
      select XYPZMC,XX0000,RHXX00,XDBH00,CKDW00,DJ0000,-CKSL00,-JE0000*CKSL00/abs(CKSL00),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC) FJJE00,
             -CKSL00/abs(CKSL00)
              *(select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
                where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from VW_XK_XYCKMX X
      where CKRQ00>=PKSRQ00 and CKRQ00<=PJSRQ00
        and (PYYID00='-1'or exists(select 1 from BM_BMBM00 where BMBH00=X.XKBMBH and YYID00=PYYID00));
  else
    --统计出库信息
    insert into XK_XYJXCH_TMP(XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,SL0000,JE0000,FJJE00,ZFJJE0)
      select XYPZMC,XX0000,RHXX00,XDBH00,CKDW00,DJ0000,CKSL00,JE0000*CKSL00/abs(CKSL00),
             (select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
              where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC) FJJE00,
             -CKSL00/abs(CKSL00)
              *(select FJJE00 from BM_XYXX00 T,BM_TYZD00 W
                where T.NBBH00=W.NBBH00 and W.ZDMC00='血液品种' and W.MC0000=X.XYPZMC)
      from XK_XYCK00 X
      where CKRQ00>=PKSRQ00 and CKRQ00<=PJSRQ00;
  end if;
  --更新出库信息
  update XK_XYJXCH B
  set (CKSL00,CKJE00,CKFJJE,CKZJE0)=(select nvl(B.CKSL00,0)+nvl(SL0000,0),nvl(B.CKJE00,0)+nvl(JE0000,0),
                                            nvl(B.CKFJJE,0)+nvl(ZFJJE0,0),nvl(B.CKZJE0,0)+nvl(JE0000,0)+nvl(ZFJJE0,0)
                                     from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,abs(sum(SL0000)) SL0000,abs(sum(JE0000)) JE0000,
                                                 sum(ZFJJE0*SL0000/abs(SL0000)) ZFJJE0
                                          from XK_XYJXCH_TMP X
                                          group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                     where XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                                       and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00)
  where exists(select 1 from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000
                             from XK_XYJXCH_TMP
                             group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
               where (SL0000<>0 or JE0000<>0)
                 and XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                 and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00);
 /* update XK_XYJXCH B
  set (CKSL00,CKJE00,CKFJJE,CKZJE0)=(select nvl(B.CKSL00,0)+nvl(SL0000,0),nvl(B.CKJE00,0)+nvl(JE0000,0),
                                            nvl(B.CKFJJE,0)+nvl(ZFJJE0,0),nvl(B.CKZJE0,0)+nvl(JE0000,0)+nvl(ZFJJE0,0)
                                     from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,abs(sum(SL0000)) SL0000,abs(sum(JE0000)) JE0000,
                                                 abs(sum(ZFJJE0*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                                                                 from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,count(*) CSL000
                                                                      from(select distinct XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,FJJE00
                                                                           from XK_XYJXCH_TMP)
                                                                      group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                                                 where XYPZMC=X.XYPZMC and XX0000=X.XX0000 and RHXX00=X.RHXX00
                                                                   and DW0000=X.DW0000 and DJ0000=X.DJ0000 and FJJE00=X.FJJE00))) ZFJJE0
                                          from XK_XYJXCH_TMP X
                                          group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                     where XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                                       and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00)
  where exists(select 1 from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,sum(SL0000) SL0000,sum(JE0000) JE0000
                             from XK_XYJXCH_TMP
                             group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
               where (SL0000<>0 or JE0000<>0)
                 and XYPZMC=B.XYPZMC and XX0000=B.XX0000 and RHXX00=B.RHXX00
                 and DW0000=B.DW0000 and DJ0000=B.DJ0000 and FJJE00=B.FJJE00);*/
  --插出进库信息
  insert into XK_XYJXCH(XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,CKSL00,CKJE00,CKFJJE,CKZJE0)
    select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,ZFJJE0+JE0000
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,abs(sum(SL0000)) SL0000,abs(sum(JE0000)) JE0000,
                sum(ZFJJE0*SL0000/abs(SL0000)) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00) T
    where not exists(select 1 from XK_XYJXCH
                     where XYPZMC=T.XYPZMC and XX0000=T.XX0000 and RHXX00=T.RHXX00
                       and DW0000=T.DW0000 and DJ0000=T.DJ0000 and FJJE00=T.FJJE00);
    /*select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,SL0000,JE0000,ZFJJE0,ZFJJE0+JE0000
    from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,abs(sum(SL0000)) SL0000,abs(sum(JE0000)) JE0000,
                abs(sum(ZFJJE0*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                                from(select XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00,count(*) CSL000
                                     from(select distinct XYPZMC,XX0000,RHXX00,XDBH00,DW0000,DJ0000,FJJE00
                                          from XK_XYJXCH_TMP)
                                     group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00)
                                where XYPZMC=X.XYPZMC and XX0000=X.XX0000 and RHXX00=X.RHXX00
                                  and DW0000=X.DW0000 and DJ0000=X.DJ0000 and FJJE00=X.FJJE00))) ZFJJE0
         from XK_XYJXCH_TMP X
         group by XYPZMC,XX0000,RHXX00,DW0000,DJ0000,FJJE00) T
    where not exists(select 1 from XK_XYJXCH
                     where XYPZMC=T.XYPZMC and XX0000=T.XX0000 and RHXX00=T.RHXX00
                       and DW0000=T.DW0000 and DJ0000=T.DJ0000 and FJJE00=T.FJJE00);*/
  --更新期末信息
  update XK_XYJXCH
  set QMSL00=nvl(QCSL00,0)+nvl(RKSL00,0)-nvl(CKSL00,0),
      QMJE00=nvl(QCJE00,0)+nvl(RKJE00,0)-nvl(CKJE00,0),
      QMFJJE=nvl(QCFJJE,0)+nvl(RKFJJE,0)-nvl(CKFJJE,0),
      QMZJE0=nvl(QCZJE0,0)+nvl(RKZJE0,0)-nvl(CKZJE0,0);
  commit;
exception
  when others then
     rollback;
    raise_application_error(-20011,substr(SQLERRM||'执行过程SP_XK_XYJXCH',1,240));
end;