CREATE OR REPLACE PROCEDURE SP_SF_MZCFHJGMFH
(   as_XM0000     IN   CHAR,         --姓名
    ad_GHY000     IN   NUMBER,       --挂号员编号、或操作员编号
    as_GHYXM0     IN   CHAR,         --挂号员姓名
    ad_CZYKS0     IN   NUMBER,       --操作员的科室,或挂号员的科室
    ad_GHKS00     IN   NUMBER,       --挂号科室编号
    ad_JZYS00     IN   NUMBER,       --就诊医生编号
    as_JZYSXM     IN   CHAR,         --就诊医生姓名
    ad_GHHBID     IN   NUMBER,       --挂号号表流水号
    as_YJJXH0     IN   CHAR,         --打印在挂号单的外部序列号
    as_FPXH00     IN   CHAR,         --打印在发票上的序号
    as_CZBZ00     IN   CHAR,         --挂号标志,'0':持卡病人,'1':现金病人
    as_SFYB00     IN   CHAR default '0',  --是否医保
    as_YBMC00     IN   CHAR,         --病人所属医保中心名称
    as_BRZTQK     IN   CHAR,         --病人职退情况
    as_YBKH00     IN   CHAR,   
    as_YBID00     IN   CHAR,
    as_SFLSCF     IN   CHAR default 'Y',
    ad_BRID00     IN   NUMBER,
    as_GHH000  IN OUT  VARCHAR2,     --门诊挂号号,供前台程序打印挂号单时使用
    as_YHMSG0    OUT   VARCHAR,      --存储过程提示的中文错误信息
    as_SYSMSG    OUT   VARCHAR,      --系统提示的错误信息
    as_brid00    out   number
)
as
    ls_MZID00      SF_BRXXB0.GHID00%TYPE;   --病人的挂号ID
    ls_GHLB00      BM_GHLBB0.LBBH00%TYPE;   --挂号类别编码
    ls_PJH000      SF_PJSYQK.PJH000%TYPE;   --票据流水号
    ls_GHYXTS      SF_BRXXB0.GHYXTS%TYPE;   --门诊挂号有效天数
    ls_BRID00      SF_BRXXB0.BRID00%TYPE;   --病人ID
    ls_CZRQ00      CHAR(8);
    ls_CZSJ00      CHAR(8);
    ls_YBLBBH     IC_YBBRLB.YBLB00%TYPE;   --病人所属医保中心名称对应的编号
    ls_YBZXLB     IC_YBBRLB.YBZXLB%TYPE;   --病人医保中心类别
    ls_YBBRLB     BM_BRXXB0.YBBRLB%TYPE;   --医保病人类别
    lv_FBBH00     BM_BRFBB0.FBBH00%TYPE;         --费别名称  
    ls_BRXB00     SF_BRXXB0.XB0000%TYPE;   --性别 
    ls_BRBLH0     BM_BRXXB0.BRBLH0%TYPE;   --病例号 
    ls_CSRQ00     BM_BRXXB0.BRCSRQ%TYPE;
