-- Start of DDL Script for Procedure SD_HOSPITAL.SP_XT_TSXZ00
-- Generated 2013.05.09 16:14:04 from SD_HOSPITAL@ORCL

-- Drop the old instance of SP_XT_TSXZ00
DROP PROCEDURE sd_hospital.sp_xt_tsxz00
/

CREATE OR REPLACE 
PROCEDURE sd_hospital.sp_xt_tsxz00 (
--0为门诊，传入挂号ID字段，1为住院，传入住院ID字段。
 V_FLAG00 IN NUMBER DEFAULT 0,
 V_GHZYID IN NUMBER,
 V_RETURN OUT NUMBER,
 V_ERROR0 OUT VARCHAR2
)
AS
C_BRID00 NUMBER(10);
C_FBBH00 NUMBER(5);
C_ZDFBBRMYCFXZ NUMBER(5);
V_COUNT NUMBER(3);
C_YBZXLB CHAR(1);
C_YBBRLB CHAR(1);
C_BJSL00 NUMBER(12,2);
C_JYSL00 NUMBER(12,2);
C_SL0000 NUMBER(12,2);
V_COUNT0 NUMBER(5);     --计数器
C_RETURN NUMBER(5);     --本次限制是否跳过，1跳过，0检查
C_GHLB00 SF_BRXXB0.GHLB00%TYPE;
C_GHKS00 SF_BRXXB0.GHKS00%TYPE;
C_SFCZ00 SF_BRXXB0.SFCZ00%TYPE;
V_CFJE00 BM_YD0000.KCJE00%TYPE;
C_SFJE00 NUMBER(8,2);
C_MFHXZE NUMBER(8,2);
SFQYNBF0 CHAR(1);
C_XZYPNM VARCHAR2(200);--限制药品内码
V_BRZBYSFTS CHAR(1);--医生站开自备药是否进行提示 Y;是 N:否  默认为N
C_YPMC00 varchar2(150); --
C_TSBZCFJEXZ NUMBER(6,2); --特殊病种处方限制金额
V_SFLXBR CHAR(1);  --是否离休病人
C_TSBZYTCFZJEXZ number(6,2);--特殊病种一天处方总金额
C_XZBFYP VARCHAR2(100);  --处理龙岩医院部分药品要放在冰箱存放，限制医生部分药品只能开１次的用量 MZYS_XZBFYP 
LS_BZ0000    BM_TYZD00.BZ0000%type;
LS_BMMC00   BM_BMBM00.BMMC00%TYPE;
LS_COU       number(10);
LS_COU1       number(10);  
cursor  C_ZBYP IS   SELECT B.YPMC00 FROM YF_MZCF00 A, YF_MZCFMX B 
         WHERE A.CFLSH0=B.CFLSH0 AND A.GHID00=V_GHZYID and b.SFZBY0='Y';
BEGIN
--0表示成功，
--1表示违反警告，
--2表示违反限制。
--3表示其他错误。
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGY      2009.03.13   门诊病人,如果一个挂号中含有一个自备药，要进行提醒。
-- CSF        2009.04.05   门诊病人,某费别(参数控制)不允许开自费药品
-- CSF        2009.05.04   门诊病人,特殊病种病人单张处方金额限制
-- CSF        2009.05.06   门诊病人,用参数指定的费别，每个月只能开10次处方，一天的所有处方算1次，一天所有处方金额限制130
-- YANGY      2009.05.09   门诊大市离休病人，单张处方大于130是予以提示，一天处方超过260元予以提示  龙岩人民医院。
-- linzy      2009.07.03   限制医生部分药品只能一天开一只不能多开
-- Zhangwz    2010.04.23   门诊特殊病种病人一天处方总金额限制
-- dsm        2012.01.30   把触发器TR_YS_CFXXB0_BFUPD_INS的限制移到这个过程中 for MZYS-20120130-001
  V_RETURN:=0;
  V_ERROR0:='没有错误';
  IF V_FLAG00=0 THEN
    BEGIN
      SELECT BRID00 INTO C_BRID00 FROM SF_BRXXB0 WHERE GHID00=V_GHZYID;
    EXCEPTION
    WHEN OTHERS THEN
      V_RETURN:=3;
      V_ERROR0:='查无此人！';
      RETURN;
    END;
  ELSE
    BEGIN
      SELECT BRID00 INTO C_BRID00 FROM ZY_BRXXB0 WHERE ZYID00=V_GHZYID;
    EXCEPTION
    WHEN OTHERS THEN
      V_RETURN:=3;
      V_ERROR0:='查无此人！';
      RETURN;
    END;
  END IF;
  BEGIN
    SELECT A.FBBH00,B.YBZXLB,A.YBBRLB INTO C_FBBH00,C_YBZXLB,C_YBBRLB
       FROM BM_BRXXB0 A,IC_YBBRLB B
         WHERE BRID00=C_BRID00 AND A.FBBH00=B.FBBH00 AND A.YBLB00=B.YBLB00;
  EXCEPTION
  WHEN OTHERS THEN
    V_RETURN:=3;
    V_ERROR0:='查无此人！';
    RETURN;
  END;

