-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SF_MZCFQD_TEMPTJ -- Generated 24-十月-2014 11:02:45 from SD_HOSPITAL@SD10G CREATE OR REPLACE PROCEDURE sp_sf_mzcfqd_temptj ( as_GHH000 in char, --挂号号 as_JZDH00 in char, --结算单号 ad_ID0000 out number, --临时表的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_ZFXSBL char(1); --自付显示比例,'0':不显示,'1':显示自付百分比 '2' 显示甲乙类 SF_AZLXM0 char(1); SF_AZLXM1 char(1); ls_FBBH00 SF_BRXXB0.FBBH00%TYPE; ls_JYSFDM SF_FYMX00.XMBH00%TYPE; --中药代煎费的收费代码 ls_ZFHJCE SF_JZB000.HJJE00%TYPE; --保存合计金额与自付金额的差额 ls_CFLSH0 YF_MZCF00.CFLSH0%TYPE; --处方流水号 ls_YPDLBH YF_MZCF00.YPDLBH%TYPE; --药品类别编码 ls_YPDLBH_1 YF_MZCF00.YPDLBH%TYPE; --药品类别编码 ls_ZYTS00 YF_MZCF00.ZYTS00%TYPE; --中药帖数 ls_CFZJE0 YF_MZCF00.CFZJE0%TYPE; --处方总金额 ls_YSXM00 YF_MZCF00.YSXM00%TYPE; --医生姓名 ls_ZWXM00 BM_YGBM00.ZWXM00%TYPE; --操作员的中文姓名 ls_CKMC00 BM_CKBM00.CKMC00%TYPE; --发药窗口名称 ls_DCBZ00 YJ_YW0000.DCBZ00%TYPE; ls_YZCFID YJ_YW0000.YZCFID%TYPE; ls_ZLXMID YJ_YW0000.ZLXMID%TYPE; ls_BRID00 SF_BRXXB0.BRID00%TYPE; ls_hjje00 sf_fymx00.hjje00%type; ls_xkh000 BM_YGBM00.xkh000%type; ls_sfksly varchar2(20); ls_JZFBBH SF_JZB000.FBBH00%TYPE; --结算费别编号 ls_ZXKSMC BM_BMBM00.BMMC00%TYPE; --执行部门名称 ls_HJJE01 sf_fymx00.hjje00%type; ls_HJJE02 sf_fymx00.hjje00%type; ls_GHLBMC BM_GHLBB0.LBMC00%TYPE; ls_GHLB00 BM_GHLBB0.LBBH00%TYPE; ls_XMDJ01 YJ_YWJJ00.JJJE00%TYPE; ld_ZXKSBH BM_BMBM00.BMBH00%type; ls_SFDW00 char(1); ls_lyks00 yf_mzcf00.lyks00%type; cursor CUR_SF_MZCFQD_CFLSH0 is select B.CFLSH0,B.YPDLBH,B.ZYTS00,B.CFZJE0,B.YSXM00,D.ZWXM00,d.XKH000,b.lyks00 from YF_MZCF00 B,SF_BRFY00 C,BM_YGBM00 D --加LYKS00 where B.DJH000 = C.DJH000 and B.CZY000 = D.YGBH00 and C.JZDH00 = as_JZDH00 and (B.GHH000 = as_GHH000 or as_GHH000 = '0') and exists (select 1 from YF_MZCFMX where CFLSH0 = B.CFLSH0) and B.CFZT00 in ('1','2') --已收费过的处方 order by B.YPDLBH,B.CFLSH0; cursor CUR_SF_MZCFQD_ZYDJF0 is --中药代煎费对应的费用 select * from YF_MZCFMX F where CFLSH0 = ls_CFLSH0 and CXBZ00 = 'Z' and SFZBY0 = 'N'; cursor CUR_SF_MZCFQD_TEMPTJ is select A.YPMC00,A.YPZSL0,A.CFDW00,round(A.YPZSL0*A.LSDJ00,2) CFZJE0,a.ZFBL00,a.CSL000,a.JLDW00, (select YBLXBH from BM_YD0000 where YPNM00=A.YPNM00) YBLXBH -- 01 基本类 02 控制类 03 自费类 from YF_MZCFMX A,YF_MZCF00 B where A.CFLSH0 = B.CFLSH0 and (B.CFLSH0 = ls_CFLSH0 or B.BCCFH0 = ls_CFLSH0) and B.YPDLBH = 2 and (B.GHH000 = as_GHH000 or as_GHH000 = '0') and B.CFZT00 in ('1','2','6') and A.SFZBY0 = 'N' and B.DJH000 in (select DJH000 from SF_BRFY00 C where 1 = 1 and (C.MZH000 = as_GHH000 or as_GHH000 = '0') and C.JZDH00 = as_JZDH00) order by a.ZB0000,a.CFID00; cursor CUR_SF_MZCFQD_FYMXTJ is select A.SFXMID,A.XMMC00,'',A.DW0000,d.JJJE00,d.ZFBL00,sum(d.SL0000) XMSL00, sum(d.SL0000*d.SFCS00*d.JJJE00) HJJE00,k.sfdjh0,k.YJDJH0 from YJ_YW0000 k,BM_YYSFXM a,BM_ZLXMLB c,BM_ZLZD00 g,YJ_YWJJ00 d where k.ZLXMID=g.ZLXMID and g.LBBH00=c.LBBH00 and k.YJDJH0=d.YJDJH0 and c.LBMC00 in('治疗','手术','化验') and a.SFXMID=d.SFXMID and k.MZZYBZ='0' --and k.SFDJH0 in( select b.DJH000 from SF_BRFY00 b where B.JZDH00 = as_JZDH00 --and (B.MZH000 = as_GHH000 or as_GHH000 = '0')) and k.YJDJH0 in( select c.YJDJH0 from SF_BRFY00 b,SF_FYMX00 c where b.DJH000=c.DJH000 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0')) group by A.SFXMID,A.XMMC00,A.DW0000,d.JJJE00,d.ZFBL00,k.sfdjh0,k.YJDJH0 having sum(d.SL0000) <> 0; cursor CUR_SF_MZCFQD_FYMXTJ_01 is --modified by jlg -- select k.zlxmid,k.ZLXMJC||decode(K.bz0000,'','','/'||K.bz0000),'',k.DW0000,round(k.ZJE000/K.SL0000,2),sum(K.SL0000) XMSL00, select h.BMMC00,k.ZLXMID,decode(c.LBMC00,'化验',k.ZLXMJC||decode(g.BZ0000,'','','/'||g.bz0000),k.ZLXMJC||decode(K.BZ0000,'',decode(K.BWMC00,'','','/'||K.BWMC00),'/'||K.bz0000)),'',k.DW0000,round(k.ZJE000/K.SL0000,2),sum(K.SL0000) XMSL00, sum(K.ZJE000) HJJE00,decode(ls_SFDW00,'Y',(select distinct c.DJH000 from SF_BRFY00 b,SF_FYMX00 c where b.DJH000=c.DJH000 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and c.YJDJH0=k.YJDJH0 and c.BEIZHU is null),k.sfdjh0) sfdjh0,k.YJDJH0 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 g,BM_BMBM00 h where k.ZLXMID=g.ZLXMID and g.LBBH00=c.LBBH00 and k.YJKSBH=h.BMBH00 -- and c.LBMC00 in('治疗','手术') and c.LBMC00 in('检查','治疗','手术','化验') and k.MZZYBZ='0' and k.YJKSBH+0=ld_ZXKSBH --and k.SFDJH0 in( select b.DJH000 from SF_BRFY00 b where B.JZDH00 = as_JZDH00 --and (B.MZH000 = as_GHH000 or as_GHH000 = '0')) and YJDJH0 in( select c.YJDJH0 from SF_BRFY00 b,SF_FYMX00 c where b.DJH000=c.DJH000 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0')) -- group by k.zlxmid,k.ZLXMJC||decode(K.bz0000,'','','/'||K.bz0000),k.DW0000,round(k.ZJE000/K.SL0000,2) group by h.BMMC00,k.ZLXMID,decode(c.LBMC00,'化验',k.ZLXMJC||decode(g.BZ0000,'','','/'||g.BZ0000),k.ZLXMJC||decode(K.BZ0000,'',decode(K.BWMC00,'','','/'||K.BWMC00),'/'||K.bz0000)),k.DW0000,round(k.ZJE000/K.SL0000,2),k.sfdjh0,k.YJDJH0 having sum(k.SL0000) <> 0; cursor CUR_ZXKS is select distinct E.BMMC00,F.KSWZ00,E.BMBH00 from YJ_YW0000 A,BM_BMBM00 E,BM_KSFB00 F,BM_ZLXMLB G,BM_ZLZD00 H where A.YJKSBH=E.BMBH00 and E.BMBH00=F.KSBH00(+) and A.ZLXMID=H.ZLXMID and G.LBMC00 in ('检查','治疗','手术','化验') and A.MZZYBZ='0' and G.LBBH00=H.LBBH00 and YJDJH0 in( select c.YJDJH0 from SF_BRFY00 b,SF_FYMX00 c where b.DJH000=c.DJH000 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0')) order by E.BMMC00; lv_MZCFMX YF_MZCFMX%rowtype; lv_FYMX00 SF_FYMX00%rowtype; lv_MZCFQD BM_MZCFQD_TEMP00%rowtype; ls_DYID00 BM_MZCFQD_TEMP00.DYID00%type; ls_fydlxh YF_CFDL00.FYDLXH%type; ls_COUNT0 number(12,2); ls_COUNT1 number(12,2); ls_COUNT3 number(12,2); ls_COUNT4 number(12,2); ls_COUNT5 number(12,2); ls_COUNT6 number(12,2); ls_COUNT7 number(12,2); ls_XLH000 number(12,2); ls_ghrq00 char(20); ls_ghksmc char(20); ls_jzysxm char(20); ls_ickh00 char(20); ls_xm0000 char(30); ls_xb0000 char(10); ls_fbmc00 char(20); ls_csrq00 char(10); --ls_GHDLX0 VARCHAR2(30); ls_yyid00 XT_YYXX00.YYID00%type; ls_ts0000 varchar2(50); ls_XJSFDYH XT_XTCS00.VALUE0%type; --小计是否显示在第一行 ls_mc0000 varchar2(100); ls_YBLXBH BM_YYSFXM.YBLXBH%type; -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2008.04.15 由清单显示医生输入的备注(yj_yw0000.bz0000)改为后台维护的诊疗项目的备注(bm_zlzd00.bz0000) MZSF-20080114-002 -- jlg 2008.05.17 '科室领药' 改为 '治疗用药' -- qks 2008.06.06 增加结算费别编号ls_JZFBBH MZSF-20080605-001 -- qks 2008.07.28 收费清单增加一个打印显示栏，“请XX时间内取药”，XX=打印时间+30分钟 MZSF-20080723-004 -- qks 2008.10.27 CUR_SF_MZCFQD_FYMXTJ_01要求增加检查类诊疗项目打印，同时要打印出[部位]名称 MZSF-20081027-001 -- qks 2009.03.09 增加bz0001写入空格 MZSF-20090309-003 -- qks 2009.03.16 中药处方单位改为剂量单位 MZSF-20090313-003 -- qks 2009.04.28 检查/治疗/手术/化验项目后增加“执行科室” -- qks 2009.06.19 取消2008.07.28修改内容 MZSF-20090617-004 --xuzw 2009.09.18 问题：非药品查询部分会将住院费用统计在内 解决：过滤掉住院部分 加入k.mzzybz='0' -- JXQ 2009.10.23 门诊清单小计金额是否显示在第一行， 处理：加入SF_MZQDXJSFXSZDYH参数进行控制，默认为'Y' -- zhr 2010.05.21 清单可选择显示自付百分比或者甲乙类 -- qks 2011.03.06 广东省要求按结算时的费别打印自付比例 -- qks 2011.06.24 申请退费未确认记录，再打印清单时会显示该申请退费记录 MZSF-20110624-001 -- qks 2011.11.10 珠海医院：窗口名称+取药+排队号 -- qks 2011.12.04 珠海医院："/类别"去掉, "/自费"改为' z',"/乙类"改为' k',"/甲类"去掉; 参数SF_MZCFQDMXXS值为3 -- qks 2011.12.08 珠海医院：诊疗名称去掉 '科-' MZSF-20111209-003 4. -- qks 2012.01.07 珠海医院：处方清单上不要重复打印"就诊时间 姓名 就诊科室"这一行。MZSF-20120107-002 2. -- linzy 2012.04.13 珠海医院：清单显示医保类别按具体药品和收费项目。 -- jinfl 2012.05.30 定额项目取原单价 MZSF-20120530-001 -- jinfl 2012.06.04 单位体检病人增加限制挂号类别 MZSF-20120605-002 -- jinfl 2012.06.06 个人体检项目打折后，处方清单单价还是不对。原因是单价取的是yj_yw0000表中的中ZJE000/SL0000 MZSF-20120606-002 -- jinfl 2012.06.14 个人体检项目打折后，处方清单的单价乘以收费次数 MZSF-20120614-004 -- jinfl 2012.07.19 限额单位部分的处方清单统计 MZSF-20120712-004 -- linzy 2014.03.10 打印处方清单的时候要求打印出执行地点，并按执行地点把项目分好类. by MZSF-20140219-002 -- linzy 2014.04.23 请到与西药房取号合并一起显示粗体 BY MZSF-20140417-002 -- ------- ------------ ---------------------------------------------------------------------------------------------- begin ls_XLH000 := 1; ls_COUNT0 := 0; ls_COUNT4 := 0; ls_COUNT5 := 0; ls_COUNT6 := 0; ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_DYID00 := 1; ls_ghrq00 :=''; ls_ghksmc :=''; ls_jzysxm :=''; ls_ickh00 :=''; ls_xm0000 :=''; ls_xb0000 :=''; ls_fbmc00 :=''; ls_csrq00 :=''; ls_ts0000 :=''; ls_SFDW00 :='N'; -- BEGIN -- select NVL(rtrim(value0),'0') into ls_GHDLX0 from XT_XTCS00 where name00 = 'SF_GHDLX0'; -- EXCEPTION -- WHEN OTHERS THEN -- ls_GHDLX0:='0'; -- END; begin select SF_SF_GETYYID00() YYID00 into ls_yyid00 from dual; exception when OTHERS then ls_yyid00:='0'; end; --if ls_GHDLX0='9' then -- ls_ts0000:='请在'||to_char(sysdate+1/48,'HH24:MI')||'时间内取药'; --end if; begin select trim(value0) into ls_JYSFDM from XT_XTCS00 where NAME00='YF_ZYJYF_SFDM'; exception when others then ls_JYSFDM:=0; end; begin select trim(value0) into SF_AZLXM0 from XT_XTCS00 where NAME00='SF_AZLXM0'; exception when others then SF_AZLXM0:='N'; end; begin select trim(value0) into SF_AZLXM1 from XT_XTCS00 where NAME00='SF_AZLXM1'; exception when others then SF_AZLXM1:='N'; end; begin select nvl(trim(VALUE0), 'Y') INTO ls_XJSFDYH FROM XT_XTCS00 where trim(NAME00) = 'SF_MZQDXJSFXSZDYH'; exception when others then ls_XJSFDYH := 'Y'; end; select HJJE00 - ZFJE00,BRID00,FBBH00 into ls_ZFHJCE,LS_BRID00,ls_JZFBBH from SF_JZB000 where JZDH00 = as_JZDH00; select B.YBMC00 into LS_FBMC00 from BM_BRXXB0 A,IC_YBBRLB B where A.FBBH00 = B.FBBH00 and A.YBLB00 = B.YBLB00 and BRID00=LS_BRID00; if as_GHH000 <> '0' then select FBBH00,GHLB00 into ls_FBBH00,ls_GHLB00 from SF_BRXXB0 where GHH000 = as_GHH000; select trim(GHRQ00),trim(GHKSMC),trim(JZYSXM),trim(ICKH00),trim(XM0000),trim(XB0000),trim(SF_SF_CSRQTONL(CSRQ00)) into ls_ghrq00,ls_ghksmc,ls_jzysxm,ls_ickh00,ls_xm0000,ls_xb0000,ls_csrq00 from VW_SF_JZXX00 where JZDH00 =as_JZDH00 and GHH000 =as_GHH000 and rownum=1; else select FBBH00,0 into ls_FBBH00,ls_GHLB00 from BM_BRXXB0 where BRID00 = LS_BRID00 AND ROWNUM=1; select trim(B.XM0000),trim(B.XB0000),trim(A.JZRQ00) into ls_xm0000,ls_xb0000,ls_ghrq00 from SF_JZB000 A,SF_PJSYQK B where A.PJH000 = B.FZPJH0 and A.JZDH00=as_JZDH00 and ROWNUM=1; end if; begin select decode(instr(LBMC00,'体检'),0,'FTJ','TJ') into ls_GHLBMC from BM_GHLBB0 where LBBH00=ls_GHLB00; exception when OTHERS then ls_GHLBMC:='FTJ'; end; if ls_GHLBMC='TJ' then select count(1) into ls_COUNT0 from BM_TYDWB0 where BRID00=LS_BRID00; if ls_COUNT0>0 then ls_SFDW00 := 'Y'; end if; end if; if ls_ZFHJCE <> 0 or ls_FBBH00 <> 1 or ls_JZFBBH<>1 then ls_ZFXSBL:='1'; select nvl(trim(max(value0)),'1') into ls_ZFXSBL from XT_XTCS00 where NAME00='SF_MZCFQDMXXS'; --'0'不显示'1'显示百分比'2'显示甲乙类,'3'显示' z'自费类,' k'控制类(乙类) --if ls_GHDLX0='9' and ls_JZFBBH=1 then --广东按结算费别 if ls_yyid00 = 'A' and ls_JZFBBH=1 then --广东按结算费别 ls_ZFXSBL:='0'; end if; else ls_ZFXSBL:='0'; end if; --自付显示比例,'0':不显示,'1':显示 --珠海医院不管病人是自费还是医保按药品或收费项目对应医保类别显示 linzy if ls_yyid00='H00000' then ls_ZFXSBL:='3'; end if; select SQ_BM_MZCFQD_TEMP00_ID0000.nextval into ad_ID0000 from dual; open CUR_SF_MZCFQD_CFLSH0; --获取药品的明细清单信息 loop fetch CUR_SF_MZCFQD_CFLSH0 into ls_CFLSH0,ls_YPDLBH,ls_ZYTS00,ls_CFZJE0,ls_YSXM00,ls_ZWXM00,ls_xkh000,ls_lyks00; --加 LS_LYKS00 exit when CUR_SF_MZCFQD_CFLSH0%NOTFOUND; --获得发药窗口名称 begin select B.CKMC00,nvl(a.FYDLXH,0) into ls_CKMC00,ls_fydlxh from YF_CFDL00 A,BM_CKBM00 B where A.FYCKBH = B.CKBH00 and A.CFLSH0 = ls_CFLSH0; exception when OTHERS then ls_CKMC00 := ''; ls_fydlxh :=0; end; --退药时要合并处方 select sum(round(b.YPZSL0*b.LSDJ00,2)),max(trim(b.YPDLBH)) into ls_CFZJE0,ls_YPDLBH_1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.SFZBY0 = 'N' and (a.CFLSH0=ls_CFLSH0 or a.BCCFH0=ls_CFLSH0 and a.DJH000>0); if ls_CFZJE0<>0 then if ls_YPDLBH <> 2 AND ls_lyks00 IS NULL then --非草药 -- YZB 加了 AND ls_lyks00 IS NULL -- ls_DYID00:=ls_DYID00+1; -- select decode(SFKSLY,'Y','[治疗用药]','') into ls_sfksly from YF_MZCF00 where cflsh0=ls_CFLSH0;--是否治疗用药 /* if ls_XJSFDYH = 'Y' THEN if ls_yyid00 = 'H00000' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||decode(ls_YPDLBH_1,3,'','取药')||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药](无需取药)',' 排队号:'||to_char(ls_fydlxh))); else insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,decode(ls_YPDLBH_1,3,'卫生材料:','西成药取药:')||trim(ls_CKMC00)||decode(ls_sfksly,'[治疗用药]',' [治疗用药](无需取药)',' '||to_char(ls_fydlxh)||'号')); end if; end if; linzy 去掉处方号显示 BY MZSF-20140219-002 */ -- ls_DYID00:=ls_DYID00+1; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) -- values(ad_ID0000,'9NO:'||trim(ls_CFLSH0)||DECODE(ls_YSXM00,ls_ZWXM00,'','('||trim(ls_xkh000)||')'),'',ls_ts0000,'','',ls_DYID00); --请到窗口取药 西药、成药 LBBH00=1 -- if ls_sfksly='[治疗用药]' then -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'11[治疗用药](无需取药)','','1',''); -- else insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) values(ad_ID0000,'','','','','',ls_DYID00,'请到'||trim(ls_CKMC00)||'取药',' 排队号:'||to_char(ls_fydlxh),'1',''); -- end if; --药品明细 ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'药品名称/规格','',' 数量',' 单价 ','金额'||DECODE(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00,'1'); ls_DYID00:=ls_DYID00+1; --详细用法:次数量+剂量单位,CSL000+JLDW00/频次名称PCMC00/用药时间数+时间单位YYSJ00+YYSJDW/药品用法名称YPYFMC insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) select * from (select ad_ID0000,substr(trim(YPMC00)||'/'||trim(YPGG00),1,100),'',' '||substr(to_char(YPZSL0)||' '||trim(CFDW00),1,20) BZ0000,to_char(LSDJ00),trim(substr(SF_SF_CONVERTJE(round(YPZSL0*LSDJ00,2)),1,20)||DECODE(ls_ZFXSBL,'1','/'||SF_SF_CONVERTJE(ZFBL00)*100||'%','2','/'||decode(ZFBL00,0,'甲类',1,'自费','乙类'),'3',decode((select YBLXBH from BM_YD0000 where YPNM00=A.YPNM00),'01',' 基本','02',' 控制','03',' 自费'),' ')),ls_DYID00,'1' from YF_MZCFMX A,YF_MZCF00 B where (B.CFLSH0 = ls_CFLSH0 OR B.BCCFH0=ls_CFLSH0 and b.DJH000>0) and A.SFZBY0 = 'N' AND A.CFLSH0=B.CFLSH0 order by a.ZB0000,a.CFID00); --group by substr(trim(YPMC00)||'/'||trim(YPGG00),1,100),substr(' '||SF_SF_CONVERTJE(abs(CSL000))||trim(JLDW00)||'/'||trim(PCMC00)||'/'||YYSJ00||trim(YYSJDW)||'/'||trim(YPYFMC),1,200),substr(SF_SF_CONVERTJE_DJ(LSDJ00),1,20); ls_DYID00:=ls_DYID00+1; --小计 insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,LBBH00) --yzb 吧处方号跟小计绑在一起 values(ad_ID0000,'','','','NO:'||trim(ls_CFLSH0)||' '||'小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,'','1'); /* if ls_XJSFDYH = 'N' then if ls_yyid00 = 'H00000' then Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||decode(ls_YPDLBH_1,3,'','取药')||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' 排队号:'||to_char(ls_fydlxh))); else Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,decode(ls_YPDLBH_1,3,'卫生材料:','西成药取药:')||trim(ls_CKMC00)||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' '||to_char(ls_fydlxh)||'号')); end if; end if; */ --添加一行的空格 --Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) -- values(ad_ID0000,LPAD('-',80,'-'),LPAD('-',20,'-'),LPAD('-',50,'-'),LPAD('-',20,'-'),LPAD('-',10,'-'),ls_DYID00); else --说明,ls_YPDLBH = 2,是草药处方 IF ls_lyks00 IS NULL THEN --YZB -- ls_DYID00:=ls_DYID00+1; -- select decode(sfksly,'Y','[治疗用药]','') into ls_sfksly from YF_MZCF00 where cflsh0=ls_CFLSH0;--是否治疗用药 /* IF ls_XJSFDYH = 'Y' THEN if ls_yyid00 = 'H00000' then Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','','小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||'取药'||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' 排队号:'||to_char(ls_fydlxh))); else Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','','小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,'中药取药:'||trim(ls_CKMC00)||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' '||to_char(ls_fydlxh)||'号')); end if; end if; */ --请到窗口取药 草药 LBBH00=2 -- if ls_sfksly='[治疗用药]' then -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'[治疗用药](无需取药)','','2',''); -- else insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) values(ad_ID0000,'','','','','',ls_DYID00,'请到'||trim(ls_CKMC00)||'取药',' 排队号:'||to_char(ls_fydlxh),'2',''); -- end if; -- ls_DYID00:=ls_DYID00+1; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,LBBH00) -- values(ad_ID0000,'99NO:'||trim(ls_CFLSH0)||DECODE(ls_YSXM00,ls_ZWXM00,'','('||trim(ls_xkh000)||')'),'',ls_ts0000,'','',ls_DYID00,' 剂数:'||to_char(ls_ZYTS00),'2'); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'药品名称','总量',' 金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)')||' 药品名称 ',' 总量 ','金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00,'2'); ls_DYID00:=ls_DYID00+1; for lv_MZCFMX IN CUR_SF_MZCFQD_TEMPTJ loop ls_COUNT6:=ls_COUNT6+1; end loop; select floor((ls_COUNT6+1)/2) into ls_COUNT6 from dual; for lv_MZCFMX in CUR_SF_MZCFQD_TEMPTJ loop if ls_COUNT5 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select k.SL0000,k.ZJE000 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where k.MZZYBZ='0' and SFDJH0 in (select b.DJH000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') --MZSF-20081027-001 ); ls_DYID00:=ls_DYID00+1; if ls_COUNT0 > 0 then --增加病人信息 if ls_COUNT4=1 then if ls_yyid00 = 'A' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,brxx00,DYID00,LBBH00) values(ad_ID0000,'','','','','',LPAD('=',84,'='),ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,brxx00,LBBH00) values(ad_ID0000,'','','','','',ls_DYID00,'','就诊时间:'||substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||LPAD(' ',36-lengthb(substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||'就诊'),' ')||'就诊科室:'||trim(ls_ghksmc)||lpad(' ',12-lengthb(trim(ls_ghksmc)),' ')||' 主诊医生:'||trim(ls_jzysxm),'3'); ls_DYID00:=ls_DYID00+1; end if; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) -- values(ad_ID0000,LPAD('-',80,'-'),LPAD('-',20,'1-'),LPAD('-',50,'-'),LPAD('-',20,'-'),LPAD('-',10,'-'),ls_DYID00,'3'); -- ls_DYID00:=ls_DYID00+1; end if; /* if ls_XJSFDYH = 'Y' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',SF_SF_CONVERTJE(ls_hjje00),ls_DYID00); end if; */ ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'项目名称','',' 数量',' 单价 ','金额(元)',ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) select ad_ID0000,substr(trim(XMMC00),1,100)||decode(BZ0000,'','','/'||bz0000),'',' '||trim(XMDW00)||'*'||to_char(sum(XMSL00)),to_char(XMDJ00),trim(SF_SF_CONVERTJE(sum(HJJE00))),ls_DYID00,'3' from (select a.XMMC00,a.XMSL00,a.XMDW00,decode(ls_FBBH00||ls_GHLBMC,'99TJ',decode(sign(a.TJDJ00-a.XMDJ00),1,a.TJDJ00,a.XMDJ00),a.XMDJ00) XMDJ00,a.HJJE00,c.BZ0000 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D where A.DJH000 = B.DJH000 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and a.SFYDJ0 =decode(ls_SFDW00,'Y',a.SFYDJ0,'1') and A.HJJE00 <> 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select k.ZLXMJC,k.SL0000,k.DW0000,k.ZJE000/k.SL0000,k.ZJE000,k.BZ0000 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where SFDJH0 in (select b.DJH000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and k.MZZYBZ='0' and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') --MZSF-20081027-001 ) group by XMMC00,BZ0000,XMDW00,XMDJ00 having sum(XMSL00) <> 0; if ls_XJSFDYH = 'N' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',SF_SF_CONVERTJE(ls_hjje00),ls_DYID00,'3'); end if; ls_DYID00:=ls_DYID00+1; ls_COUNT4:=1; end if; else select sum(XMSL00),sum(HJJE00) into ls_COUNT0,ls_HJJE00 from (select a.XMSL00,a.HJJE00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D where A.DJH000 = B.DJH000 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and a.SFYDJ0 =decode(ls_SFDW00,'Y',a.SFYDJ0,'1') and A.HJJE00 <> 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select k.SL0000,k.ZJE000 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where SFDJH0 in (select b.DJH000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and k.MZZYBZ='0' and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') --MZSF-20081027-001 ); ls_DYID00:=ls_DYID00+1; if ls_COUNT0 > 0 then --增加病人信息 if ls_COUNT4=1 then if ls_yyid00 = 'A' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,brxx00,DYID00,LBBH00) values(ad_ID0000,'','','','','',LPAD('=',84,'='),ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,brxx00,LBBH00) values(ad_ID0000,'','','','','',ls_DYID00,'','就诊时间:'||substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||LPAD(' ',36-lengthb(substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||'就诊'),' ')||'就诊科室:'||trim(ls_ghksmc)||lpad(' ',12-lengthb(trim(ls_ghksmc)),' ')||' 主诊医生:'||trim(ls_jzysxm),'3'); ls_DYID00:=ls_DYID00+1; end if; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,LPAD('-',80,'-'),LPAD('-',20,'-'),LPAD('-',50,'-'),LPAD('-',20,'-'),LPAD('-',10,'-'),ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; end if; if ls_XJSFDYH = 'Y' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',SF_SF_CONVERTJE(ls_hjje00),ls_DYID00,'3'); end if; ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'项目名称','',' 数量',' 单价 ','金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) --SF_SF_CONVERTJE_DJ(XMDJ00) select ad_ID0000,substr(trim(XMMC00),1,100),'',' '||trim(XMDW00)||'*'||to_char(sum(XMSL00)),to_char(XMDJ00),trim(SF_SF_CONVERTJE(sum(HJJE00))||decode(ls_ZFXSBL,'1','/'||SF_SF_CONVERTJE(ZFBL00)*100||'%','2','/'||decode(ZFBL00,0,'甲类',1,'自费','乙类'),'3',decode(ZFBL00,0,' ',1,' z',' k'),' ')),ls_DYID00,'3' from -- select ad_ID0000,substr(trim(XMMC00),1,100),' ',to_char(sum(SL0000))||trim(DW0000),SF_SF_CONVERTJE_DJ(jjje00),trim(SF_SF_CONVERTJE(sum(d.jjje00,d.sl0000*d.sfcs00*d.jjje00)||DECODE(ls_ZFXSBL,'1','/'||SF_SF_CONVERTJE(ZFBL00)*100||'%',' ')),ls_DYID00 from ( select a.XMMC00,a.XMSL00,a.XMDW00,decode(ls_FBBH00||ls_GHLBMC,'99TJ',decode(sign(a.TJDJ00-a.XMDJ00),1,a.TJDJ00,a.XMDJ00),a.XMDJ00) XMDJ00,a.HJJE00,a.ZFBL00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D where A.DJH000 = B.DJH000 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and a.SFYDJ0 =decode(ls_SFDW00,'Y',a.SFYDJ0,'1') and A.HJJE00 <> 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select a.XMMC00,d.SL0000,a.DW0000,d.JJJE00,d.SL0000*d.SFCS00*d.JJJE00,d.ZFBL00 from YJ_YW0000 k,BM_YYSFXM a,BM_ZLXMLB c,BM_ZLZD00 g,YJ_YWJJ00 d where k.YJDJH0=d.YJDJH0 and a.SFXMID=d.SFXMID and k.SFDJH0 in (select b.DJH000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=g.ZLXMID and g.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and k.MZZYBZ='0' and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') --MZSF-20081027-001 ) group by XMMC00,XMDW00,XMDJ00,ZFBL00 having sum(XMSL00) <> 0; /* if ls_XJSFDYH = 'N' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',SF_SF_CONVERTJE(ls_hjje00),ls_DYID00); end if; */ insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',SF_SF_CONVERTJE(ls_hjje00),ls_DYID00,'3'); ls_DYID00:=ls_DYID00+1; ls_COUNT4:=1; end if; end if; ls_COUNT7:=0; if SF_AZLXM1='Y' then --添加检查/治疗/手术/化验项目 select sum(k.SL0000),sum(k.ZJE000) into ls_COUNT1,ls_hjje00 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 in('检查','治疗','手术','化验') and k.MZZYBZ='0' and k.SFDJH0 in( select b.DJH000 from SF_BRFY00 b where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0')); ls_DYID00:=ls_DYID00+1; if ls_COUNT1 > 0 then --增加病人信息 if ls_COUNT4=1 then if ls_yyid00 = 'A' then Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,brxx00,DYID00,LBBH00) values(ad_ID0000,'','','','','',LPAD('=',84,'='),ls_DYID00,'4'); ls_DYID00:=ls_DYID00+1; Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,BRXX00,LBBH00) values(ad_ID0000,'','','','','',ls_DYID00,'','就诊时间:'||substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||LPAD(' ',36-lengthb(substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||'就诊'),' ')||'就诊科室:'||trim(ls_ghksmc)||lpad(' ',12-lengthb(trim(ls_ghksmc)),' ')||' 主诊医生:'||trim(ls_jzysxm),'4'); ls_DYID00:=ls_DYID00+1; end if; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) -- values(ad_ID0000,lpad('-',80,'-'),lpad('-',20,'-1'),lpad('-',50,'-'),lpad('-',20,'-'),LPAD('-',10,'-'),ls_DYID00,'4'); -- ls_DYID00:=ls_DYID00+1; end if; SELECT sum(HJJE00) into ls_HJJE02 from (select a.XMSL00,a.HJJE00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D where A.DJH000 = B.DJH000 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and a.SFYDJ0 =decode(ls_SFDW00,'Y',a.SFYDJ0,'1') and A.HJJE00 <> 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select k.SL0000,k.ZJE000 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where SFDJH0 in (select b.DJH000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and k.MZZYBZ='0' and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') ); /* if ls_XJSFDYH = 'Y' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00,BZ0002)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE02),SF_SF_CONVERTJE(ls_hjje00)),ls_DYID00,'4','请到'); end if; ls_DYID00:=ls_DYID00+1; */ --insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) --values(ad_ID0000,'检查/治疗/手术/化验项目','数量',' 执行科室',' 单价 ','金额(元)',ls_DYID00,'4'); ls_DYID00:=ls_DYID00+1; end if; for F_ZXKS in CUR_ZXKS loop --执行科室及地点 ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00,FYCK00,BZ0002) values(ad_ID0000,'','',' ',' ','',ls_DYID00,'4','请到'||F_ZXKS.KSWZ00,''); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'检查/治疗/手术/化验项目','',' 数量',' 单价 ','金额(元)',ls_DYID00,'4'); ld_ZXKSBH:=F_ZXKS.BMBH00; ls_HJJE02:=0; ls_HJJE00:=0; open CUR_SF_MZCFQD_FYMXTJ_01; loop fetch CUR_SF_MZCFQD_FYMXTJ_01 into ls_ZXKSMC, lv_FYMX00.XMBH00,lv_FYMX00.XMMC00, lv_FYMX00.XMGG00,lv_FYMX00.XMDW00, lv_FYMX00.XMDJ00, lv_FYMX00.XMSL00,lv_FYMX00.HJJE00,lv_FYMX00.DJH000,lv_FYMX00.YJDJH0; exit when CUR_SF_MZCFQD_FYMXTJ_01%NOTFOUND; --add by qks 2011.12.08 if instr(trim(lv_FYMX00.XMMC00),'科-')>0 then ls_mc0000 := substr(trim(lv_FYMX00.XMMC00),instr(trim(lv_FYMX00.XMMC00),'科-')+2,13); else ls_mc0000 := substr(trim(lv_FYMX00.XMMC00),1,13); end if; --取医保类别 linzy for CUR_YYSFXM_YBLXBH in (select A.YBLXBH from BM_YYSFXM A,XT_ZLSFGX B where A.SFXMID=B.SFXMID and B.ZLXMID=lv_FYMX00.XMBH00) loop if CUR_YYSFXM_YBLXBH.YBLXBH='01' or CUR_YYSFXM_YBLXBH.YBLXBH='02' then ls_YBLXBH:='01'; exit; else ls_YBLXBH:='03'; end if; end loop; --取项目实际收的金额 select nvl(sum(JJJE00*SFCS00),0) into ls_XMDJ01 from YJ_YWJJ00 where YJDJH0=lv_FYMX00.YJDJH0; select nvl(sum(HJJE00),0) into ls_HJJE01 from SF_FYMX00 where (DJH000=lv_FYMX00.DJH000 or BEIZHU='*DWZK*'||lv_FYMX00.DJH000) and YJDJH0=lv_FYMX00.YJDJH0; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,ls_mc0000,'',' '||trim(lv_FYMX00.XMDW00)||'*'||to_char(lv_FYMX00.XMSL00),decode(ls_GHLBMC,'TJ',decode(ls_FBBH00,'99',to_char(lv_FYMX00.XMDJ00),SF_SF_CONVERTJE(ls_XMDJ01)),to_char(lv_FYMX00.XMDJ00)), decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE01),trim(SF_SF_CONVERTJE(lv_FYMX00.HJJE00)))||decode(ls_YBLXBH,'01',' 基本','03',' 自费',' '),ls_DYID00,'4'); ls_DYID00:=ls_DYID00+1; --费用合计重新赋值 ls_HJJE02:=nvl(ls_HJJE02,0) + ls_HJJE01; ls_HJJE00:=nvl(ls_HJJE00,0) + lv_FYMX00.HJJE00; end loop; close CUR_SF_MZCFQD_FYMXTJ_01; if (ls_COUNT1 > 0 ) then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00)--项目增加小计 values(ad_ID0000,' ',' ',' ','小计:',decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE02),SF_SF_CONVERTJE(ls_HJJE00)),ls_DYID00,'4'); end if; end loop; --------------------------------------------------------------------- open CUR_SF_MZCFQD_CFLSH0; --获取药品的明细清单信息 loop fetch CUR_SF_MZCFQD_CFLSH0 into ls_CFLSH0,ls_YPDLBH,ls_ZYTS00,ls_CFZJE0,ls_YSXM00,ls_ZWXM00,ls_xkh000,ls_lyks00; --加 LS_LYKS00 exit when CUR_SF_MZCFQD_CFLSH0%NOTFOUND; --获得发药窗口名称 begin select B.CKMC00,nvl(a.FYDLXH,0) into ls_CKMC00,ls_fydlxh from YF_CFDL00 A,BM_CKBM00 B where A.FYCKBH = B.CKBH00 and A.CFLSH0 = ls_CFLSH0; exception when OTHERS then ls_CKMC00 := ''; ls_fydlxh :=0; end; --退药时要合并处方 select sum(round(b.YPZSL0*b.LSDJ00,2)),max(trim(b.YPDLBH)) into ls_CFZJE0,ls_YPDLBH_1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.SFZBY0 = 'N' and (a.CFLSH0=ls_CFLSH0 or a.BCCFH0=ls_CFLSH0 and a.DJH000>0) AND lyks00 IS NOT NULL ; --YZB if ls_CFZJE0<>0 then if ls_YPDLBH <> 3 then --非材料 -- YZB ls_DYID00:=ls_DYID00+1; select decode(SFKSLY,'Y','[治疗用药]','') into ls_sfksly from YF_MZCF00 where cflsh0=ls_CFLSH0;--是否治疗用药 /* if ls_XJSFDYH = 'Y' THEN if ls_yyid00 = 'H00000' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||decode(ls_YPDLBH_1,3,'','取药')||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药](无需取药)',' 排队号:'||to_char(ls_fydlxh))); else insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,decode(ls_YPDLBH_1,3,'卫生材料:','西成药取药:')||trim(ls_CKMC00)||decode(ls_sfksly,'[治疗用药]',' [治疗用药](无需取药)',' '||to_char(ls_fydlxh)||'号')); end if; end if; linzy 去掉处方号显示 BY MZSF-20140219-002 */ -- ls_DYID00:=ls_DYID00+1; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) -- values(ad_ID0000,'9NO:'||trim(ls_CFLSH0)||DECODE(ls_YSXM00,ls_ZWXM00,'','('||trim(ls_xkh000)||')'),'',ls_ts0000,'','',ls_DYID00); --请到窗口取药 西药、成药 LBBH00=1 -- if ls_sfksly='[治疗用药]' AND ls_lyks00 IS NOT NULL then -- YZB 加了 AND ls_lyks00 IS NOT NULL -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'[治疗用药](无需取药)','','1',''); -- else -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'请到'||trim(ls_CKMC00)||'取药112',' 排队号:'||to_char(ls_fydlxh),'1',''); -- end if; --药品明细 ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) values(ad_ID0000,'药品名称/规格（无需取药）','',' 数量',' 单价 ','金额'||DECODE(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00,'1'); ls_DYID00:=ls_DYID00+1; --详细用法:次数量+剂量单位,CSL000+JLDW00/频次名称PCMC00/用药时间数+时间单位YYSJ00+YYSJDW/药品用法名称YPYFMC insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) select * from (select ad_ID0000,substr(trim(YPMC00)||'/'||trim(YPGG00),1,100),'',' '||substr(to_char(YPZSL0)||' '||trim(CFDW00),1,20) BZ0000,to_char(LSDJ00),trim(substr(SF_SF_CONVERTJE(round(YPZSL0*LSDJ00,2)),1,20)||DECODE(ls_ZFXSBL,'1','/'||SF_SF_CONVERTJE(ZFBL00)*100||'%','2','/'||decode(ZFBL00,0,'甲类',1,'自费','乙类'),'3',decode((select YBLXBH from BM_YD0000 where YPNM00=A.YPNM00),'01',' 基本','02',' 控制','03',' 自费'),' ')),ls_DYID00,'1' from YF_MZCFMX A,YF_MZCF00 B where (B.CFLSH0 = ls_CFLSH0 OR B.BCCFH0=ls_CFLSH0 and b.DJH000>0) and A.SFZBY0 = 'N' AND A.CFLSH0=B.CFLSH0 order by a.ZB0000,a.CFID00); --group by substr(trim(YPMC00)||'/'||trim(YPGG00),1,100),substr(' '||SF_SF_CONVERTJE(abs(CSL000))||trim(JLDW00)||'/'||trim(PCMC00)||'/'||YYSJ00||trim(YYSJDW)||'/'||trim(YPYFMC),1,200),substr(SF_SF_CONVERTJE_DJ(LSDJ00),1,20); ls_DYID00:=ls_DYID00+1; --小计 insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,LBBH00) --yzb 把处方号跟小计绑在一起 values(ad_ID0000,'','','','NO:'||trim(ls_CFLSH0)||' '||'小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,'','1'); /* if ls_XJSFDYH = 'N' then if ls_yyid00 = 'H00000' then Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||decode(ls_YPDLBH_1,3,'','取药')||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' 排队号:'||to_char(ls_fydlxh))); else Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','',' 小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,decode(ls_YPDLBH_1,3,'卫生材料:','西成药取药:')||trim(ls_CKMC00)||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' '||to_char(ls_fydlxh)||'号')); end if; end if; */ --添加一行的空格 --Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) -- values(ad_ID0000,LPAD('-',80,'-'),LPAD('-',20,'-'),LPAD('-',50,'-'),LPAD('-',20,'-'),LPAD('-',10,'-'),ls_DYID00); -- else --说明,ls_YPDLBH = 2,是草药处方 -- ls_DYID00:=ls_DYID00+1; -- select decode(sfksly,'Y','[治疗用药]','') into ls_sfksly from YF_MZCF00 where cflsh0=ls_CFLSH0;--是否治疗用药 /* IF ls_XJSFDYH = 'Y' THEN if ls_yyid00 = 'H00000' then Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','','小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,trim(ls_CKMC00)||'取药'||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' 排队号:'||to_char(ls_fydlxh))); else Insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00) values(ad_ID0000,'','','','小计:',SF_SF_CONVERTJE(ls_CFZJE0),ls_DYID00,'中药取药:'||trim(ls_CKMC00)||DECODE(ls_sfksly,'[治疗用药]',' [治疗用药]',' '||to_char(ls_fydlxh)||'号')); end if; end if; */ --请到窗口取药 草药 LBBH00=2 -- if ls_sfksly='[治疗用药]' then -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'[治疗用药](无需取药)','','2',''); -- else -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,FYCK00,PDH000,LBBH00,BZ0002) -- values(ad_ID0000,'','','','','',ls_DYID00,'30请到'||trim(ls_CKMC00)||'取药',' 排队号:'||to_char(ls_fydlxh),'2',''); -- end if; -- -- ls_DYID00:=ls_DYID00+1; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,LBBH00) -- values(ad_ID0000,'99NO:'||trim(ls_CFLSH0)||DECODE(ls_YSXM00,ls_ZWXM00,'','('||trim(ls_xkh000)||')'),'',ls_ts0000,'','',ls_DYID00,' 剂数:'||to_char(ls_ZYTS00),'2'); -- -- ls_DYID00:=ls_DYID00+1; -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,LBBH00) -- values(ad_ID0000,'药品名称','20总量',' 金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)')||' 药品名称 ',' 总量 ','金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00,'2'); -- ls_DYID00:=ls_DYID00+1; /* for lv_MZCFMX IN CUR_SF_MZCFQD_TEMPTJ loop ls_COUNT6:=ls_COUNT6+1; end loop; select floor((ls_COUNT6+1)/2) into ls_COUNT6 from dual; for lv_MZCFMX in CUR_SF_MZCFQD_TEMPTJ loop if ls_COUNT5 0 then --增加病人信息 if ls_COUNT4=1 then if ls_yyid00 = 'A' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,brxx00,DYID00) values(ad_ID0000,'','','','','',lpad('=',84,'='),ls_DYID00); ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00,BZ0001,brxx00) values(ad_ID0000,'','','','','',ls_DYID00,'','就诊时间:'||substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||LPAD(' ',36-lengthb(substr(ls_ghrq00,1,4)||'年'||substr(ls_ghrq00,5,2)||'月'||substr(ls_ghrq00,7,2)||'日'||' 姓名:'||trim(ls_xm0000)||'就诊'),' ')||'就诊科室:'||trim(ls_ghksmc)||lpad(' ',12-lengthb(trim(ls_ghksmc)),' ')||' 主诊医生:'||trim(ls_jzysxm)); ls_DYID00:=ls_DYID00+1; end if; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) values(ad_ID0000,lpad('-',80,'-'),lpad('-',20,'-'),lpad('-',50,'-'),lpad('-',20,'-'),lpad('-',10,'-'),ls_DYID00); ls_DYID00:=ls_DYID00+1; end if; SELECT sum(HJJE00) into ls_HJJE02 from (select a.xmsl00,a.hjje00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D where A.DJH000 = B.DJH000 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and a.sfydj0 =decode(ls_SFDW00,'Y',a.sfydj0,'1') and A.HJJE00 <> 0 and B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') and A.XMBH00 not in (9999999996,9999999997,9999999998,9999999999) union all select k.SL0000,k.ZJE000 from YJ_YW0000 k,BM_ZLXMLB c,BM_ZLZD00 d where SFDJH0 in (select b.djh000 from SF_BRFY00 B where B.JZDH00 = as_JZDH00 and (B.MZH000 = as_GHH000 or as_GHH000 = '0') ) and k.ZLXMID=d.ZLXMID and d.LBBH00=c.LBBH00 and c.LBMC00 not in('治疗','手术','化验') and k.mzzybz='0' and (c.LBMC00 not in ('检查') and SF_AZLXM1='Y' or SF_AZLXM1='N') --MZSF-20081027-001 ); if ls_XJSFDYH = 'Y' then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00)--项目增加小计 values(ad_ID0000,' ',' ',' ','22小计:',decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE02),SF_SF_CONVERTJE(ls_hjje00)),ls_DYID00); end if; ls_DYID00:=ls_DYID00+1; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) values(ad_ID0000,'治疗/手术/化验项目',' ','数量',' 单价 ','金额'||decode(ls_ZFXSBL,'1','/'||'自付','2','/'||'类别','(元)'),ls_DYID00); ls_DYID00:=ls_DYID00+1; end if; open CUR_SF_MZCFQD_FYMXTJ; loop fetch CUR_SF_MZCFQD_FYMXTJ into lv_FYMX00.XMBH00,lv_FYMX00.XMMC00, lv_FYMX00.XMGG00,lv_FYMX00.XMDW00, lv_FYMX00.XMDJ00,lv_FYMX00.ZFBL00, lv_FYMX00.XMSL00,lv_FYMX00.HJJE00,lv_FYMX00.DJH000,lv_FYMX00.YJDJH0; exit when CUR_SF_MZCFQD_FYMXTJ%NOTFOUND; select nvl(sum(JJJE00*SFCS00),0) into ls_XMDJ01 from YJ_YWJJ00 where YJDJH0=lv_FYMX00.YJDJH0; select nvl(sum(HJJE00),0) into ls_HJJE01 from SF_FYMX00 where (DJH000=lv_FYMX00.DJH000 or BEIZHU='*DWZK*'||lv_FYMX00.DJH000) and YJDJH0=lv_FYMX00.YJDJH0; insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) values(ad_ID0000,substr(trim(lv_FYMX00.XMMC00),1,100),' ',to_char(lv_FYMX00.XMSL00)||trim(lv_FYMX00.XMDW00),decode(ls_GHLBMC,'TJ',decode(ls_FBBH00,'99',to_char(lv_FYMX00.XMDJ00),SF_SF_CONVERTJE(ls_XMDJ01)),to_char(lv_FYMX00.XMDJ00)), trim(decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE01),SF_SF_CONVERTJE(lv_FYMX00.HJJE00))||DECODE(ls_ZFXSBL,'1','/'||SF_SF_CONVERTJE(lv_FYMX00.ZFBL00)*100||'%','2','/'||decode(lv_FYMX00.ZFBL00,0,'甲类',1,'自费','乙类'),'3',decode(lv_FYMX00.ZFBL00,0,' ',1,' z',' k'),' ')),ls_DYID00); ls_DYID00:=ls_DYID00+1; end loop; CLOSE CUR_SF_MZCFQD_FYMXTJ; if (ls_XJSFDYH = 'N') and (ls_COUNT1 > 0) then insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00)--项目增加小计 values(ad_ID0000,' ',' ',' ','33小计:',decode(ls_FBBH00||ls_GHLBMC,'99TJ',SF_SF_CONVERTJE(ls_HJJE02),SF_SF_CONVERTJE(ls_hjje00)),ls_DYID00); end if; end if; ls_DYID00:=ls_DYID00+1; -- IF (ls_COUNT1 > 0)or(ls_COUNT4=1)or(ls_COUNT0>0) then -- insert into BM_MZCFQD_TEMP00(ID0000,MC0000,SL0000,BZ0000,DJ0000,JE0000,DYID00) -- values(ad_ID0000,lpad('-',80,'-'),lpad('-',20,'-'),lpad('-',50,'-'),lpad('-',20,'-'),lpad('-',10,'-'),ls_DYID00); -- end if; exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZCFQD_TEMPTJ'||',as_YHMSG0,as_SYSMSG)',1,200); rollback; end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SF_MZCFQD_TEMPTJ