begin
    ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
    ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
    ls_GHYXTS := 1;
    ls_YBBRLB:='0';
    ls_YBLBBH:='0';   
    lv_FBBH00:=1;  
  
    begin
        select Trim(LBBH00) into  ls_GHLB00 FROM bm_ghlbb0 where LBMC00='免费号'; --先查找是否在BM_GHLBB0中有免费的挂号类别，有取之，没有用9998
    exception
       when others then
            ls_GHLB00 := 9998; --零散处方处挂免费号的挂号类别
    end;
    
    IF as_SFYB00 = 'Y'  THEN   --是否是医保病人信息登记,'Y':医保病人
    BEGIN
        select  YBLB00,YBZXLB  into  ls_YBLBBH,ls_YBZXLB  from  IC_YBBRLB  where YBMC00 = as_YBMC00;
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
                    as_YHMSG0:='医保病人所属医保中心名称有错，系统字典维护不完整！';
                    as_SYSMSG:=SQLERRM||'Not exists(select * from ic_ybbrlb where ybmc00='||as_YBMC00||')';
                    ROLLBACK;
                    RETURN;
   END;
   --再得到医保病人类别
   BEGIN
       select  YBBRLB  into  ls_YBBRLB  from  BM_YBBRLB  where BRLBMC = as_BRZTQK and YBZXLB = ls_YBZXLB;
       EXCEPTION
           WHEN OTHERS THEN
                ls_YBBRLB:='0';
   END;
   lv_FBBH00 :=3;
   else
      ls_YBLBBH := '0';
      ls_YBZXLB := '0';
      lv_FBBH00 :=1;
   end if;
   select   SQ_SF_BRXXB0_GHID00.nextval   into  ls_MZID00   from  dual; --挂号ID
   if  as_GHH000 is null or trim(as_GHH000) = '' then
       select   SQ_SF_BRXXB0_GHH000.nextval   into  as_GHH000   from  dual;  --挂号号
   end if;
   Select   SQ_SF_PJSYQK_PJH000.nextval   into  ls_PJH000  from  dual;   --挂号单的票据号
     --先得到挂号有效天数
   BEGIN
       select  NVL(max(VAlUE0),1)      into  ls_GHYXTS   from  XT_XTCS00  where  name00 = 'GHYXTS';
          EXCEPTION
              WHEN OTHERS THEN
                  ls_GHYXTS:=1 ;       --若没有查找到,设置成1天
   END;
   ls_BRID00:=ad_BRID00;  
   if as_SFLSCF='Y' then  --是否零散处方,零散处方传入的BRID00为空
       Select  SQ_BM_BRXXB0_BRID00.nextval  into ls_BRID00  from  dual;  --病人ID序列号
       as_BRID00:=ls_BRID00;
       Insert into  BM_BRXXB0(BRID00,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRCSRQ,FBBH00,BRGFZH,CZY000,CZRQ00,CZSJ00,RYCS00,ZYZT00,SFYX00,BRZTQK)
                       values(ls_BRID00,'0',NVL(as_YBKH00,0),NVL(as_YBID00,0),ls_YBLBBH,as_XM0000,'男',to_char(sysdate,'yyyymmdd'),lv_FBBH00,'0',ad_GHY000,ls_CZRQ00,ls_CZSJ00,0,'0','Y',as_BRZTQK);

   end if;        
   --ls_BRID00:=ad_BRID00;   
   begin
   select Trim(BRXB00),Trim(BRBLH0),BRCSRQ into ls_BRXB00,ls_BRBLH0,ls_CSRQ00 from bm_brxxb0 where brid00=ls_BRID00;       
       EXCEPTION 
          when others then
            ls_BRXB00 :='男';
            ls_BRBLH0 :='0';
            ls_CSRQ00 :=' ';
   end;
    insert  into   SF_BRXXB0(GHID00,BRBLH0,GHH000,YBGHH0,GHLB00,BRID00,SFCZ00,XM0000,XB0000,CSRQ00,
                           FBBH00,LBBH00,GFZH00,GHRQ00,GHSJ00,GHY000,GHYXM0,GHKS00,
                           JZRQ00,JZSJ00,JZKS00,JZYS00,JZYSXM,SFYY00,THBZ00,PJH000,GHYXTS,SFNLYD,HZLB00,HZSJD0,GHJE00,CZRQ00,CZSJ00,GHZKBH)
                    Values(ls_MZID00,ls_BRBLH0,as_GHH000,'0',ls_GHLB00,ls_BRID00,'0',as_XM0000,ls_BRXB00,ls_CSRQ00,
                           lv_FBBH00,decode(as_SFLSCF,'Y',0,1),'0',ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_GHKS00,
                           ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,as_JZYSXM,'N','1',ls_PJH000,ls_GHYXTS,'N',1,'',0,ls_CZRQ00,ls_CZSJ00,'');
    begin
        Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,JZJE00)
                    select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,0,0,ad_GHY000,as_GHYXM0,ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,
                          ' ',0,0   from  bm_pjlxb0 where  PLBH00 = 0;--PLBH00=0是挂号单
        if  SQL%NOTFOUND  then
                   as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!';
                   as_SYSMSG:=SQLERRM||'票据类型(挂号单)不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 0)';
                   ROLLBACK;
                   RETURN;
        end if;
    end; 
    commit;
    
    EXCEPTION
        WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||',执行存储过程错误:SP_SF_MZCFHJGMFH('||to_char(ad_GHY000)||','||as_GHYXM0||','||to_char(ad_CZYKS0)||','||to_char(ad_JZYS00)||','||
                 as_JZYSXM||','||to_char(ad_GHHBID)||','||as_YJJXH0||','||as_FPXH00||','||as_CZBZ00||','||as_GHYXM0||','||
                 to_char(ad_CZYKS0)||','||to_char(ad_GHKS00)||','||to_char(ad_JZYS00)||','||as_JZYSXM||','||to_char(ad_GHHBID)||','||
                 'as_GHH000,as_YHMSG0,as_SYSMSG)',1,150);
                ROLLBACK;
end;