create or replace procedure SP_YK_YPTJQR(
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhanghr     2012.07.16  create
-- zhagnwz     2012.10.26  解决调价确认后调价明细的确认信息没有根据实际调价情况进行更改.
-- zhangwz     2012.10.27  解决调价单的TJFW00=1(只调药典价格)时功能没有实现的问题。
-- zhangwz     2013.05.22  对于 YK_YPMXZ0.JCSL00 的数据写入由原先的包装单位更改为剂量单位来实现,因为表结果的设计的存储是针对剂量单位.
-- qks         2014.03.26  增加库存物资调价确认功能
-- qks         2014.03.27  库存物资字典调价,同步调整价表字典价格;
-- qks         2015.06.30  修改调用“药品明细账审核”位置;
-- qks         2016.03.09  需要调整批发单价(量采价); by YK9-20160313-002
-- qks         2017.06.08  出现部分调价记录有存在零售盈亏,但是盈亏没有写入到YK_YPMXZ0中问题;
                         --将未确认的入库单现零售单价也需要进行同步更新。 for YK9-20170608-002 
-- qks         2019.04.17  在写入YF_YPMXZ0、YK_YPMXZ0时，增加记录YPRKPC。 for YK9-20190412-001
-- qks         2019.06.13  新增YK_YPTJSFTZKCGJJ：药品调价,范围选择"全部"或"只调库存"时,如果有调整购进价,是否调整现有库存购进价。 for YK9-20190510-002                        
-- qks         2019.08.13  允许购进价、零售价为0; for KCWZ9-20190710-001
-- linshu      2020.06.05  当参数WZ_SFTJDTJYXKS=1时，增加物资库调价的同时也对科室库存进行调价 
-- qks         2021.01.07  修正bug：当批次调价情况下,结存零售金额和结存购进金额取值不对,不能所有批次都使用调价后新价格; for YK9-20210108-001
  Pyptjpc in     char                 ,             --药品调价批次
  Pykbmbh in     number               ,             --药库部门编号
  Pczy000 in     number               ,             --操作员
  Pczyks0 in     number               ,             --操作员科室
  Pcommit in     varchar2 default 'N' ,             --是否提交
  Pzxcgbz out    number               ,             --执行成功标志 1成功 0失败 
  Ptsxx00 out    varchar2             ,             --执行结果提示信息，成功的情况下也可能有警告信息
  Pxxxx00 out    varchar2                           --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标    
)
as
  Vkclsh0 YF_PCKCMX.KCLSH0%type;
  Vmxzlsh YK_YPMXZ0.MXZLSH%type;
  Vzy0000 YK_YPMXZ0.ZY0000%type;
  Vsldj00 YK_YPMXZ0.SLDJ00%type;
  Vslsl00 YK_YPMXZ0.SLSL00%type;
  Vslje00 YK_YPMXZ0.SLJE00%type;
  Vfcdj00 YK_YPMXZ0.FCDJ00%type;
  Vfcsl00 YK_YPMXZ0.FCSL00%type;
  Vfcje00 YK_YPMXZ0.FCJE00%type; 
  Vjcdj00 YK_YPMXZ0.JCDJ00%type;
  Vjcgjdj YK_YPMXZ0.JCGJDJ%type;
  Vrclxbh YK_YPMXZ0.RCLXBH%type;
  Vrclxmc YK_YPMXZ0.RCLXMC%type;
  Vjzxz00 YK_YPMXZ0.JZXZ00%type;
  Vgjslje YK_YPMXZ0.GJSLJE%type;
  Vgjfcje YK_YPMXZ0.GJFCJE%type;
  Vsjkcsl YK_YPMXZ0.JCSL00%type;
  Vk2j000 BM_YD0000.K2J000%type;
  
  Vyxbmbh YK_YPTJ00.YXBMBH%type;
  Vykbmbh BM_YD0000.YKBMBH%type;
  Vkcsl00 BM_YD0000.KCSL00%type;
  Vkcje00 BM_YD0000.KCJE00%type;
  Vkcgjje BM_YD0000.KCGJJE%type;
  Vkcpfje BM_YD0000.KCPFJE%type;
  Vsyje00 BM_YD0000.KCJE00%type;
  Vsfqr00 YK_YPTJ00.SFQR00%type;
  Vzyjcsl BM_YD0000.ZYJCSL%type;
  Vzyjcje BM_YD0000.ZYJCJE%type;
  Vzygjje BM_YD0000.ZYGJJE%type;
  Vsfzfdj BM_YD0000.SFZFDJ%type; --是否政府定价
  Vzfzgdj BM_YD0000.ZFZGDJ%type; --政府最高定价
  Vsfzbyp BM_YD0000.SFZBYP%type; --是否招标药品
  Vztyfjg YK_YPTJ00.ZTYFJG%type; --是否只调药房价格
  Vsfpctj YK_YPTJ00.SFPCTJ%type; --是否批次调价
  Vtjfw00 YK_YPTJ00.TJFW00%type; --调价范围
  Vykthfs XT_XTCS00.VALUE0%type; --0确认时候选择批次(传统方式)，1确认时候不选择批次按录入时候的批次(推荐方式) 默认为0
  Vzrjrcj XT_XTCS00.VALUE0%type; --取出折让是否进入差价参数
  Vyzqwjz XT_XTCS00.VALUE0%type; --是否关闭调价1个礼拜前已经产生但未记账的单据的药品价格
  Vtzgjj0 XT_XTCS00.VALUE0%type; --药品调价,范围选择"全部"或"只调库存"时,如果有调整购进价,是否调整现有库存购进价,1调整,2不调整 默认值为1
  Vbmxz00 BM_BMBM00.BMXZ00%type;
  VSFKSTJ XT_XTCS00.VALUE0%type; --是否进行科室调价
  Vtjyk00     number;                              --调价盈亏
  Vsysdate    date;                                --日期变量
  Vcounter    number(14,4);                        --计数器变
  Ecustom     exception;                           --错误变量  

  cursor C_YPTJMX_YK is                   --定义药品调价单数据集游标
      select * from YK_YPTJMX where  YPTJPC=Pyptjpc and YSBZ00='0' and SFCYTJ='1' and
             YXBMBH in (select BMBH00 from BM_BMBM00 where (BMXZ00='6' or BMXZ00='c'));
  cursor C_YPTJMX_YF is                   --定义药品调价单数据集游标
      select * from YK_YPTJMX where  YPTJPC=Pyptjpc and YSBZ00='0' and 
             YXBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00 in ('2','3','k')) and SFCYTJ='1';
  cursor C_YPTJMX_YSSJ is                 --定义药品调价单数据集游标(调价单原始数据) 用于处理药典价格的同步
      select * from YK_YPTJMX where  YPTJPC=Pyptjpc and YSBZ00='1' and SFCYTJ='1';
  cursor C_YPTJMX_KS is                   --定义科室调价单数据集游标
      select * from YK_YPTJMX where  YPTJPC=Pyptjpc and YSBZ00='0' and 
        YXBMBH not in (select BMBH00 from BM_BMBM00 where BMXZ00 in ('2','3','k','6','c')) and SFCYTJ='1';    
  --cursor C_YPTJMX_KS is                   --定义科室调价单数据集游标
  --   select a.yfbmbh, b.* from  KS_PCKCMX a,YK_YPTJMX b where a.ypnm00=b.ypnm00 and a.ykrkpc=b.YPRKPC and b.YPTJPC=Pyptjpc;              
             
