CREATE OR REPLACE FUNCTION SF_dbzhq_jcbb_dzbl(ad_zyid00 number,ad_flag00 number)

return VARCHAR2 is
pragma AUTONOMOUS_TRANSACTION;
ls_dbzbm varchar2(20);
ls_brnl number(10);
ls_cbcwf number(10,4);
ls_count number(10);
ls_zdlb00 varchar2(20);
LS_ZDM000 varchar2(30);
LS_ZDMC00 varchar2(80);
LS_ID0000 NUMBER(10);
begin
select sq_jcbb_id0000.nextval into LS_ID0000 from dual;
select count(*) into ls_count from zy_brxxb0 where dbzbm0 is not null and  zyid00=ad_zyid00;
if nvl(ls_count,0) <>0 then --进入单病种的
select dbzbm0 into ls_dbzbm from zy_brxxb0 where dbzbm0 is not null and  zyid00=ad_zyid00;
select  b.dbzbm0,c.ZDM000,c.ZDMC00 into ls_dbzbm,LS_ZDM000,LS_ZDMC00 from BM_DBZZDB B, BQ_BRZDXX C,BM_DBZDEB D
where B.DBZBM0=D.DBZBM0
AND  ( ( b.WHLB00 = '0' and B.ZDBM00 = trim(C.ZDM000) )  or (b.WHLB00 = '1' and INSTR(C.ZDM000,B.ZDBM00)>0))
and C.ZYID00=ad_zyid00
and b.dbzbm0=ls_dbzbm
and ROWNUM=1;
    if ad_flag00='0' then
    return  ls_dbzbm;
    elsif ad_flag00='1' then
    return LS_ZDM000;
    elsif ad_flag00='2' then
    return LS_ZDMC00;
    end if;
end if;
--有出院主诊断判断主诊断，没有就判断入院诊断

select decode(count(*),0,'2','3') into ls_zdlb00 from BQ_BRZDXX C1 where C1.zdlb00='3' and C1.zyid00=ad_zyid00;
ls_count:=0;
select count(*) into ls_count from zy_brxxb0 m where  --医保类别过滤
exists(select 1 from ic_ybbrlb n where m.ybzxlb=n.ybzxlb and m.yblb00=n.yblb00 and (ybmc00 like '%福建省保健办%'  or ybmc00 like '%福建省两费中心%' or ybmc00 like '%省市特殊人群%' or ybmc00 like '%铁路医保中心%'))
and zyid00=ad_zyid00;
if ls_count <>0 then --医保类别过滤
ls_dbzbm :='';
return ls_dbzbm;
else
select trunc(MONTHS_BETWEEN(to_date(A.sjryrq,'yyMMdd'),to_date(A.CSRQ00,'yyMMdd'))/12) into ls_brnl from zy_brxxb0 a where zyid00=ad_zyid00;
--判断在院是否有病案室主操作
ls_count:=0;
select count(*) into ls_count from zy_brxxb0 c,BA_BRSSXX E where c.zyid00=E.LSH000 and e.sslb00='1' and BRZT00 like '2%' and c.zyid00=ad_zyid00;
if ls_count =0 then --在院并且没有主操作的，只判断诊断

INSERT INTO lsb_dbzzcb(DBZBM0,ZDM000,ZDMC00,FWMC00,ID0000)
select b.dbzbm0,c.ZDM000,c.ZDMC00,decode(c.FWMC00,'双','双',1),LS_ID0000 --into ls_dbzbm,LS_ZDM000,LS_ZDMC00
from BM_DBZZDB B, BQ_BRZDXX C,BM_DBZDEB D
where B.DBZBM0=D.DBZBM0
AND  ( ( b.WHLB00 = '0' and B.ZDBM00 = trim(C.ZDM000) )  or (b.WHLB00 = '1' and INSTR(C.ZDM000,B.ZDBM00)>0))
AND c.zdlb00=ls_zdlb00
and ad_zyid00=C.ZYID00
and
case when ls_brnl >14 and b.dbzbm0 in('BZ0000160','BZ0000162','BZ0000164','BZ0000261','BZ0000270') then 0
     when ls_brnl <=14 and b.dbzbm0 in('BZ0000161','BZ0000163','BZ0000165','BZ0000271') then 0
     when ls_brnl >=14 and b.dbzbm0 in('BZ0000001','BZ0000005') then 0
     when ls_brnl >6 and b.dbzbm0 in('BZ0000433','BZ0000434') then 0
else 1 end=1
;commit;

