create or replace procedure SP_ZY_JSQDQUERY_ITEMINFO( PYBJKID in number ,--[1]输入:医保接口ID PZYID00 in number ,--[2]输入:住院ID PJZDH00 in number ,--[3]输入:结账单号 PCOMMIT in varchar2 default 'N' ,--[4]输入:是否提交事务 Y:提交 N:不提交 PQQXML0 in varchar2 ,--[5]输入:请求参数(XML格式) PZXZT00 out number ,--[6]输出:执行状态 0:失败 1：成功 PERRMSG out varchar2 ,--[7]输出:错误信息 PFHXML0 out varchar2 ,--[8]输出:返回参数(XML格式) PFHNUM0 out number --[9]输出:返回记录数 ) as -- MODIFICATION HISTORY -- Person Date Comments --zhangyc 2023.08.29 create by 住院医疗保障基金结算清单(收费项目信息) LS_iteminfo_Json ZY_YBJKMX.VALUE0%type;--[字符]收费项目信息 LS_iteminfo_Num number(12); --[数量]收费项目信息 LS_iteminfoDataType varchar2(10);--0:ZY_YBFPMX或SF_YBFPMX 1:ZY_JZMX00或SF_JZMX00 LS_AAZ149 ZY_YBJKRZ.AAZ149%type;--病种编码 LS_YBYL04 ZY_JZB000.YBYL04%type;--单病种限额 LS_YBYL33 ZY_JZB000.YBYL33%type;--超标床位费用(付费单病种写入) LS_YBYL34 ZY_JZB000.YBYL34%type;--除外耗材费用(付费单病种写入) LS_AKC227 ZY_YBJKRZ.AKC227%type;--医疗费总金额 LS_DRGZFE ZY_YBJKRZ.DRGZFE%type;--DRG付费标准或单病种定额 LS_BKC101 ZY_YBJKRZ.BKC101%type;--除外耗材费用 LS_BKC125 ZY_YBJKRZ.BKC125%type;--超标床位费用 --收费项目信息开始(病种发票) cursor CUR_BZFPMX is select tt.med_chrgitm as med_chrgitm, tt.adm_cond_type_mc as adm_cond_type_mc,--医疗项目名称 sum(tt.amt) as amt, sum(tt.claa_sumfee) as claa_sumfee, sum(tt.clab_amt) as clab_amt, sum(tt.fulamt_ownpay_amt) as fulamt_ownpay_amt, sum(tt.oth_amt) as oth_amt from (select '92' as med_chrgitm,--1 med_chrgitm 医疗收费项目 92:单病种标准,90:单病种超标床位费,91:单病种除外耗材 '单病种标准' as adm_cond_type_mc,--医疗项目名称 LS_YBYL04 as amt,--2 amt 金额 number(16,2) =akc227 0 as claa_sumfee,--3 claa_sumfee 甲类费用合计 number(16,2)=bkc008 0 as clab_amt,--4 clab_amt 乙类金额 number(16,2)=bkc009 0 as fulamt_ownpay_amt,--5 fulamt_ownpay_amt 全自费金额 number(16,2)=bkc011 0 as oth_amt--6 oth_amt 其他金额 number(16,2) =bkc010 from dual union all select '90' as med_chrgitm,--1 med_chrgitm 医疗收费项目 92:单病种标准,90:单病种超标床位费,91:单病种除外耗材 '单病种超标床位费' as adm_cond_type_mc,--医疗项目名称 LS_YBYL33 as amt,--2 amt 金额 number(16,2) =akc227 0 as claa_sumfee,--3 claa_sumfee 甲类费用合计 number(16,2)=bkc008 0 as clab_amt,--4 clab_amt 乙类金额 number(16,2)=bkc009 0 as fulamt_ownpay_amt,--5 fulamt_ownpay_amt 全自费金额 number(16,2)=bkc011 0 as oth_amt--6 oth_amt 其他金额 number(16,2) =bkc010 from dual union all select '91' as med_chrgitm,--1 med_chrgitm 医疗收费项目 92:单病种标准,90:单病种超标床位费,91:单病种除外耗材 '单病种除外耗材' as adm_cond_type_mc,--医疗项目名称 LS_YBYL34 as amt,--2 amt 金额 number(16,2) =akc227 0 as claa_sumfee,--3 claa_sumfee 甲类费用合计 number(16,2)=bkc008 0 as clab_amt,--4 clab_amt 乙类金额 number(16,2)=bkc009 0 as fulamt_ownpay_amt,--5 fulamt_ownpay_amt 全自费金额 number(16,2)=bkc011 0 as oth_amt--6 oth_amt 其他金额 number(16,2) =bkc010 from dual ) tt group by tt.med_chrgitm,tt.adm_cond_type_mc ; --收费项目信息开始 cursor CUR_iteminfo is select tt.med_chrgitm as med_chrgitm, tt.adm_cond_type_mc, sum(tt.amt) as amt, sum(tt.claa_sumfee) as claa_sumfee, sum(tt.clab_amt) as clab_amt, sum(tt.fulamt_ownpay_amt) as fulamt_ownpay_amt, sum(tt.oth_amt) as oth_amt from(select bb.AKA063 as med_chrgitm,--1 med_chrgitm 医疗收费项目 varchar2(6) bb.AKA063_MC as adm_cond_type_mc,--医疗项目名称 nvl(bb.akc227,0) as amt,--2 amt 金额 number(16,2) =akc227 nvl(bb.bkc008,0) as claa_sumfee,--3 claa_sumfee 甲类费用合计 number(16,2)=bkc008 nvl(bb.bkc009,0) as clab_amt,--4 clab_amt 乙类金额 number(16,2)=bkc009 nvl(bb.bkc011,0) as fulamt_ownpay_amt,--5 fulamt_ownpay_amt 全自费金额 number(16,2)=bkc011 nvl(bb.bkc010,0) as oth_amt--6 oth_amt 其他金额 number(16,2) =bkc010 from ZY_JZB000 AA,ZY_YBFPMX BB where AA.JZDH00=BB.JZDH00 and aa.ZYID00=PZYID00 and ((nvl(PJZDH00,0)>0 and aa.JZDH00=PJZDH00) or (nvl(PJZDH00,0)=0 and 1=1)) and LS_AAZ149 is null and nvl(LS_iteminfoDataType,'0') not in ('1') ) tt group by tt.med_chrgitm,tt.adm_cond_type_mc ; --收费项目信息开始（普通） cursor CUR_YNFPMX is select tt.med_chrgitm as med_chrgitm, ff.SXSM00 as adm_cond_type_mc, sum(tt.amt) as amt, sum(tt.claa_sumfee) as claa_sumfee, sum(tt.clab_amt) as clab_amt, sum(tt.fulamt_ownpay_amt) as fulamt_ownpay_amt, sum(tt.oth_amt) as oth_amt from(select nvl(CC.GJFPBH,'14') as med_chrgitm,--1 med_chrgitm 医疗收费项目 varchar2(6) nvl(bb.XMJE00,0) as amt,--2 amt 金额 number(16,2) =akc227 0 as claa_sumfee,--3 claa_sumfee 甲类费用合计 number(16,2)=bkc008 0 as clab_amt,--4 clab_amt 乙类金额 number(16,2)=bkc009 0 as fulamt_ownpay_amt,--5 fulamt_ownpay_amt 全自费金额 number(16,2)=bkc011 0 as oth_amt--6 oth_amt 其他金额 number(16,2) =bkc010 from ZY_JZB000 AA,ZY_JZMX00 BB,BM_FPXM00 CC where AA.JZDH00=BB.JZDH00 and BB.XMBH00 =CC.FPXMID and aa.ZYID00=PZYID00 and ((nvl(PJZDH00,0)>0 and aa.JZDH00=PJZDH00) or (nvl(PJZDH00,0)=0 and 1=1)) ) tt,bm_ybsxzd ff where tt.med_chrgitm=ff.VALUE0 and ff.JKBM00=2 and ff.sxbm00='med_chrgitm_type' group by tt.med_chrgitm,ff.SXSM00 ; begin PZXZT00:=0;--输出:执行状态 0:失败 1：成功 PERRMSG:='';--输出:错误信息 PFHXML0:='';--输出:返回参数(XML格式) PFHNUM0:=0; --输出:返回记录数 LS_iteminfo_Json:=null;--[字符]收费项目信息 LS_iteminfo_Num :=0; --[数量]收费项目信息 --解析入参数 LS_iteminfoDataType :=trim(substrb(SF_XT_GETXMLSTR('iteminfoDataType',PQQXML0),1,50));--0:ZY_YBFPMX或SF_YBFPMX 1:ZY_JZMX00或SF_JZMX00 LS_AAZ149 :=trim(substrb(SF_XT_GETXMLSTR('AAZ149',PQQXML0),1,50));--病种编码 LS_YBYL04 :=SF_SF_STRTONUM(trim(substrb(SF_XT_GETXMLSTR('YBYL04',PQQXML0),1,50)));--单病种限额 LS_YBYL33 :=SF_SF_STRTONUM(trim(substrb(SF_XT_GETXMLSTR('YBYL33',PQQXML0),1,50)));--超标床位费用(付费单病种写入) LS_YBYL34 :=SF_SF_STRTONUM(trim(substrb(SF_XT_GETXMLSTR('YBYL34',PQQXML0),1,50)));--除外耗材费用(付费单病种写入) --单病种--begin-- if LS_AAZ149 is not null then LS_AKC227:=0; --医疗费总金额 LS_DRGZFE:=0;--DRG付费标准或单病种定额 LS_BKC101:=0;--除外耗材费用 LS_BKC125:=0;--超标床位费用 for C_List in CUR_BZFPMX loop LS_iteminfo_Json:=null; LS_iteminfo_Json:=LS_iteminfo_Json||'{'; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm"'||':'||'"'||C_List.med_chrgitm||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm_mc"'||':'||'"'||C_List.adm_cond_type_mc||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"amt"'||':'||'"'||C_List.amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"claa_sumfee"'||':'||'"'||C_List.claa_sumfee||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"clab_amt"'||':'||'"'||C_List.clab_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"fulamt_ownpay_amt"'||':'||'"'||C_List.fulamt_ownpay_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"oth_amt"'||':'||'"'||C_List.oth_amt||'"'||''; LS_iteminfo_Json:=LS_iteminfo_Json||'}'; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_iteminfo_Num,'iteminfo','data','iteminfo','收费项目信息',null,null,'1',null,LS_iteminfo_Json from dual; --'90','单病种超标床位费','91','单病种除外耗材','92','单病种标准' if C_List.med_chrgitm='92' then LS_DRGZFE:=nvl(C_List.amt,0);--DRG付费标准或单病种定额 elsif C_List.med_chrgitm='91' then LS_BKC101:=nvl(C_List.amt,0);--除外耗材费用 elsif C_List.med_chrgitm='90' then LS_BKC125:=nvl(C_List.amt,0);--超标床位费用 end if; LS_AKC227:=nvl(LS_AKC227,0)+nvl(C_List.amt,0); LS_iteminfo_Num:=nvl(LS_iteminfo_Num,0)+1; end loop; --单病种--end-- --非单病种--begin-- PFHXML0:='';--输出:返回参数(XML格式) PFHXML0:=substrb(PFHXML0||SF_XT_XMLJSON('AKC227',LS_AKC227,0,0,null,null),1,2000); PFHXML0:=substrb(PFHXML0||SF_XT_XMLJSON('DRGZFE',LS_DRGZFE,0,0,null,null),1,2000); PFHXML0:=substrb(PFHXML0||SF_XT_XMLJSON('BKC101',LS_BKC101,0,0,null,null),1,2000); PFHXML0:=substrb(PFHXML0||SF_XT_XMLJSON('BKC125',LS_BKC125,0,0,null,null),1,2000); else for C_List in CUR_iteminfo loop LS_iteminfo_Json:=null; LS_iteminfo_Json:=LS_iteminfo_Json||'{'; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm"'||':'||'"'||C_List.med_chrgitm||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm_mc"'||':'||'"'||C_List.adm_cond_type_mc||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"amt"'||':'||'"'||C_List.amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"claa_sumfee"'||':'||'"'||C_List.claa_sumfee||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"clab_amt"'||':'||'"'||C_List.clab_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"fulamt_ownpay_amt"'||':'||'"'||C_List.fulamt_ownpay_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"oth_amt"'||':'||'"'||C_List.oth_amt||'"'||''; LS_iteminfo_Json:=LS_iteminfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,med_chrgitm_type,adm_cond_type_mc,amt,claa_sumfee,clab_amt,fulamt_ownpay_amt,oth_amt) select PYBJKID,'iteminfo','收费项目信息',LS_iteminfo_Num, substrb(C_List.med_chrgitm,1,10) as med_chrgitm, C_List.adm_cond_type_mc, C_List.amt as amt, C_List.claa_sumfee as claa_sumfee, C_List.clab_amt as clab_amt, C_List.fulamt_ownpay_amt as fulamt_ownpay_amt, C_List.oth_amt as oth_amt from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_iteminfo_Num,'iteminfo','data','iteminfo','收费项目信息',null,null,'1',null,LS_iteminfo_Json from dual; LS_iteminfo_Num:=nvl(LS_iteminfo_Num,0)+1; end loop; --直接取SF_JZMX00--begin-- --未找到有效数据按院内生成 if nvl(LS_iteminfo_Num,0)=0 then for C_List in CUR_YNFPMX loop LS_iteminfo_Json:=null; LS_iteminfo_Json:=LS_iteminfo_Json||'{'; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm"'||':'||'"'||C_List.med_chrgitm||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"med_chrgitm_mc"'||':'||'"'||C_List.adm_cond_type_mc||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"amt"'||':'||'"'||C_List.amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"claa_sumfee"'||':'||'"'||C_List.claa_sumfee||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"clab_amt"'||':'||'"'||C_List.clab_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"fulamt_ownpay_amt"'||':'||'"'||C_List.fulamt_ownpay_amt||'"'||','; LS_iteminfo_Json:=LS_iteminfo_Json||'"oth_amt"'||':'||'"'||C_List.oth_amt||'"'||''; LS_iteminfo_Json:=LS_iteminfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,med_chrgitm_type,adm_cond_type_mc,amt,claa_sumfee,clab_amt,fulamt_ownpay_amt,oth_amt) select PYBJKID,'iteminfo','收费项目信息', substrb(C_List.med_chrgitm,1,10) as med_chrgitm, C_List.adm_cond_type_mc, C_List.amt as amt, C_List.claa_sumfee as claa_sumfee, C_List.clab_amt as clab_amt, C_List.fulamt_ownpay_amt as fulamt_ownpay_amt, C_List.oth_amt as oth_amt from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_iteminfo_Num,'iteminfo','data','iteminfo','收费项目信息',null,null,'1',null,LS_iteminfo_Json from dual; LS_iteminfo_Num:=nvl(LS_iteminfo_Num,0)+1; end loop; end if; --直接取SF_JZMX00--end-- end if; --非单病种--end-- --无数据插入一笔空 if nvl(LS_iteminfo_Num,0)=0 then insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,SQ_ZY_YBJKMX_JKMXID.nextval,'iteminfo','data','iteminfo','收费项目信息',null,null,'1',null,LS_iteminfo_Json from dual; end if; --是否生成iteminfo--begin--- --补全发票明细 insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,med_chrgitm_type,adm_cond_type_mc,amt,claa_sumfee,clab_amt,fulamt_ownpay_amt,oth_amt) select PYBJKID,'iteminfo','收费项目信息',substrb(b.VALUE0,1,10) as MED_CHRGITM_TYPE,SXSM00 as adm_cond_type_mc, 0 as AMT,0 as CLAA_SUMFEE,0 as CLAB_AMT, 0 as FULAMT_OWNPAY_AMT,0 as OTH_AMT from BM_YBSXZD b where b.JKBM00=2 and b.sxbm00='med_chrgitm_type' and b.VALUE0 not in ('90','91','92') and substrb(VALUE0,1,10) not in (select nvl(A.med_chrgitm_type,'14') from ZY_YBJSDX a where a.YBJKID=PYBJKID and a.JKJDBM='iteminfo') ; if PCOMMIT='Y' then commit; end if; PZXZT00:=1; --输出:执行状态 0:失败 1：成功 PERRMSG:=null; --输出:错误信息 PFHNUM0:=nvl(LS_iteminfo_Num,0);--输出:返回记录数 exception when no_data_found then PZXZT00:=0; PERRMSG:=substrb('住院医疗保障基金结算清单(收费项目信息)失败！原因:'||sqlerrm,1,200); when others then PZXZT00:=0; PERRMSG:=substrb('住院医疗保障基金结算清单(收费项目信息)失败！原因:'||sqlerrm,1,200); end; /