CREATE OR REPLACE FUNCTION SF_SF_FPXSPDXH(JZDH number) return varchar2 is lv_CFCK00 VARCHAR(20); lv_CFCK01 VARCHAR(20); lv_DLXH00 VARCHAR(100); lv_BZ0000 VARCHAR2(20); --lv_DLXH00 number; lv_DLXH01 VARCHAR(100); lv_YFMC00 VARCHAR(100); lv_FYCK00 VARCHAR(100); lv_FYDLXH VARCHAR(20); lv_PHBZ00 char(1); lv_ZYDJS0 number; lv_YPDLBH CHAR(1); lv_PDXYDL VARCHAR(100);--第一种药品类别队列 lv_PDCYDL VARCHAR(100);--第二种药品类别队列 lv_PDXLMC VARCHAR(200); lv_ZCYXX0 varchar(50); lv_FYCKCY VARCHAR(50); ls_FYCKMC BM_CKBM00.CKMC00%type;--发药窗口名称 ls_FYCKBH BM_CKBM00.CKBH00%type;--发药窗口编号 ls_YPDLBH BM_YPLB00.LBBH00%TYPE;--药品大类 SF_XCYSFHBCK XT_XTCS00.value0%type;--西成药是否合并窗口 Y是N否默认为N ls_XYDLXX varchar2(100);--西药队列信息 ls_CYDLXX varchar2(100);--成药队列信息 ls_ZYDLXX_1 varchar2(100);--中药队列信息 普通药房 ls_ZYDLXX_2 varchar2(100);--中药队列信息 颗粒药房 ls_ZYDLXX_3 varchar2(100);--中药队列信息 颗粒药房 ls_XHXX00 varchar2(100);--临时信息 SF_JRZJPH XT_XTCS00.value0%type; --进入诊结排号的药房 SF_MZFPQYXXXS varchar2(100); Vyyid00 varchar2(6); CURSOR CUR_CK IS select distinct C.FYCK00 FYCKBH,C.CKMC00 FYCK00 from --||'窗口' SF_BRFY00 A,YF_MZCF00 B,VW_YF_PDPRINT C WHERE 1=1 and A.DJH000=B.DJH000 and B.CFLSH0=C.CFLSH0 and B.CFZT00 in ('0','1','2') and ((VYYID00='220045' and B.yfbmbh<>'109') or (VYYID00<>'220045' and 1=1)) and (C.PHBZ00='0' OR C.PHBZ00='2' OR C.PHBZ00='3') AND A.JZDH00=JZDH and not exists(select 1 from yf_mzcf00 where bccfh0=b.cflsh0); CURSOR CUR_SF_CKPDXH IS select to_char(C.DLXH00),C.YFMC00,C.CKMC00||'窗口' FYCK00,to_char(C.FYDLXH),c.PHBZ00,b.ZYDJS0, b.YPDLBH from SF_BRFY00 A,YF_MZCF00 B,VW_YF_PDPRINT C WHERE 1=1 AND A.DJH000=B.DJH000 and B.CFLSH0=C.CFLSH0 and B.CFZT00 in ('0','1','2') and (C.PHBZ00='0' OR C.PHBZ00='2' OR C.PHBZ00='3') AND A.JZDH00=JZDH and not exists(select 1 from yf_mzcf00 where bccfh0=b.cflsh0) and C.FYCK00=ls_FYCKBH; CURSOR CUR_CK_WJ IS --存在未进诊结排号的药房 select distinct C.FYCK00 FYCKBH,C.CKMC00||'窗口' FYCK00 from SF_BRFY00 A,YF_MZCF00 B,VW_YF_PDPRINT C WHERE 1=1 AND A.DJH000=B.DJH000 and B.CFLSH0=C.CFLSH0 and B.CFZT00 in ('0','1','2') and SF_SF_SFWJYF(C.YFBMBH)=1 and (C.PHBZ00='0' OR C.PHBZ00='2' OR C.PHBZ00='3') AND A.JZDH00=JZDH and not exists(select 1 from yf_mzcf00 where bccfh0=b.cflsh0); CURSOR CUR_SF_CKPDXH_WJ IS --存在未进诊结排号的药房 select to_char(C.DLXH00),C.YFMC00,C.CKMC00||'窗口' FYCK00,to_char(C.FYDLXH),c.PHBZ00,b.ZYDJS0, b.YPDLBH from SF_BRFY00 A,YF_MZCF00 B,VW_YF_PDPRINT C WHERE 1=1 AND A.DJH000=B.DJH000 and B.CFLSH0=C.CFLSH0 and B.CFZT00 in ('0','1','2') and SF_SF_SFWJYF(C.YFBMBH)=1 and (C.PHBZ00='0' OR C.PHBZ00='2' OR C.PHBZ00='3') AND A.JZDH00=JZDH and not exists(select 1 from yf_mzcf00 where bccfh0=b.cflsh0) and C.FYCK00=ls_FYCKBH; CURSOR CUR_WJDLYF IS select distinct C.YFMC00 from SF_BRFY00 A,YF_MZCF00 B,VW_YF_PDPRINT C WHERE 1=1 AND A.DJH000=B.DJH000 and B.CFLSH0=C.CFLSH0 and B.CFZT00 in ('0','1','2') and SF_SF_SFWJYF(C.YFBMBH)=0 and (C.PHBZ00='0' OR C.PHBZ00='2' OR C.PHBZ00='3') AND A.JZDH00=JZDH and not exists(select 1 from yf_mzcf00 where bccfh0=b.cflsh0); begin -- MODIFICATION HISTORY -- Person Date Comments -- xuzw 2009.05.06 create -- xuzw 2009.05.12 发药窗口相同，只要显示一个发药窗口... -- xuzw 2009.05.15 草药不要显示草药颗粒，直接显示颗粒 -- xuzw 2009.11.17 退药的窗口不要显示出来 -- xuzw 2010.01.15 lv_pdxydl,lv_pdcydl,lv_pdxlmc 长度改大 -- CSF 2011.09.25 西成药按是否合并药房来决定西成药品类别是否合并显示，由参数来控制SF_XCYSFHBCK MZSF-20110925-001 -- CSF 2011.09.27 改变写法 草药也合并药品类别和窗口 -- jinfl 2011.12.26 修改没进入队列的处方显示药房 MZSF-20111223-00 --取参数西成药是否合并窗口 Y是N否 默认为N -- laijg 2012.05.25 中药ls_ZYDLXX_2字段累加信息有错 by MZSF-20120525-001 -- zhangyc 2014.12.10 增加条件只显示正常的处方状态 by MZSF-20141205-001 -- zhangyc 2015.05.06 增加YYID00(中医药大学国医堂门诊部=220045)控制显示 by MZSF-20150407-001 -- zhangyc 2015.12.11 增加参数SF_MZFPQYXXXS控制取药信息 by MZSF-20151127-002 -- zhangyc 2015.03.16 归档脚本 by MZSF-20160309-002 ------------ ------------ ----------------------------------------------------- select SF_SF_GETYYID00() YYID00 into Vyyid00 from dual; select nvl(max(trim(value0)),'N') into SF_XCYSFHBCK from XT_XTCS00 where name00='SF_XCYSFHBCK'; select nvl(trim(max(value0)),'N') into SF_JRZJPH from XT_XTCS00 where name00='SF_JRZJPH'; select nvl(trim(max(value0)),'N') into SF_MZFPQYXXXS from XT_XTCS00 where name00='SF_MZFPQYXXXS'; ls_XYDLXX:=' '; ls_CYDLXX:=' '; ls_ZYDLXX_1:=' '; ls_ZYDLXX_2:=' '; ls_ZYDLXX_3:=' '; lv_BZ0000 := ''; if SF_JRZJPH='N' then open CUR_CK; fetch CUR_CK into ls_FYCKBH,ls_FYCKMC; while CUR_CK%found loop ls_XHXX00:=' '; --原来在CUR_CK有加'窗口' 现在改为下面语句判断 if instrb(ls_FYCKMC,'窗口')<=0 then ls_FYCKMC:=ls_FYCKMC||'窗口'; end if; open CUR_SF_CKPDXH ; fetch CUR_SF_CKPDXH into lv_DLXH00,lv_YFMC00,lv_FYCK00,lv_FYDLXH,lv_PHBZ00,lv_ZYDJS0,lv_YPDLBH; while CUR_SF_CKPDXH%found loop if trim(lv_PHBZ00) = '2' then --phbz00='2'发药序号后面应该加上"(补)"字样 lv_BZ0000 := '(补)'; end if; if ls_XHXX00=' ' then ls_XHXX00:=lv_FYDLXH||lv_BZ0000; else ls_XHXX00:=ls_XHXX00||'、'||lv_FYDLXH||lv_BZ0000; end if; if lv_ZYDJS0>0 then --是否有代煎数 ls_XHXX00 := ls_XHXX00||'(代煎'||to_char(lv_ZYDJS0)||'剂)'; end if; fetch CUR_SF_CKPDXH into lv_DLXH00,lv_YFMC00,lv_FYCK00,lv_FYDLXH,lv_PHBZ00,lv_ZYDJS0,lv_YPDLBH; end loop; Close CUR_SF_CKPDXH; if SF_MZFPQYXXXS='Y' then if (lv_YPDLBH='0' ) or (lv_YPDLBH='1' ) or (lv_YPDLBH='2') then if (trim(ls_XHXX00) is null ) or (ls_XHXX00='、') then if ls_XYDLXX=' ' then ls_XYDLXX:='请到['||lv_YFMC00||']取药'; else ls_XYDLXX:=ls_XYDLXX||','||'请到['||lv_YFMC00||']取药'; end if; else if ls_XYDLXX=' ' then ls_XYDLXX:='请['||ls_XHXX00||']号到['||lv_YFMC00||ls_FYCKMC||']取药'; else ls_XYDLXX:=ls_XYDLXX||','||'请['||ls_XHXX00||']号到['||lv_YFMC00||ls_FYCKMC||']取药'; end if; end if; end if; elsif VYYID00='220045' then ---国医堂--begin-- if SF_XCYSFHBCK='N' then--不合并西成药 if (lv_YPDLBH='0' ) or (lv_YPDLBH='1' ) then if ls_XYDLXX=' ' then ls_XYDLXX:='西药房'||' '||ls_XHXX00; else ls_XYDLXX:=ls_XYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; end if; end if; if lv_YPDLBH='2' then if lv_YFMC00='颗粒剂药房' then if ls_ZYDLXX_1=' ' then ls_ZYDLXX_1:='颗粒药房三号窗'||' '||ls_XHXX00||' '; else ls_ZYDLXX_1:=ls_ZYDLXX_1||' '||ls_FYCKMC||ls_XHXX00; end if; elsif lv_YFMC00='中药房' then if ls_ZYDLXX_2=' ' then ls_ZYDLXX_2:='中药房'||' '||ls_XHXX00; else ls_ZYDLXX_2:=ls_ZYDLXX_2||' '||ls_FYCKMC||ls_XHXX00; end if; elsif lv_YFMC00='配方颗粒剂药房' then if ls_ZYDLXX_3=' ' then ls_ZYDLXX_3:='颗粒药房五号窗'||' '||ls_XHXX00; else ls_ZYDLXX_3:=ls_ZYDLXX_3||' '||ls_FYCKMC||ls_XHXX00; end if; end if; end if; else ---国医堂--end--其它医院--begin-- if SF_XCYSFHBCK='N' then--不合并西成药 if lv_YPDLBH='0' then if ls_XYDLXX=' ' then ls_XYDLXX:='西药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_XYDLXX:=ls_XYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; elsif lv_YPDLBH='1' then if ls_CYDLXX=' ' then ls_CYDLXX:='成药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_CYDLXX:=ls_CYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; end if; elsif (lv_YPDLBH='0' ) or (lv_YPDLBH='1' ) then if ls_XYDLXX=' ' then ls_XYDLXX:='西成药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_XYDLXX:=ls_XYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; end if; if lv_YPDLBH='2' then if lv_YFMC00='配方颗粒药房' then if ls_ZYDLXX_1=' ' then ls_ZYDLXX_1:='颗粒 '||ls_FYCKMC||' '||ls_XHXX00; else ls_ZYDLXX_1:=ls_ZYDLXX_1||' '||ls_FYCKMC||ls_XHXX00; end if; else if ls_ZYDLXX_2=' ' then ls_ZYDLXX_2:='草药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_ZYDLXX_2:=ls_ZYDLXX_2||' '||ls_FYCKMC||ls_XHXX00; end if; end if; end if; end if; --其它医院--end-- fetch CUR_CK into ls_FYCKBH,ls_FYCKMC; end loop; Close CUR_CK; else open CUR_CK_WJ; fetch CUR_CK_WJ into ls_FYCKBH,ls_FYCKMC; while CUR_CK_WJ%found loop ls_XHXX00:=' '; open CUR_SF_CKPDXH_WJ ; fetch CUR_SF_CKPDXH_WJ into lv_DLXH00,lv_YFMC00,lv_FYCK00,lv_FYDLXH,lv_PHBZ00,lv_ZYDJS0,lv_YPDLBH; while CUR_SF_CKPDXH_WJ%found loop if trim(lv_PHBZ00) = '2' then --phbz00='2'发药序号后面应该加上"(补)"字样 lv_BZ0000 := '(补)'; end if; if ls_XHXX00=' ' then ls_XHXX00:=lv_FYDLXH||lv_BZ0000; else ls_XHXX00:=ls_XHXX00||'、'||lv_FYDLXH||lv_BZ0000; end if; if lv_ZYDJS0>0 then --是否有代煎数 ls_XHXX00 := ls_XHXX00||'(代煎'||to_char(lv_ZYDJS0)||'剂)'; end if; fetch CUR_SF_CKPDXH_WJ into lv_DLXH00,lv_YFMC00,lv_FYCK00,lv_FYDLXH,lv_PHBZ00,lv_ZYDJS0,lv_YPDLBH; end loop; Close CUR_SF_CKPDXH_WJ; if SF_XCYSFHBCK='N' then--不合并西成药 if lv_YPDLBH='0' then if ls_XYDLXX=' ' then ls_XYDLXX:='西药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_XYDLXX:=ls_XYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; elsif lv_YPDLBH='1' then if ls_CYDLXX=' ' then ls_CYDLXX:='成药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_CYDLXX:=ls_CYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; end if; elsif (lv_YPDLBH='0' ) or (lv_YPDLBH='1' ) then if ls_XYDLXX=' ' then ls_XYDLXX:='西成药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_XYDLXX:=ls_XYDLXX||' '||ls_FYCKMC||ls_XHXX00; end if; end if; if lv_YPDLBH='2' then if lv_YFMC00='配方颗粒药房' then if ls_ZYDLXX_1=' ' then ls_ZYDLXX_1:='颗粒 '||ls_FYCKMC||' '||ls_XHXX00; else ls_ZYDLXX_1:=ls_ZYDLXX_1||' '||ls_FYCKMC||ls_XHXX00; end if; else if ls_ZYDLXX_2=' ' then ls_ZYDLXX_2:='草药 '||ls_FYCKMC||' '||ls_XHXX00; else ls_ZYDLXX_2:=ls_ZYDLXX_2||' '||ls_FYCKMC||ls_XHXX00; end if; end if; end if; fetch CUR_CK_WJ into ls_FYCKBH,ls_FYCKMC; end loop; Close CUR_CK_WJ; end if; lv_PDXLMC:=ls_XYDLXX||ls_CYDLXX||ls_ZYDLXX_1||ls_ZYDLXX_2||ls_ZYDLXX_3; if SF_JRZJPH<>'N' then open CUR_WJDLYF; fetch CUR_WJDLYF into ls_XHXX00; while CUR_WJDLYF%found loop if trim(lv_PDXLMC)='' then lv_PDXLMC:=lv_PDXLMC||ls_XHXX00; else lv_PDXLMC:=lv_PDXLMC||','||ls_XHXX00; end if; fetch CUR_WJDLYF into ls_XHXX00; end loop; close CUR_WJDLYF; end if; return lv_PDXLMC; end; /