-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YJYZZX_YGBR00 -- Generated 2019-04-20 01:25:39 from SD_HOSPITAL@KFYY CREATE OR REPLACE PROCEDURE sp_bq_yjyzzx_ygbr00 ( Pczy000 in number, --操作员 Pzyid00 in number, --住院流水号 Pbqh000 in number, --病区号 Pzxrq00 in date , --提取日期 Pbctqid in number, --本次提取ID Pczyxm0 in char , --操作员姓名 Pjflbid in number, --计费类别 Psfyxqf in char , --是否允许欠费,护理医嘱无条件允许欠费 Pcjksbh in char default '', --采集科室编号 默认 '' PYSTJDZ in char default 'N' --是否住院医生提交医嘱操作动作 Y 是 ) as Vsysdate date; Vcounter number(10,2); Vcscoun number; Vjzsjch char(16); Vreturn number(10); --函数的返回值 Vjzrqsj date; --本次医嘱提取截至日期(含时间) Vyzsjfg char(8); --医嘱时间分割点 Vczfjfy char(1); --存在附加费用 Vbqdsfy varchar2(20); --病区代收其他科室执行的诊疗单的费用 Vbqdsbf char(1); --病区代收其他科室（部分）执行的诊疗单的费用 Verrmsg varchar2(255); Ecustom exception; --错误 VYJDJH0 YJ_YW0000.YJDJH0%type; VYJFZH0 YJ_YW0000.YJFZH0%type; Vyzmxid BQ_YJYZLS.YZMXID%type; Vzlxmid BM_ZLZD00.ZLXMID%type; Vmcsl00 BQ_YJYZLS.MCSL00%type; Vcs0000 BQ_YJYZLS.CS0000%type; Vzxks00 BM_BMBM00.BMBH00%type; VKSH000 BM_BMBM00.BMBH00%type; VZYH000 ZY_BRXXB0.ZYH000%type; VXM0000 ZY_BRXXB0.XM0000%type; VFBBH00 BM_BRXXB0.FBBH00%type; VBRID00 ZY_BRXXB0.BRID00%type; VDJH000 ZY_BRFY00.DJH000%type; ZY_SFSYFPXMZMYHJM CHAR(1); SF_SFSYSFXMZMYHJM CHAR(1); Vjydzjsf CHAR(1); --检验单直接收费 Vbqjydtxm CHAR(1); --检验单产生条形码 Vzxqzkf CHAR(1); --执行时强制扣费 Vxzbkszx CHAR(1); --非药医嘱是否仅限本科室执行 VHZYZXMST VARCHAR2(100); Vsfykbz ZY_BRFY00.SFYKBZ%type; --是否预扣标志 '0':要预扣,不允许为负'1':不预扣,不允许为负 --'2':要预扣,允许为负'3':不预扣,允许为负 VYBQH00 ZY_BRXXB0.YBQH00%type; VSFMZJZ ZY_BRXXB0.SFMZJZ%type; VZZYS00 ZY_BRXXB0.ZZYS00%TYPE; --主治医生 --本次提取的某个病人的非常规医技单(数量>1或组合项目) cursor C_fcgyjd is select YZMXID,ZLXMID,MCSL00,CS0000,ZXKS00 from BQ_YJYZLS where ID0000=Pbctqid and ZYID00=Pzyid00 and YZCLLB in ('正常') and (CS0000>1 or ZLXMID>=90000000) --cs0000周期计算次数 and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=BQ_YJYZLS.YZMXID AND -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )) and ( --增加过滤有预约的检查治疗科室在住院医生医嘱提交的时候就已经生成那个了YJ_YW0000.XMZT00='6' 的医嘱记录 ((CLBZ00='1') and not exists (select 1 from YJ_YW0000 where ZYGHID=Pzyid00 and XMZT00='6' and trim(YZCFID)=to_char(BQ_YJYZLS.YZMXID))) or (CLBZ00<>'1') ) ; -- and (ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' -- or exists (select 1 from JY_BBCJKS where ZLXMID=BQ_YJYZLS.ZLXMID and KSBH00=Pbqh000)) ; --JY_BBCJKS中诊疗项目在病区不被执行 --超过每天收费次数上限的医技单,限bm_pc0000.ZQDW00值为"小时"的 cursor C_csxyjd is select a.YZMXID,a.ZLXMID,a.MCSL00, b.SFCSSX*(TRUNC(a.JZRQSJ,'DD')-TRUNC(a.TQRQSJ,'DD'))+least(b.SFCSSX,ceil((to_date(to_char(a.TQRQSJ,'YYYYMMDD')||'23:59:59','YYYYMMDDHH24:MI:SS')-a.TQRQSJ)*24/C.HSZQCD*C.ZQCS00)) SFSX00 from BQ_YJYZLS a,BM_ZLZD00 b,BM_PC0000 c where a.ID0000=Pbctqid and a.ZYID00=Pzyid00 and a.ZLXMID=b.ZLXMID and a.MCSL00>(b.SFCSSX*(TRUNC(a.JZRQSJ,'DD')-TRUNC(a.TQRQSJ,'DD')) +least(b.SFCSSX,ceil((to_date(to_char(a.TQRQSJ,'YYYYMMDD')||'23:59:59','YYYYMMDDHH24:MI:SS')-a.TQRQSJ)*24/C.HSZQCD*C.ZQCS00))) --第1天要单独再计算 and a.PCID00=c.pcid00 and c.ZQDW00='小时' and b.SFCSSX>0 and ( ((a.CLBZ00='1') and not exists (select 1 from YJ_YW0000 where ZYGHID=Pzyid00 and XMZT00='6' and trim(YZCFID)=to_char(a.YZMXID))) or (a.CLBZ00<>'1') ); --本次提取的某个病人的常规医技单(非组合项目) cursor C_zlxmid is select Y.YJDJH0,Y.ZLXMID from YJ_YW0000 Y,BQ_YJYZLS L where Y.TQPC00=Pbctqid and Y.ZYGHID=Pzyid00 and L.ID0000=Pbctqid and L.ZYID00=Pzyid00 and trim(Y.YZCFID)=to_char(L.YZMXID) and L.ZLXMID<90000000 --and L.CS0000<=1 and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=L.YZMXID and -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )) ; -- and (L.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' -- or exists (select 1 from JY_BBCJKS where ZLXMID=L.ZLXMID and KSBH00=Pbqh000)); --JY_BBCJKS中诊疗项目在病区不被执行 -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.07.23 往医技业务表中插入BWMC00字段 -- zhr 2007.08.22 改“修改医嘱的提取时间”语句的写法 exists 改为 in -- qks 2007.10.19 增加医嘱的首次执行日期和时间 -- qks 2007.12.26 往表zy_fymx00中加入FLAG00,DJID00;往表BQ_JZXM00中加入YZMXID -- chenqw 2008.10.12 增加BBID00字段 -- CSF 2009.06.05 bq_yjyz00增加zxys00,yj_yw0000的执行医生改从bq_yjyz00.zxys00,同时处理会诊费非本病区也可以直接扣费。 -- qks 2009.08.19 插入医技非组合项目业务明细表,去掉条件and Y.YJFZH0 is NULL -- qks 2009.08.27 插入医技非组合项目业务明细表,改用游标C_zlxmid处理,解决组合项目只插入主项目问题(要更新VW_YJ_YWMXXM) -- qks 2009.10.20 属于外科室的附加费用，BMBH填入CH0000字段NVL(TO_number(X.CH0000),VKSH000)-->改为填入VKSH000 --往表zy_fymx00中加入DJID00,如果是YJ_YWJJ00表，值改用YJ_YWJJ00.ID0000 -- qks 2009.11.30 新增参数SF_SFSYSFXMZMYHJM -- qks 2009.12.29 帐户已停用或冻结，不允许执行非药医嘱！ -- liuj 2010.07.15 新增参数 BQ_YZZXSFZYXZXBKS -- chenyw 2010.09.02 增加过程参数来判断是否执行科室是否是病区，并且限制JY_BBCJKS的项目病区不能执行 -- qks 2010.09.09 根据BM_ZLZD00.SFCSSX来限制执行的收费次数上限 -- zhangwz 2010.11.02 2010.09.02 chenyw 做的限制病区执行的增加一个病区进行过滤 Pbqh000 实际上为 SP_BQ_YJYZZX_NOHL.pCJKS00 by BQ-20101101-002 -- chenyw 2010.11.08 增加一个采集科室参数 -- qks 2011.01.05 发生调价时修正BQ_YJYZJJ.JJJE00金额 BQ-20110106-001 -- CSF 2011.01.10 住院医生工作站开具非药品医嘱时如果频次选择 'st' 时进行YJ_YW0000表的插入时,jzbz00的值的为Y 其余时刻为N.(原有的是全部为N) -- zhangwz 2011.06.17 相关业务表增加YEXM00数据的插入. -- zhanghr 2011.06.29 修正BQ_KSWZXFYYZBQYXSFBZ='Y"的情况下，病区的附加费用的执行科室为医技科室的BUG --qks 2011.08.22 暂时先恢复回去 -- qks 2011.08.22 琐BQ_YJYZ00表,防止非药医嘱同时执行,造成重复收费. -- qks 2011.08.25 病区的的附加费用,使用独立的收费单据号;同时恢复 zhanghr 2011.06.29修改内容。 -- zhangwz 2011.11.08 手术室医嘱执行时有传入采集科室数据，解决这种情况下BQ_YJYZ00.CJKS00 is null 的记录没有执行成功的问题 by SSMZ-20111108-001. -- liuj 2011.12.26 病区执行医嘱速度慢,该过程中的一段话 --婴儿部分重新赋值SFDJH0 该语句对应的 Y.SFDJH0=VDJH000 改为Y.SFDJH0+0=VDJH000 by BQ-20111220-001 -- liuj 2012.01.10 会诊医嘱的项目执行时写入zy_fymx00表的zxks00字段不能为Vksh000，改为取yj_yw0000里的yjksbh。by BQ-20111229-001 -- zhangwz 2012.05.10 增加参数 PYSTJDZ 用于处理住院医生医嘱提交非药短嘱的时候直接生成YJ_YW0000表XMZT00=6的数据 by BQ-20120503-002 -- dsm 2013.04.28 写入BQ_YJYZ00.ZXRSC0 for BQ-20130428-001; -- dsm 2013.06.03 省二执行时生成条码（BQ_YZZX_JYDZJSF=M） for BQ-20130521-002 -- dsm 2013.08.21 写入YJ_YW0000.Sqid00 for YJ-20130819-001 -- dsm 2013.10.11 把BWBH00写入YJ_YW0000 for ZYYS-20131009-002 -- liuj 2014.01.23 问题:由病区护士执行后，写入YJ_YW0000表时，直接处于已执行状态，执行人及执行日期也都有值，但ZXSL00还是为0 for BQ-20140111-001 -- dsm 2014.04.28 YJ_YW0000.JZBZ00的写入修改，当JZBZ00为非空时直接用BQ_YJYZ00.JZBZ00 for YJ-20140415-001 -- dsm 2014.05.09 相关业务表写入YSZID0 for BQ-20140509-001 -- linzy 2014.07.15 修正写入ZY_FYXM00时XMDJ00加入trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM)参数判断，避免与计算HJJE00的XMDJ00不一致，导致XMDJ0*XMSL00<>HJJE00。by BQ-20140715-001 -- dsm 2014.08.12 借床病人BM_BQKS00的判断都加ZY_BRXXB0.YBQBH00 for BQ-20140807-001 -- dsm 2017.09.29 武夷山民政救助病人护理和床位优惠 for ZYSF-20170922-001 -- zhengzk 2017.10.27 过滤病区不扣费项目 for BQ-20171018-001 -- dsm 2018.05.22 预撤销医嘱不能执行 for BQ-20180509-001 -- zhengzk 2018.05.25 增加病区代收某个（也可以是某几个）科室费用 for BQ-20180517-001 -- dsm 2018.07.05 条形码生成完后再做个校验 for YJ-20180705-001 -- dsm 2019.04.20 修正ZY_FYMX00.KDYS00=0问题 1、增加变量VZZYS00，2、变量取值规则ZY_BRXXB0 NVL(DECODE(ZZYS00, NULL, DECODE--(SXYS00,NULL,ZRYS00,0,ZRYS00,SXYS00),0,DECODE(SXYS00,NULL,ZRYS00,0,ZRYS00,SXYS00),ZZYS00),0) 3、原来插入0，修改为插入VZZYS00 for BQ-20190420-001 -- ------- --------------- --------------------------------------------------------------------------------------------------- begin Vsysdate:=sysdate; Vbqdsfy:='N'; --获取参数 东南眼科套餐 --ZY_SFSYFPXMZMYHJM:='N'; select count(*) into Vcounter from xt_xtcs00 where name00='ZY_SFSYFPXMZMYHJM' and UPPER(trim(value0))='Y'; if Vcounter>0 then --select UPPER(trim(value0)) into ZY_SFSYFPXMZMYHJM from xt_xtcs00 where name00='ZY_SFSYFPXMZMYHJM'; ZY_SFSYFPXMZMYHJM:='Y'; else --VHZYZXMST:='N'; ZY_SFSYFPXMZMYHJM:='';--注意要为空 end if; Vczfjfy :='N'; if Psfyxqf='Y' then Vsfykbz:='2'; else Vsfykbz:='0'; end if; --取非本区执行的诊疗单是否也要记帐的参数 begin select trim(upper(VALUE0)) into VHZYZXMST from XT_XTCS00 where NAME00='BQ_HZYZXMST'; exception when others then VHZYZXMST:='N'; end; --取非药医嘱是否仅限本科室执行的参数 begin select trim(upper(VALUE0)) into Vxzbkszx from XT_XTCS00 where NAME00='BQ_YZZXSFZYXZXBKS'; exception when others then Vxzbkszx:='N'; end; begin--执行非药医嘱时,检验单直接收费,省二 select substrb(Upper(VALUE0),1,1) into Vjydzjsf from XT_XTCS00 where NAME00='BQ_YZZX_JYDZJSF'; exception when OTHERS then Vjydzjsf:='N'; end; begin--检验单产生条形码,省二 select substrb(Upper(VALUE0),1,1) into Vbqjydtxm from XT_XTCS00 where NAME00='BQ_YZZX_JYDTXM'; exception when OTHERS then Vbqjydtxm:='N'; end; begin --本科室执行的非药医嘱，执行时强制扣费 select trim(upper(VALUE0)) into Vzxqzkf from XT_XTCS00 where NAME00='BQ_FYYZZXQZKF'; exception when others then Vzxqzkf:='N'; end; IF Vzxqzkf='Y' then Vsfykbz:='2'; end if; --判定临时表有没有申请记录 select count(*) into Vcounter from BQ_YJYZLS where ID0000=Pbctqid and ZYID00=Pzyid00; if Vcounter=0 then return; end if; --非药医嘱的分割时间 select count(VALUE0) into Vcounter from XT_XTCS00 where NAME00='BQ_FYYZSJFG'; if Vcounter<>1 then Verrmsg:='系统参数:医嘱时间分割点错误'; raise Ecustom ; else select substr(VALUE0,1,8) into Vyzsjfg from XT_XTCS00 where NAME00='BQ_FYYZSJFG'; end if; --执行的截至日期时间 if Pzxrq00 is not NULL then Vjzrqsj:=TO_date(to_char(Pzxrq00,'YYYYMMDD')||Vyzsjfg,'YYYYMMDDHH24:MI:SS'); Vjzsjch:=to_char(Pzxrq00,'YYYYMMDD')||Vyzsjfg; end if; select DQKS00,ZYH000,BRID00,XM0000,nvl(YBQH00,0),nvl(SFMZJZ,'0') , NVL(DECODE(ZZYS00, NULL, DECODE(SXYS00,NULL,ZRYS00,0,ZRYS00,SXYS00), 0,DECODE(SXYS00,NULL,ZRYS00,0,ZRYS00,SXYS00),ZZYS00),0) into VKSH000,VZYH000,VBRID00,VXM0000,VYBQH00,VSFMZJZ,VZZYS00 from ZY_BRXXB0 where ZYID00=Pzyid00; select FBBH00 into VFBBH00 from BM_BRXXB0 where BRID00=VBRID00; select count(1) into Vcounter from zy_brzhxx where brid00=VBRID00 and SFDJ00<>'0'; if Vcounter>0 then Verrmsg:='帐户已停用或冻结，不允许执行非药医嘱！'; raise Ecustom; end if; if VSFMZJZ='1' then ZY_SFSYFPXMZMYHJM:='Y'; end if; --琐BQ_YJYZ00表 UPDATE BQ_YJYZ00 SET LRSJ00=LRSJ00 WHERE ZYID00=PZYID00 and YZZT00 in ('1','0','2'); --获取参数 使用收费项目专门优惠减免方案 select count(*) into Vcounter from xt_xtcs00 where name00='SF_SFSYSFXMZMYHJM' and value0='Y'; if Vcounter>0 then SF_SFSYSFXMZMYHJM:='Y'; else SF_SFSYSFXMZMYHJM:=''; end if; --还要判断是否属于减免病人 if SF_SFSYSFXMZMYHJM ='Y' then if SF_SF_SFXMZMYHJM(VBRID00,'1')<>'1' then SF_SFSYSFXMZMYHJM:=''; --重新赋值 end if; end if; --预撤销医嘱不能执行 select count(*) into Vcounter from BQ_YJYZ00 a,BQ_YJYZLS b where a.YZMXID=b.YZMXID and b.ID0000=Pbctqid and nvl(a.YCXBZ0,'0')='1' ; if Vcounter>0 then Verrmsg:=VXM0000||'病人存在预撤销医嘱没有操作,请先处理预撤销医嘱！'; raise Ecustom; end if; --发生调价时修正BQ_YJYZJJ.JJJE00金额 add by qks 2011.01.05 if SF_SFSYSFXMZMYHJM = 'Y' or ZY_SFSYFPXMZMYHJM = 'Y' then update BQ_YJYZJJ a set JJJE00= (select decode(SF_SFSYSFXMZMYHJM,'Y',(s.SFJE00-nvl(s.JMJE00,0)),nvl(z.TCJE00,s.SFJE00)) from BQ_YJYZ00 m,XT_ZLSFGX z,BM_YYSFXM s where m.YZMXID = a.YZMXID and m.ZLXMID=z.ZLXMID and z.SFXMID=a.SFXMID and z.SFXMID=s.SFXMID and rownum=1) where YZMXID in (select YZMXID from BQ_YJYZLS where ID0000=Pbctqid and zyid00=Pzyid00 and YZCLLB in ('正常')) and exists (select 1 FROM BQ_YJYZ00 m,XT_ZLSFGX z,BM_YYSFXM s where m.YZMXID = a.YZMXID and m.ZLXMID=z.ZLXMID and z.SFXMID=a.SFXMID and z.SFXMID=s.SFXMID and s.sfje00>0 and a.JJJE00<>decode(SF_SFSYSFXMZMYHJM,'Y',s.sfje00-nvl(s.JMJE00,0),s.sfje00)); end if; for v_csxyjd IN C_csxyjd loop update BQ_YJYZLS a set MCSL00=v_csxyjd.SFSX00 where a.ID0000=Pbctqid and a.ZYID00=Pzyid00 and a.YZMXID=v_csxyjd.YZMXID and a.ZLXMID=v_csxyjd.ZLXMID and a.MCSL00=v_csxyjd.MCSL00; end loop; if Vbqdsfy='N' then--如果病区外执行的非药医嘱不收费，那么附加费需要另外收取 --收取病区外执行执行但属于病区附加费用(增加单位), --收取长嘱的附加费用 (BQ_YJYZJJ指的是每次的附加费用) if Vxzbkszx='N' then insert into BQ_JZXM00 (ZYID00 , CLBZ00, XMLB00, SFXMID, CS0000, SFJE00 , LRRXM0, LRRQ00, LRSJ00 , QSRQ00, BZ0000 , LYBZ00, GJBM00,YZMXID,YSZID0) select Pzyid00 , '1' , '3' ,F.SFXMID,SUM(Z.MCSL00*Z.CS0000*F.SFCS00), F.JJJE00, Pczyxm0,to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'),to_char(Pzxrq00,'YYYYMMDD'), '诊疗附加费','1',F.GJBM00,Y.YZMXID,Y.YSZID0 from BQ_YJYZLS Z,VW_BQ_YJFJXM F,BQ_YJYZ00 Y where F.YZMXID=Z.YZMXID and Z.YZMXID=Y.YZMXID and Z.ZYID00=Pzyid00 and Z.ID0000=Pbctqid and Z.YZCLLB in ('正常')and F.BQSFBZ='Y' and Y.ZYID00=Pzyid00 and Y.CLBZ00='0' and Y.YZZT00 in ('1','2')and Y.ZXKS00 not in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00)) and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=Z.YZMXID AND -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )) -- and (Z.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' or -- exists (select 1 from JY_BBCJKS where ZLXMID=Z.ZLXMID and KSBH00=Pbqh000)) group by Z.ZYID00,F.SFXMID,F.JJJE00,F.GJBM00,Y.YZMXID,Y.YSZID0; if not sql%notfound then Vczfjfy:='Y'; end if; --收取短嘱附加费用((BQ_YJYZJJ指的是所有的附加费用)) insert into BQ_JZXM00 (ZYID00 , CLBZ00, XMLB00, SFXMID, CS0000, SFJE00 , LRRXM0, LRRQ00, LRSJ00 , QSRQ00, BZ0000 , LYBZ00, GJBM00, YZMXID,YSZID0) select Pzyid00 , '1', '3',F.SFXMID,F.SFCS00, F.JJJE00, Pczyxm0,to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'),to_char(Pzxrq00,'YYYYMMDD'), '诊疗附加费','1',F.GJBM00,Y.YZMXID,Y.YSZID0 from VW_BQ_YJFJXM F,BQ_YJYZ00 Y where F.YZMXID=Y.YZMXID and F.BQSFBZ='Y' and Y.ZYID00=Pzyid00 and Y.CLBZ00='1' and Y.YZZT00='1' and ZXKS00 not in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00)) and exists (select 1 from BQ_YJYZLS Z where Z.YZMXID=Y.YZMXID and Z.ZYID00=Pzyid00 and Z.ID0000=Pbctqid and Z.YZCLLB in ('正常')); if not sql%notfound then Vczfjfy:='Y'; end if; end if; if Vxzbkszx='Y' then insert into BQ_JZXM00 (ZYID00 , CLBZ00, XMLB00, SFXMID, CS0000, SFJE00 , LRRXM0, LRRQ00, LRSJ00 , QSRQ00, BZ0000 , LYBZ00, GJBM00,YZMXID,YSZID0) select Pzyid00 , '1' , '3' ,F.SFXMID,SUM(Z.MCSL00*Z.CS0000*F.SFCS00), F.JJJE00, Pczyxm0,to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'),to_char(Pzxrq00,'YYYYMMDD'), '诊疗附加费','1',F.GJBM00,Y.YZMXID,Y.YSZID0 from BQ_YJYZLS Z,VW_BQ_YJFJXM F,BQ_YJYZ00 Y where F.YZMXID=Z.YZMXID and Z.YZMXID=Y.YZMXID and Z.ZYID00=Pzyid00 and Z.ID0000=Pbctqid and Z.YZCLLB in ('正常')and F.BQSFBZ='Y' and Y.ZYID00=Pzyid00 and Y.CLBZ00='0' and Y.YZZT00 in ('1','2')and Y.ZXKS00<>VKSH000--not in (select KSH000 from BM_BQKS00 where BQH000=Pbqh000) and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=Z.YZMXID AND -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )) -- and (Z.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' or --exists (select 1 from JY_BBCJKS where ZLXMID=Z.ZLXMID and KSBH00=Pbqh000)) group by Z.ZYID00,F.SFXMID,F.JJJE00,F.GJBM00,Y.YZMXID,Y.YSZID0; if not sql%notfound then Vczfjfy:='Y'; end if; --收取短嘱附加费用((BQ_YJYZJJ指的是所有的附加费用)) insert into BQ_JZXM00 (ZYID00 , CLBZ00, XMLB00, SFXMID, CS0000, SFJE00 , LRRXM0, LRRQ00, LRSJ00 , QSRQ00, BZ0000 , LYBZ00, GJBM00, YZMXID,YSZID0) select Pzyid00 , '1', '3',F.SFXMID,F.SFCS00, F.JJJE00, Pczyxm0,to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'),to_char(Pzxrq00,'YYYYMMDD'), '诊疗附加费','1',F.GJBM00,Y.YZMXID,Y.YSZID0 from VW_BQ_YJFJXM F,BQ_YJYZ00 Y where F.YZMXID=Y.YZMXID and F.BQSFBZ='Y' and Y.ZYID00=Pzyid00 and Y.CLBZ00='1' and Y.YZZT00='1' and ZXKS00<>VKSH000 AND-- not in (select KSH000 from BM_BQKS00 where BQH000=Pbqh000) and exists (select 1 from BQ_YJYZLS Z where Z.YZMXID=Y.YZMXID and Z.ZYID00=Pzyid00 and Z.ID0000=Pbctqid and Z.YZCLLB in ('正常')); if not sql%notfound then Vczfjfy:='Y'; end if; end if; end if; --同一个时间点的医嘱，只生成一张申请单，不同时间点的医嘱，生成多张申请单 --处理常规的医技业务单(数量为1，非组合项目) insert into YJ_YW0000 (YJDJH0, YJFZH0, YJKSBH, ZYGHID, YEXM00, ZLXMID, KDYS00, KDKSBH,KDBQ00,YZCFID,SL0000, KDRQ00, KDSJ00, LRXM00, JZBZ00, FBZXBZ, BWMC00, ZDID00, ZDMC00, XMZT00, MZZYBZ, BZ0000, TQPC00,DCBZ00,BQSFBZ, SM0000,BBID00, ZXYS00,CJKSBH,SQID00,BWBH00,YSZID0) select SQ_YJ_YW0000_YJDJH0.NEXTVAL,NULL,Y.ZXKS00,Y.ZYID00,Y.YEXM00,Y.ZLXMID,Y.KZYS00, Y.KSH000,Y.BQH000,Y.YZMXID,L.MCSL00, to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), Pczyxm0,decode(Y.JZBZ00,'Y','Y','N','N',decode(Y.PCMC00,'st','Y','ST','Y','N')), Z.FBZXBZ, Y.BWMC00, Y.ZDID00, Y.ZDMC00,decode(trim(PYSTJDZ),'Y','6','1'), '1',Y.BZ0000, Pbctqid,'0',BQJZBZ, Y.BQZY00,Y.bbid00 , decode(nvl(Y.ZXYS00,0),0,Y.KZYS00,Y.ZXYS00),Y.CJKS00,Y.SQID00,Y.BWBH00,Y.YSZID0 from BM_ZLZD00 Z,BQ_YJYZ00 Y,BQ_YJYZLS L where Z.ZLXMID=Y.ZLXMID and Y.YZMXID=L.YZMXID and Y.ZYID00=Pzyid00 and Y.YZCLLB in ('正常') and YZZT00 in ('1','2') and L.ZYID00=Pzyid00 and L.ID0000=Pbctqid and L.CS0000<=1 and L.ZLXMID<90000000 and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=L.YZMXID AND -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )) and ( ((L.CLBZ00='1') and not exists (select 1 from YJ_YW0000 where ZYGHID=Pzyid00 and XMZT00='6' and trim(YZCFID)=to_char(L.YZMXID))) or (L.CLBZ00<>'1') ); -- and (L.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' or exists (select 1 from JY_BBCJKS where ZLXMID=L.ZLXMID and KSBH00=Pbqh000)); --处理非常规医技业务单(数量>1或组合项目) open C_fcgyjd; loop fetch C_fcgyjd into Vyzmxid,Vzlxmid,Vmcsl00,Vcs0000, Vzxks00; exit when C_fcgyjd%notFOUND; Vcscoun:=1; while Vcscoun<=Vcs0000 loop if Vzlxmid>=90000000 then--组合项目,拆分后要同组 select SQ_YJ_YW0000_YJFZH0.NEXTVAL into VYJFZH0 from DUAL where ROWNUM=1;--取分组号 --组合项目医嘱生成医技单,规则:明细项的类别相同的,生成一张单据,主表用类别,明细表子项目 --组合项目要进行估价和插入明细项目附加费用和 insert into YJ_YW0000 ( YJDJH0, YJFZH0, YJKSBH, ZYGHID, YEXM00, ZLXMID, KDYS00, KDKSBH, KDBQ00, YZCFID, SL0000, KDRQ00, KDSJ00, LRXM00, JZBZ00, FBZXBZ, BWMC00, ZDID00, ZDMC00, XMZT00, MZZYBZ, BZ0000, TQPC00, DCBZ00, BQSFBZ, SM0000,BBID00,ZXYS00,CJKSBH,SQID00,BWBH00,YSZID0) select SQ_YJ_YW0000_YJDJH0.NEXTVAL,VYJFZH0,Y.ZXKS00,Y.ZYID00,Y.YEXM00,Z.SJXMID,Y.KZYS00, Y.KSH000,Y.BQH000, Y.YZMXID,Vmcsl00,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'),Pczyxm0, decode(Y.JZBZ00,'Y','Y','N','N',decode(Y.PCMC00,'st','Y','ST','Y','N')), Z.FBZXBZ, Y.BWMC00, Y.ZDID00, Y.ZDMC00,decode(trim(PYSTJDZ),'Y','6','1'), '1', Y.BZ0000, Pbctqid, '0', Y.BQJZBZ, Y.BQZY00,Y.bbid00,decode(nvl(Y.ZXYS00,0),0,Y.KZYS00,Y.ZXYS00),Y.CJKS00,Y.SQID00,Y.BWBH00,Y.YSZID0 from VW_BQ_ZHMXSJ Z,BQ_YJYZ00 Y where Z.ZHXMID=Vzlxmid and Y.YZMXID=Vyzmxid; --插入归类的收费项目 insert into YJ_YWJJ00 (ID0000,YJDJH0,YJFZH0,SFXMID,SFXX00,JJJE00, SFSX00,CXBZ00,GDBZ00,FJFYBZ,SL0000,SFCS00,GJBM00,SFLB00,JMJE00) select SQ_YJ_YWJJ00_ID0000.NEXTVAL, Y.YJDJH0,VYJFZH0 ,G.SFXMID,B.SFXX00, decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(G.JJJE00,b.SFJE00),decode(B.SFJE00,0,G.JJJE00,B.SFJE00)),--调整计价金额 B.SFSX00,'Z',G.GDBZ00,G.FJFYBZ,Vmcsl00,--一张单据上的数量 G.SFCS00,G.GJBM00,B.JSXM00,round(decode(SF_SFSYSFXMZMYHJM,'Y',nvl(b.JMJE00,0),0)*Vmcsl00*G.SFCS00,2) from BQ_YJYZJJ G,YJ_YW0000 Y,BM_YYSFXM B where G.YZMXID=Vyzmxid and Y.YJFZH0=VYJFZH0 and G.MXXMID=Y.ZLXMID and G.SFXMID=B.SFXMID and ( Vbqdsfy='Y' or (Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000) or (Vbqdsfy='N' and G.FJFYBZ='N' and ((Vxzbkszx='N' and Y.YJKSBH not in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and Y.YJKSBH<>VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST )) ) and not exists (select 1 from BM_TYZD00 q where ZDMC00 = '病区不扣费项目' and instr(','||q.MC0000||',', ','||Y.ZLXMID||',') > 0) ; --补充插入没有归类的收费项目 select max(YJDJH0) into VYJDJH0 from YJ_YW0000 where YJFZH0=VYJFZH0; insert into YJ_YWJJ00 (ID0000, YJDJH0, YJFZH0, SFXMID, SFXX00, JJJE00, SFSX00, CXBZ00, SL0000, SFCS00,GJBM00,SFLB00,JMJE00) select SQ_YJ_YWJJ00_ID0000.NEXTVAL, VYJDJH0, VYJFZH0, G.SFXMID,B.SFXX00, decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(G.JJJE00,b.SFJE00),decode(B.SFJE00,0,G.JJJE00,B.SFJE00)),--调整计价金额 B.SFSX00, 'Z', Vmcsl00,--一张单据上的数量 G.SFCS00,G.GJBM00,B.JSXM00,round(decode(SF_SFSYSFXMZMYHJM,'Y',nvl(b.JMJE00,0),0)*Vmcsl00*G.SFCS00,2) from BQ_YJYZJJ G,BM_YYSFXM B where G.YZMXID=Vyzmxid and G.SFXMID=B.SFXMID and G.MXXMID not in (select ZLXMID from YJ_YW0000 where YJFZH0=VYJFZH0) and ( Vbqdsfy='Y' or (Vxzbkszx='N' and VZXKS00 in (select KSH000 from BM_BQKS00 where BQH000 in (Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and VZXKS00=VKSH000) OR (Vbqdsfy='N' and G.FJFYBZ='N' and ((Vxzbkszx='N' and VZXKS00 not in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and VZXKS00<>VKSH000)))) and not exists (select 1 from BM_TYZD00 q where ZDMC00 = '病区不扣费项目' and instr(','||q.MC0000||',', ','||G.MXXMID||',') > 0); --组合项目插入明细(增加一级) insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID) select SQ_YJ_YWMX00_ID0000.NEXTVAL,YJDJH0,S.ZXMID0 from YJ_YW0000 Y,VW_BQ_ZHMXXJ S ,VW_BM_ZLSJXM X where YJFZH0=VYJFZH0 and S.ZHXMID=Vzlxmid and Y.ZLXMID=X.SJXMID and X.ZXMID0=S.ZXMID0; else--字典项目,非组合项目 insert into YJ_YW0000 ( YJDJH0, YJFZH0, YJKSBH, ZYGHID, YEXM00, ZLXMID, KDYS00, KDKSBH,KDBQ00,YZCFID,SL0000, KDRQ00, KDSJ00, LRXM00, JZBZ00, FBZXBZ, BWMC00, ZDID00,ZDMC00, XMZT00, MZZYBZ, BZ0000, TQPC00,DCBZ00,BQSFBZ,SM0000,BBID00,ZXYS00,CJKSBH,SQID00,BWBH00,YSZID0) select SQ_YJ_YW0000_YJDJH0.NEXTVAL,NULL,Y.ZXKS00,Pzyid00,Y.YEXM00,Vzlxmid,Y.KZYS00, VKSH000,Y.BQH000, Y.YZMXID,Vmcsl00, to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), Pczyxm0,decode(Y.PCMC00,'st','Y','ST','Y','N'), Z.FBZXBZ, Y.BWMC00, Y.ZDID00, Y.ZDMC00, decode(trim(PYSTJDZ),'Y','6','1'), '1',Y.BZ0000, Pbctqid,'0',Y.BQJZBZ, Y.BQZY00,Y.bbid00, decode(nvl(Y.ZXYS00,0),0,Y.KZYS00,Y.ZXYS00),Y.cjks00,Y.SQID00,Y.BWBH00,Y.YSZID0 from BM_ZLZD00 Z,BQ_YJYZ00 Y where Z.ZLXMID=Vzlxmid and Y.YZMXID=Vyzmxid ; end if; Vcscoun:=Vcscoun+1; end loop;--end while end loop; close C_fcgyjd;--非常规医技单处理完毕 /* --插入医技非组合项目业务明细表 --检验单自动生成YJFZH0=YJDJH0 insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID) select SQ_YJ_YWMX00_ID0000.NEXTVAL,YJDJH0,M.ZXMID0 from YJ_YW0000 Y,VW_YJ_YWMXXM M where TQPC00=Pbctqid and Y.ZLXMID=M.SJXMID and ZYGHID=Pzyid00 and M.GDXMBZ='Y' ; --and Y.YJFZH0 is NULL --插入医技非组合项目业务明细表 --如果直接写,XT_ZHMX00没使用到索引,速度慢 insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID) select SQ_YJ_YWMX00_ID0000.NEXTVAL,YJDJH0,M.ZXMID0 from YJ_YW0000 Y,VW_YJ_YWMXXM M,BQ_YJYZLS L where TQPC00=Pbctqid and Y.ZLXMID=M.SJXMID and ZYGHID=Pzyid00 and M.GDXMBZ='Y' and L.ID0000=Pbctqid and L.ZYID00=Pzyid00 and Y.YZCFID=L.YZMXID and L.CS0000<=1 and L.ZLXMID<90000000; */ --插入医技非组合项目业务明细表 FOR v_zlxmid IN C_zlxmid LOOP insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID) select SQ_YJ_YWMX00_ID0000.NEXTVAL,v_zlxmid.YJDJH0,M.ZXMID0 from VW_YJ_YWMXXM M where M.SJXMID=v_zlxmid.zlxmid and M.GDXMBZ='Y'; END LOOP; --非同组的医技单据估价 insert into YJ_YWJJ00(ID0000, YJDJH0, YJFZH0, SFXMID, SFXX00, JJJE00, SFSX00, CXBZ00, SL0000, FJFYBZ, GDBZ00, SFCS00, GJBM00 ,SFLB00,JMJE00) select SQ_YJ_YWJJ00_ID0000.NEXTVAL, Y.YJDJH0,Y.YJFZH0, S.SFXMID, M.SFXX00, decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(S.JJJE00,M.SFJE00),decode(M.SFJE00,0,S.JJJE00,M.SFJE00)) ,--调整计价金额 M.SFSX00, 'Z', Y.SL0000, S.FJFYBZ, S.GDBZ00, S.SFCS00,S.GJBM00,M.JSXM00,round(decode(SF_SFSYSFXMZMYHJM,'Y',nvl(M.JMJE00,0),0)*Y.SL0000*S.SFCS00,2) from YJ_YW0000 Y,BQ_YJYZJJ S,BM_YYSFXM M where Y.YZCFID=S.YZMXID and S.SFXMID=M.SFXMID and Y.TQPC00=Pbctqid and ZYGHID=Pzyid00 and ( Vbqdsfy='Y' or (Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000) or (Vbqdsfy='N' and (S.FJFYBZ='N' or (S.FJFYBZ='Y' and S.BQSFBZ='N')) and ((Vxzbkszx='N' and Y.YJKSBH not in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and Y.YJKSBH<>VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST ))) and not exists (select 1 from BM_TYZD00 q where ZDMC00 = '病区不扣费项目' and instr(','||q.MC0000||',', ','||Y.ZLXMID||',') > 0);-- and nvl(YJFZH0,0)<=0 --处理住院医生医嘱提交非药短嘱的时候已经生成了取消状态下的YJ_YW00000表的数据,为了病区的执行收费--begin--- if trim(PYSTJDZ)<>'Y' then begin update YJ_YW0000 a set XMZT00='1',TQPC00=Pbctqid where a.ZYGHID=Pzyid00 and a.XMZT00='6' and exists (select 1 from BQ_YJYZLS b where to_char(b.YZMXID)=trim(a.YZCFID) and b.ZYID00=a.ZYGHID and b.CLBZ00='1'); end; end if; ------------------------------end------------------------------------------- if trim(PYSTJDZ)='Y' then begin --删除不需要的数据 delete from BQ_YJYZLS where ID0000=Pbctqid and ZYID00=Pzyid00; goto ZYYZTJEND; --住院医嘱提交的时候只处理到YJ_YW0000表的生成 end; end if; --修改公费比例等，修改医保标志,总价由触发器保证 --检查病区内执行的普通医技医嘱是否正常计价 --病区内执行的项目收费 select count(*) into Vcounter from YJ_YWJJ00 X,YJ_YW0000 Y where X.YJDJH0=Y.YJDJH0 and Y.TQPC00=Pbctqid and Y.ZYGHID=Pzyid00 and ((Y.BQSFBZ='Y' and ((Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00)) ) or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST ))) and ROWNUM=1; if Vcounter>0 and Vczfjfy='Y' then--有正式费用和附加费 select SQ_ZY_BRJFB0_DJH000.NEXTVAL into VDJH000 from DUAL where ROWNUM=1; insert into ZY_FYMX00(MXID00, DJH000, XMBH00, XMMC00, XMDJ00, XMSL00, GJBM00,SFLB00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDBQ00, KDKS00, KDYS00, YSKS00, ZXKS00, ZXYS00, XMDW00,ZFBL00,FLAG00,DJID00,YEXM00,YSZID0) select SQ_ZY_FYMX00_MXID00.NEXTVAL,VDJH000,X.SFXMID,X.XMMC00,X.SFJE00,X.CS0000,X.GJBM00,Y.JSXM00, X.SFJE00*X.CS0000, X.SFJE00*X.CS0000-nvl(X.GFJE00,0)-nvl(X.JZJE00,0), nvl(X.QZFJE0,0), nvl(X.GFJE00,0), nvl(X.JZJE00,0), to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), X.QSRQ00,to_char(Vsysdate,'HH24:MI:SS'), Pbqh000,VKSH000, VZZYS00, 0,VKSH000,0,X.DW0000,X.ZFBL00, --属于外科室的附加费用，BMBH填入CH0000字段NVL(TO_number(X.CH0000),VKSH000)-->改为填入VKSH000 decode(X.BZ0000,'诊疗附加费','7','2'),X.ID0000,X.YEXM00,X.YSZID0 from BQ_JZXM00 X,BM_YYSFXM Y where X.ZYID00=Pzyid00 and X.CLBZ00='1'and JFLBID=Pjflbid and X.TZRQ00 is null and X.XMLB00='3' AND X.SFXMID=Y.SFXMID; Vcounter:=SF_BQ_MXTOFY(VDJH000, Pzyid00, VZYH000, VBRID00, VXM0000, VFBBH00, Pjflbid, Pczy000, Pbqh000, 0, Vsfykbz, Pczyxm0, Vsysdate) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 if not sql%notfound then update BQ_JZXM00 set GZRQ00=QSRQ00,TZRQ00=to_char(Vsysdate,'YYYYMMDD') where ZYID00=Pzyid00 and CLBZ00='1'and JFLBID=Pjflbid and TZRQ00 is NULL and XMLB00='3'; end if; select SQ_ZY_BRJFB0_DJH000.NEXTVAL into VDJH000 from dual where ROWNUM=1; --向住院病人费用明细表 ZY_FYMX00插入明细 insert into ZY_FYMX00(MXID00, DJH000, XMBH00, XMMC00, XMDJ00, XMSL00, GJBM00, SFLB00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDBQ00, KDKS00, KDYS00, YSKS00, ZXKS00, ZXYS00, XMDW00,ZFBL00, JMJE00, JMBZ00, FLAG00, DJID00, YEXM00,YSZID0) select SQ_ZY_FYMX00_MXID00.NEXTVAL,VDJH000,X.SFXMID,XMMC00, nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0),SFCS00*X.SL0000,X.GJBM00,S.JSXM00, round(nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0)*SFCS00*X.SL0000,2), round(nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0)*SFCS00*X.SL0000,2)-round(nvl(GFJE00,0),2)-round(nvl(JZJE00,0),2), round(nvl(QZFJE0,0),2), round(nvl(GFJE00,0),2), round(nvl(JZJE00,0),2), to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), Pbqh000,VKSH000,Y.KDYS00,nvl(B.BMBH00,0),decode(SF_BQ_HZXMPD(Y.zlxmid),1,y.yjksbh,VKSH000),decode(nvl(Y.ZXYS00,0),0,Y.KDYS00,Y.ZXYS00),/*Y.KDYS00*/S.DW0000,ZFBL00, nvl(X.JMJE00,0),decode(nvl(X.JMJE00,0),0,'0','4'),decode(X.FJFYBZ,'Y','4','1'),X.ID0000,Y.YEXM00,Y.YSZID0 from YJ_YWJJ00 X,BM_YYSFXM S,YJ_YW0000 Y,BM_YGBM00 B -- where (X.YJDJH0=Y.YJDJH0 or (X.YJFZH0=Y.YJFZH0 and X.YJFZH0 is not NULL)) where X.YJDJH0=Y.YJDJH0 and Y.TQPC00=Pbctqid and Y.ZYGHID=Pzyid00 and X.SFXMID=S.SFXMID and Y.KDYS00=B.YGBH00(+) and ((Y.BQSFBZ='Y' and ((Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00)) ) --借床的执行不了 所以加 or Y.KDBQ00=Pbqh000 or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST ))); Vcounter:=SF_BQ_MXTOFY(VDJH000, Pzyid00, VZYH000, VBRID00, VXM0000, VFBBH00, Pjflbid, Pczy000, Pbqh000, 0, Vsfykbz, Pczyxm0, Vsysdate) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 --由触发器完成实际余额和预扣余额的计算 -- if not sql%notfound then -- UPdate BQ_JZXM00 SET GZRQ00=QSRQ00,TZRQ00=to_char(Vsysdate,'YYYYMMDD') -- where ZYID00=Pzyid00 and CLBZ00='1'and JFLBID=Pjflbid and TZRQ00 is NULL and XMLB00='3'; -- end if; elsif Vcounter>0 and Vczfjfy<>'Y' then--有正式费用但无附加费 select SQ_ZY_BRJFB0_DJH000.NEXTVAL into VDJH000 from dual where ROWNUM=1; --向住院病人费用明细表 ZY_FYMX00插入明细 insert into ZY_FYMX00( MXID00, DJH000, XMBH00, XMMC00 ,XMDJ00, GJBM00 , SFLB00 ,ZFJE00 ,QZFJE0 ,XMSL00 ,HJJE00 ,GFJE00 ,JZJE00 ,CZRQ00, CZSJ00, ZXRQ00, ZXSJ00,KDBQ00,KDKS00,KDYS00,YSKS00,ZXKS00,ZXYS00,XMDW00,ZFBL00, JMJE00,JMBZ00,FLAG00,DJID00,YEXM00,YSZID0) select SQ_ZY_FYMX00_MXID00.NEXTVAL,VDJH000,X.SFXMID,XMMC00 ,nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0),X.GJBM00,S.JSXM00 ,round(nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0)*SFCS00*X.SL0000,2)-nvl(GFJE00,0)-nvl(JZJE00,0) ,nvl(QZFJE0,0)--QZFJE0, ,SFCS00*X.SL0000 ,round(nvl(decode(trim(ZY_SFSYFPXMZMYHJM||SF_SFSYSFXMZMYHJM),'Y',nvl(X.JJJE00,S.SFJE00),S.SFJE00),0)*SFCS00*X.SL0000,2)--HJJE00 ,nvl(GFJE00,0)--GFJE00 ,nvl(JZJE00,0)--JZJE00 ,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS') ,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS') ,Pbqh000,VKSH000,Y.KDYS00,nvl(B.BMBH00,0),decode(SF_BQ_HZXMPD(Y.zlxmid),1,y.yjksbh,VKSH000),decode(nvl(Y.ZXYS00,0),0,Y.KDYS00,Y.ZXYS00),/*Y.KDYS00*/S.DW0000,ZFBL00, nvl(X.JMJE00,0),decode(nvl(X.JMJE00,0),0,'0','4'),decode(X.FJFYBZ,'Y','4','1'),X.ID0000,Y.YEXM00,Y.YSZID0 from YJ_YWJJ00 X,BM_YYSFXM S,YJ_YW0000 Y,BM_YGBM00 B -- where ( X.YJDJH0=Y.YJDJH0 or (X.YJFZH0=Y.YJFZH0 and X.YJFZH0 is not NULL)) where X.YJDJH0=Y.YJDJH0 and Y.TQPC00=Pbctqid and Y.ZYGHID=Pzyid00 and X.SFXMID=S.SFXMID and Y.KDYS00=B.YGBH00(+) and ((Y.BQSFBZ='Y' and ((Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in (Pbqh000,VYBQH00)) ) --借床的执行不了 or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST ))); Vcounter:=SF_BQ_MXTOFY(VDJH000, Pzyid00, VZYH000, VBRID00, VXM0000, VFBBH00, Pjflbid, Pczy000, Pbqh000, 0, Vsfykbz, Pczyxm0, Vsysdate) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 --由触发器完成实际余额和预扣余额的计算 --MODifY BY Lindj 2003.12.31 --if not sql%notfound then -- select ZFJE00 into Vcounter from ZY_BRFY00 where DJH000=VDJH000; -- if Vcounter>0 then -- Vcounter:=SF_BQ_YKYEPD(Pzyid00,NULL); -- else -- Vcounter:=1; -- end if; -- if Vcounter<>1 then --病人押金不足,下一个帐户 -- Verrmsg:='病人金额不足，不能执行非药医嘱'; -- raise Ecustom;--病人金额不足 -- end if; --end if; elsif Vcounter=0 and Vczfjfy='Y' then --只有附加费没有正式费用 Vreturn:=SF_BQ_FJXMSF(Pzyid00,Pbqh000,Pczy000,'3',Pzxrq00); --如果账户余额不足的话，回滚并删除医技项目 if Vreturn is NULL or Vreturn <0 then--收取附加费用时出错,删除该病人的执行 ROLLBACK; Verrmsg:='病人账户余额不足'; raise Ecustom; end if;--附加费收取完毕,返回收费单据号 if Vreturn>0 then VDJH000:=Vreturn; end if; end if;--收费结束 VDJH000:=nvl(VDJH000,0); --修改病区内执行的医技单的收费单据号和标志 update YJ_YW0000 Y set (XMZT00,SFDJH0,ZXR000,ZXRXM0,ZXSL00,ZXRQ00,ZXSJ00)= (select '3',decode(count(1),0,0,VDJH000),Pczy000,Pczyxm0,Y.SL0000,--,0 BQ-20140111-001 to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS') from YJ_YWJJ00 J where Y.YJDJH0=J.YJDJH0) where TQPC00=Pbctqid and ZYGHID=Pzyid00 and ((Y.BQSFBZ='Y' and ((Vxzbkszx='N' and Y.YJKSBH in (select KSH000 from BM_BQKS00 where BQH000 in(Pbqh000,VYBQH00))) or (Vxzbkszx='Y' and Y.YJKSBH=VKSH000))) or (VHZYZXMST<>'N' and Y.ZLXMID IN (select ZLXMID FROM BQ_HZYZXMST ))); -- update YJ_YW0000 Y set SFDJH0=nvl((select DJH000 from ZY_BRFY00 where ZYID00=Pzyid00 and CFQDJH=VDJH000 and YEXM00=Y.YEXM00 and rownum=1),SFDJH0) -- where Y.ZYGHID=Pzyid00 and Y.SFDJH0=VDJH000 and YEXM00 is not null; --婴儿部分重新赋值SFDJH0 8i写法不支持 -- update YJ_YW0000 Y set (SFDJH0)=(select DJH000 from ZY_BRFY00 where ZYID00=Pzyid00 and nvl(CFQDJH,DJH000)=VDJH000 and YEXM00=Y.YEXM00 and rownum=1) -- where Y.ZYGHID=Pzyid00 and Y.SFDJH0=VDJH000 and YEXM00 is not null; --婴儿部分重新赋值SFDJH0 update YJ_YW0000 Y set (SFDJH0)=(select decode(count(1),0,VDJH000,max(DJH000)) from ZY_BRFY00 where ZYID00=Pzyid00 and nvl(CFQDJH,DJH000)=VDJH000 and YEXM00=Y.YEXM00 and rownum=1) where Y.ZYGHID=Pzyid00 and Y.SFDJH0+0=VDJH000 and YEXM00 is not null; --婴儿部分重新赋值SFDJH0 begin insert into BQ_YZZXJL (ZYID00,YZMXID, TQDJH0, DJHLB0, FYDJH0, ZT0000, CZR000, YZID00, QSRQ00, QSSJ00, JSRQ00, JSSJ00, SJJSRQ, SJJSSJ, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, SCJSRQ, ZXRXM0 ) select DisTinCT Pzyid00,J.YZMXID, Pbctqid, '1' ,VDJH000, '1', Pczy000,0, to_char(J.TQRQSJ,'YYYYMMDD'), to_char(J.TQRQSJ,'HH24:MI:SS'), --decode(YZZT00,'1',to_char(Z.QSRQSJ,'YYYYMMDD'),'2',to_char(Z.CLRQSJ,'YYYYMMDD')), --decode(YZZT00,'1',to_char(Z.QSRQSJ,'HH24:MI:SS'),'2',to_char(Z.CLRQSJ,'HH24:MI:SS')), to_char(Vjzrqsj,'YYYYMMDD'), to_char(Vjzrqsj,'HH24:MI:SS'), to_char(JZRQSJ,'YYYYMMDD'), to_char(JZRQSJ,'HH24:MI:SS'), to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'), to_char(Vsysdate,'YYYYMMDD'), to_char(Vsysdate,'HH24:MI:SS'), Z.TQRQ00||Z.TQSJ00, Pczyxm0 from BQ_YJYZLS J,VW_BQ_YJYZ00 Z--全表扫瞄 where ID0000=Pbctqid and J.YZMXID=Z.YZMXID and Z.ZYID00=Pzyid00 and exists (select 1 from BQ_YJYZ00 WHERE YZMXID=Z.YZMXID AND -- (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )); -- and (J.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' or -- exists (select 1 from JY_BBCJKS where ZLXMID=J.ZLXMID and KSBH00=Pbqh000)); exception when OTHERS then --出错，删除该病人的信息，下一个病人 if SQLCODE=-2290 then raise_application_error(-20104,substrb('病人非药医嘱已经执行！*'||SQLERRM,1,150)); else raise_application_error(-20105,substrb('生成执行记录时发生错误！*'||SQLERRM,1,150)); end if; end; --修改医嘱的提取时间 UPdate BQ_YJYZ00 Y SET (TQRQ00,TQSJ00)=(select decode(P.ZQDW00,'小时',to_char(SF_BQ_YZJZSJ(QYRQ00||QYSJ00,TQRQ00||TQSJ00,LEasT(nvl(TZRQ00||TZSJ00,Vjzsjch),Vjzsjch),HSZQCD,ZQCS00),'YYYYMMDD'),to_char(Pzxrq00,'YYYYMMDD')), decode(P.ZQDW00,'小时',to_char(SF_BQ_YZJZSJ(QYRQ00||QYSJ00,TQRQ00||TQSJ00,LEasT(nvl(TZRQ00||TZSJ00,Vjzsjch),Vjzsjch),HSZQCD,ZQCS00),'HH24:MI:SS'),Vyzsjfg)from VW_BQ_PC0000 P where Y.PCID00=P.PCID00), YZZT00=decode(CLBZ00,'1','3','0',decode(TZRQ00,NULL,'2', decode(SIGN(Vjzrqsj-TO_date(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3'))), TZRQ00=decode(CLBZ00,'0',TZRQ00,'1',to_char(Vsysdate,'YYYYMMDD')), TZSJ00=decode(CLBZ00,'0',TZSJ00,'1',to_char(Vsysdate,'HH24:MI:SS')), TZYS00=decode(CLBZ00,'0',NULL,'1',KZYS00), TZRXM0=decode(CLBZ00,'0',NULL,'1',Pczyxm0), ZXRSC0=decode(ZXRSC0,null,PCZY000,ZXRSC0) where Y.ZYID00=Pzyid00 and YZZT00 in ('1','0','2') and Y.YZMXID in (select L.YZMXID from BQ_YJYZLS L where ID0000=Pbctqid and L.ZYID00=Pzyid00 -- AND (((CJKS00 is null and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0')) and (((CJKS00 is null and ((nvl(Pcjksbh,'0')=pbqh000) or (nvl(Pcjksbh,'0')='0') or (nvl(Pcjksbh,'0')=KSH000 and YZLY00='2'))) or ( cjks00=pbqh000 and (nvl(Pcjksbh,'0')=pbqh000 or nvl(Pcjksbh,'0')='0'))) or to_char(cjks00)=nvl(Pcjksbh,'0') )); --and (L.ZLXMID not in (select ZLXMID from JY_BBCJKS) or Psfbqzx<>'Y' or --exists (select 1 from JY_BBCJKS where ZLXMID=L.ZLXMID and KSBH00=Pbqh000) )); --修改医嘱的首次执行日期、时间 UPDATE BQ_YJYZ00 Y SET ZXRQSC=TO_CHAR(VSYSDATE,'YYYYMMDD'), ZXSJSC=TO_CHAR(VSYSDATE,'HH24:MI:SS'), ZXRSC0=Pczy000 WHERE Y.ZYID00=PZYID00 AND ZXRQSC IS NULL AND YZZT00 in ('1','0','2') and Y.YZMXID in (select L.YZMXID from BQ_YJYZLS L where ID0000=Pbctqid and L.ZYID00=Pzyid00); --删除不需要的数据 delete from BQ_YJYZLS where ID0000=Pbctqid and ZYID00=Pzyid00; --取非本区执行的诊疗单是否也要记帐的参数 select upper(nvl(max(trim(VALUE0)),'N')) into Vbqdsfy from XT_XTCS00 where name00='BQ_KSWZXFYYZBQYXSFBZ'; select upper(substr(nvl(trim(VALUE0), 'N'), 0, 1)) into Vbqdsbf from XT_XTCS00 where NAME00 = 'BQ_BQDSBFKSFY'; if (Vbqdsfy='Y') or (Vbqdsbf = 'Y') then SP_BQ_YJDSF0(Pczy000,Pzyid00); end if; if Vjydzjsf='Y' then SP_BQ_JYDSF0(Pczy000,Pzyid00); end if; if Vbqjydtxm='Y' or Vjydzjsf='M' then SP_BQ_JYDTXM(Pczy000,Pzyid00); select count(distinct BRID00) into Vcounter from YJ_YW0000 where TXM000 in(select TXM000 from YJ_YW0000 b,BM_ZLZD00 c where ZYGHID=Pzyid00 and nvl(b.TXM000,' ')<>'*' and b.MZZYBZ='1' and b.ZLXMID=c.ZLXMID and c.LBBH00=4); if Vcounter>1 then Verrmsg:='检验条形码生成异常(重复),请重试!'; raise Ecustom; end if; end if; <> commit; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||Verrmsg||sqlerrm,1,220)); when Ecustom then raise_application_error(-20010,substrb(Verrmsg||'！*',1,220)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Verrmsg,1,220)); end ; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YJYZZX_YGBR00