-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SF_YJGZ00_YJD000
-- Generated 2012-3-29 10:53:04 from SD_HOSPITAL@PTHJYY

CREATE OR REPLACE 
PROCEDURE sp_sf_yjgz00_yjd000
(
        ad_CXDJH0    IN    NUMBER,            --被冲销的医技单据号
        ad_CZY000    IN    NUMBER,            --操作员
        as_CZYXM0    IN    CHAR,              --操作员姓名
        ad_CZYKS0    IN    NUMBER,            --操作员所在科室名称
        as_SHRXM0    IN    CHAR,              --审核人
        as_SHRYY0    IN    CHAR,              --审核原因
        as_YHMSG0    OUT   VARCHAR,           --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR            --系统提示的错误信息
)
as
       ls_DJH000       SF_BRFY00.DJH000%TYPE;
       ls_JZDH00       SF_BRFY00.JZDH00%TYPE;
       ls_GHH000       YJ_YW0000.ZYHGHH%TYPE;
       ls_GHID00       YJ_YW0000.ZYGHID%TYPE;
       ls_BRID00       YJ_YW0000.BRID00%TYPE;
       ls_XM0000       YJ_YW0000.BRXM00%TYPE;
       ls_XMZT00       YJ_YW0000.XMZT00%TYPE;
       ls_SFDJH0       YJ_YW0000.YJDJH0%TYPE;
       ls_YJKSBH       YJ_YW0000.YJKSBH%TYPE; --医技科室,执行科室
       ls_KDYS00       YJ_YW0000.KDYS00%TYPE; --开单医生编号
       ls_KDKSBH       YJ_YW0000.KDKSBH%TYPE; --开单科室编号
       ls_YSSZKS       YJ_YW0000.YSSZKS%TYPE; --医生所在科室
       ls_FBBH00       SF_BRXXB0.FBBH00%TYPE;
       ls_LBBH00       SF_BRXXB0.LBBH00%TYPE;
       ls_ZXR000       YJ_YW0000.ZXR000%TYPE;
       ls_CZRQ00       CHAR(8);
       ls_CZSJ00       CHAR(8);
       ls_COUNT0       NUMBER(5);
       ls_FSJMJE       CHAR(1);               --是否向医保发送减免金额
