prompt No.001 SF_YK_GETLSH   获得一个前导'0'的指定长度字符流水号函数
create or replace function SF_YK_GETLSH(
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- lintj       2012.06.04  modify
-- lintj       2013.04.17  修改 ZYYF_ZYCFH 的序列号
-- gzj         2018.09.12  物资、药库部门支持设置各自的入出库开头字母和单号序列 KCWZ9-20180827-001
-- luowf       2022.07.19  更换ZYYF_ZYCFH序列SQ_BQ_ZBTQYZ_ID0000与病区同步
  P_lsh    in  varchar2,             --输入流水号类型
  P_BMBH   in  varchar2  default ''  --部门编号
)
return CHAR is           --返回前导'0'的指定长度字符流水号
  v_Return         varchar2(16);
  v_LSH            number;
  v_lenght         number:=0; 
  v_XTCS_VALUE     XT_XTCS00.VALUE0%TYPE;     --系统参数值
  v_XTCS_DYFHCD    number               ;     --系统参数定义返回长度
  v_dhsffhnf       varchar2(1)          ;     --单号是否返回年份
  v_count          number               ;     --计数器
  v_XTCS_KTZM      XT_XTCS00.VALUE0%TYPE;     --物资、药库系统入出库单号开头字母
  v_RCKDHKTZM      varchar2(100);             --部门对应的入出库单号开头字母
  v_RKD_KTZM       varchar2(10);              --入库单开头字母
  v_CKD_KTZM       varchar2(10);              --出库单开头字母
  v_sql            varchar2(300);
