-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YJ_ZLXMADD -- Generated 3-六月-2021 16:58:55 from SD_HOSPITAL@zpxzyy CREATE OR REPLACE PROCEDURE sp_yj_zlxmadd --诊疗字典项目增加. ( SJXMBH in varchar2 , --父节点编号 LBBZ00 in char , --是否类别标志'1'子项目(子项目不可以再有子项目),'0'大类 ZLXMJC in char , --诊疗项目简称 GDXMBZ in char , --是否包含固定子项目'Y'是'N'否 JGDW00 in char , --结果单位 CKZ000 in char , --参考值 DW0000 in char , --单位 WBM000 in char , --五笔码 PYM000 in char , --拼音码 LBBH00 in number , --诊疗项目类别(全部取上级的类别编号)，无用参数 XXMS00 in char , --详细描述 FBZXBZ in char , --分步执行标志 SFYXSQ in char , --是否允许申请 BZ0000 in char , --备注 QYRQ00 in char , --启用日期(系统日期超过启用日期,不能删除,只能停用) PZLXMBH out varchar2 , PZXKS00 in number , --执行科室 PZYZBID in char default '', --专业组别 PBBID00 in char default '', --标本名称 PFYID00 in char default '0', --分院ID PJCMLID in number default '0', --检查目录，老年要求 PBWBH00 in char default '', --部位 PJCLBID in number default '', --检查类别 PJCZID0 in number default '', --预约检查组 PCYGLX0 in char default '', --采样管类型 PSQDID0 in number default '' , --门诊检查申请单 PZYSQID in number default '' , --住院检查申请单 PXBXZ00 in char default '0', --性别限制 PSFXZJZ in char default 'N', --是否限制急证 PYBXYFW in varchar2 default '',--第三方使用医保限用范围 PZFXYFW in varchar2 default '',--第三方使用自费限用范围 PXZFSLB in varchar2 default '0'--第三方弹窗是否选择自费医保发送标志 ) as zlxmbh BM_ZLZD00.ZLXMBH%TYPE; --诊疗项目编号 tempbh number; --子层编号; xmcount number; --子层项目记录数; i number; --计数器 mrwbm char(15); --默认五笔码 mrpym char(15); --默认拼音码 qyrq char(8); --默认启用日期 VZLXMJC BM_ZLZD00.ZLXMJC%TYPE; VSJLBBH BM_ZLZD00.LBBH00%TYPE; E_xmgd exception; E_XMMCCF exception; VZLXMID number; LS_ERR char(100); V_CS varchar2(188); Vlbbz00 char(1); --类别标志 Vzhxmbz char(1); --组合项目标志 -- MODIFICATION HISTORY -- Person Date Comments -- qks 2008.06.11 增加传入参数PZYZBID,PBBID00 YJ-20080606-001 -- chenyw 2013.10.18 诊疗项目简称截取改成到100字符 for YJ-20131016-001 -- linzy 2014.04.01 增加入参PFYID00 BY XMGL-20140319-001 -- dsm 2014.05.16 add PJCMLID for XMGL-20140506-001 -- dsm 2015.06.24 add PBWBH00 for XMGL-20150624-001 -- wusq 2016.02.17 add PJCLBID,PJCZID0,PCYGLX0,PSQDID0,PZYSQID for YJ-20151026-001 -- dsm 2016.03.24 add XBXZ00 for YJ-20160225-002 -- dsm 2016.05.04 增加SFXZJZ for YJ-20160421-001 -- hetf 2018.09.29 增加XEJSXM for XMGL-20180926-001 -- huangjy 2020.07.21 BM_ZLZD00.LBBH00取入参LBBH00,取消取VSJLBBH for YJ-20200525-001 -- huangjy 2020.09.08 修正LBBH00入参空的问题for YJ-20200908-001 -- huangjy 2021.04.28 BM_ZLZD00.YBXYFW,ZFXYFW,XZFSLB取入参PYBXYFW,PZFXYFW,PXZFSLB for YJ-20210413-001 -- --------- -------------- ----------------------------------------------------- begin --if Instrb(ZLXMJC,'[')>0 then -- VZLXMJC:=Substrb(ZLXMJC,1,Instrb(ZLXMJC,'[')-1); --else VZLXMJC:=substrb(ZLXMJC,1,100); --end if; select decode(LBBZ00,'0','0','1') into Vlbbz00 from dual where rownum=1; select decode(LBBZ00,'2','1','0') into Vzhxmbz from dual where rownum=1; V_CS:='SP_YJ_ZLXMADD('||SJXMBH||','||LBBZ00||','||ZLXMJC||','||GDXMBZ||','||JGDW00||','||CKZ000||','||DW0000||','|| WBM000||','||PYM000||','||LBBH00||','; /* XXMS00 in char , --详细描述 FBZXBZ in char , --分步执行标志 SFYXSQ in char , --是否允许申请 BZ0000 in char , --备注 QYRQ00 in char , --启用日期(系统日期超过启用日期,不能删除,只能停用) PZLXMBH out varchar2, PZXKS00 in number --执行科室*/ LS_ERR:='select LBBH00 into VSJLBBH from BM_ZLZD00 where ZLXMBH='||trim(SJXMBH); if (LBBH00 is null)or (trim(LBBH00)='') then select LBBH00 into VSJLBBH from BM_ZLZD00 where ZLXMBH=trim(SJXMBH); else VSJLBBH:=LBBH00; end if; select count(*) into XMcount from BM_ZLZD00 where ZLXMJC=VZLXMJC and ROWNUM=1; --if XMcount>0 then -- RAISE E_XMMCCF;--项目名称重复 -- return; --end if; select count(*) into xmcount from BM_ZLZD00 where ZLXMBH like SJXMBH||'___'; if xmcount=999 then--子项目已满 raise E_xmgd; --(-20000,'该项目含有的子项目已超过999个'); -- elsif xmcount=0 then zlxmbh:=sjxmbh||'000'; else--取最大编号值 LS_ERR:='select MAX(ZLXMBH)into zlxmbh from BM_ZLZD00 where ZLXMBH LIKE '||SJXMBH||'___'; select max(ZLXMBH)into zlxmbh from BM_ZLZD00 where ZLXMBH like SJXMBH||'___'; --Get Max Value end if; tempbh:=TO_NUMBER(SUBSTR(zlxmbh,LENGTH(sjxmbh)+1,3));--取末尾编码 if tempbh<999 then --编码小于999 zlxmbh:=sjxmbh||lpad(to_char(tempbh+1),3,'0'); --编码加1 else --编码=999，倒求值 while tempbh>0 loop tempbh:=tempbh-1; select count(*) into i from BM_ZLZD00 where ZLXMBH=SJXMBH||lpad(to_char(TEMPBH),3,'0'); exit when i=0; end loop; if tempbh=0 then--子项目已满 RAISE E_xmgd;--_APPLICATION_ERROR(-20000,'该项目含有的子项目已超过999个'); -- else--tempbh<>0 zlxmbh:=sjxmbh||lpad(to_char(tempbh),3,'0'); --编码加1 end if; end if; SP_YK_GETPYWB(VZLXMJC,mrpym,mrwbm); --qyrq:=NVL(QYRQ00,to_char(sysdate,'YYYYMMDD')); select SQ_BM_ZLZD00_ZLXMID.NEXTVAL into VZLXMID from dual; insert into BM_ZLZD00 (ZLXMID,ZLXMBH,LBBZ00,ZLXMJC,GDXMBZ,DW0000, JGDW00,CKZ000,WBSM00,PYSM00,LBBH00,XXMS00,FBZXBZ, QYRQ00,BZ0000,SFYXSQ,ZXKS00,ZHXMBZ,ZYZBID,BBID00, FYID00,JCMLID,BWBH00,JCLBID,JCZID0,CYGLX0,SQDID0,ZYSQID,XBXZ00,SFXZJZ,XEJSXM,YBXYFW,ZFXYFW,XZFSLB) values (VZLXMID,ZLXMBH,Vlbbz00,VZLXMJC,GDXMBZ,DW0000, JGDW00,CKZ000,NVL(WBM000,MRWBM),NVL(PYM000,MRPYM),VSJLBBH,XXMS00,FBZXBZ, NVL(QYRQ00,to_char(sysdate,'YYYYMMDD')),BZ0000,NVL(SFYXSQ,'N'),PZXKS00,Vzhxmbz,PZYZBID,PBBID00, PFYID00,PJCMLID,PBWBH00,PJCLBID,PJCZID0,PCYGLX0,PSQDID0,PZYSQID,PXBXZ00,PSFXZJZ,'0',PYBXYFW,PZFXYFW,PXZFSLB); PZLXMBH:=ZLXMBH; update BM_YBFYYZ set JCMLID=nvl(PJCMLID,0) where ZLXMID=VZLXMID; --if PZXKS00 IS NOT null then --INSERT into XT_KSZXXM (ZXKSBH,ZLXMID) VALUES (PZXKS00,VZLXMID); --end if; commit; exception when E_xmgd then raise_application_error(-20000,'该项目含有的子项目已超过999个！*'); when E_XMMCCF then raise_application_error(-20003,'诊疗项目名称不能重复！*'||vzlxmjc); when NO_DATA_FOUND then raise_application_error(-20001, '数据没有找到！*'||LS_ERR); when OTHERS then raise_application_error(-20002, nvl(sqlerrm, '原因不明出错')||'！*'); rollback; end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_YJ_ZLXMADD