CREATE OR REPLACE PROCEDURE SP_YS_YBFYYZ
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2009.08.12  通过函数SF_YS_GETYBBZ取值写入医报提示字段BZ0000
-- CSF         2010.04.30  BM_YBFYYZ表增加记录字段YBXMBH
-- CSF         2010.05.07  YBXMBH取为空值。
-- zhr         2010.05.12  做了优化，增加中间表BM_YBFYYZ_TEMP01
-- zhangwz     2012.03.16  增加对农保对照信息字段的update by MZYS-20120316-003.
-- zhangwz     2012.03.21  对农保对照信息的update 从 SP_YS_NBDZXXUPDATE(1)-->SP_YS_NBDZXXUPDATE(2);  by MZYS-20120320-001.
-- linzy       2014.04.01  增加分院信息fyid00 by XMGL-20140319-001
-- dsm         2014.04.17  add JCLBID 把BM_ZLZD00.JCLBID也整理到BM_YBFYYZ for 归档 MZYS-20140307-001
-- dsm         2014.05.13  add JCMLID for XMGL-20140506-001
-- zhanghr     2015.01.15  优化 for YJ-20150109-001
-- csf         2015.03.27  未定价部分插入BM_YBFYYZ的数据 YBZXLB null->0 YBBRLB null->0 YJ-20150327-001
-- dsm         2015.03.31  BM_YBFYYZ.JCMLID for MZYS-20150330-001
-- dsm         2015.06.24  处理BM_YBFYYZ.BWBH00 for XMGL-20150624-001
-- nixj        2015.11.18  改BM_ZLZD00.YYID00与 BM_YBFYYZ.FYID00对应  LIS-20151118-002
-- dsm         2016.12.14  BM_YBFYYZ_TEMP00.GGBZTS扩成500 for MZYS-20161128-001
-- dsm         2021.10.11  BM_YBFYYZ改名成BM_YBZLXM for XMGL-20211011-001
-- ----------- --------    ------------------------------------------------------------------------------
Vcounter  number(10);
BEGIN
  SP_EXECUTE_SQL('TRUNCATE TABLE BM_YBFYYZ_TEMP01');
  insert into BM_YBFYYZ_TEMP01(ZLXMID,TCJE00,XMJE00,SFCS00,ZFBL00,FBBH00,YBZXLB,YBBRLB,YBXMBH)
    select
        distinct
        a.ZLXMID,  --项目类别ID
        CC.TCJE00,
        SF_GETXMJE(bb.SFYP00,bb.SFXMID,bb.YYDJ00,0,'0','0') XMJE00,
        cc.SFCS00,
        bb.ZFBL00,
        bb.FBBH00, --费别编号
        bb.YBZXLB, --医保中心类别
     --   bb.YBBRLB,
        'Z',
        bb.YBXMBH
    from  VW_YS_YXSQZL a, VW_BM_YYSFXM aa, BM_YBSFDY bb, XT_ZLSFGX cc
    where bb.SFXMID = aa.SFXMID and cc.SFXMID = aa.SFXMID and
        cc.ZLXMID = a.ZLXMID and bb.SFYP00 = 'N';
  commit;

  SP_EXECUTE_SQL('TRUNCATE TABLE BM_YBFYYZ_TEMP00');
  insert  into BM_YBFYYZ_TEMP00 (ZLXMID,FBBH00,YBZXLB,YBLBMC,YBBRLB,SFJE00,ZFBL00,SFSFXM,YBXMBH)
    select
      a.ZLXMID,--项目类别ID
      a.FBBH00,--费别编号
      a.YBZXLB,--医保中心类别
      decode(sum(a.XMJE00 * a.SFCS00),0,'非医保',null,'非医保',decode(Round(sum(a.XMJE00 * a.SFCS00 * a.ZFBL00)/sum(a.XMJE00 * a.SFCS00),2),0,'医保',1,'非医保','部分医保')) YBLBMC,
      a.YBBRLB,--医保病人类别
      sum(NVL(a.TCJE00,a.XMJE00) * a.SFCS00) SFJE00,--单价
      decode(sum(NVL(a.TCJE00,a.XMJE00) * a.SFCS00),0,0,null,0,to_char(Round(sum(NVL(a.TCJE00,a.XMJE00)  * a.SFCS00 * a.ZFBL00)/sum(NVL(a.TCJE00,a.XMJE00) * a.SFCS00),2))*100)||'%' ZFBL00,--自付比例
      'Y' SFSFXM,--是否收费项目
