prompt 创建过程南平检验排队叫号等待人信息显示  SF_JY_XSPDDXX
create or replace function  SF_JY_XSPDDXX
(PXYCKSL in number,  --血液队列数量
 PTYCKSL in number,  --体液队列数量
 PZTS000 in number   --总显示数量
 )
return varchar2 is
  v_BRXM00  VARCHAR2(20);
  v_return  BM_XSPDM0.BZ0000%TYPE;
  V_COUNT   NUMBER(5);
-- Person              Date                  Comments
-- chenyw             2012.07.06      创建南平检验排队叫号等待人信息显示   
-- jinfl              2013.07.18      当姓名为0时,姓名为空。  归档JYPDJH-20130306-001   
-- chenyw        2015.12.01   显示屏幕的名字第二位是否改成*   for JYPDJH-20151201-001              
begin
 v_return:='';
 if PXYCKSL>0 then
   declare 
     cursor cur_JY_PDJHB0_XY is 
      select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
          dllxid=1 and jhbz00 in ('0','4') and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
         where rownum<=PXYCKSL;
   begin
    for v_JY_PDJHB0_XY in cur_JY_PDJHB0_XY loop
      v_BRXM00:=nvl(v_JY_PDJHB0_XY.BRXM00,'');
      select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual;
      v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' ');
      v_return:=v_return||'请'||v_JY_PDJHB0_XY.DLH000||'号'||v_BRXM00||'准备'||v_JY_PDJHB0_XY.JZBZ00||''||chr(13);
    end loop;
    end;
 end if;
 if PTYCKSL>0 then
   declare 
     cursor cur_JY_PDJHB0_TY is 
      select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
          dllxid=2 and jhbz00 in ('0','4') and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
         where rownum<=PTYCKSL;
   begin
    for v_JY_PDJHB0_TY in cur_JY_PDJHB0_TY loop
      v_BRXM00:=nvl(v_JY_PDJHB0_TY.BRXM00,'');
      select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual;
      v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' ');
      v_return:=v_return||'请'||v_JY_PDJHB0_TY.DLH000||'号'||v_BRXM00||'准备'||v_JY_PDJHB0_TY.JZBZ00||''||chr(13);
    end loop;
   end;
 end if;
 if PZTS000-PXYCKSL-PTYCKSL>0 then
   declare 
     cursor cur_JY_PDJHB0_QB is 
      select * from 
      (select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
         dllxid=1 and jhbz00 in ('0','4') and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
         where rownum<=(PZTS000-PTYCKSL)
       minus
       select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
             dllxid=1 and jhbz00 in ('0','4')  and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
             where rownum<=PXYCKSL);
   begin
    for V_JY_PDJHB0_QB in cur_JY_PDJHB0_QB loop
      v_BRXM00:=nvl(V_JY_PDJHB0_QB.BRXM00,'');
      select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual;
      v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' ');
      if trim(v_BRXM00) = '0' then
        v_BRXM00 := '';
      end if;
      v_return:=v_return||'请'||V_JY_PDJHB0_QB.DLH000||'号'||v_BRXM00||'等待'||V_JY_PDJHB0_QB.JZBZ00||''||chr(13);
    end loop;
   end;
   select count(1) into V_COUNT from 
     (select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
       dllxid=1 and jhbz00 in ('0','4') and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
        where rownum<=(PZTS000-PTYCKSL)
     minus
     select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急症)','') JZBZ00  from JY_PDJHB0  where  
       dllxid=1 and jhbz00 in ('0','4')  and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) 
         where rownum<=PXYCKSL);
   v_return:=chr(13)||v_return||'共有'||V_COUNT||'位病人在等待'||chr(13);
 end if;
 v_return:=v_return||'%'; 
 return v_return;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%