CREATE OR REPLACE PROCEDURE SP_BM_DBZXX0_JXZHCL
(
  Popflag  in     varchar2,                   --操作类别 1不覆盖操作 2覆盖操作
  Pdrczid  in     number,                     --BM_DBZDR0表的DRCZID值
  Pdbzbm0  in     varchar2,                   --BM_DBZXX0表的DBZBM0值
  Pzzdbm0  in     varchar2,                   --BM_DBZXX0表的ZZDBM0值
  Pssczbm  in     varchar2,                   --BM_DBZXX0表的SSCZBM值
  Pczy000  in     number     default 0,       --操作员
  Pczyks0  in     number     default 0,       --操作员科室
  Pcommit  in     varchar2   default 'N',     --是否提交 Y是N否
  Pycshow  in     varchar2   default '0',     --出现异常是否显示 0不显示，1显示
  Pxmlstr  in     varchar2   default '',      --统一的备用入参，以xml格式编码
  Pzxcgbz  out    number,                     --执行成功标志 1成功 0失败
  Ptsxx00  out    varchar2,                   --执行结果提示信息，成功的情况下也可能有警告信息
  Pxxxx00  out    varchar2,                   --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
  Poutstr  out    varchar2                    --统一的备用出参，以xml格式编码
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jlg        2021.04.06   created 导入的单病种原始数据信息表解析转换处理过程  XMGL9-20210108-002
--xcj      2021.04.06   条件修改 为 (ZZDBM0 = Pzzdbm0  or  Pzzdbm0 is null)  and (SSCZBM = Pssczbm or Pssczbm is null)
--xcj      2021.04.06   SP_BM_FFBZBM_ZJSCXG  :v22,:v23 入参 添加 病种国家编码和国家名称 
as
  ls_CZRQ00    char(8);
  ls_CZSJ00    char(8);
  ls_Count0    number(10);
  LS_SQLSTR    varchar2(1000);
  Ecustom      exception;
  ls_SFBZID    BM_BZSFBZ.SFBZID%type;
  ls_opflag    char(1);
  ls_BZMCPY    BM_BZSFBZ.BZMCPY%type;    --省属单病种名称拼音码
  ls_BZMCWB    BM_BZSFBZ.BZMCWB%type;    --省属单病种名称五笔码
  ls_SFTCTS    BM_BZSFBZ.SFTCTS%type;
  VRowMX       BM_DBZXX0%rowtype;      --导入的单病种原始数据信息表
  VRowRZ       BM_DBZDR0%rowtype;      --省属单病种导入操作日志表

  cursor cur_YBZX is
      select FBBH00,YBZXLB from IC_YBBRLB a where FBBH00 in(1,3) and YBLB00 = YBZXLB
        and not exists(select 1 from BM_BZSFBZ where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and FFBZBM = Pdbzbm0
        and (ZZDBM0 = Pzzdbm0  or  Pzzdbm0 is null)  and (SSCZBM = Pssczbm or Pssczbm is null)
        );  --排除对应中心已存在的记录


begin
  Pzxcgbz:=0;
  ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
  ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
  select * into VRowMX from BM_DBZXX0 where DRCZID = Pdrczid and(ZZDBM0 = Pzzdbm0  or  Pzzdbm0 is null)  and (SSCZBM = Pssczbm or Pssczbm is null);
  if sql%notfound then
    return;
  end if;

  --如果不覆盖操作，则已转换的不处理
  if Popflag = 1 and VRowMX.CZFLAG ='1' then
    Pzxcgbz:=1;
    return;
  end if;

  select * into VRowRZ from BM_DBZDR0 where DRCZID = Pdrczid;
  if sql%notfound then
    return;
  end if;

  ls_BZMCPY:= substr(SF_XT_GETPYSM(VRowMX.DBZMC0),1,20);
  ls_BZMCWB:= substr(SF_XT_GETWBSM(VRowMX.DBZMC0),1,20);

  --先将对应医保中心未有数据的先插入，已存在的就修改
  for ybzx in cur_YBZX loop
    select count(*) into ls_Count0 from BM_BZSFBZ where FBBH00 = ybzx.FBBH00 and YBZXLB = ybzx.YBZXLB and FFBZBM = Pdbzbm0 and  (ZZDBM0 = Pzzdbm0  or  Pzzdbm0 is null)  and (SSCZBM = Pssczbm or Pssczbm is null)  ;
    if ls_Count0 >0 then
      ls_opflag:= '2';   --修改操作
      select nvl(max(SFTCTS),'0'),SFBZID into ls_SFTCTS,ls_SFBZID from BM_BZSFBZ where FBBH00 = ybzx.FBBH00 and YBZXLB = ybzx.YBZXLB and FFBZBM = Pdbzbm0  and(ZZDBM0 = Pzzdbm0  or  Pzzdbm0 is null)  and (SSCZBM = Pssczbm or Pssczbm is null);
    else
      ls_opflag:= '1';   --新增操作
      ls_SFTCTS:= '0';   --是否弹窗显示
    end if;

    LS_SQLSTR:='begin SP_BM_FFBZBM_ZJSCXG(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15,:v16,:v17,:v18,:v19,:v20,:v21,:v22,:v23); end;';
      execute immediate LS_SQLSTR using
      in     ls_opflag,                   --Popflag varchar2,        --操作类别 1增加 2修改 3删除
      in out ls_SFBZID,                   --Psfbzid number,          --省属单病种字典ID
      in     ybzx.FBBH00,                 --Pfbbh00 number,          --费别
      in     ybzx.YBZXLB,                 --Pybzxlb varchar2,        --医保中心类别
      in     Pdbzbm0,                     --Pffbzbm varchar2,        --省属单病种编码
      in     VRowMX.DBZMC0,               --Pbzmc00 varchar2,        --省属单病种名称
      in     ls_BZMCPY,                   --Pbzmcpy varchar2,        --省属单病种名称拼音码
      in     ls_BZMCWB,                   --Pbzmcwb varchar2,        --省属单病种名称五笔码
      in     Pzzdbm0,                     --Pzzdbm0 varchar2,        --主诊断编码
      in     VRowMX.SSCZMC,               --Pzczmc0 varchar2,        --主操作名称
      in     Pssczbm,                     --Pssczbm varchar2,        --手术（操作）码
      in     VRowMX.CWNR00,               --Pcwnr00 varchar2,        --除外内容
      in     VRowMX.BZSF00,               --Pbzsfje number,          --病种收费标准
      in     VRowMX.BZSM00,               --Pbz0000 varchar2,        --备注
      in     Pczy000,                     --Pczy000 number,          --操作员
      in     Pczyks0,                     --Pczyks0 number,          --操作员科室
      in     Pcommit,                     --Pcommit varchar2,        --是否提交
      in     ls_SFTCTS,                   --PSFTCTS varchar2,        --（电子病历系统纳入病种后）是否弹窗提示
      out    Pzxcgbz,                     --Pzxcgbz number,          --执行成功标志 1成功 0失败
      out    Ptsxx00,                     --Ptsxx00 varchar2,        --执行结果提示信息，成功的情况下也可能有警告信息
      out    Pxxxx00,                   --Pxxxx00 varchar2         --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
      in    Pdbzbm0,   --国家编码
      in     VRowMX.DBZMC0 ;  --国家名称
  end loop;


  --此记录更新为 ‘已解析处理’
  update BM_DBZXX0 set CZFLAG = '1' where DRCZID = Pdrczid and DBZBM0 = Pdbzbm0 and ZZDBM0 = Pzzdbm0 and SSCZBM = Pssczbm;
  --对应日志表更新为 ‘HIS提取未完成’
  update BM_DBZDR0 set CZFLAG = '1' where DRCZID = Pdrczid;

  if Pcommit ='Y' then
    commit;
  end if;

  Pzxcgbz:=1;
exception
  when no_data_found then
    if Pycshow = '1' then
      raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240));
    else
      Ptsxx00:= substrb('没有找到数据！*'||sqlerrm,1,240);
    end if;
  when Ecustom       then
    if Pycshow = '1' then
      raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240));
    end if;
  when others        then
    if Pycshow = '1' then
      raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
    else
      Ptsxx00:= substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240);
    end if;
end;