create or replace procedure SP_YF_YBZSRZ_EDIT
-- modification history
-- Person      Date         Comments
-- linshu      2024.06.05   create 医保追溯码日志操作;
-- huangjy     2024.06.06   增加YF_YZZSMX for YF-20240605-001
-- huangjy     2024.06.20   修正异常处理 for YFPY-20240619-001
-- huangjy     2024.06.21   修正单价格YPRKPC for YF-20240621-001
-- huangjy     2024.06.24   修正单价格YPRKPC for YF-20240621-002
-- huangjy     2024.06.26   更新脚本 for YF-20240626-001
-- huangjy     2024.06.28   变更流程 for   YF-20240627-001
-- huangjy     2024.07.02   增加处方补刷合并到之前ZSID00 for YF-20240524-001
-- huangjy     2024.07.12   生产日期修正 for YK-20240712-001
-- huangjy     2024.07.16   修正多价格批次库存删除的问题 for YF-20240716-001
-- huangjy     2024.07.25   增加住院药品追溯码  for   YF-20240725-001
-- huangjy     2024.09.09   如果没有YKRKPC的药品增加异常处理并删除之前生成的待上传数据 for YF-20240909-001
-- huangjy     2024.09.13   修正异常报错 for YF-20240913-002
-- huangjy     2024.10.30   增加自动预扣的批次如果已经扫码，不再生成上传数据 for YK-20241024-002
-- huangjy     2025.01.15   修正预扣批次入库刷满，不再生成上传日志的问题 for YFPY-20250106-001
( POPFLAG  in     number               ,             --操作标志 0：发药  1：退药
  PMZZYBZ  in     varchar2             ,             --0:门诊  1：住院
  PCFLSH0  in     char             ,                 --处方流水号,出库单
  PCZY000  in     number               ,             --操作员
  PCOMMIT  in     varchar2 default 'N' ,             --是否提交
  PZXCGBZ  out    number               ,             --执行成功标志 1成功 0失败
  PTSXX00  out    varchar2             ,             --执行结果提示信息，成功的情况下也可能有警告信息
  PXXXX00  out    varchar2                           --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
 )
as
  Vsysdate    date;                                --日期变量
  Vcounter    number(5);
  Ecustom     exception;
  V_BRXM00    BM_BRXXB0.BRXM00%type;
  V_ZYGHID    number(10);
  v_YZLB00    varchar2(2);
  v_YPQLPC    number(20);
  vZSID00     YF_YBZSRZ.ZSID00%type;
  LS_ZSMQY    BM_TYZD00.MC0000%type;
  V_YKRKPC    YF_KCYKMX.YKRKPC%type;
  V_YPRKMX    YK_YPRKMX%rowtype;
  V_YKBMBH    YK_YPRKD0.YKBMBH%type;
  vCFLSH0     YF_YZYPSQ.ckdh00%type;
  vSCRQ00     YK_YPRKMX.SCRQ00%type;
  v_DLSJGL    XT_XTCS00.Value0%type;
  vYPSXRQ     YK_YPRKMX.YPSXRQ%type;
  v_CZSJ00    YF_YZZSMX.CZSJ00%type;
  i_RKSL00    YK_YPRKMX.RKSL00%type;
  i_ZHL000    YK_YPRKMX.ZHL000%type;
  i_K2J000    BM_YD0000.K2J000%type;
  SFSSQZ_MZ   BM_TYZD00.MC0000%type;
  ZSMCCBZ     boolean;
  Vcnt        number(4);
