create or replace function SF_YF_YPPSJG
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- lxl         2023.09.14  create:药房显示皮试结果通过过程返回(SF_JCBB_GETYPPSJG)
-- linshu      2023.10.11  由函数过程SF_JCBB_GETYPPSJG重命名而来; for YF9-20230917-001
(
  PYZID00  number ,            --医嘱号,
  PZYID00  number,             --住院id
  PSRRQ00  varchar2,           --医嘱请领单生成日期
  PSRSJ00  varchar2            --医嘱请领单生成时间
)
/*****************************************规则设置***************************
--1、判断新医嘱中是否有"青霉素类"和"皮试剂皮试"（通用）
     如果是这两类皮试，判断是否有做过"青霉素皮试剂"的皮试结果
     有，直接取皮试结果值；没有，皮试结果为空
--2、其他皮试类型
     判断是否相同药品名是否有皮试结果
     有，取皮试结果值；没有，皮试结果为空
--3、在1和2都没有皮试结果的情况下
     判断皮试类型是否为免试，或者医嘱上已有皮试结果
     有则按结果值显示；没有判断医生此条医嘱是否为皮试用
****************************************************************************/
return varchar2 is
    V_PSJG00 varchar2(1000);
    V_PSID00 varchar2(5);
    V_PSMC00 varchar2(20);
    V_count1 number(2);
    V_BRID00 number(20);
    V_YPNM00 bm_yd0000.ypnm00%type;
    V_PSTGBZ varchar2(20);
    V_PSYBZ0 varchar2(20);   
begin
  V_PSJG00 :='';

  begin
     select BRID00,MX.YPNM00,PSID00,PSMC00,MX.PSTGBZ,MX.PSYBZ0 INTO V_BRID00,V_YPNM00,V_PSID00,V_PSMC00,V_PSTGBZ,V_PSYBZ0 from BQ_YPYZ00 YZ,BQ_YPYZMX MX,ZY_BRXXB0 ZY
        where MX.YZID00 = PYZID00 and ZY.ZYID00 = YZ.ZYID00  and YZ.YZID00 = MX.YZID00 and PSID00 is not null and rownum=1;
  exception
  when no_data_found then
     V_PSJG00 :='';
     return V_PSJG00;
  end;
  if instrb(V_PSMC00,'青霉素类')>0 or instrb(V_PSMC00,'皮试剂皮试')>0 then
     select count(*) into V_count1 from BQ_YPYZ00 YZ,BQ_YPYZMX MX where YZ.YZID00 = MX.YZID00 AND MX.YPNM00 in ( select BH0000 from BM_TYZD00 where ZDMC00 = '皮试药品配置') AND YZ.ZYID00 = PZYID00;
     if V_count1 >0 then
        begin
             select decode(JGMS00,'+','(+)','-','(-)',JGMS00) into V_PSJG00 from YJ_BRGMJL JL where ZYGHID = PZYID00 and BRID00 = V_BRID00 and YPNM00 in ( select BH0000 from BM_TYZD00 where ZDMC00 = '皮试药品配置') and (JL.JLRQ00,JL.JLSJ00) in
             (select max(JLRQ00),max(JLSJ00) from YJ_BRGMJL where BRID00 = V_BRID00 and YPNM00  in ( select BH0000 from BM_TYZD00 where ZDMC00 = '皮试药品配置') group by BRID00)
             and JL.JLRQ00||JL.JLSJ00<=PSRRQ00||PSRSJ00;
        exception
          when no_data_found then
               V_PSJG00 :='';
        end;
     end if;
  else
     begin
         select decode(JGMS00,'+','(+)','-','(-)',JGMS00) into V_PSJG00 from YJ_BRGMJL JL where ZYGHID = PZYID00 and BRID00 = V_BRID00 and YPNM00 = V_YPNM00 and (JL.JLRQ00,JL.JLSJ00) in
             (select max(JLRQ00),max(JLSJ00) from YJ_BRGMJL where BRID00 = V_BRID00 and YPNM00 = V_YPNM00 group by BRID00)
             and JL.JLRQ00||JL.JLSJ00<=PSRRQ00||PSRSJ00;
     exception
        when no_data_found then
             V_PSJG00 :='';
        end;
  end if;

  if V_PSJG00 ='' then
     --select from daul;
     select decode(V_PSTGBZ,'+','(+)','-','(-)',decode(V_PSMC00,'免试','免试',V_PSYBZ0)) into V_PSJG00 from dual;
  end if;

  return V_PSJG00;
end;
/