-- Start of DDL script for SP_SST_JSDP_HQYYHY
-- Generated 26-三月-15  2:36:59 pm
-- from fw2-SD_HOSPITAL:1

-- Procedure SP_SST_JSDP_HQYYHY

CREATE OR REPLACE
procedure SP_SST_JSDP_HQYYHY(
	P_ZDBH00 in varchar2,    --终端编号
	P_FZXBH0 in varchar2,    --分中心编号
	P_CARDTYPE in varchar2,  --IC卡类型
	P_CARDNO in varchar2,    --IC卡号
	P_YJDJH0 in varchar2,    --医技单据号=SP_SST_JSDP_GETJCZ.r_cur1.YJDJH0  (合并预约时传入格式A,B 没有合并预约格式A)
	P_PBHBID in varchar2,    --排班号表id=SP_SST_JSDP_KSPBHB.r_cur1.PBHBID
	P_BYRC00 in varchar2,    --备用入参
	P_ERRMSG out varchar2,   --出错信息
	r_cur1  out pkg_fee_qry.MY_CURSOR --结果集游标
	)
	/*
	结果集游标字段说明：
PBHBID:排班号表id   HBXHID:号表序号  XH0000:序号
YYRQ00:预约日期     YYSJ00:预约时间  PBSXW0:排班时段(上午或下午)
BRXM00：预约病人    HZSJ00：候诊时间 CKMC00:诊室名称
	*/
as
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- zhangyc     2013.09.04   created;
-- zhangyc     2014.08.28   把入参数P_YYRQ00-->P_PBHBID
  LS_CARDNO  IC_YHXX00.ICKH00%type;
  LS_BRID00  BM_BRXXB0.BRID00%type;
  LS_ZT0000  IC_YHXX00.ZT0000%type;
  LS_ZYE000  SF_BRZHXX.ZYE000%type;
  LS_YGBH00  BM_YGBM00.YGBH00%type;
  LS_YGXM00  BM_YGBM00.ZWXM00%type;
  LS_XKH000  BM_YGBM00.XKH000%type;
  LS_XMZXJE  number(12,4);
  LS_DYRQ00  varchar2(30);
  LS_COUNT   number(3);
  LS_YJDJH0  YJ_YW0000.YJDJH0%type;
  LS_YJKSBH  varchar2(100);
  LS_JCLBID  varchar2(100);
  LS_JCZID0  varchar2(100);
    cursor CUR_JCBW is
  select aa.YJKSBH,aa.JCLBID,aa.JCZID0 from VW_YJ_YW0000 aa  where instrb(P_YJDJH0,aa.YJDJH0)>0
     and aa.brid00=LS_BRID00 and aa.MZZYBZ='0' and aa.jclbid>0;
begin
	 select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual;
   begin
	    select BRID00,ZT0000 into LS_BRID00,LS_ZT0000 from IC_YHXX00 where  ICKH00 =LS_CARDNO and ZT0000=1;
	    exception
		  when no_data_found then
          P_ERRMSG:='IC卡号:'||LS_CARDNO||'在本院IC_YHXX00表中未找到,请联系管理员!';
          return;
	  end;

	 --获取自助机终端编号对应的收费员原编号
  begin
	select YGBH00,ZWXM00,XKH000 into LS_YGBH00,LS_YGXM00,LS_XKH000 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00;
	exception
	  when no_data_found then
      P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员,请联系管理员!';
      return;
  end;

select count(*) into LS_COUNT from VW_YJ_JCPBYHMX a
 where  PBHBID=P_PBHBID;
if LS_COUNT<=0 then
	 P_ERRMSG:='未找到对应预约号源,无法预约！';
      return;
end if;

open r_cur1 for
select  a.PBHBID,--排班号表id
a.HBXHID,--号表序号
a.XH0000,--序号
a.YYRQ00,--预约日期
a.YYSJ00,--预约时间
a.PBSXW0,--排班时段
a.BRXM00,  --预约病人
a.HZSJ00,--候诊时间
a.CKMC00 --诊室名称
 from VW_YJ_JCPBYHMX a
 where  A.PBHBID=P_PBHBID and a.YYLB00='2'
 --AND A.ZZYYBZ='1'
 and a.jclbid='3' and
 --to_char(a.yysj00||':00')>to_char(sysdate,'HH24:MI:SS')
 a.YYRQ00||decode(length(YYSJ00),5,YYSJ00||':00',YYSJ00)>to_char(sysdate,'YYYYMMDDHH24:MI:SS')
   and a.MZZYBZ in ('0','2')
    --and nvl(a.BRXM00,' ')<>' ' --已预约号源
 and  a.BRXM00 is null --未预约号源
  order by a.XH0000;

  /*
  for AA in CUR_JCBW loop
    if LS_YJKSBH is null then
        LS_YJKSBH:=AA.YJKSBH;
        LS_JCLBID:=AA.JCLBID;
        LS_JCZID0:=AA.JCZID0;
      else
         LS_YJKSBH:=LS_YJKSBH||','||AA.YJKSBH;
         LS_JCLBID:=LS_JCLBID||','||AA.JCLBID;
         LS_JCZID0:=LS_JCZID0||','||AA.JCZID0;
      end if;

 end loop;


open r_cur1 for
select
a.PBHBID,--预序号模板ID
a.HBXHID,--号表序号
a.XH0000,--序号
a.YYRQ00,--预约日期
a.YYSJ00,--预约时间
a.HZSJ00, --候诊时段
a.BRXM00  --预约病人
from YJ_JCHBXH a ,YJ_KSPBHB b
 where
   a.PBHBID=b.PBHBID
   and a.YYRQ00=P_YYRQ00
   and instrb(LS_YJKSBH,to_char(a.YJKSBH))>0
   and (instrb(LS_JCLBID,to_char(nvl(b.JCLBID,0)))>0 or nvl(b.JCLBID,0)=0  )
   and (instrb(LS_JCZID0,to_char(nvl(b.XMWHID,0)))>0  or  nvl(b.XMWHID,0)=0)
   and a.YYLB00='2'
   and a.MZZYBZ in ('0','2')
    --and nvl(a.BRXM00,' ')<>' ' --已预约号源
 and  a.BRXM00 is null --未预约号源
 and a.YYRQ00||decode(length(YYSJ00),5,YYSJ00||':00',YYSJ00)>to_char(sysdate,'YYYYMMDDHH24:MI:SS')
 order by a.XH0000;
*/
exception
  when no_data_found then
      P_ERRMSG:='数据没有找到!'||SQLERRM;
  when others then
      P_ERRMSG:='发生未知错误'||SQLERRM;
end  SP_SST_JSDP_HQYYHY;
/

-- End of DDL script for SP_SST_JSDP_HQYYHY