BEGIN
    ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
    ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
    --是否向医保发送减免金额
    BEGIN
        select upper(trim(VALUE0))  into ls_FSJMJE   from  XT_XTCS00  where  NAME00 = 'SF_SFXYBFSJMJE';  
    EXCEPTION
       WHEN OTHERS THEN
         ls_FSJMJE:='Y';
    END; 
    select     SFDJH0,XMZT00,YJKSBH,KDYS00,KDKSBH,YSSZKS,ZYHGHH,ZYGHID,BRID00,BRXM00, ZXR000
         into  ls_SFDJH0,ls_XMZT00,ls_YJKSBH,ls_KDYS00,ls_KDKSBH,ls_YSSZKS,ls_GHH000,ls_GHID00,ls_BRID00,ls_XM0000,ls_ZXR000
         from  YJ_YW0000  where  YJDJH0 = ad_CXDJH0;
    if  ls_SFDJH0 = 0  then
                as_YHMSG0:='病人被冲的医技单据尚未记账,请先记账后再进行本操作!';
                as_SYSMSG:=SQLERRM;
                ROLLBACK;
                RETURN;
    end if;
    --先加个判断,若病人被冲的单据号已结算过,暂时不做处理!
    select  JZDH00 into  ls_JZDH00  from  SF_BRFY00  where  DJH000 = ls_SFDJH0;
    if  ls_JZDH00 <> 0  then
                as_YHMSG0:='病人被冲的处方号已结算,本过程尚未处理!';
                as_SYSMSG:=SQLERRM;
                ROLLBACK;
                RETURN;
    end if;

    BEGIN
    select  FBBH00,LBBH00  into  ls_FBBH00,ls_LBBH00  from  SF_BRXXB0 where GHID00 = ls_GHID00;
    EXCEPTION
      WHEN  NO_DATA_FOUND  THEN
            ls_FBBH00 := 1;   --当作自费处理
            ls_LBBH00 := 1;   --门诊预交金
    END;
    if  ls_LBBH00 = 0  then
        as_YHMSG0:='该医技单对应的挂号信息是现金的信息,系统更正失败!';
        as_SYSMSG:=SQLERRM;
                ROLLBACK;
        RETURN;
    end if;
    select  SQ_SF_BRJFB0_DJH000.nextval  into  ls_DJH000  from   dual;
    --修改医记表中的相关信息
    UPDATE YJ_YW0000  SET  XMZT00 = DECODE(sign(SL0000),-1,'3','2'),          --已执行,区别与处方
                           SFDJH0 = ls_DJH000
                     WHERE YJDJH0 in ( select YJDJH0 from YJ_YW0000  where CXDJH0 = ad_CXDJH0 )
                       and SFDJH0 +0= 0;
    select  count(*) into  ls_COUNT0  from  yj_yw0000 A,bm_zlzd00 B where A.ZLXMID = B.ZLXMID and B.LBBH00 ='4' and YJDJH0 = ad_CXDJH0;
    IF  ls_COUNT0 > 0  THEN  --若是检验项目,则将其从接口的临时表删除
        delete from BM_YJDJH0 where YJDJH0=ad_CXDJH0;
    END IF;

   --添加新的费用明细
    Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,
           CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,CXBZ00,JMSHR0,JMYY00,GJBM00,SFLB00,ZFBL00,JMJE00,JMBZ00)
       select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,B.SFXMID,B.XMMC00,'',DECODE(B.DW0000,NULL,'次',B.DW0000),A.JJJE00,A.SFCS00*A.SL0000,round(A.JJJE00*A.SFCS00*A.SL0000,2),round(A.ZFJE00,2),A.GFJE00,A.JZJE00,
           ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,
           ls_KDKSBH,ls_KDYS00,ls_YJKSBH,nvl(ls_ZXR000,0),'0',ls_YSSZKS,'-',as_SHRXM0,as_SHRYY0,A.GJBM00,A.SFLB00,A.ZFBL00,nvl(a.JMJE00,0),DECODE(NVL(a.JMJE00,0),0,'0','4') --decode(ls_FSJMJE,'Y',0,NVL(A.JMJE00,0)) 
         from  YJ_YWJJ00 A,BM_YYSFXM B where A.SFXMID = B.SFXMID
             and  A.YJDJH0 in ( select  YJDJH0  from  YJ_YW0000  where  CXDJH0 = ad_CXDJH0 and SFDJH0=ls_DJH000 );
    --添加费用主表的信息
    Insert into  SF_BRFY00(DJH000,YBDJH0,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00,
               CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00)
        select ls_DJH000,'0',ls_GHH000,ls_GHID00,ls_LBBH00,ls_BRID00,ls_XM0000,ls_FBBH00,sum(HJJE00),sum(ZFJE00),sum(JZJE00),sum(GFJE00),0,
               ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKS0,0,ls_SFDJH0,as_CZYXM0,'医技单冲销'
           from  SF_FYMX00   where  DJH000 = ls_DJH000;
    --Update  SF_FYMX00
    --Update  SF_FYMX00  set  CXBZ00 = '+'  where  DJH000 = ls_SFDJH0
    --                                        and  XMBH00 in ( select A.SFXMID from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0)
    --Update  SF_FYMX00  set  CXBZ00 = '+'  where  MXID00  in ( select  A.MXID00  from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0);
    Update  SF_FYMX00  set  CXBZ00 = '+'  where  DJH000 = ls_SFDJH0  --2008.11.24
                                            and  XMBH00 in ( select A.SFXMID from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0);
COMMIT;
EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YJGZ00_YJD000('||to_char(ad_CXDJH0)||','||to_char(ad_CZY000)||','||
                 as_CZYXM0||','||to_char(ad_CZYKS0)||',as_YHMSG0,as_SYSMSG)',1,150);
                ROLLBACK;
END;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_SF_YJGZ00_YJD000