prompt No.050 获取未打印发票信息SP_SST_FPDY_FPLB_YKT create or replace procedure SP_SST_FPDY_FPLB_YKT( P_ZDBH00 in varchar2, --终端编号 P_CARDNO in varchar2, --卡号 P_CARDTYPE in varchar2, --0 代表院内卡 1 代表社保卡 P_QSSJ00 in varchar2, --起始时间(20110102) P_JZSJ00 in varchar2, --结束时间 P_ERRMSG out varchar2--无法获取明细的错误信息，空表示正确 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2014.05.29 create -- zhangyc 2014.06.20 把票据号移到打印的过程中处理 -- zhangyc 2014.07.15 增加字段YSXM00,BCKBCS,BCFYDX,YJJYE0 -- --------- ---------- ------- LS_SYSMSG varchar2(50); LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_CSRQ00 char(8); LS_YGBH00 BM_YGBM00.YGBH00%type; LS_YGXM00 BM_YGBM00.ZWXM00%type; LS_COUNT0 number(10); LS_CARDNO IC_YHXX00.ICKH00%TYPE; LS_BRID00 BM_BRXXB0.BRID00%TYPE; LS_XTGZH0 varchar2(32); --系统跟踪号 LS_BRNL00 varchar2(10); LS_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --病人医保中心类别 LS_TSBZBH varchar2(20); --特殊病种编号 LS_TSBZMC varchar2(50); --特殊病种名称 LS_GHID00 SF_BRXXB0.GHID00%TYPE; LS_GHYXM0 SF_BRXXB0.GHYXM0%TYPE; LS_GHKSMC varchar2(20); LS_DQFPH0 SST_FPDY_SERIALNO.DQFPH0%TYPE; LS_DQFPH1 SST_FPDY_SERIALNO.DQFPH0%TYPE; LS_QSFPH0 SST_FPDY_SERIALNO.QSFPH0%TYPE; LS_JZFPH0 SST_FPDY_SERIALNO.JZFPH0%TYPE; LS_PJID00 SST_FPDY_SERIALNO.ID0000%TYPE; LS_ZDZ000 varchar2(50); LS_YJJYE0 Number(12,2); cursor CUR_SF_PJXX00 is select JZDH00,PJH000 from VW_SF_FP0000 where BRID00=LS_BRID00 and JZRQ00>=P_QSSJ00 and JZRQ00<=P_JZSJ00 and DYBZ00='N' and PJZT00='0' order by JZDH00; cursor CUR_PJFPDY is select ZDBH00,ZDMC00,ZDLX00,PXXH00 from SF_PJFPDY where SFFS00='1' order by PXXH00; begin LS_SYSMSG:='SP_SST_FPDY_FPLB_YKT begin'; LS_CZRQ00 := to_char(sysdate,'YYYYMMDD'); LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); begin select YGBH00,ZWXM00 into LS_YGBH00,LS_YGXM00 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; LS_SYSMSG:='取病人信息'; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; begin select BRID00 into LS_BRID00 from IC_YHXX00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when NO_DATA_FOUND then P_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!'; return; end; begin Select BRCSRQ, SF_YS_CSRQTONL(BRCSRQ)BRNL00,C.YBZXLB into LS_CSRQ00,LS_BRNL00,LS_YBZXLB from BM_BRXXB0 a ,IC_YBBRLB c Where a.FBBH00=c.FBBH00 and a.YBLB00 = c.YBLB00 and a.brid00=LS_BRID00; exception when no_data_found then P_ERRMSG := '未找到改病人的基本信息!'; return; end; LS_SYSMSG:='判断是否有未打印的发票'; Select count(1) into LS_COUNT0 from VW_SF_FP0000 where BRID00=LS_BRID00 and JZRQ00>=P_QSSJ00 and JZRQ00<=P_JZSJ00 and DYBZ00='N' and PJZT00='0'; if nvl(LS_COUNT0,0)<=0 then P_ERRMSG:=P_QSSJ00||'至'||P_JZSJ00||'未找到未打印的发票信息！'; return; end if; Delete SST_FPDY_JBXX where CARDNO=P_CARDNO; Delete SST_FPDY_MXXX where CARDNO=P_CARDNO; delete SST_PJFPDY where CARDNO=P_CARDNO; --把未打印的发票插入到临时表 for CUR_PJ in CUR_SF_PJXX00 loop select TSBZBH,MZID00 into LS_TSBZBH,LS_GHID00 from VW_SF_FP0000 where BRID00=LS_BRID00 and JZDH00=CUR_PJ.JZDH00 and PJH000=CUR_PJ.PJH000; begin select MC0000 into LS_TSBZMC from BM_TSBZB0 where trim(BH0000)=LS_TSBZBH and YBZXLB =LS_YBZXLB; exception when no_data_found then LS_TSBZMC:='无'; end; Select B.BMMC00,A.GHYXM0 into LS_GHKSMC,LS_GHYXM0 from SF_BRXXB0 A,BM_BMBM00 b WHERE A.GHKS00=B.BMBH00 and a.BRID00=LS_BRID00 and a.GHID00=LS_GHID00; select nvl(ZYE000,0)-nvl(TYJE00,0) into LS_YJJYE0 from SF_BRZHXX where BRID00=LS_BRID00 and JFLBID=1; /* begin select ID0000,nvl(DQFPH0,'0'),nvl(QSFPH0,'0'),nvl(JZFPH0,'0') into LS_PJID00,LS_DQFPH0,LS_QSFPH0,LS_JZFPH0 from SST_FPDY_SERIALNO where ZDBH00=P_ZDBH00 and SFKY00='Y'; exception when no_data_found then LS_PJID00:=0; LS_DQFPH0:='0'; LS_QSFPH0:='0'; LS_JZFPH0:='0'; end; if (LS_DQFPH0='0') and (LS_QSFPH0<>'0') then LS_DQFPH0:=LS_QSFPH0; end if; */ --select SST_JSDP_JBXX_XTGZH0.nextval into LS_XTGZH0 from dual; select SQ_SST_FPDY_ID0000.nextval into LS_XTGZH0 from dual; insert into SST_FPDY_JBXX ( XTGZH0,ZDBH00,CARDNO,BRID00,JZDH00,JZY000,JZYXM0,PJH000,PJXH00,PJID00, GHID00,GHH000,JSRQ00,JSSJ00,YBKH00,YBID00,XMING0,XBIE00,BRNL00,BQBM00, BQMC00,CFXMS0,MZLSH0,DJLSH0,GHKSMC,HJJE00,BCBXF0,BCFYZE,ZHZFE0,GRZFE0, JJZFE0,GWYBZ0,FYBFY0,CFDXJE,GRZHYE,YLZFJE,SBTCJJ,SBGRZF,SYBXZF,SBJJZF, SSBJJJ,BJJJZF,BCYLJZ,YBJJZF,TMQFLJ,TMJJLJ,DYRQ00,DYSJ00,DYY000,DYYXM0, QTFPXX,SERIALNO,SFDY00,YSXM00,BCKBCS,BCFYDX,YJJYE0 ) select LS_XTGZH0,P_ZDBH00,P_CARDNO,BRID00,JZDH00,CZYBH0,CZYXM0,PJH000,LS_DQFPH0,LS_PJID00, LS_GHID00,GHH000,JZRQ00,JZSJ00,YBKH00,YBID00,BRXM00,BRXB00,LS_BRNL00,LS_TSBZBH, LS_TSBZMC,0,YBGHH0,YBDJH0,LS_GHKSMC,HJJE00,BRZJE0,BRZJE0,GRZHZF,ZFJE00, TCJJZF,GWYBZ0,FYBJE0,CFDXJE,YBZHYE,YLZFJE,SBTCJJ,SBGRZH,SYBXZF,SBZFE0, 0,BJZFE0,YBYL01,TCJJZF,YBYL09,YBYL10,'','','','',QYXX00,LS_DQFPH0,'N', LS_GHYXM0,BCKBCS,SF_SST_GetMoney_YKT(BRZJE0),LS_YJJYE0 --SP_BM_RMBDX0(BRZJE0) from VW_SF_FP0000 where BRID00=LS_BRID00 and JZDH00=CUR_PJ.JZDH00 and PJH000=CUR_PJ.PJH000; insert into SST_FPDY_MXXX (XTGZH0,CARDNO,BRID00,JZDH00,XMBH00,XMMC00,XMJE00,KPXMMC,BYXMMC) Select LS_XTGZH0,P_CARDNO,LS_BRID00,A.JZDH00,A.XMBH00,decode(nvl(B.KPXMMC,'0'),'0',A.XMMC00,B.KPXMMC)XMMC00,sum(XMJE00)XMJE00, B.KPXMMC, B.XMMC00 from SF_JZMX00 A,BM_FPXM00 B Where A.JZDH00=CUR_PJ.JZDH00 AND A.XMMC00=B.XMMC00 group by A.JZDH00,A.XMBH00,decode(nvl(B.KPXMMC,'0'),'0',A.XMMC00,B.KPXMMC),B.KPXMMC, B.XMMC00; /* insert into SST_PJXHDY(XTGZH0,JZDH00,PJH000,PJID00,PJXH00,SFYD00) values(LS_XTGZH0,CUR_PJ.JZDH00,CUR_PJ.PJH000,LS_PJID00,LS_DQFPH0,'N'); if nvl(LS_DQFPH0,'0')<>'0' then if LS_DQFPH0=LS_JZFPH0 then update SST_FPDY_SERIALNO set SFKY00='N',FPZFRQ=LS_CZRQ00,FPZFSJ=LS_CZSJ00,GXRQ00=LS_CZRQ00||' '||LS_CZSJ00 where ZDBH00=P_ZDBH00 and SFKY00='Y'; else --select lpad(LS_DQFPH0,greatest(length(trim(LS_DQFPH0)),length(nvl(LS_DQFPH0,0)+1)),'0') into LS_DQFPH1 from dual; select lpad(to_number(trim(LS_DQFPH0))+1, length(trim(LS_DQFPH0)),'0') into LS_DQFPH1 from dual; Update SST_FPDY_SERIALNO set DQFPH0 =LS_DQFPH1 where ID0000=LS_PJID00; end if; end if; */ for CUR_DY in CUR_PJFPDY loop select decode(CUR_DY.ZDBH00, 'BRBLH0',BRBLH0,'YBGHH0',YBGHH0,'MZID00',MZID00,'YBGHH0',YBGHH0,'YBKH00',YBKH00, 'YBID00',YBID00,'BRID00',BRID00,'BRXM00',BRXM00,'BRXB00',BRXB00,'BRZJE0',BRZJE0, 'TCJJZF',TCJJZF,'GRZHZF',GRZHZF,'ZFJE00',ZFJE00,'JZJE00',JZJE00,'GFJE00',GFJE00, 'JMJE00',JMJE00,'SBTCJJ',SBTCJJ,'SBGRZH',SBGRZH,'GWYBZ0',GWYBZ0,'SYBXZF',SYBXZF, 'BCKBCS',BCKBCS,'YBYL01',YBYL01,'YBYL02',YBYL02,'YBYL03',YBYL03,'YBYL04',YBYL04, 'YBYL05',YBYL05,'YBYL06',YBYL06,'YBYL07',YBYL08,'YBYL09',YBYL09,'YBYL10',YBYL10, 'YBYL11',YBYL11,'YBYL12',YBYL12,'YBYL13',YBYL13,'YBYL14',YBYL14,'YBYL15',YBYL15, 'YBYL16',YBYL16,'YBYL17',YBYL17,'YBYL18',YBYL18,'YBYL19',YBYL19,'YBYL20',YBYL20, 'YBYL21',YBYL21,'YBYL22',YBYL22,'YBYL23',YBYL23,'YBYL24',YBYL24,'YBYL25',YBYL25, 'YBZHYE',YBZHYE,'JZDH00',JZDH00) into LS_ZDZ000 from VW_SF_FP0000 where BRID00=LS_BRID00 and JZDH00=CUR_PJ.JZDH00 and PJH000=CUR_PJ.PJH000; --select CUR_DY.ZDBH00 into LS_ZDZ000 from VW_SF_FP0000 where BRID00=LS_BRID00 and JZDH00=CUR_PJ.JZDH00 and PJH000=CUR_PJ.PJH000; insert into SST_PJFPDY(XTGZH0,CARDNO,ZDBH00,ZDMC00,ZDLX00,ZDZ000,PXXH00) values(LS_XTGZH0,P_CARDNO,CUR_DY.ZDBH00,CUR_DY.ZDMC00,CUR_DY.ZDLX00,LS_ZDZ000,CUR_DY.PXXH00); end loop; end loop; LS_SYSMSG:='SP_SST_FPDY_FPLB_YKT end'; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'获取未打印发票信息失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'获取未打印发票信息失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt No.051 自助机发票打印SP_SST_FPDY_FPDY_YKT create or replace procedure SP_SST_FPDY_FPDY_YKT( P_ZDBH00 in varchar2, --终端编号 P_CARDNO in varchar2, --卡号 P_CARDTYPE in varchar2, --0 代表院内卡 1 代表社保卡 P_XTGZH0 in varchar2, --系统跟踪号 P_SERIALNO in varchar2, --发票流水号 P_SZZW00 out varchar2,--数字指纹 P_JDPH00 out varchar2,--机打票号 P_ERRMSG out varchar2--无法获取明细的错误信息，空表示正确 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2014.05.29 create -- zhangyc 2014.06.20 把票据号从获取发票移到这里 -- --------- ---------- ------- LS_SYSMSG varchar2(50); LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_CSRQ00 char(8); LS_YGBH00 BM_YGBM00.YGBH00%type; LS_YGXM00 BM_YGBM00.ZWXM00%type; LS_COUNT0 number(10); LS_CARDNO IC_YHXX00.ICKH00%type; LS_BRID00 BM_BRXXB0.BRID00%type; LS_BRNL00 varchar2(10); LS_YBZXLB IC_YBBRLB.YBZXLB%type; --病人医保中心类别 LS_PJXH00 SF_PJSYQK.PJXH00%type; LS_YGBMBH BM_YGBM00.BMBH00%TYPE; LS_DQFPH0 SST_FPDY_SERIALNO.DQFPH0%TYPE; LS_DQFPH1 SST_FPDY_SERIALNO.DQFPH0%TYPE; LS_QSFPH0 SST_FPDY_SERIALNO.QSFPH0%TYPE; LS_JZFPH0 SST_FPDY_SERIALNO.JZFPH0%TYPE; LS_PJID00 SST_FPDY_SERIALNO.ID0000%TYPE; LS_JZDH00 SST_FPDY_JBXX.JZDH00%TYPE; LS_PJH000 SST_FPDY_JBXX.PJH000%TYPE; begin LS_SYSMSG:='SP_SST_FPDY_FPDY_YKT begin'; LS_CZRQ00 := to_char(sysdate,'YYYYMMDD'); LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); begin select YGBH00,ZWXM00,BMBH00 into LS_YGBH00,LS_YGXM00,LS_YGBMBH from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; LS_SYSMSG:='取病人信息'; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; begin select BRID00 into LS_BRID00 from IC_YHXX00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when NO_DATA_FOUND then P_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!'; return; end; LS_SYSMSG:='判断是否有未打印的发票'; Select count(1) into LS_COUNT0 from SST_FPDY_JBXX where BRID00=LS_BRID00 and XTGZH0=P_XTGZH0; if nvl(LS_COUNT0,0)<=0 then P_ERRMSG:='未找到有效的发票信息！'; return; end if; Select PJH000,PJXH00,JZDH00 into LS_PJH000,LS_PJXH00,LS_JZDH00 from SST_FPDY_JBXX where BRID00=LS_BRID00 and XTGZH0=P_XTGZH0; LS_SYSMSG:='更新发票信息！'; LS_SYSMSG:='取当前票据号'; begin select ID0000,nvl(DQFPH0,'0'),nvl(QSFPH0,'0'),nvl(JZFPH0,'0') into LS_PJID00,LS_DQFPH0,LS_QSFPH0,LS_JZFPH0 from SST_FPDY_SERIALNO where ZDBH00=P_ZDBH00 and SFKY00='Y'; exception when no_data_found then LS_PJID00:=0; LS_DQFPH0:='0'; LS_QSFPH0:='0'; LS_JZFPH0:='0'; end; if (LS_DQFPH0='0') and (LS_QSFPH0<>'0') then LS_DQFPH0:=LS_QSFPH0; end if; LS_SYSMSG:='更新当前票据号'; Update SST_FPDY_JBXX set PJXH00=LS_DQFPH0,SERIALNO=LS_DQFPH0,PJID00=LS_PJID00 where BRID00=LS_BRID00 and XTGZH0=P_XTGZH0; insert into SST_PJXHDY(XTGZH0,JZDH00,PJH000,PJID00,PJXH00,SFYD00) values(P_XTGZH0,LS_JZDH00,LS_PJH000,LS_PJID00,LS_DQFPH0,'N'); LS_SYSMSG:='更新票据表的当前号'; if nvl(LS_DQFPH0,'0')<>'0' then if LS_DQFPH0=LS_JZFPH0 then update SST_FPDY_SERIALNO set SFKY00='N',FPZFRQ=LS_CZRQ00,FPZFSJ=LS_CZSJ00,GXRQ00=LS_CZRQ00||' '||LS_CZSJ00 where ZDBH00=P_ZDBH00 and SFKY00='Y'; else --select lpad(LS_DQFPH0,greatest(length(trim(LS_DQFPH0)),length(nvl(LS_DQFPH0,0)+1)),'0') into LS_DQFPH1 from dual; select lpad(to_number(trim(LS_DQFPH0))+1, length(trim(LS_DQFPH0)),'0') into LS_DQFPH1 from dual; Update SST_FPDY_SERIALNO set DQFPH0 =LS_DQFPH1 where ID0000=LS_PJID00; end if; end if; LS_PJXH00:=nvl(LS_DQFPH0,'0'); Update SF_PJSYQK set PJXH00=LS_PJXH00,SFDY00='Y',CZYKS0=decode(CZYKS0,0,LS_YGBMBH,CZYKS0) where PJH000=LS_PJH000; update SST_FPDY_JBXX set DYRQ00=LS_CZRQ00,DYSJ00=LS_CZSJ00,DYY000=LS_YGBH00,DYYXM0=LS_YGXM00,SFDY00='Y' where BRID00=LS_BRID00 and XTGZH0=P_XTGZH0; update SST_PJXHDY set SFYD00='Y' where XTGZH0=P_XTGZH0; LS_SYSMSG:='备份发票打印信息！'; Insert into SST_FPDY_JBXX_LOG select * from SST_FPDY_JBXX where BRID00=LS_BRID00 and XTGZH0=P_XTGZH0; insert into SST_FPDY_MXXX_LOG select * from SST_FPDY_MXXX where XTGZH0=P_XTGZH0; insert into SST_PJFPDY_LOG select * from SST_PJFPDY where XTGZH0=P_XTGZH0; P_SZZW00:=LS_PJH000;--数字指纹 P_JDPH00:=LS_PJXH00;--机打票号 LS_SYSMSG:='SP_SST_FPDY_FPDY_YKT end'; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'自助机发票打印失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'自助机发票打印失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); END; / show error; prompt No.049 自助机发票票据登记过程SP_SST_FPDY_SERIALNO_YKT create or replace procedure SP_SST_FPDY_SERIALNO_YKT( P_ZDBH00 in varchar2, --终端编号 P_QSFPH0 in varchar2, --起始发票号 P_JZFPH0 in varchar2, --截止发票号 P_ERRMSG out varchar2--无法获取明细的错误信息，空表示正确 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2014.05.29 create -- --------- ---------- ------- LS_SYSMSG varchar2(50); LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_YGBH00 BM_YGBM00.YGBH00%type; LS_YGXM00 BM_YGBM00.ZWXM00%type; LS_COUNT0 number(10); begin LS_SYSMSG:='SP_SST_FPDY_SERIALNO_YKT begin'; LS_CZRQ00 := to_char(sysdate,'YYYYMMDD'); LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); begin select YGBH00,ZWXM00 into LS_YGBH00,LS_YGXM00 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; LS_SYSMSG:='判断是否有有效的票据号'; if length(trim(P_QSFPH0))<>length(trim(P_JZFPH0)) then P_ERRMSG:='起始发票号和截止发票号长度不一致！'; return; end if; Select count(1)into LS_COUNT0 from SST_FPDY_SERIALNO where ZDBH00=P_ZDBH00 and SFKY00='Y'; if nvl(LS_COUNT0,0)>0 then --把有效的发票作废掉保证只有一条有效的票据 update SST_FPDY_SERIALNO set SFKY00='N',FPZFRQ=LS_CZRQ00,FPZFSJ=LS_CZSJ00,GXRQ00=LS_CZRQ00||' '||LS_CZSJ00 where ZDBH00=P_ZDBH00 and SFKY00='Y'; end if; --插入票据 insert into SST_FPDY_SERIALNO(ID0000,ZDBH00,SFYBM0,SFYXM0,QSFPH0, JZFPH0,DQFPH0,GXRQ00,SFKY00,PJLXBM, PJLXMC,FPDJRQ,FPDJSJ,FPZFRQ,FPZFSJ) values(SQ_SST_FPDY_ID0000.nextval,P_ZDBH00,ls_YGBH00,LS_YGXM00,P_QSFPH0, P_JZFPH0,P_QSFPH0,'','Y',1, '门诊发票',LS_CZRQ00,LS_CZSJ00,'',''); LS_SYSMSG:='SP_SST_FPDY_SERIALNO_YKT end'; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'自助机发票票据登记失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'自助机发票票据登记失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); END; / show error;