CREATE OR REPLACE FUNCTION SF_SF_GETYYKSBM
(
 pBRID00 in number,--病人id
 pGHH000 in char , --挂号
 pFHLX00 in number --返回类型 0医院科室编码(YYKSBM) 1医生证件号(YSZJHM)
)
return  varchar2 is
  lv_Return varchar2(50);
  lv_GHKS00 SF_BRXXB0.GHKS00%TYPE;
  lv_JZKS00 SF_BRXXB0.JZKS00%TYPE;
  lv_GHY000 SF_BRXXB0.GHY000%TYPE;
  lv_JZYS00 SF_BRXXB0.JZYS00%TYPE;
  lv_BMBH00 BM_YGBM00.BMBH00%TYPE;
  lv_YSZJHM BM_YGBM00.YSZJHM%TYPE;
  lv_YYKSBM BM_BMBM00.YYKSBM%TYPE;
  lv_YBSFFSYYTS varchar2(100);
  lv_SFXMYBJK   varchar2(100);
  LS_ZJHMLY BM_TYZD00.MC0000%type;--医生证件号码来源 0:取BM_YGBM00.YSZJHM 1:取BM_YGBM00.SFZBH0 默认0
 -- ls_error  varchar2(100);
begin
--Modification History
--Person       Date        CommentS
--zhangyc    2012.04.10    取医院科室编码 by MZSF-20120409-003
--zhangyc    2013.01.22    参数SF_YBSFFSYYTS<>0时直接取挂号科室对应的科室编码有区别于厦门(取挂号医生所在科室)by MZSF-20130122-002
-- csf       2013.03.01    加异常错误保护，返回值改为不出现空值 MZSF-20130301-005
--zhangyc    2013.06.25    去掉SF_YBSFFSYYTS判断，医院科室编码取挂号医生所在的科室 BY MZSF-20130625-002
--zhangyc    2014.08.14    非厦门地区(SF_SFXMYBJK='N')科室编码按挂号科室取值 by MZSF-20140812-001
--zhangyc    2020.09.29    返回值去空 by MZSF-20200929-004
-- linbin    2021.03.30    增加通用参数YSZJHMLY区分医生证件号码来源 0:取BM_YGBM00.YSZJHM 1:取BM_YGBM00.SFZBH0 默认0 by MZSF-20210325-002

   begin
     -- 医保收费对应未找到是否直接按自费结算 Y:是 N:否-提示错误 默认:N
     select nvl(MC0000,'0') into LS_ZJHMLY from BM_TYZD00 where ZDMC00='医保新接口参数设置' and BH0000='YSZJHMLY' ;
   exception
     when others then
       LS_ZJHMLY:='0';
   end;

   if (pBRID00=0) or (pGHH000='0') then
     return '';
   end if;
   lv_Return:='';
   begin
     select trim(value0) into lv_YBSFFSYYTS from xt_xtcs00 where name00='SF_YBSFFSYYTS';
   exception
   when  OTHERS  then
      lv_YBSFFSYYTS:='0';
   end;

   begin
     select trim(value0) into lv_SFXMYBJK from xt_xtcs00 where name00='SF_SFXMYBJK';
   exception
   when  OTHERS  then
      lv_SFXMYBJK:='N';
   end;
   if pGHH000 is null then
     lv_Return:='';
   else
     Select GHKS00,JZKS00,GHY000,JZYS00  into lv_GHKS00,lv_JZKS00,lv_GHY000,lv_JZYS00 from SF_BRXXB0 where BRID00=pBRID00 and GHH000=pGHH000;
     begin
      Select BMBH00,trim(decode(LS_ZJHMLY,'1',SFZBH0,YSZJHM)) into lv_BMBH00,lv_YSZJHM from BM_YGBM00  where YGBH00=decode(nvl(lv_JZYS00,0),0,lv_GHY000,lv_JZYS00);
     exception
     when others then
       lv_BMBH00:=0;
       lv_YSZJHM:='';
     end;

     begin
        if lv_SFXMYBJK='N' then --非厦门地区按挂号科室取医院科室编码
            Select trim(YYKSBM) into lv_YYKSBM from BM_BMBM00 where BMBH00=lv_GHKS00;
        else
          Select trim(YYKSBM) into lv_YYKSBM from BM_BMBM00 where BMBH00=decode(lv_BMBH00,0,lv_GHKS00,lv_BMBH00);
     end if;
     exception
     when others then
       lv_YYKSBM:=lv_GHKS00;
     end;

     if lv_YYKSBM is null then
        select YYKSBM into lv_YYKSBM from bm_bmbm00 where bmbh00=lv_BMBH00;
     end if;
   end if;

   if pFHLX00=0 then
     lv_Return:=lv_YYKSBM;
   else
     lv_Return:=lv_YSZJHM;
   end if;

  return trim(lv_Return);
EXCEPTION
when others then
    raise_application_error(-20002,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'@@'||substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,220));
    rollback;
end;