CREATE OR REPLACE PROCEDURE SP_SF_BRDB00_REG000
(
        ad_YGBH00    IN    NUMBER,    --员工编号
        ad_JFLBID    IN    NUMBER,    --账户交费类别
        ad_BRID00    IN    NUMBER,    --病人ID
        ad_DBJE00    IN    NUMBER,    --担保金额
        ad_CZY000    IN    NUMBER,    --操作员
        as_CZYXM0    IN    CHAR,      --操作员姓名
        as_SSLB00    IN    CHAR,      --所属类别,'1':担保,'2':允许欠费
        as_DBYY00    IN    CHAR,      --原因
        as_DBJSRQ    IN    CHAR,      --担保的结束日期或允许欠费的结束日期
        as_DBJSSJ    IN    CHAR,      --担保的结束时间或允许欠费的结束时间
        as_YHMSG0    OUT   VARCHAR,   --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR    --系统提示的错误信息
)
as
        ls_DBJE00    SF_BRDB00.DBJE00%TYPE;
        ls_YXDBJE    ZY_DBQXB0.DBJE00%TYPE;
        ls_BZ0000    ZY_DBQXB0.BZ0000%TYPE;
        ls_fbbh00    bm_brxxb0.fbbh00%TYPE;
        LS_YYID00    xt_yyxx00.yyid00%TYPE;
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- yangh       2009.11.09   担保的不计算,只计算欠费的SSLB00='2'
-- xuzw        2009.12.18   把担保金额也加到欠费字段中
-- zhr         2010.04.22   不再修改SF_BRZHXX.YXQFJE由SF_BRDB00触发器完成
-- wangjin     2019.09.18   莆田涵江医院门诊医保病人限制欠费担保
BEGIN
    --判断该员工对应的担保金额是否是有效的
    if as_SSLB00<>'3' then
        select NVL(DBJE00,0) into  ls_YXDBJE  from  ZY_DBQXB0  where YGBH00 = ad_YGBH00  and BZ0000=as_SSLB00;
        select NVL(sum(DBJE00),0) into  ls_DBJE00  from  SF_BRDB00  where  YGBH00 = ad_YGBH00 and SSLB00 = as_SSLB00  and SFYX00 = 'Y';
        if  ls_YXDBJE < (ls_DBJE00+ad_DBJE00)  then
            as_YHMSG0:='该员工的操作金额已超过系统所设定的最高限额:'||to_char(ls_YXDBJE)||',历史累计:'||to_char(ls_DBJE00)||',操作失败！';
            as_SYSMSG:=SQLERRM;
            ROLLBACK;
            RETURN;
        end if;
    end if;

    select fbbh00 into ls_fbbh00 from bm_brxxb0 where brid00=ad_BRID00;
    select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual;
    --莆田涵江医院门诊医保病人限制欠费担保;
    if ls_fbbh00='3' and LS_YYID00='223455' then
       as_YHMSG0:='该病人为医保病人无法设置欠费担保,操作失败！';
       as_SYSMSG:=SQLERRM;
        ROLLBACK;
        RETURN;
    end if;

    Insert into  SF_BRDB00(BRDBID,YGBH00,JFLBID,BRID00,DBJE00,SFYX00,DBRQ00,DBSJ00,CZY000,CZYXM0,DBJSRQ,DBJSSJ,SSLB00,DBYY00)
        values(SQ_SF_BRDB00_BRDBID.nextval,ad_YGBH00,ad_JFLBID,ad_BRID00,ad_DBJE00,'Y',to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_CZY000,as_CZYXM0,as_DBJSRQ,as_DBJSSJ,as_SSLB00,as_DBYY00);
-- zhr         2010.04.22   不再修改SF_BRZHXX.YXQFJE由SF_BRDB00触发器完成，写入SF_BRZHXX.DBJE00
--    if (as_SSLB00='1') or (as_SSLB00='2') then
--       Update  SF_BRZHXX  set  YXQFJE = NVL(YXQFJE,0)+ad_DBJE00
--                 where  BRID00 = ad_BRID00  and  JFLBID = ad_JFLBID;
--    end if;
EXCEPTION
      WHEN OTHERS THEN
           as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
           as_SYSMSG:=substr(SQLERRM||'执行存储过程错误: SP_SF_BRDB00_REG000('||to_char(ad_YGBH00)||','||to_char(ad_JFLBID)||','||to_char(ad_BRID00)||','||
           to_char(ad_DBJE00)||','||to_char(ad_CZY000)||','||as_CZYXM0||','||as_SSLB00||',as_YHMSG0,as_SYSMSG)',1,150);
           ROLLBACK;
END;