-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YF_CFPSXX_BLDJFY -- Generated 2016/6/2 10:53:17 from SD_HOSPITAL@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.202.9)(PORT = 1521))) (CONNECT_DATA = (SID = dyhis1))) CREATE OR REPLACE PROCEDURE sp_yf_cfpsxx_bldjfy ( ad_LSH000 in number, --配送地址流水号 as_GHH000 in char, --挂号号 as_CFLSH0 in char, --处方流水号 as_FLAG00 in char, --1 新增 0 删除 as_SFDJ00 in char, --是否代煎 as_DJFS00 in char, --代煎方式 0 瓶装 1 包装 as_SFPS00 in char, --1 配送 0 不配送 ad_CZY000 in number, --操作员 as_CZBZ00 in char, --1 已收费改代煎 2 已发药改代煎 3 配送不代煎处方改配送代煎 as_SYSMSG out varchar2, as_USEMSG out varchar2, ad_CZYKS0 IN NUMBER default 0 --操作员科室,或也可以称为登录科室 ) as ls_count0 number(3); lv_FSBZ00 char(1); R_SF_BRPSDZ BM_BRPSDZ%rowtype; ls_CZRQ00 char(8); ls_CZSJ00 char(8); lv_YPDLBH YF_MZCF00.YPDLBH%type; lv_ZYTS00 YF_MZCF00.ZYTS00%type; lv_DJFS00_OLD SF_CFPSXX.DJFS00%type; lv_CFZT00 YF_MZCF00.CFZT00%TYPE; V_CFLSH0 YF_MZCF00.CFLSH0%TYPE; V_CFLSH1 YF_MZCF00.CFLSH0%TYPE; V_YFBMBH YF_MZCF00.YFBMBH%TYPE; V_DJH000 YF_MZCF00.DJH000%TYPE; lv_YYID00 XT_YYXX00.YYID00%type; v_CFID00 varchar2(500); lv_CFSRBZ YF_MZCF00.CFSRBZ%type; lv_JDGZ_RESULT varchar2(400); lv_SF_CFPSSYYY XT_XTCS00.VALUE0%TYPE; lv_GHKS_YYID00 XT_YYXX00.YYID00%TYPE; lv_YPMC00 YF_MZCFMX.YPMC00%TYPE; lv_YFBMBH BM_BMBM00.BMBH00%TYPE; ls_YPDLBH SF_CFPSXX.YPDLBH%TYPE; lv_SFGF00 CHAR(1); --增加膏方处方标志 20151113 Fengcx ls_FYDLXH number(4); ls_DLXH00 number(10); ls_count1 number(3); ls_count2 number(3); -- MODIFICATION HISTORY -- Person Date Comments -- linzy 2015.05.18 create by MZSF-20150429-002 -- linzy 2015.06.14 已收费改代煎补收代煎费用，已发药改代煎不补收代煎费用。已收费补录后不发药 by YF-20150614-001 -- linzy 2015.06.19 新增加处方BCCFH0改为空值，避免无法发送康美 by -- qks 2015.08.31 增加西成药配送功能,新增参数SF_CFPSYPDLBH控制 by MZSF-20150828-001 -- qks 2015.09.07 1、如果该处方是通过医生工作站开单的，SF_CFPSXX中.SHZT00=2，而不是为0; 2、也要支持跨院区收费配送,新增入参ad_CZYKS0处理 by MZSF-20150828-001 -- qks 2015.09.10 已收费未发药处方,在处方队列表中要生成冲销记录 by MZSF-20150828-001 -- daihq 2015.09.30 处方队列表生成冲销记录时增加取FYDLXH的值 -- fengcx 2015.11.13 增加处理膏方处方标志的康美配送 YF-20151026-002 Fengcx -- gaoyj 2015.11.27 收费处方转代煎或配送的自动冲销记录增加获取原处方号的FYDLXH by MZSF-20151127-004 -- fengcx 2016.01.21 增加浓缩翻煎标志默认为N by YF-20160121-001 -- gaoyj 2016.05.17 增加as_CZBZ00=‘3’配送不代煎处方改配送代煎操作 by MZSF-20160513-003 -- fengcx 2016.05.27 修正SF_CFPSXX表中CFSFLY字段值3改为0 -- fengcx 2016.06.03 修正已发送康美成功的配送不代煎处方禁止继续改代煎配送 by MZSF-20160602-001 -- --------- ------------ --------------------------------------------------------------------------------- begin select to_char(sysdate,'HH24:MI:SS'),to_char(sysdate,'YYYYMMDD') into ls_CZSJ00,ls_CZRQ00 from dual; select count(*) into ls_count0 from SF_CFPSXX where CFLSH0=as_CFLSH0 and SFDJ00='1' and CXBZ00='Z'; if ls_count0>0 then as_USEMSG:='处方代煎限制'; as_SYSMSG:='该处方已代煎,不允许修改！'; rollback; return; end if; select nvl(max(YPDLBH),'') into ls_YPDLBH from yf_mzcf00 where CFLSH0=as_CFLSH0; --获取分院参数地址 select nvl(max(VALUE0),'N') into lv_SF_CFPSSYYY from XT_XTCS00 where NAME00='SF_CFPSSYYY'; --获取挂号所在分院 --改为取操作员登录科室所在分院 if nvl(ad_CZYKS0,0)>0 then select trim(max(YYID00)) into lv_GHKS_YYID00 from bm_bmbm00 where BMBH00=ad_CZYKS0; else select B.YYID00 into lv_GHKS_YYID00 from SF_BRXXB0 A,BM_BMBM00 B where A.JZKS00=B.BMBH00 and A.GHH000=as_GHH000; end if; --判断是否配送功能 if instr(','||trim(lv_SF_CFPSSYYY)||',',','||trim(lv_GHKS_YYID00)||',')<=0 and lv_SF_CFPSSYYY<>'N' then as_USEMSG:='配送(代煎)处方发送虚拟药房出错！'; as_SYSMSG:='该分院未启用配送功能！'; rollback; return; end if; --取虚拟药房 select nvl(max(XSXH00),0) into lv_YFBMBH from BM_TYZD00 where ZDMC00 like '虚拟%药房' and BH0000=trim(lv_GHKS_YYID00) and DJ0000=decode(ls_YPDLBH,'2','2','0') and ls_YPDLBH in ('0','1','2'); if as_DJFS00='0' and as_SFDJ00='1' then select nvl(max(XSXH00),0) into lv_YFBMBH from BM_TYZD00 where ZDMC00='虚拟中心药房' and BH0000=trim(lv_GHKS_YYID00); end if; if lv_YFBMBH=0 then as_USEMSG:='配送(代煎)处方发送虚拟药房出错！'; as_SYSMSG:='未设置虚拟药房！'; rollback; return; end if; select nvl(max(YPMC00),'0') into lv_YPMC00 from YF_MZCFMX A where CFLSH0=as_CFLSH0 and not exists(select 1 from YF_YPKCXX where YFBMBH=lv_YFBMBH and YPNM00=A.YPNM00); if lv_YPMC00<>'0' then as_USEMSG:='配送(代煎)处方发送虚拟药房出错！'; as_SYSMSG:='药品['||lv_YPMC00||'未隶属！'; rollback; return; end if; if as_FLAG00='1' then select count(*) into ls_count0 from SF_CFPSXX where CFLSH0=as_CFLSH0 and SFDJ00='1' and CXBZ00='Z'; --select nvl(max(FSBZ00),'0') into lv_FSBZ00 from SF_CFPSXX where CFLSH0=as_CFLSH0; if ls_count0>0 then as_USEMSG:='处方代煎限制'; as_SYSMSG:='该处方已代煎,不允许修改！'; rollback; return; end if; --********************************* --修正已发送康美成功的配送不代煎处方禁止继续改代煎配送 2016.06.03 if as_CZBZ00='3' then select count(*) into ls_count0 from SF_CFPSXX where CFLSH0=as_CFLSH0 and CXBZ00='Z' and FSBZ00='1' and SFDJ00='0'; if ls_count0>0 then as_USEMSG:='配送不代煎处方改代煎配送操作错误'; as_SYSMSG:='该处方: '||as_CFLSH0||' 已发送成功至康美智慧药房,不允许进行改代煎配送操作！'; rollback; return; end if; end if; --增加处理膏方处方标志的康美配送 YF-20151026-002 Fengcx --修正开单方式（医生开单或护士代录）20151113 Fengcx --处方输入标志CFSRBZ:0医生,2收费处 select A.YFBMBH,A.DJH000,A.ZYTS00,A.YPDLBH,A.CFZT00,B.YYID00,DECODE(A.YSGZH0,A.CZY000,'0','2') CFSRBZ,TO_CHAR(NVL(A.SFGF00,'0')) into V_YFBMBH,V_DJH000,lv_ZYTS00,lv_YPDLBH,lv_CFZT00,lv_YYID00,lv_CFSRBZ,lv_SFGF00 from YF_MZCF00 A,BM_BMBM00 B where CFLSH0=as_CFLSH0 and A.YFBMBH=B.BMBH00; /* select A.YFBMBH,A.DJH000,A.ZYTS00,A.YPDLBH,A.CFZT00,B.YYID00,CFSRBZ into V_YFBMBH,V_DJH000,lv_ZYTS00,lv_YPDLBH,lv_CFZT00,lv_YYID00,lv_CFSRBZ from YF_MZCF00 A,BM_BMBM00 B where CFLSH0=as_CFLSH0 and A.YFBMBH=B.BMBH00; */ if lv_CFZT00<>'2' and as_CZBZ00='2' then as_USEMSG:='处方代煎限制'; as_SYSMSG:='该处方未发药,不允许配送代煎！'; rollback; return; end if; if as_CZBZ00='1' and lv_CFZT00<>'1' then as_USEMSG:='处方代煎限制'; as_SYSMSG:='该处方未收费,请在收费界面设置代煎！'; rollback; return; end if; if as_SFDJ00='1' and lv_YPDLBH<>'2' then as_USEMSG:='处方代代煎限制'; as_SYSMSG:='不是中药处方,不允许设置代煎！'; rollback; return; end if; if lv_YPDLBH='2' and as_SFDJ00='1' then lv_JDGZ_RESULT := SF_SF_CFDJGZ(as_CFLSH0); if nvl(lv_JDGZ_RESULT,' ')<>' ' then as_USEMSG:='有药品不符合代煎规则！'; as_SYSMSG:=lv_JDGZ_RESULT; rollback; return; end if; end if; end if; -- update YF_MZCFMX set BCCXSL=YPZSL0 where CFLSH0=as_CFLSH0 and SFZBY0='N' and YPZSL0>ZCXSL0; --判断是否有隶属 select nvl(max(YPMC00),'0') into lv_YPMC00 from YF_MZCFMX A where CFLSH0=as_CFLSH0 and not exists(select 1 from YF_YPKCXX where YFBMBH=lv_YFBMBH and YPNM00=A.YPNM00); if lv_YPMC00<>'0' then as_USEMSG:='配送(代煎)处方发送虚拟药房出错！'; as_SYSMSG:='药品['||lv_YPMC00||'未隶属！'; rollback; return; end if; if as_CZBZ00='2' then SP_YF_MZCFTY(as_CFLSH0,ad_CZY000,ad_CZY000,V_CFLSH0,'0','N'); --复制新的记录 select SF_YK_GETLSH('MZYF_MZCFH') into V_CFLSH1 from dual; insert into YF_MZCF00(CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, CFZT00, CZY000, CFSRBZ, PFR000, FHR000, SRRQ00, SRSJ00, CFZJE0, BRDZ00, DJH000, FJFDJH, CYJE00, BCCFH0, TSBZBH, CFDGS0) select V_CFLSH1, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, '0' ,ad_CZY000, '0' , PFR000, FHR000,ls_CZRQ00,ls_CZSJ00, CFZJE0, BRDZ00, V_DJH000 , 0 , CYJE00, as_CFLSH0, TSBZBH,CFDGS0 from YF_MZCF00 where CFLSH0=as_CFLSH0; --被冲销处方流水号 insert into YF_MZCFMX(CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, YPZSL0, CFDW00, ZHL000, CXBZ00, SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, BZ0000, ZXCS00, F2J000) select SQ_YS_CFMX00_CFID00.NEXTVAL,V_CFLSH1, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, YPZSL0, CFDW00, ZHL000, 'Z' , SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, BZ0000, ZXCS00, F2J000 from YF_MZCFMX where CFLSH0=as_CFLSH0 ; --备注 end if; --冲销处方 if as_CZBZ00='1' then V_CFID00:=''; for F_CFID in (select * from YF_MZCFMX where CFLSH0=as_CFLSH0) loop V_CFID00:=V_CFID00||F_CFID.CFID00||'*'; end loop; SP_YS_CFTY(as_CFLSH0,V_CFID00,ad_CZY000,1,V_CFLSH1,'N'); --已收费未发药处方,在处方队列表中要生成冲销记录 for dl in ( select CFLSH0,CFZJE0,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,BCCFH0,CFDGS0,YFBMBH from YF_MZCF00 where CFLSH0 in (select CFLSH0 from YF_MZCF00 where BCCFH0=as_CFLSH0) and CFZT00 in ('0','1','5','6') and (nvl(fyrq00,'20991231')='20991231') and CFZJE0<0 --and exists (select 1 from YF_CFDL00 where CFLSH0=as_CFLSH0) ) loop select SQ_YF_CFDL00_DLXH00.nextval into ls_DLXH00 from dual; Insert into YF_CFDL00(DLXH00,CFLSH0,YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000,CFDGS0,PHBZ00,PHRQ00,BYLX00,SFZDDY) select ls_DLXH00,dl.cflsh0,YFBMBH,dl.cfzje0,dl.ghid00,dl.ghh000,dl.brid00,dl.brxm00,dl.xb0000,dl.csrq00,PYCKBH,FYCKBH,'0',dl.CFDGS0,'3',ls_CZRQ00,BYLX00,decode(BYLX00,'1','0','1') from YF_CFDL00 where CFLSH0=as_CFLSH0 and rownum=1; ---收费处方转代煎或配送的自动冲销记录增加获取原处方号的FYDLXH 2015.11.27 select count(1) into ls_count1 from YF_CFDL00 where dlxh00=ls_DLXH00 and fydlxh=0; if ls_count1>0 then select fydlxh into ls_FYDLXH from yf_cfdl00 where cflsh0=as_CFLSH0 and YPJE00>0; update yf_cfdl00 set fydlxh=ls_FYDLXH where dlxh00=ls_DLXH00; end if; end loop; /* --已收费未发药处方,在处方队列表中要生成冲销记录 for dl in ( select CFLSH0,CFZJE0,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,BCCFH0,CFDGS0,YFBMBH from YF_MZCF00 where CFLSH0 in (select CFLSH0 from YF_MZCF00 where BCCFH0=as_CFLSH0) and CFZT00 in ('0','1','5','6') and (nvl(fyrq00,'20991231')='20991231') and CFZJE0<0 --and exists (select 1 from YF_CFDL00 where CFLSH0=as_CFLSH0) ) loop Insert into YF_CFDL00(DLXH00,CFLSH0,YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000,CFDGS0,PHBZ00,PHRQ00,BYLX00,SFZDDY) select SQ_YF_CFDL00_DLXH00.nextval,dl.cflsh0,YFBMBH,dl.cfzje0,dl.ghid00,dl.ghh000,dl.brid00,dl.brxm00,dl.xb0000,dl.csrq00,PYCKBH,FYCKBH,'0',dl.CFDGS0,'3',ls_CZRQ00,BYLX00,decode(BYLX00,'1','0','1') from YF_CFDL00 where CFLSH0=as_CFLSH0 and rownum=1; end loop; */ end if; --生成代煎费用 已发药的处方先收代煎费用再改为代煎方式 不自动生成代煎费用 if as_SFDJ00='1' and (as_CZBZ00='1' or as_CZBZ00='3') then if as_CZBZ00='3' then SP_SF_CFSZDJ(as_CFLSH0,'0',ad_CZY000,'N',as_DJFS00,'门诊代煎|'||trim(as_CFLSH0),as_SYSMSG,as_USEMSG); else SP_SF_CFSZDJ(V_CFLSH1,'0',ad_CZY000,'N',as_DJFS00,'门诊代煎|'||trim(V_CFLSH1),as_SYSMSG,as_USEMSG); end if; if nvl(as_USEMSG,' ')<>' ' then rollback; return; end if; end if; if as_CZBZ00<>'3' then --更改冲销费用单号 update YF_MZCF00 set DJH000=V_DJH000 where BCCFH0=as_CFLSH0; --冲销负数处方 update YF_MZCF00 set CFZT00 = decode(SFKSLY,'Y','1','6'), JZRQ00 = ls_CZRQ00, JZSJ00 = ls_CZSJ00 where BCCFH0 = as_CFLSH0 and CFZT00 in ('0','5') and CFZJE0<0; --设置病人配送处方信息 --院内设置代煎默认SFFSKM=N 由药房代煎系统(执行过程SP_SF_YNDJFSKM)确认SFFSKM --院内代煎处方 CFSFLY='0' 普通院内配送为0 支付宝微信则为1 代煎系统生成记录=3 --处理所有膏方处方的发送康美配送标志均默认为'N' YF-20151026-002 Fengcx --原来发送康美配送标志取值为：decode(as_SFDJ00,'0','Y','N') if ad_LSH000<>0 then insert into SF_CFPSXX( PSLSH0, GHH000, CFLSH0, SF0000, CS0000, XQ0000, JD0000, XXDZ00, SHRXM0, MOBILE, TEL000, YB0000, CZRQ00, CZSJ00, CZY000, SFDJ00, SFPS00, DJFS00, ZYTS00, YPDLBH, YYID00, SHZT00, CXBZ00, CFSFLY, SFFSKM,SFGF00,FJ0000,NS0000) select SQ_SF_CFPSXX_PSLSH0.NextVal,as_GHH000,V_CFLSH1, SF0000, CS0000, XQ0000, JD0000, XXDZ00, SHRXM0, MOBILE, TEL000, YB0000, ls_CZRQ00, ls_CZSJ00,ad_CZY000, as_SFDJ00,as_SFPS00,as_DJFS00, decode(ls_YPDLBH,'2',lv_ZYTS00,0),lv_YPDLBH,lv_YYID00,decode(ls_YPDLBH,'2',decode(lv_CFSRBZ,'0',decode(lv_SFGF00,'1','2','0'),'2'),'0','2','0'),'Z', decode(as_SFDJ00,'0','0','1','0'),decode(as_SFDJ00,'0',decode(lv_SFGF00,'1','N','Y'),'N'),lv_SFGF00,'N','N' --修正SF_CFPSXX表中CFSFLY字段值3改为0 2016.05.27 from BM_BRPSDZ where LSH000=ad_LSH000; else insert into SF_CFPSXX(PSLSH0, GHH000, CFLSH0, CZRQ00, CZSJ00, CZY000, SFDJ00, DJFS00, SFPS00, ZYTS00, YPDLBH, YYID00, SHZT00,CXBZ00, CFSFLY, SFFSKM,SFGF00,FJ0000,NS0000) values(SQ_SF_CFPSXX_PSLSH0.NextVal,as_GHH000,V_CFLSH1, ls_CZRQ00, ls_CZSJ00,ad_CZY000, as_SFDJ00, as_DJFS00, as_SFPS00, decode(ls_YPDLBH,'2',lv_ZYTS00,0), lv_YPDLBH,lv_YYID00,decode(ls_YPDLBH,'2',decode(lv_CFSRBZ,'0',decode(lv_SFGF00,'1','2','0'),'2'),'0','2','0'), 'Z',decode(as_SFDJ00,'0','0','1','0'),decode(as_SFDJ00,'0',decode(lv_SFGF00,'1','N','Y'),'N'),lv_SFGF00,'N','N'); --修正SF_CFPSXX表中CFSFLY字段值3改为0 2016.05.27 end if; --更改处方药房 SP_SF_CFPSYF(V_CFLSH1,as_SYSMSG,as_USEMSG,ad_CZYKS0); if nvl(as_USEMSG,' ')<>' ' then rollback; return; end if; --更改新处方状态 update YF_MZCF00 set CFZT00 = '1', JZRQ00 = ls_CZRQ00, JZSJ00 = ls_CZSJ00, BCCFH0 = null where BCCFH0 = as_CFLSH0 and CFZJE0>0 and CFZT00 in ('0'); --被冲销处方状态更新 if as_CZBZ00='2' then update YF_MZCF00 set CFZT00 = '6' where CFLSH0 = as_CFLSH0 and CFZT00 = '2'; elsif as_CZBZ00='1' then update YF_MZCF00 set CFZT00 = '5' where CFLSH0 = as_CFLSH0 and CFZT00 = '1'; end if; --新处方进行收费发药处理 if as_CZBZ00='2' then update YF_MZCF00 set CFZT00='2',FYR000=ad_CZY000, FYRQ00=ls_CZRQ00,FYSJ00=ls_CZSJ00 where CFLSH0=V_CFLSH1 and CFZT00='1'; end if; elsif as_CZBZ00='3' then select count(*) into ls_count2 from sf_cfpsxx where sfdj00='0' and sfps00='1' and cxbz00='Z' and CFLSH0=as_CFLSH0; if ls_count2>0 then update sf_cfpsxx set sfdj00='1',czsj00=ls_CZSJ00,czrq00=ls_CZRQ00,czy000=ad_CZY000,bz0000='配送不代煎改代煎配送' where sfdj00='0' and sfps00='1' and cxbz00='Z' and CFLSH0=as_CFLSH0; end if; end if; exception when others then as_USEMSG:='设置处方代煎/配送出现异常！'; as_SYSMSG:=sqlerrm; rollback; return; end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_YF_CFPSXX_BLDJFY