-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YJ_CXCX00
-- Generated 23-八月-2013 9:12:52 from SD_HOSPITAL@DXCTEST1

CREATE OR REPLACE 
PROCEDURE sp_yj_cxcx00
(
	PYJDJH0		NUMBER,		--医技单据号
	PCZY000		NUMBER,		--员工编号
	PCZYKS0		NUMBER,		--科室编号
        Pcommit         varchar2 default 'Y' --是否提交
)
AS
----有同组：
------ for 冲销同组的所有撤消单据
--        select * from YJ_YW0000 Y where Y.CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJFZH0=VYJFZH0)
--          AND ((SFDJH0 IS NOT NULL) AND (SFDJH0<>0))
--           有SFDJH0，不撤消
------ END
--     for 冲销同组的所有撤消单据
--        删除对应的定价表，YJ——YWMX00，YJ——YW0000
------ END
--     FOR 所有同组被冲销单据
--        改YJ_YWJJ00表的冲销标志
--     END

----没有同组：
------ for 冲销的所有撤消单据
--           有SFDJH0，不撤消
------ END
--     for 冲销的所有撤消单据
--        删除对应的定价表，YJ——YWMX00，YJ——YW0000
------ END
--     FOR 所有被冲销单据
--        改YJ_YWJJ00表的冲销标志
--     END
  LS_CX0000 VARCHAR2(50);
  VYJDJH0   YJ_YW0000.YJDJH0%TYPE;
  Verrmsg  VARCHAR2(100);
  VYJFZH0 YJ_YW0000.YJFZH0%TYPE;
  LS_XMZT00 YJ_YW0000.XMZT00%TYPE;
  RYW0000   YJ_YW0000%ROWTYPE;
  Ecustom EXCEPTION;
  Vcounter NUMBER;
  LS_ZYHGHH YJ_YW0000.ZYHGHH%TYPE;