begin
  Pzxcgbz := 0;
  Vsysdate := sysdate;
  Pxxxx00 :=nvl(to_char(Pyptjpc),'null')||','||nvl(to_char(Pykbmbh),'null')||','||
            nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(Pcommit,'null');
  SP_TransLog(Vsysdate,'SP_YK_YPTJQR',Pczy000,Pczyks0,Pxxxx00);  
  
  --0确认时候选择批次(传统方式)，1确认时候不选择批次按录入时候的批次(推荐方式) 默认为0
  select nvl(max(trim(VALUE0)),'0') into Vykthfs from XT_XTCS00 where NAME00='YK_YKTHFS';   

  --取出折让是否进入差价参数
  select nvl(max(trim(VALUE0)),'Y') into Vzrjrcj from XT_XTCS00 where NAME00='YK_ZRJRCJ'; 

  --是否关闭调价1个礼拜前已经产生但未记账的单据的药品价格
  select nvl(max(trim(VALUE0)),'N') into Vyzqwjz from XT_XTCS00 where NAME00='YK_SFTZYZWJZDJYPJG';
  
  --调价时是否调整目前现有库存的购进价
  select nvl(max(trim(value0)),'1') into Vtzgjj0 from XT_XTCS00 where name00='YK_YPTJSFTZKCGJJ';  
  
  --调价时是否调价科室库存的价格
   select nvl(max(trim(value0)),'0') into VSFKSTJ from XT_XTCS00 where name00='WZ_SFTJDTJYXKS';  
  
     
  --取出是否确认，是否只调药房价格
  select SFQR00,nvl(ZTYFJG,'N'),SFPCTJ,TJFW00,YKBMBH,YXBMBH into Vsfqr00,Vztyfjg,Vsfpctj,Vtjfw00,Vykbmbh,Vyxbmbh
    from YK_YPTJ00 where YPTJPC=Pyptjpc;
  if Vykbmbh<>Pykbmbh then
     Pxxxx00:='传入的药库部门内码不对，请检查确认！';
     raise Ecustom;
  end if;
  select BMXZ00 into Vbmxz00 from BM_BMBM00 where BMBH00=Pykbmbh;
  
  --药库盘点期间不允许调价
  select nvl(max(BMBH00),-3) into Vcounter from BM_BMBM00 where FYBZ00='3' and instrb(','||Vyxbmbh||',',','||to_char(BMBH00)||',')>0;
  if Vcounter<>-3 then
     select BMMC00 into Pxxxx00 from BM_BMBM00 where BMBH00=Vcounter;
     Pxxxx00:=Pxxxx00||'正在盘点，盘点期间不允许调价！';
     raise Ecustom;
  end if;
  --有效性判断
  if Vsfqr00='Y' then
      Ptsxx00 := '该次调价已经被确认过了,不能再确认!*';
      raise Ecustom;
  end if;
  if Vsfpctj='1' then --按批次调价
     select count(*) into Vcounter from YK_YPTJMX where YPTJPC=Pyptjpc and YPRKPC is null and YSBZ00='1';
     if Vcounter>0 then
        Ptsxx00 := '本次调价模式为按批次调价，请检查是不是所有调价记录都有批次?';
        raise Ecustom;
     end if;
/*
     --更新库房原单价  
     update YK_YPTJMX A set (YPFDJ0,YLSDJ0) = 
                     (select decode(Vzrjrcj,'Y',ROUND(X.GJDJ00*D.K2J000/X.ZHL000,4),ROUND(X.FPGJDJ*D.K2J000/X.ZHL000,4)),
                             round(X.XXLSDJ*D.K2J000/X.ZHL000,4)
                      from   YK_YPRKD0 R,YK_YPRKMX X,BM_YD0000 D 
                      where  R.RKDH00=X.RKDH00 AND X.YPNM00=D.YPNM00 and r.YKBMBH=Pykbmbh
                     )
            where YPTJPC=Pyptjpc and YXBMBH=Pykbmbh;
     --更新库存数
     update YK_YPTJMX A set (ZMKC00,SJKC00)
                          = (select sum(round(nvl(X.KCSL00/B.K2J000,0),3)),sum(round(nvl(X.KCSL00/B.K2J000,0),3))
                             from BM_YD0000 B,YK_YPKCXX X
            where A.YPNM00=B.YPNM00  and B.YPNM00=X.YPNM00 and X.YKBMBH=Pykbmbh)
      where YPTJPC=Pyptjpc and YXBMBH=Pykbmbh;
*/
  else
     select count(*) into Vcounter from YK_YPTJMX where YPTJPC=Pyptjpc and YPRKPC is not null and YSBZ00='1';
     if Vcounter>0 then
        Ptsxx00 := '本次调价模式为按品种调价，不能指定批次进行调价?';
        raise Ecustom;
     end if;   
