create or replace procedure SP_BQ_YPFJFY ( p_yzid00 number,--医嘱ID p_czy000 number --操作员 )as --v_yznr01 varchar(200);--医嘱内容1 v_dxsysl number;--大型输液记数器 v_zyid00 BQ_YPYZ00.ZYID00%type;--住院ID v_yplbbh BQ_YPYZ00.YPLBBH%type;--药品类别 v_clbz00 BQ_YPYZ00.CLBZ00%type;--长短医嘱 v_pcid00 BQ_YPYZ00.PCID00%type;--频次ID v_zqs000 BQ_YPYZ00.ZQS000%type;--周期数 v_zqcs00 BM_PC0000.ZQCS00%type;--周期次数 v_yfid00 BM_YPYF00.YFID00%type;--用法ID v_zqcyts BQ_YPYZ00.ZQCYTS%type;--周期草药贴数 v_jycs00 BQ_YPYZ00.JYCS00%type;--煎药次数 v_sfxmid BM_YYSFXM.SFXMID%type;--医院收费项目ID v_yzmxsm number; V_dsysf0 XT_XTCS00.VALUE0%type; v_Count number;--记数器 -- MODIFICATION HISTORY -- Person Date Comments ---qks 2009.03.12 增加限制：只有短嘱写入皮试附加费用 BQ-20090226-002 2、 ---zhangwz 2011.07.26 把药品医嘱的嘱咐内容YYZF00增加到BQ_YPYZMX.YZNR01处 by ZYYS-20110726-006. -- zhangwz 2011.08.12 增加参数控制BQ_YPYZMX.YZNR01数据写入时是否先频次后用法 by BQ-20110812-001. -- zhangwz 2011.08.18 SP_BQ_YZNRSC 增加传入参数 p_yzyf00 by BQ-20110818-002. -- zhanghr 2013.06.02 整改，变更每日一次的算法 begin SP_BQ_YZNRSC(p_yzid00); select nvl(max(VALUE0),'0') into V_dsysf0 from XT_XTCS00 where name00='BQ_DSYFJFSF'; --判断附加费用是否已产生 select Count(1) into v_Count from BQ_FJZXXM where YZID00 = p_yzid00 and CSLY00 in ('0','1','3'); if v_Count > 0 then --删除已产生的附加费用 delete from BQ_FJZXXM where YZID00 = p_yzid00 and CSLY00 in ('0','1','3'); end if; --计算同组'正常','基数药','自备药','嘱托','门诊带药'，‘产房领药’ select Count(1) into v_yzmxsm from BQ_YPYZMX where YZID00 = p_yzid00 and yzcllb in ('正常','自备药','嘱托','门诊带药','产房领药'); --填入同组条数 update BQ_YPYZ00 set YZMXSM = v_yzmxsm where YZID00 = p_yzid00; --药品没有'正常','自备药','嘱托','门诊带药' if v_yzmxsm = 0 then return; end if; --住院ID,药品类别,长短医嘱,频次ID,用法ID select ZYID00,YPLBBH,CLBZ00,PCID00,ZQS000,YFID00,ZQCYTS,JYCS00 into v_zyid00,v_yplbbh,v_clbz00,v_pcid00,v_zqs000,v_yfid00,v_zqcyts,v_jycs00 from BQ_YPYZ00 where YZID00 = p_yzid00; --周期次数 select nvl(max(ZQCS00),1) into v_zqcs00 from BM_PC0000 where PCID00 = v_pcid00; select v_zqcs00/greatest(count(distinct RQXS00),1),v_zqs000*greatest(count(distinct RQXS00),1) into v_zqcs00,v_zqs000 from BM_YZPCMX where PCID00 = v_pcid00; --西药中成药 if v_yplbbh <> '2' then --判断 select Count(1) into v_Count from BM_YPYF00 where YFID00 = v_yfid00; -- if v_Count = 1 then begin select ceil(M.MCSL00/M.Z2J000) into v_dxsysl from BQ_YPYZMX M,BM_YD0000 Y where YZID00 = p_yzid00 and M.YPNM00 = Y.YPNM00 and DXSYBZ = '1' and rownum = 1; exception when No_Data_Found then v_dxsysl := 0; end; insert into BQ_FJZXXM(ID0000,ZYID00,YZID00,CSLY00,SFXMID,SFJE00,CLBZ00, CS0000,BQSFBZ,LRR000,LRRQ00,LRSJ00,GJBM00, BZ0000,CJFS00 ) select SQ_BQ_FJZXXM_ID0000.NEXTVAL,v_zyid00,p_yzid00,'0',DCXMID,0,v_clbz00, CS0000,BQSFBZ,p_czy000,To_Char(SYSDATE,'YYYYMMDD'),To_Char(SYSDATE,'HH24:MI:SS'),GJBM00, BZ0000,CJFS00 from ( select b.DCXMID,b.BQSFBZ,b.GJBM00, b.SL0000*Decode(b.CJFS00, '0',1, --每组(帖)一次 N表示药品个数M表示大输液药品数量 '3',Decode(v_yzmxsm,1,0,1), --每组加药一次 (N-1/N)向上取整 'A',1, --每日一次 'C',1, --第二组起每组一次 '1',v_yzmxsm, --每组(帖)中的每个药品一次即每组N次 '2',v_yzmxsm-1, --每组N-1次 '4',v_dxsysl, --每组M次 '5',v_yzmxsm * v_dxsysl - 1,--每组NM-1次 'B',v_yzmxsm, --每组(帖)中的每个药品一次即每组N次 0) CS0000, decode(b.CJFS00,'A','每日一次','C','第二组起每组一次','0','每组一次','3','加药每组一次',null)BZ0000, decode(b.CJFS00,'A','A','C','C','0') CJFS00 from BQ_DCXMGZ b where b.LYXMID = v_yfid00 and b.XMLB00='0' and b.CSLY00='0' and (V_dsysf0='1' or (V_dsysf0='0' and CJFS00<>'A')) order by b.CJFS00 ) ; if v_clbz00 = '0' then --长期医嘱 update BQ_FJZXXM set YSZCS0=decode(CJFS00,'A',1,CS0000*v_zqcs00),ZCS000=decode(CJFS00,'A',1,CS0000*v_zqcs00) where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0'; update BQ_FJZXXM set ZCS000=ZCS000-1 where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0' and CJFS00='C' and exists (select 1 from BQ_FJZXXM where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0' and CJFS00='A'); else --短期医嘱 update BQ_FJZXXM set YSZCS0=v_zqs000*decode(CJFS00,'A',1,CS0000*v_zqcs00),ZCS000=v_zqs000*decode(CJFS00,'A',1,CS0000*v_zqcs00) where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0'; update BQ_FJZXXM set ZCS000=v_zqs000*(CS0000*v_zqcs00-1) where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0' and CJFS00='C' and exists (select 1 from BQ_FJZXXM where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='0' and CJFS00='A'); end if; end if; --填入皮试费用 select Count(1) into v_Count from BQ_YPYZMX a,BM_PS0000 b where a.YZID00 = p_yzid00 and b.PSID00 = a.PSID00 and v_clbz00 <> '0'; --modify by qks 2009.03.12 if v_Count > 0 then --填入皮试费用 insert into BQ_FJZXXM(ID0000, ZYID00, YZID00, CSLY00, SFXMID, SFJE00, CS0000, CLBZ00, BQSFBZ, LRR000, LRRQ00, LRSJ00, GJBM00, BZ0000 ) select SQ_BQ_FJZXXM_ID0000.NEXTVAL, v_zyid00, p_yzid00, '1', b.DCXMID, 0, b.SL0000, v_clbz00, b.BQSFBZ, p_czy000, To_Char(SYSDATE,'YYYYMMDD'), To_Char(SYSDATE,'HH24:MI:SS'), GJBM00, '皮试' from BQ_DCXMGZ b where b.XMLB00='0' and b.CSLY00='1' and b.LYXMID in ( select to_number(PSID00) from BQ_YPYZMX where YZID00 = p_yzid00 and PSID00 > 0); end if; update BQ_FJZXXM set YSZCS0=CS0000 where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00='1'; else --草药附加费 insert into BQ_FJZXXM (ID0000, ZYID00, YZID00, CSLY00, SFXMID, SFJE00, CS0000, CLBZ00, BQSFBZ, LRR000, LRRQ00, LRSJ00, GJBM00, BZ0000 ) select SQ_BQ_FJZXXM_ID0000.NEXTVAL, v_zyid00, p_yzid00, '0', b.DCXMID, 0, --b.SL0000 * Decode(b.BQSFBZ,'Y',v_zqcyts,v_jycs00), decode(v_clbz00,'0',v_zqs000,'1',1)*b.SL0000*v_zqcyts, v_clbz00, b.BQSFBZ, p_czy000, To_Char(SYSDATE,'YYYYMMDD'), To_Char(SYSDATE,'HH24:MI:SS'), GJBM00, '中药' from BQ_DCXMGZ b where b.LYXMID = v_yfid00 and b.XMLB00='0' and b.CSLY00='0'; --草药煎药费 begin select To_number(VALUE0) into v_sfxmid from XT_XTCS00 where NAME00 = 'YF_ZYJYF_SFDM'; exception when others then v_sfxmid:=0; end; if v_sfxmid>0 then insert into BQ_FJZXXM (ID0000, ZYID00, YZID00, CSLY00, SFXMID, SFJE00, CS0000, CLBZ00, BQSFBZ, LRR000, LRRQ00, LRSJ00, GJBM00, BZ0000 ) SELECT SQ_BQ_FJZXXM_ID0000.NEXTVAL, v_zyid00, p_yzid00, '3', v_sfxmid, 0, decode(v_clbz00,'0',v_zqs000,'1',1)*v_jycs00, v_clbz00, 'N', p_czy000, To_Char(SYSDATE,'YYYYMMDD'), To_Char(SYSDATE,'HH24:MI:SS'), GJBM00, '中药' FROM BM_YYSFXM WHERE SFXMID=v_sfxmid; end if; update BQ_FJZXXM set YSZCS0=CS0000 where ZYID00=v_zyid00 and YZID00=p_yzid00 and CSLY00 in ('0','3'); end if; --删除附加费用表次数为0 delete from BQ_FJZXXM where YZID00=p_yzid00 and (CS0000=0 or ZCS000=0); --计算收费单价 update BQ_FJZXXM J set SFJE00=(select NVL(SFJE00,SFXX00) from BM_YYSFXM F where J.SFXMID=F.SFXMID) where YZID00 = p_yzid00; exception when No_Data_Found then Raise_Application_Error(-20001,substrb(NVL(SQLERRM, '原因不明出错')||'!*'|| 'SP_BQ_YPFJFY:p_yzid00'||To_Char(p_yzid00)||'p_czy000'||To_Char(p_czy000),1,220)); end;