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))