/*
      --更新原单价  
      update YK_YPTJMX A set (YPFDJ0,YLSDJ0) = (select GJJ000,LSJ000 from BM_YD0000 B where A.YPNM00=B.YPNM00)
      where A.YXBMBH=Pykbmbh and YPTJPC=Pyptjpc;
      --更新库存
      update YK_YPTJMX A set (ZMKC00,SJKC00)
                     = (select sum(round(nvl(X.KCSL00/B.K2J000,0),3)),sum(round(nvl(X.KCSL00/B.K2J000,0),3))
                        from BM_YD0000 B,YK_YPKCXX X
                        where A.YPNM00=B.YPNM00  and B.YPNM00=X.YPNM00 and x.YKBMBH=Pykbmbh)
      where A.YXBMBH=Pykbmbh and YPTJPC=Pyptjpc;
*/
  end if;
  
  --药典调价  
  --2019.06.13 药典调价移到最前面优先处理，调价确认后把YK_YPTJMX中的现购进价、现批发价直接改为原购进价、原批发价; for YK9-20190510-002
  if Vtjfw00 in ('0','1') then 
--     for tjmx in C_YPTJMX_YK loop
       for tjmx in C_YPTJMX_YSSJ loop       
         update BM_YD0000 set LSJ000=tjmx.XLSDJ0,
                              GJJ000=tjmx.XPFDJ0,
                              PFJ000=tjmx.XPFDJ1,
                              --YPJCL0=round(tjmx.XLSDJ0/tjmx.XPFDJ0,4)-1  --同步更新折扣率。
                              --2019.08.13 解决除数为0
                              YPJCL0=decode(tjmx.XPFDJ0,0,0,round(tjmx.XLSDJ0/tjmx.XPFDJ0,4)-1)  --同步更新折扣率。
                where YPNM00=tjmx.YPNM00;            
         --update YK_YPKCXX set LSJ000=tjmx.XLSDJ0, GJJ000=tjmx.XPFDJ0 where YPNM00=tjmx.YPNM00 ;
         --2019.06.13 Vtzgjj0='2'不调整购进价                      
         update YK_YPKCXX set LSJ000=tjmx.XLSDJ0, GJJ000=decode(Vtzgjj0,'2',GJJ000,tjmx.XPFDJ0) where YPNM00=tjmx.YPNM00 ;      
          --启用药品标记？
          --update BM_YD0000 set QYRQ00=to_char(Vsysdate,'YYYYMMDD') where YPNM00=tjmx.YPNM00 and QYRQ00 is null;
         --库存物资调价,同步调整收费字典价格
         if Vbmxz00 = 'c' then
           Update BM_YYSFXM set SFJE00=tjmx.XLSDJ0, 
                                BZJE00=tjmx.XPFDJ0,
                                --FDBL00=round(tjmx.XLSDJ0/tjmx.XPFDJ0,2)-1,
                                --2019.08.13 解决除数为0
                                FDBL00=decode(tjmx.XPFDJ0,0,0,round(tjmx.XLSDJ0/tjmx.XPFDJ0,2)-1),
                                SFSX00=tjmx.XLSDJ0, 
                                SFXX00=least(SFXX00,tjmx.XLSDJ0),
                                XGR000=Pczy000,
                                XGRQSJ=to_char(sysdate,'YYYYMMDDHH24:MI:SS')
                          where SFXMID=(select SFXMID from BM_WZQTXX where YPNM00=tjmx.YPNM00) and JSXM00='2';         
         end if;      
     end loop;
  end if;
  
  --2019.06.13 药典调价移到最前面优先处理，调价确认后把YK_YPTJMX中的现购进价、现批发价直接改为原购进价、原批发价; for YK9-20190510-002 
  if Vtzgjj0 = '2' and Vtjfw00<>'1' then
    update YK_YPTJMX set XPFDJ0=YPFDJ0,XPFDJ1=YPFDJ1,BZ0000=substrb(BZ0000||'购进价:'||XPFDJ0||',批发价:'||XPFDJ1,1,100) 
     where YPTJPC=Pyptjpc and (XPFDJ0<>YPFDJ0 or XPFDJ1<>YPFDJ1);
  end if;

  --重新刷新药库的库存
  SP_YK_YPTJYKSY(Pyptjpc,Pykbmbh,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
  
  --重新刷新药房的库存
  SP_YK_YPTJYFSY(Pyptjpc,Pykbmbh,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
  
  --重新刷新科室的库存
  if VSFKSTJ = '1' and Vbmxz00 = 'c' then
    SP_YK_YPTJKSSY(Pyptjpc,Pykbmbh,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
  end if;  


  --药库调价
  if Vztyfjg in ('N','T') then   /*‘N’时只调药房价格即(当药库允许多价格，药房不允许零售价单独管理时不处理药库调价，只处理药房调价）*/    
     for tjmx in C_YPTJMX_YK loop
     --*********************1.更新药品零售价、购进价、批发价***************
          if Vtjfw00=0 or Vtjfw00=1 then
            update YK_YPKCXX set LSJ000=tjmx.XLSDJ0, GJJ000=tjmx.XPFDJ0,PFJ000=tjmx.XPFDJ1 where YPNM00=tjmx.YPNM00 and YKBMBH=tjmx.YXBMBH;
          end if;
          
          --从后面移到这,为了计算Vkcpfje
          update YK_YPRKMX set XXLSDJ=round(tjmx.XLSDJ0*ZHL000/tjmx.ZHL000,4),
                               GJDJ00=round(tjmx.XPFDJ0*ZHL000/tjmx.ZHL000,4),
                               FPGJDJ=round(tjmx.XPFDJ0*ZHL000/tjmx.ZHL000,4),
                               PFDJ00=round(tjmx.XPFDJ1*ZHL000/tjmx.ZHL000,4)
                where YPNM00=tjmx.YPNM00 and YPRKPC=tjmx.YPRKPC; 
                         
          select sum(PCKC00),nvl(sum(round(PCKC00*PFDJ00,2)),0) into Vkcsl00,Vkcpfje from VW_YK_YPPCKC where YPNM00=tjmx.YPNM00 and YKBMBH=tjmx.YXBMBH;
          --  *********************2.记账**************************
          select SFZFDJ,ZFZGDJ,SFZBYP into Vsfzfdj,Vzfzgdj,Vsfzbyp from BM_YD0000 where YPNM00=tjmx.YPNM00;
          --初始化损益金额
          Vsyje00:=0;
          --计算药典账面金额，只考虑零售金额
          select nvl(ZYJCJE,0)+round(tjmx.SJKC00*(tjmx.XLSDJ0-tjmx.YLSDJ0),2),
                 nvl(KCJE00,0)+round(tjmx.SJKC00*(tjmx.XLSDJ0-tjmx.YLSDJ0),2),                               
                 nvl(ZYGJJE,0)+round(tjmx.SJKC00*(tjmx.XPFDJ0-tjmx.YPFDJ0),2),
                 nvl(KCGJJE,0)+round(tjmx.SJKC00*(tjmx.XPFDJ0-tjmx.YPFDJ0),2)
                into Vzyjcje,Vkcje00,Vzygjje,Vkcgjje
                from VW_YK_YPZMCX
                where YPNM00=tjmx.YPNM00 and YKBMBH=Pykbmbh;
          if Vkcje00<0 then
             Vkcje00:=0;
          end if;
          --有隶属药品功能          
            update YK_YPKCXX set ZYJCJE=Vzyjcje,
                                 KCJE00=Vkcje00,
                                 ZYGJJE=Vzygjje,
                                 KCGJJE=Vkcgjje
                   where YPNM00=tjmx.YPNM00 and YKBMBH=tjmx.YXBMBH;
          --记账
          --Vsyje00 :=round((tjmx.XLSDJ0-tjmx.YLSDJ0)*round(tjmx.SJKC00/tjmx.ZHL000,4),2);  --调价损益
          --2017.06.07
          Vsyje00 :=round((tjmx.XLSDJ0-tjmx.YLSDJ0)*tjmx.SJKC00,2);  --调价损益
          Vmxzlsh := null; 
          --2016.03.09  需要调整批发单价(量采价)             
          if (Vsyje00>0) or (tjmx.XPFDJ0>tjmx.YPFDJ0) or (tjmx.XPFDJ1>tjmx.YPFDJ1) then --调盈  
              Vtjyk00 := 1;
          end if;  
          if (Vsyje00<0) or (tjmx.XPFDJ0<tjmx.YPFDJ0) or (tjmx.XPFDJ1<tjmx.YPFDJ1) then --调亏
             Vtjyk00 := -1;
          end if;    
          if Vtjyk00 in (-1, 1) then
                  select decode(Vtjyk00,1,'调价报盈','调价报亏') into Vzy0000 from dual;
                  select decode(Vtjyk00,1,tjmx.XLSDJ0,null) into Vsldj00 from dual;
                  select decode(Vtjyk00,1,0,null) into Vslsl00 from dual;
                  select decode(Vtjyk00,1,round(tjmx.SJKC00*(tjmx.XLSDJ0-tjmx.YLSDJ0),2),null) into Vslje00 from dual;                                  
                  select decode(Vtjyk00,1,null,tjmx.XLSDJ0) into Vfcdj00 from dual;
                  select decode(Vtjyk00,1,null,0) into Vfcsl00 from dual;
                  select decode(Vtjyk00,1,null,-1*round(tjmx.SJKC00*(tjmx.XLSDJ0-tjmx.YLSDJ0),2)) into Vfcje00 from dual;
                  select decode(Vkcsl00,0,0,round(Vkcje00/Vkcsl00,4)) into Vjcdj00 from dual;
                  select decode(Vkcsl00,0,0,round(Vkcgjje/Vkcsl00,4)) into Vjcgjdj from dual;
                  select decode(Vtjyk00,1,'09','08') into Vrclxbh from dual;
                  select decode(Vtjyk00,1,'调价报盈','调价报亏') into Vrclxmc from dual;
                  select decode(Vtjyk00,1,'R','C') into Vjzxz00 from dual;
                  Vgjslje:=round((tjmx.XPFDJ0-tjmx.YPFDJ0)*tjmx.SJKC00,2);
                  select decode(sign(Vgjslje),-1,abs(Vgjslje),0) into  Vgjfcje from dual;
                  select decode(sign(Vgjslje), 1,Vgjslje,0) into  Vgjslje from dual;
                  select K2J000 into Vk2j000 from BM_YD0000 where YPNM00=tjmx.YPNM00;
                                        
                  SP_YK_YPMXZ0_EDIT(0, tjmx.YPTJPC, Vmxzlsh, Pykbmbh, tjmx.YPRKPC, null, tjmx.YPNM00, tjmx.YPMC00, tjmx.YPGG00, 
                                    to_char(Vsysdate,'yyyymmdd'), to_char(Vsysdate,'hh24:mi:ss'), 77777, Vzy0000, tjmx.TJDW00, 
                                    tjmx.ZHL000, tjmx.XPFDJ0, tjmx.XPFDJ1, --tjmx.XPFDJ0, 
                                    Vsldj00, Vslsl00, Vslje00, Vfcdj00, Vfcsl00, Vfcje00, 
                                    Vjcdj00, Vkcsl00*Vk2j000, Vkcje00, Vjcgjdj, Vkcgjje, Vkcpfje, Vrclxbh, Vrclxmc, Vjzxz00, 0, Vzyjcsl, 
                                    Vzyjcje, Vzygjje, Vsfzfdj, Vzfzgdj, Vsfzbyp, null, null, null, null,
                                    Vgjslje, Vgjfcje, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00);
                  if Pzxcgbz = 0 then
                      Ptsxx00 := '药品明细账操作错误(2)';
                      raise Ecustom;
                  end if;
                  /*
                  SP_YK_YPMXZ0_AUDIT(tjmx.YPTJPC,to_char(Vsysdate,'yyyymmdd'),to_char(Vsysdate,'hh24:mi:ss'),Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
                  if Pzxcgbz = 0 then
                      Ptsxx00 := '药品明细账审核出错(2)';
                      raise Ecustom;
                  end if; 
                  */                     
          end if;                                                                                 
          --考虑到退库，所以不论批次库存是否为0，将现行零售价都调
          /*移动到前面
          update YK_YPRKMX set XXLSDJ=round(tjmx.XLSDJ0*ZHL000/tjmx.ZHL000,4),
                               GJDJ00=round(tjmx.XPFDJ0*ZHL000/tjmx.ZHL000,4),
                               FPGJDJ=round(tjmx.XPFDJ0*ZHL000/tjmx.ZHL000,4),
                               PFDJ00=round(tjmx.XPFDJ1*ZHL000/tjmx.ZHL000,4)
                where YPNM00=tjmx.YPNM00 and YPRKPC=tjmx.YPRKPC;
          */
     end loop;
         --2015.07.01
         if Vtjyk00 in (-1, 1) then
               SP_YK_YPMXZ0_AUDIT(Pyptjpc,to_char(Vsysdate,'yyyymmdd'),to_char(Vsysdate,'hh24:mi:ss'),Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
                  if Pzxcgbz = 0 then
                      Ptsxx00 := '药品明细账审核出错(2)';
                      raise Ecustom;
                  end if; 
         end if;         
  end if; 
  
  --药房调价

  for tjmx in C_YPTJMX_YF loop    
      update YF_YPKCXX set LSDJ00=tjmx.XLSDJ0,GJDJ00=tjmx.XPFDJ0 where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;      
      update YF_PCKCMX set GJDJ00=tjmx.XPFDJ0,LSDJ00=tjmx.XLSDJ0 
             where decode(YKRKPC,0,KCLSH0,YKRKPC)=tjmx.YPRKPC and YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      select round(sum(SJKCSL)/tjmx.ZHL000,4),sum(SJKCSL) into Vkcsl00,Vsjkcsl from YF_PCKCMX where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      --影响药房记账
      --select sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XLSDJ0),sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XPFDJ0) 
      --     into Vkcje00,Vkcgjje from YF_PCKCMX
      --     where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      
      --2021.01.07 修正bug：当批次调价情况下,结存零售金额和结存购进金额取值不对,不能所有批次都使用调价后新价格     
      select sum(round(a.SJKCSL*a.LSDJ00/b.k2j000,2)),sum(round(a.SJKCSL*a.GJDJ00/b.k2j000,2))
        into Vkcje00,Vkcgjje
        from YF_PCKCMX a,BM_YD0000 b 
       where a.YFBMBH=tjmx.YXBMBH and a.YPNM00=tjmx.YPNM00 and a.YPNM00=b.YPNM00;      
                  
      --更新零售单价
      if (tjmx.XLSDJ0>tjmx.YLSDJ0) OR (tjmx.XPFDJ0>tjmx.YPFDJ0)  then  --报盈
                  insert into YF_YPMXZ0(
                    MXZLSH,    --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH
                    YFBMBH,    --药房部门编号
                    YPRKPC,    --药品入库批次-->YF_YPRKMX
                    YPNM00,    --药品内码
                    YPMC00,    --药品名称
                    YPGG00,    --规格
                    JZRQ00,    --记账日期
                    JZSJ00,    --记账时间
                    CRBMBH,    --出入部门编号
                    ZY0000,    --摘要(自动：出入部门名称)
                    JZDW00,    --记账单位
                    ZHL000,    --转换率(记账单位/剂量单位)
                    GJDJ00,    --购进单价(记账单位)
                    SLDJ00,    --收入单价(零售价)(记账单位)
                    SLSL00,    --收入数量(记账单位)
                    SLJE00,    --收入金额(零售价)
                    JCDJ00,    --结存单价(结存金额/结存数量)
                    JCSL00,    --结存数量(剂量单位)(药品总量)
                    JCJE00,    --结存金额(零售价)(药品总量)
                    RCLXBH,    --入出库类型编码:BM_YFRKLX,BM_YFCKLX
                    RCLXMC,    --入出库类型名称:BM_YFRKLX,BM_YFCKLX
                    JZXZ00,    --记账性质:'R',入库,'C',出库
                    GJSLJE,
                    GJFCJE,
                    JCGJJE,
                    DJH000,
                    YKRKPC 
                    )
                  values
                    (SQ_YF_YPMXZ0_MXZLSH.nextval,
                    tjmx.YXBMBH,
                    tjmx.YPTJPC,
                    tjmx.YPNM00,
                    tjmx.YPMC00,
                    tjmx.YPGG00,
                    to_char(Vsysdate,'yyyymmdd'),
                    to_char(Vsysdate,'hh24:mi:ss'),
                    77777,
                    '调价报盈',
                    tjmx.TJDW00,
                    tjmx.ZHL000,
                    tjmx.XPFDJ0,
                    tjmx.XLSDJ0,
                    0,
                    ROUND((tjmx.XLSDJ0-tjmx.YLSDJ0)*tjmx.SJKC00,2),
                    DECODE(Vkcsl00,0,0,ROUND(Vkcje00/Vkcsl00,4)),
                    Vsjkcsl,
                    Vkcje00,
                    '07',
                    '调价报盈',
                    'R',
                    round((tjmx.XPFDJ0-tjmx.YPFDJ0)*tjmx.sjkc00,2),
                    0,
                    Vkcgjje,
                    Pyptjpc,
                    tjmx.YPRKPC
                    );
      elsif (tjmx.XLSDJ0<tjmx.YLSDJ0) OR (tjmx.xpfdj0<tjmx.ypfdj0) then                  --报亏
                  insert into YF_YPMXZ0(
                    MXZLSH,    --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH
                    YFBMBH,    --药房部门编号
                    YPCKPC,    --药品出库批次-->YF_YPCKMX
                    YPNM00,    --药品内码
                    YPMC00,    --药品名称
                    YPGG00,    --规格
                    JZRQ00,    --记账日期
                    JZSJ00,    --记账时间
                    CRBMBH,    --出入部门编号
                    ZY0000,    --摘要(自动：出入部门名称)
                    JZDW00,    --记账单位
                    ZHL000,    --转换率(记账单位/剂量单位)
                    GJDJ00,    --购进单价(记账单位)
                    FCDJ00,    --付出单价(零售价)(记账单位)
                    FCSL00,    --付出数量(记账单位)
                    FCJE00,    --付出金额(零售价)
                    JCDJ00,    --结存单价(结存金额/结存数量)
                    JCSL00,    --结存数量(剂量单位)(药品总量)
                    JCJE00,    --结存金额(零售价)(药品总量)
                    RCLXBH,    --入出库类型编码:BM_YFRKLX,BM_YFCKLX
                    RCLXMC,    --入出库类型名称:BM_YFRKLX,BM_YFCKLX
                    JZXZ00,
                    GJSLJE,
                    GJFCJE,
                    JCGJJE,
                    DJH000,
                    YKRKPC 
                        )    --记账性质:'R',入库,'C',出库
                  values
                    (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL,
                    tjmx.YXBMBH,
                    tjmx.YPTJPC,
                    tjmx.YPNM00,
                    tjmx.YPMC00,
                    tjmx.YPGG00,
                    to_char(Vsysdate,'yyyymmdd'),
                    to_char(Vsysdate,'hh24:mi:ss'),
                    77777,
                    '调价报亏',
                    tjmx.TJDW00,
                    tjmx.ZHL000,
                    tjmx.XPFDJ0,
                    tjmx.XLSDJ0,
                    0,
                    -ROUND((tjmx.XLSDJ0-tjmx.YLSDJ0)*tjmx.SJKC00,2),
                    DECODE(Vkcsl00,0,0,ROUND(Vkcje00/Vkcsl00,4)),
                    Vsjkcsl,
                    Vkcje00,
                    '06',
                    '调价报亏',
                    'C',
                    0,
                    -round((tjmx.XPFDJ0-tjmx.YPFDJ0)*tjmx.SJKC00,2),
                    Vkcgjje,
                    Pyptjpc,
                    tjmx.YPRKPC 
                    );
              
      end if;
      if Vyzqwjz='N' then
                  --调整调价1个礼拜前已经产生但未记账的单据的药品价格begin
                  --门诊处方
                  update YF_MZCFMX a set LSDJ00=round(tjmx.XLSDJ0*a.ZHL000/tjmx.ZHL000,4),
                                         GJDJ00=round(tjmx.XPFDJ0*a.ZHL000/tjmx.ZHL000,4)
                          where a.YPNM00=tjmx.YPNM00 and a.CXBZ00 not in('-','+') 
                          and exists (select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 
                                      and SRRQ00>=to_char(sysdate-7,'yyyymmdd') and CFZT00='0' );
                  --住院处方
                  update YF_ZYCFMX a set LSDJ00=round(tjmx.XLSDJ0*a.ZHL000/tjmx.ZHL000,4),
                                         GJDJ00=round(tjmx.XPFDJ0*a.ZHL000/tjmx.ZHL000,4)
                          where a.YPNM00=tjmx.YPNM00 and a.CXBZ00 not in('-','+') 
                          and exists (select 1 from YF_ZYCF00 where CFLSH0=a.CFLSH0 
                                      and SRRQ00>=to_char(sysdate-7,'yyyymmdd') and CFZT00='0' );
                  --医嘱
                  update YF_YZYPSQ a set LSDJ00=round(tjmx.XLSDJ0*a.ZHL000/tjmx.ZHL000,4),
                                         GJDJ00=round(tjmx.XPFDJ0*a.ZHL000/tjmx.ZHL000,4)
                          where a.YPNM00=tjmx.YPNM00 and QLZT00='0' and a.CXBZ00 not in('-','+')  and SRRQ00>=to_char(sysdate-7,'YYYYMMDD');
      end if;
              --调整调价1个月前已经产生但未记账的单据的药品价格end  

  end loop;
  
  
   --科室调价开始

  for tjmx in C_YPTJMX_KS loop 
      update KS_YPKCXX set LSDJ00=tjmx.XLSDJ0,GJDJ00=tjmx.XPFDJ0 where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;      
      update KS_PCKCMX set GJDJ00=tjmx.XPFDJ0,LSDJ00=tjmx.XLSDJ0 
             where decode(YKRKPC,0,KCLSH0,YKRKPC)=tjmx.YPRKPC and YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      select round(sum(SJKCSL)/tjmx.ZHL000,4),sum(SJKCSL) into Vkcsl00,Vsjkcsl from KS_PCKCMX where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      --影响药房记账
      select sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XLSDJ0),sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XPFDJ0) 
           into Vkcje00,Vkcgjje from KS_PCKCMX
           where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YXBMBH;
      /*update KS_YPKCXX set LSDJ00=tjmx.XLSDJ0,GJDJ00=tjmx.XPFDJ0 where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;   
      if Vsfpctj = '1' then     --按批次         
          update KS_PCKCMX set GJDJ00=tjmx.XPFDJ0,LSDJ00=tjmx.XLSDJ0 
                 where decode(YKRKPC,0,KCLSH0,YKRKPC)=tjmx.YPRKPC and YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;
          select round(sum(SJKCSL)/tjmx.ZHL000,4),sum(SJKCSL) into Vkcsl00,Vsjkcsl from KS_PCKCMX where YPNM00=tjmx.YPNM00 and  decode(YKRKPC,0,KCLSH0,YKRKPC)=tjmx.YPRKPC and YFBMBH=tjmx.YFBMBH;
          --影响药房记账
          select sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XLSDJ0),sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XPFDJ0) 
               into Vkcje00,Vkcgjje from KS_PCKCMX
               where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;
        else    --按品种
           update KS_PCKCMX set GJDJ00=tjmx.XPFDJ0,LSDJ00=tjmx.XLSDJ0 
                 where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;
          select round(sum(SJKCSL)/tjmx.ZHL000,4),sum(SJKCSL) into Vkcsl00,Vsjkcsl from KS_PCKCMX where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;
          --影响药房记账
          select sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XLSDJ0),sum(round(SJKCSL/tjmx.ZHL000,4)*tjmx.XPFDJ0) 
               into Vkcje00,Vkcgjje from KS_PCKCMX
               where YPNM00=tjmx.YPNM00 and YFBMBH=tjmx.YFBMBH;
        end if; */      
      --更新零售单价
      if (tjmx.XLSDJ0>tjmx.YLSDJ0) OR (tjmx.XPFDJ0>tjmx.YPFDJ0)  then  --报盈
                  insert into YF_YPMXZ0(
                    MXZLSH,    --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH
                    YFBMBH,    --药房部门编号
                    YPRKPC,    --药品入库批次-->YF_YPRKMX
                    YPNM00,    --药品内码
                    YPMC00,    --药品名称
                    YPGG00,    --规格
                    JZRQ00,    --记账日期
                    JZSJ00,    --记账时间
                    CRBMBH,    --出入部门编号
                    ZY0000,    --摘要(自动：出入部门名称)
                    JZDW00,    --记账单位
                    ZHL000,    --转换率(记账单位/剂量单位)
                    GJDJ00,    --购进单价(记账单位)
                    SLDJ00,    --收入单价(零售价)(记账单位)
                    SLSL00,    --收入数量(记账单位)
                    SLJE00,    --收入金额(零售价)
                    JCDJ00,    --结存单价(结存金额/结存数量)
                    JCSL00,    --结存数量(剂量单位)(药品总量)
                    JCJE00,    --结存金额(零售价)(药品总量)
                    RCLXBH,    --入出库类型编码:BM_YFRKLX,BM_YFCKLX
                    RCLXMC,    --入出库类型名称:BM_YFRKLX,BM_YFCKLX
                    JZXZ00,    --记账性质:'R',入库,'C',出库
                    GJSLJE,
                    GJFCJE,
                    JCGJJE,
                    DJH000,
                    YKRKPC 
                    )
                  values
                    (SQ_YF_YPMXZ0_MXZLSH.nextval,
                    tjmx.YXBMBH,
                    tjmx.YPTJPC,
                    tjmx.YPNM00,
                    tjmx.YPMC00,
                    tjmx.YPGG00,
                    to_char(Vsysdate,'yyyymmdd'),
                    to_char(Vsysdate,'hh24:mi:ss'),
                    77777,
                    '调价报盈',
                    tjmx.TJDW00,
                    tjmx.ZHL000,
                    tjmx.XPFDJ0,
                    tjmx.XLSDJ0,
                    0,
                    ROUND((tjmx.XLSDJ0-tjmx.YLSDJ0)*tjmx.SJKC00,2),
                    DECODE(Vkcsl00,0,0,ROUND(Vkcje00/Vkcsl00,4)),
                    Vsjkcsl,
                    Vkcje00,
                    '07',
                    '调价报盈',
                    'R',
                    round((tjmx.XPFDJ0-tjmx.YPFDJ0)*tjmx.sjkc00,2),
                    0,
                    Vkcgjje,
                    Pyptjpc,
                    tjmx.YPRKPC
                    );
      elsif (tjmx.XLSDJ0<tjmx.YLSDJ0) OR (tjmx.xpfdj0<tjmx.ypfdj0) then                  --报亏
                  insert into YF_YPMXZ0(
                    MXZLSH,    --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH
                    YFBMBH,    --药房部门编号
                    YPCKPC,    --药品出库批次-->YF_YPCKMX
                    YPNM00,    --药品内码
                    YPMC00,    --药品名称
                    YPGG00,    --规格
                    JZRQ00,    --记账日期
                    JZSJ00,    --记账时间
                    CRBMBH,    --出入部门编号
                    ZY0000,    --摘要(自动：出入部门名称)
                    JZDW00,    --记账单位
                    ZHL000,    --转换率(记账单位/剂量单位)
                    GJDJ00,    --购进单价(记账单位)
                    FCDJ00,    --付出单价(零售价)(记账单位)
                    FCSL00,    --付出数量(记账单位)
                    FCJE00,    --付出金额(零售价)
                    JCDJ00,    --结存单价(结存金额/结存数量)
                    JCSL00,    --结存数量(剂量单位)(药品总量)
                    JCJE00,    --结存金额(零售价)(药品总量)
                    RCLXBH,    --入出库类型编码:BM_YFRKLX,BM_YFCKLX
                    RCLXMC,    --入出库类型名称:BM_YFRKLX,BM_YFCKLX
                    JZXZ00,
                    GJSLJE,
                    GJFCJE,
                    JCGJJE,
                    DJH000,
                    YKRKPC 
                        )    --记账性质:'R',入库,'C',出库
                  values
                    (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL,
                    tjmx.YXBMBH,
                    tjmx.YPTJPC,
                    tjmx.YPNM00,
                    tjmx.YPMC00,
                    tjmx.YPGG00,
                    to_char(Vsysdate,'yyyymmdd'),
                    to_char(Vsysdate,'hh24:mi:ss'),
                    77777,
                    '调价报亏',
                    tjmx.TJDW00,
                    tjmx.ZHL000,
                    tjmx.XPFDJ0,
                    tjmx.XLSDJ0,
                    0,
                    -ROUND((tjmx.XLSDJ0-tjmx.YLSDJ0)*tjmx.SJKC00,2),
                    DECODE(Vkcsl00,0,0,ROUND(Vkcje00/Vkcsl00,4)),
                    Vsjkcsl,
                    Vkcje00,
                    '06',
                    '调价报亏',
                    'C',
                    0,
                    -round((tjmx.XPFDJ0-tjmx.YPFDJ0)*tjmx.SJKC00,2),
                    Vkcgjje,
                    Pyptjpc,
                    tjmx.YPRKPC 
                    );
              
      end if;  

  end loop;
  -- 科室调价结束

  --*********************3.  将未确认的入库单、请领单相关药品单价进行同步更新
  for tjmx in C_YPTJMX_YK loop
      --非退库单不修改购进价   
      --2017.06.07 同时需要一起修改现零售单价: ,XXLSDJ=tjmx.XLSDJ0 
      update YK_YPRKMX set LSDJ00=tjmx.XLSDJ0,XXLSDJ=tjmx.XLSDJ0 where YPNM00=tjmx.YPNM00 
             and RKDH00 in (select RKDH00 from YK_YPRKD0 where RKZT00='0' and RKLXBH<>'06' and YKBMBH=tjmx.YXBMBH);
      --退库单修改购进价 
      --2017.06.07 同时需要一起修改现零售单价: ,XXLSDJ=tjmx.XLSDJ0
      update YK_YPRKMX set LSDJ00=tjmx.XLSDJ0, FPGJDJ=tjmx.XPFDJ0, GJDJ00=tjmx.XPFDJ0,PFDJ00=tjmx.XPFDJ1,XXLSDJ=tjmx.XLSDJ0
             where YPNM00=tjmx.YPNM00
               and RKDH00 in (select RKDH00 from YK_YPRKD0 where RKZT00='0' and RKLXBH='06' and YKBMBH=tjmx.YXBMBH);

      update YK_YPQLMX set QLDJ00=round(tjmx.XLSDJ0*tjmx.ZHL000/ZHL000,4),PFDJ00=round(tjmx.XPFDJ1*tjmx.ZHL000/ZHL000,4)
             where YPNM00=tjmx.YPNM00
               and YPQLDH in (select YPQLDH from YK_YPQLD0 where QLZT00='0' and YKBMBH=tjmx.YXBMBH);
     
      if Vykthfs='1'  then                      
         update YK_YPQLMX set GJDJ00=round(tjmx.XPFDJ0*tjmx.ZHL000/ZHL000,4),PFDJ00=round(tjmx.XPFDJ1*tjmx.ZHL000/ZHL000,4)              
                where YPNM00=tjmx.YPNM00     
                  and YPQLDH in (select YPQLDH from YK_YPQLD0 where QLZT00='0' and CKLXBH<>'09' and YKBMBH=tjmx.YXBMBH);
      else                       
         update YK_YPQLMX set GJDJ00=round(tjmx.XPFDJ0*tjmx.ZHL000/ZHL000,4),PFDJ00=round(tjmx.XPFDJ1*tjmx.ZHL000/ZHL000,4)
                where YPNM00=tjmx.YPNM00 
                  and YPQLDH in (select YPQLDH from YK_YPQLD0 where QLZT00='0' and YKBMBH=tjmx.YXBMBH);                  
      end if;   
  end loop;                   
  --*********************4.药品调价表确认*********************
  --if  Vztyfjg='Y'  then /*Vztyfjg='Y'即（当药库允许多价格，药房不允许零售价单独管理时不处理药库调价，只处理药房调价）*/
  --    delete from YK_YPTJMX where  YPTJPC=Pyptjpc and YXBMBH=Pykbmbh;
  --end if;
  update YK_YPTJMX set SFQR00='Y', QRRBH0=Pczy000, QRRQ00=to_char(Vsysdate,'yyyymmdd'), QRSJ00=to_char(Vsysdate,'hh24:mi:ss')
   where YPTJPC=Pyptjpc and YSBZ00='0' and SFCYTJ='1'; --and YXBMBH=Pykbmbh;

  update YK_YPTJ00 set SFQR00='Y', QRRBH0=Pczy000, QRRQ00=to_char(Vsysdate,'yyyymmdd'), QRSJ00=to_char(Vsysdate,'hh24:mi:ss')
   where YPTJPC=Pyptjpc;
  --*********************5.更新调价状态

  update BM_BMBM00 set FYBZ00='0' where  BMBH00=Pykbmbh;
exception
  when No_Data_Found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||Ptsxx00,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;