-- Start of DDL Script for Function SD_HOSPITAL.SP_YS_YYURL0_MZDZBL
-- Generated 13-三月-2020 10:47:16 from SD_HOSPITAL@136

CREATE OR REPLACE 
FUNCTION sp_ys_yyurl0_mzdzbl(
  PBRID00 in number,                  --病人ID
  PYGBH00 in number,                  --员工编号
  PBMBH00 in number                   --部门编号
)
return  varchar2 is
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- ruanbh      2020.01.03  create 获取预约URL MZYSSJ5-20191225-001
  VCOUNTER    number;                              --计数器变量
  ECUSTOM     exception;                           --异常
  VTSXX00     varchar2(200);                       --异常信息
  VXKH000     BM_YGBM00.XKH000%type;               --胸卡号
  VKMH000     IC_YHXX00.KMH000%type;               --卡面号
  VBRXM00     BM_BRXXB0.BRXM00%type;               --病人姓名
  VBRLXDH     BM_BRXXB0.BRLXDH%type;               --病人联系电话
  VBRZJBH     BM_BRXXB0.BRZJBH%type;               --病人证件编号
  VBRXB00     BM_BRXXB0.BRXB00%type;               --病人性别
  VJKBH01     BM_BMBM00.JKBH01%type;               --部门接口编号
  VURL000     varchar2(1000);                      --预约URL
  VYYIP00     XT_XTCS00.VALUE0%type;               --预约IP
  VYYTQTS     BM_YGBM00.YYTQTS%type;               --预约提前天数
begin
  select nvl(max(VALUE0),'') into VYYIP00 from XT_XTCS00 where name00='YS_YYIPNEW';
  select XKH000,yytqts into VXKH000,VYYTQTS from BM_YGBM00 where YGBH00=PYGBH00;
  select JKBH01 into VJKBH01 from BM_BMBM00 where BMBH00=PBMBH00;
  select KMH000 into VKMH000 from IC_YHXX00 where BRID00=PBRID00 and ZT0000='1' and rownum=1;
  select BRXM00,nvl(BRLXDH,BRDH00),trim(BRZJBH),BRXB00 into VBRXM00,VBRLXDH,VBRZJBH,VBRXB00 from BM_BRXXB0 where BRID00=PBRID00;
  VURL000 := VYYIP00||'ysxkh0='||VXKH000||'&brxm00='||VBRXM00||'&brlxdh='||VBRLXDH||'&sfzh00='||VBRZJBH||'&ghksbh='||VJKBH01
  ||'&yyCode=X'||'&dateType='||VYYTQTS||'&brxb00='||VBRXB00||'&kmh000='||VKMH000;
  return VURL000;
exception
  when no_data_found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240));
  when ECUSTOM       then
      raise_application_error(-20010,substrb(VTSXX00||'！*',1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
end;
/



-- End of DDL Script for Function SD_HOSPITAL.SP_YS_YYURL0_MZDZBL