create or replace function SF_XM_GETSFXMBH
(
  PBMGZID     number,            --编码规则ID
  PXMLBBH     varchar2,          --项目类别编号（前端当前类别树节点）
  PFLAG00     varchar2,          --枝叶标志；L为叶（项目），T为枝（类别)
  PSFXM_WHQX  varchar2           --PSFXM_WHQX=1为医院收费项目；PSFXM_WHQX=2为材料价表，BH0000=9打头
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- chenHeyi    2022.02.14  create     XMGL9-20220211-001  
-- chenHeyi    2022.02.25  在BMGZID=1时，使用36进制(0..9..A..Z)生成编号，材料价表要以9打头  XMGL9-20220224-001  
-- chenHeyi    2022.06.19  生成项目编号段，先TreeBH =PXMLBBH有无子项目，没有再查TreeBH like PXMLBBH||'%'  XMGL9-20220601-001  
-- chenHeyi    2023.06.25  返回<RETCODE>为-1时，前端给提示后继续; <RETCODE>为0,前端提示后中止;<RETCODE>为1时为正常有效    XMGL9-20230619-001   
-- chenrh      2025.06.20  增加编号第一位为0的处理   
return varchar2 is 
  LS_RETXML    varchar2(200);          --返回的编号:<TREEBH>#####</TREEBH><BH0000>#####</BH0000><RETCODE>1</RETCODE><RETMSG>XXX</RETMSG>
  LS_LNEWBH   BM_YYSFXM.BH0000%type;  --生成的项目编号 
  LS_TNEWBH   BM_YYSFXM.TREEBH%type;  --生成的类编号 
  
  LS_BMYIJI   BM_BMGZ00.BMYIJI%type; 
  LS_BMERJI   BM_BMGZ00.BMERJI%type;
  LS_BMANJI   BM_BMGZ00.BMANJI%type; 
  LS_BMSIJI   BM_BMGZ00.BMSIJI%type;
  LS_BMWUJI   BM_BMGZ00.BMWUJI%type;
  LS_MWGZ00   BM_BMGZ00.MWGZ00%type;
  LD_BMYIJI   number(5);
  LD_BMERJI   number(5);
  LD_BMANJI   number(5);
  LD_BMSIJI   number(5);
  LD_BMWUJI   number(5);
  LD_MWGZ00   number(5);
  LD_LEVEL0   number(5);     --入参：项目类别编号 的级数
  LD_WS0000   number(5);     --入参：项目类别编号 的位数
  LD_XJZWS0   number(5);     --下一级总位数
  LD_XJWS00   number(5);     --下一级码位位数
  LS_TMAXBH   BM_YYSFXM.TREEBH%type;  --最大的 类别编号
  LS_LMAXBH   BM_YYSFXM.BH0000%type;  --最大的 项目编号
  LS_SFIRST   varchar2(30);
  LS_XMGL_XMBHSCSYBH  XT_XTCS00.VALUE0%type;
  LD_TMAXBH   number(20); 
  LD_LMAXBH   number(30);
  LS_RETCODE  varchar(5);    --1正常，0异常
  LS_RETMSG   varchar(200);  --消息字串
  LD_Count    number(5);
