create or replace procedure SP_SF_BRZHXX(
    ad_BRID00    in   number,               --病人ID
    ad_CZY000    in   number,               --操作员ID
    as_CZYXM0    in   char,                 --操作员姓名
    as_JFLBMC    in   char,                 --账户类别、或称为交费类别
    ad_XJYE00    in   number,               --病人现金余额
    ad_ZZYE00    in   number,               --病人转账余额
    ad_BJJE00    in   number,               --报警金额
    ad_TYJE00    in   number,               --停用金额
    ad_YXQFJE    in   number,               --允许病人的欠费的最大金额
    as_ZFFSMC    in   char,                 --支付方式
    as_ZPH000    in   char,                 --支票号
    as_ZPKHYH    in   char,                 --出票的开户银行
    as_ZPDWMC    in   char,                 --出票单位/出票人
    as_YJJXH0    in   char,                 --预交金票据号
    as_CZBZ00    in   char,                 --'0':交款,'1':退款
    as_KTZZBZ    in   char default '0',     --'0':可以退转账款,'1':不可以退转账款,'2':可以退转账款(不受医院参数控制)        
    ad_PJH000    out  number,               --内部票据号
    as_YHMSG0    out  varchar,              --存储过程提示的错误信息
    as_SYSMSG    out  varchar,              --系统提示的错误信息
    as_COMMIT    in   char default 'Y',     --是否提交 默认提交
    as_ZPZH00    in   char default null,    --出票单位帐号或医保中心帐号
    ad_CZYKS0    in   number,               --操作员科室
    ad_ZPDWID    in   number default null,  --转账单位ID
    as_JYLSH0    in   char default null,    --交易流水号
    as_YKTSH0    in   char default null,    --健康通流水号
    as_JYLXMC    in   char default null,    --交易类型名称
    as_JGBM00    in   char default null,    --机构编码
    as_JGMC00    in   char default null,    --机构名称
    as_DDLSH0    in   char default null    --订单流水号
    
)
-- MODIFICATION HISTORY
-- Person   Date          Comments
-- lintj    2012.08.09    modify
-- zhangwz  2012.08.27    对于 ls_YYID00 的引用从数字型变更为字符型。
-- zhanghr  2012.10.30    整改
-- zhanghr  2012.11.2     电子钱包不产生票据 ?
-- jlg      2016.07.12    修改病人在住院时不能进行门诊预交金操作的处理 MZSF9-20160616-002 
-- zhangyc  2017.06.26    增加微信支付(ZFFS00=24)和支付宝支付(ZFFS00=25); by MZSF9-20170328-001
-- jlg      2017.09.19    增加ZPDWID字段传参 MZSF9-20170919-002
-- jlg      2017.09.22    增加参数SF_SFYXZFBRTZZYE的处理 MZSF9-20170912-002
-- qks      2018.03.15    增加入参as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0; for MZSF9-20180315-001
as
    ls_JFLBID         BM_BRJFLB.JFLBID%type;
    ls_ZFFSBH         BM_BRZFFS.ZFFSBH%type;
    ls_TMPZYE         SF_BRZHXX.ZYE000%type;
    ls_TMPXJE         SF_BRZHXX.XJYE00%type;
    ls_TMPZZE         SF_BRZHXX.ZZYE00%type;
    ls_ZJE000         SF_BRJFB0.JFJE00%type;
    ls_PJH000         SF_BRJFB0.PJH000%type;
    ls_BRXM00         BM_BRXXB0.BRXM00%type;
    ls_BRXB00         BM_BRXXB0.BRXB00%type;
    ls_MZZHID         SF_BRZHXX.MZZHID%type;
    ls_SFDJ00         SF_BRZHXX.SFDJ00%type;
    ls_JZDH00         SF_BRJFB0.JZDH00%type;
    ls_YHKJE0         SF_PJSYQK.YHKJE0%type;   --银行卡金额
    ls_ZZYE00         SF_PJSYQK.ZZJE00%type;
    ls_TYDWID         BM_BRXXB0.TYDWID%type;         
    ls_ZYZT00         BM_BRXXB0.ZYZT00%type; 
    ls_FBBH00         number;
    ls_SSWUTSCL       char(4);
    ls_YJJPJ0         char(4);                 --预交金票据与预交金退一样
    ls_PLBHCS         char(4);
    ls_CZRQ00         char(8);
    ls_CZSJ00         char(8);
    ls_count0         number(5);
    ls_DZQBJE         SF_PJSYQK.DZQBJE%type;   --电子钱包金额
    ls_YYID00         XT_YYXX00.YYID00%type;
    ls_ZYJKT0         XT_XTCS00.VALUE0%type;   --住院病人在门诊的交、退款限制
    ls_YXZZTK         XT_XTCS00.VALUE0%type;   --是否允许自费病人转账退款
    ls_ZZXJTC         XT_XTCS00.VALUE0%type;   --预交金已转帐，是否可以以现金退出
    Vzxcgbz           number;
    Vtsxx00           varchar2(200);
    Vxxxx00           varchar2(200);          
