CREATE OR REPLACE 
procedure sp_bq_wfjpypzdtf
as
    Vzxcgbz     number;
    Vtsxx00     varchar2(200);
    Vxxxx00     varchar2(200);
    Vjpyfbh     XT_XTCS00.VALUE0%type;
    Vyzid00     YF_YZYPSQ.YZID00%type;
    Vtqdjh0     YF_YZYPSQ.TQDJH0%type;


    cursor c_wfyp is select CH0000,TQDJH0,YYRQ00,YZID00,CZY000,ZYID00,BQBMBH,BRXM00,(select bmmc00 from bm_bmbm00 where bmbh00=a.bqbmbh)BQBMMC
                     from YF_YZYPSQ  a where QLZT00 in('0','1') and YFBMBH=Vjpyfbh and CXBZ00='Z' and YYRQ00>=to_char(sysdate,'yyyymmdd') and YYRQ00<=to_char(sysdate+1,'yyyymmdd')
                     and exists(select 1 from BM_BMBM00 where BMXZ00='0' and BMBH00=a.DQKS00);
    cursor c_fjf  is select YJDJH0 from VW_BQ_YJYW00_FJF where TQDJH0=Vtqdjh0 and YZID00=Vyzid00;
-- Person   Date         Comments
-- zhaoxz   2017.03.16   create 今天明天静配中心用药，自动退药，让护士再次提取 BQHS9.0-20170308-001
-- zhaoxz   2017.04.26   修改过程事务，以及未发药品剔除负单
begin
  select nvl(max(VALUE0),'0') into Vjpyfbh from XT_XTCS00 where NAME00='BQ_JMZXYFBH';
  --没有设置静配药房
  if Vjpyfbh = '0' then
     return;
  end if;

  for wfyp in c_wfyp loop
      begin
        SP_BQ_YZTYSQ(wfyp.tqdjh0,wfyp.zyid00,wfyp.yzid00,wfyp.czy000,wfyp.BQBMBH,'N',Vzxcgbz,Vtsxx00,Vxxxx00);
        update YF_YZYPSQ set BZ0000='自动退药' where TQDJH0=wfyp.tqdjh0 and YZID00=wfyp.yzid00;
        --退附加费
        Vtqdjh0 := wfyp.tqdjh0;
        Vyzid00 := wfyp.yzid00;
        for fjf in c_fjf loop
          begin
            SP_YJ_YWCX(fjf.YJDJH0,wfyp.czy000,wfyp.BQBMBH,'Y','N',null,'N','N');
          end;
        end loop;
        --写入消息表
        insert into XT_XTTSXX(XTDM00,XXBH00,XXNR00,YXQ000,CZYXM0,CZRQ00,
                              XH0000,CZYKS0,JSKSBH,JSKSMC,XXXZ00,FSDX00,XXZT00)
                       values('MC',sq_xt_xttsxx_xxbh00.nextval,wfyp.CH0000||'床'||wfyp.BRXM00||'，由于病人余额不足静配药房未能摆药，现已自动退回药单，请重新生成药单,医嘱号：'||wfyp.YZID00||',用药日期：'||wfyp.YYRQ00,to_char(sysdate,'yyyymmdd'),'系统',to_char(sysdate,'yyyymmdd'),
                              sq_xt_xttsxx_xh0000.nextval,wfyp.BQBMBH,wfyp.BQBMBH,wfyp.BQBMMC,'0','0','自动退药');
        commit;
      exception
        when others then
          rollback;
          Vtsxx00 := substrb(Vtsxx00||sqlerrm,1,200);
          insert into yf_yzypsq_1(TQDJH0,YZID00,BZ0000) values (wfyp.tqdjh0,wfyp.yzid00,Vtsxx00);  
          commit; 
      end;
  end loop;
exception
  when no_data_found then
    raise_application_error(-20002,substrb('数据没有找到！*'||sqlerrm||','||Vtsxx00,1,220));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'!*'||Vtsxx00,1,220));
end;
/