prompt 家庭病床接口JSON报文 SF_ZY_JTBCJK_JSONSTR
create or replace function SF_ZY_JTBCJK_JSONSTR
(
PJKCJBM varchar2 default '', --接口场景编码
PCZY000 number default 0, --操作员=BM_YGBM00.YGBH00
PCZYKS0 number default 0, --操作员科室=BM_YGBM00.BMBH00
PINTSTR varchar2 default '' --备用参数 xml编码格式传入(其它请求入参或HIS前端请求入参数)
)
-- modification history
-- Person Date Comments
-- chenHeyi 2023.08.29 create ZYSF-20230828-004
-- chenHeyi 2023.09.14 增加ZYSF_JTBCJK_0002，ZYSF_JTBCJK_1002，ZYSF_JTBCJK_1003 for ZYSF-20230914-002
-- chenHeyi 2023.09.22 增加ZYSF_JTBCJK_0003 BQ-20230922-001
-- chenHeyi 2023.09.28 增加ZYSF_JTBCJK_0004，ZYSF_JTBCJK_0005，ZYSF_JTBCJK_0006 ZYSF-20231007-001
-- chenHeyi 2023.10.27 调整费用明细查询太慢 ZYSF-20231031-002
-- chenHeyi 2023.11.01 char型字段长度过长，加去空 ZYSF-20231031-003
-- chenHeyi 2023.11.07 住院信息加ID0000，医护人员性别取值
-- -------- ---------- ---------
return clob is
LS_FHXX00 clob; --
LS_JSONSTR clob;
ld_Counts number(10);
ls_id0000 ZY_JTBCSQ.id0000%type;
R_JTBCSQ ZY_JTBCSQ%rowtype;
ls_yyid00 BM_TYZD00.MC0000%type;
ls_jkbj BM_TYZD00.MC0000%type;
ls_keyvalue BM_TYZD00.MC0000%type;
ls_MRN000 ZY_BRXXB0.MRN000%type;
ls_YGBH00 varchar2(30);
R_YGBM00 BM_YGBM00%rowtype;
ls_ygxz00 varchar2(10);
ls_BMBH00 varchar(20);
R_BMBM00 BM_BMBM00%rowtype;
ls_CH0000 varchar(20);
R_CWK000 BM_CWK000%rowtype;
R_BM_BRXXB0 BM_BRXXB0%rowtype;
ls_ZYID00 varchar(20);
R_ZY_BRXXB0 ZY_BRXXB0%rowtype;
ls_DJH000 varchar(20);
ls_MXID00 varchar(20);
ls_JZDH00 varchar(20);
ls_YHRYXB varchar(1); --医护人员性别
R_IC_YHXX00 IC_YHXX00%rowtype;
cursor CUR_BRFY00 is
select aa.* from VW_ZY_JTBCJK_BRFY aa where aa.DJH000=ls_DJH000 and aa.ZYID00=ls_ZYID00;
cursor CUR_FYMX00 is
select aa.* from VW_ZY_JTBCJK_FYMX aa where aa.MXID00=ls_MXID00 and aa.ZYID00=ls_ZYID00;
cursor CUR_JZB00 is
select aa.* from VW_ZY_JTBCJK_JZXX aa where aa.JZDH00=ls_JZDH00 and aa.ZYID00=ls_ZYID00;
begin
LS_FHXX00:='';
ls_yyid00:=substrb(trim(SF_SF_TYZD00('家庭病床接口','JTBCJK_yyid00')),1,6);
ls_jkbj:=trim(SF_SF_TYZD00('家庭病床接口','JTBCJK_jkbj'));
ls_keyvalue:=trim(SF_SF_TYZD00('家庭病床接口','JTBCJK_keyvalue'));
ls_id0000:=SF_SF_GETXMLNOTE('id0000',trim(PINTSTR));
begin
ls_ZYID00:=to_number(SF_SF_GETXMLNOTE('ZYID00',trim(PINTSTR)));
exception
when others then
ls_ZYID00:=0;
end;
--建床状态更新
if PJKCJBM='ZYSF_JTBCJK_0001' then
select count(*) into ld_Counts from ZY_JTBCSQ where id0000=ls_id0000;
if ld_Counts>0 then
select * into R_JTBCSQ from ZY_JTBCSQ aa where id0000=ls_id0000;
if nvl(R_JTBCSQ.ZYID00,-1)>0 then
select MRN000 into ls_MRN000 from ZY_BRXXB0 where ZYID00=R_JTBCSQ.ZYID00;
end if;
LS_JSONSTR:='"id0000":"'||R_JTBCSQ.id0000||'",'
||'"brxm00":"'||trim(R_JTBCSQ.brxm00)||'",'
||'"jzkh00":"'||trim(R_JTBCSQ.jzkh00)||'",'
||'"shsj00":"'||trim(R_JTBCSQ.shsj00)||'",'
||'"shrid0":"'||trim(R_JTBCSQ.shrid0)||'",'
||'"shzt00":"'||trim(R_JTBCSQ.shzt00)||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||trim(ls_yyid00)||'",'
||'"jkbj":"'||trim(ls_jkbj)||'",'
||'"keyvalue":"'||trim(ls_keyvalue)||'",'
||'"bah000":"'||trim(ls_MRN000)||'",'
||'"jsyy00":"'||trim(R_JTBCSQ.jsyy00)||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
--家庭病床病人信息
if PJKCJBM='ZYSF_JTBCJK_0002' then
select count(*) into ld_Counts from ZY_JTBCSQ where id0000=ls_id0000;
if ld_Counts>0 then
select * into R_JTBCSQ from ZY_JTBCSQ aa where id0000=ls_id0000;
if nvl(R_JTBCSQ.ZYID00,-1)>0 then
select MRN000 into ls_MRN000 from ZY_BRXXB0 where ZYID00=R_JTBCSQ.ZYID00;
end if;
select * into R_BM_BRXXB0 from BM_BRXXB0 where BRID00=R_JTBCSQ.BRID00;
select * into R_IC_YHXX00 from IC_YHXX00 where BRID00=R_JTBCSQ.BRID00 and ZT0000='1';
LS_JSONSTR:='"id0000":"'||R_JTBCSQ.id0000||'",'
||'"brid00":"'||trim(ls_yyid00)||to_char(R_JTBCSQ.BRID00)||'",'
||'"bah000":"'||trim(nvl(ls_MRN000,R_BM_BRXXB0.MRN000))||'",'
||'"ybkh00":"'||trim(substr(nvl(nvl(R_BM_BRXXB0.YBKH00,R_IC_YHXX00.ICKH00),to_char(R_JTBCSQ.BRID00)),1,30))||'",'
||'"ybid00":"'||trim(R_BM_BRXXB0.YBID00)||'",'
||'"brxm00":"'||trim(R_BM_BRXXB0.BRXM00)||'",'
||'"brxb00":"'||trim(nvl(R_BM_BRXXB0.XBBH00,'9'))||'",'
||'"brcsrq":"'||trim(R_BM_BRXXB0.BRCSRQ)||'",'
||'"brhyzk":"'||trim(R_BM_BRXXB0.BRHYZK)||'",'
||'"brzy00":"'||trim(R_BM_BRXXB0.BRZY00)||'",'
||'"brmz00":"'||trim(R_BM_BRXXB0.BRMZ00)||'",'
||'"brgj00":"'||trim(R_BM_BRXXB0.BRGJ00)||'",'
||'"brzjlx":"'||trim(R_BM_BRXXB0.ZJLXBH)||'",'
||'"brzjbh":"'||trim(R_BM_BRXXB0.BRZJBH)||'",'
||'"brdh00":"'||trim(R_BM_BRXXB0.BRDH00)||'",'
||'"brlxgx":"'||trim(R_BM_BRXXB0.BRLXGX)||'",'
||'"brlxxm":"'||trim(R_BM_BRXXB0.BRLXXM)||'",'
||'"brlxdz":"'||trim(R_BM_BRXXB0.BRLXDZ)||'",'
||'"brlxdh":"'||trim(R_BM_BRXXB0.BRLXDH)||'",'
||'"czdzsf":"'||trim(R_BM_BRXXB0.BRSZSF)||'",'
||'"czdzds":"'||trim(R_BM_BRXXB0.BRSZS0)||'",'
||'"czdzxq":"'||trim(R_BM_BRXXB0.BRSZZ0)||'",'
||'"czdzmh":"'||trim(R_BM_BRXXB0.BRSZMP)||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
--家庭病床住院信息
if PJKCJBM='ZYSF_JTBCJK_0003' then
select count(*) into ld_Counts from ZY_JTBCSQ where id0000=ls_id0000 and ZYID00=ls_ZYID00 and ls_ZYID00>0;
if ld_Counts>0 then
select * into R_ZY_BRXXB0 from ZY_BRXXB0 where ZYID00=ls_ZYID00;
select * into R_BM_BRXXB0 from BM_BRXXB0 where BRID00=R_ZY_BRXXB0.BRID00;
select * into R_IC_YHXX00 from IC_YHXX00 where BRID00=R_JTBCSQ.BRID00 and ZT0000='1';
LS_JSONSTR:='"id0000":"'||trim(ls_id0000)||'",'
||'"brid00":"'||trim(ls_yyid00)||to_char(R_ZY_BRXXB0.BRID00)||'",'
||'"mrn000":"'||trim(nvl(R_ZY_BRXXB0.MRN000,R_BM_BRXXB0.MRN000))||'",'
||'"zyid00":"'||to_char(R_ZY_BRXXB0.ZYID00)||'",'
||'"zyh000":"'||trim(R_ZY_BRXXB0.ZYH000)||'",'
||'"ybzyh0":"'||trim(R_ZY_BRXXB0.YBZYH0)||'",'
||'"xm0000":"'||trim(R_ZY_BRXXB0.XM0000)||'",'
||'"xb0000":"'||trim(nvl(R_BM_BRXXB0.XBBH00,'9'))||'",'
||'"csrq00":"'||trim(R_ZY_BRXXB0.CSRQ00)||'",'
||'"fbbh00":"'||trim(to_char(R_ZY_BRXXB0.FBBH00))||'",'
||'"ybkh00":"'||trim(substr(nvl(nvl(R_ZY_BRXXB0.YBKH00,R_IC_YHXX00.ICKH00),to_char(R_JTBCSQ.BRID00)),1,30))||'",'
||'"ybid00":"'||trim(to_char(R_ZY_BRXXB0.YBID00))||'",'
||'"ryrq00":"'||trim(R_ZY_BRXXB0.RYRQ00)||'",'
||'"rysj00":"'||trim(R_ZY_BRXXB0.RYSJ00)||'",'
||'"ryczrq":"'||trim(R_ZY_BRXXB0.RYRQ00)||'",'
||'"ryczsj":"'||trim(R_ZY_BRXXB0.RYSJ00)||'",'
||'"rybq00":"'||trim(to_char(R_ZY_BRXXB0.RYBQ00))||'",'
||'"cwid00":"'||trim(R_ZY_BRXXB0.RYCWH0)||'",'
||'"cyrq00":"'||trim(R_ZY_BRXXB0.CYRQ00)||'",'
||'"cysj00":"'||trim(R_ZY_BRXXB0.CYSJ00)||'",'
||'"zzys00":"'||trim(to_char(R_ZY_BRXXB0.ZZYS00))||'",'
||'"zzysxm":"'||trim(R_ZY_BRXXB0.ZZYSXM)||'",'
||'"zrys00":"'||trim(to_char(R_ZY_BRXXB0.ZRYS00))||'",'
||'"zrysxm":"'||trim(R_ZY_BRXXB0.ZRYSXM)||'",'
||'"zyys00":"'||trim(to_char(R_ZY_BRXXB0.ZZYS00))||'",'
||'"zyysxm":"'||trim(R_ZY_BRXXB0.ZZYSXM)||'",'
||'"brrycs":"'||trim(R_ZY_BRXXB0.BRRYCS)||'",'
||'"brzt00":"'||substrb(R_ZY_BRXXB0.BRZT00,1,1)||'",'
||'"szys00":"'||trim(to_char(R_ZY_BRXXB0.SZYS00))||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
--病人费用
if PJKCJBM='ZYSF_JTBCJK_0004' then
ls_DJH000:=to_number(SF_SF_GETXMLNOTE('DJH000',trim(PINTSTR)));
for REC in CUR_BRFY00 loop
LS_JSONSTR:='"djh000":"'||trim(ls_yyid00)||to_char(REC.DJH000)||'",'
||'"ybdjh0":"'||trim(REC.YBDJH0)||'",'
||'"zyid00":"'||to_char(REC.ZYID00)||'",'
||'"xm0000":"'||trim(REC.XM0000)||'",'
||'"hjje00":"'||to_char(REC.HJJE00)||'",'
||'"zfje00":"'||to_char(REC.ZFJE00)||'",'
||'"jzje00":"'||to_char(REC.JZJE00)||'",'
||'"brye00":"'||to_char(REC.BRYE00)||'",'
||'"czrq00":"'||REC.CZRQ00||'",'
||'"czsj00":"'||REC.CZSJ00||'",'
||'"czy000":"'||to_char(REC.CZY000)||'",'
||'"czyxm0":"'||trim(REC.CZYXM0)||'",'
||'"czyks0":"'||to_char(REC.CZYKS0)||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
end loop;
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
--费用明细
if PJKCJBM='ZYSF_JTBCJK_0005' then
ls_MXID00:=to_number(SF_SF_GETXMLNOTE('MXID00',trim(PINTSTR)));
for REC in CUR_FYMX00 loop
LS_JSONSTR:='"mxid00":"'||trim(ls_yyid00)||to_char(REC.MXID00)||'",'
||'"djh000":"'||REC.DJH000||'",'
||'"xmbh00":"'||to_char(REC.xmbh00)||'",'
||'"xmfb00":"'||to_char(REC.FPXMID)||'",'
||'"xmmc00":"'||trim(REC.XMMC00)||'",'
||'"xmgg00":"'||trim(REC.XMGG00)||'",'
||'"xmdw00":"'||trim(REC.XMDW00)||'",'
||'"xmdj00":"'||to_char(REC.XMDJ00)||'",'
||'"xmsl00":"'||to_char(REC.XMSL00)||'",'
||'"hjje00":"'||to_char(REC.HJJE00)||'",'
||'"zfje00":"'||to_char(REC.ZFJE00)||'",'
||'"jzje00":"'||to_char(REC.JZJE00)||'",'
||'"czrq00":"'||REC.CZRQ00||'",'
||'"czsj00":"'||REC.CZSJ00||'",'
||'"kdys00":"'||to_char(REC.kdys00)||'",'
||'"zxys00":"'||to_char(REC.ZXYS00)||'",'
||'"bz0000":"'||trim(REC.BZ0000)||'",'
||'"zfbl00":"'||to_char(REC.zfbl00)||'",'
||'"ybbm00":"'||trim(REC.YBBM00)||'",'
||'"gjbm00":"'||trim(REC.GJBM00)||'",'
||'"yppc00":"'||trim(REC.YPPC00)||'",'
||'"sfyp00":"'||trim(REC.SFYP00)||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
end loop;
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
--结算单
if PJKCJBM='ZYSF_JTBCJK_0006' then
ls_JZDH00:=to_number(SF_SF_GETXMLNOTE('JZDH00',trim(PINTSTR)));
for REC in CUR_JZB00 loop
LS_JSONSTR:='"jzdh00":"'||trim(ls_yyid00)||to_char(REC.JZDH00)||'",'
--||'"id0000":"'||trim(LS_id0000)||'",'
||'"ybdjh0":"'||trim(REC.YBDJH0)||'",'
||'"zyid00":"'||to_char(REC.ZYID00)||'",'
||'"hjje00":"'||to_char(REC.HJJE00)||'",'
||'"jzje00":"'||to_char(REC.JZJE00)||'",'
||'"jmje00":"'||to_char(REC.JMJE00)||'",'
||'"grzhzf":"'||to_char(REC.GRZHZF)||'",'
||'"tcjjzf":"'||to_char(REC.TCJJZF)||'",'
||'"zfje00":"'||to_char(REC.ZFJE00)||'",'
||'"ybzhye":"'||to_char(REC.YBZHYE)||'",'
||'"tsbzbh":"'||trim(REC.TSBZBH)||'",'
||'"gwybz0":"'||to_char(REC.GWYBZ0)||'",'
||'"bcyljz":"'||to_char(REC.BCYLJZ)||'",'
||'"jsjzze":"'||to_char(REC.JSJZZE)||'",'
||'"cljzze":"'||to_char(REC.CLJZZE)||'",'
||'"dbbcbcje":"'||to_char(REC.DBBCBCJE)||'",'
||'"yljzjmje":"'||to_char(REC.YLJZJMJE)||'",'
||'"hlfjm0":"'||to_char(REC.HLFJM0)||'",'
||'"jcfjm0":"'||to_char(REC.JCFJM0)||'",'
||'"cwfjm0":"'||to_char(REC.CWFJM0)||'",'
||'"ssfjm0":"'||to_char(REC.SSFJM0)||'",'
||'"jzfp02":"'||to_char(REC.JZFP02)||'",'
||'"jzfp01":"'||to_char(REC.JZFP01)||'",'
||'"csjz00":"'||to_char(REC.CSJZ00)||'",'
||'"dbzsjfy":"'||to_char(REC.DBZSJFY)||'",'
||'"jzfp03":"'||to_char(REC.JZFP03)||'",'
||'"dbjjzf":"'||to_char(REC.DBJJZF)||'",'
||'"jzfpje":"'||to_char(REC.JZFPJE)||'",'
||'"bcjj01":"'||to_char(REC.BCJJ01)||'",'
||'"mzbzje":"'||to_char(REC.MZBZJE)||'",'
||'"jzrq00":"'||REC.JZRQ00||'",'
||'"jzsj00":"'||REC.JZSJ00||'",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
end loop;
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
--家庭病床医护人员 PINTSTR=15
if PJKCJBM='ZYSF_JTBCJK_1001' then
ls_YGBH00:=SF_SF_GETXMLNOTE('YGBH00',trim(PINTSTR));
select count(*) into ld_Counts from BM_YGBM00 where to_char(YGBH00)=ls_YGBH00;
if ld_Counts>0 then
select * into R_YGBM00 from BM_YGBM00 where to_char(YGBH00)=ls_YGBH00;
ls_ygxz00:='2';
if instr(','||R_YGBM00.YGXZ00||',' , ',1,')>0 then
ls_ygxz00:='1';
elsif instr(','||R_YGBM00.YGXZ00||',' , ',3,')>0 then
ls_ygxz00:='3';
elsif instr(','||R_YGBM00.YGXZ00||',' , ',4,')>0 then
ls_ygxz00:='4';
end if;
if R_YGBM00.XB0000='男' then
ls_YHRYXB:='1';
elsif R_YGBM00.XB0000='女' then
ls_YHRYXB:='2';
else
ls_YHRYXB:='9';
end if;
LS_JSONSTR:='"yyid00":"'||ls_yyid00||'",'
||'"zwxm00":"'||trim(R_YGBM00.ZWXM00)||'",'
||'"xb0000":"'||trim(ls_YHRYXB)||'",'
||'"csrq00":"'||substrb(R_YGBM00.SFZBH0,7,8)||'",'
||'"sfzh00":"'||trim(R_YGBM00.SFZBH0)||'",'
||'"ygxz00":"'||trim(ls_ygxz00)||'",'
||'"ygbh00":"'||trim(ls_yyid00)||to_char(R_YGBM00.YGBH00)||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'",'
||'"cjrqsj":"'||R_YGBM00.CJRQSJ||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
--家庭病床病区 PINTSTR=15
if PJKCJBM='ZYSF_JTBCJK_1002' then
ls_BMBH00:=SF_SF_GETXMLNOTE('BMBH00',trim(PINTSTR));
select count(*) into ld_Counts from BM_BMBM00 where to_char(BMBH00)=ls_BMBH00;
if ld_Counts>0 then
select * into R_BMBM00 from BM_BMBM00 where to_char(BMBH00)=ls_BMBH00;
LS_JSONSTR:='"bmbh00":"'||trim(ls_yyid00)||to_char(R_BMBM00.BMBH00)||'",'
||'"groupname":"'||trim(R_BMBM00.BMMC00)||'",'
||'"createtime":"'||R_BMBM00.CJRQSJ||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
--家庭病床床位 PINTSTR=15
if PJKCJBM='ZYSF_JTBCJK_1003' then
ls_CH0000:=trim(SF_SF_GETXMLNOTE('CH0000',trim(PINTSTR)));
select count(*) into ld_Counts from BM_CWK000 where trim(CH0000)=ls_CH0000;
if ld_Counts>0 then
select * into R_CWK000 from BM_CWK000 where trim(CH0000)=ls_CH0000;
LS_JSONSTR:='"cwid00":"'||trim(ls_yyid00)||trim(R_CWK000.CH0000)||'",'
||'"ch0000":"'||trim(R_CWK000.CH0000)||'",'
||'"cwxz00":"0",'
||'"fillintime":"'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'",'
||'"yyid00":"'||ls_yyid00||'",'
||'"jkbj":"'||ls_jkbj||'",'
||'"keyvalue":"'||ls_keyvalue||'"';
LS_FHXX00:='{'||LS_JSONSTR||'}';
end if;
end if;
return LS_FHXX00;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%