-- Start of DDL Script for Procedure SD_HOSPITAL.SP_ZY_ZDDB00 -- Generated 06-6月-2024 09:35:33 from SD_HOSPITAL@sayy CREATE OR REPLACE PROCEDURE sp_zy_zddb00( Pzyid00 in number , --住院id Pczy000 in number , --输入：操作员编码 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) -- modification history -- Person Date Comments -- zhanghr 2013.06.11 create -- jlg 2013.08.12 自动担保计算修改，加入医保预结算的处理 -- jlg 2013.08.14 增加Pzxcgbz的处理 -- jlg 2013.08.16 增加对于YBTZJE的处理 -- qks 2013.10.11 医保预结算统筹支付 >= 医保病人透支金额,那么透支金额自动清0 -- qks 2016.01.06 择日住院病人不进行 住院自动担保计算 by ZYSF9-20160105-001 -- xuzw 2016.02.03 增加参数ZY_DBSZJE设置担保金额 ZYSF9-20160202-001 -- jlg 2016.11.16 处理医保病人自动担保金额按参数ZY_DBSZJE来取值 ZYSF9-20161116-001 -- jlg 2016.12.04 处理原担保过程参数ZY_DBSZJE对于自费病人的无效的问题 ZYSF9-20161204-001 -- jlg 2017.03.20 对于省属付费单病种病人的自动担保的计算 ZYSF9-20170320-001 -- jlg 2017.03.28 修改进入付费单病种的，自动担保就取消了 ZYSF9-20170313-001 -- jlg 2017.09.26 增加不允许自动担保的特殊属性的处理 ZYSF9-20170925-003 -- jlg 2018.04.23 增加当ICZTMC='停保'时不能进行自动担保的问题 ZYSF9-20180422-002 -- qks 2018.06.13 用微信、支付宝交款也要放大担保 ZYSF9-20180613-002 -- jlg 2018.08.17 增加BM_BMBM00.ZFQFJE(自费住院病人自动设置欠费金额(本科室或本病区))所设置的自动欠费金额的处理 ZYSF9-20180312-003 -- pwt 2019.03.27 增加参数精准扶贫病人设置的自动担保金额 ZYSF9-20190325-001 -- liwm 2019.06.24 增加省老年院内记账病人自动担保 ZYSF9-20190624-001 -- liwm 2019.08.08 ZY_FYBBRSFZDDB并添加判断分支条件设置不严谨，导致医保病人按比例自动担保无法正常使用 ZYSF9-20190809-001 -- jlg 2020.02.04 增加特需医疗服务费用TXYLFW的处理 ZYSF9-20200204-001 -- jlg 2020.08.07 增加先诊疗后付费对象（又称城乡特困人员）的自动担保处理 ZYSF9-20200803-005 -- linzetao 2020.11.19 增加分中心押金放大比例和押金放大上限的判断 ZYSF9-20201027-001 -- linzetao 2021.10.13 增加当ICZTMC=*时为有效状态 ZYSF9-20211013-001 -- linzetao 2024.04.16 调整先诊疗后付费自动担保判断条件, 增加医疗救助对象条件 ZYSF9-20240416-002 -- --------- ---------- -------------------------------------------------------------------------------- as ls_BRID00 BM_BRXXB0.BRID00%type; --病人ID ls_NDTCXE BM_BRXXB0.NDTCXE%type; --年度统筹限额 ls_YJFDBL BM_YBBRLB.YJFDBL%type; --医保病人预交金放大比例 ls_DBJE00 ZY_BRDB00.DBJE00%type; --担保金额 ls_YBZFBL BM_YBBRLB.YBZFBL%type; --医保支付比例 ls_YJFDSX BM_YBBRLB.YJFDSX%type; --预交金放大上限 ls_FBBH00 BM_YBBRLB.FBBH00%type; --费别编号 ls_YBZDDB XT_XTCS00.VALUE0%type; --医保病人按比例允许欠费 ls_WJYJJE ZY_BRJFB0.JFJE00%type; --未结算交费金额 ls_YJZFJE ZY_BRJFB0.JFJE00%type; --医保预结算--自付金额 ls_YBTZJE BM_YBBRLB.YBTZJE%type; --医保透支金额 ls_YJTCJE ZY_BRZHXX.YJTCJE%type; --医保预结算--统筹支付 ls_ZRZYBZ ZY_BRXXB0.ZRZYBZ%type; --择日住院标识：0非择日,1择日住院,2择日转正式住院 ls_DBSZJE ZY_BRDB00.DBJE00%type; --医保病人自动担保设置金额 ls_ZFQFJE ZY_BRDB00.DBJE00%type; --自费病人自动欠费设置金额 ls_FPSZJE ZY_BRDB00.DBJE00%type; --精准扶贫病人自动担保设置金额 ls_FFBZBM ZY_BRXXB0.FFBZBM%type; --付费单病种编码 ls_DBZCWF ZY_BRZHXX.DBZCWF%type; --付费单病种超标床位费（暂存） ls_DBZHCF ZY_BRZHXX.DBZHCF%type; --付费单病种除外耗材费（暂存） ls_TXYLFW ZY_BRZHXX.TXYLFW%type; --付费单病种特需医疗服务费（暂存） ls_BRQTLB XT_XTCS00.VALUE0%type; --不允许自动担保的病人其它类别(TSSXMC) ls_TSSXMC BM_BRXXB0.TSSXMC%type; --病人特殊属性(其它类别) ls_ICZTMC BM_BRXXB0.ICZTMC%type; --医保IC状态名称 ls_DQKS00 ZY_BRXXB0.DQKS00%type; --病人当前入住科室 ls_DQBQ00 ZY_BRXXB0.DQBQ00%type; --病人当前入住病区 ls_SFYLJZ ZY_BRXXB0.SFYLJZ%type; --是否医疗救助 ls_BMQFJE BM_BMBM00.ZFQFJE%type; --自费住院病人自动设置欠费金额(本科室或本病区) ls_SFJZFP ZY_BRXXB0.SFJZFP%type; --是否精准扶贫 ls_temp00 varchar2(20); Ecustom exception; ls_ZDDBSZ XT_XTCS00.VALUE0%type; --记账病人自动担保的金额 ls_BYZD01 BM_BRXXB0.BYZD01%type; --是否先诊疗后付费对象（是否城乡特困人员），取值是或否 ls_TKQFJE ZY_BRDB00.DBJE00%type; --先诊疗后付费对象（又称城乡特困人员）设置的自动担保金额 ls_FZXYJFDBL IC_YBBRLB.YJFDBL%type; --医保分中心病人预交金放大比例 ls_FZXYJFDSX IC_YBBRLB.YJFDSX%type; --医保分中心预交金放大上限 begin Pzxcgbz :=0; Ptsxx00 :=nvl(to_char(Pzyid00),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null'); SP_TransLog(sysdate,'SP_ZY_ZDDB00',Pczy000,null,Ptsxx00); --医保病人按比例允许欠费 select nvl(max(VALUE0),'N') into ls_YBZDDB from XT_XTCS00 where NAME00 ='ZY_YBABLYXQF'; --医保病人自动担保设置金额 select nvl(max(VALUE0),0) into ls_DBSZJE from XT_XTCS00 where NAME00 ='ZY_YBBRZDQFSZJE'; --自费病人自动担保设置金额 select nvl(max(VALUE0),0) into ls_ZFQFJE from XT_XTCS00 where NAME00 ='ZY_ZFBRZDQFSZJE'; --不允许自动担保的病人其它类别(TSSXMC)，默认为null(在ZY_YBBRZDQFSZJE,ZY_ZFBRZDQFSZJE两个参数开启情况下的特殊处理) select nvl(max(VALUE0),'0') into ls_BRQTLB from XT_XTCS00 where NAME00 ='ZY_BYXZDDBDBRQTLB'; --精准扶贫病人自动担保设置金额 select nvl(max(VALUE0),0) into ls_temp00 from XT_XTCS00 where NAME00 ='ZY_JZFPZDQFSZJE'; --省老年记账病人自动担保 select nvl(max(VALUE0),'N') into ls_ZDDBSZ from XT_XTCS00 where NAME00 = 'ZY_FYBBRSFZDDB'; ls_ZDDBSZ := ls_ZDDBSZ||','; --先诊疗后付费对象（又称城乡特困人员）设置的自动担保金额 select nvl(max(VALUE0),'0') into ls_TKQFJE from XT_XTCS00 where NAME00='ZY_XZLHFFDXZDQFSZJE'; if ls_YBZDDB<>'Y' then Pzxcgbz :=1; return; end if; --获取病人相关信息 select c.YJFDBL,c.YBZFBL,c.YJFDSX,c.YBTZJE,d.NDTCXE,a.BRID00,a.FBBH00,a.ZRZYBZ,a.FFBZBM,d.TSSXMC,nvl(d.ICZTMC,'有效'), a.DQKS00,a.DQBQ00,a.SFJZFP,d.BYZD01,b.YJFDBL,b.YJFDSX,nvl(a.SFYLJZ,'N') into ls_YJFDBL,ls_YBZFBL,ls_YJFDSX,ls_YBTZJE,ls_NDTCXE,ls_BRID00,ls_FBBH00,ls_ZRZYBZ,ls_FFBZBM,ls_TSSXMC,ls_ICZTMC, ls_DQKS00,ls_DQBQ00,ls_SFJZFP,ls_BYZD01,ls_FZXYJFDBL,ls_FZXYJFDSX,ls_SFYLJZ from ZY_BRXXB0 A,IC_YBBRLB B,BM_YBBRLB C,BM_BRXXB0 d where A.FBBH00 = B.FBBH00 and A.YBLB00 = B.YBLB00 and a.BRID00 = d.BRID00 and A.ZYID00 = Pzyid00 and a.FBBH00 = c.FBBH00 and a.YBZXLB = c.YBZXLB and a.YBBRLB = c.YBBRLB; if nvl(ls_ZRZYBZ,'0')='1' then Pzxcgbz :=1; return; end if; --如果病人所属分中心有设置预交金放大比例, 则以分中心的放大比例和放大上限覆盖费别的比例和上限 if ls_FZXYJFDBL > 0 then ls_YJFDBL := ls_FZXYJFDBL; ls_YJFDSX := ls_FZXYJFDSX; end if; --自费病人自动欠费金额获取的优先级别：科室设置>病区设置>参数设置 if ls_FBBH00 = 1 then --自费病人 --优先从科室设置的自费病人自动欠费金额获取 select nvl(max(ZFQFJE),0) into ls_BMQFJE from BM_BMBM00 where BMBH00 = ls_DQKS00; if ls_BMQFJE = 0 then --科室没有设置，再从病区设置的获取自费病人自动欠费金额 select nvl(max(ZFQFJE),0) into ls_BMQFJE from BM_BMBM00 where BMBH00 = ls_DQBQ00; end if; if ls_BMQFJE > 0 then ls_ZFQFJE := ls_BMQFJE; end if; end if; begin ls_FPSZJE := to_number(ls_temp00); exception when others then ls_FPSZJE := 0; end; if (ls_FBBH00 = 1 and ls_ZFQFJE >0 and ls_TSSXMC<>ls_BRQTLB) then --自费病人有设置参数，则自动产生欠费金额 update ZY_BRDB00 set DBJE00=ls_ZFQFJE,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='Y',DBYY00='自动欠费' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; if sql%notfound then insert into ZY_BRDB00 (BRDBID,YGBH00,JFLBID,BRID00,DBJE00,DBRQ00,DBSJ00,CZY000, CZYXM0,DBJSRQ,DBJSSJ,DBYY00,SSLB00,SFYX00,ZYID00) select SQ_ZY_BRDB00_BRDBID.nextval,0,2,ls_BRID00,ls_ZFQFJE,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),Pczy000, ZWXM00,'20991231','00:00:00','自动欠费','3','Y',Pzyid00 from BM_YGBM00 where YGBH00=Pczy000; end if; elsif (ls_FBBH00=3 and (ls_BYZD01 = '是' or ls_SFYLJZ in ('1','Y')) and ls_TKQFJE >0) then --先诊疗后付费对象（又称城乡特困人员）设置的自动担保金额，优先级设置更高点 update ZY_BRDB00 set DBJE00=ls_TKQFJE,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='Y',DBYY00='自动欠费' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; if sql%notfound then insert into ZY_BRDB00 (BRDBID,YGBH00,JFLBID,BRID00,DBJE00,DBRQ00,DBSJ00,CZY000, CZYXM0,DBJSRQ,DBJSSJ,DBYY00,SSLB00,SFYX00,ZYID00) select SQ_ZY_BRDB00_BRDBID.nextval,0,2,ls_BRID00,ls_TKQFJE,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),Pczy000, ZWXM00,'20991231','00:00:00','自动欠费','3','Y',Pzyid00 from BM_YGBM00 where YGBH00=Pczy000; end if; elsif (ls_FBBH00=3 and ls_SFJZFP = '1' and ls_FPSZJE > 0) then --精准扶贫担保金额优先级大于医保病人担保金额 update ZY_BRDB00 set DBJE00=ls_FPSZJE,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='Y',DBYY00='自动欠费' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; if sql%notfound then insert into ZY_BRDB00 (BRDBID,YGBH00,JFLBID,BRID00,DBJE00,DBRQ00,DBSJ00,CZY000, CZYXM0,DBJSRQ,DBJSSJ,DBYY00,SSLB00,SFYX00,ZYID00) select SQ_ZY_BRDB00_BRDBID.nextval,0,2,ls_BRID00,ls_FPSZJE,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),Pczy000, ZWXM00,'20991231','00:00:00','自动欠费','3','Y',Pzyid00 from BM_YGBM00 where YGBH00=Pczy000; end if; --elsif ( (ls_FBBH00=3 or ( ls_ZDDBSZ<>'N,' and instr(ls_ZDDBSZ, ','||ls_FBBH00||',') > 0 ) ) and ls_YBZFBL=0 and ls_TSSXMC<>ls_BRQTLB and ls_ICZTMC<>'停保') then --医保支付比例=0 -- elsif ( (ls_FBBH00=3 or ( ls_ZDDBSZ<>'N,' and instr(ls_ZDDBSZ, ','||ls_FBBH00||',') > 0 ) ) and ls_YBZFBL=0 and ls_TSSXMC<>ls_BRQTLB and trim(ls_ICZTMC) in ('有效','正常','电力托管','*')) then --医保支付比例=0 elsif ( (ls_FBBH00=3 and ( ls_ZDDBSZ<>'N,' and instr(ls_ZDDBSZ, ','||ls_FBBH00||',') > 0 ) ) and ls_YBZFBL=0 and ls_TSSXMC<>ls_BRQTLB and trim(ls_ICZTMC) in ('有效','正常','电力托管','*')) then --医保支付比例=0 --只有ICZTMC等于'有效'或'正常'或'电力托管'才进行担保 --医保病人自动产生欠费记录 update ZY_BRDB00 set DBJE00=ls_DBSZJE,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='Y',DBYY00='自动欠费' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; if sql%notfound then insert into ZY_BRDB00 (BRDBID,YGBH00,JFLBID,BRID00,DBJE00,DBRQ00,DBSJ00,CZY000, CZYXM0,DBJSRQ,DBJSSJ,DBYY00,SSLB00,SFYX00,ZYID00) select SQ_ZY_BRDB00_BRDBID.nextval,0,2,ls_BRID00,ls_DBSZJE,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),Pczy000, ZWXM00,'20991231','00:00:00','自动欠费','3','Y',Pzyid00 from BM_YGBM00 where YGBH00=Pczy000; end if; --elsif (ls_FBBH00=3 and ls_YJFDBL>0 and ls_TSSXMC<>ls_BRQTLB and ls_ICZTMC<>'停保') then --押金放大比例>0 elsif (ls_FBBH00=3 and ls_YJFDBL>0 and ls_TSSXMC<>ls_BRQTLB and trim(ls_ICZTMC) in ('有效','正常','电力托管','*')) then --押金放大比例>0 --只有ICZTMC等于'有效'或'正常'或'电力托管'才进行押金放大 --获取未结算的预交金总额 select nvl(sum(JFJE00),0) into ls_WJYJJE from ZY_BRJFB0 where ZYID00=Pzyid00 and JFLBID = 2 and ZFFS00 in (1,2,8,18,24,25) and JZDH00=0 and JFBZ00<>'2'; --获取预结算自付金额，预结算统筹金额 select nvl(sum(YBYJS0-YBYZF0),0),nvl(sum(YJTCJE),0),nvl(sum(DBZCWF),0),nvl(sum(DBZHCF),0),nvl(sum(TXYLFW),0) into ls_YJZFJE,ls_YJTCJE,ls_DBZCWF,ls_DBZHCF,ls_TXYLFW from ZY_BRZHXX where ZYID00 = Pzyid00 and JFLBID = 2 and SFDJ00 = '0'; --如果 医保预结算统筹支付 >= 医保病人透支金额,那么透支金额自动清0 qks if ls_YJTCJE-ls_YBTZJE>=0 then ls_YBTZJE := 0; end if; if (ls_FFBZBM is not null) then --进入单病种后，自动担保金额就取消了 ls_DBJE00:= 0; elsif ls_WJYJJE - ls_YJZFJE >0 then --自动担保金额 = (未结算的预交总金额-预结算的自付金额)*预交金放大比例 + 医保透支金额 jlg ls_DBJE00:= (ls_WJYJJE - ls_YJZFJE)*ls_YJFDBL + ls_YBTZJE; else ls_DBJE00:= ls_YBTZJE; end if; --自动担保金额不能超过押金放大上限 ls_DBJE00:=least(ls_DBJE00,ls_YJFDSX); --如果有年度限额，则自动担保金额还不能超过年度限额 if ls_NDTCXE>0 then ls_DBJE00:=least(ls_DBJE00,ls_NDTCXE); end if; --医保病人自动产生欠费记录 update ZY_BRDB00 set DBJE00=ls_DBJE00,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='Y',DBYY00='押金放大欠费' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; if sql%notfound and ls_DBJE00<>0 then insert into ZY_BRDB00 (BRDBID,YGBH00,JFLBID,BRID00,DBJE00,DBRQ00,DBSJ00,CZY000, CZYXM0,DBJSRQ,DBJSSJ,DBYY00,SSLB00,SFYX00,ZYID00) select SQ_ZY_BRDB00_BRDBID.nextval,0,2,ls_BRID00,ls_DBJE00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),Pczy000, ZWXM00,'20991231','00:00:00','押金放大欠费','3','Y',Pzyid00 from BM_YGBM00 where YGBH00=Pczy000; end if; else --非以上情况，则自动担保金额清零 update ZY_BRDB00 set DBJE00=0,XGR000=Pczy000,XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'),SFYX00='N' where ZYID00=Pzyid00 and DBJSRQ='20991231' and SSLB00='3'; end if; --把过期的担保设为无效 update ZY_BRDB00 set SFYX00 = 'N' where ZYID00=Pzyid00 and SFYX00='Y' and nvl(DBJSRQ,to_char(sysdate,'YYYYMMDD'))<=to_char(sysdate,'YYYYMMDD') and nvl(DBJSRQ,to_char(sysdate,'YYYYMMDD'))||nvl(DBJSSJ,to_char(sysdate,'HH24:MI:SS')) < to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24:MI:SS'); update ZY_BRZHXX set YXQFJE=(select nvl(sum(DBJE00),0) from ZY_BRDB00 where ZYID00=Pzyid00 and SFYX00='Y') where ZYID00=Pzyid00; Pzxcgbz :=1; if Pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm||','||Ptsxx00,1,120)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,120)); when others then raise_application_error(-20266,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Ptsxx00,1,120)); end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_ZY_ZDDB00 --modify by 2004.10.14 11:45 PROMPT 177.--病人记账项目 CREATE OR REPLACE VIEW VW_BQ_BRJZXM --2007-07-25 qks 增加另收材料字段:LSCL00 --2009-05-21 chenqw XMMC00加入备注等内容，有参数：BQ_JZXMSFXSBZ --2016.11.19 jlg YJ9-20161109-002 --2017-05-27 linyx 增加yexm00 显示 BQHS9.0-20170525-001 --2018-10-30 linyx 增加收费标准等级字段显示，修改查询的bm_yysfxm表为VW_BQ_YYSFXM_DJBZ for BQHS9.0-20181024-001 --2021-09-20 dongxb 增加国家标准医保编码，国家标准医保名称 for ZYHS11-20210918-001、BQHS9.0-20210918-001 --2022-05-06 dongxb 增加频次ID，频次名称字段 for ZYHS11-20220420-001、BQHS9.0-20220808-004 --2022-05-09 dongxb 增加业务开始日期，业务结束日期字段 for ZYHS11-20220425-002、BQHS9.0-20220808-005 --2023-11-10 lisuzhu 床位显示最新的 for BQHS9.0-20231110-001 --2023-12-26 lisuzhu 增加 ZFFSBZ for BQHS9.0-20231214-015 --2024-05-04 lisuzhu 增加 SCCJ00 for BQHS9.0-20240502-009 AS SELECT J.ID0000,J.ZYH000,J.ZYID00,J.CLBZ00,Z.RYCWH0 AS CH0000,J.XMLB00,J.JFLBID,J.GJBM00, J.SFXMID, decode((select trim(value0) from xt_xtcs00 where name00 = 'BQ_JZXMSFXSBZ'),'Y', SUBSTR(J.XMMC00||DECODE(S.JSXM00,'2',decode(S.GG0000,null,null,'['||S.GG0000||']'), decode(S.BZ0000,null,null,'['||S.bz0000||']')),0,120 ),'N',J.XMMC00,J.XMMC00) XMMC00 ,J.CS0000,J.DW0000,J.SFJE00,J.ZFJE00,J.JZJE00,J.GFJE00,--J.XMMC00 J.LRRXM0,J.LRRQ00,J.LRSJ00,J.QSRQ00,J.GZRQ00,J.GZSJ00,J.TZRQ00,J.TZSJ00, J.TZRXM0,J.BZ0000,J.JJSFBZ,J.LYBZ00, S.FDBL00 , --浮动比率,默认值为0 S.SFXX00 , --收费下限,收费金额最小值，默认值为0 S.SFSX00 , --收费上限,收费金额最大值，Null为自定义金额 S.JSXM00 , --加收项目标志 S.MZFPID , --门诊发票项目 对应的门诊发票项目唯一号 S.ZYFPID , --住院发票项目 对应的住院发票项目唯一号 B.ZYZFBL , --自付比率 B.FBBH00 , --费别编号 B.YBLB00 , --医保项目类别 B.YBZXLB , --医保中心类别 B.YBBRLB , --医保病人类别 B.SFYBXM , --是否医保项目 decode(B.YBLB00,'0','医保','1','部分','自付') YBLBMC, --医保类别名称 S.LSCL00, --另收材料 J.ZXKS00 , --执行科室 (select BMMC00 FROM BM_BMBM00 WHERE BMBH00=J.ZXKS00) ZXKSMC, --执行科室名称 J.YEXM00, S.SFBZDJ, nvl(J.GJBZBM,S.GJBZBM) GJBZBM, --国家标准编码 S.GJBZMC, J.PCID00, J.PCMC00, J.YWKSRQ, J.YWJSRQ, J.ZFFSBZ, S.SCCJ00 FROM BQ_JZXM00 J,ZY_BRXXB0 Z,BM_BRXXB0 X,IC_YBBRLB Y,VW_BQ_YYSFXM_DJBZ S,VW_BM_YBSFDY B WHERE J.ZYID00=Z.ZYID00 AND Z.BRID00=X.BRID00 AND X.YBLB00=Y.YBLB00 AND X.FBBH00=Y.FBBH00 AND X.YBBRLB=B.YBBRLB AND Y.YBZXLB=B.YBZXLB AND B.SFYP00='N' AND X.FBBH00=B.FBBH00 AND J.SFXMID=B.SFXMID AND S.SFXMID=B.SFXMID /