CREATE OR REPLACE PROCEDURE SP_ZY_BASYXX_CREATE ( Pzyid00 in number, Pcommit in char default 'N' ) as -- MODIFICATION HISTORY -- Person Date Comments -- linzetao 2020.12.16 移植5.0SP_ZS_BASYXX_CREATE过程 ZYSF9-20201201-001 -- linzetao 2021.01.15 归并省人民所做调整内容, 增加判断病案已发送, 不再重新生成 ZYSF9-20210115-001 lsCount0 number(3); Vmzzdm0 varchar2(20); --门诊诊断编码; Vmzzdmc varchar2(200); --门诊诊断名称; Vryzdm0 varchar2(20); --入院诊断编码; Vryzdmc varchar2(200); --入院诊断名称; Vcyzdm0 varchar2(20); --出院诊断编码; Vcyzdmc varchar2(200); --出院诊断名称; Vybzxlb ZY_BRXXB0.YBZXLB%type; --医保中心类别 VRYBQ00 varchar2(20) ; --入院病情:(1.有，2.临床未确定，3.情况不明，4.无) begin --病案已发送医保, 不再重新生成 select count(*) into lsCount0 from vw_zy_basyxx where ZYID00=Pzyid00 and FSCGBZ='1'; if lsCount0 > 0 then return; end if; delete from ZY_BASYXX where ZYID00=Pzyid00; insert into ZY_BASYXX(ZYID00,ZYLSH0,CARDNO,YYBAH0,RYKB00,RYBS00,ZKKB00,CYKB00,CYBS00,RYQK00,RYZDQK,CYZD00,ZDMC00,CYQK00, CYZD01,ZDMC01,CYQK01,CYZD02,ZDMC02,CYQK02,CYZD03,ZDMC03,CYQK03,CYZD04,ZDMC04,CYQK04,CYZD05,ZDMC05,CYQK05,BLZD00,ZZYSXM, SSBH01,SSMC01,SSRQ01,MZFS01,SSBH02,SSMC02,SSRQ02,MZFS02,SSBH03,SSMC03,SSRQ03,MZFS03,SSBH04,SSMC04,SSRQ04,MZFS04, SSBH05,SSMC05,SSRQ05,MZFS05,SSBH06,SSMC06,SSRQ06,MZFS06,SSBH07,SSMC07,SSRQ07,MZFS07,SSBH08,SSMC08,SSRQ08,MZFS08, FSCGBZ,ERROR0, RYKSBM,CYKSBM,ZYTS00,RYTJ00,SSYY00,SSYYDM,BLZDBM,BLHM00,YWGM00,GMYWMC,SWSJ00,XUEX00,RH0000,BAZL00, ZKYS00,ZKRQ00,LYFS00,NJSJG0,CYJH00,CYJHMD,YQHMTS,YQHMXS,YQHMFZ,YHHMTS,YHHMXS,YHHMFZ) select Pzyid00,trim(a.YBZYH0),nvl(nvl(b.YBKH00,b.cardno),c.YBKH00),trim(a.MRN000), (select substrb(max(BMMC00),1,20) from BM_BMBM00 where a.RZKS00=BMBH00), (select substrb(max(BMMC00),1,20) from BM_BMBM00 where a.RYBQ00=BMBH00), (select substrb(max(BMMC00),1,20) from BM_BMBM00 where substrb(b.ZKKB00,1,decode(instrb(b.ZKKB00,';'),0,lengthb(b.ZKKB00),instrb(b.ZKKB00,';')-1))=BMBH00), (select substrb(max(BMMC00),1,20) from BM_BMBM00 where a.DQKS00=BMBH00), (select substrb(max(BMMC00),1,20) from BM_BMBM00 where a.DQBQ00=BMBH00), ' ',null,' ',' ',' ', null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,substrb(b.BLZD00,1,200),a.ZZYSXM, null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, '0',null, (select YBKSBH from bm_bmbm00 where bmbh00=a.RZKS00) RYKSBM, -- 入院科别编码(新增) (select YBKSBH from bm_bmbm00 where bmbh00=a.DQKS00) CYKSBM, -- 出院科别编码(新增) b.ZYTS00, -- 实际住院天数(新增) b.RYTJ00, -- 入院途径(新增)1.急诊2.门诊3.其他医疗机构转入9.其他 b.SSZDWY, -- 损伤、中毒的外部原因(新增) null, --损伤、中毒的疾病编码(新增) b.BLZDBM, --病理诊断编码(新增) substrb(b.BLH000,1,20), --病理号(新增) decode(b.SFYWGM,'有','2','2','2','1'), --药物过敏(新增)(1.无，2.有) b.GMYW00, -- 过敏药物名称 null, --死亡患者尸检(新增)(1.无，2.有) case b.XX0000 when 'AB型' then '4' when 'A型' then '1' when 'B型' then '2' when 'O型' then '3' when '其他' then '3' else '6' end, --血型(1.A2.B3.O4.AB5.不详6.未查) case b.RH0000 when '阴性' then '1' when '阳性' then '2' when '不详' then '3' else '4' end RH0000, --RH(1.阴2.阳3.不详4.未查) case b.BAZL00 when '甲' then '1' when '乙' then '2' else '3' end BAZL00, --病案质量(1.甲2.乙3.丙) null ZKYS00, -- 质控医师 b.ZKRQ00, -- 质控日期(年月日) case b.LYFS00 when '医嘱离院' then '1' when '医嘱转院' then '2' when '医嘱转社区卫生服务机构' then '3' when '非医嘱离院' then '4' when '死亡' then '5' else '9' end LYFS00, --离院方式(1.医嘱离院，2.医嘱转院，3.医嘱转社区卫生服务机构/乡镇卫生院，4.非医嘱离院5.死亡9.其他) null NJSJG0, -- 拟接收医疗机构名称 decode(ZZYJH0,'有','2','Y','2','1') CYJH00, --是否有出院31天内再住院计划(新增)(1.无，2.有) b.ZZYMD0 CYJHMD, --出院31天内再住院目的(新增) b.HMRYHD YQHMTS, -- 入院前颅脑损伤患者昏迷时间（_天_小时_分钟）天 b.HMRYHH YQHMXS, -- 入院前颅脑损伤患者昏迷时间（_天_小时_分钟）小时 b.HMRYHM YQHMFZ, -- 入院前颅脑损伤患者昏迷时间（_天_小时_分钟）分钟 b.HMRYQD YHHMTS, -- 入院后颅脑损伤患者昏迷时间（_天_小时_分钟）天 b.HMRYQH YHHMXS, -- 入院后颅脑损伤患者昏迷时间（_天_小时_分钟）小时 b.HMRYQM YHHMFZ -- 入院后颅脑损伤患者昏迷时间（_天_小时_分钟）分钟 from ZY_BRXXB0 a,BA_BRZYXX b,BM_BRXXB0 c where a.ZYID00=Pzyid00 and a.ZYID00=b.LSH000(+) and a.BRID00=c.BRID00; --门诊诊断 select max(ZDM000) into Vmzzdm0 from BQ_BRZDXX a,XT_ICD900 b where a.ZYID00=Pzyid00 and a.ZDLB00='1' and b.XTBZ00='1' and a.ZDM000=b.ICD900; --and b.XTBZ00='1' if Vmzzdm0 is not null then select max(nvl(DYBZBM,ICD900)),max(nvl(DYJBMC,JBMC00)) into Vmzzdm0,Vmzzdmc from XT_ICD900 where ICD900=Vmzzdm0 and XTBZ00='1'; --and XTBZ00='1' end if; --入院诊断 select max(ZDM000) into Vryzdm0 from BQ_BRZDXX a,XT_ICD900 b where ZYID00=Pzyid00 and a.ZDLB00='2' and b.XTBZ00='1' and a.ZDM000=b.ICD900; if Vryzdm0 is not null then select max(nvl(DYBZBM,ICD900)),max(nvl(DYJBMC,JBMC00)) into Vryzdm0,Vryzdmc from XT_ICD900 where ICD900=Vryzdm0 and XTBZ00='1'; end if; --出院诊断 select max(ZDM000) into Vcyzdm0 from BQ_BRZDXX a,XT_ICD900 b where ZYID00=Pzyid00 and a.ZDLB00='3' and b.XTBZ00='1' and a.ZDM000=b.ICD900; if Vcyzdm0 is not null then select max(nvl(DYBZBM,ICD900)),max(nvl(DYJBMC,JBMC00)) into Vcyzdm0,Vcyzdmc from XT_ICD900 where ICD900=Vcyzdm0 and XTBZ00='1'; elsif Vmzzdm0 is not null then --1、取西医门诊诊断 Vcyzdm0 := Vmzzdm0; Vcyzdmc := Vmzzdmc; else --2、取同类型西医诊断 select rpad(substrb(replace(max(ZDM000),'+',''),1,5),7,'0') into Vcyzdm0 from BQ_BRZDXX a,XT_ICD900 b where ZYID00=Pzyid00 and a.ZDLB00='3' and b.XTBZ00='0' and a.ZDM000=b.ICD900; select max(nvl(DYBZBM,ICD900)),max(nvl(DYJBMC,JBMC00)) into Vcyzdm0,Vcyzdmc from XT_ICD900 where ICD900=Vcyzdm0 and XTBZ00='1'; if Vcyzdmc is null then --3、取一个有效的西医诊断 select max(ZDM000) into Vcyzdm0 from BQ_BRZDXX a,XT_ICD900 b where ZYID00=Pzyid00 and b.XTBZ00='1' and a.zdm000=b.ICD900; if Vcyzdm0 is null then --4、取一个中医出院疾病诊断 Vcyzdm0 := 'ZY9999'; select max(ZDMC00) into Vcyzdmc from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00 in ('a','b','e','f'); if Vcyzdmc is null then select max(ZDMC00) into Vcyzdmc from BQ_BRZDXX where ZYID00=Pzyid00; end if; else select max(nvl(DYBZBM,ICD900)),max(nvl(DYJBMC,JBMC00)) into Vcyzdm0,Vcyzdmc from XT_ICD900 where ICD900=Vcyzdm0 and XTBZ00='1'; end if; end if; end if; --入院病区 select nvl(max(RYBQ00),'4') into VRYBQ00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00='2' and RYBQ00 is not null; if VRYBQ00='有' then VRYBQ00:='1'; elsif VRYBQ00='临床未确定' then VRYBQ00:='2'; elsif VRYBQ00='情况不明' then VRYBQ00:='3'; elsif VRYBQ00='无' then VRYBQ00:='4'; elsif VRYBQ00 not in ('1','2','3','4') then VRYBQ00:='4'; end if; update ZY_BASYXX z set MZZDMC=Vmzzdmc, -- 门（急）诊诊断名称 MZZD00=Vmzzdm0, -- 门（急）诊诊断疾病编码 RYZD00=Vryzdm0, -- 入院诊断编码 RYZDQK=nvl(Vryzdmc,'0'), -- 入院诊断名称 RYBQ00=Vrybq00, -- 入院主要病情（新增）(1.有，2.临床未确定，3.情况不明，4.无) CYZD00=nvl(Vcyzdm0,'0'), -- 出院诊断编码; ZDMC00=Vcyzdmc -- 出院诊断名称; where ZYID00=Pzyid00; --update ZY_BASYXX z set RYKB00=(select substrb(max(BMMC00),1,20) from ZY_BRXXB0 a,BM_BMBM00 where a.ZYID00=z.ZYID00 and a.RZKS00=BMBH00), -- RYBS00=(select substrb(max(BMMC00),1,20) from ZY_BRXXB0 a,BM_BMBM00 where a.ZYID00=z.ZYID00 and a.RYBQ00=BMBH00), -- ZKKB00=(select substrb(max(BMMC00),1,20) from bm_bmbm00 where substrb(b.zkkb00,1,decode(instrb(b.zkkb00,';'),0,lengthb(b.zkkb00),instrb(b.zkkb00,';')-1))=BMBH00), -- CYKB00=(select max(BMMC00) from ZY_BRXXB0 a,BM_BMBM00 where a.ZYID00=z.ZYID00 and a.DQKS00=BMBH00), -- CYBS00=(select max(BMMC00) from ZY_BRXXB0 a,BM_BMBM00 where a.ZYID00=z.ZYID00 and a.DQBQ00=BMBH00) -- where ZYID00=Pzyid00; --update ZY_BASYXX z set RYQK00=(select nvl(max(DM0000),'3') from BA_BRZYXX b,XT_ZD0000 where b.LSH000=z.ZYID00 and ZDBM00= 'RYQK00' and (b.RYQK00=VALUE0 or b.RYQK00=DM0000)), -- RYZDQK=(select nvl(max(ZDMC00),'0') from BQ_BRZDXX a where a.ZYID00=z.ZYID00 and ZDLB00='2' and rownum=1), -- CYZD00=(select nvl(max(ZDM000),'0') from BQ_BRZDXX a where a.ZYID00=z.ZYID00 and ZDLB00='3' and rownum=1), -- ZDMC00=(select nvl(substrb(max(ZDMC00),1,60),'0') from BQ_BRZDXX a where a.ZYID00=z.ZYID00 and ZDLB00='3' and rownum=1), -- CYQK00=(select nvl(max(x.DM0000),'2') from BQ_BRZDXX v,XT_ZLXG00 x where z.ZYID00=v.ZYID00 and v.ZDLB00='3' and v.ZLXG00=x.DM0000 and rownum=1) -- where ZYID00=Pzyid00; update ZY_BASYXX z set RYQK00=(select nvl(max(DM0000),'3') from BA_BRZYXX b,XT_ZD0000 where b.LSH000=z.ZYID00 and ZDBM00= 'RYQK00' and (instr(value0,b.RYQK00)>0 or instr(value0,b.RYQK00)>0 or b.RYQK00=DM0000 or value0=DM0000) and rownum=1), CYQK00=(select nvl(max(x.DM0000),'2') from BQ_BRZDXX v,XT_ZLXG00 x where z.ZYID00=v.ZYID00 and v.ZDLB00='3' and v.ZLXG00=x.DM0000 and rownum=1) where ZYID00=Pzyid00; --手术麻醉 select nvl(max(ybzxlb),'0') into Vybzxlb from zy_brxxb0 where ZYID00=Pzyid00; declare cursor c_ssxx is select a.SSXH00,a.SSRQ00 as SSQSRQ,a.SSM000 as SSBH00,substrb(a.SSMC00,1,60) as SSMC00, (select max(decode(Vybzxlb,'W',lpad(MZYBBH,2,'0'),MZYBBH)) from BM_MZZD00 where MZMC00=a.MZFS00 and rownum=1) as MZBH00, decode(substr(a.SSZL00,1,1),'特','4','大','3','中','2','小','1','0') as SSDJMC,a.SSYSMC as SSYSXM, case a.QKYHDJ when 'Ⅰ甲' then '1' when 'Ⅰ乙' then '2' when 'Ⅰ丙' then '3' when 'Ⅱ甲' then '4' when 'Ⅱ乙' then '5' when 'Ⅱ丙' then '6' when 'Ⅲ甲' then '7' when 'Ⅲ乙' then '8' when 'Ⅲ丙' then '9' else null end YHDJ00 from BA_BRSSXX a where LSH000=Pzyid00 order by SSXH00; --select a.SSXH00,a.SSQSRQ,a.SSBH00,a.SSMC00,a.MZBH00 from VW_ZY_BRSSXX a where ZYID00=Pzyid00 order by SSXH00; begin for ss in c_ssxx loop if ss.SSXH00=1 then update ZY_BASYXX set SSJB01=ss.SSDJMC,CZYS01=ss.SSYSXM,YHDJ01=ss.YHDJ00,SSBH01=ss.SSBH00,SSMC01=ss.SSMC00,SSRQ01=ss.SSQSRQ,MZFS01=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=2 then update ZY_BASYXX set SSJB02=ss.SSDJMC,CZYS02=ss.SSYSXM,YHDJ02=ss.YHDJ00,SSBH02=ss.SSBH00,SSMC02=ss.SSMC00,SSRQ02=ss.SSQSRQ,MZFS02=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=3 then update ZY_BASYXX set SSJB03=ss.SSDJMC,CZYS03=ss.SSYSXM,YHDJ03=ss.YHDJ00,SSBH03=ss.SSBH00,SSMC03=ss.SSMC00,SSRQ03=ss.SSQSRQ,MZFS03=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=4 then update ZY_BASYXX set SSJB04=ss.SSDJMC,CZYS04=ss.SSYSXM,YHDJ04=ss.YHDJ00,SSBH04=ss.SSBH00,SSMC04=ss.SSMC00,SSRQ04=ss.SSQSRQ,MZFS04=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=5 then update ZY_BASYXX set SSJB05=ss.SSDJMC,CZYS05=ss.SSYSXM,YHDJ05=ss.YHDJ00,SSBH05=ss.SSBH00,SSMC05=ss.SSMC00,SSRQ05=ss.SSQSRQ,MZFS05=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=6 then update ZY_BASYXX set SSJB06=ss.SSDJMC,CZYS06=ss.SSYSXM,YHDJ06=ss.YHDJ00,SSBH06=ss.SSBH00,SSMC06=ss.SSMC00,SSRQ06=ss.SSQSRQ,MZFS06=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=7 then update ZY_BASYXX set SSJB07=ss.SSDJMC,CZYS07=ss.SSYSXM,YHDJ07=ss.YHDJ00,SSBH07=ss.SSBH00,SSMC07=ss.SSMC00,SSRQ07=ss.SSQSRQ,MZFS07=ss.MZBH00 where ZYID00=Pzyid00; elsif ss.SSXH00=8 then update ZY_BASYXX set SSJB08=ss.SSDJMC,CZYS08=ss.SSYSXM,YHDJ08=ss.YHDJ00,SSBH08=ss.SSBH00,SSMC08=ss.SSMC00,SSRQ08=ss.SSQSRQ,MZFS08=ss.MZBH00 where ZYID00=Pzyid00; end if; end loop; end; --出院诊断 declare cursor c_zdxx is select rownum row000,trim(a.ZDM000) ZDM000 ,trim(a.ZDMC00) ZDMC00 ,trim(a.DM0000) dm0000 from ( select --rownum row000, case when d.ZDBM00='ZYZDLB' and instrb(d.VALUE0,'疾病')>0 then (select nvl(DYBZBM,JBBH00) from BM_ZYJB00 where JBBH00=a.ZDM000 and rownum=1) when d.ZDBM00='ZYZDLB' and (instrb(d.VALUE0,'证候')>0 or instrb(d.VALUE0,'症候')>0) then (select nvl(DYBZBM,ZHBH00) from BM_ZYZH00 where ZHBH00=a.ZDM000 and rownum=1) else (select nvl(DYBZBM,ZDM000) from XT_ICD900 where icd900=a.ZDM000 and rownum=1) end ZDM000, --ICD9码BM_ZYJB00.JBBH00'; case when d.ZDBM00='ZYZDLB' and instrb(d.VALUE0,'疾病')>0 then (select nvl(DYJBMC,JBMC00) from BM_ZYJB00 where JBBH00=a.ZDM000 and rownum=1) when d.ZDBM00='ZYZDLB' and (instrb(d.VALUE0,'证候')>0 or instrb(d.VALUE0,'症候')>0) then (select nvl(DYJBMC,ZHMC00) from BM_ZYZH00 where ZHBH00=a.ZDM000 and rownum=1) else (select nvl(DYJBMC,jbmc00) from XT_ICD900 where icd900=a.ZDM000 and rownum=1) end ZDMC00, --诊断描述名称 12.14'; case when (b.DM0000>'5' or b.DM0000 is null) then '5' else b.DM0000 end DM0000 from BQ_BRZDXX a,XT_ZLXG00 b,XT_ZD0000 d --XT_ICD900 c, where a.ZYID00=Pzyid00 --and ZDLB00 in ('3','4') and a.ZLXG00=b.DM0000(+) --and a.ZDM000=c.ICD900--(+) and a.ZDLB00=d.DM0000 and d.zdbm00 in ('ZDLB00','ZYZDLB') and (d.ZDBM00='ZDLB00' and exists (select 1 from XT_ICD900 where icd900=a.ZDM000) or d.ZDBM00='ZYZDLB' and a.ZDM000 is not null) and a.ZDLB00 not in ('1','2','c','g','d','j','3','e','f') order by decode(zdlb00,'a','0','b','1','3','2','4','3',zdlb00),a.ID0000 ) a; --select rownum row000,a.ZDM000,substrb(a.ZDMC00,1,60) ZDMC00,b.DM0000 from BQ_BRZDXX a,XT_ZLXG00 b where ZYID00=Pzyid00 and ZDLB00='4' and a.ZLXG00=b.DM0000 order by a.ID0000; begin for zd in c_zdxx loop if zd.ROW000=1 then update ZY_BASYXX set CYZD01=zd.ZDM000,ZDMC01=zd.ZDMC00,CYQK01=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=2 then update ZY_BASYXX set CYZD02=zd.ZDM000,ZDMC02=zd.ZDMC00,CYQK02=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=3 then update ZY_BASYXX set CYZD03=zd.ZDM000,ZDMC03=zd.ZDMC00,CYQK03=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=4 then update ZY_BASYXX set CYZD04=zd.ZDM000,ZDMC04=zd.ZDMC00,CYQK04=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=5 then update ZY_BASYXX set CYZD05=zd.ZDM000,ZDMC05=zd.ZDMC00,CYQK05=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=6 then update ZY_BASYXX set CYZD06=zd.ZDM000,ZDMC06=zd.ZDMC00,CYQK06=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=7 then update ZY_BASYXX set CYZD07=zd.ZDM000,ZDMC07=zd.ZDMC00,CYQK07=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=8 then update ZY_BASYXX set CYZD08=zd.ZDM000,ZDMC08=zd.ZDMC00,CYQK08=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=9 then update ZY_BASYXX set CYZD09=zd.ZDM000,ZDMC09=zd.ZDMC00,CYQK09=zd.DM0000 where ZYID00=Pzyid00; elsif zd.ROW000=10 then update ZY_BASYXX set CYZD10=zd.ZDM000,ZDMC10=zd.ZDMC00,CYQK10=zd.DM0000 where ZYID00=Pzyid00; end if; end loop; update ZY_BASYXX set CYQK01=nvl(CYQK01,'5'), CYQK02=nvl(CYQK02,'5'), CYQK03=decode(CYZD03,null,null,nvl(CYQK03,'5')), CYQK04=decode(CYZD04,null,null,nvl(CYQK04,'5')), CYQK05=decode(CYZD05,null,null,nvl(CYQK05,'5')), CYQK06=decode(CYZD06,null,null,nvl(CYQK06,'5')), CYQK07=decode(CYZD07,null,null,nvl(CYQK07,'5')), CYQK08=decode(CYZD08,null,null,nvl(CYQK08,'5')), CYQK09=decode(CYZD09,null,null,nvl(CYQK09,'5')), CYQK10=decode(CYZD10,null,null,nvl(CYQK10,'5')) where ZYID00=Pzyid00; end; if Pcommit = 'Y' then commit; end if; exception when others then raise_application_error(-20020, nvl(sqlerrm, '原因不明出错！*')); end;