create or replace procedure SP_ZY_JSQDQUERY_PAYINFO(
 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  住院医疗保障基金结算清单(基金支付信息<payinfo>) 
LS_payinfo_Json       ZY_YBJKMX.VALUE0%type;--[字符]基金支付信息<payinfo>
LS_payinfo_Num        number(12);--[数量]基金支付信息<payinfo>

--基金支付信息<payinfo>开始_医保
cursor CUR_payinfo_YB is
  select aa.fund_pay_type      as  fund_pay_type          ,--fund_pay_type        基金支付类型编码
         aa.fund_pay_type_name as fund_pay_type_name      ,--fund_pay_type_name   基金支付类型名称
         aa.fund_payamt        as fund_payamt              --fund_payamt          基金支付金额
  from ZY_YBDATA AA
  where aa.ZYID00=PZYID00
  and ((nvl(PJZDH00,0)>0 and aa.JZDH00=PJZDH00) 
  or (nvl(PJZDH00,0)=0 and ( aa.ybjkid in (select bb.ybjkid from zy_jzb000 bb where bb.zyid00=PZYID00 and bb.hjje00>=0 and nvl(bb.JZZT00,'0')='0' ))))
  and aa. JKJDBM='setldetail'
;

--基金支付信息<payinfo>开始_院内 
cursor CUR_payinfo_YN is
  select tt.fund_pay_type as fund_pay_type,
         substrb(SF_XT_GETYBSXZD ('fund_pay_type', tt.fund_pay_type, '3', '2', 0),1,50) as fund_pay_type_name,
         sum(tt.fund_payamt) as fund_payamt
    from (select decode(aa.ZFFS00,3,'310200',4,'310100',16,'320100','390100') as  fund_pay_type,--fund_pay_type 基金支付类型    varchar2(6)     
                 aa.JFJE00 as fund_payamt                                                       --fund_payamt   基金支付金额    number(16,2)
            from ZY_BRJFB0 AA 
              where aa.ZYID00=PZYID00 
                and ((nvl(PJZDH00,0)>0 and aa.JZDH00=PJZDH00) or (nvl(PJZDH00,0)=0 and 1=1))
                and aa.ZFFS00  in (3,4,10,11,13,14,15,16)
         ) tt group by tt.fund_pay_type,substrb(SF_XT_GETYBSXZD ('fund_pay_type', tt.fund_pay_type, '3', '2', 0),1,50)
;


begin
 PZXZT00:=0;--输出:执行状态 0:失败 1：成功
 PERRMSG:='';--输出:错误信息
 PFHXML0:='';--输出:返回参数(XML格式)
 PFHNUM0:=0; --输出:返回记录数
 LS_payinfo_Json:=null;     --[字符]基金支付信息<payinfo>
 LS_payinfo_Num :=0;--[数量]基金支付信息<payinfo>
 --步骤1：先查医保返回的
 for C_List in CUR_payinfo_YB loop
        LS_payinfo_Json:=null;
      LS_payinfo_Json:=LS_payinfo_Json||'{';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_pay_type"'||':'||'"'||C_List.fund_pay_type||'"'||',';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_pay_type_name"'||':'||'"'||C_List.fund_pay_type_name||'"'||',';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_payamt"'||':'||'"'||trim(to_char(C_List.fund_payamt,'9999999990.99'))||'"'||'';
      LS_payinfo_Json:=LS_payinfo_Json||'}';
      --插入ZY_YBJSDX
      insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,fund_pay_type,poolarea_fund_pay_type,poolarea_fund_pay_name,fund_payamt)
                    select PYBJKID,'payinfo','基金支付信息',LS_payinfo_Num,
                           substrb(C_List.fund_pay_type,1,10) as fund_pay_type,
                           substrb(C_List.fund_pay_type,1,10) as poolarea_fund_pay_type,
                           substrb(C_List.fund_pay_type_name,1,50) as poolarea_fund_pay_name,
                           C_List.fund_payamt
                      from dual A;
      --插入ZY_YBJKMX
      insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0)
                     select PYBJKID,LS_payinfo_Num,'payinfo','data','payinfo','基金支付信息',null,null,'1',null,LS_payinfo_Json from dual;
      LS_payinfo_Num:=nvl(LS_payinfo_Num,0)+1;
  end loop;
 --步骤2：查下院内 
 if nvl(LS_payinfo_Num,0)=0 then 
  for C_List in CUR_payinfo_YN loop
        LS_payinfo_Json:=null;
      LS_payinfo_Json:=LS_payinfo_Json||'{';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_pay_type"'||':'||'"'||C_List.fund_pay_type||'"'||',';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_pay_type_name"'||':'||'"'||C_List.fund_pay_type_name||'"'||',';
      LS_payinfo_Json:=LS_payinfo_Json||'"fund_payamt"'||':'||'"'||trim(to_char(C_List.fund_payamt,'9999999990.99'))||'"'||'';
      LS_payinfo_Json:=LS_payinfo_Json||'}';
      --插入ZY_YBJSDX
      insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,fund_pay_type,poolarea_fund_pay_type,poolarea_fund_pay_name,fund_payamt)
                    select PYBJKID,'payinfo','基金支付信息',LS_payinfo_Num,
                           substrb(C_List.fund_pay_type,1,10) as fund_pay_type,
                           substrb(C_List.fund_pay_type,1,10) as poolarea_fund_pay_type,
                           substrb(C_List.fund_pay_type_name,1,50) as poolarea_fund_pay_name,
                           C_List.fund_payamt
                      from dual A;
      --插入ZY_YBJKMX
      insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0)
                     select PYBJKID,LS_payinfo_Num,'payinfo','data','payinfo','基金支付信息',null,null,'1',null,LS_payinfo_Json from dual;
      LS_payinfo_Num:=nvl(LS_payinfo_Num,0)+1;
  end loop;     
 end if; 
 
  --无数据插入一笔空    
 if nvl(LS_payinfo_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,'payinfo','data','payinfo','基金支付信息',null,null,'1',null,
   LS_payinfo_Json from dual;
 end if;
     
  
 if  PCOMMIT='Y' then
  commit;
 end if;
 PZXZT00:=1;                    --输出:执行状态 0:失败 1：成功
 PERRMSG:=null;                 --输出:错误信息
 PFHNUM0:=nvl(LS_payinfo_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;
/