begin
  PZXCGBZ:=0;
  PTSXX00:='';
  PXXXX00:='';
  ZSMCCBZ:=false;
  Vcnt:=0;
  begin
    select nvl(MC0000,'0') into LS_ZSMQY from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='YBZSMSFQY' and YXBZ00='1';
  exception
      when others then
       LS_ZSMQY:='0';
  end;
  if LS_ZSMQY='0' then
    return;
  end if ;
  select to_char(sysdate,'HH24:MI:SS') into v_CZSJ00 from dual;
  begin
    SELECT Trim(VALUE0) INTO v_DLSJGL FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL';
  exception
    when others then
      v_DLSJGL:='N';
  end;
  if (POPFLAG=1)  then
     begin
       select YZLB00,YPQLPC into v_YZLB00,v_YPQLPC from YF_CFZSMX where TYCFH0 = trim(PCFLSH0) and rownum=1;
     exception
      when others then
        v_YZLB00:='-1';
     end;
  else
     begin
       --select YZLB00,YPQLPC into v_YZLB00,v_YPQLPC from YF_CFZSMX where CFLSH0 =rpad(PCFLSH0,16,' ') and rownum=1;
       select YZLB00,YPQLPC into v_YZLB00,v_YPQLPC from YF_CFZSMX where CFLSH0 =PCFLSH0 and rownum=1;
     exception
      when others then
        v_YZLB00:='-1';
     end;
  end if;

  if v_YZLB00='-1' then
     return;
  end if;

  if (POPFLAG=1)  then
     if (v_YZLB00='0') then
       select count(1) into Vcounter from YF_MZCF00 where CFLSH0 = PCFLSH0 and CFZT00='0';
     elsif (v_YZLB00='1') then
       select count(1) into Vcounter from YF_YZYPSQ where CKDH00 = PCFLSH0 and QLZT00='0';
     else
       select count(1) into Vcounter from YF_ZYCF00 where CFLSH0 = PCFLSH0 and CFZT00='0';
     end if;

     if Vcounter>0 then
       return;
     end if;
  end if;

  if (POPFLAG=1)  then
    if (v_YZLB00='0') then
      select BCCFH0 into vCFLSH0 from YF_MZCF00 where CFLSH0=PCFLSH0;
    end if;
    if (v_YZLB00='1') then
      select CKDH00 into vCFLSH0 from YF_YZYPSQ where CKDH00=PCFLSH0 and CXBZ00='-' ;
    end if;
    if (v_YZLB00='2') then
      select BCCFH0 into vCFLSH0 from YF_ZYCF00 where CFLSH0=PCFLSH0;
    end if;
  else
    vCFLSH0:=PCFLSH0;
  end if;
  if Popflag = 0 then
    select count(1) into Vcounter from YF_CFZSMX where CFLSH0 = PCFLSH0  and SCZT00='0' ;
    if Vcounter=0 then
      return;
    end if;
  else
    select count(1) into Vcounter from YF_CFZSMX where CFLSH0 = vCFLSH0 and TYCFH0 = trim(PCFLSH0) ;
    if Vcounter=0 then
      return;
    end if;
  end if;

   --发药
  if Popflag = 0 then
      --退药第二次进入判断，不能重复生成
     select count(*) into Vcounter
        from YF_CFZSMX a where a.CFLSH0=vCFLSH0  and a.MZZYBZ=PMZZYBZ and a.SCZT00='0'
        and not exists(select 1 from YF_YZZSMX B where B.CFLSH0=A.CFLSH0
                       and B.MZZYBZ=a.MZZYBZ and B.ZSM000=A.ZSM000);
     if Vcounter=0 then
        return;
     end if;

     if v_YZLB00 = '0' then
       select BRXM00,GHID00 into V_BRXM00,V_ZYGHID from YF_MZCF00 where CFLSH0 = PCFLSH0;
     end if;
     if v_YZLB00 = '2' then
       select BRXM00,ZYID00 into V_BRXM00,V_ZYGHID from YF_ZYCF00 where CFLSH0 = PCFLSH0;
     end if;
     if v_YZLB00 = '1' then
       select BRXM00,ZYID00 into V_BRXM00,V_ZYGHID from YF_YZYPSQ where CKDH00 = PCFLSH0;
     end if;
     begin
       select a.ZSID00 into vZSID00
        from YF_YBZSRZ a where a.CFLSH0=vCFLSH0  and a.MZZYBZ=PMZZYBZ and a.ZT0000='0';
     exception
       when others then
        vZSID00:=0;
     end;
     if vZSID00=0 then
       select SQ_YF_YBZSRZ_ZSID00.NEXTVAL into vZSID00 from dual;
       insert into YF_YBZSRZ
          (
            ZSID00,MZZYBZ,CFLSH0,BRXM00,ZYGHID,LB0000,ZT0000,BZ0000
           )
       values
           (
             vZSID00,PMZZYBZ,vCFLSH0,V_BRXM00,V_ZYGHID,'0','0',null
            );
     end if;


    update YF_CFZSMX A set FYTYBZ = '1'
        where a.CFLSH0=PCFLSH0 and a.SCZT00 = '0' and A.MZZYBZ = PMZZYBZ and FYTYBZ = '0'
          and not exists ( select 1 from YF_YZZSMX B where B.CFLSH0 = A.CFLSH0 and B.MZZYBZ = PMZZYBZ and B.ZSM000 = A.ZSM000 );


    insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000,CZSJ00)
     select vZSID00,a.MZZYBZ,a.YZLB00,a.CFLSH0,a.YPQLPC,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0',v_CZSJ00
        from YF_CFZSMX a where a.CFLSH0=PCFLSH0  and a.MZZYBZ=PMZZYBZ and a.SCZT00 = '0' and a.YZLB00=v_YZLB00
         and not exists(select 1 from YF_YZZSMX B where B.CFLSH0=A.CFLSH0 and B.MZZYBZ=a.MZZYBZ and B.ZSM000=A.ZSM000
         and YZLB00=v_YZLB00);

    for cur_zsm in ( select  a.YPNM00,a.MZZYBZ,a.YZLB00,a.ZSM000,a.CFLSH0,a.YPQLPC CFID00,a.CZY000
                     from YF_YZZSMX a where ZSID00=vZSID00
         ) loop

       begin
        if v_YZLB00 = '0' then
          select nvl(YKRKPC,0) into V_YKRKPC from YF_YPMXZ0 A where  A.RCLXBH= '09' and A.DJH000 = PCFLSH0
          and A.YPNM00 = cur_zsm.YPNM00 and A.YPCKPC = cur_zsm.Cfid00 and JZRQ00>=to_char(sysdate-2,'YYYYMMDD');
        elsif v_YZLB00 = '1' then
          select nvl(YKRKPC,0) into V_YKRKPC from YF_YPMXZ0 A where  A.RCLXBH= '03' and A.DJH000 = PCFLSH0
          and A.YPNM00 = cur_zsm.YPNM00 and A.YPCKPC = cur_zsm.Cfid00 and JZRQ00>=to_char(sysdate-2,'YYYYMMDD');
        else
          select nvl(YKRKPC,0) into V_YKRKPC from YF_YPMXZ0 A where  A.RCLXBH= '10' and A.DJH000 = PCFLSH0
          and A.YPNM00 = cur_zsm.YPNM00 and A.YPCKPC = cur_zsm.Cfid00 and JZRQ00>=to_char(sysdate-2,'YYYYMMDD');
        end if;
       exception
         when others then
         V_YKRKPC := 0;
       end;

       if v_DLSJGL='Y' then
        --获取药品入库批次
         if V_YKRKPC=0 then
           begin
            select c.YPRKPC into V_YKRKPC from YK_YPRKMX c,YF_PCKCMX b,YF_KCYKMX a
            where a.KCLSH0=b.KCLSH0 and a.CFLSH0=PCFLSH0 and A.YPNM00=cur_zsm.YPNM00 and a.LYDJH0=cur_zsm.CFID00
             and c.YPNM00=a.YPNM00 and c.SCPH00=b.SCPH00 and c.YPSXRQ=b.YPSXRQ  and rownum=1;
           exception
            when others then
            V_YKRKPC := 0;
           end;
         end if;
         if V_YKRKPC=0 then
           begin
            select c.YPRKPC into V_YKRKPC from YK_YPRKMX c,YF_KCYKMX a
            where a.CFLSH0=PCFLSH0 and A.YPNM00=cur_zsm.YPNM00 and a.LYDJH0=cur_zsm.CFID00
             and c.YPNM00=a.YPNM00 and a.SCPH00=c.SCPH00 and a.YPSXRQ=c.YPSXRQ  and rownum=1;
           exception
            when others then
            V_YKRKPC := 0;
           end;
         end if;
       else
         if (v_YZLB00='0') or (v_YZLB00='2')or (v_YZLB00='1') then
           begin
               select c.YPRKPC into V_YKRKPC from YK_YPRKMX c,YF_YPRKMX b,YF_KCYKMX a
             where a.YKRKPC=c.YPRKPC  and A.YPNM00=cur_zsm.YPNM00 --and c.YPRKPC=b.YKRKPC
                and b.YPRKPC=to_number(a.KCLSH0)
                and a.LYDJH0=cur_zsm.CFID00
                and c.YPNM00=a.YPNM00 and trim(c.SCPH00)=trim(b.SCPH00) and c.YPSXRQ=b.YPSXRQ  and rownum=1;
           exception
             when others then
             V_YKRKPC := 0;
           end;
           if V_YKRKPC=0 then
             begin
               select max(c.YPRKPC) into V_YKRKPC from YK_YPRKMX c,YF_YZYPSQ a
              where a.CKDH00=PCFLSH0 and A.YPNM00=cur_zsm.YPNM00 and a.YPQLPC=cur_zsm.CFID00
               and c.YPNM00=a.YPNM00 and c.PCKCSL>0 ;

             exception
              when others then
                V_YKRKPC := 0;
             end;
           end if;


         end if;

       end if;

       if V_YKRKPC>0 then
         select count(1) into Vcounter from YK_RKZSM0 a where A.YPNM00 = cur_zsm.YPNM00 and a.ZSM000=cur_zsm.ZSM000;
         if Vcounter=0 then 
           select count(1) into Vcounter from YK_RKZSM0 a where a.YKRKPC=V_YKRKPC;
           --select count(1) into Vcnt from YF_CFZSMX a where a.CFLSH0=PCFLSH0  and a.MZZYBZ=PMZZYBZ
           --  and a.SCZT00 = '0' and a.YZLB00=v_YZLB00 and a.YPNM00=cur_zsm.YPNM00;

           select B.RKSL00,B.ZHL000,C.K2J000 into i_RKSL00,i_ZHL000,i_K2J000  from YK_YPRKMX b,BM_YD0000 C
             where b.YPNM00=c.YPNM00 and b.YPRKPC=V_YKRKPC;
           select nvl(max(MC0000),'0') into SFSSQZ_MZ from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='SFSSQZ_MZ';
           if (SFSSQZ_MZ='1') then
             if ceil(i_RKSL00*i_ZHL000/i_K2J000)-Vcounter<=0 then
                ZSMCCBZ:=true;
             else
                ZSMCCBZ:=false;
             end if;
           else
             if floor(i_RKSL00*i_ZHL000/i_K2J000)-Vcounter<=0 then
               ZSMCCBZ:=true;
             else
               ZSMCCBZ:=false;
             end if;
           end if;
        end if;

       end if;

        --查找是否已有药库追溯码记录
        select count(1) into  Vcounter from YK_RKZSM0 A where  A.YPNM00 = cur_zsm.YPNM00 and A.ZSM000 = cur_zsm.ZSM000;
        if ( Vcounter > 0 ) then
           begin
             select nvl(SCRQ00,'0') into vSCRQ00 from YK_RKZSM0 A where  A.YPNM00 = cur_zsm.YPNM00 and A.ZSM000 = cur_zsm.ZSM000;
           exception
             when others then
                vSCRQ00 := '0';
           end;
           if vSCRQ00='0' then
             begin
               select YXQ000 into vYPSXRQ from YK_RKZSM0 A where  A.YPNM00 = cur_zsm.YPNM00 and A.ZSM000 = cur_zsm.ZSM000;
             exception
               when others then
                 vYPSXRQ := '0';
             end;
           end if;
           if (length(vYPSXRQ)=8) and (vSCRQ00='0') then
              select  to_char(add_months(to_date(vYPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into vSCRQ00 FROM DUAL;
              update YK_RKZSM0 a set a.SCRQ00=vSCRQ00 where  a.YPNM00 = cur_zsm.YPNM00 and a.ZSM000 = cur_zsm.ZSM000;
           end if;
        elsif ( Vcounter = 0 ) and ( V_YKRKPC > 0 )then
          if ZSMCCBZ=false then
            select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_zsm.YPNM00;
            select a.YKBMBH into V_YKBMBH from YK_YPRKD0 A where a.RKDH00=V_YPRKMX.RKDH00;
            if (trim(V_YPRKMX.SCRQ00)='')or (V_YPRKMX.SCRQ00 is null) then

              if ( trim(V_YPRKMX.YPSXRQ) <> '' ) or ( V_YPRKMX.YPSXRQ is not null )  then
                select  to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into vSCRQ00 FROM DUAL;
              else
                vSCRQ00 := V_YPRKMX.SCRQ00;
              end if;
            else
              vSCRQ00:=V_YPRKMX.SCRQ00;
            end if;
            insert into YK_RKZSM0
            (
              YKRKPC,
              YPNM00,
              ZSM000,
              ZSMLY0,
              LYKF00,
              CZY000,
              SCPH00,
              SCRQ00,
              YXQ000,
              ZT0000
             )
            values
             (
               V_YKRKPC,
               cur_zsm.YPNM00,
               cur_zsm.ZSM000,
               '0',
               V_YKBMBH,
               cur_zsm.CZY000,
               V_YPRKMX.SCPH00,
               vSCRQ00,
               V_YPRKMX.YPSXRQ,
               '0'
              );
          end if;
        end if;
        if (ZSMCCBZ=true) then
           delete from YF_YZZSMX where ZSID00=vZSID00 and CZSJ00= v_CZSJ00 and YPNM00=cur_zsm.YPNM00 and ZSM000=cur_zsm.ZSM000;
        end if;
        if (Vcounter=0)and(V_YKRKPC=0) then
           --删除掉没有YKRKPC的药品，不传
           delete from YF_YZZSMX where ZSID00=vZSID00 and CZSJ00= v_CZSJ00 and YPNM00=cur_zsm.YPNM00;
        end if;
    end loop;

    select count(*) into Vcounter from YF_YZZSMX where ZSID00=vZSID00;
    if ( Vcounter = 0 )then
      delete from  YF_YBZSRZ where ZSID00=vZSID00;
    end if;
  end if;

  --退药
  if Popflag =1 then
     --退药第二次进入判断，不能重复生成
    select count(*) into Vcounter
        from YF_CFZSMX a where a.CFLSH0 = vCFLSH0 and a.TYCFH0 = trim(PCFLSH0) and a.MZZYBZ=PMZZYBZ
        and not exists(select 1 from YF_YZZSMX b,YF_YBZSRZ c where b.ZSID00=c.ZSID00 and b.CFLSH0=vCFLSH0
                       and b.ZSM000=a.ZSM000 and c.LB0000='1');


    if Vcounter=0 then
        return;
     end if;

    if v_YZLB00 = '0' then
       select a.BRXM00,a.GHID00 into V_BRXM00,V_ZYGHID from YF_MZCF00 a where a.CFLSH0 = PCFLSH0;
    end if;
    if v_YZLB00 = '2' then
       select a.BRXM00,a.ZYID00 into V_BRXM00,V_ZYGHID from YF_ZYCF00 a where a.CFLSH0 = PCFLSH0;
    end if;
    if v_YZLB00 = '1' then
       select a.BRXM00,a.ZYID00 into V_BRXM00,V_ZYGHID from YF_YZYPSQ a where a.CKDH00 = PCFLSH0 and rownum=1;
    end if;

    begin
      select c.ZSID00 into vZSID00 from YF_YZZSMX b,YF_YBZSRZ c where b.ZSID00=c.ZSID00
           and b.CFLSH0=VCFLSH0  and c.LB0000='1' and c.ZT0000='0' and rownum=1;
    exception
      when others then
        vZSID00:=0;
    end;
    if vZSID00=0 then
      select SQ_YF_YBZSRZ_ZSID00.NEXTVAL into vZSID00 from dual;
      insert into YF_YBZSRZ
      (
        ZSID00,
        MZZYBZ,
        CFLSH0,
        BRXM00,
        ZYGHID,
        LB0000,
        ZT0000,
        BZ0000
       )
     values
       (
         vZSID00,
         PMZZYBZ,
         VCFLSH0,
         V_BRXM00,
         V_ZYGHID,
         '1',
         '0',
         trim(PCFLSH0)
        );
     end if;
     if (v_YZLB00='1') then
         insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000)
         select vZSID00,a.MZZYBZ,a.YZLB00,a.CFLSH0,a.YPQLPC,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0'
           from YF_CFZSMX a where a.CFLSH0 = VCFLSH0 and A.TYCFH0 = trim(PCFLSH0)
           and not exists(select 1 from YF_YZZSMX b,YF_YBZSRZ c where b.ZSID00=c.ZSID00
                           and b.CFLSH0=vCFLSH0 and b.ZSM000=a.ZSM000 and c.LB0000='1');
     else
        insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000)
         select vZSID00,a.MZZYBZ,a.YZLB00,a.CFLSH0,a.YPQLPC,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0'
           from YF_CFZSMX a where a.CFLSH0 = VCFLSH0 and A.TYCFH0 = trim(PCFLSH0)
           and not exists(select 1 from YF_YZZSMX b,YF_YBZSRZ c where b.ZSID00=c.ZSID00
                           and b.CFLSH0=vCFLSH0 and b.ZSM000=a.ZSM000 and c.LB0000='1');
     end if;
  end if;
  PZXCGBZ:=1;
  if pcommit='Y' then
     commit;
  end if;
exception
  when no_data_found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
end;