-- Start of DDL Script for Procedure SD_HOSPITAL.SP_XM_YBDR_XLS
-- Generated 9-十月-2021 15:44:09 from SD_HOSPITAL@FW2

CREATE OR REPLACE 
PROCEDURE sp_xm_ybdr_xls
(

        ad_FBBH00    IN    NUMBER,     --费别编号
        ad_YBZXLB    IN    CHAR,       --医保中心类别
        ad_MLBH00    IN    CHAR,       --目录包含类型 '1':全部目录,'2'只包含药品,'3'只包含收费项目  --没用到
        ad_GXLX00    IN    CHAR,       --更新类型 '1':完整更新,'2'部分更新
        ad_CZY000    IN    NUMBER,     --操作员编码
        ad_GXHJLS    OUT   NUMBER,     --更新之后记录数
        as_YHMSG0    OUT   VARCHAR,    --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR     --系统提示的错误信息
)
as
       ls_COUNT0    NUMBER(5);
       ls_CZRQ00    CHAR(8);
       ls_CZSJ00    CHAR(8);
     
  cursor CUR_YBFPXM is
    select distinct ybfpxm from bm_ybypml_temp09 a where not exists
      (select 1 from bm_ybfpxm b where b.xmmc00=a.ybfpxm and YBZXLB=ad_YBZXLB);

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks        2010.08.30   Create
-- qks        2010.09.21   解决update bm_ybsfdy表速度慢问题,ad_MLBH00入参不再使用
-- zhangwz    2010.10.15   解决导入excel时如果是已经存在的医保编码时,没有导入zfbl00,YBLB00,SFYBXM,BZ0000相关信息.
-- zhangwz    2011.01.29   zfbl00的decode函数增加以下几种自付比例:非医保,甲类,乙类.by XMGL-20110129-001
-- wusq       2016.03.09   解决了修改excel中的生产厂家名称时，没有导入SCCJ00相关信息 by XMGL-20160302-001
-- dsm        2017.05.04   增加YBJSDJ,YBZGDJ for XMGL-20170502-001
-- dsm        2017.05.11   增加YBJSDJ1,YBZGDJ1 for XMGL-20170510-001
-- dsm        2017.12.11   陈曦修改归档 for XMGL-20171130-003
-- qiulf      2021.06.07   修改bm_ybypml_temp07.zfbl00 写法改成cast(replace(ZFBL00,'%','') as decimal(18,2))/100，因医保提供的自付比例超出了原来给定的DECODE范围
--                         因现在字段YBXMBH已改成VArchar2（50），修改substr(xmbh00||'                    ',1,20)=a.ybxmbh写法，并且注释掉length(trim(YBXMBH))=20 、length(trim(xmbh00))=12等 by 	XMGL-20210524-001
BEGIN
    ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
    ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');

    select count(1) into ls_COUNT0 from bm_ybypml_temp09 where rownum=1;
    if ls_COUNT0=0 then
       as_YHMSG0:='中间临时表bm_ybypml_temp09数据为空!';
       as_SYSMSG:=SQLERRM;
       rollback;
       return;
    end if;

    for C_YBFPXM in CUR_YBFPXM loop
      as_YHMSG0:=as_YHMSG0||C_YBFPXM.ybfpxm||',';
    end loop;
    if as_YHMSG0 is not null  then
        as_YHMSG0:='以下医保发票项目不存在于表bm_ybfpxm中:'||as_YHMSG0;
        as_SYSMSG:='请直接修改字段bm_ybypml_temp09.YBFPXM或者重新修改Excel文件后再导入！';
        rollback;
        return;
    end if;

    --1、从临时表bm_ybypml_temp09导入到bm_ybypml_temp07
    SP_EXECUTE_SQL('truncate table bm_ybypml_temp07');   --处理导入的excel编码与现有库的差集
    SP_EXECUTE_SQL('truncate table bm_ybypml_temp10');   --处理导入的excel编码与现有库的交集

    update bm_ybypml_temp09 set XMBH00=Trim(XMBH00);
    insert into bm_ybypml_temp07              --差集
    (XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ,
     SFYB00,
     ZFBL00,
     YBLB00,
     YBFPXM,
     SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00
    )
    select a.XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ,
      decode(SFYB00,'是','Y','N'),
       --round((ybzgdj-ybjsdj)/ybzgdj,2),--自付比例按公式计算
     /* Decode(sfyb00,'否',1,decode(zfbl00,'无',decode(yblb00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0,
      '1',1,null,0,'0',0,'.05',0.05,'.1',0.1,'.15',0.15,'.2',0.2,'.25',0.25,'.3',0.3,'.35',0.35,'.4',0.4,'.5',0.5,'.6',0.6, 
      '100%',1,'0%',0,'5%',0.05,'10%',0.1,'15%',0.15,'20%',0.2,'25%',0.25,'30%',0.3,'35%',0.35,'40%',0.4,'50%',0.5,'60%',0.6)),*/
      -- 2020.01.02 修改开始 
     -- Decode(SFYB00,'否',1,decode(ZFBL00,'无',decode(YBLB00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0,null,0,cast(replace(ZFBL00,'%','') as decimal(18,2))/100)),
        --2021.09.18 huangjh  自付比例增加一个REPLACE替换自付字眼 
        decode(SFYB00,'否',1,decode(ZFBL00,'无',decode(YBLB00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0,null,0,cast(replace(replace(ZFBL00,'%',''),'自付','')  as decimal(18,2))/100)) zfbl01,
      -- 2020.01.02 修改结束
      decode(SFYB00,'否',7,decode(YBLB00,'非医保',7,'甲类',4,'乙类',5)),
      B.BH0000,
      SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,a.YBJSDJ,a.YBZGDJ,a.YBJSDJ1,a.YBZGDJ1,a.XMDW01,a.CFBL00
    from bm_ybypml_temp09 a,bm_ybfpxm b where b.xmmc00=a.ybfpxm and b.YBZXLB=ad_YBZXLB and b.FBBH00=ad_FBBH00
      and a.xmbh00  in
      (
        select xmbh00 from bm_ybypml_temp09
        minus
        select trim(xmbh00) from bm_ybsfxm where YBZXLB=ad_YBZXLB and FBBH00=ad_FBBH00
       );

    insert into bm_ybypml_temp10              --交集
    (XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ,
     SFYB00,
     ZFBL00,
     YBLB00,
     YBFPXM,
     SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00
    )
    select a.XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ,
      decode(SFYB00,'是','Y','N'),
     -- round((ybzgdj-ybjsdj)/ybzgdj,2),--自付比例按公式计算
      /*decode(sfyb00,'否',1,decode(zfbl00,'无',decode(yblb00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0,
      '1',1,null,0,'0',0,'.05',0.05,'.1',0.1,'.15',0.15,'.2',0.2,'.25',0.25,'.3',0.3,'.35',0.35,'.4',0.4,'.5',0.5, '.6',0.6,
      '100%',1,'0%',0,'5%',0.05,'10%',0.1,'15%',0.15,'20%',0.2,'25%',0.25,'30%',0.3,'35%',0.35,'40%',0.4,'60%',0.6,'50%',0.5)),*/
      -- 2020.01.02 修改开始 
      decode(SFYB00,'否',1,decode(ZFBL00,'无',decode(YBLB00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0,null,0,cast(replace(replace(ZFBL00,'%',''),'自付','') as decimal(18,2))/100)),
      -- 2020.01.02 修改结束
      decode(SFYB00,'否',7,decode(YBLB00,'非医保',7,'甲类',4,'乙类',5)),
      B.BH0000,
      SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00
    from bm_ybypml_temp09 a,bm_ybfpxm b where b.xmmc00=a.ybfpxm and b.YBZXLB=ad_YBZXLB and b.FBBH00=ad_FBBH00
      and a.xmbh00  in
      (
        select xmbh00 from bm_ybypml_temp09
        intersect
        select trim(xmbh00) from bm_ybsfxm where YBZXLB=ad_YBZXLB and FBBH00=ad_FBBH00
       );


    commit;

    --2、备份表bm_ybsfxm数据
    insert into BM_YBXMBF   (
      YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00,
      SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,YBBRLB,SFSX00,SCCJ00,YPGG00,BZ0000,
      FBBH00,CZY000,CZRQ00,CZSJ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1)
    select       YBZXLB ,XMBH00 ,FLAG00 ,ZYFPXM ,MZFPXM ,XMMC00 ,YPJX00 ,XMDW00 ,ZFBL00 ,SFYX00 ,
      SFYBXM ,YBLB00 ,PYSM00 ,WBSM00 ,GXXLH0 ,YBBRLB ,SFSX00 ,SCCJ00 ,YPGG00 ,BZ0000 ,
      FBBH00 ,ad_CZY000 ,ls_CZRQ00 ,ls_CZSJ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1
    from bm_ybsfxm a where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00;
    commit;

    --3、临时表bm_ybypml_temp09导入到BM_YBSFXM
    insert into bm_ybsfxm (
      YBZXLB,XMBH00,FLAG00,ZYFPXM,
      MZFPXM,XMMC00,YPJX00,XMDW00,
      ZFBL00,SFYX00,SFYBXM,YBLB00,
      PYSM00,WBSM00,GXXLH0,YBBRLB,
      SFSX00,YPGG00,BZ0000,SCCJ00,fbbh00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00
      )
    select ad_YBZXLB,XMBH00,'1',YBFPXM,
      YBFPXM,nvl(YPTYM0,YPMC00),YPJX00,YPJJDW,
      nvl(ZFBL00,1),'Y',SFYB00,YBLB00,
      substrb(SF_YJ_GETPYSM(nvl(YPTYM0,YPMC00)),1,10),substrb(SF_YJ_GETWBSM(nvl(YPTYM0,YPMC00)),1,10),0,'Z',
      SFSX00,YPGG00,BZ0000,YPSCCJ,ad_FBBH00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00
    from bm_ybypml_temp07;

    SP_EXECUTE_SQL('alter table bm_ybsfxm disable all triggers');
--    update bm_ybsfxm a set (zfbl00,yblb00,SFYBXM)=
--      (select zfbl00,YBLB00,SFYB00 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00))
--       where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00
--        and exists (select 1 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00));   --应该是处理交集而不是差集 同时该写法执行比较慢，主要是trim(a.xmbh00)慢

--update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00 by CC 20170622
     update bm_ybsfxm a set (zfbl00,yblb00,SFYBXM,BZ0000,SCCJ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00,xmdw00)=
        (select zfbl00,YBLB00,SFYB00,BZ0000,YPSCCJ,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00,ypjjdw
         from bm_ybypml_temp10 b where b.xmbh00=a.xmbh00)
         where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00
          and exists (select 1 from bm_ybypml_temp10 b where b.xmbh00=a.xmbh00);

    commit;
    SP_EXECUTE_SQL('alter table bm_ybsfxm enable all triggers');

    --4、备份表bm_ybsfdy数据
    insert into BM_YBDYBF   (
        SFXMID,SFYP00,YBZXLB,YBXMBH,YBZYFP,YBMZFP,
        ZFBL00,SFYBXM,YBLB00,YBBRLB,FBBH00,YYDJ00,
        ZFJE00,JMBL00,JMJE00,SFSX00,BZ0000,YXBZ00,CZY000,CZRQ00,CZSJ00
    )
    select SFXMID,SFYP00,YBZXLB,YBXMBH,YBZYFP,YBMZFP,
        ZFBL00,SFYBXM,YBLB00,YBBRLB,FBBH00,YYDJ00,
        ZFJE00,JMBL00,JMJE00,SFSX00,BZ0000,YXBZ00,ad_CZY000,ls_CZRQ00,ls_CZSJ00
    from bm_ybsfdy a where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00;
    commit;

    --5、修改对应表的自付比例、医保类别、是否医保项目（以医保项目表的数据为准）
    --if ad_GXLX00='1' then
      SP_EXECUTE_SQL('alter table bm_ybsfdy disable all triggers');
    --end if;


    --5.1 修改对应表药品
         select count(1) into ls_COUNT0 from bm_ybypml_temp09 where  rownum=1; --length(trim(xmbh00))=20 and  
        if ls_COUNT0>0 then
          if ad_GXLX00='1' then
              update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N'
                      where  ybzxlb=ad_YBZXLB and sfyp00='Y' and FBBH00=ad_FBBH00 and --length(trim(YBXMBH))=20 and
                            not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh);
          end if;
          --update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1 by CC 20170622
          update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1)=
             (select zfbl00,yblb00,SFYBXM,BZ0000,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1
              from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00)
             where  ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='Y' --length(trim(YBXMBH))=20 and
                and exists (select 1 from bm_ybypml_temp09 where xmbh00=a.ybxmbh);
                
        end if;
    --end if;

    --5.2 修改对应表项目
  
        select count(1) into ls_COUNT0 from bm_ybypml_temp09 where rownum=1;  -- length(trim(xmbh00))=12 and 
        if ls_COUNT0>0 then
          if ad_GXLX00='1' then
              update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N'
                      where ybzxlb=ad_YBZXLB and sfyp00='N' and  FBBH00=ad_FBBH00 and  --length(trim(YBXMBH))=12 and 
                            not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh);

          end if;
          update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000)=
             (select zfbl00,yblb00,SFYBXM,BZ0000 from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00)
             where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='N' --length(trim(YBXMBH))=12 and 
                and exists (select 1 from bm_ybypml_temp09 where xmbh00=a.ybxmbh);
        end if;
    --end if;

    commit;
    --if ad_GXLX00='1' then
      SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers');
    --end if;
    select count(1) into ad_GXHJLS from bm_ybsfxm where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00;
exception
        when no_data_found then
             as_YHMSG0:='数据没有找到，请核对您所输入的票据类别是否正确！';
             as_SYSMSG:=SQLERRM;
             SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers');
             Rollback;
        when others then
             as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
             as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_XM_YBDR_XLS('||ad_YBZXLB||','||ad_MLBH00||','||ad_GXLX00||',as_YHMSG0,as_SYSMSG)',1,200);
             SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers');
             rollback;
end;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_XM_YBDR_XLS