--单个挂号所有处方总金额限制  0
--每张处方方总金额限制  1
  C_RETURN:=0;
  BEGIN
    SELECT BJSL00,JYSL00 INTO C_BJSL00,C_JYSL00 FROM BM_TSXZ00
      WHERE BH0000=0 AND (FBBH00=0 OR FBBH00=C_FBBH00)
            AND (YBZXLB='Z' OR YBZXLB=C_YBZXLB)
            AND (YBBRLB='Z' OR YBBRLB=C_YBBRLB) AND ROWNUM=1 ;
  EXCEPTION
  WHEN OTHERS THEN
    C_RETURN:=1;
--    V_ERROR0:='没有错误';  --继续下一个限制
  END;
  IF C_RETURN=0 THEN
    BEGIN
      SELECT SUM(CFZJE0) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
    EXCEPTION
    WHEN OTHERS THEN
      C_SL0000:=0;
    END;
    IF C_SL0000>C_JYSL00 THEN
      V_RETURN:=2;
      V_ERROR0:='所有处方总金额超过禁用数量：'||TRIM(TO_CHAR(C_JYSL00));
      RETURN;
    ELSE
      IF C_SL0000>C_BJSL00 THEN
        V_RETURN:=1;
        V_ERROR0:='所有处方总金额超过报警数量：'||TRIM(TO_CHAR(C_BJSL00));  --继续下一个限制
      END IF;
    END IF;
  END IF;
  --建阳市立医院要求特殊处理，离休，512人员参加保险，但没有医保中心并新增医保病人类别，
  --其特征是bm_brxxb0的TYDWMC='建阳市离休、5.12、二乙人员'，但要求对这类病人进行处方限制
  select count(*) into V_COUNT0 from xt_yyxx00 where YYJC00 ='建阳市立医院' ;
  IF V_COUNT0>0 then
     SELECT count(*) into V_COUNT0 FROM BM_BRXXB0 WHERE BRID00=C_BRID00 AND TYDWMC='建阳市离休、5.12、二乙人员';
     IF V_COUNT0>0 THEN
        C_SL0000:=0;
        SELECT SUM(CFZJE0) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
        IF C_SL0000>80 THEN
           V_RETURN:=2;
           V_ERROR0:='所有处方总金额超过禁用数量：'||TRIM(TO_CHAR(C_JYSL00));
           RETURN;
        END IF;
     END IF;
  END IF;
  
  --老年医院省医保控制110元
  select count(*) into V_COUNT0 from xt_yyxx00 where YYJC00 like '%老年医院%' ;
  IF V_COUNT0>0 then
     IF C_YBZXLB='1' THEN
        C_SL0000:=0;
        SELECT SUM(CFZJE0) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
        IF C_SL0000>110 THEN
           V_RETURN:=2;
           V_ERROR0:='所有处方总金额超过省医保的最大数量:110';
           RETURN;
        END IF;
     END IF;
  END IF;
  
  select count(*) into V_COUNT0 from xt_yyxx00 where YYJC00 like '%邵武市人民医院%';
  IF V_COUNT0>0 then
     SELECT count(*) into V_COUNT0 FROM BM_BRXXB0 WHERE BRID00=C_BRID00 AND TYDWMC='中共邵武市委老干部局(离休)';
     IF V_COUNT0>0 THEN
        C_SL0000:=0;
        SELECT count(*) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
        IF C_SL0000>1 THEN
           V_RETURN:=2;
           V_ERROR0:='一个挂号只能开一张处方';
           RETURN;
        END IF;
        SELECT SUM(CFZJE0) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
        IF C_SL0000>80 THEN
           V_RETURN:=2;
           V_ERROR0:='所有处方总金额超过禁用数量：'||TRIM(TO_CHAR(C_JYSL00));
           RETURN;
        END IF;
     END IF;
  END IF;
  
  select count(*) into V_COUNT0 from xt_yyxx00 where YYJC00 like '%平潭县医院%';
  IF (V_COUNT0>0) and (V_FLAG00=0) then
     C_SL0000:=0;
     SELECT SUM(CFZJE0) INTO C_SL0000 FROM YF_MZCF00 WHERE GHID00=V_GHZYID AND CFZT00<>'3';
     SELECT GHLB00 into C_GHLB00 FROM SF_BRXXB0 WHERE GHID00=V_GHZYID;
     SELECT GHKS00 into C_GHKS00 FROM SF_BRXXB0 WHERE GHID00=V_GHZYID;
     SELECT SFCZ00 into C_SFCZ00 FROM SF_BRXXB0 WHERE GHID00=V_GHZYID;
     IF C_GHLB00 IS NOT NULL THEN
          select COUNT(1) INTO V_COUNT0
          from   sf_fjghf0 a,vw_bm_ybsfdy b 
          where  a.FJFFBH=b.SFXMID and (fjghks=C_GHKS00 or nvl(fjghks,0)=0)and fjghlb=C_GHLB00 and fjbrlb=C_SFCZ00 and
          b.sfyp00='N' and b.ybzxlb=C_YBZXLB and b.ybbrlb=C_YBBRLB and b.fbbh00=C_FBBH00;
          
          IF V_COUNT0=0 THEN
            C_SFJE00:=0;
          ELSE
            select SUM(round(nvl(xmje00,0),2)) INTO C_SFJE00
            from   sf_fjghf0 a,vw_bm_ybsfdy b 
            where  a.FJFFBH=b.SFXMID and (fjghks=C_GHKS00 or nvl(fjghks,0)=0)and fjghlb=C_GHLB00 and fjbrlb=C_SFCZ00 and
            b.sfyp00='N' and b.ybzxlb=C_YBZXLB and b.ybbrlb=C_YBBRLB and b.fbbh00=C_FBBH00;
          END IF;
       --Select nvl(SUM(SFJE00),0) INTO C_SFJE00 from  VW_SF_ghlb00 where  lbbh00=C_GHLB00 and ghks00=-1 and ROWNUM=1;
       IF C_SFJE00=0 THEN
         begin
           select TO_NUMBER(VALUE0) into C_MFHXZE  from XT_XTCS00 where NAME00='YS_MFGHXZCFJE';  
         exception
         when others then
           C_MFHXZE:=80;
         end; 
         IF C_SL0000>C_MFHXZE THEN
           V_RETURN:=2;
           V_ERROR0:='所有处方总金额超过限制金额：'||TRIM(TO_CHAR(C_MFHXZE)); 
           RETURN;          
         END IF;
       END IF;
     END IF;
  END IF;  
  
  IF V_FLAG00=0 THEN    
    SELECT COUNT(*) INTO V_COUNT0 FROM XT_YYXX00 WHERE YYJC00 LIKE '%漳浦县中医院%';
    IF V_COUNT0>0 then
       SELECT count(*) into V_COUNT0 FROM SF_BRXXB0 WHERE GHID00=V_GHZYID AND GHJE00=0;
       IF V_COUNT0>0 THEN          
         V_RETURN:=2;
         V_ERROR0:='此次挂号为免费挂号，不允许开处方！';
         RETURN;
       END IF;
    END IF;
  END IF;
  
  IF V_FLAG00=0 THEN  
    select nvl(max(trim(value0)),'N') into V_BRZBYSFTS from xt_xtcs00 where name00='YS_BRZBYSFTS';  
    IF V_BRZBYSFTS ='Y' then
       SELECT count(1) into V_COUNT0 FROM YF_MZCF00 A, YF_MZCFMX B 
         WHERE A.CFLSH0=B.CFLSH0 AND A.GHID00=V_GHZYID and b.SFZBY0='Y';
       IF V_COUNT0 > 0 THEN 
         C_YPMC00 := '';    
         for rec in C_ZBYP loop
           C_YPMC00 := C_YPMC00||'; '||rec.YPMC00;
         end loop;   
         C_YPMC00 := substrb(C_YPMC00, 3, lengthb(C_YPMC00)-1);  
         V_RETURN:=1;
         V_ERROR0:='友情提示： 本次挂号包含有自备药:"'||C_YPMC00||'"，自备药无法在药房发药！';
         RETURN;
       END IF;
    END IF;
  END IF;  
  --龙岩人民医院fbbh00=6的离休病人，不允许开自费药品
  IF V_FLAG00=0 THEN
    select to_number(nvl(max(trim(value0)),'-9')) into C_FBBH00 from xt_xtcs00 where name00='YS_GFBBRBYXKZFY';
    IF C_FBBH00>-9 THEN
      C_YPMC00:=' ';
      FOR YP IN (SELECT C.YPMC00 FROM SF_BRXXB0 A,YF_MZCF00 B,YF_MZCFMX C WHERE A.GHID00=V_GHZYID and
        A.FBBH00=C_FBBH00 and B.GHID00=V_GHZYID and B.CFLSH0=C.CFLSH0 and b.CFZT00='0' and EXISTS  (
          select 1 from BM_YBSFDY WHERE  SFYP00='Y' and FBBH00=C_FBBH00 and  ZFBL00=1 and SFXMID=C.YPNM00)) LOOP
          C_YPMC00:=C_YPMC00||';'||YP.YPMC00;
      END LOOP;
      IF C_YPMC00<>' ' THEN
         V_RETURN:=2;
         V_ERROR0:=C_YPMC00||'为自费药品,请修改！';
      END IF;
    END IF;
  END IF;
   --龙岩人民医院fbbh00=6的离休病人，不允许开自费药品
  IF V_FLAG00=0 THEN
    select to_number(nvl(max(trim(value0)),'0')) into C_TSBZCFJEXZ from xt_xtcs00 where name00='YS_TSBZCFJEXZ';
    IF C_TSBZCFJEXZ>0 THEN
      C_YPMC00:='';
      FOR TSBZCF IN (SELECT CFLSH0 FROM (SELECT B.CFLSH0,ROUND(SUM(C.YPZSL0*C.LSDJ00),2) JE0000 FROM YF_MZCF00 B,YF_MZCFMX C WHERE  B.GHID00=V_GHZYID 
          and B.CFLSH0=C.CFLSH0 and b.CFZT00='0' AND B.TSBZBH IS NOT NULL GROUP BY B.CFLSH0) WHERE JE0000>C_TSBZCFJEXZ) LOOP
          IF C_YPMC00='' THEN
            C_YPMC00:=TSBZCF.CFLSH0;
          ELSE
            C_YPMC00:=C_YPMC00||';'||TSBZCF.CFLSH0;
          END IF;
      END LOOP;
      IF C_YPMC00<>' ' THEN
         V_RETURN:=2;
         V_ERROR0:='特殊病种处方'||TRIM(C_YPMC00)||'超过'||trim(to_char(C_TSBZCFJEXZ))||'元,不能保存,请修改！';
      END IF;   
    END IF;
  END IF;
    
  IF V_FLAG00=0 THEN
      SELECT COUNT(*) INTO V_COUNT FROM       
      (select distinct B.SRRQ00 FROM SF_BRXXB0 A,YF_MZCF00 B WHERE A.BRID00=C_BRID00 and
         A.FBBH00=C_ZDFBBRMYCFXZ and A.GHID00=B.GHID00 AND B.CFZT00 IN ('0','1','2') and
       B.SRRQ00>=TO_CHAR(SYSDATE,'YYYYMM')||'01' AND B.SRRQ00<=TO_CHAR(last_day(sysdate),'YYYYMMDD'));
      IF V_COUNT>10 THEN
         V_RETURN:=2;
         V_ERROR0:='该病人本月已经开了10次处方,不能再增加处方了！';
      END IF;
      
      IF V_COUNT>0 THEN
      
         
        C_YPMC00:=' ';
       -- FOR YP IN (select SRRQ00 FROM (SELECT B.SRRQ00,ROUND(SUM(C.YPZSL0*LSDJ00),2) CFJE00 FROM SF_BRXXB0 A,YF_MZCF00 B,YF_MZCFMX C
         --  WHERE A.GHID00=V_GHZYID and
          -- A.FBBH00=C_ZDFBBRMYCFXZ and B.GHID00=V_GHZYID and B.CFLSH0=C.CFLSH0 and b.CFZT00='0'
           --GROUP BY B.SRRQ00) WHERE CFJE00>130 ) LOOP
          SELECT NVL(ROUND(SUM(C.YPZSL0*LSDJ00),2),0) CFJE00 INTO V_CFJE00 FROM SF_BRXXB0 A,YF_MZCF00 B,YF_MZCFMX C 
          WHERE A.BRID00=C_BRID00 and  B.CFZT00 IN ('0','1','2') and
          A.FBBH00=C_ZDFBBRMYCFXZ AND A.GHID00=B.GHID00 AND B.CFLSH0=C.CFLSH0 AND B.SRRQ00=TO_CHAR(SYSDATE,'YYYYMMDD');
          
          
       -- END LOOP;
        IF V_CFJE00>200 THEN
           V_RETURN:=2;
           V_ERROR0:='病人今天的处方金额超过200,请修改！';
        END IF;
      END IF;
  END IF;  
  
  IF V_FLAG00=0 THEN
    select to_number(nvl(max(trim(value0)),'-9')) into C_ZDFBBRMYCFXZ from xt_xtcs00 where name00='YS_ZDFBBRMYCFXZ';
    IF C_ZDFBBRMYCFXZ>-9 THEN
      SELECT COUNT(*) INTO V_COUNT FROM       
      (select distinct B.SRRQ00 FROM SF_BRXXB0 A,YF_MZCF00 B WHERE A.BRID00=C_BRID00 and
         A.FBBH00=C_ZDFBBRMYCFXZ and A.GHID00=B.GHID00 AND B.CFZT00 IN ('0','1','2') and
       B.SRRQ00>=TO_CHAR(SYSDATE,'YYYYMM')||'01' AND B.SRRQ00<=TO_CHAR(last_day(sysdate),'YYYYMMDD'));
      IF V_COUNT>10 THEN
         V_RETURN:=2;
         V_ERROR0:='该病人本月已经开了10次处方,不能再增加处方了！';
      END IF;
      
      IF V_COUNT>0 THEN
      
         
        C_YPMC00:=' ';
       -- FOR YP IN (select SRRQ00 FROM (SELECT B.SRRQ00,ROUND(SUM(C.YPZSL0*LSDJ00),2) CFJE00 FROM SF_BRXXB0 A,YF_MZCF00 B,YF_MZCFMX C
         --  WHERE A.GHID00=V_GHZYID and
          -- A.FBBH00=C_ZDFBBRMYCFXZ and B.GHID00=V_GHZYID and B.CFLSH0=C.CFLSH0 and b.CFZT00='0'
           --GROUP BY B.SRRQ00) WHERE CFJE00>130 ) LOOP
          SELECT NVL(ROUND(SUM(C.YPZSL0*LSDJ00),2),0) CFJE00 INTO V_CFJE00 FROM SF_BRXXB0 A,YF_MZCF00 B,YF_MZCFMX C 
          WHERE A.BRID00=C_BRID00 and  B.CFZT00 IN ('0','1','2') and
          A.FBBH00=C_ZDFBBRMYCFXZ AND A.GHID00=B.GHID00 AND B.CFLSH0=C.CFLSH0 AND B.SRRQ00=TO_CHAR(SYSDATE,'YYYYMMDD');
          
          
       -- END LOOP;
        IF V_CFJE00>200 THEN
           V_RETURN:=2;
           V_ERROR0:='病人今天的处方金额超过200,请修改！';
        END IF;
      END IF;
    END IF;
  END IF;
  
  
  select count(*) into V_COUNT0 from xt_yyxx00 where YYJC00 like '%龙岩人民医院%';
  IF (V_COUNT0>0) and (V_FLAG00=0) THEN
      V_SFLXBR := SF_SF_LXBR00(C_BRID00);   --离休病人
      IF V_SFLXBR = '1' THEN
            SELECT NVL(ROUND(SUM(C.YPZSL0*LSDJ00),2),0) CFJE00 INTO V_CFJE00 FROM YF_MZCF00 B,YF_MZCFMX C 
            WHERE B.CFLSH0=C.CFLSH0 AND B.BRID00=C_BRID00 and  B.CFZT00 IN ('0','1','2') and
              B.SRRQ00=TO_CHAR(SYSDATE,'YYYYMMDD');
              
            IF V_CFJE00>260 THEN
               V_RETURN:=2;
               V_ERROR0:='病人今天的处方金额超过260元!';
               RETURN;
            END IF;
            
            select max(cfje00) into V_CFJE00 from (SELECT NVL(ROUND(SUM(C.YPZSL0*LSDJ00),2),0) CFJE00 
                  FROM YF_MZCF00 B,YF_MZCFMX C 
                  WHERE B.CFLSH0=C.CFLSH0 AND B.CFZT00 IN ('0','1','2') and B.GHID00=V_GHZYID group by B.CFLSH0);
              
            IF V_CFJE00>200 THEN
               V_RETURN:=1;
               V_ERROR0:='病人单张处方金额超过200元!';
               RETURN;
            END IF;         
            
             
      END IF;
  END IF;   
  --龙岩医院部分药品要放在冰箱存放，限制医生部分药品只能开１次的用量  linzy 09-07-02 
  IF V_FLAG00=0 THEN
     select nvl(max(trim(VALUE0)),'0') into C_Xzypnm from XT_XTCS00 where NAME00='MZYS_XZBFYP';
     if C_Xzypnm<>'0' then
       declare cursor I_INSERT is select YPMC00
          from YS_CFMX00 a where  a.CFLSH0 in (select CFLSH0 from YS_CFXXB0 where GHID00=V_GHZYID) and 
               instr(','||C_Xzypnm||',',','||to_char(A.YPNM00)||',')>0  and ceil(a.CSL000*a.F2J000/a.ZHL000)<a.YPZSL0;
       BEGIN
         C_XZBFYP:=' ';
         for YP in I_INSERT loop 
             C_XZBFYP:=C_XZBFYP||'"'||YP.YPMC00||'",';
         end loop; 
         if C_XZBFYP<>' ' then
            V_RETURN:=2;
            V_ERROR0:=V_ERROR0||'药品'||C_XZBFYP||'只能开１次的用量，不能多开！';
            return; 
         end if ; 
       END;     
     END IF;  
  END IF; 

  IF V_FLAG00=0 THEN
    SELECT COUNT(*) INTO V_COUNT0 FROM bm_brxxb0 A,ic_ybbrlb B,bm_tsbzb0 C WHERE A.BRID00=C_BRID00 AND A.FBBH00=3
           AND B.YBLB00=A.YBLB00 AND B.FBBH00=A.FBBH00 AND C.ybzxlb=B.ybzxlb;
    select to_number(nvl(max(trim(value0)),'0')) into C_TSBZYTCFZJEXZ from xt_xtcs00 where name00='YS_TSBZYTCFZJEXZ';        
    IF (C_TSBZYTCFZJEXZ>0) AND (V_COUNT0>0) THEN
        SELECT ROUND(SUM(C.YPZSL0*C.LSDJ00),2)-C_TSBZYTCFZJEXZ INTO V_CFJE00 FROM YF_MZCF00 B,YF_MZCFMX C WHERE  B.BRID00=C_BRID00 
           and b.CFZT00<='2' and B.SRRQ00=to_char(sysdate,'YYYYMMDD') and B.CFLSH0=C.CFLSH0 AND B.TSBZBH IS NOT NULL  ;
        IF V_CFJE00>0 THEN
         V_RETURN:=2;
         IF V_ERROR0='没有错误' THEN
            V_ERROR0:='特殊病种处方单天总金额超过系统定义的'||trim(to_char(C_TSBZYTCFZJEXZ))||'元;超过'||trim(to_char(V_CFJE00))||'元,不能保存,请修改！';          
         ELSE   
           V_ERROR0:=V_ERROR0||'特殊病种处方单天总金额超过系统定义的'||trim(to_char(C_TSBZYTCFZJEXZ))||'元;超过'||trim(to_char(V_CFJE00))||'元,不能保存,请修改！'; 
         END IF;
        END IF;
    END IF;     
  END IF;
  IF V_FLAG00=0 THEN --南平儿科药房排班对处方保存的限制从TR_YS_CFXXB0_BFUPD_INS移到这个过程
      select COUNT(*)  into ls_cou from BM_TYZD00 a where ZDMC00='药房处方时间段限制' 
                                     and exists(select 1 from YF_MZCF00 where GHID00=V_GHZYID and YFBMBH=a.XSXH00 and CFZT00='0' );      
      if LS_COU>0 then 
        LS_BZ0000:='a';
        begin
        	select 'a'||nvl(BZ0000,'a')  into LS_BZ0000 from (
          select * from BM_TYZD00 a where ZDMC00='药房处方时间段限制'   
                         and  exists(select 1 from YF_MZCF00 where GHID00=V_GHZYID and YFBMBH=a.XSXH00 and CFZT00='0' ) )
                                                    where  TO_NUMBER(TO_CHAR(SYSDATE, 'D'))-1=TO_NUMBER(BH0000) and rownum=1;
        exception
          when others then
           LS_BZ0000:='a';
        end;  
        LS_COU1:=1;
        if LS_BZ0000<>'a' then
          select COUNT(*) into LS_COU1 from (
          select * from BM_TYZD00 a where ZDMC00='药房处方时间段限制' and  exists(select 1 from YF_MZCF00 where GHID00=V_GHZYID and YFBMBH=a.XSXH00 and CFZT00='0' ))
                                where ( (SUBSTR(TO_CHAR(SYSDATE,'HH24:MI:SS'),1,5) between SUBSTR(BZ0000,1,5) and SUBSTR(BZ0000,7,11))
                                or  (SUBSTR(TO_CHAR(SYSDATE,'HH24:MI:SS'),1,5) between SUBSTR(BZ0000,13,5) and SUBSTR(BZ0000,19,23))
                                   ) and  TO_NUMBER(TO_CHAR(SYSDATE, 'D'))-1=TO_NUMBER(BH0000);
        end if;  
        if LS_BZ0000='a' or LS_COU1=0 then
            select BMMC00 into LS_BMMC00 from BM_BMBM00 a where  exists(select 1 from YF_MZCF00 where GHID00=V_GHZYID and YFBMBH=a.BMBH00 and CFZT00='0' ) 
            and exists (select 1 from BM_TYZD00 where ZDMC00='药房处方时间段限制' and XSXH00=a.BMBH00)
            and rownum=1; 
            if LS_BZ0000='a' then 
               LS_BZ0000:='药房没有排班,请改药房'; 
               V_ERROR0:='此时药品不能发往'||LS_BMMC00||','||LS_BZ0000;
            else
              LS_BZ0000:=replace(LS_BZ0000,'a',' 上午:');
              LS_BZ0000:=replace(LS_BZ0000,';',' 下午:'); 
              V_ERROR0:='此时药品不能发往'||LS_BMMC00||',药房的处方保存时间是：'||LS_BZ0000;               
            end if;
            V_RETURN:=2;
        end if;         
      end if;      
   end if;   
END;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_XT_TSXZ00