BEGIN
  Verrmsg:='';
  LS_CX0000:='SP_YJ_CXCX00('||PYJDJH0||','||PCZY000||','||PCZYKS0||')';
  select count(*) INTO Vcounter from YJ_YW0000 where YJDJH0=PYJDJH0;
  IF Vcounter<>1 THEN
    Verrmsg:='单据号为'||TO_CHAR(PYJDJH0)||'的医技单不存在';
    RAISE Ecustom;
  END IF;
  VYJDJH0:=PYJDJH0;
  select * INTO RYW0000 from YJ_YW0000 where YJDJH0=VYJDJH0;
  LS_XMZT00:=RYW0000.XMZT00;LS_ZYHGHH:=RYW0000.ZYHGHH;
  IF LS_XMZT00='6' THEN     --050720QKS 撤消已作废的项目
     UPDATE YJ_YW0000 SET XMZT00='1' where YJDJH0=VYJDJH0;
  ELSE
  IF (LS_XMZT00='0' ) OR (LS_XMZT00='1') THEN
    IF (nvl(RYW0000.CXDJH0,0)>0) AND (RYW0000.SL0000<0) THEN--冲销记录,找被冲纪录
      select count(*) INTO Vcounter from YJ_YW0000 where YJDJH0=RYW0000.CXDJH0;
      IF Vcounter<>1 THEN
        Verrmsg:='找不到单据号为'||TO_CHAR(RYW0000.CXDJH0)||'的被冲原始医技单';
        RAISE Ecustom;
      ELSE
        select * INTO RYW0000 from YJ_YW0000 where YJDJH0=RYW0000.CXDJH0;
        LS_XMZT00:=RYW0000.XMZT00;LS_ZYHGHH:=RYW0000.ZYHGHH;
        VYJDJH0:=RYW0000.YJDJH0;
        IF (LS_XMZT00='0' ) OR (LS_XMZT00='1') THEN
          Verrmsg:='单据号为'||TO_CHAR(VYJDJH0)||'的被冲原始医技单未收费，不允许撤消';
          RAISE Ecustom;
        END IF;
      END IF;
    ELSE
      Verrmsg:='单据号为'||TO_CHAR(VYJDJH0)||'的医技单未收费，不允许撤消';
      RAISE Ecustom;
    END IF;
  END IF;
  select count(*) INTO Vcounter from YJ_YW0000 where CXDJH0=VYJDJH0;
  IF Vcounter <1 THEN
    Verrmsg:='该项目没有被冲销,不能撤消';
    RAISE Ecustom;
  END IF;
  select YJFZH0 INTO VYJFZH0 from YJ_YW0000 where YJDJH0=VYJDJH0;
  IF VYJFZH0>0 THEN--有同组
    select count(*) into Vcounter from YJ_YW0000 Y
       where Y.CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJFZH0=VYJFZH0)
       AND  (SFDJH0<>0);
    IF Vcounter >0 THEN
      Verrmsg:='收费处已经更正,不能撤消';
      RAISE Ecustom;
    END IF;
    delete from yj_ywzxjl where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where ZYHGHH=LS_ZYHGHH AND CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where  W.YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH));
    delete from YJ_YWJJ00 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where ZYHGHH=LS_ZYHGHH AND CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where  W.YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH));
    delete from YJ_YWMX00 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where ZYHGHH=LS_ZYHGHH AND CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH));
    delete from YJ_YW0000 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where ZYHGHH=LS_ZYHGHH AND CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH));
    UPDATE YJ_YWJJ00 SET CXBZ00='Z'
      where YJDJH0 IN(select W.YJDJH0 from YJ_YW0000 W where W.YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH);
    UPDATE yj_yw0000 set cxsl00='0' where YJFZH0=VYJFZH0 AND ZYHGHH=LS_ZYHGHH;
  ELSE--没有同组
    select count(*) into Vcounter from YJ_YW0000 Y
       where Y.CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=VYJDJH0)
       AND  (SFDJH0<>0);
    IF Vcounter >0 THEN
      Verrmsg:='收费处已经更正,不能撤消';
      RAISE Ecustom;
    END IF;
    IF RYW0000.DCBZ00='2' AND RYW0000.YZCFID>'0' THEN--判定相关的技术药处方是否退费，如果退费，则不能撤销申请
      IF RYW0000.MZZYBZ='0' THEN
        select count(*) INTO Vcounter from YF_MZCF00 where CFLSH0=RYW0000.YZCFID AND SFKSLY='Y' AND LYKS00=RYW0000.YJKSBH;
        IF Vcounter>0 THEN
          select count(*) INTO Vcounter from YF_MZCFMX where CFLSH0=RYW0000.YZCFID AND CXBZ00='+';
          IF Vcounter>0 THEN
            Verrmsg:='相关的基数药处方已经退费,项目不能撤消申请';
            RAISE Ecustom;
          END IF;
        END IF;
      ELSE
        select count(*) INTO Vcounter from YF_ZYCF00 where CFLSH0=RYW0000.YZCFID AND SFKSLY='Y' AND LYKS00=RYW0000.YJKSBH;
        IF Vcounter>0 THEN
          select count(*) INTO Vcounter from YF_ZYCFMX where CFLSH0=RYW0000.YZCFID AND CXBZ00='+';
          IF Vcounter>0 THEN
            Verrmsg:='相关的基数药处方已经退费,项目不能撤消申请';
            RAISE Ecustom;
          END IF;
        END IF;
      END IF;
    END IF;
    delete from yj_ywzxjl where yjdjh0 in
    (select YJDJH0 from YJ_YW0000 Y
        where CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=VYJDJH0));
    delete from YJ_YWJJ00 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=VYJDJH0));
    delete from YJ_YWMX00 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=VYJDJH0));
    delete from YJ_YW0000 where YJDJH0 IN
      (select YJDJH0 from YJ_YW0000 Y
        where CXDJH0 IN (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=VYJDJH0));
    UPDATE YJ_YWJJ00 SET CXBZ00='Z' where YJDJH0=VYJDJH0;
    UPDATE YJ_YW0000 SET cxsl00='0' where YJDJH0=VYJDJH0;
  END IF;
  END IF;
  --撤销冲消时要求去掉"(取消)"字样
  if RYW0000.mzzybz='1' and RYW0000.dcbz00='0' and RYW0000.yzcfid is not null then--
     update bq_yjyz00 set zlxmjc=substrb(zlxmjc,instr(zlxmjc,'(取消)')+6,lengthb(zlxmjc))
            where yzmxid=to_number(RYW0000.yzcfid) and clbz00='1' and instrb(zlxmjc,'(取消)')>0;
  end if;

  if nvl(pcommit,'Y')='Y' then
    commit;
  end if;
EXCEPTION
    WHEN Ecustom THEN
      RAISE_APPLICATION_ERROR(-20011,Verrmsg||'*'||LS_CX0000);
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, '数据没有找到*'||Verrmsg||LS_CX0000);
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'||Verrmsg||LS_CX0000));
    ROLLBACK;
END SP_YJ_CXCX00;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_YJ_CXCX00