--1.获取全院员工对应业务科室信息 CREATE OR REPLACE VIEW SD_HOSPITAL.V_STAFF_DICT_DEPT_HQBLOOD ( DEPT_CODE, DEPT_NAME, STAFF_NO, USER_NAME, NAME, WORK_KIND, TITLE_CODE, TITLE_NAME, PY_CODE, WB_CODE ) AS select c.BMBH00 DEPT_CODE,-- 科室代码 (select BMMC00 from BM_BMBM00 where BMBH00=c.BMBH00) DEPT_NAME,-- 科室名称 (select XKH000 from bm_ygbm00 where ygbh00=c.ygbh00) staff_no,-- 员工工号 (select XKH000 from bm_ygbm00 where ygbh00=c.ygbh00) user_name, (select zwxm00 from bm_ygbm00 where ygbh00=c.ygbh00) NAME,-- 姓名 ' ' work_kind, (select yslb00 from bm_ygbm00 where ygbh00=c.ygbh00) TITLE_CODE,-- 职称代码 (select yslbmc from bm_yslb00 where yslb00 in (select yslb00 from bm_ygbm00 where ygbh00=c.ygbh00)) TITLE_NAME,-- 员工职称 (select pysm00 from bm_ygbm00 where ygbh00=c.ygbh00) PY_CODE,-- 拼音码 (select wbsm00 from bm_ygbm00 where ygbh00=c.ygbh00) WB_CODE from XT_YHBMQX c where c.bmbh00 in (select bmbh00 from bm_bmbm00 where (CZBZ00<>'2' and bmmc00 not like '%停用%')) group by c.bmbh00,c.ygbh00 / --2.获取全院员工字典信息 CREATE OR REPLACE VIEW SD_HOSPITAL.V_STAFF_DICT_HQBLOOD ( DEPT_CODE, DEPT_NAME, USER_ID, STAFF_NO, USER_NAME, NAME, PHONE_NUMBER, ID_CARD_NUM, WORK_KIND, TITLE_CODE, TITLE_NAME, PY_CODE, WB_CODE, BIRTHDATE, SEX ) AS select a.BMBH00 DEPT_CODE,-- 科室代码 (select BMMC00 from BM_BMBM00 where BMBH00=a.BMBH00) DEPT_NAME,-- 科室名称 a.ygbh00 user_id, a.XKH000 staff_no,-- 员工工号 a.XKH000 user_name, a.ZWXM00 NAME,-- 姓名 a.dh0000 phone_number, a.sfzbh0 Id_card_num, ' ' work_kind, a.yslb00 TITLE_CODE,-- 职称代码 (select yslbmc from bm_yslb00 where yslb00=a.yslb00) TITLE_NAME,-- 员工职称 PYSM00 PY_CODE,-- 拼音码 WBSM00 WB_CODE, '' birthdate, a.xb0000 sex from BM_YGBM00 a where CZBZ00<>'2' / --3.获取全院科室字典信息 CREATE OR REPLACE VIEW SD_HOSPITAL.V_DEPARTMENT_DICT_HQBLOOD ( DEPT_CODE, DEPT_NAME, PY_CODE ) AS select a.BMBH00 DEPT_CODE,-- 科室代码 a.BMMC00 DEPT_NAME, a.pysm00 py_code from BM_BMBM00 a where CZBZ00<>'2' and a.bmmc00 not like '%停用%' and a.bmmc00 not like 'GD%' and a.bmmc00 not like '%总务科%' and a.bmmc00 not like '%保卫科%' / --4.存储过程：血液自动扣费 CREATE OR REPLACE PROCEDURE SP_BLOOD_AUTO_PERFORM_FEE( AS_SICK_ID in Varchar2,--入参: 病人SICK AS_RESIDENCE_NO in Varchar2,--入参: 病人住院号 AS_CLINIC_CODE in Varchar2,--入参: 收费诊疗代码 AS_CLINIC_NUMBER in NUMBER,--入参：扣费次数 AS_OPERAT_CODE in Varchar2,--入参：扣费执行人代码 AS_OPERAT_NAME in Varchar2,--入参：扣费执行人名称 AS_OPERAT_DEPT in Varchar2,--入参：扣费执行科室代码 RS_RETURN_FLAG out Varchar2, --出参: Y成功 N 失败 RS_MESSAGE out Varchar2 --出参：失败原因 ) as -- MODIFICATION HISTORY -- Person Date Comments -- dsm 2017.12.01 create for GGJK-20171201-001 VZYGHID ZY_BRXXB0.ZYID00%type; Vzyh000 zy_brxxb0.zyh000%type; VYSZID0 SS_YW0000.YSZID0%type; VZLXMID SS_YWMX00.SSBH00%type; VCZYXM0 BM_YGBM00.ZWXM00%type; VSSDJBH BM_ZLZD00.SSDJBH%type; VDJ0000 BM_YYSFXM.SFJE00%type; as_YJDJH0 YJ_YW0000.YJDJH0%type; VBRID00 ZY_BRXXB0.BRID00%type; VDQBQ00 ZY_BRXXB0.DQBQ00%type; VXM0000 ZY_BRXXB0.XM0000%type; VXB0000 ZY_BRXXB0.XB0000%type; VCSRQ00 ZY_BRXXB0.CSRQ00%type; vczybm0 bm_ygbm00.ygbh00%type; Ecustom exception; VCOUNT0 number(5); ls_error varchar2(200); vzlxmjc varchar2(50); begin RS_RETURN_FLAG:='N'; ls_error:='住院病人不存在'; select ZYID00,zyh000,BRID00,DQBQ00,XM0000,XB0000,CSRQ00 into VZYGHID,vzyh000,VBRID00,VDQBQ00,VXM0000,VXB0000,VCSRQ00 from ZY_BRXXB0 where brid00=to_number(AS_SICK_ID) and substrb(brzt00,1,1)='2'; ls_error:='操作员不存在YGBH00='||AS_OPERAT_CODE; select ZWXM00 into VCZYXM0 from BM_YGBM00 where xkh000=AS_OPERAT_CODE; select ygbh00 into vczybm0 from BM_YGBM00 where xkh000=AS_OPERAT_CODE; ls_error:='诊疗项目不存在ZLXMBH='||AS_CLINIC_CODE; select sum(b.sfcs00*a.sfje00) into VDJ0000 from BM_YYSFXM a,xt_zlsfgx b where a.sfxmid=b.sfxmid and b.zlxmid=AS_CLINIC_CODE; select ZLXMID,ZLXMJC into VZLXMID,VZLXMJC from BM_ZLZD00 where zlxmid=AS_CLINIC_CODE; if AS_CLINIC_NUMBER<0 then ls_error:='收费数量不能小于0!'; raise Ecustom; end if; ls_error:=' '; select SQ_YJ_YW0000_YJDJH0.NEXTVAL into as_YJDJH0 from dual where rownum=1; /* insert into YJ_YW0000(YJDJH0,SL0000,ZYGHID,MZZYBZ,ZLXMID,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00,LRXM00,XMZT00,YJLRBZ,hisyjh) values(as_YJDJH0,AS_CLINIC_NUMBER,VZYGHID,'1',VZLXMID,AS_OPERAT_DEPT,vczybm0,AS_OPERAT_DEPT,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),VCZYXM0,'1','S','HQSX'); */ insert into YJ_YW0000(YJDJH0,SL0000,BRID00,BRXM00,XB0000,CSRQ00,ZYGHID,ZYHGHH,MZZYBZ,ZLXMID,zlxmjc,kdysxm,YSSZKS,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00,LRXM00,XMZT00,YJLRBZ,HISYJH,KDBQ00,zje000) values(as_YJDJH0,AS_CLINIC_NUMBER,VBRID00,VXM0000,VXB0000,VCSRQ00,VZYGHID,vzyh000,'1',VZLXMID,vzlxmjc,AS_OPERAT_NAME,AS_OPERAT_DEPT,'1138',vczybm0,AS_OPERAT_DEPT,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),VCZYXM0,'1','S','HQSX',VDQBQ00,AS_CLINIC_NUMBER*VDJ0000); insert into YJ_YWJJ00(ID0000,YJDJH0,SFXMID,SL0000,SFCS00,JJJE00,GJBM00,SFXX00,SFSX00,SSLB00) select SQ_YJ_YWJJ00_ID0000.nextval,as_YJDJH0,a.SFXMID,AS_CLINIC_NUMBER,1,a.SFJE00,a.GJBM00,SFXX00,SFSX00,'0' from BM_YYSFXM a,XT_ZLSFGX b where b.ZLXMID=VZLXMID and a.SFXMID=b.SFXMID; /*if AS_SICK_ID='3197284' then commit; end if;*/ SP_YJ_YWZX00(as_YJDJH0,0,AS_CLINIC_NUMBER,1,AS_OPERAT_CODE,AS_OPERAT_DEPT,'Y'); select count(*) into VCOUNT0 from YJ_YW0000 where YJDJH0=as_YJDJH0 and SFDJH0>0; if VCOUNT0>0 then RS_RETURN_FLAG:='Y'; RS_MESSAGE:=as_YJDJH0; end if; exception when others then RS_RETURN_FLAG:='N'; RS_MESSAGE:=ls_error; rollback; end; / --5.存储过程：血液自动退费 CREATE OR REPLACE PROCEDURE SP_BLOOD_AUTO_BACK_PERFORM_FEE( AS_JIFEI_ID in number, --中输出的as_YJDJH0 AS_SICK_ID in varchar2,---病人ID AS_RESIDENCE_NO in varchar2,---住院号 AS_OPERATER_CODE in number, --操作员工号,不能为空 AS_OPERATER_NAME in varchar2, ---退费操作执行人姓名 AS_OPERATER_DEPT_CODE in number, --退费操作执行人科室代码 RS_RETURN_FLAG out varchar2, --出参: Y成功 N 失败 RS_MESSAGE out varchar2 --失败原因 ) as VZYGHID SS_YW0000.ZYGHID%type; VYSZID0 SS_YW0000.YSZID0%type; VZLXMID SS_YWMX00.SSBH00%type; VCZYXM0 BM_YGBM00.ZWXM00%type; VSSDJBH BM_ZLZD00.SSDJBH%type; VDJ0000 BM_YYSFXM.SFJE00%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VXKH000 BM_YGBM00.XKH000%type; vygbh00 bm_ygbm00.ygbh00%type; --VZLXMID BM_ZLZD00.ZLXMID%type; LS_ZLXMID varchar2(50); Ecustom exception; ls_error varchar2(200); VCXSL00 YJ_YW0000.SL0000%type; VZXKS00 YJ_YW0000.YJKSBH%type; as_CXDJH0 YJ_YW0000.YJDJH0%TYPE; -- Person Date Comments -- dsm 2018.05.28 begin --项目冲销 ls_error:='操作员不存在YGBH00='||AS_OPERATER_CODE; select ZWXM00 into VCZYXM0 from BM_YGBM00 where xkh000=to_char(AS_OPERATER_CODE); select XKH000 into VXKH000 from BM_YGBM00 where xkh000=to_char(AS_OPERATER_CODE); select ygbh00 into VYGBH00 FROM BM_YGBM00 where XKH000=TO_CHAR(AS_OPERATER_CODE); ls_error:='单据号不存在as_YJDJH0='||AS_JIFEI_ID; select SL0000,YJKSBH into VCXSL00,VZXKS00 from YJ_YW0000 where YJDJH0=AS_JIFEI_ID; ls_error:='操作科室与执行科室不一致不能冲销as_CZYKS0='||AS_OPERATER_DEPT_CODE; ls_error:=' '; ---SP_YJ_YWCX(AS_JIFEI_ID,VYGBH00,AS_OPERATER_DEPT_CODE,'Y',VCXSL00,-1,'N'); SP_YJ_YWCX(AS_JIFEI_ID,VYGBH00,AS_OPERATER_DEPT_CODE,'Y','N',VCXSL00,'N'); select nvl(max(YJDJH0),0) into as_CXDJH0 from YJ_YW0000 where CXDJH0=AS_JIFEI_ID and SL0000<0; if as_CXDJH0=0 then ls_error:='冲销失败，原因未知!'; --- raise Ecustom; RS_RETURN_FLAG:='N'; RS_MESSAGE:=ls_error; rollback; return; end if; RS_RETURN_FLAG:='Y'; RS_MESSAGE:=ls_error; commit; exception when Ecustom then RS_RETURN_FLAG:='N'; RS_MESSAGE:= ls_error; --as_CXDJH0:=''; rollback; when others then if ls_error=' ' then RS_MESSAGE:=sqlerrm; else RS_MESSAGE:= ls_error; end if; as_CXDJH0:=''; rollback; end; / --9.扣费项目诊疗字典 CREATE OR REPLACE VIEW SD_HOSPITAL.V_CHARGE_ITEM_DIC_HQBLOOD ( ITEM_CODE, ITEM_NAME, PRICE, ITEM_CLASS, SPECIMEN, EXEC_DEPT ) AS select a.ZLXMID Item_code ,--Varchar 20 项目代码 a.ZLXMJC Item_name ,--Varchar 50 项目名称 sum(SFJE00*sfcs00) Price, --Numeric 单价 a.LBBH00 Item_class ,--Varchar 20 项目类别、归类 (select BBMC00 from BM_BBZD00 where BBID00=a.BBID00) Specimen ,-- Varchar 20 标本类型 a.ZXKS00 Exec_dept --Varchar 20 执行科室 from BM_ZLZD00 a ,XT_ZLSFGX b,bm_yysfxm c where a.ZLXMID=b.zlxmid and c.sfxmid=b.SFXMID and c.bh0000<>'507030000001' and a.lbbh00 in ('7','4') and a.sfyxsq='Y' group by a.ZLXMID,a.ZLXMJC,a.LBBH00,a.ZXKS00,a.BBID00 /