CREATE OR REPLACE PROCEDURE SP_WM_TCMPAT_JXZHCL
(
  Popflag  in     varchar2,                   --操作类别 1不覆盖操作 2覆盖操作
  Pxzczid  in     number,                     --WM_TCMPAT_LIST_DLD_DET表的xzczid值
  Pmdcode  in     varchar2,                   --WM_TCMPAT_LIST_DLD_DET表的med_list_codg值
  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.12.29   created 国家医保平台西药中成药目录下载明细表解析转换处理过程     GGMK9EX-20211228-004
as
  ls_CZRQ00    char(8);
  ls_CZSJ00    char(8);
  ls_CZRQSJ    char(16);
  ls_Count0    number(10);
  Ecustom      exception;
  VRowMX       WM_TCMPAT_LIST_DLD_DET%rowtype; --新医保目录更新明细表
  VRowRZ       GEN_LIST_DLD_LOG%rowtype;       --新医保目录更新操作记录表
  ls_YBGXRQ    BM_YBSFXM.YBGXRQ%type;          --医保更新日期
  ls_PTFPBH    BM_YBFPXM.PTFPBH%type;          --国家医保平台发票项目类别
  ls_ZYFPXM    BM_YBSFXM.ZYFPXM%type;          --
  ls_MZFPXM    BM_YBSFXM.MZFPXM%type;          --
  ls_XMMC00    BM_YBSFXM.XMMC00%type;          --
  ls_YPJX00    BM_YBSFXM.YPJX00%type;          --
  ls_XMDW00    BM_YBSFXM.XMDW00%type;          --
  ls_ZFBL00    BM_YBSFXM.ZFBL00%type;          --
  ls_SFYX00    BM_YBSFXM.SFYX00%type;          --
  ls_SFYBXM    BM_YBSFXM.SFYBXM%type;          --
  ls_YBLB00    BM_YBSFXM.YBLB00%type;          --
  ls_PYSM00    BM_YBSFXM.PYSM00%type;          --
  ls_WBSM00    BM_YBSFXM.WBSM00%type;          --
  ls_GXXLH0    BM_YBSFXM.GXXLH0%type;          --
  ls_SFSX00    BM_YBSFXM.SFSX00%type;          --
  ls_SCCJ00    BM_YBSFXM.SCCJ00%type;          --
  ls_YPGG00    BM_YBSFXM.YPGG00%type;          --
  ls_BZ0000    BM_YBSFXM.BZ0000%type;          --
  ls_CPID00    BM_YBSFXM.CPID00%type;          --
  ls_ZXJJDW    BM_YBSFXM.ZXJJDW%type;          --
  ls_CJR000    BM_YBSFXM.CJR000%type;          --
  ls_CFBL00    BM_YBSFXM.CFBL00%type;          --
  ls_YBZGDJ    BM_YBSFXM.YBZGDJ%type;          --
  ls_YBZGDJ1   BM_YBSFXM.YBZGDJ1%type;         --
  ls_YBJSDJ    BM_YBSFXM.YBJSDJ%type;          --
  ls_YBJSDJ1   BM_YBSFXM.YBJSDJ1%type;         --
  ls_BKEH83    BM_YBSFXM.BKEH83%type;          --医保国家标准编码

  cursor cur_YBZX is
      select FBBH00,YBZXLB from IC_YBBRLB a where FBBH00 = 3 and YBLB00 = YBZXLB and YBZXLB = 'G'
        and not exists(select 1 from BM_YBSFXM where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and XMBH00 = Pmdcode);  --排除对应中心已存在的记录

begin
  Pzxcgbz:=0;
  ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
  ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
  ls_CZRQSJ := ls_CZRQ00||ls_CZSJ00;
  select * into VRowRZ from GEN_LIST_DLD_LOG where xzczid = Pxzczid;
  if sql%notfound then
    return;
  end if;

  select * into VRowMX from WM_TCMPAT_LIST_DLD_DET where xzczid = Pxzczid and med_list_codg = Pmdcode;
  if sql%notfound then
    return;
  end if;

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

  ls_YBGXRQ := replace(VRowMX.updt_time,'-','');                                                      --医保更新日期
  ls_XMMC00 := VRowMX.reg_name;                                                                      --项目名称
  ls_PTFPBH := (case when VRowMX.tcmpat_flag = '1' then '02' else '01' end);                         --医保发票项目  tcmpat_flag 中成药标志 1是 2否  --> 01西药 02成药
  ls_YPJX00 := substr(VRowMX.drug_dosform,1,50);                                                     --剂型
  ls_XMDW00 := substr(VRowMX.min_prcunt,1,20);                                                       --单位-->最小计价单位
  ls_SFYX00 := (case when (VRowMX.vali_flag = '0' or VRowMX.vali_flag = 'N') then 'N' else 'Y' end); --是否有效
  ls_ZFBL00 := 1;                                                                                    --自付比例 @@jlg暂无法从何取值
  ls_SFYBXM := 'Y';                                                                                  --是否医保项目 Y是 N否 @@jlg暂无法从何取值
  ls_YBLB00 := (case when VRowMX.nat_hi_drug_list_clas_flag = '乙' then '5' else '4' end);           --医保类别 (无对应项)  0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目4甲类药品 5乙类药品 6民族药品 7非医保药品
  ls_PYSM00 := substr(SF_XT_GETPYSM(ls_XMMC00),1,10);                                                --拼音首码
  ls_WBSM00 := substr(SF_XT_GETWBSM(ls_XMMC00),1,10);                                                --五笔首码
  ls_GXXLH0 := 0;                                                                                    --更新序列号
  ls_SFSX00 := null;                                                                                 --收费上限 (无对应下)
  ls_SCCJ00 := substrb(VRowMX.prodentp_name,1,100);                                                  --生产厂家
  ls_YPGG00 := substrb(VRowMX.drug_spec,1,50);                                                       --规格型号
  --ls_BZ0000 := substrb(VRowMX.each_dos,1,250);                                                       --备注说明
  ls_BZ0000 := substrb(trim(VRowMX.NAT_HI_DRUG_LIST_MEMO),1,250);                                           --备注说明  WHY 20220602
  ls_YBZGDJ := 0;                                                                                    --医保最高单价(大单位)
  ls_YBZGDJ1:= 0;                                                                                    --医保最高单价(小单位)
  ls_CPID00 := null;                                                                                 --医保产品ID号 (无对应项)
  ls_ZXJJDW := substrb(VRowMX.min_prcunt,1,10);                                                      --最小计价单位
  ls_CFBL00 := null;                                                                                 --拆分比例（倍率）
  ls_CJR000 := VRowRZ.gxczy0;                                                                        --创建人
  ls_YBJSDJ := null;                                                                                 --医保结算单价(大单位)
  ls_YBJSDJ1:= null;                                                                                 --医保结算单价(小单位)
  ls_BKEH83 := substr(VRowMX.drug_std_code,1,50);                                                    --药监本位码
  --先补充插入缺失的医保中心项目
  for ybzx in cur_YBZX loop
    ls_ZYFPXM := SF_XT_GETYBFPBHByPTFPBH(ybzx.FBBH00,ybzx.YBZXLB,ls_PTFPBH);  --根据国家医保平台发票编号获取院内医保住院发票
    ls_MZFPXM := SF_XT_GETYBFPBHByPTFPBH(ybzx.FBBH00,ybzx.YBZXLB,ls_PTFPBH);  --根据国家医保平台发票编号获取院内医保门诊发票
    insert into BM_YBSFXM(
                FBBH00,
                YBZXLB,
                XMBH00,
                XMMC00,
                ZYFPXM,
                MZFPXM,
                YPJX00,
                XMDW00,
                ZFBL00,
                ZYZFBL,
                SFYX00,
                SFYBXM,
                YBLB00,
                PYSM00,
                WBSM00,
                GXXLH0,
                SFSX00,
                SCCJ00,
                YPGG00,
                BZ0000,
                YBZGDJ,
                YBZGDJ1,
                CPID00,
                ZXJJDW,
                CFBL00,
                CJR000,
                YBJSDJ,
                YBJSDJ1,
                BKEH83
            )
            values(
                ybzx.FBBH00,
                ybzx.YBZXLB,
                Pmdcode,
                ls_XMMC00,
                ls_ZYFPXM,
                ls_MZFPXM,
                ls_YPJX00,
                ls_XMDW00,
                ls_ZFBL00,
                ls_ZFBL00,
                ls_SFYX00,
                ls_SFYBXM,
                ls_YBLB00,
                ls_PYSM00,
                ls_WBSM00,
                ls_GXXLH0,
                ls_SFSX00,
                ls_SCCJ00,
                ls_YPGG00,
                ls_BZ0000,
                ls_YBZGDJ,
                ls_YBZGDJ1,
                ls_CPID00,
                ls_ZXJJDW,
                ls_CFBL00,
                ls_CJR000,
                ls_YBJSDJ,
                ls_YBJSDJ1,
                ls_BKEH83
            );

  end loop;


  --医保所有该项目的医保中心记录都修改；但只修改部分字段
  update BM_YBSFXM set
         XMMC00 = ls_XMMC00,
         ZYFPXM = nvl(SF_XT_GETYBFPBHByPTFPBH(FBBH00,YBZXLB,ls_PTFPBH),ZYFPXM),  --此三个字段，如果没有取到值，则仍按原来的
         MZFPXM = nvl(SF_XT_GETYBFPBHByPTFPBH(FBBH00,YBZXLB,ls_PTFPBH),MZFPXM),  --
         YPJX00 = nvl(ls_YPJX00,YPJX00),  --
         XMDW00 = ls_XMDW00,
         SFYX00 = ls_SFYX00,
         PYSM00 = ls_PYSM00,
         WBSM00 = ls_WBSM00,
         SCCJ00 = ls_SCCJ00,
         YPGG00 = ls_YPGG00,
         BZ0000 = ls_BZ0000,
         ZFBL00 = ls_ZFBL00,
         ZYZFBL = ls_ZFBL00,
         SFYBXM = ls_SFYBXM,
         YBLB00 = ls_YBLB00,
         BKEH83 = nvl(ls_BKEH83,BKEH83),  --改为如果传进来的为空，那还是按原来的
         XGR000 = ls_CJR000,
         XGRQSJ = ls_CZRQSJ,
         YBGXRQ = ls_YBGXRQ
    where FBBH00 = 3
      and YBZXLB = 'G'
      and XMBH00 = Pmdcode
      and (YBGXRQ is null or YBGXRQ < ls_YBGXRQ) --jlg 注意这个条件：只有当前医保更新日期小于或等于更新明细项目里的医保更新日期，才进行更新；如果当前医保更新日期大于更新明细项目的医保更新日期，则不进行更新
      ;

  --此记录更新为 ‘已转换’
  update WM_TCMPAT_LIST_DLD_DET set ZHCLBZ = '1' where xzczid = Pxzczid and med_list_codg = Pmdcode;
  --对应日志表更新为 ‘HIS提取未完成’
  update GEN_LIST_DLD_LOG set HSTQBZ = '2' where xzczid = Pxzczid ;

  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;
