prompt 获取分配的配药人
create or replace function SF_PY_GETMRPYR(ad_YFBMBH  number   --药房
                                )
return number is
  LS_PYR000  BM_YGBM00.YGBH00%type;
-- MODIFICATION HISTORY
-- Person      Date        Comments  
-- dsm         2017.10.10  增加分配配药员for YFPY-20170718-002     
begin
  begin
    select YGBH00 into LS_PYR000 from (
     select YGBH00,DPYSL0 from SF_PYRYB0 where YFBMBH=ad_YFBMBH and YXBZ00='1' order by DPYSL0 
      ) where rownum=1;
  exception
    when others then
    	LS_PYR000:=0;     
  end;  
  return  LS_PYR000; 
end;     
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                           
                                 




--py_proc.sql(3.1)
--配药取发药队列
create or replace procedure SP_YF_CFDL00_FYDLXH(
     v_fydlxh  in  char,
     v_dlqsh0  in number default 1,
     v_value0  out number) 
as
PRAGMA AUTONOMOUS_TRANSACTION;
   v_old    Varchar2(50);
   v_old2   varchar2(50);
   v_old3   varchar2(50);
   v_old4   varchar2(50);
   v_old5   varchar2(50);
   v_old6   varchar2(50);
   v_old7   varchar2(50);
   v_new    Varchar2(50);
   v_new2   varchar2(50);
   v_new3   varchar2(50);
   v_new4   varchar2(50);
   v_new5   varchar2(50);
   v_new6   varchar2(50);
   v_new7   varchar2(50);
   c_value0 Number(5);
   v_sql    varchar2(300);
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- CSF         2008.01.07  不同窗口队列启始号不一致  
------------------------------------------------------------------------------------------
begin
  begin
    v_sql:='select '||trim(v_fydlxh)||'.nextval  value0 from dual where rownum=1';
    execute immediate v_sql  into c_value0; 
  exception  when others then
    v_old:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-1,'DD'));
    v_old2:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-2,'DD'));
    v_old3:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-3,'DD'));
    v_old4:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-4,'DD'));
    v_old5:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-5,'DD'));
    v_old6:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-6,'DD'));
    v_old7:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE-7,'DD'));

    v_new:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+1,'DD')); 
    v_new2:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+2,'DD'));
    v_new3:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+3,'DD'));
    v_new4:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+4,'DD'));
    v_new5:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+5,'DD'));
    v_new6:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+6,'DD'));
    v_new7:=trim(substrb(v_fydlxh,1,length(trim(v_fydlxh))-2))||trim(to_char(SYSDATE+7,'DD'));
    execute immediate 'create SEQUENCE '||trim(v_fydlxh)||' INCREMENT BY 1 START WITH '||to_char(v_dlqsh0+1)||' MINVALUE '||to_char(v_dlqsh0)||' MAXVALUE 99999 NOCYCLE ORDER NOCACHE';
    begin 
      execute immediate 'drop SEQUENCE '||trim(v_old);
      execute immediate 'drop SEQUENCE '||trim(v_old2);
      execute immediate 'drop SEQUENCE '||trim(v_old3);
      execute immediate 'drop SEQUENCE '||trim(v_old4);
      execute immediate 'drop SEQUENCE '||trim(v_old5);
      execute immediate 'drop SEQUENCE '||trim(v_old6);
      execute immediate 'drop SEQUENCE '||trim(v_old7);
    exception  when others then
      c_value0:=v_dlqsh0;
    end;
    begin 
      execute immediate 'drop SEQUENCE '||trim(v_new);
      execute immediate 'drop SEQUENCE '||trim(v_new2);
      execute immediate 'drop SEQUENCE '||trim(v_new3);
      execute immediate 'drop SEQUENCE '||trim(v_new4);
      execute immediate 'drop SEQUENCE '||trim(v_new5);
      execute immediate 'drop SEQUENCE '||trim(v_new6);
      execute immediate 'drop SEQUENCE '||trim(v_new7);
    exception  when others then
      c_value0:=v_dlqsh0;
    end;
    c_value0:=v_dlqsh0;
  end;
  v_value0:=c_value0;
end SP_YF_CFDL00_FYDLXH;
/

/*CREATE OR REPLACE TRIGGER tr_sf_sf_yf_cfdl00_insert
 BEFORE
  INSERT
 ON yf_cfdl00
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
v_fydlxh Varchar2(50);
v_value0 Number(5);
--异常声明
begin
          if  :NEW.ypje00 <0  then
              begin 
               select nvl(fydlxh,0) INTO  :new.fydlxh from YF_CFDL00 where cflsh0 in (select BCCFH0 from yf_mzcf00 where cflsh0=:new.cflsh0) ;
              exception
                when others then
                  :new.fydlxh:=0;
              end; 
          else
               v_fydlxh:='SEQ_YF_FYDLXH_'||to_char(:new.FYCKBH)||'_'||to_char(SYSDATE,'DD');
               begin      
                 SP_YF_CFDL00_FYDLXH(v_fydlxh,v_value0);
               exception WHEN OTHERS THEN
                 v_value0:=-1;
               end;
               begin 
                 select v_value0 into :new.fydlxh from dual where rownum=1;
               exception WHEN OTHERS THEN
                 :new.fydlxh:=0;
               end;
          end if;

exception
      WHEN OTHERS THEN
	  RAISE_APPLICATION_ERROR(-20906,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*');
end;
/
*/

create or replace function sf_py_brzxx
(pghid00  number             --挂号id
)
-- modified history
-- csf  2011.05.09  诊断名称截取处理。
return varchar2 is 
  Vzdmc00    varchar2(255);--诊断名称
  Vcounter   number(5);
  cursor c_zdmc00 is select ZDMC00 from ys_brzdxx z where z.ghid00=pghid00 and zzdbz0 in ('3','4','5') order by zzdbz0;
  cursor c_zdmc01 is select ZDMC00 from ys_brzdxx z where z.ghid00=pghid00 and zzdbz0 in ('1','2') order by zzdbz0;
begin --'3'主要,'4'其他'5'从属 中医'1'疾病,'2'症候
  Vcounter:=0;
  for mc in c_zdmc00 loop
    Vcounter:=Vcounter+1;
    Vzdmc00:=substr(Vzdmc00||'第'||to_char(Vcounter)||'诊断:'||mc.ZDMC00||';',1,250);
  end loop;
  if Vcounter=0 then
     for mc in c_zdmc01 loop
       Vcounter:=Vcounter+1;
       Vzdmc00:=substr(Vzdmc00||'第'||to_char(Vcounter)||'诊断:'||mc.ZDMC00||';',1,250);
     end loop;
  end if;
  return Vzdmc00;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

--add by 2007.05.18
PROMPT 60.新增取药品名称 SF_PY_PDSFZJ
CREATE OR REPLACE FUNCTION SF_PY_PDSFZJ(
 p_ypnm00    IN  number         --输入药品内码
 
 )RETURN CHAR IS           --


v_Return      char(1);
V_JXBH00      BM_YD0000.JXBH00%TYPE;
BEGIN
  v_Return:='N';
  
  BEGIN
    Select 'Y' INTO v_Return  FROM BM_YD0000 WHERE ypnm00=p_ypnm00 AND JXBH00 IN('001','002','003','004','005','006','007');
  EXCEPTION
    WHEN OTHERS THEN
      v_Return:='N';
  END;
  
  RETURN v_Return;
END;
/
--add end.