begin
  ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
  ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
  ls_YHKJE0 := 0;          --银行卡金额
  ls_DZQBJE := 0;          --电子钱包金额
  ls_ZZYE00 := ad_ZZYE00;
  --爱健康卡客户限制
  select count(*) into ls_count0 from BM_JTKBR0 where BRID00=ad_BRID00 and KLB000='0';
  if ls_count0>0 then
    if ad_CZY000<>99999 then
       as_YHMSG0:='爱健康卡客户,不能操作预交金!';
       as_SYSMSG:='爱健康卡客户,不能操作预交金!';
       rollback;
       return;
    end if;
  end if;
  --住院病人门诊交退款控制 0:交、退款都限制 1:不允许交款 2 不允许退款 3 交、退都不限制
  select nvl(max(trim(VALUE0)),'0')  into  ls_ZYJKT0   from XT_XTCS00  where NAME00='SF_ZYBRSFYXJKTKZ';
  --门诊预交金退款票据是否取预交金交款票据序号 Y:是N否，默认N
  select nvl(max(trim(VALUE0)),'N')  into  ls_YJJPJ0   from XT_XTCS00  where  name00 = 'SF_YJJPJ0';
   --SF_YJJTKPJLX:当SF_YJJPJ0=Y时门诊预交金退款票据类型(SF_PJSYQK.PLBH00)于交款票据类型是否一致
  select nvl(max(trim(VALUE0)),'Y')  into  ls_PLBHCS   from XT_XTCS00  where  name00 = 'SF_YJJTKPJLX';
  --四舍五入部分的金额特殊处理,Y:是
  select nvl(max(trim(VALUE0)),'N')  into  ls_SSWUTSCL from XT_XTCS00  where  name00 = 'SF_SSWUTSCL';
  --门诊是否允许自费病人退转账余额 1是0否，默认0
  select nvl(max(trim(VALUE0)),'0')  into  ls_YXZZTK   from XT_XTCS00  where  name00 = 'SF_SFYXZFBRTZZYE'; 
  
  --预交金已转帐，是否可以以现金退出
  select nvl(max(trim(VALUE0)),'N') into ls_ZZXJTC from XT_XTCS00 where name00 = 'SF_ZZSRSFNYXJTC'; 
    
  begin
      select SF_SF_GETYYID00() YYID00 into LS_YYID00  from dual;                 
  exception
    when others then 
      LS_YYID00:='0'; 
  end;
             
  select  JFLBID into  ls_JFLBID  from  BM_BRJFLB   where   JFLBMC = as_JFLBMC;
  select  ZFFSBH into  ls_ZFFSBH  from  BM_BRZFFS   where   ZFFSMC = as_ZFFSMC;
  
  if  as_CZBZ00 = '1'  then  
      ls_JZDH00:=1;  
  else 
      ls_JZDH00:=0;  
  end if;
  
  if  ls_ZFFSBH in (8,24,25)  then
      ls_YHKJE0 := ls_ZZYE00;
      ls_ZZYE00 := 0;
  end if;
  
  if  ls_ZFFSBH = 18  then
      ls_DZQBJE := ls_ZZYE00;
      ls_ZZYE00 := 0;
  end if;   
  
  --从BM_BRXXB0.ZYZT00获取病人住院状态
  select  BRXM00,BRXB00,ZYZT00,nvl(TYDWID,0),MZZHID,FBBH00 into  ls_BRXM00,ls_BRXB00,ls_ZYZT00,ls_TYDWID,ls_MZZHID,ls_FBBH00  
          from  BM_BRXXB0  where BRID00 = ad_BRID00;
  --病人如果处于住院状态，不允许进行门诊预交金操作
  --加参数SF_ZYBRSFYXJKTKZ控制 0：限制交、退款 1限制交款 2限制退款 3 交、退都不限制
  if (ls_ZYZT00='1') then 
    if (ls_ZYJKT0='0') then 
      as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交退操作!';
      as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!';
      rollback;
      return;
    elsif (ls_ZYJKT0='1') and (as_CZBZ00 ='0') then 
      as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交款操作!';
      as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!';
      rollback;
      return;     
    elsif (ls_ZYJKT0='2') and (as_CZBZ00 ='1') then 
      as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金退款操作!';
      as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!';
      rollback;
      return;     
    end if; --其它情况允许操作
  end if ;
  
  --0、判断该类病人的账户信息是否存在,若不存在，应该添加该病人的账户信息
  select count(*) into ls_count0 from SF_BRZHXX where MZZHID=ls_MZZHID;
  if ls_count0=0 then
      as_YHMSG0:='该病人无门诊账户，不能操作';
      as_SYSMSG:='数据异常';
      rollback;
      return;
  end if;
  
  select  SFDJ00,ZYE000,XJYE00,ZZYE00  into  ls_SFDJ00,ls_TMPZYE,ls_TMPXJE,ls_TMPZZE  
          from  SF_BRZHXX  where MZZHID=ls_MZZHID and JFLBID = ls_JFLBID;
          
  if  ls_SFDJ00 <> '0'   then
      as_YHMSG0:='该病人的账户的状态不是有效的状态,请先将该病人的账户转换成有效状态后,再进行本操作!!';
      as_SYSMSG:=SQLERRM||'SFDJ00:='||ls_SFDJ00;
      rollback;
      return;
  end if;
  
  ls_ZJE000:=ad_XJYE00+ad_ZZYE00;
  
  if  as_CZBZ00 = '1'  then  --若是退款的                 
      if  ls_TMPZYE <> ls_TMPXJE + ls_TMPZZE  then
        as_YHMSG0:='该病人的账户的信息非法,请与系统管理员联系!';
        as_SYSMSG:=SQLERRM||'该病人的帐户信息不合法,现金余额+转账余额不等于总余额!';
        rollback;
        return;
      end if;
      if  abs(ls_ZJE000) > ls_TMPZYE  then
         as_YHMSG0:='该病人的账户余额不足于支付该病人本次操作的金额!';
         as_SYSMSG:=SQLERRM||'目前的账户余额是:'||to_char(ls_TMPZYE);
         rollback;
         return;
      end if;

      if ls_ZZXJTC='Y' then 
         ls_TMPXJE:=ls_TMPXJE+ls_TMPZZE;
      end if;      
      
      if ad_XJYE00<0 and -1*ad_XJYE00>ls_TMPXJE then --退现金
         as_YHMSG0:='该病人账户现金余额('||to_char(ls_TMPXJE)||')不足支付该病人本次退款的现金金额';
         as_SYSMSG:=SQLERRM;
         rollback;
         return;
      end if;
      if ls_FBBH00=1 and ls_ZZYE00<0 and ls_YXZZTK<>'1' then
         as_YHMSG0:='自费病人不能做转账退款操作';
         as_SYSMSG:=SQLERRM||'目前的账户转账余额是:'||to_char(ls_TMPZZE);
         rollback;
         return;
      end if;
  end if;
  
  select   SQ_SF_PJSYQK_PJH000.nextval  into  ls_PJH000   from  dual;
  select   SQ_SF_BRJFB0_DJH000.nextval  into  ad_PJH000   from  dual;
  
  --1、添加病人交费记录
  if as_CZBZ00='0' then
     insert into  SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000,
                            JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPZH00,CZYKS0,ZPDWID,
                            JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0)
         values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000,
                 ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_ZPZH00,ad_CZYKS0,ad_ZPDWID,
                 as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0);
  else
     insert into  SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000,
                            JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0,ZPDWID,
                            JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0)
     values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000,
            ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,ad_CZYKS0,ad_ZPDWID,
            as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0);
  end if;
  
  SP_SF_BRJFB0_INSERT(ad_PJH000,ad_CZY000,ad_CZYKS0,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG);
   
  --2、添加票据使用情况
  if ls_ZFFSBH <> 18 then--电子钱包不产生票据
     if as_CZBZ00 = '0'  then  --'0':交款,'1':退款
        insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                              DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0)
            select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0,
                   ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0
            from BM_PJLXB0 where PLBH00 = 7 ; --收预交金票据
        if  sql%notfound  then
                 as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!';
                 as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 7)';
                 rollback;
                 return;
        end if;
     else
        if (trim(ls_YJJPJ0)='Y') and (trim(ls_PLBHCS)='Y') then
            Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                                  DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0)
               select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0,
                      ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0   
               from   BM_PJLXB0 where PLBH00 = 7 ; --退预交金票据
        else
            Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                                  DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0)
               select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0,
                      ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0
                 from BM_PJLXB0 where PLBH00 = 2 ; --退预交金票据
        end if;
        if  sql%notfound  then
                 as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!';
                 as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 2)';
                 rollback;
                 return;
        end if;
     end if;
     
     SP_SF_PJSYQK_INSERT(
       ls_PJH000,             --票据号 
       ad_CZY000,             --输入：操作员编码  
       ad_CZYKS0,             --操作员科室
       'N',                   --是否提交
       Vzxcgbz,               --执行成功标志 1成功 0失败 
       Vtsxx00,               --执行结果提示信息，成功的情况下也可能有警告信息
       Vxxxx00                --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 
     );   
  end if;
  
  if as_COMMIT='Y' then
    commit;
  end if;
  
exception
        when no_data_found then
             as_YHMSG0:='数据没有找到，请核对病人费别或病人的交费类别是否正确!';
             as_SYSMSG:=SQLERRM;
             rollback;
        when others then
             as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM;
             as_SYSMSG:=substr('执行过程SP_SF_BRZHXX时发生错误。'||SQLERRM,1,150);
             rollback;
end;