ls_count:=0;
select count(*) into ls_count from lsb_dbzzcb a,lsb_dbzdsc b where a.dbzbm0=b.dbzbm0 AND A.ID0000=LS_ID0000;--是否判断单双侧的判断
if ls_count >0 then  --是否判断单双侧的判断
    select a.dbzbm0,A.ZDM000,A.zdmc00 into ls_dbzbm,LS_ZDM000,LS_ZDMC00 from lsb_dbzzcb a,lsb_dbzdsc b where a.dbzbm0=b.dbzbm0 AND A.ID0000=LS_ID0000
    and DECODE(a.FWMC00,'双','双',1)=DECODE(b.dscsm0,'双','双',1) and rownum=1;
else
   select a.dbzbm0,A.ZDM000,A.zdmc00 into ls_dbzbm,LS_ZDM000,LS_ZDMC00 from lsb_dbzzcb a where A.ID0000=LS_ID0000 AND rownum=1;
end if;
    if ad_flag00='0' then
    return  ls_dbzbm;
    elsif ad_flag00='1' then
    return LS_ZDM000;
    elsif ad_flag00='2' then
    return LS_ZDMC00;
    end if;
end if;
--获取单病种  判断操作和诊断
INSERT INTO lsb_dbzzcb(DBZBM0,ZDM000,ZDMC00,FWMC00,ID0000)
select b.dbzbm0,c.ZDM000,c.ZDMC00,decode(c.FWMC00,'双','双',1),LS_ID0000 --into ls_dbzbm,LS_ZDM000,LS_ZDMC00
from BM_DBZZDB B, BQ_BRZDXX C,BM_DBZDEB D
where B.DBZBM0=D.DBZBM0
AND  ( ( b.WHLB00 = '0' and B.ZDBM00 = trim(C.ZDM000) )  or (b.WHLB00 = '1' and INSTR(C.ZDM000,B.ZDBM00)>0))
AND c.zdlb00=ls_zdlb00
and ( (trim(D.SSCZBM) IS  NOT NULL  AND exists  (select 1 from BA_BRSSXX E where C.ZYID00=E.LSH000 and INSTR(d.SSCZBM, e.ssm000)>0 AND  e.sslb00='1' ))
                        or (trim(D.SSCZBM) IS NULL and (not exists  (select 1 from SS_YW0000 E  where C.ZYID00=E.ZYGHID AND E.SSZT00 IN ('2','4')) and not exists  (select 1 from ba_basssb E  where C.ZYID00=E.lsh000 AND E.LB0000 IN ('介入治疗','手术')) ) ) )
and ad_zyid00=C.ZYID00
--AND DECODE(C.FWMC00,'双','双',1)=DECODE(D.BZ0000,'双侧','双',1)
and
case when ls_brnl >14 and b.dbzbm0 in('BZ0000160','BZ0000162','BZ0000164','BZ0000261','BZ0000270') then 0
     when ls_brnl <=14 and b.dbzbm0 in('BZ0000161','BZ0000163','BZ0000165','BZ0000271') then 0
     when ls_brnl >=14 and b.dbzbm0 in('BZ0000001','BZ0000005') then 0
     when ls_brnl >6 and b.dbzbm0 in('BZ0000433','BZ0000434') then 0
else 1 end=1
;commit;

ls_count:=0;
select count(*) into ls_count from lsb_dbzzcb a,lsb_dbzdsc b where a.dbzbm0=b.dbzbm0 AND A.ID0000=LS_ID0000;--是否判断单双侧的判断
if ls_count >0 then  --是否判断单双侧的判断
    select a.dbzbm0,A.ZDM000,A.zdmc00 into ls_dbzbm,LS_ZDM000,LS_ZDMC00 from lsb_dbzzcb a,lsb_dbzdsc b where a.dbzbm0=b.dbzbm0 AND A.ID0000=LS_ID0000
    and DECODE(a.FWMC00,'双','双',1)=DECODE(b.dscsm0,'双','双',1) and rownum=1;
else
   select a.dbzbm0,A.ZDM000,A.zdmc00 into ls_dbzbm,LS_ZDM000,LS_ZDMC00 from lsb_dbzzcb a where A.ID0000=LS_ID0000 AND rownum=1;
end if;
    if ad_flag00='0' then
    return  ls_dbzbm;
    elsif ad_flag00='1' then
    return LS_ZDM000;
    elsif ad_flag00='2' then
    return LS_ZDMC00;
    end if;
end if;
end;