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，医护人员性别取值 ZYSF-20231108-001
-- chenHeyi
-- -------- ---------- ---------
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;
ls_ICKH00 IC_YHXX00.ICKH00%type;
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;
begin
select ICKH00 into ls_ICKH00 from IC_YHXX00 where BRID00=R_JTBCSQ.BRID00 and ZT0000='1' and rownum=1;
exception
when others then
ls_ICKH00:='';
end;
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,ls_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';
begin
select ICKH00 into ls_ICKH00 from IC_YHXX00 where BRID00=R_JTBCSQ.BRID00 and ZT0000='1' and rownum=1;
exception
when others then
ls_ICKH00:='';
end;
LS_JSONSTR:='"id0000":"'||trim(ls_id0000)||'",'
||'"brid00":"'||to_char(R_ZY_BRXXB0.BRID00)||'",'
||'"mrn000":"'||trim(nvl(R_ZY_BRXXB0.MRN000,R_BM_BRXXB0.MRN000))||'",'
||'"zyid00":"'||trim(ls_yyid00)||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,ls_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":"'||trim(ls_yyid00)||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":"'||trim(ls_yyid00)||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":"'||trim(ls_yyid00)||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;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 家庭病床业务请求 SP_ZY_JTBCYWQQ
create or replace procedure SP_ZY_JTBCYWQQ
(
PJKCJBM in varchar2 , --接口场景编码
PINTSTR in varchar2 default '' , --His入参 xml编码格式传入
PCOMMIT in varchar2 default 'N' , --是否提交事务 Y:提交 N:不提交
PZXZT00 out number , --[出参]执行状态 0:失败 1：成功
PRZID00 out number , --[出参]输出:日志记录ID
POUTSTR out varchar2 , --[出参]备用出参xml格式输出
PERRMSG out varchar2 --[出参]错误信息
)
-- MODIFICATION HISTORY
-- Person Date Comments
-- chenHeyi 2023.08.29 created ZYSF-20230828-004
-- chenHeyi 2023.09.14 场景名称从BM_TYCJBM取 ZYSF-20230914-002
-- chenHeyi 2023.09.28 细化处理场景ZYSF_JTBCJK_0001~0006的日记生成 ZYSF-20231007-001
-- chenHeyi 2023.11.09 入参PCOMMIT默认值改为N ZYSF-20231108-001
-- --------- ---------- -------
as
ld_Counts number(10);
ls_id0000 ZY_JTBCRZ.ID0000%type;
ld_RZID00 ZY_JTBCRZ.RZID00%type;
ld_CZY000 BM_YGBM00.YGBH00%type;
ld_CZKS00 BM_BMBM00.BMBH00%type;
ls_JKCJMC BM_TYCJBM.CJMC00%type;
ls_BRID00 varchar(50);
ls_ZYID00 varchar(50);
ls_DJH000 varchar(50);
ls_MXID00 varchar(50);
ls_JZDH00 varchar(50);
begin
PZXZT00:=0;
begin
ld_CZY000:=to_number(SF_SF_GETXMLNOTE('UserId',trim(PINTSTR)));
exception
when others then
ld_CZY000:=0;
end;
begin
ld_CZKS00:=to_number(SF_SF_GETXMLNOTE('DeptId',trim(PINTSTR)));
exception
when others then
ld_CZKS00:=0;
end;
ls_id0000:=SF_SF_GETXMLNOTE('id0000',trim(PINTSTR));
select CJMC00 into ls_JKCJMC from BM_TYCJBM where XTMC00='家庭病床接口' and CJBM00=PJKCJBM;
if PJKCJBM='ZYSF_JTBCJK_0001' then
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and JKZTBZ in ('0','-1');
if ld_Counts>0 then
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and JKZTBZ in ('0','-1') and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0');
end if;
end if;
if PJKCJBM='ZYSF_JTBCJK_0002' then
ls_BRID00:=SF_SF_GETXMLNOTE('BRID00',trim(PINTSTR));
if ls_BRID00 is null or ls_BRID00='' then
select to_char(BRID00) into ls_BRID00 from ZY_JTBCSQ where ID0000=ls_id0000 ;
end if;
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='BM_BRXXB0' and DATAID=ls_BRID00;
if ld_Counts>0 then
update ZY_JTBCRZ set JKZTBZ='0' where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='BM_BRXXB0' and DATAID=ls_BRID00;
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='BM_BRXXB0' and DATAID=ls_BRID00 and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ,TABLE0,DATAID,BRID00)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0','BM_BRXXB0',ls_BRID00,ls_BRID00);
end if;
end if;
if PJKCJBM='ZYSF_JTBCJK_0003' then
ls_BRID00:=SF_SF_GETXMLNOTE('BRID00',trim(PINTSTR));
ls_ZYID00:=SF_SF_GETXMLNOTE('ZYID00',trim(PINTSTR));
if ls_BRID00 is null or ls_BRID00='' then
select to_char(BRID00) into ls_BRID00 from ZY_JTBCSQ where ID0000=ls_id0000 ;
end if;
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRXXB0' and DATAID=ls_ZYID00;
if ld_Counts>0 then
update ZY_JTBCRZ set JKZTBZ='0' where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRXXB0' and DATAID=ls_ZYID00;
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRXXB0' and DATAID=ls_ZYID00 and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ,TABLE0,DATAID,BRID00,ZYID00)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0','ZY_BRXXB0',ls_ZYID00,ls_BRID00,ls_ZYID00);
end if;
end if;
if PJKCJBM='ZYSF_JTBCJK_0004' then
ls_DJH000:=SF_SF_GETXMLNOTE('DJH000',trim(PINTSTR));
ls_BRID00:=SF_SF_GETXMLNOTE('BRID00',trim(PINTSTR));
ls_ZYID00:=SF_SF_GETXMLNOTE('ZYID00',trim(PINTSTR));
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRFY00' and DATAID=ls_DJH000 and to_char(ZYID00)=ls_ZYID00;
if ld_Counts>0 then
update ZY_JTBCRZ set JKZTBZ='0' where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRFY00' and DATAID=ls_DJH000 and to_char(ZYID00)=ls_ZYID00;
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_BRFY00' and DATAID=ls_DJH000 and to_char(ZYID00)=ls_ZYID00 and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ,TABLE0,DATAID,BRID00,ZYID00)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0','ZY_BRFY00',ls_DJH000,ls_BRID00,ls_ZYID00);
end if;
end if;
if PJKCJBM='ZYSF_JTBCJK_0005' then
ls_MXID00:=SF_SF_GETXMLNOTE('MXID00',trim(PINTSTR));
ls_BRID00:=SF_SF_GETXMLNOTE('BRID00',trim(PINTSTR));
ls_ZYID00:=SF_SF_GETXMLNOTE('ZYID00',trim(PINTSTR));
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_FYMX00' and DATAID=ls_MXID00 and to_char(ZYID00)=ls_ZYID00;
if ld_Counts>0 then
update ZY_JTBCRZ set JKZTBZ='0' where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_FYMX00' and DATAID=ls_MXID00 and to_char(ZYID00)=ls_ZYID00;
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_FYMX00' and DATAID=ls_MXID00 and to_char(ZYID00)=ls_ZYID00 and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ,TABLE0,DATAID,BRID00,ZYID00)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0','ZY_FYMX00',ls_MXID00,ls_BRID00,ls_ZYID00);
end if;
end if;
if PJKCJBM='ZYSF_JTBCJK_0006' then
ls_JZDH00:=SF_SF_GETXMLNOTE('JZDH00',trim(PINTSTR));
ls_BRID00:=SF_SF_GETXMLNOTE('BRID00',trim(PINTSTR));
ls_ZYID00:=SF_SF_GETXMLNOTE('ZYID00',trim(PINTSTR));
select count(*) into ld_Counts from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_JZB000' and DATAID=ls_JZDH00 and to_char(ZYID00)=ls_ZYID00;
if ld_Counts>0 then
update ZY_JTBCRZ set JKZTBZ='0' where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_JZB000' and DATAID=ls_JZDH00 and to_char(ZYID00)=ls_ZYID00;
select RZID00 into ld_RZID00 from ZY_JTBCRZ where ID0000=ls_id0000 and JKCJBM=PJKCJBM and TABLE0='ZY_JZB000' and DATAID=ls_JZDH00 and to_char(ZYID00)=ls_ZYID00 and rownum=1;
else
select SQ_ZY_JTBCRZ_RZID00.Nextval into ld_RZID00 from dual;
insert into ZY_JTBCRZ(RZID00,ID0000,JKCJBM,JKCJMC,CZY000,CZKS00,INTSTR,JKZTBZ,TABLE0,DATAID,BRID00,ZYID00)
values(ld_RZID00,ls_id0000,PJKCJBM,ls_JKCJMC,ld_CZY000,ld_CZKS00,PINTSTR,'0','ZY_JZB000',ls_JZDH00,ls_BRID00,ls_ZYID00);
end if;
end if;
PRZID00:=ld_RZID00;
if PCOMMIT='Y' then
commit;
end if;
PZXZT00:=1;--输出:执行状态 0:失败 1：成功
exception
when no_data_found then
PZXZT00:=0;
PERRMSG:=substrb('家庭病床业务请求失败!原因:'||sqlerrm,1,200);
rollback;
when others then
PZXZT00:=0;
PERRMSG:=substrb('家庭病床业务请求失败!原因:'||sqlerrm,1,200);
rollback;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%