BEGIN
  begin
    select VALUE0 into v_XTCS_VALUE from XT_XTCS00 where name00='YK_RCKDHSFSYHNFGS';
  exception
    when others then
      v_XTCS_VALUE:='N';
  end;
  
  begin
    select VALUE0 into v_XTCS_KTZM from XT_XTCS00 where name00='WZ_YK_RCKDHKTZM';
  exception
    when others then
      v_XTCS_KTZM :='N';
  end;
  
  --物资、药库部门设置各自的入出库开头字母和单号序列
  if (nvl(instr(v_XTCS_KTZM,P_BMBH),0) > 0) and ((UPPER(p_LSH)='YKRKDH') or (UPPER(p_LSH)='YKYPQLDH')) then
  
    v_RCKDHKTZM := substr(v_XTCS_KTZM,instr(v_XTCS_KTZM,P_BMBH),instr(v_XTCS_KTZM,',',instr(v_XTCS_KTZM,P_BMBH))-instr(v_XTCS_KTZM,P_BMBH));
    v_RKD_KTZM := regexp_substr(v_RCKDHKTZM,'[^-]+',1,2);
    v_CKD_KTZM := regexp_substr(v_RCKDHKTZM,'[^-]+',1,3);
    
    select count(*) into v_LSH from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPRKD0_RKDH00_'||P_BMBH and OBJECT_TYPE='SEQUENCE';
      if v_LSH=0 then
        SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPRKD0_RKDH00_'||P_BMBH||' INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');
      end if;
      
    select count(*) into v_LSH from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPQLD0_YPQLDH_'||P_BMBH and OBJECT_TYPE='SEQUENCE';
      if v_LSH=0 then
        SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPQLD0_YPQLDH_'||P_BMBH||' INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');
      end if;
    
    if UPPER(p_LSH)='YKRKDH' then        --药库入库单号       
      v_Lenght:=5;
      v_sql:='select SQ_YK_YPRKD0_RKDH00_'||P_BMBH||'.nextval  value0 from dual where rownum=1';
      execute immediate v_sql  into v_LSH;
      v_Return:= v_RKD_KTZM||LPAD(v_LSH,v_Lenght,'0');    
    elsif UPPER(p_LSH)='YKYPQLDH' then   --药库药品请领单号
      v_Lenght:=5;
      v_sql:='select SQ_YK_YPQLD0_YPQLDH_'||P_BMBH||'.nextval  value0 from dual where rownum=1';
      execute immediate v_sql  into v_LSH;
      v_Return:= v_CKD_KTZM||LPAD(v_LSH,v_Lenght,'0');
    end if;      
      
  else 
    if instr(v_XTCS_VALUE,'Y')>0 then
      begin
            v_dhsffhnf:='Y';
            if instr(v_XTCS_VALUE,',')>0 then
              begin
                    v_XTCS_DYFHCD:=to_number(substr(v_XTCS_VALUE,3,10));
                    exception
            when others then
             v_XTCS_DYFHCD:=5;              
              end;
              else begin
                v_XTCS_DYFHCD:=5;
              end;  
            end if;
      end;
      else begin
            v_dhsffhnf:='N';
      end;
    end if;
    
    if v_dhsffhnf='Y' then   --重置序列号
      begin
          select count(*) into v_LSH from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPRKD0_RKDH00' and OBJECT_TYPE='SEQUENCE';
          if v_LSH=0 then
          begin
            SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPRKD0_RKDH00 INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');
          end;
          else begin
                  select count(*) into v_count from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPRKD0_RKDH00' and OBJECT_TYPE='SEQUENCE' and to_char(CREATED,'YYYY')=to_char(sysdate,'YYYY');
                  if v_count=0 then
                  begin
                          SP_EXECUTE_SQL('DROP SEQUENCE SQ_YK_YPRKD0_RKDH00');
                    SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPRKD0_RKDH00 INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');   
                  end;
            end if; 
          end;  
        end if;
        select count(*) into v_LSH from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPQLD0_YPQLDH' and OBJECT_TYPE='SEQUENCE';
          if v_LSH=0 then
          begin
            SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPQLD0_YPQLDH INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');
          end;
          else begin
                  select count(*) into v_count from ALL_OBJECTS where OBJECT_NAME='SQ_YK_YPQLD0_YPQLDH' and OBJECT_TYPE='SEQUENCE' and to_char(CREATED,'YYYY')=to_char(sysdate,'YYYY');
                  if v_count=0 then
                  begin
                          SP_EXECUTE_SQL('DROP SEQUENCE SQ_YK_YPQLD0_YPQLDH');
                    SP_EXECUTE_SQL('CREATE SEQUENCE SQ_YK_YPQLD0_YPQLDH INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999 CYCLE NOORDER NOCACHE');   
                  end;
            end if; 
          end;  
        end if;
      end;
    end if;
    
    IF UPPER(p_LSH)='YKRKDH' THEN                                 --药库入库单号
      v_Lenght:=5;
      SELECT SQ_YK_YPRKD0_RKDH00.NEXTVAL INTO v_LSH FROM DUAL;  
    ELSIF  UPPER(p_LSH)='YKYPQLDH' THEN                           --药库药品请领单号
      v_Lenght:=5;
      SELECT SQ_YK_YPQLD0_YPQLDH.NEXTVAL INTO v_LSH FROM DUAL;  
    ELSIF  UPPER(p_LSH)='YKYPCGJHDH' THEN                         --药库药品采购计划单号
      v_Lenght:=5;
      SELECT SQ_YK_YPCGJH_CGJHDH.NEXTVAL INTO v_LSH FROM DUAL;
    ELSIF  UPPER(p_LSH)='YKYPTJDH' THEN                           --药库药品调价批次
      v_Lenght:=5;
      SELECT SQ_YK_YPTJ00_YPTJPC.NEXTVAL INTO v_LSH FROM DUAL;
    ELSIF  UPPER(p_LSH)='YFYPRKDH' THEN                           --药房药品入库单号
      v_Lenght:=5;
      SELECT SQ_YF_YPRKD0_RKDH00.NEXTVAL INTO v_LSH FROM DUAL;
    ELSIF  UPPER(p_LSH)='YF_CKDH00' THEN                          --药房药品出库单号
      v_Lenght:=5;
      SELECT SQ_YF_YPCKD0_CKDH00.NEXTVAL INTO v_LSH FROM DUAL;
    ELSIF  UPPER(p_LSH)='MZYF_MZCFH' THEN                         --门诊药房处方流水号
      v_Lenght:=5;
      SELECT SQ_YS_CFXXB0_CFLSH0.NEXTVAL INTO v_LSH FROM DUAL;
    ELSIF  UPPER(p_LSH)='ZYYF_ZYCFH' THEN                         --住院药房处方流水号
      v_Lenght:=5;
      --SELECT SQ_BQ_YZMX00_YZMXID.NEXTVAL INTO v_LSH FROM DUAL; 
      select SQ_BQ_ZBTQYZ_ID0000.nextval into v_LSH from dual;  
    END IF;
        
    IF v_Lenght>0 THEN
      if UPPER(p_LSH)='YKRKDH' and v_dhsffhnf='Y' then
        v_Return:='RK'||to_char(sysdate,'YYYY')|| LPAD(v_LSH,v_XTCS_DYFHCD,'0');   
      elsif UPPER(p_LSH)='YKYPQLDH' and v_dhsffhnf='Y' then
        v_Return:='CK'||to_char(sysdate,'YYYY')|| LPAD(v_LSH,v_XTCS_DYFHCD,'0');   
      elsIF LENGTH(TO_CHAR(v_LSH))<v_Lenght THEN
        if UPPER(p_LSH)='YKRKDH' and v_XTCS_VALUE='M' then
          v_Return:='R'||LPAD(v_LSH,v_Lenght,'0');
        elsif UPPER(p_LSH)='YKYPQLDH' and v_XTCS_VALUE='M' then
          v_Return:='C'||LPAD(v_LSH,v_Lenght,'0');
        else
          v_Return:=LPAD(v_LSH,v_Lenght,'0');
        end if; 
      ELSE
        if UPPER(p_LSH)='YKRKDH' and v_XTCS_VALUE='M' then
          v_Return:='R'||TO_CHAR(v_LSH);
        elsif UPPER(p_LSH)='YKYPQLDH' and v_XTCS_VALUE='M' then
          v_Return:='C'||TO_CHAR(v_LSH);
        else
          v_Return:=TO_CHAR(v_LSH);
        end if;
      END IF;
    ELSE
      v_Return:='';
    END IF;
  END IF;
  
  RETURN v_Return;
  
END;
/ 
show error;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%