-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SST_ZY_QDHBCX_YKT -- Generated 2017-07-25 11:20:39 from SD_HOSPITAL@KFYY CREATE OR REPLACE PROCEDURE sp_sst_zy_qdhbcx_ykt( P_ZDBH00 in varchar2, --入参: 终端编号 P_YYJGDM in varchar2, --入参: 医院机构代码 P_ZYHAO0 in varchar2, --入参:住院号 P_QSRQ00 in varchar2, --入参：起始日期 P_JZRQ00 in varchar2, --入参：截止日期 P_QDCXFS in varchar2, --入参：清单查询方式 0：日清单 1：汇总清单 P_QSYM00 in varchar2, --入参：起始页码 P_MYJLS0 in varchar2, --入参：每页记录数 P_BYRC00 in varchar2, --入参：备用入参 P_ZGJLS0 out varchar2, --出参: 总共记录数 P_BCFYZE out varchar2, --出参: 本次费用总额 P_BCZFZE out varchar2, --出参: 本次自费总额 P_BYCC00 out varchar2, --出参：备用出参 P_ERRMSG out varchar2 --出参：错误信息，空表示正确 ) -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2016.12.21 created:住院日清单查询 -- zhangyc 2017.01.02 按V1.24 整理 -- zhangyc 2017.02.15 修正Oracle 9i 执行慢问题 -- --------- ---------- ------- as LS_BRID00 ZY_BRXXB0.BRID00%type; --病人ID LS_ZYID00 ZY_BRXXB0.ZYID00%type; LS_CARDNO IC_YHXX00.ICKH00%type; --IC卡号 LS_ZT0000 IC_YHXX00.ZT0000%type; --IC卡状态 LS_SFDJ00 SF_BRZHXX.SFDJ00%type; --账户冻结标志 LS_ERRMSG varchar2(50); --错误提示信息 LS_KSRQ00 varchar2(20); LS_JSRQ00 varchar2(20); LS_ZGJLS0 number(12,2); --出参: 总共记录数 LS_BCFYZE number(12,2); --出参: 本次费用总额 LS_BCZFZE number(12,2); --出参: 本次自费总额 begin begin select SF_SST_ZYID00_YKT('','',P_ZYHAO0,'0') into LS_ZYID00 from dual; exception when others then LS_ZYID00:=0; end; if P_QSRQ00 is null then LS_KSRQ00:=to_char(sysdate,'YYYYMMDD'); else LS_KSRQ00:=P_QSRQ00; end if; if P_JZRQ00 is null then LS_JSRQ00:=to_char(sysdate,'YYYYMMDD'); else LS_JSRQ00:=P_JZRQ00; end if; delete SST_ZY_ZYQDCX where ZYID00=LS_ZYID00; delete SST_TEMP00 where ZYID00=LS_ZYID00; if P_QDCXFS='0' then --入参：清单查询方式 0：日清单 1：汇总清单 --先插入中间表 insert into SST_TEMP00(CZLX00,ZDBH00,PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01) select 'SP_SST_ZY_QDHBCX_YKT','P_ZDBH00',0,ZYID00,trim(ZYH000), CZRQ00,CZSJ00,nvl(trim(GJBM00),to_char(XMBH00))XMBH00,trim(XMMC00)XMMC00,trim(XMGG00)XMGG00, trim(XMDW00)XMDW00,XMDJ00,XMSL00,HJJE00,ZFBL01, ZXKSMC,ZYFPMC,HJJE00*ZFBL01 from VW_ZY_SSTRQD where zyid00=LS_ZYID00 and CZRQ00>=LS_KSRQ00 and CZRQ00<=LS_JSRQ00 order by CZRQ00,CZSJ00; --先插入中间表 insert into SST_TEMP00(CZLX00,ZDBH00,PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01) select CZLX00,ZDBH00,rownum PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01 from SST_TEMP00 where ZYID00=LS_ZYID00; Delete SST_TEMP00 where ZYID00=LS_ZYID00 and PXXH00=0; select count(1),DECODE(SUBSTR(sum(XMJE00),1,1),'.',0||sum(XMJE00),sum(XMJE00)), DECODE(SUBSTR(sum(TEMP01),1,1),'.',0||sum(TEMP01),sum(TEMP01)) into LS_ZGJLS0,LS_BCFYZE,LS_BCZFZE from SST_TEMP00 where ZYID00=LS_ZYID00 and CZRQ00>=LS_KSRQ00 and CZRQ00<=LS_JSRQ00; insert into SST_ZY_ZYQDCX( ZYID00 , --ZYID00 zyhao0 ,--住院号 QDCXFS , -- 0：日清单 1：汇总清单 CXFS00 , -- 查询方式(见附录) CXDM00 , -- 查询代码 SFRQ00 , -- 收费日期(YYYY-MM-DD) SFSJ00 , -- 收费时间(HH24:mm:ss) XMBH00 , -- 项目编号 XMMC00 , -- 项目名称 XMGG00 , -- 项目规格 XMDW00 , -- 项目单位 XMDJ00 , -- 项目单价 XMSL00 , -- 项目数量 XMJE00 , -- 项目金额 ZFBL00 , -- 自付比例 ZXKS00 , -- 执行科室 fpxmmc, HJJE00 , DYBZ00 ) select ZYID00, ZYH000, '0', '', CXDM00, CZRQ00, CZSJ00, XMBH00, XMMC00, XMGG00, XMDW00, trim(to_char(nvl(XMDJ00,0),'9999990.0000'))XMDJ00, trim(to_char(nvl(XMSL00,0),'9999990.00'))XMSL00, trim(to_char(nvl(XMJE00,0),'9999990.00'))XMJE00, ZFBL00, KSMC00 as ZXKSMC, FPMC00 as ZYFPMC , trim(to_char(nvl(XMJE00,0),'9999990.00'))XMJE00, '0' from SST_TEMP00 where ZYID00=LS_ZYID00 and CZRQ00>=LS_KSRQ00 and CZRQ00<=LS_JSRQ00 --and PXXH00>=(to_number(P_QSYM00)-1)*to_number(P_MYJLS0)+1 --and PXXH00<=to_number(P_QSYM00)*to_number(P_MYJLS0) order by CZRQ00; end if; if P_QDCXFS='1' then --入参：清单查询方式 0：日清单 1：汇总清单 --先插入中间表 insert into SST_TEMP00(CZLX00,ZDBH00,PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01) select 'SP_SST_ZY_QDHBCX_YKT','P_ZDBH00',0,ZYID00,trim(ZYH000), max(CZRQ00),max(CZSJ00),nvl(trim(GJBM00),to_char(XMBH00)),trim(XMMC00),trim(XMGG00), trim(XMDW00),XMDJ00,sum(XMSL00),sum(HJJE00),max(ZFBL01)ZFBL00, '' as ZXKS00,ZYFPMC,sum(HJJE00*ZFBL01)YBZFJE from VW_ZY_SSTRQD where 1=1 and ZYID00=LS_ZYID00 and CZRQ00>=LS_KSRQ00 and CZRQ00<=LS_JSRQ00 group by ZYID00,ZYH000,nvl(trim(GJBM00),to_char(XMBH00)),trim(XMMC00),trim(XMGG00),trim(XMDW00),XMDJ00,ZYFPMC order by trim(XMMC00); --先插入中间表 insert into SST_TEMP00(CZLX00,ZDBH00,PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01) select CZLX00,ZDBH00,rownum PXXH00,ZYID00,ZYH000, CZRQ00,CZSJ00,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,XMJE00,ZFBL00, KSMC00,FPMC00,TEMP01 from SST_TEMP00 where ZYID00=LS_ZYID00; Delete SST_TEMP00 where ZYID00=LS_ZYID00 and PXXH00=0; select count(1),DECODE(SUBSTR(sum(XMJE00),1,1),'.',0||sum(XMJE00),sum(XMJE00)), DECODE(SUBSTR(sum(TEMP01),1,1),'.',0||sum(TEMP01),sum(TEMP01)) into LS_ZGJLS0,LS_BCFYZE,LS_BCZFZE from SST_TEMP00 where ZYID00=LS_ZYID00; insert into SST_ZY_ZYQDCX( ZYID00 , --ZYID00 zyhao0 ,--住院号 QDCXFS , -- 0：日清单 1：汇总清单 CXFS00 , -- 查询方式(见附录) CXDM00 , -- 查询代码 SFRQ00 , -- 收费日期(YYYY-MM-DD) SFSJ00 , -- 收费时间(HH24:mm:ss) XMBH00 , -- 项目编号 XMMC00 , -- 项目名称 XMGG00 , -- 项目规格 XMDW00 , -- 项目单位 XMDJ00 , -- 项目单价 XMSL00 , -- 项目数量 XMJE00 , -- 项目金额 ZFBL00 , -- 自付比例 ZXKS00 , -- 执行科室 fpxmmc , HJJE00, DYBZ00 ) select ZYID00, ZYH000, '1' as QDCXFS, '' as CXFS00, --ZYH000 as CXDM00, CXDM00, CZRQ00, CZSJ00, XMBH00, XMMC00, XMGG00, XMDW00, trim(to_char(nvl(XMDJ00,0),'9999990.0000'))XMDJ00, trim(to_char(nvl(XMSL00,0),'9999990.00'))XMSL00, trim(to_char(nvl(XMJE00,0),'9999990.00'))XMJE00, ZFBL00, KSMC00 as ZXKSMC, FPMC00 as ZYFPMC , trim(to_char(nvl(XMJE00,0),'9999990.00'))XMJE00, '0' from SST_TEMP00 where ZYID00=LS_ZYID00 --and PXXH00>=(to_number(P_QSYM00)-1)*to_number(P_MYJLS0)+1 --and PXXH00<=to_number(P_QSYM00)*to_number(P_MYJLS0) order by XMMC00; update SST_ZY_ZYQDCX a set DYBZ00= nvl((select DYBZ00 from SST_ZY_QDDYZB b where b.ZYID00=a.ZYID00 and b.CXFS00=a.CXFS00 and rownum=1),'0') where ZYID00=LS_ZYID00; end if; P_ZGJLS0:=LS_ZGJLS0; --出参: 总共记录数 P_BCFYZE:=LS_BCFYZE; --出参: 本次费用总额 P_BCZFZE:=LS_BCZFZE; --出参: 本次自费总额 --出参: 本次自费总额 exception when no_data_found then P_ERRMSG:= substr(SQLERRM||'住院日清单查询失败！',1,255); when others then P_ERRMSG:= substr(SQLERRM||'住院日清单查询失败！',1,255); end; / -- Grants for Procedure GRANT EXECUTE ON sp_sst_zy_qdhbcx_ykt TO sst / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SST_ZY_QDHBCX_YKT