CREATE OR REPLACE FUNCTION SF_ZY_ZRZYFY
(
 ad_ZYH000 in   char,  --住院号
 ad_BRID00 in   number, --病人id
 ad_FHLX00 in   number  --返还类型 0:结算判断 1:入院判断 2:获取择日挂号日期
)
return Varchar2 is
  VZRZYFY number(12,2); --择日住院费用
  SF_ZRZYSFYXJS varchar2(100);
  VMsgStr   varchar2(100);
  LS_KSRQ00 char(8);
  LS_JSRQ00 char(8);
  VZRKSRQ char(8);
  LS_COUNT0 number(5);
  LS_COUNT1 number(5);
  ZY_ZRZYRYDJXZ varchar2(100);
  LS_ZY_ZDXZZRRYTSFW XT_XTCS00.VALUE0%type;


  LS_RQSJ00 varchar2(16);
-- MODIFICATION HISTORY
--Person       Date        Comment
--zhangyc    2015.12.08     create by ZYSF-20151109-001
--zhangyc    2015.12.17     增加入参ad_FHLX00
--zhangyc    2016.07.12     修正限制提示 by ZYSF-20160530-001
--zhangyc    2016.12.15     增加ad_FHLX00=3 判断 by ZYSF-20161021-001
--qiulf      2019.11.22     增加参数ZY_ZDXZZRRYTSFW控制门诊无费用在一定天数内也可以择日入院 by ZYSF-20191111-002
--qiulf      2021.11.18     只判断出科前的日期是否有未处理的费用 by ZYSF-20211108-002
begin
  VMsgStr:='';
  VZRKSRQ:='';
  LS_JSRQ00:=to_char(sysdate,'YYYYMMDD');
  LS_KSRQ00:=to_char(sysdate-180,'YYYYMMDD');
--门诊收费择日住院病人(SF_BRXXB0.SFZRZY=Y)是否允许结算 Y:允许 N:不允许 默认值:N
  begin
    select trim(value0) into SF_ZRZYSFYXJS from xt_xtcs00 where  name00='SF_ZRZYSFYXJS';
  exception
    when others then
        SF_ZRZYSFYXJS:='N';
  end;
 --择日住院入院登记(门诊费用已结算是否限制入院登记 Y:提示 N:限制) 默认值:Y
  begin
    select trim(value0) into ZY_ZRZYRYDJXZ from xt_xtcs00 where  name00='ZY_ZRZYRYDJXZ';
  exception
    when others then
        ZY_ZRZYRYDJXZ:='Y';
  end;

    if (SF_ZRZYSFYXJS='N') then
        LS_RQSJ00 := null;
        if ad_ZYH000 is not null then
            begin
                select b.JSRQ00||b.JSSJ00 into LS_RQSJ00
                from ZY_BRXXB0 a,BQ_BRLDXX b where a.ZYID00=b.ZYID00 and  ZYH000=ad_ZYH000 and JSZT00='出院' and rownum=1 order by b.JSRQ00||b.JSSJ00 desc ;
            exception
            when others then
              LS_RQSJ00 := null;
            end;
        end if;
        begin
        select  sum(a.ZJE000),min(KDRQ00) into VZRZYFY,VZRKSRQ
            from YJ_YW0000 a
            where a.BRID00=ad_BRID00
            and a.ZYGHID in
            (select GHID00  from  VW_ZY_MZZRGH a
            where  a.brid00=ad_BRID00 and GHRQ00>=LS_KSRQ00 and GHRQ00<=LS_JSRQ00
            and (LS_RQSJ00 is null  or GHRQ00||GHSJ00 < LS_RQSJ00  and sfzrzy='Y')
            )
            and a.MZZYBZ='0' and nvl(ZRZYLS,0)=0
            --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废
            and a.XMZT00  in ('2','3')
            and NVL(CXDJH0,0)=0 and NVL(CXSL00,0)=0  and a.SFDJH0>0 --已收费未冲销
            --是否为附加单据0主单1采血费2试管费3挂号费4诊查费5血库子单6预约挂号7代煎9其他子单a注射b床位c病区普通常规费 d病区常规项目附加费 e药品用药附加费(bq_fjf000)
            and nvl(a.FJDJBZ,'1') not in ('3','4')--3挂号费4诊查费
            order by YJDJH0;
        exception
        when others then
        VZRZYFY:=0;
    end;
        if VZRZYFY>0 then
        if ad_FHLX00=0 then
          --VMsgStr:='该病人有门诊择日费用未处理,[是][否]继续结算?';
          VMsgStr:='该病人有门诊择日费用未处理,请先处理后再做出院结算！';
        elsif ad_FHLX00=1 then
            VMsgStr:='该病人为择日住院病人,[是][否]进行择日住院登记?';
        elsif ad_FHLX00=2 then
            VMsgStr:=VZRKSRQ;
        elsif ad_FHLX00=3 then
            select count(1) into LS_COUNT0
            from SF_BRFY00
            where  JZDH00+0>0 and brid00=ad_BRID00
            and CZRQ00>=LS_KSRQ00 and CZRQ00<=LS_JSRQ00
            and MZID00 in (select GHID00  from  VW_ZY_MZZRGH a where  a.brid00=ad_BRID00 and GHRQ00>=LS_KSRQ00 and GHRQ00<=LS_JSRQ00  and sfzrzy='Y');
            if LS_COUNT0>=1 then
                if ZY_ZRZYRYDJXZ='N'then
                    VMsgStr:='该病人的门诊择日费用已结算,不能以择日住院来入院！';
                else
                VMsgStr:='该病人的门诊择日费用已结算,[是][否]进行择日住院登记?';
                end if;
            else
                VMsgStr:='该病人为择日住院病人,[是][否]进行择日住院登记?';
                ZY_ZRZYRYDJXZ:='Y';
            end if;
            VMsgStr:=ZY_ZRZYRYDJXZ||','||VMsgStr;
        else
        VMsgStr:='';
        end if;
    else  --门诊无费用在一定天数内也可以择日入院
      begin
            select nvl(trim(VALUE0),'0') into LS_ZY_ZDXZZRRYTSFW from XT_XTCS00 where name00='ZY_ZDXZZRRYTSFW';
          exception
            when others  then
                LS_ZY_ZDXZZRRYTSFW := '0';
          end;
      if ad_FHLX00 = 3 and to_number(LS_ZY_ZDXZZRRYTSFW)>0 then
            select count(1) into LS_COUNT1 from SF_BRXXB0 where BRID00=ad_BRID00 and   trim(SFZRZY)='Y' and GHRQ00 >= to_char(sysdate-to_number(LS_ZY_ZDXZZRRYTSFW),'YYYYMMDD') and ZYID00 is null;
                if LS_COUNT1 > 0 then
                    VMsgStr:='Y,该病人为择日住院病人,[是][否]进行择日住院登记?';
                end if;
            end if;
        end if;
    end if;
    return VMsgStr;
end;