CREATE OR REPLACE PROCEDURE SP_HIS_TTTJSF( PCZY000 in number , --输入：操作员编码 PTJJZDH in varchar2 , --输入：体检结账单号 PFPTTMC in varchar2 , --输入：发票抬头名称 PPJXH00 in varchar2 default '0', --输入：发票序号 PCOMMIT in varchar2 default 'N', --输入：是否提交 Y:是 N:否 PJZDH00 out number , --输出：HIS内部结账单号 PPJH000 out number , --输出：HIS内部票据号 PZXCGBZ out number , --输出：执行成功标志 1成功 0失败 PTSXX00 out varchar2 , --输出：执行结果提示信息，成功的情况下也可能有警告信息 PXXXX00 out varchar2 --输出：执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2017.01.02 create -- zhangyc 2017.05.22 修正团体结算错误 -- --------- ---------- ------- VROW BM_BRXXB0%rowtype; VSYSDATE date; ECUSTOM exception; VCOUNTER number(14,4); LS_CZRQ00 char(8); LS_CZSJ00 char(8); SF_TJGHLB varchar2(100); SF_TTTJBRFBBH varchar2(100); SF_TJKDKSBH varchar2(100); LS_FPJMJE number(12,2); LS_JZHJM0 number(12,2); LS_GHHQZ0 varchar2(10); --挂号前缀 LS_TJH000 varchar2(20); LS_GHH001 varchar2(20); LS_SFSCDJ varchar2(1);--(标记)是否需要生成单据号的序列号 LS_TTID00 varchar2(10); LS_TTQC00 varchar2(50); LS_JMDJH0 number(12); LS_ZXCGBZ number(12); LS_ID0000 SF_TJSF00.ID0000%type; LS_GHID00 SF_BRXXB0.GHID00%type; LS_GHH000 SF_BRXXB0.GHH000%type; LS_TJGHH0 SF_BRXXB0.GHH000%type; LS_LBBH00 BM_GHLBB0.LBBH00%type; LS_LBMC00 BM_GHLBB0.LBMC00%type; LS_YJDJH0 YJ_YW0000.YJDJH0%type; LS_XKH000 BM_YGBM00.XKH000%type; LS_ZLXMID BM_ZLZD00.ZLXMID%type; LS_FBMC00 BM_BRFBB0.FBMC00%type; LS_YBLBBH IC_YBBRLB.YBLB00%type; LS_YBZXLB IC_YBBRLB.YBZXLB%type; LS_JFLBID BM_BRJFLB.JFLBID%type; LS_JFLBMC BM_BRJFLB.JFLBMC%type; LS_CZYXM0 BM_YGBM00.ZWXM00%type; LS_CZYKS0 BM_YGBM00.BMBH00%type; LS_BRID00 BM_BRXXB0.BRID00%type; LS_MRN000 BM_BRXXB0.MRN000%type; LS_BRBLH0 BM_BRXXB0.BRBLH0%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LS_YBID00 BM_BRXXB0.YBID00%type; LS_YBLB00 BM_BRXXB0.YBLB00%type; LS_BRXM00 BM_BRXXB0.BRXM00%type; LS_BRXB00 BM_BRXXB0.BRXB00%type; LS_BRXX00 BM_BRXXB0.BRXX00%type; LS_BRCSRQ BM_BRXXB0.BRCSRQ%type; LS_BRHYZK BM_BRXXB0.BRHYZK%type; LS_BRZY00 BM_BRXXB0.BRZY00%type; LS_BRMZ00 BM_BRXXB0.BRMZ00%type; LS_BRGJ00 BM_BRXXB0.BRGJ00%type; LS_BRZJLX BM_BRXXB0.BRZJLX%type; LS_BRZJBH BM_BRXXB0.BRZJBH%type; LS_FBBH00 BM_BRXXB0.FBBH00%type; LS_BRGFZH BM_BRXXB0.BRGFZH%type; LS_BRDYFW BM_BRXXB0.BRDYFW%type; LS_BRDH00 BM_BRXXB0.BRDH00%type; LS_BRSZSF BM_BRXXB0.BRSZSF%type; LS_BRSZS0 BM_BRXXB0.BRSZS0%type; LS_BRSZZ0 BM_BRXXB0.BRSZZ0%type; LS_BRSZC0 BM_BRXXB0.BRSZC0%type; LS_BRJTDZ BM_BRXXB0.BRJTDZ%type; LS_BRYB00 BM_BRXXB0.BRYB00%type; LS_TYDWID BM_BRXXB0.TYDWID%type; LS_TYDWMC BM_BRXXB0.TYDWMC%type; LS_BRLXGX BM_BRXXB0.BRLXGX%type; LS_BRLXXM BM_BRXXB0.BRLXXM%type; LS_BRLXDZ BM_BRXXB0.BRLXDZ%type; LS_BRLXDH BM_BRXXB0.BRLXDH%type; LS_BRZTQK BM_BRXXB0.BRZTQK%type; LS_BRJKQK BM_BRXXB0.BRJKQK%type; LS_BRLX00 BM_BRXXB0.BRLX00%type; LS_CZY000 BM_BRXXB0.CZY000%type; LS_RYCS00 BM_BRXXB0.RYCS00%type; LS_SFYX00 BM_BRXXB0.SFYX00%type; LS_BZ0000 BM_BRXXB0.BZ0000%type; LS_YBBRLB BM_BRXXB0.YBBRLB%type; LS_SFYD00 BM_BRXXB0.SFYD00%type; LS_SFYXJB BM_BRXXB0.SFYXJB%type; LS_BJJBIE BM_BRXXB0.BJJBIE%type; LS_TSSXMC BM_BRXXB0.TSSXMC%type; LS_FWBJ00 BM_BRXXB0.FWBJ00%type; LS_XZQH00 BM_BRXXB0.XZQH00%type; LS_GRSFEN BM_BRXXB0.GRSFEN%type; LS_GRSFMC BM_BRXXB0.GRSFMC%type; LS_SFBLWT BM_BRXXB0.SFBLWT%type; LS_CARD16 BM_BRXXB0.CARD16%type; LS_MZZHID BM_BRXXB0.MZZHID%type; LS_FBBH01 BM_BRXXB0.FBBH01%type; LS_YBBRL1 BM_BRXXB0.YBBRL1%type; LS_YBLB01 BM_BRXXB0.YBLB01%type; LS_DAID00 BM_BRXXB0.DAID00%type; LS_BRCSSJ BM_BRXXB0.BRCSSJ%type; LS_SFTJBR BM_BRXXB0.SFTJBR%type; LS_BRSFHY BM_BRXXB0.BRSFHY%type; LS_BRSBLB BM_BRXXB0.BRSBLB%type; LS_BRYGCP BM_BRXXB0.BRYGCP%type; LS_XJYE00 SF_BRZHXX.XJYE00%type; LS_ZZYE00 SF_BRZHXX.ZZYE00%type; LS_ZYE000 SF_BRZHXX.ZYE000%type; LS_YKZYE0 SF_BRZHXX.YKZYE0%type; LS_BJJE00 SF_BRZHXX.BJJE00%type; LS_TYJE00 SF_BRZHXX.TYJE00%type; LS_YXQFJE SF_BRZHXX.YXQFJE%type; LS_YBZHYE SF_BRZHXX.YBZHYE%type; LS_KL0000 SF_BRZHXX.KL0000%type; LS_JSJE00 SF_TJSF00.JSJE00%type; LS_JMJE00 SF_TJSF00.JMJE00%type; LS_SJJE00 SF_TJSF00.SJJE00%type; LS_JZYID0 SF_TJSF00.JZYID0%type; LS_JZRXM0 SF_TJSF00.JZRXM0%type; LS_JZRQ00 SF_TJSF00.JZRQ00%type; LS_JZSJ00 SF_TJSF00.JZSJ00%type; LS_JZZT00 SF_TJSF00.JZZT00%type; LS_SFCL00 SF_TJSF00.SFCL00%type; LS_FPJE00 SF_TJSF00.FPJE00%type; LS_FPDWMC SF_TJSF00.FPDWMC%type; LS_DYRBH0 SF_TJSF00.DYRBH0%type; LS_DYRXM0 SF_TJSF00.DYRXM0%type; LS_DYRQ00 SF_TJSF00.DYRQ00%type; LS_DYSJ00 SF_TJSF00.DYSJ00%type; LS_HJJE00 SF_BRFY00.HJJE00%type; LS_ZFJE00 SF_BRFY00.ZFJE00%type; LS_GFJE00 SF_BRFY00.GFJE00%type; LS_JZJE00 SF_BRFY00.JZJE00%type; LS_JZDH00 SF_BRFY00.JZDH00%type; LS_DJH000 SF_BRFY00.DJH000%type; LS_PJH000 SF_PJSYQK.PJH000%type; --票据流水号 LS_BCKBCS SF_JZB000.BCKBCS%type; LS_TSBZBH SF_JZB000.TSBZBH%type; --LS_JMJE00 SF_JZB000.JMJE00%type; LS_GRZHZF SF_JZB000.GRZHZF%type; LS_TCJJZF SF_JZB000.TCJJZF%type; --LS_ZFJE00 SF_JZB000.ZFJE00%type; --LS_JZRQ00 SF_JZB000.JZRQ00%type; --LS_JZSJ00 SF_JZB000.JZSJ00%type; LS_JZY000 SF_JZB000.JZY000%type; LS_GWYBZ0 SF_JZB000.GWYBZ0%type; LS_FYBJE0 SF_JZB000.FYBJE0%type; LS_YLZFJE SF_JZB000.YLZFJE%type; LS_SBTCJJ SF_JZB000.SBTCJJ%type; LS_SBGRZH SF_JZB000.SBGRZH%type; LS_JJZFE0 SF_JZB000.JJZFE0%type; LS_SBZFE0 SF_JZB000.SBZFE0%type; LS_BJZFE0 SF_JZB000.BJZFE0%type; LS_SYBXZF SF_JZB000.SYBXZF%type; LS_XJFKJE SF_JZB000.XJFKJE%type; LS_ZZFKJE SF_JZB000.ZZFKJE%type; LS_YHJE00 SF_PJSYQK.YHJE00%type; LS_MXID00 SF_FYMX00.MXID00%type; LS_RWID00 number(12); LS_JZR000 number(5); LS_KDKSBH number(5); LS_MSGSTR varchar2(400); es varchar2(400); --体检收费项目 cursor CUR_TJXMSF is select JZDH00,SQXMID,SQXMMC,XMSL00,XMJE00,JZR000,JZRXM0, JZRQ00,TTID00,TTQC00,RWID00,trim(SFXMDM)SFXMDM,SFXMMC,RS0000,'' as KDYSBH,'' as ZXKSBH ,'' as ZXKSMC from VW_PE_XMJZMX@HISPE where 1=1 and JZDH00=PTJJZDH and JZDH00 in (select JZDH00 from VW_PE_TTJZHZ@HISPE where JZDH00=PTJJZDH and JZZT00 in ('0')--0:结账;1:反结 and SFCL00 in ('0')--1:处理;0:未处理 ) order by TTID00,JZDH00; cursor CUR_TJJMJE is select sum(a.HJJE00)HJJE00,a.MZFPID,D.XMMC00 as FPXMMC from SF_FYMX00 A,BM_FPXM00 D where 1=1 and A.MZFPID = D.FPXMID and A.BRID00>=LS_BRID00 and A.DJH000 in (select SFDJH0 from SF_TJSFMX where BRID00>=LS_BRID00 and ID0000=LS_ID0000) group by a.MZFPID,D.XMMC00; cursor CUR_YJDJGL is select distinct ZYJDJH from YJ_YW0000 a,YJ_YJDJGL b where a.YJDJH0=b.FYJDJH and a.BRID00=LS_BRID00 and a.YJDJH0=LS_YJDJH0; begin es :='1@'; PZXCGBZ:=0; --执行成功标志 1成功 0失败 VSYSDATE:=sysdate; LS_GHHQZ0:='TT'; LS_CZRQ00:=to_char(VSYSDATE,'yyyymmdd'); LS_CZSJ00:=to_char(VSYSDATE,'hh24:mi:ss'); LS_TJH000:=null; LS_CZY000:=PCZY000; LS_JFLBID:=1; LS_SFSCDJ:= '1'; LS_MSGSTR:=''; /* PXXXX00 :=nvl(to_char(PCZY000),'null')||','||nvl(to_char(PCZYKS0),'null')||','|| nvl(to_char(PBRID00),'null')||','||nvl(PTJH000,'null')||','||nvl(PCOMMIT,'null'); SP_TransLog(VSYSDATE,'SP_HIS_TJSFXM',PCZY000,PCZYKS0,PXXXX00); */ begin es :='1.1@'; select trim(VALUE0) into SF_TJGHLB from XT_XTCS00 where NAME00='SF_TJGHLB'; exception when others then SF_TJGHLB:='-1' ; end; --获取挂号类别 begin es :='1.2@'; select LBBH00,LBMC00 into LS_LBBH00,LS_LBMC00 from BM_GHLBB0 where LBBH00 =SF_TJGHLB; exception when others then LS_LBBH00:='-1'; LS_LBMC00:='免费号'; end; --体检开单科室编码维护=BM_BMBM00.BMBH00 默认值:null begin es :='1.3@'; select trim(VALUE0) into SF_TJKDKSBH from XT_XTCS00 where NAME00='SF_TJKDKSBH'; exception when others then SF_TJKDKSBH:='0' ; end; SF_TJKDKSBH:=nvl(SF_TJKDKSBH,'0'); --团体体检病人费别编号=IC_YBBRLB.FBBH00 默认值:null begin es :='1.4@'; select trim(VALUE0) into SF_TTTJBRFBBH from XT_XTCS00 where NAME00='SF_TTTJBRFBBH'; exception when others then SF_TTTJBRFBBH:='1' ; end; LS_FBBH00:=to_number(nvl(SF_TTTJBRFBBH,'1')); begin es :='1.5@'; select FBMC00 into LS_FBMC00 from BM_BRFBB0 where FBBH00 = LS_FBBH00; exception when others then LS_FBMC00:='自费'; end; --取医保机构 begin select YBLB00,YBZXLB into LS_YBLBBH,LS_YBZXLB from IC_YBBRLB where YBMC00 = LS_FBMC00; exception when no_data_found then begin select a.YBLB00,a.YBZXLB into LS_YBLBBH,LS_YBZXLB from IC_YBBRLB a,BM_FZXDY0 b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and b.FZXMC0 = LS_FBMC00; exception when no_data_found then LS_YBLBBH:='0'; LS_YBZXLB:=0; end; end; /* --取医保病人类别 begin select YBBRLB into LS_YBBRLB from BM_BRZTQK where ZTQKMC = as_BRZTQK and YBZXLB = LS_YBZXLB and FBBH00=LS_FBBH00; --先从BM_BRZTQK表中取，是否是城镇居民医保 exception when others then begin select YBBRLB into LS_YBBRLB from BM_YBBRLB where BRLBMC = as_BRZTQK and YBZXLB = LS_YBZXLB ; exception when others then LS_YBBRLB:='0'; end; end; */ LS_YBBRLB:='0'; begin es :='1.6@'; select JFLBMC into LS_JFLBMC from BM_BRJFLB where JFLBID = LS_JFLBID; exception when others then LS_JFLBMC:='门诊预交金'; end; --获取团体ID select TTID00,TTQC00,JZR000 into LS_TTID00,LS_TTQC00,LS_JZR000 from VW_PE_XMJZMX@HISPE where 1=1 and JZDH00=PTJJZDH and rownum=1; --查找体检的收费是否存在 select count(1) into VCOUNTER from BM_YGBM00 where YGBH00=LS_JZR000; /* if VCOUNTER>0 then LS_CZY000:=LS_JZR000; end if; */ begin es :='1.7@'; select ZWXM00,BMBH00,XKH000 into LS_CZYXM0,LS_CZYKS0,LS_XKH000 from BM_YGBM00 where YGBH00 = LS_CZY000; exception when others then LS_CZYXM0:='系统'; LS_CZYKS0:=0; end; --查找基本信息 LS_BRBLH0:=LS_GHHQZ0||LS_TTID00; LS_BRXM00:=substrb(LS_TTQC00,1,40); LS_MRN000:='0'; --病案号 LS_YBKH00:='0'; --病人医保卡号 LS_YBID00:='0'; --病人医疗保险号(医保ID) LS_YBLB00:=LS_YBLBBH; --病人医保类别,'0':非医保'1':省直医保,'2':市医保(对应病人所属分中心名称) --LS_BRXM00:='0'; --姓名 LS_BRXB00:='未知'; --性别 LS_BRXX00:=null; --血型 LS_BRCSRQ:=to_char(sysdate,'YYYYMMDD'); --出生日期 LS_BRHYZK:=null; --婚姻状况 LS_BRZY00:=null; --职业 LS_BRMZ00:=null; --民族 LS_BRGJ00:=null; --国籍 LS_BRZJLX:='身份证'; --证件类型 LS_BRZJBH:=null; --证件编号 --LS_FBBH00:='0'; --费别 F.K=BM_BRFBB0.FBBH00 LS_BRGFZH:='0'; --凭证号/公费证号 LS_BRDYFW:='本市(县)'; --地域范围;本市、外地(对应病人所属地区名称） LS_BRDH00:=null; --电话 LS_BRSZSF:=null; -- LS_BRSZS0:=null; -- LS_BRSZZ0:=null; -- LS_BRSZC0:=null; -- LS_BRJTDZ:=null; --家庭地址/通信地址 LS_BRYB00:=null; --邮编 LS_TYDWID:=null;--LS_TTID00; --单位ID LS_TYDWMC:=LS_TTQC00; --单位名称 LS_BRLXGX:=null; --联系人关系 LS_BRLXXM:=null; --联系人姓名 LS_BRLXDZ:=null; --联系人地址 LS_BRLXDH:=null; --联系人电话 LS_BRZTQK:=null; --职退情况 LS_BRJKQK:=null; --健康状况 LS_BRLX00:='初诊'; --病人类型 --LS_CZY000:='0'; --操作员ID LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); --操作日期 LS_CZSJ00:=to_char(sysdate,'hh24:mi:ss'); --操作时间 LS_RYCS00:=0; --入院次数 LS_SFYX00:='Y'; --状态 'Y':有效,'N':停用 LS_BZ0000:=null; --备注 --LS_YBBRLB:='0'; --医保病人类别 LS_SFYD00:='0'; --是否属于优待病人(挂号用，费用优待) LS_SFYXJB:='0'; --是否优先级别，挂号时使用(优先就诊) LS_BJJBIE:='04'; --保健级别 LS_TSSXMC:='普通'; --'普通' LS_FWBJ00:=null; --病人服务半径 LS_XZQH00:=null; --行政区号 LS_GRSFEN:=null; --个人身份类型 LS_GRSFMC:=null; --个人身份名称 LS_SFBLWT:=null; --是否办理委托0未办理1已办理 LS_CARD16:=null; --CARD16 LS_FBBH01:=LS_FBBH00; --住院费别 F.K=BM_BRFBB0.FBBH00 LS_YBBRL1:=LS_YBBRLB; --住院医保病人类别,-->BM_YBBRLB.YBBRLB LS_YBLB01:=LS_YBLBBH; --住院病人医保类别,-->IC_YBBRLB.YBLB00 LS_DAID00:='0'; --档案ID LS_BRCSSJ:=null; --病人(婴儿)出生时间,目前只保留时点数,比如01 LS_SFTJBR:='2'; --是否体检病人 0非体检病人 1 个人体检病人 2:单位体检 LS_BRSFHY:=null; --是否会员 LS_BRSBLB:=null; --商保类别 LS_BRYGCP:=null; --已购产品 LS_XJYE00:=0; --病人现金余额 LS_ZZYE00:=0; --病人转帐余额 LS_ZYE000:=0; --病人转帐余额 LS_YKZYE0:=0; --预扣后的总余额 LS_BJJE00:=0; --报警限额 LS_TYJE00:=0; --停用限额 LS_YXQFJE:=0; --允许病人的欠费的最大金额 LS_YBZHYE:=0; --医保账户余额 LS_KL0000:=null; --帐户口令 --创建病人基本信息(参考门诊建档过程SP_SF_BRXX00_REG000)--begin--- begin es :='1.8@'; select BRID00 into LS_BRID00 from BM_BRXXB0 A where BRBLH0=LS_BRBLH0 ; -- and BRXM00=LS_BRXM00; exception when others then LS_BRID00:=0; end; if nvl(LS_BRID00,'0')<=0 then select SQ_BM_BRXXB0_BRID00.nextval into LS_BRID00 from dual; select SQ_SF_BRZHXX_ZHYEID.nextval into LS_MZZHID from dual; --MZZHID --设置病人的基本信息,姓名、性别、病历号等信息 insert into BM_BRXXB0(BRID00,MRN000,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRXX00,BRCSRQ,BRHYZK,BRZY00,BRMZ00, BRGJ00,BRZJLX,BRZJBH,FBBH00,BRGFZH,BRDYFW,BRDH00,BRSZSF,BRSZS0,BRSZZ0,BRSZC0,BRJTDZ,BRYB00, TYDWID,TYDWMC,BRLXGX,BRLXXM,BRLXDZ,BRLXDH,BRZTQK,BRJKQK,BRLX00,CZY000,CZRQ00,CZSJ00,RYCS00, SFYX00,BZ0000,YBBRLB,SFYD00,SFYXJB,BJJBIE,TSSXMC,FWBJ00,XZQH00,GRSFEN,GRSFMC,SFBLWT,CARD16, MZZHID,FBBH01,YBBRL1,YBLB01,DAID00,BRCSSJ,SFTJBR,BRSFHY,BRSBLB,BRYGCP) values(LS_BRID00,LS_MRN000,LS_BRBLH0,LS_YBKH00,LS_YBID00,LS_YBLB00,LS_BRXM00,LS_BRXB00,LS_BRXX00,LS_BRCSRQ,LS_BRHYZK,LS_BRZY00,LS_BRMZ00, LS_BRGJ00,LS_BRZJLX,LS_BRZJBH,LS_FBBH00,LS_BRGFZH,LS_BRDYFW,LS_BRDH00,LS_BRSZSF,LS_BRSZS0,LS_BRSZZ0,LS_BRSZC0,LS_BRJTDZ,LS_BRYB00, LS_TYDWID,LS_TYDWMC,LS_BRLXGX,LS_BRLXXM,LS_BRLXDZ,LS_BRLXDH,LS_BRZTQK,LS_BRJKQK,LS_BRLX00,LS_CZY000,LS_CZRQ00,LS_CZSJ00,LS_RYCS00, LS_SFYX00,LS_BZ0000,LS_YBBRLB,LS_SFYD00,LS_SFYXJB,LS_BJJBIE,LS_TSSXMC,LS_FWBJ00,LS_XZQH00,LS_GRSFEN,LS_GRSFMC,LS_SFBLWT,LS_CARD16, LS_MZZHID,LS_FBBH01,LS_YBBRL1,LS_YBLB01,LS_DAID00,LS_BRCSSJ,LS_SFTJBR,LS_BRSFHY,LS_BRSBLB,LS_BRYGCP); --设置病人的账户信息 Insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00,CZYBH0,CZYXM0,XJYE00,ZZYE00, ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE,YBZHYE,KL0000) values(LS_MZZHID,LS_JFLBID,LS_BRID00,'0',LS_CZRQ00,LS_CZSJ00,LS_CZY000,LS_CZYXM0,LS_XJYE00,LS_ZZYE00, LS_ZYE000,LS_YKZYE0,LS_BJJE00,LS_TYJE00,LS_YXQFJE,LS_YBZHYE,LS_KL0000); end if; select * into VROW from BM_BRXXB0 A where BRID00=LS_BRID00; --创建病人基本信息(参考门诊建档过程SP_SF_BRXX00_REG000)--end--- --创建挂号表信息--begin--- --判断是否已经挂号 begin es :='1.9@'; Select GHID00,GHH000 into LS_GHID00,LS_GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=LS_GHHQZ0||PTJJZDH;--体检挂号号; exception when others then LS_GHID00:=0; LS_GHH000:='0'; end; LS_GHH000:=nvl(LS_GHH000,'0'); --生成挂号表 if LS_GHH000='0' then LS_TJGHH0:= null; SP_SF_BRXXB0_BRGH00( PBRID00=>LS_BRID00, --in number , --ID号 PGHLB00=>LS_LBBH00, --in number , --挂号类别 PGHKS00=>LS_CZYKS0, --in number , --挂号科室 PJZYS00=>LS_CZY000, --in number , --就诊医生 PGHHBID=>0, --in number , --挂号号表ID PSFYD00=>'N', --in varchar2 , --是否优待 PHZLB00=>0, --in number , --候诊类别 PHZXH00=>0, --in number , --候诊序号 PSFZZ00=>'N', --in varchar2 , --是否转诊 PGHH000=>LS_TJGHH0, --in out varchar2 , --挂号号 PCZY000=>LS_CZY000, --in number , --操作员 PCZYKS0=>LS_CZYKS0, --in number , --操作员科室 PCOMMIT=>'N', --in varchar2 default 'N' , --是否提交 PZXCGBZ=>LS_ZXCGBZ,--out number , --执行成功标志 1成功 0失败 PTSXX00=>PTSXX00, --out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 PXXXX00=>PXXXX00, --out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 PGHFJE0=>0, --in number default 0 , --挂号费金额 PZCFJE0=>0, --in number default 0 , --诊察费金额 PCWTS00=>'0', --in varchar2 default '0' , --错误提示 0报错提示 1不提示直接退出 PSFLNYX=>'N', --in varchar2 default 'N' , --是否老年优先就诊(>=80岁) PBXLB00=>'', --in varchar2 default '' , --保险类别 PBZ0000=>'', --in varchar2 default '' , --候诊病人预挂号备注 PSFXEJS=>'N', --in varchar2 default 'N' , --是否小儿加收BM_GHXMDY.XEJSXM PSFCZ00=>'0' --in varchar2 default '0' --初复诊 SF_BRXXB0.SFCZ00 ); if LS_ZXCGBZ = 0 then Ptsxx00 := '生成免费号错误:(SP_SF_BRXXB0_BRGH00)!'; return; end if; --修改挂号 LS_GHH000:=LS_GHHQZ0||PTJJZDH; update SF_BRXXB0 set GHH000=LS_GHH000 where BRID00=LS_BRID00 and GHH000=LS_TJGHH0; --重新获取挂号ID和挂号号 begin Select GHID00,GHH000 into LS_GHID00,LS_GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=LS_GHH000; exception when others then LS_GHID00:=0; LS_GHH000:='0'; end; end if; --创建挂号表信息--end--- --团体结账汇总视图VW_PE_TTJZHZ select YSJE00 as JSJE00,JZHJM0,SJJE00,JZR000 as JZYID0,JZRXM0,JZRQ00,JZSJ00,JZZT00, SFCL00 as SFCL00,FPTTMC,RWID00 into LS_JSJE00,LS_JZHJM0,LS_SJJE00,LS_JZYID0,LS_JZRXM0,LS_JZRQ00,LS_JZSJ00,LS_JZZT00,LS_SFCL00,LS_FPDWMC,LS_RWID00 from VW_PE_TTJZHZ@HISPE where JZDH00=PTJJZDH and rownum=1; if PFPTTMC is not null then LS_FPDWMC:=PFPTTMC; end if; --发票抬头名称 --判断是否有对应 select SF_HIS_ZLXMBHTS(PTJJZDH,'1') into LS_MSGSTR from dual; if trim(LS_MSGSTR) is not null then LS_MSGSTR:='未找到有效的诊疗项目或体检系统未对应!'||LS_MSGSTR; PTSXX00 :=LS_MSGSTR; return; end if; --判断是否有收费项目 select count(1) into VCOUNTER from VW_PE_XMJZMX@HISPE where 1=1 and JZDH00=PTJJZDH; if VCOUNTER<=0 then PTSXX00 :='未找到有效的团体结算数据！'; return; end if; for CUR_SF in CUR_TJXMSF loop --生成诊疗单--begin-- if SF_TJKDKSBH='0' then LS_KDKSBH:=nvl(CUR_SF.ZXKSBH,LS_CZYKS0); else LS_KDKSBH:=to_number(SF_TJKDKSBH); end if; LS_YJDJH0:= null; LS_ZLXMID:=SF_HIS_ZLXMID(CUR_SF.SFXMDM); --CUR_SF.SFXMDM=BM_ZLZD00.ZLXMID --LS_ZLXMID:=19515; --测试用 select count(1) into VCOUNTER from BM_ZLZD00 where ZLXMID=LS_ZLXMID and SFYX00 in ('1'); if VCOUNTER<=0 then Ptsxx00 := '未找到有效的诊疗项目,请到体检系统重新对应后再收费!SFXMDM:['||CUR_SF.SFXMDM||']SFXMMC:['||CUR_SF.SFXMMC||']'; return; end if; SP_YJ_YJSQWH( POPFLAG=>2,-- in number , --操作内容 1修改 2增加 3删除 PYJDJH0=>LS_YJDJH0,-- in out number , --操作的医技单据号 PZYGHID=>LS_GHID00,-- in number , --住院挂号ID PMZZYBZ=>'0',-- in varchar2 , --门诊住院标志 PZLXMID=>LS_ZLXMID,-- in number , --诊疗项目id PZXMID0=>null,-- in varchar2 , --子项目id串,如果没有传入参数，表示使用所有明细项 PZDID00=>null,-- in varchar2 , --诊断id PZDMC00=>null,-- in varchar2 , --诊断名称 PBBID00=>null,-- in varchar2 , --标本ID PBH0000=>null,-- in varchar2 , --特殊病种编号 PYJLRBZ=>'N',-- in varchar2 , --医技录入标志 PJZBZ00=>'N',-- in varchar2 , --急诊标志 PKDYS00=>nvl(CUR_SF.KDYSBH,LS_CZY000),-- in number , --开单医生 PKDKSBH=>LS_KDKSBH,-- in number , --开单科室编号 PKDBQ00=>null,-- in number , --开单病区 PYJKSBH=>LS_KDKSBH,-- in number , --医技科室编号 PSL0000=>CUR_SF.XMSL00,--CUR_SF.XMSL00,-- in number , --数量 PBZ0000=>null,-- in varchar2 , --备注 PFJDJBZ=>'0',-- in char , --附加单据 0主单1采血费2试管费3挂号费4诊查费5病历本6预约挂号7代煎9其他子单 PCZY000=>LS_CZY000,-- in number , --操作员 PCZYKS0=>LS_CZYKS0,-- in number , --操作员科室 PCOMMIT=>'N',-- in varchar2 default 'N' , --是否提交 PZXCGBZ=>LS_ZXCGBZ,-- out number , --执行成功标志 1成功 0失败 PTSXX00=>PTSXX00,-- out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 PXXXX00=>PXXXX00,-- out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 PYWMXID=>null,-- in number default null , --业务明细ID Popflag=3时 可能用到 PBWMC00=>null,-- in varchar2 default null, --部位名称 PFYJDJH=>null,-- in number default null , --父医技单据号 PCXJJ00=>1,-- in number default 1 , --重新计价 PYZCFID=>null,-- in varchar2 default null, --医嘱处方ID PSSDH00=>null,-- in varchar2 default null, --手术单号 PTXM000=>null,-- in varchar2 default null, --条形码 PWZBM00=>null,-- in varchar2 default null, --物资编码 PSSBM00=>null,-- in varchar2 default null, --手术编码 PSSMC00=>null,-- in varchar2 default null, --手术名称 PSSYJDH=>null,-- in varchar2 default null, --手术医技单据号 PWZBH00=>null,-- in varchar2 default null, --物资备货 PTQPC00=>0,-- in number default 0, --提取批次 PXMZK00=>1,-- in number default 1, --项目折扣（基数) PYJZHJE=>CUR_SF.XMJE00,-- in number default 0, --医技折后金额 by 2017.05.22 --PYJZHJE=>0,-- in number default 0, --医技折后金额 PTCID00=>null,-- in number default null, --套餐ID PMBMXID=>null,-- in number default null --模板明细ID PSFTJ00=>'Y' --varchar2 default 'N' --是否体检 N:否 Y:是 ); if LS_ZXCGBZ = 0 then Ptsxx00 := '生成医技单错误:(SP_YJ_YJSQWH)'; return; end if; --删除附加费(体检系统附加项目是独立开单) for CUR_FJ in CUR_YJDJGL loop delete YJ_YWJJ00 where YJDJH0=CUR_FJ.ZYJDJH; delete YJ_YWMX00 where YJDJH0=CUR_FJ.ZYJDJH; delete SF_TJSFMX where YJDJH0=CUR_FJ.ZYJDJH and BRID00=LS_BRID00 and TJJZDH=PTJJZDH ; delete YJ_YW0000 where YJDJH0=CUR_FJ.ZYJDJH and BRID00=LS_BRID00; delete YJ_YJDJGL where ZYJDJH=CUR_FJ.ZYJDJH; end loop; --生成诊疗单--end-- --收费(参数过程SP_SST_JSDP_JSMXQR_YKT)--begin-- if (LS_SFSCDJ= '1') then LS_SFSCDJ := '0'; select SQ_SF_BRJFB0_DJH000.nextval into LS_DJH000 from dual; end if; SP_SF_YJDJ00( ad_YJDJH0=>LS_YJDJH0, --in varchar2, --医技单据号(YJDJH1,YJDJH2,YJDJH3...) ad_DJH000=>LS_DJH000, --in number, --单据号 ad_ZXKS00=>LS_CZYKS0, --in number, --执行科室 ad_ZXYS00=>LS_CZY000, --in number, --执行医生 as_CZRQ00=>LS_CZRQ00, --in char, --操作日期 as_CZSJ00=>LS_CZSJ00, --in char, --操作时间 as_YHMSG0=>PTSXX00, --out varchar, --存储过程提示的错误信息 as_SYSMSG=>PXXXX00, --out varchar, --系统提示的错误信息 as_SFYDJ0=>'0', --in char default '0', --收费员登记 1是 0否 as_CXBZ00=>'Z', --in char default 'Z', --操作标志 Z记账插入 -冲销插入 as_SFYKBZ=>'0' --in char default '0' ); if PTSXX00 is not null then Ptsxx00 := '执行过程:(SP_SF_YJDJ00)错误!'; return; end if; --收费(参数过程SP_SST_JSDP_JSMXQR_YKT)--end-- --暂时不执行到收费为止 /* --医技单业务扣费执行--begin-- SP_YJ_YWZX00( PYJDJH0=>LS_YJDJH0,-- in NUMBER , --单据号 PZLXMID=>LS_ZLXMID,-- in NUMBER default 0 , --子项目ID PZXSL00=>1,--CUR_SF.XMSL00,-- in NUMBER , --执行数量 PZXMBZ0=>'2',-- in NUMBER , --子项目标志,1:执行子项目,2:大项目 PXKH000=>LS_XKH000,-- in BM_YGBM00.XKH000%TYPE , --执行人胸卡号 PZXKS00=>LS_CZYKS0,-- in NUMBER , --执行科室 PCOMMIT=>'N',-- in varchar2 default 'Y' , --提交标志 PSFXKCS=>0,-- in NUMBER DEFAULT 0 , --标识是否输血系统执行过程 PZXDJH0=>null,-- in number default null , --执行单据号 PZXRQ00=>null,-- in varchar2 default null , --执行日期 PCWTS00=>'0' -- in varchar2 default '0' --错误提示 0报错提示 1不提示直接退出 ); --医技单业务扣费执行--end-- */ --插入主表SF_TJSFMX select count(1) into VCOUNTER from SF_TJSF00 where BRID00=LS_BRID00 and TJJZDH=PTJJZDH and JZDH00=0; if VCOUNTER<=0 then select SQ_SF_TJSF00_ID0000.nextval into LS_ID0000 from dual; --插入主表SF_TJSFMX insert into SF_TJSF00(ID0000,BRID00,BRXM00,CZRQ00,CZSJ00,CZY000,TJH000,SFTT00,GHID00,GHH000, JZDH00,TJJZDH,JSJE00,JMJE00,SJJE00,JZYID0,JZRXM0,JZRQ00,JZSJ00,JZZT00, SFCL00,FPJE00,FPDWMC,DYRBH0,DYRXM0,DYRQ00,DYSJ00) values(LS_ID0000,LS_BRID00,VROW.BRXM00,LS_CZRQ00,LS_CZSJ00,LS_CZY000,LS_TJH000,'1',LS_GHID00,LS_GHH000, 0,PTJJZDH,LS_JSJE00,LS_JZHJM0,LS_SJJE00,LS_JZYID0,LS_JZRXM0,LS_JZRQ00,LS_JZSJ00,LS_JZZT00, LS_SFCL00,LS_FPJE00,LS_FPDWMC,LS_DYRBH0,LS_DYRXM0,LS_DYRQ00,LS_DYSJ00); else select ID0000 into LS_ID0000 from SF_TJSF00 where BRID00=LS_BRID00 and TJJZDH=PTJJZDH and JZDH00=0 and rownum=1; end if; --插入明细表SF_TJSFMX insert into SF_TJSFMX( ID0000,BRID00,BRXM00,CZRQ00,CZSJ00,CZY000,TJH000,CXDJH0,SFXMDM,SL0000,YSJE00, ZFJE00,TSJE00,SFYWID,SQXMID,SFXMMC,TFNTBZ,SFSF00,SFJZ00,SFTT00,SFTF00, GHID00,GHH000,ZXKSBH,ZXKSMC,KDYSBH,YJDJH0,SFDJH0,JZDH00,CZLX00,JZR000, JZRXM0,JZRQ00,TTID00,TTQC00,RWID00,RS0000,TJJZDH )values(LS_ID0000,LS_BRID00,VROW.BRXM00,LS_CZRQ00,LS_CZSJ00,LS_CZY000,null,0,CUR_SF.SFXMDM,CUR_SF.XMSL00,CUR_SF.XMJE00, CUR_SF.XMJE00,CUR_SF.XMJE00,null,CUR_SF.SQXMID,CUR_SF.SFXMMC,null,null,null,'1',null, LS_GHID00,LS_GHH000,CUR_SF.ZXKSBH,CUR_SF.ZXKSMC,CUR_SF.KDYSBH,LS_YJDJH0,LS_DJH000,0,'0',CUR_SF.JZR000, CUR_SF.JZRXM0,CUR_SF.JZRQ00,CUR_SF.TTID00,CUR_SF.TTQC00,CUR_SF.RWID00,CUR_SF.RS0000,CUR_SF.JZDH00 ); end loop; --取本次费用总额 select nvl(sum(HJJE00),0)HJJE00 into LS_HJJE00 from SF_BRFY00 D where D.JZDH00+0= 0 and D.BRID00=LS_BRID00 and D.MZID00=LS_GHID00 and D.MZH000=LS_GHH000 and D.JFLBID= 1 and D.DJH000=LS_DJH000; --重新算减免金额 LS_JMJE00:=nvl(LS_HJJE00,0)-nvl(LS_SJJE00,0); if LS_JMJE00<0 then Ptsxx00 := '体检实际结账金额高于HIS结算总金额!体检结算金额['||LS_SJJE00||']HIS结算金额['||LS_HJJE00||']'; return; end if; --执行减免金额插入--begin-- if LS_JMJE00>0 then LS_FPJMJE:=0; select SQ_SF_BRJFB0_DJH000.nextval into LS_JMDJH0 from dual; for CUR_JM in CUR_TJJMJE loop if CUR_JM.HJJE00>=LS_JMJE00 then LS_FPJMJE:=LS_JMJE00; else LS_FPJMJE:=CUR_JM.HJJE00; end if; if LS_FPJMJE>0 then --插入费用SF_FYMX00明细 select SQ_SF_FYMX00_MXID00.nextval into LS_MXID00 from dual; insert into SF_FYMX00( MXID00, DJH000, MZZHID, MZH000, JFLBID, BRID00, XM0000, BRFB00, YBLB00, BRYE00, CZY000, CZYXM0, CZYKS0, CXPJR0, CXYY00, SFYKBZ, ZZZHZF, XMBH00, SFLB00, XMMC00, XMGG00, XMDW00, XMDJ00, XMSL00, HJJE00, QZFJE0, ZFJE00, GFJE00, JZJE00, ZHJE00, JMJE00, JMSHR0, JMYY00, JMBZ00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, CXBZ00, KDKS00, YSKS00, KDYS00, KDBQ00, ZXKS00, ZXYS00, BEIZHU, ZFBL00, GJBM00, SFYDJ0, SFLSCF, YJDJH0, YJJZID, SHJG00, PH0000, OMXID0, JZDH00, MZFPID, HSXMID, KJXMID, YSZID0, GHKS00, GHY000,YBDJH0,GJDJ00) select LS_MXID00, LS_JMDJH0, max(MZZHID),max(MZH000), max(JFLBID), max(BRID00), max(XM0000), max(BRFB00), max(YBLB00), max(BRYE00), max(CZY000), max(CZYXM0), max(CZYKS0), max(CXPJR0), max(CXYY00), max(SFYKBZ), max(ZZZHZF), max(XMBH00), max(SFLB00), CUR_JM.FPXMMC||'减免', max(XMGG00), max(XMDW00), LS_FPJMJE, -1, -LS_FPJMJE, 0, 0, 0, -LS_FPJMJE, 0, 0, null, null, '3', to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:mi:ss'),null,null, '-', max(KDKS00), max(YSKS00), max(KDYS00), max(KDBQ00), max(ZXKS00), max(ZXYS00), null, max(ZFBL00), max(GJBM00), max(SFYDJ0), max(SFLSCF), max(YJDJH0), max(YJJZID), max(SHJG00), max(PH0000), max(MXID00), max(JZDH00), max(MZFPID), max(HSXMID), max(KJXMID), max(YSZID0), max(GHKS00), max(GHY000), max(YBDJH0), max(GJDJ00) from SF_FYMX00 where BRID00=LS_BRID00 and DJH000=LS_DJH000 and MZFPID=CUR_JM.MZFPID; LS_JMJE00:=LS_JMJE00-LS_FPJMJE; if LS_JMJE00<=0 then LS_JMJE00:=0; end if; end if; end loop; --插入SF_BRFY00 insert into SF_BRFY00(DJH000,YBDJH0,MZID00,MZH000,JFLBID,BRID00,XM0000,BRFB00,HJJE00, ZFJE00,GFJE00,JZJE00,BRYE00,CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0, JZDH00,YBLB00) select max(DJH000),'0',LS_GHID00,LS_GHH000,max(JFLBID),max(BRID00),max(XM0000),max(BRFB00),sum(HJJE00), sum(ZFJE00),sum(GFJE00),sum(JZJE00),max(BRYE00),max(CZRQ00),max(CZSJ00),max(CZY000),max(CZYXM0),max(CZYKS0), max(JZDH00),max(YBLB00) from SF_FYMX00 where DJH000 = LS_JMDJH0 and BRID00=LS_BRID00 and JMBZ00='3'; --插入中间表数据 insert into SF_TJSFMX( ID0000,BRID00,BRXM00,CZRQ00,CZSJ00,CZY000,TJH000,CXDJH0,SFXMDM,SL0000,YSJE00, ZFJE00,TSJE00,SFYWID,SQXMID,SFXMMC,TFNTBZ,SFSF00,SFJZ00,SFTT00,SFTF00, GHID00,GHH000,ZXKSBH,ZXKSMC,KDYSBH,YJDJH0,SFDJH0,JZDH00,CZLX00,JZR000, JZRXM0,JZRQ00,TTID00,TTQC00,RWID00,RS0000,TJJZDH,SFJM00) select LS_ID0000,BRID00,XM0000,CZRQ00,CZSJ00,CZY000,null,0,null,1,HJJE00, 0,0,null,null,null,null,null,null,'1',null, LS_GHID00,LS_GHH000,null,null,null,0,DJH000,0,0,LS_JZYID0, LS_JZRXM0,LS_JZRQ00,LS_TTID00,LS_TTQC00,LS_RWID00,0,PTJJZDH,'1' from SF_BRFY00 where DJH000 = LS_JMDJH0 and BRID00=LS_BRID00; end if; --执行减免金额插入--end-- select SQ_SF_JZB000_JZDH00.nextval into LS_JZDH00 from dual; select SQ_SF_PJSYQK_PJH000.nextval into LS_PJH000 from dual; --中间表 update SF_TJSFMX set JZDH00=LS_JZDH00 where BRID00=LS_BRID00 and ID0000=LS_ID0000; update SF_TJSF00 set JZDH00=LS_JZDH00,YNJMJE=nvl(LS_JMJE00,0) where BRID00=LS_BRID00 and ID0000=LS_ID0000; --修改费用表 select nvl(sum(HJJE00),0)HJJE00,nvl(sum(ZFJE00),0)ZFJE00,nvl(sum(GFJE00),0)GFJE00,nvl(sum(JZJE00),0) JZJE00 into LS_HJJE00,LS_ZFJE00,LS_GFJE00,LS_JZJE00 from SF_BRFY00 D where D.JZDH00+0= 0 and D.BRID00=LS_BRID00 and D.MZID00=LS_GHID00 and D.MZH000=LS_GHH000 and D.JFLBID= 1 and D.DJH000 in (select SFDJH0 from SF_TJSFMX where BRID00=LS_BRID00 and ID0000=LS_ID0000); update SF_BRFY00 D set YBDJH0 ='0', JZDH00 = LS_JZDH00 where D.JZDH00+0= 0 and D.MZID00=LS_GHID00 and D.MZH000=LS_GHH000 and D.BRID00 = LS_BRID00 and D.JFLBID = 1 and D.DJH000 in (select SFDJH0 from SF_TJSFMX where BRID00=LS_BRID00 and ID0000=LS_ID0000); update SF_FYMX00 set JZDH00=LS_JZDH00,YBDJH0='0' where BRID00= LS_BRID00 and DJH000 in (Select DJH000 from SF_BRFY00 where BRID00=ls_BRID00 and MZID00=LS_GHID00 and MZH000=LS_GHH000 and JZDH00=LS_JZDH00 ) ; --变量初始化 LS_BCKBCS:=0; --本次看病次数 LS_TSBZBH:=null; --特殊病种编码 LS_JMJE00:=0; --减免金额 LS_GRZHZF:=0; --个人医疗帐户支付(医保返回) LS_TCJJZF:=0; --统筹基金支付(医保返回) LS_ZFJE00:=0; --自付金额(医保返回) LS_JZRQ00:=to_char(sysdate,'YYYYMMDD'); --记账日期 LS_JZSJ00:=to_char(sysdate,'hh24:mi:ss');--结账时间 LS_JZY000:=LS_CZY000; --结账员 LS_GWYBZ0:=0; --公务员补助 LS_FYBJE0:=0; --非医保基恩 LS_YLZFJE:=0; --医疗自付金额 LS_YBZHYE:=0; --医保账户余额 LS_SBTCJJ:=0; --商保统筹基金 LS_SBGRZH:=0; --商保个人账户 LS_JJZFE0:=0; --基金自付额 LS_SBZFE0:=0; --商保个人自付 LS_BJZFE0:=0; --保健支付 LS_SYBXZF:=0; --商业保险 LS_XJFKJE:=0; --现金付款 LS_ZZFKJE:=0; --转账付款 LS_YHJE00:=0; --写入结账表 Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,BCKBCS,TSBZBH,JMJE00, GRZHZF,TCJJZF,ZFJE00,PJH000,JZRQ00,JZSJ00,JZY000,GWYBZ0,FYBJE0,YLZFJE, YBZHYE,SBTCJJ,SBGRZH,JJZFE0,SBZFE0,BJZFE0,SYBXZF,XJFKJE,ZZFKJE,YBDJH0, CZYKS0,FBBH00,YBBRLB,YBLB00,YBZXLB,JFLBID,YBGHH0) values(LS_JZDH00,LS_BRID00,LS_GHID00,LS_HJJE00,LS_JZJE00,LS_GFJE00,LS_HJJE00,LS_BCKBCS,LS_TSBZBH,LS_JMJE00, LS_GRZHZF,LS_TCJJZF,LS_ZFJE00,LS_PJH000,LS_JZRQ00,LS_JZSJ00,LS_JZY000,LS_GWYBZ0,LS_FYBJE0,LS_YLZFJE, LS_YBZHYE,LS_SBTCJJ,LS_SBGRZH,LS_JJZFE0,LS_SBZFE0,LS_BJZFE0,LS_SYBXZF,LS_XJFKJE,LS_ZZFKJE,'0', LS_CZYKS0,LS_FBBH00,LS_YBBRLB,LS_YBLB00,LS_YBZXLB,LS_JFLBID,'0'); --写入结账明细表 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select LS_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,1,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,SF_BRFY00 D where 1=1 and A.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = LS_JZDH00 and D.JFLBID = 1 and D.BRID00 = LS_BRID00 and D.MZID00=LS_GHID00 and D.MZH000=LS_GHH000 group by B.FPXMID,B.XMMC00; insert into SF_JZMXXM (JZDH00,MXID00,BZ0000) select JZDH00,MXID00,1 from SF_FYMX00 where brid00=LS_BRID00 and JZDH00=LS_JZDH00; --写入票据表 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00, DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00,HJJE00, ZFJE00,JZJE00,JMJE00,YHJE00,JFLBID,FPTTMC) values (LS_PJH000,1,'门诊发票',PPJXH00,LS_HJJE00,0,--PCZY000, --zj LS_JZY000,LS_CZYXM0,LS_JZRQ00, LS_JZSJ00,decode(nvl(PPJXH00,'0'),'0','N','Y'),'0',VROW.BRXM00,VROW.BRXB00,LS_PJH000,LS_BRID00,ls_HJJE00,ls_ZFJE00, LS_JZJE00,LS_JMJE00,LS_YHJE00,LS_JFLBID,LS_FPDWMC); --写入票据明细表 insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) select LS_PJH000,XMBH00,XMMC00,sum(XMJE00),'0',max(ZFBL00),sum(QZFJE0) from SF_JZMX00 where JZDH00=LS_JZDH00 group by XMBH00,XMMC00; --更新票据 es:='2@'||PCZY000||'@'; SP_SF_PJSYQK_INSERT( Ppjh000=>LS_PJH000, --in number , --票据号 Pczy000=>LS_CZY000, --in number , --输入：操作员编码 Pczyks0=>LS_CZYKS0, --in number , --操作员科室 Pcommit=>'N', --in varchar2 default 'N' , --是否提交 Pzxcgbz=>LS_ZXCGBZ, --out number , --执行成功标志 1成功 0失败 PTSXX00=>PTSXX00, --out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 PXXXX00=>PXXXX00 --out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); es:='3@'; if PTSXX00 is not null then Ptsxx00 := '执行过程:(SP_SF_PJSYQK_INSERT)错误!'; return; end if; --/* --2．团体结算审核 SP_PE_TTJSSH (用于HIS系统调用进行结算审核) SP_PE_TTJSSH@HISPE ( PCZBZ00=>'0', --In number 操作标志，0收费操作（审核）；1反收费 （反审核） PJZDH00=>PTJJZDH,-- In Number 结账单号 PBZ0000=>LS_CZYXM0,-- In Varchar2 备注，请传回收费员的姓名 PZXCGBZ=>LS_ZXCGBZ-- Out Number 成功标志 1操作成功，0失败 ); if LS_ZXCGBZ = 0 then Ptsxx00 := '执行团体结算审核错误:(SP_PE_TTJSSH)'; return; end if; -- */ PJZDH00:=LS_JZDH00; PPJH000:=LS_PJH000; PZXCGBZ:=1; --输出：执行成功标志 1成功 0失败 if pcommit='Y' then commit; end if; exception when no_data_found then rollback; raise_application_error(-20001,es||substrb('没有找到数据！*'||sqlerrm,1,240)); when Ecustom then rollback; raise_application_error(-20010,es||substrb(Ptsxx00||'！*',1,240)); when others then rollback; raise_application_error(-20002,es||substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;