--      substrb(SF_YS_GETYBBZ(a.ZLXMID,a.FBBH00,a.YBZXLB),1,100), --医保提示
      null YBXMBH  --bb.YBXMBH --医保细目编号
    from  BM_YBFYYZ_TEMP01 a
    group by a.ZLXMID, --项目类别ID
         a.FBBH00,
         a.YBZXLB,--医保中心类别
         a.YBBRLB --医保病人类别
        -- bb.YBXMBH
  ;
  commit;
  update BM_YBFYYZ_TEMP00 a set BZ0000=substrb(SF_YS_GETYBBZ(a.ZLXMID,a.FBBH00,a.YBZXLB),1,100),COLOUR=decode(ZFBL00,'100%','$0000FF','');
  update BM_YBFYYZ_TEMP00 a set GGBZTS=substrb(SF_YJ_GETYBGGBZTS(a.ZLXMID,a.FBBH00,a.YBZXLB),1,500);
  commit;
  insert into BM_YBFYYZ_TEMP00 (ZLXMID,FBBH00,YBZXLB,YBLBMC,YBBRLB,SFJE00,ZFBL00,SFSFXM,YBXMBH)
     select
           ZLXMID,--项目类别ID
           1 FBBH00,
           '0' YBZXLB,--医保中心类别
           '非收费' YBLBMC,--
           'Z' YBBRLB,--医保病人类别
           0 SFJE00,--单价
           null ZFBL00,--自付比例
           'N' SFSFXM,--是否收费项目
           null YBXMBH --医保细目编号
     from   VW_YS_YXSQZL
     where not exists (select ZLXMID from XT_ZLSFGX where ZLXMID = VW_YS_YXSQZL.ZLXMID);
  commit;
  update BM_YBFYYZ_TEMP00 a set (LBBH00,ZLXMJC,ZLXMBH,ZYZBID,PYSM00,WBSM00,
                                 BH0000,DW0000,JCLBID,JCMLID,FYID00,ZYMZBZ,BWBH00)=
                           (select b.LBBH00,b.ZLXMJC,b.ZLXMBH,b.ZYZBID,b.PYSM00,b.WBSM00,
                                   b.BH0000,b.DW0000,nvl(b.JCLBID,0),nvl(b.JCMLID,0),nvl(b.FYID00,'0'),b.ZYMZBZ,b.BWBH00
                            from BM_ZLZD00 b where b.ZLXMID=a.ZLXMID);
  commit;
  select count(*) into Vcounter from BM_YBFYYZ_TEMP00 where rownum=1;
  if Vcounter>0 then
     SP_EXECUTE_SQL('TRUNCATE TABLE BM_YBZLXM');
     insert into BM_YBZLXM(ZLXMID,LBBH00,ZLXMJC,ZLXMBH,ZYZBID,PYSM00,WBSM00,BH0000,DW0000,
                           FBBH00,YBZXLB,YBLBMC,YBBRLB,SFJE00,ZFBL00,SFSFXM,BZ0000,YBXMBH,
                           FYID00,JCLBID,ZYMZBZ,GGBZTS,COLOUR,JCMLID,BWBH00)
                    select ZLXMID,LBBH00,ZLXMJC,ZLXMBH,ZYZBID,PYSM00,WBSM00,BH0000,DW0000,
                           FBBH00,YBZXLB,YBLBMC,YBBRLB,SFJE00,ZFBL00,SFSFXM,BZ0000,YBXMBH,
                           FYID00,JCLBID,ZYMZBZ,GGBZTS,COLOUR,JCMLID,BWBH00 from BM_YBFYYZ_TEMP00;
     commit;
     select count(*) into Vcounter from BM_YBZLXM where rownum=1;  --BM_YBFYYZ
     if Vcounter>0 then
        SP_EXECUTE_SQL('TRUNCATE TABLE BM_YBFYYZ_TEMP00');
        SP_EXECUTE_SQL('TRUNCATE TABLE BM_YBFYYZ_TEMP01');
        commit;
     end if;
  end if;
  SP_YS_NBDZXXUPDATE(2);

  commit;
END;