PROMPT 5.中药代煎费记帐      SP_SF_ZYDJFJZ
CREATE OR REPLACE PROCEDURE SP_SF_ZYDJFJZ     --中药代煎费记帐  
(
        as_CFLSH0     IN   CHAR,              --处方流水号
        ad_ZYDJS0     IN   NUMBER,            --中药代煎数 
        ad_CZYBH0     IN NUMBER,               --操作员编码
        as_YHMSG0    OUT   VARCHAR,           --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR            --系统提示的错误信息
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH       2007.09.17  CREATE:对中药处方修改代煎数，同时对病人记帐。
-- CSF         2010.07.23  如果代煎从无改成有的话，那么应把fjfdjh添上。
-- ---------   ----------  -------------------------------------------
       ls_CFZJE0       YF_MZCF00.CFZJE0%TYPE;      --处方总金额
       ls_BRID00       YF_MZCF00.BRID00%TYPE;
       ls_GHID00       YF_MZCF00.GHID00%TYPE;
       ls_GHH000       YF_MZCF00.GHH000%TYPE;
       ls_BRXM00       YF_MZCF00.BRXM00%TYPE;
       ls_XB0000       YF_MZCF00.XB0000%TYPE;
       ls_CSRQ00       YF_MZCF00.CSRQ00%TYPE;
       ls_YBID00       BM_BRXXB0.YBID00%TYPE;
       ls_FBBH00       BM_BRXXB0.FBBH00%TYPE;
       ls_YBLB00       BM_BRXXB0.YBLB00%TYPE;
       ls_YBBRLB       BM_BRXXB0.YBBRLB%TYPE;
       ls_SRRQ00       YF_MZCF00.SRRQ00%TYPE;
       ls_SRSJ00       YF_MZCF00.SRSJ00%TYPE;
       ls_YSGZH0       YF_MZCF00.YSGZH0%TYPE;
       ls_SSKS00       YF_MZCF00.SSKS00%TYPE;
       ls_GHKS00       YF_MZCF00.GHKS00%TYPE;
       ls_YFBMBH       YF_MZCF00.YFBMBH%TYPE;
       ls_PSZT00       YF_MZCF00.PSZT00%TYPE;
       ls_ZYTS00       YF_MZCF00.ZYTS00%TYPE;
       ls_CFZT00       YF_MZCF00.CFZT00%TYPE;
       ls_BCCFH0       YF_MZCF00.BCCFH0%TYPE;
       ls_SFDM00       BM_YPLB00.SFDM00%TYPE;
       ls_SFXMMC       BM_YPLB00.SFXMMC%TYPE;
       ls_DLXH00       YF_CFDL00.DLXH00%TYPE;
       ls_PYCKBH       YF_CFDL00.PYCKBH%TYPE;  --配药窗口编码
       ls_FYCKBH       YF_CFDL00.FYCKBH%TYPE;  --发药窗口编码
       ls_ZFBL00       SF_FYMX00.ZFBL00%TYPE;
       ls_GFBL00       SF_FYMX00.ZFBL00%TYPE;
       ls_ZFJE00       SF_FYMX00.ZFJE00%TYPE;
       ls_GFJE00       SF_FYMX00.GFJE00%TYPE;
       ls_JZJE00       SF_FYMX00.JZJE00%TYPE;
       ls_HJJE00       SF_FYMX00.HJJE00%TYPE;
       ls_QZFJE0       SF_FYMX00.QZFJE0%TYPE;
       ls_CZRQ00       CHAR(8);
       ls_CZSJ00       CHAR(8);
       ls_SFKSLY       YF_MZCF00.SFKSLY%TYPE;
       V_ZYDJS0        YF_MZCF00.ZYDJS0%TYPE;
       V_YPDLBH        YF_MZCF00.YPDLBH%TYPE;
       V_JYSFDM        BM_YYSFXM.SFXMID%TYPE;            --中药煎药费收费代码
       V_JYSFMC        BM_YYSFXM.XMMC00%TYPE;            --中药煎药费收费名称
       V_JYSFJE        BM_YYSFXM.SFJE00%TYPE;            --中药煎药费收费金额
       V_ZFJE00        YF_MZCF00.CFZJE0%TYPE;
       V_GFJE00        YF_MZCF00.CFZJE0%TYPE; 
       V_ZFBL00        SF_FYMX00.ZFBL00%TYPE;
       V_YBBRLB        BM_BRXXB0.YBBRLB%TYPE;
       V_FBBH00        SF_BRXXB0.FBBH00%TYPE;  
       V_YBZXLB        IC_YBBRLB.YBZXLB%TYPE; 
       V_CZYXM0        BM_YGBM00.ZWXM00%TYPE;
       ad_DJH000       SF_BRJFB0.DJH000%TYPE;
       V_BMBH00        BM_YGBM00.BMBH00%TYPE;   
       V_MZCF00        YF_MZCF00%ROWTYPE;
       v_counter       number;
       
       E_custom        exception;                   --错误变量
BEGIN
    ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
    ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
    ls_CFZJE0 := 0;
      --取出操作员姓名与科室
    SELECT ZWXM00,BMBH00 INTO V_CZYXM0,V_BMBH00
      FROM BM_YGBM00 WHERE YGBH00=ad_CZYBH0;
    BEGIN
     select NVL(A.BRID00,0),SFKSLY,A.PSZT00,B.SFDM00,B.SFXMMC,A.SRRQ00,A.SRSJ00,A.YSGZH0,A.SSKS00,A.GHKS00,A.YFBMBH,
            A.ZYTS00,A.ZYDJS0,A.YPDLBH,A.GHID00,A.GHH000,A.BRXM00,A.XB0000,A.CSRQ00,A.CFZT00,A.BCCFH0
       into ls_BRID00,ls_SFKSLY,ls_PSZT00,ls_SFDM00,ls_SFXMMC,ls_SRRQ00,ls_SRSJ00,ls_YSGZH0,ls_SSKS00,ls_GHKS00,ls_YFBMBH,
            ls_ZYTS00,V_ZYDJS0,V_YPDLBH,ls_GHID00,ls_GHH000,ls_BRXM00,ls_XB0000,ls_CSRQ00,ls_CFZT00,ls_BCCFH0
       from YF_MZCF00 A,BM_YPLB00 B where A.YPDLBH = B.LBBH00  and A.CFLSH0 = as_CFLSH0 and 
             ((CFZJE0>0 AND CFZT00 ='1'));
       SELECT * INTO V_MZCF00 FROM YF_MZCF00 WHERE CFLSH0=as_CFLSH0;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
                  as_YHMSG0:='处方(CFLSH0='''||as_CFLSH0||''')不存在或已记过账,登记非法!';
                  as_SYSMSG:=SQLERRM;
                  ROLLBACK;
                  RETURN;
    END;
    IF  ls_BRID00 <> 0  THEN
        BEGIN
        select  YBID00,FBBH00,YBLB00,YBBRLB  into  ls_YBID00,ls_FBBH00,ls_YBLB00,ls_YBBRLB  from   BM_BRXXB0  where  BRID00 = ls_BRID00;
        EXCEPTION
           WHEN OTHERS THEN
                as_YHMSG0:='系统查找不到该处方对应的病人信息!';
                as_SYSMSG:=SQLERRM||'select * from bm_brxxb0  where brid00 ='||to_char(ls_BRID00);
                ROLLBACK;
                RETURN;
        END;
    ELSE
        BEGIN
        ls_YBID00:=0;
        ls_FBBH00:=1;
        ls_YBLB00:='0';
        ls_YBBRLB:='0';
        END;
    END IF;
  
    --如果是草药处方,收必须的煎药费
    IF ((V_YPDLBH='2') AND (ad_ZYDJS0>0)) THEN
        BEGIN
          SELECT VALUE0 INTO V_JYSFDM  FROM  XT_XTCS00  WHERE NAME00='YF_ZYJYF_SFDM';
        EXCEPTION
           WHEN OTHERS THEN
             V_JYSFDM:=V_JYSFDM;
        END;
        --如果没有中药煎药费的参数，不收取中药煎药费11.16
        IF V_JYSFDM IS NOT NULL THEN
            SELECT SFJE00,XMMC00 INTO V_JYSFJE,V_JYSFMC FROM BM_YYSFXM WHERE SFXMID=V_JYSFDM;
            SELECT B.YBZXLB,A.YBBRLB,A.FBBH00 INTO V_YBZXLB,V_YBBRLB,V_FBBH00
              FROM BM_BRXXB0 A,IC_YBBRLB B
              WHERE A.BRID00=V_MZCF00.BRID00 AND 
                    B.YBLB00=A.YBLB00 and B.FBBH00=a.FBBH00; 
            --找自付比例 JETHUA 2003.03.18
            BEGIN
              SELECT ZFBL00,JMJE00 INTO V_ZFBL00,V_GFJE00
                FROM VW_BM_YBSFDY B
                WHERE B.SFXMID=V_JYSFDM AND B.SFYP00='N' AND
                    B.YBZXLB=V_YBZXLB AND B.YBBRLB=V_YBBRLB AND B.FBBH00=V_FBBH00 ;  
            EXCEPTION
              WHEN OTHERS THEN
                RAISE E_custom;
            END;
            SELECT SQ_SF_BRJFB0_DJH000.NEXTVAL INTO ad_DJH000 FROM DUAL;
              
            V_JYSFJE:=V_JYSFJE*ad_ZYDJS0;
            V_ZFJE00:=V_JYSFJE*V_ZFBL00;
            V_GFJE00:=V_GFJE00*ad_ZYDJS0;
              
            -- JETHUA 2003.06.01 自费病人和医保病人单独处理:自付金额=处方总金额-公费金额
            IF V_FBBH00=1 OR V_FBBH00=3 OR V_FBBH00=90 THEN
              V_ZFJE00:=V_JYSFJE-V_GFJE00;
            END IF; 
              
            --向门诊病人费用明细表 SF_FYMX00 插入一条西药费
            INSERT INTO SF_FYMX00(
                  MXID00,    --门诊病人费用明细流水号 SQ_SF_FYMX00_MXID00
                  DJH000,    --单据号  F.K=SF_BRFY00.DJH000
                  XMBH00,    --医疗收费编码或药品编码
                  XMMC00,    --项目名称
                  XMDJ00,    --单价
                  XMSL00,    --数量
                  HJJE00,    --合计金额
                  ZFJE00,    --自费金额
                  GFJE00,    --公费金额
                  JZJE00,    --记账金额
                  ZFBL00,    --自付比例
                  CZRQ00,    --开单日期
                  CZSJ00,    --开单时间
                  ZXRQ00,    --执行日期
                  ZXSJ00,    --执行时间
                  KDKS00,    --开单科室编号
                  KDYS00,    --开单医生工号
                  ZXKS00,    --执行科室编号
                  YSKS00,   --医生科室     --医生所在科室
                  SFLSCF,    --零散处方标志,'0':非处方,'1':是零散处方,'2':病区处方
                  SFYDJ0,   --收费员登记 '0'其他人登记 '1'收费员登记
                  BEIZHU,   --备注：处方单流水号。为可打印煎药费   --JETHUA  2002.12.18
                  ZXYS00)     --执行医生工号
            VALUES(
                  SQ_SF_FYMX00_MXID00.NEXTVAL,         ad_DJH000,          V_JYSFDM,           V_JYSFMC,
                  ROUND(V_JYSFJE/ad_ZYDJS0,4),         ad_ZYDJS0,          V_JYSFJE,       
                  NVL(V_ZFJE00,0),                     NVL(V_GFJE00,0),    V_JYSFJE-NVL(V_ZFJE00,0)-NVL(V_GFJE00,0),                                   
                  V_ZFBL00,                            ls_CZRQ00,          ls_CZSJ00,          ls_CZRQ00,
                  ls_CZSJ00,                           V_MZCF00.GHKS00,    V_MZCF00.YSGZH0,    V_MZCF00.YFBMBH,
                  V_MZCF00.SSKS00,                     '1',                '1',                as_CFLSH0,
                  V_MZCF00.YSGZH0);
           
           INSERT INTO SF_BRFY00(
                  DJH000,    --病人费用单据流水号(SQ_SF_BRJFB0_DJH000)
                  MZID00,    --门诊ID,F.K=SF_BRXXB0.GHID00
                  MZH000,    --门诊号,本处指挂号号
                  JFLBID,    --病人账户类别, JFLBID = 0,说明该病人是现金病人(现金病人-没有预交金额账户的病人)交费
                  BRID00,    --病人ID号
                  XM0000,    --病人姓名
                  BRFB00,    --病人费别 F.K=BM_BRFBB0.FBBH00
                  HJJE00,    --总金额,提供给医保使用(=自费金额+公费金额+记账金额)
                  ZFJE00,    --自费金额=明细表中的自费金额+减免金额
                  GFJE00,    --公费金额
                  JZJE00,    --记账金额
                  BRYE00,    --病人余额,指预交金的余额
                  CZRQ00,    --操作日期
                  CZSJ00,    --操作时间
                  CZY000,    --操作员  F.K=YGBM00.YGBH00
                  CZYXM0,    --操作员姓名
                  CZYKS0)    --操作员科室,或也可以称为收费科室 F.K=BM_BMBM00.BMBH00
            VALUES(
                  ad_DJH000,        V_MZCF00.GHID00,    V_MZCF00.GHH000,     1,  --病人缴费类别:门诊预缴金,
                  V_MZCF00.BRID00,  V_MZCF00.BRXM00,    V_FBBH00,
                  V_JYSFJE,         NVL(V_ZFJE00,0),    NVL(V_GFJE00,0),     V_JYSFJE-NVL(V_ZFJE00,0)-NVL(V_GFJE00,0),
                  0.0,               --病人余额,指预交金的余额(触发器维护)
                  ls_CZRQ00,        ls_CZSJ00,          ad_CZYBH0,           V_CZYXM0,
                  V_BMBH00);
            --附加费单据号
            --附加费单据号
            UPDATE YF_MZCF00 SET FJFDJH=ad_DJH000
             WHERE CFLSH0=as_CFLSH0 and FJFDJH=0;

      END IF;
    END IF;
    
EXCEPTION
      WHEN NO_DATA_FOUND THEN
                  as_YHMSG0:='对应的处方不存在或已记过账,登记非法!';
                  as_SYSMSG:=SQLERRM;
                  ROLLBACK;
      WHEN E_custom      THEN
                  as_YHMSG0:='医保对应表中未找到煎药费信息';
                  as_SYSMSG:=SQLERRM;
                  ROLLBACK;
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YFCF00('||as_CFLSH0||','||to_char(ad_DJH000)||',as_YHMSG0,as_SYSMSG)',1,150);
		ROLLBACK;
END;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

/*请做成任务，并安排到零晨*/
prompt 删除队列 SP_YF_delfydl
create or replace procedure SP_YF_delfydl
as
v_sql varchar(200);
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- CSF         2008.01.21   删除所有发药队列
-- zhr         2008.02.29   迁移历史处方队列信息
-- huangjy     2018.02.28   队列中未发药代煎队列保留 for YF-20180131-001
------------------------------------------------------------------------------------------
begin
declare
 CURSOR my is select 'drop SEQUENCE '||trim(object_name) SQL_STR  from all_objects where object_name like '%FYDLXH_%'  AND OBJECT_TYPE='SEQUENCE';
 begin
   open my;
   fetch my into v_sql;
     while my%found loop
       execute immediate v_sql;
       fetch my into v_sql;
     end loop;
   close my;
 end;
 --2008.02.29 zhr 迁移历史处方队列信息
 insert into yf_cfdl01(YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000,DYRQ00,DYSJ00,PYRQ00,PYSJ00,FYRQ00,FYSJ00,DLXH00,CFLSH0,FYDLXH,CFDGS0,PHBZ00,PHZXH0,PHRQ00,CHECK0,YPDLBH)
     select YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000,DYRQ00,DYSJ00,PYRQ00,PYSJ00,FYRQ00,FYSJ00,DLXH00,CFLSH0,FYDLXH,CFDGS0,PHBZ00,PHZXH0,PHRQ00,CHECK0,YPDLBH
        from yf_cfdl00 where not exists (select 1 from yf_mzcf00 where cflsh0=yf_cfdl00.cflsh0 and srrq00=to_char(sysdate,'yyyymmdd'));
 if sql%notfound=False then
    delete yf_cfdl00 a where not exists (select 1 from yf_mzcf00 where cflsh0=a.cflsh0 and srrq00=to_char(sysdate,'yyyymmdd') and ZYDJS0>0 ) or a.ZT0000='3';
 end if;
 commit;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 

prompt 取是否运动员及是否含有兴奋剂  GETCFJNDJCTS
CREATE OR REPLACE FUNCTION GETCFJNDJCTS(
P_CFLSH0       IN CHAR)        --输入：处方流水号
RETURN  VARCHAR2 IS
  v_count number;
  v_count1 number;
  v_brid00 bm_brxxb0.brid00%type;
  v_return char(30);

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- csf         2008.05.06  create
-- ---------   ----------  -------------------------------------------

begin
  
  select brid00 into v_brid00 from yf_mzcf00 where cflsh0=p_cflsh0;/*取出brid*/
  select count(*) into v_count from bm_brxxb0 where brid00=v_brid00 and sfydy0='Y';/*判断是否奥运会运动员*/    
  /*判断是否含有兴奋剂药品*/
  if  v_count>0 then
   select count(*) into v_count1 from yf_mzcfmx a,bm_yd0000 b where a.cflsh0=p_cflsh0 and a.ypnm00=b.ypnm00 and b.ndjcts like '%兴奋剂%';
  end if;
  if v_count>0 then
    v_return:='奥运会运动员';
  end if; 
  if v_count1>0 then
    v_return:='奥运会运动员 含兴奋剂药品';
  end if;
  return v_return;
end;
/

--prompt 创建函数，取药品的盘点序号，得出区位码，并计算包数 sp_py_getqwm
create or replace function sp_py_getqwm(
p_yfbmbh in number,
p_ypnm00 in number,
p_cjl000     in number)
return char is 
v_pdxh00 bm_yd0000.pdxh00%type; --盘点序号
v_dbl000 char(2);
v_xbl000 char(2);
i_dbl000 number(2,0);
i_xbl000 number(2,0);
i_dbcs00 number(5,0);
i_xbcs00 number(2,0);
i_ys0000 number(3,0);
v_result varchar(100);
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- CSF         2008.06.26  create
-- CSF         2008.07.08  区位码算法修改成：
-- ---------    ----------  -------------------------------------------  
begin
 begin
   select pdxh00 into v_pdxh00 from yf_ypkcxx where yfbmbh=p_yfbmbh and ypnm00=p_ypnm00;
   
   begin/*取大包量*/
     v_dbl000:=substr(trim(v_pdxh00),3,2);
     i_dbl000:=to_number(v_dbl000);
   exception
   when others then
     i_dbl000:=0;
   end;
   begin/*取小包量*/
     v_xbl000:=substr(trim(v_pdxh00),5,2);
     i_xbl000:=to_number(v_xbl000);
   exception
   when others then
     i_xbl000:=0;
   end;
   if (i_dbl000>0) and (i_xbl000)>0 then
     --求商及余数
     i_dbcs00:=floor(p_cjl000/i_dbl000);
     i_ys0000:=mod(p_cjl000,i_dbl000);
     
     if (i_ys0000>0) and mod(i_ys0000,i_xbl000)=0 then--如果余数是小包数的整数倍则返回 i_dbcs00个'A' i_xbcs00个B
       i_xbcs00:=i_ys0000/i_xbl000;
     elsif  mod(i_ys0000,i_xbl000)<>0 then
       /*如果余数<>小包数 那么拆分一大包，加上余数，如果此量除以小包数是整数，则按i_dbcs00-1大包加
         (i_dbl000+i_ys0000)/i_xbl000小包来计算,如果不是整数则再拿余数除以大小包量来比余数与大小包的差值，
          差值靠近大包装,则再加上一次大包装,若差值靠近小包装则再按floor(余数/小包装)+1来计算
          */
       if mod((i_dbl000+i_ys0000),i_xbl000)=0 then
         i_xbcs00:=(i_dbl000+i_ys0000)/i_xbl000;
         i_dbcs00:=i_dbcs00-1; 
       else
         if i_dbl000-i_ys0000>i_xbl000-mod(i_ys0000,i_xbl000) then --按floor(余数/小包装)+1来计算
            i_xbcs00:=floor(i_ys0000/i_xbl000)+1;
         else --加一大包
            i_dbcs00:=i_dbcs00+1;
            i_xbcs00:=0;
         end if;
                         
       end if;
     end if;
     
   end if;
   --求返回的字符窜
   if i_dbcs00>0 then
     v_result:=trim(lpad(' ',i_dbcs00+1,'A'));
   end if;
   if i_xbcs00>0 then
     if length(trim(v_result))>0 then
        v_result:=v_result||trim(lpad(' ',i_xbcs00+1,'B'));
     else 
        v_result:=trim(lpad(' ',i_xbcs00+1,'B'));
     end if;
   end if;

   if length(trim(v_result))>0  then     
     return '('||substr(v_pdxh00,1,2)||v_result||')';
   else
     return '('||substr(v_pdxh00,1,2)||')'; 
   end if;
 exception
 when others then 
   return '0';
 end;
end;
/



--prompt 创建函数，取处方小包数 sp_py_getxbs
create or replace function sp_py_getxbs(
p_cflsh0 in char
)
return number is 
v_pdxh00 bm_yd0000.pdxh00%type; --盘点序号
i_yfbmbh yf_ypkcxx.yfbmbh%type;
p_cjl000 yf_mzcfmx.csl000%type;
v_dbl000 char(2);
v_xbl000 char(2);
i_dbl000 number(2,0);
i_xbl000 number(2,0);
i_dbcs00 number(5,0);
i_xbcs00 number(2,0);
i_ys0000 number(3,0);
v_result varchar(100);
i_result number(30);

-- MODIFICATION HISTORY
-- Person       Date        Comments
-- CSF         2008.06.26  create
-- CSF         2008.07.08  区位码算法修改成：
-- ---------    ----------  -------------------------------------------  
begin
 select yfbmbh into i_yfbmbh from yf_mzcf00 where cflsh0=p_cflsh0;
 i_result:=0;
 declare
 cursor my is
  select ypnm00,csl000 from yf_mzcfmx where cflsh0=p_cflsh0;
 v_my my%rowtype;
 begin
   begin
     i_result:=0;
     open my;
     fetch my into v_my;
     while my%found loop
       v_result:='';
       select pdxh00 into v_pdxh00 from yf_ypkcxx where yfbmbh=i_yfbmbh and ypnm00=v_my.ypnm00;
       begin/*取大包量*/
         v_dbl000:=substr(trim(v_pdxh00),3,2);
         i_dbl000:=to_number(v_dbl000);
       exception
       when others then
         i_dbl000:=0;
       end;
       begin/*取小包量*/
         v_xbl000:=substr(trim(v_pdxh00),5,2);
         i_xbl000:=to_number(v_xbl000);
       exception
       when others then
         i_xbl000:=0;
       end;
       if (i_dbl000>0) and (i_xbl000)>0 then
        --求商及余数
         i_dbcs00:=floor(v_my.csl000/i_dbl000);
         i_ys0000:=mod(v_my.csl000,i_dbl000);
     
         if (i_ys0000>0) and mod(i_ys0000,i_xbl000)=0 then--如果余数是小包数的整数倍则返回 i_dbcs00个'A' i_xbcs00个B
          i_xbcs00:=i_ys0000/i_xbl000;
         elsif  mod(i_ys0000,i_xbl000)<>0 then
           /*如果余数<>小包数 那么拆分一大包，加上余数，如果此量除以小包数是整数，则按i_dbcs00-1大包加
           (i_dbl000+i_ys0000)/i_xbl000小包来计算,如果不是整数则再拿余数除以大小包量来比余数与大小包的差值，
            差值靠近大包装,则再加上一次大包装,若差值靠近小包装则再按floor(余数/小包装)+1来计算
            */
           if mod((i_dbl000+i_ys0000),i_xbl000)=0 then
             i_xbcs00:=(i_dbl000+i_ys0000)/i_xbl000;
             i_dbcs00:=i_dbcs00-1; 
           else
             if i_dbl000-i_ys0000>i_xbl000-mod(i_ys0000,i_xbl000) then --按floor(余数/小包装)+1来计算
               i_xbcs00:=floor(i_ys0000/i_xbl000)+1;
             else --加一大包
               i_dbcs00:=i_dbcs00+1;
               i_xbcs00:=0;
             end if;
                         
           end if;
         end if;
     
       end if;
   --求返回的字符窜
       if i_dbcs00>0 then
         v_result:=trim(lpad(' ',i_dbcs00+1,'A'));
       end if;
       if i_xbcs00>0 then
         if length(trim(v_result))>0 then
            v_result:=v_result||trim(lpad(' ',i_xbcs00+1,'B'));
         else 
            v_result:=trim(lpad(' ',i_xbcs00+1,'B'));
         end if;
       end if;

       if length(trim(v_result))>0  then     
         i_result:=i_result+length(trim(v_result));
       else
         i_result:=i_result;
       end if;
       fetch my into v_my;
     end loop;
     close my;
    end;
    return i_result;
 exception
 when others then
  close my;
  return 0;
 end;
end;
/


prompt 按照区位码 纯大包 0 有大包又有小包  1 纯小包 2 返回数值 
create or replace function sp_py_getqwmsx(
p_yfbmbh in number,
p_ypnm00 in number,
p_cjl000     in number)
return char is 
v_pdxh00 bm_yd0000.pdxh00%type; --盘点序号
v_dbl000 char(2);
v_xbl000 char(2);
i_dbl000 number(2,0);
i_xbl000 number(2,0);
i_dbcs00 number(5,0);
i_xbcs00 number(2,0);
i_ys0000 number(3,0);
v_result varchar(100);
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- CSF         2008.11.10   create :按照区位码 纯大包 有大包又有小包 纯小包 返回数值
-- CSF         2008.11.11   中药处方排列顺序先按盘点序号的前第一位,再按纯大包,既有大包小包,再纯小包.最后按盘点序号的第二位．
-- ---------    ----------  -------------------------------------------  
begin
 begin
   select pdxh00 into v_pdxh00 from yf_ypkcxx where yfbmbh=p_yfbmbh and ypnm00=p_ypnm00;
   
   begin/*取大包量*/
     v_dbl000:=substr(trim(v_pdxh00),3,2);
     i_dbl000:=to_number(v_dbl000);
   exception
   when others then
     i_dbl000:=0;
   end;
   begin/*取小包量*/
     v_xbl000:=substr(trim(v_pdxh00),5,2);
     i_xbl000:=to_number(v_xbl000);
   exception
   when others then
     i_xbl000:=0;
   end;
   if (i_dbl000>0) and (i_xbl000)>0 then
     --求商及余数
     i_dbcs00:=floor(p_cjl000/i_dbl000);
     i_ys0000:=mod(p_cjl000,i_dbl000);
     
     if (i_ys0000>0) and mod(i_ys0000,i_xbl000)=0 then--如果余数是小包数的整数倍则返回 i_dbcs00个'A' i_xbcs00个B
       i_xbcs00:=i_ys0000/i_xbl000;
     elsif  mod(i_ys0000,i_xbl000)<>0 then
       /*如果余数<>小包数 那么拆分一大包，加上余数，如果此量除以小包数是整数，则按i_dbcs00-1大包加
         (i_dbl000+i_ys0000)/i_xbl000小包来计算,如果不是整数则再拿余数除以大小包量来比余数与大小包的差值，
          差值靠近大包装,则再加上一次大包装,若差值靠近小包装则再按floor(余数/小包装)+1来计算
          */
       if mod((i_dbl000+i_ys0000),i_xbl000)=0 then
         i_xbcs00:=(i_dbl000+i_ys0000)/i_xbl000;
         i_dbcs00:=i_dbcs00-1; 
       else
         if i_dbl000-i_ys0000>i_xbl000-mod(i_ys0000,i_xbl000) then --按floor(余数/小包装)+1来计算
            i_xbcs00:=floor(i_ys0000/i_xbl000)+1;
         else --加一大包
            i_dbcs00:=i_dbcs00+1;
            i_xbcs00:=0;
         end if;
                         
       end if;
     end if;
     
   end if;
   --求返回的值
   if (i_dbcs00>0) and (i_xbcs00<=0) then--纯大包
     v_result:='0';
   elsif (i_dbcs00>0) and (i_xbcs00>0) then--既有大包又有小包
    v_result:='1';
   elsif (i_dbcs00<=0) and (i_xbcs00>0) then--纯小包
     v_result:='2';
   else v_result:='0';
   end if;
   if length(substr(v_pdxh00,2,1))>0 then
     v_result:=substr(v_pdxh00,1,1)||v_result||substr(v_pdxh00,2,1);
   else
     v_result:=substr(v_pdxh00,1,1)||v_result;
   end if;
   return v_result;
 exception
 when others then 
   return '0';
 end;
end;
/

CREATE OR REPLACE FUNCTION SF_YF_GET_CFLSH0_DJH000_ROWNUM  
(  
   P_LSH000      IN  CHAR
)
RETURN CHAR IS
   V_DJH000 YF_MZCF00.DJH000%TYPE;
   V_ROWNUM INTEGER;

BEGIN
  --XM    RQ          BZ
  --YANGH 2009.03.20  create:返回与该处方号在相同单据号下的位置 
  BEGIN
    SELECT DJH000 INTO V_DJH000 FROM YF_MZCF00 WHERE CFLSH0=P_LSH000;
  EXCEPTION
    WHEN OTHERS THEN
      V_DJH000 := 0;
  END;
  BEGIN  
    SELECT ROW000 INTO V_ROWNUM FROM 
      (SELECT ROWNUM ROW000,CFLSH0 FROM YF_MZCF00 WHERE DJH000=V_DJH000 ORDER BY CFLSH0)
    WHERE CFLSH0=P_LSH000;
  EXCEPTION
    WHEN OTHERS THEN
      V_ROWNUM := 0;
  END;
  --输出结果
  RETURN V_ROWNUM;
EXCEPTION
  WHEN OTHERS THEN
  RETURN 0;
END;
/


prompt 创建中药处方细目明细 SF_YF_GETZYCFMXYFDY  
create or replace function SF_YF_GETZYCFMXYFDY(
 AS_CFLSH0    in char          --处方流水号
 )RETURN varchar2 IS           --中药处方明细用法


  V_ZDMC00      varchar2(200);
  V_TEMPZD      varchar2(200);


-- MODIFICATION HISTORY
-- Person      Date        Comments
-- chenyw      2010.06.04   CREATE 
-- ---------   ----------  -------------------------------------------
 begin

    V_ZDMC00:=' ';
    for XMYF in (select DISTINCT XMYFMC from YF_MZCFMX where CFLSH0=AS_CFLSH0 and XMYFMC is not null ) loop
      
      if V_ZDMC00=' ' then
         V_ZDMC00:=XMYF.XMYFMC||':';
      else
         V_ZDMC00:=V_ZDMC00||chr(13)||XMYF.XMYFMC||':';
      end if;
      for YP in (select YPMC00 from YF_MZCFMX where CFLSH0=AS_CFLSH0 and XMYFMC=XMYF.XMYFMC) loop
        if substr(V_ZDMC00,length(V_ZDMC00)-1,1)=':' then
          V_ZDMC00:=V_ZDMC00||YP.YPMC00;
        else
          V_ZDMC00:=V_ZDMC00||'  '||YP.YPMC00;
        end if;
      end loop;
      
    end loop;
              
  if V_ZDMC00<>' ' then  
    RETURN substr(trim(V_ZDMC00),1,120);
  else
    RETURN '';
  end if;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



prompt 创建过程取门诊配药同个药房同个配药窗口（已记帐，未发药） SF_PY_GETCFZSYDQS 
create or replace function SF_PY_GETCFZSYDQS
  (
    P_CFLSH0 in  char  --处方流水号
  )
  return varchar2 as 
  --Person         Date           Comments
  --chenyw         2010.06.10     创建过程取门诊配药同个药房同个配药窗口（已记帐，未发药）
  V_BRID00  YF_CFDL00.brid00%type;
  V_PYCKBH  YF_CFDL00.pyckbh%type;
  V_YFBMBH  YF_CFDL00.YFBMBH%type;
  V_ZCFS   number(5); 
  V_DQCFXH  NUMBER(5);
  return_HS  varchar2(30);
  i  number(5);
  begin
    begin
        select BRID00,PYCKBH,YFBMBH into V_BRID00,V_PYCKBH,V_YFBMBH from yf_cfdl00 where cflsh0=P_CFLSH0;
        select count(*) into  V_ZCFS from yf_cfdl00 a,YF_MZCF00 b where  a.zt0000 in ('0','1','2') and  a.BRID00=V_BRID00 
        and a.PYCKBH= V_PYCKBH and a.YFBMBH=V_YFBMBH and a.cflsh0=b.cflsh0 and b.jzrq00 = to_char(sysdate,'YYYYMMDD');
        DECLARE  VC_CFLSH0  yf_cfdl00%ROWTYPE;
        cursor cur_CFLSH0 is 
        select a.CFLSH0  from yf_cfdl00 a,YF_MZCF00 b where  a.zt0000 in ('0','1','2') 
        and  a.BRID00=V_BRID00 and a.PYCKBH= V_PYCKBH and a.YFBMBH=V_YFBMBH
         and a.cflsh0=b.cflsh0 and b.jzrq00 = to_char(sysdate,'YYYYMMDD') order by  a.CFLSH0;
        begin
         V_DQCFXH :=0;
         i :=1;
         for VC_CFLSH0 in cur_CFLSH0 loop
           if VC_CFLSH0.CFLSH0= P_CFLSH0 then
              V_DQCFXH :=i;
            else
               i :=i+1;
           end if;
         end loop;
       end;
       
    exception 
    when others then
       V_ZCFS :=0;
       V_DQCFXH :=0;
    end;
    if (V_ZCFS=0) or (V_DQCFXH=0) then
      return_HS :=' ';
    else
      return_HS :='共 '||to_char(V_ZCFS)||'张  第'||to_char(V_DQCFXH)||'张';
    end if;
    
    return return_HS;
  end; 
  /
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 创建中药部分带煎分单   SP_PY_ZYDJFD
create or replace procedure SP_PY_ZYDJFD
(
        as_CFLSH0     IN   CHAR,              --处方流水号
        as_SCLSH0     OUT  Varchar,           --输出流水号
        as_YHMSG0    OUT   VARCHAR,           --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR            --系统提示的错误信息
)
as
       V_CFLSH0_temp00  varchar2(40);--临时处方流水号;
       V_CFLSH0_NEW     YF_MZCF00.cflsh0%type;--新的处方流水号
       V_CFLSH0     YF_MZCF00.cflsh0%type;--处方流水号
       V_CFID00_NEW     YF_MZCFMX.CFID00%type;--新的处方ID
       V_ZYDJS0_JL    YF_MZCF00.ZYDJS0%TYPE;  --记录中药带煎数 
       V_ZYTS00_JL    YF_MZCF00.ZYTS00%TYPE;  --记录中药总贴数 
       V_CZJE00_JL    YF_MZCF00.CFZJE0%TYPE;  --记录处方总金额
       V_YPZSL0        YF_MZCFMX.YPZSL0%TYPE; --药品总数量
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- chenyw      2010.07.19  中药部分带煎分单  
-- csf         2010.10.08  8.1.6版本不支持的写法纠正
begin
	  --处方号复制
	    V_CFLSH0_temp00 :=as_CFLSH0||',';
    begin
      select ZYTS00,ZYDJS0 into V_ZYTS00_JL,V_ZYDJS0_JL from YF_MZCF00 where ZYDJS0>0 and YPDLBH='2' and ZYTS00<>ZYDJS0 and CFLSH0=as_CFLSH0;
	  exception
	    when others then
	      V_ZYTS00_JL:=0;
	      V_ZYDJS0_JL:=0;
	  end;
	  if (V_ZYTS00_JL<>0) and (V_ZYDJS0_JL<>0) then
	     select  to_char(SQ_YS_CFXXB0_CFLSH0.nextval) into V_CFLSH0_NEW from dual;
	     --插入新的处方单
	     insert into YF_MZCF00(CFLSH0,YFBMBH,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,DJH000,YSGZH0,YSXM00,SSKS00,
                             GHKS00,ZYTS00,YPDLBH,CFXZ00,BZID00,BZMC00,CFZT00,SFKSLY,PSZT00,FJFDJH,CZY000,SRRQ00,
                             SRSJ00,CFZJE0,BRDZ00,BZ0000,CFSRBZ,ZYDJS0,ZYMRCS,BCCFH0,TSBZBH,ZYSFJJ,ZYSFNJ,ZYNJJL)
                      select V_CFLSH0_NEW,YFBMBH,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,DJH000,YSGZH0,YSXM00,SSKS00,
                             GHKS00,V_ZYTS00_JL-V_ZYDJS0_JL,YPDLBH,CFXZ00,BZID00,BZMC00,'7',SFKSLY,PSZT00,FJFDJH,CZY000,SRRQ00,
                             SRSJ00,0,BRDZ00,BZ0000,CFSRBZ,0,ZYMRCS,BCCFH0,TSBZBH,ZYSFJJ,ZYSFNJ,ZYNJJL from YF_MZCF00 
                             where CFLSH0=as_CFLSH0;
      Insert into ys_cfxxb0 (CFLSH0,YFBMBH,GHID00,GHH000,BRID00,BRXM00,XB0000,DJH000,BRLB00,YSGZH0,SSKS00,GHKS00,YPDLBH,
                            CFXZ00,ZYTS00,ZYDJS0,BZID00,CFZT00,FJFDJH,CZY000,SRRQ00,SRSJ00,CFMBMC,CFZJE0,BRDZ00,TSBZBH,CFDGS0)
                    select V_CFLSH0_NEW CFLSH0,YFBMBH,GHID00,GHH000,BRID00,BRXM00,XB0000,DJH000,BRLB00,YSGZH0,SSKS00,GHKS00,YPDLBH,
                            CFXZ00,V_ZYTS00_JL-V_ZYDJS0_JL,0,BZID00,CFZT00,FJFDJH,CZY000,SRRQ00,SRSJ00,CFMBMC,V_CZJE00_JL,BRDZ00,TSBZBH,CFDGS0 from ys_cfxxb0
                           where CFLSH0=as_CFLSH0;
      update YF_MZCF00  set CFZT00='0' where CFLSH0=V_CFLSH0_NEW;
      declare
         cursor CUR_YF_MZCFMX_CFID is 
         select CFID00 from YF_MZCFMX where CFLSH0=as_CFLSH0;
      begin
     	 for V_YF_MZCFMX_CFID in CUR_YF_MZCFMX_CFID loop  
     	 select SQ_YS_CFMX00_CFID00.nextval into V_CFID00_NEW from dual;                    
       insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,
                            YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,
                            THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00, CQLY00,F2J000)
                     select V_CFID00_NEW,V_CFLSH0_NEW,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,
                            CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,SF_YS_LYLJS0(YPNM00,CSL000,ZHL000,'',V_ZYTS00_JL-V_ZYDJS0_JL),CFDW00,ZHL000,CXBZ00,
                            SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00, CQLY00,F2J000  from YF_MZCFMX 
                            where CFID00= V_YF_MZCFMX_CFID.CFID00; 
                                
       insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, LSDJ00, YFID00, PCID00,CSL000, JLDW00, YYSJ00, YYSJDW, YYZF00 , 
                           ZB0000, PSLBID, SFZBY0,YPZSL0, CFDW00, ZHL000, ZQCS00, ZFBL00, YBLB00, THBZ00,ZXCS00,TSYS00,CQLY00,F2J000,XMYFMC,ZJYFID)
                   select  V_CFID00_NEW,V_CFLSH0_NEW,A.YPNM00, A.YPMC00, A.YPGG00, A.LSDJ00, A.YFID00, A.PCID00,A.CSL000, A.JLDW00, A.YYSJ00, A.YYSJDW, A.YYZF00 , 
                           A.ZB0000, A.PSLBID, A.SFZBY0,SF_YS_LYLJS0(YPNM00,CSL000,ZHL000,'',V_ZYTS00_JL-V_ZYDJS0_JL), A.CFDW00, A.ZHL000, A.ZQCS00, A.ZFBL00, A.YBLB00, 
                           A.THBZ00,A.ZXCS00,A.TSYS00,A.CQLY00,A.F2J000,A.XMYFMC,A.ZJYFID
                           from YS_CFMX00 A where  CFID00=V_YF_MZCFMX_CFID.CFID00;
                           
    --修改原先中药处方细单的一些信息
    select nvl(sum(YPZSL0),0) into V_YPZSL0 from YF_MZCFMX where CFID00=V_CFID00_NEW;
    
    update YF_MZCFMX  set YPZSL0=YPZSL0-V_YPZSL0 where CFID00=V_YF_MZCFMX_CFID.CFID00;
    update YS_CFMX00  set YPZSL0=YPZSL0-V_YPZSL0 where CFID00=V_YF_MZCFMX_CFID.CFID00;  
    end loop;
    --修改新中药处方主单的一些信息;
    select round(sum(YPZSL0*LSDJ00),2) into V_CZJE00_JL from YF_MZCFMX  where CFLSH0=V_CFLSH0_NEW;
    update YF_MZCF00  set CFZJE0=V_CZJE00_JL where CFLSH0=V_CFLSH0_NEW;
    update YS_CFXXB0  set CFZJE0=V_CZJE00_JL where CFLSH0=V_CFLSH0_NEW;
    --修改旧中药处方主单的一些信息;
    select round(sum(YPZSL0*LSDJ00),2) into V_CZJE00_JL from YF_MZCFMX  where CFLSH0=as_CFLSH0;
    update YF_MZCF00  set CFZJE0=V_CZJE00_JL,ZYTS00=V_ZYDJS0_JL  where CFLSH0=as_CFLSH0;
    update YS_CFXXB0  set CFZJE0=V_CZJE00_JL,ZYTS00=V_ZYDJS0_JL  where CFLSH0=as_CFLSH0;
    V_CFLSH0_temp00:=V_CFLSH0_temp00||V_CFLSH0_NEW||',';
    end; 
	  end if;
	  as_SCLSH0:=V_CFLSH0_temp00;
exception 
 WHEN OTHERS THEN
                as_YHMSG0:='带煎分单出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_PY_ZYDJFD('||as_CFLSH0||',as_SCLSH0,as_YHMSG0,as_SYSMSG)',1,150);
                ROLLBACK;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


prompt 11.取病人排号显示信息 SF_PY_GETQHXX
create or replace
FUNCTION sd_hospital.SF_PY_GETQHXX
(
   pbrid00      in      number     --传入病人id
)
return  Varchar2 is   
  TmpStr Varchar2(255);  
  Ls_xsxx    Varchar2(255);  
  ls_xsxh00  varchar2(50);
  I number(5);
  J number(5);
  L number(5);
  v_COUNT number(5);
  ls_YFBMBH BM_BMBM00.BMBH00%TYPE;--部门编号
  ls_BMMC00 BM_BMBM00.BMMC00%TYPE;--部门名称
  MZHS_CYPHYF XT_XTCS00.VALUE0%type;--参与排号药房
  MZPY_JZHSFZJFY XT_XTCS00.VALUE0%type;--排号后是否直接发药
  ZZQH_XHXSFS XT_XTCS00.VALUE0%type;--序号显示方式0 最小序号 1所有序号
  ZZQH_JRPDJHCFBLTS XT_XTCS00.VALUE0%type;-- 默认为0 ，则是当天的处方
  ls_COUNT  number(3,0);
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- CSF         2011.05.09   create 病人排号显示信息
-- CSF         2011.05.12   MZHS_CYPHYF参数应加上,，或者可能造成处方少显示。
-- CSF         2011.06.01   显示的序号，是显示病人的所有序号，还是最小序号，加参数控制ZZQH_XHXSFS
-- CSF         2011.11.23   按参数ZZQH_JRPDJHCFBLTS几天内的处方参与排号
-- CSF         2012.02.28   如果一个药房窗口都关闭，此时DELPHI端有处理，所以本过程对此种药房处方不做提示 YFQH-20120225-001
-- ----------  ----------   ----------------------------------------------------------------------  
begin
	--取参数
	begin
		select trim(VALUE0) into MZHS_CYPHYF from XT_XTCS00 where NAME00='MZHS_CYPHYF';
	exception
	  when others then
	    MZHS_CYPHYF:='N';
  end;
	--取参数
	begin
		select trim(VALUE0) into ZZQH_JRPDJHCFBLTS from XT_XTCS00 where NAME00='ZZQH_JRPDJHCFBLTS';
	exception
	  when others then
	    ZZQH_JRPDJHCFBLTS:='0';
  end;  

	begin
		select trim(VALUE0) into MZPY_JZHSFZJFY from XT_XTCS00 where NAME00='MZPY_JZHSFZJFY';
	exception
	  when others then
	    MZPY_JZHSFZJFY:='N';
  end;  

	begin
		select trim(VALUE0) into ZZQH_XHXSFS from XT_XTCS00 where NAME00='ZZQH_XHXSFS';
	exception
	  when others then
	    ZZQH_XHXSFS:='0';
  end;  
  
  
  TmpStr:=' ';
  if MZHS_CYPHYF<>'N' then
      --I,记录出现'*'的上一次的位置,
      --J,计数器,
      --L,记录出现'*'的当前位置
    MZHS_CYPHYF:=trim(MZHS_CYPHYF)||',';
    I:=0;    
    J:=1;      
    SELECT  INSTR(MZHS_CYPHYF,',',1,J)  INTO  L  FROM  DUAL;  --取出第J次出现','的位置
        
    WHILE  L>0  LOOP 
            --取出参数组合中的药房部门编号                
      LS_YFBMBH:=TO_NUMBER(SUBSTR(MZHS_CYPHYF,I+1,L-I-1)); 
      
      
      select nvl(MAX(BMMC00),' ') into ls_BMMC00 from BM_BMBM00 where BMBH00=LS_YFBMBH;
      
      select count(*) into ls_COUNT  from BM_CKBM00 a,BM_CKBM00 b,BM_CKDY00 c where a.FLAG00='2' and b.FLAG00='3'
      and a.CKBH00=c.SCKBH0 and b.CKBH00=c.DCKBH0 and a.BMBH00=LS_YFBMBH and b.BMBH00=LS_YFBMBH
      and a.ZT0000='0' and b.ZT0000='0' ;   
      
      Ls_xsxx:=' ';
      
      if (ls_BMMC00<>' ') and (ls_COUNT>0) then
         
        for v_xsxx in (
          select CKBH00,CKMC00,min(FYDLXH) ZXXH00,COUNT(*) CFS000 from (
            select b.CKMC00,b.CKBH00,A.FYDLXH
               from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,YF_CFDL00 A,BM_CKBM00 B
               where C.BRID00=pbrid00 and C.YFBMBH=LS_YFBMBH 
               and ((((C.CFZT00 = '1'  and not exists (select 1 from YF_MZCF00 where BCCFH0=c.CFLSH0))
                   or (C.CFZT00='2' and MZPY_JZHSFZJFY='Y' 
                       and not exists (select 1 from YF_CFDL00 where CFLSH0=C.CFLSH0  and ZT0000='3')))
                   and C.SRRQ00>=TO_CHAR(SYSDATE-1/3-ZZQH_JRPDJHCFBLTS,'YYYYMMDD')
                   and C.BCCFH0 IS NULL))
               and C.GHKS00=M.BMBH00
               and C.CZY000=G.YGBH00
               and C.SFKSLY='N'
               and C.CFLSH0=A.CFLSH0 and a.FYCKBH=b.CKBH00 and a.phbz00>='1' and  nvl(a.FYCKBH,'0')<>'0' AND NVL(to_char(a.FYDLXH),'')<>'未排'
               )
          group by CKBH00,CKMC00 order by CKBH00) loop
          ls_xsxh00:=' ';
          if ZZQH_XHXSFS='1' then            
            for v_xsxh in ( select distinct a.fydlxh from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,YF_CFDL00 A,BM_CKBM00 B
               where C.BRID00=pbrid00 and C.YFBMBH=LS_YFBMBH 
               and ((((C.CFZT00 = '1'  and not exists (select 1 from YF_MZCF00 where BCCFH0=c.CFLSH0))
                   or (C.CFZT00='2' and MZPY_JZHSFZJFY='Y' 
                       and not exists (select 1 from YF_CFDL00 where CFLSH0=C.CFLSH0  and ZT0000='3')))
                   and C.SRRQ00>=TO_CHAR(SYSDATE-1/3-ZZQH_JRPDJHCFBLTS,'YYYYMMDD')
                   and C.BCCFH0 IS NULL))
               and C.GHKS00=M.BMBH00
               and C.CZY000=G.YGBH00
               and C.SFKSLY='N'
               and C.CFLSH0=A.CFLSH0 and a.FYCKBH=b.CKBH00 and a.phbz00>='1' and  nvl(a.FYCKBH,0)<>0 AND NVL(to_char(a.FYDLXH),'')<>'未排'
               and a.FYCKBH=v_xsxx.CKBH00 ) loop
               if ls_xsxh00=' ' then
                  ls_xsxh00:=to_char(v_xsxh.FYDLXH);
               else
                  ls_xsxh00:=ls_xsxh00||'、'||to_char(v_xsxh.FYDLXH);
               end if;
             end loop;
          else
            ls_xsxh00:=to_char(v_xsxx.zxxh00);
          end if;
          
          if  ls_xsxx=' ' then
             ls_xsxx:=RPAD(ls_BMMC00,10,' ')||' '||RPAD(v_xsxx.CKMC00,8,' ')||' '||RPAD(to_char(v_xsxx.CFS000)||'张处方',8,' ')||RPAD('序号:'||ls_xsxh00,16,' ');
          else
             ls_xsxx:=ls_xsxx||chr(13)||rpad(' ',10,' ')||' '||RPAD(v_xsxx.CKMC00,8,' ')||' '||RPAD(to_char(v_xsxx.CFS000)||'张处方',8,' ')||RPAD('序号:'||ls_xsxh00,16,' ');
          end if;
        end loop;
        if ls_xsxx<>' ' then
          if TmpStr=' ' then
            TmpStr:=ls_xsxx;
          else
           TmpStr:=TmpStr||chr(13)||ls_xsxx; 
          end if ;
        end if;
      end if;      
      I:=L;
      J:=J+1;
      SELECT  INSTR(MZHS_CYPHYF,',',1,J)  INTO  L  FROM  DUAL;  --取出第J次出现','的位置
    end loop; 
  end if;
  
  if tMPsTR=' ' then
     TmpStr:='-1';
  end if;
  return TmpStr;
end SF_PY_GETQHXX;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 住院取号窗口分配过程 SP_PY_GETCKBH
create or replace procedure SP_PY_GETCKBH(
ad_YFBMBH in number,
ad_CKBH00 in number,
ad_CKLX00 in number,
ad_CFDGS0 in varchar2,
ad_BRNL00 in number,
ad_CFKDKS in number,
ad_WHBTBR in varchar2,
ad_HZBZ00 in number,
as_RSCKBH out number
)
as
  --配药窗口对应的编码
  ls_PYCKBH   YF_CFDL00.PYCKBH%type;
  --发药窗口对应的编码
  ls_FYCKBH   YF_CFDL00.FYCKBH%type;
  ls_CKBH00   YF_CFDL00.FYCKBH%type;
  ls_countkx  number(5);
  ls_countpy  number(5);
  ls_count    number(5);
  ls_count1   number(5);
  ls_CFDGS0   YF_CFDL00.CFDGS0%TYPE;   --处方单格式
  ls_YPDLBH   YF_CFDL00.YPDLBH%TYPE;   --处方大类编号
  V_WHNLYXPD  XT_XTCS00.VALUE0%TYPE;      --维护年龄优先排队
  V_WHBTLBBRYXPD  XT_XTCS00.VALUE0%TYPE;      --维护年龄优先排队
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.06.06  create for  YFPY-20161212-001
-- huangjy     2018.08.27  取消在过程内commit问题。by YFPY-20180827-001
-- huangjy     2019.09.23  修正配药窗口无法获取的问题 by YFPY-20190919-001
begin
  ls_PYCKBH:= 0;
  ls_FYCKBH:= 0;
  ls_CKBH00:= 0;
  --维护年龄优先排队
  begin
    select trim(Value0) into V_WHNLYXPD   from xt_xtcs00 where name00='MZPY_WHNLYXPD';
  exception
    when others then
      V_WHNLYXPD:='0';
  end;
  --维护不同类别病人优先排队
  begin
    select trim(Value0) into V_WHBTLBBRYXPD   from xt_xtcs00 where name00='MZPY_WHBTLBBRYXPD';
  exception
    when others then
      V_WHBTLBBRYXPD:='N';
  end;
  ls_CFDGS0:=substrb(ad_CFDGS0,1,1);
  if instrb(ad_CFDGS0,',')>0 then
     ls_YPDLBH:=substrb(ad_CFDGS0,3,1);
  end if;
  if ad_CKLX00 = 1 then  --计算配药窗口
     if   ls_PYCKBH=0 then
       begin
        --是否有轮次内空闲窗口
        select  count(B.CKBH00) into ls_count
         from BM_CKDY00 A, BM_CKBM00 B,
         (select PYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1','2')  group by PYCKBH) C
         where A.DCKBH0 = B.CKBH00  and  B.FLAG00 = '2'  --and B.ZT0000='0',='0'是开机状态
             and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0)
             and  A.DCKBH0 = C.PYCKBH(+)
             and  B.BMBH00 = ad_YFBMBH
             and  B.ZT0000='0'
             and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
             and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
             and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
             --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
             -- and  nvl(NUM,0)=0
             and  B.DLLS00=0;
         if (ls_count=0) then --空闲窗口 --01
         --重置轮次开始发牌分配
           update BM_CKBM00
           set DLLS00=0
           where CKBH00 in (
               select B.CKBH00 from BM_CKDY00 A, BM_CKBM00 B,
                 (select PYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1','2')  group by PYCKBH) C
               where A.DCKBH0 = B.CKBH00  and  B.FLAG00 = '2'
               and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0)
               and  A.DCKBH0 = C.PYCKBH(+)
               and  B.BMBH00 = ad_YFBMBH
               and  B.ZT0000='0'
               and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
               and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
               and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
               --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
               );
            --commit;
          end if; --01
          select  CKBH00 into ls_PYCKBH  from ( select B.CKBH00 from BM_CKDY00 A, BM_CKBM00 B,
                 (select PYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1','2')  group by PYCKBH) C
             where A.DCKBH0 = B.CKBH00  and  B.FLAG00 = '2'
             and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0)
             and  A.DCKBH0 = C.PYCKBH(+)
             and  B.BMBH00 = ad_YFBMBH
             and  B.ZT0000='0'
             and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
             and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
             and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
             --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
             -- and  nvl(NUM,0)=0
             and  B.DLLS00=0
             order by B.DLLS00,nvl(NUM,0))
           where  rownum = 1;
       exception
           when others then
              ls_PYCKBH:=0;
       end;
     end if;
     ls_CKBH00:=ls_PYCKBH;
  end if;
  if ad_CKLX00 = 2 then  --计算发药窗口
     if  ls_FYCKBH=0 then
       begin
       --是否有轮次内空闲窗口
         select count(E.CKBH00) into ls_count from BM_CKDY00 A, BM_CKBM00 B,
         (select FYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1')  group by FYCKBH) C,
         BM_CKBM00 E
         where A.SCKBH0 = B.CKBH00  and  B.FLAG00 = '2'  --and B.ZT0000='0',='0'是开机状态
           and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0)
           and  A.DCKBH0 = C.FYCKBH(+)
           and  A.DCKBH0 = E.CKBH00 and E.FLAG00 ='3' and E.ZT0000='0'
           and  B.BMBH00 = ad_YFBMBH
           and  B.ZT0000='0'
           and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
           and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
           and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
           --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
           and  E.DLLS00=0;
         if (ls_count=0) then --01
           --重置轮次开始发牌分配
           update BM_CKBM00
           set DLLS00=0
           where CKBH00 in
                (select distinct(E.CKBH00) from BM_CKDY00 A, BM_CKBM00 B,
                (select FYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1')  group by FYCKBH) C,
               BM_CKBM00 E where  A.SCKBH0 = B.CKBH00  and  B.FLAG00 = '2'
               and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0)
               and  A.DCKBH0 = C.FYCKBH(+) and  A.DCKBH0 = E.CKBH00
               and E.FLAG00 ='3' and E.ZT0000='0' and  B.BMBH00 = ad_YFBMBH  and  B.ZT0000='0'
               and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
               and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
               and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
               --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
               --and  E.DLLS00=1
               );
            --commit;
         end if; --01
         select  CKBH00 into ls_FYCKBH  from
             (select E.CKBH00 from BM_CKDY00 A, BM_CKBM00 B,
             (select FYCKBH,count(*) NUM  from YF_CFDL00 where ZT0000 in ('0','1')  group by FYCKBH) C,BM_CKBM00 E
              where A.SCKBH0 = B.CKBH00  and  B.FLAG00 = '2'  --and B.ZT0000='0',='0'是开机状态
            and (A.SCKBH0 = ad_CKBH00  or ad_CKBH00 = 0) and  A.DCKBH0 = C.FYCKBH(+)
            and  A.DCKBH0 = E.CKBH00 and E.FLAG00 ='3' and E.ZT0000='0'
            and  B.BMBH00 = ad_YFBMBH  and  B.ZT0000='0'
            and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
            and  instrb(nvl(B.YPDLBH,'0,1,2,3,4,6'),nvl(ls_YPDLBH,'0'))>0
            and (  (nvl(B.BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',ad_WHBTBR)>0 or (ad_BRNL00>=to_number(V_WHNLYXPD)))) or
                     (nvl(B.BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0'))
            --and  not exists (select 1 from BM_CKBMMX where CKBH00=B.CKBH00 and JLFL00='0' and instrb(';'||CZD000||';',ad_WHBTBR)<=0)
            and  E.DLLS00=0 order by E.DLLS00,nvl(NUM,0))
         where  rownum = 1;

        exception
           when others then
              ls_FYCKBH:=0;
        end;
      end if;
      ls_CKBH00:=ls_FYCKBH;
  end if;
  as_RSCKBH:=ls_CKBH00;
 -- 单独调试过程
 -- update bm_ckbm00 set DLLS00='1' where CKBH00=ls_CKBH00;
 -- commit;
exception
  when no_data_found then
    raise_application_error(-20001,substr('系统未无法查找到对应的可用的窗口,请记录本信息并与系统管理员联系！*'||sqlerrm,1,220));
    rollback;
  when others  then
    raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*',1,220));
    rollback;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 39、门诊病人处方信息记账  SP_PY_SCPHDL
create or replace procedure SP_PY_SCPHDL
(
        as_CFLSH0     in   char,              --处方流水号
        ad_SFCKBH     in   number default 0,           --收费窗口编号 (Add on 2003-03-31)
        ad_PYCKBH     in   number default 0,
        ad_FYCKBH     in   number default 0,
        as_YHMSG0     out  varchar,           --存储过程提示的错误信息
        as_SYSMSG     out  varchar,            --系统提示的错误信息
        as_BYLX00     in   char default '0', --摆药类型 0 人工摆药 1 机器摆药
        as_SFZDDY     in   char default '0'  --是否自动打印 0 自动打印 1 不自动打印 机器摆药时不自动打印 
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF         2011.05.10  插入处方队列
-- CSF         2011.05.15  如果处方中的药品只包含通用字典中描述的不进入排号队列的药品，以病人未取药的所有处方为判断范围而不是判断一张处方。
-- CSF         2011.06.17  同个病人取原有的窗口要时候，若原有窗口为0还是要重新取
-- CSF         2011.06.20  发药窗口为0时候，把配药窗口也改为0
-- CSF         2011.11.09  毒麻（dpdj00>0 or mzdj00>0）处方不进入排号队列 用参数控制PY_DMCFSFBJRPHDL
-- laijg       2011.12.23  针对中药代煎处方，启用中药代煎管理系参数情况下调用SP_PY_INSERTJYXX by YFPY-20111205-001
-- CSF         2012.02.29  取号时候，取病人原窗口处方必须判断原窗口开启且存在对应开启的发药窗口  YFQH-20120225-001
-- laijg       2012.03.29  通过设置参数 实现两费保健、离休老干部、85岁以上老人等分费别分年龄的优先排队  by YFPY-20120313-002
-- csf         2012.04.13  按BM_CKBMMX表的记录，判断某些开单科室的处方指定到具体的窗口。 YFPY-20120405-001
-- laijg       2012.05.24  YFPY-20111205-001,该需求的功能加个参数控制 by YFPY-20120524-001
-- csf         2012.06.05  YFPY-20120605-001,SP_PY_SCPHDL,取不到窗口值的时候，应将pyckbh和fyckbh置成0.
-- laijg       2013.05.16  病人有两张处方时，在排号时会打出两张排队条码，而且会在不同的窗口取药，原因处方单格式在打开的窗口都不满足情况，取配药发药窗口再加一茬 by YFQH-20130305-001
-- daihq       2015.09.11  省二增加指定类别病人（高干病人）专窗 for YFPY-20150825-001
-- dsm         2015.10.20  省二代煎和普通自煎处方的配药窗口要分开 for YFPY-20151015-001
-- dsm         2015.10.20  MZPY_WHNLYXPDNEW此参数只要作用于某些药房 for YFPY-20151019-001
-- dsm         2015.12.03  增加参数MZPY_JSDJYPSFPD控制精神类处方是否进入排队叫号,MZPY_CXCFBJINRDL(冲销处方是否排队) for YFPY-20151130-005
-- dsm         2017.10.10  增加分配配药员for YFPY-20170718-002
-- huangjy     2018.06.06  增加当前窗口平均分配不按之前分配数多少分配  YFPY-20161212-001
-- huangjy     2018.08.06  摆药机增加处方队列写入BYLX00,SFZDDY字段 by YF-20180728-002
-- huangjy     2019.08.27  增加单处方自备药不进去排号 by YFPY-20190821-001
-- ---------   ----------  --------------------------------------------------------------------------------------------------
       ls_CFZJE0       YF_MZCF00.CFZJE0%TYPE;      --处方总金额
       ls_BRID00       YF_MZCF00.BRID00%TYPE;
       ls_GHID00       YF_MZCF00.GHID00%TYPE;
       ls_GHH000       YF_MZCF00.GHH000%TYPE;
       ls_BRXM00       YF_MZCF00.BRXM00%TYPE;
       ls_XB0000       YF_MZCF00.XB0000%TYPE;
       ls_CSRQ00       YF_MZCF00.CSRQ00%TYPE;
       ls_YBID00       BM_BRXXB0.YBID00%TYPE;
       ls_FBBH00       BM_BRXXB0.FBBH00%TYPE;
       ls_YBLB00       BM_BRXXB0.YBLB00%TYPE;
       ls_YBBRLB       BM_BRXXB0.YBBRLB%TYPE;
       ls_SRRQ00       YF_MZCF00.SRRQ00%TYPE;
       ls_SRSJ00       YF_MZCF00.SRSJ00%TYPE;
       ls_YSGZH0       YF_MZCF00.YSGZH0%TYPE;
       ls_SSKS00       YF_MZCF00.SSKS00%TYPE;
       ls_GHKS00       YF_MZCF00.GHKS00%TYPE;
       ls_YFBMBH       YF_MZCF00.YFBMBH%TYPE;
       ls_PSZT00       YF_MZCF00.PSZT00%TYPE;
       ls_ZYTS00       YF_MZCF00.ZYTS00%TYPE;
       ls_CFZT00       YF_MZCF00.CFZT00%TYPE;
       ls_BCCFH0       YF_MZCF00.BCCFH0%TYPE;
       ls_CFDGS0       VARCHAR2(10);
       ls_SFDM00       BM_YPLB00.SFDM00%TYPE;
       ls_SFXMMC       BM_YPLB00.SFXMMC%TYPE;
       ls_DLXH00       YF_CFDL00.DLXH00%TYPE;
       ls_PYCKBH       YF_CFDL00.PYCKBH%TYPE;  --配药窗口编码
       ls_FYCKBH       YF_CFDL00.FYCKBH%TYPE;  --发药窗口编码
       ls_ZFBL00       SF_FYMX00.ZFBL00%TYPE;
       ls_GFBL00       SF_FYMX00.ZFBL00%TYPE;
       ls_ZFJE00       SF_FYMX00.ZFJE00%TYPE;
       ls_GFJE00       SF_FYMX00.GFJE00%TYPE;
       ls_JZJE00       SF_FYMX00.JZJE00%TYPE;
       ls_HJJE00       SF_FYMX00.HJJE00%TYPE;
       ls_QZFJE0       SF_FYMX00.QZFJE0%TYPE;
       ls_djh000       YF_MZCF00.DJH000%TYPE;--收费单据号
       --P_CFMXJE_CZFS   XT_XTCS00.VALUE0%TYPE;
       --P_CFZJE_CZFS    XT_XTCS00.VALUE0%TYPE;
       --P_CFZJE_SRWS    XT_XTCS00.VALUE0%TYPE;
       --P_CFMXJE_SRWS   XT_XTCS00.VALUE0%TYPE;
       V_DJZYCFBJRCFDL   XT_XTCS00.VALUE0%TYPE;
       ls_CZRQ00       CHAR(8);
       ls_CZSJ00       CHAR(8);
       ls_MZPY_YYGXH   CHAR(10);   --医院配药个性化参数
       ls_SFKSLY       YF_MZCF00.SFKSLY%TYPE;
       --Modified on 2001-11-02
       V_ZYDJS0    YF_MZCF00.ZYDJS0%TYPE;
       V_YPDLBH    YF_MZCF00.YPDLBH%TYPE;
       V_JYSFDM    BM_YYSFXM.SFXMID%TYPE;            --中药煎药费收费代码
       V_JYSFMC    BM_YYSFXM.XMMC00%TYPE;            --中药煎药费收费名称
       --V_JYSFJE    BM_YYSFXM.SFJE00%TYPE;          --中药煎药费收费金额
       v_counter  number;
       v_SFCFZYJJ         CHAR(1);--处方是否转预交金，Y转 N不转
       V_COUNT0  number(3,0);
       V_CFLSH0_temp00  varchar2(40);--临时处方流水号;
       ls_CFLSH0_temp00  varchar2(40);--临时处方流水号;
       V_CFLSH0     YF_MZCF00.cflsh0%type;--处方流水号
       V_SFDJFD     Xt_XTCS00.value0%type;--是否代煎分单
       V_SFBFDJ        Number(2);
       L         number(2);
       I         number(2);
       J         number(2);
       ls_YHMSG0      varchar2(400);
       ls_SYSMSG      varchar2(400);
       V_SFQYDJGLGN  Xt_XTCS00.value0%type;--是否启用中药代煎管理系统
       V_DJDH00     number;--返回代煎单号
       V_FBBH00     sf_brxxb0.fbbh00%TYPE;
       V_YBZXLB     sf_brxxb0.ybzxlb%TYPE;
       V_YBBRLB     sf_brxxb0.ybbrlb%TYPE;
       V_WHNLYXPD   XT_XTCS00.VALUE0%TYPE;      --维护年龄优先排队
       V_WHBTLBBRYXPD   XT_XTCS00.VALUE0%TYPE;  --维护不同类别病人优先排队
       V_SCPHDLSFCRJYXX XT_XTCS00.VALUE0%TYPE;  --生成排号队列时是否插入煎药信息
       V_SCPHDLSFAKQCKPJFP  XT_XTCS00.VALUE0%TYPE;  --生成排号队列时是否按开启窗口平均分配
       V_NL     NUMBER;
       V_WHBTBR  VARCHAR(20);
       DJFD_ERR    exception;
       V_WHNLYXPDNEW      varchar2(100);
       v_GLBRJZYCKYFBMBH   varchar2(100);
       V_SJDJ00   number(5);
       LS_YPPZS0  number(5);
       LS_PYR000  YF_CFDL00.PYR000%type;
       v_counter_ZBY number(5);
BEGIN
    select count(*) into v_counter  from YF_CFDL00 where CFLSH0=AS_CFLSH0;
    if v_counter>0 then
     return;
    end if;
  --精神等级>0的药品不排队
    select count(*) into  v_counter from XT_XTCS00 where NAME00='MZPY_JSDJYPSFPD' and VALUE0='N' ;
    if v_counter>0 then
      select count(*) into V_SJDJ00  from YF_MZCFMX　a,BM_YD0000 b where a.CFLSH0=V_CFLSH0 and a.YPNM00=b.YPNM00 and nvl(SJDJ00,0)>0;
      if V_SJDJ00>0 then
        return;
      end if;
    end if;

    select count(*) into v_counter from XT_XTCS00 where NAME00='PY_DMCFSFBJRPHDL' and VALUE0='Y';
    if v_counter>0 then
      select count(*) into v_counter from YF_MZCFMX a,BM_YD0000   b where a.CFLSH0=AS_CFLSH0 and a.YPNM00=b.YPNM00 and (b.DPDJ00>0 or b.MZDJ00>0);
      if  v_counter>0 then
        return;
      end if;
    end if;

  --被冲销处方是否不排队
    select count(*) into  v_counter from XT_XTCS00 where NAME00='MZPY_CXCFBJINRDL' and VALUE0='N' ;
    if v_counter>0 then
      select count(*) into v_counter  from YF_MZCF00 a where  CFLSH0=AS_CFLSH0 and  exists(select 1 from YF_MZCF00 where GHID00=a.GHID00 and BCCFH0=a.CFLSH0);
      if v_counter>0 then
        return;
      end if;
      select count(*) into v_counter  from YF_MZCF00 a where  CFLSH0=AS_CFLSH0 and BCCFH0 is not null and not exists(select 1 from YF_CFDL00 where CFLSH0=a.BCCFH0 and GHID00=a.GHID00);
      if v_counter>0 then  --如果正的处方没有排号,负数处方也不排
        return;
      end if;
    end if;
    
    --单处方自备药不排队 for YFPY-20190821-001
    select count(*) into v_counter_ZBY from YF_MZCFMX a where a.CFLSH0=AS_CFLSH0 and a.SFZBY0='Y';
    select count(*) into v_counter from YF_MZCFMX a where a.CFLSH0=AS_CFLSH0 ;
    if  (v_counter=1) and (v_counter_ZBY=1) then
      return;
    end if;
    
    begin
     select NVL(A.BRID00,0),SFKSLY,A.PSZT00,B.SFDM00,B.SFXMMC,A.SRRQ00,A.SRSJ00,A.YSGZH0,A.SSKS00,A.GHKS00,A.YFBMBH,
            A.ZYTS00,A.ZYDJS0,A.YPDLBH,A.GHID00,A.GHH000,A.BRXM00,A.XB0000,A.CSRQ00,A.CFZT00,A.BCCFH0,A.CFDGS0||','||decode(A.ZYDJS0,0,A.YPDLBH,'6'),
            A.DJH000,A.CFZJE0
       into ls_BRID00,ls_SFKSLY,ls_PSZT00,ls_SFDM00,ls_SFXMMC,ls_SRRQ00,ls_SRSJ00,ls_YSGZH0,ls_SSKS00,ls_GHKS00,ls_YFBMBH,
            ls_ZYTS00,V_ZYDJS0,V_YPDLBH,ls_GHID00,ls_GHH000,ls_BRXM00,ls_XB0000,ls_CSRQ00,ls_CFZT00,ls_BCCFH0,ls_CFDGS0,ls_DJH000,LS_CFZJE0
       from YF_MZCF00 A,BM_YPLB00 B where A.YPDLBH = B.LBBH00  and A.CFLSH0 = AS_CFLSH0 ;
    exception
      when NO_DATA_FOUND then
                  as_YHMSG0:='处方(CFLSH0='''||as_CFLSH0||''')不存在,登记非法!';
                  as_SYSMSG:=SQLERRM;
                  rollback;
                  return;
    end;

    begin
     select  a.FBBH00,a.YBZXLB,a.YBBRLB,SF_YS_CSRQTONL(a.CSRQ00)  into V_FBBH00,V_YBZXLB,V_YBBRLB,V_NL  from SF_BRXXB0 a,YF_MZCF00 b
     where  a.GHID00=b.GHID00  and b.CFLSH0=AS_CFLSH0;
    exception
      when NO_DATA_FOUND then
                  as_YHMSG0:='处方(CFLSH0='''||as_CFLSH0||''')未收费,登记非法!';
                  as_SYSMSG:=SQLERRM;
                  rollback;
                  return;
    end;

    V_WHBTBR:=';'||to_char(V_FBBH00)||','||V_YBZXLB||','||V_YBBRLB||';';

    --取参数有待煎的中药处方是否不进入处方配药队列
    begin
      select trim(Value0) into V_DJZYCFBJRCFDL from XT_XTCS00 where NAME00='MZPY_DJZYCFBJRCFDL';
    exception
      when others then
        V_DJZYCFBJRCFDL:='N';
    end;

        --是否启用中药代煎管理系统
    begin
      select trim(Value0) into V_SFQYDJGLGN   from XT_XTCS00 where NAME00='YF_SFQYDJGLGN';
    exception
      when others then
        V_SFQYDJGLGN:='N';
    end;

    --维护年龄优先排队
    begin
      select trim(Value0) into V_WHNLYXPD   from XT_XTCS00 where NAME00='MZPY_WHNLYXPD';
    exception
      when others then
        V_WHNLYXPD:='0';
    end;

    --维护不同类别病人优先排队
    begin
      select trim(Value0) into V_WHBTLBBRYXPD   from XT_XTCS00 where NAME00='MZPY_WHBTLBBRYXPD';
    exception
      when others then
        V_WHBTLBBRYXPD:='N';
    end;

    v_GLBRJZYCKYFBMBH:='0';
    --维护年龄优先排队,年龄:YFBMBH1,YFBMBH2
    begin
      select nvl(max(trim(Value0)),0) into V_WHNLYXPDNEW   from XT_XTCS00 where NAME00='MZPY_WHNLYXPDNEW';
      v_counter:=instr(V_WHNLYXPDNEW,':');
      if v_counter>0 then
        v_GLBRJZYCKYFBMBH:=substr(V_WHNLYXPDNEW,v_counter+1,100);
        V_WHNLYXPDNEW:=substr(V_WHNLYXPDNEW,1,v_counter-1);
      else
        v_GLBRJZYCKYFBMBH:='0';
      end if;
    exception
      when others then
        V_WHNLYXPDNEW:='0';
        v_GLBRJZYCKYFBMBH:='0';
    end;
    if V_WHNLYXPDNEW<>'0' then
      if instr(','||v_GLBRJZYCKYFBMBH||',',','||ls_YFBMBH||',')=0 then
        V_WHNLYXPDNEW:='0';
      end if;
    end if;
    
    
        --生成排号队列时是否插入煎药信息
    begin
      select trim(Value0) into V_SCPHDLSFCRJYXX   from XT_XTCS00 where name00='MZPY_SCPHDLSFCRJYXX';
    exception
      when others then
        V_SCPHDLSFCRJYXX:='N';
    end;
   
   --取参数生成排号队列时是否按开启窗口平均分配
    begin
      select trim(Value0) into V_SCPHDLSFAKQCKPJFP from XT_XTCS00 where name00='MZPY_SCPHDLSFAKQCKPJFP';
    exception
      when others then
        V_SCPHDLSFAKQCKPJFP:='N';
    end;


    /*省二中药待煎处方不进入处方配药队列 开始*/
    if NOT ((V_DJZYCFBJRCFDL='Y') and (V_YPDLBH='2') AND (V_ZYDJS0>0) ) THEN

      --医院配药个性化参数
      select substrb(nvl(max(trim(value0)),'0'),1,1) into ls_MZPY_YYGXH from XT_XTCS00 where name00='MZPY_YYGXH';
            --Modify on 2002-12-01, At 广州,添加处方队列表
      if (NVL(ad_PYCKBH,0)<>0)and(NVL(ad_FYCKBH,0)<>0) then
         ls_PYCKBH:=ad_PYCKBH;
         ls_FYCKBH:=ad_FYCKBH;
      elsif ls_MZPY_YYGXH='1' then --省二模式，配药窗口和发药窗口交叉。
            ls_PYCKBH:=0;
            --省二增加指定类别病人（高干病人）专窗，指定类别在BM_CKBMMX.CZD000中维护   //指定科室指定发往窗口
            --维护不同类别病人，格式为：sf_brxxb0.fbbh00,sf_brxxb0.ybzxlb,sf_brxxb0.ybbrlb 一组数值用逗号隔开，多组用;隔开
            select count(*) into v_counter from BM_CKBM00 a,BM_CKBMMX b where a.CKBH00=b.CKBH00 and b.JLFL00='0'
            and b.CZD000 is not null
            and (instrb(';'||b.CZD000||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPDNEW) and to_number(V_WHNLYXPDNEW)>0) )
            and a.ZT0000='0' and a.FLAG00='2' and a.BMBH00=ls_YFBMBH;

            if v_counter>0 then --这里需要注意的是，虽然指定了科室，但病人满足到优先窗口条件时候，优先窗口优先
              select  nvl(max(a.PYCKBH),0) into ls_PYCKBH from YF_CFDL00 a
                             where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                             and exists (select 1 from BM_CKBM00 where a.PYCKBH=CKBH00 and ZT0000 in ('0')
                             and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                             and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                             and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD))))
                                  or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') ) )
                             and exists (select 1 from BM_CKBMMX where CKBH00=a.PYCKBH and JLFL00='0' and CZD000 is not null
                                 and (instrb(';'||CZD000||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPDNEW) and to_number(V_WHNLYXPDNEW)>0) ))
                             --该配药窗口存在对应的开启状态的发药窗口
                             and exists (select 1 from BM_CKDY00 b,BM_CKBM00 c where b.SCKBH0=a.PYCKBH and b.DCKBH0=c.CKBH00 and c.ZT0000='0')
                             and a.PYCKBH>0 ;
            end if;

            if ls_PYCKBH=0 then
              --判断是否开单科室是否指定发往窗口。   //指定科室指定发往窗口
             select count(*) into v_counter from BM_CKBM00 a,BM_CKBMMX b where a.CKBH00=b.CKBH00 and b.JLFL00='0'
              and b.NZD000=ls_GHKS00 and a.ZT0000='0' and a.FLAG00='2' and a.BMBH00=ls_YFBMBH;

              if v_counter>0 then --这里需要注意的是，虽然指定了科室，但病人满足到优先窗口条件时候，优先窗口优先
                select  nvl(max(a.PYCKBH),0) into ls_PYCKBH from YF_CFDL00 a
                               where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                               and exists (select 1 from BM_CKBM00 d where a.PYCKBH=CKBH00 and ZT0000 in ('0')
                                            and ((nvl(d.ypdlbh,'0')='6' and  V_ZYDJS0>0) or (nvl(d.ypdlbh,'0')<>'6' and V_ZYDJS0=0))
                                            and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                                            and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                                            and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD))))
                                                or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') )
                                           )
                               and exists (select 1 from BM_CKBMMX where CKBH00=a.PYCKBH and JLFL00='0' and NZD000=ls_GHKS00)

                               --该配药窗口存在对应的开启状态的发药窗口
                               and exists (select 1 from BM_CKDY00 b,BM_CKBM00 c where b.SCKBH0=a.PYCKBH and b.DCKBH0=c.CKBH00 and c.ZT0000='0')
                               and a.PYCKBH>0 ;
              end if;
            end if;

            if (ls_PYCKBH=0) then
              begin
              --配药窗口尽量取同一配药窗口，避免病人多窗口等待(原则:配药窗口开放，处方未发药的)
              select  a.PYCKBH into ls_PYCKBH from YF_CFDL00 a
                             where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                             and exists (select 1 from BM_CKBM00 d where a.PYCKBH=CKBH00 and ZT0000 in ('0')
                             and ((nvl(d.ypdlbh,'0')='6' and  V_ZYDJS0>0) or (nvl(d.ypdlbh,'0')<>'6' and V_ZYDJS0=0))
                             and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                             and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                             and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD))))
                                  or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') ) )
                             and not exists (select 1 from BM_CKBMMX where CKBH00=a.PYCKBH and JLFL00='0' and ((NZD000<>ls_GHKS00) or (instrb(';'||CZD000||';',V_WHBTBR)<=0)))
                             --该配药窗口存在对应的开启状态的发药窗口
                             and exists (select 1 from BM_CKDY00 b,BM_CKBM00 c where b.SCKBH0=a.PYCKBH and b.DCKBH0=c.CKBH00 and c.ZT0000='0')
                             and rownum=1
                             and a.PYCKBH>0 ;
              exception
                when others then
                  ls_PYCKBH :=0;
              end;
            end if;

            if ls_PYCKBH=0 then
            begin
              --配药窗口尽量取同一配药窗口，避免病人多窗口等待(原则:配药窗口开放，处方未发药的)
              select  a.PYCKBH into ls_PYCKBH from YF_CFDL00 a
                             where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                             and exists (select 1 from BM_CKBM00 d where a.PYCKBH=CKBH00 and ZT0000 in ('0')
                                    and ((nvl(d.ypdlbh,'0')='6' and  V_ZYDJS0>0) or (nvl(d.ypdlbh,'0')<>'6'  and V_ZYDJS0=0)) ) --for YFPY-20151015-001
                        --     and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                        --     and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                        --     and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD))))
                        --          or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') ) )
                        --     and not exists (select 1 from BM_CKBMMX where CKBH00=a.PYCKBH and JLFL00='0' and NZD000<>ls_GHKS00)
                             --2015.09.11 daihq add for YFPY-20150825-001
                             --配药窗口不取有指定类别的病人使用的窗口  CZD000维护的值和参数MZPY_WHBTLBBRYXPD一样
                             and not exists (select 1 from BM_CKBMMX where CKBH00=a.PYCKBH and JLFL00='0' and instrb(';'||CZD000||';',V_WHBTBR)<=0)
                             --该配药窗口存在对应的开启状态的发药窗口
                             and exists (select 1 from BM_CKDY00 b,BM_CKBM00 c where b.SCKBH0=a.PYCKBH and b.DCKBH0=c.CKBH00 and c.ZT0000='0')
                             and rownum=1
                             and a.PYCKBH>0 ;
            exception
              when others then
              --病人没有特殊制定窗口，没有尽量取同一配药窗口情况下，配药窗口重新取
              BEGIN
                if V_SCPHDLSFAKQCKPJFP='Y' then
                  SP_PY_GETCKBH(ls_YFBMBH,0,1,ls_CFDGS0,V_NL,ls_GHKS00,V_WHBTBR,0,ls_PYCKBH);  -- 从过程中得到配药窗口
                else
                  ls_PYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ad_SFCKBH,1,ls_CFDGS0,V_WHBTBR,V_NL,ls_GHKS00);  --从函数中得到配药窗口
                end if;
              EXCEPTION
              WHEN  OTHERS  THEN
                     as_YHMSG0:='获取该处方的配药窗口失败,请稍候再试!';
                     as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(0)||',1)';
                     ROLLBACK;
                     RETURN;
              END;
            end;
            end if;
            if V_SCPHDLSFAKQCKPJFP='Y' then
               update BM_CKBM00  set DLLS00=1 where CKBH00=ls_PYCKBH and FLAG00='2';
            end if;
            --省二增加指定类别病人（高干病人）专窗，指定类别在BM_CKBMMX.CZD000中维护
            --维护不同类别病人，格式为：sf_brxxb0.fbbh00,sf_brxxb0.ybzxlb,sf_brxxb0.ybbrlb 一组数值用逗号隔开，多组用;隔开
            ls_FYCKBH:=0;--这里需要注意的是，虽然指定了科室，但病人满足到优先窗口条件时候，优先窗口优先
            select count(*) into v_counter from BM_CKBM00 a,BM_CKBMMX b where a.CKBH00=b.CKBH00 and b.JLFL00='1' and b.CZD000 is not null
            and (instrb(';'||b.CZD000||';',V_WHBTBR)>0  or V_NL>=to_number(V_WHNLYXPDNEW))
            and a.ZT0000='0' and a.FLAG00='3' and a.BMBH00=ls_YFBMBH;
            if  v_counter>0  then
              select  nvl(max(a.FYCKBH),0) into ls_FYCKBH from YF_CFDL00 a
                            where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                             and exists (select 1 from BM_CKBM00 where a.FYCKBH=CKBH00 and ZT0000 in ('0')
                             and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                             and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                             and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD)))) or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') )
                             )
                             and exists (select 1 from BM_CKBMMX where CKBH00=a.FYCKBH and JLFL00='1' and CZD000 is not null
                                 and (instrb(';'||CZD000||';',V_WHBTBR)>0 or V_NL>=to_number(V_WHNLYXPDNEW)))
                             and a.FYCKBH in (select DCKBH0 from BM_CKDY00 where SCKBH0=ls_PYCKBH) and rownum=1
                             and a.FYCKBH>0;
            end if;

            if ls_FYCKBH=0 then
              --判断是否开单科室是否指定发往窗口。
              select count(*) into v_counter from BM_CKBM00 a,BM_CKBMMX b where a.CKBH00=b.CKBH00 and b.JLFL00='1'
              and b.NZD000=ls_GHKS00 and a.ZT0000='0' and a.FLAG00='3' and a.BMBH00=ls_YFBMBH;
              if  v_counter>0  then
                select  nvl(max(a.FYCKBH),0) into ls_FYCKBH from YF_CFDL00 a
                               where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                               and exists (select 1 from BM_CKBM00 where a.FYCKBH=CKBH00 and ZT0000 in ('0')
                               and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                               and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                               and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD)))) or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') )
                               )
                               and exists (select 1 from BM_CKBMMX where CKBH00=a.FYCKBH and JLFL00='1' and NZD000=ls_GHKS00)
                               and a.FYCKBH in (select DCKBH0 from BM_CKDY00 where SCKBH0=ls_PYCKBH) and rownum=1
                               and a.FYCKBH>0;
              end if;
            end if;

              if ls_FYCKBH=0 then
              begin
                --发药窗口尽量取同一发药窗口，避免病人多窗口等待(原则:发药窗口开放，处方未发药的)
                select  a.FYCKBH into ls_FYCKBH from YF_CFDL00 a
                               where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                               and exists (select 1 from BM_CKBM00 where a.FYCKBH=CKBH00 and ZT0000 in ('0')
                               and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                               and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                               and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD)))) or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') )
                               )
                               and not exists (select 1 from BM_CKBMMX where CKBH00=a.FYCKBH and JLFL00='1' and ((NZD000<>ls_GHKS00) or (instrb(';'||CZD000||';',V_WHBTBR)<=0)))
                               and a.FYCKBH in (select DCKBH0 from BM_CKDY00 where SCKBH0=ls_PYCKBH) and rownum=1
                               and a.FYCKBH>0;
              exception
                when others then
                  ls_FYCKBH :=0;

              end;
            end if;

            if ls_FYCKBH=0 then
              begin
                --发药窗口尽量取同一发药窗口，避免病人多窗口等待(原则:发药窗口开放，处方未发药的)
                select  a.FYCKBH into ls_FYCKBH from YF_CFDL00 a
                               where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2')
                              and exists (select 1 from BM_CKBM00 where a.FYCKBH=CKBH00 and ZT0000 in ('0') )
                             --  and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0
                             --  and instrb(nvl(YPDLBH,'0,1,2,3,4,5,6'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0
                             --  and ((nvl(BRYXPD,'N')='Y' and (instrb(';'||V_WHBTLBBRYXPD||';',V_WHBTBR)>0 or (V_NL>=to_number(V_WHNLYXPD)))) or (nvl(BRYXPD,'N')='N' and V_WHBTLBBRYXPD='N' and V_WHNLYXPD='0') )
                             --  )
                             --  and not exists (select 1 from BM_CKBMMX where CKBH00=a.FYCKBH and JLFL00='1' and NZD000<>ls_GHKS00)
                               --2015.09.11 daihq add for YFPY-20150825-001
                               --配药窗口不取有指定类别的病人使用的窗口  CZD000维护的值和参数MZPY_WHBTLBBRYXPD一样
                               and not exists (select 1 from BM_CKBMMX where CKBH00=a.FYCKBH and JLFL00='1' and instrb(';'||CZD000||';',V_WHBTBR)<=0)
                               and a.FYCKBH in (select DCKBH0 from BM_CKDY00 where SCKBH0=ls_PYCKBH) and rownum=1
                               and a.FYCKBH>0;
              exception
                when others then
                BEGIN
                  if V_SCPHDLSFAKQCKPJFP='Y' then
                    SP_PY_GETCKBH(ls_YFBMBH,ls_PYCKBH,2,ls_CFDGS0,V_NL,ls_GHKS00,V_WHBTBR,0,ls_FYCKBH);  -- 从过程中得到配药窗口
                  else
                    ls_FYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ls_PYCKBH,2,ls_CFDGS0,V_WHBTBR,V_NL,LS_GHKS00);   --从函数中得到发药窗口
                  end if;
                EXCEPTION
                  WHEN  OTHERS  THEN
                            as_YHMSG0:='获取该处方的发药窗口失败,请稍候再试!';
                            as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ls_PYCKBH)||',2)';
                            ROLLBACK;
                            RETURN;
                END;
              end;
            end if;
            if V_SCPHDLSFAKQCKPJFP='Y' then
              update BM_CKBM00  set DLLS00=1 where CKBH00=ls_FYCKBH and FLAG00='3';
            end if;
      end if;


      --else    --广东模式,现金记账且本次挂号处方一次记账
      --    begin
      --           select  a.PYCKBH,a.FYCKBH into ls_PYCKBH,ls_FYCKBH from YF_CFDL00 a,yf_mzcf00 b
      --                    where a.cflsh0=b.cflsh0 and b.djh000=LS_DJH000 and a.YFBMBH=ls_YFBMBH
      --                    and rownum=1 and a.PYCKBH<>0; --qks 2009.09.17
      --    exception
      --           when others then
      --                BEGIN
      --                ls_PYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ad_SFCKBH,1,ls_CFDGS0);  --从函数中得到配药窗口
      --                EXCEPTION
      --                   WHEN  OTHERS  THEN
      --                              as_YHMSG0:='获取该处方的配药窗口失败,请稍候再试!';
      --                              as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(0)||',1)';
      --                              ROLLBACK;
      --                              RETURN;
      --                END;
      --                BEGIN
      --                ls_FYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ls_PYCKBH,2,ls_CFDGS0);   --从函数中得到发药窗口
      --
      --                EXCEPTION
      --                   WHEN  OTHERS  THEN
      --                              as_YHMSG0:='获取该处方的发药窗口失败,请稍候再试!';
      --                              as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ls_PYCKBH)||',2)';
      --                              ROLLBACK;
      --                              RETURN;
      --                END;
      --    end;
      --end if;
      IF NVL(TRIM(ls_SFKSLY),'N')='N' THEN -- zjf modif on 20030527
         if (ls_FYCKBH=0) or (ls_PYCKBH=0) or (ls_FYCKBH is null) or (ls_PYCKBH is null) then
           ls_PYCKBH:=0;
           ls_FYCKBH:=0;
         end if;
         select count(*) into v_counter from XT_XTCS00 where name00='MZPY_PDJHSFFPDR' and instr(','||trim(VALUE0)||',',','||ls_YFBMBH||',')>0;
         select  SQ_YF_CFDL00_DLXH00.nextval   into  ls_DLXH00  from  dual;
         if v_counter>0 then
           LS_PYR000:=SF_PY_GETMRPYR(ls_YFBMBH);
         else
           LS_PYR000:=0;
         end if;
         Insert  into YF_CFDL00(DLXH00,CFLSH0,YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000,PYR000,BYLX00,SFZDDY)
            values(ls_DLXH00,as_CFLSH0,ls_YFBMBH,ls_CFZJE0,ls_GHID00,ls_GHH000,ls_BRID00,ls_BRXM00,ls_XB0000,ls_CSRQ00,ls_PYCKBH,ls_FYCKBH,'0',ls_PYR000,as_BYLX00,as_SFZDDY);
         select nvl(ZYTS00,1) into ls_ZYTS00 from YF_MZCF00 where CFLSH0=as_CFLSH0;
         if LS_PYR000>0 then    ---YFPY-20170718-002
           select  count(*)*ls_ZYTS00 into LS_YPPZS0 from YF_MZCFMX where CFLSH0=as_CFLSH0 and SFZBY0='N';
           update YF_CFDL00 set YPPZSL=LS_YPPZS0 where DLXH00= ls_DLXH00 ;
           update SF_PYRYB0 set DPYSL0=nvl(DPYSL0,0)+LS_YPPZS0 where YFBMBH=ls_YFBMBH and YGBH00=ls_PYR000;
           update YF_MZCF00 set PFR000=LS_PYR000 where CFLSH0=as_CFLSH0;
         end if;
         --if as_BYLX00='1' then
         --   update YF_CFDL00 set DYRQ00=to_char(sysdate,'YYYYMMDD'),DYSJ00=to_char(sysdate,'HH24:MI:SS') where DLXH00= ls_DLXH00;
         --end if;
      END IF;
   if V_SCPHDLSFCRJYXX='Y' then
     IF  ((V_SFQYDJGLGN='Y') and (V_YPDLBH='2') AND (V_ZYDJS0>0) ) THEN  --by YFPY-20111205-001
       SP_PY_INSERTJYXX(as_CFLSH0,as_YHMSG0,as_SYSMSG,V_DJDH00);
     END if;
   end if;
    END IF;      /*省二中药待煎处方不进入处方配药队列 结束*/
--本处不加事务,在Delphi客户端处理
EXCEPTION
      WHEN NO_DATA_FOUND THEN
                  as_YHMSG0:='对应的处方不存在或已记过账,登记非法!';
                  as_SYSMSG:=SQLERRM;
                  ROLLBACK;
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_PY_SCPHDL('||AS_CFLSH0||','||',as_YHMSG0,as_SYSMSG)',1,120);
                ROLLBACK;
END;
/
show error;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%




prompt 创建过程自动将处方从发药状态置成发药状态 SP_FY_ZDPYTOFY
create or replace procedure SP_FY_ZDPYTOFY
(
 p_YFBMBH in number --药房部门编号 
)
as
MZPY_PYQRHZDZFYZTSJ  NUMBER(5);--配药处方自动置成发药处方的时间 0不处理
--modified history
-- CSF 2011.05.19 CREATE:创建过程自动将处方从发药状态置成发药状态
begin
	begin
		select to_number(trim(Value0)) into  MZPY_PYQRHZDZFYZTSJ from XT_XTCS00 where NAME00='MZPY_PYQRHZDZFYZTSJ';
	exception
	  when others then
	  MZPY_PYQRHZDZFYZTSJ:=0;  
  end;
  if MZPY_PYQRHZDZFYZTSJ>0 then
    update YF_CFDL00 a set ZT0000='3',FYRQ00=PYRQ00,FYSJ00=PYSJ00 where YFBMBH=P_YFBMBH 
     and ZT0000='2' and DYRQ00=to_char(sysdate,'YYYYMMDD') 
    and PYSJ00<=to_char(SYSDATE-MZPY_PYQRHZDZFYZTSJ/1440/60,'HH24:MI:SS')
    and exists(select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00='2' );
  end if;
	
exception
   
  when others then
     rollback;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
/*2012.2.22 LGHUA 因更新会造成问题产生，要求注释掉
prompt 创建过程生成排队语音叫号信息 SF_PY_YFPDYYXX
create or replace procedure sf_py_getyfpdyyxx(
p_YFBMBH    IN    NUMBER,    --药房部门编号
P_BRID00    IN    NUMBER,    --病人id 0取某个发药窗口,>0取该病人的信息
p_CKBH00    IN    NUMBER,    --发药窗口编号
p_QYXX00    out   varchar2,    --叫号信息
p_BRIDSTR   out   varchar2     --返回病人窜
)--RETURN VARCHAR2 IS         
as
-- CSF 2011.11.10 CREATE:返回处方队列的语音叫号信息
 v_return varchar2(1000);
 I      NUMBER(2);
 ls_mgbrstr_dg varchar2(500);
 ls_mgbrstr varchar2(500);
 ls_CKMC00 BM_CKBM00.CKMC00%type;--窗口名称
 v_CFDLSJ  NUMBER(5);
 MZPY_MGBRJHCS XT_XTCS00.VALUE0%type;--每个病人叫号次数 默认 1
 MZPY_JHLX     XT_XTCS00.VALUE0%type;--叫号类型：0-编号;1-姓名;2-编号与姓名 默认 0
 MZPY_MCSL     XT_XTCS00.VALUE0%type;--每次每个窗口叫号数量  默认 1
 MZPY_XHFYFS   XT_XTCS00.VALUE0%type;--序号发音方式 比如1002  1 一千零贰 0 一零零二 默认 0
 v_bridstr  varchar2(100);
 V_EXE_SQL  varchar2(225);
 ls_fydlxh  varchar2(50);
 cursor c_ckbrjhxx is 
 select * from (select * from (
                            select a.BRID00,a.BRXM00,b.FYCKBH,LS_CKMC00 CKMC00,min(FYDLXH) FYDLXH 
                              from YF_MZCF00 A,YF_CFDL00 B WHERE A.JZRQ00=to_char(sysdate,'YYYYMMDD') and A.CFLSH0=B.CFLSH0
                              and B.ZT0000='2' and B.DYRQ00=to_char(sysdate,'YYYYMMDD')  
                               and ( (b.BJBZ00='0' and B.PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS') and B.JHCS00=0)
                               or (b.BJBZ00='1'))                              
                               and  A.YFBMBH = p_YFBMBH  and B.FYCKBH=P_CKBH00
                               group by a.BRID00,a.BRXM00,b.FYCKBH ) order by FYDLXH) where ROWNUM<=MZPY_MCSL;
 cursor c_ckbrjhxx_dg is 
 select  a.BRID00,a.BRXM00,b.FYCKBH,ls_CKMC00 CKMC00,min(FYDLXH) FYDLXH 
                              from YF_MZCF00 A,YF_CFDL00 B WHERE A.JZRQ00=to_char(sysdate,'YYYYMMDD') and A.CFLSH0=B.CFLSH0
                              and B.ZT0000='2' and B.DYRQ00=to_char(sysdate,'YYYYMMDD')  
                              and B.PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS')
                              and  A.YFBMBH = p_YFBMBH  and B.FYCKBH=P_CKBH00 and a.BRID00=P_BRID00
                               group by a.BRID00,a.BRXM00,b.FYCKBH;                              
 
 v_ckbrjhxx    c_ckbrjhxx%rowtype;
 v_ckbrjhxx_dg c_ckbrjhxx_dg%rowtype;
                                         
begin
    --取序号的发音方式
    select nvl(max(trim(VALUE0)),'0') into MZPY_XHFYFS from XT_XTCS00 where NAME00='MZPY_XHFYFS' ;

	  --保留叫号时间，最短 15分钟    	  
    begin
      select to_number(VALUE0) into v_CFDLSJ from XT_XTCS00 where NAME00='XT_CFDLSJ' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CFDLSJ:=15;
    end;
    if v_CFDLSJ<15 then
      v_CFDLSJ:=15;
    end if;	
   
   begin 
     select to_number(VALUE0) into MZPY_MGBRJHCS from XT_XTCS00 where NAME00='MZPY_MGBRJHCS';
   exception
     when others then
     MZPY_MGBRJHCS:=2;
   end; 
   if MZPY_MGBRJHCS>=2 then
      MZPY_MGBRJHCS:=2;
   end if;
   begin 
     select to_number(VALUE0) into MZPY_JHLX from XT_XTCS00 where NAME00='MZPY_JHLX';
   exception
     when others then
     MZPY_JHLX:=0;
   end; 
   
   begin 
     select to_number(VALUE0) into MZPY_MCSL from XT_XTCS00 where NAME00='MZPY_MCSL';
   exception
     when others then
     MZPY_MCSL:=1;
   end;  
   if MZPY_MCSL>=2 then
      MZPY_MCSL:=2;
   end if;     
   v_return:=' ';
   if P_BRID00=0 then --循环叫号开始
     --for v_ckbh in (  select CKBH00,CKMC00 from  BM_CKBM00 where BMBH00=P_YFBMBH and FLAG00='3' and ZT0000='0' ) loop 
       select NVL(MAX(trim(CKMC00)),' ') into LS_CKMC00 from BM_CKBM00 where CKBH00=P_CKBH00;
        v_bridstr:=' ';

        begin 
        	open  c_ckbrjhxx;
        	  fetch c_ckbrjhxx into v_ckbrjhxx;
        	  while c_ckbrjhxx%found loop
        --for v_ckbrjhxx in c_ckbrjhxx  loop 
         --每个窗口病人处理 begin  
             if MZPY_XHFYFS='0' then
               ls_FYDLXH:=SP_PY_GETFYDLXH(to_char(v_ckbrjhxx.FYDLXH));
             else
              ls_FYDLXH:=to_char(v_ckbrjhxx.FYDLXH);
             end if;                   
             ls_mgbrstr:=' ';                      
             ls_mgbrstr_dg:='请';                             
             if MZPY_JHLX=0 then --编号
               ls_mgbrstr_dg:=ls_mgbrstr_dg||ls_FYDLXH;
             elsif MZPY_JHLX=1 then --姓名
               ls_mgbrstr_dg:=ls_mgbrstr_dg||v_ckbrjhxx.BRXM00;
             else                   --编号加姓名 
               ls_mgbrstr_dg:=ls_mgbrstr_dg||ls_FYDLXH||'号'||v_ckbrjhxx.BRXM00;
             end if;
             ls_mgbrstr_dg:=ls_mgbrstr_dg||'到'||v_ckbrjhxx.CKMC00||'取药';
             i:=MZPY_MGBRJHCS-1 ;
             ls_mgbrstr:=ls_mgbrstr_dg;
             --每个病人循环次数处理  begin 
             while i>0 loop 
                ls_mgbrstr:=ls_mgbrstr||ls_mgbrstr_dg; 
               i:=i-1;
             end loop;    
             --每个病人循环次数处理  end
             --修改病人的叫号次数
             if v_bridstr=' ' then 
                v_bridstr:=to_char(v_ckbrjhxx.BRID00);
             else
                v_bridstr:=v_bridstr||','||to_char(v_ckbrjhxx.BRID00);
             end if;   
             
             
    
             if v_return=' ' then
                v_return:=ls_mgbrstr;
             else
                v_return:=v_return||chr(13)||'?'||ls_mgbrstr;
             end if;
             --每个窗口病人处理 end   
             
             fetch c_ckbrjhxx into v_ckbrjhxx;
           end loop;
         close c_ckbrjhxx;
   --         deallocate c_ckbrjhxx;
       end;
       p_BRIDSTR:=v_bridstr;
       --执行语句
   --    if v_bridstr<>' ' then          
   --       V_EXE_SQL:='Update YF_CFDL00 a set a.JHCS00=nvl(a.JHCS00,0)+1 where DYRQ00=to_char(sysdate,''YYYYMMDD'') and BRID00 IN ('||v_bridstr||') and YFBMBH='||to_char(P_YFBMBH)||' and FYCKBH='||TO_CHAR(p_CKBH00)||' and ZT0000=''2'' ';
   --       SP_EXECUTE_SQL(V_EXE_SQL);
   --    end if;      

    -- end loop;
-- else --循环叫号结束
  --指定序号补叫 开始
--        p_BRIDSTR:=P_BRID00;
--        select NVL(MAX(trim(CKMC00)),' ') into LS_CKMC00 from BM_CKBM00 where CKBH00=P_CKBH00;
--        v_bridstr:=' ';
--        begin
--        	open c_ckbrjhxx_dg;
--        	fetch c_ckbrjhxx_dg into v_ckbrjhxx_dg;
--        	while c_ckbrjhxx_dg%found loop
--        	
 --       --for v_ckbrjhxx_dg in c_ckbrjhxx_dg loop 
 --        --每个窗口病人处理 begin                     
--             ls_mgbrstr:=' ';                      
--             ls_mgbrstr_dg:='请';                             
--             if MZPY_JHLX=0 then --编号
 --              ls_mgbrstr_dg:=ls_mgbrstr_dg||v_ckbrjhxx_dg.FYDLXH;
---             elsif MZPY_JHLX=1 then --姓名
---               ls_mgbrstr_dg:=ls_mgbrstr_dg||v_ckbrjhxx_dg.BRXM00;
--             else                   --编号加姓名 
--               ls_mgbrstr_dg:=ls_mgbrstr_dg||v_ckbrjhxx_dg.FYDLXH||'号'||v_ckbrjhxx_dg.BRXM00;
--             end if;
--             ls_mgbrstr_dg:=ls_mgbrstr_dg||'到'||v_ckbrjhxx_dg.CKMC00||'取药';
--             i:=MZPY_MGBRJHCS-1 ;
--             
--             --每个病人循环次数处理  begin 
--             ls_mgbrstr:=ls_mgbrstr_dg;
--             
--             --每个病人循环次数处理  begin 
 --            while i>0 loop 
--                ls_mgbrstr:=ls_mgbrstr||ls_mgbrstr_dg; 
--               i:=i-1;
--             end loop;    
--             --每个病人循环次数处理  end
---             --修改病人的叫号次数
--                      --修改病人的叫号次数
--             if v_bridstr=' ' then 
--                v_bridstr:=to_char(v_ckbrjhxx_dg.BRID00);
 --            else
 --               v_bridstr:=v_bridstr||','||to_char(v_ckbrjhxx_dg.BRID00);
 --            end if;                     
--    
 --            if v_return=' ' then
  --              v_return:=ls_mgbrstr;
 --            else
 --               v_return:=v_return||chr(13)||'?'||ls_mgbrstr;
--             end if;
 --            --每个窗口病人处理 end  
 --            fetch  c_ckbrjhxx_dg into v_ckbrjhxx_dg;
 --         end loop;
 ---         close c_ckbrjhxx_dg;
  --       deallocate c_ckbrjhxx_dg;
  --      end;
                 --执行语句
  --      if v_bridstr<>' ' then          
  --        V_EXE_SQL:='Update YF_CFDL00 a set a.JHCS00=nvl(a.JHCS00,0)+1 where DYRQ00=to_char(sysdate,''YYYYMMDD'') and BRID00 IN ('||v_bridstr||') and YFBMBH='||to_char(P_YFBMBH)||' and FYCKBH='||TO_CHAR(P_CKBH00)||' and ZT0000=''2'' ';
  --        SP_EXECUTE_SQL(V_EXE_SQL);
  --      end if;
  --指定序号补叫 结束
  end if;
	  
	/*if p_flag00=0 then --正常叫号病人语句
    v_return:='select BRXM00,FYCKBH,CKMC00,min(FYDLXH) FYDLXH from ( select B.FYDLXH,A.BRXM00,B.FYCKBH,'||
    ' (select CKMC00 FROM BM_CKBM00 where CKBH00=B.FYCKBH) CKMC00'||
    ' from YF_MZCF00 A,YF_CFDL00 B WHERE A.JZRQ00=to_char(sysdate,''YYYYMMDD'') and A.CFLSH0=B.CFLSH0'||
    ' and B.ZT0000=''2'' and B.DYRQ00=to_char(sysdate,''YYYYMMDD'')  '||
    ' and B.PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,''HH24:MI:SS'')'||
    ' and  A.YFBMBH = '||to_char(P_YFBMBH)||' and B.JHCS00=0'||
    ' order by PYSJ00 ) group by BRXM00,FYCKBH,CKMC00';
	else --补叫语句
    v_return:='select BRXM00,FYCKBH,CKMC00,min(FYDLXH) FYDLXH from ( select B.FYDLXH,A.BRXM00,B.FYCKBH,'||
    ' (select CKMC00 FROM BM_CKBM00 where CKBH00=B.FYCKBH) CKMC00'||
    ' from YF_MZCF00 A,YF_CFDL00 B WHERE A.JZRQ00=to_char(sysdate,''YYYYMMDD'') and A.CFLSH0=B.CFLSH0'||
    ' and B.ZT0000=''2'' and B.DYRQ00=to_char(sysdate,''YYYYMMDD'')  '||
    ' and  A.YFBMBH = '||to_char(P_YFBMBH)||
    ' order by PYSJ00 ) group by BRXM00,FYCKBH,CKMC00';	
  end if;
  */
/*  p_qyxx00:=substr(v_return,1,130) ;
exception	
when others then
  rollback;
  p_qyxx00:='-1';
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
*/




prompt 创建函数-输入发药队列序号，返回汉字数字 SP_PY_GETFYDLXH
create or replace function SP_PY_GETFYDLXH
(
 p_FYDLXH in varchar2 --输入发药队列序号，返回汉字数字
)return varchar2 is
v_return varchar2(50);
ls_FYDLXH varchar2(50);
--modified history
-- CSF 2011.11.10 CREATE:返回汉字数字
begin
   v_return:=p_FYDLXH;	
   ls_FYDLXH:=''; 
	 if p_FYDLXH is not null then
	   while length(v_return)>0 loop
	     select ls_FYDLXH||','||decode(substr(v_return,1,1),'0','零','1','一','2','二','3','三','4','四','5','五','6','六','7','七',
	     '8','八','9','九') into ls_FYDLXH from dual;
	     v_return:=substr(v_return,2,length(v_return)-1);
	   end loop;
	 end if;	    
  return ls_FYDLXH||',';
exception
  when others then
    v_return:='-1';
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--## laijg 2012.03.06 规范命名，用过程SP_PY_ZDPYTOFY替代，删除原来过程 by  YFPY-20120222-001
drop procedure SP_FY_ZDPYTOFY;
commit;
prompt 创建过程自动将处方从发药状态置成发药状态 SP_PY_ZDPYTOFY
create or replace procedure SP_PY_ZDPYTOFY
(
 p_YFBMBH in number --药房部门编号 
)
as
MZPY_PYQRHZDZFYZTSJ  number(5);--配药处方自动置成发药处方的时间 0不处理
--modified     date          comment
-- CSF         2011.05.19    CREATE:创建过程自动将处方从发药状态置成发药状态
-- laijg       2012.03.06    将过程名SP_FY_ZDPYTOFY规范为SP_PY_ZDPYTOFY by YFPY-20120222-001 
---------    ------------------------------------------------------------------
begin
	begin
		select to_number(trim(Value0)) into  MZPY_PYQRHZDZFYZTSJ from XT_XTCS00 where NAME00='MZPY_PYQRHZDZFYZTSJ';
	exception
	  when others then
	  MZPY_PYQRHZDZFYZTSJ:=0;  
  end;
   
  if MZPY_PYQRHZDZFYZTSJ>0 then
    update YF_CFDL00 a set ZT0000='3',FYRQ00=PYRQ00,FYSJ00=PYSJ00 where YFBMBH=P_YFBMBH 
     and ZT0000='2' and DYRQ00=to_char(sysdate,'YYYYMMDD') 
    and PYSJ00<=to_char(SYSDATE-MZPY_PYQRHZDZFYZTSJ/1440/60,'HH24:MI:SS')
    and exists(select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00='2' );
  end if;
	
exception
   
  when others then
     rollback;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
PROMPT 14.生成煎药信息表  SP_PY_INSERTJYXX
create or replace procedure SP_PY_INSERTJYXX
 (
  AS_CFLSH0     in   char,              --处方流水号
  AS_YHMSG0    out  varchar,          --存储过程提示的错误信息
  AS_SYSMSG    out  varchar,           --系统提示的错误信息
  AS_JYDH00    out  number             --返回煎药单号
 )


-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF        2010.05.16   CREATE,生成YF_JYXXB0数据
-- CSF        2010.06.21   取药时间按半小时取整
-- CSF        2010.06.24   第二天的取药时间点按设置的参数MZPY_DETQYSJ+排号的当前时间与参数MZPY_YCZDETQYSJ设置的时间差额。
--laijg       2011.05.25   加个参数MZPY_ZRQYSYSJ描述周日的顺延时间
--daihq       2013.11.12   增加参数YF_MZCFFYSFZJPYQR控制门诊处方发药是否直接中药煎药处方配方确认 by YF-20131022-003
 AS
  V_SFQYDJGLMK    xt_xtcs00.value0%type;
  VYF_MZCF00      yf_mzcf00%rowtype;
  V_EXE_SQL       varchar2(1000);
  E_EXCEPTION     exception;
  VERRORSQL       varchar2(1000);
  VCOUNT          number(3,0);--计数器
  VSFCRB0         char(1);--是否传染病 '0'不是 '1' 是
  VQYRQ00         char(8);--取药日期
  VQYSJ00         char(8);--取药时间
  V_ZCQYSJ        number(8,3);--正常取药顺延时间，单位为小时
  V_YCZDETQYSJ    XT_XTCS00.VALUE0%type; --延至第二天的时间点,格式为：XX时XX分00
  V_DETQYSJ       XT_XTCS00.VALUE0%type;--第二天取药时间点：格式为：XX时XX分00
  V_ZRQYSYSJ      XT_XTCS00.VALUE0%type; --针对周日延至第二天的时间点,格式为：XX时XX分00
  V_MZCFFYSFZJPYQR    XT_XTCS00.VALUE0%type;--门诊处方发药是否直接中药煎药处方配方确认，Y是N否，默认为N
  Begin
    as_JYDH00:=0;
    select nvl(max(trim(value0)),'N') into V_SFQYDJGLMK from XT_XTCS00 where name00='YF_SFQYDJGLGN';
    if V_SFQYDJGLMK='Y' then
      begin
        select nvl(max(trim(value0)),'N') into V_MZCFFYSFZJPYQR from XT_XTCS00 where name00='YF_MZCFFYSFZJPYQR';
      exception
        when others then
          V_MZCFFYSFZJPYQR := 'N';
      end;

      begin
        select a.* into vYF_MZCF00  from YF_MZCF00 a where a.CFLSH0=as_CFLSH0;
      exception
        when others then
          as_YHMSG0:='单据不存在';
          as_SYSMSG:='select * into vYF_MZCF00  from YF_MZCF00 where CFLSH0='||as_CFLSH0;
          raise E_EXCEPTION;
      end;

      if (vYF_MZCF00.YPDLBH='2') and (vYF_MZCF00.ZYDJS0>0) then
        select count(*) into  vCOUNT from YS_BRZDXX a  where a.GHH000=vYF_MZCF00.GHH000 and exists (select 1 from XT_ICD900 b where b.ICD900=a.ICD900 and b.SFCRB0>0);
        if vCount=0 then
          vSFCRB0:='0';
        else
        	vSFCRB0:='1';
        end if;
        if (vYF_MZCF00.ZYSFJJ='Y')  then
          V_ZCQYSJ:='2';
        ELSE
        select to_number(nvl(max(trim(value0)),'4')) into V_ZCQYSJ from XT_XTCS00 where name00='MZPY_ZCQYSJ';
        END IF;
        select nvl(max(trim(value0)),'16:00:00') into V_YCZDETQYSJ from XT_XTCS00 where name00='MZPY_YCZDETQYSJ';
        select nvl(max(trim(value0)),'10:30:00') into V_DETQYSJ from XT_XTCS00 where name00='MZPY_DETQYSJ';
        select nvl(max(trim(value0)),'14:30:00') into V_ZRQYSYSJ from XT_XTCS00 where name00='MZPY_ZRQYSYSJ';



    if  to_char(sysdate,'day')='星期日'and V_ZRQYSYSJ<>'' then


        if to_char(sysdate,'HH24:MI:SS')>=V_ZRQYSYSJ then
          vQYRQ00:=to_char(sysdate+1,'YYYYMMDD');
          vQYSJ00:=to_char(to_date(to_char(sysdate+1,'YYYYMMDD')||trim(V_DETQYSJ),'YYMMDDHH24:MI:SS')+(SYSDATE-TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')||V_ZRQYSYSJ ,'YYYYMMDDHH24:MI:SS')),'HH24:MI:SS') ;
        else
          vQYRQ00:=to_char(sysdate,'YYYYMMDD');
          vQYSJ00:=to_char(sysdate+V_ZCQYSJ/24,'HH24:MI:SS');
        end if;

     else


        if to_char(sysdate,'HH24:MI:SS')>=V_YCZDETQYSJ then
          vQYRQ00:=to_char(sysdate+1,'YYYYMMDD');
          vQYSJ00:=to_char(to_date(to_char(sysdate+1,'YYYYMMDD')||trim(V_DETQYSJ),'YYMMDDHH24:MI:SS')+(SYSDATE-TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')||V_YCZDETQYSJ ,'YYYYMMDDHH24:MI:SS')),'HH24:MI:SS') ;
        else
          vQYRQ00:=to_char(sysdate,'YYYYMMDD');
          vQYSJ00:=to_char(sysdate+V_ZCQYSJ/24,'HH24:MI:SS');
        end if;

    end if;




        --时间按半小时取整
        if substr(vQYSJ00,4,2)>='00' and substr(vQYSJ00,4,2)<='30' then
          vQYSJ00:=to_char(to_date(vQYRQ00||substr(vQYSJ00,1,2),'YYYYMMDDHH24')+1/48,'HH24:MI:SS');
        else
          vQYSJ00:=to_char(to_date(vQYRQ00||substr(vQYSJ00,1,2),'YYYYMMDDHH24')+1/24,'HH24:MI:SS');
        end if;



        insert into YF_JYXXB0(JYDH00,CFLSH0,GHH000,BRID00,BRXM00,XB0000,SFCRB0,ZYSFJJ,CSRQ00,PHRQ00,QYRQ00,QYSJ00)
        Select SQ_YF_JYXXB0_JYDH00.nextval,as_CFLSH0,vYF_MZCF00.GHH000,vYF_MZCF00.BRID00,vYF_MZCF00.BRXM00,vYF_MZCF00.XB0000,
          vSFCRB0,vYF_MZCF00.ZYSFJJ,vYF_MZCF00.CSRQ00,to_char(sysdate,'YYYYMMDD'),vQYRQ00,vQYSJ00 from dual where not exists (
          select 1 from YF_JYXXB0 b where b.CFLSH0=as_CFLSH0);
        if SQL%NOTFOUND then
          as_JYDH00:=0;
        else
          begin
            select JYDH00 into as_JYDH00 from YF_JYXXB0 where  CFLSH0=as_CFLSH0;
          exception
          when others then
            as_YHMSG0:='单据不存在';
            as_SYSMSG:='select JYDH00 into as_JYDH00 from YF_JYXXB0 where  CFLSH0='||as_CFLSH0;
            raise E_EXCEPTION;
          end;
        end if;
        if V_MZCFFYSFZJPYQR = 'Y' then
           update YF_JYXXB0 set PFRQ00=to_char(sysdate,'YYYYMMDD'),PFSJ00=to_char(sysdate,'HH24:MI:SS' ),PFCZY0=vYF_MZCF00.FYR000
           where CFLSH0=as_CFLSH0 and LYRQ00='20991231';
        end if;
      end if;

    end if;
    exception


        WHEN E_EXCEPTION THEN
             RAISE_APPLICATION_ERROR(-20001,substrb(as_YHMSG0||'！*'||as_SYSMSG,1,150));
        WHEN OTHERS THEN
             as_YHMSG0:='数据库错误：出错原因未知！';
             as_SYSMSG:=substr('执行存储过程错误:SP_YF_INSERTJYXX'||SQLERRM,1,150);
             RAISE_APPLICATION_ERROR(-20001,substrb(as_YHMSG0||'！*'||as_SYSMSG,1,150));
  end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 14.生成煎药信息表  SP_PY_INSERTZYJYXX
create or replace procedure SP_PY_INSERTZYJYXX
 (
  AS_CFLSH0     in   char,              --住院处方号
  AS_TQDJH0     in   number,              --病区医嘱提取单号
  AS_YZID00     in   number,            --病区医嘱ID号
  AS_ZYYZBZ     in   char,              --住院医嘱标志 ‘0’住院零散处方 '1' 医嘱处方
  AS_YHMSG0    out  varchar,          --存储过程提示的错误信息
  AS_SYSMSG    out  varchar,           --系统提示的错误信息
  AS_JYDH00    out  number             --返回煎药单号
 )


-- MODIFICATION HISTORY
-- Person      Date        Comments
-- laijg      2012.07.11   CREATE,生成生成住院零散处方、医嘱YF_ZYJYXX数据 YF-20120711-001
-- daihq      2013.11.12   增加参数YF_ZYCFFYSFZJPYQR控制住院处方发药是否直接住院中药煎药处方配方确认 by YF-20131022-003
-- daihq      2014.01.20   修改医嘱处方直接住院中药煎药处方配方确认的判断条件 by YF-20140116-001
-- qiulf      2016.04.29   YF_ZYJYXX增加俩个字段YYRQ00,ZFXX00用来保存首次服药日期、嘱咐 by ZYDJ-20160418-001
--------    ------------   ---------------------------------------------------------------
 as
  V_SFQYDJGLMK    xt_xtcs00.value0%type;
  VYF_ZYCF00      yf_zycf00%rowtype;
  VYF_YZYPSQ      yf_yzypsq%rowtype;
  VZY_BRXXB0      ZY_BRXXB0%rowtype;
  V_EXE_SQL       varchar2(1000);
  E_EXCEPTION     exception;
  VERRORSQL       varchar2(1000);
  VCOUNT          number(3,0);--计数器
  VSFCRB0         char(1);--是否传染病 '0'不是 '1' 是
  VQYRQ00         char(8);--取药日期
  VQYSJ00         char(8);--取药时间
  V_ZCQYSJ        number(8,3);--正常取药顺延时间，单位为小时
  V_YCZDETQYSJ    XT_XTCS00.VALUE0%type; --延至第二天的时间点,格式为：XX时XX分00
  V_DETQYSJ       XT_XTCS00.VALUE0%type;--第二天取药时间点：格式为：XX时XX分00
  V_ZRQYSYSJ      XT_XTCS00.VALUE0%type; --针对周日延至第二天的时间点,格式为：XX时XX分00
  V_ZYCFFYSFZJPYQR  XT_XTCS00.VALUE0%type; --住院处方发药是否直接住院中药煎药处方配方确认，Y是N否，默认为N
  V_YYRQ00      BQ_YPYZ00.YYRQ00%type;--首次服药日期 
  begin
    as_JYDH00:=0;
    select nvl(max(trim(value0)),'N') into V_SFQYDJGLMK from XT_XTCS00 where name00='YF_SFQYDJGLGN';
    if V_SFQYDJGLMK='Y' then
      begin
        select nvl(max(trim(value0)),'N') into V_ZYCFFYSFZJPYQR from XT_XTCS00 where name00='YF_ZYCFFYSFZJPYQR';
      exception
        when others then
          V_ZYCFFYSFZJPYQR := 'N';
      end;
if AS_ZYYZBZ='0' then   --‘0’住院零散处方
      begin
        select a.* into VYF_ZYCF00  from YF_ZYCF00 a where a.CFLSH0=as_CFLSH0;
      exception
        when others then
          as_YHMSG0:='单据不存在';
          as_SYSMSG:='select * into VYF_ZYCF00  from YF_ZYCF00 where CFLSH0='||as_CFLSH0;
          raise E_EXCEPTION;
      end;
      if (VYF_ZYCF00.YPDLBH='2') and (VYF_ZYCF00.ZYDJS0>0) then
        select count(*) into  vCOUNT from VW_BQ_BRZDXX a  where a.ZYID00=VYF_ZYCF00.ZYID00 and a.BRID00=VYF_ZYCF00.BRID00 and NVL(a.SFCRB0,'0')>'0';
        if vCount=0 then
          vSFCRB0:='0';
        else
        	vSFCRB0:='1';
        end if;
      end if;
elsif AS_ZYYZBZ='1' then    --'1' 医嘱处方
      begin
       select a.* into VYF_YZYPSQ from  YF_YZYPSQ a where a.TQDJH0=AS_TQDJH0 and a.YZID00=AS_YZID00 and rownum=1;
      exception
        when others then
          as_YHMSG0:='单据不存在';
          as_SYSMSG:='select * from  YF_YZYPSQ where TQDJH0='||AS_TQDJH0||' and YZID00='||AS_YZID00||' and rownum=1';
          raise E_EXCEPTION;
      end;

      select a.* into VZY_BRXXB0 from ZY_BRXXB0 a where a.ZYID00=VYF_YZYPSQ.ZYID00;
      if (VYF_YZYPSQ.YPDLBH='2') and (VYF_YZYPSQ.ZYDJS0>0) then
   --     select count(*) into  vCOUNT from BQ_BRZDXX a  where a.ZYID00=VYF_YZYPSQ.ZYID00 and SFCRB0>0;
        select count(*) into  vCOUNT from VW_BQ_BRZDXX a  where a.ZYID00=VZY_BRXXB0.ZYID00 and a.BRID00=VZY_BRXXB0.BRID00 and NVL(a.SFCRB0,'0')>'0';
        if vCount=0 then
          vSFCRB0:='0';
        else
        	vSFCRB0:='1';
        end if;
      end if;


end if;
   --------------------------------------------------------------------------------------------------------------------------------------
           select to_number(nvl(max(trim(value0)),'4')) into V_ZCQYSJ from XT_XTCS00 where name00='MZPY_ZCQYSJ';
        select nvl(max(trim(value0)),'16:00:00') into V_YCZDETQYSJ from XT_XTCS00 where name00='MZPY_YCZDETQYSJ';
        select nvl(max(trim(value0)),'10:30:00') into V_DETQYSJ from XT_XTCS00 where name00='MZPY_DETQYSJ';
        select nvl(max(trim(value0)),'14:30:00') into V_ZRQYSYSJ from XT_XTCS00 where name00='MZPY_ZRQYSYSJ';
    if  to_char(sysdate,'day')='星期日'and V_ZRQYSYSJ<>'' then
        if to_char(sysdate,'HH24:MI:SS')>=V_ZRQYSYSJ then
          vQYRQ00:=to_char(sysdate+1,'YYYYMMDD');
          vQYSJ00:=to_char(to_date(to_char(sysdate+1,'YYYYMMDD')||trim(V_DETQYSJ),'YYMMDDHH24:MI:SS')+(sysdate-to_date(to_char(sysdate,'YYYYMMDD')||V_ZRQYSYSJ ,'YYYYMMDDHH24:MI:SS')),'HH24:MI:SS') ;
        else
          vQYRQ00:=to_char(sysdate,'YYYYMMDD');
          vQYSJ00:=to_char(sysdate+V_ZCQYSJ/24,'HH24:MI:SS');
        end if;
     else
        if to_char(sysdate,'HH24:MI:SS')>=V_YCZDETQYSJ then
          vQYRQ00:=to_char(sysdate+1,'YYYYMMDD');
          vQYSJ00:=to_char(to_date(to_char(sysdate+1,'YYYYMMDD')||trim(V_DETQYSJ),'YYMMDDHH24:MI:SS')+(sysdate-to_date(to_char(sysdate,'YYYYMMDD')||V_YCZDETQYSJ ,'YYYYMMDDHH24:MI:SS')),'HH24:MI:SS') ;
        else
          vQYRQ00:=to_char(sysdate,'YYYYMMDD');
          vQYSJ00:=to_char(sysdate+V_ZCQYSJ/24,'HH24:MI:SS');
        end if;

    end if;
        --时间按半小时取整
        if substr(vQYSJ00,4,2)>='00' and substr(vQYSJ00,4,2)<='30' then
          vQYSJ00:=to_char(to_date(vQYRQ00||substr(vQYSJ00,1,2),'YYYYMMDDHH24')+1/48,'HH24:MI:SS');
        else
          vQYSJ00:=to_char(to_date(vQYRQ00||substr(vQYSJ00,1,2),'YYYYMMDDHH24')+1/24,'HH24:MI:SS');
        end if;

------------------------------------------------------------------------------------------------------------------------------------------

if AS_ZYYZBZ='0' then   --‘0’住院零散处方
       begin
        select YYRQ00 into V_YYRQ00 from BQ_YPYZ00 where YZID00=AS_YZID00;
       exception
        when NO_DATA_FOUND then
         V_YYRQ00 := '';
       end;
        insert into YF_ZYJYXX(JYDH00,JLLX00,TQDJH0,YZID00,CFLSH0,ZYH000,ZYID00,BRID00,BRXM00,XB0000,BQBMBH,DQKS00,SFCRB0,CSRQ00,PHRQ00,QYRQ00,QYSJ00,ZYDJS0,ZFXX00,YYRQ00)
        select SQ_YF_JYXXB0_JYDH00.nextval,AS_ZYYZBZ,AS_TQDJH0,AS_YZID00,AS_CFLSH0,VYF_ZYCF00.ZYH000,VYF_ZYCF00.ZYID00,VYF_ZYCF00.BRID00,VYF_ZYCF00.BRXM00,VYF_ZYCF00.XB0000,VYF_ZYCF00.DQBQ00,VYF_ZYCF00.DQKS00,
          vSFCRB0,VYF_ZYCF00.CSRQ00,to_char(sysdate,'YYYYMMDD'),vQYRQ00,vQYSJ00,VYF_ZYCF00.ZYDJS0,VYF_ZYCF00.BZ0000,V_YYRQ00 from dual where not exists (
          select 1 from YF_ZYJYXX b where b.CFLSH0=as_CFLSH0);
        if sql%notfound then
          as_JYDH00:=0;
        else
          begin
            select JYDH00 into as_JYDH00 from YF_ZYJYXX where  CFLSH0=as_CFLSH0;
          exception
          when others then
            as_YHMSG0:='单据不存在';
            as_SYSMSG:='select JYDH00 into as_JYDH00 from YF_ZYJYXX where  CFLSH0='||as_CFLSH0;
            raise E_EXCEPTION;
          end;
        end if;
        if V_ZYCFFYSFZJPYQR = 'Y' then
          update YF_ZYJYXX set PFRQ00=to_char(sysdate,'YYYYMMDD'),PFSJ00=to_char(sysdate,'HH24:MI:SS' ),PFCZY0=VYF_ZYCF00.FYR000
          where CFLSH0=as_CFLSH0 and LYRQ00='20991231';
        end if;
elsif AS_ZYYZBZ='1' then    --'1' 医嘱处方
			 begin
        select YYRQ00 into V_YYRQ00 from BQ_YPYZ00 where YZID00=AS_YZID00;
       exception
        when NO_DATA_FOUND then
         V_YYRQ00 := '';
       end;
        insert into YF_ZYJYXX(JYDH00,JLLX00,TQDJH0,YZID00,CFLSH0,ZYH000,ZYID00,BRID00,BRXM00,XB0000,BQBMBH,DQKS00,SFCRB0,CSRQ00,PHRQ00,QYRQ00,QYSJ00,ZYDJS0,ZFXX00,YYRQ00)
        select SQ_YF_JYXXB0_JYDH00.nextval,AS_ZYYZBZ,AS_TQDJH0,AS_YZID00,AS_CFLSH0,VYF_YZYPSQ.ZYH000,VYF_YZYPSQ.ZYID00,VZY_BRXXB0.BRID00,VYF_YZYPSQ.BRXM00,VZY_BRXXB0.XB0000,VYF_YZYPSQ.BQBMBH,VYF_YZYPSQ.DQKS00,
          vSFCRB0,VZY_BRXXB0.CSRQ00,to_char(sysdate,'YYYYMMDD'),vQYRQ00,vQYSJ00,VYF_YZYPSQ.ZYDJS0,VYF_YZYPSQ.YFSMMC,V_YYRQ00 from dual where not exists (
          select 1 from YF_ZYJYXX b where b.TQDJH0=AS_TQDJH0 and b.YZID00=AS_YZID00);
        if sql%notfound then
          as_JYDH00:=0;
        else
          begin
             select JYDH00 into as_JYDH00 from  YF_ZYJYXX where TQDJH0=AS_TQDJH0 and YZID00=AS_YZID00;
          exception
          when others then
            as_YHMSG0:='单据不存在';
            as_SYSMSG:='select JYDH00 into as_JYDH00 from  YF_ZYJYXX where TQDJH0='||AS_TQDJH0||' and YZID00='||AS_YZID00;
            raise E_EXCEPTION;
          end;
        end if;
        if V_ZYCFFYSFZJPYQR = 'Y' then
          update YF_ZYJYXX set PFRQ00=to_char(sysdate,'YYYYMMDD'),PFSJ00=to_char(sysdate,'HH24:MI:SS' ),PFCZY0=VYF_YZYPSQ.FYR000
          where TQDJH0=AS_TQDJH0 and YZID00=AS_YZID00 and LYRQ00='20991231';
        end if;

end if;

    end if;
    exception


        when E_EXCEPTION then
             RAISE_APPLICATION_ERROR(-20001,substrb(as_YHMSG0||'！*'||as_SYSMSG,1,150));
        when OTHERS then
             as_YHMSG0:='数据库错误：出错原因未知！';
             as_SYSMSG:=substr('执行存储过程错误:SP_PY_INSERTZYJYXX'||sqlerrm,1,150);
             RAISE_APPLICATION_ERROR(-20001,substrb(as_YHMSG0||'！*'||as_SYSMSG,1,150));
  end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 15.药房配药人工作量自动清零 SP_PY_YFPYRGZLQL 
create or replace procedure SP_PY_YFPYRGZLQL  --药房配药人工作量自动清零
(SFQL in char --是否清零
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2017.10.31  CREATE 配药人工作量清零 for YFPY-20171013-002
as
  VSYSDATE                  DATE;
begin
  if SFQL='1' then
    update SF_PYRYB0 set DPYSL0=0 where  YGXZ00='1';
    commit;
  end if;
exception
  when others then
      raise_application_error(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220));
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 获取处方审核结果
create or replace function SF_PY_GETHLYYTSXX(sCFLSH0 in varchar2    )                                                                                               
return varchar2 is
  ls_CFLSH0  YS_CFSHRZ.CFLSH0%type;
  ls_SHJGDM  YS_CFSHRZ.SHJGDM%type;
  ls_TSXX00  varchar(300);
  ls_ID0000  YS_CFSHRZ.ID0000%type;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm        2019.09.20   created  for  YFPY-20190919-002
begin  
  ls_CFLSH0:=sCFLSH0;
  select max(ID0000) into ls_ID0000 from YS_CFSHRZ a where   CFLSH0=ls_CFLSH0;
  for c_ts in(  
  select a.SHJGDM,a.SHJG00,(select YPMC00 from BM_YD0000 where YPNM00=a.YPNM00) YPMC00 from YS_CFSHRZ a where   CFLSH0=ls_CFLSH0  and ID0000=ls_ID0000
    and nvl(SHJGDM,'0')>'0'
    --and SHJGDM=ls_SHJGDM
  ) loop
    if ls_TSXX00=' ' then
       ls_TSXX00:=c_ts.YPMC00||' '||c_ts.SHJG00;
    else
       ls_TSXX00:=ls_TSXX00||c_ts.YPMC00||' '||c_ts.SHJG00;
    end if;      
    ls_SHJGDM:=c_ts.SHJGDM;
  end loop;
  if ls_SHJGDM='8' then
    ls_TSXX00:='2合理用药审查系统审查出存在重大问题,此处方不能保存！';
  elsif  ls_SHJGDM='7' then
    ls_TSXX00:='1如下要品需要双签:'||chr(13)||ls_TSXX00;
  elsif  ls_TSXX00<>' ' then
    --ls_TSXX00:='1'||ls_TSXX00||chr(13)||'请确认是否继续保存?';
    ls_TSXX00:='1'||ls_TSXX00||chr(13)||'';
  end if;   
  return ls_TSXX00;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%