create or replace function SF_YJ_GETSQDURL(ad_YJDJH0 number)
return  varchar2 is
  VYJROW    YJ_YW0000%rowtype;
  LS_PARAM0 varchar2(100) ;
  LS_URL000 varchar2(210);
  LS_WBYZID varchar2(100);
  LS_SQL000 varchar2(100);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2018.03.20  create for YJ-20180319-001   ;
-- dsm         2018.03.28  调试修改YJDJH0改成WBYZID  ZYSF-20180329-002
-- dsm         2018.11.29  修改for  YJ-20181121-001
-- dsm         2019.02.23  修改 for YJ-20190201-001
begin
  select * into VYJROW from YJ_YW0000 where YJDJH0=ad_YJDJH0;
  LS_URL000:=' ';
  if VYJROW.MZZYBZ='1' then
    --into LS_WBYZID
    begin
       LS_SQL000:='  select max(WBZID0) WBZID0  from BLJK_YZJKB0 where YZID00='||VYJROW.YZCFID||' and ZYID00='||VYJROW.ZYGHID;
       execute immediate LS_SQL000 into LS_WBYZID;
    exception
       when others then
         LS_WBYZID:=VYJROW.YJDJH0;
    end;
    for c_url in(
     select BZ0000,MC0000,PYSM00 from BM_TYZD00 where ZDMC00='地址申请单URL'  and WBSM00='住院' order by XSXH00 )
    loop
      LS_PARAM0:=' ';
      if c_url.MC0000='URL' then
        LS_URL000:=c_url.BZ0000;
      elsif c_url.MC0000='ZYGHID' then
        LS_PARAM0:=c_url.PYSM00||'='||VYJROW.ZYGHID;
      elsif c_url.MC0000='WBYZID' then
        LS_PARAM0:=c_url.PYSM00||'='||LS_WBYZID;
      elsif c_url.MC0000='YJDJH0' then
        LS_PARAM0:=c_url.PYSM00||'='||ad_YJDJH0;
      end if;
      if LS_PARAM0<>' ' then
        LS_URL000:=LS_URL000||'&'|| LS_PARAM0 ;
      end if;
    end loop;
  elsif VYJROW.MZZYBZ='0' then
    begin
       LS_SQL000:='  select max(WBZID0) WBZID0  from SD_DZBLJK.BLJK_YW0000 where YJDJH0='||VYJROW.YJDJH0||' and ZYGHID='||VYJROW.ZYGHID;
       execute immediate LS_SQL000 into LS_WBYZID;
    exception
       when others then
         LS_WBYZID:=VYJROW.YJDJH0;
    end;

    for c_url in(
     select BZ0000,MC0000,PYSM00 from BM_TYZD00 where ZDMC00='地址申请单URL'  and WBSM00='门诊' order by XSXH00 )
    loop
    --http://192.168.182.239:9080/htnmz/do?module=outpord&action=ExamOrder&method=getExamOpenXmlMain&pid=8813858?isterSN=19906059
      LS_PARAM0:=' ';
      if c_url.MC0000='URL' then
        LS_URL000:=c_url.BZ0000;
      elsif c_url.MC0000='ZYGHID' then
        LS_PARAM0:=c_url.PYSM00||'='||VYJROW.ZYGHID;
      elsif c_url.MC0000='ZYHGHH' then --isterSN
        LS_PARAM0:=c_url.PYSM00||'='||VYJROW.ZYHGHH;
      elsif c_url.MC0000='WBYZID' then
        LS_PARAM0:=c_url.PYSM00||'='||LS_WBYZID;
      elsif c_url.MC0000='BRID00' then   --pid
        LS_PARAM0:=c_url.PYSM00||'='||VYJROW.BRID00;
      elsif c_url.MC0000='YJDJH0' then
        LS_PARAM0:=c_url.PYSM00||'='||ad_YJDJH0;
      end if;
      if LS_PARAM0<>' ' then
        LS_URL000:=LS_URL000||'&'|| LS_PARAM0 ;
      end if;
    end loop;
  end if;
  return trim(LS_URL000);
end;