create or replace procedure SP_YF_RKZSM0_BACK
-- modification history
-- Person      Date        Comments
-- linshu      2024.06.05  create 回写药库入库追溯码;
-- linshu      2024.06.21  生产日期为空的，取失效日期前三个月; for YF9-20240621-003
-- linshu      2024.06.27  不重复生成YK_RKZSM0数据，生产日期为空的，取失效日期前三年; for YF9-20240627-002
-- linshu      2024.07.01  修正了同一个处方同一个药品有多个批次的情况，不能回写药库入库追溯码问题; for YF9-20240621-001
-- linshu      2024.07.04  增加日志; for YF9-20240703-001
-- linshu      2024.07.19  增加了住院扫码回写药库入库追溯码功能; for YF9-20240712-002 
-- linshu      2024.07.26  增加了门诊处方同一药品有多条记录报错问题; for YF9-20240726-002 
-- linshu      2024.07.31  修正一条追溯码被拆零成多个出库批次出库不能回写药库入库追溯码问题; for YF9-20240731-001
(
  PMZZYBZ  in     varchar2             ,             --0:门诊  1：住院
  PCFLSH0  in     varchar2             ,             --门诊：处方流水号 住院：出库单号
  PCZY000  in     number               ,             --操作员
  Pcommit  in     varchar2 default 'N' ,             --是否提交
  Pzxcgbz  out    number               ,             --执行成功标志 1成功 0失败
  Ptsxx00  out    varchar2             ,             --执行结果提示信息，成功的情况下也可能有警告信息
  Pxxxx00  out    varchar2             ,             --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
  PCZYKS0  in     number   default 0   ,             --操作员科室
  PZYID00  in     number   default 0   ,             --住院ID 
  PYPQLPC  in     number   default 0                 --YF_YZYPSQ.YPQLPC
)
as
  Vsysdate    date;                                --日期变量
  Vcount      number(14,4);                        --计数器变量
  Ecustom     exception;
  V_YKRKPC    number(16);
  V_YFBMBH    YF_MZCF00.YFBMBH%type;
  V_YPRKMX    YK_YPRKMX%rowtype;
  V_SCRQ00    YK_YPRKMX.SCRQ00%type;
  V_ZSL000    number(5);
  V_SL0000    number(5);
  V_CZYKS0    YF_YPCKD0.YFBMBH%type;
  V_XXlOG0    varchar2(2000);