begin
    LS_RETCODE:='1';
    LS_RETMSG:='';
  select nvl(max(VALUE0),'0') into LS_XMGL_XMBHSCSYBH from XT_XTCS00 where NAME00 ='XMGL_XMBHSCSYBH';
    
  select  nvl(BMYIJI,'0'),nvl(BMERJI,'0'),nvl(BMANJI,'0'),nvl(BMSIJI,'0'),nvl(BMWUJI,'0'),nvl(MWGZ00,'0') into LS_BMYIJI,LS_BMERJI,LS_BMANJI,LS_BMSIJI,LS_BMWUJI,LS_MWGZ00  from BM_BMGZ00 where BMGZID=PBMGZID;
  begin
        LD_BMYIJI:=to_number(LS_BMYIJI);
    exception
      when others then
        LD_BMYIJI:=0;
  end;
  begin
        LD_BMERJI:=to_number(LS_BMERJI);
    exception
      when others then
        LD_BMERJI:=0;
  end;
  begin
        LD_BMANJI:=to_number(LS_BMANJI);
    exception
      when others then
        LD_BMANJI:=0;
  end;
  begin
        LD_BMSIJI:=to_number(LS_BMSIJI);
    exception
      when others then
        LD_BMSIJI:=0;
  end;
  begin
        LD_BMWUJI:=to_number(LS_BMWUJI);
    exception
      when others then
        LD_BMWUJI:=0;
  end;
  begin
        LD_MWGZ00:=to_number(LS_MWGZ00);
    exception
      when others then
        LD_MWGZ00:=0;
  end;
  LD_LEVEL0:=0;
  LD_WS0000:=0;
  if PXMLBBH is null or PXMLBBH='0' then
    --根节点
    LD_LEVEL0:=0;
    LD_WS0000:=0;
  else
    if LD_WS0000+LD_BMYIJI<=length(PXMLBBH) then
      LD_WS0000:=LD_WS0000+LD_BMYIJI;
      LD_LEVEL0:=LD_LEVEL0+1;
    end if; 
    if LD_LEVEL0=1 and LD_WS0000+LD_BMERJI<=length(PXMLBBH) then 
      LD_WS0000:=LD_WS0000+LD_BMERJI;
      LD_LEVEL0:=LD_LEVEL0+1;
    end if; 
    if LD_LEVEL0=2 and LD_WS0000+LD_BMANJI<=length(PXMLBBH) then 
      LD_WS0000:=LD_WS0000+LD_BMANJI;
      LD_LEVEL0:=LD_LEVEL0+1;
    end if;  
    if LD_LEVEL0=3 and LD_WS0000+LD_BMSIJI<=length(PXMLBBH) then 
      LD_WS0000:=LD_WS0000+LD_BMSIJI;
      LD_LEVEL0:=LD_LEVEL0+1;
    end if; 
    if LD_LEVEL0=4 and LD_WS0000+LD_BMWUJI<=length(PXMLBBH) then 
      LD_WS0000:=LD_WS0000+LD_BMWUJI;
      LD_LEVEL0:=LD_LEVEL0+1;
    end if;     
  end if;
  if PFLAG00='T' then
    --取下一子级码总位数，下一子级码位数
    if LD_LEVEL0=0 then
      LD_XJZWS0:=LD_BMYIJI;
      LD_XJWS00:=LD_BMYIJI;
    elsif LD_LEVEL0=1 then 
      LD_XJZWS0:=LD_BMYIJI+LD_BMERJI;
      LD_XJWS00:=LD_BMERJI;
    elsif LD_LEVEL0=2 then
      LD_XJZWS0:=LD_BMYIJI+LD_BMERJI+LD_BMANJI;
      LD_XJWS00:=LD_BMANJI;
    elsif LD_LEVEL0=3 then
      LD_XJZWS0:=LD_BMYIJI+LD_BMERJI+LD_BMANJI+LD_BMSIJI;
      LD_XJWS00:=LD_BMSIJI;
    else
      LD_XJZWS0:=LD_BMYIJI+LD_BMERJI+LD_BMANJI+LD_BMSIJI+LD_BMWUJI;
      LD_XJWS00:=LD_BMWUJI;
    end if;
    --取子级最大码编号
    if LD_LEVEL0=0 then
      --根节点增加子节点
      select nvl(max(TreeBH),'') into LS_TMAXBH from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID and length(TreeBH)=LD_XJZWS0;      
      if PBMGZID='1' then --BMGZID=1的 使用带字母 
        if PSFXM_WHQX='2' then
          --材料价表 9打头，
          if LS_TMAXBH is null or LS_TMAXBH='' then
            LS_TNEWBH:=PXMLBBH||substrb('0000000000000',1,LD_XJWS00-1)||'9'; --无子级时拼9
          else 
            LD_TMAXBH:=SF_XM_36TO10(LS_TMAXBH);--当36进制，转10进制
            LS_TNEWBH:='9'||SF_XM_10TO36(LD_TMAXBH+1); --10进制+1，转36进制，带字母
            --强制拼9打头，可能与2级其它节点重码 
            select count(1) into LD_Count from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID and BH0000=LS_TNEWBH;
            if LD_Count<>0 then
              --重码用2级码算最大码，
              select nvl(max(TreeBH),'') into LS_TMAXBH from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID and length(TreeBH)=2; 
              LD_TMAXBH:=SF_XM_36TO10(LS_TMAXBH);--当36进制，转10进制
              LS_TNEWBH:=SF_XM_10TO36(LD_TMAXBH+1); --10进制+1，转36进制，带字母          
            end if;
            if length(LS_TNEWBH)>2 then
              LS_RETCODE:='0';
                LS_RETMSG:='生成编号'||LS_TNEWBH||'长度超出！(本级长度最大2位)' ;
            end if;
            if substrb(LS_TNEWBH,1,1)<>'9' then
              LS_RETCODE:='0';
                LS_RETMSG:='生成编号'||LS_TNEWBH||' 值超出！(本级最大9Z)' ;
            end if;
          end if; 
        else
          --收费项目 PSFXM_WHQX='1'
          if LS_TMAXBH is null or LS_TMAXBH='' then
            LS_TNEWBH:=PXMLBBH||substrb('0000000000000',1,LD_XJWS00-1)||'1'; --无子级时拼组1号
          else 
            LD_TMAXBH:=SF_XM_36TO10(LS_TMAXBH);--当36进制，转10进制
            LS_TNEWBH:=SF_XM_10TO36(LD_TMAXBH+1); --10进制+1，转36进制，带字母 
            if length(LS_TNEWBH)>LD_XJZWS0 then
              LS_RETCODE:='0';
                LS_RETMSG:='生成编号'||LS_TNEWBH||'长度超出！(本级长度最大'||to_char(LD_XJZWS0)||'位)' ;
            end if;
          end if;
        end if;
      else  --BMGZID=0的 不使用字母；此处不涉及材料价表，材料价表BMGZID=1
        if LS_TMAXBH is null or LS_TMAXBH='' then
          LS_TNEWBH:=PXMLBBH||substrb('0000000000000',1,LD_XJWS00-1)||'1'; --无子级时拼组1号
        else
          --有子级时最大码+1
          LS_SFIRST:=substrb(LS_TMAXBH,1,1);--省二药库二化中的药品编码以'E'打头,StrToInt64出错   yangh 20060308
          if LS_SFIRST>='0' and LS_SFIRST<='9' then  
            begin  
              LS_TNEWBH:= to_char(to_number(LS_TMAXBH)+1);
              LS_TNEWBH:= substrb('0000000000000',1,length(LS_TMAXBH)-length(LS_TNEWBH))||LS_TNEWBH;
            exception
              when others  then
                LS_RETCODE:='-1';
                LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
                LS_TNEWBH:=LS_TMAXBH;
            end; 
          else
            begin
              LS_TNEWBH:= substrb(LS_TMAXBH,2,length(LS_TMAXBH)); 
              LS_TNEWBH:= to_char(to_number(LS_TNEWBH)+1);
              LS_TNEWBH:= substrb('0000000000000',1,length(LS_TMAXBH)-length(LS_TNEWBH))||LS_TNEWBH;
            exception
              when others  then
                LS_RETCODE:='-1';
                LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
                LS_TNEWBH:=LS_TMAXBH;
            end; 
          end if;
        end if;
        if length(LS_TNEWBH)>LD_XJZWS0 then
          LS_RETCODE:='0';
            LS_RETMSG:='生成编号'||LS_TNEWBH||'长度超出！(本级长度最大'||to_char(LD_XJZWS0)||'位)' ;
        end if;
      end if;    
    else
      --非根节点下加子节点
      select nvl(max(TreeBH),'') into LS_TMAXBH from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID
       and TreeBH like PXMLBBH||'%' and TreeBH<>PXMLBBH and length(TreeBH)=LD_XJZWS0;
      if PBMGZID='1' then --BMGZID=1的 使用带字母 
        if LS_TMAXBH is null or LS_TMAXBH='' then
          LS_TNEWBH:=PXMLBBH||substrb('0000000000000',1,LD_XJWS00-1)||'1'; --无子级时拼组1号
        else
          LD_TMAXBH:=SF_XM_36TO10(LS_TMAXBH);--当36进制，转10进制
          LS_TNEWBH:=SF_XM_10TO36(LD_TMAXBH+1); --10进制+1，转36进制，带字母 
          --2025.06.20 chenrh 增加编号第一位为0的处理
          if substrb(PXMLBBH,1,1)='0'then
             LS_TNEWBH:= '0'||LS_TNEWBH;
          end if; 
          if length(LS_TNEWBH)>LD_XJZWS0 then
            LS_RETCODE:='0';
              LS_RETMSG:='生成编号'||LS_TNEWBH||'长度超出！(本级长度最大'||to_char(LD_XJZWS0)||'位)' ;
          end if; 
          if PSFXM_WHQX='2' then  --材料价表9打头，加1后可能非9打头
            if substrb(LS_TNEWBH,1,1)<>'9' then
              LS_RETCODE:='0';
                LS_RETMSG:='生成编号'||LS_TNEWBH||' 值超出，造成首字符非9！(材料价表编码以9打头)' ;
            end if;
          end if;
        end if;
      else  --BMGZID=0的 不使用带字母 
        if LS_TMAXBH is null or LS_TMAXBH='' then
          LS_TNEWBH:=PXMLBBH||substrb('0000000000000',1,LD_XJWS00-1)||'1'; --无子级时拼组1号
        else
          --有子级时最大码+1
          LS_SFIRST:=substrb(LS_TMAXBH,1,1);--省二药库二化中的药品编码以'E'打头,StrToInt64出错   yangh 20060308
          if LS_SFIRST>='0' and LS_SFIRST<='9' then 
            begin   
              LS_TNEWBH:= to_char(to_number(LS_TMAXBH)+1);
              LS_TNEWBH:= substrb('0000000000000',1,length(LS_TMAXBH)-length(LS_TNEWBH))||LS_TNEWBH;
            exception
              when others  then
                LS_RETCODE:='-1';
                LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
                LS_TNEWBH:=LS_TMAXBH;
            end;
          else
            begin
              LS_TNEWBH:= substrb(LS_TMAXBH,2,length(LS_TMAXBH)); 
              LS_TNEWBH:= to_char(to_number(LS_TNEWBH)+1);
              LS_TNEWBH:= substrb('0000000000000',1,length(LS_TMAXBH)-length(LS_TNEWBH))||LS_TNEWBH;
            exception
              when others  then
                LS_RETCODE:='-1';
                LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
                LS_TNEWBH:=LS_TMAXBH;
            end;
          end if;       
        end if;  
        if length(LS_TNEWBH)>LD_XJZWS0 then
          LS_RETCODE:='0';
            LS_RETMSG:='生成编号'||LS_TNEWBH||'长度超出！(本级长度最大'||to_char(LD_XJZWS0)||'位)' ;
        end if;      
      end if;
    end if;
    
    LS_RETXML:=SF_XT_GETELEMENTXMLSTR('TREEBH',LS_TNEWBH)||SF_XT_GETELEMENTXMLSTR('BH0000',LS_TNEWBH)||SF_XT_GETELEMENTXMLSTR('RETCODE',LS_RETCODE)||SF_XT_GETELEMENTXMLSTR('RETMSG',LS_RETMSG);
  end if;
  if PFLAG00='L' then
    LS_TMAXBH:=PXMLBBH; --生成项目编码，TreeBH引用前端当前类别树的编号
    --先查TreeBH=前端当前类别树编号的项目，没有再查包含  XMGL9-20220601-001 
    select nvl(max(BH0000),'')  into LS_LMAXBH from BM_YYSFXM where FLAG00='1' and BMGZID=PBMGZID and TreeBH = PXMLBBH;
    if LS_LMAXBH is null or LS_LMAXBH='' then  --没有再查包含
      select nvl(max(BH0000),'')  into LS_LMAXBH from BM_YYSFXM where FLAG00='1' and BMGZID=PBMGZID and TreeBH like PXMLBBH||'%';
    end if;
    --两次查询均没有，就拼组1号项目
    if LS_LMAXBH is null or LS_LMAXBH='' then    
      --子级无项目时拼组1号
      if LS_XMGL_XMBHSCSYBH='1' then  --子类别下首个收费项目的编号是否使用类别的BH0000来生成(原来是用TREEBH生成), 1是0否, 默认0
        select nvl(max(BH0000),'') into LS_TNEWBH from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID and TreeBH = PXMLBBH;
        if LS_TNEWBH is null or LS_TNEWBH='' then
          LS_TNEWBH:=LS_TMAXBH;
        end if; 
        LS_LNEWBH:=LS_TNEWBH||substrb('0000000000000',1,11-length(LS_TNEWBH))||'1';  
      else  
        LS_LNEWBH:=LS_TMAXBH||substrb('0000000000000',1,11-length(LS_TMAXBH))||'1'; 
      end if; 
    else
      --子级有项目最大码+1
      if PBMGZID='1' then 
        --去打头类别编码，后+1，再拼
        if LS_XMGL_XMBHSCSYBH='1' then 
          select nvl(max(BH0000),'') into LS_TNEWBH from BM_YYSFXM where FLAG00='0' and BMGZID=PBMGZID and TreeBH = PXMLBBH;
          if LS_TNEWBH is null or LS_TNEWBH='' then
            LS_TNEWBH:=LS_TMAXBH;
          end if; 
          begin  
            LS_LNEWBH:=to_char(to_number(substrb(LS_LMAXBH,length(LS_TNEWBH)+1,length(LS_LMAXBH)-length(LS_TNEWBH)))+1);
            LS_LNEWBH:=LS_TNEWBH||substrb('0000000000000',1,length(LS_LMAXBH)-length(LS_TNEWBH||LS_LNEWBH))||LS_LNEWBH;
          exception
            when others  then
              LS_RETCODE:='-1';
              LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
              LS_LNEWBH:=LS_LMAXBH;
          end;          
        else
          begin
            LS_LNEWBH:=to_char(to_number(substrb(LS_LMAXBH,length(LS_TMAXBH)+1,length(LS_LMAXBH)-length(LS_TMAXBH)))+1);
            LS_LNEWBH:=LS_TMAXBH||substrb('0000000000000',1,length(LS_LMAXBH)-length(LS_TMAXBH||LS_LNEWBH))||LS_LNEWBH;
          exception
            when others  then
              LS_RETCODE:='-1';
              LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
              LS_LNEWBH:=LS_LMAXBH;
          end;           
        end if;
      else  --BMGZID=0的 不使用带字母 
        LS_SFIRST:=substrb(LS_LMAXBH,1,1);--省二药库二化中的药品编码以'E'打头,StrToInt64出错   yangh 20060308
        if LS_SFIRST>='0' and LS_SFIRST<='9' then 
          begin
            LS_TNEWBH:=to_char(to_number(LS_LMAXBH)+1);    
            LS_LNEWBH:=substrb('0000000000000',1,length(LS_LMAXBH)-length(LS_TNEWBH))||LS_TNEWBH;
          exception
            when others  then
              LS_RETCODE:='-1';
              LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
              LS_LNEWBH:=LS_LMAXBH;
          end; 
        else 
          begin
            LS_TNEWBH:=substrb(LS_LMAXBH,2,length(LS_LMAXBH));
            LS_TNEWBH:=to_char(to_number(LS_TNEWBH)+1); 
            LS_LNEWBH:=LS_SFIRST||substrb('0000000000000',1,length(LS_LMAXBH)-length(LS_TNEWBH)-1)||LS_TNEWBH;
          exception
            when others  then
              LS_RETCODE:='-1';
              LS_RETMSG:='最大编号非数字，无法自动生成编号，请手动输入编号';
              LS_LNEWBH:=LS_LMAXBH;
          end;
        end if;
      end if ;
    end if;
    LS_RETXML:=SF_XT_GETELEMENTXMLSTR('TREEBH',LS_TMAXBH)||SF_XT_GETELEMENTXMLSTR('BH0000',LS_LNEWBH)||SF_XT_GETELEMENTXMLSTR('RETCODE',LS_RETCODE)||SF_XT_GETELEMENTXMLSTR('RETMSG',LS_RETMSG);
  end if;
  return LS_RETXML;
end;