-- Start of DDL Script for Function SD_HOSPITAL.SF_XK_GETZXJYJG_QT
-- Generated 2017-9-7 18:29:03 from SD_HOSPITAL@NPHIS

CREATE OR REPLACE 
FUNCTION sf_xk_getzxjyjg_qt(ad_ZYID00 number,
                                              as_MZZYBZ varchar2
                                           )
return varchar2 is
   V_JYJG00   varchar2(400);
   V_JYJGALL   varchar2(400);
   V_LISCJDM  varchar2(10);
   strSQL1    varchar2(1000);
   LS_MZZYBZ  YJ_YW0000.MZZYBZ%type;
   ls_MC0000 varchar2(120);
-- modification history
-- Person      Date        Comments
-- dsm         2017.08.15  create  LKSX-20170802-001
begin
  select nvl(max(trim(VALUE0)),'0') into V_LISCJDM from 	XT_XTCS00 where name00='XT_LISCJDM'   ;
  if as_MZZYBZ='门诊' then
  	  LS_MZZYBZ:='0';
  else 	  
    LS_MZZYBZ:='1';
  end if;   
  V_JYJGALL:=' ';
  for c_jybgxm in(select MC0000,BH0000 from BM_TYZD00 where ZDMC00='输血申请显示检验项')
  loop
    if V_LISCJDM='0' then --易联众
       select c_jybgxm.MC0000||':'||nvl(max(JYJG00),'无数据') into V_JYJG00  from (select A.ZWMC00,A.JYJG00 
        from VW_JY_JYJG00 A,JY_YW0000 B where A.PYRQ00=B.PYRQ00 and A.YBH000=B.YBH000 and A.JYYWID=B.JYYWID 
          and B.ZYGHID=ad_ZYID00 and A.BGXMID =c_jybgxm.BH0000 and B.MZZYBZ=LS_MZZYBZ
          and A.PYRQ00 <= To_Char(SYSDATE,'yyyymmdd')
           order by b.PYRQ00 desc,b.PYSJ00 desc) M where rownum=1;
    else
       ls_MC0000:=c_jybgxm.MC0000||':';
       strSQL1:='SELECT  nvl(max(BGJYJG),''无数据'')  FROM (
       select A.ZWMC00,A.BGJYJG
         from JYJK_JYJG00 A,JYJK_YW0000 B where A.PYRQ00=B.PYRQ00 and A.YBH000=B.YBH000 and A.JYYWID=B.JYYWID 
          and B.brid00=(select brid00 from zy_brxxb0 where zyid00='||ad_ZYID00||') and A.BGXMID ='||c_jybgxm.BH0000||' and B.MZZYBZ='||LS_MZZYBZ||' 
          and A.PYRQ00 <= To_Char(SYSDATE,''yyyymmdd'')
           order by b.PYRQ00 desc,b.PYSJ00 desc) M where rownum=1'; 
       begin
          execute immediate strSQL1 into V_JYJG00 ;
          V_JYJG00:=ls_MC0000||V_JYJG00;
       exception
       when others then
          V_JYJG00:=' ';
       end;                  
    end if;          
    if V_JYJGALL=' ' then V_JYJGALL:=V_JYJG00;
    else V_JYJGALL:=V_JYJGALL||chr(13)||chr(13)||V_JYJG00;
    end if;   
  end loop;  
  return V_JYJGALL;                          
end;



-- End of DDL Script for Function SD_HOSPITAL.SF_XK_GETZXJYJG_QT
/



-- End of DDL Script for Function SD_HOSPITAL.SF_XK_GETZXJYJG_QT