CREATE OR REPLACE PROCEDURE SP_TYJK_ADDSFXM_ONE(v_operflag in varchar ,--增加修改标志,A:增加,U:修改 v_HCBH00 in varchar, --耗材编码 v_XMMC00 in varchar, --项目名称 v_GG0000 in varchar, --规格 v_XMXH00 in varchar, --型号 v_SCCJ00 in varchar, --生产厂家 v_XMLB00 in varchar, --项目分类 耗材，药品，化验，检查 v_XMDW00 in varchar, --项目单位 v_XMDJ00 in number, --项目单价 v_GJDJ00 in number, --采购单价 v_PYSM00 in varchar, --拼音码 v_YBXMBH in varchar, --医保项目编号 v_SMLRBZ in varchar, --是否扫码计费 v_XMZT00 in varchar, --01启用，00停用 v_ZCZH00 in varchar, --注册证号 v_SFGZ00 in varchar, --是否高值：Y高值材料，N不是高值材料，默认为空，空值代表N，表示不是高值材料 v_CZY000 in number, --操作员 v_HISID0 in number, --HISID v_RCSTR0 in varchar, --入参合并串 v_BYRC00 in varchar, --备用入参 v_HISFH0 out varchar --HIS返回值 ) as in_operflag varchar2(10); in_sfxmid BM_YYSFXM.SFXMID%type; in_bmgzid BM_YYSFXM.BMGZID%type; in_bh0000 BM_YYSFXM.BH0000%type; in_xmmc00 BM_YYSFXM.XMMC00%type; in_pysm00 BM_YYSFXM.PYSM00%type; in_wbsm00 BM_YYSFXM.WBSM00%type; in_xmnh00 BM_YYSFXM.XMNH00%type; in_cwnr00 BM_YYSFXM.CWNR00%type; in_gg0000 BM_YYSFXM.GG0000%type; in_xmxh00 BM_YYSFXM.XMXH00%type; in_pzwh00 BM_YYSFXM.PZWH00%type; in_sccj00 BM_YYSFXM.SCCJ00%type; in_bz0000 BM_YYSFXM.BZ0000%type; in_dw0000 BM_YYSFXM.DW0000%type; in_bzje00 BM_YYSFXM.BZJE00%type; in_sfje00 BM_YYSFXM.SFJE00%type; in_hsxmid BM_YYSFXM.HSXMID%type; in_sfxx00 BM_YYSFXM.SFSX00%type; in_sfsx00 BM_YYSFXM.SFSX00%type; in_baxmid BM_YYSFXM.BAXMID%type; in_qyrq00 BM_YYSFXM.QYRQ00%type; ls_MAXBH0 BM_YYSFXM.BH0000%type; ls_MAXBH0_HZ BM_YYSFXM.BH0000%type; in_fdbl00 BM_YYSFXM.FDBL00%type; in_mzfpid BM_YYSFXM.MZFPID%type; in_zyfpid BM_YYSFXM.ZYFPID%type; in_KJXMID BM_YYSFXM.KJXMID%type; in_zjbz00 BM_YYSFXM.ZJBZ00%type; in_sfgz00 BM_YYSFXM.SFGZ00%type; v_yyxh BM_YYSFXM.XH0000%type; v_XH BM_YYSFXM.XH0000%type; v_flag BM_YYSFXM.FLAG00%type; v_TREE BM_YYSFXM.TREEBH%type; v_TREEBH BM_YYSFXM.TREEBH%type; Ecustom Exception; V_ERROR varchar2(1000); VZLXMBH BM_ZLZD00.ZLXMBH%TYPE; VSJZLBH BM_ZLZD00.ZLXMBH%TYPE; VYBSFXM BM_YBSFXM%rowtype; Vzlxmbh0 BM_ZLZD00.ZLXMBH%TYPE; V_YBZXLB IC_YBBRLB.YBZXLB%type; Vcount0 number; Vcount_1 number; tzqy_flag number; Vhsxmid bm_yysfxm.hsxmid%TYPE; Vbaxmid bm_yysfxm.baxmid%TYPE; begin in_bmgzid:='1'; begin select to_number(mc0000) into Vhsxmid from BM_TYZD00 where bh0000='GZHCJK_HSXMID' and zdmc00='高值耗材通用接口配置参数' ; exception when others then Vhsxmid:=null; end; begin select to_number(mc0000) into Vbaxmid from BM_TYZD00 where bh0000='GZHCJK_BAXMID' and zdmc00='高值耗材通用接口配置参数' ; exception when others then Vbaxmid:=null; end; --select nvl(max(BH0000),v_HCBH00) into ls_MAXBH0 from BM_YYSFXM where BH0000 like v_HCBH00||'%' and BMGZID='1'; --ls_MAXBH0_HZ:=substr(ls_MAXBH0,7,10); --if ls_MAXBH0_HZ='' then -- in_bh0000:=substr(LS_MAXBH0,1,6)||'000001' ; --else -- select substr(LS_MAXBH0,1,6)||lpad(to_number(substr(LS_MAXBH0,7,7) )+1,6,0) into in_bh0000 from dual; --end if; --select max(BH0000) into ls_MAXBH0 from BM_YYSFXM where BH0000 like v_HCBH00||'%' and BMGZID='1'; --if ls_MAXBH0 is null then --in_bh0000:=substr(v_HCBH00,1,9)||'001' ; -- else -- in_bh0000:=LS_MAXBH0+1; --end if; --select nvl(to_number(max(BH0000)),v_HCBH00) into ls_MAXBH0 from BM_YYSFXM where BH0000 like substr(v_HCBH00,1,9)||'%' and BMGZID='1'; select nvl(max(to_number(BH0000)),v_HCBH00) into ls_MAXBH0 from BM_YYSFXM where BH0000 like substr(v_HCBH00,1,9)||'%' and BMGZID='1'; --20220404WHY ls_MAXBH0_HZ:=substr(ls_MAXBH0,10,10); --if ls_MAXBH0_HZ='' then if NVL(ls_MAXBH0_HZ,0)=0 then in_bh0000:=substr(LS_MAXBH0,1,9)||'001' ; else --select substr(LS_MAXBH0,1,9)||lpad(to_number(substr(LS_MAXBH0,10,3) )+1,3,0) into in_bh0000 from dual; select substr(LS_MAXBH0,1,9)||lpad(to_number(substr(LS_MAXBH0,10,4) )+1,4,0) into in_bh0000 from dual;--20220404WHY end if; select HSXMID into in_hsxmid from BM_YYHSXM where HSXMMC like '%材料%' and rownum=1; in_xmmc00:=substrb(v_XMMC00,1,150); in_pysm00:=SUBSTRB(SF_XT_GETPYSM(v_XMMC00),0,10); in_wbsm00:=SUBSTRB(SF_XT_GETWBSM(v_XMMC00),0,10); in_xmnh00:=''; in_cwnr00:=''; in_gg0000:=substrb(v_GG0000,1,50); in_xmxh00:=substrb(v_XMXH00,1,150); in_pzwh00:=''; in_sccj00:=substrb(v_SCCJ00,1,100); in_bz0000:=''; in_dw0000:=substrb(v_XMDW00,1,20); in_bzje00:=v_XMDJ00; in_sfje00:=v_XMDJ00; in_fdbl00:=0; in_sfxx00:=v_XMDJ00; in_sfsx00:=v_XMDJ00; in_mzfpid:=22; --其他费 in_zyfpid:=22; --其他费 in_KJXMID:=138; --材料收入(会计核算) in_BAXMID:=305; --材料费 in_qyrq00:=to_char(sysdate,'YYYYMMDD'); in_zjbz00:='3'; in_sfgz00:=v_SFGZ00; if v_operflag not in ('A','U') then V_ERROR:='操作标准错误'; end if; if v_operflag='A' then select SQ_BM_YYSFXM_SFXMID.nextval+8000000000 into in_sfxmid from dual; else in_sfxmid:= v_HISID0; end if; in_operflag:=v_operflag; /* SP_XM_YYWHADD( v_operflag =>in_operflag, --in varchar, v_item =>'L', --in varchar, v_sfxmid =>in_sfxmid , --in number, v_bmgzid =>in_bmgzid, --in number default 0, v_bh0000 =>in_bh0000, --in varchar2, v_xmmc00 =>in_xmmc00, --in varchar2, v_pysm00 =>in_pysm00, --in varchar2, v_wbsm00 =>in_wbsm00, --in varchar2, v_xmnh00 =>in_xmnh00, --in varchar2, v_cwnr00 =>in_cwnr00, --in varchar2, v_gg0000 =>in_gg0000, --in varchar2, v_pzwh00 =>in_pzwh00, --in varchar2, v_sccj00 =>in_sccj00, --in varchar2, v_bz0000 =>in_bz0000, --in varchar2, v_dw0000 =>in_dw0000, --in char, v_bzje00 =>in_bzje00, --in number, v_sfje00 =>in_sfje00, --in number DEFAULT NULL, v_fdbl00 =>0, --in number, v_sfxx00 =>in_sfxx00, --in number, v_sfsx00 =>in_sfsx00, --in number, v_mzfpid =>in_mzfpid, --in number DEFAULT NULL, v_zyfpid =>in_zyfpid, --in number DEFAULT NULL, v_qyrq00 =>in_qyrq00, --in char, v_qysj00 =>in_qyrq00, --in char, v_zjbz00 =>in_zjbz00, --in char default '3', v_hsxmid =>in_hsxmid, --in number, v_baxmid =>in_baxmid, --in number default null, v_parent =>null, --in varchar2 default null, --该编码的父编码 v_refbro =>'NB', --in char default 'NB', --修改时是否更新兄弟项目 'YB' 更新,'NB' 不更新 v_gjbm00 =>V_HCBH00, --in varchar2, v_jsxm00 =>'2', --in char default '0', v_lscl00 =>'0', --in varchar2, v_rsfsx0 =>0, --in number, v_jmje00 =>null, --in number default null, v_FYID00 =>'0', --in char default '0', v_SFGZ00 =>'N', --in varchar2 default 'N', --是否高值 v_XYFW00 =>'', --in varchar2 default '', --限用范围 v_XEJSXM =>'0', --in varchar2 default '0', --项目类型 0是大人小孩可以用的项目,1大人用项目,2儿童项目 v_XZFSLB =>'0', --in varchar2 default '0', --1是 可以让医生弹框选择，0不行 v_BKEH83 =>v_YBXMBH, --in varchar2 default '', --医保国家标准编码 v_GJBZMC =>v_XMMC00, --in varchar2 default '' --国家医保名称 v_ZCZH00 =>v_ZCZH00, v_SMLRBZ =>v_SMLRBZ );*/ select max(XH0000) into v_yyxh from BM_YYSFXM Where BMGZID=in_bmgzid and XH0000<9000000000; select max(TREEBH) into v_TREE from BM_YYSFXM Where BMGZID=in_bmgzid and TREEBH LIKE '98903%'; v_xh:=v_yyxh+1; v_TREEBH:=v_TREE+1; v_flag:='1'; if v_operflag='A' then select to_char(nvl(max(SFXMID),0)) into v_HISFH0 from bm_yysfxm where WDWBH0=v_BYRC00; if v_HISFH0<>'0' then return; end if; insert into BM_YYSFXM (SFXMID,BMGZID,XH0000,BH0000,FLAG00,PYSM00,WBSM00,XMMC00,XMNH00,CWNR00, DW0000,BZJE00,SFJE00,FDBL00,SFXX00,SFSX00,MZFPID,ZYFPID,QYRQ00,QYSJ00, TZRQ00,TZSJ00,ZJBZ00,HSXMID,BAXMID,GG0000,SCCJ00,PZWH00,BZ0000,YYDJ00, DJXS00,ZHXMBZ,GJBM00,JSXM00,LSCL00,RSFSX0,JMJE00,FYID00,SFGZ00,XEJSXM, XGRQSJ,BKEH83,GJBZMC,ZCZH00,TREEBH,KJXMID,WDWBH0,XMXH00) values(in_sfxmid,in_bmgzid,v_xh,in_bh0000,v_flag,in_pysm00,in_wbsm00,substrb(in_xmmc00||'('||in_gg0000||')',1,150),in_cwnr00,in_cwnr00, in_dw0000,in_bzje00,in_sfje00,0,in_sfxx00,in_sfsx00,in_mzfpid,in_zyfpid,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), null,null,in_zjbz00,Vhsxmid,Vbaxmid,in_gg0000,in_sccj00,in_pzwh00,in_bz0000,'A', 1,'0',v_HCBH00,'2','0',0,0,'0',in_sfgz00,'0', to_char(sysdate,'YYYYMMDDHH24:MI:SS'),v_YBXMBH,v_XMMC00,v_ZCZH00,in_bh0000,in_KJXMID, v_BYRC00,in_xmxh00); --医保对照 begin select count(*) into Vcount0 from BM_YBSFXM where XMBH00=v_YBXMBH and FBBH00=3; if (Vcount0 = 0) and (v_YBXMBH<>'NULL') and (v_YBXMBH<>'null') and (v_YBXMBH is not null) then insert into BM_YBSFXM(FBBH00,YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00, SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,SFSX00,YBBRLB,ZYZFBL,cjr000) values ( 3, --V_FBBH00, '1', --V_YBZXLB, v_YBXMBH, --V_XMBH00, '1', --v_FLAG00, '22', --V_ZYFPXM, '22', --V_MZFPXM, in_xmmc00, --V_XMMC00, '', --V_YPJX00, in_dw0000, --V_XMDW00, 0.1, --V_ZFBL00, 'Y', --V_SFYX00, 'Y', --V_SFYBXM, '1', --V_YBLB00, in_pysm00, --RV_PYXMMC, in_wbsm00, --RV_WBXMMC, 0, --V_GXXLH0 , null, --V_SFSX00, 'Z', --V_YBBRLB, 0 --V_ZYZFBL ,0 ); insert into BM_YBSFXM(FBBH00,YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00, SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,SFSX00,YBBRLB,ZYZFBL,cjr000) values ( 3, --V_FBBH00, '2', --V_YBZXLB, v_YBXMBH, --V_XMBH00, '1', --v_FLAG00, '22', --V_ZYFPXM, '22', --V_MZFPXM, in_xmmc00, --V_XMMC00, '', --V_YPJX00, in_dw0000, --V_XMDW00, 0.1, --V_ZFBL00, 'Y', --V_SFYX00, 'Y', --V_SFYBXM, '1', --V_YBLB00, in_pysm00, --RV_PYXMMC, in_wbsm00, --RV_WBXMMC, 0, --V_GXXLH0 , null, --V_SFSX00, 'Z', --V_YBBRLB, 0 --V_ZYZFBL ,0 ); end if; select * into VYBSFXM from BM_YBSFXM where XMBH00=v_YBXMBH and FBBH00=3 and rownum=1; V_YBZXLB:='1'; SP_XM_YBDYADDEDT( V_EIADID=>'A', --增加数据为‘A’，修改数据为‘E’ V_SFXMID=>in_sfxmid, --医院流水号 V_YBZXLB=>V_YBZXLB, --医保中心类别 V_SFYP00=>'N', --是否药品 V_YBXMBH=>v_YBXMBH, --医保项目编号 V_YBZYFP=>VYBSFXM.ZYFPXM, --住院发票项目编号 V_YBMZFP=>VYBSFXM.MZFPXM, --门诊发票项目编号 V_SFYBXM=>'Y', --是否医保项目 V_YBLB00=>VYBSFXM.YBLB00, --医保类别 V_ZFBL00=>VYBSFXM.ZFBL00, --自费比率 V_YBBRLB=>'0', --医保病人类别 V_FBBH00=>3, --费别编号 V_ZFJE00=>0, --自费金额 V_JMBL00=>0, --减免比率 V_JMJE00=>0, --减免金额 V_SFSX00=>null, --V_CJR000=>v_CZY000, --创建人 V_CJR000=>'8176', --创建人 V_ZYZFBL=> 0, --住院自付比率 V_ZYZFJE=>0 --住院自付金额 ); V_YBZXLB:='2'; SP_XM_YBDYADDEDT( V_EIADID=>'A', --增加数据为‘A’，修改数据为‘E’ V_SFXMID=>in_sfxmid, --医院流水号 V_YBZXLB=>V_YBZXLB, --医保中心类别 V_SFYP00=>'N', --是否药品 V_YBXMBH=>v_YBXMBH, --医保项目编号 V_YBZYFP=>VYBSFXM.ZYFPXM, --住院发票项目编号 V_YBMZFP=>VYBSFXM.MZFPXM, --门诊发票项目编号 V_SFYBXM=>'Y', --是否医保项目 V_YBLB00=>VYBSFXM.YBLB00, --医保类别 V_ZFBL00=>VYBSFXM.ZFBL00, --自费比率 V_YBBRLB=>'0', --医保病人类别 V_FBBH00=>3, --费别编号 V_ZFJE00=>0, --自费金额 V_JMBL00=>0, --减免比率 V_JMJE00=>0, --减免金额 V_SFSX00=>null, --V_CJR000=>v_CZY000, --创建人 V_CJR000=>'8176', --创建人 V_ZYZFBL=> 0, --住院自付比率 V_ZYZFJE=>0 --住院自付金额 ); V_YBZXLB:='1'; exception when others then V_ERROR:=substr(sqlerrm,1,300); Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'BM_YYSFXM',2,v_CZY000,substr('增加错误:'||'生成BM_YBSFDY及BM_ZLZD00失败',1,1000)||V_ERROR,in_sfxmid); end; --收费转诊疗 if nvl(in_sfgz00,'N')='N' then --VSJZLBH:='Z001001'; Vzlxmbh := 'Z013005'; --select SF_XT_GETZLXMBH(VSJZLBH) into Vzlxmbh0 from dual; --Z001001001 SP_YJ_ZLXMADD(Vzlxmbh,'1',substrb(in_xmmc00||in_gg0000,1,200),'N',null,null,in_dw0000,substrb(SF_XT_GETWBSM(in_xmmc00),1,15), substrb(SF_XT_GETPYSM(in_xmmc00),1,15),null,null,'N','Y',null,TO_CHAR(SYSDATE,'YYYYMMDD'),VZLXMBH,null); insert into xt_zlsfgx (gxid00,zlxmid,sfxmid) select SQ_XT_ZLSFGX_GXID00.Nextval,zlxmid,in_sfxmid from bm_zlzd00 a where a.zlxmbh=VZLXMBH and lbbz00='1'; end if; --VSJZLBH:=PSJZLBH;--选中的收费项目不能作为诊疗类别 else --修改医院收费项目 update BM_YYSFXM set XMMC00=v_XMMC00, gg0000=v_GG0000, xmxh00=v_XMXH00, BKEH83=v_YBXMBH, GJBZMC=v_XMMC00, BZJE00=in_bzje00, SFJE00=in_sfje00, sfsx00=in_sfje00, --lzg sfxx00=in_sfje00, --lzg 240607 添加上下限修改 SCCJ00=v_SCCJ00, PZWH00=v_ZCZH00 where SFXMID=in_sfxmid; select count(*) into tzqy_flag from BM_YYSFXM where SFXMID=in_sfxmid and tzrq00 is null; if v_xmzt00 ='00' and tzqy_flag =1 then update BM_YYSFXM set tzrq00 = to_char(sysdate,'yyyymmdd') , tzsj00 = to_char(sysdate,'hh24:mm:ss') where SFXMID=in_sfxmid; end if ; if v_xmzt00 ='01' and tzqy_flag =0 then update BM_YYSFXM set tzrq00 = null , tzsj00 = null where SFXMID=in_sfxmid; end if ; --修改收费对应的医保项目编号 update BM_Ybsfdy set YBXMBH=v_YBXMBH, bkeh83=v_YBXMBH, XGRQSJ=to_char(sysdate,'yyyymmddhh24:mi:ss'), XGR000='8176' where SFXMID=in_sfxmid ; ----判断是否有这个医保项目 select count(1) into Vcount_1 from bm_ybsfxm where xmbh00 = v_YBXMBH; if Vcount_1=0 then --没有这个医保项目 --增加医保项目 insert into BM_YBSFXM(FBBH00,YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00, SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,SFSX00,YBBRLB,ZYZFBL,cjr000) values ( 3, --V_FBBH00, '1', --V_YBZXLB, v_YBXMBH, --V_XMBH00, '1', --v_FLAG00, '22', --V_ZYFPXM, '22', --V_MZFPXM, in_xmmc00, --V_XMMC00, '', --V_YPJX00, in_dw0000, --V_XMDW00, 0.1, --V_ZFBL00, 'Y', --V_SFYX00, 'Y', --V_SFYBXM, '1', --V_YBLB00, in_pysm00, --RV_PYXMMC, in_wbsm00, --RV_WBXMMC, 0, --V_GXXLH0 , null, --V_SFSX00, 'Z', --V_YBBRLB, 0 --V_ZYZFBL ,0 ); insert into BM_YBSFXM(FBBH00,YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00, SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,SFSX00,YBBRLB,ZYZFBL,cjr000) values ( 3, --V_FBBH00, '2', --V_YBZXLB, v_YBXMBH, --V_XMBH00, '1', --v_FLAG00, '22', --V_ZYFPXM, '22', --V_MZFPXM, in_xmmc00, --V_XMMC00, '', --V_YPJX00, in_dw0000, --V_XMDW00, 0.1, --V_ZFBL00, 'Y', --V_SFYX00, 'Y', --V_SFYBXM, '1', --V_YBLB00, in_pysm00, --RV_PYXMMC, in_wbsm00, --RV_WBXMMC, 0, --V_GXXLH0 , null, --V_SFSX00, 'Z', --V_YBBRLB, 0 --V_ZYZFBL ,0 ); end if; end if; v_HISFH0:=in_sfxmid; if v_operflag='A' then Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'BM_YYSFXM',2,v_CZY000,'增加:'||v_RCSTR0,in_sfxmid); elsif v_operflag='U' then Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'BM_YYSFXM',2,v_CZY000,'修改:'||v_RCSTR0,in_sfxmid); end if; commit; exception when Ecustom then raise_application_error(-20010,dbms_utility.format_error_backtrace||substrb(V_error||'！*'||'@'||dbms_utility.format_error_backtrace,1,240)); RollBack; when others then raise_application_error(-20002,dbms_utility.format_error_backtrace||substrb(V_error||'！*'||nvl(sqlerrm, '原因不明出错')||'@'||dbms_utility.format_error_backtrace,1,240)); RollBack; end;