CREATE OR REPLACE PROCEDURE SP_SST_YNYY_MZGHKS( P_ZDBH00 in varchar2, --终端编号 P_FZXBH0 in varchar2, --参保机构编号 P_YYJGDM in varchar2, --医院机构代码 P_CARDTYPE in varchar2, --0 代表院内卡 1 代表社保卡 P_CARDNO in varchar2, --IC卡号 P_ERRMSG out varchar2, --出错信息 R_CUR1 out PKG_YY_QRY.MY_CURSOR --结果集游标 --BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号 BH0000:编号 ) AS -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2014.05.05 create YS_FZJMXSYGSJD varchar2(100); LS_KSSJCE number(10); LS_JSSJCE number(10); LS_SFJSRQ varchar2(2); LS_COUNT0 number(10); LS_GHMZSJ varchar2(8); LS_GHKSSJ varchar2(8); LS_GHJSSJ varchar2(8); LS_ERRORS varchar2(40); LS_BRID00 BM_BRXXB0.BRID00%type; LS_CARDNO IC_YHXX00.ICKH00%type; LS_ZYZT00 varchar2(10); begin LS_KSSJCE:=0; --开始时间差 LS_JSSJCE:=0; --结束时间差 LS_COUNT0:=0; --计数 LS_SFJSRQ:='Y';--是否有结束日期 LS_GHMZSJ:=to_char(sysdate,'YYYYMMDD'); LS_ERRORS:='1'; if nvl(P_CARDNO,'0')<>'0' then select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual; begin select BRID00 into LS_BRID00 from ic_yhxx00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when NO_DATA_FOUND then P_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!'; return; end; select ZYZT00 Into LS_ZYZT00 from vw_ys_brxxb0 where BRID00=LS_BRID00; if LS_ZYZT00='1' then P_ERRMSG:='该病人为住院病人,不能在门诊就诊!'; return; end if; end if; --分诊界面是否只显示一个时间段内的医生 N:否 Y: 是 ,M可显示排班时间可延长一个小时 默认为N begin select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='YS_FZJMXSYGSJD'; exception when others then YS_FZJMXSYGSJD:='N' ; end; if YS_FZJMXSYGSJD = 'Y' then LS_KSSJCE:=1; --排班结束时间后半小时 LS_JSSJCE:=3; --排班时间开始前1.5小时 LS_SFJSRQ:='Y'; elsif YS_FZJMXSYGSJD = 'M' then LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=3; --排班时间开始前1.5小时 LS_SFJSRQ:='Y'; elsif YS_FZJMXSYGSJD = 'K' then LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=2; --排班时间开始前1小时 LS_SFJSRQ:='Y'; else LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=0; LS_SFJSRQ:='N'; end if; LS_GHKSSJ:=to_char((sysdate-LS_KSSJCE/48),'HH24:mi:ss'); LS_GHJSSJ:=to_char((sysdate+LS_JSSJCE/48),'HH24:mi:ss'); select count(1) into LS_COUNT0 from ( Select distinct b.GHKSMC BMMC00,a.BMBH00,a.PXXH00,a.BH0000 from VW_YS_KSGHHB b,BM_BMBM00 a where b.GHKSBH=a.BMBH00 and PBLX00='0' and b.GHMZSJ=LS_GHMZSJ and b.GHJSSJ>=LS_GHKSSJ AND B.TZBZ00='0' and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')) order by a.PXXH00,a.BH0000 ); if LS_COUNT0<=0 then P_ERRMSG:='没有排班记录,不能操作！'; return; else open r_cur1 for ----BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号 BH0000:编号 select distinct b.GHKSMC BMMC00,a.BMBH00,a.PXXH00,a.BH0000 from VW_YS_KSGHHB b,BM_BMBM00 a where b.GHKSBH=a.BMBH00 and PBLX00='0' and b.GHMZSJ=LS_GHMZSJ and b.GHJSSJ>=LS_GHKSSJ AND B.TZBZ00='0' and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')) order by a.PXXH00,a.BH0000; end if; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'获取有效预约科室失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'获取有效预约科室失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255); end; CREATE OR REPLACE PROCEDURE SP_SST_YNYY_MZGHYS( P_ZDBH00 in varchar2, --终端编号 P_FZXBH0 in varchar2, --参保机构编号 P_YYJGDM in varchar2, --医院机构代码 P_CARDTYPE in varchar2, --0 代表院内卡 1 代表社保卡 P_CARDNO in varchar2, --IC卡号 P_BMBH00 in varchar2, --部门编号=SP_SST_YNYY_MZGHKS.R_CUR1.BMBH00 P_ERRMSG out varchar2, --出错信息 R_CUR1 out PKG_YY_QRY.MY_CURSOR --结果集游标 --GHLBMC:挂号类别名称 GHKSBH:挂号科室编码 YSYGBH:医生编号 YSXM00:医生姓名 XKH000:胸卡号 XSXH00：显示序号 GHHBID:挂号号表id GHZKMC:专科名称 CKMC00:窗口名称 ) AS -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2014.05.05 create YS_FZJMXSYGSJD varchar2(100); LS_KSSJCE number(10); LS_JSSJCE number(10); LS_SFJSRQ varchar2(2); LS_COUNT0 number(10); LS_GHMZSJ varchar2(8); LS_GHKSSJ varchar2(8); LS_GHJSSJ varchar2(8); LS_ERRORS varchar2(40); LS_BRID00 BM_BRXXB0.BRID00%type; LS_CARDNO IC_YHXX00.ICKH00%type; begin LS_KSSJCE:=0; --开始时间差 LS_JSSJCE:=0; --结束时间差 LS_COUNT0:=0; --计数 LS_SFJSRQ:='Y';--是否有结束日期 LS_GHMZSJ:=to_char(sysdate,'YYYYMMDD'); LS_ERRORS:='1'; if nvl(P_CARDNO,'0')<>'0' then select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual; begin select BRID00 into LS_BRID00 from ic_yhxx00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when NO_DATA_FOUND then P_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!'; return; end; end if; --分诊界面是否只显示一个时间段内的医生 N:否 Y: 是 ,M可显示排班时间可延长一个小时 默认为N begin select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='YS_FZJMXSYGSJD'; exception when others then YS_FZJMXSYGSJD:='N' ; end; if YS_FZJMXSYGSJD = 'Y' then LS_KSSJCE:=1; --排班结束时间后半小时 LS_JSSJCE:=3; --排班时间开始前1.5小时 LS_SFJSRQ:='Y'; elsif YS_FZJMXSYGSJD = 'M' then LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=3; --排班时间开始前1.5小时 LS_SFJSRQ:='Y'; elsif YS_FZJMXSYGSJD = 'K' then LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=2; --排班时间开始前1小时 LS_SFJSRQ:='Y'; else LS_KSSJCE:=2; --排班结束时间后1个小时 LS_JSSJCE:=0; LS_SFJSRQ:='N'; end if; LS_GHKSSJ:=to_char((sysdate-LS_KSSJCE/48),'HH24:mi:ss'); LS_GHJSSJ:=to_char((sysdate+LS_JSSJCE/48),'HH24:mi:ss'); select count(1) into LS_COUNT0 from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c where a.GHYSBH=b.YGBH00 and a.GHLBBH=c.LBBH00 and a.GHKSBH=P_BMBH00 and a.GHMZSJ=LS_GHMZSJ and a.GHJSSJ>=LS_GHKSSJ AND A.TZBZ00='0' and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')); if LS_COUNT0<=0 then P_ERRMSG:='没有排班记录,不能操作！'; return; else open r_cur1 for --GHLBMC:挂号类别名称 GHKSBH:挂号科室编码 YSYGBH:医生编号 YSXM00:医生姓名 XKH000:胸卡号 XSXH00：显示序号 GHHBID:挂号号表id GHZKMC:专科名称 CKMC00:窗口名称 select distinct b.ZWXM00||'('||decode(instr(LBMC00,'号'),0,c.LBMC00,substr(c.LBMC00,1,instr(LBMC00,'号')-1))||')' as GHLBMC, a.GHKSBH, b.ZWXM00 as YSXM00,b.XKH000,c.XSXH00,b.YGBH00 as YSYGBH,a.GHHBID,GHZKMC,CKMC00 from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c where a.GHYSBH=b.YGBH00 and a.GHLBBH=c.LBBH00 and a.GHKSBH=P_BMBH00 and a.GHMZSJ=LS_GHMZSJ and a.GHJSSJ>=LS_GHKSSJ AND A.TZBZ00='0' and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')) order by c.XSXH00,b.XKH000; end if; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'获取有效排班医生失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'获取有效排班医生失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255); end;