begin
  Pzxcgbz := 0;
  Vsysdate := sysdate;  
  Ptsxx00:=''; 
  V_XXlOG0 :='PMZZYBZ='||nvl(to_char(PMZZYBZ),'null')||';'||'PCFLSH0='||nvl(to_char(PCFLSH0),'null')||';'||'PCZY000='||nvl(to_char(PCZY000),'null')||';'||
            'Pcommit='||nvl(to_char(Pcommit),'null')||';'||'PCZYKS0='||nvl(to_char(PCZYKS0),'0')||';'||'PZYID00='||nvl(to_char(PZYID00),'0')||';'||
            'PYPQLPC='||nvl(to_char(PYPQLPC),'0');
  SP_TransLog(Vsysdate,'SP_YF_RKZSM0_BACK',PCZY000,PCZYKS0,V_XXlOG0);
  
  if PMZZYBZ = '0' then  --门诊
    select YFBMBH into V_YFBMBH from YF_MZCF00 where CFLSH0 = PCFLSH0;
      for cur_ypmxz0 in  ( select * from YF_YPMXZ0 A where  A.RCLXBH= '09' and A.DJH000 = PCFLSH0 
           and (a.ypnm00,a.ypckpc) in ( select b.ypnm00,b.ypqlpc from YF_YZZSMX b where 
           not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = B.ZSM000 ) ))  loop
           
       select decode(floor(a.fcsl00*a.zhl000/b.k2j000),0,1,floor(a.fcsl00*a.zhl000/b.k2j000)) into V_ZSL000 
         from YF_YPMXZ0 A,BM_YD0000 b 
        where a.ypnm00 = b.ypnm00 
          and A.RCLXBH = '09' 
          and A.DJH000 = PCFLSH0 
          and A.YPNM00 = cur_ypmxz0.YPNM00 
          and A.YKRKPC = cur_ypmxz0.YKRKPC 
          and A.YPCKPC = cur_ypmxz0.YPCKPC;   
           
       V_SL0000 := 0; 
          
      --追溯码循环 
      for cur_zsm in ( select b.zsm000, b.ypnm00,b.ypqlpc from YF_YZZSMX b  
        where b.ypnm00 = cur_ypmxz0.ypnm00 and b.ypqlpc = cur_ypmxz0.ypckpc and b.cflsh0 = PCFLSH0  
          and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = B.ZSM000 ) ) loop       
          
          --防止重复插入
          select count(1) into Vcount from YK_RKZSM0 where zsm000 = cur_zsm.zsm000;
          V_SL0000 := V_SL0000 + 1;    
          
          if (Vcount = 0)  and (V_SL0000 <= V_ZSL000)then
            select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = cur_ypmxz0.YKRKPC and A.YPNM00 = cur_zsm.Ypnm00; 
            if V_YPRKMX.SCRQ00 is null then
              if V_YPRKMX.YPSXRQ is not null  then            
                select  to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL;            
              else
                V_SCRQ00 := V_YPRKMX.SCRQ00;                
              end if; 
              else   
			    V_SCRQ00 := V_YPRKMX.SCRQ00;  
            end if;       
            insert into YK_RKZSM0
              (
                YKRKPC,
                YPNM00,
                ZSM000,
                ZSMLY0,
                LYKF00,
                CZY000,
                SCPH00,
                SCRQ00,
                YXQ000,
                ZT0000,
                SFLB00
               )
            values
               (
                 V_YPRKMX.YPRKPC,
                 cur_zsm.YPNM00,
                 cur_zsm.ZSM000,
                 '0',
                 V_YFBMBH,
                 PCZY000,
                 V_YPRKMX.Scph00,
                 V_SCRQ00,
                 V_YPRKMX.YPSXRQ,
                 '0',
                 '3'
                );
           
          end if;         
       end loop;  
    end loop;
  elsif nvl(PYPQLPC,0) > 0 then
    
    for cur_CKMX in  ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.YPQLPC = PYPQLPC and MZZYBZ = '1' and YZLB00 = '1' 
                          and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 ))  loop
        --获取药品的入库批次
        select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B  where A.CKDH00 = B.CKDH00 and  A.CKDH00 = cur_CKMX.CFLSH0 and A.YPQLPC = PYPQLPC and YPNM00 = cur_CKMX.Ypnm00 and rownum = 1;
        if V_YKRKPC > 0 then       
          select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00;
          if V_YPRKMX.SCRQ00 is null then
              if  V_YPRKMX.YPSXRQ is not null  then            
                select  to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL;            
              else
                V_SCRQ00 := V_YPRKMX.SCRQ00;                
              end if; 
              else   
			    V_SCRQ00 := V_YPRKMX.SCRQ00;  
           end if;                           
           insert into YK_RKZSM0
                (
                  YKRKPC,
                  YPNM00,
                  ZSM000,
                  ZSMLY0,
                  LYKF00,
                  CZY000,
                  SCPH00,
                  SCRQ00,
                  YXQ000,
                  ZT0000,
                  SFLB00
                 )
              values
                 (
                   V_YKRKPC,
                   cur_CKMX.YPNM00,
                   cur_CKMX.ZSM000,
                   '0',
                   V_CZYKS0,
                   PCZY000,
                   V_YPRKMX.Scph00,
                   V_SCRQ00,
                   V_YPRKMX.YPSXRQ,
                   '0',
                   '3'
                  );                   
         end if;  
     end loop;
    elsif nvl(PZYID00,0) > 0 then
      for cur_YZYPSQ in ( select CKDH00 from  YF_YZYPSQ B where B.ZYID00 = PZYID00 and B.QLZT00 = '2' ) loop     
       for cur_CKMX in  ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.CFLSH0 = cur_YZYPSQ.CKDH00 and A.ZYID00 = PZYID00 and MZZYBZ = '1' and YZLB00 = '1'
                             and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 ))  loop
        --获取药品的入库批次
        select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B  where A.CKDH00 = B.CKDH00 and A.CKDH00 = cur_CKMX.CFLSH0 and A.YPQLPC = cur_CKMX.YPQLPC and YPNM00 = cur_CKMX.YPNM00 and rownum = 1;
        if V_YKRKPC > 0 then       
          select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00;
          if V_YPRKMX.SCRQ00 is null then
              if V_YPRKMX.YPSXRQ is not null  then            
                select  to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL;            
              else
                V_SCRQ00 := V_YPRKMX.SCRQ00;                
              end if; 
              else   
			    V_SCRQ00 := V_YPRKMX.SCRQ00;  
           end if; 
           insert into YK_RKZSM0
                (
                  YKRKPC,
                  YPNM00,
                  ZSM000,
                  ZSMLY0,
                  LYKF00,
                  CZY000,
                  SCPH00,
                  SCRQ00,
                  YXQ000,
                  ZT0000,
                  SFLB00
                 )
              values
                 (
                   V_YKRKPC,
                   cur_CKMX.YPNM00,
                   cur_CKMX.ZSM000,
                   '0',
                   V_CZYKS0,
                   PCZY000,
                   V_YPRKMX.Scph00,
                   V_SCRQ00,
                   V_YPRKMX.YPSXRQ,
                   '0',
                   '3'
                  );                   
         end if;    
       end loop;                                 
    end loop;
    else
      for cur_CKMX in  ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.CFLSH0 = PCFLSH0 and MZZYBZ = '1' and YZLB00 = '1'
                             and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 ))  loop
        --获取药品的入库批次
        select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B  where A.CKDH00 = B.CKDH00 and A.CKDH00 = PCFLSH0 and A.YPQLPC = cur_CKMX.YPQLPC and YPNM00 = CUR_CKMX.YPNM00 and rownum = 1;
        if V_YKRKPC > 0 then       
          select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00;
          if V_YPRKMX.SCRQ00 is null then
              if V_YPRKMX.YPSXRQ is not null then            
                select  to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL;            
              else
                V_SCRQ00 := V_YPRKMX.SCRQ00;                
              end if; 
              else   
			    V_SCRQ00 := V_YPRKMX.SCRQ00;  
           end if; 
           insert into YK_RKZSM0
                (
                  YKRKPC,
                  YPNM00,
                  ZSM000,
                  ZSMLY0,
                  LYKF00,
                  CZY000,
                  SCPH00,
                  SCRQ00,
                  YXQ000,
                  ZT0000,
                  SFLB00
                 )
              values
                 (
                   V_YKRKPC,
                   cur_CKMX.YPNM00,
                   cur_CKMX.ZSM000,
                   '0',
                   V_CZYKS0,
                   PCZY000,
                   V_YPRKMX.Scph00,
                   V_SCRQ00,
                   V_YPRKMX.YPSXRQ,
                   '0',
                   '3'
                  );  
        end if;                
      end loop;        
     
  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 Ecustom       then
    raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
end;
